o
    Ki                     @   s&  d dl Zd dlmZmZ d dlmZ d dlmZmZ d dl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 d dlZedZed	Zd
efddZd
efddZd
efddZd
edefddZd
efddZd
efddZd
edefddZd
edefddZd
efddZdS )    N)get_db_configlogger)	timedelta)datetimer   )DataBarRule)r   )get_gemini_model
Sales_C_DBSales_Goalskill_login
session_idc           !      C   s  t d}ddddd}ddd	d
ddd}g d}i }d }d }zAztjjdi |}|jdd}| D ]\}}	d|	 }
d|	 }g }ddgt| }d|
 d| d}d| d| d}| g| }||| |	 }||| |	 }i }|D ]}|d |vr|||d < q~i }|D ]}|d |vr|||d < q|D ]y}|
|}|r|d }|d d urt|d nd}|d d urt|d nd}nd}d}d}|
|}|r|d }|d d urt|d nd}|d d urt|d nd}nd}d}d}|d u rd}|| }||| |||||||d q|||< q5|W W |r+|  |r3|  S S  ty` }  z td|   i W  Y d } ~ W |rS|  |r[|  S S d } ~ ww |rh|  |rp|  w w ) Nr   dailyweeklymonthly	quarterly   日次u   週次u   月次u	   1/4期次	   架電数   繋がるための行動   アポ   訪問   見積   受注                  T
dictionaryP_T_,%sz^
                SELECT category, achievement_rate, target, achievement 
                FROM 9 
                WHERE session_id = %s AND category IN (A)
                ORDER BY record_date DESC, id DESC
            ]
                SELECT category, achievement_rate, target, achievement
                FROM categoryachievement_ratetargetr   achievement)titletanakateamdiffp_targetp_achievementt_targett_achievementzReal Chart Data Error:  )r   mysql	connectorconnectcursoritemsjoinlenexecutefetchallgetintappendclose	Exceptionr   error)!r
   configperiodscategory_maptarget_categoriesresponse_dataconnr7   labelsuffixp_tablet_tableperiod_listformat_stringssql_psql_tparamsp_rowst_rowsp_dictrowt_dictcat_idp_rowtanaka_ratep_tgtp_acht_row	team_ratet_tgtt_achr.   er3   r3   </home/air/goalskill_sales/back/app/models/progress_module.pyget_real_chart_data   s   






rb   c                 C   
   t | dS )Nam_get_ampm_chart_datar
   r3   r3   ra   get_am_chart_data      
 rh   c                 C   rc   )Npmre   rg   r3   r3   ra   get_pm_chart_data   ri   rk   	time_typec              
   C   s  t d}ddddd}ddd	d
ddd}g d}i }d }d }zztjjdi |}|jdd}| D ]\}	}
d| d|
 }g }ddgt| }d| d| d}| g| }||| |	 }i }|D ]}|d |vru|||d < qg|D ]<}|
|}|r|d }|d d urt|d nd}|d d urt|d nd}nd}d}d}||| |||d qx|||	< q3|W W |r|  |r|  S S  ty } z"t|  d|  i W  Y d }~W |r|  |r|  S S d }~ww |r|  |r|  w w )Nr   r   r   r   r   r   r   r   r   r   r   r   r   Tr   today_sales__r"   r#   r&   r$   r%   r'   r(   r)   r   r*   r+   rater)   r*   z Chart Data Error: r3   )r   r4   r5   r6   r7   r8   r9   r:   r;   r<   r=   r>   r?   r@   rA   r   rB   upper)r
   rl   rC   rD   rE   rF   rG   rH   r7   rI   rJ   
table_namerM   rN   sqlrQ   rows	data_dictrU   rW   rp   tgtachr`   r3   r3   ra   rf      sr   



rf   c              
   C   s  t d}ddddd}ddd	d
ddd}g d}i }d}d}zztjjdi |}|jdd}| D ]\}}	d|	 }
g }ddgt| }d|
 d| d}| g| }||| |	 }i }|D ]}|d |vrr|||d < qd|D ]:}|
