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

import com.github.sd4324530.jtuple.Tuple2;
import com.google.common.base.CaseFormat;
import com.zanxiang.game.data.serve.component.DataPowerComponent;
import com.zanxiang.game.data.serve.pojo.dto.PromotionDayDTO;
import com.zanxiang.game.data.serve.pojo.dto.PromotionDayTotalDTO;
import com.zanxiang.game.data.serve.pojo.dto.TencentPromotionDayDTO;
import com.zanxiang.game.data.serve.pojo.dto.TencentPromotionDayTotalDTO;
import com.zanxiang.game.data.serve.pojo.enums.OrderByEnum;
import com.zanxiang.game.data.serve.pojo.vo.*;
import com.zanxiang.game.data.serve.service.IAdsPromotionDayService;
import com.zanxiang.game.data.serve.utils.Page;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.StringUtils;
import org.nutz.dao.Cnd;
import org.nutz.dao.Dao;
import org.nutz.dao.Sqls;
import org.nutz.dao.pager.Pager;
import org.nutz.dao.sql.Criteria;
import org.nutz.dao.sql.Sql;
import org.nutz.dao.util.Daos;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.BeanWrapper;
import org.springframework.beans.BeanWrapperImpl;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.beans.PropertyDescriptor;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @author tianhua
 * @time 2023/7/27
 * @Description
 **/
@Service
@Slf4j
public class PromotionDayServiceImpl implements IAdsPromotionDayService {

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

