event condition speed improvements, RT#6802

[freeside.git] / FS / FS / cust_main.pm

diff --git a/FS/FS/cust_main.pm b/FS/FS/cust_main.pm
index 1f9e3cd..104a0c4 100644 (file)
--- a/FS/FS/cust_main.pm
+++ b/FS/FS/cust_main.pm
@@ -1723,6 +1723,7 @@ sub check {
     } else {
       return "Illegal expiration date: ". $self->paydate;
     }
+    $m = sprintf('%02d',$m);
     $self->paydate("$y-$m-01");
     my($nowm,$nowy)=(localtime(time))[4,5]; $nowm++; $nowy+=1900;
     return gettext('expired_card')
@@ -2269,7 +2270,7 @@ sub total_owed_date {
         AND _date <= $time
   ";
 
-  sprintf( "%.2f", $self->scalar_sql($sql) );
+  sprintf( "%.2f", $self->scalar_sql($sql) || 0 );
 
 }
 
@@ -2349,8 +2350,7 @@ sub total_unapplied_credits {
       WHERE custnum = $custnum
   ";
 
-  #XXX fix harmless but loud: Argument "" isn't numeric in sprintf 
-  sprintf( "%.2f", $self->scalar_sql($sql) );
+  sprintf( "%.2f", $self->scalar_sql($sql) || 0 );
 
 }
 
@@ -2388,8 +2388,7 @@ sub total_unapplied_payments {
       WHERE custnum = $custnum
   ";
 
-  #XXX fix harmless but loud: Argument "" isn't numeric in sprintf 
-  sprintf( "%.2f", $self->scalar_sql($sql) );
+  sprintf( "%.2f", $self->scalar_sql($sql) || 0 );
 
 }
 
@@ -2427,8 +2426,7 @@ sub total_unapplied_refunds {
       WHERE custnum = $custnum
   ";
 
-  #XXX fix harmless but loud: Argument "" isn't numeric in sprintf 
-  sprintf( "%.2f", $self->scalar_sql($sql) );
+  sprintf( "%.2f", $self->scalar_sql($sql) || 0 );
 
 }
 
@@ -2486,7 +2484,7 @@ sub balance_date_range {
   my $self = shift;
   my $sql = 'SELECT SUM('. $self->balance_date_sql(@_).
             ') FROM cust_main WHERE custnum='. $self->custnum;
-  sprintf( '%.2f', $self->scalar_sql($sql) );
+  sprintf( '%.2f', $self->scalar_sql($sql) || 0 );
 }
 
 =item balance_pkgnum PKGNUM
@@ -3729,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
@@ -4516,10 +4532,21 @@ sub process_bill_and_collect {
 sub _upgrade_data { #class method
   my ($class, %opts) = @_;
 
-  foreach my $sql (
+  my @statements = (
     'UPDATE h_cust_main SET paycvv = NULL WHERE paycvv IS NOT NULL',
     'UPDATE cust_main SET signupdate = (SELECT signupdate FROM h_cust_main WHERE signupdate IS NOT NULL AND h_cust_main.custnum = cust_main.custnum ORDER BY historynum DESC LIMIT 1) WHERE signupdate IS NULL',
-  ) {
+  );
+  # fix yyyy-m-dd formatted paydates
+  if ( driver_name =~ /^mysql$/i ) {
+    push @statements,
+    "UPDATE cust_main SET paydate = CONCAT( SUBSTRING(paydate FROM 1 FOR 5), '0', SUBSTRING(paydate FROM 6) ) WHERE SUBSTRING(paydate FROM 7 FOR 1) = '-'";
+  }
+  else { # the SQL standard
+    push @statements, 
+    "UPDATE cust_main SET paydate = SUBSTRING(paydate FROM 1 FOR 5) || '0' || SUBSTRING(paydate FROM 6) WHERE SUBSTRING(paydate FROM 7 FOR 1) = '-'";
+  }
+
+  foreach my $sql ( @statements ) {
     my $sth = dbh->prepare($sql) or die dbh->errstr;
     $sth->execute or die $sth->errstr;
   }