java-insagent-pc/src/main/java/com/ydtech/modules/esm/dao/EsmUserReferrerMapper.java

package com.ydtech.modules.esm.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.ydtech.modules.admin.model.SysUser;
import com.ydtech.modules.admin.model.dto.EmployeeIncreaseReportDto;
import com.ydtech.modules.admin.model.dto.SysUserDto;
import com.ydtech.modules.admin.model.dto.SysUserTeamDto;
import com.ydtech.modules.admin.model.vo.AppCustomerManagementQueryVo;
import com.ydtech.modules.admin.model.vo.SysReferrerVo;
import com.ydtech.modules.base.model.dto.PageDto;
import com.ydtech.modules.esm.model.EsmUserReferrer;
import com.ydtech.modules.esm.model.QueryChannel;
import com.ydtech.modules.esm.model.vo.EsmTeamPolicyVO;
import com.ydtech.modules.esm.model.vo.EsmUserPolicyForUserVO;
import com.ydtech.modules.esm.model.vo.EsmUserPolicyVO;
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;


@Repository
public interface EsmUserReferrerMapper extends BaseMapper<EsmUserReferrer> {

    String referrerFieldSql = "select " +
            " org.id as orgId" +
            ",org.name as orgName" +
            ",d.name as deptName" +
            ",d.id as deptId" +
            ",u.id as userId" +
            ",u.name as userName" +
            ",u.mobile as userMobile" +
            ",ui.sex  as userSex" +
            ",t.level  as level" +
            ",t.referrer_id  as referrerId" +
            ",t.referrer_name  as referrerName" +
            ",t.affiliation"
            ;

    String referrerFromSql =
            "   from esm_user_referrer t " +
            "	left join sys_user u on t.id = u.id" +
            "	left join sys_user_info ui on u.id = ui.id" +
            "	left join (select name,id from sys_dept where inSTR(id,'D')) d on d.id = u.dept_id" +
            "	left join (select name,id from sys_dept where !inSTR(id,'D')) org on org.id = substring_index(u.dept_id,'D',1)" +
            "	where 1=1 "
            ;

    String referrerWhereSql = "" +
            "<if test='page.dto !=null  '> " +
                "<if test='page.dto.affiliation !=null and page.dto.affiliation!=\"\" '> " +
                    " and t.affiliation = #{page.dto.affiliation}" +
                "</if> " +
                "<if test='page.dto.ids !=null '> " +
                    "and t.id in "+
                    "<foreach item='item' index='index' collection='page.dto.ids'  open='(' separator=',' close=')'> " +
                    " #{item}" +
                    "</foreach > " +
                "</if> " +
            "</if> "
            ;
    String referrerOrderSql = "" +
            " ORDER BY t.level "
            ;









//    String referrerSql = "select org.id as orgId,org.name as orgName,d.name as deptName,d.id as deptId,u.id,u.name,u.mobile,ui.sex ,t.affiliation" +
//            " from esm_user_referrer t " +
//            "	left join sys_user u on t.id = u.id" +
//            "	left join sys_user_info ui on u.id = ui.id" +
//            "	left join (select name,id from sys_dept where inSTR(id,'D')) d on d.id = u.dept_id" +
//            "	left join (select name,id from sys_dept where !inSTR(id,'D')) org on org.id = substring_index(u.dept_id,'D',1)" +
//            "	where t.affiliation=#{affiliation} "
//            ;

    @Select({"<script>",
            "select * from (",
            referrerFieldSql,
            referrerFromSql,
            referrerWhereSql,
            referrerOrderSql,
           // referrerOrderSql,
            " limit #{page.pageNum},#{page.pageSize} ) aaa",
            "</script>"})
    List<SysReferrerVo> selectReferrerByAffiliation(@Param("page") PageDto pageDto);

