optimize billing for customers with lots of existing packages and invoices, RT#30238

5 files changed, 63 insertions, 35 deletions

diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm
index 1b82e0ec7..64e3f1caf 100644
--- a/FS/FS/Schema.pm
+++ b/FS/FS/Schema.pm
@@ -2587,8 +2587,9 @@ sub tables_hashref {
       'index'        => [ ['custnum'], ['pkgpart'], ['pkgbatch'],
                           ['locationnum'], ['usernum'], ['agent_pkgid'],
                           ['order_date'], [ 'start_date' ], ['setup'], ['bill'],
-                          ['last_bill'], ['susp'], ['adjourn'], ['cancel'],
-                          ['expire'], ['contract_end'], ['change_date'],
+                          ['last_bill'], ['susp'], ['adjourn'], ['resume'],
+                          ['cancel'], ['expire'], ['contract_end'],
+                          ['change_date'],
                           ['no_auto'],
                           #['contactnum'],
                           ['salesnum'],
diff --git a/FS/FS/cust_main.pm b/FS/FS/cust_main.pm
index 7d3185a22..cbf95f549 100644
--- a/FS/FS/cust_main.pm
+++ b/FS/FS/cust_main.pm
@@ -3777,9 +3777,17 @@ Returns all the payments (see L<FS::cust_pay>) for this customer.
 
 sub cust_pay {
   my $self = shift;
-  return $self->num_cust_pay unless wantarray;
-  sort { $a->_date <=> $b->_date }
-    qsearch( 'cust_pay', { 'custnum' => $self->custnum } )
+  my $opt = ref($_[0]) ? shift : { @_ };
+
+  return $self->num_cust_pay unless wantarray || keys %$opt;
+
+  $opt->{'table'} = 'cust_pay';
+  $opt->{'hashref'}{'custnum'} = $self->custnum;
+
+  map { $_ } #behavior of sort undefined in scalar context
+    sort { $a->_date <=> $b->_date }
+      qsearch($opt);
+
 }
 
 =item num_cust_pay
@@ -3797,6 +3805,22 @@ sub num_cust_pay {
   $sth->fetchrow_arrayref->[0];
 }
 
+=item unapplied_cust_pay
+
+Returns all the unapplied payments (see L<FS::cust_pay>) for this customer.
+
+=cut
+
+sub unapplied_cust_pay {
+  my $self = shift;
+
+  $self->cust_pay(
+    'extra_sql' => ' AND '. FS::cust_pay->unapplied_sql. ' > 0',
+    #@_
+  );
+
+}
+
 =item cust_pay_pkgnum
 
 Returns all the payments (see L<FS::cust_pay>) for this customer's specific
diff --git a/FS/FS/cust_main/Billing.pm b/FS/FS/cust_main/Billing.pm
index 4f9108078..dfc8c8607 100644
--- a/FS/FS/cust_main/Billing.pm
+++ b/FS/FS/cust_main/Billing.pm
@@ -730,14 +730,16 @@ sub bill {
 
     my $charged = sprintf('%.2f', ${ $total_setup{$pass} } + ${ $total_recur{$pass} } );
 
-    my @cust_bill = $self->cust_bill;
     my $balance = $self->balance;
-    my $previous_bill = $cust_bill[-1] if @cust_bill;
-    my $previous_balance = 0;
-    if ( $previous_bill ) {
-      $previous_balance = $previous_bill->billing_balance 
-                        + $previous_bill->charged;
-    }
+
+    my $previous_bill = qsearchs({ 'table'     => 'cust_bill',
+                                   'hashref'   => { custnum=>$self->custnum },
+                                   'extra_sql' => 'ORDER BY _date DESC LIMIT 1',
+                                });
+    my $previous_balance =
+      $previous_bill
+        ? ( $previous_bill->billing_balance + $previous_bill->charged )
+        : 0;
 
     warn "creating the new invoice\n" if $DEBUG;
     #create the new invoice
@@ -2426,13 +2428,9 @@ sub apply_payments {
 
   #return 0 unless
 
-  my @payments = sort { $b->_date <=> $a->_date }
-                 grep { $_->unapplied > 0 }
-                 $self->cust_pay;
+  my @payments = $self->unapplied_cust_pay;
 
-  my @invoices = sort { $a->_date <=> $b->_date}
-                 grep { $_->owed > 0 }
-                 $self->cust_bill;
+  my @invoices = $self->open_cust_bill;
 
   if ( $conf->exists('pkg-balances') ) {
     # limit @payments to those w/ a pkgnum grepped from $self
diff --git a/FS/FS/part_event/Condition/inactive_age.pm b/FS/FS/part_event/Condition/inactive_age.pm
index cbf4b9e0a..562e10175 100644
--- a/FS/FS/part_event/Condition/inactive_age.pm
+++ b/FS/FS/part_event/Condition/inactive_age.pm
@@ -57,20 +57,24 @@ sub condition_sql {
   # will evaluate to zero if there isn't one
   my @sql;
   for my $t (qw(cust_pay cust_credit cust_refund)) {
-    push @sql,
-      "NOT EXISTS( SELECT 1 FROM $t ".
-      "WHERE $t.custnum = cust_main.custnum AND $t._date >= $age".
-      ")";
+    push @sql, "
+      NOT EXISTS( SELECT 1 FROM $t
+                    WHERE $t.custnum = cust_main.custnum AND $t._date >= $age
+                    LIMIT 1
+                )
+    ";
   }
   #cust_bill
-  push @sql,
-    "NOT EXISTS( ".
-    "SELECT 1 FROM cust_bill JOIN cust_bill_pkg USING (invnum) ".
-    "JOIN cust_pkg USING (pkgnum) JOIN part_pkg USING (pkgpart) ".
-    "WHERE cust_bill.custnum = cust_main.custnum ".
-    "AND cust_bill._date >= $age ".
-    "AND COALESCE(part_pkg.classnum, -1) != $ignore_pkgclass ".
-    ")";
+  push @sql, "
+    NOT EXISTS(
+                SELECT 1 FROM cust_bill JOIN cust_bill_pkg USING (invnum)
+                      JOIN cust_pkg USING (pkgnum) JOIN part_pkg USING (pkgpart)
+                  WHERE cust_bill.custnum = cust_main.custnum
+                    AND cust_bill._date >= $age
+                    AND COALESCE(part_pkg.classnum, -1) != $ignore_pkgclass
+                  LIMIT 1
+              )
+  ";
   join(' AND ', @sql);
 }
 
diff --git a/FS/FS/part_event/Condition/once.pm b/FS/FS/part_event/Condition/once.pm
index d004814ff..f1645828b 100644
--- a/FS/FS/part_event/Condition/once.pm
+++ b/FS/FS/part_event/Condition/once.pm
@@ -43,11 +43,12 @@ sub condition_sql {
 
   my %tablenum = %{ FS::part_event->eventtable_pkey_sql };
 
-  "0 = ( SELECT COUNT(*) FROM cust_event
-           WHERE cust_event.eventpart = part_event.eventpart
-             AND cust_event.tablenum = $tablenum{$table}
-             AND status != 'failed'
-       )
+  "NOT EXISTS ( SELECT 1 FROM cust_event
+                  WHERE cust_event.eventpart = part_event.eventpart
+                    AND cust_event.tablenum = $tablenum{$table}
+                    AND status != 'failed'
+                  LIMIT 1
+              )
   ";
 
 }