o
    +zh                    @   s  d dl Zd dlmZmZmZmZmZmZm	Z	m
Z
mZmZ d dlZd dlZd dlZd dlmZ d dlmZ d dlmZmZ d dlmZ d dlmZ d dlZd dlZd dlmZ d dl Z d dl!Z!d d	l"m#Z# d d
l$m%Z%m&Z&m'Z'm(Z(m)Z)m*Z* d dl+T d dl,T d dl-m.Z.m/Z/ d dl0m1Z1 d dl2m3Z3m4Z4 d dl5m6Z6 d dl7m7Z7 d dl8Z8d dl9m:Z:m;Z; d dl<m=Z= d dl>m?Z? d dl@mAZA d dlBmCZC d dlZeC  eDdZEeDdZFeeGZHeAeH eHjIe?dd deH_JeHKddd ZLeHMddd ZNeHMd d!d  ZOeHMd"d#d" ZPeHMd$d%d$ ZQeHMd&d'd& ZRd(d) ZSd*d+ ZTd,d- ZUejVWejVXd.d/ZYejVZeYs>e[eY eYeHj\d0< G d1d2 d2Z]e] Z^d3d4 Z_eHjKd5d6d7gd8d9d: Z`eHKd;d<d= ZaeHjKd>d6d7gd8d?d@ ZbeHjKdAd6d7gd8e_dBdC ZcdDdE ZddFdG ZedHdI ZfdJdK ZgddMdNZheHjKdOd6d7gd8dPdQ ZieHKdRdSdT ZjeHKdUdVdW ZkeHKdXdYdZ ZleHjKd[d7gd8d\d] ZmeHjKd^d7gd8d_d` Zndadb ZoeHjKdcd7gd8e_ddde Zpdfdg Zqdhdi Zrdjdk Zsdldm ZteHjKdnd7gd8e_dodp ZueHjKdqd7gd8drds ZveHKdte_dudv ZweHKdwe_dxdy ZxeHKdzd{d| ZyeHKd}e_dddZzeHjKdd7gd8e_dd Z{eHjKdd7gd8e_dd Z|dddZ}eHKddd Z~eHKde_dd ZeHKde_dd ZeHKde_dd ZeHKde_dd ZeHKddd ZeHjKdd7gd8e_dd ZeHjKdd7gd8e_dd ZeHKddd ZeHKddd ZdS )    N)
Flaskrequestrender_template	send_filejsonifysend_from_directoryredirecturl_forsessionflash)get_close_matches)Document)InchesPt)WD_ALIGN_PARAGRAPH)WD_ALIGN_VERTICAL)defaultdict)damage_solutions)COMPONENT_ORDERnormalize_componentremove_special_characterssort_componentsget_db_connectionclean_dataframe_data)*)#generate_condition_evaluation_pivot"generate_condition_evaluation_html)"generate_all_component_evaluations)&generate_detailed_condition_evaluation generate_detailed_condition_html)validate_excel_file)datetime)generate_password_hashcheck_password_hash)secure_filename)api_bp)CORS)load_dotenv
JWT_SECRETJWT_ALGORITHMz/api)
url_prefixyour_secret_key_herez/favicon.icoc                   C   
   t ddS )Nstaticzfavicon.icor    r/   r/   2/home/skpark/git/infrasmart_work/infrasmart/app.pyfavicon2      
r1   evaluate_crackc                 C   R   | dks	t | rdS t| } | dkrdS | dkrdS | dkr!dS | d	kr'd
S dS )N-a      ?e      ?d333333?c皙?bpdisnafloatvaluer/   r/   r0   r3   8      evaluate_crack_ratioc                 C   r4   )Nr5   r6      r8      r:   
   r<      r>   r?   rC   r/   r/   r0   rF   G   rE   evaluate_leakc                 C   sF   | dks	t | rdS t| } | dkrdS | dkrdS | dkr!dS dS )	Nr5   r6   rG   r:   rI   r<   r   r>   r?   rC   r/   r/   r0   rK   V   s   evaluate_surfacec                 C   R   | dks	t | rdS t| } | dkrdS | dkrdS | dkr!dS | d	kr'd
S dS )Nr5   r6   rG   r8   rI   r:   rJ   r<   r   r>   r?   rC   r/   r/   r0   rL   c   rE   evaluate_rebarc                 C   rM   )Nr5   r6      r8      r:      r<   r   r>   r?   rC   r/   r/   r0   rN   r   rE   c                  C   s   t  } |  }|d |d |d |d |d |d |d |d | d	 d	krDtd
}|dd|df |d |   |  |   d S )Na  
       
CREATE TABLE IF NOT EXISTS public.users
            (
                id integer NOT NULL DEFAULT nextval('users_id_seq'::regclass),
                username character varying(50) COLLATE pg_catalog."default" NOT NULL,
                password character varying(255) COLLATE pg_catalog."default" NOT NULL,
                email character varying(100) COLLATE pg_catalog."default" NOT NULL,
                created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
                company character varying(100) COLLATE pg_catalog."default",
                CONSTRAINT users_pkey PRIMARY KEY (id),
                CONSTRAINT users_email_key UNIQUE (email),
                CONSTRAINT users_username_key UNIQUE (username)
            )

    u  
        CREATE TABLE IF NOT EXISTS uploaded_files (
            id SERIAL PRIMARY KEY,
            user_id INTEGER REFERENCES users(id),
            filename VARCHAR(255) NOT NULL,
            original_filename VARCHAR(255) NOT NULL,
            upload_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            file_data JSONB NOT NULL,
            bridge_name VARCHAR(255), -- 교량명
            length NUMERIC,          -- 연장
            width NUMERIC,           -- 폭
            structure_type VARCHAR(255), -- 구조형식
            span_count INTEGER,      -- 경간수
            expansion_joint_location TEXT, -- 신축이음위치
            UNIQUE(user_id, filename)
        )
    u  
    CREATE TABLE IF NOT EXISTS file_damage_details (
        id SERIAL PRIMARY KEY,
        user_id INTEGER REFERENCES users(id),
        username TEXT NOT NULL,
        filename VARCHAR(255) NOT NULL,
        component_name TEXT NOT NULL,       -- 부재명
        damage_description TEXT NOT NULL,   -- 손상내용
        unit TEXT,
        damage_quantity NUMERIC,
        repair_quantity NUMERIC,
        count INTEGER,
        repair_method TEXT,
        priority TEXT,
        unit_price NUMERIC,
        estimated_cost NUMERIC,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        UNIQUE(user_id, filename, component_name, damage_description)
    );
    ao  
    CREATE TABLE IF NOT EXISTS evaluation_results (
        id SERIAL PRIMARY KEY,
        user_id INTEGER REFERENCES users(id),
        filename VARCHAR(255) NOT NULL,
        evaluation_data JSONB NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        UNIQUE(user_id, filename)
    );
    u  
    CREATE TABLE IF NOT EXISTS span_damage (
        id SERIAL PRIMARY KEY,
        filename VARCHAR(255) NOT NULL,
        user_id VARCHAR(255) NOT NULL,
        username VARCHAR(255) ,
        email VARCHAR(255)  ,
        span_id VARCHAR(255) NOT NULL,
        type VARCHAR(255) NOT NULL,           -- 예: 'slab'
        damage_type VARCHAR(255) NOT NULL,    -- 예: '균열'
        damage_quantity NUMERIC,             -- 손상물량
        count INTEGER,                       -- 개수
        unit VARCHAR(255),                    -- 단위 (예: 'm')
        inspection_area NUMERIC,             -- 점검면적
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );
    a&  
    CREATE TABLE IF NOT EXISTS public.damage_meta
    (
        id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1000000 MINVALUE 1000000 MAXVALUE 2147483647 CACHE 1 ),
        category character varying(50) COLLATE pg_catalog."default",
        keyword character varying(50) COLLATE pg_catalog."default",
        description text COLLATE pg_catalog."default",
        parent_id integer,
        use_yn character(1) COLLATE pg_catalog."default" DEFAULT 'Y'::bpchar,
        CONSTRAINT damage_meta_pkey PRIMARY KEY (id)
    )
    a  
    CREATE TABLE IF NOT EXISTS public.meta_keyword
    (
        id integer NOT NULL DEFAULT nextval('meta_keyword_id_seq'::regclass),
        keyword character varying(256) COLLATE pg_catalog."default",
        use_yn character(1) COLLATE pg_catalog."default" DEFAULT 'Y'::bpchar,
        source text COLLATE pg_catalog."default",
        meta_id integer,
        CONSTRAINT meta_keyword_pkey PRIMARY KEY (id)
    )
    z3SELECT COUNT(*) FROM users WHERE username = 'admin'r   admin123AINSERT INTO users (username, password, email) VALUES (%s, %s, %s)adminzadmin@example.com)r   cursorexecutefetchoner"   commitclose)conncurhashed_passwordr/   r/   r0   init_db   s(   








