<?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.qianhe.mapper.SjFybpZbMapper">
    
    <resultMap type="SjFybpZb" id="SjFybpZbResult">
        <result property="id"    column="id"    />
        <result property="fyrq"    column="fyrq"    />
        <result property="deptId"    column="dept_id"    />
        <result property="fyje"    column="fyje"    />
        <result property="zt"    column="zt"    />
        <result property="tjr"    column="tjr"    />
        <result property="tjrq"    column="tjrq"    />
        <result property="sprq"    column="sprq"    />
        <result property="spr"    column="spr"    />
        <result property="ysyje"    column="ysyje"    />
        <result property="spyj"    column="spyj"    />
        <result property="createBy"    column="create_by"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateBy"    column="update_by"    />
        <result property="updateTime"    column="update_time"    />
        <result property="remark"    column="remark"    />
        <result property="yl1"    column="yl1"    />
        <result property="yl2"    column="yl2"    />
        <result property="yl3"    column="yl3"    />
        <result property="yl4"    column="yl4"    />
        <result property="yl5"    column="yl5"    />

        <result property="deptName"    column="dept_name"    />
        <result property="sprName"    column="spr_name"    />

    </resultMap>

    <resultMap id="SjFybpZbSjFybpResult" type="SjFybpZb" extends="SjFybpZbResult">
        <collection property="sjFybpList" notNullColumn="sub_id" javaType="java.util.List" resultMap="SjFybpResult" />
    </resultMap>

    <resultMap type="SjFybp" id="SjFybpResult">
        <result property="id"    column="sub_id"    />
        <result property="fymc"    column="sub_fymc"    />
        <result property="fyrq"    column="sub_fyrq"    />
        <result property="deptId"    column="sub_dept_id"    />
        <result property="fylx"    column="sub_fylx"    />
        <result property="fyms"    column="sub_fyms"    />
        <result property="fyje"    column="sub_fyje"    />
        <result property="fjid"    column="sub_fjid"    />
        <result property="zt"    column="sub_zt"    />
        <result property="tjr"    column="sub_tjr"    />
        <result property="tjrq"    column="sub_tjrq"    />
        <result property="sprq"    column="sub_sprq"    />
        <result property="spr"    column="sub_spr"    />
        <result property="pfje"    column="sub_pfje"    />
        <result property="ysyje"    column="sub_ysyje"    />
        <result property="spyj"    column="sub_spyj"    />
        <result property="createBy"    column="sub_create_by"    />
        <result property="createTime"    column="sub_create_time"    />
        <result property="updateBy"    column="sub_update_by"    />
        <result property="updateTime"    column="sub_update_time"    />
        <result property="remark"    column="sub_remark"    />
        <result property="yl1"    column="sub_yl1"    />
        <result property="yl2"    column="sub_yl2"    />
        <result property="yl3"    column="sub_yl3"    />
        <result property="yl4"    column="sub_yl4"    />
        <result property="yl5"    column="sub_yl5"    />
        <result property="zbId"    column="sub_zb_id"    />
    </resultMap>

    <sql id="selectSjFybpZbVo">
        select id,
               fyrq,
               a.dept_id,
               fyje,
               zt,
               tjr,
               tjrq,
               sprq,
               spr,
               ysyje,
               spyj,
               a.create_by,
               a.create_time,
               a.update_by,
               a.update_time,
               a.remark,
               yl1,
               yl2,
               yl3,
               yl4,
               yl5,
               d.dept_name,
               u.user_name spr_name
             from sj_fybp_zb a
                 left join  sys_dept d on a.dept_id=d.dept_id
                 left join sys_user u on a.spr=u.user_name
