<?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.sunxung.factoring.mapper.sys.UserMapper">

	<resultMap type="User" id="userResult">
		<result property="id" column="id" />
		<result property="userid" column="userid" />
		<result property="loginName" column="login_name" />
		<result property="name" column="name" />
		<result property="password" column="password" />
		<result property="mobile" column="mobile" />
		<result property="email" column="email" />
		<result property="jobNumber" column="job_number" />
		<result property="position" column="position" />
		<result property="tel" column="tel" />
		<result property="enabled" column="is_enabled" />
		<result property="gradation" column="gradation" />
		<result property="headImgUrl" column="head_img_url" />
		<result property="simplePinYin" column="simple_pinyin" />
		<result property="fullPinYin" column="full_pinyin" />
		<result property="relationDingDing" column="is_relation_dingding" />
		<result property="userCreate" column="user_create" />
		<result property="userModified" column="user_modified" />
		<result property="gmtCreate" column="gmt_create" />
		<result property="gmtModified" column="gmt_modified" />
		<result property="rank" column="rank" />
		<result property="followType" column="follow_type"/>
		<result property="idCard" column="id_card"/>
		<result property="referenceNumber" column="reference_number"/>
	</resultMap>

	<resultMap type="User" id="userExtendsResult">
		<result property="id" column="id" />
		<result property="name" column="name" />
		<result property="mobile" column="mobile" />
		<result property="jobNumber" column="job_number" />
	</resultMap>

	<resultMap type="UserOrgRel" id="userOrgRelAllResult">
		<result property="userId" column="user_id"/>
		<result property="orgId" column="org_id"/>
		<result property="orgCode" column="org_code"/>
		<result property="userIdentity" column="user_identity"/>
		<result property="gradation" column="gradation"/>
	</resultMap>

	<sql id="user_fields">
		id,
		userid,
		login_name,
		name,
		password,
		mobile,
		email,
		job_number,
		position,
		tel,
		is_enabled,
		gradation,
		head_img_url,
		simple_pinyin
		full_pinyin,
		is_relation_dingding,
		user_create,
		user_modified,
		gmt_create,
		gmt_modified,
		rank,
		follow_type,
    id_card,
    reference_number
	</sql>

	<sql id="userSysRel_fields">
		user_id,
		org_id,
		org_code,
		user_identity,
		gradation
	</sql>

	<insert id="add" useGeneratedKeys="true" keyProperty="id" keyColumn="id" parameterType="User">
		insert into sys_user
			( userid, login_name, name,email,head_img_url,job_number,
			password, mobile, is_enabled,position,tel,gradation,is_relation_dingding,
			simple_pinyin, full_pinyin, user_create, user_modified, rank, follow_type,enterprise_customer_id,id_card,reference_number )
		values
			( #{userid}, #{loginName},#{name},#{email},
			#{headImgUrl},#{jobNumber}, #{password}, #{mobile},#{enabled},
			#{position},#{tel},#{gradation},#{relationDingDing},#{simplePinYin},#{fullPinYin}, #{userCreate},
			#{userModified}, #{rank}, #{followType},#{enterpriseCustomerId},#{idCard},#{referenceNumber})
	</insert>

	<insert id="addUserOrgRel">
		insert into sys_user_org_rel
			(user_id,org_id,org_code,user_identity,gradation)
		values
			(#{userId},#{orgId},#{orgCode},#{userIdentity},#{gradation})
	</insert>

	<delete id="deleteUserOrgRel" parameterType="java.lang.Long">
		delete from sys_user_org_rel where user_id=#{userId}
	</delete>

	<delete id="deleteUserOrgRelByOrgId" parameterType="java.lang.Long">
		delete from sys_user_org_rel where org_id=#{orgId}
	</delete>

	<select id="getOrgRelByUserId" parameterType="java.lang.Long" resultMap="userOrgRelAllResult">
		select <include refid="userSysRel_fields"/> from sys_user_org_rel where user_id=#{userId}
	</select>

	<insert id="addUserRoleRel" parameterType="UserRoleRel">
		insert into sys_user_role_rel
			( user_id, role_id,admin )
		values
			( #{userId}, #{roleId},#{admin} )
	</insert>

	<update id="update" parameterType="User">
		update sys_user
		<set>
			id=#{id}
			<if test="name != null and name != ''">
				, name=#{name}
			</if>
			<if test="loginName != null and loginName !='' ">
				,login_name=#{loginName}
			</if>
			<if test="mobile != null and mobile != ''">
				,mobile=#{mobile}
			</if>
			<if test="simplePinYin != null and simplePinYin != ''">
				, simple_pinyin=#{simplePinYin}
			</if>
			<if test="userid != null and userid != '' ">
				,userid=#{userid}
			</if>
		   <if test="email != null and email !='' ">
				,email=#{email}
			</if>
			<if test="headImgUrl != null and headImgUrl!='' ">
				,head_img_url=#{headImgUrl}
			</if>
			<if test="rank != null and rank !='' ">
				,rank=#{rank}
			</if>
			<if test="followType != null ">
				,follow_type=#{followType}
			</if>
		    <if test="password != null" >
				,password=#{password}
			</if>
			<if test="enabled != null ">
				, is_enabled=#{enabled}
			</if>
			 ,position=#{position}
			 ,tel=#{tel}
			 , full_pinyin=#{fullPinYin}
			 ,job_number=#{jobNumber}
			, is_relation_dingding=#{relationDingDing}
			, user_modified=#{userModified}
		  ,id_card = #{idCard}
		</set>
		where id=#{id}
	</update>

	<update id="resetPassword">
		update sys_user set password=#{password} where id = #{id}
	</update>

	<update id="modifyLock">
		update sys_user set is_enabled=#{enabled} where id=#{id}
	</update>

	<select id="get" parameterType="java.lang.Long" resultMap="userResult">
		select <include refid="user_fields"/> from sys_user where id=#{value}
	</select>

	<select id="getByLoginName" parameterType="java.lang.String" resultMap="userResult">
		select <include refid="user_fields"/> from sys_user where login_name = #{value}
	</select>

	<sql id="sql_where">
		<where>
			<if test="orgId != null">
				rel.org_id=#{orgId}
			</if>
			<if test="roleId != null">
				and rel.role_id=#{roleId}
			</if>
			<if test="enabled != null">
				and is_enabled=#{enabled}
			</if>
			<if test="userIds != null and userIds.size()>0">
				and id in
				<foreach collection="userIds" item="item" index="index" open="(" separator="," close=")">
					#{item}
				</foreach>
			</if>
		</where>
	</sql>

	<!-- 获取用户总数量 -->
	<select id="countBySearch" parameterType="SeachUser" resultType="java.lang.Integer">
		select count(*) from sys_user u
		left join sys_user_org_rel rel on u.id=rel.user_id
		<include refid="sql_where"/>
	</select>

	<!-- 用户管理页面查询 -->
	<select id="findBySearch" parameterType="SeachUser" resultMap="userResult">
		select u.* from sys_user u
		left join sys_user_org_rel rel on u.id=rel.user_id
		<include refid="sql_where"/>
		order by rel.gradation asc
	</select>

	<select id="findUserIdByRoleId" parameterType="java.lang.Long" resultType="java.lang.Long">
		SELECT user_id FROM sys_user_role_rel WHERE role_id=#{roleId} ORDER BY gradation ASC
	</select>

	<select id="findByRoleId" parameterType="SeachUser" resultMap="userResult">
		select u.name,u.job_number,u.mobile,u.id
		from  sys_user u
	    LEFT join sys_user_role_rel role on role.user_id=u.id
	    where role.role_id=#{roleId}
		ORDER BY role.gradation
	</select>

	<resultMap type="UserRoleRel" id="userRoleRelResult">
		<result property="userId" column="user_id" />
		<result property="roleId" column="role_id" />
		<result property="admin" column="admin" />
	</resultMap>
	<!-- 用户管理修改页面引入已被选中的岗位 -->
	<select id="getUserRoleByUserId" resultMap="userRoleRelResult">
		select user_id,role_id,admin from sys_user_role_rel where user_id = #{value}
	</select>

	<select id="isRoleAdmin" resultType="boolean">
		select admin from sys_user_role_rel where user_id = #{userId} and roleId=#{roleId}
	</select>

	<select id="getRoleRelByRoleId" parameterType="java.lang.Long" resultMap="userRoleRelResult">
		select user_id,role_id,admin from sys_user_role_rel
		<where>
			<if test="roleId != null ">
				and role_id=#{roleId}
			</if>
			<if test="userId">
		    	and user_id=#{userId}
			</if>
		</where>

	</select>

	<!-- 当前用户的密码密文的获取 -->
	<select id="getUserPasswordSimilar" resultType="java.lang.String">
		select password from sys_user where id = #{value}
	</select>

	<!-- 删除当前用户和所有岗位的关系 -->
	<delete id="deleteUserRoleRel">
		delete from sys_user_role_rel where user_id = #{value}
	</delete>

	<!-- 删除当前用户 -->
	<delete id="delete">
		delete from sys_user where id = #{value}
	</delete>

	<select id="findByOrgcode" parameterType="SearchWeekWorkPlan" resultMap="userResult">
		select u.id
		from  (
				select min(org_id) org_id,user_id,max(gradation) gradation,min(org_code) org_code from sys_user_org_rel
				<if test="null != department and department.internalCode != null">
					 where org_code like CONCAT(#{department.internalCode},'%')
				</if>
				 GROUP BY user_id

		) gr
		left join (
			select id,parent_id,name,dept_no,sub_count,gradation,internal_code,seq from sys_org
			order by seq,parent_id,gradation
		) org on org.id = gr.org_id
		LEFT join sys_user u on u.id = gr.user_id
		where u.is_enabled=1
		<if test="null != department and department.internalCode != null and department.internalCode != ''">
			and gr.org_code like CONCAT(#{department.internalCode},'%')
		</if>

		<if test="null == sellAll">
			and u.id in(
					<!-- 查询当前登录用户所在部门及部门下的子部门能查看的所有用户 -->
							select u.id from sys_user u
							left join sys_user_org_rel s on u.id=s.user_id
							where u.is_enabled=1
							<!-- 当前登录用户所在部门及部门下的子部门所有用户 -->
							<if test="null != orgList and orgList.size()>0">
								and
								<foreach collection="orgList" item="item" index="index" open="(" separator="or" close=")">
									s.org_code like CONCAT(#{item.internalCode},'%')
								</foreach>
							</if>
							<!-- 如果当前登录用户所在的部门中有是普通身份的，则需要去除普通身份的部门中的上级身份的用户 -->
							<if test="null != normalOrgList and normalOrgList.size()>0">
								and s.user_id not in(
								select user_id from sys_user_org_rel where
								org_id in
								<foreach collection="normalOrgList" item="item" index="index" open="(" separator="," close=")">
									#{item.id}
								</foreach>
								and user_identity=1)
							</if>
						)

		</if>
		ORDER BY org.seq,org.parent_id,org.gradation,gr.gradation

	</select>

	<select id="countByOrgcode" parameterType="SearchWeekWorkPlan" resultType="Integer">
		select count(DISTINCT b.id)  from (
		select u.id from sys_user u
		left join sys_user_org_rel s on u.id=s.user_id
		left join sys_org o on o.id=s.org_id
		where u.is_enabled=1
			<if test="null != department and department.internalCode != null and department.internalCode != ''">
				and s.org_code like CONCAT(#{department.internalCode},'%')
			</if>

			<if test="null == sellAll">
				and u.id in(
						select u.id from sys_user u
						left join sys_user_org_rel s on u.id=s.user_id
						where u.is_enabled=1
						<if test="null != orgList and orgList.size>0">
						    and
							<foreach collection="orgList" item="item" index="index" open="(" separator="or" close=")">
								s.org_code like CONCAT(#{item.internalCode},'%')
							</foreach>
						</if>
						<if test="null != normalOrgList and normalOrgList.size>0">
							and s.user_id not in(
							select user_id from sys_user_org_rel where
							org_id in
							<foreach collection="normalOrgList" item="item" index="index" open="(" separator="," close=")">
								#{item.id}
							</foreach>
							and user_identity=1)
						</if>
					)

			</if>
			) b
	</select>


    <select id="orderUserByOrgGradation" parameterType="SearchWeekWorkPlan" resultMap="userResult">
        select u.id
        from  (
            select min(org_id) org_id,user_id,max(gradation) gradation,min(org_code) org_code from sys_user_org_rel
            GROUP BY user_id

        ) gr
        left join (
            select id,parent_id,name,dept_no,sub_count,gradation,internal_code,seq from sys_org
            order by seq,parent_id,gradation
        ) org on org.id = gr.org_id
        LEFT join sys_user u on u.id = gr.user_id
        where u.is_enabled=1
        <if test="null != userList and userList.size>0">
            and u.id in

            <foreach collection="userList" item="item" index="index" open="(" separator="," close=")">
                #{item.id}
            </foreach>
        </if>

        ORDER BY org.seq,org.parent_id,org.gradation,gr.gradation

    </select>

	<select id="countUserOrgRelByOrgCode" parameterType="java.lang.String" resultType="java.lang.Integer">
		select count(1) from (
	  		select DISTINCT user_id from sys_user_org_rel rel where rel.org_code like CONCAT(#{orgCode},'%')
		    <if test="orgCode!='.888.' ">
					and  rel.org_code !='.888.'
				</if>
		)aa
	</select>

	<select id="getByUserIdAndOrgId" resultMap="userOrgRelAllResult">
		select <include refid="userSysRel_fields"/> from sys_user_org_rel where user_id=#{userId} and org_id=#{orgId}
	</select>

	<select id="findUserOrgRel" resultMap="userOrgRelAllResult">
		SELECT
			user_id,org_id,org_code ,user_identity,gradation
		FROM
			sys_user_org_rel
		<where>
			<if test="null != userId">
				and user_id=#{userId}
			</if>
			<if test="null != orgId">
				and org_id=#{orgId}
			</if>
		</where>
	</select>

	<select id="findUserIdByOrgId" parameterType="java.lang.Long" resultType="java.lang.Long">
		SELECT user_id FROM sys_user_org_rel WHERE org_id=#{orgId}
	</select>

	<select id="getUserOrgRelByUserId" resultMap="userOrgRelAllResult">
		select <include refid="userSysRel_fields"/> from sys_user_org_rel where user_id=#{userId} order by org_id
	</select>

	<select id="getByDingdingUserId" parameterType="java.lang.String" resultMap="userResult">
		select <include refid="user_fields"/> from sys_user where userid=#{userId}
	</select>

	<update id="adjustOrder">
		update sys_user_org_rel set gradation=#{gradation} where user_id=#{userId} and org_id=#{orgId}
	</update>

	<select id="findList" parameterType="java.lang.String" resultMap="userResult">
		select <include refid="user_fields"/> from sys_user
		<where>
			<if test="keyword != null and keyword !=''">
				name LIKE CONCAT('%',#{keyword},'%') or simple_pinyin like  CONCAT('%',#{keyword},'%')
				or full_pinyin like  CONCAT('%',#{keyword},'%')
			</if>
		</where>
	</select>

	<select id="getMaxGradationByOrgId" parameterType="java.lang.Long" resultType="java.lang.Integer">
		select max(gradation) from sys_user_org_rel where org_id=#{orgId}
	</select>

	<select id="findViewByOrgCode" parameterType="SearchWeekWorkPlan" resultType="java.lang.Long">
		<!-- 查询当前登录用户所在部门及部门下的子部门能查看的所有用户 -->
		select u.id from sys_user u
		left join sys_user_org_rel s on u.id=s.user_id
		where u.is_enabled=1
		<!-- 当前登录用户所在部门及部门下的子部门所有用户 -->
		<if test="null != orgList and orgList.size()>0">
		    and
			<foreach collection="orgList" item="item" index="index" open="(" separator="or" close=")">
				s.org_code like CONCAT(#{item.internalCode},'%')
			</foreach>
		</if>
		<!-- 如果当前登录用户所在的部门中有是普通身份的，则需要去除普通身份的部门中的上级身份的用户 -->
		<if test="null != normalOrgList and normalOrgList.size()>0">
			and s.user_id not in(
			select user_id from sys_user_org_rel where
			<if test="normalOrgList !=null and normalOrgList.size()>0">
				org_id in
				<foreach collection="normalOrgList" item="item" index="index" open="(" separator="," close=")">
					#{item.id}
				</foreach>
			</if>
			and user_identity=1)
		</if>
	</select>


	<select id="getByName" parameterType="java.lang.String" resultMap="userResult">
		select <include refid="user_fields"/> from sys_user where name = #{value}
	</select>



	<select id="getListByName" resultType="com.sunxung.factoring.entity.sys.User"
		parameterType="java.lang.String">
		select <include refid="user_fields"/> from sys_user where name = #{value}
	</select>


	<select id="findAllEnabledUserEmail" resultType="java.lang.String">
		SELECT DISTINCT
			u.email
		FROM
			sys_user u
		WHERE u.follow_type in(0,1)
	</select>

	<select id="selectByEnterpriseCustomerId" parameterType="java.lang.Long" resultType="User">
		select * from sys_user where enterprise_customer_id = #{value}
	</select>
	<select id="getUserByEnterpriseCustomerId" resultType="com.sunxung.factoring.entity.sys.User">
		SELECT * from sys_user where `enterprise_customer_id`= #{value}
	</select>
    <select id="findAllLoginName" resultType="java.lang.String">
		SELECT login_name FROM sys_user
	</select>




</mapper>
