7 lines
2.5 KiB
MySQL
7 lines
2.5 KiB
MySQL
|
-- ============================================
|
||
|
|
-- 此文件由 Meoo Cloud 自动生成，请勿修改
|
||
|
|
-- This file is auto-generated by Meoo Cloud, DO NOT MODIFY
|
||
|
|
-- ============================================
|
||
|
|
|
||
|
|
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; BEGIN 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; v_purchaser_company_id := v_washing_plan.purchaser_id; SELECT * FROM warehouses WHERE id = NEW.warehouse_id INTO v_warehouse; 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; $$;
|