huahuibao/console/controllers/ClearController.php

<?php

namespace console\controllers;

use bizGhs\clear\classes\OrderCgClearClass;
use bizGhs\clear\models\Clear;
use bizGhs\order\classes\OrderClearClass;
use bizGhs\order\classes\OrderClass;
use bizHd\purchase\classes\PurchaseClass;
use bizHd\purchase\classes\PurchaseClearClass;
use common\components\dict;
use Yii;
use yii\console\Controller;
use yii\console\ExitCode;

class ClearController extends Controller
{

    /** @var int 是否仅预览，1=是 */
    public $dryRun = 0;

    /** @var int 每批扫描 xhClear 条数（宜小，减轻锁与内存） */
    public $batchSize = 50;

    /** @var int 每批之间休眠毫秒数，降低对线上库压力 */
    public $sleepMs = 100;

    /** @var int 指定结账单 ID，0=全部 */
    public $clearId = 0;

    /** @var int 是否重置进度从最大 id 重跑，1=是 */
    public $reset = 0;

    /** @var int 本次最多处理 xhClear 条数，0=不限制 */
    public $limit = 0;

    /** @var int 每处理多少条输出一次进度，0=仅每批汇总 */
    public $logEvery = 500;

    /** @var int 每种 clearStyle 抽样条数（check-order-cg-clear-sample 默认 50000） */
    public $samplePerStyle = 50000;

    /** @var int 抽样检查最多打印的问题明细条数 */
    public $showIssues = 30;

    /** @var string 回填进度文件后缀（内部使用） */
    private $backfillProgressKey = '';

    public function options($actionID)
    {
        $options = array_merge(parent::options($actionID), [
            'dryRun',
            'batchSize',
            'sleepMs',
            'clearId',
            'reset',
            'limit',
            'logEvery',
        ]);
        if ($actionID === 'check-order-cg-clear-sample') {
            $options[] = 'samplePerStyle';
            $options[] = 'showIssues';
        }
        return $options;
    }

    //更新 ssh 2023503
    public function actionUpdate()
    {
        ini_set('memory_limit', '2045M');
        set_time_limit(0);
        $list = OrderClearClass::getAllByCondition(['status' => 2], null, '*', null, true);
        if (!empty($list)) {
            foreach ($list as $clear) {
                $payTime = $clear->payTime ?? '';
                $saleIds = $clear->saleIds ?? '';
                $ids = explode(',', trim($saleIds));
                print_r($ids);
                if (!empty($ids)) {
                    OrderClass::updateByIds($ids, ['clearTime' => $payTime]);
                }
                $purchaseIds = $clear->purchaseIds ?? '';
                $purchaseIds = explode(',', $purchaseIds);
                print_r($purchaseIds);
                if (!empty($purchaseIds)) {
                    PurchaseClass::updateByIds($purchaseIds, ['clearTime' => $payTime]);
                }
                echo "-----------------\n";
            }
        }

    }

    /**
     * 抽样检查 xhOrderCgClear：clearStyle 1/2/3 各抽 N 条（默认 50000）
     *
     * 检查漏补、旧格式等问题；不检 amount=0（由 patch-order-cg-clear-amount 处理）
     *
     * 用法:
     *   php yii clear/check-order-cg-clear-sample
     *   php yii clear/check-order-cg-clear-sample --samplePerStyle=50000
     *   php yii clear/check-order-cg-clear-sample --dryRun=0   # 发现问题顺带漏补
     */
    public function actionCheckOrderCgClearSample()
    {
        if (!$this->isCliOptionPassed('dryRun')) {
            $this->dryRun = 1;
        }
        return $this->runSampleCheckOrderCgClear();
    }

