java-insagent-pc/src/main/java/com/ydtech/modules/admin/dao/SysUserMapper.java

package com.ydtech.modules.admin.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.ydtech.modules.admin.model.QuTeamNum;
import com.ydtech.modules.admin.model.QuUserNum;
import com.ydtech.modules.admin.model.SysUser;
import com.ydtech.modules.admin.model.dto.*;
import com.ydtech.modules.admin.model.report.user.QianLineQDPersonnelReportDto;
import com.ydtech.modules.admin.model.vo.*;
import com.ydtech.modules.base.model.dto.PageDto;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author Administrator
 * @description 针对表【sys_user(用户管理)】的数据库操作Mapper
 * @createDate 2022-08-17 11:41:01
 * @Entity generator.domain.SysUser
 */
@Repository
public interface SysUserMapper extends BaseMapper<SysUser> {

    String userSql = "select t1.id as userId,t1.name as name,t1.mobile as mobile,t1.`status` as `status`,t2.id as deptId,t2.`name` as deptName,sd2.id as pDeptId,sd2.`name` as pDeptName,t4.id as roleId,t4.`name` as roleName ,t1.create_time as createTime " +
            "            from sys_user t1 " +
            "			 left join sys_dept t2 on t1.dept_id=t2.id " +
            "            left join sys_dept sd2 on sd2.id=t2.parent_id  " +
            "            left join sys_user_role t3 on t1.id=t3.user_id" +
            "			 left join sys_role t4 on t3.role_id=t4.id" +
            " where t1.id=#{userId}";
    ;

    String internalUserSql = "select t.id,t.name,t.dept_id deptid,t2.name deptname,t3.id comid,t3.name comname,t.status   " +
            "from sys_user t left join sys_dept t2 on t.dept_id=t2.id " +
            "left join sys_dept t3 on t2.parent_id=t3.id " +
            "left join esm_user_internal a on t.id=a.id " +
            "where t.id=#{userId}";

    String internalUserByDeptIdSql = "select t1.id,t1.name,t1.dept_id deptid,sd.name deptname,sd2.id comid,sd2.name comname,t2.status " +
            "from sys_user t1 left join esm_user_internal t2 on t1.id=t2.id " +
            "LEFT JOIN sys_dept sd ON t1.dept_id=sd.id " +
            "LEFT JOIN sys_dept sd2 ON sd2.id=sd.parent_id " +
            "where 1=1  and t2.isteamleader = '1' and t1.dept_id=#{deptId}";


    String selectUserParamSql = "select su.id as userId,su.name,info.sex,su.mobile,su.status" +
            ",sd.id as deptId,sd.name as deptName" +
            ",psd.id as pDeptId,psd.name as pDeptName" +
            ",t4.id as roleId" +
            ",t4.name as roleName";


    String selectUserFromSql = " from sys_user su " +
            " left join sys_dept sd on su.dept_id=sd.id " +
            " left join sys_dept psd on sd.parent_id=psd.id " +
            " left join sys_user_info info on info.id=su.id " +
            " left join sys_user_role t3 on su.id=t3.user_id " +
            " left join sys_role t4 on t3.role_id=t4.id " +
            " where 1=1 " +
            " AND sd.id &lt;&gt; '' "
            + "";

