playlet/app/Service/DataStatisti...

<?php
/**
 * Created by PhpStorm.
 * User: shensong
 * Date: 2022/4/20
 * Time: 11:55
 */

namespace App\Service;

use App\Console\Commands\PlayletDataStatistics;
use App\Console\Commands\PlayletTrendDataStatistics;
use App\Console\Commands\NewReport\AdqPlayletAccountDataTrendStatistics;
use App\Console\Commands\Report\AdqPlayletDataTrendStatistics;
use App\Console\Commands\NewReport\MpPlayletAccountDataTrendStatistics;
use App\Console\Commands\Report\MpPlayletDataTrendStatistics;
use App\Log;
use App\Models\AccountConfigNoUserRelation;
use App\Models\ActiveFansData;
use App\Models\AdqAccountTrendData;
use App\Models\AdqPlayletTrendData;
use App\Models\AdqUser;
use App\Models\DjOrder;
use App\Models\DramaSeries;
use App\Models\DramaUserRela;
use App\Models\Es\AccountDataTrend;
use App\Models\Es\OperateDataTrend;
use App\Models\Es\PlayletAccountDataTrend;
use App\Models\MpAccountTrendData;
use App\Models\MpPlayletTrendData;
use App\Models\OfficialAccount;
use App\Models\OfficialAccountData;
use App\Models\OfficialAccountRelation;
use App\Models\OfficialWebUserActionSetId;
use App\Models\PlayletTrendStatistics;
use App\Models\Report\DjCumulativeRecoveryData;
use App\Models\Report\DjDataCycleAdq;
use App\Models\Report\DjDataCycleMp;
use App\Models\Report\DjDataCycleNur;
use App\Models\Report\DjRegUserRangeReport;
use App\Models\Report\PlayletDataTrend;
use App\Models\System\Users;
use App\Models\TencentAdAuth;
use App\Models\TencentAdDailyReport;
use App\Models\VpOrder;
use App\RedisModel;
use App\Models\PlayletStatisticsData as Statistics;
use App\Support\EmailQueue;

class DataStatisticsService
{
    protected static $days = 2;

    // 公众号数据
    public static function wechatAccountData($startDate, $endDate, $accountId, $corpid, $sortField, $page, $pageSize, $sysGroupId)
    {
        // 查询公众号列表
        $select = 'mp_app_id as app_id, mp_name as account_name';
        list($accountList, $total) = OfficialAccount::getSysGroupMpAppList($accountId, $sysGroupId, $select,
            'DataStatisticsService.wechatAccountData');
        if(empty($accountList)) return [['list' => [], 'total' => []], 0];

        $appIds = array_unique(array_column($accountList->toArray(), 'app_id'));

        // 获取最早消耗日期，最晚消耗日期和累计消耗金额
        $paidCondition = TencentAdDailyReport::paidCondition($appIds, $startDate, $endDate);

        // 获取累计充值金额，累计充值人数
        $chargeOrders = DjOrder::chargeOrders($appIds, $startDate, $endDate);

        // 获取最近三天的充值数据
        $days = self::$days;
        $recentPaid = DjOrder::getRecentPaidNew($appIds, $days, $startDate, $endDate);

        // 获取各个日期对应的账号对应投手数据
        $promoteList = DramaUserRela::getPromoteByDate($appIds);

        // 获取所有投手基础信息
        $promoterList = DataStatisticsService::getPromoterList();

        # 获取最近三天数据
        $today = date('Y-m-d');
        $yesterday = date('Y-m-d', strtotime('-1 days'));
        $beforeYesterday = date('Y-m-d', strtotime('-2 days'));

        # 处理数据
        foreach($accountList as $list) {
            $promoter_name = [];
            # 最早消耗日期，最晚消耗日期和累计消耗，总关注人数处理
            if(isset($paidCondition[$list->app_id])) {
                $list->start_paid = $paidCondition[$list->app_id]['start_paid'];
                $list->end_paid = $paidCondition[$list->app_id]['end_paid'];
                $list->paid_total = $paidCondition[$list->app_id]['paid_total'] / 100;
                $list->follow_total = $paidCondition[$list->app_id]['scan_follow_count'];// 企微关注数

                // 投手数据
                $promoteInfo = $promoteList->where('app_id', $list->app_id)
                    ->where('start_date', '<=', $list->end_paid)
                    ->where('end_date', '>=', $list->start_paid)
                    ->pluck('user_id')
                    ->toArray();

                foreach ($promoteInfo as $promoterId) {
                    $promoterInfo = isset($promoterList[$promoterId]) ? $promoterList[$promoterId] : null;
                    $promoterName = isset($promoterInfo['name']) ? $promoterInfo['name'] : null;

                    if(!empty($promoterName)) {
                        $promoter_name[] = $promoterName;
                    }
                }

                $promoter_name = array_unique($promoter_name);
            } else {
                $list->start_paid = 0;
                $list->end_paid = 0;
                $list->paid_total = 0;
                $list->follow_total = 0;
            }

            $list->promoter_name = implode(',', $promoter_name);

            # 平均关注人数成本（累计消耗 / 总的企微关注人数）
            if(is_numeric($list->paid_total) && is_numeric($list->follow_total)) {
                $list->average_follow_paid = $list->follow_total != 0 ?
                    round($list->paid_total / $list->follow_total, 2) : 0;
            } else {
                $list->average_follow_paid = 0;
            }

            # 累计充值、总充值人数(在时间范围内新注册用户的)
            if(isset($chargeOrders[$list->app_id])) {
                $list->charge_total = $chargeOrders[$list->app_id]['charge_total'] / 10000;
                $list->charge_user_total = $chargeOrders[$list->app_id]['charge_user_total'];
            } else {
                $list->charge_total = 0;
                $list->charge_user_total = 0;
            }

            # 充值转化比率（总充值人数 / 总的企微关注数）
            if(is_numeric($list->charge_user_total) && is_numeric($list->follow_total)) {
                $list->charge_transform = $list->follow_total != 0 ? round(($list->charge_user_total / $list->follow_total) * 100, 2) : 0;
            } else {
                $list->charge_transform = 0;
            }

            # 总毛利额（累计充值金额 - 累计消耗）、回本率（累计充值金额 / 累计消耗）
            if(is_numeric($list->paid_total) && is_numeric($list->charge_total)) {
                $list->margin_rate = round($list->charge_total - $list->paid_total, 2);
                $list->cost_recovery_rate = $list->paid_total != 0 ?
                    round(($list->charge_total / $list->paid_total) * 100,2) : 0;

            } else {
                $list->margin_rate = 0;
                $list->cost_recovery_rate = 0;

            }

            # 充值转化成本（累计消耗金额 / 总充值人数）
            if(is_numeric($list->paid_total) && is_numeric($list->charge_user_total)) {
                $list->charge_transform_cost = $list->charge_user_total != 0 ?
                    round($list->paid_total / $list->charge_user_total, 2) : 0;
            } else {
                $list->charge_transform_cost = 0;
            }

            # 获取近三天的充值金额
            $todayData = $recentPaid->where('paid_date', $today)
                ->where('app_id', $list->app_id)->first();

            $list->today = isset($todayData->day_paid) ? ($todayData->day_paid / 10000) : 0;

            $yesterdayData = $recentPaid->where('paid_date', $yesterday)
                ->where('app_id', $list->app_id)->first();

            $list->yesterday = isset($yesterdayData->day_paid) ? ($yesterdayData->day_paid / 10000) : 0;

            $beforeYesterdayData = $recentPaid->where('paid_date', $beforeYesterday)
                ->where('app_id', $list->app_id)->first();

            $list->before_yesterday = isset($beforeYesterdayData->day_paid) ? ($beforeYesterdayData->day_paid / 10000)
                : 0;
        }

        // 计算总概数据
        $condition = DataStatisticsService::accountCondition($startDate, $endDate, $accountId, $sysGroupId);

        // 数据切割，排序
        $accountList = json_decode(json_encode($accountList), 1);
        $newAccountList = DataStatisticsService::my_array_multisort($accountList, $sortField);
        $accountList = array_splice($newAccountList, ($page - 1)*$pageSize, $pageSize);

        $data = ['list' => $accountList, 'total' => $condition];

        return [$data, $total];
    }

    // 获取企业关联的公众号列表
    public static function getAccountIdListByCorpid($corpid)
    {
        $data = OfficialAccount::query()
            ->where('enable', 1)
            ->where('corp_id', $corpid)
            ->pluck('mp_app_id')
            ->toArray();

        return $data;
    }

    // 获取投手列表
    public static function getPromoterList()
    {
        $list = Users::query()->get()->toArray();
        $list = array_column($list, null, 'id');
        return $list;
    }

    // 公众号数据总概
    public static function accountCondition($startDate, $endDate, $accountId, $sysGroupId)
    {
        $select = 'mp_app_id as app_id, mp_name as account_name';
        list($accountList, $total) = OfficialAccount::getSysGroupMpAppList($accountId, $sysGroupId, $select,
            'DataStatisticsService.accountCondition');
        if(!$total) return [];

        $appIds = array_column($accountList->toArray(), 'app_id');

        # 获取MP端数据汇总
        $mpData = TencentAdDailyReport::getPaidTotalCondition($startDate, $endDate, $appIds);

        # 获取小说平台订单数据汇总
        $orderData = DjOrder::getOrderTotalCondition($startDate, $endDate, $appIds);

        $paidTotal = isset($mpData->paid_total) ? ($mpData->paid_total / 100) : 0;
        $chargeTotal = isset($orderData->charge_total) ? ($orderData->charge_total / 10000) : 0;
        $followTotal = isset($mpData->scan_follow_count) ? $mpData->scan_follow_count : 0;

        # 获取最近三天充值数据
        $days = self::$days;
        $recentPaid = DjOrder::getTotalRecentPaidNew($appIds, $days, $startDate, $endDate);

        # 获取最近三天数据
        $today = date('Y-m-d');
        $yesterday = date('Y-m-d', strtotime('-1 days'));
        $beforeYesterday = date('Y-m-d', strtotime('-2 days'));

        # 获取近三天的充值金额
        $todayData = $recentPaid->where('paid_date', $today)->first();
        $yesterdayData = $recentPaid->where('paid_date', $yesterday)->first();
        $beforeYesterdayData = $recentPaid->where('paid_date', $beforeYesterday)->first();

        $data = [
            'paid_total' => round($paidTotal, 2),
            'charge_total' => round($chargeTotal, 2),
            'margin_rate' => round($chargeTotal - $paidTotal, 2),
            'cost_recovery_rate' => $paidTotal != 0 ? round(($chargeTotal / $paidTotal) * 100, 2) : 0,
            'follow_total' => $followTotal,
            'average_follow_paid' => $followTotal != 0 ? round($paidTotal / $followTotal, 2) : 0,
            'charge_user_total' => isset($orderData->charge_uv_total) ? $orderData->charge_uv_total : 0,
            'today' => isset($todayData->day_paid) ? ($todayData->day_paid / 10000) : 0,
            'yesterday' => isset($yesterdayData->day_paid) ? ($yesterdayData->day_paid / 10000) : 0,
            'before_yesterday' => isset($beforeYesterdayData->day_paid) ? ($beforeYesterdayData->day_paid / 10000) : 0,
        ];

        return $data;
    }

    // 内存排序
    public static function my_array_multisort($data,$sort_order_field,$sort_order=SORT_DESC,$sort_type=SORT_NUMERIC){
        $key_arrays = [];

        foreach($data as $val){
            $key_arrays[]=$val[$sort_order_field];
        }
        array_multisort($key_arrays,$sort_order,$sort_type,$data);
        return $data;
    }

