FundsSummaryService.php 18 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372
  1. <?php
  2. namespace App\Services;
  3. use Carbon\Carbon;
  4. use App\Support\StatsScope;
  5. use Illuminate\Support\Facades\DB;
  6. use Illuminate\Support\Facades\Schema;
  7. class FundsSummaryService
  8. {
  9. public static function summary(?string $startDate = null, ?string $endDate = null): array
  10. {
  11. [$start, $end] = $startDate !== null && $endDate !== null
  12. ? self::dateRange($startDate, $endDate)
  13. : [null, null];
  14. $balance = self::balanceAggregates($start, $end);
  15. $withdraw = self::withdrawAggregates($start, $end);
  16. $paymentOrder = self::paymentOrderAggregates($start, $end);
  17. $combinedPeople = self::combinedPeople($start, $end);
  18. $agentFees = self::agentFeeAggregates($start, $end);
  19. $commission = app(ThirdGameCommissionService::class)->summary($start?->toDateTimeString(), $end?->toDateTimeString());
  20. $income = [
  21. self::item('company_deposit', '公司入款', $balance->company_deposit_amount, $balance->company_deposit_people),
  22. self::item(
  23. 'third_party_deposit',
  24. '第三方支付(入款)',
  25. $balance->third_party_deposit_amount,
  26. $balance->third_party_deposit_people
  27. ),
  28. self::item('manual_credit', '人工上分', $balance->manual_credit_amount, $balance->manual_credit_people),
  29. self::item(
  30. 'member_withdraw_deduction',
  31. '会员出款扣除',
  32. // 只有 USDT 提现向会员收手续费;三方/人工提现的 fee 是平台成本,不扣会员。
  33. $withdraw->member_withdraw_deduction_amount,
  34. $combinedPeople['member_withdraw_deduction']
  35. ),
  36. ];
  37. $expense = [
  38. self::item(
  39. 'member_withdrawal',
  40. '会员出款',
  41. $withdraw->member_withdrawal_amount,
  42. $withdraw->member_withdrawal_people
  43. ),
  44. self::item(
  45. 'manual_withdrawal',
  46. '人工出款',
  47. (float)$balance->manual_debit_amount + (float)$paymentOrder->manual_withdrawal_amount,
  48. $combinedPeople['manual_withdrawal']
  49. ),
  50. self::item('vip_discount', '给予优惠(VIP 折扣)', 0, 0),
  51. self::item('rebate', '给予返点', $balance->rebate_amount, $balance->rebate_people),
  52. self::item('bonus', '优惠金', $balance->bonus_amount, $balance->bonus_people),
  53. // 下面几项手续费都是三方按通道费率向平台收的,平台承担,不扣会员/代理。
  54. self::item(
  55. 'third_party_fee',
  56. '第三方支付(入款)手续费',
  57. $paymentOrder->third_party_fee_amount,
  58. $paymentOrder->third_party_fee_people
  59. ),
  60. self::item(
  61. 'third_party_auto_withdrawal',
  62. '第三方支付出款(自动出款)',
  63. $paymentOrder->auto_withdrawal_amount,
  64. $paymentOrder->auto_withdrawal_people
  65. ),
  66. self::item(
  67. 'third_party_auto_withdrawal_fee',
  68. '第三方支付自动出款手续费',
  69. $paymentOrder->auto_withdrawal_fee_amount,
  70. $paymentOrder->auto_withdrawal_fee_people
  71. ),
  72. self::item('agent_deposit_channel_fee', '代理三方充值手续费', $agentFees->deposit_fee, $agentFees->deposit_fee_people),
  73. self::item('agent_payout_channel_fee', '代理三方提现手续费', $agentFees->channel_fee, $agentFees->channel_fee_people),
  74. ['key' => 'third_game_commission', 'label' => '第三方游戏平台抽成',
  75. 'amount' => $commission['amount'] === null ? null : self::commissionMoney($commission['amount']),
  76. 'people' => $commission['people']],
  77. ];
  78. return [
  79. 'start_date' => $startDate,
  80. 'end_date' => $endDate,
  81. 'income' => $income,
  82. 'expense' => $expense,
  83. 'income_total' => self::sumItems($income),
  84. 'expense_total' => $commission['complete'] ? self::sumItems($expense) : null,
  85. 'known_expense_total' => bcadd(self::sumItems($expense),
  86. $commission['complete'] ? '0.00' : self::commissionMoney($commission['known_amount']), 2),
  87. 'third_game_net_profit' => $commission['net_profit'] === null ? null : self::commissionMoney($commission['net_profit']),
  88. 'third_game_commission_meta' => $commission,
  89. 'fee_summary' => self::feeSummary($withdraw, $paymentOrder, $agentFees, $start, $end),
  90. ];
  91. }
  92. /**
  93. * 手续费盈亏:收入是向客户收的手续费(会员 USDT 提现、代理提现),成本是三方按通道费率向平台收的手续费。
  94. * 代理提现的历史订单没有记录三方成本(channel_fee 为 NULL),计入 unrecorded_count。
  95. */
  96. private static function feeSummary(object $withdraw, object $paymentOrder, object $agentFees, ?Carbon $start, ?Carbon $end): array
  97. {
  98. $income = [
  99. self::item('member_usdt_withdraw_fee', '会员USDT提现手续费', $withdraw->member_withdraw_deduction_amount,
  100. self::successfulWithdrawLogs($start, $end)->where('w.service_charge', '>', 0)->distinct()->count('bl.member_id')),
  101. self::item('agent_withdraw_fee', '代理提现手续费', $agentFees->withdraw_fee, $agentFees->withdraw_fee_people),
  102. ];
  103. $cost = [
  104. self::item('member_deposit_channel_fee', '会员三方充值手续费', $paymentOrder->third_party_fee_amount, $paymentOrder->third_party_fee_people),
  105. self::item('member_payout_channel_fee', '会员三方提现手续费', $paymentOrder->auto_withdrawal_fee_amount, $paymentOrder->auto_withdrawal_fee_people),
  106. self::item('agent_deposit_channel_fee', '代理三方充值手续费', $agentFees->deposit_fee, $agentFees->deposit_fee_people),
  107. self::item('agent_payout_channel_fee', '代理三方提现手续费', $agentFees->channel_fee, $agentFees->channel_fee_people),
  108. ];
  109. $incomeTotal = self::sumItems($income);
  110. $costTotal = self::sumItems($cost);
  111. return [
  112. 'income' => $income,
  113. 'cost' => $cost,
  114. 'income_total' => $incomeTotal,
  115. 'cost_total' => $costTotal,
  116. 'net' => bcsub($incomeTotal, $costTotal, 2),
  117. 'unrecorded_agent_payout_count' => (int)$agentFees->unrecorded,
  118. ];
  119. }
  120. /**
  121. * 代理侧手续费:代理在线充值的三方手续费(平台成本)、代理提现向代理收的手续费(收入)和三方代付成本(平台成本)。
  122. * 代理提现的历史订单没有记录三方成本(channel_fee 为 NULL),计入 unrecorded。
  123. */
  124. private static function agentFeeAggregates(?Carbon $start, ?Carbon $end): object
  125. {
  126. $result = ['deposit_fee' => 0, 'deposit_fee_people' => 0, 'withdraw_fee' => 0, 'withdraw_fee_people' => 0,
  127. 'channel_fee' => 0, 'channel_fee_people' => 0, 'unrecorded' => 0];
  128. if (Schema::hasTable('agent_payment_orders')) {
  129. $deposit = self::applyDateRange(StatsScope::excludeTestAgents(DB::table('agent_payment_orders'))
  130. ->where('type', PaymentOrderService::TYPE_PAY)
  131. ->where('status', PaymentOrderService::STATUS_SUCCESS)
  132. ->where('fee', '>', 0), 'created_at', $start, $end)
  133. ->selectRaw('COALESCE(SUM(fee), 0) AS deposit_fee, COUNT(DISTINCT agent_id) AS deposit_fee_people')
  134. ->first();
  135. $result = array_merge($result, (array)$deposit);
  136. }
  137. if (Schema::hasTable('agent_withdrawals') && Schema::hasColumn('agent_withdrawals', 'channel_fee')) {
  138. $withdraw = self::applyDateRange(StatsScope::excludeTestAgents(DB::table('agent_withdrawals'))->where('status', 'paid'), 'paid_at', $start, $end)
  139. ->selectRaw(<<<'SQL'
  140. COALESCE(SUM(fee), 0) AS withdraw_fee,
  141. COUNT(DISTINCT CASE WHEN fee > 0 THEN agent_id END) AS withdraw_fee_people,
  142. COALESCE(SUM(channel_fee), 0) AS channel_fee,
  143. COUNT(DISTINCT CASE WHEN channel_fee > 0 THEN agent_id END) AS channel_fee_people,
  144. COUNT(CASE WHEN channel_fee IS NULL THEN 1 END) AS unrecorded
  145. SQL)
  146. ->first();
  147. $result = array_merge($result, (array)$withdraw);
  148. }
  149. return (object)$result;
  150. }
  151. private static function balanceAggregates(?Carbon $start, ?Carbon $end): object
  152. {
  153. $query = StatsScope::excludeTestMembers(DB::table('balance_logs'))
  154. ->whereIn('change_type', [
  155. '充值', '三方充值', '人工充值', '订单补偿', '人工扣款',
  156. '比比返', '笔笔返', '返水', '回水',
  157. '注册赠送', '优惠活动', '即充即送', '充值返现', '老用户回归',
  158. ]);
  159. return self::applyDateRange($query, 'created_at', $start, $end)
  160. ->selectRaw(<<<'SQL'
  161. COALESCE(SUM(CASE
  162. WHEN change_type = '充值' AND amount > 0 THEN amount ELSE 0 END), 0
  163. ) AS company_deposit_amount,
  164. COUNT(DISTINCT CASE
  165. WHEN change_type = '充值' AND amount > 0 THEN member_id END
  166. ) AS company_deposit_people,
  167. COALESCE(SUM(CASE
  168. WHEN change_type = '三方充值' AND amount > 0 THEN amount ELSE 0 END), 0
  169. ) AS third_party_deposit_amount,
  170. COUNT(DISTINCT CASE
  171. WHEN change_type = '三方充值' AND amount > 0 THEN member_id END
  172. ) AS third_party_deposit_people,
  173. COALESCE(SUM(CASE
  174. WHEN ((change_type = '人工充值' AND related_id IS NULL) OR change_type = '订单补偿') AND amount > 0 THEN amount ELSE 0 END), 0
  175. ) AS manual_credit_amount,
  176. COUNT(DISTINCT CASE
  177. WHEN ((change_type = '人工充值' AND related_id IS NULL) OR change_type = '订单补偿') AND amount > 0 THEN member_id END
  178. ) AS manual_credit_people,
  179. COALESCE(SUM(CASE
  180. WHEN change_type = '人工扣款' AND amount < 0 THEN ABS(amount) ELSE 0 END), 0
  181. ) AS manual_debit_amount,
  182. COALESCE(SUM(CASE
  183. WHEN change_type IN ('比比返', '笔笔返', '返水', '回水') AND amount > 0 THEN amount ELSE 0 END), 0
  184. ) AS rebate_amount,
  185. COUNT(DISTINCT CASE
  186. WHEN change_type IN ('比比返', '笔笔返', '返水', '回水') AND amount > 0 THEN member_id END
  187. ) AS rebate_people,
  188. COALESCE(SUM(CASE
  189. WHEN (
  190. change_type IN ('注册赠送', '优惠活动', '即充即送', '充值返现', '老用户回归')
  191. OR (change_type = '人工充值' AND related_id = 0)
  192. ) AND amount > 0 THEN amount ELSE 0 END), 0
  193. ) AS bonus_amount,
  194. COUNT(DISTINCT CASE
  195. WHEN (
  196. change_type IN ('注册赠送', '优惠活动', '即充即送', '充值返现', '老用户回归')
  197. OR (change_type = '人工充值' AND related_id = 0)
  198. ) AND amount > 0 THEN member_id END
  199. ) AS bonus_people
  200. SQL)
  201. ->first();
  202. }
  203. private static function withdrawAggregates(?Carbon $start, ?Carbon $end): object
  204. {
  205. $balanceLogAlias = self::rawAlias('bl');
  206. $withdrawAlias = self::rawAlias('w');
  207. return self::successfulWithdrawLogs($start, $end)
  208. ->selectRaw(<<<SQL
  209. COALESCE(SUM(ABS({$balanceLogAlias}.`amount`)), 0) AS member_withdrawal_amount,
  210. COUNT(DISTINCT {$balanceLogAlias}.`member_id`) AS member_withdrawal_people,
  211. COALESCE(SUM(CASE
  212. WHEN {$withdrawAlias}.`service_charge` > 0
  213. THEN ABS({$balanceLogAlias}.`amount`) * {$withdrawAlias}.`service_charge`
  214. / NULLIF({$withdrawAlias}.`amount`, 0)
  215. ELSE 0 END), 0
  216. ) AS member_withdraw_deduction_amount
  217. SQL)
  218. ->first();
  219. }
  220. private static function paymentOrderAggregates(?Carbon $start, ?Carbon $end): object
  221. {
  222. $pay = PaymentOrderService::TYPE_PAY;
  223. $payout = PaymentOrderService::TYPE_PAYOUT;
  224. $manualPayout = PaymentOrderService::TYPE_SELF_PAYOUT;
  225. $query = StatsScope::excludeTestMembers(DB::table('payment_orders'))
  226. ->where('status', PaymentOrderService::STATUS_SUCCESS)
  227. ->whereIn('type', [$pay, $payout, $manualPayout]);
  228. return self::applyDateRange($query, 'created_at', $start, $end)
  229. ->selectRaw(<<<SQL
  230. COALESCE(SUM(CASE WHEN type = {$pay} AND fee > 0 THEN fee ELSE 0 END), 0)
  231. AS third_party_fee_amount,
  232. COUNT(DISTINCT CASE WHEN type = {$pay} AND fee > 0 THEN member_id END)
  233. AS third_party_fee_people,
  234. COALESCE(SUM(CASE WHEN type = {$payout} THEN amount ELSE 0 END), 0)
  235. AS auto_withdrawal_amount,
  236. COUNT(DISTINCT CASE WHEN type = {$payout} THEN member_id END)
  237. AS auto_withdrawal_people,
  238. COALESCE(SUM(CASE WHEN type = {$payout} AND fee > 0 THEN fee ELSE 0 END), 0)
  239. AS auto_withdrawal_fee_amount,
  240. COUNT(DISTINCT CASE WHEN type = {$payout} AND fee > 0 THEN member_id END)
  241. AS auto_withdrawal_fee_people,
  242. COALESCE(SUM(CASE WHEN type = {$manualPayout} THEN amount ELSE 0 END), 0)
  243. AS manual_withdrawal_amount
  244. SQL)
  245. ->first();
  246. }
  247. /**
  248. * Count unique members across categories backed by more than one table.
  249. *
  250. * @return array{member_withdraw_deduction:int, manual_withdrawal:int}
  251. */
  252. private static function combinedPeople(?Carbon $start, ?Carbon $end): array
  253. {
  254. $withdrawFees = self::successfulWithdrawLogs($start, $end)
  255. ->where('w.service_charge', '>', 0)
  256. ->selectRaw("'member_withdraw_deduction' AS category")
  257. ->addSelect('bl.member_id as member_id');
  258. $manualDebits = self::applyDateRange(StatsScope::excludeTestMembers(DB::table('balance_logs as bl'), 'bl.member_id')
  259. ->where('bl.change_type', '人工扣款')
  260. ->where('bl.amount', '<', 0), 'bl.created_at', $start, $end)
  261. ->selectRaw("'manual_withdrawal' AS category")
  262. ->addSelect('bl.member_id as member_id');
  263. $manualPayouts = self::applyDateRange(StatsScope::excludeTestMembers(DB::table('payment_orders as po'), 'po.member_id')
  264. ->where('po.type', PaymentOrderService::TYPE_SELF_PAYOUT)
  265. ->where('po.status', PaymentOrderService::STATUS_SUCCESS), 'po.created_at', $start, $end)
  266. ->selectRaw("'manual_withdrawal' AS category")
  267. ->addSelect('po.member_id as member_id');
  268. $union = $withdrawFees
  269. ->unionAll($manualDebits)
  270. ->unionAll($manualPayouts);
  271. $rows = DB::query()
  272. ->fromSub($union, 'finance_members')
  273. ->whereNotNull('member_id')
  274. ->select('category')
  275. ->selectRaw('COUNT(DISTINCT member_id) AS people')
  276. ->groupBy('category')
  277. ->get();
  278. $result = [
  279. 'member_withdraw_deduction' => 0,
  280. 'manual_withdrawal' => 0,
  281. ];
  282. foreach ($rows as $row) {
  283. $result[(string)$row->category] = (int)$row->people;
  284. }
  285. return $result;
  286. }
  287. private static function successfulWithdrawLogs(?Carbon $start, ?Carbon $end)
  288. {
  289. $query = StatsScope::excludeTestMembers(DB::table('balance_logs as bl'), 'bl.member_id')
  290. ->join('withdraws as w', 'w.id', '=', 'bl.related_id')
  291. ->where('w.status', 1)
  292. ->where('bl.change_type', '提现')
  293. ->where('bl.amount', '<', 0);
  294. return self::applyDateRange($query, 'bl.created_at', $start, $end);
  295. }
  296. private static function applyDateRange($query, string $column, ?Carbon $start, ?Carbon $end)
  297. {
  298. if ($start !== null && $end !== null) {
  299. $query->whereBetween($column, [$start, $end]);
  300. }
  301. return $query;
  302. }
  303. private static function rawAlias(string $alias): string
  304. {
  305. return '`' . str_replace('`', '``', DB::getTablePrefix() . $alias) . '`';
  306. }
  307. private static function item(string $key, string $label, $amount, $people): array
  308. {
  309. return [
  310. 'key' => $key,
  311. 'label' => $label,
  312. 'amount' => self::money((float)$amount),
  313. 'people' => (int)$people,
  314. ];
  315. }
  316. private static function sumItems(array $items): string
  317. {
  318. return array_reduce($items, static fn (string $sum, array $item): string
  319. => bcadd($sum, $item['amount'] ?? '0.00', 2), '0.00');
  320. }
  321. private static function commissionMoney(string $amount): string
  322. {
  323. return bcadd($amount, bccomp($amount, '0', 10) < 0 ? '-0.005' : '0.005', 2);
  324. }
  325. private static function money(float $amount): string
  326. {
  327. return number_format($amount, 2, '.', '');
  328. }
  329. /**
  330. * @return array{0: Carbon, 1: Carbon}
  331. */
  332. private static function dateRange(string $startDate, string $endDate): array
  333. {
  334. $timezone = config('app.timezone', 'Asia/Shanghai');
  335. return [
  336. Carbon::createFromFormat('Y-m-d', $startDate, $timezone)->startOfDay(),
  337. Carbon::createFromFormat('Y-m-d', $endDate, $timezone)->endOfDay(),
  338. ];
  339. }
  340. }