123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613 |
- <?php
- namespace App\Http\Controllers\Admin;
- use App\Exports\BasicExport;
- use App\Http\Logics\Admin\MeterLogic;
- use App\Http\Models\Article;
- use App\Http\Models\Asset;
- use App\Http\Models\Feedback;
- use App\Http\Models\LinkTaskDetail;
- use App\Http\Models\Product;
- use App\Http\Models\Role;
- use App\Http\Models\ServiceFeedback;
- use App\Http\Models\Site;
- use App\Http\Models\SocialPublish;
- use App\Http\Models\UrlCheck;
- use App\Http\Requests\Meter\ServiceFeedbackRequest;
- use Illuminate\Database\Eloquent\Builder;
- use Illuminate\Http\Request;
- use App\Http\Controllers\Controller;
- use Illuminate\Support\Facades\Auth;
- use Illuminate\Support\Facades\Cache;
- use Illuminate\Support\Facades\DB;
- use Illuminate\Support\Facades\Log;
- use App\Http\Traits\HasSites;
- /**
- * 控制台
- * Class MeterController
- * @package App\Http\Controllers\Admin
- */
- class MeterController extends Controller
- {
- use HasSites;
- protected $logic;
- public function __construct(MeterLogic $logic)
- {
- $this->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('<span class="%s" title="%s">%s</span>', $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(
- '<div class="%s">%s|%s</div>',
- ($failSocialClass ?? ''),
- $item->social_publish_count,
- $item->socialPublishFailCount
- );
- if ($item->article_goal <= $publishArticleCount) {
- $articleReachClass = 'reachGoal';
- }
- $item->article_goal_text = sprintf('<div class="%s">%s</div>', ($articleReachClass ?? ''), $item->article_goal);
- if ($item->link_goal <= $item->link_urls_count) {
- $linkReachClass = 'reachGoal';
- }
- $item->link_goal_text = sprintf('<div class="%s">%s</div>', ($linkReachClass ?? ''), $item->link_goal);
- if ($item->status == 2 && $item->weekArticlesCount < 1) {//实时期
- $articleClass = 'articleUnUpdate';
- }
- if ($item->status == 3 && $item->monthArticlesCount < 1) {//服务期
- $articleClass = 'articleUnUpdate';
- }
- //外链一个月没更新
- if ($item->lastMonthLinkUrlsCount < 1) {
- $linkClass = 'linkUnUpdate';
- }
- $item->articles_count_mul = sprintf(
- '<div class="%s">%s<b>|</b><span style="color: red">%s</span><b>|</b>%s</div>',
- ($articleClass ?? ''),
- $publishArticleCount, $item->month_articles_count, $item->no_sync_articles_count
- );
- $item->link_details_count_mul = sprintf('%s|%s', $item->link_details_count, $item->month_link_details_count);
- $item->link_urls_count_mul = sprintf('<div class="%s">%s|%s<div>', ($linkClass ?? ''), $item->link_urls_count, $item->month_link_urls_count);
- });
- return response()->json([
- 'rows' => $items,
- 'total' => $records->total()
- ]);
- }
- }
|