r]   c                  C   s   t  } |  }|d dd | D }ddddddd}| D ]$\}}||vr>|d	| d
|  td| d q"td| d q"|   |  |   d S )Nv
        SELECT column_name 
        FROM information_schema.columns 
        WHERE table_name = 'uploaded_files'
    c                 S      g | ]}|d  qS r   r/   .0rowr/   r/   r0   
<listcomp>"      z)check_and_add_columns.<locals>.<listcomp>zVARCHAR(255)NUMERICINTEGERTEXT)bridge_namelengthwidthstructure_type
span_countexpansion_joint_locationz&ALTER TABLE uploaded_files ADD COLUMN  u   컬럼 'u   '이 추가되었습니다.u7   '은 이미 존재합니다. 추가를 건너뜁니다.)r   rU   rV   fetchallitemsprintrX   rY   )rZ   r[   existing_columnscolumns_to_addcolumncolumn_typer/   r/   r0   check_and_add_columns  s&   

rw   c                 C   s   dd t d| D S )Nc                 S   s$   g | ]}|  rt|n| qS r/   )isdigitintlower)rb   textr/   r/   r0   rd   ;  s    z$natural_sort_key.<locals>.<listcomp>z([0-9]+))resplit)sr/   r/   r0   natural_sort_key:  s   
r   ~uploadsUPLOAD_FOLDERc                   @   s$   e Zd Zdd Zdd Zdd ZdS )BridgeEvaluationc                 C   s   i | _ i | _d S N)bridge_dataevaluation_results)selfr/   r/   r0   __init__H  s   
zBridgeEvaluation.__init__c                 C   sB   || d }|| d }|d d }||d  d }||||dS )Nr;   皙?rP   rJ   rQ      )girder_areacrossbeam_areaabutment_area	pier_arear/   )r   rj   rk   rm   r   r   r   r   r/   r/   r0   calculate_areasL  s   z BridgeEvaluation.calculate_areasc                 C   s^   t |d t|d  }|dkrd}n|dkrd}n|dkr!d}n	|dkr(d	}nd
}||dS )Nweightsgffffff?Eg?D皙?C      ?BA)defect_scoregrade)sumlen)r   datar   r   r/   r/   r0   evaluate_conditionZ  s   z#BridgeEvaluation.evaluate_conditionN)__name__
__module____qualname__r   r   r   r/   r/   r/   r0   r   G  s    r   c                    s    fdd} j |_ |S )Nc                     s"   dt vr
ttdS  | i |S Nusernamelogin)r
   r   r	   )argskwargsfr/   r0   decorated_functiont  s   z*login_required.<locals>.decorated_function)r   )r   r   r/   r   r0   login_requireds  s   r   z/loginGETPOST)methodsc               
   C   s:  t jdkrt jd } t jd }t }| }zyzB|d| f | }|rSt|d |rS|d td< |d td< |d	 td
< t	dd t
tdW W |  |  S t	dd W n tyu } zt	dt| d W Y d }~nd }~ww W |  |  tdS W |  |  tdS |  |  w tdS )Nr   r   password'SELECT * FROM users WHERE username = %srP   r   user_idrQ   rO   emailu   로그인 성공!successindexu;   아이디 또는 비밀번호가 올바르지 않습니다.erroru,   로그인 중 오류가 발생했습니다: z
login.html)r   methodformr   rU   rV   rW   r#   r
   r   r   r	   rY   	Exceptionstrr   )r   r   rZ   r[   userr8   r/   r/   r0   r   {  s@   




 
r   z/logoutc                   C   s   t dd  ttdS r   )r
   popr   r	   r/   r/   r/   r0   logout  s   r   z/signupc               
   C   s  t jdkrt jd } t jd }t jd }t jd }||kr'td ttdS td|s7td	 ttdS t }|	 }zzc|
d
| f | r_td ttdW W |  |  S |
d|f | r~td ttdW W |  |  S t|}|
d| ||f |  td ttdW W |  |  S  ty } ztd ttdW  Y d }~W |  |  S d }~ww |  |  w tdS )Nr   r   r   r   confirm_passwordzPasswords do not matchsignupz[^@]+@[^@]+\.[^@]+zInvalid email formatr   zUsername already existsz$SELECT * FROM users WHERE email = %szEmail already existsrS   z+Account created successfully! Please login.r   z$An error occurred. Please try again.zsignup.html)r   r   r   r   r   r	   r|   matchr   rU   rV   rW   rY   r"   rX   r   r   )r   r   r   r   rZ   r[   r\   r8   r/   r/   r0   r     s\   









r   /c               
      s  t jdkrdt jv rt jd } | jdkrtdd tt jS | r| jdrzGt	| }t
|}t }| }|jddd	}t jd
d}|dtd t| j| j||f |  |  |  tdd ttdW S  ty } ztdt| d tt jW  Y d }~S d }~ww tdd tt jS t }| }|d dd | D  |dd  dtd f  fdd| D }|  |  td|dS )Nr   file u'   파일이 선택되지 않았습니다.r   z.xlsxrecordsF)orientforce_asciiri   ac  
                    INSERT INTO uploaded_files 
                    (user_id, filename, original_filename, file_data, bridge_name) 
                    VALUES (%s, %s, %s, %s, %s)
                    ON CONFLICT (user_id, filename) 
                    DO UPDATE SET file_data = EXCLUDED.file_data, bridge_name = EXCLUDED.bridge_name
                    r   u3   파일이 성공적으로 업로드되었습니다.r   r   u0   파일 처리 중 오류가 발생했습니다: u7   올바른 Excel 파일(.xlsx)을 업로드해주세요.r^   c                 S   r_   r`   r/   ra   r/   r/   r0   rd     re   zindex.<locals>.<listcomp>z
        SELECT , za 
        FROM uploaded_files 
        WHERE user_id = %s 
        ORDER BY upload_date DESC
    c                    s   g | ]	}t t |qS r/   )dictzipra   columnsr/   r0   rd   "      z
files.html)files)r   r   r   filenamer   r   urlendswithr@   
read_excelr   r   rU   to_jsonr   getrV   r
   r$   rX   rY   r	   r   r   rp   joinr   )r   dfrZ   r[   	file_datari   r8   r   r/   r   r0   r     s`   










