ID_, COMPANY_ID_, COMPANY_NAME_, ROAD_SEGMENT_ID_, ROAD_SEGMENT_NAME_, ACC_DATE_MONTH_, ACC_DATE_YEAR_, TOTAL_NUMS_, TENANT_ID_ SELECT COMPANY_ID_, COMPANY_NAME_, SUM( TOTAL_NUMS_ ) AS total, SUM (TOTAL_NUMS_NO_) AS totalNumsNo FROM biz_work_statistic WHERE 1 = 1 AND ACC_DATE_MONTH_ = #{map.accDateMonth} AND ACC_DATE_YEAR_ = #{map.accDateYear} AND ROAD_SEGMENT_ID_ = #{map.roadSegmentId} GROUP BY COMPANY_ID_, COMPANY_NAME_ SELECT COMPANY_ID_, COMPANY_NAME_, IFNULL( SUM( CASE WHEN SITE_CODE_ = 1 THEN COMPLETE_ END ), 0 ) AS dayCheckingFinish, IFNULL( SUM( CASE WHEN SITE_CODE_ = 1 THEN COMPLETE_NO_ END ), 0 ) AS dayCheckingOnFinsh, IFNULL( SUM( CASE WHEN SITE_CODE_ = 2 THEN COMPLETE_ END ), 0 ) AS nightCheckingFinish, IFNULL( SUM( CASE WHEN SITE_CODE_ = 2 THEN COMPLETE_NO_ END ), 0 ) AS nightCheckingOnFinsh, SUM( COMPLETE_ ) completeTotal, SUM( COMPLETE_NO_ ) completeNoTotal FROM biz_site_statistic WHERE 1=1 AND ACC_DATE_MONTH_ = #{map.accDateMonth} AND ACC_DATE_YEAR_ = #{map.accDateYear} AND ROAD_SEGMENT_ID_ = #{map.roadSegmentId} GROUP BY COMPANY_ID_, COMPANY_NAME_ SELECT ROAD_SEGMENT_ID_, ROAD_SEGMENT_NAME_, IFNULL( SUM( CASE WHEN SITE_CODE_ = 1 THEN COMPLETE_ END ), 0 ) AS dayCheckingFinish, IFNULL( SUM( CASE WHEN SITE_CODE_ = 1 THEN COMPLETE_NO_ END ), 0 ) AS dayCheckingOnFinsh, IFNULL( SUM( CASE WHEN SITE_CODE_ = 2 THEN COMPLETE_ END ), 0 ) AS nightCheckingFinish, IFNULL( SUM( CASE WHEN SITE_CODE_ = 2 THEN COMPLETE_NO_ END ), 0 ) AS nightCheckingOnFinsh, SUM( COMPLETE_ ) completeTotal, SUM( COMPLETE_NO_ ) completeNoTotal FROM biz_site_statistic WHERE 1=1 AND COMPANY_ID_ = #{map.companyId} AND ACC_DATE_MONTH_ = #{map.accDateMonth} AND ACC_DATE_YEAR_ = #{map.accDateYear} AND ROAD_SEGMENT_ID_ = #{map.roadSegmentId} GROUP BY ROAD_SEGMENT_ID_, ROAD_SEGMENT_NAME_ SELECT SITE_CODE_ siteCode, SITE_NAME_ siteName, (SUM( COMPLETE_ )+SUM(COMPLETE_No_)) siteCodeTotal FROM `biz_site_statistic` WHERE SITE_CODE_ NOT IN ( 1, 2 ) AND ACC_DATE_MONTH_ = #{map.accDateMonth} AND ACC_DATE_YEAR_ = #{map.accDateYear} AND ROAD_SEGMENT_ID_ = #{map.roadSegmentId} AND COMPANY_ID_ = #{map.companyId} GROUP BY SITE_CODE_, SITE_NAME_ SELECT COMPANY_ID_, COMPANY_NAME_, SUM( TOTAL_MONEY_ ) TOTAL_MONEY_ FROM biz_work_money_statistic WHERE 1=1 AND ACC_DATE_MONTH_ = #{map.accDateMonth} AND ACC_DATE_YEAR_ = #{map.accDateYear} AND ROAD_SEGMENT_ID_ = #{map.roadSegmentId} GROUP BY COMPANY_ID_, COMPANY_NAME_ SELECT IFNULL(SUM(CONSERVE_MONEY_),0) CONSERVE_MONEY_, IFNULL(SUM(SPECIAL_MONEY_),0) SPECIAL_MONEY_, IFNULL(SUM(RUSH_MONEY_),0) RUSH_MONEY_, IFNULL( SUM( BUDGET_SUM_ ),0) BUDGET_SUM_, IFNULL(SUM( ALREADY_COUNT_ ),0) ALREADY_COUNT_ FROM biz_money_statistic WHERE 1 =1 AND ACC_DATE_YEAR_ = #{map.accDateYear} AND ACC_DATE_YEAR_ = #{map.accDateMonth} AND COMPANY_ID_ = #{map.companyId} AND ROAD_SEGMENT_ID_ = #{map.roadSegmentId} SELECT ROAD_SEGMENT_ID_, ROAD_SEGMENT_NAME_, SUM( TOTAL_NUMS_ ) total, SUM (TOTAL_NUMS_NO_) totalNumsNo FROM biz_work_statistic WHERE 1=1 AND COMPANY_ID_ = #{map.companyId} AND ACC_DATE_MONTH_ = #{map.accDateMonth} AND ACC_DATE_YEAR_ = #{map.accDateYear} AND ROAD_SEGMENT_ID_ = #{map.roadSegmentId} GROUP BY ROAD_SEGMENT_ID_, ROAD_SEGMENT_NAME_ SELECT ROAD_SEGMENT_ID_, ROAD_SEGMENT_NAME_, SUM( TOTAL_MONEY_ ) TOTAL_MONEY_ FROM biz_work_money_statistic WHERE 1=1 AND ACC_DATE_MONTH_ = #{map.accDateMonth} AND ACC_DATE_YEAR_ = #{map.accDateYear} AND ROAD_SEGMENT_ID_ = #{map.roadSegmentId} GROUP BY ROAD_SEGMENT_ID_, ROAD_SEGMENT_NAME_ SELECT COUNT( 1 ) countDisFinished FROM biz_disease WHERE HANDLING_SITUATION_ = 2 AND date_format(CREATE_TIME_,'%Y-%m') = #{map.accDateMonth} AND year(CREATE_TIME_) = #{map.accDateYear} AND ROAD_ID_ = #{map.roadSegmentId} SELECT COUNT( 1 ) countDisUnFinished FROM biz_disease WHERE HANDLING_SITUATION_ NOT IN(2) AND date_format(CREATE_TIME_,'%Y-%m') = #{map.accDateMonth} AND year(CREATE_TIME_) = #{map.accDateYear} AND ROAD_ID_ = #{map.roadSegmentId} SELECT * FROM biz_disease WHERE 1 = 1 AND date_format(CREATE_TIME_,'%Y-%m') = #{map.accDateMonth} AND year(CREATE_TIME_) = #{map.accDateYear} AND ROAD_ID_ = #{map.roadSegmentId} AND DISEASE_CATEGORY_ = #{map.type} SELECT IFNULL(SUM(TIMESTAMPDIFF(DAY,HANDLED_TIME_START_,HANDLED_TIME_END_)),0) FROM biz_disease WHERE HANDLING_SITUATION_ = 2 AND date_format(HANDLED_TIME_END_,'%Y-%m') = #{map.accDateMonth} AND year(HANDLED_TIME_END_) = #{map.accDateYear} AND ROAD_ID_ = #{map.roadSegmentId} SELECT count (1) FROM biz_disease WHERE HANDLING_SITUATION_ = 2 AND date_format(HANDLED_TIME_END_,'%Y-%m') = #{map.accDateMonth} AND year(HANDLED_TIME_END_) = #{map.accDateYear} AND ROAD_ID_ = #{map.roadSegmentId} SELECT d.task_key_ AS 'taskKey', d.task_name_ AS 'taskName' FROM ( SELECT a.*, b.id_ AS instId FROM biz_work_order_information a INNER JOIN bpm_pro_inst b ON a.ID_ = b.biz_key_ ) c INNER JOIN ( SELECT b.* FROM ( SELECT proc_inst_id_, MAX( create_time_ ) AS create_time_ FROM bpm_check_opinion GROUP BY proc_inst_id_ ) a INNER JOIN bpm_check_opinion b ON a.proc_inst_id_ = b.proc_inst_id_ AND a.create_time_ = b.create_time_ AND b.status_ NOT IN ( 'start', 'skip' ) ) d ON c.instId = d.proc_inst_id_ WHERE 1=1 AND date_format(c.ISSUE_DATE_,'%Y-%m') = #{map.accDateMonth} AND year(c.ISSUE_DATE_) = #{map.accDateYear} AND ROAD_SEGMENT_ID_ =#{map.roadSegmentId} SELECT d.task_key_ AS 'taskKey', d.task_name_ AS 'taskName', c.roadId, c.roadName, c.workId FROM ( SELECT a.ID_ as workId ,a.*,a.ROAD_SEGMENT_ID_ as roadId,road.NAME_ as roadName,b.id_ AS instId FROM biz_work_order_information a INNER JOIN bpm_pro_inst b ON a.ID_ = b.biz_key_ INNER JOIN biz_road_section road on a.ROAD_SEGMENT_ID_ = road.ID_ where a.is_dele_='0' ) c INNER JOIN ( SELECT b.* FROM ( SELECT proc_inst_id_, MAX( create_time_ ) AS create_time_ FROM bpm_check_opinion GROUP BY proc_inst_id_ ) a INNER JOIN bpm_check_opinion b ON a.proc_inst_id_ = b.proc_inst_id_ AND a.create_time_ = b.create_time_ AND b.status_ NOT IN ( 'start', 'skip' ) ) d ON c.instId = d.proc_inst_id_ WHERE 1 =1 AND date_format(c.ISSUE_DATE_,'%Y-%m') = #{map.accDateMonth} AND date_format(c.ISSUE_DATE_,'%Y-%m-%d') = #{map.accDateDay} AND year(c.ISSUE_DATE_) = #{map.accDateYear} AND c.ISSUE_DATE_ >= #{map.startDate} AND c.ISSUE_DATE_ <= #{map.endDate} AND ROAD_SEGMENT_ID_ IN #{item} SELECT d.task_key_ AS 'taskKey', d.task_name_ AS 'taskName', c.roadId, c.roadName FROM ( SELECT a.*,a.ROAD_SEGMENT_ID_ as roadId,road.NAME_ as roadName,b.id_ AS instId FROM biz_work_order_information a INNER JOIN bpm_pro_inst b ON a.ID_ = b.biz_key_ INNER JOIN biz_road_section road on a.ROAD_SEGMENT_ID_ = road.ID_ ) c INNER JOIN ( SELECT b.* FROM ( SELECT proc_inst_id_, MAX( create_time_ ) AS create_time_ FROM bpm_check_opinion GROUP BY proc_inst_id_ ) a INNER JOIN bpm_check_opinion b ON a.proc_inst_id_ = b.proc_inst_id_ AND a.create_time_ = b.create_time_ AND b.status_ NOT IN ( 'start', 'skip' ) ) d ON c.instId = d.proc_inst_id_ WHERE 1=1 AND date_format(c.ISSUE_DATE_,'%Y-%m') = #{map.accDateMonth} AND date_format(c.ISSUE_DATE_,'%Y-%m-%d') = #{map.accDateDay} AND year(c.ISSUE_DATE_) = #{map.accDateYear} AND ROAD_SEGMENT_ID_ IN #{item} /* SELECT * FROM biz_work_order_information WHERE IS_DELE_= "0"*/ SELECT c.* FROM ( SELECT a.*, b.id_ AS instId FROM biz_work_order_information a INNER JOIN bpm_pro_inst b ON a.ID_ = b.biz_key_ ) c INNER JOIN ( SELECT b.* FROM ( SELECT proc_inst_id_, MAX( create_time_ ) AS create_time_ FROM bpm_check_opinion GROUP BY proc_inst_id_ ) a INNER JOIN bpm_check_opinion b ON a.proc_inst_id_ = b.proc_inst_id_ AND a.create_time_ = b.create_time_ AND b.status_ NOT IN ( 'start', 'skip' ) ) d ON c.instId = d.proc_inst_id_ WHERE 1 = 1 AND date_format(c.ISSUE_DATE_,'%Y-%m') = #{map.accDateMonth} AND year(c.ISSUE_DATE_) = #{map.accDateYear} AND c.ROAD_SEGMENT_ID_ IN #{item} SELECT SUM( TOTAL_NUMS_ ) totalNums, SUM (TOTAL_NUMS_NO_) totalNumsNo FROM biz_work_statistic WHERE 1=1 AND COMPANY_ID_ = #{map.companyId} AND ACC_DATE_MONTH_ = #{map.accDateMonth} AND ACC_DATE_YEAR_ = #{map.accDateYear} AND ROAD_SEGMENT_ID_ = #{id} SELECT IFNULL( SUM( CASE WHEN SITE_CODE_ = 1 THEN COMPLETE_ END ), 0 ) AS dayCheckingFinish, IFNULL( SUM( CASE WHEN SITE_CODE_ = 1 THEN COMPLETE_NO_ END ), 0 ) AS dayCheckingOnFinsh, IFNULL( SUM( CASE WHEN SITE_CODE_ = 2 THEN COMPLETE_ END ), 0 ) AS nightCheckingFinish, IFNULL( SUM( CASE WHEN SITE_CODE_ = 2 THEN COMPLETE_NO_ END ), 0 ) AS nightCheckingOnFinsh, SUM( CASE WHEN SITE_CODE_ = 1 THEN COMPLETE_ END )+ SUM( CASE WHEN SITE_CODE_ = 2 THEN COMPLETE_ END ) completeTotal, SUM( CASE WHEN SITE_CODE_ = 1 THEN COMPLETE_NO_ END )+ SUM( CASE WHEN SITE_CODE_ = 2 THEN COMPLETE_NO_ END ) completeNoTotal FROM biz_site_statistic WHERE 1=1 AND COMPANY_ID_ = #{map.companyId} AND ACC_DATE_MONTH_ = #{map.accDateMonth} AND ACC_DATE_YEAR_ = #{map.accDateYear} AND ROAD_SEGMENT_ID_ = #{id} SELECT SITE_CODE_ siteCode, SITE_NAME_ siteName, (SUM( COMPLETE_ )+SUM(COMPLETE_No_)) siteCodeTotal FROM `biz_site_statistic` WHERE SITE_CODE_ NOT IN ( 1, 2 ) AND ACC_DATE_MONTH_ = #{map.accDateMonth} AND ACC_DATE_YEAR_ = #{map.accDateYear} AND ROAD_SEGMENT_ID_ = #{id} AND COMPANY_ID_ = #{map.companyId} GROUP BY SITE_CODE_, SITE_NAME_ SELECT IFNULL(SUM( cast( TOTAL_MONEY_ AS DECIMAL ( 18, 2 )) ),0) totalMoney FROM biz_work_money_statistic WHERE 1=1 AND ACC_DATE_MONTH_ = #{map.accDateMonth} AND ACC_DATE_YEAR_ = #{map.accDateYear} AND ROAD_SEGMENT_ID_ = #{id} SELECT * FROM biz_money_statistic WHERE 1 =1 AND ACC_DATE_YEAR_ = #{map.accDateYear} AND ACC_DATE_YEAR_ = #{map.accDateMonth} AND COMPANY_ID_ = #{map.companyId} AND ROAD_SEGMENT_ID_ = #{map.roadSegmentId} SELECT SUM( TAX_MONEY_ ) FROM biz_bill_of_quantities WHERE is_dele_ = '0' and WORK_ORDER_INFORMATION_ID_ IN ( SELECT ID_ FROM biz_work_order_information info WHERE info.IS_DELE_ ='0' AND END_TIME_ IS NOT NULL AND STATUS_ = '1' AND date_format(ISSUE_DATE_,'%Y') = #{map.accDateYear} AND date_format(ISSUE_DATE_,'%Y-%m') = #{map.accDateMonth} AND ISSUE_DATE_ >= #{map.startDate} AND ISSUE_DATE_ <= #{map.endDate} AND ROAD_SEGMENT_ID_ IN #{item} )