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

import com.google.common.base.CaseFormat;
import com.zanxiang.game.data.serve.component.DataPowerComponent;
import com.zanxiang.game.data.serve.pojo.dto.ActiveDataDayDTO;
import com.zanxiang.game.data.serve.pojo.dto.ActiveDataTotalDTO;
import com.zanxiang.game.data.serve.pojo.dto.MediaActiveDataDayDTO;
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.IActiveDataService;
import com.zanxiang.game.data.serve.utils.Page;
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.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.util.*;
import java.util.stream.Collectors;

/**
 * @author tianhua
 * @time 2023/8/23
 * @Description
 **/
@Service
public class IActiveDataServiceImpl implements IActiveDataService {

    //存储PitcherGameDataDayVO的映射
    private static final List<Tuple2<Field, Field>> activeDataDayNFieldMapList;


    //存储PitcherGameDataDayTotalVO的映射
    private static final List<Tuple2<Field, Field>> activeDataDayNTotalFieldMapList;

    //月数据
    private static final List<Tuple2<Field, Field>> activeDataMonthNFieldMapList;
    private static final List<Tuple2<Field, Field>> activeDataMonthNTotalFieldMapList;

    private static final List<Tuple2<Field, Field>> gameMediaactiveDataDayNFieldMapList;
    private static final List<Tuple2<Field, Field>> gameMediaActiveDataDayNTotalFieldMapList;


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

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


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