    String selectUserFromWhereSql = " " +
//            "<if test='page.columnFilters.usertype !=null and page.columnFilters.usertype.value!=\"\" and page.columnFilters.usertype.value!=null '>" +
//            "  and su.usertype = #{page.columnFilters.usertype.value}" +
//            "</if> " +
//            "<if test='page.columnFilters.approvalStatus !=null and page.columnFilters.approvalStatus.value!=\"\" and page.columnFilters.approvalStatus.value!=null '>" +
//            " and su.approval_status = #{page.columnFilters.approvalStatus.value}" +
//            "</if> " +
            "<if test='page.dto !=null  '> " +
            "<if test='page.dto.status !=null and page.dto.status!=\"\" '> " +
            " and su.status = #{page.dto.status}" +
            "</if> " +
            "<if test='page.dto.name !=null and page.dto.name!=\"\" '> " +
            " and su.name like concat('%',#{page.dto.name},'%')" +
            "</if> " +
            "<if test='page.dto.userId !=null and page.dto.userId!=\"\" '>" +
            " and su.id = #{page.dto.userId}" +
            "</if> " +
            "<if test='page.dto.mobile !=null and page.dto.mobile!=\"\"  '>" +
            " and su.mobile = #{page.dto.mobile}" +
            "</if> " +
            "<if test='page.dto.deptId !=null and page.dto.deptId!=\"\"  '>" +
            " and su.dept_id like concat('%',#{page.dto.deptId},'%')" +
            "</if> " +
            "<if test='page.dto.roleId !=null and page.dto.roleId!=\"\"  '>" +
            " and t3.role_id = #{page.dto.roleId}" +
            "</if> " +
            "</if> " +
            "";


//    String selectUserFromWhereSql = " " +
//            " and su.usertype = #{page.columnFilters.usertype.value}" +
//            " and su.status = #{page.columnFilters.status.value}" +
//            " and su.name like concat('%',#{page.columnFilters.name.value},'%')" +
//            " and su.id = #{page.columnFilters.userId.value}" +
//            " and su.mobile = #{page.columnFilters.mobile.value}" +
//            "";


    @Select(internalUserSql)
    SysUserVO gainInternalUser(@Param("userId") String userId);


    @Select(internalUserByDeptIdSql)
    SysUserVO gainInternalUserByDeptId(@Param("deptId") String deptId);


    List<SysUserVO> findByReferrerId(@Param("deptId") String deptId);


    @Select("SELECT u.id,u.name FROM sys_user u " +
            "LEFT JOIN sys_user_role sur ON sur.user_id = u.id " +
            "LEFT JOIN sys_role r ON r.id = sur.role_id WHERE r.id IN ('22') and u.status=1 and u.id NOT LIKE '%M%'")
    List<SysUser> findBackListByDeptId(@Param("deptId") String deptId);

    List<SysUserVO> findListByDeptId(@Param("sysUser") SysUser sysUser);

    List<SysUserVO> findListByDeptIdNew(@Param("sysUser") SysUser sysUser);

    Page<SysUserBaseVO> gainUserBaseInfo(@Param("page") Page page, @Param("user") SysUserDto user);

    IPage<SysUserBaseVO> findUserList(@Param("page") Page page, @Param("user") SysUserDto user);

    IPage<SysUserBaseVO> findAllUserList(@Param("page") Page page, @Param("user") SysUserDto user);

    IPage<SysUserBaseVO> findBackUserList(@Param("page") Page page, @Param("user") SysUserDto user);

    List<SysUserBaseVO> findBackUserList1(@Param("user") SysUserDto user);

    /**
     * 获取用户列表
     *
     * @param page            分页信息
     * @param sysUser         查询条件
     * @param containsSubDept 是否包含下级机构
     * @return
     */
    public IPage<SysUser> selectUserPage(Page page, @Param("sysUser") SysUser sysUser, @Param("containsSubDept") String containsSubDept);


    //    @Select("select count(1) " + selectUserFromSql + selectUserFromWhereSql)
    @Select({"<script>", "select count(1) ", selectUserFromSql, selectUserFromWhereSql, "</script>"})
    int gainUserBaseInfoCount(@Param("page") PageDto pageDto);


    @Select(userSql)
    SysUserBaseVO selectUserById(@Param("userId") String userId);

    String selectSql = "SELECT u.* FROM sys_user u " +
            "LEFT JOIN sys_user_role sur ON sur.user_id = u.id " +
            " WHERE sur.role_id = #{roleId} and u.status=1 " +
            "<if test='userId!=\"\" '>" +
            "   and u.id=#{userId} " +
            "</if> ";

    @Select({"<script>", selectSql, "</script>"})
    List<SysUser> findAllAgentRoleUser(@Param("userId") String userId, @Param("roleId") String roleId);

