toDateString(); $start = $day . ' 00:00:00'; $end = $day . ' 23:59:59'; $byUser = []; $byMember = []; foreach (DB::table('share_bet_facts')->where('settled', 1)->where('voided', 0)->whereBetween('settled_at', [$start, $end]) ->groupBy('user_id', 'category') ->selectRaw('user_id, category, COUNT(*) bet_count, COALESCE(SUM(bet_amount),0) bet_amount, COALESCE(SUM(effective_amount),0) valid_bet_amount, COALESCE(SUM(net_profit),0) win_loss') ->get() as $row) { $item = &$byUser[(int) $row->user_id]; $item['bet_count'] = ($item['bet_count'] ?? 0) + (int) $row->bet_count; foreach (['bet_amount', 'valid_bet_amount', 'win_loss'] as $field) { $item[$field] = bcadd($item[$field] ?? '0', (string) $row->{$field}, 4); } $item['categories'][(string) $row->category] = [ 'name' => ShareDefaults::categories()[(string) $row->category] ?? (string) $row->category, 'bet_count' => (int) $row->bet_count, 'bet_amount' => bcadd((string) $row->bet_amount, '0', 4), 'valid_bet_amount' => bcadd((string) $row->valid_bet_amount, '0', 4), 'win_loss' => bcadd((string) $row->win_loss, '0', 4), ]; unset($item); } if (Schema::hasTable('agent_profit_entries')) { foreach (DB::table('agent_profit_entries')->where('status', 'counted')->where('settle_date', $day) ->groupBy('user_id')->selectRaw('user_id, COALESCE(SUM(share_amount),0) total')->get() as $row) { $byUser[(int) $row->user_id]['agent_share'] = bcadd((string) $row->total, '0', 4); } } $this->addMoney($byMember, DB::table('recharges')->where('status', 1)->whereBetween('created_at', [$start, $end]), 'deposit_amount'); $this->addMoney($byMember, DB::table('withdraws')->where('status', 1)->whereBetween('created_at', [$start, $end]), 'withdraw_amount'); if (Schema::hasTable('payment_orders')) { $orders = fn () => DB::table('payment_orders')->where('status', PaymentOrderService::STATUS_SUCCESS)->whereBetween('created_at', [$start, $end]); $this->addMoney($byMember, $orders()->whereIn('type', [PaymentOrderService::TYPE_PAY, PaymentOrderService::TYPE_SELF_PAY]), 'deposit_amount'); $this->addMoney($byMember, $orders()->whereIn('type', [PaymentOrderService::TYPE_PAYOUT, PaymentOrderService::TYPE_SELF_PAYOUT]), 'withdraw_amount'); } foreach (DB::table('balance_logs')->whereBetween('created_at', [$start, $end])->groupBy('member_id') ->selectRaw(<<<'SQL' member_id, COALESCE(SUM(CASE WHEN ((change_type = '人工充值' AND related_id IS NULL) OR change_type = '订单补偿') AND amount > 0 THEN amount ELSE 0 END), 0) AS manual_credit, COALESCE(SUM(CASE WHEN change_type = '人工扣款' AND amount < 0 THEN ABS(amount) ELSE 0 END), 0) AS manual_debit, COALESCE(SUM(CASE WHEN (change_type IN ('注册赠送','优惠活动','即充即送','充值返现','老用户回归') OR (change_type = '人工充值' AND related_id = 0)) AND amount > 0 THEN amount ELSE 0 END), 0) AS bonus_amount, COALESCE(SUM(CASE WHEN change_type IN ('比比返','笔笔返','返水','回水') AND amount > 0 THEN amount ELSE 0 END), 0) AS rebate_amount SQL)->get() as $row) { foreach (['manual_credit', 'manual_debit', 'bonus_amount', 'rebate_amount'] as $field) { if (bccomp((string) $row->{$field}, '0', 4) === 0) continue; $byMember[(string) $row->member_id][$field] = bcadd($byMember[(string) $row->member_id][$field] ?? '0', (string) $row->{$field}, 4); } } $users = collect(); foreach (array_chunk(array_keys($byUser), 500) as $ids) { $users = $users->merge(DB::table('users')->whereIn('id', $ids)->get(['id', 'member_id', 'agent_id'])); } foreach (array_chunk(array_map('strval', array_keys($byMember)), 500) as $memberIds) { $users = $users->merge(DB::table('users')->whereIn('member_id', $memberIds)->get(['id', 'member_id', 'agent_id'])); } $rows = []; $now = now(); foreach ($users->unique('id') as $user) { $values = array_merge($byUser[(int) $user->id] ?? [], $byMember[(string) $user->member_id] ?? []); $row = ['report_date' => $day, 'user_id' => (int) $user->id, 'member_id' => (string) $user->member_id, 'agent_id' => $user->agent_id ? (int) $user->agent_id : null, 'bet_count' => (int) ($values['bet_count'] ?? 0), 'categories' => json_encode($values['categories'] ?? new \stdClass(), JSON_UNESCAPED_UNICODE), 'created_at' => $now, 'updated_at' => $now]; foreach (self::MONEY_FIELDS as $field) $row[$field] = bcadd($values[$field] ?? '0', '0', 4); $rows[] = $row; } DB::transaction(function () use ($day, $rows) { DB::table('member_daily_reports')->where('report_date', $day)->delete(); foreach (array_chunk($rows, 500) as $chunk) DB::table('member_daily_reports')->insert($chunk); }); return count($rows); } /** 总后台查看全部会员;代理端传入 $agent 只看自己的会员。 */ public function paginate(array $params, ?Agent $agent = null): array { $page = max(1, (int) ($params['page'] ?? 1)); $limit = min(100, max(1, (int) ($params['limit'] ?? 20))); $startDate = (string) ($params['start_date'] ?? now()->subDays(6)->toDateString()); $endDate = (string) ($params['end_date'] ?? now()->toDateString()); if (Carbon::parse($startDate)->diffInDays(Carbon::parse($endDate)) > 92) { throw new \InvalidArgumentException('单次查询不能超过92天'); } $query = MemberDailyReport::query()->from('member_daily_reports as r') ->leftJoin('users as u', 'u.id', '=', 'r.user_id') ->leftJoin('agents as a', 'a.id', '=', 'r.agent_id') ->whereBetween('r.report_date', [$startDate, $endDate]); if ($agent) { $query->where('r.agent_id', $agent->id); } else { if (!empty($params['agent_id'])) $query->where('r.agent_id', (int) $params['agent_id']); if (!empty($params['agent_type'])) $query->where('a.type', (int) $params['agent_type']); } if (!empty($params['member_id'])) $query->where('r.member_id', (string) $params['member_id']); if (!empty($params['username'])) $query->where('u.username', 'like', '%' . $params['username'] . '%'); // 原生 SQL 中的别名也要带表前缀(r -> bot_r),交给 grammar 包裹。 $grammar = DB::connection()->getQueryGrammar(); $summary = (clone $query)->selectRaw('COUNT(*) row_count, COALESCE(SUM(' . $grammar->wrap('r.bet_count') . '),0) bet_count, ' . implode(', ', array_map(fn ($field) => 'COALESCE(SUM(' . $grammar->wrap("r.{$field}") . "),0) {$field}", self::MONEY_FIELDS))) ->toBase()->first(); $total = (int) ($summary->row_count ?? 0); $list = $query->select('r.*', 'u.username', 'u.first_name', 'a.username as agent_username', 'a.type as agent_type') ->orderByDesc('r.report_date')->orderByDesc('r.id')->forPage($page, $limit)->get(); $totals = ['bet_count' => (int) ($summary->bet_count ?? 0)]; foreach (self::MONEY_FIELDS as $field) $totals[$field] = bcadd((string) ($summary->{$field} ?? 0), '0', 4); return compact('total', 'page', 'limit', 'totals', 'list'); } private function addMoney(array &$byMember, $query, string $field): void { foreach ($query->groupBy('member_id')->selectRaw('member_id, COALESCE(SUM(amount),0) total')->get() as $row) { $byMember[(string) $row->member_id][$field] = bcadd($byMember[(string) $row->member_id][$field] ?? '0', (string) $row->total, 4); } } }