[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb: [1983] trunk/sql/modules
- Subject: SF.net SVN: ledger-smb: [1983] trunk/sql/modules
- From: ..hidden..
- Date: Wed, 19 Dec 2007 14:17:24 -0800
Revision: 1983
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=1983&view=rev
Author: einhverfr
Date: 2007-12-19 14:17:24 -0800 (Wed, 19 Dec 2007)
Log Message:
-----------
Some bulk payment enhancements
Modified Paths:
--------------
trunk/sql/modules/Payment.sql
trunk/sql/modules/Voucher.sql
Modified: trunk/sql/modules/Payment.sql
===================================================================
--- trunk/sql/modules/Payment.sql 2007-12-19 17:25:47 UTC (rev 1982)
+++ trunk/sql/modules/Payment.sql 2007-12-19 22:17:24 UTC (rev 1983)
@@ -113,7 +113,8 @@
contact_name text,
account_number text,
total_due numeric,
- invoices text[]
+ invoices text[],
+ has_vouchers int
);
CREATE OR REPLACE FUNCTION payment_get_all_contact_invoices
@@ -142,25 +143,30 @@
THEN 0
ELSE (a.amount - coalesce(a.paid, 0)) * coalesce(c.discount, 0) / 100
END))::text]]),
+ sum(case when v.batch_id = in_batch_id then 1
+ else 0 END),
bool_and(lock_record(a.id, (select max(session_id) FROM "session" where users_id = (
select id from users WHERE username =
SESSION_USER))))
+
FROM entity e
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, on_hold
FROM ap
+ WHERE in_account_class = 1
UNION
SELECT id, invnumber, transdate, amount, entity_id,
paid, curr, 2 as invoice_class,
entity_credit_account, on_hold
FROM ar
+ WHERE in_account_class = 2
ORDER BY transdate
) a USING (entity_id)
JOIN transactions t ON (a.id = t.id)
- WHERE a.id IN (select voucher.trans_id FROM voucher
- WHERE batch_id = in_batch_id)
+ LEFT JOIN voucher v ON (v.trans_id = a.id)
+ WHERE v.batch_id = in_batch_id
OR (a.invoice_class = in_account_class
AND c.business_id =
coalesce(in_business_id, c.business_id)
@@ -184,6 +190,8 @@
))
GROUP BY c.id, e.name, c.meta_number, c.threshold
HAVING sum(a.amount - a.paid) > c.threshold
+ OR sum(case when v.batch_id = in_batch_id then 1
+ else 0 END) > 0
LOOP
RETURN NEXT payment_item;
END LOOP;
@@ -334,7 +342,7 @@
LOOP
INSERT INTO acc_trans
(trans_id, chart_id, amount, approved, voucher_id,
- transdate)
+ transdate, source)
VALUES
(in_transactions[out_count][1],
case when in_account_class = 1 THEN t_cash_id
@@ -345,7 +353,7 @@
CASE WHEN t_voucher_id IS NULL THEN true
ELSE false END,
- t_voucher_id, in_payment_date),
+ t_voucher_id, in_payment_date, in_source),
(in_transactions[out_count][1],
case when in_account_class = 1 THEN t_ar_ap_id
@@ -356,7 +364,7 @@
CASE WHEN t_voucher_id IS NULL THEN true
ELSE false END,
- t_voucher_id, in_payment_date);
+ t_voucher_id, in_payment_date, in_source);
UPDATE ap
set paid = paid +in_transactions[out_count][2]
where id =in_transactions[out_count][1];
@@ -565,28 +573,18 @@
max(cc.description), max(ac.id), max(ac.accno),
max(ac.description)
FROM acc_trans at
- JOIN entity_credit_account ec ON
- (at.trans_id IN
- (select id FROM ar
- WHERE in_account_class = 2
- AND entity_credit_account =
- (SELECT id
- FROM entity_credit_account
- WHERE meta_number
- = in_meta_number
- AND entity_class =
- in_account_class)
- UNION
- SELECT id FROM ap
- WHERE in_account_class = 1 AND
- entity_credit_account =
- (select id
- FROM entity_credit_account
- WHERE meta_number
- = in_meta_number
- AND entity_class =
- in_account_class)))
-
+ JOIN (select id, entity_credit_account
+ FROM ar
+ WHERE in_account_class = 2
+ UNION
+ SELECT id, entity_credit_account
+ FROM ap
+ WHERE in_account_class = 1) arap
+ ON (arap.id = at.trans_id)
+
+ JOIN entity_credit_account ec ON (
+ ec.entity_class = in_account_class
+ AND arap.entity_credit_account = ec.id)
JOIN company c ON (ec.entity_id = c.entity_id)
LEFT JOIN chart cc ON (at.chart_id = cc.id AND
cc.link LIKE '%paid%')
Modified: trunk/sql/modules/Voucher.sql
===================================================================
--- trunk/sql/modules/Voucher.sql 2007-12-19 17:25:47 UTC (rev 1982)
+++ trunk/sql/modules/Voucher.sql 2007-12-19 22:17:24 UTC (rev 1983)
@@ -71,25 +71,45 @@
WHERE class = 'ar')
UNION
-- TODO: Add the class labels to the class table.
- SELECT v.id, a.source, a.memo, v.batch_id, v.trans_id,
+ 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 amount END, a.transdate,
+ ELSE a.amount END, a.transdate,
CASE WHEN bc.class = 'payment' THEN 'Payment'
- WHEN bc.class = 'receipt' THEN 'Receipt'
WHEN bc.class = 'payment_reversal'
THEN 'Payment 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 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
+ SELECT v.id, a.source, a.memo, 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 = 'receipt' THEN 'Receipt'
WHEN bc.class = 'receipt_reversal'
THEN 'Receipt Reversal'
- ELSE 'UNKNOWN'
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 'payment%' AND c.link = 'AP')
- OR (bc.class like 'receipt%' AND c.link = 'AR')
+ AND (bc.class like 'receipt%' AND c.link = 'AR')
UNION
SELECT v.id, g.reference, g.description, v.batch_id, v.trans_id,
sum(a.amount), g.transdate, 'gl'
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.