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

<?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()
        ]);
    }

}