From c6251a1dcd226056780bf28f8ca79f078f8c78bd Mon Sep 17 00:00:00 2001 From: Mitch Jackson Date: Fri, 19 Jan 2018 01:34:48 -0600 Subject: [PATCH] rt# 78547 Implement report listing future auto-bill charges --- httemplate/elements/menu.html | 2 + httemplate/search/elements/grid-report.html | 8 +- httemplate/search/future_autobill.html | 189 ++++++++++++++++++++++++++ httemplate/search/report_future_autobill.html | 42 ++++++ 4 files changed, 240 insertions(+), 1 deletion(-) create mode 100644 httemplate/search/future_autobill.html create mode 100644 httemplate/search/report_future_autobill.html diff --git a/httemplate/elements/menu.html b/httemplate/elements/menu.html index 66e419af0..02a506772 100644 --- a/httemplate/elements/menu.html +++ b/httemplate/elements/menu.html @@ -418,6 +418,8 @@ if( $curuser->access_right('Financial reports') ) { $report_financial{'Customer Accounting Summary'} = [ $fsurl.'search/report_customer_accounting_summary.html', 'Customer accounting summary report' ]; + $report_financial{'Upcoming Auto-Bill Transactions'} = [ $fsurl.'search/report_future_autobill.html', 'Upcoming auto-bill transactions' ]; + } elsif($curuser->access_right('Receivables report')) { $report_financial{'A/R Aging'} = [ $fsurl.'search/report_receivables.html', 'Accounts Receivable Aging report' ]; diff --git a/httemplate/search/elements/grid-report.html b/httemplate/search/elements/grid-report.html index 98e81785f..b1e543012 100644 --- a/httemplate/search/elements/grid-report.html +++ b/httemplate/search/elements/grid-report.html @@ -161,7 +161,7 @@ as ">Excel spreadsheet
.shaded { background-color: #c8c8c8; } .totalshaded { background-color: #bfc094; } - +
% foreach my $rowinfo (@rows) { {class} ? ' class="'.$rowinfo->{class}.'"' : ''%>> % my $thisrow = shift @cells; @@ -172,7 +172,11 @@ as ">Excel spreadsheet
% $style .= " rowspan=".$cell->{rowspan} if $cell->{rowspan} > 1; % $style .= " colspan=".$cell->{colspan} if $cell->{colspan} > 1; % $style .= ' class="' . $cell->{class} . '"' if $cell->{class}; +% if ($cell->{bypass_filter}) { + <<%$td%><%$style%>><% $cell->{value} %>> +% } else { <<%$td%><%$style%>><% $cell->{value} |h %>> +% } % } % } @@ -186,4 +190,6 @@ $title @cells $head => '' $foot => '' +$table_width => "100%" +$table_class => "report" diff --git a/httemplate/search/future_autobill.html b/httemplate/search/future_autobill.html new file mode 100644 index 000000000..711a25f82 --- /dev/null +++ b/httemplate/search/future_autobill.html @@ -0,0 +1,189 @@ +<%doc> + +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. + +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. + + +<& elements/grid-report.html, + title => 'Upcoming auto-bill transactions', + rows => \@rows, + cells => \@cells, + table_width => "", + table_class => 'gridreport', + head => ' + + ', +&> + +<%init> + +use FS::UID qw( dbh myconnect ); + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); + + my $target_dt; + my @target_dates; + + # Work with all date/time operations @ 12 noon + my %noon = ( + hour => 12, + minute => 0, + second => 0 + ); + + my $now_dt = DateTime->now; + $now_dt = DateTime->new( + month => $now_dt->month, + day => $now_dt->day, + year => $now_dt->year, + %noon, + ); + + # Get target date from form + if ($cgi->param('target_date')) { + my ($mm, $dd, $yy) = split /[\-\/]/,$cgi->param('target_date'); + $target_dt = DateTime->new( + month => $mm, + day => $dd, + year => $yy, + %noon, + ) if $mm && $dd & $yy; + + # Catch a date from the past: time only travels in one direction + $target_dt = undef if $target_dt->epoch < $now_dt->epoch; + } + + # 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 + ); + } + + # 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')) { + my $walking_dt = DateTime->from_epoch(epoch => $now_dt->epoch); + until ($walking_dt->epoch > $target_dt->epoch) { + push @target_dates, $walking_dt->epoch; + $walking_dt->add(days => 1); + } + } else { + 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', + hashref => { + weight => { op => '>', value => '0' }, + }, + order_by => " ORDER BY weight DESC ", + extra_sql => " AND ( payby = 'CHEK' OR ( paydate > '".$target_dt->ymd."')) ", + }); + + 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; + local $FS::UID::AutoCommit = 0; + + # Generate report data into @rows + for my $custnum (keys %cust_payby) { + my $cust_main = qsearchs('cust_main', {custnum => $custnum}); + + # walk forward through billing dates + for my $query_epoch (@target_dates) { + 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 }, + ] + }; + } + + } + $temp_dbh->rollback; + } # /foreach $custnum + + }; # /eval + warn("$@") if $@; + + # Sort output by date, and format for output to grid-report.html + my @cells = [ + # header row + { class => 'gridreport', value => '#', header => 1 }, + { class => 'gridreport', value => 'Name', header => 1 }, + { class => 'gridreport', value => 'Amount', header => 1 }, + { class => 'gridreport', value => 'Date', header => 1 }, + { class => 'gridreport', value => 'Type', header => 1 }, + { class => 'gridreport', value => 'Account', header => 1 }, + ]; + push @cells, + map { $_->{cells} } + sort { $a->{_date} <=> $b->{_date} || $a->{name} cmp $b->{name} } + @rows; + + # grid-report.html requires a parallel @rows parameter to accompany @cells + @rows = map { {class => 'gridreport'} } 1..scalar(@cells); + + diff --git a/httemplate/search/report_future_autobill.html b/httemplate/search/report_future_autobill.html new file mode 100644 index 000000000..1a0c9f48a --- /dev/null +++ b/httemplate/search/report_future_autobill.html @@ -0,0 +1,42 @@ +<%doc> + +Display date selector for the future_autobill.html report + + +<% include('/elements/header.html', 'Future Auto-Bill Transactions' ) %> + + +
+
+<& /elements/tr-input-date-field.html, + { + name => 'target_date', + value => $target_date, + label => emt('Target billing date').': ', + required => 1 + } +&> + +<& /elements/tr-checkbox.html, + 'label' => emt('Multiple billing dates (slow)').': ', + 'field' => 'multiple_billing_dates', + 'value' => '1', +&> + +
+ +
+ + + + +<% include('/elements/footer.html') %> + +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); + +my $target_date = DateTime->from_epoch(epoch=>(time()+86400))->mdy('/'); + + -- 2.11.0