103 lines
2.8 KiB
PL/PgSQL
103 lines
2.8 KiB
PL/PgSQL
-- ============================================
|
||
-- 此文件由 Meoo Cloud 自动生成，请勿修改
|
||
-- This file is auto-generated by Meoo Cloud, DO NOT MODIFY
|
||
-- ============================================
|
||
|
||
|
||
CREATE OR REPLACE FUNCTION validate_plan_quantity_consistency()
|
||
RETURNS TRIGGER LANGUAGE plpgsql AS $$
|
||
DECLARE
|
||
v_inventory_total NUMERIC;
|
||
v_tolerance NUMERIC := 0.01;
|
||
BEGIN
|
||
IF OLD.completed_quantity IS DISTINCT FROM NEW.completed_quantity THEN
|
||
SELECT COALESCE(SUM(quantity), 0) INTO v_inventory_total
|
||
FROM inventory_records
|
||
WHERE plan_id = NEW.id;
|
||
|
||
IF ABS(NEW.completed_quantity - v_inventory_total) > v_tolerance THEN
|
||
RAISE EXCEPTION
|
||
'数据完整性错误：计划 % 的完成数量(%)与入库记录总和(%)不一致，差异: %',
|
||
NEW.plan_code, NEW.completed_quantity, v_inventory_total,
|
||
NEW.completed_quantity - v_inventory_total;
|
||
END IF;
|
||
END IF;
|
||
|
||
RETURN NEW;
|
||
END;
|
||
$$;
|
||
|
||
CREATE OR REPLACE FUNCTION validate_inventory_record_positive()
|
||
RETURNS TRIGGER LANGUAGE plpgsql AS $$
|
||
BEGIN
|
||
IF NEW.quantity < 0 THEN
|
||
RAISE EXCEPTION '入库数量不能为负数: %', NEW.quantity;
|
||
END IF;
|
||
|
||
IF NEW.rolls < 0 THEN
|
||
RAISE EXCEPTION '入库匹数不能为负数: %', NEW.rolls;
|
||
END IF;
|
||
|
||
IF NEW.price_per_meter < 0 THEN
|
||
RAISE EXCEPTION '单价不能为负数: %', NEW.price_per_meter;
|
||
END IF;
|
||
|
||
RETURN NEW;
|
||
END;
|
||
$$;
|
||
|
||
CREATE OR REPLACE FUNCTION validate_payment_amount()
|
||
RETURNS TRIGGER LANGUAGE plpgsql AS $$
|
||
DECLARE
|
||
v_expected_amount NUMERIC;
|
||
v_tolerance NUMERIC := 0.01;
|
||
BEGIN
|
||
v_expected_amount := NEW.quantity * NEW.price_per_meter;
|
||
|
||
IF ABS(NEW.amount - v_expected_amount) > v_tolerance THEN
|
||
RAISE EXCEPTION
|
||
'付款金额不一致：期望 %(数量 % × 单价 %)，实际 %',
|
||
v_expected_amount, NEW.quantity, NEW.price_per_meter, NEW.amount;
|
||
END IF;
|
||
|
||
RETURN NEW;
|
||
END;
|
||
$$;
|
||
|
||
CREATE OR REPLACE FUNCTION validate_accounts_payable_totals()
|
||
RETURNS TRIGGER LANGUAGE plpgsql AS $$
|
||
DECLARE
|
||
v_items_total NUMERIC;
|
||
v_items_paid NUMERIC;
|
||
v_tolerance NUMERIC := 0.01;
|
||
BEGIN
|
||
SELECT
|
||
COALESCE(SUM(amount), 0),
|
||
COALESCE(SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END), 0)
|
||
INTO v_items_total, v_items_paid
|
||
FROM accounts_payable_items
|
||
WHERE accounts_payable_id = NEW.id;
|
||
|
||
IF ABS(NEW.total_amount - v_items_total) > v_tolerance THEN
|
||
RAISE EXCEPTION
|
||
'应付账款总金额不一致：表头 %, 明细合计 %',
|
||
NEW.total_amount, v_items_total;
|
||
END IF;
|
||
|
||
IF ABS(NEW.paid_amount - v_items_paid) > v_tolerance THEN
|
||
RAISE EXCEPTION
|
||
'应付账款已付金额不一致：表头 %, 明细合计 %',
|
||
NEW.paid_amount, v_items_paid;
|
||
END IF;
|
||
|
||
IF ABS(NEW.unpaid_amount - (v_items_total - v_items_paid)) > v_tolerance THEN
|
||
RAISE EXCEPTION
|
||
'应付账款未付金额不一致：表头 %, 计算值 %',
|
||
NEW.unpaid_amount, (v_items_total - v_items_paid);
|
||
END IF;
|
||
|
||
RETURN NEW;
|
||
END;
|
||
$$;
|
||
|