RT# 78547 Upcoming Auto-Bill Transaction Report

2 files changed, 180 insertions, 99 deletions

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.
 
 </%doc>
 <& 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 => '<a href="/view/cust_main.cgi?"'.$custnum.'">'.$cust_main->name.'</a>',
-                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{<a href="${fsurl}view/cust_main.cgi?${custnum}">} . encode_entities( $cust_main->name ). '</a>',
+              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,
+  );
+
 </%init>
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
 
 </%doc>
-<% include('/elements/header.html', 'Future Auto-Bill Transactions' ) %>
+<% include('/elements/header.html', $report_title ) %>
 
 
-<FORM ACTION="future_autobill.html" METHOD="GET">
-<TABLE>
-<& /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',
-&>
+  <div style="font-color: red">
+  NOTE: This report is disabled due to tax engine configuration
+  </div>
 
-</TABLE>
+% } else {
 
-<BR>
-<INPUT TYPE="submit" VALUE="<% mt('Get Report') |h %>">
+  <FORM ACTION="future_autobill.html" METHOD="GET">
+  <TABLE>
+  <& /elements/tr-input-date-field.html,
+    {
+      name     => 'target_date',
+      value    => $target_date,
+      label    => emt('Target billing date').': ',
+      required => 1
+    }
+  &>
 
-</FORM>
+  </TABLE>
+
+  <BR>
+
+  <INPUT TYPE="submit" VALUE="<% mt('Get Report') |h %>">
+
+  </FORM>
+
+% }
 
 <% 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,
+);
 
 </%init>
+