<?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="org.springblade.modules.business.equipment.inspection.base.mapper.EquipmentInspectionMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="equipmentInspectionInfoResultMap"
               type="org.springblade.modules.business.equipment.inspection.base.entity.EquipmentInspectionInfoEntity">
        <result column="inspection_title" property="inspectionTitle"/>
        <result column="inspection_date" property="inspectionDate"/>
        <result column="rtu_code" property="rtuCode"/>
        <result column="before_rain_season_status" property="beforeRainSeasonStatus"/>
        <result column="before_rain_season_report_time" property="beforeRainSeasonReportTime"/>
        <result column="before_rain_season_report_user" property="beforeRainSeasonReportUser"/>
        <result column="before_rain_season_report_id" property="beforeRainSeasonReportId"/>
        <result column="rain_season_first_status" property="rainSeasonFirstStatus"/>
        <result column="rain_season_first_report_time" property="rainSeasonFirstReportTime"/>
        <result column="rain_season_first_report_user" property="rainSeasonFirstReportUser"/>
        <result column="rain_season_first_report_id" property="rainSeasonFirstReportId"/>
        <result column="rain_season_second_status" property="rainSeasonSecondStatus"/>
        <result column="rain_season_second_report_time" property="rainSeasonSecondReportTime"/>
        <result column="rain_season_second_report_user" property="rainSeasonSecondReportUser"/>
        <result column="rain_season_second_report_id" property="rainSeasonSecondReportId"/>
    </resultMap>

    <resultMap id="equipmentInspectionInfoVOResultMap"
               type="org.springblade.modules.business.equipment.inspection.base.vo.EquipmentInspectionInfoVO">
        <result column="rtu_code" property="rtuCode"/>
        <result column="inspection_title" property="inspectionTitle"/>
        <result column="inspection_date" property="inspectionDate"/>
        <result column="before_rain_season_status" property="beforeRainSeasonStatus"/>
        <result column="before_rain_season_report_time" property="beforeRainSeasonReportTime"/>
        <result column="before_rain_season_report_user" property="beforeRainSeasonReportUser"/>
        <result column="before_rain_season_report_id" property="beforeRainSeasonReportId"/>
        <result column="rain_season_first_status" property="rainSeasonFirstStatus"/>
        <result column="rain_season_first_report_time" property="rainSeasonFirstReportTime"/>
        <result column="rain_season_first_report_user" property="rainSeasonFirstReportUser"/>
        <result column="rain_season_first_report_id" property="rainSeasonFirstReportId"/>
        <result column="rain_season_second_status" property="rainSeasonSecondStatus"/>
        <result column="rain_season_second_report_time" property="rainSeasonSecondReportTime"/>
        <result column="rain_season_second_report_user" property="rainSeasonSecondReportUser"/>
        <result column="rain_season_second_report_id" property="rainSeasonSecondReportId"/>
        <result column="rtu_name" property="rtuName"/>
        <result column="before_rain_season_report_user_name" property="beforeRainSeasonReportUserName"/>
        <result column="rain_season_first_report_user_name" property="rainSeasonFirstReportUserName"/>
        <result column="rain_season_second_report_user_name" property="rainSeasonSecondReportUserName"/>

        <result column="ad_city" property="adCity"/>
        <result column="ad_dist" property="adDist"/>

        <result column="is_rain" property="isRain"/>
        <result column="is_river" property="isRiver"/>
        <result column="is_res" property="isRes"/>
        <result column="is_ground" property="isGround"/>
        <result column="is_video" property="isVideo"/>
        <result column="location_desc" property="locationDesc"/>
        <result column="lng" property="lng"/>
        <result column="lat" property="lat"/>
    </resultMap>


    <resultMap id="equipmentInspectionStatisticsVOResultMap"
               type="org.springblade.modules.business.equipment.inspection.base.vo.EquipmentInspectionStatisticsVO">
        <result column="real_name" property="servicePersonName"/>
        <result column="complete_count" property="completeCount"/>
    </resultMap>


    <select id="selectPage" resultMap="equipmentInspectionInfoVOResultMap">
        SELECT
        n.*,
        r.rtu_name ,u.real_name AS before_rain_season_report_user_name,u2.real_name AS
        rain_season_first_report_user_name,u3.real_name AS rain_season_second_report_user_name,r.ad_city,r.ad_dist
        FROM
        equipment_inspection n
        LEFT JOIN rtu_info r ON r.rtu_code = n.rtu_code and r.is_deleted=0
        LEFT JOIN data_ad_cd_b a ON a.adcd = r.ad_code and a.is_deleted=0
        LEFT JOIN blade_user u ON u.id = n.before_rain_season_report_user and u.is_deleted=0
        LEFT JOIN blade_user u2 ON u2.id = n.rain_season_first_report_user and u2.is_deleted=0
        LEFT JOIN blade_user u3 ON u3.id = n.rain_season_second_report_user and u2.is_deleted=0
        WHERE
        n.is_deleted = 0
        <if test="inspectionInfoDTO.rtuCode!=null">
            and n.rtu_code like concat(concat('%', #{inspectionInfoDTO.rtuCode}), '%')
        </if>
        <if test="inspectionInfoDTO.rtuName!=null">
            and r.rtu_name like concat(concat('%', #{inspectionInfoDTO.rtuName}), '%')
        </if>
        <if test="inspectionInfoDTO.inspectionYearStartDate!=null">
            and DATE_FORMAT(n.inspection_date,'%Y-%m-%d') &gt;=
            DATE_FORMAT(#{inspectionInfoDTO.inspectionYearStartDate},'%Y-%m-%d')
        </if>
        <if test="inspectionInfoDTO.inspectionYearEndDate!=null">
            and DATE_FORMAT(n.inspection_date,'%Y-%m-%d') &lt;
            DATE_FORMAT(#{inspectionInfoDTO.inspectionYearEndDate},'%Y-%m-%d')
        </if>
        <if test="inspectionInfoDTO.beforeRainSeasonStatus!=null">
            and  n.before_rain_season_status = #{inspectionInfoDTO.beforeRainSeasonStatus}
        </if>
        <if test="inspectionInfoDTO.rainSeasonFirstStatus!=null">
            and  n.rain_season_first_status = #{inspectionInfoDTO.rainSeasonFirstStatus}
        </if>
        <if test="inspectionInfoDTO.rainSeasonSecondStatus!=null">
            and  n.rain_season_second_status = #{inspectionInfoDTO.rainSeasonSecondStatus}
        </if>
        <if test="inspectionInfoDTO.beforeRainSeasonReportUser!=null">
            and n.before_rain_season_report_user = #{inspectionInfoDTO.beforeRainSeasonReportUser}
        </if>
        <if test="inspectionInfoDTO.rainSeasonFirstReportUser!=null">
            and n.rain_season_first_report_user = #{inspectionInfoDTO.rainSeasonFirstReportUser}
        </if>
        <if test="inspectionInfoDTO.rainSeasonSecondReportUser!=null">
            and n.rain_season_second_report_user = #{inspectionInfoDTO.rainSeasonSecondReportUser}
        </if>
        <if test="inspectionInfoDTO.adCode!=null">
            and (a.adcd =#{inspectionInfoDTO.adCode} or a.ancestors in (SELECT ancestors FROM data_ad_cd_b WHERE
            ancestors like concat(concat('%', #{inspectionInfoDTO.adCode}), '%')))
        </if>
        <choose>
            <when test="inspectionInfoDTO.order!=null and inspectionInfoDTO.order=='ascending'">
                <if test="inspectionInfoDTO.prop!=null and inspectionInfoDTO.prop=='beforeRainSeasonReportTime'">
                    order by n.before_rain_season_report_time
                </if>
                <if test="inspectionInfoDTO.prop!=null and inspectionInfoDTO.prop=='rainSeasonFirstReportTime'">
                    order by n.rain_season_first_report_time
                </if>
                <if test="inspectionInfoDTO.prop!=null and inspectionInfoDTO.prop=='rainSeasonSecondReportTime'">
                    order by n.rain_season_second_report_time
                </if>
            </when>
            <when test="inspectionInfoDTO.order!=null and inspectionInfoDTO.order=='descending'">
                <if test="inspectionInfoDTO.prop!=null and inspectionInfoDTO.prop=='beforeRainSeasonReportTime'">
                    order by n.before_rain_season_report_time desc
                </if>
                <if test="inspectionInfoDTO.prop!=null and inspectionInfoDTO.prop=='rainSeasonFirstReportTime'">
                    order by n.rain_season_first_report_time desc
                </if>
                <if test="inspectionInfoDTO.prop!=null and inspectionInfoDTO.prop=='rainSeasonSecondReportTime'">
                    order by n.rain_season_second_report_time desc
                </if>
            </when>
            <otherwise>
                order by n.rtu_code
            </otherwise>
        </choose>
    </select>


    <select id="selectRtuPage" resultMap="equipmentInspectionInfoVOResultMap">
        SELECT
        r.rtu_name,r.rtu_code,r.ad_city,r.ad_dist,r.is_rain,r.is_river,r.is_res,r.is_ground,r.is_video,r.location_desc,r.lng,r.lat, n.run_status_id,n.last_up_time
        FROM
        rtu_info r
        LEFT JOIN  rtu_status n ON n.rtu_code = r.rtu_code and  n.is_deleted = 0
        LEFT JOIN data_ad_cd_b a ON a.adcd = r.ad_code and a.is_deleted=0
        WHERE
        r.is_deleted = 0
        <if test="inspectionInfoDTO.rtuCode!=null">
            and r.rtu_code like concat(concat('%', #{inspectionInfoDTO.rtuCode}), '%')
        </if>
        <if test="inspectionInfoDTO.rtuName!=null">
            and r.rtu_name like concat(concat('%', #{inspectionInfoDTO.rtuName}), '%')
        </if>
        <if test="inspectionInfoDTO.adCode!=null">
            and (a.adcd =#{inspectionInfoDTO.adCode} or a.ancestors in (SELECT ancestors FROM data_ad_cd_b WHERE
            ancestors like concat(concat('%', #{inspectionInfoDTO.adCode}), '%')))
        </if>
        and r.rtu_code not in (select n.rtu_code from equipment_inspection n where
        n.is_deleted = 0
        <if test="inspectionInfoDTO.inspectionYearStartDate!=null">
            and DATE_FORMAT(n.inspection_date,'%Y-%m-%d') &gt;= DATE_FORMAT(#{inspectionInfoDTO.inspectionYearStartDate},'%Y-%m-%d')
        </if>
        <if test="inspectionInfoDTO.inspectionYearEndDate!=null">
            and DATE_FORMAT(n.inspection_date,'%Y-%m-%d') &lt; DATE_FORMAT(#{inspectionInfoDTO.inspectionYearEndDate},'%Y-%m-%d')
        </if>
        <if test="inspectionInfoDTO.beforeRainSeasonStatus!=null">
            and n.before_rain_season_status = #{inspectionInfoDTO.beforeRainSeasonStatus}
        </if>
        <if test="inspectionInfoDTO.rainSeasonFirstStatus!=null">
            and n.rain_season_first_status = #{inspectionInfoDTO.rainSeasonFirstStatus}
        </if>
        <if test="inspectionInfoDTO.rainSeasonSecondStatus!=null">
            and n.rain_season_second_status= #{inspectionInfoDTO.rainSeasonSecondStatus}
        </if>
        )
        order by r.rtu_code
    </select>

    <select id="inspectionReportCount" resultMap="equipmentInspectionStatisticsVOResultMap">
        SELECT u.real_name,u.id,s.complete_count FROM blade_user u LEFT JOIN
        (SELECT create_user,count(create_user) AS complete_count FROM equipment_inspection_report_info WHERE is_deleted
        =0
        <if test="inspectionInfoDTO.planId!=null">
            AND plan_id= #{inspectionInfoDTO.planId}
        </if>
        GROUP BY create_user) s
        on s.create_user = u.id
        WHERE u.is_deleted=0
        <if test="inspectionInfoDTO.orgId!=null">
            AND u.dept_id = #{inspectionInfoDTO.orgId}
        </if>
    </select>

    <select id="inspectionCount" resultType="java.lang.Long">
        SELECT
        count(*)
        FROM
        equipment_inspection n
        LEFT JOIN rtu_info i ON n.rtu_code = i.rtu_code and i.is_deleted=0
        LEFT JOIN data_ad_cd_b a ON a.adcd = i.ad_code and a.is_deleted=0
        WHERE
        n.is_deleted = 0
        <if test="inspectionInfoDTO.inspectionYearStartDate!=null">
            and DATE_FORMAT(n.inspection_date,'%Y-%m-%d') &gt;=
            DATE_FORMAT(#{inspectionInfoDTO.inspectionYearStartDate},'%Y-%m-%d')
        </if>
        <if test="inspectionInfoDTO.inspectionYearEndDate!=null">
            and DATE_FORMAT(n.inspection_date,'%Y-%m-%d') &lt;
            DATE_FORMAT(#{inspectionInfoDTO.inspectionYearEndDate},'%Y-%m-%d')
        </if>
        <if test="inspectionInfoDTO.beforeRainSeasonReportUser!=null">
            and n.before_rain_season_report_user = #{inspectionInfoDTO.beforeRainSeasonReportUser}
        </if>
        <if test="inspectionInfoDTO.rainSeasonFirstReportUser!=null">
            and n.rain_season_first_report_user = #{inspectionInfoDTO.rainSeasonFirstReportUser}
        </if>
        <if test="inspectionInfoDTO.rainSeasonSecondReportUser!=null">
            and n.rain_season_second_report_user = #{inspectionInfoDTO.rainSeasonSecondReportUser}
        </if>
        <if test="inspectionInfoDTO.adCode!=null">
            and (a.adcd =#{inspectionInfoDTO.adCode} or a.ancestors in (SELECT ancestors FROM data_ad_cd_b WHERE
            ancestors like concat(concat('%', #{inspectionInfoDTO.adCode}), '%')))
        </if>
    </select>


    <select id="selectUnfinishedInspectionEquipmentPage" resultMap="equipmentInspectionInfoVOResultMap">
        SELECT r1.rtu_code,r1.rtu_name,r1.ad_city,r1.ad_dist,r1.is_rain,r1.is_river,r1.is_res,r1.is_ground,r1.is_video,r1.location_desc,r1.lng,r1.lat,i1.before_rain_season_status,i1.rain_season_first_status ,i1.rain_season_second_status FROM rtu_info r1
        INNER JOIN ( SELECT r.rtu_code
        FROM rtu_info r
        LEFT JOIN equipment_inspection i on i.rtu_code = r.rtu_code   and i.is_deleted =0
        <if test="inspectionInfoDTO.inspectionYearStartDate!=null">
            and i.create_time &gt;= #{inspectionInfoDTO.inspectionYearStartDate}
        </if>
        <if test="inspectionInfoDTO.inspectionYearEndDate!=null">
            and i.create_time &lt; #{inspectionInfoDTO.inspectionYearEndDate}
        </if>
        <if test="inspectionInfoDTO.rainSeasonKind!=null">
            <if test="inspectionInfoDTO.rainSeasonKind == 1">
                and (i.before_rain_season_status is not null  and i.before_rain_season_status = 1)
            </if>
            <if test="inspectionInfoDTO.rainSeasonKind == 2">
                and (i.rain_season_first_status is not null  and i.rain_season_first_status = 1)
            </if>
            <if test="inspectionInfoDTO.rainSeasonKind == 3">
                and (i.rain_season_second_status is not null  and i.rain_season_second_status = 1)
            </if>
        </if>
        WHERE  r.is_deleted =0  and  i.rtu_code is null ) s1  on s1.rtu_code = r1.rtu_code
        LEFT JOIN equipment_inspection i1 on i1.rtu_code = r1.rtu_code and i1.is_deleted=0
        <if test="inspectionInfoDTO.inspectionYearStartDate!=null">
            and i1.create_time &gt;= #{inspectionInfoDTO.inspectionYearStartDate}
        </if>
        <if test="inspectionInfoDTO.inspectionYearEndDate!=null">
            and i1.create_time &lt; #{inspectionInfoDTO.inspectionYearEndDate}
        </if>
        LEFT JOIN data_ad_cd_b a ON a.adcd = r1.ad_code and a.is_deleted=0
        WHERE r1.is_deleted=0
        <if test="inspectionInfoDTO.adCode!=null">
            and (a.adcd =#{inspectionInfoDTO.adCode} or a.ancestors in (SELECT ancestors FROM data_ad_cd_b WHERE
            ancestors
            like concat(concat('%', #{inspectionInfoDTO.adCode}), '%')))
        </if>
        order by  i1.before_rain_season_status desc ,i1.rain_season_first_status  desc ,i1.rain_season_second_status desc , r1.rtu_code ASC
    </select>

    <select id="selectUnfinishedInspectionEquipmentList" resultMap="equipmentInspectionInfoVOResultMap">
        SELECT r1.rtu_code,r1.rtu_name,r1.ad_city,r1.ad_dist,r1.is_rain,r1.is_river,r1.is_res,r1.is_ground,r1.is_video,r1.location_desc,r1.lng,r1.lat,i1.before_rain_season_status,i1.rain_season_first_status ,i1.rain_season_second_status FROM rtu_info r1
        INNER JOIN ( SELECT r.rtu_code
        FROM rtu_info r
        LEFT JOIN equipment_inspection i on i.rtu_code = r.rtu_code   and i.is_deleted =0
        <if test="inspectionInfoDTO.inspectionYearStartDate!=null">
            and i.create_time &gt;= #{inspectionInfoDTO.inspectionYearStartDate}
        </if>
        <if test="inspectionInfoDTO.inspectionYearEndDate!=null">
            and i.create_time &lt; #{inspectionInfoDTO.inspectionYearEndDate}
        </if>
        <if test="inspectionInfoDTO.rainSeasonKind!=null">
            <if test="inspectionInfoDTO.rainSeasonKind == 1">
                and (i.before_rain_season_status is not null  and i.before_rain_season_status = 1)
            </if>
            <if test="inspectionInfoDTO.rainSeasonKind == 2">
                and (i.rain_season_first_status is not null  and i.rain_season_first_status = 1)
            </if>
            <if test="inspectionInfoDTO.rainSeasonKind == 3">
                and (i.rain_season_second_status is not null  and i.rain_season_second_status = 1)
            </if>
        </if>
        WHERE  r.is_deleted =0  and  i.rtu_code is null ) s1  on s1.rtu_code = r1.rtu_code
        LEFT JOIN equipment_inspection i1 on i1.rtu_code = r1.rtu_code and i1.is_deleted=0
        <if test="inspectionInfoDTO.inspectionYearStartDate!=null">
            and i1.create_time &gt;= #{inspectionInfoDTO.inspectionYearStartDate}
        </if>
        <if test="inspectionInfoDTO.inspectionYearEndDate!=null">
            and i1.create_time &lt; #{inspectionInfoDTO.inspectionYearEndDate}
        </if>
        LEFT JOIN data_ad_cd_b a ON a.adcd = r1.ad_code and a.is_deleted=0
        WHERE r1.is_deleted=0
        <if test="inspectionInfoDTO.adCode!=null">
            and (a.adcd =#{inspectionInfoDTO.adCode} or a.ancestors in (SELECT ancestors FROM data_ad_cd_b WHERE
            ancestors
            like concat(concat('%', #{inspectionInfoDTO.adCode}), '%')))
        </if>
        order by  r1.ad_code, r1.rtu_code
    </select>
</mapper>
