bot-28/app/Services/FundsSummaryService.php

<?php

namespace App\Services;

use Carbon\Carbon;
use Illuminate\Support\Facades\DB;

class FundsSummaryService
{
    public static function summary(string $startDate, string $endDate): array
    {
        [$start, $end] = self::dateRange($startDate, $endDate);
        $balance = self::balanceAggregates($start, $end);
        $withdraw = self::withdrawAggregates($start, $end);
        $paymentOrder = self::paymentOrderAggregates($start, $end);
        $combinedPeople = self::combinedPeople($start, $end);

        $income = [
            self::item('company_deposit', '公司入款', $balance->company_deposit_amount, $balance->company_deposit_people),
            self::item(
                'third_party_deposit',
                '第三方支付（入款）',
                $balance->third_party_deposit_amount,
                $balance->third_party_deposit_people
            ),
            self::item('manual_credit', '人工上分', $balance->manual_credit_amount, $balance->manual_credit_people),
            self::item(
                'member_withdraw_deduction',
                '会员出款扣除',
                (float)$withdraw->member_withdraw_deduction_amount
                    + (float)$paymentOrder->manual_withdrawal_fee_amount,
                $combinedPeople['member_withdraw_deduction']
            ),
        ];

        $expense = [
            self::item(
                'member_withdrawal',
                '会员出款',
                $withdraw->member_withdrawal_amount,
                $withdraw->member_withdrawal_people
            ),
            self::item(
                'manual_withdrawal',
                '人工出款',
                (float)$balance->manual_debit_amount + (float)$paymentOrder->manual_withdrawal_amount,
                $combinedPeople['manual_withdrawal']
            ),
            self::item('vip_discount', '给予优惠（VIP 折扣）', 0, 0),
            self::item('rebate', '给予返点', $balance->rebate_amount, $balance->rebate_people),
            self::item('bonus', '优惠金', $balance->bonus_amount, $balance->bonus_people),
            self::item(
                'third_party_fee',
                '第三方手续费',
                $paymentOrder->third_party_fee_amount,
                $paymentOrder->third_party_fee_people
            ),
            self::item(
                'third_party_auto_withdrawal',
                '第三方支付出款（自动出款）',
                $paymentOrder->auto_withdrawal_amount,
                $paymentOrder->auto_withdrawal_people
            ),
            self::item(
                'third_party_auto_withdrawal_fee',
                '第三方支付自动出款手续费',
                $paymentOrder->auto_withdrawal_fee_amount,
                $paymentOrder->auto_withdrawal_fee_people
            ),
        ];

        return [
            'start_date' => $startDate,
            'end_date' => $endDate,
            'income' => $income,
            'expense' => $expense,
            'income_total' => self::sumItems($income),
            'expense_total' => self::sumItems($expense),
        ];
    }

