MemberFundTotalsService.php 2.6 KB

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