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

import com.google.common.base.CaseFormat;
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.component.DataPowerComponent;
import com.zanxiang.game.data.serve.pojo.dto.*;
import com.zanxiang.game.data.serve.pojo.entity.AdsGameServerDay;
import com.zanxiang.game.data.serve.pojo.entity.AdsGameServerDayParent;
import com.zanxiang.game.data.serve.pojo.entity.AdsGameServerSumDay;
import com.zanxiang.game.data.serve.pojo.entity.AdsGameServerSumDayParent;
import com.zanxiang.game.data.serve.pojo.enums.OrderByEnum;
import com.zanxiang.game.data.serve.pojo.vo.*;
import com.zanxiang.game.data.serve.service.IGameServerService;
import com.zanxiang.game.data.serve.utils.Page;
import com.zanxiang.game.module.base.ServerInfo;
import com.zanxiang.game.module.base.pojo.enums.GameAuthEnum;
import com.zanxiang.game.module.base.pojo.vo.GameAuthUserVO;
import com.zanxiang.game.module.base.rpc.GameAuthRpc;
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.nutz.dao.util.cri.Static;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import reactor.util.function.Tuple2;
import reactor.util.function.Tuples;

import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @author tianhua
 * @time 2023/8/14
 * @Description 游戏区服接口逻辑处理
 **/
@Slf4j
@Service
public class GameServerServiceImpl implements IGameServerService {

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

    @DubboReference(providedBy = ServerInfo.SERVER_DUBBO_NAME)
    private GameAuthRpc gameAuthRpc;

    //存储映射的List
    private static final List<Tuple2<Field, Field>> dayNFieldMapList;
    private static final List<Tuple2<Field, Field>> dayNTotalFieldMapList;
    private static final List<Tuple2<Field, Field>> dayNFieldMapListForServerDay;
    private static final List<Tuple2<Field, Field>> dayNTotalFieldMapListForServerDay;


    static {
        //解析游戏区服的dayN映射
        Map<String, Field> fieldMap = new HashMap<>();
        List<Field> dayNFieldList = new ArrayList<>();
        Class<?> tempClazz = GameServerDayVO.class;
        while (tempClazz != null) {
            //得到所有的field
            Field[] fields = tempClazz.getDeclaredFields();
            for (Field field : fields) {
                if (Modifier.isFinal(field.getModifiers()) || Modifier.isStatic(field.getModifiers())) {
                    continue;
                }
                //map里面存储了所有的field
                fieldMap.put(field.getName(), field);
                if (field.getType() == GameServerTrendVO.class) {
                    //list里面只存储了 Trend 相关的field
                    dayNFieldList.add(field);
                }
            }
            tempClazz = tempClazz.getSuperclass();
        }
        if (dayNFieldList.isEmpty()) {
            dayNFieldMapList = Collections.emptyList();
        } else {
            dayNFieldMapList = new ArrayList<>(dayNFieldList.size());
            for (Field dayNField : dayNFieldList) {
                dayNField.setAccessible(true);
                Field sourceField = fieldMap.get(dayNField.getName().replace("Trend", ""));
                sourceField.setAccessible(true);
                if (sourceField != null) {
                    dayNFieldMapList.add(Tuples.of(sourceField, dayNField));
                }
            }
        }

        //游戏区服总计dayN映射
        Map<String, Field> fieldTotalMap = new HashMap<>();
        List<Field> dayNTotalFieldList = new ArrayList<>();
        Class<?> tempTotalClazz = GameServerDayTotalVO.class;
        while (tempTotalClazz != null) {
            //得到所有的field
            Field[] fields = tempTotalClazz.getDeclaredFields();
            for (Field field : fields) {
                if (Modifier.isFinal(field.getModifiers()) || Modifier.isStatic(field.getModifiers())) {
                    continue;
                }
                //map里面存储了所有的field
                fieldTotalMap.put(field.getName(), field);
                if (field.getType() == GameServerTrendVO.class) {
                    //list里面只存储了 Trend 相关的field
                    dayNTotalFieldList.add(field);
                }
            }
            tempTotalClazz = tempTotalClazz.getSuperclass();
        }
        if (dayNTotalFieldList.isEmpty()) {
            dayNTotalFieldMapList = Collections.emptyList();
        } else {
            dayNTotalFieldMapList = new ArrayList<>(dayNTotalFieldList.size());
            for (Field dayNTotalField : dayNTotalFieldList) {
                dayNTotalField.setAccessible(true);
                Field sourceField = fieldTotalMap.get(dayNTotalField.getName().replace("Trend", ""));
                sourceField.setAccessible(true);
                if (sourceField != null) {
                    dayNTotalFieldMapList.add(Tuples.of(sourceField, dayNTotalField));
                }
            }
        }

        //解析游戏区服每日的dayN映射
        Map<String, Field> fieldMapDay = new HashMap<>();
        List<Field> dayNFieldListForServerDay = new ArrayList<>();
        Class<?> tempClazzForServerDay = GameServerSumDayVO.class;
        while (tempClazzForServerDay != null) {
            //得到所有的field
            Field[] fields = tempClazzForServerDay.getDeclaredFields();
            for (Field field : fields) {
                if (Modifier.isFinal(field.getModifiers()) || Modifier.isStatic(field.getModifiers())) {
                    continue;
                }
                //map里面存储了所有的field
                fieldMapDay.put(field.getName(), field);
                if (field.getType() == GameServerTrendVO.class) {
                    //list里面只存储了 Trend 相关的field
                    dayNFieldListForServerDay.add(field);
                }
            }
            tempClazzForServerDay = tempClazzForServerDay.getSuperclass();
        }
        if (dayNFieldListForServerDay.isEmpty()) {
            dayNFieldMapListForServerDay = Collections.emptyList();
        } else {
            dayNFieldMapListForServerDay = new ArrayList<>(dayNFieldListForServerDay.size());
            for (Field dayNFieldForServerDay : dayNFieldListForServerDay) {
                dayNFieldForServerDay.setAccessible(true);
                Field sourceField = fieldMapDay.get(dayNFieldForServerDay.getName().replace("Trend", ""));
                sourceField.setAccessible(true);
                if (sourceField != null) {
                    dayNFieldMapListForServerDay.add(Tuples.of(sourceField, dayNFieldForServerDay));
                }
            }
        }

        //游戏区服每日总计dayN映射
        Map<String, Field> fieldTotalMapDay = new HashMap<>();
        List<Field> dayNTotalFieldListForServerDay = new ArrayList<>();
        Class<?> tempTotalClazzForServerDay = GameServerSumDayTotalVO.class;
        while (tempTotalClazzForServerDay != null) {
            //得到所有的field
            Field[] fields = tempTotalClazzForServerDay.getDeclaredFields();
            for (Field field : fields) {
                if (Modifier.isFinal(field.getModifiers()) || Modifier.isStatic(field.getModifiers())) {
                    continue;
                }
                //map里面存储了所有的field
                fieldTotalMapDay.put(field.getName(), field);
                if (field.getType() == GameServerTrendVO.class) {
                    //list里面只存储了 Trend 相关的field
                    dayNTotalFieldListForServerDay.add(field);
                }
            }
            tempTotalClazzForServerDay = tempTotalClazzForServerDay.getSuperclass();
        }
        if (dayNTotalFieldListForServerDay.isEmpty()) {
            dayNTotalFieldMapListForServerDay = Collections.emptyList();
        } else {
            dayNTotalFieldMapListForServerDay = new ArrayList<>(dayNTotalFieldListForServerDay.size());
            for (Field dayNTotalFieldForServerDay : dayNTotalFieldListForServerDay) {
                dayNTotalFieldForServerDay.setAccessible(true);
                Field sourceField = fieldTotalMapDay.get(dayNTotalFieldForServerDay.getName().replace("Trend", ""));
                sourceField.setAccessible(true);
                if (sourceField != null) {
                    dayNTotalFieldMapListForServerDay.add(Tuples.of(sourceField, dayNTotalFieldForServerDay));
                }
            }
        }

    }

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