    private static function balanceAggregates(Carbon $start, Carbon $end): object
    {
        return DB::table('balance_logs')
            ->whereBetween('created_at', [$start, $end])
            ->whereIn('change_type', [
                '充值', '三方充值', '人工充值', '人工扣款',
                '比比返', '笔笔返', '返水', '回水',
                '注册赠送', '优惠活动', '即充即送', '充值返现', '老用户回归',
            ])
            ->selectRaw(<<<'SQL'
                COALESCE(SUM(CASE
                    WHEN change_type = '充值' AND amount > 0 THEN amount ELSE 0 END), 0
                ) AS company_deposit_amount,
                COUNT(DISTINCT CASE
                    WHEN change_type = '充值' AND amount > 0 THEN member_id END
                ) AS company_deposit_people,
                COALESCE(SUM(CASE
                    WHEN change_type = '三方充值' AND amount > 0 THEN amount ELSE 0 END), 0
                ) AS third_party_deposit_amount,
                COUNT(DISTINCT CASE
                    WHEN change_type = '三方充值' AND amount > 0 THEN member_id END
                ) AS third_party_deposit_people,
                COALESCE(SUM(CASE
                    WHEN change_type = '人工充值' AND related_id IS NULL AND amount > 0 THEN amount ELSE 0 END), 0
                ) AS manual_credit_amount,
                COUNT(DISTINCT CASE
                    WHEN change_type = '人工充值' AND related_id IS NULL AND amount > 0 THEN member_id END
                ) AS manual_credit_people,
                COALESCE(SUM(CASE
                    WHEN change_type = '人工扣款' AND amount < 0 THEN ABS(amount) ELSE 0 END), 0
                ) AS manual_debit_amount,
                COALESCE(SUM(CASE
                    WHEN change_type IN ('比比返', '笔笔返', '返水', '回水') AND amount > 0 THEN amount ELSE 0 END), 0
                ) AS rebate_amount,
                COUNT(DISTINCT CASE
                    WHEN change_type IN ('比比返', '笔笔返', '返水', '回水') AND amount > 0 THEN member_id END
                ) AS rebate_people,
                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,
                COUNT(DISTINCT CASE
                    WHEN (
                        change_type IN ('注册赠送', '优惠活动', '即充即送', '充值返现', '老用户回归')
                        OR (change_type = '人工充值' AND related_id = 0)
                    ) AND amount > 0 THEN member_id END
                ) AS bonus_people
            SQL)
            ->first();
    }

    private static function withdrawAggregates(Carbon $start, Carbon $end): object
    {
        $balanceLogAlias = self::rawAlias('bl');
        $withdrawAlias = self::rawAlias('w');

        return self::successfulWithdrawLogs($start, $end)
            ->selectRaw(<<<SQL
                COALESCE(SUM(ABS({$balanceLogAlias}.`amount`)), 0) AS member_withdrawal_amount,
                COUNT(DISTINCT {$balanceLogAlias}.`member_id`) AS member_withdrawal_people,
                COALESCE(SUM(CASE
                    WHEN {$withdrawAlias}.`service_charge` > 0
                    THEN ABS({$balanceLogAlias}.`amount`) * {$withdrawAlias}.`service_charge`
                        / NULLIF({$withdrawAlias}.`amount`, 0)
                    ELSE 0 END), 0
                ) AS member_withdraw_deduction_amount
            SQL)
            ->first();
    }

    private static function paymentOrderAggregates(Carbon $start, Carbon $end): object
    {
        $pay = PaymentOrderService::TYPE_PAY;
        $payout = PaymentOrderService::TYPE_PAYOUT;
        $manualPayout = PaymentOrderService::TYPE_SELF_PAYOUT;

        return DB::table('payment_orders')
            ->where('status', PaymentOrderService::STATUS_SUCCESS)
            ->whereIn('type', [$pay, $payout, $manualPayout])
            ->whereBetween('created_at', [$start, $end])
            ->selectRaw(<<<SQL
                COALESCE(SUM(CASE WHEN type = {$pay} AND fee > 0 THEN fee ELSE 0 END), 0)
                    AS third_party_fee_amount,
                COUNT(DISTINCT CASE WHEN type = {$pay} AND fee > 0 THEN member_id END)
                    AS third_party_fee_people,
                COALESCE(SUM(CASE WHEN type = {$payout} THEN amount ELSE 0 END), 0)
                    AS auto_withdrawal_amount,
                COUNT(DISTINCT CASE WHEN type = {$payout} THEN member_id END)
                    AS auto_withdrawal_people,
                COALESCE(SUM(CASE WHEN type = {$payout} AND fee > 0 THEN fee ELSE 0 END), 0)
                    AS auto_withdrawal_fee_amount,
                COUNT(DISTINCT CASE WHEN type = {$payout} AND fee > 0 THEN member_id END)
                    AS auto_withdrawal_fee_people,
                COALESCE(SUM(CASE WHEN type = {$manualPayout} THEN amount ELSE 0 END), 0)
                    AS manual_withdrawal_amount,
                COALESCE(SUM(CASE WHEN type = {$manualPayout} AND fee > 0 THEN fee ELSE 0 END), 0)
                    AS manual_withdrawal_fee_amount
            SQL)
            ->first();
    }

