playlet/app/Service/ShortPlayDat...

<?php
/**
 * Created by PhpStorm.
 * User: shensong
 * Date: 2022/9/18
 * Time: 21:21
 */

namespace App\Service;


use App\Log;
use App\Models\AdqPlayletTrendData;
use App\Models\DramaSeries;
use App\Models\Es\PlayletAccountDataTrend;
use App\Models\OfficialAccount;
use App\Models\PlayletAccountTrendData;
use App\Models\Report\PlayletDataTrend;
use App\Models\MpPlayletTrendData;

class ShortPlayDataTrendService
{
    public static function adqShortPlayDataTrendList($params, $page, $pageSize, $sortField, $sortType)
    {
        # 表头处理
        $commonHeader = config('shortPlayDataTrendHeader.basic');
        # 获取day1至day150表头
        $daysHeader = [];
        for($i = 0;$i < AdqPlayletTrendData::PLAYLET_TREND_DATA_RECORD_DAYS;$i++) {
            $title = [
                'name' => 'DAY'.($i+1),
                'column' => 'day'.($i+1)
            ];
            $daysHeader[] = $title;
        }
        $header = array_merge($commonHeader, $daysHeader);

        # 查询数据
        if(isset($params['playlet_id']) && $params['playlet_id']) {
            list($list, $count) = AdqPlayletTrendData::getShortPlayDataTrendTrend($params, $sortField, $sortType, $page, $pageSize);
        } else {
            # 获取企业下的adq账号
            $playletIdList = DramaSeries::query()
                ->select('id')
                ->where('enable', 1)
                ->where('sys_group_id', $params['sys_group_id'])
                ->distinct()
                ->pluck('id');

            if(empty($playletIdList)) return [0, [], $header];
            list($list, $count) = AdqPlayletTrendData::getShortPlayDataTrendSummaryData(
                $params, $playletIdList, $sortField, $sortType, $page, $pageSize
            );
        }

        if(empty($list)) return [0, [], $header];

        # 获取短剧信息
        $playletIds = $list->pluck('playlet_id')->unique();
        $playletData = DramaSeries::query()
            ->select('id', 'name')
            ->whereIn('id', $playletIds)
            ->where('sys_group_id', $params['sys_group_id'])
            ->get();

        foreach ($list as $item) {
            # 短剧信息处理
            $playletInfo = $playletData->where('id', $item->playlet_id)->first();
            $item->playlet_name = $playletInfo->name ?? '-';
            if(isset($params['playlet_id']) && $params['playlet_id']) {
                unset($item->expense_date_end);
            }
            # 消耗金额单位处理
            $item->paid = $item->paid / 100;
            # 企微关注成本
            $item->follow_cost = round($item->follow_cost / 100, 2);
            # 首日roi
            $item->first_roi = round($item->first_roi * 100, 2) . '%';
            # 用户累计充值金额
            $item->pay_money_total = $item->pay_money_total / 100;
            # 首日下单成本
            $item->first_charge_user_cost = round($item->first_charge_user_cost / 100, 2);
            # 首日新增用户充值金额
            $item->pay_money = $item->pay_money / 100;
            # 当天新增用户arpu
            $item->day_paid_user_arpu = round($item->day_paid_user_arpu / 100, 2);
            # 总回本率
            $item->cost_cover_rate = round($item->cost_cover_rate * 100, 2) . '%';
            # 企微关注人数
            $item->scan_follow_count = intval($item->scan_follow_count);

            # 查询150天数据
            $dayInfo = [];
            $dayData = AdqPlayletTrendData::query()
                ->where('enable', 1)
                ->where(function($query) use ($params) {
                    if(isset($params['plat_order_type']) && is_numeric($params['plat_order_type']))
                        $query->where('plat_order_type', $params['plat_order_type']);
                })
                ->where('playlet_id', $item->playlet_id);

                if(isset($params['playlet_id']) && $params['playlet_id']) {
                    $dayData = $dayData->where('expense_date', '=', $item->expense_date)
                        ->where('ref_date', '<=', date('Y-m-d', strtotime($item->expense_date.' +149 day')));
                } else {
                    $dayData = $dayData->where('expense_date', '>=', $item->expense_date)
                        ->where('expense_date', '<=', $item->expense_date_end)
                        ->where('ref_date', '<=', date('Y-m-d', strtotime($item->expense_date_end.' +149 day')));
                }

            $dayData = $dayData->selectRaw("sum(pay_user) as pay_user,sum(pay_money) as pay_money, sum(pay_money_total) as pay_money_total, ref_date, expense_date, days_type")
                ->groupBy(['days_type'])
                ->get();

            $paidDataMid = [];
            $payMoneyTotal = 0;
            if(!empty($dayData)){
                foreach($dayData as $val){
                    $nd = (strtotime($val['ref_date']) - strtotime($val['expense_date'])) / 86400;
                    $payMoneyTotal+=$val['pay_money'];
                    if(isset($paidDataMid[$nd])) {
                        $paidDataMid[$nd]['pay_user'] += $val['pay_user'];
                        $paidDataMid[$nd]['pay_money'] += $val['pay_money'];
                        $paidDataMid[$nd]['pay_money_total'] += $payMoneyTotal;
                    } else {
                        $paidDataMid[$nd]['pay_user'] = $val['pay_user'];
                        $paidDataMid[$nd]['pay_money'] = $val['pay_money'];
                        $paidDataMid[$nd]['pay_money_total'] = $payMoneyTotal;
                    }
                }

                foreach ($paidDataMid as $i=>$v) {
                    $new_roi = $item->paid ? round($v['pay_money'] / 100 / $item->paid, 4) * 100 . '%' : '0%';
                    $total_roi = $item->paid ? round($v['pay_money_total'] / 100 / $item->paid, 4) * 100 . '%' : '0%';
                    $income_times = $item->pay_money ? round($v['pay_money_total'] / 100 / $item->pay_money, 2) : '0%';

                    $dayInfo[$i] = [
                        'days' => 'day' . ($i+1),
                        'pay_money' => round($v['pay_money']/100, 2),
                        'new_roi' => $new_roi,
                        'total_roi' => $total_roi,
                        'income_times' => $income_times,
                        'charge_user' => $v['pay_user']
                    ];
                }
            }

            $item->day_info = $dayInfo;
        }

        return [$count, $list, $header];
    }

