<?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.SjGfjsXjbzMapper">

    <resultMap type="SjGfjsXjbz" id="SjGfjsXjbzResult">
        <result property="id"    column="id"    />
        <result property="bzId"    column="bz_id"    />
        <result property="deptId"    column="dept_id"    />
        <result property="parentDeptId"    column="parent_dept_id"    />
        <result property="nd"    column="nd"    />
        <result property="bzlx"    column="bzlx"    />
        <result property="bzmc"    column="bzmc"    />
        <result property="xj"    column="xj"    />
        <result property="xjms"    column="xjms"    />
        <result property="pm"    column="pm"    />
        <result property="zt"    column="zt"    />
        <result property="fdjb"    column="fdjb"    />
        <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="jcdwmc"    column="jcdwmc"    />
        <result property="zsdwmc"    column="zsdwmc"    />
    </resultMap>

    <sql id="selectSjGfjsXjbzVo">
        select a.id, a.bz_id, a.dept_id, a.parent_dept_id, a.nd, a.bzlx, a.bzmc, a.xj, a.xjms, a.pm, a.zt, a.fdjb ,
               a.create_by,
               a.create_time,
               a.update_by,
               a.update_time,
               a.remark,
               d.dept_name jcdwmc, d2.dept_name zsdwmc
        from sj_gfjs_xjbz a
         left join sys_dept d on a.dept_id = d.dept_id
         left join sys_dept d2 on d.parent_id = d2.dept_id
    </sql>

    <select id="selectSjGfjsXjbzList" parameterType="SjGfjsXjbz" resultMap="SjGfjsXjbzResult">
        <include refid="selectSjGfjsXjbzVo"/>
        <where>
            <if test="bzId != null "> and a.bz_id = #{bzId}</if>
            <if test="deptId != null "> and (a.dept_id = #{deptId} or  find_in_set(#{deptId},d.ancestors))</if>
            <if test="parentDeptId != null "> and a.parent_dept_id = #{parentDeptId}</if>
            <if test="nd != null  and nd != ''"> and a.nd = #{nd}</if>
            <if test="bzlx != null  and bzlx != ''"> and a.bzlx = #{bzlx}</if>
            <if test="bzmc != null  and bzmc != ''"> and a.bzmc = #{bzmc}</if>
            <if test="xj != null  and xj != ''"> and a.xj = #{xj}</if>
            <if test="xjms != null  and xjms != ''"> and a.xjms = #{xjms}</if>
            <if test="pm != null "> and a.pm = #{pm}</if>
            <if test="zt != null  and zt != ''"> and a.zt = #{zt}</if>
            <if test="fdjb != null  and fdjb != ''"> and a.fdjb = #{fdjb}</if>
        </where>
    </select>
    <select id="selectSjGfjsXjbzById" parameterType="Long" resultMap="SjGfjsXjbzResult">
        <include refid="selectSjGfjsXjbzVo"/>
        where a.id = #{id}
    </select>

    <insert id="insertSjGfjsXjbz" parameterType="SjGfjsXjbz" useGeneratedKeys="true" keyProperty="id">
        insert into sj_gfjs_xjbz
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="bzId != null">bz_id,</if>
            <if test="deptId != null">dept_id,</if>
            <if test="parentDeptId != null">parent_dept_id,</if>
            <if test="nd != null">nd,</if>
            <if test="bzlx != null">bzlx,</if>
            <if test="bzmc != null">bzmc,</if>
            <if test="xj != null">xj,</if>
            <if test="xjms != null">xjms,</if>
            <if test="pm != null">pm,</if>
            <if test="zt != null">zt,</if>
            <if test="fdjb != null">fdjb,</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>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="bzId != null">#{bzId},</if>
            <if test="deptId != null">#{deptId},</if>
            <if test="parentDeptId != null">#{parentDeptId},</if>
            <if test="nd != null">#{nd},</if>
            <if test="bzlx != null">#{bzlx},</if>
            <if test="bzmc != null">#{bzmc},</if>
            <if test="xj != null">#{xj},</if>
            <if test="xjms != null">#{xjms},</if>
            <if test="pm != null">#{pm},</if>
            <if test="zt != null">#{zt},</if>
            <if test="fdjb != null">#{fdjb},</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>
         </trim>
    </insert>
    <update id="updateSjGfjsXjbz" parameterType="SjGfjsXjbz">
        update sj_gfjs_xjbz
        <trim prefix="SET" suffixOverrides=",">
            <if test="bzId != null">bz_id = #{bzId},</if>
            <if test="deptId != null">dept_id = #{deptId},</if>
            <if test="parentDeptId != null">parent_dept_id = #{parentDeptId},</if>
            <if test="nd != null">nd = #{nd},</if>
            <if test="bzlx != null">bzlx = #{bzlx},</if>
            <if test="bzmc != null">bzmc = #{bzmc},</if>
            <if test="xj != null">xj = #{xj},</if>
            <if test="xjms != null">xjms = #{xjms},</if>
            <if test="pm != null">pm = #{pm},</if>
            <if test="zt != null">zt = #{zt},</if>
            <if test="fdjb != null">fdjb = #{fdjb},</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>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteSjGfjsXjbzById" parameterType="Long">
        delete from sj_gfjs_xjbz where id = #{id}
    </delete>
    <delete id="deleteSjGfjsXjbzByIds" parameterType="String">
        delete from sj_gfjs_xjbz where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>


    <insert id="batchSave">
        insert into sj_gfjs_xjbz(id, bz_id, dept_id, parent_dept_id, nd, bzlx, bzmc, xj, xjms, pm, zt, fdjb) values
        <foreach item="item" index="index" collection="list" separator=",">
            ( #{item.id},
            #{item.bzId},
            #{item.deptId},
            #{item.parentDeptId},
            #{item.nd},
            #{item.bzlx},
            #{item.bzmc},
            #{item.xj},
            #{item.xjms},
            #{item.pm},
            #{item.zt},
            #{item.fdjb})
        </foreach>
    </insert>

    <update id="pledit">
        <foreach collection="list" item="item" index="index" open="" close="" separator=";">
            update sj_gfjs_xjbz
            <trim prefix="SET" suffixOverrides=",">
                <if test="item.xj != null">xj = #{item.xj},</if>
                <if test="item.xjms != null">xjms = #{item.xjms},</if>
                <if test="item.pm != null">pm = #{item.pm},</if>
                <if test="item.zt != null">zt = #{item.zt},</if>
                <if test="item.fdjb != null">fdjb = #{item.fdjb},</if>
            </trim>
            where id = #{item.id}
        </foreach>
    </update>
    <!--批量修改 根据ids-->
    <update id="plxg" parameterType="SjGfjsXjbz">
        update sj_gfjs_xjbz
        <trim prefix="SET" suffixOverrides=",">
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="zt != null">zt = #{zt},</if>
        </trim>
        where FIND_IN_SET(id, #{ids})
    </update>



    <update id="updateBzxx_xjbz" parameterType="String">
        delete
        from sj_gfjs_xjbz
        where bz_id not in ( select id from sj_bzxx b )
        and nd =#{nd} ;

         INSERT INTO sj_gfjs_xjbz (bz_id, dept_id, nd, bzlx, bzmc, fdjb)
         select id, dept_id, #{nd} nd, bzlx, bzmc, rs
           from sj_bzxx b
          where b.id not in  ( select bz_id from sj_gfjs_xjbz where nd =#{nd} )
        ;
        update sj_gfjs_xjbz x
        left join sj_bzxx b on b.id = x.bz_id
        set x.bzlx = b.bzlx, x.bzmc = b.bzmc, x.fdjb = b.rs
        where x.nd =#{nd};
    </update>
</mapper>