|}|r|d }|d }|d }nd}d}d}t|drt|}t|drt|}||| |||d qu|||< q3|W W |r|  |r|  S S  ty } ztd|  i W  Y d}~W |r|  |r|  S S d}~ww |r|  |r|  w w ) u   
    Result 테이블(result_daily 등)에서 성과율뿐만 아니라
    target(목표)과 achievement(실적) 값도 함께 조회하여 반환
    r   r   r   r   r   r   r   r   r   r   r   r   r   NTr   result_r"   r#   r&   zL 
                WHERE session_id = %s 
                  AND category IN (r%   r'   r(   r)   r*   r   to_integral_valuero   zResult Chart Data Error: r3   )r   r4   r5   r6   r7   r8   r9   r:   r;   r<   r=   hasattrr>   r?   r@   rA   r   rB   )r
   rC   rD   rE   rF   rG   rH   r7   rI   rJ   rr   rM   rN   rs   rQ   rt   ru   rU   rW   rp   
target_valachieve_valr`   r3   r3   ra   get_result_chart_data   s   





r}   c           >         s`  d}d}d}d}zzt d}tjjd[i |}|jdd}d}||| f | }|scdddW W d	t v r?|r?|  d
t v rJ|rJ|  dt v rU|rU|  dt v ra|rb|  S S S |d }|d pld}	t d}
tjjd[i |
}|jdd}g d}d	dgt
| }d| d}||| g|  | }i }|D ]  d } d }|durt|nd||< q|  |  d}d| d}|||g|  | }dddddd d}g }i }|D ]]  d }||d!}||d} d" durt d" nd} d# durt d# nd}|dkr+|}td$| d%| d& ||||d'||< || d(| d)| d*| d+ qd,	|}d-|	 d.| d/}t }|jjd0|d1}|j} td2|   i }!|  d,D ]"}"| D ]}||"v rtd3|"}#|#rt|#d4|!|< qyqsd}$|! D ] \}}%||}&|&r|&d5 }d6}'||'|%||f |$d47 }$q|  d7}(d8})tj	|(|) d9|  d:}*t  |*}+|+sd;d<|$ d=| d>W W d	t v r|r|  d
t v r|r|  dt v r|r|  dt v r|r|  S S S |+d },t!j"|,dd?}-|-d@ t!j"|,dAd?}.|.d@ }/d}0t#dBdCD ]+ t$j% d4dDj&pJdE}1t$j% dFdDj&pWdE}2dG|1v scdG|2v rg }0 nq=|0du rddHdW W d	t v r|r|  d
t v r|r|  dt v r|r|  dt v r|r|  S S S d}3t#|0|0dI D ]  fdJdKt#d4dLD }4t'dMdN |4D rш }3 nq|3r]dOdPdQdRdSdTdU}5|! D ]q\}}%||5v rS|5| }6j%|3|6dDj&}7|7du rd}7zt|7}7W n t(y   d}7Y nw |7|% }8|%|/j%|3|6dD_&|8dkrSdVD ]-}9|3|9 }:j%|:|6dDj&};z|;dur=t|;nd};W n   d};Y |;|8 |/j%|:|6dD_&q%q|.)|, dW}<ndX}<d;dY|< | d>W W d	t v rv|rv|  d
t v r|r|  dt v r|r|  dt v r|r|  S S S  t*y }= zLt+dZ|=  dt$|=dW  Y d}=~=W d	t v r|r|  d
t v r|r|  dt v r|r|  dt v r|r|  S S S d}=~=ww d	t v r|r|  d
t v r|r|  dt v r |r |  dt v r.|r/|  w w w )\u   
    오후 계획 자동 조정 
    (Sales_C_DB.P_daily의 target을 100% 기준으로 조회 -> AI 분석(70~75%) -> DB & Excel 업데이트)
    N
