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

import com.github.sd4324530.jtuple.Tuple2;
import com.zanxiang.game.data.serve.component.DataPowerComponent;
import com.zanxiang.game.data.serve.pojo.dto.PlayerBannedListDTO;
import com.zanxiang.game.data.serve.pojo.dto.PlayerDataListDTO;
import com.zanxiang.game.data.serve.pojo.dto.PlayerLoginLogDataListDTO;
import com.zanxiang.game.data.serve.pojo.dto.PlayerRoleDataListDTO;
import com.zanxiang.game.data.serve.pojo.enums.DeviceTypeEnum;
import com.zanxiang.game.data.serve.pojo.enums.GameCategoryEnum;
import com.zanxiang.game.data.serve.pojo.enums.LogTypeEnum;
import com.zanxiang.game.data.serve.pojo.enums.OrderByEnum;
import com.zanxiang.game.data.serve.pojo.vo.PlayerDataVO;
import com.zanxiang.game.data.serve.pojo.vo.PlayerRoleDataVO;
import com.zanxiang.game.data.serve.service.IPlayerDataService;
import com.zanxiang.game.data.serve.utils.Page;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.BooleanUtils;
import org.apache.commons.lang3.StringUtils;
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.stereotype.Service;

import java.util.Collection;
import java.util.Collections;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * @author tianhua
 * @time 2023/9/6
 * @Description 玩家数据的逻辑实现层
 **/
@Service
@Slf4j
public class PlayerDataServiceImpl implements IPlayerDataService {

    @Autowired
    private Dao dao;
    @Autowired
    private DataPowerComponent dataPowerComponent;

    /**
     * 玩家列表
     *
     * @param dto PlayerDataListDTO
     * @return Page<PlayerDataVO>
     */
    @Override
    public Page<PlayerDataVO> getPlayerDataList(PlayerDataListDTO dto) {
        Tuple2<List<Long>, List<Long>> poerInfo = dataPowerComponent.getPowerInfo();
        List<Long> pitcherIds = StringUtils.isBlank(dto.getPitcherId()) ? poerInfo.first : Collections.singletonList(Long.valueOf(dto.getPitcherId()));
        List<Long> gameIds = dto.getGameId() == null ? poerInfo.second : Collections.singletonList(dto.getGameId());

        //创建查询条件
        Criteria cri = Cnd.cri();
        if (StringUtils.isNotBlank(dto.getSourceSystem())) {
            cri.where().andEquals("source_system", dto.getSourceSystem());
        }
        if (dto.getUserId() != null) {
            cri.where().andEquals("id", dto.getUserId());
        }
        if (StringUtils.isNotBlank(dto.getUserName())) {
            cri.where().andLike("username", dto.getUserName());
        }
        if (StringUtils.isNotBlank(dto.getNickname())) {
            cri.where().andLike("nickname", dto.getNickname());
        }
        if (StringUtils.isNotBlank(dto.getRoleName())) {
            cri.where().andLike("last_game_role_name", dto.getRoleName());
        }
        if (StringUtils.isNotBlank(dto.getMobile())) {
            cri.where().andEquals("mobile", dto.getMobile());
        }
        if (StringUtils.isNotBlank(dto.getRegIp())) {
            cri.where().andEquals("reg_ip", dto.getRegIp());
        }
        if (pitcherIds != null) {
            cri.where().andInList("pitcher_id", pitcherIds);
        }
        //判断是否绑定手机
        if (BooleanUtils.isFalse(dto.getIsBindMobile())) {
            cri.where().andIsNull("mobile");
        }
        if (BooleanUtils.isTrue(dto.getIsBindMobile())) {
            cri.where().andNotIsNull("mobile").andNotEquals("mobile", "null");
        }
        //判断是否实名
        if (BooleanUtils.isFalse(dto.getIsAuth())) {
            //未实名
            cri.where().andEquals("auth", 0);
        }
        if (BooleanUtils.isTrue(dto.getIsAuth())) {
            //实名
            cri.where().andInList("auth", List.of(1L, 2L));
        }
        if (dto.getCpId() != null) {
            cri.where().andEquals("cp_id", dto.getCpId());
        }
        if (gameIds != null) {
            cri.where().andInList("game_id", gameIds);
        }
        if (dto.getGameCategoryId() != null) {
            cri.where().andEquals("game_category_id", dto.getGameCategoryId());
        }
        if (dto.getRechargeBeginDate() != null && dto.getRechargeEndDate() != null) {
            //充值时间
            cri.where().andBetween("DATE(last_recharge_time)", dto.getRechargeBeginDate(), dto.getRechargeEndDate());
        }
        //判断是否充值
        if (BooleanUtils.isFalse(dto.getIsRecharge())) {
            //未充值 充值金额为0
            cri.where().andEquals("recharge_money", 0);
        }
        if (BooleanUtils.isTrue(dto.getIsRecharge())) {
            //充值金额大于0
            cri.where().andNotEquals("recharge_money", 0);
        }
        if (dto.getStatus() != null) {
            cri.where().andEquals("status", dto.getStatus());
        }
        if (dto.getChannelId() != null) {
            //渠道id
            cri.where().andIn("agent_id", dto.getChannelId().toArray(String[]::new));
        }
        if (StringUtils.isNotBlank(dto.getAccountId())) {
            cri.where().andEquals("account_id", dto.getAccountId());
        }
        if (dto.getBeginDate() != null && dto.getEndDate() != null) {
            //注册时间
            cri.where().andBetween("DATE(create_time)", dto.getBeginDate(), dto.getEndDate());
        }
        if (dto.getRegPayIntervalTimeMin() != null) {
            //充值到注册的最小间隔时间（分）
            cri.where().andGTE("TIMESTAMPDIFF(MINUTE, create_time, last_recharge_time)", dto.getRegPayIntervalTimeMin());
        }
        if (dto.getRegPayIntervalTimeMax() != null) {
            //充值到注册的最大间隔时间(分)
            cri.where().andLTE("TIMESTAMPDIFF(MINUTE, create_time, last_recharge_time)", dto.getRegPayIntervalTimeMax());
        }
        //判断是否创角
        if (BooleanUtils.isFalse(dto.getCreateRole())) {
            //未创角 创角数为0
            cri.where().andEquals("role_count", 0);
        }
        if (BooleanUtils.isTrue(dto.getCreateRole())) {
            //创角数大于0
            cri.where().andNotEquals("role_count", 0);
        }
        if (dto.getRoleCountMin() != null) {
            //创角最小数
            cri.where().andGTE("role_count", dto.getRoleCountMin());
        }
        if (dto.getRoleCountMax() != null) {
            //创角最大数
            cri.where().andLTE("role_count", dto.getRoleCountMax());
        }
        if (dto.getUserLastRegTime() != null) {
            //玩家最新染色时间
            cri.where().andEquals("DATE(user_last_reg_time)", dto.getUserLastRegTime());
        }
        if (dto.getUserLastPitcherId() != null) {
            //玩家最新染色归因投手
            cri.where().andEquals("user_last_pitcher_id", dto.getUserLastPitcherId());
        }
        if (dto.getUserLastRegAgentId() != null) {
            //玩家最新染色归因投手
            cri.where().andEquals("user_last_reg_agent_id", dto.getUserLastRegAgentId());
        }

        //设置pager
        Pager pager = dao.createPager(dto.getPageNum(), dto.getPageSize());
        //设置查询的记录数
        Sql countSql = Sqls.create(getPlayerDataListCountSql() + 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.isBlank(dto.getSortFiled())) {
            cri.getOrderBy().orderBy("update_time", dto.getSortType());
        } else {
            cri.getOrderBy().orderBy(dto.getSortFiled(), dto.getSortFiled());
        }
        //创建sql查询数据
        Sql sql = Sqls.create(getPlayerDataListSql() + cri);
        //sql设置pager
        sql.setPager(pager);
        //设置自定义回传对象
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(PlayerDataVO.class));
        //执行sql
        dao.execute(sql);
        //处理结果
        List<PlayerDataVO> list = sql.getList(PlayerDataVO.class).stream().map(vo -> {
            //通过枚举得到游戏应用类型名称
            if (vo.getGameCategoryId() != null) {
                vo.setGameCategoryName(GameCategoryEnum.getNameByCategory(vo.getGameCategoryId()));
            }
            //修改手机号的显示
            if ("null".equals(vo.getMobile())) {
                vo.setMobile(null);
            }
            return vo;
        }).collect(Collectors.toList());

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

