<?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="cn.com.ctop.common.module.mapper.SysUserMapper">

    <select id="selectAllUser" resultType="org.jeecg.common.system.entity.SysUser">
		select * from  sys_user
	</select>

    <!-- 根据用户名查询 -->
    <!-- 修改前的版本select * from  sys_user  where username = #{username} and del_flag = 0 -->
    <select id="getUserByName" resultType="org.jeecg.common.system.entity.SysUser">

		SELECT
            t2.*,
            t4.role_name
        FROM
            sys_user t2
        LEFT JOIN sys_user_role t3 ON t2.id = t3.user_id
        LEFT JOIN sys_role t4 ON t3.role_id = t4.id
        WHERE
            t2.username = #{username}
        AND t2.del_flag = 0
	</select>

    <!-- 根据部门Id查询 -->
    <select id="getUserByDepId" resultType="org.jeecg.common.system.entity.SysUser">
        select * from sys_user where del_flag = 0 and id in (select user_id from sys_user_depart where
        dep_id=#{departId})
        <if test="username!=null and username!=''">
            and username = #{username}
        </if>
    </select>

    <!-- 查询用户的所属部门名称信息 -->
    <select id="getDepNamesByUserIds" resultType="cn.com.ctop.common.module.vo.SysUserDepVo">
        select d.depart_name,ud.user_id from sys_user_depart ud,sys_depart d where d.id = ud.dep_id and ud.user_id in
        <foreach collection="userIds" index="index" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
    </select>

    <!-- 通过多个部门IDS，查询部门下的用户信息 -->
    <select id="getUserByDepIds" resultType="org.jeecg.common.system.entity.SysUser">
        select * from sys_user where del_flag = 0
        <if test="departIds!=null  and departIds.size()>0">
            and id in (select user_id from sys_user_depart where dep_id in
            <foreach collection="departIds" index="index" item="id" open="(" separator="," close=")">
                #{id}
            </foreach>
            )
        </if>
        <if test="username!=null and username!=''">
            and username = #{username}
        </if>
    </select>

    <!-- 根据角色Id查询 -->
    <select id="getUserByRoleId" resultType="org.jeecg.common.system.entity.SysUser">
        select * from sys_user where del_flag = 0 and id in (select user_id from sys_user_role where role_id=#{roleId})
        <if test="username!=null and username!=''">
            and username = #{username}
        </if>
    </select>

    <!--  修改用户部门code -->
    <update id="updateUserDepart">
		UPDATE sys_user SET org_code = #{orgCode} where username = #{username}
	</update>

    <!-- 根据手机号查询 -->
    <select id="getUserByPhone" resultType="org.jeecg.common.system.entity.SysUser">
		select * from  sys_user  where phone = #{phone} and del_flag = 0
	</select>

    <!-- 根据realname查询 -->
    <select id="getUserByRealName" resultType="org.jeecg.common.system.entity.SysUser">
        select * from  sys_user  where realname = #{realname} and del_flag = 0 limit 1
    </select>

    <!-- 根据邮箱查询用户信息 -->
    <select id="getUserByEmail" resultType="org.jeecg.common.system.entity.SysUser">
	select * from  sys_user  where email = #{email} and del_flag = 0
	</select>
    <select id="queryRoleCode" parameterType="Object" resultType="java.util.Map">
SELECT
   role_code roleCode,
   role_name roleName,
   id roleId
   FROM
   sys_role
   WHERE
   id = (
   SELECT
   t2.role_id
   FROM
   sys_user t1
   LEFT JOIN sys_user_role t2 ON t1.id = t2.user_id
   WHERE
   t1.id = #{userId,jdbcType=VARCHAR}
   )
</select>
    <select id="getAllUser" resultType="cn.com.ctop.common.module.vo.SysUserVo">
        SELECT
        t1.id userId,
        t1.leader_id,
        t1.username userName,
        t1.realname realName,
        (SELECT role_name FROM sys_role WHERE id = t2.role_id ) roleName
        FROM sys_user t1
        LEFT JOIN sys_user_role t2 on t1.id = t2.user_id
        where 1 = 1
        <if test="userName != null and userName != ''">
            and t1.realname like concat(concat('%',#{userName}),'%')
        </if>
        <if test="status != null and status != ''">
            and t1.status =#{status}
            AND t1.del_flag = 0
        </if>
    </select>
    <!-- SQL片段：getUserByOrgCode 的 FROM 和 WHERE 部分 -->
    <sql id="getUserByOrgCodeFromSql">
        FROM
        sys_depart
        INNER JOIN sys_user_depart ON sys_user_depart.dep_id = sys_depart.id
        INNER JOIN sys_user ON sys_user.id = sys_user_depart.user_id
        WHERE
        sys_user.del_flag = 0 AND sys_depart.org_code LIKE '${orgCode}%'

        <if test="userParams != null">
            <if test="userParams.realname != null and userParams.realname != ''">
                AND sys_user.realname LIKE concat(concat('%',#{userParams.realname}),'%')
            </if>
            <if test="userParams.workNo != null and userParams.workNo != ''">
                AND sys_user.work_no LIKE concat(concat('%',#{userParams.workNo}),'%')
            </if>
        </if>
    </sql>
    <select id="getUserListByCompany" resultType="cn.com.ctop.common.module.vo.SysUserVo">
        SELECT
        t2.id userId,
        t2.username userName,
        t2.realname realName,
        (SELECT role_name FROM sys_role WHERE id = t3.role_id ) roleName
        FROM user_company t1 LEFT JOIN
        sys_user t2 ON
        t1.user_id = t2.id
        LEFT JOIN sys_user_role t3 on t1.user_id = t3.user_id
        WHERE t1.company_id = #{companyId}
        <if test="userName != null and userName != ''">
            and t2.realname like concat(concat('%',#{userName}),'%')
        </if>

    </select>
    <select id="getAllSale" resultType="cn.com.ctop.common.module.vo.SysUserVo">
       SELECT
	t2.id userId,
	t2.username userName,
	t2.realname realName,
   t4.role_name AS roleName
    FROM
	user_company t1
    LEFT JOIN sys_user t2 ON t1.user_id = t2.id
    LEFT JOIN sys_user_role t3 ON t2.id = t3.user_id
    LEFT JOIN sys_role t4 ON t3.role_id = t4.id
    WHERE
	t4.role_name LIKE '%销售%';
    </select>


    <select id="getAllSalesByCompanyId" resultType="cn.com.ctop.common.module.vo.SysUserVo">
       SELECT
	t2.id userId,
	t2.username userName,
	t2.realname realName,
    t4.role_name AS roleName
  FROM
	user_company t1
    LEFT JOIN sys_user t2 ON t1.user_id = t2.id
    LEFT JOIN sys_user_role t3 ON t2.id = t3.user_id
    LEFT JOIN sys_role t4 ON t3.role_id = t4.id
    WHERE t1.company_id = #{companyId}
	and t4.role_name LIKE '%销售%';
    </select>
    <!-- 根据 orgCode 查询用户，包括子部门下的用户 -->
    <select id="getUserByOrgCode" resultType="cn.com.ctop.common.module.model.SysUserSysDepartModel">
        SELECT
        sys_user.id AS id,
        sys_user.realname AS realname,
        sys_user.work_no AS workNo,
        sys_user.post AS post,
        sys_user.telephone AS telephone,
        sys_user.email AS email,
        sys_user.phone AS phone,
        sys_depart.id AS departId,
        sys_depart.depart_name AS departName
        <include refid="getUserByOrgCodeFromSql"/>
        ORDER BY
        sys_depart.org_code ASC
    </select>

    <!-- 查询 getUserByOrgCode 的总数-->
    <select id="getUserByOrgCodeTotal" resultType="java.lang.Integer">
        SELECT COUNT(1)
        <include refid="getUserByOrgCodeFromSql"/>
    </select>

    <!-- 批量删除角色的与用户关系-->
    <update id="deleteBathRoleUserRelation">
        delete from sys_user_role
        where role_id in
        <foreach item="id" collection="roleIdArray" open="(" separator="," close=")">
            #{id}
        </foreach>
    </update>
    <!-- 批量删除角色的与权限关系-->
    <update id="deleteBathRolePermissionRelation">
        delete from sys_role_permission
        where role_id in
        <foreach item="id" collection="roleIdArray" open="(" separator="," close=")">
            #{id}
        </foreach>
    </update>

    <!-- 查询被逻辑删除的用户 -->
    <select id="selectLogicDeleted" resultType="org.jeecg.common.system.entity.SysUser">
		SELECT * FROM sys_user ${ew.customSqlSegment}
	</select>

    <!-- 更新被逻辑删除的用户 -->
    <update id="revertLogicDeleted">
		UPDATE
			sys_user
		SET
			del_flag = 0,
			update_by = #{entity.updateBy},
			update_time = #{entity.updateTime}
		WHERE
			del_flag = 1
			AND id IN (${userIds})
	</update>

    <!-- 彻底删除被逻辑删除的用户 -->
    <delete id="deleteLogicDeleted">
		DELETE FROM sys_user WHERE del_flag = 1 AND id IN (${userIds})
	</delete>

    <!-- 更新空字符串为null -->
    <update id="updateNullByEmptyString">
        UPDATE sys_user SET ${fieldName} = NULL WHERE ${fieldName} = ''
    </update>

    <!-- 通过多个部门IDS，查询部门下的用户信息 -->
    <select id="queryByDepIds" resultType="org.jeecg.common.system.entity.SysUser">
        select * from sys_user where del_flag = 0
        <if test="departIds!=null  and departIds.size()>0">
            and id in (select user_id from sys_user_depart where dep_id in
            <foreach collection="departIds" index="index" item="id" open="(" separator="," close=")">
                #{id}
            </foreach>
            )
        </if>
        <if test="username!=null and username!=''">
            and username != #{username}
        </if>
    </select>

    <select id="selectUserByAccount" resultType="com.alibaba.fastjson.JSONObject">
        SELECT *
        FROM  sys_user t1
        INNER JOIN  ctop_user_allocation t2 ON t1.id = t2.user_id
        WHERE t2.account_id = #{accountId}
    </select>
    <select id="selectAllDesigner" resultType="org.jeecg.common.system.entity.SysUser">
        select user.realname,user.id,role.role_name from sys_user_role userRole left join sys_user user on userRole.user_id = user.id
        left join sys_role role on userRole.role_id = role.id
            where role.role_code in ('plane','plan','shot','clip','designTeamLeader')
            and user.status = 1
    </select>
    <select id="getDesignerTeamLeaderBy" resultType="org.jeecg.common.system.entity.SysUser">
        SELECT
            user2.realname,user2.id
        FROM sys_user user2
        LEFT JOIN sys_user_role userRole ON user2.id = userRole.user_id
        LEFT JOIN sys_role role ON userRole.role_id = role.id
        WHERE
            user2.org_code = #{orgCode}
        AND
            role.role_code = 'designTeamLeader'
    </select>

    <!-- 查询用户列表-->
    <select id="queryPageList" resultType="org.jeecg.common.system.entity.SysUser">
        SELECT t4.* from (
        SELECT t1.*,t3.depart_name ,role.role_name,t2.create_time AS updTime
        FROM sys_user t1
        LEFT JOIN sys_user_depart t2 ON t1.id = t2.user_id
        LEFT JOIN sys_depart t3 ON t2.dep_id = t3.id
        LEFT JOIN sys_user_role userRole ON t1.id = userRole.user_id
        LEFT JOIN sys_role role ON userRole.role_id = role.id
        where t1.del_flag = 0
        <if test="realname != null and realname != ''">
            AND t1.realname LIKE CONCAT(CONCAT('%',#{realname}),'%')
        </if>
        <if test="username != null and username != ''">
            AND t1.username LIKE CONCAT(CONCAT('%',#{username}),'%')
        </if>
        <if test="email != null and email != ''">
            AND t1.email LIKE CONCAT(CONCAT('%',#{email}),'%')
        </if>
        <if test="phone != null and phone != ''">
            AND t1.phone LIKE CONCAT(CONCAT('%',#{phone}),'%')
        </if>
        <if test="status != null and status != ''">
            AND t1.`status` = #{status}
        </if>
        <if test="leaderName != null and leaderName != ''">
            AND t1.leader_name LIKE CONCAT(CONCAT('%',#{leaderName}),'%')
        </if>
        <if test="departId != null and departId != ''">
            AND t3.id =#{departId}
        </if>
        <if test="roleName != null and roleName != ''">
            AND role.role_name LIKE CONCAT(CONCAT('%',#{roleName}),'%')
        </if>
        <if test="sex != null and sex != ''">
            AND t1.sex =#{sex}
        </if>
        <if test="id != null and id != ''">
            AND t1.id =#{id}
        </if>

        ) t4
        GROUP BY t4.id
    </select>
    <select id="getUserMapByMap" resultType="com.alibaba.fastjson.JSONObject">
        select id,realname from sys_user
        where id in
        <foreach collection="ids" item="ids" separator="," open="(" close=")">
            #{ids}
        </foreach>
    </select>




    <!-- 修改用户 设计组长姓名 -->
    <update id="updUserDesignLeaderById">
        UPDATE sys_user SET design_team_leader_name = #{designTeamLeaderName} WHERE id = #{userId}
    </update>

    <select id="getUserIdsByLeaderId" resultType="java.lang.String">
        select id from sys_user
        where leader_id = #{leaderId}
    </select>

    <select id="getDepart" resultType="com.alibaba.fastjson.JSONObject">
        select * from sys_manager_company
        where id = #{companyId}
    </select>

</mapper>
