Good morning!
In order to integrate LSMB into another business workflow application I've
developed, I've written a stored procedure that can be called to insert GL
transactions into the LSMB database instead of trying to use HTTP calls to
the web based system. I know that Chris is starting to move to bindings
for other languages; so, I don't know if this would be useful for everyone,
or not. There's probably more work that could be done here. I only did
some rudimentary error checking and I'm not terribly familiar with the
schema. I attempted to do something similar for AR and AP as well, but I
ran out of development time before I was able to get them working.
Anyway, here it is...
Best regards,
Mark
----------------------------------------------------------
-- Function: gl__transaction_create(date, text, text, text, boolean,
integer, text[], numeric[], numeric[], text[], text[])
-- DROP FUNCTION 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
array_size INTEGER;
array_size2 INTEGER;
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.
array_size := 0;
for marray in select * from unnest(chart_id) loop
array_size := array_size + 1;
end loop;
array_size2 := 0;
for marray in select * from unnest(debit) loop
array_size2 := array_size2 + 1;
end loop;
if array_size <> array_size2 then
raise 'Arrays are not balanced.';
end if;
array_size2 := 0;
for marray in select * from unnest(credit) loop
array_size2 := array_size2 + 1;
end loop;
if array_size <> array_size2 then
raise 'Arrays are not balanced.';
end if;
array_size2 := 0;
for marray in select * from unnest(source) loop
array_size2 := array_size2 + 1;
end loop;
if array_size <> array_size2 then
raise 'Arrays are not balanced.';
end if;
array_size2 := 0;
for marray in select * from unnest(memo) loop
array_size2 := array_size2 + 1;
end loop;
if array_size <> array_size2 then
raise 'Arrays are not balanced.';
end if;
-- Be sure the transaction is balanced.
total := 0;
for marray in select unnest(debit) as d, unnest(credit) as c loop
total := total + marray.d - marray.c;
end loop;
if total <> 0 then
raise 'The transaction is not balanced. %', total;
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
COST 100;
ALTER FUNCTION gl__transaction_create(date, text, text, text, boolean,
integer, text[], numeric[], numeric[], text[], text[]) OWNER TO mpl;
GRANT EXECUTE ON FUNCTION gl__transaction_create(date, text, text, text,
boolean, integer, text[], numeric[], numeric[], text[], text[]) TO public;
GRANT EXECUTE ON FUNCTION gl__transaction_create(date, text, text, text,
boolean, integer, text[], numeric[], numeric[], text[], text[]) TO mpl;
GRANT EXECUTE ON FUNCTION gl__transaction_create(date, text, text, text,
boolean, integer, text[], numeric[], numeric[], text[], text[]) TO
lsmb_josephine_ledger__gl_voucher_create;
GRANT EXECUTE ON FUNCTION gl__transaction_create(date, text, text, text,
boolean, integer, text[], numeric[], numeric[], text[], text[]) TO
lsmb_josephine_ledger__gl_transaction_create;
------------------------------------------------------------------------------
Live Security Virtual Conference
Exclusive live event will cover all the ways today's security and
threat landscape has changed and how IT managers can respond. Discussions
will include endpoint security, mobile security and the latest in malware
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
_______________________________________________
Ledger-smb-devel mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/ledger-smb-devel
