ID_, COMPANY_ID_, SORT_, NUMBER_, ROAD_SEGMENT_ID_, OFFICE_ID_, NAME_, TYPE_, CENT_PEG_, DIRECTION_, GRADE_, LENGTH_, WIDTH_, HIGHT_, ENTRANCE_FORM_, EXIT_FORM_, SURFACE_LAY_, ON_MATERIAL_, BODY_MATERIAL_, DESIGN_UNIT_, WATCH_UNIT_, CONSTRUCT_UNIT_, SAFE_CHANNELS_COUNT_, FINISH_DAY_, DESIGN_DRAWING_, DESIGN_FILE_, CONSTRUCTION_FILE_, COMPLETE_FILE_, CHECK_FILE_, ADMIN_FILE_, REGULAR_CHECK_REPORT_, SPECIAL_CHECK_REPORT_, FILE_NUM_, FILE_PLACE_, CREATE_DAY_, START_PEG_, END_PEG_, X_, Y_, TYPE_VALUE_, CENT_PEG_K_, EXIT_FORM_VALUE_, GRADE_VALUE_, DIRECTION_VALUE_, BODY_MATERIAL_VALUE_, ENTRANCE_FORM_VALUE_, SURFACE_LAY_VALUE_, ON_MATERIAL_VALUE_, CREATE_BY_, CREATE_ORG_ID_, CREATE_TIME_, UPDATE_BY_, UPDATE_TIME_, TENANT_ID_ a. ID_ AS "tunnelInformation.id", a.COMPANY_ID_ AS "tunnelInformation.companyId", a.SN_ AS "tunnelInformation.sn", a.NUMBER_ AS "tunnelInformation.number", a.ROAD_SEGMENT_ID_ AS "tunnelInformation.roadSegmentId", a.OFFICE_ID_ AS "tunnelInformation.officeId", a.NAME_ AS "tunnelInformation.name", a.TYPE_ AS "tunnelInformation.type", a.CENT_PEG_ AS "tunnelInformation.centPeg", a.DIRECTION_ AS "tunnelInformation.direction", a.GRADE_ AS "tunnelInformation.grade", a.LENGTH_ AS "tunnelInformation.length", a.WIDTH_ AS "tunnelInformation.width", a.HIGHT_ AS "tunnelInformation.hight", a.ENTRANCE_FORM_ AS "tunnelInformation.entranceForm", a.EXIT_FORM_ AS "tunnelInformation.exitForm", a.SURFACE_LAY_ AS "tunnelInformation.surfaceLay", a.ON_MATERIAL_ AS "tunnelInformation.onMaterial", a.BODY_MATERIAL_ AS "tunnelInformation.bodyMaterial", a.DESIGN_UNIT_ AS "tunnelInformation.designUnit", a.WATCH_UNIT_ AS "tunnelInformation.watchUnit", a.CONSTRUCT_UNIT_ AS "tunnelInformation.constructUnit", a.SAFE_CHANNELS_COUNT_ AS "tunnelInformation.safeChannelsCount", a.FINISH_DAY_ AS "tunnelInformation.finishDay", a.DESIGN_DRAWING_ AS "tunnelInformation.designDrawing", a.DESIGN_FILE_ AS "tunnelInformation.designFile", a.CONSTRUCTION_FILE_ AS "tunnelInformation.constructionFile", a.COMPLETE_FILE_ AS "tunnelInformation.completeFile", a.CHECK_FILE_ AS "tunnelInformation.checkFile", a.ADMIN_FILE_ AS "tunnelInformation.adminFile", a.REGULAR_CHECK_REPORT_ AS "tunnelInformation.regularCheckReport", a.SPECIAL_CHECK_REPORT_ AS "tunnelInformation.specialCheckReport", a.FILE_NUM_ AS "tunnelInformation.fileNum", a.FILE_PLACE_ AS "tunnelInformation.filePlace", a.CREATE_DAY_ AS "tunnelInformation.createDay", a.START_PEG_ AS "tunnelInformation.startPeg", a.END_PEG_ AS "tunnelInformation.endPeg", a.X_ AS "tunnelInformation.x", a.Y_ AS "tunnelInformation.y", a.TYPE_VALUE_ AS "tunnelInformation.typeValue", a.CENT_PEG_K_ AS "tunnelInformation.centPegK", a.EXIT_FORM_VALUE_ AS "tunnelInformation.exitFormValue", a.GRADE_VALUE_ AS "tunnelInformation.gradeValue", a.DIRECTION_VALUE_ AS "tunnelInformation.directionValue", a.BODY_MATERIAL_VALUE_ AS "tunnelInformation.bodyMaterialValue", a.ENTRANCE_FORM_VALUE_ AS "tunnelInformation.entranceFormValue", a.SURFACE_LAY_VALUE_ AS "tunnelInformation.surfaceLayValue", a.ON_MATERIAL_VALUE_ AS "tunnelInformation.onMaterialValue", b.ID_ AS "road.id", b.NUMBER_ AS "road.number", b.NAME_ AS "road.name", b.ADDRESS_ AS "road.address", b.ROUTE_ID_ AS "road.routeId", b.SIMPLICITY_ AS "road.simplicity", b.DESIGN_DEEP_ AS "road.designDeep", b.TYPE_ AS "road.type", b.LENGTH_ AS "road.length", b.WIDTH_ AS "road.width", b.ROADSURFACE_TYPE_ AS "road.roadsurfaceType", b.TECHNOLOGY_CLASS_ AS "road.technologyClass", b.DRIVEWAY_TYPE_ AS "road.drivewayType", b.DRIVEWAY_WIDTH_ AS "road.drivewayWidth", b.STARTING_POINT_ AS "road.startingPoint", b.TERMINAL_POINT_ AS "road.terminalPoint", b.START_PEG_ AS "road.startPeg", b.END_PEG_ AS "road.endPeg", b.PASS_DAY_ AS "road.passDay", b.TYPE_VALUE_ AS "road.typeValue", b.START_PEG_K_ AS "road.startPegK", b.END_PEG_K_ AS "road.endPegK", b.ROADSURFACE_TYPE_VALUE_ AS "road.roadsurfaceTypeValue", b.TECHNOLOGY_CLASS_VALUE_ AS "road.technologyClassValue", b.DRIVEWAY_TYPE_VALUE_ AS "road.drivewayTypeValue" SELECT FROM biz_tunnel_information a INNER JOIN biz_road_section b ON a.ROAD_SEGMENT_ID_ = b.ID_ ${ew.customSqlSegment} SELECT e.*, ( SELECT date_add( e.CHECK_DATE_, INTERVAL ( CASE WHEN ( e.GRADE_ = '1' ) THEN ( SELECT `value` FROM portal_sys_properties d WHERE d.alias = "tunnel_often_check_one" ) WHEN ( e.GRADE_ = '2' ) THEN ( SELECT `value` FROM portal_sys_properties d WHERE d.alias = "tunnel_often_check_two" ) ELSE ( SELECT `value` FROM portal_sys_properties d WHERE d.alias = "tunnel_often_check_three" ) END ) MONTH )) AS CHECK_NEW_DAY_ FROM ( SELECT ( CASE WHEN ( SELECT date_sub(( SELECT date_add( b.CHECK_DATE_, INTERVAL ( CASE WHEN ( a.GRADE_ = '1' ) THEN ( SELECT `value` FROM portal_sys_properties d WHERE d.alias = "tunnel_often_check_one" ) WHEN ( a.GRADE_ = '2' ) THEN ( SELECT `value` FROM portal_sys_properties d WHERE d.alias = "tunnel_often_check_two" ) ELSE ( SELECT `value` FROM portal_sys_properties d WHERE d.alias = "tunnel_often_check_three" ) END ) MONTH )), INTERVAL ( SELECT `value` FROM portal_sys_properties e WHERE e.alias = "tunnel_daily_check_wran_date" ) DAY )) > now() THEN 0 WHEN b.CHECK_DATE_ IS NULL THEN 0 ELSE 1 END ) AS 'temporary', a.*, b.* FROM biz_tunnel_information a LEFT JOIN ( SELECT Max( c.CHECK_DATE_ ) 'CHECK_DATE_', c.TUNNEL_ID_ FROM biz_tunnel_often_check c WHERE c.IS_DELE_ = '0' GROUP BY c.TUNNEL_ID_ ) b ON a.ID_ = b.TUNNEL_ID_ WHERE a.IS_DELE_ = '0' ) e ${ew.customSqlSegment} AND checkDate >=#{map.startTime,jdbcType=TIMESTAMP} AND checkDate <=#{map.endTime,jdbcType=TIMESTAMP} SELECT * FROM biz_tunnel_information ${ew.customSqlSegment} AND ROAD_SEGMENT_ID_ IN ( SELECT ID_ FROM biz_road_section WHERE IS_DELE_ = '0' AND ROUTE_ID_ =#{map.routeId} ) SELECT e.*,( SELECT date_add( e.CHECK_DATE_, INTERVAL ( SELECT `value` FROM portal_sys_properties e WHERE e.alias = "tunnel_regular_check" ) YEAR ) ) AS CHECK_NEW_DAY_ FROM ( SELECT ( CASE WHEN ( SELECT date_sub(( SELECT date_add( b.CHECK_DATE_, INTERVAL ( SELECT `value` FROM portal_sys_properties e WHERE e.alias = "tunnel_regular_check" ) YEAR )), INTERVAL ( SELECT `value` FROM portal_sys_properties e WHERE e.alias = "tunnel_regular_check_warn_date" ) DAY )) > now() THEN 0 WHEN b.CHECK_DATE_ IS NULL THEN 0 ELSE 1 END ) AS 'temporary', a.*, b.* FROM biz_tunnel_information a LEFT JOIN ( SELECT Max( c.CHECK_DATE_ ) 'CHECK_DATE_', c.TUNNEL_ID_ FROM biz_tunnel_regular_check c WHERE c.IS_DELE_ = '0' GROUP BY c.TUNNEL_ID_ ) b ON a.ID_ = b.TUNNEL_ID_ WHERE a.IS_DELE_ = '0' )e ${ew.customSqlSegment} AND checkDate>=#{map.startTime,jdbcType=TIMESTAMP} AND checkDate <=#{map.endTime,jdbcType=TIMESTAMP}