test/app/Http/Controllers/Admin/Analyze/CollectController.php

<?php
/**
 * 汇总统计
 * @copyright 2021-浙江引擎力营销策划有限公司
 * @author Lc<sunshinecc1@163.com>
 * @since 2021-10-13
 */

namespace App\Http\Controllers\Admin\Analyze;

use App\Http\Controllers\Controller;
use App\Http\Models\PrKeywordExtend;
use App\Http\Models\Role;
use App\Http\Models\Site;
use App\Http\Models\SitesStatus;
use App\Http\Models\User;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Contracts\View;
use Illuminate\Http\JsonResponse;

class CollectController extends Controller
{

    /**
     * 404合集
     * @param Request $request
     * @return View\Factory|JsonResponse|View\View
     */
    public function collect(Request $request)
    {
        if (!$request->ajax()) {
            return view('admin/analyze/collect', [
                'web' => Role::getUsers(Role::TYPE_WEB),
                'optimizers' => Role::getUsers(Role::TYPE_OPTIMIZER),
                'optimizers_edit' => Role::getUsers(Role::TYPE_OPTIMIZATION_EDITING),
            ]);
        }
        $build = SitesStatus::query();
        $build = $this->inquire($request, $build);

        $cnTitleList = Site::query()->pluck('cn_title', 'id');
        $domainList = Site::query()->pluck('domain', 'id');
        $memoList = Site::query()->pluck('not_found_memo', 'id');

        $list = $build->with(['users'])
            ->where('status', 404)
            ->where('is_handle', 0)
            ->where('url', 'NOT LIKE', '%javascript:%')
            ->where('url', 'NOT LIKE', '%tel:%')
            ->where('url', 'NOT LIKE', '%mailto:%')
            ->where('url', 'NOT LIKE', '%$%')
            ->where('url', 'NOT LIKE', '%skype%')
            ->where('url', 'NOT LIKE', '%whatsapp%')
            ->selectRaw('count(*) as count,site_id')
            ->groupBy('site_id')
            ->paginate($request->input('pageSize') ?? 20);

        foreach ($list as $item) {
            $item->not_found = $memoList[$item->site_id] ?? '';
            $item->cn_title = $cnTitleList[$item->site_id] ?? '';
            $item->domain = $domainList[$item->site_id] ?? '';
            $item->web = $item->users->where('role_id', Role::TYPE_WEB)->first()->nickname ?? '';
            $item->optimization = $item->users->where('role_id', Role::TYPE_OPTIMIZER)->first()->nickname ?? '';
            $item->optimizationAe = $item->users->where('role_id', Role::TYPE_OPTIMIZATION_EDITING)->first()->nickname ?? '';
        }

        return response()->json([
            'rows' => $list->items(),
            'total' => $list->total()
        ]);
    }

    /**
     * 测速合集
     * @param Request $request
     * @return View\Factory|JsonResponse|\Illuminate\View\View
     */
    public function speedMeasurement(Request $request)
    {
        if (!$request->ajax()) {
            return view('admin/analyze/speed', [
                'web' => Role::getUsers(Role::TYPE_WEB),
                'optimizers' => Role::getUsers(Role::TYPE_OPTIMIZER),
                'optimizers_edit' => Role::getUsers(Role::TYPE_OPTIMIZATION_EDITING),
            ]);
        }
        $build = Site::query();
        $build = $this->inquire($request, $build, 'id');

        $siteList = $build->with(['users'])
            ->whereIn('status', [2, 3])
            ->select('id', 'old_id', 'domain', 'cn_title', 'speed_memo')
            ->whereNotNull('database')
            ->get();

        $pcSpeedMeasurement = [];
        $mobileSpeedMeasurement = [];

        $speedMeasurementList = DB::table('app_speed_measurement_cache')->get();
        foreach ($speedMeasurementList as $key => $value) {
            $array = json_decode($value->cache, true);
            $pcSpeedMeasurementResult = $array['pc'];
            $mobileSpeedMeasurementResult = $array['mobile'];

            $oldId = str_replace('cache:app/Http/Controllers/SpeedMeasurementController/index:', '', $value->key);
            $pcSpeedMeasurement[$oldId] = $pcSpeedMeasurementResult;
            $mobileSpeedMeasurement[$oldId] = $mobileSpeedMeasurementResult;
        }


        foreach ($siteList as $item) {
            $item->web = $item->users->where('role_id', Role::TYPE_WEB)->first()->nickname ?? '';
            $item->optimization = $item->users->where('role_id', Role::TYPE_OPTIMIZER)->first()->nickname ?? '';
            $item->optimizationAe = $item->users->where('role_id', Role::TYPE_OPTIMIZATION_EDITING)->first()->nickname ?? '';
            $item->pcSpeedMeasurement = $pcSpeedMeasurement[$item->old_id] ?? '';
            $item->mobileSpeedMeasurement = $mobileSpeedMeasurement[$item->old_id] ?? '';
        }

        foreach ($siteList as $key => $value) {
            if ($value->pcSpeedMeasurement > 79) {
                unset($siteList[$key]);
            }
        }

        $res = collect($siteList)->sortByDesc('pcSpeedMeasurement');
        $perPage = $request->input('pageSize') ?? TABLE_PAGE_SIZE;
        $result = $this->paginateCollection($res, $perPage);

        return response()->json([
            'rows' => $result->items(),
            'total' => $result->total()
        ]);
    }