Sales_B_DBTr   z
            SELECT id, morning_motivation 
            FROM daily_plans 
            WHERE session_id = %s 
            ORDER BY date DESC, id DESC LIMIT 1
        rB   No daily plan found in DBstatusmessagecursor_bconn_bcursor_cconn_cidmorning_motivationu   特になしr   r   r"   r#   zs
            SELECT category, target 
            FROM P_daily 
            WHERE session_id = %s AND category IN (z9)
            ORDER BY record_date DESC, id DESC
        r'   r)   r   z
            SELECT category, original_target_count, current_count 
            FROM goals 
            WHERE plan_id = %s AND category IN (
)
        r   r   r   r   r   r   Unknownoriginal_target_countcurrent_countzNo P_daily target for z, using plan_target (z) as standard.)rW   
std_targetplan_targetachievedu    -> 【100%基準(P_daily): u   件】 / 本日当初計画: u   件 / 現在実績: u   件
u!  
        あなたは敏腕営業マネージャーです。
        部下（田中）の進捗状況を分析し、午後から達成すべき「今日1日の最終的な修正目標数値」を再設定してください。

        【部下の現状】
        モチベーション: u1   

        【数値データ (重要)】
        u  

        【計算ルール】
        1. 各項目の「100%基準(P_daily)」がその日の完全達成ライン(MAX)です。**絶対にこれを超えてはいけません。** (例: 100%基準が20件なら、25件にするのは間違いです)
        2. 体調や進捗を考慮し、**「100%基準」に対して「70% 〜 75%」の範囲**になるように目標を再計算してください。
           - 計算式: (100%基準 × 0.70) 〜 (100%基準 × 0.75)
           - 計算結果が小数になる場合は、整数（四捨五入または切り捨て）にしてください。
        3. もし現在実績が既に75%を超えている場合は、その実績値を維持、または無理のない範囲（MAX 80%程度）で微増させてください。
        4. 出力は「今日1日のトータル目標値」です。

        【重要】
        ※ 金額ではなく、必ず「件数(件)」で出力してください。

        【出力フォーマット】(数値のみ、単位「件」をつける)
        架電数: N件
        繋がるための行動: N件
        アポ: N件
        訪問: N件
        見積: N件
        受注: N件
        zgemini-2.5-flash)modelcontentszGemini Plan Output: 
z(\d+)   rW   z
                    UPDATE goals 
                    SET adjusted_target_count = %s 
                    WHERE plan_id = %s AND category = %s
                z;/home/air/goalskill_sales/front/progress_xls/Sales_NEW_GOAL20260219_Sales_today_NEW_GOAL_.xlsxsuccesszDB updated (z ), but NEW_GOAL Excel not found.)r   r   	ai_output	data_only
   Data入力F   2   rU   column r      田中u)   Could not find '田中' section in Excel.   c                        g | ]}t j |d jqS r   strcellvalue.0crU   ws_valr3   ra   
<listcomp>       z0execute_afternoon_adjustment.<locals>.<listcomp>r   c                 s       | ]}d |v V  qdS r   Nr3   r   sr3   r3   ra   	<genexpr>      z/execute_afternoon_adjustment.<locals>.<genexpr>r                  )r   r   r   r   r   r   r   r   r   zExcel updated successfully.u   Could not find '日次' row.z0Updated DB & Excel (Based on P_daily Standard). zAfternoon Plan Logic Error: r3   ),r   r4   r5   r6   r7   r;   fetchonelocalsr@   r9   r:   r<   r>   r=   r   warningr?   r   modelsgenerate_contenttextinfostripsplitvaluesresearchgroupr8   commitospathglobopenpyxlload_workbookranger   r   r   any
ValueErrorsaverA   rB   )>r
   r   r   r   r   b_configsql_planplan_rowplan_id
