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;