MES/yawei-mes/.sql/2026-02-04_v1.6.036_周启威_生产订单添加成本字段.sql

-- 生产订单添加成本字段
-- 作者: 周启威
-- 日期: 2026-02-03
-- 版本: v1.6.036

-- 为生产订单表添加四个成本相关字段
-- 注意：如果字段已存在，请注释掉此部分或删除已存在的字段后再执行
ALTER TABLE `sal_order` 
ADD COLUMN `material_cost` DECIMAL(18,2) NULL DEFAULT 0.00 COMMENT '材料成本：通过BOM单计算当前订单需要的物料成本' AFTER `remark`,
ADD COLUMN `labor_cost` DECIMAL(18,2) NULL DEFAULT 0.00 COMMENT '人员成本：通过工序路线中的人员成本相加带出' AFTER `material_cost`,
ADD COLUMN `equipment_cost` DECIMAL(18,2) NULL DEFAULT 0.00 COMMENT '设备损耗成本：通过工序路线中的设备损耗成本相加带出' AFTER `labor_cost`,
ADD COLUMN `energy_cost` DECIMAL(18,2) NULL DEFAULT 0.00 COMMENT '能耗成本：手动填写' AFTER `equipment_cost`;

-- 如果需要重新添加字段，请先删除已存在的字段：
-- ALTER TABLE `sal_order` DROP COLUMN `material_cost`, DROP COLUMN `labor_cost`, DROP COLUMN `equipment_cost`, DROP COLUMN `energy_cost`;

-- ==========================================
-- 成本计算与更新
-- ==========================================

-- 1. 更新材料成本（通过BOM单计算）
-- 说明：根据销售订单分录中的物料，查找对应的BOM，计算所需物料的总成本
UPDATE `sal_order` so
SET so.material_cost = (
    SELECT IFNULL(SUM(
        CASE 
            -- 如果BOM明细中有单价和用量，则计算：用量 * 单价 * 订单数量
            WHEN bi.unit_price IS NOT NULL AND bi.quantity IS NOT NULL 
            THEN bi.quantity * bi.unit_price * soe.quantity
            -- 否则使用BOM主表的材料成本 * 订单数量
            ELSE IFNULL(b.material_cost, 0) * soe.quantity
        END
    ), 0)
    FROM `sal_order_entry` soe
    LEFT JOIN `md_new_bom` b ON b.material_id = soe.material_id AND b.status = '1' -- 只取已发布的BOM
    LEFT JOIN `md_new_bom_item` bi ON bi.bom_id = b.id
    WHERE soe.main_id = so.id
)
WHERE EXISTS (
    SELECT 1 FROM `sal_order_entry` soe WHERE soe.main_id = so.id
);

-- 2. 更新人员成本（通过工序路线计算）
-- 说明：根据销售订单分录中的物料，查找对应的工序路线，汇总所有工序的人工成本
UPDATE `sal_order` so
SET so.labor_cost = (
    SELECT IFNULL(SUM(rp.labor_cost), 0)
    FROM `sal_order_entry` soe
    LEFT JOIN `md_material` m ON m.id = soe.material_id
    LEFT JOIN `pro_route_process` rp ON rp.route_id = m.route_id
    WHERE soe.main_id = so.id
)
WHERE EXISTS (
    SELECT 1 FROM `sal_order_entry` soe WHERE soe.main_id = so.id
);

-- 3. 更新设备损耗成本（通过工序路线计算）
-- 说明：根据销售订单分录中的物料，查找对应的工序路线，汇总所有工序的设备损耗成本
UPDATE `sal_order` so
SET so.equipment_cost = (
    SELECT IFNULL(SUM(rp.equipment_cost), 0)
    FROM `sal_order_entry` soe
    LEFT JOIN `md_material` m ON m.id = soe.material_id
    LEFT JOIN `pro_route_process` rp ON rp.route_id = m.route_id
    WHERE soe.main_id = so.id
)
WHERE EXISTS (
    SELECT 1 FROM `sal_order_entry` soe WHERE soe.main_id = so.id
);

-- ==========================================
-- 验证查询（可选）
-- ==========================================

-- 【调试查询1】检查销售订单分录和物料的关联情况
SELECT 
    so.id AS '订单ID',
    so.number AS '订单编号',
    soe.id AS '分录ID',
    soe.material_id AS '物料ID',
    soe.material_name AS '物料名称',
    soe.quantity AS '订单数量',
    m.route_id AS '工序路线ID'
FROM `sal_order` so
LEFT JOIN `sal_order_entry` soe ON soe.main_id = so.id
LEFT JOIN `md_material` m ON m.id = soe.material_id
ORDER BY so.id DESC
LIMIT 10;

-- 【调试查询2】检查工序路线和成本数据
SELECT 
    r.id AS '路线ID',
    r.name AS '路线名称',
    rp.id AS '工序明细ID',
    rp.process_name AS '工序名称',
    rp.labor_cost AS '人工成本',
    rp.equipment_cost AS '设备成本'
FROM `pro_route` r
LEFT JOIN `pro_route_process` rp ON rp.route_id = r.id
WHERE r.id IS NOT NULL
ORDER BY r.id, rp.sort
LIMIT 20;

-- 【调试查询3】完整的关联查询（检查数据流）- 最重要！
SELECT 
    so.id AS '订单ID',
    so.number AS '订单编号',
    soe.material_name AS '物料名称',
    soe.quantity AS '订单数量',
    m.route_id AS '路线ID',
    r.name AS '路线名称',
    rp.process_name AS '工序名称',
    rp.labor_cost AS '人工成本',
    rp.equipment_cost AS '设备成本',
    (rp.labor_cost * soe.quantity) AS '计算后人工成本',
    (rp.equipment_cost * soe.quantity) AS '计算后设备成本'
FROM `sal_order` so
LEFT JOIN `sal_order_entry` soe ON soe.main_id = so.id
LEFT JOIN `md_material` m ON m.id = soe.material_id
LEFT JOIN `pro_route` r ON r.id = m.route_id
LEFT JOIN `pro_route_process` rp ON rp.route_id = r.id
ORDER BY so.id DESC, rp.sort
LIMIT 30;

-- 【调试查询4】查看当前成本数据
SELECT 
    so.id,
    so.number AS '订单编号',
    so.customer_name AS '客户名称',
    so.material_cost AS '材料成本',
    so.labor_cost AS '人员成本',
    so.equipment_cost AS '设备成本',
    so.energy_cost AS '能耗成本',
    (so.material_cost + so.labor_cost + so.equipment_cost + so.energy_cost) AS '总成本'
FROM `sal_order` so
ORDER BY so.id DESC
LIMIT 10;

-- ==========================================
-- 说明
-- ==========================================
-- 1. 材料成本：从BOM单中计算，优先使用BOM明细的单价*用量，否则使用BOM主表的材料成本
-- 2. 人员成本：从工序路线明细表的labor_cost字段汇总
-- 3. 设备损耗成本：从工序路线明细表的equipment_cost字段汇总
-- 4. 能耗成本：需要手动填写，默认为0
-- 5. 如果物料表中没有route_id字段关联工序路线，需要根据实际表结构调整关联逻辑