water/server/plugin/saiadmin/app/logic/fund/DepositRefundLogic.php

<?php

namespace plugin\saiadmin\app\logic\fund;

use DateTime;
use plugin\saiadmin\app\model\system\SystemUser;
use plugin\saiadmin\basic\think\BaseLogic;
use plugin\saiadmin\exception\ApiException;
use plugin\saiadmin\service\OpenSpoutWriter;
use support\Response;
use support\think\Db;

/**
 * 押金退还任务查询与导出。
 */
class DepositRefundLogic extends BaseLogic
{
    private const SOURCE_NAMES = [1 => '微信', 2 => '支付宝'];

    private const REFUND_TYPE_NAMES = [0 => '原路退回', 1 => '退回钱包'];

    private const STATUS_NAMES = [0 => '待退还', 1 => '已退还', 2 => '退款异常'];

    private const DEVICE_NAMES = [
        1 => '充电底座',
        100 => 'K霸宝',
        101 => '充电宝',
        102 => 'K霸宝 Blue',
        103 => '密码线充',
        104 => '蓝牙线充',
        105 => '密码蓝牙线充',
        107 => '车载充电器',
    ];

    private const EXPORT_LIMIT = 50000;

    public function indexList(array $filters): array
    {
        $page = max(1, (int) request()->input('page', 1));
        $limit = min(100, max(1, (int) request()->input('limit', 20)));

        $result = $this->buildQuery($filters)
            ->order('r.apply_time', 'desc')
            ->order('r.id', 'desc')
            ->paginate(['page' => $page, 'list_rows' => $limit])
            ->toArray();
        $result['data'] = array_map(fn (array $row): array => $this->formatRecord($row), $result['data'] ?? []);
        $result['summary'] = $this->summary($filters);

        return $result;
    }

    public function export(array $filters): Response
    {
        $query = $this->buildQuery($filters);
        $count = (int) (clone $query)->count();
        if ($count > self::EXPORT_LIMIT) {
            throw new ApiException('导出记录超过 50000 条，请缩小筛选范围');
        }

        $rows = $query->order('r.apply_time', 'desc')->order('r.id', 'desc')->select()->toArray();
        $data = [];
        foreach ($rows as $row) {
            $record = $this->formatRecord($row);
            $data[] = [
                'order_sn' => $this->excelSafe($record['order_sn']),
                'user_id' => $record['user_id'],
                'nickname' => $this->excelSafe($record['nickname']),
                'phone' => $this->excelSafe($record['phone']),
                'source_name' => $record['source_name'],
                'device_type_name' => $record['device_type_name'],
                'deposit_amount' => $record['deposit_amount'],
                'consumption_amount' => $record['consumption_amount'],
                'refund_amount' => $record['refund_amount'],
                'apply_time' => $record['apply_time'],
                'scheduled_refund_time' => $record['scheduled_refund_time'],
                'refund_time' => $record['refund_time'],
                'refund_type_name' => $record['refund_type_name'],
                'status_name' => $record['status_name'],
                'manager_name' => $this->excelSafe($record['manager_name']),
                'remark' => $this->excelSafe($record['remark']),
            ];
        }

        $fileName = '退押记录_' . date('Ymd_His') . '.xlsx';
        $writer = new OpenSpoutWriter($fileName);
        $writer->setWidth([24, 12, 22, 18, 10, 18, 14, 14, 14, 22, 22, 22, 14, 12, 18, 36]);
        $writer->setHeader([
            '订单号', '用户ID', '用户昵称', '手机号', '来源', '设备类型', '押金金额', '消费金额',
            '退款金额', '申请时间', '计划退款时间', '实际退款时间', '退款类型', '状态', '管理代理', '备注',
        ]);
        $writer->setData($data);

        return response()->download($writer->returnFile(), urlencode($fileName));
    }

