add net vs gross amounts to invoice report; make the "net sales" links on sales/credi...

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

diff --git a/FS/FS/Record.pm b/FS/FS/Record.pm
index 915bbd8..dbcbf09 100644 (file)
--- a/FS/FS/Record.pm
+++ b/FS/FS/Record.pm
@@ -26,7 +26,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]';
@@ -213,6 +213,7 @@ The preferred usage is to pass a hash reference of named parameters:
                            #these are optional...
                            'select'    => '*',
                            'extra_sql' => 'AND field ',
+                           'order_by'  => 'ORDER BY something',
                            #'cache_obj' => '', #optional
                            'addl_from' => 'LEFT JOIN othtable USING ( field )',
                          }
@@ -235,13 +236,14 @@ fine in the common case where there are only two parameters:
 =cut
 
 sub qsearch {
-  my($stable, $record, $select, $extra_sql, $cache, $addl_from );
+  my($stable, $record, $select, $extra_sql, $order_by, $cache, $addl_from );
   if ( ref($_[0]) ) { #hashref for now, eventually maybe accept a list too
     my $opt = shift;
     $stable    = $opt->{'table'}     or die "table name is required";
     $record    = $opt->{'hashref'}   || {};
     $select    = $opt->{'select'}    || '*';
     $extra_sql = $opt->{'extra_sql'} || '';
+    $order_by  = $opt->{'order_by'}  || '';
     $cache     = $opt->{'cache_obj'} || '';
     $addl_from = $opt->{'addl_from'} || '';
   } else {
@@ -362,6 +364,7 @@ sub qsearch {
   }
 
   $statement .= " $extra_sql" if defined($extra_sql);
+  $statement .= " $order_by"  if defined($order_by);
 
   warn "[debug]$me $statement\n" if $DEBUG > 1;
   my $sth = $dbh->prepare($statement)
@@ -2142,6 +2145,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