summaryrefslogtreecommitdiff
path: root/sql
diff options
context:
space:
mode:
Diffstat (limited to 'sql')
-rw-r--r--sql/modules/Payment.sql43
-rw-r--r--sql/modules/Voucher.sql70
2 files changed, 91 insertions, 22 deletions
diff --git a/sql/modules/Payment.sql b/sql/modules/Payment.sql
index 77a48045..56eb0e9d 100644
--- a/sql/modules/Payment.sql
+++ b/sql/modules/Payment.sql
@@ -149,12 +149,12 @@ BEGIN
JOIN entity_credit_account c ON (e.id = c.entity_id)
JOIN (SELECT id, invnumber, transdate, amount, entity_id,
paid, curr, 1 as invoice_class,
- entity_credit_account
+ entity_credit_account, on_hold
FROM ap
UNION
SELECT id, invnumber, transdate, amount, entity_id,
paid, curr, 2 as invoice_class,
- entity_credit_account
+ entity_credit_account, on_hold
FROM ar
ORDER BY transdate
) a USING (entity_id)
@@ -170,6 +170,7 @@ BEGIN
AND a.curr = in_currency
AND a.entity_credit_account = c.id
AND a.amount - a.paid <> 0
+ AND NOT a.on_hold
AND NOT (t.locked_by IS NOT NULL AND t.locked_by IN
(select "session_id" FROM "session"
WHERE users_id IN
@@ -231,6 +232,7 @@ RETURNS bool AS $$
DECLARE
queue_record RECORD;
t_auth_name text;
+ t_counter int;
BEGIN
-- TODO: Move the set session authorization into a utility function
SELECT entered_by INTO t_auth_name FROM pending_job
@@ -238,18 +240,30 @@ BEGIN
EXECUTE 'SET SESSION AUTHORIZATION ' || quote_ident(t_auth_name);
- FOR queue_record IN
- SELECT * from payments_queue WHERE job_id = in_job_id
+ t_counter := 0;
+
+ FOR queue_record IN
+ SELECT *
+ FROM payments_queue WHERE job_id = in_job_id
LOOP
PERFORM payment_bulk_post
- (transactions, batch_id, source, total, ar_ap_accno, cash_accno,
- payment_date, account_class)
- FROM payments_queue WHERE job_id = in_job_id;
- END LOOP;
- UPDATE pending_job
- SET completed_at = timeofday()::timestamp,
- success = true
- WHERE id = in_job_id;
+ (queue_record.transactions, queue_record.batch_id,
+ queue_record.source, queue_record.total,
+ queue_record.ar_ap_accno,
+ queue_record.cash_accno,
+ queue_record.payment_date,
+ queue_record.account_class);
+
+ t_counter := t_counter + 1;
+ RAISE NOTICE 'Processed record %, starting transaction %',
+ t_counter, queue_record.transactions[1][1];
+ END LOOP;
+ DELETE FROM payments_queue WHERE job_id = in_job_id;
+
+ UPDATE pending_job
+ SET completed_at = timeofday()::timestamp,
+ success = true
+ WHERE id = in_job_id;
RETURN TRUE;
END;
$$ language plpgsql;
@@ -259,7 +273,7 @@ RETURNS int AS
$$
BEGIN
INSERT INTO pending_job (batch_class, batch_id)
- VALUES (in_batch_class, in_batch_id);
+ VALUES (coalesce(in_batch_class, 3), in_batch_id);
RETURN currval('pending_job_id_seq');
END;
@@ -300,7 +314,8 @@ DECLARE
t_amount numeric;
BEGIN
IF in_batch_id IS NULL THEN
- t_voucher_id := NULL;
+ -- t_voucher_id := NULL;
+ RAISE EXCEPTION 'Bulk Post Must be from Batch!';
ELSE
INSERT INTO voucher (batch_id, batch_class, trans_id)
values (in_batch_id, 3, in_transactions[1][1]);
diff --git a/sql/modules/Voucher.sql b/sql/modules/Voucher.sql
index 0633ab5d..2bfc6fcb 100644
--- a/sql/modules/Voucher.sql
+++ b/sql/modules/Voucher.sql
@@ -101,10 +101,12 @@ $$ language plpgsql;
CREATE TYPE batch_list_item AS (
id integer,
batch_class text,
+ control_code text,
description text,
created_by text,
created_on date,
- total numeric
+ transaction_total numeric,
+ payment_total numeric
);
CREATE OR REPLACE FUNCTION
@@ -116,9 +118,23 @@ $$
DECLARE out_value batch_list_item;
BEGIN
FOR out_value IN
- SELECT b.id, c.class, b.description, u.username, b.created_on,
- sum(coalesce(ar.amount - ar.paid, ap.amount - ap.paid,
- al.amount)) AS amount
+ 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
+ THEN al.amount
+ WHEN vc.id NOT IN (3, 4, 6, 7)
+ THEN coalesce(ar.amount, ap.amount, 0)
+ ELSE 0
+ END) AS transaction_total,
+ sum(
+ CASE WHEN alc.link = 'AR' AND vc.id IN (3,4,6,7)
+ THEN al.amount
+ WHEN alc.link = 'AP' AND vc.id IN (3,4,6,7)
+ THEN al.amount * -1
+ ELSE 0
+ END
+ ) AS payment_total
FROM batch b
JOIN batch_class c ON (b.batch_class_id = c.id)
JOIN users u ON (u.entity_id = b.created_by)
@@ -128,8 +144,9 @@ BEGIN
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, 7, 8) AND al.voucher_id = v.id)
+ (vc.id IN (3, 4, 6, 7) AND al.voucher_id = v.id)
AND al.amount > 0)
+ LEFT JOIN chart alc ON (al.chart_id = alc.id)
WHERE c.id = coalesce(in_class_id, c.id) AND
b.description LIKE
'%' || coalesce(in_description, '') || '%' AND
@@ -139,7 +156,8 @@ BEGIN
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
+ GROUP BY b.id, c.class, b.description, u.username, b.created_on,
+ b.control_code
HAVING
sum(coalesce(ar.amount - ar.paid, ap.amount - ap.paid,
al.amount))
@@ -208,6 +226,18 @@ BEGIN
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) RETURNS int AS
$$
@@ -227,12 +257,34 @@ $$
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) * -1 from acc_trans
+ join chart ON (acc_trans.chart_id = chart.id)
+ where link = 'AR' AND trans_id = ar.id
+ AND 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 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 voucher_id FROM voucher where batch_id = in_batch_id);
+ (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);
@@ -240,6 +292,8 @@ BEGIN
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);