summaryrefslogtreecommitdiff
path: root/sql/modules
diff options
context:
space:
mode:
Diffstat (limited to 'sql/modules')
-rw-r--r--sql/modules/Payment.sql82
-rw-r--r--sql/modules/Session.sql2
2 files changed, 71 insertions, 13 deletions
diff --git a/sql/modules/Payment.sql b/sql/modules/Payment.sql
index d19a821b..d3026420 100644
--- a/sql/modules/Payment.sql
+++ b/sql/modules/Payment.sql
@@ -125,7 +125,7 @@ $$
DECLARE payment_item payment_contact_invoice;
BEGIN
FOR payment_item IN
- SELECT e.id AS contact_id, e.name AS contact_name,
+ SELECT c.id AS contact_id, e.name AS contact_name,
c.meta_number AS account_number,
sum(a.amount - a.paid) AS total_due,
compound_array(ARRAY[[
@@ -134,13 +134,13 @@ BEGIN
(CASE WHEN c.discount_terms
> extract('days' FROM age(a.transdate))
THEN 0
- ELSE (a.amount - a.paid) * c.discount / 100
+ ELSE (a.amount - coalesce(a.paid, 0)) * coalesce(c.discount, 0) / 100
END)::text,
- (a.amount - a.paid -
+ (a.amount - coalesce(a.paid, 0) -
(CASE WHEN c.discount_terms
> extract('days' FROM age(a.transdate))
THEN 0
- ELSE (a.amount - a.paid) * c.discount / 100
+ ELSE (a.amount - coalesce(a.paid, 0)) * coalesce(c.discount, 0) / 100
END))::text]]),
bool_and(lock_record(a.id, (select max(session_id) FROM "session" where users_id = (
select id from users WHERE username =
@@ -160,26 +160,28 @@ BEGIN
) a USING (entity_id)
JOIN transactions t ON (a.id = t.id)
WHERE a.invoice_class = in_account_class
- AND ((a.transdate >= in_date_from
- AND a.transdate <= in_date_to)
+ AND c.business_id =
+ coalesce(in_business_type, c.business_id)
+ AND ((a.transdate >= COALESCE(in_date_from, a.transdate)
+ AND a.transdate <= COALESCE(in_date_to, a.transdate))
OR a.id IN (select voucher.trans_id FROM voucher
WHERE batch_id = in_batch_id))
AND c.entity_class = in_account_class
AND a.curr = in_currency
AND a.entity_credit_account = c.id
AND a.amount - a.paid <> 0
- AND t.locked_by NOT IN
+ AND NOT (t.locked_by IS NOT NULL AND t.locked_by IN
(select "session_id" FROM "session"
WHERE users_id IN
(select id from users
- where username <> SESSION_USER))
+ where username <> SESSION_USER)))
AND EXISTS (select trans_id FROM acc_trans
WHERE trans_id = a.id AND
chart_id = (SELECT id frOM chart
WHERE accno
= in_ar_ap_accno)
)
- GROUP BY e.id, e.name, c.meta_number, c.threshold
+ GROUP BY c.id, e.name, c.meta_number, c.threshold
HAVING sum(a.amount - a.paid) > c.threshold
LOOP
RETURN NEXT payment_item;
@@ -206,18 +208,74 @@ $$;
CREATE OR REPLACE FUNCTION payment_bulk_post
(in_transactions numeric[], in_batch_id int, in_source text, in_total numeric,
- in_ar_ap_accno text, in_cash_accno text, in_approved bool,
+ in_ar_ap_accno text, in_cash_accno text,
in_payment_date date, in_account_class int)
RETURNS int AS
$$
-DECLARE payment_trans numeric[];
+DECLARE
+ payment_trans numeric[];
+ out_count int;
+ t_voucher_id int;
+ t_trans_id int;
+ t_amount numeric;
BEGIN
+ IF in_batch_id IS NULL THEN
+ t_voucher_id := NULL;
+ ELSE
+ INSERT INTO voucher (batch_id, trans_id)
+ values (in_batch_id, in_transactions[1][1]);
+
+ t_voucher_id := currval('voucher_id_seq');
+ END IF;
+ FOR out_count IN
+ array_lower(in_transactions, 1) ..
+ array_upper(in_transactions, 1)
+ LOOP
+ INSERT INTO acc_trans
+ (trans_id, chart_id, amount, approved, voucher_id,
+ transdate)
+ VALUES
+ (in_transactions[out_count][1],
+ case when in_account_class = 1 THEN
+ (SELECT id FROM chart
+ WHERE accno = in_cash_accno)
+ WHEN in_account_class = 2 THEN
+ (SELECT id FROM chart
+ WHERE accno = in_ar_ap_accno)
+ ELSE -1 END,
+
+ in_transactions[out_count][2],
+
+ CASE WHEN t_voucher_id IS NULL THEN true
+ ELSE false END,
+ t_voucher_id, in_payment_date),
+
+ (in_transactions[out_count][1],
+ case when in_account_class = 1 THEN
+ (SELECT id FROM chart
+ WHERE accno = in_ar_ap_accno)
+ WHEN in_account_class = 2 THEN
+ (SELECT id FROM chart
+ WHERE accno = in_cash_accno)
+ ELSE -1 END,
+
+ in_transactions[out_count][2]* -1,
+
+ CASE WHEN t_voucher_id IS NULL THEN true
+ ELSE false END,
+ t_voucher_id, in_payment_date);
+ insert into test_pay(id, amount) values (in_transactions[out_count][1],in_transactions[out_count][2]);
+ UPDATE ap
+ set paid = paid +in_transactions[out_count][2]
+ where id =in_transactions[out_count][1];
+ END LOOP;
+ return out_count;
END;
$$ language plpgsql;
COMMENT ON FUNCTION payment_bulk_post
(in_transactions numeric[], in_batch_id int, in_source text, in_total numeric,
- in_ar_ap_accno text, in_cash_accno text, in_approved bool,
+ in_ar_ap_accno text, in_cash_accno text,
in_payment_date date, in_account_class int)
IS
$$ Note that in_transactions is a two-dimensional numeric array. Of each
diff --git a/sql/modules/Session.sql b/sql/modules/Session.sql
index a44ed924..58de0f2e 100644
--- a/sql/modules/Session.sql
+++ b/sql/modules/Session.sql
@@ -21,7 +21,7 @@ BEGIN
-- session
IF NOT FOUND THEN
- SELECT id FROM users WHERE username = SESSION_USER;
+ PERFORM id FROM users WHERE username = SESSION_USER;
IF NOT FOUND THEN
RAISE EXCEPTION 'User Not Known';
END IF;