| 1234567891011121314151617181920212223242526272829303132333435363738394041 |
- <?php
- namespace App\Services;
- use Illuminate\Support\Facades\DB;
- /** 会员列表“查看”弹窗里的三项历史合计:总充值、总提现、总赠送彩金。全部来自 balance_logs。 */
- final class MemberFundTotalsService
- {
- /** 前台充值(公司入款、USDT)和三方充值 */
- public const DEPOSIT_TYPES = ['充值', '三方充值'];
- /** 后台加款里算充值的:人工充值(related_id = 0 的是活动赠送,算彩金)和其他 */
- public const MANUAL_DEPOSIT_TYPES = ['其他'];
- /** 会员提现:申请时扣款为负、失败退款为正,相加就是净提现 */
- public const WITHDRAW_TYPES = ['提现', '三方提现', '人工提现'];
- /** 赠送彩金。转盘、净亏邀请、邀请升级、充值渠道赠送、首充奖励入账时都记为“优惠活动” */
- public const BONUS_TYPES = ['注册赠送', '优惠活动', '神秘彩金', '即充即送', '充值返现', '老用户回归',
- '比比返', '笔笔返', '返水', '回水', '分享佣金领取'];
- /** @return array{total_deposit:string,total_withdraw:string,total_bonus:string} */
- public static function totals(string $memberId): array
- {
- $in = fn (array $types) => "'" . implode("','", $types) . "'";
- $deposit = $in(self::DEPOSIT_TYPES);
- $manual = $in(self::MANUAL_DEPOSIT_TYPES);
- $withdraw = $in(self::WITHDRAW_TYPES);
- $bonus = $in(self::BONUS_TYPES);
- $row = DB::table('balance_logs')->where('member_id', $memberId)
- ->whereIn('change_type', array_merge(self::DEPOSIT_TYPES, self::MANUAL_DEPOSIT_TYPES, self::WITHDRAW_TYPES, self::BONUS_TYPES, ['人工充值', '人工扣款']))
- ->selectRaw(<<<SQL
- COALESCE(SUM(CASE WHEN amount > 0 AND (change_type IN ({$deposit}, {$manual})
- OR (change_type = '人工充值' AND (related_id IS NULL OR related_id <> 0))) THEN amount ELSE 0 END), 0) AS total_deposit,
- COALESCE(SUM(CASE WHEN change_type IN ({$withdraw}) THEN -amount
- WHEN change_type = '人工扣款' AND amount < 0 THEN -amount ELSE 0 END), 0) AS total_withdraw,
- COALESCE(SUM(CASE WHEN amount > 0 AND (change_type IN ({$bonus})
- OR (change_type = '人工充值' AND related_id = 0)) THEN amount ELSE 0 END), 0) AS total_bonus
- SQL)->first();
- $money = fn ($value) => number_format(max(0, (float) $value), 2, '.', '');
- return ['total_deposit' => $money($row->total_deposit), 'total_withdraw' => $money($row->total_withdraw), 'total_bonus' => $money($row->total_bonus)];
- }
- }
|