AiDOPWarehouse/server/Admin.NET.Web.Entry/UpdateScripts/1.0.538.sql

-- ============================================================================
-- 1.0.538 · API_INBOUND WP-D/E：S0 五表来源列 + T8/员工入站实体（号段避让远端已占用的 1.0.530）
--
-- 幂等：扩列 information_schema + PREPARE 自包含；实体 INSERT ... WHERE NOT EXISTS。
-- AutoVersionUpdate 按 ; 拆句、无事务。
-- ============================================================================

SET @exist := (SELECT COUNT(1) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='ItemMaster' AND COLUMN_NAME='source_system');
SET @sql := IF(@exist=0, "ALTER TABLE ItemMaster ADD COLUMN source_system VARCHAR(50) NULL COMMENT '入站来源'", 'SELECT 1');
PREPARE s FROM @sql;
EXECUTE s;
DEALLOCATE PREPARE s;

SET @exist := (SELECT COUNT(1) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='ItemMaster' AND COLUMN_NAME='source_updated_at');
SET @sql := IF(@exist=0, "ALTER TABLE ItemMaster ADD COLUMN source_updated_at DATETIME NULL COMMENT '入站源更新时间'", 'SELECT 1');
PREPARE s FROM @sql;
EXECUTE s;
DEALLOCATE PREPARE s;

SET @exist := (SELECT COUNT(1) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='CustMaster' AND COLUMN_NAME='source_system');
SET @sql := IF(@exist=0, "ALTER TABLE CustMaster ADD COLUMN source_system VARCHAR(50) NULL COMMENT '入站来源'", 'SELECT 1');
PREPARE s FROM @sql;
EXECUTE s;
DEALLOCATE PREPARE s;

SET @exist := (SELECT COUNT(1) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='CustMaster' AND COLUMN_NAME='source_updated_at');
SET @sql := IF(@exist=0, "ALTER TABLE CustMaster ADD COLUMN source_updated_at DATETIME NULL COMMENT '入站源更新时间'", 'SELECT 1');
PREPARE s FROM @sql;
EXECUTE s;
DEALLOCATE PREPARE s;

SET @exist := (SELECT COUNT(1) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='SuppMaster' AND COLUMN_NAME='source_system');
SET @sql := IF(@exist=0, "ALTER TABLE SuppMaster ADD COLUMN source_system VARCHAR(50) NULL COMMENT '入站来源'", 'SELECT 1');
PREPARE s FROM @sql;
EXECUTE s;
DEALLOCATE PREPARE s;

SET @exist := (SELECT COUNT(1) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='SuppMaster' AND COLUMN_NAME='source_updated_at');
SET @sql := IF(@exist=0, "ALTER TABLE SuppMaster ADD COLUMN source_updated_at DATETIME NULL COMMENT '入站源更新时间'", 'SELECT 1');
PREPARE s FROM @sql;
EXECUTE s;
DEALLOCATE PREPARE s;

SET @exist := (SELECT COUNT(1) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='LocationMaster' AND COLUMN_NAME='source_system');
SET @sql := IF(@exist=0, "ALTER TABLE LocationMaster ADD COLUMN source_system VARCHAR(50) NULL COMMENT '入站来源'", 'SELECT 1');
PREPARE s FROM @sql;
EXECUTE s;
DEALLOCATE PREPARE s;

SET @exist := (SELECT COUNT(1) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='LocationMaster' AND COLUMN_NAME='source_updated_at');
SET @sql := IF(@exist=0, "ALTER TABLE LocationMaster ADD COLUMN source_updated_at DATETIME NULL COMMENT '入站源更新时间'", 'SELECT 1');
PREPARE s FROM @sql;
EXECUTE s;
DEALLOCATE PREPARE s;

SET @exist := (SELECT COUNT(1) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='EmployeeMaster' AND COLUMN_NAME='source_system');
SET @sql := IF(@exist=0, "ALTER TABLE EmployeeMaster ADD COLUMN source_system VARCHAR(50) NULL COMMENT '入站来源'", 'SELECT 1');
PREPARE s FROM @sql;
EXECUTE s;
DEALLOCATE PREPARE s;