    public static function adqSummaryOfShortDramaDataTrends($params)
    {
        # 表头处理
        $header = config('shortPlayDataTrendHeader.summary');

        # 数据处理
        if(isset($params['playlet_id']) && $params['playlet_id']) {
            $data = AdqPlayletTrendData::shortPlayDataTrendCollect($params, [$params['playlet_id']]);
            $playletIdList = [$params['playlet_id']];
        } else {
            # 获取企业下的adq账号
            $playletIdList = DramaSeries::query()
                ->select('id')
                ->where('enable', 1)
                ->where('sys_group_id', $params['sys_group_id'])
                ->distinct()
                ->pluck('id');

            $data = AdqPlayletTrendData::shortPlayDataTrendCollect($params, $playletIdList);
        }

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

        # 获取累计回收金额
        $recycleData = AdqPlayletTrendData::getShortPlayDataTrendCumulativeMoney($params, $playletIdList);

        # 获取首日累计回收金额
        $firstDayRecycleData = AdqPlayletTrendData::getShortPlayDataTrendFirstDayCumulativeMoney($params, $playletIdList);

        # 3天累计回收金额
        $threeDaysInfo = $recycleData->where('days_type', 3)->first();
        $threeDaysMoney = $threeDaysInfo->pay_money_total ?? 0;
        $data['three_days_rate'] = $firstDayRecycleData['three_days_pay_money'] ? round($threeDaysMoney / $firstDayRecycleData['three_days_pay_money'], 2) : 0;
        # 获取7天累计回收金额
        $sevenDaysInfo = $recycleData->where('days_type', 7)->first();
        $sevenDaysMoney = $sevenDaysInfo->pay_money_total ?? 0;
        $data['seven_days_rate'] = $firstDayRecycleData['seven_days_pay_money'] ? round($sevenDaysMoney / $firstDayRecycleData['seven_days_pay_money'], 2) : 0;
        # 获取15天累计回收金额
        $fifteenDaysInfo = $recycleData->where('days_type', 15)->first();
        $fifteenDaysMoney = $fifteenDaysInfo->pay_money_total ?? 0;
        $data['fifteen_days_rate'] = $firstDayRecycleData['fifteen_days_pay_money'] ? round($fifteenDaysMoney / $firstDayRecycleData['fifteen_days_pay_money'], 2) : 0;
        # 获取30天累计回收金额
        $thirtyDaysInfo = $recycleData->where('days_type', 30)->first();
        $thirtyDaysMoney = $thirtyDaysInfo->pay_money_total ?? 0;
        $data['thirty_days_rate'] = $firstDayRecycleData['thirty_days_pay_money'] ? round($thirtyDaysMoney / $firstDayRecycleData['thirty_days_pay_money'], 2) : 0;
        # 消耗金额单位处理
        $data['paid'] = $data['paid'] / 100;
        # 企微关注成本
        $data['follow_cost'] = round($data['follow_cost'] / 100, 1);
        # 首日roi
        $data['first_roi'] = round($data['first_roi'] * 100, 2) . '%';
        # 总回本率
        $data['cost_cover_rate'] = round($data['cost_cover_rate'] * 100, 2) . '%';
        # 首日新增用户充值金额
        $data['pay_money'] = $data['pay_money'] / 100;
        # 用户累计充值金额
        $data['pay_money_total'] = $data['pay_money_total'] / 100;
        # 首日下单成本
        $data['first_charge_user_cost'] = round($data['first_charge_user_cost'] / 100, 2);
        # 累计下单成本
        $data['charge_user_cost'] = round($data['charge_user_cost'] / 100, 2);
        # 首日付费用户arpu
        $data['day_paid_user_arpu'] = round($data['day_paid_user_arpu'] / 100, 2);
        # 企微关注人数
        $data['scan_follow_count'] = intval($data['scan_follow_count']);
        # 短剧
        $data['playlet_name'] = '汇总';

        return [$data, $header];
    }

    public static function adqShortPlayDataTrendCurve($params)
    {
        if(isset($params['playlet_id']) && $params['playlet_id']) {
            $playletIdList = [$params['playlet_id']];
        } else {
            # 获取企业下的adq账号
            $playletIdList = DramaSeries::query()
                ->select('id')
                ->where('enable', 1)
                ->where('sys_group_id', $params['sys_group_id'])
                ->distinct()
                ->pluck('id');

            if(empty($playletIdList)) return [];
        }

        # Adq账号数据汇总曲线
        $data = AdqPlayletTrendData::getShortPlayDataTrendCurve($params, $playletIdList);

        # 获取累计回收金额
        $recycleData = AdqPlayletTrendData::getShortPlayDataTrendCumulativeMoney($params, $playletIdList, true);

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

        # 数据处理
        foreach($data as $item) {
            # 消耗金额
            $item->paid = $item->paid / 100;
            # 首日roi
            $item->first_roi = round($item->first_roi * 100, 2);
            # 3天累计回收金额
            $threeDaysInfo = $recycleData->where('days_type', 3)->where('expense_date', $item->expense_date)->first();
            $threeDaysMoney = $threeDaysInfo->pay_money_total ?? 0;
            $item->three_days_rate = $item->pay_money ? round($threeDaysMoney * 100 / $item->pay_money, 2) : 0;
            # 获取7天累计回收金额
            $sevenDaysInfo = $recycleData->where('days_type', 7)->where('expense_date', $item->expense_date)->first();
            $sevenDaysMoney = $sevenDaysInfo->pay_money_total ?? 0;
            $item->seven_days_rate = $item->pay_money ? round($sevenDaysMoney * 100 / $item->pay_money, 2) : 0;
            # 获取15天累计回收金额
            $fifteenDaysInfo = $recycleData->where('days_type', 15)->where('expense_date', $item->expense_date)->first();
            $fifteenDaysMoney = $fifteenDaysInfo->pay_money_total ?? 0;
            $item->fifteen_days_rate = $item->pay_money ? round($fifteenDaysMoney * 100 / $item->pay_money, 2) : 0;
            # 获取30天累计回收金额
            $thirtyDaysInfo = $recycleData->where('days_type', 30)->where('expense_date', $item->expense_date)->first();
            $thirtyDaysMoney = $thirtyDaysInfo->pay_money_total ?? 0;
            $item->thirty_days_rate = $item->pay_money ? round($thirtyDaysMoney * 100 / $item->pay_money, 2) : 0;

            # 首日新增用户充值金额
            $item->pay_money = $item->pay_money / 100;

            $item->day_paid_user_arpu = empty($item->day_paid_user_arpu) ? 0 : round($item->day_paid_user_arpu/100, 2);
            $item->first_charge_user_cost = empty($item->first_charge_user_cost) ? 0 : round($item->first_charge_user_cost/100, 2);
        }

        return $data;
    }

