X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=FS%2FFS%2Fpart_event%2FCondition%2Fcust_bill_hasnt_noauto.pm;h=d782c12c15f0b03c6ba349c62433d5cde78f27db;hp=027625569d1d439f8e844ce2774d1f3a1aea0057;hb=18af4f3316291938fa8f0a74e083209f62eac4fa;hpb=0549def83bcba56e53482ea0f33472e18b215649 diff --git a/FS/FS/part_event/Condition/cust_bill_hasnt_noauto.pm b/FS/FS/part_event/Condition/cust_bill_hasnt_noauto.pm index 027625569..d782c12c1 100644 --- a/FS/FS/part_event/Condition/cust_bill_hasnt_noauto.pm +++ b/FS/FS/part_event/Condition/cust_bill_hasnt_noauto.pm @@ -26,32 +26,18 @@ sub condition { sub condition_sql { my( $class, $table, %opt ) = @_; - # XXX: can be made faster with optimizations? - # -remove some/all sub-selects? - # -remove the two main separate selects? - - "0 = (select count(1) from cust_pkg - where cust_pkg.no_auto = 'Y' and cust_pkg.pkgnum in - (select distinct cust_bill_pkg.pkgnum - from cust_bill_pkg, cust_pkg - where cust_bill_pkg.pkgnum = cust_pkg.pkgnum - and cust_bill_pkg.invnum = cust_bill.invnum - and cust_bill_pkg.pkgnum > 0 - ) - ) - AND - 0 = (select count(1) from part_pkg - where part_pkg.no_auto = 'Y' and part_pkg.pkgpart in - (select cust_pkg.pkgpart from cust_pkg - where pkgnum in - (select distinct cust_bill_pkg.pkgnum - from cust_bill_pkg, cust_pkg - where cust_bill_pkg.pkgnum = cust_pkg.pkgnum - and cust_bill_pkg.invnum = cust_bill.invnum - and cust_bill_pkg.pkgnum > 0 - ) - ) - ) + # can be made still faster with optimizations? + + "NOT EXISTS ( SELECT 1 FROM cust_pkg + LEFT JOIN part_pkg USING (pkgpart) + WHERE ( cust_pkg.no_auto = 'Y' OR part_pkg.no_auto = 'Y' ) + AND cust_pkg.pkgnum IN + ( SELECT DISTINCT cust_bill_pkg.pkgnum + FROM cust_bill_pkg + WHERE cust_bill_pkg.invnum = cust_bill.invnum + AND cust_bill_pkg.pkgnum > 0 + ) + ) "; }