
    ji!                     R   d dl mZ d dlmZ d dlmZmZ d dlmZ de	de
fdZde	de
fdZde	de
fd	Zde	de
fd
Zde	dee
         fdZde	dee
         fdZde	dee
         fdZde	dee
         fdZde	de
fdZddedee
         fdZde	de	dz  dee
         de
fdZde	de
fdZdS )    )combinations)logger)	query_one	query_all)get_score_trendzjhmreturnc                 ^    d}t          |d| i          }|sd}t          |d| i          }|pi S )Nz
        SELECT c.zjhm, c.xm, c.xb, c.mz, c.csrq, c.hjdz, c.xzdxz, c.whcd,
               c.fqxm, c.fqzjhm, c.mqxm, c.mqzjhm, c.lxdh
        FROM "jcgkzx_monitor"."wcnr_czrk" c
        WHERE c.zjhm = %(zjhm)s
    r   z
            SELECT zjhm, xm, xb, csrq, ssfj, sspcs
            FROM "jcgkzx_monitor"."wcnr_target_pool"
            WHERE zjhm = %(zjhm)s
        r   )r   sqlrowpool_sqls       L/home/longshao/multi-rider-rag/modules/profile/services/profile_assembler.pyget_basic_infor      sL    C C&$
(
(C 2
 64.119"    c                 *    d}t          |d| i          S )Nzq
        SELECT zjhm, zp, zp_source
        FROM "jcgkzx_monitor"."wcnr_rk_zp"
        WHERE zjhm = %(zjhm)s
    r   r   r   r   s     r   	get_photor      s    C
 S64.)))r   c                 P    d}	 t          |d| i          S # t          $ r i cY S w xY w)Nz
        SELECT knjtlx, etlb, fmsftswc, jhr1xm, jhr1lxdh, fxdj
        FROM "ywdata"."b_per_qskjwcnr"
        WHERE zjhm = %(zjhm)s
        LIMIT 1
    r   )r   	Exceptionr   s     r   get_family_infor   #   sF    Cvtn---   			s    %%c                     d}d}d}d}t          |d| i          }|rddi|S t          |d| i          }|rddi|S t          |d| i          }|rdd	i|S t          |d| i          }|rdd
i|S ddiS )Nz
        SELECT
            zjhm,
            xm,
            NULL::VARCHAR AS yxx,
            NULL::VARCHAR AS nj,
            jxqk,
            NULL::VARCHAR AS ssbm
        FROM "ywdata"."b_per_qscxwcnr"
        WHERE zjhm = %(zjhm)s LIMIT 1
    zv
        SELECT zjhm, xm, jxqk, fxdj
        FROM "ywdata"."b_per_qskjwcnr"
        WHERE zjhm = %(zjhm)s LIMIT 1
    zn
        SELECT zjhm, xm, ly
        FROM "ywdata"."b_per_qslswcnr"
        WHERE zjhm = %(zjhm)s LIMIT 1
    zz
        SELECT yxx, nj, jzyy, whdj
        FROM "ywdata"."zq_zfba_wcnr_sfzxx"
        WHERE sfzhm = %(zjhm)s LIMIT 1
    r   statusdropoutlosttruantenrolledunknownr   )	r   dropout_sql