r   c                    s  t    }t fdddD }t fdddD } ddddd	dd
ddddd td r>dS td rFdS d v ritd }|rgt|d}|dkr_dS |dkredS dS dS |r}td|rsdS td|r{dS dS |rtd|rdS td|rdS dS td  rtd! rdS td" rdS td# rd$S td% rd&S td' rdS td( rdS td) rdS td* rd+S td, rd-S dS ).Nc                 3       | ]}| v V  qd S r   r/   rb   keyworddescr/   r0   	<genexpr>.      z"classify_repair.<locals>.<genexpr>)u   교면포장u   포장u   신축이음u   포장균열c                 3   r   r   r/   r   r   r/   r0   r   /  r   )#u   난간   연석u   방호울타리u	   방호벽u	   방음벽u	   방음판u	   차광망u   낙석방지망u   낙석방지책u	   중분대u   중앙분리대u	   경계석u   가드레일u   안전난간u   보행자난간u   차량방호울타리u   중앙분리난간u   측면방호울타리u   원형난간u   사각난간u   방호시설u   안전시설u   교량난간u   보도난간u   차도난간u   콘크리트난간u   강재난간u   알루미늄난간u   스테인리스난간u   복합난간u   투명난간u   유리난간u   펜스u	   울타리u   방벽	   보수부r      받침콘크리트   받침몰탈   받침	   전단키r   uQ   균열\(0\.3mm\)|균열\(0\.3mm이상\)|균열\(0\.3㎜\)|균열\(0\.3㎜이상\)u   주입보수u]   균열\(0\.3mm미만\)|균열\(0\.2mm이하\)|균열\(0\.2㎜이하\)|균열\(0\.3㎜미만\)u   표면처리   균열z(\d+(\.\d+)?)mmrQ   r7   u   충진보수r;   u   균열|망상균열u   실링보수   파손|패임|들뜸u   부분재포장   주의관찰   파손|탈락|변형u	   재설치   부식|도장u   도장보수   신축이음|이음장치u	   후타재'   본체파손|본체탈락|탈락|파손   철근노출u   단면보수(방청)@   박리|들뜸|박락|재료분리|파손|침식|세굴|층분리u   단면보수K   백태|누수흔적|오염|망상균열|흔적|균열부백태|누수오염4   부식|도장박리|도장박락|도장|플레이트   탈락|망실|미설치   막힘|퇴적|적치u   청소   배수관탈락|길이부족u   배수관 재설치)r   anyreplacer|   searchrB   group)r   original_descis_pavement
is_railingr   
crack_sizer/   r   r0   classify_repair*  sd   
r   c                 C   s   t | } td| rdS td| rtd| rdS td| r(td| r(dS td| r8td	| r6dS dS td
| r@dS td| rHdS dS )Nu   포장균열|포장망상균열3r   r   2   교면포장|포장r   u  난간|연석|방호울타리|방호벽|방음벽|방음판|차광망|낙석방지망|낙석방지책|중분대|중앙분리대|경계석|가드레일|안전난간|보행자난간|차량방호울타리|중앙분리난간|측면방호울타리|원형난간|사각난간|방호시설|안전시설|교량난간|보도난간|차도난간|콘크리트난간|강재난간|알루미늄난간|스테인리스난간|복합난간|투명난간|유리난간|펜스|울타리|방벽r   u;   균열\(0\.3mm\)|균열\(0\.3mm이상\)|철근노출|세굴1u   주의관찰|청소)r   r|   r   r   r/   r/   r0   match_priorityr  s$   r   c                 C   s  t | } t| } | dddddddddddd}td| r@td	| r0d
S td| r8dS td| r@dS td| rNtd| rNdS td| rftd| r\dS td| rddS dS td|rndS td|rvdS td|r~dS td|rdS td|rdS td	|rdS td|rdS td |rdS td!|rdS td"|rdS td#|rd$S td%|rd&S td'|rd(S dS ))Nr   r   r   r   r   r   r   r   r   i N  u   파손|패임i@  u   들뜸|탈락r   r   i u   난간|연석|방호울타리|방호벽|방음벽|방음판|차광망|낙석방지망|낙석방지책|중분대|중앙분리대|경계석r   iI r   i  i0u  u   균열\(0\.3mm이상\)iS u   균열\(0\.3mm\)u   균열\(0\.3mm미만\)i0  u   균열\(0\.2mm\)   백태r   i r   r   r   r   i r   i*  r   i )r   normalize_damager   r|   r   )r   checkr/   r/   r0   match_unit_price  sB   
r   c                    sj    d dv r
 d S d d v sd d v r,t  fddd	D r,t d d
 d dS t d d
 dS )N   단위)   개소eaEA   손상물량r      손상내용u	   균열부c                 3   s    | ]	}| d  v V  qdS )r  Nr/   rb   xrc   r/   r0   r         zadjust.<locals>.<genexpr>)z0.2u   0.3㎜미만u   0.3mm미만g333333?r   rP   )r   roundr	  r/   r	  r0   adjust  s
   .r  r   c                 C   sl  t  }| }d }d}d}|dtd |f | d }|dkr)t| d ntd | d ur|  }|d jjdd	d
|d jjdd	d
B }	d|j	|	df< t
|d  }
||d |
 }|g dddg   }|d t|d< |d t|d< |d t|d< |jtddd|d< |d |d  |d< |dkr|dtd |f | }g d}|rtj||d}n
|  |  dS |  |  |d7 }|d7 }|d7 }|d 7 }|d!7 }|d d"d# |d$< |d$jd$dd}| D ]N\}}|d%|d  d&|d  d&|d'  d&|d d(d&|d d(d&t|d  d)| d*|d  d+| d*|d  d,| d*t|d  d-t|d d.d/7 }q |d07 }||d d1k  }|jg d2d	d3d4d4d5d# d6d4d7 }|d d8d# |d$< |d$jd$dd}|d97 }|d:7 }d}| D ]A\}}|d%|d  d&|d  d&|d  d&|d  d&|d d(d&t|d  d;t|d d.d;t|d d.d/7 }||d 7 }q|d<t|d.d=7 }|d>7 }|d?7 }|d@7 }d}|dd   D ]+\}}t|}t|dA }|| }||7 }|dB| dC|d.dC|d.dC|d.dD	7 }q|dE|d.dF7 }||dfS )GNr   za
    SELECT COUNT(*) 
    FROM file_damage_details 
    WHERE user_id = %s AND filename = %s
    r   r   u1   개의 보수물량 데이터가 존재합니다.uI   해당 사용자 파일에 대한 데이터가 존재하지 않습니다.r  r   Fnar      교량받침	   부재명r  r  r  r  r     보수방안   우선순위   단가rQ   axisrP      보수물량   개략공사비a	  
            SELECT component_name, damage_description, repair_method, priority, damage_quantity,
                repair_quantity, count, unit_price, estimated_cost, unit
            FROM file_damage_details
            WHERE user_id = %s AND filename = %s
        )
