From 24708fad478ce6484e7a6987e17ffa58855b7d27 Mon Sep 17 00:00:00 2001 From: einhverfr Date: Mon, 30 Jun 2008 21:24:59 +0000 Subject: Adding batch_search_mini api Addressing issue on create batch screen of searching through all batches Adding error message in LedgerSMB::call_procedure if procname is undefined. git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@2178 4979c152-3d1c-0410-bac9-87ea11338e46 --- sql/modules/Voucher.sql | 38 ++++++++++++++++++++++++++++++++++++++ 1 file changed, 38 insertions(+) (limited to 'sql/modules') diff --git a/sql/modules/Voucher.sql b/sql/modules/Voucher.sql index c9d6cc60..5c236ed5 100644 --- a/sql/modules/Voucher.sql +++ b/sql/modules/Voucher.sql @@ -213,6 +213,44 @@ BEGIN END; $$ LANGUAGE PLPGSQL; +CREATE OR REPLACE FUNCTION batch_get_class_id (in_type text) returns int AS +$$ +SELECT id FROM batch_class WHERE class = $1; +$$ language sql; + +CREATE OR REPLACE FUNCTION +batch_search_mini +(in_class_id int, in_description text, in_created_by_eid int, in_approved bool) +RETURNS SETOF batch_list_item AS +$$ +DECLARE out_value batch_list_item; +BEGIN + FOR out_value IN + SELECT b.id, c.class, b.control_code, b.description, u.username, + b.created_on, NULL + FROM batch b + JOIN batch_class c ON (b.batch_class_id = c.id) + LEFT JOIN users u ON (u.entity_id = b.created_by) + JOIN voucher v ON (v.batch_id = b.id) + WHERE (c.id = in_class_id OR in_class_id IS NULL) AND + (b.description LIKE + '%' || in_description || '%' OR + in_description IS NULL) AND + (in_created_by_eid = b.created_by OR + in_created_by_eid IS NULL) AND + ((in_approved = false OR in_approved IS NULL AND + approved_on IS NULL) OR + (in_approved = true AND approved_on IS NOT NULL) + ) + GROUP BY b.id, c.class, b.description, u.username, b.created_on, + b.control_code + LOOP + RETURN NEXT out_value; + END LOOP; +END; +$$ LANGUAGE PLPGSQL; + + CREATE OR REPLACE FUNCTION batch_post(in_batch_id INTEGER) -- cgit v1.2.3