rt# 78547 Implement report listing future auto-bill charges

3 files changed, 238 insertions, 1 deletions

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 <A HREF="<% "$myself;_type=xls" %>">Excel spreadsheet</A><BR>
 .shaded { background-color: #c8c8c8; }
 .totalshaded { background-color: #bfc094; }
 </style>
-<table class="report" width="100%" cellspacing=0>
+<table class="<% $table_class %>" width="<% $table_width %>" cellspacing=0>
 % foreach my $rowinfo (@rows) {
   <tr<% $rowinfo->{class} ? ' class="'.$rowinfo->{class}.'"' : ''%>>
 %   my $thisrow = shift @cells;
@@ -172,7 +172,11 @@ as <A HREF="<% "$myself;_type=xls" %>">Excel spreadsheet</A><BR>
 %     $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} %></<%$td%>>
+% } else {
       <<%$td%><%$style%>><% $cell->{value} |h %></<%$td%>>
+% }
 %   }
   </tr>
 % }
@@ -186,4 +190,6 @@ $title
 @cells
 $head => ''
 $foot => ''
+$table_width => "100%"
+$table_class => "report"
 </%args>
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.
+
+</%doc>
+<& elements/grid-report.html,
+  title => 'Upcoming auto-bill transactions',
+  rows => \@rows,
+  cells => \@cells,
+  table_width => "",
+  table_class => 'gridreport',
+  head => '
+    <style type="text/css">
+      table.gridreport { margin: .5em; border: solid 1px #aaa; }
+      th.gridreport { background-color: #ccc; }
+      tr.gridreport:nth-child(even) { background-color: #eee; }
+      tr.gridreport:nth-child(odd)  { background-color: #fff; }
+      td.gridreport { margin: 0 .2em; padding: 0 .4em; }
+    </style>
+  ',
+&>
+
+<%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 => '<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 },
+            ]
+          };
+        }
+
+      }
+      $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);
+
+</%init>
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
+
+</%doc>
+<% include('/elements/header.html', 'Future Auto-Bill Transactions' ) %>
+
+
+<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
+  }
+&>
+
+<& /elements/tr-checkbox.html,
+     'label' => emt('Multiple billing dates (slow)').': ',
+     'field' => 'multiple_billing_dates',
+     'value' => '1',
+&>
+
+</TABLE>
+
+<BR>
+<INPUT TYPE="submit" VALUE="<% mt('Get Report') |h %>">
+
+</FORM>
+
+<% 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('/');
+
+</%init>