<?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.storlead.user.mapper.UserMapper">
	<!-- 获取登录用户拥有的权限 -->

	<!-- 通用查询映射结果 -->
	<resultMap id="BaseResultMap" extends="com.storlead.framework.mybatis.mapper.SysBaseFieldMapper.BaseResultMap" type="com.storlead.user.pojo.entity.UserEntity">
		<id column="id" property="id" />
		<result column="user_name" property="userName" />
		<result column="password" property="password" />
		<result column="xwork_user_id" property="xworkUserId" />
		<result column="avatar" property="avatar" />
		<result column="sex" property="sex" />
		<result column="nick_name" property="nickName" />
		<result column="real_name" property="realName" />
		<result column="mobile" property="mobile" />
		<result column="email" property="email" />
		<result column="job_id" property="jobId" />
		<result column="dept_id" property="deptId" />
		<result column="sub_company_id" property="subCompanyId" />
		<result column="company_id" property="companyId" />
		<result column="id_num" property="idNum" />
		<result column="address" property="address" />
		<result column="birthday" property="birthday" />
		<result column="entry_date" property="entryDate" />
		<result column="permanent_date" property="permanentDate" />
		<result column="resign_date" property="resignDate" />
		<result column="bank_name" property="bankName" />
		<result column="bank_card_num" property="bankCardNum" />
		<result column="status" property="status" />
		<result column="is_shenzhen_census" property="isShenzhenCensus" />
		<result column="census" property="census" />
		<result column="ethnic" property="ethnic" />
		<result column="marriage" property="marriage" />
		<result column="education" property="education" />
		<result column="job_des" property="jobDes" />
		<result column="is_freeze" property="isFreeze" />
		<result column="remark" property="remark" />
		<result column="last_activity_time" property="lastActivityTime" />
		<result column="external_user_type" property="externalUserType" />
	</resultMap>

	<!-- 通用查询结果列 -->
	<sql id="Base_Column_List">
		id, user_name, `password`, data_source,xwork_user_id, avatar, sex, nick_name, real_name, mobile, email, job_id, dept_id, sub_company_id, company_id, id_num
