water/server/plugin/saiadmin/app/logic/operation/MerchantAlertLogic.php

<?php

namespace plugin\saiadmin\app\logic\operation;

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;

/**
 * 商户连续无交易警报。
 *
 * 规则沿用旧系统：最近完成交易距今满 2 天进入警报，满 3 天归入
 * “连续3天及以上无交易”，从未产生完成订单的商户归入“一直无交易”。
 */
class MerchantAlertLogic extends BaseLogic
{
    private const ALERT_TWO_DAYS = 'two_days';
    private const ALERT_THREE_DAYS = 'three_days';
    private const ALERT_NEVER = 'never';
    private const EXPORT_LIMIT = 50000;

    private const LAST_TRANSACTION_SQL = '(SELECT MAX(s.stat_date) FROM sa_merchant_daily_statistics s'
        . ' WHERE s.merchant_id = m.id AND s.order_count > 0)';

    private const LAST_DISTRIBUTION_SQL = '(SELECT MAX(r.action_time) FROM sa_merchant_device_binding_record r'
        . ' WHERE r.merchant_id = m.id AND r.action_type = 1)';

    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)
            ->orderRaw($this->alertWeightSql() . ' ASC')
            ->order('m.id', 'asc')
            ->paginate(['page' => $page, 'list_rows' => $limit])
            ->toArray();
        $result['data'] = array_map(
            fn (array $row): array => $this->formatAlert($row),
            $result['data'] ?? []
        );

        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
            ->orderRaw($this->alertWeightSql() . ' ASC')
            ->order('m.id', 'asc')
            ->select()
            ->toArray();
        $data = [];
        foreach ($rows as $row) {
            $alert = $this->formatAlert($row);
            $data[] = [
                'merchant_id' => $alert['merchant_id'],
                'merchant_name' => $this->excelSafe($alert['merchant_name']),
                'created_at' => $alert['created_at'],
                'last_login_at' => $alert['last_login_at'],
                'last_distribution_at' => $alert['last_distribution_at'],
                'reason' => $alert['reason'],
                'contact' => $this->excelSafe($alert['contact']),
                'phone' => $this->excelSafe($alert['phone']),
            ];
        }

        $fileName = '商户警报_' . date('Ymd_His') . '.xlsx';
        $writer = new OpenSpoutWriter($fileName);
        $writer->setWidth([12, 28, 22, 22, 22, 24, 18, 20]);
        $writer->setHeader([
            '商户ID', '商户名称', '创建时间', '最新登录时间',
            '最新铺货时间', '交易停止原因', '联系人', '联系电话',
        ]);
        $writer->setData($data);

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

    private function buildQuery(array $filters)
    {
        $query = Db::table('sa_merchant')->alias('m')
            ->whereNull('m.delete_time')
            ->whereRaw(
                '(' . self::LAST_TRANSACTION_SQL . ' IS NULL OR '
                . self::LAST_TRANSACTION_SQL . ' <= DATE_SUB(CURDATE(), INTERVAL 2 DAY))'
            )
            ->field(implode(',', [
                'm.id AS merchant_id', 'm.name AS merchant_name', 'm.create_time AS created_at',
                'm.contact', 'm.phone', self::LAST_TRANSACTION_SQL . ' AS last_transaction_date',
                self::LAST_DISTRIBUTION_SQL . ' AS last_distribution_at',
            ]));

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

        $merchantName = trim((string) ($filters['merchant_name'] ?? ''));
        if ($merchantName !== '') {
            $query->whereLike('m.name', '%' . $merchantName . '%');
        }

        $alertType = trim((string) ($filters['alert_type'] ?? ''));
        if ($alertType !== '') {
            $this->applyAlertType($query, $alertType);
        }

        return $query;
    }

    private function applyAlertType($query, string $alertType): void
    {
        if ($alertType === self::ALERT_NEVER) {
            $query->whereRaw(self::LAST_TRANSACTION_SQL . ' IS NULL');
            return;
        }
        if ($alertType === self::ALERT_THREE_DAYS) {
            $query->whereRaw(
                self::LAST_TRANSACTION_SQL . ' IS NOT NULL AND '
                . self::LAST_TRANSACTION_SQL . ' <= DATE_SUB(CURDATE(), INTERVAL 3 DAY)'
            );
            return;
        }
        if ($alertType === self::ALERT_TWO_DAYS) {
            $query->whereRaw(
                self::LAST_TRANSACTION_SQL . ' > DATE_SUB(CURDATE(), INTERVAL 3 DAY) AND '
                . self::LAST_TRANSACTION_SQL . ' <= DATE_SUB(CURDATE(), INTERVAL 2 DAY)'
            );
            return;
        }
        throw new ApiException('警报类型不支持');
    }

    private function alertWeightSql(): string
    {
        return 'CASE WHEN ' . self::LAST_TRANSACTION_SQL . ' IS NULL THEN 99 '
            . 'WHEN ' . self::LAST_TRANSACTION_SQL . ' <= DATE_SUB(CURDATE(), INTERVAL 3 DAY) THEN 3 '
            . 'ELSE 2 END';
    }

    private function formatAlert(array $row): array
    {
        $lastTransactionDate = (string) ($row['last_transaction_date'] ?? '');
        if ($lastTransactionDate === '') {
            $alertType = self::ALERT_NEVER;
            $reason = '一直无交易';
        } elseif ($lastTransactionDate <= date('Y-m-d', strtotime('-3 days'))) {
            $alertType = self::ALERT_THREE_DAYS;
            $reason = '连续3天及以上无交易';
        } else {
            $alertType = self::ALERT_TWO_DAYS;
            $reason = '连续2天无交易';
        }

        return [
            'merchant_id' => (int) ($row['merchant_id'] ?? 0),
            'merchant_name' => (string) ($row['merchant_name'] ?? ''),
            'created_at' => (string) ($row['created_at'] ?? ''),
            // 新系统尚未开放商户登录入口，没有可信的登录流水，不能伪造该时间。
            'last_login_at' => '',
            'last_distribution_at' => (string) ($row['last_distribution_at'] ?? ''),
            'last_transaction_date' => $lastTransactionDate,
            'alert_type' => $alertType,
            'reason' => $reason,
            'contact' => (string) ($row['contact'] ?? ''),
            'phone' => (string) ($row['phone'] ?? ''),
        ];
    }

    /** @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;
    }
}