ID_, COMPANY_ID_, COMPANY_NAME_, SN_, NUMBER_, ROAD_SEGMENT_ID_, ROAD_SEGMENT_NAME_, OFFICE_ID_, OFFICE_NAME_, NAME_, TYPE_, CENT_PEG_, DIRECTION_, RAMP_PEG_, HEIGHT_, LENGTH_, SPAN_, HOLE_COUNT_, BODY_MATERIAL_, TOP_MATERIAL_, ENTRANCE_MATERIAL_, ENTRANCE_FORM_, EXIT_MATERIAL_, EXIT_FORM_, FINISH_DAY_, USES_, X_, Y_, TYPE_VALUE_, CENT_PEG_K_, RAMP_PEG_K_, EXIT_FORM_VALUE_, USES_VALUE_, DIRECTION_VALUE_, CENT_PEG_AND_RAMP_PEG_K_, BODY_MATERIAL_VALUE_, TOP_MATERIAL_VALUE_, ENTRANCE_MATERIAL_VALUE_, ENTRANCE_FORM_VALUE_, EXIT_MATERIAL_VALUE_, 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_ SELECT * FROM biz_culvert_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 = "culvert_often_check" ) MONTH ) )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 = "culvert_often_check" ) MONTH )), INTERVAL ( SELECT `value` FROM portal_sys_properties e WHERE e.alias = "culvert_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_culvert_information a LEFT JOIN ( SELECT Max( c.CHECK_DATE_ ) 'CHECK_DATE_', c.CULVERT_ID_ FROM biz_culvert_often_check c WHERE c.IS_DELE_ = '0' GROUP BY c.CULVERT_ID_ ) b ON a.ID_ = b.CULVERT_ID_ WHERE a.IS_DELE_ = '0' ) e ${ew.customSqlSegment} AND checkDate>=#{map.startTime,jdbcType=TIMESTAMP} AND checkDate <=#{map.endTime,jdbcType=TIMESTAMP} SELECT e.*, ( SELECT date_add( e.CHECK_DATE_, INTERVAL ( SELECT `value` FROM portal_sys_properties e WHERE e.alias = "culvert_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 = "culvert_regular_check" ) YEAR )), INTERVAL ( SELECT `value` FROM portal_sys_properties e WHERE e.alias = "culvert_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_culvert_information a LEFT JOIN ( SELECT Max( c.CHECK_DATE_ ) 'CHECK_DATE_', c.CULVERT_ID_ FROM biz_culvert_regular_check c WHERE c.IS_DELE_ = '0' GROUP BY c.CULVERT_ID_ ) b ON a.ID_ = b.CULVERT_ID_ WHERE a.IS_DELE_ = '0' )e ${ew.customSqlSegment} AND checkDate>=#{map.startTime,jdbcType=TIMESTAMP} AND checkDate <=#{map.endTime,jdbcType=TIMESTAMP}