97 lines
3.3 KiB
PL/PgSQL
97 lines
3.3 KiB
PL/PgSQL
-- ============================================
|
||
-- 修复自动导入函数，让成品归属到采购商公司
|
||
-- ============================================
|
||
|
||
CREATE OR REPLACE FUNCTION auto_import_to_finished_products()
|
||
RETURNS TRIGGER LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
|
||
DECLARE
|
||
v_washing_plan RECORD;
|
||
v_product RECORD;
|
||
v_warehouse RECORD;
|
||
v_finished_product_id UUID;
|
||
v_purchaser_company_id UUID; -- 采购商公司ID（计划创建公司）
|
||
BEGIN
|
||
-- 获取水洗计划信息，同时获取采购商公司ID
|
||
SELECT wp.*, p.product_name, p.color, p.fabric_code, p.color_code, p.weight,
|
||
wp.company_id as purchaser_id -- 计划创建公司就是采购商
|
||
FROM washing_plans wp
|
||
JOIN products p ON wp.product_id = p.id
|
||
WHERE wp.id = NEW.washing_plan_id
|
||
INTO v_washing_plan;
|
||
|
||
IF v_washing_plan IS NULL THEN
|
||
RAISE EXCEPTION 'Washing plan not found';
|
||
END IF;
|
||
|
||
-- 采购商公司ID
|
||
v_purchaser_company_id := v_washing_plan.purchaser_id;
|
||
|
||
SELECT * FROM warehouses WHERE id = NEW.warehouse_id INTO v_warehouse;
|
||
|
||
-- 插入成品到采购商的成品仓库（使用采购商company_id，而非水洗厂）
|
||
INSERT INTO finished_products (
|
||
company_id, washing_plan_id, product_id, product_name, color, fabric_code, color_code, weight,
|
||
washed_meters, shrinkage_rate, rolls, stock_meters, stock_rolls, warehouse_id, warehouse_location,
|
||
washing_cost, unit_cost, status, notes, created_by
|
||
) VALUES (
|
||
v_purchaser_company_id, -- 关键修复：归属到采购商公司
|
||
NEW.washing_plan_id,
|
||
v_washing_plan.product_id,
|
||
v_washing_plan.product_name,
|
||
v_washing_plan.color,
|
||
v_washing_plan.fabric_code,
|
||
v_washing_plan.color_code,
|
||
v_washing_plan.weight,
|
||
NEW.actual_washed_meters,
|
||
NEW.actual_shrinkage_rate,
|
||
NEW.rolls,
|
||
NEW.actual_washed_meters,
|
||
NEW.rolls,
|
||
NEW.warehouse_id,
|
||
NEW.warehouse_location,
|
||
NEW.actual_washing_cost,
|
||
NEW.unit_cost,
|
||
'in_stock',
|
||
NEW.notes,
|
||
NEW.completed_by
|
||
) RETURNING id INTO v_finished_product_id;
|
||
|
||
-- 插入入库记录（归属到采购商公司）
|
||
INSERT INTO finished_product_inventory_records (
|
||
company_id, finished_product_id, record_type, rolls, meters, washing_completion_id,
|
||
warehouse_id, warehouse_location, notes, batch_no, operator_id
|
||
) VALUES (
|
||
v_purchaser_company_id, -- 关键修复：归属到采购商公司
|
||
v_finished_product_id,
|
||
'inbound',
|
||
NEW.rolls,
|
||
NEW.actual_washed_meters,
|
||
NEW.id,
|
||
NEW.warehouse_id,
|
||
NEW.warehouse_location,
|
||
'水洗完成自动入库',
|
||
'WASH-' || TO_CHAR(NOW(), 'YYYYMMDD') || '-' || SUBSTRING(v_finished_product_id::text, 1, 8),
|
||
NEW.completed_by
|
||
);
|
||
|
||
-- 更新水洗计划状态
|
||
UPDATE washing_plans SET
|
||
actual_washed_meters = NEW.actual_washed_meters,
|
||
actual_shrinkage_rate = NEW.actual_shrinkage_rate,
|
||
actual_total_cost = NEW.actual_washing_cost,
|
||
status = 'completed',
|
||
washing_date = NEW.washing_date,
|
||
updated_at = NOW()
|
||
WHERE id = NEW.washing_plan_id;
|
||
|
||
-- 设置完成记录的关联信息
|
||
NEW.finished_product_id := v_finished_product_id;
|
||
NEW.auto_imported := TRUE;
|
||
|
||
RETURN NEW;
|
||
END;
|
||
$$;
|
||
|
||
-- 添加函数注释
|
||
COMMENT ON FUNCTION auto_import_to_finished_products() IS '水洗完成时自动导入成品到采购商的成品仓库';
|