water/server/plugin/saiadmin/app/logic/fund/MemberActivationRecordLogic.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 MemberActivationRecordLogic extends BaseLogic
{
    private const SOURCE_NAMES = [1 => '微信', 2 => '支付宝', 3 => '后台开通'];

    private const CARD_TYPE_NAMES = [0 => '月卡', 1 => '季卡', 2 => '半年卡', 3 => '年卡'];

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

    private const STATUS_NAMES = [0 => '开通失败', 1 => '开通成功'];

    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.activation_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.activation_time', 'desc')->order('r.id', 'desc')->select()->toArray();
        $data = [];
        foreach ($rows as $row) {
            $record = $this->formatRecord($row);
            $data[] = [
                'merchant_order_sn' => $this->excelSafe($record['merchant_order_sn']),
                'transaction_sn' => $this->excelSafe($record['transaction_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'],
                'merchant_name' => $this->excelSafe($record['merchant_name']),
                'pay_amount' => $record['pay_amount'],
                'card_type_name' => $record['card_type_name'],
                'activation_time' => $record['activation_time'],
                'expire_time' => $record['expire_time'],
                'status_name' => $record['status_name'],
                'remark' => $this->excelSafe($record['remark']),
            ];
        }

        $fileName = '会员开通记录_' . date('Ymd_His') . '.xlsx';
        $writer = new OpenSpoutWriter($fileName);
        $writer->setWidth([26, 28, 12, 22, 18, 12, 18, 24, 14, 12, 22, 22, 12, 36]);
        $writer->setHeader([
            '商户单号', '交易单号', '用户ID', '用户昵称', '手机号', '用户来源', '设备类型',
            '商户', '支付金额', '卡类型', '开通时间', '到期时间', '状态', '备注',
        ]);
        $writer->setData($data);

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

    private function buildQuery(array $filters)
    {
        $query = Db::table('sa_member_activation_record')->alias('r')
            ->leftJoin('sa_rental_user u', 'u.id = r.user_id AND u.delete_time IS NULL')
            ->leftJoin('sa_merchant m', 'm.id = r.merchant_id AND m.delete_time IS NULL')
            ->whereNull('r.delete_time')
            ->field(implode(',', [
                'r.id', 'r.merchant_order_sn', 'r.transaction_sn', 'r.user_id', 'r.manage_agent_id',
                'r.source', 'r.device_type', 'r.merchant_id', 'r.merchant_name AS merchant_name_snapshot',
                'r.pay_amount', 'r.card_type', 'r.activation_time', 'r.expire_time', 'r.status', 'r.remark',
                'u.nickname', 'u.phone', 'u.avatar', 'm.name AS current_merchant_name',
            ]));

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

        $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);
        }

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

        $deviceType = trim((string) ($filters['device_type'] ?? ''));
        if ($deviceType !== '') {
            if (!ctype_digit($deviceType) || !isset(self::DEVICE_NAMES[(int) $deviceType])) {
                throw new ApiException('设备类型不支持');
            }
            $query->where('r.device_type', (int) $deviceType);
        }

        $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);
        }

        $nickname = trim((string) ($filters['nickname'] ?? ''));
        if ($nickname !== '') {
            $query->whereLike('u.nickname', '%' . $nickname . '%');
        }

        [$startDate, $endDate] = $this->dateRange($filters);
        if ($startDate !== '') {
            $query->where('r.activation_time', '>=', $startDate . ' 00:00:00');
        }
        if ($endDate !== '') {
            $query->where('r.activation_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(CASE WHEN r.status = 1 THEN r.pay_amount ELSE 0 END), 0) AS total_paid_amount',
            'SUM(CASE WHEN r.status = 1 AND r.expire_time >= NOW() THEN 1 ELSE 0 END) AS active_count',
            'SUM(CASE WHEN r.status = 1 AND r.expire_time < NOW() THEN 1 ELSE 0 END) AS expired_count',
        ]))->select()->toArray();
        $row = $rows[0] ?? [];

        return [
            'record_count' => (int) ($row['record_count'] ?? 0),
            'total_paid_amount' => round((float) ($row['total_paid_amount'] ?? 0), 2),
            'active_count' => (int) ($row['active_count'] ?? 0),
            'expired_count' => (int) ($row['expired_count'] ?? 0),
        ];
    }

    private function formatRecord(array $row): array
    {
        $source = (int) ($row['source'] ?? 0);
        $cardType = (int) ($row['card_type'] ?? -1);
        $deviceType = (int) ($row['device_type'] ?? 0);
        $status = (int) ($row['status'] ?? 0);
        $merchantName = trim((string) ($row['merchant_name_snapshot'] ?? ''));

        return [
            'id' => (int) ($row['id'] ?? 0),
            'merchant_order_sn' => (string) ($row['merchant_order_sn'] ?? ''),
            'transaction_sn' => (string) ($row['transaction_sn'] ?? ''),
            'user_id' => (int) ($row['user_id'] ?? 0),
            'nickname' => (string) (($row['nickname'] ?? '') ?: '未设置昵称'),
            'phone' => (string) ($row['phone'] ?? ''),
            'avatar' => (string) ($row['avatar'] ?? ''),
            'source' => $source,
            'source_name' => self::SOURCE_NAMES[$source] ?? '其他',
            'device_type' => $deviceType,
            'device_type_name' => self::DEVICE_NAMES[$deviceType] ?? '其他设备',
            'merchant_id' => (int) ($row['merchant_id'] ?? 0),
            'merchant_name' => $merchantName !== '' ? $merchantName : (string) (($row['current_merchant_name'] ?? '') ?: '--'),
            'pay_amount' => round((float) ($row['pay_amount'] ?? 0), 2),
            'card_type' => $cardType,
            'card_type_name' => self::CARD_TYPE_NAMES[$cardType] ?? '其他卡',
            'activation_time' => (string) ($row['activation_time'] ?? ''),
            'expire_time' => (string) ($row['expire_time'] ?? ''),
            '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;
    }
}