    // 短剧投放趋势数据
    public static function playletTrendData($startDate, $endDate, $accountId, $corpid, $playletId, $page, $pageSize)
    {
        if(empty($endDate)) {
            $endDate = date('Y-m-d');
        }

        // 整理日期列表
        list($dateList, $total) = PlayletTrendStatistics::playletTrendDateList($startDate, $endDate, $accountId, $corpid,
            $playletId, $page, $pageSize);

        // 提取日期数据
        $dates = array_column($dateList->toArray(), 'ref_date');

        if(empty($dates)) {
            return [['list' => [], 'overview' => []], $total];
        }

        // 获取日期范围内的统计数据
        $dataList = PlayletTrendStatistics::statisticsData(null, null, $dates, $accountId, $corpid, $playletId);

        $appIds = array_values(array_unique(array_column($dataList->toArray(), 'app_id')));

        # 获取短剧列表
        $dramaSeriesList = DramaSeries::getDramaSeriesList();

        // 获取公众号基础信息
        $accountList = OfficialAccount::getAccountListCondition($appIds);

        $result = [];
        foreach($dataList->toArray() as $value) {
            // 获取公众号名称
            $accountName = $accountList->where('mp_app_id', $value['app_id'])->first();
            $value['account_name'] = isset($accountName->mp_name) ? $accountName->mp_name : null;

            // 当日roi
            $value['first_day_roi'] = ($value['day_paid'] != 0) ? round($value['new_user_charge'] / $value['day_paid'], 4) * 100  : 0;
            // 回本率
            $value['cost_cover_reate'] = round($value['cost_cover_rate'] * 100, 2) . '%';

            // 统计数据(总)
            $value['chargeData'] = [
                // 充：当日回收
                'day_charge' => $value['new_user_charge'],
                // 累计充值
                'charge_total' => $value['charge_total'],
                // 当日消耗
                'day_paid' => $value['day_paid'],
                // 增：当日roi = 当日回收/消耗
                'day_add' => ($value['day_paid'] != 0 ) ?
                    round(($value['new_user_charge'] / $value['day_paid']) * 100, 2) . '%' : '0%',
                // 回：回本率 = 累计充值/消耗
                'day_cover' => ($value['day_paid'] != 0 ) ?
                    round(($value['charge_total'] / $value['day_paid']) * 100, 2) . '%' : '0%',
                // 倍：累计充值/当日回收
                'day_times' => ($value['day_paid'] * $value['first_day_roi'] != 0)  ? ($value['charge_total'] / ($value['day_paid'] * $value['first_day_roi'])) : 0,
                // day1...day100
                'days' => DataStatisticsService::chargeDataFormat(json_decode($value['charge_data'], 1)),
            ];

//            $value['first_day_roi'] = $value['first_day_roi'] . '%';

            // 设置日期
            if(!isset($result[$value['ref_date']])) {
                $result[$value['ref_date']]['ref_date'] = $value['ref_date'];
            }

            // 设置剧集名称
            if(!isset($result[$value['ref_date']][$value['playlet_id']])) {
                $dramaSeries = $dramaSeriesList->where('id', $value['playlet_id'])->first();
                $dramaSeriesName = isset($dramaSeries->name) ? $dramaSeries->name : null;
                $result[$value['ref_date']]['playlet_list'][$value['playlet_id']]['playlet_name'] = $dramaSeriesName;
            }

            // 将单条数据写入数组中
            $result[$value['ref_date']]['playlet_list'][$value['playlet_id']]['app_list'][] = $value;

            // 统计汇总数据（短剧在某日的汇总）
            if(isset($result[$value['ref_date']]['playlet_list'][$value['playlet_id']]['total'])) {
                $totalData = $result[$value['ref_date']]['playlet_list'][$value['playlet_id']]['total'];
                $chargeTotalData = $result[$value['ref_date']]['playlet_list'][$value['playlet_id']]['total']['chargeData']['days'];

                // 可以直接累加的数据
                $newTotalData = [
                    // 企微关注人数
                    'scan_follow_count' => $value['scan_follow_count'] + $totalData['scan_follow_count'],
                    // 当日回收
                    'new_user_charge' => $value['new_user_charge'] + $totalData['new_user_charge'],
                    // 当日消耗
                    'day_paid' => $value['day_paid'] + $totalData['day_paid'],
                    // 累计充值金额
                    'charge_total' => $value['charge_total'] + $totalData['charge_total'],
                    // 新增用户数
                    'fans_increase' => $value['fans_increase'] + $totalData['fans_increase'],
                    // 当日充值人数
                    'new_user_charge_uv' => $value['new_user_charge_uv'] + $totalData['new_user_charge_uv'],
                    // 总充值人数
                    'new_user_charge_uv_count' => $value['new_user_charge_uv_count'] + $totalData['new_user_charge_uv_count'],
                    // 当日充值次数
                    'new_user_charge_pv' => $value['new_user_charge_pv'] + $totalData['new_user_charge_pv'],
                    // 总充值次数
                    'new_user_charge_pv_count' => $value['new_user_charge_pv_count'] + $totalData['new_user_charge_pv_count'],
                ];

                // 累加后需要计算的数据
                // 企微关注成本
                $newTotalData['follow_cost'] = ($newTotalData['scan_follow_count'] != 0) ?
                    round($newTotalData['day_paid'] / $newTotalData['scan_follow_count'], 2) : 0;
                // 回本率
                $newTotalData['cost_cover_reate'] = ($newTotalData['day_paid'] != 0) ?
                    round(($newTotalData['charge_total'] / $newTotalData['day_paid']) * 100, 2) . '%' : '0%';
                // 充值用户成本
                $newTotalData['charge_user_cost'] = ($newTotalData['new_user_charge_uv'] != 0) ?
                    round($newTotalData['day_paid'] / $newTotalData['new_user_charge_uv'], 2) : 0;
                // 首日roi
                $newTotalData['first_day_roi'] = ($newTotalData['day_paid'] != 0) ?
                    round($newTotalData['new_user_charge'] / $newTotalData['day_paid'], 4) * 100  : 0;

                $newChargeTotalData = DataStatisticsService::chargeDataSummary($chargeTotalData, $value['charge_data'], $newTotalData['first_day_roi']);
//                $newTotalData['first_day_roi'] = $newTotalData['first_day_roi'] . '%';

                // 统计数据
                $newTotalData['chargeData'] = [
                    // 充：当日回收
                    'day_charge' => $newTotalData['new_user_charge'],
                    // 累计充值
                    'charge_total' => $newTotalData['charge_total'],
                    // 当日消耗
                    'day_paid' => $newTotalData['day_paid'],
                    // 增：当日roi = 当日回收/消耗
                    'day_add' => ($newTotalData['day_paid'] != 0 ) ?
                        round(($newTotalData['new_user_charge'] / $newTotalData['day_paid']) * 100, 2) . '%' : '0%',
                    // 回：回本率 = 累计充值/消耗
                    'day_cover' => ($newTotalData['day_paid'] != 0 ) ?
                        round(($newTotalData['charge_total'] / $newTotalData['day_paid']) * 100, 2) . '%' : '0%',
                    // 倍：累计充值/首日回收
                    'day_times' => (($value['day_paid'] * $value['first_day_roi'])) ?
                        round($newTotalData['charge_total'] / ($value['day_paid'] * $value['first_day_roi']), 2) : 0,
                    // 每日统计
                    'days' => DataStatisticsService::chargeDataFormat($newChargeTotalData),
                ];

                $result[$value['ref_date']]['playlet_list'][$value['playlet_id']]['total'] = $newTotalData;
            } else {
                // 短剧对应的第一条公众号数据，直接赋值
                $result[$value['ref_date']]['playlet_list'][$value['playlet_id']]['total'] = [
                    // 当日回收
                    'new_user_charge' => $value['new_user_charge'],
                    // 当日消耗
                    'day_paid' => $value['day_paid'],
                    // 企微关注人数
                    'scan_follow_count' => $value['scan_follow_count'],
                    // 企微关注成本
                    'follow_cost' => $value['follow_cost'],
                    // 累计充值金额
                    'charge_total' => $value['charge_total'],
                    // 新增用户数（公众号关注人数）
                    'fans_increase' => $value['fans_increase'],
                    // 回本率
                    'cost_cover_reate' => round($value['cost_cover_rate'] * 100, 2) . '%',
                    // 当日充值人数
                    'new_user_charge_uv' => $value['new_user_charge_uv'],
                    // 总充值人数
                    'new_user_charge_uv_count' => $value['new_user_charge_uv_count'],
                    // 当日充值次数
                    'new_user_charge_pv' => $value['new_user_charge_pv'],
                    // 总充值次数
                    'new_user_charge_pv_count' => $value['new_user_charge_pv_count'],
                    // 充值用户成本
                    'charge_user_cost' => $value['charge_user_cost'],
                    // 统计
                    'chargeData' => $value['chargeData'],
                ];
            }
        }

        // 键值格式化，便于前端处理
        foreach($result as &$item) {
            foreach($item['playlet_list'] as &$value) {
                $value['app_list'] = array_values($value['app_list']);
            }

            $item['playlet_list'] = array_values($item['playlet_list']);
        }

        $result = array_values($result);
        $result = self::my_array_multisort($result, 'ref_date', SORT_DESC,SORT_STRING);

        // 总概
        $overview = DataStatisticsService::getPlayletTrendDataOverview($startDate, $endDate, $accountId, $corpid,
            $playletId);

        // 获取day1至day100表头
        $extra = [];
        for($i = 0;$i < PlayletTrendStatistics::DAYS;$i++) {
            $title = 'day'.($i+1);
            $extra[] = $title;
        }

        return [['list' => $result, 'overview' => $overview, 'extra' => $extra], $total];
    }

    // 短剧投放趋势数据总概
    public static function getPlayletTrendDataOverview($startDate, $endDate, $accountId, $corpid, $playletId)
    {
        // 获取日期范围内的统计数据
        $dataList = PlayletTrendStatistics::statisticsData($startDate, $endDate, null, $accountId, $corpid, $playletId);

        // 提取公众号数据
        $appIds = array_values(array_unique(array_column($dataList->toArray(), 'app_id')));

        $paidTotal = $chargeTotal = $followTotal = $chargeUVTotal = 0;
        foreach($dataList as $item) {
            $paidTotal += $item['day_paid'];
            $chargeTotal += $item['charge_total'];
            $followTotal += $item['scan_follow_count'];
            $chargeUVTotal += $item['new_user_charge_uv_count'];
        }

        # 获取最近三天充值数据
        $days = self::$days;
        $recentPaid = DjOrder::getTotalRecentPaidNew($appIds, $days, $startDate, $endDate);

        # 获取最近三天数据
        $today = date('Y-m-d');
        $yesterday = date('Y-m-d', strtotime('-1 days'));
        $beforeYesterday = date('Y-m-d', strtotime('-2 days'));

        # 获取近三天的充值金额
        $todayData = $recentPaid->where('paid_date', $today)->first();
        $yesterdayData = $recentPaid->where('paid_date', $yesterday)->first();
        $beforeYesterdayData = $recentPaid->where('paid_date', $beforeYesterday)->first();

        $data = [
            'paid_total' => round($paidTotal, 2),
            'charge_total' => round($chargeTotal, 2),
            'margin_rate' => round($chargeTotal - $paidTotal, 2),
            'cost_recovery_rate' => $paidTotal != 0 ? round(($chargeTotal / $paidTotal) * 100, 2) : 0,
            'follow_total' => $followTotal,
            'average_follow_paid' => $followTotal != 0 ? ($paidTotal / $followTotal) : 0,
            'charge_user_total' => $chargeUVTotal,
            'today' => isset($todayData->day_paid) ? ($todayData->day_paid / 10000) : 0,
            'yesterday' => isset($yesterdayData->day_paid) ? ($yesterdayData->day_paid / 10000) : 0,
            'before_yesterday' => isset($beforeYesterdayData->day_paid) ? ($beforeYesterdayData->day_paid / 10000) : 0,
        ];

        return $data;
    }

    // 短剧数据报表
    public static function playletData($startDate, $endDate, $accountId, $corpid, $playletId, $sortField, $page, $pageSize)
    {
        $data = PlayletTrendStatistics::getPlayletDataList($startDate, $endDate, $accountId, $corpid,
            $playletId);
        $total = count($data);

        if(empty($data)) return [[], 0];

        // 提取公众号信息
        $appIds = array_values(array_unique(array_column($data->toArray(), 'app_id')));

        # 获取短剧列表
        $dramaSeriesList = DramaSeries::getDramaSeriesList();

        // 获取公众号基础信息
        $accountList = OfficialAccount::getAccountListCondition($appIds);

        // 获取各个日期对应的账号对应投手数据
        $promoteList = DramaUserRela::getPromoteByDate($appIds);

        // 获取所有投手基础信息
        $promoterList = DataStatisticsService::getPromoterList();

        # 获取最近三天充值数据
        $days = self::$days;
        $recentPaid = DjOrder::getRecentPaidNew($appIds, $days, $startDate, $endDate);

        # 获取最近三天数据
        $today = date('Y-m-d');
        $yesterday = date('Y-m-d', strtotime('-1 days'));
        $beforeYesterday = date('Y-m-d', strtotime('-2 days'));

        // 循环拼接数据
        foreach($data as $item) {
            // 公众号名称
            $accountName = $accountList->where('mp_app_id', $item->app_id)->first();
            $item->account_name = isset($accountName->mp_name) ? $accountName->mp_name : null;
            // 短剧名称
            $dramaSeries = $dramaSeriesList->where('id', $item->playlet_id)->first();
            $item->playlet_name = isset($dramaSeries->name) ? $dramaSeries->name : null;
            // 投手名称
            $promoter_name = [];
            // 投手数据
            $promoteInfo = $promoteList->where('app_id', $item->app_id)
                ->where('start_date', '<=', $item->end_paid)
                ->where('end_date', '>=', $item->start_paid)
                ->pluck('user_id')
                ->toArray();

            foreach ($promoteInfo as $promoterId) {
                $promoterInfo = isset($promoterList[$promoterId]) ? $promoterList[$promoterId] : null;
                $promoterName = isset($promoterInfo['name']) ? $promoterInfo['name'] : null;

                if(!empty($promoterName)) {
                    $promoter_name[] = $promoterName;
                }
            }

            $promoter_name = array_unique($promoter_name);
            $item->promoter_name = implode(',', $promoter_name);

            // 总毛利润
            $item->margin_rate = $item->charge_total - $item->paid_total;
            // 回本率
            $item->cost_recovery_rate = $item->paid_total != 0 ?
                round(($item->charge_total / $item->paid_total) * 100, 2) : 0;
            // 平均关注人数成本
            $item->average_follow_paid = $item->follow_total != 0 ? round($item->paid_total / $item->follow_total, 2) : 0;
            // 充值转化比率
            $item->charge_transform = $item->follow_total != 0 ?
                round(($item->charge_user_total / $item->follow_total) * 100, 2) : 0;
            // 充值转化成本
            $item->charge_transform_cost = $item->charge_user_total != 0 ?
                round($item->paid_total / $item->charge_user_total, 2) : 0;;

            // 今日充值金额
            $todayData = $recentPaid->where('paid_date', $today)->where('app_id', $item->app_id)->first();
            // 昨日充值金额
            $yesterdayData = $recentPaid->where('paid_date', $yesterday)->where('app_id', $item->app_id)->first();
            // 前日充值金额
            $beforeYesterdayData = $recentPaid->where('paid_date', $beforeYesterday)->where('app_id', $item->app_id)->first();

            // 今日充值金额
            $item->today = isset($todayData->day_paid) ? ($todayData->day_paid / 10000) : 0;
            // 昨日充值金额
            $item->yesterday = isset($yesterdayData->day_paid) ? ($yesterdayData->day_paid / 10000) : 0;
            // 前日充值金额
            $item->before_yesterday = isset($beforeYesterdayData->day_paid) ? ($beforeYesterdayData->day_paid / 10000) : 0;
        }

        // 排序
        $data = DataStatisticsService::my_array_multisort($data->toArray(), $sortField);

        // 分片
        $result = array_splice($data, ($page -1) * $pageSize, $pageSize);

        return [$result, $total];
    }

    // 新增绑定关系
    public static function mpRelaAdd($appId, $dramaId, $userId, $startDate, $endDate)
    {
        $requestData = [
            'app_id'     => $appId,
            'drama_id'   => $dramaId,
            'user_id'    => $userId,
            'start_date' => $startDate,
            'end_date'   => $endDate
        ];
        try{
            $date = $startDate;
            if($endDate >= date('Y-m-d')) {
                $endDate = date('Y-m-d');
            }

            while($date <= $endDate) {
                $value['app_list']   = [$appId];
                $value['ref_date']   = $date;
                $value['playlet_id'] = $dramaId;
                $value['user_id']    = $userId; //投手标识
                $value['order_type'] = 1;

                # 投手数据
                RedisModel::lPush(PlayletDataStatistics::$redisKey, json_encode($value, JSON_UNESCAPED_UNICODE));
                # 短剧数据
//                PlayletTrendDataStatistics::updateByOperate($dramaId, $date);

                $date = date('Y-m-d', strtotime($date.' +1 day'));
            }

            # 短剧数据趋势
            MpPlayletDataTrendStatistics::updateByOperate($dramaId, $startDate, $endDate);
            MpPlayletAccountDataTrendStatistics::updateByOperate($dramaId, $startDate, $endDate);

        } catch (\Exception $exception) {
            Log::logError('mpRelaAdd 添加关联关系同步短剧离线数据 request:'.json_encode($requestData, JSON_UNESCAPED_UNICODE), [
                'file'    => $exception->getFile(),
                'line'    => $exception->getLine(),
                'message' => $exception->getMessage(),
                'trace'   => $exception->getTraceAsString(),
            ], 'interface');
        }
    }

