test/app/Http/Logics/Admin/MeterLogic.php

<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2019/4/29 0029
 * Time: 17:16
 */

namespace App\Http\Logics\Admin;

use App\Http\Models\Article;
use App\Http\Models\Business;
use App\Http\Models\Contract;
use App\Http\Models\CustomerLog;
use App\Http\Models\Link;
use App\Http\Models\LinkTask;
use App\Http\Models\LinkTaskDetail;
use App\Http\Models\MasterMeter;
use App\Http\Models\Role;
use App\Http\Models\Site;
use App\Http\Models\SiteProcess;
use App\Http\Models\UrlCheck;
use App\Http\Models\User;
use \Illuminate\Database\ConnectionInterface;
use Illuminate\Database\Query\Builder;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
use Illuminate\Database\Eloquent\Relations\HasMany;

class MeterLogic
{
    public function indexLast5Sites(ConnectionInterface $rankDatabase, &$validSiteIds)
    {
        $contracts = Contract::query()->pluck('title', 'id')->toArray();
        //order_at 下单时间=合同时间 文章数
        $last5Sites = Site::query()->whereIn('id', $validSiteIds)->withCount(['articles', 'linkTasks'])
            ->with(['users'])->limit(5)->orderByDesc('order_at')->get();

        //top10 首页 top30 前三页
        //rank 205 project doKeyword line
        $oldIds = array_filter($last5Sites->pluck('old_id')->toArray());
//        $oldIds = [90];
        $last5SitesKeywords = $rankDatabase->table('project_keyword')->selectRaw('
            SUM(CASE WHEN google_rank <= 10 THEN 1 ELSE 0 END) as top10,
            SUM(CASE WHEN google_rank <= 30 THEN 1 ELSE 0 END) as top30,
            SUM(CASE WHEN google_rank <= 50 THEN 1 ELSE 0 END) as top100,
            project_id
        ')->where('google_rank', '<>', 9999)->whereIn('project_id', $oldIds)
            ->groupBy('project_id')->get()->keyBy('project_id')->toArray();

        foreach ($last5Sites as $lastSite) {

            $contractIds = $lastSite->contract_ids ?? [];
            $lastSite->contractsTitle = implode(',', array_map(function ($item) use (&$contracts) {
                return $contracts[$item];
            }, $contractIds));

//            $lastSite->keywords = $last5SitesKeywords[90] ?? [];
            $lastSite->keywords = $last5SitesKeywords[$lastSite->old_id] ?? [];
            //到期时间 达标日期往后推10个月
//            $lastSite->is_expired = (time() > strtotime('+10 month', strtotime($lastSite['reach_at'])));
            $lastSite->expired_time = $lastSite['reach_at'] ?
                strtotime('+10 month', strtotime($lastSite['reach_at'])) : null;


            //引擎力顾问
            $lastSite->managesInfo = $lastSite->users->where('role_id', Role::TYPE_MANAGER);

            $lastSite->serverUsersArray = array_values($lastSite->users->where('role_id', Role::TYPE_SERVER)->toArray());
        }
        return $last5Sites;
    }

    public function indexDropKeywordSites(ConnectionInterface $rankDatabase, &$validSiteIds)
    {
        //关键词排名急剧下降
        $nowMonthKeywords = $rankDatabase->table('project_history')->selectRaw('max(create_time),top10,project_id')->where([
            ['create_time', '>', strtotime(date('Y-m-01'))],
            ['create_time', '<', strtotime(date('Y-m-t 23:59:59'))],
        ])->groupBy('project_id')->pluck('top10', 'project_id')->toArray();

        $lastMonthKeywords = $rankDatabase->table('project_history')->selectRaw('max(create_time),top10,project_id')->where([
            ['create_time', '>', strtotime(date('Y-m-01', strtotime('-1 month')))],
            ['create_time', '<', strtotime(date('Y-m-t 23:59:59', strtotime('-1 month')))],
        ])->groupBy('project_id')->pluck('top10', 'project_id')->toArray();

        $dropResults = [];

        foreach ($lastMonthKeywords as $inx => $val) {
            $nowTop10 = $nowMonthKeywords[$inx] ?? 0;
            $lessVal = $val - $nowTop10;
            if ($lessVal > 20) {
                $dropResults[$inx] = ['project_id' => $inx, 'nowTop10' => $nowTop10, 'lastTop10' => $val];
            }
        }

        $keyDropSites = Site::query()->with('users')->select(['id', 'old_id', 'domain', 'cn_title'])->whereIn('id', $validSiteIds)
            ->whereIn('old_id', array_column($dropResults, 'project_id'))->get()->keyBy('id');
        foreach ($keyDropSites as $item){
            $item->dropResults = $dropResults[$item->old_id];

            $item->managers_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGER)->pluck('nickname')->toArray());
            $item->optimizerTitle = implode('-', $item->users->where('role_id', Role::TYPE_OPTIMIZER)->pluck('nickname')->toArray());
        }

