further optimize condition_sql for "Invoice eligible for automatic collection" condit...

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 0276255..d782c12 100644 (file)
--- 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
+                          )
+              )
   ";
 }