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

-- 1.0.322.sql  S2 排程正确性重构 G1：按产线补齐工作日历。
--   使 S1 CapacityDateCalculator（按 ProdLine + ShiftsHours1）与后续 S2 产线维度排程可用。
--   班制已决策：周一~周五 IsWorkDay=1，周六周日=0，ShiftsHours1=10.5（待办 P-027 业务复核）。
--   幂等：以 (tenant_id, TRIM(ProdLine), WeekDay) 判存在，只新增不修改。
--   强制 TRIM(Line)：避免尾随空格导致同一产线播种 14 行、触发 Count!=7 静默退化。
-- ============================================================================

SET NAMES utf8mb4;

INSERT INTO ShopCalendarWorkCtr
    (ProdLine, WorkCtr, WeekDay, IsWorkDay, ShiftsHours1,
     IsActive, IsConfirm, tenant_id, CreateUser, UpdateUser, CreateTime, UpdateTime)
SELECT
    l.ProdLine,
    '',
    d.WeekDay,
    CASE WHEN d.WeekDay BETWEEN 1 AND 5 THEN 1 ELSE 0 END,
    10.5,
    1,
    0,
    l.tenant_id,
    'sys', 'sys', NOW(), NOW()
FROM (
    SELECT DISTINCT tenant_id, TRIM(Line) AS ProdLine
    FROM ProdLineDetail
    WHERE IFNULL(IsActive, 0) = 1
      AND TRIM(IFNULL(Line, '')) <> ''
) l
CROSS JOIN (
    SELECT 0 AS WeekDay UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
    UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
) d
LEFT JOIN (
    SELECT tenant_id AS t, TRIM(IFNULL(ProdLine, '')) AS pl, WeekDay AS wd
    FROM ShopCalendarWorkCtr
) c ON c.t = l.tenant_id AND c.pl = l.ProdLine AND c.wd = d.WeekDay
WHERE c.t IS NULL;

-- G2 存量回填：修正 WorkOrdRouting 错误映射（Rate 曾误写入 MachBdnRate）
--   仅回填仍能关联到 ProdLineDetail 的活跃关键工序行。
UPDATE WorkOrdRouting r
INNER JOIN ProdLineDetail b
    ON b.Part = r.ItemNum AND b.Op = r.OP AND b.tenant_id = r.tenant_id
SET
    r.StdRunTime = CASE WHEN IFNULL(b.Rate, 0) > 0 THEN 1.0 / b.Rate ELSE r.StdRunTime END,
    r.RunCrew = CAST(IFNULL(NULLIF(TRIM(IFNULL(b.RunCrew, '')), ''), '0') AS DECIMAL(18, 4)),
    r.Line = LEFT(TRIM(IFNULL(b.Line, '')), 8),
    r.ProdLine = LEFT(TRIM(IFNULL(b.Line, '')), 8),
    r.MachBdnRate = 0,
    r.UpdateTime = NOW()
WHERE IFNULL(r.IsActive, 0) = 1
  AND IFNULL(r.MilestoneOp, 0) = 1
  AND IFNULL(b.IsActive, 0) = 1;