bot-28/app/Services/ShareCommissionDiagnostics.php

<?php

namespace App\Services;

use App\Models\BalanceLog;
use App\Models\Config;
use App\Models\ShareCommissionRecord;
use App\Models\User;
use Illuminate\Support\Facades\DB;

/** Read-only counts that locate the missing stage; never calculates or grants unpaid rewards. */
class ShareCommissionDiagnostics
{
    public function inspect(?array $range): array
    {
        $rebates = BalanceLog::where('change_type', '充值返现')->selectRaw('COUNT(*) AS total')
            ->selectRaw('COALESCE(SUM(CASE WHEN type = 1 THEN 1 ELSE 0 END), 0) AS type1')
            ->selectRaw('COALESCE(SUM(CASE WHEN type = 1 AND amount > 0 THEN 1 ELSE 0 END), 0) AS positive')
            ->selectRaw('MIN(CASE WHEN type = 1 AND amount > 0 THEN created_at END) AS first_rebate_at')
            ->selectRaw('MAX(CASE WHEN type = 1 AND amount > 0 THEN created_at END) AS last_rebate_at')->first();
        $selected = BalanceLog::where('change_type', '充值返现')->where('type', 1)->where('amount', '>', 0);
        if ($range !== null) $selected->whereBetween('created_at', $range);

        $invited = User::query()->from('users as invitee')
            ->whereNotNull('invitee.agent_user_code')->where('invitee.agent_user_code', '<>', '');
        $realInvited = (clone $invited)->where('invitee.from', 1);
        $linked = (clone $realInvited)->where('invitee.agent_user_code', '<>', '0')->whereExists(function ($q) {
            $q->selectRaw('1')->from('users as promoter')
                ->whereColumn('promoter.user_code', 'invitee.agent_user_code')
                ->whereNotNull('promoter.member_id')->where('promoter.member_id', '<>', '')->where('promoter.member_id', '<>', '0');
        });

        $deposits = BalanceLog::where('type', 1)->where('amount', '>', 0)
            ->whereIn('change_type', ['充值', '三方充值']);
        $realDeposits = (clone $deposits)->whereExists(function ($q) {
            $q->selectRaw('1')->from('users as invitee')
                ->whereColumn('invitee.member_id', 'balance_logs.member_id')->where('invitee.from', 1)
                ->whereNotNull('invitee.agent_user_code')->where('invitee.agent_user_code', '<>', '');
        });
        $linkedDeposits = (clone $realDeposits)->whereExists(function ($q) {
            $q->selectRaw('1')->from('users as invitee')
                ->join('users as promoter', 'promoter.user_code', '=', 'invitee.agent_user_code')
                ->whereColumn('invitee.member_id', 'balance_logs.member_id')
                ->where('invitee.from', 1)->whereNotNull('invitee.agent_user_code')->where('invitee.agent_user_code', '<>', '')->where('invitee.agent_user_code', '<>', '0')
                ->whereNotNull('promoter.member_id')->where('promoter.member_id', '<>', '')->where('promoter.member_id', '<>', '0');
        });
        $walletDeposits = (clone $linkedDeposits)->whereExists(function ($q) {
            $q->selectRaw('1')->from('users as invitee')
                ->join('users as promoter', 'promoter.user_code', '=', 'invitee.agent_user_code')
                ->join('wallets as promoter_wallet', 'promoter_wallet.member_id', '=', 'promoter.member_id')
                ->whereColumn('invitee.member_id', 'balance_logs.member_id')
                ->where('invitee.from', 1)->whereNotNull('invitee.agent_user_code')->where('invitee.agent_user_code', '<>', '')->where('invitee.agent_user_code', '<>', '0')
                ->whereNotNull('promoter.member_id')->where('promoter.member_id', '<>', '')->where('promoter.member_id', '<>', '0');
        });
        $activeRecords = ShareCommissionRecord::where('status', 1);
        if ($range !== null) {
            $activeRecords->whereBetween('stat_date', [substr($range[0], 0, 10), substr($range[1], 0, 10)]);
        }
        $rate = Config::where('field', 'recharge_rate')->value('val');
        $setting = DB::connection()->getSchemaBuilder()->hasTable('share_settings')
            ? DB::table('share_settings')->orderBy('id')->first(['enabled', 'tier_one_rate', 'tier_two_rate']) : null;
        return [
            'database' => DB::connection()->getDatabaseName(),
            'table_prefix' => DB::getTablePrefix(),
            'recharge_rate' => $rate,
            'share_enabled' => $setting->enabled ?? null,
            'tier_one_rate' => $setting->tier_one_rate ?? null,
            'tier_two_rate' => $setting->tier_two_rate ?? null,
            'counts' => [
                'rebate_logs_all' => (int)$rebates->total,
                'type1_rebate_logs_all' => (int)$rebates->type1,
                'positive_type1_rebate_logs_all' => (int)$rebates->positive,
                'selected_rebate_logs' => $selected->count(),
                'invited_members_all' => $invited->count(),
                'real_invited_members_all' => $realInvited->count(),
                'linked_real_invited_members_all' => $linked->count(),
                'deposit_logs_all' => $deposits->count(),
                'real_invitee_deposit_logs_all' => $realDeposits->count(),
                'linked_real_invitee_deposit_logs_all' => $linkedDeposits->count(),
                'with_promoter_wallet_deposit_logs_all' => $walletDeposits->count(),
                'commission_records_all' => ShareCommissionRecord::count(),
                'active_commission_records_selected' => $activeRecords->count(),
            ],
            'first_rebate_at' => $rebates->getRawOriginal('first_rebate_at'),
            'last_rebate_at' => $rebates->getRawOriginal('last_rebate_at'),
        ];
    }
}