playlet/app/Service/StatisticsSe...

<?php

namespace App\Service;


use App\Log;
use App\Models\AdqAccountTrendData;
use App\Models\AdqUser;
use App\Models\Customer;
use App\Models\DjOrder;
use App\Models\CustomerDetails;
use App\Models\CustomerDayReport;
use App\Models\AuthorizeCorp;
use App\Models\DjUser;
use App\Models\Es\AccountDataTrend;
use App\Models\OfficialWebUserActionSetId;
use App\Models\TencentAdDailyReport;
use App\Models\WxOfficialAccountFanRecord;
use App\Models\OfficialAccount;
use App\Models\PopularizPlayletPlans;
use App\Models\PopularizeMoments;
use App\Models\PopularizeAppids;
use App\Models\CorpMapping;
use App\Models\DramaUserRela;
use App\Models\PlayletTrendStatistics;
use App\Models\DramaSeries;
use App\Models\ActiveFansData;
use App\Models\CustomerHourRecord;
use App\RedisModel;
use App\Models\System\Users;
use App\Models\System\AdminManageCorp;

class StatisticsService
{
    const OPERATE_DAY_RETRIEVE = 'Playlet::NewOperateDayRetrieveRds';
    const ADQ_OPERATE_DAY_RETRIEVE = 'Playlet::AdqOperateDayRetrieveRds';

    public static function custTotal($corpid)
    {
        $start = date('Y-m-d');
        $last_time = date('Y-m-d H:i:s', strtotime('-1 day')); //昨日同时段
        $last_day = date('Y-m-d', strtotime('-1 day')); //昨日起始时间

        $cust_total_info = CustomerDetails::suffix($corpid)
            ->where('corpid', $corpid)
            ->where('enable', 1)
            ->selectRaw('count(distinct(external_userid)) as cust_total')
            ->first();
        $cust_total_uc = $cust_total_info->cust_total ?? 0;

        $cust_total_tadd = CustomerDetails::suffix($corpid)
            ->where('corpid', $corpid)
            ->where('enable', 1)
            ->where('createtime', '>', strtotime($last_time))
            ->select('external_userid')
            ->distinct()
            ->pluck('external_userid')
            ->all();

        if( empty($cust_total_tadd) ){
            $cust_total_uc_compare = 0;
        } else {
            //过滤之前在其他客服处注册的
            $cust_tadd_ago = CustomerDetails::suffix($corpid)
                ->where('corpid', $corpid)
                ->whereIn('external_userid', $cust_total_tadd)
                ->where('createtime', '<', strtotime($last_time))
                ->select('external_userid')
                ->distinct()
                ->pluck('external_userid')
                ->all();
            $cust_total_uc_compare = count($cust_total_tadd) - count($cust_tadd_ago);
        }

        $cust_new_tadd = CustomerDetails::suffix($corpid)
            ->where('corpid', $corpid)
            ->where('enable', 1)
            ->where('createtime', '>', strtotime($start))
            ->select('external_userid')
            ->distinct()
            ->pluck('external_userid')
            ->all();

        if( empty($cust_new_tadd) ){
            $cust_add_uc = 0;
        } else {
            //过滤之前在其他客服处注册的
            $cust_new_ago = CustomerDetails::suffix($corpid)
                ->where('corpid', $corpid)
                ->whereIn('external_userid', $cust_new_tadd)
                ->where('createtime', '<', strtotime($start))
                ->select('external_userid')
                ->distinct()
                ->pluck('external_userid')
                ->all();
            $cust_add_uc = count($cust_new_tadd) - count($cust_new_ago);
        }

        $last_cust_new_tadd = CustomerDetails::suffix($corpid)
            ->where('corpid', $corpid)
            ->where('enable', 1)
            ->where('createtime', '>=', strtotime($last_day))
            ->where('createtime', '<=', strtotime($last_time))
            ->select('external_userid')
            ->distinct()
            ->pluck('external_userid')
            ->all();

        if( empty($last_cust_new_tadd) ){
            $last_cust_add_uc = 0;
        } else {
            //过滤之前在其他客服处注册的
            $last_cust_new_ago = CustomerDetails::suffix($corpid)
                ->where('corpid', $corpid)
                ->whereIn('external_userid', $last_cust_new_tadd)
                ->where('createtime', '<', strtotime($last_day))
                ->select('external_userid')
                ->distinct()
                ->pluck('external_userid')
                ->all();
            $last_cust_add_uc = count($last_cust_new_tadd) - count($last_cust_new_ago);
        }

        $cust_add_uc_compare = $cust_add_uc - $last_cust_add_uc;

        $cust_tloss = CustomerDetails::suffix($corpid)
            ->where('loss_time', '>=', $start)
            ->where('enable', 0)
            ->where('corpid', $corpid)
            ->select('external_userid')
            ->distinct()
            ->pluck('external_userid')
            ->all();
        #过滤真正流失的
        if( empty($cust_tloss) ){
            $cust_loss_uc = 0;
        } else {
            $cust_noloss = CustomerDetails::suffix($corpid)
                ->where('enable', 1)
                ->where('corpid', $corpid)
                ->whereIn('external_userid', $cust_tloss)
                ->select('external_userid')
                ->distinct()
                ->pluck('external_userid')
                ->all();
            $cust_loss_uc = count($cust_tloss) - count($cust_noloss);
        }

        $last_cust_tloss = CustomerDetails::suffix($corpid)
            ->where('loss_time', '>=', $last_day)
            ->where('loss_time', '<=', $last_time)
            ->where('enable', 0)
            ->where('corpid', $corpid)
            ->select('external_userid')
            ->distinct()
            ->pluck('external_userid')
            ->all();
        #过滤真正流失的
        if( empty($last_cust_tloss) ){
            $last_cust_loss_uc = 0;
        } else {
            $last_cust_tloss = array_diff($last_cust_tloss, $cust_tloss); //若昨日时段流失客户同时在今日也有流失，说明昨日没真正流失，要排除

            $last_cust_noloss = CustomerDetails::suffix($corpid)
                ->where('enable', 1)
                ->where('corpid', $corpid)
                ->whereIn('external_userid', $last_cust_tloss)
                ->select('external_userid')
                ->distinct()
                ->pluck('external_userid')
                ->all();
            $last_cust_loss_uc = count($last_cust_tloss) - count($last_cust_noloss);
        }

        $cust_loss_uc_compare = $cust_loss_uc - $last_cust_loss_uc;

        //先查关联公众号
        $official_account_uc = null;
        $official_account_uc_compare = null;
        $appids = OfficialAccount::where('corp_id', $corpid)->pluck('mp_app_id')->all(); 
        if( !empty($appids) ){
            $official_info = WxOfficialAccountFanRecord::whereIn('app_id', $appids)
                ->where('record_at', date('Y-m-d H:00:00', strtotime('-1 hour') ))
                ->selectRaw('sum(follow_uv) as follow_uv')
                ->first();
            $official_account_uc = $official_info->follow_uv ?? null;// 公众号关注数

            //昨日同时段关注数
            $lastday_h = date('Y-m-d H:00:00', strtotime('-1 day'));
            $last_official = WxOfficialAccountFanRecord::whereIn('app_id', $appids)
                ->where('record_at', $lastday_h)
                ->selectRaw('sum(follow_uv) as follow_uv')
                ->first();
            $last_official_account_uc = $last_official->follow_uv ?? null;;// 昨天同时段公众号关注数
            #公众号关注对比
            $official_account_uc_compare = $official_account_uc - $last_official_account_uc;
        }

        $cust_pay_uc = null;
        $cust_pay_amount = null; //付费金额
        $cust_pay = DjOrder::where('system_corpid', $corpid)
            ->where('pay_status', 1)
            ->where('is_ad_user', 1)
            ->where('order_pay_time', '>=', strtotime($start).'000')
            ->where('enable', 1)
            ->selectRaw('count(distinct(unionid)) as cust_uc, sum(pay_money) as pay_amount')
            ->first();

        if( isset($cust_pay->cust_uc) ){
            $cust_pay_uc = $cust_pay->cust_uc;
            $cust_pay_amount = round($cust_pay->pay_amount/10000, 2);
        }
        //昨日同时段付费
        $last_cust_pay_uc = null;
        $last_cust_pay_amount = null; //付费金额
        $last_cust_pay = DjOrder::where('system_corpid', $corpid)
            ->where('pay_status', 1)
            ->where('is_ad_user', 1)
            ->where('order_pay_time', '>=', strtotime($last_day).'000')
            ->where('order_pay_time', '<=', strtotime($last_time).'000')
            ->where('enable', 1)
            ->selectRaw('count(distinct(unionid)) as cust_uc, sum(pay_money) as pay_amount')
            ->first();

        if( isset($last_cust_pay->cust_uc) ){
            $last_cust_pay_uc = $last_cust_pay->cust_uc;
            $last_cust_pay_amount = round($last_cust_pay->pay_amount/10000, 2);
        }

        $cust_pay_uc_compare = $cust_pay_uc - $last_cust_pay_uc;
        $cust_pay_amount_compare = $cust_pay_amount - $last_cust_pay_amount;

        $result = compact('cust_total_uc', 'cust_total_uc_compare', 'cust_add_uc', 'cust_add_uc_compare', 'cust_loss_uc', 'cust_loss_uc_compare', 'official_account_uc', 'official_account_uc_compare', 'cust_pay_uc', 'cust_pay_uc_compare', 'cust_pay_amount', 'cust_pay_amount_compare');

        return $result;
    }

