o
    ,i'                  	   @   sH  d Z ddlmZmZmZmZ ddlZddlm	Z	 ddl
mZ ddlmZ ddlmZ edZd	Zd
dddddd	Zdedeeef fddZdedeeef fddZdedeeef fddZdededeeef defddZd#dededeeef fddZdedeeeeef f fdd Zd#dededeeef fd!d"ZdS )$uY   
現在地とチーム基準: sales_quarter_plan 테이블 접근 + 1s 목표치 조회.
    )DictAnyListOptionalN)logger)get_db_config)get_sales_sheet_by_session)get_username
Sales_A_DB)                  u	   架電数u   繋がるための行動u   アポu   訪問u   見積u   受注
session_idreturnc              
   C   s   t | d}dd |D }i }|D ]8}|d}|du rqzt|dp$d}W n ttfy4   d}Y nw |t|kr?t|nt|d|t|< qd	d
 tD }||dS )u   
    1s, quarter, record_type=target 인 목표치만 조회.
    category IN (2,3,4,5,6,8).
    Returns: { "targets": {"2": 680, ...}, "category_names": {"2": "架電数", ...} }
    1sc                 S   s:   g | ]}| d dkr| ddkr| dtv r|qS )period_typequarterrecord_typetargetcategorygetQUARTER_PLAN_CATEGORIES.0r r   D/home/air/goalskill_sales/back/app/models/current_position_module.py
<listcomp>    s    z'get_quarter_targets.<locals>.<listcomp>r   Nvaluer      c                 S   $   i | ]}t |t|d | qS    項目strCATEGORY_NAMESr   r   cr   r   r    
<dictcomp>0      $ z'get_quarter_targets.<locals>.<dictcomp>)targetscategory_names)	r   r   float	TypeError
ValueErrorintroundr(   r   )r   rowsquarter_targetsr.   r   catvalr/   r   r   r    get_quarter_targets   s"   

(
r9   c           
   
   C   s   t | d}dd |D }i }i }|D ]Z}|d}|du rqzt|dp&d}W n ttfy6   d}Y nw t|}|dd	krT|t|krLt|nt|d
||< q|ddkrl|t|kret|nt|d
||< qdd tD }	|||	ddS )u   
    1s, quarter の target + result を sales_sheet_data から取得（フォーム事前表示・02/19データ用）.
    Returns: { "targets": {"2": 680, ...}, "results": {"2": 400, ...}, "category_names": {...}, "data_date": "02/19" }
    r   c                 S   ,   g | ]}| d dkr| dtv r|qS r   r   r   r   r   r   r   r    r!   :      , z3get_quarter_targets_and_results.<locals>.<listcomp>r   Nr"   r   r   r   r#   resultc                 S   r$   r%   r'   r*   r   r   r    r,   J   r-   z3get_quarter_targets_and_results.<locals>.<dictcomp>z02/19)r.   resultsr/   	data_date)	r   r   r0   r1   r2   r(   r3   r4   r   )
r   r5   quarter_rowsr.   r>   r   r7   r8   keyr/   r   r   r    get_quarter_targets_and_results4   s2   

$"rB   c              	   C   s   t | }|dp
i }|dpi }dd tD }i }|D ]3}||p%d}||p,d}|r<|dkr<t|| d dnd}	|||	tt|d| d	||< qt| pVd
}
|||
dS )u   
    1s quarter の result/target で 개인 달성율만 계산（팀 없이）.
    Returns: { "by_category": { "2": { "target": 680, "result": 400, "rate": 58.8 }, ... }, "categories_order": [...], "username": ... }
    r.   r>   c                 S      g | ]}t |qS r   r(   r*   r   r   r    r!   [       z0get_user_achievement_summary.<locals>.<listcomp>r   d   r#   r&   )r   r=   ratecategory_name	   あなたby_categorycategories_orderusername)rB   r   r   r4   r)   r3   r	   )r   infor.   r>   rL   rK   cat_strr   r=   rG   rM   r   r   r    get_user_achievement_summaryS   s    "rP   r   plansc              
   C   s,  | r|sdS |p	d}zvt jjdi t}| }|d d}d}| D ]L\}}zt|}	W n tt	fy:   Y q%w |	t