r  r  r  r  r  r  r  r  r  r  r   )r   r   u0   <p>📭 저장된 데이터가 없습니다.</p>uO   <div style="text-align:right"><span style="padding:10px">할증율 : 20%</span>u_   <input type="button" class="btn btn-secondary" value="저장" onclick="saveCostTable()"/></div>zR<div class="table-container repair-table"><table class="table-striped"><thead><tr>uo   <th>부재명</th><th>손상내용</th><th>단위</th><th>손상물량</th><th>보수물량</th><th>개소</th>ue   <th>보수방안</th><th>우선순위</th><th>단가</th><th>개략공사비</th></tr></thead><tbody>c                 S   "   t | tv rtt | S ttS r   r   r   r   r   r  r/   r/   r0   <lambda>     " z(generate_repair_tables.<locals>.<lambda>   부재명_순서z
        <tr>
            <td>z</td>
            <td>r  .2fz`</td>
            <td><input type="text" class="form-control repair-method" name="repair_method_z	" value="zX"></td>
            <td><input type="text" class="form-control priority" name="priority_z^"></td>
            <td><input type="number" class="form-control unit-price" name="unit_price_zM" step="1"></td>
            <td class="total-cost" style="text-align:right">,z</td>
        </tr>
        z</tbody></table></div>r   r  r  r  dropnar   c                 S      d tt| S Nr   r   sortedsetr  r/   r/   r0   r  '      firstr  r  r  r  r  c                 S   r  r   r  r  r/   r/   r0   r  ,  r  zP<div class="table-container cost-table"><table class="table-striped"><thead><tr>u   <th>부재명</th><th>손상내용</th><th>보수방안</th><th>우선순위</th><th>보수물량</th><th>개소</th><th>단가</th><th>개략공사비</th></tr></thead><tbody>z*</td>
            <td class="cost-amount">u   
        <tr class="table-primary">
            <td colspan="7" class="text-end"><strong>총계</strong></td>
            <td class="cost-amount"><strong id="costTotal_text">z@</strong></td>
        </tr>
        </tbody></table></div>
    u6   <h4 class="mt-4">우선순위별 공사비 요약</h4>z<<table id="cost_sum" class="table table-striped"><thead><tr>un   <th>우선순위</th><th>순공사비</th><th>제경비(50%)</th><th>전체 공사비</th></tr></thead><tbody>r9   <tr><td>z#</td><td style="text-align:right;">
</td></tr>u   
        <tr class="table-primary">
            <td><strong>총괄 개략공사비</strong></td>
            <td></td><td></td>
            <td style="text-align:right;"><strong>z:</strong></td>
        </tr>
        </tbody></table>
    ) r   rU   rV   r
   rW   rr   copyr   containslocr   uniqueisingroupbyr   reset_indexapplyr   r   r   r  r  rp   r@   	DataFramerY   sort_valuesdropiterrowsry   aggrq   )r   r   rZ   r[   repairrepair_html	cost_htmlcount	df_repairbearing_damage_maskunique_componentsrowsr   idxrc   filteredresult
total_cost_	total_sumprioamountsoftindirecttotalr/   r/   r0   generate_repair_tables  s   


		






	,
rN  z/getDatac                  C   s.   t jdkrt jd } tt| \}}}tddS )Nr   r   
static/css	style.css)r   r   r   rN  r   r   )r   r<  r=  rG  r/   r/   r0   getData6  s   


rQ  z
/style.cssc                   C   r,   )NrO  rP  r.   r/   r/   r/   r0   style@  r2   rR  z/js/<path:filename>c                 C   s
   t d| S )Nz	static/jsr.   )r   r/   r/   r0   serve_jsD  r2   rS  z/download/<table_type>c           .   
      s  t jdsdS td}| dkrt }|dd |dD ]a\}}|jd| dd	 tt	|d
 }d
|}| d| d}|| t|d  td}|jddt|d  d d}	d|	_|	jD ]7}
|
jD ]1}td|jd j_td|jd j_tj|jd _tj|_|jD ]}|jD ]}td|j_qqqmqh|	jd j}d|d _ d
|d _ d|d _ t!|D ]\}}||d|d   _ ||d|d   _ qd|d _ d|d _ |	jd j}d|d _ d|d _ d|d _ t"t|D ]}d|d|d   _ d|d|d   _ qd|d _ d|d _ |d
}|D ]\}}|	# j}t$||d _ t$||d _ t$|d j%d |d _ d}d}t!|D ]U\}}||d |k }|j&st'|d j%d }t(|d j%d }|d|d|d   _ t$||d|d   _ ||7 }||7 }qFd|d|d   _ d|d|d   _ qF|d|d _ t$||d _ |D ]4}td|jd j_td|jd j_tj|jd _tj|_|jD ]}|jD ]	}td|j_qӐqΐqq|jddd	 |D ]}t)|}|jd | d!| d"d#d$ zlt*| d% v r@t+ fd&d'd(D s@t,-d) r?d%t.v r?t/0t.d% d}|D ]}|1d*|1d+|}|| q,n+ t.v rktt.  dkrkt/0t.  d}|D ]}|1d*|1d+|}|| qXW n
 t2yv   Y nw |d, q|3  q n| d-kr|g d.ddg 4 5 }|d 6d|d< t }|d/d |jdd0d}	d|	_|	jD ];}
|
jD ]4}td|jd j_td|jd j_tj|jd _tj|_|jD ]}|jD ]	}td|j_qqߐqq|	jd j}d|d _ d
|d _ d|d _ d|d _ d|d _ |7 D ]p\} }
|	# j}t$|
d |d _ t$|
d
 |d _ t$|
d |d _ t'|
d d|d _ t$|
d |d _ |D ]4}td|jd j_td|jd j_tj|jd _tj|_|jD ]}|jD ]	}td|j_qxqsqQqn| d1kr|g d.ddg 4 5 }!|!d
 8t)|!d2< |!d
 8t9|!d3< |!d
 8t:|!d4< |!j8t;dd5|!d6< |!d6 6d|!d6< |!d6 |!d4  |!d7< t }|d8d |jddd}	d|	_|	jD ];}
|
jD ]4}td|jd j_td|jd j_tj|jd _tj|_|jD ]}|jD ]	}td|j_qqqq|	jd j}d|d _ d
|d _ d|d _ d|d _ d6|d _ d|d0 _ d2|d9 _ d3|d: _ d4|d; _ |!7 D ]\} }
|	# j}t$|
d |d _ t$|
d
 |d _ t$|
d |d _ t'|
d d|d _ t'|
d6 d|d _ t$|
d |d0 _ t$|
d2 |d9 _ t$|
d3 |d: _ t(|
d4 d<|d; _ |D ]4}td|jd j_td|jd j_tj|jd _tj|_|jD ]}|jD ]	}td|j_qqqq^n| d=kr|g d.ddg 4 5 }!|!d
 8t)|!d2< |!d
 8t9|!d3< |!d
 8t:|!d4< |!j8t;dd5|!d6< |!d6 6d|!d6< |!d6 |!d4  |!d7< |!|!d2 d>k < }"|"jg d?d@dA=dBdBdCdD dEdBdF5 }#t }|dGd |jdd9d}	d|	_|	jD ];}
|
jD ]4}td|jd j_td|jd j_tj|jd _tj|_|jD ]}|jD ]	}td|j_qqqqz|	jd j}d|d _ d2|d _ d3|d _ d6|d _ d|d _ d7|d0 _ |#7 D ]{\} }
|	# j}t$|
d |d _ t$|
d2 |d _ t$|
d3 |d _ t'|
d6 d|d _ t$|
d |d _ t(|
d7 d<|d0 _ |D ]4}td|jd j_td|jd j_tj|jd _tj|_|jD ]}|jD ]	}td|j_qJqEq#q|jdHdd	 |jddd}$d|$_|$jD ];}
|
jD ]4}td|jd j_td|jd j_tj|jd _tj|_|jD ]}|jD ]	}td|j_qqqsqn|$jd j}d3|d _ dI|d _ dJ|d _ dK|d _ |#d3d7 4 }%d}&|%> D ]o\}'}(t(|(})t(|)dL }*|)|* }+|&|+7 }&|$# j}t$|'|d _ |)d<|d _ |*d<|d _ |+d<|d _ |D ]4}td|jd j_td|jd j_tj|jd _tj|_|jD ]}|jD ]	}td|j_q3q.qq|$# j}dM|d _ d|d _ d|d _ |&d<|d _ |D ]4}td|jd j_td|jd j_tj|jd _tj|_|jD ]}|jD ]	}td|j_qqq`t?@ },|A|, |,Bd | dkrdN}-n| d-krdO}-n| d1krdP}-n| d=krdQ}-n|  dR}-tC|,dS|-dTdUS )VNz	cache.csvu   데이터가 없습니다.detailu   부재별 집계표r   r  u   부재명: rQ   )levelr  r   u=   에 대한 외관조사결과에서 조사된 바와 같이, u"    등의 손상이 조사되었다.   부재위치keyrP   rO   )rB  colsz
Table Grid	   r  r   u   합계r   r  r  r  r5   u!   손상별 원인 및 대책방안u   손상내용: u    (보수방안: )z	Heading 3)rR  r   c                 3   r   r   r/   r  normalized_dmgr/   r0   r     r   z!download_table.<locals>.<genexpr>)r      누수u   부u"   균열\(?[a-zA-Z]*=?[\d.]+(mm|㎜)z{name}u   {보수방안}z2--------------------------------------------------overallr  u   외관조사 총괄표rJ   r;  r  r  r  r  r  r  u   보수물량표         r   costr   r!  Fr"  r   c                 S   r$  r%  r&  r  r/   r/   r0   r  [  r)  z download_table.<locals>.<lambda>r*  r+  u   개략공사비표u    우선순위별 공사비 요약u   순공사비u   제경비(50%)u   전체 공사비r9   u   총괄 개략공사비u   부재별_집계표.docxu   외관조사_총괄표.docxu   보수물량표.docxu   개략공사비표.docxu	   _표.docxTzGapplication/vnd.openxmlformats-officedocument.wordprocessingml.document)as_attachmentdownload_namemimetype)Dospathexistsr@   read_csvr   add_headingr3  r'  r(  r   add_paragraphr1  r   	add_tabler   rR  rB  cellsr   
paragraphsparagraph_formatspace_beforespace_afterr   CENTER	alignmentr   vertical_alignmentrunsfontsizer{   	enumeraterangeadd_rowr   ilocemptyrB   ry   r   r   r   r|   r   r   randomsampler   r   add_page_breakr   r4  r  r9  r5  r   r   r  r.  r:  rq   ioBytesIOsaveseekr   ).
table_typer   docnamer   unique_damagesdamage_textsummary	positionstablerc   cell	paragraphrunheader_cellsipossubheader_cellsdamage_groupsdamagedamage_group	row_cellstotal_damagetotal_countpos_datar>  dmgrepair_methodselected_solutionssolutionformatted_solutionra  rG  r;  rD  rE  summary_tablegroup_by_priorityrH  rI  rJ  rK  rL  rM  
doc_streamr   r/   r^  r0   download_tableH  s  






