SET @exist := (SELECT COUNT(1) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='EmployeeMaster' AND COLUMN_NAME='source_updated_at');
SET @sql := IF(@exist=0, "ALTER TABLE EmployeeMaster ADD COLUMN source_updated_at DATETIME NULL COMMENT '入站源更新时间'", 'SELECT 1');
PREPARE s FROM @sql;
EXECUTE s;
DEALLOCATE PREPARE s;

INSERT INTO mdp_entity
  (tenant_id, source_id, entity_code, entity_name, entity_type, source_table_name, target_table_name,
   sync_mode, batch_size, biz_key_expr, status, inbound_enabled, inbound_max_rows, stale_guard_enabled,
   remark, create_time, update_time)
SELECT 797403760988229, s.id, 'S5_INVENTORY_TXN', '库存流水(入站草案)', 'TABLE', 'S5_INVENTORY_TXN', 'mdp_stg_t8_kc_tz_list',
       'FULL', 500, 'Id', 1, 1, 500, 1,
       'v1-draft Q7方案A：拍平行落 tz_list，头字段冗余在 raw_data inbound_delete=unsupported', NOW(), NOW()
FROM mdp_source s
WHERE s.source_code='TEST_INBOUND_ERP'
  AND NOT EXISTS (SELECT 1 FROM mdp_entity x WHERE x.entity_code='S5_INVENTORY_TXN');

INSERT INTO mdp_entity
  (tenant_id, source_id, entity_code, entity_name, entity_type, source_table_name, target_table_name,
   sync_mode, batch_size, biz_key_expr, status, inbound_enabled, inbound_max_rows, stale_guard_enabled,
   remark, create_time, update_time)
SELECT 797403760988229, s.id, 'S5_INVENTORY_OPENING_BALANCE', '库存期初(入站草案)', 'TABLE', 'S5_INVENTORY_OPENING_BALANCE', 'mdp_stg_t8_kc_tz_list',
       'FULL', 500, 'Id', 1, 1, 500, 1,
       'v1-draft 与流水同分表、source_table 隔离 inbound_delete=unsupported', NOW(), NOW()
FROM mdp_source s
WHERE s.source_code='TEST_INBOUND_ERP'
  AND NOT EXISTS (SELECT 1 FROM mdp_entity x WHERE x.entity_code='S5_INVENTORY_OPENING_BALANCE');

INSERT INTO mdp_entity
  (tenant_id, source_id, entity_code, entity_name, entity_type, source_table_name, target_table_name,
   sync_mode, batch_size, biz_key_expr, status, inbound_enabled, inbound_max_rows, stale_guard_enabled,
   remark, create_time, update_time)
SELECT 797403760988229, s.id, 'S7_FINISHED_ONHAND', '成品结存(入站草案)', 'TABLE', 'S7_FINISHED_ONHAND', 'mdp_stg_t8_kc_zj_list',
       'FULL', 500, 'Id', 1, 1, 500, 1,
       'v1-draft 落 kc_zj_list inbound_delete=unsupported', NOW(), NOW()
FROM mdp_source s
WHERE s.source_code='TEST_INBOUND_ERP'
  AND NOT EXISTS (SELECT 1 FROM mdp_entity x WHERE x.entity_code='S7_FINISHED_ONHAND');

INSERT INTO mdp_entity
  (tenant_id, source_id, entity_code, entity_name, entity_type, source_table_name, target_table_name,
   sync_mode, batch_size, biz_key_expr, status, inbound_enabled, inbound_max_rows, stale_guard_enabled,
   remark, create_time, update_time)
SELECT 797403760988229, s.id, 'S7_FINISHED_OPENING_BALANCE', '成品期初(入站草案)', 'TABLE', 'S7_FINISHED_OPENING_BALANCE', 'mdp_stg_t8_kc_zj_list',
       'FULL', 500, 'Id', 1, 1, 500, 1,
       'v1-draft inbound_delete=unsupported', NOW(), NOW()