    public static function custTrends($corpid, $start, $end, $user_id=null){
        if(!$user_id){
            $type = 0;
        } else {
            $type = 1;
        }

        $res = CustomerDayReport::where('corpid', $corpid)
            ->where('idate', '>=', $start)
            ->where('idate', '<=', $end)
            ->where('type', $type)
            ->where(function($query) use($user_id){
                if($user_id) $query->where('user_id', $user_id);
            })
            ->orderBy('idate', 'asc')
            ->get();
            
        return $res;
    }

    public static function playletPopulariz($principalName, $mpAppId, $start, $end, $playletId, $operators, $page, $pagesize)
    {
        $mpAppIds = null;
        if($principalName){
            $mpAppIds = PopularizeAppids::where('principalName', $principalName)->pluck('mpAppId')->all();
            if( empty($mpAppIds) ){
                return [[], 0];
            }
        }

        $offset = ($page-1) * $pagesize;
        $listQuery = PopularizPlayletPlans::where(function($query) use($start, $end, $playletId, $operators, $mpAppId, $mpAppIds){
                if($start) $query->where('create_time', '>=', $start);
                if($end) $query->where('create_time', '<=', $end.' 23:59:59');
                if($playletId) $query->where('playletId', '=', $playletId);
                if($operators) $query->where('operators', '=', $operators);
                if($mpAppId) $query->where('mpAppId', $mpAppId);
                if($mpAppIds) $query->whereIn('mpAppId', $mpAppIds);
            });

        $count = $listQuery->count();
        if(!$count){
            return [[], 0];
        }
        $list = $listQuery->orderBy('create_time', 'desc')
            ->offset($offset)
            ->limit($pagesize)
            ->get();

        return [$list, $count];
    }

    public static function momentsPopulariz($principalName, $mpAppId, $start, $end, $playletId, $operators, $page, $pagesize)
    {
        $mpAppIds = null;
        if($principalName){
            $mpAppIds = PopularizeAppids::where('principalName', $principalName)->pluck('mpAppId')->all();
            if( empty($mpAppIds) ){
                return [[], 0];
            }
        }

        $offset = ($page-1) * $pagesize;
        $listQuery = PopularizeMoments::where(function($query) use($start, $end, $playletId, $operators, $mpAppId, $mpAppIds){
                if($start) $query->where('create_time', '>=', $start);
                if($end) $query->where('create_time', '<=', $end.' 23:59:59');
                if($playletId) $query->where('playletId', '=', $playletId);
                if($operators) $query->where('operators', '=', $operators);
                if($mpAppId) $query->where('mpAppId', $mpAppId);
                if($mpAppIds) $query->whereIn('mpAppId', $mpAppIds);
            });

        $count = $listQuery->count();
        if(!$count){
            return [[], 0];
        }
        $list = $listQuery->orderBy('create_time', 'desc')
            ->offset($offset)
            ->limit($pagesize)
            ->get();

        return [$list, $count];
    }

    public static function corpidToMpAppId($corpid)
    {
        return CorpMapping::where('corpid', $corpid)->value('platform_corpid');
    }

    public static function popularizAccounts($type)
    {
        $list = PopularizeAppids::where(function($query) use($type){
            if($type==1) $query->where('serviceType', 10); 
            if($type==2) $query->where('serviceType', 2);
            if(!$type) $query->whereIn('serviceType', [10, 2]);
        })
        ->where('enable', 1)
        ->select('mpAppId', 'nickName', 'serviceType')
        ->get();

        return $list;
    }

    public static function popularizPlaylets($mpAppId, $type)
    {
        if(!$type){
            //朋友圈短剧
            $list = PopularizeMoments::where('mpAppId', $mpAppId)
                ->where('playletTitle','>', '')
                ->select('playletId', 'playletTitle')
                ->groupBy('playletId')
                ->get();

            return $list;
        } else {
            $list = PopularizPlayletPlans::where('mpAppId', $mpAppId)
                ->where('playletTitle','>', '')
                ->select('playletId', 'playletTitle')
                ->groupBy('playletId')
                ->get();

            return $list;
        }
    }

    public static function popularizCompanys($type)
    {    
        $list = PopularizeAppids::select('principalName')
            ->distinct()
            ->where('principalName', '>', '')
            ->where(function($query) use($type){
                if($type==1) $query->where('serviceType', 10); 
                if($type==2) $query->where('serviceType', 2);
                if(!$type) $query->whereIn('serviceType', [10, 2]);
            })
            ->get();

        return $list;
        
    }

