kwaiAD/app/Console/Commands/StarLiveRank...

<?php

namespace App\Console\Commands;

use App\Models\JuxingAdAccount;
use App\Models\JxQtaskLiveOrderList;
use App\Models\JxStarLiveOrderList;
use App\Models\JxStarLiveInfoList;
use App\Models\JxStarUsers;
use App\Support\Log;
use App\Support\RedisModel;
use Illuminate\Console\Command;

class StarLiveRankStat extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'StarLiveRankStat {day?}';
    protected $rkey = 'StarLiveRankDatas-';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = '达人排行榜-直播更新缓存';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Execute the console command.
     *
     * @return int
     */
    public function handle()
    {
        $this->info(date('H:i') . ' 开始执行');

        $d = $this->argument('day') ? $this->argument('day') : 30;

        $this->start($d);

        $this->info(date('H:i') . ' 结束执行');

    }

    public function start($d)
    {
        $start = date('Y-m-d', strtotime('-'.$d.' day')); //统计近d天

        $orderList = array();

        $orderGenerator = $this->getQtaskDbData(0, $start);
        foreach($orderGenerator as $list){
            $orderList = array_merge($orderList, $list);
        }

        $orderGenerator = $this->getStarDbData(0, $start);
        foreach($orderGenerator as $list){
            $orderList = array_merge($orderList, $list);
        }

        #字段指标
        $columns = [
            'amount', //消耗
            'play_cnt', 
            'play_uv', 
            'like_cnt', 
            'comment_cnt', 
            'share_cnt', 
            'item_show_cnt', 
            'item_click_cnt', 
            'item_click_rate',
            //'ctr',  // 视频曝光到视频播放的点击率
            'cpm',  // 千次播放成本
            //'item_cpc',  // 点击成本 
            'cpa',  // 激活成本/注册成本

        ];

        #达人头像，粉丝数
        $userInfos = JxStarUsers::select('user_id', 'name', 'head_url', 'fans_number', 'gender')->get()->keyBy('user_id')->toArray();

        $data_max = array();
        $data_min = array();
        foreach($orderList as $k=>$v){

            #计算值补充
            $v['cpm'] = $v['play_cnt']>0 ? round($v['amount']/$v['play_cnt']*1000, 2) : null;
            //$v['item_cpc'] = $v['item_click_cnt']>0 ? round($v['amount']/$v['item_click_cnt'], 2) : null;
            $v['cpa'] = $v['item_click_cnt']>0 ? round($v['amount']/$v['item_click_cnt'], 2) : null; //激活成本

            $uid = $v['user_id'];
            $uid = $v['user_id'];
            $v['head_url'] = $userInfos[$uid]['head_url'] ?? null;
            $v['fans_number'] = $userInfos[$uid]['fans_number'] ?? null;
            $v['gender'] = $userInfos[$uid]['gender'] ?? null;


            foreach($columns as $column){
                if( !isset($data_max[$column][$uid]) ){
                    $data_max[$column][$uid] = $v;
                } else {
                    if($v[$column]>$data_max[$column][$uid][$column]){
                        $data_max[$column][$uid] = $v;
                    }
                }

                if( !isset($data_min[$column][$uid]) ){
                    $data_min[$column][$uid] = $v;
                } else {
                    if($v[$column]<$data_min[$column][$uid][$column]){
                        $data_min[$column][$uid] = $v;
                    }
                }
            }
        }

        if(!empty($data_max)){
            foreach($data_max as $column=>$list){
                $rkey = $this->rkey . 'max-'. $column.'-'.$d;

                $list = array_values($list);
                array_multisort(array_column($list, $column), SORT_DESC, $list);
                RedisModel::set($rkey, json_encode($list));
            }
        }

        if(!empty($data_min)){
            foreach($data_min as $column=>$list){
                $rkey = $this->rkey . 'min-'. $column.'-'.$d;

                $list = array_values($list);
                array_multisort(array_column($list, $column), SORT_ASC, $list);
                RedisModel::set($rkey, json_encode($list));
            }
        }
        
    }

    private function getQtaskDbData($min_id=0, $start){
        while(true){
            $orderList = JxQtaskLiveOrderList::query()
                ->where('star_release_time', '>=', $start)
                ->where('enable', 1)
                ->where('amount', '>', 0)
                ->where('id', '>', $min_id)
                ->select([
                    'id',
                    'advertiser_id',
                    'task_id',
                    'order_id',
                    'amount',
                    'star_name',
                    'kwai_id',
                    'user_id',
                    'play_cnt',
                    'play_uv',
                    'like_cnt',
                    'comment_cnt',
                    'share_cnt',
                    'item_show_cnt',
                    'item_click_cnt',
                    'item_click_rate',
                    'star_release_time'
                ])
                ->orderBy('id')
                ->limit(500)
                ->get()
                ->toArray();

            if(empty($orderList)){
                break;
            }

            $min_id = end($orderList)['id'];

            yield $orderList;
        }
    }

    private function getStarDbData($min_id=0, $start){
   
        while(true){
            $orderList = JxStarLiveOrderList::query()
                ->where('live_start_time', '>=', $start)
                ->where('enable', 1)
                ->where('real_amount', '>', 0)
                ->where('id', '>', $min_id)
                ->select([
                    'id',
                    'advertiser_id',
                    'task_id',
                    'order_id',
                    'real_amount', //
                    'star_name',
                    'kwai_id',
                    'user_id',
                    'play_cnt',
                    'play_uv',
                    'like_cnt',
                    'comment_cnt',
                    'share_cnt',
                    'live_start_time' //
        
                ])
                ->orderBy('id')
                ->limit(500)
                ->get()
                ->toArray();

            if(empty($orderList)){
                break;
            }

            foreach($orderList as &$val){
                $val['amount'] = $val['real_amount'];
                $val['star_release_time'] = $val['live_start_time'];

                #查组件数据
                $liveInfo = JxStarLiveInfoList::where('order_id', $val['order_id'])
                    ->where('enable', 1)
                    ->selectRaw('sum(item_show_cnt) as item_show_cnt, sum(item_click_cnt) as item_click_cnt')
                    ->first();

                if(!empty($liveInfo)){
                    $val['item_show_cnt'] = $liveInfo->item_show_cnt;
                    $val['item_click_cnt'] = $liveInfo->item_click_cnt;
                    $val['item_click_rate'] = $val['item_show_cnt']>0 ? round($val['item_click_cnt']/$val['item_show_cnt'], 4) : null;
                } else {
                    $val['item_show_cnt'] = null;
                    $val['item_click_cnt'] = null;
                    $val['item_click_rate'] = null;
                }
            }

            $min_id = end($orderList)['id'];

            yield $orderList;
        }
    }

}