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

import com.github.sd4324530.jtuple.Tuple2;
import com.google.common.base.CaseFormat;
import com.google.gson.Gson;
import com.zanxiang.game.data.serve.component.DataPowerComponent;
import com.zanxiang.game.data.serve.pojo.dto.*;
import com.zanxiang.game.data.serve.pojo.entity.AdsAccountAgentDay;
import com.zanxiang.game.data.serve.pojo.entity.AdsAccountAgentDayParent;
import com.zanxiang.game.data.serve.pojo.entity.AdsAgentDayAgain;
import com.zanxiang.game.data.serve.pojo.enums.OrderByEnum;
import com.zanxiang.game.data.serve.pojo.vo.*;
import com.zanxiang.game.data.serve.service.IAccountAgentDayService;
import com.zanxiang.game.data.serve.utils.Page;
import com.zanxiang.module.util.DateUtil;
import com.zanxiang.module.util.exception.BaseException;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.nutz.dao.Cnd;
import org.nutz.dao.Dao;
import org.nutz.dao.Sqls;
import org.nutz.dao.entity.Entity;
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.nutz.dao.util.cri.SimpleCriteria;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.time.LocalDate;
import java.util.*;
import java.util.concurrent.atomic.AtomicReference;
import java.util.stream.Collectors;

@Slf4j
@Service
public class AccountAgentDayServiceImpl implements IAccountAgentDayService {

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