    public static function operateDayRetrieve($app_id, $start, $end, $page, $pagesize, $drama_id, $sysGroupId)
    {   
        $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');//当年一月

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

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

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

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

        $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::getSysGroupMpAppIdList($sysGroupId, 'StatisticsService.operateDayRetrieve');
        if(empty($mpAppIdList)) {
            return [
                'list' => [],
                'total'=> 0,
                'retrieveColumns' => null
            ];
        }

        //短剧筛选，获取对应公众号，时间
        if($drama_id){
            $drama_info = DramaUserRela::query()
                ->where('drama_id', $drama_id)
                ->where(function($query) use($app_id){
                    if($app_id) $query->where('app_id', $app_id);
                })
                ->where('sys_group_id', $sysGroupId)
                ->get();
            if( $drama_info->isEmpty() ){
                return [
                    'list' => [],
                    'total'=> 0,
                    'retrieveColumns' => null
                ];
            }

            return self::operateDayRetrieveDrama($drama_info, $start, $end, $retrievePart, $retrieveColumns, $page,
                $pagesize, $mpAppIdList);
        }

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

        if(empty($app_id)) {
            $rds_key = StatisticsService::OPERATE_DAY_RETRIEVE . $sysGroupId;
        } else {
            $rds_key = StatisticsService::OPERATE_DAY_RETRIEVE . $app_id;
        }

        $rdsData = RedisModel::get($rds_key);
        if($rdsData){
            $rdsData = json_decode($rdsData, true);
        }

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

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

        $np_num = $total<$pagesize ? $total : $pagesize;
        $pend = date('Y-m-d', strtotime($end .' -'.$offset.' day'));
        $pstart = date('Y-m-d', strtotime($pend .' -'.($np_num-1).' day'));

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

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

        $data = array();
        while($pstart<=$pend){

            if( isset($rdsData[$pend]) ){
                $data[] = $rdsData[$pend];
            } else {

                $retrieveInfo = $retrievePart;
                #查日报
                $cost = null;
                $fan_new = null;
                $dayReport = TencentAdDailyReport::query()
                    ->where('ref_date', $pend)
                    ->where(function($query) use($sysGroupId, $mpAppIdList, $app_id){
                        if($app_id) $query->where('app_id', $app_id);
                        if(!empty($sysGroupId)) $query->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);
                    $fan_new = $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)
                    //->where('order_source', 1)
                    ->where(function($query) use($sysGroupId, $mpAppIdList, $app_id){
                        if($app_id) $query->where('bind_app_id', $app_id);
                        if(!empty($sysGroupId)) $query->whereIn('bind_app_id', $mpAppIdList);
                    })
                    ->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)
//                    ->whereNotIn('bind_app_id', $mpAppIdList)
                    ->where(function($query) use($app_id, $sysGroupId){
//                        if($app_id) $query->where('bind_app_id', $app_id);
                        if($sysGroupId) $query->where('sys_group_id', $sysGroupId);
                    })->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==$pend ){
                            //新用户
                            $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(substr($pay_date,0,4)==$lyear){
                                    $retrieveInfo[$lyear] += $order->pay_money;
                                }
                            }
                        } else {
                            if($pay_date>=$dymonth){
                                //今年的
                                $mon_key = substr($pay_date, 0, 7).'-01';
                                $retrieveInfo[$mon_key] += $order->pay_money;
                            } else {
                                //往年
                                if(substr($pay_date,0,4)==$lyear){
                                    $retrieveInfo[$lyear] += $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 : null;
                $other_pay_money = isset($otherOrders->pay_money) ? round($otherOrders->pay_money/10000, 2) : null;

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

                $data[] = [
                    'date' => $pend,
                    '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,
                ]; 

            }   

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

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

    public static function operateDayRetrieveSecond($app_id, $start, $end, $page, $pagesize, $drama_id, $sysGroupId)
    {
        $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('2022-01-01');//当年一月

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

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

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

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

        $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::getSysGroupMpAppIdList($sysGroupId, 'StatisticsService.operateDayRetrieve');
        if(empty($mpAppIdList)) {
            return [
                'list' => [],
                'total'=> 0,
                'retrieveColumns' => null
            ];
        }

        //短剧筛选，获取对应公众号，时间
        if($drama_id){
            $drama_info = DramaUserRela::query()
                ->where('drama_id', $drama_id)
                ->where(function($query) use($app_id){
                    if($app_id) $query->where('app_id', $app_id);
                })
                ->where('sys_group_id', $sysGroupId)
                ->get();
            if( $drama_info->isEmpty() ){
                return [
                    'list' => [],
                    'total'=> 0,
                    'retrieveColumns' => null
                ];
            }

            return self::operateDayRetrieveDrama($drama_info, $start, $end, $retrievePart, $retrieveColumns, $page,
                $pagesize, $mpAppIdList);
        }

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

//        if(empty($app_id)) {
//            $rds_key = StatisticsService::OPERATE_DAY_RETRIEVE . $sysGroupId;
//        } else {
//            $rds_key = StatisticsService::OPERATE_DAY_RETRIEVE . $app_id;
//        }
//
//        $rdsData = RedisModel::get($rds_key);
//        if($rdsData){
//            $rdsData = json_decode($rdsData, true);
//        }
        $rdsData = [];

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

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

        $np_num = $total<$pagesize ? $total : $pagesize;
        $pend = date('Y-m-d', strtotime($end .' -'.$offset.' day'));
        $pstart = date('Y-m-d', strtotime($pend .' -'.($np_num-1).' day'));

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

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

        $data = array();
        while($pstart<=$pend){

            if( isset($rdsData[$pend]) ){
                $data[] = $rdsData[$pend];
            } else {

                $retrieveInfo = $retrievePart;
                #查日报
                $cost = null;
                $fan_new = null;
                $dayReport = TencentAdDailyReport::query()
                    ->where('ref_date', $pend)
                    ->where(function($query) use($sysGroupId, $mpAppIdList, $app_id){
                        if($app_id) $query->where('app_id', $app_id);
                        if(!empty($sysGroupId)) $query->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);
                    $fan_new = $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)
                    //->where('order_source', 1)
                    ->where(function($query) use($sysGroupId, $mpAppIdList, $app_id){
                        if($app_id) $query->where('bind_app_id', $app_id);
                        if(!empty($sysGroupId)) $query->whereIn('bind_app_id', $mpAppIdList);
                    })
                    ->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)
//                    ->whereNotIn('bind_app_id', $mpAppIdList)
                    ->where(function($query) use($app_id, $sysGroupId){
//                        if($app_id) $query->where('bind_app_id', $app_id);
                        if($sysGroupId) $query->where('sys_group_id', $sysGroupId);
                    })->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==$pend ){
                            //新用户
                            $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(substr($pay_date,0,4)==$lyear){
                                    $retrieveInfo[$lyear] += $order->pay_money;
                                }
                            }
                        } else {
                            if($pay_date>=$dymonth){
                                //今年的
                                $mon_key = substr($pay_date, 0, 7).'-01';
                                $retrieveInfo[$mon_key] += $order->pay_money;
                            } else {
                                //往年
                                if(substr($pay_date,0,4)==$lyear){
                                    $retrieveInfo[$lyear] += $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 : null;
                $other_pay_money = isset($otherOrders->pay_money) ? round($otherOrders->pay_money/10000, 2) : null;

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

                $data[] = [
                    'date' => $pend,
                    '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,
                ];

            }

            $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');//当年一月
        $lyear = date('Y') - 1;
        $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);
                    })
//                    ->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);
                    $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);
                    })
