
    j#                         d dl mZmZ d Zd Zddededee         fdZ	dde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dededee         fdZddededee         fdZdS )    )	query_all	query_onec                 p    | d S t          | d          r|                                 S t          |           S )N	isoformat)hasattrr   strvalues    H/home/longshao/multi-rider-rag/modules/graph/services/relation_engine.py_format_timer      s:    }tuk"" !   u::    c                 \    | d S 	 t          |           S # t          t          f$ r | cY S w xY w)N)float	TypeError
ValueErrorr	   s    r   	_to_floatr      sF    }tU||z"   s    ++   zjhmlimitreturnc                 "   d}t          || |d          }g }|D ]}|                    d          }|sd| }|                    |d|||                    d          t          |                    d                    t	          |                    d                    t	          |                    d	                    d
dd|  |dd|                    d          t          |                    d                    ddd           |S )NaO  
        SELECT device_name, COUNT(*) AS count, MAX(shot_time) AS last_time,
               AVG(jd) AS jd, AVG(wd) AS wd
        FROM "jcgkzx_monitor"."wcnr_ryrl_gj"
        WHERE zjhm = %(zjhm)s
          AND device_name IS NOT NULL
        GROUP BY device_name
        ORDER BY count DESC, last_time DESC
        LIMIT %(limit)s
    r   r   device_nameL_locationcount	last_timejdwd)r   r   r   r   r   idtypelabel
propertiesP_APPEARED_ATu   出现r   r   sourcetargetr"   r#   r$   nodeedge)r   getappendr   r   )r   r   sqlrowsresultsrowr   node_ids           r   appeared_atr5      sI   	C S4%8899DG  ggm,, 	${$$"$#. WWW--!-cggk.B.B!C!C#CGGDMM22#CGGDMM22 	  &t++!%! WWW--!-cggk.B.B!C!C 	 	
 
 	 	 	 	0 Nr      c                    d}t          || |d          }g }|D ]}|                    d          }|sd| }|                    |d|||                    d          |                    d          t          |                    d                    d	d
d|  |dd|                    d          t          |                    d                    ddd           |S )Na  
        SELECT lgmc, lgdz, COUNT(*) AS count, MAX(rzsj) AS last_time
        FROM "jcgkzx_monitor"."wcnr_ly_checkin"
        WHERE zjhm = %(zjhm)s
          AND lgmc IS NOT NULL
        GROUP BY lgmc, lgdz
        ORDER BY count DESC, last_time DESC
        LIMIT %(limit)s
    r   lgmcO_organizationlgdzr   r   )r8   r;   r   r   r    r%   
CHECKED_INu   入住r'   r(   r+   )r   r.   r/   r   )r   r   r0   r1   r2   r3   
hotel_namer4   s           r   
checked_inr>   B   s*   C S4%8899DG  WWV__
 	#z##&#&GGFOO WWW--!-cggk.B.B!C!C	 	
 
 &t++!$! WWW--!-cggk.B.B!C!C 	 	
 
 	 	 	 	. Nr   ajbhc           	         d}t          |d| i          }g }|D ]}|                    d          p|                    d          }|s/|                    ||                    d          p|                    d          |                    d          p|                    d          |                    d	          p|                    d
          d           |S )Naq  
        SELECT DISTINCT
               s."saryxx_sfzh" AS zjhm,
               s."saryxx_xm" AS xm,
               s."saryxx_csrq" AS csrq,
               s."saryxx_shfd" AS shfd
        FROM "ywdata"."zq_zfba_saryxx" s
        WHERE POSITION(%(ajbh)s IN COALESCE(s."ajxx_ajbhs", '')) > 0
          AND NULLIF(BTRIM(COALESCE(s."saryxx_sfzh", '')), '') IS NOT NULL
    r?   r   saryxx_sfzhxm	saryxx_xmcsrqsaryxx_csrqshfdsaryxx_shfd)r   rB   rD   rF   )r   r.   r/   )r?   r0   r1   r2   r3   r   s         r   victims_of_caserH   m   s    	C S64.))DG 	 	wwv8#''-"8"8 	''$--7377;#7#7GGFOO=sww}'='=GGFOO=sww}'='=	
 
 	 	 	 	 Nr   c                 \   d}t          |d| i          }|sg S g }dD ]2}|                    |          }|r||vr|                    |           3g }|D ]W}d| }|                    |d||||                    d          |                    d          dd	d