"

  





































































r  z/update_repairc                  C   s  zt  } dd | D }i }|D ]}|d }||vrg ||< || | qd}|d7 }|d7 }d}| D ]p\}}i }|D ],}|d |d	 f}	|	|vr^|d |d |d	 |d
 dd||	< ||	 d  |d 7  < q>| D ]6\}	}t|d
 t|d  }
||
7 }|d|d  d|d  d|d	  d|d
  d|d  d|
dd7 }qoq6|d7 }td|iW S  ty } ztdt|idfW  Y d }~S d }~ww )Nc                 S   s   g | ]
}|d  dkr|qS )repairMethodr   r/   )rb   itemr/   r/   r0   rd         z!update_repair.<locals>.<listcomp>	componentz$<table class="table table-bordered">u   <thead><tr><th>부재명</th><th>보수방안</th><th>우선순위</th><th>단가</th><th>물량</th><th>공사비</th></tr></thead><tbody>r   r  priority	unitPrice)r  r  r  r  quantityr  r,  	</td><td>z,.0fr-  z</tbody></table>
cost_tabler     )r   get_jsonappendrq   rB   r   r   r   )repair_datafiltered_datacomponent_groupsr  r  cost_table_htmlrF  rq   grouped_itemsrX  re  r8   r/   r/   r0   update_repair  sL   
F r  z/pivot_detailc               
   C   l   zt  } td }t|| d | d }td|iW S  ty5 } ztdt|idfW  Y d }~S d }~ww Ncurrent_filenamepivotrT  detail_htmlr   r  r   r  r
   pivot_detail_viewr   r   r   r   r   r  r8   r/   r/   r0   pivot_detail!  s    r  c                 C   sn   t | trdd |  D S t | ttfrdd | D S t| dr%|  S t| dr.|  S t	| r5dS | S )u>   numpy/pandas 타입을 Python 네이티브 타입으로 변환c                 S   s   i | ]	\}}|t |qS r/   convert_numpy_types)rb   kvr/   r/   r0   
<dictcomp>5  r   z'convert_numpy_types.<locals>.<dictcomp>c                 S   s   g | ]}t |qS r/   r  )rb   r  r/   r/   r0   rd   7  re   z'convert_numpy_types.<locals>.<listcomp>r  tolistN)

isinstancer   rq   listtuplehasattrr  r  r@   rA   )objr/   r/   r0   r  2  s   



r  z/crack_subdivisionc               
   C   r  r  r  r  r/   r/   r0   crack_subdivisionA  s    r  c              
   C   sd  zt | d  }d}|D ]s}| | d |k }|jrq|ddgddg   }zt|d  td}W n tyI   t|d  }Y nw |d	| d
7 }|d7 }|d7 }|d7 }|d7 }|D ]
}|d| d7 }qd|d7 }|d7 }|d7 }|D ]}|d7 }q}|d7 }|d7 }|d7 }|	 D ]\}}	|	d }
|	d }d|
v rt
||
|}| D ]k\}}|d dks|d dkr|d7 }|d| d| d7 }|D ]4}|d |i d d}|d |i d!d}|dks|dkr|d|d"d| d7 }q|d#7 }q|d$|d d"d%|d  d&7 }|d7 }qq|d7 }|d|
 d| d7 }d}d}|D ];}||d |
k|d |k@  }|jsg|d  }|d  }|d|d"d| d7 }||7 }||7 }q1|d#7 }q1|d$|d"d%| d&7 }|d7 }q|d'7 }q|W S  ty } ztd(t|  dd)l}|  d*t| d+W  Y d)}~S d)}~ww ),uY   균열 세분화 뷰 생성 - 기존 테이블 형태 유지하면서 균열만 세분화r  r   r  r  r  r  rV  rW  <h4></h4><div class="table-container">#<table class="table table-striped"><thead><tr>$   <th>손상내용</th><th>단위</th><th colspan="2"></th>   <th colspan="2">합계</th></tr><tr><th></th><th></th>$   <th>손상물량</th><th>개소</th></tr></thead>r  r   total_quantityr   r  <tr><td>r  </td>r  r  r>  r  <td>-</td><td>-</td><td><strong></strong></td><td><strong></strong></td></tbody></table></div><br>u(   generate_crack_subdivision_view 오류: Nu=   <p>균열 세분화 처리 중 오류가 발생했습니다: </p>)r   r1  r  r3  r   r4  r'  r   	NameErrorr9  subdivide_crack_datarq   r   r   rr   r   	traceback	print_exc)r   rA  html_outputr  component_dfdamage_summaryr  r  rG  
damage_rowdamage_typeunitcrack_subdivisions
sub_damagesub_datapos_quantity	pos_countr  r  r  r  r>  r8   r  r/   r/   r0   generate_crack_subdivision_viewW  s   



 