    List<QuTeamNum> getDept(@Param("quTeamNumDto") QuTeamNumDto quTeamNumDto);

    List<QuTeamNum> getDeptByCompany(@Param("quTeamNumDto") QuTeamNumDto quTeamNumDto);

    List<QuTeamNum> getUser(@Param("quTeamNumDto") QuTeamNumDto quTeamNumDto);

    List<QuUserNum> getUserNum(@Param("quUserNumDto") QuUserNumDto quUserNumDto);

    List<QuUserNum> getMainWaitAuditSum(@Param("quUserNumDto") QuUserNumDto quUserNumDto);

    List<QuUserNum> getMainWaitPaySum(@Param("quUserNumDto") QuUserNumDto quUserNumDto);

    List<QuUserNum> getMainAcceptInsuranceSum(@Param("quUserNumDto") QuUserNumDto quUserNumDto);

//    List<QuUserNum> getMainOrderDetails(@Param("quUserNumDto") QuUserNumDto quUserNumDto);

    List<QuUserNum> getUserByCompany(@Param("quUserNumDto") QuUserNumDto quUserNumDto);

    List<QuUserNum> getComMainWaitAuditSum(@Param("quUserNumDto") QuUserNumDto quUserNumDto);

    List<QuUserNum> getComMainWaitPaySum(@Param("quUserNumDto") QuUserNumDto quUserNumDto);

    List<QuUserNum> getComMainAcceptInsuranceSum(@Param("quUserNumDto") QuUserNumDto quUserNumDto);

    List<QuUserNum> getUserDetail(@Param("quUserNumDto") QuUserNumDto quUserNumDto);

    List<QuUserNum> getUserMainWaitAuditSum(@Param("quUserNumDto") QuUserNumDto quUserNumDto);

    List<QuUserNum> getUserMainWaitPaySum(@Param("quUserNumDto") QuUserNumDto quUserNumDto);

    List<QuUserNum> getUserMainAcceptInsuranceSum(@Param("quUserNumDto") QuUserNumDto quUserNumDto);

    /**
     * @version
     * @author: hxl
     * @Date: 2024/4/15 17:51
     * @Description: 添加查询后线人员
     */
    String findQXUserSql = " SELECT  t1.id AS userId,t1.NAME AS NAME,t1.mobile AS mobile,t1.`status` AS `status`,t2.id AS deptId,t2.`name` AS deptName,sd2.id AS pDeptId,sd2.`name` AS pDeptName  " +
            " FROM sys_user t1 LEFT JOIN sys_dept t2 ON t1.dept_id = t2.id LEFT JOIN sys_dept sd2 ON sd2.id = t2.parent_id " +
            " WHERE t1.`status` = 1 AND t1.id not LIKE '%M%' " +
            "<if test='userId!=\"\" and userId!=null '>" +
            "   AND t1.id=#{userId} " +
            "</if> ";

    @Select({"<script>", findQXUserSql, "</script>"})
    List<SysUserBaseVO> findQXUserList(@Param("userId") String userId);

    /**
     * @version
     * @author: hxl
     * @Date: 2024/4/16 16:25
     * @Description: 通过工号、姓名、手机号查询工号信息
     */
    List<SysUserBaseVO> getAllUsersByHospitality(@Param("user") SysUserVOExt sysUserVOExt);

    /**
     * @version
     * @author: hxl
     * @Date: 2024/4/16 16:25
     * @Description: 查询领导人为用户id的数据
     */
    @Select(" SELECT  t1.id AS userId,t1.NAME AS NAME,t1.mobile AS mobile,t1.`status` AS `status`,t2.id AS deptId,t2.`name` AS deptName,sd2.id AS pDeptId,sd2.`name` AS pDeptName  " +
            " FROM sys_user t1 LEFT JOIN sys_dept t2 ON t1.dept_id = t2.id LEFT JOIN sys_dept sd2 ON sd2.id = t2.parent_id " +
            " WHERE t1.`status` = 1 AND t1.leader_id = #{userId}")
    List<SysUserBaseVO> getAllUsersByLeaderId(@Param("userId") String userId);

