ID_, EVENT_NO_, EVENT_TEL_, EVENT_TIME_, EVENT_TYPE_, EVENT_SRC_, INSPECT_ID_, ROAD_ID_, ROAD_NAME_, ROAD_CODE_, DIRECTION_, PEG_S_VAL_, PEG_E_VAL_, LNG_, LAT_, START_PATROL_ID_, START_PATROL_NAME_, END_PATROL_ID_, END_PATROL_NAME_, EVENT_LEVEL_, HOLDUP_USER_NUM_, HURT_USER_NUM_, DEAD_USER_NUM_, BROKEN_CAR_NUM_, HOLDUP_CAR_NUM_, BLOCK_LENGTH_, LOSS_, CAUSE_, CAUSE_CAR_, MEMO_, TRAFFIC_TEAM_, TEAM_ID_, TEAM_FULL_ID_, EVENT_STATUS_, UPLOAD_ID_, UPLOAD_TEAM_ID_, UPLOAD_TEAM_FULL_ID_, UPLOAD_TEAM_NAME_, UPLOAD_USER_ID_, UPLOAD_USER_NAME_, UPLOAD_TIME_, CREATE_BY_, CREATE_ORG_ID_, CREATE_ORG_NAME_, CREATE_COMPANY_ID_, CREATE_COMPANY_NAME_, CREATE_NAME_, CREATE_TIME_, UPDATE_BY_, UPDATE_NAME_, UPDATE_COMPANY_NAME_, UPDATE_COMPANY_ID_, UPDATE_ORG_ID_, UPDATE_ORG_NAME_, UPDATE_TIME_, LAST_TIME_, IS_DELE_, VERSION_, TENANT_ID_ SELECT info.*, o.NAME_ AS teamName FROM biz_event_info info LEFT JOIN uc_org o ON info.TEAM_ID_=o.ID_ ${ew.customSqlSegment} with handle_node_ as ( select h.event_id_,h.event_node_ from biz_event_handle h join (select max(create_time_) AS create_time_,max(event_node_) AS event_node_,event_id_ from biz_event_handle where is_dele_='0' group by event_id_)t on h.event_id_=t.event_id_ and h.create_time_=t.create_time_ and h.event_node_=t.event_node_ and h.is_dele_='0' ), power_ as ( select bm.team_id_,tm.id_ from biz_base_team_member tm join biz_bind_member bm on tm.id_=bm.member_id_ where tm.is_dele_='0' and user_id_ = #{userId} ) select e.*,h.event_node_ AS eventNode from biz_event_info e join power_ tm on e.team_id_=tm.team_id_ and (e.accept_user_id_ is null or (e.accept_user_id_ is not null and e.accept_user_id_=tm.id_)) left join handle_node_ h on e.id_=h.event_id_ ${ew.customSqlSegment} WITH control_info AS ( SELECT event_id_, count( event_id_ ) AS number_ FROM biz_traffic_control WHERE is_dele_ = '0' GROUP BY event_id_ ) SELECT ei.*, CASE WHEN ci.number_ > 0 THEN '是' ELSE '否' END AS isExitTrafficControl FROM biz_event_info ei LEFT JOIN control_info ci ON ei.id_ = ci.event_id_ ${ew.customSqlSegment} with handle_node_ as ( select h.event_id_,h.event_node_ from biz_event_handle h join (select max(create_time_) AS create_time_,max(event_node_) AS event_node_,event_id_ from biz_event_handle where is_dele_='0' group by event_id_)t on h.event_id_=t.event_id_ and h.create_time_=t.create_time_ and h.event_node_=t.event_node_ and h.is_dele_='0' ) select e.*,h.event_node_ AS eventNode from biz_event_info e left join handle_node_ h on e.id_=h.event_id_ ${ew.customSqlSegment} update biz_event_info set EVENT_STATUS_=#{status} ,ACCEPT_USER_ID_= #{acceptUserId} where ID_=#{id} and IS_DELE_='0' SELECT sum( CASE WHEN type = 'inspect' THEN num END ) as xcNum, sum( CASE WHEN type = 'event' THEN num END )as sjNum, sum( CASE WHEN type = 'rescue' THEN num END ) as jyNum FROM ( SELECT 'inspect' AS type, count(1) AS num FROM biz_inspection_task WHERE is_dele_ = '0' AND task_status_ IN ( 2, 3, 5 ) AND task_date_ >= #{startTime} AND task_date_ #{endTime} AND road_id_ IN #{id} UNION ALL SELECT 'event' AS type, count(1) AS num FROM biz_event_info WHERE is_dele_ = '0' AND event_status_ IN ( 2, 3, 1 ) AND date(event_time_) >= #{startTime} AND date(event_time_) #{endTime} AND road_id_ IN #{id} UNION ALL SELECT 'rescue' AS type, count(1) AS num FROM biz_rescue_info WHERE is_dele_ = '0'AND rescue_status_ IN ( 1, 2, 4 ) AND date(rescue_time_) >= #{startTime} AND date(rescue_time_) #{endTime} AND road_id_ IN #{id} ) cn SELECT e.* FROM biz_event_info e ${ew.customSqlSegment} AND date(event_time_) >= #{ew.paramNameValuePairs.startTime} AND date(event_time_) #{ew.paramNameValuePairs.endTime} AND road_id_ IN #{id} SELECT e.*, IF(r.SJ_UPLOAD_IMAGE_=0,FALSE,TRUE)AS uploadImage, IF(r.SJ_UPLOAD_IMAGE_=0,FALSE,TRUE)AS sjUploadImage FROM biz_event_info e LEFT JOIN biz_base_road r ON e.road_id_=r.id_ AND r.is_dele_='0' WHERE e.is_dele_='0' AND e.id_=#{id} SELECT * FROM biz_event_info ${ew.customSqlSegment} WHERE 1=1 AND is_dele_='0' AND (date(event_time_) >= #{ew.paramNameValuePairs.startTime} AND date(event_time_) #{ew.paramNameValuePairs.endTime}) AND event_status_=#{ew.paramNameValuePairs.status} AND road_id_ IN #{id} SELECT count(1) AS zsNum, count(case when event_status_ IN (-1,0,1) then 1 end ) AS djdNum, count(case when event_status_ = 2 then 1 end ) AS czzNum, count(case when event_status_ = 3 then 1 end ) AS czjsNum, count(case when event_status_ = 4 then 1 end ) AS ybjNum FROM biz_event_info where is_dele_='0' and date(event_time_) between #{startTime} and #{endTime} AND road_id_ IN #{id} WITH event_info_ AS( SELECT r.ID_, r.NAME_ AS roadName, COUNT(CASE WHEN EVENT_LEVEL_ != 0 THEN 1 END) AS eventLevelNum, COUNT(ei.id_) AS totalNum, COUNT(CASE WHEN LW_EVENT_TYPE_ IN ('19') THEN 1 END) AS trafficNum, COUNT(CASE WHEN LW_EVENT_TYPE_ IN ('20') THEN 1 END) AS dgNum, COUNT(CASE WHEN LW_EVENT_TYPE_ IN ('21') THEN 1 END) AS fireNum, COUNT(CASE WHEN LW_EVENT_TYPE_ IN ('22') THEN 1 END) AS otherNum, COUNT(CASE WHEN HURT_USER_NUM_ + DEAD_USER_NUM_ > 0 THEN 1 END) AS hurtNum, IFNULL(SUM(HURT_USER_NUM_),0) AS hurtPeopleNum, IFNULL(SUM(DEAD_USER_NUM_),0) AS deadPeopleNum, CONCAT(SUBSTRING_INDEX(o.path_, '.', LENGTH(o.path_) - LENGTH(REPLACE(o.path_, '.', '')) - 1), '.', r.id_, '.') AS path_ FROM uc_org o JOIN biz_base_road r ON o.name_ = r.name_ LEFT JOIN biz_event_info ei ON r.id_=ei.road_id_ AND ei.IS_DELE_ = '0' AND DATE(EVENT_TIME_) BETWEEN #{dto.startTime} AND #{dto.endTime} WHERE o.grade_ = '5' AND o.org_kind_ = 'dept' and o.is_dele_='0' and r.is_dele_='0' AND r.id_ IN #{id} GROUP BY r.ID_,r.NAME_,o.path_ ) SELECT o.NAME_ AS orgName, ei.* FROM uc_org o JOIN event_info_ ei ON ei.path_ LIKE CONCAT('%', o.path_, '%') WHERE o.grade_='3' AND o.is_dele_ = '0' AND o.org_kind_ = 'ogn' WITH event_info_ AS( SELECT r.ID_, r.NAME_ AS roadName, COUNT(CASE WHEN EVENT_LEVEL_ != 0 THEN 1 END) AS eventLevelNum, COUNT(ei.id_) AS totalNum, COUNT(CASE WHEN LW_EVENT_TYPE_ IN ('1','2','3','4','5','6') THEN 1 END) AS weatherHazardNum, COUNT(CASE WHEN LW_EVENT_TYPE_ IN ('7','8','9','10','11','12','13','14','15','16','17','18') THEN 1 END) AS geoHazardNum, COUNT(CASE WHEN LW_EVENT_TYPE_ IN ('19','20','21','22') THEN 1 END) AS eventNum, COUNT(CASE WHEN LW_EVENT_TYPE_ IN ('25') THEN 1 END) AS groupEventNum, COUNT(CASE WHEN LW_EVENT_TYPE_ IN ('27','28','29','30','31','32','33','34','35') THEN 1 END) AS repairNum, COUNT(CASE WHEN LW_EVENT_TYPE_ IN ('38') THEN 1 END) AS trafficNum, COUNT(CASE WHEN LW_EVENT_TYPE_ IN ('40') THEN 1 END) AS otRoadNum, COUNT(CASE WHEN LW_EVENT_TYPE_ IN ('45') THEN 1 END) AS otherNum, COUNT(CASE WHEN HURT_USER_NUM_ + DEAD_USER_NUM_ > 0 THEN 1 END) AS hurtNum, IFNULL(SUM(HURT_USER_NUM_),0) AS hurtPeopleNum, IFNULL(SUM(DEAD_USER_NUM_),0) AS deadPeopleNum, CONCAT(SUBSTRING_INDEX(o.path_, '.', LENGTH(o.path_) - LENGTH(REPLACE(o.path_, '.', '')) - 1), '.', r.id_, '.') AS path_ FROM uc_org o JOIN biz_base_road r ON o.name_ = r.name_ LEFT JOIN biz_event_info ei ON r.id_=ei.road_id_ AND ei.IS_DELE_ = '0' AND DATE(EVENT_TIME_) BETWEEN #{dto.startTime} AND #{dto.endTime} WHERE o.grade_ = '5' AND o.org_kind_ = 'dept' and o.is_dele_='0' and r.is_dele_='0' AND r.id_ IN #{id} GROUP BY r.ID_,r.NAME_,o.path_ ) SELECT o.NAME_ AS orgName, ei.* FROM uc_org o JOIN event_info_ ei ON ei.path_ LIKE CONCAT('%', o.path_, '%') WHERE o.grade_='3' AND o.is_dele_ = '0' AND o.org_kind_ = 'ogn' WITH control_info AS( SELECT r.ID_, r.NAME_ AS roadName, COUNT(tc.id_)AS totalNum, COUNT(CASE WHEN MEASURE_TYPE_=4 THEN 1 END)AS otRoadNum, COUNT(CASE WHEN MEASURE_TYPE_=3 THEN 1 END)AS halfClosedNum, COUNT(CASE WHEN PLACE_DETAIL_ = 3 AND MEASURE_TYPE_ IN (1,3) THEN 1 END) AS outletClosedNum, COUNT(CASE WHEN PLACE_DETAIL_ = 4 AND MEASURE_TYPE_ IN (1,3) THEN 1 END) AS inletClosedNum, COUNT(CASE WHEN MEASURE_TYPE_ IN (1,3) THEN 1 END) AS laneClosedNum, CONCAT(SUBSTRING_INDEX(o.path_, '.', LENGTH(o.path_) - LENGTH(REPLACE(o.path_, '.', '')) - 1), '.', r.id_, '.') AS path_ FROM uc_org o JOIN biz_base_road r ON o.name_ = r.name_ LEFT JOIN biz_traffic_control tc ON r.id_=tc.ROAD_ID_ AND tc.IS_DELE_='0' AND DATE(START_TIME_) BETWEEN #{dto.startTime} AND #{dto.endTime} WHERE o.grade_ = '5' AND o.org_kind_ = 'dept' and o.is_dele_='0' and r.is_dele_='0' AND r.id_ IN #{id} GROUP BY r.ID_,r.NAME_,o.path_ ) SELECT o.NAME_ AS orgName, ci.* FROM uc_org o JOIN control_info ci ON ci.path_ LIKE CONCAT('%', o.path_, '%') WHERE o.grade_='3' AND o.is_dele_ = '0' AND o.org_kind_ = 'ogn' WITH ins_count AS ( SELECT t.road_id_, COUNT(distinct t.id_) AS insTaskNum, SUM(CASE WHEN t.task_status_=4 THEN tl.mileage_ END) AS insMileage FROM biz_inspection_task t LEFT JOIN biz_inspection_result tl ON t.id_=tl.task_id_ WHERE tl.is_dele_='0' AND t.is_dele_='0' AND t.TASK_DATE_ BETWEEN #{dto.startTime} AND #{dto.endTime} GROUP BY road_id_ ), car_count AS ( SELECT road_id_,COUNT(1) AS reCarNum FROM biz_base_car WHERE USE_TYPE_=2 AND IS_DELE_='0' GROUP BY road_id_ ), point_count AS ( SELECT road_id_,COUNT(1) AS rePointNum FROM biz_base_stationary WHERE TYPE_=2 AND IS_DELE_='0' GROUP BY road_id_ ), event_count AS ( select road_id_,count(1) AS etTaskNum FROM biz_event_info WHERE is_dele_='0' AND EVENT_TIME_ BETWEEN #{dto.startTime} AND #{dto.endTime} GROUP BY road_id_ ), memter_count AS ( SELECT road_id_,count(1) AS reMemberNum FROM biz_base_team_member WHERE is_dele_='0' GROUP BY road_id_ ), rescue_count AS ( SELECT road_id_,count(1) AS reTaskNum FROM biz_rescue_info WHERE is_dele_='0' AND rescue_status_ not in (0,1) AND RESCUE_TIME_ BETWEEN #{dto.startTime} AND #{dto.endTime} GROUP BY road_id_ ), all_info AS ( SELECT r.ID_, r.NAME_ AS roadName, IFNULL(SUM(ic.insTaskNum),0) AS insTaskNum , IFNULL(SUM(ic.insMileage),0) AS insMileage, IFNULL(SUM(cc.reCarNum),0) AS reCarNum, IFNULL(SUM(pc.rePointNum),0) AS rePointNum, IFNULL(SUM(ec.etTaskNum),0) AS etTaskNum, IFNULL(SUM(mc.reMemberNum),0) AS reMemberNum, IFNULL(SUM(rc.reTaskNum),0) AS reTaskNum, CONCAT(SUBSTRING_INDEX(o.path_, '.', LENGTH(o.path_) - LENGTH(REPLACE(o.path_, '.', '')) - 1), '.', r.id_, '.') AS path_ FROM uc_org o JOIN biz_base_road r ON o.name_ = r.name_ LEFT JOIN ins_count ic ON r.id_=ic.road_id_ LEFT JOIN car_count cc ON r.id_=cc.road_id_ LEFT JOIN point_count pc ON r.id_=pc.road_id_ LEFT JOIN event_count ec ON r.id_=ec.road_id_ LEFT JOIN memter_count mc ON r.id_=mc.road_id_ LEFT JOIN rescue_count rc ON r.id_=rc.road_id_ GROUP BY r.id_,r.NAME_,o.path_ ) SELECT o.NAME_ AS orgName, ai.* FROM uc_org o JOIN all_info ai ON ai.path_ LIKE CONCAT('%', o.path_, '%') WHERE o.grade_='3' AND o.is_dele_ = '0' AND o.org_kind_ = 'ogn' AND ai.id_ IN #{id} WITH dict_info AS ( SELECT sd.code_, dd.name_, dd.value_ FROM portal_sys_dictionary sd JOIN portal_sys_dictionary_detail dd ON sd.id_ = dd.dictionary_id_ WHERE sd.code_ IN ('sjly', 'lx_sjlxxq', 'sjdj') ), member AS ( SELECT id_, name_ FROM biz_base_team_member ), handle_info AS ( SELECT event_id_, MIN(CASE WHEN EVENT_NODE_ = '2' THEN HANDLE_TIME_ END) AS inspectTime, MIN(CASE WHEN EVENT_NODE_ = '9' THEN HANDLE_TIME_ END) AS eventEndTime FROM biz_event_handle WHERE is_dele_ = '0' AND EVENT_NODE_ IN ('2', '9') GROUP BY event_id_ ), rescue_hand AS ( SELECT RESCUE_ID_, MIN(CASE WHEN RESCUE_NODE_ = '2' THEN HANDLE_TIME_ END) AS rescueTime FROM biz_rescue_handle WHERE is_dele_ = '0' AND RESCUE_NODE_ = '2' GROUP BY RESCUE_ID_ ), rescue_info AS ( SELECT ri.event_id_, rh.rescueTime, tm.name_ AS rescueUserName FROM biz_rescue_info ri JOIN rescue_hand rh ON ri.id_ = rh.RESCUE_ID_ LEFT JOIN member tm ON ri.ACCEPT_USER_ID_ = tm.id_ WHERE ri.is_dele_ = '0' ) SELECT ei.id_, IFNULL(ei.road_name_,'/') AS roadName, ei.event_no_, IFNULL(ei.event_time_,'/') AS eventTime , IFNULL(ei.RECEIVE_TIME_,'/') AS receiveTime, IFNULL(ei.ENFORCEMENT_USER_,'/') AS enforcementUser, IFNULL(ei.ENFORCEMENT_TIME_,'/') AS enforcementTime, IFNULL(ei.SUBMIT_CONTENT_,'/') AS submitContent, IFNULL(ei.SUBMIT_AREA_,'/') AS submitArea , IFNULL(ei.ELAPSE_,'/') AS elapse, IFNULL(ei.CONTROL_MEASURES_,'/') AS controlMeasures, IFNULL(ei.DISPOSITION_,'/') AS disposition, IFNULL(ei.REPORT_TIME_,'/') AS reportTime, IFNULL(ei.REPORT_USER_,'/') AS reportUser, IFNULL(ei.RECEIVE_USER_,'/') AS receiveUser, IFNULL(ei.MEMO_,'/') AS memo, IFNULL(CONCAT('本次事件中,', '受伤人数:', IFNULL(ei.HURT_USER_NUM_, 0), '人', ',死亡人数:', IFNULL(ei.DEAD_USER_NUM_, 0), '人。'),'/') AS hurtInfo, IFNULL( di.name_,'/') AS eventSrcText, IFNULL(di1.name_,'/') AS eventTypeText, IFNULL(ei.create_name_,'/') AS createName, IFNULL(ei.PEG_S_VAL_,'/') AS pegSVal, IFNULL(di2.name_,'/') AS eventLevelText, IFNULL(hi.inspectTime,'/') AS inspectTime, IFNULL(hi.eventEndTime,'/') AS eventEndTime, IFNULL(ri.rescueTime,'/') AS rescueTime, IFNULL(ri.rescueUserName,'/') AS rescueUserName, IFNULL(tm.name_,'/') AS inspectUserName FROM biz_event_info ei LEFT JOIN handle_info hi ON ei.id_ = hi.event_id_ LEFT JOIN rescue_info ri ON ei.id_ = ri.event_id_ LEFT JOIN member tm ON ei.ACCEPT_USER_ID_ = tm.id_ LEFT JOIN dict_info di ON ei.event_src_ = di.value_ AND di.code_ = 'sjly' LEFT JOIN dict_info di1 ON ei.lw_event_type_ = di1.value_ AND di1.code_ = 'lx_sjlxxq' LEFT JOIN dict_info di2 ON ei.event_level_ = di2.value_ AND di2.code_ = 'sjdj' ${ew.customSqlSegment} SELECT br.ID_, br.NAME_ AS roadName, ROUND(SUM( IFNULL(ei.LOSS_, 0 )),2)AS loss, COUNT(ei.ID_) AS totalNum, COUNT(CASE WHEN ei.EVENT_STATUS_ =4 THEN 1 END) AS finishNum, COUNT(CASE WHEN ei.EVENT_STATUS_ !=4 THEN 1 END) AS unFinishNum FROM biz_base_road br LEFT JOIN biz_event_info ei ON br.ID_=ei.ROAD_ID_ AND ei.EVENT_TIME_ BETWEEN #{dto.startTime} AND #{dto.endTime} AND ei.IS_DELE_='0' WHERE br.IS_DELE_='0' AND br.id_ IN #{id} GROUP BY br.ID_,br.NAME_ SELECT br.ID_, br.NAME_ AS roadName, ROUND(SUM( IFNULL(ei.LOSS_, 0 )),2)AS loss, COUNT(ei.ID_) AS totalNum, COUNT(CASE WHEN ei.EVENT_STATUS_ =4 THEN 1 END) AS finishNum, COUNT(CASE WHEN ei.EVENT_STATUS_ !=4 THEN 1 END) AS unFinishNum FROM biz_base_road br LEFT JOIN biz_event_info ei ON br.ID_=ei.ROAD_ID_ AND ei.EVENT_TIME_ BETWEEN #{dto.startTime} AND #{dto.endTime} AND ei.IS_DELE_='0' WHERE br.IS_DELE_='0' AND br.id_ IN #{id} GROUP BY br.ID_,br.NAME_ SELECT ei.ROAD_CODE_ AS roadCode, ei.ROAD_NAME_ AS roadName, START_TIME_ AS startTime, END_TIME_ AS endTime, TIMESTAMPDIFF(MINUTE, START_TIME_, END_TIME_) AS take, tc.PLACE_NAME_ AS placeName, CASE WHEN LW_EVENT_TYPE_ = 1 THEN 1 END AS fogNum, CASE WHEN LW_EVENT_TYPE_ = 2 THEN 1 END AS rainNum, CASE WHEN LW_EVENT_TYPE_ = 4 THEN 1 END AS snowNum, CASE WHEN LW_EVENT_TYPE_ = 5 THEN 1 END AS icingNum, CASE WHEN LW_EVENT_TYPE_ = 40 THEN 1 END AS otRoadNum, CASE WHEN LW_EVENT_TYPE_ = 38 THEN 1 END AS trafficNum, CASE WHEN LW_EVENT_TYPE_ = 39 THEN 1 END AS powerNum, CASE WHEN LW_EVENT_TYPE_ BETWEEN 7 AND 18 THEN 1 END AS addressDisasterNum, CASE WHEN LW_EVENT_TYPE_ BETWEEN 27 AND 35 THEN 1 END AS constructionNum, CASE WHEN LW_EVENT_TYPE_=36 THEN 1 END AS dutyControlNum, CASE WHEN LW_EVENT_TYPE_=37 THEN 1 END AS jhxOtherNum, CASE WHEN LW_EVENT_TYPE_ IN (3,6,19,20,21,22,23,24,25,26,41,42,43,44,45,46,47) THEN 1 END AS tfOtherNum, MEASURE_TYPE_ AS measureType, MEMO_ AS memo, DISPOSITION_ AS disposition, tc.STATUS_ AS status, HURT_USER_NUM_ AS hurtUserNum, DEAD_USER_NUM_ AS deadUserNum, CASE WHEN REPLACE(REPLACE(tc.PEG_S_VAL_, 'k', ''), '+', '.') = '0.0' OR REPLACE(REPLACE(tc.PEG_E_VAL_, 'k', ''), '+', '.') = '0.0' THEN 0 ELSE ABS(CAST(REPLACE(REPLACE(tc.PEG_E_VAL_, 'k', ''), '+', '.') AS DECIMAL(10,1)) - CAST(REPLACE(REPLACE(tc.PEG_S_VAL_, 'k', ''), '+', '.') AS DECIMAL(10,1))) END AS measureMileage, CASE WHEN REPLACE(REPLACE(tc.PEG_S_VAL_, 'k', ''), '+', '.') = '0.0' OR REPLACE(REPLACE(tc.PEG_E_VAL_, 'k', ''), '+', '.') = '0.0' THEN 0 ELSE ABS(CAST(REPLACE(REPLACE(tc.PEG_E_VAL_, 'k', ''), '+', '.') AS DECIMAL(10,1)) - CAST(REPLACE(REPLACE(tc.PEG_S_VAL_, 'k', ''), '+', '.') AS DECIMAL(10,1)))*TIMESTAMPDIFF(MINUTE, START_TIME_, END_TIME_) END AS measureQz FROM biz_event_info ei JOIN biz_traffic_control tc ON ei.id_ = tc.EVENT_ID_ ${ew.customSqlSegment} SELECT ei.ROAD_ID_, ei.ROAD_NAME_, ei.ROAD_CODE_, DATE_FORMAT(ei.event_time_, '%Y-%m') AS measureDate, COUNT(CASE WHEN ei.LW_EVENT_TYPE_ = 1 THEN 1 END) AS fogNum, COUNT(CASE WHEN ei.LW_EVENT_TYPE_ = 2 THEN 1 END) AS rainNum, COUNT(CASE WHEN ei.LW_EVENT_TYPE_ = 4 THEN 1 END) AS snowNum, COUNT(CASE WHEN ei.LW_EVENT_TYPE_ = 5 THEN 1 END) AS icingNum, COUNT(CASE WHEN ei.LW_EVENT_TYPE_ = 40 THEN 1 END) AS otRoadNum, COUNT(CASE WHEN ei.LW_EVENT_TYPE_ = 38 THEN 1 END) AS trafficNum, COUNT(CASE WHEN ei.LW_EVENT_TYPE_ = 39 THEN 1 END) AS powerNum, COUNT(CASE WHEN ei.LW_EVENT_TYPE_ BETWEEN 7 AND 18 THEN 1 END) AS addressDisasterNum, COUNT(CASE WHEN ei.LW_EVENT_TYPE_ BETWEEN 27 AND 35 THEN 1 END) AS constructionNum, COUNT(CASE WHEN ei.LW_EVENT_TYPE_ = 37 THEN 1 END) AS dutyControlNum, COUNT(CASE WHEN ei.LW_EVENT_TYPE_ = 36 THEN 1 END) AS jhxOtherNum, COUNT(CASE WHEN ei.LW_EVENT_TYPE_ IN (3,6,19,20,21,22,23,24,25,26,41,42,43,44,45,46,47) THEN 1 END) AS tfOtherNum, -- 计划性事件(27-37)的控制措施统计 SUM(CASE WHEN ei.LW_EVENT_TYPE_ BETWEEN 27 AND 37 THEN tc.zdNum ELSE 0 END) AS jhxZdNum, SUM(CASE WHEN ei.LW_EVENT_TYPE_ BETWEEN 27 AND 37 THEN tc.ddstNum ELSE 0 END) AS jhxDdstNum, SUM(CASE WHEN ei.LW_EVENT_TYPE_ BETWEEN 27 AND 37 THEN tc.bffbNum ELSE 0 END) AS jhxBffbNum, SUM(CASE WHEN ei.LW_EVENT_TYPE_ BETWEEN 27 AND 37 THEN tc.qffbNum ELSE 0 END) AS jhxQffbNum, SUM(CASE WHEN ei.LW_EVENT_TYPE_ BETWEEN 27 AND 37 THEN tc.rkfbNum ELSE 0 END) AS jhxRkfbNum, SUM(CASE WHEN ei.LW_EVENT_TYPE_ BETWEEN 27 AND 37 THEN tc.rkcdfbNum ELSE 0 END) AS jhxRkcdfbNum, SUM(CASE WHEN ei.LW_EVENT_TYPE_ BETWEEN 27 AND 37 THEN tc.rkcdxlNum ELSE 0 END) AS jhxRkcdxlNum, SUM(CASE WHEN ei.LW_EVENT_TYPE_ BETWEEN 27 AND 37 THEN tc.ckfbNum ELSE 0 END) AS jhxCkfbNum, SUM(CASE WHEN ei.LW_EVENT_TYPE_ BETWEEN 27 AND 37 THEN tc.ckcdfbNum ELSE 0 END) AS jhxCkcdfbNum, SUM(CASE WHEN ei.LW_EVENT_TYPE_ BETWEEN 27 AND 37 THEN tc.ckcdxlNum ELSE 0 END) AS jhxCkcdxlNum, -- 突发性事件(非27-37)的控制措施统计 SUM(CASE WHEN ei.LW_EVENT_TYPE_ NOT BETWEEN 27 AND 37 OR ei.LW_EVENT_TYPE_ IS NULL THEN tc.zdNum ELSE 0 END) AS tfZdNum, SUM(CASE WHEN ei.LW_EVENT_TYPE_ NOT BETWEEN 27 AND 37 OR ei.LW_EVENT_TYPE_ IS NULL THEN tc.ddstNum ELSE 0 END) AS tfDdstNum, SUM(CASE WHEN ei.LW_EVENT_TYPE_ NOT BETWEEN 27 AND 37 OR ei.LW_EVENT_TYPE_ IS NULL THEN tc.bffbNum ELSE 0 END) AS tfBffbNum, SUM(CASE WHEN ei.LW_EVENT_TYPE_ NOT BETWEEN 27 AND 37 OR ei.LW_EVENT_TYPE_ IS NULL THEN tc.qffbNum ELSE 0 END) AS tfQffbNum, SUM(CASE WHEN ei.LW_EVENT_TYPE_ NOT BETWEEN 27 AND 37 OR ei.LW_EVENT_TYPE_ IS NULL THEN tc.rkfbNum ELSE 0 END) AS tfRkfbNum, SUM(CASE WHEN ei.LW_EVENT_TYPE_ NOT BETWEEN 27 AND 37 OR ei.LW_EVENT_TYPE_ IS NULL THEN tc.rkcdfbNum ELSE 0 END) AS tfRkcdfbNum, SUM(CASE WHEN ei.LW_EVENT_TYPE_ NOT BETWEEN 27 AND 37 OR ei.LW_EVENT_TYPE_ IS NULL THEN tc.rkcdxlNum ELSE 0 END) AS tfRkcdxlNum, SUM(CASE WHEN ei.LW_EVENT_TYPE_ NOT BETWEEN 27 AND 37 OR ei.LW_EVENT_TYPE_ IS NULL THEN tc.ckfbNum ELSE 0 END) AS tfCkfbNum, SUM(CASE WHEN ei.LW_EVENT_TYPE_ NOT BETWEEN 27 AND 37 OR ei.LW_EVENT_TYPE_ IS NULL THEN tc.ckcdfbNum ELSE 0 END) AS tfCkcdfbNum, SUM(CASE WHEN ei.LW_EVENT_TYPE_ NOT BETWEEN 27 AND 37 OR ei.LW_EVENT_TYPE_ IS NULL THEN tc.ckcdxlNum ELSE 0 END) AS tfCkcdxlNum, SUM(take*measureMileage) AS measureQz, SUM(HURT_USER_NUM_) AS hurtUserNum, SUM(DEAD_USER_NUM_) AS deadUserNum, SUM(measureMileage) AS measureMileage FROM biz_event_info ei LEFT JOIN ( SELECT event_id_, COUNT(CASE WHEN MEASURE_TYPE_ = 4 THEN 1 END) AS zdNum, COUNT(CASE WHEN MEASURE_TYPE_ = 5 THEN 1 END) AS ddstNum, COUNT(CASE WHEN MEASURE_TYPE_ = 3 THEN 1 END) AS bffbNum, COUNT(CASE WHEN MEASURE_TYPE_ = 1 THEN 1 END) AS qffbNum, COUNT(CASE WHEN MEASURE_TYPE_ = 6 THEN 1 END) AS rkfbNum, COUNT(CASE WHEN MEASURE_TYPE_ = 8 AND PLACE_TYPE_ = 2 AND PLACE_DETAIL_ = 3 THEN 1 END) AS rkcdfbNum, COUNT(CASE WHEN MEASURE_TYPE_ = 2 AND PLACE_TYPE_ = 2 AND PLACE_DETAIL_ = 3 THEN 1 END) AS rkcdxlNum, COUNT(CASE WHEN MEASURE_TYPE_ = 7 THEN 1 END) AS ckfbNum, COUNT(CASE WHEN MEASURE_TYPE_ = 8 AND PLACE_TYPE_ = 2 AND PLACE_DETAIL_ = 4 THEN 1 END) AS ckcdfbNum, COUNT(CASE WHEN MEASURE_TYPE_ = 2 AND PLACE_TYPE_ = 2 AND PLACE_DETAIL_ = 4 THEN 1 END) AS ckcdxlNum, SUM(CASE WHEN REPLACE(REPLACE(PEG_S_VAL_, 'k', ''), '+', '.') = '0.0' OR REPLACE(REPLACE(PEG_E_VAL_, 'k', ''), '+', '.') = '0.0' THEN 0 ELSE ABS(CAST(REPLACE(REPLACE(PEG_E_VAL_, 'k', ''), '+', '.') AS DECIMAL(10,1)) - CAST(REPLACE(REPLACE(PEG_S_VAL_, 'k', ''), '+', '.') AS DECIMAL(10,1))) END ) AS measureMileage, SUM(TIMESTAMPDIFF(MINUTE, START_TIME_, END_TIME_)) AS take FROM biz_traffic_control WHERE is_dele_ = '0' GROUP BY event_id_ ) tc ON ei.id_ = tc.event_id_ ${ew.customSqlSegment} GROUP BY ei.ROAD_ID_,ei.ROAD_NAME_,ei.ROAD_CODE_,DATE_FORMAT(ei.event_time_, '%Y-%m') SELECT COUNT(CASE WHEN ei.LW_EVENT_TYPE_ = 1 THEN 1 END) AS fogNum, COUNT(CASE WHEN ei.LW_EVENT_TYPE_ = 2 THEN 1 END) AS rainNum, COUNT(CASE WHEN ei.LW_EVENT_TYPE_ = 4 THEN 1 END) AS snowNum, COUNT(CASE WHEN ei.LW_EVENT_TYPE_ = 5 THEN 1 END) AS icingNum, COUNT(CASE WHEN ei.LW_EVENT_TYPE_ = 40 THEN 1 END) AS otRoadNum, COUNT(CASE WHEN ei.LW_EVENT_TYPE_ = 38 THEN 1 END) AS trafficNum, COUNT(CASE WHEN ei.LW_EVENT_TYPE_ = 39 THEN 1 END) AS powerNum, COUNT(CASE WHEN ei.LW_EVENT_TYPE_ BETWEEN 7 AND 18 THEN 1 END) AS addressDisasterNum, COUNT(CASE WHEN ei.LW_EVENT_TYPE_ BETWEEN 27 AND 35 THEN 1 END) AS constructionNum, COUNT(CASE WHEN ei.LW_EVENT_TYPE_ = 37 THEN 1 END) AS dutyControlNum, COUNT(CASE WHEN ei.LW_EVENT_TYPE_ = 36 THEN 1 END) AS jhxOtherNum, COUNT(CASE WHEN ei.LW_EVENT_TYPE_ IN (3,6,19,20,21,22,23,24,25,26,41,42,43,44,45,46,47) THEN 1 END) AS tfOtherNum, -- 计划性事件(27-37)的控制措施统计 SUM(CASE WHEN ei.LW_EVENT_TYPE_ BETWEEN 27 AND 37 THEN tc.zdNum ELSE 0 END) AS jhxZdNum, SUM(CASE WHEN ei.LW_EVENT_TYPE_ BETWEEN 27 AND 37 THEN tc.ddstNum ELSE 0 END) AS jhxDdstNum, SUM(CASE WHEN ei.LW_EVENT_TYPE_ BETWEEN 27 AND 37 THEN tc.bffbNum ELSE 0 END) AS jhxBffbNum, SUM(CASE WHEN ei.LW_EVENT_TYPE_ BETWEEN 27 AND 37 THEN tc.qffbNum ELSE 0 END) AS jhxQffbNum, SUM(CASE WHEN ei.LW_EVENT_TYPE_ BETWEEN 27 AND 37 THEN tc.rkfbNum ELSE 0 END) AS jhxRkfbNum, SUM(CASE WHEN ei.LW_EVENT_TYPE_ BETWEEN 27 AND 37 THEN tc.rkcdfbNum ELSE 0 END) AS jhxRkcdfbNum, SUM(CASE WHEN ei.LW_EVENT_TYPE_ BETWEEN 27 AND 37 THEN tc.rkcdxlNum ELSE 0 END) AS jhxRkcdxlNum, SUM(CASE WHEN ei.LW_EVENT_TYPE_ BETWEEN 27 AND 37 THEN tc.ckfbNum ELSE 0 END) AS jhxCkfbNum, SUM(CASE WHEN ei.LW_EVENT_TYPE_ BETWEEN 27 AND 37 THEN tc.ckcdfbNum ELSE 0 END) AS jhxCkcdfbNum, SUM(CASE WHEN ei.LW_EVENT_TYPE_ BETWEEN 27 AND 37 THEN tc.ckcdxlNum ELSE 0 END) AS jhxCkcdxlNum, -- 突发性事件(非27-37)的控制措施统计 SUM(CASE WHEN ei.LW_EVENT_TYPE_ NOT BETWEEN 27 AND 37 OR ei.LW_EVENT_TYPE_ IS NULL THEN tc.zdNum ELSE 0 END) AS tfZdNum, SUM(CASE WHEN ei.LW_EVENT_TYPE_ NOT BETWEEN 27 AND 37 OR ei.LW_EVENT_TYPE_ IS NULL THEN tc.ddstNum ELSE 0 END) AS tfDdstNum, SUM(CASE WHEN ei.LW_EVENT_TYPE_ NOT BETWEEN 27 AND 37 OR ei.LW_EVENT_TYPE_ IS NULL THEN tc.bffbNum ELSE 0 END) AS tfBffbNum, SUM(CASE WHEN ei.LW_EVENT_TYPE_ NOT BETWEEN 27 AND 37 OR ei.LW_EVENT_TYPE_ IS NULL THEN tc.qffbNum ELSE 0 END) AS tfQffbNum, SUM(CASE WHEN ei.LW_EVENT_TYPE_ NOT BETWEEN 27 AND 37 OR ei.LW_EVENT_TYPE_ IS NULL THEN tc.rkfbNum ELSE 0 END) AS tfRkfbNum, SUM(CASE WHEN ei.LW_EVENT_TYPE_ NOT BETWEEN 27 AND 37 OR ei.LW_EVENT_TYPE_ IS NULL THEN tc.rkcdfbNum ELSE 0 END) AS tfRkcdfbNum, SUM(CASE WHEN ei.LW_EVENT_TYPE_ NOT BETWEEN 27 AND 37 OR ei.LW_EVENT_TYPE_ IS NULL THEN tc.rkcdxlNum ELSE 0 END) AS tfRkcdxlNum, SUM(CASE WHEN ei.LW_EVENT_TYPE_ NOT BETWEEN 27 AND 37 OR ei.LW_EVENT_TYPE_ IS NULL THEN tc.ckfbNum ELSE 0 END) AS tfCkfbNum, SUM(CASE WHEN ei.LW_EVENT_TYPE_ NOT BETWEEN 27 AND 37 OR ei.LW_EVENT_TYPE_ IS NULL THEN tc.ckcdfbNum ELSE 0 END) AS tfCkcdfbNum, SUM(CASE WHEN ei.LW_EVENT_TYPE_ NOT BETWEEN 27 AND 37 OR ei.LW_EVENT_TYPE_ IS NULL THEN tc.ckcdxlNum ELSE 0 END) AS tfCkcdxlNum, SUM(take*measureMileage) AS measureQz, SUM(HURT_USER_NUM_) AS hurtUserNum, SUM(DEAD_USER_NUM_) AS deadUserNum, SUM(measureMileage) AS measureMileage FROM biz_event_info ei LEFT JOIN ( SELECT event_id_, COUNT(CASE WHEN MEASURE_TYPE_ = 4 THEN 1 END) AS zdNum, COUNT(CASE WHEN MEASURE_TYPE_ = 5 THEN 1 END) AS ddstNum, COUNT(CASE WHEN MEASURE_TYPE_ = 3 THEN 1 END) AS bffbNum, COUNT(CASE WHEN MEASURE_TYPE_ = 1 THEN 1 END) AS qffbNum, COUNT(CASE WHEN MEASURE_TYPE_ = 6 THEN 1 END) AS rkfbNum, COUNT(CASE WHEN MEASURE_TYPE_ = 8 AND PLACE_TYPE_ = 2 AND PLACE_DETAIL_ = 3 THEN 1 END) AS rkcdfbNum, COUNT(CASE WHEN MEASURE_TYPE_ = 2 AND PLACE_TYPE_ = 2 AND PLACE_DETAIL_ = 3 THEN 1 END) AS rkcdxlNum, COUNT(CASE WHEN MEASURE_TYPE_ = 7 THEN 1 END) AS ckfbNum, COUNT(CASE WHEN MEASURE_TYPE_ = 8 AND PLACE_TYPE_ = 2 AND PLACE_DETAIL_ = 4 THEN 1 END) AS ckcdfbNum, COUNT(CASE WHEN MEASURE_TYPE_ = 2 AND PLACE_TYPE_ = 2 AND PLACE_DETAIL_ = 4 THEN 1 END) AS ckcdxlNum, SUM(CASE WHEN REPLACE(REPLACE(PEG_S_VAL_, 'k', ''), '+', '.') = '0.0' OR REPLACE(REPLACE(PEG_E_VAL_, 'k', ''), '+', '.') = '0.0' THEN 0 ELSE ABS(CAST(REPLACE(REPLACE(PEG_E_VAL_, 'k', ''), '+', '.') AS DECIMAL(10,1)) - CAST(REPLACE(REPLACE(PEG_S_VAL_, 'k', ''), '+', '.') AS DECIMAL(10,1))) END ) AS measureMileage, SUM(TIMESTAMPDIFF(MINUTE, START_TIME_, END_TIME_)) AS take FROM biz_traffic_control WHERE is_dele_ = '0' GROUP BY event_id_ ) tc ON ei.id_ = tc.event_id_ ${ew.customSqlSegment} SELECT parent .value_ FROM portal_sys_dictionary dict INNER JOIN portal_sys_dictionary_detail child ON dict.id_=child.dictionary_id_ INNER JOIN portal_sys_dictionary_cascade dc ON child.id_ = dc.lower_detail_id_ INNER JOIN portal_sys_dictionary_detail parent ON parent .id_ = dc.detail_id_ WHERE dict.code_= #{dictCode} AND child.value_= #{dictValue} LIMIT 1