o
    'h׳                    @   sr  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*m+Z+ d dl,T d dl-T d dl.m/Z/m0Z0 d dl1m2Z2m3Z3 d dl4m5Z5m6Z6 d dl7m8Z8 d dl9m9Z9 d dl:Z:d dl;m<Z<m=Z= d dl>m?Z? d dl@mAZA d dlBmCZC d dlDmEZE d dlFmGZG d dlHmIZI d dlJmKZK d dlLmMZM d dlZeM  eNdZOeNdZPeeQZReKeR eRjSeAdd eRjSeCdd eRjSeEdd eRjSeGdd eRSeI deR_TeRUdd d! ZVeRWd"d#d" ZXeRWd$d%d$ ZYeRWd&d'd& ZZeRWd(d)d( Z[eRWd*d+d* Z\d,d- Z]d.d/ Z^d0d1 Z_ej`aej`bd2d3Zcej`decsteeec eceRjfd4< G d5d6 d6Zgeg Zhd7d8 ZieRjUd9d:d;gd<d=d> ZjeRUd?d@dA ZkeRjUdBd:d;gd<dCdD ZleRjUdEd:d;gd<eidFdG ZmddIdJZneRjUdKd:d;gd<dLdM ZoeRUdNdOdP ZpeRUdQdRdS ZqeRjUdTd;gd<dUdV ZreRjUdWd;gd<dXdY ZsdZd[ ZteRjUd\d;gd<eid]d^ Zud_d` Zvdadb Zwdcdd Zxdedf ZyeRjUdgd;gd<eidhdi ZzeRjUdjd;gd<dkdl Z{eRUdmeidndo Z|eRUdpeidqdr Z}eRUdsdtdu Z~eRUdveiddxdyZeRjUdzd;gd<eid{d| ZeRjUd}d;gd<eid~d ZdddZeRUddd ZeRUdeidd ZeRUdeidd ZeRUdeidd ZeRUdeidd ZeRUddd ZeRjUdd;gd<eidd ZeRjUdd;gd<eidd ZeRjUdd;gd<eidd ZeRjUdd;gd<eidd ZeRjUdd;gd<eidd ZeRjUdd;gd<eidd ZeRUddd ZeRUddd ZeRjUdd;gd<eidd Zdd Zdd 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get_circled_number)*)#generate_condition_evaluation_pivot"generate_condition_evaluation_html)"generate_all_component_evaluations"generate_component_evaluation_data)&generate_detailed_condition_evaluation generate_detailed_condition_html)validate_excel_file)datetime)generate_password_hashcheck_password_hash)secure_filename)api_bp)carbonation_test_bp)component_selection_bp)evaluation_weights_bp)download_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    r5   r5   9/home/skpark/git/infrasmart_work/infrasmart/app_backup.pyfavicon:      
r7   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valuer5   r5   r6   r9   @      evaluate_crack_ratioc                 C   r:   )Nr;   r<      r>      r@   
   rB      rD   rE   rI   r5   r5   r6   rL   O   rK   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 )	Nr;   r<   rM   r@   rO   rB   r   rD   rE   rI   r5   r5   r6   rQ   ^   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 )Nr;   r<   rM   r>   rO   r@   rP   rB   r   rD   rE   rI   r5   r5   r6   rR   k   rK   evaluate_rebarc                 C   rS   )Nr;   r<      r>      r@      rB   r   rD   rE   rI   r5   r5   r6   rT   z   rK   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, -- 신축이음위치
            markup_rate NUMERIC DEFAULT 20.0, -- 할증율 (기본값 20%)
            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_passwordr5   r5   r6   init_db   s(   








rc   c               
   C   s   t  } |  }|d dd | D }dddddddd	d
d	}| D ]$\}}||vrA|d| d|  td| d q%td| d q%d|v red|vre|d |d |d td |   |  |   d S )Nt
        SELECT column_name
        FROM information_schema.columns
        WHERE table_name = 'uploaded_files'
    c                 S      g | ]}|d  qS r   r5   .0rowr5   r5   r6   
<listcomp>+      z)check_and_add_columns.<locals>.<listcomp>zVARCHAR(255)NUMERICINTEGERTEXTzNUMERIC DEFAULT 20.0zNUMERIC DEFAULT 50.0zNUMERIC DEFAULT 0)	bridge_namelengthwidthstructure_type
span_countexpansion_joint_locationmarkup_rateoverhead_ratesubsidiary_costz&ALTER TABLE uploaded_files ADD COLUMN  u   컬럼 'u   '이 추가되었습니다.u7   '은 이미 존재합니다. 추가를 건너뜁니다.subsidiary_raterw   zGALTER TABLE uploaded_files ADD COLUMN subsidiary_cost NUMERIC DEFAULT 0z-UPDATE uploaded_files SET subsidiary_cost = 0z6ALTER TABLE uploaded_files DROP COLUMN subsidiary_rateu@   subsidiary_rate 컬럼을 subsidiary_cost로 변경했습니다.)r   r[   r\   fetchallitemsprintr^   r_   )r`   ra   existing_columnscolumns_to_addcolumncolumn_typer5   r5   r6   check_and_add_columns!  s6   