    /**
     * @version
     * @author: hxl
     * @Date: 2024/4/17 16:55
     * @Description: 查询所有的代理人
     */
    @Select(" SELECT su.id FROM  sys_user su  LEFT JOIN sys_user_info info ON info.id = su.id  LEFT JOIN sys_dept sd ON su.dept_id = sd.id " +
            " LEFT JOIN sys_dept psd ON psd.id = sd.parent_id   LEFT JOIN sys_user_role t3 ON su.id = t3.user_id  LEFT JOIN sys_role t4 ON t3.role_id = t4.id  " +
            " AND sd.del_flag = '0'  and su.status = 1 " +
            " WHERE  su.dept_id LIKE concat( '%', 9, '%' )    AND t3.role_id IN ( 20 )  AND sd.management_source = 2")
    List<String> getAgentUserIds();

    /**
     * @version
     * @author: hxl
     * @Date: 2024/4/18 16:15
     * @Description: 获取客服人员数据
     */
    List<SysUserBaseVO> getCustomerUsers(@Param("user") SysUserVOExt sysUserVOExt);

    /**
     * @version
     * @author: hxl
     * @Date: 2024/4/23 10:49
     * @Description: 通过用户id和角色ids查询机构ids
     */
    List<String> getDeptIdsByRoleIdsAndUserIds(@Param("userIds") List<String> agentUserIds, @Param("roleIds") List<String> roleIds);

    /**
     * @version
     * @author: hxl
     * @Date: 2024/4/23 11:39
     * @Description: 查询后线人员+分页
     */
    Page<SysUserBaseVO> findUserListByPage(@Param("page") Page<SysUserBaseVO> page, @Param("user") SysUserDto sysUserDto);

    /**
     * @version
     * @author: hxl
     * @Date: 2024/4/16 16:25
     * @Description: 查询领导人为用户id的数据ids数据
     */
    @Select(" SELECT  t1.id  FROM sys_user t1 WHERE t1.`status` = 1 AND t1.leader_id = #{userId}")
    List<String> getUserIdsByLeaderId(@Param("userId") String userId);

    /**
     * @version
     * @author: hxl
     * @Date: 2024/4/25 8:50
     * @Description: 通过当前的用户查询渠道下的团队长的的管理的部门ids
     */
    List<String> getDeptIdsByUserIds(@Param("userId") String userId);

    /**
     * @version
     * @author: hxl
     * @Date: 2024/4/26 17:25
     * @Description: 查询用户分页+区域标志显示
     */
    Page<SysUserBaseVO> findUserListByAreaPage(@Param("page") Page<SysUserBaseVO> page, @Param("user") SysUserDto sysUserDto);

    /**
     * @version
     * @author: hxl
     * @Date: 2024/4/27 09:55
     * @Description: 查询所有的代理人
     */
    List<SysUserBaseVO> getAgentUserList();


    List<SysUserBaseVO> getAgentUserIdsByAreaIds();

    /**
     * @version
     * @author: hxl
     * @Date: 2024/5/7 10:45
     * @Description: 通过角色id和部门id查询是否存在团队长
     */
    int getCountByDeptAndRoleId(@Param("user") SysUser sysUserVo);

    /**
     * @version
     * @author: hxl
     * @Date: 2024/5/7 11:46
     * @Description: 查询该部门的渠道团队长
     */
    String findTDZUserIdByDeptId(@Param("deptId") String deptId);

    /**
     * @version
     * @author: hxl
     * @Date: 2024/6/28 9:34
     * @Description: 通过机构查询统计数据
     */
    HashMap<String, String> findSumCountAndAvgAge(@Param("deptId") String deptId);

    /**
     * @version
     * @author: hxl
     * @Date: 2024/6/28 9:48
     * @Description: 查询某个部门的学历分布
     */
    List<ReportDto> findEducationSumCount(@Param("deptId") String deptId);

    /**
     * @version
     * @author: hxl
     * @Date: 2024/6/28 10:35
     * @Description: 查询团队长的总人数和平均年龄
     */
    HashMap<String, String> findQXTDSumCountAndAvgAge(@Param("deptId") String deptId);