    public static function mpShortPlayDataTrendList($params, $page, $pageSize, $sortField, $sortType)
    {
        # 表头处理
        $commonHeader = config('shortPlayDataTrendHeader.basic');
        # 获取day1至day150表头
        $daysHeader = [];
        for($i = 0;$i < MpPlayletTrendData::PLAYLET_TREND_DATA_RECORD_DAYS;$i++) {
            $title = [
                'name' => 'DAY'.($i+1),
                'column' => 'day'.($i+1)
            ];
            $daysHeader[] = $title;
        }
        $header = array_merge($commonHeader, $daysHeader);

        # 查询数据
        if(isset($params['playlet_id']) && $params['playlet_id']) {
            list($list, $count) = MpPlayletTrendData::getShortPlayDataTrendTrend($params, $sortField, $sortType, $page, $pageSize);
        } else {
            # 获取企业下的adq账号
            $playletIdList = DramaSeries::query()
                ->select('id')
                ->where('enable', 1)
                ->where('sys_group_id', $params['sys_group_id'])
                ->distinct()
                ->pluck('id');

            if(empty($playletIdList)) return [0, [], $header];
            list($list, $count) = MpPlayletTrendData::getShortPlayDataTrendSummaryData(
                $params, $playletIdList, $sortField, $sortType, $page, $pageSize
            );
        }

        if(empty($list)) return [0, [], $header];

        # 获取短剧信息
        $playletIds = $list->pluck('playlet_id')->unique();
        $playletData = DramaSeries::query()
            ->select('id', 'name')
            ->whereIn('id', $playletIds)
            ->where('sys_group_id', $params['sys_group_id'])
            ->get();

        foreach ($list as $item) {
            # 短剧信息处理
            $playletInfo = $playletData->where('id', $item->playlet_id)->first();
            $item->playlet_name = $playletInfo->name ?? '-';
            if(isset($params['playlet_id']) && $params['playlet_id']) {
                unset($item->expense_date_end);
            }
            # 消耗金额单位处理
            $item->paid = $item->paid / 100;
            # 企微关注成本
            $item->follow_cost = round($item->follow_cost / 100, 1);
            # 首日roi
            $item->first_roi = round($item->first_roi * 100, 2) . '%';
            # 用户累计充值金额
            $item->pay_money_total = $item->pay_money_total / 100;
            # 首日下单成本
            $item->first_charge_user_cost = round($item->first_charge_user_cost / 100, 2);
            # 首日新增用户充值金额
            $item->pay_money = $item->pay_money / 100;
            # 当天新增用户arpu
            $item->day_paid_user_arpu = round($item->day_paid_user_arpu / 100, 2);
            # 总回本率
            $item->cost_cover_rate = round($item->cost_cover_rate * 100, 2) . '%';
            # 企微关注人数
            $item->scan_follow_count = intval($item->scan_follow_count);

            # 查询150天数据
            $dayInfo = [];
            $dayData = MpPlayletTrendData::query()
                ->where('enable', 1)
                ->where(function($query) use ($params) {
                    if(isset($params['plat_order_type']) && is_numeric($params['plat_order_type']))
                        $query->where('plat_order_type', $params['plat_order_type']);
                })
                ->where('playlet_id', $item->playlet_id);
                if(isset($params['playlet_id']) && $params['playlet_id']) {
                    $dayData = $dayData->where('expense_date', '=', $item->expense_date)
                        ->where('ref_date', '<=', date('Y-m-d', strtotime($item->expense_date.' +149 day')));
                } else {
                    $dayData = $dayData->where('expense_date', '>=', $item->expense_date)
                        ->where('expense_date', '<=', $item->expense_date_end)
                        ->where('ref_date', '<=', date('Y-m-d', strtotime($item->expense_date_end.' +149 day')));
                }
            $dayData = $dayData->selectRaw("sum(pay_user) as pay_user,sum(pay_money) as pay_money, sum(pay_money_total) as pay_money_total, ref_date, expense_date, days_type")
                ->groupBy(['days_type'])
                ->get();

            $paidDataMid = [];
            $payMoneyTotal = 0;
            if(!empty($dayData)){
                foreach($dayData as $val){
                    $nd = (strtotime($val['ref_date']) - strtotime($val['expense_date'])) / 86400;
                    $payMoneyTotal += $val['pay_money'];
                    if(isset($paidDataMid[$nd])) {
                        $paidDataMid[$nd]['pay_user'] += $val['pay_user'];
                        $paidDataMid[$nd]['pay_money'] += $val['pay_money'];
                        $paidDataMid[$nd]['pay_money_total'] += $payMoneyTotal;
//                        $paidDataMid[$nd]['pay_money_total'] += $val['pay_money_total'];
                    } else {
                        $paidDataMid[$nd]['pay_user'] = $val['pay_user'];
                        $paidDataMid[$nd]['pay_money'] = $val['pay_money'];
//                        $paidDataMid[$nd]['pay_money_total'] = $val['pay_money_total'];
                        $paidDataMid[$nd]['pay_money_total'] = $payMoneyTotal;
                    }
                }

                foreach ($paidDataMid as $i=>$v) {
                    $new_roi = $item->paid ? round($v['pay_money'] / 100 / $item->paid, 4) * 100 . '%' : '0%';
                    $total_roi = $item->paid ? round($v['pay_money_total'] / 100 / $item->paid, 4) * 100 . '%' : '0%';
                    $income_times = $item->pay_money ? round($v['pay_money_total'] / 100 / $item->pay_money, 2) : '0%';

                    $dayInfo[$i] = [
                        'days' => 'day' . ($i+1),
                        'pay_money' => round($v['pay_money']/100, 2),
                        'new_roi' => $new_roi,
                        'total_roi' => $total_roi,
                        'income_times' => $income_times,
                        'charge_user' => $v['pay_user']
                    ];
                }
            }

            $item->day_info = $dayInfo;
        }

        return [$count, $list, $header];
    }

    public static function mpSummaryOfShortDramaDataTrends($params)
    {
        # 表头处理
        $header = config('shortPlayDataTrendHeader.summary');

        # 数据处理
        if(isset($params['playlet_id']) && $params['playlet_id']) {
            $data = MpPlayletTrendData::shortPlayDataTrendCollect($params, [$params['playlet_id']]);
            $playletIdList = [$params['playlet_id']];
        } else {
            # 获取企业下的adq账号
            $playletIdList = DramaSeries::query()
                ->select('id')
                ->where('enable', 1)
                ->where('sys_group_id', $params['sys_group_id'])
                ->distinct()
                ->pluck('id');

            $data = MpPlayletTrendData::shortPlayDataTrendCollect($params, $playletIdList);
        }

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

        # 获取累计回收金额
        $recycleData = MpPlayletTrendData::getShortPlayDataTrendCumulativeMoney($params, $playletIdList);

        # 获取首日累计回收金额
        $firstDayRecycleData = MpPlayletTrendData::getShortPlayDataTrendFirstDayCumulativeMoney($params, $playletIdList);

//        # 3天累计回收金额
//        $threeDaysInfo = $recycleData->where('days_type', 3)->first();
//        $threeDaysMoney = $threeDaysInfo->pay_money_total ?? 0;
//        $data->three_days_rate = $data->pay_money ? round($threeDaysMoney / $data->pay_money, 4) * 100 . "%" : "0%";
//        # 获取7天累计回收金额
//        $sevenDaysInfo = $recycleData->where('days_type', 7)->first();
//        $sevenDaysMoney = $sevenDaysInfo->pay_money_total ?? 0;
//        $data->seven_days_rate = $data->pay_money ? round($sevenDaysMoney / $data->pay_money, 4) * 100 . "%" : "0%";
//        # 获取15天累计回收金额
//        $fifteenDaysInfo = $recycleData->where('days_type', 15)->first();
//        $fifteenDaysMoney = $fifteenDaysInfo->pay_money_total ?? 0;
//        $data->fifteen_days_rate = $data->pay_money ? round($fifteenDaysMoney / $data->pay_money, 4) * 100 . "%" : "0%";
//        # 获取30天累计回收金额
//        $thirtyDaysInfo = $recycleData->where('days_type', 30)->first();
//        $thirtyDaysMoney = $thirtyDaysInfo->pay_money_total ?? 0;
//        $data->thirty_days_rate = $data->pay_money ? round($thirtyDaysMoney / $data->pay_money, 4) * 100 . "%" : "0%";
//        # 消耗金额单位处理
//        $data->paid = $data->paid / 100;
//        # 企微关注成本
//        $data->follow_cost = round($data->follow_cost / 100, 1);
//        # 首日roi
//        $data->first_roi = $data->first_roi * 100 . '%';
//        # 总回本率
//        $data->cost_cover_rate = $data->cost_cover_rate * 100 . '%';
//        # 首日新增用户充值金额
//        $data->pay_money = $data->pay_money / 100;
//        # 用户累计充值金额
//        $data->pay_money_total = $data->pay_money_total / 100;
//        # 首日下单成本
//        $data->first_charge_user_cost = round($data->first_charge_user_cost / 100, 2);
//        # 累计下单成本
//        $data->charge_user_cost = round($data->charge_user_cost / 100, 2);
//        # 首日付费用户arpu
//        $data->day_paid_user_arpu = round($data->day_paid_user_arpu / 100, 2);
//        # 企微关注人数
//        $data->scan_follow_count = intval($data->scan_follow_count);
//        # 短剧
//        $data->playlet_name = '汇总';

        # 3天累计回收金额
        $threeDaysInfo = $recycleData->where('days_type', 3)->first();
        $threeDaysMoney = $threeDaysInfo->pay_money_total ?? 0;
        $data['three_days_rate'] = $firstDayRecycleData['three_days_pay_money'] ? round($threeDaysMoney / $firstDayRecycleData['three_days_pay_money'], 2)  : 0;
        # 获取7天累计回收金额
        $sevenDaysInfo = $recycleData->where('days_type', 7)->first();
        $sevenDaysMoney = $sevenDaysInfo->pay_money_total ?? 0;
        $data['seven_days_rate'] = $firstDayRecycleData['seven_days_pay_money'] ? round($sevenDaysMoney / $firstDayRecycleData['seven_days_pay_money'], 2) : 0;
        # 获取15天累计回收金额
        $fifteenDaysInfo = $recycleData->where('days_type', 15)->first();
        $fifteenDaysMoney = $fifteenDaysInfo->pay_money_total ?? 0;
        $data['fifteen_days_rate'] = $firstDayRecycleData['fifteen_days_pay_money'] ? round($fifteenDaysMoney / $firstDayRecycleData['fifteen_days_pay_money'], 2) : 0;
        # 获取30天累计回收金额
        $thirtyDaysInfo = $recycleData->where('days_type', 30)->first();
        $thirtyDaysMoney = $thirtyDaysInfo->pay_money_total ?? 0;
        $data['thirty_days_rate'] = $firstDayRecycleData['thirty_days_pay_money'] ? round($thirtyDaysMoney / $firstDayRecycleData['thirty_days_pay_money'], 2) : 0;
        # 消耗金额单位处理
        $data['paid'] = $data['paid'] / 100;
        # 企微关注成本
        $data['follow_cost'] = round($data['follow_cost'] / 100, 1);
        # 首日roi
        $data['first_roi'] = round($data['first_roi'] * 100, 2) . '%';
        # 总回本率
        $data['cost_cover_rate'] = round($data['cost_cover_rate'] * 100, 2) . '%';
        # 首日新增用户充值金额
        $data['pay_money'] = $data['pay_money'] / 100;
        # 用户累计充值金额
        $data['pay_money_total'] = $data['pay_money_total'] / 100;
        # 首日下单成本
        $data['first_charge_user_cost'] = round($data['first_charge_user_cost'] / 100, 2);
        # 累计下单成本
        $data['charge_user_cost'] = round($data['charge_user_cost'] / 100, 2);
        # 首日付费用户arpu
        $data['day_paid_user_arpu'] = round($data['day_paid_user_arpu'] / 100, 2);
        # 企微关注人数
        $data['scan_follow_count'] = intval($data['scan_follow_count']);
        # 短剧
        $data['playlet_name'] = '汇总';

        return [$data, $header];
    }

