
    j                     H    d dl Z d dlmZmZmZ d Zd Zd Z	 	 dd
ZddZ	dS )    N)	query_one	query_allexecutec	                 f    d}	t          |	| |||||||t          j        |d          d	           d S )Na_  
        INSERT INTO "jcgkzx_monitor"."wcnr_score"
            (zjhm, total_score, risk_level, dim_case, dim_behavior,
             dim_family, dim_education, dim_social, calc_time, detail_json)
        VALUES
            (%(zjhm)s, %(total_score)s, %(risk_level)s, %(dim_case)s, %(dim_behavior)s,
             %(dim_family)s, %(dim_education)s, %(dim_social)s, CURRENT_TIMESTAMP, %(detail_json)s)
        ON CONFLICT (zjhm) DO UPDATE SET
            total_score = EXCLUDED.total_score,
            risk_level = EXCLUDED.risk_level,
            dim_case = EXCLUDED.dim_case,
            dim_behavior = EXCLUDED.dim_behavior,
            dim_family = EXCLUDED.dim_family,
            dim_education = EXCLUDED.dim_education,
            dim_social = EXCLUDED.dim_social,
            calc_time = CURRENT_TIMESTAMP,
            detail_json = EXCLUDED.detail_json
    F)ensure_ascii)	zjhmtotal_score
risk_leveldim_casedim_behavior
dim_familydim_education
dim_socialdetail_json)r   jsondumps)
r   r	   r
   r   r   r   r   r   r   sqls
             D/home/longshao/multi-rider-rag/modules/score/services/score_store.pyupsert_scorer      sY    C$ C[
l = KV[1\1\1\	          c                 2    d}t          || ||d           d S )Nz
        INSERT INTO "jcgkzx_monitor"."wcnr_score_history"
            (zjhm, total_score, risk_level)
        VALUES (%(zjhm)s, %(total_score)s, %(risk_level)s)
    )r   r	   r
   )r   )r   r	   r
   r   s       r   append_historyr   !   s+    C
 C${*UUVVVVVr   c                 *    d}t          |d| i          S )Nz
        SELECT s.*, p.xm
        FROM "jcgkzx_monitor"."wcnr_score" s
        LEFT JOIN "jcgkzx_monitor"."wcnr_target_pool" p ON p.zjhm = s.zjhm
        WHERE s.zjhm = %(zjhm)s
    r   )r   )r   r   s     r   	get_scorer   *   s    C S64.)))r   d         descc                 t   ddg}| |d}|r|                     d           ||d<   |r|                     d           ||d<   d                    |          }	|d	k    rd
nd}
d|	 d}t          ||                              dd          }|dz
  |z  }||d<   ||d<   d|	 d|
 d}t	          ||          }||fS )Nzs.total_score >= %(min_score)szs.total_score <= %(max_score)s)	min_score	max_scorezs.risk_level = %(risk_level)sr
   zp.ssfjdm = %(area_code)s	area_codez AND r   DESCASCz
        SELECT COUNT(*) AS total
        FROM "jcgkzx_monitor"."wcnr_score" s
        LEFT JOIN "jcgkzx_monitor"."wcnr_target_pool" p ON p.zjhm = s.zjhm
        WHERE z
    totalr   r   limitoffsetaZ  
        SELECT s.zjhm, p.xm, p.xb, p.source_type, p.ssfj, p.sspcs,
               s.total_score, s.risk_level, s.dim_case, s.dim_behavior,
               s.dim_family, s.dim_education, s.dim_social, s.calc_time
        FROM "jcgkzx_monitor"."wcnr_score" s
        LEFT JOIN "jcgkzx_monitor"."wcnr_target_pool" p ON p.zjhm = s.zjhm
        WHERE z 
        ORDER BY s.total_score z/
        LIMIT %(limit)s OFFSET %(offset)s
    )appendjoinr   getr   )r    r!   r
   r"   pagesizesort
conditionsparamswhere_clauseorder	count_sqlr%   r'   list_sqlitemss                   r   get_score_listr5   4   s,   24TUJ$9==F *9:::)| (4555'{<<
++LfnnFF%E 	  I i((,,Wa88EQh$FF7OF8	 	 	 !&	 	 	H h''E%<r      c                 ,    d}t          || |d          S )Nz
        SELECT total_score, risk_level, calc_time
        FROM "jcgkzx_monitor"."wcnr_score_history"
        WHERE zjhm = %(zjhm)s
          AND calc_time >= CURRENT_TIMESTAMP - make_interval(months => %(months)s)
        ORDER BY calc_time
    )r   months)r   )r   r8   r   s      r   get_score_trendr9   ^   s#    C S46::;;;r   )r   r   NNr   r   r   )r6   )
r   shared.db.kingbaser   r   r   r   r   r   r5   r9    r   r   <module>r<      s     < < < < < < < < < <  8W W W* * * ;?9?' ' ' 'T< < < < < <r   