ID_, PLAN_ID_, TASK_NO_, TASK_DATE_, PLAN_TYPE_, WORKCLASS_, INSPECT_TYPE_, ROAD_ID_, ROAD_NAME_, ROAD_CODE_, START_PATROL_ID_, START_PATROL_NAME_, END_PATROL_ID_, END_PATROL_NAME_, TEAM_ID_, TEAM_FULL_ID_, TASK_STATUS_, 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_ WITH power_ AS ( SELECT bm.team_id_, tm.id_ FROM biz_base_team_member tm INNER JOIN biz_bind_member bm ON tm.id_ = bm.member_id_ WHERE tm.is_dele_ = '0' AND tm.user_id_ = #{userId} ), rank_ AS ( SELECT ru.user_id_, ri.org_id_ FROM biz_inspect_rank_info ri INNER JOIN biz_inspect_rank_user ru ON ri.id_ = ru.rank_id_ WHERE ri.is_dele_ = '0' AND ru.is_dele_ = '0' AND ( -- 情况1: 工作时间不跨天 ( ri.work_start_time_ ri.work_end_time_ AND CURRENT_TIME BETWEEN ri.work_start_time_ AND ri.work_end_time_ AND CURRENT_DATE = ru.rank_date_ ) OR -- 情况2: 工作时间跨天 ( ri.work_start_time_ > ri.work_end_time_ AND ( (CURRENT_TIME >= ri.work_start_time_ AND CURRENT_DATE = ru.rank_date_) OR (CURRENT_TIME ri.work_end_time_ AND CURRENT_DATE = DATE_SUB(ru.rank_date_, INTERVAL 1 DAY)) ) ) ) ) SELECT t.*, r.id_ AS resultId FROM biz_inspection_task t INNER JOIN power_ tm ON t.team_id_ = tm.team_id_ AND (t.accept_user_id_ IS NULL OR t.accept_user_id_ = tm.id_) LEFT JOIN rank_ rk ON t.team_id_ = rk.org_id_ LEFT JOIN biz_inspection_result r ON t.id_ = r.task_id_ AND r.is_dele_ = '0' ${ew.customSqlSegment} WHERE 1=1 AND (rk.user_id_ IS NULL OR FIND_IN_SET(tm.id_,rk.user_id_)) update biz_inspection_task set TASK_STATUS_=#{status} ,ACCEPT_USER_ID_= #{acceptUserId} where ID_=#{id} and is_dele_='0' SELECT t.*, d.MAX_CREATE_TIME_NODE_2 AS planStartTime, d.MAX_CREATE_TIME_NODE_4 AS planEndTime, r.INSPECT_USERS_NAME_ AS inspectUsersName, r.id_ AS resultId FROM biz_inspection_task t LEFT JOIN biz_inspection_result r ON r.TASK_ID_ = t.ID_ LEFT JOIN ( SELECT TASK_ID_, MAX(CASE WHEN INSPECT_NODE_ = 2 THEN CREATE_TIME_ END) AS MAX_CREATE_TIME_NODE_2, MAX(CASE WHEN INSPECT_NODE_ = 4 THEN CREATE_TIME_ END) AS MAX_CREATE_TIME_NODE_4 FROM biz_inspection_result_detail GROUP BY TASK_ID_ ) d ON t.id_=d.TASK_ID_ ${ew.customSqlSegment} WITH plan_content AS ( SELECT plan_id_, group_concat( p.CONTENT_ ) AS CONTENT_ FROM biz_inspection_plan_details d INNER JOIN biz_base_patrol p ON d.PATROL_ID_ = p.id_ group by plan_id_ ) SELECT t.*, p.CONTENT_ AS planContent, IF(r.XC_UPLOAD_IMAGE_=0,FALSE ,TRUE)AS uploadImage, IF(r.XC_TEMP_CLOCK_=0,FALSE ,TRUE)AS tempClock FROM biz_inspection_task t LEFT JOIN plan_content p ON t.PLAN_ID_ = p.plan_id_ LEFT JOIN biz_base_road r ON t.road_id_=r.id_ AND r.is_dele_='0' WHERE t.id_=#{id} SELECT * FROM biz_inspection_task ${ew.customSqlSegment} AND task_date_ >= #{ew.paramNameValuePairs.startTime} AND task_date_ #{ew.paramNameValuePairs.endTime} AND road_id_ IN #{id} SELECT t.id_, IFNULL(t.TEAM_NAME_,'/') AS teamName, IFNULL(t.TASK_DATE_,'/') AS taskDate, t.TASK_NO_ AS inspectNo, IFNULL(r.WEATHER_,'/') AS weather, IFNULL(t.ROAD_NAME_,'/') AS roadName, IFNULL(r.CAR_CODE_,'/') AS carCode, IFNULL(r.INSPECT_USERS_NAME_,'/') AS inspectUsersName, IFNULL(CASE WHEN r.CAR_APPEARANCE_ = '0' THEN '异常' ELSE '正常' END,'/') AS carAppearance, IFNULL(CASE WHEN r.CAR_ENGINE_ = '0' THEN '异常' ELSE '正常' END,'/') AS carEngine, IFNULL(CASE WHEN r.CAR_FFE_ = '0' THEN '异常' ELSE '正常' END,'/') AS carFfe, IFNULL(CASE WHEN r.CAR_LIGHT_ = '0' THEN '异常' ELSE '正常' END,'/') AS carLight, IFNULL(CASE WHEN r.CAR_OP_ = '0' THEN '异常' ELSE '正常' END,'/') AS carOp, IFNULL(ROUND(cr.START_MILEAGE_, 3),0) AS startMileage, IFNULL(ROUND(cr.END_MILEAGE_, 3),0) AS endMileage, IFNULL(ROUND(r.mileage_, 3),0) AS mileage, IFNULL(r.create_name_,'/') AS createName FROM biz_inspection_task t LEFT JOIN biz_inspection_result r ON t.id_ = r.TASK_ID_ LEFT JOIN ( SELECT TASK_ID_, START_MILEAGE_, END_MILEAGE_, ROW_NUMBER() OVER(PARTITION BY TASK_ID_ ORDER BY CREATE_TIME_ DESC) AS rn FROM biz_car_task_log WHERE IS_DELE_ = '0' ) cr ON t.id_ = cr.TASK_ID_ AND cr.rn = 1 ${ew.customSqlSegment} SELECT t.road_id_ AS roadId, t.road_name_ AS roadName, t.team_id_ AS teamId, t.team_name_ AS teamName, COUNT(DISTINCT t.id_) AS taskNum, -- 计算任务数量，去重 -- 将秒转换为小时，保留2位小数，确保总时长按小时显示 COALESCE(ROUND(SUM(r.time_spent_) / 3600.0, 2), 0) AS taskTime, -- 计算总里程，保证不为 NULL 时累加 COALESCE(SUM(r.mileage_), 0) AS taskMileage, -- 计算异常点位总数 COALESCE(SUM(d.handleAll), 0) AS handleAll, COALESCE(SUM(d.handleAll), 0) AS outlierPoint, -- 计算异常点上报总数-现场处置（个） COALESCE(SUM(d.handleScene), 0) AS handleScene, -- 计算现场处理和中心处理的差异 COALESCE(SUM(d.handleAll), 0) - COALESCE(SUM(d.handleScene), 0) AS handleCenter FROM biz_inspection_task t LEFT JOIN ( -- 聚合 biz_inspection_result 表，按 TASK_ID_ 计算总时间和总里程 SELECT TASK_ID_, SUM(time_spent_) AS time_spent_, -- 累计时间，秒 SUM(mileage_) AS mileage_ -- 累计里程 FROM biz_inspection_result WHERE is_dele_ = '0' GROUP BY TASK_ID_ ) r ON t.id_ = r.TASK_ID_ LEFT JOIN ( -- 聚合 biz_inspection_result_detail 表，避免重复计数 SELECT task_id_, COUNT(CASE WHEN inspect_status_ = '0' THEN 1 END) AS handleAll, COUNT(CASE WHEN inspect_status_ = '0' AND handle_type_ = '1' THEN 1 END) AS handleScene FROM biz_inspection_result_detail WHERE is_dele_ = '0' GROUP BY task_id_ ) d ON t.id_ = d.task_id_ ${ew.customSqlSegment} GROUP BY t.road_id_, t.road_name_, t.team_id_, t.team_name_ ORDER BY t.road_id_; SELECT COUNT(DISTINCT t.id_) AS taskNum, -- 计算任务数量，去重 -- 将秒转换为小时，保留2位小数，确保总时长按小时显示 COALESCE(ROUND(SUM(r.time_spent_) / 3600.0, 2), 0) AS taskTime, -- 计算总里程，保证不为 NULL 时累加 COALESCE(SUM(r.mileage_), 0) AS taskMileage, -- 计算异常点位总数 COALESCE(SUM(d.handleAll), 0) AS handleAll, COALESCE(SUM(d.handleAll), 0) AS outlierPoint, -- 计算异常点上报总数-现场处置（个） COALESCE(SUM(d.handleScene), 0) AS handleScene, -- 计算现场处理和中心处理的差异 COALESCE(SUM(d.handleAll), 0) - COALESCE(SUM(d.handleScene), 0) AS handleCenter FROM biz_inspection_task t LEFT JOIN ( -- 聚合 biz_inspection_result 表，按 TASK_ID_ 计算总时间和总里程 SELECT TASK_ID_, SUM(time_spent_) AS time_spent_, -- 累计时间，秒 SUM(mileage_) AS mileage_ -- 累计里程 FROM biz_inspection_result WHERE is_dele_ = '0' GROUP BY TASK_ID_ ) r ON t.id_ = r.TASK_ID_ LEFT JOIN ( -- 聚合 biz_inspection_result_detail 表，避免重复计数 SELECT task_id_, COUNT(CASE WHEN inspect_status_ = '0' THEN 1 END) AS handleAll, COUNT(CASE WHEN inspect_status_ = '0' AND handle_type_ = '1' THEN 1 END) AS handleScene FROM biz_inspection_result_detail WHERE is_dele_ = '0' GROUP BY task_id_ ) d ON t.id_ = d.task_id_ ${ew.customSqlSegment} SELECT t.road_id_ AS roadId, t.road_name_ AS roadName, t.team_id_ as teamId, t.team_name_ AS teamName, r.inspect_users_id_ AS inspectUserId, -- 单独保留用户ID COUNT(DISTINCT t.id_) AS taskNum, COALESCE(ROUND(SUM(r.time_spent_) / 3600.0, 2), 0) AS taskTime, COALESCE(SUM(r.mileage_), 0) AS taskMileage, COALESCE(SUM(d.handleAll), 0) AS handleAll, COALESCE(SUM(d.handleScene), 0) AS handleScene, COALESCE(SUM(d.handleAll), 0) - COALESCE(SUM(d.handleScene), 0) AS handleCenter FROM biz_inspection_task t LEFT JOIN ( SELECT TASK_ID_, inspect_users_id_, SUM(time_spent_) AS time_spent_, SUM(mileage_) AS mileage_ FROM biz_inspection_result WHERE is_dele_ = '0' GROUP BY TASK_ID_, inspect_users_id_ ) r ON t.id_ = r.TASK_ID_ LEFT JOIN ( SELECT task_id_, COUNT(CASE WHEN inspect_status_ = '0' THEN 1 END) AS handleAll, COUNT(CASE WHEN inspect_status_ = '0' AND handle_type_ = '1' THEN 1 END) AS handleScene FROM biz_inspection_result_detail WHERE is_dele_ = '0' GROUP BY task_id_ ) d ON t.id_ = d.task_id_ ${ew.customSqlSegment} GROUP BY t.road_id_, t.road_name_, t.team_id_, t.team_name_, r.inspect_users_id_ ORDER BY t.road_id_ SELECT d.type_ AS type, COUNT(DISTINCT t.id_) AS taskNum, -- 对应任务数量 COUNT(CASE WHEN d.inspect_status_ = '0' THEN 1 END) AS outlierPoint, -- 异常点位 COUNT(CASE WHEN d.inspect_status_ = '0' AND d.handle_type_ = '1' THEN 1 END) AS disposalAll, -- 现场处置 COUNT(CASE WHEN d.inspect_status_ = '0' THEN 1 END) - COUNT(CASE WHEN d.inspect_status_ = '0' AND d.handle_type_ = '1' THEN 1 END) AS handleAll -- 中心处置 FROM biz_inspection_task t JOIN biz_inspection_result r ON t.id_ = r.task_id_ AND r.is_dele_ = '0' JOIN biz_inspection_result_detail d ON r.id_ = d.result_id_ AND d.is_dele_ = '0' ${ew.customSqlSegment} GROUP BY d.type_ ORDER BY outlierPoint DESC; SELECT COUNT(DISTINCT t.id_) AS taskNum, -- 对应任务数量 COUNT(CASE WHEN d.inspect_status_ = '0' THEN 1 END) AS outlierPoint, -- 异常点位 COUNT(CASE WHEN d.inspect_status_ = '0' AND d.handle_type_ = '1' THEN 1 END) AS disposalAll, -- 现场处置 COUNT(CASE WHEN d.inspect_status_ = '0' THEN 1 END) - COUNT(CASE WHEN d.inspect_status_ = '0' AND d.handle_type_ = '1' THEN 1 END) AS handleAll -- 中心处置 FROM biz_inspection_task t JOIN biz_inspection_result r ON t.id_ = r.task_id_ AND r.is_dele_ = '0' JOIN biz_inspection_result_detail d ON r.id_ = d.result_id_ AND d.is_dele_ = '0' ${ew.customSqlSegment} ORDER BY outlierPoint DESC; SELECT t.road_id_ AS roadId, t.road_name_ AS roadName, t.team_id_ as teamId, t.team_name_ AS teamName, d.TYPE_ as type , d.TYPE_CONTENT_ AS typeContent, r.inspect_users_id_ AS inspectUserId, COUNT( DISTINCT d.ID_) AS point FROM biz_inspection_task t LEFT JOIN biz_inspection_result r ON t.id_ = r.task_id_ AND r.is_dele_ = '0' LEFT JOIN biz_inspection_result_detail d ON r.id_ = d.result_id_ AND d.is_dele_ = '0' ${ew.customSqlSegment} GROUP BY t.road_id_, t.road_name_, t.team_id_, t.team_name_,d.TYPE_ ,d.TYPE_CONTENT_,r.inspect_users_id_; WITH task_count AS ( SELECT COUNT(DISTINCT t.id_) AS taskNum, COUNT(DISTINCT CASE WHEN t.TASK_STATUS_=4 THEN t.id_ END) AS taskFinishNum, COUNT(td.id_) AS pointNum, COUNT(rd.id_) AS pointFinishNum, t.ROAD_ID_ FROM biz_inspection_task t LEFT JOIN biz_inspection_task_details td ON t.ID_=td.TASK_ID_ LEFT JOIN biz_inspection_result_detail rd ON rd.TASK_ID_=t.ID_ AND rd.PATROL_ID_=td.PATROL_ID_ AND rd.IS_DELE_='0' WHERE t.IS_DELE_='0' AND t.task_date_ BETWEEN DATE(#{dto.startTime}) AND DATE(#{dto.endTime}) GROUP BY t.ROAD_ID_ ) SELECT r.ID_ AS roadId, r.NAME_ AS roadName, IFNULL(tc.taskNum,0) AS taskNum, IFNULL(tc.taskFinishNum,0) AS taskFinishNum, IFNULL(CASE taskFinishNum WHEN 0 THEN 0 ELSE ROUND(taskFinishNum*100.00/taskNum,2) END,0) AS taskFinishRate, IFNULL(tc.pointNum,0) AS pointNum, IFNULL(tc.pointFinishNum,0) AS pointFinishNum, IFNULL(CASE pointFinishNum WHEN 0 THEN 0 ELSE ROUND(pointFinishNum*100.00/pointNum,2) END,0) AS pointFinishRate FROM biz_base_road r LEFT JOIN task_count tc ON r.ID_=tc.ROAD_ID_ AND r.ID_ IN #{orgId} WITH task_stats AS ( SELECT t.id_ AS task_id, t.TASK_STATUS_, td.id_ AS point_id, rd.id_ AS result_id, t.ROAD_ID_ -- 添加此字段以便主查询过滤 FROM biz_inspection_task t LEFT JOIN biz_inspection_task_details td ON t.ID_ = td.TASK_ID_ LEFT JOIN biz_inspection_result_detail rd ON rd.TASK_ID_ = t.ID_ AND rd.PATROL_ID_ = td.PATROL_ID_ AND rd.IS_DELE_ = '0' WHERE t.IS_DELE_ = '0' AND t.task_date_ BETWEEN DATE(#{dto.startTime}) AND DATE(#{dto.endTime}) AND t.ROAD_ID_ IN #{orgId} ) SELECT COUNT(DISTINCT task_id) AS taskNum, COUNT(DISTINCT CASE WHEN TASK_STATUS_ = 4 THEN task_id END) AS taskFinishNum, ROUND(COUNT(DISTINCT CASE WHEN TASK_STATUS_ = 4 THEN task_id END) * 100.0 / NULLIF(COUNT(DISTINCT task_id), 0), 2 ) AS taskFinishRate, COUNT(point_id) AS pointNum, COUNT(result_id) AS pointFinishNum, ROUND(COUNT(result_id) * 100.0 / NULLIF(COUNT(point_id), 0),2) AS pointFinishRate FROM task_stats