SF.net SVN: ledger-smb:[5067] addons/1.3

[..hidden..]

Revision: 5067
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=5067&view=rev
Author:   einhverfr
Date:     2012-07-30 15:12:16 +0000 (Mon, 30 Jul 2012)
Log Message:
-----------
Adding Mark Lubratt's gl stored proc for entering gl transactions with some modifications

Added Paths:
-----------
    addons/1.3/gl_stored_proc/
    addons/1.3/gl_stored_proc/trunk/
    addons/1.3/gl_stored_proc/trunk/sql/
    addons/1.3/gl_stored_proc/trunk/sql/modules/
    addons/1.3/gl_stored_proc/trunk/sql/modules/gl-stored-proc.sql

Added: addons/1.3/gl_stored_proc/trunk/sql/modules/gl-stored-proc.sql
===================================================================
--- addons/1.3/gl_stored_proc/trunk/sql/modules/gl-stored-proc.sql	                        (rev 0)
+++ addons/1.3/gl_stored_proc/trunk/sql/modules/gl-stored-proc.sql	2012-07-30 15:12:16 UTC (rev 5067)
@@ -0,0 +1,71 @@
+begin;
+DROP FUNCTION IF EXISTS gl__transaction_create(date, text, text, text, boolean, integer, text[], numeric[], numeric[], text[], text[]);
+
+CREATE OR REPLACE FUNCTION gl__transaction_create(trans_date date, ref text, description text, notes text, approved boolean, department_id integer, chart_id text[], debit numeric[], credit numeric[], source text[], memo text[])
+  RETURNS boolean AS
+$BODY$
+declare
+trans_id	INTEGER;
+p_id	INTEGER;
+total	NUMERIC;
+marray	RECORD;
+chart	RECORD;
+begin
+-- Check the sizes of the passed arrays to verify they are the same size.
+
+IF array_upper(chart_id, 1) <> array_upper(debit, 1)
+   OR array_upper(chart_id, 1) <> array_upper(credit, 1)
+   OR array_upper(chart_id, 1) <> array_upper(source, 1)
+   OR array_upper(chart_id, 1) <> array_upper(memo, 1) 
+THEN
+   RAISE EXCEPTION 'arrays not balanced';
+END IF;
+
+IF sum(unnest(debit)) <> sum(unnest(credit)) THEN
+   -- Exception is short for machine processing, more info in warning for logs
+   RAISE WARNING 'Unbalanced transaction, %', total;
+   RAISE EXCEPTION 'Transaction Not Balanced';
+END IF;
+
+-- Get a transaction ID.
+
+select * into trans_id from nextval('id'::regclass);
+
+-- Make an entry into the gl table.
+
+insert into gl (id, reference, description, transdate, person_id, notes, approved, department_id)
+values (trans_id::integer, ref, description, trans_date, (select id from users where username=SESSION_USER), notes,
+approved, department_id);
+
+-- Loop through arrays and make entries to acc_trans.
+
+for marray in select unnest(chart_id) as cid, unnest(debit) as d, unnest(credit) as c, unnest(source) as s, unnest(memo) as m loop
+begin
+select * into strict chart from account__get_from_accno(marray.cid);
+exception
+when NO_DATA_FOUND then
+raise 'Account % not found', marray.cid;
+when TOO_MANY_ROWS then
+raise 'Account % not unique.', marray.cid;
+end;
+
+total := marray.c - marray.d;
+
+insert into acc_trans(trans_id, chart_id, amount, transdate, source, memo)
+values(trans_id, chart.id, total, trans_date, marray.s, marray.m);
+end loop;
+
+-- If transaction is approved, update the transactions table.
+
+if approved then
+if not draft_approve(trans_id) then
+raise 'Unable to approve this transaction.';
+end if;
+end if;
+
+return TRUE;
+end;
+$BODY$
+  LANGUAGE plpgsql VOLATILE SECURITY DEFINER;
+
+COMMIT;

This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.