    /**
     * 补写 xhOrderCgClear.amount
     *
     * 1. 从 xhOrderCgClear 查 amount=0
     * 2. 按 orderId/cgId + clearStyle 反查源单 actPrice
     * 3. 按关系行 id 更新（且仍满足 amount=0 才写，避免覆盖已有值）
     *
     * 用法:
     *   php yii clear/patch-order-cg-clear-amount --dryRun=1 --limit=1000
     *   php yii clear/patch-order-cg-clear-amount --reset=1 --batchSize=100 --sleepMs=200
     */
    public function actionPatchOrderCgClearAmount()
    {
        ini_set('memory_limit', '512M');
        set_time_limit(0);

        if ($this->batchSize < 1) {
            $this->stderr("batchSize 必须大于 0\n");
            return ExitCode::UNSPECIFIED_ERROR;
        }

        $this->backfillProgressKey = 'amount';

        if ($this->reset) {
            $this->clearBackfillProgress();
            $this->stdout("已重置补 amount 进度\n");
        }

        $cursorId = $this->loadBackfillProgress();
        $total = 0;
        $updated = 0;
        $skipped = 0;
        $batchNo = 0;

        $this->stdout(sprintf(
            "补 amount | 1查xhOrderCgClear.amount=0 → 2反查actPrice → 3更新 | dryRun=%d batchSize=%d sleepMs=%d limit=%d | id<%s\n",
            (int)$this->dryRun,
            $this->batchSize,
            $this->sleepMs,
            (int)$this->limit,
            $cursorId === null ? 'max' : (string)$cursorId
        ));

        while (true) {
            if ($this->limit > 0 && $total >= $this->limit) {
                break;
            }
            $fetchLimit = $this->batchSize;
            if ($this->limit > 0) {
                $fetchLimit = min($fetchLimit, $this->limit - $total);
                if ($fetchLimit < 1) {
                    break;
                }
            }

            $rows = OrderCgClearClass::fetchZeroAmountRelationBatch($cursorId, $fetchLimit);
            if (empty($rows)) {
                break;
            }
            $batchNo++;
            $batchMinId = null;

            $result = OrderCgClearClass::patchZeroAmountRows($rows, (bool)$this->dryRun, (bool)$this->dryRun);
            $batchUpdated = intval($result['updated'] ?? 0);
            $batchSkipped = intval($result['skipped'] ?? 0);
            if ($this->dryRun && !empty($result['details'])) {
                foreach ($result['details'] as $detail) {
                    if (($detail['status'] ?? '') === 'would_update') {
                        $this->stdout(sprintf(
                            "  预览 id=%d clearId=%d orderId=%d cgId=%d => amount=%s\n",
                            (int)$detail['id'],
                            (int)$detail['clearId'],
                            (int)$detail['orderId'],
                            (int)$detail['cgId'],
                            $detail['amount']
                        ));
                    }
                }
            }
            $updated += $batchUpdated;
            $skipped += $batchSkipped;
            $total += count($rows);

            foreach ($rows as $row) {
                $batchMinId = intval($row['id']);
                $cursorId = $batchMinId;
            }

            if (!$this->dryRun && $batchMinId !== null) {
                $this->saveBackfillProgress($batchMinId);
            }

            $this->stdout(sprintf(
                "第 %d 批：amount=0 行 %d 条，补写 %d，跳过 %d，checkpoint id=%d\n",
                $batchNo,
                count($rows),
                $batchUpdated,
                $batchSkipped,
                (int)$batchMinId
            ));

            if (count($rows) < $fetchLimit) {
                break;
            }
            $this->sleepBetweenBatch();
        }

        if (!$this->dryRun && $cursorId !== null) {
            $this->stdout("进度已保存，下次从 id < {$cursorId} 继续\n");
        }
        $this->stdout(sprintf(
            "完成：处理 amount=0 行 %d 条，补写 %d，跳过 %d\n",
            $total,
            $updated,
            $skipped
        ));
        return ExitCode::OK;
    }