r   c                 C   s   dd t d| D S )Nc                 S   s$   g | ]}|  rt|n| qS r5   )isdigitintlower)rh   textr5   r5   r6   rj   Q  s    z$natural_sort_key.<locals>.<listcomp>z([0-9]+))resplit)sr5   r5   r6   natural_sort_keyP  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)selfr5   r5   r6   __init__^  s   
zBridgeEvaluation.__init__c                 C   sB   || d }|| d }|d d }||d  d }||||dS )NrA   皙?rV   rP   rW      )girder_areacrossbeam_areaabutment_area	pier_arear5   )r   rp   rq   rs   r   r   r   r   r5   r5   r6   calculate_areasb  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   r5   r5   r6   evaluate_conditionp  s   z#BridgeEvaluation.evaluate_conditionN)__name__
__module____qualname__r   r   r   r5   r5   r5   r6   r   ]  s    r   c                    s    fdd} j |_ |S )Nc                     s"   dt vr
ttdS  | i |S Nusername
auth.login)r
   r   r	   )argskwargsfr5   r6   decorated_function  s   z*login_required.<locals>.decorated_function)r   )r   r   r5   r   r6   login_required  s   r   z/loginGETPOST)methodsc               
   C   s<  t jdkrt jd } t jd }t }| }zzzC|d| | f | }|rTt|d |rT|d td< |d td< |d	 td
< t	dd t
tdW W |  |  S t	dd W n tyv } 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   passwordz3SELECT * FROM users WHERE username=%s or email = %srV   r   user_idrW   rU   emailu   로그인 성공!successindexu;   아이디 또는 비밀번호가 올바르지 않습니다.erroru,   로그인 중 오류가 발생했습니다: z
login.html)r   methodformr   r[   r\   r]   r%   r
   r   r   r	   r_   	Exceptionstrr   )r   r   r`   ra   userr>   r5   r5   r6   login  s@   




 
r   z/logoutc                   C   s   t dd  ttdS r   )r
   popr   r	   r5   r5   r5   r6   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_passwordu*   비밀번호가 일치하지 않습니다.signupz[^@]+@[^@]+\.[^@]+u.   이메일 형식이 올바르지 않습니다.z'SELECT * FROM users WHERE username = %su+   이미 사용 중인 사용자명입니다.z$SELECT * FROM users WHERE email = %su$   이미 등록된 이메일입니다.rY   uH   계정이 성공적으로 생성되었습니다! 로그인 해주세요.r   z$An error occurred. Please try again.zsignup.html)r   r   r   r   r   r	   r   matchr   r[   r\   r]   r_   r$   r^   r   r   )r   r   r   r   r`   ra   rb   r>   r5   r5   r6   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 '   파일이 선택되지 않았습니다.r   .xlsxrecordsF)orientforce_asciiro   a`  
                    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)을 업로드해주세요.rd   c                 S   re   rf   r5   rg   r5   r5   r6   rj   /  rk   zindex.<locals>.<listcomp>z
        SELECT , z^
        FROM uploaded_files
        WHERE user_id = %s
        ORDER BY upload_date DESC
    c                    s   g | ]	}t t |qS r5   )dictziprg   columnsr5   r6   rj   :      z
files.html)files)r   r   r   filenamer   r   urlendswithrF   
read_excelr   r   r[   to_jsonr   getr\   r
   r&   r^   r_   r	   r   r   rz   joinr   )r   dfr`   ra   	file_dataro   r>   r   r5   r   r6   r     s`   










