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

    <resultMap type="SjBfzwh" id="SjBfzwhResult">
        <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="SjBfzbfdx" id="SjBfzbfdxResult">
        <result property="id"    column="id"    />
        <result property="deptId"    column="dept_id"    />
        <result property="deptName"    column="dept_name"    />

    </resultMap>

    <sql id="selectSjBfzwhVo">
        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_bfzwh a
		left join sys_user s on a.lxr = s.user_id
    </sql>

    <select id="selectSjBfzwhList" parameterType="SjBfzwh" resultMap="SjBfzwhResult">
        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_bfzwh a
        left join sys_user s on a.lxr = s.user_id
        left join  sj_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
    </select>

    <select id="selectSjBfzwhById" parameterType="Long" resultMap="SjBfzwhResult">
        <include refid="selectSjBfzwhVo"/>
        where id = #{id}
    </select>

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

    <insert id="insertSjBfzwh" parameterType="SjBfzwh" useGeneratedKeys="true" keyProperty="id">
        insert into sj_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="updateSjBfzwh" parameterType="SjBfzwh">
        update sj_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="deleteSjBfzwhById" parameterType="Long">
        delete from sj_bfzwh where id = #{id}
    </delete>

    <delete id="deleteSjBfzwhByIds" parameterType="String">
        delete from sj_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_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_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_bfzbfdx where id=#{id}
	</delete>

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


    <select id="selectBfdxlist" parameterType="SjBfzbfdx" resultMap="SjBfzbfdxResult">
        select bfdx.id, bfdx.dept_id,  CONCAT(p.dept_name,'/',c.dept_name) dept_name, c.dept_lx from sj_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>
</mapper>
