From 556cefb34ed648303951009e3bec919e18532bed Mon Sep 17 00:00:00 2001 From: ivan Date: Sun, 28 Oct 2007 03:55:27 +0000 Subject: [PATCH] mysql me harder --- FS/FS/Record.pm | 33 +++++++++++++++++++++- FS/FS/cust_svc.pm | 47 +++++++++++++++++--------------- FS/FS/part_export/sqlradius.pm | 13 ++------- htetc/handler.pl | 2 +- httemplate/search/report_receivables.cgi | 8 +++--- 5 files changed, 64 insertions(+), 39 deletions(-) diff --git a/FS/FS/Record.pm b/FS/FS/Record.pm index 7f35265ec..9db95f40f 100644 --- a/FS/FS/Record.pm +++ b/FS/FS/Record.pm @@ -24,7 +24,7 @@ use Tie::IxHash; @ISA = qw(Exporter); #export dbdef for now... everything else expects to find it here -@EXPORT_OK = qw(dbh fields hfields qsearch qsearchs dbdef jsearch); +@EXPORT_OK = qw(dbh fields hfields qsearch qsearchs dbdef jsearch str2time_sql); $DEBUG = 0; $me = '[FS::Record]'; @@ -2202,6 +2202,37 @@ sub DESTROY { return; } =back +=head1 SUBROUTINES + +=over 4 + +=item str2time_sql [ DRIVER_NAME ] + +Returns a function to convert to unix time based on database type, such as +"EXTRACT( EPOCH FROM" for Pg or "UNIX_TIMESTAMP(" for mysql. You are +responsible for the closing parenthesis yourself. Don't let it down. It's a +sensitive parenthesis. + +You can pass an optional driver name such as "Pg", "mysql" or +$dbh->{Driver}->{Name} to return a function for that database instead of +the current database. + +=cut + +sub str2time_sql { + my $driver = shift || driver_name; + + return 'UNIX_TIMESTAMP(' if $driver =~ /^mysql/i; + return 'EXTRACT( EPOCH FROM ' if $driver =~ /^Pg/i; + + warn "warning: unknown database type $driver; guessing how to convert ". + "dates to UNIX timestamps"; + return 'EXTRACT(EPOCH FROM '; + +} + +=back + =head1 BUGS This module should probably be renamed, since much of the functionality is diff --git a/FS/FS/cust_svc.pm b/FS/FS/cust_svc.pm index 5178a959a..af54f2230 100644 --- a/FS/FS/cust_svc.pm +++ b/FS/FS/cust_svc.pm @@ -4,7 +4,7 @@ use strict; use vars qw( @ISA $DEBUG $me $ignore_quantity ); use Carp; use FS::Conf; -use FS::Record qw( qsearch qsearchs dbh ); +use FS::Record qw( qsearch qsearchs dbh str2time_sql ); use FS::cust_pkg; use FS::part_pkg; use FS::part_svc; @@ -428,17 +428,8 @@ sub seconds_since_sqlradacct { or die "can't connect to sqlradius database: ". $DBI::errstr; #select a unix time conversion function based on database type - my $str2time; - if ( $dbh->{Driver}->{Name} =~ /^mysql(PP)?$/ ) { - $str2time = 'UNIX_TIMESTAMP('; - } elsif ( $dbh->{Driver}->{Name} eq 'Pg' ) { - $str2time = 'EXTRACT( EPOCH FROM '; - } else { - warn "warning: unknown database type ". $dbh->{Driver}->{Name}. - "; guessing how to convert to UNIX timestamps"; - $str2time = 'extract(epoch from '; - } - + my $str2time = str2time_sql( $dbh->{Driver}->{Name} ); + my $username = $part_export->export_username($svc_x); my $query; @@ -538,16 +529,7 @@ sub attribute_since_sqlradacct { or die "can't connect to sqlradius database: ". $DBI::errstr; #select a unix time conversion function based on database type - my $str2time; - if ( $dbh->{Driver}->{Name} =~ /^mysql(PP)?$/ ) { - $str2time = 'UNIX_TIMESTAMP('; - } elsif ( $dbh->{Driver}->{Name} eq 'Pg' ) { - $str2time = 'EXTRACT( EPOCH FROM '; - } else { - warn "warning: unknown database type ". $dbh->{Driver}->{Name}. - "; guessing how to convert to UNIX timestamps"; - $str2time = 'extract(epoch from '; - } + my $str2time = str2time_sql( $dbh->{Driver}->{Name} ); my $username = $part_export->export_username($svc_x); @@ -637,6 +619,27 @@ sub get_cdrs_for_update { } ); } + #astricon hack? config option? + push @cdrs, + qsearch( { + 'table' => 'cdr', + 'hashref' => { 'freesidestatus' => '', + 'src' => $number, + }, + 'extra_sql' => 'FOR UPDATE', + } ); + + if ( length($default_prefix) ) { + push @cdrs, + qsearch( { + 'table' => 'cdr', + 'hashref' => { 'freesidestatus' => '', + 'src' => "$default_prefix$number", + }, + 'extra_sql' => 'FOR UPDATE', + } ); + } + @cdrs; } diff --git a/FS/FS/part_export/sqlradius.pm b/FS/FS/part_export/sqlradius.pm index 9995952c5..4190b034d 100644 --- a/FS/FS/part_export/sqlradius.pm +++ b/FS/FS/part_export/sqlradius.pm @@ -2,7 +2,7 @@ package FS::part_export::sqlradius; use vars qw(@ISA $DEBUG %info %options $notes1 $notes2); use Tie::IxHash; -use FS::Record qw( dbh qsearch qsearchs ); +use FS::Record qw( dbh qsearch qsearchs str2time_sql ); use FS::part_export; use FS::svc_acct; use FS::export_svc; @@ -560,16 +560,7 @@ sub usage_sessions { qw( datasrc username password ) ); #select a unix time conversion function based on database type - my $str2time; - if ( $dbh->{Driver}->{Name} =~ /^mysql(PP)?$/ ) { - $str2time = 'UNIX_TIMESTAMP('; - } elsif ( $dbh->{Driver}->{Name} eq 'Pg' ) { - $str2time = 'EXTRACT( EPOCH FROM '; - } else { - warn "warning: unknown database type ". $dbh->{Driver}->{Name}. - "; guessing how to convert to UNIX timestamps"; - $str2time = 'extract(epoch from '; - } + my $str2time = str2time_sql( $dbh->{Driver}->{Name} ); my @fields = ( qw( username realm framedipaddress diff --git a/htetc/handler.pl b/htetc/handler.pl index 8bf0d59d8..913a60250 100644 --- a/htetc/handler.pl +++ b/htetc/handler.pl @@ -127,7 +127,7 @@ sub handler use Locale::Country; use FS; use FS::UID qw(cgisuidsetup dbh getotaker datasrc driver_name); - use FS::Record qw(qsearch qsearchs fields dbdef); + use FS::Record qw(qsearch qsearchs fields dbdef str2time_sql); use FS::Conf; use FS::CGI qw(header menubar popurl rooturl table itable ntable idiot eidiot small_custview myexit http_header); diff --git a/httemplate/search/report_receivables.cgi b/httemplate/search/report_receivables.cgi index 425fa2b89..e68eb8229 100755 --- a/httemplate/search/report_receivables.cgi +++ b/httemplate/search/report_receivables.cgi @@ -82,13 +82,13 @@ sub owed { #handle start and end ranges + my $str2time = str2time; + #24h * 60m * 60s - push @where, "cust_bill._date <= extract(epoch from now())-". - ($start * 86400) + push @where, "cust_bill._date <= $str2time now() ) - ". ($start * 86400) if $start; - push @where, "cust_bill._date > extract(epoch from now()) - ". - ($end * 86400) + push @where, "cust_bill._date > $str2time now() ) - ". ($end * 86400) if $end; #handle 'cust' option -- 2.11.0