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

import com.github.sd4324530.jtuple.Tuple2;
import com.google.common.base.CaseFormat;
import com.zanxiang.game.data.serve.component.DataPowerComponent;
import com.zanxiang.game.data.serve.pojo.dto.TencentCreativeDayDTO;
import com.zanxiang.game.data.serve.pojo.enums.OrderByEnum;
import com.zanxiang.game.data.serve.pojo.vo.PromotionRechargeTrendVO;
import com.zanxiang.game.data.serve.pojo.vo.TencentCreativeDayTotalVO;
import com.zanxiang.game.data.serve.pojo.vo.TencentCreativeDayVO;
import com.zanxiang.game.data.serve.service.IAdsCreativeDayService;
import com.zanxiang.game.data.serve.utils.Page;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.nutz.dao.Cnd;
import org.nutz.dao.Dao;
import org.nutz.dao.Sqls;
import org.nutz.dao.pager.Pager;
import org.nutz.dao.sql.Criteria;
import org.nutz.dao.sql.Sql;
import org.nutz.dao.util.Daos;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.BeanWrapper;
import org.springframework.beans.BeanWrapperImpl;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

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

/**
 * packageName com.zanxiang.game.data.serve.service.impl
 *
 * @author ZhangXianyu
 * @date 2025/1/14
 * @description 创意日数据
 */
@Service
public class AdsCreativeDayServiceImpl implements IAdsCreativeDayService {


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

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

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

            //返回最终数据
            return vo;
        }).collect(Collectors.toList());
        //返回查询得结果
        return new Page<>(list, pager);
    }

    @Override
    public TencentCreativeDayTotalVO getTencentCreativeTotalData(TencentCreativeDayDTO dto) {
        Tuple2<List<Long>, List<Long>> poerInfo = dataPowerComponent.getPowerInfo();
        List<Long> userIds = dto.getPitcherId() == null ? poerInfo.first : Collections.singletonList(dto.getPitcherId());
        List<Long> gameIds = dto.getGameId() == null ? poerInfo.second : Collections.singletonList(dto.getGameId());
//        ArrayList<Long> userIds = new ArrayList<>();
//        ArrayList<Long> gameIds = new ArrayList<>();
        //创建查询条件
        Criteria cri = Cnd.cri();
        if (dto.getAccountId() != null) {
            //拼接推广账号Id查询条件
            cri.where().andEquals("account_id", dto.getAccountId());
        }
        if (StringUtils.isNotBlank(dto.getAccountName())) {
            //拼接推广账号名称查询条件
            cri.where().andLike("account_name", dto.getAccountName());
        }
        if (StringUtils.isNotBlank(dto.getAccountType())) {
            //推广账号类型
            cri.where().andEquals("account_type", dto.getAccountType());
        }
        if (CollectionUtils.isNotEmpty(userIds)) {
            cri.where().andInList("pitcher_id", userIds);
        }
        if (CollectionUtils.isNotEmpty(dto.getAgentId())) {
            cri.where().andInList("agent_id", dto.getAgentId());
        }
        if (StringUtils.isNotBlank(dto.getCpName())) {
            cri.where().andEquals("cp_name", dto.getCpName());
        }
        if (CollectionUtils.isNotEmpty(gameIds)) {
            cri.where().andInList("game_id", gameIds);
        }
        if (dto.getClassify() != null) {
            cri.where().andEquals("classify", dto.getClassify());
        }
        if (StringUtils.isNotBlank(dto.getPromotionName())) {
            cri.where().andLike("promotion_name", dto.getPromotionName());
        }
        if (dto.getPromotionId() != null) {
            cri.where().andEquals("promotion_id", dto.getPromotionId());
        }
        if (StringUtils.isNotBlank(dto.getStatus())) {
            cri.where().andEquals("status", dto.getStatus());
        }
        if(StringUtils.isNotEmpty(dto.getCreativeStatus())){
            cri.where().andEquals("configured_status", dto.getCreativeStatus());
        }
        if (StringUtils.isNotBlank(dto.getDynamicCreativeName())) {
            cri.where().andLike("dynamic_creative_name", dto.getDynamicCreativeName());
        }
        if (dto.getDynamicCreativeId() != null) {
            cri.where().andEquals("dynamic_creative_id", dto.getDynamicCreativeId());
        }
        if (dto.getCostBeginDate() != null && dto.getCostEndDate() != null) {
            cri.where().andBetween("dt", dto.getCostBeginDate(), dto.getCostEndDate());
        }
        if (StringUtils.isNotBlank(dto.getSourceSystem())) {
            cri.where().andEquals("source_system", dto.getSourceSystem());
        }
        //创建sql
        Sql creativeDayTotalSql = Sqls.create(tencentCreativeDayTotalSql() + cri);
        //设置回传对象
        creativeDayTotalSql.setCallback(Sqls.callback.entity());
        creativeDayTotalSql.setEntity(dao.getEntity(TencentCreativeDayTotalVO.class));
        //执行sql
        dao.execute(creativeDayTotalSql);
        //得到结果
        TencentCreativeDayTotalVO vo = creativeDayTotalSql.getObject(TencentCreativeDayTotalVO.class);

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

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

            return vo;
        }

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

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

        return vo;
    }


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


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

        return notNullFieldSet.toArray(notNullField);
    }


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



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


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


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



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

        if (StringUtils.isNotBlank(dto.getDynamicCreativeName())) {
            cri.where().andLike("dynamic_creative_name", dto.getDynamicCreativeName());
        }
        if (dto.getDynamicCreativeId() != null) {
            cri.where().andEquals("dynamic_creative_id", dto.getDynamicCreativeId());
        }
        if (dto.getCostBeginDate() != null && dto.getCostEndDate() != null) {
            cri.where().andBetween("dt", dto.getCostBeginDate(), dto.getCostEndDate());
        }
        if (StringUtils.isNotBlank(dto.getSourceSystem())) {
            cri.where().andEquals("source_system", dto.getSourceSystem());
        }
        if (needGroupBy) {
            //拼接分组条件
            cri.getGroupBy().groupBy("dynamic_creative_id");
        }

        return cri;
    }

}