motivationc_configrF   rN   sql_p_dailyrR   standard_targetscatval	sql_goals	goal_rowscat_map_namestatus_linesai_input_datarW   namer   r   r   status_textpromptclientresponser   new_targets_maplinematchupdated_db_countsnew_valcat_data
update_sqlbase_dir_writetarget_datesearch_pattern_writefiles_writefile_path_writewb_valwb_writews_writetanaka_start_rowval_aval_b
target_rowrow_valscol_map_excelcol_idxcurrent_valr.   offsetr_idxold_upper_val	excel_msgr`   r3   r   ra   execute_afternoon_adjustment*  sl   x 






O 
7 


   r  report_typec           4         s4  d}d}zzt d}tjjdCi |}|jdd}d}||| f | }|s>dddW W |r6|  |r=|  S S |d	 }t	d
|  g d}d
dgt| }	d|	 d}
||
|g|  | }i }|D ]6 zt d } d }|durt|nd}|||< W qo ty } ztd|  W Y d}~qod}~ww t	d|  ddddddd}d}d|  }d| }tj
|| d|  d|  d }t|}d!}|r|d }tj|dd"}|d# tj|d$d"}|d# }d}td%d&D ]+ tj d'd(jpd!}tj d)d(jpd!}d*|v s(d*|v r, } nq|sbtd'd&D ]+ tj d'd(jpCd!}tj d)d(jpPd!}d*|v s\d*|v r` } nq6|rd}t||d+ D ]  fd,d-td'd.D } td/d0 | D r } nqn|r| D ]C\}!}"||!d}#|#|j||"d(_d1D ]-}$||$ }%j|%|"d(j}&z|&durt|&nd}&W n   d}&Y |&|# |j|%|"d(_qq|| | d2}n| d3}n| d4}n| d5}d6}'tj
|'| d|  d }(t|(})d!}*|)r|)d }+tj|+dd"},|,d# }-tj|+d$d"}.|.d# }/g }0|d7kr4|0d8 n|d9krC|0d: |0d d}1|0D ]O}2| D ]C\}!}"||!d}#|#|/j|2|"d(_d1D ]-}$|2|$ }3|-j|3|"d(j}&z|&duryt|&nd}&W n   d}&Y |&|# |/j|3|"d(_qaqM|1d'7 }1qG|.|+ d;|0 d<}*nd=}*d>| d?|* |rtj|d nd@|)rtj|)d nd@dAW W |r|  |r|  S S  ty	 } z%tdB|  dt|dW  Y d}~W |r|  |r|  S S d}~ww |r|  |r|  w w )Du`   
    AM/PM 리포트 엑셀 업데이트 + Sales_today 종합 리포트 업데이트 함수
    Nr~   Tr   z
            SELECT id, date 
            FROM daily_plans 
            WHERE session_id = %s 
            ORDER BY date DESC, id DESC LIMIT 1
        rB   r   r   r   zTarget Plan ID: r   r"   r#   zu
            SELECT category, current_count 
            FROM goals 
            WHERE plan_id = %s AND category IN (r   r'   r   r   zData conversion error: zCurrent Counts: r   r   r   r   r   r   r   Sales_z-/home/air/goalskill_sales/front/progress_xls/_Sales_today_report_rn   r   r   r   r   Fr   r   r   r   r   r   r   c                    r   r   r   r   r   r3   ra   r     r   z)execute_report_update.<locals>.<listcomp>r   c                 s   r   r   r3   r   r3   r3   ra   r     r   z(execute_report_update.<locals>.<genexpr>r   z file updated.z$ file found but 'Daily' row missing.z! file found but 'Tanaka' missing.z file NOT found.z8/home/air/goalskill_sales/front/progress_xls/Sales_todayAM   PM(   z Sales_today file updated (Rows: z).zSales_today file NOT found.r   z | None)r   r   
file_am_pm
file_todayzReport Update Error: r3   ) r   r4   r5   r6   r7   r;   r   r@   r   r   r9   r:   r<   r>   rA   rB   rq   r   r   r   r   r   r   r   r   r   r   r8   r=   r   r?   basename)4r
   r  rH   r7   r   r   r   r   rF   rN   r   r   current_counts_mapcat_keyr   	count_valr`   
cat_to_colr   folder_namebase_dirsearch_patternfiles	msg_part1	file_pathr   r   r   
tanaka_rowr   r   r   valsrW   r   	cur_countr   r   old_valbase_dir_todaysearch_pattern_todayfiles_today	msg_part2file_path_todaywb_val_tws_val_t
wb_write_t
ws_write_ttarget_daily_rowsupdated_today_counttarget_r	upper_rowr3   r   ra   execute_report_updateE  s"   < 	
$








