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.QuTeamNumDto;
import com.ydtech.modules.admin.model.dto.QuUserNumDto;
import com.ydtech.modules.admin.model.dto.ReportDto;
import com.ydtech.modules.admin.model.dto.SysUserDto;
import com.ydtech.modules.admin.model.vo.SysUserBaseVO;
import com.ydtech.modules.admin.model.vo.SysUserVO;
import com.ydtech.modules.admin.model.vo.SysUserVOExt;
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;

/**
 * @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 " +
            "            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);

    @Select("SELECT u.id,u.name,r.id roleId,r.name roleName 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 ('21') and u.status=1 and u.dept_id=#{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);

    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);

    /**
     * 获取用户列表
     * @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, Integer> 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, Integer> findQXTDSumCountAndAvgAge(@Param("deptId") String deptId);
    /**
     *  @version
     *  @author: hxl
     *  @Date: 2024/6/28 10:48
     *  @Description: 查询代理人总数
     */
    HashMap<String, Integer> findQXDLSumCountAndAvgAge(@Param("deptId") String deptId,@Param("type") String type);
}