From 952a47ccdcf38a966433e3eb87dd953b750b4fcd Mon Sep 17 00:00:00 2001 From: einhverfr Date: Mon, 30 Apr 2007 02:06:42 +0000 Subject: Adding first draft of voucher handling stored procedures git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@1122 4979c152-3d1c-0410-bac9-87ea11338e46 --- sql/modules/Voucher.sql | 268 ++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 268 insertions(+) create mode 100644 sql/modules/Voucher.sql (limited to 'sql') diff --git a/sql/modules/Voucher.sql b/sql/modules/Voucher.sql new file mode 100644 index 00000000..6ed8fa7d --- /dev/null +++ b/sql/modules/Voucher.sql @@ -0,0 +1,268 @@ + +CREATE OR REPLACE FUNCTION voucher_get_batch (in_batch_id integer) +RETURNS batches AS +$$ +DECLARE + batch_out batches%ROWTYPE; +BEGIN + SELECT * INTO batch_out FROM batches b WHERE b.id = in_batch_id; + RETURN batch_out; +END; +$$ language plpgsql; + + +CREATE TYPE batch_list AS ( +id integer, +batch_number text, +description text, +entered date, +approved date, +amount numeric, +employee text, +manager text); + +CREATE FUNCTION batch_search +(in_batch text, in_description text, in_batch_number text, in_date_from date, + in_date_to date, in_date_include date, in_approved boolean) +RETURNS SETOF batch_list +AS $$ +DECLARE + batch_out batch_list; +BEGIN + FOR batch_out IN + SELECT b.id, b.batch, b.batch_number, b.description, + b.entered, b.approved, b.amount, + e.name AS employee, m.name AS manager + FROM batches b + LEFT JOIN employees e ON (b.employee_id = e.id) + LEFT JOIN employees m ON (b.managerid = m.id) + WHERE supplied_and_equal(in_batch, b.batch) + AND supplied_and_like(in_description, description) + AND supplied_and_like(in_batch_number, batch_number) + AND supplied_and_later(in_date_from, entered) + AND supplied_and_earlier(in_date_to, entered) + AND (coalesce(in_approved, 'f') = (approved IS NULL)) + + LOOP + RETURN NEXT batch_out; + END LOOP; +END; +$$ language PLPGSQL; + +CREATE FUNCTION batch_post (in_batch text, in_login varchar, in_entered date, + in_batch_number text, in_description text, in_id integer) +RETURNS integer AS +$$ +BEGIN + UPDATE batches + SET batch_number = in_batch_number, + description = in_description, + entered = in_entered + WHERE id = in_id; + + IF FOUND THEN + RETURN in_id; + END IF; + + INSERT INTO batches (batch, employee_id, batch_number, description, + entered) + VALUES (in_batch, (SELECT id FROM employees WHERE login = in_login), + in_batch_number, description); + + RETURN currval('id'); +END; +$$ LANGUAGE PLPGSQL; + +CREATE TYPE voucher_list AS ( + id int, + reference text, + description text, + batch_id int, + transaction_id integer, + amount numeric, + transaction_date date, + voucher_number text +); + +CREATE OR REPLACE FUNCTION voucher_list_ap (in_batch_id integer) +RETURNS SETOF voucher_list AS +$$ +DECLARE + voucher_out voucher_list%ROWTYPE; +BEGIN + FOR voucher_out IN SELECT v.id, a.invnumber AS reference, + c.name ||' -- ' || c.vendornumber AS description, + v.batch_id, a.id AS transaction_id, + a.amount, v.voucher_number + FROM vouchers v + JOIN ap a ON (a.id = v.trans_id) + JOIN vendor c ON (c.id = a.vendor_id) + WHERE v.br_id = in_batch_id + + LOOP + RETURN NEXT voucher_out; + END LOOP; + +END; +$$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION voucher_list_payment (in_batch_id integer) +RETURNS SETOF voucher_list AS +$$ +DECLARE + voucher_out voucher_list%ROWTYPE; +BEGIN + FOR voucher_out IN SELECT v.id, c.vendornumber AS reference, + c.name AS description, in_batch_id AS batch_id, + v.transaction_id AS transaction_id, sum(ac.amount) AS amount, + v.voucher_number + FROM acc_trans ac + JOIN vouchers v ON (v.id = ac.vr_id AND v.transaction_id = ac.trans_id) + JOIN chart ch ON (ch.id = ac.chart_id) + JOIN ap a ON (a.id = ac.trans_id) + JOIN vendor c ON (c.id = a.vendor_id) + WHERE v.br_id = in_batch_id + AND ch.link LIKE '%AP_paid%' + GROUP BY v.id, c.name, c.vendornumber, v.voucher_number, + a.vendor_id, v.transaction_id + + + LOOP + RETURN NEXT voucher_out; + END LOOP; + +END; +$$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION voucher_list_payment_reversal (in_batch_id integer) +RETURNS SETOF voucher_list AS +$$ +DECLARE + voucher_out voucher_list%ROWTYPE; +BEGIN + FOR voucher_out IN + SELECT v.id, ac.source AS reference, + c.vendornumber || ' -- ' || c.name AS description, + sum(ac.amount) * -1 AS amount, in_batch_id AS batch_id, + v.transaction_id AS transaction_id, v.voucher_number + FROM acc_trans ac + JOIN vr v ON (v.id = ac.vr_id AND v.trans_id = ac.trans_id) + JOIN chart ch ON (ch.id = ac.chart_id) + JOIN ap a ON (a.id = ac.trans_id) + JOIN vendor c ON (c.id = a.vendor_id) + WHERE vr.br_id = in_batch_id + AND c.link LIKE '%AP_paid%' + GROUP BY v.id, c.name, c.vendornumber, v.voucher_number, + a.vendor_id, ac.source + + LOOP + RETURN NEXT voucher_out; + END LOOP; + +END; +$$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION voucher_list_ap (in_batch_id integer) +RETURNS SETOF voucher_list AS +$$ +DECLARE + voucher_out voucher_list%ROWTYPE; +BEGIN + FOR voucher_out IN + SELECT v.id, g.reference, g.description, in_batch_id AS batch_id, + SUM(ac.amount) AS amount, g.id AS transaction_id, + v.vouchernumber + FROM acc_trans ac + JOIN gl g ON (g.id = ac.trans_id) + JOIN vouchers v ON (v.trans_id = g.id) + WHERE v.batch_id = in_batch_id + AND ac.amount >= 0 + GROUP BY g.id, g.reference, g.description, v.id, + v.voucher_number + + LOOP + RETURN NEXT voucher_out; + END LOOP; + +END; +$$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION batch_post (in_batch_id integer[], in_batch text, + in_control_amount NUMERIC) +RETURNS BOOL AS +$$ +DECLARE + control_amount NUMERIC; + voucher vouchers%ROWTYPE; + incriment NUMERIC; +BEGIN +-- CHECK CONTROL NUMBERS + IF in_batch = 'gl' THEN + SELECT sum(amount) INTO control_amount + FROM acc_trans + WHERE trans_id IN ( + SELECT id FROM gl + WHERE coalesce(approved, false) != true) + AND trans_id IN ( + SELECT transaction_id FROM vouchers + WHERE batch_id = ANY (in_batch_id)) + AND coalesce(approved, false) != true + AND amount > 0 + FOR UPDATE; + + ELSE IF in_batch like '%payment%' THEN + + SELECT sum(ac.amount) INTO control_amount + FROM acc_trans ac + JOIN vouchers v ON (v.transaction_id = ac.trans_id) + WHERE v.batch_id = ANY (in_batch_id) + AND ac.vr_id = v.id + AND coalesce(approved, false) = false + FOR UPDATE; + + ELSE + SELECT sum(amount) INTO control_amount + FROM acc_trans + WHERE trans_id IN + (SELECT transaction_id FROM vouchers + WHERE batch_id = ANY (in_batch_id)) + AND trans_id IN + (SELECT trans_id FROM ap + WHERE coalesce(approved, false) = false) + AND amount > 0 + FOR UPDATE; + + END IF; + + IF control_amount != in_control_amount THEN + RETURN FALSE; + END IF; + +-- TODO: POST TRANSACTIONALLY + + IF in_batch like '%payment%' THEN + ELSE + UPDATE acc_trans + SET approved = true + WHERE trans_id IN + (SELECT transaction_id FROM vouchers + WHERE batch_id = ANY (in_batch_id)); + + IF in_batch = 'gl' THEN + + UPDATE gl SET approved = true + WHERE trans_id IN + (SELECT transaction_id FROM vouchers + WHERE batch_id = ANY (in_batch_id)); + + ELSE + UPDATE ap SET approved = true + WHERE trans_id IN + (SELECT transaction_id FROM vouchers + WHERE batch_id = ANY (in_batch_id)); + END IF; + END IF; + + RETURN TRUE; +END; +$$ LANGUAGE PLPGSQL; -- cgit v1.2.3