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

    <resultMap type="SysUser" id="SysUserResult">
        <id     property="userId"       column="userId"      />
        <result property="deptId"       column="deptId"      />
        <result property="userName"     column="userName"    />
        <result property="userLoginname"     column="userLoginname"    />
        <result property="userEmail"        column="userEmail"        />
        <result property="userMobile"  column="userMobile"  />
        <result property="userSex"          column="userSex"          />
		<result property="userAvatar"          column="userAvatar"          />
        <result property="userLoginpwd"     column="userLoginpwd"     />
        <result property="userLockstatus"       column="userLockstatus"       />
        <result property="userDelstatus"      column="userDelstatus"     />
        <result property="userLastloginip"      column="userLastloginip"     />
        <result property="userLastlogindate"    column="userLastlogindate"   />
        <result property="userCreator"     column="userCreator"    />
        <result property="userCreateddate"   column="userCreateddate"  />
        <result property="userModifier"     column="userModifier"    />
        <result property="userModifieddate"   column="userModifieddate"  />
        <result property="userRemark"       column="userRemark"       />
        <association property="dept"    column="deptId" javaType="SysDept" resultMap="deptResult" />
        <collection  property="roles"   javaType="java.util.List"           resultMap="RoleResult" />
    </resultMap>
	
    <resultMap id="deptResult" type="SysDept">
        <id     property="deptId"    column="deptId"     />
        <result property="parentId"  column="parent_id"   />
        <result property="deptName"  column="deptName"   />
        <result property="deptLevelcode" column="deptLevelcode"   />
        <result property="deptSeq"  column="deptSeq"   />
        <result property="deptManager"    column="deptManager"      />
        <result property="deptDelstatus"    column="deptDelstatus" />
    </resultMap>
	
    <resultMap id="RoleResult" type="SysRole">
        <id     property="roleId"       column="roleId"        />
        <result property="roleName"     column="role_name"      />
        <result property="roleKey"      column="role_key"       />
        <result property="roleSort"     column="role_sort"      />
        <result property="dataScope"     column="data_scope"    />
        <result property="status"       column="role_status"    />
    </resultMap>
	
	<sql id="selectUserVo">
        select u.userId, u.deptId, u.userLoginname, u.userName, u.userEmail, u.userMobile, u.userLoginpwd, u.userSex, u.userLockstatus, u.userDelstatus, u.userLastloginip, u.userLastlogindate, u.userCreator, u.userCreateddate, u.userRemark,
        d.deptId, d.deptParentid, d.deptLevelcode, d.deptName, d.deptSeq, d.deptManager, d.deptDelstatus as deptDelstatus,
        r.roleId, r.roleName, r.roleKey, r.roleSeq, r.roleDatascope, r.roleAvlstatus as roleAvlstatus,

        res.resUrl as userAvatar
        from SysUser u
		    left join SysDept d on u.deptId = d.deptId
		    left join SysUserrole ur on u.userId = ur.userId
		    left join SysRole r on r.roleId = ur.roleId

		    left join SysRes res on res.resBusId = u.userId
    </sql>
    
    <select id="selectUserList" parameterType="SysUser" resultMap="SysUserResult">
		select u.userId, u.deptId, u.userName,u.userLoginname,  u.userEmail, u.userMobile, u.userSex, u.userLockstatus, u.userDelstatus, u.userLastloginip, u.userLastlogindate, u.userCreator, u.userCreateddate, u.userRemark, d.deptName, d.deptManager from SysUser u
		left join SysDept d on u.deptId = d.deptId
		where u.userDelstatus = '0'
		<if test="userId != null and userId != ''">
			AND u.userId = #{userId}
		</if>
		<if test="userLoginname != null and userLoginname != ''">
			AND u.userLoginname like concat('%', #{userLoginname}, '%')
		</if>
		<if test="userLockstatus != null and userLockstatus != ''">
			AND u.userLockstatus = #{userLockstatus}
		</if>
		<if test="userMobile != null and userMobile != ''">
			AND u.userMobile like concat('%', #{userMobile}, '%')
		</if>
		<if test="params.beginTime != null and params.beginTime != ''"><!-- 开始时间检索 -->
			AND date_format(u.userCreateddate,'%y%m%d') &gt;= date_format(#{params.beginTime},'%y%m%d')
		</if>
		<if test="params.endTime != null and params.endTime != ''"><!-- 结束时间检索 -->
			AND date_format(u.userCreateddate,'%y%m%d') &lt;= date_format(#{params.endTime},'%y%m%d')
		</if>
		<if test="deptId != null and deptId != ''">
			AND (u.deptId = #{deptId} OR u.deptId IN ( SELECT t.deptId FROM SysDept t WHERE find_in_set(#{deptId}, deptLevelcode) ))
		</if>
		<!-- 数据范围过滤 -->
		${params.dataScope}
	</select>

	<select id="selectUserListOfAuth" parameterType="SysUser" resultMap="SysUserResult">
		select u.userId, u.deptId, u.userName,u.userLoginname,  u.userEmail, u.userMobile, u.userSex, u.userLockstatus,
		u.userDelstatus, u.userLastloginip, u.userLastlogindate, u.userCreator, u.userCreateddate, u.userRemark
		from SysUser u
		left join SysUserrole ur on u.userId = ur.userId
		left join SysRole r on r.roleId = ur.roleId
		where u.userDelstatus = '0'
		<if test="userId != null and userId != ''">
			AND u.userId = #{userId}
		</if>
		<if test="userLoginname != null and userLoginname != ''">
			AND u.userLoginname like concat('%', #{userLoginname}, '%')
		</if>
		<if test="userLockstatus != null and userLockstatus != ''">
			AND u.userLockstatus = #{userLockstatus}
		</if>
		<if test="userMobile != null and userMobile != ''">
			AND u.userMobile like concat('%', #{userMobile}, '%')
		</if>
		<if test="params.beginTime != null and params.beginTime != ''"><!-- 开始时间检索 -->
			AND date_format(u.userCreateddate,'%y%m%d') &gt;= date_format(#{params.beginTime},'%y%m%d')
		</if>
		<if test="params.endTime != null and params.endTime != ''"><!-- 结束时间检索 -->
			AND date_format(u.userCreateddate,'%y%m%d') &lt;= date_format(#{params.endTime},'%y%m%d')
		</if>
		<if test="roleKey != null and roleKey != ''"><!-- 结束时间检索 -->
			AND r.roleKey = #{roleKey}
		</if>
		GROUP BY u.userId
	</select>
	
	<select id="selectAllocatedList" parameterType="SysUser" resultMap="SysUserResult">
	    select distinct u.userId, u.deptId, u.userLoginname, u.userName, u.userEmail, u.userMobile, u.userLockstatus, u.userCreateddate
	    from SysUser u
			 left join SysDept d on u.deptId = d.deptId
			 left join SysUserrole ur on u.userId = ur.userId
			 left join SysRole r on r.roleId = ur.roleId
	    where u.userDelstatus = '0' and r.roleId = #{roleId}
	    <if test="userLoginname != null and userLoginname != ''">
			AND u.userLoginname like concat('%', #{userLoginname}, '%')
		</if>
		<if test="userMobile != null and userMobile != ''">
			AND u.userMobile like concat('%', #{userMobile}, '%')
		</if>
		<!-- 数据范围过滤 -->
		${params.dataScope}
	</select>
	
	<select id="selectUnallocatedList" parameterType="SysUser" resultMap="SysUserResult">
	    select distinct u.userId, u.deptId, u.userLoginname, u.userName,  u.userEmail, u.userMobile, u.userLockstatus, u.userCreateddate
	    from SysUser u
			 left join SysDept d on u.deptId = d.deptId
			 left join SysUserrole ur on u.userId = ur.userId
			 left join SysRole r on r.roleId = ur.roleId
	    where u.userDelstatus = '0' and (r.roleId != #{roleId} or r.roleId IS NULL)
	    and u.userId not in (select u.userId from SysUser u inner join SysUserrole ur on u.userId = ur.userId and ur.roleId = #{roleId})
	    <if test="userLoginname != null and userLoginname != ''">
			AND u.userLoginname like concat('%', #{userLoginname}, '%')
		</if>
		<if test="userMobile != null and userMobile != ''">
			AND u.userMobile like concat('%', #{userMobile}, '%')
		</if>
		<!-- 数据范围过滤 -->
		${params.dataScope}
	</select>
	
	<select id="selectUserByUserName" parameterType="String" resultMap="SysUserResult">
	    <include refid="selectUserVo"/>
		where u.userLoginname = #{userLoginname} and u.userDelstatus = '0'
	</select>
	
	<select id="selectUserById" parameterType="String" resultMap="SysUserResult">
		<include refid="selectUserVo"/>
		where u.userId = #{userId}
	</select>
	
	<select id="checkUserNameUnique" parameterType="String" resultMap="SysUserResult">
		select userId, userLoginname from SysUser where userLoginname = #{userLoginname} and userDelstatus = '0' limit 1
	</select>
	
	<select id="checkPhoneUnique" parameterType="String" resultMap="SysUserResult">
		select userId, userMobile from SysUser where userMobile = #{userMobile} and userDelstatus = '0' limit 1
	</select>
	
	<select id="checkEmailUnique" parameterType="String" resultMap="SysUserResult">
		select userId, userEmail from SysUser where userEmail = #{userEmail} and userDelstatus = '0' limit 1
	</select>
	
	<insert id="insertUser" parameterType="SysUser" useGeneratedKeys="true" keyProperty="userId">
 		insert into SysUser(
 			<if test="userId != null and userId != ''">userId,</if>
 			<if test="deptId != null and deptId != ''">deptId,</if>
 			<if test="userLoginname != null and userLoginname != ''">userLoginname,</if>
 			<if test="userName != null and userName != ''">userName,</if>
 			<if test="userEmail != null and userEmail != ''">userEmail,</if>
 			<if test="userMobile != null and userMobile != ''">userMobile,</if>
 			<if test="userSex != null and userSex != ''">userSex,</if>
 			<if test="userLoginpwd != null and userLoginpwd != ''">userLoginpwd,</if>
 			<if test="userLockstatus != null and userLockstatus != ''">userLockstatus,</if>
 			<if test="userCreator != null and userCreator != ''">userCreator,</if>
 			<if test="userRemark != null and userRemark != ''">userRemark,</if>
		userCreateddate
 		)values(
 			<if test="userId != null and userId != ''">#{userId},</if>
 			<if test="deptId != null and deptId != ''">#{deptId},</if>
 			<if test="userLoginname != null and userLoginname != ''">#{userLoginname},</if>
 			<if test="userName != null and userName != ''">#{userName},</if>
 			<if test="userEmail != null and userEmail != ''">#{userEmail},</if>
 			<if test="userMobile != null and userMobile != ''">#{userMobile},</if>
 			<if test="userSex != null and userSex != ''">#{userSex},</if>
 			<if test="userLoginpwd != null and userLoginpwd != ''">#{userLoginpwd},</if>
 			<if test="userLockstatus != null and userLockstatus != ''">#{userLockstatus},</if>
 			<if test="userCreator != null and userCreator != ''">#{userCreator},</if>
 			<if test="userRemark != null and userRemark != ''">#{userRemark},</if>
		date_format(sysdate(),'%Y-%m-%d %H:%i:%s')
 		)
	</insert>
	
	<update id="updateUser" parameterType="SysUser">
 		update SysUser
 		<set>
 			<if test="deptId != null and deptId != ''">deptId = #{deptId},</if>
 			<if test="userLoginname != null and userLoginname != ''">userLoginname = #{userLoginname},</if>
 			<if test="userName != null and userName != ''">userName = #{userName},</if>
 			<if test="userEmail != null ">userEmail = #{userEmail},</if>
 			<if test="userMobile != null ">userMobile = #{userMobile},</if>
 			<if test="userSex != null and userSex != ''">userSex = #{userSex},</if>
 			<if test="userLoginpwd != null and userLoginpwd != ''">userLoginpwd = #{userLoginpwd},</if>
 			<if test="userLockstatus != null and userLockstatus != ''">userLockstatus = #{userLockstatus},</if>
 			<if test="userLastloginip != null and userLastloginip != ''">userLastloginip = #{userLastloginip},</if>
 			<if test="userLastlogindate != null">userLastlogindate = #{userLastlogindate},</if>
 			<if test="userModifier != null and userModifier != ''">userModifier = #{userModifier},</if>
 			<if test="userRemark != null">userRemark = #{userRemark},</if>
			userModifieddate = date_format(sysdate(),'%Y-%m-%d %H:%i:%s')
 		</set>
 		where userId = #{userId}
	</update>
	
	<update id="updateUserStatus" parameterType="SysUser">
 		update SysUser set userLockstatus = #{userLockstatus} where userId = #{userId}
	</update>

	<update id="resetUserPwd" parameterType="SysUser">
 		update SysUser set userLoginpwd = #{userLoginpwd} where userLoginname = #{userLoginname}
	</update>
	
	<delete id="deleteUserById" parameterType="String">
 		update SysUser set userDelstatus = '2' where userId = #{userId}
 	</delete>
 	
 	<delete id="deleteUserByIds" parameterType="String">
 		update SysUser set userDelstatus = '2' where userId in
 		<foreach collection="array" item="userId" open="(" separator="," close=")">
 			#{userId}
        </foreach> 
 	</delete>
	
</mapper> 