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

1 files changed, 268 insertions, 0 deletions

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;