package com.zanxiang.game.data.serve.service.impl;

import com.alibaba.fastjson2.JSON;
import com.zanxiang.erp.base.ErpServer;
import com.zanxiang.erp.base.rpc.ISysUserRpc;
import com.zanxiang.erp.security.util.SecurityUtil;
import com.zanxiang.game.data.serve.pojo.dto.*;
import com.zanxiang.game.data.serve.pojo.enums.OrderByEnum;
import com.zanxiang.game.data.serve.pojo.vo.RoleChatDetailVO;
import com.zanxiang.game.data.serve.pojo.vo.RoleChatVO;
import com.zanxiang.game.data.serve.pojo.vo.RoleGameCountryVO;
import com.zanxiang.game.data.serve.service.IRoleManageService;
import com.zanxiang.game.data.serve.utils.Page;
import com.zanxiang.game.module.base.ServerInfo;
import com.zanxiang.game.module.base.pojo.dto.SendMsgDTO;
import com.zanxiang.game.module.base.pojo.params.SendMsgTaskParam;
import com.zanxiang.game.module.base.pojo.params.SendMsgTaskResultParam;
import com.zanxiang.game.module.base.pojo.vo.SendMsgResultVO;
import com.zanxiang.game.module.base.pojo.vo.SendMsgVO;
import com.zanxiang.game.module.base.rpc.ICPSendMsgRpc;
import com.zanxiang.game.module.base.util.PageUtil;
import com.zanxiang.module.util.exception.BaseException;
import com.zanxiang.module.util.pojo.ResultVO;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.dubbo.config.annotation.DubboReference;
import org.nutz.dao.Cnd;
import org.nutz.dao.Dao;
import org.nutz.dao.Sqls;
import org.nutz.dao.pager.Pager;
import org.nutz.dao.sql.Criteria;
import org.nutz.dao.sql.Sql;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Lazy;
import org.springframework.stereotype.Service;
import org.springframework.util.StopWatch;

import javax.annotation.Resource;
import java.time.LocalTime;
import java.util.*;
import java.util.function.Function;
import java.util.stream.Collectors;

/**
 * @author tianhua
 * @time 2023/9/8
 * @Description 角色运营管理逻辑处理
 **/
@Service
@Slf4j
public class RoleManageServiceImpl implements IRoleManageService {

    @Autowired
    private Dao dao;

    @DubboReference(providedBy = ErpServer.SERVER_DUBBO_NAME)
    private ISysUserRpc sysUserRpc;

    @DubboReference(providedBy = ServerInfo.SERVER_DUBBO_NAME)
    private ICPSendMsgRpc sendMsgRpc;

    @Resource
    @Lazy
    private IRoleManageService baseService;


    /**
     * 角色充值排行榜
     *
     * @param dto RoleRechargeRankingDTO
     * @return Page<List < Map < String, Object>>>
     */
    @Override
    public Page<Map> getRoleRechargeRanking(RoleRechargeRankingDTO dto) {
        //创建查询条件 给主表使用
        Criteria criA = getSqlByQuery(dto);
        //给充值时间查询条件
        Criteria criTodayAmount = getDateSqlByQuery(dto);
        //判断是否合服 默认不合服
        String margeSql = "is_merge = 0";
        if (dto.getIsMergeServer()) {
            margeSql = "is_source_server = 0";
        }
        //分页对象
        Pager pager = dao.createPager(dto.getPageNum(), dto.getPageSize());

        StopWatch watch = new StopWatch();
        watch.start();
        //查询总记录数
        Sql countSql = Sqls.create(getCountNumSql2(criA, criTodayAmount, margeSql));
        countSql.setCallback(Sqls.callback.integer());
        dao.execute(countSql);
        watch.stop();

        pager.setRecordCount(countSql.getInt());
        //主表添加排序条件
        if (StringUtils.isBlank(dto.getSortType())) {
            dto.setSortType(OrderByEnum.DESC.getOrderType());
        }
        if (StringUtils.isBlank(dto.getSortFiled())) {
            criA.getOrderBy().orderBy("amount", dto.getSortType());
        } else {
            criA.getOrderBy().orderBy(dto.getSortFiled(), dto.getSortType());
        }
        //创建sql
        StopWatch watch2 = new StopWatch();
        watch2.start();
        String roleRechargeRankingSql = getRoleRechargeRankingSql(criA, criTodayAmount, margeSql);
        log.error("------sql:" + roleRechargeRankingSql);
        Sql sql = Sqls.create(roleRechargeRankingSql);
        sql.setCallback(Sqls.callback.maps());
        sql.setPager(pager);
        dao.execute(sql);
        watch2.stop();
        //查询结果
        List<Map> list = sql.getList(Map.class);
        //用户id列表
        List<Long> userIds = new ArrayList<>();
        for (Map map : list) {
            //把投手id和运营id和GSID和客服人员ID 转成List
            List<String> keys = Arrays.asList("put_user_id", "oper_user_id", "gsId", "customer_service_id");
            for (String key : keys) {
                if (map.get(key) != null) {
                    userIds.add(Long.valueOf(map.get(key).toString()));
                }
            }
            //去掉字符串中的‘[]’
            String str = (String) map.get("role_amount");
            if (str != null && str.contains("[") && str.contains("]")) {
                map.put("role_amount", str.replaceAll("[\\[\\]]", ""));
            }
            //去除‘null’字符串
            List<String> nullStringKeys = Arrays.asList("add_corp_user_id", "user_wechat", "remark", "user_phone", "country", "corpId", "external_user_id", "gsAccount");
            nullStringKeys.stream()
                    .filter(key -> "null".equals(map.get(key)))
                    .forEach(key -> map.put(key, null));
            if (map.get("is_remove_game") != null && map.get("is_remove_game").equals(2)) {
                map.put("is_remove_game", null);
            }
            if (map.get("is_change_game_type") != null && map.get("is_change_game_type").equals(2)) {
                map.put("is_change_game_type", null);
            }
            if (map.get("is_add_corp_wechat") != null && map.get("is_add_corp_wechat").equals(2)) {
                map.put("is_add_corp_wechat", null);
            }
            if (map.get("is_wake_up") != null && map.get("is_wake_up").equals(2)) {
                map.put("is_wake_up", null);
            }
        }
        //去重userIds
        userIds = userIds.stream().distinct().collect(Collectors.toList());
        //发送RPC接口查询所有用户
        ResultVO<Map<Long, String>> userMap = sysUserRpc.getUserNameByIds(userIds);

        for (Map map : list) {
            //投手名
            updateUserName(map, "put_user_id", "put_user_name", userMap);
            //运营人员名
            updateUserName(map, "oper_user_id", "oper_user_name", userMap);
            //GS人员名
            updateUserName(map, "gsId", "gs_name", userMap);
            //客服人员名
            updateUserName(map, "customer_service_id", "customer_service_name", userMap);
        }
        //返回结果
        log.info("查询总记录数耗时：{}", watch.getTotalTimeMillis());
        log.info("查询数据耗时：{}", watch2.getTotalTimeMillis());
        return new Page<>(list, pager);
    }


    /**
     * 角色充值排行榜（优化版）
     *
     * @param dto RoleRechargeRankingDTO
     * @return Page<List < Map < String, Object>>>
     */
    public Page<Map> getRoleRechargeRanking2(RoleRechargeRankingDTO dto) {
        //创建查询条件 给主表使用
        Criteria criA = getSqlByQuery(dto);
        //给充值时间查询条件
        Criteria criTodayAmount = getDateSqlByQuery(dto);
        //分页对象
        Pager pager = dao.createPager(dto.getPageNum(), dto.getPageSize());
        //判断是否合服 默认不合服
        String margeSql = "is_merge = 0";
        if (dto.getIsMergeServer()) {
            margeSql = "is_source_server = 0";
        }

        StopWatch watch = new StopWatch();
        watch.start();
        //查询总记录数
        Sql countSql = Sqls.create(getCountNumSql2(criA, criTodayAmount, margeSql));
        countSql.setCallback(Sqls.callback.integer());
        dao.execute(countSql);
        watch.stop();

        pager.setRecordCount(countSql.getInt());
        //主表添加排序条件
        if (StringUtils.isBlank(dto.getSortType())) {
            dto.setSortType(OrderByEnum.DESC.getOrderType());
        }
        if (StringUtils.isBlank(dto.getSortFiled())) {
            criA.getOrderBy().orderBy("amount", dto.getSortType());
        } else {
            criA.getOrderBy().orderBy(dto.getSortFiled(), dto.getSortType());
        }
        //创建sql
        StopWatch watch2 = new StopWatch();
        watch2.start();
        Sql sql = Sqls.create(getRoleRechargeRankingSql(criA, criTodayAmount, margeSql));
        sql.setCallback(Sqls.callback.maps());
        sql.setPager(pager);
        dao.execute(sql);
        watch2.stop();
        //查询结果
        List<Map> list = sql.getList(Map.class);
        //用户id列表
        List<Long> userIds = new ArrayList<>();
        for (Map map : list) {
            //把投手id和运营id和GSID和客服人员ID 转成List
            List<String> keys = Arrays.asList("put_user_id", "oper_user_id", "gs_id", "customer_service_id");
            for (String key : keys) {
                if (map.get(key) != null) {
                    userIds.add(Long.valueOf(map.get(key).toString()));
                }
            }
            //去掉字符串中的‘[]’
            String str = (String) map.get("role_amount");
            if (str != null && str.contains("[") && str.contains("]")) {
                map.put("role_amount", str.replaceAll("[\\[\\]]", ""));
            }
            //去除‘null’字符串
            List<String> nullStringKeys = Arrays.asList("add_corp_user_id", "user_wechat", "remark", "user_phone", "country");
            nullStringKeys.stream()
                    .filter(key -> "null".equals(map.get(key)))
                    .forEach(key -> map.put(key, null));
        }
        //去重userIds
        userIds = userIds.stream().distinct().collect(Collectors.toList());
        //发送RPC接口查询所有用户
//        ResultVO<Map<Long, String>> userMap = sysUserRpc.getUserNameByIds(userIds);
//
//        for (Map map : list) {
//            //投手名
//            updateUserName(map, "put_user_id", "put_user_name", userMap);
//            //运营人员名
//            updateUserName(map, "oper_user_id", "oper_user_name", userMap);
//            //GS人员名
//            updateUserName(map, "gs_id", "gs_name", userMap);
//            //客服人员名
//            updateUserName(map, "customer_service_id", "customer_service_name", userMap);
//        }
        //返回结果
        log.info("查询总记录数耗时：{}", watch.getTotalTimeMillis());
        log.info("查询数据耗时：{}", watch2.getTotalTimeMillis());
        return new Page<>(list, pager);
    }

    // 更新用户名称
    private void updateUserName(Map<String, Object> map, String userIdKey, String userNameKey, ResultVO<Map<Long, String>> userMap) {
        Object userIdObj = map.get(userIdKey);
        if (userIdObj != null) {
            try {
                long userId = Long.parseLong(userIdObj.toString());
                String userName = userMap.getData().get(userId);
                if (userName != null) {
                    map.put(userNameKey, userName);
                }
            } catch (NumberFormatException e) {
                // 处理长整型转换异常
                log.error("Error parsing user ID for key " + userIdKey + ": " + e.getMessage());
            }
        }
    }


    /**
     * 根据条件生成查询sql条件
     */
    private Criteria getSqlByQuery(RoleRechargeRankingDTO dto) {
        //默认查询不合服的数据
        if (dto.getIsMergeServer() == null) {
            dto.setIsMergeServer(Boolean.FALSE);
        }
        Criteria criA = Cnd.cri();

        //角色等级区间筛选
        if (dto.getRoleLevelMin() != null) {
            criA.where().andGTE("role_level", dto.getRoleLevelMin());
        }
        if (dto.getRoleLevelMax() != null) {
            criA.where().andLTE("role_level", dto.getRoleLevelMax());
        }
        //玩家注册时间
        if (dto.getRegisterTimeMax() != null && dto.getRegisterTimeMin() != null) {
            criA.where().andBetween("user_create_time", dto.getRegisterTimeMin().atTime(LocalTime.MIN), dto.getRegisterTimeMax().atTime(LocalTime.MAX));
        }
        //玩家渠道
        if (CollectionUtils.isNotEmpty(dto.getAgentIds())) {
            criA.where().andInList("agent_id", dto.getAgentIds());
        }

        //玩家注册渠道类型（0自然量  1非自然量）
        if (dto.getRegisterType() != null) {
            if (dto.getRegisterType() == 0) {
                criA.where().andEquals("agent_id", 0);
            } else {
                criA.where().andNotEquals("agent_id", 0);
            }
        }


        if (CollectionUtils.isNotEmpty(dto.getGameId())) {
            //角色注册子游戏
            criA.where().andInList("role_reg_game_id", dto.getGameId());
        }

        //拼接24内充值金额条件
        if (dto.getRechargeAmountWithin24h() != null) {
            spliceRechargeAmountWithin24h(dto, criA);
        }
        if (dto.getRechargeTotalAmountWithin24h() != null) {
            spliceRechargeTotalAmountWithin24h(dto, criA);
        }

        if (CollectionUtils.isNotEmpty(dto.getParentGameIds())) {
            //角色注册父游戏
            criA.where().andInList("role_reg_parent_game_id", dto.getParentGameIds());
        }
        if (dto.getSuperGameId() != null) {
            //角色注册超父游戏
            criA.where().andEquals("role_reg_super_game_id", dto.getSuperGameId());
        }
        if (StringUtils.isNotBlank(dto.getSourceSystem())) {
            criA.where().andEquals("source_system", dto.getSourceSystem());
        }
        if (StringUtils.isNotBlank(dto.getRoleId())) {
            criA.where().andEquals("role_id", dto.getRoleId());
        }
        if (StringUtils.isNotBlank(dto.getRoleName())) {
            criA.where().andLike("role_name", dto.getRoleName());
        }
        if (CollectionUtils.isNotEmpty(dto.getServerIds()) && !dto.getIsMergeServer()) {
            //区服列表查询,不合服的区服
            criA.where().andIn("server_id", dto.getServerIds().toArray(String[]::new));
        }
        if (CollectionUtils.isNotEmpty(dto.getServerIds()) && dto.getIsMergeServer()) {
            //合服区服列表查询
            criA.where().andIn("merge_server_id", dto.getServerIds().toArray(String[]::new));
        }
        if (dto.getCreateRoleBeginDate() != null && dto.getCreateRoleEndDate() != null) {
            //角色创建时间
            criA.where().andBetween("DATE(role_create_time)", dto.getCreateRoleBeginDate(), dto.getCreateRoleEndDate());
        }
        if (CollectionUtils.isNotEmpty(dto.getVipLevel())) {
            //角色vip等级
            criA.where().andInList("vip_level", dto.getVipLevel());
        }
        if (dto.getIsSendMail() != null) {
            criA.where().andEquals("is_send_mail", dto.getIsSendMail());
        }
        //是否转端
        List<Long> isChange = dto.getIsChange();
        if (CollectionUtils.isNotEmpty(isChange)) {
            criA.where().andInList("is_change_game_type", dto.getIsChange());
        }
        if (StringUtils.isNotBlank(dto.getWeChatCompany())) {
            //企业微信号
            criA.where().andLike("add_corp_user_id", dto.getWeChatCompany());
        }
        if (StringUtils.isNotBlank(dto.getWeChat())) {
            //客户微信
            criA.where().andLike("user_wechat", dto.getWeChat());
        }
        if (StringUtils.isNotBlank(dto.getPhone())) {
            //客户手机号
            criA.where().andEquals("user_phone", dto.getPhone());
        }
        if (dto.getUserPhoneStatus() != null) {
            //客服手机号绑定状态
            criA.where().andEquals("user_phone_status", dto.getUserPhoneStatus());
        }
        if (dto.getGsId() != null) {
            //GS
            if (dto.getGsId() == 0L) {
                criA.where().andIsNull("gsId");
            } else {
                criA.where().andEquals("gsId", dto.getGsId());
            }
        }
        if (dto.getPitcherId() != null) {
            //投手
            criA.where().andEquals("put_user_id", dto.getPitcherId());
        }
        if (dto.getOperatorId() != null) {
            //运营
            if (dto.getOperatorId() == 0L) {
                criA.where().andIsNull("oper_user_id");
            } else {
                criA.where().andEquals("oper_user_id", dto.getOperatorId());
            }
        }
        if (dto.getCustomerServerId() != null) {
            //客服
            if (dto.getCustomerServerId() == 0L) {
                criA.where().andIsNull("customer_service_id");
            } else {
                criA.where().andEquals("customer_service_id", dto.getCustomerServerId());
            }
        }
        if (dto.getTotalRechargeMin() != null) {
            //累计充值金额最小值
            criA.where().andGTE("amount", dto.getTotalRechargeMin());
        }
        if (dto.getTotalRechargeMax() != null) {
            //累计充值金额最大值
            criA.where().andLTE("amount", dto.getTotalRechargeMax());
        }
        if (StringUtils.isNotBlank(dto.getOs())) {
            //玩家操作系统
            criA.where().andEquals("os", dto.getOs());
        }
        List<Long> isRemoveGame = dto.getIsRemoveGame();
        if (CollectionUtils.isNotEmpty(isRemoveGame)) {
            //是否退游
            criA.where().andInList("is_remove_game", dto.getIsRemoveGame());
        }
        if (dto.getIsRemoveGameForSystem() != null) {
            //是否退游(系统判定)
            criA.where().andEquals("is_remove_game_for_system", dto.getIsRemoveGameForSystem());
        }
        List<Long> isWakeUp = dto.getIsWakeUp();
        if (CollectionUtils.isNotEmpty(isWakeUp)) {
            criA.where().andInList("is_wake_up", dto.getIsWakeUp());
        }
        List<Long> isAddCorpWechat = dto.getIsAddCorpWechat();
        if (CollectionUtils.isNotEmpty(isAddCorpWechat)) {
            //是否添加企微
            criA.where().andInList("is_add_corp_wechat", dto.getIsAddCorpWechat());
        }
        //角色最近活跃时间
        if (dto.getLastActiveTimeMax() != null && dto.getLastActiveTimeMin() != null) {
            criA.where().andBetween("DATE(role_active_time)", dto.getLastActiveTimeMin(), dto.getLastActiveTimeMax());
        }
        //角色最近充值时间
        if (dto.getRoleLastPayTimeMin() != null && dto.getRoleLastPayTimeMax() != null) {
            criA.where().andBetween("DATE(role_last_pay_time)", dto.getRoleLastPayTimeMin(), dto.getRoleLastPayTimeMax());
        }

        if (StringUtils.isNotBlank(dto.getIpCity())) {
            criA.where().andEquals("ip_city", dto.getIpCity());
        }
        if (StringUtils.isNotBlank(dto.getIpProv())) {
            criA.where().andEquals("ip_prov", dto.getIpProv());
        }
        if (StringUtils.isNotBlank(dto.getLoginIpCity())) {
            criA.where().andEquals("login_ip_city", dto.getLoginIpCity());
        }
        if (StringUtils.isNotBlank(dto.getLoginIpProv())) {
            criA.where().andEquals("login_ip_prov", dto.getLoginIpProv());
        }
        return criA;
    }

    /**
     * 根据时间条件生成查询sql
     */
    private Criteria getDateSqlByQuery(RoleRechargeRankingDTO dto) {
        Criteria criTodayAmount = Cnd.cri();
        //查询充值成功的
        criTodayAmount.where().andEquals("status", 2);
        if (dto.getRechargeBeginDate() != null && dto.getRechargeEndDate() != null) {
            //充值时间限制
            criTodayAmount.where().andBetween("DATE(pay_time)", dto.getRechargeBeginDate(), dto.getRechargeEndDate());
        }
        return criTodayAmount;
    }


    private void spliceRechargeTotalAmountWithin24h(RoleRechargeRankingDTO dto, Criteria criA) {
        switch (dto.getRechargeTotalAmountWithin24hUnit()) {
            case ">" -> criA.where().andGT("role_total_amount", dto.getRechargeTotalAmountWithin24h());
            case ">=" -> criA.where().andGTE("role_total_amount", dto.getRechargeTotalAmountWithin24h());
            case "=" -> criA.where().andEquals("role_total_amount", dto.getRechargeTotalAmountWithin24h());
            case "<" -> criA.where().andLT("role_total_amount", dto.getRechargeTotalAmountWithin24h());
            case "<=" -> criA.where().andLTE("role_total_amount", dto.getRechargeTotalAmountWithin24h());
            default -> criA.where().andGTE("role_total_amount", dto.getRechargeTotalAmountWithin24h());
        }
    }

    private void spliceRechargeAmountWithin24h(RoleRechargeRankingDTO dto, Criteria criA) {
        switch (dto.getRechargeAmountWithin24hUnit()) {
            case ">" -> criA.where().andGT("max_amount", dto.getRechargeAmountWithin24h());
//            case ">=" -> criA.where().andGTE("max_amount", dto.getRechargeAmountWithin24h());
            //这里用到了array_contains 函数，如果包含则返回1，不包含则返回0
            case "=" ->
                    criA.where().andEquals("array_contains(role_amount," + dto.getRechargeAmountWithin24h() + ")", 1);
            case "<" -> criA.where().andLT("min_amount", dto.getRechargeAmountWithin24h());
//            case "<=" -> criA.where().andLTE("max_amount", dto.getRechargeAmountWithin24h());
            default -> criA.where().andGT("max_amount", dto.getRechargeAmountWithin24h());
        }
    }

    /**
     * 角色战力排行榜
     *
     * @param dto RoleCombatRankingDTO
     * @return Page<List < Map < String, Object>>>
     */
    @Override
    public Page<Map> getRoleCombatRanking(RoleCombatRankingDTO dto) {

        //是否查询合服数据(默认查询合服数据)
        boolean isQueryMerge = true;
        //如果不传值，查看的是原始服数据
        if (dto.getIsMergeServer() == null || Boolean.FALSE.equals(dto.getIsMergeServer())) {
            isQueryMerge = false;
        }
        //是否查询超父游戏数据（默认查询超父游戏数据）
        boolean isSuperParentGame = true;
        if (dto.getParentGameId() != null) {
            isSuperParentGame = false;
        }
        //查询区服相关数据
        if (isQueryMerge) {
            if (isSuperParentGame) {
                //查询合服-超父
                return getMergeSuperParentData(dto);
            } else {
                //查询合服-父游戏
                return getMergeParentData(dto);
            }
        } else {
            if (isSuperParentGame) {
                //查询原始服-超父
                return getOriginSuperParentData(dto);
            } else {
                //查询原始服-父游戏
                return getOriginParentData(dto);
            }
        }
    }

    /**
     * 创建发送消息任务
     */
    @Override
    public void createSendMsgTask(SendMsgTaskDTO dto) {
        //校验游戏id的超父不为2的不让发消息，cp方未提供此功能
        Map<Long, GameDTO> parentGameMap = getParentGameMap();
        GameDTO gameDTO = parentGameMap.get(dto.getGameId());
        Map<Long, SuperGameDTO> superGameMap = getSuperGameMap();
        SuperGameDTO superGameDTO = superGameMap.get(gameDTO.getSuperGameId());
        if (superGameDTO == null
                || StringUtils.isEmpty(superGameDTO.getCpSendMsgKey()) || StringUtils.isEmpty(superGameDTO.getCpSendMsgUrl())) {
            throw new BaseException(gameDTO.getGameName() + ">该游戏不支持发送消息");
        }
        //获取当前用户id
        Long sysUserId = SecurityUtil.getUserId();
        //构建发送消息dto
        SendMsgDTO sendMsgDto = buildSendMsgDto(dto, sysUserId);
        //调用发送消息接口
        ResultVO<Boolean> booleanResultVO = sendMsgRpc.sendMsg(sendMsgDto);
        if (!booleanResultVO.isSuccess()) {
            throw new BaseException(booleanResultVO.getMsg());
        }
    }


    private SendMsgDTO buildSendMsgDto(SendMsgTaskDTO dto, Long sysUserId) {
//        //先查出来所有的角色
//        List<Map> roleList = getRoleList(dto.getRoleRechargeRankingDTO());
//
//        //筛选出角色id
//        List<String> roleIdList = roleList.stream()
//                .map(map -> Optional.ofNullable(map.get("role_id")).map(Object::toString).orElse(null))
//                .toList();

        SendMsgDTO sendMsgDto = new SendMsgDTO();
        //任务名称
        sendMsgDto.setTaskName(dto.getTaskName());
        //消息
        sendMsgDto.setMsg(dto.getSendContent());
        //游戏id
        sendMsgDto.setGameId(dto.getGameId());
        //暂时用前端传的角色id后期在用自己查的
        sendMsgDto.setRoles(dto.getRoleIds());
        //创建人
        sendMsgDto.setCreateBy(sysUserId);
        //条件
        RoleRechargeRankingDTO roleRechargeRankingDTO = dto.getRoleRechargeRankingDTO();
        sendMsgDto.setSendConditionJson(JSON.toJSONString(roleRechargeRankingDTO));

        return sendMsgDto;
    }


    /**
     * 获取角色列表
     */
    @Override
    public List<Map> getRoleList(RoleRechargeRankingDTO dto) {

        //默认查询不合服的数据
        if (dto.getIsMergeServer() == null) {
            dto.setIsMergeServer(Boolean.FALSE);
        }
        //创建查询条件 给主表使用
        Criteria criA = Cnd.cri();
        if (CollectionUtils.isNotEmpty(dto.getGameId())) {
            //角色注册子游戏
            criA.where().andInList("role_reg_game_id", dto.getGameId());
        }

        //拼接24内充值金额条件
        if (dto.getRechargeAmountWithin24h() != null) {
            spliceRechargeAmountWithin24h(dto, criA);
        }
        if (dto.getRechargeTotalAmountWithin24h() != null) {
            spliceRechargeTotalAmountWithin24h(dto, criA);
        }

        if (CollectionUtils.isNotEmpty(dto.getParentGameIds())) {
            //角色注册父游戏
            criA.where().andInList("role_reg_parent_game_id", dto.getParentGameIds());
        }
        if (dto.getSuperGameId() != null) {
            //角色注册超父游戏
            criA.where().andEquals("role_reg_super_game_id", dto.getSuperGameId());
        }
        if (StringUtils.isNotBlank(dto.getSourceSystem())) {
            criA.where().andEquals("source_system", dto.getSourceSystem());
        }
        if (StringUtils.isNotBlank(dto.getRoleId())) {
            criA.where().andEquals("role_id", dto.getRoleId());
        }
        if (StringUtils.isNotBlank(dto.getRoleName())) {
            criA.where().andLike("role_name", dto.getRoleName());
        }
        if (CollectionUtils.isNotEmpty(dto.getServerIds()) && !dto.getIsMergeServer()) {
            //区服列表查询,不合服的区服
            criA.where().andIn("server_id", dto.getServerIds().toArray(String[]::new));
        }
        if (CollectionUtils.isNotEmpty(dto.getServerIds()) && dto.getIsMergeServer()) {
            //合服区服列表查询
            criA.where().andIn("merge_server_id", dto.getServerIds().toArray(String[]::new));
        }
        if (dto.getCreateRoleBeginDate() != null && dto.getCreateRoleEndDate() != null) {
            //角色创建时间
            criA.where().andBetween("DATE(role_create_time)", dto.getCreateRoleBeginDate(), dto.getCreateRoleEndDate());
        }
        if (CollectionUtils.isNotEmpty(dto.getVipLevel())) {
            //角色vip等级
            criA.where().andInList("vip_level", dto.getVipLevel());
        }
        if (dto.getIsSendMail() != null) {
            criA.where().andEquals("is_send_mail", dto.getIsSendMail());
        }
        //是否转端
        List<Long> isChange = dto.getIsChange();
        if (CollectionUtils.isNotEmpty(isChange)) {
            if (isChange.contains(2L)) {
                //是否转端
                criA.where().andIsNull("is_change_game_type");
            }
            if (isChange.contains(1L) || isChange.contains(0L)) {
                //是否转端
                criA.where().andInList("is_change_game_type", dto.getIsChange());
            }
        }
        if (StringUtils.isNotBlank(dto.getWeChatCompany())) {
            //企业微信号
            criA.where().andLike("add_corp_user_id", dto.getWeChatCompany());
        }
        if (StringUtils.isNotBlank(dto.getWeChat())) {
            //客户微信
            criA.where().andLike("user_wechat", dto.getWeChat());
        }
        if (StringUtils.isNotBlank(dto.getPhone())) {
            //客户手机号
            criA.where().andEquals("user_phone", dto.getPhone());
        }
        if (dto.getGsId() != null) {
            //GS
            if (dto.getGsId() == 0L) {
                criA.where().andIsNull("gs_id");
            } else {
                criA.where().andEquals("gs_id", dto.getGsId());
            }
        }
        if (dto.getPitcherId() != null) {
            //投手
            criA.where().andEquals("put_user_id", dto.getPitcherId());
        }
        if (dto.getOperatorId() != null) {
            //运营
            if (dto.getOperatorId() == 0L) {
                criA.where().andIsNull("oper_user_id");
            } else {
                criA.where().andEquals("oper_user_id", dto.getOperatorId());
            }
        }
        if (dto.getCustomerServerId() != null) {
            //客服
            if (dto.getCustomerServerId() == 0L) {
                criA.where().andIsNull("customer_service_id");
            } else {
                criA.where().andEquals("customer_service_id", dto.getCustomerServerId());
            }
        }
        if (dto.getTotalRechargeMin() != null) {
            //累计充值金额最小值
            criA.where().andGTE("amount", dto.getTotalRechargeMin());
        }
        if (dto.getTotalRechargeMax() != null) {
            //累计充值金额最大值
            criA.where().andLTE("amount", dto.getTotalRechargeMax());
        }
        if (StringUtils.isNotBlank(dto.getOs())) {
            //玩家操作系统
            criA.where().andEquals("os", dto.getOs());
        }
        List<Long> isRemoveGame = dto.getIsRemoveGame();
        if (CollectionUtils.isNotEmpty(isRemoveGame)) {
            if (isRemoveGame.contains(2L)) {
                //是否退游
                criA.where().andIsNull("is_remove_game");
            }
            if (isRemoveGame.contains(1L) || isRemoveGame.contains(0L)) {
                //是否退游
                criA.where().andInList("is_remove_game", dto.getIsRemoveGame());
            }
        }
        if (dto.getIsRemoveGameForSystem() != null) {
            //是否退游(系统判定)
            criA.where().andEquals("is_remove_game_for_system", dto.getIsRemoveGameForSystem());
        }
        //是否唤醒
        List<Long> isWakeUp = dto.getIsWakeUp();
        if (CollectionUtils.isNotEmpty(isWakeUp)) {
            criA.where().andInList("is_wake_up", dto.getIsWakeUp());
        }
        List<Long> isAddCorpWechat = dto.getIsAddCorpWechat();
        if (CollectionUtils.isNotEmpty(isAddCorpWechat)) {

            criA.where().andInList("is_add_corp_wechat", dto.getIsAddCorpWechat());

        }
        //给充值时间查询条件
        Criteria criTodayAmount = Cnd.cri();
        //查询充值成功的
        criTodayAmount.where().andEquals("status", 2);
        if (dto.getRechargeBeginDate() != null && dto.getRechargeEndDate() != null) {
            //充值时间限制
            criTodayAmount.where().andBetween("DATE(pay_time)", dto.getRechargeBeginDate(), dto.getRechargeEndDate());
        }

        //主表添加排序条件
        if (StringUtils.isBlank(dto.getSortType())) {
            dto.setSortType(OrderByEnum.DESC.getOrderType());
        }
        if (StringUtils.isBlank(dto.getSortFiled())) {
            criA.getOrderBy().orderBy("amount", dto.getSortType());
        } else {
            criA.getOrderBy().orderBy(dto.getSortFiled(), dto.getSortType());
        }
        //判断是否合服 默认不合服
        String margeSql = "is_merge = 0";
        if (dto.getIsMergeServer()) {
            margeSql = "is_source_server = 0";
        }
        //创建sql
        Sql sql = Sqls.create(getRoleRechargeRankingSql(criA, criTodayAmount, margeSql));
        sql.setCallback(Sqls.callback.maps());
        dao.execute(sql);
        return sql.getList(Map.class);
    }

