<?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="im.zhaojun.system.mapper.TraffdevicewriteresultMapper">

    <select id="querySbtdspsrResultByPage" resultType="im.zhaojun.system.model.SbtdspsrResult">
        select
            ta.*,
            a.createtime,
            a.pushstatus,
            a.pushcount,
            a.remark,
            a.pushdesc,
            p.xzmc as xztreename
        from sbtdspsr ta
        left join traffdevicewriteresult a on a.sbbh = ta.sbbh and a.tdbh = ta.tdbh
        left join ptxzs p on p.xzbh = ta.xzbh
        <where>
        <if test="sbbh !=null and sbbh != ''">
            and (ta.sbbh like concat('%',#{sbbh},'%') OR ta.tdmc like '%'||#{sbbh}||'%' OR ta.tdmcpy like '%'||#{sbbh}||'%' )
        </if>
        <if test="xzbh !=null and xzbh != ''">
            AND ta.xzbh IN
            in (
            select t1.dept_id
            from dept t1
            left join dept t2
            on t1.parent_id = t2.dept_id
            where t1.parent_id =#{xzbh}
            or t1.dept_id =#{xzbh}
            or t2.parent_id =#{xzbh}
            or t2.dept_id = #{xzbh}
            )
        </if>
        <if test="pushstatus !=null and pushstatus != 9">
            and a.pushstatus = #{pushstatus}
        </if>
        <if test="pushstatus !=null and pushstatus == 9">
            and (a.pushstatus is null)
        </if>
        </where>
    </select>

    <select id="querySbtdspsrByXh" resultType="im.zhaojun.system.model.SbtdspsrParams">
        select
        ta.*,
        a.createtime,
        a.pushstatus,
        a.pushcount,
        a.remark,
        a.pushdesc,
        p.xzmc as xztreename,
        b.remark1 as deviceconfig
        from sbtdspsr ta
        left join traffdevicewriteresult a on a.sbbh = ta.sbbh and a.tdbh = ta.tdbh
        left join traffdeviceconfig b on b.fdid = ta.sbbh and b.channelid = ta.tdbh
        left join ptxzs p on p.xzbh = ta.xzbh
        <where>
        <if test="xhs !=null and xhs.size>0 ">
            and ta.xh in
                <foreach item="xh" collection="xhs" open="(" separator="," close=")">
                    #{xh}
                </foreach>
        </if>

        </where>
    </select>
</mapper>
