duanju_crontab_script/app/Console/Commands/Stats/MiniprogramStats.php

<?php
namespace App\Console\Commands\Stats;


use App\Models\User;
use App\Models\VideoStatByCompany;
use App\Models\VideoStatByUser;
use App\Service\Stats\MiniprogramStatService;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Redis;

class MiniprogramStats extends Command{
        /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'Stats:MiniprogramStats';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = '短剧统计';

    private $day;

    /**
     * Execute the console command.
     */
    public function handle(){
        $this->day = date('Y-m-d',time()-86400);
        $this->processUser();
        $this->processCompany();
    }

    /**
     * 公司级别统计
     *
     * @return void
     */
    private function processCompany(){
        VideoStatByCompany::where('day',$this->day)->update(['is_delete'=>1]);
        $sql = <<<EDF
insert into video_stat_by_company(`day`,user_id,video_id,video_name,amount,charge_count,charge_user_num,play_count,created_at,updated_at)
select '%s' as `date`,puser_id,video_id,video_name,sum(amount),sum(charge_count),sum(charge_user_num),sum(play_count),now(),now() FROM video_stat_by_user where `day`='%s' group by puser_id,video_id,video_name
EDF;
        $sql = sprintf($sql,$this->day,$this->day);
        DB::insert($sql);

        $iterator = 0;
        $match = sprintf('djClickUv.%s.*', $this->day);
        $now = date('Y-m-d H:i:s');
        do {
            list($iterator, $keys) = Redis::scan($iterator, 'match', $match, 'count', 1000);
            foreach ($keys as $key) {
                $arr = explode('.', $key);
                $count = Redis::pfcount($key);
                if(DB::table('video_stat_by_company')
                    ->where([
                        ['user_id', '=', $arr[3]],
                        ['video_id', '=', $arr[2]],
                        ['is_delete', '=', 0],
                        ['day','=', $this->day]
                    ])->exists()) {
                    DB::table('video_stat_by_company')
                        ->where([
                            ['user_id', '=', $arr[3]],
                            ['video_id', '=', $arr[2]],
                            ['is_delete', '=', 0],
                            ['day','=', $this->day]
                        ])->update([
                            'click_uv' => $count, 'updated_at' => $now,
                        ]);
                } else {
                    DB::table('video_stat_by_company')
                        ->insert([
                            'user_id' => $arr[3],
                            'video_id' => $arr[2],
                            'video_name' => DB::table('videos')->where('id', $arr[2])->value('name') ?? '',
                            'day' => $this->day,
                            'click_uv' => $count,
                            'created_at' => $now,
                            'updated_at' => $now,
                        ]);
                }
            }
        }while($iterator > 0);

    }

    /**
     * 投手级别统计
     *
     * @return void
     */
    private function processUser(){
        $all_optimizer = User::join('user_has_roles','user_has_roles.user_id','=','users.id')
        ->join('roles','roles.id','=','user_has_roles.role_id')
        ->where('roles.identify','optimizer')
        ->select('users.id','users.pid')
        ->get();
        $all_video = DB::table('videos')->select('id','name')->get();
        foreach($all_optimizer as $optimizer_item){
            foreach($all_video as $video){
                $this->processUserDetail($optimizer_item->pid,$video->id,$optimizer_item->id,$video->name);
            }
        }
        $all_company = User::join('user_has_roles','user_has_roles.user_id','=','users.id')
        ->join('roles','roles.id','=','user_has_roles.role_id')
        ->where('roles.identify','company')
        ->select('users.id','users.pid')
        ->get();

        foreach($all_company as $company_item){
            foreach($all_video as $video){
                $this->processUserDetail($company_item->id,$video->id,$company_item->id,$video->name);
            }
        }

        MiniprogramStatService::deleteAll($this->day);
    }

    /**
     * 投手统计详情
     *
     * @param integer $puser_id
     * @param integer $video_id
     * @param integer $user_id
     * @param string $video_name
     * @return void
     */
    private function processUserDetail(int $puser_id, int $video_id,int $user_id,string $video_name){
        $day = $this->day;
        $stat_data = MiniprogramStatService::getOneItem($video_id,$user_id,$day);
        if(!$stat_data['amount']  && !$stat_data['charge_count'] && !$stat_data['charge_user_num'] && !$stat_data['play_count']){
            return ;
        }
        $stat_data['amount'] = ((int)($stat_data['amount']))/100;
        $old_record = VideoStatByUser::where('user_id',$user_id)->where('day',$day)->where('video_id',$video_id)->select('id')->first();
        if($old_record){
            VideoStatByUser::where('id',$old_record->id)->update($stat_data);
        }else{
            $stat_data['day'] = $day;
            $stat_data['user_id'] = $user_id;
            $stat_data['video_name'] = $video_name;
            $stat_data['video_id'] = $video_id;
            $stat_data['puser_id'] = $puser_id;
            VideoStatByUser::create($stat_data);
        }
       MiniprogramStatService::deleteCache($video_id,$user_id,$day);
    }
}