test/app/Console/Commands/Link.php

<?php

namespace App\Console\Commands;

use App\Http\Models\LinkStatistical;
use App\Http\Models\Role;
use App\Http\Models\Site;
use Illuminate\Console\Command;
use Illuminate\Database\Eloquent\Builder;

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

    /**
     * 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()
            ->whereIn('status', [2, 3])
            ->with(['users' => function (\Illuminate\Database\Eloquent\Relations\BelongsToMany $q) {
                $q->select(['id', 'username', 'nickname', 'role_id']);
            }, 'linkDetails' => function (\Illuminate\Database\Eloquent\Relations\HasMany $q) {
                $q->select(['id', 'redundant_site_id', 'link_id'])
                    ->where(['enable' => 1, 'link_tasks_detail.status' => 5]);
            }])->withCount([
                'linkUrls as publishThisMonth' => function (Builder $b) {
                    $b->where('link_tasks_detail.enable', 1)
                        ->where('link_tasks_detail.status', 5)
                        ->where('link_tasks_url.status', 5)
                        ->whereBetween('link_tasks_url.created_at', [date('Y-m-01 00:00:00'), date('Y-m-t 23:59:59')]);
                },
                'linkUrls as publishLastMonth' => function (Builder $b) {
                    $b->where('link_tasks_detail.enable', 1)
                        ->where('link_tasks_detail.status', 5)
                        ->where('link_tasks_url.status', 5)
                        ->whereBetween('link_tasks_url.created_at', [date('Y-m-01 00:00:00', strtotime('last month')), date('Y-m-d 23:59:59', strtotime(date('Y-m-1') . '-1 day'))]);
                },
                'linkUrls as linkUrls' => function (Builder $query) {
                    $query->where('link_tasks_detail.enable', 1)
                        ->where('link_tasks_detail.status', 5)
                        ->where('link_tasks_url.status', 5);
                }])->orderByDesc('id')->get();

        $data = [];
        foreach ($sites as $value) {
            $data[] = [
                'site_id' => $value->id,
                'cn_title' => $value->cn_title ?? '',
                'domain' => $value->domain ?? '',
                'site_status' => $value->status,
                'link_goal' => $value->link_goal ?? 0,
                'link_pact_count' => $value->linkDetails->groupBy('link_id')->count(),
                'link_urls' => $value->linkUrls ?? 0,
                'publish_last_month' => $value->publishLastMonth ?? 0,
                'publish_this_month' => $value->publishThisMonth ?? 0,
                'server_people_id' => implode(',', $value->users->where('role_id', Role::TYPE_SERVER)->pluck('id')->toArray()),
                'chongqing_link_people_id' => implode(',', $value->users->where('role_id', Role::TYPE_LINK_PART_CHONGQING)->pluck('id')->toArray()),
                'optimizer_people_id' => implode(',', $value->users->where('role_id', Role::TYPE_OPTIMIZER)->pluck('id')->toArray()),
                'optimizer_editing_people_id' => implode(',', $value->users->where('role_id', Role::TYPE_OPTIMIZATION_EDITING)->pluck('id')->toArray()),
                'created_at' => date('Y-m-d H:i:s')
            ];
        }
        LinkStatistical::query()->delete();
        LinkStatistical::query()->insert($data);
    }

}