kwaiAD/app/Console/Commands/StarVideoRan...

<?php

namespace App\Console\Commands;

use App\Models\JuxingAdAccount;
use App\Models\JxQtaskVideoOrderList;
use App\Models\JxStarVideoOrderList;
use App\Models\JxStarUsers;
use App\Support\Log;
use App\Support\RedisModel;
use Illuminate\Console\Command;

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

    /**
     * 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', //消耗
            'view', 
            'play', 
            'action', 
            'admire', 
            'comment', 
            'click_rate', 
            'click_cnt', 
            'play_5s_rate', 
            'play_end_rate', 
            'item_impression_cnt', 
            'item_click_cnt', 
            'item_click_rate',
            //'ctr',  // 视频曝光到视频播放的点击率
            'cpm',  // 千次播放成本
            'cpc',  // 点击成本
            //'actr',  // 行为率，播放到点击视频链接的点击率
            '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){
            if(!isset($v['amount'])){
                $v['amount'] = $v['real_amount'];
            }

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

            $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 = JxQtaskVideoOrderList::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',
                    'view',
                    'play',
                    'action',
                    'admire',
                    'comment',
                    'click_rate',
                    'play_5s_rate',
                    'play_end_rate',
                    'item_impression_cnt',
                    'item_click_cnt',
                    'item_click_rate',
                    'star_release_time',
                    'caption',
                    'video_url'
                ])
                ->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 = JxStarVideoOrderList::query()
                ->where('star_release_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',
                    'view',
                    'play',
                    'action',
                    'admire',
                    'comment',
                    'click_rate',   
                    'play_5s_rate',
                    'play_end_rate',
                    'item_impression_cnt',
                    'item_click_cnt',
                    'item_click_rate',
                    'star_release_time',
                    'caption',
                    'video_url'
                ])
                ->orderBy('id')
                ->limit(500)
                ->get()
                ->toArray();

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

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

            yield $orderList;
        }
    }

}