    private function buildQuery(array $filters)
    {
        $query = Db::table('sa_deposit_refund_record')->alias('r')
            ->leftJoin('sa_rental_order o', 'o.id = r.order_id AND o.delete_time IS NULL')
            ->leftJoin('sa_rental_user u', 'u.id = r.user_id AND u.delete_time IS NULL')
            ->leftJoin('sa_system_user a', 'a.id = r.manage_agent_id AND a.delete_time IS NULL')
            ->whereNull('r.delete_time')
            ->field(implode(',', [
                'r.id', 'r.order_id', 'r.order_sn', 'r.user_id', 'r.manage_agent_id', 'r.source',
                'r.device_type', 'r.deposit_amount', 'r.consumption_amount', 'r.refund_amount',
                'r.apply_time', 'r.scheduled_refund_time', 'r.refund_time', 'r.refund_type',
                'r.status', 'r.remark', 'u.nickname', 'u.phone', 'u.avatar',
                'o.user_name AS order_user_name', 'o.user_phone AS order_user_phone',
                'a.username AS manager_username', 'a.realname AS manager_realname',
            ]));

        $managerIds = $this->accessibleManagerIds();
        if (is_array($managerIds)) {
            if ($managerIds === []) {
                $query->where('r.id', 0);
            } else {
                $query->whereIn('r.manage_agent_id', $managerIds);
            }
        }

        $status = trim((string) ($filters['status'] ?? ''));
        if ($status !== '') {
            if (!ctype_digit($status) || !isset(self::STATUS_NAMES[(int) $status])) {
                throw new ApiException('退押状态不支持');
            }
            $query->where('r.status', (int) $status);
        }

        $source = trim((string) ($filters['source'] ?? ''));
        if ($source !== '') {
            if (!ctype_digit($source) || !isset(self::SOURCE_NAMES[(int) $source])) {
                throw new ApiException('订单来源不支持');
            }
            $query->where('r.source', (int) $source);
        }

        $refundType = trim((string) ($filters['refund_type'] ?? ''));
        if ($refundType !== '') {
            if (!ctype_digit($refundType) || !isset(self::REFUND_TYPE_NAMES[(int) $refundType])) {
                throw new ApiException('退款类型不支持');
            }
            $query->where('r.refund_type', (int) $refundType);
        }

        $orderSn = trim((string) ($filters['order_sn'] ?? ''));
        if ($orderSn !== '') {
            if (mb_strlen($orderSn) > 64) {
                throw new ApiException('订单号不能超过 64 个字符');
            }
            $query->whereLike('r.order_sn', '%' . $orderSn . '%');
        }

        $userId = trim((string) ($filters['user_id'] ?? ''));
        if ($userId !== '') {
            if (!ctype_digit($userId) || (int) $userId <= 0) {
                throw new ApiException('用户 ID 格式不正确');
            }
            $query->where('r.user_id', (int) $userId);
        }

        [$startDate, $endDate] = $this->dateRange($filters);
        if ($startDate !== '') {
            $query->where('r.apply_time', '>=', $startDate . ' 00:00:00');
        }
        if ($endDate !== '') {
            $query->where('r.apply_time', '<=', $endDate . ' 23:59:59');
        }

        return $query;
    }

    private function summary(array $filters): array
    {
        $query = $this->buildQuery($filters);
        $query->removeOption('field');
        $rows = $query->field(implode(',', [
            'COUNT(*) AS record_count',
            'COALESCE(SUM(r.refund_amount), 0) AS total_refund_amount',
            'COALESCE(SUM(CASE WHEN r.status = 1 THEN r.refund_amount ELSE 0 END), 0) AS refunded_amount',
            'COALESCE(SUM(CASE WHEN r.status <> 1 THEN r.refund_amount ELSE 0 END), 0) AS pending_amount',
        ]))->select()->toArray();
        $row = $rows[0] ?? [];

        return [
            'record_count' => (int) ($row['record_count'] ?? 0),
            'total_refund_amount' => round((float) ($row['total_refund_amount'] ?? 0), 2),
            'refunded_amount' => round((float) ($row['refunded_amount'] ?? 0), 2),
            'pending_amount' => round((float) ($row['pending_amount'] ?? 0), 2),
        ];
    }