, address, birthday, entry_date,external_user_type, permanent_date, resign_date, bank_name, bank_card_num, status, probation_pay, formal_pay, is_shenzhen_census, census, ethnic, marriage
, education,job_des,is_freeze,last_activity_time,remark, <include refid="com.storlead.framework.mybatis.mapper.SysBaseFieldMapper.Base_Column_List"></include>
	</sql>

	<update id="addUserAvatar">
		update `user` as us left join (
			SELECT m.user_id,ms.color FROM mood m
			WHERE m.date_mood= CURRENT_DATE()
			) as md
		on us.id = md.user_id
		where md.color is not NULL
		  and LOCATE('?color',us.avatar) = 0;
	</update>

    <select id="list" resultType="com.storlead.user.pojo.vo.UserVo">
		SELECT
			u.*,
			j.NAME AS job_name,
			u1.real_name AS manager_name,
		    d.name as dept_name
		FROM
			(select * from `user` as mt ${ew.customSqlSegment}) AS u
				LEFT JOIN job AS j ON j.id = u.job_id
				LEFT JOIN `user` u1 ON u.manager_id = u1.id
				LEFT JOIN `dept` as d ON d.id = u.dept_id
	</select>

	<select id="searchHomeUserList" resultType="com.storlead.user.pojo.vo.UserVo">
		SELECT
			u.*,
			j.NAME AS job_name,
			u1.real_name AS manager_name,
			d.name as dept_name
		FROM
				(select * from `user` ${ew.customSqlSegment}) AS u
					LEFT JOIN job AS j ON j.id = u.job_id
					LEFT JOIN `user` u1 ON u.manager_id = u1.id
					LEFT JOIN `dept` as d ON d.id = u.dept_id
		        order by u.last_activity_time desc,u.id asc
	</select>

	<select id="getUserInfoById" resultType="com.storlead.user.pojo.vo.UserVo">
		SELECT
			u.*,
			j.NAME AS job_name,
			u1.real_name AS manager_name,
			d.name as dept_name,
		       d.dept_job_des
		FROM
			`user` AS u
					LEFT JOIN job AS j ON j.id = u.job_id
					LEFT JOIN `user` u1 ON u.manager_id = u1.id
					LEFT JOIN `dept` as d ON d.id = u.dept_id
			<where>
				<if test="userId !=null" >
					AND u.id = #{userId}
				</if>
			</where>
	</select>

	<select id="queryByUserId" parameterType="Object"  resultType="com.storlead.user.model.UserBaseInfo">
		   SELECT
			u.id AS create_id,
			u.dept_id,
			u.sub_company_id,
			u.company_id,
			u.job_id,
			u.is_freeze,
			u.is_leader,
			u.avatar AS create_photo,
			u.real_name AS create_name,
			d. NAME AS dept_name,
			s.`name` AS sub_company_name,
			c.`name` AS company_name,
			j.`name` AS job_name

		FROM
			USER u
		LEFT JOIN dept d ON u.dept_id = d.id
		LEFT JOIN sub_company s ON s.id = u.sub_company_id
		LEFT JOIN company c ON c.id = u.company_id
		LEFT JOIN job j ON j.id = u.job_id
		WHERE u.enabled = 1
			and u.id = #{userId}
	</select>

	<select id="queryByUserIdList" parameterType="Object"  resultType="com.storlead.user.model.UserBaseInfo">
		   SELECT
			u.id AS create_by,
			u.dept_id,
			u.sub_company_id,
			u.company_id,
			u.is_freeze,
			u.job_id,
			u.is_leader,
			u.avatar AS create_photo,
			u.real_name AS create_name,
			d. NAME AS dept_name,
			s.`name` AS sub_company_name,
			c.`name` AS company_name,
			j.`name` AS job_name
		FROM
			USER u
		LEFT JOIN dept d ON u.dept_id = d.id
		LEFT JOIN sub_company s ON s.id = u.sub_company_id
		LEFT JOIN company c ON c.id = u.company_id
		LEFT JOIN job j ON j.id = u.job_id
		WHERE u.enabled = 1
			and u.id in
		<foreach collection="ids" item="id" index="index" open="(" close=")" separator=",">
			#{id}
		</foreach>
	</select>

	<select id="queryList" parameterType="Object"  resultType="com.storlead.user.model.UserInfo">
		SELECT
			u.id,
			u.user_name,
			u.avatar,
			u.sex,
			u.is_leader,
			u.nick_name,
			u.real_name,
			u.is_freeze,
			u.mobile,
			u.email,
			u.job_id,
			u.dept_id,
			u.company_id,
			u.sub_company_id,
			u. status,
			d. NAME AS dept_name,
			s.`name` AS sub_company_name,
			c.`name` AS company_name,
			j.`name` AS job_name
		FROM
			USER u
		LEFT JOIN dept d ON u.dept_id = d.id
		LEFT JOIN sub_company s ON s.id = u.sub_company_id
		LEFT JOIN company c ON c.id = u.company_id
		LEFT JOIN job j ON j.id = u.job_id
		WHERE
			u.enabled = 1 and u.resign_date is null
		<if test="query.subCompanyId !=null" >
			and u.sub_company_id = #{query.subCompanyId}
		</if>
		<if test="query.deptId !=null" >
			and u.dept_id = #{query.deptId}
		</if>
		<if test="query.keyword !=null and query.keyword !=''" >
			and u.real_name like concat('%',#{query.keyword},'%')
		</if>
	</select>

	<select id="getUserIdsByDeptIds" resultType="java.lang.Long">
		select id from `user` where dept_id IN (
		SELECT d.id
		FROM dept d
		JOIN (select * from dept
		<where>
			<if test="deptIds != null and deptIds.size > 0">
				and id in
				<foreach collection="deptIds" item="deptId" open="(" separator="," close=")">
					#{deptId}
				</foreach>
			</if>
		</where>
		) p
		ON d.route_code LIKE CONCAT(p.route_code, '%')
		where d.is_delete = 0
		)
		AND is_delete = 0 and enabled = 1
	</select>



	<select id="getListByUserRole" resultType="java.lang.Long">
		SELECT u.id
		FROM user u
		WHERE 1=1 AND u.enabled = 1
		<if test="roleType != null">
			<if test="roleType == @com.storlead.sales.common.enums.UserRoleType@SubCompanyLeader">
				AND u.sub_company_id = #{subCompanyId}
			</if>
			<if test="roleType == @com.storlead.sales.common.enums.UserRoleType@Leader">
				AND u.dept_id = #{deptId}
			</if>
			<if test="roleType == @com.storlead.sales.common.enums.UserRoleType@Individuals">
				AND u.id = #{id}
			</if>
		</if>

	</select>
    <select id="getAllSubordinate" resultType="java.lang.Long">
		select id FROM user u WHERE FIND_IN_SET(#{currentUserId}, u.managers)
	</select>


	<select id="selectUserRoleIdsByApiUrl" resultType="java.lang.Long">
		SELECT ur.role_id FROM user_role AS ur
				LEFT JOIN role_menu AS rm
											 ON ur.role_id = rm.role_id
		WHERE ur.user_id = #{userId} AND rm.menu_id = (select id from menu where menu_code = (SELECT menu_code FROM menu_api WHERE api_url =  #{apiUrl}  limit 1))
	</select>

	<select id="selectUserRoleIdsByMenuId" resultType="java.lang.Long">
		SELECT ur.role_id FROM user_role AS ur
								   LEFT JOIN role_menu AS rm
											 ON ur.role_id = rm.role_id
		WHERE ur.user_id = #{userId}
		<if test="menuId != null">
			AND rm.menu_id = #{menuId}
		</if>
	</select>


	<select id="selectUserRoleIdsByCommonScopeType" resultType="java.lang.Long">
		SELECT ur.role_id FROM user_role AS ur
		LEFT JOIN role_menu AS rm
		ON ur.role_id = rm.role_id
		WHERE ur.user_id = #{userId}
		<if test="commonScopeType != null">
			AND rm.menu_id in (select id from menu where remark = #{commonScopeType})
		</if>
	</select>

	 <update id="updateLastFollowUpTime">
		 UPDATE customer set last_follow_up_time = NOW() , no_follow_up_date = 0
		 <if test="ownerByIds != null and ownerByIds.size > 0">
			 and owner_by in
			 <foreach collection="ownerByIds" item="ownerById" open="(" separator="," close=")">
				 #{ownerById}
			 </foreach>
		 </if>
	 </update>
</mapper>
