test/app/Console/Commands/ArticleMonitor.php

<?php

namespace App\Console\Commands;

use App\Http\Models\Article;
use App\Http\Models\ArticleStatistic;
use App\Http\Models\Site;
use Illuminate\Console\Command;
use Illuminate\Database\Eloquent\Relations\HasOne;

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

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Command description';

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

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        $sites = Site::query()->selectRaw('expired_at,id,status,renewal_at,order_at')->with(['siteInfo' => function (HasOne $q) {
            $q->selectRaw('site_id,article_need_num');
        }])->whereIn('status', [2,3])->get();


        $beginAt = date('Y-m-01 00:00:00');
        $endAt = date('Y-m-t 23:59:59');

        $siteIds = array_column($sites->toArray(), 'id');
        $createMap = Article::query()->selectRaw('SUM(id) as total,site_id')->whereIn('site_id', $siteIds)->where([['created_at', '>=', $beginAt], ['created_at', '<=', $endAt]])
            ->groupBy('site_id')->pluck('total', 'site_id')->toArray();
//        dump($createMap);

        $pubMap = Article::query()->selectRaw('SUM(id) as total,site_id')->whereIn('site_id', $siteIds)->where([['publish_at', '>=', $beginAt], ['publish_at', '<=', $endAt]])
            ->groupBy('site_id')->pluck('total', 'site_id')->toArray();
//        dd($pubMap);


        $totalNeedNum = [
            'exec' => 0, //实施期
            'server' => 0 //服务期
        ];
        //本月待查找文章数：实施期：xxx篇 服务期：xxx篇
        //本月待更新文章数：实施期：xxx篇 服务期：xxx篇
        $totalCreateNum = $totalPubNum = ['exec' => 0, 'server' => 0];

//        dump('$siteIds=',$siteIds);

        foreach ($sites as $site) {


            if ($site->siteInfo->article_need_num ?? 0) {
                $needNum = $site->siteInfo->article_need_num;

            } else {
                $whereAt = null;
                if (!empty($site->renewal_at)) {
                    $whereAt = $site->renewal_at;
                } else {
                    $whereAt = $site->order_at;
                }
                if (!$whereAt || empty($site->expired_at)) {
//                    echo sprintf('whereAt continue=%d%s',$site->id,PHP_EOL);
                    continue;
                }

                //距离到期时间的月份数
                $md = $this->getMonthNum(strtotime($site->expired_at));
                if ($md < 1) {
//                    echo sprintf('md continue=%d%s',$site->id,PHP_EOL);
                    continue;
                }

                //剩余文章数
                $surplusNum = Article::query()->where([['created_at', '>', $whereAt], ['site_id', '=', $site->id]])->count();
                $needNum = floor($surplusNum / $md);
            }


            if ($site->status == 2) { //实施期

                $needNum = $needNum < 10 ? 10 : $needNum;
                $totalNeedNum['exec'] += $needNum;

                $createNum = $createMap[$site->id] ?? 0;




                if ($createNum > $needNum) {
                    $createNum = $needNum;
                }
                $pubNum = $pubMap[$site->id] ?? 0;


                $totalCreateNum['exec'] += $createNum;
                $totalPubNum['exec'] += $pubNum;

            }

            if ($site->status == 3) {
                $needNum = $needNum < 4 ? 4 : $needNum;
                $totalNeedNum['server'] += $needNum;


                $createNum = $createMap[$site->id] ?? 0;
                if ($createNum > $needNum) {
                    $createNum = $needNum;
                }
                $pubNum = $pubMap[$site->id] ?? 0;

                $totalCreateNum['server'] += $createNum;
                $totalPubNum['server'] += $pubNum;
            }
        }


        //本月待查找文章数量=所有项目的本月需更新文章数总和-已上传数量（根据上传时间计算）
        //本月待更新文章数量=所有项目的本月需更新文章数总和-已发布数量（根据发布时间计算）

        ArticleStatistic::query()->create([
            'day' => date('Ymd'),
            'query_num' =>
                [
                    'exec' => $totalNeedNum['exec'] - $totalCreateNum['exec'],
                    'server' => $totalNeedNum['server'] - $totalCreateNum['server'],
                ]
            ,
            'update_num' =>
                [
                    'exec' => $totalNeedNum['exec'] - $totalPubNum['exec'],
                    'server' => $totalNeedNum['server'] - $totalPubNum['server']
                ]


        ]);


        dump($totalNeedNum);

        $this->info('success');
        return;
    }


    protected function getMonthNum($targetTime)
    {
        $target = date('Ym', $targetTime);

        $now = date('Ym');

        $ty = substr($target, 0, 4);
        $tm = substr($target, 4, 2);

        $ny = substr($now, 0, 4);
        $nm = substr($now, 4, 2);

        $yd = intval($ty) - intval($ny);


        $md = intval($tm - $nm);

        $result = $yd * 12 + $md;

        return $result;

    }
}