        return $keyDropSites;
    }

    public function indexReachSites(&$validSiteIds, $dateRangeFilter = null)
    {
        //关键词达标的 sites_process.process_id=17  关键词达标
        $builder = Site::query()->select(['reach_at', 'cn_title', 'id', 'domain'])->with(['sitesProcess' => function (HasMany $builder) {
            $builder->where(['process_id' => 17]);
        }, 'sitePayment'])->whereExists(function (Builder $builder) {
            $builder->select(DB::raw(1))->from('sites_process')
                ->whereRaw('sites.id=sites_process.site_id AND sites_process.process_id=17 AND sites_process.active=1');
        });

        if ($dateRangeFilter) {
            $builder->where($dateRangeFilter);
        } else {
            $builder->where([['reach_at', '<', date('Y-m-d H:i:s')]]);
        }
        return $builder->whereIn('id', $validSiteIds)->get();
    }

    public function indexBuildDoneSites(&$validSiteIds, $dateRangeFilter = null)
    {
        ////建站完成的  process_id_15网站上线
        $builder = Site::query()->select(['reach_at', 'cn_title', 'id', 'domain'])->with(['sitesProcess' => function (HasMany $builder) {
            $builder->where(['process_id' => 15]);
        }])->whereExists(function (Builder $builder) {
            $builder->select(DB::raw(1))->from('sites_process')
                ->whereRaw('sites.id=sites_process.site_id AND sites_process.process_id=15 AND sites_process.active=1');
        });
        if ($dateRangeFilter) {
            $builder->where($dateRangeFilter);
        }

        return $builder->whereIn('id', $validSiteIds)->get();

    }

    public function indexMasterMeterTrafficsRemark(ConnectionInterface $rankDatabase, &$validSiteIds)
    {
        $masterMeterRecords = MasterMeter::query()->where([
            ['ym', '=', date('Ym')],
            ['traffic', '>=', 300]
        ])->whereIn('site_id', $validSiteIds)->get()->groupBy('site_id');

        $masterMeterRecords = $masterMeterRecords->map(function (Collection $items) {
            return $items->last();
        });
        $masterMeterRecordsList = $masterMeterRecords->toArray();

        $oldIds = array_column($masterMeterRecordsList, 'old_id');


//        $rankDatabase->table('project_history')->select('top10')->orderByDesc('create_time')->first();


        $top10ListMap = $rankDatabase->table('project_keyword')
            ->selectRaw('SUM(CASE WHEN google_rank <= 10 THEN 1 ELSE 0 END) as top10,project_id')->whereIn('project_id', $oldIds)
            ->groupBy('project_id')->get()->keyBy('project_id')->toArray();

        array_walk($masterMeterRecordsList, function (&$record) use (&$top10ListMap) {
            $record['top10'] = '-';
            foreach ($top10ListMap as $item) {
                if ($item->project_id == $record['old_id']) {
                    $record['top10'] = $item->top10;
                }
            }
        });


        $groupList = [300 => [], 500 => [], 1000 => []];
        foreach ($masterMeterRecordsList as $item) {
            if ($item['traffic'] >= 300 && $item['traffic'] < 500) {
                $groupList[300][] = $item;
            }
            if ($item['traffic'] >= 500 && $item['traffic'] < 1000) {
                $groupList[500][] = $item;
            }
            if ($item['traffic'] >= 1000) {
                $groupList[1000][] = $item;
            }
        }
        return $groupList;
    }


    public function indexMasterMeterTraffics(ConnectionInterface $rankDatabase, &$validSiteIds)
    {
        $masterMeterRecords = MasterMeter::query()->where([
            ['ym', '=', date('Ym')],
            ['traffic', '>=', 300]
        ])->whereIn('site_id', $validSiteIds)->get()->groupBy('site_id');

        $masterMeterRecords = $masterMeterRecords->map(function (Collection $items) {
            return $items->last();
        });
        $oldIdMapProjects = $masterMeterRecords->keyBy('old_id')->toArray();

        $masterMeterRecordsList = $masterMeterRecords->toArray();

        $oldIds = array_column($masterMeterRecordsList, 'old_id');


        $top10ListMap = $rankDatabase->table('project_keyword')
            ->selectRaw('SUM(CASE WHEN google_rank <= 10 THEN 1 ELSE 0 END) as top10,project_id')->whereIn('project_id', $oldIds)
            ->groupBy('project_id')->get()->keyBy('project_id')->toArray();

//        array_walk($masterMeterRecordsList, function (&$record) use (&$top10ListMap) {
//            $record['top10'] = '-';
//            foreach ($top10ListMap as $item) {
//                if ($item->project_id == $record['old_id']) {
//                    $record['top10'] = $item->top10;
//                }
//            }
//        });
//

        $oldIdMapClicks = $rankDatabase->table('webmaster_effect')->selectRaw('SUM(clicks) as clicks,project_id')
            ->whereIn('project_id', $oldIds)->where(['ym' => date('Ym', strtotime('first day of -1 month'))])
            ->groupBy('project_id')->get()->keyBy('project_id')->toArray();

        $groupList = [300 => [], 500 => [], 1000 => []];

        foreach ($oldIdMapClicks as $inx => $item) {
            $clicks = intval($item->clicks);
            if ($clicks >= 300 && $clicks < 500) {
                $groupList[300][] = $oldIdMapProjects[$inx] + ['clicks' => $clicks] + ['top10' => $top10ListMap[$inx]->top10 ?? 0];
            }
            if ($clicks >= 500 && $clicks < 1000) {
                $groupList[500][] = $oldIdMapProjects[$inx] + ['clicks' => $clicks] + ['top10' => $top10ListMap[$inx]->top10 ?? 0];
            }
            if ($clicks >= 1000) {
                $groupList[1000][] = $oldIdMapProjects[$inx] + ['clicks' => $clicks] + ['top10' => $top10ListMap[$inx]->top10 ?? 0];
            }
        }
        return $groupList;
    }


    public function indexKeywordTotal(ConnectionInterface $rankDatabase, &$validOldSiteIds)
    {
        //'project.number' => 1, //1,2,3
        // //关键词总量
        return $rankDatabase->table('project_keyword')
            ->leftJoin('project', 'project.id', '=', 'project_keyword.project_id')
            ->where(['project.allow_googlerank' => 1])
            ->whereIn('project_id', $validOldSiteIds)
            ->count();
    }

    public function indexSiteStatus(&$validSiteIds)
    {
        //"上线确认之后"就是建站期结束进入优化期，达标确认之后就是优化期结束进入服务期
        //建站期项目数 15 网站上线
//        $onlineSiteIds = SiteProcess::query()->where(['process_id' => 15, 'active' => 1])->pluck('site_id')->toArray();
//        $onlineCount = Site::query()->where(['status' => 1])->whereNotIn('id', $onlineSiteIds)->count();

        $onlineCount = Site::query()->whereIn('id', $validSiteIds)->where(['status' => 1])->count();


        //实施期项目数   seo检查16  关键词达标17 处理中期间则说明处于实施期
//        $implementSiteIds = SiteProcess::query()->where(['process_id' => 16, 'active' => 2])->orWhere([
//            'process_id' => 17, 'active' => 2
//        ])->pluck('site_id')->toArray();
//        $implementCount = Site::query()->where(['status' => 1])->whereIn('id', $implementSiteIds)->count();

        $implementCount = Site::query()->whereIn('id', $validSiteIds)->where(['status' => 2])->count();


        //服务期项目数
//        $serviceSiteIds = SiteProcess::query()->where(['process_id' => 17, 'active' => 1])->pluck('site_id')->toArray();
//        $serviceCount = Site::query()->where(['status' => 1])->whereIn('id', $serviceSiteIds)->count();

        $serviceCount = Site::query()->whereIn('id', $validSiteIds)->where(['status' => 3])->count();


        //暂停的项目数
        $pauseCount = Site::query()->whereIn('id', $validSiteIds)->where(['status' => 4])->count();


        return [
            'xAxis' => ['建站期', '实施期', '服务期', '暂停期'],
            'yAxis' => [$onlineCount, $implementCount, $serviceCount, $pauseCount],
            'hybrid' => [
                ['name' => '建站期', 'value' => $onlineCount],
                ['name' => '实施期', 'value' => $implementCount],
                ['name' => '服务期', 'value' => $serviceCount],
                ['name' => '暂停期', 'value' => $pauseCount],
            ]
        ];
    }

    public function indexNature(&$validSiteIds)
    {
        //公司类型统计
        $natureCount = Site::query()->selectRaw('COUNT(id) as total,nature_type')
            ->whereIn('id', $validSiteIds)
            ->groupBy('nature_type')->get()->toArray();

        $natureCountMap = array_column($natureCount, 'total', 'nature_type');

        $natureHybrid = array_map(function ($item) use ($natureCountMap) {
            return [
                'name' => $item['val'],
                'value' => $natureCountMap[$item['inx']] ?? 0
            ];
        }, Site::NATURE_TYPE);

        return [
            'name' => array_column(Site::NATURE_TYPE, 'val'),
            'hybrid' => $natureHybrid
        ];
    }

    public function indexBusiness(&$validSiteIds)
    {
        //根据公司行业
        $businessCount = Site::query()->selectRaw('COUNT(id) as total,business_id')
            ->whereIn('id', $validSiteIds)->groupBy('business_id')->get()->toArray();
        $businessCountMap = array_column($businessCount, 'total', 'business_id');

        $businessMap = Business::query()->select(['title', 'id'])->get()->toArray();

        $businessHybrid = array_map(function ($item) use ($businessCountMap) {
            return [
                'name' => $item['title'],
                'value' => $businessCountMap[$item['id']] ?? 0
            ];
        }, $businessMap);

        return [
            'name' => array_column($businessMap, 'title'),
            'hybrid' => $businessHybrid
        ];

    }

    protected function lastSixMonths()
    {
        $sixMonth = [];
        for ($i = 0; $i < 6; $i++) {
            $sixMonth[] = date("Y-m", strtotime("first day of -$i month"));
        }
        return $sixMonth;
    }


    protected function lastSixMonthsASC()
    {
        $sixMonth = [];
        for ($i = 5; $i >= 0; $i--) {
            $sixMonth[] = date("Y-m", strtotime("first day of -$i month"));
        }
        return $sixMonth;
    }

    protected function passSixMonthsASC()
    {
        $sixMonth = [];
        for ($i = 6; $i >= 1; $i--) {
            $sixMonth[] = date("Y-m", strtotime("first day of -$i month"));
        }
        return $sixMonth;
    }

    public function indexSales(&$validSiteIds)
    {

        $channelType = [Role::TYPE_CHANNEL_BOSS, Role::TYPE_CHANNEL_MANAGER, Role::TYPE_CHANNEL_WORKER];
        //渠道销售占比
        $channelSales = Site::query()->whereIn('id', $validSiteIds)->selectRaw('COUNT(id) as total,LEFT(order_at,7) as orderYm')->whereExists(function (Builder $builder) use ($channelType) {
            $builder->select(DB::raw(1))->from('users')
//                ->whereRaw(sprintf('users.id=sites.creator_id AND users.role_id=%s', Role::TYPE_CHANNEL));
                ->whereRaw(sprintf('users.id=sites.creator_id AND users.role_id in (%s)', implode(',', $channelType)));
        })->groupBy('orderYm')->orderByDesc('orderYm')->get()->pluck('total', 'orderYm')->toArray();

        $notChannelSales = Site::query()->whereIn('id', $validSiteIds)->selectRaw('COUNT(id) as total,LEFT(order_at,7) as orderYm')->whereNotExists(function (Builder $builder) use ($channelType) {
            $builder->select(DB::raw(1))->from('users')
//                ->whereRaw(sprintf('users.id=sites.creator_id AND users.role_id=%s', Role::TYPE_CHANNEL));
                ->whereRaw(sprintf('users.id=sites.creator_id AND users.role_id in (%s)', implode(',', $channelType)));
        })->groupBy('orderYm')->orderByDesc('orderYm')->get()->pluck('total', 'orderYm')->toArray();

        $lastSixMonths = $this->lastSixMonths();

        $channelResults = $notChannelResults = [];
        foreach ($lastSixMonths as $month) {
            $channelResults[$month] = $channelSales[$month] ?? 0;
            $notChannelResults[$month] = $notChannelSales[$month] ?? 0;
        }

        return [
            'xAxis' => $lastSixMonths,
            'channelSales' => array_values($channelResults),
            'notChannelSales' => array_values($notChannelResults)
        ];
    }

    public function indexCertExpiredSites(&$validSiteIds)
    {
        //证书过期项目
        return $certExpiredSites = Site::query()->whereIn('id', $validSiteIds)
            ->whereDate('cert_expired_date', '<', date('Y-m-d', strtotime('+1 month')))->get();
    }

    public function indexDomainExpiredSites(&$validSiteIds)
    {
        //域名过期项目
        return $domainExpiredSites = Site::query()->whereIn('id', $validSiteIds)
            ->whereDate('domain_expired_date', '<', date('Y-m-d', strtotime('+1 month')))->get();
    }

    public function indexLinksLine()
    {
        $linkTypes = Link::TYPES + [0 => '已过期'];
        $linkRecords = Link::query()->selectRaw('COUNT(id) as total,type')
            ->groupBy('type')->pluck('total', 'type')->toArray();
        $typeTotals = [];
        foreach ($linkTypes as $key => $val) {
            $typeTotals[$key] = $linkRecords[$key] ?? 0;
        }
        $typeTotals[0] = Link::onlyTrashed()->count();//获取已删除的
        return [
            'xAxis' => array_values($linkTypes),
            'yAxis' => array_values($typeTotals),
        ];
    }

    public function indexExpireSiteProcessList(&$validSiteIds)
    {
        //流程过期提醒
        return SiteProcess::query()->with(['process', 'site'])->where([
            ['active', '=', 2],
            ['should_end_at', '<', date('Y-m-d H:i:s')],
        ])->whereIn('site_id', $validSiteIds)->get();
    }


    const CUSTOMER_FIELD_MAP = [
        'addition' => [ //网站信息
            'domain_type' => '新域名注册',
            'new_domain' => '新域名信息',

            'old_domain' => '旧域名信息-域名',
            'old_url' => '旧域名信息-注册网址',
            'old_account' => '旧域名信息-登陆账号',
            'old_password' => '旧域名信息-密码',
            'old_expired' => '旧域名信息-域名到期时间',

            'site_url' => '网站后台账号-后台网址',
            'site_account' => '网站后台账号-用户名',
            'site_password' => '网站后台账号-密码',

            'ftp_account' => 'FTP-FTP Username',
            'ftp_domain' => 'FTP-FTP 服务器',
            'ftp_password' => 'FTP-登录密码',
            'ftp_port' => 'FTP-连接端口',
            'ftp_ip' => 'FTP-IP地址',
        ],
        'enterprise' => [ //项目基础信息
            'export_region' => '主要出口国家或市场',
            'business_id' => '所属行业',
            'nature_type' => '公司类型',
            'customer_scope' => '客户群体',
            'brand' => '品牌',
            'cn_title' => '公司名称（中文）',
            'cn_profile' => '公司简介（中文）',
            'en_title' => '公司名称（英文）',
            'en_profile' => '公司简介（英文）',
            'cn_address' => '公司地址(中文)',
            'en_address' => '公司地址(英文)',
            'setup_time' => '公司成立时间',
            'postcode' => '邮编',
            'line_phone' => '公司座机',
            'fax' => '公司传真',
            'email' => '公司邮箱',
            'deliver_period' => '发货期',
            'supply' => '供应量',
            'min_supply' => '最低供应量',
            'price_range' => '价格区间',
            'packing_way' => '包装方式',
            'pay_way' => '支付方式*',

            'skype' => '聊天工具-Skype账号',
            'whats_app' => '聊天工具-Skype账号',
            'wechat' => '聊天工具-Wechat 二维码',
            'qq' => '聊天工具-QQ',
            'other' => '聊天工具-其他',
        ],
        'station' => [ //其他项目信息
            'portal_sites' => '门户网站',
            'compete_sites' => '竞争对手网址',
            'other_sites' => '公司其他网站',
        ],
        'keyword' => [
            'keyword' => '核心关键词'
        ],
        'marketer' => [
            'en_name' => '联系人英文姓名',
            'en_profile' => '联系人简介英文',
            'birthday' => '联系人出生年月',
            'sex_text' => '联系人性别 ',
            'position' => '联系人职位 ',
            'department' => '联系人部门',
            'contact' => '联系人电话',

            'enterprise_login_url' => '企业邮箱外链用-登陆地址',
            'enterprise_email' => '企业邮箱外链用-邮箱',
            'enterprise_email_password' => '企业邮箱外链用-密码',

            'gmail_account' => 'Gmail-邮箱',
            'gmail_password' => 'Gmail-密码',
            'gmail_contact' => 'Gmail-验证手机号码',
            'gmail_assist' => 'Gmail-辅助邮箱',
        ],
        'sns' => [
            'youtube_url' => 'Youtube-展示url地址:',
            'youtube_account' => 'Youtube-用户名（登入邮箱）',
            'youtube_password' => 'Youtube-密码',
            'facebook_url' => 'Facebook-企业page展示url地址',
            'facebook_account' => 'Facebook-用户名（登入邮箱)',
            'facebook_password' => 'Facebook-密码',
            'twitter_url' => 'Twitter-展示url地址',
            'twitter_account' => 'Twitter-用户名（登入邮箱）',
            'twitter_password' => 'Twitter-密码',
            'linkedin_url' => 'linkedin-企业page展示url地址',
            'linkedin_account' => 'linkedin-用户名（登入邮箱）',
            'linkedin_password' => 'linkedin-密码',
            'pinterst_url' => 'Pinterst-展示url地址',
            'pinterst_account' => 'Pinterst-用户名（登入邮箱）',
            'pinterst_password' => 'Pinterst-密码',
        ]
    ];

    public function indexCustomerLog(&$validSiteIds)
    {
        $business = Business::query()->pluck('title', 'id')->toArray();

        $natureTypes = array_column(Site::NATURE_TYPE, 'val', 'inx');

        $records = CustomerLog::query()->whereIn('site_id', $validSiteIds)->get();
        foreach ($records as $key => $record) {


            $content = unserialize($record->content);
            $oldContent = unserialize($record->old_content);


            $mapFields = self::CUSTOMER_FIELD_MAP[$record->type] ?? [];
            $resultOldContent = '';
            foreach ($oldContent as $inx => $item) {

                if ($record->type == 'enterprise') {
                    if ($inx == 'business_id') {
                        $item = $business[$item] ?? '';
                    }
                    if ($inx == 'nature_type') {
                        $item = $natureTypes[$item] ?? '';
                    }
                }

                if ($record->type == 'station') {
                    if (is_array($item)) {
                        $item = implode(',', $item);
                    }
                }

                $resultOldContent .= sprintf('%s：%s', ($mapFields[$inx] ?? ''), $item) . '，';
            }

            $resultContent = '';
            foreach ($content as $inx => $item) {

                if ($record->type == 'enterprise') {
                    if ($inx == 'business_id') {
                        $item = $business[$item] ?? '';
                    }
                    if ($inx == 'nature_type') {
                        $item = $natureTypes[$item] ?? '';
                    }
                }

                if ($record->type == 'station' && is_array($item)) {

                    if (count($item) == count($item, 1)) {
                        $item = implode(',', $item);
                    } else {
                        $tempStr = '';
                        foreach ($item as $k => $v) {
                            $tempStr .= ($v['url'] ?? '') . ($v['remark'] ?? '') . ',';
                        }
                        $item = $tempStr;
                    }
                }

                $resultContent .= sprintf('%s：%s', ($mapFields[$inx] ?? ''), $item) . '，';
            }

            $record->detail = sprintf('[%s]修改了[%s]-修改之前数据为%s修改之后数据为%s', $record->operator_name, $record->title, $resultOldContent, $resultContent);
        }

        return $records;
    }

    public function indexTokenExpired()
    {
        //linked token过期提醒
//        $linkedExpired = Social::query()->where([['type', '=', 'linkedIn'], ['token_expired_at', '<', date('Y-m-d H:i:s', strtotime('+5 days'))]])->exists();
//        if ($linkedExpired) {
//            //todo
//        }
    }


    public function singleReachDays($site)
    {
        //关键词达标的 17 关键词达标
        $nowDatetime = new \DateTime();
        $reachDatetime = new \DateTime($site->reach_at);
        $interval = $reachDatetime->diff($nowDatetime);
        $diffDays = $interval->format('%R%a');
        return $diffDays;
    }

    public function singleBuildDone($siteId)
    {
        //建站完成的  15网站上线 未打分的
        return SiteProcess::query()->where([
            'process_id' => 15,
            'active' => 1,
            'evaluate' => null,
            'site_id' => $siteId
        ])->first();
    }

    public function singleUrlFailed($siteId)
    {
        //网址异常
        return UrlCheck::query()->where([
            'site_id' => $siteId
        ])->first();
    }

    public function singleKeywordLessVal(ConnectionInterface $rankDatabase, $oldId)
    {
        if (!$oldId) {
            return 0;
        }
        //关键词排名急剧下降
        $nowMonthKeyword = $rankDatabase->table('project_history')->selectRaw('top10')->where([
            ['project_id', '=', $oldId],
            ['create_time', '>', strtotime(date('Y-m-01'))],
            ['create_time', '<', strtotime(date('Y-m-t 23:59:59'))],
        ])->orderByDesc('create_time')->first();

        $lastMonthKeyword = $rankDatabase->table('project_history')->selectRaw('top10')->where([
            ['project_id', '=', $oldId],
            ['create_time', '>', strtotime(date('Y-m-01', strtotime('-1 month')))],
            ['create_time', '<', strtotime(date('Y-m-t 23:59:59', strtotime('-1 month')))],
        ])->orderByDesc('create_time')->first();

        return ($nowMonthKeyword->top10 ?? 0) - ($lastMonthKeyword->top10 ?? 0);
//        return -10;
    }

    public function singleSiteProfile(ConnectionInterface $rankDatabase, $site)
    {
        //项目概况
        //合作项目
        $contracts = Contract::query()->pluck('title', 'id')->toArray();
        $contractIds = $site->contract_ids ?? [];
        $site->contractsTitle = implode(',', array_map(function ($item) use (&$contracts) {
            return $contracts[$item];
        }, $contractIds));
        //引擎力顾问
        $site->managesInfo = $site->users->where('role_id', Role::TYPE_MANAGER);
        //到期时间
//        $site->is_expired = (time() > strtotime('+10 month', strtotime($site->reach_at)));
//        $site->expired_at = date('Y-m-d H:i:s', strtotime('+10 month', strtotime($site->reach_at)));
        $site->expired_time = $site->reach_at ? strtotime('+10 month', strtotime($site->reach_at)) : null;
        //首页 前三页 前10页
        if ($site->old_id) {
            $topKeywords = $rankDatabase->table('project_keyword')->selectRaw('
            SUM(CASE WHEN google_rank <= 10 THEN 1 ELSE 0 END) as top10,
            SUM(CASE WHEN google_rank <= 30 THEN 1 ELSE 0 END) as top30,
            SUM(CASE WHEN google_rank <= 50 THEN 1 ELSE 0 END) as top100
        ')->where('google_rank', '<>', 9999)->where('project_id', $site->old_id)->first();
        }

        $site->topKeywords = $topKeywords ?? null;
    }

    public function singleMyArticles($siteId)
    {   //我的文章
        $myArticles = Article::query()->with(['site', 'translator'])->where(['site_id' => $siteId])->orderByDesc('id')->limit(5)->get();
        $myArticles->each(function ($item) {
            $item->status_title = $item->status_with_css;
            $item->site_title = $item->site->cn_title ?? '';
            $item->translator_name = $item->translator->username ?? '';
        });
        return $myArticles;
    }

    public function singleMyLinksTasks($siteId)
    {
        //我的外链任务
        $myLinkTasks = LinkTask::query()->with(['site', 'worker'])->where(['site_id' => $siteId])->orderByDesc('id')->limit(5)->get();
        $myLinkTasks->each(function ($item) {
            $item->worker_name = $item->worker->username ?? '';
            $item->site_title = $item->site->cn_title ?? '';
            $item->status_title = $item->status_with_css;
        });

        return $myLinkTasks;
    }

    public function singleTrafficLine(ConnectionInterface $masterDatabase)
    {
        $lastSixMonths = $this->passSixMonthsASC();
        //流量折线图
        $trafficVal = [];
        $traffic = $masterDatabase->table('traffic_report_hourly')->selectRaw('SUM(pv) AS pv,FROM_UNIXTIME(timestamp,"%Y-%m") as Ym')->where([
        ])->groupBy('Ym')->orderByDesc('Ym')->get()->pluck('pv', 'Ym')->toArray();

        foreach ($lastSixMonths as $month) {
            $trafficVal[$month] = $traffic[$month] ?? 0;
        }
        return [
            'xAxis' => $lastSixMonths,
            'yAxis' => array_values($trafficVal)
        ];
    }


    public function twoCompare($one, $two)
    {
        $result = $one - $two;

        if ($result === 0) {
            $status = 'same';
        } elseif ($result > 0) {
            $status = 'up';
        } else {
            $status = 'down';
        }

        return [
            'one' => $one,
            'two' => $two,
            'diff' => abs($result),
            'status' => $status
        ];
    }


    public function passTwoMonth()
    {
        $passTwoMonth = [];
        for ($i = 1; $i <= 2; $i++) {
            $passTwoMonth[] = date("Ym", strtotime("first day of -$i month"));
        }
        return $passTwoMonth;
    }

    public function singleCompare($last1, $last2, $last3)
    {
        $last2Diff = $last1 - $last2;

        if ($last2Diff === 0) {
            $last2Status = 'same';
        } elseif ($last2Diff > 0) {
            $last2Status = 'up';
        } else {
            $last2Status = 'down';
        }

        $last3Diff = $last1 - $last3;


        if ($last3Diff === 0) {
            $last3Status = 'same';
        } elseif ($last3Diff > 0) {
            $last3Status = 'up';
        } else {
            $last3Status = 'down';
        }


        return [
            'last1' => $last1,
            'last2' => [
                'val' => $last2,
                'diff' => abs($last2Diff),
                'status' => $last2Status
            ],
            'last3' => [
                'val' => $last3,
                'diff' => abs($last3Diff),
                'status' => $last3Status
            ]
        ];
    }

    public function singlePvUvcData(ConnectionInterface $masterDatabase)
    {

        $pvList = [];
        $uvList = [];
        for ($i = 1; $i <= 3; $i++) {
            $result = $masterDatabase->table('traffic_report_hourly')->selectRaw('SUM(pv) AS pv,SUM(uv) AS uv')
                ->where([
                    ['timestamp', '>', strtotime(date('Y-m-d 00:00:00', strtotime('first day of -' . $i . ' month')))],
                    ['timestamp', '<', strtotime(date('Y-m-d 23:59:59', strtotime('last day of -' . $i . ' month')))]
                ])->first();
            $pvList[] = $result->pv ?? 0;
            $uvList[] = $result->uv ?? 0;
        }

        $totalResult = $masterDatabase->table('traffic_report_hourly')->selectRaw('SUM(pv) AS pv,SUM(uv) AS uv')->first();

        $pvResult = $this->singleCompare($pvList[0], $pvList[1], $pvList[2]);
        $uvResult = $this->singleCompare($uvList[0], $uvList[1], $uvList[2]);

        return [
            'pv' => $pvResult,
            'uv' => $uvResult,
            'pvTotal'=>$totalResult->pv??0,
            'uvTotal'=>$totalResult->uv??0,

        ];
    }

    public function singleTopKeywordData(ConnectionInterface $rankDatabase, $oldId)
    {

        $top10List = [];
        $top30List = [];
        $top50List = [];
        for ($i = 1; $i <= 3; $i++) {
            $start = strtotime(date('Y-m-d 00:00:00', strtotime('first day of -' . $i . ' month')));
            $end = strtotime(date('Y-m-d 23:59:59', strtotime('last day of -' . $i . ' month')));

            $result = $rankDatabase->table('project_history')->where(['project_id' => $oldId])->where([
                ['create_time', '>', $start],
                ['create_time', '<', $end]
            ])->orderByDesc('create_time')->first();

            $top10List[] = $result->top10 ?? 0;
            $top30List[] = $result->top30 ?? 0;
            $top50List[] = $result->top50 ?? 0;
        }

        $top10Res = $this->singleCompare($top10List[0], $top10List[1], $top10List[2]);
        $top30Res = $this->singleCompare($top30List[0], $top30List[1], $top30List[2]);
        $top50Res = $this->singleCompare($top50List[0], $top50List[1], $top50List[2]);

        return [
            'top10' => $top10Res,
            'top30' => $top30Res,
            'top50' => $top50Res,
            'top10History' => $top10List
        ];

    }

    public function singleInquireData(ConnectionInterface $masterDatabase)
    {

        $resultList = [];
        $one_last = strtotime(date('Y-m-d 23:59:59', strtotime('last day of - 1 month')));
        $two_last = strtotime(date('Y-m-d 23:59:59', strtotime('last day of - 2 month')));
        $three_last = strtotime(date('Y-m-d 23:59:59', strtotime('last day of - 3 month')));
        $one_begin = strtotime(date('Y-m-01 00:00:00', strtotime('last day of - 1 month')));
        $two_begin = strtotime(date('Y-m-01 00:00:00', strtotime('last day of - 2 month')));
        $three_begin = strtotime(date('Y-m-01 00:00:00', strtotime('last day of - 3 month')));
//        for ($i = 1; $i <= 3; $i++) {
//            $resultList[] = $masterDatabase->table('user_msg')
//                ->where([
//                    ['is_delete', '=', 0],
//                    ['create_time', '<', strtotime(date('Y-m-d 23:59:59', strtotime('last day of -' . $i . ' month')))]
//                ])->count();
//        }
        $resultList[0]=$resultList[] = $masterDatabase->table('user_msg')
                ->where([
                    ['is_delete', '=', 0],
                ])->whereBetween('create_time',[$one_begin,$one_last])->count();
        $resultList[1]=$resultList[] = $masterDatabase->table('user_msg')
            ->where([
                ['is_delete', '=', 0],
            ])->whereBetween('create_time',[$two_begin,$two_last])->count();
        $resultList[2]=$resultList[] = $masterDatabase->table('user_msg')
            ->where([
                ['is_delete', '=', 0],
            ])->whereBetween('create_time',[$three_begin,$three_last])->count();
        var_dump($resultList);
        $res = $this->singleCompare($resultList[0], $resultList[1], $resultList[2]);

        return $res;
    }

    public function singleWebmasterEffectData(ConnectionInterface $rankDatabase, $oldId)
    {
        $pass3Month = [];
        for ($i = 1; $i <= 3; $i++) {
            $pass3Month[] = date('Ym', strtotime('first day of -' . $i . ' month'));
        }

        $results = $rankDatabase->table('webmaster_effect')->selectRaw('ym,SUM(clicks) as clicks,SUM(impressions) AS impressions')
            ->where(['project_id' => $oldId])->whereIn('ym', $pass3Month)->groupBy('ym')->get()->keyBy('ym')->toArray();
        $last1 = empty($results[$pass3Month[0]]) ? 0 : $results[$pass3Month[0]]->impressions;
        $last2 = empty($results[$pass3Month[1]]) ? 0 : $results[$pass3Month[1]]->impressions;
        $last3 = empty($results[$pass3Month[2]]) ? 0 : $results[$pass3Month[2]]->impressions;
        $res = $this->singleCompare($last1, $last2, $last3);
        return $res;
    }


    public function singlePageCount(ConnectionInterface $masterDatabase)
    {
        $pageList = [];
        for ($i = 1; $i <= 3; $i++) {
            $end = strtotime(date('Y-m-d 23:59:59', strtotime('last day of -' . $i . ' month')));

            $pageList[] = $masterDatabase->table('content')->where(['is_enabled' => 1, 'redirect' => '', 'lang' => 'en'])
                ->whereNotIn('uri', ['404', 'search'])->where('create_time', '<=', $end)->count();

        }
        return $this->singleCompare($pageList[0], $pageList[1], $pageList[2]);

    }

    public function singleArticleRate($site)
    {
        $reachList = [];
        for ($i = 1; $i <= 3; $i++) {
            $end = strtotime(date('Y-m-d 23:59:59', strtotime('last day of -' . $i . ' month')));
            $reachList[] = Article::query()->where([
                ['site_id', '=', $site->id],
//                ['publish_at', '>', 0],
                ['publish_at', '<=', date('Y-m-d H:i:s', $end)],
            ])->count();
        }
        return [
            'goal' => $site->article_goal,
            'last1Reach' => $reachList[0],
            'last2Reach' => $reachList[1],
            'last3Reach' => $reachList[2],
        ];

    }

    public function singleLinkRate($site)
    {
        $reachList = [];
        for ($i = 1; $i <= 3; $i++) {
            $end = strtotime(date('Y-m-d 23:59:59', strtotime('last day of -' . $i . ' month')));
            $reachList[] = LinkTaskDetail::query()->selectRaw('link_id')->where([
                'redundant_site_id' => $site->id,
                'enable' => 1,
                'status' => 5
            ])->where('created_at', '<=', date('Y-m-d H:i:s', $end))->groupBy('link_id')->get()->count();
        };

        return [
            'goal' => $site->link_goal,
            'last1Reach' => $reachList[0],
            'last2Reach' => $reachList[1],
            'last3Reach' => $reachList[2],
        ];

    }

    public function singleTopKeywordGoalData()
    {

    }

    public function singleInquireLine(ConnectionInterface $masterDatabase)
    {
        //询盘折线图
        $lastSixMonths = $this->passSixMonthsASC();
        $inquiresVal = [];
        foreach ($lastSixMonths as $month) {
            $inquiresVal[$month] = $masterDatabase->table('user_msg')->where([
                ['is_delete', '=', '0'],
                ['create_time', '>=', strtotime($month)],
                ['create_time', '<=', strtotime(sprintf('%s 23:59:59', date('Y-m-t', strtotime($month))))]
            ])->count();
        }
        return [
            'xAxis' => $lastSixMonths,
            'yAxis' => array_values($inquiresVal)
        ];
    }

    public function singleKeywordLine(ConnectionInterface $rankDatabase, $oldId)
    {
        //关键词折线图
        $lastSixMonths = $this->passSixMonthsASC();
        $keywordVal = [];
        foreach ($lastSixMonths as $month) {
            if (!$oldId) {
                $keywordVal[$month] = 0;
                continue;
            }
            $history = $rankDatabase->table('project_history')
                ->selectRaw('top10')->where([
                    ['project_id', '=', $oldId],
                    ['create_time', '>=', strtotime($month)],
                    ['create_time', '<=', strtotime(sprintf('%s 23:59:59', date('Y-m-t', strtotime($month))))],
                ])
                ->orderByDesc('create_time')->first();
            $keywordVal[$month] = $history->top10 ?? 0;

        }
        return [
            'xAxis' => $lastSixMonths,
            'yAxis' => array_values($keywordVal)
        ];
    }

    public function getSingleSite()
    {
        $user = auth()->user();
        if ($user->is_super) {
            return Site::query()->first();
        }
        /** @var \App\Http\Models\User $user */
        return $user->sites()->first();
    }
}