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

-- AIDOP-S567-HUMAN-EFFICIENCY-AND-S6-DASHBOARD-CLOSEOUT-1：3 个人效 KPI 月度维度配置生产固化（幂等）。
-- S5_L1_003 物料仓储人效 / S6_L2_003 工单制造人效 / S7_L1_003 成品仓储人效。
-- 铁律：人数为月度全岗总数（count gw），dwd 每 biz_month 仅 1 行，只支持 biz_month，绝不分摊/复制 headcount。
-- 各 DIMENSION_SQL 与对应 SUMMARY 同源（读同一 dwd_*_efficiency），仅按 biz_month 输出标准维度契约；未改业务公式。
-- 动态绑定当前生效 SUMMARY（不写死 Id）；已有当前生效维度配置则跳过（NOT EXISTS IsCurrent=1）。幂等：重复执行影响行 0。

-- ───────── S5_L1_003 物料仓储人效（月 · biz_month · RATIO_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_003','S5',s.`Id`,s.`VersionNo`,1,'MDP_LOCAL',
'SELECT STR_TO_DATE(CONCAT(biz_month, ''-01''), ''%Y-%m-%d'') AS value_date, ''BIZ_MONTH'' AS dimension_type,
       biz_month AS dimension_code, CONCAT(SUBSTRING(biz_month, 1, 4), ''年'', SUBSTRING(biz_month, 6, 2), ''月'') AS dimension_name,
       efficiency AS metric_value, online_qty AS numerator, warehouse_headcount AS denominator,
       online_qty AS sum_value, warehouse_headcount AS sample_count, CONCAT(''S5_L1_003:'', biz_month) AS source_key
FROM dwd_t8_material_warehouse_efficiency
WHERE tenant_id = @tenant_id AND factory_id = @factory_id AND source_ztid = @ztid AND biz_month = DATE_FORMAT(@biz_date, ''%Y-%m'')
GROUP BY biz_month',
'RATIO_OF_SUMS','["biz_month"]',60,'PUBLISHED',1,'SUMMARY S5_L1_003 同源 dwd_t8_material_warehouse_efficiency 月度全仓管人效（不分摊）','生产固化：物料仓储人效按月度维度','S5_L1_003 维度配置生产初始化','system',NOW()
FROM ado_smart_ops_kpi_calc_config s
WHERE s.`MetricCode`='S5_L1_003' 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_003' AND d.`TenantId`=s.`TenantId` AND d.`IsCurrent`=1)
LIMIT 1;

-- ───────── S6_L2_003 工单制造人效（月 · biz_month · RATIO_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`,'S6_L2_003','S6',s.`Id`,s.`VersionNo`,1,'MDP_LOCAL',
'SELECT STR_TO_DATE(CONCAT(biz_month, ''-01''), ''%Y-%m-%d'') AS value_date, ''BIZ_MONTH'' AS dimension_type,
       biz_month AS dimension_code, CONCAT(SUBSTRING(biz_month, 1, 4), ''年'', SUBSTRING(biz_month, 6, 2), ''月'') AS dimension_name,
       efficiency AS metric_value, done_count AS numerator, production_headcount AS denominator,
       done_count AS sum_value, production_headcount AS sample_count, CONCAT(''S6_L2_003:'', biz_month) AS source_key
FROM dwd_t8_work_order_mfg_efficiency
WHERE tenant_id = @tenant_id AND factory_id = @factory_id AND source_ztid = @ztid AND biz_month = DATE_FORMAT(@biz_date, ''%Y-%m'')
GROUP BY biz_month',
'RATIO_OF_SUMS','["biz_month"]',60,'PUBLISHED',1,'SUMMARY S6_L2_003 同源 dwd_t8_work_order_mfg_efficiency 月度全生产人效（不分摊）','生产固化：工单制造人效按月度维度','S6_L2_003 维度配置生产初始化','system',NOW()
FROM ado_smart_ops_kpi_calc_config s
WHERE s.`MetricCode`='S6_L2_003' 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_003' AND d.`TenantId`=s.`TenantId` AND d.`IsCurrent`=1)
LIMIT 1;

-- ───────── S7_L1_003 成品仓储人效（月 · biz_month · RATIO_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_003','S7',s.`Id`,s.`VersionNo`,1,'MDP_LOCAL',
'SELECT STR_TO_DATE(CONCAT(biz_month, ''-01''), ''%Y-%m-%d'') AS value_date, ''BIZ_MONTH'' AS dimension_type,
       biz_month AS dimension_code, CONCAT(SUBSTRING(biz_month, 1, 4), ''年'', SUBSTRING(biz_month, 6, 2), ''月'') AS dimension_name,
       efficiency AS metric_value, shipment_qty AS numerator, warehouse_headcount AS denominator,
       shipment_qty AS sum_value, warehouse_headcount AS sample_count, CONCAT(''S7_L1_003:'', biz_month) AS source_key
FROM dwd_t8_finished_warehouse_efficiency
WHERE tenant_id = @tenant_id AND factory_id = @factory_id AND source_ztid = @ztid AND biz_month = DATE_FORMAT(@biz_date, ''%Y-%m'')
GROUP BY biz_month',
'RATIO_OF_SUMS','["biz_month"]',60,'PUBLISHED',1,'SUMMARY S7_L1_003 同源 dwd_t8_finished_warehouse_efficiency 月度全仓管人效（不分摊）','生产固化：成品仓储人效按月度维度','S7_L1_003 维度配置生产初始化','system',NOW()
FROM ado_smart_ops_kpi_calc_config s
WHERE s.`MetricCode`='S7_L1_003' 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_003' AND d.`TenantId`=s.`TenantId` AND d.`IsCurrent`=1)
LIMIT 1;