    public static function mpShortPlayDataTrendCurve($params)
    {
        if(isset($params['playlet_id']) && $params['playlet_id']) {
            $playletIdList = [$params['playlet_id']];
        } else {
            # 获取企业下的adq账号
            $playletIdList = DramaSeries::query()
                ->select('id')
                ->where('enable', 1)
                ->where('sys_group_id', $params['sys_group_id'])
                ->distinct()
                ->pluck('id');

            if(empty($playletIdList)) return [];
        }

        # Adq账号数据汇总曲线
        $data = MpPlayletTrendData::getShortPlayDataTrendCurve($params, $playletIdList);

        # 获取累计回收金额
        $recycleData = MpPlayletTrendData::getShortPlayDataTrendCumulativeMoney($params, $playletIdList, true);

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

        # 数据处理
        foreach($data as $item) {
            # 消耗金额
            $item->paid = $item->paid / 100;
            # 首日roi
            $item->first_roi = round($item->first_roi * 100, 2);
            # 3天累计回收金额
            $threeDaysInfo = $recycleData->where('days_type', 3)->where('expense_date', $item->expense_date)->first();
            $threeDaysMoney = $threeDaysInfo->pay_money_total ?? 0;
            $item->three_days_rate = $item->pay_money ? round($threeDaysMoney * 100 / $item->pay_money, 2) : 0;
            # 获取7天累计回收金额
            $sevenDaysInfo = $recycleData->where('days_type', 7)->where('expense_date', $item->expense_date)->first();
            $sevenDaysMoney = $sevenDaysInfo->pay_money_total ?? 0;
            $item->seven_days_rate = $item->pay_money ? round($sevenDaysMoney * 100 / $item->pay_money, 2) : 0;
            # 获取15天累计回收金额
            $fifteenDaysInfo = $recycleData->where('days_type', 15)->where('expense_date', $item->expense_date)->first();
            $fifteenDaysMoney = $fifteenDaysInfo->pay_money_total ?? 0;
            $item->fifteen_days_rate = $item->pay_money ? round($fifteenDaysMoney * 100 / $item->pay_money, 2) : 0;
            # 获取30天累计回收金额
            $thirtyDaysInfo = $recycleData->where('days_type', 30)->where('expense_date', $item->expense_date)->first();
            $thirtyDaysMoney = $thirtyDaysInfo->pay_money_total ?? 0;
            $item->thirty_days_rate = $item->pay_money ? round($thirtyDaysMoney * 100 / $item->pay_money, 2) : 0;

            # 首日新增用户充值金额
            $item->pay_money = $item->pay_money / 100;

            $item->day_paid_user_arpu = empty($item->day_paid_user_arpu) ? 0 : round($item->day_paid_user_arpu/100, 2);
            $item->first_charge_user_cost = empty($item->first_charge_user_cost) ? 0 : round($item->first_charge_user_cost/100, 2);

        }

        return $data;
    }

