From 32ec9551c2c00aa85c4479451e5d4d55d09679e0 Mon Sep 17 00:00:00 2001 From: einhverfr Date: Mon, 17 Dec 2007 23:22:43 +0000 Subject: Batch enhancements git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@1980 4979c152-3d1c-0410-bac9-87ea11338e46 --- LedgerSMB/Batch.pm | 18 ++++++++++- LedgerSMB/DBObject.pm | 32 +++++++++++++++++++ LedgerSMB/DBObject/Payment.pm | 22 +++++++------ UI/batch/filter.html | 73 +++++++++++++++++++++++++++++++++++++++++++ sql/modules/Payment.sql | 43 ++++++++++++++++--------- sql/modules/Voucher.sql | 70 ++++++++++++++++++++++++++++++++++++----- 6 files changed, 226 insertions(+), 32 deletions(-) create mode 100644 UI/batch/filter.html diff --git a/LedgerSMB/Batch.pm b/LedgerSMB/Batch.pm index 5c948f79..28be86f7 100644 --- a/LedgerSMB/Batch.pm +++ b/LedgerSMB/Batch.pm @@ -6,10 +6,26 @@ use base qw(LedgerSMB::DBObject); sub create { $self = shift @_; my ($ref) = $self->exec_method(funcname => 'batch_create'); - print STDERR "$ref, $ref->{batch_create}, " . join (':', keys %$ref); $self->{id} = $ref->{batch_create}; $self->{dbh}->commit; return $ref->{id}; } +sub get_search_criteria { + $self = shift @_; + @{$self->{batch_classes}} = $self->exec_method( + funcname => 'batch_list_classes' + ); + + @{$self->{batch_users}} = $self->exec_method( + funcname => 'batch_get_users' + ); +} + +sub get_search_results { + my ($self) = @_; + @{$self->{search_results}} = $self->exec_method(funcname => 'batch_search'); + return @{$self->{search_results}}; +} + 1; diff --git a/LedgerSMB/DBObject.pm b/LedgerSMB/DBObject.pm index 2a8d976b..0691489a 100644 --- a/LedgerSMB/DBObject.pm +++ b/LedgerSMB/DBObject.pm @@ -29,6 +29,11 @@ arguments. =item __validate__ is called on every new() invocation. It is blank in this module but can be overridden in decendant modules. +=item _db_array_scalars(@elements) creates a db array from scalars. + +=item _db_array_literal(@elements) creates a multiple dimension db array from + preparsed db arrays or other data which does not need to be escaped. + =back =head1 Copyright (C) 2007, The LedgerSMB core team. @@ -274,4 +279,31 @@ sub _parse_array { return @return_array; } +sub _db_array_scalars { + my $self = shift @_; + my @args = @_; + for my $arg (@args){ + $arg =~ s/(["{},])/\\$1/g; + if ($arg =~ /(\s|\\)/){ + $arg = qq|"$arg"|; + } + } + return _db_array_literal(@args); +} + +sub _db_array_literal { + my $self = shift @_; + my @args = @_; + my $return_string = '{}'; + for my $arg (@args){ + if ($return_string eq '{}'){ + $return_string = "{$arg}"; + } + else { + $return_string =~ s/\}$/,$arg\}/ + } + } + return $return_string; +} + 1; diff --git a/LedgerSMB/DBObject/Payment.pm b/LedgerSMB/DBObject/Payment.pm index 2d4ced79..f4382a28 100644 --- a/LedgerSMB/DBObject/Payment.pm +++ b/LedgerSMB/DBObject/Payment.pm @@ -382,7 +382,7 @@ sub get_payment_detail_data { $inv->{invoices} = []; @{$inv->{invoices}} = $self->_parse_array($tmp_invoices); } - # $self->{dbh}->commit; # Commit locks + $self->{dbh}->commit; # Commit locks } sub post_bulk { @@ -398,6 +398,10 @@ sub post_bulk { funcname => 'job__create' ); $self->{job_id} = $job_ref->{job__create}; + + ($self->{job}) = $self->exec_method( + funcname => 'job__status' + ); } $self->{payment_date} = $self->{datepaid}; for my $contact_row (1 .. $self->{contact_count}){ @@ -406,29 +410,29 @@ sub post_bulk { my $invoice_array = "{}"; # Pg Array for my $invoice_row (1 .. $self->{"invoice_count_$contact_id"}){ my $invoice_id = $self->{"invoice_${contact_id}_${invoice_row}"}; - print STDERR "invoice_${contact_id}_${invoice_row}: $invoice_id\n"; my $pay_amount = ($self->{"paid_$contact_id"} eq 'all' ) ? $self->{"net_$invoice_id"} : $self->{"payment_$invoice_id"}; - if (!$pay_amount){ - $pay_amount = 0; - } + next if ! $pay_amount; + $pay_amount = $pay_amount * 1; my $invoice_subarray = "{$invoice_id,$pay_amount}"; + if ($invoice_subarray !~ /^\{\d+\,\-?\d*\.?\d+\}$/){ + $self->error("Invalid subarray: $invoice_subarray"); + } + $invoice_subarray =~ s/[^0123456789{},.]//; if ($invoice_array eq '{}'){ # Omit comma $invoice_array = "{$invoice_subarray}"; } else { - $invoice_array =~ s/}$/,$invoice_subarray}/; + $invoice_array =~ s/\}$/,$invoice_subarray\}/; } } $self->{transactions} = $invoice_array; $self->{source} = $self->{"source_$contact_id"}; if ($queue_payments){ + $self->{batch_class} = 3; $self->exec_method( funcname => 'payment_bulk_queue' ); - ($self->{job}) = $self->exec_method( - funcname => 'job__status' - ); } else { $self->exec_method(funcname => 'payment_bulk_post'); } diff --git a/UI/batch/filter.html b/UI/batch/filter.html new file mode 100644 index 00000000..3cb69089 --- /dev/null +++ b/UI/batch/filter.html @@ -0,0 +1,73 @@ + + +
+
+
+ + +
+
+
+
+ +
+
+ + +
+
+
+
+ + + 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); -- cgit v1.2.3