
    j                         d dl mZ d dlmZmZ d dlmZmZ dedede	fdZ
dededee         fdZd	 Zd
ededefdZd
edeee	f         fdZdefdZdS )    )logger)	query_all	query_one)build_age_exists_clauseget_age_filter_thresholdschematablereturnc                     d}	 t          t          || |d                    S # t          $ r"}t          j        d| ||           Y d }~dS d }~ww xY w)Nz
        SELECT 1 AS exists
        FROM information_schema.tables
        WHERE table_schema = %(s)s
          AND table_name = %(t)s
        LIMIT 1
    stz(Summary table probe failed for %s.%s: %sF)boolr   	Exceptionr   warningr   r	   sqlexcs       L/home/longshao/multi-rider-rag/modules/dashboard/services/summary_service.py_table_existsr      sr    CIce#<#<==>>>   A65RUVVVuuuuus   $ 
AAAc                     d}	 d t          || |d          D             S # t          $ r/}t          j        d| ||           t	                      cY d }~S d }~ww xY w)Nz
        SELECT column_name
        FROM information_schema.columns
        WHERE table_schema = %(s)s
          AND table_name = %(t)s
    c                 b    h | ],}|                     d           |                     d           -S )column_name)get).0rows     r   	<setcomp>z!_table_columns.<locals>.<setcomp>   s9    vvv3_b_f_fgt_u_uv&&vvv    r   z)Summary column probe failed for %s.%s: %s)r   r   r   r   setr   s       r   _table_columnsr       s    Cvv)CvTYAZAZ2[2[vvvv   BFESVWWWuus   ! 
A$AAAc                     |dv rd S t          t          | pd          t          |          z
  dz  t          |          z  d          S )N)Nr   r         Y@   )roundfloat)currentpreviouss     r   _change_pctr(   #   sJ    9t%1%%h75@5??RTUVVVr   where_clause
age_clausec                     d|  d| dS )Nzl
        SELECT COUNT(DISTINCT a."ajxx_ajbh") AS total
        FROM "ywdata"."zq_zfba_ajxx" a
        WHERE z
          z
     )r)   r*   s     r   _case_count_sqlr-   )   s/       	   r   c           	         t                      dk    rCt          t          | d                    }t          |                    d          pd          dfS t          t          | t          dd                              }t          |                    d          pd          }|r|dfS t          j        d|           t          t          | d                    }t          |                    d          pd          dfS )	Nr    totalFaxz?Summary case count fallback triggered, primary returned %d rowsT)r   r   r-   intr   r   r   info)r)   r   r0   fallbacks       r   _query_case_count_with_degrader6   2   s    !!Q&&b99::3777##(q))500
OL2I#s2S2STT
U
UC  %A&&E e|
KQSXYYYr::;;Hx||G$$)**D00r   c            
         d} d}d}d}t          |                               dd          }t          |                              dd          }t          d          \  }}t          d          \  }}	t          |                              dd          }
t          |                              d	d          }|||
||t          ||          |p|	|rt	          |          ndd
}t          dd          r>d}t          |                              dd          }||d<   t          ||          |d<   n
d |d<   d |d<   t          dd          rt          dd          t          fddD             d           }t          fddD             d           }|r|r|dk    rd}nd| d}d| d| d}	 t          |          }t          |                    d          pd          }t          |                    d          pd          }||d<   |rt          |dz  |z  d          nd |d <   n,# t          $ r}t          j        d!|           Y d }~nd }~ww xY w|S )"NzO
        SELECT COUNT(*) AS total FROM "jcgkzx_monitor"."wcnr_target_pool"
    zi
        SELECT COUNT(*) AS total FROM "jcgkzx_monitor"."wcnr_score"
        WHERE total_score >= 60
    zi
        SELECT COUNT(*) AS total FROM "jcgkzx_monitor"."wcnr_score"
        WHERE total_score >= 80
    z
        SELECT ROUND(AVG(total_score), 1) AS avg_score
        FROM "jcgkzx_monitor"."wcnr_score"
        WHERE total_score > 0
    r0   r   z2a."ajxx_fasj" >= DATE_TRUNC('month', CURRENT_DATE)za."ajxx_fasj" >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 month'
          AND a."ajxx_fasj" < DATE_TRUNC('month', CURRENT_DATE)	avg_score)total_personshigh_risk_countextreme_risk_countmonth_casesmonth_cases_prevmonth_cases_change_pctmonth_cases_degradedr8   jcgkzx_monitorwcnr_score_historyaN  
            WITH latest AS (
                SELECT zjhm, MAX(calc_time) AS calc_time
                FROM "jcgkzx_monitor"."wcnr_score_history"
                WHERE calc_time >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 month'
                  AND calc_time < DATE_TRUNC('month', CURRENT_DATE)
                GROUP BY zjhm
            )
            SELECT COUNT(*) AS total
            FROM latest l
            JOIN "jcgkzx_monitor"."wcnr_score_history" h
              ON h.zjhm = l.zjhm
             AND h.calc_time = l.calc_time
            WHERE h.total_score >= 60
        high_risk_count_prevhigh_risk_count_change_pct
wcnr_visitc              3   $   K   | ]
}|v |V  d S Nr,   r   colvisit_fieldss     r   	<genexpr>zget_summary.<locals>.<genexpr>   s/      yyehlxexexexexexexyyr   )
visit_time
visit_datecreate_time
created_atc              3   $   K   | ]
}|v |V  d S rF   r,   rG   s     r   rJ   zget_summary.<locals>.<genexpr>   s/      nnZ]amZmZmZmZmZmZmnnr   )is_passpass_statusstatusresultrP   z"is_pass" = TRUE"u+   " IN ('达标', '合格', 'pass', 'passed')zP
                SELECT COUNT(*) AS total,
                       SUM(CASE WHEN zh THEN 1 ELSE 0 END) AS passed
                FROM "jcgkzx_monitor"."wcnr_visit"
                WHERE "z3" >= DATE_TRUNC('month', CURRENT_DATE)
            passedvisit_total_monthr"   r#   visit_pass_ratezVisit summary skipped: %s)r   r   r6   r(   r%   r   r    nextr3   r$   r   r   r   )total_persons_sqlhigh_risk_sqlextreme_risk_sqlavg_score_sqlr9   	high_riskr<   r?   r=   month_cases_prev_degradedextreme_riskr8   rS   high_risk_prev_sqlhigh_risk_prevtime_colpass_col	pass_expr	visit_sqlvisitvisit_totalvisit_passedr   rI   s                          @r   get_summaryri   A   s7   MM /0044Wa@@M-((,,Wa88I(F>) )%K% 3Q	L3 3// -..227A>>L-((,,[!<<I '$*","-k;K"L"L 4 Q8Q)29U9%%%	 	F %';<< 4 ##566::7AFF)7%&/:9n/U/U+,,)-%&/3+,%|44 A%&6EEyyyy(ayyy{  A  Annnn(Vnnnptuu 	A 	A9$$.		]]]]	&/  !	  IA!),,!%))G"4"4"9::"599X#6#6#;!<<.9*+\g,qE,2F2TVW,X,X,Xmq()) A A A:C@@@@@@@@A Ms   4A9H. .
I8IIN)shared.config.configr   shared.db.kingbaser   r   shared.age_filterr   r   strr   r   r   r    r(   r-   tupler3   r6   dictri   r,   r   r   <module>rp      s/   ' ' ' ' ' ' 3 3 3 3 3 3 3 3 O O O O O O O O# c d    3 s s3x    W W W# 3 3    1 1sDy9I 1 1 1 1XT X X X X X Xr   