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

-- AIDOP-S567-KPI-DIMENSION-REMAINING-8-CLOSEOUT-1：5 个 FULLY 维度配置生产固化（幂等）。
-- S5_L1_001 物料 / S5_L1_002 工单 / S5_L1_004 品类 / S6_L2_002 工单 / S7_L1_002 订单。
-- 各 DIMENSION_SQL 与对应 SUMMARY_SQL 同源同 WHERE，仅加 GROUP BY 维度键 + 标准输出别名；未改业务公式。
-- 动态绑定当前生效 SUMMARY（不写死 Id）；已有当前生效维度配置则跳过（NOT EXISTS IsCurrent=1，杜绝双 Current）。
-- 幂等：重复执行影响行 0。

-- ───────── S5_L1_001 物料上线周期（日 · 物料 · AVERAGE_OF_SUMS）─────────
INSERT INTO ado_smart_ops_kpi_dimension_config
  (`TenantId`,`MetricCode`,`ModuleCode`,`SummaryConfigId`,`SummaryConfigVersion`,`DimensionConfigVersion`,
   `DataSourceCode`,`SqlScript`,`AggregationType`,`SupportedDimensionsJson`,`TimeoutSeconds`,`PublishStatus`,`IsCurrent`,`BusinessSqlSource`,`ChangeRemark`,`Remark`,`CreatedBy`,`CreatedAt`)
SELECT s.`TenantId`,'S5_L1_001','S5',s.`Id`,s.`VersionNo`,1,'MDP_LOCAL',
'SELECT biz_date AS value_date, ''MATERIAL'' AS dimension_type, item_code AS dimension_code, item_code AS dimension_name,
       AVG(cycle_days) AS metric_value, SUM(cycle_days) AS sum_value, COUNT(*) AS sample_count, item_code AS source_key
FROM dwd_t8_material_online_cycle
WHERE tenant_id = @tenant_id AND factory_id = @factory_id AND biz_date = @biz_date AND source_ztid = @ztid AND cycle_days IS NOT NULL
GROUP BY biz_date, item_code',
'AVERAGE_OF_SUMS','["value_date","material_code"]',60,'PUBLISHED',1,'SUMMARY S5_L1_001 同源 dwd_t8_material_online_cycle 物料级 cycle_days','生产固化：物料上线周期按物料维度','S5_L1_001 维度配置生产初始化','system',NOW()
FROM ado_smart_ops_kpi_calc_config s
WHERE s.`MetricCode`='S5_L1_001' AND s.`IsCurrent`=1 AND s.`PublishStatus`='PUBLISHED'
  AND NOT EXISTS (SELECT 1 FROM ado_smart_ops_kpi_dimension_config d WHERE d.`MetricCode`='S5_L1_001' AND d.`TenantId`=s.`TenantId` AND d.`IsCurrent`=1)
LIMIT 1;

-- ───────── S5_L1_002 物料上线满足率（日 · 工单 · AVERAGE_OF_SUMS 复刻均值×100）─────────
INSERT INTO ado_smart_ops_kpi_dimension_config
  (`TenantId`,`MetricCode`,`ModuleCode`,`SummaryConfigId`,`SummaryConfigVersion`,`DimensionConfigVersion`,
   `DataSourceCode`,`SqlScript`,`AggregationType`,`SupportedDimensionsJson`,`TimeoutSeconds`,`PublishStatus`,`IsCurrent`,`BusinessSqlSource`,`ChangeRemark`,`Remark`,`CreatedBy`,`CreatedAt`)
SELECT s.`TenantId`,'S5_L1_002','S5',s.`Id`,s.`VersionNo`,1,'MDP_LOCAL',
'SELECT biz_date AS value_date, ''WORK_ORDER'' AS dimension_type, work_order_no AS dimension_code, work_order_no AS dimension_name,
       ROUND(AVG(fulfillment_rate) * 100, 4) AS metric_value, SUM(fulfillment_rate * 100) AS sum_value, COUNT(*) AS sample_count,
       SUM(before_kgdate_rows) AS numerator, SUM(total_rows) AS denominator, work_order_no AS source_key
