From b89e874e8258288d15c98ed3799a9fede6515fd5 Mon Sep 17 00:00:00 2001 From: Mitch Jackson Date: Tue, 11 Sep 2018 03:33:33 -0400 Subject: [PATCH] RT# 78547 Upcoming Auto-Bill Transaction Report --- FS/FS/cust_payby.pm | 25 +++ httemplate/search/future_autobill.html | 213 ++++++++++++++++---------- httemplate/search/report_future_autobill.html | 66 +++++--- 3 files changed, 205 insertions(+), 99 deletions(-) diff --git a/FS/FS/cust_payby.pm b/FS/FS/cust_payby.pm index c497059fa..301eb6106 100644 --- a/FS/FS/cust_payby.pm +++ b/FS/FS/cust_payby.pm @@ -914,8 +914,33 @@ sub search_sql { =back +=item count_autobill_cards + +Returns the number of unexpired cards configured for autobill + +=cut + +sub count_autobill_cards { + shift->count(" + weight > 0 + AND payby IN ('CARD','DCRD') + AND paydate > '".DateTime->now->ymd."' + "); +} + +=item count_autobill_checks + +Returns the number of check accounts configured for autobill + =cut +sub count_autobill_checks { + shift->count(" + weight > 0 + AND payby IN ('CHEK','DCHEK') + "); +} + sub _upgrade_data { my $class = shift; diff --git a/httemplate/search/future_autobill.html b/httemplate/search/future_autobill.html index 711a25f82..d6438d9dc 100644 --- a/httemplate/search/future_autobill.html +++ b/httemplate/search/future_autobill.html @@ -2,20 +2,18 @@ Report listing upcoming auto-bill transactions -Spec requested the ability to run this report with a longer date range, -and see which charges will process on which day. Checkbox multiple_billing_dates -enables this functionality. +For every customer with a valid auto-bill payment method, +report runs bill_and_collect() for each customer, for each +day, from today through the report target date. After +recording the results, all operations are rolled back. -Performance: -This is a dynamically generated report. The time this report takes to run -will depends on the number of customers. Installations with a high number -of auto-bill customers may find themselves unable to run this report -because of browser timeout. Report could be implemented as a queued job if -necessary, to solve the performance problem. +This report relies on the ability to safely run bill_and_collect(), +with all exports and messaging disabled, and then to roll back the +results. <& elements/grid-report.html, - title => 'Upcoming auto-bill transactions', + title => $report_title, rows => \@rows, cells => \@cells, table_width => "", @@ -32,11 +30,12 @@ necessary, to solve the performance problem. &> <%init> + use FS::UID qw( dbh myconnect ); -use FS::UID qw( dbh myconnect ); + die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); -die "access denied" - unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); + my $DEBUG = $cgi->param('DEBUG') || 0; my $target_dt; my @target_dates; @@ -45,14 +44,13 @@ die "access denied" my %noon = ( hour => 12, minute => 0, - second => 0 + second => 0, ); - my $now_dt = DateTime->now; $now_dt = DateTime->new( - month => $now_dt->month, - day => $now_dt->day, - year => $now_dt->year, + month => $now_dt->month, + day => $now_dt->day, + year => $now_dt->year, %noon, ); @@ -60,9 +58,9 @@ die "access denied" if ($cgi->param('target_date')) { my ($mm, $dd, $yy) = split /[\-\/]/,$cgi->param('target_date'); $target_dt = DateTime->new( - month => $mm, - day => $dd, - year => $yy, + month => $mm, + day => $dd, + year => $yy, %noon, ) if $mm && $dd & $yy; @@ -72,18 +70,12 @@ die "access denied" # without a target date, default to tomorrow unless ($target_dt) { - $target_dt = DateTime->from_epoch( epoch => time() + 86400) ; - $target_dt = DateTime->new( - month => $target_dt->month, - day => $target_dt->day, - year => $target_dt->year, - %noon - ); + $target_dt = $now_dt->clone->add( days => 1 ); } - # If multiple_billing_dates checkbox selected, create a range of dates - # from today until the given report date. Otherwise, use target date only. - if ($cgi->param('multiple_billing_dates')) { + # Create a range of dates from today until the given report date + # (leaving the probably useless 'quick-report' mode, but disabled) + if ( 1 || $cgi->param('multiple_billing_dates')) { my $walking_dt = DateTime->from_epoch(epoch => $now_dt->epoch); until ($walking_dt->epoch > $target_dt->epoch) { push @target_dates, $walking_dt->epoch; @@ -93,17 +85,6 @@ die "access denied" push @target_dates, $target_dt->epoch; } - # List all customers with an auto-bill method - # - # my %cust_payby = map {$_->custnum => $_} qsearch({ - # table => 'cust_payby', - # hashref => { - # weight => { op => '>', value => '0' }, - # paydate => { op => '>', value => $target_dt->ymd }, - # }, - # order_by => " ORDER BY weight DESC ", - # }); - # List all customers with an auto-bill method that's not expired my %cust_payby = map {$_->custnum => $_} qsearch({ table => 'cust_payby', @@ -111,62 +92,121 @@ die "access denied" weight => { op => '>', value => '0' }, }, order_by => " ORDER BY weight DESC ", - extra_sql => " AND ( payby = 'CHEK' OR ( paydate > '".$target_dt->ymd."')) ", + extra_sql => " + AND ( + payby IN ('CHEK','DCHK') + OR ( paydate > '".$target_dt->ymd."') + ) + ", }); + my $fakebill_time = time(); my %abreport; my @rows; local $@; local $SIG{__DIE__}; - my $temp_dbh = myconnect(); - eval { # Creating sandbox dbh where all connections are to be rolled back - local $FS::UID::dbh = $temp_dbh; + + eval { # Sandbox + + # Create new database handle and supress all COMMIT statements + my $oldAutoCommit = $FS::UID::AutoCommit; local $FS::UID::AutoCommit = 0; + local $FS::UID::ForceObeyAutoCommit = 1; + + # Suppress notices generated by billing events + local $FS::Misc::DISABLE_ALL_NOTICES = 1; - # Generate report data into @rows + # Bypass payment processing, recording a fake payment + local $FS::cust_main::Billing_Realtime::BOP_TESTING = 1; + local $FS::cust_main::Billing_Realtime::BOP_TESTING_SUCCESS = 1; + + warn sprintf "Report involves %s customers", scalar keys %cust_payby + if $DEBUG; + + # Run bill_and_collect(), for each customer with an autobill payment method, + # for each day represented in the report for my $custnum (keys %cust_payby) { my $cust_main = qsearchs('cust_main', {custnum => $custnum}); + warn "-- Processing custnum $custnum\n" + if $DEBUG; + # walk forward through billing dates for my $query_epoch (@target_dates) { + $FS::cust_main::Billing_Realtime::BOP_TESTING_TIMESTAMP = $query_epoch; my $return_bill = []; - eval { # Don't let an error on one customer crash the report - my $error = $cust_main->bill( - time => $query_epoch, - return_bill => $return_bill, - no_usage_reset => 1, - ); - die "$error (simulating future billing)" if $error; - }; - warn ("$@: (future_autobill custnum:$custnum)"); - - if (@{$return_bill}) { - my $inv = $return_bill->[0]; - push @rows,{ - name => $cust_main->name, - _date => $inv->_date, - cells => [ - { class => 'gridreport', value => $custnum }, - { class => 'gridreport', - value => ''.$cust_main->name.'', - bypass_filter => 1, - }, - { class => 'gridreport', value => $inv->charged, format => 'money' }, - { class => 'gridreport', value => DateTime->from_epoch(epoch=>$inv->_date)->ymd }, - { class => 'gridreport', value => ($cust_payby{$custnum}->payby || $cust_payby{$custnum}->paytype) }, - { class => 'gridreport', value => $cust_payby{$custnum}->paymask }, - ] - }; - } + warn "---- Set billtime to ". + DateTime->from_epoch( epoch => $query_epoch )."\n" + if $DEBUG; + + my $error = $cust_main->bill_and_collect( + time => $query_epoch, + return_bill => $return_bill, + no_usage_reset => 1, + fake => 1, + ); + warn "!!! $error (simulating future billing)\n" if $error; } - $temp_dbh->rollback; - } # /foreach $custnum + # Generate report rows from recorded payments in cust_pay + for my $cust_pay ( + qsearch( cust_pay => { + custnum => $custnum, + _date => { op => '>=', value => $fakebill_time }, + }) + ) { + push @rows,{ + name => $cust_main->name, + _date => $cust_pay->_date, + cells => [ + + # Customer number + { class => 'gridreport', value => $custnum }, + + # Customer name / customer link + { class => 'gridreport', + value => qq{} . encode_entities( $cust_main->name ). '', + bypass_filter => 1 + }, + + # Amount + { class => 'gridreport', + value => $cust_pay->paid, + format => 'money' + }, + + # Transaction Date + { class => 'gridreport', + value => DateTime->from_epoch( epoch => $cust_pay->_date )->ymd + }, + + # Payment Method + { class => 'gridreport', + value => encode_entities( $cust_pay->paycardtype || $cust_pay->payby ), + }, + + # Masked Payment Instrument + { class => 'gridreport', + value => encode_entities( $cust_pay->paymask ), + }, + ] + }; + + } # /foreach payment + + # Roll back database at the end of each customer + # Makes the report slighly slower, but ensures only one customer row + # locked at a time + + warn "-- custnum $custnum -- rollback()\n"; + dbh->rollback if $oldAutoCommit; + + } # /foreach $custnum }; # /eval - warn("$@") if $@; + warn("future_autobill.html report generated error $@") if $@; # Sort output by date, and format for output to grid-report.html my @cells = [ @@ -186,4 +226,21 @@ die "access denied" # grid-report.html requires a parallel @rows parameter to accompany @cells @rows = map { {class => 'gridreport'} } 1..scalar(@cells); + # Dynamic report title + my $title_types = ''; + my $card_count = FS::cust_payby->count_autobill_cards; + my $check_count = FS::cust_payby->count_autobill_checks; + if ( $card_count && $check_count ) { + $title_types = 'Card and Check'; + } elsif ( $card_count ) { + $title_types = 'Card'; + } elsif ( $check_count ) { + $title_types = 'Check'; + } + + my $report_title = sprintf( + 'Upcoming Auto Bill %s Transactions', + $title_types, + ); + diff --git a/httemplate/search/report_future_autobill.html b/httemplate/search/report_future_autobill.html index 1a0c9f48a..ff2f85715 100644 --- a/httemplate/search/report_future_autobill.html +++ b/httemplate/search/report_future_autobill.html @@ -3,40 +3,64 @@ Display date selector for the future_autobill.html report -<% include('/elements/header.html', 'Future Auto-Bill Transactions' ) %> +<% include('/elements/header.html', $report_title ) %> -
- -<& /elements/tr-input-date-field.html, - { - name => 'target_date', - value => $target_date, - label => emt('Target billing date').': ', - required => 1 - } -&> +% if ( FS::TaxEngine->new->info->{batch} ) { -<& /elements/tr-checkbox.html, - 'label' => emt('Multiple billing dates (slow)').': ', - 'field' => 'multiple_billing_dates', - 'value' => '1', -&> +
+ NOTE: This report is disabled due to tax engine configuration +
-
+% } else { -
- + + + <& /elements/tr-input-date-field.html, + { + name => 'target_date', + value => $target_date, + label => emt('Target billing date').': ', + required => 1 + } + &> - +
+ +
+ + + + + +% } <% include('/elements/footer.html') %> <%init> +use FS::cust_payby; die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); -my $target_date = DateTime->from_epoch(epoch=>(time()+86400))->mdy('/'); +my $target_date = DateTime->now->add(days => 1)->mdy('/'); + +# Dynamic report title +my $title_types = ''; +my $card_count = FS::cust_payby->count_autobill_cards; +my $check_count = FS::cust_payby->count_autobill_checks; +if ( $card_count && $check_count ) { + $title_types = 'Card and Check'; +} elsif ( $card_count ) { + $title_types = 'Card'; +} elsif ( $check_count ) { + $title_types = 'Check'; +} + +my $report_title = sprintf( + 'Upcoming Auto Bill %s Transactions', + $title_types, +); + -- 2.11.0