    public static function shortPlayDataTrendList($params, $page, $pageSize, $sortField, $sortType)
    {
        # 表头处理
        $commonHeader = config('shortPlayDataTrendHeader.basic');
        # 获取day1至day150表头
        $daysHeader = [];
        for($i = 0;$i < PlayletDataTrend::PLAYLET_TREND_DATA_RECORD_DAYS;$i++) {
            $title = [
                'name' => 'DAY'.($i+1),
                'column' => 'day'.($i+1)
            ];
            $daysHeader[] = $title;
        }
        $header = array_merge($commonHeader, $daysHeader);

        # 查询数据
        if(isset($params['playlet_id']) && $params['playlet_id']) {
            list($list, $count) = PlayletDataTrend::getShortPlayDataTrendTrend($params, $sortField, $sortType, $page, $pageSize);
        } else {
            # 获取企业下的adq账号
            $playletIdList = DramaSeries::query()
                ->select('id')
                ->where('enable', 1)
                ->where('sys_group_id', $params['sys_group_id'])
                ->distinct()
                ->pluck('id');

            if(empty($playletIdList)) return [0, [], $header];
            list($list, $count) = PlayletDataTrend::getShortPlayDataTrendSummaryData(
                $params, $playletIdList, $sortField, $sortType, $page, $pageSize
            );
        }

        if(empty($list)) return [0, [], $header];

        # 获取短剧信息
        $playletIds = $list->pluck('playlet_id')->unique();
        $playletData = DramaSeries::query()
            ->select('id', 'name')
            ->whereIn('id', $playletIds)
            ->where('sys_group_id', $params['sys_group_id'])
            ->get();

        foreach ($list as $item) {
            # 短剧信息处理
            $playletInfo = $playletData->where('id', $item->playlet_id)->first();
            $item->playlet_name = $playletInfo->name ?? '-';
            if(isset($params['playlet_id']) && $params['playlet_id']) {
                unset($item->expense_date_end);
            }
            # 消耗金额单位处理
            $item->paid = $item->paid / 100;
            # 企微关注成本
            $item->follow_cost = round($item->follow_cost / 100, 1);
            # 首日roi
            $item->first_roi = round($item->first_roi * 100, 2) . '%';
            # 用户累计充值金额
            $item->pay_money_total = $item->pay_money_total / 100;
            # 首日下单成本
            $item->first_charge_user_cost = round($item->first_charge_user_cost / 100, 2);
            # 首日新增用户充值金额
            $item->pay_money = $item->pay_money / 100;
            # 当天新增用户arpu
            $item->day_paid_user_arpu = round($item->day_paid_user_arpu / 100, 2);
            # 总回本率
            $item->cost_cover_rate = round($item->cost_cover_rate * 100, 2) . '%';
            # 企微关注人数
            $item->scan_follow_count = intval($item->scan_follow_count);

            # 查询150天数据
            $dayInfo = [];
            $dayData = PlayletDataTrend::query()
                ->where('enable', 1)
                ->where(function($query) use ($params) {
                    if(isset($params['plat_order_type']) && is_numeric($params['plat_order_type']))
                        $query->where('plat_order_type', $params['plat_order_type']);
                })
                ->where('playlet_id', $item->playlet_id);

            if(isset($params['playlet_id']) && $params['playlet_id']) {
                $dayData = $dayData->where('expense_date', '=', $item->expense_date)
                    ->where('ref_date', '<=', date('Y-m-d', strtotime($item->expense_date.' +149 day')));
            } else {
                $dayData = $dayData->where('expense_date', '>=', $item->expense_date)
                    ->where('expense_date', '<=', $item->expense_date_end)
                    ->where('ref_date', '<=', date('Y-m-d', strtotime($item->expense_date_end.' +149 day')));
            }

            $dayData = $dayData->selectRaw("sum(pay_user) as pay_user,sum(pay_money) as pay_money, sum(pay_money_total) as pay_money_total, ref_date, expense_date, days_type")
                ->groupBy(['days_type'])
                ->get();

            $paidDataMid = [];
            $payMoneyTotal = 0;
            if(!empty($dayData)){
                foreach($dayData as $val){
                    $nd = (strtotime($val['ref_date']) - strtotime($val['expense_date'])) / 86400;
                    $payMoneyTotal+=$val['pay_money'];
                    if(isset($paidDataMid[$nd])) {
                        $paidDataMid[$nd]['pay_user'] += $val['pay_user'];
                        $paidDataMid[$nd]['pay_money'] += $val['pay_money'];
                        $paidDataMid[$nd]['pay_money_total'] += $payMoneyTotal;
                    } else {
                        $paidDataMid[$nd]['pay_user'] = $val['pay_user'];
                        $paidDataMid[$nd]['pay_money'] = $val['pay_money'];
                        $paidDataMid[$nd]['pay_money_total'] = $payMoneyTotal;
                    }
                }

                foreach ($paidDataMid as $i=>$v) {
                    $new_roi = $item->paid ? round($v['pay_money'] / 100 / $item->paid, 4) * 100 . '%' : '0%';
                    $total_roi = $item->paid ? round($v['pay_money_total'] / 100 / $item->paid, 4) * 100 . '%' : '0%';
                    $income_times = $item->pay_money ? round($v['pay_money_total'] / 100 / $item->pay_money, 2) : '0%';

                    $dayInfo[$i] = [
                        'days' => 'day' . ($i+1),
                        'pay_money' => round($v['pay_money']/100, 2),
                        'new_roi' => $new_roi,
                        'total_roi' => $total_roi,
                        'income_times' => $income_times,
                        'charge_user' => $v['pay_user']
                    ];
                }
            }

            $item->day_info = $dayInfo;
        }

        return [$count, $list, $header];
    }

    public static function summaryOfShortDramaDataTrends($params)
    {
        # 表头处理
        $header = config('shortPlayDataTrendHeader.summary');

        # 数据处理
        if(isset($params['playlet_id']) && $params['playlet_id']) {
            $data = PlayletDataTrend::shortPlayDataTrendCollect($params, [$params['playlet_id']]);
            $playletIdList = [$params['playlet_id']];
        } else {
            # 获取企业下的adq账号
            $playletIdList = DramaSeries::query()
                ->select('id')
                ->where('enable', 1)
                ->where('sys_group_id', $params['sys_group_id'])
                ->distinct()
                ->pluck('id');

            $data = PlayletDataTrend::shortPlayDataTrendCollect($params, $playletIdList);
        }

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

        # 获取累计回收金额
        $recycleData = PlayletDataTrend::getShortPlayDataTrendCumulativeMoney($params, $playletIdList);

        # 获取首日累计回收金额
        $firstDayRecycleData = PlayletDataTrend::getShortPlayDataTrendFirstDayCumulativeMoney($params, $playletIdList);

        # 3天累计回收金额
        $threeDaysInfo = $recycleData->where('days_type', 3)->first();
        $threeDaysMoney = $threeDaysInfo->pay_money_total ?? 0;
        $data['three_days_rate'] = $firstDayRecycleData['three_days_pay_money'] ? round($threeDaysMoney / $firstDayRecycleData['three_days_pay_money'], 2) : 0;
        # 获取7天累计回收金额
        $sevenDaysInfo = $recycleData->where('days_type', 7)->first();
        $sevenDaysMoney = $sevenDaysInfo->pay_money_total ?? 0;
        $data['seven_days_rate'] = $firstDayRecycleData['seven_days_pay_money'] ? round($sevenDaysMoney / $firstDayRecycleData['seven_days_pay_money'], 2) : 0;
        # 获取15天累计回收金额
        $fifteenDaysInfo = $recycleData->where('days_type', 15)->first();
        $fifteenDaysMoney = $fifteenDaysInfo->pay_money_total ?? 0;
        $data['fifteen_days_rate'] = $firstDayRecycleData['fifteen_days_pay_money'] ? round($fifteenDaysMoney / $firstDayRecycleData['fifteen_days_pay_money'], 2) : 0;
        # 获取30天累计回收金额
        $thirtyDaysInfo = $recycleData->where('days_type', 30)->first();
        $thirtyDaysMoney = $thirtyDaysInfo->pay_money_total ?? 0;
        $data['thirty_days_rate'] = $firstDayRecycleData['thirty_days_pay_money'] ? round($thirtyDaysMoney / $firstDayRecycleData['thirty_days_pay_money'], 2) : 0;
        # 消耗金额单位处理
        $data['paid'] = $data['paid'] / 100;
        # 企微关注成本
        $data['follow_cost'] = round($data['follow_cost'] / 100, 1);
        # 首日roi
        $data['first_roi'] = round($data['first_roi'] * 100, 2) . '%';
        # 总回本率
        $data['cost_cover_rate'] = round($data['cost_cover_rate'] * 100, 2) . '%';
        # 首日新增用户充值金额
        $data['pay_money'] = $data['pay_money'] / 100;
        # 用户累计充值金额
        $data['pay_money_total'] = $data['pay_money_total'] / 100;
        # 首日下单成本
        $data['first_charge_user_cost'] = round($data['first_charge_user_cost'] / 100, 2);
        # 累计下单成本
        $data['charge_user_cost'] = round($data['charge_user_cost'] / 100, 2);
        # 首日付费用户arpu
        $data['day_paid_user_arpu'] = round($data['day_paid_user_arpu'] / 100, 2);
        # 企微关注人数
        $data['scan_follow_count'] = intval($data['scan_follow_count']);
        # 短剧
        $data['playlet_name'] = '汇总';

        return [$data, $header];
    }