r   r   c                   C   sb  t  }| }d }d}d}d| j| d jjddddf< t| d   |d|td f |	 }|r@|d	 d ur@t
|d	 nd
}|rP|d d urPt
|d nd}	|r`|d d ur`t
|d n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< tj|d dd|d < tj|d d|d!   dd|d< tj|d dd|d< |d |d  d	|d"< |d	kr|d#td |f | }g d$}|rxtj||d%}tj|d dd|d < tj|d ddd|d!   |d< tj|d dd|d< |d |d  d	|d"< n
|  |  d&S |  |  |d'| d(| d)7 }|d*7 }|d+7 }|d,7 }|d d-d. |d/< |d/jd/dd0}| D ]T\}}|d1|d  d2|d  d2|d3  d2|d d4d5|d  d4d6|d d4d2t|d  d7| d8|d  d9| d8|d  d:| d8t|d  d;t|d" d<d=7 }q|d>7 }||d d?k  }|jg d@ddA dBdBdCd. dDdBdE }|d dFd. |d/< |d/jd/dd0}|dG7 }|dH7 }d	}| D ]A\}}|d1|d  d2|d  d2|d  d2|d  d2|d d4d2t|d  dIt|d d<dIt|d" d<d=7 }||d" 7 }qV|dJt|d<dK7 }|dL7 }|dM7 }|dN7 }|dO7 }|dP|
dQdR7 }|dS7 }|dT7 }|dU7 }|dV7 }|dW7 }|dX7 }|dY7 }|dZ7 }|d[7 }|d\7 }|d]7 }|d^7 }|d_7 }|d`7 }|dP|	dadR7 }|db7 }|dc7 }|dd7 }|de7 }|df7 }|dg7 }|dV7 }|dW7 }|dX7 }|dh7 }|di7 }|dj7 }|dk7 }|dl7 }|dm|	 dn7 }d	}|	d! }|dd"  ! D ]7\}}t|}t|| }|| }||7 }t"t|}|do| dpt|d<dpt|d<dpt|d<dq	7 }qN|dr7 }|ds7 }|dtt|
d<du7 }|dv7 }||
 }|dwt|d<dx7 }||dfS )yNr      교량받침	   부재명u   받침장치Fnaz}
    SELECT markup_rate, overhead_rate, subsidiary_cost
    FROM uploaded_files
    WHERE filename = %s AND user_id = %s
    r   r         4@rW         I@rV   g        z_
    SELECT COUNT(*)
    FROM file_damage_details
    WHERE user_id = %s AND filename = %s
    u1   개의 보수물량 데이터가 존재합니다.uI   해당 사용자 파일에 대한 데이터가 존재하지 않습니다.   손상내용   받침	   전단키r   r      단위   손상물량   개소   보수방안   우선순위   단가r=   )axissave_overhead_rate   보수물량coerceerrorsu   보수물량_notAddd      개략공사비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>u   <div style="text-align:right; padding:10px;">
        <label style="width:100px" for="markup_rate">할증율 : </label>
        <input type="number" id="markup_rate" value="u   " min="0" max="100" step="0.1" style="width:80px; text-align:center;"/> %
        <input type="button" class="btn btn-secondary" value="할증율 저장" onclick="saveMarkupRate('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   xr5   r5   r6   <lambda>     " z(generate_repair_tables.<locals>.<lambda>   부재명_순서r   z
        <tr>
            <td>z</td>
            <td>r   .2fz</td>
            <td notadd="z">z`</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>
        </tbody></table></div>   주의관찰)r   r   r   )dropnar   c                 S   s   d tt| S )Nr   )r   sortedsetr   r5   r5   r6   r     s    first)r   r   r   r   r   c                 S   r   r   r   r   r5   r5   r6   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>
    u   <div style="display: flex; justify-content: space-between; align-items: center;"> <h4 class="mt-4">우선순위별 공사비 요약</h4>u#      <div>    <span>부대공:<input z              type="text" z#              id="subsidiary_cost" z              value=",.0fz" u?                 placeholder="부대공사비를 입력하세요" z6              style="width:200px; text-align:center;" u               />원z            <button zM              type="button" style="width:100px; margin-left:4px;padding:0px" z5              class="btn btn-secondary form-control" z2              onkeypress="return isNumber(event)" z0              oninput="formatNumberInput(this)" zC              onclick="saveSubsidiaryCost(window.currentFilename)" u               > 부대공 저장 z            </button> </span>u          <span>제경비: <input z              type="number" z!              id="overhead_rate" z.0fz              min="0" z              max="1000" z              step="0.1" u<                 placeholder="제경비율을 입력하세요" z5              style="width:80px; text-align:center;" z            />%zA              onclick="saveOverheadRate(window.currentFilename)" u               > 재경비 저장 z*            </button> </span> </div></div>z<<table id="cost_sum" class="table table-striped"><thead><tr>u7   <th>우선순위</th><th>순공사비</th><th>제경비z (u4   %)</th><th>전체 공사비</th></tr></thead><tbody><tr><td>z#</td><td style="text-align:right;">
</td></tr>u%   <tr><td>부대공사비</td><td></td>uG   <td style="text-align:right;">가설 안전 환경 실시설계등</td>z<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   r[   locr   containsr|   uniquer\   r
   r]   rH   copyr   isingroupbyr   reset_indexapplyclassify_repairmatch_prioritymatch_unit_priceadjustroundrF   
