tms-erp-api/doc/sql/transport/blade_receipt_flow_push_20260928.sql

-- 收款认领推送金蝶重构（认领本地化 / A301-3 定时+手动推送 / 按流水归集）
-- 支持重复执行，兼容 20260924 脚本已执行/未执行的环境，与该脚本无执行顺序要求
-- 变更点：
--   1. 收款流水表新增推送域字段（推送状态/失败原因/推送时间/推送编码/金蝶收款单号/回写状态）
--   2. 认领主单移除 awrp_src_code 及其索引（推送编码上移至流水：分摊结算单号拼接）
--   3. 收款流水表新增定时任务扫描索引与 A301-1 回写匹配索引

-- 1. 收款流水表：推送状态
SET @ddl_sql = (
  SELECT IF(COUNT(*) = 0,
    'ALTER TABLE `blade_kingdee_receipt_flow` ADD COLUMN `push_status` varchar(20) COLLATE utf8mb4_general_ci NOT NULL DEFAULT ''none'' COMMENT ''推送状态（none未推送/pushed已推送/failed推送失败）''',
    'SELECT 1')
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'blade_kingdee_receipt_flow' AND COLUMN_NAME = 'push_status'
);
PREPARE ddl_stmt FROM @ddl_sql;
EXECUTE ddl_stmt;
DEALLOCATE PREPARE ddl_stmt;

-- 2. 收款流水表：最近一次推送失败原因
SET @ddl_sql = (
  SELECT IF(COUNT(*) = 0,
    'ALTER TABLE `blade_kingdee_receipt_flow` ADD COLUMN `push_fail_reason` varchar(1000) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT ''最近一次推送失败原因（成功后清空）''',
    'SELECT 1')
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'blade_kingdee_receipt_flow' AND COLUMN_NAME = 'push_fail_reason'
);
PREPARE ddl_stmt FROM @ddl_sql;
EXECUTE ddl_stmt;
DEALLOCATE PREPARE ddl_stmt;

-- 3. 收款流水表：首次推送成功时间
SET @ddl_sql = (
  SELECT IF(COUNT(*) = 0,
    'ALTER TABLE `blade_kingdee_receipt_flow` ADD COLUMN `pushed_time` datetime DEFAULT NULL COMMENT ''首次推送金蝶成功时间''',
    'SELECT 1')
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'blade_kingdee_receipt_flow' AND COLUMN_NAME = 'pushed_time'
);
PREPARE ddl_stmt FROM @ddl_sql;
EXECUTE ddl_stmt;
DEALLOCATE PREPARE ddl_stmt;

-- 4. 收款流水表：推送来源单据编码（A301-3 awrp_src_code，分摊结算单号拼接，A301-1 回写匹配键）
SET @ddl_sql = (
  SELECT IF(COUNT(*) = 0,
    'ALTER TABLE `blade_kingdee_receipt_flow` ADD COLUMN `awrp_src_code` varchar(64) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT ''金蝶推送来源单据编码（分摊结算单号下划线拼接，A301-1回写匹配键）''',
    'SELECT 1')
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'blade_kingdee_receipt_flow' AND COLUMN_NAME = 'awrp_src_code'
);
PREPARE ddl_stmt FROM @ddl_sql;
EXECUTE ddl_stmt;
DEALLOCATE PREPARE ddl_stmt;

-- 5. 收款流水表：金蝶收款单号（推送响应回填 / A301-1 回写推进）
SET @ddl_sql = (
  SELECT IF(COUNT(*) = 0,
    'ALTER TABLE `blade_kingdee_receipt_flow` ADD COLUMN `kingdee_bill_no` varchar(64) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT ''金蝶收款单号''',
    'SELECT 1')
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'blade_kingdee_receipt_flow' AND COLUMN_NAME = 'kingdee_bill_no'
);
PREPARE ddl_stmt FROM @ddl_sql;
EXECUTE ddl_stmt;
DEALLOCATE PREPARE ddl_stmt;