vr@q%z|durIt|nd}
W n tt	fyY   d}
Y nw |
du sb|
dk rcq%||| ||	|
f |d7 }q%|  |  |  |W S  ty } z	td|   d}~ww )	ui   
    sales_quarter_plan 에 INSERT or ON DUPLICATE KEY UPDATE.
    plans: { "2": 400, "3": 70, ... }
    r   r   SET time_zone = '+09:00'z
            INSERT INTO sales_quarter_plan (session_id, period_type, category, planned_value)
            VALUES (%s, %s, %s, %s)
            ON DUPLICATE KEY UPDATE planned_value = VALUES(planned_value), updated_at = CURRENT_TIMESTAMP
        Nr#   zsales_quarter_plan save error: r   )mysql	connectorconnectA_DB_CONFIGcursorexecuteitemsr3   r1   r2   r   r0   commitclose	Exceptionr   error)r   r   rQ   connrW   sqlcountrO   r8   r7   ver   r   r    save_quarter_plank   sF   

rc   r   c              
   C   s  t | }|dp
i }|sdi iS z*tjjdi t}|jdd}|d |d| |f | }|	  |	  W n t
yY } ztd|  di iW  Y d}~S d}~ww d	d
 |D }i }	| D ]#\}
}||
d}|r|dkrt|| d dnd}|||d|	|
< qgd|	iS )u   
    target(1s) + planned(sales_quarter_plan) 으로 카테고리별 계획 달성률 계산.
    Returns: { "by_category": { "2": { "target": 680, "planned": 400, "rate": 58.8 }, ... } }
    r.   rK   T)
dictionaryrR   z
            SELECT category, planned_value
            FROM sales_quarter_plan
            WHERE session_id = %s AND period_type = %s AND category IN (2,3,4,5,6,8)
            zsales_quarter_plan get error: Nc                 S   s&   i | ]}t |d  t|d pdqS )r   planned_valuer   )r(   r0   r   r   r   r    r,      s   & z,get_quarter_plan_summary.<locals>.<dictcomp>r   rF   r#   )r   plannedrG   r   )r9   r   rS   rT   rU   rV   rW   rX   fetchallr[   r\   r   r]   rY   r4   )r   r   rN   r.   r^   rW   r5   rb   planned_maprK   rO   r   rf   rG   r   r   r    get_quarter_plan_summary   s<   
"ri   c              
   C   s(  t | d}dd |D }i }|D ]O}|d}|du rqzt|dp$d}W n ttfy4   d}Y nw t|}||vrDddd||< |d	d
krR||| d
< q|d	dkr_||| d< qi }| D ]+\}	}
|
d
ppd}|
dpwd}|r|dkrt|| d dnd}|||d||	< qf|S )u   
    2s(팀 시트)에서 quarter, category in (2,3,4,5,6,8) 의 target/result 조회 후 팀 달성율 계산.
    Returns: { "2": { "target": 700, "result": 500, "rate": 71.4 }, ... }
    2sc                 S   r:   r;   r   r   r   r   r    r!      r<   z*get_team_quarter_rates.<locals>.<listcomp>r   Nr"   r   )r   r=   r   r   r=   rF   r#   )r   r=   rG   )r   r   r0   r1   r2   r(   rY   r4   )r   r5   r@   by_catr   r7   r8   rA   outrO   ra   r   r=   rG   r   r   r    get_team_quarter_rates   s6   

"rm   c                 C   s  t | }t| }|dpi }|dpdd tD }i }|D ]c}||p'i }||p.i }	|dp5d}
|dp<d}|	dpCd}|	dpJd}t|
| d}t|pVd|pYd d}tt|d	| |d
|d|
|	d
|	d||t|d	||< qt| pd}|||dS )u'  
    ユーザー達成率(1s result) vs チーム達成率 비교.
    Returns: {
      "by_category": {
        "2": {
          "category_name": "架電数",
          "user_target": 680, "user_result": 400, "user_rate": 58.8,
          "team_target": 700, "team_result": 500, "team_rate": 71.4,
          "diff": -12.6,   // user_rate - team_rate
          "user_result_minus_team": -100  // チームより〇〇件多く/少なく
        }, ...
      },
      "categories_order": ["2","3","4","5","6","8"],
      "username": "田中"
    }
    rK   rL   c                 S   rC   r   rD   r*   r   r   r    r!      rE   z'get_comparison_data.<locals>.<listcomp>rG   r   r=   r#   r&   r   )	rH   user_targetuser_result	user_rateteam_targetteam_result	team_ratediffuser_result_minus_teamrI   rJ   )rP   rm   r   r   r4   r)   r3   r	   )r   r   user_summary
team_ratesrk   rL   r=   rO   utrp   ro   rs   rr   rt   ru   rM   r   r   r    get_comparison_data   s4   rz   )r   )__doc__typingr   r   r   r   mysql.connectorrS   app.corer   app.core.configr   app.models.sales_sheet_moduler   app.models.general_moduler	   rV   r   r)   r(   r9   rB   rP   r3   rc   ri   rm   rz   r   r   r   r    <module>   s.    
" *"($ 