bot-28/app/Http/Controllers/admin/Operation.php

<?php

namespace App\Http\Controllers\admin;

use App\Http\Controllers\Controller;
use Exception;
use App\Models\User;
use App\Models\FundsRecord;
use App\Models\Order;
use App\Models\Operation as OperationModel;
use App\Constants\HttpStatus;
use App\Services\PlayedGameService;
use Carbon\Carbon;

class Operation extends Controller
{
    /**
     * 用户报表
     */
    function exchangeList()
    {
        try {
            $params = request()->validate([
                'page' => ['nullable', 'integer', 'min:1'],
                'limit' => ['nullable', 'integer', 'min:1', 'max:200'],
                'start_time' => ['nullable', 'date', 'date_format:Y-m-d', 'required_with:end_time'],
                'end_time' => ['nullable', 'date', 'date_format:Y-m-d', 'required_with:start_time'],
                'user_id' => ['nullable', 'string'],
                'member_id' => ['nullable'],
                'account' => ['nullable', 'string', 'max:64'],
                'first_name' => ['nullable'],
                'sort_field' => ['nullable', 'string', 'in:recharge,withdraw,withdraw_recharge_difference,money'],
                'sort_order' => ['nullable', 'string', 'in:asc,desc'],
            ]);
            $page = request()->input('page', 1);
            $limit = request()->input('limit', 15);
            $query = User::where('users.from','<>', 2);
            if (isset($params['user_id'])) {
                $user_id = $params['user_id'] ?? null;
                $query->where(function ($query1) use ($user_id) {
                    $query1->where('users.member_id', $user_id)
                    ->orWhere('users.first_name', 'like', "%{$user_id}%");
                });
            }
            if (!empty($params['member_id'])) {
                $query = $query->where('users.member_id', $params['member_id']);
            }
            if (!empty($params['account'])) {
                $query = $query->where('users.account', 'like', '%' . trim((string)$params['account']) . '%');
            }
            if (!empty($params['first_name'])) {
                $query = $query->where('users.first_name', 'like', "%{$params['first_name']}%");
            }

            $start = !empty($params['start_time']) ? "{$params['start_time']} 00:00:00" : null;
            $end = !empty($params['end_time']) ? "{$params['end_time']} 23:59:59" : null;
            $query->join('wallets', 'users.member_id', '=', 'wallets.member_id');
            $count = (clone $query)->count();

            // Aggregate before pagination so sorting covers all filtered members, using numeric amounts.
            $funds = FundsRecord::query()->select('member_id')
                ->whereIn('member_id', (clone $query)->select('users.member_id'))
                ->whereIn('change_type', ['充值', '人工充值', '三方充值', '提现', '三方提现'])
                ->selectRaw("SUM(CASE WHEN change_type IN ('充值', '人工充值', '三方充值') THEN amount ELSE 0 END) AS recharge_total")
                ->selectRaw("SUM(CASE WHEN change_type IN ('提现', '三方提现') THEN amount ELSE 0 END) AS withdraw_change")
                ->groupBy('member_id');
            if ($start && $end) $funds->whereBetween('created_at', [$start, $end]);

            $grammar = $query->getQuery()->getGrammar();
            $recharge = 'COALESCE(' . $grammar->wrap('exchange_funds.recharge_total') . ', 0)';
            $withdrawChange = 'COALESCE(' . $grammar->wrap('exchange_funds.withdraw_change') . ', 0)';
            // 提现流水为负、退款为正；盈亏 = 充值 - 提现净额 = 充值 + 提现流水合计。
            $query->leftJoinSub($funds, 'exchange_funds', 'users.member_id', '=', 'exchange_funds.member_id')
                ->select(['users.id', 'users.user_id', 'users.member_id', 'users.account', 'users.first_name', 'wallets.available_balance as money'])
                ->selectRaw("{$recharge} AS recharge, -({$withdrawChange}) AS withdraw, {$recharge} + {$withdrawChange} AS withdraw_recharge_difference");
            if (!empty($params['sort_field'])) {
                $query->orderBy($params['sort_field'], $params['sort_order'] ?? 'desc');
            }
            $list = $query->orderByDesc('users.created_at')->orderByDesc('users.id')
                ->forPage($page, $limit)->get()->toArray();

            $playedGames = PlayedGameService::forMembers(array_column($list, 'member_id'), $start, $end);
            $startTimestamp = $start ? Carbon::parse($start, 'Asia/Shanghai')->timestamp : null;
            $endTimestamp = $end ? Carbon::parse($end, 'Asia/Shanghai')->timestamp : null;

            foreach ($list as &$item) {
                $item['recharge'] = number_format($item['recharge'], 2);
                $item['withdraw'] = number_format($item['withdraw'], 2);
                $item['withdraw_recharge_difference'] = number_format($item['withdraw_recharge_difference'], 2);
                $item['played_games'] = $playedGames[$item['member_id']] ?? [];
                //订单总额
                $item['order_amount'] = number_format(Order::where('user_id', $item['user_id'])
                    ->where('status', 1)
                    ->where('pay_status', 1)
                    ->where('return_status', 0)
                    ->where(function ($query1) use ($startTimestamp, $endTimestamp) {
                        if ($startTimestamp && $endTimestamp) {
                            $query1->where('create_time', '>=', $startTimestamp)
                                ->where('create_time', '<=', $endTimestamp);
                        }
                    })
                    ->sum('amount'), 2);
            }
        } catch (Exception $e) {
            return $this->error(HttpStatus::CUSTOM_ERROR, $e->getMessage());
        }
        return $this->success(['total' => $count, 'data' => $list]);

    }

