<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.tofly.gpsboot.mapper.plan.PlanMapper">

    <resultMap id="planMap" type="com.tofly.pojo.plan.Plan">
        <id property="id" column="ID"/>
        <result property="planType" column="PLAN_TYPE"/>
        <result property="mouthId" column="MOUTH_ID"/>
        <result property="periodId" column="PERIOD_ID"/>
        <result property="planBegindate" column="PLAN_BEGINDATE"/>
        <result property="planEnddate" column="PLAN_ENDDATE"/>
        <result property="regionId" column="REGION_ID"/>
        <result property="regionName" column="REGION_NAME"/>
        <result property="inspectType" column="INSPECT_TYPE"/>
        <result property="inspectContents" column="INSPECT_CONTENTS"/>
        <result property="isread" column="ISREAD"/>
        <result property="isover" column="ISOVER"/>
        <result property="planState" column="PLAN_STATE"/>
        <result property="lossRate" column="LOSS_RATE"/>
        <result property="intervalDay" column="INTERVAL_DAY"/>
        <result property="planPercent" column="PLAN_PERCENT"/>
        <result property="percent" column="PERCENT"/>
        <result property="totalLength" column="TOTAL_LENGTH"/>
        <result property="totalCount" column="TOTAL_COUNT"/>
        <result property="description" column="DESCRIPTION"/>
        <result property="lastTime" column="LAST_TIME"/>
        <result property="lastUser" column="LAST_USER"/>
        <result property="createUser" column="CREATE_USER"/>
        <result property="createTime" column="CREATE_TIME"/>
        <result property="isDispatching" column="IS_DISPATCHING"/>
        <result property="timeoutRemindFlag" column="TIMEOUT_REMIND_FLAG"/>
        <result property="maintenanceFlag" column="MAINTENANCE_FLAG"/>
        <result property="isEmergency" column="IS_EMERGENCY"/>
        <association property="overRate" column="ID" select="getOverRate"/>
        <association property="lossRate" column="ID" select="getLossRate"/>
        <association property="planTypeName" column="PLAN_TYPE" select="getPlanTypeName"/>
        <association property="periodName" column="PERIOD_ID" select="com.tofly.gpsboot.mapper.plan.PeriodMapper.getPeriodName"/>
        <association property="inspectTypeName" column="INSPECT_TYPE" select="getInspectTypeName"/>
        <association property="planStateName" column="PLAN_STATE" select="getPlanStateName"/>
        <association property="inspectGroup" column="ID" select="getInspectGroup"/>
        <association property="inspectGroupId" column="ID" select="getInspectGroupId"/>
        <association property="inspectUserList" column="ID" select="getInspectUserList"/>
        <association property="inspectUserIdList" column="ID" select="getInspectUserIdList"/>
        <association property="isDispatchingName" column="IS_DISPATCHING" select="getIsDispatchingName"/>
        <association property="isPlanStopName" column="ID" select="getIsPlanStopName"/>
        <association property="isPlanInvalidName" column="ID" select="getIsPlanInvalidName"/>
        <association property="isEmergencyName" column="IS_EMERGENCY" select="getIsEmergencyName"/>
        <association property="timeoutRemindFlagName" column="TIMEOUT_REMIND_FLAG" select="getTimeoutRemindFlagName"/>
        <association property="maintenanceFlagName" column="MAINTENANCE_FLAG" select="getMaintenanceFlagName"/>
        <association property="createUserName" column="CREATE_USER" select="com.tofly.gpsboot.mapper.plan.HydrantrecordMapper.getRealName"/>
        <collection property="planTypeOverList" column="ID" select="com.tofly.gpsboot.mapper.plan.PlantypeMapper.getPlanTypeOverList"/>

    </resultMap>
    <select id="getIsEmergencyName" resultType="string">
        SELECT CNAME
        FROM
            TF_YWPN_SYSCHILDCODE_D
        WHERE
            PCID =52600
          AND
            CCODE = #{isDispatching}
    </select>
    <select id="getOverRate" resultType="java.math.BigDecimal">
        SELECT CASE MAINTENANCE_FLAG  WHEN '0' THEN T2.PERCENT
                                      WHEN '1' THEN t2.EQUIPMENT_RATE
                   END
        FROM TF_YWPN_PLAN_W t1
                 LEFT JOIN TF_YWPN_PLANTYPE_W t2 ON T1.ID = T2.PLAN_ID
        WHERE t1.ID = #{id}
    </select>
    <select id="getLossRate" resultType="java.math.BigDecimal">
        SELECT CASE MAINTENANCE_FLAG  WHEN '0' THEN T2.LOSS_RATE
                                      WHEN '1' THEN t2.LOSS_EQUIPMENT_RATE
                   END
        FROM TF_YWPN_PLAN_W t1
                 LEFT JOIN TF_YWPN_PLANTYPE_W t2 ON T1.ID = T2.PLAN_ID
        WHERE t1.ID = #{id}
    </select>

    <select id="getIsDispatchingName" resultType="string">
        SELECT CNAME
        FROM
            TF_YWPN_SYSCHILDCODE_D
        WHERE
            PCID =51100
          AND
            CCODE = #{isDispatching}
    </select>

    <select id="getMaintenanceFlagName" resultType="string">
        SELECT CNAME
        FROM
            TF_YWPN_SYSCHILDCODE_D
        WHERE
            PCID =51600
          AND
            CCODE = #{maintenanceFlag}
    </select>
    <select id="getPlanTypeName" resultType="string">
        SELECT CNAME
        FROM
            TF_YWPN_SYSCHILDCODE_D
        WHERE
            PCID =50900
          AND
            CCODE = #{planType}
    </select>
    <select id="getInspectTypeName" resultType="string" databaseId="oracle">
        select wm_concat(name)
        from TF_YWPN_INSPECTTYPE_W
        where 1=1
        <if test="inspectType != null and inspectType != ''  "> and id in
            <foreach item="nodeCodestr" collection="inspectType.split(',')" open="(" separator="," close=")">
                #{nodeCodestr}
            </foreach>
        </if>
    </select>
    <select id="getPlanStateName" resultType="string">
        SELECT CNAME
        FROM
            TF_YWPN_SYSCHILDCODE_D
        WHERE
            PCID =51000
          AND
            CCODE = #{planState}
    </select>
    <select id="getInspectGroup" resultType="string">
        select t2.name
        from  TF_YWPN_PLAN_INSPECT_GROUP_W t1 left join  TF_YWPN_INSPECTGROUP_W t2 on t1.INSPECT_GROUP_ID = t2.ID
        where t1.PLAN_ID = #{id}
    </select>
    <select id="getInspectGroupId" resultType="java.lang.Long">
        select t2.ID
        from  TF_YWPN_PLAN_INSPECT_GROUP_W t1 left join  TF_YWPN_INSPECTGROUP_W t2 on t1.INSPECT_GROUP_ID = t2.ID
        where t1.PLAN_ID = #{id}
    </select>

    <select id="getInspectUserList" resultType="string">
        select wm_concat(t2.REAL_NAME)
        from TF_YWPN_PLAN_INSPECT_USER_W t1 left join TF_SMPN_USER_X t2 on t1.USER_ID = t2.ID
        where t1.PLAN_ID = #{id}
    </select>
    <select id="getInspectUserIdList" resultType="string">
        select wm_concat(t2.ID)
        from TF_YWPN_PLAN_INSPECT_USER_W t1 left join TF_SMPN_USER_X t2 on t1.USER_ID = t2.ID
        where t1.PLAN_ID = #{id}
    </select>

    <select id="getIsPlanStopName" resultType="string">
        select CASE PLAN_STATE
                   WHEN '8' THEN '是'
                   ELSE '否'
                   END
        from TF_YWPN_PLAN_W
        where ID = #{id}
    </select>
    <select id="getIsPlanInvalidName" resultType="string">
        select CASE PLAN_STATE
                   WHEN '10' THEN '是'
                   ELSE '否'
                   END
        from TF_YWPN_PLAN_W
        where ID = #{id}
    </select>
    <select id="getTimeoutRemindFlagName" resultType="string">
        SELECT CNAME
        FROM
            TF_YWPN_SYSCHILDCODE_D
        WHERE
            PCID =51400
          AND
            CCODE = #{planState}
    </select>

    <select id="getDistinctPlanIdList" resultType="java.lang.Long">
        select distinct(ID) as ID from TF_YWPN_PLAN_W
        where (to_char(PLAN_BEGINDATE, 'yyyy-mm') &lt; to_char(#{startTime},'yyyy-mm')
          and to_char(PLAN_ENDDATE, 'yyyy-mm') >= to_char(#{endTime},'yyyy-mm'))
            or
            (to_char(PLAN_BEGINDATE, 'yyyy-mm')  >= to_char(#{startTime},'yyyy-mm')
                and to_char(PLAN_ENDDATE, 'yyyy-mm') &lt; to_char(#{endTime},'yyyy-mm'))
    </select>
</mapper>
