diff options
| -rw-r--r-- | FS/FS/Record.pm | 29 | ||||
| -rw-r--r-- | FS/FS/svc_port.pm | 51 |
2 files changed, 40 insertions, 40 deletions
diff --git a/FS/FS/Record.pm b/FS/FS/Record.pm index 29e5322ef..e23699cce 100644 --- a/FS/FS/Record.pm +++ b/FS/FS/Record.pm @@ -30,8 +30,10 @@ 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 - str2time_sql str2time_sql_closing regexp_sql not_regexp_sql ); +@EXPORT_OK = qw( + dbh fields hfields qsearch qsearchs dbdef jsearch + str2time_sql str2time_sql_closing regexp_sql not_regexp_sql concat_sql +); $DEBUG = 0; $me = '[FS::Record]'; @@ -3099,6 +3101,29 @@ sub not_regexp_sql { } +=item concat_sql [ DRIVER_NAME ] ITEMS_ARRAYREF + +Returns the items concatendated based on database type, using "CONCAT()" for +mysql and " || " for Pg and other databases. + +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 concat_sql { + my $driver = ref($_[0]) ? driver_name : shift; + my $items = shift; + + if ( $driver =~ /^mysql/i ) { + 'CONCAT('. join(',', @$items). ')'; + } else { + join('||', @$items); + } + +} + =back =head1 BUGS diff --git a/FS/FS/svc_port.pm b/FS/FS/svc_port.pm index 16d520813..78da13d19 100644 --- a/FS/FS/svc_port.pm +++ b/FS/FS/svc_port.pm @@ -3,7 +3,8 @@ package FS::svc_port; use strict; use vars qw($conf $system $DEBUG $me ); use base qw( FS::svc_Common ); -use FS::Record qw( qsearch qsearchs dbh str2time_sql str2time_sql_closing ); +use FS::Record qw( qsearch qsearchs dbh + str2time_sql str2time_sql_closing concat_sql ); #dbh use FS::cust_svc; use GD::Graph; use GD::Graph::mixed; @@ -255,50 +256,24 @@ sub graph_png { || $end <= $start || $end < 0 || $end > $now || $start > $now || $end-$start > 86400*366 ); - local($FS::Record::nowarn_classload) = 1; + my $_date = str2time_sql. concat_sql([ 'srv_date', "' '", 'srv_time' ]). + str2time_sql_closing; my $serviceid_sql = "('${serviceid}_IN','${serviceid}_OUT')"; - my @records; - my $dbh = dbh; - if ( $dbh->{Driver}->{Name} eq 'Pg' ) { - @records = qsearch({ - 'table' => 'srvexport', - 'select' => "*, date_part('epoch',to_timestamp(srv_date||' '||srv_time,'YYYY-MM-DD HH:MI:SS')) as _date", - 'extra_sql' => "where serviceid in $serviceid_sql and - date_part('epoch',to_timestamp(srv_date||' '||srv_time,'YYYY-MM-DD HH:MI:SS')) >= $start - and date_part('epoch',to_timestamp(srv_date||' '||srv_time,'YYYY-MM-DD HH:MI:SS')) <= $end", - 'order_by' => "order by date_part('epoch',to_timestamp(srv_date||' '||srv_time,'YYYY-MM-DD HH:MI:SS')) asc", - }); - } elsif ( $dbh->{Driver}->{Name} eq 'mysql' ) { - @records = qsearch({ - 'table' => 'srvexport', - 'select' => "*, unix_timestamp(srv_date||' '||srv_time) as _date", - 'extra_sql' => "where serviceid in $serviceid_sql and - unix_timestamp(srv_date||' '||srv_time) >= $start - and unix_timestamp(srv_date||' '||srv_time) <= $end", - 'order_by' => "order by unix_timestamp(srv_date||' '||srv_time) asc", - }); - } else { - return 'Unsupported DBMS'; - } - - #my $_date = str2time_sql. "srv_date||' '||srv_time". - # str2time_sql_closing; - - #my @records = qsearch({ - # 'table' => 'srvexport', - # 'select' => "*, $_date as _date", - # 'extra_sql' => "where serviceid in $serviceid_sql - # and $_date >= $start - # and $_date <= $end", - # 'order_by' => "order by $_date asc", - #}); + local($FS::Record::nowarn_classload) = 1; + my @records = qsearch({ + 'table' => 'srvexport', + 'select' => "*, $_date as _date", + 'extra_sql' => "where serviceid in $serviceid_sql + and $_date >= $start + and $_date <= $end", + 'order_by' => "order by $_date asc", + }); warn "$me ". scalar(@records). " records returned for $serviceid\n" if $DEBUG; - # assume data in DB is correct, # assume always _IN and _OUT pair, assume intvl = 300 |