<%doc> Report listing upcoming auto-bill transactions 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. 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. This report takes time. If 200 customers have automatic payment methods, and requester is looking one week ahead, there will be 1,400 billing and payment cycles simulated

<% $report_subtitle %>

<& elements/grid-report.html, title => $report_title, rows => \@rows, cells => \@cells, table_width => "", table_class => 'gridreport', head => ' ', suppress_header => $job ? 1 : 0, suppress_footer => $job ? 1 : 0, &> % if ( %pmt_type_subtotal ) { % for my $pmt_type ( sort keys %pmt_type_subtotal ) { % } % if ( keys %pmt_type_subtotal > 1 ) { % $pmt_type_subtotal{Total} += $_ for values %pmt_type_subtotal;
Summary
<% sprintf '$%.2f', $pmt_type_subtotal{ $pmt_type } %> <% $pmt_type |h %>
<% sprintf( '$%.2f', $pmt_type_subtotal{Total} ) %> Total
% } % } <%init> use DateTime; use FS::Misc::Savepoint; use FS::Report::Queued::FutureAutobill; use FS::UID qw( dbh ); die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); my $job = $FS::Report::Queued::FutureAutobill::job; $job->update_statustext('0,Finding customers') if $job; my $DEBUG = $cgi->param('DEBUG') || 0; my $agentnum = $cgi->param('agentnum') if $cgi->param('agentnum') =~ /^\d+/; 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')) { # DateTime::Format::DateParse would be better my ($mm, $dd, $yy) = split /[\-\/]/,$cgi->param('target_date'); ( $yy, $mm, $dd ) = ( $mm, $dd, $yy ) if $mm > 1900; $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 unless $target_dt && $now_dt && $now_dt <= $target_dt; } # without a target date, default to tomorrow unless ($target_dt) { $target_dt = $now_dt->clone->add( days => 1 ); } my $report_title = FS::cust_payby->future_autobill_report_title; my $report_subtitle = sprintf( '(%s through %s)', $now_dt->mdy('/'), $target_dt->mdy('/'), ); # 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; $walking_dt->add(days => 1); } } else { push @target_dates, $target_dt->epoch; } # List all customers with an auto-bill method that's not expired my %cust_payby = map {$_->custnum => $_} qsearch({ table => 'cust_payby', addl_from => 'JOIN cust_main USING (custnum)', hashref => { weight => { op => '>', value => '0' }}, order_by => " ORDER BY weight DESC ", extra_sql => "AND ( cust_payby.payby IN ('CHEK','DCHK','DCHEK') OR ( cust_payby.paydate > '".$target_dt->ymd."') ) AND " . $FS::CurrentUser::CurrentUser->agentnums_sql . ($agentnum ? "AND cust_main.agentnum = $agentnum" : ''), }); my $completion_target = scalar(keys %cust_payby) * scalar( @target_dates ); my $completion_progress = 0; my $fakebill_time = time(); my %abreport; my @rows; my %pmt_type_subtotal; local $@; local $SIG{__DIE__}; eval { # Sandbox # Supress 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; # 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; my $savepoint_label = 'future_autobill'; savepoint_create( $savepoint_label ); 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 = []; 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; my $statustext = sprintf( '%s,Simulating upcoming invoices and payments', int( ( ++$completion_progress / $completion_target ) * 100 ) ); $job->update_statustext( $statustext ) if $job; warn "[ $completion_progress / $completion_target ] $statustext\n" if $DEBUG; } # 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 ), }, ] }; $pmt_type_subtotal{ $cust_pay->paycardtype || $cust_pay-> payby } += $cust_pay->paid; } # /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" if $DEBUG; savepoint_rollback( $savepoint_label ); dbh->rollback if $oldAutoCommit; } # /foreach $custnum }; # /eval warn("future_autobill.html report generated error $@") 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);