    @Override
    public Page<GamePromoteDayVO> accountAgentDay(GamePromoteDayDTO dto) {
        Tuple2<List<Long>, List<Long>> poerInfo = dataPowerComponent.getPowerInfo(dto.getSourceSystem());
        List<Long> userIds = StringUtils.isBlank(dto.getSysUserId()) ? poerInfo.first : List.of(Long.valueOf(dto.getSysUserId()));
        List<Long> gameIds = dto.getGameId() == null ? poerInfo.second : List.of(dto.getGameId());

        if (null == dto.getBeginDay() || null == dto.getEndDay()) {
            dto.setBeginDay(LocalDate.now());
            dto.setEndDay(LocalDate.now());
        }
        if (dto.getGameDimension() == null) {
            //默认查询子游戏维度
            dto.setGameDimension(1L);
        }
        String gameColumn = "game_id";
        String gameNameColumn = "game_name";
        String gameTypeColumn = "game_type";
        Class<?> clazz = AdsAccountAgentDay.class;
        if (dto.getGameDimension() == 2L) {
            gameColumn = "parent_game_id";
            gameNameColumn = "parent_game_name";
            gameTypeColumn = "parent_game_type";
            clazz = AdsAccountAgentDayParent.class;
        }
        Pager pager = dao.createPager(dto.getPageNum(), dto.getPageSize());
        // 创建一个 Criteria 接口实例
        SimpleCriteria cri = Cnd.cri();
        // 组装条件
        if (CollectionUtils.isNotEmpty(userIds)) {
            cri.where().andInList("pitcher_id", userIds);
        }
        if (StringUtils.isNotBlank(dto.getAccountId())) {
            cri.where().andEquals("account_id", dto.getAccountId());
        }
        if (StringUtils.isNotBlank(dto.getAccountType())) {
            cri.where().andEquals("account_type", dto.getAccountType());
        }
        if (!CollectionUtils.isEmpty(dto.getAgentId())) {
            cri.where().andInList("agent_id", dto.getAgentId());
        }
        if (StringUtils.isNotBlank(dto.getCpName())) {
            cri.where().andLike("game_cp", dto.getCpName());
        }
        if (StringUtils.isNotBlank(dto.getGameName())) {
            cri.where().andLike(gameNameColumn, dto.getGameName());
        }
        if (CollectionUtils.isNotEmpty(gameIds)) {
            cri.where().andInList(gameColumn, gameIds);
        }
        if (StringUtils.isNotBlank(dto.getGameType())) {
            cri.where().andEquals(gameTypeColumn, dto.getGameType());
        }
        if (null != dto.getBeginDay()) {
            cri.where().and("dt", ">=", dto.getBeginDay());
        }
        if (null != dto.getEndDay()) {
            cri.where().and("dt", "<=", dto.getEndDay());
        }
        if (StringUtils.isNotBlank(dto.getSourceSystem())) {
            cri.where().andEquals("source_system", dto.getSourceSystem());
        }
        if (StringUtils.isBlank(dto.getSortType())) {
            dto.setSortType(OrderByEnum.DESC.getOrderType());
        }
        if (StringUtils.isBlank(dto.getSortFiled())) {
            cri.getOrderBy().orderBy("dt", dto.getSortType());
            cri.getOrderBy().orderBy("cost", dto.getSortType());
        } else {
            cri.orderBy(CaseFormat.LOWER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, dto.getSortFiled()), dto.getSortType());
        }
        Sql sql;
        if (dto.getGameDimension() == 1L) {
            sql = Sqls.queryEntity(accountAgentDaySql() + "$condition");
        } else {
            sql = Sqls.queryEntity(accountAgentDaySqlForParent() + "$condition");
        }
        sql.setPager(pager);
        Entity<GamePromoteDayVO> entity = dao.getEntity(GamePromoteDayVO.class);
        sql.setEntity(entity).setCondition(cri);
        dao.execute(sql);
        pager.setRecordCount(dao.count(clazz, cri));
        List<GamePromoteDayVO> collect = sql.getList(GamePromoteDayVO.class).stream().map(item -> {
            setDayRechargeTrend(item);
            return item;
        }).collect(Collectors.toList());
        return new Page<>(collect, pager);
    }

    private void setDayRechargeTrend(GamePromoteDayVO item) {
        item.setRechargeTrendDay1(getRechargeTrendVO(item.getTrendDay1()));
        item.setRechargeTrendDay2(getRechargeTrendVO(item.getTrendDay2()));
        item.setRechargeTrendDay3(getRechargeTrendVO(item.getTrendDay3()));
        item.setRechargeTrendDay4(getRechargeTrendVO(item.getTrendDay4()));
        item.setRechargeTrendDay5(getRechargeTrendVO(item.getTrendDay5()));
        item.setRechargeTrendDay6(getRechargeTrendVO(item.getTrendDay6()));
        item.setRechargeTrendDay7(getRechargeTrendVO(item.getTrendDay7()));
        item.setRechargeTrendDay8(getRechargeTrendVO(item.getTrendDay8()));
        item.setRechargeTrendDay9(getRechargeTrendVO(item.getTrendDay9()));

        item.setRechargeTrendDay10(getRechargeTrendVO(item.getTrendDay10()));
        item.setRechargeTrendDay11(getRechargeTrendVO(item.getTrendDay11()));
        item.setRechargeTrendDay12(getRechargeTrendVO(item.getTrendDay12()));
        item.setRechargeTrendDay13(getRechargeTrendVO(item.getTrendDay13()));
        item.setRechargeTrendDay14(getRechargeTrendVO(item.getTrendDay14()));
        item.setRechargeTrendDay15(getRechargeTrendVO(item.getTrendDay15()));
        item.setRechargeTrendDay16(getRechargeTrendVO(item.getTrendDay16()));
        item.setRechargeTrendDay17(getRechargeTrendVO(item.getTrendDay17()));
        item.setRechargeTrendDay18(getRechargeTrendVO(item.getTrendDay18()));
        item.setRechargeTrendDay19(getRechargeTrendVO(item.getTrendDay19()));

        item.setRechargeTrendDay20(getRechargeTrendVO(item.getTrendDay20()));
        item.setRechargeTrendDay21(getRechargeTrendVO(item.getTrendDay21()));
        item.setRechargeTrendDay22(getRechargeTrendVO(item.getTrendDay22()));
        item.setRechargeTrendDay23(getRechargeTrendVO(item.getTrendDay23()));
        item.setRechargeTrendDay24(getRechargeTrendVO(item.getTrendDay24()));
        item.setRechargeTrendDay25(getRechargeTrendVO(item.getTrendDay25()));
        item.setRechargeTrendDay26(getRechargeTrendVO(item.getTrendDay26()));
        item.setRechargeTrendDay27(getRechargeTrendVO(item.getTrendDay27()));
        item.setRechargeTrendDay28(getRechargeTrendVO(item.getTrendDay28()));
        item.setRechargeTrendDay29(getRechargeTrendVO(item.getTrendDay29()));

        item.setRechargeTrendDay30(getRechargeTrendVO(item.getTrendDay30()));
        item.setRechargeTrendDay31(getRechargeTrendVO(item.getTrendDay31()));
        item.setRechargeTrendDay32(getRechargeTrendVO(item.getTrendDay32()));
        item.setRechargeTrendDay33(getRechargeTrendVO(item.getTrendDay33()));
        item.setRechargeTrendDay34(getRechargeTrendVO(item.getTrendDay34()));
        item.setRechargeTrendDay35(getRechargeTrendVO(item.getTrendDay35()));
        item.setRechargeTrendDay36(getRechargeTrendVO(item.getTrendDay36()));
        item.setRechargeTrendDay37(getRechargeTrendVO(item.getTrendDay37()));
        item.setRechargeTrendDay38(getRechargeTrendVO(item.getTrendDay38()));
        item.setRechargeTrendDay39(getRechargeTrendVO(item.getTrendDay39()));

        item.setRechargeTrendDay40(getRechargeTrendVO(item.getTrendDay40()));
        item.setRechargeTrendDay41(getRechargeTrendVO(item.getTrendDay41()));
        item.setRechargeTrendDay42(getRechargeTrendVO(item.getTrendDay42()));
        item.setRechargeTrendDay43(getRechargeTrendVO(item.getTrendDay43()));
        item.setRechargeTrendDay44(getRechargeTrendVO(item.getTrendDay44()));
        item.setRechargeTrendDay45(getRechargeTrendVO(item.getTrendDay45()));
        item.setRechargeTrendDay46(getRechargeTrendVO(item.getTrendDay46()));
        item.setRechargeTrendDay47(getRechargeTrendVO(item.getTrendDay47()));
        item.setRechargeTrendDay48(getRechargeTrendVO(item.getTrendDay48()));
        item.setRechargeTrendDay49(getRechargeTrendVO(item.getTrendDay49()));

        item.setRechargeTrendDay50(getRechargeTrendVO(item.getTrendDay50()));
        item.setRechargeTrendDay51(getRechargeTrendVO(item.getTrendDay51()));
        item.setRechargeTrendDay52(getRechargeTrendVO(item.getTrendDay52()));
        item.setRechargeTrendDay53(getRechargeTrendVO(item.getTrendDay53()));
        item.setRechargeTrendDay54(getRechargeTrendVO(item.getTrendDay54()));
        item.setRechargeTrendDay55(getRechargeTrendVO(item.getTrendDay55()));
        item.setRechargeTrendDay56(getRechargeTrendVO(item.getTrendDay56()));
        item.setRechargeTrendDay57(getRechargeTrendVO(item.getTrendDay57()));
        item.setRechargeTrendDay58(getRechargeTrendVO(item.getTrendDay58()));
        item.setRechargeTrendDay59(getRechargeTrendVO(item.getTrendDay59()));

        item.setRechargeTrendDay60(getRechargeTrendVO(item.getTrendDay60()));
        item.setRechargeTrendDay61(getRechargeTrendVO(item.getTrendDay61()));
        item.setRechargeTrendDay62(getRechargeTrendVO(item.getTrendDay62()));
        item.setRechargeTrendDay63(getRechargeTrendVO(item.getTrendDay63()));
        item.setRechargeTrendDay64(getRechargeTrendVO(item.getTrendDay64()));
        item.setRechargeTrendDay65(getRechargeTrendVO(item.getTrendDay65()));
        item.setRechargeTrendDay66(getRechargeTrendVO(item.getTrendDay66()));
        item.setRechargeTrendDay67(getRechargeTrendVO(item.getTrendDay67()));
        item.setRechargeTrendDay68(getRechargeTrendVO(item.getTrendDay68()));
        item.setRechargeTrendDay69(getRechargeTrendVO(item.getTrendDay69()));

        item.setRechargeTrendDay70(getRechargeTrendVO(item.getTrendDay70()));
        item.setRechargeTrendDay71(getRechargeTrendVO(item.getTrendDay71()));
        item.setRechargeTrendDay72(getRechargeTrendVO(item.getTrendDay72()));
        item.setRechargeTrendDay73(getRechargeTrendVO(item.getTrendDay73()));
        item.setRechargeTrendDay74(getRechargeTrendVO(item.getTrendDay74()));
        item.setRechargeTrendDay75(getRechargeTrendVO(item.getTrendDay75()));
        item.setRechargeTrendDay76(getRechargeTrendVO(item.getTrendDay76()));
        item.setRechargeTrendDay77(getRechargeTrendVO(item.getTrendDay77()));
        item.setRechargeTrendDay78(getRechargeTrendVO(item.getTrendDay78()));
        item.setRechargeTrendDay79(getRechargeTrendVO(item.getTrendDay79()));

        item.setRechargeTrendDay80(getRechargeTrendVO(item.getTrendDay80()));
        item.setRechargeTrendDay81(getRechargeTrendVO(item.getTrendDay81()));
        item.setRechargeTrendDay82(getRechargeTrendVO(item.getTrendDay82()));
        item.setRechargeTrendDay83(getRechargeTrendVO(item.getTrendDay83()));
        item.setRechargeTrendDay84(getRechargeTrendVO(item.getTrendDay84()));
        item.setRechargeTrendDay85(getRechargeTrendVO(item.getTrendDay85()));
        item.setRechargeTrendDay86(getRechargeTrendVO(item.getTrendDay86()));
        item.setRechargeTrendDay87(getRechargeTrendVO(item.getTrendDay87()));
        item.setRechargeTrendDay88(getRechargeTrendVO(item.getTrendDay88()));
        item.setRechargeTrendDay89(getRechargeTrendVO(item.getTrendDay89()));
        item.setRechargeTrendDay90(getRechargeTrendVO(item.getTrendDay90()));

        item.setRechargeTrendMonth4(getRechargeTrendVO(item.getTrendMonth4()));
        item.setRechargeTrendMonth5(getRechargeTrendVO(item.getTrendMonth5()));
        item.setRechargeTrendMonth6(getRechargeTrendVO(item.getTrendMonth6()));
        item.setRechargeTrendMonth7(getRechargeTrendVO(item.getTrendMonth7()));
        item.setRechargeTrendMonth8(getRechargeTrendVO(item.getTrendMonth8()));
        item.setRechargeTrendMonth9(getRechargeTrendVO(item.getTrendMonth9()));
        item.setRechargeTrendMonth10(getRechargeTrendVO(item.getTrendMonth10()));
        item.setRechargeTrendMonth11(getRechargeTrendVO(item.getTrendMonth11()));
        item.setRechargeTrendMonth12(getRechargeTrendVO(item.getTrendMonth12()));
        item.setRechargeTrendTotal(getRechargeTrendVO(item.getTrendTotal()));
    }

    private RechargeTrendVO getRechargeTrendVO(String trendDay) {
        String[] trendDayResult = trendDay.split("/");
        return RechargeTrendVO.builder()
                .rechargeMoney(new BigDecimal(trendDayResult[0]))
                .rechargeUserCount(Long.valueOf(trendDayResult[1]))
                .increase(new BigDecimal(trendDayResult[2]))
                .back(new BigDecimal(trendDayResult[3]))
                .multiples(new BigDecimal(trendDayResult[4]))
                .build();
    }

    @Override
    public GamePromoteDayTotalVO accountAgentDayTotal(GamePromoteDayTotalDTO dto) {
        Tuple2<List<Long>, List<Long>> poerInfo = dataPowerComponent.getPowerInfo(dto.getSourceSystem());
        List<Long> userIds = StringUtils.isBlank(dto.getSysUserId()) ? poerInfo.first : List.of(Long.valueOf(dto.getSysUserId()));
        List<Long> gameIds = dto.getGameId() == null ? poerInfo.second : List.of(dto.getGameId());

        if (null == dto.getBeginDay() || null == dto.getEndDay()) {
            dto.setBeginDay(LocalDate.now());
            dto.setEndDay(LocalDate.now());
        }
        if (dto.getGameDimension() == null) {
            //默认查询子游戏维度
            dto.setGameDimension(1L);
        }
        String gameColumn = "game_id";
        String gameNameColumn = "game_name";
        String gameTypeColumn = "game_type";
        if (dto.getGameDimension() == 2L) {
            gameColumn = "parent_game_id";
            gameNameColumn = "parent_game_name";
            gameTypeColumn = "parent_game_type";
        }
        // 创建一个 Criteria 接口实例
        SimpleCriteria cri = Cnd.cri();
        // 组装条件
        if (CollectionUtils.isNotEmpty(userIds)) {
            cri.where().andInList("pitcher_id", userIds);
        }
        if (StringUtils.isNotBlank(dto.getAccountId())) {
            cri.where().andEquals("account_id", dto.getAccountId());
        }
        if (StringUtils.isNotBlank(dto.getAccountType())) {
            cri.where().andEquals("account_type", dto.getAccountType());
        }
        if (!CollectionUtils.isEmpty(dto.getAgentId())) {
            cri.where().andInList("agent_id", dto.getAgentId());
        }
        if (StringUtils.isNotBlank(dto.getCpName())) {
            cri.where().andLike("game_cp", dto.getCpName());
        }
        if (StringUtils.isNotBlank(dto.getGameName())) {
            cri.where().andLike(gameNameColumn, dto.getGameName());
        }
        if (StringUtils.isNotBlank(dto.getGameType())) {
            cri.where().andEquals(gameTypeColumn, dto.getGameType());
        }
        if (CollectionUtils.isNotEmpty(gameIds)) {
            cri.where().andInList(gameColumn, gameIds);
        }
        if (null != dto.getBeginDay()) {
            cri.where().and("dt", ">=", dto.getBeginDay());
        }
        if (null != dto.getEndDay()) {
            cri.where().and("dt", "<=", dto.getEndDay());
        }
        if (StringUtils.isNotBlank(dto.getSourceSystem())) {
            cri.where().andEquals("source_system", dto.getSourceSystem());
        }
        Sql sql = Sqls.queryEntity(accountAgentDayTotalSql() + "$condition");
        Entity<GamePromoteDayTotalVO> entity = dao.getEntity(GamePromoteDayTotalVO.class);
        sql.setEntity(entity).setCondition(cri);
        dao.execute(sql);
        GamePromoteDayTotalVO gamePromoteDayTotalVO = sql.getObject(GamePromoteDayTotalVO.class);
        if (null == gamePromoteDayTotalVO.getCost()) {
            return gamePromoteDayTotalVO;
        }
        setDayTotalRechargeTrend(gamePromoteDayTotalVO);
        return gamePromoteDayTotalVO;
    }

    private void setDayTotalRechargeTrend(GamePromoteDayTotalVO item) {
        BigDecimal trendDay1Money = new BigDecimal(item.getTrendDay1().split("/")[0]);
        item.setRechargeTrendDay1(getSumRechargeTrendVO(item.getTrendDay1()));
        item.setRechargeTrendDay2(getSumRechargeTrendVO(item.getTrendDay2()));
        item.setRechargeTrendDay3(getSumRechargeTrendVO(item.getTrendDay3()));
        item.setRechargeTrendDay4(getSumRechargeTrendVO(item.getTrendDay4()));
        item.setRechargeTrendDay5(getSumRechargeTrendVO(item.getTrendDay5()));
        item.setRechargeTrendDay6(getSumRechargeTrendVO(item.getTrendDay6()));
        item.setRechargeTrendDay7(getSumRechargeTrendVO(item.getTrendDay7()));
        item.setRechargeTrendDay8(getSumRechargeTrendVO(item.getTrendDay8()));
        item.setRechargeTrendDay9(getSumRechargeTrendVO(item.getTrendDay9()));

        item.setRechargeTrendDay10(getSumRechargeTrendVO(item.getTrendDay10()));
        item.setRechargeTrendDay11(getSumRechargeTrendVO(item.getTrendDay11()));
        item.setRechargeTrendDay12(getSumRechargeTrendVO(item.getTrendDay12()));
        item.setRechargeTrendDay13(getSumRechargeTrendVO(item.getTrendDay13()));
        item.setRechargeTrendDay14(getSumRechargeTrendVO(item.getTrendDay14()));
        item.setRechargeTrendDay15(getSumRechargeTrendVO(item.getTrendDay15()));
        item.setRechargeTrendDay16(getSumRechargeTrendVO(item.getTrendDay16()));
        item.setRechargeTrendDay17(getSumRechargeTrendVO(item.getTrendDay17()));
        item.setRechargeTrendDay18(getSumRechargeTrendVO(item.getTrendDay18()));
        item.setRechargeTrendDay19(getSumRechargeTrendVO(item.getTrendDay19()));

        item.setRechargeTrendDay20(getSumRechargeTrendVO(item.getTrendDay20()));
        item.setRechargeTrendDay21(getSumRechargeTrendVO(item.getTrendDay21()));
        item.setRechargeTrendDay22(getSumRechargeTrendVO(item.getTrendDay22()));
        item.setRechargeTrendDay23(getSumRechargeTrendVO(item.getTrendDay23()));
        item.setRechargeTrendDay24(getSumRechargeTrendVO(item.getTrendDay24()));
        item.setRechargeTrendDay25(getSumRechargeTrendVO(item.getTrendDay25()));
        item.setRechargeTrendDay26(getSumRechargeTrendVO(item.getTrendDay26()));
        item.setRechargeTrendDay27(getSumRechargeTrendVO(item.getTrendDay27()));
        item.setRechargeTrendDay28(getSumRechargeTrendVO(item.getTrendDay28()));
        item.setRechargeTrendDay29(getSumRechargeTrendVO(item.getTrendDay29()));

        item.setRechargeTrendDay30(getSumRechargeTrendVO(item.getTrendDay30()));
        item.setRechargeTrendDay31(getSumRechargeTrendVO(item.getTrendDay31()));
        item.setRechargeTrendDay32(getSumRechargeTrendVO(item.getTrendDay32()));
        item.setRechargeTrendDay33(getSumRechargeTrendVO(item.getTrendDay33()));
        item.setRechargeTrendDay34(getSumRechargeTrendVO(item.getTrendDay34()));
        item.setRechargeTrendDay35(getSumRechargeTrendVO(item.getTrendDay35()));
        item.setRechargeTrendDay36(getSumRechargeTrendVO(item.getTrendDay36()));
        item.setRechargeTrendDay37(getSumRechargeTrendVO(item.getTrendDay37()));
        item.setRechargeTrendDay38(getSumRechargeTrendVO(item.getTrendDay38()));
        item.setRechargeTrendDay39(getSumRechargeTrendVO(item.getTrendDay39()));

        item.setRechargeTrendDay40(getSumRechargeTrendVO(item.getTrendDay40()));
        item.setRechargeTrendDay41(getSumRechargeTrendVO(item.getTrendDay41()));
        item.setRechargeTrendDay42(getSumRechargeTrendVO(item.getTrendDay42()));
        item.setRechargeTrendDay43(getSumRechargeTrendVO(item.getTrendDay43()));
        item.setRechargeTrendDay44(getSumRechargeTrendVO(item.getTrendDay44()));
        item.setRechargeTrendDay45(getSumRechargeTrendVO(item.getTrendDay45()));
        item.setRechargeTrendDay46(getSumRechargeTrendVO(item.getTrendDay46()));
        item.setRechargeTrendDay47(getSumRechargeTrendVO(item.getTrendDay47()));
        item.setRechargeTrendDay48(getSumRechargeTrendVO(item.getTrendDay48()));
        item.setRechargeTrendDay49(getSumRechargeTrendVO(item.getTrendDay49()));

        item.setRechargeTrendDay50(getSumRechargeTrendVO(item.getTrendDay50()));
        item.setRechargeTrendDay51(getSumRechargeTrendVO(item.getTrendDay51()));
        item.setRechargeTrendDay52(getSumRechargeTrendVO(item.getTrendDay52()));
        item.setRechargeTrendDay53(getSumRechargeTrendVO(item.getTrendDay53()));
        item.setRechargeTrendDay54(getSumRechargeTrendVO(item.getTrendDay54()));
        item.setRechargeTrendDay55(getSumRechargeTrendVO(item.getTrendDay55()));
        item.setRechargeTrendDay56(getSumRechargeTrendVO(item.getTrendDay56()));
        item.setRechargeTrendDay57(getSumRechargeTrendVO(item.getTrendDay57()));
        item.setRechargeTrendDay58(getSumRechargeTrendVO(item.getTrendDay58()));
        item.setRechargeTrendDay59(getSumRechargeTrendVO(item.getTrendDay59()));

        item.setRechargeTrendDay60(getSumRechargeTrendVO(item.getTrendDay60()));
        item.setRechargeTrendDay61(getSumRechargeTrendVO(item.getTrendDay61()));
        item.setRechargeTrendDay62(getSumRechargeTrendVO(item.getTrendDay62()));
        item.setRechargeTrendDay63(getSumRechargeTrendVO(item.getTrendDay63()));
        item.setRechargeTrendDay64(getSumRechargeTrendVO(item.getTrendDay64()));
        item.setRechargeTrendDay65(getSumRechargeTrendVO(item.getTrendDay65()));
        item.setRechargeTrendDay66(getSumRechargeTrendVO(item.getTrendDay66()));
        item.setRechargeTrendDay67(getSumRechargeTrendVO(item.getTrendDay67()));
        item.setRechargeTrendDay68(getSumRechargeTrendVO(item.getTrendDay68()));
        item.setRechargeTrendDay69(getSumRechargeTrendVO(item.getTrendDay69()));

        item.setRechargeTrendDay70(getSumRechargeTrendVO(item.getTrendDay70()));
        item.setRechargeTrendDay71(getSumRechargeTrendVO(item.getTrendDay71()));
        item.setRechargeTrendDay72(getSumRechargeTrendVO(item.getTrendDay72()));
        item.setRechargeTrendDay73(getSumRechargeTrendVO(item.getTrendDay73()));
        item.setRechargeTrendDay74(getSumRechargeTrendVO(item.getTrendDay74()));
        item.setRechargeTrendDay75(getSumRechargeTrendVO(item.getTrendDay75()));
        item.setRechargeTrendDay76(getSumRechargeTrendVO(item.getTrendDay76()));
        item.setRechargeTrendDay77(getSumRechargeTrendVO(item.getTrendDay77()));
        item.setRechargeTrendDay78(getSumRechargeTrendVO(item.getTrendDay78()));
        item.setRechargeTrendDay79(getSumRechargeTrendVO(item.getTrendDay79()));

        item.setRechargeTrendDay80(getSumRechargeTrendVO(item.getTrendDay80()));
        item.setRechargeTrendDay81(getSumRechargeTrendVO(item.getTrendDay81()));
        item.setRechargeTrendDay82(getSumRechargeTrendVO(item.getTrendDay82()));
        item.setRechargeTrendDay83(getSumRechargeTrendVO(item.getTrendDay83()));
        item.setRechargeTrendDay84(getSumRechargeTrendVO(item.getTrendDay84()));
        item.setRechargeTrendDay85(getSumRechargeTrendVO(item.getTrendDay85()));
        item.setRechargeTrendDay86(getSumRechargeTrendVO(item.getTrendDay86()));
        item.setRechargeTrendDay87(getSumRechargeTrendVO(item.getTrendDay87()));
        item.setRechargeTrendDay88(getSumRechargeTrendVO(item.getTrendDay88()));
        item.setRechargeTrendDay89(getSumRechargeTrendVO(item.getTrendDay89()));
        item.setRechargeTrendDay90(getSumRechargeTrendVO(item.getTrendDay90()));

        item.setRechargeTrendMonth4(getSumRechargeTrendVO(item.getTrendMonth4()));
        item.setRechargeTrendMonth5(getSumRechargeTrendVO(item.getTrendMonth5()));
        item.setRechargeTrendMonth6(getSumRechargeTrendVO(item.getTrendMonth6()));
        item.setRechargeTrendMonth7(getSumRechargeTrendVO(item.getTrendMonth7()));
        item.setRechargeTrendMonth8(getSumRechargeTrendVO(item.getTrendMonth8()));
        item.setRechargeTrendMonth9(getSumRechargeTrendVO(item.getTrendMonth9()));
        item.setRechargeTrendMonth10(getSumRechargeTrendVO(item.getTrendMonth10()));
        item.setRechargeTrendMonth11(getSumRechargeTrendVO(item.getTrendMonth11()));
        item.setRechargeTrendMonth12(getSumRechargeTrendVO(item.getTrendMonth12()));

        String[] trendDayResult = item.getTrendTotal().split("/");
        BigDecimal dayTotalRecharge = new BigDecimal(trendDayResult[1]);
        BigDecimal dayRecharge = new BigDecimal(trendDayResult[0]);
        BigDecimal cost = item.getCost();
        item.setRechargeTrendTotal(RechargeTrendVO.builder()
                .rechargeMoney(new BigDecimal(trendDayResult[0]))
                .rechargeUserCount(Long.valueOf(trendDayResult[2]))
                .rechargeMoneyTotal(dayTotalRecharge)
                .increase(cost.compareTo(BigDecimal.ZERO) == 0 ?
                        BigDecimal.ZERO : dayRecharge.divide(cost, 4, RoundingMode.DOWN))
                .back(cost.compareTo(BigDecimal.ZERO) == 0 ?
                        BigDecimal.ZERO : dayTotalRecharge.divide(cost, 4, RoundingMode.DOWN))
                .multiples(trendDay1Money.compareTo(BigDecimal.ZERO) == 0 ?
                        BigDecimal.ZERO : dayTotalRecharge.divide(trendDay1Money, 4, RoundingMode.DOWN))
                .build());
    }

    private RechargeTrendVO getSumRechargeTrendVO(String trendDay) {
        String[] trendDayResult = trendDay.split("/");
        BigDecimal dayTotalRecharge = new BigDecimal(trendDayResult[1]);
        BigDecimal dayRecharge = new BigDecimal(trendDayResult[0]);
        BigDecimal currCost = new BigDecimal(trendDayResult[3]);
        BigDecimal currDay1 = new BigDecimal(trendDayResult[4]);
        return RechargeTrendVO.builder()
                .rechargeMoney(dayRecharge)
                .rechargeUserCount(Long.valueOf(trendDayResult[2]))
                .rechargeMoneyTotal(dayTotalRecharge)
                .increase(currCost.compareTo(BigDecimal.ZERO) == 0 ?
                        BigDecimal.ZERO : dayRecharge.divide(currCost, 4, RoundingMode.DOWN))
                .back(currCost.compareTo(BigDecimal.ZERO) == 0 ?
                        BigDecimal.ZERO : dayTotalRecharge.divide(currCost, 4, RoundingMode.DOWN))
                .multiples(currDay1.compareTo(BigDecimal.ZERO) == 0 ?
                        BigDecimal.ZERO : dayTotalRecharge.divide(currDay1, 4, RoundingMode.DOWN))
                .build();
    }

    @Override
    public Page<GamePromoteTotalVO> accountAgentTotal(GamePromoteTotalDTO dto) {
        Tuple2<List<Long>, List<Long>> poerInfo = dataPowerComponent.getPowerInfo(dto.getSourceSystem());
        List<Long> userIds = dto.getSysUserId() == null ? poerInfo.first : Collections.singletonList(dto.getSysUserId());
        List<Long> gameIds = dto.getGameId() == null ? poerInfo.second : Collections.singletonList(dto.getGameId());
        /*Long sysUserId = SecurityUtil.isAdmin() ? null : SecurityUtil.getUserId();
        sysUserId = null == dto.getSysUserId() ? sysUserId : dto.getSysUserId();
        dto.setSysUserId(sysUserId);*/
        if (StringUtils.isBlank(dto.getSortFiled())) {
            dto.setSortFiled("cost");
        }
        if (StringUtils.isBlank(dto.getSortType())) {
            dto.setSortType(OrderByEnum.DESC.getOrderType());
        }
        if (null == dto.getCostEndDay() || null == dto.getCostBeginDay()) {
            dto.setCostBeginDay(LocalDate.now());
            dto.setCostEndDay(LocalDate.now());
        }
        if (null == dto.getRechargeEndDay() || null == dto.getRechargeBeginDay()) {
            dto.setRechargeEndDay(LocalDate.now());
            dto.setRechargeBeginDay(LocalDate.now());
        }
        Pager pager = dao.createPager(dto.getPageNum(), dto.getPageSize());
        // 创建一个 Criteria 接口实例
        SimpleCriteria cri = Cnd.cri();
        // 组装条件
        if (userIds != null) {
            cri.where().andInList("a.pitcher_id", userIds);
        }
        if (StringUtils.isNotBlank(dto.getAccountId())) {
            cri.where().andEquals("a.account_id", dto.getAccountId());
        }
        if (StringUtils.isNotBlank(dto.getAccountType())) {
            cri.where().andEquals("account_type", dto.getAccountType());
        }
        if (!CollectionUtils.isEmpty(dto.getAgentId())) {
            cri.where().andInList("agent_id", dto.getAgentId());
        }
        if (StringUtils.isNotBlank(dto.getCpName())) {
            cri.where().andLike("a.game_cp", dto.getCpName());
        }
        if (StringUtils.isNotBlank(dto.getGameName())) {
            cri.where().andLike("a.game_name", dto.getGameName());
        }
        if (StringUtils.isNotBlank(dto.getGameType())) {
            cri.where().andEquals("a.game_type", dto.getGameType());
        }
        if (null != dto.getCostBeginDay()) {
            cri.where().and("a.dt", ">=", dto.getCostBeginDay());
        }
        if (null != dto.getCostEndDay()) {
            cri.where().and("a.dt", "<=", dto.getCostEndDay());
        }
        if (null != gameIds) {
            cri.where().andInList("a.game_id", gameIds);
        }
        if (StringUtils.isNotBlank(dto.getSourceSystem())) {
            cri.where().andEquals("a.source_system", dto.getSourceSystem());
        }
        cri.groupBy("a.account_id,a.agent_id,a.source_system");
        cri.orderBy(CaseFormat.LOWER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, dto.getSortFiled()), dto.getSortType());
        Sql sql = Sqls.queryEntity(accountAgentTotalSql() + "$condition");
        sql.setPager(pager);
        Entity<GamePromoteTotalVO> entity = dao.getEntity(GamePromoteTotalVO.class);
        sql.setEntity(entity).setCondition(cri);
        dao.execute(sql);
        Sql sqlCount = Sqls.queryEntity("select count(*) from ads_account_agent_day a " + "$condition");
        sqlCount.setCondition(cri);
        pager.setRecordCount((int) Daos.queryCount(dao, sqlCount));
        List<GamePromoteTotalVO> list = sql.getList(GamePromoteTotalVO.class);

        //渠道注册时间内的dayn充值数据，获取表ads_dayn_amount的json数据
        Map<String, Object> dayNMap = new HashMap<>(4);
        dayNMap.put("beginDay", dto.getCostBeginDay());
        dayNMap.put("endDay", dto.getCostEndDay());

        String show = """
                select
                ifnull(sum(a.amount_count), 0) as show_recharge_count,
                (select count(distinct user_id) from ads_information
                where
                source_system = a.source_system
                and agent_id = a.agent_id
                and account_id = a.account_id
                and order_time >= @rechargeBeginDay
                and order_time <= @rechargeEndDay) as show_recharge_user,
                ifnull(sum(a.amount), 0) as show_recharge_money,
                round(if(sum(a.amount_count) > 0, sum(a.amount) / sum(a.amount_count), 0), 4) as avg_show_user_recharge
                from
                ads_account_agent_day a
                where
                a.source_system = @sourceSystem
                and a.agent_id = @agentId
                and a.account_id = @accountId
                and a.dt >= @rechargeBeginDay
                and a.dt <= @rechargeEndDay
                group by
                a.account_id,
                a.agent_id,
                a.source_system
                """;
        Sql showSql = Sqls.queryEntity(show);
        Entity<ShowRecharge> showEntity = dao.getEntity(ShowRecharge.class);
        showSql.setEntity(showEntity);
        showSql.setParam("rechargeBeginDay", dto.getRechargeBeginDay());
        showSql.setParam("rechargeEndDay", dto.getRechargeEndDay());

        Map<String, Object> newUserNumMap = new HashMap<>(7);
        newUserNumMap.put("costBeginDay", dto.getCostBeginDay());
        newUserNumMap.put("costEndDay", dto.getCostEndDay());
        newUserNumMap.put("rechargeBeginDay", dto.getRechargeBeginDay());
        newUserNumMap.put("rechargeEndDay", dto.getRechargeEndDay());
        Sql newUserNum = Sqls.fetchLong("""
                select
                	count(DISTINCT user_id) as repeatNum
                from
                	ads_information
                where
                    source_system = @sourceSystem
                    and agent_id = @agentId
                    and account_id = @accountId
                    and order_time >= @rechargeBeginDay
                    and order_time <= @rechargeEndDay
                    and reg_time >= @costBeginDay
                    and reg_time <= @costEndDay
                """);

        Sql newUserRepeatNum = Sqls.fetchLong("""
                SELECT count(*)
                FROM
                (select
                	count(user_id) as repeatNum
                from
                	ads_information
                where
                    source_system = @sourceSystem
                    and agent_id = @agentId
                    and account_id = @accountId
                    and order_time >= @rechargeBeginDay
                    and order_time <= @rechargeEndDay
                    and reg_time >= @costBeginDay
                    and reg_time <= @costEndDay
                    group by user_id) a
                WHERE a.repeatNum > 1
                """);

        Sql roleUserNum = Sqls.fetchLong("""
                select
                	count(DISTINCT role_user_id)
                from
                	dw_create_role_detail
                where
                    source_system = @sourceSystem
                    and user_agent_id = @agentId
                    and user_account_id = @accountId
                    and DATE(role_create_time) >= @rechargeBeginDay
                    and DATE(role_create_time) <= @rechargeEndDay
                    and user_dt >= @costBeginDay
                    and user_dt <= @costEndDay
                """);
        //循环总数据的每条数据
        List<GamePromoteTotalVO> hasRechargeDayList = list.stream().map(item -> {
            showSql.setParam("sourceSystem", item.getSourceSystem());
            showSql.setParam("agentId", item.getAgentId());
            showSql.setParam("accountId", item.getAccountId());
            dao.execute(showSql);
            ShowRecharge showRecharge = showSql.getObject(ShowRecharge.class);
            if (null != showRecharge) {
                item.setShowRechargeArpu(showRecharge.getShowRechargeUser() == 0 ? BigDecimal.ZERO :
                        showRecharge.getShowRechargeMoney().divide(new BigDecimal(showRecharge.getShowRechargeUser()), 4, RoundingMode.DOWN));
                item.setShowRechargeCount(showRecharge.getShowRechargeCount());
                item.setShowRechargeMoney(showRecharge.getShowRechargeMoney());
                item.setShowRechargeUser(showRecharge.getShowRechargeUser());
                item.setAvgShowUserRecharge(showRecharge.getAvgShowUserRecharge());
            } else {
                item.setShowRechargeArpu(BigDecimal.ZERO);
                item.setShowRechargeCount(0L);
                item.setShowRechargeMoney(BigDecimal.ZERO);
                item.setShowRechargeUser(0L);
                item.setAvgShowUserRecharge(BigDecimal.ZERO);
            }
            Sql sqlDayN = Sqls.queryString("""
                    select day_n as dayN
                    from ads_dayn_amount
                    where
                    dt>=@beginDay and dt<=@endDay
                    and account_id = @accountId
                    and agent_id = @agentId
                    and source_system = @sourceSystem
                    """);
            dayNMap.put("accountId", item.getAccountId());
            dayNMap.put("agentId", item.getAgentId());
            dayNMap.put("sourceSystem", item.getSourceSystem());
            sqlDayN.setParams(dayNMap);
            dao.execute(sqlDayN);
            List<String> dayNList = sqlDayN.getList(String.class);
            DayN dayN = null;
            //ads_dayn_amount存在充值数据
            newUserNumMap.put("accountId", item.getAccountId());
            newUserNumMap.put("agentId", item.getAgentId());
            newUserNumMap.put("sourceSystem", item.getSourceSystem());
            roleUserNum.setParams(newUserNumMap);
            dao.execute(roleUserNum);
            Long roleUserNumLong = roleUserNum.getLong();
            if (!CollectionUtils.isEmpty(dayNList)) {
                Gson gson = new Gson();
                List<Map<String, String>> jsonList = gson.fromJson(dayNList.toString(), ArrayList.class);

                Map<String, String> resultMap = new HashMap<>();
                resultMap.put("dayN", "0.00-0-0-0");
                for (Map<String, String> jsonMap : jsonList) {
                    for (Map.Entry<String, String> entry : jsonMap.entrySet()) {
                        String key = entry.getKey();
                        String value = entry.getValue();
                        String[] parts = value.split("-");
                        if (DateUtil.parseLocalDate(key).compareTo(dto.getRechargeBeginDay()) >= 0
                                && DateUtil.parseLocalDate(key).compareTo(dto.getRechargeEndDay()) <= 0) {
                            String[] existingParts = resultMap.get("dayN").split("-");
                            BigDecimal money = new BigDecimal(existingParts[0]).add(new BigDecimal(parts[0]));
                            int count = Integer.parseInt(existingParts[1]) + Integer.parseInt(parts[1]);
                            int num = Integer.parseInt(existingParts[2]) + Integer.parseInt(parts[2]);
                            int repeatNum = Integer.parseInt(existingParts[3]) + Integer.parseInt(parts[3]);
                            String newValue = money + "-" + count + "-" + num + "-" + repeatNum;
                            resultMap.put("dayN", newValue);
                        }
                    }
                }


                newUserNum.setParams(newUserNumMap);
                dao.execute(newUserNum);
                Long newUserNumLong = newUserNum.getLong();

                newUserRepeatNum.setParams(newUserNumMap);
                dao.execute(newUserRepeatNum);
                Long newUserRepeatNumLong = newUserRepeatNum.getLong();
                if (!resultMap.isEmpty() && !resultMap.get("dayN").isEmpty()) {
                    String[] parts = resultMap.get("dayN").split("-");
                    dayN = DayN.builder()
                            .rechargeMoney(new BigDecimal(parts[0]))
                            .rechargeCount(Long.valueOf(parts[1]))
                            .rechargeNum(newUserNumLong)
                            .repeatNum(newUserRepeatNumLong)
                            .build();
                }
            }
            item.setRoleNum(roleUserNumLong);
            //充值时间范围内存在充值数据
            if (null != dayN) {
                item.setUserRechargeMoney(dayN.getRechargeMoney());
                item.setUserRechargeCount(dayN.getRechargeCount());
                item.setUserRechargeUser(dayN.getRechargeNum());
                item.setRepeatRechargeUser(dayN.getRepeatNum());
                item.setUserRechargeArpu(item.getUserRechargeUser() == 0 ?
                        BigDecimal.ZERO : item.getUserRechargeMoney().divide(BigDecimal.valueOf(item.getUserRechargeUser()), 4, RoundingMode.DOWN));

                item.setOldUserRechargeMoney(item.getShowRechargeMoney().subtract(item.getUserRechargeMoney()));
                item.setOldUserRechargeCount(item.getShowRechargeCount() - item.getUserRechargeCount());
                item.setOldUserRechargeUser(item.getShowRechargeUser() - item.getUserRechargeUser());

                item.setRecoveryRate(item.getCost().compareTo(BigDecimal.ZERO) == 0 ?
                        BigDecimal.ZERO : item.getUserRechargeMoney().divide(item.getCost(), 4, RoundingMode.DOWN));

                item.setUserRechargeRate(item.getRegUserCount() == 0 ?
                        BigDecimal.ZERO : BigDecimal.valueOf(item.getUserRechargeUser()).divide(BigDecimal.valueOf(item.getRegUserCount()), 4, RoundingMode.DOWN));

                item.setNewUserRechargeRatio(item.getShowRechargeUser() == 0 ?
                        BigDecimal.ZERO : BigDecimal.valueOf(item.getUserRechargeUser()).divide(BigDecimal.valueOf(item.getShowRechargeUser()), 4, RoundingMode.DOWN));

                item.setAvgUserRecharge(item.getUserRechargeCount() == 0 ?
                        BigDecimal.ZERO : item.getUserRechargeMoney().divide(BigDecimal.valueOf(item.getUserRechargeCount()), 4, RoundingMode.DOWN));

                item.setUserRechargeCost(item.getUserRechargeUser() == 0 ?
                        BigDecimal.ZERO : item.getCost().divide(BigDecimal.valueOf(item.getUserRechargeUser()), 2, RoundingMode.DOWN));

                item.setRepeatRechargeRate(item.getUserRechargeUser() == 0 ?
                        BigDecimal.ZERO : BigDecimal.valueOf(item.getRepeatRechargeUser()).divide(BigDecimal.valueOf(item.getUserRechargeUser()), 4, RoundingMode.DOWN));
                item.setUserRepeatRechargeRate(item.getNewUserRechargeUser() == 0 ?
                        BigDecimal.ZERO : BigDecimal.valueOf(item.getRepeatRechargeUser()).divide(BigDecimal.valueOf(item.getNewUserRechargeUser()), 4, RoundingMode.DOWN));

                item.setUserRechargeCountCost(item.getUserRechargeCount() == 0 ?
                        BigDecimal.ZERO : item.getCost().divide(BigDecimal.valueOf(item.getUserRechargeCount()), 4, RoundingMode.DOWN));
                item.setOldUserRechargeCountCost(item.getOldUserRechargeCount() == 0 ?
                        BigDecimal.ZERO : item.getCost().divide(BigDecimal.valueOf(item.getOldUserRechargeCount()), 4, RoundingMode.DOWN));
                item.setShowRechargeCountCost(item.getShowRechargeCount() == 0 ?
                        BigDecimal.ZERO : item.getCost().divide(BigDecimal.valueOf(item.getShowRechargeCount()), 4, RoundingMode.DOWN));
            }
            return item;
        }).collect(Collectors.toList());
        return new Page<>(hasRechargeDayList, pager);
    }

    @Override
    public Map<LocalDate, List<AgentDayAgainVO>> accountAgentDayAgain(AgentDayAgainDTO dto) {

        //如果查询时间不传递默认从今天开始查询
        if (dto.getBeginDate() == null) {
            dto.setBeginDate(LocalDate.now());
        }
        //判断开始时间后的30天是否超过当前日期
        if (dto.getBeginDate().plusDays(30).isAfter(LocalDate.now())) {
            //只查询到当前的数据
            dto.setEndDate(LocalDate.now());
        } else {
            //只考虑30天的数据
            dto.setEndDate(dto.getBeginDate().plusDays(30));
        }
        //拼接查询条件
        Criteria cri = Cnd.cri();
        if (dto.getAccountId() != null) {
            cri.where().andEquals("account_id", dto.getAccountId());
        }
        if (dto.getAgentId() != null) {
            cri.where().andEquals("agent_id", dto.getAgentId());
        }
        if (StringUtils.isNotBlank(dto.getSourceSystem())) {
            //拼接SDK来源
            cri.where().andEquals("source_system", dto.getSourceSystem());
        }
        if (dto.getBeginDate() != null) {
            cri.where().andEquals("dt", dto.getBeginDate());
            cri.where().andBetween("ddf", dto.getBeginDate(), dto.getEndDate());
        }
        //按 "ddf" 升序排序
        cri.getOrderBy().asc("ddf");

        //获取推广每日复充表的查询结果
        Sql againSql = Sqls.create(agentDayAgainSql() + cri);
        againSql.setCallback(Sqls.callback.entities());
        againSql.setEntity(dao.getEntity(AdsAgentDayAgain.class));
        //执行sql
        dao.execute(againSql);
        //获取到原始数据list
        List<AdsAgentDayAgain> list = againSql.getList(AdsAgentDayAgain.class);

        //创建Map记录数据
        Map<LocalDate, List<AgentDayAgainVO>> map = new TreeMap<>(
                (o1, o2) -> o1.isBefore(o2) == true ? -1 : 1);
        //有序存储每个list,每个list包含当天的所有充值次数的复充数据
        List<List<AgentDayAgainVO>> tempList = new ArrayList<>();
        //记录查询出的记录条数
        int count = list.size();
        //没有数据直接返回空结果
        if (count == 0) {
            return map;
        }
        //初始化list
        initList(tempList, count);

        //外层循环8次 表示不同复充次数的数据
        for (int i = 0; i < 8; i++) {
            //用来计算Ci
            int ci = i + 1;
            //第一天的所有充值次数都是先计算的
            //充值次数
            tempList.get(0).get(i).setCn("C" + ci);
            AgentDayAgainVO tempVO = tempList.get(0).get(i);
            //"原": Ci
            tempList.get(0).get(i).setOriginal(getCnByNum(list.get(0), ci));
            //"增":初始为 0
            tempList.get(0).get(i).setIncrease(0L);
            //"移":下一个充值次数的Ci 即C(i+1)
            tempList.get(0).get(i).setDecrease(getCnByNum(list.get(0), ci + 1));
            //"现":公式计算 = 原 + 增 - 移
            tempList.get(0).get(i).setPresent(
                    tempVO.getOriginal() + (tempVO.getIncrease()) - (tempVO.getDecrease())
            );
            //"比"
            tempList.get(0).get(i).setRate(getCnByNum(list.get(0), 1).compareTo(0L) == 0 ? BigDecimal.ZERO :
                    BigDecimal.valueOf(tempVO.getPresent().doubleValue() / getCnByNum(list.get(0), 1).doubleValue()).setScale(4, RoundingMode.HALF_UP)
            );

            //计算第2-n天的数据 复充次数i的数据
            for (int j = 1; j < count; j++) {
                //充值次数
                tempList.get(j).get(i).setCn("C" + ci);
                //"原":前一天的"现"
                tempList.get(j).get(i).setOriginal(tempList.get(j - 1).get(i).getPresent());
                //"现":当天的Ci - 当天的C(i+1)
                tempList.get(j).get(i).setPresent(
                        getCnByNum(list.get(j), ci) - (getCnByNum(list.get(j), ci + 1)));
                //"增":当天的Ci - 前一天的Ci
                tempList.get(j).get(i).setIncrease(
                        getCnByNum(list.get(j), ci) - (getCnByNum(list.get(j - 1), ci)));
                //"移":当天的C(i+1) - 前一天的C(i+1)
                tempList.get(j).get(i).setDecrease(
                        getCnByNum(list.get(j), ci + 1) - (getCnByNum(list.get(j - 1), ci + 1)));
                //"比":当天的现 / 前一天的C1
                tempList.get(j).get(i).setRate(getCnByNum(list.get(j - 1), 1).compareTo(0L) == 0 ? BigDecimal.ZERO :
                        BigDecimal.valueOf(tempList.get(j).get(i).getPresent().doubleValue() / getCnByNum(list.get(j - 1), 1).doubleValue()).setScale(4, RoundingMode.HALF_UP));
            }
        }
        //赋值时间数据
        for (int i = 0; i < tempList.size(); i++) {
            map.put(list.get(i).getDdf(), tempList.get(i));
        }

        return map;
    }

    @Override
    public GamePromoteTotalSumVO accountAgentTotalSum(GamePromoteTotalSumDTO dto) {
        Tuple2<List<Long>, List<Long>> poerInfo = dataPowerComponent.getPowerInfo(dto.getSourceSystem());
        List<Long> userIds = dto.getSysUserId() == null ? poerInfo.first : Collections.singletonList(dto.getSysUserId());
        List<Long> gameIds = dto.getGameId() == null ? poerInfo.second : Collections.singletonList(dto.getGameId());
        /*Long sysUserId = SecurityUtil.isAdmin() ? null : SecurityUtil.getUserId();
        sysUserId = null == dto.getSysUserId() ? sysUserId : dto.getSysUserId();
        dto.setSysUserId(sysUserId);*/
        if (null == dto.getCostEndDay() || null == dto.getCostBeginDay()) {
            dto.setCostBeginDay(LocalDate.now());
            dto.setCostEndDay(LocalDate.now());
        }
        if (null == dto.getRechargeEndDay() || null == dto.getRechargeBeginDay()) {
            dto.setRechargeEndDay(LocalDate.now());
            dto.setRechargeBeginDay(LocalDate.now());
        }
        // 推广总计
        SimpleCriteria cri = getSimpleCriteria(userIds, gameIds, dto);
        if (null != dto.getCostBeginDay()) {
            cri.where().and("a.dt", ">=", dto.getCostBeginDay());
        }
        if (null != dto.getCostEndDay()) {
            cri.where().and("a.dt", "<=", dto.getCostEndDay());
        }
        Sql sql = Sqls.queryEntity(accountAgentTotalSumSql() + "$condition");
        Entity<GamePromoteTotalSumVO> entity = dao.getEntity(GamePromoteTotalSumVO.class);
        sql.setEntity(entity).setCondition(cri);
        dao.execute(sql);
        GamePromoteTotalSumVO gamePromoteTotalSumVO = sql.getObject(GamePromoteTotalSumVO.class);
        if (null == gamePromoteTotalSumVO.getCost()) {
            return gamePromoteTotalSumVO;
        }

        //账面总计
        SimpleCriteria showCri = getSimpleCriteria(userIds, gameIds, dto);
        if (null != dto.getRechargeBeginDay()) {
            showCri.where().and("a.dt", ">=", dto.getRechargeBeginDay());
        }
        if (null != dto.getRechargeEndDay()) {
            showCri.where().and("a.dt", "<=", dto.getRechargeEndDay());
        }
        String show = """
                select
                ifnull(sum(a.amount_count),0) as show_recharge_count,
                ifnull(sum(a.amount_num),0) as show_recharge_user,
                ifnull(sum(a.amount),0) as show_recharge_money,
                round(if(sum(a.amount_count) > 0, sum(a.amount) / sum(a.amount_count), 0), 4) as avg_show_user_recharge,
                round(if(sum(a.amount_num) > 0, sum(a.amount) / sum(a.amount_num), 0), 4) as show_recharge_arpu
                from
                ads_account_agent_day a
                """;
        Sql showSql = Sqls.queryEntity(show + "$condition");
        Entity<ShowRecharge> showEntity = dao.getEntity(ShowRecharge.class);
        showSql.setEntity(showEntity).setCondition(showCri);
        dao.execute(showSql);
        ShowRecharge showRecharge = showSql.getObject(ShowRecharge.class);

        String showRechargeUser = """
                select count(distinct user_id) from ads_information
                where
                source_system = @sourceSystem
                and agent_id = @agentId
                and account_id = @accountId
                and order_time >= @rechargeBeginDay
                and order_time <= @rechargeEndDay
                """;
        Sql showRechargeUserSql = Sqls.fetchLong(showRechargeUser);
        showRechargeUserSql.setParam("rechargeBeginDay", dto.getRechargeBeginDay());
        showRechargeUserSql.setParam("rechargeEndDay", dto.getRechargeEndDay());
        if (null != showRecharge) {
            gamePromoteTotalSumVO.setShowRechargeArpu(showRecharge.getShowRechargeArpu());
            gamePromoteTotalSumVO.setShowRechargeCount(showRecharge.getShowRechargeCount());
            gamePromoteTotalSumVO.setShowRechargeMoney(showRecharge.getShowRechargeMoney());
            gamePromoteTotalSumVO.setShowRechargeUser(showRecharge.getShowRechargeUser());
            gamePromoteTotalSumVO.setAvgShowUserRecharge(showRecharge.getAvgShowUserRecharge());
        } else {
            gamePromoteTotalSumVO.setShowRechargeArpu(BigDecimal.ZERO);
            gamePromoteTotalSumVO.setShowRechargeCount(0L);
            gamePromoteTotalSumVO.setShowRechargeMoney(BigDecimal.ZERO);
            gamePromoteTotalSumVO.setShowRechargeUser(0L);
            gamePromoteTotalSumVO.setAvgShowUserRecharge(BigDecimal.ZERO);
        }

        // dayN总计
        SimpleCriteria agentAccountCri = getSimpleCriteria(userIds, gameIds, dto);
        if (null != dto.getCostBeginDay()) {
            agentAccountCri.where().and("a.dt", ">=", dto.getCostBeginDay());
        }
        if (null != dto.getCostEndDay()) {
            agentAccountCri.where().and("a.dt", "<=", dto.getCostEndDay());
        }
        agentAccountCri.groupBy("a.account_id,a.agent_id,a.source_system");
        Sql agentAccountSql = Sqls.queryEntity(accountAgentTotalSumDayNSql() + "$condition");
        Entity<AgentAccount> agentAccountEntity = dao.getEntity(AgentAccount.class);
        agentAccountSql.setEntity(agentAccountEntity).setCondition(agentAccountCri);
        dao.execute(agentAccountSql);
        List<AgentAccount> agentAccountList = agentAccountSql.getList(AgentAccount.class);
        //渠道注册时间内的dayn充值数据，获取表ads_dayn_amount的json数据
        Map<String, Object> dayNMap = new HashMap<>(4);
        dayNMap.put("beginDay", dto.getCostBeginDay());
        dayNMap.put("endDay", dto.getCostEndDay());

        Map<String, Object> newUserNumMap = new HashMap<>(7);
        newUserNumMap.put("costBeginDay", dto.getCostBeginDay());
        newUserNumMap.put("costEndDay", dto.getCostEndDay());
        newUserNumMap.put("rechargeBeginDay", dto.getRechargeBeginDay());
        newUserNumMap.put("rechargeEndDay", dto.getRechargeEndDay());
        Sql sqlDayN = Sqls.queryString("""
                select day_n as dayN
                from ads_dayn_amount
                where
                dt>=@beginDay and dt<=@endDay
                and account_id = @accountId
                and agent_id = @agentId
                and source_system = @sourceSystem
                """);

        Sql newUserNum = Sqls.fetchLong("""
                select
                	count(DISTINCT user_id) as repeatNum
                from
                	ads_information
                where
                    source_system = @sourceSystem
                    and agent_id = @agentId
                    and account_id = @accountId
                    and order_time >= @rechargeBeginDay
                    and order_time <= @rechargeEndDay
                    and reg_time >= @costBeginDay
                    and reg_time <= @costEndDay
                """);

        Sql newUserRepeatNum = Sqls.fetchLong("""
                SELECT count(*)
                FROM
                (select
                	count(user_id) as repeatNum
                from
                	ads_information
                where
                    source_system = @sourceSystem
                    and agent_id = @agentId
                    and account_id = @accountId
                    and order_time >= @rechargeBeginDay
                    and order_time <= @rechargeEndDay
                    and reg_time >= @costBeginDay
                    and reg_time <= @costEndDay
                    group by user_id) a
                WHERE a.repeatNum > 1
                """);

        Sql roleUserNum = Sqls.fetchLong("""
                select
                	count(DISTINCT role_user_id)
                from
                	dw_create_role_detail
                where
                    source_system = @sourceSystem
                    and user_agent_id = @agentId
                    and user_account_id = @accountId
                    and DATE(role_create_time) >= @rechargeBeginDay
                    and DATE(role_create_time) <= @rechargeEndDay
                    and user_dt >= @costBeginDay
                    and user_dt <= @costEndDay
                """);
        AtomicReference<Long> showRechargeUserNum = new AtomicReference<>(0L);
        AtomicReference<Long> roleUserNumLong = new AtomicReference<>(0L);
        DayN dayNTotal = agentAccountList.stream().map(item -> {
            showRechargeUserSql.setParam("sourceSystem", item.getSourceSystem());
            showRechargeUserSql.setParam("agentId", item.getAgentId());
            showRechargeUserSql.setParam("accountId", item.getAccountId());
            dao.execute(showRechargeUserSql);
            long showRechargeUserSqlLong = showRechargeUserSql.getLong();
            showRechargeUserNum.set(showRechargeUserNum.get() + showRechargeUserSqlLong);
            dayNMap.put("accountId", item.getAccountId());
            dayNMap.put("agentId", item.getAgentId());
            dayNMap.put("sourceSystem", item.getSourceSystem());
            sqlDayN.setParams(dayNMap);
            dao.execute(sqlDayN);
            List<String> dayNList = sqlDayN.getList(String.class);
            DayN dayN = null;

            newUserNumMap.put("accountId", item.getAccountId());
            newUserNumMap.put("agentId", item.getAgentId());
            newUserNumMap.put("sourceSystem", item.getSourceSystem());

            roleUserNum.setParams(newUserNumMap);
            dao.execute(roleUserNum);
            roleUserNumLong.set(roleUserNumLong.get() + roleUserNum.getLong());
            //ads_dayn_amount存在充值数据
            if (!CollectionUtils.isEmpty(dayNList)) {
                Gson gson = new Gson();
                List<Map<String, String>> jsonList = gson.fromJson(dayNList.toString(), ArrayList.class);

                Map<String, String> resultMap = new HashMap<>();
                resultMap.put("dayN", "0.00-0-0-0");
                for (Map<String, String> jsonMap : jsonList) {
                    for (Map.Entry<String, String> entry : jsonMap.entrySet()) {
                        String key = entry.getKey();
                        String value = entry.getValue();
                        String[] parts = value.split("-");
                        if (DateUtil.parseLocalDate(key).compareTo(dto.getRechargeBeginDay()) >= 0
                                && DateUtil.parseLocalDate(key).compareTo(dto.getRechargeEndDay()) <= 0) {
                            String[] existingParts = resultMap.get("dayN").split("-");
                            BigDecimal money = new BigDecimal(existingParts[0]).add(new BigDecimal(parts[0]));
                            int count = Integer.parseInt(existingParts[1]) + Integer.parseInt(parts[1]);
                            int num = Integer.parseInt(existingParts[2]) + Integer.parseInt(parts[2]);
                            int repeatNum = Integer.parseInt(existingParts[3]) + Integer.parseInt(parts[3]);
                            String newValue = money + "-" + count + "-" + num + "-" + repeatNum;
                            resultMap.put("dayN", newValue);
                        }
                    }
                }
                if (!resultMap.isEmpty() && !resultMap.get("dayN").isEmpty()) {
                    newUserNum.setParams(newUserNumMap);
                    dao.execute(newUserNum);
                    Long newUserNumLong = newUserNum.getLong();

                    newUserRepeatNum.setParams(newUserNumMap);
                    dao.execute(newUserRepeatNum);
                    Long newUserRepeatNumLong = newUserRepeatNum.getLong();
                    String[] parts = resultMap.get("dayN").split("-");
                    dayN = DayN.builder()
                            .rechargeMoney(new BigDecimal(parts[0]))
                            .rechargeCount(Long.valueOf(parts[1]))
                            .rechargeNum(newUserNumLong)
                            .repeatNum(newUserRepeatNumLong)
                            .build();
                }
            }
            return dayN;
        }).filter(Objects::nonNull).reduce((accumulator, currentItem) -> {
            //其他字段相加的逻辑
            accumulator.setRechargeCount(accumulator.getRechargeCount() + currentItem.getRechargeCount());
            accumulator.setRechargeMoney(accumulator.getRechargeMoney().add(currentItem.getRechargeMoney()));
            accumulator.setRechargeNum(accumulator.getRechargeNum() + currentItem.getRechargeNum());
            accumulator.setRepeatNum(accumulator.getRepeatNum() + currentItem.getRepeatNum());
            return accumulator;
        }).orElse(new DayN());

        gamePromoteTotalSumVO.setRoleNum(roleUserNumLong.get());
        gamePromoteTotalSumVO.setShowRechargeUser(showRechargeUserNum.get());
        gamePromoteTotalSumVO.setShowRechargeArpu(gamePromoteTotalSumVO.getShowRechargeUser() == 0 ? BigDecimal.ZERO :
                gamePromoteTotalSumVO.getShowRechargeMoney().divide(new BigDecimal(gamePromoteTotalSumVO.getShowRechargeUser()), 4, RoundingMode.DOWN));
        //充值时间范围内存在充值数据
        gamePromoteTotalSumVO.setUserRechargeMoney(dayNTotal.getRechargeMoney());
        gamePromoteTotalSumVO.setUserRechargeCount(dayNTotal.getRechargeCount());
        gamePromoteTotalSumVO.setUserRechargeUser(dayNTotal.getRechargeNum());
        gamePromoteTotalSumVO.setRepeatRechargeUser(dayNTotal.getRepeatNum());
        gamePromoteTotalSumVO.setUserRechargeArpu(gamePromoteTotalSumVO.getUserRechargeUser() == 0 ?
                BigDecimal.ZERO : gamePromoteTotalSumVO.getUserRechargeMoney().divide(BigDecimal.valueOf(gamePromoteTotalSumVO.getUserRechargeUser()), 4, RoundingMode.DOWN));

        gamePromoteTotalSumVO.setOldUserRechargeMoney(gamePromoteTotalSumVO.getShowRechargeMoney().subtract(gamePromoteTotalSumVO.getUserRechargeMoney()));
        gamePromoteTotalSumVO.setOldUserRechargeCount(gamePromoteTotalSumVO.getShowRechargeCount() - gamePromoteTotalSumVO.getUserRechargeCount());
        gamePromoteTotalSumVO.setOldUserRechargeUser(gamePromoteTotalSumVO.getShowRechargeUser() - gamePromoteTotalSumVO.getUserRechargeUser());

        gamePromoteTotalSumVO.setRecoveryRate(gamePromoteTotalSumVO.getCost().compareTo(BigDecimal.ZERO) == 0 ?
                BigDecimal.ZERO : gamePromoteTotalSumVO.getUserRechargeMoney().divide(gamePromoteTotalSumVO.getCost(), 4, RoundingMode.DOWN));

        gamePromoteTotalSumVO.setUserRechargeRate(gamePromoteTotalSumVO.getRegUserCount() == 0 ?
                BigDecimal.ZERO : BigDecimal.valueOf(gamePromoteTotalSumVO.getUserRechargeUser()).divide(BigDecimal.valueOf(gamePromoteTotalSumVO.getRegUserCount()), 4, RoundingMode.DOWN));

        gamePromoteTotalSumVO.setNewUserRechargeRatio(gamePromoteTotalSumVO.getShowRechargeUser() == 0 ?
                BigDecimal.ZERO : BigDecimal.valueOf(gamePromoteTotalSumVO.getUserRechargeUser()).divide(BigDecimal.valueOf(gamePromoteTotalSumVO.getShowRechargeUser()), 4, RoundingMode.DOWN));

        gamePromoteTotalSumVO.setAvgUserRecharge(gamePromoteTotalSumVO.getUserRechargeCount() == 0 ?
                BigDecimal.ZERO : gamePromoteTotalSumVO.getUserRechargeMoney().divide(BigDecimal.valueOf(gamePromoteTotalSumVO.getUserRechargeCount()), 4, RoundingMode.DOWN));

        gamePromoteTotalSumVO.setUserRechargeCost(gamePromoteTotalSumVO.getUserRechargeUser() == 0 ?
                BigDecimal.ZERO : gamePromoteTotalSumVO.getCost().divide(BigDecimal.valueOf(gamePromoteTotalSumVO.getUserRechargeUser()), 4, RoundingMode.DOWN));

        gamePromoteTotalSumVO.setRepeatRechargeRate(gamePromoteTotalSumVO.getUserRechargeUser() == 0 ?
                BigDecimal.ZERO : BigDecimal.valueOf(gamePromoteTotalSumVO.getRepeatRechargeUser()).divide(BigDecimal.valueOf(gamePromoteTotalSumVO.getUserRechargeUser()), 4, RoundingMode.DOWN));
        gamePromoteTotalSumVO.setUserRepeatRechargeRate(gamePromoteTotalSumVO.getNewUserRechargeUser() == 0 ?
                BigDecimal.ZERO : BigDecimal.valueOf(gamePromoteTotalSumVO.getRepeatRechargeUser()).divide(BigDecimal.valueOf(gamePromoteTotalSumVO.getNewUserRechargeUser()), 4, RoundingMode.DOWN));

        gamePromoteTotalSumVO.setUserRechargeCountCost(gamePromoteTotalSumVO.getUserRechargeCount() == 0 ?
                BigDecimal.ZERO : gamePromoteTotalSumVO.getCost().divide(BigDecimal.valueOf(gamePromoteTotalSumVO.getUserRechargeCount()), 4, RoundingMode.DOWN));
        gamePromoteTotalSumVO.setOldUserRechargeCountCost(gamePromoteTotalSumVO.getOldUserRechargeCount() == 0 ?
                BigDecimal.ZERO : gamePromoteTotalSumVO.getCost().divide(BigDecimal.valueOf(gamePromoteTotalSumVO.getOldUserRechargeCount()), 4, RoundingMode.DOWN));
        gamePromoteTotalSumVO.setShowRechargeCountCost(gamePromoteTotalSumVO.getShowRechargeCount() == 0 ?
                BigDecimal.ZERO : gamePromoteTotalSumVO.getCost().divide(BigDecimal.valueOf(gamePromoteTotalSumVO.getShowRechargeCount()), 4, RoundingMode.DOWN));
        return gamePromoteTotalSumVO;
    }

    private SimpleCriteria getSimpleCriteria(List<Long> userIds, List<Long> gameIds, GamePromoteTotalSumDTO dto) {
        SimpleCriteria cri = Cnd.cri();
        // 组装条件
        if (null != userIds) {
            cri.where().andInList("a.pitcher_id", userIds);
        }
        if (StringUtils.isNotBlank(dto.getAccountId())) {
            cri.where().andEquals("a.account_id", dto.getAccountId());
        }
        if (StringUtils.isNotBlank(dto.getAccountType())) {
            cri.where().andEquals("account_type", dto.getAccountType());
        }
        if (!CollectionUtils.isEmpty(dto.getAgentId())) {
            cri.where().andInList("agent_id", dto.getAgentId());
        }
        if (StringUtils.isNotBlank(dto.getCpName())) {
            cri.where().andLike("a.game_cp", dto.getCpName());
        }
        if (StringUtils.isNotBlank(dto.getGameName())) {
            cri.where().andLike("a.game_name", dto.getGameName());
        }
        if (StringUtils.isNotBlank(dto.getGameType())) {
            cri.where().andEquals("a.game_type", dto.getGameType());
        }
        if (null != gameIds) {
            cri.where().andInList("a.game_id", gameIds);
        }
        if (StringUtils.isNotBlank(dto.getSourceSystem())) {
            cri.where().andEquals("a.source_system", dto.getSourceSystem());
        }
        return cri;
    }

    /**
     * 初始化List数据
     *
     * @param list 需要初始化的数据
     * @param num  多少天的数据
     */
    private void initList(List<List<AgentDayAgainVO>> list, Integer num) {
        //外层循环 num 次 表示多少天的数据
        for (int i = 0; i < num; i++) {
            list.add(new ArrayList<>());
            //内层循环8次 一共初始化8种复充次数数据对象
            for (int j = 0; j < 8; j++) {
                list.get(i).add(
                        AgentDayAgainVO.builder()
                                .decrease(0L)
                                .increase(0L)
                                .original(0L)
                                .present(0L)
                                .rate(BigDecimal.ZERO)
                                .build()
                );
            }
        }
    }

    /**
     * 通过反射来获取Cn的值
     *
     * @param dto 数据库查询出来的原始数据对象实体
     * @param num 1-9
     * @return Cn
     */
    private Long getCnByNum(Object dto, int num) {
        try {
            Method m1 = AdsAgentDayAgain.class.getDeclaredMethod(String.format("getC%s", num));
            //返回对应的Cn值
            return (Long) m1.invoke(dto);
        } catch (Exception e) {
            throw new BaseException("发生错误");
        }
    }

    /**
     * 推广每日复充趋势sql
     *
     * @return
     */
    private String agentDayAgainSql() {
        return """
                SELECT
                    dt,
                    ddf,
                    account_id,
                    agent_id,
                    agent_name,
                    source_system,
                    c1,
                    c2,
                    c3,
                    c4,
                    c5,
                    c6,
                    c7,
                    c8,
                    c9
                FROM
                    ads_agent_day_again
                """;
    }

    /**
     * 推广每日数据SQL
     *
     * @return
     */
    private String accountAgentDaySql() {
        return """
                select
                    dt,
                	concat( dt, account_id, agent_id ) AS id,
                	source_system,
                	agent_id,
                	agent_name,
                	account_type,
                	account_id,
                	agent_id,
                	pitcher AS sys_user_name,
                	game_cp AS cp_name,
                	game_id,
                	game_name,
                	game_type,
                	dt AS cost_day,
                	pitcher_id AS sys_user_id,
                	cost,
                	plan_count AS ad_plan_count,
                	view_count,
                	click_count,
                	change_count AS convert_count,
                	change_rate AS convert_rate,
                	avg_change_price AS avg_convert_cost,
                	depth_change_count AS deep_convert_count,
                	depth_change_rate AS deep_convert_rate,
                	avg_depth_change_price AS deep_convert_cost,
                	thousand_display_price AS thousand_view_cost,
                	click_rate,
                	avg_click_price AS avg_click_cost,
                	download_start AS app_download_count,
                	download_start_cost AS app_download_cost,
                	download_start_rate AS app_download_rate,
                	download_finish,
                	download_finish_cost,
                	download_finish_rate,
                	install_finish AS app_install_count,
                	install_finish_cost AS app_install_cost,
                	install_finish_rate AS app_install_rate,
                	active AS app_active_count,
                	active_cost AS app_active_cost,
                	active_rate AS app_active_rate,
                	register_num AS reg_user_count,
                	register_cost AS reg_cost,
                	first_new_user_amount_count AS first_user_recharge_count,
                	first_new_user_amount_num AS first_user_recharge_user,
                	first_new_user_amount AS first_user_recharge_money,
                	amount_count AS show_recharge_count,
                	amount_num AS show_recharge_user,
                	amount AS show_recharge_money,
                	old_amount_count AS old_user_recharge_count,
                	old_amount_num AS old_user_recharge_user,
                	old_amount AS old_user_recharge_money,
                	new_user_total_amount_count AS new_user_recharge_count,
                	new_user_total_amount_num AS new_user_recharge_user,
                	new_user_total_amount AS new_user_recharge_money,
                	today_roi AS today_recovery_rate,
                	first_roi AS first_recovery_rate,
                	buy_roi AS buy_recovery_rate,
                	gross_profit AS gross_margin,
                        roi1 AS roi_day1,
                        roi2 AS roi_day2,
                        roi3 AS roi_day3,
                        roi4 AS roi_day4,
                        roi5 AS roi_day5,
                        roi6 AS roi_day6,
                        roi7 AS roi_day7,
                        roi8 AS roi_day8,
                        roi9 AS roi_day9,
                        roi10 AS roi_day10,
                        roi11 AS roi_day11,
                        roi12 AS roi_day12,
                        roi13 AS roi_day13,
                        roi14 AS roi_day14,
                        roi15 AS roi_day15,
                        roi16 AS roi_day16,
                        roi17 AS roi_day17,
                        roi18 AS roi_day18,
                        roi19 AS roi_day19,
                        roi20 AS roi_day20,
                        roi21 AS roi_day21,
                        roi22 AS roi_day22,
                        roi23 AS roi_day23,
                        roi24 AS roi_day24,
                        roi25 AS roi_day25,
                        roi26 AS roi_day26,
                        roi27 AS roi_day27,
                        roi28 AS roi_day28,
                        roi29 AS roi_day29,
                        roi30 AS roi_day30,
                        roi60 AS roi_day60,
                        roi90 AS roi_day90,
                        roi180 AS roi_day180,
                        roi1yaer AS roi_year1,
                        roi_total AS roi_total,
                	first_rate AS first_recharge_rate,
                	buy_user_rate AS buy_user_recharge_rate,
                	today_rate AS new_user_recharge_rate,
                	first_avg AS avg_first_user_recharge,
                	buy_avg AS avg_buy_user_recharge,
                	paper_avg AS avg_show_user_recharge,
                	first_amount_cost AS first_recharge_cost,
                	buy_amount_cost AS buy_user_recharge_cost,
                	today_again_rate AS repeat_recharge_rate,
                	new_reg_arpu,
                	first_arpu AS first_recharge_arpu,
                	today_arpu AS today_recharge_arpu,
                	paper_arpu AS show_recharge_arpu,
                       amount_d1 AS trend_day1,
                       amount_d2 AS trend_day2,
                       amount_d3 AS trend_day3,
                       amount_d4 AS trend_day4,
                       amount_d5 AS trend_day5,
                       amount_d6 AS trend_day6,
                       amount_d7 AS trend_day7,
                       amount_d8 AS trend_day8,
                       amount_d9 AS trend_day9,
                       amount_d10 AS trend_day10,
                       amount_d11 AS trend_day11,
                       amount_d12 AS trend_day12,
                       amount_d13 AS trend_day13,
                       amount_d14 AS trend_day14,
                       amount_d15 AS trend_day15,
                       amount_d16 AS trend_day16,
                       amount_d17 AS trend_day17,
                       amount_d18 AS trend_day18,
                       amount_d19 AS trend_day19,
                       amount_d20 AS trend_day20,
                       amount_d21 AS trend_day21,
                       amount_d22 AS trend_day22,
                       amount_d23 AS trend_day23,
                       amount_d24 AS trend_day24,
                       amount_d25 AS trend_day25,
                       amount_d26 AS trend_day26,
                       amount_d27 AS trend_day27,
                       amount_d28 AS trend_day28,
                       amount_d29 AS trend_day29,
                       amount_d30 AS trend_day30,
                       amount_d31 AS trend_day31,
                       amount_d32 AS trend_day32,
                       amount_d33 AS trend_day33,
                       amount_d34 AS trend_day34,
                       amount_d35 AS trend_day35,
                       amount_d36 AS trend_day36,
                       amount_d37 AS trend_day37,
                       amount_d38 AS trend_day38,
                       amount_d39 AS trend_day39,
                       amount_d40 AS trend_day40,
                       amount_d41 AS trend_day41,
                       amount_d42 AS trend_day42,
                       amount_d43 AS trend_day43,
                       amount_d44 AS trend_day44,
                       amount_d45 AS trend_day45,
                       amount_d46 AS trend_day46,
                       amount_d47 AS trend_day47,
                       amount_d48 AS trend_day48,
                       amount_d49 AS trend_day49,
                       amount_d50 AS trend_day50,
                       amount_d51 AS trend_day51,
                       amount_d52 AS trend_day52,
                       amount_d53 AS trend_day53,
                       amount_d54 AS trend_day54,
                       amount_d55 AS trend_day55,
                       amount_d56 AS trend_day56,
                       amount_d57 AS trend_day57,
                       amount_d58 AS trend_day58,
                       amount_d59 AS trend_day59,
                       amount_d60 AS trend_day60,
                       amount_d61 AS trend_day61,
                       amount_d62 AS trend_day62,
                       amount_d63 AS trend_day63,
                       amount_d64 AS trend_day64,
                       amount_d65 AS trend_day65,
                       amount_d66 AS trend_day66,
                       amount_d67 AS trend_day67,
                       amount_d68 AS trend_day68,
                       amount_d69 AS trend_day69,
                       amount_d70 AS trend_day70,
                       amount_d71 AS trend_day71,
                       amount_d72 AS trend_day72,
                       amount_d73 AS trend_day73,
                       amount_d74 AS trend_day74,
                       amount_d75 AS trend_day75,
                       amount_d76 AS trend_day76,
                       amount_d77 AS trend_day77,
                       amount_d78 AS trend_day78,
                       amount_d79 AS trend_day79,
                       amount_d80 AS trend_day80,
                       amount_d81 AS trend_day81,
                       amount_d82 AS trend_day82,
                       amount_d83 AS trend_day83,
                       amount_d84 AS trend_day84,
                       amount_d85 AS trend_day85,
                       amount_d86 AS trend_day86,
                       amount_d87 AS trend_day87,
                       amount_d88 AS trend_day88,
                       amount_d89 AS trend_day89,
                       amount_d90 AS trend_day90,
                       amount_m4 AS trend_month4,
                       amount_m5 AS trend_month5,
                       amount_m6 AS trend_month6,
                       amount_m7 AS trend_month7,
                       amount_m8 AS trend_month8,
                       amount_m9 AS trend_month9,
                       amount_m10 AS trend_month10,
                       amount_m11 AS trend_month11,
                       amount_m12 AS trend_month12,
                       amount_sum AS trend_total,
                	round(if(first_new_user_amount_count > 0, cost / first_new_user_amount_count, 0), 4) as first_recharge_count_cost,
                	round(if(old_amount_count > 0, cost / old_amount_count, 0), 4) as old_user_recharge_count_cost,
                	round(if(amount_count > 0, cost / amount_count, 0), 4) as show_recharge_count_cost,
                	round(if(new_user_total_amount_count > 0, cost / new_user_total_amount_count, 0), 4) as new_user_recharge_count_cost,
                	hundred_user_num,
                	hundred_user_num_cost,
                	first_role_num,
                	new_user_total_role_num,
                	role_num
                from
                	ads_account_agent_day
                """;
    }

    /**
     * 推广每日数据SQL- 父游戏维度
     *
     * @return
     */
    private String accountAgentDaySqlForParent() {
        return """
                select
                    dt,
                	concat( dt, account_id, agent_id ) AS id,
                	source_system,
                	agent_id,
                	agent_name,
                	account_type,
                	account_id,
                	agent_id,
                	pitcher AS sys_user_name,
                	game_cp AS cp_name,
                	parent_game_id as game_id,
                	parent_game_name as game_name,
                	parent_game_type as game_type,
                	dt AS cost_day,
                	pitcher_id AS sys_user_id,
                	cost,
                	plan_count AS ad_plan_count,
                	view_count,
                	click_count,
                	change_count AS convert_count,
                	change_rate AS convert_rate,
                	avg_change_price AS avg_convert_cost,
                	depth_change_count AS deep_convert_count,
                	depth_change_rate AS deep_convert_rate,
                	avg_depth_change_price AS deep_convert_cost,
                	thousand_display_price AS thousand_view_cost,
                	click_rate,
                	avg_click_price AS avg_click_cost,
                	download_start AS app_download_count,
                	download_start_cost AS app_download_cost,
                	download_start_rate AS app_download_rate,
                	download_finish,
                	download_finish_cost,
                	download_finish_rate,
                	install_finish AS app_install_count,
                	install_finish_cost AS app_install_cost,
                	install_finish_rate AS app_install_rate,
                	active AS app_active_count,
                	active_cost AS app_active_cost,
                	active_rate AS app_active_rate,
                	register_num AS reg_user_count,
                	register_cost AS reg_cost,
                	first_new_user_amount_count AS first_user_recharge_count,
                	first_new_user_amount_num AS first_user_recharge_user,
                	first_new_user_amount AS first_user_recharge_money,
                	amount_count AS show_recharge_count,
                	amount_num AS show_recharge_user,
                	amount AS show_recharge_money,
                	old_amount_count AS old_user_recharge_count,
                	old_amount_num AS old_user_recharge_user,
                	old_amount AS old_user_recharge_money,
                	new_user_total_amount_count AS new_user_recharge_count,
                	new_user_total_amount_num AS new_user_recharge_user,
                	new_user_total_amount AS new_user_recharge_money,
                	today_roi AS today_recovery_rate,
                	first_roi AS first_recovery_rate,
                	buy_roi AS buy_recovery_rate,
                	gross_profit AS gross_margin,
                        roi1 AS roi_day1,
                        roi2 AS roi_day2,
                        roi3 AS roi_day3,
                        roi4 AS roi_day4,
                        roi5 AS roi_day5,
                        roi6 AS roi_day6,
                        roi7 AS roi_day7,
                        roi8 AS roi_day8,
                        roi9 AS roi_day9,
                        roi10 AS roi_day10,
                        roi11 AS roi_day11,
                        roi12 AS roi_day12,
                        roi13 AS roi_day13,
                        roi14 AS roi_day14,
                        roi15 AS roi_day15,
                        roi16 AS roi_day16,
                        roi17 AS roi_day17,
                        roi18 AS roi_day18,
                        roi19 AS roi_day19,
                        roi20 AS roi_day20,
                        roi21 AS roi_day21,
                        roi22 AS roi_day22,
                        roi23 AS roi_day23,
                        roi24 AS roi_day24,
                        roi25 AS roi_day25,
                        roi26 AS roi_day26,
                        roi27 AS roi_day27,
                        roi28 AS roi_day28,
                        roi29 AS roi_day29,
                        roi30 AS roi_day30,
                        roi60 AS roi_day60,
                        roi90 AS roi_day90,
                        roi180 AS roi_day180,
                        roi1yaer AS roi_year1,
                        roi_total AS roi_total,
                	first_rate AS first_recharge_rate,
                	buy_user_rate AS buy_user_recharge_rate,
                	today_rate AS new_user_recharge_rate,
                	first_avg AS avg_first_user_recharge,
                	buy_avg AS avg_buy_user_recharge,
                	paper_avg AS avg_show_user_recharge,
                	first_amount_cost AS first_recharge_cost,
                	buy_amount_cost AS buy_user_recharge_cost,
                	today_again_rate AS repeat_recharge_rate,
                	new_reg_arpu,
                	first_arpu AS first_recharge_arpu,
                	today_arpu AS today_recharge_arpu,
                	paper_arpu AS show_recharge_arpu,
                       amount_d1 AS trend_day1,
                       amount_d2 AS trend_day2,
                       amount_d3 AS trend_day3,
                       amount_d4 AS trend_day4,
                       amount_d5 AS trend_day5,
                       amount_d6 AS trend_day6,
                       amount_d7 AS trend_day7,
                       amount_d8 AS trend_day8,
                       amount_d9 AS trend_day9,
                       amount_d10 AS trend_day10,
                       amount_d11 AS trend_day11,
                       amount_d12 AS trend_day12,
                       amount_d13 AS trend_day13,
                       amount_d14 AS trend_day14,
                       amount_d15 AS trend_day15,
                       amount_d16 AS trend_day16,
                       amount_d17 AS trend_day17,
                       amount_d18 AS trend_day18,
                       amount_d19 AS trend_day19,
                       amount_d20 AS trend_day20,
                       amount_d21 AS trend_day21,
                       amount_d22 AS trend_day22,
                       amount_d23 AS trend_day23,
                       amount_d24 AS trend_day24,
                       amount_d25 AS trend_day25,
                       amount_d26 AS trend_day26,
                       amount_d27 AS trend_day27,
                       amount_d28 AS trend_day28,
                       amount_d29 AS trend_day29,
                       amount_d30 AS trend_day30,
                       amount_d31 AS trend_day31,
                       amount_d32 AS trend_day32,
                       amount_d33 AS trend_day33,
                       amount_d34 AS trend_day34,
                       amount_d35 AS trend_day35,
                       amount_d36 AS trend_day36,
                       amount_d37 AS trend_day37,
                       amount_d38 AS trend_day38,
                       amount_d39 AS trend_day39,
                       amount_d40 AS trend_day40,
                       amount_d41 AS trend_day41,
                       amount_d42 AS trend_day42,
                       amount_d43 AS trend_day43,
                       amount_d44 AS trend_day44,
                       amount_d45 AS trend_day45,
                       amount_d46 AS trend_day46,
                       amount_d47 AS trend_day47,
                       amount_d48 AS trend_day48,
                       amount_d49 AS trend_day49,
                       amount_d50 AS trend_day50,
                       amount_d51 AS trend_day51,
                       amount_d52 AS trend_day52,
                       amount_d53 AS trend_day53,
                       amount_d54 AS trend_day54,
                       amount_d55 AS trend_day55,
                       amount_d56 AS trend_day56,
                       amount_d57 AS trend_day57,
                       amount_d58 AS trend_day58,
                       amount_d59 AS trend_day59,
                       amount_d60 AS trend_day60,
                       amount_d61 AS trend_day61,
                       amount_d62 AS trend_day62,
                       amount_d63 AS trend_day63,
                       amount_d64 AS trend_day64,
                       amount_d65 AS trend_day65,
                       amount_d66 AS trend_day66,
                       amount_d67 AS trend_day67,
                       amount_d68 AS trend_day68,
                       amount_d69 AS trend_day69,
                       amount_d70 AS trend_day70,
                       amount_d71 AS trend_day71,
                       amount_d72 AS trend_day72,
                       amount_d73 AS trend_day73,
                       amount_d74 AS trend_day74,
                       amount_d75 AS trend_day75,
                       amount_d76 AS trend_day76,
                       amount_d77 AS trend_day77,
                       amount_d78 AS trend_day78,
                       amount_d79 AS trend_day79,
                       amount_d80 AS trend_day80,
                       amount_d81 AS trend_day81,
                       amount_d82 AS trend_day82,
                       amount_d83 AS trend_day83,
                       amount_d84 AS trend_day84,
                       amount_d85 AS trend_day85,
                       amount_d86 AS trend_day86,
                       amount_d87 AS trend_day87,
                       amount_d88 AS trend_day88,
                       amount_d89 AS trend_day89,
                       amount_d90 AS trend_day90,
                       amount_m4 AS trend_month4,
                       amount_m5 AS trend_month5,
                       amount_m6 AS trend_month6,
                       amount_m7 AS trend_month7,
                       amount_m8 AS trend_month8,
                       amount_m9 AS trend_month9,
                       amount_m10 AS trend_month10,
                       amount_m11 AS trend_month11,
                       amount_m12 AS trend_month12,
                       amount_sum AS trend_total,
                	round(if(first_new_user_amount_count > 0, cost / first_new_user_amount_count, 0), 4) as first_recharge_count_cost,
                	round(if(old_amount_count > 0, cost / old_amount_count, 0), 4) as old_user_recharge_count_cost,
                	round(if(amount_count > 0, cost / amount_count, 0), 4) as show_recharge_count_cost,
                	round(if(new_user_total_amount_count > 0, cost / new_user_total_amount_count, 0), 4) as new_user_recharge_count_cost,
                	hundred_user_num,
                	hundred_user_num_cost,
                	first_role_num,
                	new_user_total_role_num,
                	role_num
                from
                	game_ads_parent.ads_account_agent_day_parent
                """;
    }

    /**
     * 推广每日数据总计SQL
     *
     * @return
     */
    private String accountAgentDayTotalSql() {
        return """
                select
                	sum(cost) as cost,
                	sum(plan_count) as ad_plan_count,
                	sum(view_count) as view_count,
                	sum(click_count) as click_count,
                	sum(change_count) as convert_count,
                	round(if(sum(click_count) > 0, sum(change_count) / sum(click_count), 0), 4) as convert_rate,
                	round(if(sum(change_count) > 0, sum(cost) / sum(change_count), 0), 4) as avg_convert_cost,
                	sum(depth_change_count) as deep_convert_count,
                	round(if(sum(change_count) > 0, sum(depth_change_count) / sum(change_count), 0), 4) as deep_convert_rate,
                	round(if(sum(depth_change_count) > 0, sum(cost) / sum(depth_change_count), 0), 4) as deep_convert_cost,
                	round(if(sum(view_count) > 0, sum(cost) / sum(view_count) * 1000, 0), 4) as thousand_view_cost,
                	round(if(sum(view_count) > 0, sum(click_count) / sum(view_count), 0), 4) as click_rate,
                	round(if(sum(click_count) > 0, sum(cost) / sum(click_count), 0), 2) as avg_click_cost,
                	sum(download_start) as app_download_count,
                	round(if(sum(download_start) > 0, sum(cost) / sum(download_start), 0), 4) as app_download_cost,
                	round(if(sum(click_count) > 0, sum(download_start) / sum(click_count), 0), 4) as app_download_rate,
                	sum(download_finish) as download_finish,
                	round(if(sum(download_finish) > 0, sum(cost) / sum(download_finish), 0), 4) as download_finish_cost,
                	round(if(sum(download_start) > 0, sum(download_finish) / sum(download_start), 0), 4) as download_finish_rate,
                	sum(install_finish) as app_install_count,
                	round(if(sum(install_finish) > 0, sum(cost) / sum(install_finish), 0), 4) as app_install_cost,
                	round(if(sum(download_finish) > 0, sum(install_finish) / sum(download_finish), 0), 4) as app_install_rate,
                	sum(active) as app_active_count,
                	round(if(sum(active) > 0, sum(cost) / sum(active), 0), 4) as app_active_cost,
                	round(if(sum(click_count) > 0, sum(active) / sum(click_count), 0), 4) as app_active_rate,
                	sum(register_num) as reg_user_count,
                	round(if(sum(register_num) > 0, sum(cost) / sum(register_num), 0), 4) as reg_cost,
                	sum(first_new_user_amount_count) as first_user_recharge_count,
                	sum(first_new_user_amount_num) as first_user_recharge_user,
                	sum(first_new_user_amount) as first_user_recharge_money,
                	sum(amount_count) as show_recharge_count,
                	sum(amount_num) as show_recharge_user,
                	sum(amount) as show_recharge_money,
                	sum(old_amount_count) as old_user_recharge_count,
                	sum(old_amount_num) as old_user_recharge_user,
                	sum(old_amount) as old_user_recharge_money,
                	sum(new_user_total_amount_count) as new_user_recharge_count,
                	sum(new_user_total_amount_num) as new_user_recharge_user,
                	sum(new_user_total_amount) as new_user_recharge_money,
                	round(if(sum(cost) > 0, sum(first_new_user_amount) / sum(cost), 0), 4) as first_recovery_rate,
                	round(if(sum(cost) > 0, sum(new_user_total_amount) / sum(cost), 0), 4) as today_recovery_rate,
                	round(if(sum(cost) > 0, sum(buy_new_user_total_amount) / sum(cost), 0), 4) as buy_recovery_rate,
                	sum(gross_profit) as gross_margin,
                	"""
                + roiDay() +
                """
                        round(if(SUM(IF(DATE_ADD(dt, INTERVAL 29 DAY) > CURRENT_DATE(), 0, cost)) > 0, sum(SPLIT_PART(amount_d30, '/', 6)) / SUM(IF(DATE_ADD(dt, INTERVAL 29 DAY) > CURRENT_DATE(), 0, cost)), 0), 4) as roi_day30,
                        round(if(SUM(IF(DATE_ADD(dt, INTERVAL 59 DAY) > CURRENT_DATE(), 0, cost)) > 0, sum(SPLIT_PART(amount_d60, '/', 6)) / SUM(IF(DATE_ADD(dt, INTERVAL 59 DAY) > CURRENT_DATE(), 0, cost)), 0), 4) as roi_day60,
                        round(if(SUM(IF(DATE_ADD(dt, INTERVAL 89 DAY) > CURRENT_DATE(), 0, cost)) > 0, sum(SPLIT_PART(amount_d90, '/', 6)) / SUM(IF(DATE_ADD(dt, INTERVAL 89 DAY) > CURRENT_DATE(), 0, cost)), 0), 4) as roi_day90,
                        round(if(SUM(IF(DATE_ADD(dt, INTERVAL 179 DAY) > CURRENT_DATE(), 0, cost)) > 0, sum(SPLIT_PART(amount_m6, '/', 6)) / SUM(IF(DATE_ADD(dt, INTERVAL 179 DAY) > CURRENT_DATE(), 0, cost)), 0), 4) as roi_day180,
                        round(if(SUM(IF(DATE_ADD(dt, INTERVAL 359 DAY) > CURRENT_DATE(), 0, cost)) > 0, sum(SPLIT_PART(amount_m12, '/', 6)) / SUM(IF(DATE_ADD(dt, INTERVAL 359 DAY) > CURRENT_DATE(), 0, cost)), 0), 4) as roi_year1,
                        round(if(SUM(cost) > 0, sum(SPLIT_PART(amount_sum, '/', 6)) / SUM(cost), 0), 4) as roi_total,
                           """
                + trendDay() +
                """
                        CONCAT(SUM(CAST(SPLIT_PART(amount_sum , '/', 1) AS DECIMAL(10, 2))),'/',SUM(CAST(SPLIT_PART(amount_sum, '/', 6) AS DECIMAL(10, 2))),'/',SUM(CAST(SPLIT_PART(amount_sum, '/', 2) AS BIGINT))) AS trend_total,
                        round(if(sum(register_num) > 0, sum(first_new_user_amount_num) / sum(register_num), 0), 4) as first_recharge_rate,
                        round(if(sum(register_num) > 0, sum(buy_new_user_total_amount_num) / sum(register_num), 0), 4) as buy_user_recharge_rate,
                        round(if(sum(amount_num) > 0, sum(first_new_user_amount_num) / sum(amount_num), 0), 4) as new_user_recharge_rate,
                        round(if(sum(first_new_user_amount_count) > 0, sum(first_new_user_amount) / sum(first_new_user_amount_count), 0), 4) as avg_first_user_recharge,
                        round(if(sum(buy_new_user_total_amount_count) > 0, sum(buy_new_user_total_amount) / sum(buy_new_user_total_amount_count), 0), 4) as avg_buy_user_recharge,
                        round(if(sum(amount_count) > 0, sum(amount) / sum(amount_count), 0), 4) as avg_show_user_recharge,
                        round(if(sum(first_new_user_amount_num) > 0, sum(cost) / sum(first_new_user_amount_num), 0), 4) as first_recharge_cost,
                        round(if(sum(buy_new_user_total_amount_num) > 0, sum(cost) / sum(buy_new_user_total_amount_num), 0), 4) as buy_user_recharge_cost,
                        round(if(sum(new_user_total_amount_num) > 0, sum(reg_order_user_again) / sum(new_user_total_amount_num), 0), 4) as repeat_recharge_rate,
                        round(if(sum(register_num) > 0, sum(new_user_total_amount) / sum(register_num), 0), 4) as new_reg_arpu,
                        round(if(sum(first_new_user_amount_num) > 0, sum(first_new_user_amount) / sum(first_new_user_amount_num), 0), 4) as first_recharge_arpu,
                        round(if(sum(new_user_total_amount_num) > 0, sum(new_user_total_amount) / sum(new_user_total_amount_num), 0), 4) as today_recharge_arpu,
                        round(if(sum(amount_num) > 0, sum(amount) / sum(amount_num), 0), 4) as show_recharge_arpu,
                        round(if(sum(first_new_user_amount_count) > 0, sum(cost) / sum(first_new_user_amount_count), 0), 4) as first_recharge_count_cost,
                        round(if(sum(old_amount_count) > 0, sum(cost) / sum(old_amount_count), 0), 4) as old_user_recharge_count_cost,
                        round(if(sum(amount_count) > 0, sum(cost) / sum(amount_count), 0), 4) as show_recharge_count_cost,
                        round(if(sum(new_user_total_amount_count) > 0, sum(cost) / sum(new_user_total_amount_count), 0), 4) as new_user_recharge_count_cost,
                        sum(hundred_user_num) as hundred_user_num,
                        round(if(sum(hundred_user_num) > 0, sum(cost) / sum(hundred_user_num), 0), 4) as hundred_user_num_cost,
                        sum(first_role_num) as first_role_num,
                        sum(new_user_total_role_num) as new_user_total_role_num,
                        sum(role_num) as role_num
                        from
                        ads_account_agent_day
                            """;
    }

    private String trendDay() {
        StringBuilder trendDay = new StringBuilder(StringUtils.EMPTY);
        LocalDate now = LocalDate.now();
        for (int day = 1; day <= 90; day++) {
            trendDay.append("""
                    CONCAT(
                        SUM(CAST(SPLIT_PART(amount_d%s , '/', 1) AS DECIMAL(10, 2))), '/',
                        SUM(CAST(SPLIT_PART(amount_d%s, '/', 6) AS DECIMAL(10, 2))), '/',
                        SUM(CAST(SPLIT_PART(amount_d%s, '/', 2) AS BIGINT)), '/',
                        SUM(IF(DATE_ADD(dt, INTERVAL %s DAY) > '%s', 0, cost)), '/',
                        SUM(IF(DATE_ADD(dt, INTERVAL %s DAY) > '%s', 0, CAST(SPLIT_PART(amount_d1 , '/', 1) AS DECIMAL(10, 2))))
                    ) AS trend_day%s,""".formatted(day, day, day, day - 1, now, day - 1, now, day));
        }
        for (int month = 4; month <= 12; month++) {
            trendDay.append("""
                    CONCAT(
                        SUM(CAST(SPLIT_PART(amount_m%s , '/', 1) AS DECIMAL(10, 2))), '/',
                        SUM(CAST(SPLIT_PART(amount_m%s, '/', 6) AS DECIMAL(10, 2))), '/',
                        SUM(CAST(SPLIT_PART(amount_m%s, '/', 2) AS BIGINT)), '/',
                        SUM(IF(DATE_ADD(dt, INTERVAL %s month) > '%s', 0, cost)), '/',
                        SUM(IF(DATE_ADD(dt, INTERVAL %s month) > '%s', 0, CAST(SPLIT_PART(amount_d1 , '/', 1) AS DECIMAL(10, 2))))
                    ) AS trend_month%s,""".formatted(month, month, month, month, now, month, now, month));
        }
        return trendDay.toString();
    }

    private String roiDay() {
        StringBuilder roiDay = new StringBuilder(StringUtils.EMPTY);
        LocalDate now = LocalDate.now();
        for (int day = 1; day <= 29; day++) {
            roiDay.append("""
                    round(if(SUM(IF(DATE_ADD(dt, INTERVAL %s DAY) > '%s', 0, cost)) > 0, sum(SPLIT_PART(amount_d%s, '/', 6)) / SUM(IF(DATE_ADD(dt, INTERVAL %s DAY) > '%s', 0, cost)), 0), 4) as roi_day%s,
                    """
                    .formatted(day - 1, now, day, day - 1, now, day));
        }
        return roiDay.toString();
    }

    /**
     * 推广总数据SQL
     *
     * @return
     */
    private String accountAgentTotalSumDayNSql() {
        return """
                select
                a.source_system as source_system,
                a.agent_id as agent_id,
                a.account_id as account_id
                from
                	ads_account_agent_day a
                """;
    }

    /**
     * 推广总数据SQL
     *
     * @return
     */
    private String accountAgentTotalSumSql() {
        return """
                select
                	sum(a.cost) as cost,
                	sum(a.plan_count) as ad_plan_count,
                	sum(a.view_count) as view_count,
                	sum(a.click_count) as click_count,
                	sum(a.change_count) as convert_count,
                	round(if(sum(a.click_count) > 0, sum(a.change_count) / sum(a.click_count), 0), 4) as convert_rate,
                	round(if(sum(a.change_count) > 0, sum(a.cost) / sum(a.change_count), 0), 4) as avg_convert_cost,
                	sum(a.depth_change_count) as deep_convert_count,
                	round(if(sum(a.change_count) > 0, sum(a.depth_change_count) / sum(a.change_count), 0), 4) as deep_convert_rate,
                	round(if(sum(a.depth_change_count) > 0, sum(a.cost) / sum(a.depth_change_count), 0), 4) as deep_convert_cost,
                	round(if(sum(a.view_count) > 0, sum(a.cost) / sum(a.view_count) * 1000, 0), 4) as thousand_view_cost,
                	round(if(sum(a.view_count) > 0, sum(a.click_count) / sum(a.view_count), 0), 4) as click_rate,
                	round(if(sum(a.click_count) > 0, sum(a.cost) / sum(a.click_count), 0), 2) as avg_click_cost,
                	sum(a.download_start) as app_download_count,
                	round(if(sum(a.download_start) > 0, sum(a.cost) / sum(a.download_start), 0), 4) as app_download_cost,
                	round(if(sum(a.click_count) > 0, sum(a.download_start) / sum(a.click_count), 0), 4) as app_download_rate,
                	sum(a.download_finish) as download_finish,
                	round(if(sum(a.download_finish) > 0, sum(a.cost) / sum(a.download_finish), 0), 4) as download_finish_cost,
                	round(if(sum(a.download_start) > 0, sum(a.download_finish) / sum(a.download_start), 0), 4) as download_finish_rate,
                	sum(a.install_finish) as app_install_count,
                	round(if(sum(a.install_finish) > 0, sum(a.cost) / sum(a.install_finish), 0), 4) as app_install_cost,
                	round(if(sum(a.download_finish) > 0, sum(a.install_finish) / sum(a.download_finish), 0), 4) as app_install_rate,
                	sum(a.active) as app_active_count,
                	round(if(sum(a.active) > 0, sum(a.cost) / sum(a.active), 0), 4) as app_active_cost,
                	round(if(sum(a.click_count) > 0, sum(a.active) / sum(a.click_count), 0), 4) as app_active_rate,
                	sum(a.register_num) as reg_user_count,
                	round(if(sum(a.register_num) > 0, sum(a.cost) / sum(a.register_num), 0), 4) as reg_cost,
                	sum(a.first_new_user_amount_count) as first_user_recharge_count,
                	sum(a.first_new_user_amount_num) as first_user_recharge_user,
                	sum(a.first_new_user_amount) as first_user_recharge_money,
                	sum(a.first_new_user_amount_count) as user_recharge_count,
                	sum(a.first_new_user_amount_num) as user_recharge_user,
                	sum(a.first_new_user_amount) as user_recharge_money,
                	sum(a.old_amount_count) as old_user_recharge_count,
                	sum(a.old_amount_num) as old_user_recharge_user,
                	sum(a.old_amount) as old_user_recharge_money,
                	sum(a.new_user_total_amount_count) as new_user_recharge_count,
                	sum(a.new_user_total_amount_num) as new_user_recharge_user,
                	sum(a.new_user_total_amount) as new_user_recharge_money,
                	round(if(sum(a.cost) > 0, sum(a.first_new_user_amount) / sum(a.cost), 0), 4) as first_recovery_rate,
                	round(if(sum(a.cost) > 0, sum(a.new_user_total_amount) / sum(a.cost), 0), 4) as today_recovery_rate,
                	sum(a.gross_profit) as gross_margin,
                         """
                + roiDay() +
                """
                            round(if(SUM(IF(DATE_ADD(dt, INTERVAL 29 DAY) > CURRENT_DATE(), 0, cost)) > 0, sum(SPLIT_PART(amount_d30, '/', 6)) / SUM(IF(DATE_ADD(dt, INTERVAL 29 DAY) > CURRENT_DATE(), 0, cost)), 0), 4) as roi_day30,
                            round(if(SUM(IF(DATE_ADD(dt, INTERVAL 59 DAY) > CURRENT_DATE(), 0, cost)) > 0, sum(SPLIT_PART(amount_d60, '/', 6)) / SUM(IF(DATE_ADD(dt, INTERVAL 59 DAY) > CURRENT_DATE(), 0, cost)), 0), 4) as roi_day60,
                            round(if(SUM(IF(DATE_ADD(dt, INTERVAL 89 DAY) > CURRENT_DATE(), 0, cost)) > 0, sum(SPLIT_PART(amount_d90, '/', 6)) / SUM(IF(DATE_ADD(dt, INTERVAL 89 DAY) > CURRENT_DATE(), 0, cost)), 0), 4) as roi_day90,
                            round(if(SUM(IF(DATE_ADD(dt, INTERVAL 179 DAY) > CURRENT_DATE(), 0, cost)) > 0, sum(SPLIT_PART(amount_m6, '/', 6)) / SUM(IF(DATE_ADD(dt, INTERVAL 179 DAY) > CURRENT_DATE(), 0, cost)), 0), 4) as roi_day180,
                            round(if(SUM(IF(DATE_ADD(dt, INTERVAL 359 DAY) > CURRENT_DATE(), 0, cost)) > 0, sum(SPLIT_PART(amount_m12, '/', 6)) / SUM(IF(DATE_ADD(dt, INTERVAL 359 DAY) > CURRENT_DATE(), 0, cost)), 0), 4) as roi_year1,
                            round(if(SUM(cost) > 0, sum(SPLIT_PART(amount_sum, '/', 6)) / SUM(cost), 0), 4) as roi_total,
                        """
                +
                """
                        	round(if(sum(a.register_num) > 0, sum(a.first_new_user_amount_num) / sum(a.register_num), 0), 4) as first_recharge_rate,
                        	round(if(sum(a.register_num) > 0, sum(a.new_user_total_amount_num) / sum(a.register_num), 0), 4) as today_recharge_rate,
                        	round(if(sum(a.first_new_user_amount_count) > 0, sum(a.first_new_user_amount) / sum(a.first_new_user_amount_count), 0), 4) as avg_first_user_recharge,
                        	round(if(sum(a.new_user_total_amount_count) > 0, sum(a.new_user_total_amount) / sum(a.new_user_total_amount_count), 0), 4) as avg_today_recharge,
                        	round(if(sum(a.first_new_user_amount_num) > 0, sum(a.cost) / sum(a.first_new_user_amount_num), 0), 4) as first_recharge_cost,
                        	round(if(sum(a.new_user_total_amount_num) > 0, sum(a.cost) / sum(a.new_user_total_amount_num), 0), 4) as today_recharge_cost,
                        	round(if(sum(a.new_user_total_amount_num) > 0, sum(a.reg_order_user_again) / sum(a.new_user_total_amount_num), 0), 4) as today_repeat_recharge_rate,
                        	round(if(sum(a.register_num) > 0, sum(a.new_user_total_amount) / sum(a.register_num), 0), 4) as new_reg_arpu,
                        	round(if(sum(a.first_new_user_amount_num) > 0, sum(a.first_new_user_amount) / sum(a.first_new_user_amount_num), 0), 4) as first_recharge_arpu,
                        	round(if(sum(a.new_user_total_amount_num) > 0, sum(a.new_user_total_amount) / sum(a.new_user_total_amount_num), 0), 4) as today_recharge_arpu,
                        	round(if(sum(first_new_user_amount_count) > 0, sum(cost) / sum(first_new_user_amount_count), 0), 4) as first_recharge_count_cost,
                        	round(if(sum(new_user_total_amount_count) > 0, sum(cost) / sum(new_user_total_amount_count), 0), 4) as new_user_recharge_count_cost,
                        	sum(a.hundred_user_num) as hundred_user_num,
                        	round(if(sum(a.hundred_user_num) > 0, sum(a.cost) / sum(a.hundred_user_num), 0), 4) as hundred_user_num_cost,
                        	sum(first_role_num) as first_role_num,
                            sum(new_user_total_role_num) as new_user_total_role_num
                        from
                        	ads_account_agent_day a
                        """;
    }

    /**
     * 推广总数据SQL
     *
     * @return
     */
    private String accountAgentTotalSql() {
        return """
                select
                	concat(a.account_id, a.agent_id) as id,
                	a.source_system,
                	a.account_id,
                	a.agent_id,
                	max(a.agent_name) as agent_name,
                	max(a.account_type) as account_type,
                	max(a.pitcher) as sys_user_name,
                	max(a.game_cp) as cp_name,
                	max(a.game_id) as game_id,
                	max(a.game_name) as game_name,
                	max(a.game_type) as game_type,
                	max(a.pitcher_id) as sys_user_id,
                	sum(a.cost) as cost,
                	sum(a.plan_count) as ad_plan_count,
                	sum(a.view_count) as view_count,
                	sum(a.click_count) as click_count,
                	sum(a.change_count) as convert_count,
                	round(if(sum(a.click_count) > 0, sum(a.change_count) / sum(a.click_count), 0), 4) as convert_rate,
                	round(if(sum(a.change_count) > 0, sum(a.cost) / sum(a.change_count), 0), 4) as avg_convert_cost,
                	sum(a.depth_change_count) as deep_convert_count,
                	round(if(sum(a.change_count) > 0, sum(a.depth_change_count) / sum(a.change_count), 0), 4) as deep_convert_rate,
                	round(if(sum(a.depth_change_count) > 0, sum(a.cost) / sum(a.depth_change_count), 0), 4) as deep_convert_cost,
                	round(if(sum(a.view_count) > 0, sum(a.cost) / sum(a.view_count) * 1000, 0), 4) as thousand_view_cost,
                	round(if(sum(a.view_count) > 0, sum(a.click_count) / sum(a.view_count), 0), 4) as click_rate,
                	round(if(sum(a.click_count) > 0, sum(a.cost) / sum(a.click_count), 0), 4) as avg_click_cost,
                	sum(a.download_start) as app_download_count,
                	round(if(sum(a.download_start) > 0, sum(a.cost) / sum(a.download_start), 0), 4) as app_download_cost,
                	round(if(sum(a.click_count) > 0, sum(a.download_start) / sum(a.click_count), 0), 4) as app_download_rate,
                	sum(a.download_finish) as download_finish,
                	round(if(sum(a.download_finish) > 0, sum(a.cost) / sum(a.download_finish), 0), 4) as download_finish_cost,
                	round(if(sum(a.download_start) > 0, sum(a.download_finish) / sum(a.download_start), 0), 4) as download_finish_rate,
                	sum(a.install_finish) as app_install_count,
                	round(if(sum(a.install_finish) > 0, sum(a.cost) / sum(a.install_finish), 0), 4) as app_install_cost,
                	round(if(sum(a.download_finish) > 0, sum(a.install_finish) / sum(a.download_finish), 0), 4) as app_install_rate,
                	sum(a.active) as app_active_count,
                	round(if(sum(a.active) > 0, sum(a.cost) / sum(a.active), 0), 4) as app_active_cost,
                	round(if(sum(a.click_count) > 0, sum(a.active) / sum(a.click_count), 0), 4) as app_active_rate,
                	sum(a.register_num) as reg_user_count,
                	round(if(sum(a.register_num) > 0, sum(a.cost) / sum(a.register_num), 0), 4) as reg_cost,
                	sum(a.first_new_user_amount_count) as first_user_recharge_count,
                	sum(a.first_new_user_amount_num) as first_user_recharge_user,
                	sum(a.first_new_user_amount) as first_user_recharge_money,
                	sum(a.first_new_user_amount_count) as user_recharge_count,
                	sum(a.first_new_user_amount_num) as user_recharge_user,
                	sum(a.first_new_user_amount) as user_recharge_money,
                	sum(a.old_amount_count) as old_user_recharge_count,
                	sum(a.old_amount_num) as old_user_recharge_user,
                	sum(a.old_amount) as old_user_recharge_money,
                	sum(a.new_user_total_amount_count) as new_user_recharge_count,
                	sum(a.new_user_total_amount_num) as new_user_recharge_user,
                	sum(a.new_user_total_amount) as new_user_recharge_money,
                	round(if(sum(a.cost) > 0, sum(a.first_new_user_amount) / sum(a.cost), 0), 4) as first_recovery_rate,
                	round(if(sum(a.cost) > 0, sum(a.new_user_total_amount) / sum(a.cost), 0), 4) as today_recovery_rate,
                	sum(a.gross_profit) as gross_margin,
                         """
                + roiDay() +
                """
                            round(if(SUM(IF(DATE_ADD(dt, INTERVAL 29 DAY) > CURRENT_DATE(), 0, cost)) > 0, sum(SPLIT_PART(amount_d30, '/', 6)) / SUM(IF(DATE_ADD(dt, INTERVAL 29 DAY) > CURRENT_DATE(), 0, cost)), 0), 4) as roi_day30,
                            round(if(SUM(IF(DATE_ADD(dt, INTERVAL 59 DAY) > CURRENT_DATE(), 0, cost)) > 0, sum(SPLIT_PART(amount_d60, '/', 6)) / SUM(IF(DATE_ADD(dt, INTERVAL 59 DAY) > CURRENT_DATE(), 0, cost)), 0), 4) as roi_day60,
                            round(if(SUM(IF(DATE_ADD(dt, INTERVAL 89 DAY) > CURRENT_DATE(), 0, cost)) > 0, sum(SPLIT_PART(amount_d90, '/', 6)) / SUM(IF(DATE_ADD(dt, INTERVAL 89 DAY) > CURRENT_DATE(), 0, cost)), 0), 4) as roi_day90,
                            round(if(SUM(IF(DATE_ADD(dt, INTERVAL 179 DAY) > CURRENT_DATE(), 0, cost)) > 0, sum(SPLIT_PART(amount_m6, '/', 6)) / SUM(IF(DATE_ADD(dt, INTERVAL 179 DAY) > CURRENT_DATE(), 0, cost)), 0), 4) as roi_day180,
                            round(if(SUM(IF(DATE_ADD(dt, INTERVAL 359 DAY) > CURRENT_DATE(), 0, cost)) > 0, sum(SPLIT_PART(amount_m12, '/', 6)) / SUM(IF(DATE_ADD(dt, INTERVAL 359 DAY) > CURRENT_DATE(), 0, cost)), 0), 4) as roi_year1,
                            round(if(SUM(cost) > 0, sum(SPLIT_PART(amount_sum, '/', 6)) / SUM(cost), 0), 4) as roi_total,
                        """
                +
                """
                        	round(if(sum(a.register_num) > 0, sum(a.first_new_user_amount_num) / sum(a.register_num), 0), 4) as first_recharge_rate,
                        	round(if(sum(a.register_num) > 0, sum(a.new_user_total_amount_num) / sum(a.register_num), 0), 4) as today_recharge_rate,
                        	round(if(sum(a.first_new_user_amount_count) > 0, sum(a.first_new_user_amount) / sum(a.first_new_user_amount_count), 0), 4) as avg_first_user_recharge,
                        	round(if(sum(a.new_user_total_amount_count) > 0, sum(a.new_user_total_amount) / sum(a.new_user_total_amount_count), 0), 4) as avg_today_recharge,
                        	round(if(sum(a.first_new_user_amount_num) > 0, sum(a.cost) / sum(a.first_new_user_amount_num), 0), 4) as first_recharge_cost,
                        	round(if(sum(a.new_user_total_amount_num) > 0, sum(a.cost) / sum(a.new_user_total_amount_num), 0), 4) as today_recharge_cost,
                        	round(if(sum(a.new_user_total_amount_num) > 0, sum(a.reg_order_user_again) / sum(a.new_user_total_amount_num), 0), 4) as today_repeat_recharge_rate,
                        	round(if(sum(a.register_num) > 0, sum(a.new_user_total_amount) / sum(a.register_num), 0), 4) as new_reg_arpu,
                        	round(if(sum(a.first_new_user_amount_num) > 0, sum(a.first_new_user_amount) / sum(a.first_new_user_amount_num), 0), 4) as first_recharge_arpu,
                        	round(if(sum(a.new_user_total_amount_num) > 0, sum(a.new_user_total_amount) / sum(a.new_user_total_amount_num), 0), 4) as today_recharge_arpu,
                        	round(if(sum(first_new_user_amount_count) > 0, sum(cost) / sum(first_new_user_amount_count), 0), 4) as first_recharge_count_cost,
                        	round(if(sum(new_user_total_amount_count) > 0, sum(cost) / sum(new_user_total_amount_count), 0), 4) as new_user_recharge_count_cost,
                        	sum(a.hundred_user_num) as hundred_user_num,
                        	round(if(sum(a.hundred_user_num) > 0, sum(a.cost) / sum(a.hundred_user_num), 0), 4) as hundred_user_num_cost,
                        	sum(first_role_num) as first_role_num,
                            sum(new_user_total_role_num) as new_user_total_role_num
                        from
                        	ads_account_agent_day a
                        """;
    }

    @Data
    @NoArgsConstructor
    @AllArgsConstructor
    @Builder
    public static class DayN {
        private BigDecimal rechargeMoney = BigDecimal.ZERO;
        private Long rechargeCount = 0L;
        private Long rechargeNum = 0L;
        private Long repeatNum = 0L;
    }

    @Data
    @NoArgsConstructor
    @AllArgsConstructor
    @Builder
    public static class ShowRecharge {
        private Long showRechargeCount;

        private Long showRechargeUser;

        private BigDecimal showRechargeMoney;

        private BigDecimal avgShowUserRecharge;

        private BigDecimal showRechargeArpu;
    }

    @Data
    @NoArgsConstructor
    @AllArgsConstructor
    @Builder
    public static class AgentAccount {
        private String sourceSystem;

        private Long accountId;

        private Long agentId;
    }
}