r  c                 C   sL  ddi dddi dddi dddi dddi dd}| | d |k }|  D ]{\}}t|d }|dkr9d}n|dkr@d}n|d	krGd
}n	|dkrNd}nd}|d }	t|d }
t|d }|| d  |
7  < || d  |7  < |	|| d vrddd|| d |	< || d |	 d  |
7  < || d |	 d  |7  < q(|S )u*   균열 데이터를 세분화하여 리턴r   )r  r  r  )   균열(0.1mm)   균열(0.2mm)   균열(0.3mm)   균열(0.4mm)   균열(0.5mm이상)r  r=   r  r   r  r;   r  r   r  r  rV  r  r  r  r  r  )r  r>  r  r>  )r9  extract_crack_widthrB   ry   )r  original_damage_typer  r  
crack_datarG  rc   crack_widthcategorypositionr  r>  r/   r/   r0   r    s8   




	r  c              
   C   s   d| v sd| v r
dS d| v sd| v rdS g d}|D ]2}t || }|rLzd|v s-d	|v r7t|d
W   S t|dW   S  ttfyK   Y qw qdS )u#   손상내용에서 균열폭 추출u   균열(0.3mm미만)u   균열(0.3㏼미만)r   u   균열(0.3mm이상)u   균열(0.3㏼이상)r;   )u   (\d+(?:\.\d+)?)\s*(?:mm|㏼)z(\d+(?:\.\d+)?)\s*(?:m[mM])u   (パ|파)\s*(\d+(?:\.\d+)?)u   パu   파rP   rQ   g333333?)r|   r   rB   r   
ValueError
IndexError)damage_descriptionwidth_patternspatternr   r/   r/   r0   r    s"   
r  c              
   C   sd  zt | d  }d}|D ]}| | d |k }|jrq|ddgddg   }zt|d  td}W n tyH   t|d  }Y nw |d	| d
7 }|d7 }|d7 }|d7 }|d7 }|D ]
}|d| d7 }qc|d7 }|d7 }|d7 }|D ]}|d7 }q||d7 }|d7 }|d7 }|	 D ]j\}}	|	d }
|	d }|d7 }|d|
 d| d7 }d}d}|D ]8}||d |
k|d |k@  }|js|d  }|d  }|d|dd| d7 }||7 }||7 }q|d7 }q|d|dd| d 7 }|d7 }q|d!7 }q|W S  t
y1 } ztd"t|  dd#l}|  d$t| d%W  Y d#}~S d#}~ww )&u;   부재별 집계 테이블 생성 (기존 방식과 동일)r  r   r  r  r  r  rV  rW  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r   r  r  r  r  r  r  u(   generate_component_pivot_tables 오류: Nu6   <p>테이블 생성 중 오류가 발생했습니다: r  )r   r1  r  r3  r   r4  r'  r   r  r9  r   rr   r   r  r  )r   rA  r  r  r  r  r  r  rG  r  r  r  r  r  r  r  r>  r8   r  r/   r/   r0   generate_component_pivot_tables  sv   






r  z/condition_evaluationc               
   C   s  z^t d td} | stddidfW S t }| }|d| td f | }|s5tddid	fW S |d
 }t|t	rCt
|}t|}t|}|  |  t|}td|iW S  ty } zt dt	|  tddt	| idfW  Y d}~S d}~ww )u'   상태평가용 피봇 데이터 생성u   상태평가 요청 수신r  r   u)   파일 정보를 찾을 수 없습니다.  ISELECT file_data FROM uploaded_files WHERE filename = %s AND user_id = %sr   "   파일을 찾을 수 없습니다.  r   evaluation_htmlu    상태평가 처리 중 오류: u6   상태평가 처리 중 오류가 발생했습니다: r  N)rr   r
   r   r   r   rU   rV   rW   r  r   jsonloadsr@   r6  r   rY   r   r   )r   rZ   r[   rE  r   r   all_evaluations_htmlr8   r/   r/   r0   condition_evaluationa  s<   




&r  z	/evaluatec               
      s  t   g d} t fdd| D stddidfS zL d t d d	 d
 t d d	 d t d d	 d t d d	d} ddgd  ddgd |d}t|}t||dW S  ty } ztddt	| idfW  Y d }~S d }~ww )N)
bridgeNamerj   rk   structureType	spanCountslabType
girderTypecrossbeamTypepavementTypeslabArea
girderAreacrossbeamAreapavementAreac                 3   r   r   r/   )rb   fieldr   r/   r0   r     r   zevaluate.<locals>.<genexpr>r   u.   필수 입력 항목이 누락되었습니다.r  r  r  )typearear  r  r  r  r  r  )slabgirder	crossbeampavementr   rQ   rI   valuesr9   )r   r"  areas)r#  
evaluationu)   평가 중 오류가 발생했습니다: r  )
r   r  allr   rB   r   bridge_evalr   r   r   )required_fieldsr#  evaluation_dataresultsr8   r/   r  r0   evaluate  s>   





&r*  z/evaluation_formc                   C      t dS )u2   
    교량 상태평가 폼 페이지 제공
    zevaluation_form.htmlr   r/   r/   r/   r0   evaluation_form     r-  z/evaluation_tablec                   C   r+  )u4   
    새로운 상태평가표 페이지 제공
    zevaluation_table.htmlr,  r/   r/   r/   r0   evaluation_table  r.  r/  z/pricingc                   C   r+  )Nzpricing.htmlr,  r/   r/   r/   r0   pricing  s   r0  z/view_file/<filename>Fc                 C   s  t  }| }| td< zؐz|d| td f | }|s3td ttdW W |  |  S |d }t	|t
rAt|}t|}t|}t|d  }||d | }d}d	}	d}
d}d}t|  t| d
dd\}}	t|  | }|d j
jddd|d j
jdddB }d|j|df< |g dddg   }|d d|d< |d dd |d< |djddd}|
d7 }
|
|jdddd7 }
|
d7 }
t || \}}}||d dk  }g }t!|d   t"d!D ]}||d  |k }|d"  }||d j
jd#dd d$ # }||d j
jd#dd d  }||d j
jd%dd d  }||d j
jd&dd d  }||d j
jd'dd d  }t$|||||d(}|%|t|dt&|sj|nd)|dkrut|dnd)|dkrt|dnd)|dkrt|d*nd)|dkrt|dnd)|d+ qt'd,t(j)*d-d.||	|
||||d d/
W W |  |  S  t+y } ztd0t
|  ttdW  Y d }~W |  |  S d }~ww |  |  w )1Nr  r  r   r  r   r   r  r   aaaaaTF)r  rT  r  r   r  r   r  r  r  r  rP   c                 S   r  r   r  r  r/   r/   r0   r  =  r  zview_file.<locals>.<lambda>r  rQ   r  z<div class='table-container' >ztable table-striped)classesr   borderz</div>	   바닥판rV  rW     점검면적r   u   폭u   백태|누수u$   박락|파손|재료분리|층분리   철근부식)r  crack_ratio
leak_ratiosurface_damage_ratiorebar_corrosion_ratior5   rO   )   구분r5  u	   균열폭u	   균열율r      표면손상r6  u   등급index_re.htmltabrT  )	
active_tabr  detail_html_header_linkoverall_htmlr<  r=  r   slab_eval_tableerror_messageu9   파일을 불러오는 중 오류가 발생했습니다: ),r   rU   r
   rV   rW   r   r   r	   rY   r  r   r  r  r@   r6  r   r   r1  r2  rr   r  r.  r/  r0  r3  r   r4  r  r5  r7  r8  to_htmlrN  r'  r   maxevaluate_slab_conditionr  rA   r   r   r   r   r   )r   r  rZ   r[   rE  r   r   rA  r  r@  rA  r<  r=  