    /**
     * 运营数据
     * @apiParam {String{'day','week','month','all'}} type 类型
     * - day 今日
     * - week 本周
     * - month 本月
     * - all 全部
     */
    function index()
    {
        try {
            $params = request()->validate([
                'page' => ['nullable', 'integer', 'min:1'],
                'limit' => ['nullable', 'integer', 'min:1', 'max:200'],
                'type' => ['required', 'string', 'in:day,week,month,all'],
                'start_time' => ['nullable', 'date', 'date_format:Y-m-d', 'required_with:end_time'],
                'end_time' => ['nullable', 'date', 'date_format:Y-m-d', 'required_with:start_time'],
            ]);
            $data = [
                'recharge' => 0,
                'withdraw' => 0,
                'total_price' => 0,
                'profit_price' => 0
            ];
            $page = request()->input('page', 1);
            $limit = request()->input('limit', 15);
            $query = OperationModel::query();
            $query1 = OperationModel::query();
            if (!empty($params['start_time'])) {
                $query->where('date', '>=', $params['start_time'])
                    ->where('date', '<=', $params['end_time']);
                $query1->where('date', '>=', $params['start_time'])
                    ->where('date', '<=', $params['end_time']);
            } else if ($params['type'] != 'all') {
                switch ($params['type']) {
                    case 'day':
                        $date = date('Y-m-d');
                        break;
                    case "week":
                        $date = date('Y-m-d', strtotime('this week'));
                        break;
                    case "month":
                        $date = date('Y-m-d', strtotime('this month'));
                        break;
                }
                $query->where('date', '>=', $date);
                $query1->where('date', '>=', $date);
            }
            $data['recharge'] = $query1->sum('recharge');
            $data['withdraw'] = $query1->sum('withdraw');
            $data['total_price'] = $query1->sum('total_price');
            $count = $query->count();
            $list = $query
                ->forPage($page, $limit)
                ->orderByDesc('date')
                ->get()->toArray();


        } catch (Exception $e) {
            return $this->error(HttpStatus::CUSTOM_ERROR, $e->getMessage());
        }
        return $this->success(['total' => $count, 'data' => $list, 'count' => $data]);
    }
}