where('member_id', $memberId)->first(['id', 'member_id', 'level', 'username', 'first_name']); } /** Category totals for one New York day: bet (non-void), effective and profit (settled only). */ public static function rebateBasis(object $user, string $nyDate): array { $start = Carbon::parse($nyDate . ' 00:00:00', 'America/New_York')->setTimezone('Asia/Shanghai'); $end = $start->copy()->addDay(); $query = self::factsFor($user) ->whereBetween('bet_date', [$start->toDateString(), $end->toDateString()]) ->where('bet_at', '>=', $start->format('Y-m-d H:i:s'))->where('bet_at', '<', $end->format('Y-m-d H:i:s')); $basis = []; foreach (self::totals($query, ['category']) as $row) $basis[$row->category] = self::amounts($row); return $basis; } /** Category totals for one Asia/Shanghai month (Y-m). */ public static function backflowBasis(object $user, string $month): array { $start = Carbon::parse($month . '-01', 'Asia/Shanghai'); // Historical facts identify a child bet. A missing or duplicated parent must not attribute its loss to a game. $orderType = DB::table('lhc_order_item') ->join('lhc_order', 'lhc_order.ordernum', '=', 'lhc_order_item.ordernum') ->whereColumn('lhc_order_item.id', 'share_bet_facts.source_id') ->whereColumn('lhc_order_item.member_id', 'share_bet_facts.member_id'); $grammar = $orderType->getGrammar(); $type = $grammar->wrap('lhc_order.type'); $orderMember = $grammar->wrap('lhc_order.member_id'); $itemMember = $grammar->wrap('lhc_order_item.member_id'); $orderType->selectRaw("CASE WHEN COUNT(*) = 1 AND MIN($orderMember) = MIN($itemMember) AND MIN($type) BETWEEN 1 AND 13 THEN MIN($type) END"); $facts = self::factsFor($user) ->whereBetween('bet_date', [$start->toDateString(), $start->copy()->endOfMonth()->toDateString()]) ->select('category', 'bet_amount', 'effective_amount') ->selectRaw('CASE WHEN category = ? AND settled = 0 THEN 0 ELSE net_profit END AS net_profit', ['self:lottery']) ->selectRaw("CASE WHEN category = ? AND source = ? THEN ({$orderType->toSql()}) END AS lottery_order_type", ['self:lottery', 'lhc_order_item']); $basis = []; foreach (self::totals(DB::query()->fromSub($facts, 'monthly_facts'), ['category', 'lottery_order_type']) as $row) { $amounts = self::amounts($row); $category = $row->category; if ($row->lottery_order_type !== null) { $type = (int) $row->lottery_order_type; // lhc_order uses 1–4 for Mark Six, 5–6 for the two 28 games, and 7–13 for jisu types 1–7. $category = match (true) { $type <= 4 => 'self:lottery:lhc:' . $type, $type === 5 => 'self:lottery:pc28', $type === 6 => 'self:lottery:jisu28', default => 'self:lottery:jisu:' . ($type - 6), }; $amounts['label'] = LhcOrder::getRemark($type); } $basis[$category] = $amounts; } return $basis; } /** Highest tier of the member's level whose threshold the category's effective bet reaches. */ public static function rebateReward(array $basis, int $level, ?array $tiers = null): array { $tiers ??= RewardRuleService::tiers('rebate'); $result = self::empty($basis); foreach ($basis as $category => $row) { $detail = self::detail($category, $row) + ['level' => $level]; if (bccomp($row['effective_amount'], '0', 2) > 0) { foreach ($tiers[$category] ?? [] as $tier) { if ($tier['level'] !== $level || bccomp($row['effective_amount'], $tier['min_effective_amount'], 2) < 0) continue; $amount = bcmul($row['effective_amount'], bcdiv($tier['percentage'], '100', 8), 2); $detail = self::matched($detail, $tier, $amount) + ['min_effective_amount' => $tier['min_effective_amount'], 'percentage' => $tier['percentage']]; break; } } $result = self::add($result, $detail); } return $result; } /** Highest tier whose loss threshold the category's monthly net loss reaches; the reward is a fixed amount. */ public static function backflowReward(array $basis, ?array $tiers = null, ?array $labels = null): array { $tiers ??= RewardRuleService::tiers('backflow'); $result = self::empty($basis) + ['loss_amount' => '0.00']; foreach ($basis as $category => $row) { $loss = bccomp($row['profit'], '0', 2) < 0 ? bcsub('0', $row['profit'], 2) : '0.00'; $labels ??= RewardRuleService::categories('backflow'); $label = $category === 'self:lottery' ? '未识别彩种' : ($labels[$category] ?? $row['label'] ?? $category); $detail = self::detail($category, $row, $label) + ['loss_amount' => $loss]; if (bccomp($loss, '0', 2) > 0) { $result['loss_amount'] = bcadd($result['loss_amount'], $loss, 2); $categoryTiers = $category === 'self:lottery' ? [] : ($tiers[$category] ?? []); foreach ($categoryTiers as $tier) { if (bccomp($loss, $tier['min_loss_amount'], 2) < 0) continue; $detail = self::matched($detail, $tier, $tier['reward_amount']) + ['min_loss_amount' => $tier['min_loss_amount']]; break; } } $result = self::add($result, $detail); } return $result; } /** Fresh rebate result for a stored row; legacy PC28 days without any facts keep their stored effective bet. */ public static function rebate(object $reward): array { $user = self::user((string) $reward->member_id); $basis = $user ? self::rebateBasis($user, (string) $reward->date) : []; if ($basis === [] && bccomp((string) $reward->effective_betting_amount, '0', 2) > 0) { $basis = ['self:lottery' => ['bet_amount' => self::money($reward->betting_amount), 'effective_amount' => self::money($reward->effective_betting_amount), 'profit' => self::money($reward->profit ?? 0)]]; } return self::rebateReward($basis, (int) ($user->level ?? 0)); } public static function backflow(object $reward): array { $user = self::user((string) $reward->member_id); return self::backflowReward($user ? self::backflowBasis($user, (string) $reward->date) : []); } private static function factsFor(object $user): Builder { $currency = strtoupper(trim((string) config('third_game.currency', 'CNY'))); return DB::table('share_bet_facts')->where('user_id', $user->id)->where('voided', 0)->where('currency', $currency); } private static function totals(Builder $query, array $groups): Collection { $query->select($groups) ->selectRaw('SUM(bet_amount) AS bet_amount, SUM(effective_amount) AS effective_amount, SUM(net_profit) AS profit') ->groupBy($groups); foreach ($groups as $group) $query->orderBy($group); return $query->get(); } private static function amounts(object $row): array { return ['bet_amount' => self::money($row->bet_amount), 'effective_amount' => self::money($row->effective_amount), 'profit' => self::money($row->profit)]; } private static function empty(array $basis): array { return ['betting_amount' => '0.00', 'effective_amount' => '0.00', 'profit' => '0.00', 'amount' => '0.00', 'need_flow' => '0.00', 'details' => [], 'has_facts' => $basis !== []]; } private static function detail(string $category, array $row, ?string $label = null): array { return ['category' => $category, 'label' => $label ?? RewardRuleService::categories()[$category] ?? $category, 'bet_amount' => $row['bet_amount'], 'effective_amount' => $row['effective_amount'], 'profit' => $row['profit'], 'rule_id' => null, 'turnover_multiple' => '0.00', 'amount' => '0.00', 'need_flow' => '0.00']; } private static function matched(array $detail, array $tier, string $amount): array { return array_merge($detail, ['rule_id' => $tier['id'], 'turnover_multiple' => $tier['turnover_multiple'], 'amount' => $amount, 'need_flow' => bcmul($amount, $tier['turnover_multiple'], 2)]); } private static function add(array $result, array $detail): array { $result['betting_amount'] = bcadd($result['betting_amount'], $detail['bet_amount'], 2); $result['effective_amount'] = bcadd($result['effective_amount'], $detail['effective_amount'], 2); $result['profit'] = bcadd($result['profit'], $detail['profit'], 2); $result['amount'] = bcadd($result['amount'], $detail['amount'], 2); $result['need_flow'] = bcadd($result['need_flow'], $detail['need_flow'], 2); $result['details'][] = $detail; return $result; } /** SUM() is an exact string on MySQL but may be a float on SQLite. */ private static function money($value): string { if ($value === null || $value === '') return '0.00'; if (is_float($value) || is_int($value)) $value = number_format((float) $value, 10, '.', ''); return bcadd(EffectiveBet::decimal($value), '0', 2); } }