df_overallr@  ra  	eval_htmlslab_dfrB  r  subr  r  r7  r8  r9  rebar_ratior   r8   r/   r/   r0   	view_file  s   
d



     



rL  z/delete_file/<filename>c              
   C   s   t  }| }z<z|d| td f |  tdd W n ty9 } ztdt| d W Y d }~nd }~ww W |  |  n	|  |  w t	t
dS )Nz?DELETE FROM uploaded_files WHERE filename = %s AND user_id = %sr   u0   파일이 성공적으로 삭제되었습니다.r   u0   파일 삭제 중 오류가 발생했습니다: r   r   )r   rU   rV   r
   rX   r   r   r   rY   r   r	   )r   rZ   r[   r8   r/   r/   r0   delete_file|  s&   
 

rM  z/update_file_damage_detailsc                  C   sV  t  } td }tdd}tdd}zz]t }| }|d||f |  D ]+\}}|d||||d |d	 |d
 |d |d |d |d |d |d |d f q)|  t	d |\}}	}
t
d||	dW W |  |  S  ty } z#td| |  t
dt|ddfW  Y d }~W |  |  S d }~ww |  |  w )Nr   r   unknownr  unnamed_filezf
            DELETE FROM file_damage_details
            WHERE user_id = %s AND filename = %s
        a}  
                INSERT INTO file_damage_details (
                    user_id, username, filename,unit,
                    component_name, damage_description,
                    repair_method, priority,damage_quantity,
                    repair_quantity, count, unit_price, estimated_cost
                ) VALUES (%s, %s, %s,%s, %s, %s, %s, %s, %s,%s, %s, %s, %s)
            r  r  r  r   r  damage_quantityr  r>  r  	totalCostu   보수물량 저장 완료)messager<  r=  u   업데이트 오류:u   오류 발생)rR  r   r  )r   r  r
   r   r   rU   rV   rq   rX   rN  r   rY   r   rr   rollbackr   )r   r   r   r   rZ   r[   rX  rT  r<  cost_htmlxxxxrG  r8   r/   r/   r0   update_file_damage_details  sT   

"

rU  c           "         sv  ddgddddgdddddd	gidg d
iddgid}|rp|  D ]J\}}||v rot|tr?d|v r?|d || d< q%t|tro|  D ]&\}}||| v rnd|v r`|d || | d< d|v rn|d || | d< qHq%ddddddddddd
}dd | D }|s|S tdd |D }	|	|d< d}
d}d}d}d}d}d}|D ]}|d  t|d }t|d }t fdd|d d d D rd}t| }|rt|d}|d d d }|| }t	|
|}
||7 }n9t fdd|d d d D r(d}t| }|r(t|d}|d d d }|| }t	||}||7 }t
 fd d|d d D r@||7 }||7 }t
 fd!d|d" d D rT||7 }t
 fd#dd$D rid% vri||7 }q|
|d&< |	dkry||	 d' nd|d(< |dkrd)n||d*< |	dkr||	 d' nd|d+< |	dkr||	 d' nd|d,< |	dkr||	 d' nd|d-< |	dkr||	 d' nd|d.< t	|
|}t	|d( |d+ }|d, }|d- }|d. }d}|d/krd0}n|d1krd2}n|d3krd4}n|d5krd6}|d7kr
t	|d0}n |d8krt	|d2}n|d9kr t	|d4}n
|dkr*t	|d6}|d8kr5t	|d4}n
|dkr?t	|d6}|d8krJt	|d2}n|d9krUt	|d4}n
|dkr_t	|d6}|d9krjt	|d2}n
|dkrtt	|d4}||d:< t }| } z6z| d;|f |  W n ty }! ztd<|!  W Y d=}!~!nd=}!~!ww W |  |S W |  |S |  w )>u%  
    부재별 집계표 데이터를 기반으로 콘크리트 바닥판 상태평가표 데이터를 생성합니다.
    
    Args:
        component_data (list): 부재별 집계표 데이터 리스트
        damage_mapping (dict): 손상 유형별 매핑 설정
            예시: {
                '균열': {
                    '1방향': {
                        'keywords': ['1방향', '균열'],
                        'length_factor': 0.25
                    },
                    '2방향': {
                        'keywords': ['2방향', '균열'],
                        'length_factor': 0.25
                    }
                },
                '누수': {
                    'keywords': ['누수', '백태']
                },
                '표면손상': {
                    'keywords': ['표면손상', '박락', '파손']
                },
                '철근부식': {
                    'keywords': ['철근부식']
                }
            }
        
    Returns:
        dict: 상태평가표 데이터
       균열부백태r   r   keywordslength_factor   망상균열   1방향   2방향rX  r`  r   u   박리u   박락u   파손r   r   r`  r<  r6  rY  u   콘크리트 바닥판r   r6   )
r;  r5     1방향 균열 최대폭   1방향 균열 균열율   2방향 균열 최대폭   2방향 균열 균열율   누수 및 백태 면적율   표면손상 면적율   철근부식 손상면적율   상태평가결과c                 S   s   g | ]
}d |d v r|qS )r4  u   부재구분r/   rb   compr/   r/   r0   rd   	  r  z,process_slab_damage_data.<locals>.<listcomp>c                 s   s    | ]	}t |d  V  qdS )   면적N)rB   rh  r/   r/   r0   r   %	  r
  z+process_slab_damage_data.<locals>.<genexpr>r5  r  rj  r  c                 3   r   r   r/   r   damage_descr/   r0   r   7	  r   r\  u    (\d+(?:\.\d+)?)\s*(?:mm|㎜|m|M)rQ   c                 3   r   r   r/   r   rk  r/   r0   r   B	  r   r]  c                 3   r   r   r/   r   rk  r/   r0   r   N	  r   c                 3   r   r   r/   r   rk  r/   r0   r   S	  r   r<  c                 3   r   r   r/   r   rk  r/   r0   r   W	  r   )r   r6  u   부식u   잡철근노출r`  d   ra  r5   rb  rc  rd  re  rf  r7   r8   r9   r:   r;   r<   r=   r>   rG   rI   rP   rg  u   
            UPDATE component_damage
            SET 상태평가결과 = ?
            WHERE 부재구분 LIKE '%바닥판%'
        z"Error updating grade in database: N)rq   r  r   r   rB   r%  r|   r   r   rE  r   r   rU   rV   rX   r   rr   rY   )"component_datadamage_mappingdefault_mappingr  mappingsub_typesub_mapping	slab_dataslab_components
total_areamax_crack_width_1dtotal_crack_length_1dmax_crack_width_2dtotal_crack_length_2d	leak_areasurface_damage_arearebar_corrosion_areari  r  rP  crack_patterncrack_matchr  rY  crack_lengthmax_crack_widthmax_crack_ratior8  r9  r:  r   rZ   rU   r8   r/   rk  r0   process_slab_damage_data  s  $
"
$
  "




