    /**
     * 玩家角色列表
     *
     * @param dto PlayerRoleDataListDTO
     * @return Page<PlayerRoleDataVO>
     */
    @Override
    public Page<PlayerRoleDataVO> getPlayerRoleDataList(PlayerRoleDataListDTO dto) {
        Tuple2<List<Long>, List<Long>> poerInfo = dataPowerComponent.getPowerInfo();
        List<Long> pitcherIds = StringUtils.isBlank(dto.getPitcherId()) ? poerInfo.first : Collections.singletonList(Long.valueOf(dto.getPitcherId()));
        List<Long> gameIds = dto.getGameId() == null ? poerInfo.second : Collections.singletonList(dto.getGameId());
        //查询条件
        Criteria cri = Cnd.cri();
        if (dto.getUserId() != null) {
            cri.where().andEquals("user_id", dto.getUserId());
        }
        if (StringUtils.isNotBlank(dto.getUserName())) {
            //玩家账号
            cri.where().andEquals("username", dto.getUserName());
        }
        if (StringUtils.isNotBlank(dto.getNickname())) {
            //玩家昵称
            cri.where().andLike("nickname", dto.getNickname());
        }
        if (StringUtils.isNotBlank(dto.getRegIp())) {
            //玩家注册IP
            cri.where().andEquals("reg_ip", dto.getRegIp());
        }
        if (StringUtils.isNotBlank(dto.getServerName())) {
            //游戏区服
            cri.where().andLike("server_name", dto.getServerName());
        }
        if (StringUtils.isNotBlank(dto.getRoleName())) {
            cri.where().andLike("role_name", dto.getRoleName());
        }
        if (StringUtils.isNotBlank(dto.getRoleId())) {
            cri.where().andEquals("role_id", dto.getRoleId());
        }
        if (dto.getCpId() != null) {
            cri.where().andEquals("cp_id", dto.getCpId());
        }
        if (dto.getGameCategoryId() != null) {
            //游戏应用类型
            cri.where().andEquals("game_category_id", dto.getGameCategoryId());
        }
        if (StringUtils.isNotBlank(dto.getAccountId())) {
            //推广账号ID
            cri.where().andEquals("account_id", dto.getAccountId());
        }
        if (CollectionUtils.isNotEmpty(dto.getChannelId())) {
            //渠道ID
            cri.where().andIn("agent_id", dto.getChannelId().toArray(String[]::new));
        }
        if (pitcherIds != null) {
            cri.where().andInList("pitcher_id", pitcherIds);
        }
        if (gameIds != null) {
            cri.where().andInList("game_id", gameIds);
        }
        if (StringUtils.isNotBlank(dto.getOs())) {
            cri.where().andEquals("os", dto.getOs());
        }
        //判断是否充值
        if (BooleanUtils.isFalse(dto.getIsRecharge())) {
            //未充值 充值金额为0
            cri.where().andEquals("recharge_money", 0);
        }
        if (BooleanUtils.isTrue(dto.getIsRecharge())) {
            //充值金额大于0
            cri.where().andNotEquals("recharge_money", 0);
        }
        if (dto.getVipLevel() != null) {
            cri.where().andEquals("role_vip_level", dto.getVipLevel());
        }
        if (dto.getRoleLevelMin() != null) {
            //玩家最小等级
            cri.where().andGTE("role_level", dto.getRoleLevelMin());
        }
        if (dto.getRoleLevelMax() != null) {
            //玩家最大等级
            cri.where().andLTE("role_level", dto.getRoleLevelMax());
        }
        if (dto.getBeginDate() != null && dto.getEndDate() != null) {
            //角色创建时间
            cri.where().andBetween("DATE(create_time)", dto.getBeginDate(), dto.getEndDate());
        }
        if (dto.getRechargeBeginDate() != null && dto.getRechargeEndDate() != null) {
            //最近充值时间
            cri.where().andBetween("DATE(last_recharge_time)", dto.getRechargeBeginDate(), dto.getRechargeEndDate());
        }
        if (dto.getRegTimeBeginDate() != null && dto.getRegTimeEndDate() != null) {
            //玩家注册时间
            cri.where().andBetween("DATE(reg_time)", dto.getRegTimeBeginDate(), dto.getRegTimeEndDate());
        }
        if (dto.getRegPayIntervalTimeMin() != null) {
            //充值到注册的最小间隔时间（分）
            cri.where().andGTE("TIMESTAMPDIFF(MINUTE, reg_time, last_recharge_time)", dto.getRegPayIntervalTimeMin());
        }
        if (dto.getRegPayIntervalTimeMax() != null) {
            //充值到注册的最大间隔时间(分)
            cri.where().andLTE("TIMESTAMPDIFF(MINUTE, reg_time, last_recharge_time)", dto.getRegPayIntervalTimeMax());
        }
        if (StringUtils.isNotBlank(dto.getSourceSystem())) {
            cri.where().andEquals("source_system", dto.getSourceSystem());
        }
        //pager对象
        Pager pager = dao.createPager(dto.getPageNum(), dto.getPageSize());
        //总记录数
        Sql countSql = Sqls.create(getUserRoleDataListCountSql() + 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.isBlank(dto.getSortFiled())) {
            //默认游戏角色创建时间排序
            cri.getOrderBy().orderBy("create_time", dto.getSortType());
        } else {
            cri.getOrderBy().orderBy(dto.getSortFiled(), dto.getSortFiled());
        }
        //查询sql
        Sql sql = Sqls.create(getUserRoleDataListSql() + cri);
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(PlayerRoleDataVO.class));
        sql.setPager(pager);
        //执行sql 得到结果
        dao.execute(sql);
        List<PlayerRoleDataVO> list = sql.getList(PlayerRoleDataVO.class).stream().map(vo -> {
            String gameClassify = GameCategoryEnum.getNameByCategory(vo.getGameCategoryId());
            if (StringUtils.isNotBlank(gameClassify)) {
                vo.setGameCategoryName(gameClassify);
            }
            //“null”字符串处理
            if ("null".equals(vo.getCountry())) {
                vo.setCountry(null);
            }
            return vo;
        }).collect(Collectors.toList());

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

