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 DB;

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

    /**
     * 投手级别统计
     *
     * @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);
    }
}