<?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.dcsoft.system.mapper.SysBranchMapper">

    <resultMap type="SysBranch" id="SysBranchResult">
        <result property="id"    column="id"    />
        <result property="name"    column="name"    />
        <result property="leader"    column="leader"    />
        <result property="phone"    column="phone"    />
        <result property="parentId"    column="parent_id"    />
        <result property="ancestors"    column="ancestors"    />
        <result property="spaceId"    column="space_id"    />
        <result property="deptId"    column="dept_id"    />
        <result property="ruleId"    column="rule_id"    />
        <result property="remark"    column="remark"    />
        <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="ruleId"    column="rule_id"    />
        <result property="ladderRuleId"    column="ladder_rule_id"    />
    </resultMap>

    <sql id="selectSysBranchVo">
        select b.id, b.name, b.leader, b.phone, b.parent_id, b.ancestors, b.space_id,b.dept_id, b.remark, b.create_by,
               b.create_time, b.update_by, b.update_time, b.rule_id, b.ladder_rule_id, b.is_examine isExamine
        from sys_branch b
        left join sys_dept d on b.dept_id = d.dept_id
    </sql>

    <select id="selectSysBranchList" parameterType="SysBranch" resultMap="SysBranchResult">
        <include refid="selectSysBranchVo"/>
        <where>
            <if test="id != null  and id != ''"> and b.id = #{id}</if>
            <if test="name != null  and name != ''"> and b.name like concat('%', #{name}, '%')</if>
            <if test="leader != null  and leader != ''"> and b.leader = #{leader}</if>
            <if test="phone != null  and phone != ''"> and b.phone = #{phone}</if>
            <if test="parentId != null "> and b.parent_id = #{parentId}</if>
            <if test="ancestors != null  and ancestors != ''"> and b.ancestors = #{ancestors}</if>
            <if test="spaceId != null "> and b.space_id = #{spaceId}</if>
            <!-- 数据范围过滤 -->
            ${params.dataScope}
        </where>
        order by  b.id asc
    </select>

    <select id="selectSysBranchById" parameterType="String" resultMap="SysBranchResult">
        <include refid="selectSysBranchVo"/>
        where id = #{id}
    </select>

    <insert id="insertSysBranch" parameterType="SysBranch">
        insert into sys_branch
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">id,</if>
            <if test="name != null and name != ''">name,</if>
            <if test="leader != null">leader,</if>
            <if test="phone != null">phone,</if>
            <if test="parentId != null">parent_id,</if>
            <if test="ancestors != null">ancestors,</if>
            <if test="spaceId != null">space_id,</if>
            <if test="remark != null">remark,</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="deptId != null">dept_id,</if>
            <if test="ruleId != null">rule_id,</if>
            <if test="ladderRuleId != null">ladder_rule_id,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null">#{id},</if>
            <if test="name != null and name != ''">#{name},</if>
            <if test="leader != null">#{leader},</if>
            <if test="phone != null">#{phone},</if>
            <if test="parentId != null">#{parentId},</if>
            <if test="ancestors != null">#{ancestors},</if>
            <if test="spaceId != null">#{spaceId},</if>
            <if test="remark != null">#{remark},</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="deptId != null">#{deptId},</if>
            <if test="ruleId != null">#{ruleId},</if>
            <if test="ladderRuleId != null">#{ladderRuleId},</if>
        </trim>
    </insert>
    <insert id="saveBranchTree">
        insert into sys_branch (id, name, parent_id, level, ancestors)
        values
        <foreach collection="list" item="i" index="index" separator=",">
            (
            #{i.deptcode},
            #{i.name},
            #{i.parentcode},
            #{i.level},
            #{i.ancestors}
            )
        </foreach>
        ON DUPLICATE KEY UPDATE
        name = values(name),
        parent_id = values(parent_id),
        level = values(level),
        ancestors = values(ancestors);
    </insert>
    <insert id="saveUserInfo">
        insert into sys_people (sex, branch_id, position, name, phone, gh, guid, del_flag)
        VALUES
        <foreach collection="list" item="i" index="index" separator=",">
            (
            #{i.sex},
            #{i.ssbm},
            #{i.posts},
            #{i.lastname},
            #{i.telphone},
            #{i.workcode},
            REPLACE(uuid(), '-', ''),
            #{i.delFlag}
            )
        </foreach>
        ON DUPLICATE KEY UPDATE
        sex = values(sex),
        branch_id = values(branch_id),
        position = values(position),
        name = values(name),
        phone = values(phone),
        del_flag = values(del_flag)
    </insert>
    <insert id="saveStation">
        insert into jj_station(id, postid, deptcode, postname, postcode, begindate)
        VALUES
        <foreach collection="list" item="i" index="index" separator=",">
            (
            REPLACE(uuid(), '-', ''),
            #{i.postid},
            #{i.deptcode},
            #{i.postname},
            #{i.postcode},
            #{i.begindate}
            )
        </foreach>
    </insert>

    <update id="updateSysBranch" parameterType="SysBranch">
        update sys_branch
        <trim prefix="SET" suffixOverrides=",">
            <if test="name != null and name != ''">name = #{name},</if>
            <if test="leader != null">leader = #{leader},</if>
            <if test="phone != null">phone = #{phone},</if>
            <if test="parentId != null">parent_id = #{parentId},</if>
            <if test="ancestors != null">ancestors = #{ancestors},</if>
            <if test="spaceId != null">space_id = #{spaceId},</if>
            <if test="remark != null">remark = #{remark},</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="deptId != null">dept_id = #{deptId},</if>
            <if test="ruleId != null">rule_id = #{ruleId},</if>
            <if test="ladderRuleId != null">ladder_rule_id = #{ladderRuleId},</if>
            <if test="isExamine != null">is_examine = #{isExamine},</if>
        </trim>
        where id = #{id}
    </update>
    <update id="updateBranchTree">
        update sys_branch
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="ancestors =case" suffix="end,">
                <foreach collection="list" item="item" index="index">
                    <if test="null != item.ancestors">
                        WHEN ID=#{item.id} THEN #{item.ancestors}
                    </if>
                </foreach>
            </trim>
        </trim>
        WHERE
        ID IN
        <foreach collection="list" index="index" item="item" separator="," open="(" close=")">
            #{item.id}
        </foreach>
    </update>

    <delete id="deleteSysBranchById" parameterType="Long">
        delete from sys_branch where id = #{id}
    </delete>

    <delete id="deleteSysBranchByBranchId" parameterType="String">
        delete from sys_branch where id = #{id}
    </delete>

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

    <select id="selectSysBranchByDeptId" parameterType="Long" resultMap="SysBranchResult">
        <include refid="selectSysBranchVo"/>
        where b.dept_id = #{deptId} limit 1
    </select>

    <select id="queryBranchTree" resultMap="SysBranchResult">
        select b.id, b.name, b.leader, b.phone, b.parent_id, b.ancestors, b.space_id,b.dept_id, b.remark, b.create_by,
               b.create_time, b.update_by, b.update_time,b.rule_id
        from sys_branch b
                 left join sys_dept d on b.dept_id = d.dept_id
        where b.level in('1', '2')
    </select>
    <select id="selectBranchTree" resultType="com.dcsoft.system.domain.vo.JjDivisionVo">
        select b.id, b.parent_id parentId from sys_branch b
        LEFT JOIN sys_dept d ON b.dept_id = d.dept_id
        <where>
        <!-- 数据范围过滤 -->
        ${sysPeople.params.dataScope}
        </where>
    </select>

    <select id="selectBranchTreeOther" resultType="com.dcsoft.system.domain.vo.JjDivisionVo">
        select b.id, b.parent_id parentId from sys_branch b
        LEFT JOIN sys_dept d ON b.dept_id = d.dept_id
        <where>
            <!-- 数据范围过滤 -->
            ${sysPeople.params.dataScope}
        </where>
    </select>
    <select id="queryPropertyDept" resultType="com.dcsoft.system.domain.SysBranch">
        select id, name
        from sys_branch
        <where>
            <if test="parentId != null and parentId != ''">
                parent_id = #{parentId}
            </if>
        </where>
    </select>
    <select id="queryJuniorBranch" resultType="com.dcsoft.system.domain.SysBranch">
        WITH RECURSIVE subdepartments AS (
            SELECT id, name, parent_id
            FROM sys_branch
            WHERE parent_id = #{branchId}
            UNION ALL
            SELECT d.id, d.name, d.parent_id
            FROM sys_branch d
                     JOIN subdepartments sd ON d.parent_id = sd.id
        )
        SELECT * FROM subdepartments
    </select>

</mapper>