FROM mdp_source s
WHERE s.source_code='TEST_INBOUND_ERP'
  AND NOT EXISTS (SELECT 1 FROM mdp_entity x WHERE x.entity_code='S7_FINISHED_OPENING_BALANCE');

INSERT INTO mdp_entity
  (tenant_id, source_id, entity_code, entity_name, entity_type, source_table_name, target_table_name,
   sync_mode, batch_size, biz_key_expr, status, inbound_enabled, inbound_max_rows, stale_guard_enabled,
   remark, create_time, update_time)
SELECT 797403760988229, s.id, 'MDM_EMPLOYEE_HEADCOUNT', '雇员主数据(入站草案)', 'TABLE', 'EmployeeMaster', 'mdp_stg_employee',
       'FULL', 500, 'Domain,Employee', 1, 1, 500, 1,
       'v1-draft 落 mdp_stg_employee inbound_delete=unsupported', NOW(), NOW()
FROM mdp_source s
WHERE s.source_code='TEST_INBOUND_ERP'
  AND NOT EXISTS (SELECT 1 FROM mdp_entity x WHERE x.entity_code='MDM_EMPLOYEE_HEADCOUNT');

INSERT INTO mdp_inbound_grant
  (tenant_id, access_key, entity_code, factory_id, source_code, rate_limit_per_min, batch_row_limit, status, remark, create_time, update_time)
SELECT 797403760988229, 'TESTKPARTNER', 'S5_INVENTORY_TXN', NULL, 'TEST_INBOUND_ERP', 60, 500, 1, 'WP-E 联调', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM mdp_inbound_grant WHERE access_key='TESTKPARTNER' AND entity_code='S5_INVENTORY_TXN');

INSERT INTO mdp_inbound_grant
  (tenant_id, access_key, entity_code, factory_id, source_code, rate_limit_per_min, batch_row_limit, status, remark, create_time, update_time)
SELECT 797403760988229, 'TESTKPARTNER', 'S5_INVENTORY_OPENING_BALANCE', NULL, 'TEST_INBOUND_ERP', 60, 500, 1, 'WP-E 联调', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM mdp_inbound_grant WHERE access_key='TESTKPARTNER' AND entity_code='S5_INVENTORY_OPENING_BALANCE');

INSERT INTO mdp_inbound_grant
  (tenant_id, access_key, entity_code, factory_id, source_code, rate_limit_per_min, batch_row_limit, status, remark, create_time, update_time)
SELECT 797403760988229, 'TESTKPARTNER', 'S7_FINISHED_ONHAND', NULL, 'TEST_INBOUND_ERP', 60, 500, 1, 'WP-E 联调', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM mdp_inbound_grant WHERE access_key='TESTKPARTNER' AND entity_code='S7_FINISHED_ONHAND');

INSERT INTO mdp_inbound_grant
  (tenant_id, access_key, entity_code, factory_id, source_code, rate_limit_per_min, batch_row_limit, status, remark, create_time, update_time)
SELECT 797403760988229, 'TESTKPARTNER', 'S7_FINISHED_OPENING_BALANCE', NULL, 'TEST_INBOUND_ERP', 60, 500, 1, 'WP-E 联调', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM mdp_inbound_grant WHERE access_key='TESTKPARTNER' AND entity_code='S7_FINISHED_OPENING_BALANCE');

INSERT INTO mdp_inbound_grant
  (tenant_id, access_key, entity_code, factory_id, source_code, rate_limit_per_min, batch_row_limit, status, remark, create_time, update_time)
SELECT 797403760988229, 'TESTKPARTNER', 'MDM_EMPLOYEE_HEADCOUNT', NULL, 'TEST_INBOUND_ERP', 60, 500, 1, 'WP-E 联调', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM mdp_inbound_grant WHERE access_key='TESTKPARTNER' AND entity_code='MDM_EMPLOYEE_HEADCOUNT');