<?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.rtumanage.mapper.RtuManageMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="rtuStatusResultMap" type="org.springblade.modules.business.rtumanage.entity.RtuStatusEntity">
        <result column="rtu_code" property="rtuCode"/>
        <result column="run_status_id" property="runStatusId"/>
        <result column="last_up_time" property="lastUpTime"/>
    </resultMap>

    <!-- 通用查询映射结果 -->
    <resultMap id="rtuStatusVOResultMap" type="org.springblade.modules.business.rtumanage.vo.RtuStatusVO">
        <result column="run_status_id" property="runStatusId"/>
        <result column="last_up_time" property="lastUpTime"/>
        <result column="rtu_code" property="rtuCode"/>
        <result column="rtu_name" property="rtuName"/>
        <result column="lng" property="lng"/>
        <result column="lat" property="lat"/>
        <result column="sim_id" property="simId"/>
        <result column="rtu_kinds" property="rtuKinds"/>
        <result column="location_desc" property="locationDesc"/>
        <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="ad_city" property="adCity"/>
        <result column="ad_dist" property="adDist"/>
        <result column="rtu_brand" property="rtuBrand"/>
        <result column="rtu_model" property="rtuModel"/>
        <result column="rtu_replace_date" property="rtuReplaceDate"/>
        <result column="rain_sensor_brand" property="rainSensorBrand"/>
        <result column="rain_sensor_model" property="rainSensorModel"/>
        <result column="rain_sensor_replace_date" property="rainSensorReplaceDate"/>
        <result column="water_sensor_brand" property="waterSensorBrand"/>
        <result column="water_sensor_model" property="waterSensorModel"/>
        <result column="water_sensor_replace_date" property="waterSensorReplaceDate"/>
        <result column="ground_sensor_brand" property="groundSensorBrand"/>
        <result column="ground_sensor_model" property="groundSensorModel"/>
        <result column="ground_sensor_replace_date" property="groundSensorReplaceDate"/>
        <result column="battery_model" property="batteryModel"/>
        <result column="battery_replace_date" property="batteryReplaceDate"/>
        <result column="sun_power_model" property="sunPowerModel"/>
        <result column="network_sim_id" property="networkSimId"/>
        <result column="network_payer" property="networkPayer"/>
        <result column="network_pay_end_date" property="networkPayEndDate"/>
        <result column="is_satellite" property="isSatellite"/>
        <result column="satellite_model" property="satelliteModel"/>
    </resultMap>


    <select id="selectPage" resultMap="rtuStatusVOResultMap">
        SELECT
        d.*, n.run_status_id,n.last_up_time
        FROM
        rtu_info d
        LEFT JOIN  rtu_status n ON n.rtu_code = d.rtu_code and  n.is_deleted = 0
        LEFT JOIN  data_ad_cd_b a ON a.adcd = d.ad_code and a.is_deleted=0
        WHERE
        d.is_deleted = 0
        <if test="rtuInfoDTO.rtuName!=null">
            and d.rtu_name like concat(concat('%', #{rtuInfoDTO.rtuName}), '%')
        </if>
        <if test="rtuInfoDTO.rtuCode!=null">
            and d.rtu_code like concat(concat('%', #{rtuInfoDTO.rtuCode}), '%')
        </if>
        <if test="rtuInfoDTO.isRain!=null">
            and d.is_rain = #{rtuInfoDTO.isRain}
        </if>
        <if test="rtuInfoDTO.isRiver!=null">
            and d.is_river = #{rtuInfoDTO.isRiver}
        </if>
        <if test="rtuInfoDTO.isRes!=null">
            and d.is_res = #{rtuInfoDTO.isRes}
        </if>
        <if test="rtuInfoDTO.isGround!=null">
            and d.is_ground = #{rtuInfoDTO.isGround}
        </if>
        <if test="rtuInfoDTO.isVideo!=null">
            and d.is_video = #{rtuInfoDTO.isVideo}
        </if>
        <if test="rtuInfoDTO.adCode!=null">
            and (d.ad_code =#{rtuInfoDTO.adCode} or a.ancestors in (SELECT ancestors FROM data_ad_cd_b WHERE ancestors  like concat(concat('%', #{rtuInfoDTO.adCode}), '%')))
        </if>
        order by IF(isnull(n.run_status_id),1,0) ,n.run_status_id desc,d.rtu_code asc
    </select>

    <select id="selectWarnPage" resultMap="rtuStatusVOResultMap">
        SELECT
        d.*, n.run_status_id,n.last_up_time
        FROM
        rtu_info d
        LEFT JOIN  rtu_status n ON n.rtu_code = d.rtu_code and n.is_deleted = 0
        LEFT JOIN  rtu_warning w ON w.rtu_code = d.rtu_code and w.is_deleted = 0
        LEFT JOIN  data_ad_cd_b a ON a.adcd = d.ad_code and a.is_deleted=0
        WHERE
        d.is_deleted = 0
        and w.warning_kind = #{rtuInfoDTO.warnKind}
        <if test="rtuInfoDTO.rtuName!=null">
            and d.rtu_name like concat(concat('%', #{rtuInfoDTO.rtuName}), '%')
        </if>
        <if test="rtuInfoDTO.rtuCode!=null">
            and d.rtu_code like concat(concat('%', #{rtuInfoDTO.rtuCode}), '%')
        </if>
        <if test="rtuInfoDTO.isRain!=null">
            and d.is_rain = #{rtuInfoDTO.isRain}
        </if>
        <if test="rtuInfoDTO.isRiver!=null">
            and d.is_river = #{rtuInfoDTO.isRiver}
        </if>
        <if test="rtuInfoDTO.isRes!=null">
            and d.is_res = #{rtuInfoDTO.isRes}
        </if>
        <if test="rtuInfoDTO.isGround!=null">
            and d.is_ground = #{rtuInfoDTO.isGround}
        </if>
        <if test="rtuInfoDTO.isVideo!=null">
            and d.is_video = #{rtuInfoDTO.isVideo}
        </if>
        <if test="rtuInfoDTO.adCode!=null">
            and (d.ad_code =#{rtuInfoDTO.adCode} or a.ancestors in (SELECT ancestors FROM data_ad_cd_b WHERE ancestors  like concat(concat('%', #{rtuInfoDTO.adCode}), '%')))
        </if>
        order by IF(isnull(n.run_status_id),1,0) ,n.run_status_id desc, d.rtu_code asc
    </select>
</mapper>