        //解析ActiveDataTotalVO的映射
        Map<String, Field> fieldTotalMap = new HashMap<>();
        List<Field> dayNTotalFieldList = new ArrayList<>();
        Class<?> tempTotalClazz = MediaActiveDataTotalVO.class;
        while (tempTotalClazz != null) {
            Field[] fields = tempTotalClazz.getDeclaredFields();
            for (Field field : fields) {
                if (Modifier.isFinal(field.getModifiers()) || Modifier.isStatic(field.getModifiers())) {
                    //属性前面的修饰符为static或final不加入map中
                    continue;
                }
                fieldTotalMap.put(field.getName(), field);
                if (field.getType() == ActiveDataTrendVO.class) {
                    //存储所有类型为ActiveDataTrendVO属性的映射 以Trend为结尾的
                    dayNTotalFieldList.add(field);
                }
            }
            tempTotalClazz = tempTotalClazz.getSuperclass();
        }
        if (dayNTotalFieldList.isEmpty()) {
            gameMediaActiveDataDayNTotalFieldMapList = Collections.emptyList();
        } else {
            gameMediaActiveDataDayNTotalFieldMapList = new ArrayList<>(dayNTotalFieldList.size());
            for (Field field : dayNTotalFieldList) {
                field.setAccessible(true);
                Field sourceField = fieldTotalMap.get(field.getName().replace("Trend", ""));
                sourceField.setAccessible(true);
                gameMediaActiveDataDayNTotalFieldMapList.add(Tuples.of(sourceField, field));
            }
        }

    }

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

        //解析ActiveDataTotalVO的映射
        Map<String, Field> fieldTotalMap = new HashMap<>();
        List<Field> dayNTotalFieldList = new ArrayList<>();
        Class<?> tempTotalClazz = ActiveDataMonthTotalVO.class;
        while (tempTotalClazz != null) {
            Field[] fields = tempTotalClazz.getDeclaredFields();
            for (Field field : fields) {
                if (Modifier.isFinal(field.getModifiers()) || Modifier.isStatic(field.getModifiers())) {
                    //属性前面的修饰符为static或final不加入map中
                    continue;
                }
                fieldTotalMap.put(field.getName(), field);
                if (field.getType() == ActiveDataTrendVO.class) {
                    //存储所有类型为ActiveDataTrendVO属性的映射 以Trend为结尾的
                    dayNTotalFieldList.add(field);
                }
            }
            tempTotalClazz = tempTotalClazz.getSuperclass();
        }
        if (dayNTotalFieldList.isEmpty()) {
            activeDataMonthNTotalFieldMapList = Collections.emptyList();
        } else {
            activeDataMonthNTotalFieldMapList = new ArrayList<>(dayNTotalFieldList.size());
            for (Field field : dayNTotalFieldList) {
                field.setAccessible(true);
                Field sourceField = fieldTotalMap.get(field.getName().replace("Trend", ""));
                sourceField.setAccessible(true);
                activeDataMonthNTotalFieldMapList.add(Tuples.of(sourceField, field));
            }
        }
    }

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

    /**
     * 留存每日数据
     *
     * @param dto ActiveDataDayDTO
     * @return Page<ActiveDataDayVO>
     */
    @Override
    public Page<ActiveDataDayVO> getActiveDataDay(ActiveDataDayDTO dto) {
        com.github.sd4324530.jtuple.Tuple2<List<Long>, List<Long>> poerInfo = dataPowerComponent.getPowerInfo();
        List<Long> userGameIds = dto.getGameId() == null ? poerInfo.second : dto.getGameId();
//        List<Long> userGameIds = dto.getGameId();

        //默认查询的字段及表名
        String gameColumn = "game_id";
        String classifyColumn = "game_classify";

        if (dto.getGameDimension() == null) {
            //默认子游戏维度
            dto.setGameDimension(1L);
        }
        //默认查询总数据
        if (StringUtils.isBlank(dto.getTableTypes())) {
            dto.setTableTypes("total");
        }
        //查询的class
        Class<?> tempClazz = null;
        //默认查询注册留存
        String queryTableName = StringUtils.EMPTY;
        if (StringUtils.isBlank(dto.getActiveTypes())) {
            dto.setActiveTypes("reg");
        }
        switch (dto.getActiveTypes()) {
            case "reg" -> {
                queryTableName = "ads_game_active_reg_day";
                tempClazz = AdsGameActiveRegDay.class;
            }
            case "role" -> {
                queryTableName = "ads_game_active_role_day";
                tempClazz = AdsGameActiveRoleDay.class;
            }
            case "amount" -> {
                queryTableName = "ads_game_active_order_day";
                tempClazz = AdsGameActiveOrderDay.class;
            }
        }
        if (dto.getGameDimension() == 2L) {
            gameColumn = "parent_game_id";
            classifyColumn = "parent_game_classify";
            switch (dto.getActiveTypes()) {
                case "reg" -> {
                    queryTableName = "game_ads_parent.ads_game_active_reg_day_parent";
                    tempClazz = AdsGameActiveRegDayParent.class;
                }
                case "role" -> {
                    queryTableName = "game_ads_parent.ads_game_active_role_day_parent";
                    tempClazz = AdsGameActiveRoleDayParent.class;
                }
                case "amount" -> {
                    queryTableName = "game_ads_parent.ads_game_active_order_day_parent";
                    tempClazz = AdsGameActiveOrderDayParent.class;
                }
            }
        }
        //创建查询条件
        Criteria cri = Cnd.cri();
        //拼接游戏ID
        if (CollectionUtils.isNotEmpty(userGameIds)) {
            cri.where().andInList(gameColumn, userGameIds);
        }
        //拼接游戏应用类型
        if (dto.getClassify() != null) {
            cri.where().andEquals(classifyColumn, dto.getClassify());
        }
        //拼接SDK来源
        if ("reg".equals(dto.getActiveTypes())) {
            //注册留存只看 ZX_ONE的数据
            cri.where().andEquals("source_system", "ZX_ONE");
        } else {
            if (StringUtils.isNotBlank(dto.getSourceSystem())) {
                cri.where().andEquals("source_system", dto.getSourceSystem());
            }
        }
        //拼接查询时间
        if (dto.getRegisteredBeginDate() != null && dto.getRegisteredEndDate() != null) {
            cri.where().andBetween("dt", dto.getRegisteredBeginDate(), dto.getRegisteredEndDate());
        }
        //拼接排序条件
        Criteria orderByCri = Cnd.cri();
        //如果没有排序条件给默认值
        if (StringUtils.isBlank(dto.getSortType())) {
            dto.setSortType(OrderByEnum.DESC.getOrderType());
        }
        if (StringUtils.isBlank(dto.getSortFiled())) {
            orderByCri.getOrderBy().orderBy("cost_date", 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;
        if (dto.getGameDimension() == 1L) {
            sql = Sqls.create(getActiveDataDaySql(dto.getTableTypes(), queryTableName, cri) + orderByCri);
        } else {
            sql = Sqls.create(getActiveDataDaySqlForParent(dto.getTableTypes(), queryTableName, cri) + orderByCri);
        }
        //设置自定义回传类型
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(ActiveDataDayVO.class));
        //设置pager对象
        Pager pager = dao.createPager(dto.getPageNum(), dto.getPageSize());
        sql.setPager(pager);
        //执行sql
        dao.execute(sql);
        //得到结果集list
        List<ActiveDataDayVO> list = sql.getList(ActiveDataDayVO.class);
        //设置查询总数 (根据表名)
        pager.setRecordCount(dao.count(tempClazz, cri));
        List<ActiveDataDayVO> activeDataDayVOList = list.stream().map(vo -> {
            //将string转成 ActiveDataTrendVO 对象
            formatDayN(vo, dto.getActiveTypes());
            return vo;
        }).collect(Collectors.toList());

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

    /**
     * 留存每日数据总计
     *
     * @param dto ActiveDataTotalDTO
     * @return ActiveDataTotalVO
     */
    @Override
    public ActiveDataTotalVO getActiveDataTotal(ActiveDataTotalDTO dto) {
        com.github.sd4324530.jtuple.Tuple2<List<Long>, List<Long>> poerInfo = dataPowerComponent.getPowerInfo();
        List<Long> gameIds = dto.getGameId() == null ? poerInfo.second : dto.getGameId();
//        ArrayList<Long> gameIds = new ArrayList<>();

        //默认查询字段
        String gameColumn = "game_id";
        String classifyColumn = "game_classify";
        String tableName = "game_ads.ads_game_day";

        if (dto.getGameDimension() == null) {
            //默认查询子游戏维度
            dto.setGameDimension(1L);
        }
        //默认查询总数据
        if (StringUtils.isBlank(dto.getTableTypes())) {
            dto.setTableTypes("total");
        }
        //默认查询注册留存
        String queryTableName = StringUtils.EMPTY;
        if (StringUtils.isBlank(dto.getActiveTypes())) {
            dto.setActiveTypes("reg");
        }
        switch (dto.getActiveTypes()) {
            case "reg" -> queryTableName = "ads_game_active_reg_day";
            case "role" -> queryTableName = "ads_game_active_role_day";
            case "amount" -> queryTableName = "ads_game_active_order_day";
        }
        if (dto.getGameDimension() == 2L) {
            gameColumn = "parent_game_id";
            classifyColumn = "parent_game_classify";
            tableName = "game_ads_parent.ads_game_day_parent";
            switch (dto.getActiveTypes()) {
                case "reg" -> queryTableName = "game_ads_parent.ads_game_active_reg_day_parent";
                case "role" -> queryTableName = "game_ads_parent.ads_game_active_role_day_parent";
                case "amount" -> queryTableName = "game_ads_parent.ads_game_active_order_day_parent";
            }
        }
        //创建查询条件
        Criteria cri = Cnd.cri();
        //拼接游戏ID
        if (CollectionUtils.isNotEmpty(gameIds)) {
            cri.where().andInList(gameColumn, gameIds);
        }
        //拼接游戏应用类型
        if (dto.getClassify() != null) {
            cri.where().andEquals(classifyColumn, dto.getClassify());
        }
        //拼接SDK来源
        if ("reg".equals(dto.getActiveTypes())) {
            //注册留存只看 ZX_ONE的数据
            cri.where().andEquals("source_system", "ZX_ONE");
        } else {
            if (StringUtils.isNotBlank(dto.getSourceSystem())) {
                cri.where().andEquals("source_system", dto.getSourceSystem());
            }
        }
        //拼接查询时间
        if (dto.getRegisteredBeginDate() != null && dto.getRegisteredEndDate() != null) {
            cri.where().andBetween("dt", dto.getRegisteredBeginDate(), dto.getRegisteredEndDate());
        }
        //创建sql 获取游戏数据部分的数据
        Sql sqlGameData = Sqls.create(getTotalGameData(dto.getTableTypes(), tableName) + cri);
        //设置自定义回传对象
        sqlGameData.setCallback(Sqls.callback.entity());
        sqlGameData.setEntity(dao.getEntity(ActiveDataTotalVO.class));
        //执行sql
        dao.execute(sqlGameData);
        //得到每日总计对象(游戏部分)
        ActiveDataTotalVO voGameData = sqlGameData.getObject(ActiveDataTotalVO.class);

        //创建sql 获取留存数据部分的数据
        Sql sqlActiveData = Sqls.create(activeDataTotalSql(dto.getTableTypes(), queryTableName) + cri);
        //设置自定义回传对象
        sqlActiveData.setCallback(Sqls.callback.entity());
        sqlActiveData.setEntity(dao.getEntity(ActiveDataTotalVO.class));
        //执行sql
        dao.execute(sqlActiveData);
        //得到每日总计对象
        ActiveDataTotalVO voActiveData = sqlActiveData.getObject(ActiveDataTotalVO.class);
        //voActiveData有值时进行映射的取值处理
        if (StringUtils.isNotBlank(voActiveData.getDa1())) {
            //计算留存dayN数据
            formatTotalDayN(voActiveData, dto.getActiveTypes());
        }
        //将两个对象的属性结合
        copyNullProperties(voActiveData, voGameData);
        //返回
        return voGameData;
    }

    @Override
    public Page<MediaActiveDataDayVO> getMediaActiveDataDay(MediaActiveDataDayDTO dto) {
        com.github.sd4324530.jtuple.Tuple2<List<Long>, List<Long>> poerInfo = dataPowerComponent.getPowerInfo();
        List<Long> userGameIds = dto.getGameId() == null ? poerInfo.second : dto.getGameId();
//        List<Long> userGameIds = null;
        //默认查询的字段及表名
        String gameColumn = "game_id";
        String classifyColumn = "game_classify";
        //查询的class
        Class<?> tempClazz = null;
        //默认查询注册留存
        String queryTableName = StringUtils.EMPTY;
        if (StringUtils.isBlank(dto.getActiveTypes())) {
            dto.setActiveTypes("reg");
        }
        switch (dto.getActiveTypes()) {
            case "reg" -> {
                queryTableName = "game_ads.ads_game_type_active_reg_day";
                tempClazz = AdsGameMediaActiveRegDay.class;
            }
            case "role" -> {
                queryTableName = "game_ads.ads_game_type_active_role_day";
                tempClazz = AdsGameMediaActiveRoleDay.class;
            }
            case "amount" -> {
                queryTableName = "game_ads.ads_game_type_active_order_day";
                tempClazz = AdsGameMediaActiveOrderDay.class;
            }
        }
        if (dto.getGameDimension() == 2L) {
            gameColumn = "parent_game_id";
            classifyColumn = "parent_game_classify";
            switch (dto.getActiveTypes()) {
                case "reg" -> {
                    queryTableName = "game_ads_parent.ads_game_type_active_reg_day_parent";
                    tempClazz = AdsGameMediaActiveRegDayParent.class;
                }
                case "role" -> {
                    queryTableName = "game_ads_parent.ads_game_type_active_role_day_parent";
                    tempClazz = AdsGameMediaActiveRoleDayParent.class;
                }
                case "amount" -> {
                    queryTableName = "game_ads_parent.ads_game_type_active_order_day_parent";
                    tempClazz = AdsGameMediaActiveOrderDayParent.class;
                }
            }
        }
        //创建查询条件
        Criteria cri = Cnd.cri();
        //拼接游戏ID
        if (CollectionUtils.isNotEmpty(userGameIds)) {
            cri.where().andInList(gameColumn, userGameIds);
        }
        //拼接游戏应用类型
        if (dto.getClassify() != null) {
            cri.where().andEquals(classifyColumn, dto.getClassify());
        }
        //媒体类型
        if (dto.getAccountType() != null) {
            cri.where().andEquals("account_type", dto.getAccountType());
        }
        //拼接SDK来源
        if ("reg".equals(dto.getActiveTypes())) {
            //注册留存只看 ZX_ONE的数据
            cri.where().andEquals("source_system", "ZX_ONE");
        } else {
            if (StringUtils.isNotBlank(dto.getSourceSystem())) {
                cri.where().andEquals("source_system", dto.getSourceSystem());
            }
        }
        //拼接查询时间
        if (dto.getRegisteredBeginDate() != null && dto.getRegisteredEndDate() != null) {
            cri.where().andBetween("dt", dto.getRegisteredBeginDate(), dto.getRegisteredEndDate());
        }
        //拼接排序条件
        Criteria orderByCri = Cnd.cri();
        //如果没有排序条件给默认值
        if (StringUtils.isBlank(dto.getSortType())) {
            dto.setSortType(OrderByEnum.DESC.getOrderType());
        }
        if (StringUtils.isBlank(dto.getSortFiled())) {
            orderByCri.getOrderBy().orderBy("cost_date", 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;
        if (dto.getGameDimension() == 1L) {
            sql = Sqls.create(getMediaActiveDataDaySql(queryTableName, cri) + orderByCri);
        } else {
            sql = Sqls.create(getMediaActiveDataDaySqlForParent(queryTableName, cri) + orderByCri);
        }
        //设置自定义回传类型
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(MediaActiveDataDayVO.class));
        //设置pager对象
        Pager pager = dao.createPager(dto.getPageNum(), dto.getPageSize());
        sql.setPager(pager);
        //执行sql
        dao.execute(sql);
        //得到结果集list
        List<MediaActiveDataDayVO> list = sql.getList(MediaActiveDataDayVO.class);
        //设置查询总数 (根据表名)
        pager.setRecordCount(dao.count(tempClazz, cri));
        List<MediaActiveDataDayVO> activeDataDayVOList = list.stream().map(vo -> {
            //将string转成 ActiveDataTrendVO 对象
            gameMediaActiveFormatDayN(vo, dto.getActiveTypes());
            return vo;
        }).collect(Collectors.toList());

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

    private String getMediaActiveDataDaySqlForParent(String tableName, Criteria cri) {
        return """
                SELECT
                                   a.*,
                                   b.*
                               FROM (
                                   SELECT
                                       dt cost_date,
                                     parent_game_name  as game_name,
                                      parent_game_id as game_id,
                                      parent_game_classify as game_classify,
                                       account_type,
                                       source_system,
                                       cost,
                                       register_num as reg_num,
                                       first_new_user_amount_count,
                                       first_new_user_amount_num,
                                       first_new_user_amount,
                                       old_amount_count as old_user_count,
                                       old_amount_num as old_user_num,
                                       old_amount as old_user_amount,
                                       amount_count,
                                       amount_num,
                                       amount,
                                       new_user_total_amount_count,
                                       new_user_total_amount_num,
                                       new_user_total_amount,
                                       today_roi,
                                       first_roi,
                                       first_amount_rate,
                                       today_amount_rate,
                                         new_user_amount_rate as new_user_rate,
                                       first_avg_amount,
                                       today_avg_amount,
                                       paper_avg,
                                       user_again_rate,
                                       new_reg_arpu,
                                       first_arpu,
                                       today_arpu,
                                       paper_arpu,
                                       first_amount_cost as first_new_user_recharge_cost,
                                       register_cost as reg_cost,
                                       hundred_user_num,
                                       hundred_user_num_cost,
                                       today_amount_cost as total_recharge_cost,
                                       first_role_num,
                                       role_num,
                                       new_user_total_role_num,
                                        round(if(amount_count > 0, amount / amount_count, 0), 2)                    avg_amount,
                                         round(if(first_new_user_amount_num > 0, first_new_user_amount / first_new_user_amount_num, 0),2) first_amount_arpu,
                                        round(if(amount_num > 0, amount / amount_num, 0), 2)                        amount_arpu,
                                        round(if(register_num > 0, new_user_total_amount / register_num, 0), 2)     reg_user_arpu,
                                        round(if(cost > 0, new_user_total_amount / cost, 0), 4)                     total_roi,
                                       round(IF(first_role_num > 0, cost / first_role_num, 0), 2) first_role_num_cost,
                                       round(IF(role_num > 0, cost / role_num, 0), 2) role_num_cost,
                                       round(IF(new_user_total_role_num >0, cost / new_user_total_role_num, 0), 2) new_user_total_role_num_cost,
                                       round(IF(register_num >0, first_role_num / register_num, 0), 4) first_role_num_rate,
                                       round(IF(register_num >0, role_num / register_num, 0), 4) role_num_rate,
                                       round(IF(register_num >0, new_user_total_role_num / register_num, 0), 4) new_user_total_role_num_rate
                                   FROM
                                       game_ads_parent.ads_game_type_day_parent
                               """ + cri +
                """
                                ) a
                        LEFT JOIN (
                            SELECT
                                parent_game_id ,
                                parent_game_name ,
                                dt,
                                da1,
                                da2,
                                da3,
                                da4,
                                da5,
                                da6,
                                da7,
                                da8,
                                da9,
                                da10,
                                da11,
                                da12,
                                da13,
                                da14,
                                da15,
                                da16,
                                da17,
                                da18,
                                da19,
                                da20,
                                da21,
                                da22,
                                da23,
                                da24,
                                da25,
                                da26,
                                da27,
                                da28,
                                da29,
                                da30,
                                da31,
                                da32,
                                da33,
                                da34,
                                da35,
                                da36,
                                da37,
                                da38,
                                da39,
                                da40,
                                da41,
                                da42,
                                da43,
                                da44,
                                da45,
                                da46,
                                da47,
                                da48,
                                da49,
                                da50,
                                da51,
                                da52,
                                da53,
                                da54,
                                da55,
                                da56,
                                da57,
                                da58,
                                da59,
                                da60,
                                da61,
                                da62,
                                da63,
                                da64,
                                da65,
                                da66,
                                da67,
                                da68,
                                da69,
                                da70,
                                da71,
                                da72,
                                da73,
                                da74,
                                da75,
                                da76,
                                da77,
                                da78,
                                da79,
                                da80,
                                da81,
                                da82,
                                da83,
                                da84,
                                da85,
                                da86,
                                da87,
                                da88,
                                da89,
                                da90,
                                m4,
                                m5,
                                m6,
                                m7,
                                m8,
                                m9,
                                m10,
                                m11,
                                m12
                            FROM
                        """ + tableName + cri +
                """
                                            ) b
                                        ON a.game_id = b.parent_game_id and a.cost_date = b.dt and a.game_name = b.parent_game_name
                        """;
    }

    private String getMediaActiveDataDaySql(String tableName, Criteria cri) {
        return """
                SELECT
                                   a.*,
                                   b.*
                               FROM (
                                   SELECT
                                       dt cost_date,
                                       game_name,
                                       game_id,
                                       account_type,
                                       game_classify,
                                       source_system,
                                       cost,
                                       register_num as reg_num,
                                       first_new_user_amount_count,
                                       first_new_user_amount_num,
                                       first_new_user_amount,
                                         old_amount_count as old_user_count,
                                         old_amount_num as old_user_num,
                                         old_amount as old_user_amount,
                                       amount_count,
                                       amount_num,
                                       amount,
                                       new_user_total_amount_count,
                                       new_user_total_amount_num,
                                       new_user_total_amount,
                                       today_roi,
                                       first_roi,
                                       first_amount_rate,
                                       today_amount_rate,
                                       new_user_amount_rate as new_user_rate,
                                       first_avg_amount,
                                       today_avg_amount,
                                       paper_avg,
                                       user_again_rate,
                                       new_reg_arpu,
                                       first_arpu,
                                       today_arpu,
                                       paper_arpu,
                                       first_amount_cost as first_new_user_recharge_cost,
                                        register_cost as reg_cost,
                                       hundred_user_num,
                                       hundred_user_num_cost,
                                       today_amount_cost as total_recharge_cost,
                                       first_role_num,
                                       role_num,
                                       new_user_total_role_num,
                                        round(if(amount_count > 0, amount / amount_count, 0), 2)                    avg_amount,
                                         round(if(first_new_user_amount_num > 0, first_new_user_amount / first_new_user_amount_num, 0),2) first_amount_arpu,
                                        round(if(amount_num > 0, amount / amount_num, 0), 2)                        amount_arpu,
                                        round(if(new_user_total_amount_num > 0, new_user_total_amount /new_user_total_amount_num, 0),2)   today_amount_arpu,
                                        round(if(register_num > 0, new_user_total_amount / register_num, 0), 2)     reg_user_arpu,
                                        round(if(cost > 0, new_user_total_amount / cost, 0), 4)                     total_roi,
                                       round(IF(first_role_num > 0, cost / first_role_num, 0), 2) first_role_num_cost,
                                       round(IF(role_num > 0, cost / role_num, 0), 2) role_num_cost,
                                       round(IF(new_user_total_role_num >0, cost / new_user_total_role_num, 0), 2) new_user_total_role_num_cost,
                                       round(IF(register_num >0, first_role_num / register_num, 0), 4) first_role_num_rate,
                                       round(IF(register_num >0, role_num / register_num, 0), 4) role_num_rate,
                                       round(IF(register_num >0, new_user_total_role_num / register_num, 0), 4) new_user_total_role_num_rate
                                   FROM
                                       game_ads.ads_game_type_day
                               """ + cri +
                """
                                ) a
                        LEFT JOIN (
                            SELECT
                                game_id,
                                game_name,
                                dt,
                                da1,
                                da2,
                                da3,
                                da4,
                                da5,
                                da6,
                                da7,
                                da8,
                                da9,
                                da10,
                                da11,
                                da12,
                                da13,
                                da14,
                                da15,
                                da16,
                                da17,
                                da18,
                                da19,
                                da20,
                                da21,
                                da22,
                                da23,
                                da24,
                                da25,
                                da26,
                                da27,
                                da28,
                                da29,
                                da30,
                                da31,
                                da32,
                                da33,
                                da34,
                                da35,
                                da36,
                                da37,
                                da38,
                                da39,
                                da40,
                                da41,
                                da42,
                                da43,
                                da44,
                                da45,
                                da46,
                                da47,
                                da48,
                                da49,
                                da50,
                                da51,
                                da52,
                                da53,
                                da54,
                                da55,
                                da56,
                                da57,
                                da58,
                                da59,
                                da60,
                                da61,
                                da62,
                                da63,
                                da64,
                                da65,
                                da66,
                                da67,
                                da68,
                                da69,
                                da70,
                                da71,
                                da72,
                                da73,
                                da74,
                                da75,
                                da76,
                                da77,
                                da78,
                                da79,
                                da80,
                                da81,
                                da82,
                                da83,
                                da84,
                                da85,
                                da86,
                                da87,
                                da88,
                                da89,
                                da90,
                                m4,
                                m5,
                                m6,
                                m7,
                                m8,
                                m9,
                                m10,
                                m11,
                                m12
                            FROM
                        """ + tableName + cri +
                """
                                            ) b
                                        ON a.game_id = b.game_id and a.cost_date = b.dt and a.game_name = b.game_name
                        """;
    }

    @Override
    public MediaActiveDataTotalVO getMediaActiveDataTotal(MediaActiveDataDayDTO dto) {
        com.github.sd4324530.jtuple.Tuple2<List<Long>, List<Long>> poerInfo = dataPowerComponent.getPowerInfo();
        List<Long> gameIds = dto.getGameId() == null ? poerInfo.second : dto.getGameId();
//        List<Long> gameIds = null;

        //默认查询字段
        String gameColumn = "game_id";
        String classifyColumn = "game_classify";

        //默认查询注册留存
        String queryTableName = StringUtils.EMPTY;
        if (StringUtils.isBlank(dto.getActiveTypes())) {
            dto.setActiveTypes("reg");
        }

        switch (dto.getActiveTypes()) {
            case "reg" -> {
                queryTableName = "game_ads.ads_game_type_active_reg_day";
            }
            case "role" -> {
                queryTableName = "game_ads.ads_game_type_active_role_day";
            }
            case "amount" -> {
                queryTableName = "game_ads.ads_game_type_active_order_day";
            }
        }
        if (dto.getGameDimension() == 2L) {
            gameColumn = "parent_game_id";
            classifyColumn = "parent_game_classify";
            switch (dto.getActiveTypes()) {
                case "reg" -> {
                    queryTableName = "game_ads_parent.ads_game_type_active_reg_day_parent";
                }
                case "role" -> {
                    queryTableName = "game_ads_parent.ads_game_type_active_role_day_parent";
                }
                case "amount" -> {
                    queryTableName = "game_ads_parent.ads_game_type_active_order_day_parent";
                }
            }
        }

        //创建查询条件
        Criteria cri = Cnd.cri();
        //拼接游戏ID
        if (CollectionUtils.isNotEmpty(gameIds)) {
            cri.where().andInList(gameColumn, gameIds);
        }
        //拼接游戏应用类型
        if (dto.getClassify() != null) {
            cri.where().andEquals(classifyColumn, dto.getClassify());
        }
        //拼接SDK来源
        if ("reg".equals(dto.getActiveTypes())) {
            //注册留存只看 ZX_ONE的数据
            cri.where().andEquals("source_system", "ZX_ONE");
        } else {
            if (StringUtils.isNotBlank(dto.getSourceSystem())) {
                cri.where().andEquals("source_system", dto.getSourceSystem());
            }
        }

        if (dto.getAccountType() != null) {
            cri.where().andEquals("account_type", dto.getAccountType());
        }
        //拼接查询时间
        if (dto.getRegisteredBeginDate() != null && dto.getRegisteredEndDate() != null) {
            cri.where().andBetween("dt", dto.getRegisteredBeginDate(), dto.getRegisteredEndDate());
        }
        //创建sql 获取游戏数据部分的数据
        Sql sqlGameData = Sqls.create(getTotalMediaGameData() + cri);
        //设置自定义回传对象
        sqlGameData.setCallback(Sqls.callback.entity());
        sqlGameData.setEntity(dao.getEntity(MediaActiveDataTotalVO.class));
        //执行sql
        dao.execute(sqlGameData);
        //得到每日总计对象(游戏部分)
        MediaActiveDataTotalVO voGameData = sqlGameData.getObject(MediaActiveDataTotalVO.class);

        //创建sql 获取留存数据部分的数据
        Sql sqlActiveData = Sqls.create(mediaActiveDataTotalSql(queryTableName) + cri);
        //设置自定义回传对象
        sqlActiveData.setCallback(Sqls.callback.entity());
        sqlActiveData.setEntity(dao.getEntity(MediaActiveDataTotalVO.class));
        //执行sql
        dao.execute(sqlActiveData);
        //得到每日总计对象
        MediaActiveDataTotalVO voActiveData = sqlActiveData.getObject(MediaActiveDataTotalVO.class);
        //voActiveData有值时进行映射的取值处理
        if (StringUtils.isNotBlank(voActiveData.getDa1())) {
            //计算留存dayN数据
            gameMediaformatTotalDayN(voActiveData, dto.getActiveTypes());
        }
        //将两个对象的属性结合
        copyNullProperties(voActiveData, voGameData);
        //返回
        return voGameData;
    }

    @Override
    public Page<ActiveDataMonthVO> getActiveDataMonth(ActiveDataDayDTO dto) {
        com.github.sd4324530.jtuple.Tuple2<List<Long>, List<Long>> poerInfo = dataPowerComponent.getPowerInfo();
        List<Long> userGameIds = dto.getGameId() == null ? poerInfo.second : dto.getGameId();
//        List<Long> userGameIds = dto.getGameId();

        //默认查询的字段及表名
        String gameColumn = "game_id";
        String classifyColumn = "game_classify";

        if (dto.getGameDimension() == null) {
            //默认子游戏维度
            dto.setGameDimension(1L);
        }
        //默认查询总数据
        if (StringUtils.isBlank(dto.getTableTypes())) {
            dto.setTableTypes("total");
        }
        //查询的class
        Class<?> tempClazz = null;
        //默认查询注册留存
        String queryTableName = StringUtils.EMPTY;
        if (StringUtils.isBlank(dto.getActiveTypes())) {
            dto.setActiveTypes("reg");
        }
        switch (dto.getActiveTypes()) {
            case "reg" -> {
                queryTableName = "ads_game_active_reg_month";
                tempClazz = AdsGameActiveRegMonth.class;
            }
            case "role" -> {
                queryTableName = "ads_game_active_role_month";
                tempClazz = AdsGameActiveRoleMonth.class;
            }
            case "amount" -> {
                queryTableName = "ads_game_active_order_month";
                tempClazz = AdsGameActiveOrderMonth.class;
            }
        }
        if (dto.getGameDimension() == 2L) {
            gameColumn = "parent_game_id";
            classifyColumn = "parent_game_classify";
            switch (dto.getActiveTypes()) {
                case "reg" -> {
                    queryTableName = "game_ads_parent.ads_game_active_reg_month_parent";
                    tempClazz = AdsGameActiveRegMonthParent.class;
                }
                case "role" -> {
                    queryTableName = "game_ads_parent.ads_game_active_role_month_parent";
                    tempClazz = AdsGameActiveRoleMonthParent.class;
                }
                case "amount" -> {
                    queryTableName = "game_ads_parent.ads_game_active_order_month_parent";
                    tempClazz = AdsGameActiveOrderMonthParent.class;
                }
            }
        }
        //创建查询条件
        Criteria cri = Cnd.cri();
        //拼接游戏ID
        if (CollectionUtils.isNotEmpty(userGameIds)) {
            cri.where().andInList(gameColumn, userGameIds);
        }
        //拼接游戏应用类型
        if (dto.getClassify() != null) {
            cri.where().andEquals(classifyColumn, dto.getClassify());
        }
        //拼接SDK来源
        if ("reg".equals(dto.getActiveTypes())) {
            //注册留存只看 ZX_ONE的数据
            cri.where().andEquals("source_system", "ZX_ONE");
        } else {
            if (StringUtils.isNotBlank(dto.getSourceSystem())) {
                cri.where().andEquals("source_system", dto.getSourceSystem());
            }
        }
        //拼接查询时间
        if (dto.getRegisteredBeginDate() != null && dto.getRegisteredEndDate() != null) {
            cri.where().andBetween("dt", dto.getRegisteredBeginDate(), dto.getRegisteredEndDate());
        }
        //拼接排序条件
        Criteria orderByCri = Cnd.cri();
        //如果没有排序条件给默认值
        if (StringUtils.isBlank(dto.getSortType())) {
            dto.setSortType(OrderByEnum.DESC.getOrderType());
        }
        if (StringUtils.isBlank(dto.getSortFiled())) {
            orderByCri.getOrderBy().orderBy("dt", dto.getSortType());
        } else {
            orderByCri.getOrderBy().orderBy(CaseFormat.LOWER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, dto.getSortFiled()), dto.getSortType());
        }
        //创建sql查询数据
        Sql sql;
        if (dto.getGameDimension() == 1L) {
            sql = Sqls.create(getActiveDataMonthSql(dto.getTableTypes(),dto.getActiveTypes(), queryTableName, cri) + orderByCri);
        } else {
            sql = Sqls.create(getActiveDataMonthSqlForParent(dto.getTableTypes(),dto.getActiveTypes(), queryTableName, cri) + orderByCri);
        }
        //设置自定义回传类型
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(ActiveDataMonthVO.class));
        //设置pager对象
        Pager pager = dao.createPager(dto.getPageNum(), dto.getPageSize());
        sql.setPager(pager);
        //执行sql
        dao.execute(sql);
        //得到结果集list
        List<ActiveDataMonthVO> list = sql.getList(ActiveDataMonthVO.class);
        //设置查询总数 (根据表名)
        pager.setRecordCount(dao.count(tempClazz, cri));
        List<ActiveDataMonthVO> activeDataMonthVOList = list.stream().map(vo -> {
            //将string转成 ActiveDataTrendVO 对象
            formatMonthN(vo, dto.getActiveTypes());
            return vo;
        }).collect(Collectors.toList());

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

    /**
     * 通过反射赋值留存数据monthN
     *
     * @param vo         ActiveDataMonthVO
     * @param activeType 留存类型 ： reg -> 注册留存; role -> 创角留存; amount -> 付费留存
     */
    private void formatMonthN(ActiveDataMonthVO vo, String activeType) {
        if (CollectionUtils.isEmpty(activeDataMonthNFieldMapList)) {
            return;
        }
        if ("reg".equals(activeType)) {
            //处理注册留存数据
            activeDataMonthNFieldMapList.forEach(dayNFieldMapList -> {
                try {
                    String dataStr = (String) dayNFieldMapList.getT1().get(vo);
                    if (StringUtils.isNotBlank(dataStr)) {
                        String[] temps = dataStr.split("/");
                        dayNFieldMapList.getT2().set(vo, ActiveDataTrendVO.builder()
                                .activeNum(Long.valueOf(temps[0]))
                                .regActiveRate(new BigDecimal(temps[1]))
                                .build());
                    } else {
                        dayNFieldMapList.getT2().set(vo, ActiveDataTrendVO.builder()
                                .activeNum(0L)
                                .regActiveRate(BigDecimal.ZERO)
                                .build());
                    }
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                }
            });
        } else if ("amount".equals(activeType)) {
            //处理付费留存数据
            activeDataMonthNFieldMapList.forEach(dayNFieldMapList -> {
                try {
                    //  第N天的新增创角用户，第N天的老活跃用户，第N天的累计创角用户，第N天的累计活跃用户，创角留存率
                    String dataStr = (String) dayNFieldMapList.getT1().get(vo);
                    //判断取出的字符串数据是否为空
                    if (StringUtils.isNotBlank(dataStr)) {
                        String[] temps = dataStr.split("/");
                        dayNFieldMapList.getT2().set(vo, ActiveDataTrendVO.builder()
                                .newUserAmountNum(Long.valueOf(temps[0]))
                                .oldActiveUserNum(Long.valueOf(temps[1]))
                                .newUserTotalAmountNum(Long.valueOf(temps[2]))
                                .newActiveUserTotalNum(Long.valueOf(temps[3]))
                                .amountActiveRate(new BigDecimal(temps[4]))
                                .build());
                    } else {
                        dayNFieldMapList.getT2().set(vo, ActiveDataTrendVO.builder()
                                .newUserAmountNum(0L)
                                .oldActiveUserNum(0L)
                                .newUserTotalAmountNum(0L)
                                .newActiveUserTotalNum(0L)
                                .amountActiveRate(BigDecimal.ZERO)
                                .build());
                    }
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                }
            });
        } else {
            //处理创角留存数据
            activeDataMonthNFieldMapList.forEach(dayNFieldMapList -> {
                try {
                    //第N天的新增付费用户，第N天的老活跃用户，第N天的累计付费用户，第N天的累计活跃用户，付费留存率
                    String dataStr = (String) dayNFieldMapList.getT1().get(vo);
                    if (StringUtils.isNotBlank(dataStr)) {
                        String[] temps = dataStr.split("/");
                        dayNFieldMapList.getT2().set(vo, ActiveDataTrendVO.builder()
                                .roleNewUserNum(Long.valueOf(temps[0]))
                                .roleOldActiveUserNum(Long.valueOf(temps[1]))
                                .roleNewUserTotalNum(Long.valueOf(temps[2]))
                                .roleNewActiveUserTotalNum(Long.valueOf(temps[3]))
                                .roleActiveRate(new BigDecimal(temps[4]))
                                .build());
                    } else {
                        dayNFieldMapList.getT2().set(vo, ActiveDataTrendVO.builder()
                                .roleNewUserNum(0L)
                                .roleOldActiveUserNum(0L)
                                .roleNewUserTotalNum(0L)
                                .roleNewActiveUserTotalNum(0L)
                                .roleActiveRate(BigDecimal.ZERO)
                                .build());
                    }
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                }
            });
        }
    }


    @Override
    public ActiveDataMonthTotalVO getActiveMonthDataTotal(ActiveDataTotalDTO dto) {
        com.github.sd4324530.jtuple.Tuple2<List<Long>, List<Long>> poerInfo = dataPowerComponent.getPowerInfo();
        List<Long> gameIds = dto.getGameId() == null ? poerInfo.second : dto.getGameId();
//        List<Long> gameIds = dto.getGameId();
        //默认查询字段
        String gameColumn = "game_id";
        String classifyColumn = "game_classify";
        String tableName = "game_ads.ads_game_day";

        if (dto.getGameDimension() == null) {
            //默认查询子游戏维度
            dto.setGameDimension(1L);
        }
        //默认查询总数据
        if (StringUtils.isBlank(dto.getTableTypes())) {
            dto.setTableTypes("total");
        }
        //默认查询注册留存
        String queryTableName = StringUtils.EMPTY;
        if (StringUtils.isBlank(dto.getActiveTypes())) {
            dto.setActiveTypes("reg");
        }
        switch (dto.getActiveTypes()) {
            case "reg" -> queryTableName = "ads_game_active_reg_month";
            case "role" -> queryTableName = "ads_game_active_role_month";
            case "amount" -> queryTableName = "ads_game_active_order_month";
        }
        if (dto.getGameDimension() == 2L) {
            gameColumn = "parent_game_id";
            classifyColumn = "parent_game_classify";
            tableName = "game_ads_parent.ads_game_day_parent";
            switch (dto.getActiveTypes()) {
                case "reg" -> queryTableName = "game_ads_parent.ads_game_active_reg_month_parent";
                case "role" -> queryTableName = "game_ads_parent.ads_game_active_role_month_parent";
                case "amount" -> queryTableName = "game_ads_parent.ads_game_active_order_month_parent";
            }
        }
        //创建查询条件
        Criteria cri = Cnd.cri();
        //拼接游戏ID
        if (CollectionUtils.isNotEmpty(gameIds)) {
            cri.where().andInList(gameColumn, gameIds);
        }
        //拼接游戏应用类型
        if (dto.getClassify() != null) {
            cri.where().andEquals(classifyColumn, dto.getClassify());
        }
        //拼接SDK来源
        if ("reg".equals(dto.getActiveTypes())) {
            //注册留存只看 ZX_ONE的数据
            cri.where().andEquals("source_system", "ZX_ONE");
        } else {
            if (StringUtils.isNotBlank(dto.getSourceSystem())) {
                cri.where().andEquals("source_system", dto.getSourceSystem());
            }
        }
        //拼接查询时间
        if (dto.getRegisteredBeginDate() != null && dto.getRegisteredEndDate() != null) {
            cri.where().andBetween("dt", dto.getRegisteredBeginDate(), dto.getRegisteredEndDate());
        }
        //创建sql 获取留存数据部分的数据
        Sql sqlActiveData = Sqls.create(activeDataMonthTotalSql(dto.getTableTypes(), queryTableName) + cri);
        //设置自定义回传对象
        sqlActiveData.setCallback(Sqls.callback.entity());
        sqlActiveData.setEntity(dao.getEntity(ActiveDataMonthTotalVO.class));
        //执行sql
        dao.execute(sqlActiveData);
        //得到每日总计对象
        ActiveDataMonthTotalVO voActiveData = sqlActiveData.getObject(ActiveDataMonthTotalVO.class);
        //voActiveData有值时进行映射的取值处理
        if (StringUtils.isNotBlank(voActiveData.getM1())) {
            //计算留存dayN数据
            formatTotalMonthN(voActiveData, dto.getActiveTypes());
        }
        //将两个对象的属性结合
//        copyNullProperties(voActiveData, voGameData);
        //返回
        return voActiveData;
    }

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


    /**
     * 留存每月总计sql（留存monthN数据部分）
     *
     * @param type      查询的类型 buy、nature、total
     * @param tableName 表名
     * @return String : 查询出来的结果 第n月的 充值金额/充值人数
     */
    private String activeDataMonthTotalSql(String type, String tableName) {
        String regType = new String(type);
        if ("buy".equals(type)) {
            regType = regType + "_";
        } else if ("nature".equals(type)) {
            regType = regType + "_";
        } else {
            regType = "";
        }
        //修改类型，与数据库字段名相匹配
        if ("buy".equals(type)) {
            type = type + "_";
        } else if ("nature".equals(type)) {
            type = type + "_";
        } else {
            type = "total"+"_";
        }

        //拼接查询条件
        StringBuilder trendDay = new StringBuilder(StringUtils.EMPTY);
        trendDay.append("SELECT ");
        if ("ads_game_active_reg_month".equals(tableName) ||
                "game_ads_parent.ads_game_active_reg_month_parent".equals(tableName)) {
            //m1-m23数据
            for (int month = 1; month < 24; month++) {
                trendDay.append("""
                        CONCAT( SUM(CAST(SPLIT_PART(%sm%s, '/', 1) AS BIGINT(20))), '/',
                                SUM(IF(DATE_ADD(dt, INTERVAL %s month) <= Local.now(), %sreg_num, 0))
                        ) AS m%s,
                           """.formatted(type, month, month, regType, month));
            }
            //m24数据
            trendDay.append("""
                    CONCAT( SUM(CAST(SPLIT_PART(%sm24, '/', 1) AS BIGINT(20))), '/',
                            SUM(IF(DATE_ADD(dt, INTERVAL 24 month) <= Local.now(), %sreg_num, 0))
                    ) AS m24
                       """.formatted(type, regType));
        } else if ("ads_game_active_order_month".equals(tableName) ||
                "game_ads_parent.ads_game_active_role_month_parent".equals(tableName)) {

            //m1-m23数据
            for (int month = 1; month < 24; month++) {
                trendDay.append("""
                        CONCAT( SUM(CAST(SPLIT_PART(%sm%s, '/', 1) AS BIGINT(20))), '/',
                                SUM(CAST(SPLIT_PART(%sm%s, '/', 2) AS BIGINT(20))), '/',
                                SUM(IF(DATE_ADD(dt, INTERVAL %s month) <= Local.now(), CAST(SPLIT_PART(%sm%s, '/', 3) AS BIGINT(20)), 0)), '/',
                                SUM(IF(DATE_ADD(dt, INTERVAL %s month) <= Local.now(), CAST(SPLIT_PART(%sm%s, '/', 4) AS BIGINT(20)), 0))
                        ) AS m%s,
                           """.formatted(type, month, type, month, month, type, month, month, type, month, month));
            }
            //m24数据
            trendDay.append("""
                    CONCAT( SUM(CAST(SPLIT_PART(%sm24, '/', 1) AS BIGINT(20))), '/',
                            SUM(CAST(SPLIT_PART(%sm24, '/', 2) AS BIGINT(20))), '/',
                            SUM(IF(DATE_ADD(dt, INTERVAL 24 month) <= Local.now(), CAST(SPLIT_PART(%sm24, '/', 3) AS BIGINT(20)), 0)), '/',
                            SUM(IF(DATE_ADD(dt, INTERVAL 24 month) <= Local.now(), CAST(SPLIT_PART(%sm24, '/', 4) AS BIGINT(20)), 0))
                    ) AS m24
                       """.formatted(type, type, type, type));
        } else {
            //m1-m23数据
            for (int month = 1; month < 24; month++) {
                trendDay.append("""
                        CONCAT( SUM(CAST(SPLIT_PART(%sm%s, '/', 1) AS BIGINT(20))), '/',
                                SUM(CAST(SPLIT_PART(%sm%s, '/', 2) AS BIGINT(20))), '/',
                                SUM(IF(DATE_ADD(dt, INTERVAL %s month) <= Local.now(), CAST(SPLIT_PART(%sm%s, '/', 3) AS BIGINT(20)), 0)), '/',
                                SUM(IF(DATE_ADD(dt, INTERVAL %s month) <= Local.now(), CAST(SPLIT_PART(%sm%s, '/', 4) AS BIGINT(20)), 0))
                        ) AS m%s,
                           """.formatted(type, month, type, month, month, type, month, month, type, month, month));
            }
            //m24数据
            trendDay.append("""
                    CONCAT( SUM(CAST(SPLIT_PART(%sm24, '/', 1) AS BIGINT(20))), '/',
                            SUM(CAST(SPLIT_PART(%sm24, '/', 2) AS BIGINT(20))), '/',
                            SUM(IF(DATE_ADD(dt, INTERVAL 24 month) <= Local.now(), CAST(SPLIT_PART(%sm24, '/', 3) AS BIGINT(20)), 0)), '/',
                            SUM(IF(DATE_ADD(dt, INTERVAL 24 month) <= Local.now(), CAST(SPLIT_PART(%sm24, '/', 4) AS BIGINT(20)), 0))
                    ) AS m24
                       """.formatted(type, type, type, type));
        }
        trendDay.append("FROM ");
        trendDay.append(tableName);

        return trendDay.toString();
    }


    /**
     * 留存每日总计sql（留存dayN数据部分）
     *
     * @param type      查询的类型 buy、nature、total
     * @param tableName 表名
     * @return String : 查询出来的结果 第n天的 充值金额/充值人数
     */
    private String activeDataTotalSql(String type, String tableName) {
        //修改类型，与数据库字段名相匹配
        if ("buy".equals(type)) {
            type = type + "_";
        } else if ("nature".equals(type)) {
            type = type + "_";
        } else {
            type = "";
        }
        //拼接查询条件
        StringBuilder trendDay = new StringBuilder(StringUtils.EMPTY);
        trendDay.append("SELECT ");
        if ("ads_game_active_reg_day".equals(tableName) ||
                "game_ads_parent.ads_game_active_reg_day_parent".equals(tableName)) {
            //90天数据
            for (int day = 1; day <= 90; day++) {
                trendDay.append("""
                        CONCAT( SUM(CAST(SPLIT_PART(%sda%s, '/', 1) AS BIGINT(20))), '/',
                                SUM(IF(DATE_ADD(dt, INTERVAL %s day) <= Local.now(), %sreg_num, 0))
                        ) AS da%s,
                           """.formatted(type, day, day - 1, type, day));
            }
            //m4-m11数据
            for (int month = 4; month < 12; month++) {
                trendDay.append("""
                        CONCAT( SUM(CAST(SPLIT_PART(%sm%s, '/', 1) AS BIGINT(20))), '/',
                                SUM(IF(DATE_ADD(dt, INTERVAL %s month) <= Local.now(), %sreg_num, 0))
                        ) AS m%s,
                           """.formatted(type, month, month, type, month));
            }
            //m12数据
            trendDay.append("""
                    CONCAT( SUM(CAST(SPLIT_PART(%sm12, '/', 1) AS BIGINT(20))), '/',
                            SUM(IF(DATE_ADD(dt, INTERVAL 12 month) <= Local.now(), %sreg_num, 0))
                    ) AS m12
                       """.formatted(type, type));
        } else if ("ads_game_active_order_day".equals(tableName) ||
                "game_ads_parent.ads_game_active_role_day_parent".equals(tableName)) {
            // 第N天新增付费用户数/第N天老活跃用户数/累计到第N天的付费用户数/累计到第N天的活跃人数
            //90天数据
            for (int day = 1; day <= 90; day++) {
                trendDay.append("""
                        CONCAT( SUM(CAST(SPLIT_PART(%sda%s, '/', 1) AS BIGINT(20))), '/',
                                SUM(CAST(SPLIT_PART(%sda%s, '/', 2) AS BIGINT(20))), '/',
                                SUM(IF(DATE_ADD(dt, INTERVAL %s day) <= Local.now(), CAST(SPLIT_PART(%sda%s, '/', 3) AS BIGINT(20)), 0)), '/',
                                SUM(IF(DATE_ADD(dt, INTERVAL %s day) <= Local.now(), CAST(SPLIT_PART(%sda%s, '/', 4) AS BIGINT(20)), 0))
                        ) AS da%s,
                           """.formatted(type, day, type, day, day - 1, type, day, day - 1, type, day, day));
            }
            //m4-m11数据
            for (int month = 4; month < 12; month++) {
                trendDay.append("""
                        CONCAT( SUM(CAST(SPLIT_PART(%sm%s, '/', 1) AS BIGINT(20))), '/',
                                SUM(CAST(SPLIT_PART(%sm%s, '/', 2) AS BIGINT(20))), '/',
                                SUM(IF(DATE_ADD(dt, INTERVAL %s month) <= Local.now(), CAST(SPLIT_PART(%sm%s, '/', 3) AS BIGINT(20)), 0)), '/',
                                SUM(IF(DATE_ADD(dt, INTERVAL %s month) <= Local.now(), CAST(SPLIT_PART(%sm%s, '/', 4) AS BIGINT(20)), 0))
                        ) AS m%s,
                           """.formatted(type, month, type, month, month, type, month, month, type, month, month));
            }
            //m12数据
            trendDay.append("""
                    CONCAT( SUM(CAST(SPLIT_PART(%sm12, '/', 1) AS BIGINT(20))), '/',
                            SUM(CAST(SPLIT_PART(%sm12, '/', 2) AS BIGINT(20))), '/',
                            SUM(IF(DATE_ADD(dt, INTERVAL 12 month) <= Local.now(), CAST(SPLIT_PART(%sm12, '/', 3) AS BIGINT(20)), 0)), '/',
                            SUM(IF(DATE_ADD(dt, INTERVAL 12 month) <= Local.now(), CAST(SPLIT_PART(%sm12, '/', 4) AS BIGINT(20)), 0))
                    ) AS m12
                       """.formatted(type, type, type, type));
        } else {
            // 第N天的新增创角用户/第N天的老活跃用户/第N天的累计创角用户/第N天的累计活跃用户
            //90天数据
            for (int day = 1; day <= 90; day++) {
                trendDay.append("""
                        CONCAT( SUM(CAST(SPLIT_PART(%sda%s, '/', 1) AS BIGINT(20))), '/',
                                SUM(CAST(SPLIT_PART(%sda%s, '/', 2) AS BIGINT(20))), '/',
                                SUM(IF(DATE_ADD(dt, INTERVAL %s day) <= Local.now(), CAST(SPLIT_PART(%sda%s, '/', 3) AS BIGINT(20)), 0)), '/',
                                SUM(IF(DATE_ADD(dt, INTERVAL %s day) <= Local.now(), CAST(SPLIT_PART(%sda%s, '/', 4) AS BIGINT(20)), 0))
                        ) AS da%s,
                           """.formatted(type, day, type, day, day - 1, type, day, day - 1, type, day, day));
            }
            //m4-m11数据
            for (int month = 4; month < 12; month++) {
                trendDay.append("""
                        CONCAT( SUM(CAST(SPLIT_PART(%sm%s, '/', 1) AS BIGINT(20))), '/',
                                SUM(CAST(SPLIT_PART(%sm%s, '/', 2) AS BIGINT(20))), '/',
                                SUM(IF(DATE_ADD(dt, INTERVAL %s month) <= Local.now(), CAST(SPLIT_PART(%sm%s, '/', 3) AS BIGINT(20)), 0)), '/',
                                SUM(IF(DATE_ADD(dt, INTERVAL %s month) <= Local.now(), CAST(SPLIT_PART(%sm%s, '/', 4) AS BIGINT(20)), 0))
                        ) AS m%s,
                           """.formatted(type, month, type, month, month, type, month, month, type, month, month));
            }
            //m12数据
            trendDay.append("""
                    CONCAT( SUM(CAST(SPLIT_PART(%sm12, '/', 1) AS BIGINT(20))), '/',
                            SUM(CAST(SPLIT_PART(%sm12, '/', 2) AS BIGINT(20))), '/',
                            SUM(IF(DATE_ADD(dt, INTERVAL 12 month) <= Local.now(), CAST(SPLIT_PART(%sm12, '/', 3) AS BIGINT(20)), 0)), '/',
                            SUM(IF(DATE_ADD(dt, INTERVAL 12 month) <= Local.now(), CAST(SPLIT_PART(%sm12, '/', 4) AS BIGINT(20)), 0))
                    ) AS m12
                       """.formatted(type, type, type, type));
        }
        trendDay.append("FROM ");
        trendDay.append(tableName);

        return trendDay.toString();
    }


    private String mediaActiveDataTotalSql(String tableName) {
        String type = "";
        //拼接查询条件
        StringBuilder trendDay = new StringBuilder(StringUtils.EMPTY);
        trendDay.append("SELECT ");
        if ("game_ads.ads_game_type_active_reg_day".equals(tableName) ||
                "game_ads_parent.ads_game_type_active_reg_day_parent".equals(tableName)) {
            //90天数据
            for (int day = 1; day <= 90; day++) {
                trendDay.append("""
                        CONCAT( SUM(CAST(SPLIT_PART(%sda%s, '/', 1) AS BIGINT(20))), '/',
                                SUM(IF(DATE_ADD(dt, INTERVAL %s day) <= Local.now(), %sreg_num, 0))
                        ) AS da%s,
                           """.formatted(type, day, day - 1, type, day));
            }
            //m4-m11数据
            for (int month = 4; month < 12; month++) {
                trendDay.append("""
                        CONCAT( SUM(CAST(SPLIT_PART(%sm%s, '/', 1) AS BIGINT(20))), '/',
                                SUM(IF(DATE_ADD(dt, INTERVAL %s month) <= Local.now(), %sreg_num, 0))
                        ) AS m%s,
                           """.formatted(type, month, month, type, month));
            }
            //m12数据
            trendDay.append("""
                    CONCAT( SUM(CAST(SPLIT_PART(%sm12, '/', 1) AS BIGINT(20))), '/',
                            SUM(IF(DATE_ADD(dt, INTERVAL 12 month) <= Local.now(), %sreg_num, 0))
                    ) AS m12
                       """.formatted(type, type));
        } else if ("game_ads.ads_game_type_active_order_day".equals(tableName) ||
                "game_ads_parent.ads_game_type_active_order_day_parent".equals(tableName)) {
            // 第N天新增付费用户数/第N天老活跃用户数/累计到第N天的付费用户数/累计到第N天的活跃人数
            //90天数据
            for (int day = 1; day <= 90; day++) {
                trendDay.append("""
                        CONCAT( SUM(CAST(SPLIT_PART(%sda%s, '/', 1) AS BIGINT(20))), '/',
                                SUM(CAST(SPLIT_PART(%sda%s, '/', 2) AS BIGINT(20))), '/',
                                SUM(IF(DATE_ADD(dt, INTERVAL %s day) <= Local.now(), CAST(SPLIT_PART(%sda%s, '/', 3) AS BIGINT(20)), 0)), '/',
                                SUM(IF(DATE_ADD(dt, INTERVAL %s day) <= Local.now(), CAST(SPLIT_PART(%sda%s, '/', 4) AS BIGINT(20)), 0))
                        ) AS da%s,
                           """.formatted(type, day, type, day, day - 1, type, day, day - 1, type, day, day));
            }
            //m4-m11数据
            for (int month = 4; month < 12; month++) {
                trendDay.append("""
                        CONCAT( SUM(CAST(SPLIT_PART(%sm%s, '/', 1) AS BIGINT(20))), '/',
                                SUM(CAST(SPLIT_PART(%sm%s, '/', 2) AS BIGINT(20))), '/',
                                SUM(IF(DATE_ADD(dt, INTERVAL %s month) <= Local.now(), CAST(SPLIT_PART(%sm%s, '/', 3) AS BIGINT(20)), 0)), '/',
                                SUM(IF(DATE_ADD(dt, INTERVAL %s month) <= Local.now(), CAST(SPLIT_PART(%sm%s, '/', 4) AS BIGINT(20)), 0))
                        ) AS m%s,
                           """.formatted(type, month, type, month, month, type, month, month, type, month, month));
            }
            //m12数据
            trendDay.append("""
                    CONCAT( SUM(CAST(SPLIT_PART(%sm12, '/', 1) AS BIGINT(20))), '/',
                            SUM(CAST(SPLIT_PART(%sm12, '/', 2) AS BIGINT(20))), '/',
                            SUM(IF(DATE_ADD(dt, INTERVAL 12 month) <= Local.now(), CAST(SPLIT_PART(%sm12, '/', 3) AS BIGINT(20)), 0)), '/',
                            SUM(IF(DATE_ADD(dt, INTERVAL 12 month) <= Local.now(), CAST(SPLIT_PART(%sm12, '/', 4) AS BIGINT(20)), 0))
                    ) AS m12
                       """.formatted(type, type, type, type));
        } else {
            // 第N天的新增创角用户/第N天的老活跃用户/第N天的累计创角用户/第N天的累计活跃用户
            //90天数据
            for (int day = 1; day <= 90; day++) {
                trendDay.append("""
                        CONCAT( SUM(CAST(SPLIT_PART(%sda%s, '/', 1) AS BIGINT(20))), '/',
                                SUM(CAST(SPLIT_PART(%sda%s, '/', 2) AS BIGINT(20))), '/',
                                SUM(IF(DATE_ADD(dt, INTERVAL %s day) <= Local.now(), CAST(SPLIT_PART(%sda%s, '/', 3) AS BIGINT(20)), 0)), '/',
                                SUM(IF(DATE_ADD(dt, INTERVAL %s day) <= Local.now(), CAST(SPLIT_PART(%sda%s, '/', 4) AS BIGINT(20)), 0))
                        ) AS da%s,
                           """.formatted(type, day, type, day, day - 1, type, day, day - 1, type, day, day));
            }
            //m4-m11数据
            for (int month = 4; month < 12; month++) {
                trendDay.append("""
                        CONCAT( SUM(CAST(SPLIT_PART(%sm%s, '/', 1) AS BIGINT(20))), '/',
                                SUM(CAST(SPLIT_PART(%sm%s, '/', 2) AS BIGINT(20))), '/',
                                SUM(IF(DATE_ADD(dt, INTERVAL %s month) <= Local.now(), CAST(SPLIT_PART(%sm%s, '/', 3) AS BIGINT(20)), 0)), '/',
                                SUM(IF(DATE_ADD(dt, INTERVAL %s month) <= Local.now(), CAST(SPLIT_PART(%sm%s, '/', 4) AS BIGINT(20)), 0))
                        ) AS m%s,
                           """.formatted(type, month, type, month, month, type, month, month, type, month, month));
            }
            //m12数据
            trendDay.append("""
                    CONCAT( SUM(CAST(SPLIT_PART(%sm12, '/', 1) AS BIGINT(20))), '/',
                            SUM(CAST(SPLIT_PART(%sm12, '/', 2) AS BIGINT(20))), '/',
                            SUM(IF(DATE_ADD(dt, INTERVAL 12 month) <= Local.now(), CAST(SPLIT_PART(%sm12, '/', 3) AS BIGINT(20)), 0)), '/',
                            SUM(IF(DATE_ADD(dt, INTERVAL 12 month) <= Local.now(), CAST(SPLIT_PART(%sm12, '/', 4) AS BIGINT(20)), 0))
                    ) AS m12
                       """.formatted(type, type, type, type));
        }
        trendDay.append("FROM ");
        trendDay.append(tableName);

        return trendDay.toString();
    }


    /**
     * 游戏媒体留存数据 通过反射赋值留存数据dayN
     *
     * @param vo         ActiveDataDayVO
     * @param activeType 留存类型 ： reg -> 注册留存; role -> 创角留存; amount -> 付费留存
     */
    private void gameMediaActiveFormatDayN(MediaActiveDataDayVO vo, String activeType) {
        if (CollectionUtils.isEmpty(gameMediaactiveDataDayNFieldMapList)) {
            return;
        }
        if ("reg".equals(activeType)) {
            //处理注册留存数据
            gameMediaactiveDataDayNFieldMapList.forEach(dayNFieldMapList -> {
                try {
                    String dataStr = (String) dayNFieldMapList.getT1().get(vo);
                    if (StringUtils.isNotBlank(dataStr)) {
                        String[] temps = dataStr.split("/");
                        dayNFieldMapList.getT2().set(vo, ActiveDataTrendVO.builder()
                                .activeNum(Long.valueOf(temps[0]))
                                .regActiveRate(new BigDecimal(temps[1]))
                                .build());
                    } else {
                        dayNFieldMapList.getT2().set(vo, ActiveDataTrendVO.builder()
                                .activeNum(0L)
                                .regActiveRate(BigDecimal.ZERO)
                                .build());
                    }
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                }
            });
        } else if ("amount".equals(activeType)) {
            //处理付费留存数据
            gameMediaactiveDataDayNFieldMapList.forEach(dayNFieldMapList -> {
                try {
                    //  第N天的新增创角用户，第N天的老活跃用户，第N天的累计创角用户，第N天的累计活跃用户，创角留存率
                    String dataStr = (String) dayNFieldMapList.getT1().get(vo);
                    //判断取出的字符串数据是否为空
                    if (StringUtils.isNotBlank(dataStr)) {
                        String[] temps = dataStr.split("/");
                        dayNFieldMapList.getT2().set(vo, ActiveDataTrendVO.builder()
                                .newUserAmountNum(Long.valueOf(temps[0]))
                                .oldActiveUserNum(Long.valueOf(temps[1]))
                                .newUserTotalAmountNum(Long.valueOf(temps[2]))
                                .newActiveUserTotalNum(Long.valueOf(temps[3]))
                                .amountActiveRate(new BigDecimal(temps[4]))
                                .build());
                    } else {
                        dayNFieldMapList.getT2().set(vo, ActiveDataTrendVO.builder()
                                .newUserAmountNum(0L)
                                .oldActiveUserNum(0L)
                                .newUserTotalAmountNum(0L)
                                .newActiveUserTotalNum(0L)
                                .amountActiveRate(BigDecimal.ZERO)
                                .build());
                    }
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                }
            });
        } else {
            //处理创角留存数据
            gameMediaactiveDataDayNFieldMapList.forEach(dayNFieldMapList -> {
                try {
                    //第N天的新增付费用户，第N天的老活跃用户，第N天的累计付费用户，第N天的累计活跃用户，付费留存率
                    String dataStr = (String) dayNFieldMapList.getT1().get(vo);
                    if (StringUtils.isNotBlank(dataStr)) {
                        String[] temps = dataStr.split("/");
                        dayNFieldMapList.getT2().set(vo, ActiveDataTrendVO.builder()
                                .roleNewUserNum(Long.valueOf(temps[0]))
                                .roleOldActiveUserNum(Long.valueOf(temps[1]))
                                .roleNewUserTotalNum(Long.valueOf(temps[2]))
                                .roleNewActiveUserTotalNum(Long.valueOf(temps[3]))
                                .roleActiveRate(new BigDecimal(temps[4]))
                                .build());
                    } else {
                        dayNFieldMapList.getT2().set(vo, ActiveDataTrendVO.builder()
                                .roleNewUserNum(0L)
                                .roleOldActiveUserNum(0L)
                                .roleNewUserTotalNum(0L)
                                .roleNewActiveUserTotalNum(0L)
                                .roleActiveRate(BigDecimal.ZERO)
                                .build());
                    }
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                }
            });
        }
    }


    /**
     * 通过反射赋值留存数据dayN
     *
     * @param vo         ActiveDataDayVO
     * @param activeType 留存类型 ： reg -> 注册留存; role -> 创角留存; amount -> 付费留存
     */
    private void formatDayN(ActiveDataDayVO vo, String activeType) {
        if (CollectionUtils.isEmpty(activeDataDayNFieldMapList)) {
            return;
        }
        if ("reg".equals(activeType)) {
            //处理注册留存数据
            activeDataDayNFieldMapList.forEach(dayNFieldMapList -> {
                try {
                    String dataStr = (String) dayNFieldMapList.getT1().get(vo);
                    if (StringUtils.isNotBlank(dataStr)) {
                        String[] temps = dataStr.split("/");
                        dayNFieldMapList.getT2().set(vo, ActiveDataTrendVO.builder()
                                .activeNum(Long.valueOf(temps[0]))
                                .regActiveRate(new BigDecimal(temps[1]))
                                .build());
                    } else {
                        dayNFieldMapList.getT2().set(vo, ActiveDataTrendVO.builder()
                                .activeNum(0L)
                                .regActiveRate(BigDecimal.ZERO)
                                .build());
                    }
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                }
            });
        } else if ("amount".equals(activeType)) {
            //处理付费留存数据
            activeDataDayNFieldMapList.forEach(dayNFieldMapList -> {
                try {
                    //  第N天的新增创角用户，第N天的老活跃用户，第N天的累计创角用户，第N天的累计活跃用户，创角留存率
                    String dataStr = (String) dayNFieldMapList.getT1().get(vo);
                    //判断取出的字符串数据是否为空
                    if (StringUtils.isNotBlank(dataStr)) {
                        String[] temps = dataStr.split("/");
                        dayNFieldMapList.getT2().set(vo, ActiveDataTrendVO.builder()
                                .newUserAmountNum(Long.valueOf(temps[0]))
                                .oldActiveUserNum(Long.valueOf(temps[1]))
                                .newUserTotalAmountNum(Long.valueOf(temps[2]))
                                .newActiveUserTotalNum(Long.valueOf(temps[3]))
                                .amountActiveRate(new BigDecimal(temps[4]))
                                .build());
                    } else {
                        dayNFieldMapList.getT2().set(vo, ActiveDataTrendVO.builder()
                                .newUserAmountNum(0L)
                                .oldActiveUserNum(0L)
                                .newUserTotalAmountNum(0L)
                                .newActiveUserTotalNum(0L)
                                .amountActiveRate(BigDecimal.ZERO)
                                .build());
                    }
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                }
            });
        } else {
            //处理创角留存数据
            activeDataDayNFieldMapList.forEach(dayNFieldMapList -> {
                try {
                    //第N天的新增付费用户，第N天的老活跃用户，第N天的累计付费用户，第N天的累计活跃用户，付费留存率
                    String dataStr = (String) dayNFieldMapList.getT1().get(vo);
                    if (StringUtils.isNotBlank(dataStr)) {
                        String[] temps = dataStr.split("/");
                        dayNFieldMapList.getT2().set(vo, ActiveDataTrendVO.builder()
                                .roleNewUserNum(Long.valueOf(temps[0]))
                                .roleOldActiveUserNum(Long.valueOf(temps[1]))
                                .roleNewUserTotalNum(Long.valueOf(temps[2]))
                                .roleNewActiveUserTotalNum(Long.valueOf(temps[3]))
                                .roleActiveRate(new BigDecimal(temps[4]))
                                .build());
                    } else {
                        dayNFieldMapList.getT2().set(vo, ActiveDataTrendVO.builder()
                                .roleNewUserNum(0L)
                                .roleOldActiveUserNum(0L)
                                .roleNewUserTotalNum(0L)
                                .roleNewActiveUserTotalNum(0L)
                                .roleActiveRate(BigDecimal.ZERO)
                                .build());
                    }
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                }
            });
        }
    }


    /**
     * 通过反射赋值留存数据总计的monthN
     * 活跃人数/注册人数（去掉时间没到的数据）
     *
     * @param vo         ActiveDataMonthTotalVO
     * @param activeType 留存类型 ： reg -> 注册留存; role -> 创角留存; amount -> 付费留存
     */
    private void formatTotalMonthN(ActiveDataMonthTotalVO vo, String activeType) {
        if (CollectionUtils.isEmpty(activeDataMonthNTotalFieldMapList)) {
            return;
        }
        if ("reg".equals(activeType)) {
            activeDataMonthNTotalFieldMapList.forEach(dayNFieldMapList -> {
                try {
                    String[] temps = ((String) dayNFieldMapList.getT1().get(vo)).split("/");
                    //活跃人数
                    Long activeNum = Long.valueOf(temps[0]);
                    //注册人数
                    Long regNum = Long.valueOf(temps[1]);
                    dayNFieldMapList.getT2().set(vo, ActiveDataTrendVO.builder()
                            .activeNum(activeNum)
                            .regActiveRate(regNum == 0L ? BigDecimal.ZERO :
                                    BigDecimal.valueOf(activeNum.doubleValue() / regNum.doubleValue()).setScale(4, RoundingMode.HALF_UP))
                            .build());
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                }
            });
        } else if ("amount".equals(activeType)) {
            activeDataMonthNTotalFieldMapList.forEach(dayNFieldMapList -> {
                try {
                    // 第N天新增付费用户数/第N天老活跃用户数/累计到第N天的付费用户数/累计到第N天的活跃人数
                    String[] temps = ((String) dayNFieldMapList.getT1().get(vo)).split("/");
                    //累计到第N天的活跃人数(活跃人数)
                    Long activeUserNum = Long.valueOf(temps[3]);
                    //累计到第N天的付费人数
                    Long amountTotalNum = Long.valueOf(temps[2]);
                    dayNFieldMapList.getT2().set(vo, ActiveDataTrendVO.builder()
                            .newUserAmountNum(Long.valueOf(temps[0]))
                            .oldActiveUserNum(Long.valueOf(temps[1]))
                            .newUserTotalAmountNum(amountTotalNum)
                            .newActiveUserTotalNum(activeUserNum)
                            .amountActiveRate(amountTotalNum == 0L ? BigDecimal.ZERO :
                                    BigDecimal.valueOf(activeUserNum.doubleValue() / amountTotalNum.doubleValue())
                                            .setScale(4, RoundingMode.HALF_UP))
                            .build());
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                }
            });
        } else {
            activeDataMonthNTotalFieldMapList.forEach(dayNFieldMapList -> {
                try {
                    // 第N天的新增创角用户/第N天的老活跃用户/第N天的累计创角用户/第N天的累计活跃用户
                    String[] temps = ((String) dayNFieldMapList.getT1().get(vo)).split("/");
                    //累计到第N天的活跃人数(活跃人数)
                    Long roleActiveUserNum = Long.valueOf(temps[3]);
                    //累计到第N天的创角用户数
                    Long roleTotalNum = Long.valueOf(temps[2]);
                    dayNFieldMapList.getT2().set(vo, ActiveDataTrendVO.builder()
                            .roleNewUserNum(Long.valueOf(temps[0]))
                            .roleOldActiveUserNum(Long.valueOf(temps[1]))
                            .roleNewUserTotalNum(roleTotalNum)
                            .roleNewActiveUserTotalNum(roleActiveUserNum)
                            .roleActiveRate(roleTotalNum == 0L ? BigDecimal.ZERO :
                                    BigDecimal.valueOf(roleActiveUserNum.doubleValue() / roleTotalNum.doubleValue())
                                            .setScale(4, RoundingMode.HALF_UP))
                            .build());
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                }
            });
        }
    }

    /**
     * 通过反射赋值留存数据总计的dayN
     * 活跃人数/注册人数（去掉时间没到的数据）
     *
     * @param vo         ActiveDataTotalVO
     * @param activeType 留存类型 ： reg -> 注册留存; role -> 创角留存; amount -> 付费留存
     */
    private void formatTotalDayN(ActiveDataTotalVO vo, String activeType) {
        if (CollectionUtils.isEmpty(activeDataDayNTotalFieldMapList)) {
            return;
        }
        if ("reg".equals(activeType)) {
            activeDataDayNTotalFieldMapList.forEach(dayNFieldMapList -> {
                try {
                    String[] temps = ((String) dayNFieldMapList.getT1().get(vo)).split("/");
                    //活跃人数
                    Long activeNum = Long.valueOf(temps[0]);
                    //注册人数
                    Long regNum = Long.valueOf(temps[1]);
                    dayNFieldMapList.getT2().set(vo, ActiveDataTrendVO.builder()
                            .activeNum(activeNum)
                            .regActiveRate(regNum == 0L ? BigDecimal.ZERO :
                                    BigDecimal.valueOf(activeNum.doubleValue() / regNum.doubleValue()).setScale(4, RoundingMode.HALF_UP))
                            .build());
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                }
            });
        } else if ("amount".equals(activeType)) {
            activeDataDayNTotalFieldMapList.forEach(dayNFieldMapList -> {
                try {
                    // 第N天新增付费用户数/第N天老活跃用户数/累计到第N天的付费用户数/累计到第N天的活跃人数
                    String[] temps = ((String) dayNFieldMapList.getT1().get(vo)).split("/");
                    //累计到第N天的活跃人数(活跃人数)
                    Long activeUserNum = Long.valueOf(temps[3]);
                    //累计到第N天的付费人数
                    Long amountTotalNum = Long.valueOf(temps[2]);
                    dayNFieldMapList.getT2().set(vo, ActiveDataTrendVO.builder()
                            .newUserAmountNum(Long.valueOf(temps[0]))
                            .oldActiveUserNum(Long.valueOf(temps[1]))
                            .newUserTotalAmountNum(amountTotalNum)
                            .newActiveUserTotalNum(activeUserNum)
                            .amountActiveRate(amountTotalNum == 0L ? BigDecimal.ZERO :
                                    BigDecimal.valueOf(activeUserNum.doubleValue() / amountTotalNum.doubleValue())
                                            .setScale(4, RoundingMode.HALF_UP))
                            .build());
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                }
            });
        } else {
            activeDataDayNTotalFieldMapList.forEach(dayNFieldMapList -> {
                try {
                    // 第N天的新增创角用户/第N天的老活跃用户/第N天的累计创角用户/第N天的累计活跃用户
                    String[] temps = ((String) dayNFieldMapList.getT1().get(vo)).split("/");
                    //累计到第N天的活跃人数(活跃人数)
                    Long roleActiveUserNum = Long.valueOf(temps[3]);
                    //累计到第N天的创角用户数
                    Long roleTotalNum = Long.valueOf(temps[2]);
                    dayNFieldMapList.getT2().set(vo, ActiveDataTrendVO.builder()
                            .roleNewUserNum(Long.valueOf(temps[0]))
                            .roleOldActiveUserNum(Long.valueOf(temps[1]))
                            .roleNewUserTotalNum(roleTotalNum)
                            .roleNewActiveUserTotalNum(roleActiveUserNum)
                            .roleActiveRate(roleTotalNum == 0L ? BigDecimal.ZERO :
                                    BigDecimal.valueOf(roleActiveUserNum.doubleValue() / roleTotalNum.doubleValue())
                                            .setScale(4, RoundingMode.HALF_UP))
                            .build());
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                }
            });
        }
    }

    /**
     * 通过反射赋值留存数据总计的dayN
     * 活跃人数/注册人数（去掉时间没到的数据）
     *
     * @param vo         ActiveDataTotalVO
     * @param activeType 留存类型 ： reg -> 注册留存; role -> 创角留存; amount -> 付费留存
     */
    private void gameMediaformatTotalDayN(MediaActiveDataTotalVO vo, String activeType) {
        if (CollectionUtils.isEmpty(gameMediaActiveDataDayNTotalFieldMapList)) {
            return;
        }
        if ("reg".equals(activeType)) {
            gameMediaActiveDataDayNTotalFieldMapList.forEach(dayNFieldMapList -> {
                try {
                    String[] temps = ((String) dayNFieldMapList.getT1().get(vo)).split("/");
                    //活跃人数
                    Long activeNum = Long.valueOf(temps[0]);
                    //注册人数
                    Long regNum = Long.valueOf(temps[1]);
                    dayNFieldMapList.getT2().set(vo, ActiveDataTrendVO.builder()
                            .activeNum(activeNum)
                            .regActiveRate(regNum == 0L ? BigDecimal.ZERO :
                                    BigDecimal.valueOf(activeNum.doubleValue() / regNum.doubleValue()).setScale(4, RoundingMode.HALF_UP))
                            .build());
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                }
            });
        } else if ("amount".equals(activeType)) {
            gameMediaActiveDataDayNTotalFieldMapList.forEach(dayNFieldMapList -> {
                try {
                    // 第N天新增付费用户数/第N天老活跃用户数/累计到第N天的付费用户数/累计到第N天的活跃人数
                    String[] temps = ((String) dayNFieldMapList.getT1().get(vo)).split("/");
                    //累计到第N天的活跃人数(活跃人数)
                    Long activeUserNum = Long.valueOf(temps[3]);
                    //累计到第N天的付费人数
                    Long amountTotalNum = Long.valueOf(temps[2]);
                    dayNFieldMapList.getT2().set(vo, ActiveDataTrendVO.builder()
                            .newUserAmountNum(Long.valueOf(temps[0]))
                            .oldActiveUserNum(Long.valueOf(temps[1]))
                            .newUserTotalAmountNum(amountTotalNum)
                            .newActiveUserTotalNum(activeUserNum)
                            .amountActiveRate(amountTotalNum == 0L ? BigDecimal.ZERO :
                                    BigDecimal.valueOf(activeUserNum.doubleValue() / amountTotalNum.doubleValue())
                                            .setScale(4, RoundingMode.HALF_UP))
                            .build());
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                }
            });
        } else {
            gameMediaActiveDataDayNTotalFieldMapList.forEach(dayNFieldMapList -> {
                try {
                    // 第N天的新增创角用户/第N天的老活跃用户/第N天的累计创角用户/第N天的累计活跃用户
                    String[] temps = ((String) dayNFieldMapList.getT1().get(vo)).split("/");
                    //累计到第N天的活跃人数(活跃人数)
                    Long roleActiveUserNum = Long.valueOf(temps[3]);
                    //累计到第N天的创角用户数
                    Long roleTotalNum = Long.valueOf(temps[2]);
                    dayNFieldMapList.getT2().set(vo, ActiveDataTrendVO.builder()
                            .roleNewUserNum(Long.valueOf(temps[0]))
                            .roleOldActiveUserNum(Long.valueOf(temps[1]))
                            .roleNewUserTotalNum(roleTotalNum)
                            .roleNewActiveUserTotalNum(roleActiveUserNum)
                            .roleActiveRate(roleTotalNum == 0L ? BigDecimal.ZERO :
                                    BigDecimal.valueOf(roleActiveUserNum.doubleValue() / roleTotalNum.doubleValue())
                                            .setScale(4, RoundingMode.HALF_UP))
                            .build());
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                }
            });
        }
    }

    /**
     * 留存每日数据sql
     *
     * @param type      查询的类型 buy -> 买量  ;  nature ->  自然量 ; total -> 总量
     * @param tableName 查询的表名
     * @param cri       查询条件
     * @return String
     */
    private String getActiveDataDaySql(String type, String tableName, Criteria cri) {
        if ("buy".equals(type)) {
            return """
                    SELECT
                        a.*,
                        b.*
                    FROM (
                        SELECT
                            dt cost_date,
                            game_name,
                            game_id,
                            game_classify,
                            source_system,
                            cost,
                            buy_reg_num as reg_num,
                            buy_first_new_user_amount_count as first_new_user_amount_count,
                            buy_first_new_user_amount_num as first_new_user_amount_num,
                            buy_first_new_user_amount as first_new_user_amount,
                            buy_old_user_count as old_user_count,
                            buy_old_user_num as old_user_num,
                            buy_old_user_amount as old_user_amount,
                            buy_amount_count as amount_count,
                            buy_amount_num as amount_num,
                            buy_amount as amount,
                            buy_new_user_total_amount_count as new_user_total_amount_count,
                            buy_new_user_total_amount_num as new_user_total_amount_num,
                            buy_new_user_total_amount as new_user_total_amount,
                            buy_total_roi as total_roi,
                            buy_first_roi as first_roi,
                            buy_first_amount_rate as first_amount_rate,
                            buy_today_amount_rate as today_amount_rate,
                            buy_new_user_rate as new_user_rate,
                            buy_first_avg_amount as first_avg_amount,
                            buy_today_avg_amount as today_avg_amount,
                            buy_avg_amount as avg_amount,
                            buy_user_again_rate as user_again_rate,
                            buy_reg_user_arpu as reg_user_arpu,
                            buy_first_amount_arpu as first_amount_arpu,
                            buy_today_amount_arpu as today_amount_arpu,
                            buy_amount_arpu as amount_arpu,
                            buy_reg_cost as reg_cost,
                            buy_first_amount_cost as first_new_user_recharge_cost,
                            buy_total_amount_cost as total_recharge_cost,
                            buy_hundred_user_num as hundred_user_num,
                            buy_hundred_user_num_cost as hundred_user_num_cost,
                            buy_first_role_num as first_role_num,
                            buy_role_num as role_num,
                            buy_new_user_total_role_num as new_user_total_role_num,
                            round(IF(buy_first_role_num > 0, cost / buy_first_role_num, 0), 2) first_role_num_cost,
                            round(IF(buy_role_num > 0, cost / buy_role_num, 0), 2) role_num_cost,
                            round(IF(buy_new_user_total_role_num >0, cost / buy_new_user_total_role_num, 0), 2) new_user_total_role_num_cost,
                            round(IF(buy_reg_num >0, buy_first_role_num / buy_reg_num, 0), 4) first_role_num_rate,
                            round(IF(buy_reg_num >0, buy_role_num / buy_reg_num, 0), 4) role_num_rate,
                            round(IF(buy_reg_num >0, buy_new_user_total_role_num / buy_reg_num, 0), 4) new_user_total_role_num_rate
                        FROM
                            ads_game_day
                    """ + cri +
                    """
                                    ) a
                            LEFT JOIN (
                                SELECT
                                    game_id,
                                    game_name,
                                    dt,
                                    buy_da1 as da1,
                                    buy_da2 as da2,
                                    buy_da3 as da3,
                                    buy_da4 as da4,
                                    buy_da5 as da5,
                                    buy_da6 as da6,
                                    buy_da7 as da7,
                                    buy_da8 as da8,
                                    buy_da9 as da9,
                                    buy_da10 as da10,
                                    buy_da11 as da11,
                                    buy_da12 as da12,
                                    buy_da13 as da13,
                                    buy_da14 as da14,
                                    buy_da15 as da15,
                                    buy_da16 as da16,
                                    buy_da17 as da17,
                                    buy_da18 as da18,
                                    buy_da19 as da19,
                                    buy_da20 as da20,
                                    buy_da21 as da21,
                                    buy_da22 as da22,
                                    buy_da23 as da23,
                                    buy_da24 as da24,
                                    buy_da25 as da25,
                                    buy_da26 as da26,
                                    buy_da27 as da27,
                                    buy_da28 as da28,
                                    buy_da29 as da29,
                                    buy_da30 as da30,
                                    buy_da31 as da31,
                                    buy_da32 as da32,
                                    buy_da33 as da33,
                                    buy_da34 as da34,
                                    buy_da35 as da35,
                                    buy_da36 as da36,
                                    buy_da37 as da37,
                                    buy_da38 as da38,
                                    buy_da39 as da39,
                                    buy_da40 as da40,
                                    buy_da41 as da41,
                                    buy_da42 as da42,
                                    buy_da43 as da43,
                                    buy_da44 as da44,
                                    buy_da45 as da45,
                                    buy_da46 as da46,
                                    buy_da47 as da47,
                                    buy_da48 as da48,
                                    buy_da49 as da49,
                                    buy_da50 as da50,
                                    buy_da51 as da51,
                                    buy_da52 as da52,
                                    buy_da53 as da53,
                                    buy_da54 as da54,
                                    buy_da55 as da55,
                                    buy_da56 as da56,
                                    buy_da57 as da57,
                                    buy_da58 as da58,
                                    buy_da59 as da59,
                                    buy_da60 as da60,
                                    buy_da61 as da61,
                                    buy_da62 as da62,
                                    buy_da63 as da63,
                                    buy_da64 as da64,
                                    buy_da65 as da65,
                                    buy_da66 as da66,
                                    buy_da67 as da67,
                                    buy_da68 as da68,
                                    buy_da69 as da69,
                                    buy_da70 as da70,
                                    buy_da71 as da71,
                                    buy_da72 as da72,
                                    buy_da73 as da73,
                                    buy_da74 as da74,
                                    buy_da75 as da75,
                                    buy_da76 as da76,
                                    buy_da77 as da77,
                                    buy_da78 as da78,
                                    buy_da79 as da79,
                                    buy_da80 as da80,
                                    buy_da81 as da81,
                                    buy_da82 as da82,
                                    buy_da83 as da83,
                                    buy_da84 as da84,
                                    buy_da85 as da85,
                                    buy_da86 as da86,
                                    buy_da87 as da87,
                                    buy_da88 as da88,
                                    buy_da89 as da89,
                                    buy_da90 as da90,
                                    buy_m4 as m4,
                                    buy_m5 as m5,
                                    buy_m6 as m6,
                                    buy_m7 as m7,
                                    buy_m8 as m8,
                                    buy_m9 as m9,
                                    buy_m10 as m10,
                                    buy_m11 as m11,
                                    buy_m12 as m12
                                FROM
                            """ + tableName + cri +
                    """
                                ) b
                            ON a.game_id = b.game_id and a.cost_date = b.dt and a.game_name = b.game_name
                            """;
        } else if ("nature".equals(type)) {
            return """
                    SELECT
                        a.*,
                        b.*
                    FROM (
                        SELECT
                            dt cost_date,
                            game_name,
                            game_id,
                            game_classify,
                            source_system,
                            cost,
                            nature_reg_num as reg_num,
                            nature_first_new_user_amount_count as first_new_user_amount_count,
                            nature_first_new_user_amount_num as first_new_user_amount_num,
                            nature_first_new_user_amount as first_new_user_amount,
                            nature_old_user_count as old_user_count,
                            nature_old_user_num as old_user_num,
                            nature_old_user_amount as old_user_amount,
                            nature_amount_count as amount_count,
                            nature_amount_num as amount_num,
                            nature_amount as amount,
                            nature_new_user_total_amount_count as new_user_total_amount_count,
                            nature_new_user_total_amount_num as new_user_total_amount_num,
                            nature_new_user_total_amount as new_user_total_amount,
                            nature_total_roi as total_roi,
                            nature_first_roi as first_roi,
                            nature_first_amount_rate as first_amount_rate,
                            nature_today_amount_rate as today_amount_rate,
                            nature_new_user_rate as new_user_rate,
                            nature_first_avg_amount as first_avg_amount,
                            nature_today_avg_amount as today_avg_amount,
                            nature_avg_amount as avg_amount,
                            nature_user_again_rate as user_again_rate,
                            nature_reg_user_arpu as reg_user_arpu,
                            nature_first_amount_arpu as first_amount_arpu,
                            nature_today_amount_arpu as today_amount_arpu,
                            nature_amount_arpu as amount_arpu,
                            nature_reg_cost as reg_cost,
                            nature_first_amount_cost as first_new_user_recharge_cost,
                            nature_total_amount_cost as total_recharge_cost,
                            nature_hundred_user_num as hundred_user_num,
                            nature_hundred_user_num_cost as hundred_user_num_cost,
                            nature_first_role_num as first_role_num,
                            nature_role_num as role_num,
                            nature_new_user_total_role_num as new_user_total_role_num,
                            round(IF(nature_first_role_num > 0, cost / nature_first_role_num, 0), 2) first_role_num_cost,
                            round(IF(nature_role_num > 0, cost / nature_role_num, 0), 2) role_num_cost,
                            round(IF(nature_new_user_total_role_num >0, cost / nature_new_user_total_role_num, 0), 2) new_user_total_role_num_cost,
                            round(IF(nature_reg_num >0, nature_first_role_num / nature_reg_num, 0), 4) first_role_num_rate,
                            round(IF(nature_reg_num >0, nature_role_num / nature_reg_num, 0), 4) role_num_rate,
                            round(IF(nature_reg_num >0, nature_new_user_total_role_num / nature_reg_num, 0), 4) new_user_total_role_num_rate
                        FROM
                            ads_game_day
                    """ + cri +
                    """
                                    ) a
                            LEFT JOIN (
                                SELECT
                                    game_id,
                                    game_name,
                                    dt,
                                    nature_da1 as da1,
                                    nature_da2 as da2,
                                    nature_da3 as da3,
                                    nature_da4 as da4,
                                    nature_da5 as da5,
                                    nature_da6 as da6,
                                    nature_da7 as da7,
                                    nature_da8 as da8,
                                    nature_da9 as da9,
                                    nature_da10 as da10,
                                    nature_da11 as da11,
                                    nature_da12 as da12,
                                    nature_da13 as da13,
                                    nature_da14 as da14,
                                    nature_da15 as da15,
                                    nature_da16 as da16,
                                    nature_da17 as da17,
                                    nature_da18 as da18,
                                    nature_da19 as da19,
                                    nature_da20 as da20,
                                    nature_da21 as da21,
                                    nature_da22 as da22,
                                    nature_da23 as da23,
                                    nature_da24 as da24,
                                    nature_da25 as da25,
                                    nature_da26 as da26,
                                    nature_da27 as da27,
                                    nature_da28 as da28,
                                    nature_da29 as da29,
                                    nature_da30 as da30,
                                    nature_da31 as da31,
                                    nature_da32 as da32,
                                    nature_da33 as da33,
                                    nature_da34 as da34,
                                    nature_da35 as da35,
                                    nature_da36 as da36,
                                    nature_da37 as da37,
                                    nature_da38 as da38,
                                    nature_da39 as da39,
                                    nature_da40 as da40,
                                    nature_da41 as da41,
                                    nature_da42 as da42,
                                    nature_da43 as da43,
                                    nature_da44 as da44,
                                    nature_da45 as da45,
                                    nature_da46 as da46,
                                    nature_da47 as da47,
                                    nature_da48 as da48,
                                    nature_da49 as da49,
                                    nature_da50 as da50,
                                    nature_da51 as da51,
                                    nature_da52 as da52,
                                    nature_da53 as da53,
                                    nature_da54 as da54,
                                    nature_da55 as da55,
                                    nature_da56 as da56,
                                    nature_da57 as da57,
                                    nature_da58 as da58,
                                    nature_da59 as da59,
                                    nature_da60 as da60,
                                    nature_da61 as da61,
                                    nature_da62 as da62,
                                    nature_da63 as da63,
                                    nature_da64 as da64,
                                    nature_da65 as da65,
                                    nature_da66 as da66,
                                    nature_da67 as da67,
                                    nature_da68 as da68,
                                    nature_da69 as da69,
                                    nature_da70 as da70,
                                    nature_da71 as da71,
                                    nature_da72 as da72,
                                    nature_da73 as da73,
                                    nature_da74 as da74,
                                    nature_da75 as da75,
                                    nature_da76 as da76,
                                    nature_da77 as da77,
                                    nature_da78 as da78,
                                    nature_da79 as da79,
                                    nature_da80 as da80,
                                    nature_da81 as da81,
                                    nature_da82 as da82,
                                    nature_da83 as da83,
                                    nature_da84 as da84,
                                    nature_da85 as da85,
                                    nature_da86 as da86,
                                    nature_da87 as da87,
                                    nature_da88 as da88,
                                    nature_da89 as da89,
                                    nature_da90 as da90,
                                    nature_m4 as m4,
                                    nature_m5 as m5,
                                    nature_m6 as m6,
                                    nature_m7 as m7,
                                    nature_m8 as m8,
                                    nature_m9 as m9,
                                    nature_m10 as m10,
                                    nature_m11 as m11,
                                    nature_m12 as m12
                                FROM
                            """ + tableName + cri +
                    """
                                ) b
                            ON a.game_id = b.game_id and a.cost_date = b.dt and a.game_name = b.game_name
                            """;
        }
        //总量 total
        return """
                SELECT
                    a.*,
                    b.*
                FROM (
                    SELECT
                        dt cost_date,
                        game_name,
                        game_id,
                        game_classify,
                        source_system,
                        cost,
                        reg_num,
                        first_new_user_amount_count,
                        first_new_user_amount_num,
                        first_new_user_amount,
                        old_user_count,
                        old_user_num,
                        old_user_amount,
                        amount_count,
                        amount_num,
                        amount,
                        new_user_total_amount_count,
                        new_user_total_amount_num,
                        new_user_total_amount,
                        total_roi,
                        first_roi,
                        first_amount_rate,
                        today_amount_rate,
                        new_user_rate,
                        first_avg_amount,
                        today_avg_amount,
                        avg_amount,
                        user_again_rate,
                        reg_user_arpu,
                        first_amount_arpu,
                        today_amount_arpu,
                        amount_arpu,
                        reg_cost,
                        first_amount_cost as first_new_user_recharge_cost,
                        total_amount_cost as total_recharge_cost,
                        hundred_user_num,
                        hundred_user_num_cost,
                        first_role_num,
                        role_num,
                        new_user_total_role_num,
                        round(IF(first_role_num > 0, cost / first_role_num, 0), 2) first_role_num_cost,
                        round(IF(role_num > 0, cost / role_num, 0), 2) role_num_cost,
                        round(IF(new_user_total_role_num >0, cost / new_user_total_role_num, 0), 2) new_user_total_role_num_cost,
                        round(IF(reg_num >0, first_role_num / reg_num, 0), 4) first_role_num_rate,
                        round(IF(reg_num >0, role_num / reg_num, 0), 4) role_num_rate,
                        round(IF(reg_num >0, new_user_total_role_num / reg_num, 0), 4) new_user_total_role_num_rate
                    FROM
                        ads_game_day
                """ + cri +
                """
                                ) a
                        LEFT JOIN (
                            SELECT
                                game_id,
                                game_name,
                                dt,
                                da1,
                                da2,
                                da3,
                                da4,
                                da5,
                                da6,
                                da7,
                                da8,
                                da9,
                                da10,
                                da11,
                                da12,
                                da13,
                                da14,
                                da15,
                                da16,
                                da17,
                                da18,
                                da19,
                                da20,
                                da21,
                                da22,
                                da23,
                                da24,
                                da25,
                                da26,
                                da27,
                                da28,
                                da29,
                                da30,
                                da31,
                                da32,
                                da33,
                                da34,
                                da35,
                                da36,
                                da37,
                                da38,
                                da39,
                                da40,
                                da41,
                                da42,
                                da43,
                                da44,
                                da45,
                                da46,
                                da47,
                                da48,
                                da49,
                                da50,
                                da51,
                                da52,
                                da53,
                                da54,
                                da55,
                                da56,
                                da57,
                                da58,
                                da59,
                                da60,
                                da61,
                                da62,
                                da63,
                                da64,
                                da65,
                                da66,
                                da67,
                                da68,
                                da69,
                                da70,
                                da71,
                                da72,
                                da73,
                                da74,
                                da75,
                                da76,
                                da77,
                                da78,
                                da79,
                                da80,
                                da81,
                                da82,
                                da83,
                                da84,
                                da85,
                                da86,
                                da87,
                                da88,
                                da89,
                                da90,
                                m4,
                                m5,
                                m6,
                                m7,
                                m8,
                                m9,
                                m10,
                                m11,
                                m12
                            FROM
                        """ + tableName + cri +
                """
                            ) b
                        ON a.game_id = b.game_id and a.cost_date = b.dt and a.game_name = b.game_name
                        """;
    }

    /**
     * 留存每月数据sql
     *
     * @param type      查询的类型 buy -> 买量  ;  nature ->  自然量 ; total -> 总量
     * @param tableName 查询的表名
     * @param cri       查询条件
     * @return String
     */
    private String getActiveDataMonthSql(String type, String activeTypes,String tableName, Criteria cri) {
        boolean isReg = "reg".equals(activeTypes);
        if ("buy".equals(type)) {
            String regNumField = "buy_reg_num as reg_num,";
            if(!isReg){
                regNumField = "";
            }
            return """
                    SELECT
                                                        dt,
                                                        source_system,
                                                        game_id,
                                                        game_name,
                                                        game_classify,
                                                        """
                                                        +regNumField+
                                                        """
                                                        buy_m1 as m1,
                                                        buy_m2 as m2,
                                                        buy_m3 as m3,
                                                        buy_m4 as m4,
                                                        buy_m5 as m5,
                                                        buy_m6 as m6,
                                                        buy_m7 as m7,
                                                        buy_m8 as m8,
                                                        buy_m9 as m9,
                                                        buy_m10 as m10,
                                                        buy_m11 as m11,
                                                        buy_m12 as m12,
                                                        buy_m13  as m13,
                                                        buy_m14 as m14,
                                                        buy_m15 as m15,
                                                        buy_m16 as m16,
                                                        buy_m17 as m17,
                                                        buy_m18 as m18,
                                                        buy_m19 as m19,
                                                        buy_m20 as m20,
                                                        buy_m21 as m21,
                                                        buy_m22 as m22,
                                                        buy_m23 as m23,
                                                        buy_m24 as m24
                                                    FROM """ +" " + tableName + cri;
        } else if ("nature".equals(type)) {
            String regNumField = "nature_reg_num as reg_num,";
            if(!isReg){
                regNumField = "";
            }
            return """
                    SELECT
                                                        dt,
                                                        source_system,
                                                        game_id,
                                                        game_name,
                                                        game_classify,
                                                        """
                                                        +regNumField+
                                                        """
                                                        nature_m1 as m1,
                                                        nature_m2 as m2,
                                                        nature_m3 as m3,
                                                        nature_m4 as m4,
                                                        nature_m5 as m5,
                                                        nature_m6 as m6,
                                                        nature_m7 as m7,
                                                        nature_m8 as m8,
                                                        nature_m9 as m9,
                                                        nature_m10 as m10,
                                                        nature_m11 as m11,
                                                        nature_m12 as m12,
                                                        nature_m13  as m13,
                                                        nature_m14 as m14,
                                                        nature_m15 as m15,
                                                        nature_m16 as m16,
                                                        nature_m17 as m17,
                                                        nature_m18 as m18,
                                                        nature_m19 as m19,
                                                        nature_m20 as m20,
                                                        nature_m21 as m21,
                                                        nature_m22 as m22,
                                                        nature_m23 as m23,
                                                        nature_m24 as m24
                                                    FROM """ + " "+tableName + cri;
        }
        String regNumField = "reg_num,";
        if(!isReg){
            regNumField = "";
        }
        //总量 total
        return """
                    SELECT
                                                        dt,
                                                        source_system,
                                                        game_id,
                                                        game_name,
                                                        game_classify,
                                                        """
                                                        +regNumField+
                                                        """
                                                        total_m1 as m1,
                                                        total_m2 as m2,
                                                        total_m3 as m3,
                                                        total_m4 as m4,
                                                        total_m5 as m5,
                                                        total_m6 as m6,
                                                        total_m7 as m7,
                                                        total_m8 as m8,
                                                        total_m9 as m9,
                                                        total_m10 as m10,
                                                        total_m11 as m11,
                                                        total_m12 as m12,
                                                        total_m13  as m13,
                                                        total_m14 as m14,
                                                        total_m15 as m15,
                                                        total_m16 as m16,
                                                        total_m17 as m17,
                                                        total_m18 as m18,
                                                        total_m19 as m19,
                                                        total_m20 as m20,
                                                        total_m21 as m21,
                                                        total_m22 as m22,
                                                        total_m23 as m23,
                                                        total_m24 as m24
                                                    FROM """ + " " +tableName + cri;
    }

    /**
     * 留存每日数据sql-父游戏维度
     *
     * @param type      查询的类型 buy -> 买量  ;  nature ->  自然量 ; total -> 总量
     * @param tableName 查询的表名
     * @param cri       查询条件
     * @return String
     */
    private String getActiveDataDaySqlForParent(String type, String tableName, Criteria cri) {
        if ("buy".equals(type)) {
            return """
                    SELECT
                        a.*,
                        b.*
                    FROM (
                        SELECT
                            dt cost_date,
                            parent_game_name as game_name,
                            parent_game_id as game_id,
                            parent_game_name_classify as game_classify,
                            source_system,
                            cost,
                            buy_reg_num as reg_num,
                            buy_first_new_user_amount_count as first_new_user_amount_count,
                            buy_first_new_user_amount_num as first_new_user_amount_num,
                            buy_first_new_user_amount as first_new_user_amount,
                            buy_old_user_count as old_user_count,
                            buy_old_user_num as old_user_num,
                            buy_old_user_amount as old_user_amount,
                            buy_amount_count as amount_count,
                            buy_amount_num as amount_num,
                            buy_amount as amount,
                            buy_new_user_total_amount_count as new_user_total_amount_count,
                            buy_new_user_total_amount_num as new_user_total_amount_num,
                            buy_new_user_total_amount as new_user_total_amount,
                            buy_total_roi as total_roi,
                            buy_first_roi as first_roi,
                            buy_first_amount_rate as first_amount_rate,
                            buy_today_amount_rate as today_amount_rate,
                            buy_new_user_rate as new_user_rate,
                            buy_first_avg_amount as first_avg_amount,
                            buy_today_avg_amount as today_avg_amount,
                            buy_avg_amount as avg_amount,
                            buy_user_again_rate as user_again_rate,
                            buy_reg_user_arpu as reg_user_arpu,
                            buy_first_amount_arpu as first_amount_arpu,
                            buy_today_amount_arpu as today_amount_arpu,
                            buy_amount_arpu as amount_arpu,
                            buy_reg_cost as reg_cost,
                            buy_first_amount_cost as first_new_user_recharge_cost,
                            buy_total_amount_cost as total_recharge_cost,
                            buy_hundred_user_num as hundred_user_num,
                            buy_hundred_user_num_cost as hundred_user_num_cost,
                            buy_first_role_num as first_role_num,
                            buy_role_num as role_num,
                            buy_new_user_total_role_num as new_user_total_role_num,
                            round(IF(buy_first_role_num > 0, cost / buy_first_role_num, 0), 2) first_role_num_cost,
                            round(IF(buy_role_num > 0, cost / buy_role_num, 0), 2) role_num_cost,
                            round(IF(buy_new_user_total_role_num >0, cost / buy_new_user_total_role_num, 0), 2) new_user_total_role_num_cost,
                            round(IF(buy_reg_num >0, buy_first_role_num / buy_reg_num, 0), 4) first_role_num_rate,
                            round(IF(buy_reg_num >0, buy_role_num / buy_reg_num, 0), 4) role_num_rate,
                            round(IF(buy_reg_num >0, buy_new_user_total_role_num / buy_reg_num, 0), 4) new_user_total_role_num_rate
                        FROM
                            game_ads_parent.ads_game_day_parent
                    """ + cri +
                    """
                                    ) a
                            LEFT JOIN (
                                SELECT
                                    parent_game_id as game_id,
                                    parent_game_name as game_name,
                                    dt,
                                    buy_da1 as da1,
                                    buy_da2 as da2,
                                    buy_da3 as da3,
                                    buy_da4 as da4,
                                    buy_da5 as da5,
                                    buy_da6 as da6,
                                    buy_da7 as da7,
                                    buy_da8 as da8,
                                    buy_da9 as da9,
                                    buy_da10 as da10,
                                    buy_da11 as da11,
                                    buy_da12 as da12,
                                    buy_da13 as da13,
                                    buy_da14 as da14,
                                    buy_da15 as da15,
                                    buy_da16 as da16,
                                    buy_da17 as da17,
                                    buy_da18 as da18,
                                    buy_da19 as da19,
                                    buy_da20 as da20,
                                    buy_da21 as da21,
                                    buy_da22 as da22,
                                    buy_da23 as da23,
                                    buy_da24 as da24,
                                    buy_da25 as da25,
                                    buy_da26 as da26,
                                    buy_da27 as da27,
                                    buy_da28 as da28,
                                    buy_da29 as da29,
                                    buy_da30 as da30,
                                    buy_da31 as da31,
                                    buy_da32 as da32,
                                    buy_da33 as da33,
                                    buy_da34 as da34,
                                    buy_da35 as da35,
                                    buy_da36 as da36,
                                    buy_da37 as da37,
                                    buy_da38 as da38,
                                    buy_da39 as da39,
                                    buy_da40 as da40,
                                    buy_da41 as da41,
                                    buy_da42 as da42,
                                    buy_da43 as da43,
                                    buy_da44 as da44,
                                    buy_da45 as da45,
                                    buy_da46 as da46,
                                    buy_da47 as da47,
                                    buy_da48 as da48,
                                    buy_da49 as da49,
                                    buy_da50 as da50,
                                    buy_da51 as da51,
                                    buy_da52 as da52,
                                    buy_da53 as da53,
                                    buy_da54 as da54,
                                    buy_da55 as da55,
                                    buy_da56 as da56,
                                    buy_da57 as da57,
                                    buy_da58 as da58,
                                    buy_da59 as da59,
                                    buy_da60 as da60,
                                    buy_da61 as da61,
                                    buy_da62 as da62,
                                    buy_da63 as da63,
                                    buy_da64 as da64,
                                    buy_da65 as da65,
                                    buy_da66 as da66,
                                    buy_da67 as da67,
                                    buy_da68 as da68,
                                    buy_da69 as da69,
                                    buy_da70 as da70,
                                    buy_da71 as da71,
                                    buy_da72 as da72,
                                    buy_da73 as da73,
                                    buy_da74 as da74,
                                    buy_da75 as da75,
                                    buy_da76 as da76,
                                    buy_da77 as da77,
                                    buy_da78 as da78,
                                    buy_da79 as da79,
                                    buy_da80 as da80,
                                    buy_da81 as da81,
                                    buy_da82 as da82,
                                    buy_da83 as da83,
                                    buy_da84 as da84,
                                    buy_da85 as da85,
                                    buy_da86 as da86,
                                    buy_da87 as da87,
                                    buy_da88 as da88,
                                    buy_da89 as da89,
                                    buy_da90 as da90,
                                    buy_m4 as m4,
                                    buy_m5 as m5,
                                    buy_m6 as m6,
                                    buy_m7 as m7,
                                    buy_m8 as m8,
                                    buy_m9 as m9,
                                    buy_m10 as m10,
                                    buy_m11 as m11,
                                    buy_m12 as m12
                                FROM
                            """ + tableName + cri +
                    """
                                ) b
                            ON a.game_id = b.game_id and a.cost_date = b.dt and a.game_name = b.game_name
                            """;
        } else if ("nature".equals(type)) {
            return """
                    SELECT
                        a.*,
                        b.*
                    FROM (
                        SELECT
                            dt cost_date,
                            parent_game_name as game_name,
                            parent_game_id as game_id,
                            parent_game_name_classify as game_classify,
                            source_system,
                            cost,
                            nature_reg_num as reg_num,
                            nature_first_new_user_amount_count as first_new_user_amount_count,
                            nature_first_new_user_amount_num as first_new_user_amount_num,
                            nature_first_new_user_amount as first_new_user_amount,
                            nature_old_user_count as old_user_count,
                            nature_old_user_num as old_user_num,
                            nature_old_user_amount as old_user_amount,
                            nature_amount_count as amount_count,
                            nature_amount_num as amount_num,
                            nature_amount as amount,
                            nature_new_user_total_amount_count as new_user_total_amount_count,
                            nature_new_user_total_amount_num as new_user_total_amount_num,
                            nature_new_user_total_amount as new_user_total_amount,
                            nature_total_roi as total_roi,
                            nature_first_roi as first_roi,
                            nature_first_amount_rate as first_amount_rate,
                            nature_today_amount_rate as today_amount_rate,
                            nature_new_user_rate as new_user_rate,
                            nature_first_avg_amount as first_avg_amount,
                            nature_today_avg_amount as today_avg_amount,
                            nature_avg_amount as avg_amount,
                            nature_user_again_rate as user_again_rate,
                            nature_reg_user_arpu as reg_user_arpu,
                            nature_first_amount_arpu as first_amount_arpu,
                            nature_today_amount_arpu as today_amount_arpu,
                            nature_amount_arpu as amount_arpu,
                            nature_reg_cost as reg_cost,
                            nature_first_amount_cost as first_new_user_recharge_cost,
                            nature_total_amount_cost as total_recharge_cost,
                            nature_hundred_user_num as hundred_user_num,
                            nature_hundred_user_num_cost as hundred_user_num_cost,
                            nature_first_role_num as first_role_num,
                            nature_role_num as role_num,
                            nature_new_user_total_role_num as new_user_total_role_num,
                            round(IF(nature_first_role_num > 0, cost / nature_first_role_num, 0), 2) first_role_num_cost,
                            round(IF(nature_role_num > 0, cost / nature_role_num, 0), 2) role_num_cost,
                            round(IF(nature_new_user_total_role_num >0, cost / nature_new_user_total_role_num, 0), 2) new_user_total_role_num_cost,
                            round(IF(nature_reg_num >0, nature_first_role_num / nature_reg_num, 0), 4) first_role_num_rate,
                            round(IF(nature_reg_num >0, nature_role_num / nature_reg_num, 0), 4) role_num_rate,
                            round(IF(nature_reg_num >0, nature_new_user_total_role_num / nature_reg_num, 0), 4) new_user_total_role_num_rate
                        FROM
                            game_ads_parent.ads_game_day_parent
                    """ + cri +
                    """
                                    ) a
                            LEFT JOIN (
                                SELECT
                                    parent_game_id as game_id,
                                    parent_game_name as game_name,
                                    dt,
                                    nature_da1 as da1,
                                    nature_da2 as da2,
                                    nature_da3 as da3,
                                    nature_da4 as da4,
                                    nature_da5 as da5,
                                    nature_da6 as da6,
                                    nature_da7 as da7,
                                    nature_da8 as da8,
                                    nature_da9 as da9,
                                    nature_da10 as da10,
                                    nature_da11 as da11,
                                    nature_da12 as da12,
                                    nature_da13 as da13,
                                    nature_da14 as da14,
                                    nature_da15 as da15,
                                    nature_da16 as da16,
                                    nature_da17 as da17,
                                    nature_da18 as da18,
                                    nature_da19 as da19,
                                    nature_da20 as da20,
                                    nature_da21 as da21,
                                    nature_da22 as da22,
                                    nature_da23 as da23,
                                    nature_da24 as da24,
                                    nature_da25 as da25,
                                    nature_da26 as da26,
                                    nature_da27 as da27,
                                    nature_da28 as da28,
                                    nature_da29 as da29,
                                    nature_da30 as da30,
                                    nature_da31 as da31,
                                    nature_da32 as da32,
                                    nature_da33 as da33,
                                    nature_da34 as da34,
                                    nature_da35 as da35,
                                    nature_da36 as da36,
                                    nature_da37 as da37,
                                    nature_da38 as da38,
                                    nature_da39 as da39,
                                    nature_da40 as da40,
                                    nature_da41 as da41,
                                    nature_da42 as da42,
                                    nature_da43 as da43,
                                    nature_da44 as da44,
                                    nature_da45 as da45,
                                    nature_da46 as da46,
                                    nature_da47 as da47,
                                    nature_da48 as da48,
                                    nature_da49 as da49,
                                    nature_da50 as da50,
                                    nature_da51 as da51,
                                    nature_da52 as da52,
                                    nature_da53 as da53,
                                    nature_da54 as da54,
                                    nature_da55 as da55,
                                    nature_da56 as da56,
                                    nature_da57 as da57,
                                    nature_da58 as da58,
                                    nature_da59 as da59,
                                    nature_da60 as da60,
                                    nature_da61 as da61,
                                    nature_da62 as da62,
                                    nature_da63 as da63,
                                    nature_da64 as da64,
                                    nature_da65 as da65,
                                    nature_da66 as da66,
                                    nature_da67 as da67,
                                    nature_da68 as da68,
                                    nature_da69 as da69,
                                    nature_da70 as da70,
                                    nature_da71 as da71,
                                    nature_da72 as da72,
                                    nature_da73 as da73,
                                    nature_da74 as da74,
                                    nature_da75 as da75,
                                    nature_da76 as da76,
                                    nature_da77 as da77,
                                    nature_da78 as da78,
                                    nature_da79 as da79,
                                    nature_da80 as da80,
                                    nature_da81 as da81,
                                    nature_da82 as da82,
                                    nature_da83 as da83,
                                    nature_da84 as da84,
                                    nature_da85 as da85,
                                    nature_da86 as da86,
                                    nature_da87 as da87,
                                    nature_da88 as da88,
                                    nature_da89 as da89,
                                    nature_da90 as da90,
                                    nature_m4 as m4,
                                    nature_m5 as m5,
                                    nature_m6 as m6,
                                    nature_m7 as m7,
                                    nature_m8 as m8,
                                    nature_m9 as m9,
                                    nature_m10 as m10,
                                    nature_m11 as m11,
                                    nature_m12 as m12
                                FROM
                            """ + tableName + cri +
                    """
                                ) b
                            ON a.game_id = b.game_id and a.cost_date = b.dt and a.game_name = b.game_name
                            """;
        }
        //总量 total
        return """
                SELECT
                    a.*,
                    b.*
                FROM (
                    SELECT
                        dt cost_date,
                        parent_game_name as game_name,
                        parent_game_id as game_id,
                        parent_game_name_classify as game_classify,
                        source_system,
                        cost,
                        reg_num,
                        first_new_user_amount_count,
                        first_new_user_amount_num,
                        first_new_user_amount,
                        old_user_count,
                        old_user_num,
                        old_user_amount,
                        amount_count,
                        amount_num,
                        amount,
                        new_user_total_amount_count,
                        new_user_total_amount_num,
                        new_user_total_amount,
                        total_roi,
                        first_roi,
                        first_amount_rate,
                        today_amount_rate,
                        new_user_rate,
                        first_avg_amount,
                        today_avg_amount,
                        avg_amount,
                        user_again_rate,
                        reg_user_arpu,
                        first_amount_arpu,
                        today_amount_arpu,
                        amount_arpu,
                        reg_cost,
                        first_amount_cost as first_new_user_recharge_cost,
                        total_amount_cost as total_recharge_cost,
                        hundred_user_num,
                        hundred_user_num_cost,
                        first_role_num,
                        role_num,
                        new_user_total_role_num,
                        round(IF(first_role_num > 0, cost / first_role_num, 0), 2) first_role_num_cost,
                        round(IF(role_num > 0, cost / role_num, 0), 2) role_num_cost,
                        round(IF(new_user_total_role_num >0, cost / new_user_total_role_num, 0), 2) new_user_total_role_num_cost,
                        round(IF(reg_num >0, first_role_num / reg_num, 0), 4) first_role_num_rate,
                        round(IF(reg_num >0, role_num / reg_num, 0), 4) role_num_rate,
                        round(IF(reg_num >0, new_user_total_role_num / reg_num, 0), 4) new_user_total_role_num_rate
                    FROM
                        game_ads_parent.ads_game_day_parent
                """ + cri +
                """
                                ) a
                        LEFT JOIN (
                            SELECT
                                parent_game_id as game_id,
                                parent_game_name as game_name,
                                dt,
                                da1,
                                da2,
                                da3,
                                da4,
                                da5,
                                da6,
                                da7,
                                da8,
                                da9,
                                da10,
                                da11,
                                da12,
                                da13,
                                da14,
                                da15,
                                da16,
                                da17,
                                da18,
                                da19,
                                da20,
                                da21,
                                da22,
                                da23,
                                da24,
                                da25,
                                da26,
                                da27,
                                da28,
                                da29,
                                da30,
                                da31,
                                da32,
                                da33,
                                da34,
                                da35,
                                da36,
                                da37,
                                da38,
                                da39,
                                da40,
                                da41,
                                da42,
                                da43,
                                da44,
                                da45,
                                da46,
                                da47,
                                da48,
                                da49,
                                da50,
                                da51,
                                da52,
                                da53,
                                da54,
                                da55,
                                da56,
                                da57,
                                da58,
                                da59,
                                da60,
                                da61,
                                da62,
                                da63,
                                da64,
                                da65,
                                da66,
                                da67,
                                da68,
                                da69,
                                da70,
                                da71,
                                da72,
                                da73,
                                da74,
                                da75,
                                da76,
                                da77,
                                da78,
                                da79,
                                da80,
                                da81,
                                da82,
                                da83,
                                da84,
                                da85,
                                da86,
                                da87,
                                da88,
                                da89,
                                da90,
                                m4,
                                m5,
                                m6,
                                m7,
                                m8,
                                m9,
                                m10,
                                m11,
                                m12
                            FROM
                        """ + tableName + cri +
                """
                            ) b
                        ON a.game_id = b.game_id and a.cost_date = b.dt and a.game_name = b.game_name
                        """;
    }


    /**
     * 留存每日数据sql-父游戏维度
     *
     * @param type      查询的类型 buy -> 买量  ;  nature ->  自然量 ; total -> 总量
     * @param tableName 查询的表名
     * @param cri       查询条件
     * @return String
     */
    private String getActiveDataMonthSqlForParent(String type,String activeTypes, String tableName, Criteria cri) {
        boolean isReg = "reg".equals(activeTypes);
        if ("buy".equals(type)) {
            String regNumField = "buy_reg_num as reg_num,";
            if(!isReg){
                regNumField = "";
            }
            return """
                    SELECT
                                                        dt,
                                                        source_system,
                                                        parent_game_id as game_id,
                                                        parent_game_name as game_name,
                                                        parent_game_classify as game_classify,
                                                        """
                                                            +regNumField+
                                                        """
                                                        buy_m1 as m1,
                                                        buy_m2 as m2,
                                                        buy_m3 as m3,
                                                        buy_m4 as m4,
                                                        buy_m5 as m5,
                                                        buy_m6 as m6,
                                                        buy_m7 as m7,
                                                        buy_m8 as m8,
                                                        buy_m9 as m9,
                                                        buy_m10 as m10,
                                                        buy_m11 as m11,
                                                        buy_m12 as m12,
                                                        buy_m13  as m13,
                                                        buy_m14 as m14,
                                                        buy_m15 as m15,
                                                        buy_m16 as m16,
                                                        buy_m17 as m17,
                                                        buy_m18 as m18,
                                                        buy_m19 as m19,
                                                        buy_m20 as m20,
                                                        buy_m21 as m21,
                                                        buy_m22 as m22,
                                                        buy_m23 as m23,
                                                        buy_m24 as m24
                                                    FROM """ + " " + tableName + cri;
        } else if ("nature".equals(type)) {
            String regNumField = "nature_reg_num as reg_num,";
            if(!isReg){
                regNumField = "";
            }
            return """
                    SELECT
                                                        dt,
                                                        source_system,
                                                        parent_game_id as game_id,
                                                        parent_game_name as game_name,
                                                        parent_game_classify as game_classify,
                                                        """
                                                        +regNumField+
                                                        """
                                                        nature_m1 as m1,
                                                        nature_m2 as m2,
                                                        nature_m3 as m3,
                                                        nature_m4 as m4,
                                                        nature_m5 as m5,
                                                        nature_m6 as m6,
                                                        nature_m7 as m7,
                                                        nature_m8 as m8,
                                                        nature_m9 as m9,
                                                        nature_m10 as m10,
                                                        nature_m11 as m11,
                                                        nature_m12 as m12,
                                                        nature_m13  as m13,
                                                        nature_m14 as m14,
                                                        nature_m15 as m15,
                                                        nature_m16 as m16,
                                                        nature_m17 as m17,
                                                        nature_m18 as m18,
                                                        nature_m19 as m19,
                                                        nature_m20 as m20,
                                                        nature_m21 as m21,
                                                        nature_m22 as m22,
                                                        nature_m23 as m23,
                                                        nature_m24 as m24
                                                    FROM """ + " " + tableName + cri;
        }
        String regNumField = "reg_num,";
        if(!isReg){
            regNumField = "";
        }
        //总量 total
        return """
                    SELECT
                                                        dt,
                                                        source_system,
                                                        parent_game_id as game_id,
                                                        parent_game_name as game_name,
                                                        parent_game_classify as game_classify,
                                                        """
                                                        +regNumField+
                                                        """
                                                        total_m1 as m1,
                                                        total_m2 as m2,
                                                        total_m3 as m3,
                                                        total_m4 as m4,
                                                        total_m5 as m5,
                                                        total_m6 as m6,
                                                        total_m7 as m7,
                                                        total_m8 as m8,
                                                        total_m9 as m9,
                                                        total_m10 as m10,
                                                        total_m11 as m11,
                                                        total_m12 as m12,
                                                        total_m13  as m13,
                                                        total_m14 as m14,
                                                        total_m15 as m15,
                                                        total_m16 as m16,
                                                        total_m17 as m17,
                                                        total_m18 as m18,
                                                        total_m19 as m19,
                                                        total_m20 as m20,
                                                        total_m21 as m21,
                                                        total_m22 as m22,
                                                        total_m23 as m23,
                                                        total_m24 as m24
                                                    FROM """ + " " + tableName + cri;
    }

    /**
     * 游戏数据每日总计一栏（按类型返回sql）
     *
     * @param type 查询的类型 buy、nature、total
     * @return String
     */
    private String getTotalGameData(String type, String tableName) {
        if ("buy".equals(type)) {
            return """
                    SELECT
                        IFNULL(SUM(cost), 0) cost,
                        IFNULL(SUM(buy_reg_num), 0) reg_num,
                        IFNULL(SUM(buy_first_new_user_amount_count), 0) first_new_user_amount_count,
                        IFNULL(SUM(buy_first_new_user_amount_num), 0) first_new_user_amount_num,
                        IFNULL(SUM(buy_first_new_user_amount), 0) first_new_user_amount,
                        IFNULL(SUM(buy_old_user_count), 0) old_user_count,
                        IFNULL(SUM(buy_old_user_num), 0) old_user_num,
                        IFNULL(SUM(buy_old_user_amount), 0) old_user_amount,
                        IFNULL(SUM(buy_amount_count), 0) amount_count,
                        IFNULL(SUM(buy_amount_num), 0) amount_num,
                        IFNULL(SUM(buy_amount), 0) amount,
                        IFNULL(SUM(buy_new_user_total_amount_count), 0) new_user_total_amount_count,
                        IFNULL(SUM(buy_new_user_total_amount_num), 0) new_user_total_amount_num,
                        IFNULL(SUM(buy_new_user_total_amount), 0) new_user_total_amount,
                        round(if(SUM(cost) > 0 , SUM(buy_first_new_user_amount) / SUM(cost) ,0), 4) first_roi,
                        round(if(SUM(buy_reg_num) > 0 , SUM(buy_first_new_user_amount_num) / SUM(buy_reg_num) ,0), 4) first_amount_rate,
                        round(if(SUM(buy_reg_num) > 0, SUM(buy_new_user_total_amount_num) / SUM(buy_reg_num), 0) ,4) today_amount_rate,
                        round(if(SUM(buy_amount_num) > 0 , SUM(buy_first_new_user_amount_num) / SUM(buy_amount_num) ,0), 4) new_user_rate,
                        round(if(SUM(buy_first_new_user_amount_count) > 0, SUM(buy_first_new_user_amount) / SUM(buy_first_new_user_amount_count), 0), 2) first_avg_amount,
                        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) today_avg_amount,
                        round(if(SUM(buy_amount_count) > 0, SUM(buy_amount) / SUM(buy_amount_count), 0), 2) avg_amount,
                        round(if(SUM(buy_new_user_total_amount_num) > 0, SUM(buy_reg_order_user_again) / SUM(buy_new_user_total_amount_num), 0), 4) user_again_rate,
                        round(if(SUM(buy_reg_num) > 0, SUM(buy_new_user_total_amount) / SUM(buy_reg_num), 0), 2) reg_user_arpu,
                        round(if(SUM(buy_first_new_user_amount_num) > 0 , SUM(buy_first_new_user_amount) / SUM(buy_first_new_user_amount_num), 0), 2) first_amount_arpu,
                        round(if(SUM(buy_new_user_total_amount_num) > 0 , SUM(buy_new_user_total_amount) / SUM(buy_new_user_total_amount_num), 0), 2) today_amount_arpu,
                        round(if(SUM(buy_amount_num) > 0, SUM(buy_amount) / SUM(buy_amount_num), 0), 2) amount_arpu,
                        round(if(SUM(buy_reg_num) > 0, SUM(cost) / SUM(buy_reg_num), 0), 2) reg_cost,
                        round(if(SUM(buy_first_new_user_amount_num) > 0, SUM(cost) / SUM(buy_first_new_user_amount_num), 0), 2) first_new_user_recharge_cost,
                        round(if(SUM(buy_new_user_total_amount_num) > 0, SUM(cost) / SUM(buy_new_user_total_amount_num), 0), 2) total_recharge_cost,
                        round(if(SUM(cost) > 0, SUM(buy_new_user_total_amount) / SUM(cost), 0), 4) total_roi,
                        IFNULL(SUM(buy_hundred_user_num), 0) hundred_user_num,
                        round(IF(SUM(buy_hundred_user_num) > 0, SUM(cost) / SUM(buy_hundred_user_num), 0), 2) hundred_user_num_cost,
                        IFNULL(SUM(buy_first_role_num), 0) first_role_num,
                        IFNULL(SUM(buy_role_num), 0) role_num,
                        IFNULL(SUM(buy_new_user_total_role_num), 0) new_user_total_role_num,
                        round(IF(SUM(buy_first_role_num) > 0, SUM(cost) / SUM(buy_first_role_num), 0), 2) first_role_num_cost,
                        round(IF(SUM(buy_role_num) > 0, SUM(cost) / SUM(buy_role_num), 0), 2) role_num_cost,
                        round(IF(SUM(buy_new_user_total_role_num) >0, SUM(cost) / SUM(buy_new_user_total_role_num), 0), 2) new_user_total_role_num_cost,
                        round(IF(SUM(buy_reg_num) >0, SUM(buy_first_role_num) / SUM(buy_reg_num), 0), 4) first_role_num_rate,
                        round(IF(SUM(buy_reg_num) >0, SUM(buy_role_num) / SUM(buy_reg_num), 0), 4) role_num_rate,
                        round(IF(SUM(buy_reg_num) >0, SUM(buy_new_user_total_role_num) / SUM(buy_reg_num), 0), 4) new_user_total_role_num_rate
                    FROM 
                    """ + tableName;
        } else if ("nature".equals(type)) {
            return """
                    SELECT
                        IFNULL(SUM(cost), 0) cost,
                        IFNULL(SUM(nature_reg_num), 0) reg_num,
                        IFNULL(SUM(nature_first_new_user_amount_count), 0) first_new_user_amount_count,
                        IFNULL(SUM(nature_first_new_user_amount_num), 0) first_new_user_amount_num,
                        IFNULL(SUM(nature_first_new_user_amount), 0) first_new_user_amount,
                        IFNULL(SUM(nature_old_user_count), 0) old_user_count,
                        IFNULL(SUM(nature_old_user_num), 0) old_user_num,
                        IFNULL(SUM(nature_old_user_amount), 0) old_user_amount,
                        IFNULL(SUM(nature_amount_count), 0) amount_count,
                        IFNULL(SUM(nature_amount_num), 0) amount_num,
                        IFNULL(SUM(nature_amount), 0) amount,
                        IFNULL(SUM(nature_new_user_total_amount_count), 0) new_user_total_amount_count,
                        IFNULL(SUM(nature_new_user_total_amount_num), 0) new_user_total_amount_num,
                        IFNULL(SUM(nature_new_user_total_amount), 0) new_user_total_amount,
                        round(if(SUM(cost) > 0 , SUM(nature_first_new_user_amount) / SUM(cost) ,0), 4) first_roi,
                        round(if(SUM(nature_reg_num) > 0 , SUM(nature_first_new_user_amount_num) / SUM(nature_reg_num) ,0), 4) first_amount_rate,
                        round(if(SUM(nature_reg_num) > 0, SUM(nature_new_user_total_amount_num) / SUM(nature_reg_num), 0) ,4) today_amount_rate,
                        round(if(SUM(nature_amount_num) > 0 , SUM(nature_first_new_user_amount_num) / SUM(nature_amount_num) ,0), 4) new_user_rate,
                        round(if(SUM(nature_first_new_user_amount_count) > 0, SUM(nature_first_new_user_amount) / SUM(nature_first_new_user_amount_count), 0), 2) first_avg_amount,
                        round(if(SUM(nature_new_user_total_amount_count) > 0, SUM(nature_new_user_total_amount) / SUM(nature_new_user_total_amount_count), 0), 2) today_avg_amount,
                        round(if(SUM(nature_amount_count) > 0, SUM(nature_amount) / SUM(nature_amount_count), 0), 2) avg_amount,
                        round(if(SUM(nature_new_user_total_amount_num) > 0, SUM(nature_reg_order_user_again) / SUM(nature_new_user_total_amount_num), 0), 4) user_again_rate,
                        round(if(SUM(nature_reg_num) > 0, SUM(nature_new_user_total_amount) / SUM(nature_reg_num), 0), 2) reg_user_arpu,
                        round(if(SUM(nature_first_new_user_amount_num) > 0 , SUM(nature_first_new_user_amount) / SUM(nature_first_new_user_amount_num), 0), 2) first_amount_arpu,
                        round(if(SUM(nature_new_user_total_amount_num) > 0 , SUM(nature_new_user_total_amount) / SUM(nature_new_user_total_amount_num), 0), 2) today_amount_arpu,
                        round(if(SUM(nature_amount_num) > 0, SUM(nature_amount) / SUM(nature_amount_num), 0), 2) amount_arpu,
                        round(if(SUM(nature_reg_num) > 0, SUM(cost) / SUM(nature_reg_num), 0), 2) reg_cost,
                        round(if(SUM(nature_first_new_user_amount_num) > 0, SUM(cost) / SUM(nature_first_new_user_amount_num), 0), 2) first_new_user_recharge_cost,
                        round(if(SUM(nature_new_user_total_amount_num) > 0, SUM(cost) / SUM(nature_new_user_total_amount_num), 0), 2) total_recharge_cost,
                        round(if(SUM(cost) > 0, SUM(nature_new_user_total_amount) / SUM(cost), 0), 4) total_roi,
                        IFNULL(SUM(nature_hundred_user_num), 0) hundred_user_num,
                        round(IF(SUM(nature_hundred_user_num) > 0, SUM(cost) / SUM(nature_hundred_user_num), 0), 2) hundred_user_num_cost,
                        IFNULL(SUM(nature_first_role_num), 0) first_role_num,
                        IFNULL(SUM(nature_role_num), 0) role_num,
                        IFNULL(SUM(nature_new_user_total_role_num), 0) new_user_total_role_num,
                        round(IF(SUM(nature_first_role_num) > 0, SUM(cost) / SUM(nature_first_role_num), 0), 2) first_role_num_cost,
                        round(IF(SUM(nature_role_num) > 0, SUM(cost) / SUM(nature_role_num), 0), 2) role_num_cost,
                        round(IF(SUM(nature_new_user_total_role_num) >0, SUM(cost) / SUM(nature_new_user_total_role_num), 0), 2) new_user_total_role_num_cost,
                        round(IF(SUM(nature_reg_num) >0, SUM(nature_first_role_num) / SUM(nature_reg_num), 0), 4) first_role_num_rate,
                        round(IF(SUM(nature_reg_num) >0, SUM(nature_role_num) / SUM(nature_reg_num), 0), 4) role_num_rate,
                        round(IF(SUM(nature_reg_num) >0, SUM(nature_new_user_total_role_num) / SUM(nature_reg_num), 0), 4) new_user_total_role_num_rate
                    FROM 
                    """ + tableName;
        }
        //total总量数据
        return """
                SELECT
                    IFNULL(SUM(cost), 0) cost,
                    IFNULL(SUM(reg_num), 0) reg_num,
                    IFNULL(SUM(first_new_user_amount_count), 0) first_new_user_amount_count,
                    IFNULL(SUM(first_new_user_amount_num), 0) first_new_user_amount_num,
                    IFNULL(SUM(first_new_user_amount), 0) first_new_user_amount,
                    IFNULL(SUM(old_user_count), 0) old_user_count,
                    IFNULL(SUM(old_user_num), 0) old_user_num,
                    IFNULL(SUM(old_user_amount), 0) old_user_amount,
                    IFNULL(SUM(amount_count), 0) amount_count,
                    IFNULL(SUM(amount_num), 0) amount_num,
                    IFNULL(SUM(amount), 0) amount,
                    IFNULL(SUM(new_user_total_amount_count), 0) new_user_total_amount_count,
                    IFNULL(SUM(new_user_total_amount_num), 0) new_user_total_amount_num,
                    IFNULL(SUM(new_user_total_amount), 0) new_user_total_amount,
                    round(if(SUM(cost) > 0 , SUM(first_new_user_amount) / SUM(cost) ,0), 4) first_roi,
                    round(if(SUM(reg_num) > 0 , SUM(first_new_user_amount_num) / SUM(reg_num) ,0), 4) first_amount_rate,
                    round(if(SUM(reg_num) > 0, SUM(new_user_total_amount_num) / SUM(reg_num), 0) ,4) today_amount_rate,
                    round(if(SUM(amount_num) > 0 , SUM(first_new_user_amount_num) / SUM(amount_num) ,0), 4) new_user_rate,
                    round(if(SUM(first_new_user_amount_count) > 0, SUM(first_new_user_amount) / SUM(first_new_user_amount_count), 0), 2) first_avg_amount,
                    round(if(SUM(new_user_total_amount_count) > 0, SUM(new_user_total_amount) / SUM(new_user_total_amount_count), 0), 2) today_avg_amount,
                    round(if(SUM(amount_count) > 0, SUM(amount) / SUM(amount_count), 0), 2) avg_amount,
                    round(if(SUM(new_user_total_amount_num) > 0, SUM(reg_order_user_again) / SUM(new_user_total_amount_num), 0), 4) user_again_rate,
                    round(if(SUM(reg_num) > 0, SUM(new_user_total_amount) / SUM(reg_num), 0), 2) reg_user_arpu,
                    round(if(SUM(first_new_user_amount_num) > 0 , SUM(first_new_user_amount) / SUM(first_new_user_amount_num), 0), 2) first_amount_arpu,
                    round(if(SUM(new_user_total_amount_num) > 0 , SUM(new_user_total_amount) / SUM(new_user_total_amount_num), 0), 2) today_amount_arpu,
                    round(if(SUM(amount_num) > 0, SUM(amount) / SUM(amount_num), 0), 2) amount_arpu,
                    round(if(SUM(reg_num) > 0, SUM(cost) / SUM(reg_num), 0), 2) reg_cost,
                    round(if(SUM(first_new_user_amount_num) > 0, SUM(cost) / SUM(first_new_user_amount_num), 0), 2) first_new_user_recharge_cost,
                    round(if(SUM(new_user_total_amount_num) > 0, SUM(cost) / SUM(new_user_total_amount_num), 0), 2) total_recharge_cost,
                    round(if(SUM(cost) > 0, SUM(new_user_total_amount) / SUM(cost), 0), 4) total_roi,
                    IFNULL(SUM(hundred_user_num), 0) hundred_user_num,
                    round(IF(SUM(hundred_user_num) > 0, SUM(cost) / SUM(hundred_user_num), 0), 2) hundred_user_num_cost,
                    IFNULL(SUM(first_role_num), 0) first_role_num,
                    IFNULL(SUM(role_num), 0) role_num,
                    IFNULL(SUM(new_user_total_role_num), 0) new_user_total_role_num,
                    round(IF(SUM(first_role_num) > 0, SUM(cost) / SUM(first_role_num), 0), 2) first_role_num_cost,
                    round(IF(SUM(role_num) > 0, SUM(cost) / SUM(role_num), 0), 2) role_num_cost,
                    round(IF(SUM(new_user_total_role_num) >0, SUM(cost) / SUM(new_user_total_role_num), 0), 2) new_user_total_role_num_cost,
                    round(IF(SUM(reg_num) >0, SUM(first_role_num) / SUM(reg_num), 0), 4) first_role_num_rate,
                    round(IF(SUM(reg_num) >0, SUM(role_num) / SUM(reg_num), 0), 4) role_num_rate,
                    round(IF(SUM(reg_num) >0, SUM(new_user_total_role_num) / SUM(reg_num), 0), 4) new_user_total_role_num_rate
                FROM 
                """ + tableName;
    }

    private String getTotalMediaGameData() {

        return """
                  SELECT
                                     IFNULL(SUM(cost), 0) cost,
                                     IFNULL(SUM(register_num), 0) reg_num,
                                     IFNULL(SUM(first_new_user_amount_count), 0) first_new_user_amount_count,
                                     IFNULL(SUM(first_new_user_amount_num), 0) first_new_user_amount_num,
                                     IFNULL(SUM(first_new_user_amount), 0) first_new_user_amount,
                                     IFNULL(SUM(old_amount_count), 0) old_user_count,
                                     IFNULL(SUM(old_amount_num), 0) old_user_num,
                                     IFNULL(SUM(old_amount), 0) old_user_amount,
                                     IFNULL(SUM(amount_count), 0) amount_count,
                                     IFNULL(SUM(amount_num), 0) amount_num,
                                     IFNULL(SUM(amount), 0) amount,
                                     IFNULL(SUM(new_user_total_amount_count), 0) new_user_total_amount_count,
                                     IFNULL(SUM(new_user_total_amount_num), 0) new_user_total_amount_num,
                                     IFNULL(SUM(new_user_total_amount), 0) new_user_total_amount,
                                     round(if(SUM(cost) > 0 , SUM(first_new_user_amount) / SUM(cost) ,0), 4) first_roi,
                                     round(if(SUM(register_num) > 0 , SUM(first_new_user_amount_num) / SUM(register_num) ,0), 4) first_amount_rate,
                                     round(if(SUM(register_num) > 0, SUM(new_user_total_amount_num) / SUM(register_num), 0) ,4) today_amount_rate,
                                     round(if(SUM(amount_num) > 0 , SUM(first_new_user_amount_num) / SUM(amount_num) ,0), 4) new_user_rate,
                                     round(if(SUM(first_new_user_amount_count) > 0, SUM(first_new_user_amount) / SUM(first_new_user_amount_count), 0), 2) first_avg_amount,
                                     round(if(SUM(new_user_total_amount_count) > 0, SUM(new_user_total_amount) / SUM(new_user_total_amount_count), 0), 2) today_avg_amount,
                                     round(if(SUM(amount_count) > 0, SUM(amount) / SUM(amount_count), 0), 2) avg_amount,
                                     round(if(SUM(new_user_total_amount_num) > 0, SUM(reg_order_user_again) / SUM(new_user_total_amount_num), 0), 4) user_again_rate,
                                     round(if(SUM(register_num) > 0, SUM(new_user_total_amount) / SUM(register_num), 0), 2) reg_user_arpu,
                                     round(if(SUM(first_new_user_amount_num) > 0 , SUM(first_new_user_amount) / SUM(first_new_user_amount_num), 0), 2) first_amount_arpu,
                                     round(if(SUM(new_user_total_amount_num) > 0 , SUM(new_user_total_amount) / SUM(new_user_total_amount_num), 0), 2) today_amount_arpu,
                                     round(if(SUM(amount_num) > 0, SUM(amount) / SUM(amount_num), 0), 2) amount_arpu,
                                     round(if(SUM(register_num) > 0, SUM(cost) / SUM(register_num), 0), 2) reg_cost,
                                     round(if(SUM(first_new_user_amount_num) > 0, SUM(cost) / SUM(first_new_user_amount_num), 0), 2) first_new_user_recharge_cost,
                                     round(if(SUM(new_user_total_amount_num) > 0, SUM(cost) / SUM(new_user_total_amount_num), 0), 2) total_recharge_cost,
                                     round(if(SUM(cost) > 0, SUM(new_user_total_amount) / SUM(cost), 0), 4) total_roi,
                                     IFNULL(SUM(hundred_user_num), 0) hundred_user_num,
                                     round(IF(SUM(hundred_user_num) > 0, SUM(cost) / SUM(hundred_user_num), 0), 2) hundred_user_num_cost,
                                     IFNULL(SUM(first_role_num), 0) first_role_num,
                                     IFNULL(SUM(role_num), 0) role_num,
                                     IFNULL(SUM(new_user_total_role_num), 0) new_user_total_role_num,
                                     round(IF(SUM(first_role_num) > 0, SUM(cost) / SUM(first_role_num), 0), 2) first_role_num_cost,
                                     round(IF(SUM(role_num) > 0, SUM(cost) / SUM(role_num), 0), 2) role_num_cost,
                                     round(IF(SUM(new_user_total_role_num) >0, SUM(cost) / SUM(new_user_total_role_num), 0), 2) new_user_total_role_num_cost,
                                     round(IF(SUM(register_num) >0, SUM(first_role_num) / SUM(register_num), 0), 4) first_role_num_rate,
                                     round(IF(SUM(register_num) >0, SUM(role_num) / SUM(register_num), 0), 4) role_num_rate,
                                     round(IF(SUM(register_num) >0, SUM(new_user_total_role_num) / SUM(register_num), 0), 4) new_user_total_role_num_rate
                                 FROM game_ads.ads_game_type_day
                """;

    }

}