to_numericrz   	DataFramer_   sort_valuesdropiterrowsr   aggr{   r   ) r   r   r`   ra   repairrepair_html	cost_htmlrates_resultru   rv   rw   count	df_repairbearing_damage_maskunique_componentsrowsr   idxri   filteredresult
total_cost_	total_sumoverhead_ratioprioamountsoftindirecttotalprio_circledfinal_totalr5   r5   r6   generate_repair_tablesB  st  
   

"

"

		






	8
r7  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   r7  r   r   )r   r!  r"  r-  r5   r5   r6   getData  s   


r:  z
/style.cssc                   C   r2   )Nr8  r9  r4   r5   r5   r5   r6   style  r8   r;  z/js/<path:filename>c                 C   s
   t d| S )Nz	static/jsr4   )r   r5   r5   r6   serve_js  r8   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  r5   )rh   itemr5   r5   r6   rj   '      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=  rB  rC  quantityrD  r	  	</td><td>r  r
  z</tbody></table>
cost_tabler     )r   get_jsonappendr{   rH   r   r   r   )repair_datafiltered_datacomponent_groupsr>  r@  cost_table_htmlr,  r{   grouped_itemskeycostr>   r5   r5   r6   update_repair   sL   
F rQ  z/pivot_detailc               
   C   sl   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pivotdetaildetail_htmlr   rG  r   rH  r
   pivot_detail_viewr   r   r   )r   r   rU  r>   r5   r5   r6   pivot_detailV  s    rX  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 r5   convert_numpy_types)rh   kvr5   r5   r6   
<dictcomp>j  r   z'convert_numpy_types.<locals>.<dictcomp>c                 S   s   g | ]}t |qS r5   rY  )rh   r\  r5   r5   r6   rj   l  rk   z'convert_numpy_types.<locals>.<listcomp>r>  tolistN)

isinstancer   r{   listtuplehasattrr>  r^  rF   rG   )objr5   r5   r6   rZ  g  s   



rZ  z/crack_subdivisionc               
   C   sr   zt  } td }t|| d | d \}}t||dW S  ty8 } ztdt|idfW  Y d }~S d }~ww )NrR  rS  rT  )rU  detail_html_header_linkr   rG  rV  )r   r   rU  rd  r>   r5   r5   r6   crack_subdivisionv  s   
 re  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      부재위치rO  <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>r  <tr><th></th><th></th>$   <th>손상물량</th><th>개소</th></tr></thead>rA     균열total_quantityr   total_count<tr><td>rE  r  	positionsrD  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   r  emptyr  r   r  r  r   	NameErrorr  subdivide_crack_datar{   r   r   r|   r   	traceback	print_exc)r   r'  html_outputr@  component_dfdamage_summaryry  posr-  
damage_rowdamage_typeunitcrack_subdivisions
sub_damagesub_datapos_quantity	pos_countru  rv  pos_datarD  r$  r>   r  r5   r5   r6   generate_crack_subdivision_view  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   )ru  rv  ry  )   균열(0.1mm)   균열(0.2mm)   균열(0.3mm)   균열(0.4mm)   균열(0.5mm이상)r   rC   r  r   r  rA   r  r   r  r  rf  r   r   ru  rv  ry  )rD  r$  rD  r$  )r  extract_crack_widthrH   r   )r  original_damage_typery  r  
crack_datar-  ri   crack_widthcategorypositionrD  r$  r5   r5   r6   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㏼이상)rA   )u   (\d+(?:\.\d+)?)\s*(?:mm|㏼)z(\d+(?:\.\d+)?)\s*(?:m[mM])u   (パ|파)\s*(\d+(?:\.\d+)?)u   パu   파rV   rW   g333333?)r   searchrH   group
ValueError
IndexError)damage_descriptionwidth_patternspatternr   r5   r5   r6   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   rf  rg  rh  ri  rj  rk  rl  rm  rn  ro  rp  r  rq  rr  rs  rA  rw  rx  rE  r  r   r   rz  r{  r|  r}  r~  u(   generate_component_pivot_tables 오류: Nu6   <p>테이블 생성 중 오류가 발생했습니다: r  )r   r  r  r  r   r  r  r   r  r  r   r|   r   r  r  )r   r'  r  r@  r  r  ry  r  r-  r  r  r  ru  rv  r  rD  r$  r>   r  r5   r5   r6   generate_component_pivot_tablesB  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   상태평가 요청 수신rR  r   u)   파일 정보를 찾을 수 없습니다.  ISELECT file_data FROM uploaded_files WHERE filename = %s AND user_id = %sr   "   파일을 찾을 수 없습니다.  r   evaluation_htmlu    상태평가 처리 중 오류: u6   상태평가 처리 중 오류가 발생했습니다: rG  N)r|   r
   r   r   r   r[   r\   r]   r_  r   jsonloadsrF   r  r   r_   r   r   )r   r`   ra   r+  r   r   all_evaluations_htmlr>   r5   r5   r6   condition_evaluation  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)
bridgeNamerp   rq   structureType	spanCountslabType
girderTypecrossbeamTypepavementTypeslabArea
girderAreacrossbeamAreapavementAreac                 3       | ]}| v V  qd S r   r5   )rh   fieldr   r5   r6   	<genexpr>      zevaluate.<locals>.<genexpr>r   u.   필수 입력 항목이 누락되었습니다.r  r  r  )typearear  r  r  r  r  r  )slabgirder	crossbeampavementr   rW   rO   valuesr?   )r   r  areas)r  
evaluationu)   평가 중 오류가 발생했습니다: rG  )
r   rH  allr   rH   r   bridge_evalr   r   r   )required_fieldsr  evaluation_dataresultsr>   r5   r  r6   evaluate  s>   