    /**
     * 漏补 xhOrderCgClear：clearStyle 1(hd2Gys) + 2(gys2Hd) + 3(gys2KmGys) 统一扫描
     *
     * 仅处理：无关系行 / 关系行不足 / amount 为空 / gys2KmGys 旧格式；已完整则跳过。
     *
     * 用法:
     *   php yii clear/backfill-order-cg-clear --dryRun=1 --limit=1000
     *   php yii clear/backfill-order-cg-clear --reset=1 --batchSize=50 --sleepMs=200
     *   php yii clear/backfill-order-cg-clear --clearId=123
     */
    public function actionBackfillOrderCgClear()
    {
        return $this->runBackfillOrderCgClear(
            OrderCgClearClass::getRelationClearStyles(),
            'all',
            'hd2Gys(1)+gys2Hd(2)+gys2KmGys(3)',
            true
        );
    }

    /**
     * @deprecated 请改用 backfill-order-cg-clear（已合并 1+2+3）
     */
    public function actionBackfillOrderCgClearHdGys()
    {
        $this->stderr("已合并至: php yii clear/backfill-order-cg-clear\n");
        return $this->runBackfillOrderCgClear(
            [
                intval(dict::getDict('clearStyle', 'hd2Gys')),
                intval(dict::getDict('clearStyle', 'gys2Hd')),
            ],
            'hd_gys',
            'hd2Gys(1)+gys2Hd(2)',
            false
        );
    }

    /**
     * @deprecated 请改用 backfill-order-cg-clear（已合并 1+2+3）
     */
    public function actionBackfillOrderCgClearKmGys()
    {
        $this->stderr("已合并至: php yii clear/backfill-order-cg-clear\n");
        return $this->runBackfillOrderCgClear(
            [intval(dict::getDict('clearStyle', 'gys2KmGys'))],
            'km_gys',
            'gys2KmGys(3)',
            true
        );
    }

    /**
     * 迁移 gys2KmGys 关系行：orderId/cgId 旧格式 -> cgId 存 xhGhsCgOrder、orderId=0
     *
     * 用法:
     *   php yii clear/migrate-gys2-km-gys-order-cg-clear --dryRun=1
     *   php yii clear/migrate-gys2-km-gys-order-cg-clear
     *
     * 注意：若仍存在唯一索引 order_id_clear_id(clearId,orderId)，请先执行 rebuild-order-cg-clear-index
     */
    public function actionMigrateGys2KmGysOrderCgClear()
    {
        set_time_limit(0);
        $this->stdout("迁移 gys2KmGys 关系行 | dryRun={$this->dryRun} batchSize={$this->batchSize}\n");
        $count = OrderCgClearClass::migrateLegacyGys2KmGysRows((bool)$this->dryRun, $this->batchSize, $this->sleepMs);
        $this->stdout(($this->dryRun ? '预览' : '完成') . "：处理 {$count} 行\n");
        return ExitCode::OK;
    }

    /**
     * 重建 xhOrderCgClear 唯一索引为 (clearId, cgId, orderId)
     *
     * 用法:
     *   php yii clear/rebuild-order-cg-clear-index --dryRun=1
     *   php yii clear/rebuild-order-cg-clear-index
     */
    public function actionRebuildOrderCgClearIndex()
    {
        set_time_limit(0);
        if ($this->dryRun) {
            $info = OrderCgClearClass::rebuildRelationUniqueIndex(true);
            $this->stdout(sprintf(
                "预览：旧索引=%s 新索引=%s 待迁移旧格式行=%d\n",
                !empty($info['hasLegacyIndex']) ? '有' : '无',
                !empty($info['hasNewIndex']) ? '有' : '无',
                (int)($info['legacyRows'] ?? 0)
            ));
            return ExitCode::OK;
        }
        $this->stdout("开始重建唯一索引 uk_clear_cg_order(clearId,cgId,orderId)\n");
        $result = OrderCgClearClass::rebuildRelationUniqueIndex(false);
        $this->stdout(sprintf(
            "完成：删旧索引=%s 建新索引=%s 迁移旧格式行=%d\n",
            !empty($result['droppedLegacyIndex']) ? '是' : '否',
            !empty($result['addedNewIndex']) ? '是' : '否',
            (int)($result['migratedLegacyRows'] ?? 0)
        ));
        return ExitCode::OK;
    }

