<?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',[
'optimizers' => Role::getUsers(Role::TYPE_OPTIMIZATION_EDITING),
'managers' => Role::getUsers(Role::TYPE_MANAGER),
'managerHelpers' => Role::getUsers(Role::TYPE_MANAGE_HELPER),
'sellerUsers' => Role::getUsers(Role::TYPE_SELLER),
]);
}
$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();
$filterUserIds = [];
$keyword = $request->input('keyword');
$pay_way = $request->input('pay_way');
$address = $request->input('address');
$status = $request->input('status');
$sellerId = $request->input('sellerId');
$sellerId && $filterUserIds[] = $sellerId;
$editorId = $request->input('editorId');
$editorId && $filterUserIds[] = $editorId;
$manageHelperId = $request->input('manageHelperId');
$manageHelperId && $filterUserIds[] = $manageHelperId;
$signAt = $request->input('sign_at');
$builder = Site::query()->with(['users', 'sitePayment'])->where(function (Builder $q) use ($keyword, $status, $address,$pay_way) {
if ($keyword) {
$q->where('domain', 'like', '%' . $keyword . '%')
->orWhere('cn_title', 'like', '%' . $keyword . '%');
}
if ($status) {
$q->where('status', $status);
}
if ($pay_way) {
$q->where('pay_way', $pay_way);
}
if ($address) {
$address = json_encode($address);
$address = (trim($address, '"'));
$address = str_replace('\\', '%' . '\\', $address);
$q->where('area_text', 'like', '%' . $address . '%');
}
});
if ($signAt) {
$begin = $signAt.' 00:00:00';
$end_time =$signAt.' 23:59:59';
$builder->whereBetween('sign_at', [$begin, $end_time]);
}
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 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);
}
}
}