test/app/Http/Controllers/Admin/Analyze/IndexController_bak.php

<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2020/1/7 0007
 * Time: 13:19
 */

namespace App\Http\Controllers\Admin\Analyze;

use App\Exports\BasicExport;
use App\Http\Constant\CacheConstant;
use App\Http\Controllers\Controller;
use App\Http\Models\Article;
use App\Http\Models\ArticleStatistic;
use App\Http\Models\Business;
use App\Http\Models\LinkStatistical;
use App\Http\Models\LinkTaskDetail;
use App\Http\Models\Role;
use App\Http\Models\Site;
use App\Http\Models\SitePayment;
use App\Http\Models\User;
use GuzzleHttp\Client;
use Illuminate\Http\Request;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Facades\Cache;
use Illuminate\Support\Facades\DB;

/**
 * 菜单栏 的 "数据"
 * Class IndexController
 * @package App\Http\Controllers\Admin\Analyze
 */
class IndexController extends Controller
{

    const NOT_RENEWED_MEMO_LIST = [
        0 => '',
        1 => '效果不好',
        2 => '疫情影响',
        3 => '业务转型',
        4 => '价格高',
        5 => '服务器不在引擎力',
    ];

    const REFUND_REASON_LIST = [
        0 => '',
        1 => '建站期太长',
        2 => '客户原因',
        3 => '客户原因/未启动',
        4 => '实施期中止',
        5 => '主动终止',
    ];

    //汇总
    public function overview(Request $request)
    {
        $rateFormat = function ($data) {
            return number_format($data, 2) * 100 . '%';
        };
        $thisMonth = [date('Y-m-01 00:00:00'), date('Y-m-t 23:59:59')];
        $lastMonth = [date('Y-m-01 00:00:00', strtotime('last month')), date('Y-m-d 23:59:59', strtotime(date('Y-m-01') . '-1 day'))];

        //销售签单
        $nowMonthOrder = Site::query()->whereBetween('order_at', [$thisMonth, $lastMonth])->count();
        //分配
        $nowMonthAssign = Site::query()->whereBetween('assign_at', [$thisMonth, $lastMonth])->count();
        //当月上线
        $nowMonthOnline = Site::query()->whereBetween('online_at', [$thisMonth, $lastMonth])->count();
        //当月达标
        $nowMonthReach = Site::query()->whereBetween('reach_at', [$thisMonth, $lastMonth])->count();
        //上线消耗
        $onlineConsume = $nowMonthOrder > 0 ? $rateFormat($nowMonthOnline / $nowMonthOrder) : '无';
        //达标消耗
        $reachConsume = $nowMonthOrder > 0 ? $rateFormat($nowMonthReach / $nowMonthOrder) : "无";
        //项目总数
        $totalSite = Site::query()->count();
        //未下单项目数
        $noOrderCount = Site::query()->whereNull('assign_at')->count();

        $statusMap = Site::STATUS_MAP;

        $statusGroup = Site::query()->selectRaw('COUNT(id) AS count,status')->whereIn('status', array_keys($statusMap))->groupBy('status')->get()->pluck('count', 'status');

        $statusResult = [];
        foreach ($statusMap as $inx => $item) {
            $statusResult[$inx] = [
                'count' => $statusGroup[$inx] ?? 0,
                'title' => $item
            ];
        }

        //上线消耗：（总项目数-建站期-建站期暂停）/ 总项目数
        //达标消耗: 服务期总数/ 总项目数
        $totalOnlineConsume = $rateFormat(($totalSite - ($statusGroup[1] ?? 0) - ($statusGroup[4] ?? 0)) / $totalSite);
        $totalReachConsume = $rateFormat(($statusGroup[3] ?? 0) / $totalSite);


        $startYear = sprintf('%s-01-01 00:00:00', date('Y'));
        $endYear = sprintf('%s-12-31 11:59:59', date('Y'));

        $yearRenewal = Site::query()->where([
            ['renewal_at', '>=', $startYear],
            ['renewal_at', '<=', $endYear],
        ])->count();

        $yearExpired = Site::query()->where([
            ['expired_at', '>=', $startYear],
            ['expired_at', '<=', $endYear],
        ])->count();

        $renewalConsume = $yearExpired ? $rateFormat($yearRenewal / $yearExpired) : "无";

        $threeHistoryOnline = [];
        $threeHistoryReach = [];

        for ($i = 0; $i < 3; $i++) {
            $startMonth = date('Y-m-d 00:00:00', strtotime('first day of -' . $i . ' month'));
            $endMonth = date('Y-m-d 23:59:59', strtotime('last day of -' . $i . ' month'));

            $threeHistoryOnline[] = Site::query()->where([
                ['online_at', '>=', $startMonth],
                ['online_at', '<=', $endMonth]
            ])->count();

            $threeHistoryReach[] = Site::query()->where([
                ['reach_at', '>=', $startMonth],
                ['reach_at', '<=', $endMonth]
            ])->count();
        }


        return view('admin/analyze/overview',
            compact('nowMonthOrder',
                'nowMonthAssign',
                'nowMonthOnline',
                'nowMonthReach',
                'onlineConsume',
                'reachConsume',
                'totalSite',
                'noOrderCount',
                'yearRenewal',
                'yearExpired',
                'renewalConsume',
                'threeHistoryOnline',
                'threeHistoryReach',
                'statusResult',
                'totalOnlineConsume',
                'totalReachConsume'
            ));
    }


    public function overviewAjax(Request $request)
    {
        $monthRange = $request->input('monthRange');

        $mr = explode(" - ", $monthRange);
        $start = $mr[0] ?? '';
        $end = $mr[1] ?? '';

        $rateFormat = function ($data) {
            return number_format($data, 2) * 100 . '%';
        };

        $nowMonthOrderWhere = [['order_at', '>=', date('Y-m-01 00:00:00')]];
        $nowMonthAssignWhere = [['assign_at', '>=', date('Y-m-01 00:00:00')]];
        $nowMonthOnlineWhere = [['online_at', '>=', date('Y-m-01 00:00:00')]];
        $nowMonthReachWhere = [['reach_at', '>=', date('Y-m-01 00:00:00')]];

        if ($start && $end) {
            $start = sprintf('%s-01 00:00:00', $start);
            $end = date('Y-m-t 23:59:59', strtotime(sprintf('%s-01 00:00:00', $end)));


            $nowMonthOrderWhere = [
                ['order_at', '>=', $start],
                ['order_at', '<=', $end],
            ];

            $nowMonthAssignWhere = [
                ['assign_at', '>=', $start],
                ['assign_at', '<=', $end],
            ];
            $nowMonthOnlineWhere = [
                ['online_at', '>=', $start],
                ['online_at', '<=', $end],
            ];
            $nowMonthReachWhere = [
                ['reach_at', '>=', $start],
                ['reach_at', '<=', $end],
            ];

        }
        //销售签单
        $nowMonthOrder = Site::query()->where($nowMonthOrderWhere)->count();

        $nowMonthAssign = Site::query()->where($nowMonthAssignWhere)->count();
        //当月上线
        $nowMonthOnline = Site::query()->where($nowMonthOnlineWhere)->count();
        //当月达标
        $nowMonthReach = Site::query()->where($nowMonthReachWhere)->count();
        //上线消耗
        $onlineConsume = $nowMonthOrder > 0 ? $rateFormat($nowMonthOnline / $nowMonthOrder) : '无';
        //达标消耗
        $reachConsume = $nowMonthOrder > 0 ? $rateFormat($nowMonthReach / $nowMonthOrder) : "无";

        return response()->json(['data' => compact(
            'nowMonthOrder',
            'nowMonthAssign',
            'nowMonthOnline',
            'nowMonthReach',
            'onlineConsume',
            'reachConsume'
        )]);
    }

