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.*;
import com.zanxiang.game.data.serve.pojo.enums.OrderByEnum;
import com.zanxiang.game.data.serve.pojo.vo.*;
import com.zanxiang.game.data.serve.service.IMediaDataService;
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.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/30
 * @Description 媒体数据的逻辑处理
 **/
@Service
@Slf4j
public class IMediaDataServiceImpl implements IMediaDataService {

    //存储PitcherGameMediaDataDayVO的映射List
    private static final List<Tuple2<Field, Field>> pitcherGameMediaDayNMapList;
    //存储PitcherMediaDataDayVO的映射List
    private static final List<Tuple2<Field, Field>> pitcherMediaDayNMapList;
    //存储GameMediaDataDayVO的映射List
    private static final List<Tuple2<Field, Field>> gameMediaDayNMapList;

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

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

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

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

    /**
     * 推广媒体每日数据
     * @param dto PitcherGameMediaDataDayDTO
     * @return Page<PitcherGameMediaDataDayVO>
     */
    @Override
    public Page<PitcherGameMediaDataDayVO> getPitcherGameMediaDataDay(PitcherGameMediaDataDayDTO dto) {
        //添加权限
        com.github.sd4324530.jtuple.Tuple2<List<Long>, List<Long>> poerInfo = dataPowerComponent.getPowerInfo();
        List<Long> pitcherIds = CollectionUtils.isEmpty(dto.getPitcherId()) ? poerInfo.first : dto.getPitcherId();
        List<Long> gameIds = CollectionUtils.isEmpty(dto.getGameId()) ? poerInfo.second : dto.getGameId();

        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_acctype_day";
        Class<?> clazz = AdsGamePitcherAcctypeDay.class;
        if (dto.getGameDimension() == 2L) {
            gameColumn = "parent_game_id";
            gameCpColumn = "parent_game_cp";
            gameTypeColumn = "parent_game_type";
            tableName = "game_ads_parent.ads_game_pitcher_acctype_day_parent";
            clazz = AdsGamePitcherAcctypeDayParent.class;
        }
        //创建查询条件
        Criteria cri = Cnd.cri();
        if (CollectionUtils.isNotEmpty(dto.getPitcherId())) {
            cri.where().andInList("pitcher_id", pitcherIds);
        }
        if (CollectionUtils.isNotEmpty(dto.getGameId())) {
            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());
        }
        if (StringUtils.isNotBlank(dto.getAccountType())) {
            cri.where().andEquals("account_type", dto.getAccountType());
        }
        //排序条件
        //拼接排序条件，如果没有排序条件给默认值
        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(mediaDataDaySql(tableName) + cri);
        //设置回传对象
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(PitcherGameMediaDataDayVO.class));
        //设置pager
        sql.setPager(pager);
        //执行sql
        dao.execute(sql);
        //设置总记录数
        pager.setRecordCount(dao.count(clazz, cri));
        //处理dayN数据
        List<PitcherGameMediaDataDayVO> tempList = sql.getList(PitcherGameMediaDataDayVO.class);
        List<PitcherGameMediaDataDayVO> list = tempList.stream().map(vo -> {
            formatMediaDataDayDayN(vo);
            return vo;
        }).collect(Collectors.toList());
        //返回结果
        return new Page<>(list, pager);
    }

    /**
     * 推广媒体每日数据总计
     * @param dto PitcherGameMediaDataDayTotalDTO
     * @return PitcherGameMediaDataDayVO
     */
    @Override
    public PitcherGameMediaDataDayVO getPitcherGameMediaDataDayTotal(PitcherGameMediaDataDayTotalDTO dto) {
        //添加权限
        com.github.sd4324530.jtuple.Tuple2<List<Long>, List<Long>> poerInfo = dataPowerComponent.getPowerInfo();
        List<Long> pitcherIds = CollectionUtils.isEmpty(dto.getPitcherId()) ? poerInfo.first : dto.getPitcherId();
        List<Long> gameIds = CollectionUtils.isEmpty(dto.getGameId()) ? poerInfo.second : dto.getGameId();

        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_acctype_day";
        if (dto.getGameDimension() == 2L) {
            gameColumn = "parent_game_id";
            gameCpColumn = "parent_game_cp";
            gameTypeColumn = "parent_game_type";
            tableName = "game_ads_parent.ads_game_pitcher_acctype_day_parent";
        }

        //创建查询条件
        Criteria cri = Cnd.cri();
        if (pitcherIds != null) {
            cri.where().andInList("pitcher_id", pitcherIds);
        }
        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());
        }
        if (StringUtils.isNotBlank(dto.getAccountType())) {
            cri.where().andEquals("account_type", dto.getAccountType());
        }
        //sql语句
        Sql sql = Sqls.create(mediaDataDayTotalSql(tableName) + cri);
        //设置回传对象
        sql.setCallback(Sqls.callback.entity());
        sql.setEntity(dao.getEntity(PitcherGameMediaDataDayVO.class));
        //执行sql
        dao.execute(sql);
        PitcherGameMediaDataDayVO vo = sql.getObject(PitcherGameMediaDataDayVO.class);
        if (StringUtils.isNotBlank(vo.getAmountD1())) {
            //处理dayN数据
            formatMediaDataDayTotalDayN(vo);
        }
        return vo;
    }

    /**
     * 推广媒体总数据
     * @param dto PitcherGameMediaDataTotalDTO
     * @return Page<PitcherGameMediaDataTotalVO>
     */
    @Override
    public Page<PitcherGameMediaDataTotalVO> getPitcherGameMediaDataTotal(PitcherGameMediaDataTotalDTO dto) {
        //添加权限
        com.github.sd4324530.jtuple.Tuple2<List<Long>, List<Long>> poerInfo = dataPowerComponent.getPowerInfo();
        List<Long> pitcherIds = 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 gameColumn = "game_id";
        if (dto.getGameDimension() == 2L) {
            gameColumn = "parent_game_id";
        }
        //主表查询条件
        Criteria criA = myCri(dto, "criA", gameIds, pitcherIds);
        //拼接分组条件
        criA.getGroupBy().groupBy(gameColumn, "pitcher_id", "account_type");
        //账面相关查询条件 时间条件只和充值时间相关
        Criteria criB = myCri(dto, "criB", gameIds, pitcherIds);
        //拼接分组条件
        criB.getGroupBy().groupBy(gameColumn, "pitcher_id", "account_type");
        //账面人数相关 时间只需要充值时间条件,不需要注册时间条件
        Criteria criAmount = myCriRecharge(dto, false, gameIds, pitcherIds);
        //新用户充值人数和新用户复充人数 时间条件需要注册时间和充值时间
        Criteria criNewUser = myCriRecharge(dto, true, gameIds, pitcherIds);
        //创角人数查询条件
        Criteria criRoleNum = myCriRole(dto, gameIds, pitcherIds);
        //拼接排序条件
        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(pitcherGameMediaDataTotalSql(criA, criB, criAmount, criNewUser, criRoleNum) + orderByCri);
        } else {
            sql = Sqls.create(pitcherGameMediaDataTotalSqlForParent(criA, criB, criAmount, criNewUser, criRoleNum) + orderByCri);
        }
        //设置自定义回显对象
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(PitcherGameMediaDataTotalVO.class));
        Pager pager = dao.createPager(dto.getPageNum(), dto.getPageSize());
        sql.setPager(pager);
        //执行sql
        dao.execute(sql);
        //得到结果集list
        List<PitcherGameMediaDataTotalVO> list = sql.getList(PitcherGameMediaDataTotalVO.class);
        //设置查询总数
        Sql sqlCount;
        if (dto.getGameDimension() == 1L) {
            sqlCount = Sqls.queryEntity("select count(*) from game_ads.ads_game_pitcher_acctype_day" + criA);
        } else {
            sqlCount = Sqls.queryEntity("select count(*) from game_ads_parent.ads_game_pitcher_acctype_day_parent" + criA);
        }
        pager.setRecordCount((int) Daos.queryCount(dao, sqlCount));

        //获取所有筛选条件内的dayN数据
        Map<String, String> pitcherGameMediaDataTotalDayNMap = tempDayNData(dto, gameIds, pitcherIds);
        //根据充值时间修改值
        List<PitcherGameMediaDataTotalVO> pitcherGameDataTotalVOList = list.stream().map(vo -> {
            //包含<accountType-pitcherId-gameId>下所有的游戏的dayn数据
            String key = vo.getAccountType() + "-" + vo.getPitcherId() + "-" + vo.getGameId();
            String dayNStr = pitcherGameMediaDataTotalDayNMap.get(key);
            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 PitcherGameMediaDataTotalSumDTO
     * @return PitcherGameMediaDataTotalVO
     */
    @Override
    public PitcherGameMediaDataTotalVO getPitcherGameMediaDataTotalSum(PitcherGameMediaDataTotalSumDTO dto) {
        com.github.sd4324530.jtuple.Tuple2<List<Long>, List<Long>> poerInfo = dataPowerComponent.getPowerInfo();
        List<Long> pitcherId = 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_acctype_day";
        if (dto.getGameDimension() == 2L){
            tableName = "game_ads_parent.ads_game_pitcher_acctype_day_parent";
        }
        //PitcherGameMediaDataTotalSumDTO 到 PitcherGameMediaDataTotalDTO
        PitcherGameMediaDataTotalDTO totalDTO = new PitcherGameMediaDataTotalDTO();
        copyNullProperties(dto, totalDTO);
        //主表查询条件
        Criteria criA = myCri(totalDTO, "criA", gameIds, pitcherId);
        //账面相关查询条件 时间条件只和充值时间相关
        Criteria criB = myCri(totalDTO, "criB", gameIds, pitcherId);
        //账面人数相关 时间只需要充值时间条件,不需要注册时间条件
        Criteria criAmount = myCriRecharge(totalDTO, false, gameIds, pitcherId);
        //新用户充值人数和新用户复充人数 时间条件需要注册时间和充值时间
        Criteria criNewUser = myCriRecharge(totalDTO, true, gameIds, pitcherId);
        //创角人数查询条件
        Criteria criRoleNum = myCriRole(totalDTO, gameIds, pitcherId);
        //创建sql语句 执行sql
        Sql sql = Sqls.create(mediaDataTotalSumSql(tableName) + criA);
        //设置自定义回显对象
        sql.setCallback(Sqls.callback.entity());
        sql.setEntity(dao.getEntity(PitcherGameMediaDataTotalVO.class));
        //执行sql
        dao.execute(sql);
        //得到结果
        PitcherGameMediaDataTotalVO vo = sql.getObject(PitcherGameMediaDataTotalVO.class);
        //得到账面相关数据、账面人数、新用户人数、新用户复充人数、创角人数数据
        PitcherGameMediaDataTotalVO tempVO = getPitcherGameMediaDataTotalSumAmountData(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, gameIds, pitcherId);
        //新用户充值金额
        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 dto PitcherMediaDataDayDTO
     * @return Page<PitcherMediaDataDayVO>
     */
    @Override
    public Page<PitcherMediaDataDayVO> getPitcherMediaDataDay(PitcherMediaDataDayDTO dto) {
        //添加权限
        com.github.sd4324530.jtuple.Tuple2<List<Long>, List<Long>> poerInfo = dataPowerComponent.getPowerInfo();
        List<Long> pitcherIds = CollectionUtils.isEmpty(dto.getPitcherId()) ? poerInfo.first : dto.getPitcherId();

        if (dto.getGameDimension() == null) {
            //默认查询子游戏维度
            dto.setGameDimension(1L);
        }
        String tableName = "game_ads.ads_pitcher_acctype_day";
        Class<?> clazz = AdsPitcherAcctypeDay.class;
        if (dto.getGameDimension() == 2L) {
            tableName = "game_ads_parent.ads_pitcher_acctype_day_parent";
            clazz = AdsPitcherAcctypeDayParent.class;
        }

        //创建查询条件
        Criteria cri = Cnd.cri();
        if (CollectionUtils.isNotEmpty(pitcherIds)) {
            cri.where().andInList("pitcher_id", pitcherIds);
        }
        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.getAccountType())) {
            cri.where().andEquals("account_type", dto.getAccountType());
        }
        //排序条件
        //拼接排序条件，如果没有排序条件给默认值
        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(mediaDataDaySql(tableName) + cri);
        //设置回传对象
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(PitcherMediaDataDayVO.class));
        //设置pager
        sql.setPager(pager);
        //执行sql
        dao.execute(sql);
        //设置总记录数
        pager.setRecordCount(dao.count(clazz, cri));
        //处理dayN数据
        List<PitcherMediaDataDayVO> tempList = sql.getList(PitcherMediaDataDayVO.class);
        List<PitcherMediaDataDayVO> list = tempList.stream().map(vo -> {
            formatMediaDataDayDayN(vo);
            return vo;
        }).collect(Collectors.toList());
        //返回结果
        return new Page<>(list, pager);
    }

    /**
     * 投手媒体每日数据总计
     * @param dto PitcherMediaDataDayTotalDTO
     * @return PitcherMediaDataDayVO
     */
    @Override
    public PitcherMediaDataDayVO getPitcherMediaDataDayTotal(PitcherMediaDataDayTotalDTO dto) {
        //添加权限
        com.github.sd4324530.jtuple.Tuple2<List<Long>, List<Long>> poerInfo = dataPowerComponent.getPowerInfo();
        List<Long> pitcherIds = CollectionUtils.isEmpty(dto.getPitcherId()) ? poerInfo.first : dto.getPitcherId();

        if (dto.getGameDimension() == null) {
            //默认查询子游戏维度
            dto.setGameDimension(1L);
        }
        String tableName = "game_ads.ads_pitcher_acctype_day";
        if (dto.getGameDimension() == 2L) {
            tableName = "game_ads_parent.ads_pitcher_acctype_day_parent";
        }

        //创建查询条件
        Criteria cri = Cnd.cri();
        if (CollectionUtils.isNotEmpty(pitcherIds)) {
            cri.where().andInList("pitcher_id", pitcherIds);
        }
        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.getAccountType())) {
            cri.where().andEquals("account_type", dto.getAccountType());
        }
        //sql语句
        Sql sql = Sqls.create(mediaDataDayTotalSql(tableName) + cri);
        //设置回传对象
        sql.setCallback(Sqls.callback.entity());
        sql.setEntity(dao.getEntity(PitcherMediaDataDayVO.class));
        //执行sql
        dao.execute(sql);
        PitcherMediaDataDayVO vo = sql.getObject(PitcherMediaDataDayVO.class);
        if (StringUtils.isNotBlank(vo.getAmountD1())) {
            //处理dayN数据
            formatMediaDataDayTotalDayN(vo);
        }
        return vo;
    }

    /**
     * 投手媒体总数据
     * @param dto PitcherMediaDataTotalDTO
     * @return Page<PitcherMediaDataTotalVO>
     */
    @Override
    public Page<PitcherMediaDataTotalVO> getPitcherMediaDataTotal(PitcherMediaDataTotalDTO dto) {
        //添加权限
        com.github.sd4324530.jtuple.Tuple2<List<Long>, List<Long>> poerInfo = dataPowerComponent.getPowerInfo();
        List<Long> pitcherIds = CollectionUtils.isEmpty(dto.getPitcherId()) ? poerInfo.first : 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(dto, "criA", pitcherIds);
        //拼接分组条件
        criA.getGroupBy().groupBy("pitcher_id", "account_type");
        //账面相关查询条件 时间条件只和充值时间相关
        Criteria criB = myCriPitcher(dto, "criB", pitcherIds);
        //拼接分组条件
        criB.getGroupBy().groupBy("pitcher_id", "account_type");
        //账面人数相关 时间只需要充值时间条件,不需要注册时间条件
        Criteria criAmount = myCriRechargePitcher(dto, false, pitcherIds);
        //新用户充值人数和新用户复充人数 时间条件需要注册时间和充值时间
        Criteria criNewUser = myCriRechargePitcher(dto, true, pitcherIds);
        //创角人数查询条件
        Criteria criRoleNum = myCriRolePitcher(dto, pitcherIds);
        //拼接排序条件
        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(pitcherMediaDataTotalSql(criA, criB, criAmount, criNewUser, criRoleNum) + orderByCri);
        } else {
            sql = Sqls.create(pitcherMediaDataTotalSqlForParent(criA, criB, criAmount, criNewUser, criRoleNum) + orderByCri);
        }
        //设置自定义回显对象
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(PitcherMediaDataTotalVO.class));
        Pager pager = dao.createPager(dto.getPageNum(), dto.getPageSize());
        sql.setPager(pager);
        //执行sql
        dao.execute(sql);
        //得到结果集list
        List<PitcherMediaDataTotalVO> list = sql.getList(PitcherMediaDataTotalVO.class);
        //设置查询总数
        Sql sqlCount;
        if (dto.getGameDimension() == 1L) {
            sqlCount = Sqls.queryEntity("select count(*) from game_ads.ads_pitcher_acctype_day" + criA);
        } else {
            sqlCount = Sqls.queryEntity("select count(*) from game_ads_parent.ads_pitcher_acctype_day_parent" + criA);
        }
        pager.setRecordCount((int) Daos.queryCount(dao, sqlCount));

        //获取所有筛选条件内的dayN数据
        Map<String, String> pitcherMediaDataTotalDayNMap = tempDayNDataPitcher(dto, pitcherIds);
        //根据充值时间修改值
        List<PitcherMediaDataTotalVO> pitcherMediaDataTotalVOList = list.stream().map(vo -> {
            //包含<accountType-pitcherId-gameId>下所有的游戏的dayn数据
            String key = vo.getAccountType() + "-" + vo.getPitcherId();
            String dayNStr = pitcherMediaDataTotalDayNMap.get(key);
            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<>(pitcherMediaDataTotalVOList, pager);
    }

    /**
     * 投手媒体总数据总计
     * @param dto PitcherMediaDataTotalSumDTO
     * @return PitcherMediaDataTotalVO
     */
    @Override
    public PitcherMediaDataTotalVO getPitcherMediaDataTotalSum(PitcherMediaDataTotalSumDTO dto) {
        com.github.sd4324530.jtuple.Tuple2<List<Long>, List<Long>> poerInfo = dataPowerComponent.getPowerInfo();
        List<Long> pitcherId = CollectionUtils.isEmpty(dto.getPitcherId()) ? poerInfo.first : 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_acctype_day";
        if (dto.getGameDimension() == 2L) {
            tablename = "game_ads_parent.ads_pitcher_acctype_day_parent";
        }
        //PitcherGameMediaDataTotalSumDTO 到 PitcherGameMediaDataTotalDTO
        PitcherMediaDataTotalDTO totalDTO = new PitcherMediaDataTotalDTO();
        copyNullProperties(dto, totalDTO);
        //主表查询条件
        Criteria criA = myCriPitcher(totalDTO, "criA", pitcherId);
        //账面相关查询条件 时间条件只和充值时间相关
        Criteria criB = myCriPitcher(totalDTO, "criB", pitcherId);
        //账面人数相关 时间只需要充值时间条件,不需要注册时间条件
        Criteria criAmount = myCriRechargePitcher(totalDTO, false, pitcherId);
        //新用户充值人数和新用户复充人数 时间条件需要注册时间和充值时间
        Criteria criNewUser = myCriRechargePitcher(totalDTO, true, pitcherId);
        //创角人数查询条件
        Criteria criRoleNum = myCriRolePitcher(totalDTO, pitcherId);
        //创建sql语句 执行sql
        Sql sql = Sqls.create(mediaDataTotalSumSql(tablename) + criA);
        //设置自定义回显对象
        sql.setCallback(Sqls.callback.entity());
        sql.setEntity(dao.getEntity(PitcherMediaDataTotalVO.class));
        //执行sql
        dao.execute(sql);
        //得到结果
        PitcherMediaDataTotalVO vo = sql.getObject(PitcherMediaDataTotalVO.class);
        //得到账面相关数据、账面人数、新用户人数、新用户复充人数、创角人数数据
        PitcherMediaDataTotalVO tempVO = getPitcherMediaDataTotalSumAmountData(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, pitcherId);
        //新用户充值金额
        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 dto GameMediaDataDayDTO
     * @return Page<GameMediaDataDayVO>
     */
    @Override
    public Page<GameMediaDataDayVO> getGameMediaDataDay(GameMediaDataDayDTO dto) {
        //添加权限
        com.github.sd4324530.jtuple.Tuple2<List<Long>, List<Long>> poerInfo = dataPowerComponent.getPowerInfo();
        List<Long> gameIds = CollectionUtils.isEmpty(dto.getGameId()) ? poerInfo.second : dto.getGameId();

        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_acctype_day";
        Class<?> clazz = AdsGameAcctypeDay.class;
        if (dto.getGameDimension() == 2L) {
            gameColumn = "parent_game_id";
            gameCpColumn = "parent_game_cp";
            gameTypeColumn = "parent_game_type";
            tableName = "game_ads_parent.ads_game_acctype_day_parent";
            clazz = AdsGameAcctypeDayParent.class;
        }

        //创建查询条件
        Criteria cri = Cnd.cri();
        if (CollectionUtils.isNotEmpty(dto.getGameId())) {
            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());
        }
        if (StringUtils.isNotBlank(dto.getAccountType())) {
            cri.where().andEquals("account_type", dto.getAccountType());
        }
        //排序条件
        //拼接排序条件，如果没有排序条件给默认值
        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(mediaDataDaySql(tableName) + cri);
        //设置回传对象
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(GameMediaDataDayVO.class));
        //设置pager
        sql.setPager(pager);
        //执行sql
        dao.execute(sql);
        //设置总记录数
        pager.setRecordCount(dao.count(clazz, cri));
        //处理dayN数据
        List<GameMediaDataDayVO> tempList = sql.getList(GameMediaDataDayVO.class);
        List<GameMediaDataDayVO> list = tempList.stream().map(vo -> {
            formatMediaDataDayDayN(vo);
            return vo;
        }).collect(Collectors.toList());
        //返回结果
        return new Page<>(list, pager);
    }

    /**
     * 游戏媒体每日数据总计
     * @param dto GameMediaDataDayTotalDTO
     * @return GameMediaDataDayVO
     */
    @Override
    public GameMediaDataDayVO getGameMediaDataDayTotal(GameMediaDataDayTotalDTO dto) {
        //添加权限
        com.github.sd4324530.jtuple.Tuple2<List<Long>, List<Long>> poerInfo = dataPowerComponent.getPowerInfo();
        List<Long> gameIds = CollectionUtils.isEmpty(dto.getGameId()) ? poerInfo.second : dto.getGameId();

        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_acctype_day";
        if (dto.getGameDimension() == 2L) {
            gameColumn = "parent_game_id";
            gameCpColumn = "parent_game_cp";
            gameTypeColumn = "parent_game_type";
            tableName = "game_ads_parent.ads_game_acctype_day_parent";
        }
        //创建查询条件
        Criteria cri = Cnd.cri();
        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());
        }
        if (StringUtils.isNotBlank(dto.getAccountType())) {
            cri.where().andEquals("account_type", dto.getAccountType());
        }
        //sql语句
        Sql sql = Sqls.create(mediaDataDayTotalSql(tableName) + cri);
        //设置回传对象
        sql.setCallback(Sqls.callback.entity());
        sql.setEntity(dao.getEntity(GameMediaDataDayVO.class));
        //执行sql
        dao.execute(sql);
        GameMediaDataDayVO vo = sql.getObject(GameMediaDataDayVO.class);
        if (StringUtils.isNotBlank(vo.getAmountD1())) {
            //处理dayN数据
            formatMediaDataDayTotalDayN(vo);
        }
        return vo;
    }

    /**
     * 游戏媒体总数据
     * @param dto GameMediaDataTotalDTO
     * @return Page<GameMediaDataTotalVO>
     */
    @Override
    public Page<GameMediaDataTotalVO> getGameMediaDataTotal(GameMediaDataTotalDTO dto) {
        //添加权限
        com.github.sd4324530.jtuple.Tuple2<List<Long>, List<Long>> poerInfo = dataPowerComponent.getPowerInfo();
        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 gameColumn = "game_id";
        if (dto.getGameDimension() == 2L) {
            gameColumn = "parent_game_id";
        }
        //主表查询条件
        Criteria criA = myCriGame(dto, "criA", gameIds);
        //拼接分组条件
        criA.getGroupBy().groupBy(gameColumn, "account_type");
        //账面相关查询条件 时间条件只和充值时间相关
        Criteria criB = myCriGame(dto, "criB", gameIds);
        //拼接分组条件
        criB.getGroupBy().groupBy(gameColumn, "account_type");
        //账面人数相关 时间只需要充值时间条件,不需要注册时间条件
        Criteria criAmount = myCriRechargeGame(dto, false, gameIds);
        //新用户充值人数和新用户复充人数 时间条件需要注册时间和充值时间
        Criteria criNewUser = myCriRechargeGame(dto, true, gameIds);
        //创角人数查询条件
        Criteria criRoleNum = myCriRoleGame(dto, gameIds);
        //拼接排序条件
        Criteria orderByCri = Cnd.cri();
        //如果没有排序条件给默认值
        if (StringUtils.isBlank(dto.getSortType())) {
            dto.setSortType(OrderByEnum.DESC.getOrderType());
        }
        if (StringUtils.isBlank(dto.getSortFiled())) {
            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(gameMediaDataTotalSql(criA, criB, criAmount, criNewUser, criRoleNum) + orderByCri);
        } else {
            sql = Sqls.create(gameMediaDataTotalSqlForParent(criA, criB, criAmount, criNewUser, criRoleNum) + orderByCri);
        }
        //设置自定义回显对象
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(GameMediaDataTotalVO.class));
        Pager pager = dao.createPager(dto.getPageNum(), dto.getPageSize());
        sql.setPager(pager);
        //执行sql
        dao.execute(sql);
        //得到结果集list
        List<GameMediaDataTotalVO> list = sql.getList(GameMediaDataTotalVO.class);
        //设置查询总数
        Sql sqlCount;
        if (dto.getGameDimension() == 1L) {
            sqlCount = Sqls.queryEntity("select count(*) from game_ads.ads_game_acctype_day" + criA);
        } else {
            sqlCount = Sqls.queryEntity("select count(*) from game_ads_parent.ads_game_acctype_day_parent" + criA);
        }
        pager.setRecordCount((int) Daos.queryCount(dao, sqlCount));

        //获取所有筛选条件内的dayN数据
        Map<String, String> gameMediaDataTotalDayNMap = tempDayNDataGame(dto, gameIds);
        //根据充值时间修改值
        List<GameMediaDataTotalVO> gameDataTotalVOList = list.stream().map(vo -> {
            //包含<accountType-gameId>下所有的游戏的dayn数据
            String key = vo.getAccountType() + "-" + vo.getGameId();
            String dayNStr = gameMediaDataTotalDayNMap.get(key);
            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<>(gameDataTotalVOList, pager);
    }

    /**
     * 游戏媒体总数据总计
     * @param dto GameMediaDataTotalSumDTO
     * @return GameMediaDataTotalVO
     */
    @Override
    public GameMediaDataTotalVO getGameMediaDataTotalSum(GameMediaDataTotalSumDTO dto) {
        com.github.sd4324530.jtuple.Tuple2<List<Long>, List<Long>> poerInfo = dataPowerComponent.getPowerInfo();
        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_acctype_day";
        if (dto.getGameDimension() == 2L) {
            tableName = "game_ads_parent.ads_game_acctype_day_parent";
        }
        //GameMediaDataTotalSumDTO 到 GameMediaDataTotalDTO
        GameMediaDataTotalDTO totalDTO = new GameMediaDataTotalDTO();
        copyNullProperties(dto, totalDTO);
        //主表查询条件
        Criteria criA = myCriGame(totalDTO, "criA", gameIds);
        //账面相关查询条件 时间条件只和充值时间相关
        Criteria criB = myCriGame(totalDTO, "criB", gameIds);
        //账面人数相关 时间只需要充值时间条件,不需要注册时间条件
        Criteria criAmount = myCriRechargeGame(totalDTO, false, gameIds);
        //新用户充值人数和新用户复充人数 时间条件需要注册时间和充值时间
        Criteria criNewUser = myCriRechargeGame(totalDTO, true, gameIds);
        //创角人数查询条件
        Criteria criRoleNum = myCriRoleGame(totalDTO, gameIds);
        //创建sql语句 执行sql
        Sql sql = Sqls.create(mediaDataTotalSumSql(tableName) + criA);
        //设置自定义回显对象
        sql.setCallback(Sqls.callback.entity());
        sql.setEntity(dao.getEntity(GameMediaDataTotalVO.class));
        //执行sql
        dao.execute(sql);
        //得到结果
        GameMediaDataTotalVO vo = sql.getObject(GameMediaDataTotalVO.class);
        //得到账面相关数据、账面人数、新用户人数、新用户复充人数、创角人数数据
        GameMediaDataTotalVO tempVO = getGameMediaDataTotalSumAmountData(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 = tempDayNDataTotalGame(totalDTO, 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;
    }

    /**
     * 处理媒体数据相关的每日数据的DayN数据
     * @param vo PitcherGameMediaDataDayVO、PitcherMediaDataDayVO、GameMediaDataDayVO
     */
    private void formatMediaDataDayDayN(Object vo) {
        List<Tuple2<Field, Field>> tempMapList;
        //不同的对象使用不同的映射list
        if (vo instanceof PitcherGameMediaDataDayVO) {
            tempMapList = pitcherGameMediaDayNMapList;
        } else if (vo instanceof PitcherMediaDataDayVO) {
            tempMapList = pitcherMediaDayNMapList;
        } else {
            tempMapList = gameMediaDayNMapList;
        }
        if (CollectionUtils.isEmpty(tempMapList)) {
            return;
        }
        tempMapList.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 PitcherGameMediaDataDayVO、PitcherMediaDataDayVO、GameMediaDataDayVO
     */
    private void formatMediaDataDayTotalDayN(Object vo) {
        List<Tuple2<Field, Field>> tempMapList;
        //不同的对象使用不同的映射list
        if (vo instanceof PitcherGameMediaDataDayVO) {
            tempMapList = pitcherGameMediaDayNMapList;
        } else if (vo instanceof PitcherMediaDataDayVO) {
            tempMapList = pitcherMediaDayNMapList;
        } else {
            tempMapList = gameMediaDayNMapList;
        }
        if (CollectionUtils.isEmpty(tempMapList)) {
            return;
        }
        tempMapList.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("映射出错");
            }
        });
    }

    /**
     * 所有为空值的属性都不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);
    }

    /**
     * 得到账面相关数据、账面人数、新用户人数、新用户复充人数、创角人数数据（推广媒体总数据总计）
     *
     * @return PitcherGameMediaDataTotalVO
     */
    private PitcherGameMediaDataTotalVO getPitcherGameMediaDataTotalSumAmountData(PitcherGameMediaDataTotalSumDTO dto, Criteria criB, Criteria criAmount, Criteria criNewUser, Criteria criRoleNum) {
        String tableName = "game_ads.ads_game_pitcher_acctype_day";
        if (dto.getGameDimension() == 2L) {
            tableName = "game_ads_parent.ads_game_pitcher_acctype_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(PitcherGameMediaDataTotalVO.class));
        //执行sql
        dao.execute(sqlAmountData);
        PitcherGameMediaDataTotalVO vo = sqlAmountData.getObject(PitcherGameMediaDataTotalVO.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;
    }

    /**
     * 得到账面相关数据、账面人数、新用户人数、新用户复充人数、创角人数数据（投手媒体总数据总计）
     *
     * @return PitcherMediaDataTotalVO
     */
    private PitcherMediaDataTotalVO getPitcherMediaDataTotalSumAmountData(PitcherMediaDataTotalSumDTO dto, Criteria criB, Criteria criAmount, Criteria criNewUser, Criteria criRoleNum) {
        String tableName = "game_ads.ads_pitcher_acctype_day";
        if (dto.getGameDimension() == 2L) {
            tableName = "game_ads_parent.ads_pitcher_acctype_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(PitcherMediaDataTotalVO.class));
        //执行sql
        dao.execute(sqlAmountData);
        PitcherMediaDataTotalVO vo = sqlAmountData.getObject(PitcherMediaDataTotalVO.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;
    }

    /**
     * 得到账面相关数据、账面人数、新用户人数、新用户复充人数、创角人数数据（游戏媒体总数据总计）
     *
     * @return GameMediaDataTotalVO
     */
    private GameMediaDataTotalVO getGameMediaDataTotalSumAmountData(GameMediaDataTotalSumDTO dto, Criteria criB, Criteria criAmount, Criteria criNewUser, Criteria criRoleNum) {
        String tableName = "game_ads.ads_game_acctype_day";
        if (dto.getGameDimension() == 2L) {
            tableName = "game_ads_parent.ads_game_acctype_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(GameMediaDataTotalVO.class));
        //执行sql
        dao.execute(sqlAmountData);
        GameMediaDataTotalVO vo = sqlAmountData.getObject(GameMediaDataTotalVO.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;
    }

    /**
     * 生成DayN数据方法（推广媒体总数据）
     *
     * @param dto       前端查询参数
     * @param gameIds 查询的游戏id列表
     * @param pitcherIds 查询的投手id列表
     * @return Map
     */
    private Map<String, String> tempDayNData(PitcherGameMediaDataTotalDTO dto, List<Long> gameIds, List<Long> pitcherIds) {
        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(pitcherIds)) {
            cri.where().andInList("pitcher_id", pitcherIds);
        }
        if (StringUtils.isNotBlank(dto.getAccountType())) {
            cri.where().andEquals("account_type", dto.getAccountType());
        }
        Sql dayNSql;
        if (dto.getGameDimension() == 1L) {
            dayNSql = Sqls.create("""
                SELECT
                    dt,
                    game_id,
                    pitcher_id,
                    source_system,
                    account_type,
                    day_n
                FROM
                    game_ads.ads_game_pitcher_account_dayn
                """ + cri);
        } else {
            dayNSql = Sqls.create("""
                SELECT
                    dt,
                    parent_game_id as game_id,
                    pitcher_id,
                    source_system,
                    account_type,
                    day_n
                FROM
                    game_ads_parent.ads_game_pitcher_account_dayn_parent
                """ + cri);
        }

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

        //将数据库中获取的所有结果封装到一个List中
        List<AdsGamePitcherAccountDayn> list = dayNSql.getList(AdsGamePitcherAccountDayn.class);
        // <accountType-pitcherId-gameId,dayN>
        Map<String, String> tempMap = new HashMap<>();
        //用来拼接 accountType、pitcherId、gameId 字符串
        StringBuilder tempStr = new StringBuilder(StringUtils.EMPTY);
        //循环遍历 list 按照 accountType-pitcherId-gameId 进行存储
        for (AdsGamePitcherAccountDayn adsGamePitcherAccountDayn : list) {
            tempStr.append(adsGamePitcherAccountDayn.getAccountType());
            tempStr.append("-");
            tempStr.append(adsGamePitcherAccountDayn.getPitcherId());
            tempStr.append("-");
            tempStr.append(adsGamePitcherAccountDayn.getGameId());
            //判断是否含有改主键的数据
            if (!tempMap.containsKey(tempStr.toString())) {
                //不含有改主键数据，初始化主键数据
                tempMap.put(tempStr.toString(), "0.00-0");
            }
            //含有改主键的数据，拿到之前的数据，解析当前的dayN数据，再去更新数据
            String dataStr = parseJsonData(adsGamePitcherAccountDayn, tempMap.get(tempStr.toString()), dto);
            tempMap.put(tempStr.toString(), dataStr);
            //清空主键
            tempStr.setLength(0);
        }

        return tempMap;
    }

    /**
     * 生成DayN数据方法（投手媒体总数据）
     *
     * @param dto       前端查询参数
     * @param pitcherIds 查询的投手id列表
     * @return Map
     */
    private Map<String, String> tempDayNDataPitcher(PitcherMediaDataTotalDTO dto, List<Long> pitcherIds) {
        String tableName = "game_ads.ads_pitcher_account_dayn";
        if (dto.getGameDimension() == 2L) {
            tableName = "game_ads_parent.ads_pitcher_account_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 (pitcherIds != null) {
            cri.where().andInList("pitcher_id", pitcherIds);
        }
        if (StringUtils.isNotBlank(dto.getAccountType())) {
            cri.where().andEquals("account_type", dto.getAccountType());
        }
        Sql dayNSql = Sqls.create("""
                SELECT
                    dt,
                    pitcher_id,
                    source_system,
                    account_type,
                    day_n
                FROM 
                """ +tableName + cri);

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

        //将数据库中获取的所有结果封装到一个List中
        List<AdsPitcherAccountDayn> list = dayNSql.getList(AdsPitcherAccountDayn.class);
        // <accountType-pitcherId,dayN>
        Map<String, String> tempMap = new HashMap<>();
        //用来拼接 accountType、pitcherId、gameId 字符串
        StringBuilder tempStr = new StringBuilder(StringUtils.EMPTY);
        //循环遍历 list 按照 accountType-pitcherId 进行存储
        for (AdsPitcherAccountDayn adsPitcherAccountDayn : list) {
            tempStr.append(adsPitcherAccountDayn.getAccountType());
            tempStr.append("-");
            tempStr.append(adsPitcherAccountDayn.getPitcherId());
            //判断是否含有改主键的数据
            if (!tempMap.containsKey(tempStr.toString())) {
                //不含有改主键数据，初始化主键数据
                tempMap.put(tempStr.toString(), "0.00-0");
            }
            //含有改主键的数据，拿到之前的数据，解析当前的dayN数据，再去更新数据
            String dataStr = parseJsonDataPitcher(adsPitcherAccountDayn, tempMap.get(tempStr.toString()), dto);
            tempMap.put(tempStr.toString(), dataStr);
            //清空主键
            tempStr.setLength(0);
        }

        return tempMap;
    }

    /**
     * 生成DayN数据方法（游戏媒体总数据）
     *
     * @param dto       前端查询参数
     * @param gameIds 查询的Gameid列表
     * @return Map
     */
    private Map<String, String> tempDayNDataGame(GameMediaDataTotalDTO dto, 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 (StringUtils.isNotBlank(dto.getAccountType())) {
            cri.where().andEquals("account_type", dto.getAccountType());
        }
        Sql dayNSql;
        if (dto.getGameDimension() == 1L) {
            dayNSql = Sqls.create("""
                SELECT
                    dt,
                    game_id,
                    source_system,
                    account_type,
                    day_n
                FROM
                    game_ads.ads_game_account_dayn
                """ + cri);
        } else {
            dayNSql = Sqls.create("""
                SELECT
                    dt,
                    parent_game_id as game_id,
                    source_system,
                    account_type,
                    day_n
                FROM
                    game_ads_parent.ads_game_account_dayn_parent
                """ + cri);
        }

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

        //将数据库中获取的所有结果封装到一个List中
        List<AdsGameAccountDayn> list = dayNSql.getList(AdsGameAccountDayn.class);
        // <accountType-gameId,dayN>
        Map<String, String> tempMap = new HashMap<>();
        //用来拼接 accountType、gameId 字符串
        StringBuilder tempStr = new StringBuilder(StringUtils.EMPTY);
        //循环遍历 list 按照 accountType-gameId 进行存储
        for (AdsGameAccountDayn adsGameAccountDayn : list) {
            tempStr.append(adsGameAccountDayn.getAccountType());
            tempStr.append("-");
            tempStr.append(adsGameAccountDayn.getGameId());
            //判断是否含有改主键的数据
            if (!tempMap.containsKey(tempStr.toString())) {
                //不含有改主键数据，初始化主键数据
                tempMap.put(tempStr.toString(), "0.00-0");
            }
            //含有改主键的数据，拿到之前的数据，解析当前的dayN数据，再去更新数据
            String dataStr = parseJsonDataGame(adsGameAccountDayn, tempMap.get(tempStr.toString()), dto);
            tempMap.put(tempStr.toString(), dataStr);
            //清空主键
            tempStr.setLength(0);
        }

        return tempMap;
    }

    /**
     * 计算所有的dayN(推广媒体总数据总计)
     *
     * @param dto 前端查询参数
     * @param gameIds 查询的游戏id列表
     * @param pitcherIds 查询的投手id列表
     * @return String
     */
    private String tempDayNDataTotal(PitcherGameMediaDataTotalDTO dto ,List<Long> gameIds, List<Long> pitcherIds) {
        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(pitcherIds)) {
            cri.where().andInList("pitcher_id", pitcherIds);
        }
        if (StringUtils.isNotBlank(dto.getAccountType())) {
            cri.where().andEquals("account_type", dto.getAccountType());
        }
        Sql dayNSql;
        if (dto.getGameDimension() == 1L) {
            dayNSql = Sqls.create("""
                SELECT
                    dt,
                    game_id,
                    pitcher_id,
                    source_system,
                    account_type,
                    day_n
                FROM
                    game_ads.ads_game_pitcher_account_dayn
                """ + cri);
        } else {
            dayNSql = Sqls.create("""
                SELECT
                    dt,
                    parent_game_id as game_id,
                    pitcher_id,
                    source_system,
                    account_type,
                    day_n
                FROM
                    game_ads_parent.ads_game_pitcher_account_dayn_parent
                """ + cri);
        }
        //设置回传
        dayNSql.setCallback(Sqls.callback.entities());
        dayNSql.setEntity(dao.getEntity(AdsGamePitcherAccountDayn.class));
        //执行sql 获取数据到list中
        dao.execute(dayNSql);

        //将数据库中获取的所有结果封装到一个List中
        List<AdsGamePitcherAccountDayn> list = dayNSql.getList(AdsGamePitcherAccountDayn.class);
        //最终结果
        String dataStr = "0.00-0";
        //循环遍历 list 按照 gameId 进行存储
        for (AdsGamePitcherAccountDayn adsGamePitcherAccountDayn : list) {
            //解析每个 adsPitcherGameDayn 的 dayN
            dataStr = parseJsonData(adsGamePitcherAccountDayn, dataStr, dto);
        }
        //返回结果
        return dataStr;
    }

    /**
     * 计算所有的dayN(投手媒体总数据总计)
     *
     * @param dto 前端查询参数
     * @param pitcherIds 查询的投手id列表
     * @return String
     */
    private String tempDayNDataTotalPitcher(PitcherMediaDataTotalDTO dto ,List<Long> pitcherIds) {
        String tableName = "game_ads.ads_pitcher_account_dayn";
        if (dto.getGameDimension() == 2L) {
            tableName = "game_ads_parent.ads_pitcher_account_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 (pitcherIds != null) {
            cri.where().andInList("pitcher_id", pitcherIds);
        }
        if (StringUtils.isNotBlank(dto.getAccountType())) {
            cri.where().andEquals("account_type", dto.getAccountType());
        }
        Sql dayNSql = Sqls.create("""
                SELECT
                    dt,
                    pitcher_id,
                    source_system,
                    account_type,
                    day_n
                FROM 
                """ +tableName + cri);

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

        //将数据库中获取的所有结果封装到一个List中
        List<AdsPitcherAccountDayn> list = dayNSql.getList(AdsPitcherAccountDayn.class);
        //最终结果
        String dataStr = "0.00-0";
        //循环遍历 list 按照 gameId 进行存储
        for (AdsPitcherAccountDayn adsPitcherAccountDayn : list) {
            //解析每个 adsPitcherGameDayn 的 dayN
            dataStr = parseJsonDataPitcher(adsPitcherAccountDayn, dataStr, dto);
        }
        //返回结果
        return dataStr;
    }

    /**
     * 计算所有的dayN(游戏媒体总数据总计)
     *
     * @param dto 前端查询参数
     * @param gameIds 查询的游戏id列表
     * @return String
     */
    private String tempDayNDataTotalGame(GameMediaDataTotalDTO dto ,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 (StringUtils.isNotBlank(dto.getAccountType())) {
            cri.where().andEquals("account_type", dto.getAccountType());
        }
        Sql dayNSql;
        if (dto.getGameDimension() == 1L) {
            dayNSql = Sqls.create("""
                SELECT
                    dt,
                    game_id,
                    source_system,
                    account_type,
                    day_n
                FROM
                    game_ads.ads_game_account_dayn
                """ + cri);
        } else {
            dayNSql = Sqls.create("""
                SELECT
                    dt,
                    parent_game_id as game_id,
                    source_system,
                    account_type,
                    day_n
                FROM
                    game_ads_parent.ads_game_account_dayn_parent
                """ + cri);
        }

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

        //将数据库中获取的所有结果封装到一个List中
        List<AdsGameAccountDayn> list = dayNSql.getList(AdsGameAccountDayn.class);
        //最终结果
        String dataStr = "0.00-0";
        //循环遍历 list 按照 gameId 进行存储
        for (AdsGameAccountDayn adsGameAccountDayn : list) {
            //解析每个 adsPitcherGameDayn 的 dayN
            dataStr = parseJsonDataGame(adsGameAccountDayn, dataStr, dto);
        }
        //返回结果
        return dataStr;
    }

    /**
     * 解析dayN json字符串数据（推广媒体总数据）
     *
     * @param adsGamePitcherAccountDayn 对象
     * @param dataStr      记录最终结果的String
     * @param dto         前端查询条件
     */
    private String parseJsonData(AdsGamePitcherAccountDayn adsGamePitcherAccountDayn, String dataStr, PitcherGameMediaDataTotalDTO dto) {
        //将每一个adsDaynGame中的dayN属性值 由json转为 Map 型
        Gson gson = new Gson();
        Map<String, String> jsonList = gson.fromJson(adsGamePitcherAccountDayn.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 = dataStr.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的值
                dataStr = chargeMoney + "-" + count;
            }
        }
        return dataStr;
    }

    /**
     * 解析dayN json字符串数据（投手媒体总数据）
     *
     * @param adsPitcherAccountDayn 对象
     * @param dataStr      记录最终结果的String
     * @param dto         前端查询条件
     */
    private String parseJsonDataPitcher(AdsPitcherAccountDayn adsPitcherAccountDayn, String dataStr, PitcherMediaDataTotalDTO dto) {
        //将每一个adsDaynGame中的dayN属性值 由json转为 Map 型
        Gson gson = new Gson();
        Map<String, String> jsonList = gson.fromJson(adsPitcherAccountDayn.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 = dataStr.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的值
                dataStr = chargeMoney + "-" + count;
            }
        }
        return dataStr;
    }

    /**
     * 解析dayN json字符串数据（游戏媒体总数据）
     *
     * @param adsGameAccountDayn 对象
     * @param dataStr      记录最终结果的String
     * @param dto         前端查询条件
     */
    private String parseJsonDataGame(AdsGameAccountDayn adsGameAccountDayn, String dataStr, GameMediaDataTotalDTO dto) {
        //将每一个adsDaynGame中的dayN属性值 由json转为 Map 型
        Gson gson = new Gson();
        Map<String, String> jsonList = gson.fromJson(adsGameAccountDayn.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 = dataStr.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的值
                dataStr = chargeMoney + "-" + count;
            }
        }
        return dataStr;
    }

    /**
     * 主表 账面相关 查询条件(推广媒体总数据、总计)
     *
     * @param dto  前端传递的查询条件
     * @param type criA 查询主表  criB 查询账面相关
     * @param gameIds 查询的游戏id数组
     * @param pitcherIds 查询的投手id数组
     * @return 查询条件
     */
    private Criteria myCri(PitcherGameMediaDataTotalDTO dto, String type, List<Long> gameIds, List<Long> pitcherIds) {
        //默认查询的字段
        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(pitcherIds)) {
            //拼接投手ID
            cri.where().andInList("pitcher_id", pitcherIds);
        }
        if (StringUtils.isNotBlank(dto.getAccountType())) {
            //拼接推广媒体类型
            cri.where().andEquals("account_type", dto.getAccountType());
        }
        //根据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());
            }
        }

        return cri;
    }

    /**
     * 账面充值人数、新用户充值人数、新用户复充人数的查询条件(推广媒体总数据、总计)
     *
     * @param dto 前端传递的查询条件参数实体
     * @param needRegTime 是否需要拼接注册时间
     * @param gameIds 查询的游戏id数组
     * @param pitcherIds 查询的投手id数组
     * @return 查询条件
     */
    private Criteria myCriRecharge(PitcherGameMediaDataTotalDTO dto, Boolean needRegTime, List<Long> gameIds, List<Long> pitcherIds) {
        //默认查询的字段
        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(pitcherIds)) {
            //拼接投手ID
            cri.where().andInList("pitcher_id", pitcherIds);
        }
        if (StringUtils.isNotBlank(dto.getAccountType())) {
            //拼接推广媒体类型
            cri.where().andEquals("account_type", dto.getAccountType());
        }
        //拼接不同的时间查询条件
        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 前端传递的查询条件
     * @param gameIds 查询的游戏id数组
     * @param pitcherIds 查询的投手id数组
     * @return 查询条件
     */
    private Criteria myCriRole(PitcherGameMediaDataTotalDTO dto, List<Long> gameIds, List<Long> pitcherIds) {
        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(pitcherIds)) {
            //拼接投手ID
            cri.where().andInList("user_zx_pitcher_id", pitcherIds);
        }
        if (StringUtils.isNotBlank(dto.getAccountType())) {
            //拼接推广媒体类型
            cri.where().andEquals("user_account_type", dto.getAccountType());
        }
        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 pitcherIds 查询的投手id数组
     * @return 查询条件
     */
    private Criteria myCriPitcher(PitcherMediaDataTotalDTO dto, String type, List<Long> pitcherIds) {
        //根据传入的dto拼接查询参数
        Criteria cri = Cnd.cri();
        if (StringUtils.isNotBlank(dto.getSourceSystem())) {
            //拼接SDK来源
            cri.where().andEquals("source_system", dto.getSourceSystem());
        }
        if (CollectionUtils.isNotEmpty(pitcherIds)) {
            //拼接投手ID
            cri.where().andInList("pitcher_id", pitcherIds);
        }
        if (StringUtils.isNotBlank(dto.getAccountType())) {
            //拼接推广媒体类型
            cri.where().andEquals("account_type", dto.getAccountType());
        }
        //根据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());
            }
        }

        return cri;
    }

    /**
     * 账面充值人数、新用户充值人数、新用户复充人数的查询条件(投手媒体总数据、总计)
     *
     * @param dto 前端传递的查询条件参数实体
     * @param needRegTime 是否需要拼接注册时间
     * @param pitcherIds 查询的投手id数组
     * @return 查询条件
     */
    private Criteria myCriRechargePitcher(PitcherMediaDataTotalDTO dto, Boolean needRegTime, List<Long> pitcherIds) {
        //根据传入的dto拼接查询参数
        Criteria cri = Cnd.cri();
        if (StringUtils.isNotBlank(dto.getSourceSystem())) {
            //拼接SDK来源
            cri.where().andEquals("source_system", dto.getSourceSystem());
        }
        if (CollectionUtils.isNotEmpty(pitcherIds)) {
            //拼接投手ID
            cri.where().andInList("pitcher_id", pitcherIds);
        }
        if (StringUtils.isNotBlank(dto.getAccountType())) {
            //拼接推广媒体类型
            cri.where().andEquals("account_type", dto.getAccountType());
        }
        //拼接不同的时间查询条件
        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 前端传递的查询条件
     * @param pitcherIds 查询的投手id数组
     * @return 查询条件
     */
    private Criteria myCriRolePitcher(PitcherMediaDataTotalDTO dto, List<Long> pitcherIds) {
        //根据传入的dto拼接查询参数
        Criteria cri = Cnd.cri();
        if (StringUtils.isNotBlank(dto.getSourceSystem())) {
            //拼接SDK来源
            cri.where().andEquals("source_system", dto.getSourceSystem());
        }
        if (CollectionUtils.isNotEmpty(pitcherIds)) {
            //拼接投手ID
            cri.where().andInList("user_zx_pitcher_id", pitcherIds);
        }
        if (StringUtils.isNotBlank(dto.getAccountType())) {
            //拼接推广媒体类型
            cri.where().andEquals("user_account_type", dto.getAccountType());
        }
        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 gameIds 查询的游戏id数组
     * @return 查询条件
     */
    private Criteria myCriGame(GameMediaDataTotalDTO dto, String type, List<Long> gameIds) {
        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 (StringUtils.isNotBlank(dto.getAccountType())) {
            //拼接推广媒体类型
            cri.where().andEquals("account_type", dto.getAccountType());
        }
        //根据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());
            }
        }

        return cri;
    }

    /**
     * 账面充值人数、新用户充值人数、新用户复充人数的查询条件(游戏媒体总数据、总计)
     *
     * @param dto 前端传递的查询条件参数实体
     * @param needRegTime 是否需要拼接注册时间
     * @param gameIds 查询的游戏id数组
     * @return 查询条件
     */
    private Criteria myCriRechargeGame(GameMediaDataTotalDTO dto, Boolean needRegTime, List<Long> gameIds) {
        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 (StringUtils.isNotBlank(dto.getAccountType())) {
            //拼接推广媒体类型
            cri.where().andEquals("account_type", dto.getAccountType());
        }
        //拼接不同的时间查询条件
        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 前端传递的查询条件
     * @param gameIds 查询的游戏id数组
     * @return 查询条件
     */
    private Criteria myCriRoleGame(GameMediaDataTotalDTO dto, List<Long> gameIds) {
        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 (StringUtils.isNotBlank(dto.getAccountType())) {
            //拼接推广媒体类型
            cri.where().andEquals("user_account_type", dto.getAccountType());
        }
        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;
    }

    /**
     * 媒体相关数据的每日数据SQL(推广每日数据、投手每日数据、游戏每日数据)
     * @param tableName 表名
     * @return String
     */
    private String mediaDataDaySql(String tableName) {
        StringBuilder sql = new StringBuilder(StringUtils.EMPTY);
        //不同的表，查询的字段不同
        if ("game_ads.ads_pitcher_acctype_day".equals(tableName) ||
                "game_ads_parent.ads_pitcher_acctype_day_parent".equals(tableName)) {
            //投手媒体数据（子游戏、父游戏维度）
            sql.append("""
                    SELECT
                        pitcher_id,
                        pitcher,
                    """);
        } else if ("game_ads.ads_game_pitcher_acctype_day".equals(tableName)) {
            //推广媒体数据
            sql.append("""
                    SELECT
                        pitcher_id,
                        game_id,
                        pitcher,
                        game_name,
                        game_cp,
                        game_type,
                    """);
        } else if ("game_ads.ads_game_acctype_day".equals(tableName)){
            //游戏媒体数据
            sql.append("""
                    SELECT
                        game_id,
                        game_name,
                        game_cp,
                        game_type,
                    """);
        } else if ("game_ads_parent.ads_game_pitcher_acctype_day_parent".equals(tableName)) {
            //推广媒体数据-父游戏维度
            sql.append("""
                    SELECT
                        pitcher_id,
                        parent_game_id as game_id,
                        pitcher,
                        parent_game_name as game_name,
                        parent_game_cp as game_cp,
                        parent_game_type as game_type,
                    """);
        } else {
            //游戏媒体数据 - 父游戏维度
            sql.append("""
                    SELECT
                        parent_game_id as game_id,
                        parent_game_name as game_name,
                        parent_game_cp as game_cp,
                        parent_game_type as game_type,
                    """);
        }
        //拼接公共的查询数据部分
        sql.append("""
                    dt,
                    source_system,
                    account_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    
                """);
        sql.append(tableName);

        return sql.toString();
    }

    /**
     * 媒体相关数据的每日数据总计SQL（推广媒体每日总计、投手媒体每日总计、游戏媒体每日总计）
     * @param tableName 查询的表名
     * @return String
     */
    private String mediaDataDayTotalSql(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(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,
                """ + getMeidaDataDayTotalDayNsql() +
                """
                FROM
                """ + tableName;
    }

    /**
     * 媒体相关的每日数据总计付费趋势SQL（推广媒体每日总计、投手媒体每日总计、游戏媒体每日总计）
     * @return String : Dn的充值金额 / D1-Dn的充值总金额 / Dn的充值人数 /当前消耗(剔除不存在的天数数据) /D1充值金额总和(剔除不存在的天数数据)
     */
    private String getMeidaDataDayTotalDayNsql() {
        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();
    }

    /**
     * 每日roi计算SQL(所有媒体总数据以及总数据总计的每日ROI计算)
     * @return String
     */
    private String mediaDataTotalRoiSql() {
        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 pitcherGameMediaDataTotalSql(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,
                		account_type,
                		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,
                """ + mediaDataTotalRoiSql() +
                """
                        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_acctype_day
                """ + criA +
                """
                ) a
                LEFT JOIN (
                    SELECT
                        pitcher_id as b_pitcher_id,
                        game_id as b_game_id,
                		account_type as b_account_type,
                        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_acctype_day
                """ + criB +
                """
                ) b ON a.pitcher_id = b.b_pitcher_id AND a.game_id = b.b_game_id AND a.account_type = b.b_account_type
                LEFT JOIN (
                    SELECT
                        pitcher_id as c_pitcher_id,
                        game_id as c_game_id,
                        account_type as c_account_type,
                        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 , account_type
                ) c ON a.pitcher_id = c.c_pitcher_id AND a.game_id = c.c_game_id AND a.account_type = c.c_account_type
                LEFT JOIN (
                    SELECT
                        pitcher_id as d_pitcher_id,
                        game_id as d_game_id,
                        account_type as d_account_type,
                        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 , account_type
                ) d ON a.pitcher_id = d.d_pitcher_id AND a.game_id = d.d_game_id AND a.account_type = d.d_account_type
                LEFT JOIN (
                    SELECT
                        game_id as e_game_id,
                        pitcher_id as e_pitcher_id,
                        account_type as e_account_type,
                        COUNT(tempA.num) as new_user_order_again
                    FROM (
                        SELECT
                            game_id,
                            pitcher_id,
                            account_type,
                            COUNT(user_id) num
                        FROM
                            game_ads.ads_information
                """ + criNewUser +
                """
                        AND NOT agent_id = 0
                    GROUP BY user_id, pitcher_id, game_id, account_type
                    HAVING COUNT(user_id) > 1
                    ) tempA
                GROUP BY tempA.game_id, tempA.pitcher_id, tempA.account_type
                ) e ON a.game_id = e.e_game_id AND a.pitcher_id = e.e_pitcher_id AND a.account_type = e.e_account_type
                LEFT JOIN (
                    SELECT
                        user_game_id as f_game_id,
                        user_zx_pitcher_id as f_pitcher_id,
                        user_account_type as f_account_type,
                        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, user_account_type
                ) f ON a.game_id = f.f_game_id AND a.pitcher_id = f.f_pitcher_id AND a.account_type = f.f_account_type
                """;
    }

    /**
     * 查询推广媒体总数据SQL-父游戏维度
     * @param criA       主表查询条件
     * @param criB       账面相关查询条件
     * @param criAmount  账面充值人数查询条件
     * @param criNewUser 新用户相关查询条件
     * @param criRoleNum 创角人数相关查询条件
     * @return String
     */
    private String pitcherGameMediaDataTotalSqlForParent(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,
                		account_type,
                		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,
                """ + mediaDataTotalRoiSql() +
                """
                        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_acctype_day_parent
                """ + criA +
                """
                ) a
                LEFT JOIN (
                    SELECT
                        pitcher_id as b_pitcher_id,
                        parent_game_id as b_game_id,
                		account_type as b_account_type,
                        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_acctype_day_parent
                """ + criB +
                """
                ) b ON a.pitcher_id = b.b_pitcher_id AND a.game_id = b.b_game_id AND a.account_type = b.b_account_type
                LEFT JOIN (
                    SELECT
                        pitcher_id as c_pitcher_id,
                        parent_game_id as c_game_id,
                        account_type as c_account_type,
                        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 , account_type
                ) c ON a.pitcher_id = c.c_pitcher_id AND a.game_id = c.c_game_id AND a.account_type = c.c_account_type
                LEFT JOIN (
                    SELECT
                        pitcher_id as d_pitcher_id,
                        parent_game_id as d_game_id,
                        account_type as d_account_type,
                        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 , account_type
                ) d ON a.pitcher_id = d.d_pitcher_id AND a.game_id = d.d_game_id AND a.account_type = d.d_account_type
                LEFT JOIN (
                    SELECT
                        parent_game_id as e_game_id,
                        pitcher_id as e_pitcher_id,
                        account_type as e_account_type,
                        COUNT(tempA.num) as new_user_order_again
                    FROM (
                        SELECT
                            parent_game_id,
                            pitcher_id,
                            account_type,
                            COUNT(user_id) num
                        FROM
                            game_ads.ads_information
                """ + criNewUser +
                """
                        AND NOT agent_id = 0
                    GROUP BY user_id, pitcher_id, parent_game_id, account_type
                    HAVING COUNT(user_id) > 1
                    ) tempA
                    GROUP BY tempA.parent_game_id, tempA.pitcher_id, tempA.account_type
                ) e ON a.game_id = e.e_game_id AND a.pitcher_id = e.e_pitcher_id AND a.account_type = e.e_account_type
                LEFT JOIN (
                    SELECT
                        parent_game_id as f_game_id,
                        user_zx_pitcher_id as f_pitcher_id,
                        user_account_type as f_account_type,
                        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, user_account_type
                ) f ON a.game_id = f.f_game_id AND a.pitcher_id = f.f_pitcher_id AND a.account_type = f.f_account_type
                """;
    }

    /**
     * 查询投手媒体总数据SQL
     * @param criA       主表查询条件
     * @param criB       账面相关查询条件
     * @param criAmount  账面充值人数查询条件
     * @param criNewUser 新用户相关查询条件
     * @param criRoleNum 创角人数相关查询条件
     * @return String
     */
    private String pitcherMediaDataTotalSql(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,
                		account_type,
                		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,
                """ + mediaDataTotalRoiSql() +
                """
                        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_pitcher_acctype_day
                """ + criA +
                """
                ) a
                LEFT JOIN (
                    SELECT
                        pitcher_id as b_pitcher_id,
                		account_type as b_account_type,
                        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_acctype_day
                """ + criB +
                """
                ) b ON a.pitcher_id = b.b_pitcher_id AND a.account_type = b.b_account_type
                LEFT JOIN (
                    SELECT
                        pitcher_id as c_pitcher_id,
                        account_type as c_account_type,
                        IFNULL(COUNT(DISTINCT user_id), 0) amount_num
                    FROM
                        game_ads.ads_information
                """ + criAmount +
                """
                        AND NOT agent_id = 0
                    GROUP BY pitcher_id , account_type
                ) c ON a.pitcher_id = c.c_pitcher_id AND a.account_type = c.c_account_type
                LEFT JOIN (
                    SELECT
                        pitcher_id as d_pitcher_id,
                        account_type as d_account_type,
                        COUNT(DISTINCT user_id) new_user_amount_num
                    FROM
                        game_ads.ads_information
                """ + criNewUser +
                """
                        AND NOT agent_id = 0
                    GROUP BY pitcher_id , account_type
                ) d ON a.pitcher_id = d.d_pitcher_id AND a.account_type = d.d_account_type
                LEFT JOIN (
                    SELECT
                        pitcher_id as e_pitcher_id,
                        account_type as e_account_type,
                        COUNT(tempA.num) as new_user_order_again
                    FROM (
                        SELECT
                            pitcher_id,
                            account_type,
                            COUNT(user_id) num
                        FROM
                            game_ads.ads_information
                """ + criNewUser +
                """
                        AND NOT agent_id = 0
                    GROUP BY user_id, pitcher_id , account_type
                    HAVING COUNT(user_id) > 1
                    ) tempA
                GROUP BY tempA.pitcher_id, tempA.account_type
                ) e ON a.pitcher_id = e.e_pitcher_id AND a.account_type = e.e_account_type
                LEFT JOIN (
                    SELECT
                        user_zx_pitcher_id as f_pitcher_id,
                        user_account_type as f_account_type,
                        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, user_account_type
                ) f ON a.pitcher_id = f.f_pitcher_id AND a.account_type = f.f_account_type
                """;
    }

    /**
     * 查询投手媒体总数据SQL-父游戏维度
     * @param criA       主表查询条件
     * @param criB       账面相关查询条件
     * @param criAmount  账面充值人数查询条件
     * @param criNewUser 新用户相关查询条件
     * @param criRoleNum 创角人数相关查询条件
     * @return String
     */
    private String pitcherMediaDataTotalSqlForParent(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,
                		account_type,
                		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,
                """ + mediaDataTotalRoiSql() +
                """
                        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_acctype_day_parent
                """ + criA +
                """
                ) a
                LEFT JOIN (
                    SELECT
                        pitcher_id as b_pitcher_id,
                		account_type as b_account_type,
                        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_acctype_day_parent
                """ + criB +
                """
                ) b ON a.pitcher_id = b.b_pitcher_id AND a.account_type = b.b_account_type
                LEFT JOIN (
                    SELECT
                        pitcher_id as c_pitcher_id,
                        account_type as c_account_type,
                        IFNULL(COUNT(DISTINCT user_id), 0) amount_num
                    FROM
                        game_ads.ads_information
                """ + criAmount +
                """
                        AND NOT agent_id = 0
                    GROUP BY pitcher_id , account_type
                ) c ON a.pitcher_id = c.c_pitcher_id AND a.account_type = c.c_account_type
                LEFT JOIN (
                    SELECT
                        pitcher_id as d_pitcher_id,
                        account_type as d_account_type,
                        COUNT(DISTINCT user_id) new_user_amount_num
                    FROM
                        game_ads.ads_information
                """ + criNewUser +
                """
                        AND NOT agent_id = 0
                    GROUP BY pitcher_id , account_type
                ) d ON a.pitcher_id = d.d_pitcher_id AND a.account_type = d.d_account_type
                LEFT JOIN (
                    SELECT
                        pitcher_id as e_pitcher_id,
                        account_type as e_account_type,
                        COUNT(tempA.num) as new_user_order_again
                    FROM (
                        SELECT
                            pitcher_id,
                            account_type,
                            COUNT(user_id) num
                        FROM
                            game_ads.ads_information
                """ + criNewUser +
                """
                        AND NOT agent_id = 0
                    GROUP BY user_id, pitcher_id , account_type
                    HAVING COUNT(user_id) > 1
                    ) tempA
                GROUP BY tempA.pitcher_id, tempA.account_type
                ) e ON a.pitcher_id = e.e_pitcher_id AND a.account_type = e.e_account_type
                LEFT JOIN (
                    SELECT
                        user_zx_pitcher_id as f_pitcher_id,
                        user_account_type as f_account_type,
                        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, user_account_type
                ) f ON a.pitcher_id = f.f_pitcher_id AND a.account_type = f.f_account_type
                """;
    }

    /**
     * 查询游戏媒体总数据SQL
     * @param criA       主表查询条件
     * @param criB       账面相关查询条件
     * @param criAmount  账面充值人数查询条件
     * @param criNewUser 新用户相关查询条件
     * @param criRoleNum 创角人数相关查询条件
     * @return String
     */
    private String gameMediaDataTotalSql(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
                		game_id,
                		account_type,
                		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,
                """ + mediaDataTotalRoiSql() +
                """
                        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_acctype_day
                """ + criA +
                """
                ) a
                LEFT JOIN (
                    SELECT
                        game_id as b_game_id,
                		account_type as b_account_type,
                        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_acctype_day
                """ + criB +
                """
                ) b ON a.game_id = b.b_game_id AND a.account_type = b.b_account_type
                LEFT JOIN (
                    SELECT
                        game_id as c_game_id,
                        account_type as c_account_type,
                        IFNULL(COUNT(DISTINCT user_id), 0) amount_num
                    FROM
                        game_ads.ads_information
                """ + criAmount +
                """
                        AND NOT agent_id = 0
                    GROUP BY game_id , account_type
                ) c ON a.game_id = c.c_game_id AND a.account_type = c.c_account_type
                LEFT JOIN (
                    SELECT
                        game_id as d_game_id,
                        account_type as d_account_type,
                        COUNT(DISTINCT user_id) new_user_amount_num
                    FROM
                        game_ads.ads_information
                """ + criNewUser +
                """
                        AND NOT agent_id = 0
                    GROUP BY game_id , account_type
                ) d ON a.game_id = d.d_game_id AND a.account_type = d.d_account_type
                LEFT JOIN (
                    SELECT
                        game_id as e_game_id,
                        account_type as e_account_type,
                        COUNT(tempA.num) as new_user_order_again
                    FROM (
                        SELECT
                            game_id,
                            account_type,
                            COUNT(user_id) num
                        FROM
                            game_ads.ads_information
                """ + criNewUser +
                """
                        AND NOT agent_id = 0
                    GROUP BY user_id, game_id, account_type
                    HAVING COUNT(user_id) > 1
                    ) tempA
                GROUP BY tempA.game_id, tempA.account_type
                ) e ON a.game_id = e.e_game_id AND a.account_type = e.e_account_type
                LEFT JOIN (
                    SELECT
                        user_game_id as f_game_id,
                        user_account_type as f_account_type,
                        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_account_type
                ) f ON a.game_id = f.f_game_id AND a.account_type = f.f_account_type
                """;
    }

    /**
     * 查询游戏媒体总数据SQL-父游戏维度
     * @param criA       主表查询条件
     * @param criB       账面相关查询条件
     * @param criAmount  账面充值人数查询条件
     * @param criNewUser 新用户相关查询条件
     * @param criRoleNum 创角人数相关查询条件
     * @return String
     */
    private String gameMediaDataTotalSqlForParent(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
                		parent_game_id as game_id,
                		account_type,
                		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,
                """ + mediaDataTotalRoiSql() +
                """
                        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_acctype_day_parent
                """ + criA +
                """
                ) a
                LEFT JOIN (
                    SELECT
                        parent_game_id as b_game_id,
                		account_type as b_account_type,
                        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_acctype_day_parent
                """ + criB +
                """
                ) b ON a.game_id = b.b_game_id AND a.account_type = b.b_account_type
                LEFT JOIN (
                    SELECT
                        parent_game_id as c_game_id,
                        account_type as c_account_type,
                        IFNULL(COUNT(DISTINCT user_id), 0) amount_num
                    FROM
                        game_ads.ads_information
                """ + criAmount +
                """
                        AND NOT agent_id = 0
                    GROUP BY parent_game_id , account_type
                ) c ON a.game_id = c.c_game_id AND a.account_type = c.c_account_type
                LEFT JOIN (
                    SELECT
                        parent_game_id as d_game_id,
                        account_type as d_account_type,
                        COUNT(DISTINCT user_id) new_user_amount_num
                    FROM
                        game_ads.ads_information
                """ + criNewUser +
                """
                        AND NOT agent_id = 0
                    GROUP BY parent_game_id , account_type
                ) d ON a.game_id = d.d_game_id AND a.account_type = d.d_account_type
                LEFT JOIN (
                    SELECT
                        parent_game_id as e_game_id,
                        account_type as e_account_type,
                        COUNT(tempA.num) as new_user_order_again
                    FROM (
                        SELECT
                            parent_game_id,
                            account_type,
                            COUNT(user_id) num
                        FROM
                            game_ads.ads_information
                """ + criNewUser +
                """
                        AND NOT agent_id = 0
                    GROUP BY user_id, parent_game_id, account_type
                    HAVING COUNT(user_id) > 1
                    ) tempA
                    GROUP BY tempA.parent_game_id, tempA.account_type
                ) e ON a.game_id = e.e_game_id AND a.account_type = e.e_account_type
                LEFT JOIN (
                    SELECT
                        parent_game_id as f_game_id,
                        user_account_type as f_account_type,
                        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_account_type
                ) f ON a.game_id = f.f_game_id AND a.account_type = f.f_account_type
                """;
    }

    /**
     * 查询媒体相关的总数据总计SQL（推广媒体总数据总计、投手媒体总数据总计、游戏媒体总数据总计）
     *
     * @param tableName 查询的表名
     * @return String
     */
    private String mediaDataTotalSumSql(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,
                """ + mediaDataTotalRoiSql() +
                """
                    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;
    }

}