|  |dddd           X|S )Nzx
        SELECT hjdz, xzdxz
        FROM "jcgkzx_monitor"."wcnr_czrk"
        WHERE zjhm = %(zjhm)s
        LIMIT 1
    r   )xzdxzhjdzr   r   rK   rJ   )nameaddressrK   rJ   r    r%   LIVES_ATu   居住)r)   r*   r"   r#   r+   )r   r.   r/   )	r   r0   r3   	addressesfieldr
   r2   rM   r4   s	            r   lives_atrQ      s   C C&$
(
(C 	I" $ $ 	$U)++U###G   w.." #&GGFOO WWW--	 	
 
 &t++!"!	 
 
 	 	 	 	& Nr   c                 6   d}t          |d| i          }|pi                     d          }|s+d}t          |d| i          }|pi                     d          }|sg S d}g }t          || ||d          D ]}|                    d          }|sd| }	|                    |	d|                    d	          p	|d d
         ||                    d	          ||                    d          |                    d          ddd|  |	ddd|idd           |S )Nz
        SELECT yxx
        FROM "ywdata"."b_per_qscxwcnr"
        WHERE zjhm = %(zjhm)s
          AND NULLIF(BTRIM(COALESCE(yxx, '')), '') IS NOT NULL
        LIMIT 1
    r   yxxz
            SELECT yxx
            FROM "ywdata"."zq_zfba_wcnr_sfzxx"
            WHERE sfzhm = %(zjhm)s
              AND NULLIF(BTRIM(COALESCE(yxx, '')), '') IS NOT NULL
            LIMIT 1
        a  
        SELECT DISTINCT p.zjhm,
               COALESCE(p.xm, c.xm, s.xm) AS xm,
               sc.total_score,
               sc.risk_level
        FROM "jcgkzx_monitor"."wcnr_target_pool" p
        LEFT JOIN "ywdata"."b_per_qscxwcnr" c ON c.zjhm = p.zjhm
        LEFT JOIN "ywdata"."zq_zfba_wcnr_sfzxx" s ON s.sfzhm = p.zjhm
        LEFT JOIN "jcgkzx_monitor"."wcnr_score" sc ON sc.zjhm = p.zjhm
        WHERE p.zjhm <> %(zjhm)s
          AND (c.yxx = %(school)s OR s.yxx = %(school)s)
        LIMIT %(limit)s
    )r   schoolr   r%   personrB      total_score
risk_level)r   rB   rT   
risk_scorerX   r    SAME_SCHOOLu   同校rT   r(   r+   r   r.   r   r/   )
r   r   
school_sqlr3   school_namesfz_sql	peers_sqlr2   	peer_zjhmr4   s
             r   same_schoolra      s   J J
/
/C9"//%((K 	- &$00ybooe,, 	I GT[SX$Y$YZZ  GGFOO	 	"y"" 7)BQB-%''$--)"%''-"8"8"%'',"7"7 	  &t++!%!'5 
 
 	 	 	 	* Nr   c                    d}t          |d| i          }|pi                     d          }|sg S d}g }t          || ||d          D ]}|                    d          }|sd| }|                    |d|                    d          p	|d d	         ||                    d          ||                    d
          |                    d          ddd|  |ddd|idd           |S )Nz
        SELECT sspcs
        FROM "jcgkzx_monitor"."wcnr_target_pool"
        WHERE zjhm = %(zjhm)s
          AND sspcs IS NOT NULL
        LIMIT 1
    r   sspcsaE  
        SELECT p.zjhm, p.xm, sc.total_score, sc.risk_level
        FROM "jcgkzx_monitor"."wcnr_target_pool" p
        LEFT JOIN "jcgkzx_monitor"."wcnr_score" sc ON sc.zjhm = p.zjhm
        WHERE p.sspcs = %(sspcs)s
          AND p.zjhm <> %(zjhm)s
        ORDER BY sc.total_score DESC NULLS LAST
        LIMIT %(limit)s
    )r   rc   r   r%   rU   rB   rV   rW   rX   )r   rB   rc   rY   rX   r    	SAME_AREAu	   同辖区r(   r+   r[   )	r   r   area_sqlr3   	area_namer_   r2   r`   r4   s	            r   	same_arearg      sP   H Hvtn
-
-C((I 	I GTIPU$V$VWW  GGFOO	 	"y"" 7)BQB-%''$--&"%''-"8"8"%'',"7"7 	  &t++!#$&	2 
 
 	 	 	 	* Nr   N)r   )r6   )shared.db.kingbaser   r   r   r   r   intlistdictr5   r>   rH   rQ   ra   rg    r   r   <module>rm      s]   3 3 3 3 3 3 3 3    * *c *# *d4j * * * *Z( (S ( (T$Z ( ( ( (V# $t*    4'3 '4: ' ' ' 'T? ?c ?# ?d4j ? ? ? ?D1 1C 1 1DJ 1 1 1 1 1 1r   