truant_sqllost_sqlsfz_sqlr   r   r   sfzs	            r   get_education_infor$   0   s    
KJ
H
G fd^44G 0)/w//X~..D *&)D))zFD>22F .(-f--
Gfd^
,
,C
 -*,,,i  r   c                 *    d}t          |d| i          S )Na>  
        SELECT a."ajxx_ajbh", a."ajxx_ajmc", a."ajxx_ay",
               a."ajxx_fasj", a."ajxx_cbdw_mc"
        FROM "ywdata"."zq_zfba_ajxx" a
        JOIN "ywdata"."zq_zfba_xyrxx" x
          ON x."ajxx_join_ajxx_ajbh" = a."ajxx_ajbh"
        WHERE x."xyrxx_sfzh" = %(zjhm)s
        ORDER BY a."ajxx_fasj" DESC
    r   r   r   s     r   	get_casesr'   _   s    C S64.)))r   c                 *    d}t          |d| i          S )Nz
        SELECT wf_sj, wfxw_cn, blxwlx_cn, fsdd, cphm
        FROM "ywdata"."t_wcnrxwjl_xx"
        WHERE sfzhm = %(zjhm)s
        ORDER BY wf_sj DESC
        LIMIT 20
    r   r&   r   s     r   get_behaviorsr)   l       C S64.)))r   c                 *    d}t          |d| i          S )Nz
        SELECT lgmc, lgdz, rzsj, lksj, tfrxm, tfrzjhm
        FROM "jcgkzx_monitor"."wcnr_ly_checkin"
        WHERE zjhm = %(zjhm)s
        ORDER BY rzsj DESC
        LIMIT 10
    r   r&   r   s     r   get_hotel_recordsr,   w   r*   r   c                 *    d}t          |d| i          S )Na  
        SELECT x2."xyrxx_sfzh" AS zjhm, x2."xyrxx_xm" AS xm,
               COUNT(*) AS case_count
        FROM "ywdata"."zq_zfba_xyrxx" x1
        JOIN "ywdata"."zq_zfba_xyrxx" x2
          ON x2."ajxx_join_ajxx_ajbh" = x1."ajxx_join_ajxx_ajbh"
          AND x2."xyrxx_sfzh" <> x1."xyrxx_sfzh"
        WHERE x1."xyrxx_sfzh" = %(zjhm)s
          AND NULLIF(BTRIM(COALESCE(x2."xyrxx_sfzh", '')), '') IS NOT NULL
        GROUP BY x2."xyrxx_sfzh", x2."xyrxx_xm"
        ORDER BY case_count DESC
        LIMIT 10
    r   r&   r   s     r   get_co_suspectsr.      s    C S64.)))r   c                 *    d}t          |d| i          S )Nz
        SELECT total_score, risk_level, dim_case, dim_behavior,
               dim_family, dim_education, dim_social, calc_time
        FROM "jcgkzx_monitor"."wcnr_score"
        WHERE zjhm = %(zjhm)s
    r   r   r   s     r   get_score_infor0      s    C S64.)))r      limitc                     d}	 t          |d| i          S # t          $ r!}t          j        d|           g cY d}~S d}~ww xY w)u9  Monitored people ordered by risk score — powers the demo landing page list.

    Anchored on the target pool (the monitored population). Risk score/level are
    taken from wcnr_score when available, otherwise from the pool's own columns,
    so the list still works whichever table the demo data lives in.
    u  
        SELECT t.zjhm,
               COALESCE(NULLIF(BTRIM(t.xm), ''), '未知') AS xm,
               t.xb,
               t.csrq,
               COALESCE(NULLIF(BTRIM(t.sspcs), ''),
                        NULLIF(BTRIM(t.ssfj), ''), '') AS area,
               COALESCE(s.total_score, t.risk_score, 0) AS total_score,
               COALESCE(NULLIF(s.risk_level, ''),
                        NULLIF(t.risk_level, ''), 'normal') AS risk_level
        FROM "jcgkzx_monitor"."wcnr_target_pool" t
        LEFT JOIN "jcgkzx_monitor"."wcnr_score" s ON s.zjhm = t.zjhm
        ORDER BY COALESCE(s.total_score, t.risk_score, 0) DESC, t.zjhm
        LIMIT %(limit)s
    r2   z Featured people query failed: %sN)r   r   r   warning)r2   r   excs      r   get_featured_peopler6      sd    Cw.///   93???						s    
A<AAxmNco_suspectsc           	      .     |pdi|D ]3}|                     d          }|r|                     d          pd|<   4t                    dk     rddg dS d t                    D             }d	                    d
 t	          t                              D                       }d| d}t          ||          }d D             }	|D ]z}
|
                     d          }|
                     d          }||	v rJ|rH|	|                             |           |
                     d          r|
                     d          |<   {t                                                    }t                      t          |d          D ]n\  }}|	                     |t                                |	                     |t                                z  r$                    t          ||f                     ot	          t          |          dd          D ]y}t          ||          D ]f vrt          fdt          d          D                       r6 g fd|D             z   }dt          |          fd|D             dc c S gzddg dS )N r   r7      Fr   )is_gangsizemembersc                      i | ]\  }}d | |S )p ).0iperson_zjhms      r   