    /**
     * 获取发送消息任务列表
     *
     * @param dto dto
     * @return Page<SendMsgVo>
     */
    @Override
    public Page<SendMsgVO> getSendMsgTaskList(MsgTaskDTO dto) {
        try {
            SendMsgTaskParam sendMsgTaskParam = new SendMsgTaskParam();
            sendMsgTaskParam.setGameId(dto.getGameId());
            sendMsgTaskParam.setTaskStatus(dto.getTaskStatus());
            sendMsgTaskParam.setPageNum(dto.getPageNum());
            sendMsgTaskParam.setPageSize(dto.getPageSize());
            PageUtil<SendMsgVO> sendMsgTaskList = sendMsgRpc.getSendMsgTaskList(sendMsgTaskParam);
            if (CollectionUtils.isEmpty(sendMsgTaskList.getRecords())) {
                return new Page<>(sendMsgTaskList.getRecords(), sendMsgTaskList.getTotal(), sendMsgTaskList.getSize(), sendMsgTaskList.getCurrent(), sendMsgTaskList.getPages());
            }
            //查询所有游戏名称map key为游戏id，value为游戏名称
            Map<Long, GameDTO> gameMap = getParentGameMap();

            //查询创建人id key为创建人id，value为创建人名称
            Map<Long, String> userMap = getCreateByNameMap(sendMsgTaskList);

            //循环遍历获取游戏名称, 获取创建人名称
            sendMsgTaskList.getRecords().forEach(item -> {
                GameDTO gameDTO = gameMap.get(item.getGameId());
                if (gameDTO != null && gameDTO.getGameName() != null) {
                    item.setGameName(gameDTO.getGameName());
                }
                String userName = userMap.get(item.getCreateBy());
                if (userName != null) {
                    item.setCreateByName(userName);
                }
            });
            return new Page<>(sendMsgTaskList.getRecords(), sendMsgTaskList.getTotal(), sendMsgTaskList.getSize(), sendMsgTaskList.getCurrent(), sendMsgTaskList.getPages());
        } catch (Exception exception) {
            log.error("获取发送消息任务列表失败", exception);
            throw new BaseException("获取发送消息任务列表失败");
        }
    }


    /**
     * 获取创建人名称
     */
    private Map<Long, String> getCreateByNameMap(PageUtil<SendMsgVO> sendMsgTaskList) {
        //取创建人id变成list
        List<Long> userId = sendMsgTaskList.getRecords().stream().
                map(SendMsgVO::getCreateBy).collect(Collectors.toList());
        ResultVO<Map<Long, String>> userNameByIds = sysUserRpc.getUserNameByIds(userId);
        return userNameByIds.getData();
    }