    /**
     * 查询
     * @param $request object
     * @param $build object
     * @param $siteFile string
     * @return mixed
     */
    private function inquire($request, $build, $siteFile = 'site_id')
    {
        $keyword = $request->input('keyword');
        if (!empty($keyword)) {
            $siteIds = Site::query()
                ->where('domain', 'like', '%' . $keyword . '%')
                ->orWhere('cn_title', 'like', '%' . $keyword . '%')
                ->pluck('id');
            $build->whereIn($siteFile, $siteIds);
        }
        $webId = $request->input('webId');
        if (!empty($webId)) {
            $userIds = User::query()->where('id', $webId)->pluck('id');
            $siteIds = DB::table('user_has_sites')->whereIn('user_id', $userIds)->pluck('site_id');
            $build->whereIn($siteFile, $siteIds);
        }

        $optimizerId = $request->input('optimizerId');
        if (!empty($optimizerId)) {
            $userIds = User::query()->where('id', $optimizerId)->pluck('id');
            $siteIds = DB::table('user_has_sites')->whereIn('user_id', $userIds)->pluck('site_id');
            $build->whereIn($siteFile, $siteIds);
        }
        $optimizersEditId = $request->input('optimizersEditId');
        if (!empty($optimizersEditId)) {
            $userIds = User::query()->where('id', $optimizersEditId)->pluck('id');
            $siteIds = DB::table('user_has_sites')->whereIn('user_id', $userIds)->pluck('site_id');
            $build->whereIn($siteFile, $siteIds);
        }
        return $build;
    }

    /**
     * 保存备注
     * @param Request $request
     * @return JsonResponse
     */
    public function saveMemo(Request $request)
    {
        $memo = $request->input('memo') ?? '';
        $type = $request->input('type') ?? 1;
        $siteId = $request->input('siteId');

        if (!empty($siteId)) {
            if ($type == 1) {
                Site::query()->where('id', $siteId)->update(['speed_memo' => $memo]);
            } elseif ($type == 2) {
                Site::query()->where('id', $siteId)->update(['not_found_memo' => $memo]);
            } else {
                Site::query()->where('id', $siteId)->update(['keyword_memo' => $memo]);
            }
            return response()->json(['message' => '操作成功']);
        }
    }