    /**
     * @version
     * @author: hxl
     * @Date: 2024/6/28 10:48
     * @Description: 查询代理人总数
     */
    HashMap<String, String> findQXDLSumCountAndAvgAge(@Param("deptId") String deptId, @Param("type") String type);

    /**
     * @version
     * @author: hxl
     * @Date: 2024/7/5 16:39
     * @Description: 查询团队长区域分布
     */
    List<ReportDto> findQXTDAreaSumCount(@Param("deptId") String deptId);

    /**
     * @version
     * @author: hxl
     * @Date: 2024/7/5 16:43
     * @Description: 查询代理人的区域分布
     */
    List<ReportDto> findQXDLAreaSumCount(@Param("deptId") String deptId);

    /**
     * @version
     * @author: hxl
     * @Date: 2024/7/5 16:47
     * @Description: 查询团队张的性别分布
     */
    List<ReportDto> findQXTDSexSumCount(@Param("deptId") String deptId);

    /**
     * @version
     * @author: hxl
     * @Date: 2024/7/5 16:54
     * @Description: 查询代理人的性别分布
     */
    List<ReportDto> findQXDLSexSumCount(@Param("deptId") String deptId);

    /**
     * @version
     * @author: hxl
     * @Date: 2024/7/5 16:56
     * @Description: 团队长维护人分布
     */
    List<ReportDto> findQXTDWhSumCount(@Param("deptId") String deptId);

    /**
     * @version
     * @author: hxl
     * @Date: 2024/7/5 17:04
     * @Description: 代理人维护人分布
     */
    List<ReportDto> findQXDLWhSumCount(@Param("deptId") String deptId);

    /**
     * @version
     * @author: hxl
     * @Date: 2024/7/5 17:11
     * @Description: 查询渠道数据
     */
    List<QianLineQDPersonnelReportDto> queryQXQDReportData(@Param("deptId") String deptId);

    /**
     * @version
     * @author: hxl
     * @Date: 2024/7/5 17:41
     * @Description: 查询该机构下的代理人
     */
    List<SysUser> findQXDLUserList(@Param("deptId") String deptId, @Param("type") String type);

    /**
     * @version
     * @author: hxl
     * @Date: 2024/7/11 16:09
     * @Description: 查询所有的后线人员
     */
    List<SysUser> findHxUserList();

    @Select("select id   from  sys_user  where   status ='1' ")
    List<String> getUserIds();

    HashMap<String, Object> queryHXReportDataNew(@Param("vo") HouLinePersonnelReportVo houLinePersonnelReportVo);

    IPage<QxFrontlineAgentDto> queryQxFrontlineAgent(@Param("page") Page page, @Param("vo") QxFrontlineAgentVo qxFrontlineAgentVo);

    IPage<QxFrontlineAgentDto> getByIdsList(@Param("page") Page page, @Param("vo") QxFrontlineAgentVo qxFrontlineAgentVo);


    SysUser getByUserId(String userId);

    /**
     * 获取电销人员的用户名
     *
     * @return 电销人员工号列表
     */
    List<String> getTelemarketingPersonnelUserId();

    /**
     * @version
     * @author: hxl
     * @Date: 2024/8/29 17:52
     * @Description: 按照省市区查询数据
     */
    List<QxFrontlineAgentDto> queryQxFrontlineAgentList(@Param("vo") QxFrontlineAgentVo qxFrontlineAgentVo);

    /**
     * @version
     * @author: hxl
     * @Date: 2024/8/30 18:47
     * @Description: 分页查询人员数据
     */
    Page<QxFrontlineAgentDto> findUserListByParam(@Param("page") Page page, @Param("vo") QxFrontlineAgentVo qxFrontlineAgentVo);

    /**
     * @version
     * @author: hxl
     * @Date: 2024/8/30 18:47
     * @Description: 更新
     */
    Integer updateManagementPersonnel(@Param("managementDto") UpdateManagementDto managementDto);


