CREATE OR REPLACE FUNCTION voucher_get_batch (in_batch_id integer) RETURNS batch AS $$ DECLARE batch_out batch%ROWTYPE; BEGIN SELECT * INTO batch_out FROM batch b WHERE b.id = in_batch_id; RETURN batch_out; END; $$ language plpgsql; CREATE OR REPLACE FUNCTION batch_update (in_batch text, in_login varchar, in_entered date, in_batch_number text, in_description text, in_id integer) RETURNS integer AS $$ BEGIN UPDATE batch 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 batch (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, batch_class text ); CREATE OR REPLACE FUNCTION voucher_list (in_batch_id integer) RETURNS SETOF voucher_list AS $$ declare voucher_item record; BEGIN FOR voucher_item IN SELECT v.id, a.invnumber, e.name, v.batch_id, v.trans_id, a.amount, a.transdate, 'Payable' FROM voucher v JOIN ap a ON (v.trans_id = a.id) JOIN entity_credit_account eca ON (eca.id = a.entity_credit_account) JOIN entity e ON (eca.entity_id = e.id) WHERE v.batch_id = in_batch_id AND v.batch_class = (select id from batch_class WHERE class = 'ap') UNION SELECT v.id, a.invnumber, e.name, v.batch_id, v.trans_id, a.amount, a.transdate, 'Receivable' FROM voucher v JOIN ar a ON (v.trans_id = a.id) JOIN entity_credit_account eca ON (eca.id = a.entity_credit_account) JOIN entity e ON (eca.entity_id = e.id) WHERE v.batch_id = in_batch_id AND v.batch_class = (select id from batch_class WHERE class = 'ar') UNION ALL -- TODO: Add the class labels to the class table. SELECT v.id, a.source, cr.meta_number || '--' || co.legal_name , v.batch_id, v.trans_id, CASE WHEN bc.class LIKE 'payment%' THEN a.amount * -1 ELSE a.amount END, a.transdate, CASE WHEN bc.class = 'payment' THEN 'Payment' WHEN bc.class = 'payment_reversal' THEN 'Payment Reversal' END FROM voucher v JOIN acc_trans a ON (v.id = a.voucher_id) JOIN batch_class bc ON (bc.id = v.batch_class) JOIN chart c ON (a.chart_id = c.id) JOIN ap ON (ap.id = a.trans_id) JOIN entity_credit_account cr ON (ap.entity_credit_account = cr.id) JOIN company co ON (cr.entity_id = co.entity_id) WHERE v.batch_id = in_batch_id AND a.voucher_id = v.id AND (bc.class like 'payment%' AND c.link = 'AP') UNION ALL SELECT v.id, a.source, a.memo, v.batch_id, v.trans_id, CASE WHEN bc.class LIKE 'receipt%' THEN a.amount * -1 ELSE a.amount END, a.transdate, CASE WHEN bc.class = 'receipt' THEN 'Receipt' WHEN bc.class = 'receipt_reversal' THEN 'Receipt Reversal' END FROM voucher v JOIN acc_trans a ON (v.trans_id = a.trans_id) JOIN batch_class bc ON (bc.id = v.batch_class) JOIN chart c ON (a.chart_id = c.id) JOIN ar ON (ar.id = a.trans_id) JOIN entity_credit_account cr ON (ar.entity_credit_account = cr.id) JOIN company co ON (cr.entity_id = co.entity_id) WHERE v.batch_id = in_batch_id AND a.voucher_id = v.id AND (bc.class like 'receipt%' AND c.link = 'AR') UNION ALL SELECT v.id, g.reference, g.description, v.batch_id, v.trans_id, sum(a.amount), g.transdate, 'gl' FROM voucher v JOIN gl g ON (g.id = v.trans_id) JOIN acc_trans a ON (v.trans_id = a.trans_id) WHERE a.amount > 0 AND v.batch_id = in_batch_id AND v.batch_class IN (select id from batch_class where class = 'gl') GROUP BY v.id, g.reference, g.description, v.batch_id, v.trans_id, g.transdate ORDER BY 7, 1 LOOP RETURN NEXT voucher_item; END LOOP; END; $$ language plpgsql; CREATE TYPE batch_list_item AS ( id integer, batch_class text, control_code text, description text, created_by text, created_on date, transaction_total numeric, payment_total numeric ); CREATE OR REPLACE FUNCTION batch_search(in_class_id int, in_description text, in_created_by_eid int, in_amount_gt numeric, in_amount_lt numeric, 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, sum( CASE WHEN vc.id = 5 AND al.amount < 0 -- GL THEN al.amount WHEN vc.id = 1 THEN ap.amount WHEN vc.id = 2 THEN ap.amount ELSE 0 END) AS transaction_total, sum( CASE WHEN alc.link = 'AR' AND vc.id IN (6, 7) THEN al.amount WHEN alc.link = 'AP' AND vc.id IN (3, 4) THEN al.amount * -1 ELSE 0 END ) AS payment_total 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) JOIN batch_class vc ON (v.batch_class = vc.id) LEFT JOIN ar ON (vc.id = 2 AND v.trans_id = ar.id) LEFT JOIN ap ON (vc.id = 1 AND v.trans_id = ap.id) LEFT JOIN acc_trans al ON ((vc.id = 5 AND v.trans_id = al.trans_id) OR (vc.id IN (3, 4, 6, 7) AND al.voucher_id = v.id)) LEFT JOIN chart alc ON (al.chart_id = alc.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 HAVING (in_amount_gt IS NULL OR sum(coalesce(ar.amount - ar.paid, ap.amount - ap.paid, al.amount)) >= in_amount_gt) AND (in_amount_lt IS NULL OR sum(coalesce(ar.amount - ar.paid, ap.amount - ap.paid, al.amount)) <= in_amount_lt) LOOP RETURN NEXT out_value; END LOOP; 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) 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) returns date AS $$ BEGIN UPDATE ar SET approved = true WHERE id IN (select trans_id FROM voucher WHERE batch_id = in_batch_id AND batch_class = 2); UPDATE ap SET approved = true WHERE id IN (select trans_id FROM voucher WHERE batch_id = in_batch_id AND batch_class = 1); UPDATE gl SET approved = true WHERE id IN (select trans_id FROM voucher WHERE batch_id = in_batch_id AND batch_class = 5); UPDATE acc_trans SET approved = true WHERE voucher_id IN (select id FROM voucher WHERE batch_id = in_batch_id AND batch_class IN (3, 4, 7, 8)); UPDATE batch SET approved_on = now(), approved_by = (select entity_id FROM users WHERE username = SESSION_USER) WHERE id = in_batch_id; RETURN now()::date; END; $$ LANGUAGE PLPGSQL; CREATE OR REPLACE FUNCTION batch_list_classes() RETURNS SETOF batch_class AS $$ DECLARE out_val record; BEGIN FOR out_val IN select * from batch_class LOOP return next out_val; END LOOP; END; $$ language plpgsql; CREATE OR REPLACE FUNCTION batch_get_users() RETURNS SETOF users AS $$ DECLARE out_record users%ROWTYPE; BEGIN FOR out_record IN SELECT * from users WHERE entity_id IN (select created_by from batch) LOOP RETURN NEXT out_record; END LOOP; END; $$ LANGUAGE PLPGSQL; CREATE OR REPLACE FUNCTION batch_create( in_batch_number text, in_description text, in_batch_class text, in_batch_date date) RETURNS int AS $$ BEGIN INSERT INTO batch (batch_class_id, default_date, description, control_code, created_by) VALUES ((SELECT id FROM batch_class WHERE class = in_batch_class), in_batch_date, in_description, in_batch_number, (select entity_id FROM users WHERE username = session_user)); return currval('batch_id_seq'); END; $$ LANGUAGE PLPGSQL; CREATE OR REPLACE FUNCTION batch_delete(in_batch_id int) RETURNS int AS $$ DECLARE t_transaction_ids int[]; BEGIN -- Adjust AR/AP tables for payment and payment reversal vouchers -- voucher_id is only set in acc_trans on payment/receipt vouchers and -- their reversals. -CT update ar set paid = amount + (select sum(amount) from acc_trans join chart ON (acc_trans.chart_id = chart.id) where link = 'AR' AND trans_id = ar.id AND (voucher_id IS NULL OR voucher_id NOT IN (select id from voucher WHERE batch_id = in_batch_id))) where id in (select trans_id from acc_trans where voucher_id IN (select id from voucher where batch_id = in_batch_id)); update ap set paid = amount - (select sum(amount) from acc_trans join chart ON (acc_trans.chart_id = chart.id) where link = 'AP' AND trans_id = ap.id AND (voucher_id IS NULL OR voucher_id NOT IN (select id from voucher WHERE batch_id = in_batch_id))) where id in (select trans_id from acc_trans where voucher_id IN (select id from voucher where batch_id = in_batch_id)); DELETE FROM acc_trans WHERE voucher_id IN (select id FROM voucher where batch_id = in_batch_id); -- The rest of this function involves the deletion of actual -- transactions, vouchers, and batches, and jobs which are in progress. -- -CT SELECT as_array(trans_id) INTO t_transaction_ids FROM voucher WHERE batch_id = in_batch_id AND batch_class IN (1, 2, 5); DELETE FROM acc_trans WHERE trans_id = ANY(t_transaction_ids); DELETE FROM ap WHERE id = ANY(t_transaction_ids); DELETE FROM gl WHERE id = ANY(t_transaction_ids); DELETE FROM voucher WHERE batch_id = in_batch_id; DELETE FROM payments_queue WHERE batch_id = in_batch_id; DELETE FROM pending_job WHERE batch_id = in_batch_id; DELETE FROM batch WHERE id = in_batch_id; DELETE FROM transactions WHERE id = ANY(t_transaction_ids); RETURN 1; END; $$ language plpgsql SECURITY DEFINER; REVOKE ALL ON FUNCTION batch_delete(int) FROM PUBLIC; CREATE OR REPLACE FUNCTION voucher__delete(in_voucher_id int) RETURNS int AS $$ DECLARE voucher_row RECORD; BEGIN SELECT * INTO voucher_row FROM voucher WHERE id = in_voucher_id; IF voucher_row.batch_class IN (1, 2, 5) THEN DELETE from acc_trans WHERE trans_id = voucher_row.trans_id; DELETE FROM ar WHERE id = voucher_row.trans_id; DELETE FROM ap WHERE id = voucher_row.trans_id; DELETE FROM gl WHERE id = voucher_row.trans_id; DELETE FROM voucher WHERE id = voucher_row.id; DELETE FROM transactions WHERE id = voucher_row.trans_id; ELSE update ar set paid = amount + (select sum(amount) from acc_trans join chart ON (acc_trans.chart_id = chart.id) where link = 'AR' AND trans_id = ar.id AND (voucher_id IS NULL OR voucher_id <> voucher_row.id)) where id in (select trans_id from acc_trans where voucher_id = voucher_row.id); update ap set paid = amount - (select sum(amount) from acc_trans join chart ON (acc_trans.chart_id = chart.id) where link = 'AP' AND trans_id = ap.id AND (voucher_id IS NULL OR voucher_id <> voucher_row.id)) where id in (select trans_id from acc_trans where voucher_id = voucher_row.id); DELETE FROM acc_trans where voucher_id = voucher_row.id; END IF; RETURN 1; END; $$ LANGUAGE PLPGSQL SECURITY DEFINER; REVOKE ALL ON FUNCTION voucher__delete(int) FROM public;