<?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.yunfeiyun.agmp.common.web.system.mapper.SysDeptMapper">

	<resultMap type="SysDept" id="SysDeptResult">
		<id     property="deptId"     column="deptId"     />
		<result property="deptParentid"   column="deptParentid"   />
		<result property="deptLevelcode"  column="deptLevelcode"   />
		<result property="deptName"   column="deptName"   />
		<result property="deptSeq"   column="deptSeq"   />
		<result property="deptManager"     column="deptManager"      />
		<result property="deptPhone"      column="deptPhone"       />
		<result property="deptEmail"      column="deptEmail"       />
		<result property="deptDelstatus"    column="deptDelstatus"    />
		<result property="parentName" column="parent_name" />
		<result property="deptCreator"   column="deptCreator"   />
		<result property="deptCreateddate" column="deptCreateddate" />
		<result property="deptModifier"   column="deptModifier"   />
		<result property="deptModifieddate" column="deptModifieddate" />
	</resultMap>
	
	<sql id="selectDeptVo">
        select d.deptId, d.deptParentid, d.deptLevelcode, d.deptName, d.deptSeq, d.deptManager, d.deptPhone, d.deptEmail, d.deptDelstatus, d.deptCreator, d.deptCreateddate
        from SysDept d
    </sql>
    
	<select id="selectDeptList" parameterType="SysDept" resultMap="SysDeptResult">
        <include refid="selectDeptVo"/>
        where d.deptDelstatus = '0'
		<if test="deptId != null and deptId != '' and deptId != '0' ">
			AND deptId = #{deptId}
		</if>
        <if test="deptParentid != null and deptParentid != ''">
			AND deptParentid = #{deptParentid}
		</if>
		<if test="deptName != null and deptName != ''">
			AND deptName like concat('%', #{deptName}, '%')
		</if>
		<if test="deptDelstatus != null and deptDelstatus != ''">
			AND deptDelstatus = #{deptDelstatus}
		</if>
		<!-- 数据范围过滤 -->
		${params.dataScope}
		order by d.deptParentid, d.deptSeq
    </select>
    
    <select id="selectDeptListByRoleId" resultType="String">
		select d.deptId
		from SysDept d
            left join SysRoledept rd on d.deptId = rd.deptId
        where rd.roleId = #{roleId}
            <if test="deptCheckStrictly">
              and d.deptId not in (select d.deptParentid from SysDept d inner join SysRoledept rd on d.deptId = rd.deptId and rd.roleId = #{roleId})
            </if>
		order by d.deptParentid, d.deptSeq
	</select>
    
    <select id="selectDeptById" parameterType="String" resultMap="SysDeptResult">
		select d.deptId, d.deptParentid, d.deptLevelcode, d.deptName, d.deptSeq, d.deptManager, d.deptPhone, d.deptEmail, d.deptDelstatus,
			(select deptName from SysDept where deptId = d.deptParentid) parentName
		from SysDept d
		where d.deptId = #{deptId}
	</select>
    
    <select id="checkDeptExistUser" parameterType="String" resultType="int">
		select count(1) from SysUser where deptId = #{deptId} and userDelstatus = '0'
	</select>
	
	<select id="hasChildByDeptId" parameterType="String" resultType="int">
		select count(1) from SysDept
		where deptDelstatus = '0' and deptParentid = #{deptId} limit 1
	</select>
	
	<select id="selectChildrenDeptById" parameterType="String" resultMap="SysDeptResult">
		select * from SysDept where find_in_set(#{deptId}, deptLevelcode)
	</select>
	
	<select id="selectNormalChildrenDeptById" parameterType="String" resultType="int">
		select count(*) from SysDept where deptDelstatus = '0' and find_in_set(#{deptId}, deptLevelcode)
	</select>
	
	<select id="checkDeptNameUnique" resultMap="SysDeptResult">
	    <include refid="selectDeptVo"/>
		where deptName=#{deptName} and deptParentid = #{deptParentid} and deptDelstatus = '0' limit 1
	</select>
    
    <insert id="insertDept" parameterType="SysDept">
 		insert into SysDept(
 			<if test="deptId != null and deptId != ''">deptId,</if>
 			<if test="deptParentid != null and deptParentid != ''">deptParentid,</if>
 			<if test="deptName != null and deptName != ''">deptName,</if>
 			<if test="deptLevelcode != null and deptLevelcode != ''">deptLevelcode,</if>
 			<if test="deptSeq != null">deptSeq,</if>
 			<if test="deptManager != null and deptManager != ''">deptManager,</if>
 			<if test="deptPhone != null and deptPhone != ''">deptPhone,</if>
 			<if test="deptEmail != null and deptEmail != ''">deptEmail,</if>
 			<if test="deptDelstatus != null">deptDelstatus,</if>
 			<if test="deptCreator != null and deptCreator != ''">deptCreator,</if>
		deptCreateddate
 		)values(
 			<if test="deptId != null and deptId != ''">#{deptId},</if>
 			<if test="deptParentid != null and deptParentid != ''">#{deptParentid},</if>
 			<if test="deptName != null and deptName != ''">#{deptName},</if>
 			<if test="deptLevelcode != null and deptLevelcode != ''">#{deptLevelcode},</if>
 			<if test="deptSeq != null">#{deptSeq},</if>
 			<if test="deptManager != null and deptManager != ''">#{deptManager},</if>
 			<if test="deptPhone != null and deptPhone != ''">#{deptPhone},</if>
 			<if test="deptEmail != null and deptEmail != ''">#{deptEmail},</if>
 			<if test="deptDelstatus != null">#{deptDelstatus},</if>
 			<if test="deptCreator != null and deptCreator != ''">#{deptCreator},</if>
		date_format(sysdate(),'%Y-%m-%d %H:%i:%s')
 		)
	</insert>
	
	<update id="updateDept" parameterType="SysDept">
 		update SysDept
 		<set>
 			<if test="deptParentid != null and deptParentid != ''">deptParentid = #{deptParentid},</if>
 			<if test="deptName != null and deptName != ''">deptName = #{deptName},</if>
 			<if test="deptLevelcode != null and deptLevelcode != ''">deptLevelcode = #{deptLevelcode},</if>
 			<if test="deptSeq != null">deptSeq = #{deptSeq},</if>
 			<if test="deptManager != null">deptManager = #{deptManager},</if>
 			<if test="deptPhone != null">deptPhone = #{deptPhone},</if>
 			<if test="deptEmail != null">deptEmail = #{deptEmail},</if>
 			<if test="deptDelstatus != null and deptDelstatus != ''">deptDelstatus = #{deptDelstatus},</if>
 			<if test="deptModifier != null and deptModifier != ''">deptModifier = #{deptModifier},</if>
			deptModifieddate = date_format(sysdate(),'%Y-%m-%d %H:%i:%s')
 		</set>
 		where deptId = #{deptId}
	</update>
	
	<update id="updateDeptChildren" parameterType="java.util.List">
	    update SysDept set deptLevelcode =
	    <foreach collection="depts" item="item" index="index"
	        separator=" " open="case deptId" close="end">
	        when #{item.deptId} then #{item.deptLevelcode}
	    </foreach>
	    where deptId in
	    <foreach collection="depts" item="item" index="index"
	        separator="," open="(" close=")">
	        #{item.deptId}
	    </foreach>
	</update>
	 
	<update id="updateDeptStatusNormal" parameterType="String">
 	    update SysDept set deptDelstatus = '0' where deptId in
 	    <foreach collection="array" item="deptId" open="(" separator="," close=")">
        	#{deptId}
        </foreach>
	</update>
	
	<delete id="deleteDeptById" parameterType="String">
		update SysDept set deptDelstatus = '2' where deptId = #{deptId}
	</delete>

</mapper> 