    /**
     * 游戏区服数据
     *
     * @param dto 前端传递的查询参数
     * @return page
     */
    @Override
    public Page<GameServerDayVO> getGameServerDataDay(GameServerDayDTO dto) {
        com.github.sd4324530.jtuple.Tuple2<List<Long>, List<Long>> poerInfo = dataPowerComponent.getPowerInfo();
        List<Long> gameIds = dto.getGameId() == null ? poerInfo.second : dto.getGameId();

        if (dto.getGameDimension() == null) {
            //默认查询子游戏维度
            dto.setGameDimension(1L);
        }
        //默认查询子游戏维度字段
        String gameColumn = "game_id";
        if (dto.getGameDimension() == 2L) {
            gameColumn = "parent_game_id";
        }
        //创建查询条件
        Criteria cri = Cnd.cri();
        if (CollectionUtils.isNotEmpty(gameIds)) {
            //拼接游戏ID查询条件
            cri.where().andInList(gameColumn, gameIds);
        }
        if (dto.getServerId() != null) {
            //拼接区服ID查询条件
            cri.where().andEquals("server_id", dto.getServerId());
        }
        if (StringUtils.isNotBlank(dto.getServerName())) {
            //拼接区服名称查询条件
            cri.where().andLike("server_name", dto.getServerName());
        }
        if (dto.getBeginDate() != null && dto.getEndDate() != null) {
            //拼接开服时间查询条件
            cri.where().andBetween("dt", dto.getBeginDate(), dto.getEndDate());
        }
        if (StringUtils.isNotBlank(dto.getSourceSystem())) {
            //拼接sdk来源
            cri.where().andEquals("source_system", dto.getSourceSystem());
        }
        //添加排序条件
        //拼接排序条件，如果没有排序条件给默认值
        if (StringUtils.isBlank(dto.getSortType())) {
            dto.setSortType(OrderByEnum.DESC.getOrderType());
        }
        if (StringUtils.isBlank(dto.getSortFiled())) {
            cri.getOrderBy().orderBy("dt", dto.getSortType());
            cri.getOrderBy().orderBy("server_id", dto.getSortType());
        } else {
            cri.getOrderBy().orderBy(CaseFormat.LOWER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, dto.getSortFiled()), dto.getSortType());
        }
        //设置分页对象
        Pager pager = dao.createPager(dto.getPageNum(), dto.getPageSize());
        //创建sql语句查询数据
        Sql sql;
        if (dto.getGameDimension() == 1L) {
            sql = Sqls.create(gameServerDaySql() + cri);
            //设置总记录数
            pager.setRecordCount(dao.count(AdsGameServerDay.class, cri));
        } else {
            sql = Sqls.create(gameServerDaySqlForParent() + cri);
            //设置总记录数
            pager.setRecordCount(dao.count(AdsGameServerDayParent.class, cri));
        }
        //设置自定义回传对象
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(GameServerDayVO.class));
        sql.setPager(pager);
        //执行sql
        dao.execute(sql);
        List<GameServerDayVO> tempList = sql.getList(GameServerDayVO.class);
        List<GameServerDayVO> list = tempList.stream().map(vo -> {
            formatDayN(vo);
            return vo;
        }).collect(Collectors.toList());
        //返回结果
        return new Page<>(list, pager);
    }

    /**
     * 游戏区服数据总计
     *
     * @param dto 前端传递的查询参数
     * @return vo
     */
    @Override
    public GameServerDayTotalVO getGameServerDataDayTotal(GameServerDayTotalDTO dto) {
        com.github.sd4324530.jtuple.Tuple2<List<Long>, List<Long>> poerInfo = dataPowerComponent.getPowerInfo();
        List<Long> gameIds = dto.getGameId() == null ? poerInfo.second : dto.getGameId();

        //默认查询注册留存数据
        if (StringUtils.isBlank(dto.getActiveTypes())) {
            dto.setActiveTypes("reg");
        }
        if (dto.getGameDimension() == null) {
            //默认查询子游戏维度
            dto.setGameDimension(1L);
        }
        //默认查询子游戏维度字段
        String gameColumn = "game_id";
        if (dto.getGameDimension() == 2L) {
            gameColumn = "parent_game_id";
        }
        //创建查询条件
        Criteria cri = Cnd.cri();
        if (CollectionUtils.isNotEmpty(gameIds)) {
            //拼接游戏ID查询条件
            cri.where().andInList(gameColumn, gameIds);
        }
        if (dto.getServerId() != null) {
            //拼接区服ID查询条件
            cri.where().andEquals("server_id", dto.getServerId());
        }
        if (StringUtils.isNotBlank(dto.getServerName())) {
            //拼接区服名称查询条件
            cri.where().andLike("server_name", dto.getServerName());
        }
        if (dto.getBeginDate() != null && dto.getEndDate() != null) {
            //拼接开服时间查询条件
            cri.where().andBetween("dt", dto.getBeginDate(), dto.getEndDate());
        }
        if (StringUtils.isNotBlank(dto.getSourceSystem())) {
            //拼接sdk来源
            cri.where().andEquals("source_system", dto.getSourceSystem());
        }
        //创建sql语句查询数据
        Sql sql;
        if (dto.getGameDimension() == 1L) {
            sql = Sqls.create(gameServerDayTotalSql(dto.getActiveTypes()) + cri);
        } else {
            sql = Sqls.create(gameServerDayTotalSqlForParent(dto.getActiveTypes()) + cri);
        }
        //设置自定义回传对象
        sql.setCallback(Sqls.callback.entity());
        sql.setEntity(dao.getEntity(GameServerDayTotalVO.class));
        //执行sql
        dao.execute(sql);
        //得到查询的结果
        GameServerDayTotalVO vo = sql.getObject(GameServerDayTotalVO.class);
        if (StringUtils.isNotBlank(vo.getDa1())) {
            formatTotalDayN(vo, dto.getActiveTypes());
        }
        //返回结果
        return vo;
    }

    /**
     * 游戏区服每日数据
     *
     * @param dto GameServerSumDayDTO
     * @return Page<GameServerSumDayVO>
     */
    @Override
    public Page<GameServerSumDayVO> getGameServerDataSumDay(GameServerSumDayDTO dto) {
        //添加权限
        com.github.sd4324530.jtuple.Tuple2<List<Long>, List<Long>> poerInfo = dataPowerComponent.getPowerInfo();
        List<Long> gameIds = dto.getGameId() == null ? poerInfo.second : dto.getGameId();

        if (dto.getGameDimension() == null) {
            //默认查询子游戏维度
            dto.setGameDimension(1L);
        }
        //默认查询子游戏维度字段
        String gameColumn = "game_id";
        if (dto.getGameDimension() == 2L) {
            gameColumn = "parent_game_id";
        }
        //创建查询条件
        Criteria cri = Cnd.cri();
        if (CollectionUtils.isNotEmpty(gameIds)) {
            //拼接游戏ID查询条件
            cri.where().andInList(gameColumn, gameIds);
        }
        if (dto.getBeginDate() != null && dto.getEndDate() != null) {
            //拼接开服时间查询条件
            cri.where().andBetween("dt", dto.getBeginDate(), dto.getEndDate());
        }
        if (StringUtils.isNotBlank(dto.getSourceSystem())) {
            //拼接sdk来源
            cri.where().andEquals("source_system", dto.getSourceSystem());
        }
        //添加排序条件
        //拼接排序条件，如果没有排序条件给默认值
        if (StringUtils.isBlank(dto.getSortType())) {
            dto.setSortType(OrderByEnum.DESC.getOrderType());
        }
        if (StringUtils.isBlank(dto.getSortFiled())) {
            cri.getOrderBy().orderBy("dt", dto.getSortType());
            cri.getOrderBy().orderBy("game_id", dto.getSortType());
        } else {
            cri.getOrderBy().orderBy(CaseFormat.LOWER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, dto.getSortFiled()), dto.getSortType());
        }
        //设置分页对象
        Pager pager = dao.createPager(dto.getPageNum(), dto.getPageSize());
        //创建sql语句查询数据
        Sql sql;
        if (dto.getGameDimension() == 1L) {
            sql = Sqls.create(gameServerSumDaySql() + cri);
            //设置总记录数
            pager.setRecordCount(dao.count(AdsGameServerSumDay.class, cri));
        } else {
            sql = Sqls.create(gameServerSumDaySqlForParent() + cri);
            //设置总记录数
            pager.setRecordCount(dao.count(AdsGameServerSumDayParent.class, cri));
        }
        //设置自定义回传对象
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(GameServerSumDayVO.class));
        sql.setPager(pager);
        //执行sql
        dao.execute(sql);
        List<GameServerSumDayVO> tempList = sql.getList(GameServerSumDayVO.class);
        List<GameServerSumDayVO> list = tempList.stream().map(vo -> {
            formatDayNForServerDay(vo);
            return vo;
        }).collect(Collectors.toList());
        //返回结果
        return new Page<>(list, pager);

    }

    /**
     * 游戏区服每日数据总计
     *
     * @param dto GameServerSumDayTotalDTO
     * @return GameServerSumDayTotalVO
     */
    @Override
    public GameServerSumDayTotalVO getGameServerDataSumDayTotal(GameServerSumDayTotalDTO dto) {
        com.github.sd4324530.jtuple.Tuple2<List<Long>, List<Long>> poerInfo = dataPowerComponent.getPowerInfo();
        List<Long> gameIds = dto.getGameId() == null ? poerInfo.second : dto.getGameId();

        if (dto.getGameDimension() == null) {
            //默认查询子游戏维度
            dto.setGameDimension(1L);
        }
        //默认查询子游戏维度字段
        String gameColumn = "game_id";
        if (dto.getGameDimension() == 2L) {
            gameColumn = "parent_game_id";
        }

        //创建查询条件
        Criteria cri = Cnd.cri();
        if (CollectionUtils.isNotEmpty(gameIds)) {
            //拼接游戏ID查询条件
            cri.where().andInList(gameColumn, gameIds);
        }
        if (dto.getBeginDate() != null && dto.getEndDate() != null) {
            //拼接开服时间查询条件
            cri.where().andBetween("dt", dto.getBeginDate(), dto.getEndDate());
        }
        if (StringUtils.isNotBlank(dto.getSourceSystem())) {
            //拼接sdk来源
            cri.where().andEquals("source_system", dto.getSourceSystem());
        }
        //创建sql语句查询数据
        Sql sql;
        if (dto.getGameDimension() == 1L) {
            sql = Sqls.create(gameServerSumDayTotalSql() + cri);
        } else {
            sql = Sqls.create(gameServerSumDayTotalSqlForParent() + cri);
        }
        //设置自定义回传对象
        sql.setCallback(Sqls.callback.entity());
        sql.setEntity(dao.getEntity(GameServerSumDayTotalVO.class));
        //执行sql
        dao.execute(sql);
        //得到查询的结果
        GameServerSumDayTotalVO vo = sql.getObject(GameServerSumDayTotalVO.class);
        if (StringUtils.isNotBlank(vo.getDa1())) {
            formatTotalDayNForServerDay(vo);
        }
        //返回结果
        return vo;
    }

    @Override
    public List<GameServerVO> getAllGameServer(GameServerListDTO dto) {
        if (StringUtils.isBlank(dto.getSourceSystem())) {
            dto.setSourceSystem("ZX_ONE");
        }
        //创建查询条件
        Criteria cri = Cnd.cri();
        if (StringUtils.isNotBlank(dto.getSourceSystem())) {
            cri.where().andEquals("source_system", dto.getSourceSystem());
        }
        if (CollectionUtils.isNotEmpty(dto.getGameId())) {
            cri.where().andInList("game_id", dto.getGameId());
        }
        if (CollectionUtils.isNotEmpty(dto.getParentGameId())) {
            cri.where().andInList("parent_game_id", dto.getParentGameId());
        }
        //排序条件
        cri.getOrderBy().orderBy("game_id", OrderByEnum.ASC.getOrderType());
        cri.getOrderBy().orderBy("create_time", OrderByEnum.ASC.getOrderType());

        Sql sql = Sqls.create(gameServerSql() + cri);
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(GameServerVO.class));
        dao.execute(sql);
        return sql.getList(GameServerVO.class);
    }

    /**
     * 游戏区服数据
     *
     * @param dto GameServerDayDTO
     * @return Page<GameServerDayVO>
     */
    @Override
    public Page<GSGameServerDayVO> getGSGameServerDataDay(GSGameServerDayDTO dto) {
        Criteria cri = getGameServerDayCriteria(dto);
        Criteria orderCri = Cnd.cri();
        if (StringUtils.isNotBlank(dto.getSortType())) {
            cri.getOrderBy().orderBy("a.dt", dto.getSortType());
        }
        Pager pager = dao.createPager(dto.getPageNum(), dto.getPageSize());
        Sql sql = Sqls.create(getGSGameServerDateSql(cri, orderCri));
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(GSGameServerDayVO.class));
        sql.setPager(pager);
        dao.execute(sql);
        List<GSGameServerDayVO> vos = sql.getList(GSGameServerDayVO.class);
        pager.setRecordCount(dao.count("game_ads_parent.ads_game_server_gs_day_parent a", cri));

        Set<Long> serverIds = vos.stream().map(GSGameServerDayVO::getServerId).collect(Collectors.toSet());

        Set<Long> gameIds = vos.stream().map(GSGameServerDayVO::getParentGameId).collect(Collectors.toSet());
        //根据游戏id和区服id查询大R小R数据
        List<GSGameServerDayRVO> rDatas = getRData(dto.getSourceSystem(), gameIds, serverIds);
        //将数据通过source_system,server_id,parent_game_id作为key生成map 前面信息作为key，vlaue为以order_day排序的list
        Map<String, List<GSGameServerDayRVO>> collect = rDatas.stream().collect(Collectors.groupingBy(rvo -> rvo.getSourceSystem() + rvo.getServerId() + rvo.getParentGameId()));

        //获取所有的da字段
        List<Field> fieldList = Arrays.stream(GSGameServerDayVO.class.getDeclaredFields())
                .filter(field -> field.getName().contains("daStr"))
                .peek(field -> field.setAccessible(true))
                .toList();

        //获取所有的da的object
        List<Field> fieldObjectList = Arrays.stream(GSGameServerDayVO.class.getDeclaredFields())
                .filter(field -> field.getName().contains("daRetention"))
                .peek(field -> field.setAccessible(true))
                .toList();
        //把所有gsIds取出来用,拼起来
        String gsIds = vos.stream().filter(i -> StringUtils.isNotEmpty(i.getGsIds())).map(GSGameServerDayVO::getGsIds).collect(Collectors.joining(","));
        ResultVO<Map<Long, String>> userMap = null;
        if (StringUtils.isNotEmpty(gsIds)) {
            //转换为Long类型
            List<Long> userIds = Arrays.stream(gsIds.split(",")).map(Long::parseLong).toList();
            //发送RPC接口查询所有用户
            userMap = sysUserRpc.getUserNameByIds(userIds);
        }
        for (GSGameServerDayVO vo : vos) {
            List<GSGameServerDayRVO> rDataList = collect.get(vo.getSourceSystem() + vo.getServerId() + vo.getParentGameId());
            for (int i = 0; i < fieldList.size(); i++) {
                try {
                    Field field = fieldList.get(i);
                    String value = (String) field.get(vo);
                    String rData;
                    if (rDataList != null && rDataList.size() >= i + 1) {
                        //累计的充值人数，为了/r数据
                        String[] split = value.split("/");
                        GSGameServerDayRVO gsGameServerDayRVO = rDataList.get(i);
                        rData = getRDataStr(gsGameServerDayRVO, Long.valueOf(split[split.length - 1]));
                    } else {
                        rData = "0/0/0/0/0/0/0/0/0/0";
                    }
                    value += "/" + rData;
//                    field.set(vo, value + "/" + rData);
                    String[] split = value.split("/");
                    GSServerRetentionVO retentionVO = GSServerRetentionVO.builder()
                            .activeRetention(split[0])
                            .PaidRetentionRate(split[1])
                            .totalRegNum(split[2])
                            .totalAmountNum(split[3])
                            .smallR(split[5])
                            .mediumR(split[6])
                            .largeR(split[7])
                            .superR(split[8])
                            .smallRNum(split[9])
                            .mediumRNum(split[10])
                            .largeRNum(split[11])
                            .superRNum(split[12])
                            .rTotal(split[13])
                            .rTotalRetention(split[14])
                            .build();
                    fieldObjectList.get(i).set(vo, retentionVO);
                } catch (IllegalAccessException e) {
                    throw new RuntimeException(e);
                }
            }
            if (userMap != null && userMap.getData() != null && StringUtils.isNotEmpty(vo.getGsIds())) {
                Map<Long, String> data = userMap.getData();
                List<Long> userIds = Arrays.stream(vo.getGsIds().split(",")).map(Long::parseLong).toList();
                String gsNames = userIds.stream().map(data::get).collect(Collectors.joining(","));
                vo.setGsNames(gsNames);
            }

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

    @Override
    public List<String> getServerIdByGSId(Long gsId) {
        if (gsId == null) {
            return null;
        }
        Sql sql = Sqls.create(getServerIdByGSIdSql(gsId));
        sql.setCallback(Sqls.callback.strList());
        dao.execute(sql);
        List<String> list = sql.getList(String.class);
        return list;
    }

    private String getServerIdByGSIdSql(Long gsId) {
        String sql = "select server_id from dm_game_order.t_game_server_merge where find_in_set(" + gsId + ", gs_ids) ";
        return sql;
    }


    /**
     * 区服总数据
     *
     * @param dto
     * @return
     */
    @Override
    public GSGameServerDayVO getGSGameServerDataDayTotal(GSGameServerDayDTO dto) {
        Criteria cri = getGameServerDayCriteria(dto);
        Sql sql = Sqls.create(getGSGameServerDateTotalSql() + cri);
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(GSGameServerDayVO.class));
        dao.execute(sql);
        GSGameServerDayVO vo = sql.getObject(GSGameServerDayVO.class);
        List<GSGameServerTotalRVo> rDatas = getRDataList(cri);
        //获取所有的da字段
        List<Field> fieldList = Arrays.stream(GSGameServerDayVO.class.getDeclaredFields())
                .filter(field -> field.getName().contains("daStr"))
                .peek(field -> field.setAccessible(true))
                .toList();
        //获取所有的da的object
        List<Field> fieldObjectList = Arrays.stream(GSGameServerDayVO.class.getDeclaredFields())
                .filter(field -> field.getName().contains("daRetention"))
                .peek(field -> field.setAccessible(true))
                .toList();

        Map<Integer, GSGameServerDayRVO> dayRVOMap = new HashMap<>();
        for (GSGameServerTotalRVo rData : rDatas) {
            if (StringUtils.isEmpty(rData.getSmallR())) {
                continue;
            }
            String[] smallRs = rData.getSmallR().split(",");
            String[] mediumRs = rData.getMediumR().split(",");
            String[] largeRs = rData.getLargeR().split(",");
            String[] superRs = rData.getSuperR().split(",");
            for (int i = 0; i < fieldList.size(); i++) {
                if (i >= smallRs.length) {
                    continue;
                }
                String smallR = smallRs[i];
                String mediumR = mediumRs[i];
                String largeR = largeRs[i];
                String superR = superRs[i];
                if (dayRVOMap.get(i) == null) {
                    GSGameServerDayRVO gsGameServerDayRVO = new GSGameServerDayRVO();
                    gsGameServerDayRVO.setSmallR(Long.parseLong(smallR.trim()));
                    gsGameServerDayRVO.setMediumR(Long.parseLong(mediumR.trim()));
                    gsGameServerDayRVO.setLargeR(Long.parseLong(largeR.trim()));
                    gsGameServerDayRVO.setSuperR(Long.parseLong(superR.trim()));
                    dayRVOMap.put(i, gsGameServerDayRVO);
                } else {
                    GSGameServerDayRVO gsGameServerDayRVO = dayRVOMap.get(i);
                    gsGameServerDayRVO.setSmallR(gsGameServerDayRVO.getSmallR() + Long.parseLong(smallR.trim()));
                    gsGameServerDayRVO.setMediumR(gsGameServerDayRVO.getMediumR() + Long.parseLong(mediumR.trim()));
                    gsGameServerDayRVO.setLargeR(gsGameServerDayRVO.getLargeR() + Long.parseLong(largeR.trim()));
                    gsGameServerDayRVO.setSuperR(gsGameServerDayRVO.getSuperR() + Long.parseLong(superR.trim()));
                }
            }
        }
        for (int i = 0; i < fieldList.size(); i++) {
            Field field = fieldList.get(i);
            try {
                String value = (String) field.get(vo);
                //总的累计数据
                String[] split1 = value.split("/");
                GSGameServerDayRVO gsGameServerDayRVO = dayRVOMap.get(i);
                String rData = getRDataStr(gsGameServerDayRVO, Long.valueOf(split1[split1.length - 1]));
                value += "/" + rData;
                String[] split = value.split("/");
                GSServerRetentionVO retentionVO = GSServerRetentionVO.builder()
                        .activeRetention(split[0])
                        .PaidRetentionRate(split[1])
                        .totalRegNum(split[2])
                        .totalAmountNum(split[3])
                        .smallR(split[5])
                        .mediumR(split[6])
                        .largeR(split[7])
                        .superR(split[8])
                        .smallRNum(split[9])
                        .mediumRNum(split[10])
                        .largeRNum(split[11])
                        .superRNum(split[12])
                        .rTotal(split[13])
                        .rTotalRetention(split[14])
                        .build();
                fieldObjectList.get(i).set(vo, retentionVO);
            } catch (IllegalAccessException e) {
                throw new RuntimeException(e);
            }
        }
        return vo;
    }


    private List<GSGameServerTotalRVo> getRDataList(Criteria cri) {
        Sql sql = Sqls.create(getGSGameServerDateAndRSql(cri));
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(GSGameServerTotalRVo.class));
        dao.execute(sql);
        return sql.getList(GSGameServerTotalRVo.class);
    }

    private String getGSGameServerDateAndRSql(Criteria cri) {

        return """
                select a.source_system,
                       a.server_id,
                       a.parent_game_id,
                       GROUP_CONCAT(CONCAT(CAST(xiao_r AS CHAR))) as small_r,
                       GROUP_CONCAT(CONCAT(CAST(zhong_r AS CHAR))) as medium_r,
                       GROUP_CONCAT(CONCAT(CAST(da_r AS CHAR))) as large_r,
                       GROUP_CONCAT(CONCAT(CAST(chao_r AS CHAR))) as super_r
                from game_ads_parent.ads_game_server_gs_day_parent a
                left join game_ads_parent.ads_game_server_gs_r_day_parent e
                          on a.source_system = e.source_system and a.server_id = e.server_id and
                             a.parent_game_id = e.parent_game_id and a.dt = e.dt
                             """ + cri + """
                group by a.source_system, a.server_id, a.parent_game_id
                """;
    }


    /**
     * 拼接R留存率  (R/充值总人数)
     *
     * @param rvo
     * @return
     */
    private String getRDataStr(GSGameServerDayRVO rvo, Long totalNum) {
        if (rvo != null && totalNum != null && totalNum != 0) {
            //R人数
            Long smallRCount = rvo.getSmallR();
            Long mediumRCount = rvo.getMediumR();
            Long largeRCont = rvo.getLargeR();
            Long superRCount = rvo.getSuperR();
            //R总
            long rCount = smallRCount + mediumRCount + largeRCont + superRCount;
            //R留存率
            BigDecimal smallR = new BigDecimal(smallRCount).divide(new BigDecimal(totalNum), 4, RoundingMode.HALF_UP).multiply(BigDecimal.valueOf(100)).setScale(2, RoundingMode.DOWN);
            BigDecimal mediumR = new BigDecimal(mediumRCount).divide(new BigDecimal(totalNum), 4, RoundingMode.HALF_UP).multiply(BigDecimal.valueOf(100)).setScale(2, RoundingMode.DOWN);
            BigDecimal largeR = new BigDecimal(largeRCont).divide(new BigDecimal(totalNum), 4, RoundingMode.HALF_UP).multiply(BigDecimal.valueOf(100)).setScale(2, RoundingMode.DOWN);
            BigDecimal superR = new BigDecimal(superRCount).divide(new BigDecimal(totalNum), 4, RoundingMode.HALF_UP).multiply(BigDecimal.valueOf(100)).setScale(2, RoundingMode.DOWN);
            //R总留存
            BigDecimal r = new BigDecimal(rCount).divide(new BigDecimal(totalNum), 4, RoundingMode.HALF_UP).multiply(BigDecimal.valueOf(100)).setScale(2, RoundingMode.DOWN);
            return smallR + "/" + mediumR + "/" + largeR + "/" + superR + "/" + smallRCount + "/" + mediumRCount + "/" + largeRCont + "/" + superRCount+"/"+rCount+"/"+r;
        } else {
            return "0/0/0/0/0/0/0/0/0/0";
        }
    }


    /**
     * 获取R数据
     *
     * @param
     * @return
     */
    private List<GSGameServerDayRVO> getRData(String sourceSystem, Set<Long> gameIds, Set<Long> serverIds) {
        Criteria cri = Cnd.cri();
        if (CollectionUtils.isNotEmpty(gameIds)) {
            List<Long> gameList = new ArrayList<>(gameIds);
            cri.where().andInList("parent_game_id", gameList);
        }
        if (CollectionUtils.isNotEmpty(serverIds)) {
            List<Long> serverList = new ArrayList<>(serverIds);
            cri.where().andInList("server_id", serverList);
        }
        if (StringUtils.isNotEmpty(sourceSystem)) {
            cri.where().andEquals("source_system", sourceSystem);
        }
        Sql sql = Sqls.create(getGSGameServerRDateSql(cri));
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(GSGameServerDayRVO.class));
        dao.execute(sql);
        List<GSGameServerDayRVO> vos = sql.getList(GSGameServerDayRVO.class);
        return vos;
    }

    private String getGSGameServerRDateSql(Criteria cri) {
        return """
                 select
                 source_system,
                 order_day,
                 server_id,
                 parent_game_id,
                 IFNULL(any_value(xiao_r),0) as small_r,
                 IFNULL(any_value(zhong_r),0) as medium_r,
                 IFNULL(any_value(da_r),0) as large_r,
                 IFNULL(any_value(chao_r),0) as super_r
                 from game_ads_parent.ads_game_server_gs_r_day_parent
                """ + cri + """
                group by server_id,parent_game_id,source_system,order_day
                order by order_day asc
                """;
    }


    private Criteria getGameServerDayCriteria(GSGameServerDayDTO dto) {
        GameAuthUserVO userGameInfo = gameAuthRpc.getGameAuthByUserIds().getData();
        List<Long> serverIdByGS = null;
        //判断是不是GS
        if (GameAuthEnum.getByValue(userGameInfo.getGameAuthEnum().getValue()) != null) {
            //查询GS管理的区服列表
            serverIdByGS = getServerIdByGSId(SecurityUtil.getUserId()).stream().filter(StringUtils::isNotEmpty).map(Long::parseLong).collect(Collectors.toList());
        }
        //创建查询条件
        Criteria cri = Cnd.cri();
        if (CollectionUtils.isNotEmpty(serverIdByGS)) {
            cri.where().andInList("a.server_id", serverIdByGS);
        }
        if (StringUtils.isNotEmpty(dto.getSourceSystem())) {
            cri.where().andEquals("a.source_system", dto.getSourceSystem());
        }
        if (dto.getBeginDate() != null && dto.getEndDate() != null) {
            cri.where().andBetween("a.dt", dto.getBeginDate(), dto.getEndDate());
        }
        if (CollectionUtils.isNotEmpty(dto.getGameId())) {
            cri.where().andInList("a.parent_game_id", dto.getGameId());
        }
        if (CollectionUtils.isNotEmpty(dto.getServerId())) {
            cri.where().andInList("a.server_id", dto.getServerId());
        }
        if (StringUtils.isNotEmpty(dto.getServerName())) {
            cri.where().andLike("a.server_name", dto.getServerName());
        }
        if (dto.getGsId() != null) {
            cri.where().and(new Static("find_in_set(" + dto.getGsId() + ",a.gs_ids)"));
        }
        return cri;
    }

    private String getGSGameServerDateSql(Criteria cri, Criteria orderCri) {
        return """
                select
                    """ + getRemainDaySql() + """
                a.source_system,
                a.server_id,
                a.server_name,
                a.dt,
                datediff(Date(now()),a.dt)+ 1 as days, -- 开服日期 要算当天所以+1
                a.parent_game_id,
                a.parent_game_classify,
                a.parent_game_name,
                a.gs_ids,
                a.total_role_num,
                a.total_reg_num,
                a.total_amount_num
                from game_ads_parent.ads_game_server_gs_day_parent a
                left join game_dw_parent.dw_game_server_active_role_day_parent e  on a.source_system = e.source_system  and a.server_id =e.server_id and a.parent_game_id=e.parent_game_id and a.dt = e.dt
                left join game_dw_parent.dw_game_server_day_parent c  on a.source_system =c.source_system  and a.server_id =c.server_id and a.parent_game_id=c.parent_game_id and a.dt = c.dt
                left join game_dw_parent.dw_game_server_active_order_day_parent d on a.source_system = d.source_system  and a.server_id =d.server_id and a.parent_game_id=d.parent_game_id and a.dt = d.dt
                """ + cri + """
                """ + orderCri + """
                """;
    }

    private String getGSGameServerDateTotalSql() {
        return """
                select
                    """ + getRemainDayTotalSql() + """
                    SUM(ifnull(c.total_role_num, 0)) total_role_num,  -- 创角人数
                    SUM(ifnull(c.total_reg_num, 0))  total_reg_num,   -- 注册人数
                    SUM(ifnull(c.total_num, 0))      total_amount_num -- 付费人数
                    from game_ads_parent.ads_game_server_gs_day_parent a
                    left join game_dw_parent.dw_game_server_active_role_day_parent e  on a.source_system = e.source_system  and a.server_id =e.server_id and a.parent_game_id=e.parent_game_id and a.dt = e.dt
                    left join game_dw_parent.dw_game_server_day_parent c  on a.source_system =c.source_system  and a.server_id =c.server_id and a.parent_game_id=c.parent_game_id and a.dt = c.dt
                    left join game_dw_parent.dw_game_server_active_order_day_parent d on a.source_system = d.source_system  and a.server_id =d.server_id and a.parent_game_id=d.parent_game_id and a.dt = d.dt
                """;
    }

    /**
     * 获取每天活跃留存率/付费留存率的sql
     * -
     */
    private String getRemainDaySql() {
        StringBuilder sql = new StringBuilder(StringUtils.EMPTY);
        for (int i = 1; i <= 90; i++) {
            sql.append("""
                    concat(a.da%d,'/',IFNULL(c.da%d_role_num,0),'/',IFNULL(c.da%d_num,0),'/',IFNULL(c.da%d_total_num,0)) as da_str%d,
                     """.formatted(i, i, i, i, i));

        }
        return sql.toString();
    }

    /**
     * 获取总计每天活跃留存率/付费留存率的sql
     */
    private String getRemainDayTotalSql() {
        StringBuilder sql = new StringBuilder(StringUtils.EMPTY);
        for (int i = 1; i <= 90; i++) {
            sql.append("""
                    concat(ifnull(round(SUM(c.da%d_active_num) / SUM(e.new_da%d_total_num) ,4),0),'/',ifnull(round(sum(c.da%d_num)/sum(c.da%d_total_num),4),0),'/',IFNULL(SUM(c.da%d_role_num),0),'/',IFNULL(SUM(c.da%d_num),0),'/',IFNULL(sum(c.da%d_total_num),0)) as da_str%d,
                     """.formatted(i, i, i, i, i, i, i, i));
        }
        return sql.toString();
    }


    public static void main(String[] args) {
        String remainDaySql = new GameServerServiceImpl().getRemainDaySql();
        System.out.println(remainDaySql);
    }


    /**
     * 将vo中的原始String数据修改为一个对象
     *
     * @param vo 展示给前端的游戏区服数据
     */
    private void formatDayN(GameServerDayVO vo) {
        if (CollectionUtils.isEmpty(dayNFieldMapList)) {
            return;
        }
        dayNFieldMapList.forEach(dayNFieldMap -> {
            try {
                String[] temps = ((String) dayNFieldMap.getT1().get(vo)).split("/");
                dayNFieldMap.getT2().set(vo, GameServerTrendVO.builder()
                        .regNum(Long.parseLong(temps[0]))
                        .roleNum(Long.parseLong(temps[1]))
                        .activeNum(Long.parseLong(temps[2]))
                        .amountNum(Long.parseLong(temps[3]))
                        .amount(new BigDecimal(temps[4]))
                        .rollServerNum(Long.parseLong(temps[5]))
                        .rollServerAmountNum(Long.parseLong(temps[6]))
                        .rollServerAmount(new BigDecimal(temps[7]))
                        .rollServerAmountRate(new BigDecimal(temps[8]))
                        .regActiveNum(Long.parseLong(temps[9]))
                        .regActiveRate(new BigDecimal(temps[10]))
                        .newUserAmountNum(Long.parseLong(temps[11]))
                        .oldActiveUserNum(Long.parseLong(temps[12]))
                        .newUserTotalAmountNum(Long.parseLong(temps[13]))
                        .newActiveUserTotalNum(Long.parseLong(temps[14]))
                        .amountActiveRate(new BigDecimal(temps[15]))
                        .roleNewUserNum(Long.parseLong(temps[16]))
                        .roleOldActiveUserNum(Long.parseLong(temps[17]))
                        .roleNewUserTotalNum(Long.parseLong(temps[18]))
                        .roleNewActiveUserTotalNum(Long.parseLong(temps[19]))
                        .roleActiveRate(new BigDecimal(temps[20]))
                        .build()
                );
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }

        });
    }

    /**
     * 将vo中的原始String数据修改为一个对象(游戏区服每日数据)
     *
     * @param vo 展示给前端的游戏区服每日数据
     */
    private void formatDayNForServerDay(GameServerSumDayVO vo) {
        if (CollectionUtils.isEmpty(dayNFieldMapListForServerDay)) {
            return;
        }
        dayNFieldMapListForServerDay.forEach(dayNFieldMapForServerDay -> {
            try {
                String[] temps = ((String) dayNFieldMapForServerDay.getT1().get(vo)).split("/");
                dayNFieldMapForServerDay.getT2().set(vo, GameServerTrendVO.builder()
                        .regNum(Long.parseLong(temps[0]))
                        .roleNum(Long.parseLong(temps[1]))
                        .activeNum(Long.parseLong(temps[2]))
                        .amountNum(Long.parseLong(temps[3]))
                        .amount(new BigDecimal(temps[4]))
                        .rollServerNum(Long.parseLong(temps[5]))
                        .rollServerAmountNum(Long.parseLong(temps[6]))
                        .rollServerAmount(new BigDecimal(temps[7]))
                        .rollServerAmountRate(new BigDecimal(temps[8]))
                        .build()
                );
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }

        });
    }

    /**
     * 将vo中的原始String数据修改为一个对象
     *
     * @param vo         展示给前端的游戏区服数据总计
     * @param activeType 查询的留存类型 reg -> 注册留存 ; role -> 角色留存 ; amount -> 付费留存
     */
    private void formatTotalDayN(GameServerDayTotalVO vo, String activeType) {
        if (CollectionUtils.isEmpty(dayNTotalFieldMapList)) {
            return;
        }
        dayNTotalFieldMapList.forEach(dayNTotalFieldMap -> {
            try {
                /*注册人数0/创角人数1/活跃人数2/付费人数3/付费金额4/滚服人数5/滚服付费人数6/滚服付费金额7/ （游戏区服数据）*/
                String[] temps = ((String) dayNTotalFieldMap.getT1().get(vo)).split("/");
                //付费金额
                BigDecimal amount = new BigDecimal(temps[4]).setScale(2, RoundingMode.HALF_UP);
                //滚服付费金额
                BigDecimal rollServerAmount = new BigDecimal(temps[7]);
                //不同留存数据
                if ("reg".equals(activeType)) {
                    //注册留存
                    /* DayN活跃人数8/ （注册留存数据）/ 注册人数9（去除时间未到的数据）*/
                    //活跃人数（注册留存数据）
                    Long regActiveNum = Long.valueOf(temps[8]);
                    //注册人数（注册留存数据）
                    Long regNum = Long.valueOf(temps[9]);
                    //设置trend对象
                    dayNTotalFieldMap.getT2().set(vo, GameServerTrendVO.builder()
                            .regNum(Long.parseLong(temps[0]))
                            .roleNum(Long.parseLong(temps[1]))
                            .activeNum(Long.parseLong(temps[2]))
                            .amountNum(Long.parseLong(temps[3]))
                            .amount(amount)
                            .rollServerNum(Long.parseLong(temps[5]))
                            .rollServerAmountNum(Long.parseLong(temps[6]))
                            .rollServerAmount(rollServerAmount)
                            .rollServerAmountRate(amount.compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                                    rollServerAmount.divide(amount, 4, RoundingMode.HALF_UP))
                            .regActiveNum(regActiveNum)
                            .regActiveRate(regNum == 0L ? BigDecimal.ZERO :
                                    BigDecimal.valueOf(regActiveNum.doubleValue() / regNum.doubleValue()).setScale(4, RoundingMode.HALF_UP))
                            .build());
                } else if ("amount".equals(activeType)) {
                    //付费留存
                    /* 第N天的新增付费用户8/第N天的老活跃用户9/第N天的累计付费用户10/第N天的累计活跃用户11/（付费留存数据）*/
                    //累计到第N天的付费人数（付费留存数据）
                    Long amountTotalNum = Long.parseLong(temps[10]);
                    //累计到第N天的活跃人数（付费留存数据）
                    Long amountActiveTotalNum = Long.valueOf(temps[11]);
                    //设置trend对象
                    dayNTotalFieldMap.getT2().set(vo, GameServerTrendVO.builder()
                            .regNum(Long.parseLong(temps[0]))
                            .roleNum(Long.parseLong(temps[1]))
                            .activeNum(Long.parseLong(temps[2]))
                            .amountNum(Long.parseLong(temps[3]))
                            .amount(amount)
                            .rollServerNum(Long.parseLong(temps[5]))
                            .rollServerAmountNum(Long.parseLong(temps[6]))
                            .rollServerAmount(rollServerAmount)
                            .rollServerAmountRate(amount.compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                                    rollServerAmount.divide(amount, 4, RoundingMode.HALF_UP))
                            .newUserAmountNum(Long.parseLong(temps[8]))
                            .oldActiveUserNum(Long.valueOf(temps[9]))
                            .newUserTotalAmountNum(amountTotalNum)
                            .newActiveUserTotalNum(amountActiveTotalNum)
                            .amountActiveRate(amountTotalNum == 0L ? BigDecimal.ZERO :
                                    BigDecimal.valueOf(amountActiveTotalNum.doubleValue() / amountTotalNum.doubleValue())
                                            .setScale(4, RoundingMode.HALF_UP))
                            .build());
                } else {
                    /* 第N天的新增创角用户8/第N天的老活跃用户9/第N天的累计创角用户10/第N天的累计活跃用户11/（创角留存数据）*/
                    //创角留存
                    //累计到第N天的创角用户数
                    Long roleActiveTotalNum = Long.valueOf(temps[10]);
                    //累计到第N天的活跃人数(活跃人数)
                    Long roleActiveUserNum = Long.valueOf(temps[11]);
                    //设置trend对象
                    dayNTotalFieldMap.getT2().set(vo, GameServerTrendVO.builder()
                            .regNum(Long.parseLong(temps[0]))
                            .roleNum(Long.parseLong(temps[1]))
                            .activeNum(Long.parseLong(temps[2]))
                            .amountNum(Long.parseLong(temps[3]))
                            .amount(amount)
                            .rollServerNum(Long.parseLong(temps[5]))
                            .rollServerAmountNum(Long.parseLong(temps[6]))
                            .rollServerAmount(rollServerAmount)
                            .rollServerAmountRate(amount.compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                                    rollServerAmount.divide(amount, 4, RoundingMode.HALF_UP))
                            .roleNewUserNum(Long.valueOf(temps[8]))
                            .roleOldActiveUserNum(Long.valueOf(temps[9]))
                            .roleNewUserTotalNum(roleActiveTotalNum)
                            .roleNewActiveUserTotalNum(roleActiveUserNum)
                            .roleActiveRate(roleActiveTotalNum == 0L ? BigDecimal.ZERO :
                                    BigDecimal.valueOf(roleActiveUserNum.doubleValue() / roleActiveTotalNum.doubleValue())
                                            .setScale(4, RoundingMode.HALF_UP))
                            .build());
                }
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }

        });
    }

    /**
     * 将vo中的原始String数据修改为一个对象
     *
     * @param vo 展示给前端的游戏区服每日数据总计
     */
    private void formatTotalDayNForServerDay(GameServerSumDayTotalVO vo) {
        if (CollectionUtils.isEmpty(dayNTotalFieldMapListForServerDay)) {
            return;
        }
        dayNTotalFieldMapListForServerDay.forEach(dayNTotalFieldMap -> {
            try {
                /*注册人数0/创角人数1/活跃人数2/付费人数3/付费金额4/滚服人数5/滚服付费人数6/滚服付费金额7/ （游戏区服数据）*/
                String[] temps = ((String) dayNTotalFieldMap.getT1().get(vo)).split("/");
                //付费金额
                BigDecimal amount = new BigDecimal(temps[4]).setScale(2, RoundingMode.HALF_UP);
                //滚服付费金额
                BigDecimal rollServerAmount = new BigDecimal(temps[7]);
                //设置trend对象
                dayNTotalFieldMap.getT2().set(vo, GameServerTrendVO.builder()
                        .regNum(Long.parseLong(temps[0]))
                        .roleNum(Long.parseLong(temps[1]))
                        .activeNum(Long.parseLong(temps[2]))
                        .amountNum(Long.parseLong(temps[3]))
                        .amount(amount)
                        .rollServerNum(Long.parseLong(temps[5]))
                        .rollServerAmountNum(Long.parseLong(temps[6]))
                        .rollServerAmount(rollServerAmount)
                        .rollServerAmountRate(amount.compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                                rollServerAmount.divide(amount, 4, RoundingMode.HALF_UP))
                        .build());
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }

        });
    }

    /**
     * 计算总计一栏的daN数据
     * 注册人数/创角人数/活跃人数/付费人数/付费金额/滚服人数/滚服付费人数/滚服付费金额/ （游戏区服数据）
     * DayN活跃人数/ （注册留存数据）/ 注册人数（去除时间未到的数据）
     * 第N天的新增付费用户/第N天的老活跃用户/第N天的累计付费用户/第N天的累计活跃用户/ （付费留存数据）
     * 第N天的新增创角用户/第N天的老活跃用户/第N天的累计创角用户/第N天的累计活跃用户/ （创角留存数据）
     *
     * @param activeType reg -> 注册留存 ; role -> 角色留存 ; amount -> 付费留存
     * @return String
     */
    private String trendDay(String activeType) {
        StringBuilder daySql = new StringBuilder(StringUtils.EMPTY);
        //注册留存数据
        if ("reg".equals(activeType)) {
            //拼接da1-da90 sql
            for (int i = 1; i <= 90; i++) {
                daySql.append("""
                        CONCAT(
                        	SUM(da%s[1]), '/',
                        	SUM(da%s[2]), '/',
                        	SUM(da%s[3]), '/',
                        	SUM(da%s[4]), '/',
                        	SUM(da%s[5]), '/',
                        	SUM(da%s[6]), '/',
                        	SUM(da%s[7]), '/',
                        	SUM(da%s[8]), '/',
                        	SUM(da%s[10]), '/',
                            SUM(IF(DATE_ADD(dt, INTERVAL %s day) <= Local.now(), total_reg_num, 0))
                        ) as da%s ,
                        """.formatted(i, i, i, i, i, i, i, i, i, i - 1, i));
            }
            //拼接m4-m12 sql
            for (int i = 4; i <= 12; i++) {
                daySql.append("""
                        CONCAT(
                        	SUM(m%s[1]), '/',
                        	SUM(m%s[2]), '/',
                        	SUM(m%s[3]), '/',
                        	SUM(m%s[4]), '/',
                        	SUM(m%s[5]), '/',
                        	SUM(m%s[6]), '/',
                        	SUM(m%s[7]), '/',
                        	SUM(m%s[8]), '/',
                        	SUM(m%s[10]), '/',
                            SUM(IF(DATE_ADD(dt, INTERVAL %s month) <= Local.now(), total_reg_num, 0))
                        ) as m%s ,
                        """.formatted(i, i, i, i, i, i, i, i, i, i, i));
            }
            //拼接total
            daySql.append("""
                    CONCAT(
                    	SUM(total[1]), '/',
                    	SUM(total[2]), '/',
                    	SUM(total[3]), '/',
                    	SUM(total[4]), '/',
                    	SUM(total[5]), '/',
                    	SUM(total[6]), '/',
                    	SUM(total[7]), '/',
                    	SUM(total[8]), '/',
                    	SUM(total[10]), '/',
                        SUM(total_reg_num)
                    ) as total
                    """);
        } else if ("amount".equals(activeType)) {
            //付费留存数据
            //拼接da1-da90 sql
            for (int i = 1; i <= 90; i++) {
                daySql.append("""
                        CONCAT(
                        	SUM(da%s[1]), '/',
                        	SUM(da%s[2]), '/',
                        	SUM(da%s[3]), '/',
                        	SUM(da%s[4]), '/',
                        	SUM(da%s[5]), '/',
                        	SUM(da%s[6]), '/',
                        	SUM(da%s[7]), '/',
                        	SUM(da%s[8]), '/',
                            SUM(da%s[12]), '/',
                            SUM(da%s[13]), '/',
                            SUM(IF(DATE_ADD(dt, INTERVAL %s day) <= Local.now(), da%s[14], 0)), '/',
                            SUM(IF(DATE_ADD(dt, INTERVAL %s day) <= Local.now(), da%s[15], 0))
                        ) as da%s ,
                        """.formatted(i, i, i, i, i, i, i, i, i, i, i - 1, i, i - 1, i, i));
            }
            //拼接m4-m12 sql
            for (int i = 4; i <= 12; i++) {
                daySql.append("""
                        CONCAT(
                        	SUM(m%s[1]), '/',
                        	SUM(m%s[2]), '/',
                        	SUM(m%s[3]), '/',
                        	SUM(m%s[4]), '/',
                        	SUM(m%s[5]), '/',
                        	SUM(m%s[6]), '/',
                        	SUM(m%s[7]), '/',
                        	SUM(m%s[8]), '/',
                            SUM(m%s[12]), '/',
                            SUM(m%s[13]), '/',
                            SUM(IF(DATE_ADD(dt, INTERVAL %s month) <= Local.now(), m%s[14], 0)), '/',
                            SUM(IF(DATE_ADD(dt, INTERVAL %s month) <= Local.now(), m%s[15], 0))
                        ) as m%s ,
                        """.formatted(i, i, i, i, i, i, i, i, i, i, i, i, i, i, i));
            }
            //拼接total
            daySql.append("""
                    CONCAT(
                    	SUM(total[1]), '/',
                    	SUM(total[2]), '/',
                    	SUM(total[3]), '/',
                    	SUM(total[4]), '/',
                    	SUM(total[5]), '/',
                    	SUM(total[6]), '/',
                    	SUM(total[7]), '/',
                    	SUM(total[8]), '/',
                        SUM(total[12]), '/',
                        SUM(total[13]), '/',
                        SUM(total[14]), '/',
                        SUM(total[15])
                    ) as total
                    """);
        } else {
            //创角留存数据
            //拼接da1-da90 sql
            for (int i = 1; i <= 90; i++) {
                daySql.append("""
                        CONCAT(
                        	SUM(da%s[1]), '/',
                        	SUM(da%s[2]), '/',
                        	SUM(da%s[3]), '/',
                        	SUM(da%s[4]), '/',
                        	SUM(da%s[5]), '/',
                        	SUM(da%s[6]), '/',
                        	SUM(da%s[7]), '/',
                        	SUM(da%s[8]), '/',
                            SUM(da%s[17]), '/',
                            SUM(da%s[18]), '/',
                            SUM(IF(DATE_ADD(dt, INTERVAL %s day) <= Local.now(), da%s[19], 0)), '/',
                            SUM(IF(DATE_ADD(dt, INTERVAL %s day) <= Local.now(), da%s[20], 0))
                        ) as da%s ,
                        """.formatted(i, i, i, i, i, i, i, i, i, i, i - 1, i, i - 1, i, i));
            }
            //拼接m4-m12 sql
            for (int i = 4; i <= 12; i++) {
                daySql.append("""
                        CONCAT(
                        	SUM(m%s[1]), '/',
                        	SUM(m%s[2]), '/',
                        	SUM(m%s[3]), '/',
                        	SUM(m%s[4]), '/',
                        	SUM(m%s[5]), '/',
                        	SUM(m%s[6]), '/',
                        	SUM(m%s[7]), '/',
                        	SUM(m%s[8]), '/',
                            SUM(m%s[17]), '/',
                            SUM(m%s[18]), '/',
                            SUM(IF(DATE_ADD(dt, INTERVAL %s month) <= Local.now(), m%s[19], 0)), '/',
                            SUM(IF(DATE_ADD(dt, INTERVAL %s month) <= Local.now(), m%s[20], 0))
                        ) as m%s ,
                        """.formatted(i, i, i, i, i, i, i, i, i, i, i, i, i, i, i));
            }
            //拼接total
            daySql.append("""
                    CONCAT(
                    	SUM(total[1]), '/',
                    	SUM(total[2]), '/',
                    	SUM(total[3]), '/',
                    	SUM(total[4]), '/',
                    	SUM(total[5]), '/',
                    	SUM(total[6]), '/',
                    	SUM(total[7]), '/',
                    	SUM(total[8]), '/',
                        SUM(total[17]), '/',
                        SUM(total[18]), '/',
                        SUM(total[19]), '/',
                        SUM(total[20])
                    ) as total
                    """);
        }

        return daySql.toString();
    }

    /**
     * 计算总计一栏的daN数据(游戏区服每日总计)
     * 注册人数/创角人数/活跃人数/付费人数/付费金额/滚服人数/滚服付费人数/滚服付费金额/ （游戏区服数据）
     */
    private String trendDayForServerDay() {
        StringBuilder daySql = new StringBuilder(StringUtils.EMPTY);
        //拼接da1-da90 sql
        for (int i = 1; i <= 90; i++) {
            daySql.append("""
                    CONCAT(
                    	SUM(da%s[1]), '/',
                    	SUM(da%s[2]), '/',
                    	SUM(da%s[3]), '/',
                    	SUM(da%s[4]), '/',
                    	SUM(da%s[5]), '/',
                    	SUM(da%s[6]), '/',
                    	SUM(da%s[7]), '/',
                    	SUM(da%s[8])
                    ) as da%s ,
                    """.formatted(i, i, i, i, i, i, i, i, i));
        }
        //拼接m4-m12 sql
        for (int i = 4; i <= 12; i++) {
            daySql.append("""
                    CONCAT(
                    	SUM(m%s[1]), '/',
                    	SUM(m%s[2]), '/',
                    	SUM(m%s[3]), '/',
                    	SUM(m%s[4]), '/',
                    	SUM(m%s[5]), '/',
                    	SUM(m%s[6]), '/',
                    	SUM(m%s[7]), '/',
                    	SUM(m%s[8])
                    ) as m%s ,
                    """.formatted(i, i, i, i, i, i, i, i, i));
        }
        //拼接total
        daySql.append("""
                CONCAT(
                	SUM(total[1]), '/',
                	SUM(total[2]), '/',
                	SUM(total[3]), '/',
                	SUM(total[4]), '/',
                	SUM(total[5]), '/',
                	SUM(total[6]), '/',
                	SUM(total[7]), '/',
                	SUM(total[8])
                ) as total
                """);

        return daySql.toString();
    }

    /**
     * 游戏区服数据SQL
     *
     * @return String
     */
    private String gameServerDaySql() {
        return """
                SELECT
                    dt,
                    source_system,
                    server_id,
                    server_name,
                    game_id,
                    game_name,
                    classify,
                    out_total_num,
                    out_total_amount_num,
                    out_total_amount,
                    out_total_rate,
                    total_role_num,
                    total_reg_num,
                    total_amount_num,
                    total_amount,
                    da1,
                    da2,
                    da3,
                    da4,
                    da5,
                    da6,
                    da7,
                    da8,
                    da9,
                    da10,
                    da11,
                    da12,
                    da13,
                    da14,
                    da15,
                    da16,
                    da17,
                    da18,
                    da19,
                    da20,
                    da21,
                    da22,
                    da23,
                    da24,
                    da25,
                    da26,
                    da27,
                    da28,
                    da29,
                    da30,
                    da31,
                    da32,
                    da33,
                    da34,
                    da35,
                    da36,
                    da37,
                    da38,
                    da39,
                    da40,
                    da41,
                    da42,
                    da43,
                    da44,
                    da45,
                    da46,
                    da47,
                    da48,
                    da49,
                    da50,
                    da51,
                    da52,
                    da53,
                    da54,
                    da55,
                    da56,
                    da57,
                    da58,
                    da59,
                    da60,
                    da61,
                    da62,
                    da63,
                    da64,
                    da65,
                    da66,
                    da67,
                    da68,
                    da69,
                    da70,
                    da71,
                    da72,
                    da73,
                    da74,
                    da75,
                    da76,
                    da77,
                    da78,
                    da79,
                    da80,
                    da81,
                    da82,
                    da83,
                    da84,
                    da85,
                    da86,
                    da87,
                    da88,
                    da89,
                    da90,
                    m4,
                    m5,
                    m6,
                    m7,
                    m8,
                    m9,
                    m10,
                    m11,
                    m12,
                    total
                FROM
                    game_ads.ads_game_server_day
                """;
    }

    /**
     * 游戏区服数据SQL-父游戏维度
     *
     * @return String
     */
    private String gameServerDaySqlForParent() {
        return """
                SELECT
                    dt,
                    source_system,
                    server_id,
                    server_name,
                    parent_game_id as game_id,
                    parent_game_name as game_name,
                    parent_game_classify as classify,
                    out_total_num,
                    out_total_amount_num,
                    out_total_amount,
                    out_total_rate,
                    total_role_num,
                    total_reg_num,
                    total_amount_num,
                    total_amount,
                    da1,
                    da2,
                    da3,
                    da4,
                    da5,
                    da6,
                    da7,
                    da8,
                    da9,
                    da10,
                    da11,
                    da12,
                    da13,
                    da14,
                    da15,
                    da16,
                    da17,
                    da18,
                    da19,
                    da20,
                    da21,
                    da22,
                    da23,
                    da24,
                    da25,
                    da26,
                    da27,
                    da28,
                    da29,
                    da30,
                    da31,
                    da32,
                    da33,
                    da34,
                    da35,
                    da36,
                    da37,
                    da38,
                    da39,
                    da40,
                    da41,
                    da42,
                    da43,
                    da44,
                    da45,
                    da46,
                    da47,
                    da48,
                    da49,
                    da50,
                    da51,
                    da52,
                    da53,
                    da54,
                    da55,
                    da56,
                    da57,
                    da58,
                    da59,
                    da60,
                    da61,
                    da62,
                    da63,
                    da64,
                    da65,
                    da66,
                    da67,
                    da68,
                    da69,
                    da70,
                    da71,
                    da72,
                    da73,
                    da74,
                    da75,
                    da76,
                    da77,
                    da78,
                    da79,
                    da80,
                    da81,
                    da82,
                    da83,
                    da84,
                    da85,
                    da86,
                    da87,
                    da88,
                    da89,
                    da90,
                    m4,
                    m5,
                    m6,
                    m7,
                    m8,
                    m9,
                    m10,
                    m11,
                    m12,
                    total
                FROM
                    game_ads_parent.ads_game_server_day_parent
                """;
    }

    /**
     * 游戏区服每日数据SQL
     *
     * @return String
     */
    private String gameServerSumDaySql() {
        return """
                SELECT
                    id,
                    dt,
                    source_system,
                    server_id,
                    server_name,
                    game_id,
                    game_name,
                    classify,
                    out_total_num,
                    out_total_amount_num,
                    out_total_amount,
                    out_total_rate,
                    total_role_num,
                    total_reg_num,
                    total_amount_num,
                    total_amount,
                    da1,
                    da2,
                    da3,
                    da4,
                    da5,
                    da6,
                    da7,
                    da8,
                    da9,
                    da10,
                    da11,
                    da12,
                    da13,
                    da14,
                    da15,
                    da16,
                    da17,
                    da18,
                    da19,
                    da20,
                    da21,
                    da22,
                    da23,
                    da24,
                    da25,
                    da26,
                    da27,
                    da28,
                    da29,
                    da30,
                    da31,
                    da32,
                    da33,
                    da34,
                    da35,
                    da36,
                    da37,
                    da38,
                    da39,
                    da40,
                    da41,
                    da42,
                    da43,
                    da44,
                    da45,
                    da46,
                    da47,
                    da48,
                    da49,
                    da50,
                    da51,
                    da52,
                    da53,
                    da54,
                    da55,
                    da56,
                    da57,
                    da58,
                    da59,
                    da60,
                    da61,
                    da62,
                    da63,
                    da64,
                    da65,
                    da66,
                    da67,
                    da68,
                    da69,
                    da70,
                    da71,
                    da72,
                    da73,
                    da74,
                    da75,
                    da76,
                    da77,
                    da78,
                    da79,
                    da80,
                    da81,
                    da82,
                    da83,
                    da84,
                    da85,
                    da86,
                    da87,
                    da88,
                    da89,
                    da90,
                    m4,
                    m5,
                    m6,
                    m7,
                    m8,
                    m9,
                    m10,
                    m11,
                    m12,
                    total
                FROM
                    game_ads.ads_game_server_day_sum
                """;
    }

    /**
     * 游戏区服每日数据SQL-父游戏维度
     *
     * @return String
     */
    private String gameServerSumDaySqlForParent() {
        return """
                SELECT
                    id,
                    dt,
                    source_system,
                    server_id,
                    server_name,
                    parent_game_id as game_id,
                    parent_game_name as game_name,
                    parent_game_classify as classify,
                    out_total_num,
                    out_total_amount_num,
                    out_total_amount,
                    out_total_rate,
                    total_role_num,
                    total_reg_num,
                    total_amount_num,
                    total_amount,
                    da1,
                    da2,
                    da3,
                    da4,
                    da5,
                    da6,
                    da7,
                    da8,
                    da9,
                    da10,
                    da11,
                    da12,
                    da13,
                    da14,
                    da15,
                    da16,
                    da17,
                    da18,
                    da19,
                    da20,
                    da21,
                    da22,
                    da23,
                    da24,
                    da25,
                    da26,
                    da27,
                    da28,
                    da29,
                    da30,
                    da31,
                    da32,
                    da33,
                    da34,
                    da35,
                    da36,
                    da37,
                    da38,
                    da39,
                    da40,
                    da41,
                    da42,
                    da43,
                    da44,
                    da45,
                    da46,
                    da47,
                    da48,
                    da49,
                    da50,
                    da51,
                    da52,
                    da53,
                    da54,
                    da55,
                    da56,
                    da57,
                    da58,
                    da59,
                    da60,
                    da61,
                    da62,
                    da63,
                    da64,
                    da65,
                    da66,
                    da67,
                    da68,
                    da69,
                    da70,
                    da71,
                    da72,
                    da73,
                    da74,
                    da75,
                    da76,
                    da77,
                    da78,
                    da79,
                    da80,
                    da81,
                    da82,
                    da83,
                    da84,
                    da85,
                    da86,
                    da87,
                    da88,
                    da89,
                    da90,
                    m4,
                    m5,
                    m6,
                    m7,
                    m8,
                    m9,
                    m10,
                    m11,
                    m12,
                    total
                FROM
                    game_ads_parent.ads_game_server_day_sum_parent
                """;
    }

    /**
     * 游戏区服数据总计SQL
     *
     * @param activeType 查询的留存类型 reg -> 注册留存 ; role -> 角色留存 ; amount -> 付费留存
     * @return String
     */
    private String gameServerDayTotalSql(String activeType) {
        return """
                SELECT
                    IFNULL(SUM(out_total_num), 0) as out_total_num,
                    IFNULL(SUM(out_total_amount_num), 0) as out_total_amount_num,
                    IFNULL(SUM(out_total_amount), 0) as out_total_amount,
                    ROUND(IF(SUM(total_amount) > 0, SUM(out_total_amount) / SUM(total_amount), 0), 4) as out_total_rate,
                    IFNULL(SUM(total_role_num), 0) as total_role_num,
                    IFNULL(SUM(total_reg_num), 0) as total_reg_num,
                    IFNULL(SUM(total_amount_num), 0) as total_amount_num,
                    IFNULL(SUM(total_amount), 0) as total_amount,
                """ + trendDay(activeType) +
                """
                        FROM (
                            SELECT
                                dt,
                                source_system,
                                server_id,
                                server_name,
                                game_id,
                                game_name,
                                classify,
                                out_total_num,
                                out_total_amount_num,
                                out_total_amount,
                                out_total_rate,
                                total_role_num,
                                total_reg_num,
                                total_amount_num,
                                total_amount,
                                SPLIT(da1, '/') as da1,
                                SPLIT(da2, '/') as da2,
                                SPLIT(da3, '/') as da3,
                                SPLIT(da4, '/') as da4,
                                SPLIT(da5, '/') as da5,
                                SPLIT(da6, '/') as da6,
                                SPLIT(da7, '/') as da7,
                                SPLIT(da8, '/') as da8,
                                SPLIT(da9, '/') as da9,
                                SPLIT(da10, '/') as da10,
                                SPLIT(da11, '/') as da11,
                                SPLIT(da12, '/') as da12,
                                SPLIT(da13, '/') as da13,
                                SPLIT(da14, '/') as da14,
                                SPLIT(da15, '/') as da15,
                                SPLIT(da16, '/') as da16,
                                SPLIT(da17, '/') as da17,
                                SPLIT(da18, '/') as da18,
                                SPLIT(da19, '/') as da19,
                                SPLIT(da20, '/') as da20,
                                SPLIT(da21, '/') as da21,
                                SPLIT(da22, '/') as da22,
                                SPLIT(da23, '/') as da23,
                                SPLIT(da24, '/') as da24,
                                SPLIT(da25, '/') as da25,
                                SPLIT(da26, '/') as da26,
                                SPLIT(da27, '/') as da27,
                                SPLIT(da28, '/') as da28,
                                SPLIT(da29, '/') as da29,
                                SPLIT(da30, '/') as da30,
                                SPLIT(da31, '/') as da31,
                                SPLIT(da32, '/') as da32,
                                SPLIT(da33, '/') as da33,
                                SPLIT(da34, '/') as da34,
                                SPLIT(da35, '/') as da35,
                                SPLIT(da36, '/') as da36,
                                SPLIT(da37, '/') as da37,
                                SPLIT(da38, '/') as da38,
                                SPLIT(da39, '/') as da39,
                                SPLIT(da40, '/') as da40,
                                SPLIT(da41, '/') as da41,
                                SPLIT(da42, '/') as da42,
                                SPLIT(da43, '/') as da43,
                                SPLIT(da44, '/') as da44,
                                SPLIT(da45, '/') as da45,
                                SPLIT(da46, '/') as da46,
                                SPLIT(da47, '/') as da47,
                                SPLIT(da48, '/') as da48,
                                SPLIT(da49, '/') as da49,
                                SPLIT(da50, '/') as da50,
                                SPLIT(da51, '/') as da51,
                                SPLIT(da52, '/') as da52,
                                SPLIT(da53, '/') as da53,
                                SPLIT(da54, '/') as da54,
                                SPLIT(da55, '/') as da55,
                                SPLIT(da56, '/') as da56,
                                SPLIT(da57, '/') as da57,
                                SPLIT(da58, '/') as da58,
                                SPLIT(da59, '/') as da59,
                                SPLIT(da60, '/') as da60,
                                SPLIT(da61, '/') as da61,
                                SPLIT(da62, '/') as da62,
                                SPLIT(da63, '/') as da63,
                                SPLIT(da64, '/') as da64,
                                SPLIT(da65, '/') as da65,
                                SPLIT(da66, '/') as da66,
                                SPLIT(da67, '/') as da67,
                                SPLIT(da68, '/') as da68,
                                SPLIT(da69, '/') as da69,
                                SPLIT(da70, '/') as da70,
                                SPLIT(da71, '/') as da71,
                                SPLIT(da72, '/') as da72,
                                SPLIT(da73, '/') as da73,
                                SPLIT(da74, '/') as da74,
                                SPLIT(da75, '/') as da75,
                                SPLIT(da76, '/') as da76,
                                SPLIT(da77, '/') as da77,
                                SPLIT(da78, '/') as da78,
                                SPLIT(da79, '/') as da79,
                                SPLIT(da80, '/') as da80,
                                SPLIT(da81, '/') as da81,
                                SPLIT(da82, '/') as da82,
                                SPLIT(da83, '/') as da83,
                                SPLIT(da84, '/') as da84,
                                SPLIT(da85, '/') as da85,
                                SPLIT(da86, '/') as da86,
                                SPLIT(da87, '/') as da87,
                                SPLIT(da88, '/') as da88,
                                SPLIT(da89, '/') as da89,
                                SPLIT(da90, '/') as da90,
                                SPLIT(m4, '/') as m4,
                                SPLIT(m5, '/') as m5,
                                SPLIT(m6, '/') as m6,
                                SPLIT(m7, '/') as m7,
                                SPLIT(m8, '/') as m8,
                                SPLIT(m9, '/') as m9,
                                SPLIT(m10, '/') as m10,
                                SPLIT(m11, '/') as m11,
                                SPLIT(m12, '/') as m12,
                                SPLIT(total, '/') as total
                            FROM
                                game_ads.ads_game_server_day
                        ) a
                                """;
    }

    /**
     * 游戏区服数据总计SQL-父游戏维度
     *
     * @param activeType 查询的留存类型 reg -> 注册留存 ; role -> 角色留存 ; amount -> 付费留存
     * @return String
     */
    private String gameServerDayTotalSqlForParent(String activeType) {
        return """
                SELECT
                    IFNULL(SUM(out_total_num), 0) as out_total_num,
                    IFNULL(SUM(out_total_amount_num), 0) as out_total_amount_num,
                    IFNULL(SUM(out_total_amount), 0) as out_total_amount,
                    ROUND(IF(SUM(total_amount) > 0, SUM(out_total_amount) / SUM(total_amount), 0), 4) as out_total_rate,
                    IFNULL(SUM(total_role_num), 0) as total_role_num,
                    IFNULL(SUM(total_reg_num), 0) as total_reg_num,
                    IFNULL(SUM(total_amount_num), 0) as total_amount_num,
                    IFNULL(SUM(total_amount), 0) as total_amount,
                """ + trendDay(activeType) +
                """
                        FROM (
                            SELECT
                                dt,
                                source_system,
                                server_id,
                                server_name,
                                parent_game_id,
                                parent_game_name,
                                parent_game_classify,
                                out_total_num,
                                out_total_amount_num,
                                out_total_amount,
                                out_total_rate,
                                total_role_num,
                                total_reg_num,
                                total_amount_num,
                                total_amount,
                                SPLIT(da1, '/') as da1,
                                SPLIT(da2, '/') as da2,
                                SPLIT(da3, '/') as da3,
                                SPLIT(da4, '/') as da4,
                                SPLIT(da5, '/') as da5,
                                SPLIT(da6, '/') as da6,
                                SPLIT(da7, '/') as da7,
                                SPLIT(da8, '/') as da8,
                                SPLIT(da9, '/') as da9,
                                SPLIT(da10, '/') as da10,
                                SPLIT(da11, '/') as da11,
                                SPLIT(da12, '/') as da12,
                                SPLIT(da13, '/') as da13,
                                SPLIT(da14, '/') as da14,
                                SPLIT(da15, '/') as da15,
                                SPLIT(da16, '/') as da16,
                                SPLIT(da17, '/') as da17,
                                SPLIT(da18, '/') as da18,
                                SPLIT(da19, '/') as da19,
                                SPLIT(da20, '/') as da20,
                                SPLIT(da21, '/') as da21,
                                SPLIT(da22, '/') as da22,
                                SPLIT(da23, '/') as da23,
                                SPLIT(da24, '/') as da24,
                                SPLIT(da25, '/') as da25,
                                SPLIT(da26, '/') as da26,
                                SPLIT(da27, '/') as da27,
                                SPLIT(da28, '/') as da28,
                                SPLIT(da29, '/') as da29,
                                SPLIT(da30, '/') as da30,
                                SPLIT(da31, '/') as da31,
                                SPLIT(da32, '/') as da32,
                                SPLIT(da33, '/') as da33,
                                SPLIT(da34, '/') as da34,
                                SPLIT(da35, '/') as da35,
                                SPLIT(da36, '/') as da36,
                                SPLIT(da37, '/') as da37,
                                SPLIT(da38, '/') as da38,
                                SPLIT(da39, '/') as da39,
                                SPLIT(da40, '/') as da40,
                                SPLIT(da41, '/') as da41,
                                SPLIT(da42, '/') as da42,
                                SPLIT(da43, '/') as da43,
                                SPLIT(da44, '/') as da44,
                                SPLIT(da45, '/') as da45,
                                SPLIT(da46, '/') as da46,
                                SPLIT(da47, '/') as da47,
                                SPLIT(da48, '/') as da48,
                                SPLIT(da49, '/') as da49,
                                SPLIT(da50, '/') as da50,
                                SPLIT(da51, '/') as da51,
                                SPLIT(da52, '/') as da52,
                                SPLIT(da53, '/') as da53,
                                SPLIT(da54, '/') as da54,
                                SPLIT(da55, '/') as da55,
                                SPLIT(da56, '/') as da56,
                                SPLIT(da57, '/') as da57,
                                SPLIT(da58, '/') as da58,
                                SPLIT(da59, '/') as da59,
                                SPLIT(da60, '/') as da60,
                                SPLIT(da61, '/') as da61,
                                SPLIT(da62, '/') as da62,
                                SPLIT(da63, '/') as da63,
                                SPLIT(da64, '/') as da64,
                                SPLIT(da65, '/') as da65,
                                SPLIT(da66, '/') as da66,
                                SPLIT(da67, '/') as da67,
                                SPLIT(da68, '/') as da68,
                                SPLIT(da69, '/') as da69,
                                SPLIT(da70, '/') as da70,
                                SPLIT(da71, '/') as da71,
                                SPLIT(da72, '/') as da72,
                                SPLIT(da73, '/') as da73,
                                SPLIT(da74, '/') as da74,
                                SPLIT(da75, '/') as da75,
                                SPLIT(da76, '/') as da76,
                                SPLIT(da77, '/') as da77,
                                SPLIT(da78, '/') as da78,
                                SPLIT(da79, '/') as da79,
                                SPLIT(da80, '/') as da80,
                                SPLIT(da81, '/') as da81,
                                SPLIT(da82, '/') as da82,
                                SPLIT(da83, '/') as da83,
                                SPLIT(da84, '/') as da84,
                                SPLIT(da85, '/') as da85,
                                SPLIT(da86, '/') as da86,
                                SPLIT(da87, '/') as da87,
                                SPLIT(da88, '/') as da88,
                                SPLIT(da89, '/') as da89,
                                SPLIT(da90, '/') as da90,
                                SPLIT(m4, '/') as m4,
                                SPLIT(m5, '/') as m5,
                                SPLIT(m6, '/') as m6,
                                SPLIT(m7, '/') as m7,
                                SPLIT(m8, '/') as m8,
                                SPLIT(m9, '/') as m9,
                                SPLIT(m10, '/') as m10,
                                SPLIT(m11, '/') as m11,
                                SPLIT(m12, '/') as m12,
                                SPLIT(total, '/') as total
                            FROM
                                game_ads_parent.ads_game_server_day_parent
                        ) a
                        """;
    }

    /**
     * 游戏区服每日数据总计SQL
     *
     * @return String
     */
    private String gameServerSumDayTotalSql() {
        return """
                SELECT
                    IFNULL(SUM(out_total_num), 0) as out_total_num,
                    IFNULL(SUM(out_total_amount_num), 0) as out_total_amount_num,
                    IFNULL(SUM(out_total_amount), 0) as out_total_amount,
                    ROUND(IF(SUM(total_amount) > 0, SUM(out_total_amount) / SUM(total_amount), 0), 4) as out_total_rate,
                    IFNULL(SUM(total_role_num), 0) as total_role_num,
                    IFNULL(SUM(total_reg_num), 0) as total_reg_num,
                    IFNULL(SUM(total_amount_num), 0) as total_amount_num,
                    IFNULL(SUM(total_amount), 0) as total_amount,
                """ + trendDayForServerDay() +
                """
                        FROM (
                            SELECT
                                dt,
                                source_system,
                                server_id,
                                server_name,
                                game_id,
                                game_name,
                                classify,
                                out_total_num,
                                out_total_amount_num,
                                out_total_amount,
                                out_total_rate,
                                total_role_num,
                                total_reg_num,
                                total_amount_num,
                                total_amount,
                                SPLIT(da1, '/') as da1,
                                SPLIT(da2, '/') as da2,
                                SPLIT(da3, '/') as da3,
                                SPLIT(da4, '/') as da4,
                                SPLIT(da5, '/') as da5,
                                SPLIT(da6, '/') as da6,
                                SPLIT(da7, '/') as da7,
                                SPLIT(da8, '/') as da8,
                                SPLIT(da9, '/') as da9,
                                SPLIT(da10, '/') as da10,
                                SPLIT(da11, '/') as da11,
                                SPLIT(da12, '/') as da12,
                                SPLIT(da13, '/') as da13,
                                SPLIT(da14, '/') as da14,
                                SPLIT(da15, '/') as da15,
                                SPLIT(da16, '/') as da16,
                                SPLIT(da17, '/') as da17,
                                SPLIT(da18, '/') as da18,
                                SPLIT(da19, '/') as da19,
                                SPLIT(da20, '/') as da20,
                                SPLIT(da21, '/') as da21,
                                SPLIT(da22, '/') as da22,
                                SPLIT(da23, '/') as da23,
                                SPLIT(da24, '/') as da24,
                                SPLIT(da25, '/') as da25,
                                SPLIT(da26, '/') as da26,
                                SPLIT(da27, '/') as da27,
                                SPLIT(da28, '/') as da28,
                                SPLIT(da29, '/') as da29,
                                SPLIT(da30, '/') as da30,
                                SPLIT(da31, '/') as da31,
                                SPLIT(da32, '/') as da32,
                                SPLIT(da33, '/') as da33,
                                SPLIT(da34, '/') as da34,
                                SPLIT(da35, '/') as da35,
                                SPLIT(da36, '/') as da36,
                                SPLIT(da37, '/') as da37,
                                SPLIT(da38, '/') as da38,
                                SPLIT(da39, '/') as da39,
                                SPLIT(da40, '/') as da40,
                                SPLIT(da41, '/') as da41,
                                SPLIT(da42, '/') as da42,
                                SPLIT(da43, '/') as da43,
                                SPLIT(da44, '/') as da44,
                                SPLIT(da45, '/') as da45,
                                SPLIT(da46, '/') as da46,
                                SPLIT(da47, '/') as da47,
                                SPLIT(da48, '/') as da48,
                                SPLIT(da49, '/') as da49,
                                SPLIT(da50, '/') as da50,
                                SPLIT(da51, '/') as da51,
                                SPLIT(da52, '/') as da52,
                                SPLIT(da53, '/') as da53,
                                SPLIT(da54, '/') as da54,
                                SPLIT(da55, '/') as da55,
                                SPLIT(da56, '/') as da56,
                                SPLIT(da57, '/') as da57,
                                SPLIT(da58, '/') as da58,
                                SPLIT(da59, '/') as da59,
                                SPLIT(da60, '/') as da60,
                                SPLIT(da61, '/') as da61,
                                SPLIT(da62, '/') as da62,
                                SPLIT(da63, '/') as da63,
                                SPLIT(da64, '/') as da64,
                                SPLIT(da65, '/') as da65,
                                SPLIT(da66, '/') as da66,
                                SPLIT(da67, '/') as da67,
                                SPLIT(da68, '/') as da68,
                                SPLIT(da69, '/') as da69,
                                SPLIT(da70, '/') as da70,
                                SPLIT(da71, '/') as da71,
                                SPLIT(da72, '/') as da72,
                                SPLIT(da73, '/') as da73,
                                SPLIT(da74, '/') as da74,
                                SPLIT(da75, '/') as da75,
                                SPLIT(da76, '/') as da76,
                                SPLIT(da77, '/') as da77,
                                SPLIT(da78, '/') as da78,
                                SPLIT(da79, '/') as da79,
                                SPLIT(da80, '/') as da80,
                                SPLIT(da81, '/') as da81,
                                SPLIT(da82, '/') as da82,
                                SPLIT(da83, '/') as da83,
                                SPLIT(da84, '/') as da84,
                                SPLIT(da85, '/') as da85,
                                SPLIT(da86, '/') as da86,
                                SPLIT(da87, '/') as da87,
                                SPLIT(da88, '/') as da88,
                                SPLIT(da89, '/') as da89,
                                SPLIT(da90, '/') as da90,
                                SPLIT(m4, '/') as m4,
                                SPLIT(m5, '/') as m5,
                                SPLIT(m6, '/') as m6,
                                SPLIT(m7, '/') as m7,
                                SPLIT(m8, '/') as m8,
                                SPLIT(m9, '/') as m9,
                                SPLIT(m10, '/') as m10,
                                SPLIT(m11, '/') as m11,
                                SPLIT(m12, '/') as m12,
                                SPLIT(total, '/') as total
                            FROM
                                game_ads.ads_game_server_day_sum
                        ) a
                                """;
    }

    /**
     * 游戏区服每日数据总计SQL-父游戏维度
     *
     * @return String
     */
    private String gameServerSumDayTotalSqlForParent() {
        return """
                SELECT
                    IFNULL(SUM(out_total_num), 0) as out_total_num,
                    IFNULL(SUM(out_total_amount_num), 0) as out_total_amount_num,
                    IFNULL(SUM(out_total_amount), 0) as out_total_amount,
                    ROUND(IF(SUM(total_amount) > 0, SUM(out_total_amount) / SUM(total_amount), 0), 4) as out_total_rate,
                    IFNULL(SUM(total_role_num), 0) as total_role_num,
                    IFNULL(SUM(total_reg_num), 0) as total_reg_num,
                    IFNULL(SUM(total_amount_num), 0) as total_amount_num,
                    IFNULL(SUM(total_amount), 0) as total_amount,
                """ + trendDayForServerDay() +
                """
                        FROM (
                            SELECT
                                dt,
                                source_system,
                                server_id,
                                server_name,
                                parent_game_id,
                                parent_game_name,
                                parent_game_classify,
                                out_total_num,
                                out_total_amount_num,
                                out_total_amount,
                                out_total_rate,
                                total_role_num,
                                total_reg_num,
                                total_amount_num,
                                total_amount,
                                SPLIT(da1, '/') as da1,
                                SPLIT(da2, '/') as da2,
                                SPLIT(da3, '/') as da3,
                                SPLIT(da4, '/') as da4,
                                SPLIT(da5, '/') as da5,
                                SPLIT(da6, '/') as da6,
                                SPLIT(da7, '/') as da7,
                                SPLIT(da8, '/') as da8,
                                SPLIT(da9, '/') as da9,
                                SPLIT(da10, '/') as da10,
                                SPLIT(da11, '/') as da11,
                                SPLIT(da12, '/') as da12,
                                SPLIT(da13, '/') as da13,
                                SPLIT(da14, '/') as da14,
                                SPLIT(da15, '/') as da15,
                                SPLIT(da16, '/') as da16,
                                SPLIT(da17, '/') as da17,
                                SPLIT(da18, '/') as da18,
                                SPLIT(da19, '/') as da19,
                                SPLIT(da20, '/') as da20,
                                SPLIT(da21, '/') as da21,
                                SPLIT(da22, '/') as da22,
                                SPLIT(da23, '/') as da23,
                                SPLIT(da24, '/') as da24,
                                SPLIT(da25, '/') as da25,
                                SPLIT(da26, '/') as da26,
                                SPLIT(da27, '/') as da27,
                                SPLIT(da28, '/') as da28,
                                SPLIT(da29, '/') as da29,
                                SPLIT(da30, '/') as da30,
                                SPLIT(da31, '/') as da31,
                                SPLIT(da32, '/') as da32,
                                SPLIT(da33, '/') as da33,
                                SPLIT(da34, '/') as da34,
                                SPLIT(da35, '/') as da35,
                                SPLIT(da36, '/') as da36,
                                SPLIT(da37, '/') as da37,
                                SPLIT(da38, '/') as da38,
                                SPLIT(da39, '/') as da39,
                                SPLIT(da40, '/') as da40,
                                SPLIT(da41, '/') as da41,
                                SPLIT(da42, '/') as da42,
                                SPLIT(da43, '/') as da43,
                                SPLIT(da44, '/') as da44,
                                SPLIT(da45, '/') as da45,
                                SPLIT(da46, '/') as da46,
                                SPLIT(da47, '/') as da47,
                                SPLIT(da48, '/') as da48,
                                SPLIT(da49, '/') as da49,
                                SPLIT(da50, '/') as da50,
                                SPLIT(da51, '/') as da51,
                                SPLIT(da52, '/') as da52,
                                SPLIT(da53, '/') as da53,
                                SPLIT(da54, '/') as da54,
                                SPLIT(da55, '/') as da55,
                                SPLIT(da56, '/') as da56,
                                SPLIT(da57, '/') as da57,
                                SPLIT(da58, '/') as da58,
                                SPLIT(da59, '/') as da59,
                                SPLIT(da60, '/') as da60,
                                SPLIT(da61, '/') as da61,
                                SPLIT(da62, '/') as da62,
                                SPLIT(da63, '/') as da63,
                                SPLIT(da64, '/') as da64,
                                SPLIT(da65, '/') as da65,
                                SPLIT(da66, '/') as da66,
                                SPLIT(da67, '/') as da67,
                                SPLIT(da68, '/') as da68,
                                SPLIT(da69, '/') as da69,
                                SPLIT(da70, '/') as da70,
                                SPLIT(da71, '/') as da71,
                                SPLIT(da72, '/') as da72,
                                SPLIT(da73, '/') as da73,
                                SPLIT(da74, '/') as da74,
                                SPLIT(da75, '/') as da75,
                                SPLIT(da76, '/') as da76,
                                SPLIT(da77, '/') as da77,
                                SPLIT(da78, '/') as da78,
                                SPLIT(da79, '/') as da79,
                                SPLIT(da80, '/') as da80,
                                SPLIT(da81, '/') as da81,
                                SPLIT(da82, '/') as da82,
                                SPLIT(da83, '/') as da83,
                                SPLIT(da84, '/') as da84,
                                SPLIT(da85, '/') as da85,
                                SPLIT(da86, '/') as da86,
                                SPLIT(da87, '/') as da87,
                                SPLIT(da88, '/') as da88,
                                SPLIT(da89, '/') as da89,
                                SPLIT(da90, '/') as da90,
                                SPLIT(m4, '/') as m4,
                                SPLIT(m5, '/') as m5,
                                SPLIT(m6, '/') as m6,
                                SPLIT(m7, '/') as m7,
                                SPLIT(m8, '/') as m8,
                                SPLIT(m9, '/') as m9,
                                SPLIT(m10, '/') as m10,
                                SPLIT(m11, '/') as m11,
                                SPLIT(m12, '/') as m12,
                                SPLIT(total, '/') as total
                            FROM
                                game_ads_parent.ads_game_server_day_sum_parent
                        ) a
                        """;
    }

    /**
     * 获取所有区服sql
     *
     * @return
     */
    private String gameServerSql() {
        return """
                SELECT
                    *
                FROM (
                    SELECT
                        a.source_system ,
                        a.game_id ,
                        a.game_name ,
                        a.server_id ,
                        a.server_name ,
                        IFNULL(b.parent_id , a.game_id ) as parent_game_id,
                        a.create_time
                    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
                """;
    }


}