    /**
     * 广告监控每日数据列表
     *
     * @param dto
     * @return
     */
    public Page<PromotionDayVO> getPromotionDayListData(PromotionDayDTO dto) {
        Tuple2<List<Long>, List<Long>> poerInfo = dataPowerComponent.getPowerInfo();
        List<Long> userIds = dto.getPitcherId() == null ? poerInfo.first : Collections.singletonList(dto.getPitcherId());
        List<Long> gameIds = dto.getGameId() == null ? poerInfo.second : Collections.singletonList(dto.getGameId());

        //如果没有排序条件给默认值
        if (StringUtils.isBlank(dto.getSortFiled())) {
            dto.setSortFiled("dt");
        }
        if (StringUtils.isBlank(dto.getSortType())) {
            dto.setSortType(OrderByEnum.DESC.getOrderType());
        }
        Criteria cri = myCri(userIds, gameIds, dto, false);

        //计算总记录条数
        Sql sqlCount = Sqls.create("select count(*) from ads_promotion_day" + cri);
        sqlCount.setCallback(Sqls.callback.integer());
        //执行sql
        dao.execute(sqlCount);

        //拼接排序条件
        cri.getOrderBy().orderBy(CaseFormat.LOWER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, dto.getSortFiled()), dto.getSortType());
        //创建sql语句
        Sql sql = Sqls.create(getPromotionDayListSql() + cri);
        //添加自定义回传对象
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(PromotionDayVO.class));
        //设置pager对象
        Pager pager = dao.createPager(dto.getPageNum(), dto.getPageSize());
        sql.setPager(pager);
        //执行sql
        dao.execute(sql);
        //获取到结果list
        List<PromotionDayVO> list = sql.getList(PromotionDayVO.class);
        //分页对象设置总记录条数
        pager.setRecordCount(sqlCount.getInt());

        //处理List中的每个vo对象缺少的数据
        list.stream().map(vo -> {

            //计算d2(次日)数据
            vo.setD2Trend(PromotionRechargeTrendVO.builder()
                    .roi(vo.getTodayCost().compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                            vo.getD2().divide(vo.getTodayCost(), 4, RoundingMode.HALF_UP))
                    .rechargeMoney(vo.getD2())
                    .multiples(vo.getD1().compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                            vo.getD2().divide(vo.getD1(), 2, RoundingMode.HALF_UP))
                    .build());
            //计算d3数据
            vo.setD3Trend(PromotionRechargeTrendVO.builder()
                    .roi(vo.getTodayCost().compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                            vo.getD3().divide(vo.getTodayCost(), 4, RoundingMode.HALF_UP))
                    .rechargeMoney(vo.getD3())
                    .multiples(vo.getD1().compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                            vo.getD3().divide(vo.getD1(), 2, RoundingMode.HALF_UP))
                    .build());
            //计算d7数据
            vo.setD7Trend(PromotionRechargeTrendVO.builder()
                    .roi(vo.getTodayCost().compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                            vo.getD7().divide(vo.getTodayCost(), 4, RoundingMode.HALF_UP))
                    .rechargeMoney(vo.getD7())
                    .multiples(vo.getD1().compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                            vo.getD7().divide(vo.getD1(), 2, RoundingMode.HALF_UP))
                    .build());
            //计算d15数据
            vo.setD15Trend(PromotionRechargeTrendVO.builder()
                    .roi(vo.getTodayCost().compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                            vo.getD15().divide(vo.getTodayCost(), 4, RoundingMode.HALF_UP))
                    .rechargeMoney(vo.getD15())
                    .multiples(vo.getD1().compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                            vo.getD15().divide(vo.getD1(), 2, RoundingMode.HALF_UP))
                    .build());

            //返回最终数据
            return vo;
        }).collect(Collectors.toList());

        //返回查询得结果
        return new Page<>(list, pager);
    }

    /**
     * 广告监控数据
     *
     * @param dto
     * @return
     */
    @Override
    public Page<PromotionDayVO> getPromotionDayData(PromotionDayDTO dto) {
        Tuple2<List<Long>, List<Long>> poerInfo = dataPowerComponent.getPowerInfo();
        List<Long> userIds = dto.getPitcherId() == null ? poerInfo.first : Collections.singletonList(dto.getPitcherId());
        List<Long> gameIds = dto.getGameId() == null ? poerInfo.second : Collections.singletonList(dto.getGameId());
//        List<Long> userIds = dto.getPitcherId() == null ? null : Collections.singletonList(dto.getPitcherId());
//        List<Long> gameIds = dto.getGameId() == null ? null : Collections.singletonList(dto.getGameId());

        //如果没有排序条件给默认值
        if (StringUtils.isBlank(dto.getSortFiled())) {
            dto.setSortFiled("today_cost");
        }
        if (StringUtils.isBlank(dto.getSortType())) {
            dto.setSortType(OrderByEnum.DESC.getOrderType());
        }
        Criteria criA = myCri(userIds, gameIds, dto, true);
        Criteria criB = myCri(userIds, gameIds, dto, false);
        Criteria criOrderBy = Cnd.cri();
        //拼接排序条件
        criOrderBy.getOrderBy().orderBy(CaseFormat.LOWER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, dto.getSortFiled()), dto.getSortType());
        //创建sql语句
        Sql sql = Sqls.create(getPromotionDayDataSql(criA,criB) + criOrderBy);
        //添加自定义回传对象
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(PromotionDayVO.class));
        //设置pager对象
        Pager pager = dao.createPager(dto.getPageNum(), dto.getPageSize());
        sql.setPager(pager);
        //执行sql
        dao.execute(sql);
        //获取到结果list
        List<PromotionDayVO> list = sql.getList(PromotionDayVO.class);
        //分页对象设置总条数
        Sql sqlCount = Sqls.queryEntity("select count(*) from ads_promotion_day" + criA);
        pager.setRecordCount((int) Daos.queryCount(dao, sqlCount));

        //处理List中的每个vo对象缺少的数据
        list.stream().map(vo -> {

            //计算d2(次日)数据
            vo.setD2Trend(PromotionRechargeTrendVO.builder()
                    .roi(vo.getTodayCost().compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                            vo.getD2().divide(vo.getTodayCost(), 4, RoundingMode.HALF_UP))
                    .rechargeMoney(vo.getD2())
                    .multiples(vo.getD1().compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                            vo.getD2().divide(vo.getD1(), 2, RoundingMode.HALF_UP))
                    .build());
            //计算d3数据
            vo.setD3Trend(PromotionRechargeTrendVO.builder()
                    .roi(vo.getTodayCost().compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                            vo.getD3().divide(vo.getTodayCost(), 4, RoundingMode.HALF_UP))
                    .rechargeMoney(vo.getD3())
                    .multiples(vo.getD1().compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                            vo.getD3().divide(vo.getD1(), 2, RoundingMode.HALF_UP))
                    .build());
            //计算d7数据
            vo.setD7Trend(PromotionRechargeTrendVO.builder()
                    .roi(vo.getTodayCost().compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                            vo.getD7().divide(vo.getTodayCost(), 4, RoundingMode.HALF_UP))
                    .rechargeMoney(vo.getD7())
                    .multiples(vo.getD1().compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                            vo.getD7().divide(vo.getD1(), 2, RoundingMode.HALF_UP))
                    .build());
            //计算d15数据
            vo.setD15Trend(PromotionRechargeTrendVO.builder()
                    .roi(vo.getTodayCost().compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                            vo.getD15().divide(vo.getTodayCost(), 4, RoundingMode.HALF_UP))
                    .rechargeMoney(vo.getD15())
                    .multiples(vo.getD1().compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                            vo.getD15().divide(vo.getD1(), 2, RoundingMode.HALF_UP))
                    .build());

            //返回最终数据
            return vo;
        }).collect(Collectors.toList());

        //返回查询得结果
        return new Page<>(list, pager);
    }

    /**
     * 广告监控数据总计一栏
     *
     * @param dto
     * @return
     */
    @Override
    public PromotionDayTotalVO getPromotionDayTotalData(PromotionDayTotalDTO dto) {
        Tuple2<List<Long>, List<Long>> poerInfo = dataPowerComponent.getPowerInfo();
        List<Long> userIds = dto.getPitcherId() == null ? poerInfo.first : Collections.singletonList(dto.getPitcherId());
        List<Long> gameIds = dto.getGameId() == null ? poerInfo.second : Collections.singletonList(dto.getGameId());
//        List<Long> userIds = dto.getPitcherId() == null ? null : Collections.singletonList(dto.getPitcherId());
//        List<Long> gameIds = dto.getGameId() == null ? null : Collections.singletonList(dto.getGameId());

        //创建查询条件
        Criteria cri = Cnd.cri();
        if (dto.getAccountId() != null) {
            //拼接推广账号Id查询条件
            cri.where().andEquals("account_id", dto.getAccountId());
        }
        if (StringUtils.isNotBlank(dto.getAccountName())) {
            //拼接推广账号名称查询条件
            cri.where().andLike("account_name", dto.getAccountName());
        }
        if (StringUtils.isNotBlank(dto.getAccountType())) {
            //推广账号类型
            cri.where().andEquals("account_type", dto.getAccountType());
        }
        if (userIds != null) {
            cri.where().andInList("pitcher_id", userIds);
        }
        if (ArrayUtils.isNotEmpty(dto.getAgentId())) {
            cri.where().andInList("agent_id", Arrays.asList(dto.getAgentId()));
        }
        if (StringUtils.isNotBlank(dto.getCpName())) {
            cri.where().andEquals("cp_name", dto.getCpName());
        }
        if (gameIds != null) {
            cri.where().andInList("game_id", gameIds);
        }
        if (dto.getClassify() != null) {
            cri.where().andEquals("classify", dto.getClassify());
        }
        if (StringUtils.isNotBlank(dto.getPromotionName())) {
            cri.where().andLike("promotion_name", dto.getPromotionName());
        }
        if (dto.getPromotionId() != null) {
            cri.where().andEquals("promotion_id", dto.getPromotionId());
        }
        if (StringUtils.isNotBlank(dto.getStatus())) {
            cri.where().andEquals("status", dto.getStatus());
        }
        if (StringUtils.isNotBlank(dto.getProjectName())) {
            cri.where().andLike("project_name", dto.getProjectName());
        }
        if (dto.getProjectId() != null) {
            cri.where().andEquals("project_id", dto.getProjectId());
        }
        if (dto.getCostBeginDate() != null && dto.getCostEndDate() != null) {
            cri.where().andBetween("dt", dto.getCostBeginDate(), dto.getCostEndDate());
        }
        if (StringUtils.isNotBlank(dto.getSourceSystem())) {
            cri.where().andEquals("source_system", dto.getSourceSystem());
        }
        //首日充值金额筛选
        if (dto.getFirstRechargeAmountMin() != null) {
            cri.where().andGTE("first_new_user_amount", dto.getFirstRechargeAmountMin());
        }
        if (dto.getFirstRechargeAmountMax() != null) {
            cri.where().andLTE("first_new_user_amount", dto.getFirstRechargeAmountMax());
        }
        //创建sql
        Sql promotionDayTotalSql = Sqls.create(promotionDayTotalSql() + cri);
        //设置回传对象
        promotionDayTotalSql.setCallback(Sqls.callback.entity());
        promotionDayTotalSql.setEntity(dao.getEntity(PromotionDayTotalVO.class));
        //执行sql
        dao.execute(promotionDayTotalSql);
        //得到结果
        PromotionDayTotalVO vo = promotionDayTotalSql.getObject(PromotionDayTotalVO.class);

        //计算总数据要根据promotionId分组
        Criteria totalCri = cri;
        totalCri.getGroupBy().groupBy("promotion_id");
        //构建sql
        Sql tempSql = Sqls.create(promotionDayTotalTotalDataTempSql() + totalCri);
        Sql totalSumSql = Sqls.create(promotionDayTotalTotalDataSumSql() + "(" + tempSql + ") a");
        //设置回传对象
        totalSumSql.setCallback(Sqls.callback.entity());
        totalSumSql.setEntity(dao.getEntity(PromotionDayTotalVO.class));
        //执行sql
        dao.execute(totalSumSql);
        PromotionDayTotalVO tempVO = totalSumSql.getObject(PromotionDayTotalVO.class);
        //将取到的值赋值vo
        copyNullProperties(tempVO, vo);

        //如果没有tempVO，返回默认值0
        if (tempVO.getPromotionTotalCost() == null) {
            //计算总数据
            //总注册成本
            vo.setRegTotalCost(BigDecimal.ZERO);
            //总创角成本
            vo.setRoleTotalCost(BigDecimal.ZERO);
            //总创角率
            vo.setRoleTotalRate(BigDecimal.ZERO);
            //广告总ROI
            vo.setPromotionTotalRoi(BigDecimal.ZERO);
            //总付费成本
            vo.setTotalRechargeCost(BigDecimal.ZERO);
            //计算d2，d3，d7，d15数据
            //计算d2(次日)数据
            vo.setD2Trend(PromotionRechargeTrendVO.builder()
                    .roi(BigDecimal.ZERO)
                    .rechargeMoney(BigDecimal.ZERO)
                    .multiples(BigDecimal.ZERO)
                    .build());
            //计算d3数据
            vo.setD3Trend(PromotionRechargeTrendVO.builder()
                    .roi(BigDecimal.ZERO)
                    .rechargeMoney(BigDecimal.ZERO)
                    .multiples(BigDecimal.ZERO)
                    .build());
            //计算d7数据
            vo.setD7Trend(PromotionRechargeTrendVO.builder()
                    .roi(BigDecimal.ZERO)
                    .rechargeMoney(BigDecimal.ZERO)
                    .multiples(BigDecimal.ZERO)
                    .build());
            //计算d15数据
            vo.setD15Trend(PromotionRechargeTrendVO.builder()
                    .roi(BigDecimal.ZERO)
                    .rechargeMoney(BigDecimal.ZERO)
                    .multiples(BigDecimal.ZERO)
                    .build());

            return vo;
        }

        //计算总数据
        //总注册成本
        vo.setRegTotalCost(vo.getRegTotalNum() == 0L ? BigDecimal.ZERO :
                vo.getPromotionTotalCost().divide(BigDecimal.valueOf(vo.getRegTotalNum()), 2, RoundingMode.HALF_UP));
        //总创角成本
        vo.setRoleTotalCost(vo.getRoleTotalNum() == 0L ? BigDecimal.ZERO :
                vo.getPromotionTotalCost().divide(BigDecimal.valueOf(vo.getRoleTotalNum()), 2, RoundingMode.HALF_UP));
        //总创角率
        vo.setRoleTotalRate(vo.getRegTotalNum() == 0L ? BigDecimal.ZERO :
                BigDecimal.valueOf(vo.getRoleTotalNum()).divide(BigDecimal.valueOf(vo.getRegTotalNum()), 4, RoundingMode.HALF_UP));
        //广告总ROI
        vo.setPromotionTotalRoi(vo.getPromotionTotalCost().compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                vo.getTotalAmount().divide(vo.getPromotionTotalCost(), 4, RoundingMode.HALF_UP));
        //总付费成本
        vo.setTotalRechargeCost(vo.getTotalAmountNum() == 0L ? BigDecimal.ZERO :
                vo.getPromotionTotalCost().divide(BigDecimal.valueOf(vo.getTotalAmountNum()), 2, RoundingMode.HALF_UP));
        //总充值次数成本
        vo.setTotalAmountCountCost(vo.getTotalAmountCount() == 0L ? BigDecimal.ZERO :
                vo.getPromotionTotalCost().divide(BigDecimal.valueOf(vo.getTotalAmountCount()), 2, RoundingMode.HALF_UP));

        //计算d2，d3，d7，d15数据
        //计算d2(次日)数据
        vo.setD2Trend(PromotionRechargeTrendVO.builder()
                .roi(vo.getTodayCost().compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                        vo.getD2().divide(vo.getTodayCost(), 4, RoundingMode.HALF_UP))
                .rechargeMoney(vo.getD2())
                .multiples(vo.getD1().compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                        vo.getD2().divide(vo.getD1(), 2, RoundingMode.HALF_UP))
                .build());
        //计算d3数据
        vo.setD3Trend(PromotionRechargeTrendVO.builder()
                .roi(vo.getTodayCost().compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                        vo.getD3().divide(vo.getTodayCost(), 4, RoundingMode.HALF_UP))
                .rechargeMoney(vo.getD3())
                .multiples(vo.getD1().compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                        vo.getD3().divide(vo.getD1(), 2, RoundingMode.HALF_UP))
                .build());
        //计算d7数据
        vo.setD7Trend(PromotionRechargeTrendVO.builder()
                .roi(vo.getTodayCost().compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                        vo.getD7().divide(vo.getTodayCost(), 4, RoundingMode.HALF_UP))
                .rechargeMoney(vo.getD7())
                .multiples(vo.getD1().compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                        vo.getD7().divide(vo.getD1(), 2, RoundingMode.HALF_UP))
                .build());
        //计算d15数据
        vo.setD15Trend(PromotionRechargeTrendVO.builder()
                .roi(vo.getTodayCost().compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                        vo.getD15().divide(vo.getTodayCost(), 4, RoundingMode.HALF_UP))
                .rechargeMoney(vo.getD15())
                .multiples(vo.getD1().compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                        vo.getD15().divide(vo.getD1(), 2, RoundingMode.HALF_UP))
                .build());

        return vo;
    }

    /**
     * 广告监控每日数据列表(腾讯广告)
     *
     * @param dto
     * @return
     */
    public Page<TencentPromotionDayVO> getTencentPromotionDayListData(TencentPromotionDayDTO dto) {
        Tuple2<List<Long>, List<Long>> poerInfo = dataPowerComponent.getPowerInfo();
        List<Long> userIds = dto.getPitcherId() == null ? poerInfo.first : Collections.singletonList(dto.getPitcherId());
        List<Long> gameIds = dto.getGameId() == null ? poerInfo.second : Collections.singletonList(dto.getGameId());
        //如果没有排序条件给默认值
        if (StringUtils.isBlank(dto.getSortFiled())) {
            dto.setSortFiled("dt");
        }
        if (StringUtils.isBlank(dto.getSortType())) {
            dto.setSortType(OrderByEnum.DESC.getOrderType());
        }
        Criteria cri = myCriTencent(userIds, gameIds, dto, false);

        //计算总记录条数
        Sql sqlCount = Sqls.create("select count(*) from ads_adgroup_day" + cri);
        sqlCount.setCallback(Sqls.callback.integer());
        //执行sql
        dao.execute(sqlCount);

        //拼接排序条件
        cri.getOrderBy().orderBy(CaseFormat.LOWER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, dto.getSortFiled()), dto.getSortType());
        //创建sql语句
        Sql sql = Sqls.create(getTencentPromotionDayListSql() + cri);
        //添加自定义回传对象
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(TencentPromotionDayVO.class));
        //设置pager对象
        Pager pager = dao.createPager(dto.getPageNum(), dto.getPageSize());
        sql.setPager(pager);
        //执行sql
        dao.execute(sql);
        //获取到结果list
        List<TencentPromotionDayVO> list = sql.getList(TencentPromotionDayVO.class);
        //分页对象设置总记录条数
        pager.setRecordCount(sqlCount.getInt());

        //处理List中的每个vo对象缺少的数据
        list.stream().map(vo -> {

            //计算d2(次日)数据
            vo.setD2Trend(PromotionRechargeTrendVO.builder()
                    .roi(vo.getTodayCost().compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                            vo.getD2().divide(vo.getTodayCost(), 4, RoundingMode.HALF_UP))
                    .rechargeMoney(vo.getD2())
                    .multiples(vo.getD1().compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                            vo.getD2().divide(vo.getD1(), 2, RoundingMode.HALF_UP))
                    .build());
            //计算d3数据
            vo.setD3Trend(PromotionRechargeTrendVO.builder()
                    .roi(vo.getTodayCost().compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                            vo.getD3().divide(vo.getTodayCost(), 4, RoundingMode.HALF_UP))
                    .rechargeMoney(vo.getD3())
                    .multiples(vo.getD1().compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                            vo.getD3().divide(vo.getD1(), 2, RoundingMode.HALF_UP))
                    .build());
            //计算d7数据
            vo.setD7Trend(PromotionRechargeTrendVO.builder()
                    .roi(vo.getTodayCost().compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                            vo.getD7().divide(vo.getTodayCost(), 4, RoundingMode.HALF_UP))
                    .rechargeMoney(vo.getD7())
                    .multiples(vo.getD1().compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                            vo.getD7().divide(vo.getD1(), 2, RoundingMode.HALF_UP))
                    .build());
            //计算d15数据
            vo.setD15Trend(PromotionRechargeTrendVO.builder()
                    .roi(vo.getTodayCost().compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                            vo.getD15().divide(vo.getTodayCost(), 4, RoundingMode.HALF_UP))
                    .rechargeMoney(vo.getD15())
                    .multiples(vo.getD1().compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                            vo.getD15().divide(vo.getD1(), 2, RoundingMode.HALF_UP))
                    .build());

            //返回最终数据
            return vo;
        }).collect(Collectors.toList());

        //返回查询得结果
        return new Page<>(list, pager);
    }

    /**
     * 广告监控数据(腾讯广告)
     *
     * @param dto
     * @return
     */
    @Override
    public Page<TencentPromotionDayVO> getTencentPromotionDayData(TencentPromotionDayDTO dto) {
        Tuple2<List<Long>, List<Long>> poerInfo = dataPowerComponent.getPowerInfo();
        List<Long> userIds = dto.getPitcherId() == null ? poerInfo.first : Collections.singletonList(dto.getPitcherId());
        List<Long> gameIds = dto.getGameId() == null ? poerInfo.second : Collections.singletonList(dto.getGameId());
        //如果没有排序条件给默认值
        if (StringUtils.isBlank(dto.getSortFiled())) {
            dto.setSortFiled("today_cost");
        }
        if (StringUtils.isBlank(dto.getSortType())) {
            dto.setSortType(OrderByEnum.DESC.getOrderType());
        }
        Criteria criA = myCriTencent(userIds, gameIds, dto, true);
        Criteria criB = myCriTencent(userIds, gameIds, dto, false);
        Criteria criOrderBy = Cnd.cri();
        //拼接排序条件
        criOrderBy.getOrderBy().orderBy(CaseFormat.LOWER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, dto.getSortFiled()), dto.getSortType());
        //创建sql语句
        Sql sql = Sqls.create(getTencentPromotionDayDataSql(criA,criB) + criOrderBy);
        //添加自定义回传对象
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(TencentPromotionDayVO.class));
        //设置pager对象
        Pager pager = dao.createPager(dto.getPageNum(), dto.getPageSize());
        sql.setPager(pager);
        //执行sql
        dao.execute(sql);
        //获取到结果list
        List<TencentPromotionDayVO> list = sql.getList(TencentPromotionDayVO.class);
        //分页对象设置总条数
        Sql sqlCount = Sqls.queryEntity("select count(*) from ads_adgroup_day" + criA);
        pager.setRecordCount((int) Daos.queryCount(dao, sqlCount));

        //处理List中的每个vo对象缺少的数据
        list.stream().map(vo -> {

            //计算d2(次日)数据
            vo.setD2Trend(PromotionRechargeTrendVO.builder()
                    .roi(vo.getTodayCost().compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                            vo.getD2().divide(vo.getTodayCost(), 4, RoundingMode.HALF_UP))
                    .rechargeMoney(vo.getD2())
                    .multiples(vo.getD1().compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                            vo.getD2().divide(vo.getD1(), 2, RoundingMode.HALF_UP))
                    .build());
            //计算d3数据
            vo.setD3Trend(PromotionRechargeTrendVO.builder()
                    .roi(vo.getTodayCost().compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                            vo.getD3().divide(vo.getTodayCost(), 4, RoundingMode.HALF_UP))
                    .rechargeMoney(vo.getD3())
                    .multiples(vo.getD1().compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                            vo.getD3().divide(vo.getD1(), 2, RoundingMode.HALF_UP))
                    .build());
            //计算d7数据
            vo.setD7Trend(PromotionRechargeTrendVO.builder()
                    .roi(vo.getTodayCost().compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                            vo.getD7().divide(vo.getTodayCost(), 4, RoundingMode.HALF_UP))
                    .rechargeMoney(vo.getD7())
                    .multiples(vo.getD1().compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                            vo.getD7().divide(vo.getD1(), 2, RoundingMode.HALF_UP))
                    .build());
            //计算d15数据
            vo.setD15Trend(PromotionRechargeTrendVO.builder()
                    .roi(vo.getTodayCost().compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                            vo.getD15().divide(vo.getTodayCost(), 4, RoundingMode.HALF_UP))
                    .rechargeMoney(vo.getD15())
                    .multiples(vo.getD1().compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                            vo.getD15().divide(vo.getD1(), 2, RoundingMode.HALF_UP))
                    .build());

            //返回最终数据
            return vo;
        }).collect(Collectors.toList());

        //返回查询得结果
        return new Page<>(list, pager);
    }

    /**
     * 广告监控数据总计一栏(腾讯广告)
     *
     * @param dto
     * @return
     */
    @Override
    public TencentPromotionDayTotalVO getTencentPromotionDayTotalData(TencentPromotionDayTotalDTO dto) {
        Tuple2<List<Long>, List<Long>> poerInfo = dataPowerComponent.getPowerInfo();
        List<Long> userIds = dto.getPitcherId() == null ? poerInfo.first : Collections.singletonList(dto.getPitcherId());
        List<Long> gameIds = dto.getGameId() == null ? poerInfo.second : Collections.singletonList(dto.getGameId());
        //创建查询条件
        Criteria cri = Cnd.cri();
        if (dto.getAccountId() != null) {
            //拼接推广账号Id查询条件
            cri.where().andEquals("account_id", dto.getAccountId());
        }
        if (StringUtils.isNotBlank(dto.getAccountName())) {
            //拼接推广账号名称查询条件
            cri.where().andLike("account_name", dto.getAccountName());
        }
        if (StringUtils.isNotBlank(dto.getAccountType())) {
            //推广账号类型
            cri.where().andEquals("account_type", dto.getAccountType());
        }
        if (userIds != null) {
            cri.where().andInList("pitcher_id", userIds);
        }
        if (ArrayUtils.isNotEmpty(dto.getAgentId())) {
            cri.where().andInList("agent_id", Arrays.asList(dto.getAgentId()));
        }
        if (StringUtils.isNotBlank(dto.getCpName())) {
            cri.where().andEquals("cp_name", dto.getCpName());
        }
        if (gameIds != null) {
            cri.where().andInList("game_id", gameIds);
        }
        if (dto.getClassify() != null) {
            cri.where().andEquals("classify", dto.getClassify());
        }
        if (StringUtils.isNotBlank(dto.getPromotionName())) {
            cri.where().andLike("promotion_name", dto.getPromotionName());
        }
        if (dto.getPromotionId() != null) {
            cri.where().andEquals("promotion_id", dto.getPromotionId());
        }
        if (StringUtils.isNotBlank(dto.getStatus())) {
            cri.where().andEquals("status", dto.getStatus());
        }
        if (StringUtils.isNotBlank(dto.getProjectName())) {
            cri.where().andLike("project_name", dto.getProjectName());
        }
        if (dto.getProjectId() != null) {
            cri.where().andEquals("project_id", dto.getProjectId());
        }
        if (dto.getCostBeginDate() != null && dto.getCostEndDate() != null) {
            cri.where().andBetween("dt", dto.getCostBeginDate(), dto.getCostEndDate());
        }
        if (StringUtils.isNotBlank(dto.getSourceSystem())) {
            cri.where().andEquals("source_system", dto.getSourceSystem());
        }
        //创建sql
        Sql promotionDayTotalSql = Sqls.create(tencentPromotionDayTotalSql() + cri);
        //设置回传对象
        promotionDayTotalSql.setCallback(Sqls.callback.entity());
        promotionDayTotalSql.setEntity(dao.getEntity(TencentPromotionDayTotalVO.class));
        //执行sql
        dao.execute(promotionDayTotalSql);
        //得到结果
        TencentPromotionDayTotalVO vo = promotionDayTotalSql.getObject(TencentPromotionDayTotalVO.class);

        //计算总数据要根据promotionId分组
        Criteria totalCri = cri;
        totalCri.getGroupBy().groupBy("promotion_id");
        //构建sql
        Sql tempSql = Sqls.create(tencentPromotionDayTotalTotalDataTempSql() + totalCri);
        Sql totalSumSql = Sqls.create(tencentPromotionDayTotalTotalDataSumSql() + "(" + tempSql + ") a");
        //设置回传对象
        totalSumSql.setCallback(Sqls.callback.entity());
        totalSumSql.setEntity(dao.getEntity(TencentPromotionDayTotalVO.class));
        //执行sql
        dao.execute(totalSumSql);
        TencentPromotionDayTotalVO tempVO = totalSumSql.getObject(TencentPromotionDayTotalVO.class);
        //将取到的值赋值vo
        copyNullProperties(tempVO, vo);

        //如果没有tempVO，返回默认值0
        if (tempVO.getPromotionTotalCost() == null) {
            //计算总数据
            //总注册成本
            vo.setRegTotalCost(BigDecimal.ZERO);
            //总创角成本
            vo.setRoleTotalCost(BigDecimal.ZERO);
            //总创角率
            vo.setRoleTotalRate(BigDecimal.ZERO);
            //广告总ROI
            vo.setPromotionTotalRoi(BigDecimal.ZERO);
            //总付费成本
            vo.setTotalRechargeCost(BigDecimal.ZERO);
            //计算d2，d3，d7，d15数据
            //计算d2(次日)数据
            vo.setD2Trend(PromotionRechargeTrendVO.builder()
                    .roi(BigDecimal.ZERO)
                    .rechargeMoney(BigDecimal.ZERO)
                    .multiples(BigDecimal.ZERO)
                    .build());
            //计算d3数据
            vo.setD3Trend(PromotionRechargeTrendVO.builder()
                    .roi(BigDecimal.ZERO)
                    .rechargeMoney(BigDecimal.ZERO)
                    .multiples(BigDecimal.ZERO)
                    .build());
            //计算d7数据
            vo.setD7Trend(PromotionRechargeTrendVO.builder()
                    .roi(BigDecimal.ZERO)
                    .rechargeMoney(BigDecimal.ZERO)
                    .multiples(BigDecimal.ZERO)
                    .build());
            //计算d15数据
            vo.setD15Trend(PromotionRechargeTrendVO.builder()
                    .roi(BigDecimal.ZERO)
                    .rechargeMoney(BigDecimal.ZERO)
                    .multiples(BigDecimal.ZERO)
                    .build());

            return vo;
        }

        //计算总数据
        //总注册成本
        vo.setRegTotalCost(vo.getRegTotalNum() == 0L ? BigDecimal.ZERO :
                vo.getPromotionTotalCost().divide(BigDecimal.valueOf(vo.getRegTotalNum()), 2, RoundingMode.HALF_UP));
        //总创角成本
        vo.setRoleTotalCost(vo.getRoleTotalNum() == 0L ? BigDecimal.ZERO :
                vo.getPromotionTotalCost().divide(BigDecimal.valueOf(vo.getRoleTotalNum()), 2, RoundingMode.HALF_UP));
        //总创角率
        vo.setRoleTotalRate(vo.getRegTotalNum() == 0L ? BigDecimal.ZERO :
                BigDecimal.valueOf(vo.getRoleTotalNum()).divide(BigDecimal.valueOf(vo.getRegTotalNum()), 4, RoundingMode.HALF_UP));
        //广告总ROI
        vo.setPromotionTotalRoi(vo.getPromotionTotalCost().compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                vo.getTotalAmount().divide(vo.getPromotionTotalCost(), 4, RoundingMode.HALF_UP));
        //总付费成本
        vo.setTotalRechargeCost(vo.getTotalAmountNum() == 0L ? BigDecimal.ZERO :
                vo.getPromotionTotalCost().divide(BigDecimal.valueOf(vo.getTotalAmountNum()), 2, RoundingMode.HALF_UP));
        //总充值次数成本
        vo.setTotalAmountCountCost(vo.getTotalAmountCount() == 0L ? BigDecimal.ZERO :
                vo.getPromotionTotalCost().divide(BigDecimal.valueOf(vo.getTotalAmountCount()), 2, RoundingMode.HALF_UP));

        //计算d2，d3，d7，d15数据
        //计算d2(次日)数据
        vo.setD2Trend(PromotionRechargeTrendVO.builder()
                .roi(vo.getTodayCost().compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                        vo.getD2().divide(vo.getTodayCost(), 4, RoundingMode.HALF_UP))
                .rechargeMoney(vo.getD2())
                .multiples(vo.getD1().compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                        vo.getD2().divide(vo.getD1(), 2, RoundingMode.HALF_UP))
                .build());
        //计算d3数据
        vo.setD3Trend(PromotionRechargeTrendVO.builder()
                .roi(vo.getTodayCost().compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                        vo.getD3().divide(vo.getTodayCost(), 4, RoundingMode.HALF_UP))
                .rechargeMoney(vo.getD3())
                .multiples(vo.getD1().compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                        vo.getD3().divide(vo.getD1(), 2, RoundingMode.HALF_UP))
                .build());
        //计算d7数据
        vo.setD7Trend(PromotionRechargeTrendVO.builder()
                .roi(vo.getTodayCost().compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                        vo.getD7().divide(vo.getTodayCost(), 4, RoundingMode.HALF_UP))
                .rechargeMoney(vo.getD7())
                .multiples(vo.getD1().compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                        vo.getD7().divide(vo.getD1(), 2, RoundingMode.HALF_UP))
                .build());
        //计算d15数据
        vo.setD15Trend(PromotionRechargeTrendVO.builder()
                .roi(vo.getTodayCost().compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                        vo.getD15().divide(vo.getTodayCost(), 4, RoundingMode.HALF_UP))
                .rechargeMoney(vo.getD15())
                .multiples(vo.getD1().compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                        vo.getD15().divide(vo.getD1(), 2, RoundingMode.HALF_UP))
                .build());

        return vo;
    }

    /**
     * 广告监控用到的查询条件
     *
     * @param dto         前端传递的查询条件
     * @param needGroupBy 需要聚合分组
     * @return 查询条件
     */
    private Criteria myCri(List<Long> userIds, List<Long> gameIds, PromotionDayDTO dto, Boolean needGroupBy) {
        //创建查询条件
        Criteria cri = Cnd.cri();
        if (dto.getAccountId() != null) {
            //拼接推广账号Id查询条件
            cri.where().andEquals("account_id", dto.getAccountId());
        }
        if (StringUtils.isNotBlank(dto.getAccountName())) {
            //拼接推广账号名称查询条件 模糊查询
            cri.where().andLike("account_name", dto.getAccountName());
        }
        if (StringUtils.isNotBlank(dto.getAccountType())) {
            //推广账号类型
            cri.where().andEquals("account_type", dto.getAccountType());
        }
        if (CollectionUtils.isNotEmpty(userIds)) {
            cri.where().andInList("pitcher_id", userIds);
        }
        if (CollectionUtils.isNotEmpty(dto.getAgentId())) {
            cri.where().andInList("agent_id", dto.getAgentId());
        }
        if (StringUtils.isNotBlank(dto.getCpName())) {
            cri.where().andEquals("cp_name", dto.getCpName());
        }
        if (CollectionUtils.isNotEmpty(gameIds)) {
            cri.where().andInList("game_id", gameIds);
        }
        if (dto.getClassify() != null) {
            cri.where().andEquals("classify", dto.getClassify());
        }
        if (StringUtils.isNotBlank(dto.getPromotionName())) {
            cri.where().andLike("promotion_name", dto.getPromotionName());
        }
        if (dto.getPromotionId() != null) {
            cri.where().andEquals("promotion_id", dto.getPromotionId());
        }
        if (StringUtils.isNotBlank(dto.getStatus())) {
            cri.where().andEquals("status", dto.getStatus());
        }
        if (StringUtils.isNotBlank(dto.getProjectName())) {
            cri.where().andLike("project_name", dto.getProjectName());
        }
        if (dto.getProjectId() != null) {
            cri.where().andEquals("project_id", dto.getProjectId());
        }
        if (dto.getCostBeginDate() != null && dto.getCostEndDate() != null) {
            cri.where().andBetween("dt", dto.getCostBeginDate(), dto.getCostEndDate());
        }
        if (StringUtils.isNotBlank(dto.getSourceSystem())) {
            cri.where().andEquals("source_system", dto.getSourceSystem());
        }
        if (needGroupBy) {
            //只作用于主表查询，首日充值金额筛选
            if (dto.getFirstRechargeAmountMin() != null) {
                cri.where().andGTE("first_new_user_amount", dto.getFirstRechargeAmountMin());
            }
            if (dto.getFirstRechargeAmountMax() != null) {
                cri.where().andLTE("first_new_user_amount", dto.getFirstRechargeAmountMax());
            }
            //拼接分组条件
            cri.getGroupBy().groupBy("promotion_id");
        }

        return cri;
    }

    /**
     * 广告监控用到的查询条件(腾讯广告)
     *
     * @param dto         前端传递的查询条件
     * @param needGroupBy 需要聚合分组
     * @return 查询条件
     */
    private Criteria myCriTencent(List<Long> userIds, List<Long> gameIds, TencentPromotionDayDTO dto, Boolean needGroupBy) {
        //创建查询条件
        Criteria cri = Cnd.cri();
        if (dto.getAccountId() != null) {
            //拼接推广账号Id查询条件
            cri.where().andEquals("account_id", dto.getAccountId());
        }
        if (StringUtils.isNotBlank(dto.getAccountName())) {
            //拼接推广账号名称查询条件 模糊查询
            cri.where().andLike("account_name", dto.getAccountName());
        }
        if (StringUtils.isNotBlank(dto.getAccountType())) {
            //推广账号类型
            cri.where().andEquals("account_type", dto.getAccountType());
        }
        if (userIds != null) {
            cri.where().andInList("pitcher_id", userIds);
        }
        if (CollectionUtils.isNotEmpty(dto.getAgentId())) {
            cri.where().andInList("agent_id", dto.getAgentId());
        }
        if (StringUtils.isNotBlank(dto.getCpName())) {
            cri.where().andEquals("cp_name", dto.getCpName());
        }
        if (gameIds != null) {
            cri.where().andInList("game_id", gameIds);
        }
        if (dto.getClassify() != null) {
            cri.where().andEquals("classify", dto.getClassify());
        }
        if (StringUtils.isNotBlank(dto.getPromotionName())) {
            cri.where().andLike("promotion_name", dto.getPromotionName());
        }
        if (dto.getPromotionId() != null) {
            cri.where().andEquals("promotion_id", dto.getPromotionId());
        }
        if (StringUtils.isNotBlank(dto.getStatus())) {
            cri.where().andEquals("status", dto.getStatus());
        }
        if (StringUtils.isNotBlank(dto.getProjectName())) {
            cri.where().andLike("project_name", dto.getProjectName());
        }
        if (dto.getProjectId() != null) {
            cri.where().andEquals("project_id", dto.getProjectId());
        }
        if (dto.getCostBeginDate() != null && dto.getCostEndDate() != null) {
            cri.where().andBetween("dt", dto.getCostBeginDate(), dto.getCostEndDate());
        }
        if (StringUtils.isNotBlank(dto.getSourceSystem())) {
            cri.where().andEquals("source_system", dto.getSourceSystem());
        }
        if (needGroupBy) {
            //拼接分组条件
            cri.getGroupBy().groupBy("promotion_id");
        }

        return cri;
    }

    /**
     * 所有为空值的属性都不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
     *
     * @return String
     */
    private String getPromotionDayListSql() {
        return """
                SELECT
                    dt,
                    status,
                    creative_preview,
                    landing_type,
                    pricing,
                    cpa_bid,
                    roi_goal,
                    budget,
                    concat(start_time,'/',end_time) as schedule_time,
                    notes,
                    service,
                    balance,
                    promotion_total_cost,
                    convert_target,
                    reg_total_num,
                    role_total_num,
                    reg_total_cost,
                    role_total_cost,
                    role_total_rate,
                    total_amount_count,
                    total_amount_num,
                    total_amount,
                    promotion_total_roi,
                    total_recharge_cost,
                	promotion_id,
                	promotion_name,
                	project_id,
                	project_name,
                	account_name,
                	account_id,
                	account_type,
                	pitcher_id,
                	pitcher_name,
                	agent_id,
                	agent_name,
                	agent_key,
                	cp_name,
                	game_id,
                	game_name,
                	classify,
                	today_cost,
                	show_count,
                	thousand_display_price,
                	click_count,
                	avg_click_cost,
                	ctr,
                	convert_count,
                	convert_cost,
                	convert_rate,
                	reg_num,
                	first_role_num,
                	new_user_total_role_num,
                	reg_cost,
                	first_role_cost,
                	new_user_total_role_cost,
                	first_role_rate,
                	new_user_total_role_rate,
                	first_new_user_amount_count,
                	first_new_user_amount_num,
                	first_new_user_amount,
                	new_user_total_amount_count,
                	new_user_total_amount_num,
                	new_user_total_amount,
                	first_roi,
                	twenty_four_hours_amount,
                	twenty_four_hours_roi,
                	total_roi,
                	first_new_user_recharge_cost,
                	new_user_total_recharge_cost,
                	first_new_user_arppu,
                	new_user_total_amount_arppu,
                	first_new_user_hundred_user_num,
                	first_new_user_hundred_user_cost,
                	first_recharge_fifty_hundred_num,
                	first_recharge_fifty_hundred_rate,
                	first_new_user_two_hundred_user_num,
                	first_new_user_two_hundred_user_cost,
                	new_user_total_hundred_user_num,
                	new_user_total_hundred_user_cost,
                	first_ios_amount_num,
                	first_ios_amount_count,
                	first_ios_amount,
                	first_ios_amount_num_rate,
                	first_ios_amount_rate,
                	first_ios_amount_roi,
                	first_android_amount_count,
                	first_android_amount_num,
                	first_android_amount,
                	first_android_amount_num_rate,
                	first_android_amount_rate,
                	first_android_amount_roi,
                	first_amount_rate,
                	first_new_user_avg_price,
                	new_user_total_avg_price,
                	d1,
                	d2,
                	d3,
                	d7,
                	d15,
                	active,
                    active_cost,
                    first_convert_count,
                    first_convert_cost,
                    first_active_pay_rate,
                    first_attribution_game_in_app_ltv1day,
                    first_attribution_game_in_app_roi1day,
                    game_pay_count,
                    ROUND(IF(game_pay_count > 0, today_cost / game_pay_count, 0), 2) as game_pay_count_cost,
                    ltv_day1,
                    ROUND(IF(reg_num > 0, first_new_user_amount / reg_num, 0), 2) as ltv_day1_actual_data,
                    sub_order_num,
                    sub_order_amount,
                    ROUND(IF(first_new_user_amount_count > 0, today_cost / first_new_user_amount_count, 0), 2) as first_new_user_amount_count_cost,
                    ROUND(IF(new_user_total_amount_count > 0, today_cost / new_user_total_amount_count, 0), 2) as new_user_total_amount_count_cost,
                    ROUND(IF(total_amount_count > 0, promotion_total_cost / total_amount_count, 0), 2) as total_amount_count_cost,
                    attribution_game_pay7d_count,
                    attribution_active_pay7d_per_count,
                    attribution_game_pay7d_cost,
                    day7_amount_count,
                    day7_amount_per_cost,
                    day7_amount_per_count,
                    day7_arpu,
                    day7_amount_num,
                    day7_amount,
                    day7_one_hundred_amount_num,
                    day7_one_hundred_amount_num_cost,
                    day7_two_hundred_amount_num,
                    day7_two_hundred_amount_num_cost,
                    new_user_two_hundred_user_num,
                    new_user_two_hundred_user_num_cost,
                    attribution_game_in_app_roi2days,
                    attribution_game_in_app_roi3days,
                    attribution_game_in_app_roi4days,
                    attribution_game_in_app_roi5days,
                    attribution_game_in_app_roi6days,
                    attribution_game_in_app_roi7days,
                    attribution_game_in_app_roi8days
                FROM
                	game_ads.ads_promotion_day
                """;
    }

    /**
     * 获取广告监控数据的SQL
     * @param criA
     * @param criB
     * @return
     */
    private String getPromotionDayDataSql(Criteria criA, Criteria criB) {
        return """
                SELECT
                b.*,
                c.*
                FROM(
                	SELECT
                		promotion_id,
                		MIN(promotion_name) as promotion_name,
                		MAX(project_id) as project_id,
                		MAX(project_name) as project_name,
                		MAX(account_name) as account_name,
                		MAX(account_id) as account_id,
                		MAX(account_type) as account_type,
                		MAX(pitcher_id) as pitcher_id,
                		MAX(pitcher_name) as pitcher_name,
                		MAX(agent_id) as agent_id,
                		MAX(agent_name) as agent_name,
                		MAX(agent_key) as agent_key,
                		MAX(cp_name) as cp_name,
                		MAX(game_id) as game_id,
                		MAX(game_name) as game_name,
                		MAX(classify) as classify,
                		SUM(today_cost) as today_cost,
                		SUM(show_count) as show_count,
                		round(if(SUM(show_count) > 0, SUM(today_cost) * 1000 / SUM(show_count), 0), 2) as thousand_display_price,
                		SUM(click_count) as click_count,
                		round(if(SUM(click_count) > 0, SUM(today_cost) / SUM(click_count), 0), 2) as avg_click_cost,
                		round(if(SUM(show_count) > 0, SUM(click_count) / SUM(show_count), 0), 4) as ctr,
                		SUM(convert_count) as convert_count,
                		ROUND(IF(SUM(convert_count) > 0, SUM(today_cost) / SUM(convert_count), 0), 2) as convert_cost,
                		round(if(SUM(click_count) > 0, SUM(convert_count) / SUM(click_count), 0), 4) as convert_rate,
                		SUM(reg_num) as reg_num,
                		SUM(first_role_num) as first_role_num,
                		SUM(new_user_total_role_num) as new_user_total_role_num,
                		round(if(SUM(reg_num) > 0, SUM(today_cost) / SUM(reg_num), 0), 2) as reg_cost,
                		round(if(SUM(first_role_num) > 0, SUM(today_cost) / SUM(first_role_num), 0), 2) as first_role_cost,
                		round(if(SUM(new_user_total_role_num) > 0, SUM(today_cost) / SUM(new_user_total_role_num), 0), 2) as new_user_total_role_cost,
                		round(if(SUM(reg_num) > 0, SUM(first_role_num) / SUM(reg_num), 0), 4) as first_role_rate,
                		round(if(SUM(reg_num) > 0, SUM(new_user_total_role_num) / SUM(reg_num), 0), 4) as new_user_total_role_rate,
                		SUM(first_new_user_amount_count) as first_new_user_amount_count,
                		SUM(first_new_user_amount_num) as first_new_user_amount_num,
                		SUM(first_new_user_amount) as first_new_user_amount,
                		SUM(new_user_total_amount_count) as new_user_total_amount_count,
                		SUM(new_user_total_amount_num) as new_user_total_amount_num,
                		SUM(new_user_total_amount) as new_user_total_amount,
                		SUM(first_attribution_game_pay7d_count) as first_attribution_game_pay7d_count,  -- 七日首次付费次数（也就是七日付费人数）
                		round(if(SUM(today_cost) > 0, SUM(first_new_user_amount) / SUM(today_cost), 0), 4) as first_roi,
                		SUM(twenty_four_hours_amount) as twenty_four_hours_amount,
                		round(if(SUM(today_cost) > 0, SUM(twenty_four_hours_amount) / SUM(today_cost), 0), 4) as twenty_four_hours_roi,
                		round(if(SUM(today_cost) > 0, SUM(new_user_total_amount) / SUM(today_cost), 0), 4) as total_roi,
                		round(if(SUM(first_new_user_amount_num) > 0, SUM(today_cost) / SUM(first_new_user_amount_num), 0), 2) as first_new_user_recharge_cost,
                		round(if(SUM(new_user_total_amount_num) > 0, SUM(today_cost) / SUM(new_user_total_amount_num), 0), 2) as new_user_total_recharge_cost,
                		round(if(SUM(first_new_user_amount_num) > 0, SUM(first_new_user_amount) / SUM(first_new_user_amount_num), 0), 2) as first_new_user_arppu,
                		round(if(SUM(new_user_total_amount_num) > 0, SUM(new_user_total_amount) / SUM(new_user_total_amount_num), 0), 2) as new_user_total_amount_arppu,
                		SUM(first_new_user_hundred_user_num) as first_new_user_hundred_user_num,
                		round(if(SUM(first_new_user_hundred_user_num) > 0, SUM(today_cost) / SUM(first_new_user_hundred_user_num), 0), 2) as first_new_user_hundred_user_cost,
                		SUM(first_recharge_fifty_hundred_num) as first_recharge_fifty_hundred_num,
                		round(if(SUM(new_user_total_amount_num) > 0, SUM(first_recharge_fifty_hundred_num) / SUM(new_user_total_amount_num), 0), 4) as first_recharge_fifty_hundred_rate,
                		SUM(first_new_user_two_hundred_user_num) as first_new_user_two_hundred_user_num,
                		round(if(SUM(first_new_user_two_hundred_user_num) > 0, SUM(today_cost) / SUM(first_new_user_two_hundred_user_num), 0), 2) as first_new_user_two_hundred_user_cost,
                		SUM(new_user_total_hundred_user_num) as new_user_total_hundred_user_num,
                		round(if(SUM(new_user_total_hundred_user_num) > 0, SUM(today_cost) / SUM(new_user_total_hundred_user_num), 0), 2) as new_user_total_hundred_user_cost,
                		SUM(first_ios_amount_num) as first_ios_amount_num,
                		SUM(first_ios_amount_count) as first_ios_amount_count,
                		SUM(first_ios_amount) as first_ios_amount,
                		round(if(SUM(first_new_user_amount_num) > 0, SUM(first_ios_amount_num) / SUM(first_new_user_amount_num), 0), 4) as first_ios_amount_num_rate,
                		round(if(SUM(first_new_user_amount) > 0, SUM(first_ios_amount) / SUM(first_new_user_amount), 0), 4) as first_ios_amount_rate,
                		round(if(SUM(today_cost)> 0, SUM(first_ios_amount) / SUM(today_cost), 0), 4) as first_ios_amount_roi,
                		SUM(first_android_amount_count) as first_android_amount_count,
                		SUM(first_android_amount_num) as first_android_amount_num,
                		SUM(first_android_amount) as first_android_amount,
                		round(if(SUM(first_new_user_amount_num) > 0,SUM(first_android_amount_num) / SUM(first_new_user_amount_num), 0), 4) as first_android_amount_num_rate,
                		round(if(SUM(first_new_user_amount) > 0, SUM(first_android_amount) / SUM(first_new_user_amount), 0), 4) as first_android_amount_rate,
                		round(if(SUM(today_cost) > 0, SUM(first_android_amount) / SUM(today_cost), 0), 4) as first_android_amount_roi,
                		round(if(SUM(reg_num) > 0, SUM(first_new_user_amount_num) / SUM(reg_num), 0), 4) as first_amount_rate,
                		round(if(SUM(first_new_user_amount_count) > 0, SUM(first_new_user_amount) / SUM(first_new_user_amount_count), 0), 2) as first_new_user_avg_price,
                		round(if(SUM(new_user_total_amount_count) > 0, SUM(new_user_total_amount) / SUM(new_user_total_amount_count), 0), 2) as new_user_total_avg_price,
                		SUM(d1) as d1,
                		SUM(d2) as d2,
                		SUM(d3) as d3,
                		SUM(d7) as d7,
                		SUM(d15) as d15,
                		IFNULL(SUM(active), 0) as active,
                	    ROUND(IF(SUM(active) > 0, SUM(today_cost) / SUM(active), 0), 2) as active_cost,
                	    IFNULL(SUM(first_convert_count), 0) as first_convert_count,
                	    ROUND(IF(SUM(first_convert_count) > 0, SUM(today_cost) / SUM(first_convert_count), 0), 2) as first_convert_cost,
                	    ROUND(IF(SUM(active) > 0, SUM(game_pay_count) / SUM(active), 0), 4) as first_active_pay_rate,
                	    SUM(first_attribution_game_in_app_ltv1day) as first_attribution_game_in_app_ltv1day,
                	    ROUND(IF(SUM(today_cost) > 0 , SUM(first_attribution_game_in_app_ltv1day) / SUM(today_cost), 0), 4) as first_attribution_game_in_app_roi1day,
                	    SUM(game_pay_count) as game_pay_count,
                	    ROUND(IF(SUM(game_pay_count) > 0, SUM(today_cost) / SUM(game_pay_count), 0), 2) as game_pay_count_cost,
                	    ROUND(IF(SUM(reg_num) > 0, SUM(first_attribution_game_in_app_ltv1day) / SUM(reg_num), 0), 2) as ltv_day1,
                	    ROUND(IF(SUM(reg_num) > 0, SUM(first_new_user_amount) / SUM(reg_num), 0), 2) as ltv_day1_actual_data,
                	    (SUM(first_new_user_amount_count) - SUM(first_sub_amount_count)) as sub_order_num,
                	    (SUM(first_new_user_amount) - SUM(first_sub_amount)) as sub_order_amount,
                	    ROUND(IF(SUM(first_new_user_amount_count) > 0, SUM(today_cost) / SUM(first_new_user_amount_count), 0), 2) as first_new_user_amount_count_cost,
                	    ROUND(IF(SUM(new_user_total_amount_count) > 0, SUM(today_cost) / SUM(new_user_total_amount_count), 0), 2) as new_user_total_amount_count_cost,
                	    SUM(attribution_game_pay7d_count) as attribution_game_pay7d_count,
                	    SUM(attribution_active_pay7d_per_count) as attribution_active_pay7d_per_count,
                	    ROUND(if(SUM(attribution_game_pay7d_count) > 0, SUM(today_cost) / SUM(attribution_game_pay7d_count), 0), 2) as attribution_game_pay7d_cost,
                	    SUM(day7_amount_count) as day7_amount_count,
                	    ROUND(if(SUM(day7_amount_count) > 0, SUM(today_cost) / SUM(day7_amount_count), 0), 2) as day7_amount_per_cost,
                	    ROUND(if(SUM(day7_amount_num) > 0, SUM(day7_amount_count) / SUM(day7_amount_num), 0), 2) as day7_amount_per_count,
                	    ROUND(if(SUM(day7_amount_num) > 0, SUM(day7_amount) / SUM(day7_amount_num), 0), 2) as day7_arpu,
                	    SUM(day7_amount) as day7_amount,
                	    SUM(day7_amount_num) as day7_amount_num,
                	    SUM(day7_one_hundred_amount_num) as day7_one_hundred_amount_num ,
                     	SUM(day7_one_hundred_amount_num_cost) as day7_one_hundred_amount_num_cost,
                     	SUM(day7_two_hundred_amount_num) as day7_two_hundred_amount_num,
                     	SUM(day7_two_hundred_amount_num_cost) as day7_two_hundred_amount_num_cost,
                     	SUM(new_user_two_hundred_user_num) as new_user_two_hundred_user_num,
                     	SUM(new_user_two_hundred_user_num_cost) as new_user_two_hundred_user_num_cost,
                        -- 2-8日的roi
                        ROUND(IF(SUM(today_cost)>0,SUM(attribution_game_in_app_ltv2days)/SUM(today_cost)*100,0),2) as attribution_game_in_app_roi2days,
                        ROUND(IF(SUM(today_cost)>0,SUM(attribution_game_in_app_ltv3days)/SUM(today_cost)*100,0),2) as attribution_game_in_app_roi3days,
                        ROUND(IF(SUM(today_cost)>0,SUM(attribution_game_in_app_ltv4days)/SUM(today_cost)*100,0),2) as attribution_game_in_app_roi4days,
                        ROUND(IF(SUM(today_cost)>0,SUM(attribution_game_in_app_ltv5days)/SUM(today_cost)*100,0),2) as attribution_game_in_app_roi5days,
                        ROUND(IF(SUM(today_cost)>0,SUM(attribution_game_in_app_ltv6days)/SUM(today_cost)*100,0),2) as attribution_game_in_app_roi6days,
                        ROUND(IF(SUM(today_cost)>0,SUM(attribution_game_in_app_ltv7days)/SUM(today_cost)*100,0),2) as attribution_game_in_app_roi7days,
                        ROUND(IF(SUM(today_cost)>0,SUM(attribution_game_in_app_ltv8days)/SUM(today_cost)*100,0),2) as attribution_game_in_app_roi8days
                	FROM
                		game_ads.ads_promotion_day
                """ + criA +
                """
                	 ) b
                LEFT JOIN(
                	SELECT
                	*
                	FROM(
                	SELECT
                	    ROW_NUMBER()over(partition by promotion_id order by dt desc) as num,
                	    promotion_id,
                	    status,
                	    creative_preview,
                	    landing_type,
                	    pricing,
                	    cpa_bid,
                	    roi_goal,
                	    budget,
                	    concat(start_time,'/',end_time) as schedule_time,
                	    notes,
                	    service,
                	    balance,
                	    promotion_total_cost,
                	    convert_target,
                	    reg_total_num,
                	    role_total_num,
                	    reg_total_cost,
                	    role_total_cost,
                	    role_total_rate,
                	    total_amount_count,
                	    total_amount_num,
                	    total_amount,
                	    promotion_total_roi,
                	    ROUND(IF(total_amount_count > 0, promotion_total_cost / total_amount_count, 0), 2) as total_amount_count_cost,
                	    total_recharge_cost
                	FROM
                		game_ads.ads_promotion_day
                	""" + criB +
                """
                 ) a
                WHERE a.num = 1) c
            ON b.promotion_id = c.promotion_id
            """;
    }

    /**
     * 查询广告监控每日总计一栏sql
     *
     * @return String
     */
    private String promotionDayTotalSql() {
        return """
                SELECT
                    IFNULL(SUM(today_cost), 0) as today_cost,
                	IFNULL(SUM(show_count), 0) as show_count,
                	round(if(SUM(show_count) > 0, SUM(today_cost) * 1000 / SUM(show_count), 0), 2) as thousand_display_price,
                	IFNULL(SUM(click_count), 0) as click_count,
                	round(if(SUM(click_count) > 0, SUM(today_cost) / SUM(click_count), 0), 2) as avg_click_cost,
                	round(if(SUM(show_count) > 0, SUM(click_count) / SUM(show_count), 0), 4) as ctr,
                	IFNULL(SUM(convert_count), 0) as convert_count,
                	ROUND(IF(SUM(convert_count) > 0, SUM(today_cost) / SUM(convert_count), 0), 2) as convert_cost,
                	round(if(SUM(click_count) > 0, SUM(convert_count) / SUM(click_count), 0), 4) as convert_rate,
                	IFNULL(SUM(reg_num), 0) as reg_num,
                	IFNULL(SUM(first_role_num), 0) as first_role_num,
                	IFNULL(SUM(new_user_total_role_num), 0) as new_user_total_role_num,
                	round(if(SUM(reg_num) > 0, SUM(today_cost) / SUM(reg_num), 0), 2) as reg_cost,
                	round(if(SUM(first_role_num) > 0, SUM(today_cost) / SUM(first_role_num), 0), 2) as first_role_cost,
                	round(if(SUM(new_user_total_role_num) > 0, SUM(today_cost) / SUM(new_user_total_role_num), 0), 2) as new_user_total_role_cost,
                	round(if(SUM(reg_num) > 0, SUM(first_role_num) / SUM(reg_num), 0), 4) as first_role_rate,
                	round(if(SUM(reg_num) > 0, SUM(new_user_total_role_num) / SUM(reg_num), 0), 4) as new_user_total_role_rate,
                	IFNULL(SUM(first_new_user_amount_count), 0) as first_new_user_amount_count,
                	IFNULL(SUM(first_new_user_amount_num), 0) as first_new_user_amount_num,
                	IFNULL(SUM(first_new_user_amount), 0) as first_new_user_amount,
                	IFNULL(SUM(new_user_total_amount_count), 0) as new_user_total_amount_count,
                	IFNULL(SUM(new_user_total_amount_num), 0) as new_user_total_amount_num,
                	IFNULL(SUM(new_user_total_amount), 0) as new_user_total_amount,
                	round(if(SUM(today_cost) > 0, SUM(first_new_user_amount) / SUM(today_cost), 0), 4) as first_roi,
                	IFNULL(SUM(twenty_four_hours_amount), 0) as twenty_four_hours_amount,
                	round(if(SUM(today_cost) > 0, SUM(twenty_four_hours_amount) / SUM(today_cost), 0), 4) as twenty_four_hours_roi,
                	round(if(SUM(today_cost) > 0, SUM(new_user_total_amount) / SUM(today_cost), 0), 4) as total_roi,
                	round(if(SUM(first_new_user_amount_num) > 0, SUM(today_cost) / SUM(first_new_user_amount_num), 0), 2) as first_new_user_recharge_cost,
                	round(if(SUM(new_user_total_amount_num) > 0, SUM(today_cost) / SUM(new_user_total_amount_num), 0), 2) as new_user_total_recharge_cost,
                	round(if(SUM(first_new_user_amount_num) > 0, SUM(first_new_user_amount) / SUM(first_new_user_amount_num), 0), 2) as first_new_user_arppu,
                	round(if(SUM(new_user_total_amount_num) > 0, SUM(new_user_total_amount) / SUM(new_user_total_amount_num), 0), 2) as new_user_total_amount_arppu,
                	IFNULL(SUM(first_new_user_hundred_user_num), 0) as first_new_user_hundred_user_num,
                	round(if(SUM(first_new_user_hundred_user_num) > 0, SUM(today_cost) / SUM(first_new_user_hundred_user_num), 0), 2) as first_new_user_hundred_user_cost,
                	IFNULL(SUM(first_recharge_fifty_hundred_num), 0) as first_recharge_fifty_hundred_num,
                	round(if(SUM(new_user_total_amount_num) > 0, SUM(first_recharge_fifty_hundred_num) / SUM(new_user_total_amount_num), 0), 4) as first_recharge_fifty_hundred_rate,
                	IFNULL(SUM(first_new_user_two_hundred_user_num), 0) as first_new_user_two_hundred_user_num,
                	round(if(SUM(first_new_user_two_hundred_user_num) > 0, SUM(today_cost) / SUM(first_new_user_two_hundred_user_num), 0), 2) as first_new_user_two_hundred_user_cost,
                	IFNULL(SUM(new_user_total_hundred_user_num), 0) as new_user_total_hundred_user_num,
                	round(if(SUM(new_user_total_hundred_user_num) > 0, SUM(today_cost) / SUM(new_user_total_hundred_user_num), 0), 2) as new_user_total_hundred_user_cost,
                	IFNULL(SUM(first_ios_amount_num), 0) as first_ios_amount_num,
                	IFNULL(SUM(first_ios_amount_count), 0) as first_ios_amount_count,
                	IFNULL(SUM(first_ios_amount), 0) as first_ios_amount,
                	round(if(SUM(first_new_user_amount_num) > 0, SUM(first_ios_amount_num) / SUM(first_new_user_amount_num), 0), 4) as first_ios_amount_num_rate,
                	round(if(SUM(first_new_user_amount) > 0, SUM(first_ios_amount) / SUM(first_new_user_amount), 0), 4) as first_ios_amount_rate,
                	round(if(SUM(today_cost)> 0, SUM(first_ios_amount) / SUM(today_cost), 0), 4) as first_ios_amount_roi,
                	IFNULL(SUM(first_android_amount_count), 0) as first_android_amount_count,
                	IFNULL(SUM(first_android_amount_num), 0) as first_android_amount_num,
                	IFNULL(SUM(first_android_amount), 0) as first_android_amount,
                	round(if(SUM(first_new_user_amount_num) > 0,SUM(first_android_amount_num) / SUM(first_new_user_amount_num), 0), 4) as first_android_amount_num_rate,
                	round(if(SUM(first_new_user_amount) > 0, SUM(first_android_amount) / SUM(first_new_user_amount), 0), 4) as first_android_amount_rate,
                	round(if(SUM(today_cost) > 0, SUM(first_android_amount) / SUM(today_cost), 0), 4) as first_android_amount_roi,
                	round(if(SUM(reg_num) > 0, SUM(first_new_user_amount_num) / SUM(reg_num), 0), 4) as first_amount_rate,
                	round(if(SUM(first_new_user_amount_count) > 0, SUM(first_new_user_amount) / SUM(first_new_user_amount_count), 0), 2) as first_new_user_avg_price,
                	round(if(SUM(new_user_total_amount_count) > 0, SUM(new_user_total_amount) / SUM(new_user_total_amount_count), 0), 2) as new_user_total_avg_price,
                	IFNULL(SUM(d1), 0) as d1,
                	IFNULL(SUM(d2), 0) as d2,
                	IFNULL(SUM(d3), 0) as d3,
                	IFNULL(SUM(d7), 0) as d7,
                	IFNULL(SUM(d15), 0) as d15,
                	IFNULL(SUM(active), 0) as active,
                    ROUND(IF(SUM(active) > 0, SUM(today_cost) / SUM(active), 0), 2) as active_cost,
                    IFNULL(SUM(first_convert_count), 0) as first_convert_count,
                    ROUND(IF(SUM(first_convert_count) > 0, SUM(today_cost) / SUM(first_convert_count), 0), 2) as first_convert_cost,
                    ROUND(IF(SUM(active) > 0, SUM(game_pay_count) / SUM(active), 0), 4) as first_active_pay_rate,
                    SUM(first_attribution_game_in_app_ltv1day) as first_attribution_game_in_app_ltv1day,
                    ROUND(IF(SUM(today_cost) > 0 , SUM(first_attribution_game_in_app_ltv1day) / SUM(today_cost), 0), 4) as first_attribution_game_in_app_roi1day,
                    SUM(game_pay_count) as game_pay_count,
                    ROUND(IF(SUM(game_pay_count) > 0, SUM(today_cost) / SUM(game_pay_count), 0), 2) as game_pay_count_cost,
                    ROUND(IF(SUM(reg_num) > 0, SUM(first_attribution_game_in_app_ltv1day) / SUM(reg_num), 0), 2) as ltv_day1,
                    ROUND(IF(SUM(reg_num) > 0, SUM(first_new_user_amount) / SUM(reg_num), 0), 2) as ltv_day1_actual_data,
                    (SUM(first_new_user_amount_count) - SUM(first_sub_amount_count)) as sub_order_num,
                    (SUM(first_new_user_amount) - SUM(first_sub_amount)) as sub_order_amount,
                    ROUND(IF(SUM(first_new_user_amount_count) > 0, SUM(today_cost) / SUM(first_new_user_amount_count), 0), 2) as first_new_user_amount_count_cost,
                    ROUND(IF(SUM(new_user_total_amount_count) > 0, SUM(today_cost) / SUM(new_user_total_amount_count), 0), 2) as new_user_total_amount_count_cost,
                    SUM(attribution_game_pay7d_count) as attribution_game_pay7d_count,
                    SUM(first_attribution_game_pay7d_count) as first_attribution_game_pay7d_count,
                    ROUND(SUM(attribution_active_pay7d_per_count)/SUM(first_attribution_game_pay7d_count),2) as attribution_active_pay7d_per_count,   -- 总计的人均要用  七日付费次数/七日首次付费次数（也就是付费人数）
                    ROUND(if(SUM(attribution_game_pay7d_count) > 0, SUM(today_cost) / SUM(attribution_game_pay7d_count), 0), 2) as attribution_game_pay7d_cost,
                    SUM(day7_amount_count) as day7_amount_count,
                    ROUND(if(SUM(day7_amount_count) > 0, SUM(today_cost) / SUM(day7_amount_count), 0), 2) as day7_amount_per_cost,
                    ROUND(if(SUM(day7_amount_num) > 0, SUM(day7_amount_count) / SUM(day7_amount_num), 0), 2) as day7_amount_per_count,
                    ROUND(if(SUM(day7_amount_num) > 0, SUM(day7_amount) / SUM(day7_amount_num), 0), 2) as day7_arpu,
                    SUM(day7_amount) as day7_amount,
                    SUM(day7_amount_num) as day7_amount_num,
                    SUM(day7_one_hundred_amount_num) as day7_one_hundred_amount_num,
                    SUM(day7_one_hundred_amount_num_cost) as day7_one_hundred_amount_num_cost,
                    SUM(day7_two_hundred_amount_num) as day7_two_hundred_amount_num,
                    SUM(day7_two_hundred_amount_num_cost) as day7_two_hundred_amount_num_cost,
                    SUM(new_user_two_hundred_user_num) as new_user_two_hundred_user_num,
                    SUM(new_user_two_hundred_user_num_cost) as new_user_two_hundred_user_num_cost,
                     -- 2-8日的roi
                    ROUND(IF(SUM(today_cost)>0,SUM(attribution_game_in_app_ltv2days)/SUM(today_cost)*100,0),2) as attribution_game_in_app_roi2days,
                    ROUND(IF(SUM(today_cost)>0,SUM(attribution_game_in_app_ltv3days)/SUM(today_cost)*100,0),2) as attribution_game_in_app_roi3days,
                    ROUND(IF(SUM(today_cost)>0,SUM(attribution_game_in_app_ltv4days)/SUM(today_cost)*100,0),2) as attribution_game_in_app_roi4days,
                    ROUND(IF(SUM(today_cost)>0,SUM(attribution_game_in_app_ltv5days)/SUM(today_cost)*100,0),2) as attribution_game_in_app_roi5days,
                    ROUND(IF(SUM(today_cost)>0,SUM(attribution_game_in_app_ltv6days)/SUM(today_cost)*100,0),2) as attribution_game_in_app_roi6days,
                    ROUND(IF(SUM(today_cost)>0,SUM(attribution_game_in_app_ltv7days)/SUM(today_cost)*100,0),2) as attribution_game_in_app_roi7days,
                    ROUND(IF(SUM(today_cost)>0,SUM(attribution_game_in_app_ltv8days)/SUM(today_cost)*100,0),2) as attribution_game_in_app_roi8days
                FROM
                	game_ads.ads_promotion_day
                """;
    }

    /**
     * 用来查询出总计一栏的总数据sql
     *
     * @return String
     */
    private String promotionDayTotalTotalDataTempSql() {
        return """
                SELECT
                    MAX(promotion_total_cost) promotion_total_cost,
                    MAX(reg_total_num) reg_total_num,
                    MAX(role_total_num) role_total_num,
                    MAX(total_amount_count) total_amount_count,
                    MAX(total_amount_num) total_amount_num,
                    MAX(total_amount) total_amount
                FROM
                    game_ads.ads_promotion_day
                """;
    }

    /**
     * 得到计算后的总数据
     *
     * @return String
     */
    private String promotionDayTotalTotalDataSumSql() {
        return """
                SELECT
                    IFNULL(SUM(promotion_total_cost), 0) promotion_total_cost,
                    IFNULL(SUM(reg_total_num), 0) reg_total_num,
                    IFNULL(SUM(role_total_num), 0) role_total_num,
                    IFNULL(SUM(total_amount_count), 0) total_amount_count,
                    IFNULL(SUM(total_amount_num), 0) total_amount_num,
                    IFNULL(SUM(total_amount), 0) total_amount
                FROM
                """;
    }

    /**
     * 广告监控每日数据sql(腾讯广告)
     *
     * @return String
     */
    private String getTencentPromotionDayListSql() {
        return """
                SELECT
                    dt,
                    status,
                    creative_preview,
                    landing_type,
                    pricing,
                    cpa_bid,
                    roi_goal,
                    budget,
                    concat(start_time,'/',end_time) as schedule_time,
                    notes,
                    service,
                    balance,
                    is_deleted,
                	configured_status,
                    promotion_total_cost,
                    convert_target,
                    reg_total_num,
                    role_total_num,
                    reg_total_cost,
                    role_total_cost,
                    role_total_rate,
                    total_amount_count,
                    total_amount_num,
                    total_amount,
                    promotion_total_roi,
                    total_recharge_cost,
                	promotion_id,
                	promotion_name,
                	project_id,
                	project_name,
                	account_name,
                	account_id,
                	account_type,
                	pitcher_id,
                	pitcher_name,
                	agent_id,
                	agent_name,
                	agent_key,
                	cp_name,
                	game_id,
                	game_name,
                	classify,
                	today_cost,
                	show_count,
                	thousand_display_price,
                	click_count,
                	avg_click_cost,
                	ctr,
                	convert_count,
                	convert_cost,
                	convert_rate,
                	reg_num,
                	first_role_num,
                	new_user_total_role_num,
                	reg_cost,
                	first_role_cost,
                	new_user_total_role_cost,
                	first_role_rate,
                	new_user_total_role_rate,
                	first_new_user_amount_count,
                	first_new_user_amount_num,
                	first_new_user_amount,
                	new_user_total_amount_count,
                	new_user_total_amount_num,
                	new_user_total_amount,
                	first_roi,
                	twenty_four_hours_amount,
                	twenty_four_hours_roi,
                	total_roi,
                	first_new_user_recharge_cost,
                	new_user_total_recharge_cost,
                	first_new_user_arppu,
                	new_user_total_amount_arppu,
                	first_new_user_hundred_user_num,
                	first_new_user_hundred_user_cost,
                	first_recharge_fifty_hundred_num,
                	first_recharge_fifty_hundred_rate,
                	first_new_user_two_hundred_user_num,
                	first_new_user_two_hundred_user_cost,
                	new_user_total_hundred_user_num,
                	new_user_total_hundred_user_cost,
                	first_ios_amount_num,
                	first_ios_amount_count,
                	first_ios_amount,
                	first_ios_amount_num_rate,
                	first_ios_amount_rate,
                	first_ios_amount_roi,
                	first_android_amount_count,
                	first_android_amount_num,
                	first_android_amount,
                	first_android_amount_num_rate,
                	first_android_amount_rate,
                	first_android_amount_roi,
                	first_amount_rate,
                	first_new_user_avg_price,
                	new_user_total_avg_price,
                	d1,
                	d2,
                	d3,
                	d7,
                	d15,
                	mini_game_register_users,
                    mini_game_register_cost,
                    mini_game_register_rate,
                    mini_game_paying_count,
                    mini_game_paying_amount,
                    mini_game_paying_users_d1,
                    mini_game_paying_amount_d1,
                    mini_game_paying_amount_d3,
                    mini_game_paying_amount_d7,
                    mini_game_paying_amount_d14,
                    mini_game_paying_amount_d30,
                    mini_game_first_paying_users,
                    mini_game_first_day_paying_roi,
                    mini_game_paying_roi,
                    mini_game_paying_arpu,
                    mini_game_pay_d3_roi,
                    mini_game_pay_d7_roi,
                    mini_game_pay_d14_roi,
                    mini_game_pay_d30_roi,
                    minigame1d_pay_count,
                    minigame24h_pay_uv,
                    minigame24h_pay_amount,
                    minigame24h_pay_roi,
                    minigame24h_pay_arpu,
                    ltv_day1,
                    ROUND(IF(reg_num > 0, first_new_user_amount / reg_num, 0), 2) as ltv_day1_actual_data,
                    sub_order_num,
                    sub_order_amount,
                    ROUND(IF(first_new_user_amount_count > 0, today_cost / first_new_user_amount_count, 0), 2) as first_new_user_amount_count_cost,
                    ROUND(IF(new_user_total_amount_count > 0, today_cost / new_user_total_amount_count, 0), 2) as new_user_total_amount_count_cost,
                    ROUND(IF(total_amount_count > 0, promotion_total_cost / total_amount_count, 0), 2) as total_amount_count_cost
                FROM
                	game_ads.ads_adgroup_day
                """;
    }

    /**
     * 获取广告监控数据的SQL(腾讯广告)
     * @param criA
     * @param criB
     * @return
     */
    private String getTencentPromotionDayDataSql(Criteria criA, Criteria criB) {
        return """
                SELECT
                b.*,
                c.*
                FROM(
                	SELECT
                		promotion_id,
                		MAX(promotion_name) as promotion_name,
                		MAX(project_id) as project_id,
                		MAX(project_name) as project_name,
                		MAX(account_name) as account_name,
                		MAX(account_id) as account_id,
                		MAX(account_type) as account_type,
                		MAX(pitcher_id) as pitcher_id,
                		MAX(pitcher_name) as pitcher_name,
                		MAX(agent_id) as agent_id,
                		MAX(agent_name) as agent_name,
                		MAX(agent_key) as agent_key,
                		MAX(cp_name) as cp_name,
                		MAX(game_id) as game_id,
                		MAX(game_name) as game_name,
                		MAX(classify) as classify,
                		MAX(is_deleted) as is_deleted,
                		MAX(configured_status) as configured_status,
                		SUM(today_cost) as today_cost,
                		SUM(show_count) as show_count,
                		round(if(SUM(show_count) > 0, SUM(today_cost) * 1000 / SUM(show_count), 0), 2) as thousand_display_price,
                		SUM(click_count) as click_count,
                		round(if(SUM(click_count) > 0, SUM(today_cost) / SUM(click_count), 0), 2) as avg_click_cost,
                		round(if(SUM(show_count) > 0, SUM(click_count) / SUM(show_count), 0), 4) as ctr,
                		SUM(convert_count) as convert_count,
                		ROUND(IF(SUM(convert_count) > 0, SUM(today_cost) / SUM(convert_count), 0), 2) as convert_cost,
                		round(if(SUM(click_count) > 0, SUM(convert_count) / SUM(click_count), 0), 4) as convert_rate,
                		SUM(reg_num) as reg_num,
                		SUM(first_role_num) as first_role_num,
                		SUM(new_user_total_role_num) as new_user_total_role_num,
                		round(if(SUM(reg_num) > 0, SUM(today_cost) / SUM(reg_num), 0), 2) as reg_cost,
                		round(if(SUM(first_role_num) > 0, SUM(today_cost) / SUM(first_role_num), 0), 2) as first_role_cost,
                		round(if(SUM(new_user_total_role_num) > 0, SUM(today_cost) / SUM(new_user_total_role_num), 0), 2) as new_user_total_role_cost,
                		round(if(SUM(reg_num) > 0, SUM(first_role_num) / SUM(reg_num), 0), 4) as first_role_rate,
                		round(if(SUM(reg_num) > 0, SUM(new_user_total_role_num) / SUM(reg_num), 0), 4) as new_user_total_role_rate,
                		SUM(first_new_user_amount_count) as first_new_user_amount_count,
                		SUM(first_new_user_amount_num) as first_new_user_amount_num,
                		SUM(first_new_user_amount) as first_new_user_amount,
                		SUM(new_user_total_amount_count) as new_user_total_amount_count,
                		SUM(new_user_total_amount_num) as new_user_total_amount_num,
                		SUM(new_user_total_amount) as new_user_total_amount,
                		round(if(SUM(today_cost) > 0, SUM(first_new_user_amount) / SUM(today_cost), 0), 4) as first_roi,
                		SUM(twenty_four_hours_amount) as twenty_four_hours_amount,
                		round(if(SUM(today_cost) > 0, SUM(twenty_four_hours_amount) / SUM(today_cost), 0), 4) as twenty_four_hours_roi,
                		round(if(SUM(today_cost) > 0, SUM(new_user_total_amount) / SUM(today_cost), 0), 4) as total_roi,
                		round(if(SUM(first_new_user_amount_num) > 0, SUM(today_cost) / SUM(first_new_user_amount_num), 0), 2) as first_new_user_recharge_cost,
                		round(if(SUM(new_user_total_amount_num) > 0, SUM(today_cost) / SUM(new_user_total_amount_num), 0), 2) as new_user_total_recharge_cost,
                		round(if(SUM(first_new_user_amount_num) > 0, SUM(first_new_user_amount) / SUM(first_new_user_amount_num), 0), 2) as first_new_user_arppu,
                		round(if(SUM(new_user_total_amount_num) > 0, SUM(new_user_total_amount) / SUM(new_user_total_amount_num), 0), 2) as new_user_total_amount_arppu,
                		SUM(first_new_user_hundred_user_num) as first_new_user_hundred_user_num,
                		round(if(SUM(first_new_user_hundred_user_num) > 0, SUM(today_cost) / SUM(first_new_user_hundred_user_num), 0), 2) as first_new_user_hundred_user_cost,
                		SUM(first_recharge_fifty_hundred_num) as first_recharge_fifty_hundred_num,
                		round(if(SUM(new_user_total_amount_num) > 0, SUM(first_recharge_fifty_hundred_num) / SUM(new_user_total_amount_num), 0), 4) as first_recharge_fifty_hundred_rate,
                		SUM(first_new_user_two_hundred_user_num) as first_new_user_two_hundred_user_num,
                		round(if(SUM(first_new_user_two_hundred_user_num) > 0, SUM(today_cost) / SUM(first_new_user_two_hundred_user_num), 0), 2) as first_new_user_two_hundred_user_cost,
                		SUM(new_user_total_hundred_user_num) as new_user_total_hundred_user_num,
                		round(if(SUM(new_user_total_hundred_user_num) > 0, SUM(today_cost) / SUM(new_user_total_hundred_user_num), 0), 2) as new_user_total_hundred_user_cost,
                		SUM(first_ios_amount_num) as first_ios_amount_num,
                		SUM(first_ios_amount_count) as first_ios_amount_count,
                		SUM(first_ios_amount) as first_ios_amount,
                		round(if(SUM(first_new_user_amount_num) > 0, SUM(first_ios_amount_num) / SUM(first_new_user_amount_num), 0), 4) as first_ios_amount_num_rate,
                		round(if(SUM(first_new_user_amount) > 0, SUM(first_ios_amount) / SUM(first_new_user_amount), 0), 4) as first_ios_amount_rate,
                		round(if(SUM(today_cost)> 0, SUM(first_ios_amount) / SUM(today_cost), 0), 4) as first_ios_amount_roi,
                		SUM(first_android_amount_count) as first_android_amount_count,
                		SUM(first_android_amount_num) as first_android_amount_num,
                		SUM(first_android_amount) as first_android_amount,
                		round(if(SUM(first_new_user_amount_num) > 0,SUM(first_android_amount_num) / SUM(first_new_user_amount_num), 0), 4) as first_android_amount_num_rate,
                		round(if(SUM(first_new_user_amount) > 0, SUM(first_android_amount) / SUM(first_new_user_amount), 0), 4) as first_android_amount_rate,
                		round(if(SUM(today_cost) > 0, SUM(first_android_amount) / SUM(today_cost), 0), 4) as first_android_amount_roi,
                		round(if(SUM(reg_num) > 0, SUM(first_new_user_amount_num) / SUM(reg_num), 0), 4) as first_amount_rate,
                		round(if(SUM(first_new_user_amount_count) > 0, SUM(first_new_user_amount) / SUM(first_new_user_amount_count), 0), 2) as first_new_user_avg_price,
                		round(if(SUM(new_user_total_amount_count) > 0, SUM(new_user_total_amount) / SUM(new_user_total_amount_count), 0), 2) as new_user_total_avg_price,
                		IFNULL(SUM(d1), 0) as d1,
                		IFNULL(SUM(d2), 0) as d2,
                		IFNULL(SUM(d3), 0) as d3,
                		IFNULL(SUM(d7), 0) as d7,
                		IFNULL(SUM(d15), 0) as d15,
                		IFNULL(SUM(mini_game_register_users), 0) as mini_game_register_users,
                        round(if(SUM(mini_game_register_users) > 0, SUM(today_cost) / SUM(mini_game_register_users), 0), 2) as mini_game_register_cost,
                        ROUND(IF(SUM(click_count) > 0, SUM(mini_game_register_users) / SUM(click_count), 0), 4) as mini_game_register_rate,
                        IFNULL(SUM(mini_game_paying_count), 0) as mini_game_paying_count,
                        IFNULL(SUM(mini_game_paying_amount), 0) as mini_game_paying_amount,
                        IFNULL(SUM(mini_game_paying_users_d1), 0) as mini_game_paying_users_d1,
                        IFNULL(SUM(mini_game_paying_amount_d1), 0) as mini_game_paying_amount_d1,
                        IFNULL(SUM(mini_game_paying_amount_d3), 0) as mini_game_paying_amount_d3,
                        IFNULL(SUM(mini_game_paying_amount_d7), 0) as mini_game_paying_amount_d7,
                        IFNULL(SUM(mini_game_paying_amount_d14), 0) as mini_game_paying_amount_d14,
                        IFNULL(SUM(mini_game_paying_amount_d30), 0) as mini_game_paying_amount_d30,
                        IFNULL(SUM(mini_game_first_paying_users), 0) as mini_game_first_paying_users,
                        IFNULL(SUM(mini_game_paying_roi), 0) as mini_game_paying_roi,
                        IFNULL(SUM(mini_game_paying_arpu), 0) as mini_game_paying_arpu,
                        IFNULL(SUM(mini_game_first_day_paying_roi), 0) as mini_game_first_day_paying_roi,
                        IFNULL(SUM(mini_game_pay_d3_roi), 0) as mini_game_pay_d3_roi,
                        IFNULL(SUM(mini_game_pay_d7_roi), 0) as mini_game_pay_d7_roi,
                        IFNULL(SUM(mini_game_pay_d14_roi), 0) as mini_game_pay_d14_roi,
                        IFNULL(SUM(mini_game_pay_d30_roi), 0) as mini_game_pay_d30_roi,
                        IFNULL(SUM(minigame1d_pay_count), 0) as minigame1d_pay_count,
                        IFNULL(SUM(minigame24h_pay_uv), 0) as minigame24h_pay_uv,
                        IFNULL(SUM(minigame24h_pay_amount), 0) as minigame24h_pay_amount,
                        IFNULL(SUM(minigame24h_pay_roi), 0) as minigame24h_pay_roi,
                        IFNULL(SUM(minigame24h_pay_arpu), 0) as minigame24h_pay_arpu,
                	    ROUND(IF(SUM(reg_num) > 0, SUM(mini_game_paying_amount_d1) / SUM(reg_num), 0), 2) as ltv_day1,
                	    ROUND(IF(SUM(reg_num) > 0, SUM(first_new_user_amount) / SUM(reg_num), 0), 2) as ltv_day1_actual_data,
                	    (SUM(first_new_user_amount_count) - SUM(first_sub_amount_count)) as sub_order_num,
                	    (SUM(first_new_user_amount) - SUM(first_sub_amount)) as sub_order_amount,
                	    ROUND(IF(SUM(first_new_user_amount_count) > 0, SUM(today_cost) / SUM(first_new_user_amount_count), 0), 2) as first_new_user_amount_count_cost,
                	    ROUND(IF(SUM(new_user_total_amount_count) > 0, SUM(today_cost) / SUM(new_user_total_amount_count), 0), 2) as new_user_total_amount_count_cost
                	FROM
                		game_ads.ads_adgroup_day
                """ + criA +
                """
                         ) b
                    LEFT JOIN(
                        SELECT
                        *
                        FROM(
                        SELECT
                            ROW_NUMBER()over(partition by promotion_id order by dt desc) as num,
                            promotion_id,
                            status,
                            creative_preview,
                            landing_type,
                            pricing,
                            cpa_bid,
                            roi_goal,
                            budget,
                            concat(start_time,'/',end_time) as schedule_time,
                            notes,
                            service,
                            balance,
                            promotion_total_cost,
                            convert_target,
                            reg_total_num,
                            role_total_num,
                            reg_total_cost,
                            role_total_cost,
                            role_total_rate,
                            total_amount_count,
                            total_amount_num,
                            total_amount,
                            promotion_total_roi,
                            ROUND(IF(total_amount_count > 0, promotion_total_cost / total_amount_count, 0), 2) as total_amount_count_cost,
                            total_recharge_cost
                        FROM
                            game_ads.ads_adgroup_day
                        """ + criB +
                    """
                     ) a
                    WHERE a.num = 1) c
                ON b.promotion_id = c.promotion_id
                """;
    }

    /**
     * 查询广告监控每日总计一栏sql(腾讯广告)
     *
     * @return String
     */
    private String tencentPromotionDayTotalSql() {
        return """
                SELECT
                    IFNULL(SUM(today_cost), 0) as today_cost,
                	IFNULL(SUM(show_count), 0) as show_count,
                	round(if(SUM(show_count) > 0, SUM(today_cost) * 1000 / SUM(show_count), 0), 2) as thousand_display_price,
                	IFNULL(SUM(click_count), 0) as click_count,
                	round(if(SUM(click_count) > 0, SUM(today_cost) / SUM(click_count), 0), 2) as avg_click_cost,
                	round(if(SUM(show_count) > 0, SUM(click_count) / SUM(show_count), 0), 4) as ctr,
                	IFNULL(SUM(convert_count), 0) as convert_count,
                	ROUND(IF(SUM(convert_count) > 0, SUM(today_cost) / SUM(convert_count), 0), 2) as convert_cost,
                	round(if(SUM(click_count) > 0, SUM(convert_count) / SUM(click_count), 0), 4) as convert_rate,
                	IFNULL(SUM(reg_num), 0) as reg_num,
                	IFNULL(SUM(first_role_num), 0) as first_role_num,
                	IFNULL(SUM(new_user_total_role_num), 0) as new_user_total_role_num,
                	round(if(SUM(reg_num) > 0, SUM(today_cost) / SUM(reg_num), 0), 2) as reg_cost,
                	round(if(SUM(first_role_num) > 0, SUM(today_cost) / SUM(first_role_num), 0), 2) as first_role_cost,
                	round(if(SUM(new_user_total_role_num) > 0, SUM(today_cost) / SUM(new_user_total_role_num), 0), 2) as new_user_total_role_cost,
                	round(if(SUM(reg_num) > 0, SUM(first_role_num) / SUM(reg_num), 0), 4) as first_role_rate,
                	round(if(SUM(reg_num) > 0, SUM(new_user_total_role_num) / SUM(reg_num), 0), 4) as new_user_total_role_rate,
                	IFNULL(SUM(first_new_user_amount_count), 0) as first_new_user_amount_count,
                	IFNULL(SUM(first_new_user_amount_num), 0) as first_new_user_amount_num,
                	IFNULL(SUM(first_new_user_amount), 0) as first_new_user_amount,
                	IFNULL(SUM(new_user_total_amount_count), 0) as new_user_total_amount_count,
                	IFNULL(SUM(new_user_total_amount_num), 0) as new_user_total_amount_num,
                	IFNULL(SUM(new_user_total_amount), 0) as new_user_total_amount,
                	round(if(SUM(today_cost) > 0, SUM(first_new_user_amount) / SUM(today_cost), 0), 4) as first_roi,
                	IFNULL(SUM(twenty_four_hours_amount), 0) as twenty_four_hours_amount,
                	round(if(SUM(today_cost) > 0, SUM(twenty_four_hours_amount) / SUM(today_cost), 0), 4) as twenty_four_hours_roi,
                	round(if(SUM(today_cost) > 0, SUM(new_user_total_amount) / SUM(today_cost), 0), 4) as total_roi,
                	round(if(SUM(first_new_user_amount_num) > 0, SUM(today_cost) / SUM(first_new_user_amount_num), 0), 2) as first_new_user_recharge_cost,
                	round(if(SUM(new_user_total_amount_num) > 0, SUM(today_cost) / SUM(new_user_total_amount_num), 0), 2) as new_user_total_recharge_cost,
                	round(if(SUM(first_new_user_amount_num) > 0, SUM(first_new_user_amount) / SUM(first_new_user_amount_num), 0), 2) as first_new_user_arppu,
                	round(if(SUM(new_user_total_amount_num) > 0, SUM(new_user_total_amount) / SUM(new_user_total_amount_num), 0), 2) as new_user_total_amount_arppu,
                	IFNULL(SUM(first_new_user_hundred_user_num), 0) as first_new_user_hundred_user_num,
                	round(if(SUM(first_new_user_hundred_user_num) > 0, SUM(today_cost) / SUM(first_new_user_hundred_user_num), 0), 2) as first_new_user_hundred_user_cost,
                	IFNULL(SUM(first_recharge_fifty_hundred_num), 0) as first_recharge_fifty_hundred_num,
                	round(if(SUM(new_user_total_amount_num) > 0, SUM(first_recharge_fifty_hundred_num) / SUM(new_user_total_amount_num), 0), 4) as first_recharge_fifty_hundred_rate,
                	IFNULL(SUM(first_new_user_two_hundred_user_num), 0) as first_new_user_two_hundred_user_num,
                	round(if(SUM(first_new_user_two_hundred_user_num) > 0, SUM(today_cost) / SUM(first_new_user_two_hundred_user_num), 0), 2) as first_new_user_two_hundred_user_cost,
                	IFNULL(SUM(new_user_total_hundred_user_num), 0) as new_user_total_hundred_user_num,
                	round(if(SUM(new_user_total_hundred_user_num) > 0, SUM(today_cost) / SUM(new_user_total_hundred_user_num), 0), 2) as new_user_total_hundred_user_cost,
                	IFNULL(SUM(first_ios_amount_num), 0) as first_ios_amount_num,
                	IFNULL(SUM(first_ios_amount_count), 0) as first_ios_amount_count,
                	IFNULL(SUM(first_ios_amount), 0) as first_ios_amount,
                	round(if(SUM(first_new_user_amount_num) > 0, SUM(first_ios_amount_num) / SUM(first_new_user_amount_num), 0), 4) as first_ios_amount_num_rate,
                	round(if(SUM(first_new_user_amount) > 0, SUM(first_ios_amount) / SUM(first_new_user_amount), 0), 4) as first_ios_amount_rate,
                	round(if(SUM(today_cost)> 0, SUM(first_ios_amount) / SUM(today_cost), 0), 4) as first_ios_amount_roi,
                	IFNULL(SUM(first_android_amount_count), 0) as first_android_amount_count,
                	IFNULL(SUM(first_android_amount_num), 0) as first_android_amount_num,
                	IFNULL(SUM(first_android_amount), 0) as first_android_amount,
                	round(if(SUM(first_new_user_amount_num) > 0,SUM(first_android_amount_num) / SUM(first_new_user_amount_num), 0), 4) as first_android_amount_num_rate,
                	round(if(SUM(first_new_user_amount) > 0, SUM(first_android_amount) / SUM(first_new_user_amount), 0), 4) as first_android_amount_rate,
                	round(if(SUM(today_cost) > 0, SUM(first_android_amount) / SUM(today_cost), 0), 4) as first_android_amount_roi,
                	round(if(SUM(reg_num) > 0, SUM(first_new_user_amount_num) / SUM(reg_num), 0), 4) as first_amount_rate,
                	round(if(SUM(first_new_user_amount_count) > 0, SUM(first_new_user_amount) / SUM(first_new_user_amount_count), 0), 2) as first_new_user_avg_price,
                	round(if(SUM(new_user_total_amount_count) > 0, SUM(new_user_total_amount) / SUM(new_user_total_amount_count), 0), 2) as new_user_total_avg_price,
                	IFNULL(SUM(d1), 0) as d1,
                	IFNULL(SUM(d2), 0) as d2,
                	IFNULL(SUM(d3), 0) as d3,
                	IFNULL(SUM(d7), 0) as d7,
                	IFNULL(SUM(d15), 0) as d15,
                	IFNULL(SUM(mini_game_register_users), 0) as mini_game_register_users,
                    round(if(SUM(mini_game_register_users > 0), SUM(today_cost) / SUM(mini_game_register_users), 0), 2) as mini_game_register_cost,
                    ROUND(IF(SUM(click_count) > 0 , SUM(mini_game_register_users) / SUM(click_count), 0), 4) as mini_game_register_rate,
                    IFNULL(SUM(mini_game_paying_count), 0) as mini_game_paying_count,
                    IFNULL(SUM(mini_game_paying_amount), 0) as mini_game_paying_amount,
                    IFNULL(SUM(mini_game_paying_users_d1), 0) as mini_game_paying_users_d1,
                    IFNULL(SUM(mini_game_paying_amount_d1), 0) as mini_game_paying_amount_d1,
                    IFNULL(SUM(mini_game_paying_amount_d3), 0) as mini_game_paying_amount_d3,
                    IFNULL(SUM(mini_game_paying_amount_d7), 0) as mini_game_paying_amount_d7,
                    IFNULL(SUM(mini_game_paying_amount_d14), 0) as mini_game_paying_amount_d14,
                    IFNULL(SUM(mini_game_paying_amount_d30), 0) as mini_game_paying_amount_d30,
                    IFNULL(SUM(mini_game_first_paying_users), 0) as mini_game_first_paying_users,
                    IFNULL(SUM(mini_game_paying_roi), 0) as mini_game_paying_roi,
                    IFNULL(SUM(mini_game_paying_arpu), 0) as mini_game_paying_arpu,
                    IFNULL(SUM(mini_game_first_day_paying_roi), 0) as mini_game_first_day_paying_roi,
                    IFNULL(SUM(mini_game_pay_d3_roi), 0) as mini_game_pay_d3_roi,
                    IFNULL(SUM(mini_game_pay_d7_roi), 0) as mini_game_pay_d7_roi,
                    IFNULL(SUM(mini_game_pay_d14_roi), 0) as mini_game_pay_d14_roi,
                    IFNULL(SUM(mini_game_pay_d30_roi), 0) as mini_game_pay_d30_roi,
                    IFNULL(SUM(minigame1d_pay_count), 0) as minigame1d_pay_count,
                    IFNULL(SUM(minigame24h_pay_uv), 0) as minigame24h_pay_uv,
                    IFNULL(SUM(minigame24h_pay_amount), 0) as minigame24h_pay_amount,
                    IFNULL(SUM(minigame24h_pay_roi), 0) as minigame24h_pay_roi,
                    IFNULL(SUM(minigame24h_pay_arpu), 0) as minigame24h_pay_arpu,
                    ROUND(IF(SUM(reg_num) > 0, SUM(mini_game_paying_amount_d1) / SUM(reg_num), 0), 2) as ltv_day1,
                    ROUND(IF(SUM(reg_num) > 0, SUM(first_new_user_amount) / SUM(reg_num), 0), 2) as ltv_day1_actual_data,
                    (SUM(first_new_user_amount_count) - SUM(first_sub_amount_count)) as sub_order_num,
                    (SUM(first_new_user_amount) - SUM(first_sub_amount)) as sub_order_amount,
                    ROUND(IF(SUM(first_new_user_amount_count) > 0, SUM(today_cost) / SUM(first_new_user_amount_count), 0), 2) as first_new_user_amount_count_cost,
                    ROUND(IF(SUM(new_user_total_amount_count) > 0, SUM(today_cost) / SUM(new_user_total_amount_count), 0), 2) as new_user_total_amount_count_cost
                FROM
                	game_ads.ads_adgroup_day
                """;
    }

    /**
     * 用来查询出总计一栏的总数据sql(腾讯广告)
     *
     * @return String
     */
    private String tencentPromotionDayTotalTotalDataTempSql() {
        return """
                SELECT
                    MAX(promotion_total_cost) promotion_total_cost,
                    MAX(reg_total_num) reg_total_num,
                    MAX(role_total_num) role_total_num,
                    MAX(total_amount_count) total_amount_count,
                    MAX(total_amount_num) total_amount_num,
                    MAX(total_amount) total_amount
                FROM
                    game_ads.ads_adgroup_day
                """;
    }

    /**
     * 得到计算后的总数据(腾讯广告)
     *
     * @return String
     */
    private String tencentPromotionDayTotalTotalDataSumSql() {
        return """
                SELECT
                    IFNULL(SUM(promotion_total_cost), 0) promotion_total_cost,
                    IFNULL(SUM(reg_total_num), 0) reg_total_num,
                    IFNULL(SUM(role_total_num), 0) role_total_num,
                    IFNULL(SUM(total_amount_count), 0) total_amount_count,
                    IFNULL(SUM(total_amount_num), 0) total_amount_num,
                    IFNULL(SUM(total_amount), 0) total_amount
                FROM
                """;
    }

}