-- 6. 收款流水表：金蝶回写状态（A301-1 回写生命周期）
SET @ddl_sql = (
  SELECT IF(COUNT(*) = 0,
    'ALTER TABLE `blade_kingdee_receipt_flow` ADD COLUMN `kingdee_bill_status` varchar(20) COLLATE utf8mb4_general_ci NOT NULL DEFAULT ''none'' COMMENT ''金蝶回写状态（none未推送/pushed已推送/approved审核通过/received已收款/failed失败）''',
    'SELECT 1')
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'blade_kingdee_receipt_flow' AND COLUMN_NAME = 'kingdee_bill_status'
);
PREPARE ddl_stmt FROM @ddl_sql;
EXECUTE ddl_stmt;
DEALLOCATE PREPARE ddl_stmt;

-- 7. 认领主单：删除 awrp_src_code 索引（列上移至流水表；0924 未执行过则本就无此索引）
SET @ddl_sql = (
  SELECT IF(COUNT(*) > 0,
    'ALTER TABLE `blade_receipt_claim` DROP KEY `idx_receipt_claim_src_code`',
    'SELECT 1')
  FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'blade_receipt_claim' AND INDEX_NAME = 'idx_receipt_claim_src_code'
);
PREPARE ddl_stmt FROM @ddl_sql;
EXECUTE ddl_stmt;
DEALLOCATE PREPARE ddl_stmt;

-- 8. 认领主单：删除 awrp_src_code 列（0924 未执行过则本就无此列）
SET @ddl_sql = (
  SELECT IF(COUNT(*) > 0,
    'ALTER TABLE `blade_receipt_claim` DROP COLUMN `awrp_src_code`',
    'SELECT 1')
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'blade_receipt_claim' AND COLUMN_NAME = 'awrp_src_code'
);
PREPARE ddl_stmt FROM @ddl_sql;
EXECUTE ddl_stmt;
DEALLOCATE PREPARE ddl_stmt;

-- 9. 收款流水表：定时任务扫描索引（push_status IN (none,failed) AND claim_status='claimed' AND bill_id 非空）
SET @ddl_sql = (
  SELECT IF(COUNT(*) = 0,
    'ALTER TABLE `blade_kingdee_receipt_flow` ADD KEY `idx_kingdee_receipt_flow_push_scan` (`tenant_id`,`push_status`,`claim_status`)',
    'SELECT 1')
  FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'blade_kingdee_receipt_flow' AND INDEX_NAME = 'idx_kingdee_receipt_flow_push_scan'
);
PREPARE ddl_stmt FROM @ddl_sql;
EXECUTE ddl_stmt;
DEALLOCATE PREPARE ddl_stmt;

-- 10. 收款流水表：A301-1 回写匹配索引（sourcebillnumber = awrp_src_code）
SET @ddl_sql = (
  SELECT IF(COUNT(*) = 0,
    'ALTER TABLE `blade_kingdee_receipt_flow` ADD KEY `idx_kingdee_receipt_flow_src_code` (`tenant_id`,`awrp_src_code`)',
    'SELECT 1')
  FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'blade_kingdee_receipt_flow' AND INDEX_NAME = 'idx_kingdee_receipt_flow_src_code'
);
PREPARE ddl_stmt FROM @ddl_sql;
EXECUTE ddl_stmt;
DEALLOCATE PREPARE ddl_stmt;

-- 11. 按钮权限：收款流水页「推送金蝶」（父级菜单 2090000000000001260 收款流水，排序 4）
INSERT INTO `blade_menu`
  (`id`,`parent_id`,`code`,`name`,`alias`,`path`,`source`,`sort`,`category`,`action`,`is_open`,`component`,`remark`,`is_deleted`)
VALUES
  (2090000000000001264,2090000000000001260,'receipt_flow_push','推送金蝶','receipt_flow_push','','',4,2,0,1,NULL,'',0)
ON DUPLICATE KEY UPDATE
  `name`=VALUES(`name`),
  `alias`=VALUES(`alias`),
  `is_deleted`=0;