playlet/app/Service/NewStatistic...

<?php
/**
 * Created by PhpStorm.
 * User: shensong
 * Date: 2023/1/10
 * Time: 18:23
 */

namespace App\Service;


use App\Log;
use App\Models\AdqUser;
use App\Models\AuthorizeCorp;
use App\Models\DjOrder;
use App\Models\DjUser;
use App\Models\DramaSeries;
use App\Models\DramaUserRela;
use App\Models\Es\AccountDataTrend;
use App\Models\OfficialAccount;
use App\Models\PlayletTrendStatistics;
use App\Models\Report\CustomerServiceData;
use App\Models\Report\DjCumulativeRecoveryData;
use App\Models\Report\DjRegUserRangeReport;
use App\Models\Report\NonReleaseRecovery;
use App\Models\Report\OperateDayRetrieve;
use App\Models\System\AdminManageCorp;
use App\Models\System\Users;
use App\Models\TencentAdAuth;
use App\Models\TencentAdDailyReport;
use App\RedisModel;
use App\Service\Admin\AdminService;
use App\Support\EmailQueue;

class NewStatisticsService
{
    # 运营数据统计
    public static function operateDayRetrieveByAccount($appId, $start, $end, $page, $pageSize, $dramaId, $sysGroupId
        , $adminId, $isSystemAdmin)
    {
        $today_date = date('Y-m-d');

        // 若选择截止日期大于今日，则将截止日期修改为今日
        if( !$end || $end>$today_date ){
            $end = $today_date;
        }

        #定义回收时段
        $retrievePart = array();    // 动态表头对应的默认回收数据
        $retrieveColumns = array(); // 动态表头

        $dmonth = $ddmonth = date('Y-m-01'); //当前月份
        $dymonth = date('Y-01-01', strtotime('-1 years'));//去年一月

        // 今年月份表头以及对应默认值格式化
        while($dmonth>=$dymonth){
            $retrievePart[$dmonth] = 0;

            $retrieveColumns[] = [
                'name'=> intval( substr($dmonth, 2, 4) ) . '年' . intval( substr($dmonth, 5, 2) ).'月用户回收',
                'column' => $dmonth
            ];

            $dmonth = date('Y-m-01', strtotime($dmonth.' -1 month'));
        }

        $lyear = $year = date('Y') - 2; // 去年

        while($year>='2021') {
            $retrievePart[$year] = 0; // 去年对应的默认回收数据
            $retrieveColumns[] = [
                'name' => substr($year, 2, 2). '年用户回收',
                'column' => $year
            ];
            $year = $year-1;
        }

        $retrievePart['other_pay_uv'] = 0;
        $retrievePart['other_pay_money'] = 0;
        $retrieveColumns[] = [
            'name' => '非投放回收人数',
            'column' => 'other_pay_uv'
        ];
        $retrieveColumns[] = [
            'name' => '非投放回收金额',
            'column' => 'other_pay_money'
        ];

        // 总回收表头字段及默认值
        $retrievePart['total_pay'] = 0;
        $retrieveColumns[] = [
            'name' => '运营总回收',
            'column' => 'total_pay'
        ];

        # 登录账号数据权限
        $mpAppIdList = OfficialAccount::getAccountMpAppIdList($adminId, $sysGroupId, $isSystemAdmin);
        if(empty($mpAppIdList)) {
            return [
                'list' => [],
                'total'=> 0,
                'retrieveColumns' => $retrieveColumns
            ];
        }

        //短剧筛选
        if($dramaId){
            $dramaInfo = DramaUserRela::query()
                ->where('drama_id', $dramaId)
                ->where(function($query) use($appId, $mpAppIdList){
                    if($appId) {
                        $query->where('app_id', $appId);
                    } else {
                        $query->whereIn('app_id', $appId);
                    }
                })
                ->where('sys_group_id', $sysGroupId)
                ->get();
            if( $dramaInfo->isEmpty() ){
                return [
                    'list' => [],
                    'total'=> 0,
                    'retrieveColumns' => $retrieveColumns
                ];
            }

            return NewStatisticsService::operateDayRetrieveDrama($dramaInfo, $start, $end, $retrievePart, $retrieveColumns
                , $page, $pageSize, $mpAppIdList);
        }

        if( !$start ){
            $start = date('Y-m-d', strtotime('-12 month'));
        }

        $total = (strtotime($end) - strtotime($start))/86400+1;
        $offset = ($page-1) * $pageSize;

        $npNum = $total<$pageSize ? $total : $pageSize;
        $pEnd = date('Y-m-d', strtotime($end .' -'.$offset.' day'));
        $pStart = date('Y-m-d', strtotime($pEnd .' -'.($npNum-1).' day'));

        if($pEnd<$start){
            return [
                'list' => [],
                'total'=> $total,
                'retrieveColumns' => $retrieveColumns
            ];
        }

        if($pStart<$start){
            $pStart = $start;
        }

        $dataStat = self::getTrendData($pStart, $pEnd, $mpAppIdList, $sysGroupId, 1);

        $data = array();
        while($pStart<=$pEnd){
            if( isset($dataStat[$pEnd]) ){
                $data[] = $dataStat[$pEnd];
            } else {
                $retrieveInfo = $retrievePart;
                #查日报
                $cost = null;
                $fanNew = null;
                $dayReport = TencentAdDailyReport::query()
                    ->where('ref_date', $pEnd)->whereIn('app_id', $mpAppIdList)
                    ->selectRaw('sum(paid) as cost, sum(scan_follow_count) as fan_new')
                    ->first();
                if( isset($dayReport->cost) ){
                    $cost = round($dayReport->cost/100, 2);
                    $fanNew = $dayReport->fan_new;
                }

                #查当日订单
                $orders = DjOrder::query()
                    ->where('order_pay_time', '>=', strtotime($pEnd.' 00:00:00').'000')
                    ->where('order_pay_time', '<=', strtotime($pEnd.' 23:59:59').'000' )
                    ->where('enable', 1)
                    ->where('pay_status', 1)
                    ->where('is_ad_user', 1)
                    ->where('order_type', 1)
                    ->whereIn('bind_app_id', $mpAppIdList)
                    ->where(function($query) use($sysGroupId, $mpAppIdList, $appId){
                        if($appId) $query->where('bind_app_id', $appId);
                    })
                    ->select('mp_user_register_time', 'pay_money', 'external_userid')
                    ->get();

                $otherOrders = DjOrder::query()
                    ->selectRaw('count(distinct(openid)) as pay_uv, sum(pay_money) as pay_money')
                    ->where('order_pay_time', '>=', strtotime($pEnd.' 00:00:00').'000')
                    ->where('order_pay_time', '<=', strtotime($pEnd.' 23:59:59').'000' )
                    ->where('enable', 1)
                    ->where('pay_status', 1)
                    ->where('is_ad_user', 0)
                    ->where('order_type', 1)
                    ->where(function($query) use($sysGroupId){
                        if($sysGroupId) $query->where('sys_group_id', $sysGroupId);
                    })->first();

                $newPayArr = [];
                $newPayMoney = 0;
                if( !$orders->isEmpty() ){
                    foreach($orders as $order){
                        $order->pay_money = round($order->pay_money/10000, 2);
                        $pay_date = date('Y-m-d', substr($order->mp_user_register_time, 0, 10));
                        if( $pay_date==$pEnd ){
                            //新用户
                            $newPayArr[] = $order->external_userid;
                            $newPayMoney += $order->pay_money;

                            if($pay_date>=$dymonth){
                                //今年的
                                $mon_key = substr($pay_date, 0, 7).'-01';
                                $retrieveInfo[$mon_key] += $order->pay_money;
                            } else {
                                //往年
                                if(isset($retrieveInfo[substr($pay_date,0,4)])){
                                    $retrieveInfo[substr($pay_date,0,4)] += $order->pay_money;
                                }
                            }
                        } else {
                            if($pay_date>=$dymonth){
                                //今年的
                                $mon_key = substr($pay_date, 0, 7).'-01';
                                $retrieveInfo[$mon_key] += $order->pay_money;
                            } else {
                                //往年
                                if(isset($retrieveInfo[substr($pay_date,0,4)])){
                                    $retrieveInfo[substr($pay_date,0,4)] += $order->pay_money;
                                }
                            }
                        }

                        if($pay_date<$ddmonth){
                            $retrieveInfo['total_pay'] += $order->pay_money;
                        }
                    }

                    $newPayMoney = round($newPayMoney, 2);
                    $retrieveInfo = array_map(function($val){
                        return round($val, 2);
                    }, $retrieveInfo);
                }

                $newPayUc = count( array_unique($newPayArr) );
                $newRoi = $cost>0 ? round($newPayMoney/$cost, 2) : null;
                $new_cost = $newPayUc>0 ? round($cost/$newPayUc, 2) : null;
                $otherPayUv = isset($otherOrders->pay_uv) ? $otherOrders->pay_uv : null;
                $other_pay_money = isset($otherOrders->pay_money) ? round($otherOrders->pay_money/10000, 2) : null;

                $retrieveInfo['other_pay_uv'] = $otherPayUv;
                $retrieveInfo['other_pay_money'] = $other_pay_money;

                $data[] = [
                    'date' => $pEnd,
                    'cost' => $cost,
                    'fan_new' => $fanNew,
                    'new_pay_uc' => $newPayUc,
                    'new_pay_money' => $newPayMoney,
                    'new_roi' => $newRoi,
                    'new_cost' => $new_cost,
                    'retrieveInfo' => $retrieveInfo,
                ];

            }

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

        return [
            'list' => $data,
            'total' => $total,
            'retrieveColumns' => $retrieveColumns
        ];
    }

    public static function operateDayRetrieveAdqByAccount($start, $end, $page, $pageSize, $sysGroupId, $adminId
        , $isSystemAdmin)
    {
        $today_date = date('Y-m-d');

        // 若选择截止日期大于今日，则将截止日期修改为今日
        if( !$end || $end>$today_date ){
            $end = $today_date;
        }

        #定义回收时段
        $retrievePart = array();    // 动态表头对应的默认回收数据
        $retrieveColumns = array(); // 动态表头

        $dmonth = $ddmonth = date('Y-m-01'); //当前月份
        $dymonth = date('Y-01-01', strtotime('-1 year'));//当年一月

        // 今年月份表头以及对应默认值格式化
        while($dmonth>=$dymonth){
            $retrievePart[$dmonth] = 0;

            $retrieveColumns[] = [
                'name'=> intval( substr($dmonth, 2, 4) ) . '年' . intval( substr($dmonth, 5, 2) ) . '月用户回收',
                'column' => $dmonth
            ];

            $dmonth = date('Y-m-01', strtotime($dmonth.' -1 month'));
        }

        $lyear = $year = date('Y') - 2; // 去年

        while($year >= '2021') {
            $retrievePart[$year] = 0; // 去年对应的默认回收数据
            $retrieveColumns[] = [
                'name' => substr($year, 2, 2). '年用户回收',
                'column' => $year
            ];

            $year = $year-1;
        }

        $retrievePart['other_pay_uv'] = 0;
        $retrievePart['other_pay_money'] = 0;
        $retrieveColumns[] = [
            'name' => '非投放回收人数',
            'column' => 'other_pay_uv'
        ];
        $retrieveColumns[] = [
            'name' => '非投放回收金额',
            'column' => 'other_pay_money'
        ];

        // 总回收表头字段及默认值
        $retrievePart['total_pay'] = 0;
        $retrieveColumns[] = [
            'name' => '运营总回收',
            'column' => 'total_pay'
        ];

        $adqAccountIdList = PitcherService::adqAccountListForUser($adminId, $sysGroupId, $isSystemAdmin);
        if(empty($adqAccountIdList)) {
            return [
                'list' => [],
                'total'=> 0,
                'retrieveColumns' => $retrieveColumns
            ];
        }

        if( !$start ){
            $start = date('Y-m-d', strtotime('-12 month'));
        }

        $total = (strtotime($end) - strtotime($start))/86400+1;

        $offset = ($page-1) * $pageSize;

        $npNum = $total<$pageSize ? $total : $pageSize;
        $pEnd = date('Y-m-d', strtotime($end .' -'.$offset.' day'));
        $pStart = date('Y-m-d', strtotime($pEnd .' -'.($npNum-1).' day'));

        if($pEnd<$start){
            return [
                'list' => [],
                'total'=> $total,
                'retrieveColumns' => $retrieveColumns
            ];
        }

        if($pStart<$start){
            $pStart = $start;
        }

        $dataStat = self::getTrendData($pStart, $pEnd, $adqAccountIdList, $sysGroupId, 2);

        $data = array();
        while($pStart<=$pEnd){
            if( isset($dataStat[$pEnd]) ){
                $data[] = $dataStat[$pEnd];
            } else {

                $retrieveInfo = $retrievePart;
                #查日报
                $cost = null;
                $fanNew = null;
                $dayReport = TencentAdDailyReport::query()
                    ->where('ref_date', $pEnd)
                    ->where(function($query) use($sysGroupId, $adqAccountIdList){
                        if(!empty($sysGroupId)) $query->whereIn('account_id', $adqAccountIdList);
                    })
                    ->selectRaw('sum(paid) as cost, sum(scan_follow_count) as fan_new')
                    ->first();
                if( isset($dayReport->cost) ){
                    $cost = round($dayReport->cost/100, 2);
                    $fanNew = $dayReport->fan_new;
                }

                #查当日订单
                $orders = DjOrder::query()
                    ->where('order_pay_time', '>=', strtotime($pEnd.' 00:00:00').'000')
                    ->where('order_pay_time', '<=', strtotime($pEnd.' 23:59:59').'000' )
                    ->where('enable', 1)->where('pay_status', 1)
                    ->where('order_type', 2)->where('is_ad_user', 1)
                    ->whereIn('adq_account_id', $adqAccountIdList)
                    ->select('mp_user_register_time', 'pay_money', 'external_userid')
                    ->get();

                $otherOrders = DjOrder::query()
                    ->selectRaw('count(distinct(openid)) as pay_uv, sum(pay_money) as pay_money')
                    ->where('order_pay_time', '>=', strtotime($pEnd.' 00:00:00').'000')
                    ->where('order_pay_time', '<=', strtotime($pEnd.' 23:59:59').'000' )
                    ->where('enable', 1)->where('pay_status', 1)->where('is_ad_user', 0)->where('order_type', 2)
                    ->where('sys_group_id', $sysGroupId)->first();

                $newPayArr = [];
                $newPayMoney = 0;
                if( !$orders->isEmpty() ){
                    foreach($orders as $order){
                        $order->pay_money = round($order->pay_money/10000, 2);
                        $pay_date = date('Y-m-d', substr($order->mp_user_register_time, 0, 10));
                        if( $pay_date==$pEnd ){
                            //新用户
                            $newPayArr[] = $order->external_userid;
                            $newPayMoney += $order->pay_money;

                            if($pay_date>=$dymonth){
                                //今年的
                                $mon_key = substr($pay_date, 0, 7).'-01';
                                $retrieveInfo[$mon_key] += $order->pay_money;
                            } else {
                                //往年
                                if(isset($retrieveInfo[substr($pay_date,0,4)])){
                                    $retrieveInfo[substr($pay_date,0,4)] += $order->pay_money;
                                }
                            }
                        } else {
                            if($pay_date>=$dymonth){
                                //今年的
                                $mon_key = substr($pay_date, 0, 7).'-01';
                                $retrieveInfo[$mon_key] += $order->pay_money;
                            } else {
                                //往年
                                if(isset($retrieveInfo[substr($pay_date,0,4)])){
                                    $retrieveInfo[substr($pay_date,0,4)] += $order->pay_money;
                                }
                            }
                        }

                        if($pay_date<$ddmonth){
                            $retrieveInfo['total_pay'] += $order->pay_money;
                        }
                    }

                    $newPayMoney = round($newPayMoney, 2);
                    $retrieveInfo = array_map(function($val){
                        return round($val, 2);
                    }, $retrieveInfo);
                }

                $newPayUc = count( array_unique($newPayArr) );
                $newRoi = $cost>0 ? round($newPayMoney/$cost, 2) : null;
                $new_cost = $newPayUc>0 ? round($cost/$newPayUc, 2) : null;
                $otherPayUv = isset($otherOrders->pay_uv) ? $otherOrders->pay_uv : null;
                $other_pay_money = isset($otherOrders->pay_money) ? round($otherOrders->pay_money/10000, 2) : null;

                $retrieveInfo['other_pay_uv'] = $otherPayUv;
                $retrieveInfo['other_pay_money'] = $other_pay_money;

                $data[] = [
                    'date' => $pEnd,
                    'cost' => $cost,
                    'fan_new' => $fanNew,
                    'new_pay_uc' => $newPayUc,
                    'new_pay_money' => $newPayMoney,
                    'new_roi' => $newRoi,
                    'new_cost' => $new_cost,
                    'retrieveInfo' => $retrieveInfo,
                ];

            }

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

        return [
            'list' => $data,
            'total' => $total,
            'retrieveColumns' => $retrieveColumns
        ];
    }

    public static function operateDayRetrieveDrama($drama_info, $start, $end, $retrievePart, $retrieveColumns, $page,
                                                   $pageSize, $mpAppIdList)
    {
        $ddmonth = date('Y-m-01');//当前月份
        $dymonth = date('Y-01-01', strtotime('-1 year'));//当年一月
        $data = array();
        foreach($drama_info as $item){
            $app_id = $item->app_id;

            if($start){
                if($item->start_date<$start){
                    $item->start_date = $start;
                }
            }

            if($end){
                if($item->end_date>$end){
                    $item->end_date = $end;
                }
            }

            $sdate = $item->start_date;
            $edate = $item->end_date;

            while($edate>=$sdate){

                $retrieveInfo = $retrievePart;
                #查日报
                $cost = null;
                $fan_new = null;
                $dayReport = TencentAdDailyReport::query()
                    ->where('ref_date', $edate)
                    ->where(function($query) use($app_id){
                        if($app_id) $query->where('app_id', $app_id);
                    })
                    ->selectRaw('sum(paid) as cost, sum(scan_follow_count) as fan_new')
                    ->first();
                if( isset($dayReport->cost) ){
                    $cost = round($dayReport->cost/100, 2);
                    $fan_new = $dayReport->fan_new;
                }

                #查当日订单
                $orders = DjOrder::query()
                    ->where('order_pay_time', '>=', strtotime($edate.' 00:00:00').'000')
                    ->where('order_pay_time', '<=', strtotime($edate.' 23:59:59').'000' )
                    ->where('enable', 1)
                    ->where('pay_status', 1)
                    ->where('is_ad_user', 1)
                    ->where('order_type', 1)
                    ->where(function($query) use($app_id){
                        if($app_id) $query->where('bind_app_id', $app_id);
                    })
                    ->select('mp_user_register_time', 'pay_money', 'external_userid')
                    ->get();

                $otherOrders = DjOrder::query()
                    ->selectRaw('count(distinct(openid)) as pay_uv, sum(pay_money) as pay_money')
                    ->where('order_pay_time', '>=', strtotime($edate.' 00:00:00').'000')
                    ->where('order_pay_time', '<=', strtotime($edate.' 23:59:59').'000' )
                    ->where('enable', 1)
                    ->where('pay_status', 1)
                    ->where('is_ad_user', 0)
                    ->where('order_type', 1)
                    ->where(function($query) use($app_id){
                        if($app_id) $query->where('bind_app_id', $app_id);
                    })->first();

                $new_pay_arr = [];
                $new_pay_money = 0;
                if( !$orders->isEmpty() ){
                    foreach($orders as $order){
                        $order->pay_money = round($order->pay_money/10000, 2);
                        $pay_date = date('Y-m-d', substr($order->mp_user_register_time, 0, 10));
                        if( $pay_date==$edate ){
                            //新用户
                            $new_pay_arr[] = $order->external_userid;
                            $new_pay_money += $order->pay_money;

                            if($pay_date>=$dymonth){
                                //今年的
                                $mon_key = substr($pay_date, 0, 7).'-01';
                                $retrieveInfo[$mon_key] += $order->pay_money;
                            } else {
                                //往年
                                if(isset($retrieveInfo[substr($pay_date,0,4)])){
                                    $retrieveInfo[substr($pay_date,0,4)] += $order->pay_money;
                                }
                            }
                        } else {
                            if($pay_date>=$dymonth){
                                //今年的
                                $mon_key = substr($pay_date, 0, 7).'-01';
                                $retrieveInfo[$mon_key] += $order->pay_money;
                            } else {
                                //往年
                                if(isset($retrieveInfo[substr($pay_date,0,4)])){
                                    $retrieveInfo[substr($pay_date,0,4)] += $order->pay_money;
                                }
                            }
                        }

                        if($pay_date<$ddmonth){
                            $retrieveInfo['total_pay'] += $order->pay_money;
                        }
                    }

                    $new_pay_money = round($new_pay_money, 2);
                    $retrieveInfo = array_map(function($val){
                        return round($val, 2);
                    }, $retrieveInfo);
                }

                $new_pay_uc = count( array_unique($new_pay_arr) );
                $new_roi = $cost>0 ? round($new_pay_money/$cost, 2) : null;
                $new_cost = $new_pay_uc>0 ? round($cost/$new_pay_uc, 2) : null;
                $other_pay_uv = isset($otherOrders->pay_uv) ? $otherOrders->pay_uv : 0;
                $other_pay_money = isset($$otherOrders->pay_money) ? round($otherOrders->pay_money/10000, 2) : 0;

                $retrieveInfo['other_pay_uv'] = $other_pay_uv;
                $retrieveInfo['other_pay_money'] = $other_pay_money;

                if( isset($data[$edate]) ){
                    $data[$edate]['cost'] += $cost;
                    $data[$edate]['fan_new'] += $fan_new;
                    $data[$edate]['new_pay_uc'] += $new_pay_uc;
                    $data[$edate]['new_pay_money'] += $new_pay_money;

                    foreach($retrieveInfo as $k=>$v){
                        $data[$edate]['retrieveInfo'][$k] += $v;
                    }

                } else {
                    $data[$edate] = [
                        'date' => $edate,
                        'cost' => $cost,
                        'fan_new' => $fan_new,
                        'new_pay_uc' => $new_pay_uc,
                        'new_pay_money' => $new_pay_money,
                        'new_roi' => $new_roi,
                        'new_cost' => $new_cost,
                        'retrieveInfo' => $retrieveInfo,
                    ];

                }

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

        }

        if( !empty($data) ){
            krsort($data);

            foreach($data as $k=>&$v){
                $v['new_roi'] = $v['cost']>0 ? round($v['new_pay_money']/$v['cost'], 2) : null;
                $v['new_cost'] = $v['new_pay_uc']>0 ? round($v['cost']/$v['new_pay_uc'], 2) : null;
            }
        }

        $offset = ($page-1) * $pageSize;
        $total = count($data);
        $list = array_slice(array_values($data), $offset, $pageSize);

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

    public static function getTrendData($startDate, $endDate, $accountIdList, $sysGroupId, $adType)
    {
        # 统一将需要的离线数据查询出来
        $dataStat = OperateDayRetrieve::query()->selectRaw('expense_date as date, recovery_month, '
            .'sum(cost) as cost, sum(fan_new) as fan_new, sum(new_pay_money) as new_pay_money, sum(recovery_money)'
            .' as recovery_money')->whereIn('account_id', $accountIdList)->where('expense_date', '>=', $startDate)
            ->where('expense_date', '<=', $endDate)->where('enable', 1)->groupBy(['date', 'recovery_month'])->get();

        $nonReleaseRecovery = NonReleaseRecovery::query()->selectRaw('expense_date as date, pay_uv, pay_money')
            ->where('enable', 1)->where('expense_date', '>=', $startDate)->where('expense_date', '<=', $endDate)
            ->where('sys_group_id', $sysGroupId)->where('ad_type', (3-$adType))->get();

        $data = [];
        $date = $startDate;
        $year = date('Y') - 2; // 去年

        $retrievePart = [];
        while($year >= '2021') {
            $retrievePart[$year] = 0; // 去年对应的默认回收数据
            $year = $year-1;
        }

        if($endDate >= date('Y-m-d', strtotime('-1 days'))) {
            $endDate = date('Y-m-d', strtotime('-1 days'));
        }

        // 实时查询首日下单人数（因为涉及到多个投放账号之间的充值人数去重问题）
        $newPayUcList = DjOrder::query()->selectRaw("FROM_UNIXTIME(LEFT(mp_user_register_time,10),'%Y-%m-%d') "
            ."as expense_date, count(distinct(external_userid)) as uv")
            ->where('pay_status', 1)->where('enable', 1)
            ->where('is_ad_user', 1)->where('order_type', $adType)
            ->where('mp_user_register_time', '>=', strtotime($startDate.' 00:00:00').'000')
            ->where('mp_user_register_time', '<=', strtotime($endDate.' 23:59:59').'000' )
            ->where('order_pay_time', '>=', strtotime($startDate.' 00:00:00').'000')
            ->where('order_pay_time', '<=', strtotime($endDate.' 23:59:59').'000' )
            ->whereRaw("FROM_UNIXTIME(LEFT(mp_user_register_time,10),'%Y-%m-%d')=FROM_UNIXTIME(LEFT(order_pay_time,10),'%Y-%m-%d')")
            ->where(function ($query) use ($adType, $accountIdList) {
                if(1 == $adType) {
                    $query->whereIn('bind_app_id', $accountIdList);
                } else {
                    $query->whereIn('adq_account_id', $accountIdList);
                }
            })->groupBy('expense_date')->get();

        // 将所需要的数据按照日期处理成之前接口的格式，然后返回
        while($date <= $endDate) {
            $item = [];
            $item['date'] = $date;
            // 统计日期的成本，新粉数，新粉充值金额直接累加
            $item['cost'] = $dataStat->where('date', $date)->sum('cost');
            $item['fan_new'] = $dataStat->where('date', $date)->sum('fan_new');
            $item['new_pay_money'] = $dataStat->where('date', $date)->sum('new_pay_money');

            $newPayUc = $newPayUcList->where('expense_date', $date)->first();
            $item['new_pay_uc'] = isset($newPayUc->uv) ? round($newPayUc->uv) : 0;

            // 计算企微成本以及首日roi
            $item['new_cost'] = $item['new_pay_uc'] > 0 ? round($item['cost']/$item['new_pay_uc'], 2) : 0;
            $item['new_roi'] = $item['cost'] > 0 ? round($item['new_pay_money']/$item['cost'], 2) : 0;

            // 这里这样处理的原因是需要保持和之前接口返回的数据格式一致
            $retrieveInfo = [];
            $month = date('Y-01', strtotime('-1 year'));
            $endMonth = date('Y-m');
            // 统计近两年各个月份的回收金额，按月份放入一个字段中
            while($month<=$endMonth){
                $recoveryInfo = $dataStat->where('date', $date)->where('recovery_month', $month)
                    ->first();
                $retrieveInfo[$month.'-01'] = isset($recoveryInfo->recovery_money) ? round($recoveryInfo->recovery_money, 2): 0;
                $month = date('Y-m', strtotime($month.'+1 month'));
            }

            foreach($retrievePart as $year => $payMoney) {
                // 统计去年的回收金额
                $retrieveInfo[$year] = $dataStat->where('recovery_month', '>=', $year.'-01')
                    ->where('recovery_month', '<=', $year . '-12')->where('date', $date)
                    ->sum('recovery_money');
                $retrieveInfo[$year] = round($retrieveInfo[$year], 2);
            }

            // 因为非投放回收金额无法归类到具体的投放账号，因此这里展示了所在公司账号在当天的回收金额以及人数
            $retrieveInfo['other_pay_uv'] = $nonReleaseRecovery->where('date', $date)->sum('pay_uv');
            $retrieveInfo['other_pay_money'] = $nonReleaseRecovery->where('date', $date)->sum('pay_money');
            $retrieveInfo['total_pay'] = $dataStat->where('date', $date)
                ->where('recovery_month', '<', date('Y-m'))->sum('recovery_money');

            # 数据格式化处理
            $retrieveInfo['other_pay_uv'] = round($retrieveInfo['other_pay_uv']);
            $retrieveInfo['other_pay_money'] = round($retrieveInfo['other_pay_money']);
            $retrieveInfo['total_pay'] = round($retrieveInfo['total_pay'], 2);
            $item['retrieveInfo'] = $retrieveInfo;

            $item['cost'] = round($item['cost'], 2);
            $item['fan_new'] = round($item['fan_new']);
            $item['new_pay_money'] = round($item['new_pay_money'], 2);

            $data[$date] = $item;
            $date = date('Y-m-d', strtotime($date . ' +1 days'));
        }

        return $data;
    }

    /* 数据循环统计 */
    public static function dataCycleListByAccount($params, $orderType, $page, $pageSize, $sortField, $sortType)
    {
        $requestData = [
            'page'       => $page,
            'page_size'  => $pageSize,
            'sort_field' => $sortField,
            'sort_type'  => $sortType,
            'params'     => $params,
            'order_type' => $orderType,
        ];

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

        //补充头列表
        $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' => false],
            ['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];
        }

//        Log::logInfo('1', [getMillisecond()], 'time_test');
        try{

            // 1mp 2adq
            if(1 == $orderType) {
                $accountIdList = OfficialAccount::getAccountMpAppIdList($params['admin_id'], $params['sys_group_id'], $params['is_system_admin']);
            } else {
                $accountIdList = PitcherService::adqAccountListForUser($params['admin_id'], $params['sys_group_id'], $params['is_system_admin']);
            }
//            Log::logInfo('2', [getMillisecond()], 'time_test');
            if(empty($accountIdList)){
                $overview['head'] = $overviewHead;
                $overview['list'] = [];
                $data['head'] = $listHead;
                $data['list'] = [];
                return [0, $data, $overview];
            }

            if(isset($params['closing_date']) && $params['closing_date']) {
                if($params['closing_date'] < $params['end_date']) {
                    $params['end_date'] = $params['closing_date'];
                }
            }

            list($list, $count) = DjRegUserRangeReport::getDataTrend($params, $sortField, $sortType, $page, $pageSize, $accountIdList);
            $list = $list->toArray();

            # 提取日期
            $dateList = array_column($list, 'date');
            $trendData = DjRegUserRangeReport::getTendData($accountIdList, $dateList);
            $trendData = array_column($trendData->toArray(), null, 'date');

//            Log::logInfo('3', [getMillisecond()], 'time_test');
            foreach ($list as $k=>$item){
                # 查询当日新增用户首单人数
                $firstDayPaidUserInfo = DjOrder::query()->selectRaw('count(distinct(external_userid)) as pay_user_unique')
                    ->where('pay_status', 1)->where('enable', 1)->where('order_type', $orderType)
                    ->whereBetween('order_pay_time',[
                        strtotime($item['date'].' 00:00:00')*1000,
                        strtotime($item['date'].' 23:59:59')*1000
                    ])
                    ->whereBetween('mp_user_register_time',[
                        strtotime($item['date'].' 00:00:00')*1000,
                        strtotime($item['date'].' 23:59:59')*1000
                    ])
                    ->where(function($query) use ($orderType, $accountIdList, $params) {
                        if($params['plat_order_type']) $query->where('plat_order_type', $params['plat_order_type']);
                        if(1 == $orderType) {
                            $query->whereIn('bind_app_id', $accountIdList);
                        } else {
                            $query->whereIn('adq_account_id', $accountIdList);
                        }
                    })->first();
                $firstDayPaidUserUnique = $firstDayPaidUserInfo->pay_user_unique ?? 0;
//                Log::logInfo('4', [getMillisecond()], 'time_test');

                # 如果筛选了收益截止日期，则实时计算总回收金额以及回本率
                if($params['closing_date']) {
                    $totalPay = DjOrder::query()->selectRaw('sum(pay_money) as money')
                        ->where('pay_status', 1)->where('enable', 0)->where('order_type', $orderType)
                        ->whereBetween('order_pay_time',[
                            strtotime($item['date'].' 00:00:00')*1000,
                            strtotime($params['closing_date'].' 23:59:59')*1000
                        ])
                        ->whereBetween('mp_user_register_time',[
                            strtotime($item['date'].' 00:00:00')*1000,
                            strtotime($item['date'].' 23:59:59')*1000
                        ])
                        ->where(function($query) use ($orderType, $accountIdList, $params) {
                            if($params['plat_order_type']) $query->where('plat_order_type', $params['plat_order_type']);
                            if(1 == $orderType) {
                                $query->whereIn('bind_app_id', $accountIdList);
                            } else {
                                $query->whereIn('adq_account_id', $accountIdList);
                            }
                        })->first();
                    $list[$k]['total_cvt_amt'] = $totalPay->money ?? 0;
                    $list[$k]['total_roi'] = $item['advertiser_cost'] > 0 ? round($list[$k]['total_cvt_amt'] / $item['advertiser_cost'], 4) * 100 : 0;
                }

                # 数据格式化处理
                $list[$k]['advertiser_cost'] = round($list[$k]['advertiser_cost'], 2);
                $list[$k]['first_day_roi'] = empty($list[$k]['first_day_roi']) ? '0.00%' : round($list[$k]['first_day_roi'], 2).'%';
                $list[$k]['pay_user_unique'] = $firstDayPaidUserUnique;
                $list[$k]['first_order_cost_unique'] = $firstDayPaidUserUnique ?
                    round($list[$k]['advertiser_cost'] / $firstDayPaidUserUnique, 2) : '0.00';
                $list[$k]['follow_uv'] = empty( $list[$k]['follow_uv'])? '0' : round($list[$k]['follow_uv']);
                $list[$k]['per_follow_cost'] = empty( $list[$k]['per_follow_cost'])? '0' : round($list[$k]['per_follow_cost'], 2);
                $list[$k]['total_roi'] = empty($list[$k]['total_roi']) ? '0.00%' : round($list[$k]['total_roi'], 2).'%';
                $list[$k]['first_order_cost'] = empty(round($list[$k]['advertiser_cost'])) || empty(round($list[$k]['first_order_ucnt'])) ?
                    '0.00' :
                    round($list[$k]['advertiser_cost']/$list[$k]['first_order_ucnt'],2);
                $list[$k]['total_cvt_amt'] = empty($list[$k]['total_cvt_amt']) ? '0.00' : $list[$k]['total_cvt_amt'];

                # 处理day1~day100的数据
                $advertiser_cost_amount = $list[$k]['advertiser_cost'];
                foreach (DjRegUserRangeReport::count_range_days() as $day) {

//                    $day_cvt_amt = $list[$k]['day'.$day.'_cvt_amt'];
                    $day_cvt_amt = $trendData[$item['date']]['day'.$day.'_cvt_amt'] ?? 0;
                    if($params['closing_date']) {
                        $searchEndDate = $params['closing_date'];
                    } else {
                        $searchEndDate = date('Y-m-d');
                    }
                    //补充ROI
                    if(strtotime($item['date'])+($day-1)*86400 > strtotime($searchEndDate) ){    //大于当前时间，才是空字符串
                        $list[$k]['day'.$day.'_roi'] = '';
                    }elseif(round($day_cvt_amt)>0 && round($advertiser_cost_amount)>0){
                        $list[$k]['day'.$day.'_roi'] = round($day_cvt_amt * 100 / $advertiser_cost_amount,2).'%';
                    }else{
                        $list[$k]['day'.$day.'_roi'] = '0.00%';
                    }
                    unset($list[$k]['day'.$day.'_cvt_amt']);
                }
            }
//            Log::logInfo('5', [getMillisecond()], 'time_test');
            $overviewList = self::dataCycleListOverview($params, $orderType, $accountIdList);
//            Log::logInfo('9', [getMillisecond()], 'time_test');
            $overview['list'] = $overviewList;
            $overview['head'] = $overviewHead;
            $data['list'] = $list;
            $data['head'] = $listHead;

        } catch (\Exception $exception) {
            Log::logError('NewStatisticsService.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'], '猎羽');

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

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

    public static function dataCycleListOverview($params, $orderType, $accountIdList)
    {
        $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%'];

        $list = DjRegUserRangeReport::getSummaryData($params, $accountIdList);
        if($list->isNotEmpty()) {
            $dateList = array_unique($list->pluck('date')->toArray());

            $minDate = min($dateList);
            $maxDate = max($dateList);
//            Log::logInfo('6', [getMillisecond()], 'time_test');
            $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($params['plat_order_type'])) {
                $orderQuery->where('plat_order_type', $params['plat_order_type']);
            }

            // 1mp 2adq
            if(1 == $orderType) {
                $orderQuery->whereIn("bind_app_id", $accountIdList);
            } else if (2 == $orderType) {
                $orderQuery->whereIn("adq_account_id", $accountIdList);
            }

            $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;
//            Log::logInfo('7', [getMillisecond()], 'time_test');
            $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%';
//            Log::logInfo('8', [getMillisecond()], 'time_test');
        }

        return $data;
    }

    public static function cumulativeRecoveryData($startDate, $endDate, $page, $pageSize, $isSystemAdmin, $adminId, $sysGroupId)
    {
        $todayDate = date('Y-m-d');

        // 若选择截止日期大于今日，则将截止日期修改为今日
        if( !$endDate || $endDate>$todayDate ){
            $endDate = $todayDate;
        }

        $dmonth = $ddmonth = date('Y-m-01'); //当前月份
        $dymonth = '2023-03-01';//当年一月

        $retrieveColumns = DjCumulativeRecoveryData::getReportTitle($dmonth, $dymonth);

        # 登录账号数据权限
        $appIdList = OfficialAccount::getSysGroupMpAppIdList($sysGroupId, 'cumulativeRecoveryData');
        if(empty($appIdList)) {
            return [
                'list' => [],
                'total'=> 0,
                'retrieveColumns' => $retrieveColumns
            ];
        }

        if( !$startDate ){
            $startDate = date('Y-m-d', strtotime('-1 month'));
        }

        $total = (strtotime($endDate) - strtotime($startDate))/86400+1;
        $offset = ($page-1) * $pageSize;

        $npNum = $total<$pageSize ? $total : $pageSize;
        $pEnd = date('Y-m-d', strtotime($endDate .' -'.$offset.' day'));
        $pStart = date('Y-m-d', strtotime($pEnd .' -'.($npNum-1).' day'));

        if($pEnd<$startDate) {
            return [
                'list' => [],
                'total'=> $total,
                'retrieveColumns' => $retrieveColumns
            ];
        }

        if($pStart<$startDate){
            $pStart = $startDate;
        }

        $data = DjCumulativeRecoveryData::getTrendData($pStart, $pEnd, $appIdList);

        return [
            'list' => $data,
            'total' => $total,
            'retrieveColumns' => $retrieveColumns
        ];
    }

    public static function customerServiceDataTotal($params, $isSystemAdmin, $adminId, $sysGroupId)
    {
        $defaultResponse = [
            'data' => [],
            'columns' => CustomerServiceData::SUMMARY_TITLE,
        ];

        $corpStat = AuthorizeCorp::getAllCorpList();
        $flag = self::getSearchParams($isSystemAdmin, $sysGroupId, $adminId, $corpStat, $params);
        if(!$flag) {
            return $defaultResponse;
        }

        $res = CustomerServiceData::getUserListTotal($params);
        $defaultResponse['data'] = $res;
        return $defaultResponse;
    }

    public static function customerServiceData($params, $page, $pageSize, $isSystemAdmin, $adminId, $sysGroupId)
    {
        $defaultResponse = [
            'list' => [],
            'total' => 0,
            'columns' => CustomerServiceData::TITLE,
        ];

        $corpStat = AuthorizeCorp::getAllCorpList();

        $flag = self::getSearchParams($isSystemAdmin, $sysGroupId, $adminId, $corpStat, $params);
        if(!$flag) {
            return $defaultResponse;
        }

        list($data, $total) = CustomerServiceData::getUserList($params, $page, $pageSize);

        # 提取查询结果中的客服ID
        $statUser = [];
        foreach ($data as $item) {
            $statUser[] = '("' . $item->corpid . '","' . $item->user_id . '")';
        }

        # 查询客服ID名称以及绑定的运营人员
        $operatorIdStat = AdqUser::search(['user_sql' => $statUser]);
        $userStat = DjUser::getUserBySearch(['user_sql' => $statUser]);
        $adminStat = Users::query()->where('group_admin_id', $sysGroupId)->get();

        foreach($data as &$item) {
            # 客服名称
            $userStatInfo = $userStat->where('corpid', $item->corpid)->where('user_id', $item->user_id)->first();
            $item->user_name = $userStatInfo->name ?? '';

            # 企微主体
            $corpStatInfo = $corpStat->where('corpid', $item->corpid)->first();
            $item->corp_name = $corpStatInfo->corp_name ?? '';

            # 运营人员
            $operatorIdInfo = $operatorIdStat->where('corpid', $item->corpid)->where('user_id', $item->user_id)->first();
            $operatorId = $operatorIdInfo->operator_id ?? 0;
            $operatorInfo = $adminStat->where('id', $operatorId)->first();
            $item->operator_name = $operatorInfo->name ?? '';

            $item->ref_date = substr($item->create_time, 0, 10);

            unset($item['create_time']);
        }

        return [
            'list' => $data,
            'total' => $total,
            'columns' => CustomerServiceData::TITLE,
        ];
    }

    public static function getSearchParams($isSystemAdmin, $sysGroupId, $adminId, $corpStat, &$params) {
        # 当前登录用户数据权限
        $isSuperUser = AdminService::isSuperUser($isSystemAdmin, $sysGroupId, $adminId);
        if($isSuperUser) {
            $corp_id_list = AdminManageCorp::getSysGroupCorpList($sysGroupId);
            if(empty($corp_id_list)) {
                return false;
            }

            $corp_list = $corpStat->whereIn('id', $corp_id_list)->all();
            $params['corp_list'] = array_column($corp_list, 'corpid');
        } else {
            $params['operator_uid'] = $adminId;
        }

        $userSqlQuery = [];
        if(!empty($params['user_name']) || !empty($params['corp_list'])) {
            $userRes = DjUser::getUserBySearch([
                'corpid'    => $params['corp_id'],
                'user_name' => $params['user_name'],
                'corp_list' => $params['corp_list'] ?? null,
            ]);
            if($userRes->isEmpty()) {
                return false;
            }

            foreach($userRes as $userInfo) {
                $userSqlQuery[] = '("' . $userInfo->corpid . '","' . $userInfo->user_id . '")';
            }
        }

        $operatorSqlQuery = [];
        if(!empty($params['operator_uid'])) {
            $operatorRes = AdqUser::search([
                'corpid'      => $params['corp_id'],
                'operator_id' => $params['operator_uid'],
                'corp_list'  => $params['corp_list'] ?? null,
            ]);
            if($operatorRes->isEmpty()) {
                return false;
            }

            foreach($operatorRes as $operatorInfo) {
                $operatorSqlQuery[] = '("' . $operatorInfo->corpid . '","' . $operatorInfo->user_id . '")';
            }
        }

        if(!empty($params['user_name']) && !empty($params['operator_uid'])){
            $userSqlArr = array_unique(array_intersect($userSqlQuery, $operatorSqlQuery));
            $params['user_sql'] = $userSqlArr;
            if(empty($params['user_sql'])) {
                return false;
            }
        } else if (!empty($params['user_name']) && empty($params['operator_uid'])) {
            $params['user_sql'] = $userSqlQuery;
        } else if (!empty($params['operator_uid']) && empty($params['user_name'])) {
            $params['user_sql'] = $operatorSqlQuery;
        }

        return true;
    }

    public static function throwPersonListFormat(&$list, $start, $end, $closingDate, $appId) {
        $uids = $list->pluck('user_id');
        $dramaids = $list->pluck('drama_id');
        $accountIdList = $list->pluck('account_id')->toArray();
        $accountIdList = array_filter($accountIdList);

        $accounts = OfficialAccount::query()->pluck('mp_name', 'mp_app_id');
        $users = Users::query()->whereIn('id', $uids)->pluck('name', 'id');
        $playlets = DramaSeries::query()->whereIn('id', $dramaids)->pluck('name', 'id');
        $relationList = AdqUser::getAppIdList($accountIdList);

        $accountData = RedisModel::get(PlayletTrendStatistics::ACCOUNT_USER_LIST);
        $accountData = !empty($accountData) ? json_decode($accountData, 1) : [];

        # 查询投放账号账户余额
        $accountFundsList = TencentAdAuth::query()->whereIn('account_id', $accountIdList)->select(['balance', 'account_id'])->get();

        foreach($list as $key => $item){
            #查对应时间信息
//            if(!empty($item->disable_date)) $item->end_date = min($item->end_date, $item->disable_date);// 投手禁用日期与截止日期取最小
            $item->start_date = max($item->start_date, $start);
            $item->end_date = min($item->end_date, $end);// 截止日期与当前日期取最小

            if($closingDate) {
                $item->day3_end_date = (strtotime($item->end_date . '+2 days') > strtotime($closingDate)) ?
                    date('Y-m-d', strtotime($closingDate . '-2 days')) : $item->end_date;
                $item->day7_end_date = (strtotime($item->end_date . '+6 days') > strtotime($closingDate)) ?
                    date('Y-m-d', strtotime($closingDate . '-6 days')) : $item->end_date;
            } else {
                $item->day3_end_date = (strtotime($item->end_date . '+2 days') > strtotime(date('Y-m-d'))) ?
                    date('Y-m-d', strtotime('-2 days')) : $item->end_date;
                $item->day7_end_date = (strtotime($item->end_date . '+6 days') > strtotime(date('Y-m-d'))) ?
                    date('Y-m-d', strtotime('-6 days')) : $item->end_date;
            }

            if(!empty($item->app_id)){
               $item->account_id = $item->app_id;
                # 公众号
                $item->account_name = $accounts->get($item->app_id) ?? null;
                $item->official_account_name = [];

                # 客服
                $item->user_list = [];

                # 余额
                $item->balance = 0;

            } else {
                # 获取绑定信息
                $bindInformation = PlayletTrendStatistics::getAccountBindInformation($item->account_id, $item->start_date, $item->end_date, $accounts);

                # adq账号
                $item->account_name = $item->account_id;
                if(!empty($bindInformation['bind_app_list'])) {
                    $item->official_account_name = array_column($bindInformation['bind_app_list'], 'app_name');
                    $item->official_account_id = array_column($bindInformation['bind_app_list'], 'app_id');
                } else {
                    $relationInfo = $relationList->where('account_id', $item->account_id)->first();
                    $officialAccountId = $relationInfo->app_id ?? null;
                    $official_account_name = $accounts->get($officialAccountId) ?? null;
                    $item->official_account_name = empty($official_account_name) ? [] : [$official_account_name];
                    $item->official_account_id = !empty($officialAccountId) ? [$officialAccountId] : [];
                }

                # 判断筛选公众号与当前公众号是否有交集
                if(!empty($appId) && empty(array_intersect($item->official_account_id, $appId))) {
                    unset($list[$key]);
                    continue;
                }

                # 客服
                if(!empty($bindInformation['bind_user_list'])) {
                    $item->user_list = $bindInformation['bind_user_list'];
                } else {
                    $item->user_list = $accountData[$item->account_id] ?? [];
                }

                # 余额
                $accountFundsInfo = $accountFundsList->where('account_id', $item->account_id)->first();
                $item->balance = $accountFundsInfo->balance ?? 0;
            }

            $res = PlayletTrendStatistics::query()->where('playlet_id', $item->drama_id)
                ->where('ref_date', '>=', $item->start_date)
                ->where('ref_date', '<=', $item->end_date)
                ->where('app_id', $item->account_id)
                ->where('user_id', $item->user_id)
                ->where('enable', 1)
                ->selectRaw('sum(day_paid) as cost, sum(charge_total) as pay_money, sum(new_user_charge) '
                    .'as first_day_pay, sum(scan_follow_count) as scan_follow_total, sum(new_user_charge_uv) as new_pay_uc,'
                    .' sum(first_user_charge) as first_charge, sum(conversions_count) as conversions_count, '
                    .'sum(deep_conversions_count) as deep_conversions_count, sum(followers_number) as followers_number,'
                    .'sum(repeat_fans) as repeat_fans')
                ->first();

            # 3天倍率金额
            if(!empty($item->day3_end_date)) {
                $day3Charge = PlayletTrendStatistics::query()->where('playlet_id', $item->drama_id)
                    ->where('ref_date', '>=', $item->start_date)
                    ->where('ref_date', '<=', $item->day3_end_date)
                    ->where('app_id', $item->account_id)
                    ->where('user_id', $item->user_id)
                    ->where('enable', 1)
                    ->selectRaw('sum(new_user_charge) as day3_first_day_pay, sum(day3_charge) as day3_charge')
                    ->first();
            } else  {
                $day3Charge = null;
            }

            # 7天倍率金额
            if(!empty($item->day7_end_date)) {
                $day7Charge = PlayletTrendStatistics::query()->where('playlet_id', $item->drama_id)
                    ->where('ref_date', '>=', $item->start_date)
                    ->where('ref_date', '<=', $item->day7_end_date)
                    ->where('app_id', $item->account_id)
                    ->where('user_id', $item->user_id)
                    ->where('enable', 1)
                    ->selectRaw('sum(new_user_charge) as day7_first_day_pay, sum(day7_charge) as day7_charge')
                    ->first();
            } else {
                $day7Charge = null;
            }

            $paramsData = [
                'account_id'   => $item->account_id,
                'start_date'   => $item->start_date,
                'end_date'     => $item->end_date,
                'closing_date' => $closingDate
            ];

            if($closingDate) {
                $chargeTotal = AccountDataTrend::getChargeTotal($paramsData);
                $res->pay_money = round($chargeTotal/100, 2);
            }

            if( !isset($res->cost) ){
                $item->cost               = null;
                $item->pay_money          = null;
                $item->first_day_roi      = null;
                $item->first_roi          = null;
                $item->profile            = null;
                $item->recall_rate        = null;
                $item->qw_cost            = null;
                $item->new_cost           = null;
                $item->scan_follow_total  = null;
                $item->three_days_rate    = null;
                $item->seven_days_rate    = null;
                $item->day3_charge        = null;
                $item->day7_charge        = null;
                $item->day3_first_day_pay = null;
                $item->day7_first_day_pay = null;
                $item->new_pay_uv         = null;
                $item->conversions_count  = null;
                $item->deep_conversions_count = null;
                $item->followers_number   = null;
                $item->followers_cost     = null;
                $item->repeat_fans        = null;
                $item->repeat_rate        = null;
            } else {
                // 累计消耗
                $item->cost = round($res->cost, 2);
                // 累计充值
                $item->pay_money = round($res->pay_money, 2);
                // 首日充值金额
                $item->first_day_pay = round($res->first_day_pay, 2);
                // 首日roi
                $item->first_day_roi = $res->cost > 0 ? round($res->first_day_pay / $res->cost, 2) : null;
                // 首单充值金额
                $item->first_charge = round($res->first_charge, 2);
                // 首单roi
                $item->first_roi = $res->cost > 0 ? round($res->first_charge / $res->cost, 2) : null;
                // 总毛利额
                $item->profile = $res->pay_money - $res->cost;
                // 回本率
                $item->recall_rate = $res->cost > 0 ? round($res->pay_money / $res->cost * 100, 2) : null;
                // 企微关注用户成本
                $item->qw_cost = $res->scan_follow_total > 0 ? round($res->cost / $res->scan_follow_total, 2) : null;
                // 企微加粉成本
                $item->followers_cost = $res->followers_number > 0 ? round($res->cost / $res->followers_number, 2) : null;
                // 实际下单人数(新用户下单人数)
                $item->new_pay_uv = $res->new_pay_uc;
                // 新用户下单成本
                $item->new_cost = $res->new_pay_uc > 0 ? round($res->cost / $res->new_pay_uc, 2) : null;
                // 企微关注数(企微粉丝人数)
                $item->scan_follow_total = $res->scan_follow_total;
                // 企微加粉人数
                $item->followers_number = $res->followers_number;
                // 回传下单人数(目标转化量)
                $item->conversions_count = $res->conversions_count;
                // 下单人数（深度目标转化量）
                $item->deep_conversions_count = $res->deep_conversions_count;
                // 复粉人数
                $item->repeat_fans = $res->repeat_fans;
                // 复粉率
                $item->repeat_rate = $res->followers_number > 0 ? round($res->repeat_fans / $res->followers_number * 100, 2) : null;
                // 3天倍率
                $item->three_days_rate = isset($day3Charge->day3_first_day_pay) && $day3Charge->day3_first_day_pay > 0
                    && isset($day3Charge->day3_charge) ? round($day3Charge->day3_charge / $day3Charge->day3_first_day_pay, 2) : "0";
                // 3天充值
                $item->day3_charge = isset($day3Charge->day3_charge) ? round($day3Charge->day3_charge, 2) : 0;
                // 3天首日充值
                $item->day3_first_day_pay = isset($day3Charge->day3_first_day_pay) ? round($day3Charge->day3_first_day_pay, 2) : 0;
                // 7天倍率
                $item->seven_days_rate = isset($day7Charge->day7_first_day_pay) && $day7Charge->day7_first_day_pay > 0
                    && isset($day7Charge->day7_charge) ? round($day7Charge->day7_charge / $day7Charge->day7_first_day_pay, 2) : "0";
                // 7天充值
                $item->day7_charge = isset($day7Charge->day7_charge) ? round($day7Charge->day7_charge, 2) : 0;
                // 7天首日充值
                $item->day7_first_day_pay = isset($day7Charge->day7_first_day_pay) ? round($day7Charge->day7_first_day_pay, 2) : 0;
            }

            #投手
            $item->user_name = $users->get($item->user_id) ?? null;
            #剧集
            $item->playletTitle = $playlets->get($item->drama_id) ?? null;
        }
        return ;
    }
}