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

import com.google.common.base.CaseFormat;
import com.google.gson.Gson;
import com.zanxiang.game.data.serve.component.DataPowerComponent;
import com.zanxiang.game.data.serve.pojo.dto.*;
import com.zanxiang.game.data.serve.pojo.entity.AdsPitcherDay;
import com.zanxiang.game.data.serve.pojo.entity.AdsPitcherDayParent;
import com.zanxiang.game.data.serve.pojo.entity.AdsPitcherDayn;
import com.zanxiang.game.data.serve.pojo.entity.AdsPitcherGameDayn;
import com.zanxiang.game.data.serve.pojo.enums.OrderByEnum;
import com.zanxiang.game.data.serve.pojo.vo.*;
import com.zanxiang.game.data.serve.service.IPitcherDataService;
import com.zanxiang.game.data.serve.utils.Page;
import com.zanxiang.module.util.DateUtil;
import com.zanxiang.module.util.exception.BaseException;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.collections4.MapUtils;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.StringUtils;
import org.nutz.dao.Cnd;
import org.nutz.dao.Dao;
import org.nutz.dao.Sqls;
import org.nutz.dao.pager.Pager;
import org.nutz.dao.sql.Criteria;
import org.nutz.dao.sql.Sql;
import org.nutz.dao.util.Daos;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.BeanWrapper;
import org.springframework.beans.BeanWrapperImpl;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import reactor.util.function.Tuple2;
import reactor.util.function.Tuples;

import java.beans.PropertyDescriptor;
import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.time.LocalDate;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @author tianhua
 * @time 2023/8/15
 * @Description 投手数据接口逻辑处理
 **/
@Service
@Slf4j
public class PitcherDataServiceImpl implements IPitcherDataService {
    //存储PitcherGameDataDayVO的映射
    private static final List<Tuple2<Field, Field>> pitcherGameDayNFieldMapList;
    //存储PitcherGameDataDayTotalVO的映射
    private static final List<Tuple2<Field, Field>> pitcherGameDayNTotalFieldMapList;
    //存储PitcherDataDayVO的映射
    private static final List<Tuple2<Field, Field>> pitcherDayNFieldMapList;
    //存储PitcherDataDayTotalVO的映射
    private static final List<Tuple2<Field, Field>> pitcherDayNTotalFieldMapList;

    //解析投手游戏每日数据及总计一栏相关的映射
    static {
        //解析PitcherGameDataDayVO的映射
        Map<String, Field> fieldMap = new HashMap<>();
        List<Field> dayNFieldList = new ArrayList<>();
        Class<?> tempClazz = PitcherGameDataDayVO.class;
        while (tempClazz != null) {
            Field[] fields = tempClazz.getDeclaredFields();
            for (Field field : fields) {
                if (Modifier.isFinal(field.getModifiers()) || Modifier.isStatic(field.getModifiers())) {
                    //属性前面的修饰符为static或final不加入map中
                    continue;
                }
                fieldMap.put(field.getName(), field);
                if (field.getType() == RechargeTrendVO.class) {
                    //存储所有类型为RechargeTrendVO属性的映射 以Trend为结尾的
                    dayNFieldList.add(field);
                }
            }
            tempClazz = tempClazz.getSuperclass();
        }
        if (dayNFieldList.isEmpty()) {
            pitcherGameDayNFieldMapList = Collections.emptyList();
        } else {
            pitcherGameDayNFieldMapList = new ArrayList<>(dayNFieldList.size());
            for (Field field : dayNFieldList) {
                field.setAccessible(true);
                Field sourceField = fieldMap.get(field.getName().replace("Trend", ""));
                sourceField.setAccessible(true);
                if (sourceField != null) {
                    pitcherGameDayNFieldMapList.add(Tuples.of(sourceField, field));
                }
            }
        }

        //解析PitcherGameDataDayTotalVO映射
        Map<String, Field> fieldTotalMap = new HashMap<>();
        List<Field> dayNTotalFieldList = new ArrayList<>();
        Class<?> tempTotalClazz = PitcherGameDataDayTotalVO.class;
        while (tempTotalClazz != null) {
            Field[] fields = tempTotalClazz.getDeclaredFields();
            for (Field field : fields) {
                if (Modifier.isFinal(field.getModifiers()) || Modifier.isStatic(field.getModifiers())) {
                    //属性为static或final的不加入map
                    continue;
                }
                fieldTotalMap.put(field.getName(), field);
                if (field.getType() == RechargeTrendVO.class) {
                    //只存储属性值类型为RechargeTrendVO的属性
                    dayNTotalFieldList.add(field);
                }
            }
            tempTotalClazz = tempTotalClazz.getSuperclass();
        }
        if (dayNTotalFieldList.isEmpty()) {
            pitcherGameDayNTotalFieldMapList = Collections.emptyList();
        } else {
            pitcherGameDayNTotalFieldMapList = new ArrayList<>(dayNTotalFieldList.size());
            for (Field field : dayNTotalFieldList) {
                field.setAccessible(true);
                Field sourceField = fieldTotalMap.get(field.getName().replace("Trend", ""));
                sourceField.setAccessible(true);
                if (sourceField != null) {
                    pitcherGameDayNTotalFieldMapList.add(Tuples.of(sourceField, field));
                }
            }
        }

    }

    //解析投手每日数据及总计一栏相关的的映射
    static {
        //解析PitcherDataDayVO映射
        Map<String, Field> pitcherDayFieldMap = new HashMap<>();
        List<Field> pitcherDayDayNList = new ArrayList<>();
        Class<?> pitcherDayClazz = PitcherDataDayVO.class;
        while (pitcherDayClazz != null) {
            Field[] fields = pitcherDayClazz.getDeclaredFields();
            for (Field field : fields) {
                if (Modifier.isFinal(field.getModifiers()) || Modifier.isStatic(field.getModifiers())) {
                    continue;
                }
                pitcherDayFieldMap.put(field.getName(), field);
                if (field.getType() == RechargeTrendVO.class) {
                    pitcherDayDayNList.add(field);
                }
            }
            pitcherDayClazz = pitcherDayClazz.getSuperclass();
        }
        if (pitcherDayDayNList.isEmpty()) {
            pitcherDayNFieldMapList = Collections.emptyList();
        } else {
            pitcherDayNFieldMapList = new ArrayList<>(pitcherDayDayNList.size());
            for (Field field : pitcherDayDayNList) {
                field.setAccessible(true);
                Field soureField = pitcherDayFieldMap.get(field.getName().replace("Trend", ""));
                soureField.setAccessible(true);
                pitcherDayNFieldMapList.add(Tuples.of(soureField, field));
            }
        }

        //解析PitcherDataDayTotalVO映射
        Map<String, Field> pitcherDayTotalMap = new HashMap<>();
        List<Field> pitcherDayTotalList = new ArrayList<>();
        Class<?> pitcherDayTotalClazz = PitcherDataDayTotalVO.class;
        while (pitcherDayTotalClazz != null) {
            Field[] fields = pitcherDayTotalClazz.getDeclaredFields();
            for (Field field : fields) {
                if (Modifier.isFinal(field.getModifiers()) || Modifier.isStatic(field.getModifiers())) {
                    continue;
                }
                pitcherDayTotalMap.put(field.getName(), field);
                if (field.getType() == RechargeTrendVO.class) {
                    pitcherDayTotalList.add(field);
                }
            }
            pitcherDayTotalClazz = pitcherDayTotalClazz.getSuperclass();
        }
        if (pitcherDayTotalList.isEmpty()) {
            pitcherDayNTotalFieldMapList = Collections.emptyList();
        } else {
            pitcherDayNTotalFieldMapList = new ArrayList<>(pitcherDayTotalList.size());
            for (Field field : pitcherDayTotalList) {
                field.setAccessible(true);
                Field sourceField = pitcherDayTotalMap.get(field.getName().replace("Trend", ""));
                sourceField.setAccessible(true);
                pitcherDayNTotalFieldMapList.add(Tuples.of(sourceField, field));
            }
        }
    }

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

