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

-- 1.0.574 开通 S1–S4 / C-4 推送实体与授权。
-- 只新增登记。不删除行，不改已有实体的来源、蛇形贴源表和游标，不改 mdp_tenant_std_source。
-- 唯一的既有行改动：把报工实体的 Pascal 列改成与蛇形列一致。
-- 模板取现有 S5_INVENTORY_TXN 的最早一行；没有模板则本脚本不插入，避免造出一个没有来源的实体。

INSERT INTO mdp_entity (
  `tenant_id`, `source_id`, `entity_code`, `entity_name`, `entity_type`, `source_table_name`, `source_api_path`, `api_config_id`, `target_table_name`,
  `sync_mode`, `incr_column`, `batch_size`, `response_data_path`, `dedup_key_path`, `last_cursor`, `last_sync_to`, `job_id`, `status`, `remark`, `create_time`, `update_time`,
  `TenantId`, `SourceId`, `EntityCode`, `EntityName`, `EntityType`, `SourceTableName`, `SourceApiPath`, `ApiConfigId`, `TargetTableName`,
  `SyncMode`, `IncrColumn`, `BatchSize`, `ResponseDataPath`, `DedupKeyPath`, `LastCursor`,
  `biz_key_expr`, `file_config_json`, `is_public_template`, `inbound_enabled`, `inbound_max_rows`, `stale_guard_enabled`, `silence_calendar`, `silence_alert_hours`)
SELECT
  src.tenant_id, src.source_id, d.entity_code, d.entity_name, src.entity_type, d.entity_code, src.source_api_path, src.api_config_id, d.target_table,
  src.sync_mode, src.incr_column, src.batch_size, src.response_data_path, src.dedup_key_path, NULL, NULL, NULL, 1, d.remark, NOW(), NOW(),
  src.TenantId, src.SourceId, d.entity_code, d.entity_name, src.EntityType, d.entity_code, src.SourceApiPath, src.ApiConfigId, d.target_table,
  src.SyncMode, src.IncrColumn, src.BatchSize, src.ResponseDataPath, src.DedupKeyPath, NULL,
  d.biz_key, src.file_config_json, src.is_public_template, 1, 500, 1, NULL, NULL
FROM (
  SELECT * FROM (
    SELECT * FROM mdp_entity WHERE entity_code='S5_INVENTORY_TXN' ORDER BY id LIMIT 1
  ) tmpl
) src
JOIN (
  SELECT 'S2_WORK_ORDER_SCHEDULE' AS entity_code, '排程工单' AS entity_name, 'mdp_stg_schedule' AS target_table, 'WorkOrder,ItemCode' AS biz_key, '推送契约，不改既有来源登记' AS remark
  UNION ALL SELECT 'S3_PURCHASE_ORDER', '采购订单', 'mdp_stg_purchase_order', 'PoNo,PoLine', '推送契约，不改既有来源登记'
  UNION ALL SELECT 'S3_PURCHASE_RECEIPT', '采购收货', 'mdp_stg_purchase_receipt', 'Domain,Receiver,Line', '推送契约，不改既有来源登记'
  UNION ALL SELECT 'S4_SHIPMENT', '供应商发货', 'mdp_stg_s4_shipment', 'ShipmentNo', '推送契约，不改既有来源登记'
  UNION ALL SELECT 'S4_IQC', '来料检验', 'mdp_stg_s4_iqc', 'PoNo,PoLine,ItemCode', '推送契约，不改既有来源登记'
  UNION ALL SELECT 'S4_RETURN', '采购退货', 'mdp_stg_s4_return', 'PoNo,PoLine,ItemCode', '推送契约，不改既有来源登记'
  UNION ALL SELECT 'S4_SHORTAGE', '缺料', 'mdp_stg_s4_shortage', 'WorkOrder,ItemCode', '推送契约，不改既有来源登记'
  UNION ALL SELECT 'S6_WORK_ORDER_LINE', '生产工单行', 'mdp_stg_wo_line', 'Domain,OrderNo', '推送契约，不改既有来源登记'
  UNION ALL SELECT 'S7_SALES_ORDER_LINE', '销售订单行', 'mdp_stg_wo_line', 'Domain,OrderNo,LineNo', '推送契约，不改既有来源登记'
  UNION ALL SELECT 'S5_WORK_ORDER_BOM', '工单用料', 'mdp_stg_wo_bom', 'Domain,OrderNo,ItemCode', '推送契约，不改既有来源登记'
  UNION ALL SELECT 'S6_REPORT_TXN', '报工', 'mdp_stg_s6_report', 'Domain,ReportId', '推送契约，不改既有来源登记'
  UNION ALL SELECT 'S7_FQC_TASK_TXN', 'FQC任务', 'mdp_stg_fqc_pull', 'BillNo', '推送契约，不改既有来源登记'
  UNION ALL SELECT 'S5_INVENTORY_BALANCE_MONTHLY', '月度库存金额', 'mdp_stg_inv_bal_monthly', 'PeriodYm,ItemCode', '推送契约，不改既有来源登记'
) d
WHERE NOT EXISTS (SELECT 1 FROM mdp_entity e WHERE e.entity_code = d.entity_code);

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 g.tenant_id, g.access_key, d.entity_code, g.factory_id, g.source_code, g.rate_limit_per_min, g.batch_row_limit, 1, '沿用已有入站密钥', NOW(), NOW()
FROM mdp_inbound_grant g
JOIN (
  SELECT 'S2_WORK_ORDER_SCHEDULE' AS entity_code
  UNION ALL SELECT 'S3_PURCHASE_ORDER'
  UNION ALL SELECT 'S3_PURCHASE_RECEIPT'
  UNION ALL SELECT 'S4_SHIPMENT'
  UNION ALL SELECT 'S4_IQC'
  UNION ALL SELECT 'S4_RETURN'
  UNION ALL SELECT 'S4_SHORTAGE'
  UNION ALL SELECT 'S6_WORK_ORDER_LINE'
  UNION ALL SELECT 'S7_SALES_ORDER_LINE'
  UNION ALL SELECT 'S5_WORK_ORDER_BOM'
  UNION ALL SELECT 'S6_REPORT_TXN'
  UNION ALL SELECT 'S7_FQC_TASK_TXN'
  UNION ALL SELECT 'S5_INVENTORY_BALANCE_MONTHLY'
) d
WHERE g.entity_code='S5_INVENTORY_TXN' AND g.status=1
  AND NOT EXISTS (
    SELECT 1 FROM mdp_inbound_grant x
    WHERE x.access_key=g.access_key AND x.entity_code=d.entity_code);

UPDATE mdp_entity
SET inbound_enabled=1, update_time=NOW()
WHERE entity_code IN ('MDM_CUSTOMER', 'MDM_SUPPLIER', 'MDM_LOCATION')
  AND inbound_enabled=0;

UPDATE mdp_entity
SET EntityCode = entity_code,
    EntityName = entity_name,
    SourceTableName = source_table_name,
    TargetTableName = target_table_name,
    update_time = NOW()
WHERE entity_code = 'S6_EMP_WORK_ORD_TRANS_SQLSERVER'
  AND (
    IFNULL(EntityCode, '') <> entity_code
    OR IFNULL(SourceTableName, '') <> IFNULL(source_table_name, '')
    OR IFNULL(TargetTableName, '') <> IFNULL(target_table_name, '')
  );