mescloud/db/ddl-piecework-date.sql

-- T09计件统计修复 - DDL脚本
-- 执行时间：2026-07-17
-- 目标库：mes_cloud_t09

-- 1. 新增 piecework_date 字段
ALTER TABLE mes_product_record
ADD COLUMN piecework_date DATETIME NULL
COMMENT '计件时间（首次加工完成时间）'
AFTER update_date;

-- 2. 添加索引提升查询性能
CREATE INDEX idx_piecework_oprno_date
ON mes_product_record(oprno, piecework_date, craft);

-- 3. 历史数据迁移（已完成的记录用 update_date 初始化）
UPDATE mes_product_record
SET piecework_date = update_date
WHERE craft = '100000'
  AND content NOT IN ('UDF', '')
  AND piecework_date IS NULL;

-- 验证查询
SELECT
    COUNT(*) as total,
    SUM(CASE WHEN piecework_date IS NOT NULL THEN 1 ELSE 0 END) as has_piecework_date,
    SUM(CASE WHEN content = 'UDF' THEN 1 ELSE 0 END) as udf_count
FROM mes_product_record
WHERE craft = '100000';