&r  z/evaluation_formc                   C      t dS )u2   
    교량 상태평가 폼 페이지 제공
    zevaluation_form.htmlr   r5   r5   r5   r6   evaluation_form'     r  z/evaluation_tablec                   C   r  )u4   
    새로운 상태평가표 페이지 제공
    zevaluation_table.htmlr  r5   r5   r5   r6   evaluation_table/  r  r  z/pricingc                   C   r  )Nzpricing.htmlr  r5   r5   r5   r6   pricing7  s   r  z/view_file/<filename>Fc           "      C   sT  t  }| }| td< zzg|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 }
|
d7 }
|
d7 }
|
d7 }
|
d7 }
|
d 7 }
|
d!7 }
|
d"7 }
|
d#7 }
|
d$7 }
|
d%7 }
|
d&7 }
|
d'7 }
|
d7 }
|
d7 }
|
d 7 }
|
d!7 }
|
d"7 }
|
d#7 }
| D ]?\}}|
d(7 }
|
d)|d  d*7 }
|
d+|d  d*7 }
|
d)|d,  d*7 }
|
d)|d  d*7 }
|
d)|d  d*7 }
|
d-7 }
q|
d%7 }
|
d.7 }
t || \}}}|d/| td f | }|r|d d urt!|d nd0}|r|d d urt!|d nd1}||d d2k  }g }t"|d3  t#d4D ]}||d3 |k }|d5  }||d j
jd6dd d7 $ }||d j
jd6dd d  }||d j
jd8dd d  }||d j
jd9dd d  }||d j
jd:dd d  }t%|||||d;} |&|t|dt'|s"|nd<|dkr-t|dnd<|dkr8t|dnd<|dkrCt|d=nd<|dkrNt|dnd<| d> qt(d?t)j*+d@dA||	|
||||d | ||dBW W |  |  S  t,y }! ztdCt
|!  ttdW  Y d }!~!W |  |  S d }!~!ww |  |  w )DNrR  r  r   r  r   r   r   r   aaaaaTF)rS  rT  r   r   r   r   r   r   r   r   rV   c                 S   r   r   r   r   r5   r5   r6   r   s  r   zview_file.<locals>.<lambda>r   rW   r   zb<div id="overall-table-header" class="table-container overall-table"><table class="table-striped">z <thead>z  <tr style="text-align: right;">u*    <th style="width: 150px;" >부재명</th>u-    <th style="width: 300px;" >손상내용</th>u&    <th style="width: 150px;">단위</th>u,    <th style="width: 150px;">손상물량</th>u&    <th style="width: 150px;">개소</th>z </tr>z	 </thead>r  zb<div class="table-container overall-table"><table id="overall-table" class="table-striped"><thead>zU <tr style="display:none" id="overall-print-table-header" style="text-align: right;">rw  z<td style="width: 150px;">r  z<td style="width: 300px;">r   r  z</div>z|
        SELECT markup_rate, overhead_rate
        FROM uploaded_files
        WHERE filename = %s AND user_id = %s
        r   r   	   바닥판rf  rg     점검면적rt     폭u   백태|누수u$   박락|파손|재료분리|층분리   철근부식)r  crack_ratio
leak_ratiosurface_damage_ratiorebar_corrosion_ratior;   rU   )   구분r  u	   균열폭u	   균열율   백태   표면손상r  u   등급index_re.htmltabrT  )
active_tabrU  rd  overall_htmlr!  r"  r   slab_eval_tableerror_messager   ru   rv   u9   파일을 불러오는 중 오류가 발생했습니다: )-r   r[   r
   r\   r]   r   r   r	   r_   r_  r   r  r  rF   r  r   r   r  r  r|   rW  r  r  r  r  r   r  r  r  r  r  r  r7  rH   r  r   maxevaluate_slab_conditionrI  rG   r   r   r   r   r   )"r   rS  r`   ra   r+  r   r   r'  rU  rd  r  r!  r"  
df_overallr&  overallr-  ri   	eval_htmlr#  ru   rv   slab_dfr  r  subr  r  r  r  r  rebar_ratior   r>   r5   r5   r6   	view_file;  s  
 
 



$$     


r  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   r[   r\   r
   r^   r   r   r   r_   r   r	   )r   r`   ra   r>   r5   r5   r6   delete_file  s&   
 

r  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   unknownrR  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@  damager   rB  damage_quantityrD  r$  rC  	totalCostu   보수물량 저장 완료)messager!  r"  u   업데이트 오류:u   오류 발생)r  r   rG  )r   rH  r
   r   r   r[   r\   r{   r^   r7  r   r_   r   r|   rollbackr   )r   r   r   r   r`   ra   rO  rT  r!  cost_htmlxxxxr-  r>   r5   r5   r6   update_file_damage_details  sT   

"

r  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: 상태평가표 데이터
       균열부백태rt  r   keywordslength_factor   망상균열   1방향   2방향r     누수r  u   박리u   박락u   파손   철근노출rt  r  r  r  r  u   콘크리트 바닥판r   r<   )