    public static function shortPlayDataTrendCurve($params)
    {
        if(isset($params['playlet_id']) && $params['playlet_id']) {
            $playletIdList = [$params['playlet_id']];
        } else {
            # 获取企业下的adq账号
            $playletIdList = DramaSeries::query()
                ->select('id')
                ->where('enable', 1)
                ->where('sys_group_id', $params['sys_group_id'])
                ->distinct()
                ->pluck('id');

            if(empty($playletIdList)) return [];
        }

        # Adq账号数据汇总曲线
        $data = PlayletDataTrend::getShortPlayDataTrendCurve($params, $playletIdList);

        # 获取累计回收金额
        $recycleData = PlayletDataTrend::getShortPlayDataTrendCumulativeMoney($params, $playletIdList, true);

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

        # 数据处理
        foreach($data as $item) {
            # 消耗金额
            $item->paid = $item->paid / 100;
            # 首日roi
            $item->first_roi = round($item->first_roi * 100, 2);
            # 3天累计回收金额
            $threeDaysInfo = $recycleData->where('days_type', 3)->where('expense_date', $item->expense_date)->first();
            $threeDaysMoney = $threeDaysInfo->pay_money_total ?? 0;
            $item->three_days_rate = $item->pay_money ? round($threeDaysMoney * 100 / $item->pay_money, 2) : 0;
            # 获取7天累计回收金额
            $sevenDaysInfo = $recycleData->where('days_type', 7)->where('expense_date', $item->expense_date)->first();
            $sevenDaysMoney = $sevenDaysInfo->pay_money_total ?? 0;
            $item->seven_days_rate = $item->pay_money ? round($sevenDaysMoney * 100 / $item->pay_money, 2) : 0;
            # 获取15天累计回收金额
            $fifteenDaysInfo = $recycleData->where('days_type', 15)->where('expense_date', $item->expense_date)->first();
            $fifteenDaysMoney = $fifteenDaysInfo->pay_money_total ?? 0;
            $item->fifteen_days_rate = $item->pay_money ? round($fifteenDaysMoney * 100 / $item->pay_money, 2) : 0;
            # 获取30天累计回收金额
            $thirtyDaysInfo = $recycleData->where('days_type', 30)->where('expense_date', $item->expense_date)->first();
            $thirtyDaysMoney = $thirtyDaysInfo->pay_money_total ?? 0;
            $item->thirty_days_rate = $item->pay_money ? round($thirtyDaysMoney * 100 / $item->pay_money, 2) : 0;

            # 首日新增用户充值金额
            $item->pay_money = $item->pay_money / 100;

            $item->day_paid_user_arpu = empty($item->day_paid_user_arpu) ? 0 : round($item->day_paid_user_arpu/100, 2);
            $item->first_charge_user_cost = empty($item->first_charge_user_cost) ? 0 : round($item->first_charge_user_cost/100, 2);
        }

        return $data;
    }

    public static function shortPlayDataTrendListByAccount($params, $page, $pageSize, $sortField, $sortType)
    {
        $sortField = self::getSortField($sortField);
        # 表头处理
        $commonHeader = config('shortPlayDataTrendHeader.basic');
        # 获取day1至day150表头
        $daysHeader = [];
        for($i = 0;$i < PlayletAccountTrendData::PLAYLET_TREND_DATA_RECORD_DAYS;$i++) {
            $title = [
                'name' => 'DAY'.($i+1),
                'column' => 'day'.($i+1)
            ];
            $daysHeader[] = $title;
        }
        $header = array_merge($commonHeader, $daysHeader);

        # 查询当前登录用户可见投放账号列表
        if(!empty($params['ad_type'])) {
            if(2 == $params['ad_type']) {
                $accountList = OfficialAccount::getAccountMpAppIdList($params['admin_id'], $params['sys_group_id']
                    , $params['is_system_admin']);
            } else {
                $accountList = PitcherService::adqAccountListForUser($params['admin_id'], $params['sys_group_id']
                    , $params['is_system_admin']);
            }
        } else {
            $accountList = PitcherService::adAccountList(null, $params['sys_group_id'], $params['admin_id']
                , $params['is_system_admin']);
            $accountList = array_column($accountList, 'account_id');
        }

        if(empty($accountList)){
            return [0, [], $header];
        }

        # 查询数据
        if(isset($params['playlet_id']) && $params['playlet_id']) {
            list($list, $count) = PlayletAccountDataTrend::getShortPlayDataTrend($params, $sortField, $sortType
                , $page, $pageSize, $accountList);
        } else {
            # 获取企业下的adq账号
            $playletIdList = DramaSeries::query()
                ->select('id')
                ->where('enable', 1)
                ->where('sys_group_id', $params['sys_group_id'])
                ->distinct()
                ->pluck('id')->toArray();

            if(empty($playletIdList)) return [0, [], $header];
            list($list, $count) = PlayletAccountDataTrend::getShortPlayDataTrendSummaryData($params, $playletIdList
                , $sortField, $sortType, $page, $pageSize, $accountList
            );
        }

        if(empty($list)) return [0, [], $header];

        # 获取短剧信息
        $playletIds = array_unique(array_column($list, 'playlet_id'));
        $playletData = DramaSeries::query()
            ->select('id', 'name')
            ->whereIn('id', $playletIds)
            ->where('sys_group_id', $params['sys_group_id'])
            ->get();

        foreach ($list as &$item) {
            # 短剧信息处理
            $playletInfo = $playletData->where('id', $item['playlet_id'])->first();
            $item['playlet_name'] = $playletInfo->name ?? '-';
            # 消耗金额单位处理
            $item['paid'] = $item['paid'] / 100;
            # 企微关注成本
            $item['follow_cost'] = round($item['follow_cost'] / 100, 2);
            # 首日roi
            $item['first_roi'] = round($item['first_roi'] * 100, 2) . '%';
            # 用户累计充值金额
            $item['pay_money_total'] = $item['pay_money_total'] / 100;
            # 首日下单成本
            $item['first_charge_user_cost'] = round($item['first_charge_user_cost'] / 100, 2);
            # 首日新增用户充值金额
            $item['pay_money'] = $item['pay_money'] / 100;
            # 当天新增用户arpu
            $item['day_paid_user_arpu'] = round($item['day_paid_user_arpu'] / 100, 2);
            # 总回本率
            $item['cost_cover_rate'] = round($item['cost_cover_rate'] * 100, 2) . '%';
            # 企微关注人数
            $item['scan_follow_count'] = intval($item['scan_follow_count']);
            # 首单充值金额
            $item['day_first_pay_money'] = $item['day_first_pay_money'] / 100;
            # 首单roi
            $item['first_pay_roi'] = round($item['first_pay_roi'] * 100) . '%';
            # 企微加粉人数
            $item['followers_number'] = intval($item['followers_number']);
            # 企微加粉成本
            $item['followers_cost'] = round($item['followers_cost'] / 100, 2);

            # 查询150天数据
            $dayInfo = [];
            if(isset($params['playlet_id']) && $params['playlet_id']) {
                $dayData = PlayletAccountDataTrend::getDataAfterExpense($params, $item['expense_date'], null,
                    $item['playlet_id'], $accountList);
                if(!empty($dayData)){
                    foreach($dayData as $val){
                        $nd = (strtotime($val['ref_date']) - strtotime($item['expense_date'])) / 86400;
                        $new_roi = $item['paid'] ? round($val['pay_money'] / 100 / $item['paid'], 4) * 100 . '%' : '0%';
                        $total_roi = $item['paid'] ? round($val['pay_money_total'] / 100 / $item['paid'], 4) * 100 . '%' : '0%';
                        $income_times = $item['pay_money'] ? round($val['pay_money_total'] / 100 / $item['pay_money'], 2) : 0;
                        $dayInfo[$nd] = [
                            'days' => 'day' . ($nd+1),
                            'pay_money' => round($val['pay_money']/100, 2),
                            'new_roi' => $new_roi,
                            'total_roi' => $total_roi,
                            'income_times' => $income_times,
                            'charge_user' => $val['pay_user']
                        ];
                    }
                }
            } else {
                $dayData = PlayletAccountDataTrend::getDataAfterExpense($params, $item['expense_date'], $item['expense_date_end'],
                    $item['playlet_id'], $accountList);
                $paidDataMid = [];
                $payMoneyTotal = 0;
                if(!empty($dayData)){
                    foreach($dayData as $val){
                        $nd = (strtotime($val['ref_date']) - strtotime($val['expense_date'])) / 86400;
                        $payMoneyTotal += $val['pay_money'];
                        if(isset($paidDataMid[$nd])) {
                            $paidDataMid[$nd]['active_fans'] += $val['active_fans'];
                            $paidDataMid[$nd]['pay_user'] += $val['pay_user'];
                            $paidDataMid[$nd]['pay_money'] += $val['pay_money'];
                            $paidDataMid[$nd]['pay_money_total'] += $payMoneyTotal;
                        } else {
                            $paidDataMid[$nd]['active_fans'] = $val['active_fans'];
                            $paidDataMid[$nd]['pay_user'] = $val['pay_user'];
                            $paidDataMid[$nd]['pay_money'] = $val['pay_money'];
                            $paidDataMid[$nd]['pay_money_total'] = $payMoneyTotal;
                        }
                    }

                    foreach ($paidDataMid as $i=>$v) {
                        $new_roi = $item['paid'] ? round($v['pay_money'] / 100 / $item['paid'], 4) * 100 . '%' : '0%';
                        $total_roi = $item['paid'] ? round($v['pay_money_total'] / 100 / $item['paid'], 4) * 100 . '%' : '0%';
                        $income_times = $item['pay_money'] ? round($v['pay_money_total'] / 100 / $item['pay_money'], 2) : 0;

                        $dayInfo[$i] = [
                            'days' => 'day' . ($i+1),
                            'pay_money' => round($v['pay_money']/100, 2),
                            'new_roi' => $new_roi,
                            'total_roi' => $total_roi,
                            'income_times' => $income_times,
                            'charge_user' => $v['pay_user']
                        ];
                    }
                }
            }
            $item['day_info'] = $dayInfo;
        }

        return [$count, $list, $header];
    }

