huahuibao/scripts/sql/add_clear_audit_fields.sql

-- =============================================================================
-- 充值销账审计字段 — 对照零售 xhBalanceChange.settleId / settleAmount
-- =============================================================================
-- 【用途】批发充值、余额变动可记录「本笔来款中有多少用于销挂账」，接口返回 becomeBalance。
-- 【业务类】CustomPaymentAllocateService::patchRechargeAudit
-- 【列表】CustomBalanceChangeClass::getChangeList 计算 becomeBalance
--
-- 执行前确认列不存在（已存在则跳过该表）：
--   SELECT TABLE_NAME, COLUMN_NAME FROM information_schema.COLUMNS
--   WHERE TABLE_SCHEMA = DATABASE()
--     AND TABLE_NAME IN ('xhCustomBalanceChange','xhGhsBalanceChange','xhCustomRecharge')
--     AND COLUMN_NAME IN ('clearId','clearSn','clearAmount','giveAmount');
--
-- 依赖：建议先执行 add_balance_merged.sql（挂账已并入净 balance）
-- 锁表：ALGORITHM=INPLACE, LOCK=NONE（同 add_balance_merged.sql）
-- =============================================================================

SET SESSION lock_wait_timeout = 10;

ALTER TABLE `xhCustomBalanceChange`
    ADD COLUMN `clearId` int(11) NOT NULL DEFAULT 0 COMMENT '关联结账单ID(批发OrderClear)',
    ADD COLUMN `clearSn` varchar(64) NOT NULL DEFAULT '' COMMENT '结账单号',
    ADD COLUMN `clearAmount` decimal(12,2) NOT NULL DEFAULT 0.00 COMMENT '本笔来款中用于销挂账的金额',
    ADD COLUMN `giveAmount` decimal(12,2) NOT NULL DEFAULT 0.00 COMMENT '赠送金额(批发预留,现多为0)',
    ALGORITHM = INPLACE,
    LOCK = NONE;

ALTER TABLE `xhGhsBalanceChange`
    ADD COLUMN `clearId` int(11) NOT NULL DEFAULT 0 COMMENT '关联结账单ID',
    ADD COLUMN `clearSn` varchar(64) NOT NULL DEFAULT '' COMMENT '结账单号',
    ADD COLUMN `clearAmount` decimal(12,2) NOT NULL DEFAULT 0.00 COMMENT '本笔来款中用于销挂账的金额',
    ADD COLUMN `giveAmount` decimal(12,2) NOT NULL DEFAULT 0.00 COMMENT '赠送金额(批发预留)',
    ALGORITHM = INPLACE,
    LOCK = NONE;

ALTER TABLE `xhCustomRecharge`
    ADD COLUMN `clearId` int(11) NOT NULL DEFAULT 0 COMMENT '关联结账单ID',
    ADD COLUMN `clearSn` varchar(64) NOT NULL DEFAULT '' COMMENT '结账单号',
    ADD COLUMN `clearAmount` decimal(12,2) NOT NULL DEFAULT 0.00 COMMENT '本笔充值中用于销挂账的金额',
    ALGORITHM = INPLACE,
    LOCK = NONE;