    /**
     * clearStyle 1/2/3 各抽样 samplePerStyle 条，检查关系表漏补等问题（不含 amount=0）
     */
    private function runSampleCheckOrderCgClear()
    {
        ini_set('memory_limit', '512M');
        set_time_limit(0);

        $sampleSize = intval($this->samplePerStyle);
        if ($sampleSize < 1) {
            $this->stderr("samplePerStyle 必须大于 0\n");
            return ExitCode::UNSPECIFIED_ERROR;
        }

        $styleLabels = [
            intval(dict::getDict('clearStyle', 'hd2Gys')) => 'hd2Gys(1)',
            intval(dict::getDict('clearStyle', 'gys2Hd')) => 'gys2Hd(2)',
            intval(dict::getDict('clearStyle', 'gys2KmGys')) => 'gys2KmGys(3)',
        ];

        $this->stdout(sprintf(
            "抽样检查 xhOrderCgClear | 各 clearStyle 抽 %d 条(id DESC) | 不检 amount=0 | dryRun=%d | 最多展示 %d 条问题明细\n",
            $sampleSize,
            (int)$this->dryRun,
            (int)$this->showIssues
        ));

        $totalSampled = 0;
        $totalIssues = 0;
        $totalFixed = 0;
        $allIssues = [];
        $reasonStats = [];

        foreach ($styleLabels as $clearStyle => $label) {
            $list = $this->fetchClearSampleByStyle($clearStyle, $sampleSize);
            $sampled = count($list);
            $totalSampled += $sampled;

            if ($sampled === 0) {
                $this->stdout("{$label}：无数据\n");
                continue;
            }

            $clearIds = array_map('intval', array_column($list, 'id'));
            $relationCountMap = [];
            $legacyGys2KmGysClearIds = [];
            foreach (array_chunk($clearIds, 2000) as $clearIdChunk) {
                $relationCountMap += OrderCgClearClass::getRelationCountMap($clearIdChunk);
                $legacyGys2KmGysClearIds = array_merge(
                    $legacyGys2KmGysClearIds,
                    OrderCgClearClass::getClearIdsWithLegacyGys2KmGysRows($clearIdChunk)
                );
            }
            $legacyGys2KmGysClearIds = array_values(array_unique(array_map('intval', $legacyGys2KmGysClearIds)));

            $styleIssues = 0;
            $styleFixed = 0;

            foreach ($list as $clear) {
                $currentClearId = intval($clear['id'] ?? 0);
                $relationCount = $relationCountMap[$currentClearId] ?? 0;
                $issue = OrderCgClearClass::detectBackfillIssue(
                    $clear,
                    $relationCount,
                    [],
                    $legacyGys2KmGysClearIds,
                    false
                );
                if ($issue === null) {
                    continue;
                }

                $styleIssues++;
                $totalIssues++;
                $reason = $issue['reason'];
                $reasonStats[$reason] = ($reasonStats[$reason] ?? 0) + 1;

                $allIssues[] = [
                    'clearId' => $currentClearId,
                    'clearStyle' => $clearStyle,
                    'label' => $label,
                    'reason' => $reason,
                    'relationCount' => $issue['relationCount'],
                    'expected' => $issue['expected'],
                ];

                if (!$this->dryRun) {
                    $count = OrderCgClearClass::backfillFromClear($clear, true);
                    if ($count > 0) {
                        $styleFixed++;
                        $totalFixed += $count;
                    }
                }
            }

            $this->stdout(sprintf(
                "%s：抽样 %d 条，问题 %d 条%s\n",
                $label,
                $sampled,
                $styleIssues,
                !$this->dryRun ? "，已写入关系 {$styleFixed} 行" : ''
            ));
        }

        if (!empty($reasonStats)) {
            $this->stdout("问题类型汇总：\n");
            foreach ($reasonStats as $reason => $cnt) {
                $this->stdout("  {$reason}：{$cnt}\n");
            }
        }

        $showLimit = max(0, intval($this->showIssues));
        if ($showLimit > 0 && !empty($allIssues)) {
            $this->stdout("问题明细（前 {$showLimit} 条）：\n");
            foreach (array_slice($allIssues, 0, $showLimit) as $item) {
                $this->stdout(sprintf(
                    "  clearId=%d %s %s 已有=%d 应有≈%d\n",
                    $item['clearId'],
                    $item['label'],
                    $item['reason'],
                    $item['relationCount'],
                    $item['expected']
                ));
            }
            if (count($allIssues) > $showLimit) {
                $this->stdout('  ... 还有 ' . (count($allIssues) - $showLimit) . " 条未展示\n");
            }
        }

        $this->stdout(sprintf(
            "检查完成：共抽样 %d 条，发现问题结账单 %d 个%s\n",
            $totalSampled,
            $totalIssues,
            $totalIssues === 0 ? '，未发现漏补' : ''
        ));
        if (!$this->dryRun && $totalFixed > 0) {
            $this->stdout("已漏补写入关系 {$totalFixed} 行\n");
        }
        if ($this->dryRun && $totalIssues > 0) {
            $this->stdout("请加 --dryRun=0 执行漏补，或运行 backfill-order-cg-clear --reset=1 全量漏补\n");
        }
        if ($totalIssues === 0) {
            $this->stdout("amount=0 请单独跑: php yii clear/patch-order-cg-clear-amount\n");
        }

        return $totalIssues > 0 ? ExitCode::UNSPECIFIED_ERROR : ExitCode::OK;
    }