    // 编辑绑定关系
    public static function mpRelaEdit($dramaId, $userId, $startDate, $endDate, $enable, $old)
    {
        $requestData = [
            'drama_id'   => $dramaId,
            'user_id'    => $userId,
            'start_date' => $startDate,
            'end_date'   => $endDate,
            'enable'     => $enable,
            'old'        => json_encode($old, JSON_UNESCAPED_UNICODE),
        ];

        try{
            // 判断修改的内容是什么
            if($enable != $old['enable']) {
                // 优先判断是否禁用或者启用了
                if(0 == $enable) {
                    // 禁用了,将对应公众号日期范围内的数据删除
                    PlayletTrendStatistics::query()
                        ->where('app_id', $old['app_id'])
                        ->where('ref_date', '>=', $old['start_date'])
                        ->where('ref_date', '<=', $old['end_date'])
                        ->update(['enable' => 0]);
                } else {
                    // 启用，按照新增的逻辑重新跑一遍数据
                    if($endDate >= date('Y-m-d')) {
                        $endDate = date('Y-m-d');
                    }
                    $date = $startDate;
                    while($date <= $endDate) {
                        $value['app_list']   = [$old['app_id']];
                        $value['ref_date']   = $date;
                        $value['playlet_id'] = $dramaId;
                        $value['user_id']    = $userId; //投手标识
                        $value['order_type'] = 1;

                        # 投手数据
                        RedisModel::lPush(PlayletDataStatistics::$redisKey, json_encode($value, JSON_UNESCAPED_UNICODE));
                        # 短剧数据
//                        PlayletTrendDataStatistics::updateByOperate($dramaId, $date);

                        $date = date('Y-m-d', strtotime($date.' +1 day'));
                    }
                }
            }

            // 在禁用的情况下修改数据，不做处理
            if($enable == $old['enable'] && 1 == $enable) {
                // 将原来数据中多余日期的数据状态更新
                PlayletTrendStatistics::query()
                    ->where('app_id', $old['app_id'])
                    ->where('ref_date', '>=', $old['start_date'])
                    ->where('ref_date', '<=', $old['end_date'])
                    ->where(function($query) use ($endDate, $startDate){
                        $query->where('ref_date', '>', $endDate)->orWhere('ref_date', '<', $startDate);
                    })
                    ->update(['enable' => 0]);

                $date = $startDate;
                if($endDate >= date('Y-m-d')) {
                    $endDate = date('Y-m-d');
                }
                while($date <= $endDate) {
                    $value['app_list']   = [$old['app_id']];
                    $value['ref_date']   = $date;
                    $value['playlet_id'] = $dramaId;
                    $value['user_id']    = $userId; //投手标识
                    $value['order_type'] = 1;

                    # 投手数据
                    RedisModel::lPush(PlayletDataStatistics::$redisKey, json_encode($value, JSON_UNESCAPED_UNICODE));
                    # 短剧数据
//                    PlayletTrendDataStatistics::updateByOperate($dramaId, $date);

                    $date = date('Y-m-d', strtotime($date.' +1 day'));
                }
            }

            if($enable != $old['enable'] || $startDate != $old['start_date'] || $endDate != $old['end_date']){
                $startDate = (($startDate < $old['start_date']) ? $startDate : $old['start_date']);
                $endDate = (($endDate > $old['end_date']) ? $endDate : $old['end_date']);

                # 短剧数据趋势
                MpPlayletDataTrendStatistics::updateByOperate($dramaId, $startDate, $endDate);
                MpPlayletAccountDataTrendStatistics::updateByOperate($dramaId, $startDate, $endDate);
            }

        } catch (\Exception $exception) {
            Log::logError('mpRelaEdit 编辑关联关系同步短剧离线数据 request:'.json_encode($requestData, JSON_UNESCAPED_UNICODE), [
                'file'    => $exception->getFile(),
                'line'    => $exception->getLine(),
                'message' => $exception->getMessage(),
                'trace'   => $exception->getTraceAsString(),
            ], 'interface');
        }
    }

    // 新增绑定关系
    public static function adqRelaAdd($accountId, $dramaId, $userId, $startDate, $endDate)
    {
        $requestData = [
            'account_id' => $accountId,
            'drama_id'   => $dramaId,
            'user_id'    => $userId,
            'start_date' => $startDate,
            'end_date'   => $endDate
        ];
        try{
            $date = $startDate;
            if($endDate >= date('Y-m-d')) {
                $endDate = date('Y-m-d');
            }

            while($date <= $endDate) {
                $value['account_list'] = [$accountId];
                $value['ref_date']     = $date;
                $value['playlet_id']   = $dramaId;
                $value['user_id']      = $userId; //投手标识

                # 投手数据
                RedisModel::lPush(PlayletDataStatistics::$redisKey, json_encode($value, JSON_UNESCAPED_UNICODE));
                # 短剧数据
                //PlayletTrendDataStatistics::updateByOperate($dramaId, $date);

                # 新版公众号数据 start
                // 查询投放账号在当天绑定的公众号ID
                $appIdList = OperateDataTrend::getAppIdList($accountId, $date);
                if(!empty($appIdList)) {
                    RedisModel::lPush(OperateDataTrend::UPDATE_OPEATE_DATA_PITCHER, json_encode([
                        'app_list' => $appIdList,
                        'expense_date' => $date,
                    ]));
                }
                # 新版公众号数据 end

                $date = date('Y-m-d', strtotime($date.' +1 day'));
            }

            # 短剧数据趋势
            AdqPlayletDataTrendStatistics::updateByOperate($dramaId, $startDate, $endDate);
            AdqPlayletAccountDataTrendStatistics::updateByOperate($dramaId, $startDate, $endDate);

        } catch (\Exception $exception) {
            Log::logError('adqRelaAdd 添加关联关系同步短剧离线数据 request:'.json_encode($requestData, JSON_UNESCAPED_UNICODE), [
                'file'    => $exception->getFile(),
                'line'    => $exception->getLine(),
                'message' => $exception->getMessage(),
                'trace'   => $exception->getTraceAsString(),
            ], 'interface');
        }
    }

    // 编辑绑定关系
    public static function adqRelaEdit($dramaId, $userId, $startDate, $endDate, $enable, $old)
    {
        $requestData = [
            'drama_id'   => $dramaId,
            'user_id'    => $userId,
            'start_date' => $startDate,
            'end_date'   => $endDate,
            'enable'     => $enable,
            'old'        => json_encode($old, JSON_UNESCAPED_UNICODE),
        ];

        try{
            // 判断修改的内容是什么
            if($enable != $old['enable']) {
                // 优先判断是否禁用或者启用了
                if(0 == $enable) {
                    // 禁用了,将对应公众号日期范围内的数据删除
                    PlayletTrendStatistics::query()
                        ->where('app_id', $old['account_id'])
                        ->where('ref_date', '>=', $old['start_date'])
                        ->where('ref_date', '<=', $old['end_date'])
                        ->update(['enable' => 0]);
                } else {
                    // 启用，按照新增的逻辑重新跑一遍数据
                    if($endDate >= date('Y-m-d')) {
                        $endDate = date('Y-m-d');
                    }
                    $date = $startDate;
                    while($date <= $endDate) {
                        $value['account_list'] = [$old['account_id']];
                        $value['ref_date']     = $date;
                        $value['playlet_id']   = $dramaId;
                        $value['user_id']      = $userId; //投手标识

                        # 投手数据
                        RedisModel::lPush(PlayletDataStatistics::$redisKey, json_encode($value, JSON_UNESCAPED_UNICODE));
                        # 短剧数据
                        //PlayletTrendDataStatistics::updateByOperate($dramaId, $date);

                        $date = date('Y-m-d', strtotime($date.' +1 day'));
                    }
                }
            }

            // 在禁用的情况下修改数据，不做处理
            if($enable == $old['enable'] && 1 == $enable) {
                // 将原来数据中多余日期的数据状态更新
                PlayletTrendStatistics::query()
                    ->where('app_id', $old['account_id'])
                    ->where('ref_date', '>=', $old['start_date'])
                    ->where('ref_date', '<=', $old['end_date'])
                    ->where(function($query) use ($endDate, $startDate){
                        $query->where('ref_date', '>', $endDate)->orWhere('ref_date', '<', $startDate);
                    })
                    ->update(['enable' => 0]);

                $date = $startDate;
                if($endDate >= date('Y-m-d')) {
                    $endDate = date('Y-m-d');
                }
                while($date <= $endDate) {
                    $value['account_list'] = [$old['account_id']];
                    $value['ref_date']     = $date;
                    $value['playlet_id']   = $dramaId;
                    $value['user_id']      = $userId; //投手标识
                    $value['order_type']   = 2;

                    # 投手数据
                    RedisModel::lPush(PlayletDataStatistics::$redisKey, json_encode($value, JSON_UNESCAPED_UNICODE));
                    # 短剧数据
                    //PlayletTrendDataStatistics::updateByOperate($dramaId, $date);

                    $date = date('Y-m-d', strtotime($date.' +1 day'));
                }
            }

            if($enable != $old['enable'] || $startDate != $old['start_date'] || $endDate != $old['end_date']){
                # 取最大范围日期
                $startDate = (($startDate < $old['start_date']) ? $startDate : $old['start_date']);
                $endDate = (($endDate > $old['end_date']) ? $endDate : $old['end_date']);

                # 短剧数据趋势
                AdqPlayletDataTrendStatistics::updateByOperate($dramaId, $startDate, $endDate);
                AdqPlayletAccountDataTrendStatistics::updateByOperate($dramaId, $startDate, $endDate);

                $date = $startDate;
                while($date <= $endDate) {
                    $value['account_list'] = [$old['account_id']];
                    $value['ref_date']     = $date;
                    $value['playlet_id']   = $dramaId;
                    $value['user_id']      = $userId; //投手标识
                    $value['order_type']   = 2;

                    # 新版公众号数据 start
                    // 查询投放账号在当天绑定的公众号ID
                    $appIdList = OperateDataTrend::getAppIdList($old['account_id'], $date);
                    if(!empty($appIdList)) {
                        RedisModel::lPush(OperateDataTrend::UPDATE_OPEATE_DATA_PITCHER, json_encode([
                            'app_list' => $appIdList,
                            'expense_date' => $date,
                        ]));
                    }
                    # 新版公众号数据 end

                    $date = date('Y-m-d', strtotime($date.' +1 day'));
                }
            }

        } catch (\Exception $exception) {
            Log::logError('adqRelaEdit 编辑关联关系同步短剧离线数据 request:'.json_encode($requestData, JSON_UNESCAPED_UNICODE), [
                'file'    => $exception->getFile(),
                'line'    => $exception->getLine(),
                'message' => $exception->getMessage(),
                'trace'   => $exception->getTraceAsString(),
            ], 'interface');
        }
    }

    public static function chargeDataSummary($totalChargeData, $chargeData, $firstDayRoi)
    {
        $newChargeData = [];
        for($i = 0;$i < PlayletTrendStatistics::DAYS;$i++) {
            if (isset($newChargeData[$i])) {
                //已存在,累加
                $newChargeData[$i]['day_charge'] = isset($chargeData[$i]['day_charge']) ? $totalChargeData[$i]['day_charge'] + $chargeData[$i]['day_charge'] : 0;
                $newChargeData[$i]['charge_total'] = isset($chargeData[$i]['charge_total']) ? $totalChargeData[$i]['charge_total'] + $chargeData[$i]['charge_total'] : 0;
                $newChargeData[$i]['day_paid'] = isset($chargeData[$i]['day_paid']) ? $totalChargeData[$i]['day_paid'] + $chargeData[$i]['day_paid'] : 0;
            } else {
                //不存在,赋值
                $newChargeData[$i]['day_charge'] = isset($chargeData[$i]['day_charge']) ? $chargeData[$i]['day_charge'] : 0;
                $newChargeData[$i]['charge_total'] = isset($chargeData[$i]['charge_total']) ? $chargeData[$i]['charge_total'] : 0;
                $newChargeData[$i]['day_paid'] = isset($chargeData[$i]['day_paid']) ? $chargeData[$i]['day_paid'] : 0;
            }

            $newChargeData[$i]['day_add'] = (isset($newChargeData[$i]['day_paid']) && $newChargeData[$i]['day_paid'] != 0) ? round($newChargeData[$i]['day_charge'] / $newChargeData[$i]['day_paid'], 4) : 0;
            $newChargeData[$i]['day_cover'] = (isset($newChargeData[$i]['day_paid']) && $newChargeData[$i]['day_paid'] != 0) ? round($newChargeData[$i]['charge_total'] / $newChargeData[$i]['day_paid'], 4) : 0;
            $newChargeData[$i]['day_times'] = (isset($chargeData[$i]['day_paid']) && ($chargeData[$i]['day_paid'] * $firstDayRoi) != 0) ? ($newChargeData[$i]['charge_total'] / $chargeData[$i]['day_paid'] * $firstDayRoi) : 0;
        }

        return $newChargeData;
    }

    public static function chargeDataFormat($chargeData)
    {
        $dataFormat = [];
        for($i = 0;$i < PlayletTrendStatistics::DAYS;$i++) {
            $title = 'day'.($i+1);
            $data = isset($chargeData[$i]) ? $chargeData[$i] : [];
            if(!empty($data)) {
                $data['day_add'] = !empty($data['day_add']) ? round($data['day_add'], 4) * 100 . '%' : '0%';
                $data['day_cover'] = !empty($data['day_cover']) ? round($data['day_cover'], 4) * 100 . '%' : '0%';
            }

            $dataFormat[$title] = $data;
        }

        return $dataFormat;
    }

    public static function chargeAfterSubscribed($subscribedAt, $days, $accountId, $roi, $paid, $orderType = 1)
    {
        # 校验是否是日期以及$days的合法性
        if((date("Y-m-d", strtotime($subscribedAt)) != $subscribedAt) || !is_int($days)) {
            Log::logError('日期格式非法',[
                'subscribedAt' => $subscribedAt,
                'days' => $days,
                'accountId' => $accountId
            ], 'ChargeAfterSubscribed');
            return [];
        }

        # 日期处理
        $expiryAt = date("Y-m-d", strtotime('+'.$days. ' days', strtotime($subscribedAt)));
        if($expiryAt >date("Y-m-d")) $expiryAt = date("Y-m-d");
//        $chargeData = RedisModel::getAfterDecode(self::CHARGE_PAID . $appId . '_' . $subscribedAt . '_' . $expiryAt);
        $chargeData = [];
        if(empty($chargeData)) {
            if(1 == $orderType) {
                # 充值数据
                $data = DjOrder::getChargeAfterSubscribed($accountId, $subscribedAt, $expiryAt, $subscribedAt);
            } else {
                # 充值数据
                $data = DjOrder::getAdqChargeAfterSubscribed($accountId, $subscribedAt, $expiryAt, $subscribedAt);
            }

            # 获取后N天每天的充值数据及截止第N天的统计数据
            $chargeData = [];
            $chargePriceTotal = 0;
            for($i=0; $i<$days; $i++) {
                $refDate = date("Y-m-d", strtotime('+'.$i. ' days', strtotime($subscribedAt)));

                if($refDate>date("Y-m-d")) {
                    $chargeData[$i] = [];
                    continue;
                }

                # 第N天的充值数据
                $chargePrice = $data->where('paid_at', '>=', strtotime($refDate . ' 00:00:00') * 1000)
                    ->where('paid_at', '<=', strtotime($refDate . ' 23:59:59') * 1000)
                    ->sum('price');
                $chargePrice = $chargePrice / 10000;

                $chargePriceTotal += $chargePrice;

                $chargeData[$i] = [
                    'day_charge' => $chargePrice,
                    'charge_total' => $chargePriceTotal,
                    'day_paid' => $paid,
                    'day_add' => $paid != 0 ? ($chargePrice / $paid) : 0,
                    'day_cover' => $paid != 0 ? ($chargePriceTotal / $paid) : 0,
                    'day_times' => ($paid * $roi) != 0 ? ($chargePriceTotal / ($paid * $roi)) : 0
                ];
            }
        }

        return $chargeData;
    }

