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

-- 1.0.356：数据库层阻断旧节点再次写入 S1 无效租户/工厂作用域。
UPDATE `mdp_stg_so` SET `factory_id`=NULL
WHERE `tenant_id` > 0 AND `factory_id`=`tenant_id`;
UPDATE `mdp_std_so` SET `factory_id`=1
WHERE `tenant_id` > 0 AND `factory_id`=`tenant_id`;

DELETE FROM `mdp_stg_so`
WHERE `id` IN (
  SELECT `id` FROM (
    SELECT `id`,
           ROW_NUMBER() OVER (
             PARTITION BY `source_system`,`source_table`,`source_biz_key`
             ORDER BY `update_time` DESC,`id` DESC) AS `rn`
    FROM `mdp_stg_so` IGNORE INDEX (`uk_source_key`)
  ) duplicate_rows
  WHERE `rn` > 1
);

DELETE FROM `mdp_stg_so` WHERE `tenant_id` IS NULL OR `tenant_id` IN (0,1,1300000000001);
DELETE FROM `mdp_std_so` WHERE `tenant_id` IS NULL OR `tenant_id` IN (0,1,1300000000001);

DELETE FROM `dwd_ship_trans`
WHERE `id` IN (
  SELECT `id` FROM (
    SELECT `id`,
           ROW_NUMBER() OVER (
             PARTITION BY `tenant_id`,`stat_date`,`order_no`,`order_line`,`item_code`
             ORDER BY `id` DESC) AS `rn`
    FROM `dwd_ship_trans` IGNORE INDEX (`uk_order_line_stat`)
  ) duplicate_rows
  WHERE `rn` > 1
);

DELETE FROM `dwd_requirement_examine_detail`
WHERE `id` IN (
  SELECT `id` FROM (
    SELECT `id`,
           ROW_NUMBER() OVER (
             PARTITION BY `tenant_id`,`row_id`,`calc_batch_id`
             ORDER BY `id` DESC) AS `rn`
    FROM `dwd_requirement_examine_detail` IGNORE INDEX (`uk_dwd_req_exam_detail`)
  ) duplicate_rows
  WHERE `rn` > 1
);

DELETE FROM `dwd_ship_trans` WHERE `tenant_id` IN (0,1,1300000000001) OR `factory_id`=`tenant_id`;
DELETE FROM `dwd_requirement_examine_detail` WHERE `tenant_id` IN (0,1,1300000000001) OR `factory_id`=`tenant_id`;
DELETE FROM `ado_smart_ops_kpi_atomic_day` WHERE `tenant_id` IN (0,1,1300000000001) OR `factory_id`=`tenant_id`;
DELETE FROM `ado_module_dashboard_rebuild_job`
WHERE `tenant_id` IN (0,1,1300000000001) OR `factory_id`=`tenant_id`;

SET @sql := IF(
  (SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
   WHERE CONSTRAINT_SCHEMA=DATABASE() AND TABLE_NAME='mdp_stg_so'
     AND CONSTRAINT_NAME='ck_mdp_stg_so_valid_scope')=0,
  'ALTER TABLE `mdp_stg_so` ADD CONSTRAINT `ck_mdp_stg_so_valid_scope` CHECK (`tenant_id` IS NOT NULL AND `tenant_id` NOT IN (0,1,1300000000001) AND (`factory_id` IS NULL OR `factory_id`<>`tenant_id`))',
  'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
  (SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
   WHERE CONSTRAINT_SCHEMA=DATABASE() AND TABLE_NAME='mdp_std_so'
     AND CONSTRAINT_NAME='ck_mdp_std_so_valid_scope')=0,
  'ALTER TABLE `mdp_std_so` ADD CONSTRAINT `ck_mdp_std_so_valid_scope` CHECK (`tenant_id` IS NOT NULL AND `tenant_id` NOT IN (0,1,1300000000001) AND (`factory_id` IS NULL OR `factory_id`<>`tenant_id`))',
  'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
  (SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
   WHERE CONSTRAINT_SCHEMA=DATABASE() AND TABLE_NAME='dwd_ship_trans'
     AND CONSTRAINT_NAME='ck_dwd_ship_trans_valid_scope')=0,
  'ALTER TABLE `dwd_ship_trans` ADD CONSTRAINT `ck_dwd_ship_trans_valid_scope` CHECK (`tenant_id` NOT IN (0,1,1300000000001) AND (`factory_id` IS NULL OR `factory_id`<>`tenant_id`))',
  'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
  (SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
   WHERE CONSTRAINT_SCHEMA=DATABASE() AND TABLE_NAME='dwd_requirement_examine_detail'
     AND CONSTRAINT_NAME='ck_dwd_requirement_valid_scope')=0,
  'ALTER TABLE `dwd_requirement_examine_detail` ADD CONSTRAINT `ck_dwd_requirement_valid_scope` CHECK (`tenant_id` NOT IN (0,1,1300000000001) AND (`factory_id` IS NULL OR `factory_id`<>`tenant_id`))',
  'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
  (SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
   WHERE CONSTRAINT_SCHEMA=DATABASE() AND TABLE_NAME='ado_smart_ops_kpi_atomic_day'
     AND CONSTRAINT_NAME='ck_kpi_atomic_valid_scope')=0,
  'ALTER TABLE `ado_smart_ops_kpi_atomic_day` ADD CONSTRAINT `ck_kpi_atomic_valid_scope` CHECK (`tenant_id` NOT IN (0,1,1300000000001) AND (`factory_id` IS NULL OR `factory_id`<>`tenant_id`))',
  'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
  (SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
   WHERE CONSTRAINT_SCHEMA=DATABASE() AND TABLE_NAME='ado_module_dashboard_rebuild_job'
     AND CONSTRAINT_NAME='ck_rebuild_job_valid_scope')=0,
  'ALTER TABLE `ado_module_dashboard_rebuild_job` ADD CONSTRAINT `ck_rebuild_job_valid_scope` CHECK (`tenant_id` NOT IN (0,1,1300000000001) AND `factory_id`<>`tenant_id`)',
  'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;