    //下单
    public function placeOrder(Request $request)
    {
        if (!$request->ajax()) {
            return view('admin/analyze/place_order', [
                'services' => Role::getUsers(Role::TYPE_SERVER), //服务人员
                'editors' => Role::getUsers(Role::TYPE_AE),
                'managers' => Role::getUsers(Role::TYPE_MANAGER),
                'managerHelpers' => Role::getUsers(Role::TYPE_MANAGE_HELPER),
                'sellerUsers' => Role::getUsers(Role::TYPE_SELLER),
                'optimizers' => Role::getUsers(Role::TYPE_OPTIMIZER),
            ]);
        }

        $builder = $this->placeOrderBuilder();
        $sites = $builder->orderByDesc('order_at')->paginate($request->input('pageSize') ?? TABLE_PAGE_SIZE);
        $items = $sites->items();

        array_walk($items, function ($item) {
            $item->managers_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGER)->pluck('nickname')->toArray());
            $item->manage_helper_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGE_HELPER)->pluck('nickname')->toArray());
            $item->ae_title = implode('-', $item->users->where('role_id', Role::TYPE_AE)->pluck('nickname')->toArray());
            $item->optimizerTitle = implode('-', $item->users->where('role_id', Role::TYPE_OPTIMIZER)->pluck('nickname')->toArray());
            $item->bidTitle = implode('-', $item->users->where('role_id', Role::TYPE_BID)->pluck('nickname')->toArray());
            $item->articleTitle = implode('-', $item->users->where('role_id', Role::TYPE_PROPAGANDA)->pluck('nickname')->toArray());
            $item->designerTitle = implode('-', $item->users->where('role_id', Role::TYPE_DESIGNER)->pluck('nickname')->toArray());
            $item->webTitle = implode('-', $item->users->where('role_id', Role::TYPE_WEB)->pluck('nickname')->toArray());
            $item->server_title = implode('-', $item->users->where('role_id', Role::TYPE_SERVER)->pluck('nickname')->toArray());
            $item->seller_title = implode('-', $item->users->where('role_id', Role::TYPE_SELLER)->pluck('nickname')->toArray());

            $item->sign_at = substr($item->sign_at, 0, 10);
            $item->order_at = substr($item->order_at, 0, 10);
            $item->assign_at = substr($item->assign_at, 0, 10);
            $item->online_at = substr($item->online_at, 0, 10);
            $item->reach_at = substr($item->reach_at, 0, 10);
            $item->expired_at = substr($item->expired_at, 0, 10);
            $item->bq_at = substr($item->bq_at, 0, 10);
            $item->renewal_at = substr($item->renewal_at, 0, 10);
            $item->task_at = substr($item->task_at, 0, 10);
        });
        $result = [];
        foreach ($items as $item) {
            if (empty($item->order_at)) {
                $result[] = $item;
            }
        }

        foreach ($items as $item) {
            if (!empty($item->order_at)) {
                $result[] = $item;
            }
        }


        return response()->json([
            'rows' => $result,
            'total' => $sites->total()
        ]);
    }

    public function placeOrderBuilder()
    {
        $request = \request();
        $filterUserIds = [];
        $editorId = $request->input('editorId');
        $editorId && $filterUserIds[] = $editorId;
        $manageHelperId = $request->input('manageHelperId');
        $manageHelperId && $filterUserIds[] = $manageHelperId;
        $managerId = $request->input('managerId');
        $managerId && $filterUserIds[] = $managerId;
        $serverId = $request->input('serverId');
        $serverId && $filterUserIds[] = $serverId;
        $sellerId = $request->input('sellerId');
        $sellerId && $filterUserIds[] = $sellerId;

        $optimizerId = $request->input('optimizerId');
        $optimizerId && $filterUserIds[] = $optimizerId;

        $startDate = $request->input('startDate');
        $endDate = $request->input('endDate');

        $keyword = $request->input('keyword');
        $builder = Site::query()->with(['users'])->where(function (Builder $q) use ($keyword) {
            if ($keyword) {
                $q->where('domain', 'like', '%' . $keyword . '%')
                    ->orWhere('cn_title', 'like', '%' . $keyword . '%');
            }
        });

        if ($startDate) {
            $start = sprintf("%s-01 00:00:00", $startDate);
            $builder->where('order_at', '>=', $start);
        }

        if ($endDate) {
            $end = sprintf("%s-%s 23:59:59", $endDate, date('t', strtotime($endDate)));
            $builder->where('order_at', '<=', $end);
        }

        if ($startDate && $endDate) {
            $startDate = sprintf("%s-01 00:00:00", $startDate);
            $builder->where('order_at', '>=', $startDate)
                ->where('order_at', '<=', sprintf("%s-%s 23:59:59", $endDate, date('t', strtotime($endDate))));
        }

        if (!$startDate && !$endDate) {
            $builder->where(function (Builder $q) {
                $q->whereNull('order_at')->orWhere('order_at', '>=', date('Y-m-01 00:00:00'));
            });
        }

        if ($filterUserIds) {
            $builder->whereExists(function (\Illuminate\Database\Query\Builder $b) use ($filterUserIds) {
                $raw = sprintf('SUM(CASE WHEN user_id in (%s) then  1 ELSE  0 end) as total', implode(',', $filterUserIds));
                $b->select(DB::raw($raw))->from('user_has_sites')
                    ->whereRaw(sprintf('user_has_sites.site_id=sites.id HAVING total=%s', count($filterUserIds)));
            });
        }
        return $builder;
    }

    public function placeOrderExport()
    {
        $builder = $this->placeOrderBuilder();
        $sites = $builder->orderByDesc('order_at')->get()->toArray();

        $list = [];
        foreach ($sites as $key => $site) {
            $sellerTitle = [];//销售
            $serverTitle = [];//客服
            $managersTitle = [];//项目经理1
            $manageHelperTitle = [];//项目经理

            $aeTitle = [];//采编
            $optimizerTitle = [];//优化师
            $bidTitle = [];//竞价
            $articleTitle = [];//软文
            $designerTitle = [];//设计
            $webTitle = [];//前端

            $userList = $site['users'];
            foreach ($userList as $kk => $value) {
                if ($value['role_id'] == Role::TYPE_SELLER) {
                    $sellerTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_SERVER) {
                    $serverTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_MANAGER) {
                    $managersTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_MANAGE_HELPER) {
                    $manageHelperTitle [] = $value['nickname'];
                }

                if ($value['role_id'] == Role::TYPE_AE) {
                    $aeTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_OPTIMIZER) {
                    $optimizerTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_BID) {
                    $bidTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_PROPAGANDA) {
                    $articleTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_DESIGNER) {
                    $designerTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_WEB) {
                    $webTitle [] = $value['nickname'];
                }
            }

            $list[$key]['order_at'] = substr($site['order_at'], 0, 10);//下单时间
            $list[$key]['sign_at'] = substr($site['sign_at'], 0, 10);//合同签订时间
            $list[$key]['task_at'] = substr($site['task_at'], 0, 10);//首款

            $list[$key]['cn_title'] = $site['cn_title'];//公司
            $list[$key]['domain'] = $site['domain'];//网站
            $list[$key]['keyword_goal'] = $site['keyword_goal'];//关键词达标

            $list[$key]['seller_title'] = implode('-', $sellerTitle);//销售
            $list[$key]['server_title'] = implode('-', $serverTitle);//客服经理
            $list[$key]['managers_title'] = implode('-', $managersTitle);//项目经理
            $list[$key]['manage_helper_title'] = implode('-', $manageHelperTitle);//项目经理1

            $list[$key]['ae_title'] = implode('-', $aeTitle);//采编
            $list[$key]['optimizerTitle'] = implode('-', $optimizerTitle);//优化
            $list[$key]['bidTitle'] = implode('-', $bidTitle);//竞价
            $list[$key]['articleTitle'] = implode('-', $articleTitle);//软文
            $list[$key]['designerTitle'] = implode('-', $designerTitle);//设计
            $list[$key]['webTitle'] = implode('-', $webTitle);//前端

            //$list[$key]['order_at'] = substr($site['order_at'], 0, 10);//分配时间
            $list[$key]['online_at'] = substr($site['online_at'], 0, 10);//上线时间
            $list[$key]['reach_at'] = substr($site['reach_at'], 0, 10);//达标时间
            $list[$key]['expired_at'] = substr($site['expired_at'], 0, 10);//过期时间
            $list[$key]['bq_at'] = substr($site['bq_at'], 0, 10);//百千约访时间
            $list[$key]['renewal_times'] = $site['renewal_times'];//续费次数
            $list[$key]['renewal_at'] = substr($site['renewal_at'], 0, 10);//续费时间
        }

        $result = [];
        foreach ($list as $item) {
            if (empty($item['order_at'])) {
                $result[] = $item;
            }
        }

        foreach ($list as $item) {
            if (!empty($item['order_at'])) {
                $result[] = $item;
            }
        }

        array_unshift($result, ['下单时间', '合同签订时间', '首款时间', '公司', '网站', '关键词指标', '项目销售', '客服经理', '项目经理1', '项目经理', '项目采编', '优化师', '竞价', '软文', '设计', '前端', '上线时间', '达标时间', '过期时间', '百千约访时间', '续费次数', '续费时间']);
        return (new BasicExport($result))->download(sprintf('下单%s.xls', date('YmdHis')));
    }

    //当月达标
    public function reach(Request $request)
    {
        if (!$request->ajax()) {
            return view('admin/analyze/reach', [
                'services' => Role::getUsers(Role::TYPE_SERVER), //服务人员
                'optimizers' => Role::getUsers(Role::TYPE_OPTIMIZER),
                'optimizersAe' => Role::getUsers(Role::TYPE_OPTIMIZATION_EDITING),
                'sellerUsers' => Role::getUsers(Role::TYPE_SELLER),
                'editors' => Role::getUsers(Role::TYPE_AE),
                'managerHelpers' => Role::getUsers(Role::TYPE_MANAGE_HELPER),
            ]);
        }

        $builder = $this->reachBuilder();
        $sites = $builder->orderByDesc('id')->paginate($request->input('pageSize') ?? TABLE_PAGE_SIZE);
        $items = $sites->items();

        $oldIds = array_filter(array_column($items, 'old_id'));
        [$top10ListMap, $listReportMap] = $this->getInquiryAndFlowList($oldIds);

        foreach ($items as $item) {
            $item->status = Site::STATUS_MAP[$item->status];
            $item->optimizerAe = implode('-', $item->users->where('role_id', Role::TYPE_OPTIMIZATION_EDITING)->pluck('nickname')->toArray());

            $item->server_title = implode('-', $item->users->where('role_id', Role::TYPE_SERVER)->pluck('nickname')->toArray());
            $item->seller_title = implode('-', $item->users->where('role_id', Role::TYPE_SELLER)->pluck('nickname')->toArray());
            $item->optimizerTitle = implode('-', $item->users->where('role_id', Role::TYPE_OPTIMIZER)->pluck('nickname')->toArray());

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

            $item->bidTitle = implode('-', $item->users->where('role_id', Role::TYPE_BID)->pluck('nickname')->toArray());
            $item->articleTitle = implode('-', $item->users->where('role_id', Role::TYPE_PROPAGANDA)->pluck('nickname')->toArray());
            $item->designerTitle = implode('-', $item->users->where('role_id', Role::TYPE_DESIGNER)->pluck('nickname')->toArray());
            $item->webTitle = implode('-', $item->users->where('role_id', Role::TYPE_WEB)->pluck('nickname')->toArray());

            $item->sign_at = substr($item->sign_at, 0, 10);
            $item->order_at = substr($item->order_at, 0, 10);
            $item->assign_at = substr($item->assign_at, 0, 10);
            $item->online_at = substr($item->online_at, 0, 10);
            $item->bq_at = substr($item->bq_at, 0, 10);
            $item->renewal_at = substr($item->renewal_at, 0, 10);

            $item->reach_mail_at = substr($item->reach_mail_at, 0, 10);
            $item->reach_pay_at = substr($item->reach_pay_at, 0, 10);

            if ($item->reach_at && $item->online_at) {
                $reachAt = date_create($item->reach_at);
                $onlineAt = date_create($item->online_at);
                $diff = date_diff($reachAt, $onlineAt);
                $item->reach_days = $diff->days;
            } else {
                $item->reach_days = null;
            }
            $item->reach_at = substr($item->reach_at, 0, 10);
            $item->expired_at = substr($item->expired_at, 0, 10);
        };

        return response()->json([
            'rows' => $items,
            'total' => $sites->total()
        ]);
    }

    protected function reachBuilder()
    {
        $request = \request();
        $filterUserIds = [];

        $optimizerId = $request->input('optimizerId');
        $optimizerId && $filterUserIds[] = $optimizerId;
        $managerId = $request->input('managerId');
        $managerId && $filterUserIds[] = $managerId;
        $serverId = $request->input('serverId');
        $serverId && $filterUserIds[] = $serverId;
        $sellerId = $request->input('sellerId');
        $sellerId && $filterUserIds[] = $sellerId;
        $manageHelperId = $request->input('manageHelperId');
        $manageHelperId && $filterUserIds[] = $manageHelperId;
        $optimizerAeId = $request->input('optimizerAeId');
        $optimizerAeId && $filterUserIds[] = $optimizerAeId;
        $editorId = $request->input('editorId');
        $editorId && $filterUserIds[] = $editorId;

        $keyword = $request->input('keyword');
        $status = $request->input('status');

        $builder = Site::query()->with(['users'])->where(function (Builder $q) use ($keyword, $status) {
            if ($keyword) {
                $q->where('domain', 'like', '%' . $keyword . '%')
                    ->orWhere('cn_title', 'like', '%' . $keyword . '%');
            }
            if ($status) {
                $q->where('status', $status);
            }
        });


        $startDate = $request->input('startDate');
        $endDate = $request->input('endDate');

        if ($startDate) {
            $start = sprintf("%s-01 00:00:00", $startDate);
            $builder->where('reach_at', '>=', $start);
        }

        if ($endDate) {
            $end = sprintf("%s-%s 23:59:59", $endDate, date('t', strtotime($endDate)));
            $builder->where('reach_at', '<=', $end);
        }

        if ($startDate && $endDate) {
            $startDate = sprintf("%s-01 00:00:00", $startDate);
            $builder->where('reach_at', '>=', $startDate)
                ->where('reach_at', '<=', sprintf("%s-%s 23:59:59", $endDate, date('t', strtotime($endDate))));
        }

        if (!$startDate && !$endDate) {
            $startDate = date('Y-m-01 00:00:00');
            $builder->whereRaw('reach_at >= ? AND reach_at <= ?', [
                    date('Y-m-01 00:00:00'),
                    sprintf("%s-%s 23:59:59", date('Y-m'), date('t', strtotime($startDate)))
                ]
            );
        }


        if ($filterUserIds) {
            $builder->whereExists(function (\Illuminate\Database\Query\Builder $b) use ($filterUserIds) {
                $raw = sprintf('SUM(CASE WHEN user_id in (%s) then  1 ELSE  0 end) as total', implode(',', $filterUserIds));
                $b->select(DB::raw($raw))->from('user_has_sites')
                    ->whereRaw(sprintf('user_has_sites.site_id=sites.id HAVING total=%s', count($filterUserIds)));
            });
        }
        return $builder;
    }

    //导出达标项目
    public function reachExport()
    {

        $builder = $this->reachBuilder();
        $items = $builder->orderByDesc('id')->get();

        $oldIds = array_filter(array_column($items->toArray(), 'old_id'));
        [$top10ListMap, $listReportMap] = $this->getInquiryAndFlowList($oldIds);

        $results = [];
        foreach ($items as $item) {

            if ($item->reach_at && $item->online_at) {
                $reachAt = date_create($item->reach_at);
                $onlineAt = date_create($item->online_at);
                $diff = date_diff($reachAt, $onlineAt);
                $reach_days = $diff->days;
            } else {
                $reach_days = null;
            }

            $results[] = [
                'reach_at' => substr($item->reach_at, 0, 10),
                'expired_at' => substr($item->expired_at, 0, 10),

                'reach_mail_at' => substr($item->reach_mail_at, 0, 10),
                'reach_pay_at' => substr($item->reach_pay_at, 0, 10),

                'cn_title' => $item->cn_title,
                'status' => Site::STATUS_MAP[$item->status],
                'domain' => $item->domain,
                'keyword_goal' => $item->keyword_goal,
                'top10' => $item->old_id ? ($top10ListMap[$item->old_id]->top10 ?? '-') : '未关联',
                'reach_days' => $reach_days,
                'inquire' => $item->old_id ? ($listReportMap[$item->old_id]->inquire ?? '-') : '未关联',

                'seller_title' => implode('-', $item->users->where('role_id', Role::TYPE_SELLER)->pluck('nickname')->toArray()),
                'server_title' => implode('-', $item->users->where('role_id', Role::TYPE_SERVER)->pluck('nickname')->toArray()),

                'optimizerTitle' => implode('-', $item->users->where('role_id', Role::TYPE_OPTIMIZER)->pluck('nickname')->toArray()),
                'optimizerAe' => implode('-', $item->users->where('role_id', Role::TYPE_OPTIMIZATION_EDITING)->pluck('nickname')->toArray()),

                'bidTitle' => implode('-', $item->users->where('role_id', Role::TYPE_BID)->pluck('nickname')->toArray()),
                'articleTitle' => implode('-', $item->users->where('role_id', Role::TYPE_PROPAGANDA)->pluck('nickname')->toArray()),
                'designerTitle' => implode('-', $item->users->where('role_id', Role::TYPE_DESIGNER)->pluck('nickname')->toArray()),
                'webTitle' => implode('-', $item->users->where('role_id', Role::TYPE_WEB)->pluck('nickname')->toArray()),

                'sign_at' => substr($item->sign_at, 0, 10),
                'order_at' => substr($item->order_at, 0, 10),
                'assign_at' => substr($item->assign_at, 0, 10),
                'online_at' => substr($item->online_at, 0, 10),
                'bq_at' => substr($item->bq_at, 0, 10),
                'renewal_times' => $item->renewal_times,
                'renewal_at' => substr($item->renewal_at, 0, 10),
            ];
        };

        array_unshift($results, ['达标时间', '过期时间', '达标邮件确认时间', '达标款支付时间', '公司', '项目状态', '网站', '关键词达标', '实际达标关键词', '达标时长', '询盘', '项目销售', '客服经理', '优化师', '优化采编', '竞价', '软文', '设计', '前端', '合同签订时间', '下单时间', '分配时间', '上线时间', '百千约访时间', '续费次数', '续费时间']);

        return (new BasicExport($results))->download(sprintf('当月达标%s.xls', date('YmdHis')));
    }

    //历史达标
    public function historyReach(Request $request)
    {
        if (!$request->ajax()) {
            return view('admin/analyze/history_reach', [
                'services' => Role::getUsers(Role::TYPE_SERVER), //服务人员
                'optimizers' => Role::getUsers(Role::TYPE_OPTIMIZER),
                'managers' => Role::getUsers(Role::TYPE_MANAGER),
                'sellerUsers' => Role::getUsers(Role::TYPE_SELLER),
                'editors' => Role::getUsers(Role::TYPE_AE),
                'managerHelpers' => Role::getUsers(Role::TYPE_MANAGE_HELPER),
            ]);
        }


        $builder = $this->historyReachBuilder();
        $sites = $builder->orderByDesc('reach_at')->paginate($request->input('pageSize') ?? TABLE_PAGE_SIZE);

        $items = $sites->items();

        $oldIds = array_filter(array_column($items, 'old_id'));
        [$top10ListMap, $listReportMap] = $this->getInquiryAndFlowList($oldIds);


        foreach ($items as $item) {

            if ($item->reach_at && $item->online_at) {
                $reachAt = date_create($item->reach_at);
                $onlineAt = date_create($item->online_at);
                $diff = date_diff($reachAt, $onlineAt);
                $item->reach_days = $diff->days;
            } else {
                $item->reach_days = null;
            }

            $item->managers_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGER)->pluck('nickname')->toArray());
            $item->server_title = implode('-', $item->users->where('role_id', Role::TYPE_SERVER)->pluck('nickname')->toArray());
            $item->seller_title = implode('-', $item->users->where('role_id', Role::TYPE_SELLER)->pluck('nickname')->toArray());
            $item->optimizerTitle = implode('-', $item->users->where('role_id', Role::TYPE_OPTIMIZER)->pluck('nickname')->toArray());
            $item->manage_helper_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGE_HELPER)->pluck('nickname')->toArray());
            $item->ae_title = implode('-', $item->users->where('role_id', Role::TYPE_AE)->pluck('nickname')->toArray());

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

            $item->bidTitle = implode('-', $item->users->where('role_id', Role::TYPE_BID)->pluck('nickname')->toArray());
            $item->articleTitle = implode('-', $item->users->where('role_id', Role::TYPE_PROPAGANDA)->pluck('nickname')->toArray());
            $item->designerTitle = implode('-', $item->users->where('role_id', Role::TYPE_DESIGNER)->pluck('nickname')->toArray());
            $item->webTitle = implode('-', $item->users->where('role_id', Role::TYPE_WEB)->pluck('nickname')->toArray());

            $item->sign_at = substr($item->sign_at, 0, 10);
            $item->order_at = substr($item->order_at, 0, 10);
            $item->assign_at = substr($item->assign_at, 0, 10);
            $item->online_at = substr($item->online_at, 0, 10);
            $item->bq_at = substr($item->bq_at, 0, 10);
            $item->renewal_at = substr($item->renewal_at, 0, 10);


            $item->reach_at = substr($item->reach_at, 0, 10);
            $item->expired_at = substr($item->expired_at, 0, 10);
        };

        return response()->json([
            'rows' => $items,
            'total' => $sites->total()
        ]);
    }


    protected function historyReachBuilder()
    {
        $request = \request();
        $filterUserIds = [];

        $optimizerId = $request->input('optimizerId');
        $optimizerId && $filterUserIds[] = $optimizerId;
        $managerId = $request->input('managerId');
        $managerId && $filterUserIds[] = $managerId;
        $serverId = $request->input('serverId');
        $serverId && $filterUserIds[] = $serverId;
        $sellerId = $request->input('sellerId');
        $sellerId && $filterUserIds[] = $sellerId;
        $manageHelperId = $request->input('manageHelperId');
        $manageHelperId && $filterUserIds[] = $manageHelperId;
        $editorId = $request->input('editorId');
        $editorId && $filterUserIds[] = $editorId;


        $keyword = $request->input('keyword');
        $builder = Site::query()->with(['users'])->where(function (Builder $q) use ($keyword) {
            if ($keyword) {
                $q->where('domain', 'like', '%' . $keyword . '%')
                    ->orWhere('cn_title', 'like', '%' . $keyword . '%');
            }
        })->whereIn('status', [1, 2, 3, 4, 5, 6, 8, 9]);


        $startDate = $request->input('startDate');
        $endDate = $request->input('endDate');

        if ($startDate) {
            $start = sprintf("%s-01 00:00:00", $startDate);
            $builder->where('reach_at', '>=', $start);
        }

        if ($endDate) {
            $end = sprintf("%s-%s 23:59:59", $endDate, date('t', strtotime($endDate)));
            $builder->where('reach_at', '<=', $end);
        }

        if ($startDate && $endDate) {
            $startDate = sprintf("%s-01 00:00:00", $startDate);
            $builder->where('reach_at', '>=', $startDate)
                ->where('reach_at', '<=', sprintf("%s-%s 23:59:59", $endDate, date('t', strtotime($endDate))));
        }

        if (!$startDate && !$endDate) {
            $date = date('Y-m-01 00:00:00');
            $builder->whereRaw('expired_at is null and reach_at < ?', [$date]);
        }


        if ($filterUserIds) {
            $builder->whereExists(function (\Illuminate\Database\Query\Builder $b) use ($filterUserIds) {
                $raw = sprintf('SUM(CASE WHEN user_id in (%s) then  1 ELSE  0 end) as total', implode(',', $filterUserIds));
                $b->select(DB::raw($raw))->from('user_has_sites')
                    ->whereRaw(sprintf('user_has_sites.site_id=sites.id HAVING total=%s', count($filterUserIds)));
            });
        }
        return $builder;
    }

    //历史达标导出
    public function historyReachExport()
    {
        $builder = $this->historyReachBuilder();
        $items = $builder->orderByDesc('reach_at')->get();

        $oldIds = array_filter(array_column($items->toArray(), 'old_id'));
        [$top10ListMap, $listReportMap] = $this->getInquiryAndFlowList($oldIds);

        $results = [];
        foreach ($items as $item) {

            if ($item->reach_at && $item->online_at) {
                $reachAt = date_create($item->reach_at);
                $onlineAt = date_create($item->online_at);
                $diff = date_diff($reachAt, $onlineAt);
                $reach_days = $diff->days;
            } else {
                $reach_days = null;
            }

            $results[] = [
                'reach_at' => substr($item->reach_at, 0, 10),
                'expired_at' => substr($item->expired_at, 0, 10),
                'cn_title' => $item->cn_title,
                'status' => Site::STATUS_MAP[$item->status],
                'domain' => $item->domain,
                'keyword_goal' => $item->keyword_goal,
                'top10' => $item->old_id ? ($top10ListMap[$item->old_id]->top10 ?? '-') : '未关联',
                'reach_days' => $reach_days,

                'seller_title' => implode('-', $item->users->where('role_id', Role::TYPE_SELLER)->pluck('nickname')->toArray()),
                'server_title' => implode('-', $item->users->where('role_id', Role::TYPE_SERVER)->pluck('nickname')->toArray()),
                'managers_title' => implode('-', $item->users->where('role_id', Role::TYPE_MANAGER)->pluck('nickname')->toArray()),
                'manage_helper_title' => implode('-', $item->users->where('role_id', Role::TYPE_MANAGE_HELPER)->pluck('nickname')->toArray()),
                'optimizerTitle' => implode('-', $item->users->where('role_id', Role::TYPE_OPTIMIZER)->pluck('nickname')->toArray()),
                'ae_title' => implode('-', $item->users->where('role_id', Role::TYPE_AE)->pluck('nickname')->toArray()),
                'bidTitle' => implode('-', $item->users->where('role_id', Role::TYPE_BID)->pluck('nickname')->toArray()),
                'articleTitle' => implode('-', $item->users->where('role_id', Role::TYPE_PROPAGANDA)->pluck('nickname')->toArray()),
                'designerTitle' => implode('-', $item->users->where('role_id', Role::TYPE_DESIGNER)->pluck('nickname')->toArray()),
                'webTitle' => implode('-', $item->users->where('role_id', Role::TYPE_WEB)->pluck('nickname')->toArray()),

                'sign_at' => substr($item->sign_at, 0, 10),
                'order_at' => substr($item->order_at, 0, 10),
                'assign_at' => substr($item->assign_at, 0, 10),
                'online_at' => substr($item->online_at, 0, 10),
                'bq_at' => substr($item->bq_at, 0, 10),
                'renewal_times' => $item->renewal_times,
                'renewal_at' => substr($item->renewal_at, 0, 10),
            ];

        };

        array_unshift($results, ['达标时间', '过期时间', '公司', '项目状态', '网站', '关键词达标',
            '实际达标关键词', '达标时长', '项目销售', '客服经理', '项目经理1', '项目经理', '优化师', '项目采编', '竞价', '软文', '设计', '前端', '合同签订时间', '下单时间', '分配时间', '上线时间', '百千约访时间', '续费次数', '续费时间']);

        return (new BasicExport($results))->download(sprintf('历史达标%s.xls', date('YmdHis')));
    }

    //上线
    public function online(Request $request)
    {
        if (!$request->ajax()) {

            return view('admin/analyze/online', [
                'webs' => Role::getUsers(Role::TYPE_WEB),
                'design' => Role::getUsers(Role::TYPE_DESIGNER),
                'services' => Role::getUsers(Role::TYPE_SERVER),
                'editors' => Role::getUsers(Role::TYPE_AE),
                'managers' => Role::getUsers(Role::TYPE_MANAGER),
                'managerHelpers' => Role::getUsers(Role::TYPE_MANAGE_HELPER),
                'sellerUsers' => Role::getUsers(Role::TYPE_SELLER),
                'optimizers' => Role::getUsers(Role::TYPE_OPTIMIZER),
                'optimizerAes' => Role::getUsers(Role::TYPE_OPTIMIZATION_EDITING),
            ]);
        }

        $builder = $this->onlineBuilder();
        $sites = $builder->orderByDesc('id')->paginate($request->input('pageSize') ?? TABLE_PAGE_SIZE);

        $items = $sites->items();

        foreach ($items as $item) {
            $item->identify = base64_encode($item->id);
            $item->managers_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGER)->pluck('nickname')->toArray());
            $item->server_title = implode('-', $item->users->where('role_id', Role::TYPE_SERVER)->pluck('nickname')->toArray());
            $item->seller_title = implode('-', $item->users->where('role_id', Role::TYPE_SELLER)->pluck('nickname')->toArray());
            $item->optimizerTitle = implode('-', $item->users->where('role_id', Role::TYPE_OPTIMIZER)->pluck('nickname')->toArray());
            $item->manage_helper_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGE_HELPER)->pluck('nickname')->toArray());
            $item->optimizerAe = implode('-', $item->users->where('role_id', Role::TYPE_OPTIMIZATION_EDITING)->pluck('nickname')->toArray());
            $item->ae = implode('-', $item->users->where('role_id', Role::TYPE_AE)->pluck('nickname')->toArray());
            $item->web = implode('-', $item->users->where('role_id', Role::TYPE_WEB)->pluck('nickname')->toArray());


            $item->bidTitle = implode('-', $item->users->where('role_id', Role::TYPE_BID)->pluck('nickname')->toArray());
            $item->articleTitle = implode('-', $item->users->where('role_id', Role::TYPE_PROPAGANDA)->pluck('nickname')->toArray());
            $item->designerTitle = implode('-', $item->users->where('role_id', Role::TYPE_DESIGNER)->pluck('nickname')->toArray());

            $item->sign_at = substr($item->sign_at, 0, 10);
            $item->order_at = substr($item->order_at, 0, 10);
            $item->assign_at = substr($item->assign_at, 0, 10);
            $item->reach_at = substr($item->reach_at, 0, 10);
            $item->expired_at = substr($item->expired_at, 0, 10);
            $item->bq_at = substr($item->bq_at, 0, 10);
            $item->renewal_at = substr($item->renewal_at, 0, 10);

            if ($item->online_at && $item->assign_at) {
                $orderAt = date_create($item->assign_at);
                $onlineAt = date_create($item->online_at);
                $diff = date_diff($orderAt, $onlineAt);
                $item->online_days = $diff->days;
            } else {
                $item->online_days = null;
            }
            $item->online_at = substr($item->online_at, 0, 10);
        };

        return response()->json([
            'rows' => $items,
            'total' => $sites->total()
        ]);
    }

    public function onlineBuilder()
    {
        $request = \request();
        $filterUserIds = [];
        $editorId = $request->input('editorId');
        $editorId && $filterUserIds[] = $editorId;
        $manageHelperId = $request->input('manageHelperId');
        $manageHelperId && $filterUserIds[] = $manageHelperId;
        $managerId = $request->input('managerId');
        $managerId && $filterUserIds[] = $managerId;
        $serverId = $request->input('serverId');
        $serverId && $filterUserIds[] = $serverId;
        $sellerId = $request->input('sellerId');
        $sellerId && $filterUserIds[] = $sellerId;
        $optimizerId = $request->input('optimizerId');
        $optimizerId && $filterUserIds[] = $optimizerId;
        $optimizerAeId = $request->input('optimizerAeId');
        $optimizerAeId && $filterUserIds[] = $optimizerAeId;
        $designId = $request->input('designId');
        $designId && $filterUserIds[] = $designId;
        $webId = $request->input('webId');
        $webId && $filterUserIds[] = $webId;

        $keyword = $request->input('keyword');
        $builder = Site::query()->with(['users'])->where(function (Builder $q) use ($keyword) {
            if ($keyword) {
                $q->where('domain', 'like', '%' . $keyword . '%')
                    ->orWhere('cn_title', 'like', '%' . $keyword . '%');
            }
        });

        $sortName = $request->input('sortName');
        $sortOrder = $request->input('sortOrder');
        if (!empty($sortName) || !empty($sortOrder)) {
            $builder->orderBy($sortName, $sortOrder);
        }

        $startDate = $request->input('startDate');
        $endDate = $request->input('endDate');

        if ($startDate) {
            $start = sprintf("%s-01 00:00:00", $startDate);
            $builder->where('online_at', '>=', $start);
        }

        if ($endDate) {
            $end = sprintf("%s-%s 23:59:59", $endDate, date('t', strtotime($endDate)));
            $builder->where('online_at', '<=', $end);
        }

        if ($startDate && $endDate) {
            $startDate = sprintf("%s-01 00:00:00", $startDate);
            $builder->where('online_at', '>=', $startDate)
                ->where('online_at', '<=', sprintf("%s-%s 23:59:59", $endDate, date('t', strtotime($endDate))));
        }

        if (!$startDate && !$endDate) {
            $startDate = date('Y-m-01 00:00:00');
            $builder->Where('online_at', '>=', date('Y-m-01 00:00:00'))
                ->where('online_at', '<=', sprintf("%s-%s 23:59:59", date('Y-m'), date('t', strtotime($startDate))));
        }


        if ($filterUserIds) {
            $builder->whereExists(function (\Illuminate\Database\Query\Builder $b) use ($filterUserIds) {
                $raw = sprintf('SUM(CASE WHEN user_id in (%s) then  1 ELSE  0 end) as total', implode(',', $filterUserIds));
                $b->select(DB::raw($raw))->from('user_has_sites')
                    ->whereRaw(sprintf('user_has_sites.site_id=sites.id HAVING total=%s', count($filterUserIds)));
            });
        }
        return $builder;
    }

    public function onlineExport()
    {
        $builder = $this->onlineBuilder();
        $sites = $builder->orderByDesc('id')->get()->toArray();
        $list = [];

        foreach ($sites as $key => $site) {
            $onlineDays = 0;
            if ($site['online_at'] && $site['assign_at']) {
                $orderAt = date_create($site['assign_at']);
                $onlineAt = date_create($site['online_at']);
                $diff = date_diff($orderAt, $onlineAt);
                $onlineDays = $diff->days;
            }

            $sellerTitle = [];//销售
            $serverTitle = [];//客服
            $managersTitle = [];//项目经理1
            $manageHelperTitle = [];//项目经理

            $optimizerAe = [];//优化采编
            $ae = [];//采编
            $optimizerTitle = [];//优化师
            $bidTitle = [];//竞价
            $articleTitle = [];//软文
            $designerTitle = [];//设计
            $webTitle = [];//前端

            $userList = $site['users'];
            foreach ($userList as $kk => $value) {
                if ($value['role_id'] == Role::TYPE_SELLER) {
                    $sellerTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_SERVER) {
                    $serverTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_MANAGER) {
                    $managersTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_MANAGE_HELPER) {
                    $manageHelperTitle [] = $value['nickname'];
                }

                if ($value['role_id'] == Role::TYPE_OPTIMIZATION_EDITING) {
                    $optimizerAe [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_AE) {
                    $ae [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_OPTIMIZER) {
                    $optimizerTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_BID) {
                    $bidTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_PROPAGANDA) {
                    $articleTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_DESIGNER) {
                    $designerTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_WEB) {
                    $webTitle [] = $value['nickname'];
                }
            }

            $list[$key]['online_at'] = substr($site['online_at'], 0, 10);//上线时间
            $list[$key]['cn_title'] = $site['cn_title'];//公司
            $list[$key]['domain'] = $site['domain'];//网站
            $list[$key]['keyword_goal'] = $site['keyword_goal'];//关键词指标
            $list[$key]['online_days'] = $onlineDays;//建站时长

            $list[$key]['seller_title'] = implode('-', $sellerTitle);//销售
            $list[$key]['server_title'] = implode('-', $serverTitle);//客服经理
            $list[$key]['managers_title'] = implode('-', $managersTitle);//项目经理
            $list[$key]['manage_helper_title'] = implode('-', $manageHelperTitle);//项目经理1

            $list[$key]['optimizerTitle'] = implode('-', $optimizerTitle);//优化
            $list[$key]['optimizerAe'] = implode('-', $optimizerAe);//优化采编
            $list[$key]['ae'] = implode('-', $ae);//采编
            $list[$key]['bidTitle'] = implode('-', $bidTitle);//竞价
            $list[$key]['articleTitle'] = implode('-', $articleTitle);//软文
            $list[$key]['designerTitle'] = implode('-', $designerTitle);//设计
            $list[$key]['webTitle'] = implode('-', $webTitle);//前端

            $list[$key]['sign_at'] = substr($site['sign_at'], 0, 10);//合同签订时间
            $list[$key]['order_at'] = substr($site['order_at'], 0, 10);//下单时间
            $list[$key]['assign_at'] = substr($site['assign_at'], 0, 10);//分配时间
            //$list[$key]['online_at'] = substr($site['online_at'], 0, 10);//上线时间
            $list[$key]['reach_at'] = substr($site['reach_at'], 0, 10);//达标时间
            $list[$key]['expired_at'] = substr($site['expired_at'], 0, 10);//过期时间
            $list[$key]['bq_at'] = substr($site['bq_at'], 0, 10);//百千约访时间
            $list[$key]['renewal_times'] = $site['renewal_times'];//续费次数
            $list[$key]['renewal_at'] = substr($site['renewal_at'], 0, 10);//续费时间
        }
        array_unshift($list, ['上线时间', '公司', '网站', '关键词指标', '建站时长', '项目销售', '客服经理', '项目经理1', '项目经理', '优化师', '优化采编', '采编', '竞价', '软文', '设计', '前端', '合同签订时间', '下单时间', '分配时间', '达标时间', '过期时间', '百千约访时间', '续费次数', '续费时间']);
        return (new BasicExport($list))->download(sprintf('上线%s.xls', date('YmdHis')));

    }

    //待续费
    public function renewal(Request $request)
    {
        if (!$request->ajax()) {
            return view('admin/analyze/renewal', [
                'services' => Role::getUsers(Role::TYPE_SERVER), //服务人员
                'optimizers' => Role::getUsers(Role::TYPE_OPTIMIZER),
                'managers' => Role::getUsers(Role::TYPE_MANAGER),
                'managerHelpers' => Role::getUsers(Role::TYPE_MANAGE_HELPER),
                'sellerUsers' => Role::getUsers(Role::TYPE_SELLER),
                'editors' => Role::getUsers(Role::TYPE_AE),
            ]);
        }
        $builder = $this->renewalBuilder();
        $sites = $builder->orderBy('expired_at')->paginate($request->input('pageSize') ?? TABLE_PAGE_SIZE);

        $items = $sites->items();

        $oldIds = array_filter(array_column($items, 'old_id'));
        [$top10ListMap, $listReportMap] = $this->getInquiryAndFlowList($oldIds);


        foreach ($items as $item) {
            $item->status = Site::STATUS_MAP[$item->status];
            $item->managers_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGER)->pluck('nickname')->toArray());
            $item->manage_helper_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGE_HELPER)->pluck('nickname')->toArray());
            $item->server_title = implode('-', $item->users->where('role_id', Role::TYPE_SERVER)->pluck('nickname')->toArray());
            $item->seller_title = implode('-', $item->users->where('role_id', Role::TYPE_SELLER)->pluck('nickname')->toArray());
            $item->optimizerTitle = implode('-', $item->users->where('role_id', Role::TYPE_OPTIMIZER)->pluck('nickname')->toArray());
            $item->ae_title = implode('-', $item->users->where('role_id', Role::TYPE_AE)->pluck('nickname')->toArray());

            $item->top10 = $item->old_id ? ($top10ListMap[$item->old_id]->top10 ?? '-') : '未关联'; //实际达标关键词
            $item->traffic = $item->old_id ? ($listReportMap[$item->old_id]->traffic ?? '-') : '未关联';
            $item->inquire = $item->old_id ? ($listReportMap[$item->old_id]->inquire ?? '-') : '未关联';

            $item->bidTitle = implode('-', $item->users->where('role_id', Role::TYPE_BID)->pluck('nickname')->toArray());
            $item->articleTitle = implode('-', $item->users->where('role_id', Role::TYPE_PROPAGANDA)->pluck('nickname')->toArray());
            $item->designerTitle = implode('-', $item->users->where('role_id', Role::TYPE_DESIGNER)->pluck('nickname')->toArray());
            $item->webTitle = implode('-', $item->users->where('role_id', Role::TYPE_WEB)->pluck('nickname')->toArray());

            $item->sign_at = substr($item->sign_at, 0, 10);
            $item->order_at = substr($item->order_at, 0, 10);
            $item->assign_at = substr($item->assign_at, 0, 10);
            $item->bq_at = substr($item->bq_at, 0, 10);
            $item->renewal_at = substr($item->renewal_at, 0, 10);

            $item->expired_at = substr($item->expired_at, 0, 10);
            $item->reach_at = substr($item->reach_at, 0, 10);
            $item->online_at = substr($item->online_at, 0, 10);
        };

        return response()->json([
            'rows' => $items,
            'total' => $sites->total()
        ]);
    }

    public function renewalBuilder()
    {
        $request = \request();
        $filterUserIds = [];
        $optimizerId = $request->input('optimizerId');
        $optimizerId && $filterUserIds[] = $optimizerId;
        $managerId = $request->input('managerId');
        $managerId && $filterUserIds[] = $managerId;
        $serverId = $request->input('serverId');
        $serverId && $filterUserIds[] = $serverId;
        $sellerId = $request->input('sellerId');
        $sellerId && $filterUserIds[] = $sellerId;
        $manageHelperId = $request->input('manageHelperId');
        $manageHelperId && $filterUserIds[] = $manageHelperId;
        $editorId = $request->input('editorId');
        $editorId && $filterUserIds[] = $editorId;


        $keyword = $request->input('keyword');
        $status = $request->input('status');
        $builder = Site::query()->with(['users'])->whereIn('status', [2, 3, 5])->where(function (Builder $q) use ($keyword, $status) {
            if ($keyword) {
                $q->where('domain', 'like', '%' . $keyword . '%')
                    ->orWhere('cn_title', 'like', '%' . $keyword . '%');
            }
            if ($status) {
                $q->where('status', $status);
            }
        });

        $startDate = $request->input('startDate');
        $endDate = $request->input('endDate');

        if ($startDate) {
            $start = sprintf("%s-01 00:00:00", $startDate);
            $builder->where('expired_at', '>=', $start);
        }

        if ($endDate) {
            $end = sprintf("%s-%s 23:59:59", $endDate, date('t', strtotime($endDate)));
            $builder->where('expired_at', '<=', $end);
        }

        if ($startDate && $endDate) {
            $startDate = sprintf("%s-01 00:00:00", $startDate);
            $builder->where('expired_at', '>=', $startDate)
                ->where('expired_at', '<=', sprintf("%s-%s 23:59:59", $endDate, date('t', strtotime($endDate))));
        }

        if (!$startDate && !$endDate) {
            $builder->where([
                ['expired_at', '<', date('Y-m-d H:i:s', strtotime('+3 month'))]
            ]);
        }

        if ($filterUserIds) {
            $builder->whereExists(function (\Illuminate\Database\Query\Builder $b) use ($filterUserIds) {
                $raw = sprintf('SUM(CASE WHEN user_id in (%s) then  1 ELSE  0 end) as total', implode(',', $filterUserIds));
                $b->select(DB::raw($raw))->from('user_has_sites')
                    ->whereRaw(sprintf('user_has_sites.site_id=sites.id HAVING total=%s', count($filterUserIds)));
            });
        }
        return $builder;
    }

    public function renewalExport()
    {
        $builder = $this->renewalBuilder();
        $sites = $builder->orderBy('expired_at')->get()->toArray();
        $list = [];

        $oldIds = array_filter(array_column($sites, 'old_id'));
        [$top10ListMap, $listReportMap] = $this->getInquiryAndFlowList($oldIds);

        foreach ($sites as $key => $site) {

            $isRenewal = '否';//是否续费

            $sellerTitle = [];//销售
            $serverTitle = [];//客服
            $managersTitle = [];//项目经理1
            $manageHelperTitle = [];//项目经理

            $aeTitle = [];//采编
            $optimizerTitle = [];//优化师
            $bidTitle = [];//竞价
            $articleTitle = [];//软文
            $designerTitle = [];//设计
            $webTitle = [];//前端

            $userList = $site['users'];
            foreach ($userList as $kk => $value) {
                if ($value['role_id'] == Role::TYPE_SELLER) {
                    $sellerTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_SERVER) {
                    $serverTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_MANAGER) {
                    $managersTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_MANAGE_HELPER) {
                    $manageHelperTitle [] = $value['nickname'];
                }

                if ($value['role_id'] == Role::TYPE_AE) {
                    $aeTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_OPTIMIZER) {
                    $optimizerTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_BID) {
                    $bidTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_PROPAGANDA) {
                    $articleTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_DESIGNER) {
                    $designerTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_WEB) {
                    $webTitle [] = $value['nickname'];
                }
            }
            if (!empty($site['renewal_at'])) {
                $isRenewal = '是';
            }

            $list[$key]['cn_title'] = $site['cn_title'];
            $list[$key]['status'] = Site::STATUS_MAP[$site['status']];
            $list[$key]['domain'] = $site['domain'];
            $list[$key]['order_at'] = substr($site['order_at'], 0, 10);//下单时间
            $list[$key]['online_at'] = substr($site['online_at'], 0, 10);//上线时间
            $list[$key]['expired_at'] = substr($site['expired_at'], 0, 10);//过期时间
            $list[$key]['reach_at'] = substr($site['reach_at'], 0, 10);//达标时间
            $list[$key]['keyword_goal'] = $site['keyword_goal'];//关键词

            $list[$key]['top10'] = $top10ListMap[$site['old_id']]->top10 ?? '';
            $list[$key]['traffic'] = $listReportMap[$site['old_id']]->traffic ?? '';
            $list[$key]['inquire'] = $listReportMap[$site['old_id']]->inquire ?? '';

            $list[$key]['seller_title'] = implode('-', $sellerTitle);//销售
            $list[$key]['server_title'] = implode('-', $serverTitle);//客服经理
            $list[$key]['managers_title'] = implode('-', $managersTitle);//项目经理1
            $list[$key]['manage_helper_title'] = implode('-', $manageHelperTitle);//项目经理

            $list[$key]['optimizerTitle'] = implode('-', $optimizerTitle);//优化
            $list[$key]['ae_title'] = implode('-', $aeTitle);//采编
            $list[$key]['bidTitle'] = implode('-', $bidTitle);//竞价
            $list[$key]['articleTitle'] = implode('-', $articleTitle);//软文
            $list[$key]['designerTitle'] = implode('-', $designerTitle);//设计
            $list[$key]['webTitle'] = implode('-', $webTitle);//前端

            $list[$key]['sign_at'] = substr($site['sign_at'], 0, 10);//合同签订时间
            $list[$key]['assign_at'] = substr($site['assign_at'], 0, 10);//分配时间
            $list[$key]['bq_at'] = substr($site['bq_at'], 0, 10);//百千约访时间
            $list[$key]['is_renewal'] = $isRenewal;
            $list[$key]['renewal_times'] = $site['renewal_times'];//续费次数
        }
        array_unshift($list, ['公司', '项目状态', '网站', '下单时间', '上线时间', '过期时间', '达标时间', '关键词指标', '实际达标关键词',
            '月流量', '询盘总数', '销售', '客服', '项目总监', '项目经理', '优化组长', '建站采编',
            '竞价', '软文', '设计', '前端', '合同签订时间', '分配时间', '百千约访时间', '是否续费', '续费次数']);
        return (new BasicExport($list))->download(sprintf('待续费%s.xls', date('YmdHis')));
    }


    //未续费
    public function notRenewed(Request $request)
    {
        if (!$request->ajax()) {
            return view('admin/analyze/not_renewed', [
                'services' => Role::getUsers(Role::TYPE_SERVER), //服务人员
                'optimizers' => Role::getUsers(Role::TYPE_OPTIMIZER),
                'managers' => Role::getUsers(Role::TYPE_MANAGER),
                'managerHelpers' => Role::getUsers(Role::TYPE_MANAGE_HELPER),
                'sellerUsers' => Role::getUsers(Role::TYPE_SELLER),
                'editors' => Role::getUsers(Role::TYPE_AE),
            ]);
        }

        $builder = $this->notRenewedBuilder();
        $sites = $builder->orderBy('expired_at', 'desc')->paginate($request->input('pageSize') ?? TABLE_PAGE_SIZE);
        $items = $sites->items();

        $oldIds = array_filter(array_column($items, 'old_id'));
        [$top10ListMap, $listReportMap] = $this->getInquiryAndFlowList($oldIds);

        foreach ($items as $item) {
            $item->status = Site::STATUS_MAP[$item->status];
            $item->managers_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGER)->pluck('nickname')->toArray());
            $item->manage_helper_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGE_HELPER)->pluck('nickname')->toArray());
            $item->server_title = implode('-', $item->users->where('role_id', Role::TYPE_SERVER)->pluck('nickname')->toArray());
            $item->seller_title = implode('-', $item->users->where('role_id', Role::TYPE_SELLER)->pluck('nickname')->toArray());
            $item->optimizerTitle = implode('-', $item->users->where('role_id', Role::TYPE_OPTIMIZER)->pluck('nickname')->toArray());
            $item->ae_title = implode('-', $item->users->where('role_id', Role::TYPE_AE)->pluck('nickname')->toArray());

            $item->top10 = $item->old_id ? ($top10ListMap[$item->old_id]->top10 ?? '-') : '未关联'; //实际达标关键词
            $item->traffic = $item->old_id ? ($listReportMap[$item->old_id]->traffic ?? '-') : '未关联';
            $item->inquire = $item->old_id ? ($listReportMap[$item->old_id]->inquire ?? '-') : '未关联';

            $item->bidTitle = implode('-', $item->users->where('role_id', Role::TYPE_BID)->pluck('nickname')->toArray());
            $item->articleTitle = implode('-', $item->users->where('role_id', Role::TYPE_PROPAGANDA)->pluck('nickname')->toArray());
            $item->designerTitle = implode('-', $item->users->where('role_id', Role::TYPE_DESIGNER)->pluck('nickname')->toArray());
            $item->webTitle = implode('-', $item->users->where('role_id', Role::TYPE_WEB)->pluck('nickname')->toArray());

            $item->sign_at = substr($item->sign_at, 0, 10);
            $item->order_at = substr($item->order_at, 0, 10);
            $item->assign_at = substr($item->assign_at, 0, 10);
            $item->bq_at = substr($item->bq_at, 0, 10);
            $item->renewal_at = substr($item->renewal_at, 0, 10);

            $item->expired_at = substr($item->expired_at, 0, 10);
            $item->reach_at = substr($item->reach_at, 0, 10);
            $item->online_at = substr($item->online_at, 0, 10);
            $item->is_renewal = empty($item->renewal_at) ? '否' : '是';
            $item->notRenewedReasonAnalyze = self::NOT_RENEWED_MEMO_LIST[$item->sitePayment->not_renewed_reason_analyze];
        };
        return response()->json([
            'rows' => $items,
            'total' => $sites->total()
        ]);
    }

    public function notRenewedBuilder()
    {
        $request = \request();
        $filterUserIds = [];
        $optimizerId = $request->input('optimizerId');
        $optimizerId && $filterUserIds[] = $optimizerId;
        $managerId = $request->input('managerId');
        $managerId && $filterUserIds[] = $managerId;
        $serverId = $request->input('serverId');
        $serverId && $filterUserIds[] = $serverId;
        $sellerId = $request->input('sellerId');
        $sellerId && $filterUserIds[] = $sellerId;
        $manageHelperId = $request->input('manageHelperId');
        $manageHelperId && $filterUserIds[] = $manageHelperId;
        $editorId = $request->input('editorId');
        $editorId && $filterUserIds[] = $editorId;

        $keyword = $request->input('keyword');
        $status = $request->input('status');

        $build = Site::query()->with(['users', 'sitePayment']);
        $notRenewedReasonAnalyze = $request->input('not_renewed_reason_analyze');
        if (!empty($notRenewedReasonAnalyze)) {
            $siteIds = SitePayment::query()
                ->where('not_renewed_reason_analyze', $notRenewedReasonAnalyze)
                ->pluck('site_id');
            $build->whereIn('id', $siteIds);
        }
        $builder = $build->where('status', 10)->where(function (Builder $q) use ($keyword, $status) {
            if ($keyword) {
                $q->where('domain', 'like', '%' . $keyword . '%')
                    ->orWhere('cn_title', 'like', '%' . $keyword . '%');
            }
            if ($status) {
                $q->where('status', $status);
            }
        });

        $startDate = $request->input('startDate');
        $endDate = $request->input('endDate');

        if ($startDate) {
            $start = sprintf("%s-01 00:00:00", $startDate);
            $builder->where('expired_at', '>=', $start);
        }

        if ($endDate) {
            $end = sprintf("%s-%s 23:59:59", $endDate, date('t', strtotime($endDate)));
            $builder->where('expired_at', '<=', $end);
        }

        if ($startDate && $endDate) {
            $startDate = sprintf("%s-01 00:00:00", $startDate);
            $builder->where('expired_at', '>=', $startDate)
                ->where('expired_at', '<=', sprintf("%s-%s 23:59:59", $endDate, date('t', strtotime($endDate))));
        }

        if (!$startDate && !$endDate) {
            $builder->where([
                ['expired_at', '<', date('Y-m-d H:i:s', strtotime('+3 month'))]
            ]);
        }

        if ($filterUserIds) {
            $builder->whereExists(function (\Illuminate\Database\Query\Builder $b) use ($filterUserIds) {
                $raw = sprintf('SUM(CASE WHEN user_id in (%s) then  1 ELSE  0 end) as total', implode(',', $filterUserIds));
                $b->select(DB::raw($raw))->from('user_has_sites')
                    ->whereRaw(sprintf('user_has_sites.site_id=sites.id HAVING total=%s', count($filterUserIds)));
            });
        }
        return $builder;
    }

    public function notRenewedExport()
    {
        $builder = $this->notRenewedBuilder();
        $sites = $builder->orderBy('expired_at', 'desc')->get()->toArray();
        $list = [];

        $oldIds = array_filter(array_column($sites, 'old_id'));
        [$top10ListMap, $listReportMap] = $this->getInquiryAndFlowList($oldIds);

        foreach ($sites as $key => $site) {

            $sellerTitle = [];//销售
            $serverTitle = [];//客服
            $managersTitle = [];//项目经理1
            $manageHelperTitle = [];//项目经理

            $aeTitle = [];//采编
            $optimizerTitle = [];//优化师
            $bidTitle = [];//竞价
            $articleTitle = [];//软文
            $designerTitle = [];//设计
            $webTitle = [];//前端

            $userList = $site['users'];
            foreach ($userList as $kk => $value) {
                if ($value['role_id'] == Role::TYPE_SELLER) {
                    $sellerTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_SERVER) {
                    $serverTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_MANAGER) {
                    $managersTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_MANAGE_HELPER) {
                    $manageHelperTitle [] = $value['nickname'];
                }

                if ($value['role_id'] == Role::TYPE_AE) {
                    $aeTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_OPTIMIZER) {
                    $optimizerTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_BID) {
                    $bidTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_PROPAGANDA) {
                    $articleTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_DESIGNER) {
                    $designerTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_WEB) {
                    $webTitle [] = $value['nickname'];
                }
            }

            $list[$key]['cn_title'] = $site['cn_title'];
            $list[$key]['status'] = Site::STATUS_MAP[$site['status']];

            $list[$key]['domain'] = $site['domain'];
            $list[$key]['expired_at'] = substr($site['expired_at'], 0, 10);//过期时间
            $list[$key]['order_at'] = substr($site['order_at'], 0, 10);//下单时间
            $list[$key]['online_at'] = substr($site['online_at'], 0, 10);//上线时间
            $list[$key]['reach_at'] = substr($site['reach_at'], 0, 10);//达标时间
            $list[$key]['keyword_goal'] = $site['keyword_goal'];//关键词

            $list[$key]['top10'] = $top10ListMap[$site['old_id']]->top10 ?? '';
            $list[$key]['traffic'] = $listReportMap[$site['old_id']]->traffic ?? '';
            $list[$key]['inquire'] = $listReportMap[$site['old_id']]->inquire ?? '';

            $list[$key]['notRenewedReasonAnalyze'] = self::NOT_RENEWED_MEMO_LIST[$site['site_payment']['not_renewed_reason_analyze']];

            $list[$key]['seller_title'] = implode('-', $sellerTitle);//销售
            $list[$key]['server_title'] = implode('-', $serverTitle);//客服经理
            $list[$key]['managers_title'] = implode('-', $managersTitle);//项目经理1
            $list[$key]['manage_helper_title'] = implode('-', $manageHelperTitle);//项目经理

            $list[$key]['optimizerTitle'] = implode('-', $optimizerTitle);//优化
            $list[$key]['ae_title'] = implode('-', $aeTitle);//采编
            $list[$key]['bidTitle'] = implode('-', $bidTitle);//竞价
            $list[$key]['articleTitle'] = implode('-', $articleTitle);//软文
            $list[$key]['designerTitle'] = implode('-', $designerTitle);//设计
            $list[$key]['webTitle'] = implode('-', $webTitle);//前端

            $list[$key]['sign_at'] = substr($site['sign_at'], 0, 10);//合同签订时间
            //$list[$key]['assign_at'] = substr($site['assign_at'], 0, 10);//分配时间
            $list[$key]['bq_at'] = substr($site['bq_at'], 0, 10);//百千约访时间
            $list[$key]['renewal_times'] = $site['renewal_times'];//续费次数
            $list[$key]['renewal_at'] = substr($site['renewal_at'], 0, 10);//续费时间
        }
        array_unshift($list, ['公司', '项目状态', '网站', '过期时间', '下单时间', '上线时间', '达标时间',
            '关键词指标', '实际达标关键词', '月流量', '询盘总数', '未续费原因', '销售', '客服',
            '项目总监', '项目经理', '优化组长', '建站采编', '竞价', '软文', '设计', '前端', '合同签订时间', '百千约访时间', '续费次数', '续费时间']);
        return (new BasicExport($list))->download(sprintf('未续费%s.xls', date('YmdHis')));
    }

    //退款
    public function refund(Request $request)
    {
        if (!$request->ajax()) {
            return view('admin/analyze/refund', [
                'services' => Role::getUsers(Role::TYPE_SERVER), //服务人员
                'optimizers' => Role::getUsers(Role::TYPE_OPTIMIZER),
                'managers' => Role::getUsers(Role::TYPE_MANAGER),
                'managerHelpers' => Role::getUsers(Role::TYPE_MANAGE_HELPER),
                'sellerUsers' => Role::getUsers(Role::TYPE_SELLER),
                'editors' => Role::getUsers(Role::TYPE_AE),
            ]);
        }

        $builder = $this->refundBuilder();
        $sites = $builder->orderBy('expired_at', 'desc')->paginate($request->input('pageSize') ?? TABLE_PAGE_SIZE);
        $items = $sites->items();


        $oldIds = array_filter(array_column($items, 'old_id'));
        [$top10ListMap, $listReportMap] = $this->getInquiryAndFlowList($oldIds);

        foreach ($items as $item) {
            $item->status = Site::STATUS_MAP[$item->status];
            $item->managers_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGER)->pluck('nickname')->toArray());
            $item->manage_helper_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGE_HELPER)->pluck('nickname')->toArray());
            $item->server_title = implode('-', $item->users->where('role_id', Role::TYPE_SERVER)->pluck('nickname')->toArray());
            $item->seller_title = implode('-', $item->users->where('role_id', Role::TYPE_SELLER)->pluck('nickname')->toArray());
            $item->optimizerTitle = implode('-', $item->users->where('role_id', Role::TYPE_OPTIMIZER)->pluck('nickname')->toArray());
            $item->ae_title = implode('-', $item->users->where('role_id', Role::TYPE_AE)->pluck('nickname')->toArray());

            $item->refund = $item->sitePayment->refund ?? 0;
            $item->top10 = $item->old_id ? ($top10ListMap[$item->old_id]->top10 ?? '-') : '未关联'; //实际达标关键词
            $item->traffic = $item->old_id ? ($listReportMap[$item->old_id]->traffic ?? '-') : '未关联';
            $item->inquire = $item->old_id ? ($listReportMap[$item->old_id]->inquire ?? '-') : '未关联';

            $item->bidTitle = implode('-', $item->users->where('role_id', Role::TYPE_BID)->pluck('nickname')->toArray());
            $item->articleTitle = implode('-', $item->users->where('role_id', Role::TYPE_PROPAGANDA)->pluck('nickname')->toArray());
            $item->designerTitle = implode('-', $item->users->where('role_id', Role::TYPE_DESIGNER)->pluck('nickname')->toArray());
            $item->webTitle = implode('-', $item->users->where('role_id', Role::TYPE_WEB)->pluck('nickname')->toArray());

            $item->refund_date = substr($item->refund_date, 0, 10);
            $item->order_at = substr($item->order_at, 0, 10);
            $item->assign_at = substr($item->assign_at, 0, 10);
            $item->bq_at = substr($item->bq_at, 0, 10);
            $item->renewal_at = substr($item->renewal_at, 0, 10);

            $item->expired_at = substr($item->expired_at, 0, 10);
            $item->reach_at = substr($item->reach_at, 0, 10);
            $item->online_at = substr($item->online_at, 0, 10);
            $item->is_renewal = empty($item->renewal_at) ? '否' : '是';
            $item->refundReason = self::REFUND_REASON_LIST[$item->sitePayment->refund_reason_analyze];
        };


        return response()->json([
            'rows' => $items,
            'total' => $sites->total()
        ]);
    }

    public function refundBuilder()
    {
        $request = \request();
        $filterUserIds = [];
        $optimizerId = $request->input('optimizerId');
        $optimizerId && $filterUserIds[] = $optimizerId;
        $managerId = $request->input('managerId');
        $managerId && $filterUserIds[] = $managerId;
        $serverId = $request->input('serverId');
        $serverId && $filterUserIds[] = $serverId;
        $sellerId = $request->input('sellerId');
        $sellerId && $filterUserIds[] = $sellerId;
        $manageHelperId = $request->input('manageHelperId');
        $manageHelperId && $filterUserIds[] = $manageHelperId;
        $editorId = $request->input('editorId');
        $editorId && $filterUserIds[] = $editorId;

        $keyword = $request->input('keyword');
        $status = $request->input('status');

        $siteIds = SitePayment::query()->whereNotNull('refund')->pluck('site_id');

        $build = Site::query()->with(['users', 'sitePayment']);
        $notRenewedReasonAnalyze = $request->input('not_renewed_reason_analyze');
        if (!empty($notRenewedReasonAnalyze)) {
            $siteIds = SitePayment::query()
                ->where('not_renewed_reason_analyze', $notRenewedReasonAnalyze)
                ->pluck('site_id');
            $build->whereIn('id', $siteIds);
        }

        $refundReasonAnalyze = $request->input('refund_reason_analyze');
        if (!empty($refundReasonAnalyze)) {
            $siteIds = SitePayment::query()
                ->where('refund_reason_analyze', $refundReasonAnalyze)
                ->pluck('site_id');
            $build->whereIn('id', $siteIds);
        }

        $builder = $build
            ->whereIn('id', $siteIds)
            ->with(['users', 'sitePayment'])
            ->where(function (Builder $q) use ($keyword, $status) {
                if ($keyword) {
                    $q->where('domain', 'like', '%' . $keyword . '%')
                        ->orWhere('cn_title', 'like', '%' . $keyword . '%');
                }
                if ($status) {
                    $q->where('status', $status);
                }
            });

        if ($filterUserIds) {
            $builder->whereExists(function (\Illuminate\Database\Query\Builder $b) use ($filterUserIds) {
                $raw = sprintf('SUM(CASE WHEN user_id in (%s) then  1 ELSE  0 end) as total', implode(',', $filterUserIds));
                $b->select(DB::raw($raw))->from('user_has_sites')
                    ->whereRaw(sprintf('user_has_sites.site_id=sites.id HAVING total=%s', count($filterUserIds)));
            });
        }
        return $builder;
    }

    public function refundExport()
    {
        $builder = $this->refundBuilder();
        $sites = $builder->orderBy('expired_at', 'desc')->get()->toArray();
        $list = [];

        $oldIds = array_filter(array_column($sites, 'old_id'));
        [$top10ListMap, $listReportMap] = $this->getInquiryAndFlowList($oldIds);

        foreach ($sites as $key => $site) {

            $sellerTitle = [];//销售
            $serverTitle = [];//客服
            $managersTitle = [];//项目经理1
            $manageHelperTitle = [];//项目经理

            $aeTitle = [];//采编
            $optimizerTitle = [];//优化师
            $bidTitle = [];//竞价
            $articleTitle = [];//软文
            $designerTitle = [];//设计
            $webTitle = [];//前端

            $userList = $site['users'];
            foreach ($userList as $kk => $value) {
                if ($value['role_id'] == Role::TYPE_SELLER) {
                    $sellerTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_SERVER) {
                    $serverTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_MANAGER) {
                    $managersTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_MANAGE_HELPER) {
                    $manageHelperTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_AE) {
                    $aeTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_OPTIMIZER) {
                    $optimizerTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_BID) {
                    $bidTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_PROPAGANDA) {
                    $articleTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_DESIGNER) {
                    $designerTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_WEB) {
                    $webTitle [] = $value['nickname'];
                }
            }


            $list[$key]['cn_title'] = $site['cn_title'];
            $list[$key]['status'] = Site::STATUS_MAP[$site['status']];

            $list[$key]['domain'] = $site['domain'];
            $list[$key]['refund'] = $site['site_payment']['reach'] ?? '';
            $list[$key]['refund_date'] = substr($site['refund_date'], 0, 10);//过期时间
            $list[$key]['order_at'] = substr($site['order_at'], 0, 10);//下单时间
            $list[$key]['online_at'] = substr($site['online_at'], 0, 10);//上线时间
            $list[$key]['reach_at'] = substr($site['reach_at'], 0, 10);//达标时间
            $list[$key]['keyword_goal'] = $site['keyword_goal'];//关键词

            $list[$key]['top10'] = $top10ListMap[$site['old_id']]->top10 ?? '';
            $list[$key]['traffic'] = $listReportMap[$site['old_id']]->traffic ?? '';
            $list[$key]['inquire'] = $listReportMap[$site['old_id']]->inquire ?? '';
            $list[$key]['refundReason'] = self::REFUND_REASON_LIST[$site['site_payment']['refund_reason_analyze']];

            $list[$key]['seller_title'] = implode('-', $sellerTitle);//销售
            $list[$key]['server_title'] = implode('-', $serverTitle);//客服经理
            $list[$key]['managers_title'] = implode('-', $managersTitle);//项目经理1
            $list[$key]['manage_helper_title'] = implode('-', $manageHelperTitle);//项目经理

            $list[$key]['optimizerTitle'] = implode('-', $optimizerTitle);//优化
            $list[$key]['ae_title'] = implode('-', $aeTitle);//采编
            $list[$key]['bidTitle'] = implode('-', $bidTitle);//竞价
            $list[$key]['articleTitle'] = implode('-', $articleTitle);//软文
            $list[$key]['designerTitle'] = implode('-', $designerTitle);//设计
            $list[$key]['webTitle'] = implode('-', $webTitle);//前端

            $list[$key]['sign_at'] = substr($site['sign_at'], 0, 10);//合同签订时间
            $list[$key]['assign_at'] = substr($site['assign_at'], 0, 10);//分配时间
            $list[$key]['bq_at'] = substr($site['bq_at'], 0, 10);//百千约访时间
            $list[$key]['renewal_times'] = $site['renewal_times'];//续费次数
            $list[$key]['renewal_at'] = substr($site['renewal_at'], 0, 10);//续费时间
        }
        array_unshift($list, ['公司', '项目状态', '网站', '退款金额', '退款时间', '下单时间', '上线时间',
            '达标时间', '关键词指标', '实际达标关键词', '月流量', '询盘总数', '退款原因', '销售', '客服',
            '项目总监', '项目经理', '优化组长', '建站采编', '竞价', '软文', '设计', '前端', '合同签订时间', '分配时间', '百千约访时间', '续费次数', '续费时间']);
        return (new BasicExport($list))->download(sprintf('退款%s.xls', date('YmdHis')));
    }


    //已续费
    public function hasRenewal(Request $request)
    {
        if (!$request->ajax()) {

            return view('admin/analyze/has_renewal', [
                'services' => Role::getUsers(Role::TYPE_SERVER), //服务人员
                'optimizers' => Role::getUsers(Role::TYPE_OPTIMIZER),
                'managers' => Role::getUsers(Role::TYPE_MANAGER),
                'managerHelpers' => Role::getUsers(Role::TYPE_MANAGE_HELPER),
                'sellerUsers' => Role::getUsers(Role::TYPE_SELLER),
                'editors' => Role::getUsers(Role::TYPE_AE),
            ]);
        }

        $builder = $this->hasRenewalRenewalBuilder();
        $sites = $builder->orderBy('renewal_at')->paginate($request->input('pageSize') ?? TABLE_PAGE_SIZE);

        $items = $sites->items();

        $oldIds = array_filter(array_column($items, 'old_id'));
        [$top10ListMap, $listReportMap] = $this->getInquiryAndFlowList($oldIds);


        foreach ($items as $item) {
            $item->status = Site::STATUS_MAP[$item->status];
            $item->managers_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGER)->pluck('nickname')->toArray());
            $item->manage_helper_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGE_HELPER)->pluck('nickname')->toArray());
            $item->server_title = implode('-', $item->users->where('role_id', Role::TYPE_SERVER)->pluck('nickname')->toArray());
            $item->seller_title = implode('-', $item->users->where('role_id', Role::TYPE_SELLER)->pluck('nickname')->toArray());
            $item->optimizerTitle = implode('-', $item->users->where('role_id', Role::TYPE_OPTIMIZER)->pluck('nickname')->toArray());
            $item->ae_title = implode('-', $item->users->where('role_id', Role::TYPE_AE)->pluck('nickname')->toArray());

            $item->top10 = $item->old_id ? ($top10ListMap[$item->old_id]->top10 ?? '-') : '未关联'; //实际达标关键词
            $item->traffic = $item->old_id ? ($listReportMap[$item->old_id]->traffic ?? '-') : '未关联';
            $item->inquire = $item->old_id ? ($listReportMap[$item->old_id]->inquire ?? '-') : '未关联';

            $item->bidTitle = implode('-', $item->users->where('role_id', Role::TYPE_BID)->pluck('nickname')->toArray());
            $item->articleTitle = implode('-', $item->users->where('role_id', Role::TYPE_PROPAGANDA)->pluck('nickname')->toArray());
            $item->designerTitle = implode('-', $item->users->where('role_id', Role::TYPE_DESIGNER)->pluck('nickname')->toArray());
            $item->webTitle = implode('-', $item->users->where('role_id', Role::TYPE_WEB)->pluck('nickname')->toArray());

            $item->sign_at = substr($item->sign_at, 0, 10);
            $item->order_at = substr($item->order_at, 0, 10);
            $item->assign_at = substr($item->assign_at, 0, 10);
            $item->expired_at = substr($item->expired_at, 0, 10);
            $item->bq_at = substr($item->bq_at, 0, 10);

            $item->renewal_at = substr($item->renewal_at, 0, 10);
            $item->reach_at = substr($item->reach_at, 0, 10);
            $item->online_at = substr($item->online_at, 0, 10);

            $item->is_renewal = empty($item->renewal_at) ? '否' : '是';
            //$item->renew_type = empty($item->is_renew) ? '优化续费' : '网站续费';

        };
        return response()->json([
            'rows' => $items,
            'total' => $sites->total()
        ]);
    }

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

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

    public function hasRenewalRenewalBuilder()
    {
        $request = \request();
        $filterUserIds = [];
        $optimizerId = $request->input('optimizerId');
        $optimizerId && $filterUserIds[] = $optimizerId;
        $managerId = $request->input('managerId');
        $managerId && $filterUserIds[] = $managerId;
        $serverId = $request->input('serverId');
        $serverId && $filterUserIds[] = $serverId;
        $sellerId = $request->input('sellerId');
        $sellerId && $filterUserIds[] = $sellerId;
        $manageHelperId = $request->input('manageHelperId');
        $manageHelperId && $filterUserIds[] = $manageHelperId;
        $editorId = $request->input('editorId');
        $editorId && $filterUserIds[] = $editorId;

        $keyword = $request->input('keyword');
        $status = $request->input('status');
        $builder = Site::query()
            ->where('status', 3)
            ->with(['users'])->where($condition ?? [])->where(function (Builder $q) use ($keyword, $status) {
                if ($keyword) {
                    $q->where('domain', 'like', '%' . $keyword . '%')
                        ->orWhere('cn_title', 'like', '%' . $keyword . '%');
                }
                if ($status) {
                    $q->where('status', $status);
                }
            });

        $startDate = $request->input('startDate');
        $endDate = $request->input('endDate');

        if ($startDate) {
            $start = sprintf("%s-01 00:00:00", $startDate);
            $builder->where('renewal_at', '>=', $start);
        }

        if ($endDate) {
            $end = sprintf("%s-%s 23:59:59", $endDate, date('t', strtotime($endDate)));
            $builder->where('renewal_at', '<=', $end);
        }

        if ($startDate && $endDate) {
            $startDate = sprintf("%s-01 00:00:00", $startDate);
            $builder->where('renewal_at', '>=', $startDate)
                ->where('renewal_at', '<=', sprintf("%s-%s 23:59:59", $endDate, date('t', strtotime($endDate))));
        }

        if (!$startDate && !$endDate) {
            $startDate = date('Y-m-01 00:00:00');
            $builder->Where('renewal_at', '>=', $startDate)
                ->where('renewal_at', '<=', sprintf("%s-%s 23:59:59", date('Y-m'), date('t', strtotime($startDate))));
        }

        if ($filterUserIds) {
            $builder->whereExists(function (\Illuminate\Database\Query\Builder $b) use ($filterUserIds) {

                $raw = sprintf('SUM(CASE WHEN user_id in (%s) then  1 ELSE  0 end) as total', implode(',', $filterUserIds));
                $b->select(DB::raw($raw))->from('user_has_sites')
                    ->whereRaw(sprintf('user_has_sites.site_id=sites.id HAVING total=%s', count($filterUserIds)));
            });
        }

        return $builder;
    }

    public function hasRenewalExport()
    {
        $builder = $this->hasRenewalRenewalBuilder();
        $sites = $builder->orderBy('renewal_at')->get()->toArray();
        $list = [];

        $oldIds = array_filter(array_column($sites, 'old_id'));
        [$top10ListMap, $listReportMap] = $this->getInquiryAndFlowList($oldIds);

        foreach ($sites as $key => $site) {

            $sellerTitle = [];//销售
            $serverTitle = [];//客服
            $managersTitle = [];//项目经理1
            $manageHelperTitle = [];//项目经理

            $aeTitle = [];//采编
            $optimizerTitle = [];//优化师
            $bidTitle = [];//竞价
            $articleTitle = [];//软文
            $designerTitle = [];//设计
            $webTitle = [];//前端

            $userList = $site['users'];
            foreach ($userList as $kk => $value) {
                if ($value['role_id'] == Role::TYPE_SELLER) {
                    $sellerTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_SERVER) {
                    $serverTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_MANAGER) {
                    $managersTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_MANAGE_HELPER) {
                    $manageHelperTitle [] = $value['nickname'];
                }

                if ($value['role_id'] == Role::TYPE_AE) {
                    $aeTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_OPTIMIZER) {
                    $optimizerTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_BID) {
                    $bidTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_PROPAGANDA) {
                    $articleTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_DESIGNER) {
                    $designerTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_WEB) {
                    $webTitle [] = $value['nickname'];
                }
            }
            $list[$key]['cn_title'] = $site['cn_title'];
            $list[$key]['status'] = Site::STATUS_MAP[$site['status']];

            $list[$key]['domain'] = $site['domain'];

            $list[$key]['order_at'] = substr($site['order_at'], 0, 10);//下单时间
            $list[$key]['online_at'] = substr($site['online_at'], 0, 10);//上线时间
            $list[$key]['renewal_at'] = substr($site['renewal_at'], 0, 10);//续费时间
            $list[$key]['renewal_times'] = $site['renewal_times'];//续费次数
            $list[$key]['renew_years'] = $site['renew_years'] ?? 0;//最近一次续费年限
            $list[$key]['reach_at'] = substr($site['reach_at'], 0, 10);//达标时间
            $list[$key]['keyword_goal'] = $site['keyword_goal'];//关键词

            $list[$key]['top10'] = $top10ListMap[$site['old_id']]->top10 ?? '';
            $list[$key]['traffic'] = $listReportMap[$site['old_id']]->traffic ?? '';
            $list[$key]['inquire'] = $listReportMap[$site['old_id']]->inquire ?? '';

            $list[$key]['seller_title'] = implode('-', $sellerTitle);//销售
            $list[$key]['server_title'] = implode('-', $serverTitle);//客服经理
            $list[$key]['managers_title'] = implode('-', $managersTitle);//项目总监
            $list[$key]['manage_helper_title'] = implode('-', $manageHelperTitle);//项目经理

            $list[$key]['optimizerTitle'] = implode('-', $optimizerTitle);//优化组长
            $list[$key]['ae_title'] = implode('-', $aeTitle);//采编
            $list[$key]['bidTitle'] = implode('-', $bidTitle);//竞价
            $list[$key]['articleTitle'] = implode('-', $articleTitle);//软文
            $list[$key]['designerTitle'] = implode('-', $designerTitle);//设计
            $list[$key]['webTitle'] = implode('-', $webTitle);//前端

            $list[$key]['sign_at'] = substr($site['sign_at'], 0, 10);//合同签订时间
            $list[$key]['expired_at'] = substr($site['expired_at'], 0, 10);//过期时间
            $list[$key]['bq_at'] = substr($site['bq_at'], 0, 10);//百千约访时间
        }
        array_unshift($list, ['公司', '项目状态', '网站', '下单时间', '上线时间', '续费时间', '续费次数', '最近一次续费年限', '达标时间',
            '关键词指标', '实际达标关键词', '月流量', '询盘总数', '销售', '客服', '项目总监', '项目经理', '优化组长',
            '建站采编', '竞价', '软文', '设计', '前端', '合同签订时间',  '过期时间', '百千约访时间']);
        return (new BasicExport($list))->download(sprintf('已续费%s.xls', date('YmdHis')));
    }

    //百千计划
    public function plan(Request $request)
    {
        if (!$request->ajax()) {
            return view('admin/analyze/plan', [
                'services' => Role::getUsers(Role::TYPE_SERVER), //服务人员
                'optimizers' => Role::getUsers(Role::TYPE_OPTIMIZER),
                'managers' => Role::getUsers(Role::TYPE_MANAGER),
                'managerHelpers' => Role::getUsers(Role::TYPE_MANAGE_HELPER),
                'sellerUsers' => Role::getUsers(Role::TYPE_SELLER),
            ]);
        }

        list($items, $nowtMonthKeywords, $nowListReportMap, $listReportMapResult, $listReportMapResult1, $keywordDropResults, $keywordDropResults1, $clicksDropResults, $clicksDropResults1, $sites, $sortName, $sortOrder) = $this->planBuilder();

        $result = [];
        foreach ($items as $item) {

            $item->managers_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGER)->pluck('nickname')->toArray());
            $item->manage_helper_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGE_HELPER)->pluck('nickname')->toArray());
            $item->server_title = implode('-', $item->users->where('role_id', Role::TYPE_SERVER)->pluck('nickname')->toArray());
            $item->seller_title = implode('-', $item->users->where('role_id', Role::TYPE_SELLER)->pluck('nickname')->toArray());
            $item->optimizerTitle = implode('-', $item->users->where('role_id', Role::TYPE_OPTIMIZER)->pluck('nickname')->toArray());
            $item->ae_title = implode('-', $item->users->where('role_id', Role::TYPE_AE)->pluck('nickname')->toArray());
            $item->bidTitle = implode('-', $item->users->where('role_id', Role::TYPE_BID)->pluck('nickname')->toArray());
            $item->articleTitle = implode('-', $item->users->where('role_id', Role::TYPE_PROPAGANDA)->pluck('nickname')->toArray());
            $item->designerTitle = implode('-', $item->users->where('role_id', Role::TYPE_DESIGNER)->pluck('nickname')->toArray());
            $item->webTitle = implode('-', $item->users->where('role_id', Role::TYPE_WEB)->pluck('nickname')->toArray());

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

            $item->listReportMapResult = $listReportMapResult[$item->old_id] ?? '';
            $item->listReportMapResult1 = $listReportMapResult1[$item->old_id] ?? '';

            $item->keywordDropResults = $keywordDropResults[$item->old_id] ?? '';
            $item->keywordDropResults1 = $keywordDropResults1[$item->old_id] ?? '';

            $item->clicksDropResults = $clicksDropResults[$item->old_id] ?? '';
            $item->clicksDropResults1 = $clicksDropResults1[$item->old_id] ?? '';

            $item->reach_at = substr($item->reach_at, 0, 10);
            $item->online_at = substr($item->online_at, 0, 10);
            $item->bq_at = substr($item->bq_at, 0, 10);
            $item->bq_time = strtotime($item->bq_at);

            $item->sign_at = substr($item->sign_at, 0, 10);
            $item->order_at = substr($item->order_at, 0, 10);
            $item->assign_at = substr($item->assign_at, 0, 10);
            $item->expired_at = substr($item->expired_at, 0, 10);
            $item->renewal_at = substr($item->renewal_at, 0, 10);

            $result[] = $item;
        };

        $resList = collect($result);
        if ($sortName == 'bq_at') {
            $sortName = 'bq_time';
        }
        if (in_array($sortName, ['bq_time', 'traffic', 'inquire', 'top10'])) {
            $descending = strtolower($sortOrder) == 'asc' ? false : true;
            $resList = $resList->sortBy(function ($item, $key) use ($sortName, $descending) {
                return intval($item[$sortName]);
            }, SORT_REGULAR, $descending)->values();
        }


        return response()->json([
            'rows' => $resList,
            'total' => $sites->total()
        ]);
    }


    public function planBuilder($type = false)
    {
        $user = auth()->user();

        if (empty($user->is_super) && !in_array($user->id, [240, 554, 836])) {
            $siteList = DB::table('user_has_sites')->where('user_id', $user->id)->pluck('site_id')->toArray();
            $condition[] = [function ($query) use ($siteList) {
                $query->whereIn('id', $siteList);
            }];
        }

        $condition['is_bq'] = 1;
        $request = \request();

        $filterUserIds = [];
        $optimizerId = $request->input('optimizerId');
        $optimizerId && $filterUserIds[] = $optimizerId;
        $managerId = $request->input('managerId');
        $managerId && $filterUserIds[] = $managerId;
        $serverId = $request->input('serverId');
        $serverId && $filterUserIds[] = $serverId;
        $sellerId = $request->input('sellerId');
        $sellerId && $filterUserIds[] = $sellerId;
        $manageHelperId = $request->input('manageHelperId');
        $manageHelperId && $filterUserIds[] = $manageHelperId;

        $sortName = $request->input('sortName');
        $sortOrder = $request->input('sortOrder');

        $keyword = $request->input('keyword');
        $builder = Site::query()->with(['users'])
            ->where('status', '!=', '7')
            ->where($condition)
            ->whereIn('status', [2, 3])->where(function (Builder $q) use ($keyword) {
                if ($keyword) {
                    $q->where('domain', 'like', '%' . $keyword . '%')
                        ->orWhere('cn_title', 'like', '%' . $keyword . '%');
                }
            });

        if ($filterUserIds) {
            $builder->whereExists(function (\Illuminate\Database\Query\Builder $b) use ($filterUserIds) {
                $raw = sprintf('SUM(CASE WHEN user_id in (%s) then  1 ELSE  0 end) as total', implode(',', $filterUserIds));
                $b->select(DB::raw($raw))->from('user_has_sites')
                    ->whereRaw(sprintf('user_has_sites.site_id=sites.id HAVING total=%s', count($filterUserIds)));
            });
        }
        if ($type == false) {
            $sites = $builder->orderByDesc('id')->paginate($request->input('pageSize') ?? TABLE_PAGE_SIZE);
            $items = $sites->items();

        } else {
            $sites = $builder->orderByDesc('id')->get();
            $items = $sites->toArray();
        }

        //所有百千项目
        $siteBqList = Site::query()
            ->where('status', '!=', '7')
            ->where('is_bq', 1)
            ->whereIn('status', [2, 3])
            ->get();

        $projectCondition = [];
        foreach ($siteBqList as $item) {
            $data = [
                'old_id' => $item->old_id,
                'bq_at' => date('Y-m', strtotime($item->bq_at)),
            ];
            $projectCondition[] = $data;
        }

        //上个月的关键词排名
        if (!Cache::has(CacheConstant::NOW_MONTH_KEY_WORDS_CACHE_KEY)) {
            $maxIdList = DB::connection('rank')
                ->table('project_history')
                ->selectRaw('max(id) as max_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('max_id')->toArray();

            $nowtMonthKeywords = DB::connection('rank')->table('project_history')
                ->select('top10', 'project_id')->whereIn('id', $maxIdList)
                ->pluck('top10', 'project_id')->toArray();

            Cache::put(CacheConstant::NOW_MONTH_KEY_WORDS_CACHE_KEY, $nowtMonthKeywords, mt_rand(54000, 57600));
        }
        $nowtMonthKeywords = Cache::get(CacheConstant::NOW_MONTH_KEY_WORDS_CACHE_KEY) ?? [];

        //百千约访时的关键词排名
        if (!Cache::has(CacheConstant::LAST_MONTH_BQ_KEY_WORDS_CACHE_KEY)) {
            $bqLastKeywordsList = [];
            foreach ($projectCondition as $value) {

                $maxIdList = DB::connection('rank')
                    ->table('project_history')
                    ->selectRaw('max(id) as max_id')->where([
                        ['create_time', '>', strtotime($value['bq_at'])],
                        ['create_time', '<', strtotime('+1 month', strtotime($value['bq_at']))],
                        ['project_id', '=', $value['old_id']],
                    ])->groupBy('project_id')->pluck('max_id')->toArray();

                $keywordsInfo = DB::connection('rank')->table('project_history')
                    ->select('top10', 'project_id')->whereIn('id', $maxIdList)
                    ->pluck('top10', 'project_id')->toArray();

                foreach ($keywordsInfo as $key => $item) {
                    $bqLastKeywordsList[$key] = $item;
                }
            }
            Cache::put(CacheConstant::LAST_MONTH_BQ_KEY_WORDS_CACHE_KEY, $bqLastKeywordsList, mt_rand(54000, 57600));
        }
        $bqLastKeywordsList = Cache::get(CacheConstant::LAST_MONTH_BQ_KEY_WORDS_CACHE_KEY);

        $keywordDropResults = [];
        $keywordDropResults1 = [];

        foreach ($nowtMonthKeywords as $inx => $val) {
            $nowTop10 = $bqLastKeywordsList[$inx] ?? 0;

            if ($nowTop10 > 0) {
                $lessVal = $val - $nowTop10;
                if ($lessVal > 0) {
                    $keywordDropResults1[$inx] = $lessVal;
                } else {
                    $keywordDropResults[$inx] = abs($lessVal);
                }
            }
        }

        //百千时间的询盘
        if (!Cache::has(CacheConstant::LAST_MONTH_BQ_INQUIRE_CACHE_KEY)) {
            $bqLastListReportList = [];
            foreach ($projectCondition as $value) {
                $listReportList = DB::connection('rank')
                    ->table('project_listreport')
                    ->where('ym', date('Ym', strtotime($value['bq_at'])))
                    ->where('project_id', $value['old_id'])
                    ->get()->keyBy('project_id')->toArray();
                foreach ($listReportList as $key => $item) {
                    $bqLastListReportList[$key] = $item;
                }
            }
            Cache::put(CacheConstant::LAST_MONTH_BQ_INQUIRE_CACHE_KEY, $bqLastListReportList, mt_rand(54000, 57600));
        }
        $bqLastListReportList = Cache::get(CacheConstant::LAST_MONTH_BQ_INQUIRE_CACHE_KEY);

        //上月询盘
        if (!Cache::has(CacheConstant::NOW_MONTH_INQUIRE_CACHE_KEY)) {
            $lastListReportList = DB::connection('rank')
                ->table('project_listreport')
                ->where('ym', date('Ym', strtotime('first day of -1 month')))
                ->get()->keyBy('project_id')->toArray();
            Cache::put(CacheConstant::NOW_MONTH_INQUIRE_CACHE_KEY, $lastListReportList, mt_rand(54000, 57600));
        }
        $bqLastMonthListReportList = Cache::get(CacheConstant::NOW_MONTH_INQUIRE_CACHE_KEY);

        $listReportMapResult = [];
        $listReportMapResult1 = [];

        foreach ($bqLastListReportList as $inx => $val) {
            $lastOne = $bqLastMonthListReportList[$inx] ?? null;
            if (empty($lastOne)) {
                continue;
            }
            $lessVal = $val->inquire - $lastOne->inquire;
            if ($lessVal > 0) {
                $listReportMapResult[$inx] = $lessVal;
            } else {
                $listReportMapResult1[$inx] = abs($lessVal);
            }
        }

        if (!Cache::has(CacheConstant::NOW_MONTH_FLOW_CACHE_KEY)) {
            $nowtMonthFlow = DB::connection('rank')->table('webmaster_effect')->selectRaw('SUM(clicks) as clicks,project_id')
                ->where(['ym' => date('Ym', strtotime('first day of -1 month'))])
                ->groupBy('project_id')->get()->keyBy('project_id')->toArray();
            Cache::put(CacheConstant::NOW_MONTH_FLOW_CACHE_KEY, $nowtMonthFlow, mt_rand(54000, 57600));
        }
        $nowtMonthFlow = Cache::get(CacheConstant::NOW_MONTH_FLOW_CACHE_KEY);

        if (!Cache::has(CacheConstant::LAST_MONTH_BQ_FLOW_CACHE_KEY)) {
            $bqLastClicksList = [];
            foreach ($projectCondition as $value) {
                $lastClicksList = DB::connection('rank')
                    ->table('webmaster_effect')
                    ->selectRaw('SUM(clicks) as clicks,project_id')
                    ->where(['ym' => date('Ym', strtotime($value['bq_at'])), 'project_id' => $value['old_id']])
                    ->groupBy('project_id')
                    ->get()->keyBy('project_id')->toArray();
                foreach ($lastClicksList as $key => $item) {
                    $bqLastClicksList[$key] = $item;
                }
            }
            Cache::put(CacheConstant::LAST_MONTH_BQ_FLOW_CACHE_KEY, $bqLastClicksList, mt_rand(54000, 57600));
        }
        $bqLastClicksList = Cache::get(CacheConstant::LAST_MONTH_BQ_FLOW_CACHE_KEY);

        $clicksDropResults = [];
        $clicksDropResults1 = [];

        foreach ($bqLastClicksList as $inx => $val) {
            $lastOne = $nowtMonthFlow[$inx] ?? null;
            if (empty($lastOne)) {
                continue;
            }
            $lessVal = $val->clicks - $lastOne->clicks;
            if ($lessVal > 0) {
                $clicksDropResults[$inx] = $lessVal;
            } else {
                $clicksDropResults1[$inx] = abs($lessVal);
            }
        }

        return [$items, $nowtMonthKeywords, $bqLastMonthListReportList, $listReportMapResult, $listReportMapResult1, $keywordDropResults, $keywordDropResults1, $clicksDropResults, $clicksDropResults1, $sites, $sortName, $sortOrder];
    }

    public function planExport()
    {
        $list = [];
        list($items, $nowtMonthKeywords, $nowListReportMap, $listReportMapResult, $listReportMapResult1, $keywordDropResults, $keywordDropResults1, $clicksDropResults, $clicksDropResults1, $sites, $sortName, $sortOrder) = $this->planBuilder(true);

        foreach ($items as $key => $site) {
            $sellerTitle = [];//销售
            $serverTitle = [];//客服
            $managersTitle = [];//项目经理1
            $manageHelperTitle = [];//项目经理

            $aeTitle = [];//采编
            $optimizerTitle = [];//优化师
            $bidTitle = [];//竞价
            $articleTitle = [];//软文
            $designerTitle = [];//设计
            $webTitle = [];//前端

            $userList = $site['users'];
            foreach ($userList as $kk => $value) {
                if ($value['role_id'] == Role::TYPE_SELLER) {
                    $sellerTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_SERVER) {
                    $serverTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_MANAGER) {
                    $managersTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_MANAGE_HELPER) {
                    $manageHelperTitle [] = $value['nickname'];
                }

                if ($value['role_id'] == Role::TYPE_AE) {
                    $aeTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_OPTIMIZER) {
                    $optimizerTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_BID) {
                    $bidTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_PROPAGANDA) {
                    $articleTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_DESIGNER) {
                    $designerTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_WEB) {
                    $webTitle [] = $value['nickname'];
                }
            }
            $list[$key]['cn_title'] = $site['cn_title'];
            $list[$key]['domain'] = $site['domain'];

            $list[$key]['order_at'] = substr($site['order_at'], 0, 10);//下单时间
            $list[$key]['online_at'] = substr($site['online_at'], 0, 10);//上线时间
            $list[$key]['bq_at'] = substr($site['bq_at'], 0, 10);//百千约访时间
            $list[$key]['bq_score'] = $site['bq_score'];//百千客户评分

            $list[$key]['traffic'] = $nowListReportMap[$site['old_id']]->traffic ?? '';
            $list[$key]['top10'] = $nowtMonthKeywords[$site['old_id']] ?? '';
            $list[$key]['inquire'] = $nowListReportMap[$site['old_id']]->inquire ?? '';

            $list[$key]['seller_title'] = implode('-', $sellerTitle);//销售
            $list[$key]['server_title'] = implode('-', $serverTitle);//客服经理
            $list[$key]['managers_title'] = implode('-', $managersTitle);//项目经理1
            $list[$key]['manage_helper_title'] = implode('-', $manageHelperTitle);//项目经理

            $list[$key]['optimizerTitle'] = implode('-', $optimizerTitle);//优化
            $list[$key]['ae_title'] = implode('-', $aeTitle);//采编
            $list[$key]['bidTitle'] = implode('-', $bidTitle);//竞价
            $list[$key]['articleTitle'] = implode('-', $articleTitle);//软文
            $list[$key]['designerTitle'] = implode('-', $designerTitle);//设计
            $list[$key]['webTitle'] = implode('-', $webTitle);//前端
            $list[$key]['sign_at'] = substr($site['sign_at'], 0, 10);//合同签订时间
            $list[$key]['assign_at'] = substr($site['assign_at'], 0, 10);//分配时间
            $list[$key]['reach_at'] = substr($site['reach_at'], 0, 10);//达标时间
            $list[$key]['expired_at'] = substr($site['expired_at'], 0, 10);//过期时间
            $list[$key]['renewal_times'] = $site['renewal_times'];//续费次数
            $list[$key]['renewal_at'] = substr($site['renewal_at'], 0, 10);//续费时间
        }

        array_unshift($list, ['公司', '网站', '下单时间', '上线时间', '百千约访时间', '百千客户评分',
            '月流量', '月排名', '询盘', '项目销售', '客服经理', '项目总监', '项目经理', '优化师', '建站采编',
            '竞价', '软文', '设计', '前端', '合同签订时间', '分配时间', '达标时间', '过期时间', '续费次数', '续费时间']);
        return (new BasicExport($list))->download(sprintf('百千计划%s.xls', date('YmdHis')));
    }

    //引擎力案例
    public function example(Request $request)
    {
        if (!$request->ajax()) {

            $business = Business::query()->pluck('title', 'id');
            return view('admin/analyze/example', [
                'services' => Role::getUsers(Role::TYPE_SERVER), //服务人员
                'optimizers' => Role::getUsers(Role::TYPE_OPTIMIZER),
                'managers' => Role::getUsers(Role::TYPE_MANAGER),
                'managerHelpers' => Role::getUsers(Role::TYPE_MANAGE_HELPER),
                'sellerUsers' => Role::getUsers(Role::TYPE_SELLER),
                'business' => $business
            ]);
        }

        $filterUserIds = [];

        $optimizerId = $request->input('optimizerId');
        $optimizerId && $filterUserIds[] = $optimizerId;
        $managerId = $request->input('managerId');
        $managerId && $filterUserIds[] = $managerId;
        $serverId = $request->input('serverId');
        $serverId && $filterUserIds[] = $serverId;
        $sellerId = $request->input('sellerId');
        $sellerId && $filterUserIds[] = $sellerId;
        $manageHelperId = $request->input('manageHelperId');
        $manageHelperId && $filterUserIds[] = $manageHelperId;

        $keyword = $request->input('keyword');
        $ids = [];
        $caseIds1 = $request->input('caseIds1');
        if ($caseIds1) {
            $ids[] = $caseIds1;
        }
        $caseIds2 = $request->input('caseIds2');
        if ($caseIds2) {
            $ids[] = $caseIds2;
        }
        $caseIds3 = $request->input('caseIds3');
        if ($caseIds3) {
            $ids[] = $caseIds3;
        }
        $caseIds4 = $request->input('caseIds4');
        if ($caseIds4) {
            $ids[] = $caseIds4;
        }
        $caseIds5 = $request->input('caseIds5');
        if ($caseIds5) {
            $ids[] = $caseIds5;
        }
        $caseIds6 = $request->input('caseIds6');
        if ($caseIds6) {
            $ids[] = $caseIds6;
        }
        $caseIds7 = $request->input('caseIds7');
        if ($caseIds7) {
            $ids[] = $caseIds7;
        }

        $businessId = $request->input('businessId');
        $builder = Site::query()->with(['users', 'business'])
            ->where('status', '!=', '7')
            ->where(['is_case' => 1])->where(function (Builder $q) use ($keyword, $ids, $businessId) {
                if ($keyword) {
                    $q->where('domain', 'like', '%' . $keyword . '%')
                        ->orWhere('cn_title', 'like', '%' . $keyword . '%');
                }
                if ($ids) {
                    $q->where('case_ids', 'like', '%' . implode(',', $ids) . '%');
                }
                if ($businessId) {
                    $q->where('business_id', $businessId);
                }
            });

        if ($filterUserIds) {
            $builder->whereExists(function (\Illuminate\Database\Query\Builder $b) use ($filterUserIds) {

                $raw = sprintf('SUM(CASE WHEN user_id in (%s) then  1 ELSE  0 end) as total', implode(',', $filterUserIds));
                $b->select(DB::raw($raw))->from('user_has_sites')
                    ->whereRaw(sprintf('user_has_sites.site_id=sites.id HAVING total=%s', count($filterUserIds)));
            });
        }
        $sites = $builder->orderByDesc('id')->paginate($request->input('pageSize') ?? TABLE_PAGE_SIZE);

        $items = $sites->items();


        $oldIds = array_filter(array_column($items, 'old_id'));
        [$top10ListMap, $listReportMap] = $this->getInquiryAndFlowList($oldIds);

        array_walk($items, function ($item) use (&$top10ListMap, &$listReportMap) {

            $item->managers_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGER)->pluck('nickname')->toArray());
            $item->manage_helper_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGE_HELPER)->pluck('nickname')->toArray());
            $item->server_title = implode('-', $item->users->where('role_id', Role::TYPE_SERVER)->pluck('nickname')->toArray());
            $item->seller_title = implode('-', $item->users->where('role_id', Role::TYPE_SELLER)->pluck('nickname')->toArray());
            $item->optimizerTitle = implode('-', $item->users->where('role_id', Role::TYPE_OPTIMIZER)->pluck('nickname')->toArray());
            $item->ae_title = implode('-', $item->users->where('role_id', Role::TYPE_AE)->pluck('nickname')->toArray());
            $item->businessTitle = $item->business->title ?? "";

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

            $item->renewal_at = $item->expired_at ? date('Y-m-d', strtotime('-3 month', strtotime($item->expired_at))) : null;
            $item->order_at = substr($item->order_at, 0, 10);
            $item->reach_at = substr($item->reach_at, 0, 10);
            $item->online_at = substr($item->online_at, 0, 10);
            $item->is_renewal = empty($item->renewal_at) ? '否' : '是';
            $item->case = $this->getCaseList($item->case_ids);
            $item->example_url = $item->example_url ?? '';

        });


        return response()->json([
            'rows' => $items,
            'total' => $sites->total()
        ]);
    }

    public function getCaseList($caseIds)
    {
        $result = '';
        if (!empty($caseIds)) {
            $list = [
                '21' => '结构化数据呈现',
                '22' => '关键词分析',
                '23' => '软文优化',
                '24' => '维基百科',
                '25' => '知识图谱',
                '26' => '竞价',
                '27' => '付费外链',
            ];
            $caseIds = explode(',', $caseIds);
            $result = [];
            foreach ($caseIds as $id) {
                $result[] = $list[$id];
            }
            $result = implode('<br>', $result);
        }
        return $result;

    }


    //上市
    public function listed(Request $request)
    {
        if (!$request->ajax()) {
            return view('admin/analyze/listed', [
                'services' => Role::getUsers(Role::TYPE_SERVER), //服务人员
                'optimizers' => Role::getUsers(Role::TYPE_OPTIMIZER),
                'managers' => Role::getUsers(Role::TYPE_MANAGER),
                'managerHelpers' => Role::getUsers(Role::TYPE_MANAGE_HELPER),
                'sellerUsers' => Role::getUsers(Role::TYPE_SELLER),
            ]);
        }
        $filterUserIds = [];
        $optimizerId = $request->input('optimizerId');
        $optimizerId && $filterUserIds[] = $optimizerId;
        $managerId = $request->input('managerId');
        $managerId && $filterUserIds[] = $managerId;
        $serverId = $request->input('serverId');
        $serverId && $filterUserIds[] = $serverId;
        $sellerId = $request->input('sellerId');
        $sellerId && $filterUserIds[] = $sellerId;
        $manageHelperId = $request->input('manageHelperId');
        $manageHelperId && $filterUserIds[] = $manageHelperId;


        $keyword = $request->input('keyword');
        $builder = Site::query()->with(['users', 'business'])
            ->where('status', '!=', '7')
            ->whereNotNull('enterprise_nature')->where(function (Builder $q) use ($keyword) {
                if ($keyword) {
                    $q->where('domain', 'like', '%' . $keyword . '%')
                        ->orWhere('cn_title', 'like', '%' . $keyword . '%');
                }
            });

        if ($filterUserIds) {
            $builder->whereExists(function (\Illuminate\Database\Query\Builder $b) use ($filterUserIds) {

                $raw = sprintf('SUM(CASE WHEN user_id in (%s) then  1 ELSE  0 end) as total', implode(',', $filterUserIds));
                $b->select(DB::raw($raw))->from('user_has_sites')
                    ->whereRaw(sprintf('user_has_sites.site_id=sites.id HAVING total=%s', count($filterUserIds)));
            });
        }
        $sites = $builder->orderByDesc('id')->paginate($request->input('pageSize') ?? TABLE_PAGE_SIZE);

        $items = $sites->items();

        $oldIds = array_filter(array_column($items, 'old_id'));
        [$top10ListMap, $listReportMap] = $this->getInquiryAndFlowList($oldIds);


        array_walk($items, function ($item) use (&$top10ListMap, &$listReportMap) {

            if (!empty($item->enterprise_nature)) {
                $list = explode(',', $item->enterprise_nature);
                foreach ($list as $value) {
                    if ($value == 1) {
                        $item->the500Strongest = 1;
                    }
                    if ($value == 2) {
                        $item->listedCompany = 1;
                    }
                    if ($value == 3) {
                        $item->stateEnterprises = 1;
                    }
                    if ($value == 4) {
                        $item->leadingEnterprise = 1;
                    }
                }
            }

            $item->managers_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGER)->pluck('nickname')->toArray());
            $item->manage_helper_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGE_HELPER)->pluck('nickname')->toArray());
            $item->server_title = implode('-', $item->users->where('role_id', Role::TYPE_SERVER)->pluck('nickname')->toArray());
            $item->seller_title = implode('-', $item->users->where('role_id', Role::TYPE_SELLER)->pluck('nickname')->toArray());
            $item->optimizerTitle = implode('-', $item->users->where('role_id', Role::TYPE_OPTIMIZER)->pluck('nickname')->toArray());
            $item->ae_title = implode('-', $item->users->where('role_id', Role::TYPE_AE)->pluck('nickname')->toArray());
            $item->businessTitle = $item->business->title ?? "";

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

            $item->renewal_at = $item->expired_at ? date('Y-m-d', strtotime('-3 month', strtotime($item->expired_at))) : null;


            $item->reach_at = substr($item->reach_at, 0, 10);
            $item->online_at = substr($item->online_at, 0, 10);

            $item->is_renewal = empty($item->renewal_at) ? '否' : '是';


        });


        return response()->json([
            'rows' => $items,
            'total' => $sites->total()
        ]);
    }

    //流量异常项目
    public function abnormal(Request $request)
    {
        if (!$request->ajax()) {
            return view('admin/analyze/abnormal', [
                'services' => Role::getUsers(Role::TYPE_SERVER), //服务人员
                'optimizers' => Role::getUsers(Role::TYPE_OPTIMIZER),
                'managers' => Role::getUsers(Role::TYPE_MANAGER),
                'managerHelpers' => Role::getUsers(Role::TYPE_MANAGE_HELPER),
                'sellerUsers' => Role::getUsers(Role::TYPE_SELLER),
            ]);
        }

        $filterUserIds = [];
        $optimizerId = $request->input('optimizerId');
        $optimizerId && $filterUserIds[] = $optimizerId;
        $managerId = $request->input('managerId');
        $managerId && $filterUserIds[] = $managerId;
        $serverId = $request->input('serverId');
        $serverId && $filterUserIds[] = $serverId;
        $sellerId = $request->input('sellerId');
        $sellerId && $filterUserIds[] = $sellerId;
        $manageHelperId = $request->input('manageHelperId');
        $manageHelperId && $filterUserIds[] = $manageHelperId;


        $keyword = $request->input('keyword');
        $builder = Site::query()->with(['users'])->whereIn('status', [2, 3, 5])->where(function (Builder $q) use ($keyword) {
            if ($keyword) {
                $q->where('domain', 'like', '%' . $keyword . '%')
                    ->orWhere('cn_title', 'like', '%' . $keyword . '%');
            }
        });


        if ($filterUserIds) {
            $builder->whereExists(function (\Illuminate\Database\Query\Builder $b) use ($filterUserIds) {
//                $condition='select *,SUM(CASE WHEN user_id in (3,5) then  1 ELSE  0 end) as total  from user_has_sites where site_id=4 HAVING total=2';
                $raw = sprintf('SUM(CASE WHEN user_id in (%s) then  1 ELSE  0 end) as total', implode(',', $filterUserIds));
                $b->select(DB::raw($raw))->from('user_has_sites')
                    ->whereRaw(sprintf('user_has_sites.site_id=sites.id HAVING total=%s', count($filterUserIds)));
            });
        }
        $sites = $builder->orderByDesc('id')->paginate($request->input('pageSize') ?? TABLE_PAGE_SIZE);

        $items = $sites->items();

        $oldIds = array_filter(array_column($items, 'old_id'));


        $rankConnection = DB::connection('rank');


        $lastOneClicks = $rankConnection->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();

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


        $clicksDropResults = [];

        foreach ($lastTwoClicks as $inx => $val) {

            $lastOne = $lastOneClicks[$inx] ?? null;
            if (empty($lastOne)) {
                continue;
            }
            $lessVal = $val->clicks - $lastOne->clicks;
            if ($lessVal >= 50) {
                $clicksDropResults[$inx] = $lessVal;
            }
        }


        $result = [];

        foreach ($items as $item) {

            $item->managers_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGER)->pluck('nickname')->toArray());
            $item->manage_helper_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGE_HELPER)->pluck('nickname')->toArray());
            $item->server_title = implode('-', $item->users->where('role_id', Role::TYPE_SERVER)->pluck('nickname')->toArray());
            $item->seller_title = implode('-', $item->users->where('role_id', Role::TYPE_SELLER)->pluck('nickname')->toArray());
            $item->optimizerTitle = implode('-', $item->users->where('role_id', Role::TYPE_OPTIMIZER)->pluck('nickname')->toArray());
            $item->ae_title = implode('-', $item->users->where('role_id', Role::TYPE_AE)->pluck('nickname')->toArray());


            $item->renewal_at = $item->expired_at ? date('Y-m-d', strtotime('-3 month', strtotime($item->expired_at))) : null;

            $item->reach_at = substr($item->reach_at, 0, 10);
            $item->online_at = substr($item->online_at, 0, 10);

            $item->is_renewal = empty($item->renewal_at) ? '否' : '是';
            $item->statusTitle = Site::STATUS_MAP[$item->status] ?? '';
            $item->clicksDropResults = $clicksDropResults[$item->old_id] ?? '';


            if (empty($item->clicksDropResults)) {
                continue;
            }

            $result[] = $item;
        }

        return response()->json([
            'rows' => $result,
            'total' => $sites->total()
        ]);
    }

    //关键词骤降项目
    public function abnormal2(Request $request)
    {
        if (!$request->ajax()) {


            return view('admin/analyze/abnormal2', [
                'services' => Role::getUsers(Role::TYPE_SERVER), //服务人员
                'optimizers' => Role::getUsers(Role::TYPE_OPTIMIZER),
                'managers' => Role::getUsers(Role::TYPE_MANAGER),
                'managerHelpers' => Role::getUsers(Role::TYPE_MANAGE_HELPER),
                'sellerUsers' => Role::getUsers(Role::TYPE_SELLER),
            ]);
        }

        $filterUserIds = [];
        $optimizerId = $request->input('optimizerId');
        $optimizerId && $filterUserIds[] = $optimizerId;
        $managerId = $request->input('managerId');
        $managerId && $filterUserIds[] = $managerId;
        $serverId = $request->input('serverId');
        $serverId && $filterUserIds[] = $serverId;
        $sellerId = $request->input('sellerId');
        $sellerId && $filterUserIds[] = $sellerId;
        $manageHelperId = $request->input('manageHelperId');
        $manageHelperId && $filterUserIds[] = $manageHelperId;


        $keyword = $request->input('keyword');
        $builder = Site::query()->with(['users'])->whereIn('status', [2, 3, 5])->where(function (Builder $q) use ($keyword) {
            if ($keyword) {
                $q->where('domain', 'like', '%' . $keyword . '%')
                    ->orWhere('cn_title', 'like', '%' . $keyword . '%');
            }
        });


        if ($filterUserIds) {
            $builder->whereExists(function (\Illuminate\Database\Query\Builder $b) use ($filterUserIds) {
//                $condition='select *,SUM(CASE WHEN user_id in (3,5) then  1 ELSE  0 end) as total  from user_has_sites where site_id=4 HAVING total=2';
                $raw = sprintf('SUM(CASE WHEN user_id in (%s) then  1 ELSE  0 end) as total', implode(',', $filterUserIds));
                $b->select(DB::raw($raw))->from('user_has_sites')
                    ->whereRaw(sprintf('user_has_sites.site_id=sites.id HAVING total=%s', count($filterUserIds)));
            });
        }
        $sites = $builder->orderByDesc('id')->paginate($request->input('pageSize') ?? TABLE_PAGE_SIZE);

        $items = $sites->items();


        $rankConnection = DB::connection('rank');


        //关键词排名急剧下降
        $nowMonthKeywords = $rankConnection->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 = $rankConnection->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();

        $keywordDropResults = [];

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


        $result = [];

        foreach ($items as $item) {

            $item->managers_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGER)->pluck('nickname')->toArray());
            $item->manage_helper_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGE_HELPER)->pluck('nickname')->toArray());
            $item->server_title = implode('-', $item->users->where('role_id', Role::TYPE_SERVER)->pluck('nickname')->toArray());
            $item->seller_title = implode('-', $item->users->where('role_id', Role::TYPE_SELLER)->pluck('nickname')->toArray());
            $item->optimizerTitle = implode('-', $item->users->where('role_id', Role::TYPE_OPTIMIZER)->pluck('nickname')->toArray());
            $item->ae_title = implode('-', $item->users->where('role_id', Role::TYPE_AE)->pluck('nickname')->toArray());


            $item->renewal_at = $item->expired_at ? date('Y-m-d', strtotime('-3 month', strtotime($item->expired_at))) : null;

            $item->reach_at = substr($item->reach_at, 0, 10);
            $item->online_at = substr($item->online_at, 0, 10);

            $item->is_renewal = empty($item->renewal_at) ? '否' : '是';
            $item->statusTitle = Site::STATUS_MAP[$item->status] ?? '';

            $item->keywordDropResults = $keywordDropResults[$item->old_id] ?? '';


            if (empty($item->keywordDropResults)) {
                continue;
            }
            $result[] = $item;
        }

        return response()->json([
            'rows' => $result,
            'total' => $sites->total()
        ]);
    }

    //关键词异常项目
    public function abnormal3(Request $request)
    {
        if (!$request->ajax()) {


            return view('admin/analyze/abnormal3', [
                'services' => Role::getUsers(Role::TYPE_SERVER), //服务人员
                'optimizers' => Role::getUsers(Role::TYPE_OPTIMIZER),
                'managers' => Role::getUsers(Role::TYPE_MANAGER),
                'managerHelpers' => Role::getUsers(Role::TYPE_MANAGE_HELPER),
                'sellerUsers' => Role::getUsers(Role::TYPE_SELLER),
            ]);
        }

        $filterUserIds = [];
        $optimizerId = $request->input('optimizerId');
        $optimizerId && $filterUserIds[] = $optimizerId;
        $managerId = $request->input('managerId');
        $managerId && $filterUserIds[] = $managerId;
        $serverId = $request->input('serverId');
        $serverId && $filterUserIds[] = $serverId;
        $sellerId = $request->input('sellerId');
        $sellerId && $filterUserIds[] = $sellerId;
        $manageHelperId = $request->input('manageHelperId');
        $manageHelperId && $filterUserIds[] = $manageHelperId;


        $keyword = $request->input('keyword');
        $builder = Site::query()->with(['users'])->whereIn('status', [2, 3, 5])->where(function (Builder $q) use ($keyword) {
            if ($keyword) {
                $q->where('domain', 'like', '%' . $keyword . '%')
                    ->orWhere('cn_title', 'like', '%' . $keyword . '%');
            }
        });


        if ($filterUserIds) {
            $builder->whereExists(function (\Illuminate\Database\Query\Builder $b) use ($filterUserIds) {
//                $condition='select *,SUM(CASE WHEN user_id in (3,5) then  1 ELSE  0 end) as total  from user_has_sites where site_id=4 HAVING total=2';
                $raw = sprintf('SUM(CASE WHEN user_id in (%s) then  1 ELSE  0 end) as total', implode(',', $filterUserIds));
                $b->select(DB::raw($raw))->from('user_has_sites')
                    ->whereRaw(sprintf('user_has_sites.site_id=sites.id HAVING total=%s', count($filterUserIds)));
            });
        }
        $sites = $builder->orderByDesc('id')->paginate($request->input('pageSize') ?? TABLE_PAGE_SIZE);

        $items = $sites->items();


        $oldIds = array_filter(array_column($items, 'old_id'));
        [$top10ListMap, $listReportMap] = $this->getInquiryAndFlowList($oldIds);

        $result = [];

        foreach ($items as $item) {

            $item->managers_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGER)->pluck('nickname')->toArray());
            $item->manage_helper_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGE_HELPER)->pluck('nickname')->toArray());
            $item->server_title = implode('-', $item->users->where('role_id', Role::TYPE_SERVER)->pluck('nickname')->toArray());
            $item->seller_title = implode('-', $item->users->where('role_id', Role::TYPE_SELLER)->pluck('nickname')->toArray());
            $item->optimizerTitle = implode('-', $item->users->where('role_id', Role::TYPE_OPTIMIZER)->pluck('nickname')->toArray());
            $item->ae_title = implode('-', $item->users->where('role_id', Role::TYPE_AE)->pluck('nickname')->toArray());


            $item->renewal_at = $item->expired_at ? date('Y-m-d', strtotime('-3 month', strtotime($item->expired_at))) : null;

            $item->reach_at = substr($item->reach_at, 0, 10);
            $item->online_at = substr($item->online_at, 0, 10);

            $item->is_renewal = empty($item->renewal_at) ? '否' : '是';
            $item->statusTitle = Site::STATUS_MAP[$item->status] ?? '';


            if ($item->status == 3 && $item->reach_at && !empty($top10ListMap[$item->old_id])) {
                if ($top10ListMap[$item->old_id]->top10 < $item->keyword_goal) {
                    $item->top10NoReach = $item->keyword_goal - $top10ListMap[$item->old_id]->top10;
                }
            }
            if (empty($item->top10NoReach)) {
                continue;
            }
            $result[] = $item;
        }

        return response()->json([
            'rows' => $result,
            'total' => $sites->total()
        ]);
    }

    //总监控
    public function monitor(Request $request)
    {
        if (!$request->ajax()) {

            return view('admin/analyze/monitor', [
                'services' => Role::getUsers(Role::TYPE_SERVER), //服务人员
                'optimizers' => Role::getUsers(Role::TYPE_OPTIMIZER),
                'managers' => Role::getUsers(Role::TYPE_MANAGER),
                'sitesStatus' => Site::STATUS_MAP
            ]);
        }

        $filterUserIds = [];
        $optimizerId = $request->input('optimizerId');
        $optimizerId && $filterUserIds[] = $optimizerId;
        $managerId = $request->input('managerId');
        $managerId && $filterUserIds[] = $managerId;
        $serverId = $request->input('serverId');
        $serverId && $filterUserIds[] = $serverId;

        DB::connection()->enableQueryLog();
        $keyword = $request->input('keyword');
        $builder = Site::query()->with(['users' => function (\Illuminate\Database\Eloquent\Relations\BelongsToMany $q) {
            $q->select(['id', 'username', 'nickname', 'role_id']);
        }, 'linkDetails' => function (\Illuminate\Database\Eloquent\Relations\HasMany $q) {
//            $q->selectRaw('COUNT(DISTINCT(link_id)) as linkPactCount')->where(['enable' => 1, 'link_tasks_detail.status' => 5]);
            $q->select(['id', 'redundant_site_id', 'link_id'])->where(['enable' => 1, 'link_tasks_detail.status' => 5]);
        }])->withCount([
            'articles as noSyncArticle' => function (Builder $b) {
                $b->whereNull('sync_at');
            }, 'articles as syncArticle' => function (Builder $b) {
                $b->whereNotNull('sync_at')->whereNull('publish_at');
            }, 'articles as publishArticle' => function (Builder $b) {
                $b->whereNotNull('publish_at');
            },

            'linkUrls as linkUrls' => function (Builder $query) {
                $query->where('link_tasks_detail.status', 5)
//                    ->where('link_tasks_detail.enable', 1)
                    ->where('link_tasks_url.status', 5);

            }])->where(function (Builder $q) use ($keyword) {
            if ($keyword) {
                $q->where('domain', 'like', '%' . $keyword . '%')
                    ->orWhere('cn_title', 'like', '%' . $keyword . '%');
            }
        });

        $siteStatus = $request->input('siteStatus');

        if ($siteStatus) {
            $builder->where('status', $siteStatus);
        }


        if ($filterUserIds) {
            $builder->whereExists(function (\Illuminate\Database\Query\Builder $b) use ($filterUserIds) {
//                $condition='select *,SUM(CASE WHEN user_id in (3,5) then  1 ELSE  0 end) as total  from user_has_sites where site_id=4 HAVING total=2';
                $raw = sprintf('SUM(CASE WHEN user_id in (%s) then  1 ELSE  0 end) as total', implode(',', $filterUserIds));
                $b->select(DB::raw($raw))->from('user_has_sites')
                    ->whereRaw(sprintf('user_has_sites.site_id=sites.id HAVING total=%s', count($filterUserIds)));
            });
        }

        $sites = $builder->orderByDesc('id')->paginate($request->input('pageSize') ?? TABLE_PAGE_SIZE);


        $items = $sites->items();

        $articleFinishStatus = $request->input('articleFinishStatus');
        $linkFinishStatus = $request->input('linkFinishStatus');
        $result = collect([]);
        foreach ($items as $item) {

            //发布完成：软文指标小于软文发布量
            if ($articleFinishStatus == 1) {
                if ($item->article_goal > $item->publishArticle || !$item->article_goal) {
                    continue;
                }
            }
            //查找完成：软文指标小于待更新软文数
            if ($articleFinishStatus == 2) {
                if ($item->article_goal > $item->noSyncArticle || !$item->article_goal) {
                    continue;
                }
            }
            //未完成：软文指标大于软文发布量
            if ($articleFinishStatus == 3) {
                if ($item->article_goal <= $item->publishArticle) {
                    continue;
                }
            }

            $item->linkPactCount = $item->linkDetails->groupBy('link_id')->count();

            //未完成：外链指标大于外联完成量
            if ($linkFinishStatus == 1) {
                if ($item->link_goal <= $item->linkPactCount || !$item->link_goal) {
                    continue;
                }
            }
            //已完成：外链指标小于外联完成量
            if ($linkFinishStatus == 2) {
                if ($item->link_goal > $item->linkPactCount || !$item->link_goal) {
                    continue;
                }
            }

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

            $item->expect_renewal_at = substr(($item->expired_at ?
                date('Y-m-d', strtotime('-3 month', strtotime($item->expired_at)))
                : null), 0, 10);
            $item->order_at = substr($item->order_at, 0, 10);
            $item->statusTitle = Site::STATUS_MAP[$item->status] ?? '';
            $result->prepend($item);
        }
        return response()->json([
            'rows' => array_values($result->toArray()),
            'total' => count($items)
        ]);
    }


    //软文监控
    public function monitorArticle(Request $request)
    {
        if (!$request->ajax()) {
            $list = Site::query()
                ->whereIn('status', [2, 3, 5])
                ->where('article_pause', 0)//软文没有暂停的
                ->where('article_goal', '!=', 0)//软文指标不等于0
                ->select('id', 'domain', 'articles_updates_per_month', 'article_renewal_quantity')
                ->withCount([
                    'articles as publishThisMonth' => function (Builder $b) {
                        $b->whereBetween('publish_at', [date('Y-m-01 00:00:00'), date('Y-m-t 23:59:59')]);
                    }
                ])->get();

            $articlesUpdatesPerMonth = 0;
            $publishThisMonth = 0;
            foreach ($list as $item) {

                $articlesUpdatesPerMonth += $item->articles_updates_per_month ?? 0;
                $publishThisMonth += $item->publishThisMonth ?? 0;

                $notPublish = Article::query()->where('site_id', $item->id)
                        ->whereNull('publish_at')
                        ->where('be_applicable', 1)
                        ->count() ?? 0;
                //中文文章 数据公式：【未发布数（即无发布时间的文章数量）-（每月待更新软文数-当月发布量）】/每月待更新软文数
                $a = ($notPublish - ($item->articles_updates_per_month - $item->publishThisMonth));
                $b = 0;
                if (!empty($item->articles_updates_per_month)) {
                    $b = round($a / $item->articles_updates_per_month, 2);
                }
                $item->chineseArticles = $b;
            }
            $sites = 0;
            foreach ($list as $item) {
                if ($item->chineseArticles <= 2) {
                    $sites++;
                }
            }

            return view('admin/analyze/monitor_article', [
                'services' => Role::getUsers(Role::TYPE_SERVER), //服务人员
                'optimizers' => Role::getUsers(Role::TYPE_OPTIMIZER),
                'managers' => Role::getUsers(Role::TYPE_MANAGER),
                'optimizerEditing' => Role::getUsers(Role::TYPE_OPTIMIZATION_EDITING),
                'articleEditing' => Role::getUsers(Role::TYPE_ARTICLE),
                'sitesStatus' => Site::STATUS_MAP,
                'notUpdated' => $articlesUpdatesPerMonth - $publishThisMonth,
                'sites' => $sites,
            ]);
        }

        $filterUserIds = [];

        $optimizerId = $request->input('optimizerId');
        $optimizerId && $filterUserIds[] = $optimizerId;

        $managerId = $request->input('managerId');
        $managerId && $filterUserIds[] = $managerId;

        $serverId = $request->input('serverId');
        $serverId && $filterUserIds[] = $serverId;

        $optimizerEditingId = $request->input('optimizerEditingId');
        $optimizerEditingId && $filterUserIds[] = $optimizerEditingId;

        $articleEditingId = $request->input('articleEditingId');
        $articleEditingId && $filterUserIds[] = $articleEditingId;

        $keyword = $request->input('keyword');
        $builder = Site::query()
            ->whereIn('status', [2, 3, 5])
            ->where('article_pause', 0)//软文没有暂停的
            ->where('article_goal', '!=', 0)//软文指标不等于0
            ->select('id', 'domain', 'cn_title', 'status', 'expired_at', 'article_goal', 'articles_updates_per_month', 'article_pause', 'article_renewal_quantity')
            ->with(['users' => function (\Illuminate\Database\Eloquent\Relations\BelongsToMany $q) {
                $q->select(['id', 'username', 'nickname', 'role_id']);
            }])->withCount([
                'articles as publishThisMonth' => function (Builder $b) {
                    $b->whereBetween('publish_at', [date('Y-m-01 00:00:00'), date('Y-m-t 23:59:59')]);
                },
                'articles as fit' => function (Builder $b) {
                    //适用
                    $b->where('be_applicable', 1);
                },
                'articles as undetermined' => function (Builder $b) {
                    //待定
                    $b->where('be_applicable', 0);
                },
                'articles as pendingUpgrade' => function (Builder $b) {
                    //待更新
                    $b->where('be_applicable', 1)
                        ->whereNull('publish_at');
                }
            ])->where(function (Builder $q) use ($keyword) {
                if ($keyword) {
                    $q->where('domain', 'like', '%' . $keyword . '%')
                        ->orWhere('cn_title', 'like', '%' . $keyword . '%');
                }
            });
        $siteStatus = $request->input('siteStatus');
        if ($siteStatus) {
            $builder->where('status', $siteStatus);
        }

        if ($filterUserIds) {
            $builder->whereExists(function (\Illuminate\Database\Query\Builder $b) use ($filterUserIds) {
                $raw = sprintf('SUM(CASE WHEN user_id in (%s) then  1 ELSE  0 end) as total', implode(',', $filterUserIds));
                $b->select(DB::raw($raw))->from('user_has_sites')
                    ->whereRaw(sprintf('user_has_sites.site_id=sites.id HAVING total=%s', count($filterUserIds)));
            });
        }

        $sites = $builder->orderByDesc('id')->get();
        foreach ($sites as $item) {
            $updateMonth = Article::query()->where('site_id', $item->id)
                ->whereNotNull('publish_at')
                ->orderBy('publish_at', 'desc')
                ->value('publish_at');

            if (!empty($updateMonth)) {
                $updateMonth = date('Y-m-d', strtotime($updateMonth));
                $updateMonthTime = strtotime($updateMonth);

            } else {
                $updateMonth = '';
                $updateMonthTime = 0;
            }

            $item->updateMonth = $updateMonth;
            $item->updateMonthTime = $updateMonthTime;

            if ($item->spec_article == 1) {
                $item->spec_article = '是';
            }

            /*$item->managers_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGER)->pluck('nickname')->toArray());*/
            $item->server_title = implode('-', $item->users->where('role_id', Role::TYPE_SERVER)->pluck('nickname')->toArray());
            $item->optimizerTitle = implode('-', $item->users->where('role_id', Role::TYPE_OPTIMIZER)->pluck('nickname')->toArray());
            $item->articleEditing = implode('-', $item->users->where('role_id', Role::TYPE_ARTICLE)->pluck('nickname')->toArray());
            $item->optimizerEditing = implode('-', $item->users->where('role_id', Role::TYPE_OPTIMIZATION_EDITING)->pluck('nickname')->toArray());


            $item->order_at = substr($item->order_at, 0, 10);
            $item->statusTitle = Site::STATUS_MAP[$item->status] ?? '';

            $notPublish = Article::query()->where('site_id', $item->id)
                    ->whereNull('publish_at')
                    ->where('be_applicable', 1)
                    ->count() ?? 0;
            //中文文章 数据公式：【未发布数（即无发布时间的文章数量）-（每月待更新软文数-当月发布量）】/每月待更新软文数
            $a = ($notPublish - ($item->articles_updates_per_month - $item->publishThisMonth));
            $b = 0;
            if (!empty($item->articles_updates_per_month)) {
                $b = round($a / $item->articles_updates_per_month, 2);
            }
            $item->chineseArticles = $b;
        }

        //中文完成情况 中文文章下数字≥2
        $chineseCompletion = $request->input('chinese_completion');
        if ($chineseCompletion == 1) {
            foreach ($sites as $key => $item) {
                if ($item->chineseArticles < 2) {
                    unset($sites[$key]);
                }
            }
        }

        //不足2月库存: 2＞中文文章下数字≥1
        if ($chineseCompletion == 2) {
            foreach ($sites as $key => $item) {
                if ($item->chineseArticles < 1 || $item->chineseArticles >= 2) {
                    unset($sites[$key]);
                }
            }
        }

        //不足1月库存：1＞中文文章下数字≥0
        if ($chineseCompletion == 3) {
            foreach ($sites as $key => $item) {
                if ($item->chineseArticles < 0 || $item->chineseArticles >= 1) {
                    unset($sites[$key]);
                }
            }
        }

        //无库存：适用文章为0
        if ($chineseCompletion == 4) {
            foreach ($sites as $key => $item) {
                if ($item->fit != 0) {
                    unset($sites[$key]);
                }
            }
        }

        //中文未反馈：待定 ＞0，本月更新＜每月更新数，且待更新=0
        $array = [];
        if ($chineseCompletion == 5) {
            foreach ($sites as $key => $item) {
                if ($item->undetermined > 0 && $item->publishThisMonth < $item->articles_updates_per_month && $item->pendingUpgrade == 0) {
                    $array[] = $item;
                }
            }
            $sites = $array;
        }

        //当月完成情况、完成
        $isComplete = $request->input('is_complete');
        if ($isComplete == 1) {
            foreach ($sites as $key => $item) {
                if ($item->publishThisMonth < $item->articles_updates_per_month) {
                    unset($sites[$key]);
                }
            }
        }

        //当月完成情况、未完成
        if ($isComplete == 2) {
            foreach ($sites as $key => $item) {
                if ($item->publishThisMonth >= $item->articles_updates_per_month) {
                    unset($sites[$key]);
                }
            }
        }

        //当月完成情况、当月发布量0
        if ($isComplete == 3) {
            foreach ($sites as $key => $item) {
                if ($item->publishThisMonth != 0) {
                    unset($sites[$key]);
                }
            }
        }

        $sortName = $request->input('sortName') ?? 'id';
        $sortOrder = $request->input('sortOrder');

        if ($sortName == 'updateMonth') {
            $sortName = 'updateMonthTime';
        }
        if ($sortOrder == 'asc') {
            $res = collect($sites)->sortBy($sortName);
        } else {
            $res = collect($sites)->sortByDesc($sortName);
        }

        $result = $this->paginateCollection($res, $request->input('pageSize'));

        return response()->json([
            'rows' => $result->items(),
            'total' => $result->total(),
        ]);
    }

    //外链监控
    public function monitorLink(Request $request)
    {
        if (!$request->ajax()) {

            return view('admin/analyze/monitor_link', [
                'services' => Role::getUsers(Role::TYPE_SERVER), //服务人员
                'optimizers' => Role::getUsers(Role::TYPE_OPTIMIZER),
                'optimizerEditing' => Role::getUsers(Role::TYPE_OPTIMIZATION_EDITING),
                'chongqingLink' => Role::getUsers(Role::TYPE_LINK_PART_CHONGQING),
                'sitesStatus' => Site::STATUS_MAP
            ]);
        }
        $builder = LinkStatistical::query();
        $optimizerId = $request->input('optimizerId');
        if (!empty($optimizerId)) {
            $builder->whereRaw('FIND_IN_SET(?,optimizer_people_id)', [$optimizerId]);
        }

        $optimizerEditingId = $request->input('optimizerEditingId');
        if (!empty($optimizerEditingId)) {
            $builder->whereRaw('FIND_IN_SET(?,optimizer_editing_people_id)', [$optimizerEditingId]);
        }

        $serverId = $request->input('serverId');
        if (!empty($serverId)) {
            $builder->whereRaw('FIND_IN_SET(?,server_people_id)', [$serverId]);
        }

        $chongqingLinkId = $request->input('chongqingLinkId');
        if (!empty($chongqingLinkId)) {
            $builder->whereRaw('FIND_IN_SET(?,chongqing_link_people_id)', [$chongqingLinkId]);
        }

        $keyword = $request->input('keyword');
        if (!empty($keyword)) {
            $builder->where('cn_title', 'like', '%' . $keyword . '%');
        }
        $siteStatus = $request->input('siteStatus');
        if (!empty($siteStatus)) {
            $builder->where('site_status', $siteStatus);
        }

        $completionLastMonth = $request->input('completionLastMonth');
        if ($completionLastMonth == 1) {
            $builder->where('publish_last_month', '=', 0);
        }
        if ($completionLastMonth == 2) {
            $builder->whereBetween('publish_last_month', [1, 49]);
        }
        if ($completionLastMonth == 3) {
            $builder->whereBetween('publish_last_month', [50, 69]);
        }
        if ($completionLastMonth == 4) {
            $builder->where('publish_last_month', '>', 70);
        }
        $completionThisMonth = $request->input('completionThisMonth');
        if ($completionThisMonth == 1) {
            $builder->where('publish_this_month', '=', 0);
        }
        if ($completionThisMonth == 2) {
            $builder->whereBetween('publish_this_month', [1, 49]);
        }
        if ($completionThisMonth == 3) {
            $builder->whereBetween('publish_this_month', [50, 69]);
        }
        if ($completionThisMonth == 4) {
            $builder->where('publish_this_month', '>', 70);
        }
        $sortName = $request->input('sortName') ?? 'id';
        $sortOrder = $request->input('sortOrder') ?? 'desc';
        $sites = $builder->orderBy($sortName, $sortOrder)->paginate($request->input('pageSize'));

        $userList = User::query()->where('status', 1)->pluck('nickname', 'id');

        foreach ($sites as $key => $item) {

            $optimizerEditingList = '';
            if (!empty($item->optimizer_editing_people_id)) {
                $optimizerEditing = explode(',', $item->optimizer_editing_people_id);
                foreach ($optimizerEditing as $value) {
                    $optimizerEditingList .= $userList[$value] ?? '';
                }
            }
            $item->optimizerEditing = $optimizerEditingList;

            $chongqingLinkList = '';
            if (!empty($item->chongqing_link_people_id)) {
                $chongqingLinks = explode(',', $item->chongqing_link_people_id);
                foreach ($chongqingLinks as $value) {
                    $chongqingLinkList .= $userList[$value] ?? '';
                }
            }

            $item->chongqingLink = $chongqingLinkList;

            $serverList = '';
            if (!empty($item->server_people_id)) {
                $servers = explode(',', $item->server_people_id);
                foreach ($servers as $value) {
                    $serverList .= $userList[$value] ?? '';
                }
            }

            $item->server = $serverList;

            $optimizerList = '';
            if (!empty($item->optimizer_people_id)) {
                $optimizers = explode(',', $item->optimizer_people_id);
                foreach ($optimizers as $value) {
                    $optimizerList .= $userList[$value] ?? '';
                }
            }
            $item->optimizer = $optimizerList;
            $item->site_status = Site::STATUS_MAP[$item->site_status] ?? '';
        }

        return response()->json([
            'rows' => $sites->items(),
            'total' => $sites->total(),
        ]);
    }

    //合同档案
    public function archive(Request $request)
    {
        if (!$request->ajax()) {
            return view('admin/analyze/archive');
        }

        $builder = $this->archiveBuilder();
        $sites = $builder->orderByDesc('id')->paginate($request->input('pageSize') ?? TABLE_PAGE_SIZE);
        $items = $sites->items();

        foreach ($items as $item) {

            $item->areaText = implode(',', $item->area_text);
            $item->status = Site::STATUS_MAP[$item->status];
            $item->contract_total_fee = $item->sitePayment->contract_total_fee ?? '';
            $item->head = $item->sitePayment->head ?? '';
            $item->done = $item->sitePayment->done ?? '';
            $item->reach = $item->sitePayment->reach ?? '';
            $item->service_final_payment = $item->sitePayment->service_final_payment ?? '';

            $item->managers_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGER)->pluck('nickname')->toArray());
            $item->manage_helper_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGE_HELPER)->pluck('nickname')->toArray());
            $item->server_title = implode('-', $item->users->where('role_id', Role::TYPE_SERVER)->pluck('nickname')->toArray());
            $item->seller_title = implode('-', $item->users->where('role_id', Role::TYPE_SELLER)->pluck('nickname')->toArray());
            $item->optimizerTitle = implode('-', $item->users->where('role_id', Role::TYPE_OPTIMIZER)->pluck('nickname')->toArray());
            $item->optimizerAe = implode('-', $item->users->where('role_id', Role::TYPE_OPTIMIZATION_EDITING)->pluck('nickname')->toArray());

            $item->ae_title = implode('-', $item->users->where('role_id', Role::TYPE_AE)->pluck('nickname')->toArray());

            $item->bidTitle = implode('-', $item->users->where('role_id', Role::TYPE_BID)->pluck('nickname')->toArray());
            $item->articleTitle = implode('-', $item->users->where('role_id', Role::TYPE_PROPAGANDA)->pluck('nickname')->toArray());
            $item->designerTitle = implode('-', $item->users->where('role_id', Role::TYPE_DESIGNER)->pluck('nickname')->toArray());
            $item->webTitle = implode('-', $item->users->where('role_id', Role::TYPE_WEB)->pluck('nickname')->toArray());

        };


        return response()->json([
            'rows' => $items,
            'total' => $sites->total()
        ]);
    }

    protected function archiveBuilder()
    {
        $request = \request();

        $keyword = $request->input('keyword');
        $address = $request->input('address');
        $status = $request->input('status');

        $builder = Site::query()->with(['users', 'sitePayment'])->where(function (Builder $q) use ($keyword, $status, $address) {
            if ($keyword) {
                $q->where('domain', 'like', '%' . $keyword . '%')
                    ->orWhere('cn_title', 'like', '%' . $keyword . '%');
            }
            if ($status) {
                $q->where('status', $status);
            }

            if ($address) {
                $address = json_encode($address);
                $address = (trim($address, '"'));
                $address = str_replace('\\', '%' . '\\', $address);
                $q->where('area_text', 'like', '%' . $address . '%');
            }
        });

        return $builder;
    }


    //合同档案导出
    public function archiveExport(Request $request)
    {

        $builder = $this->archiveBuilder();
        $items = $builder->orderByDesc('id')->get();

        $list = [];
        foreach ($items as $item) {
            $data = [
                'areaText' => implode(',', $item->area_text),
                'cn_title' => $item->cn_title,
                'status' => Site::STATUS_MAP[$item->status],
                'domain' => $item->domain,
                'keyword_goal' => $item->keyword_goal,
                'article_goal' => $item->article_goal,
                'link_goal' => $item->link_goal,
                'sign_at' => $item->sign_at,
                'contract_total_fee' => $item->sitePayment->contract_total_fee ?? '',
                'head' => $item->sitePayment->head ?? '',
                'done' => $item->sitePayment->done ?? '',
                'reach' => $item->sitePayment->reach ?? '',
                'service_final_payment' => $item->sitePayment->service_final_payment ?? '',
                'renewal_times' => $item->renewal_times ?? '',
                'renewal_amount' => $item->renewal_amount ?? '',
                'remark' => $item->remark ?? '',
                'seller_title' => implode('-', $item->users->where('role_id', Role::TYPE_SELLER)->pluck('nickname')->toArray()),
                'server_title' => implode('-', $item->users->where('role_id', Role::TYPE_SERVER)->pluck('nickname')->toArray()),
                'managers_title' => implode('-', $item->users->where('role_id', Role::TYPE_MANAGER)->pluck('nickname')->toArray()),
                'manage_helper_title' => implode('-', $item->users->where('role_id', Role::TYPE_MANAGE_HELPER)->pluck('nickname')->toArray()),
                'optimizerTitle' => implode('-', $item->users->where('role_id', Role::TYPE_OPTIMIZER)->pluck('nickname')->toArray()),
                'optimizerAe' => implode('-', $item->users->where('role_id', Role::TYPE_OPTIMIZATION_EDITING)->pluck('nickname')->toArray()),

                'ae_title' => implode('-', $item->users->where('role_id', Role::TYPE_AE)->pluck('nickname')->toArray()),
                'bidTitle' => implode('-', $item->users->where('role_id', Role::TYPE_BID)->pluck('nickname')->toArray()),
                'articleTitle' => implode('-', $item->users->where('role_id', Role::TYPE_PROPAGANDA)->pluck('nickname')->toArray()),
                'designerTitle' => implode('-', $item->users->where('role_id', Role::TYPE_DESIGNER)->pluck('nickname')->toArray()),
                'webTitle' => implode('-', $item->users->where('role_id', Role::TYPE_WEB)->pluck('nickname')->toArray()),
            ];
            $list[] = $data;
        };

        array_unshift($list, ['地区', '公司', '项目状态', '网站', '关键词指标', '文章指标', '外链指标', '合同日期', '合同金额', '首付款', '建站金额', '达标金额', '服务期尾款', '续费次数', '续费金额', '备注', '项目销售', '客服经理', '项目经理1', '项目经理', '优化师', '优化采编', '项目采编', '竞价', '软文', '设计', '前端']);
        return (new BasicExport($list))->download(sprintf('合同档案%s.xls', date('YmdHis')));
    }

    //已终止
    public function abort(Request $request)
    {
        if (!$request->ajax()) {
            return view('admin/analyze/abort', [
                'services' => Role::getUsers(Role::TYPE_SERVER), //服务人员
                'sellerUsers' => Role::getUsers(Role::TYPE_SELLER),
            ]);
        }

        $builder = $this->abortBuilder();
        $sites = $builder->orderByDesc('expired_at')->paginate($request->input('pageSize') ?? TABLE_PAGE_SIZE);
        $items = $sites->items();

        $oldIds = array_filter(array_column($items, 'old_id'));
        [$top10ListMap, $listReportMap] = $this->getInquiryAndFlowList($oldIds);

        foreach ($items as $item) {
            $item->status = Site::STATUS_MAP[$item->status];
            $item->top10 = $item->old_id ? ($top10ListMap[$item->old_id]->top10 ?? '-') : '未关联'; //实际达标关键词
            $item->traffic = $item->old_id ? ($listReportMap[$item->old_id]->traffic ?? '-') : '未关联';
            $item->inquire = $item->old_id ? ($listReportMap[$item->old_id]->inquire ?? '-') : '未关联';

            $item->order_at = substr($item->order_at, 0, 10);
            $item->online_at = substr($item->online_at, 0, 10);
            $item->expired_at = substr($item->expired_at, 0, 10);
            $item->refundReasonAnalyze = self::REFUND_REASON_LIST[$item->sitePayment->refund_reason_analyze];
        };

        return response()->json([
            'rows' => $items,
            'total' => $sites->total()
        ]);
    }


    protected function abortBuilder()
    {
        $request = \request();

        $monthRange = $request->input('monthRange');
        $keyword = $request->input('keyword');
        $status = $request->input('status');

        $build = Site::query();
        $refundReasonAnalyze = $request->input('refund_reason_analyze');
        if (!empty($refundReasonAnalyze)) {
            $siteIds = SitePayment::query()
                ->where('refund_reason_analyze', $refundReasonAnalyze)
                ->pluck('site_id');
            $build->whereIn('id', $siteIds);
        }

        $filterUserIds = [];
        $sellerId = $request->input('sellerId');
        $sellerId && $filterUserIds[] = $sellerId;
        $serverId = $request->input('serverId');
        $serverId && $filterUserIds[] = $serverId;
        if ($filterUserIds) {
            $build->whereExists(function (\Illuminate\Database\Query\Builder $b) use ($filterUserIds) {
                $raw = sprintf('SUM(CASE WHEN user_id in (%s) then  1 ELSE  0 end) as total', implode(',', $filterUserIds));
                $b->select(DB::raw($raw))->from('user_has_sites')
                    ->whereRaw(sprintf('user_has_sites.site_id=sites.id HAVING total=%s', count($filterUserIds)));
            });
        }

        $builder = $build->with(['sitePayment'])->where(function (Builder $q) use ($keyword, $status) {
            if ($keyword) {
                $q->where('domain', 'like', '%' . $keyword . '%')
                    ->orWhere('cn_title', 'like', '%' . $keyword . '%');
            }
            if ($status) {
                $q->where('status', $status);
            }
        })->where(['status' => 7]);

        if ($monthRange) {
            $startDate = sprintf("%s-01 00:00:00", $monthRange);
            $builder->where('expired_at', '>=', $startDate)
                ->where('expired_at', '<=', sprintf("%s-%s 23:59:59", $monthRange, date('t', strtotime($startDate))));
        }

        return $builder;
    }


    //客服任务
    public function customerServiceTask(Request $request)
    {
        if (!$request->ajax()) {
            $projectSteward = User::query()
                ->where('status', 1)
                ->where('role_id', 7)
                ->pluck('nickname', 'id');
            return view('admin/analyze/customer_service_task', [
                'projectSteward' => $projectSteward
            ]);
        }

        $task = DB::table('tasks');
        $builder = Site::query();
        $status = $request->input('status');
        if (!empty($status)) {
            $builder->where('status', $status);
        }
        $keyword = $request->input('keyword');
        if (!empty($keyword)) {
            $siteIds = $builder->where('cn_title', 'like', '%' . $keyword . '%')->pluck('id');
            $task->whereIn('site_id', $siteIds);
        } else {
            $siteIds = $builder->pluck('id')->toArray();
            $task->whereIn('site_id', $siteIds);
        }
        $projectStewardId = $request->input('projectStewardId');
        if (!empty($projectStewardId)) {
            $siteIds = DB::table('user_has_sites')->where('user_id', $projectStewardId)->pluck('site_id');
            $task->whereIn('site_id', $siteIds);
        }

        $page = $request->input('page') ?? 1;
        $size = $request->input('size') ?? 10;
        $taskList = $task->get();

        foreach ($taskList as $key => $value) {
            $taskList[$key]->dataList = \GuzzleHttp\json_decode($value->data, true);
        }
        $list = [];

        foreach ($taskList as $key => $value) {

            foreach ($value->dataList['dataList'] as $kk => $vv) {
                $date = '';
                if (!empty($vv['flow_except_date'])) {
                    $date = date('Y-m-d', strtotime($vv['flow_except_date']));
                }
                $date1 = '';
                if (!empty($vv['initial_date'])) {
                    $date1 = date('Y-m-d', strtotime($vv['initial_date']));
                }
                $info = [
                    'siteId' => $value->site_id ?? 0,
                    'taskId' => $vv['task_id'] ?? '',
                    'statusId' => $value->status ?? 0,
                    'status' => $value->status ?? 0,
                    'title' => $vv['title'] ?? '',
                    'data' => $vv['data'] ?? '',
                    'fulfil' => $vv['fulfil'] ?? '',
                    'flow_except_date' => $date,
                    'initial_date' => $date1,
                    'personnel' => $vv['personnel'] ?? 0,

                ];

                $list[] = $info;
            }
        }
        $fulfil = $request->input('fulfil');
        if (!empty($fulfil)) {
            foreach ($list as $key => $value) {
                if ($fulfil != $value['fulfil']) {
                    unset($list[$key]);
                }
            }
        }

        $list = $this->array_sort($list, 'fulfil', 'asc');

        $listList = DB::table('sites')->whereNull('deleted_at')->pluck('cn_title', 'id')->toArray() ?? [];
        $userList = DB::table('users')->whereNull('deleted_at')->pluck('nickname', 'id')->toArray() ?? [];

        foreach ($list as $key => $value) {
            $list[$key]['siteName'] = $listList[$value['siteId']] ?? '';
            $list[$key]['user'] = $userList[$value['personnel']] ?? '';

            $list[$key]['fulfil'] = '未完成';
            if ($value['fulfil'] == 2) {
                $list[$key]['fulfil'] = '完成';
            }
            $list[$key]['status'] = Site::STATUS_MAP[$value['status']];
        }

        $count = count($list);
        $list = array_splice($list, $size * ($page - 1), $size);

        return response()->json([
            'rows' => $list,
            'total' => $count
        ]);
    }

    /**
     * 二维数组排序
     * @param array $arr 需要排序的二维数组
     * @param string $keys 所根据排序的key
     * @param string $type 排序类型，desc、asc
     * @return array $new_array 排好序的结果
     */
    function array_sort($arr, $keys, $type = 'desc')
    {
        $key_value = $new_array = array();
        foreach ($arr as $k => $v) {
            $key_value[$k] = $v[$keys];
        }
        if ($type == 'asc') {
            asort($key_value);
        } else {
            arsort($key_value);
        }
        reset($key_value);
        foreach ($key_value as $k => $v) {
            $new_array[$k] = $arr[$k];
        }
        return $new_array;
    }

    //客服任务保存\删除
    public function customerServiceTaskCarryOut($type, $siteId, $status, $taskId)
    {

        $taskInfo = DB::table('tasks')->where([['site_id', $siteId], ['status', $status]])->value('data');
        if (empty($taskInfo)) {
            return response()->json(['message' => '项目不存在'], 400);
        }
        $taskInfo = \GuzzleHttp\json_decode($taskInfo, true);

        if ($type == 1) {
            foreach ($taskInfo['dataList'] as $key => $value) {
                if ($taskId == $value['task_id']) {
                    $taskInfo['dataList'][$key]['fulfil'] = 2;
                    $taskInfo['dataList'][$key]['flow_except_date'] = date('Y-m-d');
                }
            }
            $updateData = [
                'data' => \GuzzleHttp\json_encode($taskInfo),
                'site_id' => $siteId,
                'status' => $status
            ];
        } else {
            foreach ($taskInfo['dataList'] as $key => $value) {
                if ($taskId == $value['task_id']) {
                    unset($taskInfo['dataList'][$key]);
                }
            }
            $updateData = [
                'data' => \GuzzleHttp\json_encode($taskInfo),
                'site_id' => $siteId,
                'status' => $status,
            ];
        }
        $result = DB::table('tasks')->where([['site_id', $siteId], ['status', $status]])->update($updateData);
        if (empty($result)) {
            return response()->json(['message' => '操作失败'], 400);
        } else {
            return response()->json(['message' => '操作成功']);
        }
    }


    //测速
    public function speedMeasurement(Request $request)
    {
        if (!$request->ajax()) {

            return view('admin/analyze/speed_measurement', [
                'webs' => Role::getUsers(Role::TYPE_WEB),
                'services' => Role::getUsers(Role::TYPE_SERVER), //服务人员
                'editors' => Role::getUsers(Role::TYPE_AE),
                'managers' => Role::getUsers(Role::TYPE_MANAGER),
                'managerHelpers' => Role::getUsers(Role::TYPE_MANAGE_HELPER),
                'sellerUsers' => Role::getUsers(Role::TYPE_SELLER),
                'optimizers' => Role::getUsers(Role::TYPE_OPTIMIZER),
            ]);
        }

        $builder = $this->speedMeasurementBuilder();
        $sites = $builder->orderByDesc('id')->paginate($request->input('pageSize') ?? TABLE_PAGE_SIZE);
        $items = $sites->items();

        $pcSpeedMeasurement = [];
        $mobileSpeedMeasurement = [];

        $speedMeasurementList = DB::table('app_speed_measurement_cache')->get();
        foreach ($speedMeasurementList as $key => $value) {
            $array = json_decode($value->cache, true);
            if (!empty($array)) {
                $pcSpeedMeasurementResult = $array['pc'];
                $mobileSpeedMeasurementResult = $array['mobile'];

                $oldId = str_replace('cache:app/Http/Controllers/SpeedMeasurementController/index:', '', $value->key);

                $pcSpeedMeasurement[$oldId] = $pcSpeedMeasurementResult;
                $mobileSpeedMeasurement[$oldId] = $mobileSpeedMeasurementResult;
            }
        }

        foreach ($items as $item) {
            $item->identify = base64_encode($item->id);
            $item->managers_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGER)->pluck('nickname')->toArray());
            $item->server_title = implode('-', $item->users->where('role_id', Role::TYPE_SERVER)->pluck('nickname')->toArray());
            $item->seller_title = implode('-', $item->users->where('role_id', Role::TYPE_SELLER)->pluck('nickname')->toArray());
            $item->optimizerTitle = implode('-', $item->users->where('role_id', Role::TYPE_OPTIMIZER)->pluck('nickname')->toArray());
            $item->manage_helper_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGE_HELPER)->pluck('nickname')->toArray());
            $item->ae_title = implode('-', $item->users->where('role_id', Role::TYPE_AE)->pluck('nickname')->toArray());
            $item->web = implode('-', $item->users->where('role_id', Role::TYPE_WEB)->pluck('nickname')->toArray());

            $item->bidTitle = implode('-', $item->users->where('role_id', Role::TYPE_BID)->pluck('nickname')->toArray());
            $item->articleTitle = implode('-', $item->users->where('role_id', Role::TYPE_PROPAGANDA)->pluck('nickname')->toArray());
            $item->designerTitle = implode('-', $item->users->where('role_id', Role::TYPE_DESIGNER)->pluck('nickname')->toArray());

            $item->pcSpeedMeasurement = $pcSpeedMeasurement[$item->old_id] ?? '-';
            $item->mobileSpeedMeasurement = $mobileSpeedMeasurement[$item->old_id] ?? '-';

            $item->sign_at = substr($item->sign_at, 0, 10);
            $item->order_at = substr($item->order_at, 0, 10);
            $item->assign_at = substr($item->assign_at, 0, 10);
            $item->reach_at = substr($item->reach_at, 0, 10);
            $item->expired_at = substr($item->expired_at, 0, 10);
            $item->bq_at = substr($item->bq_at, 0, 10);
            $item->renewal_at = substr($item->renewal_at, 0, 10);

            if ($item->online_at && $item->assign_at) {
                $orderAt = date_create($item->assign_at);
                $onlineAt = date_create($item->online_at);
                $diff = date_diff($orderAt, $onlineAt);
                $item->online_days = $diff->days;
            } else {
                $item->online_days = null;
            }
            $item->online_at = substr($item->online_at, 0, 10);
        };

        return response()->json([
            'rows' => $items,
            'total' => $sites->total()
        ]);
    }

    public function speedMeasurementBuilder()
    {
        $request = \request();
        $filterUserIds = [];
        $editorId = $request->input('editorId');
        $editorId && $filterUserIds[] = $editorId;
        $manageHelperId = $request->input('manageHelperId');
        $manageHelperId && $filterUserIds[] = $manageHelperId;
        $managerId = $request->input('managerId');
        $managerId && $filterUserIds[] = $managerId;
        $serverId = $request->input('serverId');
        $serverId && $filterUserIds[] = $serverId;
        $sellerId = $request->input('sellerId');
        $sellerId && $filterUserIds[] = $sellerId;

        $optimizerId = $request->input('optimizerId');
        $optimizerId && $filterUserIds[] = $optimizerId;

        $keyword = $request->input('keyword');
        $builder = Site::query()->with(['users'])->where(function (Builder $q) use ($keyword) {
            if ($keyword) {
                $q->where('domain', 'like', '%' . $keyword . '%')
                    ->orWhere('cn_title', 'like', '%' . $keyword . '%');
            }
        });

        $sortName = $request->input('sortName');
        $sortOrder = $request->input('sortOrder');
        if (!empty($sortName) || !empty($sortOrder)) {
            $builder->orderBy($sortName, $sortOrder);
        }

        if ($filterUserIds) {
            $builder->whereExists(function (\Illuminate\Database\Query\Builder $b) use ($filterUserIds) {
                $raw = sprintf('SUM(CASE WHEN user_id in (%s) then  1 ELSE  0 end) as total', implode(',', $filterUserIds));
                $b->select(DB::raw($raw))->from('user_has_sites')
                    ->whereRaw(sprintf('user_has_sites.site_id=sites.id HAVING total=%s', count($filterUserIds)));
            });
        }
        return $builder;
    }

    public function speedMeasurementExport()
    {
        $builder = $this->speedMeasurementBuilder();
        $sites = $builder->orderByDesc('id')->get()->toArray();
        $list = [];

        $pcSpeedMeasurement = [];
        $mobileSpeedMeasurement = [];

        $speedMeasurementList = DB::table('app_speed_measurement_cache')->get();
        foreach ($speedMeasurementList as $key => $value) {
            $array = json_decode($value->cache, true);
            if (!empty($array)) {
                $pcSpeedMeasurementResult = $array['pc'];
                $mobileSpeedMeasurementResult = $array['mobile'];

                $oldId = str_replace('cache:app/Http/Controllers/SpeedMeasurementController/index:', '', $value->key);

                $pcSpeedMeasurement[$oldId] = $pcSpeedMeasurementResult;
                $mobileSpeedMeasurement[$oldId] = $mobileSpeedMeasurementResult;
            }
        }

        foreach ($sites as $key => $site) {
            $onlineDays = 0;
            if ($site['online_at'] && $site['assign_at']) {
                $orderAt = date_create($site['assign_at']);
                $onlineAt = date_create($site['online_at']);
                $diff = date_diff($orderAt, $onlineAt);
                $onlineDays = $diff->days;
            }

            $sellerTitle = [];//销售
            $serverTitle = [];//客服
            $managersTitle = [];//项目经理1
            $manageHelperTitle = [];//项目经理

            $aeTitle = [];//采编
            $optimizerTitle = [];//优化师
            $bidTitle = [];//竞价
            $articleTitle = [];//软文
            $designerTitle = [];//设计
            $webTitle = [];//前端

            $userList = $site['users'];
            foreach ($userList as $kk => $value) {
                if ($value['role_id'] == Role::TYPE_SELLER) {
                    $sellerTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_SERVER) {
                    $serverTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_MANAGER) {
                    $managersTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_MANAGE_HELPER) {
                    $manageHelperTitle [] = $value['nickname'];
                }

                if ($value['role_id'] == Role::TYPE_AE) {
                    $aeTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_OPTIMIZER) {
                    $optimizerTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_BID) {
                    $bidTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_PROPAGANDA) {
                    $articleTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_DESIGNER) {
                    $designerTitle [] = $value['nickname'];
                }
                if ($value['role_id'] == Role::TYPE_WEB) {
                    $webTitle [] = $value['nickname'];
                }
            }

            $list[$key]['online_at'] = substr($site['online_at'], 0, 10);//上线时间
            $list[$key]['cn_title'] = $site['cn_title'];//公司
            $list[$key]['domain'] = $site['domain'];//网站
            $list[$key]['keyword_goal'] = $site['keyword_goal'];//关键词指标
            $list[$key]['online_days'] = $onlineDays;//建站时长

            $list[$key]['pcSpeedMeasurement'] = $pcSpeedMeasurement[$site['old_id']] ?? '';
            $list[$key]['mobileSpeedMeasurement'] = $mobileSpeedMeasurement[$site['old_id']] ?? '';

            $list[$key]['seller_title'] = implode('-', $sellerTitle);//销售
            $list[$key]['server_title'] = implode('-', $serverTitle);//客服经理
            $list[$key]['managers_title'] = implode('-', $managersTitle);//项目经理
            $list[$key]['manage_helper_title'] = implode('-', $manageHelperTitle);//项目经理1

            $list[$key]['optimizerTitle'] = implode('-', $optimizerTitle);//优化
            $list[$key]['ae_title'] = implode('-', $aeTitle);//采编
            $list[$key]['bidTitle'] = implode('-', $bidTitle);//竞价
            $list[$key]['articleTitle'] = implode('-', $articleTitle);//软文
            $list[$key]['designerTitle'] = implode('-', $designerTitle);//设计
            $list[$key]['webTitle'] = implode('-', $webTitle);//前端

            $list[$key]['sign_at'] = substr($site['sign_at'], 0, 10);//合同签订时间
            $list[$key]['order_at'] = substr($site['order_at'], 0, 10);//下单时间
            $list[$key]['assign_at'] = substr($site['assign_at'], 0, 10);//分配时间
            $list[$key]['reach_at'] = substr($site['reach_at'], 0, 10);//达标时间
            $list[$key]['expired_at'] = substr($site['expired_at'], 0, 10);//过期时间
            $list[$key]['bq_at'] = substr($site['bq_at'], 0, 10);//百千约访时间
            $list[$key]['renewal_times'] = $site['renewal_times'];//续费次数
            $list[$key]['renewal_at'] = substr($site['renewal_at'], 0, 10);//续费时间
        }
        array_unshift($list, ['上线时间', '公司', '网站', '关键词指标', '建站时长', 'pc端测速得分', '移动端测速得分', '项目销售', '客服经理', '项目经理1', '项目经理', '优化组长', '项目采编', '竞价', '软文', '设计', '前端', '合同签订时间', '下单时间', '分配时间', '达标时间', '过期时间', '百千约访时间', '续费次数', '续费时间']);
        return (new BasicExport($list))->download(sprintf('测速得分%s.xls', date('YmdHis')));

    }

    //查找任务管理
    public function findTaskManagement(Request $request)
    {
        if (!$request->ajax()) {
            $userList = User::query()->where(['status' => 1])->whereIn('role_id', [15, 7, 6, 25, 26, 2, 1])->select(['id', 'nickname', 'role_id'])->get();
            $businessList = Business::query()->pluck('title', 'id');
            return view('admin/site/index', [
                'userList' => $userList,
                'businessList' => $businessList,
                'role_id' => 15,
                'is_propaganda' => 1,//品宣只显示建站期和服务期
            ]);
        }
    }

    //项目进度
    public function projectProgress(Request $request)
    {
        if (!$request->ajax()) {
            $this->statisticsTask();
            return view('admin/analyze/project_progress');
        }
        $projectProgressStatistics = DB::table('project_progress_statistics');
        $count = DB::table('project_progress_statistics')->count();

        $keyword = $request->input('keyword');
        if (!empty($keyword)) {
            $siteIds = Db::table('sites')->where('cn_title', 'like', $keyword . '%')->pluck('id')->toArray();
            $projectProgressStatistics->whereIn('sites_id', [$siteIds]);
        }
        $sortName = $request->input('sortName');
        $sortOrder = $request->input('sortOrder');
        if (!empty($sortName) || !empty($sortOrder)) {
            $projectProgressStatistics->orderBy($sortName, $sortOrder);
        }
        $pageSize = $request->input('pageSize');

        $projectProgressStatisticsList = $projectProgressStatistics->paginate($pageSize);
        $list = $projectProgressStatisticsList->items();

        foreach ($list as $key => $value) {

            if ($value->is_project_data_collection == 1) {
                $list[$key]->is_project_data_collection = '完成';
            } else {
                $list[$key]->is_project_data_collection = '-';
            }
            if (!empty($value->project_data_collection_date)) {
                $list[$key]->project_data_collection_date = date('Y-m-d', strtotime($value->project_data_collection_date));
            }

            if ($value->is_site_architecture == 1) {
                $list[$key]->is_site_architecture = '完成';
            } else {
                $list[$key]->is_site_architecture = '-';
            }

            if (!empty($value->site_architecture_date)) {
                $list[$key]->site_architecture_date = date('Y-m-d', strtotime($value->site_architecture_date));
            }

            if ($value->is_homepage_design == 1) {
                $list[$key]->is_homepage_design = '完成';
            } else {
                $list[$key]->is_homepage_design = '-';
            }

            if (!empty($value->homepage_design_date)) {
                $list[$key]->homepage_design_date = date('Y-m-d', strtotime($value->homepage_design_date));
            }

            if ($value->is_inside_page_design == 1) {
                $list[$key]->is_inside_page_design = '完成';
            } else {
                $list[$key]->is_inside_page_design = '-';
            }

            if (!empty($value->inside_page_design_date)) {
                $list[$key]->inside_page_design_date = date('Y-m-d', strtotime($value->inside_page_design_date));
            }

            if ($value->is_test_station == 1) {
                $list[$key]->is_test_station = '完成';
            } else {
                $list[$key]->is_test_station = '-';
            }

            if (!empty($value->test_station_date)) {
                $list[$key]->test_station_date = date('Y-m-d', strtotime($value->test_station_date));
            }

            if ($value->is_key_words == 1) {
                $list[$key]->is_key_words = '完成';
            } else {
                $list[$key]->is_key_words = '-';
            }

            if (!empty($value->key_words_date)) {
                $list[$key]->key_words_date = date('Y-m-d', strtotime($value->key_words_date));
            }

            if ($value->is_seo_perfect == 1) {
                $list[$key]->is_seo_perfect = '完成';
            } else {
                $list[$key]->is_seo_perfect = '-';
            }

            if (!empty($value->seo_perfect_date)) {
                $list[$key]->seo_perfect_date = date('Y-m-d', strtotime($value->seo_perfect_date));
            }

            if ($value->is_online == 1) {
                $list[$key]->is_online = '完成';
            } else {
                $list[$key]->is_online = '-';
            }

            if (!empty($value->online_date)) {
                $list[$key]->online_date = date('Y-m-d', strtotime($value->online_date));
            }

            if ($value->is_entire_site_information_provision == 1) {
                $list[$key]->is_entire_site_information_provision = '完成';
            } else {
                $list[$key]->is_entire_site_information_provision = '-';
            }

            if (!empty($value->entire_site_information_provision_date)) {
                $list[$key]->entire_site_information_provision_date = date('Y-m-d', strtotime($value->entire_site_information_provision_date));
            }
        }

        return response()->json([
            'rows' => $list,
            'total' => count($list) ?? 0
        ]);
    }

    public function statisticsTask()
    {
        $siteList = Site::query()->with(['users' => function ($query) {
                $query->where('role_id', 25);
            }]
        )->where('status', 1)->select('id', 'cn_title')->get()->toArray();

        $flowStageList = DB::table('flow_stage as a')
            ->join('flow_info as b', 'a.id', '=', 'b.stage_id')
            ->select('a.id', 'a.title', 'a.site_id', 'b.detail_list')
            ->get()->toJson();

        $flowStageList = \GuzzleHttp\json_decode($flowStageList, true);

        foreach ($flowStageList as $key => $value) {

            $result = \GuzzleHttp\json_decode($value['detail_list'], true);
            krsort($result);
            $result = array_merge($result);
            $flowStageList[$key]['info'] = $result;
        }


        foreach ($siteList as $key => $v) {

            $siteList[$key]['list'] = [];

            foreach ($flowStageList as $kk => $vv) {

                if ($v['id'] == $vv['site_id']) {

                    $siteList[$key]['list'][] = $vv;
                }
            }
        }

        $insertData = [];
        foreach ($siteList as $key => $value) {
            $updateData = [
                'sites_id' => $value['id'] ?? '',//项目id
                'project' => $value['cn_title'] ?? '',//项目名称
                'project_manager' => $value['users'][0]['nickname'] ?? '',//项目经理
                'project_data_collection_date' => null,
                'is_project_data_collection' => 0,
                'site_architecture_date' => null,
                'is_site_architecture' => 0,
                'homepage_design_date' => null,
                'is_homepage_design' => 0,
                'inside_page_design_date' => null,
                'is_inside_page_design' => 0,
                'test_station_date' => null,
                'is_test_station' => 0,
                'key_words_date' => null,
                'is_key_words' => 0,
                'seo_perfect_date' => null,
                'is_seo_perfect' => 0,
                'online_date' => null,
                'is_online' => 0,
                'entire_site_information_provision_date' => null,
                'is_entire_site_information_provision' => 0,
            ];

            if (!empty($value['list'][0]['info'][0]['flow_done_date'])) {
                $projectDataCollectionDate = $value['list'][0]['info'][0]['flow_done_date'];
                $updateData['project_data_collection_date'] = $projectDataCollectionDate;
                $updateData['is_project_data_collection'] = 1;
            }

            if (!empty($value['list'][1]['info'][0]['flow_done_date'])) {
                $siteArchitectureDate = $value['list'][1]['info'][0]['flow_done_date'];
                $updateData['site_architecture_date'] = $siteArchitectureDate;
                $updateData['is_site_architecture'] = 1;
            }

            if (!empty($value['list'][2]['info'][0]['flow_done_date'])) {
                $homepageDesignDate = $value['list'][2]['info'][0]['flow_done_date'];
                $updateData['homepage_design_date'] = $homepageDesignDate;
                $updateData['is_homepage_design'] = 1;
            }

            if (!empty($value['list'][3]['info'][0]['flow_done_date'])) {
                $insidePageDesignDate = $value['list'][3]['info'][0]['flow_done_date'];
                $updateData['inside_page_design_date'] = $insidePageDesignDate;
                $updateData['is_inside_page_design'] = 1;
            }

            if (!empty($value['list'][4]['info'][0]['flow_done_date'])) {
                $testStationDate = $value['list'][4]['info'][0]['flow_done_date'];
                $updateData['test_station_date'] = $testStationDate;
                $updateData['is_test_station'] = 1;
            }

            if (!empty($value['list'][5]['info'][0]['flow_done_date'])) {
                $keyWordsDate = $value['list'][5]['info'][0]['flow_done_date'];
                $updateData['key_words_date'] = $keyWordsDate;
                $updateData['is_key_words'] = 1;
            }

            if (!empty($value['list'][6]['info'][0]['flow_done_date'])) {
                $seoPerfectDate = $value['list'][6]['info'][0]['flow_done_date'];
                $updateData['seo_perfect_date'] = $seoPerfectDate;
                $updateData['is_seo_perfect'] = 1;
            }

            if (!empty($value['list'][7]['info'][0]['flow_done_date'])) {
                $onlineDate = $value['list'][7]['info'][0]['flow_done_date'];
                $updateData['online_date'] = $onlineDate;
                $updateData['is_online'] = 1;
            }

            if (!empty($value['list'][8]['info'][0]['flow_done_date'])) {
                $entireSiteInformationProvisionDate = $value['list'][8]['info'][0]['flow_done_date'];
                $updateData['entire_site_information_provision_date'] = $entireSiteInformationProvisionDate;
                $updateData['is_entire_site_information_provision'] = 1;
            }
            $insertData[] = $updateData;
        }

        DB::table('project_progress_statistics')->delete();
        DB::table('project_progress_statistics')->insert($insertData);
    }

    //项目进度导出
    public function projectProgressExport()
    {
        $dataList[] = ['项目名称', '项目经理', '项目资料搜集-进度', '完成时间', '网站架构-进度', '完成时间', '首页设计-进度', '完成时间', '内页设计-进度', '完成时间', '测试站-进度', '完成时间', '关键词-进度', '完成时间', 'SEO完善-进度', '完成时间', '上线-进度', '完成时间', '整站资料提供-进度', '完成时间'];
        $list = DB::table('project_progress_statistics')
            ->select(
                'project',
                'project_manager',
                'is_project_data_collection',
                'project_data_collection_date',
                'is_site_architecture',
                'site_architecture_date',
                'is_homepage_design',
                'homepage_design_date',
                'is_inside_page_design',
                'inside_page_design_date',
                'is_test_station',
                'test_station_date',
                'is_key_words',
                'key_words_date',
                'is_seo_perfect',
                'seo_perfect_date',
                'is_online',
                'online_date',
                'is_entire_site_information_provision',
                'entire_site_information_provision_date'
            )->get()->toJson();
        $result = \GuzzleHttp\json_decode($list, true);
        foreach ($result as $key => $value) {
            if ($value['is_project_data_collection'] == 1) {
                $result[$key]['is_project_data_collection'] = '完成';
            } else {
                $result[$key]['is_project_data_collection'] = '-';
            }
            if ($value['is_site_architecture'] == 1) {
                $result[$key]['is_site_architecture'] = '完成';
            } else {
                $result[$key]['is_site_architecture'] = '-';
            }
            if ($value['is_homepage_design'] == 1) {
                $result[$key]['is_homepage_design'] = '完成';
            } else {
                $result[$key]['is_homepage_design'] = '-';
            }
            if ($value['is_inside_page_design'] == 1) {
                $result[$key]['is_inside_page_design'] = '完成';
            } else {
                $result[$key]['is_inside_page_design'] = '-';
            }
            if ($value['is_test_station'] == 1) {
                $result[$key]['is_test_station'] = '完成';
            } else {
                $result[$key]['is_test_station'] = '-';
            }
            if ($value['is_key_words'] == 1) {
                $result[$key]['is_key_words'] = '完成';
            } else {
                $result[$key]['is_key_words'] = '-';
            }
            if ($value['is_seo_perfect'] == 1) {
                $result[$key]['is_seo_perfect'] = '完成';
            } else {
                $result[$key]['is_seo_perfect'] = '-';
            }
            if ($value['is_online'] == 1) {
                $result[$key]['is_online'] = '完成';
            } else {
                $result[$key]['is_online'] = '-';
            }
            if ($value['is_entire_site_information_provision'] == 1) {
                $result[$key]['is_entire_site_information_provision'] = '完成';
            } else {
                $result[$key]['is_entire_site_information_provision'] = '-';
            }
        }
        foreach ($result as $key => $value) {
            $dataList[] = $value;
        }

        return (new BasicExport($dataList))->download(sprintf('项目进度统计%s.xls', date('YmdHis')));
    }

    public function statisticsInquiry()
    {
        set_time_limit(0);
        $siteList = Site::query()->whereIn('status', [2, 3])->get();
        DB::table('statistics_inquiry')->delete();

        foreach ($siteList as $siteInfo) {

            try {
                $config = [
                    'connection_name' => sprintf('connection_name_%s', $siteInfo->id),
                    'host' => $siteInfo->server->server_ip,
                    'port' => '3306',
                    'database' => $siteInfo->database,
                    'username' => $siteInfo->server->mysql_user_name,
                    'password' => $siteInfo->server->mysql_passwd,
                ];
                config_connection($config);
                $list = DB::connection($config['connection_name'])->table('user_msg')
                        ->selectRaw('FROM_UNIXTIME(create_time, "%Y-%m") as date, count(*) as count')
                        ->groupBy('date')
                        ->get()->toArray() ?? [];

                if (!empty($list)) {

                    $createTime = DB::connection($config['connection_name'])->table('user_msg')
                        ->orderBy('id', 'asc')
                        ->value('create_time');

                    $lastTime = DB::connection($config['connection_name'])->table('user_msg')
                        ->orderBy('id', 'desc')
                        ->value('create_time');

                    if (!empty($createTime) && !empty($lastTime)) {
                        $dateList = $this->getDateList($createTime, $lastTime);

                        foreach ($dateList as $key => $value) {
                            $dateList[$key]['count'] = 0;

                            foreach ($list as $kkk => $vvv) {

                                if (strtotime($value['date']) == strtotime($vvv->date)) {
                                    $dateList[$key]['count'] = $vvv->count;
                                }
                            }

                            $num = 0;
                            foreach ($list as $kk => $vv) {
                                if (strtotime($value['date']) >= strtotime($vv->date)) {
                                    $num += $vv->count;
                                }
                            }
                            $dateList[$key]['sum'] = $num;
                        }
                        //重置数组中的key
                        $dateList = array_merge($dateList);
                        $data = [
                            'site_id' => $siteInfo->id,
                            'cn_title' => $siteInfo->cn_title,
                            'old_id' => $siteInfo->old_id,
                            'data' => \GuzzleHttp\json_encode($dateList),
                            'last_time' => date('Y-m-d H:i:s', $lastTime),
                        ];
                        DB::table('statistics_inquiry')->insert($data);
                    }
                }
            } catch (\Throwable $exception) {
                pre_dump($exception->getMessage());
            }
        }
    }


    public function getDateList($startTime, $lastTime)
    {
        $startTime = strtotime(date("Y-m", strtotime("-1 months", $startTime)));
        $endTime = strtotime(date('Y-m', $lastTime));

        $dateList = [];
        $i = 0;
        while ($startTime < $endTime) {
            $startTime = strtotime('+1 Month', $startTime);
            $i++;
            $dateList[$i]['date'] = date('Y-m', $startTime);
        }
        return $dateList;
    }

    public function sendMessage(Request $request)
    {
        $siteId = $request->input('siteId');
        $mobile = $request->input('mobile');

        if (empty($mobile)) {
            return response()->json(['message' => '请填写手机号'], 400);
        }
        if (!preg_match("/^1[3456789]\d{9}$/", $mobile)) {
            return response()->json(['message' => '手机号输入有误'], 400);
        }
        $siteInfo = Site::query()->where('id', $siteId)->first();
        if (empty($siteInfo)) {
            return response()->json(['message' => '项目不存在'], 400);
        }
        //5分钟以内发重复的code
        $time = strtotime(date('Y-m-d H:i:s')) - strtotime($siteInfo->create_time_code);
        if (!empty($siteInfo->create_time_code) && $time > 300) {
            $code = mt_rand(1000, 9999);
        } else {
            $code = $siteInfo->code;
        }
        if (empty($siteInfo->code)) {
            $code = mt_rand(1000, 9999);
        }

        try {
            $url = 'http://translate.api.yinqingli.net/openapi/Msg/Msg';
            $data = [
                'mobile' => $mobile,
                'tpl' => 'SMS_223585128',
                'TemplateParam' => [
                    'code' => $code,
                ],
            ];
            $client = new Client();
            $response = $client->post($url, [
                'form_params' => $data,
            ]);
            $result = $response->getBody()->getContents();//没返回
            $update = [
                'code' => $data['TemplateParam']['code'],
                'create_time_code' => date('Y-m-d H:i:s'),
                'code_num' => ++$siteInfo->code_num
            ];
            Site::query()->where('id', $siteId)->update($update);

            return response()->json(['message' => '操作成功']);

        } catch (\Throwable $exception) {
            return response()->json(['message' => '服务器未知错误'], 400);
        }
    }


}