    public static function playletDataTrend($startDate, $endDate, $playletId, $page, $pageSize, $sysGroupId)
    {
        $playletList = DramaSeries::getDramaSeriesList($sysGroupId);
        $playletIdList = $playletList->pluck('id')->toArray();

        $offset = ($page - 1) * $pageSize;
        $playletList = Statistics::query()
            ->where(function($query) use ($playletId, $startDate, $endDate) {
                if($playletId) $query->where('playlet_id', $playletId);
                if($startDate) $query->where('ref_date', '>=', $startDate);
                if($endDate) $query->where('ref_date', '<=', $endDate);
            })
            ->where('enable', 1)
            ->whereIn('playlet_id', $playletIdList)
            ->orderBy('ref_date', 'desc')
            ->offset($offset)
            ->limit($pageSize)
            ->get();

        $playletCount = Statistics::query()
            ->where(function($query) use ($playletId, $startDate, $endDate) {
                if($playletId) $query->where('playlet_id', $playletId);
                if($startDate) $query->where('ref_date', '>=', $startDate);
                if($endDate) $query->where('ref_date', '<=', $endDate);
            })
            ->whereIn('playlet_id', $playletIdList)
            ->where('enable', 1)
            ->count();

        // 提取短剧id
        $playletIdList = array_column($playletList->toArray(), 'playlet_id');
        $dramaSeries = DramaSeries::getDramaSeriesList($sysGroupId);
        $dramaSeriesList = $dramaSeries->whereIn('id', $playletIdList)
            ->keyBy('id')
            ->toArray();

        foreach($playletList as $playlet) {
            // 统计数据格式化
            $playlet->charge_data = DataStatisticsService::chargeDataFormat(json_decode($playlet->charge_data, 1));
            // 拼接短剧名称
            $playlet->playlet_name = isset($dramaSeriesList[$playlet->playlet_id]['name']) ?
                $dramaSeriesList[$playlet->playlet_id]['name'] : '';
            $playlet->first_day_roi = round($playlet->first_day_roi * 100, 2) . '%';
            $playlet->cost_cover_rate = round($playlet->cost_cover_rate * 100, 2) . '%';
            // 充值用户成本(去重用户)
            $playlet->charge_user_cost_unique = $playlet->new_user_charge_uv_unique > 0 ?
                round(($playlet->day_paid / $playlet->new_user_charge_uv_unique), 2) : 0;
        }

        // 计算总概数据
        $overview = DataStatisticsService::getPlayletDataTrendOverview($playletId, $startDate, $endDate, $sysGroupId);

        // 获取day1至day100表头
        $extra = [];
        for($i = 0;$i < PlayletTrendStatistics::DAYS;$i++) {
            $title = 'day'.($i+1);
            $extra[] = $title;
        }

        return [['list' => $playletList, 'overview' => $overview, 'extra' => $extra], $playletCount];
    }

    public static function getPlayletDataTrendOverview($playletId, $startDate, $endDate, $sysGroupId)
    {
        $playletList = DramaSeries::getDramaSeriesList($sysGroupId);
        $playletIdList = $playletList->pluck('id')->toArray();

        # 按条件查询小说基础数据
        $playletData = Statistics::query()
            ->where(function($query) use ($playletId, $startDate, $endDate) {
                if($playletId) $query->where('playlet_id', $playletId);
                if($startDate) $query->where('ref_date', '>=', $startDate);
                if($endDate) $query->where('ref_date', '<=', $endDate);
            })
            ->whereIn('playlet_id', $playletIdList)
            ->get();

        if(empty($playletData)) {
            return [];
        }

        $playletData = $playletData->toArray();

        # 循环累加 因为首日roi无法通过sum求和计算
        $data = [
            'subscribe_date' => '',
            'day_paid' => 0,
            'charge_total' => 0,
            'fans_increase' => 0,
            'new_user_charge_uv' => 0,
            'new_user_charge_pv' => 0,
            'new_user_charge_uv_count' => 0,
            'new_user_charge_pv_count' => 0,
            'first_day_roi' => 0,
            'cost_cover_rate' => 0,
            'new_user_cost' => 0,
            'charge_user_cost' => 0,
        ];
        $realStartDate = null;
        $realEndDate = null;
        foreach($playletData as $playlet) {
            $item = isset($item) ? $item : ['day_charge' => 0, 'charge_total' => 0, 'day_paid' => 0];
//            $data['subscribe_date'] = $startDate.'~'.$endDate;
//            if(empty($realStartDate) || (!empty($realStartDate) && ($realStartDate > $playlet['ref_date']))) {
//                $realStartDate = $playlet['ref_date'];
//            }
//            if(empty($realEndDate) || (!empty($realEndDate) && ($playlet['ref_date'] > $realEndDate))) {
//                $realEndDate = $playlet['ref_date'];
//            }

            $data['day_paid'] = isset($data['day_paid']) ? $data['day_paid'] + $playlet['day_paid'] : 0;
            $data['charge_total'] = isset($data['charge_total']) ? round(($data['charge_total'] + $playlet['charge_total']), 2) : 0;
            $data['fans_increase'] = isset($data['fans_increase']) ? $data['fans_increase'] + $playlet['fans_increase'] : 0;
            $data['new_user_charge_uv'] = isset($data['new_user_charge_uv']) ? $data['new_user_charge_uv'] + $playlet['new_user_charge_uv'] : 0;
            $data['new_user_charge_pv'] = isset($data['new_user_charge_pv']) ? $data['new_user_charge_pv'] + $playlet['new_user_charge_pv'] : 0;
            $data['new_user_charge_uv_count'] = isset($data['new_user_charge_uv_count']) ? $data['new_user_charge_uv_count'] + $playlet['new_user_charge_uv_count'] : 0;
            $data['new_user_charge_pv_count'] = isset($data['new_user_charge_pv_count']) ? $data['new_user_charge_pv_count'] + $playlet['new_user_charge_pv_count'] : 0;
            $data['cost_cover_rate'] = $data['day_paid'] != 0 ? round($data['charge_total'] / $data['day_paid'], 4) * 100 . '%' : '0%';
            $data['new_user_cost'] = $data['fans_increase'] != 0 ? round($data['day_paid'] / $data['fans_increase'], 2) : 0;
            $data['charge_user_cost'] = $data['new_user_charge_uv_count'] != 0 ? round($data['day_paid'] / $data['new_user_charge_uv_count'], 2) : 0;
            $data['new_user_charge_cost'] = $data['new_user_charge_uv'] != 0 ? round($data['day_paid'] / $data['new_user_charge_uv'], 2) : 0;

            $chargeData = json_decode($playlet['charge_data'], 1);
            $data['new_user_charge'] = 0;
            foreach($chargeData as $key => $charge) {
                if(0 == $key) {
                    $item['day_charge'] = isset($charge['day_charge']) ? $item['day_charge'] + $charge['day_charge'] : $item['day_charge'];
                    $item['charge_total'] = isset($charge['charge_total']) ? $item['charge_total'] + $charge['charge_total'] : $item['charge_total'];
                    $item['day_paid'] = isset($charge['day_paid']) ? $item['day_paid'] + $charge['day_paid'] : $item['day_paid'];
                    $data['first_day_roi'] = isset($item['day_paid']) && isset($item['day_charge']) && $item['day_paid'] != 0 ? round($item['day_charge'] / $item['day_paid'], 4) * 100 . '%' : '0%';

                    $data['new_user_charge'] += $item['day_charge'];
                }
            }

            $data['new_user_charge'] = round($data['new_user_charge'], 2);
        }

        return $data;
    }

    /**
     * 粉丝激活趋势
     * @param $startDate string 查询起始日期
     * @param $endDate string 查询截止日期
     * @param $closingDate string 收益截止日期
     * @param $accountId string 公众号appid
     * @param $minPaid integer 最低消耗
     * @param $maxPaid integer 最高消耗
     * @param $page integer 当前页码数
     * @param $pageSize integer 每页显示条数
     * @param $sysGroupId integer 系统权限组id
     * @return mixed
     * */
    public static function activeFansData($startDate, $endDate, $closingDate, $accountId, $minPaid, $maxPaid, $page,
                                          $pageSize, $sysGroupId)
    {
        $minPaid = $minPaid * 100;
        $maxPaid = $maxPaid * 100;

        $mpAppIdList = OfficialAccount::getSysGroupMpAppIdList($sysGroupId, 'DataStatisticsService.activeFansData');

        # 获取日期范围内充值信息
        list($activateData, $total) = ActiveFansData::getActiveFansTrend(
            $startDate, $endDate, $closingDate, $accountId, $minPaid, $maxPaid, $page, $pageSize, $mpAppIdList
        );

        if(empty($activateData))
            return [[], 0];

        # 公众号数据获取
        $appIds = array_unique($activateData->pluck('app_id')->toArray());
        $accountList = OfficialAccount::getAccountListCondition($appIds);

        # 获取列表内的日期
        $dateList = array_unique($activateData->pluck('expense_date')->toArray());

        # 获取各日期对应的花费
//        $paidList = TencentAdDailyReport::getPaidData($appIds, $dateList);

        # 获取激活趋势数据
        $activeDataList = ActiveFansData::select(['app_id', 'expense_date', 'ref_date', 'active_fans'])
            ->where('enable', 1)->whereIn('expense_date', $dateList)
            ->whereIn('app_id', $appIds)->get();

        foreach($activateData as $paid) {
            # 当日新用户累计充值金额、充值人数
//            $newUserCharge = DjOrder::getNewUserCharge($paid->expense_date, $paid->app_id);
//            $paid->new_user_charge_uv = isset($newUserCharge->new_user_charge_uv) ? $newUserCharge->new_user_charge_uv : null;

            # 新用户累计充值金额
//            $paid->charge_total = isset($newUserCharge->charge_total) ? $newUserCharge->charge_total / 10000 : null;
            $paid->charge_total = $paid->charge_total / 100;

            # 公众号信息
            $accountInfo = $accountList->where('mp_app_id', $paid->app_id)->first();
            $paid->account_name = isset($accountInfo->mp_name) ? $accountInfo->mp_name : null;

            # 当日消耗
//            $paidInfo = $paidList->where('app_id', $paid->app_id)
//                ->where('ref_date', $paid->expense_date)
//                ->first();
            $paid->day_paid = $paid->paid / 100;

            # 回本率
            $paid->cost_cover_rate = $paid->day_paid ? (round($paid->charge_total / $paid->day_paid, 4) * 100) . '%' : 0;

            # 企微关注数
            $paid->scan_follow_count = isset($paidInfo->scan_follow_count) ? $paidInfo->scan_follow_count : 0;
            # 企微关注成本
            $paid->follow_cost = $paid->scan_follow_count > 0 ? round($paid->day_paid / $paid->scan_follow_count, 3) : 0;

            # 首日用户成本
            $activeFirstDay = ActiveFansData::where('expense_date', $paid->expense_date)->where('ref_date', $paid->expense_date)->where('app_id', $paid->app_id)->value('active_fans');
            $paid->charge_user_cost = $activeFirstDay ? ($paid->day_paid / $activeFirstDay) : 0;

            # 累计用户成本
            $paid->charge_user_cost_all = $paid->new_user_charge_uv ? ($paid->day_paid / $paid->new_user_charge_uv) : 0;

            $trendData = [];
            $activeFansTotal = 0;
            $activateList = $activeDataList->where('expense_date', $paid->expense_date)->where('app_id', $paid->app_id);
            for($i = 0;$i < ActiveFansData::ACTIVATE_FANS_RECORD_DAYS;$i++) {
                $statDate = date("Y-m-d", strtotime('+'.$i. ' days', strtotime($paid->expense_date)));
                if($statDate>date("Y-m-d")) {
                    $trendData[$i] = [];
                    continue;
                }

                $activeInfo = $activateList->where('ref_date', $statDate)->first();
//                if($paid->app_id == 'wx080137b49399f0ed'){
//                    Log::logInfo('粉丝激活趋势数据', [
//                        'date' => $statDate,
//                        'info' => $activeInfo,
//                        'list' => $activateList->all()
//                    ], '0516');
//                }
                # 激活人数
                $activeUserCount = isset($activeInfo->active_fans) ? $activeInfo->active_fans : null;
                $activeFansTotal += $activeUserCount;
                # 成本
                $cost = $activeFansTotal ? round($paid->day_paid / $activeFansTotal, 2) : 0;

                $trendData[$i] = [
                    'date' => $statDate,
                    'fans' => $activeUserCount,
                    'cost' => $cost,
                    'rate' => $paid->new_user_charge_uv ? (round($activeUserCount / $paid->new_user_charge_uv, 4) * 100) . '%' : null
                ];
            }

            $paid->active_trend = $trendData;
        }

        // 获取day1至day100表头
        $extra = [];
        for($i = 0;$i < ActiveFansData::ACTIVATE_FANS_RECORD_DAYS;$i++) {
            $title = 'day'.($i+1);
            $extra[] = $title;
        }

        return [['list' => $activateData, 'extra' => $extra], $total];
    }

    /**
     * 公众号数据（新版）
     * @param $startDate string 查询起始日期
     * @param $endDate string 查询截止日期
     * @param $accountId string 公众号app_id
     * @param $sortField string 排序字段
     * @param $page int 页码
     * @param $pageSize int 每页展示数据条数
     * @param $sysGroupId integer 系统权限组id
     * @return array
     */
    public static function OfficialAccountData($startDate, $endDate, $accountId, $sortField, $page, $pageSize, $sysGroupId)
    {
        $requestData = [
            'start_date' => $startDate,
            'end_date' => $endDate,
            'account_id' => $accountId,
            'sort_field' => $sortField,
            'page' => $page,
            'page_size' => $pageSize,
            'sys_group_id' => $sysGroupId,
        ];

        // 获取day1至day100表头
        $extra = [];
        for($i = 0;$i < PlayletTrendStatistics::DAYS;$i++) {
            $title = 'day'.($i+1);
            $extra[] = $title;
        }

        try{
            $mpAppIdList = OfficialAccount::getSysGroupMpAppIdList($sysGroupId, 'DataStatisticsService.OfficialAccountData');
            if(empty($mpAppIdList)) {
                Log::logError('DataStatisticsService.OfficialAccountData', [
                    'params' => $requestData,
                    'err_msg' => '当前账号公众号权限为空',
                ], 'interface');

                return [['list' => [], 'overview' => [], 'extra' => $extra], 0];
            }

            $offset = ($page - 1) * $pageSize;
            $appDataList = OfficialAccountData::query()
                ->select(['ref_date', 'app_id', 'day_paid', 'first_day_roi', 'charge_total', 'cost_cover_rate', 'fans_increase',
                    'new_user_cost', 'new_user_charge_uv', 'new_user_charge_pv', 'new_user_charge_uv_count', 'new_user_charge_pv_count',
                    'charge_user_cost', 'other_pay_uv', 'other_pay_money', 'charge_data', 'first_day_charge'])
                ->where(function($query) use ($accountId, $startDate, $endDate) {
                    if($accountId) $query->where('app_id', $accountId);
                    if($startDate) $query->where('ref_date', '>=', $startDate);
                    if($endDate) $query->where('ref_date', '<=', $endDate);
                })
                ->where('enable', 1)
                ->whereIn('app_id', $mpAppIdList)
                ->orderBy('ref_date', 'desc')
                ->offset($offset)
                ->limit($pageSize)
                ->get();

            $appDataCount = OfficialAccountData::query()
                ->where(function($query) use ($accountId, $startDate, $endDate) {
                    if($accountId) $query->where('app_id', $accountId);
                    if($startDate) $query->where('ref_date', '>=', $startDate);
                    if($endDate) $query->where('ref_date', '<=', $endDate);
                })
                ->whereIn('app_id', $mpAppIdList)
                ->where('enable', 1)
                ->count();

            // 提取短剧id
            $appIdList = array_column($appDataList->toArray(), 'app_id');
            $appData = OfficialAccount::query()
                ->where('enable', 1)
                ->whereIn('mp_app_id', $appIdList)
                ->get();

            foreach($appDataList as $app) {
                // 统计数据格式化
                $app->charge_data = DataStatisticsService::chargeDataFormat(json_decode($app->charge_data, 1));
                // 拼接公众号名称
                $appInfo = $appData->where('mp_app_id', $app->app_id)->first();
                $app->app_name = isset($appInfo->mp_name) ? $appInfo->mp_name : null;

                $app->first_day_roi = round($app->first_day_roi * 100, 2) . '%';
                $app->cost_cover_rate = round($app->cost_cover_rate * 100, 2) . '%';
                $app->new_charge_cost = ($app->new_user_charge_uv > 0) ? round($app->day_charge/$app->new_user_charge_uv, 2) : 0;
            }

            // 计算总概数据
            $overview = DataStatisticsService::OfficialAccountDataOverview($accountId, $startDate, $endDate, $mpAppIdList);

            return [['list' => $appDataList, 'overview' => $overview, 'extra' => $extra], $appDataCount];

        } catch (\Exception $exception) {
            Log::logError('DataStatisticsService.OfficialAccountData', [
                'params' => $requestData,
                'message' => $exception->getMessage(),
                'line' => $exception->getLine(),
                'trace' => $exception->getTraceAsString(),
            ], 'interface');

            EmailQueue::rPush('公众号数据', json_encode([
                'params' => $requestData,
                'message' => $exception->getMessage(),
                'line' => $exception->getLine(),
                'trace' => $exception->getTraceAsString(),
            ]), ['song.shen@kuxuan-inc.com'], []);

            return [['list' => [], 'overview' => [], 'extra' => $extra], 0];
        }
    }