    private function fetchClearSampleByStyle($clearStyle, $limit)
    {
        return Yii::$app->db->createCommand(
            'SELECT id, orderSn, saleIds, purchaseIds, status, clearStyle FROM ' . Clear::tableName()
            . ' WHERE clearStyle = :style ORDER BY id DESC LIMIT ' . intval($limit)
        )->bindValue(':style', intval($clearStyle))->queryAll();
    }

    /**
     * @param int[] $clearStyles
     * @param string $progressKey 进度文件后缀
     * @param string $label 日志标签
     * @param bool $checkLegacyGys2KmGys 是否检测 gys2KmGys 旧格式行
     */
    private function runBackfillOrderCgClear(array $clearStyles, $progressKey, $label, $checkLegacyGys2KmGys)
    {
        ini_set('memory_limit', '512M');
        set_time_limit(0);

        if ($this->batchSize < 1) {
            $this->stderr("batchSize 必须大于 0\n");
            return ExitCode::UNSPECIFIED_ERROR;
        }
        if ($this->sleepMs < 0) {
            $this->stderr("sleepMs 不能小于 0\n");
            return ExitCode::UNSPECIFIED_ERROR;
        }

        $this->backfillProgressKey = $progressKey;

        if ($this->reset && $this->clearId <= 0) {
            $this->clearBackfillProgress();
            $this->stdout("已重置回填进度 [{$progressKey}]\n");
        }

        $cursorId = $this->clearId > 0 ? null : $this->loadBackfillProgress();
        $total = 0;
        $inserted = 0;
        $skipped = 0;
        $needBackfill = 0;
        $batchNo = 0;
        $styleStats = [];

        $this->stdout(sprintf(
            "开始漏补 xhOrderCgClear [%s] | id DESC 分批 | dryRun=%d batchSize=%d sleepMs=%d limit=%d | 续跑 id<%s | 进度=%s\n",
            $label,
            (int)$this->dryRun,
            $this->batchSize,
            $this->sleepMs,
            (int)$this->limit,
            $cursorId === null ? 'max' : (string)$cursorId,
            $this->getBackfillProgressFile()
        ));

        while (true) {
            if ($this->limit > 0 && $total >= $this->limit) {
                break;
            }
            $fetchLimit = $this->batchSize;
            if ($this->limit > 0) {
                $fetchLimit = min($fetchLimit, $this->limit - $total);
                if ($fetchLimit < 1) {
                    break;
                }
            }

            $list = $this->fetchClearBatch($clearStyles, $cursorId, $fetchLimit);
            if (empty($list)) {
                break;
            }
            $batchNo++;
            $batchMinId = null;
            $clearIds = array_map('intval', array_column($list, 'id'));
            $emptyAmountClearIds = OrderCgClearClass::getClearIdsWithEmptyAmount($clearIds);
            $legacyGys2KmGysClearIds = $checkLegacyGys2KmGys
                ? OrderCgClearClass::getClearIdsWithLegacyGys2KmGysRows($clearIds)
                : [];
            $relationCountMap = OrderCgClearClass::getRelationCountMap($clearIds);

            foreach ($list as $clear) {
                $total++;
                $currentClearId = intval($clear['id'] ?? 0);
                $clearStyle = intval($clear['clearStyle'] ?? 0);
                $batchMinId = $currentClearId;
                $cursorId = $currentClearId;

                if (!isset($styleStats[$clearStyle])) {
                    $styleStats[$clearStyle] = ['scan' => 0, 'need' => 0, 'skip' => 0];
                }
                $styleStats[$clearStyle]['scan']++;

                $saleIds = trim($clear['saleIds'] ?? '');
                $purchaseIds = trim($clear['purchaseIds'] ?? '');
                if ($saleIds === '' && $purchaseIds === '') {
                    $skipped++;
                    $styleStats[$clearStyle]['skip']++;
                    continue;
                }

                $relationCount = $relationCountMap[$currentClearId] ?? 0;
                if (OrderCgClearClass::shouldSkipBackfill(
                    $clear,
                    $relationCount,
                    $emptyAmountClearIds,
                    $legacyGys2KmGysClearIds
                )) {
                    $skipped++;
                    $styleStats[$clearStyle]['skip']++;
                    continue;
                }

                $needBackfill++;
                $styleStats[$clearStyle]['need']++;

                if ($this->dryRun) {
                    $expected = OrderCgClearClass::expectedRelationCountFromClear($clear);
                    $this->stdout(sprintf(
                        "需漏补 clearId=%d clearStyle=%d 已有关系=%d 应有≈%d\n",
                        $currentClearId,
                        $clearStyle,
                        $relationCount,
                        $expected
                    ));
                    if ($this->logEvery > 0 && $needBackfill % $this->logEvery === 0) {
                        $this->stdout("dryRun 需漏补累计 {$needBackfill} 条\n");
                    }
                    continue;
                }

                $count = OrderCgClearClass::backfillFromClear($clear, true);
                if ($count > 0) {
                    $inserted += $count;
                } else {
                    $skipped++;
                    $styleStats[$clearStyle]['skip']++;
                    $styleStats[$clearStyle]['need']--;
                    $needBackfill--;
                }
                if ($this->logEvery > 0 && $total % $this->logEvery === 0) {
                    $this->stdout("进度：扫描 {$total}，需漏补 {$needBackfill}，写入关系 {$inserted} 行，跳过 {$skipped}，当前 clearId={$currentClearId}\n");
                }
            }

            if (!$this->dryRun && $this->clearId <= 0 && $batchMinId !== null) {
                $this->saveBackfillProgress($batchMinId);
            }

            $this->stdout(sprintf(
                "第 %d 批完成：本批 %d 条，累计扫描 %d，需漏补 %d，写入关系 %d 行，跳过 %d，checkpoint id=%d\n",
                $batchNo,
                count($list),
                $total,
                $needBackfill,
                $inserted,
                $skipped,
                (int)$batchMinId
            ));

            if ($this->clearId > 0 || count($list) < $fetchLimit) {
                break;
            }
            $this->sleepBetweenBatch();
        }

        if (!$this->dryRun && $this->clearId <= 0 && $cursorId !== null) {
            $this->stdout("进度已保存，下次从 id < {$cursorId} 继续\n");
        }
        $this->stdout(sprintf(
            "全部完成 [%s]：扫描 %d 条 xhClear，需漏补 %d 条，写入 %d 行关系，跳过 %d 条\n",
            $label,
            $total,
            $needBackfill,
            $inserted,
            $skipped
        ));
        if (!empty($styleStats)) {
            ksort($styleStats);
            foreach ($styleStats as $style => $stat) {
                $this->stdout(sprintf(
                    "  clearStyle=%d：扫描 %d，需漏补 %d，跳过 %d\n",
                    $style,
                    (int)$stat['scan'],
                    (int)$stat['need'],
                    (int)$stat['skip']
                ));
            }
        }
        return ExitCode::OK;
    }

