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

[freeside.git] / FS / FS / part_event / Condition / cust_bill_hasnt_noauto.pm

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 78a6d51..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
@@ -23,11 +23,22 @@ sub condition {
   ! $cust_bill->no_auto;
 }
 
-#sub condition_sql {
-#  my( $class, $table ) = @_;
-#  
-#  my $sql = qq|  |;
-#  return $sql;
-#}
+sub condition_sql {
+  my( $class, $table, %opt ) = @_;
+  
+  # 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
+                          )
+              )
+  ";
+}
 
 1;