FROM dwd_t8_material_online_fulfillment
WHERE tenant_id = @tenant_id AND factory_id = @factory_id AND biz_date = @biz_date AND source_ztid = @ztid AND fulfillment_rate IS NOT NULL
GROUP BY biz_date, work_order_no',
'AVERAGE_OF_SUMS','["value_date","work_order_no"]',60,'PUBLISHED',1,'SUMMARY S5_L1_002 同源 dwd_t8_material_online_fulfillment 工单级 fulfillment_rate','生产固化：物料上线满足率按工单维度','S5_L1_002 维度配置生产初始化','system',NOW()
FROM ado_smart_ops_kpi_calc_config s
WHERE s.`MetricCode`='S5_L1_002' AND s.`IsCurrent`=1 AND s.`PublishStatus`='PUBLISHED'
  AND NOT EXISTS (SELECT 1 FROM ado_smart_ops_kpi_dimension_config d WHERE d.`MetricCode`='S5_L1_002' AND d.`TenantId`=s.`TenantId` AND d.`IsCurrent`=1)
LIMIT 1;

-- ───────── S5_L1_004 品类物料库存周转（月 · 品类 · AVERAGE_OF_SUMS）─────────
INSERT INTO ado_smart_ops_kpi_dimension_config
  (`TenantId`,`MetricCode`,`ModuleCode`,`SummaryConfigId`,`SummaryConfigVersion`,`DimensionConfigVersion`,
   `DataSourceCode`,`SqlScript`,`AggregationType`,`SupportedDimensionsJson`,`TimeoutSeconds`,`PublishStatus`,`IsCurrent`,`BusinessSqlSource`,`ChangeRemark`,`Remark`,`CreatedBy`,`CreatedAt`)
SELECT s.`TenantId`,'S5_L1_004','S5',s.`Id`,s.`VersionNo`,1,'MDP_LOCAL',
'SELECT STR_TO_DATE(CONCAT(biz_month, ''-01''), ''%Y-%m-%d'') AS value_date, ''CATEGORY'' AS dimension_type, category_code AS dimension_code, MAX(category_name) AS dimension_name,
       AVG(turnover_days) AS metric_value, SUM(turnover_days) AS sum_value, COUNT(turnover_days) AS sample_count, category_code AS source_key
FROM dwd_t8_material_inventory_turnover
WHERE tenant_id = @tenant_id AND factory_id = @factory_id AND biz_month = DATE_FORMAT(@biz_date, ''%Y-%m'') AND source_ztid = @ztid
GROUP BY biz_month, category_code',
'AVERAGE_OF_SUMS','["value_date","category_code"]',60,'PUBLISHED',1,'SUMMARY S5_L1_004 同源 dwd_t8_material_inventory_turnover 品类级 turnover_days','生产固化：品类物料库存周转按品类维度','S5_L1_004 维度配置生产初始化','system',NOW()
FROM ado_smart_ops_kpi_calc_config s
WHERE s.`MetricCode`='S5_L1_004' AND s.`IsCurrent`=1 AND s.`PublishStatus`='PUBLISHED'
  AND NOT EXISTS (SELECT 1 FROM ado_smart_ops_kpi_dimension_config d WHERE d.`MetricCode`='S5_L1_004' AND d.`TenantId`=s.`TenantId` AND d.`IsCurrent`=1)
LIMIT 1;

-- ───────── S6_L2_002 工单制造满足率（日 · 工单 · AVERAGE_OF_SUMS 复刻均值×100）─────────
INSERT INTO ado_smart_ops_kpi_dimension_config
  (`TenantId`,`MetricCode`,`ModuleCode`,`SummaryConfigId`,`SummaryConfigVersion`,`DimensionConfigVersion`,
   `DataSourceCode`,`SqlScript`,`AggregationType`,`SupportedDimensionsJson`,`TimeoutSeconds`,`PublishStatus`,`IsCurrent`,`BusinessSqlSource`,`ChangeRemark`,`Remark`,`CreatedBy`,`CreatedAt`)