r  r     1방향 균열 최대폭   1방향 균열 균열율   2방향 균열 최대폭   2방향 균열 균열율   누수 및 백태 면적율   표면손상 면적율   철근부식 손상면적율   상태평가결과c                 S   s   g | ]
}d |d v r|qS )r  u   부재구분r5   rh   compr5   r5   r6   rj     r?  z,process_slab_damage_data.<locals>.<listcomp>c                 s   s    | ]	}t |d  V  qdS )   면적N)rH   r  r5   r5   r6   r    s    z+process_slab_damage_data.<locals>.<genexpr>r  r   r  r   c                 3   r  r   r5   rh   keyworddamage_descr5   r6   r    r  r  u    (\d+(?:\.\d+)?)\s*(?:mm|㎜|m|M)rW   c                 3   r  r   r5   r  r  r5   r6   r    r  r   c                 3   r  r   r5   r  r  r5   r6   r    r  c                 3   r  r   r5   r  r  r5   r6   r    r  r  c                 3   r  r   r5   r  r  r5   r6   r    r  )r  r  u   부식u   잡철근노출r  r   r  r;   r  r  r	  r
  r  r=   r>   r?   r@   rA   rB   rC   rD   rM   rO   rV   r  u   
            UPDATE component_damage
            SET 상태평가결과 = ?
            WHERE 부재구분 LIKE '%바닥판%'
        z"Error updating grade in database: N)r{   r_  r   r   rH   r  r   r  r  r  anyr   r[   r\   r^   r   r|   r_   )"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_arear  r  r  crack_patterncrack_matchr  r  crack_lengthmax_crack_widthmax_crack_ratior  r  r  r   r`   r[   r>   r5   r  r6   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   rW   rV   rU   r   )r   r   r   r   r   r  rt  r   r  r  r  r  r  r  r  r  r  r  )r  )	r   r[   r\   r
   rz   rI  r*  r_   r   )r`   r[   r  processed_datari   custom_damage_mappingr  r5   r5   r6   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   r-  r   Tas_attachmentdownload_nameu'   파일 다운로드 중 오류 발생: )u5   파일 다운로드 중 오류가 발생했습니다.rG  r   r[   r\   r]   r_   ospathsplitextr   appconfigr   r|   r   r   r`   r[   ro   nameextnew_filenamer>   r5   r5   r6   download_fileD  "   
r=  z/download_report/<filename>c              
   C   r.  )	Nr/  r   r-  r   Tr0  u*   보고서 다운로드 중 오류 발생: )u8   보고서 다운로드 중 오류가 발생했습니다.rG  r3  r9  r5   r5   r6   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 )Na0  
            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   ro   rp   rq   rr   rs   rt   u   추출된 교량 목록: T)r   bridgeszError getting bridge list: Fr   r   rG  )r   r[   r\   r
   rz   r   r   rI  r_   r|   r   r   r   )r`   ra   r  r   rC  ri   bridger>   r5   r5   r6   get_bridge_listr  s:   
rF  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  rD  r     r   )rf  r   r   r   r   rf  r   r   r   r  r   )r  r  r  r  r$  inspection_arear   rW   rV   rU   r   rP   )ro   rp   rq   rr   rs   rt   has_file_datadamage_dataTr   r   u$   교량 데이터 조회 중 오류: rG  )r   r[   r\   r
   r]   r   r_  r   r  r  rF   r  r   r  r  r   r  r  notnullrH   r   r   rI  boolr_   r   r|   )r   r`   ra   r+  file_data_jsonrJ  r   r@  r  component_summaryr-  ri   damage_itemr   r>   r5   r5   r6   get_bridge_data  sx   






$





rQ  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_typer4  r5  existsrF   read_csvr   to_dictr   r|   r   )ro   	file_pathr   r>   r5   r5   r6   get_bridge_components  s   
rW  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   F   파일명이 필요합니다.rD  r  r  r   r  r  r   )r  r  r  abutmentpier
foundationbearingexpansionJointr  drainagerailingTrK  u*   상태평가 데이터 생성 중 오류: Nu@   상태평가 데이터 생성 중 오류가 발생했습니다: rG  )r   rH  r   r   r   r[   r\   r
   r]   r_  r   r  r  rF   r  r_   r   r   r|   r  r  )r   r   r`   ra   r+  r   r   component_typesr  rR  r  r>   r  r5   r5   r6   generate_evaluation_data	  sj   






ra  z/api/save_markup_ratec               
   C   ^  zt  } | d}| d}|stddddfW S |du r)tddddfW S zt|}|d	k s6|d
krAtddddfW W S W n tyU   tddddf Y W S w t }| }|d||t	d f |j
d	krwtddddfW S |  |  |  tdd| ddW S  ty } ztddt| ddfW  Y d}~S d}~ww )u   할증율 저장 APIr   ru   FrX  rD  r  Nu   할증율이 필요합니다.r   r   u+   할증율은 0~100% 범위여야 합니다.u*   유효한 할증율을 입력해주세요.zOUPDATE uploaded_files SET markup_rate = %s WHERE filename = %s AND user_id = %sr   r  r  Tu
   할증율    %가 저장되었습니다.r   r  u3   할증율 저장 중 오류가 발생했습니다: rG  r   rH  r   r   rH   r  r   r[   r\   r
   rowcountr^   r_   r   r   )r   r   ru   r`   ra   r>   r5   r5   r6   save_markup_rateC	     