    public static function OfficialAccountDataOverview($accountId, $startDate, $endDate, $mpAppIdList)
    {
        # 按条件查询小说基础数据
        $appData = OfficialAccountData::query()
            ->where(function($query) use ($accountId, $startDate, $endDate) {
                if($accountId) $query->where('app_id', $accountId);
                if($startDate) $query->where('ref_date', '>=', $startDate);
                if($endDate) $query->where('ref_date', '<=', $endDate);
            })
            ->whereIn('app_id', $mpAppIdList)
            ->get();

        if(empty($appData)) {
            return [];
        }

        $appData = $appData->toArray();

        # 循环累加 因为首日roi无法通过sum求和计算
        $data = [
            'subscribe_date' => '',
            'day_paid' => 0,
            'charge_total' => 0,
            'fans_increase' => 0,
            'new_user_charge_uv' => 0,
            'new_user_charge_pv' => 0,
            'new_user_charge_uv_count' => 0,
            'new_user_charge_pv_count' => 0,
            'first_day_roi' => 0,
            'cost_cover_rate' => 0,
            'new_user_cost' => 0,
            'charge_user_cost' => 0,
            'first_day_charge' => 0,
        ];
        $realStartDate = null;
        $realEndDate = null;
        foreach($appData as $app) {
            $item = isset($item) ? $item : ['day_charge' => 0, 'charge_total' => 0, 'day_paid' => 0];

            $data['first_day_charge'] = isset($data['first_day_charge']) ? round($data['first_day_charge'] + $app['first_day_charge'], 2) : 0;
            $data['day_paid'] = isset($data['day_paid']) ? $data['day_paid'] + $app['day_paid'] : $app['day_paid'];
            $data['charge_total'] = isset($data['charge_total']) ? round(($data['charge_total'] + $app['charge_total']), 2) :
                $app['charge_total'];
            $data['fans_increase'] = isset($data['fans_increase']) ? $data['fans_increase'] + $app['fans_increase'] : $app['fans_increase'];
            $data['new_user_charge_uv'] = isset($data['new_user_charge_uv']) ? $data['new_user_charge_uv'] + $app['new_user_charge_uv'] :
                $app['new_user_charge_uv'];
            $data['new_user_charge_pv'] = isset($data['new_user_charge_pv']) ? $data['new_user_charge_pv'] + $app['new_user_charge_pv'] :
                $app['new_user_charge_pv'];
            $data['new_user_charge_uv_count'] = isset($data['new_user_charge_uv_count']) ? $data['new_user_charge_uv_count'] + $app['new_user_charge_uv_count'] :
                $app['new_user_charge_uv_count'];
            $data['new_user_charge_pv_count'] = isset($data['new_user_charge_pv_count']) ? $data['new_user_charge_pv_count'] + $app['new_user_charge_pv_count'] :
                $app['new_user_charge_pv_count'];
            $data['cost_cover_rate'] = $data['day_paid'] != 0 ? round($data['charge_total'] / $data['day_paid'], 4) * 100 . '%' : '0%';
            $data['new_user_cost'] = $data['fans_increase'] != 0 ? round($data['day_paid'] / $data['fans_increase'], 2) : 0;
            $data['charge_user_cost'] = $data['new_user_charge_uv_count'] != 0 ? round($data['day_paid'] / $data['new_user_charge_uv_count'], 2) : 0;
            $chargeData = json_decode($app['charge_data'], 1);
            $charge = isset($chargeData[0]) ? $chargeData[0] : [];
            $item['day_charge'] = isset($charge['day_charge']) ? $item['day_charge'] + $charge['day_charge'] : $item['day_charge'];
            $item['charge_total'] = isset($charge['charge_total']) ? $item['charge_total'] + $charge['charge_total'] : $item['charge_total'];
            $item['day_paid'] = isset($charge['day_paid']) ? $item['day_paid'] + $charge['day_paid'] : $item['day_paid'];
            $data['first_day_roi'] = isset($item['day_paid']) && isset($item['day_charge']) && $item['day_paid'] != 0 ? round($item['day_charge'] / $item['day_paid'], 4) * 100 . '%' : '0%';

        }

        return $data;
    }

    /**
     * 根据配置查询去重后的首日充值人数以及累计充值人数
     * @param $startDate string 起始日期
     * @param $endDate string 截止日期
     * @param $playletId integer 剧集id
     * @param $pitcherId integer 投手id
     * @param $sysGroupId integer 系统权限组id
     * @param $type int 查询类型
     * @param $adminId int 当前登录人ID
     * @param $isSystemAdmin int 是否为系统管理员
     * @param $appId string 公众号app_id 非mp投放账号概念
     * @param $launchAccountId int adq投放账号
     * @return array
     */
    public static function statisticsDataUniqueUser ($startDate, $endDate, $playletId, $pitcherId, $sysGroupId, $type
        , $adminId, $isSystemAdmin, $appId = null, $launchAccountId = null)
    {
        $data = [];
        $responseData = [
            'start_date'        => $startDate,
            'end_date'          => $endDate,
            'playlet_id'        => $playletId,
            'pitcher_id'        => $pitcherId,
            'sys_group_id'      => $sysGroupId,
            'type'              => $type,
            'admin_id'          => $adminId,
            'is_system_admin'   => $isSystemAdmin,
            'app_id'            => $appId,
            'launch_account_id' => $launchAccountId
        ];

        $mpAccountList = OfficialAccount::getAccountMpAppIdList($adminId, $sysGroupId, $isSystemAdmin);
        $adqAccountList = PitcherService::adqAccountListForUser($adminId, $sysGroupId, $isSystemAdmin);

        $launchAccountIdList = [];
        if($launchAccountId) {
            $launchAccountIdList = [$launchAccountId];
        } else if($appId) {
            $launchAccountIdList = AdqUser::getAccountIdList($appId);
        }

        try{
            if(is_array($appId)) {
                // 从缓存中读取数据
                $redisKey = 'Playlet::statisticsDataUniqueUser-'.$startDate.'-'.$endDate.'-'.$playletId.'-'.$pitcherId.'-'
                    .$sysGroupId.'-'.$type.'-'.$adminId.'-'.$isSystemAdmin.'-'.json_encode($appId).'-'.$launchAccountId;
            } else {
                // 从缓存中读取数据
                $redisKey = 'Playlet::statisticsDataUniqueUser-'.$startDate.'-'.$endDate.'-'.$playletId.'-'.$pitcherId.'-'
                    .$sysGroupId.'-'.$type.'-'.$adminId.'-'.$isSystemAdmin.'-'.$appId.'-'.$launchAccountId;
            }


            $redisData = RedisModel::get($redisKey);
            if(!empty($redisData)) {
                return json_decode($redisData, 1);
            }

            # 根据条件查询配置信息
            $confList = DramaUserRela::query()
                ->where('sys_group_id', $sysGroupId)
                ->where('enable', 1)
                ->where(function($query) use ($playletId, $pitcherId, $appId, $launchAccountId, $launchAccountIdList) {
                    if($playletId) $query->where('drama_id', $playletId);
                    if($pitcherId) $query->where('user_id', $pitcherId);
                    if($appId || $launchAccountId) $query->whereIn('account_id', $launchAccountIdList);
                })->where(function ($query) use($mpAccountList, $adqAccountList) {
                    $query->whereIn('app_id', $mpAccountList)->orWhereIn('account_id', $adqAccountList);
                })->where('start_date', '<=', $endDate)
                ->where('end_date', '>=', $startDate)
                ->get();

            # 将查询出的配置信息整理
            if($confList->isEmpty()) {
                return $data;
            }

            $accountData = [];
            $confList = $confList->toArray();
            foreach($confList as $confInfo) {
                $item['app_id'] = $confInfo['app_id'];
                $item['account_id'] = $confInfo['account_id'];
                if($confInfo['start_date'] < $startDate) {
                    $item['start_date'] = $startDate;
                } else {
                    $item['start_date'] = $confInfo['start_date'];
                }

                if($confInfo['end_date'] > $endDate) {
                    $item['end_date'] = $endDate;
                } else {
                    $item['end_date'] = $confInfo['end_date'];
                }

                $accountData[] = $item;
            }

            switch($type) {
                case 1:
                    $data['new_user_charge_uv_unique'] = DjOrder::getFirstDayChargeUserNum($accountData);
                    break;
                case 2:
                    $data['new_user_charge_uv_count_unique'] = DjOrderService::getChargeUserTotalUnique($accountData);
                    break;
                case 3:
                    $data['new_user_charge_uv_unique'] = DjOrder::getFirstDayChargeUserNum($accountData);
                    $data['new_user_charge_uv_count_unique'] = DjOrderService::getChargeUserTotalUnique($accountData);
                    break;
                default:
                    break;
            }

        } catch (\Exception $exception) {
            Log::logError('DataStatisticsService.statisticsDataUniqueUser', [
                'params' => $responseData,
                'file' => $exception->getFile(),
                'line' => $exception->getLine(),
                'message' => $exception->getMessage(),
                'trace' => $exception->getTraceAsString(),
            ], 'interface');

            EmailQueue::rPush('短剧及投手数据查询去重后的充值人数异常', json_encode([
                'params' => $responseData,
                'file' => $exception->getFile(),
                'line' => $exception->getLine(),
                'message' => $exception->getMessage(),
                'trace' => $exception->getTraceAsString(),
            ]), ['song.shen@kuxuan-inc.com'], []);
        }

        RedisModel::set($redisKey, json_encode($data));
        RedisModel::expire($redisKey, 600);

        return $data;
    }