</sql>

    <select id="selectSjFybpZbList" parameterType="SjFybpZb" resultMap="SjFybpZbResult">
        <include refid="selectSjFybpZbVo"/>
        <where>  
            <if test="fyrq != null  and fyrq != ''"> and fyrq = #{fyrq}</if>
            <if test="deptId != null  and deptId != ''"> and (a.dept_id = #{deptId} or  find_in_set(#{deptId},d.ancestors))</if>
            <if test="fyje != null "> and fyje = #{fyje}</if>
            <if test="zt != null  and zt != ''"> and zt = #{zt}</if>
            <if test="tjr != null  and tjr != ''"> and tjr = #{tjr}</if>
            <if test="tjrq != null  and tjrq != ''"> and tjrq = #{tjrq}</if>
            <if test="sprq != null  and sprq != ''"> and sprq = #{sprq}</if>
            <if test="spr != null  and spr != ''"> and spr = #{spr}</if>
            <if test="ysyje != null "> and ysyje = #{ysyje}</if>
            <if test="spyj != null  and spyj != ''"> and spyj = #{spyj}</if>
            <if test="yl1 != null  and yl1 != ''"> and yl1 = #{yl1}</if>
            <if test="yl2 != null  and yl2 != ''"> and yl2 = #{yl2}</if>
            <if test="yl3 != null  and yl3 != ''"> and yl3 = #{yl3}</if>
            <if test="yl4 != null  and yl4 != ''"> and yl4 = #{yl4}</if>
            <if test="yl5 != null  and yl5 != ''"> and yl5 = #{yl5}</if>
            <if test="spcx != null  and spcx != '' and spcx=='1'.toString()"> and zt !='未提交'</if>
            <!-- 数据范围过滤 -->
            ${params.dataScope}
        </where>
        order by a.create_time desc
    </select>
    
    <select id="selectSjFybpZbById" parameterType="Long" resultMap="SjFybpZbResult">
        select a.id, a.fyrq, a.dept_id, a.fyje, a.zt, a.tjr, a.tjrq, a.sprq, a.spr, a.ysyje, a.spyj, a.create_by, a.create_time, a.update_by, a.update_time, a.remark, a.yl1, a.yl2, a.yl3, a.yl4, a.yl5,d.dept_name
        from sj_fybp_zb a
                 left join  sys_dept d on a.dept_id=d.dept_id
        where a.id = #{id}
    </select>
        
    <insert id="insertSjFybpZb" parameterType="SjFybpZb" useGeneratedKeys="true" keyProperty="id">
        insert into sj_fybp_zb
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="fyrq != null">fyrq,</if>
            <if test="deptId != null">dept_id,</if>
            <if test="fyje != null">fyje,</if>
            <if test="zt != null">zt,</if>
            <if test="tjr != null">tjr,</if>
            <if test="tjrq != null">tjrq,</if>
            <if test="sprq != null">sprq,</if>
            <if test="spr != null">spr,</if>
            <if test="ysyje != null">ysyje,</if>
            <if test="spyj != null">spyj,</if>
            <if test="createBy != null">create_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateBy != null">update_by,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="remark != null">remark,</if>
            <if test="yl1 != null">yl1,</if>
            <if test="yl2 != null">yl2,</if>
            <if test="yl3 != null">yl3,</if>
            <if test="yl4 != null">yl4,</if>
            <if test="yl5 != null">yl5,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="fyrq != null">#{fyrq},</if>
            <if test="deptId != null">#{deptId},</if>
            <if test="fyje != null">#{fyje},</if>
            <if test="zt != null">#{zt},</if>
            <if test="tjr != null">#{tjr},</if>
            <if test="tjrq != null">#{tjrq},</if>
            <if test="sprq != null">#{sprq},</if>
            <if test="spr != null">#{spr},</if>
            <if test="ysyje != null">#{ysyje},</if>
            <if test="spyj != null">#{spyj},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateBy != null">#{updateBy},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="remark != null">#{remark},</if>
            <if test="yl1 != null">#{yl1},</if>
            <if test="yl2 != null">#{yl2},</if>
            <if test="yl3 != null">#{yl3},</if>
            <if test="yl4 != null">#{yl4},</if>
            <if test="yl5 != null">#{yl5},</if>
         </trim>
    </insert>

    <update id="updateSjFybpZb" parameterType="SjFybpZb">
        update sj_fybp_zb
        <trim prefix="SET" suffixOverrides=",">
            <if test="fyrq != null">fyrq = #{fyrq},</if>
            <if test="deptId != null">dept_id = #{deptId},</if>
            <if test="fyje != null">fyje = #{fyje},</if>
            <if test="zt != null">zt = #{zt},</if>
            <if test="tjr != null">tjr = #{tjr},</if>
            <if test="tjrq != null">tjrq = #{tjrq},</if>
            <if test="sprq != null">sprq = #{sprq},</if>
            <if test="spr != null">spr = #{spr},</if>
            <if test="ysyje != null">ysyje = #{ysyje},</if>
            <if test="spyj != null">spyj = #{spyj},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="remark != null">remark = #{remark},</if>
            <if test="yl1 != null">yl1 = #{yl1},</if>
            <if test="yl2 != null">yl2 = #{yl2},</if>
            <if test="yl3 != null">yl3 = #{yl3},</if>
            <if test="yl4 != null">yl4 = #{yl4},</if>
            <if test="yl5 != null">yl5 = #{yl5},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteSjFybpZbById" parameterType="Long">
        delete from sj_fybp_zb where id = #{id}
    </delete>

    <delete id="deleteSjFybpZbByIds" parameterType="String">
        delete from sj_fybp_zb where id in 
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>
    
    <delete id="deleteSjFybpByZbIds" parameterType="String">
        delete from sj_fybp where zb_id in 
        <foreach item="zbId" collection="array" open="(" separator="," close=")">
            #{zbId}
        </foreach>
    </delete>

    <delete id="deleteSjFybpByZbId" parameterType="Long">
        delete from sj_fybp where zb_id = #{zbId}
    </delete>

    <insert id="batchSjFybp">
        insert into sj_fybp( id, fymc, fyrq, dept_id, fylx, fyms, fyje ,zb_id) values
		<foreach item="item" index="index" collection="list" separator=",">
            ( #{item.id}, #{item.fymc}, #{item.fyrq}, #{item.deptId}, #{item.fylx}, #{item.fyms}, #{item.fyje},  #{item.zbId})
        </foreach>
    </insert>


    <select id="selectSjFybpZbList_main" parameterType="SjFybpZb" resultMap="SjFybpZbResult">
        select id,fyrq, a.dept_id, fyje, zt, tjr, tjrq, sprq, spr, ysyje, spyj, a.create_by, a.create_time, a.update_by, a.update_time, a.remark, yl1, yl2, yl3, yl4, yl5, d.dept_name, u.user_name spr_name
        from sj_fybp_zb a
        left join  sys_dept d on a.dept_id=d.dept_id
        left join sys_user u on a.spr=u.user_name
        <where>
            <if test="fyrq != null  and fyrq != ''"> and fyrq = #{fyrq}</if>
            <if test="deptId != null  and deptId != ''"> and (a.dept_id = #{deptId} or  find_in_set(#{deptId},d.ancestors))</if>
            <if test="fyje != null "> and fyje = #{fyje}</if>
            <if test="zt != null  and zt != ''"> and zt = #{zt}</if>
            <if test="tjr != null  and tjr != ''"> and tjr = #{tjr}</if>
            <if test="tjrq != null  and tjrq != ''"> and tjrq = #{tjrq}</if>
            <if test="sprq != null  and sprq != ''"> and sprq = #{sprq}</if>
            <if test="spr != null  and spr != ''"> and spr = #{spr}</if>
            <if test="ysyje != null "> and ysyje = #{ysyje}</if>
            <if test="spyj != null  and spyj != ''"> and spyj = #{spyj}</if>
            <if test="yl1 != null  and yl1 != ''"> and yl1 = #{yl1}</if>
            <if test="yl2 != null  and yl2 != ''"> and yl2 = #{yl2}</if>
            <if test="yl3 != null  and yl3 != ''"> and yl3 = #{yl3}</if>
            <if test="yl4 != null  and yl4 != ''"> and yl4 = #{yl4}</if>
            <if test="yl5 != null  and yl5 != ''"> and yl5 = #{yl5}</if>
            <if test="spcx != null  and spcx != '' and spcx=='1'.toString()"> and zt  in ('待审批','已批复')</if>
        </where>
        order by a.create_time desc
    </select>
</mapper>