rg  z/api/save_overhead_ratec               
   C   rb  )u   제경비율 저장 APIr   rv   FrX  rD  r  Nu    제경비율이 필요합니다.r   r   u.   제경비율은 0~100% 범위여야 합니다.u-   유효한 제경비율을 입력해주세요.zQUPDATE uploaded_files SET overhead_rate = %s WHERE filename = %s AND user_id = %sr   r  r  Tu   제경비율 rc  rd  u6   제경비율 저장 중 오류가 발생했습니다: rG  re  )r   r   rv   r`   ra   r>   r5   r5   r6   r   	  rh  r   z/api/save_subsidiary_costc               
   C   sX  zt  } | d}| d}|stddddfW S |du r)tddddfW S zt|}|d	k r=tdd
ddfW W S W n tyQ   tddddf Y W S w t }| }|d||t	d f |j
d	krstddddfW S |  |  |  tdd|dddW S  ty } ztddt| ddfW  Y d}~S d}~ww )u   부대공사비 저장 APIr   rw   FrX  rD  r  Nu#   부대공사비가 필요합니다.r   u2   부대공사비는 0원 이상이어야 합니다.u0   유효한 부대공사비를 입력해주세요.zSUPDATE uploaded_files SET subsidiary_cost = %s WHERE filename = %s AND user_id = %sr   r  r  Tu   부대공사비 r  u   원이 저장되었습니다.rd  u9   부대공사비 저장 중 오류가 발생했습니다: rG  re  )r   r   rw   r`   ra   r>   r5   r5   r6   save_subsidiary_cost	  s   




ri  z/api/reload_repair_tablec               
   C   s"  zpt  } | d}|stddddfW S t }| }|d|td f | }|s8t	d t
td	W S |d
 }t|trFt|}t|}t|}t|d  }||d | }t||\}}	}
td||	dW S  ty } ztddt| ddfW  Y d}~S d}~ww )u;   보수물량표 재로드 API (할증율 변경 후 사용)r   FrX  rD  r  r  r   r  r   r   r   T)r   r!  r"  u<   보수물량표 재로드 중 오류가 발생했습니다: rG  N)r   rH  r   r   r   r[   r\   r
   r]   r   r   r	   r_  r   r  r  rF   r  r   r   r  r  r7  r   )r   r   r`   ra   r+  r   r   r'  r!  r"  r-  r>   r5   r5   r6   reload_repair_table
  sT   





rj  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*   필수 데이터가 누락되었습니다.rD  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-   상태평가 결과가 저장되었습니다.rd  u'   상태평가 결과 저장 중 오류: u)   저장 중 오류가 발생했습니다: rG  N)r   rH  r   r   r   r[   r\   r
   r  dumpsr^   r_   r   r|   r   )r   r   r  r`   ra   r>   r5   r5   r6   save_evaluation_result>
  sD   


rl  z/adminc                   C      t dddS )Nzadmin/admin.htmlmetar  r  r5   r5   r5   r6   
admin_homeo
     rp  z/admin/metac                   C   rm  )Nzadmin/meta.htmlrn  ro  r  r5   r5   r5   r6   
admin_metas
  rq  rr  z/validate_filec               
   C   s  zt d dtjvrtddddfW S tjd } | jdkr(tddddfW S | jds8tdd	ddfW S t d
| j  t| }t d|j dt|j	  d|j|j	g |j
d}|jrz"ddl}|| }| d t|}t|}||d< t||d< W n' ty } zt dt|  |d dt|  W Y d}~nd}~ww t|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 )u2   파일 검증 API - 개선된 검증 로직 포함u   파일 검증 요청 수신r   Fr   rD  r  r   r   u1   Excel 파일(.xlsx)만 업로드 가능합니다.u   검증할 파일: u   검증 결과: valid=z	, errors=T)r   is_validr   warningsinfor   Nvalidation_detailstable_previewu   상세 검증 중 오류: rt  u0   상세 검증 중 오류가 발생했습니다: u   파일 검증 중 오류: u0   파일 검증 중 오류가 발생했습니다: rG  )r|   r   r   r   r   r   r"   rs  r   r   ru  pandasr   seekr   perform_detailed_validationgenerate_table_previewr   r   rI  r  r  )r   validation_resultresponse_datarF   r   detailed_validationr>   r  r5   r5   r6   validate_filey
  sx   


	

