test/app/Http/Controllers/Admin/HtPlantController.php

<?php
/**
 * Created by PhpStorm.
 * User: vanshao
 * Date: 2019-10-24
 * Time: 11:24
 */
namespace App\Http\Controllers\Admin;

use App\Http\Controllers\Controller;
use App\Http\Logics\Admin\SiteLogic;
use App\Http\Logics\Admin\SiteSyncLogic;
use App\Http\Models\Business;
use App\Http\Models\Contract;
use App\Http\Models\Role;
use App\Http\Models\Server;
use App\Http\Models\Site;
use App\Http\Models\SiteAddition;
use App\Http\Models\SiteEnterprise;
use App\Http\Models\SiteInfo;
use App\Http\Models\SiteMarketer;
use App\Http\Models\SitePayment;

use App\Http\Models\SiteSns;
use App\Http\Models\SiteStation;
use App\Http\Models\Social;
use App\Http\Models\SocialPublish;
use App\Http\Requests\Site\PaymentRequest;
use App\Http\Requests\Site\SiteOverviewRequest;
use App\Http\Requests\Site\CustomerRequest;
use App\Http\Requests\Site\SiteSaveRequest;
use App\Http\Requests\Site\SocialPublishRequest;
use App\Http\Requests\Site\SocialQueueRequest;
use App\Http\Requests\Site\SocialSaveRequest;
use GuzzleHttp\Client;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Cache;
use Illuminate\Support\Facades\DB;

use Illuminate\Support\Facades\Log;
use App\Http\Traits\HasSites;

class HtPlantController extends Controller{


    public function index(Request $request)
    {

        if (!$request->ajax()) {
            return view('admin/ht_plant/index');
        }


        $cacheKey=md5('htall');
        $cacheContent = Cache::get($cacheKey);
        $cacheContentTotal = Cache::get($cacheKey.'total');

        $sortName = $request->input('sortName')=='id'?'traffic':$request->input('sortName');
        $sortOrder = $request->input('sortOrder');
        $traffic_number=$request->input('traffic_number');
        $inquire_number=$request->input('inquire_number');


        if ($cacheContent&&!$cacheContent->isEmpty()) {
            $records=$cacheContent;
            $total=$cacheContentTotal;

        }else{

            if ($status = $request->input('status')) {
                $condition['status'] = $status;
            }

            DB::connection()->enableQueryLog();

            $keyword = $request->input('keyword');
            $builder = Site::query()->withCount(['articles' , 'linkDetails'=>function(Builder $query){
                $query->where([
                    ['enable', '=', 1]
                ]);

            }])
                ->withCount(['linkUrls'])
                ->with(['users','linkDetails','business'])->where($condition ?? [])->whereIn('status',[2,3])->where(function (Builder $builder) use ($keyword) {
                    if ($keyword) {
                        $builder->where('domain', 'like', '%' . $keyword . '%')
                            ->orWhere('cn_title', 'like', '%' . $keyword . '%');
                    }

                });



            if ($sortName == 'statusTitle') {
                $builder->orderBy('status', $sortOrder);
            }

            if ($sortName == 'order_at') {
                $builder->orderBy('order_at', $sortOrder);
            }

            $sites = $builder->orderByDesc('id')->paginate($request->input('pageSize') ?? TABLE_PAGE_SIZE);
            $items = $sites->items();
            $oldIds = array_filter(array_column($items, 'old_id'));

            $rankConnection = DB::connection('rank');
            $top10ListMap = $rankConnection->table('project_keyword')
                ->selectRaw('SUM(CASE WHEN google_rank <= 10 THEN 1 ELSE 0 END) as top10,count(id) as keyword_num,project_id')->whereIn('project_id', $oldIds)
                ->groupBy('project_id')->get()->keyBy('project_id')->toArray();

            $listReportMap = $rankConnection->table('project_listreport')->whereIn('project_id', $oldIds)->where([
                'ym' => date('Ym', strtotime('-1 month'))
            ])->get()->keyBy('project_id')->toArray();

            $reachTimeMap = $rankConnection->table('project')->whereIn('id',$oldIds)->get()->keyBy('id')->toArray();

            array_walk($items, function ($item) use (&$top10ListMap, &$listReportMap,&$reachTimeMap) {
                $item->identify = base64_encode($item->id);
                $item->managers_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGER)->pluck('nickname')->toArray());
                $item->statusTitle = Site::STATUS_MAP[$item->status] ?? '';

                $item->top10 = $item->old_id ? ($top10ListMap[$item->old_id]->top10 ?? '-') : '未关联';
                $item->keyword_num = $item->old_id ? ($top10ListMap[$item->old_id]->keyword_num ?? '-') : '未关联';
                $item->traffic = $item->old_id ? ($listReportMap[$item->old_id]->traffic ?? '-') : '未关联';
                $item->inquire = $item->old_id ? ($listReportMap[$item->old_id]->inquire ?? '-') : '未关联';

                $item->reach_time = $item->old_id ?($reachTimeMap[$item->old_id]->reach_time?intval((time()-$reachTimeMap[$item->old_id]->reach_time)/86400).'天':'未达标') : '未关联';
                $item->is_google_bid = in_array(Contract::GOOGLE_BID, $item->contract_ids ?? []) ? 1 : 0;
                $item->online_date = $item->online_at ? date('Y-m-d', strtotime($item->online_at)) : '';
                if($item->online_date){
                    $online_time=strtotime($item->online_date);
                    if($item->old_id){
                        $seo_times=$reachTimeMap[$item->old_id]->reach_time?$reachTimeMap[$item->old_id]->reach_time-$online_time:time()-$online_time;
                        $item->seo_time=intval($seo_times/86400);
                        $item->seo_time=$item->seo_time>0?$item->seo_time.'天':'-';
                    }

                }else{
                    $item->seo_time='-';
                }

                try{
                    if($item->webmaster_domain){
                        $opts=array(
                            "http"=>array(
                                "method"=>"GET",
                                "timeout"=>3
                            ),
                        );
                        $context = stream_context_create($opts);
                        $web_count=substr_count(file_get_contents($item->webmaster_domain.'sitemap.xml','false',$context),'http');
                        $item->web_count=$web_count;
                    }
                }catch(\Throwable $throwable){

                }


                $temp_link_details=array();
                foreach ($item->linkDetails as $value){
                    $temp_link_details[]=$value['link_id'];
                }
                if($temp_link_details){
                    $item->link_details_num=count(array_unique($temp_link_details));
                }
                unset($item->linkDetails);


            });
            $records = collect($items);

            $total=$sites->total();


            Cache::put($cacheKey,$records,10080);
            Cache::put($cacheKey.'total',$sites->total(),10080);
        }



        if (in_array($sortName, ['traffic', 'inquire', 'top10'])) {

            $descending = strtolower($sortOrder) == 'asc' ? false : true;
            $records = $records->sortBy(function ($item, $key) use ($sortName, $descending) {
                return intval($item[$sortName]);
            }, SORT_REGULAR, $descending)->values();
        }

        if($traffic_number){
            $number=explode(',',$traffic_number);

            $records=$records->filter(function ($value,$key) use($number){

                return $value['traffic']>$number[0]&&$value['traffic']<=$number[1];
            });
        }


        if($inquire_number){
            $number=explode(',',$inquire_number);
            $records=$records->filter(function ($value,$key) use($number){

                return $value['inquire']>$number[0]&&$value['inquire']<=$number[1];
            });
        }


        return response()->json([
            'rows' => array_values($records->toArray()),
            'total' => $total
        ]);
    }
}