ID_, REMARKS_, TYPE_, MANAGE_UNIT_ID_,APPROVE_STATE_, CONSTRUCT_UNIT_ID_, ROAD_SEGMENT_ID_, JOB_NUMBER_, POSITION_, CATEGORY_ID_, DETAILS_ID_, CONTRACT_ID_, DEMAND_FINISH_TIME_, ISSUER_, ISSUE_DATE_, TASK_CONTENT_, IS_COMPLETE_, CONSTRUCTION_FINISH_TIME_, CONSTRUCTION_START_PEG_, RESPONSIBLE_PERSON_, CONSTRUCTION_DESC_, RECEIVER_OPINION_, RECEIVEROR_, RECEIVER_DATE_, AUDIT_OPINION_, AUDITOR_, AUDIT_DATE_, TOTAL_AMOUNT_, TOTAL_MONEY_, STATUS_, COMPANY_ID_, OVERDUE_, OVERDUE_DAYS_, IS_SETTLEMENT_, IS_CONTRACT_SETTLEMENT_, TOTAL_CONTRACT_MONEY_, PROC_INS_ID_, PROC_DEF_ID_, IS_PROCESS_, STATUS_VALUE_, TYPE_VALUE_, CONSTRUCTION_START_PEG_K_, CONSTRUCTION_END_PEG_K_, WORK_ORDER_PROJECT_AMOUNT_, FILE_IDS_, DISEASE_PIC_IDS_, CONSTRUCTION_PIC_IDS_, CREATE_BY_, CREATE_ORG_NAME_, CREATE_COMPANY_ID_, CREATE_COMPANY_NAME_, CREATE_NAME_, CREATE_ORG_ID_, 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_, COMPANY_NAME_ c.ID_ AS 'cid', c.APPROVE_STATE_ AS 'capproveState', c.REMARKS_ AS 'cremarks', c.TYPE_ AS 'ctype', c.MANAGE_UNIT_ID_ AS 'cmanageUnitId', c.CONSTRUCT_UNIT_ID_ AS 'cconstructUnitId', c.ROAD_SEGMENT_ID_ AS 'croadSegmentId', c.JOB_NUMBER_ AS 'cjobNumber', c.POSITION_ AS 'cposition', c.CATEGORY_ID_ AS 'ccategoryId', c.DETAILS_ID_ AS 'cdetailsId', c.CONTRACT_ID_ AS 'ccontractId', c.DEMAND_FINISH_TIME_ AS 'cdemandFinishTime', c.ISSUER_ AS 'cissuer', ISSUE_DATE_ AS 'cissueDate', c.TASK_CONTENT_ AS 'ctaskContent', c.IS_COMPLETE_ AS 'cisComplete', c.CONSTRUCTION_FINISH_TIME_ AS 'cconstructionFinishTime', c.CONSTRUCTION_START_PEG_ AS 'cconstructionStartPeg', c.RESPONSIBLE_PERSON_ AS 'cresponsiblePerson', c.CONSTRUCTION_DESC_ AS 'cconstructionDesc', c.RECEIVER_OPINION_ AS 'creceiverOpinion', c.RECEIVEROR_ AS 'creceiveror', c.RECEIVER_DATE_ AS 'creceiverDate', c.AUDIT_OPINION_ AS 'cauditOpinion', c.AUDITOR_ AS 'cauditor', c.AUDIT_DATE_ AS 'cauditDate', c.TOTAL_AMOUNT_ AS 'ctotalAmount', c.TOTAL_MONEY_ AS 'ctotalMoney', c.STATUS_ AS 'cstatus', c.companyId AS 'companyId', c.OVERDUE_ AS 'coverdue', c.OVERDUE_DAYS_ AS 'coverdueDays', c.IS_SETTLEMENT_ AS 'cisSettlement', c.IS_CONTRACT_SETTLEMENT_ AS 'cisContractSettlement', c.TOTAL_CONTRACT_MONEY_ AS 'ctotalContractMoney', c.PROC_INS_ID_ AS 'cprocInsId', c.PROC_DEF_ID_ AS 'cprocDefId', c.IS_PROCESS_ AS 'cisProcess', c.STATUS_VALUE_ AS 'cstatusValue', c.TYPE_VALUE_ AS 'ctypeValue', c.CONSTRUCTION_START_PEG_K_ AS 'cconstructionStartPegK', c.CONSTRUCTION_END_PEG_K_ AS 'cconstructionEndPegK', c.WORK_ORDER_PROJECT_AMOUNT_ AS 'cworkOrderProjectAmount', c.FILE_IDS_ AS 'cfileIds', c.DISEASE_ID_ AS 'cdiseaseId', c.DISEASE_PIC_IDS_ AS 'cdiseasePicIds', c.CONSTRUCTION_PIC_IDS_ AS 'cconstructionPicIds', c.COMPANY_NAME_ AS 'ccompanyName', c.CONSTRUCT_UNIT_IDS_ AS 'cconstructUnitIds', c.ASSIGNED_PERSONNEL_ID_ AS 'cassignedPersonnelId', c.ASSIGNED_PERSONNEL_ACCOUNT_ AS 'cassignedPersonnelAccount', c.ASSIGNED_PERSONNEL_ AS 'cassignedPersonnel', c.MAINTENANCE_UNIT_AUDIT_ AS 'cmaintenanceUnitAudit', c.MAINTENANCE_UNIT_AUDIT_ID_ AS 'cmaintenanceUnitAuditId', c.MAINTENANCE_UNIT_AUDIT_IDS_ AS 'cmaintenanceUnitAuditIds', c.RECEIVING_PARTY_REVIEW_ID_ AS 'creceivingPartyReviewId', c.RECEIVING_PARTY_REVIEW_ACCOUNT_ AS 'creceivingPartyReviewAccount', c.RECEIVING_PARTY_REVIEW_ AS 'creceivingPartyReview', c.MAINTENANCE_STATION_ID_ AS 'cmaintenanceStationAuditId', c.MAINTENANCE_STATION_AUDIT_ AS 'cmaintenanceStationAudit', c.MAINTENANCE_STATION_ACCOUNT_ AS 'cmaintenanceStationAuditAccount', c.MAINTENANCE_DEPT_ID_ AS 'cmaintenanceDeptId', c.MAINTENANCE_DEPT_ACCOUNT_ AS 'cmaintenanceDeptAccount', c.MAINTENANCE_DEPT_ AS 'cmaintenanceDept', d.id_ AS 'did', d.proc_def_id_ AS 'dprocDefId', d.sup_inst_id_ AS 'dsupInstId', d.proc_inst_id_ AS 'dprocInstId', d.task_id_ AS 'dtaskId', d.task_key_ AS 'dtaskKey', d.task_name_ AS 'dtaskName', d.token_ AS 'dtoken', d.qualfieds_ AS 'dqualfieds', d.qualfied_names_ AS 'dqualfiedNames', d.auditor_ AS 'dauditor', d.auditor_name_ AS 'dauditorName', d.opinion_ AS 'dopinion', d.status_ AS 'dstatus', d.is_read_ AS 'disRead', d.form_def_id_ AS 'dformDefId', d.agent_leader_id_ AS 'dagentLeaderId', d.sign_type_ AS 'dsignType', d.skip_type_ AS 'dskipType', d.form_name_ AS 'dformName', d.create_time_ AS 'dcreateTime', d.assign_time_ AS 'dassignTime', d.complete_time_ AS 'dcompleteTime', d.dur_ms_ AS 'ddurMs', d.z_files_ AS 'dzfiles', d.interpose_ AS 'dinterpose', d.org_id_ AS 'dorgId', d.org_path_ AS 'dorgPath' t3.cid AS 'workOrderInformation.id', t3.capproveState AS 'workOrderInformation.approveState', t3.cremarks AS 'workOrderInformation.remarks', t3.ctype AS 'workOrderInformation.type', t3.cmanageUnitId AS 'workOrderInformation.manageUnitId', t3.cconstructUnitId AS 'workOrderInformation.constructUnitId', t3.croadSegmentId AS 'workOrderInformation.roadSegmentId', t3.cjobNumber AS 'workOrderInformation.jobNumber', t3.cposition AS 'workOrderInformation.position', t3.ccategoryId AS 'workOrderInformation.categoryId', t3.cdetailsId AS 'workOrderInformation.detailsId', t3.ccontractId AS 'workOrderInformation.contractId', t3.cdemandFinishTime AS 'workOrderInformation.demandFinishTime', t3.cissuer AS 'workOrderInformation.issuer', t3.cissueDate AS 'workOrderInformation.issueDate', t3.ctaskContent AS 'workOrderInformation.taskContent', t3.cisComplete AS 'workOrderInformation.isComplete', t3.cconstructionFinishTime AS 'workOrderInformation.constructionFinishTime', t3.cconstructionStartPeg AS 'workOrderInformation.constructionStartPeg', t3.cresponsiblePerson AS 'workOrderInformation.responsiblePerson', t3.cconstructionDesc AS 'workOrderInformation.constructionDesc', t3.creceiverOpinion AS 'workOrderInformation.receiverOpinion', t3.creceiveror AS 'workOrderInformation.receiveror', t3.creceiverDate AS 'workOrderInformation.receiverDate', t3.cauditOpinion AS 'workOrderInformation.auditOpinion', t3.cauditor AS 'workOrderInformation.auditor', t3.cauditDate AS 'workOrderInformation.auditDate', t3.ctotalAmount AS 'workOrderInformation.totalAmount', t3.ctotalMoney AS 'workOrderInformation.totalMoney', t3.cstatus AS 'workOrderInformation.status', t3.companyId AS 'workOrderInformation.companyId', t3.coverdue AS 'workOrderInformation.overdue', t3.coverdueDays AS 'workOrderInformation.overdueDays', t3.cisSettlement AS 'workOrderInformation.isSettlement', t3.cisContractSettlement AS 'workOrderInformation.isContractSettlement', t3.ctotalContractMoney AS 'workOrderInformation.totalContractMoney', t3.cprocInsId AS 'workOrderInformation.procInsId', t3.cprocDefId AS 'workOrderInformation.procDefId', t3.cisProcess AS 'workOrderInformation.isProcess', t3.cstatusValue AS 'workOrderInformation.statusValue', t3.ctypeValue AS 'workOrderInformation.typeValue', t3.cconstructionStartPegK AS 'workOrderInformation.constructionStartPegK', t3.cconstructionEndPegK AS 'workOrderInformation.constructionEndPegK', t3.cworkOrderProjectAmount AS 'workOrderInformation.workOrderProjectAmount', t3.cfileIds AS 'workOrderInformation.fileIds', t3.cdiseaseId AS 'workOrderInformation.diseaseId', t3.cdiseasePicIds AS 'workOrderInformation.diseasePicIds', t3.cconstructionPicIds AS 'workOrderInformation.constructionPicIds', t3.ccompanyName AS 'workOrderInformation.companyName', t3.cconstructUnitIds AS 'workOrderInformation.constructUnitIds', t3.cassignedPersonnelId AS 'workOrderInformation.assignedPersonnelId', t3.cassignedPersonnelAccount AS 'workOrderInformation.assignedPersonnelAccount', t3.cassignedPersonnel AS 'workOrderInformation.assignedPersonnel', t3.cmaintenanceUnitAudit AS 'workOrderInformation.maintenanceUnitAudit', t3.cmaintenanceUnitAuditId AS 'workOrderInformation.maintenanceUnitAuditId', t3.cmaintenanceUnitAuditIds AS 'workOrderInformation.maintenanceUnitAuditIds', t3.creceivingPartyReviewId AS 'workOrderInformation.receivingPartyReviewId', t3.creceivingPartyReviewAccount AS 'workOrderInformation.receivingPartyReviewAccount', t3.creceivingPartyReview AS 'workOrderInformation.receivingPartyReview', t3.cmaintenanceStationAuditId AS 'workOrderInformation.maintenanceStationAuditId', t3.cmaintenanceStationAudit AS 'workOrderInformation.maintenanceStationAudit', t3.cmaintenanceStationAuditAccount AS 'workOrderInformation.maintenanceStationAuditAccount', t3.cmaintenanceDeptId AS 'workOrderInformation.maintenanceDeptId', t3.cmaintenanceDeptAccount AS 'workOrderInformation.maintenanceDeptAccount', t3.cmaintenanceDept AS 'workOrderInformation.maintenanceDept', t3.did AS 'defaultBpmCheckOpinion.id', t3.dprocDefId AS 'defaultBpmCheckOpinion.procDefId', t3.dsupInstId AS 'defaultBpmCheckOpinion.supInstId', t3.dprocInstId AS 'defaultBpmCheckOpinion.procInstId', t3.dtaskId AS 'defaultBpmCheckOpinion.taskId', t3.dtaskKey AS 'defaultBpmCheckOpinion.taskKey', t3.dtaskName AS 'defaultBpmCheckOpinion.taskName', t3.dtoken AS 'defaultBpmCheckOpinion.token', t3.dqualfieds AS 'defaultBpmCheckOpinion.qualfieds', t3.dqualfiedNames AS 'defaultBpmCheckOpinion.qualfiedNames', t3.dauditor AS 'defaultBpmCheckOpinion.auditor', t3.dauditorName AS 'defaultBpmCheckOpinion.auditorName', t3.dopinion AS 'defaultBpmCheckOpinion.opinion', t3.dstatus AS 'defaultBpmCheckOpinion.status', t3.disRead AS 'defaultBpmCheckOpinion.isRead', t3.dformDefId AS 'defaultBpmCheckOpinion.formDefId', t3.dagentLeaderId AS 'defaultBpmCheckOpinion.agentLeaderId', t3.dsignType AS 'defaultBpmCheckOpinion.signType', t3.dskipType AS 'defaultBpmCheckOpinion.skipType', t3.dformName AS 'defaultBpmCheckOpinion.formName', t3.dcreateTime AS 'defaultBpmCheckOpinion.createTime', t3.dassignTime AS 'defaultBpmCheckOpinion.assignTime', t3.dcompleteTime AS 'defaultBpmCheckOpinion.completeTime', t3.ddurMs AS 'defaultBpmCheckOpinion.durMs', t3.dzfiles AS 'defaultBpmCheckOpinion.zfiles', t3.dinterpose AS 'defaultBpmCheckOpinion.interpose', t3.dorgId AS 'defaultBpmCheckOpinion.orgId', t3.dorgPath AS 'defaultBpmCheckOpinion.orgPath' , t3.codes AS 'codes', t3.subjectNames AS 'subjectNames' SELECT * FROM work_order_information ${ew.customSqlSegment} SELECT FROM ( SELECT * FROM ( SELECT FROM ( SELECT a.*, b.id_ AS instId , road.COMPANY_ID AS companyId 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_ ) t1 LEFT JOIN ( SELECT WORK_ORDER_INFORMATION_ID_, GROUP_CONCAT( NUMBER_ ) codes, GROUP_CONCAT( SUBJECT_NAME_ ) subjectNames FROM BIZ_BILL_OF_QUANTITIES GROUP BY WORK_ORDER_INFORMATION_ID_ ) t2 ON t1.cid = t2.WORK_ORDER_INFORMATION_ID_ ) t3 ${ew.customSqlSegment} SELECT FROM ( SELECT * FROM ( SELECT FROM ( SELECT a.*, b.id_ AS instId , road.COMPANY_ID AS companyId FROM biz_work_order_information a LEFT JOIN bpm_pro_inst b ON a.ID_ = b.biz_key_ LEFT 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_ ) t1 LEFT JOIN ( SELECT WORK_ORDER_INFORMATION_ID_, GROUP_CONCAT( NUMBER_ ) codes, GROUP_CONCAT(FINE_SUBJECT_CODE_) fineCodes, GROUP_CONCAT(SON_SUBJECT_CODE_) sonCodes, GROUP_CONCAT( SUBJECT_NAME_ ) subjectNames FROM BIZ_BILL_OF_QUANTITIES GROUP BY WORK_ORDER_INFORMATION_ID_ ) t2 ON t1.cid = t2.WORK_ORDER_INFORMATION_ID_ ) t3 ${ew.customSqlSegment} a.ID_ AS 'workOrderInformation.id', a.REMARKS_ AS 'workOrderInformation.remarks', a.TYPE_ AS 'workOrderInformation.type', a.MANAGE_UNIT_ID_ AS 'workOrderInformation.manageUnitId', a.CONSTRUCT_UNIT_ID_ AS 'workOrderInformation.constructUnitId', a.ROAD_SEGMENT_ID_ AS 'workOrderInformation.roadSegmentId', a.JOB_NUMBER_ AS 'workOrderInformation.jobNumber', a.POSITION_ AS 'workOrderInformation.position', a.CATEGORY_ID_ AS 'workOrderInformation.categoryId', a.DETAILS_ID_ AS 'workOrderInformation.detailsId', a.CONTRACT_ID_ AS 'workOrderInformation.contractId', a.DEMAND_FINISH_TIME_ AS 'workOrderInformation.demandFinishTime', a.ISSUER_ AS 'workOrderInformation.issuer', a.ISSUE_DATE_ AS 'workOrderInformation.issueDate', a.TASK_CONTENT_ AS 'workOrderInformation.taskContent', a.IS_COMPLETE_ AS 'workOrderInformation.isComplete', a.CONSTRUCTION_FINISH_TIME_ AS 'workOrderInformation.constructionFinishTime', a.CONSTRUCTION_START_PEG_ AS 'workOrderInformation.constructionStartPeg', a.RESPONSIBLE_PERSON_ AS 'workOrderInformation.responsiblePerson', a.CONSTRUCTION_DESC_ AS 'workOrderInformation.constructionDesc', a.RECEIVER_OPINION_ AS 'workOrderInformation.receiverOpinion', a.RECEIVEROR_ AS 'workOrderInformation.receiveror', a.RECEIVER_DATE_ AS 'workOrderInformation.receiverDate', a.AUDIT_OPINION_ AS 'workOrderInformation.auditOpinion', a.AUDITOR_ AS 'workOrderInformation.auditor', a.AUDIT_DATE_ AS 'workOrderInformation.auditDate', a.TOTAL_AMOUNT_ AS 'workOrderInformation.totalAmount', a.TOTAL_MONEY_ AS 'workOrderInformation.totalMoney', a.STATUS_ AS 'workOrderInformation.status', a.COMPANY_ID_ AS 'workOrderInformation.companyId', a.OVERDUE_ AS 'workOrderInformation.overdue', a.OVERDUE_DAYS_ AS 'workOrderInformation.overdueDays', a.IS_SETTLEMENT_ AS 'workOrderInformation.isSettlement', a.IS_CONTRACT_SETTLEMENT_ AS 'workOrderInformation.isContractSettlement', a.TOTAL_CONTRACT_MONEY_ AS 'workOrderInformation.totalContractMoney', a.PROC_INS_ID_ AS 'workOrderInformation.procInsId', a.PROC_DEF_ID_ AS 'workOrderInformation.procDefId', a.IS_PROCESS_ AS 'workOrderInformation.isProcess', a.STATUS_VALUE_ AS 'workOrderInformation.statusValue', a.TYPE_VALUE_ AS 'workOrderInformation.typeValue', a.CONSTRUCTION_START_PEG_K_ AS 'workOrderInformation.constructionStartPegK', a.CONSTRUCTION_END_PEG_K_ AS 'workOrderInformation.constructionEndPegK', a.WORK_ORDER_PROJECT_AMOUNT_ AS 'workOrderInformation.workOrderProjectAmount', a.FILE_IDS_ AS 'workOrderInformation.fileIds', a.DISEASE_ID_ AS 'workOrderInformation.diseaseId', a.DISEASE_PIC_IDS_ AS 'workOrderInformation.diseasePicIds', a.CONSTRUCTION_PIC_IDS_ AS 'workOrderInformation.constructionPicIds', a.COMPANY_NAME_ AS 'workOrderInformation.companyName', a.END_TIME_ AS 'workOrderInformation.endTime' SELECT FROM biz_work_order_information a ${ew.customSqlSegment} AND END_TIME_ >#{map.startDate} AND END_TIME_ < #{map.endDate} AND ROAD_SEGMENT_ID_ = #{map.roadId} SELECT a.ROAD_SEGMENT_ID_ AS roadId, ( SELECT NAME_ FROM biz_road_section WHERE id_ = roadId ) roadName, c.NUMBER_, c.SUBJECT_NAME_ AS sbjName, COUNT( IF ( a.STATUS_ = "1", 1, NULL )) AS finishCount, COUNT( IF ( a.OVERDUE_ = "0" AND a.STATUS_ = "0", 1, NULL )) AS constructionCount, COUNT( IF ( a.OVERDUE_ = "1" AND a.STATUS_ = "0", TAX_MONEY_, 0 )) AS overDueCount, sum( IF ( a.STATUS_ = "1", TAX_MONEY_, 0 )) AS finishMoney, sum( IF ( a.OVERDUE_ = "1" AND a.STATUS_ = "0", TAX_MONEY_, 0 )) AS overDueMoney, SUM( TAX_MONEY_ ) AS totalMoney, ( SELECT contract.NAME_ FROM biz_contract contract WHERE id_ = b.CONTRACT_ID_ ) contractName FROM biz_bIll_of_quantities AS c INNER JOIN biz_contract_item AS b ON c.CONTRACT_ITEM_ID_ = b.ID_ INNER JOIN biz_work_order_information a ON a.ID_ = c.WORK_ORDER_INFORMATION_ID_ LEFT JOIN biz_road_section road ON a.ROAD_SEGMENT_ID_ = road.ID_ AND road.tenant_id_ = '-1' ${ew.customSqlSegment} and b.CONTRACT_ID_ IS NOT NULL AND b.CONTRACT_ID_ != "" and a.is_dele_ = "0" GROUP BY c.NUMBER_, sbjName, b.CONTRACT_ID_, roadId ORDER BY b.CONTRACT_ID_, c.NUMBER_ ASC