significant speedup from only selected customers with outstanding packages or invoice events in the initial select, and add -a flag for agentnum

1 files changed, 52 insertions, 9 deletions

diff --git a/FS/bin/freeside-daily b/FS/bin/freeside-daily
index 75bbca7d8..db6b97128 100755
--- a/FS/bin/freeside-daily
+++ b/FS/bin/freeside-daily
@@ -11,25 +11,66 @@ use FS::cust_main;
 
 &untaint_argv;	#what it sounds like  (eww)
 use vars qw($opt_d $opt_v $opt_p $opt_s $opt_y);
-getopts("p:d:vsy:");
+getopts("p:a:d:vsy:");
 my $user = shift or die &usage;
 
 adminsuidsetup $user;
 
 $FS::cust_main::DEBUG = 1 if $opt_v;
 
-my %search;
-$search{'payby'} = $opt_p if $opt_p;
-
-my @cust_main = @ARGV
-  ? map { qsearchs('cust_main', { custnum => $_, %search } ) } @ARGV
-  : qsearch('cust_main', \%search )
-;
+my %search = ();
+$search{'payby'}    = $opt_p if $opt_p;
+$search{'agentnum'} = $opt_a if $opt_a;
 
 #we're at now now (and later).
 my($time)= $opt_d ? str2time($opt_d) : $^T;
 $time += $opt_y * 86400 if $opt_y;
 
+# select * from cust_main where
+my $where_pkg = <<"END";
+  0 < ( select count(*) from cust_pkg
+          where cust_main.custnum = cust_pkg.custnum
+            and ( cancel is null or cancel = 0 )
+            and (    setup is null or setup =  0
+                  or bill  is null or bill  <= $time 
+                  or ( expire is not null and expire <= $^T )
+                )
+      )
+END
+
+# or
+my $where_bill_event = <<"END";
+  0 < ( select count(*) from cust_bill
+          where cust_main.custnum = cust_bill.custnum
+            and 0 < charged
+                    - ( select sum(amount) from cust_bill_pay
+                          where cust_bill.invnum = cust_bill_pay.invnum )
+                    - ( select sum(amount) from cust_credit_bill
+                          where cust_bill.invnum = cust_credit_bill.invnum )
+            and 0 < ( select count(*) from part_bill_event
+                        where payby = cust_main.payby
+                          and ( disabled is null or disabled = '' )
+                          and seconds <= $time - cust_bill._date
+                          and 0 = ( select count(*) from cust_bill_event
+                                     where cust_bill.invnum = cust_bill_event.invnum
+                                       and part_bill_event.eventpart = cust_bill_event.eventpart
+                                       and status = 'done'
+                                  )
+
+                    )
+      )
+END
+
+my $extra_sql = ( scalar(%search) ? ' AND ' : ' WHERE ' ). "$where_pkg OR $where_bill_event";
+
+my @cust_main;
+if ( @ARGV ) {
+  @cust_main = map { qsearchs('cust_main', { custnum => $_, %search } ) } @ARGV
+} else {
+  @cust_main = qsearch('cust_main', \%search, '', $extra_sql );
+}
+;
+
 my($cust_main,%saw);
 foreach $cust_main ( @cust_main ) {
 
@@ -112,7 +153,7 @@ freeside-daily - Run daily billing and invoice collection events.
 
 =head1 SYNOPSIS
 
-  freeside-daily [ -d 'date' ] [ -y days ] [ -p 'payby' ] [ -s ] [ -v ] user [ custnum custnum ... ]
+  freeside-daily [ -d 'date' ] [ -y days ] [ -p 'payby' ] [ -a agentnum ] [ -s ] [ -v ] user [ custnum custnum ... ]
 
 =head1 DESCRIPTION
 
@@ -134,6 +175,8 @@ the bill and collect methods of a cust_main object.  See L<FS::cust_main>.
 
   -p: Only process customers with the specified payby (I<CARD>, I<DCRD>, I<CHEK>, I<DCHK>, I<BILL>, I<COMP>, I<LECB>)
 
+  -a: Only process customers with the specified agentnum
+
   -s: re-charge setup fees
 
   -v: enable debugging