From 2322f0123062b8cf2f52aed0a63bb4634cdbaef1 Mon Sep 17 00:00:00 2001 From: Ivan Kohler Date: Sat, 9 Aug 2014 19:38:19 -0700 Subject: [PATCH] optimize billing for customers with lots of existing packages and invoices, RT#30238 --- FS/FS/Schema.pm | 5 +++-- FS/FS/cust_main.pm | 30 +++++++++++++++++++++++++++--- FS/FS/cust_main/Billing.pm | 24 +++++++++++------------- FS/FS/part_event/Condition/inactive_age.pm | 28 ++++++++++++++++------------ FS/FS/part_event/Condition/once.pm | 11 ++++++----- 5 files changed, 63 insertions(+), 35 deletions(-) diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm index 64f8ac9cf..9c57e310c 100644 --- a/FS/FS/Schema.pm +++ b/FS/FS/Schema.pm @@ -1880,8 +1880,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 9a83099de..43d8f0ce7 100644 --- a/FS/FS/cust_main.pm +++ b/FS/FS/cust_main.pm @@ -3785,9 +3785,17 @@ Returns all the payments (see L) 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 @@ -3805,6 +3813,22 @@ sub num_cust_pay { $sth->fetchrow_arrayref->[0]; } +=item unapplied_cust_pay + +Returns all the unapplied payments (see L) 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) for this customer's specific diff --git a/FS/FS/cust_main/Billing.pm b/FS/FS/cust_main/Billing.pm index 5090a6810..0376fc857 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 @@ -2407,13 +2409,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 + ) "; } -- 2.11.0