147 lines
5.8 KiB
SQL
147 lines
5.8 KiB
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字段关联工序路线，需要根据实际表结构调整关联逻辑
|