    /**
     * 按主键倒序轻量拉取一批 xhClear（只查回填所需字段，LIMIT 走主键索引）
     */
    private function fetchClearBatch(array $clearStyles, $cursorId, $limit)
    {
        $clearStyles = array_values(array_unique(array_filter(array_map('intval', $clearStyles))));
        if (empty($clearStyles)) {
            return [];
        }
        $inParts = [];
        $params = [];
        foreach ($clearStyles as $idx => $style) {
            $key = ':s' . $idx;
            $inParts[] = $key;
            $params[$key] = $style;
        }
        $inSql = implode(',', $inParts);

        if ($this->clearId > 0) {
            $params[':id'] = intval($this->clearId);
            $row = Yii::$app->db->createCommand(
                'SELECT id, orderSn, saleIds, purchaseIds, status, clearStyle FROM ' . Clear::tableName()
                . ' WHERE id = :id AND clearStyle IN (' . $inSql . ') LIMIT 1'
            )->bindValues($params)->queryOne();
            return $row ? [$row] : [];
        }

        $sql = 'SELECT id, orderSn, saleIds, purchaseIds, status, clearStyle FROM ' . Clear::tableName()
            . ' WHERE clearStyle IN (' . $inSql . ')';
        if ($cursorId !== null) {
            $sql .= ' AND id < :cursorId';
            $params[':cursorId'] = intval($cursorId);
        }
        $sql .= ' ORDER BY id DESC LIMIT ' . intval($limit);
        return Yii::$app->db->createCommand($sql)->bindValues($params)->queryAll();
    }