    public static function summaryOfShortDramaDataTrendsByAccount($params)
    {
        # 表头处理
        $header = config('shortPlayDataTrendHeader.summary');

        # 查询当前登录用户可见投放账号列表
        if(!empty($params['ad_type'])) {
            if(2 == $params['ad_type']) {
                $accountList = OfficialAccount::getAccountMpAppIdList($params['admin_id'], $params['sys_group_id'], $params['is_system_admin']);
            } else {
                $accountList = PitcherService::adqAccountListForUser($params['admin_id'], $params['sys_group_id'], $params['is_system_admin']);
            }
        } else {
            $accountList = PitcherService::adAccountList(null, $params['sys_group_id'], $params['admin_id'], $params['is_system_admin']);
            $accountList = array_column($accountList, 'account_id');
        }

        if(empty($accountList)){
            return [[], $header];
        }

        # 数据处理
        if(isset($params['playlet_id']) && $params['playlet_id']) {
            $data = PlayletAccountDataTrend::shortPlayDataTrendCollect($params, [$params['playlet_id']], $accountList);
            $playletIdList = [$params['playlet_id']];
        } else {
            # 获取企业下的adq账号
            $playletIdList = DramaSeries::query()
                ->select('id')
                ->where('enable', 1)
                ->where('sys_group_id', $params['sys_group_id'])
                ->distinct()
                ->pluck('id');

            $data = PlayletAccountDataTrend::shortPlayDataTrendCollect($params, $playletIdList, $accountList);
        }

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

        # 获取累计回收金额
        $recycleData = collect(PlayletAccountDataTrend::getCumulativeMoney($params, $playletIdList, $accountList, [1, 3, 7, 15, 30]));

        # 3日消耗最早日期和最晚日期
        $threeDaysBegin = $recycleData->where('days_type', 3)->min('expense_date');
        $threeDaysEnd = $recycleData->where('days_type', 3)->max('expense_date');
        # 首日新增用户累计充值金额
        $threeDaysFirstPaid = $recycleData->where('expense_date', '>=', $threeDaysBegin)
            ->where('expense_date', '<=', $threeDaysEnd)->where('days_type', 1)
            ->sum('pay_money');
        # 3天累计回收金额
        $threeDaysMoney = $recycleData->where('days_type', 3)->sum('pay_money_total');
        $data['three_days_rate'] = $threeDaysFirstPaid ? round($threeDaysMoney / $threeDaysFirstPaid, 2) : "0";

        # 7日消耗最早日期和最晚日期
        $sevenDaysBegin = $recycleData->where('days_type', 7)->min('expense_date');
        $sevenDaysEnd = $recycleData->where('days_type', 7)->max('expense_date');
        # 首日新增用户累计充值金额
        $sevenDaysFirstPaid = $recycleData->where('expense_date', '>=', $sevenDaysBegin)
            ->where('expense_date', '<=', $sevenDaysEnd)->where('days_type', 1)
            ->sum('pay_money');

        # 7天累计回收金额
        $sevenDaysMoney = $recycleData->where('days_type', 7)->sum('pay_money_total');
        $data['seven_days_rate'] = $sevenDaysFirstPaid ? round($sevenDaysMoney / $sevenDaysFirstPaid, 2) : "0";

        # 15日消耗最早日期和最晚日期
        $fifteenDaysBegin = $recycleData->where('days_type', 15)->min('expense_date');
        $fifteenDaysEnd = $recycleData->where('days_type', 15)->max('expense_date');
        # 首日新增用户累计充值金额
        $fifteenDaysFirstPaid = $recycleData->where('expense_date', '>=', $fifteenDaysBegin)
            ->where('expense_date', '<=', $fifteenDaysEnd)->where('days_type', 1)
            ->sum('pay_money');

        # 15天累计回收金额
        $fifteenDaysMoney = $recycleData->where('days_type', 15)->sum('pay_money_total');
        $data['fifteen_days_rate'] = $fifteenDaysFirstPaid ? round($fifteenDaysMoney / $fifteenDaysFirstPaid, 2) : "0";

        # 30日消耗最早日期和最晚日期
        $thirtyDaysBegin = $recycleData->where('days_type', 30)->min('expense_date');
        $thirtyDaysEnd = $recycleData->where('days_type', 30)->max('expense_date');
        # 首日新增用户累计充值金额
        $thirtyDaysFirstPaid = $recycleData->where('expense_date', '>=', $thirtyDaysBegin)
            ->where('expense_date', '<=', $thirtyDaysEnd)->where('days_type', 1)
            ->sum('pay_money');

        # 30天累计回收金额
        $thirtyDaysMoney = $recycleData->where('days_type', 30)->sum('pay_money_total');
        $data['thirty_days_rate'] = $thirtyDaysFirstPaid ? round($thirtyDaysMoney / $thirtyDaysFirstPaid, 2) : "0";

//        # 3天累计回收金额
//        $threeDaysInfo = $recycleData->where('days_type', 3)->first();
//        $threeDaysMoney = $threeDaysInfo->pay_money_total ?? 0;
//        $data['three_days_rate'] = $firstDayRecycleData['three_days_pay_money'] ?
//            round($threeDaysMoney / $firstDayRecycleData['three_days_pay_money'], 2) : 0;
//        # 获取7天累计回收金额
//        $sevenDaysInfo = $recycleData->where('days_type', 7)->first();
//        $sevenDaysMoney = $sevenDaysInfo->pay_money_total ?? 0;
//        $data['seven_days_rate'] = $firstDayRecycleData['seven_days_pay_money'] ?
//            round($sevenDaysMoney / $firstDayRecycleData['seven_days_pay_money'], 2) : 0;
//        # 获取15天累计回收金额
//        $fifteenDaysInfo = $recycleData->where('days_type', 15)->first();
//        $fifteenDaysMoney = $fifteenDaysInfo->pay_money_total ?? 0;
//        $data['fifteen_days_rate'] = $firstDayRecycleData['fifteen_days_pay_money'] ?
//            round($fifteenDaysMoney / $firstDayRecycleData['fifteen_days_pay_money'], 2) : 0;
//        # 获取30天累计回收金额
//        $thirtyDaysInfo = $recycleData->where('days_type', 30)->first();
//        $thirtyDaysMoney = $thirtyDaysInfo->pay_money_total ?? 0;
//        $data['thirty_days_rate'] = $firstDayRecycleData['thirty_days_pay_money'] ?
//            round($thirtyDaysMoney / $firstDayRecycleData['thirty_days_pay_money'], 2) : 0;
        # 消耗金额单位处理
        $data['paid'] = $data['paid'] / 100;
        # 企微关注成本
        $data['follow_cost'] = round($data['follow_cost'] / 100, 1);
        # 首日roi
        $data['first_roi'] = round($data['first_roi'] * 100, 2) . '%';
        # 总回本率
        $data['cost_cover_rate'] = round($data['cost_cover_rate'] * 100, 2) . '%';
        # 首日新增用户充值金额
        $data['pay_money'] = $data['pay_money'] / 100;
        # 用户累计充值金额
        $data['pay_money_total'] = $data['pay_money_total'] / 100;
        # 首日下单成本
        $data['first_charge_user_cost'] = round($data['first_charge_user_cost'] / 100, 2);
        # 累计下单成本
//        $data['charge_user_cost'] = round($data['charge_user_cost'] / 100, 2);
        # 首日付费用户arpu
        $data['day_paid_user_arpu'] = round($data['day_paid_user_arpu'] / 100, 2);
        # 企微关注人数
        $data['scan_follow_count'] = intval($data['scan_follow_count']);
        # 短剧
        $data['playlet_name'] = '汇总';
        # 企微加粉人数
        $data['followers_number'] = intval($data['followers_number']);
        # 企微加粉成本
        $data['followers_cost'] = round($data['followers_cost'] / 100, 2);

        return [$data, $header];
    }

