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

<?php
/**
 * 竞价管理
 * @copyright 2021-浙江引擎力营销策划有限公司
 * @author Lc<sunshinecc1@163.com>
 * @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(),
        ]);

    }

}