    /*
     * 数据循环统计
     */
    public static function dataCycleList($beginDate, $endDate, $page, $pageSize, $sysGroupId, $firstOrderCostMin,
                                         $firstOrderCostMax, $firstOrderCostUniqueMin, $firstOrderCostUniqueMax, $perFollowCostMin, $perFollowCostMax,
                                         $totalRoiMin, $totalRoiMax, $firstDayRoiMin, $firstDayRoiMax, $orderType, $platOrderType, $closingDate, $paidMin,
                                         $paidMax, $sortField, $sortType)
    {
        $requestData = [
            'begin_date'                    => $beginDate,
            'end_date'                      => $endDate,
            'page'                          => $page,
            'page_size'                     => $pageSize,
            'sys_group_id'                  => $sysGroupId,
            'first_order_cost_min'          => $firstOrderCostMin,
            'first_order_cost_max'          => $firstOrderCostMax,
            'first_order_cost_unique_min'   => $firstOrderCostUniqueMin,
            'first_order_cost_unique_max'   => $firstOrderCostUniqueMax,
            'per_follow_cost_min'           => $perFollowCostMin,
            'per_follow_cost_max'           => $perFollowCostMax,
            'total_roi_min'                 => $totalRoiMin,
            'total_roi_max'                 => $totalRoiMax,
            'first_day_roi_min'             => $firstDayRoiMin,
            'first_day_roi_max'             => $firstDayRoiMax,
            'paid_min'                      => $paidMin,
            'paid_max'                      => $paidMax,
            'sort_field'                    => $sortField,
            'order_type'                    => $orderType,
            'plat_order_type'               => $platOrderType,
            'closing_date'                  => $closingDate,
            'sort_type'                     => $sortType,
        ];

        $count = 0;
        $overview = [];
        $data = [];

        try{
            //补充头列表
            $head = [
                ['column' => 'date',                      'name' => '用户注册时间', 'notes' => '', 'enable_to_sort' => true],
                ['column' => 'advertiser_cost',           'name' => '投放消耗', 'notes' => '', 'enable_to_sort' => true],
                ['column' => 'follow_uv',                 'name' => '企微关注数', 'notes' => '', 'enable_to_sort' => true],
                ['column' => 'per_follow_cost',           'name' => '企微关注成本', 'notes' => '', 'enable_to_sort' => true],
                ['column' => 'first_order_cost',          'name' => '下单成本', 'notes' => '投放消耗/当日新增用户首单人数', 'enable_to_sort' => true],
                ['column' => 'first_order_cost_unique',   'name' => '下单成本(去重)', 'notes' => '投放消耗/当日新增用户首单人数(去重)', 'enable_to_sort' => true],
                ['column' => 'total_cvt_amt',             'name' => '总回收金额', 'notes' => '', 'enable_to_sort' => true],
                ['column' => 'total_roi',                 'name' => '总回收', 'notes' => '总回收金额/投放消耗', 'enable_to_sort' => true],
            ];

            $overviewHead = $listHead = $head;
            foreach (DjRegUserRangeReport::count_range_days() as $day) {
                $listHead[] = ['column'=>'day'.$day.'_roi' ,  'name' => 'day'.$day , 'notes' => '', 'enable_to_sort' => false];
            }

            // 1mp 2adq
            if(1 == $orderType) {
                $query = DjDataCycleMp::query();
            } else {
                $query = DjDataCycleAdq::query();
            }

            $query->selectRaw('max(advertiser_cost) as advertiser_cost, max(total_cvt_amt) as total_cvt_amt, ' .
                'max(first_order_ucnt_unique) as first_order_ucnt_unique, max(first_day_roi) as first_day_roi, ' .
                'max(follow_uv) as follow_uv, max(first_order_ucnt) as first_order_ucnt, max(first_order_cost) ' .
                'as first_order_cost, max(first_order_active_cost) as first_order_cost_unique, max(per_follow_cost) ' .
                ' as per_follow_cost, max(total_roi) as total_roi, expense_date as date, max(first_order_active_ucnt) ' .
                " as first_order_active_ucnt");

            // null 全选 0小程序 1H5
            if(!is_null($platOrderType)) {
                $query->where('plat_order_type', $platOrderType);
            } else {
                $query->where('plat_order_type', 2);
            }

            $query->where('sys_group_id', $sysGroupId)
                ->where('enable', 1)
                ->where('expense_date', '>=', $beginDate)
                ->where('expense_date', '<=', $endDate);

            if(!is_null($closingDate)) $query->where('ref_date', '<=', $closingDate);
            if(!is_null($paidMin)) $query->where('advertiser_cost', '>=', $paidMin);
            if(!is_null($paidMax)) $query->where('advertiser_cost', '<=', $paidMax);
            if(!is_null($firstOrderCostMin)) $query->where('first_order_cost', '>=', $firstOrderCostMin);
            if(!is_null($firstOrderCostMax)) $query->where('first_order_cost', '<=', $firstOrderCostMax);
            if(!is_null($firstOrderCostUniqueMin)) $query->where('first_order_active_cost', '>=', $firstOrderCostUniqueMin);
            if(!is_null($firstOrderCostUniqueMax)) $query->where('first_order_active_cost', '<=', $firstOrderCostUniqueMax);
            if(!is_null($perFollowCostMin)) $query->where('per_follow_cost', '>=', $perFollowCostMin);
            if(!is_null($perFollowCostMax)) $query->where('per_follow_cost', '<=', $perFollowCostMax);
            if(!is_null($totalRoiMin)) $query->whereRaw('(total_roi*100) >= ' . $totalRoiMin);
            if(!is_null($totalRoiMax)) $query->whereRaw('(total_roi*100) <=' . $totalRoiMax);
            if(!is_null($firstDayRoiMin)) $query->whereRaw('(first_day_roi*100) >=' . $firstDayRoiMin);
            if(!is_null($firstDayRoiMax)) $query->whereRaw('(first_day_roi*100) <=' . $firstDayRoiMax);
            $query->groupBy(['expense_date']);

            $countQuery = clone $query;
            $count = $countQuery->get()->count();

            $list = $query->orderBy($sortField, $sortType)
                ->offset(($page - 1) * $pageSize)
                ->limit($pageSize)
                ->get();

            $dateList = array_unique($list->pluck('date')->toArray());

            if(1 == $orderType){
                # 获取激活趋势数据
                $activeDataList = DjDataCycleMp::query()
                    ->select(['expense_date', 'ref_date', 'daily_day_charge'])
                    ->where('enable', 1)
                    ->whereIn('expense_date', $dateList)
                    ->where('sys_group_id', $sysGroupId)
                    ->where(function($query) use ($platOrderType) {
                        if(!is_null($platOrderType)) {
                            $query->where('plat_order_type', $platOrderType);
                        } else {
                            $query->where('plat_order_type', 2);
                        }
                    })
                    ->get();
            } else {
                # 获取激活趋势数据
                $activeDataList = DjDataCycleAdq::query()
                    ->select(['expense_date', 'ref_date', 'daily_day_charge'])
                    ->where('enable', 1)
                    ->whereIn('expense_date', $dateList)
                    ->where('sys_group_id', $sysGroupId)
                    ->where(function($query) use ($platOrderType) {
                        if(!is_null($platOrderType)) {
                            $query->where('plat_order_type', $platOrderType);
                        } else {
                            $query->where('plat_order_type', 2);
                        }
                    })
                    ->get();
            }

            $searchDays = DjRegUserRangeReport::count_range_days();
            $maxSearchDay = end($searchDays);

            $finishDate = is_null($closingDate) ? date("Y-m-d") : $closingDate;

            foreach($list as $item) {
                //总ROi
                $item->total_roi = empty($item->total_roi) ? '0.00%' : ($item->total_roi * 100) . "%";
                // 首日roi
                $item->first_day_roi = empty($item->first_day_roi) ? '0.00%' : ($item->first_day_roi * 100) . '%';

                $activateList = $activeDataList->where('expense_date', $item->date);
                for($i = 0;$i < $maxSearchDay;$i++) {
                    $statDate = date("Y-m-d", strtotime('+'.$i. ' days', strtotime($item->date)));
                    if($statDate > $finishDate) {
                        $chargePrice = 0;
                    } else {
                        $activeInfo = $activateList->where('ref_date', $statDate)->first();
                        $chargePrice = $activeInfo->daily_day_charge ?? 0;
                    }

                    $day = $i+1;
                    $k = 'day'.$day.'_roi';
                    $item->$k = $item->advertiser_cost != 0 ? round($chargePrice/$item->advertiser_cost, 4) * 100 . '%' : '0.00%';
                }
            }

            $overviewList = self::dataCycleListOverview($beginDate, $endDate, $sysGroupId, $firstOrderCostMin, $firstOrderCostMax,
                $firstOrderCostUniqueMin, $firstOrderCostUniqueMax, $perFollowCostMin, $perFollowCostMax, $totalRoiMin,
                $totalRoiMax, $firstDayRoiMin, $firstDayRoiMax, $orderType, $platOrderType, $closingDate, $paidMin, $paidMax);

            $overview['head'] = $overviewHead;
            $overview['list'] = $overviewList;
            $data['head'] = $listHead;
            $data['list'] = $list;

        } catch (\Exception $exception) {
            Log::logError('DataStatisticsService.dataCycleList', [
                'request_data' => $requestData,
                'err_msg' => '数据循环统计接口发生异常',
                'line' => $exception->getLine(),
                'message' => $exception->getMessage(),
                'trace' => $exception->getTraceAsString(),
            ], 'interface');

            EmailQueue::rPush('数据循环统计接口发生异常', json_encode([
                'request_data' => $requestData,
                'line' => $exception->getLine(),
                'message' => $exception->getMessage(),
                'trace' => $exception->getTraceAsString(),
            ]), ['song.shen@kuxuan-inc.com'], '猎羽');
        }

        return [$count, $data, $overview];
    }

    public static function dataCycleListOverview($beginDate, $endDate, $sysGroupId, $firstOrderCostMin,
                                                 $firstOrderCostMax, $firstOrderCostUniqueMin, $firstOrderCostUniqueMax, $perFollowCostMin, $perFollowCostMax,
                                                 $totalRoiMin, $totalRoiMax, $firstDayRoiMin, $firstDayRoiMax, $orderType, $platOrderType, $closingDate, $paidMin,
                                                 $paidMax)
    {
        $data = ['date' => '汇总', 'advertiser_cost' => 0, 'follow_uv' => 0, 'per_follow_cost' => 0, 'first_order_cost' => 0,
            'first_order_cost_unique' => 0, 'total_cvt_amt' => 0, 'total_roi' => '0.00%'];

        // 1mp 2adq
        if(1 == $orderType) {
            $query = DjDataCycleMp::query();
        } else {
            $query = DjDataCycleAdq::query();
        }

        $query->selectRaw('expense_date, max(advertiser_cost) as advertiser_cost, max(total_cvt_amt) as total_cvt_amt, ' .
            'max(first_order_ucnt) as first_order_ucnt, max(follow_uv) as follow_uv, max(first_order_ucnt_unique) ' .
            'as first_order_ucnt_unique');

        // null 全选 0小程序 1H5
        if(!is_null($platOrderType)) {
            $query->where('plat_order_type', $platOrderType);
        } else {
            $query->where('plat_order_type', 2);
        }

        $query->where('sys_group_id', $sysGroupId)
            ->where('enable', 1)
            ->where('expense_date', '>=', $beginDate)
            ->where('expense_date', '<=', $endDate);

        if(!is_null($closingDate)) $query->where('ref_date', '<=', $closingDate);
        if(!is_null($paidMin)) $query->where('advertiser_cost', '>=', $paidMin);
        if(!is_null($paidMax)) $query->where('advertiser_cost', '<=', $paidMax);
        if(!is_null($firstOrderCostMin)) $query->where('first_order_cost', '>=', $firstOrderCostMin);
        if(!is_null($firstOrderCostMax)) $query->where('first_order_cost', '<=', $firstOrderCostMax);
        if(!is_null($firstOrderCostUniqueMin)) $query->where('first_order_active_cost', '>=', $firstOrderCostUniqueMin);
        if(!is_null($firstOrderCostUniqueMax)) $query->where('first_order_active_cost', '<=', $firstOrderCostUniqueMax);
        if(!is_null($perFollowCostMin)) $query->where('per_follow_cost', '>=', $perFollowCostMin);
        if(!is_null($perFollowCostMax)) $query->where('per_follow_cost', '<=', $perFollowCostMax);
        if(!is_null($totalRoiMin)) $query->whereRaw('(total_roi*100) >= ' . $totalRoiMin);
        if(!is_null($totalRoiMax)) $query->whereRaw('(total_roi*100) <= ' . $totalRoiMax);
        if(!is_null($firstDayRoiMin)) $query->whereRaw('(first_day_roi*100) >= ' . $firstDayRoiMin);
        if(!is_null($firstDayRoiMax)) $query->whereRaw('(first_day_roi*100) <= ' . $firstDayRoiMax);
        $query->groupBy(['expense_date']);

        $list = $query->get();

        if($list->isNotEmpty()) {
            $dateList = array_unique($list->pluck('expense_date')->toArray());

            $minDate = min($dateList);
            $maxDate = max($dateList);

            $orderQuery = DjOrder::query()
                ->where('is_ad_user', 1)
                ->where('pay_status', 1);

            $orderQuery->whereBetween("order_pay_time",[
                strtotime("$minDate 00:00:00")*1000,                    //订单范围开始时间
                strtotime("$maxDate 23:59:59")*1000                       //订单范围结束时间
            ])->whereBetween("mp_user_register_time",[
                strtotime("$minDate 00:00:00")*1000,                            //注册当日开始时间，毫秒
                strtotime("$maxDate 23:59:59")*1000             //注册当日结束时间，毫秒
            ])
                ->where('order_type', $orderType);

            if(!is_null($platOrderType)) {
                $orderQuery->where('plat_order_type', $platOrderType);
            }

            // 1mp 2adq
            if(1 == $orderType) {
                $accountIdArr = OfficialAccountRelation::query()
                    ->where('sys_group_id', $sysGroupId)
                    ->where('enable', 1)
                    ->get()
                    ->pluck('app_id')
                    ->toArray();

                $orderQuery->whereIn("bind_app_id", $accountIdArr);
            } else if (2 == $orderType) {
                $accountIdArr = OfficialWebUserActionSetId::query()
                    ->where('sys_group_id', $sysGroupId)
                    ->where('enable', 1)
                    ->get()
                    ->pluck('account_id')
                    ->toArray();

                $orderQuery->whereIn("adq_account_id", $accountIdArr);
            }

            $orderQuery->whereRaw('FROM_UNIXTIME(LEFT(`mp_user_register_time`, 10), "%Y-%m-%d") = ' .
                'FROM_UNIXTIME(LEFT(`order_pay_time`, 10), "%Y-%m-%d")');

            $first_order_ucnt_unique = $orderQuery->selectRaw('count(distinct(external_userid)) as count')->first()->count;

            $data['advertiser_cost'] = round($list->sum('advertiser_cost'), 2);
            $data['follow_uv'] = $list->sum('follow_uv');
            $data['per_follow_cost'] = ($data['follow_uv'] != 0) ? round($data['advertiser_cost'] / $data['follow_uv'], 2) : 0;
            $first_order_ucnt = $list->sum('first_order_ucnt');

            $data['first_order_cost'] = ($first_order_ucnt != 0) ? round($data['advertiser_cost'] / $first_order_ucnt, 2) : 0;
            $data['first_order_cost_unique'] = ($first_order_ucnt_unique != 0) ? round($data['advertiser_cost'] / $first_order_ucnt_unique, 2) : 0;
            $data['total_cvt_amt'] = round($list->sum('total_cvt_amt'), 2);
            $data['total_roi'] = ($data['advertiser_cost'] != 0) ? round($data['total_cvt_amt'] / $data['advertiser_cost'], 4) * 100 . '%' : '0.00%';
        }
        return $data;
    }

    /**
     * 获取数据看板更新时间
     * */
    public static function getDataUptime($type)
    {
        switch($type) {
            case 'account_data_trend':
            case 'account_data_trend_mp':
            case 'account_data_trend_adq':
                $datetime = RedisModel::get(AccountDataTrend::ACCOUNT_DATA_TREND_UPTIME);
                if(!$datetime) {
                    EmailQueue::rPush('获取账号数据趋势更新时间异常', $type, ['xiaohua.hou@kuxuan-inc.com'], '猎羽');
                    $datetime = date('Y-m-d H:i:s');
                }
                break;
            case 'playlet_data_trend_mp':   // mp 短剧数据趋势
                $datetime = RedisModel::get(PlayletAccountDataTrend::MP_DATA_TREND_UPTIME);
                if(!$datetime) {
                    $datetime = MpPlayletTrendData::query()->orderBy('updated_at', 'desc')->limit(1)
                        ->value('updated_at');
                    if($datetime)
                        RedisModel::set(PlayletAccountDataTrend::MP_DATA_TREND_UPTIME, $datetime);
                }
                break;
            case 'playlet_data_trend_adq':  // adq 短剧数据趋势
                $datetime = RedisModel::get(PlayletAccountDataTrend::ADQ_DATA_TREND_UPTIME);
                if(!$datetime) {
                    $datetime = AdqPlayletTrendData::query()->orderBy('updated_at', 'desc')->limit(1)
                        ->value('updated_at');
                    if($datetime)
                        RedisModel::set(PlayletAccountDataTrend::ADQ_DATA_TREND_UPTIME, $datetime);
                }
                break;
            case 'playlet_data_trend':  // 总表 短剧数据趋势
                $datetime = RedisModel::get(PlayletAccountDataTrend::MP_DATA_TREND_UPTIME);
                if(!$datetime) {
                    $datetime = PlayletDataTrend::query()->orderBy('updated_at', 'desc')->limit(1)
                        ->value('updated_at');
                    if($datetime)
                        RedisModel::set(PlayletAccountDataTrend::MP_DATA_TREND_UPTIME, $datetime);
                }
                break;
            case 'data_cycle_mp':  // mp 数据循环统计
                $datetime = RedisModel::get(DjRegUserRangeReport::DATA_TREND_UPTIME);
                if(!$datetime) {        // Redis中无数据
                    $datetime = DjRegUserRangeReport::query()->orderBy('updated_at', 'desc')->limit(1)
                        ->value('updated_at');
                    if($datetime)
                        RedisModel::set(DjRegUserRangeReport::DATA_TREND_UPTIME, $datetime);
                }
                break;
            case 'data_cycle_adq': // adq 数据循环统计
                $datetime = RedisModel::get(DjRegUserRangeReport::DATA_TREND_UPTIME);
                if(!$datetime) {
                    $datetime = DjRegUserRangeReport::query()->orderBy('updated_at', 'desc')->limit(1)
                        ->value('updated_at');
                    if($datetime)
                        RedisModel::set(DjRegUserRangeReport::DATA_TREND_UPTIME, $datetime);
                }
                break;
            case 'data_cycle_nur':// 花生数据循环统计
                $datetime = RedisModel::get(DjDataCycleNur::UPTIME);
                if(!$datetime){
                    $datetime = DjDataCycleNur::query()->orderBy('updated_at', 'desc')->limit(1)
                        ->value('updated_at');
                    if($datetime){
                        RedisModel::set(DjDataCycleNur::UPTIME, $datetime);
                    }
                }
                break;
            case 'operate_data_trend':
                $datetime = RedisModel::get(OperateDataTrend::ACCOUNT_DATA_TREND_UPTIME);
                break;
            case 'cumulative_recovery_data':
                $datetime = RedisModel::get(DjCumulativeRecoveryData::UPDATE_TIME);
                break;
            case 'account_funds':
                $datetime = RedisModel::get(TencentAdAuth::ADQ_ACCOUNT_FUNDS_UPDATE_TIME);
                break;
            default:
                $datetime = null;
                break;
        }

        return $datetime;
    }