SELECT s.`TenantId`,'S6_L2_002','S6',s.`Id`,s.`VersionNo`,1,'MDP_LOCAL',
'SELECT biz_date AS value_date, ''WORK_ORDER'' AS dimension_type, order_no AS dimension_code, order_no AS dimension_name,
       ROUND(AVG(fulfillment_rate) * 100, 4) AS metric_value, SUM(fulfillment_rate * 100) AS sum_value, COUNT(*) AS sample_count,
       SUM(done_qty_in_window) AS numerator, SUM(plan_qty) AS denominator, order_no AS source_key
FROM dwd_t8_work_order_mfg_fulfillment
WHERE tenant_id = @tenant_id AND factory_id = @factory_id AND biz_date = @biz_date AND source_ztid = @ztid AND fulfillment_rate IS NOT NULL
GROUP BY biz_date, order_no',
'AVERAGE_OF_SUMS','["value_date","work_order_no"]',60,'PUBLISHED',1,'SUMMARY S6_L2_002 同源 dwd_t8_work_order_mfg_fulfillment 工单级 fulfillment_rate','生产固化：工单制造满足率按工单维度','S6_L2_002 维度配置生产初始化','system',NOW()
FROM ado_smart_ops_kpi_calc_config s
WHERE s.`MetricCode`='S6_L2_002' AND s.`IsCurrent`=1 AND s.`PublishStatus`='PUBLISHED'
  AND NOT EXISTS (SELECT 1 FROM ado_smart_ops_kpi_dimension_config d WHERE d.`MetricCode`='S6_L2_002' AND d.`TenantId`=s.`TenantId` AND d.`IsCurrent`=1)
LIMIT 1;

-- ───────── S7_L1_002 订单发货满足率（日 · 订单 · AVERAGE_OF_SUMS）─────────
INSERT INTO ado_smart_ops_kpi_dimension_config
  (`TenantId`,`MetricCode`,`ModuleCode`,`SummaryConfigId`,`SummaryConfigVersion`,`DimensionConfigVersion`,
   `DataSourceCode`,`SqlScript`,`AggregationType`,`SupportedDimensionsJson`,`TimeoutSeconds`,`PublishStatus`,`IsCurrent`,`BusinessSqlSource`,`ChangeRemark`,`Remark`,`CreatedBy`,`CreatedAt`)
SELECT s.`TenantId`,'S7_L1_002','S7',s.`Id`,s.`VersionNo`,1,'MDP_LOCAL',
'SELECT biz_date AS value_date, ''ORDER'' AS dimension_type, order_no AS dimension_code, order_no AS dimension_name,
       ROUND(AVG(fulfillment_rate), 4) AS metric_value, SUM(fulfillment_rate) AS sum_value, COUNT(*) AS sample_count,
       SUM(in_window_rows) AS numerator, SUM(total_rows) AS denominator, order_no AS source_key
FROM dwd_t8_order_shipment_fulfillment
WHERE tenant_id = @tenant_id AND factory_id = @factory_id AND biz_date = @biz_date AND source_ztid = @ztid AND fulfillment_rate IS NOT NULL
GROUP BY biz_date, order_no',
'AVERAGE_OF_SUMS','["value_date","order_no"]',60,'PUBLISHED',1,'SUMMARY S7_L1_002 同源 dwd_t8_order_shipment_fulfillment 订单级 fulfillment_rate','生产固化：订单发货满足率按订单维度','S7_L1_002 维度配置生产初始化','system',NOW()
FROM ado_smart_ops_kpi_calc_config s
WHERE s.`MetricCode`='S7_L1_002' AND s.`IsCurrent`=1 AND s.`PublishStatus`='PUBLISHED'
  AND NOT EXISTS (SELECT 1 FROM ado_smart_ops_kpi_dimension_config d WHERE d.`MetricCode`='S7_L1_002' AND d.`TenantId`=s.`TenantId` AND d.`IsCurrent`=1)
LIMIT 1;