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.AdsOrderDetailDTO;
import com.zanxiang.game.data.serve.pojo.dto.AdsOrderDetailListDTO;
import com.zanxiang.game.data.serve.pojo.dto.AdsOrderDetailTotalDTO;
import com.zanxiang.game.data.serve.pojo.entity.AdsOrderDetail;
import com.zanxiang.game.data.serve.pojo.entity.TPayCallCpLog;
import com.zanxiang.game.data.serve.pojo.enums.OrderByEnum;
import com.zanxiang.game.data.serve.pojo.vo.AdsOrderDetailTotalVO;
import com.zanxiang.game.data.serve.pojo.vo.AdsOrderDetailVO;
import com.zanxiang.game.data.serve.pojo.vo.OrderCPDetailVO;
import com.zanxiang.game.data.serve.service.IAdsOrderDetailService;
import com.zanxiang.game.data.serve.utils.Page;
import com.zanxiang.module.util.bean.BeanUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.nutz.dao.Cnd;
import org.nutz.dao.Dao;
import org.nutz.dao.Sqls;
import org.nutz.dao.pager.Pager;
import org.nutz.dao.sql.Criteria;
import org.nutz.dao.sql.Sql;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.Collections;
import java.util.List;
import java.util.stream.Collectors;

@Slf4j
@Service
public class AdsOrderDetailServiceImpl implements IAdsOrderDetailService {

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

    @Override
    public Page<AdsOrderDetailVO> listOfPage(AdsOrderDetailListDTO dto) {
        Tuple2<List<Long>, List<Long>> poerInfo = dataPowerComponent.getPowerInfo(dto.getSourceSystem());
        List<Long> userIds = dto.getPitcherId() == null ? poerInfo.first : Collections.singletonList(Long.parseLong(dto.getPitcherId()));
        List<Long> paramGameIds = dto.getGameId();
        if (CollectionUtils.isNotEmpty(paramGameIds)) {
            //将paramGameIds中不在authGameIds中的游戏id过滤掉
            if (poerInfo.second != null) {
                paramGameIds.retainAll(poerInfo.second);
            }
        } else {
            paramGameIds = poerInfo.second;
        }
        Criteria cri = Cnd.cri();
        if (StringUtils.isNotBlank(dto.getSourceSystem())) {
            cri.where().andEquals("source_system", dto.getSourceSystem());
        }
        if (StringUtils.isNotBlank(dto.getOrderId())) {
            cri.where().andEquals("order_id", dto.getOrderId());
        }
        if (StringUtils.isNotBlank(dto.getCpOrderId())) {
            cri.where().andEquals("cp_order_id", dto.getCpOrderId());
        }
        if (StringUtils.isNotBlank(dto.getMerchantOrderNo())) {
            cri.where().andEquals("merchant_order_no", dto.getMerchantOrderNo());
        }
        if (StringUtils.isNotBlank(dto.getUserId())) {
            cri.where().andEquals("game_user_id", dto.getUserId());
        }
        if (StringUtils.isNotBlank(dto.getUsername())) {
            cri.where().andEquals("username", dto.getUsername());
        }
        if (StringUtils.isNotBlank(dto.getServerName())) {
            cri.where().andLike("server_name", dto.getServerName());
        }
        if (StringUtils.isNotBlank(dto.getSourceServerName())) {
            cri.where().andLike("source_server_name", dto.getSourceServerName());
        }
        if (paramGameIds != null) {
            cri.where().andInList("order_game_id", paramGameIds);
        }
        if (CollectionUtils.isNotEmpty(dto.getChannelId())) {
            cri.where().andInStrList("agent_id", dto.getChannelId());
        }
        if (StringUtils.isNotBlank(dto.getAccountId())) {
            cri.where().andEquals("account_id", dto.getAccountId());
        }
        if (userIds != null) {
            cri.where().andInList("zx_pitcher_id", userIds);
        }
        if (StringUtils.isNotBlank(dto.getDeviceSystem())) {
            cri.where().andEquals("os", dto.getDeviceSystem());
        }
        if (StringUtils.isNotBlank(dto.getProductName())) {
            cri.where().andEquals("product_name", dto.getProductName());
        }
        if (StringUtils.isNotBlank(dto.getPayWay())) {
            cri.where().andEquals("payway", dto.getPayWay());
        }
        if (StringUtils.isNotBlank(dto.getMerchantNo())) {
            cri.where().andEquals("pay_account_id", dto.getMerchantNo());
        }
        if (StringUtils.isNotBlank(dto.getStatus())) {
            cri.where().andEquals("order_status", dto.getStatus());
        }
        if (StringUtils.isNotBlank(dto.getCpStatus())) {
            cri.where().andEquals("cp_status", dto.getCpStatus());
        }
        if (StringUtils.isNotBlank(dto.getIsFirstRecharge())) {
            cri.where().andEquals("is_first_recharge", dto.getIsFirstRecharge());
        }
        if (dto.getBeginDate() != null && dto.getEndDate() != null) {
            //下单时间，订单创建时间
            cri.where().andBetween("day", dto.getBeginDate(), dto.getEndDate());
        }
        if (dto.getRegTimeStart() != null && dto.getRegTimeEnd() != null) {
            //玩家注册时间
            cri.where().andBetween("DATE(reg_time)", dto.getRegTimeStart(), dto.getRegTimeEnd());
        }
        if (dto.getPayTimeStart() != null && dto.getPayTimeEnd() != null) {
            //支付时间，订单充值时间
            cri.where().andBetween("DATE(pay_time)", dto.getPayTimeStart(), dto.getPayTimeEnd());
        }
        if (dto.getRegPayIntervalTimeMin() != null) {
            cri.where().andGTE("TIMESTAMPDIFF(MINUTE, reg_time, pay_time)", dto.getRegPayIntervalTimeMin());
        }
        if (dto.getRegPayIntervalTimeMax() != null) {
            cri.where().andLTE("TIMESTAMPDIFF(MINUTE, reg_time, pay_time)", dto.getRegPayIntervalTimeMax());
        }
        if (StringUtils.isNotBlank(dto.getRoleName())) {
            cri.where().andLike("role_name", dto.getRoleName());
        }
        if (StringUtils.isNotBlank(dto.getPromotionId())) {
            //计划ID
            cri.where().andEquals("promotion_id", dto.getPromotionId());
        }
        if (StringUtils.isNotBlank(dto.getProjectId())) {
            //广告ID
            cri.where().andEquals("project_id", dto.getProjectId());
        }
        if (dto.getBackStatus() != null) {
            //回传状态
            cri.where().andEquals("back_status", dto.getBackStatus());
        }
        if (StringUtils.isNotEmpty(dto.getCpName())) {
            //cp名称
            cri.where().andLike("cp_name", dto.getCpName());
        }
        if (StringUtils.isNotBlank(dto.getOrderAgentType())) {
            if ("buy".equals(dto.getOrderAgentType())) {
                //查询买量数据
                cri.where().andNotEquals("agent_id", 0);
            } else if ("nature".equals(dto.getOrderAgentType())) {
                //查询自然量数据
                cri.where().andEquals("agent_id", 0);
            }
        }
        //添加判断，除去BG的数据 BG的数据在我们这找不到用户以及渠道
        if (StringUtils.isNotEmpty(dto.getSourceSystem()) && dto.getSourceSystem().contains("BG")) {
            cri.where().andNotIsNull("agent_name");
            cri.where().andNotIsNull("username");
        }

        //pager
        Pager pager = dto.toPage();
        Sql countSql = Sqls.create(getCountSql() + cri);
        countSql.setCallback(Sqls.callback.integer());
        dao.execute(countSql);
        //获取记录数
        pager.setRecordCount(countSql.getInt());

        //默认排序方式
        if (StringUtils.isBlank(dto.getSortType())) {
            dto.setSortType(OrderByEnum.DESC.getOrderType());
        }
        if (StringUtils.isBlank(dto.getSortFiled())) {
            cri.getOrderBy().orderBy("day", dto.getSortType());
            cri.getOrderBy().orderBy("order_create_time", dto.getSortType());
        } else {
            cri.getOrderBy().orderBy(CaseFormat.LOWER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, dto.getSortFiled()), dto.getSortType());
        }