    private function formatRecord(array $row): array
    {
        $nickname = trim((string) ($row['nickname'] ?? ''));
        if ($nickname === '') {
            $nickname = trim((string) ($row['order_user_name'] ?? '')) ?: '未设置昵称';
        }
        $phone = trim((string) ($row['phone'] ?? ''));
        if ($phone === '') {
            $phone = trim((string) ($row['order_user_phone'] ?? ''));
        }
        $managerName = trim((string) ($row['manager_realname'] ?? ''));
        if ($managerName === '') {
            $managerName = trim((string) ($row['manager_username'] ?? ''));
        }

        $source = (int) ($row['source'] ?? 0);
        $deviceType = (int) ($row['device_type'] ?? 0);
        $refundType = (int) ($row['refund_type'] ?? 0);
        $status = (int) ($row['status'] ?? 0);

        return [
            'id' => (int) ($row['id'] ?? 0),
            'order_id' => (int) ($row['order_id'] ?? 0),
            'order_sn' => (string) ($row['order_sn'] ?? ''),
            'user_id' => (int) ($row['user_id'] ?? 0),
            'nickname' => $nickname,
            'phone' => $phone,
            'avatar' => (string) ($row['avatar'] ?? ''),
            'manage_agent_id' => (int) ($row['manage_agent_id'] ?? 0),
            'manager_name' => $managerName !== '' ? $managerName : '管理员#' . (int) ($row['manage_agent_id'] ?? 0),
            'source' => $source,
            'source_name' => self::SOURCE_NAMES[$source] ?? '未知',
            'device_type' => $deviceType,
            'device_type_name' => self::DEVICE_NAMES[$deviceType] ?? '其他设备',
            'deposit_amount' => round((float) ($row['deposit_amount'] ?? 0), 2),
            'consumption_amount' => round((float) ($row['consumption_amount'] ?? 0), 2),
            'refund_amount' => round((float) ($row['refund_amount'] ?? 0), 2),
            'apply_time' => (string) ($row['apply_time'] ?? ''),
            'scheduled_refund_time' => (string) ($row['scheduled_refund_time'] ?? ''),
            'refund_time' => (string) ($row['refund_time'] ?? ''),
            'refund_type' => $refundType,
            'refund_type_name' => self::REFUND_TYPE_NAMES[$refundType] ?? '其他',
            'status' => $status,
            'status_name' => self::STATUS_NAMES[$status] ?? '未知',
            'remark' => (string) ($row['remark'] ?? ''),
        ];
    }

    /** @return array{0:string,1:string} */
    private function dateRange(array $filters): array
    {
        $startDate = trim((string) ($filters['start_date'] ?? ''));
        $endDate = trim((string) ($filters['end_date'] ?? ''));
        foreach ([['开始', $startDate], ['结束', $endDate]] as [$label, $date]) {
            if ($date === '') {
                continue;
            }
            $parsed = DateTime::createFromFormat('!Y-m-d', $date);
            if (!$parsed || $parsed->format('Y-m-d') !== $date) {
                throw new ApiException($label . '日期格式不正确');
            }
        }
        if ($startDate !== '' && $endDate !== '' && $startDate > $endDate) {
            throw new ApiException('开始日期不能晚于结束日期');
        }
        return [$startDate, $endDate];
    }

    /** @return int[]|null */
    private function accessibleManagerIds(): ?array
    {
        $currentId = (int) ($this->adminInfo['id'] ?? 0);
        if ($currentId === 1) {
            return null;
        }
        if ($currentId <= 0) {
            return [];
        }

        $managerIds = [$currentId];
        $dept = $this->adminInfo['deptList'] ?? [];
        if (empty($dept['id']) || !isset($dept['level'])) {
            return $managerIds;
        }

        $prefix = (string) $dept['level'] . (int) $dept['id'] . ',';
        $deptIds = Db::table('sa_system_dept')->whereNull('delete_time')
            ->whereLike('level', $prefix . '%')->column('id');
        if ($deptIds !== []) {
            $managerIds = array_merge($managerIds, array_map('intval', SystemUser::whereIn('dept_id', $deptIds)
                ->whereNull('delete_time')->column('id')));
        }
        return array_values(array_unique($managerIds));
    }

    private function excelSafe(mixed $value): string
    {
        $value = (string) $value;
        return preg_match('/^[=+\-@]/u', $value) === 1 ? "'" . $value : $value;
    }
}