    /**
     * 关键词未拓展统计
     * @param Request $request
     * @return View\Factory|\Illuminate\View\View
     */
    public function keywordsExpand(Request $request)
    {
        if (!$request->ajax()) {
            return view('admin/analyze/keywords_expand', [
                'services' => Role::getUsers(Role::TYPE_SERVER), //服务人员
                'optimizers' => Role::getUsers(Role::TYPE_OPTIMIZER),
                'sellerUsers' => Role::getUsers(Role::TYPE_SELLER),
                'optimizationEditingUser' => Role::getUsers(Role::TYPE_OPTIMIZATION_EDITING),
            ]);
        }
        $keyword = $request->input('keyword');

        $filterUserIds = [];
        $sellerId = $request->input('sellerId');
        $sellerId && $filterUserIds[] = $sellerId;
        $serverId = $request->input('serverId');
        $serverId && $filterUserIds[] = $serverId;
        $optimizerId = $request->input('optimizerId');
        $optimizerId && $filterUserIds[] = $optimizerId;
        $optimizationEditingUserId = $request->input('optimizationEditingUserId');
        $optimizationEditingUserId && $filterUserIds[] = $optimizationEditingUserId;

        $builder = Site::query()->with(['users']);
        if ($filterUserIds) {
            $builder->whereExists(function (\Illuminate\Database\Query\Builder $b) use ($filterUserIds) {
                $raw = sprintf('SUM(CASE WHEN user_id in (%s) then  1 ELSE  0 end) as total', implode(',', $filterUserIds));
                $b->select(DB::raw($raw))->from('user_has_sites')
                    ->whereRaw(sprintf('user_has_sites.site_id=sites.id HAVING total=%s', count($filterUserIds)));
            });
        }
        $type = $request->input('type');

        //每个项目最新的一次的记录的id
        $maxIds = PrKeywordExtend::query()->selectRaw('max(id) as id_max,site_id')
            ->groupBy('site_id')
            ->pluck('id_max', 'site_id')->toArray();
        //全部拓展过
        $siteByKeyWordList = PrKeywordExtend::query()
            ->whereIn('id', $maxIds)
            ->pluck('ym', 'site_id')->toArray();
        $ids = array_keys($siteByKeyWordList);

        //3个月内没有拓展过
        if (empty($type)) {
            $list = PrKeywordExtend::query()->whereIn('id', $maxIds)->get();
            $ids = [];
            $date = date("Ym", strtotime("last day of -3 month", strtotime(date("Ym"))));
            foreach ($list as $value) {
                if ($value->ym <= $date) {
                    $ids[] = $value->site_id;
                }
            }
        }

        //从未拓展过
        if ($type == 1) {
            $date = date("Y-m-d", strtotime("last day of -3 month", strtotime(date("Y-m-d"))));
            $id = Site::query()
                ->where('online_at', '<=', $date)
                ->whereIn('status', [2, 3])
                ->pluck('id')->toArray();

            $ids = [];
            foreach ($id as $value) {
                $result = PrKeywordExtend::query()->where('site_id', $value)->first();
                if (empty($result)) {
                    $ids[] = $value;
                }
            }
        }
        $siteList = $builder
            ->select('id', 'old_id', 'cn_title', 'status', 'domain', 'expired_at', 'reach_at', 'keyword_goal', 'keyword_memo')
            ->whereIn('id', $ids)
            ->whereIn('status', [2, 3])
            ->where(function (Builder $builder) use ($keyword) {
                if ($keyword) {
                    $builder->where('domain', 'like', '%' . $keyword . '%')
                        ->orWhere('cn_title', 'like', '%' . $keyword . '%');
                }
            })->get();
        $oldIds = array_filter(array_column($siteList->toArray(), 'old_id'));
        $top10ListMap = DB::connection('rank')->table('project_keyword')
            ->whereIn('project_id', $oldIds)
            ->selectRaw('SUM(CASE WHEN google_rank <= 10 THEN 1 ELSE 0 END) as top10,project_id')
            ->groupBy('project_id')->get()->keyBy('project_id')->toArray();

        foreach ($siteList as $item) {
            $item->status = Site::STATUS_MAP[$item->status];
            $item->renewal_at = substr($item->renewal_at, 0, 10);
            $item->expired_at = substr($item->expired_at, 0, 10);
            $item->reach_at = substr($item->reach_at, 0, 10);
            $item->top10 = $item->old_id ? ($top10ListMap[$item->old_id]->top10 ?? '-') : '未关联'; //实际达标关键词
            $item->ym = $siteByKeyWordList[$item->id] ?? '';
            $item->optimizer = implode('-', $item->users->where('role_id', Role::TYPE_OPTIMIZER)->pluck('nickname')->toArray());
            $item->optimizerAe = implode('-', $item->users->where('role_id', Role::TYPE_OPTIMIZATION_EDITING)->pluck('nickname')->toArray());
        };

        $res = collect($siteList)->sortByDesc('ym');
        $perPage = $request->input('pageSize') ?? TABLE_PAGE_SIZE;
        $result = $this->paginateCollection($res, $perPage);

        return response()->json([
            'rows' => $result->items(),
            'total' => $result->total()
        ]);

    }
}