    /**
     * 向下三级
     * @param userId
     * @return
     */
    @Select("select a.id " +
            "from esm_user_referrer a left join sys_user b on a.id=b.id " +
            "where a.referrer_id=#{userId}  and a.id!= #{userId} and b.status=1 " +
            "union  " +
            "select a.id " +
            "from esm_user_referrer a left join sys_user b on a.id=b.id " +
            "where a.referrer_id in (select id from esm_user_referrer t where t.referrer_id=#{userId}  and t.id!= #{userId}) " +
            "and b.status=1  "
           /* +"union  " +
            "select a.id  " +
            "from esm_user_referrer a left join sys_user b on a.id=b.id " +
            "where a.referrer_id in (select id from esm_user_referrer t where t.referrer_id in ( " +
            "select id from esm_user_referrer t2 where t2.referrer_id=#{userId}  and t2.id!= #{userId})) " +
            "and b.status=1 "*/
    )
    List<String> getIdDown3(@Param("userId") String userId);
    /**
     * 向下三级--新注册人员（审批中）
     * @param userId
     * @return
     */
    @Select("select a.id " +
            "from esm_user_referrer a left join sys_user b on a.id=b.id " +
            "where a.referrer_id=#{userId}  and a.id!= #{userId} and b.status=2 " +
            "union  " +
            "select a.id " +
            "from esm_user_referrer a left join sys_user b on a.id=b.id " +
            "where a.referrer_id in (select id from esm_user_referrer t where t.referrer_id=#{userId}  and t.id!= #{userId}) " +
            "and b.status=2  " )
    List<String> getIdDown3_regist(@Param("userId") String userId);
    /**
     * 向下第1级
     * @param userId
     * @return
     */
    @Select("select a.id " +
            "from esm_user_referrer a left join sys_user b on a.id=b.id " +
            "where a.referrer_id=#{userId} and a.id!= #{userId} "+
            "and b.status in ('0','1') "
           )
    List<String> getIdDown_1(@Param("userId") String userId);
    /**
     * 向下第2级
     * @param userId
     * @return
     */
    @Select( "select a.id " +
            "from esm_user_referrer a left join sys_user b on a.id=b.id " +
            "where a.referrer_id in (select id from esm_user_referrer t where t.referrer_id=#{userId} and t.id!= #{userId} ) "+
            "and b.status in ('0','1')  "
    )
    List<String> getIdDown_2(@Param("userId") String userId);
    /**
     * 向下第3级
     * @param userId
     * @return
     */
    @Select( "select a.id  " +
            "from esm_user_referrer a  left join sys_user b on a.id=b.id " +
            "where a.referrer_id in (select id from esm_user_referrer t where t.referrer_id in ( " +
            "select id from esm_user_referrer t2 where t2.referrer_id=#{userId} and t2.id!= #{userId} )) "+
            "and b.status in ('0','1') "
    )
    List<String> getIdDown_3(@Param("userId") String userId);

    @Select({"<script>", "select count(1) ", referrerFromSql,referrerWhereSql, "</script>"})
    int countReferrerByAffiliation(@Param("page") PageDto pageDto);

    @Select("select id,referrer_id,referrer_name,`level`,affiliation from esm_user_referrer t where t.affiliation=#{affiliation}")
    List<EsmUserReferrer> queryList(@Param("affiliation") String affiliation);

//    @Select({"<script>","select a.userid,a.username,sum(a.ordernum) ordernum,sum(a.policynum) policynum,sum(a.sumpremium) sumpremium from  v_user_order2 a " +
//            "where DATE_FORMAT(orderdate, '%Y-%m-%d') " +
//            "between DATE_FORMAT(#{startDate}, '%Y-%m-%d') and DATE_FORMAT(#{endDate}, '%Y-%m-%d')" +
//            "and userid in " +
//            "<foreach item='item' index='index' collection='userList'  open='(' separator=',' close=')'> " +
//            " #{item}" +
//            "</foreach > " +
//            "group by a.userid,a.username"
//            , "</script>"})
    List<EsmUserPolicyVO> queryOrderCount(List<String> userList, String startDate, String endDate);

//    @Select({"<script>","select sum(sumpremium) sumpremium " +
//            "from  v_user_order2 a " +
//            "where DATE_FORMAT(orderdate, '%Y-%m-%d') " +
//            "between DATE_FORMAT(#{startDate}, '%Y-%m-%d') and DATE_FORMAT(#{endDate}, '%Y-%m-%d')" +
//            "and userid in " +
//            "<foreach item='item' index='index' collection='userList'  open='(' separator=',' close=')'> " +
//            " #{item}" +
//            "</foreach > "
//            , "</script>"})
//    EsmTeamPolicyVO queryOrderCountHz(List<String> userList, String startDate, String endDate);

    EsmTeamPolicyVO queryOrderCountHz(List<String> userList, String startDate, String endDate);

    Page<SysUser> getUserPage(Page page, SysUserDto dto);

    Page<QueryChannel> getQueryChannelPage(Page page, QueryChannel queryChannel);


    List<SysUserTeamDto> getByTeam(@Param("managementQueryVo") AppCustomerManagementQueryVo managementQueryVo, @Param("userId") String userId, @Param("level") int level);

    /**
     *  @version
     *  @author: hxl
     *  @Date: 2024/6/5 18:10
     *  @Description: 查询年分数据
     */
    List<EmployeeIncreaseReportDto> employeeIncreaseReportYear(HashMap<String,Object> map);
    /**
     *  @version
     *  @author: hxl
     *  @Date: 2024/6/5 18:15
     *  @Description: 查询月分数据
     */
    List<EmployeeIncreaseReportDto> employeeIncreaseReportMonth(HashMap<String,Object> map);
    /**
     *  @version
     *  @author: hxl
     *  @Date: 2024/6/5 18:16
     *  @Description: 查询天数数据
     */
    List<EmployeeIncreaseReportDto> employeeIncreaseReportDay(HashMap<String,Object> map);


    List<EsmUserPolicyForUserVO> queryOrderCountForUser(String userId,String startDate, String endDate);
}