event condition speed improvements, RT#6802

7 files changed, 70 insertions, 14 deletions

diff --git a/FS/FS/cust_main.pm b/FS/FS/cust_main.pm
index 521f77341..104a0c49c 100644
--- a/FS/FS/cust_main.pm
+++ b/FS/FS/cust_main.pm
@@ -3727,6 +3727,24 @@ sub statuses {
   keys %statuscolor;
 }
 
+=item cust_status_sql
+
+Returns an SQL fragment to determine the status of a cust_main record, as a 
+string.
+
+=cut
+
+sub cust_status_sql {
+  my $sql = 'CASE';
+  for my $status ( FS::cust_main->statuses() ) {
+    my $method = $status.'_sql';
+    $sql .= ' WHEN ('.FS::cust_main->$method.") THEN '$status'";
+  }
+  $sql .= ' END';
+  return $sql;
+}
+
+
 =item prospect_sql
 
 Returns an SQL expression identifying prospective cust_main records (customers
diff --git a/FS/FS/cust_pkg.pm b/FS/FS/cust_pkg.pm
index ebc94e7eb..9b023fda7 100644
--- a/FS/FS/cust_pkg.pm
+++ b/FS/FS/cust_pkg.pm
@@ -2542,6 +2542,22 @@ sub cancel_sql {
   "cust_pkg.cancel IS NOT NULL AND cust_pkg.cancel != 0";
 }
 
+=item status_sql
+
+Returns an SQL expression to give the package status as a string.
+
+=cut
+
+sub status_sql {
+"CASE
+  WHEN cust_pkg.cancel IS NOT NULL THEN 'cancelled'
+  WHEN cust_pkg.susp IS NOT NULL THEN 'suspended'
+  WHEN cust_pkg.setup IS NULL THEN 'not yet billed'
+  WHEN ".onetime_sql()." THEN 'one-time charge'
+  ELSE 'active'
+END"
+}
+
 =item search HASHREF
 
 (Class method)
diff --git a/FS/FS/part_event/Condition/cust_status.pm b/FS/FS/part_event/Condition/cust_status.pm
index fbdff25a5..066ee481d 100644
--- a/FS/FS/part_event/Condition/cust_status.pm
+++ b/FS/FS/part_event/Condition/cust_status.pm
@@ -29,4 +29,12 @@ sub condition {
   $hashref->{ $cust_main->status };
 }
 
+sub condition_sql {
+  my( $self, $table ) = @_;
+
+  '('.FS::cust_main->cust_status_sql . ') IN '.
+    $self->condition_sql_option_option('status');
+}
+
+
 1;
diff --git a/FS/FS/part_event/Condition/has_referral_custnum.pm b/FS/FS/part_event/Condition/has_referral_custnum.pm
index 61a815579..70c9c7f8b 100644
--- a/FS/FS/part_event/Condition/has_referral_custnum.pm
+++ b/FS/FS/part_event/Condition/has_referral_custnum.pm
@@ -38,11 +38,13 @@ sub condition {
 }
 
 sub condition_sql {
-  #my( $class, $table ) = @_;
+  my( $class, $table ) = @_;
 
-  "cust_main.referral_custnum IS NOT NULL";
-
-  #XXX a bit harder to check active status here
+  my $sql = FS::cust_main->active_sql;
+  $sql =~ s/cust_main.custnum/cust_main.referral_custnum/;
+  $sql = 'cust_main.referral_custnum IS NOT NULL AND ('.
+          $class->condition_sql_option('active') . ' IS NULL OR '.$sql.')';
+  return $sql;
 }
 
 1;
diff --git a/FS/FS/part_event/Condition/pkg_age.pm b/FS/FS/part_event/Condition/pkg_age.pm
index 8b3b4c971..4a8538780 100644
--- a/FS/FS/part_event/Condition/pkg_age.pm
+++ b/FS/FS/part_event/Condition/pkg_age.pm
@@ -49,10 +49,18 @@ sub condition {
 
 }
 
-#XXX write me for efficiency
-#sub condition_sql {
-#
-#}
+sub condition_sql {
+  my( $class, $table, %opt ) = @_;
+  my $age   = $class->condition_sql_option_age_from('age', $opt{'time'});
+  my $field = $class->condition_sql_option('field');
+#amazingly, this is actually faster 
+  my $sql = '( CASE';
+  foreach( qw(setup last_bill bill adjourn susp expire cancel) ) {
+    $sql .= " WHEN $field = '$_' THEN (cust_pkg.$_ IS NOT NULL AND cust_pkg.$_ <= $age)";
+  }
+  $sql .= ' END )';
+  return $sql;
+}
 
 1;
 
diff --git a/FS/FS/part_event/Condition/pkg_recurring.pm b/FS/FS/part_event/Condition/pkg_recurring.pm
index 1b6682126..1a08869da 100644
--- a/FS/FS/part_event/Condition/pkg_recurring.pm
+++ b/FS/FS/part_event/Condition/pkg_recurring.pm
@@ -20,12 +20,9 @@ sub condition {
 
 }
 
-
-#XXX  join part_pkg USING (pkgpart) 
-#  part_pkg.freq != '0'
-#sub condition_sql {
-#
-#}
+sub condition_sql {
+  FS::cust_pkg->recurring_sql()
+}
 
 1;
 
diff --git a/FS/FS/part_event/Condition/pkg_status.pm b/FS/FS/part_event/Condition/pkg_status.pm
index 6c1c9cca5..3fb374e9a 100644
--- a/FS/FS/part_event/Condition/pkg_status.pm
+++ b/FS/FS/part_event/Condition/pkg_status.pm
@@ -34,4 +34,11 @@ sub condition {
   $hashref->{ $cust_pkg->status };
 }
 
+sub condition_sql {
+  my( $self, $table ) = @_;
+
+  '('.FS::cust_pkg->status_sql . ') IN '.
+  $self->condition_sql_option_option('status');
+}
+
 1;