<?xml version="1.0" encoding="UTF-8"?>
<!--
  ~ Copyright (c) 2020 yifu4cloud Authors. All Rights Reserved.
  ~
  ~ Licensed under the Apache License, Version 2.0 (the "License");
  ~ you may not use this file except in compliance with the License.
  ~ You may obtain a copy of the License at
  ~
  ~     http://www.apache.org/licenses/LICENSE-2.0
  ~
  ~ Unless required by applicable law or agreed to in writing, software
  ~ distributed under the License is distributed on an "AS IS" BASIS,
  ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  ~ See the License for the specific language governing permissions and
  ~ limitations under the License.
  -->

<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.yifu.cloud.plus.v1.yifu.admin.mapper.SysUserMapper">
	<!-- 通用查询映射结果 -->
	<resultMap id="baseResultMap" type="com.yifu.cloud.plus.v1.yifu.admin.api.vo.UserVO">
		<id column="user_id" property="userId"/>
		<result column="username" property="username"/>
		<result column="password" property="password"/>
		<result column="salt" property="salt"/>
		<result column="phone" property="phone"/>
		<result column="avatar" property="avatar"/>
		<result column="ucreate_time" property="createTime"/>
		<result column="uupdate_time" property="updateTime"/>
		<result column="lock_flag" property="lockFlag"/>
		<result column="udel_flag" property="delFlag"/>
		<result column="deptId" property="deptId"/>
		<result column="deptName" property="deptName"/>
		<result column="nickname" property="nickname"/>
		<result column="user_group" property="userGroup"/>
		<result column="post" property="post"/>
		<result column="email" property="email"/>
		<result column="system_flag" property="systemFlag"/>
		<result column="type" property="type"/>
		<collection property="roleList" ofType="com.yifu.cloud.plus.v1.yifu.admin.api.entity.SysRole"
					select="com.yifu.cloud.plus.v1.yifu.admin.mapper.SysRoleMapper.listRolesByUserId" column="user_id">
		</collection>
	</resultMap>

	<!-- userVo结果集 -->
	<resultMap id="userVoResultMap" type="com.yifu.cloud.plus.v1.yifu.admin.api.vo.UserVO">
		<id column="user_id" property="userId"/>
		<result column="username" property="username"/>
		<result column="password" property="password"/>
		<result column="salt" property="salt"/>
		<result column="phone" property="phone"/>
		<result column="avatar" property="avatar"/>
		<result column="ucreate_time" property="createTime"/>
		<result column="uupdate_time" property="updateTime"/>
		<result column="lock_flag" property="lockFlag"/>
		<result column="udel_flag" property="delFlag"/>
		<result column="deptId" property="deptId"/>
		<result column="deptName" property="deptName"/>
		<result column="nickname" property="nickname"/>
		<result column="user_group" property="userGroup"/>
		<result column="post" property="post"/>
		<result column="email" property="email"/>
		<result column="system_flag" property="systemFlag"/>
		<result column="type" property="type"/>
		<collection property="roleList" ofType="com.yifu.cloud.plus.v1.yifu.admin.api.entity.SysRole">
			<id column="role_id" property="roleId"/>
			<result column="role_name" property="roleName"/>
			<result column="role_code" property="roleCode"/>
			<result column="role_desc" property="roleDesc"/>
			<result column="rcreate_time" property="createTime"/>
			<result column="rupdate_time" property="updateTime"/>
		</collection>
	</resultMap>

	<sql id="userRoleSql">
		sys_user.user_id,
		sys_user.username,
		sys_user.nickname,
		sys_user.`password`,
		sys_user.salt,
		sys_user.phone,
		sys_user.avatar,
		sys_user.dept_id,
		sys_user.create_time AS ucreate_time,
		sys_user.update_time AS uupdate_time,
		sys_user.del_flag    AS udel_flag,
		sys_user.lock_flag   AS lock_flag,
		sys_user.dept_id     AS deptId,
		sys_user.user_group,
		sys_user.post,
		sys_user.email,
		sys_user.system_flag,
		r.role_id,
		r.role_name,
		r.role_code,
		r.role_desc,
		r.create_time        AS rcreate_time,
		r.update_time        AS rupdate_time
	</sql>

	<sql id="userRoleDeptSql">
		sys_user.user_id,
		sys_user.username,
		sys_user.nickname,
		sys_user.`password`,
		sys_user.salt,
		sys_user.phone,
		sys_user.avatar,
		sys_user.create_time AS ucreate_time,
		sys_user.update_time AS uupdate_time,
		sys_user.del_flag    AS udel_flag,
		sys_user.lock_flag   AS lock_flag,
		sys_user.user_group,
		sys_user.post,
		sys_user.email,
		sys_user.system_flag,
		r.role_id,
		r.role_name,
		r.role_code,
		r.role_desc,
		r.create_time        AS rcreate_time,
		r.update_time        AS rupdate_time,
		d.name               AS deptName,
		d.dept_id            AS deptId
	</sql>

	<select id="getUserVoByUsername" resultMap="userVoResultMap">
		SELECT
		<include refid="userRoleSql"/>
		FROM sys_user AS sys_user
					 LEFT JOIN sys_user_role AS ur ON ur.user_id = sys_user.user_id
					 LEFT JOIN sys_role AS r ON r.role_id = ur.role_id
		WHERE sys_user.username = #{username}
	</select>

	<select id="getUserVoById" resultMap="userVoResultMap">
		SELECT
		<include refid="userRoleDeptSql"/>
		FROM sys_user AS sys_user
					 LEFT JOIN sys_user_role AS ur ON ur.user_id = sys_user.user_id
					 LEFT JOIN sys_role AS r ON r.role_id = ur.role_id
					 LEFT JOIN sys_dept AS d ON d.dept_id = sys_user.dept_id
		WHERE sys_user.user_id = #{id}
	</select>

	<select id="getUserVosPage" resultMap="baseResultMap">
		SELECT sys_user.user_id,
			   sys_user.username,
		       sys_user.nickname,
			   sys_user.salt,
			   sys_user.phone,
			   sys_user.avatar,
			   sys_user.dept_id,
			   sys_user.create_time AS ucreate_time,
			   sys_user.update_time AS uupdate_time,
			   sys_user.del_flag    AS udel_flag,
			   sys_user.lock_flag   AS lock_flag,
			   sys_user.dept_id     AS deptId,
			   sys_user.user_group,
			   sys_user.post,
			   sys_user.email,
			   sys_user.system_flag,
			   sys_user.type,
			   sys_dept.name        AS deptName
		FROM sys_user
					 LEFT JOIN sys_dept ON sys_dept.dept_id = sys_user.dept_id
		<where>
			sys_user.del_flag = '0' and (sys_user.type = '0' or sys_user.type = '1')
			<if test="query.username != null and query.username != ''">
				<bind name="usernameLike" value="'%' + query.username + '%'" />
				and sys_user.username LIKE  #{usernameLike}
			</if>
			<if test="query.nickname != null and query.nickname != ''">
				<bind name="nicknameLike" value="'%' + query.nickname + '%'" />
				and sys_user.nickname LIKE  #{nicknameLike}
			</if>
		</where>
		ORDER BY sys_user.create_time DESC
	</select>

	<select id="selectVoList" resultMap="baseResultMap">
		SELECT u.user_id,
			   u.username,
		       u.nickname,
			   u.password,
			   u.salt,
			   u.phone,
			   u.avatar,
			   u.dept_id,
			   u.create_time ucreate_time,
			   u.update_time uupdate_time,
			   u.del_flag,
			   u.lock_flag,
			   u.group,
			   u.user_group,
			   u.email,
			   u.system_flag,
		       u.type
			   d.name AS     deptName
		FROM sys_user u
					 LEFT JOIN sys_dept d ON d.dept_id = u.dept_id
		<where>
			u.del_flag = '0'
			<if test="query.username != null and query.username != ''">
				<bind name="usernameLike" value="'%' + query.username + '%'" />
				AND u.username LIKE #{usernameLike}
			</if>
			<if test="query.deptId != null and query.deptId != ''">
				AND u.dept_id = #{query.deptId}
			</if>
		</where>
		ORDER BY u.create_time DESC
	</select>

	<insert id="batchInsertUser" parameterType="java.util.List">
	insert into sys_user
	(user_id,username,password,phone,email,nickname,create_time,dept_id,dept_name)
	VALUES
	<foreach collection="list" item="item" index="index" separator=",">
	(
		REPLACE(UUID(),"-",""),
		#{item.uid},
		#{item.password},
		#{item.telephoneNumber},
		#{item.email},
		#{item.personName},
		now(),
		#{item.deptId},
		#{item.deptName}
	)
	</foreach>
	</insert>

	<update id="batchUpdateUser" parameterType="java.util.List">
		<foreach collection="list" index="index" item="i" separator=";">
			update sys_user
			set
			phone = COALESCE(#{i.telephoneNumber}, phone),
			update_time = now(),
			email = COALESCE(#{i.email}, email),
			nickname = COALESCE(#{i.personName}, nickname),
			password = COALESCE(#{i.password}, password),
			dept_id = COALESCE(#{i.deptId}, dept_id),
			dept_name = COALESCE(#{i.deptName}, dept_name)
			where username = #{i.uid}
		</foreach>
	</update>

	<update id="updateUser" parameterType="java.util.List">
		update sys_user
		<trim prefix="set" suffixOverrides=",">
			<trim prefix="dept_id =case" suffix="end,">
				<foreach collection="list" item="i" index="index">
					<if test="i.deptId!=null">
						when username=#{i.userId} then #{i.deptId}
					</if>
				</foreach>
			</trim>
			<trim prefix="update_time =case" suffix="end,">
				<foreach collection="list" item="i" index="index">
					when username=#{i.userId} then now()
				</foreach>
			</trim>
			<trim prefix="dept_name =case" suffix="end,">
				<foreach collection="list" item="i" index="index">
					<if test="i.deptName!=null">
						when username=#{i.userId} then #{i.deptName}
					</if>
				</foreach>
			</trim>
		</trim>
		where
		<foreach collection="list" separator="or" item="i" index="index" >
			username=#{i.userId}
		</foreach>
	</update>
	<!-- 获取b端用户分页 fxj 2022-12-27 -->
	<select id="getBusinessUserVoPage" resultType="com.yifu.cloud.plus.v1.yifu.admin.api.vo.BusinessUserVo">
		select *
		from sys_user AS `user`
		<where> `user`.del_flag = '0'
			<if test="query.deptId != null and query.deptId != ''">
				and `user`.dept_id = #{query.deptId}
			</if>
			<if test="query.type != null and query.type != ''">
				and `user`.type = #{query.type}
			</if>
			<if test="query.nickname != null and query.nickname != ''">
				and `user`.nickname LIKE CONCAT('%',#{query.nickname},'%')
			</if>
			<if test="query.username != null and query.username != ''">
				and `user`.username LIKE CONCAT('%',#{query.username},'%')
			</if>
			<if test="null != query.userIdsB and query.userIdsB.size > 0">
				where  b.user_id in
				<foreach collection="query.userIdsB" item="item" index="index" open="(" close=")" separator=",">
					#{item}
				</foreach>
			</if>
		</where>
		ORDER BY `user`.create_time DESC
	</select>

	<select id="getUserByRoleIdForCrm" resultType="com.yifu.cloud.plus.v1.yifu.admin.api.entity.SysUser">
		SELECT
			*
		FROM
			sys_user
		WHERE
			user_id IN (
				SELECT
					b.user_id
				FROM
					sys_approval_role a
						LEFT JOIN sys_approval_role_user_dept b ON a.role_id = b.approval_role_id
				WHERE
					b.dept_id = #{departId}
				    AND approval_role_id = #{roleId}
			)
	</select>

	<select id="getUserManagerByUpDepartIdUp" resultType="com.yifu.cloud.plus.v1.yifu.admin.api.entity.SysUser">
		SELECT * FROM sys_user WHERE user_id IN(
			SELECT
				user_id
			FROM
				sys_approval_role_user_dept
			WHERE
					dept_id = (
					SELECT
						ORGAN_PARENT
					FROM
						sys_organization_info
					WHERE
						id = #{departId}
						)
			AND approval_role_id = #{roleId}
		)
	</select>

	<select id="selectExitDeptCompany" resultType="java.lang.String">
		SELECT
			dept_dn
		from
			sys_dept where dept_id = #{deptId} limit 1
	</select>


	<select id="getAllUserPage" resultType="com.yifu.cloud.plus.v1.yifu.admin.api.entity.SysUser">
		SELECT user_id,nickname,phone,create_time,username FROM sys_user
		where del_flag = '0' and lock_flag = '0'
		ORDER BY create_time DESC
	</select>

	<select id="getAllUserPageWhere" resultType="com.yifu.cloud.plus.v1.yifu.admin.api.entity.SysUser">
		select u.* from
		(
			SELECT user_id,nickname,phone,create_time,username FROM sys_user
			where del_flag = '0' and lock_flag = '0'
			<if test="query.nameOrPhone != null and query.nameOrPhone != ''">
				<bind name="usernameLike" value="'%' + query.nameOrPhone + '%'"/>
				and nickname LIKE #{usernameLike}
			</if>
			union
			SELECT user_id,nickname,phone,create_time,username FROM sys_user
			where del_flag = '0' and lock_flag = '0'
			<if test="query.nameOrPhone != null and query.nameOrPhone != ''">
				<bind name="usernameLike" value="'%' + query.nameOrPhone + '%'"/>
				and username LIKE #{usernameLike}
			</if>
			union
			SELECT user_id,nickname,phone,create_time,username FROM sys_user
			where del_flag = '0' and lock_flag = '0'
			<if test="query.nameOrPhone != null and query.nameOrPhone != ''">
				and phone = #{query.nameOrPhone}
			</if>
		) u
		ORDER BY u.create_time DESC
	</select>

	<select id="cspUserPage" resultType="com.yifu.cloud.plus.v1.yifu.admin.api.entity.SysUser">
		select u.user_id,u.nickname,u.phone,u.create_time,u.username,p.dept_id,d.`name` as dept_name,p.user_type,u.type from sys_user u
		LEFT JOIN sys_user_dept_permission p on u.user_id=p.user_id
		LEFT JOIN sys_dept d on d.dept_id=p.dept_id
		where
		1=1 and  u.del_flag = '0'
		<if test="nickName != null and nickName != ''">
			and u.nickname LIKE CONCAT('%',#{nickName},'%')
		</if>
		<if test="userName != null and userName != ''">
			and u.username LIKE CONCAT('%',#{userName},'%')
		</if>
		<if test="null != deptId and  deptId != ''">
			and p.dept_id in (
			select  d.dept_id from sys_dept d where
			d.project_no =#{projectNo} and (d.ancestors like CONCAT("0","%",#{deptId},"%") or d.dept_id =#{deptId} )
			)
		</if>

		ORDER BY u.create_time DESC
	</select>
	<select id="getCspUserByDeptId" resultType="java.lang.String">
		SELECT  a.username from sys_user a
		where a.user_id in (
			select  c.user_id from sys_user_dept_permission c
			where c.project_no in (SELECT project_no from sys_user_dept_permission where  dept_id=#{deptId})
		)  and a.del_flag='0'
	</select>

	<select id="getCspUserByProjectNo" resultType="java.lang.String">
		SELECT  a.username from sys_user a
		where a.user_id in (
			select  c.user_id from sys_user_dept_permission c
			where c.project_no in (#{projectNo})
		)  and a.del_flag='0'
	</select>

	<!-- 判断用户是否有菜单 （用在户配置审核人办理人提交时的判断） -->
	<select id="judgeMenuByUserId" resultType="com.yifu.cloud.plus.v1.yifu.admin.api.vo.KeyValueVo">
		select u.user_id keyStr,count(1) valueStr
		from sys_user u
		LEFT JOIN sys_user_role ur on u.user_id=ur.user_id
		LEFT JOIN sys_role r on r.role_id=ur.role_id
		LEFT JOIN sys_role_menu rm on rm.role_id=ur.role_id
		LEFT JOIN sys_menu m on m.menu_id=rm.menu_id
		where u.user_id in
			<foreach collection="idList" item="ids" open="(" separator="," close=")">
				#{ids}
			</foreach>
		and rm.menu_id = #{menuId}
		and u.del_flag ='0' and r.del_flag='0' and m.del_flag='0'
		GROUP BY u.user_id
	</select>

	<!-- 判断用户的数据权限是否有菜单（用在户配置审核人办理人提交时的判断） -->
	<select id="judgeDiyAuthMenuByUserId" resultType="com.yifu.cloud.plus.v1.yifu.admin.api.vo.KeyValueVo">
		select keyStr,count(1) valueStr from (
			select
				a.link_id keyStr
			from sys_data_auth a
			LEFT JOIN sys_diy_sql_menu_rel r on a.id=r.sys_data_auth_id
			where a.link_type = '1' and r.menu_id = #{menuId}
			and a.link_id in
				<foreach collection="idList" item="ids" open="(" separator="," close=")">
					#{ids}
				</foreach>
			union all
			select
				u.user_id keyStr
			from sys_data_auth a
			LEFT JOIN sys_diy_sql_menu_rel r on a.id=r.sys_data_auth_id
			LEFT JOIN sys_user u on u.user_group=a.link_id
			where a.link_type = '0' and r.menu_id = #{menuId}
			and u.user_id in
				<foreach collection="idList" item="ids" open="(" separator="," close=")">
					#{ids}
				</foreach>
		) a
		GROUP BY a.keyStr
	</select>
</mapper>