r  z	/index_rec               	   C   s   t  } |  }|dtd f | }g }|D ]'}||d |d |d |d d ur/|d nd|d d ur:|d ndd qd	d
gdddd
gddddddgidg diddgid}t||}|   td|dS )Nu
  
        SELECT 부재명, 손상내용, 단위, SUM(손상물량) as 총손상물량, SUM(개소) as 총개소
        FROM file_damage_details
        WHERE user_id = %s
        GROUP BY 부재명, 손상내용, 단위
        ORDER BY 부재명, 손상내용
    r   r   rQ   rP   rO   r   )r  r  r  r  r  rV  r   r   rW  rZ  r[  rX  r`  r   r^  r   r_  r=  )rt  )	r   rU   rV   r
   rp   r  r  rY   r   )rZ   rU   rn  processed_datarc   custom_damage_mappingrt  r/   r/   r0   index_re	  s@   

r  z/download/<filename>c              
   C      z3t  }| }|d | d }|  tj| \}}| d| | }tt	j
d | d|dW S  tyO } ztdt|  W Y d }~dS d }~ww )	N)   SELECT 교량명 FROM bridge_info LIMIT 1r   rG  r   Trf  rg  u'   파일 다운로드 중 오류 발생: )u5   파일 다운로드 중 오류가 발생했습니다.r  r   rU   rV   rW   rY   ri  rj  splitextr   appconfigr   rr   r   r   rZ   rU   ri   r  extnew_filenamer8   r/   r/   r0   download_file	  "   
r  z/download_report/<filename>c              
   C   r  )	Nr  r   rG  r   Tr  u*   보고서 다운로드 중 오류 발생: )u8   보고서 다운로드 중 오류가 발생했습니다.r  r  r  r/   r/   r0   download_report	  r  r  z/api/bridge_listc               
   C   s   zBt  } |  }|dtd f | }g d}g }|D ]}tt||}|| q|  |   t	d|  t
d|dW S  tyh } zt	dt|  t
dt|d	d
fW  Y d }~S d }~ww )Na2  
            SELECT "id","user_id","filename","original_filename","upload_date","file_data",
                   "bridge_name","length","width","structure_type","span_count","expansion_joint_location"
            FROM uploaded_files 
            WHERE user_id = %s 
            ORDER BY bridge_name
        r   )idr   r   original_filenameupload_dater   ri   rj   rk   rl   rm   rn   u   추출된 교량 목록: T)r   bridgeszError getting bridge list: Fr   r   r  )r   rU   rV   r
   rp   r   r   r  rY   rr   r   r   r   )rZ   r[   r)  r   r  rc   bridger8   r/   r/   r0   get_bridge_list
  s:   
r  z/api/bridge_data/<filename>c                 C   sJ  zt  }| }|d| td f | }|s"tddddfW S |d }i }|rt|tr4t	|}t
|}t|}|d  D ]m}||d |k }|g d	d
dg   }	g ||< |	 D ]K\}
}t|d t|d t|d t
|d
 rt|d
 ndt
|d rt|d ndt
|ddrt|ddndd}|| | qdqC|d pd|d rt|d nd|d rt|d nd|d pd|d rt|d nd|d pdt|d |r|ni d}|  |  td|dW S  ty$ } ztdt|  tdt|ddfW  Y d }~S d }~ww )Nz
            SELECT bridge_name, length, width, structure_type, span_count, 
                   expansion_joint_location, file_data
            FROM uploaded_files 
            WHERE filename = %s AND user_id = %s
        r   Fr  r  r	  rb  r  )rV  r  r  r  r  rV  r  r  r   r5  rm  )r  r  r  rP  r>  inspection_arear   rQ   rP   rO   r   rJ   )ri   rj   rk   rl   rm   rn   has_file_datadamage_dataTr   r   u$   교량 데이터 조회 중 오류: r  )r   rU   rV   r
   rW   r   r  r   r  r  r@   r6  r   r1  r3  r   r4  r9  notnullrB   ry   r   r  boolrY   r   rr   )r   rZ   r[   rE  file_data_jsonr  r   r  r  component_summaryrG  rc   damage_itemr   r8   r/   r/   r0   get_bridge_data8
  sx   






$





r  z$/api/bridge/<bridge_name>/componentsc              
   C   s   z!d|  dt  d}tj|sg W S t|}t|}|dW S  tyA } zt	dt  dt
|  g W  Y d }~S d }~ww )Nzdata/r   z_summary.csvr   zError loading z data: )component_typeri  rj  rk  r@   rl  r   to_dictr   rr   r   )ri   	file_pathr   r8   r/   r/   r0   get_bridge_components
  s   
r  z/api/generate_evaluation_datac               
   C   sB  zot  } | d}|stddddfW S t }| }|d|td f | }|s7tdddd	fW S |d
 }t	|t
rEt|}t|}|  |  g d}i }|D ]}	t||	}
|
rg|
||	< qZtd|dW S  ty } z%tdt
|  d
dl}|  tddt
| ddfW  Y d}~S d}~ww )u$   상태평가용 데이터 생성 APIr   Fu   파일명이 필요합니다.r  r  r  r   r  r	  r   )r  r  r   abutmentpier
foundationbearingexpansionJointr!  drainagerailingTr  u*   상태평가 데이터 생성 중 오류: Nu@   상태평가 데이터 생성 중 오류가 발생했습니다: r  )r   r  r   r   r   rU   rV   r
   rW   r  r   r  r  r@   r6  rY   "generate_component_evaluation_datar   rr   r  r  )r   r   rZ   r[   rE  r   r   component_typesr(  r  rn  r8   r  r/   r/   r0   generate_evaluation_data
  sj   






r  z/api/save_evaluation_resultc               
   C   s   zEt  } | d}| d}|r|stddddfW S t }| }|dtd |t	|f |
  |  |  td	d
dW S  tyn } ztdt|  tddt| ddfW  Y d}~S d}~ww )u   상태평가 결과 저장 APIr   r(  Fu*   필수 데이터가 누락되었습니다.r  r  a/  
            INSERT INTO evaluation_results (user_id, filename, evaluation_data)
            VALUES (%s, %s, %s)
            ON CONFLICT (user_id, filename)
            DO UPDATE SET 
                evaluation_data = EXCLUDED.evaluation_data,
                updated_at = CURRENT_TIMESTAMP
            r   Tu-   상태평가 결과가 저장되었습니다.)r   rR  u'   상태평가 결과 저장 중 오류: u)   저장 중 오류가 발생했습니다: r  N)r   r  r   r   r   rU   rV   r
   r  dumpsrX   rY   r   rr   r   )r   r   r(  rZ   r[   r8   r/   r/   r0   save_evaluation_result
  sD   


r  z/adminc                   C      t dddS )Nzadmin/admin.htmlmetar?  r,  r/   r/   r/   r0   
admin_home     r  z/admin/metac                   C   r  )Nzadmin/meta.htmlr  r  r,  r/   r/   r/   r0   
admin_meta  r  r  )r   N)Fr   )pandasr@   flaskr   r   r   r   r   r   r   r	   r
   r   ri  r|   r  difflibr   docxr   docx.sharedr   r   docx.enum.textr   docx.enum.tabler   r  numpynpcollectionsr   r  mathstatic.data.damage_solutionsr   utils.commonr   r   r   r   r   r   utils.evaluationutils.pivot_detail_viewutils.condition_evaluationr   r   utils.bridge_evaluationr   #utils.detailed_condition_evaluationr   r   utils.file_validationr    r!   psycopg2werkzeug.securityr"   r#   werkzeug.utilsr$   apir%   
flask_corsr&   dotenvr'   getenvr(   r)   r   r  register_blueprint
secret_keyrouter1   template_filterr3   rF   rK   rL   rN   r]   rw   r   rj  r   
expanduserr   rk  makedirsr  r   r&  r   r   r   r   r   r   r   r   r  rN  rQ  rR  rS  r  r  r  r  r  r  r  r  r  r  r*  r-  r/  r0  rL  rM  rU  r  r  r  r  r  r  r  r  r  r  r  r/   r/   r/   r0   <module>   s,   0 







 "

'


5UH)
	  w
	


   %
5
l-T[
3
t
9 ]
8'P
?/