    public static function huaShengDataCycleList($adminId, $isSystemAdmin, $beginDate,$endDate,$page,$pageSize
        , $sysGroupId, $firstOrderCostMin,$firstOrderCostMax, $perFollowCostMin, $perFollowCostMax, $totalRoiMin
        , $totalRoiMax, $firstDayRoiMin, $firstDayRoiMax, $closingDate, $paidMin, $paidMax, $sortField, $sortType
        , $appId, $platOrderType, $orderSource)
    {
        $requestData = [
            'admin_id'             => $adminId,
            'is_system_admin'      => $isSystemAdmin,
            'begin_date'           => $beginDate,
            'end_date'             => $endDate,
            'page'                 => $page,
            'page_size'            => $pageSize,
            'sys_group_id'         => $sysGroupId,
            'first_order_cost_min' => $firstOrderCostMin,
            'first_order_cost_max' => $firstOrderCostMax,
            'per_follow_cost_min'  => $perFollowCostMin,
            'per_follow_cost_max'  => $perFollowCostMax,
            'total_roi_min'        => $totalRoiMin,
            'total_roi_max'        => $totalRoiMax,
            'first_day_roi_min'    => $firstDayRoiMin,
            'first_day_roi_max'    => $firstDayRoiMax,
            'paid_min'             => $paidMin,
            'paid_max'             => $paidMax,
            'sort_field'           => $sortField,
            'closing_date'         => $closingDate,
            'sort_type'            => $sortType,
            'order_source'         => $orderSource,
        ];

        $count = 0;
        $overview = [];
        $data = [];

        try{
            //补充头列表
            $head = [
                ['column' => 'date', 'name' => '日期', 'notes' => '', 'enable_to_sort' => true],
                ['column' => 'app_name', 'name' => '账户', 'notes' => '', 'enable_to_sort' => false],
                ['column' => 'advertiser_cost', 'name' => '消耗', 'notes' => '', 'enable_to_sort' => true],
                ['column' => 'follow_uv', 'name' => '企微添加人数', 'notes' => '', 'enable_to_sort' => true],
                ['column' => 'per_follow_cost', 'name' => '企微成本', 'notes' => '当日消耗/企微添加人数',
                    'enable_to_sort' => true],
                ['column' => 'first_order_cnt', 'name' => '首日下单量(次数)', 'notes' => '', 'enable_to_sort' => true],
                ['column' => 'first_order_ucnt', 'name' => '首日下单量(人数)', 'notes' => '', 'enable_to_sort' => true],
                ['column' => 'first_order_cost', 'name' => '新用户下单成本', 'notes' => '当日消耗/新用户充值人数',
                    'enable_to_sort' => true],
                ['column' => 'first_day_charge', 'name' => '首日回收金额', 'notes' => '当日新用户充值金额',
                    'enable_to_sort' => true],
                ['column' => 'first_day_roi', 'name' => '首日ROI', 'notes' => '当日新用户充值金额/当日消耗',
                    'enable_to_sort' => true],
                ['column' => 'total_cvt_ucnt', 'name' => '累计下单人数', 'notes' => '链接/小程序注册时间为当天的用户累积充值人数',
                    'enable_to_sort' => true],
                ['column' => 'total_order_cost', 'name' => '累计下单成本', 'notes' => '当日消耗/累积下单人数',
                    'enable_to_sort' => true],
                ['column' => 'total_cvt_amt', 'name' => '累计下单金额', 'notes' => '链接/小程序注册时间为这一天用户的累积下单金额',
                    'enable_to_sort' => true],
                ['column' => 'total_roi', 'name' => '累计回本率', 'notes' => '链接/小程序注册时间为这一天用户的累积下单金额/当日消耗',
                    'enable_to_sort' => true],
            ];

            $overviewHead = $listHead = $head;
            foreach (DjRegUserRangeReport::count_range_days() as $day) {
                $listHead[] = ['column'=>'day'.$day.'_roi' ,  'name' => 'day'.$day , 'notes' => '', 'enable_to_sort' => false];
            }

            # 当前登录账号绑定的公众号列表
            $accountList = OfficialAccount::getAccountMpAppIdList($adminId, $sysGroupId, $isSystemAdmin);

            # 在系统中配置的公众号列表
            $appList = AccountConfigNoUserRelation::query()->where('enable', 1)
                ->where('sys_group_id', $sysGroupId)->where(function($query) use ($appId) {
                    if($appId) $query->where('app_id', $appId);
                })->whereIn('app_id', $accountList)->get();
            $appIdList = $appList->isNotEmpty() ? array_unique(array_column($appList->toArray(), 'app_id')) : [];

            // 批量获取公众号名称
            $appDataList = OfficialAccount::query()->whereIn('mp_app_id', $appIdList)->get();

            $query = DjDataCycleNur::query();

            // null 全选 0小程序 1H5
            if(!is_null($platOrderType)) {
                $query->where('plat_order_type', $platOrderType);
            } else {
                $query->where('plat_order_type', 2);
            }

            $query->whereIn('app_id', $appIdList)
                ->where('enable', 1)
                ->where('expense_date', '>=', $beginDate)
                ->where('expense_date', '<=', $endDate);

            if(!is_null($closingDate)) $query->where('ref_date', '<=', $closingDate);
            if(!is_null($paidMin)) $query->where('advertiser_cost', '>=', $paidMin);
            if(!is_null($paidMax)) $query->where('advertiser_cost', '<=', $paidMax);
            if(!is_null($firstOrderCostMin)) $query->where('first_order_cost', '>=', $firstOrderCostMin);
            if(!is_null($firstOrderCostMax)) $query->where('first_order_cost', '<=', $firstOrderCostMax);
            if(!is_null($perFollowCostMin)) $query->where('per_follow_cost', '>=', $perFollowCostMin);
            if(!is_null($perFollowCostMax)) $query->where('per_follow_cost', '<=', $perFollowCostMax);
            if(!is_null($totalRoiMin)) $query->whereRaw('(total_roi*100) >= ' . $totalRoiMin);
            if(!is_null($totalRoiMax)) $query->whereRaw('(total_roi*100) <=' . $totalRoiMax);
            if(!is_null($firstDayRoiMin)) $query->whereRaw('(first_day_roi*100) >=' . $firstDayRoiMin);
            if(!is_null($firstDayRoiMax)) $query->whereRaw('(first_day_roi*100) <=' . $firstDayRoiMax);
            if(!empty($orderSource)) $query->where('order_source', $orderSource);
            $query->groupBy(['expense_date', 'app_id']);

            $countQuery = clone $query;

            $query->selectRaw('expense_date as date, app_id, max(advertiser_cost) as advertiser_cost, ' .
                'max(follow_uv) as follow_uv, max(per_follow_cost) as per_follow_cost, max(first_order_cnt) as ' .
                'first_order_cnt, max(first_order_cost) as first_order_cost, max(first_day_charge) as first_day_charge,' .
                ' max(first_day_roi) as first_day_roi, max(total_cvt_ucnt) as total_cvt_ucnt, min(total_order_cost) ' .
                'as total_order_cost, max(total_cvt_amt) as total_cvt_amt, max(total_roi) as total_roi, max(first_order_ucnt) ' .
                ' as first_order_ucnt');

            $count = $countQuery->get()->count();

            $list = $query->orderBy($sortField, $sortType)
                ->offset(($page - 1) * $pageSize)
                ->limit($pageSize)
                ->get();

            $dateList = array_unique($list->pluck('date')->toArray());

            # 获取激活趋势数据
            $activeDataList = DjDataCycleNur::query()
                ->select(['expense_date', 'ref_date', 'daily_day_charge', 'app_id'])
                ->where('enable', 1)
                ->whereIn('expense_date', $dateList)
                ->whereIn('app_id', $appIdList)
                ->where(function($query) use ($platOrderType, $orderSource){
                    // null 全选 0小程序 1H5
                    if(!is_null($platOrderType)) {
                        $query->where('plat_order_type', $platOrderType);
                    } else {
                        $query->where('plat_order_type', 2);
                    }
                })->where('order_source', $orderSource)->get();

            $searchDays = DjRegUserRangeReport::count_range_days();
            $maxSearchDay = end($searchDays);

            $finishDate = is_null($closingDate) ? date("Y-m-d") : $closingDate;

            foreach($list as $item) {
                // 公众号名称
                $appData = $appDataList->where('mp_app_id', $item->app_id)->first();
                $item->app_name = $appData->mp_name ?? '';

                //总ROi
                $item->total_roi = empty($item->total_roi) ? '0%' : ($item->total_roi * 100) . "%";
                // 首日roi
                $item->first_day_roi = empty($item->first_day_roi) ? '0%' : ($item->first_day_roi * 100) . '%';

                $activateList = $activeDataList->where('expense_date', $item->date)->where('app_id', $item->app_id);
                for($i = 0;$i < $maxSearchDay;$i++) {
                    $statDate = date("Y-m-d", strtotime('+'.$i. ' days', strtotime($item->date)));
                    if($statDate > $finishDate) {
                        $chargePrice = 0;
                    } else {
                        $activeInfo = $activateList->where('ref_date', $statDate)->first();
                        $chargePrice = $activeInfo->daily_day_charge ?? 0;
                    }

                    $day = $i+1;
                    $k = 'day'.$day.'_roi';
                    $item->$k = $item->advertiser_cost != 0 ? round($chargePrice/$item->advertiser_cost, 4) * 100 . '%' : '0%';
                }
            }

            $overviewList = self::huaShengDataCycleListOverview($appIdList, $beginDate, $endDate, $firstOrderCostMin
                , $firstOrderCostMax, $perFollowCostMin, $perFollowCostMax, $totalRoiMin, $totalRoiMax, $firstDayRoiMin
                , $firstDayRoiMax, $closingDate, $paidMin, $paidMax, $platOrderType, $orderSource);

            $overview['head'] = $overviewHead;
            $overview['list'] = $overviewList;
            $data['head'] = $listHead;
            $data['list'] = $list;

        } catch (\Exception $exception) {
            Log::logError('HuaShengDataStatisticsService.dataCycleList', [
                'request_data' => $requestData,
                'err_msg' => '数据循环统计接口发生异常',
                'line' => $exception->getLine(),
                'message' => $exception->getMessage(),
                'trace' => $exception->getTraceAsString(),
            ], 'interface');

            EmailQueue::rPush('花生数据循环统计接口发生异常', json_encode([
                'request_data' => $requestData,
                'line' => $exception->getLine(),
                'message' => $exception->getMessage(),
                'trace' => $exception->getTraceAsString(),
            ]), ['song.shen@kuxuan-inc.com'], '猎羽');
        }

        return [$count, $data, $overview];
    }

    public static function huaShengDataCycleListOverview($appIdList, $beginDate, $endDate, $firstOrderCostMin
        , $firstOrderCostMax, $perFollowCostMin, $perFollowCostMax, $totalRoiMin, $totalRoiMax, $firstDayRoiMin
        , $firstDayRoiMax, $closingDate, $paidMin, $paidMax, $platOrderType, $orderSource)
    {
        $data = ['date' => '汇总', 'app_name' => '账户', 'advertiser_cost' => 0, 'follow_uv' => 0, 'per_follow_cost' => 0,
            'first_order_cnt' => 0, 'first_order_ucnt' => 0, 'first_order_cost' => 0,' first_day_charge' => 0,
            'first_day_roi' => '0%', 'total_cvt_ucnt' => 0, 'total_order_cost' => 0, 'total_cvt_amt' => 0, 'total_roi' => '0%'];

        $query = DjDataCycleNur::query();

        // null 全选 0小程序 1H5
        if(!is_null($platOrderType)) {
            $query->where('plat_order_type', $platOrderType);
        } else {
            $query->where('plat_order_type', 2);
        }

        $query->selectRaw('expense_date as date, app_id, max(advertiser_cost) as advertiser_cost, ' .
            'max(follow_uv) as follow_uv, max(first_order_cnt) as first_order_cnt, max(first_order_ucnt) as ' .
            ' first_order_ucnt, max(first_day_charge) as first_day_charge, max(first_order_ucnt) as first_order_ucnt, ' .
            ' max(total_cvt_ucnt) as total_cvt_ucnt, max(total_cvt_amt) as total_cvt_amt');

        $query->whereIn('app_id', $appIdList)
            ->where('enable', 1)
            ->where('expense_date', '>=', $beginDate)
            ->where('expense_date', '<=', $endDate);

        if(!is_null($closingDate)) $query->where('ref_date', '<=', $closingDate);
        if(!is_null($paidMin)) $query->where('advertiser_cost', '>=', $paidMin);
        if(!is_null($paidMax)) $query->where('advertiser_cost', '<=', $paidMax);
        if(!is_null($firstOrderCostMin)) $query->where('first_order_cost', '>=', $firstOrderCostMin);
        if(!is_null($firstOrderCostMax)) $query->where('first_order_cost', '<=', $firstOrderCostMax);
        if(!is_null($perFollowCostMin)) $query->where('per_follow_cost', '>=', $perFollowCostMin);
        if(!is_null($perFollowCostMax)) $query->where('per_follow_cost', '<=', $perFollowCostMax);
        if(!is_null($totalRoiMin)) $query->whereRaw('(total_roi*100) >= ' . $totalRoiMin);
        if(!is_null($totalRoiMax)) $query->whereRaw('(total_roi*100) <= ' . $totalRoiMax);
        if(!is_null($firstDayRoiMin)) $query->whereRaw('(first_day_roi*100) >= ' . $firstDayRoiMin);
        if(!is_null($firstDayRoiMax)) $query->whereRaw('(first_day_roi*100) <= ' . $firstDayRoiMax);
        if(!empty($orderSource)) $query->where('order_source', $orderSource);
        $query->groupBy(['expense_date', 'app_id']);

        $list = $query->get();

        if($list->isNotEmpty()) {
            $data['advertiser_cost'] = round($list->sum('advertiser_cost'), 2);
            $data['follow_uv'] = $list->sum('follow_uv');
            $data['per_follow_cost'] = ($data['follow_uv'] != 0) ? round($data['advertiser_cost'] / $data['follow_uv'], 2) : 0;
            $data['first_order_cnt'] = $list->sum('first_order_cnt');// 首日下单次数
            $data['first_order_ucnt'] = $list->sum('first_order_ucnt');// 首日下单人数
            $first_order_ucnt = $list->sum('first_order_ucnt');
            $data['first_order_cost'] = ($first_order_ucnt != 0) ? round($data['advertiser_cost'] /
                $first_order_ucnt, 2) : 0;
            $data['first_day_charge'] = round($list->sum('first_day_charge'), 2);
            $data['first_day_roi'] = ($data['advertiser_cost'] != 0) ? round($data['first_day_charge'] /
                    $data['advertiser_cost'], 4) * 100 . '%' : '0%';
            $data['total_cvt_ucnt'] = $list->sum('total_cvt_ucnt');
            $data['total_order_cost'] = ($data['total_cvt_ucnt'] != 0) ? round($data['advertiser_cost'] /
                $data['total_cvt_ucnt'], 2) : 0;
            $data['total_cvt_amt'] = round($list->sum('total_cvt_amt'), 2);
            $data['total_roi'] = ($data['advertiser_cost'] != 0) ? round($data['total_cvt_amt'] /
                    $data['advertiser_cost'], 4) * 100 . '%' : '0%';
        }
        return $data;
    }