$
r  c                    s`  t d g }g d} fdd|D }|r$dd| gg t dS g }  D ]_\}}g }t|d sBt|d  d	krG|d
 t|d sXt|d  d	kr]|d t|d snt|d  d	krs|d t|d r|d t|d r|d t|d st|d  d	kr|d zt|d  }tj	|d dd}	tj	|d dd}
t
|	rt
|
r|rd}d}g d}g d}|D ]}| jv rt
|| rtj	|| dd} nq|D ]}| jv rt
|| rtj	|| dd} nq|dkrCt
|rCt
|
rC||
 }t|	| dkrB|d|	 d|dd| d |
 d!	 nY|d"v rt
|rt
|rt
|
r|| |
 }t|	| dkr|d#|	 d|dd| d$| d |
 d! n| d%v rt|	|
 dkr|d&|	 d|
 d' W n ty } z|d(t|  W Y d}~nd}~ww z)t
|d rtj	|d dd}t|r|d) n
|d*k r|d+ W n	   |d, Y z)t
|d rtj	|d dd}t|r|d- n
|d*k r|d. W n	   |d/ Y |r||d0 |t
|d r8t|d nd	t
|d rGt|d nd	t
|d rVt|d nd	t
|d ret|d nd	t
|d rtt|d nd	t
|d rt|d nd	dd1 q*t d2t| d3 |rd4t| d5gng |t t t| d6S )7u8   상세 검증 수행 - 손상물량 계산 검증 포함u   상세 검증 시작r   rf  r   r   r   r   c                    s   g | ]	}| j vr|qS r5   r   rh   colr   r5   r6   rj   
  r   z/perform_detailed_validation.<locals>.<listcomp>u(   필수 컬럼이 누락되었습니다: r   )r   
error_rows
total_rowsr   r   u   부재명이 비어있음rf  u   부재위치가 비어있음r   u   손상내용이 비어있음r   u   손상물량이 비어있음r   u   개소가 비어있음r   u   단위가 비어있음r   r   N)u   길이Lrp   u   연장)r  u   너비Wrq   r   mg{Gz?u(   m 단위 손상물량 불일치: 실제 u	   , 예상 r   u	    (길이 u    × 개소 ))u   ㎡u   m²m2u*   ㎡ 단위 손상물량 불일치: 실제 u    × 너비 )eaEAu   개r   u-   개수 단위 손상물량 불일치: 실제 u    (개소와 동일해야 함)u'   손상물량 계산 검증 중 오류: u    손상물량이 숫자가 아님r   u   손상물량이 음수임u   손상물량 형식 오류u   개소가 숫자가 아님u   개소가 음수임u   개소 형식 오류rW   )	row_indexr   r   u   상세 검증 완료: u   개 오류 행 발견u   총 u   개 행에서 오류 발견)r   r  r  
valid_rows)r|   r   r   r  rF   rG   r   striprI  r  notnar   absr   r   	enumerate)r   validation_errorsrequired_columnsmissing_columnsr  r)  ri   
row_errorsr  r  r$  rp   rq   length_columnswidth_columnsr  expected_quantityr>   
damage_qty	count_val	error_rowr  r   ir   r5   r  r6   rz  
  s   "
"
"


"
"&., 



rz  c              
      s|   z!|  g d} fdd|D }|r |   j ddddd}|W S  ty= } ztd	t|  W Y d
}~dS d
}~ww )u?   테이블 미리보기 생성 - HTML 테이블 형태로 반환r  c                    s   g | ]	}| j v r|qS r5   r   r  
preview_dfr5   r6   rj   e  r   z*generate_table_preview.<locals>.<listcomp>z"table table-striped table-borderedzpreview-tableFT)classestable_idescaper   u*   테이블 미리보기 생성 중 오류: Nu<   <p>테이블 미리보기를 생성할 수 없습니다.</p>)to_htmlr   r|   r   )r   r  available_columns
table_htmlr>   r5   r  r6   r{  [  s$   r{  )r   N)Fr   )rx  rF   flaskr   r   r   r   r   r   r   r	   r
   r   r4  r   randomdifflibr   docxr   docx.sharedr   r   docx.enum.textr   docx.enum.tabler   ionumpynpcollectionsr   r  mathstatic.data.damage_solutionsr   utils.commonr   r   r   r   r   r   r   utils.evaluationutils.pivot_detail_viewutils.condition_evaluationr   r   utils.bridge_evaluationr   r   #utils.detailed_condition_evaluationr    r!   utils.file_validationr"   r#   psycopg2werkzeug.securityr$   r%   werkzeug.utilsr&   apir'   api.carbonation_testr(   api.component_selectionr)   api.evaluation_weightsr*   download.download_filer+   
flask_corsr,   dotenvr-   getenvr.   r/   r   r7  register_blueprint
secret_keyrouter7   template_filterr9   rL   rQ   rR   rT   rc   r   r   r5  r   
expanduserr   rS  makedirsr8  r   r  r   r   r   r   r   r7  r:  r;  r<  rQ  rX  rZ  re  r  r  r  r  r  r  r  r  r  r  r  r  r*  r-  r=  r?  rF  rQ  rW  ra  rg  r   ri  rj  rl  rp  rr  r  rz  r{  r5   r5   r5   r6   <module>   sR   0$








 /

'


5
U   O
	


5
l-T[
3
 #
9 ]
8'P
????6/

I 