logic = $logic; } //总控制台 public function index() { $userSites = $this->userSites(); $validSiteIds = $userSites->pluck('id')->toArray(); $validOldSiteIds = array_filter(array_column($userSites->toArray(), 'id')); $data = []; $rankDatabase = DB::connection('rank'); $cacheKey = sprintf('index_%s_%s', date('Ym'), auth()->id()); $cacheContent = Cache::get($cacheKey); if ($cacheContent) { $data = $cacheContent; goto render; } //关键词排名急剧下降 $data['keyDropSites'] = $this->logic->indexDropKeywordSites($rankDatabase, $validSiteIds); //关键词达标项目 $data['reachSites'] = $this->logic->indexReachSites($validSiteIds); //建站完成的 $data['buildDoneSites'] = $this->logic->indexBuildDoneSites($validSiteIds); //证书过期项目 $data['certExpiredSites'] = $this->logic->indexCertExpiredSites($validSiteIds); //域名过期项目 $data['domainExpiredSites'] = $this->logic->indexDomainExpiredSites($validSiteIds); //月均流量 100 300 500 1000 $data['masterMeterTraffics'] = $this->logic->indexMasterMeterTraffics($rankDatabase, $validSiteIds); //关键词总量 $data['keywordTotal'] = $this->logic->indexKeywordTotal($rankDatabase, $validOldSiteIds); //项目类型数据 $data['siteStatus'] = $this->logic->indexSiteStatus($validSiteIds); //公司类型统计 $data['nature'] = $this->logic->indexNature($validSiteIds); //根据公司行业 $data['business'] = $this->logic->indexBusiness($validSiteIds); //渠道销售占比 $data['sales'] = $this->logic->indexSales($validSiteIds); //最新5个项目列表 $data['last5Sites'] = $this->logic->indexLast5Sites($rankDatabase, $validSiteIds); //外链库折线图 $data['linksLine'] = $this->logic->indexLinksLine(); Cache::put($cacheKey, $data, new \DateTime(date('Y-m-t 23:59:59'))); render: //流程过期提醒 $data['expireSiteProcessList'] = $this->logic->indexExpireSiteProcessList($validSiteIds); $data['indexCustomerLog'] = $this->logic->indexCustomerLog($validSiteIds); //网址异常的 $data['urlFailed'] = $urlFailed = UrlCheck::query()->whereIn('site_id', $validSiteIds)->get(); return view('admin/meter/index', $data); } protected function lastSixMonths() { $sixMonth = []; for ($i = 0; $i < 6; $i++) { $sixMonth[] = date("Y-m", strtotime("first day of -$i month")); } return $sixMonth; } //强制删除缓存 public function forgetCache(Request $request) { $which = $request->input('which'); $siteId = $request->input('siteId'); if ($which == 'index') { $cacheKey = sprintf('index_%s_%s', date('Ym'), auth()->id()); } elseif ($which == 'single') { $cacheKey = sprintf('single_%s_site_id%s', date('Ym'), $siteId); } else { return response()->json(['message' => '参数错误'], 422); } Cache::forget($cacheKey); return response()->json(['message' => '操作成功']); } //针对单个站点的控制台 public function singleSite(Request $request) { $siteId = $request->input('siteId'); if (!$siteId) { $singleSite = $this->logic->getSingleSite(); $siteId = $singleSite->id ?? 0; } $data = []; $site = Site::query()->withCount(['articles'])->with(['server', 'users'])->where(['id' => $siteId])->first(); if (!$site) { return view('admin/errors/tips', [ 'tips' => '项目站点信息不存在' ]); } if (!$site->database) { return view('admin/errors/tips', [ 'tips' => '项目数据库未分配' ]); } if (!$site->server) { return view('admin/errors/tips', [ 'tips' => '服务器未分配' ]); } $data['site'] = $site; $config = [ 'connection_name' => sprintf('connection_name_%s', $siteId), 'host' => $site->server->server_ip, 'port' => '3306', 'database' => $site->database, 'username' => $site->server->mysql_user_name, 'password' => $site->server->mysql_passwd, ]; config_connection($config); $masterDatabase = DB::connection($config['connection_name']); $rankDatabase = DB::connection('rank'); try { //检测是否有此数据库 $result = $masterDatabase->select(sprintf("show databases like '%s'", $site->database)); if (!$result) { return view('admin/errors/tips', [ 'tips' => $site->cn_title . '项目数据库不存在' ]); } } catch (\Throwable $throwable) { Log::warning(var_export($throwable->getMessage(), 1)); return view('admin/errors/tips', [ 'tips' => $site->cn_title . '项目数据库信息连接失败' ]); } $site->linkDetailsCount = LinkTaskDetail::query()->selectRaw('link_id') ->where([ 'redundant_site_id' => $siteId, 'enable' => 1, 'status' => 5 ])->groupBy('link_id')->get()->count(); $cacheKey = sprintf('single_%s_site_id%s', date('Ym'), $siteId); $cacheContent = Cache::get($cacheKey); if ($cacheContent) { $data = $cacheContent; goto render; } //月报记录 $historyFlowYm = $rankDatabase->table('report_flow') ->select(['flow_ym', 'project_id']) ->where('project_id', $site->old_id)->orderByDesc('flow_ym') ->limit(6) ->get(); $data['historyFlowYm'] = $historyFlowYm; //项目概况 引用修改 $this->logic->singleSiteProfile($rankDatabase, $site); //网页浏览量 访问量 $data['pvUvData'] = $this->logic->singlePvUvcData($masterDatabase); //首页关键词数 $data['topKeywordData'] = $this->logic->singleTopKeywordData($rankDatabase, $site->old_id); //询盘 $data['inquireTotalData'] = $masterDatabase->table('user_msg') ->where([['is_delete', '=', 0]])->count(); $data['inquireData'] = $this->logic->singleInquireData($masterDatabase); //网页数 $data['pageCountData'] = $this->logic->singlePageCount($masterDatabase); //关键词达标率 $data['topKeywordRate'] = [ 'goal' => $site->keyword_goal, 'last1Reach' => $data['topKeywordData']['top10']['last1'], 'last2Reach' => $data['topKeywordData']['top10']['last2']['val'], 'last3Reach' => $data['topKeywordData']['top10']['last3']['val'], ]; //网站展示量 $data['webmasterEffectImpressionsTotal'] = $rankDatabase->table('webmaster_effect')->selectRaw('ym,SUM(clicks) as clicks,SUM(impressions) AS impressions') ->where(['project_id' => $site->old_id])->first()->impressions ?? 0; $data['webmasterEffect'] = $this->logic->singleWebmasterEffectData($rankDatabase, $site->old_id); //文章达标率 $data['articleRate'] = $this->logic->singleArticleRate($site); //外链达标率 $data['linkRate'] = $this->logic->singleLinkRate($site); //我的文章 $data['myArticles'] = $this->logic->singleMyArticles($siteId); //我的外链任务 $data['myLinkTasks'] = $this->logic->singleMyLinksTasks($siteId); //流量折线图 $data['trafficLine'] = $this->logic->singleTrafficLine($masterDatabase); //询盘折线图 $data['inquireLine'] = $this->logic->singleInquireLine($masterDatabase); //关键词折线图 $data['keywordLine'] = $this->logic->singleKeywordLine($rankDatabase, $site->old_id); //引擎力学堂 $data['assets'] = Asset::query()->with('assetType')->get();; //客服人员 $data['serverUsers'] = $site->users->where('role_id', Role::TYPE_SERVER); //引擎力产品推荐 $data['products'] = Product::query()->where(['status' => 1])->get(); Cache::put($cacheKey, $data, new \DateTime(date('Y-m-t 23:59:59'))); render: if (!$site->reach_at) { $data['reachDaysData'] = '未达标'; } else { $diffDatetime = date_diff(date_create($site->reach_at), date_create()); $data['reachDaysData']=$diffDatetime->days>=0? abs($diffDatetime->days).'天':"未达标"; } //关键词达标 $data['reachDays'] = $this->logic->singleReachDays($site); //建站完成的 $data['buildDone'] = $this->logic->singleBuildDone($siteId); ////网址异常 $data['urlFailed'] = $this->logic->singleUrlFailed($siteId); //关键词排名急剧下降 //询盘 $data['inquireTotalData'] = $masterDatabase->table('user_msg') ->where([['is_delete', '=', 0]])->count(); $data['inquireData'] = $this->logic->singleInquireData($masterDatabase); //证书过期项目 $data['isCertExpired'] = $site->cert_expired_date ? (strtotime($site->cert_expired_date) < strtotime('+1 month')) : 0; //域名过期项目 domain_expired_date $data['isDomainExpired'] = $site->cert_expired_date ? (strtotime($site->domain_expired_date) < strtotime('+1 month')) : 0; $data['keywordLessVal'] = $this->logic->singleKeywordLessVal($rankDatabase, $site->old_id); return view('admin/meter/single', $data); } public function serviceFeedback(ServiceFeedbackRequest $request) { $validated = $request->validated(); $site = Site::query()->select(['cn_title', 'domain'])->where(['id' => $validated['site_id']])->first(); if (!$site) { return response()->json(['message' => '项目信息不存在'], 422); } $validated['creator_id'] = Auth::id(); $validated = $validated + $site->toArray(); ServiceFeedback::query()->create($validated); return response()->json(['message' => '操作成功']); } //客户总控台 投诉表单 public function feedback(Request $request) { $content = trim($request->input('content')); $faq = trim($request->input('faq')); if (!$content) { return response()->json(['message' => '请填写内容'], 422); } if (mb_strlen($content) > 2000) { return response()->json(['message' => '内容不能超过两千字'], 422); } Feedback::query()->create([ 'site_id' => $request->input('siteId'), 'content' => $content, 'faq' => $faq, 'creator_id' => Auth::id() ]); return response()->json(['message' => '操作成功']); } //达标项目导出 public function exportReachSitesExcel() { $userSites = $this->userSites(); $validSiteIds = $userSites->pluck('id')->toArray(); $siteRecords = $this->logic->indexReachSites($validSiteIds); $dataList[] = ['域名', '项目名称', '达标日期', '分数', '尾款']; foreach ($siteRecords as $site) { $sitesProcess = $site->sitesProcess->first(); $dataList[] = [ 'domain' => $site->domain, 'cn_title' => $site->cn_title, 'reach_at' => $site->reach_at, 'score' => $sitesProcess->evaluate['score'] ?? '', 'reach_payment' => $site->sitePayment->reach ?? '' ]; } return (new BasicExport($dataList))->download(sprintf('达标项目列表%s.xls', date('YmdHis'))); } //建站项目导出 public function exportBuildSitesExcel() { $userSites = $this->userSites(); $validSiteIds = $userSites->pluck('id')->toArray(); $siteRecords = $this->logic->indexBuildDoneSites($validSiteIds); $dataList[] = ['域名', '项目名称', '达标日期', '分数', '中期款']; foreach ($siteRecords as $site) { $sitesProcess = $site->sitesProcess->first(); $dataList[] = [ 'domain' => $site->domain, 'cn_title' => $site->cn_title, 'reach_at' => $site->reach_at, 'score' => $sitesProcess->evaluate['score'] ?? '', 'done_payment' => $site->sitePayment->done ?? '' ]; } return (new BasicExport($dataList))->download(sprintf('建站完成项目列表%s.xls', date('YmdHis'))); } protected function getTime($dateRange) { if (!$dateRange) { return null; } $startDate = substr($dateRange, 0, 10); $endDate = substr($dateRange, -10, -1); return [ ['reach_at', '>=', sprintf('%s 00:00:00', $startDate)], ['reach_at', '<=', sprintf('%s 23:59:59', $endDate)] ]; } //筛选达标站点 public function filterReachSites(Request $request) { $dateRangeFilter = $this->getTime($request->input('dateRange')); $userSites = $this->userSites(); $validSiteIds = $userSites->pluck('id')->toArray(); $siteRecords = $this->logic->indexReachSites($validSiteIds, $dateRangeFilter); $dataList = []; foreach ($siteRecords as $site) { $sitesProcess = $site->sitesProcess->first(); $dataList[] = [ 'domain' => $site->domain, 'cn_title' => $site->cn_title, 'reach_at' => $site->reach_at, 'score' => $sitesProcess->evaluate['score'] ?? '', 'reach_payment' => $site->sitePayment->reach ?? '' ]; } return response()->json(['data' => $dataList]); } //筛选建站站点 public function filterBuildSites(Request $request) { $dateRangeFilter = $this->getTime($request->input('dateRange')); $userSites = $this->userSites(); $validSiteIds = $userSites->pluck('id')->toArray(); $siteRecords = $this->logic->indexBuildDoneSites($validSiteIds, $dateRangeFilter); $dataList = []; foreach ($siteRecords as $site) { $sitesProcess = $site->sitesProcess->first(); $dataList[] = [ 'domain' => $site->domain, 'cn_title' => $site->cn_title, 'reach_at' => $site->reach_at, 'score' => $sitesProcess->evaluate['score'] ?? '', 'done_payment' => $site->sitePayment->done ?? '' ]; } return response()->json(['data' => $dataList]); } //品宣仪表 public function propaganda(Request $request) { if (!$request->ajax()) { return view('admin/meter/propaganda'); } $keyword = $request->input('keyword'); if ($status = $request->input('status')) { $condition['status'] = $status; } $builder = Site::query()->withCount([ // 'renewHistory', 'socialPublish' => function (Builder $query) { $query->whereNull('social_sync_at'); }, 'socialPublish as socialPublishFailCount' => function (Builder $query) { $query->whereJsonContains('result_status', ['pin' => SocialPublish::STATUS_FAILURE]) ->orWhereJsonContains('result_status', ['facebook' => SocialPublish::STATUS_FAILURE]) ->orWhereJsonContains('result_status', ['twitter' => SocialPublish::STATUS_FAILURE]); }, 'articles as month_articles_count' => function (Builder $query) { $query->where('created_at', '>', date('Y-m-01 00:00:00')); }, 'articles as no_sync_articles_count' => function (Builder $query) { $query->whereNull('sync_at'); }, 'articles as weekArticlesCount' => function (Builder $query) { $query->where('created_at', '>', date('Y-m-d H:i:s', strtotime('-2 week'))); }, 'articles as monthArticlesCount' => function (Builder $query) { $query->where('created_at', '>', date('Y-m-d H:i:s', strtotime('-1 month'))); }, 'linkDetails' => function (Builder $query) { $query->where([['enable', '=', 1], ['status', '=', 5]]); }, 'linkDetails as month_link_details_count' => function (Builder $query) { $query->where([['enable', '=', 1], ['status', '=', 5]])->where('created_at', '>', date('Y-m-01 00:00:00'));; }, 'linkUrls' => function (Builder $query) { $query->where('link_tasks_detail.enable', 1) ->where('link_tasks_detail.status', 5) ->where('link_tasks_url.status', 5); }, 'linkUrls as month_link_urls_count' => function (Builder $query) { $query->where('link_tasks_detail.enable', 1) ->where('link_tasks_detail.status', 5) ->where('link_tasks_url.status', 5) ->where('link_tasks_url.created_at', '>', date('Y-m-01 00:00:00')); }, 'linkUrls as lastMonthLinkUrlsCount' => function (Builder $query) { $query->where('link_tasks_url.created_at', '>', date('Y-m-d H:i:s', strtotime('-1 month'))); } ])->with(['users', 'renewHistory'])->whereIn('status', [2, 3])->where($condition ?? [])->where(function (Builder $builder) use ($keyword) { if ($keyword) { $builder->where('domain', 'like', '%' . $keyword . '%') ->orWhere('cn_title', 'like', '%' . $keyword . '%'); } }); $sortName = $request->input('sortName'); $sortOrder = $request->input('sortOrder'); if ($sortName == 'statusTitle') { $builder->orderBy('status', $sortOrder); } $records = $builder->orderByDesc('id') ->paginate($request->input('pageSize') ?? TABLE_PAGE_SIZE); $items = $records->items(); $oldIds = array_filter(array_column($items, 'old_id')); $rankConnection = DB::connection('rank'); $lastListReportMap = $rankConnection->table('project_listreport')->whereIn('project_id', $oldIds)->where([ 'ym' => date('Ym', strtotime('first day of -1 month')) ])->get()->keyBy('project_id')->toArray(); $last2ListReportMap = $rankConnection->table('project_listreport')->whereIn('project_id', $oldIds)->where([ 'ym' => date('Ym', strtotime('first day of -2 month')) ])->get()->keyBy('project_id')->toArray(); array_walk($items, function ($item) use (&$lastListReportMap, &$last2ListReportMap) { $lastRenew = $item->renewHistory->last(); //唉。 if ($lastRenew) { $publishArticleCount = Article::query()->where([ ['site_id', '=', $item->id], ['created_at', '>', $lastRenew->created_at] ])->count(); } else { $publishArticleCount = Article::query()->where([ ['site_id', '=', $item->id] ])->count(); } $item->managers_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGER)->pluck('nickname')->toArray()); $item->statusTitle = Site::STATUS_MAP[$item->status] ?? ''; if ($item->old_id) { if (empty($lastListReportMap[$item->old_id])) { $item->traffic = '-'; } else { $last2Traffic = $last2ListReportMap[$item->old_id]->traffic ?? 0; $lastTraffic = $lastListReportMap[$item->old_id]->traffic; $isIncrease = ($lastTraffic - $last2Traffic) > 0; $item->traffic = sprintf('%s', $isIncrease, $last2Traffic, $lastTraffic); } } else { $item->traffic = '未关联'; } $item->online_date = $item->online_at ? date('Y-m-d', strtotime($item->online_at)) : ''; //期数 $item->renew_count = $item->renewHistory->count() + 1; if ($item->socialPublishFailCount > 0) { $failSocialClass = 'socialFail'; } $item->social_queue_count = sprintf( '