    /**
     * 玩家角色列表总计一栏
     *
     * @param dto PlayerRoleDataListDTO
     * @return PlayerRoleDataVO
     */
    @Override
    public PlayerRoleDataVO getPlayerRoleTotalData(PlayerRoleDataListDTO dto) {
        Tuple2<List<Long>, List<Long>> poerInfo = dataPowerComponent.getPowerInfo();
        List<Long> pitcherIds = StringUtils.isBlank(dto.getPitcherId()) ? poerInfo.first : Collections.singletonList(Long.valueOf(dto.getPitcherId()));
        List<Long> gameIds = dto.getGameId() == null ? poerInfo.second : Collections.singletonList(dto.getGameId());
        //查询条件
        Criteria cri = Cnd.cri();
        if (dto.getUserId() != null) {
            cri.where().andEquals("user_id", dto.getUserId());
        }
        if (StringUtils.isNotBlank(dto.getUserName())) {
            //玩家账号
            cri.where().andEquals("username", dto.getUserName());
        }
        if (StringUtils.isNotBlank(dto.getNickname())) {
            //玩家昵称
            cri.where().andLike("nickname", dto.getNickname());
        }
        if (StringUtils.isNotBlank(dto.getRegIp())) {
            //玩家注册IP
            cri.where().andEquals("reg_ip", dto.getRegIp());
        }
        if (StringUtils.isNotBlank(dto.getServerName())) {
            //游戏区服
            cri.where().andLike("server_name", dto.getServerName());
        }
        if (StringUtils.isNotBlank(dto.getRoleName())) {
            cri.where().andLike("role_name", dto.getRoleName());
        }
        if (StringUtils.isNotBlank(dto.getRoleId())) {
            cri.where().andEquals("role_id", dto.getRoleId());
        }
        if (dto.getCpId() != null) {
            cri.where().andEquals("cp_id", dto.getCpId());
        }
        if (dto.getGameCategoryId() != null) {
            //游戏应用类型
            cri.where().andEquals("game_category_id", dto.getGameCategoryId());
        }
        if (StringUtils.isNotBlank(dto.getAccountId())) {
            //推广账号ID
            cri.where().andEquals("account_id", dto.getAccountId());
        }
        if (CollectionUtils.isNotEmpty(dto.getChannelId())) {
            //渠道ID
            cri.where().andIn("agent_id", dto.getChannelId().toArray(String[]::new));
        }
        if (pitcherIds != null) {
            cri.where().andInList("pitcher_id", pitcherIds);
        }
        if (gameIds != null) {
            cri.where().andInList("game_id", gameIds);
        }
        if (StringUtils.isNotBlank(dto.getOs())) {
            cri.where().andEquals("os", dto.getOs());
        }
        //判断是否充值
        if (BooleanUtils.isFalse(dto.getIsRecharge())) {
            //未充值 充值金额为0
            cri.where().andEquals("recharge_money", 0);
        }
        if (BooleanUtils.isTrue(dto.getIsRecharge())) {
            //充值金额大于0
            cri.where().andNotEquals("recharge_money", 0);
        }
        if (dto.getVipLevel() != null) {
            cri.where().andEquals("role_vip_level", dto.getVipLevel());
        }
        if (dto.getRoleLevelMin() != null) {
            //玩家最小等级
            cri.where().andGTE("role_level", dto.getRoleLevelMin());
        }
        if (dto.getRoleLevelMax() != null) {
            //玩家最大等级
            cri.where().andLTE("role_level", dto.getRoleLevelMax());
        }
        if (dto.getBeginDate() != null && dto.getEndDate() != null) {
            //角色创建时间
            cri.where().andBetween("DATE(create_time)", dto.getBeginDate(), dto.getEndDate());
        }
        if (dto.getRechargeBeginDate() != null && dto.getRechargeEndDate() != null) {
            //最近充值时间
            cri.where().andBetween("DATE(last_recharge_time)", dto.getRechargeBeginDate(), dto.getRechargeEndDate());
        }
        if (dto.getRegTimeBeginDate() != null && dto.getRegTimeEndDate() != null) {
            //玩家注册时间
            cri.where().andBetween("DATE(reg_time)", dto.getRegTimeBeginDate(), dto.getRegTimeEndDate());
        }
        if (dto.getRegPayIntervalTimeMin() != null) {
            //充值到注册的最小间隔时间（分）
            cri.where().andGTE("TIMESTAMPDIFF(MINUTE, reg_time, last_recharge_time)", dto.getRegPayIntervalTimeMin());
        }
        if (dto.getRegPayIntervalTimeMax() != null) {
            //充值到注册的最大间隔时间(分)
            cri.where().andLTE("TIMESTAMPDIFF(MINUTE, reg_time, last_recharge_time)", dto.getRegPayIntervalTimeMax());
        }
        if (StringUtils.isNotBlank(dto.getSourceSystem())) {
            cri.where().andEquals("source_system", dto.getSourceSystem());
        }
        //查询sql
        String userRoleDataTotalSql2 = getUserRoleDataTotalSql2(cri);
        Sql sql = Sqls.create(userRoleDataTotalSql2);
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(PlayerRoleDataVO.class));
        //执行sql 得到结果
        dao.execute(sql);