    /**
     * 获取游戏列表
     */
    private Map<Long, GameDTO> getParentGameMap() {
        Sql sql = Sqls.create(getGameSql());
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(GameDTO.class));
        dao.execute(sql);
        List<GameDTO> gameDtoList = sql.getList(GameDTO.class);
        //将游戏拼成map key为游戏id，value为游戏名称
        return gameDtoList.stream().collect(Collectors.toMap(GameDTO::getId, Function.identity()));
    }


    /**
     * 获取超父游戏列表
     */
    private Map<Long, SuperGameDTO> getSuperGameMap() {
        Sql sql = Sqls.create(getSuperGameSql());
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(SuperGameDTO.class));
        dao.execute(sql);
        List<SuperGameDTO> gameDtoList = sql.getList(SuperGameDTO.class);
        //将游戏拼成map key为游戏id，value为游戏名称
        return gameDtoList.stream().collect(Collectors.toMap(SuperGameDTO::getId, Function.identity()));
    }

    /**
     * 查询发送消息任务结果
     *
     * @param msgTaskResultDto msgTaskResultDto
     * @return Page<SendMsgResultVo>
     */
    @Override
    public Page<SendMsgResultVO> getSendMsgResultList(MsgTaskResultDTO msgTaskResultDto) {
        try {
            SendMsgTaskResultParam param = new SendMsgTaskResultParam();
            param.setTaskId(msgTaskResultDto.getTaskId());
            param.setSendStatus(msgTaskResultDto.getSendStatus());
            param.setPageNum(msgTaskResultDto.getPageNum());
            param.setPageSize(msgTaskResultDto.getPageSize());
            PageUtil<SendMsgResultVO> sendMsgTaskResultList = sendMsgRpc.getSendMsgTaskResultList(param);
            if (CollectionUtils.isEmpty(sendMsgTaskResultList.getRecords())) {
                return new Page<>(sendMsgTaskResultList.getRecords(), sendMsgTaskResultList.getTotal(), sendMsgTaskResultList.getSize(), sendMsgTaskResultList.getCurrent(), sendMsgTaskResultList.getPages());
            }
            //取角色id变成list
            List<String> roleId = sendMsgTaskResultList.getRecords().stream().
                    map(SendMsgResultVO::getRoleId).collect(Collectors.toList());

            SendMsgResultVO sendMsgResultVo = sendMsgTaskResultList.getRecords().get(0);
            Criteria cri = Cnd.cri();
            //拼接游戏ID
            cri.where().andInStrList("role_id", roleId);
            cri.where().andEquals("b.id", sendMsgResultVo.getGameId());

            //补齐参数
            Sql sql = Sqls.create(getTaskResultSql() + cri);
            sql.setCallback(Sqls.callback.entities());
            sql.setEntity(dao.getEntity(TaskResultDTO.class));
            dao.execute(sql);
            List<TaskResultDTO> list = sql.getList(TaskResultDTO.class);
            //拼成map key为角色id
            HashMap<String, TaskResultDTO> taskResultMap = new HashMap<>();
            list.forEach(item -> taskResultMap.put(item.getRoleId(), item));
            sendMsgTaskResultList.getRecords().forEach(item -> {
                TaskResultDTO taskResultDto = taskResultMap.get(item.getRoleId());
                if (taskResultDto != null) {
                    item.setGameName(taskResultDto.getGameName());
                    item.setRoleName(taskResultDto.getRoleName());
                    item.setRoleCreateTime(taskResultDto.getCreateTime());
                    item.setServerName(taskResultDto.getServerName());
                    item.setRoleVip(taskResultDto.getRoleVip());
                    item.setRoleCreateTime(taskResultDto.getCreateTime());
                }
            });

            return new Page<>(sendMsgTaskResultList.getRecords(), sendMsgTaskResultList.getTotal(), sendMsgTaskResultList.getSize(), sendMsgTaskResultList.getCurrent(), sendMsgTaskResultList.getPages());
        } catch (Exception e) {
            log.error("查询发送消息任务结果失败", e);
            throw new BaseException("查询发送消息任务结果失败");
        }
    }

    @Override
    public Page<Map> levelListOfPage(RoleLevelParamDTO dto) {
        //创建查询条件
        Criteria criA = getSqlByQuery(dto);
        //分页对象
        Pager pager = dao.createPager(dto.getPageNum(), dto.getPageSize());
        //查询总记录数
        Sql countSql = Sqls.create(getLevelListCountSql(criA));
        countSql.setCallback(Sqls.callback.integer());
        dao.execute(countSql);
        pager.setRecordCount(countSql.getInt());
        //主表添加排序条件
        if (StringUtils.isBlank(dto.getSortType())) {
            dto.setSortType(OrderByEnum.DESC.getOrderType());
        }
        if (StringUtils.isBlank(dto.getSortFiled())) {
            criA.getOrderBy().orderBy("levelTime", dto.getSortType());
        } else {
            if (dto.getSortFiled().equals("ipRoleCountFilter")) {
                dto.setSortFiled("roleUserIpCount");
            }
            if (dto.getSortFiled().equals("roleUserIpCountFilter")) {
                dto.setSortFiled("roleUserIpCountFilter");
            }
            criA.getOrderBy().orderBy(dto.getSortFiled(), dto.getSortType());
        }
        //列表sql
        String levelListSql = getLevelListSql(criA);
        Sql sql = Sqls.create(levelListSql);
        sql.setCallback(Sqls.callback.maps());
        sql.setPager(pager);
        dao.execute(sql);
        //查询结果
        List<Map> list = sql.getList(Map.class);
        if (CollectionUtils.isNotEmpty(list)) {
            List<String> keys = Arrays.asList("pitcherId", "operUserId", "gsId", "customerServiceId");
            List<Long> userIds = new ArrayList<>();
            for (Map map : list) {
                //把投手id和运营id和GSID和客服人员ID 转成List
                for (String key : keys) {
                    if (map.get(key) != null) {
                        userIds.add(Long.valueOf(map.get(key).toString()));
                    }
                }
            }
            //去重userIds
            userIds = userIds.stream().distinct().collect(Collectors.toList());
            //发送RPC接口查询所有用户
            ResultVO<Map<Long, String>> userMap = sysUserRpc.getUserNameByIds(userIds);
            for (Map map : list) {
                //投手名
                updateUserName(map, "pitcherId", "putUserName", userMap);
                //运营人员名
                updateUserName(map, "operUserId", "operUserName", userMap);
                //GS人员名
                updateUserName(map, "gsId", "gsName", userMap);
                //客服人员名
                updateUserName(map, "customerServiceId", "customerServiceName", userMap);
            }
        }
        return new Page<>(list, pager);
    }

    @Override
    public Page<Map> ipInfoListOfPage(RoleIpInfoParamDTO dto) {
        //创建查询条件
        Criteria criA = getSqlByQuery(dto);
        //分页对象
        Pager pager = dao.createPager(dto.getPageNum(), dto.getPageSize());
        //查询总记录数
        Sql countSql = Sqls.create(getIpInfoListCountSql(criA));
        countSql.setCallback(Sqls.callback.integer());
        dao.execute(countSql);
        pager.setRecordCount(countSql.getInt());
        //主表添加排序条件
        if (StringUtils.isBlank(dto.getSortType())) {
            dto.setSortType(OrderByEnum.DESC.getOrderType());
        }
        if (StringUtils.isBlank(dto.getSortFiled())) {
            criA.getOrderBy().orderBy("a.create_time", dto.getSortType());
        } else {
            if (dto.getSortFiled().equals("ip_role_count_filter")) {
                dto.setSortFiled("role_count_group_filters_count");
            }
            if (dto.getSortFiled().equals("role_user_ip_count_filter")) {
                dto.setSortFiled("role_user_group_filters_count");
            }
            criA.getOrderBy().orderBy(dto.getSortFiled(), dto.getSortType());
        }
        //列表sql
        String ipInfoListSql = getIpInfoListSql(criA);
        Sql sql = Sqls.create(ipInfoListSql);
        sql.setCallback(Sqls.callback.maps());
        sql.setPager(pager);
        dao.execute(sql);
        //查询结果
        List<Map> list = sql.getList(Map.class);
        return new Page<>(list, pager);
    }

    @Override
    public Map<String, List<RoleGameCountryVO>> gameCountryDataList(GameCountryParamDTO dto) {
        //创建查询条件
        Criteria criA = getSqlByQuery(dto);
        String gameCountryListSql = getGameCountryListSql(criA);
        Sql sql1 = Sqls.create(gameCountryListSql);
        dao.execute(sql1);
        sql1.setCallback(Sqls.callback.entities());
        sql1.setEntity(dao.getEntity(RoleGameCountryVO.class));
        dao.execute(sql1);
        //查询结果
        List<RoleGameCountryVO> list = sql1.getList(RoleGameCountryVO.class);
        if (CollectionUtils.isNotEmpty(list)) {
            //以country为key，value是list分组
            Map<String, List<RoleGameCountryVO>> map = list.stream().collect(Collectors.groupingBy(RoleGameCountryVO::getCs));
            return map;
        }
        return null;
    }

    @Override
    public Page<Map> gameCountryRoleList(RoleGameCountryParamDTO dto) {
        Criteria criA = getSqlByQuery(dto);
        //分页对象
        Pager pager = dao.createPager(dto.getPageNum(), dto.getPageSize());
        //查询总记录数
        Sql countSql = Sqls.create(getGameCountryRoleCountSql(criA));
        countSql.setCallback(Sqls.callback.integer());
        dao.execute(countSql);
        pager.setRecordCount(countSql.getInt());
        Criteria orderCri = Cnd.cri();
        //主表添加排序条件
        if (StringUtils.isBlank(dto.getSortType())) {
            dto.setSortType(OrderByEnum.DESC.getOrderType());
        }
        if (StringUtils.isBlank(dto.getSortFiled())) {
            orderCri.getOrderBy().orderBy("roleTotalAmount", dto.getSortType());
        } else {
            orderCri.getOrderBy().orderBy(dto.getSortFiled(), dto.getSortType());
        }
        //列表sql
        String gameCountryRoleListSql = getGameCountryRoleListSql(criA, orderCri);
        Sql sql = Sqls.create(gameCountryRoleListSql);
        sql.setCallback(Sqls.callback.maps());
        sql.setPager(pager);
        dao.execute(sql);
        //查询结果
        List<Map> list = sql.getList(Map.class);
        return new Page<>(list, pager);
    }

    @Override
    public List<Map> gameCountryList(GameCountryParamDTO dto) {
        Criteria cri = Cnd.cri();
        if (CollectionUtils.isNotEmpty(dto.getServerId())) {
            cri.where().andInStrList("server_id", dto.getServerId());
        }
        if (CollectionUtils.isNotEmpty(dto.getGameId())) {
            cri.where().andInList("parent_game_id", dto.getGameId());
        }
        String countryListSql = getCountryListSql(cri);
        Sql sql = Sqls.create(countryListSql);
        sql.setCallback(Sqls.callback.maps());
        dao.execute(sql);
        List<Map> list = sql.getList(Map.class);
        return list;
    }

    @Override
    public List<Map> roleIpDetailListOfPage(RoleIpInfoParamDTO dto) {
        if (dto.getRoleId() == null) {
            throw new BaseException("角色id不能为空");
        }
        List<Map> maps = baseService.ipDetailList(dto);
        if (CollectionUtils.isEmpty(maps)) {
            return new ArrayList<>();
        }
        Map map = maps.get(0);
        String ipCountIp = String.valueOf(map.get("ip_count_ip"));
        if (StringUtils.isEmpty(ipCountIp)) {
            return new ArrayList<>();
        }
        return baseService.ipCountList(Arrays.stream(ipCountIp.split(",")).map(String::trim).collect(Collectors.toList()), dto.getRoleId());
    }

    @Override
    public List<Map> ipDetailList(RoleIpInfoParamDTO dto) {
        Criteria criteria = Cnd.cri();
        criteria.where().andEquals("role_id", dto.getRoleId());
        if (CollectionUtils.isNotEmpty(dto.getGameId())) {
            criteria.where().andInList("game_id", dto.getGameId());
        }
        String ipDetailListSql = getIpDetailListSql(criteria);
        Sql sql = Sqls.create(ipDetailListSql);
        sql.setCallback(Sqls.callback.maps());
        dao.execute(sql);
        return sql.getList(Map.class);
    }

    @Override
    public List<Map> ipCountList(List<String> ip, String roleId) {
        Criteria criteria = Cnd.cri();
        criteria.where().andInStrList("a.ip", ip);
        criteria.where().andNotIsNull("a.ip");
        criteria.where().andNotEquals("a.ip", "null");
        criteria.where().andNotEquals("a.ip", "");
        criteria.where().andNotIsNull("a.role_id");
        criteria.where().andNotEquals("a.role_id", "null");
        criteria.where().andNotEquals("a.role_id", "");
        criteria.where().andNotEquals("a.role_id", "");
        String ipCountList = getIpCountList(criteria, roleId);
        Sql sql = Sqls.create(ipCountList);
        sql.setCallback(Sqls.callback.maps());
        dao.execute(sql);
        return sql.getList(Map.class);
    }

    private String getIpCountList(Criteria criteria, String roleId) {
        return """
                SELECT a.ip,
                       COUNT(DISTINCT a.role_id) AS ipRoleCount,
                       COUNT(distinct b.id) AS loginCount
                FROM dm_game_order.t_user_login_log a
                         LEFT JOIN
                     dm_game_order.t_user_login_log b
                     ON
                         a.ip = b.ip AND b.role_id = a.role_id and b.role_id = """ + roleId + """
                """ + criteria + """
                 group by a.ip
                 order by loginCount desc
                """;
    }

    @Override
    public List<Map> userDetailList(RoleIpInfoParamDTO dto) {
        if (dto.getRoleId() == null) {
            throw new BaseException("角色id不能为空");
        }
        List<Map> maps = baseService.ipDetailList(dto);
        if (CollectionUtils.isEmpty(maps)) {
            return new ArrayList<>();
        }
        Map map = maps.get(0);
        String userCountGroup = String.valueOf(map.get("user_count_group"));
        if (StringUtils.isEmpty(userCountGroup) || "null".equals(userCountGroup)) {
            return new ArrayList<>();
        }
        return baseService.userDetailList(Arrays.stream(userCountGroup.split(",")).map(String::trim).map(Long::valueOf).collect(Collectors.toList()), dto);
    }

    @Override
    public List<Map> userDetailList(List<Long> userIds, RoleIpInfoParamDTO dto) {
        Criteria criteria = Cnd.cri();
        criteria.where().andInList("user_id", userIds);
        Criteria orderCriteria = getUserDetailOrderSql(dto);
        String userDetailListSql = getUserDetailListSql(criteria, orderCriteria);
        Sql sql = Sqls.create(userDetailListSql);
        sql.setCallback(Sqls.callback.maps());
        dao.execute(sql);
        //查询结果
        return sql.getList(Map.class);
    }

    @Override
    public List<Map> roleDetailList(RoleIpInfoParamDTO dto) {
        if (dto.getRoleId() == null) {
            throw new BaseException("角色id不能为空");
        }
        List<Map> maps = baseService.ipDetailList(dto);
        if (CollectionUtils.isEmpty(maps)) {
            return new ArrayList<>();
        }
        Map map = maps.get(0);
        String filedRoleId = "role_count_group";
        if (dto.getExcludeUserType() == 1) {
            filedRoleId = "role_count_group_filters";
        }
        String roleIdStr = String.valueOf(map.get(filedRoleId));
        if (StringUtils.isEmpty(roleIdStr) || roleIdStr.equals("[]")) {
            return new ArrayList<>();
        }
        if (dto.getExcludeUserType() == 1) {
            roleIdStr = roleIdStr.replaceAll("[\\[\\]\"]", "");
        }
        return baseService.roleDetailList(Arrays.stream(roleIdStr.split(",")).map(String::trim).collect(Collectors.toList()), dto);
    }

    @Override
    public List<Map> roleDetailList(List<String> roleId) {
        Criteria criteria = Cnd.cri();
        criteria.where().andInStrList("a.role_id", roleId);
        criteria.where().andEquals("a.source_system", "ZX_ONE");
        criteria.where().andInList("c.super_game_id", List.of(12L));
        String roleDetailListSql = getRoleDetailListSql(criteria);
        Sql sql = Sqls.create(roleDetailListSql);
        sql.setCallback(Sqls.callback.maps());
        dao.execute(sql);
        //查询结果
        List<Map> list = sql.getList(Map.class);
        return list;
    }

    private String getRoleDetailListSql(Criteria criteria) {
        return """
                select any_value(a.role_name)   as roleName,
                       a.role_id                as roleId,
                       any_value(combat_num)    as combatNum,
                       any_value(a.os) as os,
                       SUM(IFNULL(b.amount, 0)) as roleTotalAmount,
                       any_value(c.game_name) as gameName,
                       any_value(a.server_id) as serverId,
                       any_value(a.server_name) as serverName ,
                       any_value(a.role_level) as roleLevel ,
                       any_value(a.create_time) as createTime ,
                       any_value(a.update_time) as updateTime ,
                       any_value(a.last_login_time) as lastLoginTime ,
                       any_value(a.last_recharge_time) as lastRechargeTime
                from dm_game_order.t_game_user_role a
                         left join game_dw.dw_order_day_amount b on a.role_id = b.role_id
                left join dm_game_order.t_game c on a.game_id = c.id and c.source_system = 'ZX_ONE'
                """ + criteria + """
                GROUP BY a.country, a.role_id, a.user_id, a.server_id
                """;
    }

    @Override
    public List<Map> roleDetailList(List<String> roleId, RoleIpInfoParamDTO roleIpInfoParamDTO) {
        Criteria criteria = Cnd.cri();
        criteria.where().andInStrList("role_id", roleId);
        String roleDetailListSql = getRoleDetailList(criteria);
        Sql sql = Sqls.create(roleDetailListSql);
        sql.setCallback(Sqls.callback.maps());
        dao.execute(sql);
        //查询结果
        List<Map> list = sql.getList(Map.class);
        return list;
    }

    @Override
    public List<Map> suspectedRoleDetailList(RoleIpInfoParamDTO dto) {
        if (dto.getRoleId() == null) {
            throw new BaseException("角色id不能为空");
        }
        List<Map> maps = baseService.ipDetailList(dto);
        if (CollectionUtils.isEmpty(maps)) {
            return new ArrayList<>();
        }
        Map map = maps.get(0);
        String suspectedRoleIds = String.valueOf(map.get("suspected_role_ids"));
        if (StringUtils.isEmpty(suspectedRoleIds) || "null".equals(suspectedRoleIds)) {
            return new ArrayList<>();
        }
        return baseService.roleDetailList(Arrays.stream(suspectedRoleIds.split(",")).map(String::trim).collect(Collectors.toList()));

    }

    @Override
    public Page<RoleChatVO> chatListOfPage(RoleChatParamDTO dto) {
        Criteria criA = getSqlByQuery(dto);
        //分页对象
        Pager pager = dao.createPager(dto.getPageNum(), dto.getPageSize());
        //查询总记录数
        Sql countSql = Sqls.create(getChatListCountSql(criA));
        countSql.setCallback(Sqls.callback.integer());
        dao.execute(countSql);
        pager.setRecordCount(countSql.getInt());
        Criteria orderCri = Cnd.cri();
        //主表添加排序条件
        if (StringUtils.isBlank(dto.getSortType())) {
            dto.setSortType(OrderByEnum.DESC.getOrderType());
        }
        if (StringUtils.isBlank(dto.getSortFiled())) {
            orderCri.getOrderBy().orderBy("last_chat_time", dto.getSortType());
        } else {
            orderCri.getOrderBy().orderBy(com.zanxiang.game.data.serve.utils.StringUtils.camelToSnake(dto.getSortFiled()), dto.getSortType());
        }
        //列表sql
        String gameCountryRoleListSql = getChatListSql(criA, orderCri);
        Sql sql = Sqls.create(gameCountryRoleListSql);
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(RoleChatVO.class));
        sql.setPager(pager);
        dao.execute(sql);
        //查询结果
        List<RoleChatVO> list = sql.getList(RoleChatVO.class);
        return new Page<>(list, pager);
    }

    private String getChatListSql(Criteria criA, Criteria orderCri) {
        return """
                select 
                role_id as role_id,
                dt,
                super_game_id as super_game_id,
                server_id as server_id,
                role_chat_count as role_chat_count,
                last_chat_time as last_chat_time,
                role_name as role_name,
                country,
                server_name as server_name,
                super_game_name as super_game_name
                from game_ads.ads_role_chat
                """ + criA + orderCri;
    }

    private String getChatListCountSql(Criteria criA) {
        return """
                select count(1) from game_ads.ads_role_chat
                """ + criA;
    }

    private Criteria getSqlByQuery(RoleChatParamDTO dto) {
        Criteria criteria = Cnd.cri();
        criteria.where().andEquals("source_system", "ZX_ONE");
        if (dto.getRoleId() != null) {
            criteria.where().andEquals("role_id", dto.getRoleId());
        }
        if (StringUtils.isNotEmpty(dto.getRoleName())) {
            criteria.where().andLike("role_name", dto.getRoleName());
        }
        if (dto.getSuperGameId() != null) {
            criteria.where().andEquals("super_game_id", dto.getSuperGameId());
        }
        if (dto.getServerId() != null) {
            criteria.where().andEquals("server_id", dto.getServerId());
        }
        if (StringUtils.isNotEmpty(dto.getCountry())) {
            criteria.where().andLike("country", dto.getCountry());
        }
        if (dto.getStartDate() != null && dto.getEndDate() != null) {
            criteria.where().andBetween("dt", dto.getStartDate().atTime(LocalTime.MIN), dto.getEndDate().atTime(LocalTime.MAX));
        }
        return criteria;
    }

    @Override
    public Page<RoleChatDetailVO> chatDetail(RoleChatDetailParamDTO dto) {
        Criteria criA = getSqlByQuery(dto);
        //分页对象
        Pager pager = dao.createPager(dto.getPageNum(), dto.getPageSize());
        //查询总记录数
        Sql countSql = Sqls.create(getChatDetailListCountSql(criA));
        countSql.setCallback(Sqls.callback.integer());
        dao.execute(countSql);
        pager.setRecordCount(countSql.getInt());
        Criteria orderCri = Cnd.cri();
        //主表添加排序条件
        if (StringUtils.isBlank(dto.getSortType())) {
            dto.setSortType(OrderByEnum.DESC.getOrderType());
        }
        if (StringUtils.isBlank(dto.getSortFiled())) {
            orderCri.getOrderBy().orderBy("a.chat_time", dto.getSortType());
        } else {
            orderCri.getOrderBy().orderBy(com.zanxiang.game.data.serve.utils.StringUtils.camelToSnake(dto.getSortFiled()), dto.getSortType());
        }
        //列表sql
        String gameCountryRoleListSql = getChatDetailListSql(criA, orderCri);
        Sql sql = Sqls.create(gameCountryRoleListSql);
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(RoleChatDetailVO.class));
        sql.setPager(pager);
        dao.execute(sql);
        //查询结果
        List<RoleChatDetailVO> list = sql.getList(RoleChatDetailVO.class);
        return new Page<>(list, pager);
    }

    private String getChatDetailListSql(Criteria criA, Criteria orderCri) {
        return """
                select
                       dt,
                       supper_game_id as super_game_id,
                       b.name as super_game_name,
                       server_id as server_id,
                       server_name as server_name,
                       role_id as role_id,
                       role_name as role_name,
                       guild_name as country,
                       chat_time as chat_time,
                       content
                from dm_game_order.t_game_user_chat a
                left join dm_game_order.t_game_super b on a.source_system = b.source_system and a.supper_game_id = b.id
                """ + criA + orderCri;
    }

    private String getChatDetailListCountSql(Criteria criA) {
        return """
                select count(1) from dm_game_order.t_game_user_chat a
                """ + criA;
    }

    private Criteria getSqlByQuery(RoleChatDetailParamDTO dto) {
        Criteria criteria = Cnd.cri();
        criteria.where().andEquals("a.source_system", "ZX_ONE");
        if (dto.getRoleId() != null) {
            criteria.where().andEquals("a.role_id", dto.getRoleId());
        }
        if (dto.getStartDate() != null && dto.getEndDate() != null) {
            criteria.where().andBetween("a.dt", dto.getStartDate().atTime(LocalTime.MIN), dto.getEndDate().atTime(LocalTime.MAX));
        }
        return criteria;
    }

    private Criteria getUserDetailOrderSql(RoleIpInfoParamDTO dto) {
        Criteria criteria = Cnd.cri();
        //主表添加排序条件
        if (StringUtils.isBlank(dto.getSortType())) {
            dto.setSortType(OrderByEnum.DESC.getOrderType());
        }
        if (StringUtils.isBlank(dto.getSortFiled())) {
            criteria.getOrderBy().orderBy("createTime", dto.getSortType());
        } else {
            criteria.getOrderBy().orderBy(dto.getSortFiled(), dto.getSortType());
        }
        return criteria;
    }

    private String getUserDetailListSql(Criteria criteria, Criteria orderCriteria) {
        return """
                select * from (select b.id,
                                                      any_value(b.username) userName,
                                                      any_value(b.agent_id) agentId ,
                                                      any_value(IF(b.agent_id = 0, '自然量', pa.agent_name)) agentGame,
                                                      any_value(b.create_time) createTime,
                                                      any_value(b.game_id) gameId,
                                                      any_value(d.game_name) gameName,
                                                      any_value(b.ip) ip,
                                                      any_value(IFNULL(c.total_amount,0)) totalAmount,
                                                      any_value((select count(1) from dm_game_order.t_game_user_role where user_id = b.id)) as userRoleCount,
                                                      MAX(ab.game_id) as lastLoginGameId,
                                                      MAX(ab.game_name) as lastLoginGame,
                                                      MAX(ab.last_login_time) as lastLoginTime,
                                                      any_value(ab.last_order_time) as lastOrderTime
                                               from dm_game_order.t_game_user b
                                                   left join dm_game_order.t_pitcher_agent pa on pa.id = b.agent_id and pa.source_system = 'ZX_ONE'
                                                        left join game_ads.ads_player_recharge_ranking c on b.id = c.pitcher_id and c.source_system = 'ZX_ONE'
                                                   left join (
                                                         SELECT
                                                             a.user_id,
                                   a.game_id,
                                   b.game_name,
                                   a.create_time AS last_login_time,
                                   latest_order.last_order_time
                               FROM
                                   dm_game_order.t_user_login_log a
                               LEFT JOIN
                                   dm_game_order.t_game b
                                   ON a.game_id = b.id
                                   AND b.source_system = 'ZX_ONE'
                               LEFT JOIN
                                   (SELECT
                                        user_id,
                                        MAX(create_time) AS last_order_time
                                    FROM
                                        dm_game_order.t_game_order
                                    WHERE
                                        source_system = 'ZX_ONE'
                                    GROUP BY
                                        user_id) latest_order
                                   ON a.user_id = latest_order.user_id
                               ORDER BY
                                   a.create_time DESC
                                               ) ab on ab.user_id = b.id
                                               left join dm_game_order.t_game d on b.game_id = d.id and d.source_system = 'ZX_ONE'
                                               """ + criteria + """
                                 group by b.id) a
                """ + orderCriteria;
    }

    private String getRoleDetailList(Criteria criteria) {
        return """
                select role_id  roleId ,any_value(role_name) roleName,game_id  gameId,any_value(game_name) gameName,ip,server_id serverId,any_value(server_name) serverName,user_id userId,any_value(user_name) userName from game_ads.role_ip_monitor
                """ + criteria + """
                    group by role_id,game_id,ip,server_id,user_id
                """;
    }

    /**
     * ipRoleCount 同ip的角色数量
     */
    private String getIpDetailListSql(Criteria criteria) {
        return """
                 select * from game_ads.role_ip_monitor
                """ + criteria + """
                order by role_level desc
                """;
    }


    private String getCountryListSql(Criteria criteria) {
        return """
                select country,server_id as serverId,parent_name as gameName,server_name as serverName,parent_game_id as gameId from dm_game_order.t_server_country
                """ + criteria;
    }

    private String getGameCountryRoleListSql(Criteria criA, Criteria orderCri) {
        return """
                select a.country as country,a.role_id as roleId,a.user_id as userId ,any_value(a.role_name) as roleName ,any_value(a.role_level) as roleLevel ,any_value(combat_num) as combatNum,
                                                                                                            a.server_id as serverId ,any_value(a.server_name) as serverName ,any_value(create_time) as createTime,SUM(IFNULL(b.amount,0)) as roleTotalAmount
                                                                                                            from dm_game_order.t_game_user_role a
                                                                                                            left join game_dw.dw_order_day_amount b on a.role_id = b.role_id and a.source_system = b.source_system  and a.user_id = b.user_id
                                                                                                            """ + criA + """
                 GROUP BY a.country, a.role_id, a.user_id, a.server_id
                """ + orderCri;
    }

    private String getGameCountryRoleCountSql(Criteria criA) {
        return """
                select count(1) from (select a.country as country,a.role_id as roleId,a.user_id as userId ,any_value(a.role_name) as roleName ,any_value(a.role_level) as roleLevel ,any_value(combat_num) as combatNum,
                                                                                                                                               a.server_id as serverId ,any_value(a.server_name) as serverName ,any_value(create_time) as createTime,SUM(IFNULL(b.amount,0)) as roleTotalAmount
                                                                                                                                               from dm_game_order.t_game_user_role a
                                                                                                                                               left join game_dw.dw_order_day_amount b on a.role_id = b.role_id and a.source_system = b.source_system  and a.user_id = b.user_id
                                                                                                                                               """ + criA + """
                                                    GROUP BY a.country, a.role_id, a.user_id, a.server_id) a
                """;
    }

    private Criteria getSqlByQuery(RoleGameCountryParamDTO dto) {
        Criteria cri = Cnd.cri();
        if (StringUtils.isNotEmpty(dto.getCountry())) {
            cri.where().andEquals("a.country", dto.getCountry());
        }
        if (StringUtils.isNotEmpty(dto.getServerId())) {
            cri.where().andEquals("a.server_id", dto.getServerId());
        }
        cri.where().andInList("b.order_super_game_id", List.of(12L));
        if (dto.getCountryLevel() != null && dto.getCountryLevel() == 0) {
            cri.where().andLTE("a.role_level", 13);
        }
        if (dto.getCountryLevel() != null && dto.getCountryLevel() == 1) {
            cri.where().andGT("a.role_level", 13);
        }
        return cri;
    }

    private Criteria getSqlByQuery(GameCountryParamDTO dto) {
        Criteria criA = Cnd.cri();
        //如果帮派为空就取4个帮派
        if (CollectionUtils.isEmpty(dto.getGameCountryList())) {
            String countryListSql = getCountryDataListSql(dto);
            Sql sql = Sqls.create(countryListSql);
            sql.setCallback(Sqls.callback.strList());
            dao.execute(sql);
            List<String> list = sql.getList(String.class);
            if (CollectionUtils.isNotEmpty(list)) {
                criA.where().andInStrList("cs", list);
            }
        } else {
            criA.where().andInStrList("cs", dto.getGameCountryList());
        }
        if (CollectionUtils.isNotEmpty(dto.getGameId())) {
            criA.where().andInList("game_id", dto.getGameId());
        }
        if (CollectionUtils.isNotEmpty(dto.getServerId())) {
            criA.where().andInStrList("server_id", dto.getServerId());
        }
        return criA;
    }

    private String getGameCountryListSql(Criteria criA) {
        return """
                select country,
                                       country_level,
                                       sum(role_total_amount) as role_total_amount,
                                       count(role_id) as role_count,
                                       sum(active) as active_count,
                                       server_id,
                                       ANY_VALUE(cs) cs,
                                       ANY_VALUE(game_id) gameId,
                                       ANY_VALUE(server_name) serverName
                                from (select a.country,
                                                      a.game_id,
                                                      a.server_name,
                                                      (select IFNULL(sum(amount),0) as amount  from game_dw.dw_order_day_amount where role_id = b.role_id and source_system = 'ZX_ONE' and user_id = b.user_id) as role_total_amount,
                                                      if(b.role_level <= 13, 0, 1)      as country_level,
                                                      if(d.role_id is not null, 1, 0) as active,
                                                      a.server_id,
                                                      concat(a.country,',',a.server_id) as cs,
                                                        b.role_id role_id
                                               from dm_game_order.t_server_country a
                                                        left join dm_game_order.t_game_user_role b on a.country = b.country and a.server_id = b.server_id
                                               	left join (
                                	select role_id,ROW_NUMBER()over(partition by role_id) as num from game_dw.dw_active_log
                                	where game_id in (35,36,10035) and server_id != '999'
                                	and dt = Date(NOW()) ) d  on b.role_id = d.role_id and d.num = 1
                                               ) a
                 """ + criA + """
                                group by country_level,country,server_id   order by role_count desc
                """;
    }

    private String getCountryDataListSql(GameCountryParamDTO dto) {
        Criteria criA = Cnd.cri();
        if (CollectionUtils.isNotEmpty(dto.getGameId())) {
            criA.where().andInList("a.game_id", dto.getGameId());
        }
        if (CollectionUtils.isNotEmpty(dto.getServerId())) {
            criA.where().andInStrList("a.server_id", dto.getServerId());
        }
        criA.where().andNotIsNull("a.country");
        criA.where().andNotEquals("a.country", "");
        criA.where().andNotEquals("a.country", "null");
        criA.where().andInList("b.super_game_id", List.of(12L));
        criA.where().andNotEquals("a.server_id", "999");
        return """
                select concat(a.country,',',a.server_id)
                from dm_game_order.t_game_user_role a
                left join dm_game_order.t_game b on a.game_id = b.id and b.source_system = 'ZX_ONE'
                """ + criA + """
                group by a.country,a.server_id order by count(a.role_id) desc limit 4
                  """;
    }

    private String getIpInfoListSql(Criteria criA) {
        return """
                               SELECT
                                 a.*,
                                 a.role_count_group_filters_count as ip_role_count_filter,
                                 a.role_user_group_filters_count as role_user_ip_count_filter,
                                 game.game_name
                               FROM game_ads.role_ip_monitor a
                               LEFT JOIN dm_game_order.t_game game ON a.game_id = game.id AND game.source_system = 'ZX_ONE'
                """ + criA;
    }

    private Criteria getSqlByQuery(RoleIpInfoParamDTO dto) {
        Criteria criA = Cnd.cri();
        if (dto.getUserId() != null) {
            //玩家id
            criA.where().andEquals("a.user_id", dto.getUserId());
        }
        if (StringUtils.isNotEmpty(dto.getUserName())) {
            //玩家名称
            criA.where().andLike("a.user_name", dto.getUserName());
        }
        if (StringUtils.isNotEmpty(dto.getServerName())) {
            //区服名称
            criA.where().andLike("a.server_name", dto.getServerName());
        }
        if (CollectionUtils.isNotEmpty(dto.getServerId())) {
            //区服id
            criA.where().andInStrList("a.server_id", dto.getServerId());
        }
        if (StringUtils.isNotEmpty(dto.getGameName())) {
            //游戏名称
            criA.where().andLike("a.game_name", dto.getGameName());
        }
        if (CollectionUtils.isNotEmpty(dto.getGameId())) {
            //游戏id
            criA.where().andInList("a.game_id", dto.getGameId());
        }
        //超父id,必传
        criA.where().andInList("game.super_game_id", List.of(12L));
        if (StringUtils.isNotEmpty(dto.getRoleId())) {
            //角色id
            criA.where().andEquals("a.role_id", dto.getRoleId());
        }
        if (StringUtils.isNotEmpty(dto.getRoleName())) {
            //角色名称
            criA.where().andLike("a.role_name", dto.getRoleName());
        }
        //角色等级区间筛选
        if (dto.getRoleLevelMin() != null) {
            criA.where().andGTE("a.role_level", dto.getRoleLevelMin());
        }
        if (dto.getRoleLevelMax() != null) {
            criA.where().andLTE("a.role_level", dto.getRoleLevelMax());
        }
        //角色创建时间
        if (dto.getCreateTimeMin() != null && dto.getCreateTimeMax() != null) {
            criA.where().andBetween("a.create_time", dto.getCreateTimeMin().atTime(LocalTime.MIN), dto.getCreateTimeMax().atTime(LocalTime.MAX));
        }
        //注册ip
        if (StringUtils.isNotEmpty(dto.getRegIp())) {
            criA.where().andEquals("a.ip", dto.getRegIp());
        }
        return criA;
    }

    private String getIpInfoListCountSql(Criteria criA) {
        return """
                      select count(1) from ( SELECT
                  a.*,
                  a.role_count_group_filters_count as ip_role_count_filter,
                  a.role_user_group_filters_count as role_user_ip_count_filter,
                  game.game_name
                FROM game_ads.role_ip_monitor a
                LEFT JOIN dm_game_order.t_game game ON a.game_id = game.id AND game.source_system = 'ZX_ONE'
                """ + criA + """
                                             ) a
                """;
    }

    private Criteria getSqlByQuery(RoleLevelParamDTO dto) {
        Criteria criA = Cnd.cri();
        if (dto.getGsId() != null) {
            //gsId
            criA.where().andEquals("b.gs_id", dto.getGsId());
        }
        if (dto.getPitcherId() != null) {
            //投手id
            criA.where().andEquals("c.pitcher_id", dto.getPitcherId());
        }
        if (dto.getCustomerServiceId() != null) {
            //客服id
            criA.where().andEquals("b.customer_service_id", dto.getCustomerServiceId());
        }
        if (dto.getOperUserId() != null) {
            //运营id
            criA.where().andEquals("b.oper_user_id", dto.getOperUserId());
        }
        if (CollectionUtils.isNotEmpty(dto.getUserId())) {
            //玩家id
            criA.where().andInList("a.user_id", dto.getUserId());
        }
        if (StringUtils.isNotEmpty(dto.getUserName())) {
            //玩家名称
            criA.where().andLike("a.user_name", dto.getUserName());
        }
        if (StringUtils.isNotEmpty(dto.getServerName())) {
            //区服名称
            criA.where().andLike("a.server_name", dto.getServerName());
        }
        if (CollectionUtils.isNotEmpty(dto.getServerId())) {
            //区服id
            criA.where().andInStrList("a.server_id", dto.getServerId());
        }
        if (StringUtils.isNotEmpty(dto.getGameName())) {
            //游戏名称
            criA.where().andLike("a.game_name", dto.getGameName());
        }
        if (CollectionUtils.isNotEmpty(dto.getGameId())) {
            //游戏id
            criA.where().andInList("a.game_id", dto.getGameId());
        }
        if (StringUtils.isNotEmpty(dto.getRoleId())) {
            //角色id
            criA.where().andEquals("a.role_id", dto.getRoleId());
        }
        if (StringUtils.isNotEmpty(dto.getRoleName())) {
            //角色名称
            criA.where().andLike("a.role_name", dto.getRoleName());
        }
        //角色等级区间筛选
        if (dto.getRoleLevelMin() != null) {
            criA.where().andGTE("a.role_level", dto.getRoleLevelMin());
        }
        if (dto.getRoleLevelMax() != null) {
            criA.where().andLTE("a.role_level", dto.getRoleLevelMax());
        }
        //角色创建时间
        if (dto.getCreateTimeMin() != null && dto.getCreateTimeMax() != null) {
            criA.where().andBetween("a.create_time", dto.getCreateTimeMin().atTime(LocalTime.MIN), dto.getCreateTimeMax().atTime(LocalTime.MAX));
        }
        //等级上报时间
        if (dto.getLevelTimeMin() != null && dto.getLevelTimeMax() != null) {
            criA.where().andBetween("a.level_time", dto.getLevelTimeMin().atTime(LocalTime.MIN), dto.getLevelTimeMax().atTime(LocalTime.MAX));
        }
        if (StringUtils.isNotEmpty(dto.getRemark())) {
            criA.where().andLike("b.remark", dto.getRemark());
        }

        return criA;
    }

    private String getLevelListSql(Criteria criA) {
        return """
                      select e.country             as country,
                      a.role_id             as roleId,
                      a.role_name           as roleName,
                      a.user_id             as userId,
                      a.user_name           as userName,
                      a.game_id             as gameId,
                      a.game_name           as gameName,
                      a.server_id           as serverId,
                      a.server_name         as serverName,
                      a.create_time         as createTime,
                      a.role_level          as roleLevel,
                      a.level_time          as levelTime,
                      a.agent_id            as agentId,
                      a.import_user as importUser,
                      b.gs_id               as gsId,
                      b.customer_service_id as customerServiceId,
                      b.oper_user_id        as operUserId,
                      b.remark as remark,
                      c.pitcher_id          as pitcherId,
                      d.ip,
                      d.ip_count as ipCount,
                      d.user_count as userCount,
                      d.role_count as roleCount,
                      d.user_role_count as userRoleCount,
                      d.role_user_ip_count as roleUserIpCount,
                      d.role_count_group_filters_count as ipRoleCountFilter,
                      d.role_user_group_filters_count as roleUserIpCountFilter,
                      IFNULL(d.suspected_count,0) as suspectedCount
                from game_ads.ads_role_level_log a
                        left join dm_game_order.t_role_operate b on a.user_id = b.user_id
                   and a.role_id = b.role_id
                   and a.server_id = b.server_id
                   and a.game_id = b.game_id
                        left join dm_game_order.t_pitcher_agent c on a.agent_id = c.id and c.source_system = 'ZX_ONE'
                        left join game_ads.role_ip_monitor d on a.role_id = d.role_id  and  a.user_id = d.user_id
                   and a.user_id = d.user_id
                   and a.server_id = d.server_id
                   and a.game_id = d.game_id
                    left join dm_game_order.t_game_user_role e on a.user_id = e.user_id and a.role_id = e.role_id and e.source_system = 'ZX_ONE' and e.country != 'null' and e.country !=''
                         """ + criA;
    }

    private String getLevelListCountSql(Criteria criA) {
        return """
                select count(1) from (
                 select e.country             as country,
                        a.role_id             as roleId,
                        a.role_name           as roleName,
                        a.user_id             as userId,
                        a.user_name           as userName,
                        a.game_id             as gameId,
                        a.game_name           as gameName,
                        a.server_id           as serverId,
                        a.server_name         as serverName,
                        a.create_time         as createTime,
                        a.role_level          as roleLevel,
                        a.level_time          as levelTime,
                        a.agent_id            as agentId,
                        b.gs_id               as gsId,
                        b.customer_service_id as customerServiceId,
                        b.oper_user_id        as operUserId,
                           b.remark as remark,
                        c.pitcher_id          as pitcherId,
                        d.ip,
                        d.ip_count,
                        d.user_count,
                        d.role_count,
                        d.user_role_count,
                        d.role_user_ip_count
                        from
                      game_ads.ads_role_level_log a
                          left join dm_game_order.t_role_operate b on a.user_id = b.user_id
                     and a.role_id = b.role_id
                     and a.server_id = b.server_id
                     and a.game_id = b.game_id
                          left join dm_game_order.t_pitcher_agent c on a.agent_id = c.id and c.source_system = 'ZX_ONE'
                          left join game_ads.role_ip_monitor d on a.role_id = d.role_id
                     and a.user_id = d.user_id
                     and a.server_id = d.server_id
                     and a.game_id = d.game_id
                      left join dm_game_order.t_game_user_role e on a.user_id = e.user_id and a.role_id = e.role_id and e.source_system = 'ZX_ONE' and e.country != 'null' and e.country !=''
                       """ + criA + """
                                     ) a
                """;
    }


    /**
     * 补充查询发送消息任务结果sql
     *
     * @return String
     */
    private String getTaskResultSql() {
        return """
                select
                    a.role_name,
                    a.role_id,
                    a.server_name,
                    a.create_time,
                    a.role_vip,
                    b.game_name
                    from dm_game_order.t_game_user_role a
                         left join dm_game_order.t_game b on a.source_system= b.source_system and a.game_id = b.id
                """;

    }


    /**
     * 查询游戏sql
     *
     * @return String
     */
    private String getGameSql() {
        return """
                 SELECT
                            -- 游戏名称、游戏类型、父游戏id、超父游戏id
                            source_system,
                            id,
                            game_name,
                            classify,
                            parent_id,
                            super_game_id
                        FROM dm_game_order.t_game
                        where source_system = 'ZX_ONE'
                """;
    }


    /**
     * 查询超父游戏sql
     *
     * @return String
     */
    private String getSuperGameSql() {
        return """
                 SELECT
                            *
                        FROM dm_game_order.t_game_super
                        where source_system = 'ZX_ONE'
                """;
    }

    /**
     * 原始服-父游戏
     *
     * @param dto RoleCombatRankingDTO
     * @return Page<Map>
     */
    private Page<Map> getOriginParentData(RoleCombatRankingDTO dto) {
        //查询条件
        Criteria cri = Cnd.cri();
        if (dto.getParentGameId() != null) {
            //父游戏ID
            cri.where().andEquals("parent_game_id", dto.getParentGameId());
        }
        if (dto.getBeginDate() != null && dto.getEndDate() != null) {
            //原始服开服时间
            cri.where().andBetween("dt", dto.getBeginDate(), dto.getEndDate());
        }
        if (CollectionUtils.isNotEmpty(dto.getServerIds())) {
            //原始区服列表
            cri.where().andIn("server_id", dto.getServerIds().toArray(String[]::new));
        }
        if (StringUtils.isNotBlank(dto.getServerName())) {
            //区服名称
            cri.where().andLike("server_name", dto.getServerName());
        }
        if (StringUtils.isNotBlank(dto.getSourceSystem())) {
            cri.where().andEquals("source_system", dto.getSourceSystem());
        }
        //拼接分组条件
        cri.getGroupBy().groupBy("source_system", "server_id", "boss_server_id", "parent_game_id", "super_game_id");
        //分页
        Pager pager = dao.createPager(dto.getPageNum(), dto.getPageSize());
        //查询总记录数
        Sql countSql = Sqls.create(getRoleCombatRankServerDataSqlACount(cri));
        countSql.setCallback(Sqls.callback.integer());
        dao.execute(countSql);
        pager.setRecordCount(countSql.getInt());
        //排序条件
        if (StringUtils.isBlank(dto.getSortType())) {
            dto.setSortType(OrderByEnum.DESC.getOrderType());
        }
        if (StringUtils.isNotBlank(dto.getSortFiled())) {
            cri.getOrderBy().orderBy(dto.getSortFiled(), dto.getSortType());
        } else {
            //默认排序
            cri.getOrderBy().orderBy("dt", dto.getSortType());
            cri.getOrderBy().orderBy("server_combat_num", dto.getSortType());
        }
        //区服数据
        Sql sql = Sqls.create(getRoleCombatRankServerDataSqlA() + cri);
        sql.setCallback(Sqls.callback.maps());
        sql.setPager(pager);
        dao.execute(sql);

        //获取角色信息
        //查询条件
        Criteria criRole = Cnd.cri();
        if (dto.getParentGameId() != null) {
            //父游戏ID
            criRole.where().andEquals("parent_game_id", dto.getParentGameId());
        }
        if (dto.getBeginDate() != null && dto.getEndDate() != null) {
            //原始服开服时间
            criRole.where().andBetween("dt", dto.getBeginDate(), dto.getEndDate());
        }
        if (CollectionUtils.isNotEmpty(dto.getServerIds())) {
            //原始服区服列表
            criRole.where().andIn("server_id", dto.getServerIds().toArray(String[]::new));
        }
        if (StringUtils.isNotBlank(dto.getServerName())) {
            //区服名称
            cri.where().andLike("server_name", dto.getServerName());
        }
        if (StringUtils.isNotBlank(dto.getSourceSystem())) {
            criRole.where().andEquals("source_system", dto.getSourceSystem());
        }
        if (dto.getRankingNum() != null) {
            //排名的个数
            criRole.where().andLTE("rank_num", dto.getRankingNum());
        } else {
            //排名的个数
            criRole.where().andLTE("rank_num", 5);
        }
        Sql sqlRole = Sqls.create(getRoleCombatRankRoleDataSqlA() + criRole);
        sqlRole.setCallback(Sqls.callback.maps());
        dao.execute(sqlRole);
        //得到数据Map   （原始服ID-合服ID-父游戏ID）- （角色信息）
        Map<String, List<Map<String, Object>>> roleDataMap = new HashMap<>();
        //得到结果
        List<Map> listRole = sqlRole.getList(Map.class).stream().map(map -> {
            StringBuilder sb = new StringBuilder(StringUtils.EMPTY);
            sb.append(map.get("server_id"))
                    .append("-")
                    .append((String) map.get("boss_server_id"))
                    .append("-")
                    .append((String) map.get("parent_game_id"));
            if (roleDataMap.get(sb.toString()) != null) {
                //含有改主键 添加数据
                roleDataMap.get(sb.toString()).add(map);
            } else {
                //不含有该主键
                List<Map<String, Object>> tempList = new ArrayList<>();
                tempList.add(map);
                roleDataMap.put(String.valueOf(sb), tempList);
            }
            return map;
        }).collect(Collectors.toList());

        List<Map> list = sql.getList(Map.class).stream().map(map -> {
            StringBuilder sb = new StringBuilder(StringUtils.EMPTY);
            sb.append(map.get("server_id"))
                    .append("-")
                    .append((String) map.get("boss_server_id"))
                    .append("-")
                    .append((String) map.get("parent_game_id"));

            if (roleDataMap.get(sb.toString()) != null) {
                //将排名信息添加
                map.put("role_rank_date", roleDataMap.get(sb.toString()));
            }
            return map;

        }).collect(Collectors.toList());

        return new Page<>(list, pager);
    }

    /**
     * 合服-父游戏
     *
     * @param dto RoleCombatRankingDTO
     * @return Page<Map>
     */
    private Page<Map> getMergeParentData(RoleCombatRankingDTO dto) {
        //查询条件
        Criteria cri = Cnd.cri();
        if (dto.getParentGameId() != null) {
            //父游戏ID
            cri.where().andEquals("parent_game_id", dto.getParentGameId());
        }
        if (dto.getBeginDate() != null && dto.getEndDate() != null) {
            //合服开服时间
            cri.where().andBetween("DATE(merge_time)", dto.getBeginDate(), dto.getEndDate());
        }
        if (CollectionUtils.isNotEmpty(dto.getServerIds())) {
            //合服区服列表
            cri.where().andIn("boss_server_id", dto.getServerIds().toArray(String[]::new));
        }
        if (StringUtils.isNotBlank(dto.getSourceSystem())) {
            cri.where().andEquals("source_system", dto.getSourceSystem());
        }
        if (StringUtils.isNotBlank(dto.getServerName())) {
            //区服名称
            cri.where().andLike("boss_server_name", dto.getServerName());
        }
        if (CollectionUtils.isEmpty(dto.getServerIds()) && StringUtils.isBlank(dto.getServerName())) {
            //默认不传查询的区服，只看最新的合服数据
            cri.where().andEquals("is_merge", 0);
        }
        //ture为参与过的 查询数据为1的是参与的 0是未参与的
//        if (dto.getIsParticipateMerge() != null&&dto.getIsParticipateMerge()) {
//            cri.where().andEquals("is_merge", 1);
//        }else {
//            cri.where().andEquals("is_merge", 0);
//        }
        //拼接分组条件
        cri.getGroupBy().groupBy("source_system", "boss_server_id", "parent_game_id", "super_game_id");
        //分页
        Pager pager = dao.createPager(dto.getPageNum(), dto.getPageSize());
        //查询总记录数
        Sql countSql = Sqls.create(getRoleCombatRankServerDataSqlBCount(cri));
        countSql.setCallback(Sqls.callback.integer());
        dao.execute(countSql);
        pager.setRecordCount(countSql.getInt());
        //排序条件
        if (StringUtils.isBlank(dto.getSortType())) {
            dto.setSortType(OrderByEnum.DESC.getOrderType());
        }
        if (StringUtils.isNotBlank(dto.getSortFiled())) {
            cri.getOrderBy().orderBy(dto.getSortFiled(), dto.getSortType());
        } else {
            //默认排序
            cri.getOrderBy().orderBy("merge_time", dto.getSortType());
            cri.getOrderBy().orderBy("server_combat_num", dto.getSortType());
        }
        //查询出区服相关数据
        Sql sql = Sqls.create(getRoleCombatRankServerDataSqlB() + cri);
        sql.setCallback(Sqls.callback.maps());
        sql.setPager(pager);
        dao.execute(sql);
        //获取角色信息
        //查询条件
        Criteria criRole = Cnd.cri();
        if (dto.getParentGameId() != null) {
            //父游戏ID
            criRole.where().andEquals("parent_game_id", dto.getParentGameId());
        }
        if (dto.getBeginDate() != null && dto.getEndDate() != null) {
            //开服时间
            criRole.where().andBetween("DATE(merge_time)", dto.getBeginDate(), dto.getEndDate());
        }
        if (CollectionUtils.isNotEmpty(dto.getServerIds())) {
            //合服区服列表
            criRole.where().andIn("boss_server_id", dto.getServerIds().toArray(String[]::new));
        }
        if (StringUtils.isNotBlank(dto.getServerName())) {
            //区服名称
            cri.where().andLike("boss_server_name", dto.getServerName());
        }
        if (StringUtils.isNotBlank(dto.getSourceSystem())) {
            criRole.where().andEquals("source_system", dto.getSourceSystem());
        }
        if (dto.getRankingNum() != null) {
            //排名的个数
            criRole.where().andLTE("rank_num", dto.getRankingNum());
        } else {
            //排名的个数
            criRole.where().andLTE("rank_num", 5);
        }
        Sql sqlRole = Sqls.create(getRoleCombatRankRoleDataSqlB() + criRole);
        sqlRole.setCallback(Sqls.callback.maps());
        dao.execute(sqlRole);
        //得到数据Map   （合服ID-父游戏ID）- （角色信息）
        Map<String, List<Map<String, Object>>> roleDataMap = new HashMap<>();
        //获取到相关角色排名信息
        List<Map> listRole = sqlRole.getList(Map.class).stream().map(dataMap -> {
            //拼接主键信息
            StringBuilder sb = new StringBuilder(StringUtils.EMPTY);
            sb.append((String) dataMap.get("boss_server_id"))
                    .append("-")
                    .append((String) dataMap.get("parent_game_id"));
            if (roleDataMap.get(sb.toString()) != null) {
                //含有改主键 添加数据
                roleDataMap.get(sb.toString()).add(dataMap);
            } else {
                //不含有该主键
                List<Map<String, Object>> tempList = new ArrayList<>();
                tempList.add(dataMap);
                roleDataMap.put(String.valueOf(sb), tempList);
            }
            return dataMap;
        }).collect(Collectors.toList());
        //将排名信息拼接到区服信息上
        List<Map> list = sql.getList(Map.class).stream().map(dataMap -> {
            //主键
            StringBuilder sb = new StringBuilder(StringUtils.EMPTY);
            sb.append((String) dataMap.get("boss_server_id"))
                    .append("-")
                    .append((String) dataMap.get("parent_game_id"));
            if (roleDataMap.get(sb.toString()) != null) {
                //将排名信息添加
                dataMap.put("role_rank_date", roleDataMap.get(sb.toString()));
            }
            //清除"null"字符串
            if ("null".equals(dataMap.get("son_server_ids"))) {
                dataMap.put("son_server_ids", StringUtils.EMPTY);
            }
            if ("null".equals(dataMap.get("source_server_ids"))) {
                dataMap.put("source_server_ids", StringUtils.EMPTY);
            }
            return dataMap;
        }).collect(Collectors.toList());

        return new Page<>(list, pager);
    }

    /**
     * 原始服-超父游戏
     *
     * @param dto RoleCombatRankingDTO
     * @return Page<Map>
     */
    private Page<Map> getOriginSuperParentData(RoleCombatRankingDTO dto) {
        //查询条件
        Criteria cri = Cnd.cri();
        if (dto.getSuperParentGameId() != null) {
            //超父游戏ID
            cri.where().andEquals("super_game_id", dto.getSuperParentGameId());
        }
        if (dto.getBeginDate() != null && dto.getEndDate() != null) {
            //原始服开服时间
            cri.where().andBetween("DATE(dt)", dto.getBeginDate(), dto.getEndDate());
        }
        if (CollectionUtils.isNotEmpty(dto.getServerIds())) {
            //原始区服列表
            cri.where().andIn("server_id", dto.getServerIds().toArray(String[]::new));
        }
        if (StringUtils.isNotBlank(dto.getServerName())) {
            //区服名称
            cri.where().andLike("server_name", dto.getServerName());
        }
        if (StringUtils.isNotBlank(dto.getSourceSystem())) {
            cri.where().andEquals("source_system", dto.getSourceSystem());
        }
        //原始服可以参与多次合服，只看最新一次的合服的原始服信息  是否参与过合服:1是，0否
//        if(dto.getIsParticipateMerge()!=null&&dto.getIsParticipateMerge()){
//            cri.where().andEquals("is_merge", 1);
//        }else {
        cri.where().andEquals("is_merge", 0);
//        }

        /*//拼接分组条件
        cri.getGroupBy().groupBy("source_system" , "server_id" , "boss_server_id" , "super_game_id");*/
        //分页
        Pager pager = dao.createPager(dto.getPageNum(), dto.getPageSize());
        //查询总记录数
        Sql countSql = Sqls.create(getRoleCombatRankServerDataSqlCCount(cri));
        countSql.setCallback(Sqls.callback.integer());
        dao.execute(countSql);
        pager.setRecordCount(countSql.getInt());
        //排序条件
        if ("min_server_start_days".equals(dto.getSortFiled())) {
            //原始区服没有 最小区服排序条件
            dto.setSortFiled(null);
        }
        if (StringUtils.isBlank(dto.getSortType())) {
            dto.setSortType(OrderByEnum.DESC.getOrderType());
        }
        if (StringUtils.isNotBlank(dto.getSortFiled())) {
            cri.getOrderBy().orderBy(dto.getSortFiled(), dto.getSortType());
        } else {
            //默认排序
            cri.getOrderBy().orderBy("dt", dto.getSortType());
            cri.getOrderBy().orderBy("server_combat_num", dto.getSortType());
        }
        //区服数据
        Sql sql = Sqls.create(getRoleCombatRankServerDataSqlC() + cri);
        sql.setCallback(Sqls.callback.maps());
        sql.setPager(pager);
        dao.execute(sql);

        //获取角色信息
        //查询条件
        Criteria criRole = Cnd.cri();
        if (dto.getSuperParentGameId() != null) {
            //超父游戏ID
            criRole.where().andEquals("super_game_id", dto.getSuperParentGameId());
        }
        if (dto.getBeginDate() != null && dto.getEndDate() != null) {
            //原始服开服时间
            criRole.where().andBetween("dt", dto.getBeginDate(), dto.getEndDate());
        }
        if (CollectionUtils.isNotEmpty(dto.getServerIds())) {
            //原始服区服列表
            criRole.where().andIn("server_id", dto.getServerIds().toArray(String[]::new));
        }
        if (StringUtils.isNotBlank(dto.getServerName())) {
            //区服名称
            cri.where().andLike("server_name", dto.getServerName());
        }
        if (StringUtils.isNotBlank(dto.getSourceSystem())) {
            criRole.where().andEquals("source_system", dto.getSourceSystem());
        }
        if (dto.getRankingNum() != null) {
            //排名的个数
            criRole.where().andLTE("rank_num", dto.getRankingNum());
        } else {
            //排名的个数
            criRole.where().andLTE("rank_num", 5);
        }
        Sql sqlRole = Sqls.create(getRoleCombatRankRoleDataSqlC() + criRole);
        sqlRole.setCallback(Sqls.callback.maps());
        dao.execute(sqlRole);
        //得到数据Map   （原始服ID-超父游戏ID）- （角色信息）
        Map<String, List<Map<String, Object>>> roleDataMap = new HashMap<>();
        //得到结果
        List<Map> listRole = sqlRole.getList(Map.class).stream().map(map -> {
            StringBuilder sb = new StringBuilder(StringUtils.EMPTY);
            sb.append(map.get("server_id"))
                    .append("-")
                    .append((String) map.get("super_game_id"));
            if (roleDataMap.get(sb.toString()) != null) {
                //含有改主键 添加数据
                roleDataMap.get(sb.toString()).add(map);
            } else {
                //不含有该主键
                List<Map<String, Object>> tempList = new ArrayList<>();
                tempList.add(map);
                roleDataMap.put(String.valueOf(sb), tempList);
            }
            return map;
        }).collect(Collectors.toList());

        List<Map> list = sql.getList(Map.class).stream().map(map -> {
            StringBuilder sb = new StringBuilder(StringUtils.EMPTY);
            sb.append(map.get("server_id"))
                    .append("-")
                    .append((String) map.get("super_game_id"));

            if (roleDataMap.get(sb.toString()) != null) {
                //将排名信息添加
                map.put("role_rank_date", roleDataMap.get(sb.toString()));
            }
            return map;

        }).collect(Collectors.toList());

        return new Page<>(list, pager);
    }

    /**
     * 合服-超父游戏
     *
     * @param dto RoleCombatRankingDTO
     * @return Page<Map>
     */
    private Page<Map> getMergeSuperParentData(RoleCombatRankingDTO dto) {
        //查询条件
        Criteria cri = Cnd.cri();
        if (dto.getSuperParentGameId() != null) {
            //超父游戏ID
            cri.where().andEquals("super_game_id", dto.getSuperParentGameId());
        }
        if (dto.getBeginDate() != null && dto.getEndDate() != null) {
            //合服开服时间
            cri.where().andBetween("DATE(dt)", dto.getBeginDate(), dto.getEndDate());
        }
        if (CollectionUtils.isNotEmpty(dto.getServerIds())) {
            //合服区服列表
            cri.where().andIn("server_id", dto.getServerIds().toArray(String[]::new));
        }
        if (StringUtils.isNotBlank(dto.getSourceSystem())) {
            cri.where().andEquals("source_system", dto.getSourceSystem());
        }
        if (StringUtils.isNotBlank(dto.getServerName())) {
            //区服名称
            cri.where().andLike("server_name", dto.getServerName());
        }
        if (CollectionUtils.isEmpty(dto.getServerIds()) && StringUtils.isBlank(dto.getServerName())) {
            //默认不传查询的区服，只看最新的合服数据
            cri.where().andEquals("is_merge", 0);
        }
        //ture为参与过的 查询数据为1的是参与的 0是未参与的
//        if (dto.getIsParticipateMerge() != null&&dto.getIsParticipateMerge()) {
//            cri.where().andEquals("is_merge", 1);
//        }else {
//            cri.where().andEquals("is_merge", 0);
//        }
        /*//拼接分组条件
        cri.getGroupBy().groupBy("source_system" , "boss_server_id" , "super_game_id");*/
        //分页
        Pager pager = dao.createPager(dto.getPageNum(), dto.getPageSize());
        //查询总记录数
        Sql countSql = Sqls.create(getRoleCombatRankServerDataSqlDCount(cri));
        countSql.setCallback(Sqls.callback.integer());
        dao.execute(countSql);
        pager.setRecordCount(countSql.getInt());
        //排序条件
        if (StringUtils.isBlank(dto.getSortType())) {
            dto.setSortType(OrderByEnum.DESC.getOrderType());
        }
        if (StringUtils.isNotBlank(dto.getSortFiled())) {
            cri.getOrderBy().orderBy(dto.getSortFiled(), dto.getSortType());
        } else {
            //默认排序
            cri.getOrderBy().orderBy("min_server_start_days", dto.getSortType());
            cri.getOrderBy().orderBy("server_combat_num", dto.getSortType());
        }
        //查询出区服相关数据
        Sql sql = Sqls.create(getRoleCombatRankServerDataSqlD() + cri);
        sql.setCallback(Sqls.callback.maps());
        sql.setPager(pager);
        dao.execute(sql);
        //获取角色信息
        //查询条件
        Criteria criRole = Cnd.cri();
        if (dto.getSuperParentGameId() != null) {
            //超父游戏ID
            criRole.where().andEquals("super_game_id", dto.getSuperParentGameId());
        }
        if (dto.getBeginDate() != null && dto.getEndDate() != null) {
            //开服时间
            criRole.where().andBetween("DATE(merge_time)", dto.getBeginDate(), dto.getEndDate());
        }
        if (CollectionUtils.isNotEmpty(dto.getServerIds())) {
            //合服区服列表
            criRole.where().andIn("boss_server_id", dto.getServerIds().toArray(String[]::new));
        }
        if (StringUtils.isNotBlank(dto.getServerName())) {
            //区服名称
            cri.where().andLike("boss_server_name", dto.getServerName());
        }
        if (StringUtils.isNotBlank(dto.getSourceSystem())) {
            criRole.where().andEquals("source_system", dto.getSourceSystem());
        }
        if (dto.getRankingNum() != null) {
            //排名的个数
            criRole.where().andLTE("rank_num", dto.getRankingNum());
        } else {
            //排名的个数
            criRole.where().andLTE("rank_num", 5);
        }
        Sql sqlRole = Sqls.create(getRoleCombatRankRoleDataSqlD() + criRole);
        sqlRole.setCallback(Sqls.callback.maps());
        dao.execute(sqlRole);
        //得到数据Map   （合服ID-超父游戏ID）- （角色信息）
        Map<String, List<Map<String, Object>>> roleDataMap = new HashMap<>();
        //获取到相关角色排名信息
        List<Map> listRole = sqlRole.getList(Map.class).stream().map(dataMap -> {
            //拼接主键信息
            StringBuilder sb = new StringBuilder(StringUtils.EMPTY);
            sb.append((String) dataMap.get("boss_server_id"))
                    .append("-")
                    .append((String) dataMap.get("super_game_id"));
            if (roleDataMap.get(sb.toString()) != null) {
                //含有改主键 添加数据
                roleDataMap.get(sb.toString()).add(dataMap);
            } else {
                //不含有该主键
                List<Map<String, Object>> tempList = new ArrayList<>();
                tempList.add(dataMap);
                roleDataMap.put(String.valueOf(sb), tempList);
            }
            return dataMap;
        }).collect(Collectors.toList());
        //将排名信息拼接到区服信息上
        List<Map> list = sql.getList(Map.class).stream().map(dataMap -> {
            //主键
            StringBuilder sb = new StringBuilder(StringUtils.EMPTY);
            sb.append((String) dataMap.get("server_id"))
                    .append("-")
                    .append((String) dataMap.get("super_game_id"));
            if (roleDataMap.get(sb.toString()) != null) {
                //将排名信息添加
                dataMap.put("role_rank_date", roleDataMap.get(sb.toString()));
            }
            //清除"null"字符串
            if ("null".equals(dataMap.get("son_server_ids"))) {
                dataMap.put("son_server_ids", StringUtils.EMPTY);
            }
            if ("null".equals(dataMap.get("source_server_ids"))) {
                dataMap.put("source_server_ids", StringUtils.EMPTY);
            }
            return dataMap;
        }).collect(Collectors.toList());

        return new Page<>(list, pager);
    }

    /**
     * 查询角色充值排行榜sql
     *
     * @param criA           主表查询条件
     * @param criTodayAmount 当天充值金额相关的查询条件
     * @return String
     */
    private String getRoleRechargeRankingSql(Criteria criA, Criteria criTodayAmount, String margeSql) {
        return """
                SELECT
                	*
                FROM (
                	SELECT
                		a.association_user_id as association_user_id, -- 来源的用户id
                		a.source_system as source_system, -- sdk来源
                		a.role_id as role_id, -- 角色id
                		b.role_name as role_name, -- 角色名
                		a.create_time as role_create_time, -- 角色创建时间
                		a.os as os, -- 角色操作系统
                		b.role_level as role_level, -- 角色等级
                		b.combat_num as combat_num, -- 角色攻击力
                		a.country as country, -- 国家属性
                		IFNULL(c.amount, 0) as amount, -- 角色累计充值金额
                		IFNULL(c.amount_count, 0) as amount_count, -- 角色累计充值次数
                		ROUND(IF(c.amount_count > 0, c.amount / c.amount_count, 0), 2) as avg_amount, -- 平均单价
                		IFNULL(w.vip_level,
                		(CASE
                			WHEN c.amount >= 0 and c.amount < 2000 THEN '1'
                			WHEN c.amount >= 2000 and c.amount < 5000 THEN '2'
                			WHEN c.amount >= 5000 and c.amount < 10000 THEN '3'
                			WHEN c.amount >= 10000 and c.amount < 20000 THEN '4'
                			WHEN c.amount >= 20000 and c.amount < 50000 THEN '5'
                			WHEN c.amount >= 50000 THEN '6'
                			ELSE '0'
                			END)) as vip_level, -- 角色vip等级
                		a.game_id as role_reg_game_id, -- 角色注册游戏id
                		d.game_name as role_reg_game_name, -- 角色注册游戏名
                		d.classify as role_reg_game_classify, -- 角色注册游戏类型
                		IFNULL(d.parent_id, a.game_id) as role_reg_parent_game_id, -- 角色注册游戏的父游戏id
                		IFNULL(e.parent_game_name, d.game_name) as role_reg_parent_game_name, -- 角色注册游戏的父游戏名
                		IFNULL(d.super_game_id, a.game_id) as role_reg_super_game_id, -- 角色注册的超父游戏id
                  		IFNULL(s.name, d.game_name) as role_reg_super_game_name, -- 角色注册的超父游戏名
                		a.server_id as server_id, -- 角色所在区服id
                		q.server_name as server_name, -- 角色所在区服名
                		t.server_name as source_server_name, -- 角色所在区服原始名
                		r.server_id as merge_server_id, -- 角色所在合服id
                  		r.server_name as merge_server_name, -- 角色所在合服名
                		IFNULL(f.real_amount, 0) as role_last_amount, -- 角色最近充值金额
                		f.pay_time as role_last_pay_time, -- 角色最近充值时间
                		g.update_time as role_active_time, -- 角色最近活跃时间
                		TIMESTAMPDIFF(SECOND, g.update_time, NOW()) as role_active_until_now, -- 角色最近活跃距今
                		IFNULL(h.real_amount, 0) as role_first_amount, -- 角色首次充值金额
                		p.today_amount as today_amount, -- 角色当天充值金额
                		TIMESTAMPDIFF(SECOND, a.create_time, f.pay_time) as role_reg_pay_time, -- 角色创建充值时间差（秒）
                		TIMESTAMPDIFF(SECOND, f.pay_time, NOW()) as role_pay_until_now, -- 角色最近充值距今（秒）
                		a.user_id as user_id, -- 玩家id
                	    i.username as username, -- 玩家名称
                	    i.nickname as nickname, -- 玩家昵称
                	    i.reg_mobile as reg_mobile, -- 玩家注册手机
                	    i.reg_email as reg_email, -- 玩家注册邮箱
                	    i.user_create_time as user_create_time, -- 玩家注册时间
                	    i.user_reg_game_id as user_reg_game_id, -- 玩家注册游戏id
                	    i.user_reg_game_name as user_reg_game_name, -- 玩家注册游戏名
                	    i.ip_city, -- 注册城市
                	    i.ip_prov,  -- 注册归属地
                	    i.login_ip_city,  -- 登录城市
                	    i.login_ip_prov, -- 登录归属地
                	    x.agent_id as agent_id, -- 玩家注册渠道id
                	    IF(x.agent_id = 0 , '自然量', y.agent_name) as agent_name, -- 玩家注册渠道名
                	    y.put_user_id as put_user_id, -- 投手id
                	    i.user_active_time as user_active_time, -- 玩家最近活跃时间
                	    TIMESTAMPDIFF(SECOND, i.user_active_time, NOW()) as user_active_until_now, -- 玩家最近活跃距今（秒）
                	    i.user_last_recharge_game_id as user_last_recharge_game_id, -- 玩家最近充值游戏id
                	    i.user_last_recharge_game_name as user_last_recharge_game_name, -- 玩家最近充值游戏名
                		i.user_pay_time as user_pay_time, -- 玩家最近充值时间
                		TIMESTAMPDIFF(SECOND, i.user_create_time, i.user_pay_time) as user_reg_pay_time, -- 玩家注册充值时间差（秒）
                		TIMESTAMPDIFF(SECOND, i.user_pay_time, NOW()) as user_pay_until_now, -- 玩家最近充值距今（秒）
                		IFNULL(j.is_send_mail, 0) as is_send_mail, -- 是否发送邮件
                		j.send_gift_id as send_gift_id, -- 最新发送礼包id
                		k.gift_name as gift_name, -- 礼包名
                		IFNULL(j.is_change_game_type,2) as is_change_game_type, -- 是否转端 1-是；0-否 2-未操作
                  		IFNULL(j.is_add_corp_wechat,2) as  is_add_corp_wechat, -- 是否添加企微 1-是； 0-否 2-未操作
                		j.add_corp_user_id as add_corp_user_id, -- 企微号
                		j.user_wechat as user_wechat, -- 客户微信号
                		j.user_phone as user_phone, -- 客户手机号
                		IF(j.user_phone is null or j.user_phone ='null',0,1) as user_phone_status,  -- 客服手机号绑定状态 0未绑定 1绑定
                		IF(TIMESTAMPDIFF(HOUR, g.update_time, NOW()) > 72,IF(TIMESTAMPDIFF(HOUR, f.pay_time, NOW()) < 72 , 0, 1) , 0) as is_remove_game_for_system, -- 是否退游 1-是;0-否(系统判定)
                        IFNULL(j.is_remove_game,2) as is_remove_game, -- 是否退游 1-是;0-否;null-代表未操作数据
                		IFNULL(j.is_wake_up,2) as is_wake_up, -- 是否唤醒 1-是；0-否 2-未操作
                		j.remark as remark, -- 备注
                		j.gs_id as gsId, -- GS_ID
                		j.corp_id as corpId, -- 企业id
                		j.gs_account as gsAccount, -- GS账号
                        j.external_user_id as external_user_id, -- 企微客户id
                		j.customer_service_id as customer_service_id, -- 客服ID
                		j.oper_user_id as oper_user_id, -- 运营ID
                		j.create_time as create_time, -- 创建时间
                		j.create_by as create_by, -- 创建者
                		j.update_time as update_time, -- 更新时间
                		j.update_by as update_by, -- 更新者
                		j.is_delete as is_delete, -- 是否删除 1-删除；0-正常
                		ara.role_total_amount as role_total_amount, -- 创角24小时内总充值金额
                		ara.max_amount as max_amount, -- 创角24小时内单笔最大充值金额
                		ara.min_amount as min_amount, -- 创角24小时内单笔最小充值金额
                		ara.role_amount as role_amount, -- 角色充值数组
                		z.role_chat_count as role_chat_count -- 角色昨日聊天总数数据
                	FROM
                	(
                		SELECT
                			*
                		FROM
                		(
                			SELECT
                				-- 角色所在子游戏id、区服id、合服id、角色id、角色名、角色创建时间、角色操作系统os（取创建时间最早的信息）
                				b.association_user_id ,
                				a.source_system,
                				a.user_id,
                				a.game_id,
                				a.server_id,
                				a.role_id,
                				a.role_name,
                				a.create_time,
                				a.os,
                				a.country,
                				ROW_NUMBER()over(partition by a.role_id , a.source_system order by a.create_time asc, a.user_id asc) as num
                			FROM dm_game_order.t_game_user_role a
                			LEFT JOIN dm_game_order.t_game_user b on a.source_system = b.source_system AND a.user_id = b.id
                		) a
                		WHERE num = 1
                	) a
                	left join(
                	    SELECT source_system,
                	    role_id,
                	    role_total_amount,
                	    array_min(role_amount) min_amount,
                	    array_max(role_amount) max_amount,
                	    role_amount
                	FROM game_ads.ads_role_amount
                	) ara on a.role_id = ara.role_id and a.source_system = ara.source_system
                	LEFT JOIN (
                		SELECT
                			-- 角色等级、角色攻击力（取最新的信息）
                			source_system,
                			role_id,
                			role_name,
                			role_level,
                			combat_num,
                			ROW_NUMBER()over(partition by role_id , source_system order by update_time desc, user_id desc) as num
                		FROM dm_game_order.t_game_user_role
                	) b on a.source_system = b.source_system AND a.role_id = b.role_id AND b.num = 1
                	LEFT JOIN (
                		SELECT
                			-- 累计充值金额、累计充值次数
                	    	role_id,
                	    	source_system,
                	    	SUM(real_amount) as amount ,
                	    	COUNT(user_id) as amount_count
                		FROM dm_game_order.t_game_order
                		WHERE status =2
                		GROUP BY role_id , source_system
                	) c on a.source_system = c.source_system AND a.role_id = c.role_id
                	LEFT JOIN (
                		SELECT
                			-- 游戏名称、游戏类型、父游戏id、超父游戏id
                   			source_system,
                   			id,
                   			game_name,
                   			classify,
                   			parent_id,
                   			super_game_id
                		FROM dm_game_order.t_game
                	) d on a.source_system = d.source_system AND a.game_id = d.id
                	LEFT JOIN (
                		SELECT
                			-- 父游戏名称
                			source_system,
                			id,
                			game_name as parent_game_name
                		FROM dm_game_order.t_game
                	) e on d.source_system = e.source_system AND d.parent_id = e.id
                	LEFT JOIN (
                		SELECT
                			-- 角色最近充值时间、角色最近充值金额
                			source_system,
                			role_id,
                			real_amount,
                			pay_time,
                			ROW_NUMBER()over(partition by role_id , source_system order by pay_time desc) as num
                		FROM dm_game_order.t_game_order
                		WHERE status =2
                	) f on a.source_system = f.source_system AND a.role_id = f.role_id AND f.num = 1
                	LEFT JOIN (
                		-- 角色最近活跃时间
                  		SELECT
                  			source_system ,
                  			role_id ,
                  			active_time as update_time ,
                  			ROW_NUMBER()over(partition by role_id , source_system order by active_time desc) as num
                  		FROM game_dw.dw_active_log
                	) g on a.source_system = g.source_system AND a.role_id = g.role_id AND g.num = 1
                	LEFT JOIN (
                		SELECT
                			-- 角色首次充值金额
                			source_system,
                			role_id,
                			real_amount,
                			ROW_NUMBER()over(partition by role_id , source_system order by pay_time asc) as num
                		FROM dm_game_order.t_game_order  WHERE status =2
                	) h on a.source_system = h.source_system AND a.role_id = h.role_id AND h.num = 1
                	LEFT JOIN (
                		SELECT
                			-- 角色当天充值金额
                			source_system,
                			role_id,
                			SUM(real_amount) as today_amount
                		FROM dm_game_order.t_game_order
                """ + criTodayAmount +
                """
                            GROUP BY role_id ,source_system
                        ) p on a.source_system = p.source_system AND a.role_id = p.role_id
                        LEFT JOIN (
                            SELECT
                                -- 区服名
                                source_system ,
                                game_id,
                                server_id,
                                server_name
                            FROM dm_game_order.t_game_server
                        ) q on a.source_system = q.source_system AND a.game_id = q.game_id AND a.server_id = q.server_id
                        LEFT JOIN (
                            SELECT
                                -- 合服id、合服名称
                                source_system,
                                game_id,
                                source_server_ids,
                                server_id,
                                server_name
                            FROM dm_game_order.t_game_server_merge
                            WHERE   
                            """ + margeSql + """ 
                        AND is_delete = 0
                    ) r on a.source_system = r.source_system AND a.server_id = r.source_server_ids AND d.super_game_id = r.game_id
                    LEFT JOIN (
                        -- 玩家信息
                        -- 玩家ID、玩家账号、玩家昵称、注册电话、注册邮箱、注册时间、注册游戏、注册渠道
                        SELECT
                            i.source_system as source_system,
                            i.association_user_id as association_user_id, -- 来源用户id
                            i.id as id, -- 玩家id
                            i.username as username,
                            i.nickname as nickname,
                            i.reg_mobile as reg_mobile,
                            i.reg_email as reg_email,
                            i.user_create_time as user_create_time,
                            i.user_reg_game_id as user_reg_game_id,
                            i.ip_prov,
                            i.ip_city,
                            ix.login_ip_prov,
                            ix.login_ip_city,
                            k.game_name as user_reg_game_name,
                            l.update_time as user_active_time,
                            m.user_last_recharge_game_id as user_last_recharge_game_id,
                            n.game_name as user_last_recharge_game_name,
                            m.pay_time as user_pay_time -- 玩家最近充值时间
                        FROM
                        (
                            SELECT
                                *
                            FROM
                            (
                                SELECT
                                    source_system,
                                    association_user_id, -- 来源用户id
                                    id , -- 玩家id
                                    username ,
                                    nickname ,
                                    reg_mobile ,
                                    reg_email ,
                                    create_time as user_create_time,
                                    game_id as user_reg_game_id,
                                    ip_prov,
                                    ip_city,
                                    ROW_NUMBER()over(partition by association_user_id, source_system order by create_time desc, id asc) as num
                                FROM dm_game_order.t_game_user
                            ) z WHERE z.num = 1
                        ) i
                        LEFT JOIN (
                                            -- 玩家登录ip
                                                SELECT
                                                zx.user_id,
                                                zx.ip_prov as login_ip_prov,
                                                zx.ip_city as login_ip_city,
                                                zx.source_system
                                                FROM
                                                (
                                                   select
                                                   user_id,
                                                   ip_prov,
                                                   ip_city,
                                                   source_system,
                                                   ROW_NUMBER()over(partition by user_id, source_system order by create_time desc, id asc) as num
                                                   from
                                                   dm_game_order.t_user_login_log
                                                ) zx WHERE zx.num = 1
                                        ) ix on i.source_system = ix.source_system AND i.association_user_id = ix.user_id
                        LEFT JOIN (
                            SELECT
                                -- 玩家注册游戏名
                                source_system,
                                id,
                                game_name
                            FROM dm_game_order.t_game
                        ) k ON i.user_reg_game_id = k.id AND i.source_system = k.source_system
                        LEFT JOIN (
                            -- 玩家最近活跃时间
                			SELECT
                			    association_user_id,
                			    b.source_system,
                			    b.active_time as update_time,
                			    ROW_NUMBER()over(partition by association_user_id, b.source_system order by b.active_time desc) as num
                			FROM dm_game_order.t_game_user a
                			LEFT JOIN (
                			    SELECT
                			    	source_system ,
                			    	user_id,
                			    	active_time
                			    FROM game_dw.dw_active_log
                			) b ON a.source_system = b.source_system AND a.id = b.user_id
                        ) l ON i.association_user_id = l.association_user_id AND i.source_system = l.source_system AND l.num = 1
                        LEFT JOIN (
                            -- 玩家最近充值游戏、玩家最近充值时间
                            SELECT
                                association_user_id,
                                b.*,
                                ROW_NUMBER()over(partition by association_user_id, b.source_system order by pay_time desc) as num
                            FROM dm_game_order.t_game_user a
                            RIGHT JOIN (
                                -- 玩家最近充值游戏id、最近充值时间
                                SELECT
                                    source_system,
                                    user_id,
                                    game_id as user_last_recharge_game_id,
                                    pay_time
                                FROM dm_game_order.t_game_order
                                WHERE status = 2
                            ) b on a.id = b.user_id AND a.source_system = b.source_system
                        ) m on i.association_user_id = m.association_user_id AND i.source_system = m.source_system AND m.num = 1
                        LEFT JOIN (
                            SELECT
                                -- 玩家最近充值游戏名
                                source_system,
                                id,
                                game_name
                            FROM dm_game_order.t_game
                        ) n ON m.user_last_recharge_game_id = n.id AND m.source_system = n.source_system
                    ) i on a.source_system = i.source_system AND a.association_user_id = i.association_user_id
                    LEFT JOIN (
                        SELECT
                            -- 操作表相关数据
                            source_system,
                            user_id,
                            role_id,
                            server_id,
                            game_id,
                            is_send_mail, -- 是否发送邮件
                            send_gift_id, -- 最新发送礼包id
                            is_change_game_type, -- 是否转端 1-是；0-否
                            is_add_corp_wechat, -- 是否添加企微 1-是； 0-否
                            corp_id, -- 企业id
                            external_user_id, -- 企微客户id
                            gs_account,  -- GS账号
                            add_corp_user_id, -- 企微号
                            user_wechat, -- 客户微信号
                            user_phone, -- 客户手机号
                            is_remove_game, -- 是否退游 1-是；0-否
                            is_wake_up, -- 是否唤醒 1-是；0-否
                            remark, -- 备注
                            gs_id, -- GS_ID
                            customer_service_id, -- 客服ID
                            oper_user_id, -- 运营ID
                            create_time, -- 创建时间
                            create_by, -- 创建者
                            update_time, -- 更新时间
                            update_by, -- 更新者
                            is_delete -- 是否删除 1-删除；0-正常
                        FROM dm_game_order.t_role_operate
                    ) j on a.source_system = j.source_system AND a.association_user_id = j.user_id AND
                    a.role_id = j.role_id AND a.server_id = j.server_id AND a.game_id = j.game_id
                    LEFT JOIN (
                        SELECT
                            -- 礼包名
                            source_system,
                            id,
                            gift_name
                        FROM dm_game_order.t_game_gift
                    ) k on j.source_system = k.source_system AND j.send_gift_id = k.id
                    LEFT JOIN (
                        SELECT
                            source_system ,
                            id,
                            name
                        FROM dm_game_order.t_game_super
                    ) s on d.source_system = s.source_system AND d.super_game_id = s.id
                    LEFT JOIN (
                        SELECT
                            -- 原始服名
                            source_system,
                            game_id,
                            server_id,
                            server_name
                        FROM dm_game_order.t_game_server_merge
                        WHERE is_delete = 0
                    ) t on a.source_system = t.source_system AND a.server_id = t.server_id AND d.super_game_id = t.game_id
                	LEFT JOIN (
                		SELECT
                			-- vip等级
                			source_system ,
                			super_game_id ,
                			parent_game_id ,
                			recharge_money_min ,
                			recharge_money_max ,
                			vip_level ,
                			is_delete
                		FROM dm_game_order.t_game_vip
                		WHERE is_delete = 0
                	) w on d.source_system = w.source_system AND IFNULL(d.parent_id, a.game_id) = w.parent_game_id
                	AND IFNULL(d.super_game_id, a.game_id) = w.super_game_id
                	AND c.amount >= w.recharge_money_min AND c.amount < recharge_money_max
                	LEFT JOIN (
                		SELECT
                			source_system,
                			id,
                			create_time,
                			end_time,
                			agent_id,
                			association_user_id
                		FROM dm_game_order.t_game_user_burst
                	) x on a.source_system = x.source_system AND a.association_user_id = x.id
                	AND a.create_time >= x.create_time AND a.create_time < x.end_time
                	LEFT JOIN (
                        SELECT
                            -- 渠道名称、投手id
                            source_system,
                            id,
                            agent_name,
                            pitcher_id as put_user_id
                        FROM dm_game_order.t_pitcher_agent
                    ) y ON x.agent_id = y.id AND x.source_system = y.source_system
                    LEFT JOIN (
                        SELECT
                            role_chat_count,
                            super_game_id,
                            source_system,
                            role_id
                        FROM game_ads.ads_role_chat
                        where dt = DATE_FORMAT(NOW() - INTERVAL 1 DAY, '%Y-%m-%d')
                    ) z ON z.super_game_id = d.super_game_id and  z.source_system = d.source_system  and a.role_id = z.role_id
                ) a
                """ + criA;
    }

    /**
     * 查询记录数
     *
     * @param criA           主表条件
     * @param criTodayAmount 今日充值条件
     * @return String
     */
    private String getCountNumSql(Criteria criA, Criteria criTodayAmount) {
        return """
                SELECT
                    COUNT(1)
                FROM (
                    SELECT
                        a.association_user_id as association_user_id, -- 来源的用户id
                        a.source_system as source_system, -- sdk来源
                        a.role_id as role_id, -- 角色id
                        b.role_name as role_name, -- 角色名
                        a.create_time as role_create_time, -- 角色创建时间
                        a.os as os, -- 角色操作系统
                        b.role_level as role_level, -- 角色等级
                        b.combat_num as combat_num, -- 角色攻击力
                        a.country as country, -- 国家属性
                        IFNULL(c.amount, 0) as amount, -- 角色累计充值金额
                        IFNULL(c.amount_count, 0) as amount_count, -- 角色累计充值次数
                        ROUND(IF(c.amount_count > 0, c.amount / c.amount_count, 0), 2) as avg_amount, -- 平均单价
                        IFNULL(w.vip_level,
                        (CASE
                            WHEN c.amount >= 0 and c.amount < 2000 THEN '1'
                            WHEN c.amount >= 2000 and c.amount < 5000 THEN '2'
                            WHEN c.amount >= 5000 and c.amount < 10000 THEN '3'
                            WHEN c.amount >= 10000 and c.amount < 20000 THEN '4'
                            WHEN c.amount >= 20000 and c.amount < 50000 THEN '5'
                            WHEN c.amount >= 50000 THEN '6'
                            ELSE '0'
                            END)) as vip_level, -- 角色vip等级
                        a.game_id as role_reg_game_id, -- 角色注册游戏id
                        d.game_name as role_reg_game_name, -- 角色注册游戏名
                        d.classify as role_reg_game_classify, -- 角色注册游戏类型
                        IFNULL(d.parent_id, a.game_id) as role_reg_parent_game_id, -- 角色注册游戏的父游戏id
                        IFNULL(e.parent_game_name, d.game_name) as role_reg_parent_game_name, -- 角色注册游戏的父游戏名
                        IFNULL(d.super_game_id, a.game_id) as role_reg_super_game_id, -- 角色注册的超父游戏id
                        IFNULL(s.name, d.game_name) as role_reg_super_game_name, -- 角色注册的超父游戏名
                        a.server_id as server_id, -- 角色所在区服id
                        q.server_name as server_name, -- 角色所在区服名
                        t.server_name as source_server_name, -- 角色所在区服原始名
                        r.server_id as merge_server_id, -- 角色所在合服id
                        r.server_name as merge_server_name, -- 角色所在合服名
                        IFNULL(f.real_amount, 0) as role_last_amount, -- 角色最近充值金额
                        f.pay_time as role_last_pay_time, -- 角色最近充值时间
                        g.update_time as role_active_time, -- 角色最近活跃时间
                        TIMESTAMPDIFF(SECOND, g.update_time, NOW()) as role_active_until_now, -- 角色最近活跃距今
                        IFNULL(h.real_amount, 0) as role_first_amount, -- 角色首次充值金额
                        p.today_amount as today_amount, -- 角色当天充值金额
                        TIMESTAMPDIFF(SECOND, a.create_time, f.pay_time) as role_reg_pay_time, -- 角色创建充值时间差（秒）
                        TIMESTAMPDIFF(SECOND, f.pay_time, NOW()) as role_pay_until_now, -- 角色最近充值距今（秒）
                        a.user_id as user_id, -- 玩家id
                        i.username as username, -- 玩家名称
                        i.nickname as nickname, -- 玩家昵称
                        i.reg_mobile as reg_mobile, -- 玩家注册手机
                        i.reg_email as reg_email, -- 玩家注册邮箱
                        i.user_create_time as user_create_time, -- 玩家注册时间
                        i.user_reg_game_id as user_reg_game_id, -- 玩家注册游戏id
                        i.user_reg_game_name as user_reg_game_name, -- 玩家注册游戏名
                        x.agent_id as agent_id, -- 玩家注册渠道id
                        IF(x.agent_id = 0 , '自然量', y.agent_name) as agent_name, -- 玩家注册渠道名
                        y.put_user_id as put_user_id, -- 投手id
                        i.user_active_time as user_active_time, -- 玩家最近活跃时间
                        TIMESTAMPDIFF(SECOND, i.user_active_time, NOW()) as user_active_until_now, -- 玩家最近活跃距今（秒）
                        i.user_last_recharge_game_id as user_last_recharge_game_id, -- 玩家最近充值游戏id
                        i.user_last_recharge_game_name as user_last_recharge_game_name, -- 玩家最近充值游戏名
                        i.user_pay_time as user_pay_time, -- 玩家最近充值时间
                        TIMESTAMPDIFF(SECOND, i.user_create_time, i.user_pay_time) as user_reg_pay_time, -- 玩家注册充值时间差（秒）
                        TIMESTAMPDIFF(SECOND, i.user_pay_time, NOW()) as user_pay_until_now, -- 玩家最近充值距今（秒）
                        IFNULL(j.is_send_mail, 0) as is_send_mail, -- 是否发送邮件
                        j.send_gift_id as send_gift_id, -- 最新发送礼包id
                        k.gift_name as gift_name, -- 礼包名
                        j.is_change_game_type as is_change_game_type, -- 是否转端 1-是；0-否
                        j.is_add_corp_wechat as is_add_corp_wechat, -- 是否添加企微 1-是； 0-否
                        j.add_corp_user_id as add_corp_user_id, -- 企微号
                        j.user_wechat as user_wechat, -- 客户微信号
                        j.user_phone as user_phone, -- 客户手机号
                        IF(TIMESTAMPDIFF(HOUR, g.update_time, NOW()) > 72,
                                        IF(TIMESTAMPDIFF(HOUR, f.pay_time, NOW()) < 72 , 0, 1) , 0) as is_remove_game_for_system, -- 是否退游 1-是;0-否(系统判定)
                        j.is_remove_game as is_remove_game, -- 是否退游 1-是;0-否;null-代表未操作数据
                        j.is_wake_up as is_wake_up, -- 是否唤醒 1-是；0-否
                        j.remark as remark, -- 备注
                        j.gs_id as gs_id, -- GS_ID
                        j.customer_service_id as customer_service_id, -- 客服ID
                        j.oper_user_id as oper_user_id, -- 运营ID
                        j.create_time as create_time, -- 创建时间
                        j.create_by as create_by, -- 创建者
                        j.update_time as update_time, -- 更新时间
                        j.update_by as update_by, -- 更新者
                        j.is_delete as is_delete, -- 是否删除 1-删除；0-正常
                        ara.role_total_amount as role_total_amount, -- 创角24小时内总充值金额
                		ara.max_amount as max_amount, -- 创角24小时内单笔最大充值金额
                		ara.min_amount as min_amount, -- 创角24小时内单笔最小充值金额
                		ara.role_amount as role_amount -- 角色充值数组
                    FROM
                    (
                        SELECT
                            *
                        FROM
                        (
                            SELECT
                                -- 角色所在子游戏id、区服id、合服id、角色id、角色名、角色创建时间、角色操作系统os（取创建时间最早的信息）
                                b.association_user_id ,
                                a.source_system,
                                a.user_id,
                                a.game_id,
                                a.server_id,
                                a.role_id,
                                a.role_name,
                                a.create_time,
                                a.os,
                                a.country,
                                ROW_NUMBER()over(partition by a.role_id , a.source_system order by a.create_time asc, a.user_id asc) as num
                            FROM dm_game_order.t_game_user_role a
                            LEFT JOIN dm_game_order.t_game_user b on a.source_system = b.source_system AND a.user_id = b.id
                        ) a
                        WHERE num = 1
                    ) a
                    left join(
                	    SELECT 
                	        source_system,
                	        role_id,
                	        role_total_amount,
                	        array_min(role_amount) min_amount,
                	        array_max(role_amount) max_amount,
                	        role_amount
                	    FROM game_ads.ads_role_amount 
                	) ara on a.role_id = ara.role_id and a.source_system = ara.source_system
                    LEFT JOIN (
                        SELECT
                            -- 角色等级、角色攻击力（取最新的信息）
                            source_system,
                            role_id,
                            role_name,
                            role_level,
                            combat_num,
                            ROW_NUMBER()over(partition by role_id , source_system order by update_time desc, user_id desc) as num
                        FROM dm_game_order.t_game_user_role
                    ) b on a.source_system = b.source_system AND a.role_id = b.role_id AND b.num = 1
                    LEFT JOIN (
                        SELECT
                            -- 累计充值金额、累计充值次数
                            role_id,
                            source_system,
                            SUM(real_amount) as amount ,
                            COUNT(user_id) as amount_count
                        FROM dm_game_order.t_game_order
                        WHERE status =2
                        GROUP BY role_id , source_system
                    ) c on a.source_system = c.source_system AND a.role_id = c.role_id
                    LEFT JOIN (
                        SELECT
                            -- 游戏名称、游戏类型、父游戏id、超父游戏id
                            source_system,
                            id,
                            game_name,
                            classify,
                            parent_id,
                            super_game_id
                        FROM dm_game_order.t_game
                    ) d on a.source_system = d.source_system AND a.game_id = d.id
                    LEFT JOIN (
                        SELECT
                            -- 父游戏名称
                            source_system,
                            id,
                            game_name as parent_game_name
                        FROM dm_game_order.t_game
                    ) e on d.source_system = e.source_system AND d.parent_id = e.id
                    LEFT JOIN (
                        SELECT
                            -- 角色最近充值时间、角色最近充值金额
                            source_system,
                            role_id,
                            real_amount,
                            pay_time,
                            ROW_NUMBER()over(partition by role_id , source_system order by pay_time desc) as num
                        FROM dm_game_order.t_game_order
                        WHERE status =2
                    ) f on a.source_system = f.source_system AND a.role_id = f.role_id AND f.num = 1
                    LEFT JOIN (
                        -- 角色最近活跃时间
                        SELECT
                            source_system ,
                            role_id ,
                            active_time as update_time ,
                            ROW_NUMBER()over(partition by role_id , source_system order by active_time desc) as num
                        FROM game_dw.dw_active_log
                    ) g on a.source_system = g.source_system AND a.role_id = g.role_id AND g.num = 1
                    LEFT JOIN (
                        SELECT
                            -- 角色首次充值金额
                            source_system,
                            role_id,
                            real_amount,
                            ROW_NUMBER()over(partition by role_id , source_system order by pay_time asc) as num
                        FROM dm_game_order.t_game_order  WHERE status =2
                    ) h on a.source_system = h.source_system AND a.role_id = h.role_id AND h.num = 1
                    LEFT JOIN (
                        SELECT
                            -- 角色当天充值金额
                            source_system,
                            role_id,
                            SUM(real_amount) as today_amount
                        FROM dm_game_order.t_game_order
                """ + criTodayAmount +
                """
                                GROUP BY role_id ,source_system
                            ) p on a.source_system = p.source_system AND a.role_id = p.role_id
                            LEFT JOIN (
                                SELECT
                                    -- 区服名
                                    source_system ,
                                    game_id,
                                    server_id,
                                    server_name
                                FROM dm_game_order.t_game_server
                            ) q on a.source_system = q.source_system AND a.game_id = q.game_id AND a.server_id = q.server_id
                            LEFT JOIN (
                                SELECT
                                    -- 合服id、合服名称
                                    source_system,
                                    game_id,
                                    source_server_ids,
                                    server_id,
                                    server_name
                                FROM dm_game_order.t_game_server_merge
                                WHERE is_merge = 0 AND is_delete = 0
                            ) r on a.source_system = r.source_system AND a.server_id = r.source_server_ids AND d.super_game_id = r.game_id
                            LEFT JOIN (
                                -- 玩家信息
                                -- 玩家ID、玩家账号、玩家昵称、注册电话、注册邮箱、注册时间、注册游戏、注册渠道
                                SELECT
                                    i.source_system as source_system,
                                    i.association_user_id as association_user_id, -- 来源用户id
                                    i.id as id, -- 玩家id
                                    i.username as username,
                                    i.nickname as nickname,
                                    i.reg_mobile as reg_mobile,
                                    i.reg_email as reg_email,
                                    i.user_create_time as user_create_time,
                                    i.user_reg_game_id as user_reg_game_id,
                                    k.game_name as user_reg_game_name,
                                    l.update_time as user_active_time,
                                    m.user_last_recharge_game_id as user_last_recharge_game_id,
                                    n.game_name as user_last_recharge_game_name,
                                    m.pay_time as user_pay_time -- 玩家最近充值时间
                                FROM
                                (
                                    SELECT
                                        *
                                    FROM
                                    (
                                        SELECT
                                            source_system,
                                            association_user_id, -- 来源用户id
                                            id , -- 玩家id
                                            username ,
                                            nickname ,
                                            reg_mobile ,
                                            reg_email ,
                                            create_time as user_create_time,
                                            game_id as user_reg_game_id,
                                            ROW_NUMBER()over(partition by association_user_id, source_system order by create_time desc, id asc) as num
                                        FROM dm_game_order.t_game_user
                                    ) z WHERE z.num = 1
                                ) i
                                LEFT JOIN (
                                    SELECT
                                        -- 玩家注册游戏名
                                        source_system,
                                        id,
                                        game_name
                                    FROM dm_game_order.t_game
                                ) k ON i.user_reg_game_id = k.id AND i.source_system = k.source_system
                                LEFT JOIN (
                                    -- 玩家最近活跃时间
                                    SELECT
                                        association_user_id,
                                        b.source_system,
                                        b.active_time as update_time,
                                        ROW_NUMBER()over(partition by association_user_id, b.source_system order by b.active_time desc) as num
                                    FROM dm_game_order.t_game_user a
                                    LEFT JOIN (
                                        SELECT
                                            source_system ,
                                            user_id,
                                            active_time
                                        FROM game_dw.dw_active_log
                                    ) b ON a.source_system = b.source_system AND a.id = b.user_id
                                ) l ON i.association_user_id = l.association_user_id AND i.source_system = l.source_system AND l.num = 1
                                LEFT JOIN (
                                    -- 玩家最近充值游戏、玩家最近充值时间
                                    SELECT
                                        association_user_id,
                                        b.*,
                                        ROW_NUMBER()over(partition by association_user_id, b.source_system order by pay_time desc) as num
                                    FROM dm_game_order.t_game_user a
                                    RIGHT JOIN (
                                        -- 玩家最近充值游戏id、最近充值时间
                                        SELECT
                                            source_system,
                                            user_id,
                                            game_id as user_last_recharge_game_id,
                                            pay_time
                                        FROM dm_game_order.t_game_order
                                        WHERE status = 2
                                    ) b on a.id = b.user_id AND a.source_system = b.source_system
                                ) m on i.association_user_id = m.association_user_id AND i.source_system = m.source_system AND m.num = 1
                                LEFT JOIN (
                                    SELECT
                                        -- 玩家最近充值游戏名
                                        source_system,
                                        id,
                                        game_name
                                    FROM dm_game_order.t_game
                                ) n ON m.user_last_recharge_game_id = n.id AND m.source_system = n.source_system
                            ) i on a.source_system = i.source_system AND a.association_user_id = i.association_user_id
                            LEFT JOIN (
                                SELECT
                                    -- 操作表相关数据
                                    source_system,
                                    user_id,
                                    role_id,
                                    server_id,
                                    game_id,
                                    is_send_mail, -- 是否发送邮件
                                    send_gift_id, -- 最新发送礼包id
                                    is_change_game_type, -- 是否转端 1-是；0-否
                                    is_add_corp_wechat, -- 是否添加企微 1-是； 0-否
                                    add_corp_user_id, -- 企微号
                                    user_wechat, -- 客户微信号
                                    user_phone, -- 客户手机号
                                    is_remove_game, -- 是否退游 1-是；0-否
                                    is_wake_up, -- 是否唤醒 1-是；0-否
                                    remark, -- 备注
                                    gs_id, -- GS_ID
                                    customer_service_id, -- 客服ID
                                    oper_user_id, -- 运营ID
                                    create_time, -- 创建时间
                                    create_by, -- 创建者
                                    update_time, -- 更新时间
                                    update_by, -- 更新者
                                    is_delete -- 是否删除 1-删除；0-正常
                                FROM dm_game_order.t_role_operate
                            ) j on a.source_system = j.source_system AND a.association_user_id = j.user_id AND
                            a.role_id = j.role_id AND a.server_id = j.server_id AND a.game_id = j.game_id
                            LEFT JOIN (
                                SELECT
                                    -- 礼包名
                                    source_system,
                                    id,
                                    gift_name
                                FROM dm_game_order.t_game_gift
                            ) k on j.source_system = k.source_system AND j.send_gift_id = k.id
                            LEFT JOIN (
                                SELECT
                                    source_system ,
                                    id,
                                    name
                                FROM dm_game_order.t_game_super
                            ) s on d.source_system = s.source_system AND d.super_game_id = s.id
                            LEFT JOIN (
                                SELECT
                                    -- 原始服名
                                    source_system,
                                    game_id,
                                    server_id,
                                    server_name
                                FROM dm_game_order.t_game_server_merge
                                WHERE is_delete = 0
                            ) t on a.source_system = t.source_system AND a.server_id = t.server_id AND d.super_game_id = t.game_id
                            LEFT JOIN (
                                SELECT
                                    -- vip等级
                                    source_system ,
                                    super_game_id ,
                                    parent_game_id ,
                                    recharge_money_min ,
                                    recharge_money_max ,
                                    vip_level ,
                                    is_delete
                                FROM dm_game_order.t_game_vip
                                WHERE is_delete = 0
                            ) w on d.source_system = w.source_system AND IFNULL(d.parent_id, a.game_id) = w.parent_game_id
                            AND IFNULL(d.super_game_id, a.game_id) = w.super_game_id
                            AND c.amount >= w.recharge_money_min AND c.amount < recharge_money_max
                            LEFT JOIN (
                                SELECT
                                    source_system,
                                    id,
                                    create_time,
                                    end_time,
                                    agent_id,
                                    association_user_id
                                FROM dm_game_order.t_game_user_burst
                            ) x on a.source_system = x.source_system AND a.association_user_id = x.id
                            AND a.create_time >= x.create_time AND a.create_time < x.end_time
                            LEFT JOIN (
                                SELECT
                                    -- 渠道名称、投手id
                                    source_system,
                                    id,
                                    agent_name,
                                    pitcher_id as put_user_id
                                FROM dm_game_order.t_pitcher_agent
                            ) y ON x.agent_id = y.id AND x.source_system = y.source_system
                        ) a
                        """ + criA;
    }

    /**
     * 查询记录数
     *
     * @param criA           主表条件
     * @param criTodayAmount 今日充值条件
     * @return String
     */
    private String getCountNumSql2(Criteria criA, Criteria criTodayAmount, String margeSql) {
        return """
                SELECT
                    COUNT(1)
                FROM (
                    SELECT
                        a.association_user_id as association_user_id, -- 来源的用户id
                        a.source_system as source_system, -- sdk来源
                        a.role_id as role_id, -- 角色id
                        a.os as os, -- 角色操作系统
                        b.role_name as role_name, -- 角色名
                        a.create_time as role_create_time, -- 角色创建时间
                        b.role_level as role_level, -- 角色等级
                        IFNULL(c.amount, 0) as amount, -- 角色累计充值金额
                        IFNULL(w.vip_level,
                        (CASE
                            WHEN c.amount >= 0 and c.amount < 2000 THEN '1'
                            WHEN c.amount >= 2000 and c.amount < 5000 THEN '2'
                            WHEN c.amount >= 5000 and c.amount < 10000 THEN '3'
                            WHEN c.amount >= 10000 and c.amount < 20000 THEN '4'
                            WHEN c.amount >= 20000 and c.amount < 50000 THEN '5'
                            WHEN c.amount >= 50000 THEN '6'
                            ELSE '0'
                            END)) as vip_level, -- 角色vip等级
                        a.game_id as role_reg_game_id, -- 角色注册游戏id
                        r.server_id as merge_server_id, -- 角色所在合服id
                        r.server_name as merge_server_name, -- 角色所在合服名
                        IFNULL(d.parent_id, a.game_id) as role_reg_parent_game_id, -- 角色注册游戏的父游戏id
                        IFNULL(d.super_game_id, a.game_id) as role_reg_super_game_id, -- 角色注册的超父游戏id
                        a.server_id as server_id, -- 角色所在区服id
                        t.server_name as source_server_name, -- 角色所在区服原始名
                        IFNULL(f.real_amount, 0) as role_last_amount, -- 角色最近充值金额
                        f.pay_time as role_last_pay_time, -- 角色最近充值时间
                        g.update_time as role_active_time, -- 角色最近活跃时间
                        TIMESTAMPDIFF(SECOND, g.update_time, NOW()) as role_active_until_now, -- 角色最近活跃距今
                        IFNULL(h.real_amount, 0) as role_first_amount, -- 角色首次充值金额
                        p.today_amount as today_amount, -- 角色当天充值金额
                        TIMESTAMPDIFF(SECOND, a.create_time, f.pay_time) as role_reg_pay_time, -- 角色创建充值时间差（秒）
                        TIMESTAMPDIFF(SECOND, f.pay_time, NOW()) as role_pay_until_now, -- 角色最近充值距今（秒）
                        a.user_id as user_id, -- 玩家id
                        x.agent_id as agent_id, -- 玩家注册渠道id
                        IF(x.agent_id = 0 , '自然量', y.agent_name) as agent_name, -- 玩家注册渠道名
                        i.user_create_time as user_create_time, -- 玩家注册时间
                        y.put_user_id as put_user_id, -- 投手id
                        IFNULL(j.is_send_mail, 0) as is_send_mail, -- 是否发送邮件
                        j.send_gift_id as send_gift_id, -- 最新发送礼包id
                        IFNULL(j.is_change_game_type,2) as is_change_game_type, -- 是否转端 1-是；0-否
                        IFNULL(j.is_add_corp_wechat,2) as is_add_corp_wechat, -- 是否添加企微 1-是； 0-否
                        j.user_phone as user_phone, -- 客户手机号
                        IF(j.user_phone is null or j.user_phone ='null',0,1) as user_phone_status,  -- 客服手机号绑定状态 0未绑定 1绑定
                        j.user_wechat as user_wechat, -- 客户微信号
                        IF(TIMESTAMPDIFF(HOUR, g.update_time, NOW()) > 72,
                        IF(TIMESTAMPDIFF(HOUR, f.pay_time, NOW()) < 72 , 0, 1) , 0) as is_remove_game_for_system, -- 是否退游 1-是;0-否(系统判定)
                        IFNULL(j.is_remove_game,2) as is_remove_game, -- 是否退游 1-是;0-否;2-代表未操作数据
                        IFNULL(j.is_wake_up,2) as is_wake_up, -- 是否唤醒 1-是；0-否 2-未操作
                        j.gs_id as gsId, -- GS_ID
                        j.customer_service_id as customer_service_id, -- 客服ID
                        j.oper_user_id as oper_user_id, -- 运营ID
                        j.is_delete as is_delete, -- 是否删除 1-删除；0-正常
                        ara.role_total_amount as role_total_amount, -- 创角24小时内总充值金额
                		ara.max_amount as max_amount, -- 创角24小时内单笔最大充值金额
                		ara.min_amount as min_amount, -- 创角24小时内单笔最小充值金额
                		i.ip_city,
                  		i.ip_prov,
                  		ix.login_ip_city,
                   		ix.login_ip_prov,
                		ara.role_amount as role_amount -- 角色充值数组
                    FROM
                    (
                        SELECT
                            *
                        FROM
                        (
                            SELECT
                                -- 角色所在子游戏id、区服id、合服id、角色id、角色名、角色创建时间、角色操作系统os（取创建时间最早的信息）
                                b.association_user_id ,
                                a.source_system,
                                a.user_id,
                                a.game_id,
                                a.server_id,
                                a.role_id,
                                a.role_name,
                                a.create_time,
                                a.os,
                                ROW_NUMBER()over(partition by a.role_id , a.source_system order by a.create_time asc, a.user_id asc) as num
                            FROM dm_game_order.t_game_user_role a
                            LEFT JOIN dm_game_order.t_game_user b on a.source_system = b.source_system AND a.user_id = b.id
                        ) a
                        WHERE num = 1
                    ) a
                    left join(
                	    SELECT 
                	        source_system,
                	        role_id,
                	        role_total_amount,
                	        array_min(role_amount) min_amount,
                	        array_max(role_amount) max_amount,
                	        role_amount
                	    FROM game_ads.ads_role_amount 
                	) ara on a.role_id = ara.role_id and a.source_system = ara.source_system
                    LEFT JOIN (
                        SELECT
                            -- 角色等级、角色攻击力（取最新的信息）
                            source_system,
                            role_id,
                            role_name,
                            role_level,
                            ROW_NUMBER()over(partition by role_id , source_system order by update_time desc, user_id desc) as num
                        FROM dm_game_order.t_game_user_role
                    ) b on a.source_system = b.source_system AND a.role_id = b.role_id AND b.num = 1
                    LEFT JOIN (
                        SELECT
                            -- 累计充值金额、累计充值次数
                            role_id,
                            source_system,
                            SUM(real_amount) as amount
                        FROM dm_game_order.t_game_order
                        WHERE status =2
                        GROUP BY role_id , source_system
                    ) c on a.source_system = c.source_system AND a.role_id = c.role_id
                    LEFT JOIN (
                        SELECT
                            -- 游戏名称、游戏类型、父游戏id、超父游戏id
                            source_system,
                            id,
                            parent_id,
                            super_game_id
                        FROM dm_game_order.t_game
                    ) d on a.source_system = d.source_system AND a.game_id = d.id
                    LEFT JOIN (
                        SELECT
                            -- 父游戏名称
                            source_system,
                            id,
                            game_name as parent_game_name
                        FROM dm_game_order.t_game
                    ) e on d.source_system = e.source_system AND d.parent_id = e.id
                    LEFT JOIN (
                        SELECT
                            -- 角色最近充值时间、角色最近充值金额
                            source_system,
                            role_id,
                            real_amount,
                            pay_time,
                            ROW_NUMBER()over(partition by role_id , source_system order by pay_time desc) as num
                        FROM dm_game_order.t_game_order
                        WHERE status =2
                    ) f on a.source_system = f.source_system AND a.role_id = f.role_id AND f.num = 1
                    LEFT JOIN (
                        -- 角色最近活跃时间
                        SELECT
                            source_system ,
                            role_id ,
                            active_time as update_time ,
                            ROW_NUMBER()over(partition by role_id , source_system order by active_time desc) as num
                        FROM game_dw.dw_active_log
                    ) g on a.source_system = g.source_system AND a.role_id = g.role_id AND g.num = 1
                    LEFT JOIN (
                        SELECT
                            -- 角色首次充值金额
                            source_system,
                            role_id,
                            real_amount,
                            ROW_NUMBER()over(partition by role_id , source_system order by pay_time asc) as num
                        FROM dm_game_order.t_game_order  WHERE status =2
                    ) h on a.source_system = h.source_system AND a.role_id = h.role_id AND h.num = 1
                    LEFT JOIN (
                        SELECT
                            -- 角色当天充值金额
                            source_system,
                            role_id,
                            SUM(real_amount) as today_amount
                        FROM dm_game_order.t_game_order
                """ + criTodayAmount +
                """
                            GROUP BY role_id ,source_system
                        ) p on a.source_system = p.source_system AND a.role_id = p.role_id
                        LEFT JOIN (
                            SELECT
                                -- 操作表相关数据
                                source_system,
                                user_id,
                                role_id,
                                server_id,
                                game_id,
                                user_phone, -- 客户手机号
                                user_wechat, -- 客户微信号
                                is_send_mail, -- 是否发送邮件
                                send_gift_id, -- 最新发送礼包id
                                is_change_game_type, -- 是否转端 1-是；0-否
                                is_add_corp_wechat, -- 是否添加企微 1-是； 0-否
                                is_remove_game, -- 是否退游 1-是；0-否
                                is_wake_up, -- 是否唤醒 1-是；0-否
                                gs_id, -- GS_ID
                                customer_service_id, -- 客服ID
                                oper_user_id, -- 运营ID
                                is_delete -- 是否删除 1-删除；0-正常
                            FROM dm_game_order.t_role_operate
                        ) j on a.source_system = j.source_system AND a.association_user_id = j.user_id AND
                        a.role_id = j.role_id AND a.server_id = j.server_id AND a.game_id = j.game_id
                        LEFT JOIN (
                            SELECT
                                source_system ,
                                id,
                                name
                            FROM dm_game_order.t_game_super
                        ) s on d.source_system = s.source_system AND d.super_game_id = s.id
                        LEFT JOIN (
                            SELECT
                                -- 合服id、合服名称
                                source_system,
                                game_id,
                                source_server_ids,
                                server_id,
                                server_name
                            FROM dm_game_order.t_game_server_merge
                            WHERE  
                             """ + margeSql + """ 
                          AND is_delete = 0
                    ) r on a.source_system = r.source_system AND a.server_id = r.source_server_ids AND d.super_game_id = r.game_id
                    LEFT JOIN (
                                            -- 玩家信息
                                            -- 玩家ID、玩家账号、玩家昵称、注册电话、注册邮箱、注册时间、注册游戏、注册渠道
                                                SELECT
                                                z.source_system as source_system,
                                                z.association_user_id as association_user_id, -- 来源用户id
                                                z.id as id, -- 玩家id
                                                z.username as username,
                                                z.nickname as nickname,
                                                z.reg_mobile as reg_mobile,
                                                z.reg_email as reg_email,
                                                z.user_create_time as user_create_time,
                                                z.ip_city,
                                                z.ip_prov,
                                                z.user_reg_game_id as user_reg_game_id
                                                FROM
                                                (
                                                    SELECT
                                                        source_system,
                                                        association_user_id, -- 来源用户id
                                                        id , -- 玩家id
                                                        username ,
                                                        nickname ,
                                                        reg_mobile ,
                                                        reg_email ,
                                                        create_time as user_create_time,
                                                        game_id as user_reg_game_id,
                                                        ip_city,
                                                        ip_prov,
                                                        ROW_NUMBER()over(partition by association_user_id, source_system order by create_time desc, id asc) as num
                                                    FROM dm_game_order.t_game_user
                                                ) z WHERE z.num = 1
                                        ) i on a.source_system = i.source_system AND a.association_user_id = i.association_user_id
                                        LEFT JOIN (
                                                                                 -- 玩家登录ip
                                                                                     SELECT
                                                                                     zx.user_id,
                                                                                     zx.ip_prov as login_ip_prov,
                                                                                     zx.ip_city as login_ip_city,
                                                                                     zx.source_system
                                                                                     FROM
                                                                                     (
                                                                                        select
                                                                                        user_id,
                                                                                        ip_prov,
                                                                                        ip_city,
                                                                                        source_system,
                                                                                        ROW_NUMBER()over(partition by user_id, source_system order by create_time desc, id asc) as num
                                                                                        from
                                                                                        dm_game_order.t_user_login_log
                                                                                     ) zx WHERE zx.num = 1
                                                                             ) ix on i.source_system = ix.source_system AND i.association_user_id = ix.user_id 
                                        
                    LEFT JOIN (
                        SELECT
                            -- 原始服名
                            source_system,
                            game_id,
                            server_id,
                            server_name
                        FROM dm_game_order.t_game_server_merge
                        WHERE is_delete = 0
                    ) t on a.source_system = t.source_system AND a.server_id = t.server_id AND d.super_game_id = t.game_id
                    LEFT JOIN (
                        SELECT
                            -- vip等级
                            source_system ,
                            super_game_id ,
                            parent_game_id ,
                            recharge_money_min ,
                            recharge_money_max ,
                            vip_level ,
                            is_delete
                        FROM dm_game_order.t_game_vip
                        WHERE is_delete = 0
                    ) w on d.source_system = w.source_system AND IFNULL(d.parent_id, a.game_id) = w.parent_game_id
                    AND IFNULL(d.super_game_id, a.game_id) = w.super_game_id
                    AND c.amount >= w.recharge_money_min AND c.amount < recharge_money_max
                    LEFT JOIN (
                        SELECT
                            source_system,
                            id,
                            create_time,
                            end_time,
                            agent_id,
                            association_user_id
                        FROM dm_game_order.t_game_user_burst
                    ) x on a.source_system = x.source_system AND a.association_user_id = x.id
                    AND a.create_time >= x.create_time AND a.create_time < x.end_time
                    LEFT JOIN (
                        SELECT
                            -- 渠道名称、投手id
                            source_system,
                            id,
                            agent_name,
                            pitcher_id as put_user_id
                        FROM dm_game_order.t_pitcher_agent
                    ) y ON x.agent_id = y.id AND x.source_system = y.source_system
                ) a
                """ + criA;
    }


    /**
     * 原始服-父游戏维度（区服相关信息）
     *
     * @return String
     */
    private String getRoleCombatRankServerDataSqlA() {
        return """
                SELECT
                	-- 查询每个父游戏-原始区服维度的信息
                	source_system,
                	parent_game_id, -- 父游戏ID
                	MAX(parent_game_name) as parent_game_name, -- 父游戏名
                	super_game_id , -- 超父游戏ID
                	MAX(super_game_name) as super_game_name , -- 超父游戏名
                	server_id, -- 区服ID
                	MAX(server_name) as server_name, -- 区服名
                	MAX(dt) as dt, -- 开服时间
                	boss_server_id, -- 合服ID
                	MAX(boss_server_name) as boss_server_name, -- 合服名
                	MAX(merge_time) as merge_time , -- 合服时间
                	MAX(sum_server_count) as sum_server_count, -- 合服次数
                	SUM(server_combat_num) as server_combat_num, -- 区服战力
                    SUM(new_user_count) as new_user_count, -- 新用户人数
                    SUM(create_role_count) as create_role_count, -- 创角人数
                    SUM(role_count) as role_count, -- 角色数量
                    SUM(total_num) as total_num, -- 总付费人数
                    SUM(total_amount) as total_amount, -- 总付费金额
                	ROUND(IF(SUM(total_num) > 0, SUM(total_amount) / SUM(total_num), 0), 2) as total_arppu, -- 总付费ARPPU
                    ROUND(IF(SUM(create_role_count) > 0, SUM(total_num) / SUM(create_role_count), 0), 4) as amount_rate, -- 付费率
                    SUM(ba1) as ba1, -- 今日付费金额
                    SUM(ba1_num) as ba1_num, -- 今日付费人数
                    SUM(ba1_count) as ba1_count, -- 今日付费次数
                    SUM(ba1_active_num) as ba1_active_num, -- 今日活跃人数
                    SUM(ba2) as ba2, -- 昨日付费金额
                    SUM(ba2_num) as ba2_num, -- 昨日付费人数
                    SUM(ba2_count) as ba2_count, -- 昨日付费次数
                    SUM(ba2_active_num) as ba2_active_num, -- 昨日活跃人数
                    SUM(ba3) as ba3, -- 前天付费金额
                    SUM(ba3_num) as ba3_num, -- 前天付费人数
                    SUM(ba3_count) as ba3_count, -- 前天付费次数
                    SUM(ba3_active_num) as ba3_active_num -- 前天活跃人数
                FROM (
                	-- 子查询，查询每个子游戏-原始区服维度的信息
                	SELECT
                		source_system,
                		game_id, -- 子游戏ID
                		MAX(game_name) as game_name, -- 子游戏名
                		parent_game_id, -- 父游戏ID
                		MAX(parent_game_name) as parent_game_name, -- 父游戏名
                		super_game_id , -- 超父游戏ID
                		MAX(super_game_name) as super_game_name , -- 超父游戏名
                		server_id, -- 区服ID
                		MAX(server_name) as server_name, -- 区服名
                		MAX(`date`) as dt, -- 开服日期
                		boss_server_id, -- 合服ID
                		MAX(boss_server_name) as boss_server_name, -- 合服名
                		MAX(merge_time) as merge_time , -- 合服时间
                		MAX(sum_server_count) as sum_server_count, -- 合服次数
                		MAX(server_combat_num) as server_combat_num, -- 区服战力
                		MAX(new_user_count) as new_user_count, -- 新用户人数
                		MAX(create_role_count) as create_role_count, -- 创角人数
                		MAX(role_count) as role_count, -- 角色数量
                		MAX(total_num) as total_num, -- 总付费人数
                		MAX(total_amount) as total_amount, -- 总付费金额
                		MAX(ba1) as ba1, -- 今日付费金额
                		MAX(ba1_num) as ba1_num, -- 今日付费人数
                		MAX(ba1_count) as ba1_count, -- 今日付费次数
                		MAX(ba1_active_num) as ba1_active_num, -- 今日活跃人数
                		MAX(ba2) as ba2, -- 昨日付费金额
                		MAX(ba2_num) as ba2_num, -- 昨日付费人数
                		MAX(ba2_count) as ba2_count, -- 昨日付费次数
                		MAX(ba2_active_num) as ba2_active_num, -- 昨日活跃人数
                		MAX(ba3) as ba3, -- 前天付费金额
                		MAX(ba3_num) as ba3_num, -- 前天付费人数
                		MAX(ba3_count) as ba3_count, -- 前天付费次数
                		MAX(ba3_active_num) as ba3_active_num -- 前天活跃人数
                	FROM game_ads.ads_role_combat_num_ranking
                	GROUP BY game_id ,parent_game_id ,super_game_id ,server_id ,boss_server_id ,source_system
                ) a
                """;
    }

    /**
     * 原始服-父游戏维度（区服相关信息）(总记录数)
     *
     * @return String
     */
    private String getRoleCombatRankServerDataSqlACount(Criteria cri) {
        return """
                SELECT
                    COUNT(1)
                FROM(
                    SELECT
                    	-- 查询每个父游戏-原始区服维度的信息
                    	source_system,
                    	parent_game_id, -- 父游戏ID
                    	MAX(parent_game_name) as parent_game_name, -- 父游戏名
                    	super_game_id , -- 超父游戏ID
                    	MAX(super_game_name) as super_game_name , -- 超父游戏名
                    	server_id, -- 区服ID
                    	MAX(server_name) as server_name, -- 区服名
                    	MAX(dt) as dt, -- 开服时间
                    	TIMESTAMPDIFF(DAY, MAX(dt), NOW()) start_day, -- 开服天数
                    	boss_server_id, -- 合服ID
                    	MAX(boss_server_name) as boss_server_name, -- 合服名
                    	MAX(merge_time) as merge_time , -- 合服时间
                    	MAX(sum_server_count) as sum_server_count, -- 合服次数
                    	SUM(server_combat_num) as server_combat_num, -- 区服战力
                        SUM(new_user_count) as new_user_count, -- 新用户人数
                        SUM(create_role_count) as create_role_count, -- 创角人数
                        SUM(role_count) as role_count, -- 角色数量
                        SUM(total_num) as total_num, -- 总付费人数
                        SUM(total_amount) as total_amount, -- 总付费金额
                    	ROUND(IF(SUM(total_num) > 0, SUM(total_amount) / SUM(total_num), 0), 2) as total_arppu, -- 总付费ARPPU
                        ROUND(IF(SUM(create_role_count) > 0, SUM(total_num) / SUM(create_role_count), 0), 4) as amount_rate, -- 付费率
                        SUM(ba1) as ba1, -- 今日付费金额
                        SUM(ba1_num) as ba1_num, -- 今日付费人数
                        SUM(ba1_count) as ba1_count, -- 今日付费次数
                        SUM(ba1_active_num) as ba1_active_num, -- 今日活跃人数
                        SUM(ba2) as ba2, -- 昨日付费金额
                        SUM(ba2_num) as ba2_num, -- 昨日付费人数
                        SUM(ba2_count) as ba2_count, -- 昨日付费次数
                        SUM(ba2_active_num) as ba2_active_num, -- 昨日活跃人数
                        SUM(ba3) as ba3, -- 前天付费金额
                        SUM(ba3_num) as ba3_num, -- 前天付费人数
                        SUM(ba3_count) as ba3_count, -- 前天付费次数
                        SUM(ba3_active_num) as ba3_active_num -- 前天活跃人数
                    FROM (
                    	-- 子查询，查询每个子游戏-原始区服维度的信息
                    	SELECT
                    		source_system,
                    		game_id, -- 子游戏ID
                    		MAX(game_name) as game_name, -- 子游戏名
                    		parent_game_id, -- 父游戏ID
                    		MAX(parent_game_name) as parent_game_name, -- 父游戏名
                    		super_game_id , -- 超父游戏ID
                    		MAX(super_game_name) as super_game_name , -- 超父游戏名
                    		server_id, -- 区服ID
                    		MAX(server_name) as server_name, -- 区服名
                    		MAX(`date`) as dt, -- 开服日期
                    		boss_server_id, -- 合服ID
                    		MAX(boss_server_name) as boss_server_name, -- 合服名
                    		MAX(merge_time) as merge_time , -- 合服时间
                    		MAX(sum_server_count) as sum_server_count, -- 合服次数
                    		MAX(server_combat_num) as server_combat_num, -- 区服战力
                    		MAX(new_user_count) as new_user_count, -- 新用户人数
                    		MAX(create_role_count) as create_role_count, -- 创角人数
                    		MAX(role_count) as role_count, -- 角色数量
                    		MAX(total_num) as total_num, -- 总付费人数
                    		MAX(total_amount) as total_amount, -- 总付费金额
                    		MAX(ba1) as ba1, -- 今日付费金额
                    		MAX(ba1_num) as ba1_num, -- 今日付费人数
                    		MAX(ba1_count) as ba1_count, -- 今日付费次数
                    		MAX(ba1_active_num) as ba1_active_num, -- 今日活跃人数
                    		MAX(ba2) as ba2, -- 昨日付费金额
                    		MAX(ba2_num) as ba2_num, -- 昨日付费人数
                    		MAX(ba2_count) as ba2_count, -- 昨日付费次数
                    		MAX(ba2_active_num) as ba2_active_num, -- 昨日活跃人数
                    		MAX(ba3) as ba3, -- 前天付费金额
                    		MAX(ba3_num) as ba3_num, -- 前天付费人数
                    		MAX(ba3_count) as ba3_count, -- 前天付费次数
                    		MAX(ba3_active_num) as ba3_active_num -- 前天活跃人数
                    	FROM game_ads.ads_role_combat_num_ranking
                    	GROUP BY game_id ,parent_game_id ,super_game_id ,server_id ,boss_server_id ,source_system
                    ) a
                """ + cri +
                """
                        ) a
                        """;
    }

    /**
     * 合服-父游戏维度（区服相关信息）
     *
     * @return String
     */
    private String getRoleCombatRankServerDataSqlB() {
        return """
                SELECT
                	-- 查询每个父游戏-合服服维度的信息
                	source_system,
                	parent_game_id, -- 父游戏ID
                	MAX(parent_game_name) as parent_game_name, -- 父游戏名
                	super_game_id , -- 超父游戏ID
                	MAX(super_game_name) as super_game_name , -- 超父游戏名
                	boss_server_id, -- 合服ID
                	MAX(boss_server_name) as boss_server_name, -- 合服名
                	MAX(merge_time) as merge_time , -- 合服时间
                	TIMESTAMPDIFF(DAY, MAX(merge_time), NOW()) start_day, -- 开服天数
                	MAX(sum_server_count) as sum_server_count, -- 合服次数
                	MAX(c.is_merge) as is_merge, -- 是否参与过合服
                	MAX(b.main_server_id) as main_server_id, -- 主服ID
                	MAX(b.main_server_name) as main_server_name, -- 主服名
                	MAX(son_server_ids) as son_server_ids, -- 合服的子服ID列表
                    MAX(source_server_ids) as source_server_ids, -- 子服ID列表
                    MAX(source_server_ids_name) as source_server_ids_name, -- 子服名列表
                	SUM(server_combat_num) as server_combat_num, -- 区服战力
                    SUM(new_user_count) as new_user_count, -- 新用户人数
                    SUM(create_role_count) as create_role_count, -- 创角人数
                    SUM(role_count) as role_count, -- 角色数量
                    SUM(total_num) as total_num, -- 总付费人数
                    SUM(total_amount) as total_amount, -- 总付费金额
                	ROUND(IF(SUM(total_num) > 0, SUM(total_amount) / SUM(total_num), 0), 2) as total_arppu, -- 总付费ARPPU
                    ROUND(IF(SUM(create_role_count) > 0, SUM(total_num) / SUM(create_role_count), 0), 4) as amount_rate, -- 付费率
                    SUM(ba1) as ba1, -- 今日付费金额
                    SUM(ba1_num) as ba1_num, -- 今日付费人数
                    SUM(ba1_count) as ba1_count, -- 今日付费次数
                    SUM(ba1_active_num) as ba1_active_num, -- 今日活跃人数
                    SUM(ba2) as ba2, -- 昨日付费金额
                    SUM(ba2_num) as ba2_num, -- 昨日付费人数
                    SUM(ba2_count) as ba2_count, -- 昨日付费次数
                    SUM(ba2_active_num) as ba2_active_num, -- 昨日活跃人数
                    SUM(ba3) as ba3, -- 前天付费金额
                    SUM(ba3_num) as ba3_num, -- 前天付费人数
                    SUM(ba3_count) as ba3_count, -- 前天付费次数
                    SUM(ba3_active_num) as ba3_active_num -- 前天活跃人数
                FROM (
                	-- 子查询，查询每个子游戏-原始区服维度的信息
                	SELECT
                		source_system,
                		game_id, -- 子游戏ID
                		MAX(game_name) as game_name, -- 子游戏名
                		parent_game_id, -- 父游戏ID
                		MAX(parent_game_name) as parent_game_name, -- 父游戏名
                		super_game_id , -- 超父游戏ID
                		MAX(super_game_name) as super_game_name , -- 超父游戏名
                		server_id, -- 区服ID
                		MAX(server_name) as server_name, -- 区服名
                		boss_server_id, -- 合服ID
                		MAX(boss_server_name) as boss_server_name, -- 合服名
                		MAX(merge_time) as merge_time , -- 合服时间
                		MAX(sum_server_count) as sum_server_count, -- 合服次数
                		MAX(server_combat_num) as server_combat_num, -- 区服战力
                		MAX(new_user_count) as new_user_count, -- 新用户人数
                		MAX(create_role_count) as create_role_count, -- 创角人数
                		MAX(role_count) as role_count, -- 角色数量
                		MAX(total_num) as total_num, -- 总付费人数
                		MAX(total_amount) as total_amount, -- 总付费金额
                		MAX(ba1) as ba1, -- 今日付费金额
                		MAX(ba1_num) as ba1_num, -- 今日付费人数
                		MAX(ba1_count) as ba1_count, -- 今日付费次数
                		MAX(ba1_active_num) as ba1_active_num, -- 今日活跃人数
                		MAX(ba2) as ba2, -- 昨日付费金额
                		MAX(ba2_num) as ba2_num, -- 昨日付费人数
                		MAX(ba2_count) as ba2_count, -- 昨日付费次数
                		MAX(ba2_active_num) as ba2_active_num, -- 昨日活跃人数
                		MAX(ba3) as ba3, -- 前天付费金额
                		MAX(ba3_num) as ba3_num, -- 前天付费人数
                		MAX(ba3_count) as ba3_count, -- 前天付费次数
                		MAX(ba3_active_num) as ba3_active_num -- 前天活跃人数
                	FROM game_ads.ads_role_combat_num_ranking
                	GROUP BY game_id ,parent_game_id ,super_game_id ,server_id ,boss_server_id ,source_system
                ) a
                LEFT JOIN (
                	SELECT
                		a.source_system as b_source_system,
                		a.server_id as b_server_id,
                		a.game_id as b_game_id,
                		MAX(a.server_name) as server_name  ,
                		MAX(a.main_server_id) as main_server_id,
                		MAX(c.server_name) as main_server_name,
                		MAX(a.son_server_ids) as son_server_ids ,
                		GROUP_CONCAT(a.source_server_ids, ',') as source_server_ids,
                		GROUP_CONCAT(b.server_name , ',') as source_server_ids_name
                	FROM dm_game_order.t_game_server_merge a
                	LEFT JOIN dm_game_order.t_game_server_merge b on a.source_system = b.source_system AND a.game_id = b.game_id AND a.source_server_ids = b.server_id
                	LEFT JOIN (
                		SELECT
                			source_system ,
                			game_id ,
                			server_id  ,
                			MAX(server_name) as server_name
                		FROM dm_game_order.t_game_server_merge
                		GROUP BY server_id ,source_system ,game_id
                	) c on a.source_system = c.source_system AND a.game_id = c.game_id AND a.main_server_id = c.server_id
                	group by a.server_id , a.source_system , a.game_id
                ) b ON a.source_system = b.b_source_system AND a.super_game_id = b.b_game_id AND a.boss_server_id = b.b_server_id
                LEFT JOIN (
                	SELECT
                		source_system as c_source_system,
                		game_id,
                		server_id,
                		is_merge
                	FROM dm_game_order.t_game_server_merge
                ) c on a.source_system = c.c_source_system AND a.boss_server_id = c.server_id AND a.super_game_id = c.game_id
                """;
    }

    /**
     * 合服-父游戏维度（区服相关信息）(总记录数)
     *
     * @return String
     */
    private String getRoleCombatRankServerDataSqlBCount(Criteria cri) {
        return """
                SELECT
                    COUNT(1)
                FROM(
                    SELECT
                    	-- 查询每个父游戏-合服服维度的信息
                    	source_system,
                    	parent_game_id, -- 父游戏ID
                    	MAX(parent_game_name) as parent_game_name, -- 父游戏名
                    	super_game_id , -- 超父游戏ID
                    	MAX(super_game_name) as super_game_name , -- 超父游戏名
                    	boss_server_id, -- 合服ID
                    	MAX(boss_server_name) as boss_server_name, -- 合服名
                    	MAX(merge_time) as merge_time , -- 合服时间
                    	TIMESTAMPDIFF(DAY, MAX(merge_time), NOW()) start_day, -- 开服天数
                    	MAX(sum_server_count) as sum_server_count, -- 合服次数
                    	MAX(c.is_merge) as is_merge, -- 是否参与过合服
                    	MAX(b.main_server_id) as main_server_id, -- 主服ID
                    	MAX(b.main_server_name) as main_server_name, -- 主服名
                    	MAX(son_server_ids) as son_server_ids, -- 合服的子服ID列表
                        MAX(source_server_ids) as source_server_ids, -- 子服ID列表
                        MAX(source_server_ids_name) as source_server_ids_name, -- 子服名列表
                    	SUM(server_combat_num) as server_combat_num, -- 区服战力
                        SUM(new_user_count) as new_user_count, -- 新用户人数
                        SUM(create_role_count) as create_role_count, -- 创角人数
                        SUM(role_count) as role_count, -- 角色数量
                        SUM(total_num) as total_num, -- 总付费人数
                        SUM(total_amount) as total_amount, -- 总付费金额
                    	ROUND(IF(SUM(total_num) > 0, SUM(total_amount) / SUM(total_num), 0), 2) as total_arppu, -- 总付费ARPPU
                        ROUND(IF(SUM(create_role_count) > 0, SUM(total_num) / SUM(create_role_count), 0), 4) as amount_rate, -- 付费率
                        SUM(ba1) as ba1, -- 今日付费金额
                        SUM(ba1_num) as ba1_num, -- 今日付费人数
                        SUM(ba1_count) as ba1_count, -- 今日付费次数
                        SUM(ba1_active_num) as ba1_active_num, -- 今日活跃人数
                        SUM(ba2) as ba2, -- 昨日付费金额
                        SUM(ba2_num) as ba2_num, -- 昨日付费人数
                        SUM(ba2_count) as ba2_count, -- 昨日付费次数
                        SUM(ba2_active_num) as ba2_active_num, -- 昨日活跃人数
                        SUM(ba3) as ba3, -- 前天付费金额
                        SUM(ba3_num) as ba3_num, -- 前天付费人数
                        SUM(ba3_count) as ba3_count, -- 前天付费次数
                        SUM(ba3_active_num) as ba3_active_num -- 前天活跃人数
                    FROM (
                    	-- 子查询，查询每个子游戏-原始区服维度的信息
                    	SELECT
                    		source_system,
                    		game_id, -- 子游戏ID
                    		MAX(game_name) as game_name, -- 子游戏名
                    		parent_game_id, -- 父游戏ID
                    		MAX(parent_game_name) as parent_game_name, -- 父游戏名
                    		super_game_id , -- 超父游戏ID
                    		MAX(super_game_name) as super_game_name , -- 超父游戏名
                    		server_id, -- 区服ID
                    		MAX(server_name) as server_name, -- 区服名
                    		boss_server_id, -- 合服ID
                    		MAX(boss_server_name) as boss_server_name, -- 合服名
                    		MAX(merge_time) as merge_time , -- 合服时间
                    		MAX(sum_server_count) as sum_server_count, -- 合服次数
                    		MAX(server_combat_num) as server_combat_num, -- 区服战力
                    		MAX(new_user_count) as new_user_count, -- 新用户人数
                    		MAX(create_role_count) as create_role_count, -- 创角人数
                    		MAX(role_count) as role_count, -- 角色数量
                    		MAX(total_num) as total_num, -- 总付费人数
                    		MAX(total_amount) as total_amount, -- 总付费金额
                    		MAX(ba1) as ba1, -- 今日付费金额
                    		MAX(ba1_num) as ba1_num, -- 今日付费人数
                    		MAX(ba1_count) as ba1_count, -- 今日付费次数
                    		MAX(ba1_active_num) as ba1_active_num, -- 今日活跃人数
                    		MAX(ba2) as ba2, -- 昨日付费金额
                    		MAX(ba2_num) as ba2_num, -- 昨日付费人数
                    		MAX(ba2_count) as ba2_count, -- 昨日付费次数
                    		MAX(ba2_active_num) as ba2_active_num, -- 昨日活跃人数
                    		MAX(ba3) as ba3, -- 前天付费金额
                    		MAX(ba3_num) as ba3_num, -- 前天付费人数
                    		MAX(ba3_count) as ba3_count, -- 前天付费次数
                    		MAX(ba3_active_num) as ba3_active_num -- 前天活跃人数
                    	FROM game_ads.ads_role_combat_num_ranking
                    	GROUP BY game_id ,parent_game_id ,super_game_id ,server_id ,boss_server_id ,source_system
                    ) a
                    LEFT JOIN (
                    	SELECT
                    		a.source_system as b_source_system,
                    		a.server_id as b_server_id,
                    		a.game_id as b_game_id,
                    		MAX(a.server_name) as server_name  ,
                    		MAX(a.main_server_id) as main_server_id,
                    		MAX(c.server_name) as main_server_name,
                    		MAX(a.son_server_ids) as son_server_ids ,
                    		GROUP_CONCAT(a.source_server_ids, ',') as source_server_ids,
                    		GROUP_CONCAT(b.server_name , ',') as source_server_ids_name
                    	FROM dm_game_order.t_game_server_merge a
                    	LEFT JOIN dm_game_order.t_game_server_merge b on a.source_system = b.source_system AND a.game_id = b.game_id AND a.source_server_ids = b.server_id
                    	LEFT JOIN (
                    		SELECT
                    			source_system ,
                    			game_id ,
                    			server_id  ,
                    			MAX(server_name) as server_name
                    		FROM dm_game_order.t_game_server_merge
                    		GROUP BY server_id ,source_system ,game_id
                    	) c on a.source_system = c.source_system AND a.game_id = c.game_id AND a.main_server_id = c.server_id
                    	group by a.server_id , a.source_system , a.game_id
                    ) b ON a.source_system = b.b_source_system AND a.super_game_id = b.b_game_id AND a.boss_server_id = b.b_server_id
                    LEFT JOIN (
                    	SELECT
                    		source_system as c_source_system,
                    		game_id,
                    		server_id,
                    		is_merge
                    	FROM dm_game_order.t_game_server_merge
                    ) c on a.source_system = c.c_source_system AND a.boss_server_id = c.server_id AND a.super_game_id = c.game_id
                """ + cri +
                """
                        ) a
                        """;
    }

    /**
     * 原始服-超父游戏维度（区服相关信息）
     *
     * @return String
     */
    private String getRoleCombatRankServerDataSqlC() {
        return """
                SELECT
                    *
                FROM
                (
                	SELECT
                		-- 查询每个超父游戏-原始区服维度的信息
                		source_system,
                		super_game_id , -- 超父游戏ID
                		MAX(super_game_name) as super_game_name , -- 超父游戏名
                		server_id, -- 区服ID
                		IFNULL(MAX(i.server_name), MAX(a.server_name)) as server_name, -- 区服名
                		MIN(dt) as dt, -- 开服日期
                		-- MAX(merge_time) as dt, -- 开服日期
                		TIMESTAMPDIFF(DAY, DATE(MIN(dt)), DATE_ADD(Date(NOW()),1)) start_day, -- 开服天数
                		boss_server_id, -- 合服ID
                		MAX(boss_server_name) as boss_server_name, -- 合服名
                		MAX(merge_time) as merge_time , -- 合服时间
                		MAX(sum_server_count) as sum_server_count, -- 合服次数
                		IFNULL(MAX(h.is_merge), 0) as is_merge, -- 是否参与过合服
                		SUM(server_combat_num) as server_combat_num, -- 区服战力
                		MAX(c.new_user_count) as new_user_count, -- 新用户人数
                		MAX(d.create_role_count) as create_role_count , -- 创角人数
                	    MAX(b.role_num) as role_count , -- 角色数量
                	    MAX(e.total_num) as total_num, -- 总付费人数
                	    SUM(total_amount) as total_amount, -- 总付费金额
                	    ROUND(IF(MAX(e.total_num) > 0, SUM(total_amount) / MAX(e.total_num), 0), 2) as total_arppu, -- 总付费ARPPU
                	    ROUND(IF(MAX(d.create_role_count) > 0, MAX(e.total_num) / MAX(d.create_role_count), 0), 4) as amount_rate, -- 付费率
                	    SUM(ba1) as ba1, -- 今日付费金额
                	    MAX(e.ba1_num) as ba1_num, -- 今日付费人数
                	    SUM(ba1_count) as ba1_count, -- 今日付费次数
                	    MAX(f.ba1_active_num) as ba1_active_num, -- 今日活跃人数
                	    SUM(ba2) as ba2, -- 昨日付费金额
                	    MAX(e.ba2_num) as ba2_num, -- 昨日付费人数
                	    SUM(ba2_count) as ba2_count, -- 昨日付费次数
                	    MAX(f.ba2_active_num) as ba2_active_num, -- 昨日活跃人数
                	    SUM(ba3) as ba3, -- 前天付费金额
                	    MAX(e.ba3_num) as ba3_num, -- 前天付费人数
                	    SUM(ba3_count) as ba3_count, -- 前天付费次数
                	    MAX(f.ba3_active_num) as ba3_active_num -- 前天活跃人数
                	FROM (
                		SELECT
                			-- 子查询，查询每个父游戏-原始区服维度的信息
                			source_system,
                			parent_game_id, -- 父游戏ID
                			MAX(parent_game_name) as parent_game_name, -- 父游戏名
                			super_game_id , -- 超父游戏ID
                			MAX(super_game_name) as super_game_name , -- 超父游戏名
                			server_id, -- 区服ID
                			MAX(server_name) as server_name, -- 区服名
                			MIN(dt) as dt, -- 开服日期
                			boss_server_id, -- 合服ID
                			MAX(boss_server_name) as boss_server_name, -- 合服名
                			MAX(merge_time) as merge_time , -- 合服时间
                			MAX(sum_server_count) as sum_server_count, -- 合服次数
                			SUM(server_combat_num) as server_combat_num, -- 区服战力
                		    SUM(total_amount) as total_amount, -- 总付费金额
                		    SUM(ba1) as ba1, -- 今日付费金额
                		    SUM(ba1_count) as ba1_count, -- 今日付费次数
                		    SUM(ba2) as ba2, -- 昨日付费金额
                		    SUM(ba2_count) as ba2_count, -- 昨日付费次数
                		    SUM(ba3) as ba3, -- 前天付费金额
                		    SUM(ba3_count) as ba3_count -- 前天付费次数
                		FROM (
                			-- 子查询，查询每个子游戏-原始区服维度的信息
                			SELECT
                				source_system,
                				game_id, -- 子游戏ID
                				MAX(game_name) as game_name, -- 子游戏名
                				parent_game_id, -- 父游戏ID
                				MAX(parent_game_name) as parent_game_name, -- 父游戏名
                				super_game_id , -- 超父游戏ID
                				MAX(super_game_name) as super_game_name , -- 超父游戏名
                				server_id, -- 区服ID
                				MAX(server_name) as server_name, -- 区服名
                				MIN(`date`) as dt, -- 开服日期
                				boss_server_id, -- 合服ID
                				MAX(boss_server_name) as boss_server_name, -- 合服名
                				MAX(merge_time) as merge_time , -- 合服时间
                				MAX(sum_server_count) as sum_server_count, -- 合服次数
                				MAX(server_combat_num) as server_combat_num, -- 区服战力
                				MAX(total_amount) as total_amount, -- 总付费金额
                				MAX(ba1) as ba1, -- 今日付费金额
                				MAX(ba1_count) as ba1_count, -- 今日付费次数
                				MAX(ba2) as ba2, -- 昨日付费金额
                				MAX(ba2_count) as ba2_count, -- 昨日付费次数
                				MAX(ba3) as ba3, -- 前天付费金额
                				MAX(ba3_count) as ba3_count -- 前天付费次数
                			FROM game_ads.ads_role_combat_num_ranking
                			GROUP BY game_id ,parent_game_id ,super_game_id ,server_id ,boss_server_id ,source_system
                		) a GROUP BY parent_game_id ,super_game_id ,server_id ,boss_server_id ,source_system
                	) a
                	LEFT JOIN (
                		SELECT
                			-- 超父-原始区服维度
                			source_system as b_source_system,
                			super_game_id as b_super_game_id,
                			server_id as b_server_id,
                			boss_server_id as b_boss_server_id,
                			COUNT(DISTINCT role_id) as role_num -- 角色数量
                		FROM game_ads.ads_role_combat_num_ranking
                		GROUP BY super_game_id, source_system, server_id , boss_server_id
                	) b on a.source_system = b.b_source_system AND a.server_id = b.b_server_id AND a.super_game_id = b.b_super_game_id AND a.boss_server_id = b.b_boss_server_id
                	LEFT JOIN (
                		SELECT
                			-- 超父-原始区服维度
                			a.source_system as c_source_system,
                			a.super_game_id as c_super_game_id,
                			a.server_id as c_server_id,
                			b.boss_server_id as c_boss_server_id,
                			COUNT(a.association_user_id) as new_user_count -- 新用户人数
                		FROM (
                			SELECT
                				a.source_system ,
                				b.id,
                				a.game_id ,
                				a.server_id,
                				a.role_id ,
                				a.role_name ,
                				b.association_user_id ,
                				a.create_time ,
                				IFNULL(c.super_game_id , a.game_id) as super_game_id,
                				ROW_NUMBER()over(partition by b.association_user_id , a.source_system order by a.create_time asc, a.user_id asc) as num
                			FROM dm_game_order.t_game_user_role a
                			LEFT JOIN dm_game_order.t_game_user b on a.source_system = b.source_system AND a.user_id = b.id
                			LEFT JOIN dm_game_order.t_game c on a.source_system = c.source_system AND a.game_id = c.id
                		) a
                		LEFT JOIN (
                			SELECT
                				source_system,
                				server_id,
                				boss_server_id,
                				super_game_id
                			FROM game_ads.ads_role_combat_num_ranking
                			GROUP BY source_system, super_game_id, server_id, boss_server_id
                		) b on a.source_system = b.source_system AND a.server_id = b.server_id AND a.super_game_id = b.super_game_id
                		WHERE num = 1
                		GROUP BY a.super_game_id, a.server_id, b.boss_server_id, a.source_system
                	) c on a.source_system = c.c_source_system AND a.super_game_id = c.c_super_game_id AND a.server_id = c.c_server_id AND a.boss_server_id = c.c_boss_server_id
                	LEFT JOIN (
                		SELECT
                			-- 超父-原始区服维度
                			source_system as d_source_system,
                			super_game_id as d_super_game_id,
                			server_id as d_server_id,
                			boss_server_id as d_boss_server_id,
                			COUNT(DISTINCT user_id) as create_role_count -- 创角人数
                		FROM game_ads.ads_role_combat_num_ranking
                		GROUP BY super_game_id, source_system, server_id , boss_server_id
                	) d on a.source_system = d.d_source_system AND a.server_id = d.d_server_id AND a.super_game_id = d.d_super_game_id AND a.boss_server_id = d.d_boss_server_id
                	LEFT JOIN (
                		SELECT
                			-- 超父-原始区服维度
                			a.source_system as e_source_system,
                			a.server_id as e_server_id,
                			c.super_game_id as e_super_game_id,
                			d.boss_server_id as e_boss_server_id,
                			count(DISTINCT IF(Date(NOW()) = day,user_id,NULL)) as ba1_num, -- 今日充值人数
                			count(DISTINCT IF(date_add(Date(NOW()),-1)=day,user_id,NULL)) as ba2_num, -- 昨日充值人数
                			count(DISTINCT IF(date_add(Date(NOW()),-2)=day,user_id,NULL)) as ba3_num, -- 前天充值人数
                			count(DISTINCT user_id) as total_num -- 总充值人数
                		FROM dm_game_order.t_game_server a
                		LEFT JOIN game_dw.dw_order_day_amount b
                		on a.source_system = b.source_system and a.server_id =b.server_id and a.game_id =b.game_id
                		LEFT JOIN (
                			SELECT
                				source_system ,
                				id,
                				IFNULL(parent_id, id) as parent_id,
                				IFNULL(super_game_id, id) as super_game_id
                			FROM dm_game_order.t_game
                		) c on a.source_system = c.source_system AND a.game_id = c.id
                		LEFT JOIN (
                			SELECT
                				source_system,
                				server_id,
                				boss_server_id,
                				super_game_id
                			FROM game_ads.ads_role_combat_num_ranking
                			GROUP BY source_system, super_game_id, server_id, boss_server_id
                		) d on a.source_system = d.source_system AND a.server_id = d.server_id AND c.super_game_id = d.super_game_id
                		group by a.source_system ,a.server_id ,d.boss_server_id, c.super_game_id
                	) e on a.source_system = e.e_source_system AND a.server_id = e.e_server_id AND a.super_game_id = e.e_super_game_id AND a.boss_server_id = e.e_boss_server_id
                	LEFT JOIN (
                		select
                			-- 超父-原始区服维度
                			a.source_system as f_source_system,
                			a.server_id as f_server_id,
                			d.boss_server_id as f_boss_server_id,
                			c.super_game_id as f_super_game_id,
                			count(distinct IF(Date(NOW()) = dt,user_id,NULL)) as ba1_active_num, -- 今日活跃人数
                			count(distinct IF(date_add(Date(NOW()),-1)=dt,user_id,NULL)) as ba2_active_num, -- 昨日活跃人数
                			count(distinct IF(date_add(Date(NOW()),-2)=dt,user_id,NULL)) as ba3_active_num -- 前日活跃人数
                		from dm_game_order.t_game_server a
                		left join game_dw.dw_active_log b on a.source_system = b.source_system and a.server_id = b.server_id and a.game_id = b.game_id
                		LEFT JOIN (
                			SELECT
                				source_system ,
                				id,
                				IFNULL(parent_id, id) as parent_id,
                				IFNULL(super_game_id, id) as super_game_id
                			FROM dm_game_order.t_game
                		) c on a.source_system = c.source_system AND a.game_id = c.id
                		LEFT JOIN (
                			SELECT
                				source_system,
                				server_id,
                				boss_server_id,
                				super_game_id
                			FROM game_ads.ads_role_combat_num_ranking
                			GROUP BY source_system, super_game_id, server_id, boss_server_id
                		) d on a.source_system = d.source_system AND a.server_id = d.server_id AND c.super_game_id = d.super_game_id
                		group by a.source_system ,a.server_id ,d.boss_server_id, c.super_game_id
                	) f on a.source_system = f.f_source_system AND a.server_id = f.f_server_id AND a.super_game_id = f.f_super_game_id AND a.boss_server_id = f.f_boss_server_id
                	LEFT JOIN (
                		SELECT
                			source_system as h_source_system,
                			game_id as h_game_id,
                			server_id as h_server_id,
                			server_name,
                			is_merge -- 是否参与过合服
                		FROM dm_game_order.t_game_server_merge
                		WHERE is_delete = 0
                	) h on a.source_system = h.h_source_system AND a.boss_server_id = h.h_server_id AND a.super_game_id = h.h_game_id
                	LEFT JOIN (
                		SELECT
                			source_system as i_source_system,
                			game_id as i_game_id,
                			server_id as i_server_id,
                			server_name
                		FROM dm_game_order.t_game_server_merge
                		WHERE is_delete = 0
                	) i on a.source_system = i.i_source_system AND a.server_id = i.i_server_id AND a.super_game_id = i.i_game_id
                	GROUP BY source_system,server_id,boss_server_id,super_game_id
                ) a
                """;
    }

    /**
     * 原始服-超父游戏维度（区服相关信息）(总记录数)
     *
     * @return String
     */
    private String getRoleCombatRankServerDataSqlCCount(Criteria cri) {
        return """
                SELECT
                    COUNT(1)
                FROM
                (
                	SELECT
                		-- 查询每个超父游戏-原始区服维度的信息
                		source_system,
                		super_game_id , -- 超父游戏ID
                		MAX(super_game_name) as super_game_name , -- 超父游戏名
                		server_id, -- 区服ID
                		IFNULL(MAX(i.server_name), MAX(a.server_name)) as server_name, -- 区服名
                		-- MIN(dt) as dt, -- 开服日期
                		MAX(merge_time) as dt, -- 开服日期
                		TIMESTAMPDIFF(DAY, DATE(MIN(dt)), DATE_ADD(Date(NOW()),1)) start_day, -- 开服天数
                		boss_server_id, -- 合服ID
                		MAX(boss_server_name) as boss_server_name, -- 合服名
                		MAX(merge_time) as merge_time , -- 合服时间
                		MAX(sum_server_count) as sum_server_count, -- 合服次数
                		IFNULL(MAX(h.is_merge), 0) as is_merge, -- 是否参与过合服
                		SUM(server_combat_num) as server_combat_num, -- 区服战力
                		MAX(c.new_user_count) as new_user_count, -- 新用户人数
                		MAX(d.create_role_count) as create_role_count , -- 创角人数
                	    MAX(b.role_num) as role_count , -- 角色数量
                	    MAX(e.total_num) as total_num, -- 总付费人数
                	    SUM(total_amount) as total_amount, -- 总付费金额
                	    ROUND(IF(MAX(e.total_num) > 0, SUM(total_amount) / MAX(e.total_num), 0), 2) as total_arppu, -- 总付费ARPPU
                	    ROUND(IF(MAX(d.create_role_count) > 0, MAX(e.total_num) / MAX(d.create_role_count), 0), 4) as amount_rate, -- 付费率
                	    SUM(ba1) as ba1, -- 今日付费金额
                	    MAX(e.ba1_num) as ba1_num, -- 今日付费人数
                	    SUM(ba1_count) as ba1_count, -- 今日付费次数
                	    MAX(f.ba1_active_num) as ba1_active_num, -- 今日活跃人数
                	    SUM(ba2) as ba2, -- 昨日付费金额
                	    MAX(e.ba2_num) as ba2_num, -- 昨日付费人数
                	    SUM(ba2_count) as ba2_count, -- 昨日付费次数
                	    MAX(f.ba2_active_num) as ba2_active_num, -- 昨日活跃人数
                	    SUM(ba3) as ba3, -- 前天付费金额
                	    MAX(e.ba3_num) as ba3_num, -- 前天付费人数
                	    SUM(ba3_count) as ba3_count, -- 前天付费次数
                	    MAX(f.ba3_active_num) as ba3_active_num -- 前天活跃人数
                	FROM (
                		SELECT
                			-- 子查询，查询每个父游戏-原始区服维度的信息
                			source_system,
                			parent_game_id, -- 父游戏ID
                			MAX(parent_game_name) as parent_game_name, -- 父游戏名
                			super_game_id , -- 超父游戏ID
                			MAX(super_game_name) as super_game_name , -- 超父游戏名
                			server_id, -- 区服ID
                			MAX(server_name) as server_name, -- 区服名
                			MIN(dt) as dt, -- 开服日期
                			boss_server_id, -- 合服ID
                			MAX(boss_server_name) as boss_server_name, -- 合服名
                			MAX(merge_time) as merge_time , -- 合服时间
                			MAX(sum_server_count) as sum_server_count, -- 合服次数
                			SUM(server_combat_num) as server_combat_num, -- 区服战力
                		    SUM(total_amount) as total_amount, -- 总付费金额
                		    SUM(ba1) as ba1, -- 今日付费金额
                		    SUM(ba1_count) as ba1_count, -- 今日付费次数
                		    SUM(ba2) as ba2, -- 昨日付费金额
                		    SUM(ba2_count) as ba2_count, -- 昨日付费次数
                		    SUM(ba3) as ba3, -- 前天付费金额
                		    SUM(ba3_count) as ba3_count -- 前天付费次数
                		FROM (
                			-- 子查询，查询每个子游戏-原始区服维度的信息
                			SELECT
                				source_system,
                				game_id, -- 子游戏ID
                				MAX(game_name) as game_name, -- 子游戏名
                				parent_game_id, -- 父游戏ID
                				MAX(parent_game_name) as parent_game_name, -- 父游戏名
                				super_game_id , -- 超父游戏ID
                				MAX(super_game_name) as super_game_name , -- 超父游戏名
                				server_id, -- 区服ID
                				MAX(server_name) as server_name, -- 区服名
                				MIN(`date`) as dt, -- 开服日期
                				boss_server_id, -- 合服ID
                				MAX(boss_server_name) as boss_server_name, -- 合服名
                				MAX(merge_time) as merge_time , -- 合服时间
                				MAX(sum_server_count) as sum_server_count, -- 合服次数
                				MAX(server_combat_num) as server_combat_num, -- 区服战力
                				MAX(total_amount) as total_amount, -- 总付费金额
                				MAX(ba1) as ba1, -- 今日付费金额
                				MAX(ba1_count) as ba1_count, -- 今日付费次数
                				MAX(ba2) as ba2, -- 昨日付费金额
                				MAX(ba2_count) as ba2_count, -- 昨日付费次数
                				MAX(ba3) as ba3, -- 前天付费金额
                				MAX(ba3_count) as ba3_count -- 前天付费次数
                			FROM game_ads.ads_role_combat_num_ranking
                			GROUP BY game_id ,parent_game_id ,super_game_id ,server_id ,boss_server_id ,source_system
                		) a GROUP BY parent_game_id ,super_game_id ,server_id ,boss_server_id ,source_system
                	) a
                	LEFT JOIN (
                		SELECT
                			-- 超父-原始区服维度
                			source_system as b_source_system,
                			super_game_id as b_super_game_id,
                			server_id as b_server_id,
                			boss_server_id as b_boss_server_id,
                			COUNT(DISTINCT role_id) as role_num -- 角色数量
                		FROM game_ads.ads_role_combat_num_ranking
                		GROUP BY super_game_id, source_system, server_id , boss_server_id
                	) b on a.source_system = b.b_source_system AND a.server_id = b.b_server_id AND a.super_game_id = b.b_super_game_id AND a.boss_server_id = b.b_boss_server_id
                	LEFT JOIN (
                		SELECT
                			-- 超父-原始区服维度
                			a.source_system as c_source_system,
                			a.super_game_id as c_super_game_id,
                			a.server_id as c_server_id,
                			b.boss_server_id as c_boss_server_id,
                			COUNT(a.association_user_id) as new_user_count -- 新用户人数
                		FROM (
                			SELECT
                				a.source_system ,
                				b.id,
                				a.game_id ,
                				a.server_id,
                				a.role_id ,
                				a.role_name ,
                				b.association_user_id ,
                				a.create_time ,
                				IFNULL(c.super_game_id , a.game_id) as super_game_id,
                				ROW_NUMBER()over(partition by b.association_user_id , a.source_system order by a.create_time asc, a.user_id asc) as num
                			FROM dm_game_order.t_game_user_role a
                			LEFT JOIN dm_game_order.t_game_user b on a.source_system = b.source_system AND a.user_id = b.id
                			LEFT JOIN dm_game_order.t_game c on a.source_system = c.source_system AND a.game_id = c.id
                		) a
                		LEFT JOIN (
                			SELECT
                				source_system,
                				server_id,
                				boss_server_id,
                				super_game_id
                			FROM game_ads.ads_role_combat_num_ranking
                			GROUP BY source_system, super_game_id, server_id, boss_server_id
                		) b on a.source_system = b.source_system AND a.server_id = b.server_id AND a.super_game_id = b.super_game_id
                		WHERE num = 1
                		GROUP BY a.super_game_id, a.server_id, b.boss_server_id, a.source_system
                	) c on a.source_system = c.c_source_system AND a.super_game_id = c.c_super_game_id AND a.server_id = c.c_server_id AND a.boss_server_id = c.c_boss_server_id
                	LEFT JOIN (
                		SELECT
                			-- 超父-原始区服维度
                			source_system as d_source_system,
                			super_game_id as d_super_game_id,
                			server_id as d_server_id,
                			boss_server_id as d_boss_server_id,
                			COUNT(DISTINCT user_id) as create_role_count -- 创角人数
                		FROM game_ads.ads_role_combat_num_ranking
                		GROUP BY super_game_id, source_system, server_id , boss_server_id
                	) d on a.source_system = d.d_source_system AND a.server_id = d.d_server_id AND a.super_game_id = d.d_super_game_id AND a.boss_server_id = d.d_boss_server_id
                	LEFT JOIN (
                		SELECT
                			-- 超父-原始区服维度
                			a.source_system as e_source_system,
                			a.server_id as e_server_id,
                			c.super_game_id as e_super_game_id,
                			d.boss_server_id as e_boss_server_id,
                			count(DISTINCT IF(Date(NOW()) = day,user_id,NULL)) as ba1_num, -- 今日充值人数
                			count(DISTINCT IF(date_add(Date(NOW()),-1)=day,user_id,NULL)) as ba2_num, -- 昨日充值人数
                			count(DISTINCT IF(date_add(Date(NOW()),-2)=day,user_id,NULL)) as ba3_num, -- 前天充值人数
                			count(DISTINCT user_id) as total_num -- 总充值人数
                		FROM dm_game_order.t_game_server a
                		LEFT JOIN game_dw.dw_order_day_amount b
                		on a.source_system = b.source_system and a.server_id =b.server_id and a.game_id =b.game_id
                		LEFT JOIN (
                			SELECT
                				source_system ,
                				id,
                				IFNULL(parent_id, id) as parent_id,
                				IFNULL(super_game_id, id) as super_game_id
                			FROM dm_game_order.t_game
                		) c on a.source_system = c.source_system AND a.game_id = c.id
                		LEFT JOIN (
                			SELECT
                				source_system,
                				server_id,
                				boss_server_id,
                				super_game_id
                			FROM game_ads.ads_role_combat_num_ranking
                			GROUP BY source_system, super_game_id, server_id, boss_server_id
                		) d on a.source_system = d.source_system AND a.server_id = d.server_id AND c.super_game_id = d.super_game_id
                		group by a.source_system ,a.server_id ,d.boss_server_id, c.super_game_id
                	) e on a.source_system = e.e_source_system AND a.server_id = e.e_server_id AND a.super_game_id = e.e_super_game_id AND a.boss_server_id = e.e_boss_server_id
                	LEFT JOIN (
                		select
                			-- 超父-原始区服维度
                			a.source_system as f_source_system,
                			a.server_id as f_server_id,
                			d.boss_server_id as f_boss_server_id,
                			c.super_game_id as f_super_game_id,
                			count(distinct IF(Date(NOW()) = dt,user_id,NULL)) as ba1_active_num, -- 今日活跃人数
                			count(distinct IF(date_add(Date(NOW()),-1)=dt,user_id,NULL)) as ba2_active_num, -- 昨日活跃人数
                			count(distinct IF(date_add(Date(NOW()),-2)=dt,user_id,NULL)) as ba3_active_num -- 前日活跃人数
                		from dm_game_order.t_game_server a
                		left join game_dw.dw_active_log b on a.source_system = b.source_system and a.server_id = b.server_id and a.game_id = b.game_id
                		LEFT JOIN (
                			SELECT
                				source_system ,
                				id,
                				IFNULL(parent_id, id) as parent_id,
                				IFNULL(super_game_id, id) as super_game_id
                			FROM dm_game_order.t_game
                		) c on a.source_system = c.source_system AND a.game_id = c.id
                		LEFT JOIN (
                			SELECT
                				source_system,
                				server_id,
                				boss_server_id,
                				super_game_id
                			FROM game_ads.ads_role_combat_num_ranking
                			GROUP BY source_system, super_game_id, server_id, boss_server_id
                		) d on a.source_system = d.source_system AND a.server_id = d.server_id AND c.super_game_id = d.super_game_id
                		group by a.source_system ,a.server_id ,d.boss_server_id, c.super_game_id
                	) f on a.source_system = f.f_source_system AND a.server_id = f.f_server_id AND a.super_game_id = f.f_super_game_id AND a.boss_server_id = f.f_boss_server_id
                	LEFT JOIN (
                		SELECT
                			source_system as h_source_system,
                			game_id as h_game_id,
                			server_id as h_server_id,
                			server_name,
                			is_merge -- 是否参与过合服
                		FROM dm_game_order.t_game_server_merge
                		WHERE is_delete = 0
                	) h on a.source_system = h.h_source_system AND a.boss_server_id = h.h_server_id AND a.super_game_id = h.h_game_id
                	LEFT JOIN (
                		SELECT
                			source_system as i_source_system,
                			game_id as i_game_id,
                			server_id as i_server_id,
                			server_name
                		FROM dm_game_order.t_game_server_merge
                		WHERE is_delete = 0
                	) i on a.source_system = i.i_source_system AND a.server_id = i.i_server_id AND a.super_game_id = i.i_game_id
                	GROUP BY source_system,server_id,boss_server_id,super_game_id
                ) a
                """ + cri;
    }

    /**
     * 合服-超父游戏维度（区服相关信息）
     *
     * @return String
     */
    private String getRoleCombatRankServerDataSqlD() {
        return """
                SELECT
                	*
                FROM
                (
                	SELECT
                		-- 查询每个超父游戏-合服维度的信息
                		source_system,
                		super_game_id , -- 超父游戏ID
                		MAX(super_game_name) as super_game_name , -- 超父游戏名
                		boss_server_id as server_id, -- 合服ID
                		IFNULL(MAX(h.server_name), MAX(a.boss_server_name)) as server_name, -- 合服名
                		MIN(merge_time) as dt, -- 合服时间
                		TIMESTAMPDIFF(DAY, DATE(MIN(merge_time)), DATE_ADD(Date(NOW()),1)) start_day, -- 开服天数
                		MAX(sum_server_count) as sum_server_count, -- 合服次数
                		IFNULL(MAX(h.is_merge), 0) as is_merge, -- 是否参与过合服
                		IFNULL(MIN(b.min_server_start_time), MIN(merge_time)) as min_server_start_time, -- 最小原始子服开服时间
                  	    IFNULL(TIMESTAMPDIFF(DAY, DATE(MIN(b.min_server_start_time)), DATE_ADD(Date(NOW()),1)),
                  	    	TIMESTAMPDIFF(DAY, DATE(MIN(merge_time)), DATE_ADD(Date(NOW()),1))) as min_server_start_days, -- 最小原始子服开服距今天数
                		MAX(b.main_server_id) as main_server_id, -- 主服ID
                		MAX(b.main_server_name) as main_server_name, -- 主服名
                		MAX(son_server_ids) as son_server_ids, -- 合服ID列表
                		MAX(son_server_ids_name) as son_server_ids_name, -- 合服ID列表
                	    MAX(source_server_ids) as source_server_ids, -- 原始子服ID列表
                	    MAX(source_server_ids_name) as source_server_ids_name, -- 原始子服名列表
                		MAX(server_combat_num) as server_combat_num, -- 区服战力
                	    MAX(d.new_user_count) as new_user_count, -- 新用户人数
                	    MAX(e.create_role_count) as create_role_count, -- 创角人数
                	    MAX(c.role_count) as role_count, -- 角色数量
                	    MAX(f.total_num) as total_num, -- 总付费人数
                	    MAX(total_amount) as total_amount, -- 总付费金额
                	    ROUND(IF(MAX(f.total_num) > 0, MAX(total_amount) / MAX(f.total_num), 0), 2) as total_arppu, -- 总付费ARPPU
                	    ROUND(IF(MAX(e.create_role_count) > 0, MAX(f.total_num) / MAX(e.create_role_count), 0), 4) as amount_rate, -- 付费率
                	    MAX(ba1) as ba1, -- 今日付费金额
                	    MAX(f.ba1_num) as ba1_num, -- 今日付费人数
                	    MAX(ba1_count) as ba1_count, -- 今日付费次数
                	    MAX(g.ba1_active_num) as ba1_active_num, -- 今日活跃人数
                	    MAX(ba2) as ba2, -- 昨日付费金额
                	    MAX(f.ba2_num) as ba2_num, -- 昨日付费人数
                	    MAX(ba2_count) as ba2_count, -- 昨日付费次数
                	    MAX(g.ba2_active_num) as ba2_active_num, -- 昨日活跃人数
                	    MAX(ba3) as ba3, -- 前天付费金额
                	    MAX(f.ba3_num) as ba3_num, -- 前天付费人数
                	    MAX(ba3_count) as ba3_count, -- 前天付费次数
                	    MAX(g.ba3_active_num) as ba3_active_num -- 前天活跃人数
                	FROM (
                		SELECT
                			-- 子查询，查询每个超父游戏-原始区服维度的信息
                			source_system,
                			super_game_id , -- 超父游戏ID
                			MAX(super_game_name) as super_game_name , -- 超父游戏名
                			-- server_id, -- 区服ID
                			MAX(server_name) as server_name, -- 区服名
                			boss_server_id, -- 合服ID
                			MAX(boss_server_name) as boss_server_name, -- 合服名
                			MIN(merge_time) as merge_time , -- 合服时间
                			MAX(sum_server_count) as sum_server_count, -- 合服次数
                			SUM(server_combat_num) as server_combat_num, -- 区服战力
                		    SUM(total_amount) as total_amount, -- 总付费金额
                		    SUM(ba1) as ba1, -- 今日付费金额
                		    SUM(ba1_count) as ba1_count, -- 今日付费次数
                		    SUM(ba2) as ba2, -- 昨日付费金额
                		    SUM(ba2_count) as ba2_count, -- 昨日付费次数
                		    SUM(ba3) as ba3, -- 前天付费金额
                		    SUM(ba3_count) as ba3_count -- 前天付费次数
                		FROM (
                			SELECT
                				-- 子查询，查询每个父游戏-区服维度的信息
                				source_system,
                				parent_game_id, -- 父游戏ID
                				MAX(parent_game_name) as parent_game_name, -- 父游戏名
                				super_game_id , -- 超父游戏ID
                				MAX(super_game_name) as super_game_name , -- 超父游戏名
                				server_id, -- 区服ID
                				MAX(server_name) as server_name, -- 区服名
                				boss_server_id, -- 合服ID
                				MAX(boss_server_name) as boss_server_name, -- 合服名
                				MIN(merge_time) as merge_time , -- 合服时间
                				MAX(sum_server_count) as sum_server_count, -- 合服次数
                				SUM(server_combat_num) as server_combat_num, -- 区服战力
                			    SUM(total_amount) as total_amount, -- 总付费金额
                			    SUM(ba1) as ba1, -- 今日付费金额
                			    SUM(ba1_count) as ba1_count, -- 今日付费次数
                			    SUM(ba2) as ba2, -- 昨日付费金额
                			    SUM(ba2_count) as ba2_count, -- 昨日付费次数
                			    SUM(ba3) as ba3, -- 前天付费金额
                			    SUM(ba3_count) as ba3_count -- 前天付费次数
                			FROM (
                				-- 子查询，查询每个子游戏-原始区服维度的信息
                				SELECT
                					source_system,
                					game_id, -- 子游戏ID
                					MAX(game_name) as game_name, -- 子游戏名
                					parent_game_id, -- 父游戏ID
                					MAX(parent_game_name) as parent_game_name, -- 父游戏名
                					super_game_id , -- 超父游戏ID
                					MAX(super_game_name) as super_game_name , -- 超父游戏名
                					server_id, -- 区服ID
                					MAX(server_name) as server_name, -- 区服名
                					boss_server_id, -- 合服ID
                					MAX(boss_server_name) as boss_server_name, -- 合服名
                					MIN(merge_time) as merge_time , -- 合服时间
                					MAX(sum_server_count) as sum_server_count, -- 合服次数
                					MAX(server_combat_num) as server_combat_num, -- 区服战力
                					MAX(total_amount) as total_amount, -- 总付费金额
                					MAX(ba1) as ba1, -- 今日付费金额
                					MAX(ba1_count) as ba1_count, -- 今日付费次数
                					MAX(ba2) as ba2, -- 昨日付费金额
                					MAX(ba2_count) as ba2_count, -- 昨日付费次数
                					MAX(ba3) as ba3, -- 前天付费金额
                					MAX(ba3_count) as ba3_count -- 前天付费次数
                				FROM game_ads.ads_role_combat_num_ranking
                				GROUP BY game_id ,parent_game_id ,super_game_id ,server_id ,boss_server_id ,source_system
                			) a GROUP BY parent_game_id ,super_game_id ,server_id ,boss_server_id ,source_system
                		) a GROUP BY super_game_id ,boss_server_id ,source_system
                	) a
                	LEFT JOIN (
                		SELECT
                			a.source_system as b_source_system,
                			a.server_id as b_server_id,
                			a.game_id as b_game_id,
                			MAX(a.server_name) as server_name,
                			MAX(a.main_server_id) as main_server_id, -- 主服ID
                			MAX(c.server_name) as main_server_name, -- 主服名
                			MAX(a.son_server_ids) as son_server_ids , -- 合服子服ID列表
                			MAX(d.son_server_ids_name) as son_server_ids_name , -- 合服子服ID列表
                			GROUP_CONCAT(a.source_server_ids, ',') as source_server_ids, -- 原始子服ID列表
                			GROUP_CONCAT(b.server_name , ',') as source_server_ids_name, -- 原始子服名
                			MIN(e.start_time) as min_server_start_time -- 最小原始子服开服时间
                		FROM (
                			SELECT
                				*
                			FROM dm_game_order.t_game_server_merge
                			WHERE is_delete = 0
                		) a
                		LEFT JOIN (
                			SELECT
                				*
                			FROM dm_game_order.t_game_server_merge
                			WHERE is_delete = 0
                		) b on a.source_system = b.source_system AND a.game_id = b.game_id AND a.source_server_ids = b.server_id
                		LEFT JOIN (
                			SELECT
                				source_system ,
                				game_id ,
                				server_id  ,
                				MAX(server_name) as server_name
                			FROM dm_game_order.t_game_server_merge
                			WHERE is_delete = 0
                			GROUP BY server_id ,source_system ,game_id
                		) c on a.source_system = c.source_system AND a.game_id = c.game_id AND a.main_server_id = c.server_id
                		LEFT JOIN (
                	 		SELECT
                	 			a.source_system ,
                	 			a.game_id,
                	 			a.server_id,
                	 			GROUP_CONCAT(b.server_name , ',') as son_server_ids_name -- 合服列表名
                	 		FROM (
                	 			SELECT
                	 				*
                	 			FROM dm_game_order.t_game_server_son_merge
                	 			WHERE is_delete = 0
                	 		) a
                	 		LEFT JOIN (
                	 			SELECT
                	 				source_system ,
                	 				game_id ,
                	 				server_id  ,
                	 				MAX(server_name) as server_name
                	 			FROM dm_game_order.t_game_server_merge
                	 			WHERE is_delete = 0
                	 			GROUP BY server_id ,source_system ,game_id
                	 		) b on a.source_system = b.source_system AND a.game_id = b.game_id AND a.son_server_ids = b.server_id
                	 		GROUP BY a.source_system , a.game_id, a.server_id
                	 	) d on a.source_system = d.source_system AND a.game_id = d.game_id AND a.server_id = d.server_id
                		LEFT JOIN (
                			SELECT
                				*
                			FROM dm_game_order.t_game_server_merge
                			WHERE is_delete = 0
                		) e on a.source_system = e.source_system AND a.source_server_ids = e.server_id AND a.game_id = e.game_id
                		group by a.server_id , a.source_system , a.game_id
                	) b ON a.source_system = b.b_source_system AND a.super_game_id = b.b_game_id AND a.boss_server_id = b.b_server_id
                	LEFT JOIN (
                		SELECT
                			-- 超父-合服维度
                			source_system as c_source_system,
                			super_game_id as c_super_game_id,
                			boss_server_id as c_boss_server_id,
                			COUNT(DISTINCT role_id) as role_count -- 角色数量
                		FROM game_ads.ads_role_combat_num_ranking
                		GROUP BY super_game_id, source_system, boss_server_id
                	) c on a.source_system = c.c_source_system AND a.super_game_id = c.c_super_game_id AND a.boss_server_id = c.c_boss_server_id
                	LEFT JOIN (
                		SELECT
                			-- 超父-合服维度
                			a.source_system as d_source_system,
                			a.super_game_id as d_super_game_id,
                			b.boss_server_id as d_boss_server_id,
                			COUNT(a.association_user_id) as new_user_count -- 新用户人数
                		FROM (
                			SELECT
                				a.source_system ,
                				b.id,
                				a.game_id ,
                				a.server_id,
                				a.role_id ,
                				a.role_name ,
                				b.association_user_id ,
                				a.create_time ,
                				IFNULL(c.super_game_id , a.game_id) as super_game_id,
                				ROW_NUMBER()over(partition by b.association_user_id , a.source_system order by a.create_time asc, a.user_id asc) as num
                			FROM dm_game_order.t_game_user_role a
                			LEFT JOIN dm_game_order.t_game_user b on a.source_system = b.source_system AND a.user_id = b.id
                			LEFT JOIN dm_game_order.t_game c on a.source_system = c.source_system AND a.game_id = c.id
                		) a
                		LEFT JOIN (
                			SELECT
                				source_system,
                				server_id,
                				boss_server_id,
                				super_game_id
                			FROM game_ads.ads_role_combat_num_ranking
                			GROUP BY source_system, super_game_id, server_id, boss_server_id
                		) b on a.source_system = b.source_system AND a.server_id = b.server_id AND a.super_game_id = b.super_game_id
                		WHERE num = 1
                		GROUP BY a.super_game_id, b.boss_server_id, a.source_system
                	) d on a.source_system = d.d_source_system AND a.super_game_id = d.d_super_game_id AND a.boss_server_id = d.d_boss_server_id
                	LEFT JOIN (
                		SELECT
                			-- 超父-合服维度
                			source_system as e_source_system,
                			super_game_id as e_super_game_id,
                			boss_server_id as e_boss_server_id,
                			COUNT(DISTINCT user_id) as create_role_count -- 创角人数
                		FROM game_ads.ads_role_combat_num_ranking
                		GROUP BY super_game_id, source_system, boss_server_id
                	) e on a.source_system = e.e_source_system AND a.super_game_id = e.e_super_game_id AND a.boss_server_id = e.e_boss_server_id
                	LEFT JOIN (
                		SELECT
                			-- 超父-合服维度
                			a.source_system as f_source_system,
                			c.super_game_id as f_super_game_id,
                			d.boss_server_id as f_boss_server_id,
                			count(DISTINCT IF(Date(NOW()) = day,user_id,NULL)) as ba1_num, -- 今日充值人数
                			count(DISTINCT IF(date_add(Date(NOW()),-1)=day,user_id,NULL)) as ba2_num, -- 昨日充值人数
                			count(DISTINCT IF(date_add(Date(NOW()),-2)=day,user_id,NULL)) as ba3_num, -- 前天充值人数
                			count(DISTINCT user_id) as total_num -- 总充值人数
                		FROM dm_game_order.t_game_server a
                		LEFT JOIN game_dw.dw_order_day_amount b
                		on a.source_system = b.source_system and a.server_id =b.server_id and a.game_id =b.game_id
                		LEFT JOIN (
                			SELECT
                				source_system ,
                				id,
                				IFNULL(parent_id, id) as parent_id,
                				IFNULL(super_game_id, id) as super_game_id
                			FROM dm_game_order.t_game
                		) c on a.source_system = c.source_system AND a.game_id = c.id
                		LEFT JOIN (
                			SELECT
                				source_system,
                				server_id,
                				boss_server_id,
                				super_game_id
                			FROM game_ads.ads_role_combat_num_ranking
                			GROUP BY source_system, super_game_id, server_id, boss_server_id
                		) d on a.source_system = d.source_system AND a.server_id = d.server_id AND c.super_game_id = d.super_game_id
                		group by a.source_system ,d.boss_server_id, c.super_game_id
                	) f on a.source_system = f.f_source_system AND a.super_game_id = f.f_super_game_id AND a.boss_server_id = f.f_boss_server_id
                	LEFT JOIN (
                		select
                			-- 超父-合服维度
                			a.source_system as g_source_system,
                			d.boss_server_id as g_boss_server_id,
                			c.super_game_id as g_super_game_id,
                			count(distinct IF(Date(NOW()) = dt,association_user_id,NULL)) as ba1_active_num, -- 今日活跃人数
                			count(distinct IF(date_add(Date(NOW()),-1)=dt,association_user_id,NULL)) as ba2_active_num, -- 昨日活跃人数
                			count(distinct IF(date_add(Date(NOW()),-2)=dt,association_user_id,NULL)) as ba3_active_num -- 前日活跃人数
                		from dm_game_order.t_game_server a
                		left join game_dw.dw_active_log b on a.source_system = b.source_system and a.server_id = b.server_id and a.game_id = b.game_id
                		LEFT JOIN (
                			SELECT
                				source_system ,
                				id,
                				IFNULL(parent_id, id) as parent_id,
                				IFNULL(super_game_id, id) as super_game_id
                			FROM dm_game_order.t_game
                		) c on a.source_system = c.source_system AND a.game_id = c.id
                		LEFT JOIN (
                			SELECT
                				source_system,
                				server_id,
                				boss_server_id,
                				super_game_id
                			FROM game_ads.ads_role_combat_num_ranking
                			GROUP BY source_system, super_game_id, server_id, boss_server_id
                		) d on a.source_system = d.source_system AND a.server_id = d.server_id AND c.super_game_id = d.super_game_id
                		group by a.source_system ,d.boss_server_id, c.super_game_id
                	) g on a.source_system = g.g_source_system AND a.super_game_id = g.g_super_game_id AND a.boss_server_id = g.g_boss_server_id
                	LEFT JOIN (
                		SELECT
                			source_system as h_source_system,
                			game_id,
                			server_id,
                			server_name,
                			is_merge -- 是否参与过合服
                		FROM dm_game_order.t_game_server_merge
                		WHERE is_delete = 0
                	) h on a.source_system = h.h_source_system AND a.boss_server_id = h.server_id AND a.super_game_id = h.game_id
                	GROUP BY source_system,boss_server_id,super_game_id
                ) a
                """;
    }

    /**
     * 合服-超父游戏维度（区服相关信息）(总记录数)
     *
     * @return String
     */
    private String getRoleCombatRankServerDataSqlDCount(Criteria cri) {
        return """
                SELECT
                	COUNT(1)
                FROM
                (
                	SELECT
                		-- 查询每个超父游戏-合服维度的信息
                		source_system,
                		super_game_id , -- 超父游戏ID
                		MAX(super_game_name) as super_game_name , -- 超父游戏名
                		boss_server_id as server_id, -- 合服ID
                		IFNULL(MAX(h.server_name), MAX(a.boss_server_name)) as server_name, -- 合服名
                		MIN(merge_time) as dt, -- 合服时间
                		TIMESTAMPDIFF(DAY, DATE(MIN(merge_time)), DATE_ADD(Date(NOW()),1)) start_day, -- 开服天数
                		MAX(sum_server_count) as sum_server_count, -- 合服次数
                		IFNULL(MAX(h.is_merge), 0) as is_merge, -- 是否参与过合服
                		IFNULL(MIN(b.min_server_start_time), MIN(merge_time)) as min_server_start_time, -- 最小原始子服开服时间
                  	    IFNULL(TIMESTAMPDIFF(DAY, DATE(MIN(b.min_server_start_time)), DATE_ADD(Date(NOW()),1)),
                  	    	TIMESTAMPDIFF(DAY, DATE(MIN(merge_time)), DATE_ADD(Date(NOW()),1))) as min_server_start_days, -- 最小原始子服开服距今天数
                		MAX(b.main_server_id) as main_server_id, -- 主服ID
                		MAX(b.main_server_name) as main_server_name, -- 主服名
                		MAX(son_server_ids) as son_server_ids, -- 合服ID列表
                		MAX(son_server_ids_name) as son_server_ids_name, -- 合服ID列表
                	    MAX(source_server_ids) as source_server_ids, -- 原始子服ID列表
                	    MAX(source_server_ids_name) as source_server_ids_name, -- 原始子服名列表
                		MAX(server_combat_num) as server_combat_num, -- 区服战力
                	    MAX(d.new_user_count) as new_user_count, -- 新用户人数
                	    MAX(e.create_role_count) as create_role_count, -- 创角人数
                	    MAX(c.role_count) as role_count, -- 角色数量
                	    MAX(f.total_num) as total_num, -- 总付费人数
                	    MAX(total_amount) as total_amount, -- 总付费金额
                	    ROUND(IF(MAX(f.total_num) > 0, MAX(total_amount) / MAX(f.total_num), 0), 2) as total_arppu, -- 总付费ARPPU
                	    ROUND(IF(MAX(e.create_role_count) > 0, MAX(f.total_num) / MAX(e.create_role_count), 0), 4) as amount_rate, -- 付费率
                	    MAX(ba1) as ba1, -- 今日付费金额
                	    MAX(f.ba1_num) as ba1_num, -- 今日付费人数
                	    MAX(ba1_count) as ba1_count, -- 今日付费次数
                	    MAX(g.ba1_active_num) as ba1_active_num, -- 今日活跃人数
                	    MAX(ba2) as ba2, -- 昨日付费金额
                	    MAX(f.ba2_num) as ba2_num, -- 昨日付费人数
                	    MAX(ba2_count) as ba2_count, -- 昨日付费次数
                	    MAX(g.ba2_active_num) as ba2_active_num, -- 昨日活跃人数
                	    MAX(ba3) as ba3, -- 前天付费金额
                	    MAX(f.ba3_num) as ba3_num, -- 前天付费人数
                	    MAX(ba3_count) as ba3_count, -- 前天付费次数
                	    MAX(g.ba3_active_num) as ba3_active_num -- 前天活跃人数
                	FROM (
                		SELECT
                			-- 子查询，查询每个超父游戏-原始区服维度的信息
                			source_system,
                			super_game_id , -- 超父游戏ID
                			MAX(super_game_name) as super_game_name , -- 超父游戏名
                			-- server_id, -- 区服ID
                			MAX(server_name) as server_name, -- 区服名
                			boss_server_id, -- 合服ID
                			MAX(boss_server_name) as boss_server_name, -- 合服名
                			MIN(merge_time) as merge_time , -- 合服时间
                			MAX(sum_server_count) as sum_server_count, -- 合服次数
                			SUM(server_combat_num) as server_combat_num, -- 区服战力
                		    SUM(total_amount) as total_amount, -- 总付费金额
                		    SUM(ba1) as ba1, -- 今日付费金额
                		    SUM(ba1_count) as ba1_count, -- 今日付费次数
                		    SUM(ba2) as ba2, -- 昨日付费金额
                		    SUM(ba2_count) as ba2_count, -- 昨日付费次数
                		    SUM(ba3) as ba3, -- 前天付费金额
                		    SUM(ba3_count) as ba3_count -- 前天付费次数
                		FROM (
                			SELECT
                				-- 子查询，查询每个父游戏-区服维度的信息
                				source_system,
                				parent_game_id, -- 父游戏ID
                				MAX(parent_game_name) as parent_game_name, -- 父游戏名
                				super_game_id , -- 超父游戏ID
                				MAX(super_game_name) as super_game_name , -- 超父游戏名
                				server_id, -- 区服ID
                				MAX(server_name) as server_name, -- 区服名
                				boss_server_id, -- 合服ID
                				MAX(boss_server_name) as boss_server_name, -- 合服名
                				MIN(merge_time) as merge_time , -- 合服时间
                				MAX(sum_server_count) as sum_server_count, -- 合服次数
                				SUM(server_combat_num) as server_combat_num, -- 区服战力
                			    SUM(total_amount) as total_amount, -- 总付费金额
                			    SUM(ba1) as ba1, -- 今日付费金额
                			    SUM(ba1_count) as ba1_count, -- 今日付费次数
                			    SUM(ba2) as ba2, -- 昨日付费金额
                			    SUM(ba2_count) as ba2_count, -- 昨日付费次数
                			    SUM(ba3) as ba3, -- 前天付费金额
                			    SUM(ba3_count) as ba3_count -- 前天付费次数
                			FROM (
                				-- 子查询，查询每个子游戏-原始区服维度的信息
                				SELECT
                					source_system,
                					game_id, -- 子游戏ID
                					MAX(game_name) as game_name, -- 子游戏名
                					parent_game_id, -- 父游戏ID
                					MAX(parent_game_name) as parent_game_name, -- 父游戏名
                					super_game_id , -- 超父游戏ID
                					MAX(super_game_name) as super_game_name , -- 超父游戏名
                					server_id, -- 区服ID
                					MAX(server_name) as server_name, -- 区服名
                					boss_server_id, -- 合服ID
                					MAX(boss_server_name) as boss_server_name, -- 合服名
                					MIN(merge_time) as merge_time , -- 合服时间
                					MAX(sum_server_count) as sum_server_count, -- 合服次数
                					MAX(server_combat_num) as server_combat_num, -- 区服战力
                					MAX(total_amount) as total_amount, -- 总付费金额
                					MAX(ba1) as ba1, -- 今日付费金额
                					MAX(ba1_count) as ba1_count, -- 今日付费次数
                					MAX(ba2) as ba2, -- 昨日付费金额
                					MAX(ba2_count) as ba2_count, -- 昨日付费次数
                					MAX(ba3) as ba3, -- 前天付费金额
                					MAX(ba3_count) as ba3_count -- 前天付费次数
                				FROM game_ads.ads_role_combat_num_ranking
                				GROUP BY game_id ,parent_game_id ,super_game_id ,server_id ,boss_server_id ,source_system
                			) a GROUP BY parent_game_id ,super_game_id ,server_id ,boss_server_id ,source_system
                		) a GROUP BY super_game_id ,boss_server_id ,source_system
                	) a
                	LEFT JOIN (
                		SELECT
                			a.source_system as b_source_system,
                			a.server_id as b_server_id,
                			a.game_id as b_game_id,
                			MAX(a.server_name) as server_name,
                			MAX(a.main_server_id) as main_server_id, -- 主服ID
                			MAX(c.server_name) as main_server_name, -- 主服名
                			MAX(a.son_server_ids) as son_server_ids , -- 合服子服ID列表
                			MAX(d.son_server_ids_name) as son_server_ids_name , -- 合服子服ID列表
                			GROUP_CONCAT(a.source_server_ids, ',') as source_server_ids, -- 原始子服ID列表
                			GROUP_CONCAT(b.server_name , ',') as source_server_ids_name, -- 原始子服名
                			MIN(e.start_time) as min_server_start_time -- 最小原始子服开服时间
                		FROM (
                			SELECT
                				*
                			FROM dm_game_order.t_game_server_merge
                			WHERE is_delete = 0
                		) a
                		LEFT JOIN (
                			SELECT
                				*
                			FROM dm_game_order.t_game_server_merge
                			WHERE is_delete = 0
                		) b on a.source_system = b.source_system AND a.game_id = b.game_id AND a.source_server_ids = b.server_id
                		LEFT JOIN (
                			SELECT
                				source_system ,
                				game_id ,
                				server_id  ,
                				MAX(server_name) as server_name
                			FROM dm_game_order.t_game_server_merge
                			WHERE is_delete = 0
                			GROUP BY server_id ,source_system ,game_id
                		) c on a.source_system = c.source_system AND a.game_id = c.game_id AND a.main_server_id = c.server_id
                		LEFT JOIN (
                	 		SELECT
                	 			a.source_system ,
                	 			a.game_id,
                	 			a.server_id,
                	 			GROUP_CONCAT(b.server_name , ',') as son_server_ids_name -- 合服列表名
                	 		FROM (
                	 			SELECT
                	 				*
                	 			FROM dm_game_order.t_game_server_son_merge
                	 			WHERE is_delete = 0
                	 		) a
                	 		LEFT JOIN (
                	 			SELECT
                	 				source_system ,
                	 				game_id ,
                	 				server_id  ,
                	 				MAX(server_name) as server_name
                	 			FROM dm_game_order.t_game_server_merge
                	 			WHERE is_delete = 0
                	 			GROUP BY server_id ,source_system ,game_id
                	 		) b on a.source_system = b.source_system AND a.game_id = b.game_id AND a.son_server_ids = b.server_id
                	 		GROUP BY a.source_system , a.game_id, a.server_id
                	 	) d on a.source_system = d.source_system AND a.game_id = d.game_id AND a.server_id = d.server_id
                		LEFT JOIN (
                			SELECT
                				*
                			FROM dm_game_order.t_game_server_merge
                			WHERE is_delete = 0
                		) e on a.source_system = e.source_system AND a.source_server_ids = e.server_id AND a.game_id = e.game_id
                		group by a.server_id , a.source_system , a.game_id
                	) b ON a.source_system = b.b_source_system AND a.super_game_id = b.b_game_id AND a.boss_server_id = b.b_server_id
                	LEFT JOIN (
                		SELECT
                			-- 超父-合服维度
                			source_system as c_source_system,
                			super_game_id as c_super_game_id,
                			boss_server_id as c_boss_server_id,
                			COUNT(DISTINCT role_id) as role_count -- 角色数量
                		FROM game_ads.ads_role_combat_num_ranking
                		GROUP BY super_game_id, source_system, boss_server_id
                	) c on a.source_system = c.c_source_system AND a.super_game_id = c.c_super_game_id AND a.boss_server_id = c.c_boss_server_id
                	LEFT JOIN (
                		SELECT
                			-- 超父-合服维度
                			a.source_system as d_source_system,
                			a.super_game_id as d_super_game_id,
                			b.boss_server_id as d_boss_server_id,
                			COUNT(a.association_user_id) as new_user_count -- 新用户人数
                		FROM (
                			SELECT
                				a.source_system ,
                				b.id,
                				a.game_id ,
                				a.server_id,
                				a.role_id ,
                				a.role_name ,
                				b.association_user_id ,
                				a.create_time ,
                				IFNULL(c.super_game_id , a.game_id) as super_game_id,
                				ROW_NUMBER()over(partition by b.association_user_id , a.source_system order by a.create_time asc, a.user_id asc) as num
                			FROM dm_game_order.t_game_user_role a
                			LEFT JOIN dm_game_order.t_game_user b on a.source_system = b.source_system AND a.user_id = b.id
                			LEFT JOIN dm_game_order.t_game c on a.source_system = c.source_system AND a.game_id = c.id
                		) a
                		LEFT JOIN (
                			SELECT
                				source_system,
                				server_id,
                				boss_server_id,
                				super_game_id
                			FROM game_ads.ads_role_combat_num_ranking
                			GROUP BY source_system, super_game_id, server_id, boss_server_id
                		) b on a.source_system = b.source_system AND a.server_id = b.server_id AND a.super_game_id = b.super_game_id
                		WHERE num = 1
                		GROUP BY a.super_game_id, b.boss_server_id, a.source_system
                	) d on a.source_system = d.d_source_system AND a.super_game_id = d.d_super_game_id AND a.boss_server_id = d.d_boss_server_id
                	LEFT JOIN (
                		SELECT
                			-- 超父-合服维度
                			source_system as e_source_system,
                			super_game_id as e_super_game_id,
                			boss_server_id as e_boss_server_id,
                			COUNT(DISTINCT user_id) as create_role_count -- 创角人数
                		FROM game_ads.ads_role_combat_num_ranking
                		GROUP BY super_game_id, source_system, boss_server_id
                	) e on a.source_system = e.e_source_system AND a.super_game_id = e.e_super_game_id AND a.boss_server_id = e.e_boss_server_id
                	LEFT JOIN (
                		SELECT
                			-- 超父-合服维度
                			a.source_system as f_source_system,
                			c.super_game_id as f_super_game_id,
                			d.boss_server_id as f_boss_server_id,
                			count(DISTINCT IF(Date(NOW()) = day,user_id,NULL)) as ba1_num, -- 今日充值人数
                			count(DISTINCT IF(date_add(Date(NOW()),-1)=day,user_id,NULL)) as ba2_num, -- 昨日充值人数
                			count(DISTINCT IF(date_add(Date(NOW()),-2)=day,user_id,NULL)) as ba3_num, -- 前天充值人数
                			count(DISTINCT user_id) as total_num -- 总充值人数
                		FROM dm_game_order.t_game_server a
                		LEFT JOIN game_dw.dw_order_day_amount b
                		on a.source_system = b.source_system and a.server_id =b.server_id and a.game_id =b.game_id
                		LEFT JOIN (
                			SELECT
                				source_system ,
                				id,
                				IFNULL(parent_id, id) as parent_id,
                				IFNULL(super_game_id, id) as super_game_id
                			FROM dm_game_order.t_game
                		) c on a.source_system = c.source_system AND a.game_id = c.id
                		LEFT JOIN (
                			SELECT
                				source_system,
                				server_id,
                				boss_server_id,
                				super_game_id
                			FROM game_ads.ads_role_combat_num_ranking
                			GROUP BY source_system, super_game_id, server_id, boss_server_id
                		) d on a.source_system = d.source_system AND a.server_id = d.server_id AND c.super_game_id = d.super_game_id
                		group by a.source_system ,d.boss_server_id, c.super_game_id
                	) f on a.source_system = f.f_source_system AND a.super_game_id = f.f_super_game_id AND a.boss_server_id = f.f_boss_server_id
                	LEFT JOIN (
                		select
                			-- 超父-合服维度
                			a.source_system as g_source_system,
                			d.boss_server_id as g_boss_server_id,
                			c.super_game_id as g_super_game_id,
                			count(distinct IF(Date(NOW()) = dt,association_user_id,NULL)) as ba1_active_num, -- 今日活跃人数
                			count(distinct IF(date_add(Date(NOW()),-1)=dt,association_user_id,NULL)) as ba2_active_num, -- 昨日活跃人数
                			count(distinct IF(date_add(Date(NOW()),-2)=dt,association_user_id,NULL)) as ba3_active_num -- 前日活跃人数
                		from dm_game_order.t_game_server a
                		left join game_dw.dw_active_log b on a.source_system = b.source_system and a.server_id = b.server_id and a.game_id = b.game_id
                		LEFT JOIN (
                			SELECT
                				source_system ,
                				id,
                				IFNULL(parent_id, id) as parent_id,
                				IFNULL(super_game_id, id) as super_game_id
                			FROM dm_game_order.t_game
                		) c on a.source_system = c.source_system AND a.game_id = c.id
                		LEFT JOIN (
                			SELECT
                				source_system,
                				server_id,
                				boss_server_id,
                				super_game_id
                			FROM game_ads.ads_role_combat_num_ranking
                			GROUP BY source_system, super_game_id, server_id, boss_server_id
                		) d on a.source_system = d.source_system AND a.server_id = d.server_id AND c.super_game_id = d.super_game_id
                		group by a.source_system ,d.boss_server_id, c.super_game_id
                	) g on a.source_system = g.g_source_system AND a.super_game_id = g.g_super_game_id AND a.boss_server_id = g.g_boss_server_id
                	LEFT JOIN (
                		SELECT
                			source_system as h_source_system,
                			game_id,
                			server_id,
                			server_name,
                			is_merge -- 是否参与过合服
                		FROM dm_game_order.t_game_server_merge
                		WHERE is_delete = 0
                	) h on a.source_system = h.h_source_system AND a.boss_server_id = h.server_id AND a.super_game_id = h.game_id
                	GROUP BY source_system,boss_server_id,super_game_id
                ) a
                """ + cri;
    }

    /**
     * 原始区服-父游戏维度（角色排行相关信息）
     *
     * @return String
     */
    private String getRoleCombatRankRoleDataSqlA() {
        return """
                SELECT
                	*
                FROM (
                	SELECT
                		source_system ,
                		server_id ,
                		boss_server_id,
                		parent_game_id ,
                		super_game_id ,
                		dt,
                		merge_time,
                		role_id ,
                		role_name,
                		role_level ,
                		role_vip ,
                		combat_num ,
                		ROW_NUMBER()over(partition by source_system ,server_id ,boss_server_id, parent_game_id, super_game_id order by combat_num desc) as rank_num
                	FROM (
                		SELECT
                			source_system ,
                			server_id ,
                			boss_server_id,
                			parent_game_id ,
                			super_game_id ,
                			MAX(`date`) as dt,
                			MAX(merge_time) as merge_time ,
                			role_id ,
                			MAX(role_name) as role_name,
                			MAX(role_level) as role_level ,
                			MAX(role_vip) as role_vip ,
                			MAX(combat_num) as combat_num
                		FROM game_ads.ads_role_combat_num_ranking
                		GROUP BY role_id , source_system ,parent_game_id ,super_game_id, server_id, boss_server_id
                	) a
                ) a
                """;
    }

    /**
     * 合服-父游戏维度（角色排行相关信息）
     *
     * @return String
     */
    private String getRoleCombatRankRoleDataSqlB() {
        return """
                SELECT
                	*
                FROM (
                	SELECT
                		source_system ,
                		boss_server_id ,
                		parent_game_id ,
                		super_game_id ,
                		merge_time,
                		role_id ,
                		role_name,
                		role_level ,
                		role_vip ,
                		combat_num ,
                		ROW_NUMBER()over(partition by source_system ,boss_server_id ,parent_game_id, super_game_id order by combat_num desc) as rank_num
                	FROM (
                		SELECT
                			source_system ,
                			boss_server_id ,
                			parent_game_id ,
                			super_game_id ,
                			MAX(merge_time) as merge_time ,
                			role_id ,
                			MAX(role_name) as role_name,
                			MAX(role_level) as role_level ,
                			MAX(role_vip) as role_vip ,
                			MAX(combat_num) as combat_num
                		FROM game_ads.ads_role_combat_num_ranking
                		GROUP BY role_id , source_system ,parent_game_id ,super_game_id, boss_server_id
                	) a
                ) a
                """;
    }

    /**
     * 原始区服-超父游戏维度（角色排行相关信息）
     *
     * @return String
     */
    private String getRoleCombatRankRoleDataSqlC() {
        return """
                SELECT
                	*
                FROM (
                	SELECT
                		a.source_system ,
                		a.server_id ,
                		a.super_game_id ,
                		a.dt,
                		a.merge_time,
                		a.role_id ,
                		b.role_name,
                		a.role_level ,
                		IF(b.country = 'null',NULL,b.country) as country,
                		a.role_vip ,
                		a.total_amount ,
                		a.combat_num ,
                		ROW_NUMBER()over(partition by a.source_system ,a.server_id ,a.super_game_id order by a.combat_num desc) as rank_num
                	FROM (
                		SELECT
                			source_system ,
                			server_id ,
                			super_game_id ,
                			MIN(`date`) as dt,
                			MAX(merge_time) as merge_time ,
                			role_id ,
                			SUM(role_total_amount) as total_amount ,
                			MAX(role_name) as role_name,
                			MAX(role_level) as role_level ,
                			MAX(role_vip) as role_vip ,
                			MAX(combat_num) as combat_num
                		FROM game_ads.ads_role_combat_num_ranking a
                		LEFT JOIN (
                			SELECT
                				source_system as b_source_system,
                				server_id as b_server_id,
                				game_id as b_game_id,
                				MAX(is_merge) as b_is_merge
                			FROM dm_game_order.t_game_server_merge
                			GROUP BY source_system , server_id , game_id
                		) b on a.source_system = b.b_source_system AND a.boss_server_id = b.b_server_id AND a.super_game_id = b.b_game_id
                		WHERE b_is_merge = 0
                		GROUP BY role_id , source_system ,super_game_id, server_id , boss_server_id
                	) a LEFT JOIN(
                		SELECT
                			a.source_system,
                			a.role_id,
                			a.role_name,
                			a.role_level,
                			a.country,
                			a.combat_num ,
                			a.game_id ,
                			b.parent_game_id,
                			b.super_game_id ,
                			ROW_NUMBER()over(partition by a.source_system , a.role_id, b.parent_game_id order by a.role_level desc,a.combat_num desc) as num
                		FROM dm_game_order.t_game_user_role a
                		LEFT JOIN (
                			SELECT
                				source_system,
                				id,
                				IFNULL(parent_id, id) as parent_game_id,
                				IFNULL(super_game_id, id) as super_game_id
                			FROM dm_game_order.t_game
                		) b on a.source_system = b.source_system AND a.game_id = b.id
                	) b on a.source_system = b.source_system AND a.role_id = b.role_id AND a.super_game_id = b.super_game_id AND b.num =1
                ) a
                """;
    }

    /**
     * 合服-超父游戏维度（角色排行相关信息）
     *
     * @return String
     */
    private String getRoleCombatRankRoleDataSqlD() {
        return """
                SELECT
                	*
                FROM (
                	SELECT
                		a.source_system ,
                		a.boss_server_id ,
                		a.super_game_id ,
                		a.merge_time,
                		a.role_id ,
                		b.role_name,
                		a.role_level ,
                		a.role_vip ,
                		a.total_amount ,
                		a.combat_num ,
                		IF(b.country = 'null',NULL,b.country) as country,
                		ROW_NUMBER()over(partition by a.source_system ,a.boss_server_id ,a.super_game_id order by a.combat_num desc) as rank_num
                	FROM (
                		SELECT
                			source_system ,
                			boss_server_id ,
                			super_game_id ,
                			MIN(merge_time) as merge_time ,
                			role_id ,
                			SUM(role_total_amount) as total_amount ,
                			MAX(role_name) as role_name,
                			MAX(role_level) as role_level ,
                			MAX(role_vip) as role_vip ,
                			MAX(combat_num) as combat_num
                		FROM game_ads.ads_role_combat_num_ranking
                		GROUP BY role_id , source_system ,super_game_id, boss_server_id
                	) a LEFT JOIN(
                		SELECT
                			a.source_system,
                			a.role_id,
                			a.role_name,
                			a.role_level,
                			a.combat_num ,
                			a.country,
                			a.game_id ,
                			b.parent_game_id,
                			b.super_game_id ,
                			ROW_NUMBER()over(partition by a.source_system , a.role_id, b.super_game_id order by a.role_level desc,a.combat_num desc) as num
                		FROM dm_game_order.t_game_user_role a
                		LEFT JOIN (
                			SELECT
                				source_system,
                				id,
                				IFNULL(parent_id, id) as parent_game_id,
                				IFNULL(super_game_id, id) as super_game_id
                			FROM dm_game_order.t_game
                		) b on a.source_system = b.source_system AND a.game_id = b.id
                	) b on a.source_system = b.source_system AND a.role_id = b.role_id AND a.super_game_id = b.super_game_id AND b.num =1
                ) a
                """;
    }

}