    /**
     * 投手每日数据
     *
     * @param dto PitcherDataDayDTO
     * @return Page<PitcherDataDayVO>
     */
    @Override
    public Page<PitcherDataDayVO> getPitcherDataDay(PitcherDataDayDTO dto) {
        com.github.sd4324530.jtuple.Tuple2<List<Long>, List<Long>> poerInfo = dataPowerComponent.getPowerInfo(dto.getSourceSystem());
        List<Long> userIds = CollectionUtils.isEmpty(dto.getPitcherId()) ? poerInfo.first : dto.getPitcherId();
//        List<Long> userIds = CollectionUtils.isEmpty(dto.getPitcherId()) ? null : dto.getPitcherId();

        //不传递时间，默认查询当天
        if (dto.getBeginDate() == null || dto.getEndDate() == null) {
            dto.setBeginDate(LocalDate.now());
            dto.setEndDate(LocalDate.now());
        }
        if (dto.getGameDimension() == null) {
            //默认查询子游戏维度数据
            dto.setGameDimension(1L);
        }
        String tableName = "game_ads.ads_pitcher_day";
        Class<?> clazz = AdsPitcherDay.class;
        if (dto.getGameDimension() == 2L) {
            tableName = "game_ads_parent.ads_pitcher_day_parent";
            clazz = AdsPitcherDayParent.class;
        }
        //创建查询条件
        Criteria cri = Cnd.cri();
        if (CollectionUtils.isNotEmpty(userIds)) {
            cri.where().andInList("pitcher_id", userIds);
        }
        if (StringUtils.isNotBlank(dto.getSourceSystem())) {
            cri.where().andEquals("source_system", dto.getSourceSystem());
        }
        if (dto.getBeginDate() != null && dto.getEndDate() != null) {
            cri.where().andBetween("dt", dto.getBeginDate(), dto.getEndDate());
        }
        //拼接排序条件，如果没有排序条件给默认值
        if (StringUtils.isBlank(dto.getSortType())) {
            dto.setSortType(OrderByEnum.DESC.getOrderType());
        }
        if (StringUtils.isBlank(dto.getSortFiled())) {
            cri.getOrderBy().orderBy("dt", dto.getSortType());
            cri.getOrderBy().orderBy("cost", dto.getSortType());
        } else {
            cri.getOrderBy().orderBy(CaseFormat.LOWER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, dto.getSortFiled()), dto.getSortType());
        }
        //Pager对象
        Pager pager = dao.createPager(dto.getPageNum(), dto.getPageSize());
        //sql语句
        Sql sql = Sqls.create(pitcherDataDaySql(tableName) + cri);
        //设置回传对象
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(PitcherDataDayVO.class));
        //设置pager
        sql.setPager(pager);
        //执行sql
        dao.execute(sql);
        //设置总记录数
        pager.setRecordCount(dao.count(clazz, cri));

        //查询用户id
        Map<String, String> map = findUsersIdForGameData(dto);

        //处理dayN数据
        List<PitcherDataDayVO> tempList = sql.getList(PitcherDataDayVO.class);
        //取出用户ids，制作响应vo
        List<PitcherDataDayVO> list = tempList.stream().map(vo -> {
            Long[] usersID = ArrayUtils.EMPTY_LONG_OBJECT_ARRAY;
            //将pitcherId和需要查询的注册时间拼接
            String pitcherIdDate = vo.getPitcherId() + "/" + vo.getDt();
            //取出所有usersId
            if (map.containsKey(pitcherIdDate)) {
                //string[] -> Long[]
                usersID = Arrays.stream(map.get(pitcherIdDate)
                                .split("/"))
                        .map(Long::parseLong)
                        .toArray(Long[]::new);
            }
            formatPitcherDataDayDayN(vo);

            FirstNewUserAgainTrendVO againTrendVO = FirstNewUserAgainTrendVO.builder()
                    .gameDimension(dto.getGameDimension())
                    .sourceSystem(dto.getSourceSystem())
                    .timeType("da1")
                    .rechargeCount(vo.getAmountD1Trend().getRechargeUserCount())
                    .usersId(usersID)
                    .build();
            vo.setUserDetails(againTrendVO);
            return vo;
        }).collect(Collectors.toList());
        //返回结果
        return new Page<>(list, pager);
    }


    /**
     * 查询出注册当天且充值的所有用户id-投手每日
     *
     * @param dto 前端查询参数
     * @return map
     */
    private Map findUsersIdForGameData(PitcherDataDayDTO dto) {
        //不选投手查询的结果 --> (投手id/日期, userId)

        //默认查询子游戏维度
        if (dto.getGameDimension() == null) {
            dto.setGameDimension(1L);
        }
        //默认查询字段
        String gameColumn = "pitcher_id";
        if (dto.getGameDimension() == 2L) {
            gameColumn = "pitcher_id";
        }
        //查询每个游戏的注册首日充值用户id,在玩家充值排行榜里查找
        Criteria findUsersCri = Cnd.cri();
        //拼接游戏ID
        if (CollectionUtils.isNotEmpty(dto.getPitcherId())) {
            findUsersCri.where().andInList(gameColumn, dto.getPitcherId());
        }
        //拼接SDK来源
        if (StringUtils.isNotBlank(dto.getSourceSystem())) {
            findUsersCri.where().andEquals("source_system", dto.getSourceSystem());
        }
        //拼接查询时间
        if (dto.getBeginDate() != null && dto.getEndDate() != null) {
            findUsersCri.where().andBetween("DATE(reg_user_time)", dto.getBeginDate(), dto.getEndDate());
        }
        findUsersCri.where().andNotEquals("reg_agent_id", 0);
        Sql findUsersIdSql;
        Criteria groupByCri = Cnd.cri();
        groupByCri.getGroupBy().groupBy("pitcher_id", "DATE(dt)");
        findUsersIdSql = Sqls.create(findUsersIdSql(findUsersCri, groupByCri));
        //自定义回传
        findUsersIdSql.setCallback((connection, resultSet, sql) -> {
            Map<String, String> tempMap = new HashMap<>();
            while (resultSet.next()) {
                tempMap.put(resultSet.getString(1), resultSet.getString(2));
            }
            return tempMap;
        });
        //运行sql
        dao.execute(findUsersIdSql);
        //返回结果
        return findUsersIdSql.getObject(Map.class);
    }

    /**
     * 找出每个游戏注册时间内的注册当天且充值的用户id-投手每日-子游戏维度 （不选投手）
     *
     * @return String
     */
    private String findUsersIdSql(Criteria cri, Criteria groupByCri) {
        return """
                SELECT
                    CONCAT(pitcher_id,'/',DATE(dt)) as game_id_date,
                    GROUP_CONCAT(CONVERT (player_id, varchar) SEPARATOR "/") as amount_users_id
                FROM
                    game_ads.ads_player_recharge_ranking
                """ + cri +
                """
                            and dt = DATE(reg_user_time) 
                        """ + groupByCri;
    }

    /**
     * 找出每个游戏注册时间内的注册当天且充值的用户id-投手每日-子游戏维度（选投手）
     *
     * @return String
     */
    private String findUsersIdSqlPitcher(Criteria cri, Criteria groupByCri) {
        return """
                SELECT
                    DATE(reg_user_time) as reg_user_time,
                    GROUP_CONCAT(CONVERT (player_id, varchar) SEPARATOR "/") as amount_users_id
                FROM
                    game_ads.ads_player_recharge_ranking
                """ + cri +
                """
                            and dt = DATE(reg_user_time) 
                        """ + groupByCri;
    }


    /**
     * 投手每日数据总计
     *
     * @param dto PitcherDataDayTotalDTO
     * @return PitcherDataDayTotalVO
     */
    @Override
    public PitcherDataDayTotalVO getPitcherDataDayTotal(PitcherDataDayTotalDTO dto) {
        com.github.sd4324530.jtuple.Tuple2<List<Long>, List<Long>> poerInfo = dataPowerComponent.getPowerInfo(dto.getSourceSystem());
        List<Long> userIds = CollectionUtils.isEmpty(dto.getPitcherId()) ? poerInfo.first : dto.getPitcherId();
//        List<Long> userIds = CollectionUtils.isEmpty(dto.getPitcherId()) ? null : dto.getPitcherId();
        //不传递时间，默认查询当天
        if (dto.getBeginDate() == null || dto.getEndDate() == null) {
            dto.setBeginDate(LocalDate.now());
            dto.setEndDate(LocalDate.now());
        }
        if (dto.getGameDimension() == null) {
            //默认查询子游戏维度数据
            dto.setGameDimension(1L);
        }
        String tableName = "game_ads.ads_pitcher_day";
        if (dto.getGameDimension() == 2L) {
            tableName = "game_ads_parent.ads_pitcher_day_parent";
        }
        //创建查询条件
        Criteria cri = Cnd.cri();
        if (userIds != null) {
            cri.where().andInList("pitcher_id", userIds);
        }
        if (StringUtils.isNotBlank(dto.getSourceSystem())) {
            cri.where().andEquals("source_system", dto.getSourceSystem());
        }
        if (dto.getBeginDate() != null && dto.getEndDate() != null) {
            cri.where().andBetween("dt", dto.getBeginDate(), dto.getEndDate());
        }
        //sql语句
        Sql sql = Sqls.create(pitcherDataDayTotalSql(tableName) + cri);
        //设置回传对象
        sql.setCallback(Sqls.callback.entity());
        sql.setEntity(dao.getEntity(PitcherDataDayTotalVO.class));
        //执行sql
        dao.execute(sql);
        PitcherDataDayTotalVO vo = sql.getObject(PitcherDataDayTotalVO.class);

        PitcherDataDayDTO pitcherDataDayDTO = new PitcherDataDayDTO();
        BeanUtils.copyProperties(dto, pitcherDataDayDTO);
        //查询用户id
        Map<String, String> map = findUsersIdForGameData(pitcherDataDayDTO);

        Long[] ids = ArrayUtils.EMPTY_LONG_OBJECT_ARRAY;

        String idStr = map.values().stream().filter(string -> !string.isEmpty()).collect(Collectors.joining("/"));
        if (StringUtils.isNotBlank(idStr)) {
            ids = Arrays.stream(idStr.split("/")).map(Long::parseLong).toArray(Long[]::new);
        }


        if (StringUtils.isNotBlank(vo.getAmountD1())) {
            //处理dayN数据
            formatPitcherDataDayTotalDayN(vo);

            FirstNewUserAgainTrendVO againTrendVO = FirstNewUserAgainTrendVO.builder()
                    .gameDimension(dto.getGameDimension())
                    .sourceSystem(dto.getSourceSystem())
                    .timeType("da1")
                    .rechargeCount(vo.getAmountD1Trend().getRechargeUserCount())
                    .usersId(ids)
                    .build();

            vo.setUserDetails(againTrendVO);
        }
        return vo;
    }

    /**
     * 投手总数据
     *
     * @param dto PitcherDataTotalDTO
     * @return Page<PitcherDataTotalVO>
     */
    @Override
    public Page<PitcherDataTotalVO> getPitcherDataTotal(PitcherDataTotalDTO dto) {
        com.github.sd4324530.jtuple.Tuple2<List<Long>, List<Long>> poerInfo = dataPowerComponent.getPowerInfo(dto.getSourceSystem());
        List<Long> userIds = CollectionUtils.isEmpty(dto.getPitcherId()) ? poerInfo.first : dto.getPitcherId();
//        List<Long> userIds = CollectionUtils.isEmpty(dto.getPitcherId()) ? null : dto.getPitcherId();
        //如果注册时间参数为空，默认设置查询当天数据
        if (dto.getBeginDate() == null || dto.getEndDate() == null) {
            dto.setBeginDate(LocalDate.now());
            dto.setEndDate(LocalDate.now());
        }
        //如果充值时间参数为空，默认设置查询当天数据
        if (dto.getAmountBeginDate() == null || dto.getAmountEndDate() == null) {
            dto.setAmountBeginDate(LocalDate.now());
            dto.setAmountEndDate(LocalDate.now());
        }
        if (dto.getGameDimension() == null) {
            //默认查询子游戏维度
            dto.setGameDimension(1L);
        }
        //主表查询条件
        Criteria criA = myCriPitcher(userIds, dto, "criA", true);
        //账面相关查询条件 时间条件只和充值时间相关
        Criteria criB = myCriPitcher(userIds, dto, "criB", true);
        //账面人数相关 时间只需要充值时间条件,不需要注册时间条件
        Criteria criAmount = myCriRechargePitcher(userIds, dto, false);
        //新用户充值人数和新用户复充人数 时间条件需要注册时间和充值时间
        Criteria criNewUser = myCriRechargePitcher(userIds, dto, true);
        //创角人数查询条件
        Criteria criRoleNum = myCriRolePitcher(userIds, dto);
        //拼接排序条件
        Criteria orderByCri = Cnd.cri();
        //如果没有排序条件给默认值
        if (StringUtils.isBlank(dto.getSortType())) {
            dto.setSortType(OrderByEnum.DESC.getOrderType());
        }
        if (StringUtils.isBlank(dto.getSortFiled())) {
            orderByCri.getOrderBy().orderBy("pitcher_id", dto.getSortType());
            orderByCri.getOrderBy().orderBy("cost", dto.getSortType());
        } else {
            orderByCri.getOrderBy().orderBy(CaseFormat.LOWER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, dto.getSortFiled()), dto.getSortType());
        }
        //创建sql语句 执行sql
        Sql sql;
        if (dto.getGameDimension() == 1L) {
            sql = Sqls.create(pitcherDataTotalSql(criA, criB, criAmount, criNewUser, criRoleNum) + orderByCri);
        } else {
            sql = Sqls.create(pitcherDataTotalSqlForParent(criA, criB, criAmount, criNewUser, criRoleNum) + orderByCri);
        }
        // 打印SQL
        System.out.println("Executing SQL: " + sql.getSourceSql());
        //设置自定义回显对象
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(PitcherDataTotalVO.class));
        Pager pager = dao.createPager(dto.getPageNum(), dto.getPageSize());
        sql.setPager(pager);
        //执行sql
        dao.execute(sql);
        //得到结果集list
        List<PitcherDataTotalVO> list = sql.getList(PitcherDataTotalVO.class);
        //设置查询总数
        Sql sqlCount;
        if (dto.getGameDimension() == 1L) {
            sqlCount = Sqls.queryEntity("select count(*) from game_ads.ads_pitcher_day" + criA);
        } else {
            sqlCount = Sqls.queryEntity("select count(*) from game_ads_parent.ads_pitcher_day_parent" + criA);
        }
        pager.setRecordCount((int) Daos.queryCount(dao, sqlCount));

        //获取所有筛选条件内的dayN数据
        Map<String, String> pitcherDataTotalDayNMap = tempDayNDataPitcher(dto, userIds);
        //根据充值时间修改值
        List<PitcherDataTotalVO> pitcherDataTotalVOList = list.stream().map(vo -> {
            //取到dayN数据
            String dayNStr = pitcherDataTotalDayNMap.get(vo.getPitcherId().toString());
            //如果为空或不存在
            if (StringUtils.isBlank(dayNStr)) {
                dayNStr = "0.00-0.00-0.00-0.00-0-0-0";
            }
            //新用户充值金额
            vo.setNewUserAmount(new BigDecimal(dayNStr.split("-")[0]));
            //支付宝充值金额
            vo.setAlipayAmount(new BigDecimal(dayNStr.split("-")[1]));
            //微信充值金额
            vo.setWxAmount(new BigDecimal(dayNStr.split("-")[2]));
            //米大师充值金额
            vo.setMiMasterAmount(new BigDecimal(dayNStr.split("-")[3]));
            //新用户充值次数
            vo.setNewUserAmountCount(Long.parseLong(dayNStr.split("-")[4]));
            //老用户充值人数、次数、金额
            vo.setOldAmount(vo.getAmount().subtract(vo.getNewUserAmount()));
            vo.setOldAmountCount(vo.getAmountCount() - vo.getNewUserAmountCount());
            vo.setOldAmountNum(vo.getAmountNum() - vo.getNewUserAmountNum());
            //新用户回收率
            vo.setNewUserRoi(vo.getCost().compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                    vo.getNewUserAmount().divide(vo.getCost(), 4, RoundingMode.HALF_UP));
            //新用户客单价
            vo.setNewUserAvg(vo.getNewUserAmountCount() == 0L ? BigDecimal.ZERO :
                    vo.getNewUserAmount().divide(BigDecimal.valueOf(vo.getNewUserAmountCount()), 4, RoundingMode.HALF_UP));
            //新用户ARPU
            vo.setNewUserArpu(vo.getNewUserAmountNum() == 0L ? BigDecimal.ZERO :
                    vo.getNewUserAmount().divide(BigDecimal.valueOf(vo.getNewUserAmountNum()), 4, RoundingMode.HALF_UP));

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

        //返回list结果封装到page对象里
        return new Page<>(pitcherDataTotalVOList, pager);
    }

    /**
     * 投手总数据总计
     *
     * @param dto PitcherDataTotalSumDTO
     * @return PitcherDataTotalSumVO
     */
    @Override
    public PitcherDataTotalSumVO getPitcherDataTotalSum(PitcherDataTotalSumDTO dto) {
        com.github.sd4324530.jtuple.Tuple2<List<Long>, List<Long>> poerInfo = dataPowerComponent.getPowerInfo(dto.getSourceSystem());
        List<Long> userIds = CollectionUtils.isEmpty(dto.getPitcherId()) ? poerInfo.first : dto.getPitcherId();
//        List<Long> userIds = dto.getPitcherId();
        //如果注册时间参数为空，默认设置查询当天数据
        if (dto.getBeginDate() == null || dto.getEndDate() == null) {
            dto.setBeginDate(LocalDate.now());
            dto.setEndDate(LocalDate.now());
        }
        //如果充值时间参数为空，默认设置查询当天数据
        if (dto.getAmountBeginDate() == null || dto.getAmountEndDate() == null) {
            dto.setAmountBeginDate(LocalDate.now());
            dto.setAmountEndDate(LocalDate.now());
        }
        if (dto.getGameDimension() == null) {
            //默认查询子游戏维度
            dto.setGameDimension(1L);
        }
        String tableName = "game_ads.ads_pitcher_day";
        if (dto.getGameDimension() == 2L) {
            tableName = "game_ads_parent.ads_pitcher_day_parent";
        }
        //复制PitcherDataTotalSumDTO 到PitcherDataTotalDTO
        PitcherDataTotalDTO totalDTO = new PitcherDataTotalDTO();
        copyNullProperties(dto, totalDTO);
        //主表查询条件
        Criteria criA = myCriPitcher(userIds, totalDTO, "criA", false);
        //账面相关查询条件 时间条件只和充值时间相关
        Criteria criB = myCriPitcher(userIds, totalDTO, "criB", false);
        //账面人数相关 时间只需要充值时间条件,不需要注册时间条件
        Criteria criAmount = myCriRechargePitcher(userIds, totalDTO, false);
        //新用户充值人数和新用户复充人数 时间条件需要注册时间和充值时间
        Criteria criNewUser = myCriRechargePitcher(userIds, totalDTO, true);
        //创角人数查询条件
        Criteria criRoleNum = myCriRolePitcher(userIds, totalDTO);
        //创建sql语句 执行sql
        Sql sql = Sqls.create(pitcherDataTotalSumSql(tableName) + criA);
        //设置自定义回显对象
        sql.setCallback(Sqls.callback.entity());
        sql.setEntity(dao.getEntity(PitcherDataTotalSumVO.class));
        //执行sql
        dao.execute(sql);
        // 打印SQL
        System.out.println("Executing SQL: " + sql.getSourceSql());
        //得到结果
        PitcherDataTotalSumVO vo = sql.getObject(PitcherDataTotalSumVO.class);
        //得到账面相关数据、账面人数、新用户人数、新用户复充人数、创角人数数据
        PitcherDataTotalSumVO tempVO = getPitcherDataTotalSumAmountData(dto, criB, criAmount, criNewUser, criRoleNum);
        //合并两个对象
        copyNullProperties(tempVO, vo);

        //计算相关指标
        //新用户充值成本
        vo.setNewUserAmountCost(vo.getNewUserAmountNum() == 0L ? BigDecimal.ZERO :
                vo.getCost().divide(BigDecimal.valueOf(vo.getNewUserAmountNum()), 2, RoundingMode.HALF_UP));
        //新用户付费率
        vo.setNewUserAmountRate(vo.getRegisterNum() == 0L ? BigDecimal.ZERO :
                BigDecimal.valueOf(vo.getNewUserAmountNum().doubleValue() / vo.getRegisterNum().doubleValue()).setScale(4, RoundingMode.HALF_UP));
        //新用户付费比
        vo.setNewUserAmountRatio(vo.getAmountNum() == 0L ? BigDecimal.ZERO :
                BigDecimal.valueOf(vo.getNewUserAmountNum().doubleValue() / vo.getAmountNum().doubleValue()).setScale(4, RoundingMode.HALF_UP));
        //新用户复充率
        vo.setNewUserAgainRate(vo.getNewUserTotalAmountNum() == 0L ? BigDecimal.ZERO :
                BigDecimal.valueOf(vo.getNewUserOrderAgain().doubleValue() / vo.getNewUserTotalAmountNum().doubleValue()).setScale(4, RoundingMode.HALF_UP));
        //创角率
        vo.setRoleNumRate(vo.getRegisterNum() == 0L ? BigDecimal.ZERO :
                BigDecimal.valueOf(vo.getRoleNum().doubleValue() / vo.getRegisterNum().doubleValue()).setScale(4, RoundingMode.HALF_UP));
        //创角成本
        vo.setRoleNumCost(vo.getRoleNum() == 0L ? BigDecimal.ZERO :
                vo.getCost().divide(BigDecimal.valueOf(vo.getRoleNum()), 2, RoundingMode.HALF_UP));

        //获取所有筛选条件内的dayN数据
        String dayNStr = tempDayNDataTotalPitcher(totalDTO, userIds);
        //新用户充值金额
        vo.setNewUserAmount(new BigDecimal(dayNStr.split("-")[0]));
        //支付宝充值金额
        vo.setAlipayAmount(new BigDecimal(dayNStr.split("-")[1]));
        //微信充值金额
        vo.setWxAmount(new BigDecimal(dayNStr.split("-")[2]));
        //米大师充值金额
        vo.setMiMasterAmount(new BigDecimal(dayNStr.split("-")[3]));
        //新用户充值次数
        vo.setNewUserAmountCount(Long.parseLong(dayNStr.split("-")[4]));
        //老用户充值人数、次数、金额
        vo.setOldAmount(vo.getAmount().subtract(vo.getNewUserAmount()));
        vo.setOldAmountCount(vo.getAmountCount() - vo.getNewUserAmountCount());
        vo.setOldAmountNum(vo.getAmountNum() - vo.getNewUserAmountNum());
        //新用户回收率
        vo.setNewUserRoi(vo.getCost().compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                vo.getNewUserAmount().divide(vo.getCost(), 4, RoundingMode.HALF_UP));
        //新用户客单价
        vo.setNewUserAvg(vo.getNewUserAmountCount() == 0L ? BigDecimal.ZERO :
                vo.getNewUserAmount().divide(BigDecimal.valueOf(vo.getNewUserAmountCount()), 4, RoundingMode.HALF_UP));
        //新用户ARPU
        vo.setNewUserArpu(vo.getNewUserAmountNum() == 0L ? BigDecimal.ZERO :
                vo.getNewUserAmount().divide(BigDecimal.valueOf(vo.getNewUserAmountNum()), 4, RoundingMode.HALF_UP));

        //结果
        return vo;
    }

    /**
     * 投手游戏每日数据
     *
     * @param dto PitcherGameDataDayDTO
     * @return Page<PitcherGameDataDayVO>
     */
    @Override
    public Page<PitcherGameDataDayVO> getPitcherGameDataDay(PitcherGameDataDayDTO dto) {
        //默认查询子游戏维度数据
        if (dto.getGameDimension() == null) {
            dto.setGameDimension(1L);
        }
        if (dto.getGameDimension() == 1L) {
            //子游戏维度
            return getPitcherGameDataDayForSon(dto);
        } else {
            return getPitcherGameDataDayForParent(dto);
        }
    }

    /**
     * 投手游戏每日数据-子游戏维度
     *
     * @param dto PitcherGameDataDayDTO
     * @return Page<PitcherGameDataDayVO>
     */
    private Page<PitcherGameDataDayVO> getPitcherGameDataDayForSon(PitcherGameDataDayDTO dto) {
        com.github.sd4324530.jtuple.Tuple2<List<Long>, List<Long>> poerInfo = dataPowerComponent.getPowerInfo(dto.getSourceSystem());
        List<Long> userIds = CollectionUtils.isEmpty(dto.getPitcherId()) ? poerInfo.first : dto.getPitcherId();
        List<Long> gameIds = CollectionUtils.isEmpty(dto.getGameId()) ? poerInfo.second : dto.getGameId();

        //不传递时间，默认查询当天
        if (dto.getBeginDate() == null || dto.getEndDate() == null) {
            dto.setBeginDate(LocalDate.now());
            dto.setEndDate(LocalDate.now());
        }
        //创建查询条件
        Criteria cri = Cnd.cri();
        if (CollectionUtils.isNotEmpty(userIds)) {
            cri.where().andInList("pitcher_id", userIds);
        }
        if (CollectionUtils.isNotEmpty(gameIds)) {
            cri.where().andInList("game_id", gameIds);
        }
        if (StringUtils.isNotBlank(dto.getSourceSystem())) {
            cri.where().andEquals("source_system", dto.getSourceSystem());
        }
        if (dto.getBeginDate() != null && dto.getEndDate() != null) {
            cri.where().andBetween("dt", dto.getBeginDate(), dto.getEndDate());
        }
        if (StringUtils.isNotBlank(dto.getGameCp())) {
            cri.where().andEquals("game_cp", dto.getGameCp());
        }
        if (dto.getGameType() != null) {
            cri.where().andEquals("game_type", dto.getGameType());
        }
        if (CollectionUtils.isNotEmpty(dto.getGameId())) {
            //有查询的游戏，将游戏合并为一条
            cri.getGroupBy().groupBy("dt", "source_system", "pitcher_id");
        } else {
            //不传递查询的游戏，默认分条展示
            cri.getGroupBy().groupBy("dt", "source_system", "pitcher_id", "game_id");
        }
        //排序条件
        //拼接排序条件，如果没有排序条件给默认值
        if (StringUtils.isBlank(dto.getSortType())) {
            dto.setSortType(OrderByEnum.DESC.getOrderType());
        }
        if (StringUtils.isBlank(dto.getSortFiled())) {
            cri.getOrderBy().orderBy("dt", dto.getSortType());
            cri.getOrderBy().orderBy("cost", dto.getSortType());
        } else {
            cri.getOrderBy().orderBy(CaseFormat.LOWER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, dto.getSortFiled()), dto.getSortType());
        }
        //Pager对象
        Pager pager = dao.createPager(dto.getPageNum(), dto.getPageSize());
        //sql语句
        Sql sql = Sqls.create(pitcherGameDataDaySqlForSon() + cri);
        //设置回传对象
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(PitcherGameDataDayVO.class));
        //设置pager
        sql.setPager(pager);
        //执行sql
        dao.execute(sql);
        //设置总记录数
        Sql sqlCount = Sqls.create(
                """
                            SELECT
                            	COUNT(1)
                            FROM (
                            	SELECT
                            		COUNT(*)
                            	FROM game_ads.ads_game_pitcher_day
                        """ + cri +
                        """
                                    ) a
                                """);
        sqlCount.setCallback(Sqls.callback.integer());
        dao.execute(sqlCount);
        pager.setRecordCount(sqlCount.getInt());
        //处理dayN数据
        List<PitcherGameDataDayVO> tempList = sql.getList(PitcherGameDataDayVO.class);
        List<PitcherGameDataDayVO> list = tempList.stream().map(vo -> {
            formatPitcherGameDataDayDayNForSon(vo);
            return vo;
        }).collect(Collectors.toList());
        //返回结果
        return new Page<>(list, pager);
    }

    /**
     * 投手游戏每日数据-父游戏维度
     *
     * @param dto PitcherGameDataDayDTO
     * @return Page<PitcherGameDataDayVO>
     */
    private Page<PitcherGameDataDayVO> getPitcherGameDataDayForParent(PitcherGameDataDayDTO dto) {
        com.github.sd4324530.jtuple.Tuple2<List<Long>, List<Long>> poerInfo = dataPowerComponent.getPowerInfo(dto.getSourceSystem());
        List<Long> userIds = CollectionUtils.isEmpty(dto.getPitcherId()) ? poerInfo.first : dto.getPitcherId();
        List<Long> gameIds = CollectionUtils.isEmpty(dto.getGameId()) ? poerInfo.second : dto.getGameId();

        //不传递时间，默认查询当天
        if (dto.getBeginDate() == null || dto.getEndDate() == null) {
            dto.setBeginDate(LocalDate.now());
            dto.setEndDate(LocalDate.now());
        }
        //创建查询条件
        Criteria cri = Cnd.cri();
        if (CollectionUtils.isNotEmpty(userIds)) {
            cri.where().andInList("pitcher_id", userIds);
        }
        if (CollectionUtils.isNotEmpty(gameIds)) {
            cri.where().andInList("parent_game_id", gameIds);
        }
        if (StringUtils.isNotBlank(dto.getSourceSystem())) {
            cri.where().andEquals("source_system", dto.getSourceSystem());
        }
        if (dto.getBeginDate() != null && dto.getEndDate() != null) {
            cri.where().andBetween("dt", dto.getBeginDate(), dto.getEndDate());
        }
        if (StringUtils.isNotBlank(dto.getGameCp())) {
            cri.where().andEquals("parent_game_cp", dto.getGameCp());
        }
        if (dto.getGameType() != null) {
            cri.where().andEquals("parent_game_type", dto.getGameType());
        }
        if (CollectionUtils.isNotEmpty(dto.getGameId())) {
            //有查询的游戏，将游戏合并为一条
            cri.getGroupBy().groupBy("dt", "source_system", "pitcher_id");
        } else {
            //不传递查询的游戏，默认分条展示
            cri.getGroupBy().groupBy("dt", "source_system", "pitcher_id", "parent_game_id");
        }
        //排序条件
        //拼接排序条件，如果没有排序条件给默认值
        if (StringUtils.isBlank(dto.getSortType())) {
            dto.setSortType(OrderByEnum.DESC.getOrderType());
        }
        if (StringUtils.isBlank(dto.getSortFiled())) {
            cri.getOrderBy().orderBy("dt", dto.getSortType());
            cri.getOrderBy().orderBy("cost", dto.getSortType());
        } else {
            cri.getOrderBy().orderBy(CaseFormat.LOWER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, dto.getSortFiled()), dto.getSortType());
        }
        //Pager对象
        Pager pager = dao.createPager(dto.getPageNum(), dto.getPageSize());
        //sql语句
        Sql sql = Sqls.create(pitcherGameDataDaySqlForParent() + cri);
        //设置回传对象
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(PitcherGameDataDayVO.class));
        //设置pager
        sql.setPager(pager);
        //执行sql
        dao.execute(sql);
        //设置总记录数
        Sql sqlCount = Sqls.create(
                """
                            SELECT
                            	COUNT(1)
                            FROM (
                            	SELECT
                            		COUNT(*)
                            	FROM game_ads_parent.ads_game_pitcher_day_parent
                        """ + cri +
                        """
                                    ) a
                                """);
        sqlCount.setCallback(Sqls.callback.integer());
        dao.execute(sqlCount);
        pager.setRecordCount(sqlCount.getInt());
        //处理dayN数据
        List<PitcherGameDataDayVO> tempList = sql.getList(PitcherGameDataDayVO.class);
        List<PitcherGameDataDayVO> list = tempList.stream().map(vo -> {
            formatPitcherGameDataDayDayNForSon(vo);
            return vo;
        }).collect(Collectors.toList());
        //返回结果
        return new Page<>(list, pager);
    }

    /**
     * 投手游戏每日数据总计
     *
     * @param dto PitcherGameDataDayTotalDTO
     * @return PitcherGameDataDayTotalVO
     */
    @Override
    public PitcherGameDataDayTotalVO getPitcherGameDataDayTotal(PitcherGameDataDayTotalDTO dto) {
        com.github.sd4324530.jtuple.Tuple2<List<Long>, List<Long>> poerInfo = dataPowerComponent.getPowerInfo(dto.getSourceSystem());
        List<Long> userIds = CollectionUtils.isEmpty(dto.getPitcherId()) ? poerInfo.first : dto.getPitcherId();
        List<Long> gameIds = CollectionUtils.isEmpty(dto.getGameId()) ? poerInfo.second : dto.getGameId();
        //不传递时间，默认查询当天
        if (dto.getBeginDate() == null || dto.getEndDate() == null) {
            dto.setBeginDate(LocalDate.now());
            dto.setEndDate(LocalDate.now());
        }
        if (dto.getGameDimension() == null) {
            //默认查询子游戏维度
            dto.setGameDimension(1L);
        }
        String gameColumn = "game_id";
        String gameCpColumn = "game_cp";
        String gameTypeColumn = "game_type";
        String tableName = "game_ads.ads_game_pitcher_day";
        if (dto.getGameDimension() == 2L) {
            gameColumn = "parent_game_id";
            gameCpColumn = "parent_game_cp";
            gameTypeColumn = "parent_game_type";
            tableName = "game_ads_parent.ads_game_pitcher_day_parent";
        }
        //创建查询条件
        Criteria cri = Cnd.cri();
        if (CollectionUtils.isNotEmpty(userIds)) {
            cri.where().andInList("pitcher_id", userIds);
        }
        if (CollectionUtils.isNotEmpty(gameIds)) {
            cri.where().andInList(gameColumn, gameIds);
        }
        if (StringUtils.isNotBlank(dto.getSourceSystem())) {
            cri.where().andEquals("source_system", dto.getSourceSystem());
        }
        if (dto.getBeginDate() != null && dto.getEndDate() != null) {
            cri.where().andBetween("dt", dto.getBeginDate(), dto.getEndDate());
        }
        if (StringUtils.isNotBlank(dto.getGameCp())) {
            cri.where().andEquals(gameCpColumn, dto.getGameCp());
        }
        if (dto.getGameType() != null) {
            cri.where().andEquals(gameTypeColumn, dto.getGameType());
        }
        //sql语句
        Sql sql = Sqls.create(pitcherGameDataDayTotalSql(tableName) + cri);
        //设置回传对象
        sql.setCallback(Sqls.callback.entity());
        sql.setEntity(dao.getEntity(PitcherGameDataDayTotalVO.class));
        //执行sql
        dao.execute(sql);
        PitcherGameDataDayTotalVO vo = sql.getObject(PitcherGameDataDayTotalVO.class);
        if (StringUtils.isNotBlank(vo.getAmountD1())) {
            //处理dayN数据
            formatPitcherGameDataDayTotalDayN(vo);
        }

        return vo;
    }

    /**
     * 投手游戏总数据
     *
     * @param dto PitcherGameDataTotalDTO
     * @return Page<PitcherGameDataTotalVO>
     */
    @Override
    public Page<PitcherGameDataTotalVO> getPitcherGameDataTotal(PitcherGameDataTotalDTO dto) {
        com.github.sd4324530.jtuple.Tuple2<List<Long>, List<Long>> poerInfo = dataPowerComponent.getPowerInfo(dto.getSourceSystem());
        List<Long> userIds = CollectionUtils.isEmpty(dto.getPitcherId()) ? poerInfo.first : dto.getPitcherId();
        List<Long> gameIds = CollectionUtils.isEmpty(dto.getGameId()) ? poerInfo.second : dto.getGameId();
        //如果注册时间参数为空，默认设置查询当天数据
        if (dto.getBeginDate() == null || dto.getEndDate() == null) {
            dto.setBeginDate(LocalDate.now());
            dto.setEndDate(LocalDate.now());
        }
        //如果充值时间参数为空，默认设置查询当天数据
        if (dto.getAmountBeginDate() == null || dto.getAmountEndDate() == null) {
            dto.setAmountBeginDate(LocalDate.now());
            dto.setAmountEndDate(LocalDate.now());
        }
        if (dto.getGameDimension() == null) {
            //默认查询子游戏维度
            dto.setGameDimension(1L);
        }
        //主表查询条件
        Criteria criA = myCri(userIds, gameIds, dto, "criA", true);
        //账面相关查询条件 时间条件只和充值时间相关
        Criteria criB = myCri(userIds, gameIds, dto, "criB", true);
        //账面人数相关 时间只需要充值时间条件,不需要注册时间条件
        Criteria criAmount = myCriRecharge(userIds, gameIds, dto, false);
        //新用户充值人数和新用户复充人数 时间条件需要注册时间和充值时间
        Criteria criNewUser = myCriRecharge(userIds, gameIds, dto, true);
        //创角人数查询条件
        Criteria criRoleNum = myCriRole(userIds, gameIds, dto);
        //拼接排序条件
        Criteria orderByCri = Cnd.cri();
        //如果没有排序条件给默认值
        if (StringUtils.isBlank(dto.getSortType())) {
            dto.setSortType(OrderByEnum.DESC.getOrderType());
        }
        if (StringUtils.isBlank(dto.getSortFiled())) {
            orderByCri.getOrderBy().orderBy("pitcher_id", dto.getSortType());
            orderByCri.getOrderBy().orderBy("cost", dto.getSortType());
        } else {
            orderByCri.getOrderBy().orderBy(CaseFormat.LOWER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, dto.getSortFiled()), dto.getSortType());
        }
        //创建sql语句 执行sql
        Sql sql;
        if (dto.getGameDimension() == 1L) {
            sql = Sqls.create(pitcherGameDataTotalSql(criA, criB, criAmount, criNewUser, criRoleNum) + orderByCri);
        } else {
            sql = Sqls.create(pitcherGameDataTotalSqlForParent(criA, criB, criAmount, criNewUser, criRoleNum) + orderByCri);
        }
        //设置自定义回显对象
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(PitcherGameDataTotalVO.class));
        Pager pager = dao.createPager(dto.getPageNum(), dto.getPageSize());
        sql.setPager(pager);
        //执行sql
        dao.execute(sql);
        //得到结果集list
        List<PitcherGameDataTotalVO> list = sql.getList(PitcherGameDataTotalVO.class);
        //设置查询总数
        Sql sqlCount;
        if (dto.getGameDimension() == 1L) {
            sqlCount = Sqls.queryEntity("select count(*) from game_ads.ads_game_pitcher_day" + criA);
        } else {
            sqlCount = Sqls.queryEntity("select count(*) from game_ads_parent.ads_game_pitcher_day_parent" + criA);
        }
        pager.setRecordCount((int) Daos.queryCount(dao, sqlCount));

        //获取所有筛选条件内的dayN数据
        Map<String, Map<String, String>> pitcherGameDataTotalDayNMap = tempDayNData(dto, userIds, gameIds);
        //根据充值时间修改值
        List<PitcherGameDataTotalVO> pitcherGameDataTotalVOList = list.stream().map(vo -> {
            //包含pitcher下所有的游戏的dayn数据
            Map<String, String> gameIDToDayNData = pitcherGameDataTotalDayNMap.get(vo.getPitcherId().toString());
            //取到dayN数据
            String dayNStr;
            if (MapUtils.isEmpty(gameIDToDayNData)) {
                dayNStr = "0.00-0";
            } else {
                dayNStr = gameIDToDayNData.get(vo.getGameId());
            }
            if (StringUtils.isBlank(dayNStr)) {
                dayNStr = "0.00-0";
            }
            //新用户充值金额
            vo.setNewUserAmount(new BigDecimal(dayNStr.split("-")[0]));
            //新用户充值次数
            vo.setNewUserAmountCount(Long.parseLong(dayNStr.split("-")[1]));
            //老用户充值人数、次数、金额
            vo.setOldAmount(vo.getAmount().subtract(vo.getNewUserAmount()));
            vo.setOldAmountCount(vo.getAmountCount() - vo.getNewUserAmountCount());
            vo.setOldAmountNum(vo.getAmountNum() - vo.getNewUserAmountNum());
            //新用户回收率
            vo.setNewUserRoi(vo.getCost().compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                    vo.getNewUserAmount().divide(vo.getCost(), 4, RoundingMode.HALF_UP));
            //新用户客单价
            vo.setNewUserAvg(vo.getNewUserAmountCount() == 0L ? BigDecimal.ZERO :
                    vo.getNewUserAmount().divide(BigDecimal.valueOf(vo.getNewUserAmountCount()), 4, RoundingMode.HALF_UP));
            //新用户ARPU
            vo.setNewUserArpu(vo.getNewUserAmountNum() == 0L ? BigDecimal.ZERO :
                    vo.getNewUserAmount().divide(BigDecimal.valueOf(vo.getNewUserAmountNum()), 4, RoundingMode.HALF_UP));

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

        //返回list结果封装到page对象里
        return new Page<>(pitcherGameDataTotalVOList, pager);
    }

    /**
     * 投手游戏总数据总计
     *
     * @param dto PitcherGameDataTotalSumDTO
     * @return PitcherGameDataTotalSumVO
     */
    @Override
    public PitcherGameDataTotalSumVO getPitcherGameDataTotalSum(PitcherGameDataTotalSumDTO dto) {
        com.github.sd4324530.jtuple.Tuple2<List<Long>, List<Long>> poerInfo = dataPowerComponent.getPowerInfo(dto.getSourceSystem());
        List<Long> userIds = CollectionUtils.isEmpty(dto.getPitcherId()) ? poerInfo.first : dto.getPitcherId();
        List<Long> gameIds = CollectionUtils.isEmpty(dto.getGameId()) ? poerInfo.second : dto.getGameId();
        //如果注册时间参数为空，默认设置查询当天数据
        if (dto.getBeginDate() == null || dto.getEndDate() == null) {
            dto.setBeginDate(LocalDate.now());
            dto.setEndDate(LocalDate.now());
        }
        //如果充值时间参数为空，默认设置查询当天数据
        if (dto.getAmountBeginDate() == null || dto.getAmountEndDate() == null) {
            dto.setAmountBeginDate(LocalDate.now());
            dto.setAmountEndDate(LocalDate.now());
        }
        if (dto.getGameDimension() == null) {
            //默认查询子游戏维度
            dto.setGameDimension(1L);
        }
        String tableName = "game_ads.ads_game_pitcher_day";
        if (dto.getGameDimension() == 2L) {
            tableName = "game_ads_parent.ads_game_pitcher_day_parent";
        }
        //复制PitcherGameDataTotalSumDTO 到PitcherGameDataTotalDTO
        PitcherGameDataTotalDTO totalDTO = new PitcherGameDataTotalDTO();
        copyNullProperties(dto, totalDTO);
        //主表查询条件
        Criteria criA = myCri(userIds, gameIds, totalDTO, "criA", false);
        //账面相关查询条件 时间条件只和充值时间相关
        Criteria criB = myCri(userIds, gameIds, totalDTO, "criB", false);
        //账面人数相关 时间只需要充值时间条件,不需要注册时间条件
        Criteria criAmount = myCriRecharge(userIds, gameIds, totalDTO, false);
        //新用户充值人数和新用户复充人数 时间条件需要注册时间和充值时间
        Criteria criNewUser = myCriRecharge(userIds, gameIds, totalDTO, true);
        //创角人数查询条件
        Criteria criRoleNum = myCriRole(userIds, gameIds, totalDTO);
        //创建sql语句 执行sql
        Sql sql = Sqls.create(pitcherGameDataTotalSumSql(tableName) + criA);
        //设置自定义回显对象
        sql.setCallback(Sqls.callback.entity());
        sql.setEntity(dao.getEntity(PitcherGameDataTotalSumVO.class));
        //执行sql
        dao.execute(sql);
        //得到结果
        PitcherGameDataTotalSumVO vo = sql.getObject(PitcherGameDataTotalSumVO.class);
        //得到账面相关数据、账面人数、新用户人数、新用户复充人数、创角人数数据
        PitcherGameDataTotalSumVO tempVO = getPitcherGameDataTotalSumAmountData(dto, criB, criAmount, criNewUser, criRoleNum);
        //合并两个对象
        copyNullProperties(tempVO, vo);

        //计算相关指标
        //新用户充值成本
        vo.setNewUserAmountCost(vo.getNewUserAmountNum() == 0L ? BigDecimal.ZERO :
                vo.getCost().divide(BigDecimal.valueOf(vo.getNewUserAmountNum()), 2, RoundingMode.HALF_UP));
        //新用户付费率
        vo.setNewUserAmountRate(vo.getRegisterNum() == 0L ? BigDecimal.ZERO :
                BigDecimal.valueOf(vo.getNewUserAmountNum().doubleValue() / vo.getRegisterNum().doubleValue()).setScale(4, RoundingMode.HALF_UP));
        //新用户付费比
        vo.setNewUserAmountRatio(vo.getAmountNum() == 0L ? BigDecimal.ZERO :
                BigDecimal.valueOf(vo.getNewUserAmountNum().doubleValue() / vo.getAmountNum().doubleValue()).setScale(4, RoundingMode.HALF_UP));
        //新用户复充率
        vo.setNewUserAgainRate(vo.getNewUserTotalAmountNum() == 0L ? BigDecimal.ZERO :
                BigDecimal.valueOf(vo.getNewUserOrderAgain().doubleValue() / vo.getNewUserTotalAmountNum().doubleValue()).setScale(4, RoundingMode.HALF_UP));
        //创角率
        vo.setRoleNumRate(vo.getRegisterNum() == 0L ? BigDecimal.ZERO :
                BigDecimal.valueOf(vo.getRoleNum().doubleValue() / vo.getRegisterNum().doubleValue()).setScale(4, RoundingMode.HALF_UP));
        //创角成本
        vo.setRoleNumCost(vo.getRoleNum() == 0L ? BigDecimal.ZERO :
                vo.getCost().divide(BigDecimal.valueOf(vo.getRoleNum()), 2, RoundingMode.HALF_UP));

        //获取所有筛选条件内的dayN数据
        String dayNStr = tempDayNDataTotal(totalDTO, userIds, gameIds);
        //新用户充值金额
        vo.setNewUserAmount(new BigDecimal(dayNStr.split("-")[0]));
        //新用户充值次数
        vo.setNewUserAmountCount(Long.parseLong(dayNStr.split("-")[1]));
        //老用户充值人数、次数、金额
        vo.setOldAmount(vo.getAmount().subtract(vo.getNewUserAmount()));
        vo.setOldAmountCount(vo.getAmountCount() - vo.getNewUserAmountCount());
        vo.setOldAmountNum(vo.getAmountNum() - vo.getNewUserAmountNum());
        //新用户回收率
        vo.setNewUserRoi(vo.getCost().compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                vo.getNewUserAmount().divide(vo.getCost(), 4, RoundingMode.HALF_UP));
        //新用户客单价
        vo.setNewUserAvg(vo.getNewUserAmountCount() == 0L ? BigDecimal.ZERO :
                vo.getNewUserAmount().divide(BigDecimal.valueOf(vo.getNewUserAmountCount()), 4, RoundingMode.HALF_UP));
        //新用户ARPU
        vo.setNewUserArpu(vo.getNewUserAmountNum() == 0L ? BigDecimal.ZERO :
                vo.getNewUserAmount().divide(BigDecimal.valueOf(vo.getNewUserAmountNum()), 4, RoundingMode.HALF_UP));

        //结果
        return vo;
    }

    /**
     * 得到账面相关数据、账面人数、新用户人数、新用户复充人数、创角人数数据（投手总数据总计）
     *
     * @param criAmount 账面数据相关查询条件
     * @return PitcherDataTotalSumVO
     */
    private PitcherDataTotalSumVO getPitcherDataTotalSumAmountData(PitcherDataTotalSumDTO dto, Criteria criB, Criteria criAmount, Criteria criNewUser, Criteria criRoleNum) {
        String tableName = "game_ads.ads_pitcher_day";
        if (dto.getGameDimension() == 2L) {
            tableName = "game_ads_parent.ads_pitcher_day_parent";
        }
        //得到账面相关数据
        Sql sqlAmountData = Sqls.create("""
                SELECT
                    IFNULL(SUM(amount), 0) as amount,
                    IFNULL(SUM(amount_count), 0) as amount_count,
                    ROUND(IF(SUM(amount_count) > 0 , SUM(amount) / SUM(amount_count), 0), 2) as paper_avg
                FROM 
                """ + tableName + criB);
        //设置回传对象
        sqlAmountData.setCallback(Sqls.callback.entity());
        sqlAmountData.setEntity(dao.getEntity(PitcherDataTotalSumVO.class));
        //执行sql
        dao.execute(sqlAmountData);
        PitcherDataTotalSumVO vo = sqlAmountData.getObject(PitcherDataTotalSumVO.class);

        //账面人数数据
        Sql sqlAmountNum = Sqls.create(
                """
                        SELECT
                            IFNULL(COUNT(DISTINCT user_id), 0) amount_num
                        FROM
                            game_ads.ads_information
                        """ + criAmount +
                        """
                                AND NOT agent_id = 0
                                """);
        //设置回传对象
        sqlAmountNum.setCallback(Sqls.callback.longValue());
        //执行sql
        dao.execute(sqlAmountNum);
        //设置账面充值人数
        vo.setAmountNum(sqlAmountNum.getLong());
        //账面ARPU
        vo.setPaperArpu(vo.getAmountNum() == 0L ? BigDecimal.ZERO :
                vo.getAmount().divide(BigDecimal.valueOf(vo.getAmountNum()), 2, RoundingMode.HALF_UP));

        //新用户充值人数
        Sql sqlNewUserNum = Sqls.create(
                """
                        SELECT
                            COUNT(DISTINCT user_id) new_user_amount_num
                        FROM
                            game_ads.ads_information
                        """ + criNewUser +
                        """
                                AND NOT agent_id = 0
                                """);
        //设置回传对象
        sqlNewUserNum.setCallback(Sqls.callback.longValue());
        //执行sql
        dao.execute(sqlNewUserNum);
        //设置新用户充值人数
        vo.setNewUserAmountNum(sqlNewUserNum.getLong());

        //新用户复充人数
        Sql sqlNewUserAgainNum = Sqls.create(
                """
                        SELECT
                            COUNT(tempA.num) as new_user_order_again
                        FROM (
                            SELECT
                                COUNT(user_id) num
                            FROM
                                game_ads.ads_information
                        """ + criNewUser +
                        """
                                    AND NOT agent_id = 0
                                    GROUP BY user_id
                                    HAVING
                                        COUNT(user_id) > 1 ) tempA
                                """);
        //设置回传对象
        sqlNewUserAgainNum.setCallback(Sqls.callback.longValue());
        //执行sql
        dao.execute(sqlNewUserAgainNum);
        //设置新用户充值人数
        vo.setNewUserOrderAgain(sqlNewUserAgainNum.getLong());

        //创角人数
        Sql sqlRoleNum = Sqls.create(
                """
                        SELECT
                            COUNT(DISTINCT role_user_id) as role_num
                        FROM
                            dw_create_role_detail
                        """ + criRoleNum +
                        """
                                AND NOT user_agent_id = 0
                                """);
        //设置回传对象
        sqlRoleNum.setCallback(Sqls.callback.longValue());
        //执行sql
        dao.execute(sqlRoleNum);
        //设置创角人数
        vo.setRoleNum(sqlRoleNum.getLong());

        return vo;
    }

    /**
     * 得到账面相关数据、账面人数、新用户人数、新用户复充人数、创角人数数据（投手游戏总数据总计）
     *
     * @param criAmount 账面数据相关查询条件
     * @return PitcherGameDataTotalSumVO
     */
    private PitcherGameDataTotalSumVO getPitcherGameDataTotalSumAmountData(PitcherGameDataTotalSumDTO dto, Criteria criB, Criteria criAmount, Criteria criNewUser, Criteria criRoleNum) {
        String tableName = "game_ads.ads_game_pitcher_day";
        if (dto.getGameDimension() == 2L) {
            tableName = "game_ads_parent.ads_game_pitcher_day_parent";
        }
        //得到账面相关数据
        Sql sqlAmountData = Sqls.create("""
                SELECT
                    IFNULL(SUM(amount), 0) as amount,
                    IFNULL(SUM(amount_count), 0) as amount_count,
                    ROUND(IF(SUM(amount_count) > 0 , SUM(amount) / SUM(amount_count), 0), 2) as paper_avg
                FROM 
                """ + tableName + criB);
        //设置回传对象
        sqlAmountData.setCallback(Sqls.callback.entity());
        sqlAmountData.setEntity(dao.getEntity(PitcherGameDataTotalSumVO.class));
        //执行sql
        dao.execute(sqlAmountData);
        PitcherGameDataTotalSumVO vo = sqlAmountData.getObject(PitcherGameDataTotalSumVO.class);

        //账面人数数据
        Sql sqlAmountNum = Sqls.create("""
                SELECT
                    IFNULL(COUNT(DISTINCT user_id), 0) amount_num
                FROM
                    game_ads.ads_information
                """ + criAmount +
                """
                        AND NOT agent_id = 0
                        """);
        //设置回传对象
        sqlAmountNum.setCallback(Sqls.callback.longValue());
        //执行sql
        dao.execute(sqlAmountNum);
        //设置账面充值人数
        vo.setAmountNum(sqlAmountNum.getLong());
        //账面ARPU
        vo.setPaperArpu(vo.getAmountNum() == 0L ? BigDecimal.ZERO :
                vo.getAmount().divide(BigDecimal.valueOf(vo.getAmountNum()), 2, RoundingMode.HALF_UP));

        //新用户充值人数
        Sql sqlNewUserNum = Sqls.create("""
                SELECT
                    COUNT(DISTINCT user_id) new_user_amount_num
                FROM
                    game_ads.ads_information
                """ + criNewUser +
                """
                        AND NOT agent_id = 0
                        """);
        //设置回传对象
        sqlNewUserNum.setCallback(Sqls.callback.longValue());
        //执行sql
        dao.execute(sqlNewUserNum);
        //设置新用户充值人数
        vo.setNewUserAmountNum(sqlNewUserNum.getLong());

        //新用户复充人数
        Sql sqlNewUserAgainNum = Sqls.create("""
                SELECT
                    COUNT(tempA.num) as new_user_order_again
                FROM (
                    SELECT
                        COUNT(user_id) num
                    FROM
                        game_ads.ads_information
                    """ + criNewUser +
                """
                            AND NOT agent_id = 0
                            GROUP BY user_id
                            HAVING
                                COUNT(user_id) > 1 ) tempA
                        """);
        //设置回传对象
        sqlNewUserAgainNum.setCallback(Sqls.callback.longValue());
        //执行sql
        dao.execute(sqlNewUserAgainNum);
        //设置新用户充值人数
        vo.setNewUserOrderAgain(sqlNewUserAgainNum.getLong());

        //创角人数
        Sql sqlRoleNum = Sqls.create("""
                SELECT
                    COUNT(DISTINCT role_user_id) as role_num
                FROM
                    dw_create_role_detail
                """ + criRoleNum +
                """
                        AND NOT user_agent_id = 0
                        """);
        //设置回传对象
        sqlRoleNum.setCallback(Sqls.callback.longValue());
        //执行sql
        dao.execute(sqlRoleNum);
        //设置创角人数
        vo.setRoleNum(sqlRoleNum.getLong());

        return vo;
    }

    /**
     * 所有为空值的属性都不copy
     *
     * @param source 原数据
     * @param target 目标数据
     */
    private void copyNullProperties(Object source, Object target) {
        BeanUtils.copyProperties(source, target, getNullField(source));
    }

    /**
     * 获取属性中为空的字段
     *
     * @param target 目标对象
     * @return 不需要替换的字段数组
     */
    private static String[] getNullField(Object target) {
        BeanWrapper beanWrapper = new BeanWrapperImpl(target);
        PropertyDescriptor[] propertyDescriptors = beanWrapper.getPropertyDescriptors();
        Set<String> notNullFieldSet = new HashSet<>();
        if (propertyDescriptors.length > 0) {
            for (PropertyDescriptor p : propertyDescriptors) {
                String name = p.getName();
                Object value = beanWrapper.getPropertyValue(name);
                if (Objects.isNull(value)) {
                    notNullFieldSet.add(name);
                }
            }
        }
        String[] notNullField = new String[notNullFieldSet.size()];

        return notNullFieldSet.toArray(notNullField);
    }

    /**
     * 主表 账面相关 查询条件(投手游戏数据相关)
     *
     * @param dto         前端传递的查询条件
     * @param type        criA 查询主表  criB 查询账面相关
     * @param needGroupBy 是否需要分组条件
     * @return 查询条件
     */
    private Criteria myCri(List<Long> userIds, List<Long> gameIds, PitcherGameDataTotalDTO dto, String type, Boolean needGroupBy) {
        String gameColumn = "game_id";
        String cpColumn = "game_cp";
        String typeColumn = "game_type";
        if (dto.getGameDimension() == 2L) {
            gameColumn = "parent_game_id";
            cpColumn = "parent_game_cp";
            typeColumn = "parent_game_type";
        }
        //根据传入的dto拼接查询参数
        Criteria cri = Cnd.cri();
        if (CollectionUtils.isNotEmpty(gameIds)) {
            //拼接游戏id查询条件
            cri.where().andInList(gameColumn, gameIds);
        }
        if (StringUtils.isNotBlank(dto.getGameCp())) {
            //拼接游戏cp方
            cri.where().andEquals(cpColumn, dto.getGameCp());
        }
        if (dto.getGameType() != null) {
            //拼接游戏类型查询条件
            cri.where().andEquals(typeColumn, dto.getGameType());
        }
        if (StringUtils.isNotBlank(dto.getSourceSystem())) {
            //拼接SDK来源
            cri.where().andEquals("source_system", dto.getSourceSystem());
        }
        if (CollectionUtils.isNotEmpty(userIds)) {
            //拼接投手ID
            cri.where().andInList("pitcher_id", userIds);
        }
        //根据type拼接不同的时间查询条件
        if ("criA".equals(type)) {
            if (dto.getBeginDate() != null && dto.getEndDate() != null) {
                //拼接注册日期查询条件
                cri.where().andBetween("dt", dto.getBeginDate(), dto.getEndDate());
            }
        } else if ("criB".equals(type)) {
            if (dto.getAmountBeginDate() != null && dto.getAmountEndDate() != null) {
                //拼接充值日期查询条件
                cri.where().andBetween("dt", dto.getAmountBeginDate(), dto.getAmountEndDate());
            }
        }
        //拼接分组条件
        if (needGroupBy) {
            cri.getGroupBy().groupBy(gameColumn, "pitcher_id");
        }

        return cri;
    }

    /**
     * 账面充值人数、新用户充值人数、新用户复充人数的查询条件(投手游戏数据相关)
     *
     * @param dto         前端传递的查询条件参数实体
     * @param needRegTime 是否需要拼接注册时间
     * @return 查询条件
     */
    private Criteria myCriRecharge(List<Long> userIds, List<Long> gameIds, PitcherGameDataTotalDTO dto, Boolean needRegTime) {
        String gameColumn = "game_id";
        String classifyColumn = "classify";
        if (dto.getGameDimension() == 2L) {
            gameColumn = "parent_game_id";
            classifyColumn = "parent_game_classify";
        }
        //根据传入的dto拼接查询参数
        Criteria cri = Cnd.cri();
        if (CollectionUtils.isNotEmpty(gameIds)) {
            //拼接游戏id查询条件
            cri.where().andInList(gameColumn, gameIds);
        }
        if (StringUtils.isNotBlank(dto.getGameCp())) {
            //拼接游戏cp方
            cri.where().andEquals("cp_name", dto.getGameCp());
        }
        if (dto.getGameType() != null) {
            //拼接游戏类型查询条件
            cri.where().andEquals(classifyColumn, dto.getGameType());
        }
        if (StringUtils.isNotBlank(dto.getSourceSystem())) {
            //拼接SDK来源
            cri.where().andEquals("source_system", dto.getSourceSystem());
        }
        if (CollectionUtils.isNotEmpty(userIds)) {
            //拼接投手ID
            cri.where().andInList("pitcher_id", userIds);
        }
        //拼接不同的时间查询条件
        if (needRegTime && dto.getBeginDate() != null && dto.getEndDate() != null) {
            //拼接注册日期查询条件
            cri.where().andBetween("reg_time", dto.getBeginDate(), dto.getEndDate());
        }
        if (dto.getAmountBeginDate() != null && dto.getAmountEndDate() != null) {
            //拼接充值日期查询条件
            cri.where().andBetween("order_time", dto.getAmountBeginDate(), dto.getAmountEndDate());
        }

        return cri;
    }

    /**
     * 创角人数相关查询条件(投手游戏数据相关)
     *
     * @param dto 前端传递的查询条件
     * @return 查询条件
     */
    private Criteria myCriRole(List<Long> userIds, List<Long> gameIds, PitcherGameDataTotalDTO dto) {
        String gameColumn = "user_game_id";
        if (dto.getGameDimension() == 2L) {
            gameColumn = "parent_game_id";
        }
        //根据传入的dto拼接查询参数
        Criteria cri = Cnd.cri();
        if (CollectionUtils.isNotEmpty(gameIds)) {
            //拼接游戏id查询条件
            cri.where().andInList(gameColumn, gameIds);
        }
        if (StringUtils.isNotBlank(dto.getGameCp())) {
            //拼接游戏cp方
            cri.where().andEquals("cp_name", dto.getGameCp());
        }
        if (dto.getGameType() != null) {
            //拼接游戏类型查询条件
            cri.where().andEquals("user_game_classify", dto.getGameType());
        }
        if (StringUtils.isNotBlank(dto.getSourceSystem())) {
            //拼接SDK来源
            cri.where().andEquals("source_system", dto.getSourceSystem());
        }
        if (CollectionUtils.isNotEmpty(userIds)) {
            //拼接投手ID
            cri.where().andInList("user_zx_pitcher_id", userIds);
        }
        if (dto.getBeginDate() != null && dto.getEndDate() != null) {
            //拼接注册日期查询条件
            cri.where().andBetween("user_dt", dto.getBeginDate(), dto.getEndDate());
        }
        if (dto.getAmountBeginDate() != null && dto.getAmountEndDate() != null) {
            //拼接充值日期查询条件 在充值时间内创角
            cri.where().andBetween("DATE(role_create_time)", dto.getAmountBeginDate(), dto.getAmountEndDate());
        }

        return cri;
    }

    /**
     * 主表 账面相关 查询条件(投手数据相关)
     *
     * @param dto         前端传递的查询条件
     * @param type        criA 查询主表  criB 查询账面相关
     * @param needGroupBy 是否需要分组条件
     * @return 查询条件
     */
    private Criteria myCriPitcher(List<Long> userIds, PitcherDataTotalDTO dto, String type, Boolean needGroupBy) {
        //根据传入的dto拼接查询参数
        Criteria cri = Cnd.cri();
        if (StringUtils.isNotBlank(dto.getSourceSystem())) {
            //拼接SDK来源
            cri.where().andEquals("source_system", dto.getSourceSystem());
        }
        if (CollectionUtils.isNotEmpty(userIds)) {
            //拼接投手ID
            cri.where().andInList("pitcher_id", userIds);
        }
        //根据type拼接不同的时间查询条件
        if ("criA".equals(type)) {
            if (dto.getBeginDate() != null && dto.getEndDate() != null) {
                //拼接注册日期查询条件
                cri.where().andBetween("dt", dto.getBeginDate(), dto.getEndDate());
            }
        } else if ("criB".equals(type)) {
            if (dto.getAmountBeginDate() != null && dto.getAmountEndDate() != null) {
                //拼接充值日期查询条件
                cri.where().andBetween("dt", dto.getAmountBeginDate(), dto.getAmountEndDate());
            }
        }
        //拼接分组条件
        if (needGroupBy) {
            cri.getGroupBy().groupBy("pitcher_id");
        }

        return cri;
    }

    /**
     * 账面充值人数、新用户充值人数、新用户复充人数的查询条件(投手数据相关)
     *
     * @param dto         前端传递的查询条件参数实体
     * @param needRegTime 是否需要拼接注册时间
     * @return 查询条件
     */
    private Criteria myCriRechargePitcher(List<Long> userIds, PitcherDataTotalDTO dto, Boolean needRegTime) {
        //根据传入的dto拼接查询参数
        Criteria cri = Cnd.cri();
        if (StringUtils.isNotBlank(dto.getSourceSystem())) {
            //拼接SDK来源
            cri.where().andEquals("source_system", dto.getSourceSystem());
        }
        if (CollectionUtils.isNotEmpty(userIds)) {
            //拼接投手ID
            cri.where().andInList("pitcher_id", userIds);
        }
        //拼接不同的时间查询条件
        if (needRegTime && dto.getBeginDate() != null && dto.getEndDate() != null) {
            //拼接注册日期查询条件
            cri.where().andBetween("reg_time", dto.getBeginDate(), dto.getEndDate());
        }
        if (dto.getAmountBeginDate() != null && dto.getAmountEndDate() != null) {
            //拼接充值日期查询条件
            cri.where().andBetween("order_time", dto.getAmountBeginDate(), dto.getAmountEndDate());
        }

        return cri;
    }

    /**
     * 创角人数相关查询条件(投手数据相关)
     *
     * @param dto 前端传递的查询条件
     * @return 查询条件
     */
    private Criteria myCriRolePitcher(List<Long> userIds, PitcherDataTotalDTO dto) {
        //根据传入的dto拼接查询参数
        Criteria cri = Cnd.cri();
        if (StringUtils.isNotBlank(dto.getSourceSystem())) {
            //拼接SDK来源
            cri.where().andEquals("source_system", dto.getSourceSystem());
        }
        if (CollectionUtils.isNotEmpty(userIds)) {
            //拼接投手ID
            cri.where().andInList("user_zx_pitcher_id", userIds);
        }
        if (dto.getBeginDate() != null && dto.getEndDate() != null) {
            //拼接注册日期查询条件
            cri.where().andBetween("user_dt", dto.getBeginDate(), dto.getEndDate());
        }
        if (dto.getAmountBeginDate() != null && dto.getAmountEndDate() != null) {
            //拼接充值日期查询条件 在充值时间内创角
            cri.where().andBetween("DATE(role_create_time)", dto.getAmountBeginDate(), dto.getAmountEndDate());
        }

        return cri;
    }

    /**
     * 生成DayN数据方法（投手总数据）
     *
     * @param dto 前端查询参数
     * @return Map
     */
    private Map<String, String> tempDayNDataPitcher(PitcherDataTotalDTO dto, List<Long> userIds) {
        String tableName = "game_ads.ads_pitcher_dayn";
        if (dto.getGameDimension() == 2L) {
            tableName = "game_ads_parent.ads_pitcher_dayn_parent";
        }
        //创建查询条件
        Criteria cri = Cnd.cri();
        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 (CollectionUtils.isNotEmpty(userIds)) {
            cri.where().andInList("pitcher_id", userIds);
        }
        Sql dayNSql = Sqls.create("""
                SELECT
                    dt,
                    pitcher_id,
                    source_system,
                    day_n
                FROM
                """ + tableName + cri);

        //设置回传
        dayNSql.setCallback(Sqls.callback.entities());
        dayNSql.setEntity(dao.getEntity(AdsPitcherDayn.class));
        //执行sql 获取数据到list中
        dao.execute(dayNSql);

        //将数据库中获取的所有结果封装到一个List中
        List<AdsPitcherDayn> list = dayNSql.getList(AdsPitcherDayn.class);
        // pitcherId - dayN(充值金额-充值次数)
        Map<String, String> tempMap = new HashMap<>();

        //循环遍历 list 按照 gameId 进行存储
        for (AdsPitcherDayn adsPitcherDayn : list) {
            if (!tempMap.containsKey(adsPitcherDayn.getPitcherId())) {
                //Map中没有该投手的相关数据，初始化tempMap
                tempMap.put(adsPitcherDayn.getPitcherId(), "0.00-0.00-0.00-0.00-0-0-0");
            }
            //解析每个 adsPitcherGameDayn 的 dayN
            parseJsonDataPitcher(adsPitcherDayn, tempMap, dto);
        }

        return tempMap;
    }

    /**
     * 生成DayN数据方法（投手游戏总数据）
     *
     * @param dto 前端查询参数
     * @return Map
     */
    private Map<String, Map<String, String>> tempDayNData(PitcherGameDataTotalDTO dto, List<Long> userIds, List<Long> gameIds) {
        String gameColumn = "game_id";
        if (dto.getGameDimension() == 2L) {
            gameColumn = "parent_game_id";
        }
        //创建查询条件
        Criteria cri = Cnd.cri();
        if (dto.getBeginDate() != null && dto.getEndDate() != null) {
            //拼接注册时间查询条件
            cri.where().andBetween("dt", dto.getBeginDate(), dto.getEndDate());
        }
        if (CollectionUtils.isNotEmpty(gameIds)) {
            //拼接游戏ID查询条件
            cri.where().andInList(gameColumn, gameIds);
        }
        if (StringUtils.isNotBlank(dto.getSourceSystem())) {
            //拼接SDK来源查询条件
            cri.where().andEquals("source_system", dto.getSourceSystem());
        }
        if (CollectionUtils.isNotEmpty(userIds)) {
            cri.where().andInList("pitcher_id", userIds);
        }
        Sql dayNSql;
        if (dto.getGameDimension() == 1L) {
            dayNSql = Sqls.create("""
                    SELECT
                        dt,
                        game_id,
                        pitcher_id,
                        source_system,
                        day_n
                    FROM
                        game_ads.ads_pitcher_game_dayn
                    """ + cri);
        } else {
            dayNSql = Sqls.create("""
                    SELECT
                        dt,
                        parent_game_id as game_id,
                        pitcher_id,
                        source_system,
                        day_n
                    FROM
                        game_ads_parent.ads_pitcher_game_dayn_parent
                    """ + cri);
        }

        //设置回传
        dayNSql.setCallback(Sqls.callback.entities());
        dayNSql.setEntity(dao.getEntity(AdsPitcherGameDayn.class));
        //执行sql 获取数据到list中
        dao.execute(dayNSql);

        //将数据库中获取的所有结果封装到一个List中
        List<AdsPitcherGameDayn> list = dayNSql.getList(AdsPitcherGameDayn.class);
        // pitcherId - <gameId, dayN>
        Map<String, Map<String, String>> tempMap = new HashMap<>();

        //循环遍历 list 按照 gameId 进行存储
        for (AdsPitcherGameDayn adsPitcherGameDayn : list) {
            if (!tempMap.containsKey(adsPitcherGameDayn.getPitcherId())) {
                //Map中没有该投手的相关数据，初始化dayNMap
                Map<String, String> resMap = new HashMap<>();
                //保存初始化结果
                tempMap.put(adsPitcherGameDayn.getPitcherId(), resMap);
            }
            //解析 dayN
            Map<String, String> resMap = tempMap.get(adsPitcherGameDayn.getPitcherId());
            if (!resMap.containsKey(adsPitcherGameDayn.getGameId())) {
                //不含有该游戏信息，初始化数据
                resMap.put(adsPitcherGameDayn.getGameId(), "0.00-0");
            }
            //解析每个 adsPitcherGameDayn 的 dayN
            parseJsonData(adsPitcherGameDayn, resMap, dto);
            //将解析的结果保存
            tempMap.put(adsPitcherGameDayn.getPitcherId(), resMap);
        }

        return tempMap;
    }

    /**
     * 计算所有的dayN(投手总数据总计)
     *
     * @param dto 前端查询参数
     * @return Map
     */
    private String tempDayNDataTotalPitcher(PitcherDataTotalDTO dto, List<Long> userIds) {
        String tableName = "game_ads.ads_pitcher_dayn";
        if (dto.getGameDimension() == 2L) {
            tableName = "game_ads_parent.ads_pitcher_dayn_parent";
        }
        //创建查询条件
        Criteria cri = Cnd.cri();
        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 (CollectionUtils.isNotEmpty(userIds)) {
            cri.where().andInList("pitcher_id", userIds);
        }
        Sql dayNSql = Sqls.create("""
                SELECT
                    dt,
                    pitcher_id,
                    source_system,
                    day_n
                FROM 
                """ + tableName + cri);

        //设置回传
        dayNSql.setCallback(Sqls.callback.entities());
        dayNSql.setEntity(dao.getEntity(AdsPitcherDayn.class));
        //执行sql 获取数据到list中
        dao.execute(dayNSql);

        //将数据库中获取的所有结果封装到一个List中
        List<AdsPitcherDayn> list = dayNSql.getList(AdsPitcherDayn.class);
        //初始化dayN结果
        Map<String, String> resMap = new HashMap<>();
        resMap.put("dayN", "0.00-0.00-0.00-0.00-0-0-0");

        //循环遍历 list 按照 gameId 进行存储
        for (AdsPitcherDayn adsPitcherDayn : list) {
            //解析每个 adsPitcherGameDayn 的 dayN
            parseJsonDataPitcher(adsPitcherDayn, resMap, dto);
        }
        //返回结果
        return resMap.get("dayN");
    }

    /**
     * 计算所有的dayN(投手游戏总数据总计)
     *
     * @param dto 前端查询参数
     * @return Map
     */
    private String tempDayNDataTotal(PitcherGameDataTotalDTO dto, List<Long> userIds, List<Long> gameIds) {
        String gameColumn = "game_id";
        if (dto.getGameDimension() == 2L) {
            gameColumn = "parent_game_id";
        }
        //创建查询条件
        Criteria cri = Cnd.cri();
        if (dto.getBeginDate() != null && dto.getEndDate() != null) {
            //拼接注册时间查询条件
            cri.where().andBetween("dt", dto.getBeginDate(), dto.getEndDate());
        }
        if (CollectionUtils.isNotEmpty(gameIds)) {
            //拼接游戏ID查询条件
            cri.where().andInList(gameColumn, gameIds);
        }
        if (StringUtils.isNotBlank(dto.getSourceSystem())) {
            //拼接SDK来源查询条件
            cri.where().andEquals("source_system", dto.getSourceSystem());
        }
        if (CollectionUtils.isNotEmpty(userIds)) {
            cri.where().andInList("pitcher_id", userIds);
        }
        Sql dayNSql;
        if (dto.getGameDimension() == 1L) {
            dayNSql = Sqls.create("""
                    SELECT
                        dt,
                        game_id,
                        pitcher_id,
                        source_system,
                        day_n
                    FROM
                        game_ads.ads_pitcher_game_dayn
                    """ + cri);
        } else {
            dayNSql = Sqls.create("""
                    SELECT
                        dt,
                        parent_game_id as game_id,
                        pitcher_id,
                        source_system,
                        day_n
                    FROM
                        game_ads_parent.ads_pitcher_game_dayn_parent
                    """ + cri);
        }

        //设置回传
        dayNSql.setCallback(Sqls.callback.entities());
        dayNSql.setEntity(dao.getEntity(AdsPitcherGameDayn.class));
        //执行sql 获取数据到list中
        dao.execute(dayNSql);

        //将数据库中获取的所有结果封装到一个List中
        List<AdsPitcherGameDayn> list = dayNSql.getList(AdsPitcherGameDayn.class);
        //初始化dayN结果
        Map<String, String> resMap = new HashMap<>();
        resMap.put("dayN", "0.00-0");

        //循环遍历 list 按照 gameId 进行存储
        for (AdsPitcherGameDayn adsPitcherGameDayn : list) {
            //解析每个 adsPitcherGameDayn 的 dayN
            parseJsonData(adsPitcherGameDayn, resMap, dto);
        }
        //返回结果
        return resMap.get("dayN");
    }

    /**
     * 解析dayN json字符串数据（投手总数据）
     *
     * @param adsPitcherDayn 对象
     * @param resMap         记录最终结果Map
     * @param dto            前端查询条件
     */
    private void parseJsonDataPitcher(AdsPitcherDayn adsPitcherDayn, Map<String, String> resMap, PitcherDataTotalDTO dto) {
        //将每一个adsDaynGame中的dayN属性值 由json转为 Map 型
        Gson gson = new Gson();
        Map<String, String> jsonList = gson.fromJson(adsPitcherDayn.getDayN(), Map.class);
        //解析 jsonList
        for (Map.Entry<String, String> entry : jsonList.entrySet()) {
            //key是充值日期
            String key = entry.getKey();
            String value = entry.getValue();
            //将value按“-”分割
            String[] resValues = value.split("-");
            //判断充值日期 在充值日期内的数据进行计算
            if (DateUtil.parseLocalDate(key).compareTo(dto.getAmountBeginDate()) >= 0
                    && DateUtil.parseLocalDate(key).compareTo(dto.getAmountEndDate()) <= 0) {
                //获取原先的dayN的值
                String[] oldValues;
                if (resMap.containsKey("dayN")) {
                    oldValues = resMap.get("dayN").split("-");
                } else {
                    oldValues = resMap.get(adsPitcherDayn.getPitcherId()).split("-");
                }
                //充值的金额
                BigDecimal chargeMoney = new BigDecimal(oldValues[0]).add(new BigDecimal(resValues[0])).setScale(2, RoundingMode.HALF_UP);
                //支付宝金额
                BigDecimal alipayMoney = new BigDecimal(oldValues[1]).add(new BigDecimal(resValues[1])).setScale(2, RoundingMode.HALF_UP);
                //微信金额
                BigDecimal wechatMoney = new BigDecimal(oldValues[2]).add(new BigDecimal(resValues[2])).setScale(2, RoundingMode.HALF_UP);
                //米大师金额
                BigDecimal miMasterMoney = new BigDecimal(oldValues[3]).add(new BigDecimal(resValues[3])).setScale(2, RoundingMode.HALF_UP);
                //充值的次数
                long count = Long.parseLong(oldValues[4]) + Long.parseLong(resValues[4]);
                //合并成字符串 更新dayN的值
                String newValue = chargeMoney + "-" + alipayMoney + "-" + wechatMoney + "-" + miMasterMoney + "-" + count;
                if (resMap.containsKey("dayN")) {
                    resMap.put("dayN", newValue);
                } else {
                    resMap.put(adsPitcherDayn.getPitcherId(), newValue);
                }
            }
        }
    }

    /**
     * 解析dayN json字符串数据（投手游戏总数据）
     *
     * @param adsPitcherGameDayn 对象
     * @param resMap             记录最终结果Map
     * @param dto                前端查询条件
     */
    private void parseJsonData(AdsPitcherGameDayn adsPitcherGameDayn, Map<String, String> resMap, PitcherGameDataTotalDTO dto) {
        //将每一个adsDaynGame中的dayN属性值 由json转为 Map 型
        Gson gson = new Gson();
        Map<String, String> jsonList = gson.fromJson(adsPitcherGameDayn.getDayN(), Map.class);
        //解析 jsonList
        for (Map.Entry<String, String> entry : jsonList.entrySet()) {
            //key是充值日期
            String key = entry.getKey();
            String value = entry.getValue();
            //将value按“-”分割
            String[] resValues = value.split("-");
            //判断充值日期 在充值日期内的数据进行计算
            if (DateUtil.parseLocalDate(key).compareTo(dto.getAmountBeginDate()) >= 0
                    && DateUtil.parseLocalDate(key).compareTo(dto.getAmountEndDate()) <= 0) {
                //获取原先的dayN的值
                String[] oldValues;
                if (resMap.containsKey("dayN")) {
                    oldValues = resMap.get("dayN").split("-");
                } else {
                    oldValues = resMap.get(adsPitcherGameDayn.getGameId()).split("-");
                }
                //充值的金额
                BigDecimal chargeMoney = new BigDecimal(oldValues[0]).add(new BigDecimal(resValues[0])).setScale(2, RoundingMode.HALF_UP);
                //充值的次数
                long count = Long.parseLong(oldValues[1]) + Long.parseLong(resValues[1]);
                //合并成字符串 更新dayN的值
                String newValue = chargeMoney + "-" + count;
                if (resMap.containsKey("dayN")) {
                    resMap.put("dayN", newValue);
                } else {
                    resMap.put(adsPitcherGameDayn.getGameId(), newValue);
                }
            }
        }
    }

    /**
     * 处理投手每日的DayN(投手每日)
     *
     * @param vo PitcherGameDataDayVO
     */
    private void formatPitcherDataDayDayN(PitcherDataDayVO vo) {
        if (CollectionUtils.isEmpty(pitcherDayNFieldMapList)) {
            return;
        }
        pitcherDayNFieldMapList.forEach(dayNFieldMap -> {
            try {
                String[] temps = ((String) dayNFieldMap.getT1().get(vo)).split("/");
                dayNFieldMap.getT2().set(vo, RechargeTrendVO.builder()
                        .rechargeMoney(new BigDecimal(temps[0]))
                        .rechargeUserCount(Long.valueOf(temps[1]))
                        .increase(new BigDecimal(temps[2]))
                        .back(new BigDecimal(temps[3]))
                        .multiples(new BigDecimal(temps[4]))
                        .build());
            } catch (Exception e) {
                throw new BaseException("映射出错");
            }
        });
    }

    /**
     * 处理投手游戏每日的DayN（投手游戏每日）
     *
     * @param vo PitcherGameDataDayVO
     */
    private void formatPitcherGameDataDayDayN(PitcherGameDataDayVO vo) {
        if (CollectionUtils.isEmpty(pitcherGameDayNFieldMapList)) {
            return;
        }
        pitcherGameDayNFieldMapList.forEach(dayNFieldMap -> {
            try {
                String[] temps = ((String) dayNFieldMap.getT1().get(vo)).split("/");
                dayNFieldMap.getT2().set(vo, RechargeTrendVO.builder()
                        .rechargeMoney(new BigDecimal(temps[0]))
                        .rechargeUserCount(Long.valueOf(temps[1]))
                        .increase(new BigDecimal(temps[2]))
                        .back(new BigDecimal(temps[3]))
                        .multiples(new BigDecimal(temps[4]))
                        .build());
            } catch (Exception e) {
                throw new BaseException("映射出错");
            }
        });
    }

    /**
     * 处理投手游戏每日的DayN（投手游戏每日-子游戏维度）
     *
     * @param vo PitcherGameDataDayTotalVO
     */
    private void formatPitcherGameDataDayDayNForSon(PitcherGameDataDayVO vo) {
        if (CollectionUtils.isEmpty(pitcherGameDayNFieldMapList)) {
            return;
        }
        pitcherGameDayNFieldMapList.forEach(dayNTotalFieldMap -> {
            try {
                //得到需要计算的值
                String[] temps = ((String) dayNTotalFieldMap.getT1().get(vo)).split("/");
                //dn的金额总计
                BigDecimal dNAmount = new BigDecimal(temps[0]).setScale(2, RoundingMode.HALF_UP);;;
                //d1-dn的金额总计
                BigDecimal d1ToDNTotalAmount = new BigDecimal(temps[1]);
                //d1-dn的消耗总计（排除了未到时间的cost）
                BigDecimal d1ToDNTotalCost = new BigDecimal(temps[3]);
                //d1的金额总计（排除了未到时间的d1）
                BigDecimal d1Amount = new BigDecimal(temps[4]);
                //赋值
                dayNTotalFieldMap.getT2().set(vo, RechargeTrendVO.builder()
                        .rechargeMoney(dNAmount)
                        .rechargeUserCount(Long.valueOf(temps[2]))
                        .increase(d1ToDNTotalCost.compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                                dNAmount.divide(d1ToDNTotalCost, 4, RoundingMode.HALF_UP))
                        .back(d1ToDNTotalCost.compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                                d1ToDNTotalAmount.divide(d1ToDNTotalCost, 4, RoundingMode.HALF_UP))
                        .multiples(d1Amount.compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                                d1ToDNTotalAmount.divide(d1Amount, 4, RoundingMode.HALF_UP))
                        .build());
            } catch (Exception e) {
                throw new BaseException("映射出错");
            }
        });
    }

    /**
     * 处理投手每日总计的DayN（投手每日总计）
     *
     * @param vo PitcherDataDayTotalVO
     */
    private void formatPitcherDataDayTotalDayN(PitcherDataDayTotalVO vo) {
        if (CollectionUtils.isEmpty(pitcherDayNTotalFieldMapList)) {
            return;
        }
        pitcherDayNTotalFieldMapList.forEach(dayNTotalFieldMap -> {
            try {
                //得到需要计算的值
                String[] temps = ((String) dayNTotalFieldMap.getT1().get(vo)).split("/");
                //dn的金额总计
                BigDecimal dNAmount = new BigDecimal(temps[0]).setScale(2, RoundingMode.HALF_UP);
                //d1-dn的金额总计
                BigDecimal d1ToDNTotalAmount = new BigDecimal(temps[1]);
                //d1-dn的消耗总计（排除了未到时间的cost）
                BigDecimal d1ToDNTotalCost = new BigDecimal(temps[3]);
                //d1的金额总计（排除了未到时间的d1）
                BigDecimal d1Amount = new BigDecimal(temps[4]);
                //赋值
                dayNTotalFieldMap.getT2().set(vo, RechargeTrendVO.builder()
                        .rechargeMoney(dNAmount)
                        .rechargeUserCount(Long.valueOf(temps[2]))
                        .increase(d1ToDNTotalCost.compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                                dNAmount.divide(d1ToDNTotalCost, 4, RoundingMode.HALF_UP))
                        .back(d1ToDNTotalCost.compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                                d1ToDNTotalAmount.divide(d1ToDNTotalCost, 4, RoundingMode.HALF_UP))
                        .multiples(d1Amount.compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                                d1ToDNTotalAmount.divide(d1Amount, 4, RoundingMode.HALF_UP))
                        .build());
            } catch (Exception e) {
                throw new BaseException("映射出错");
            }
        });
    }

    /**
     * 处理投手游戏每日总计的DayN（投手游戏每日总计）
     *
     * @param vo PitcherGameDataDayTotalVO
     */
    private void formatPitcherGameDataDayTotalDayN(PitcherGameDataDayTotalVO vo) {
        if (CollectionUtils.isEmpty(pitcherGameDayNTotalFieldMapList)) {
            return;
        }
        pitcherGameDayNTotalFieldMapList.forEach(dayNTotalFieldMap -> {
            try {
                //得到需要计算的值
                String[] temps = ((String) dayNTotalFieldMap.getT1().get(vo)).split("/");
                //dn的金额总计
                BigDecimal dNAmount = new BigDecimal(temps[0]).setScale(2, RoundingMode.HALF_UP);;
                //d1-dn的金额总计
                BigDecimal d1ToDNTotalAmount = new BigDecimal(temps[1]);
                //d1-dn的消耗总计（排除了未到时间的cost）
                BigDecimal d1ToDNTotalCost = new BigDecimal(temps[3]);
                //d1的金额总计（排除了未到时间的d1）
                BigDecimal d1Amount = new BigDecimal(temps[4]);
                //赋值
                dayNTotalFieldMap.getT2().set(vo, RechargeTrendVO.builder()
                        .rechargeMoney(dNAmount)
                        .rechargeUserCount(Long.valueOf(temps[2]))
                        .increase(d1ToDNTotalCost.compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                                dNAmount.divide(d1ToDNTotalCost, 4, RoundingMode.HALF_UP))
                        .back(d1ToDNTotalCost.compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                                d1ToDNTotalAmount.divide(d1ToDNTotalCost, 4, RoundingMode.HALF_UP))
                        .multiples(d1Amount.compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                                d1ToDNTotalAmount.divide(d1Amount, 4, RoundingMode.HALF_UP))
                        .build());
            } catch (Exception e) {
                throw new BaseException("映射出错");
            }
        });
    }

    /**
     * 查询投手每日数据SQL（投手每日）
     *
     * @param tableName 查询的表名
     * @return String
     */
    private String pitcherDataDaySql(String tableName) {
        return """
                SELECT
                    dt,
                    source_system,
                    pitcher_id,
                    pitcher,
                    cost,
                    plan_count,
                    account_count,
                    agent_count,
                    register_num,
                    first_new_user_amount_count,
                    first_new_user_amount_num,
                    first_new_user_amount,
                    amount_count,
                    amount_num,
                    amount,
                    old_amount_count,
                    old_amount_num,
                    old_amount,
                    new_user_total_amount_count,
                    new_user_total_amount_num,
                    new_user_total_amount,
                    hundred_user_num,
                    buy_new_user_total_amount,
                    buy_new_user_total_amount_num,
                    buy_new_user_total_amount_count,
                    reg_order_user_again,
                    first_role_num,
                    new_user_total_role_num,
                    role_num,
                    register_cost,
                    first_roi,
                    buy_roi,
                    today_roi,
                    gross_profit,
                    first_rate,
                    buy_user_rate,
                    today_rate,
                    first_avg,
                    buy_avg,
                    today_avg,
                    paper_avg,
                    first_amount_cost,
                    buy_amount_cost,
                    today_amount_cost,
                    today_again_rate,
                    new_reg_arpu,
                    first_arpu,
                    today_arpu,
                    paper_arpu,
                    hundred_user_num_cost,
                    roi1,
                    roi2,
                    roi3,
                    roi4,
                    roi5,
                    roi6,
                    roi7,
                    roi8,
                    roi9,
                    roi10,
                    roi11,
                    roi12,
                    roi13,
                    roi14,
                    roi15,
                    roi16,
                    roi17,
                    roi18,
                    roi19,
                    roi20,
                    roi21,
                    roi22,
                    roi23,
                    roi24,
                    roi25,
                    roi26,
                    roi27,
                    roi28,
                    roi29,
                    roi30,
                    roi60,
                    roi90,
                    roi180,
                    roi1yaer,
                    roi_total,
                    amount_d1,
                    amount_d2,
                    amount_d3,
                    amount_d4,
                    amount_d5,
                    amount_d6,
                    amount_d7,
                    amount_d8,
                    amount_d9,
                    amount_d10,
                    amount_d11,
                    amount_d12,
                    amount_d13,
                    amount_d14,
                    amount_d15,
                    amount_d16,
                    amount_d17,
                    amount_d18,
                    amount_d19,
                    amount_d20,
                    amount_d21,
                    amount_d22,
                    amount_d23,
                    amount_d24,
                    amount_d25,
                    amount_d26,
                    amount_d27,
                    amount_d28,
                    amount_d29,
                    amount_d30,
                    amount_d31,
                    amount_d32,
                    amount_d33,
                    amount_d34,
                    amount_d35,
                    amount_d36,
                    amount_d37,
                    amount_d38,
                    amount_d39,
                    amount_d40,
                    amount_d41,
                    amount_d42,
                    amount_d43,
                    amount_d44,
                    amount_d45,
                    amount_d46,
                    amount_d47,
                    amount_d48,
                    amount_d49,
                    amount_d50,
                    amount_d51,
                    amount_d52,
                    amount_d53,
                    amount_d54,
                    amount_d55,
                    amount_d56,
                    amount_d57,
                    amount_d58,
                    amount_d59,
                    amount_d60,
                    amount_d61,
                    amount_d62,
                    amount_d63,
                    amount_d64,
                    amount_d65,
                    amount_d66,
                    amount_d67,
                    amount_d68,
                    amount_d69,
                    amount_d70,
                    amount_d71,
                    amount_d72,
                    amount_d73,
                    amount_d74,
                    amount_d75,
                    amount_d76,
                    amount_d77,
                    amount_d78,
                    amount_d79,
                    amount_d80,
                    amount_d81,
                    amount_d82,
                    amount_d83,
                    amount_d84,
                    amount_d85,
                    amount_d86,
                    amount_d87,
                    amount_d88,
                    amount_d89,
                    amount_d90,
                    amount_m4,
                    amount_m5,
                    amount_m6,
                    amount_m7,
                    amount_m8,
                    amount_m9,
                    amount_m10,
                    amount_m11,
                    amount_m12,
                    amount_sum,
                    first_role_cost,
                    new_user_total_role_cost,
                    role_num_cost,
                    first_role_rate,
                    new_user_total_role_rate,
                    role_num_rate,
                    new_user_amount_ratio,
                    total_five_hundred_user_count
                FROM 
                """ + tableName;
    }

    /**
     * 查询投手游戏每日数据SQL（投手游戏每日）
     *
     * @return String
     */
    private String pitcherGameDataDaySql() {
        return """
                SELECT
                    dt,
                    source_system,
                    pitcher_id,
                    game_id,
                    pitcher,
                    game_name,
                    game_cp,
                    game_type,
                    cost,
                    plan_count,
                    account_count,
                    agent_count,
                    register_num,
                    first_new_user_amount_count,
                    first_new_user_amount_num,
                    first_new_user_amount,
                    amount_count,
                    amount_num,
                    amount,
                    old_amount_count,
                    old_amount_num,
                    old_amount,
                    new_user_total_amount_count,
                    new_user_total_amount_num,
                    new_user_total_amount,
                    hundred_user_num,
                    buy_new_user_total_amount,
                    buy_new_user_total_amount_num,
                    buy_new_user_total_amount_count,
                    reg_order_user_again,
                    first_role_num,
                    new_user_total_role_num,
                    role_num,
                    register_cost,
                    first_roi,
                    buy_roi,
                    today_roi,
                    gross_profit,
                    first_rate,
                    buy_user_rate,
                    today_rate,
                    first_avg,
                    buy_avg,
                    today_avg,
                    paper_avg,
                    first_amount_cost,
                    buy_amount_cost,
                    today_amount_cost,
                    today_again_rate,
                    new_reg_arpu,
                    first_arpu,
                    today_arpu,
                    paper_arpu,
                    hundred_user_num_cost,
                    roi1,
                    roi2,
                    roi3,
                    roi4,
                    roi5,
                    roi6,
                    roi7,
                    roi8,
                    roi9,
                    roi10,
                    roi11,
                    roi12,
                    roi13,
                    roi14,
                    roi15,
                    roi16,
                    roi17,
                    roi18,
                    roi19,
                    roi20,
                    roi21,
                    roi22,
                    roi23,
                    roi24,
                    roi25,
                    roi26,
                    roi27,
                    roi28,
                    roi29,
                    roi30,
                    roi60,
                    roi90,
                    roi180,
                    roi1yaer,
                    roi_total,
                    amount_d1,
                    amount_d2,
                    amount_d3,
                    amount_d4,
                    amount_d5,
                    amount_d6,
                    amount_d7,
                    amount_d8,
                    amount_d9,
                    amount_d10,
                    amount_d11,
                    amount_d12,
                    amount_d13,
                    amount_d14,
                    amount_d15,
                    amount_d16,
                    amount_d17,
                    amount_d18,
                    amount_d19,
                    amount_d20,
                    amount_d21,
                    amount_d22,
                    amount_d23,
                    amount_d24,
                    amount_d25,
                    amount_d26,
                    amount_d27,
                    amount_d28,
                    amount_d29,
                    amount_d30,
                    amount_d31,
                    amount_d32,
                    amount_d33,
                    amount_d34,
                    amount_d35,
                    amount_d36,
                    amount_d37,
                    amount_d38,
                    amount_d39,
                    amount_d40,
                    amount_d41,
                    amount_d42,
                    amount_d43,
                    amount_d44,
                    amount_d45,
                    amount_d46,
                    amount_d47,
                    amount_d48,
                    amount_d49,
                    amount_d50,
                    amount_d51,
                    amount_d52,
                    amount_d53,
                    amount_d54,
                    amount_d55,
                    amount_d56,
                    amount_d57,
                    amount_d58,
                    amount_d59,
                    amount_d60,
                    amount_d61,
                    amount_d62,
                    amount_d63,
                    amount_d64,
                    amount_d65,
                    amount_d66,
                    amount_d67,
                    amount_d68,
                    amount_d69,
                    amount_d70,
                    amount_d71,
                    amount_d72,
                    amount_d73,
                    amount_d74,
                    amount_d75,
                    amount_d76,
                    amount_d77,
                    amount_d78,
                    amount_d79,
                    amount_d80,
                    amount_d81,
                    amount_d82,
                    amount_d83,
                    amount_d84,
                    amount_d85,
                    amount_d86,
                    amount_d87,
                    amount_d88,
                    amount_d89,
                    amount_d90,
                    amount_m4,
                    amount_m5,
                    amount_m6,
                    amount_m7,
                    amount_m8,
                    amount_m9,
                    amount_m10,
                    amount_m11,
                    amount_m12,
                    amount_sum,
                    first_role_cost,
                    new_user_total_role_cost,
                    role_num_cost,
                    first_role_rate,
                    new_user_total_role_rate,
                    role_num_rate,
                    new_user_amount_ratio
                FROM
                    game_ads.ads_game_pitcher_day
                """;
    }

    /**
     * 查询投手游戏每日数据SQL（投手游戏每日-子游戏维度）
     *
     * @return String
     */
    private String pitcherGameDataDaySqlForSon() {
        return """
                SELECT
                    dt,
                    source_system ,
                    pitcher_id ,
                    MAX(pitcher) pitcher ,
                    GROUP_CONCAT(game_name) as game_name ,
                    SUM(cost) as cost,
                    SUM(plan_count) as plan_count,
                    SUM(account_count) as account_count,
                    SUM(agent_count) as agent_count,
                    SUM(register_num) as register_num,
                    ROUND(IF(SUM(register_num) > 0, SUM(cost) / SUM(register_num), 0), 2) as register_cost,
                    SUM(first_role_num) as first_role_num,
                    SUM(new_user_total_role_num) as new_user_total_role_num,
                    SUM(role_num) as role_num,
                    ROUND(IF(SUM(role_num) > 0 , SUM(cost) / SUM(role_num), 0), 2) as role_num_cost,
                    ROUND(IF(SUM(first_role_num) > 0 , SUM(cost) / SUM(first_role_num), 0), 2) as first_role_cost,
                    ROUND(IF(SUM(new_user_total_role_num) > 0 , SUM(cost) / SUM(new_user_total_role_num), 0), 2) as new_user_total_role_cost,
                    ROUND(IF(SUM(register_num) > 0 , SUM(first_role_num) / SUM(register_num), 0), 4) as first_role_rate,
                    ROUND(IF(SUM(register_num) > 0 , SUM(new_user_total_role_num) / SUM(register_num), 0), 4) as new_user_total_role_rate,
                    ROUND(IF(SUM(register_num) > 0 , SUM(role_num) / SUM(register_num), 0), 4) as role_num_rate,
                    SUM(first_new_user_amount_count) as first_new_user_amount_count,
                    SUM(first_new_user_amount_num) as first_new_user_amount_num,
                    SUM(first_new_user_amount) as first_new_user_amount,
                    SUM(amount_count) as amount_count,
                    SUM(amount_num) as amount_num,
                    SUM(amount) as amount,
                    SUM(old_amount_count) as old_amount_count,
                    SUM(old_amount_num) as old_amount_num,
                    SUM(old_amount) as old_amount,
                    SUM(new_user_total_amount_count) as new_user_total_amount_count,
                    SUM(new_user_total_amount_num) as new_user_total_amount_num,
                    SUM(new_user_total_amount) as new_user_total_amount,
                    SUM(buy_new_user_total_amount) as buy_new_user_total_amount,
                    SUM(buy_new_user_total_amount_num) as buy_new_user_total_amount_num,
                    SUM(buy_new_user_total_amount_count) as buy_new_user_total_amount_count,
                    ROUND(IF(SUM(cost) > 0, SUM(first_new_user_amount) / SUM(cost), 0), 4) as first_roi,
                    ROUND(IF(SUM(cost) > 0, SUM(buy_new_user_total_amount) / SUM(cost), 0), 4) as buy_roi,
                    ROUND(IF(SUM(cost) > 0, SUM(new_user_total_amount) / SUM(cost), 0), 4) as today_roi,
                    (SUM(new_user_total_amount) - SUM(cost)) as gross_profit,
                    ROUND(IF(SUM(register_num) > 0, SUM(first_new_user_amount_num) / SUM(register_num), 0), 4) as first_rate,
                    ROUND(IF(SUM(register_num) > 0, SUM(buy_new_user_total_amount_num) / SUM(register_num), 0), 4) as buy_user_rate,
                    ROUND(IF(SUM(register_num) > 0, SUM(new_user_total_amount_num) / SUM(register_num), 0), 4) as today_rate,
                    ROUND(IF(SUM(amount_num) > 0, SUM(first_new_user_amount_num) / SUM(amount_num), 0), 4) as new_user_amount_ratio,
                    ROUND(IF(SUM(first_new_user_amount_count) > 0 , SUM(first_new_user_amount) / SUM(first_new_user_amount_count), 0), 2) as first_avg,
                    ROUND(IF(SUM(buy_new_user_total_amount_count) > 0 , SUM(buy_new_user_total_amount) / SUM(buy_new_user_total_amount_count), 0), 2) as buy_avg,
                    ROUND(IF(SUM(new_user_total_amount_count) > 0 , SUM(new_user_total_amount) / SUM(new_user_total_amount_count), 0), 2) as today_avg,
                    ROUND(IF(SUM(amount_count) > 0 , SUM(amount) / SUM(amount_count), 0), 2) as paper_avg,
                    ROUND(IF(SUM(first_new_user_amount_num) > 0 , SUM(cost) / SUM(first_new_user_amount_num), 0), 2) as first_amount_cost,
                    ROUND(IF(SUM(buy_new_user_total_amount_num) > 0 , SUM(cost) / SUM(buy_new_user_total_amount_num), 0), 2) as buy_amount_cost,
                    ROUND(IF(SUM(new_user_total_amount_num) > 0 , SUM(cost) / SUM(new_user_total_amount_num), 0), 2) as today_amount_cost,
                    SUM(reg_order_user_again) as reg_order_user_again,
                    ROUND(IF(SUM(new_user_total_amount_num) > 0 ,SUM(reg_order_user_again) / SUM(new_user_total_amount_num), 0), 4) as today_again_rate,
                    ROUND(IF(SUM(register_num) > 0 , SUM(new_user_total_amount) / SUM(register_num), 0), 2) as new_reg_arpu,
                    ROUND(IF(SUM(first_new_user_amount_num) > 0 , SUM(first_new_user_amount) / SUM(first_new_user_amount_num), 0), 2) as first_arpu,
                    ROUND(IF(SUM(new_user_total_amount_num) > 0 , SUM(new_user_total_amount) / SUM(new_user_total_amount_num), 0), 2) as today_arpu,
                    ROUND(IF(SUM(amount_num) > 0 , SUM(amount) / SUM(amount_num), 0), 2) as paper_arpu,
                    
                    ROUND(IF(SUM(register_num) > 0 , SUM(first_new_user_amount) / SUM(register_num), 0), 2) as first_reg_arppu,
                    
                    SUM(hundred_user_num) as hundred_user_num,
                    ROUND(IF(SUM(hundred_user_num) > 0 , SUM(cost) / SUM(hundred_user_num), 0), 2) as hundred_user_num_cost,
                    ROUND(IF(SUM(cost) > 0 , SUM(da1) / SUM(cost), 0), 4) as roi1,
                    ROUND(IF(SUM(cost) > 0 , SUM(da2) / SUM(cost), 0), 4) as roi2,
                    ROUND(IF(SUM(cost) > 0 , SUM(da3) / SUM(cost), 0), 4) as roi3,
                    ROUND(IF(SUM(cost) > 0 , SUM(da4) / SUM(cost), 0), 4) as roi4,
                    ROUND(IF(SUM(cost) > 0 , SUM(da5) / SUM(cost), 0), 4) as roi5,
                    ROUND(IF(SUM(cost) > 0 , SUM(da6) / SUM(cost), 0), 4) as roi6,
                    ROUND(IF(SUM(cost) > 0 , SUM(da7) / SUM(cost), 0), 4) as roi7,
                    ROUND(IF(SUM(cost) > 0 , SUM(da8) / SUM(cost), 0), 4) as roi8,
                    ROUND(IF(SUM(cost) > 0 , SUM(da9) / SUM(cost), 0), 4) as roi9,
                    ROUND(IF(SUM(cost) > 0 , SUM(da10) / SUM(cost), 0), 4) as roi10,
                    ROUND(IF(SUM(cost) > 0 , SUM(da11) / SUM(cost), 0), 4) as roi11,
                    ROUND(IF(SUM(cost) > 0 , SUM(da12) / SUM(cost), 0), 4) as roi12,
                    ROUND(IF(SUM(cost) > 0 , SUM(da13) / SUM(cost), 0), 4) as roi13,
                    ROUND(IF(SUM(cost) > 0 , SUM(da14) / SUM(cost), 0), 4) as roi14,
                    ROUND(IF(SUM(cost) > 0 , SUM(da15) / SUM(cost), 0), 4) as roi15,
                    ROUND(IF(SUM(cost) > 0 , SUM(da16) / SUM(cost), 0), 4) as roi16,
                    ROUND(IF(SUM(cost) > 0 , SUM(da17) / SUM(cost), 0), 4) as roi17,
                    ROUND(IF(SUM(cost) > 0 , SUM(da18) / SUM(cost), 0), 4) as roi18,
                    ROUND(IF(SUM(cost) > 0 , SUM(da19) / SUM(cost), 0), 4) as roi19,
                    ROUND(IF(SUM(cost) > 0 , SUM(da20) / SUM(cost), 0), 4) as roi20,
                    ROUND(IF(SUM(cost) > 0 , SUM(da21) / SUM(cost), 0), 4) as roi21,
                    ROUND(IF(SUM(cost) > 0 , SUM(da22) / SUM(cost), 0), 4) as roi22,
                    ROUND(IF(SUM(cost) > 0 , SUM(da23) / SUM(cost), 0), 4) as roi23,
                    ROUND(IF(SUM(cost) > 0 , SUM(da24) / SUM(cost), 0), 4) as roi24,
                    ROUND(IF(SUM(cost) > 0 , SUM(da25) / SUM(cost), 0), 4) as roi25,
                    ROUND(IF(SUM(cost) > 0 , SUM(da26) / SUM(cost), 0), 4) as roi26,
                    ROUND(IF(SUM(cost) > 0 , SUM(da27) / SUM(cost), 0), 4) as roi27,
                    ROUND(IF(SUM(cost) > 0 , SUM(da28) / SUM(cost), 0), 4) as roi28,
                    ROUND(IF(SUM(cost) > 0 , SUM(da29) / SUM(cost), 0), 4) as roi29,
                    ROUND(IF(SUM(cost) > 0 , SUM(da30) / SUM(cost), 0), 4) as roi30,
                    ROUND(IF(SUM(cost) > 0 , SUM(da60) / SUM(cost), 0), 4) as roi60,
                    ROUND(IF(SUM(cost) > 0 , SUM(da90) / SUM(cost), 0), 4) as roi90,
                    ROUND(IF(SUM(cost) > 0 , SUM(m6) / SUM(cost), 0), 4) as roi180,
                    ROUND(IF(SUM(cost) > 0 , SUM(m12) / SUM(cost), 0), 4) as roi1yaer,
                    ROUND(IF(SUM(cost) > 0 , SUM(total) / SUM(cost), 0), 4) as roi_total,
                """ + getPitcherGameDataDayTotalDayNsql() +
                """
                        FROM
                            game_ads.ads_game_pitcher_day
                        """;
    }

    /**
     * 查询投手游戏每日数据SQL（投手游戏每日-父游戏维度）
     *
     * @return String
     */
    private String pitcherGameDataDaySqlForParent() {
        return """
                SELECT
                    dt,
                    source_system ,
                    pitcher_id ,
                    MAX(pitcher) pitcher ,
                    GROUP_CONCAT(parent_game_name) as game_name ,
                    SUM(cost) as cost,
                    SUM(plan_count) as plan_count,
                    SUM(account_count) as account_count,
                    SUM(agent_count) as agent_count,
                    SUM(register_num) as register_num,
                    ROUND(IF(SUM(register_num) > 0, SUM(cost) / SUM(register_num), 0), 2) as register_cost,
                    SUM(first_role_num) as first_role_num,
                    SUM(new_user_total_role_num) as new_user_total_role_num,
                    SUM(role_num) as role_num,
                    ROUND(IF(SUM(role_num) > 0 , SUM(cost) / SUM(role_num), 0), 2) as role_num_cost,
                    ROUND(IF(SUM(first_role_num) > 0 , SUM(cost) / SUM(first_role_num), 0), 2) as first_role_cost,
                    ROUND(IF(SUM(new_user_total_role_num) > 0 , SUM(cost) / SUM(new_user_total_role_num), 0), 2) as new_user_total_role_cost,
                    ROUND(IF(SUM(register_num) > 0 , SUM(first_role_num) / SUM(register_num), 0), 4) as first_role_rate,
                    ROUND(IF(SUM(register_num) > 0 , SUM(new_user_total_role_num) / SUM(register_num), 0), 4) as new_user_total_role_rate,
                    ROUND(IF(SUM(register_num) > 0 , SUM(role_num) / SUM(register_num), 0), 4) as role_num_rate,
                    SUM(first_new_user_amount_count) as first_new_user_amount_count,
                    SUM(first_new_user_amount_num) as first_new_user_amount_num,
                    SUM(first_new_user_amount) as first_new_user_amount,
                    SUM(amount_count) as amount_count,
                    SUM(amount_num) as amount_num,
                    SUM(amount) as amount,
                    SUM(old_amount_count) as old_amount_count,
                    SUM(old_amount_num) as old_amount_num,
                    SUM(old_amount) as old_amount,
                    SUM(new_user_total_amount_count) as new_user_total_amount_count,
                    SUM(new_user_total_amount_num) as new_user_total_amount_num,
                    SUM(new_user_total_amount) as new_user_total_amount,
                    SUM(buy_new_user_total_amount) as buy_new_user_total_amount,
                    SUM(buy_new_user_total_amount_num) as buy_new_user_total_amount_num,
                    SUM(buy_new_user_total_amount_count) as buy_new_user_total_amount_count,
                    ROUND(IF(SUM(cost) > 0, SUM(first_new_user_amount) / SUM(cost), 0), 4) as first_roi,
                    ROUND(IF(SUM(cost) > 0, SUM(buy_new_user_total_amount) / SUM(cost), 0), 4) as buy_roi,
                    ROUND(IF(SUM(cost) > 0, SUM(new_user_total_amount) / SUM(cost), 0), 4) as today_roi,
                    (SUM(new_user_total_amount) - SUM(cost)) as gross_profit,
                    ROUND(IF(SUM(register_num) > 0, SUM(first_new_user_amount_num) / SUM(register_num), 0), 4) as first_rate,
                    ROUND(IF(SUM(register_num) > 0, SUM(buy_new_user_total_amount_num) / SUM(register_num), 0), 4) as buy_user_rate,
                    ROUND(IF(SUM(register_num) > 0, SUM(new_user_total_amount_num) / SUM(register_num), 0), 4) as today_rate,
                    ROUND(IF(SUM(amount_num) > 0, SUM(first_new_user_amount_num) / SUM(amount_num), 0), 4) as new_user_amount_ratio,
                    ROUND(IF(SUM(first_new_user_amount_count) > 0 , SUM(first_new_user_amount) / SUM(first_new_user_amount_count), 0), 2) as first_avg,
                    ROUND(IF(SUM(buy_new_user_total_amount_count) > 0 , SUM(buy_new_user_total_amount) / SUM(buy_new_user_total_amount_count), 0), 2) as buy_avg,
                    ROUND(IF(SUM(new_user_total_amount_count) > 0 , SUM(new_user_total_amount) / SUM(new_user_total_amount_count), 0), 2) as today_avg,
                    ROUND(IF(SUM(amount_count) > 0 , SUM(amount) / SUM(amount_count), 0), 2) as paper_avg,
                    ROUND(IF(SUM(first_new_user_amount_num) > 0 , SUM(cost) / SUM(first_new_user_amount_num), 0), 2) as first_amount_cost,
                    ROUND(IF(SUM(buy_new_user_total_amount_num) > 0 , SUM(cost) / SUM(buy_new_user_total_amount_num), 0), 2) as buy_amount_cost,
                    ROUND(IF(SUM(new_user_total_amount_num) > 0 , SUM(cost) / SUM(new_user_total_amount_num), 0), 2) as today_amount_cost,
                    SUM(reg_order_user_again) as reg_order_user_again,
                    ROUND(IF(SUM(new_user_total_amount_num) > 0 ,SUM(reg_order_user_again) / SUM(new_user_total_amount_num), 0), 4) as today_again_rate,
                    ROUND(IF(SUM(register_num) > 0 , SUM(new_user_total_amount) / SUM(register_num), 0), 2) as new_reg_arpu,
                    ROUND(IF(SUM(first_new_user_amount_num) > 0 , SUM(first_new_user_amount) / SUM(first_new_user_amount_num), 0), 2) as first_arpu,
                    ROUND(IF(SUM(new_user_total_amount_num) > 0 , SUM(new_user_total_amount) / SUM(new_user_total_amount_num), 0), 2) as today_arpu,
                    ROUND(IF(SUM(amount_num) > 0 , SUM(amount) / SUM(amount_num), 0), 2) as paper_arpu,
                    SUM(hundred_user_num) as hundred_user_num,
                    ROUND(IF(SUM(hundred_user_num) > 0 , SUM(cost) / SUM(hundred_user_num), 0), 2) as hundred_user_num_cost,
                    
                    ROUND(IF(SUM(register_num) > 0 , SUM(first_new_user_amount) / SUM(register_num), 0), 2) as first_reg_arppu,
                    
                    ROUND(IF(SUM(cost) > 0 , SUM(da1) / SUM(cost), 0), 4) as roi1,
                    ROUND(IF(SUM(cost) > 0 , SUM(da2) / SUM(cost), 0), 4) as roi2,
                    ROUND(IF(SUM(cost) > 0 , SUM(da3) / SUM(cost), 0), 4) as roi3,
                    ROUND(IF(SUM(cost) > 0 , SUM(da4) / SUM(cost), 0), 4) as roi4,
                    ROUND(IF(SUM(cost) > 0 , SUM(da5) / SUM(cost), 0), 4) as roi5,
                    ROUND(IF(SUM(cost) > 0 , SUM(da6) / SUM(cost), 0), 4) as roi6,
                    ROUND(IF(SUM(cost) > 0 , SUM(da7) / SUM(cost), 0), 4) as roi7,
                    ROUND(IF(SUM(cost) > 0 , SUM(da8) / SUM(cost), 0), 4) as roi8,
                    ROUND(IF(SUM(cost) > 0 , SUM(da9) / SUM(cost), 0), 4) as roi9,
                    ROUND(IF(SUM(cost) > 0 , SUM(da10) / SUM(cost), 0), 4) as roi10,
                    ROUND(IF(SUM(cost) > 0 , SUM(da11) / SUM(cost), 0), 4) as roi11,
                    ROUND(IF(SUM(cost) > 0 , SUM(da12) / SUM(cost), 0), 4) as roi12,
                    ROUND(IF(SUM(cost) > 0 , SUM(da13) / SUM(cost), 0), 4) as roi13,
                    ROUND(IF(SUM(cost) > 0 , SUM(da14) / SUM(cost), 0), 4) as roi14,
                    ROUND(IF(SUM(cost) > 0 , SUM(da15) / SUM(cost), 0), 4) as roi15,
                    ROUND(IF(SUM(cost) > 0 , SUM(da16) / SUM(cost), 0), 4) as roi16,
                    ROUND(IF(SUM(cost) > 0 , SUM(da17) / SUM(cost), 0), 4) as roi17,
                    ROUND(IF(SUM(cost) > 0 , SUM(da18) / SUM(cost), 0), 4) as roi18,
                    ROUND(IF(SUM(cost) > 0 , SUM(da19) / SUM(cost), 0), 4) as roi19,
                    ROUND(IF(SUM(cost) > 0 , SUM(da20) / SUM(cost), 0), 4) as roi20,
                    ROUND(IF(SUM(cost) > 0 , SUM(da21) / SUM(cost), 0), 4) as roi21,
                    ROUND(IF(SUM(cost) > 0 , SUM(da22) / SUM(cost), 0), 4) as roi22,
                    ROUND(IF(SUM(cost) > 0 , SUM(da23) / SUM(cost), 0), 4) as roi23,
                    ROUND(IF(SUM(cost) > 0 , SUM(da24) / SUM(cost), 0), 4) as roi24,
                    ROUND(IF(SUM(cost) > 0 , SUM(da25) / SUM(cost), 0), 4) as roi25,
                    ROUND(IF(SUM(cost) > 0 , SUM(da26) / SUM(cost), 0), 4) as roi26,
                    ROUND(IF(SUM(cost) > 0 , SUM(da27) / SUM(cost), 0), 4) as roi27,
                    ROUND(IF(SUM(cost) > 0 , SUM(da28) / SUM(cost), 0), 4) as roi28,
                    ROUND(IF(SUM(cost) > 0 , SUM(da29) / SUM(cost), 0), 4) as roi29,
                    ROUND(IF(SUM(cost) > 0 , SUM(da30) / SUM(cost), 0), 4) as roi30,
                    ROUND(IF(SUM(cost) > 0 , SUM(da60) / SUM(cost), 0), 4) as roi60,
                    ROUND(IF(SUM(cost) > 0 , SUM(da90) / SUM(cost), 0), 4) as roi90,
                    ROUND(IF(SUM(cost) > 0 , SUM(m6) / SUM(cost), 0), 4) as roi180,
                    ROUND(IF(SUM(cost) > 0 , SUM(m12) / SUM(cost), 0), 4) as roi1yaer,
                    ROUND(IF(SUM(cost) > 0 , SUM(total) / SUM(cost), 0), 4) as roi_total,
                """ + getPitcherGameDataDayTotalDayNsql() +
                """
                        FROM
                            game_ads_parent.ads_game_pitcher_day_parent
                        """;
    }

    /**
     * 查询投手每日数据总计SQL（投手每日总计）
     *
     * @param tableName 查询的表名
     * @return String
     */
    private String pitcherDataDayTotalSql(String tableName) {
        return """
                SELECT
                    SUM(cost) as cost,
                    SUM(plan_count) as plan_count,
                    SUM(account_count) as account_count,
                    SUM(agent_count) as agent_count,
                    SUM(register_num) as register_num,
                    ROUND(IF(SUM(register_num) > 0, SUM(cost) / SUM(register_num), 0), 2) as register_cost,
                    SUM(first_role_num) as first_role_num,
                    SUM(new_user_total_role_num) as new_user_total_role_num,
                    SUM(role_num) as role_num,
                    ROUND(IF(SUM(role_num) > 0 , SUM(cost) / SUM(role_num), 0), 2) as role_num_cost,
                    ROUND(IF(SUM(first_role_num) > 0 , SUM(cost) / SUM(first_role_num), 0), 2) as first_role_cost,
                    ROUND(IF(SUM(new_user_total_role_num) > 0 , SUM(cost) / SUM(new_user_total_role_num), 0), 2) as new_user_total_role_cost,
                    ROUND(IF(SUM(register_num) > 0 , SUM(first_role_num) / SUM(register_num), 0), 4) as first_role_rate,
                    ROUND(IF(SUM(register_num) > 0 , SUM(new_user_total_role_num) / SUM(register_num), 0), 4) as new_user_total_role_rate,
                    ROUND(IF(SUM(register_num) > 0 , SUM(role_num) / SUM(register_num), 0), 4) as role_num_rate,
                    SUM(first_new_user_amount_count) as first_new_user_amount_count,
                    SUM(first_new_user_amount_num) as first_new_user_amount_num,
                    SUM(first_new_user_amount) as first_new_user_amount,
                    SUM(amount_count) as amount_count,
                    SUM(amount_num) as amount_num,
                    SUM(amount) as amount,
                    SUM(old_amount_count) as old_amount_count,
                    SUM(old_amount_num) as old_amount_num,
                    SUM(old_amount) as old_amount,
                    SUM(new_user_total_amount_count) as new_user_total_amount_count,
                    SUM(new_user_total_amount_num) as new_user_total_amount_num,
                    SUM(new_user_total_amount) as new_user_total_amount,
                    SUM(buy_new_user_total_amount) as buy_new_user_total_amount,
                    SUM(buy_new_user_total_amount_num) as buy_new_user_total_amount_num,
                    SUM(buy_new_user_total_amount_count) as buy_new_user_total_amount_count,
                    ROUND(IF(SUM(cost) > 0, SUM(first_new_user_amount) / SUM(cost), 0), 4) as first_roi,
                    ROUND(IF(SUM(cost) > 0, SUM(buy_new_user_total_amount) / SUM(cost), 0), 4) as buy_roi,
                    ROUND(IF(SUM(cost) > 0, SUM(new_user_total_amount) / SUM(cost), 0), 4) as today_roi,
                    (SUM(new_user_total_amount) - SUM(cost)) as gross_profit,
                    ROUND(IF(SUM(register_num) > 0, SUM(first_new_user_amount_num) / SUM(register_num), 0), 4) as first_rate,
                    ROUND(IF(SUM(register_num) > 0, SUM(buy_new_user_total_amount_num) / SUM(register_num), 0), 4) as buy_user_rate,
                    ROUND(IF(SUM(register_num) > 0, SUM(new_user_total_amount_num) / SUM(register_num), 0), 4) as today_rate,
                    ROUND(IF(SUM(amount_num) > 0, SUM(first_new_user_amount_num) / SUM(amount_num), 0), 4) as new_user_amount_ratio,
                    ROUND(IF(SUM(first_new_user_amount_count) > 0 , SUM(first_new_user_amount) / SUM(first_new_user_amount_count), 0), 2) as first_avg,
                    ROUND(IF(SUM(buy_new_user_total_amount_count) > 0 , SUM(buy_new_user_total_amount) / SUM(buy_new_user_total_amount_count), 0), 2) as buy_avg,
                    ROUND(IF(SUM(new_user_total_amount_count) > 0 , SUM(new_user_total_amount) / SUM(new_user_total_amount_count), 0), 2) as today_avg,
                    ROUND(IF(SUM(amount_count) > 0 , SUM(amount) / SUM(amount_count), 0), 2) as paper_avg,
                    ROUND(IF(SUM(first_new_user_amount_num) > 0 , SUM(cost) / SUM(first_new_user_amount_num), 0), 2) as first_amount_cost,
                    ROUND(IF(SUM(buy_new_user_total_amount_num) > 0 , SUM(cost) / SUM(buy_new_user_total_amount_num), 0), 2) as buy_amount_cost,
                    ROUND(IF(SUM(new_user_total_amount_num) > 0 , SUM(cost) / SUM(new_user_total_amount_num), 0), 2) as today_amount_cost,
                    SUM(reg_order_user_again) as reg_order_user_again,
                    ROUND(IF(SUM(new_user_total_amount_num) > 0 ,SUM(reg_order_user_again) / SUM(new_user_total_amount_num), 0), 4) as today_again_rate,
                    ROUND(IF(SUM(register_num) > 0 , SUM(new_user_total_amount) / SUM(register_num), 0), 2) as new_reg_arpu,
                    ROUND(IF(SUM(first_new_user_amount_num) > 0 , SUM(first_new_user_amount) / SUM(first_new_user_amount_num), 0), 2) as first_arpu,
                    ROUND(IF(SUM(new_user_total_amount_num) > 0 , SUM(new_user_total_amount) / SUM(new_user_total_amount_num), 0), 2) as today_arpu,
                    ROUND(IF(SUM(amount_num) > 0 , SUM(amount) / SUM(amount_num), 0), 2) as paper_arpu,
                    SUM(hundred_user_num) as hundred_user_num,
                    SUM(total_five_hundred_user_count) AS total_five_hundred_user_count,
                    ROUND(IF(SUM(hundred_user_num) > 0 , SUM(cost) / SUM(hundred_user_num), 0), 2) as hundred_user_num_cost,
                    ROUND(IF(SUM(cost) > 0 , SUM(da1) / SUM(cost), 0), 4) as roi1,
                    ROUND(IF(SUM(cost) > 0 , SUM(da2) / SUM(cost), 0), 4) as roi2,
                    ROUND(IF(SUM(cost) > 0 , SUM(da3) / SUM(cost), 0), 4) as roi3,
                    ROUND(IF(SUM(cost) > 0 , SUM(da4) / SUM(cost), 0), 4) as roi4,
                    ROUND(IF(SUM(cost) > 0 , SUM(da5) / SUM(cost), 0), 4) as roi5,
                    ROUND(IF(SUM(cost) > 0 , SUM(da6) / SUM(cost), 0), 4) as roi6,
                    ROUND(IF(SUM(cost) > 0 , SUM(da7) / SUM(cost), 0), 4) as roi7,
                    ROUND(IF(SUM(cost) > 0 , SUM(da8) / SUM(cost), 0), 4) as roi8,
                    ROUND(IF(SUM(cost) > 0 , SUM(da9) / SUM(cost), 0), 4) as roi9,
                    ROUND(IF(SUM(cost) > 0 , SUM(da10) / SUM(cost), 0), 4) as roi10,
                    ROUND(IF(SUM(cost) > 0 , SUM(da11) / SUM(cost), 0), 4) as roi11,
                    ROUND(IF(SUM(cost) > 0 , SUM(da12) / SUM(cost), 0), 4) as roi12,
                    ROUND(IF(SUM(cost) > 0 , SUM(da13) / SUM(cost), 0), 4) as roi13,
                    ROUND(IF(SUM(cost) > 0 , SUM(da14) / SUM(cost), 0), 4) as roi14,
                    ROUND(IF(SUM(cost) > 0 , SUM(da15) / SUM(cost), 0), 4) as roi15,
                    ROUND(IF(SUM(cost) > 0 , SUM(da16) / SUM(cost), 0), 4) as roi16,
                    ROUND(IF(SUM(cost) > 0 , SUM(da17) / SUM(cost), 0), 4) as roi17,
                    ROUND(IF(SUM(cost) > 0 , SUM(da18) / SUM(cost), 0), 4) as roi18,
                    ROUND(IF(SUM(cost) > 0 , SUM(da19) / SUM(cost), 0), 4) as roi19,
                    ROUND(IF(SUM(cost) > 0 , SUM(da20) / SUM(cost), 0), 4) as roi20,
                    ROUND(IF(SUM(cost) > 0 , SUM(da21) / SUM(cost), 0), 4) as roi21,
                    ROUND(IF(SUM(cost) > 0 , SUM(da22) / SUM(cost), 0), 4) as roi22,
                    ROUND(IF(SUM(cost) > 0 , SUM(da23) / SUM(cost), 0), 4) as roi23,
                    ROUND(IF(SUM(cost) > 0 , SUM(da24) / SUM(cost), 0), 4) as roi24,
                    ROUND(IF(SUM(cost) > 0 , SUM(da25) / SUM(cost), 0), 4) as roi25,
                    ROUND(IF(SUM(cost) > 0 , SUM(da26) / SUM(cost), 0), 4) as roi26,
                    ROUND(IF(SUM(cost) > 0 , SUM(da27) / SUM(cost), 0), 4) as roi27,
                    ROUND(IF(SUM(cost) > 0 , SUM(da28) / SUM(cost), 0), 4) as roi28,
                    ROUND(IF(SUM(cost) > 0 , SUM(da29) / SUM(cost), 0), 4) as roi29,
                    ROUND(IF(SUM(cost) > 0 , SUM(da30) / SUM(cost), 0), 4) as roi30,
                    ROUND(IF(SUM(cost) > 0 , SUM(da60) / SUM(cost), 0), 4) as roi60,
                    ROUND(IF(SUM(cost) > 0 , SUM(da90) / SUM(cost), 0), 4) as roi90,
                    ROUND(IF(SUM(cost) > 0 , SUM(m6) / SUM(cost), 0), 4) as roi180,
                    ROUND(IF(SUM(cost) > 0 , SUM(m12) / SUM(cost), 0), 4) as roi1yaer,
                    ROUND(IF(SUM(cost) > 0 , SUM(total) / SUM(cost), 0), 4) as roi_total,
                """ + getPitcherGameDataDayTotalDayNsql() +
                """
                        FROM 
                        """ + tableName;
    }

    /**
     * 查询投手游戏每日数据总计SQL（投手游戏每日总计）
     *
     * @param tableName 查询的表名
     * @return String
     */
    private String pitcherGameDataDayTotalSql(String tableName) {
        return """
                SELECT
                    SUM(cost) as cost,
                    SUM(plan_count) as plan_count,
                    SUM(account_count) as account_count,
                    SUM(agent_count) as agent_count,
                    SUM(register_num) as register_num,
                    ROUND(IF(SUM(register_num) > 0, SUM(cost) / SUM(register_num), 0), 2) as register_cost,
                    SUM(first_role_num) as first_role_num,
                    SUM(new_user_total_role_num) as new_user_total_role_num,
                    SUM(role_num) as role_num,
                    ROUND(IF(SUM(role_num) > 0 , SUM(cost) / SUM(role_num), 0), 2) as role_num_cost,
                    ROUND(IF(SUM(first_role_num) > 0 , SUM(cost) / SUM(first_role_num), 0), 2) as first_role_cost,
                    ROUND(IF(SUM(new_user_total_role_num) > 0 , SUM(cost) / SUM(new_user_total_role_num), 0), 2) as new_user_total_role_cost,
                    ROUND(IF(SUM(register_num) > 0 , SUM(first_role_num) / SUM(register_num), 0), 4) as first_role_rate,
                    ROUND(IF(SUM(register_num) > 0 , SUM(new_user_total_role_num) / SUM(register_num), 0), 4) as new_user_total_role_rate,
                    ROUND(IF(SUM(register_num) > 0 , SUM(role_num) / SUM(register_num), 0), 4) as role_num_rate,
                    SUM(first_new_user_amount_count) as first_new_user_amount_count,
                    SUM(first_new_user_amount_num) as first_new_user_amount_num,
                    SUM(first_new_user_amount) as first_new_user_amount,
                    SUM(amount_count) as amount_count,
                    SUM(amount_num) as amount_num,
                    SUM(amount) as amount,
                    SUM(old_amount_count) as old_amount_count,
                    SUM(old_amount_num) as old_amount_num,
                    SUM(old_amount) as old_amount,
                    SUM(new_user_total_amount_count) as new_user_total_amount_count,
                    SUM(new_user_total_amount_num) as new_user_total_amount_num,
                    SUM(new_user_total_amount) as new_user_total_amount,
                    SUM(buy_new_user_total_amount) as buy_new_user_total_amount,
                    SUM(buy_new_user_total_amount_num) as buy_new_user_total_amount_num,
                    SUM(buy_new_user_total_amount_count) as buy_new_user_total_amount_count,
                    ROUND(IF(SUM(cost) > 0, SUM(first_new_user_amount) / SUM(cost), 0), 4) as first_roi,
                    ROUND(IF(SUM(cost) > 0, SUM(buy_new_user_total_amount) / SUM(cost), 0), 4) as buy_roi,
                    ROUND(IF(SUM(cost) > 0, SUM(new_user_total_amount) / SUM(cost), 0), 4) as today_roi,
                    (SUM(new_user_total_amount) - SUM(cost)) as gross_profit,
                    ROUND(IF(SUM(register_num) > 0, SUM(first_new_user_amount_num) / SUM(register_num), 0), 4) as first_rate,
                    ROUND(IF(SUM(register_num) > 0, SUM(buy_new_user_total_amount_num) / SUM(register_num), 0), 4) as buy_user_rate,
                    ROUND(IF(SUM(register_num) > 0, SUM(new_user_total_amount_num) / SUM(register_num), 0), 4) as today_rate,
                    ROUND(IF(SUM(amount_num) > 0, SUM(first_new_user_amount_num) / SUM(amount_num), 0), 4) as new_user_amount_ratio,
                    ROUND(IF(SUM(first_new_user_amount_count) > 0 , SUM(first_new_user_amount) / SUM(first_new_user_amount_count), 0), 2) as first_avg,
                    ROUND(IF(SUM(buy_new_user_total_amount_count) > 0 , SUM(buy_new_user_total_amount) / SUM(buy_new_user_total_amount_count), 0), 2) as buy_avg,
                    ROUND(IF(SUM(new_user_total_amount_count) > 0 , SUM(new_user_total_amount) / SUM(new_user_total_amount_count), 0), 2) as today_avg,
                    ROUND(IF(SUM(amount_count) > 0 , SUM(amount) / SUM(amount_count), 0), 2) as paper_avg,
                    ROUND(IF(SUM(first_new_user_amount_num) > 0 , SUM(cost) / SUM(first_new_user_amount_num), 0), 2) as first_amount_cost,
                    ROUND(IF(SUM(buy_new_user_total_amount_num) > 0 , SUM(cost) / SUM(buy_new_user_total_amount_num), 0), 2) as buy_amount_cost,
                    ROUND(IF(SUM(new_user_total_amount_num) > 0 , SUM(cost) / SUM(new_user_total_amount_num), 0), 2) as today_amount_cost,
                    SUM(reg_order_user_again) as reg_order_user_again,
                    ROUND(IF(SUM(new_user_total_amount_num) > 0 ,SUM(reg_order_user_again) / SUM(new_user_total_amount_num), 0), 4) as today_again_rate,
                    ROUND(IF(SUM(register_num) > 0 , SUM(new_user_total_amount) / SUM(register_num), 0), 2) as new_reg_arpu,
                    ROUND(IF(SUM(first_new_user_amount_num) > 0 , SUM(first_new_user_amount) / SUM(first_new_user_amount_num), 0), 2) as first_arpu,
                    ROUND(IF(SUM(new_user_total_amount_num) > 0 , SUM(new_user_total_amount) / SUM(new_user_total_amount_num), 0), 2) as today_arpu,
                    ROUND(IF(SUM(amount_num) > 0 , SUM(amount) / SUM(amount_num), 0), 2) as paper_arpu,
                    SUM(hundred_user_num) as hundred_user_num,
                    ROUND(IF(SUM(hundred_user_num) > 0 , SUM(cost) / SUM(hundred_user_num), 0), 2) as hundred_user_num_cost,
                    
                    ROUND(IF(SUM(register_num) > 0 , SUM(first_new_user_amount) / SUM(register_num), 0), 2) as first_reg_arppu,
                    
                    ROUND(IF(SUM(cost) > 0 , SUM(da1) / SUM(cost), 0), 4) as roi1,
                    ROUND(IF(SUM(cost) > 0 , SUM(da2) / SUM(cost), 0), 4) as roi2,
                    ROUND(IF(SUM(cost) > 0 , SUM(da3) / SUM(cost), 0), 4) as roi3,
                    ROUND(IF(SUM(cost) > 0 , SUM(da4) / SUM(cost), 0), 4) as roi4,
                    ROUND(IF(SUM(cost) > 0 , SUM(da5) / SUM(cost), 0), 4) as roi5,
                    ROUND(IF(SUM(cost) > 0 , SUM(da6) / SUM(cost), 0), 4) as roi6,
                    ROUND(IF(SUM(cost) > 0 , SUM(da7) / SUM(cost), 0), 4) as roi7,
                    ROUND(IF(SUM(cost) > 0 , SUM(da8) / SUM(cost), 0), 4) as roi8,
                    ROUND(IF(SUM(cost) > 0 , SUM(da9) / SUM(cost), 0), 4) as roi9,
                    ROUND(IF(SUM(cost) > 0 , SUM(da10) / SUM(cost), 0), 4) as roi10,
                    ROUND(IF(SUM(cost) > 0 , SUM(da11) / SUM(cost), 0), 4) as roi11,
                    ROUND(IF(SUM(cost) > 0 , SUM(da12) / SUM(cost), 0), 4) as roi12,
                    ROUND(IF(SUM(cost) > 0 , SUM(da13) / SUM(cost), 0), 4) as roi13,
                    ROUND(IF(SUM(cost) > 0 , SUM(da14) / SUM(cost), 0), 4) as roi14,
                    ROUND(IF(SUM(cost) > 0 , SUM(da15) / SUM(cost), 0), 4) as roi15,
                    ROUND(IF(SUM(cost) > 0 , SUM(da16) / SUM(cost), 0), 4) as roi16,
                    ROUND(IF(SUM(cost) > 0 , SUM(da17) / SUM(cost), 0), 4) as roi17,
                    ROUND(IF(SUM(cost) > 0 , SUM(da18) / SUM(cost), 0), 4) as roi18,
                    ROUND(IF(SUM(cost) > 0 , SUM(da19) / SUM(cost), 0), 4) as roi19,
                    ROUND(IF(SUM(cost) > 0 , SUM(da20) / SUM(cost), 0), 4) as roi20,
                    ROUND(IF(SUM(cost) > 0 , SUM(da21) / SUM(cost), 0), 4) as roi21,
                    ROUND(IF(SUM(cost) > 0 , SUM(da22) / SUM(cost), 0), 4) as roi22,
                    ROUND(IF(SUM(cost) > 0 , SUM(da23) / SUM(cost), 0), 4) as roi23,
                    ROUND(IF(SUM(cost) > 0 , SUM(da24) / SUM(cost), 0), 4) as roi24,
                    ROUND(IF(SUM(cost) > 0 , SUM(da25) / SUM(cost), 0), 4) as roi25,
                    ROUND(IF(SUM(cost) > 0 , SUM(da26) / SUM(cost), 0), 4) as roi26,
                    ROUND(IF(SUM(cost) > 0 , SUM(da27) / SUM(cost), 0), 4) as roi27,
                    ROUND(IF(SUM(cost) > 0 , SUM(da28) / SUM(cost), 0), 4) as roi28,
                    ROUND(IF(SUM(cost) > 0 , SUM(da29) / SUM(cost), 0), 4) as roi29,
                    ROUND(IF(SUM(cost) > 0 , SUM(da30) / SUM(cost), 0), 4) as roi30,
                    ROUND(IF(SUM(cost) > 0 , SUM(da60) / SUM(cost), 0), 4) as roi60,
                    ROUND(IF(SUM(cost) > 0 , SUM(da90) / SUM(cost), 0), 4) as roi90,
                    ROUND(IF(SUM(cost) > 0 , SUM(m6) / SUM(cost), 0), 4) as roi180,
                    ROUND(IF(SUM(cost) > 0 , SUM(m12) / SUM(cost), 0), 4) as roi1yaer,
                    ROUND(IF(SUM(cost) > 0 , SUM(total) / SUM(cost), 0), 4) as roi_total,
                """ + getPitcherGameDataDayTotalDayNsql() +
                """
                        FROM 
                        """ + tableName;
    }

    /**
     * 投手游戏每日数据总计付费趋势sql（投手游戏每日总计、投手每日总计共用）
     *
     * @return String : Dn的充值金额 / D1-Dn的充值总金额 / Dn的充值人数 /当前消耗(剔除不存在的天数数据) /D1充值金额总和(剔除不存在的天数数据)
     */
    private String getPitcherGameDataDayTotalDayNsql() {
        StringBuilder totalDayN = new StringBuilder(StringUtils.EMPTY);
        for (int day = 1; day <= 90; day++) {
            totalDayN.append("""
                    CONCAT(
                        SUM(IF(DATE_ADD(dt, INTERVAL %s day) <= LocalDate.now(), SPLIT_PART(amount_d%s , '/', 1), 0)),'/',
                        SUM(SPLIT_PART(amount_d%s, '/', 6)),'/',
                        SUM(SPLIT_PART(amount_d%s, '/', 2)),'/',
                        SUM(IF(DATE_ADD(dt, INTERVAL %s day) <= LocalDate.now(), cost, 0)),'/',
                        SUM(IF(DATE_ADD(dt, INTERVAL %s day) <= LocalDate.now(), SPLIT_PART(amount_d1 , '/', 1), 0))
                    ) AS amount_d%s ,
                       """.formatted(day - 1, day, day, day, day - 1, day - 1, day));
        }
        for (int month = 4; month <= 12; month++) {
            totalDayN.append("""
                    CONCAT(
                        SUM(IF(DATE_ADD(dt, INTERVAL %s month) <= LocalDate.now(), SPLIT_PART(amount_m%s , '/', 1), 0)),'/',
                        SUM(SPLIT_PART(amount_m%s, '/', 6)),'/',
                        SUM(SPLIT_PART(amount_m%s, '/', 2)),'/',
                        SUM(IF(DATE_ADD(dt, INTERVAL %s month) <= LocalDate.now(), cost, 0)),'/',
                        SUM(IF(DATE_ADD(dt, INTERVAL %s month) <= LocalDate.now(), SPLIT_PART(amount_d1 , '/', 1), 0))
                    ) AS amount_m%s ,
                       """.formatted(month, month, month, month, month, month, month));
        }
        totalDayN.append("""
                CONCAT(
                    SUM(SPLIT_PART(amount_sum, '/', 1)), '/',
                    SUM(SPLIT_PART(amount_sum, '/', 6)), '/',
                    SUM(SPLIT_PART(amount_sum, '/', 2)), '/',
                    SUM(cost), '/',
                    SUM(SPLIT_PART(amount_d1 , '/', 1))
                    ) AS amount_sum
                """);
        //返回结果
        return totalDayN.toString();
    }

    /**
     * 查询投手总数据SQL（投手总数据）
     *
     * @param criA       主表查询条件
     * @param criB       账面相关查询条件
     * @param criAmount  账面充值人数查询条件
     * @param criNewUser 新用户相关查询条件
     * @param criRoleNum 创角人数相关查询条件
     * @return String
     */
    private String pitcherDataTotalSql(Criteria criA, Criteria criB, Criteria criAmount, Criteria criNewUser, Criteria criRoleNum) {
        return """
                SELECT
                	a.*,
                	IFNULL(amount, 0) as amount,
                	IFNULL(amount_count, 0) as amount_count,
                	IFNULL(amount_num, 0) as amount_num,
                	IFNULL(paper_avg, 0) as paper_avg,
                	ROUND(IF(amount_num > 0 , IFNULL(amount, 0) / amount_num, 0), 2) as paper_arpu,
                	IFNULL(new_user_amount_num, 0) as new_user_amount_num,
                	ROUND(IF(new_user_amount_num > 0, IFNULL(cost, 0) / new_user_amount_num, 0), 2) as new_user_amount_cost,
                	ROUND(IF(register_num > 0, IFNULL(new_user_amount_num, 0) / register_num, 0), 4) as new_user_amount_rate,
                	ROUND(IF(amount_num > 0, IFNULL(new_user_amount_num, 0) / amount_num, 0), 4) as new_user_amount_ratio,
                	IFNULL(new_user_order_again, 0) as new_user_order_again,
                	ROUND(IF(new_user_total_amount_num > 0, IFNULL(new_user_order_again, 0) / new_user_total_amount_num, 0), 4) as new_user_again_rate,
                	IFNULL(role_num, 0) as role_num,
                	ROUND(IF(register_num > 0, IFNULL(role_num, 0) / register_num, 0), 4) as role_num_rate,
                	ROUND(IF(role_num > 0, IFNULL(cost, 0) / role_num, 0), 2) as role_num_cost,
                	new_user_total_zfb_amount,
                 	new_user_total_wx_amount,
                 	new_user_total_mds_amount
                FROM (
                	SELECT
                		pitcher_id,
                		MAX(pitcher) as pitcher,
                		SUM(cost) as cost,
                		SUM(plan_count) as plan_count,
                		SUM(account_count) as account_count,
                		SUM(agent_count) as agent_count,
                		SUM(register_num) as register_num,
                		ROUND(IF(SUM(register_num) > 0, SUM(cost) / SUM(register_num), 0), 2) as register_cost,
                		SUM(first_role_num) as first_role_num,
                		SUM(new_user_total_role_num) as new_user_total_role_num,
                		ROUND(IF(SUM(first_role_num) > 0 , SUM(cost) / SUM(first_role_num), 0), 2) as first_role_cost,
                		ROUND(IF(SUM(new_user_total_role_num) > 0 , SUM(cost) / SUM(new_user_total_role_num), 0), 2) as new_user_total_role_cost,
                		ROUND(IF(SUM(register_num) > 0 , SUM(first_role_num) / SUM(register_num), 0), 4) as first_role_rate,
                		ROUND(IF(SUM(register_num) > 0 , SUM(new_user_total_role_num) / SUM(register_num), 0), 4) as new_user_total_role_rate,
                		SUM(first_new_user_amount_count) as first_new_user_amount_count,
                		SUM(first_new_user_amount_num) as first_new_user_amount_num,
                		SUM(first_new_user_amount) as first_new_user_amount,
                		SUM(new_user_total_amount_count) as new_user_total_amount_count,
                		SUM(new_user_total_amount_num) as new_user_total_amount_num,
                		SUM(new_user_total_amount) as new_user_total_amount,
                		SUM(buy_new_user_total_amount) as buy_new_user_total_amount,
                		SUM(buy_new_user_total_amount_num) as buy_new_user_total_amount_num,
                		SUM(buy_new_user_total_amount_count) as buy_new_user_total_amount_count,
                		ROUND(IF(SUM(cost) > 0, SUM(first_new_user_amount) / SUM(cost), 0), 4) as first_roi,
                		ROUND(IF(SUM(cost) > 0, SUM(buy_new_user_total_amount) / SUM(cost), 0), 4) as buy_roi,
                		ROUND(IF(SUM(cost) > 0, SUM(new_user_total_amount) / SUM(cost), 0), 4) as today_roi,
                		(SUM(new_user_total_amount) - SUM(cost)) as gross_profit,
                		ROUND(IF(SUM(register_num) > 0, SUM(first_new_user_amount_num) / SUM(register_num), 0), 4) as first_rate,
                		ROUND(IF(SUM(register_num) > 0, SUM(buy_new_user_total_amount_num) / SUM(register_num), 0), 4) as buy_user_rate,
                		ROUND(IF(SUM(register_num) > 0, SUM(new_user_total_amount_num) / SUM(register_num), 0), 4) as today_rate,
                		ROUND(IF(SUM(first_new_user_amount_count) > 0 , SUM(first_new_user_amount) / SUM(first_new_user_amount_count), 0), 2) as first_avg,
                		ROUND(IF(SUM(buy_new_user_total_amount_count) > 0 , SUM(buy_new_user_total_amount) / SUM(buy_new_user_total_amount_count), 0), 2) as buy_avg,
                		ROUND(IF(SUM(new_user_total_amount_count) > 0 , SUM(new_user_total_amount) / SUM(new_user_total_amount_count), 0), 2) as today_avg,
                		ROUND(IF(SUM(first_new_user_amount_num) > 0 , SUM(cost) / SUM(first_new_user_amount_num), 0), 2) as first_amount_cost,
                		ROUND(IF(SUM(buy_new_user_total_amount_num) > 0 , SUM(cost) / SUM(buy_new_user_total_amount_num), 0), 2) as buy_amount_cost,
                		ROUND(IF(SUM(new_user_total_amount_num) > 0 , SUM(cost) / SUM(new_user_total_amount_num), 0), 2) as today_amount_cost,
                		SUM(reg_order_user_again) as reg_order_user_again,
                		ROUND(IF(SUM(new_user_total_amount_num) > 0 ,SUM(reg_order_user_again) / SUM(new_user_total_amount_num), 0), 4) as today_again_rate,
                		ROUND(IF(SUM(register_num) > 0 , SUM(new_user_total_amount) / SUM(register_num), 0), 2) as new_reg_arpu,
                		ROUND(IF(SUM(first_new_user_amount_num) > 0 , SUM(first_new_user_amount) / SUM(first_new_user_amount_num), 0), 2) as first_arpu,
                		ROUND(IF(SUM(new_user_total_amount_num) > 0 , SUM(new_user_total_amount) / SUM(new_user_total_amount_num), 0), 2) as today_arpu,
                		SUM(hundred_user_num) as hundred_user_num,
                    	ROUND(IF(SUM(hundred_user_num) > 0 , SUM(cost) / SUM(hundred_user_num), 0), 2) as hundred_user_num_cost,
                		""" + pitcherDataTotalRoiSql() + """
                    	ROUND(IF(SUM(IF(DATE_ADD(dt, INTERVAL 59 day) <= LocalDate.now(), cost, 0)) > 0 ,
                            SUM(da60) / SUM(IF(DATE_ADD(dt, INTERVAL 59 day) <= LocalDate.now(), cost, 0)), 0), 4) as roi60,
                    	ROUND(IF(SUM(IF(DATE_ADD(dt, INTERVAL 89 day) <= LocalDate.now(), cost, 0)) > 0 ,
                            SUM(da90) / SUM(IF(DATE_ADD(dt, INTERVAL 89 day) <= LocalDate.now(), cost, 0)), 0), 4) as roi90,
                        ROUND(IF(SUM(IF(DATE_ADD(dt, INTERVAL 179 day) <= LocalDate.now(), cost, 0)) > 0 ,
                            SUM(m6) / SUM(IF(DATE_ADD(dt, INTERVAL 179 day) <= LocalDate.now(), cost, 0)), 0), 4) as roi180,
                        ROUND(IF(SUM(IF(DATE_ADD(dt, INTERVAL 1 year) <= LocalDate.now(), cost, 0)) > 0 ,
                            SUM(m12) / SUM(IF(DATE_ADD(dt, INTERVAL 1 year) <= LocalDate.now(), cost, 0)), 0), 4) as roi1yaer,
                    	ROUND(IF(SUM(cost) > 0 , SUM(total) / SUM(cost), 0), 4) as roi_total,
                    	SUM(new_user_total_zfb_amount) AS new_user_total_zfb_amount,
                         	SUM(new_user_total_wx_amount) AS new_user_total_wx_amount,
                         	SUM(new_user_total_mds_amount) AS new_user_total_mds_amount
                    FROM
                    	game_ads.ads_pitcher_day
                """ + criA +
                """
                        ) a
                        LEFT JOIN (
                            SELECT
                                pitcher_id as b_pitcher_id,
                                IFNULL(SUM(amount), 0) as amount,
                                IFNULL(SUM(amount_count), 0) as amount_count,
                                ROUND(IF(SUM(amount_count) > 0 , SUM(amount) / SUM(amount_count), 0), 2) as paper_avg
                            FROM
                                game_ads.ads_pitcher_day
                            """ + criB +
                """
                        ) b ON a.pitcher_id = b.b_pitcher_id
                        LEFT JOIN (
                            SELECT
                                pitcher_id as c_pitcher_id,
                                IFNULL(COUNT(DISTINCT user_id), 0) amount_num
                            FROM
                                game_ads.ads_information
                            """ + criAmount +
                """
                                AND NOT agent_id = 0
                            GROUP BY pitcher_id
                        ) c ON a.pitcher_id = c.c_pitcher_id
                        LEFT JOIN (
                            SELECT
                                pitcher_id as d_pitcher_id,
                                COUNT(DISTINCT user_id) new_user_amount_num
                            FROM
                                game_ads.ads_information
                            """ + criNewUser +
                """
                                AND NOT agent_id = 0
                            GROUP BY
                                pitcher_id
                        ) d ON a.pitcher_id = d.d_pitcher_id
                        LEFT JOIN (
                            SELECT
                                pitcher_id as e_pitcher_id,
                                COUNT(tempA.num) as new_user_order_again
                            FROM (
                                SELECT
                                    pitcher_id,
                                    COUNT(user_id) num
                                FROM
                                    game_ads.ads_information
                                """ + criNewUser +
                """
                                    AND NOT agent_id = 0
                                GROUP BY
                                    user_id,
                                    pitcher_id
                                HAVING
                                    COUNT(user_id) > 1 ) tempA
                            GROUP BY
                                tempA.pitcher_id
                        ) e ON a.pitcher_id = e.e_pitcher_id
                        LEFT JOIN (
                            SELECT
                                user_zx_pitcher_id as f_pitcher_id,
                                COUNT(DISTINCT role_user_id) as role_num
                            FROM
                                game_ads.dw_create_role_detail
                            """ + criRoleNum +
                """
                                AND NOT user_agent_id = 0
                            GROUP BY user_zx_pitcher_id
                        ) f ON a.pitcher_id = f.f_pitcher_id
                        """;
    }

    /**
     * 查询投手总数据SQL（投手总数据）-父游戏维度
     *
     * @param criA       主表查询条件
     * @param criB       账面相关查询条件
     * @param criAmount  账面充值人数查询条件
     * @param criNewUser 新用户相关查询条件
     * @param criRoleNum 创角人数相关查询条件
     * @return String
     */
    private String pitcherDataTotalSqlForParent(Criteria criA, Criteria criB, Criteria criAmount, Criteria criNewUser, Criteria criRoleNum) {
        return """
                SELECT
                	a.*,
                	IFNULL(amount, 0) as amount,
                	IFNULL(amount_count, 0) as amount_count,
                	IFNULL(amount_num, 0) as amount_num,
                	IFNULL(paper_avg, 0) as paper_avg,
                	ROUND(IF(amount_num > 0 , IFNULL(amount, 0) / amount_num, 0), 2) as paper_arpu,
                	IFNULL(new_user_amount_num, 0) as new_user_amount_num,
                	ROUND(IF(new_user_amount_num > 0, IFNULL(cost, 0) / new_user_amount_num, 0), 2) as new_user_amount_cost,
                	ROUND(IF(register_num > 0, IFNULL(new_user_amount_num, 0) / register_num, 0), 4) as new_user_amount_rate,
                	ROUND(IF(amount_num > 0, IFNULL(new_user_amount_num, 0) / amount_num, 0), 4) as new_user_amount_ratio,
                	IFNULL(new_user_order_again, 0) as new_user_order_again,
                	ROUND(IF(new_user_total_amount_num > 0, IFNULL(new_user_order_again, 0) / new_user_total_amount_num, 0), 4) as new_user_again_rate,
                	IFNULL(role_num, 0) as role_num,
                	ROUND(IF(register_num > 0, IFNULL(role_num, 0) / register_num, 0), 4) as role_num_rate,
                	ROUND(IF(role_num > 0, IFNULL(cost, 0) / role_num, 0), 2) as role_num_cost
                FROM (
                	SELECT
                		pitcher_id,
                		MAX(pitcher) as pitcher,
                		SUM(cost) as cost,
                		SUM(plan_count) as plan_count,
                		SUM(account_count) as account_count,
                		SUM(agent_count) as agent_count,
                		SUM(register_num) as register_num,
                		ROUND(IF(SUM(register_num) > 0, SUM(cost) / SUM(register_num), 0), 2) as register_cost,
                		SUM(first_role_num) as first_role_num,
                		SUM(new_user_total_role_num) as new_user_total_role_num,
                		ROUND(IF(SUM(first_role_num) > 0 , SUM(cost) / SUM(first_role_num), 0), 2) as first_role_cost,
                		ROUND(IF(SUM(new_user_total_role_num) > 0 , SUM(cost) / SUM(new_user_total_role_num), 0), 2) as new_user_total_role_cost,
                		ROUND(IF(SUM(register_num) > 0 , SUM(first_role_num) / SUM(register_num), 0), 4) as first_role_rate,
                		ROUND(IF(SUM(register_num) > 0 , SUM(new_user_total_role_num) / SUM(register_num), 0), 4) as new_user_total_role_rate,
                		SUM(first_new_user_amount_count) as first_new_user_amount_count,
                		SUM(first_new_user_amount_num) as first_new_user_amount_num,
                		SUM(first_new_user_amount) as first_new_user_amount,
                		SUM(new_user_total_amount_count) as new_user_total_amount_count,
                		SUM(new_user_total_amount_num) as new_user_total_amount_num,
                		SUM(new_user_total_amount) as new_user_total_amount,
                		SUM(buy_new_user_total_amount) as buy_new_user_total_amount,
                		SUM(buy_new_user_total_amount_num) as buy_new_user_total_amount_num,
                		SUM(buy_new_user_total_amount_count) as buy_new_user_total_amount_count,
                		ROUND(IF(SUM(cost) > 0, SUM(first_new_user_amount) / SUM(cost), 0), 4) as first_roi,
                		ROUND(IF(SUM(cost) > 0, SUM(buy_new_user_total_amount) / SUM(cost), 0), 4) as buy_roi,
                		ROUND(IF(SUM(cost) > 0, SUM(new_user_total_amount) / SUM(cost), 0), 4) as today_roi,
                		(SUM(new_user_total_amount) - SUM(cost)) as gross_profit,
                		ROUND(IF(SUM(register_num) > 0, SUM(first_new_user_amount_num) / SUM(register_num), 0), 4) as first_rate,
                		ROUND(IF(SUM(register_num) > 0, SUM(buy_new_user_total_amount_num) / SUM(register_num), 0), 4) as buy_user_rate,
                		ROUND(IF(SUM(register_num) > 0, SUM(new_user_total_amount_num) / SUM(register_num), 0), 4) as today_rate,
                		ROUND(IF(SUM(first_new_user_amount_count) > 0 , SUM(first_new_user_amount) / SUM(first_new_user_amount_count), 0), 2) as first_avg,
                		ROUND(IF(SUM(buy_new_user_total_amount_count) > 0 , SUM(buy_new_user_total_amount) / SUM(buy_new_user_total_amount_count), 0), 2) as buy_avg,
                		ROUND(IF(SUM(new_user_total_amount_count) > 0 , SUM(new_user_total_amount) / SUM(new_user_total_amount_count), 0), 2) as today_avg,
                		ROUND(IF(SUM(first_new_user_amount_num) > 0 , SUM(cost) / SUM(first_new_user_amount_num), 0), 2) as first_amount_cost,
                		ROUND(IF(SUM(buy_new_user_total_amount_num) > 0 , SUM(cost) / SUM(buy_new_user_total_amount_num), 0), 2) as buy_amount_cost,
                		ROUND(IF(SUM(new_user_total_amount_num) > 0 , SUM(cost) / SUM(new_user_total_amount_num), 0), 2) as today_amount_cost,
                		SUM(reg_order_user_again) as reg_order_user_again,
                		ROUND(IF(SUM(new_user_total_amount_num) > 0 ,SUM(reg_order_user_again) / SUM(new_user_total_amount_num), 0), 4) as today_again_rate,
                		ROUND(IF(SUM(register_num) > 0 , SUM(new_user_total_amount) / SUM(register_num), 0), 2) as new_reg_arpu,
                		ROUND(IF(SUM(first_new_user_amount_num) > 0 , SUM(first_new_user_amount) / SUM(first_new_user_amount_num), 0), 2) as first_arpu,
                		ROUND(IF(SUM(new_user_total_amount_num) > 0 , SUM(new_user_total_amount) / SUM(new_user_total_amount_num), 0), 2) as today_arpu,
                		SUM(hundred_user_num) as hundred_user_num,
                    	ROUND(IF(SUM(hundred_user_num) > 0 , SUM(cost) / SUM(hundred_user_num), 0), 2) as hundred_user_num_cost,
                """ + pitcherDataTotalRoiSql() +
                """
                                ROUND(IF(SUM(IF(DATE_ADD(dt, INTERVAL 59 day) <= LocalDate.now(), cost, 0)) > 0 ,
                                    SUM(da60) / SUM(IF(DATE_ADD(dt, INTERVAL 59 day) <= LocalDate.now(), cost, 0)), 0), 4) as roi60,
                                ROUND(IF(SUM(IF(DATE_ADD(dt, INTERVAL 89 day) <= LocalDate.now(), cost, 0)) > 0 ,
                                    SUM(da90) / SUM(IF(DATE_ADD(dt, INTERVAL 89 day) <= LocalDate.now(), cost, 0)), 0), 4) as roi90,
                                ROUND(IF(SUM(IF(DATE_ADD(dt, INTERVAL 179 day) <= LocalDate.now(), cost, 0)) > 0 ,
                                    SUM(m6) / SUM(IF(DATE_ADD(dt, INTERVAL 179 day) <= LocalDate.now(), cost, 0)), 0), 4) as roi180,
                                ROUND(IF(SUM(IF(DATE_ADD(dt, INTERVAL 1 year) <= LocalDate.now(), cost, 0)) > 0 ,
                                    SUM(m12) / SUM(IF(DATE_ADD(dt, INTERVAL 1 year) <= LocalDate.now(), cost, 0)), 0), 4) as roi1yaer,
                                ROUND(IF(SUM(cost) > 0 , SUM(total) / SUM(cost), 0), 4) as roi_total
                            FROM
                                game_ads_parent.ads_pitcher_day_parent
                        """ + criA +
                """
                        ) a
                        LEFT JOIN (
                            SELECT
                                pitcher_id as b_pitcher_id,
                                IFNULL(SUM(amount), 0) as amount,
                                IFNULL(SUM(amount_count), 0) as amount_count,
                                ROUND(IF(SUM(amount_count) > 0 , SUM(amount) / SUM(amount_count), 0), 2) as paper_avg
                            FROM
                                game_ads_parent.ads_pitcher_day_parent
                        """ + criB +
                """
                        ) b ON a.pitcher_id = b.b_pitcher_id
                        LEFT JOIN (
                            SELECT
                                pitcher_id as c_pitcher_id,
                                IFNULL(COUNT(DISTINCT user_id), 0) amount_num
                            FROM
                                game_ads.ads_information
                        """ + criAmount +
                """
                                AND NOT agent_id = 0
                            GROUP BY pitcher_id
                        ) c ON a.pitcher_id = c.c_pitcher_id
                        LEFT JOIN (
                            SELECT
                                pitcher_id as d_pitcher_id,
                                COUNT(DISTINCT user_id) new_user_amount_num
                            FROM
                                game_ads.ads_information
                        """ + criNewUser +
                """
                                AND NOT agent_id = 0
                            GROUP BY
                                pitcher_id
                        ) d ON a.pitcher_id = d.d_pitcher_id
                        LEFT JOIN (
                            SELECT
                                pitcher_id as e_pitcher_id,
                                COUNT(tempA.num) as new_user_order_again
                            FROM (
                                SELECT
                                    pitcher_id,
                                    COUNT(user_id) num
                                FROM
                                    game_ads.ads_information
                        """ + criNewUser +
                """
                                    AND NOT agent_id = 0
                                GROUP BY
                                    user_id,
                                    pitcher_id
                                HAVING
                                    COUNT(user_id) > 1 ) tempA
                            GROUP BY
                                tempA.pitcher_id
                        ) e ON a.pitcher_id = e.e_pitcher_id
                        LEFT JOIN (
                            SELECT
                                user_zx_pitcher_id as f_pitcher_id,
                                COUNT(DISTINCT role_user_id) as role_num
                            FROM
                                dw_create_role_detail
                        """ + criRoleNum +
                """
                                AND NOT user_agent_id = 0
                            GROUP BY user_zx_pitcher_id
                        ) f ON a.pitcher_id = f.f_pitcher_id
                        """;
    }

    /**
     * 投手总数据的每日roi计算sql(投手总数据和投手总数据总计使用、投手游戏总数据和投手游戏总数据总计使用)
     *
     * @return String
     */
    private String pitcherDataTotalRoiSql() {
        StringBuilder roiSql = new StringBuilder(StringUtils.EMPTY);
        for (int day = 1; day <= 30; day++) {
            roiSql.append("""
                    ROUND(IF(SUM(IF(DATE_ADD(dt, INTERVAL %s day) <= LocalDate.now(), cost, 0)) > 0 ,
                        SUM(da%s) / SUM(IF(DATE_ADD(dt, INTERVAL %s day) <= LocalDate.now(), cost, 0)), 0), 4) as roi%s,
                    """.formatted(day - 1, day, day - 1, day));
        }
        return roiSql.toString();
    }

    /**
     * 查询投手游戏总数据SQL（投手游戏总数据）
     *
     * @param criA       主表查询条件
     * @param criB       账面相关查询条件
     * @param criAmount  账面充值人数查询条件
     * @param criNewUser 新用户相关查询条件
     * @param criRoleNum 创角人数相关查询条件
     * @return String
     */
    private String pitcherGameDataTotalSql(Criteria criA, Criteria criB, Criteria criAmount, Criteria criNewUser, Criteria criRoleNum) {
        return """
                SELECT
                	a.*,
                	IFNULL(amount, 0) as amount,
                	IFNULL(amount_count, 0) as amount_count,
                	IFNULL(amount_num, 0) as amount_num,
                	IFNULL(paper_avg, 0) as paper_avg,
                	ROUND(IF(amount_num > 0 , IFNULL(amount, 0) / amount_num, 0), 2) as paper_arpu,
                	IFNULL(new_user_amount_num, 0) as new_user_amount_num,
                	ROUND(IF(new_user_amount_num > 0, IFNULL(cost, 0) / new_user_amount_num, 0), 2) as new_user_amount_cost,
                	ROUND(IF(register_num > 0, IFNULL(new_user_amount_num, 0) / register_num, 0), 4) as new_user_amount_rate,
                	ROUND(IF(amount_num > 0, IFNULL(new_user_amount_num, 0) / amount_num, 0), 4) as new_user_amount_ratio,
                	IFNULL(new_user_order_again, 0) as new_user_order_again,
                	ROUND(IF(new_user_total_amount_num > 0, IFNULL(new_user_order_again, 0) / new_user_total_amount_num, 0), 4) as new_user_again_rate,
                	IFNULL(role_num, 0) as role_num,
                	ROUND(IF(register_num > 0, IFNULL(role_num, 0) / register_num, 0), 4) as role_num_rate,
                	ROUND(IF(role_num > 0, IFNULL(cost, 0) / role_num, 0), 2) as role_num_cost
                FROM (
                	SELECT
                		pitcher_id,
                		game_id,
                		MAX(pitcher) as pitcher,
                		MAX(game_name) as game_name,
                		MAX(game_cp) as game_cp,
                		MAX(game_type) as game_type,
                		SUM(cost) as cost,
                		SUM(plan_count) as plan_count,
                		SUM(account_count) as account_count,
                		SUM(agent_count) as agent_count,
                		SUM(register_num) as register_num,
                		ROUND(IF(SUM(register_num) > 0, SUM(cost) / SUM(register_num), 0), 2) as register_cost,
                		SUM(first_role_num) as first_role_num,
                		SUM(new_user_total_role_num) as new_user_total_role_num,
                		ROUND(IF(SUM(first_role_num) > 0 , SUM(cost) / SUM(first_role_num), 0), 2) as first_role_cost,
                		ROUND(IF(SUM(new_user_total_role_num) > 0 , SUM(cost) / SUM(new_user_total_role_num), 0), 2) as new_user_total_role_cost,
                		ROUND(IF(SUM(register_num) > 0 , SUM(first_role_num) / SUM(register_num), 0), 4) as first_role_rate,
                		ROUND(IF(SUM(register_num) > 0 , SUM(new_user_total_role_num) / SUM(register_num), 0), 4) as new_user_total_role_rate,
                		SUM(first_new_user_amount_count) as first_new_user_amount_count,
                		SUM(first_new_user_amount_num) as first_new_user_amount_num,
                		SUM(first_new_user_amount) as first_new_user_amount,
                		SUM(new_user_total_amount_count) as new_user_total_amount_count,
                		SUM(new_user_total_amount_num) as new_user_total_amount_num,
                		SUM(new_user_total_amount) as new_user_total_amount,
                		SUM(buy_new_user_total_amount) as buy_new_user_total_amount,
                		SUM(buy_new_user_total_amount_num) as buy_new_user_total_amount_num,
                		SUM(buy_new_user_total_amount_count) as buy_new_user_total_amount_count,
                		ROUND(IF(SUM(cost) > 0, SUM(first_new_user_amount) / SUM(cost), 0), 4) as first_roi,
                		ROUND(IF(SUM(cost) > 0, SUM(buy_new_user_total_amount) / SUM(cost), 0), 4) as buy_roi,
                		ROUND(IF(SUM(cost) > 0, SUM(new_user_total_amount) / SUM(cost), 0), 4) as today_roi,
                		(SUM(new_user_total_amount) - SUM(cost)) as gross_profit,
                		ROUND(IF(SUM(register_num) > 0, SUM(first_new_user_amount_num) / SUM(register_num), 0), 4) as first_rate,
                		ROUND(IF(SUM(register_num) > 0, SUM(buy_new_user_total_amount_num) / SUM(register_num), 0), 4) as buy_user_rate,
                		ROUND(IF(SUM(register_num) > 0, SUM(new_user_total_amount_num) / SUM(register_num), 0), 4) as today_rate,
                		ROUND(IF(SUM(first_new_user_amount_count) > 0 , SUM(first_new_user_amount) / SUM(first_new_user_amount_count), 0), 2) as first_avg,
                		ROUND(IF(SUM(buy_new_user_total_amount_count) > 0 , SUM(buy_new_user_total_amount) / SUM(buy_new_user_total_amount_count), 0), 2) as buy_avg,
                		ROUND(IF(SUM(new_user_total_amount_count) > 0 , SUM(new_user_total_amount) / SUM(new_user_total_amount_count), 0), 2) as today_avg,
                		ROUND(IF(SUM(first_new_user_amount_num) > 0 , SUM(cost) / SUM(first_new_user_amount_num), 0), 2) as first_amount_cost,
                		ROUND(IF(SUM(buy_new_user_total_amount_num) > 0 , SUM(cost) / SUM(buy_new_user_total_amount_num), 0), 2) as buy_amount_cost,
                		ROUND(IF(SUM(new_user_total_amount_num) > 0 , SUM(cost) / SUM(new_user_total_amount_num), 0), 2) as today_amount_cost,
                		SUM(reg_order_user_again) as reg_order_user_again,
                		ROUND(IF(SUM(new_user_total_amount_num) > 0 ,SUM(reg_order_user_again) / SUM(new_user_total_amount_num), 0), 4) as today_again_rate,
                		ROUND(IF(SUM(register_num) > 0 , SUM(new_user_total_amount) / SUM(register_num), 0), 2) as new_reg_arpu,
                		ROUND(IF(SUM(first_new_user_amount_num) > 0 , SUM(first_new_user_amount) / SUM(first_new_user_amount_num), 0), 2) as first_arpu,
                		ROUND(IF(SUM(new_user_total_amount_num) > 0 , SUM(new_user_total_amount) / SUM(new_user_total_amount_num), 0), 2) as today_arpu,
                		SUM(hundred_user_num) as hundred_user_num,
                		ROUND(IF(SUM(hundred_user_num) > 0 , SUM(cost) / SUM(hundred_user_num), 0), 2) as hundred_user_num_cost,
                		""" + pitcherDataTotalRoiSql() +
                """
                            ROUND(IF(SUM(IF(DATE_ADD(dt, INTERVAL 59 day) <= LocalDate.now(), cost, 0)) > 0 ,
                                  SUM(da60) / SUM(IF(DATE_ADD(dt, INTERVAL 59 day) <= LocalDate.now(), cost, 0)), 0), 4) as roi60,
                            ROUND(IF(SUM(IF(DATE_ADD(dt, INTERVAL 89 day) <= LocalDate.now(), cost, 0)) > 0 ,
                                  SUM(da90) / SUM(IF(DATE_ADD(dt, INTERVAL 89 day) <= LocalDate.now(), cost, 0)), 0), 4) as roi90,
                            ROUND(IF(SUM(IF(DATE_ADD(dt, INTERVAL 179 day) <= LocalDate.now(), cost, 0)) > 0 ,
                                  SUM(m6) / SUM(IF(DATE_ADD(dt, INTERVAL 179 day) <= LocalDate.now(), cost, 0)), 0), 4) as roi180,
                            ROUND(IF(SUM(IF(DATE_ADD(dt, INTERVAL 1 year) <= LocalDate.now(), cost, 0)) > 0 ,
                                  SUM(m12) / SUM(IF(DATE_ADD(dt, INTERVAL 1 year) <= LocalDate.now(), cost, 0)), 0), 4) as roi1yaer,
                            ROUND(IF(SUM(cost) > 0 , SUM(total) / SUM(cost), 0), 4) as roi_total
                        FROM
                        	game_ads.ads_game_pitcher_day
                        """ + criA +
                """
                        ) a
                        LEFT JOIN (
                        	SELECT
                        	    pitcher_id as b_pitcher_id,
                        	    game_id as b_game_id,
                        	    IFNULL(SUM(amount), 0) as amount,
                        	    IFNULL(SUM(amount_count), 0) as amount_count,
                        	    ROUND(IF(SUM(amount_count) > 0 , SUM(amount) / SUM(amount_count), 0), 2) as paper_avg
                        	FROM
                        	    game_ads.ads_game_pitcher_day
                        	""" + criB +
                """
                        ) b ON a.pitcher_id = b.b_pitcher_id AND a.game_id = b.b_game_id
                        LEFT JOIN (
                        	SELECT
                        		pitcher_id as c_pitcher_id,
                        		game_id as c_game_id,
                        		IFNULL(COUNT(DISTINCT user_id), 0) amount_num
                        	FROM
                        		game_ads.ads_information
                        	""" + criAmount +
                """
                        	    AND NOT agent_id = 0
                        	GROUP BY pitcher_id , game_id
                        ) c ON a.pitcher_id = c.c_pitcher_id AND a.game_id = c.c_game_id
                        LEFT JOIN (
                        	SELECT
                        		pitcher_id as d_pitcher_id,
                        		game_id as d_game_id,
                        		COUNT(DISTINCT user_id) new_user_amount_num
                        	FROM
                        		game_ads.ads_information
                        	""" + criNewUser +
                """
                        		AND NOT agent_id = 0
                        	GROUP BY
                        		pitcher_id , game_id
                        ) d ON a.pitcher_id = d.d_pitcher_id AND a.game_id = d.d_game_id
                        LEFT JOIN (
                        	SELECT
                        		game_id as e_game_id,
                        		pitcher_id as e_pitcher_id,
                        		COUNT(tempA.num) as new_user_order_again
                        	FROM (
                        		SELECT
                        			game_id,
                        			pitcher_id,
                        			COUNT(user_id) num
                        		FROM
                        			game_ads.ads_information
                        		""" + criNewUser +
                """
                        			AND NOT agent_id = 0
                        		GROUP BY
                        			user_id,
                        			pitcher_id,
                        			game_id
                        		HAVING
                        			COUNT(user_id) > 1 ) tempA
                        	GROUP BY
                        		tempA.game_id, tempA.pitcher_id
                        ) e ON a.game_id = e.e_game_id AND a.pitcher_id = e.e_pitcher_id
                        LEFT JOIN (
                        	SELECT
                        		user_game_id as f_game_id,
                        		user_zx_pitcher_id as f_pitcher_id,
                        		COUNT(DISTINCT role_user_id) as role_num
                        	FROM
                        		dw_create_role_detail
                        	""" + criRoleNum +
                """
                        		AND NOT user_agent_id = 0
                        	GROUP BY
                        		user_game_id, user_zx_pitcher_id
                        ) f ON a.game_id = f.f_game_id AND a.pitcher_id = f.f_pitcher_id
                        """;
    }

    /**
     * 查询投手游戏总数据SQL（投手游戏总数据）- 父游戏维度
     *
     * @param criA       主表查询条件
     * @param criB       账面相关查询条件
     * @param criAmount  账面充值人数查询条件
     * @param criNewUser 新用户相关查询条件
     * @param criRoleNum 创角人数相关查询条件
     * @return String
     */
    private String pitcherGameDataTotalSqlForParent(Criteria criA, Criteria criB, Criteria criAmount, Criteria criNewUser, Criteria criRoleNum) {
        return """
                SELECT
                	a.*,
                	IFNULL(amount, 0) as amount,
                	IFNULL(amount_count, 0) as amount_count,
                	IFNULL(amount_num, 0) as amount_num,
                	IFNULL(paper_avg, 0) as paper_avg,
                	ROUND(IF(amount_num > 0 , IFNULL(amount, 0) / amount_num, 0), 2) as paper_arpu,
                	IFNULL(new_user_amount_num, 0) as new_user_amount_num,
                	ROUND(IF(new_user_amount_num > 0, IFNULL(cost, 0) / new_user_amount_num, 0), 2) as new_user_amount_cost,
                	ROUND(IF(register_num > 0, IFNULL(new_user_amount_num, 0) / register_num, 0), 4) as new_user_amount_rate,
                	ROUND(IF(amount_num > 0, IFNULL(new_user_amount_num, 0) / amount_num, 0), 4) as new_user_amount_ratio,
                	IFNULL(new_user_order_again, 0) as new_user_order_again,
                	ROUND(IF(new_user_total_amount_num > 0, IFNULL(new_user_order_again, 0) / new_user_total_amount_num, 0), 4) as new_user_again_rate,
                	IFNULL(role_num, 0) as role_num,
                	ROUND(IF(register_num > 0, IFNULL(role_num, 0) / register_num, 0), 4) as role_num_rate,
                	ROUND(IF(role_num > 0, IFNULL(cost, 0) / role_num, 0), 2) as role_num_cost
                FROM (
                	SELECT
                		pitcher_id,
                		parent_game_id as game_id,
                		MAX(pitcher) as pitcher,
                		MAX(parent_game_name) as game_name,
                		MAX(parent_game_cp) as game_cp,
                		MAX(parent_game_type) as game_type,
                		SUM(cost) as cost,
                		SUM(plan_count) as plan_count,
                		SUM(account_count) as account_count,
                		SUM(agent_count) as agent_count,
                		SUM(register_num) as register_num,
                		ROUND(IF(SUM(register_num) > 0, SUM(cost) / SUM(register_num), 0), 2) as register_cost,
                		SUM(first_role_num) as first_role_num,
                		SUM(new_user_total_role_num) as new_user_total_role_num,
                		ROUND(IF(SUM(first_role_num) > 0 , SUM(cost) / SUM(first_role_num), 0), 2) as first_role_cost,
                		ROUND(IF(SUM(new_user_total_role_num) > 0 , SUM(cost) / SUM(new_user_total_role_num), 0), 2) as new_user_total_role_cost,
                		ROUND(IF(SUM(register_num) > 0 , SUM(first_role_num) / SUM(register_num), 0), 4) as first_role_rate,
                		ROUND(IF(SUM(register_num) > 0 , SUM(new_user_total_role_num) / SUM(register_num), 0), 4) as new_user_total_role_rate,
                		SUM(first_new_user_amount_count) as first_new_user_amount_count,
                		SUM(first_new_user_amount_num) as first_new_user_amount_num,
                		SUM(first_new_user_amount) as first_new_user_amount,
                		SUM(new_user_total_amount_count) as new_user_total_amount_count,
                		SUM(new_user_total_amount_num) as new_user_total_amount_num,
                		SUM(new_user_total_amount) as new_user_total_amount,
                		SUM(buy_new_user_total_amount) as buy_new_user_total_amount,
                		SUM(buy_new_user_total_amount_num) as buy_new_user_total_amount_num,
                		SUM(buy_new_user_total_amount_count) as buy_new_user_total_amount_count,
                		ROUND(IF(SUM(cost) > 0, SUM(first_new_user_amount) / SUM(cost), 0), 4) as first_roi,
                		ROUND(IF(SUM(cost) > 0, SUM(buy_new_user_total_amount) / SUM(cost), 0), 4) as buy_roi,
                		ROUND(IF(SUM(cost) > 0, SUM(new_user_total_amount) / SUM(cost), 0), 4) as today_roi,
                		(SUM(new_user_total_amount) - SUM(cost)) as gross_profit,
                		ROUND(IF(SUM(register_num) > 0, SUM(first_new_user_amount_num) / SUM(register_num), 0), 4) as first_rate,
                		ROUND(IF(SUM(register_num) > 0, SUM(buy_new_user_total_amount_num) / SUM(register_num), 0), 4) as buy_user_rate,
                		ROUND(IF(SUM(register_num) > 0, SUM(new_user_total_amount_num) / SUM(register_num), 0), 4) as today_rate,
                		ROUND(IF(SUM(first_new_user_amount_count) > 0 , SUM(first_new_user_amount) / SUM(first_new_user_amount_count), 0), 2) as first_avg,
                		ROUND(IF(SUM(buy_new_user_total_amount_count) > 0 , SUM(buy_new_user_total_amount) / SUM(buy_new_user_total_amount_count), 0), 2) as buy_avg,
                		ROUND(IF(SUM(new_user_total_amount_count) > 0 , SUM(new_user_total_amount) / SUM(new_user_total_amount_count), 0), 2) as today_avg,
                		ROUND(IF(SUM(first_new_user_amount_num) > 0 , SUM(cost) / SUM(first_new_user_amount_num), 0), 2) as first_amount_cost,
                		ROUND(IF(SUM(buy_new_user_total_amount_num) > 0 , SUM(cost) / SUM(buy_new_user_total_amount_num), 0), 2) as buy_amount_cost,
                		ROUND(IF(SUM(new_user_total_amount_num) > 0 , SUM(cost) / SUM(new_user_total_amount_num), 0), 2) as today_amount_cost,
                		SUM(reg_order_user_again) as reg_order_user_again,
                		ROUND(IF(SUM(new_user_total_amount_num) > 0 ,SUM(reg_order_user_again) / SUM(new_user_total_amount_num), 0), 4) as today_again_rate,
                		ROUND(IF(SUM(register_num) > 0 , SUM(new_user_total_amount) / SUM(register_num), 0), 2) as new_reg_arpu,
                		ROUND(IF(SUM(first_new_user_amount_num) > 0 , SUM(first_new_user_amount) / SUM(first_new_user_amount_num), 0), 2) as first_arpu,
                		ROUND(IF(SUM(new_user_total_amount_num) > 0 , SUM(new_user_total_amount) / SUM(new_user_total_amount_num), 0), 2) as today_arpu,
                		SUM(hundred_user_num) as hundred_user_num,
                		ROUND(IF(SUM(hundred_user_num) > 0 , SUM(cost) / SUM(hundred_user_num), 0), 2) as hundred_user_num_cost,
                """ + pitcherDataTotalRoiSql() +
                """
                                ROUND(IF(SUM(IF(DATE_ADD(dt, INTERVAL 59 day) <= LocalDate.now(), cost, 0)) > 0 ,
                                      SUM(da60) / SUM(IF(DATE_ADD(dt, INTERVAL 59 day) <= LocalDate.now(), cost, 0)), 0), 4) as roi60,
                                ROUND(IF(SUM(IF(DATE_ADD(dt, INTERVAL 89 day) <= LocalDate.now(), cost, 0)) > 0 ,
                                      SUM(da90) / SUM(IF(DATE_ADD(dt, INTERVAL 89 day) <= LocalDate.now(), cost, 0)), 0), 4) as roi90,
                                ROUND(IF(SUM(IF(DATE_ADD(dt, INTERVAL 179 day) <= LocalDate.now(), cost, 0)) > 0 ,
                                      SUM(m6) / SUM(IF(DATE_ADD(dt, INTERVAL 179 day) <= LocalDate.now(), cost, 0)), 0), 4) as roi180,
                                ROUND(IF(SUM(IF(DATE_ADD(dt, INTERVAL 1 year) <= LocalDate.now(), cost, 0)) > 0 ,
                                      SUM(m12) / SUM(IF(DATE_ADD(dt, INTERVAL 1 year) <= LocalDate.now(), cost, 0)), 0), 4) as roi1yaer,
                                ROUND(IF(SUM(cost) > 0 , SUM(total) / SUM(cost), 0), 4) as roi_total
                            FROM
                                game_ads_parent.ads_game_pitcher_day_parent
                        """ + criA +
                """
                        ) a
                        LEFT JOIN (
                            SELECT
                                pitcher_id as b_pitcher_id,
                                parent_game_id as b_game_id,
                                IFNULL(SUM(amount), 0) as amount,
                                IFNULL(SUM(amount_count), 0) as amount_count,
                                ROUND(IF(SUM(amount_count) > 0 , SUM(amount) / SUM(amount_count), 0), 2) as paper_avg
                            FROM
                                game_ads_parent.ads_game_pitcher_day_parent
                        """ + criB +
                """
                        ) b ON a.pitcher_id = b.b_pitcher_id AND a.game_id = b.b_game_id
                        LEFT JOIN (
                            SELECT
                                pitcher_id as c_pitcher_id,
                                parent_game_id as c_game_id,
                                IFNULL(COUNT(DISTINCT user_id), 0) amount_num
                            FROM
                                game_ads.ads_information
                        """ + criAmount +
                """
                                AND NOT agent_id = 0
                            GROUP BY pitcher_id , parent_game_id
                        ) c ON a.pitcher_id = c.c_pitcher_id AND a.game_id = c.c_game_id
                        LEFT JOIN (
                            SELECT
                                pitcher_id as d_pitcher_id,
                                parent_game_id as d_game_id,
                                COUNT(DISTINCT user_id) new_user_amount_num
                            FROM
                                game_ads.ads_information
                        """ + criNewUser +
                """
                            AND NOT agent_id = 0
                            GROUP BY
                                pitcher_id , parent_game_id
                        ) d ON a.pitcher_id = d.d_pitcher_id AND a.game_id = d.d_game_id
                        LEFT JOIN (
                            SELECT
                                parent_game_id as e_game_id,
                                pitcher_id as e_pitcher_id,
                                COUNT(tempA.num) as new_user_order_again
                            FROM (
                                SELECT
                                    parent_game_id,
                                    pitcher_id,
                                    COUNT(user_id) num
                                FROM
                                    game_ads.ads_information
                        """ + criNewUser +
                """
                                    AND NOT agent_id = 0
                                GROUP BY
                                    user_id,
                                    pitcher_id,
                                    parent_game_id
                                HAVING
                                    COUNT(user_id) > 1 ) tempA
                            GROUP BY tempA.parent_game_id, tempA.pitcher_id
                        ) e ON a.game_id = e.e_game_id AND a.pitcher_id = e.e_pitcher_id
                        LEFT JOIN (
                            SELECT
                                parent_game_id as f_game_id,
                                user_zx_pitcher_id as f_pitcher_id,
                                COUNT(DISTINCT role_user_id) as role_num
                            FROM
                                dw_create_role_detail
                        """ + criRoleNum +
                """
                                AND NOT user_agent_id = 0
                            GROUP BY parent_game_id, user_zx_pitcher_id
                        ) f ON a.game_id = f.f_game_id AND a.pitcher_id = f.f_pitcher_id
                        """;
    }

    /**
     * 查询投手总数据总计SQL（投手总数据总计）
     *
     * @return String
     */
    private String pitcherDataTotalSumSql(String tableName) {
        return """
                SELECT
                    IFNULL(SUM(cost), 0) as cost,
                    IFNULL(SUM(plan_count), 0) as plan_count,
                    IFNULL(SUM(account_count), 0) as account_count,
                    IFNULL(SUM(agent_count), 0) as agent_count,
                    IFNULL(SUM(register_num), 0) as register_num,
                    ROUND(IF(SUM(register_num) > 0, SUM(cost) / SUM(register_num), 0), 2) as register_cost,
                    IFNULL(SUM(first_role_num), 0) as first_role_num,
                    IFNULL(SUM(new_user_total_role_num), 0) as new_user_total_role_num,
                    ROUND(IF(SUM(first_role_num) > 0 , SUM(cost) / SUM(first_role_num), 0), 2) as first_role_cost,
                    ROUND(IF(SUM(new_user_total_role_num) > 0 , SUM(cost) / SUM(new_user_total_role_num), 0), 2) as new_user_total_role_cost,
                    ROUND(IF(SUM(register_num) > 0 , SUM(first_role_num) / SUM(register_num), 0), 4) as first_role_rate,
                    ROUND(IF(SUM(register_num) > 0 , SUM(new_user_total_role_num) / SUM(register_num), 0), 4) as new_user_total_role_rate,
                    IFNULL(SUM(first_new_user_amount_count), 0) as first_new_user_amount_count,
                    IFNULL(SUM(first_new_user_amount_num), 0) as first_new_user_amount_num,
                    IFNULL(SUM(first_new_user_amount), 0) as first_new_user_amount,
                    IFNULL(SUM(new_user_total_amount_count), 0) as new_user_total_amount_count,
                    IFNULL(SUM(new_user_total_amount_num), 0) as new_user_total_amount_num,
                    IFNULL(SUM(new_user_total_amount), 0) as new_user_total_amount,
                    IFNULL(SUM(buy_new_user_total_amount), 0) as buy_new_user_total_amount,
                    IFNULL(SUM(buy_new_user_total_amount_num), 0) as buy_new_user_total_amount_num,
                    IFNULL(SUM(buy_new_user_total_amount_count), 0) as buy_new_user_total_amount_count,
                    ROUND(IF(SUM(cost) > 0, SUM(first_new_user_amount) / SUM(cost), 0), 4) as first_roi,
                    ROUND(IF(SUM(cost) > 0, SUM(buy_new_user_total_amount) / SUM(cost), 0), 4) as buy_roi,
                    ROUND(IF(SUM(cost) > 0, SUM(new_user_total_amount) / SUM(cost), 0), 4) as today_roi,
                    IFNULL((SUM(new_user_total_amount) - SUM(cost)), 0) as gross_profit,
                    ROUND(IF(SUM(register_num) > 0, SUM(first_new_user_amount_num) / SUM(register_num), 0), 4) as first_rate,
                    ROUND(IF(SUM(register_num) > 0, SUM(buy_new_user_total_amount_num) / SUM(register_num), 0), 4) as buy_user_rate,
                    ROUND(IF(SUM(register_num) > 0, SUM(new_user_total_amount_num) / SUM(register_num), 0), 4) as today_rate,
                    ROUND(IF(SUM(first_new_user_amount_count) > 0 , SUM(first_new_user_amount) / SUM(first_new_user_amount_count), 0), 2) as first_avg,
                    ROUND(IF(SUM(buy_new_user_total_amount_count) > 0 , SUM(buy_new_user_total_amount) / SUM(buy_new_user_total_amount_count), 0), 2) as buy_avg,
                    ROUND(IF(SUM(new_user_total_amount_count) > 0 , SUM(new_user_total_amount) / SUM(new_user_total_amount_count), 0), 2) as today_avg,
                    ROUND(IF(SUM(first_new_user_amount_num) > 0 , SUM(cost) / SUM(first_new_user_amount_num), 0), 2) as first_amount_cost,
                    ROUND(IF(SUM(buy_new_user_total_amount_num) > 0 , SUM(cost) / SUM(buy_new_user_total_amount_num), 0), 2) as buy_amount_cost,
                    ROUND(IF(SUM(new_user_total_amount_num) > 0 , SUM(cost) / SUM(new_user_total_amount_num), 0), 2) as today_amount_cost,
                    IFNULL(SUM(reg_order_user_again), 0) as reg_order_user_again,
                    ROUND(IF(SUM(new_user_total_amount_num) > 0 ,SUM(reg_order_user_again) / SUM(new_user_total_amount_num), 0), 4) as today_again_rate,
                    ROUND(IF(SUM(register_num) > 0 , SUM(new_user_total_amount) / SUM(register_num), 0), 2) as new_reg_arpu,
                    ROUND(IF(SUM(first_new_user_amount_num) > 0 , SUM(first_new_user_amount) / SUM(first_new_user_amount_num), 0), 2) as first_arpu,
                    ROUND(IF(SUM(new_user_total_amount_num) > 0 , SUM(new_user_total_amount) / SUM(new_user_total_amount_num), 0), 2) as today_arpu,
                    IFNULL(SUM(hundred_user_num), 0) as hundred_user_num,
                    ROUND(IF(SUM(hundred_user_num) > 0 , SUM(cost) / SUM(hundred_user_num), 0), 2) as hundred_user_num_cost,
                    SUM(new_user_total_zfb_amount) AS new_user_total_zfb_amount,
                    SUM(new_user_total_wx_amount) AS new_user_total_wx_amount,
                    SUM(new_user_total_mds_amount) AS new_user_total_mds_amount,
                """ + pitcherDataTotalRoiSql() +
                """
                            ROUND(IF(SUM(IF(DATE_ADD(dt, INTERVAL 59 day) <= LocalDate.now(), cost, 0)) > 0 ,
                                  SUM(da60) / SUM(IF(DATE_ADD(dt, INTERVAL 59 day) <= LocalDate.now(), cost, 0)), 0), 4) as roi60,
                            ROUND(IF(SUM(IF(DATE_ADD(dt, INTERVAL 89 day) <= LocalDate.now(), cost, 0)) > 0 ,
                                  SUM(da90) / SUM(IF(DATE_ADD(dt, INTERVAL 89 day) <= LocalDate.now(), cost, 0)), 0), 4) as roi90,
                            ROUND(IF(SUM(IF(DATE_ADD(dt, INTERVAL 179 day) <= LocalDate.now(), cost, 0)) > 0 ,
                                  SUM(m6) / SUM(IF(DATE_ADD(dt, INTERVAL 179 day) <= LocalDate.now(), cost, 0)), 0), 4) as roi180,
                            ROUND(IF(SUM(IF(DATE_ADD(dt, INTERVAL 1 year) <= LocalDate.now(), cost, 0)) > 0 ,
                                  SUM(m12) / SUM(IF(DATE_ADD(dt, INTERVAL 1 year) <= LocalDate.now(), cost, 0)), 0), 4) as roi1yaer,
                            ROUND(IF(SUM(cost) > 0 , SUM(total) / SUM(cost), 0), 4) as roi_total
                        FROM 
                        """ + tableName;
    }

    /**
     * 查询投手游戏总数据总计SQL（投手游戏总数据总计）
     *
     * @return String
     */
    private String pitcherGameDataTotalSumSql(String tableName) {
        return """
                SELECT
                    IFNULL(SUM(cost), 0) as cost,
                    IFNULL(SUM(plan_count), 0) as plan_count,
                    IFNULL(SUM(account_count), 0) as account_count,
                    IFNULL(SUM(agent_count), 0) as agent_count,
                    IFNULL(SUM(register_num), 0) as register_num,
                    ROUND(IF(SUM(register_num) > 0, SUM(cost) / SUM(register_num), 0), 2) as register_cost,
                    IFNULL(SUM(first_role_num), 0) as first_role_num,
                    IFNULL(SUM(new_user_total_role_num), 0) as new_user_total_role_num,
                    ROUND(IF(SUM(first_role_num) > 0 , SUM(cost) / SUM(first_role_num), 0), 2) as first_role_cost,
                    ROUND(IF(SUM(new_user_total_role_num) > 0 , SUM(cost) / SUM(new_user_total_role_num), 0), 2) as new_user_total_role_cost,
                    ROUND(IF(SUM(register_num) > 0 , SUM(first_role_num) / SUM(register_num), 0), 4) as first_role_rate,
                    ROUND(IF(SUM(register_num) > 0 , SUM(new_user_total_role_num) / SUM(register_num), 0), 4) as new_user_total_role_rate,
                    IFNULL(SUM(first_new_user_amount_count), 0) as first_new_user_amount_count,
                    IFNULL(SUM(first_new_user_amount_num), 0) as first_new_user_amount_num,
                    IFNULL(SUM(first_new_user_amount), 0) as first_new_user_amount,
                    IFNULL(SUM(new_user_total_amount_count), 0) as new_user_total_amount_count,
                    IFNULL(SUM(new_user_total_amount_num), 0) as new_user_total_amount_num,
                    IFNULL(SUM(new_user_total_amount), 0) as new_user_total_amount,
                    IFNULL(SUM(buy_new_user_total_amount), 0) as buy_new_user_total_amount,
                    IFNULL(SUM(buy_new_user_total_amount_num), 0) as buy_new_user_total_amount_num,
                    IFNULL(SUM(buy_new_user_total_amount_count), 0) as buy_new_user_total_amount_count,
                    ROUND(IF(SUM(cost) > 0, SUM(first_new_user_amount) / SUM(cost), 0), 4) as first_roi,
                    ROUND(IF(SUM(cost) > 0, SUM(buy_new_user_total_amount) / SUM(cost), 0), 4) as buy_roi,
                    ROUND(IF(SUM(cost) > 0, SUM(new_user_total_amount) / SUM(cost), 0), 4) as today_roi,
                    IFNULL((SUM(new_user_total_amount) - SUM(cost)), 0) as gross_profit,
                    ROUND(IF(SUM(register_num) > 0, SUM(first_new_user_amount_num) / SUM(register_num), 0), 4) as first_rate,
                    ROUND(IF(SUM(register_num) > 0, SUM(buy_new_user_total_amount_num) / SUM(register_num), 0), 4) as buy_user_rate,
                    ROUND(IF(SUM(register_num) > 0, SUM(new_user_total_amount_num) / SUM(register_num), 0), 4) as today_rate,
                    ROUND(IF(SUM(first_new_user_amount_count) > 0 , SUM(first_new_user_amount) / SUM(first_new_user_amount_count), 0), 2) as first_avg,
                    ROUND(IF(SUM(buy_new_user_total_amount_count) > 0 , SUM(buy_new_user_total_amount) / SUM(buy_new_user_total_amount_count), 0), 2) as buy_avg,
                    ROUND(IF(SUM(new_user_total_amount_count) > 0 , SUM(new_user_total_amount) / SUM(new_user_total_amount_count), 0), 2) as today_avg,
                    ROUND(IF(SUM(first_new_user_amount_num) > 0 , SUM(cost) / SUM(first_new_user_amount_num), 0), 2) as first_amount_cost,
                    ROUND(IF(SUM(buy_new_user_total_amount_num) > 0 , SUM(cost) / SUM(buy_new_user_total_amount_num), 0), 2) as buy_amount_cost,
                    ROUND(IF(SUM(new_user_total_amount_num) > 0 , SUM(cost) / SUM(new_user_total_amount_num), 0), 2) as today_amount_cost,
                    IFNULL(SUM(reg_order_user_again), 0) as reg_order_user_again,
                    ROUND(IF(SUM(new_user_total_amount_num) > 0 ,SUM(reg_order_user_again) / SUM(new_user_total_amount_num), 0), 4) as today_again_rate,
                    ROUND(IF(SUM(register_num) > 0 , SUM(new_user_total_amount) / SUM(register_num), 0), 2) as new_reg_arpu,
                    ROUND(IF(SUM(first_new_user_amount_num) > 0 , SUM(first_new_user_amount) / SUM(first_new_user_amount_num), 0), 2) as first_arpu,
                    ROUND(IF(SUM(new_user_total_amount_num) > 0 , SUM(new_user_total_amount) / SUM(new_user_total_amount_num), 0), 2) as today_arpu,
                    IFNULL(SUM(hundred_user_num), 0) as hundred_user_num,
                    ROUND(IF(SUM(hundred_user_num) > 0 , SUM(cost) / SUM(hundred_user_num), 0), 2) as hundred_user_num_cost,
                """ + pitcherDataTotalRoiSql() +
                """
                            ROUND(IF(SUM(IF(DATE_ADD(dt, INTERVAL 59 day) <= LocalDate.now(), cost, 0)) > 0 ,
                                  SUM(da60) / SUM(IF(DATE_ADD(dt, INTERVAL 59 day) <= LocalDate.now(), cost, 0)), 0), 4) as roi60,
                            ROUND(IF(SUM(IF(DATE_ADD(dt, INTERVAL 89 day) <= LocalDate.now(), cost, 0)) > 0 ,
                                  SUM(da90) / SUM(IF(DATE_ADD(dt, INTERVAL 89 day) <= LocalDate.now(), cost, 0)), 0), 4) as roi90,
                            ROUND(IF(SUM(IF(DATE_ADD(dt, INTERVAL 179 day) <= LocalDate.now(), cost, 0)) > 0 ,
                                  SUM(m6) / SUM(IF(DATE_ADD(dt, INTERVAL 179 day) <= LocalDate.now(), cost, 0)), 0), 4) as roi180,
                            ROUND(IF(SUM(IF(DATE_ADD(dt, INTERVAL 1 year) <= LocalDate.now(), cost, 0)) > 0 ,
                                  SUM(m12) / SUM(IF(DATE_ADD(dt, INTERVAL 1 year) <= LocalDate.now(), cost, 0)), 0), 4) as roi1yaer,
                            ROUND(IF(SUM(cost) > 0 , SUM(total) / SUM(cost), 0), 4) as roi_total
                        FROM 
                        """ + tableName;
    }


}