//                    ->whereIn('bind_app_id', $mpAppIdList)
                    ->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(substr($pay_date,0,4)==$lyear){
                                    $retrieveInfo[$lyear] += $order->pay_money;
                                }
                            }
                        } else {
                            if($pay_date>=$dymonth){
                                //今年的
                                $mon_key = substr($pay_date, 0, 7).'-01';
                                $retrieveInfo[$mon_key] += $order->pay_money;
                            } else {
                                //往年
                                if(substr($pay_date,0,4)==$lyear){
                                    $retrieveInfo[$lyear] += $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;
//                    $data[$edate]['other_pay_uc'] += $other_pay_uv;
//                    $data[$edate]['other_pay_money'] += $other_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,
//                        'other_pay_uc' => $other_pay_uv,
//                        'other_pay_money' => $other_pay_money,
                    ];

                } 

                $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 operateDayRetrieveAdq($start, $end, $page, $pagesize, $sysGroupId)
    {
        $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');//当年一月

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

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

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

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

        $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 = OfficialWebUserActionSetId::getSysGroupAccountIdList($sysGroupId, 'StatisticsService.operateDayRetrieveAdq');
        if(empty($adqAccountIdList)) {
            return [
                'list' => [],
                'total'=> 0,
                'retrieveColumns' => null
            ];
        }

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

        $rds_key = StatisticsService::ADQ_OPERATE_DAY_RETRIEVE . $sysGroupId;
        $rdsData = RedisModel::get($rds_key);
        if($rdsData){
            $rdsData = json_decode($rdsData, true);
        }

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

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

        $np_num = $total<$pagesize ? $total : $pagesize;
        $pend = date('Y-m-d', strtotime($end .' -'.$offset.' day'));
        $pstart = date('Y-m-d', strtotime($pend .' -'.($np_num-1).' day'));

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

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

        $data = array();
        while($pstart<=$pend){

            if( isset($rdsData[$pend]) ){
                $data[] = $rdsData[$pend];
            } else {

                $retrieveInfo = $retrievePart;
                #查日报
                $cost = null;
                $fan_new = 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);
                    $fan_new = $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)
                    ->where(function($query) use($sysGroupId, $adqAccountIdList){
                        if(!empty($sysGroupId)) $query->whereIn('adq_account_id', $adqAccountIdList);
//                        if(!empty($sysGroupId)) $query->where('sys_group_id', $sysGroupId);
                    })
                    ->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(function($query) use($sysGroupId){
                        if($sysGroupId) $query->where('sys_group_id', $sysGroupId);
                    })->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==$pend ){
                            //新用户
                            $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(substr($pay_date,0,4)==$lyear){
                                    $retrieveInfo[$lyear] += $order->pay_money;
                                }
                            }
                        } else {
                            if($pay_date>=$dymonth){
                                //今年的
                                $mon_key = substr($pay_date, 0, 7).'-01';
                                $retrieveInfo[$mon_key] += $order->pay_money;
                            } else {
                                //往年
                                if(substr($pay_date,0,4)==$lyear){
                                    $retrieveInfo[$lyear] += $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 : null;
                $other_pay_money = isset($otherOrders->pay_money) ? round($otherOrders->pay_money/10000, 2) : null;

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

                $data[] = [
                    'date' => $pend,
                    '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,
                ];

            }

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

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

    public static function operateDayRetrieveAdqSecond($start, $end, $page, $pagesize, $sysGroupId)
    {
        $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('2022-01-01');//当年一月

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

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

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

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

        $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 = OfficialWebUserActionSetId::getSysGroupAccountIdList($sysGroupId, 'StatisticsService.operateDayRetrieveAdq');
        if(empty($adqAccountIdList)) {
            return [
                'list' => [],
                'total'=> 0,
                'retrieveColumns' => null
            ];
        }

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

//        $rds_key = StatisticsService::ADQ_OPERATE_DAY_RETRIEVE . $sysGroupId;
//        $rdsData = RedisModel::get($rds_key);
//        if($rdsData){
//            $rdsData = json_decode($rdsData, true);
//        }
        $rdsData = [];

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

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

        $np_num = $total<$pagesize ? $total : $pagesize;
        $pend = date('Y-m-d', strtotime($end .' -'.$offset.' day'));
        $pstart = date('Y-m-d', strtotime($pend .' -'.($np_num-1).' day'));

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

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

        $data = array();
        while($pstart<=$pend){
            if( isset($rdsData[$pend]) ){
                $data[] = $rdsData[$pend];
            } else {

                $retrieveInfo = $retrievePart;
                #查日报
                $cost = null;
                $fan_new = 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);
                    $fan_new = $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)
                    ->where(function($query) use($sysGroupId, $adqAccountIdList){
                        if(!empty($sysGroupId)) $query->whereIn('adq_account_id', $adqAccountIdList);
//                        if(!empty($sysGroupId)) $query->where('sys_group_id', $sysGroupId);
                    })
                    ->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(function($query) use($sysGroupId){
                        if($sysGroupId) $query->where('sys_group_id', $sysGroupId);
                    })->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==$pend ){
                            //新用户
                            $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(substr($pay_date,0,4)==$lyear){
                                    $retrieveInfo[$lyear] += $order->pay_money;
                                }
                            }
                        } else {
                            if($pay_date>=$dymonth){
                                //今年的
                                $mon_key = substr($pay_date, 0, 7).'-01';
                                $retrieveInfo[$mon_key] += $order->pay_money;
                            } else {
                                //往年
                                if(substr($pay_date,0,4)==$lyear){
                                    $retrieveInfo[$lyear] += $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 : null;
                $other_pay_money = isset($otherOrders->pay_money) ? round($otherOrders->pay_money/10000, 2) : null;

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

                $data[] = [
                    'date' => $pend,
                    '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,
                ];

            }

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

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

    public static function throwPersonData($start, $end, $user_id, $drama_id, $sysGroupId, $adminId, $isSystemAdmin
        , $appId, $launchAccountId, $closingDate)
    {
        $mpAccountList = OfficialAccount::getAccountMpAppIdList($adminId, $sysGroupId, $isSystemAdmin);
        $adqAccountList = PitcherService::adqAccountListForUser($adminId, $sysGroupId, $isSystemAdmin);

        $launchAccountIdList = [];
        if($launchAccountId) {
            $launchAccountIdList = [$launchAccountId];
        } else if($appId) {
            # 当前绑定关系查询
            $launchAccountIdList = AdqUser::getAccountIdList($appId);
            # 历史绑定关系查询
            $historyLaunchAccountIdList = PlayletTrendStatistics::getAccountListByAppId($appId, $start, $end);
            $launchAccountIdList = array_values(array_unique(array_merge($launchAccountIdList, $historyLaunchAccountIdList)));
        }

        if($closingDate && $closingDate < $end) {
            $end = $closingDate;
        }

        #先筛选剧集
        $list = DramaUserRela::query()
            ->where('start_date', '<=', $end)
            ->where('end_date', '>=', $start)
            ->where(function($query) use($user_id, $drama_id, $appId, $launchAccountId, $launchAccountIdList){
                if($user_id) $query->where('user_id', $user_id);
                if($drama_id) $query->where('drama_id', $drama_id);
                if($appId || $launchAccountId) $query->whereIn('account_id', $launchAccountIdList);
            })
            ->where(function ($query) use($mpAccountList, $adqAccountList) {
                $query->whereIn('app_id', $mpAccountList)->orWhereIn('account_id', $adqAccountList);
            })
            ->where('enable', 1)
            ->where('sys_group_id', $sysGroupId)
            ->select('drama_id', 'user_id', 'app_id', 'account_id', 'start_date', 'end_date')
            ->get();

        if($list->isEmpty()){
            return null;
        }

        $data = [
            'cost'               => 0,
            'pay_money'          => 0,
            'first_day_pay'      => 0,
            'first_charge'       => 0,
            'scan_follow_total'  => 0,
            'new_pay_uc'         => 0,
            'day3_charge'        => 0,
            'day7_charge'        => 0,
            'day3_first_day_pay' => 0,
            'day7_first_day_pay' => 0,
            'followers_number'   => 0,
            'repeat_fans'        => 0,
        ];

        $accountIdList = $list->pluck('account_id')->toArray();
        $accountIdList = array_filter($accountIdList);
        $relationList = AdqUser::getAppIdList($accountIdList);
        $accounts = OfficialAccount::query()->pluck('mp_name', 'mp_app_id');

        foreach($list as $key => $item){
            #查对应时间信息
            $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)) {
                $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->app_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(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->app_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->app_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->app_id,
                    'start_date'   => $item->start_date,
                    'end_date'     => $item->end_date,
                    'closing_date' => $closingDate
                ];

            } else {
                $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(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;
                }

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

                #adq账号
                if(!empty($bindInformation['bind_app_list'])) {
                    $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;
                    $item->official_account_id = !empty($officialAccountId) ? [$officialAccountId] : [];
                }

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

                $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               = 0;
                $item->pay_money          = 0;
                $item->first_day_pay      = 0;
                $item->first_charge       = 0;
                $item->scan_follow_total  = 0;
                $item->new_pay_uc         = 0;
                $item->day3_charge        = 0;
                $item->day7_charge        = 0;
                $item->day3_first_day_pay = 0;
                $item->day7_first_day_pay = 0;
                $item->followers_number   = 0;
                $item->repeat_fans        = 0;
            } else {
                $item->cost               = $res->cost;
                $item->pay_money          = $res->pay_money;
                $item->first_day_pay      = $res->first_day_pay;
                $item->first_charge       = $res->first_charge;
                $item->scan_follow_total  = $res->scan_follow_total;
                $item->new_pay_uc         = $res->new_pay_uc;
                $item->day3_charge        = $day3Charge->day3_charge ?? 0;
                $item->day7_charge        = $day7Charge->day7_charge ?? 0;
                $item->day3_first_day_pay = $day3Charge->day3_first_day_pay ?? 0;
                $item->day7_first_day_pay = $day7Charge->day7_first_day_pay ?? 0;
                $item->followers_number   = $res->followers_number??0;
                $item->repeat_fans        = $res->repeat_fans??0;
            }

            $data['cost']               += $item->cost;
            $data['pay_money']          += $item->pay_money;
            $data['first_day_pay']      += $item->first_day_pay;
            $data['first_charge']       += $item->first_charge;
            $data['scan_follow_total']  += $item->scan_follow_total;
            $data['followers_number']   += $item->followers_number;
            $data['repeat_fans']        += $item->repeat_fans;
            $data['new_pay_uc']         += $item->new_pay_uc;
            $data['day3_charge']        += $item->day3_charge;
            $data['day7_charge']        += $item->day7_charge;
            $data['day3_first_day_pay'] += $item->day3_first_day_pay;
            $data['day7_first_day_pay'] += $item->day7_first_day_pay;
        }

        // 累计消耗
        $data['cost'] = round($data['cost'], 2);
        // 累计充值
        $data['pay_money'] = round($data['pay_money'], 2);
        // 每日新用户充值金额之和
        $data['first_day_pay'] = round($data['first_day_pay'], 2);
        // 首日roi
        $data['first_day_roi'] = $data['cost']>0 ? round($data['first_day_pay']/$data['cost'], 2) : null;
        // 新用户首单充值金额
        $data['first_charge'] = round($data['first_charge'], 2);
        // 新用户首单roi
        $data['first_roi'] = $data['cost'] >0 ? round($data['first_charge']/$data['cost'], 2) : null;
        // 总毛利额
        $data['profile'] = $data['pay_money'] - $data['cost'];
        // 回本率
        $data['recall_rate'] = $data['cost']>0 ? round($data['pay_money']/$data['cost']*100, 2) : null;
        // 企微关注成本
        $data['qw_cost'] = $data['scan_follow_total']>0 ? round($data['cost']/$data['scan_follow_total'], 2) : null;
        // 企微加粉成本
        $data['followers_cost'] = $data['followers_number'] > 0 ? round($data['cost'] / $data['followers_number'], 2) : null;
        // 新注册用户成本
        $data['new_cost'] = $data['new_pay_uc']>0 ? round($data['cost']/$data['new_pay_uc'], 2) : null;
        // 3天倍率
        $data['three_days_rate'] = $data['day3_first_day_pay']>0 ? round($data['day3_charge'] / $data['day3_first_day_pay'], 2) : "0";
        // 3天充值金额
        $data['day3_charge'] = round($data['day3_charge'], 2);
        // 3天首日充值
        $data['day3_first_day_pay'] = round($data['day3_first_day_pay'], 2);
        // 7天倍率
        $data['seven_days_rate'] = $data['day7_first_day_pay']>0 ? round($data['day7_charge'] / $data['day7_first_day_pay'], 2) : "0";
        // 7天充值金额
        $data['day7_charge'] = round($data['day7_charge'], 2);
        // 7天首日充值金额
        $data['day7_first_day_pay'] = round($data['day7_first_day_pay'], 2);
        // 留存复粉率
        $data['repeat_rate'] = $data['followers_number'] > 0 ? round($data['repeat_fans'] / $data['followers_number'] * 100, 2) : '0';

        return $data;
    }

    public static function throwPersonList($start, $end, $user_id, $drama_id, $page, $pageSize, $sysGroupId, $sortField
        , $adminId, $isSystemAdmin, $appId, $launchAccountId, $closingDate)
    {
        $mpAccountList = OfficialAccount::getAccountMpAppIdList($adminId, $sysGroupId, $isSystemAdmin);
        $adqAccountList = PitcherService::adqAccountListForUser($adminId, $sysGroupId, $isSystemAdmin);

        $launchAccountIdList = [];
        if($launchAccountId) {
            $launchAccountIdList = [$launchAccountId];
        } else if($appId) {
            # 当前绑定关系查询
            $launchAccountIdList = AdqUser::getAccountIdList($appId);
            # 历史绑定关系查询
            $historyLaunchAccountIdList = PlayletTrendStatistics::getAccountListByAppId($appId, $start, $end);
            $launchAccountIdList = array_values(array_unique(array_merge($launchAccountIdList, $historyLaunchAccountIdList)));
        }

        if('date' != $sortField || !empty($appId)) {
            # 由于公众号筛选时的日期与列表中实际日期并不一致，因此需要再列表中将不符合条件的数据过滤掉，从而导致要查询所有的数据
            list($data, $count) = self::throwPersonListBySort($start, $end, $user_id, $drama_id, $page, $pageSize
                , $sysGroupId, $sortField, $mpAccountList, $adqAccountList, $appId, $launchAccountId, $launchAccountIdList
                , $closingDate);
            return [$data, $count];
        }

        if($closingDate && $closingDate < $end) {
            $end = $closingDate;
        }

        $offset = ($page-1) * $pageSize;
        #先筛选剧集
        $listQuery = DramaUserRela::query()->where('sys_group_id', $sysGroupId)
            ->where('start_date', '<=', $end)->where('end_date', '>=', $start)
            ->where(function($query) use($user_id, $drama_id, $appId, $launchAccountId, $launchAccountIdList){
                if($user_id) $query->where('user_id', $user_id);
                if($drama_id) $query->where('drama_id', $drama_id);
                if($appId || $launchAccountId) $query->whereIn('account_id', $launchAccountIdList);
            })
            ->where(function ($query) use($mpAccountList, $adqAccountList) {
                $query->whereIn('app_id', $mpAccountList)->orWhereIn('account_id', $adqAccountList);
            })->where('enable', 1);

        $total = $listQuery->count();

        $list = $listQuery->select('drama_id', 'user_id', 'app_id', 'account_id', 'start_date', 'end_date', 'disable_date')
            ->orderBy('start_date', 'desc')
            ->offset($offset)
            ->limit($pageSize)
            ->get();

        if($list->isEmpty()){
            return [[], 0];
        }

//        self::throwPersonListFormat($list, $start, $end);
        NewStatisticsService::throwPersonListFormat($list, $start, $end, $closingDate, $appId);

        return [$list,$total];
    }

    public static function throwPersonListBySort($start, $end, $user_id, $drama_id, $page, $pageSize, $sysGroupId
        , $sortField, $mpAccountList, $adqAccountList, $appId, $launchAccountId, $launchAccountIdList, $closingDate)
    {

        if($closingDate && $closingDate < $end) {
            $end = $closingDate;
        }

        $offset = ($page-1) * $pageSize;
        #先筛选剧集
        $listQuery = DramaUserRela::query()->where('sys_group_id', $sysGroupId)
            ->where('start_date', '<=', $end)->where('end_date', '>=', $start)
            ->where(function($query) use($user_id, $drama_id, $appId, $launchAccountId, $launchAccountIdList){
                if($user_id) $query->where('user_id', $user_id);
                if($drama_id) $query->where('drama_id', $drama_id);
                if($appId || $launchAccountId) $query->whereIn('account_id', $launchAccountIdList);
            })
            ->where(function ($query) use($mpAccountList, $adqAccountList) {
                $query->whereIn('app_id', $mpAccountList)
                    ->orWhereIn('account_id', $adqAccountList);
            })->where('enable', 1);

//        $total = $listQuery->count();

        $list = $listQuery->select('drama_id', 'user_id', 'app_id', 'account_id', 'start_date', 'end_date', 'disable_date')
            ->get();

        if($list->isEmpty()){
            return [[], 0];
        }

//        self::throwPersonListFormat($list, $start, $end);
        NewStatisticsService::throwPersonListFormat($list, $start, $end, $closingDate, $appId);

        // 内存中排序
        $list = json_decode(json_encode($list), 1);
        $key = array_column($list, $sortField);
        array_multisort($key, SORT_DESC, $list);
        $list = array_values($list);
        $total = count($list);

        // 分页
        $list = array_splice($list, $offset, $pageSize);

        return [$list,$total];
    }

    public static function throwPersonListFormat(&$list, $start, $end) {
        $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) : [];

        foreach($list as $item){
            #查对应时间信息
            $item->start_date = $item->start_date>$start ? $item->start_date : $start;
            $item->end_date = $item->end_date<$end ? $item->end_date : $end;

            $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)){
                $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->app_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')
                    ->first();
                #公众号
                $item->account_name = $accounts->get($item->app_id) ?? null;
                $item->official_account_name = null;

                # 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->app_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->app_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;
                }

            } else {
                $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')
                    ->first();
                #adq账号
                $item->account_name = $item->account_id;
                $relationInfo = $relationList->where('account_id', $item->account_id)->first();
                $officialAccountId = $relationInfo->app_id ?? null;
                $item->official_account_name = $accounts->get($officialAccountId) ?? null;

                # 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;
                }
            }

            if( !isset($res->cost) ){
                $item->cost = null;
                $item->pay_money = null;
                $item->first_day_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;
            } 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->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->new_cost = $res->new_pay_uc>0 ? round($res->cost/$res->new_pay_uc, 2) : null;
                // 企微关注数
                $item->scan_follow_total = $res->scan_follow_total;
                // 3天倍率
                $item->three_days_rate = !empty($day3Charge->day3_first_day_pay) && $day3Charge->day3_first_day_pay > 0 ? round($day3Charge->day3_charge / $day3Charge->day3_first_day_pay, 2) : "0";
                // 3天充值
                $item->day3_charge = round($day3Charge->day3_charge, 2);
                // 7天倍率
                $item->seven_days_rate = !empty($day7Charge->day7_first_day_pay) && $day7Charge->day7_first_day_pay > 0 ? round($day7Charge->day7_charge / $day7Charge->day7_first_day_pay, 2) : "0";
                // 7天充值
                $item->day7_charge = round($day7Charge->day7_charge, 2);
            }

            #投手
            $item->user_name = $users->get($item->user_id) ?? null;
            #剧集
            $item->playletTitle = $playlets->get($item->drama_id) ?? null;
            #客服
            $item->user_list = $accountData[$item->account_id] ?? [];
        }

        return ;
    }

    public static function officialTrendTotal($start, $end, $appId, $sysGroupId)
    {
        $mpAppIdList = OfficialAccount::getSysGroupMpAppIdList($sysGroupId, 'StatisticsService.officialTrendTotal');

        $cost_info = TencentAdDailyReport::where('enable', 1)
            ->where(function($query) use($appId, $start, $end){
                if($appId) $query->where('app_id', $appId);
                if($start) $query->where('ref_date', '>=', $start);
                if($end) $query->where('ref_date', '<=', $end);
            })
            ->whereIn('app_id', $mpAppIdList)
            ->selectRaw('sum(paid) as cost, 
                sum(scan_follow_count) as qw_fans,
                sum(follow_uv) as of_fans
            ')
            ->first();

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

        $pay_info = ActiveFansData::where('enable', 1)
            ->where(function($query) use($appId, $start, $end){
                if($appId) $query->where('app_id', $appId);
                if($start) $query->where('expense_date', '>=', $start);
                if($end) $query->where('expense_date', '<=', $end);
            })
            ->whereIn('app_id', $mpAppIdList)
            ->selectRaw('sum(active_fans) as pay_ucnt, 
                sum(pay_count) as pay_cnt,
                sum(pay_money) as pay_money
            ')
            ->first();

        $first_pay_info = ActiveFansData::where('enable', 1)
            ->where(function($query) use($appId, $start, $end){
                if($appId) $query->where('app_id', $appId);
                if($start) $query->where('expense_date', '>=', $start);
                if($end) $query->where('expense_date', '<=', $end);
            })
            ->whereIn('app_id', $mpAppIdList)
            ->whereRaw('expense_date=ref_date')
            ->selectRaw('sum(active_fans) as pay_ucnt, 
                sum(pay_count) as pay_cnt,
                sum(pay_money) as pay_money
            ')
            ->first();

        $result = array();
        $result['first_roi'] = $cost_info->cost>0 && isset($first_pay_info->pay_money) ? round($first_pay_info->pay_money/$cost_info->cost, 2) : null;
        $result['cost'] = round($cost_info->cost/100);
        $result['qw_fans'] = $cost_info->qw_fans;
        $result['qw_fans_cost'] = $cost_info->qw_fans>0 ? round($cost_info->cost/100/$cost_info->qw_fans, 2) : null;
        $result['first_pay_money'] = isset($first_pay_info->pay_money) ? round($first_pay_info->pay_money/100, 2) : null;
        $result['pay_money'] = isset($pay_info->pay_money) ? round($pay_info->pay_money/100, 2) : null;
        $result['of_fans'] = $cost_info->of_fans;
        $result['income_rate'] = $cost_info->cost>0 && isset($pay_info->pay_money) ? round($pay_info->pay_money/$cost_info->cost*100, 2) : null;
        $result['first_pay_ucnt'] = $first_pay_info->pay_ucnt ?? null;
        $result['first_pay_cnt'] = $first_pay_info->pay_cnt ?? null;
        $result['pay_ucnt'] = $pay_info->pay_ucnt ?? null;
        $result['pay_cnt'] = $pay_info->pay_cnt ?? null;
        $result['pay_ucnt_cost'] = isset($pay_info->pay_ucnt) && $pay_info->pay_ucnt>0 ? round($cost_info->cost/100/$pay_info->pay_ucnt, 2) : null;

        return $result;
    }

    public static function officialTrendList($start, $end, $appId, $page, $pagesize, $sysGroupId)
    {
        $mpAppIdList = OfficialAccount::getSysGroupMpAppIdList($sysGroupId, 'StatisticsService.officialTrendList');

        $cost_query = TencentAdDailyReport::where('enable', 1)
            ->where(function($query) use($appId, $start, $end){
                if($appId) $query->where('app_id', $appId);
                if($start) $query->where('ref_date', '>=', $start);
                if($end) $query->where('ref_date', '<=', $end);
            })
            ->whereIn('app_id', $mpAppIdList);

        #150天数据初始定义
        $day_columns = $day_arr = array();
//        for($i=0; $i<150; $i++){
        for($i=0; $i<60; $i++){
            $day_columns[$i] = 'day'. ($i+1);
            $day_arr[$i] = null;
        }

        $total = (clone $cost_query)->count();

        if($total == 0){
            return [[], 0, $day_columns];
        }

        $offset = ($page-1) * $pagesize;
        $list = $cost_query->selectRaw('ref_date, app_id, paid as cost, scan_follow_count as qw_fans, follow_uv as of_fans')
            ->orderBy('ref_date', 'desc')
            ->offset($offset)
            ->limit($pagesize)
            ->get();

        if( $list->isEmpty() ){
            return [[], 0, $day_columns];
        }

        #公众号名字
        $app_ids = $list->pluck('app_id')->toArray();
        $accounts = OfficialAccount::whereIn('mp_app_id', $app_ids)->pluck('mp_name', 'mp_app_id')->toArray();

        $data = array();
        foreach ($list as $cost_info) {
            $cost_info->app_name = $accounts[$cost_info->app_id] ?? '';

            $app_id = $cost_info->app_id;
            #当天收入信息
            $first_pay_info = ActiveFansData::where('enable', 1)
                ->where('app_id', $app_id)
                ->where('expense_date', '=', $cost_info->ref_date)
                ->where('ref_date', '=', $cost_info->ref_date)
                ->selectRaw('active_fans as pay_ucnt, 
                    pay_count as pay_cnt,
                    pay_money
                ')
                ->first();

            #到今日累计信息
            $pay_info = ActiveFansData::where('enable', 1)
                ->where('app_id', $app_id)
                ->where('expense_date', '=', $cost_info->ref_date)               
                ->selectRaw('sum(active_fans) as pay_ucnt, 
                    sum(pay_count) as pay_cnt,
                    sum(pay_money) as pay_money
                ')
                ->first();

            #查150天数据
            $day_info = $day_arr;
            $end_date = date('Y-m-d', strtotime($cost_info->ref_date.' +149 day'));
            $day_data = ActiveFansData::where('enable', 1)
                ->where('app_id', $app_id)
                ->where('expense_date', '=', $cost_info->ref_date)
                ->where('ref_date', '<=', $end_date)
                ->select('pay_money', 'pay_money_total', 'ref_date')
                ->get()
                ->toArray();
            if( !empty($day_data) ){
                foreach($day_data as $val){
                    $nd = (strtotime($val['ref_date']) - strtotime($cost_info->ref_date))/86400;

                    $new_roi = $cost_info->cost>0 ? round($val['pay_money'] / $cost_info->cost, 4) * 100 . '%' : null;
                    $total_roi = $cost_info->cost>0 ? round($val['pay_money_total'] / $cost_info->cost, 4) * 100 . '%' : null;
                    $income_times = isset($first_pay_info->pay_money) && $first_pay_info->pay_money>0 ? round($val['pay_money_total'] / $first_pay_info->pay_money, 2) : null;

                    $day_info[$nd] = [
                        'date' => $val['ref_date'],
                        'pay_money' => round($val['pay_money']/100, 2),
                        'new_roi' => $new_roi,
                        'total_roi' => $total_roi,
                        'income_times' => $income_times
                    ];
                }
            }

            $result = array();
            $result['ref_date'] = $cost_info->ref_date;
            $result['app_id'] = $cost_info->app_id;
            $result['app_name'] = $cost_info->app_name;
            
            $result['first_roi'] = $cost_info->cost>0 && isset($first_pay_info->pay_money) ? round($first_pay_info->pay_money/$cost_info->cost, 2) : null;
            $result['cost'] = round($cost_info->cost/100, 2);
            $result['qw_fans'] = $cost_info->qw_fans;
            $result['qw_fans_cost'] = $cost_info->qw_fans>0 ? round($cost_info->cost/100/$cost_info->qw_fans, 2) : null;
            $result['first_pay_money'] = isset($first_pay_info->pay_money) ? round($first_pay_info->pay_money/100, 2) : null;
            $result['pay_money'] = isset($pay_info->pay_money) ? round($pay_info->pay_money/100, 2) : null;
            $result['of_fans'] = $cost_info->of_fans;
            $result['income_rate'] = $cost_info->cost>0 && isset($pay_info->pay_money) ? round($pay_info->pay_money/$cost_info->cost*100, 2) : null;
            $result['first_pay_ucnt'] = $first_pay_info->pay_ucnt ?? null;
            $result['first_pay_cnt'] = $first_pay_info->pay_cnt ?? null;
            $result['pay_ucnt'] = $pay_info->pay_ucnt ?? null;
            $result['pay_cnt'] = $pay_info->pay_cnt ?? null;
            $result['pay_ucnt_cost'] = isset($pay_info->pay_ucnt) && $pay_info->pay_ucnt>0 ?
                round($cost_info->cost/100/$pay_info->pay_ucnt, 2) : null;
            $result['day_info'] = $day_info;

            $data[] = $result;
        }

        return [$data, $total, $day_columns];
    }

    public static function pitcherData($stDate, $enDate, $userId, $page, $pageSize, $sortField, $sysGroupId, $adminId
        , $isSystemAdmin)
    {
        $summary = [
            'sum_paid'         => 0,// 消耗
            'sum_charge'       => 0,// 累计充值
            'sum_roi'          => 0,// 首日roi
            'first_roi'        => 0,// 首日首单roi
            'sum_profit'       => 0,// 利润额
            'sum_recover_rate' => 0,// 回本率
            'sum_day_charge'   => 0,// 新用户首日充值金额
            'qw_cost'          => 0,
            'new_cost'         => 0,
            'new_pay_uc'       => 0,//首日下单人数
            'followers_cost'   => 0,// 企微实际加粉成本
            'followers_number' => 0,// 企微实际加粉人数
        ];
        $list = [];

//        $mpAppIdList = OfficialAccount::getSysGroupMpAppIdList($sysGroupId, 'StatisticsService.pitcherData');
//        $accountIdList = OfficialWebUserActionSetId::query()
//            ->where('sys_group_id', $sysGroupId)
//            ->where('enable', 1)
//            ->get();
//
//        $accountIdList = $accountIdList->isNotEmpty() ? $accountIdList->pluck('account_id')->toArray() : [];
//        $accountIdList = array_merge($accountIdList, $mpAppIdList);
        $accountIdList = PitcherService::adAccountList(null, $sysGroupId, $adminId, $isSystemAdmin);
        $accountIdList = array_column($accountIdList, 'account_id');

        // 初始化对象
        $query = PlayletTrendStatistics::query()
            ->whereBetween('ref_date', [$stDate, $enDate])
            ->where(function ($query) use ($userId, $stDate, $enDate) {
                if (!empty($userId)) $query->where('user_id', $userId);
            })
            ->where('enable', 1)
            ->whereIn('app_id', $accountIdList);

        $total = (clone $query)->select(['user_id'])->distinct()->get()->count();
        if ($total > 0) {
            // 统计汇总数据
            $summaryData = (clone $query)
                ->selectRaw('SUM(day_paid) AS sum_paid, SUM(new_user_charge) AS sum_day_charge, SUM(charge_total)'
                    .' AS sum_charge, sum(scan_follow_count) as scan_follow_total, sum(new_user_charge_uv) as new_pay_uc, '
                    .'sum(first_user_charge) as sum_first_charge, sum(followers_number) as followers_number')
                ->first();

            $summary['sum_paid']         = $summaryData->sum_paid ?? 0;
            $summary['sum_charge']       = $summaryData->sum_charge ?? 0;
            $summary['sum_day_charge']   = $summaryData->sum_day_charge ?? 0;
            $summary['sum_first_charge'] = $summaryData->sum_first_charge ?? 0;
            $summary['sum_roi']          = $summaryData->sum_paid > 0 ? round($summaryData->sum_day_charge / $summaryData->sum_paid, 2) : 0;
            $summary['first_roi']        = $summaryData->sum_paid > 0 ? round($summaryData->sum_first_charge / $summaryData->sum_paid, 2) : 0;
            $summary['sum_profit']       = round($summaryData->sum_charge - $summaryData->sum_paid, 2);
            $summary['sum_recover_rate'] = ($summaryData->sum_paid > 0 ? round(($summaryData->sum_charge / $summaryData->sum_paid) * 100, 2) : 0) . '%';
            $summary['qw_cost']          = $summaryData->scan_follow_total>0 ? round($summaryData->sum_paid/$summaryData->scan_follow_total, 2) : null;
            $summary['new_cost']         = $summaryData->new_pay_uc>0 ? round($summaryData->sum_paid/$summaryData->new_pay_uc, 2) : null;
            $summary['new_pay_uc']       = $summaryData->new_pay_uc ?? 0;
            $summary['followers_cost']   = $summaryData->followers_number>0?round($summaryData->sum_paid/$summaryData->followers_number, 2) : null;
            $summary['followers_number'] = $summaryData->followers_number ?? 0;

            // 统计列表数据
            $listData = (clone $query)
                ->select(['user_id'])
                ->selectRaw('COUNT(DISTINCT ref_date) AS date_num')
                ->selectRaw('COUNT(DISTINCT app_id) AS app_num')
                ->selectRaw('SUM(day_paid) AS day_paid')
                ->selectRaw('SUM(charge_total) AS charge_total')
                ->selectRaw('SUM(new_user_charge) AS new_user_charge_total')
                ->selectRaw('IF(SUM(day_paid) > 0,  SUM(new_user_charge) / SUM(day_paid), 0) AS roi')
                ->selectRaw('(SUM(charge_total) - SUM(day_paid)) AS profit')
                ->selectRaw('IF(SUM(day_paid) > 0, SUM(charge_total) / SUM(day_paid), 0) AS recover_rate')
                ->selectRaw('IF(SUM(scan_follow_count) > 0, SUM(day_paid) / SUM(scan_follow_count), 0) AS qw_cost')
                ->selectRaw('IF(SUM(new_user_charge_uv) > 0, SUM(day_paid) / SUM(new_user_charge_uv), 0) AS new_cost')
                ->selectRaw('IF(SUM(day_paid) > 0, SUM(first_user_charge) / SUM(day_paid), 0) as first_roi')
                ->selectRaw('IF(SUM(followers_number) > 0, SUM(day_paid) / SUM(followers_number), 0) AS followers_cost')
                ->selectRaw('SUM(followers_number) AS followers_number')
                ->groupBy('user_id')
                ->orderByDesc($sortField)
                ->offset(($page - 1) * $pageSize)
                ->limit($pageSize)
                ->get();

            $userList = Users::query()
                ->whereIn('id', $listData->pluck('user_id'))
                ->pluck('name', 'id');

            foreach ($listData as $value) {
                $value->new_user_charge    = round($value->new_user_charge, 2);
                $value->roi          = round($value->roi * 100, 1)  . '%';
                $value->first_roi    = round($value->first_roi * 100, 1) . '%';
                $value->profit       = round($value->profit, 2);
                $value->recover_rate = round($value->recover_rate * 100, 2) . '%';
                $value->user_name    = $userList->get($value->user_id) ?? null;
                $value->qw_cost      = round($value->qw_cost, 2);
                $value->new_cost     = round($value->new_cost, 2);
                $value->followers_cost=round($value->followers_cost, 2);

                $uniqueNewChargeUser = DataStatisticsService::statisticsDataUniqueUser($stDate, $enDate, null
                    , $value->user_id, $sysGroupId, 1, $adminId, $isSystemAdmin);
                $uniqueNewChargeUser = isset($uniqueNewChargeUser['new_user_charge_uv_unique']) ? $uniqueNewChargeUser['new_user_charge_uv_unique'] : 0;
                $value->unique_new_cost = ($uniqueNewChargeUser > 0) ? round($value->day_paid / $uniqueNewChargeUser, 2) : 0;
            }

            $list = $listData->toArray();
        }

        $data = [
            'list'    => $list,
            'summary' => $summary,
        ];

        return [$data, $total];
    }

    public static function getAuthCorpids($admin_id)
    {
        $ids = AdminManageCorp::where('sys_user_id', $admin_id)
            ->where('is_delete', 0)
            ->pluck('corpid')
            ->all();

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

        $corpids = AuthorizeCorp::whereIn('id', $ids)->where('enable', 1)->pluck('corpid')->all();

        return $corpids;
    }

    public static function custTotalNew($corpid, $group_admin_id, $is_system_admin, $sys_group_id)
    {
//        $corpids = null;
//        if($is_system_admin==0){
//            $corpids = self::getAuthCorpids($group_admin_id);
//        }
        $corpids = self::getAuthCorpids($sys_group_id);

        $date = date('Y-m-d', strtotime('-5 minute'));
        $last_date = date('Y-m-d', strtotime($date. ' -1 day'));
        $h = (int)date('H', strtotime('-5 minute'));
        if($corpid){
            $today = CustomerHourRecord::select(
                'cust_total_uc',
                'cust_add_uc',
                'cust_loss_uc',
                'official_account_uc',
                'cust_pay_uc',
                'cust_pay_amount',
                'cust_pay_uc_total',
                'cust_loss_uc_total',
                'cust_pay_amount_total'
            )
            ->where('corpid', $corpid)
            ->where('idate', $date)
            ->where('hour', $h)
            ->where(function($query) use($corpids){
                if(!empty($corpids)) $query->whereIn('corpid', $corpids);
            })
            ->first();

            $last = CustomerHourRecord::select(
                'cust_total_uc',
                'cust_add_uc',
                'cust_loss_uc',
                'official_account_uc',
                'cust_pay_uc',
                'cust_pay_amount',
                'cust_pay_uc_total',
                'cust_loss_uc_total',
                'cust_pay_amount_total'
            )
            ->where('corpid', $corpid)
            ->where('idate', $last_date)
            ->where('hour', $h)
            ->where(function($query) use($corpids){
                if(!empty($corpids)) $query->whereIn('corpid', $corpids);
            })
            ->first();
        } else {
            $today = CustomerHourRecord::selectRaw(
                'sum(cust_total_uc) as cust_total_uc,' .
                'sum(cust_add_uc) as cust_add_uc,' .
                'sum(cust_loss_uc) as cust_loss_uc,' .
                'sum(official_account_uc) as official_account_uc,' .
                'sum(cust_pay_uc) as cust_pay_uc,' .
                'sum(cust_pay_amount) as cust_pay_amount,' .
                'sum(cust_pay_uc_total) as cust_pay_uc_total,' .
                'sum(cust_loss_uc_total) as cust_loss_uc_total,' .
                'sum(cust_pay_amount_total) as cust_pay_amount_total'
            )
            ->where('idate', $date)
            ->where('hour', $h)
            ->where(function($query) use($corpids){
                if(!empty($corpids)) $query->whereIn('corpid', $corpids);
            })
            ->first();

            $last = CustomerHourRecord::selectRaw(
                'sum(cust_total_uc) as cust_total_uc,' .
                'sum(cust_add_uc) as cust_add_uc,' .
                'sum(cust_loss_uc) as cust_loss_uc,' .
                'sum(official_account_uc) as official_account_uc,' .
                'sum(cust_pay_uc) as cust_pay_uc,' .
                'sum(cust_pay_amount) as cust_pay_amount,' .
                'sum(cust_pay_uc_total) as cust_pay_uc_total,' .
                'sum(cust_loss_uc_total) as cust_loss_uc_total,' .
                'sum(cust_pay_amount_total) as cust_pay_amount_total'
            )
            ->where('idate', $last_date)
            ->where('hour', $h)
            ->where(function($query) use($corpids){
                if(!empty($corpids)) $query->whereIn('corpid', $corpids);
            })
            ->first();
        }

        if(empty($today)){
            return null;
        }

        $today->cust_total_uc_compare = $today->cust_total_uc - ($last->cust_total_uc ?? 0);
        $today->cust_add_uc_compare = $today->cust_add_uc - ($last->cust_add_uc ?? 0);
        $today->cust_loss_uc_compare = $today->cust_loss_uc - ($last->cust_loss_uc ?? 0);
        $today->official_account_uc_compare = $today->official_account_uc - ($last->official_account_uc ?? 0);
        $today->cust_pay_uc_compare = $today->cust_pay_uc - ($last->cust_pay_uc ?? 0);
        $today->cust_pay_amount_compare = $today->cust_pay_amount - ($last->cust_pay_amount ?? 0);
        $today->charge_user_cost = $today->cust_pay_uc_total > 0 ? round($today->cust_pay_amount_total / $today->cust_pay_uc_total) : 0;
        $last->charge_user_cost = $last->cust_pay_uc_total > 0 ? round($last->cust_pay_amount_total / $last->cust_pay_uc_total) : 0;
        $today->charge_user_cost_compare = $today->charge_user_cost - $last->charge_user_cost;

        return $today;
    }

    public static function custTrendsNew($corpid, $start, $end, $group_admin_id, $is_system_admin, $sys_group_id)
    {
//        $corpids = null;
//        if($is_system_admin==0){
//            $corpids = self::getAuthCorpids($group_admin_id);
//        }
        $corpids = self::getAuthCorpids($sys_group_id);

        $res = CustomerDayReport::selectRaw(
            'sum(cust_total_uc) as cust_total_uc,' .
            'sum(cust_add_uc) as cust_add_uc,' .
            'sum(cust_loss_uc) as cust_loss_uc,' .
            'sum(cust_gain_uc) as cust_gain_uc,' .
            'sum(official_account_uc) as official_account_uc,' .
            'sum(cust_pay_uc) as cust_pay_uc,' .
            'sum(cust_pay_amount) as cust_pay_amount,' .
            'sum(total_cust_pay_amount) as total_cust_pay_amount,' .
            'sum(total_cost) as total_cost,' .
            'idate' 
        )
        ->where('idate', '>=', $start)
        ->where('idate', '<=', $end)
        ->where(function($query) use($corpid){
            if($corpid) $query->where('corpid', $corpid);
        })
        ->where(function($query) use($corpids){
            if(!empty($corpids)) $query->whereIn('corpid', $corpids);
        })
        ->groupBy('idate')
        ->orderBy('idate', 'asc')
        ->get();

        foreach ($res as $val) {
            # 付费用户客单价 = 付费金额/付费人数
            $val->charge_user_cost = $val->cust_pay_uc > 0 ? round($val->cust_pay_amount / $val->cust_pay_uc) : 0;
        }
            
        return $res;
    }

    public static function custDataList($corpid, $group_admin_id, $is_system_admin, $sys_group_id)
    {
//        $corpids = null;
//        if($is_system_admin==0){
//            $corpids = self::getAuthCorpids($group_admin_id);
//        }
        $corpids = self::getAuthCorpids($sys_group_id);

        $date = date('Y-m-d', strtotime('-5 minute'));
        $h = (int)date('H', strtotime('-5 minute'));

        $list = CustomerHourRecord::select(
            'cust_total_uc',
            'cust_add_uc',
            'cust_loss_uc',
            'official_account_uc',
            'cust_pay_uc',
            'cust_pay_amount',
            'cust_pay_uc_total',
            'cust_loss_uc_total',
            'cust_pay_amount_total',
            'corpid'
        )
        ->where('idate', $date)
        ->where('hour', $h)
        ->where(function($query) use($corpid){
            if($corpid) $query->where('corpid', $corpid);
        })
        ->where(function($query) use($corpids){
            if(!empty($corpids)) $query->whereIn('corpid', $corpids);
        })
        ->get();

        foreach($list as $item){
            #获取企微名称
            $item->corp_name = AuthorizeCorp::where('corpid', $item->corpid)->value('corp_name');
            # 付费用户客单价 = 付费金额/付费人数
            $item->charge_user_cost = $item->cust_pay_uc_total > 0 ? round($item->cust_pay_amount_total / $item->cust_pay_uc_total) : 0;
        }

        return $list;
    }

    /**
     * 获取累计充值用户（去重）
     * */
    public static function chargeUserUnique($appId, $start, $end, $sysGroupId)
    {
        # 组装数据
        if($appId) {
            $mpAppIdList = array($appId);
        } else {
            $mpAppIdList = OfficialAccount::getSysGroupMpAppIdList($sysGroupId, 'StatisticsService.chargeUserUnique');
        }

        $accountData = array();
        foreach ($mpAppIdList as $appId) {
            $datum = [
                'app_id' => $appId,
                'start_date' => $start,
                'end_date' => $end
            ];

            array_push($accountData, $datum);
        }

        # 获取累计充值用户
        return DjOrderService::getChargeUserTotalUnique($accountData);
    }

    public static function firstDayChargeUserUnique($appId, $start, $end, $sysGroupId)
    {
        # 组装数据
        if($appId) {
            $mpAppIdList = array($appId);
        } else {
            $mpAppIdList = OfficialAccount::getSysGroupMpAppIdList($sysGroupId, 'StatisticsService.chargeUserUnique');
        }

        $accountData = array();
        foreach ($mpAppIdList as $appId) {
            $datum = [
                'app_id' => $appId,
                'start_date' => $start,
                'end_date' => $end
            ];

            array_push($accountData, $datum);
        }

        # 获取累计充值用户
        return DjOrder::getFirstDayChargeUserNum($accountData);
    }
}