implement condition_sql for FS::part_event::Condition::cust_bill_hasnt_noauto, RT12714

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..bb0f3d0 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,37 @@ sub condition {
   ! $cust_bill->no_auto;
 }
 
-#sub condition_sql {
-#  my( $class, $table ) = @_;
-#  
-#  my $sql = qq|  |;
-#  return $sql;
-#}
+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?
+  # -add indices on cust_pkg.no_auto and part_pkg.no_auto and others?
+
+  "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
+                        ) 
+                )
+        )
+  ";
+}
 
 1;