    private function sleepBetweenBatch()
    {
        if ($this->sleepMs > 0) {
            usleep($this->sleepMs * 1000);
        }
    }

    private function getBackfillProgressFile()
    {
        $key = $this->backfillProgressKey !== '' ? $this->backfillProgressKey : 'all';
        return Yii::getAlias('@console/runtime/order_cg_clear_backfill_' . $key . '.last_id');
    }

    private function loadBackfillProgress()
    {
        $file = $this->getBackfillProgressFile();
        if (!is_file($file)) {
            return null;
        }
        $id = intval(trim((string)file_get_contents($file)));
        return $id > 0 ? $id : null;
    }

    private function saveBackfillProgress($clearId)
    {
        if ($this->dryRun || $this->clearId > 0) {
            return;
        }
        $clearId = intval($clearId);
        if ($clearId <= 0) {
            return;
        }
        $file = $this->getBackfillProgressFile();
        $dir = dirname($file);
        if (!is_dir($dir)) {
            mkdir($dir, 0777, true);
        }
        file_put_contents($file, (string)$clearId);
    }

    private function clearBackfillProgress()
    {
        $file = $this->getBackfillProgressFile();
        if (is_file($file)) {
            unlink($file);
        }
    }

    private function isCliOptionPassed($name)
    {
        foreach ($_SERVER['argv'] ?? [] as $arg) {
            if ($arg === '--' . $name || strpos($arg, '--' . $name . '=') === 0) {
                return true;
            }
        }
        return false;
    }

}