    /**
     * Count unique members across categories backed by more than one table.
     *
     * @return array{member_withdraw_deduction:int, manual_withdrawal:int}
     */
    private static function combinedPeople(Carbon $start, Carbon $end): array
    {
        $withdrawFees = self::successfulWithdrawLogs($start, $end)
            ->where('w.service_charge', '>', 0)
            ->selectRaw("'member_withdraw_deduction' AS category")
            ->addSelect('bl.member_id as member_id');
        $manualPayoutFees = DB::table('payment_orders as po')
            ->where('po.type', PaymentOrderService::TYPE_SELF_PAYOUT)
            ->where('po.status', PaymentOrderService::STATUS_SUCCESS)
            ->where('po.fee', '>', 0)
            ->whereBetween('po.created_at', [$start, $end])
            ->selectRaw("'member_withdraw_deduction' AS category")
            ->addSelect('po.member_id as member_id');
        $manualDebits = DB::table('balance_logs as bl')
            ->where('bl.change_type', '人工扣款')
            ->where('bl.amount', '<', 0)
            ->whereBetween('bl.created_at', [$start, $end])
            ->selectRaw("'manual_withdrawal' AS category")
            ->addSelect('bl.member_id as member_id');
        $manualPayouts = DB::table('payment_orders as po')
            ->where('po.type', PaymentOrderService::TYPE_SELF_PAYOUT)
            ->where('po.status', PaymentOrderService::STATUS_SUCCESS)
            ->whereBetween('po.created_at', [$start, $end])
            ->selectRaw("'manual_withdrawal' AS category")
            ->addSelect('po.member_id as member_id');

        $union = $withdrawFees
            ->unionAll($manualPayoutFees)
            ->unionAll($manualDebits)
            ->unionAll($manualPayouts);
        $rows = DB::query()
            ->fromSub($union, 'finance_members')
            ->whereNotNull('member_id')
            ->select('category')
            ->selectRaw('COUNT(DISTINCT member_id) AS people')
            ->groupBy('category')
            ->get();

        $result = [
            'member_withdraw_deduction' => 0,
            'manual_withdrawal' => 0,
        ];
        foreach ($rows as $row) {
            $result[(string)$row->category] = (int)$row->people;
        }

        return $result;
    }

    private static function successfulWithdrawLogs(Carbon $start, Carbon $end)
    {
        return DB::table('balance_logs as bl')
            ->join('withdraws as w', 'w.id', '=', 'bl.related_id')
            ->where('w.status', 1)
            ->where('bl.change_type', '提现')
            ->where('bl.amount', '<', 0)
            ->whereBetween('bl.created_at', [$start, $end]);
    }

    private static function rawAlias(string $alias): string
    {
        return '`' . str_replace('`', '``', DB::getTablePrefix() . $alias) . '`';
    }

    private static function item(string $key, string $label, $amount, $people): array
    {
        return [
            'key' => $key,
            'label' => $label,
            'amount' => self::money((float)$amount),
            'people' => (int)$people,
        ];
    }

    private static function sumItems(array $items): string
    {
        $total = array_reduce($items, static function (float $sum, array $item): float {
            return $sum + (float)$item['amount'];
        }, 0.0);

        return self::money($total);
    }

    private static function money(float $amount): string
    {
        return number_format($amount, 2, '.', '');
    }

    /**
     * @return array{0: Carbon, 1: Carbon}
     */
    private static function dateRange(string $startDate, string $endDate): array
    {
        $timezone = config('app.timezone', 'Asia/Shanghai');

        return [
            Carbon::createFromFormat('Y-m-d', $startDate, $timezone)->startOfDay(),
            Carbon::createFromFormat('Y-m-d', $endDate, $timezone)->endOfDay(),
        ];
    }
}