        Sql sql = Sqls.create(getOrderDetailSql() + cri);
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(AdsOrderDetailVO.class));
        sql.setPager(pager);
        //执行获取结果
        dao.execute(sql);
        List<AdsOrderDetailVO> list = sql.getList(AdsOrderDetailVO.class).stream().map(vo -> {
            //去除‘null’字符串
            if ("null".equals(vo.getPayAccountId())) {
                vo.setPayAccountId(null);
            }
            return vo;
        }).collect(Collectors.toList());

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

    @Override
    public AdsOrderDetailTotalVO orderTotal(AdsOrderDetailTotalDTO dto) {
        Tuple2<List<Long>, List<Long>> poerInfo = dataPowerComponent.getPowerInfo(dto.getSourceSystem());
        List<Long> userIds = dto.getPitcherId() == null ? poerInfo.first : Collections.singletonList(Long.parseLong(dto.getPitcherId()));
        List<Long> gameIds = CollectionUtils.isEmpty(dto.getGameId()) ? poerInfo.second : dto.getGameId();
        Criteria cri = Cnd.cri();
        if (StringUtils.isNotBlank(dto.getSourceSystem())) {
            cri.where().andEquals("source_system", dto.getSourceSystem());
        }
        if (StringUtils.isNotBlank(dto.getOrderId())) {
            cri.where().andEquals("order_id", dto.getOrderId());
        }
        if (StringUtils.isNotBlank(dto.getCpOrderId())) {
            cri.where().andEquals("cp_order_id", dto.getCpOrderId());
        }
        if (StringUtils.isNotBlank(dto.getMerchantOrderNo())) {
            cri.where().andEquals("merchant_order_no", dto.getMerchantOrderNo());
        }
        if (StringUtils.isNotBlank(dto.getUserId())) {
            cri.where().andEquals("game_user_id", dto.getUserId());
        }
        if (StringUtils.isNotBlank(dto.getUsername())) {
            cri.where().andEquals("username", dto.getUsername());
        }
        if (StringUtils.isNotBlank(dto.getServerName())) {
            cri.where().andLike("server_name", dto.getServerName());
        }
        if (StringUtils.isNotBlank(dto.getSourceServerName())) {
            cri.where().andLike("source_server_name", dto.getSourceServerName());
        }
        if (gameIds != null) {
            cri.where().andInList("order_game_id", gameIds);
        }
        if (CollectionUtils.isNotEmpty(dto.getChannelId())) {
            cri.where().andInStrList("agent_id", dto.getChannelId());
        }
        if (StringUtils.isNotBlank(dto.getAccountId())) {
            cri.where().andEquals("account_id", dto.getAccountId());
        }
        if (userIds != null) {
            cri.where().andInList("zx_pitcher_id", userIds);
        }
        if (StringUtils.isNotBlank(dto.getDeviceSystem())) {
            cri.where().andEquals("os", dto.getDeviceSystem());
        }
        if (StringUtils.isNotBlank(dto.getProductName())) {
            cri.where().andEquals("product_name", dto.getProductName());
        }
        if (StringUtils.isNotBlank(dto.getPayWay())) {
            cri.where().andEquals("payway", dto.getPayWay());
        }
        if (StringUtils.isNotBlank(dto.getMerchantNo())) {
            cri.where().andEquals("pay_account_id", dto.getMerchantNo());
        }
        if (StringUtils.isNotBlank(dto.getStatus())) {
            cri.where().andEquals("order_status", dto.getStatus());
        }
        if (StringUtils.isNotBlank(dto.getCpStatus())) {
            cri.where().andEquals("cp_status", dto.getCpStatus());
        }
        if (StringUtils.isNotBlank(dto.getIsFirstRecharge())) {
            cri.where().andEquals("is_first_recharge", dto.getIsFirstRecharge());
        }
        if (dto.getBeginDate() != null && dto.getEndDate() != null) {
            //下单时间，订单创建时间
            cri.where().andBetween("day", dto.getBeginDate(), dto.getEndDate());
        }
        if (dto.getRegTimeStart() != null && dto.getRegTimeEnd() != null) {
            //玩家注册时间
            cri.where().andBetween("DATE(reg_time)", dto.getRegTimeStart(), dto.getRegTimeEnd());
        }
        if (dto.getPayTimeStart() != null && dto.getPayTimeEnd() != null) {
            //支付时间，订单充值时间
            cri.where().andBetween("DATE(pay_time)", dto.getPayTimeStart(), dto.getPayTimeEnd());
        }
        if (dto.getRegPayIntervalTimeMin() != null) {
            cri.where().andGTE("TIMESTAMPDIFF(MINUTE, reg_time, pay_time)", dto.getRegPayIntervalTimeMin());
        }
        if (dto.getRegPayIntervalTimeMax() != null) {
            cri.where().andLTE("TIMESTAMPDIFF(MINUTE, reg_time, pay_time)", dto.getRegPayIntervalTimeMax());
        }
        if (StringUtils.isNotBlank(dto.getRoleName())) {
            cri.where().andLike("role_name", dto.getRoleName());
        }
        if (StringUtils.isNotBlank(dto.getPromotionId())) {
            //计划ID
            cri.where().andEquals("promotion_id", dto.getPromotionId());
        }
        if (StringUtils.isNotBlank(dto.getProjectId())) {
            //广告ID
            cri.where().andEquals("project_id", dto.getProjectId());
        }
        if (dto.getBackStatus() != null) {
            //回传状态
            cri.where().andEquals("back_status", dto.getBackStatus());
        }
        if (StringUtils.isNotEmpty(dto.getCpName())) {
            //cp名称
            cri.where().andLike("cp_name", dto.getCpName());
        }
        if (StringUtils.isNotBlank(dto.getOrderAgentType())) {
            if ("buy".equals(dto.getOrderAgentType())) {
                //查询买量数据
                cri.where().andNotEquals("agent_id", 0);
            } else if ("nature".equals(dto.getOrderAgentType())) {
                //查询自然量数据
                cri.where().andEquals("agent_id", 0);
            }
        }
        //添加判断，除去BG的数据 BG的数据在我们这找不到用户以及渠道
        if (StringUtils.isNotEmpty(dto.getSourceSystem()) && dto.getSourceSystem().contains("BG")) {
            cri.where().andNotIsNull("agent_name");
            cri.where().andNotIsNull("username");
        }

        Sql sql = Sqls.create(getOrderDetailTotalSql() + cri);
        sql.setCallback(Sqls.callback.entity());
        sql.setEntity(dao.getEntity(AdsOrderDetailTotalVO.class));
        dao.execute(sql);

        return sql.getObject(AdsOrderDetailTotalVO.class);
    }

    @Override
    public Page<OrderCPDetailVO> getOrderDetailList(AdsOrderDetailDTO dto) {
        //未传递参数，显示空结果
        if (StringUtils.isBlank(dto.getOrderId())) {
            return null;
        }
        //查询条件
        Criteria cri = Cnd.cri();
        cri.where().andEquals("order_id", dto.getOrderId());
        //拼接默认排序条件
        cri.getOrderBy().desc("create_time");
        //pager对象
        Pager pager = dto.toPage();
        //得到查询的结果
        List<OrderCPDetailVO> records = dao.query(TPayCallCpLog.class, cri, pager).stream().map(this::toVO).collect(Collectors.toList());
        pager.setRecordCount(dao.count(TPayCallCpLog.class, cri));
        return new Page<>(records, pager);
    }

    /**
     * 订单详情列表
     *
     * @param gameOrder 原始表中的数据
     * @return AdsOrderDetailVO
     */
    private AdsOrderDetailVO toVO(AdsOrderDetail gameOrder) {
        if (gameOrder == null) {
            return null;
        }
        //自然量渠道设定名称
        if ("0".equals(gameOrder.getAgentId()) && StringUtils.isBlank(gameOrder.getAgentName())) {
            gameOrder.setAgentName("自然量");
        }
        return BeanUtil.copy(gameOrder, AdsOrderDetailVO.class);
    }

    /**
     * cp回调信息
     *
     * @param tPayCallCpLog 原始表中的数据
     * @return OrderCPDetailVO
     */
    private OrderCPDetailVO toVO(TPayCallCpLog tPayCallCpLog) {
        if (tPayCallCpLog == null) {
            return null;
        }
        return BeanUtil.copy(tPayCallCpLog, OrderCPDetailVO.class);
    }

    /**
     * 查询订单列表sql
     *
     * @return String
     */
    ////                	LEFT JOIN dm_game_order.t_game_user_role ab
    ////                	on a.source_system = ab.source_system AND a.role_id = ab.role_id AND a.user_id = ab.user_id and a.game_id = ab.game_id
    private String getOrderDetailSql() {
        return """
                SELECT
                	*
                FROM (
                	SELECT
                		a.id, -- 主键id
                		a.source_system , -- SDK来源
                		DATE(a.create_time) as `day`, -- 订单创建日期
                		a.order_id , -- 商户订单号ID
                		a.cp_order_id , -- cp订单号
                		a.merchant_order_no , -- 第三方支付订单号
                		a.user_id as game_user_id, -- 玩家ID
                		b.username , -- 玩家账号
                		b.nickname , -- 玩家昵称
                		b.create_time as reg_time, -- 玩家注册时间
                		c.account_id , -- 广告账号
                		a.agent_id , -- 玩家注册渠道ID
                		IF(a.agent_id = 0, '自然量', c.agent_name) as agent_name,-- 注册渠道名
                		b.game_id , -- 玩家注册游戏ID
                		g.game_name , -- 玩家注册游戏名
                		g.classify , -- 玩家注册游戏类型
                		a.cp_id , -- CPID
                		e.cp_name ,-- cp名
                		IFNULL(c.pitcher_id, 0) as zx_pitcher_id, -- 投手ID
                  		IFNULL(d.zx_pitcher_name, '自然量') as zx_pitcher_name, -- 投手名
                		a.game_id as order_game_id, -- 玩家充值游戏ID
                		f.game_name as order_game_name,-- 玩家充值游戏名
                		f.classify as order_game_classify,-- 玩家充值游戏类型
                		f.category as category,                -- 游戏类型
                		f.put_type as put_type,                -- 投放类型
                		a.product_id , -- 充值商品ID
                		a.product_name , -- 充值商品名称
                		a.amount , -- 订单金额
                		a.real_amount , -- 订单实付金额
                		k.amount as role_total_amount, -- 角色累计充值金额
                		k.amount_count as role_total_amount_count, -- 角色累计充值次数
                		m.role_order_7_day_total as role_order7_day_total, -- 角色注册7天内下单成功次数
                		a.payway , -- 支付方式ID、支付方式名
                		a.pay_device as pay_scene,-- 支付场景ID、支付场景名
                		a.status as order_status, -- 支付状态
                		a.cp_status , -- cp通知状态
                		a.last_notify_time as last_cp_notify_time , -- cp最后通知时间
                		a.merchant_no as pay_account_id, -- 收款账户
                		j.merchant_name , -- 收款账户名
                		a.server_id , -- 角色所属游戏区服ID
                		IFNULL(h.server_name, a.server_name) as source_server_name , -- 原始区服名
                		a.server_name , -- 角色所属游戏区服名
                		a.role_id , -- 角色ID
                		a.role_name , -- 角色名
                		a.role_level , -- 角色等级
                		a.role_vip , -- 角色VIP等级
                		a.os , -- 操作系统
                		a.is_switch as order_is_switch, -- 是否切量
                		a.create_time as order_create_time, -- 下单时间
                		a.pay_time,  -- 支付时间
                		TIMESTAMPDIFF(SECOND, b.create_time, a.pay_time) as reg_until_pay, -- 注册充值时间差
                		TIMESTAMPDIFF(SECOND, a.pay_time, NOW()) as pay_until_now, -- 最近充值时间距今(秒)
                		IF(a.status = 2, IF(a.create_time = i.create_time, 1, 0), 0) as is_first_recharge, -- 是否首充
                		IF(b.promotion_id = 'null', NULL, b.promotion_id) as promotion_id , -- 计划ID
                  		IF(b.project_id = 'null' , NULL, b.project_id) as project_id, -- 项目ID（广告ID）
                 		IFNULL(l.back_status, 2) as back_status, -- 回传状态：-1-回传失败;0-未回传;1-已回传;2-没有回传数据
                 		IFNULL(l.back_money / 100, null) as back_money, -- 回传金额
                 		l.back_msg as back_msg, -- 回传信息
                 		l.id as back_id, -- 回传id
                 		l.back_table_name as back_table_name, -- 回传表名
                 		n.back_count -- 拆单笔数
                	FROM dm_game_order.t_game_order a

                	LEFT JOIN dm_game_order.t_game_user_burst b
                	on a.source_system = b.source_system AND a.user_id = b.id AND a.create_time >= b.create_time AND a.create_time <b.end_time
                	LEFT JOIN dm_game_order.t_pitcher_agent c on a.source_system = c.source_system AND a.agent_id = c.id
                	LEFT JOIN dm_game_order.t_pitcher_map d on c.source_system = d.source_system AND c.pitcher_id = d.zx_pitcher_id
                	LEFT JOIN dm_game_order.t_cp e on a.source_system = e.source_system AND a.cp_id = e.id
                	LEFT JOIN dm_game_order.t_game f on a.source_system = f.source_system AND a.game_id = f.id
                	LEFT JOIN dm_game_order.t_game g on b.source_system = g.source_system AND b.game_id = g.id
                	LEFT JOIN (
                	    SELECT
                            *
                        FROM dm_game_order.t_game_server_merge
                        WHERE is_delete = 0
                	) h on a.source_system = h.source_system AND a.server_id = h.server_id AND g.super_game_id = h.game_id
                	LEFT JOIN (
                		SELECT
                			source_system ,
                			user_id ,
                			order_id ,
                			create_time ,
                			ROW_NUMBER()over(partition by source_system , user_id order by create_time ASC) as num
                		FROM dm_game_order.t_game_order
                		WHERE status = 2
                	) i on a.source_system = i.source_system AND a.user_id = i.user_id AND i.num = 1
                	LEFT JOIN (
                		SELECT
                			source_system,
                			merchant_no,
                			merchant_name
                		FROM dm_game_order.t_pay_merchant
                		WHERE is_delete = 0
                	) j on a.source_system = j.source_system AND a.merchant_no = j.merchant_no
                	LEFT JOIN (
                		SELECT
                			-- 累计充值金额、累计充值次数
                			role_id,
                			user_id,
                			source_system,
                			SUM(real_amount) as amount ,
                			COUNT(user_id) as amount_count
                		FROM dm_game_order.t_game_order
                		WHERE status =2
                		GROUP BY role_id , source_system,user_id
                	) k on a.source_system = k.source_system AND a.role_id = k.role_id and a.user_id = k.user_id
                	LEFT JOIN (
                		SELECT id,
                         order_no as order_id,
                         back_status,
                         back_msg,
                         back_money,
                         'byte'   as back_table_name
                        FROM dim_byte_ad.t_game_oceanengine_order_log
                         UNION ALL
                       SELECT id,
                         order_id,
                         back_status,
                         back_msg,
                         back_money,
                         'tencent_mini' as back_table_name
                       FROM dim_tencent_ad.t_game_tencent_mini_game_order
                       where back_type = 'BACK_API'
                         UNION ALL
                       SELECT id,
                         order_id,
                         is_back      as back_status,
                         back_msg,
                         back_money,
                         'tencent_h5' as back_table_name
                       FROM dim_tencent_ad.t_game_tencent_order
                         UNION ALL
                      SELECT
                          id,
                          order_no as order_id,
                          back_status,
                          back_msg,
                          back_money,
                          'oceanengine_app' as back_table_name
                      FROM dim_byte_ad.t_game_oceanengine_app_order_log
                         UNION ALL
                      SELECT
                          id,
                          order_id,
                          is_back as  back_status,
                          back_msg,
                           back_money,
                          'tencent_app' as back_table_name
                      FROM dim_tencent_ad.t_game_tencent_app_order
                         UNION ALL
                     SELECT
                         id,
                         order_id,
                         is_back as back_status,
                         back_msg,
                          back_money,
                         'tencent_app_api' as back_table_name
                     FROM dim_tencent_ad.t_game_tencent_app_api_order
                	) l on a.order_id = l.order_id
                	LEFT JOIN (
                		SELECT
                			a.source_system ,
                			a.role_id ,
                			COUNT(1) as role_order_7_day_total
                		FROM dm_game_order.t_game_order a
                		LEFT JOIN (
                			SELECT
                					*
                			FROM
                			(
                				SELECT
                					-- 角色创建时间（取创建时间最早的信息）
                					a.source_system,
                					a.role_id,
                					a.create_time as role_create_time,
                					ROW_NUMBER()over(partition by a.role_id , a.source_system order by a.create_time asc, a.user_id asc) as num
                				FROM dm_game_order.t_game_user_role a
                				LEFT JOIN dm_game_order.t_game_user b on a.source_system = b.source_system AND a.user_id = b.id
                			) a
                			WHERE num = 1
                		) b on a.source_system = b.source_system AND a.role_id = b.role_id
                		WHERE a.source_system = 'ZX_ONE' AND status = 2
                		AND DATE(a.pay_time) BETWEEN DATE(role_create_time) AND DATE_ADD(DATE(role_create_time) ,INTERVAL 7 DAY)
                		GROUP BY a.source_system ,a.role_id
                	) m on a.source_system = m.source_system AND a.role_id = m.role_id
                	   left join (
                	        SELECT
                	               order_no as order_id,
                                   any_value(back_count) back_count
                            FROM dim_byte_ad.t_game_oceanengine_app_order_split_log group by order_no
                          union all
                             SELECT
                                   order_no as order_id,
                                   any_value(back_count)
                            FROM dim_byte_ad.t_game_oceanengine_order_split_log group by order_no
                         union all
                            SELECT
                                   order_no as order_id,
                                   any_value(back_count)
                         FROM dim_tencent_ad.t_game_tencent_app_order_split_log group by order_no
                        union all
                             SELECT
                                    order_no as order_id,
                                    any_value(back_count)
                         FROM dim_tencent_ad.t_game_tencent_mini_game_order_split_log where back_type = 'BACK_API' group by order_no
                        union all
                             SELECT
                                    order_no as order_id,
                                    any_value(back_count)
                         FROM dim_tencent_ad.t_game_tencent_order_split_log group by order_no
                        ) n on n.order_id = l.order_id
                ) a
                """;
    }

    /**
     * 查询订单列表记录数sql
     *
     * @return String
     */
    //	LEFT JOIN dm_game_order.t_game_user_role ab
    //                	on a.source_system = ab.source_system AND a.role_id = ab.role_id AND a.user_id = ab.user_id and a.game_id = ab.game_id
    private String getCountSql() {
        return """
                SELECT
                	COUNT(1)
                FROM (
                	SELECT
                		a.id, -- 主键id
                		a.source_system , -- SDK来源
                		DATE(a.create_time) as `day`, -- 订单创建日期
                		a.order_id , -- 商户订单号ID
                		a.cp_order_id , -- cp订单号
                		a.merchant_order_no , -- 第三方支付订单号
                		a.user_id as game_user_id, -- 玩家ID
                		b.username , -- 玩家账号
                		b.nickname , -- 玩家昵称
                		b.create_time as reg_time, -- 玩家注册时间
                		c.account_id , -- 广告账号
                		a.agent_id , -- 玩家注册渠道ID
                		IF(a.agent_id = 0, '自然量', c.agent_name) as agent_name,-- 注册渠道名
                		b.game_id , -- 玩家注册游戏ID
                		g.game_name , -- 玩家注册游戏名
                		g.classify , -- 玩家注册游戏类型
                		a.cp_id , -- CPID
                		e.cp_name ,-- cp名
                		IFNULL(c.pitcher_id, 0) as zx_pitcher_id, -- 投手ID
                  		IFNULL(d.zx_pitcher_name, '自然量') as zx_pitcher_name, -- 投手名
                		a.game_id as order_game_id, -- 玩家充值游戏ID
                		f.game_name as order_game_name,-- 玩家充值游戏名
                		f.classify as order_game_classify,-- 玩家充值游戏类型
                		a.product_id , -- 充值商品ID
                		a.product_name , -- 充值商品名称
                		a.amount , -- 订单金额
                		a.real_amount , -- 订单实付金额
                		k.amount as role_total_amount, -- 角色累计充值金额
                		k.amount_count as role_total_amount_count, -- 角色累计充值次数
                		m.role_order_7_day_total as role_order7_day_total, -- 角色注册7天内下单成功次数
                		a.payway , -- 支付方式ID、支付方式名
                		a.pay_device as pay_scene,-- 支付场景ID、支付场景名
                		a.status as order_status, -- 支付状态
                		a.cp_status , -- cp通知状态
                		a.last_notify_time as last_cp_notify_time , -- cp最后通知时间
                		a.merchant_no as pay_account_id, -- 收款账户
                		j.merchant_name , -- 收款账户名
                		a.server_id , -- 角色所属游戏区服ID
                		IFNULL(h.server_name, a.server_name) as source_server_name , -- 原始区服名
                		a.server_name , -- 角色所属游戏区服名
                		a.role_id , -- 角色ID
                		a.role_name , -- 角色名
                		a.role_level , -- 角色等级
                		a.role_vip , -- 角色VIP等级
                		a.os , -- 操作系统
                		a.is_switch as order_is_switch, -- 是否切量
                		a.create_time as order_create_time, -- 下单时间
                		a.pay_time,  -- 支付时间
                		TIMESTAMPDIFF(SECOND, b.create_time, a.pay_time) as reg_until_pay, -- 注册充值时间差
                		TIMESTAMPDIFF(SECOND, a.pay_time, NOW()) as pay_until_now, -- 最近充值时间距今(秒)
                		IF(a.status = 2, IF(a.create_time = i.create_time, 1, 0), 0) as is_first_recharge, -- 是否首充
                		IF(a.promotion_id = 'null', NULL, a.promotion_id) as promotion_id , -- 计划ID
                  		IF(a.project_id = 'null' , NULL, a.project_id) as project_id, -- 项目ID（广告ID）
                 		IFNULL(l.back_status, 2) as back_status, -- 回传状态：-1-回传失败;0-未回传;1-已回传;2-没有回传数据
                 		IFNULL(l.back_money / 100, null) as back_money, -- 回传金额
                 		l.back_msg as back_msg, -- 回传信息
                 		l.id as back_id, -- 回传id
                 		l.back_table_name as back_table_name -- 回传表名
                	FROM dm_game_order.t_game_order a
                                
                	LEFT JOIN dm_game_order.t_game_user_burst b
                	on a.source_system = b.source_system AND a.user_id = b.id AND a.create_time >= b.create_time AND a.create_time <b.end_time
                	LEFT JOIN dm_game_order.t_pitcher_agent c on a.source_system = c.source_system AND a.agent_id = c.id
                	LEFT JOIN dm_game_order.t_pitcher_map d on c.source_system = d.source_system AND c.pitcher_id = d.zx_pitcher_id
                	LEFT JOIN dm_game_order.t_cp e on a.source_system = e.source_system AND a.cp_id = e.id
                	LEFT JOIN dm_game_order.t_game f on a.source_system = f.source_system AND a.game_id = f.id
                	LEFT JOIN dm_game_order.t_game g on b.source_system = g.source_system AND b.game_id = g.id
                	LEFT JOIN (
                	    SELECT
                            *
                        FROM dm_game_order.t_game_server_merge
                        WHERE is_delete = 0
                	) h on a.source_system = h.source_system AND a.server_id = h.server_id AND g.super_game_id = h.game_id
                	LEFT JOIN (
                		SELECT
                			source_system ,
                			user_id ,
                			order_id ,
                			create_time ,
                			ROW_NUMBER()over(partition by source_system , user_id order by create_time ASC) as num
                		FROM dm_game_order.t_game_order
                		WHERE status = 2
                	) i on a.source_system = i.source_system AND a.user_id = i.user_id AND i.num = 1
                	LEFT JOIN (
                		SELECT
                			source_system,
                			merchant_no,
                			merchant_name
                		FROM dm_game_order.t_pay_merchant
                		WHERE is_delete = 0
                	) j on a.source_system = j.source_system AND a.merchant_no = j.merchant_no
                	LEFT JOIN (
                	SELECT
                			-- 累计充值金额、累计充值次数
                			role_id,
                			user_id,
                			source_system,
                			SUM(real_amount) as amount ,
                			COUNT(user_id) as amount_count
                		FROM dm_game_order.t_game_order
                		WHERE status =2
                		GROUP BY role_id , source_system,user_id
                	) k on a.source_system = k.source_system AND a.role_id = k.role_id and a.user_id = k.user_id
                	LEFT JOIN (
                		SELECT
                			id,
                			order_no as order_id,
                			back_status,
                			back_msg,
                			back_money,
                			'byte' as back_table_name
                		FROM dim_byte_ad.t_game_oceanengine_order_log
                		UNION ALL
                		SELECT
                			id,
                			order_id ,
                			back_status,
                			back_msg,
                			back_money,
                			'tencent_mini' as back_table_name
                		FROM dim_tencent_ad.t_game_tencent_mini_game_order
                		where back_type = 'BACK_API'
                		UNION ALL
                		SELECT
                			id,
                			order_id ,
                			is_back as back_status,
                			back_msg,
                			back_money,
                			'tencent_h5' as back_table_name
                		FROM dim_tencent_ad.t_game_tencent_order
                	) l on a.order_id = l.order_id
                	LEFT JOIN (
                		SELECT
                			a.source_system ,
                			a.role_id ,
                			COUNT(1) as role_order_7_day_total
                		FROM dm_game_order.t_game_order a
                		LEFT JOIN (
                			SELECT
                					*
                			FROM
                			(
                				SELECT
                					-- 角色创建时间（取创建时间最早的信息）
                					a.source_system,
                					a.role_id,
                					a.create_time as role_create_time,
                					ROW_NUMBER()over(partition by a.role_id , a.source_system order by a.create_time asc, a.user_id asc) as num
                				FROM dm_game_order.t_game_user_role a
                				LEFT JOIN dm_game_order.t_game_user b on a.source_system = b.source_system AND a.user_id = b.id
                			) a
                			WHERE num = 1
                		) b on a.source_system = b.source_system AND a.role_id = b.role_id
                		WHERE a.source_system = 'ZX_ONE' AND status = 2
                		AND DATE(a.pay_time) BETWEEN DATE(role_create_time) AND DATE_ADD(DATE(role_create_time) ,INTERVAL 7 DAY)
                		GROUP BY a.source_system ,a.role_id
                	) m on a.source_system = m.source_system AND a.role_id = m.role_id
                ) a
                """;
    }

    /**
     * 查询订单总计
     *
     * @return String
     */
    private String getOrderDetailTotalSql() {
        return """
                SELECT
                	SUM(amount) as amount,
                	SUM(real_amount) as real_amount,
                	SUM(back_money) as back_money
                FROM (
                	SELECT
                		a.id, -- 主键id
                		a.source_system , -- SDK来源
                		DATE(a.create_time) as `day`, -- 订单创建日期
                		a.order_id , -- 商户订单号ID
                		a.cp_order_id , -- cp订单号
                		a.merchant_order_no , -- 第三方支付订单号
                		a.user_id as game_user_id, -- 玩家ID
                		b.username , -- 玩家账号
                		b.nickname , -- 玩家昵称
                		b.create_time as reg_time, -- 玩家注册时间
                		c.account_id , -- 广告账号
                		a.agent_id , -- 玩家注册渠道ID
                		IF(a.agent_id = 0, '自然量', c.agent_name) as agent_name,-- 注册渠道名
                		b.game_id , -- 玩家注册游戏ID
                		g.game_name , -- 玩家注册游戏名
                		g.classify , -- 玩家注册游戏类型
                		a.cp_id , -- CPID
                		e.cp_name ,-- cp名
                		IFNULL(c.pitcher_id, 0) as zx_pitcher_id, -- 投手ID
                  		IFNULL(d.zx_pitcher_name, '自然量') as zx_pitcher_name, -- 投手名
                		a.game_id as order_game_id, -- 玩家充值游戏ID
                		f.game_name as order_game_name,-- 玩家充值游戏名
                		f.classify as order_game_classify,-- 玩家充值游戏类型
                		a.product_id , -- 充值商品ID
                		a.product_name , -- 充值商品名称
                		a.amount , -- 订单金额
                		a.real_amount , -- 订单实付金额
                		k.amount as role_total_amount, -- 角色累计充值金额
                		k.amount_count as role_total_amount_count, -- 角色累计充值次数
                		m.role_order_7_day_total as role_order7_day_total, -- 角色注册7天内下单成功次数
                		a.payway , -- 支付方式ID、支付方式名
                		a.pay_device as pay_scene,-- 支付场景ID、支付场景名
                		a.status as order_status, -- 支付状态
                		a.cp_status , -- cp通知状态
                		a.last_notify_time as last_cp_notify_time , -- cp最后通知时间
                		a.merchant_no as pay_account_id, -- 收款账户
                		j.merchant_name , -- 收款账户名
                		a.server_id , -- 角色所属游戏区服ID
                		IFNULL(h.server_name, a.server_name) as source_server_name , -- 原始区服名
                		a.server_name , -- 角色所属游戏区服名
                		a.role_id , -- 角色ID
                		a.role_name , -- 角色名
                		a.role_level , -- 角色等级
                		a.role_vip , -- 角色VIP等级
                		a.os , -- 操作系统
                		a.is_switch as order_is_switch, -- 是否切量
                		a.create_time as order_create_time, -- 下单时间
                		a.pay_time,  -- 支付时间
                		TIMESTAMPDIFF(SECOND, b.create_time, a.pay_time) as reg_until_pay, -- 注册充值时间差
                		TIMESTAMPDIFF(SECOND, a.pay_time, NOW()) as pay_until_now, -- 最近充值时间距今(秒)
                		IF(a.status = 2, IF(a.create_time = i.create_time, 1, 0), 0) as is_first_recharge, -- 是否首充
                		IF(a.promotion_id = 'null', NULL, a.promotion_id) as promotion_id , -- 计划ID
                  		IF(a.project_id = 'null' , NULL, a.project_id) as project_id, -- 项目ID（广告ID）
                 		IFNULL(l.back_status, 2) as back_status, -- 回传状态：-1-回传失败;0-未回传;1-已回传;2-没有回传数据
                 		IFNULL(l.back_money / 100, null) as back_money, -- 回传金额
                 		l.back_msg as back_msg, -- 回传信息
                 		l.id as back_id, -- 回传id
                 		l.back_table_name as back_table_name -- 回传表名
                	FROM dm_game_order.t_game_order a
                	LEFT JOIN dm_game_order.t_game_user_role ab
                	on a.source_system = ab.source_system AND a.role_id = ab.role_id AND a.user_id = ab.user_id
                	LEFT JOIN dm_game_order.t_game_user_burst b
                	on a.source_system = b.source_system AND a.user_id = b.id AND ab.create_time >= b.create_time AND ab.create_time <b.end_time
                	LEFT JOIN dm_game_order.t_pitcher_agent c on a.source_system = c.source_system AND a.agent_id = c.id
                	LEFT JOIN dm_game_order.t_pitcher_map d on c.source_system = d.source_system AND c.pitcher_id = d.zx_pitcher_id
                	LEFT JOIN dm_game_order.t_cp e on a.source_system = e.source_system AND a.cp_id = e.id
                	LEFT JOIN dm_game_order.t_game f on a.source_system = f.source_system AND a.game_id = f.id
                	LEFT JOIN dm_game_order.t_game g on b.source_system = g.source_system AND b.game_id = g.id
                	LEFT JOIN (
                	    SELECT
                            *
                        FROM dm_game_order.t_game_server_merge
                        WHERE is_delete = 0
                	) h on a.source_system = h.source_system AND a.server_id = h.server_id AND g.super_game_id = h.game_id
                	LEFT JOIN (
                		SELECT
                			source_system ,
                			user_id ,
                			order_id ,
                			create_time ,
                			ROW_NUMBER()over(partition by source_system , user_id order by create_time ASC) as num
                		FROM dm_game_order.t_game_order
                		WHERE status = 2
                	) i on a.source_system = i.source_system AND a.user_id = i.user_id AND i.num = 1
                	LEFT JOIN (
                		SELECT
                			source_system,
                			merchant_no,
                			merchant_name
                		FROM dm_game_order.t_pay_merchant
                		WHERE is_delete = 0
                	) j on a.source_system = j.source_system AND a.merchant_no = j.merchant_no
                	LEFT JOIN (
                		SELECT
                			-- 累计充值金额、累计充值次数
                			role_id,
                			source_system,
                			SUM(real_amount) as amount ,
                			COUNT(user_id) as amount_count
                		FROM dm_game_order.t_game_order
                		WHERE status =2
                		GROUP BY role_id , source_system
                	) k on a.source_system = k.source_system AND a.role_id = k.role_id
                	LEFT JOIN (
                		SELECT
                			id,
                			order_no as order_id,
                			back_status,
                			back_msg,
                			back_money,
                			'byte' as back_table_name
                		FROM dim_byte_ad.t_game_oceanengine_order_log
                		UNION ALL
                		SELECT
                			id,
                			order_id ,
                			back_status,
                			back_msg,
                			back_money,
                			'tencent_mini' as back_table_name
                		FROM dim_tencent_ad.t_game_tencent_mini_game_order where back_type = 'BACK_API'
                		UNION ALL
                		SELECT
                			id,
                			order_id ,
                			is_back as back_status,
                			back_msg,
                			back_money,
                			'tencent_h5' as back_table_name
                		FROM dim_tencent_ad.t_game_tencent_order
                		UNION ALL
                      SELECT
                          id,
                          order_no as order_id,
                          back_status,
                          back_msg,
                          back_money,
                          'oceanengine_app' as back_table_name
                      FROM dim_byte_ad.t_game_oceanengine_app_order_log
                         UNION ALL
                      SELECT
                          id,
                          order_id,
                          is_back as  back_status,
                          back_msg,
                           back_money,
                          'tencent_app' as back_table_name
                      FROM dim_tencent_ad.t_game_tencent_app_order
                         UNION ALL
                     SELECT
                         id,
                         order_id,
                         is_back as back_status,
                         back_msg,
                          back_money,
                         'tencent_app_api' as back_table_name
                     FROM dim_tencent_ad.t_game_tencent_app_api_order
                	) l on a.order_id = l.order_id
                	LEFT JOIN (
                		SELECT
                			a.source_system ,
                			a.role_id ,
                			COUNT(1) as role_order_7_day_total
                		FROM dm_game_order.t_game_order a
                		LEFT JOIN (
                			SELECT
                					*
                			FROM
                			(
                				SELECT
                					-- 角色创建时间（取创建时间最早的信息）
                					a.source_system,
                					a.role_id,
                					a.create_time as role_create_time,
                					ROW_NUMBER()over(partition by a.role_id , a.source_system order by a.create_time asc, a.user_id asc) as num
                				FROM dm_game_order.t_game_user_role a
                				LEFT JOIN dm_game_order.t_game_user b on a.source_system = b.source_system AND a.user_id = b.id
                			) a
                			WHERE num = 1
                		) b on a.source_system = b.source_system AND a.role_id = b.role_id
                		WHERE a.source_system = 'ZX_ONE' AND status = 2
                		AND DATE(a.pay_time) BETWEEN DATE(role_create_time) AND DATE_ADD(DATE(role_create_time) ,INTERVAL 7 DAY)
                		GROUP BY a.source_system ,a.role_id
                	) m on a.source_system = m.source_system AND a.role_id = m.role_id
                ) a
                """;
    }

}