    public static function dealDaysCharge($refDate, &$item, $charge_data) {
        $item['day3_charge'] = 0;
        $item['day7_charge'] = 0;

        for ($i = 0; $i < 7; $i++) {
            if($i < 3) {
                $item['day3_charge'] += (!empty($charge_data[$i]['day_charge']) ? $charge_data[$i]['day_charge'] : 0);
            }

            if($i < 7) {
                $item['day7_charge'] += (!empty($charge_data[$i]['day_charge']) ? $charge_data[$i]['day_charge'] : 0);
            }
        }

        if(strtotime(date('Y-m-d')) - strtotime($refDate) < 2 * 86400) {
            $item['day3_charge'] = 0;
            $item['day7_charge'] = 0;
        } else if(strtotime(date('Y-m-d')) - strtotime($refDate) < 6 * 86400) {
            $item['day7_charge'] = 0;
        }
    }

    public static function pitcherAdqAccountData($params, $sortField, $sortType) {
        $head = [
            ['column' => 'pitcher_name', 'name' => '优化师', 'notes' => '', 'enable_to_sort' => false],
            ['column' => 'account_nums', 'name' => '账户数', 'notes' => '有消耗的账户', 'enable_to_sort' => true],
            ['column' => 'avg_days', 'name' => '平均投放天数', 'notes' => '有消耗的投放天数/账户数', 'enable_to_sort' => true],
            ['column' => 'total_paid', 'name' => '总消耗', 'notes' => '', 'enable_to_sort' => true],
//            ['column' => 'account_nums_0', 'name' => '<1.5k', 'notes' => '', 'enable_to_sort' => true],
//            ['column' => 'account_nums_15', 'name' => '1.5k~5k', 'notes' => '', 'enable_to_sort' => true],
//            ['column' => 'account_nums_50', 'name' => '5k~1w', 'notes' => '', 'enable_to_sort' => true],
//            ['column' => 'account_nums_100', 'name' => '>1w', 'notes' => '', 'enable_to_sort' => true],
//            ['column' => 'paid_0', 'name' => '<1.5k', 'notes' => '', 'enable_to_sort' => true],
//            ['column' => 'paid_15', 'name' => '1.5~5k', 'notes' => '', 'enable_to_sort' => true],
//            ['column' => 'paid_50', 'name' => '5k~1w', 'notes' => '', 'enable_to_sort' => true],
//            ['column' => 'paid_100', 'name' => '>1w', 'notes' => '', 'enable_to_sort' => true],
            ['column' => 'account_nums_0_rate_format', 'name' => '<1.5k(账户数)', 'notes' => '', 'enable_to_sort' => true],
            ['column' => 'account_nums_15_rate_format', 'name' => '1.5k~5k(账户数)', 'notes' => '', 'enable_to_sort' => true],
            ['column' => 'account_nums_50_rate_format', 'name' => '5k~1w(账户数)', 'notes' => '', 'enable_to_sort' => true],
            ['column' => 'account_nums_100_rate_format', 'name' => '>1w(账户数)', 'notes' => '', 'enable_to_sort' => true],
            ['column' => 'paid_0_rate_format', 'name' => '<1.5k(消耗)', 'notes' => '', 'enable_to_sort' => true],
            ['column' => 'paid_15_rate_format', 'name' => '1.5k~5k(消耗)', 'notes' => '', 'enable_to_sort' => true],
            ['column' => 'paid_50_rate_format', 'name' => '5k~1w(消耗)', 'notes' => '', 'enable_to_sort' => true],
            ['column' => 'paid_100_rate_format', 'name' => '>1w(消耗)', 'notes' => '', 'enable_to_sort' => true],
            ['column' => 'account_cumulative_consumption', 'name' => '单账户累计消耗', 'notes' => '总消耗/账户数', 'enable_to_sort' => true],
            ['column' => 'account_daily_consumption', 'name' => '单账户日均消耗', 'notes' => '单账户累计消耗/平均投放天数', 'enable_to_sort' => true],
        ];

        $allAccountIdList = PitcherService::adqAccountListForUser($params['admin_id'], $params['sys_group_id'], $params['is_system_admin']);

        # 批量查询投手名称
        $pitcherNameList = Users::query()->where('enable', 1)->where('group_admin_id', $params['sys_group_id'])
            ->select(['id', 'name'])->get();
        $userIdList = array_column($pitcherNameList->toArray(), 'id');

        # 找到日期范围内所有的绑定关系配置
        $confList = DramaUserRela::getAdqConfListByDate($params['start_date'], $params['end_date'], $allAccountIdList, $userIdList);

        # 提取配置内的投放账号以及投手
        $pitcherIdList = array_unique(array_filter(array_column($confList->toArray(), 'user_id')));
        $accountIdList = array_unique(array_filter(array_column($confList->toArray(), 'account_id')));

        # 获取投放账号在日期范围内的所有投放消耗数据
        $accountPaidList = TencentAdDailyReport::query()->where('enable', 1)->where('ref_date', '>=', $params['start_date'])
            ->where('ref_date', '<=', $params['end_date'])->whereIn('account_id', $accountIdList)
            ->where('paid', '>', 0)
            ->select(['account_id', 'paid', 'ref_date'])->get();

        $pitcherDataList = [];
        $accountDateList = [];
        foreach($confList as $confInfo) {
            $confStartDate = ($confInfo->start_date < $params['start_date']) ? $params['start_date'] : $confInfo->start_date;
            if(!empty($confInfo->disable_date)) {
                $confEndDate = $confInfo->disable_date;
            } else {
                $confEndDate = $confInfo->end_date;
            }

            $confEndDate = ($confEndDate > $params['end_date']) ? $params['end_date'] : $confEndDate;
            if($confEndDate < $confStartDate) {
                continue;
            }

            # 统计配置中的投放天数
//            $days = round((strtotime($confEndDate.' 23:59:59') - strtotime($confStartDate.' 00:00:00'))/86400);

//            $pitcherDataList[$confInfo->user_id]['days'] = isset($pitcherDataList[$confInfo->user_id]['days']) ?
//                ($pitcherDataList[$confInfo->user_id]['days'] + $days) : $days;
            # 投放配置中投手关联的投放账号，如果这里注释掉，则只会统计有消耗金额的账户数
//            $pitcherDataList[$confInfo->user_id]['account'][$confInfo->account_id] = 0;

            $date = $confStartDate;
            while($date <= $confEndDate) {
                $key = $confInfo->account_id.'#'.$date;
                $accountDateList[$key][] = $confInfo->user_id;
                $date = date('Y-m-d', strtotime($date. ' +1 day'));
            }
        }

        foreach($accountPaidList as $accountPaidInfo) {
            $info = $accountDateList[$accountPaidInfo->account_id.'#'.$accountPaidInfo->ref_date] ?? [];
            $amount = $accountPaidInfo->paid/100;
            if(!empty($info)) {
                foreach($info as $userId) {
                    $pitcherDataList[$userId]['account'][$accountPaidInfo->account_id] = isset($pitcherDataList[$userId]['account'][$accountPaidInfo->account_id])
                        ? ($pitcherDataList[$userId]['account'][$accountPaidInfo->account_id] + $amount) : $amount;
                    $pitcherDataList[$userId]['amount'] = isset($pitcherDataList[$userId]['amount']) ?
                        round($pitcherDataList[$userId]['amount'] + $amount, 2) : $amount;
                    # 只统计有消耗的投放天数
                    $pitcherDataList[$userId]['days'] = isset($pitcherDataList[$userId]['days']) ?
                        ($pitcherDataList[$userId]['days'] + 1) : 1;
                }
            }
        }

        # 整理投手投放消耗数据
        $dataList = [];
        foreach($pitcherIdList as $pitcherId) {
            $item = [];
            $pitcherInfo = $pitcherNameList->where('id', $pitcherId)->first();
            $item['pitcher_name'] = !empty($pitcherInfo->name) ? $pitcherInfo->name : '';
            $item['pitcher_id'] = $pitcherId;
            $dataInfo = $pitcherDataList[$pitcherId] ?? [];
            $item['account_nums'] = isset($dataInfo['account']) ? count($dataInfo['account']) : 0;
            $item['total_days'] = $dataInfo['days'] ?? 0;
            $item['avg_days'] = 0;
            $item['total_paid'] = $dataInfo['amount'] ?? 0;
            $item['account_nums_0'] = 0;
            $item['account_nums_15'] = 0;
            $item['account_nums_50'] = 0;
            $item['account_nums_100'] = 0;
            $item['paid_0'] = 0;
            $item['paid_15'] = 0;
            $item['paid_50'] = 0;
            $item['paid_100'] = 0;
            $item['account_nums_0_rate_format'] = '0%';
            $item['account_nums_15_rate_format'] = '0%';
            $item['account_nums_50_rate_format'] = '0%';
            $item['account_nums_100_rate_format'] = '0%';
            $item['account_nums_0_rate'] = 0;
            $item['account_nums_15_rate'] = 0;
            $item['account_nums_50_rate'] = 0;
            $item['account_nums_100_rate'] = 0;
            $item['paid_0_rate_format'] = '0%';
            $item['paid_15_rate_format'] = '0%';
            $item['paid_50_rate_format'] = '0%';
            $item['paid_100_rate_format'] = '0%';
            $item['paid_0_rate'] = 0;
            $item['paid_15_rate'] = 0;
            $item['paid_50_rate'] = 0;
            $item['paid_100_rate'] = 0;
            $item['account_cumulative_consumption'] = 0;
            $item['account_daily_consumption'] = 0;

            if(!empty($dataInfo['account'])) {
                $item['account_nums_0_arr'] = $item['account_nums_15_arr'] = $item['account_nums_50_arr'] = $item['account_nums_100_arr'] = [];
                foreach($dataInfo['account'] as $key => $value) {
                    if($value <= 1500) {
                        $item['account_nums_0_arr'][] = $key;
                        $item['paid_0'] = isset($item['paid_0']) ? round($item['paid_0'] + $value, 2) : $value;
                    }

                    if($value > 1500 && $value <= 5000) {
                        $item['account_nums_15_arr'][] = $key;
                        $item['paid_15'] = isset($item['paid_15']) ? round($item['paid_15'] + $value, 2) : $value;
                    }

                    if($value > 5000 && $value <= 10000) {
                        $item['account_nums_50_arr'][] = $key;
                        $item['paid_50'] = isset($item['paid_50']) ? round($item['paid_50'] + $value, 2) : $value;
                    }

                    if($value > 10000) {
                        $item['account_nums_100_arr'][] = $key;
                        $item['paid_100'] = isset($item['paid_100']) ? round($item['paid_100'] + $value, 2) : $value;
                    }
                }

                $item['avg_days'] = $item['account_nums'] >0 ? round($item['total_days'] / $item['account_nums']) : 0;
                $item['account_nums_0'] = count($item['account_nums_0_arr']);
                $item['account_nums_15'] = count($item['account_nums_15_arr']);
                $item['account_nums_50'] = count($item['account_nums_50_arr']);
                $item['account_nums_100'] = count($item['account_nums_100_arr']);
                $item['account_nums_0_rate'] = $item['account_nums'] > 0 ? round($item['account_nums_0'] / $item['account_nums'], 4) : 0;
                $item['account_nums_15_rate'] = $item['account_nums'] > 0 ? round($item['account_nums_15'] / $item['account_nums'], 4) : 0;
                $item['account_nums_50_rate'] = $item['account_nums'] > 0 ? round($item['account_nums_50'] / $item['account_nums'], 4) : 0;
                $item['account_nums_100_rate'] = $item['account_nums'] > 0 ? round($item['account_nums_100'] / $item['account_nums'], 4) : 0;
                $item['account_nums_0_rate_format'] = $item['account_nums_0_rate'] * 100 . '%';
                $item['account_nums_15_rate_format'] = $item['account_nums_15_rate'] * 100 . '%';
                $item['account_nums_50_rate_format'] = $item['account_nums_50_rate'] * 100 . '%';
                $item['account_nums_100_rate_format'] = $item['account_nums_100_rate'] * 100 . '%';
                $item['paid_0_rate'] = $item['total_paid'] > 0 ? round($item['paid_0'] / $item['total_paid'], 4) : 0;
                $item['paid_15_rate'] = $item['total_paid'] > 0 ? round($item['paid_15'] / $item['total_paid'], 4) : 0;
                $item['paid_50_rate'] = $item['total_paid'] > 0 ? round($item['paid_50'] / $item['total_paid'], 4) : 0;
                $item['paid_100_rate'] = $item['total_paid'] > 0 ? round($item['paid_100'] / $item['total_paid'], 4) : 0;
                $item['paid_0_rate_format'] = $item['paid_0_rate'] * 100 . '%';
                $item['paid_15_rate_format'] = $item['paid_15_rate'] * 100 . '%';
                $item['paid_50_rate_format'] = $item['paid_50_rate'] * 100 . '%';
                $item['paid_100_rate_format'] = $item['paid_100_rate'] * 100 . '%';
                $item['account_cumulative_consumption'] = $item['account_nums'] > 0 ? round($item['total_paid'] / $item['account_nums'], 2) : 0;
                $item['account_daily_consumption'] = $item['avg_days'] > 0 ? round($item['account_cumulative_consumption'] / $item['avg_days'], 2) : 0;

                unset($item['account_nums_0_arr']);
                unset($item['account_nums_15_arr']);
                unset($item['account_nums_50_arr']);
                unset($item['account_nums_100_arr']);
            }

            $dataList[] = $item;
        }

        # 分页排序
        // 对排序字段做处理
        if(strpos($sortField, '_format') !== false ) {
            $sortField = substr($sortField, 0, -7);
        }
        $sortArr = array_column($dataList, $sortField);
        array_multisort($sortArr, $sortType=='asc'? SORT_ASC : SORT_DESC, $dataList);

        // $result = array_slice($result, ($page - 1) * $pageSize, $pageSize);

        # 返回表头以及数据
        return ['data' => $dataList, 'extra' => $head];
    }
}