community-service/app/Console/Commands/ExcellentResidents.php

<?php
/**
 * Created by PhpStorm.
 * User: durong
 * Date: 2019/6/19
 * Time: 下午3:45
 */
namespace App\Console\Commands;


use App\Models\Behavior;
use App\Models\CommentRecord;
use App\Models\GeneralRecord;
use App\Models\RegisteredRecord;
use App\Models\ReleaseRecord;
use Illuminate\Console\Command;
use Illuminate\Support\Carbon;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Redis;

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

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = '昨日优秀居民统计';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct(Behavior $behavior, RegisteredRecord $registeredRecord,
                                CommentRecord $commentRecord,
                                GeneralRecord $generalRecord,
                                ReleaseRecord $releaseRecord)
    {
        parent::__construct();
        $this->behavior = $behavior;
        $this->commentRecord = $commentRecord;
        $this->generalRecord = $generalRecord;
        $this->registeredRecord = $registeredRecord;
        $this->releaseRecord = $releaseRecord;
    }

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        $this->line("开始统计星球居民相关内容");
        $yesterday_start = Carbon::now()->addDays(-1)->startOfDay()->toDateTimeString();
        $yesterday_end = Carbon::now()->addDays(-1)->endOfDay()->toDateTimeString();
        $sum_quantity_issued = $this->behavior
            ->leftJoin('comment_account_record as c','behavior.virus_behavior_id','=','c.virus_behavior_id')
            ->leftJoin('general_ledger_record as g','behavior.virus_behavior_id','=','g.virus_behavior_id')
            ->leftJoin('registered_accounts_record as a','behavior.virus_behavior_id','=','a.virus_behavior_id')
            ->leftJoin('release_record as r','behavior.virus_behavior_id','=','r.virus_behavior_id')
            ->whereBetween('c.created_at', [$yesterday_start,$yesterday_end])
            ->orwhereBetween('g.created_at', [$yesterday_start,$yesterday_end])
            ->orwhereBetween('a.created_at', [$yesterday_start,$yesterday_end])
            ->orwhereBetween('r.created_at', [$yesterday_start,$yesterday_end])
            ->get(array(
                    DB::raw('SUM(c.quantity_issued) as c_quantity_issued'),
                    DB::raw('SUM(g.quantity_issued) as g_quantity_issued'),
                    DB::raw('SUM(a.quantity_issued) as a_quantity_issued'),
                    DB::raw('SUM(r.quantity_issued) as r_quantity_issued')
                )
            )->toArray();
        //昨日发放总豆
        $all_quantity_issued = 0;
        foreach ($sum_quantity_issued as $k=>$v){
            $all_quantity_issued += $v['c_quantity_issued'] + $v['g_quantity_issued'] + $v['a_quantity_issued'] + $v['r_quantity_issued'];
        }

        //文章被评论最多用户
        $comment = $this->commentRecord
            ->whereBetween('created_at', [$yesterday_start,$yesterday_end])
            ->select(DB::raw('count(*) as count'), 'content_author_id', 'related_content_id')
            ->groupBy('related_content_id', 'content_author_id')->orderBy('count', 'desc')->limit(1)->get();
        $comment = $comment->toArray();
        foreach ($comment as $k=>$v){
            $comment[$k]['type'] = 'comment';//类型
        }

        //昨日拉新最多用户
        $registered = $this->registeredRecord
            ->whereBetween('created_at', [$yesterday_start,$yesterday_end])
            ->select(DB::raw('count(*) as count'), 'superior_uid as content_author_id')//作为用户ID
            ->groupBy('superior_uid')->orderBy('count', 'desc')->limit(1)->get();
        $registered = $registered->toArray();
        foreach ($registered as $k=>$v){
            $registered[$k]['type'] = 'registered';
        }

        //文章被收藏最多用户
        $virus_id = $this->behavior
            ->select('virus_behavior_id')
            ->where('behavior_identification', 'collect')
            ->first();
        $collent = $this->generalRecord
            ->where('virus_behavior_id', $virus_id->virus_behavior_id)
            ->whereBetween('created_at', [$yesterday_start,$yesterday_end])
            ->select(DB::raw('count(*) as count'), 'content_author_id', 'related_content_id')
            ->groupBy('related_content_id', 'content_author_id')->orderBy('count', 'desc')->limit(1)->get();
        $collent = $collent->toArray();
        foreach ($collent as $k=>$v){
            $collent[$k]['type'] = 'collent';
        }

        //文章被喜欢最多用户
        $virus_id = $this->behavior
            ->select('virus_behavior_id')
            ->where('behavior_identification', 'like')
            ->first();
        $like = $this->generalRecord
            ->where('virus_behavior_id', $virus_id->virus_behavior_id)
            ->whereBetween('created_at', [$yesterday_start,$yesterday_end])
            ->select(DB::raw('count(*) as count'), 'content_author_id', 'related_content_id')
            ->groupBy('related_content_id', 'content_author_id')->orderBy('count', 'desc')->limit(1)->get();
        $like = $like->toArray();
        foreach ($like as $k=>$v){
            $like[$k]['type'] = 'like';
        }

        //文章被转发最多用户
        $virus_id = $this->behavior
            ->select('virus_behavior_id')
            ->where('behavior_identification', 'forward')
            ->first();
        $forward = $this->generalRecord
            ->where('virus_behavior_id', $virus_id->virus_behavior_id)
            ->whereBetween('created_at', [$yesterday_start,$yesterday_end])
            ->select(DB::raw('count(*) as count'), 'content_author_id', 'related_content_id')
            ->groupBy('related_content_id', 'content_author_id')->orderBy('count', 'desc')->limit(1)->get();
        $forward = $forward->toArray();
        foreach ($forward as $k=>$v){
            $forward[$k]['type'] = 'forward';
        }

        //文章被阅读最多用户
        $virus_id = $this->behavior
            ->select('virus_behavior_id')
            ->where('behavior_identification', 'read')
            ->first();
        $read = $this->generalRecord
            ->where('virus_behavior_id', $virus_id->virus_behavior_id)
            ->whereBetween('created_at', [$yesterday_start,$yesterday_end])
            ->select(DB::raw('count(*) as count'), 'content_author_id', 'related_content_id')
            ->groupBy('related_content_id', 'content_author_id')->orderBy('count', 'desc')->limit(1)->get();
        $read = $read->toArray();
        foreach ($read as $k=>$v){
            $read[$k]['type'] = 'read';
        }
        $all_merge = array_merge($comment, $registered, $collent, $like, $forward, $read);
        $all_excellent_residents = json_encode($all_merge);

        Log::info('统计昨日优秀居民内容'.json_encode($all_excellent_residents));
        Log::info('昨日发放总彩虹豆'.json_encode($all_quantity_issued));

        Redis::set('yesterday_excellent_residents', $all_excellent_residents);
        Redis::set('yesterday_quantity_issued', $all_quantity_issued);

        $this->line("统计昨日内容结束");
    }
}