j5LddlmZddlZddlZddlZddlZddlmZddlmZddl m Z m Z m Z d:d Z d;dZddZd?d@dZdAd"ZdBdCd$ZdDd%ZdDd&ZdEdFd)Zd*ZdGd-ZdHd/ZdId0ZdJd3ZdKd6ZdLd7ZdDd8ZdMd9Z dS)N) annotationsN)datetime)logger)execute query_all query_oneschemastrtablereturnboolcd} tt|||dS#t$r"}tjd|||Yd}~dSd}~wwxYw)Nz SELECT 1 AS exists FROM information_schema.tables WHERE table_schema = %(s)s AND table_name = %(t)s LIMIT 1 stz*Table existence probe failed for %s.%s: %sF)r r Exceptionrwarningr r sqlexcs N/home/longshao/multi-rider-rag/modules/dashboard/services/alert_rule_engine.py _table_existsr sr CIce#<#<==>>> CVUTWXXXuuuuus$ AA  Atablestuple[str, str]c4td|DS)Nc3<K|]\}}t||VdSN)r).0r r s r z _tables_exist..s0HH }VU++HHHHHH)all)rs r _tables_existr"s HHHHH H HHr set[str]cd} dt|||dDS#t$r/}tjd|||t cYd}~Sd}~wwxYw)Nz SELECT column_name FROM information_schema.columns WHERE table_schema = %(s)s AND table_name = %(t)s c|h|]9}|dt|d:S) column_name)getr rrows r z!_table_columns..(sA{{{dgdkdklydzdz{CGGM**++{{{r rz!Column probe failed for %s.%s: %s)rrrrsetrs r_table_columnsr, s~ C {{yFY^F_F_7`7`{{{{ :FE3OOOuu s! A$AAAnamecTdt|ddzdzS)N"z"")r replace)r-s r _quote_identr1.s( T""3-- - 33r columns candidatestuple[str, ...] str | Nonecd|D}|D]4}||vr||cS5dS)Nc8i|]}||Slower)rcolumns r z&_pick_first_column..3s">>>F>>>r r9)r2r3 lower_map candidates r_pick_first_columnr?2s^>>g>>>I00 ??   ) )Y__../ / / / * 4r  alert_typezjhmlocationwindow_minutesintcddg}||d}|r|d||d<|r|d||d<dd | }tt ||S) Nzalert_type = %(alert_type)szMtrigger_time >= CURRENT_TIMESTAMP - make_interval(mins => %(window_minutes)s))rArDzzjhm = %(zjhm)srBzlocation = %(location)srCzx SELECT 1 AS exists FROM "jcgkzx_monitor"."wcnr_alert" WHERE {where_clause} LIMIT 1 z AND ) where_clause)appendformatjoinr r)rArBrCrD conditionsparamsrs r _alert_existsrM:s%WJ !(F +,,,v&3444%z  GLL4455   #v&& ' ''r xm alert_level alert_contentct|||rdSd}|||||||d} t||dkS#t$r"} tjd||| Yd} ~ dSd} ~ wwxYw)NFa INSERT INTO "jcgkzx_monitor"."wcnr_alert" (zjhm, xm, alert_type, alert_level, alert_content, location, trigger_time) VALUES (%(zjhm)s, %(xm)s, %(alert_type)s, %(alert_level)s, %(alert_content)s, %(location)s, %(trigger_time)s) rBrNrArOrPrC trigger_timerz'Insert alert failed type=%s zjhm=%s: %s)rMrrrr) rBrNrArOrPrCrSrrLrs r _insert_alertrTSsZx00u C "&$FsF##a'' @*dTWXXXuuuuus5 A!AA!c tddddsdSd}d}t|dtdt|pd iD]~}|d }|d pd }|d p|}|sHt ||dd|d|d||d}|r|dz }|S)Njcgkzx_monitor wcnr_ryrl_gjrX wcnr_scorerXwcnr_target_poolrX wcnr_alertra SELECT g.zjhm, p.xm, g.device_name, g.shot_time, s.total_score FROM "jcgkzx_monitor"."wcnr_ryrl_gj" g JOIN "jcgkzx_monitor"."wcnr_score" s ON s.zjhm = g.zjhm LEFT JOIN "jcgkzx_monitor"."wcnr_target_pool" p ON p.zjhm = g.zjhm WHERE g.shot_time >= CURRENT_TIMESTAMP - make_interval(mins => %(window_minutes)s) AND s.total_score >= 80 AND g.zjhm IS NOT NULL ORDER BY g.shot_time DESC LIMIT 200 rDrUrB device_nameu 未知设备rNhigh_risk_face_hitcriticalu 在 u 出现 shot_timerR)r"rmaxrEr'rT)rDrcountr)rBrarNinserteds rscan_high_risk_face_hitrhws *(.(    q C E/QN WWT]] "d   +"::k::: --      QJE Lr c tddddsdSd}d}t|D]}|dpd}t|d pd}t|d pd}t d|d pd d d|d|d|d||d}|r|dz }|S)NrWrZr\r^ru WITH recent AS ( SELECT g.device_name, g.zjhm, COALESCE(p.xm, g.zjhm) AS xm, g.shot_time, COALESCE(s.total_score, 0) AS total_score FROM "jcgkzx_monitor"."wcnr_ryrl_gj" g LEFT JOIN "jcgkzx_monitor"."wcnr_score" s ON s.zjhm = g.zjhm LEFT JOIN "jcgkzx_monitor"."wcnr_target_pool" p ON p.zjhm = g.zjhm WHERE g.shot_time >= CURRENT_TIMESTAMP - INTERVAL '30 minutes' AND (EXTRACT(HOUR FROM g.shot_time) >= 22 OR EXTRACT(HOUR FROM g.shot_time) < 6) AND g.device_name IS NOT NULL AND g.zjhm IS NOT NULL ) SELECT device_name, COUNT(DISTINCT zjhm) AS person_count, COUNT(DISTINCT CASE WHEN total_score >= 60 THEN zjhm END) AS high_risk_count, MAX(shot_time) AS last_time, STRING_AGG(DISTINCT xm, '、') AS names FROM recent GROUP BY device_name HAVING COUNT(DISTINCT zjhm) >= 3 AND COUNT(DISTINCT CASE WHEN total_score >= 60 THEN zjhm END) >= 2 ORDER BY last_time DESC LIMIT 50 rau 未知地点 person_counthigh_risk_countnamesu 多人聚集night_aggregationru 夜间聚集 u 人，其中高风险 u 人 last_timerRr`)r"rr'rErT)rrfr)rarjrkrgs rscan_night_aggregationros *(.(    q C: E~~ggm,,> 377>227a88 cgg&788=A>> www1>*!(rr rr]lrrr --      QJE Lr c tdddsdStdd}tdd}|rd nd }|rd nd }g}|r|d |r|d dg|r(dd|nd}d|||}d}t |D]}|d} |dp|dpd} | sF|dp| } t| | dd| d| d| |d} | r|dz }|S) N)rXwcnr_ly_checkinr\r^rywdatab_per_qskjwcnrrX wcnr_czrkz8LEFT JOIN "ywdata"."b_per_qskjwcnr" f ON f.zjhm = c.zjhmz;LEFT JOIN "jcgkzx_monitor"."wcnr_czrk" r ON r.zjhm = c.zjhmzf.jhr1xmzr.fqxmzr.mqxmz COALESCE({})z, z NULL::VARCHARa WITH checkins AS ( SELECT c.zjhm, p.xm, c.lgmc, c.lgdz, c.rzsj, c.tfrxm, {guardian_expr} AS guardian_name FROM "jcgkzx_monitor"."wcnr_ly_checkin" c JOIN "jcgkzx_monitor"."wcnr_target_pool" p ON p.zjhm = c.zjhm {family_join} {basic_join} WHERE c.rzsj >= CURRENT_TIMESTAMP - INTERVAL '24 hours' AND c.zjhm IS NOT NULL ) SELECT * FROM checkins WHERE NULLIF(BTRIM(COALESCE(tfrxm, '')), '') IS NULL OR guardian_name IS NULL OR tfrxm <> guardian_name ORDER BY rzsj DESC LIMIT 100 ) guardian_expr family_join basic_joinrBlgmclgdzu旅馆rNabnormal_hotel_checkinru 入住u，同住人异常rzsjrRr`) r"rrHextendrIrJrr'rT) has_family has_basicrwrxguardian_partsrvrrfr)rB hotel_namerNrgs rscan_abnormal_hotel_checkinrs -.(   qx)9::J. <= CURRENT_TIMESTAMP - INTERVAL '1 hour' AND sc.total_score >= 80 AND g.zjhm IS NOT NULL AND g.jd IS NOT NULL AND g.wd IS NOT NULL ), hits AS ( SELECT r.zjhm, r.xm, r.device_name, r.shot_time, school.xxmc, z AS dist FROM recent r CROSS JOIN school ) SELECT * FROM hits WHERE dist <= %(radius_m)s ORDER BY shot_time DESC LIMIT 200 rz&School perimeter rule query failed: %srBrNru学校distschool_perimeter_high_riskru 出现在学校 u 周边 ~u米rdrR)r"r,r?rinfor1rerE TypeError ValueErrorrrrr'roundfloatrT)rr2lng_collat_colname_colschool_name_exprlng_exprlat_expr safe_radius dist_exprrrfrowsrr)rBrNrdistancergs rscan_school_perimeterr s *(.("   qX~66G *NOOG *MNNG ' fgggq!'+hiiH8@P4L22444j+L))++H+L))++H!SS1122 z " I& $& & & &  & &   & & & & :;& & & CP Ez;788 ?EEEqqqqqwwv WWT]] "dggfoo1  U3776??#7a8899HH:&   HHH  3!UU+UUUUU --      QJE Ls<+C C! C!>D D;D66D;1F88G G)u飙车u 翘车头u炸街r)dictcJd}dfd|DS)N) category class_namelabeltype event_type illegal_type source_name device_idra summary_text classes_raw model_key c3`K|](}t|pdV)dS)ruN)r r')rfieldr)s rrz_row_text..s:BB%C,"--BBBBBBr )rJ)r)fieldss` r _row_textrs3F 88BBBB6BBB B BBr rcdD]B}||}| t|cS#ttf$rY?wxYwdS)N) confidenceconfscore probabilityg)r'rrrr)keyvalues r_row_confidencersi=  =  <<   :&    H  3s/AAcdD]i}||}|t|ttfr0 t j|cS#t tf$rYdSwxYw|cSt jS)N)rS detected_at created_atend_timeend_tsstart_ts) r' isinstancerErr fromtimestampOSErrorrnowrs r_row_trigger_timers^    =  ec5\ * *  -e44444Z(   ttt  <>>sAA&%A&min_confidencelist[dict] | Nonec tjd}n#t$rYdSwxYwdD]}t||d}t |s#d||dfd||dfdddifdiffD]^\}} ||i|}|d|DccS#t $rY-t$r&}t jd ||gcYd}~ccSd}~wwxYwt jd gS) Nz3modules.detection.repositories.ai_result_repository)list_recent_resultsget_recent_resultsquery_recent_resultsfind_recent_resultsr8)rDr)minutesrlimitrc,g|]}t|Sr8)rr(s r z._call_detection_repository..s222cS 222r z'Detection repository call %s failed: %sz=Detection repository has no supported recent-result interface) importlib import_modulergetattrcallablerrrr) rDrrepor-funcargskwargsrrs r_call_detection_repositoryrsy&'\]] ttmtT4((~~   NnUU V ^>JJ K '3 H    LD& tT,V,,<22T22222222      H$PSTTT     KOPPP Is6 %%) B4 B B? B?B:0B?:B?job list[dict]c|d}|rtj|sgS t |dd5}t j|}dddn #1swxYwYn/#t$r"}tj d||gcYd}~Sd}~wwxYwg}|dpgD]}t|ts|dp+|dp|d pg}|ri|D]e}t|trNt|} | || || | ft|} | || | |S) Nresult_manifest_pathrzutf-8)encodingz%Read detection manifest failed %s: %sitems detectionsboxesresults)r'ospathisfileopenjsonloadrrrrrupdaterH) r manifest_pathfhmanifestrritemr detectionr)s r_manifest_detection_rowsrsGG233M } = =  -w 7 7 7 %2y}}H % % % % % % % % % % % % % % % > sSSS  D W%%+$%%  XXl++]txx/@/@]DHHYDWDW][]  ' % % i..%s))CJJt$$$JJy)))KK$$$  %s))C JJt    KK     KsAA9 A-! A9-A11A94A15A99 B%B B% B%c ddlm}n.#t$r!}tjd|gcYd}~Sd}~wwxYwt jt dt|pddzz }g} |d}n.#t$r!}tjd |gcYd}~Sd}~wwxYw|D] }| d p| d pd} t|pd|krFn#ttf$rYZwxYwt| d pd dvrt|} | r|| t| dpddkrt!|} t| dpd| d<|| "fd|DS)Nr) list_all_jobsz+SQLite detection job reader unavailable: %sr`rU<d)rzList detection jobs failed: %srrstatusru>done completedkept conf_threshrc:g|]}t|k|Sr8)r)rr)rs rrz*_sqlite_detection_rows.. s+ J J JC?3#7#7>#I#IC#I#I#Ir )shared.db.sqliterrrrtimererErr'rrrr rr}rrH) rDrrrcutoffrjobsrr manifest_rowsr)s ` r_sqlite_detection_rowsrs@2222222  A3GGG Y[[3q#n&9":":;;b@ @F D}3''' 7=== "">cggj&9&9>Q V[q!!F**+:&    H  swwx  &B ' '/D D D 055   KK & & &  swwv#! $ $ ) ) 3ii!#''-"8"8"=A>>L C J J J J4 J J JJsD 50551 A>> B)B$B)$B)C55D D c V tddsdSd}d}t||}|st||}|stjddSd}|D]Q}t | t |dpd}t fd tDs|d krot|}||kr|d p)|d p|d }|st|d|dddd||d p#|d pt |t|}|r|dz }S|S)NrXr_rrUg333333?zOSpeeding detection rule skipped: no detection repository rows or SQLite resultsrruc3 K|]}|vV dSrr8)rkeywordtexts rrz*scan_speeding_detection..s'DDw7d?DDDDDDr bczjrrarrBrNspeeding_detectedru飙车检测命中 device=rRr`)rrrrrrr r'r:anySPEEDING_KEYWORDSrrTr) rDrrrfr)rrrrgrs @rscan_speeding_detectionr  s )< 8 8qNN %nn E ED F%nnEE  efffq E~~ ,,23399;; DDDD2CDDDDD V\I\I\ $S))  & & GGK((\CGGM,B,B\cggmF\F\    wwt}}*!ByBBWW]++Wsww}/E/EWY*3//      QJE Lr ctttttd}i}|D]E\}} |||<#t $r%}tjd||d||<Yd}~>d}~wwxYw|S)N)rbrmr{rr zAlert rule %s failed: %sr) rhrorrr rrrr)rulesresultr-rrs r run_all_rulesr4s53"=&;4   EFkkmm d 466F4LL    N5tS A A AF4LLLLLL  Ms A  A;A66A;)r r r r r r )rrr r )r r r r r r#)r-r r r )r2r#r3r4r r5)r@) rAr rBr5rCr5rDrEr r )rBr5rNr5rAr rOr rPr rCr5r r )rU)rDrEr rE)r rE)r)rrEr rE)r)rr r )r)rr r)r)r)rDrErrr r)rrr r)rDrErrr r)r r)! __future__rrrrrrshared.config.configrshared.db.kingbaserrrrr"r,r1r?rMrTrhrorrr rrrrrrr rr8r rrs9"""""" ''''''<<<<<<<<<<    IIII    4444(((((2!!!!H(((((V6666rBBBBJdddddN6CCCC        <