    public static function shortPlayDataTrendCurveByAccount($params)
    {
        # 查询当前登录用户可见投放账号列表
        if(!empty($params['ad_type'])) {
            if(2 == $params['ad_type']) {
                $accountList = OfficialAccount::getAccountMpAppIdList($params['admin_id'], $params['sys_group_id'], $params['is_system_admin']);
            } else {
                $accountList = PitcherService::adqAccountListForUser($params['admin_id'], $params['sys_group_id'], $params['is_system_admin']);
            }
        } else {
            $accountList = PitcherService::adAccountList(null, $params['sys_group_id'], $params['admin_id'], $params['is_system_admin']);
            $accountList = array_column($accountList, 'account_id');
        }

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

        if(isset($params['playlet_id']) && $params['playlet_id']) {
            $playletIdList = [$params['playlet_id']];
        } else {
            # 获取企业下的adq账号
            $playletIdList = DramaSeries::query()
                ->select('id')
                ->where('enable', 1)
                ->where('sys_group_id', $params['sys_group_id'])
                ->distinct()
                ->pluck('id');

            if(empty($playletIdList)) return [];
        }

        # Adq账号数据汇总曲线
        $data = PlayletAccountDataTrend::getAccountDataCurve($params, $playletIdList, $accountList);

        # 获取累计回收金额
        $recycleData = collect(PlayletAccountDataTrend::getCumulativeMoney($params, $playletIdList, $accountList, [3, 7, 15, 30]));

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

        # 数据处理
        foreach($data as &$item) {
            # 消耗金额
            $item['paid'] = $item['paid'] / 100;
            # 首日roi
            $item['first_roi'] = round($item['first_roi'] * 100, 2);
            # 3天累计回收金额
            $threeDaysInfo = $recycleData->where('days_type', 3)->where('expense_date', $item['expense_date'])->first();
            $threeDaysMoney = $threeDaysInfo->pay_money_total ?? 0;
            $item['three_days_rate'] = $item['pay_money'] ? round($threeDaysMoney * 100 / $item['pay_money'], 2) : 0;
            # 获取7天累计回收金额
            $sevenDaysInfo = $recycleData->where('days_type', 7)->where('expense_date', $item['expense_date'])->first();
            $sevenDaysMoney = $sevenDaysInfo->pay_money_total ?? 0;
            $item['seven_days_rate'] = $item['pay_money'] ? round($sevenDaysMoney * 100 / $item['pay_money'], 2) : 0;
            # 获取15天累计回收金额
            $fifteenDaysInfo = $recycleData->where('days_type', 15)->where('expense_date', $item['expense_date'])->first();
            $fifteenDaysMoney = $fifteenDaysInfo->pay_money_total ?? 0;
            $item['fifteen_days_rate'] = $item['pay_money'] ? round($fifteenDaysMoney * 100 / $item['pay_money'], 2) : 0;
            # 获取30天累计回收金额
            $thirtyDaysInfo = $recycleData->where('days_type', 30)->where('expense_date', $item['expense_date'])->first();
            $thirtyDaysMoney = $thirtyDaysInfo->pay_money_total ?? 0;
            $item['thirty_days_rate'] = $item['pay_money'] ? round($thirtyDaysMoney * 100 / $item['pay_money'], 2) : 0;

            # 首日新增用户充值金额
            $item['pay_money'] = $item['pay_money'] / 100;

            $item['day_paid_user_arpu'] = empty($item['day_paid_user_arpu']) ? 0 : round($item['day_paid_user_arpu']/100, 2);
            $item['first_charge_user_cost'] = empty($item['first_charge_user_cost']) ? 0 : round($item['first_charge_user_cost']/100, 2);
            $item['cost_cover_rate'] = round($item['cost_cover_rate'] * 100, 2);
        }

        return $data;
    }

    /**
     * 总表排序字段处理
     * */
    public static function getSortField($field)
    {
        $data = [
            'expense_date' => '_key',
            'paid' => 'day_paid_bucket > day_paid',
            'scan_follow_count' => 'scan_follow_count_bucket > day_scan_follow_count',
            'follow_cost' => 'follow_cost',
            'first_roi' => 'first_roi',
            'pay_money' => 'day_pay_money_bucket > day_pay_money',
            'pay_money_total' => 'pay_money_total',
            'pay_user' => 'day_pay_user_bucket > day_pay_user',
            'pay_user_total' => 'pay_user_total',
            'pay_count' => 'day_pay_count_bucket > day_pay_count',
            'pay_count_total' => 'pay_count_total',
            'first_charge_user_cost' => 'first_charge_user_cost',
            'cost_cover_rate' => 'cost_cover_rate',
            'day_paid_user_arpu' => 'day_paid_user_arpu'
        ];

        return isset($data[$field]) ? $data[$field] : 'day_paid_bucket > day_paid';
    }
}