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

-- =============================================================================
-- S8 订单交付超期监控向导：真实数据源登记
-- 作用：将 ORDER_DELIVERY 监控对象的源表切换为数据中台真实发货宽表 dwd_ship_trans
--       （经受控只读视图 v_s8_order_delivery_watch 收敛）。
-- 前提（已拍板）：单客户私有云，S8（租户 1）经受控只读视图读取数据中台业务租户
--       797403760988229 的订单交付数据。
-- 安全级别：仅登记字典；不创建 watch_rule、不启用 watch_rule、不触发调度器、不自动建异常单。
-- =============================================================================

-- 1) 受控视图：锚定业务租户 797403760988229；每 order_no 折叠到最新 stat_date（1 行/单）。
--    plan_delivery_date 取 MIN（最早到期行代表订单超期）；
--    delivery_status 采用“全部行 COMPLETED 才算 COMPLETED，否则 OPEN”。
CREATE OR REPLACE VIEW v_s8_order_delivery_watch AS
SELECT
    t.order_no                            AS order_no,
    MAX(t.customer_name)                  AS customer_name,
    MIN(t.plan_delivery_date)             AS plan_delivery_date,
    CASE WHEN SUM(t.delivery_status = 'COMPLETED') = COUNT(*)
         THEN 'COMPLETED' ELSE 'OPEN' END AS delivery_status
FROM dwd_ship_trans t
JOIN (
    SELECT order_no, MAX(stat_date) AS max_stat
    FROM dwd_ship_trans
    WHERE tenant_id = 797403760988229
    GROUP BY order_no
) latest ON t.order_no = latest.order_no AND t.stat_date = latest.max_stat
WHERE t.tenant_id = 797403760988229
GROUP BY t.order_no;

-- 2) 监控对象 ORDER_DELIVERY：源表切换到受控视图（baseline 口径 tenant_id=0 / factory_id=0）。
UPDATE ado_s8_monitor_object
SET source_table = 'v_s8_order_delivery_watch', updated_at = NOW()
WHERE tenant_id = 0 AND factory_id = 0 AND object_code = 'ORDER_DELIVERY';

-- 3) 监控指标 ORDER_DUE_AT：字段映射切真实列 + 完成态口径 + 稳定业务键（order_no）。
--    enabled = 1 仅表示字典可见、向导可选；监控启用的安全闸门落在 watch_rule 层。
UPDATE ado_s8_monitor_metric
SET due_at_field             = 'plan_delivery_date',
    status_field             = 'delivery_status',
    default_completed_states = 'COMPLETED',
    object_id_field          = 'order_no',
    object_code_field        = 'order_no',
    object_name_field        = 'customer_name',
    metric_name              = '计划交付超期',
    enabled                  = 1,
    updated_at               = NOW()
WHERE tenant_id = 0 AND factory_id = 0
  AND object_code = 'ORDER_DELIVERY' AND metric_code = 'ORDER_DUE_AT';