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

    <resultMap type="SjGfjsBfzwh" id="SjGfjsBfzwhResult">
        <result property="id"    column="id"    />
        <result property="bfzmc"    column="bfzmc"    />
        <result property="lxr"    column="lxr"    />
        <result property="nd"    column="nd"    />
        <result property="lrr"    column="lrr"    />
        <result property="lrsj"    column="lrsj"    />
        <result property="xgr"    column="xgr"    />
        <result property="xgsj"    column="xgsj"    />
        <result property="deptLx"    column="dept_lx"    />
        <result property="bz"    column="bz"    />
        <result property="deptNames"    column="deptNames"    />
    </resultMap>

    <resultMap type="SjGfjsBfzbfdx" id="SjGfjsBfzbfdxResult">
        <result property="id"    column="id"    />
        <result property="deptId"    column="dept_id"    />
        <result property="deptName"    column="dept_name"    />

    </resultMap>

    <sql id="selectSjGfjsBfzwhVo">
        select a.id, a.bfzmc, a.lxr, a.nd, a.lrr, s.nick_name lxrname, a.lrsj, a.xgr, a.xgsj, a.bz
        from sj_gfjs_bfzwh a
		left join sys_user s on a.lxr = s.user_id
    </sql>

    <select id="selectSjGfjsBfzwhList" parameterType="SjGfjsBfzwh" resultMap="SjGfjsBfzwhResult">
        select a.id, a.bfzmc, a.lxr, a.nd, a.lrr, s.nick_name lxrname, a.lrsj, a.xgr, a.xgsj, a.bz,
        GROUP_CONCAT(CONCAT(p.dept_name,'/',c.dept_name))  deptNames
        from sj_gfjs_bfzwh a
        left join sys_user s on a.lxr = s.user_id
        left join  sj_gfjs_bfzbfdx bfdx on bfdx.id = a.id
        LEFT JOIN  sys_dept c on bfdx.dept_id = c.dept_id
        LEFT JOIN  sys_dept p on c.parent_id = p.dept_id
        <where>
            <if test="bfzmc != null  and bfzmc != ''"> and a.bfzmc = #{bfzmc}</if>
            <if test="lxr != null  and lxr != ''"> and a.lxr = #{lxr}</if>
            <if test="nd != null  and nd != ''"> and a.nd = #{nd}</if>
            <if test="lrr != null  and lrr != ''"> and a.lrr = #{lrr}</if>
            <if test="lrsj != null "> and a.lrsj = #{lrsj}</if>
            <if test="xgr != null  and xgr != ''"> and a.xgr = #{xgr}</if>
            <if test="xgsj != null "> and a.xgsj = #{xgsj}</if>
            <if test="bz != null  and bz != ''"> and a.bz = #{bz}</if>

        </where>
        group by a.id, a.bfzmc, a.lxr, a.nd, a.lrr, s.nick_name, a.lrsj, a.xgr, a.xgsj, a.bz,p.dept_name,c.dept_name
    </select>

    <select id="selectSjGfjsBfzwhById" parameterType="Long" resultMap="SjGfjsBfzwhResult">
        <include refid="selectSjGfjsBfzwhVo"/>
        where id = #{id}
    </select>

    <select id="selectbfz" parameterType="String" resultMap="SjGfjsBfzwhResult">
        <include refid="selectSjGfjsBfzwhVo"/>
        where lxr = #{lxr}
    </select>

    <insert id="insertSjGfjsBfzwh" parameterType="SjGfjsBfzwh" useGeneratedKeys="true" keyProperty="id">
        insert into sj_gfjs_bfzwh
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="bfzmc != null">bfzmc,</if>
            <if test="lxr != null">lxr,</if>
            <if test="nd != null">nd,</if>
            <if test="lrr != null">lrr,</if>
            <if test="lrsj != null">lrsj,</if>
            <if test="xgr != null">xgr,</if>
            <if test="xgsj != null">xgsj,</if>
            <if test="bz != null">bz,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="bfzmc != null">#{bfzmc},</if>
            <if test="lxr != null">#{lxr},</if>
            <if test="nd != null">#{nd},</if>
            <if test="lrr != null">#{lrr},</if>
            <if test="lrsj != null">#{lrsj},</if>
            <if test="xgr != null">#{xgr},</if>
            <if test="xgsj != null">#{xgsj},</if>
            <if test="bz != null">#{bz},</if>
         </trim>
    </insert>

    <update id="updateSjGfjsBfzwh" parameterType="SjGfjsBfzwh">
        update sj_gfjs_bfzwh
        <trim prefix="SET" suffixOverrides=",">
            <if test="bfzmc != null">bfzmc = #{bfzmc},</if>
            <if test="lxr != null">lxr = #{lxr},</if>
            <if test="nd != null">nd = #{nd},</if>
            <if test="lrr != null">lrr = #{lrr},</if>
            <if test="lrsj != null">lrsj = #{lrsj},</if>
            <if test="xgr != null">xgr = #{xgr},</if>
            <if test="xgsj != null">xgsj = #{xgsj},</if>
            <if test="bz != null">bz = #{bz},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteSjGfjsBfzwhById" parameterType="Long">
        delete from sj_gfjs_bfzwh where id = #{id}
    </delete>

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

    <select id="selectBfdxListByBfz" resultType="Long">
       select d.dept_id from sys_dept d
        left join sj_gfjs_bfzbfdx m
            on m.dept_id = d.dept_id
            where m.id = #{id}
        order by d.parent_id, d.order_num
    </select>

    <insert id="batchBfzBfdx">
        insert into sj_gfjs_bfzbfdx(id, dept_id) values
        <foreach item="item" index="index" collection="list" separator=",">
            (#{item.id},#{item.deptId})
        </foreach>
    </insert>

    <delete id="deleteBfzBfdxById" parameterType="Long">
		delete from sj_gfjs_bfzbfdx where id=#{id}
	</delete>

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


    <select id="selectBfdxlist" parameterType="SjGfjsBfzbfdx" resultMap="SjGfjsBfzbfdxResult">
        select bfdx.id, bfdx.dept_id,  CONCAT(p.dept_name,'/',c.dept_name) dept_name, c.dept_lx
        from sj_gfjs_bfzbfdx bfdx
        LEFT JOIN  sys_dept c on bfdx.dept_id = c.dept_id
        LEFT JOIN  sys_dept p on c.parent_id = p.dept_id
        <where>
            <if test="id != null "> and id = #{id}</if>
            <if test="deptId != null "> and dept_id = #{deptId}</if>
        </where>
    </select>

    <resultMap type="SysDept" id="SysDeptResult">
        <id     property="deptId"     column="dept_id"     />
        <result property="parentId"   column="parent_id"   />
        <result property="ancestors"  column="ancestors"   />
        <result property="deptName"   column="dept_name"   />
        <result property="orderNum"   column="order_num"   />
        <result property="leader"     column="leader"      />
        <result property="phone"      column="phone"       />
        <result property="email"      column="email"       />
        <result property="status"     column="status"      />
        <result property="delFlag"    column="del_flag"    />
        <result property="parentName" column="parent_name" />
        <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="dwjb" column="dwjb" />
        <result property="deptLx"    column="dept_lx"    />
        <result property="deptDfl"    column="dept_dfl"    />
        <result property="deptYwfl"    column="dept_ywfl"    />
    </resultMap>
    <select id="selectBfdxTree_list" parameterType="SjGfjsBfzbfdx" resultMap="SysDeptResult">
        select d.dept_id, d.parent_id,
        d.ancestors, d.dept_name,
        d.order_num, d.leader, d.phone,
        d.email, d.status, d.del_flag, d.create_by, d.create_time,d.dwjb, d.dept_lx, d.dept_dfl, d.dept_ywfl
        from sys_dept d
        where  d.dept_id in  (
        select dept_id from  sj_gfjs_bfzbfdx
        <where>
            <if test="id != null "> and id = #{id}</if>
            <if test="deptId != null "> and dept_id = #{deptId}</if>
        </where>
        )
    </select>


</mapper>