<dictcomp>zdetect_gang.<locals>.<dictcomp>   s$    OOO~q+g!gg{OOOr   z, c                     g | ]}d | d	S )z%(pz)srA   )rB   rC   s     r   
<listcomp>zdetect_gang.<locals>.<listcomp>   s     JJJakAkkkJJJr   z
        SELECT x."ajxx_join_ajxx_ajbh" AS ajbh,
               x."xyrxx_sfzh" AS zjhm,
               x."xyrxx_xm" AS xm
        FROM "ywdata"."zq_zfba_xyrxx" x
        WHERE x."xyrxx_sfzh" IN (zP)
          AND NULLIF(BTRIM(COALESCE(x."xyrxx_sfzh", '')), '') IS NOT NULL
    c                 ,    i | ]}|t                      S rA   )set)rB   rD   s     r   rE   zdetect_gang.<locals>.<dictcomp>   s    HHHk{CEEHHHr   ajbh   c              3   :   K   | ]}t          |          v V  d S )N)	frozenset)rB   pairlinked_pairss     r   	<genexpr>zdetect_gang.<locals>.<genexpr>   s.      VVt9T??l2VVVVVVr   c                 (    g | ]}|k    |v |S rA   rA   )rB   rD   groupr   s     r   rG   zdetect_gang.<locals>.<listcomp>   s0    #z#z#zK[\`M`M`eptyeyeyKeyeyeyr   Tc                 B    g | ]}|                     |d           dS )r:   )r   r7   )get)rB   rD   
member_maps     r   rG   zdetect_gang.<locals>.<listcomp>   s1    xxxalJNN;XZ<[<[ \ \xxxr   )rU   len	enumeratejoinranger   addlistkeysrI   r   rN   all)r   r7   r8   itemco_zjhmparamsplaceholdersr   rowscases_by_personr   row_zjhmrJ   peopleleftrightr=   orderedrS   rP   rV   s   `                 @@@r   detect_gangrj      s   b!J 7 7((6"" 	7"&((4.."6BJw
: !;;;OO:9N9NOOOF99JJ5Z3I3IJJJKKL
 #/  C S&!!DHHZHHHO 5 5776??wwv&&4&H%))$///wwt}} 5'*wwt}}
8$*//##$$F55L#FA.. 7 7etSUU++o.A.A%.O.OO 	7Ye}55666c&kk1b)) 
 
!&$// 		 		E5  VVVV|EST?U?UVVVVV &#z#z#z#z#z6#z#z#zz#LLxxxxpwxxx      		 aB777r   c                    t          |           }|si S t          |           }	 t          | d          }n/# t          $ r"}t	          j        d| |           g }Y d }~nd }~ww xY w|t          |           t          |           t          |           t          |           t          |           t          |           |t          | |                    d          |          dt          |           |d
S )N   zScore trend skipped for %s: %sr7   )r8   gang)
basicphotofamily	educationcases	behaviorshotels	relationsscorescore_trend)r   r.   r   r   r   r4   r   r   r$   r'   r)   r,   rj   rU   r0   )r   rn   r8   rw   r5   s        r   assemble_profilerx      s
   4  E 	!$''K%dA..   7sCCC
 4!$'''--4"4((#D))&eiioo{CC
 
  %%"  s   5 
A!AA!)r1   )	itertoolsr   shared.config.configr   shared.db.kingbaser   r   "modules.score.services.score_storer   strdictr   r   r   r$   r\   r'   r)   r,   r.   r0   intr6   rj   rx   rA   r   r   <module>r      s&   " " " " " " ' ' ' ' ' ' 3 3 3 3 3 3 3 3 > > > > > >     $*C *D * * * *
# 
$ 
 
 
 
,!S ,!T ,! ,! ,! ,!^
*C 
*DJ 
* 
* 
* 
** *T
 * * * **C *DJ * * * **# *$t* * * * *"* * * * * * s DJ    :08c 08sTz 08T
 08t 08 08 08 08f3 4      r   