    List<SysUser> getAllAgent(String pid, List<String> ids);

    /**
     * @version
     * @author: hxl
     * @Date: 2024/9/24 17:35
     * @Description: 查询电销的管理人列表
     */
    List<SysUser> findDxUserList();

    /**
     * 根据部门获取代理人渠道数
     *
     * @param sysUserDeptRelationshipVo
     * @return
     */
    Page<SysUser> findQXDLUserListNew(@Param("page") Page page, @Param("vo") SysUserDeptRelationshipVo sysUserDeptRelationshipVo);


    Page<SysUser> getChanelList(@Param("page") Page page, @Param("vo") SysUserDeptRelationshipVo sysUserDeptRelationshipVo);

    List<SysUser> selectByParams(HashMap<String, Object> params);

    IPage<SysUserBaseVO> selectForAgentsAndChannelsPage(@Param("page") Page page, @Param("sysUserVO") SysUserDto sysUserVO);

    List<Map<String, String>> selectHxUserList(@Param("sysUserDto") SysUserDto sysUserDto);

    Integer cancelManagementPersonnel(@Param("managementDto") UpdateManagementDto managementDto);

    /**
     * @version
     * @author: hxl
     * @Date: 2024/9/25 19:25
     * @Description: 通过用户id查询电销组下的用户id
     */
    List<String> getGroupByUserId(@Param("userId") String userId);

    /**
     * @version
     * @author: hxl
     * @Date: 2024/9/26 18:11
     * @Description: 通过机构id查询后线用户数据
     */
    List<SysUser> getLeadListDeptId(@Param("deptId") String deptId);

    List<SysUser> selectByLeadId(String userId);

    List<SysUser> querySourceId(String id);

    List<QxFrontlineAgentDto> getQxFrontlineAgentList(@Param("vo") QxFrontlineAgentVo qxFrontlineAgentVo);

    List<SysUser> findHxUserListNew();


    AppUserInfoVo queryByUserId(@Param("userId") String userId);

    List<SysUser> selectByParamsTreeUser(HashMap<String, Object> params);

    /**
     * 通过 机构id 和 角色id查询用户
     *
     * @param roleId 角色id
     * @param deptId 机构id
     * @return 用户信息
     */
    SysUser getByRoleIdAndDeptId(@Param("roleId") String roleId, @Param("deptId") String deptId);

    /**
     * 根据登陆者 id deptId 查询
     */
    List<SysUser> selectLeaderBylanderInfo(@Param("deptId") String deptId, @Param("leaderId") String leaderId, @Param("userId") String userId);

    /**
     * 向上查询领导人
     *
     * @param userId
     * @return
     */
    List<SysUser> queryUpTheLeader(@Param("userId") String userId);


    /**
     * 根据分组查询
     *
     * @param userId 用户id
     * @return 分组用户
     */
    List<SysUser> queryByBusinessGrouping(@Param("userId") String userId);

    /**
     * @version
     * @author: hxl
     * @Date: 2024/9/24 17:35
     * @Description: 查询电销的管理人列表
     */
    List<SysUser> findDxZyUserList();

    List<SysUser> selectByUserId(List<String> ids);

    /**
     * @version
     * @author: hxl
     * @Date: 2024/12/25 18:28
     * @Description: 查询所有的人员
     */
    Page<SysUserBaseVO> findAllUsers(@Param("page") Page<SysUserBaseVO> page, @Param("user") SysUserDto sysUserDto);


    Page<SysUserBaseVO> findPartnerUserList(@Param("page") Page<SysUserBaseVO> page, @Param("user") SysUserDto user);

    @Select(" SELECT id FROM sys_user WHERE `status` = 1 and internal_id = 1 ")
    List<String> queryInternalNumber();

    @Select("SELECT COUNT(r.role_id) FROM sys_user u LEFT JOIN sys_user_role r ON u.id = r.user_id WHERE u.mobile = #{mobile} AND r.role_id = #{roleId}")
    int countTeamLeader(@Param("mobile")String mobile,@Param("roleId") String roleId);
}