summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authoreinhverfr <einhverfr@4979c152-3d1c-0410-bac9-87ea11338e46>2007-12-19 17:25:47 +0000
committereinhverfr <einhverfr@4979c152-3d1c-0410-bac9-87ea11338e46>2007-12-19 17:25:47 +0000
commit5b20c0d8b91b5bc7c6cee549eff6c057aa210d44 (patch)
tree54a57a8c5f133f77292e5820ff061e2ec7172464
parent5f1f416312306be454a74536e036177dc1ec8f4a (diff)
More batch enhancements
git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@1982 4979c152-3d1c-0410-bac9-87ea11338e46
-rw-r--r--COMPATABILITY5
-rw-r--r--LedgerSMB/Batch.pm12
-rw-r--r--LedgerSMB/DBObject/Payment.pm7
-rw-r--r--UI/form-dynatable.html2
-rw-r--r--sql/modules/Payment.sql26
-rw-r--r--sql/modules/Voucher.sql41
6 files changed, 62 insertions, 31 deletions
diff --git a/COMPATABILITY b/COMPATABILITY
index 6d0d243d..af1cf711 100644
--- a/COMPATABILITY
+++ b/COMPATABILITY
@@ -6,6 +6,11 @@ LedgerSMB: 8.2.2, 8.1.7, and 8.0.11
These versions produce an error concerning wrong data types but the error shows
that the data types are indeed correct.
+PostgreSQL 8.3 will be supported on a best effort basis. You will need the
+tsearch2 compatibility libraries. Additionally, due to the changes in implicit
+casts, bugs may exist which cause database operations to abort. We do accept
+bug reports and will address them, however.
+
-------------------------------
W3M does not handle the <BUTTON> element properly and does not work.
diff --git a/LedgerSMB/Batch.pm b/LedgerSMB/Batch.pm
index 28be86f7..67bc0ad9 100644
--- a/LedgerSMB/Batch.pm
+++ b/LedgerSMB/Batch.pm
@@ -28,4 +28,16 @@ sub get_search_results {
return @{$self->{search_results}};
}
+sub post {
+ my ($self) = @_;
+ ($self->{post_return_ref}) = $self->exec_method(funcname => 'batch_post');
+ return $self->{post_return_ref};
+}
+
+sub delete {
+ my ($self) = @_;
+ ($self->{delete_ref}) = $self->exec_method(funcname => 'batch_delete');
+ return $self->{delete_ref};
+}
+
1;
diff --git a/LedgerSMB/DBObject/Payment.pm b/LedgerSMB/DBObject/Payment.pm
index f4382a28..b35bb2d8 100644
--- a/LedgerSMB/DBObject/Payment.pm
+++ b/LedgerSMB/DBObject/Payment.pm
@@ -366,14 +366,19 @@ sub get_payment_detail_data {
if ($source_src) {
$source_inc = $source_src;
} else {
- $source_inc = $0;
+ $source_inc = 0;
}
}
+ my $source_length = length($source_inc);
+
@{$self->{contact_invoices}} = $self->exec_method(
funcname => 'payment_get_all_contact_invoices');
for my $inv (@{$self->{contact_invoices}}){
if (defined $self->{source_start}){
my $source = $self->{source_start};
+ if (length($source_inc) < $source_length){
+ $source_inc = sprintf('%0*s', $source_length, $source_inc);
+ }
$source =~ s/$source_src(\D*)$/$source_inc$1/;
++ $source_inc;
$inv->{source} = $source;
diff --git a/UI/form-dynatable.html b/UI/form-dynatable.html
index 537316fa..cac3d287 100644
--- a/UI/form-dynatable.html
+++ b/UI/form-dynatable.html
@@ -15,6 +15,7 @@
<?lsmb PROCESS elements.html ?>
<body>
+<form method="post" action="<?lsmb form.script ?>">
<table width="100%">
<tr>
@@ -83,7 +84,6 @@
<br />
-<form method="post" action="<?lsmb form.script ?>">
<?lsmb FOREACH hidden IN hiddens.keys;
PROCESS input element_data={
type => 'hidden',
diff --git a/sql/modules/Payment.sql b/sql/modules/Payment.sql
index 56eb0e9d..48c3b7f3 100644
--- a/sql/modules/Payment.sql
+++ b/sql/modules/Payment.sql
@@ -312,6 +312,8 @@ DECLARE
t_voucher_id int;
t_trans_id int;
t_amount numeric;
+ t_ar_ap_id int;
+ t_cash_id int;
BEGIN
IF in_batch_id IS NULL THEN
-- t_voucher_id := NULL;
@@ -322,21 +324,21 @@ BEGIN
t_voucher_id := currval('voucher_id_seq');
END IF;
+
+ select id into t_ar_ap_id from chart where accno = in_ar_ap_accno;
+ select id into t_cash_id from chart where accno = in_cash_accno;
+
FOR out_count IN
- array_lower(in_transactions, 1) ..
- array_upper(in_transactions, 1)
+ 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)
+ case when in_account_class = 1 THEN t_cash_id
+ WHEN in_account_class = 2 THEN t_ar_ap_id
ELSE -1 END,
in_transactions[out_count][2],
@@ -346,12 +348,8 @@ BEGIN
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)
+ case when in_account_class = 1 THEN t_ar_ap_id
+ WHEN in_account_class = 2 THEN t_cash_id
ELSE -1 END,
in_transactions[out_count][2]* -1,
diff --git a/sql/modules/Voucher.sql b/sql/modules/Voucher.sql
index e27b199c..7abc65e7 100644
--- a/sql/modules/Voucher.sql
+++ b/sql/modules/Voucher.sql
@@ -11,7 +11,7 @@ END;
$$ language plpgsql;
-CREATE OR REPLACE FUNCTION batch_post (in_batch text, in_login varchar, in_entered date,
+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
$$
@@ -53,32 +53,43 @@ 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.paid, a.transdate, 'Payable'
+ a.amount, a.transdate, 'Payable'
FROM voucher v
JOIN ap a ON (v.trans_id = a.id)
JOIN entity e ON (a.entity_id = e.id)
WHERE v.batch_id = in_batch_id
AND v.batch_class = (select id from batch_class
- WHERE class = 'payable')
+ WHERE class = 'ap')
UNION
SELECT v.id, a.invnumber, e.name, v.batch_id, v.trans_id,
- a.amount - a.paid, a.transdate, 'Receivable'
+ a.amount, a.transdate, 'Receivable'
FROM voucher v
JOIN ar a ON (v.trans_id = a.id)
JOIN entity e ON (a.entity_id = e.id)
WHERE v.batch_id = in_batch_id
AND v.batch_class = (select id from batch_class
- WHERE class = 'receivable')
+ 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,
- a.amount, a.transdate, bc.class
+ CASE WHEN bc.class LIKE 'payment%' THEN a.amount * -1
+ ELSE 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'
+ 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)
WHERE v.batch_id = in_batch_id
AND a.voucher_id = v.id
- AND bc.class like 'payment%'
- OR bc.class like 'receipt%'
+ AND (bc.class like 'payment%' AND c.link = 'AP')
+ OR (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'
@@ -200,15 +211,15 @@ BEGIN
AND batch_class = 5);
UPDATE acc_trans SET approved = true
- WHERE id IN (select trans_id FROM voucher
+ WHERE trans_id IN (select trans_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 login = SESSION_USER)
- WHERE batch_id = in_batch_id;
+ WHERE username = SESSION_USER)
+ WHERE id = in_batch_id;
RETURN now()::date;
END;
@@ -263,18 +274,18 @@ BEGIN
(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
+ AND (voucher_id IS NULL OR voucher_id NOT IN
(select id from voucher
- WHERE batch_id = in_batch_id))
+ 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
+ AND (voucher_id IS NULL OR voucher_id NOT IN
(select id from voucher
- WHERE batch_id = in_batch_id))
+ 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));