* @since 2021-08-23 */ namespace App\Http\Controllers\Admin; use App\Http\Controllers\Controller; use App\Http\Models\Role; use App\Http\Models\Site; use App\Http\Models\SitePayment; use App\Http\Models\User; use Illuminate\Http\Request; use Illuminate\Database\Eloquent\Builder; use Illuminate\Support\Facades\DB; use App\Http\Models\Contract; class BidController extends Controller { //竞价列表 public function siteList(Request $request, $type) { if (!$request->ajax()) { $userList = User::query()->select(['id', 'nickname'])->where(['status' => 1])->whereIn('role_id', [16, 14, 27])->get(); return view('admin.bid.site_list', [ 'userList' => $userList, 'type' => $type ]); } if ($status = $request->input('status')) { $condition['status'] = $status; } if ($request->input('bid_status') == 'on') { $condition['bid_status'] = 1; } //已投放个数 if ($type == 1) { $condition['bid_status'] = 1; } //待投放个数 if ($type == 2) { $condition['bid_status'] = 0; $status = [2, 3, 5, 6, 7, 8, 9]; $condition[] = [function ($query) use ($status) { $query->whereIn('status', $status); }]; } //已暂停放个数 if ($type == 3) { $siteIdList = Site::query()->whereJsonContains('contract_ids', "3")->pluck('id'); $siteIds = SitePayment::query()->whereIn('site_id', $siteIdList)->whereNotNull('bid_account_pause_time')->pluck('site_id')->toArray() ?? []; $condition[] = [function ($query) use ($siteIds) { $query->whereIn('id', $siteIds); }]; } //已续费个数 if ($type == 4) { $siteIdList = Site::query()->whereJsonContains('contract_ids', "3")->pluck('id'); $payRecords = SitePayment::query()->whereIn('site_id', $siteIdList)->selectRaw('site_id,google_bid')->whereNotNull('google_bid')->get(); $totalRenewalNum = []; foreach ($payRecords as $record) { $bids = $record->google_bid; $lastItem = array_pop($bids); if (!empty($lastItem['google_bid_time'])) { $totalRenewalNum[] = $record->site_id; } } $condition[] = [function ($query) use ($totalRenewalNum) { $query->whereIn('id', $totalRenewalNum); }]; } if ($siteUserId = $request->input('siteUserId')) { $siteIds = DB::table('user_has_sites')->where(['user_id' => $siteUserId])->pluck('site_id')->toArray(); } $keyword = $request->input('keyword'); $builder = Site::query()->with(['users', 'sitePayment']) ->where($condition ?? [])->where(function (Builder $builder) use ($keyword) { if ($keyword) { $builder->where('domain', 'like', '%' . $keyword . '%') ->orWhere('cn_title', 'like', '%' . $keyword . '%'); } })->whereJsonContains('contract_ids', "3"); if (isset($siteIds)) { $builder = $builder->whereIn('id', $siteIds); } $sortName = $request->input('sortName'); $sortOrder = $request->input('sortOrder'); if ($sortName == 'statusTitle') { $builder->orderBy('status', $sortOrder); } $sites = $builder->orderByDesc('id')->paginate($request->input('pageSize') ?? TABLE_PAGE_SIZE); $items = $sites->items(); foreach ($items as $item) { $item->designer = implode('-', $item->users->where('role_id', Role::TYPE_DESIGNER)->pluck('nickname')->toArray()); $item->web_front = implode('-', $item->users->where('role_id', Role::TYPE_WEB)->pluck('nickname')->toArray()); $item->bidder_title = implode('-', $item->users->where('role_id', Role::TYPE_BID)->pluck('nickname')->toArray()); $item->statusTitle = Site::STATUS_MAP[$item->status] ?? ''; $item->bid_give_fee = $item->sitePayment->bid_give_fee ?? ''; $bidGiveFee = floatval($item->bid_give_fee); $gBid = $item->sitePayment->google_bid ?? []; $totalFee = $bidGiveFee; foreach ($gBid as $v) { $tempFee = floatval($v['google_bid_fee'] ?? 0); $totalFee += $tempFee; } $item->total_fee = $totalFee; $item->bid_account_online_time = empty($item->sitePayment->bid_account_online_time) ? '' : date('Y-m-d', strtotime($item->sitePayment->bid_account_online_time)); $item->bid_account_pause_time = empty($item->sitePayment->bid_account_pause_time) ? '' : date('Y-m-d', strtotime($item->sitePayment->bid_account_pause_time)); $item->bid_score = $item->sitePayment->bid_score ?? ''; $item->sustain_days = ''; if ($item->bid_account_online_time && $item->bid_account_pause_time) { $onlineAt = date_create($item->bid_account_online_time); $pauseAt = date_create($item->bid_account_pause_time); $diff = date_diff($pauseAt, $onlineAt); $item->sustain_days = $diff->days; } $item->is_google_bid = in_array(Contract::GOOGLE_BID, $item->contract_ids ?? []) ? 1 : 0; } $res = collect($items); if ($sortName == 'bid_account_online_time') { $res = $res->sortByDesc('bid_account_online_time'); } if ($sortName == 'bid_score') { $res = $res->sortByDesc('bid_score'); } if ($sortName == 'sustain_days') { $res = $res->sortByDesc('sustain_days'); } return response()->json([ 'rows' => array_values($res->toArray()), 'total' => $sites->total() ]); } //竞价仪表 public function dashboard() { $threeMonthsAgo = date('Y-m-01 00:00:00', strtotime('first day of -2 month')); $thisMonth = date('Y-m-t 23:59:59'); //竞价总费用 $data['totalTotalFee'] = $this->totalFee(); //竞价总费用三个月 $data['totalFeeDiff'] = $this->totalFee2(); //客户续费总和 $data['totalAvgScore'] = $this->renew(1); //客户续费三个月 $data['avgScoreDiff'] = $this->renew(); //续费个数 $data['totalRenewalNum'] = $this->renewal(); //续费个数三个月 $data['renewalNumDiff'] = $this->renewal2(); //账户总个数 $data['totalPpc'] = Site::query()->selectRaw('id,contract_ids') ->whereJsonContains('contract_ids', "3")->count(); //账户三个月 $data['ppcDiff'] = $this->ppcDiff($threeMonthsAgo, $thisMonth); //在投放个数 $data['totalRedPpc'] = Site::query()->selectRaw('id,contract_ids') ->whereJsonContains('contract_ids', "3")->where('bid_status', 1)->count(); //在投放个数三个月 $data['redPpcDiff'] = $this->redPpcDiff($threeMonthsAgo, $thisMonth); //未投放个数 $data['totalGrayPpc'] = Site::query()->whereIn('status', [2, 3, 5, 6, 7, 8, 9])->selectRaw('id,contract_ids') ->whereJsonContains('contract_ids', "3")->where('bid_status', 0)->count(); //未投放个数三个月 $data['grayPpcDiff'] = $this->grayPpcDiff($threeMonthsAgo, $thisMonth); //已暂停个数 $data['totalLineRedPpc'] = Site::query()->join('sites_payment', 'sites.id', '=', 'sites_payment.site_id') ->whereNotNull('bid_account_pause_time') ->whereJsonContains('contract_ids', "3")->count(); //已暂停个数三个月 $data['lineRedPpcDiff'] = $this->lineRedPpcDiff($threeMonthsAgo, $thisMonth); //既没有暂停也没有充值过的项目 $totalRedPpc = Site::query()->join('sites_payment', 'sites.id', '=', 'sites_payment.site_id') ->whereNull('bid_account_pause_time') ->whereJsonContains('contract_ids', "3")->where('bid_status', 1)->get(); $num = 0; foreach ($totalRedPpc as $key => $value) { $result = json_decode($value->google_bid); if (!empty($result)) { foreach ($result as $kk => $vv) { if (empty($vv->google_bid_fee)) { $num++; break; } } } else { $num++; } } //续费率=已续费个数/(已投放个数 - 既没有暂停也没有充值过的项目) $data['totalRenewalRate'] = number_format($data['totalRenewalNum'] / ($data['totalRedPpc'] - $num), 2); //续费率三个月 $data['renewalRateDiff'] = $this->ratio(); $data['ppcPie'] = $this->pie(); $data['feeLine'] = $this->line(); return view('admin.bid.dashboard', $data); } public function renew($type = 0) { $list = Site::query()->join('sites_payment', 'sites.id', '=', 'sites_payment.site_id') ->select('google_bid') ->whereJsonContains('contract_ids', "3") ->get()->toArray() ?? []; $array = []; foreach ($list as $key => $value) { $array[] = json_decode($value['google_bid'], true); } $result = []; foreach ($array as $key => $value) { if (is_array($value)) { foreach ($value as $kk => $vv) { if ($vv['google_bid_time'] && $vv['google_bid_fee']) { $vv['date'] = date('Y-m', strtotime($vv['google_bid_time'])); $result[] = $vv; } } } } if ($type != 0) { $num = 0; foreach ($result as $key => $value) { $num += (int)$value['google_bid_fee']; } return $num; } $threeMonthsAgo = date('Y-m-01 00:00:00', strtotime('first day of -2 month')); $res = $this->arrayGroupBy($result, 'date'); $list1 = []; foreach ($res as $key => $value) { if (strtotime($key) >= strtotime($threeMonthsAgo)) { $num = 0; foreach ($value as $kk => $vv) { $num += (int)$vv['google_bid_fee']; } $array = [ 'date' => $key, 'sum' => $num, ]; $list1[] = $array; } } sort($list1); return [ 'xAxis' => array_column($list1, 'date'), 'yAxis' => array_column($list1, 'sum'), ]; } public function lineRedPpcDiff($threeMonthsAgo, $thisMonth) { $list = Site::query()->join('sites_payment', 'sites.id', '=', 'sites_payment.site_id') ->selectRaw('DATE_FORMAT(bid_account_pause_time,"%Y-%m") as date,count(*) as value') ->whereBetween('bid_account_pause_time', [$threeMonthsAgo, $thisMonth]) ->whereJsonContains('contract_ids', "3") ->groupBy('date') ->get()->toArray() ?? []; return [ 'xAxis' => array_column($list, 'date'), 'yAxis' => array_column($list, 'value'), ]; } public function grayPpcDiff($threeMonthsAgo, $thisMonth) { $list = Site::query()->selectRaw('DATE_FORMAT(online_at,"%Y-%m") as date,count(*) as value') ->whereIn('status', [2, 3, 5, 6, 7, 8, 9]) ->whereBetween('online_at', [$threeMonthsAgo, $thisMonth]) ->whereJsonContains('contract_ids', "3")->where('bid_status', 0) ->groupBy('date') ->get()->toArray() ?? []; return [ 'xAxis' => array_column($list, 'date'), 'yAxis' => array_column($list, 'value'), ]; } public function redPpcDiff($threeMonthsAgo, $thisMonth, $type = 0) { $list = Site::query()->join('sites_payment', 'sites.id', '=', 'sites_payment.site_id') ->selectRaw('DATE_FORMAT(bid_account_online_time,"%Y-%m") as date,count(*) as value') ->whereBetween('bid_account_online_time', [$threeMonthsAgo, $thisMonth]) ->whereJsonContains('contract_ids', "3")->where('bid_status', 1) ->groupBy('date') ->get()->toArray() ?? []; if ($type == 1) { $list = Site::query()->join('sites_payment', 'sites.id', '=', 'sites_payment.site_id') ->selectRaw('DATE_FORMAT(bid_account_online_time,"%Y-%m") as date,count(*) as value') ->whereBetween('bid_account_online_time', [$threeMonthsAgo, $thisMonth]) ->whereNull('bid_account_pause_time') ->whereNull('google_bid_fee') ->whereJsonContains('contract_ids', "3")->where('bid_status', 1) ->groupBy('date') ->get()->toArray() ?? []; return $list; } return [ 'xAxis' => array_column($list, 'date'), 'yAxis' => array_column($list, 'value'), ]; } public function ppcDiff($threeMonthsAgo, $thisMonth) { $list = Site::query()->selectRaw('DATE_FORMAT(order_at,"%Y-%m") as date,count(*) as value') ->whereBetween('order_at', [$threeMonthsAgo, $thisMonth]) ->whereJsonContains('contract_ids', "3") ->groupBy('date') ->get()->toArray() ?? []; return [ 'xAxis' => array_column($list, 'date'), 'yAxis' => array_column($list, 'value'), ]; } public function ratio() { $ratio = $this->renewal2(1); //在投放个数三个月 $threeMonthsAgo = date('Y-m-01 00:00:00', strtotime('first day of -2 month')); $thisMonth = date('Y-m-t 23:59:59'); $res = $this->redPpcDiff($threeMonthsAgo, $thisMonth, 1); foreach ($ratio as $key => $value) { foreach ($res as $kk => $vv) { if ($value['date'] == $vv['date']) { $ratio[$key]['ratio'] = number_format($value['count'] / $vv['value'], 2); } } } sort($ratio); return [ 'xAxis' => array_column($ratio, 'date'), 'yAxis' => array_column($ratio, 'ratio'), ]; } //饼状图 private function pie() { $no = Site::query()->selectRaw('id,contract_ids') ->whereJsonContains('contract_ids', "3")->where('bid_status', '<>', 1)->count(); $in = Site::query()->selectRaw('id,contract_ids') ->whereJsonContains('contract_ids', "3")->where('bid_status', 1)->count(); $pause = Site::query()->join('sites_payment', 'sites.id', '=', 'sites_payment.site_id') ->whereNotNull('bid_account_pause_time') ->where('bid_status', '=', 1)->whereJsonContains('contract_ids', "3")->count(); return [ 'xAxis' => ['待投放个数', '投放个数', '已暂停个数'], 'hybrid' => [ ['name' => '待投放个数', 'value' => $no], ['name' => '投放个数', 'value' => $in], ['name' => '已暂停个数', 'value' => $pause], ] ]; } //折线图 private function line() { $monthScope = []; $monthResult = []; for ($i = 0; $i < 6; $i++) { $monthScope[] = date('Y-m', strtotime('first day of -' . $i . ' month')); $monthResult[date('Ym', strtotime('first day of -' . $i . ' month'))] = [ 'begin' => strtotime(date('Y-m-d 00:00:00', strtotime('first day of -' . $i . ' month'))), 'end' => strtotime(date('Y-m-d 23:59:59', strtotime('last day of -' . $i . ' month'))), 'total_fee' => 0 ]; } $where = sprintf("google_bid->'$[*].google_bid_time' REGEXP '%s'", implode("|", $monthScope)); $payRecords = SitePayment::query()->select(['site_id', 'google_bid'])->whereRaw($where)->get(); foreach ($payRecords as $item) { $bids = $item->google_bid; foreach ($bids as $val) { if (!empty($val['google_bid_time'])) { $time = strtotime($val['google_bid_time']); foreach ($monthResult as &$v) { if ($time >= $v['begin'] && $time <= $v['end']) { $v['total_fee'] += floatval($val['google_bid_fee']); } } } } } return [ 'xAxis' => array_reverse(array_keys($monthResult)), 'yAxis' => array_reverse(array_column($monthResult, 'total_fee')) ]; } //续费 private function renewal() { $siteIdList = Site::query()->whereJsonContains('contract_ids', "3")->pluck('id'); $payRecords = SitePayment::query()->whereIn('site_id', $siteIdList)->selectRaw('site_id,google_bid')->whereNotNull('google_bid')->get(); $totalRenewalNum = 0; foreach ($payRecords as $record) { $bids = $record->google_bid; $lastItem = array_pop($bids); if (!empty($lastItem['google_bid_time'])) { $totalRenewalNum++; } } return $totalRenewalNum; } //续费三个月 private function renewal2($type = 0) { $siteIdList = Site::query()->whereJsonContains('contract_ids', "3")->pluck('id'); $payRecords = SitePayment::query()->whereIn('site_id', $siteIdList)->select('site_id', 'google_bid')->whereNotNull('google_bid')->get(); $infos = []; foreach ($payRecords as $record) { foreach ($record->google_bid as $kk => $vv) { $res = [ 'site_id' => $record->site_id, 'google_bid_fee' => $vv['google_bid_fee'], 'google_bid_time' => $vv['google_bid_time'], ]; $infos[] = $res; } } $array = []; foreach ($infos as $kk => $vv) { if (!empty($vv['google_bid_time'])) { $vv['google_bid_date'] = date('Y-m', strtotime($vv['google_bid_time'])); $array[] = $vv; } } $threeMonthsAgo = date('Y-m-01 00:00:00', strtotime('first day of -2 month')); $argc = []; $result = $this->arrayGroupBy($array, 'google_bid_date'); foreach ($result as $key => $value) { //去掉每个月相同项目id $array = $this->arrUniq($value, 'site_id'); foreach ($array as $kk => $vv) { $argc[] = $vv; } } $arrayGroupBy = $this->arrayGroupBy($argc, 'google_bid_date'); $list = []; foreach ($arrayGroupBy as $key => $value) { if (strtotime($key) >= strtotime($threeMonthsAgo)) { $array = [ 'date' => $key, 'count' => count($value), ]; $list[] = $array; } } sort($list); if ($type != 0) { return $list; } return [ 'xAxis' => array_column($list, 'date'), 'yAxis' => array_column($list, 'count'), ]; } //二维数组按照指定键值去重 function arrUniq($arr, $key) { $key_arr = []; foreach ($arr as $k => $v) { if (in_array($v[$key], $key_arr)) { unset($arr[$k]); } else { $key_arr[] = $v[$key]; } } return $arr; } //二维数组GroupBy function arrayGroupBy($arr, $key) { $grouped = array(); foreach ($arr as $value) { $grouped[$value[$key]][] = $value; } if (func_num_args() > 2) { $args = func_get_args(); foreach ($grouped as $key => $value) { $parameter = array_merge($value, array_slice($args, 2, func_num_args())); $grouped[$key] = call_user_func_array('array_group_by', $parameter); } } return $grouped; } private function totalFee() { $totalRecords = Site::query() ->whereJsonContains('contract_ids', "3") ->where('bid_status', '=', 1) ->with(['sitePayment' => function (\Illuminate\Database\Eloquent\Relations\HasOne $builder) { $builder->selectRaw('site_id,bid_give_fee,google_bid'); }])->selectRaw('id,order_at')->get(); $totalTotalFee = 0; foreach ($totalRecords as $record) { $record->bid_give_fee = $record->sitePayment->bid_give_fee ?? ''; $bidGiveFee = floatval($record->bid_give_fee); $gBid = $record->sitePayment->google_bid ?? []; $totalFee = $bidGiveFee; foreach ($gBid as $v) { $tempFee = floatval($v['google_bid_fee'] ?? 0); $totalFee += $tempFee; } $totalTotalFee += $totalFee; } return $totalTotalFee; } private function totalFee2() { $totalRecords = Site::query() ->whereJsonContains('contract_ids', "3") ->where('bid_status', '=', 1) ->with(['sitePayment' => function (\Illuminate\Database\Eloquent\Relations\HasOne $builder) { $builder->selectRaw('site_id,bid_give_fee,google_bid'); }])->selectRaw('id,order_at')->get(); $list = []; foreach ($totalRecords as $record) { $list[] = $record->sitePayment->google_bid; } $array = []; foreach ($list as $key => $value) { if (is_array($value)) { foreach ($value as $kk => $vv) { if (!empty($vv['google_bid_time'])) { $vv['google_bid_date'] = date('Y-m', strtotime($vv['google_bid_time'])); $array[] = $vv; } } } } $threeMonthsAgo = date('Y-m-01 00:00:00', strtotime('first day of -2 month')); $list = []; $result = $this->arrayGroupBy($array, 'google_bid_date'); foreach ($result as $key => $value) { if (strtotime($key) >= strtotime($threeMonthsAgo)) { $num = 0; foreach ($value as $kk => $vv) { $num += (int)$vv['google_bid_fee']; } $array = [ 'date' => $key, 'sum' => $num, ]; $list[] = $array; } } sort($list); return [ 'xAxis' => array_column($list, 'date'), 'yAxis' => array_column($list, 'sum'), ]; } protected function avgScore() { $totalRecords = Site::query()->with(['sitePayment' => function (\Illuminate\Database\Eloquent\Relations\HasOne $builder) { $builder->selectRaw('site_id,bid_score'); }])->selectRaw('id,order_at')->get(); $totalNum = 0; $totalTotalScore = 0; foreach ($totalRecords as $record) { if (!empty($record->sitePayment->bid_score)) { $totalNum++; $totalTotalScore += floatval($record->sitePayment->bid_score); } } $totalAvg = $totalNum == 0 ? 0 : $totalTotalScore / $totalNum; return number_format($totalAvg, 2); } protected function avgScore2() { $totalRecords = Site::query()->with(['sitePayment' => function (\Illuminate\Database\Eloquent\Relations\HasOne $builder) { $builder->selectRaw('site_id,bid_score,bid_score_time'); }])->selectRaw('id,order_at')->get(); $array = []; foreach ($totalRecords as $record) { if (!empty($record->sitePayment->bid_score)) { $data = [ 'bid_score' => $record->sitePayment->bid_score, 'bid_score_time' => $record->sitePayment->bid_score_time, 'date' => date('Y-m', strtotime($record->sitePayment->bid_score_time)), ]; $array[] = $data; } } $threeMonthsAgo = date('Y-m-01 00:00:00', strtotime('first day of -2 month')); $result = $this->arrayGroupBy($array, 'date'); $list = []; foreach ($result as $key => $value) { if (strtotime($key) >= strtotime($threeMonthsAgo)) { $num = 0; $sum = 0; foreach ($value as $kk => $vv) { $num++; $sum += floatval($vv['bid_score']); } $average = $sum / $num; $array = [ 'date' => $key, 'average' => number_format($average, 2), ]; $list[] = $array; } } sort($list); return [ 'xAxis' => array_column($list, 'date'), 'yAxis' => array_column($list, 'average'), ]; } //上线账户 public function online(Request $request) { if (!$request->ajax()) { return view('admin.bid.online'); } $keyword = $request->input('keyword'); $status = $request->input('status'); $payIds = SitePayment::query()->whereNotNull('bid_account_online_time')->pluck('site_id')->toArray(); $records = Site::query() ->where($condition ?? []) ->where(function (Builder $builder) use ($keyword, $status) { if ($keyword) { $builder->where('domain', 'like', '%' . $keyword . '%') ->orWhere('cn_title', 'like', '%' . $keyword . '%'); } if ($status) { $builder->where('status', $status); } }) ->whereIn('id', $payIds)->with(['users', 'sitePayment']) ->get(); foreach ($records as $record) { $record->bidder_title = implode('-', $record->users->where('role_id', Role::TYPE_BID)->pluck('nickname')->toArray()); $record->bid_give_fee = $record->sitePayment->bid_give_fee ?? ''; $record->bid_account_online_time = empty($record->sitePayment->bid_account_online_time) ? '' : date('Y-m-d', strtotime($record->sitePayment->bid_account_online_time)); } $res = collect($records->toArray())->sortByDesc('bid_account_online_time'); $perPage = $request->input('pageSize') ?? TABLE_PAGE_SIZE; $result = $this->paginateCollection($res, $perPage); return response()->json([ 'rows' => $result->items(), 'total' => $result->total(), ]); } //充值账户 public function recharge(Request $request) { if (!$request->ajax()) { return view('admin.bid.recharge'); } $pageSize = $request->input('pageSize') ?? TABLE_PAGE_SIZE; $monthScope = []; for ($i = 0; $i < 36; $i++) { $monthScope[] = date('Y-m', strtotime('first day of -' . $i . ' month')); } $where = sprintf("google_bid->'$[*].google_bid_time' REGEXP '%s'", implode("|", $monthScope)); $site = SitePayment::query(); $keyword = $request->input('keyword'); $status = $request->input('status'); if (!empty($keyword)) { $siteIds = Site::query()->where('domain', 'like', '%' . $keyword . '%') ->orWhere('cn_title', 'like', '%' . $keyword . '%') ->pluck('id'); $site->whereIn('site_id', $siteIds); } if (!empty($status)) { $siteIds = Site::query() ->where('status', $status) ->pluck('id'); $site->whereIn('site_id', $siteIds); } if (!empty($keyword) && !empty($status)) { $siteIds = Site::query()->where('domain', 'like', '%' . $keyword . '%') ->orWhere('cn_title', 'like', '%' . $keyword . '%') ->where('status', $status) ->pluck('id'); $site->whereIn('site_id', $siteIds); } $payRecords = $site->whereNotNull('bid_account_online_time')->select(['site_id', 'google_bid'])->whereRaw($where)->get(); $results = []; foreach ($payRecords as $record) { $bids = $record->google_bid; foreach ($bids as $item) { if (strtotime($item['google_bid_time']) >= strtotime($monthScope[35])) { $results[] = [ 'site_id' => $record->site_id, 'fee' => $item['google_bid_fee'], 'time' => $item['google_bid_time'], ]; } } } $siteIds = array_column($results, 'site_id'); $siteRecords = Site::query() ->with(['users']) ->selectRaw('id,domain,cn_title') ->whereIn('id', $siteIds)->get(); foreach ($siteRecords as $item) { $item->bidder_title = implode('-', $item->users->where('role_id', Role::TYPE_BID)->pluck('nickname')->toArray()); unset($item->users); } $mapRecords = $siteRecords->keyBy('id')->toArray(); foreach ($results as &$record) { $record = array_merge($record, $mapRecords[$record['site_id']] ?? ''); } $res = collect($results)->sortByDesc('time'); $result = $this->paginateCollection($res, $pageSize); return response()->json([ 'rows' => $result->items(), 'total' => $result->total(), ]); } //暂停账户 public function pause(Request $request) { if (!$request->ajax()) { return view('admin.bid.pause'); } $payIds = SitePayment::query()->whereNotNull('bid_account_pause_time')->orderBy('bid_account_pause_time', 'desc')->pluck('site_id')->toArray(); $keyword = $request->input('keyword'); $status = $request->input('status'); $records = Site::query() ->where(function (Builder $builder) use ($keyword, $status) { if ($keyword) { $builder->where('domain', 'like', '%' . $keyword . '%') ->orWhere('cn_title', 'like', '%' . $keyword . '%'); } if ($status) { $builder->where('status', $status); } }) ->whereIn('id', $payIds)->with(['users', 'sitePayment'])->get(); foreach ($records as $record) { $record->bidder_title = implode('-', $record->users->where('role_id', Role::TYPE_BID)->pluck('nickname')->toArray()); $record->bid_give_fee = $record->sitePayment->bid_give_fee ?? ''; $record->bid_account_pause_time = $record->sitePayment->bid_account_pause_time ?? ''; $record->bid_give_fee = $record->sitePayment->bid_give_fee ?? ''; $bidGiveFee = floatval($record->bid_give_fee); $gBid = $record->sitePayment->google_bid ?? []; $totalFee = $bidGiveFee; foreach ($gBid as $v) { $tempFee = floatval($v['google_bid_fee'] ?? 0); $totalFee += $tempFee; } $record->total_fee = $totalFee; } $res = collect($records->toArray())->sortByDesc('bid_account_pause_time'); $perPage = $request->input('pageSize') ?? TABLE_PAGE_SIZE; $result = $this->paginateCollection($res, $perPage); return response()->json([ 'rows' => $result->items(), 'total' => count($res), ]); } //客户评分 public function score(Request $request) { if (!$request->ajax()) { return view('admin.bid.score'); } $payIds = SitePayment::query()->whereNotNull('bid_score_time')->orderBy('bid_score_time', 'desc')->pluck('site_id'); $keyword = $request->input('keyword'); $status = $request->input('status'); $records = Site::query() ->whereIn('id', $payIds) ->with(['users', 'sitePayment']) ->where(function (Builder $builder) use ($keyword, $status) { if ($keyword) { $builder->where('domain', 'like', '%' . $keyword . '%') ->orWhere('cn_title', 'like', '%' . $keyword . '%'); } if ($status) { $builder->where('status', $status); } })->get(); foreach ($records as $record) { $record->bidder_title = implode('-', $record->users->where('role_id', Role::TYPE_BID)->pluck('nickname')->toArray()); $record->bid_score = $record->sitePayment->bid_score ?? ''; $record->bid_score_time = $record->sitePayment->bid_score_time ?? ''; } $res = collect($records->toArray())->sortByDesc('score'); $perPage = $request->input('pageSize') ?? TABLE_PAGE_SIZE; $result = $this->paginateCollection($res, $perPage); return response()->json([ 'rows' => $result->items(), 'total' => $result->total(), ]); } }