<?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.zbTable.mapper.ZbTableMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="tableResultMap" type="org.springblade.modules.zbTable.entity.ZbTableEntity">
        <result column="id" property="id"/>
        <result column="zblx" property="zblx"/>
        <result column="zbbh" property="zbbh"/>
        <result column="sssj_bm" property="sssjBm"/>
        <result column="sssj_mc" property="sssjMc"/>
        <result column="ssfj_bm" property="ssfjBm"/>
        <result column="ssfj_mc" property="ssfjMc"/>
        <result column="sspcs_bm" property="sspcsBm"/>
        <result column="sspcs_mc" property="sspcsMc"/>
        <result column="create_user" property="createUser"/>
        <result column="create_dept" property="createDept"/>
        <result column="create_time" property="createTime"/>
        <result column="update_user" property="updateUser"/>
        <result column="update_time" property="updateTime"/>
        <result column="status" property="status"/>
        <result column="is_deleted" property="isDeleted"/>
    </resultMap>


    <select id="selectTablePage" resultMap="tableResultMap">
        select
            zt.id,
            zt.zblx,
            zt.zbbh,
            zt.zbyt,
            zt.sspcs_bm,
            zbjwd.jd,
            zbjwd.wd,
            zbls.lqrxm,
            zbls.xb,
            zbls.sfzh,
            zbls.jh,
            zbls.lxdh,
            zbls.lqsj,
            zbls.ghsj,
            zbls.zbzt
        from zb_table zt
        left join (
            select
                zbbh,
                jd,
                wd
            from zbjwd_table
            where (zbbh,create_time) in (select zbbh, MAX(create_time) from zbjwd_table group by zbbh ) ) zbjwd on zt.zbbh = zbjwd.zbbh
        left join (
            select
                zbbh,
                lqrxm,
                xb,
                sfzh,
                jh,
                lxdh,
                lqsj,
                ghsj,
                zbyt,
                zbzt
            from zbls_table
            where (zbbh,create_time) in (select zbbh, MAX(create_time) from zbls_table group by zbbh ) ) zbls on zt.zbbh = zbls.zbbh
        where zt.is_deleted = 0
        <if test="table.type == 1">
            and (zt.zbzt != '已归还' or zt.zbzt != '延期归还')
        </if>
        <if test="table.type == 2">
            and (zt.zbzt = '已归还' or zt.zbzt = '延期归还')
        </if>
        <if test="table.deptList != null and table.deptList.size > 0">
            and (zt.sssj_bm in
                <foreach collection="table.deptList" index="index" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
                 or zt.ssfj_bm in
                <foreach collection="table.deptList" index="index" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
                 or zt.sspcs_bm in
                <foreach collection="table.deptList" index="index" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach> )
        </if>
        <if test="table.zbzt == '01'">
            and zbls.zbzt = '01'
        </if>
        <if test="table.zbzt == '02'">
            and zbls.zbzt = '02'
        </if>
        <if test="table.zbzt == '03'">
            and zbls.zbzt = '03'
        </if>
        <if test="table.zbzt == '04'">
            and zbls.zbzt = '04'
        </if>
        <if test="table.zbzt == '05'">
            and zbls.zbzt = '05'
        </if>
        <if test="table.zbbh != '' and table.zbbh != null">
            and zt.zbbh = #{table.zbbh}
        </if>
        <if test="table.zbzt == '01,02'">
            and (zbls.zbzt = '01' or zbls.zbzt = '02')
        </if>
        <if test="table.zbzt == '03,04'">
            and (zbls.zbzt = '03' or zbls.zbzt = '04')
        </if>
    </select>

    <select id="selectLsjl" resultType="org.springblade.modules.zblsTable.entity.ZblsTableEntity">
        select * from zbls_table where is_deleted = 0 and zbbh = #{zbbh} and ghsj is not null order by lqsj desc
    </select>

    <select id="selectZbByZbbh" resultMap="tableResultMap">
        select
            zbls.zbzt
        from zb_table zt
        left join zbls_table zbls on zt.zbbh = zbls.zbbh
        where zt.is_deleted = 0 and zt.zbbh = #{zbbh}
        order by zbls.create_time desc
        limit 1
    </select>

    <select id="getLsjlByLqsj" resultType="org.springblade.modules.zblsTable.entity.ZblsTableEntity">
        select * from zbls_table where is_deleted = 0 and zbbh = #{zbbh} order by create_time desc limit 1
    </select>

    <select id="selectDt" resultMap="tableResultMap">
        select
            zt.id,
            zbjwd.zbbh,
            zbjwd.jd,
            zbjwd.wd
        from zb_table zt
        left join zbjwd_table zbjwd on zt.zbbh = zbjwd.zbbh
        left join (
            select
                zbbh,
                lqrxm,
                xb,
                jh,
                lxdh,
                lqsj,
                ghsj,
                zbyt,
                zbzt
            from zbls_table
            where (zbbh,create_time) in (select zbbh, MAX(create_time) from zbls_table group by zbbh ) ) zbls on zt.zbbh = zbls.zbbh
        where zt.is_deleted = 0 and (zbls.zbzt = '01' or zbls.zbzt = '02')
    </select>

    <select id="getZbByDw" resultMap="tableResultMap">
        select
            *
        from zb_table zt
        left join zbls_table zbls on zt.zbbh = zbls.zbbh
        where zt.is_deleted = 0 and (zbls.zbzt = '01' or zbls.zbzt = '02')
        and zt.id in
        <foreach collection="id" index="index" item="id" open="(" separator="," close=")">
            #{id} :: int8
        </foreach>
    </select>

    <select id="getZbzxdw" resultType="org.springblade.modules.zbTable.vo.TQDevBaseVO">
        select * from t_q_dev_base where qid = #{zbbh}
    </select>

    <select id="selectZbByPcs" resultMap="tableResultMap">
        select * from zb_table where is_deleted = 0 and (sssj_bm = #{deptId} :: int8 or ssfj_bm = #{deptId} :: int8 or sspcs_bm = #{deptId} :: int8)
    </select>

    <select id="selectZbbh" resultMap="tableResultMap">
        select * from zb_table where is_deleted = 0 and zbbh = #{zbbh}
    </select>
</mapper>
