mysql me 1.9 more times

5 files changed, 233 insertions, 36 deletions

diff --git a/FS/FS/cust_main.pm b/FS/FS/cust_main.pm
index 467ddfe..06f2c46 100644
--- a/FS/FS/cust_main.pm
+++ b/FS/FS/cust_main.pm
@@ -19,7 +19,7 @@ use String::Approx qw(amatch);
 use Business::CreditCard 0.28;
 use Locale::Country;
 use Data::Dumper;
-use FS::UID qw( getotaker dbh );
+use FS::UID qw( getotaker dbh driver_name );
 use FS::Record qw( qsearchs qsearch dbdef );
 use FS::Misc qw( send_email generate_ps do_print );
 use FS::Msgcat qw(gettext);
@@ -2890,6 +2890,8 @@ sub realtime_bop {
 
   $options{'description'} ||= 'Internet services';
 
+  return $self->fake_bop($method, $amount, %options) if $options{'fake'};
+
   eval "use Business::OnlinePayment";  
   die $@ if $@;
 
@@ -3215,7 +3217,7 @@ sub realtime_bop {
        'custnum'  => $self->custnum,
        'invnum'   => $options{'invnum'},
        'paid'     => $amount,
-       '_date'     => '',
+       '_date'    => '',
        'payby'    => $method2payby{$method},
        'payinfo'  => $payinfo,
        'paybatch' => $paybatch,
@@ -3312,6 +3314,76 @@ sub realtime_bop {
 
 }
 
+=item fake_bop
+
+=cut
+
+sub fake_bop {
+  my( $self, $method, $amount, %options ) = @_;
+
+  if ( $options{'fake_failure'} ) {
+     return "Error: No error; test failure requested with fake_failure";
+  }
+
+  my %method2payby = (
+    'CC'     => 'CARD',
+    'ECHECK' => 'CHEK',
+    'LEC'    => 'LECB',
+  );
+
+  #my $paybatch = '';
+  #if ( $payment_gateway ) { # agent override
+  #  $paybatch = $payment_gateway->gatewaynum. '-';
+  #}
+  #
+  #$paybatch .= "$processor:". $transaction->authorization;
+  #
+  #$paybatch .= ':'. $transaction->order_number
+  #  if $transaction->can('order_number')
+  #  && length($transaction->order_number);
+
+  my $paybatch = 'FakeProcessor:54:32';
+
+  my $cust_pay = new FS::cust_pay ( {
+     'custnum'  => $self->custnum,
+     'invnum'   => $options{'invnum'},
+     'paid'     => $amount,
+     '_date'    => '',
+     'payby'    => $method2payby{$method},
+     #'payinfo'  => $payinfo,
+     'payinfo'  => '4111111111111111',
+     'paybatch' => $paybatch,
+     #'paydate'  => $paydate,
+     'paydate'  => '2012-05-01',
+  } );
+  $cust_pay->payunique( $options{payunique} ) if length($options{payunique});
+
+  my $error = $cust_pay->insert($options{'manual'} ? ( 'manual' => 1 ) : () );
+
+  if ( $error ) {
+    $cust_pay->invnum(''); #try again with no specific invnum
+    my $error2 = $cust_pay->insert( $options{'manual'} ?
+                                    ( 'manual' => 1 ) : ()
+                                  );
+    if ( $error2 ) {
+      # gah, even with transactions.
+      my $e = 'WARNING: Card/ACH debited but database not updated - '.
+              "error inserting (fake!) payment: $error2".
+              " (previously tried insert with invnum #$options{'invnum'}" .
+              ": $error )";
+      warn $e;
+      return $e;
+    }
+  }
+
+  if ( $options{'paynum_ref'} ) {
+    ${ $options{'paynum_ref'} } = $cust_pay->paynum;
+  }
+
+  return ''; #no error
+
+}
+
 =item default_payment_gateway
 
 =cut
@@ -5852,9 +5924,20 @@ sub _agent_plandata {
 
   #yuck.  this whole thing needs to be reconciled better with 1.9's idea of
   #agent-specific Conf
+
+  use FS::part_event::Condition;
   
   my $agentnum = $self->agentnum;
 
+  my $regexp = '';
+  if ( driver_name =~ /^Pg/i ) {
+    $regexp = '~';
+  } elsif ( driver_name =~ /^mysql/i ) {
+    $regexp = 'REGEXP';
+  } else {
+    die "don't know how to use regular expressions in ". driver_name. " databases";
+  }
+
   my $part_event_option =
     qsearchs({
       'select'    => 'part_event_option.*',
@@ -5864,7 +5947,7 @@ sub _agent_plandata {
         LEFT JOIN part_event_option AS peo_agentnum
           ON ( part_event.eventpart = peo_agentnum.eventpart
                AND peo_agentnum.optionname = 'agentnum'
-               AND peo_agentnum.optionvalue ~ '(^|,)}. $agentnum. q{(,|$)'
+               AND peo_agentnum.optionvalue }. $regexp. q{ '(^|,)}. $agentnum. q{(,|$)'
              )
         LEFT JOIN part_event_option AS peo_cust_bill_age
           ON ( part_event.eventpart = peo_cust_bill_age.eventpart
@@ -5882,13 +5965,8 @@ sub _agent_plandata {
         " ORDER BY
            CASE WHEN peo_cust_bill_age.optionname != 'cust_bill_age'
            THEN -1
-           ELSE EXTRACT( EPOCH FROM
-                           REPLACE( peo_cust_bill_age.optionvalue,
-                                    'm',
-                                    'mon'
-                                  )::interval
-                       )
-          END
+	   ELSE ". FS::part_event::Condition->age2seconds_sql('peo_cust_bill_age.optionvalue').
+        " END
           , part_event.weight".
         " LIMIT 1"
     });
diff --git a/FS/FS/part_event/Condition.pm b/FS/FS/part_event/Condition.pm
index 3c0904c..2b71fbb 100644
--- a/FS/FS/part_event/Condition.pm
+++ b/FS/FS/part_event/Condition.pm
@@ -3,6 +3,8 @@ package FS::part_event::Condition;
 use strict;
 use base qw( FS::part_event_condition );
 
+use FS::UID qw( driver_name );
+
 =head1 NAME
 
 FS::part_event::Condition - Base class for event conditions
@@ -249,10 +251,11 @@ sub option_label {
 
 =back
 
-=item condition_sql_option
+=item condition_sql_option OPTION
 
 This is a class method that returns an SQL fragment for retreiving a condition
 option.  It is primarily intended for use in B<condition_sql>.
+
 =cut
 
 sub condition_sql_option {
@@ -267,6 +270,134 @@ sub condition_sql_option {
    )";
 }
 
+=item condition_sql_option_age_from OPTION FROM_TIMESTAMP
+
+This is a class method that returns an SQL fragment that will retreive a
+condition option, parse it from a frequency (such as "1d", "1w" or "12m"),
+and subtract that interval from the supplied timestamp.  It is primarily
+intended for use in B<condition_sql>.
+
+=cut
+
+sub condition_sql_option_age_from {
+  my( $class, $option, $from ) = @_;
+
+  my $value = $class->condition_sql_option($option);
+
+#  my $str2time = str2time_sql;
+
+  if ( driver_name =~ /^Pg/i ) {
+
+    #can we do better with Pg now that we have $from?  yes we can, bob
+    "( $from - EXTRACT( EPOCH FROM REPLACE( $value, 'm', 'mon')::interval ) )";
+
+  } elsif ( driver_name =~ /^mysql/i ) {
+
+    #hmm... is there a way we can save $value?  we're just an expression, hmm
+    #we might be able to do something like "AS ${option}_value" except we get
+    #used in more complicated expressions and we need some sort of unique
+    #identifer passed down too... yow
+
+    "CASE WHEN $value IS NULL OR $value = ''
+       THEN $from
+     WHEN $value LIKE '%m'
+       THEN UNIX_TIMESTAMP(
+              FROM_UNIXTIME($from) - INTERVAL REPLACE( $value, 'm', '' ) MONTH
+            )
+     WHEN $value LIKE '%y'
+       THEN UNIX_TIMESTAMP(
+              FROM_UNIXTIME($from) - INTERVAL REPLACE( $value, 'y', '' ) YEAR
+            )
+     WHEN $value LIKE '%w'
+       THEN UNIX_TIMESTAMP(
+              FROM_UNIXTIME($from) - INTERVAL REPLACE( $value, 'w', '' ) WEEK
+            )
+     WHEN $value LIKE '%d'
+       THEN UNIX_TIMESTAMP(
+              FROM_UNIXTIME($from) - INTERVAL REPLACE( $value, 'd', '' ) DAY
+            )
+     WHEN $value LIKE '%h'
+       THEN UNIX_TIMESTAMP(
+              FROM_UNIXTIME($from) - INTERVAL REPLACE( $value, 'h', '' ) HOUR
+            )
+     END
+    "
+  } else {
+
+    die "FATAL: don't know how to subtract frequencies from dates for ".
+        driver_name. " databases";
+
+  }
+
+}
+
+=item condition_sql_option_age OPTION
+
+This is a class method that returns an SQL fragment for retreiving a condition
+option, and additionaly parsing it from a frequency (such as "1d", "1w" or
+"12m") into an approximate number of seconds.
+
+Note that since months vary in length, the results of this method should B<not>
+be used in computations (use condition_sql_option_age_from for that).  They are
+useful for for ordering and comparison to other ages.
+
+This method is primarily intended for use in B<order_sql>.
+
+=cut
+
+sub condition_sql_option_age {
+  my( $class, $option ) = @_;
+  $class->age2seconds_sql( $class->condition_sql_option($option) );
+}
+
+=item age2seconds_sql
+
+Class method returns an SQL fragment for parsing an arbitrary frequeny (such
+as "1d", "1w", "12m", "2y" or "12h") into an approximate number of seconds.
+
+Approximate meaning: months are considered to be 30 days, years to be
+365.25 days.  Otherwise the numbers of seconds returned is exact.
+
+=cut
+
+sub age2seconds_sql {
+  my( $class, $value ) = @_;
+
+  if ( driver_name =~ /^Pg/i ) {
+
+    "EXTRACT( EPOCH FROM REPLACE( $value, 'm', 'mon')::interval )";
+
+  } elsif ( driver_name =~ /^mysql/i ) {
+
+    #hmm... is there a way we can save $value?  we're just an expression, hmm
+    #we might be able to do something like "AS ${option}_age" except we get
+    #used in more complicated expressions and we need some sort of unique
+    #identifer passed down too... yow
+    # 2592000  = 30d "1 month"
+    # 31557600 = 365.25d "1 year"
+
+    "CASE WHEN $value IS NULL OR $value = ''
+       THEN 0
+     WHEN $value LIKE '%m'
+       THEN REPLACE( $value, 'm', '' ) * 2592000 
+     WHEN $value LIKE '%y'
+       THEN REPLACE( $value, 'y', '' ) * 31557600
+     WHEN $value LIKE '%w'
+       THEN REPLACE( $value, 'w', '' ) * 604800
+     WHEN $value LIKE '%d'
+       THEN REPLACE( $value, 'd', '' ) * 86400
+     WHEN $value LIKE '%h'
+       THEN REPLACE( $value, 'h', '' ) * 3600
+     END
+    "
+  } else {
+
+    die "FATAL: don't know how to approximate frequencies for ". driver_name.
+        " databases";
+
+  }
+
+}
 
 =head1 NEW CONDITION CLASSES
 
diff --git a/FS/FS/part_event/Condition/balance_age.pm b/FS/FS/part_event/Condition/balance_age.pm
index 94f231e..ec3624a 100644
--- a/FS/FS/part_event/Condition/balance_age.pm
+++ b/FS/FS/part_event/Condition/balance_age.pm
@@ -56,26 +56,20 @@ sub condition {
 sub condition_sql {
   my( $class, $table, %opt ) = @_;
 
-  my $time = $opt{'time'};
-
-  my $over = $class->condition_sql_option('balance');
-  my $age = $class->condition_sql_option('age');
-  my $age_sql =
-    "$time - EXTRACT( EPOCH FROM REPLACE( $age, 'm', 'mon')::interval )";
+  my $over    = $class->condition_sql_option('balance');
+  my $age     = $class->condition_sql_option_age_from('age', $opt{'time'});
 
-  my $balance_sql = FS::cust_main->balance_date_sql( $age_sql );
+  my $balance_sql = FS::cust_main->balance_date_sql( $age );
 
   "$balance_sql > $over";
-
 }
 
 sub order_sql {
-  my( $class ) = @_;
-
-  my $age = $class->condition_sql_option('age');
-  "EXTRACT( EPOCH FROM REPLACE( $age, 'm', 'mon')::interval )";
+  shift->condition_sql_option_age('age');
 }
 
+use FS::UID qw( driver_name );
+
 sub order_sql_weight {
   10;
 }
diff --git a/FS/FS/part_event/Condition/cust_bill_age.pm b/FS/FS/part_event/Condition/cust_bill_age.pm
index 9af6bdd..5c1e468 100644
--- a/FS/FS/part_event/Condition/cust_bill_age.pm
+++ b/FS/FS/part_event/Condition/cust_bill_age.pm
@@ -59,21 +59,13 @@ sub condition {
 sub condition_sql {
   my( $class, $table, %opt ) = @_;
 
-  my $time = $opt{'time'};
-
-  my $age = $class->condition_sql_option('age');
-  my $age_sql = 
-    "$time - EXTRACT( EPOCH FROM REPLACE( $age, 'm', 'mon')::interval )";
-
-  "cust_bill._date <= $age_sql";
+  my $age  = $class->condition_sql_option_age_from('age', $opt{'time'} );
 
+  "cust_bill._date <= $age";
 }
 
 sub order_sql {
-  my( $class ) = @_;
-
-  my $age = $class->condition_sql_option('age');
-  "EXTRACT( EPOCH FROM REPLACE( $age, 'm', 'mon')::interval )";
+  shift->condition_sql_option_age('age');
 }
 
 sub order_sql_weight {
diff --git a/httemplate/search/cdr.html b/httemplate/search/cdr.html
index 61d41f8..9426c33 100644
--- a/httemplate/search/cdr.html
+++ b/httemplate/search/cdr.html
@@ -48,10 +48,12 @@ if ( $cgi->param('freesidestatus') eq 'NULL' ) {
 # dates
 ###
 
+my $str2time_sql = str2time_sql;
+
 my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
 push @search,
-my @dsearch = ( "extract(epoch from calldate) >= $beginning ",
-                "extract(epoch from calldate) <= $ending"
+my @dsearch = ( "$str2time calldate) >= $beginning ",
+                "$str2time calldate) <= $ending"
 	      );
 push @search, @dsearch;
 push @qsearch, @search;