From 900d4d72315d52f8838f397522e0cae238f38811 Mon Sep 17 00:00:00 2001 From: Ivan Kohler Date: Fri, 4 Mar 2016 15:50:24 -0800 Subject: [PATCH] optimize order package (cust_main->next_bill_date), RT#39822 --- FS/FS/cust_main/Packages.pm | 31 ++++++++++++++++++++++++++++++- 1 file changed, 30 insertions(+), 1 deletion(-) diff --git a/FS/FS/cust_main/Packages.pm b/FS/FS/cust_main/Packages.pm index 6a69517d5..5a14e2e86 100644 --- a/FS/FS/cust_main/Packages.pm +++ b/FS/FS/cust_main/Packages.pm @@ -618,7 +618,36 @@ undef if no billing package has a next bill date. sub next_bill_date { my $self = shift; - min( map $_->get('bill'), grep $_->get('bill'), $self->billing_pkgs ); + +# super inefficient with lots of packages +# min( map $_->get('bill'), grep $_->get('bill'), $self->billing_pkgs ); + + my $custnum = $self->custnum; + + $self->scalar_sql(" + SELECT MIN(bill) FROM cust_pkg + LEFT JOIN cust_pkg_option AS cust_suspend_bill_option + ON ( cust_pkg.pkgnum = cust_suspend_bill_option.pkgnum + AND cust_suspend_bill_option.optionname = 'suspend_bill' ) + LEFT JOIN cust_pkg_option AS cust_no_suspend_bill_option + ON ( cust_pkg.pkgnum = cust_no_suspend_bill_option.pkgnum + AND cust_no_suspend_bill_option.optionname = 'no_suspend_bill' ) + LEFT JOIN part_pkg USING (pkgpart) + LEFT JOIN part_pkg_option AS part_suspend_bill_option + ON ( part_pkg.pkgpart = part_suspend_bill_option.pkgpart + AND part_suspend_bill_option.optionname = 'suspend_bill' ) + WHERE custnum = $custnum + AND bill IS NOT NULL AND bill != 0 + AND ( cancel IS NULL OR cancel = 0 ) + AND part_pkg.freq != '' AND part_pkg.freq != '0' + AND ( ( susp IS NULL OR susp = 0 ) + OR COALESCE(cust_suspend_bill_option.optionvalue,'0') = '1' + OR ( COALESCE(part_suspend_bill_option.optionvalue,'0') = '1' + AND COALESCE(cust_no_suspend_bill_option.optionvalue,'0') = '0' + ) + ) + "); + } =item num_cancelled_pkgs -- 2.11.0