r)  c              
   C   s  d}d}zzt d}tjjdi |}|jdd}d|  d}d| d}||| f | }d	}|D ]E}	|	d
 }
|	d durHt|	d nd}|	d durVt|	d nd}|d	krg|| d }t	|}nd	}d| d}||||
f |d7 }q6|
  dd| d| d|dW W |r|  |r|  S S  ty } z&td| d|  dt|dW  Y d}~W |r|  |r|  S S d}~ww |r|  |r|  w w )u   
    AM/PM Daily 테이블의 달성률(achievement_rate) 계산 및 업데이트
    Formula: (achievement / target) * 100 -> INT
    Nr   Tr   rm   _dailyzH
            SELECT id, category, target, achievement 
            FROM z, 
            WHERE session_id = %s
        r   r   r)   g        r*   d   z
                UPDATE zW 
                SET achievement_rate = %s 
                WHERE id = %s
            r   r   zUpdated achievement_rate for z. (z rows))r   r   updated_rowszAchievement Rate Update Error (z): rB   r   r3   )r   r4   r5   r6   r7   lowerr;   r<   floatr>   r   r@   rA   r   rB   r   )r
   rl   rH   r7   r   rr   
sql_selectrt   updated_countrU   	record_idrv   rw   rate_valrate_int
sql_updater`   r3   r3   ra   update_achievement_rate_db  sT   


r5  c              
   C   s0  ddl }td}d}d}dddddd}zrzCtjjdi |}|jddd}d}||| f | }|rb|drbz|	|d |d	< |d
d|d< W n |j
ya   td|   Y nw d}||| f | }	|	r|	d dur}t|	d nd|	d durt|	d ndi d}
|	drz8|	d }t|tr|	|}| D ]#\}}t|trd|v r|d |
d |< qt|ttfr||
d |< qW n |j
tfy } ztd|  W Y d}~nd}~ww |
|d< d}||| f | }|rH|dr8z|d }t|tr|	|}||d< W n |j
tfy7 } ztd|  W Y d}~nd}~ww |ddurHt|d |d< |W W |rR|  |rZ|  S S  ty } z td|  |W  Y d}~W |rz|  |r|  S S d}~ww |r|  |r|  w w )uK   
    MBTI scores와 SPI 결과를 Sales_A_DB에서 조회하여 반환
    r   N
Sales_A_DB)mbti	mbti_typespiojtojt_averageT)r   bufferedz
            SELECT result, scores 
            FROM mbti_Output 
            WHERE session_id = %s 
            ORDER BY created_at DESC LIMIT 1
        scoresr7  resultr   r8  z*MBTI scores JSON parse error for session: z
            SELECT language_score, nonverbal_score, personality_score 
            FROM spi_Output 
            WHERE session_id = %s 
            ORDER BY created_at DESC LIMIT 1
        language_scorenonverbal_score)r?  r@  personalitypersonality_scorescorerA  z#SPI personality_score parse error: r9  z
            SELECT Node_score, output_value 
            FROM human_os_Output 
            WHERE session_id = %s 
            ORDER BY created_at DESC LIMIT 1
        
Node_scorer:  zOJT Node_score parse error: output_valuer;  zDebug Chart Data Error: r3   )jsonr   r4   r5   r6   r7   r;   r   r=   loadsJSONDecodeErrorr   rB   r.  
isinstancer   r8   dictr>   	TypeErrorr@   rA   )r
   rF  a_configrH   r7   r>  sql_mbtimbti_rowsql_spispi_rowspi_datap_datakeyr   r`   sql_ojtojt_rowojt_datar3   r3   ra   get_debug_chart_datae  s   



rW  )mysql.connectorr4   app.core.configr   r   r   r   r   
subprocessr   openpyxl.formatting.ruler   pandaspdr   r   r   C_DB_CONFIGGoalskill_login_DB_CONFIGr   rb   rh   rk   rf   r}   r  r)  r5  rW  r3   r3   r3   ra   <module>   s6    oI\   YH