        return sql.getObject(PlayerRoleDataVO.class);
    }

    /**
     * 玩家登陆日志列表
     *
     * @param dto PlayerLoginLogDataListDTO
     * @return Page<Map>
     */
    @Override
    public Page<Map> getPlayerLoginLogDataList(PlayerLoginLogDataListDTO dto) {
        Tuple2<List<Long>, List<Long>> poerInfo = dataPowerComponent.getPowerInfo();
        List<Long> gameIds = dto.getGameId() == null ? poerInfo.second : Collections.singletonList(dto.getGameId());
        //查询条件
        Criteria cri = Cnd.cri();
        if (StringUtils.isNotBlank(dto.getSourceSystem())) {
            cri.where().andEquals("source_system", dto.getSourceSystem());
        }
        if (dto.getBeginDate() != null && dto.getEndDate() != null) {
            //日期查询条件
            cri.where().andBetween("DATE(dt)", dto.getBeginDate(), dto.getEndDate());
        }
        if (StringUtils.isNotBlank(dto.getUsername())) {
            cri.where().andEquals("username", dto.getUsername());
        }
        if (dto.getUserId() != null) {
            cri.where().andEquals("user_id", dto.getUserId());
        }
        if (gameIds != null) {
            cri.where().andInList("game_id", gameIds);
        }
        if (dto.getGameClassify() != null) {
            cri.where().andEquals("classify", dto.getGameClassify());
        }
        if (dto.getParentGameId() != null) {
            cri.where().andEquals("parent_game_id", dto.getParentGameId());
        }
        if (dto.getRoleId() != null) {
            cri.where().andEquals("role_id", dto.getRoleId());
        }
        if (StringUtils.isNotBlank(dto.getRoleName())) {
            cri.where().andEquals("role_name", dto.getRoleName());
        }
        if (CollectionUtils.isNotEmpty(dto.getServerId())) {
            cri.where().andIn("server_id", dto.getServerId().toArray(String[]::new));
        }
        if (StringUtils.isNotBlank(dto.getOs())) {
            cri.where().andEquals("os", dto.getOs());
        }
        if (dto.getDeviceType() != null) {
            cri.where().andEquals("device_type", dto.getDeviceType());
        }
        if (dto.getType() != null) {
            cri.where().andEquals("log_type", dto.getType());
        }
        if (StringUtils.isNotBlank(dto.getIp())) {
            cri.where().andEquals("ip", dto.getIp());
        }
        //分页
        Pager pager = dao.createPager(dto.getPageNum(), dto.getPageSize());
        //获取记录数
        Sql countSql = Sqls.create(getPlayerLoginLogDataCountSql() + cri);
        countSql.setCallback(Sqls.callback.integer());
        dao.execute(countSql);
        pager.setRecordCount(countSql.getInt());

        //添加默认排序
        cri.getOrderBy().orderBy("dt", OrderByEnum.DESC.getOrderType());
        //创建SQL语句
        Sql sql = Sqls.create(getPlayerLoginLogDataSql() + cri);
        sql.setCallback(Sqls.callback.maps());
        sql.setPager(pager);
        //获取结果
        dao.execute(sql);
        List<Map> list = sql.getList(Map.class).stream().map(map -> {
            //获取游戏类型
            String gameClassify = GameCategoryEnum.getNameByCategory(Long.valueOf((Integer) map.get("classify")));
            if (StringUtils.isNotBlank(gameClassify)) {
                map.put("classify", gameClassify);
            }
            //获取客户端类型
            String deviceType = DeviceTypeEnum.getName((Integer) map.get("device_type"));
            if (StringUtils.isNotBlank(deviceType)) {
                map.put("device_type", deviceType);
            }
            //获取登陆类型
            String logTypeName = LogTypeEnum.getLogTypeName((Integer) map.get("log_type"));
            if (StringUtils.isNotBlank(logTypeName)) {
                map.put("log_type", logTypeName);
            }

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

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

    /**
     * 登陆IP玩家列表
     *
     * @param dto PlayerBannedListDTO
     * @return Page<Map>
     */
    @Override
    public Page<Map> getPlayerBannedDataList(PlayerBannedListDTO dto) {
        Criteria cri = Cnd.cri();
        if (StringUtils.isBlank(dto.getIp())) {
            return null;
        }
        cri.where().andEquals("ip", dto.getIp());
        cri.where().andEquals("source_system", "ZX_ONE");
        //查询出所有玩家的ID
        Sql findUserIdSql = Sqls.create("""
                SELECT
                	DISTINCT user_id
                FROM dm_game_order.t_user_login_log
                """ + cri);
        findUserIdSql.setCallback(Sqls.callback.longs());
        dao.execute(findUserIdSql);
        //执行结果
        Collection userIds = findUserIdSql.getObject(Collection.class);
        //分页
        Pager pager = dao.createPager(dto.getPageNum(), dto.getPageSize());
        //查询出用户列表
        Criteria userMapCri = Cnd.cri();
        userMapCri.where().andEquals("source_system", "ZX_ONE");
        userMapCri.where().andInList("user_id", userIds.stream().toList());

        //查询记录数
        Sql userMapCountSql = Sqls.create(getUserMapCountSql() + userMapCri);
        userMapCountSql.setCallback(Sqls.callback.integer());
        dao.execute(userMapCountSql);
        pager.setRecordCount(userMapCountSql.getInt());

        //查询结果
        Sql userMapSql = Sqls.create(getUserMapSql() + userMapCri);
        userMapSql.setCallback(Sqls.callback.maps());
        userMapSql.setPager(pager);
        dao.execute(userMapSql);
        List<Map> list = userMapSql.getList(Map.class);

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


    /**
     * 查询玩家列表数据SQL
     *
     * @return String
     */
    private String getPlayerDataListSql() {
        return """
                SELECT
                	*
                FROM (
                    select
                        a.source_system as source_system ,
                        a.id as id ,
                        a.share_user_id as share_user_id,
                        a.username as username ,
                        a.nickname as nickname ,
                        a.ip as reg_ip,
                        a.create_time as create_time ,
                        a.agent_id as agent_id ,
                        if(a.agent_id = 0, '自然量', c.agent_name) as agent_name ,
                        f.id as cp_id ,
                        f.cp_name as cp_name ,
                        a.game_id as game_id ,
                        e.game_name as game_name ,
                        e.classify as game_category_id ,
                        IFNULL(d.zx_pitcher_name, '自然量') as pitcher_name ,
                        IFNULL(d.zx_pitcher_id, 0) as pitcher_id ,
                        c.account_id as account_id,
                        c.account_type as account_type,
                        a.mobile as mobile ,
                        IFNULL(i.amount, 0) as recharge_money ,
                        IFNULL(i.amount_count, 0) as recharge_count,
                        IFNULL(b.role_num, 0) as role_count,
                        j.create_time as update_time ,
                        b.last_recharge_time as last_recharge_time,
                        a.status as status ,
                        b.role_name as last_game_role_name ,
                        h.id as relation_game_id ,
                        h.game_name as relation_game_name ,
                        a.relation_user_id as relation_user_id ,
                        a.relation_create_time as relation_create_time ,
                        a.`authentication` as auth,
                        TIMESTAMPDIFF(SECOND , a.create_time, b.last_recharge_time) as reg_pay_time_diff,
                        k.create_time as user_last_reg_time,
                        k.agent_id as user_last_reg_agent_id,
                        IF(k.agent_id = 0, '自然量', l.agent_name) as user_last_agent_name ,
                        l.put_user_id as user_last_pitcher_id,
                        IFNULL(m.zx_pitcher_name, '自然量') as user_last_pitcher_name
                    FROM dm_game_order.t_game_user a
                    left join (
                        select
                            a.source_system ,
                            a.user_id ,
                            a.role_name ,
                            a.create_time ,
                            b.last_recharge_time ,
                            a.num,
                            c.role_num
                            from (
                                select
                                    source_system ,
                                    user_id ,
                                    role_name ,
                                    create_time,
                                    update_time ,
                                    ROW_NUMBER() OVER(PARTITION BY user_id,source_system order by update_time  desc) as num
                                from dm_game_order.t_game_user_role
                                ) a
                            left join (
                                select
                                        source_system as b_source_system,
                                        user_id as b_user_id,
                                        role_name as role_name,
                                        last_recharge_time ,
                                        ROW_NUMBER() OVER(PARTITION BY user_id,source_system order by last_recharge_time desc) as b_num
                                from dm_game_order.t_game_user_role
                                ) b on a.user_id = b.b_user_id and a.source_system = b.b_source_system and a.num= b.b_num
                            left join (
                                select
                                        source_system as c_source_system,
                                        user_id as c_user_id,
                                        count(user_id) as role_num
                                from dm_game_order.t_game_user_role
                                group by user_id , source_system
                            ) c on a.user_id = c.c_user_id and a.source_system = c.c_source_system
                            WHERE a.num = 1
                    )b on a.id =b.user_id and a.source_system = b.source_system
                    left join dm_game_order.t_pitcher_agent c on a.agent_id =c.id and a.source_system = c.source_system
                    left join dm_game_order.t_pitcher_map d on c.pitcher_id =d.zx_pitcher_id and c.source_system = d.source_system
                    left join dm_game_order.t_game e on a.game_id =e.id  and a.source_system = e.source_system
                    left join dm_game_order.t_cp f on e.cp_id  =f.id and e.source_system = f.source_system
                    left join dm_game_order.t_game_user g on a.relation_user_id = g.id and a.source_system = g.source_system
                    left join dm_game_order.t_game h on g.game_id = h.id and g.source_system = h.source_system
                    LEFT JOIN (
                        select
                            user_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 user_id , source_system
                    ) i on a.id = i.user_id and a.source_system = i.source_system
                    LEFT JOIN (
                    	SELECT
                    		source_system ,
                    		user_id ,
                    		create_time ,
                    		ROW_NUMBER()over(partition by user_id , source_system order by create_time desc) as num
                    	FROM dm_game_order.t_user_login_log
                    ) j ON a.id = j.user_id AND a.source_system = j.source_system AND j.num = 1
                    LEFT JOIN (
                    	select
                    		id,
                    		source_system,
                    		create_time,
                    		agent_id,
                    		ROW_NUMBER() OVER(PARTITION BY id,source_system order by create_time desc) as num
                    	FROM dm_game_order.t_game_user_burst
                    ) k on a.id = k.id AND a.source_system = k.source_system AND k.num = 1
                    LEFT JOIN (
                        SELECT
                            -- 渠道名称、投手id
                            source_system,
                            id,
                            agent_name,
                            pitcher_id as put_user_id
                        FROM dm_game_order.t_pitcher_agent
                    ) l on k.source_system = l.source_system AND k.agent_id = l.id
                    left join dm_game_order.t_pitcher_map m on l.put_user_id = m.zx_pitcher_id and l.source_system = m.source_system
                ) a
                """;
    }

    /**
     * 查询记录的条数SQL
     *
     * @return String
     */
    private String getPlayerDataListCountSql() {
        return """
                SELECT
                	COUNT(1)
                FROM (
                    select
                        a.source_system as source_system ,
                        a.id as id ,
                        a.username as username ,
                        a.nickname as nickname ,
                        a.ip as reg_ip,
                        a.create_time as create_time ,
                        a.agent_id as agent_id ,
                        if(a.agent_id = 0, '自然量', c.agent_name) as agent_name ,
                        f.id as cp_id ,
                        f.cp_name as cp_name ,
                        a.game_id as game_id ,
                        e.game_name as game_name ,
                        e.classify as game_category_id ,
                        IFNULL(d.zx_pitcher_name, '自然量') as pitcher_name ,
                        IFNULL(d.zx_pitcher_id, 0) as pitcher_id ,
                        c.account_id as account_id,
                        c.account_type as account_type,
                        a.mobile as mobile ,
                        IFNULL(i.amount, 0) as recharge_money ,
                        IFNULL(i.amount_count, 0) as recharge_count,
                        IFNULL(b.role_num, 0) as role_count,
                        j.create_time as update_time ,
                        b.last_recharge_time as last_recharge_time,
                        a.status as status ,
                        b.role_name as last_game_role_name ,
                        h.id as relation_game_id ,
                        h.game_name as relation_game_name ,
                        a.relation_user_id as relation_user_id ,
                        a.relation_create_time as relation_create_time ,
                        a.`authentication` as auth,
                        TIMESTAMPDIFF(SECOND , a.create_time, b.last_recharge_time) as reg_pay_time_diff,
                        k.create_time as user_last_reg_time,
                        k.agent_id as user_last_reg_agent_id,
                        IF(k.agent_id = 0, '自然量', l.agent_name) as user_last_agent_name ,
                        l.put_user_id as user_last_pitcher_id,
                        IFNULL(m.zx_pitcher_name, '自然量') as user_last_pitcher_name
                    FROM dm_game_order.t_game_user a
                    left join (
                        select
                            a.source_system ,
                            a.user_id ,
                            a.role_name ,
                            a.create_time ,
                            b.last_recharge_time ,
                            a.num,
                            c.role_num
                            from (
                                select
                                    source_system ,
                                    user_id ,
                                    role_name ,
                                    create_time,
                                    update_time ,
                                    ROW_NUMBER() OVER(PARTITION BY user_id,source_system order by update_time  desc) as num
                                from dm_game_order.t_game_user_role
                                ) a
                            left join (
                                select
                                        source_system as b_source_system,
                                        user_id as b_user_id,
                                        role_name as role_name,
                                        last_recharge_time ,
                                        ROW_NUMBER() OVER(PARTITION BY user_id,source_system order by last_recharge_time desc) as b_num
                                from dm_game_order.t_game_user_role
                                ) b on a.user_id = b.b_user_id and a.source_system = b.b_source_system and a.num= b.b_num
                            left join (
                                select
                                        source_system as c_source_system,
                                        user_id as c_user_id,
                                        count(user_id) as role_num
                                from dm_game_order.t_game_user_role
                                group by user_id , source_system
                            ) c on a.user_id = c.c_user_id and a.source_system = c.c_source_system
                            WHERE a.num = 1
                    )b on a.id =b.user_id and a.source_system = b.source_system
                    left join dm_game_order.t_pitcher_agent c on a.agent_id =c.id and a.source_system = c.source_system
                    left join dm_game_order.t_pitcher_map d on c.pitcher_id =d.zx_pitcher_id and c.source_system = d.source_system
                    left join dm_game_order.t_game e on a.game_id =e.id  and a.source_system = e.source_system
                    left join dm_game_order.t_cp f on e.cp_id  =f.id and e.source_system = f.source_system
                    left join dm_game_order.t_game_user g on a.relation_user_id = g.id and a.source_system = g.source_system
                    left join dm_game_order.t_game h on g.game_id = h.id and g.source_system = h.source_system
                    LEFT JOIN (
                        select
                            user_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 user_id , source_system
                    ) i on a.id = i.user_id and a.source_system = i.source_system
                    LEFT JOIN (
                    	SELECT
                    		source_system ,
                    		user_id ,
                    		create_time ,
                    		ROW_NUMBER()over(partition by user_id , source_system order by create_time desc) as num
                    	FROM dm_game_order.t_user_login_log
                    ) j ON a.id = j.user_id AND a.source_system = j.source_system AND j.num = 1
                    LEFT JOIN (
                    	select
                    		id,
                    		source_system,
                    		create_time,
                    		agent_id,
                    		ROW_NUMBER() OVER(PARTITION BY id,source_system order by create_time desc) as num
                    	FROM dm_game_order.t_game_user_burst
                    ) k on a.id = k.id AND a.source_system = k.source_system AND k.num = 1
                    LEFT JOIN (
                        SELECT
                            -- 渠道名称、投手id
                            source_system,
                            id,
                            agent_name,
                            pitcher_id as put_user_id
                        FROM dm_game_order.t_pitcher_agent
                    ) l on k.source_system = l.source_system AND k.agent_id = l.id
                    left join dm_game_order.t_pitcher_map m on l.put_user_id = m.zx_pitcher_id and l.source_system = m.source_system
                ) a
                """;
    }

    /**
     * 玩家角色列表查询sql
     *
     * @return String
     */
    private String getUserRoleDataListSql() {
        return """
                SELECT
                	*
                FROM (
                	SELECT
                		a.id, -- 主键id
                		a.source_system , -- SDK来源
                		a.user_id , -- 玩家ID
                		b.username , -- 玩家账号
                		b.nickname , -- 玩家昵称
                		b.ip as reg_ip, -- 玩家注册IP
                		b.create_time as reg_time, -- 玩家注册时间
                		b.agent_id , -- 注册渠道ID
                		IF(b.agent_id = 0, '自然量', c.agent_name) as agent_name , -- 注册渠道名
                		IFNULL(c.pitcher_id, 0) as pitcher_id , -- 投手ID
                  		IFNULL(d.zx_pitcher_name, '自然量') as pitcher_name, -- 投手名
                		c.account_id , -- 推广账号ID
                		c.account_name , -- 推广账号名
                		c.account_type , -- 推广账号类型
                		a.game_id , -- 角色注册游戏ID
                		e.game_name , -- 游戏名
                		e.classify as game_category_id, -- 游戏应用类型
                		e.cp_id , -- cpID
                		f.cp_name , -- cp名字
                		h.server_id , -- 角色注册区服ID
                		h.server_name , -- 区服名
                		h.origin_server_id , -- 原始区服ID
                		h.origin_server_name as source_server_name, -- 原始区服名
                		a.role_id , -- 角色ID
                		a.role_name , -- 角色名
                		a.role_level , -- 角色等级
                		a.country, -- 国家属性
                		a.combat_num as role_power, -- 角色攻击力
                		a.role_vip as role_vip_level, -- 角色vip
                		a.os , -- 角色操作系统
                		a.create_time as create_time, -- 角色创建时间
                		k.active_time as update_time, -- 角色最近更新时间
                		g.pay_time as last_recharge_time, -- 角色最近充值时间
                		TIMESTAMPDIFF(SECOND, b.create_time, g.pay_time) as reg_pay_time_diff, -- 最近充值距注册时间差（秒）
                		i.active_time as last_login_time, -- 玩家最近登陆时间
                		j.amount as recharge_money, -- 角色充值金额
                		j.amount_count as recharge_count -- 角色充值次数
                	FROM dm_game_order.t_game_user_role a
                	LEFT JOIN dm_game_order.t_game_user_burst b
                	on a.source_system = b.source_system AND a.user_id = b.id AND a.create_time >= b.create_time AND a.create_time < b.end_time
                	LEFT JOIN dm_game_order.t_pitcher_agent c on b.source_system = c.source_system AND b.agent_id = c.id
                	LEFT JOIN dm_game_order.t_pitcher_map d on c.source_system = d.source_system AND c.pitcher_id = d.zx_pitcher_id
                	LEFT JOIN dm_game_order.t_game e on a.source_system = e.source_system AND a.game_id = e.id
                	LEFT JOIN dm_game_order.t_cp f on e.source_system = f.source_system AND e.cp_id = f.id
                	LEFT JOIN (
                		SELECT
                			-- 角色最近充值时间、角色最近充值金额
                			source_system,
                			role_id,
                			user_id,
                			real_amount,
                			pay_time,
                			ROW_NUMBER()over(partition by role_id , user_id , source_system order by pay_time desc) as num
                		FROM dm_game_order.t_game_order
                		WHERE status =2
                	) g on a.source_system = g.source_system AND a.role_id = g.role_id AND a.user_id = g.user_id AND g.num = 1
                	LEFT JOIN (
                		SELECT
                			a.source_system,
                			a.game_id, -- 子游戏ID
                			a.server_id, -- 区服ID
                			a.server_name, -- 区服名（冠名后）
                			ifnull(b.server_id,a.server_id) as origin_server_id, -- 原始区服ID
                			ifnull(b.server_name,a.server_name) as origin_server_name -- 原始区服名
                		from (
                			SELECT
                				a.game_id ,
                				a.server_id ,
                				a.server_name ,
                				b.super_game_id,
                				a.source_system
                			FROM dm_game_order.t_game_server a
                			LEFT JOIN dm_game_order.t_game b on a.source_system = b.source_system AND a.game_id = b.id
                		)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.super_game_id = b.game_id AND a.server_id = b.server_id
                	) h on a.source_system = h.source_system AND a.server_id = h.server_id AND a.game_id = h.game_id
                	LEFT JOIN (
                		SELECT
                			source_system ,
                			user_id,
                			game_id,
                			create_time as active_time,
                			role_id ,
                			ROW_NUMBER()over(partition by user_id, source_system order by create_time desc) as num
                		FROM dm_game_order.t_user_login_log
                	) i on a.source_system = i.source_system AND a.user_id = i.user_id AND i.num = 1
                	LEFT JOIN (
                		SELECT
                			-- 累计充值金额、累计充值次数
                	    	role_id,
                	    	user_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 , user_id , source_system
                	) j on a.source_system = j.source_system AND a.role_id = j.role_id AND a.user_id = j.user_id
                	LEFT JOIN (
                		SELECT
                			source_system ,
                			user_id,
                			game_id,
                			active_time,
                			role_id ,
                			ROW_NUMBER()over(partition by role_id ,user_id, source_system order by active_time desc) as num
                		FROM game_dw.dw_active_log
                	) k on a.source_system = k.source_system AND a.user_id = k.user_id AND a.role_id = k.role_id AND k.num = 1
                ) a
                """;
    }

    /**
     * 玩家角色列表查询sql(总记录数)
     *
     * @return String
     */
    private String getUserRoleDataListCountSql() {
        return """
                SELECT
                	COUNT(1)
                FROM (
                	SELECT
                		a.id, -- 主键id
                		a.source_system , -- SDK来源
                		a.user_id , -- 玩家ID
                		b.username , -- 玩家账号
                		b.nickname , -- 玩家昵称
                		b.ip as reg_ip, -- 玩家注册IP
                		b.create_time as reg_time, -- 玩家注册时间
                		b.agent_id , -- 注册渠道ID
                		IF(b.agent_id = 0, '自然量', c.agent_name) as agent_name , -- 注册渠道名
                		IFNULL(c.pitcher_id, 0) as pitcher_id , -- 投手ID
                  		IFNULL(d.zx_pitcher_name, '自然量') as pitcher_name, -- 投手名
                		c.account_id , -- 推广账号ID
                		c.account_name , -- 推广账号名
                		c.account_type , -- 推广账号类型
                		a.game_id , -- 角色注册游戏ID
                		e.game_name , -- 游戏名
                		e.classify as game_category_id, -- 游戏应用类型
                		e.cp_id , -- cpID
                		f.cp_name , -- cp名字
                		h.server_id , -- 角色注册区服ID
                		h.server_name , -- 区服名
                		h.origin_server_id , -- 原始区服ID
                		h.origin_server_name as source_server_name, -- 原始区服名
                		a.role_id , -- 角色ID
                		a.role_name , -- 角色名
                		a.role_level , -- 角色等级
                		a.country, -- 国家属性
                		a.combat_num as role_power, -- 角色攻击力
                		a.role_vip as role_vip_level, -- 角色vip
                		a.os , -- 角色操作系统
                		a.create_time as create_time, -- 角色创建时间
                		k.active_time as update_time, -- 角色最近更新时间
                		g.pay_time as last_recharge_time, -- 角色最近充值时间
                		TIMESTAMPDIFF(SECOND, b.create_time, g.pay_time) as reg_pay_time_diff, -- 最近充值距注册时间差（秒）
                		i.active_time as last_login_time, -- 玩家最近登陆时间
                		j.amount as recharge_money, -- 角色充值金额
                		j.amount_count as recharge_count -- 角色充值次数
                	FROM dm_game_order.t_game_user_role a
                	LEFT JOIN dm_game_order.t_game_user_burst b
                	on a.source_system = b.source_system AND a.user_id = b.id AND a.create_time >= b.create_time AND a.create_time < b.end_time
                	LEFT JOIN dm_game_order.t_pitcher_agent c on b.source_system = c.source_system AND b.agent_id = c.id
                	LEFT JOIN dm_game_order.t_pitcher_map d on c.source_system = d.source_system AND c.pitcher_id = d.zx_pitcher_id
                	LEFT JOIN dm_game_order.t_game e on a.source_system = e.source_system AND a.game_id = e.id
                	LEFT JOIN dm_game_order.t_cp f on e.source_system = f.source_system AND e.cp_id = f.id
                	LEFT JOIN (
                		SELECT
                			-- 角色最近充值时间、角色最近充值金额
                			source_system,
                			role_id,
                			user_id,
                			real_amount,
                			pay_time,
                			ROW_NUMBER()over(partition by role_id , user_id , source_system order by pay_time desc) as num
                		FROM dm_game_order.t_game_order
                		WHERE status =2
                	) g on a.source_system = g.source_system AND a.role_id = g.role_id AND a.user_id = g.user_id AND g.num = 1
                	LEFT JOIN (
                		SELECT
                			a.source_system,
                			a.game_id, -- 子游戏ID
                			a.server_id, -- 区服ID
                			a.server_name, -- 区服名（冠名后）
                			ifnull(b.server_id,a.server_id) as origin_server_id, -- 原始区服ID
                			ifnull(b.server_name,a.server_name) as origin_server_name -- 原始区服名
                		from (
                			SELECT
                				a.game_id ,
                				a.server_id ,
                				a.server_name ,
                				b.super_game_id,
                				a.source_system
                			FROM dm_game_order.t_game_server a
                			LEFT JOIN dm_game_order.t_game b on a.source_system = b.source_system AND a.game_id = b.id
                		)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.super_game_id = b.game_id AND a.server_id = b.server_id
                	) h on a.source_system = h.source_system AND a.server_id = h.server_id AND a.game_id = h.game_id
                	LEFT JOIN (
                		SELECT
                			source_system ,
                			user_id,
                			game_id,
                			create_time as active_time,
                			role_id ,
                			ROW_NUMBER()over(partition by user_id, source_system order by create_time desc) as num
                		FROM dm_game_order.t_user_login_log
                	) i on a.source_system = i.source_system AND a.user_id = i.user_id AND i.num = 1
                	LEFT JOIN (
                		SELECT
                			-- 累计充值金额、累计充值次数
                	    	role_id,
                	    	user_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 , user_id , source_system
                	) j on a.source_system = j.source_system AND a.role_id = j.role_id AND a.user_id = j.user_id
                	LEFT JOIN (
                		SELECT
                			source_system ,
                			user_id,
                			game_id,
                			active_time,
                			role_id ,
                			ROW_NUMBER()over(partition by role_id ,user_id, source_system order by active_time desc) as num
                		FROM game_dw.dw_active_log
                	) k on a.source_system = k.source_system AND a.user_id = k.user_id AND a.role_id = k.role_id AND k.num = 1
                ) a
                """;
    }

    /**
     * 玩家角色列表总计一栏sql
     *
     * @return String
     */
    private String getUserRoleDataTotalSql() {
        return """
                SELECT
                	SUM(recharge_money) as recharge_money,
                 	SUM(recharge_count) as recharge_count
                FROM (
                	SELECT
                		a.id, -- 主键id
                		a.source_system , -- SDK来源
                		a.user_id , -- 玩家ID
                		b.username , -- 玩家账号
                		b.nickname , -- 玩家昵称
                		b.ip as reg_ip, -- 玩家注册IP
                		b.create_time as reg_time, -- 玩家注册时间
                		b.agent_id , -- 注册渠道ID
                		IF(b.agent_id = 0, '自然量', c.agent_name) as agent_name , -- 注册渠道名
                		IFNULL(c.pitcher_id, 0) as pitcher_id , -- 投手ID
                  		IFNULL(d.zx_pitcher_name, '自然量') as pitcher_name, -- 投手名
                		c.account_id , -- 推广账号ID
                		c.account_name , -- 推广账号名
                		c.account_type , -- 推广账号类型
                		a.game_id , -- 角色注册游戏ID
                		e.game_name , -- 游戏名
                		e.classify as game_category_id, -- 游戏应用类型
                		e.cp_id , -- cpID
                		f.cp_name , -- cp名字
                		h.server_id , -- 角色注册区服ID
                		h.server_name , -- 区服名
                		h.origin_server_id , -- 原始区服ID
                		h.origin_server_name as source_server_name, -- 原始区服名
                		a.role_id , -- 角色ID
                		a.role_name , -- 角色名
                		a.role_level , -- 角色等级
                		a.country, -- 国家属性
                		a.combat_num as role_power, -- 角色攻击力
                		a.role_vip as role_vip_level, -- 角色vip
                		a.os , -- 角色操作系统
                		a.create_time as create_time, -- 角色创建时间
                		k.active_time as update_time, -- 角色最近更新时间
                		g.pay_time as last_recharge_time, -- 角色最近充值时间
                		TIMESTAMPDIFF(SECOND, b.create_time, g.pay_time) as reg_pay_time_diff, -- 最近充值距注册时间差（秒）
                		i.active_time as last_login_time, -- 玩家最近登陆时间
                		j.amount as recharge_money, -- 角色充值金额
                		j.amount_count as recharge_count -- 角色充值次数
                	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_pitcher_agent c on b.source_system = c.source_system AND b.agent_id = c.id
                	LEFT JOIN dm_game_order.t_pitcher_map d on c.source_system = d.source_system AND c.pitcher_id = d.zx_pitcher_id
                	LEFT JOIN dm_game_order.t_game e on a.source_system = e.source_system AND a.game_id = e.id
                	LEFT JOIN dm_game_order.t_cp f on e.source_system = f.source_system AND e.cp_id = f.id
                	LEFT JOIN (
                		SELECT
                			-- 角色最近充值时间、角色最近充值金额
                			source_system,
                			role_id,
                			user_id,
                			real_amount,
                			pay_time,
                			ROW_NUMBER()over(partition by role_id , user_id , source_system order by pay_time desc) as num
                		FROM dm_game_order.t_game_order
                		WHERE status =2
                	) g on a.source_system = g.source_system AND a.role_id = g.role_id AND a.user_id = g.user_id AND g.num = 1
                	LEFT JOIN (
                		SELECT
                			a.source_system,
                			a.game_id, -- 子游戏ID
                			a.server_id, -- 区服ID
                			a.server_name, -- 区服名（冠名后）
                			ifnull(b.server_id,a.server_id) as origin_server_id, -- 原始区服ID
                			ifnull(b.server_name,a.server_name) as origin_server_name -- 原始区服名
                		from (
                			SELECT
                				a.game_id ,
                				a.server_id ,
                				a.server_name ,
                				b.super_game_id,
                				a.source_system
                			FROM dm_game_order.t_game_server a
                			LEFT JOIN dm_game_order.t_game b on a.source_system = b.source_system AND a.game_id = b.id
                		)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.super_game_id = b.game_id AND a.server_id = b.server_id
                	) h on a.source_system = h.source_system AND a.server_id = h.server_id AND a.game_id = h.game_id
                	LEFT JOIN (
                		SELECT
                			source_system ,
                			user_id,
                			game_id,
                			create_time as active_time,
                			role_id ,
                			ROW_NUMBER()over(partition by user_id, source_system order by create_time desc) as num
                		FROM dm_game_order.t_user_login_log
                	) i on a.source_system = i.source_system AND a.user_id = i.user_id AND i.num = 1
                	LEFT JOIN (
                		SELECT
                			-- 累计充值金额、累计充值次数
                	    	role_id,
                	    	user_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 , user_id , source_system
                	) j on a.source_system = j.source_system AND a.role_id = j.role_id AND a.user_id = j.user_id
                	LEFT JOIN (
                		SELECT
                			source_system ,
                			user_id,
                			game_id,
                			active_time,
                			role_id ,
                			ROW_NUMBER()over(partition by role_id ,user_id, source_system order by active_time desc) as num
                		FROM game_dw.dw_active_log
                	) k on a.source_system = k.source_system AND a.user_id = k.user_id AND a.role_id = k.role_id AND k.num = 1
                ) a
                """;
    }


    /**
     * 玩家角色列表总计一栏sql
     *
     * @return String
     */
    private String getUserRoleDataTotalSql2(Criteria cri) {
        return """
                  SELECT 
                    SUM(recharge_money) as recharge_money,
                 	SUM(recharge_count) as recharge_count
                 	FROM(
                  SELECT
                	*
                FROM (
                	SELECT
                		a.id, -- 主键id
                		a.source_system , -- SDK来源
                		a.user_id , -- 玩家ID
                		b.username , -- 玩家账号
                		b.nickname , -- 玩家昵称
                		b.ip as reg_ip, -- 玩家注册IP
                		b.create_time as reg_time, -- 玩家注册时间
                		b.agent_id , -- 注册渠道ID
                		IF(b.agent_id = 0, '自然量', c.agent_name) as agent_name , -- 注册渠道名
                		IFNULL(c.pitcher_id, 0) as pitcher_id , -- 投手ID
                  		IFNULL(d.zx_pitcher_name, '自然量') as pitcher_name, -- 投手名
                		c.account_id , -- 推广账号ID
                		c.account_name , -- 推广账号名
                		c.account_type , -- 推广账号类型
                		a.game_id , -- 角色注册游戏ID
                		e.game_name , -- 游戏名
                		e.classify as game_category_id, -- 游戏应用类型
                		e.cp_id , -- cpID
                		f.cp_name , -- cp名字
                		h.server_id , -- 角色注册区服ID
                		h.server_name , -- 区服名
                		h.origin_server_id , -- 原始区服ID
                		h.origin_server_name as source_server_name, -- 原始区服名
                		a.role_id , -- 角色ID
                		a.role_name , -- 角色名
                		a.role_level , -- 角色等级
                		a.country, -- 国家属性
                		a.combat_num as role_power, -- 角色攻击力
                		a.role_vip as role_vip_level, -- 角色vip
                		a.os , -- 角色操作系统
                		a.create_time as create_time, -- 角色创建时间
                		k.active_time as update_time, -- 角色最近更新时间
                		g.pay_time as last_recharge_time, -- 角色最近充值时间
                		TIMESTAMPDIFF(SECOND, b.create_time, g.pay_time) as reg_pay_time_diff, -- 最近充值距注册时间差（秒）
                		i.active_time as last_login_time, -- 玩家最近登陆时间
                		j.amount as recharge_money, -- 角色充值金额
                		j.amount_count as recharge_count -- 角色充值次数
                	FROM dm_game_order.t_game_user_role a
                	LEFT JOIN dm_game_order.t_game_user_burst b
                	on a.source_system = b.source_system AND a.user_id = b.id AND a.create_time >= b.create_time AND a.create_time < b.end_time
                	LEFT JOIN dm_game_order.t_pitcher_agent c on b.source_system = c.source_system AND b.agent_id = c.id
                	LEFT JOIN dm_game_order.t_pitcher_map d on c.source_system = d.source_system AND c.pitcher_id = d.zx_pitcher_id
                	LEFT JOIN dm_game_order.t_game e on a.source_system = e.source_system AND a.game_id = e.id
                	LEFT JOIN dm_game_order.t_cp f on e.source_system = f.source_system AND e.cp_id = f.id
                	LEFT JOIN (
                		SELECT
                			-- 角色最近充值时间、角色最近充值金额
                			source_system,
                			role_id,
                			user_id,
                			real_amount,
                			pay_time,
                			ROW_NUMBER()over(partition by role_id , user_id , source_system order by pay_time desc) as num
                		FROM dm_game_order.t_game_order
                		WHERE status =2
                	) g on a.source_system = g.source_system AND a.role_id = g.role_id AND a.user_id = g.user_id AND g.num = 1
                	LEFT JOIN (
                		SELECT
                			a.source_system,
                			a.game_id, -- 子游戏ID
                			a.server_id, -- 区服ID
                			a.server_name, -- 区服名（冠名后）
                			ifnull(b.server_id,a.server_id) as origin_server_id, -- 原始区服ID
                			ifnull(b.server_name,a.server_name) as origin_server_name -- 原始区服名
                		from (
                			SELECT
                				a.game_id ,
                				a.server_id ,
                				a.server_name ,
                				b.super_game_id,
                				a.source_system
                			FROM dm_game_order.t_game_server a
                			LEFT JOIN dm_game_order.t_game b on a.source_system = b.source_system AND a.game_id = b.id
                		)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.super_game_id = b.game_id AND a.server_id = b.server_id
                	) h on a.source_system = h.source_system AND a.server_id = h.server_id AND a.game_id = h.game_id
                	LEFT JOIN (
                		SELECT
                			source_system ,
                			user_id,
                			game_id,
                			create_time as active_time,
                			role_id ,
                			ROW_NUMBER()over(partition by user_id, source_system order by create_time desc) as num
                		FROM dm_game_order.t_user_login_log
                	) i on a.source_system = i.source_system AND a.user_id = i.user_id AND i.num = 1
                	LEFT JOIN (
                		SELECT
                			-- 累计充值金额、累计充值次数
                	    	role_id,
                	    	user_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 , user_id , source_system
                	) j on a.source_system = j.source_system AND a.role_id = j.role_id AND a.user_id = j.user_id
                	LEFT JOIN (
                		SELECT
                			source_system ,
                			user_id,
                			game_id,
                			active_time,
                			role_id ,
                			ROW_NUMBER()over(partition by role_id ,user_id, source_system order by active_time desc) as num
                		FROM game_dw.dw_active_log
                	) k on a.source_system = k.source_system AND a.user_id = k.user_id AND a.role_id = k.role_id AND k.num = 1
                ) a   """ + cri + """
                )abc
                """;
    }

    /**
     * 玩家登陆日志查询列表
     *
     * @return String
     */
    private String getPlayerLoginLogDataSql() {
        return """
                SELECT
                	*
                FROM (
                	SELECT
                		a.source_system , -- SDK来源
                		a.id , -- 主键id
                		a.create_time as dt, -- 时间
                		c.username , -- 玩家账号
                		a.user_id , -- 用户id
                		a.game_id , -- 登陆游戏id
                		b.game_name , -- 登陆游戏名
                		b.classify , -- 游戏类型
                		IFNULL(b.parent_id, a.game_id ) as parent_game_id , -- 父游戏id
                		IFNULL(e.game_name, b.game_name) as parent_game_name, -- 父游戏名
                		b.super_game_id , -- 超父游戏id
                		a.role_id , -- 角色id
                		a.role_name , -- 角色名
                		d.server_id , -- 区服id
                		d.server_name , -- 区服名
                		f.server_name as source_server_name, -- 原始区服名
                		a.os , -- 操作系统
                		a.ip , -- 登陆ip
                		a.device_type , -- 客户端类型
                		a.`type` as log_type -- 登陆类型 0-注册；1-登陆；2-退出
                	FROM dm_game_order.t_user_login_log a
                	LEFT JOIN dm_game_order.t_game b on a.source_system = b.source_system AND a.game_id = b.id
                	LEFT JOIN dm_game_order.t_game_user c on a.source_system = c.source_system AND a.user_id = c.id
                	LEFT JOIN dm_game_order.t_game_user_role d on a.source_system = d.source_system AND a.user_id = d.user_id AND a.role_id = d.role_id
                	LEFT JOIN dm_game_order.t_game e on b.source_system = e.source_system AND b.parent_id = e.id
                	LEFT JOIN (
                		SELECT
                			*
                		FROM dm_game_order.t_game_server_merge
                		WHERE is_delete = 0
                	) f on a.source_system = f.source_system AND b.super_game_id = f.game_id AND d.server_id = f.server_id
                ) a
                """;
    }

    /**
     * 玩家登陆日志记录数
     *
     * @return String
     */
    private String getPlayerLoginLogDataCountSql() {
        return """
                SELECT
                    COUNT(1)
                FROM (
                	SELECT
                		a.source_system , -- SDK来源
                		a.id , -- 主键id
                		a.create_time as dt, -- 时间
                		c.username , -- 玩家账号
                		a.user_id , -- 用户id
                		a.game_id , -- 登陆游戏id
                		b.game_name , -- 登陆游戏名
                		b.classify , -- 游戏类型
                		IFNULL(b.parent_id, a.game_id ) as parent_game_id , -- 父游戏id
                		IFNULL(e.game_name, b.game_name) as parent_game_name, -- 父游戏名
                		b.super_game_id , -- 超父游戏id
                		a.role_id , -- 角色id
                		a.role_name , -- 角色名
                		d.server_id , -- 区服id
                		d.server_name , -- 区服名
                		f.server_name as source_server_name, -- 原始区服名
                		a.os , -- 操作系统
                		a.ip , -- 登陆ip
                		a.device_type , -- 客户端类型
                		a.`type` as log_type -- 登陆类型 0-注册；1-登陆；2-退出
                	FROM dm_game_order.t_user_login_log a
                	LEFT JOIN dm_game_order.t_game b on a.source_system = b.source_system AND a.game_id = b.id
                	LEFT JOIN dm_game_order.t_game_user c on a.source_system = c.source_system AND a.user_id = c.id
                	LEFT JOIN dm_game_order.t_game_user_role d on a.source_system = d.source_system AND a.user_id = d.user_id AND a.role_id = d.role_id
                	LEFT JOIN dm_game_order.t_game e on b.source_system = e.source_system AND b.parent_id = e.id
                	LEFT JOIN (
                		SELECT
                			*
                		FROM dm_game_order.t_game_server_merge
                		WHERE is_delete = 0
                	) f on a.source_system = f.source_system AND b.super_game_id = f.game_id AND d.server_id = f.server_id
                ) a
                """;
    }

    /**
     * 获取所有用户sql
     *
     * @return String
     */
    private String getUserMapSql() {
        return """
                SELECT
                    *
                FROM (
                    SELECT
                    	a.source_system ,
                    	a.id as user_id,
                    	a.username ,
                    	a.nickname ,
                    	a.status ,
                    	b.role_id ,
                    	b.role_name
                    FROM dm_game_order.t_game_user a
                    LEFT JOIN (
                    	SELECT
                    		source_system ,
                    		user_id ,
                    		role_id ,
                    		role_name ,
                    		ROW_NUMBER() OVER(PARTITION BY user_id,source_system order by create_time desc) as num
                    	FROM dm_game_order.t_user_login_log
                    	WHERE NOT role_id = 'null'
                    ) b on a.source_system = b.source_system AND a.id = b.user_id AND b.num = 1
                ) a
                """;
    }

    /**
     * 获取所有用户记录数sql
     *
     * @return String
     */
    private String getUserMapCountSql() {
        return """
                SELECT
                    COUNT(1)
                FROM (
                    SELECT
                        *
                    FROM (
                        SELECT
                            a.source_system ,
                            a.id as user_id,
                            a.username ,
                            a.nickname ,
                            a.status ,
                            b.role_id ,
                            b.role_name
                        FROM dm_game_order.t_game_user a
                        LEFT JOIN (
                            SELECT
                                source_system ,
                                user_id ,
                                role_id ,
                                role_name ,
                                ROW_NUMBER() OVER(PARTITION BY user_id,source_system order by create_time desc) as num
                            FROM dm_game_order.t_user_login_log
                            WHERE NOT role_id = 'null'
                        ) b on a.source_system = b.source_system AND a.id = b.user_id AND b.num = 1
                    ) a
                ) a
                """;
    }


}
