a.ID_ , a.COMPANY_ID_, a.SORT_,a.GRADE_ a.NUMBER_, a.ROAD_SEGMENT_ID_, a.NAME_, a.TYPE_, a.CATEGORY_, a.CENT_PEG_, a.DIRECTION_, a.UNDERCHANNELS_NAME_, DESIGNLOAD_, PASSESLOAD_, INCLINEDSLOPE_, SURFACEINSTALL_, OFFICE_ID_, FINISH_DAY_, MAIN_SPAN_, SIDE_SPAN_, VERTICAL_SLOPE_, SPAN_OBJECT_TYPE_, SPAN_OBJECT_NAME_, DESIGN_UNIT_, WATCH_UNIT_, CONSTRUCT_UNIT_, FRONT_LEAD_LENGTH_, AFTER_LEAD_LENGTH_, SHAPE_TYPE_, SPAN_COUNT_, CORD_COUNT_, CARD_PER_, CARD_DAY_, X_, Y_, DESIGN_DRAWING_, DESIGN_FILE_, CONSTRUCTION_FILE_, COMPLETE_FILE_, CHECK_FILE_, ADMIN_FILE_, REGULAR_CHECK_REPORT_, SPECIAL_CHECK_REPORT_, HISTORY_MAIN_FILE_, FILE_NUMBER_, FILE_PLACE_, CREATE_DAY_, LENGTH_, DECK_WIDTH_, LEAD_LINE_, SPAN_COMBO_, HOLE_NUMBER_, HOLE_NUM_SHAPE_, HOLE_NUM_SPAN_, HOLE_NUM_MATERIAL_, PIER_NO_, PIER_NO_SHAPE_, PIER_NO_MATERIAL_, PIER_NO_BASE_SHAPE_, GAP_TYPE_, SEAT_TYPE_, EARTHQUAKE_DEGREE_, PROTECT_SLOPE_, PROTECT_BODY_, STRUCTURE_, NATURE_WATER_LEVEL_, DESIGN_WATER_LEVEL_, HISTORY_WATER_LEVEL_, TYPE_VALUE_, CATEGORY_VALUE_, CENT_PEG_K_, GAP_TYPE_VALUE_, SEAT_TYPE_VALUE_, DIRECTION_VALUE_, SURFACEINSTALL_VALUE_, SHAPE_TYPE_VALUE_, CREATE_BY_, CREATE_ORG_ID_, CREATE_TIME_, UPDATE_BY_, UPDATE_TIME_, TENANT_ID_ a.ID_ AS "bridgeInformation.id", a.COMPANY_ID_ AS "bridgeInformation.companyId", a.COMPANY_NAME_ AS "bridgeInformation.companyName", a.SN_ AS "bridgeInformation.sn", a.NUMBER_ AS "bridgeInformation.number", a.ROAD_SEGMENT_ID_ AS "bridgeInformation.roadSegmentId", a.ROAD_SEGMENT_NAME_ AS "bridgeInformation.roadSegmentName", a.NAME_ AS "bridgeInformation.name", a.TYPE_ AS "bridgeInformation.type", a.CATEGORY_ AS "bridgeInformation.category", a.CENT_PEG_ AS "bridgeInformation.centPeg", a.DIRECTION_ AS "bridgeInformation.direction", a.UNDERCHANNELS_NAME_ AS "bridgeInformation.underchannelsName", a.DESIGNLOAD_ AS "bridgeInformation.designload", a.PASSESLOAD_ AS "bridgeInformation.passesload", a.INCLINEDSLOPE_ AS "bridgeInformation.inclinedslope", a.SURFACEINSTALL_ AS "bridgeInformation.surfaceinstall", a.OFFICE_ID_ AS "bridgeInformation.officeId", a.OFFICE_NAME_ AS "bridgeInformation.officeName", a.FINISH_DAY_ AS "bridgeInformation.finishDay", a.MAIN_SPAN_ AS "bridgeInformation.mainSpan", a.SIDE_SPAN_ AS "bridgeInformation.sideSpan", a.VERTICAL_SLOPE_ AS "bridgeInformation.verticalSlope", a.SPAN_OBJECT_TYPE_ AS "bridgeInformation.spanObjectType", a.SPAN_OBJECT_NAME_ AS "bridgeInformation.spanObjectName", a.DESIGN_UNIT_ AS "bridgeInformation.designUnit", a.WATCH_UNIT_ AS "bridgeInformation.watchUnit", a.CONSTRUCT_UNIT_ AS "bridgeInformation.constructUnit", a.FRONT_LEAD_LENGTH_ AS "bridgeInformation.frontLeadLength", a.AFTER_LEAD_LENGTH_ AS "bridgeInformation.afterLeadLength", a.SHAPE_TYPE_ AS "bridgeInformation.shapeType", a.SPAN_COUNT_ AS "bridgeInformation.spanCount", a.CARD_PER_ AS "bridgeInformation.cardPer", a.CARD_DAY_ AS "bridgeInformation.cardDay", a.X_ AS "bridgeInformation.x", a.Y_ AS "bridgeInformation.y", a.DESIGN_DRAWING_ AS "bridgeInformation.designDrawing", a.DESIGN_FILE_ AS "bridgeInformation.designFile", a.CONSTRUCTION_FILE_ AS "bridgeInformation.constructionFile", a.COMPLETE_FILE_ AS "bridgeInformation.completeFile", a.CHECK_FILE_ AS "bridgeInformation.checkFile", a.ADMIN_FILE_ AS "bridgeInformation.adminFile", a.REGULAR_CHECK_REPORT_ AS "bridgeInformation.regularCheckReport", a.SPECIAL_CHECK_REPORT_ AS "bridgeInformation.specialCheckReport", a.HISTORY_MAIN_FILE_ AS "bridgeInformation.historyMainFile", a.FILE_NUMBER_ AS "bridgeInformation.fileNumber", a.FILE_PLACE_ AS "bridgeInformation.filePlace", a.LENGTH_ AS "bridgeInformation.length", a.DECK_WIDTH_ AS "bridgeInformation.deckWidth", a.LEAD_LINE_ AS "bridgeInformation.leadLine", a.SPAN_COMBO_ AS "bridgeInformation.spanCombo", a.HOLE_NO_ AS "bridgeInformation.holeNo", a.HOLE_NO_SHAPE_ AS "bridgeInformation.holeNoShape", a.HOLE_NO_SPAN_ AS "bridgeInformation.holeNoSpan", a.HOLE_NO_MATERIAL_ AS "bridgeInformation.holeNoMaterial", a.PIER_NO_ AS "bridgeInformation.pierNo", a.PIER_NO_SHAPE_ AS "bridgeInformation.pierNoShape", a.PIER_NO_MATERIAL_ AS "bridgeInformation.pierNoMaterial", a.PIER_NO_BASE_SHAPE_ AS "bridgeInformation.pierNoBaseShape", a.GAP_TYPE_ AS "bridgeInformation.gapType", a.SEAT_TYPE_ AS "bridgeInformation.seatType", a.EARTHQUAKE_DEGREE_ AS "bridgeInformation.earthquakeDegree", a.PROTECT_SLOPE_ AS "bridgeInformation.protectSlope", a.PROTECT_BODY_ AS "bridgeInformation.protectBody", a.STRUCTURE_ AS "bridgeInformation.structure", a.NATURE_WATER_LEVEL_ AS "bridgeInformation.natureWaterLevel", a.DESIGN_WATER_LEVEL_ AS "bridgeInformation.designWaterLevel", a.HISTORY_WATER_LEVEL_ AS "bridgeInformation.historyWaterLevel", a.TYPE_VALUE_ AS "bridgeInformation.typeValue", a.CATEGORY_VALUE_ AS "bridgeInformation.categoryValue", a.CENT_PEG_K_ AS "bridgeInformation.centPegK", a.GAP_TYPE_VALUE_ AS "bridgeInformation.gapTypeValue", a.SEAT_TYPE_VALUE_ AS "bridgeInformation.seatTypeValue", a.DIRECTION_VALUE_ AS "bridgeInformation.directionValue", a.SURFACEINSTALL_VALUE_ AS "bridgeInformation.surfaceinstallValue", a.SHAPE_TYPE_VALUE_ AS "bridgeInformation.shapeTypeValue", 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_BRIDGE_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 = "bridge_often_check_one" ) WHEN ( e.GRADE_ = '2' ) THEN ( SELECT `value` FROM portal_sys_properties d WHERE d.alias = "bridge_often_check_two" ) ELSE ( SELECT `value` FROM portal_sys_properties d WHERE d.alias = "bridge_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 = "bridge_often_check_one" ) WHEN ( a.GRADE_ = '2' ) THEN ( SELECT `value` FROM portal_sys_properties d WHERE d.alias = "bridge_often_check_two" ) ELSE ( SELECT `value` FROM portal_sys_properties d WHERE d.alias = "bridge_often_check_three" ) END ) MONTH )), INTERVAL ( SELECT `value` FROM portal_sys_properties e WHERE e.alias = "bridge_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_bridge_information a LEFT JOIN ( SELECT Max( c.CHECK_DATE_ ) 'CHECK_DATE_', c.BRIGE_ID_ FROM biz_bridge_often_check c WHERE c.IS_DELE_ = '0' GROUP BY c.BRIGE_ID_ ) b ON a.ID_ = b.BRIGE_ID_ WHERE a.IS_DELE_ = '0' ) e ${ew.customSqlSegment} AND checkDate>=#{map.startTime,jdbcType=TIMESTAMP} AND checkDate <=#{map.endTime,jdbcType=TIMESTAMP} SELECT * FROM BIZ_BRIDGE_INFORMATION ${ew.customSqlSegment} AND ROAD_SEGMENT_ID_ IN ( SELECT ID_ FROM biz_road_section WHERE IS_DELE_ = '0' AND ROUTE_ID_ =#{map.routeId} ) UPDATE biz_bridge_information set X_=#{x},Y_=#{y} WHERE ID_=#{id} 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 = "bridge_regular_check_one" ) WHEN ( e.GRADE_ = '2' ) THEN ( SELECT `value` FROM portal_sys_properties d WHERE d.alias = "bridge_regular_check_two" ) ELSE ( SELECT `value` FROM portal_sys_properties d WHERE d.alias = "bridge_regular_check_three" ) END ) YEAR )) 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 = "bridge_regular_check_one" ) WHEN ( a.GRADE_ = '2' ) THEN ( SELECT `value` FROM portal_sys_properties d WHERE d.alias = "bridge_regular_check_two" ) ELSE ( SELECT `value` FROM portal_sys_properties d WHERE d.alias = "bridge_regular_check_three" ) END ) YEAR )), INTERVAL ( SELECT `value` FROM portal_sys_properties e WHERE e.alias = "bridge_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_bridge_information a LEFT JOIN ( SELECT Max( c.CHECK_DATE_ ) 'CHECK_DATE_', c.BRIGE_ID_ FROM biz_bridge_regular_check c WHERE c.IS_DELE_ = '0' GROUP BY c.BRIGE_ID_ ) b ON a.ID_ = b.BRIGE_ID_ WHERE a.IS_DELE_ = '0' ) e ${ew.customSqlSegment} AND checkDate>=#{map.startTime,jdbcType=TIMESTAMP} AND checkDate <=#{map.endTime,jdbcType=TIMESTAMP}