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

-- 1.0.591
-- A. 补齐「有 S5_L1_004 值、但本租户没有字典/布局」的缺口。不写死租户号。
--    模板取 TenantId=0。ParentId 强制 NULL：模板上的 ParentId 指向 0 号租户自己的行，抄过来是错的外键。
--    SortNo 用 20，与 AidopKpiMasterSeed 的 S5_L1_004 一致，不抄模板的 540。
-- C. 清掉 T8_V5_SQLSERVER.db_extra_params 里的 ConfigId。
--    不改 1.0.178.sql：已成功脚本内容一变，AutoVersionUpdate 会因 SHA256 不一致直接拒绝启动。

INSERT INTO ado_smart_ops_kpi_master
  (Id, MetricCode, ModuleCode, MetricLevel, ParentId, MetricName, Description, Formula,
   CalcRule, DataSource, StatFrequency, Department, DopFields, Unit, Direction,
   IsHomePage, SortNo, Remark, IsEnabled, TenantId, CreatedAt, UpdatedAt,
   YellowThreshold, RedThreshold, FormulaExpr, FormulaPreview, FormulaRefs)
SELECT
  base.max_id + gap.rn,
  tmpl.MetricCode, tmpl.ModuleCode, tmpl.MetricLevel, NULL,
  tmpl.MetricName, tmpl.Description, tmpl.Formula,
  tmpl.CalcRule, tmpl.DataSource, tmpl.StatFrequency, tmpl.Department, tmpl.DopFields,
  tmpl.Unit, tmpl.Direction,
  tmpl.IsHomePage, 20, tmpl.Remark, tmpl.IsEnabled,
  gap.tenant_id, NOW(), NOW(),
  tmpl.YellowThreshold, tmpl.RedThreshold, tmpl.FormulaExpr, tmpl.FormulaPreview, tmpl.FormulaRefs
FROM (
  SELECT v.tenant_id, ROW_NUMBER() OVER (ORDER BY v.tenant_id) AS rn
  FROM (
    SELECT DISTINCT tenant_id
    FROM ado_s9_kpi_value_l1_day
    WHERE metric_code = 'S5_L1_004' AND is_deleted = 0
  ) v
  WHERE NOT EXISTS (
    SELECT 1 FROM ado_smart_ops_kpi_master k
    WHERE k.TenantId = v.tenant_id AND k.MetricCode = 'S5_L1_004'
  )
) gap
JOIN ado_smart_ops_kpi_master tmpl
  ON tmpl.TenantId = 0 AND tmpl.MetricCode = 'S5_L1_004'
CROSS JOIN (
  SELECT COALESCE(MAX(Id), 0) AS max_id FROM ado_smart_ops_kpi_master
) base;

-- 有值的工厂补布局行。MetricCode 跨表比较必须显式 COLLATE。
INSERT INTO ado_smart_ops_layout_item
  (TenantId, FactoryId, ModuleCode, RowId, MetricLevel, MetricCode, DisplayName,
   SortNo, ParentRowId, FormulaText, PanelZone, IsEnabled, UpdateTime)
SELECT
  k.TenantId, f.factory_id, 'S5', 'S5-L1-S5_L1_004', 1, 'S5_L1_004', '品类物料库存周转',
  20, NULL, 'D1 / D2 × 30', NULL, 1, NOW()
FROM ado_smart_ops_kpi_master k
JOIN (
  SELECT DISTINCT tenant_id, factory_id
  FROM ado_s9_kpi_value_l1_day
  WHERE metric_code = 'S5_L1_004' AND is_deleted = 0
) f ON f.tenant_id = k.TenantId
WHERE k.MetricCode = 'S5_L1_004'
  AND NOT EXISTS (
    SELECT 1 FROM ado_smart_ops_layout_item l
    WHERE l.TenantId = k.TenantId
      AND l.FactoryId = f.factory_id
      AND l.MetricLevel = 1
      AND l.MetricCode COLLATE utf8mb4_general_ci = 'S5_L1_004'
  );

-- 字典已有、同模块已有其它 L1 布局、唯独缺这一行的租户（不要求当下有值）。
INSERT INTO ado_smart_ops_layout_item
  (TenantId, FactoryId, ModuleCode, RowId, MetricLevel, MetricCode, DisplayName,
   SortNo, ParentRowId, FormulaText, PanelZone, IsEnabled, UpdateTime)
SELECT DISTINCT
  l.TenantId, l.FactoryId, 'S5', 'S5-L1-S5_L1_004', 1, 'S5_L1_004', '品类物料库存周转',
  20, NULL, 'D1 / D2 × 30', NULL, 1, NOW()
FROM ado_smart_ops_layout_item l
JOIN ado_smart_ops_kpi_master k
  ON k.TenantId = l.TenantId AND k.MetricCode = 'S5_L1_004'
WHERE l.ModuleCode = 'S5' AND l.MetricLevel = 1
  AND NOT EXISTS (
    SELECT 1 FROM ado_smart_ops_layout_item x
    WHERE x.TenantId = l.TenantId
      AND x.FactoryId = l.FactoryId
      AND x.MetricLevel = 1
      AND x.MetricCode COLLATE utf8mb4_general_ci = 'S5_L1_004'
  );

UPDATE mdp_source
SET db_extra_params = NULL, update_time = NOW()
WHERE source_code = 'T8_V5_SQLSERVER'
  AND db_extra_params LIKE '%ConfigId%';