From: ivan Date: Mon, 29 Oct 2007 10:31:31 +0000 (+0000) Subject: mysql me 1.9 more times X-Git-Tag: TRIXBOX_2_6~258 X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=commitdiff_plain;h=804a4999a84b827ac4ec3fa34158e097da31ade3 mysql me 1.9 more times --- diff --git a/FS/FS/cust_main.pm b/FS/FS/cust_main.pm index 467ddfe0c..06f2c4636 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 3c0904c6c..2b71fbb77 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. + =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. + +=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 +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. + +=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 94f231ec1..ec3624a6d 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 9af6bdd4b..5c1e46869 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 61d41f8d0..9426c33c3 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;