<?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.vctgo.system.mapper.SysDeptMapper">

	<resultMap type="com.vctgo.system.api.domain.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="deptType"   column="dept_type"   />
		<result property="orderNum"   column="order_num"   />
		<result property="leaderId"     column="leader_id"      />
		<result property="leader"     column="leader"      />
		<result property="phone"      column="phone"       />
		<result property="email"      column="email"       />
		<result property="status"     column="status"      />
		<result property="remark"     column="remark"      />
		<result property="delFlag"    column="del_flag"    />
		<result property="parentName" column="parent_name" />
		<result property="createById"   column="create_by_id"   />
		<result property="createBy"   column="create_by"   />
		<result property="createTime" column="create_time" />
		<result property="updateById"   column="update_by_id"   />
		<result property="updateBy"   column="update_by"   />
		<result property="updateTime" column="update_time" />
		<result property="tenantId"   column="tenant_id" />
	</resultMap>

	<resultMap id="selectSysUser" type="com.vctgo.system.api.domain.SysDept" extends="SysDeptResult">
		<collection property="sysUserList"
					ofType="com.vctgo.system.api.domain.SysUser"
					javaType="java.util.List"
					column="{deptId = dept_id, nick_name=nick_name, phonenumber = phonenumber,user_id = user_id}"
					select="selectUserList"
					fetchType="eager"/>
		<collection property="children"
					ofType="com.vctgo.system.api.domain.SysDept"
					javaType="java.util.List"
					column="{parentId = dept_id, nick_name=nick_name, phonenumber = phonenumber,user_id = user_id}"
					select="selectDeptListByParentId"
					fetchType="eager"/>
	</resultMap>

	<resultMap type="com.vctgo.system.api.domain.SysPlatformUser" id="SimpleSysUserResult">
		<id     property="userId"       column="user_id"      />
		<result property="deptId"       column="dept_id"      />
		<result property="nickName"     column="nick_name"    />
		<result property="phonenumber"     column="phonenumber"    />
	</resultMap>

	<select id="selectUserList" resultMap="SimpleSysUserResult">
		select user_id,dept_id,nick_name,phonenumber from sys_user
		where del_flag='0'
		and user_status='0'
		and account_status = '0'
		and dept_id = #{deptId}
		<if test="user_id != null and user_id != ''">
			and user_id != #{user_id}
		</if>
		<if test="phonenumber != null and phonenumber != ''">
			and phonenumber = #{phonenumber}
		</if>
		<if test="nick_name != null and nick_name != ''">
			and nick_name like concat('%', #{nick_name}, '%')
		</if>
	</select>

	<select id="selectDeptListByParentId"  resultMap="selectSysUser">
		select d.dept_id, d.parent_id, d.ancestors, d.dept_name, d.dept_type, d.order_num,
			   d.leader_id, d.leader, d.phone, d.email, d.status, d.remark, d.del_flag,
			   d.create_by_id, d.create_by, d.create_time, d.tenant_id,
			   #{nick_name} nick_name, #{phoneNumber} phonenumber, #{user_id} user_id
		from sys_dept d
		where d.del_flag = '0' and d.parent_id =#{parentId} order by d.parent_id, d.order_num
	</select>

	<select id="selectUserByDept" resultMap="selectSysUser">
		select d.*, #{nickName} nick_name, #{phoneNumber} phonenumber, #{userId} user_id from sys_dept d where d.del_flag ='0' and d.parent_id = 0 and d.tenant_id = #{tenantId}
	</select>

	<sql id="selectDeptVo">
        select d.dept_id, d.parent_id, d.ancestors, d.dept_name, d.dept_type, d.order_num,
               d.leader_id, d.leader, d.phone, d.email, d.status, d.remark, d.del_flag,
               d.create_by_id, d.create_by, d.create_time, d.tenant_id
        from sys_dept d
    </sql>

	<sql id="selectAssociateTenantDeptVo">
		select t.id dept_id, IFNULL(re.id,0) parent_id, 0 ancestors, t.tenant_name dept_name,
			   case t.tenant_type when '1' then '1' when '2' then '2' when '3' then '2' else null end dept_type, null order_num, null leader_id, null leader,t.user_phone phone,
			   t.user_email email, t.status, t.remark, t.del_flag, t.create_by, t.create_time, t.id tenant_id
		from sys_tenant t
				 left join (select garden_id, id from sys_tenant
				            where tenant_type=1 and garden_id is not null and branch_id is null) re on re.garden_id = t.garden_id
			and t.tenant_type = 2
		where t.del_flag = '0'
		<if test="params.tenantType == '1'.toString()">
		  and t.id IN (SELECT id FROM sys_tenant WHERE garden_id =
		                 (SELECT garden_id FROM sys_tenant WHERE id = #{tenantId}) order by t.tenant_type)
		</if>
		<if test="params.tenantType == '2'.toString() || params.tenantType == '3'.toString()">
		  and t.id = #{tenantId}
		</if>
		<if test="deptName != null and deptName != ''">
			and t.tenant_name like concat('%', #{deptName}, '%')
		</if>
		<if test="deptType == '2'.toString()">
            and t.tenant_type in ('2','3')
		</if>
		<if test="deptType == '1'.toString() || deptType == '3'.toString()">
			and t.tenant_type = #{deptType}
		</if>
		<if test="createBy != null  and createBy != ''">
			and t.create_by = #{createBy}
		</if>
		<if test="params.beginCreateTime != null and params.beginCreateTime != ''">
			and date_format(create_time,'%y%m%d') &gt;= date_format(#{params.beginCreateTime},'%y%m%d')
		</if>
		<if test="params.endCreateTime != null and params.endCreateTime != ''">
			and date_format(create_time,'%y%m%d') &lt;= date_format(#{params.endCreateTime},'%y%m%d')
		</if>
		union
		SELECT d.dept_id, IF(d.parent_id=0,tenant_id,parent_id) parent_id, d.ancestors, d.dept_name, d.dept_type, d.order_num,
			   d.leader_id, d.leader, d.phone, d.email, d.status, d.del_flag, d.create_by, d.create_time,
			   d.tenant_id FROM sys_dept d
		WHERE d.del_flag = '0' AND tenant_id IN (
			SELECT id FROM sys_tenant WHERE garden_id = (SELECT garden_id FROM sys_tenant WHERE id = #{tenantId})
			                          order by d.tenant_id, d.create_time desc)
		<if test="deptName != null and deptName != ''">
			and d.dept_name like concat('%', #{deptName}, '%')
		</if>
		<if test="deptType != null and deptType != ''">
			and d.dept_type = #{deptType}
		</if>
		<if test="createBy != null  and createBy != ''">
		 and d.create_by like concat('%', #{createBy}, '%')
		</if>
		<if test="params.beginCreateTime != null and params.beginCreateTime != ''">
			and date_format(create_time,'%y%m%d') &gt;= date_format(#{params.beginCreateTime},'%y%m%d')
		</if>
		<if test="params.endCreateTime != null and params.endCreateTime != ''">
			and date_format(create_time,'%y%m%d') &lt;= date_format(#{params.endCreateTime},'%y%m%d')
		</if>
		order by tenant_id, dept_type, create_time desc
	</sql>

	<select id="selectAssociateTenantDeptList"  resultMap="SysDeptResult">
        <include refid="selectAssociateTenantDeptVo"/>
<!--        where d.del_flag = '0'
		<if test="params.isGardenTenant == '1'.toString()">
			and tenant_id in (select id from sys_tenant where garden_id =
            (select garden_id from sys_tenant where id = #{tenantId}) and del_flag = 0)
		</if>
		<if test="deptId != null and deptId != 0">
			AND dept_id = #{deptId}
		</if>
        <if test="parentId != null and parentId != 0">
			AND parent_id = #{parentId}
		</if>
		<if test="deptName != null and deptName != ''">
			AND dept_name like concat('%', #{deptName}, '%')
		</if>
		<if test="status != null and status != ''">
			AND status = #{status}
		</if>-->
    </select>

	<select id="selectDeptList"  resultMap="SysDeptResult">
		select d.dept_id, d.parent_id, d.ancestors, d.dept_name, d.dept_type, d.order_num,
		       d.leader_id, u3.nick_name leader, d.phone, d.email, d.status, d.remark, d.del_flag,
		       d.create_by_id, u.nick_name create_by, d.create_time, d.tenant_id,
			   IFNULL(re.user_count,0) userCount
		from sys_dept d
		left join sys_user u on d.create_by_id = u.user_id
		left join (select u2.dept_id,count(u2.user_id) user_count from sys_user u2
		left join sys_dept d2 on d2.dept_id = u2.dept_id
		where u2.del_flag = '0' and d2.del_flag = '0' group by u2.dept_id) re on re.dept_id = d.dept_id
		left join sys_user u3 ON d.leader_id = u3.user_id
		where d.del_flag = '0'
		<if test="params.isGardenTenant == '1'.toString()">
			and d.tenant_id in (select id from sys_tenant where garden_id =
			(select garden_id from sys_tenant where id = #{tenantId}))
		</if>
		<if test="params.isSingleTenant != null and params.isSingleTenant != ''">
			and d.tenant_id = #{params.isSingleTenant}
		</if>
		<if test="deptName != null and deptName != ''">
			and d.dept_name like concat('%', #{deptName}, '%')
		</if>
		<if test="deptType != null and deptType != ''">
			and d.dept_type = #{deptType}
		</if>
		<if test="createBy != null  and createBy != ''">
			and u.nick_name like concat('%', #{createBy}, '%')
		</if>
		<if test="params.beginCreateTime != null and params.beginCreateTime != ''">
			and date_format(d.create_time,'%y%m%d') &gt;= date_format(#{params.beginCreateTime},'%y%m%d')
		</if>
		<if test="params.endCreateTime != null and params.endCreateTime != ''">
			and date_format(d.create_time,'%y%m%d') &lt;= date_format(#{params.endCreateTime},'%y%m%d')
		</if>
		<if test="params.gardenTenantId != null and params.gardenTenantId != ''">
			and d.tenant_id = #{params.gardenTenantId}
		</if>
		<if test="params.excludeDept != null and params.excludeDept != ''">
			and d.dept_type != '3'
		</if>
		<!-- 数据范围过滤 -->
		${params.dataScope}
		order by d.tenant_id, d.dept_type, d.create_time desc
	</select>

	<select id="selectDeptTreeData"  resultMap="SysDeptResult">
		<include refid="selectDeptVo"/>
		where d.del_flag = '0'
		<if test="params.isGardenTenant == '1'.toString()">
			and d.tenant_id in (select id from sys_tenant where garden_id =
			(select garden_id from sys_tenant where id = #{tenantId}))
		</if>
		<if test="params.isSingleTenant != null and params.isSingleTenant != ''">
			and d.tenant_id = #{params.isSingleTenant}
		</if>
		<if test="deptName != null and deptName != ''">
			and d.dept_name like concat('%', #{deptName}, '%')
		</if>
		<if test="deptType != null and deptType != ''">
			and d.dept_type = #{deptType}
		</if>
		<if test="createBy != null  and createBy != ''">
			and d.create_by like concat('%', #{createBy}, '%')
		</if>
		<if test="params.beginCreateTime != null and params.beginCreateTime != ''">
			and date_format(create_time,'%y%m%d') &gt;= date_format(#{params.beginCreateTime},'%y%m%d')
		</if>
		<if test="params.endCreateTime != null and params.endCreateTime != ''">
			and date_format(create_time,'%y%m%d') &lt;= date_format(#{params.endCreateTime},'%y%m%d')
		</if>
		<if test="params.gardenTenantId != null and params.gardenTenantId != ''">
			and d.tenant_id = #{params.gardenTenantId}
		</if>
		<if test="params.excludeDept != null and params.excludeDept != ''">
			and d.dept_type != '3'
		</if>
		<!-- 数据范围过滤 -->
		${params.dataScope}
		order by d.tenant_id, d.order_num, d.create_time desc
	</select>

    <select id="selectDeptListByRoleId" resultType="Long">
		select d.dept_id
		from sys_dept d
            left join sys_role_dept rd on d.dept_id = rd.dept_id
        where rd.role_id = #{roleId}
            <if test="deptCheckStrictly">
              and d.dept_id not in (select d.parent_id from sys_dept d inner join sys_role_dept rd on d.dept_id = rd.dept_id and rd.role_id = #{roleId})
            </if>
		order by d.parent_id, d.order_num
	</select>

    <select id="selectDeptById" parameterType="Long" resultMap="SysDeptResult">
		<include refid="selectDeptVo"/>
		where d.dept_id = #{deptId}
	</select>

	<select id="selectTopDeptByTenantId" parameterType="Long" resultMap="SysDeptResult">
		<include refid="selectDeptVo"/>
		where d.tenant_id = #{tenantId} order by d.ancestors limit 1
	</select>


    <select id="checkDeptExistUser" parameterType="Long" resultType="int">
		select count(1) from sys_user where dept_id = #{deptId} and del_flag = '0'
	</select>

	<select id="hasChildByDeptId" parameterType="Long" resultType="int">
		select count(1) from sys_dept
		where del_flag = '0' and parent_id = #{deptId} limit 1
	</select>

	<select id="selectChildrenDeptById" parameterType="Long" resultMap="SysDeptResult">
		select * from sys_dept where find_in_set(#{deptId}, ancestors)
	</select>

	<select id="selectNormalChildrenDeptById" parameterType="Long" resultType="int">
		select count(*) from sys_dept where status = 0 and del_flag = '0' and find_in_set(#{deptId}, ancestors)
	</select>

	<select id="checkDeptNameUnique" resultMap="SysDeptResult">
	    <include refid="selectDeptVo"/>
		where dept_name = #{deptName}
		and tenant_id = #{tenantId} and del_flag = '0' limit 1
	</select>

	<select id="selectManageSubDepartment" resultType="com.vctgo.system.api.domain.dto.SysDeptDto">
		select d1.dept_id, d1.dept_id manageDept from sys_dept d1
		where d1.del_flag = '0' and d1.dept_id not in (select parent_id from sys_dept)
		<if test="params.isGardenTenant == '1'.toString()">
			and d1.tenant_id in (select id from sys_tenant where garden_id =
			(select garden_id from sys_tenant where id = #{tenantId}))
		</if>
		<if test="params.isSingleTenant != null and params.isSingleTenant != ''">
			and d1.tenant_id = #{params.isSingleTenant}
		</if>
		union
		select d2.parent_id dept_id,
			   CONCAT(d2.parent_id,',',GROUP_CONCAT(DISTINCT d2.dept_id)) manageDept
		from sys_dept d2
		where d2.del_flag = '0'
		<if test="params.isGardenTenant == '1'.toString()">
			and d2.tenant_id in (select id from sys_tenant where garden_id =
			(select garden_id from sys_tenant where id = #{tenantId}))
		</if>
		<if test="params.isSingleTenant != null and params.isSingleTenant != ''">
			and d2.tenant_id = #{params.isSingleTenant}
		</if>
		group by d2.parent_id order by dept_id
	</select>

	<select id="staticsUserCountByDeptId" parameterType="Long" resultType="Long">
		select count(*) userCount from sys_user
		where del_flag ='0' and
				dept_id in (select dept_id from sys_dept where FIND_IN_SET(#{deptId},ancestors)>0 or dept_id=#{deptId})
	</select>

	<select id="staticsUserCountByTenantId" parameterType="Long" resultType="Long">
		select count(*) userCount from sys_user
		where del_flag ='0' and tenant_id in (
			select id from sys_tenant where garden_id in
			(select garden_id from sys_tenant where id = #{tenantId}))
			and dept_id is not null
	</select>

	<insert id="insertDept" >
 		insert into sys_dept(
 			<if test="deptId != null and deptId != 0">dept_id,</if>
 			<if test="parentId != null and parentId != 0">parent_id,</if>
 			<if test="deptName != null and deptName != ''">dept_name,</if>
 			<if test="deptType != null and deptType != ''">dept_type,</if>
 			<if test="ancestors != null and ancestors != ''">ancestors,</if>
		    <if test="orderNum != null">order_num,</if>
 			<if test="leaderId != null and leaderId != 0">leader_id,</if>
 			<if test="leader != null and leader != ''">leader,</if>
 			<if test="phone != null and phone != ''">phone,</if>
 			<if test="email != null and email != ''">email,</if>
 			<if test="status != null">status,</if>
 			<if test="remark != null">remark,</if>
 			<if test="tenantId != null">tenant_id,</if>
 			<if test="createById != null">create_by_id,</if>
 			<if test="createBy != null and createBy != ''">create_by,</if>
 			create_time
 		)values(
 			<if test="deptId != null and deptId != 0">#{deptId},</if>
 			<if test="parentId != null and parentId != 0">#{parentId},</if>
 			<if test="deptName != null and deptName != ''">#{deptName},</if>
 			<if test="deptType != null and deptType != ''">#{deptType},</if>
 			<if test="ancestors != null and ancestors != ''">#{ancestors},</if>
		    <if test="orderNum != null">#{orderNum},</if>
 			<if test="leaderId != null and leaderId != 0">#{leaderId},</if>
 			<if test="leader != null and leader != ''">#{leader},</if>
 			<if test="phone != null and phone != ''">#{phone},</if>
 			<if test="email != null and email != ''">#{email},</if>
 			<if test="status != null">#{status},</if>
 			<if test="remark != null">#{remark},</if>
 			<if test="tenantId != null">#{tenantId},</if>
 			<if test="createById != null">#{createById},</if>
 			<if test="createBy != null and createBy != ''">#{createBy},</if>
 			sysdate()
 		)
	</insert>

	<update id="updateDept" >
 		update sys_dept
 		<set>
 			<if test="parentId != null and parentId != 0">parent_id = #{parentId},</if>
 			<if test="deptName != null and deptName != ''">dept_name = #{deptName},</if>
 			<if test="deptType != null and deptType != ''">dept_type = #{deptType},</if>
 			<if test="ancestors != null and ancestors != ''">ancestors = #{ancestors},</if>
			<if test="orderNum != null">order_num = #{orderNum},</if>
 			leader_id = #{leaderId},leader = #{leader},
 			<if test="phone != null">phone = #{phone},</if>
 			<if test="email != null">email = #{email},</if>
 			<if test="status != null and status != ''">status = #{status},</if>
 			<if test="remark != null and remark != ''">remark = #{remark},</if>
 			<if test="updateBy != null and updateBy != ''">update_by = #{updateBy},</if>
 			update_time = sysdate()
 		</set>
 		where dept_id = #{deptId}
	</update>

	<update id="updateDeptChildren" parameterType="java.util.List">
	    update sys_dept set ancestors =
	    <foreach collection="depts" item="item" index="index"
	        separator=" " open="case dept_id" close="end">
	        when #{item.deptId} then #{item.ancestors}
	    </foreach>
	    where dept_id in
	    <foreach collection="depts" item="item" index="index"
	        separator="," open="(" close=")">
	        #{item.deptId}
	    </foreach>
	</update>

	<update id="updateDeptStatusNormal" parameterType="Long">
 	    update sys_dept set status = '0' where dept_id in
 	    <foreach collection="array" item="deptId" open="(" separator="," close=")">
        	#{deptId}
        </foreach>
	</update>

    <update id="clearLeaderInfoByUserId">
		update sys_dept set leader_id = null, leader = null where leader_id = #{userId}
	</update>

    <update id="manageLeader">
		update sys_dept set leader_id = #{leaderId}, leader = #{leader}
		where dept_id = #{deptId}
	</update>

    <update id="updateTenantName">
		update sys_dept set dept_name = #{deptName}
		where del_flag = '0'
		<choose>
			<when test="deptId != null and deptType == '1'.toString()">
				and tenant_id = (select id from sys_tenant where del_flag = '0' and garden_id = #{deptId} and tenant_type = 1) and dept_type = #{deptType}
			</when>
			<when test="deptId != null and deptType == '2'.toString()">
				and tenant_id = (select id from sys_tenant where del_flag = '0' and branch_id = #{deptId} and tenant_type in (2, 3)) and dept_type = #{deptType}
			</when>
			<otherwise >
			</otherwise>
		</choose>
	</update>

    <delete id="deleteDeptById" parameterType="Long">
		update sys_dept set del_flag = '1' where dept_id = #{deptId}
	</delete>

	<delete id="deleteDeptByTenantId">
		delete from sys_dept where tenant_id in
		<foreach item="id" collection="array" open="(" separator="," close=")">
			#{id}
		</foreach>
	</delete>


</mapper>
