RT#42724: Data Usage Report - System Error on Column Sort [v3]

diff --git a/httemplate/search/elements/search.html b/httemplate/search/elements/search.html
index beb0173..8b85324 100644 (file)
--- a/httemplate/search/elements/search.html
+++ b/httemplate/search/elements/search.html
@@ -135,8 +135,11 @@ Example:
 
     # sort, link & display properties for fields
 
-    'sort_fields' => [], #optional list of field names or SQL expressions for
-                         # sorts
+    'sort_fields' => [], #optional list of field names or SQL expressions for sorts
+
+    'order_by_sql' => {              #to keep complex SQL expressions out of cgi order_by value,
+      'fieldname' => 'sql snippet',  #  maps fields/sort_fields values to sql snippets
+    }
    
     #listref - each item is the empty string,
     #          or a listref of link and method name to append,
@@ -406,6 +409,12 @@ $order_by = $cgi->param('order_by') if $cgi->param('order_by');
 my $header = [ map { ref($_) ? $_->{'label'} : $_ } @{$opt{header}} ];
 my $rows;
 
+my ($order_by_key,$order_by_desc) = ($order_by =~ /^\s*(.*?)(\s+DESC)?\s*$/i);
+$opt{'order_by_sql'} ||= {};
+$order_by_desc ||= '';
+$order_by = $opt{'order_by_sql'}{$order_by_key} . $order_by_desc
+  if $opt{'order_by_sql'}{$order_by_key};
+
 if ( ref $query ) {
   my @query;
   if (ref($query) eq 'HASH') {

diff --git a/httemplate/search/sqlradius_usage.html b/httemplate/search/sqlradius_usage.html
index fcf6c10..561476e 100644 (file)
--- a/httemplate/search/sqlradius_usage.html
+++ b/httemplate/search/sqlradius_usage.html
@@ -39,6 +39,7 @@
                      @svc_fields,
                      @svc_usage,
                    ],
+  'order_by_sql' => $order_by_sql,
   'links'       => [ #( map { $_ ne 'Cust. Status' ? $link_cust : '' }
                      #  FS::UI::Web::cust_header() ),
                      $link_cust,
@@ -256,4 +257,24 @@ sub bytes_to_gb {
   $_[0] ?  sprintf('%.3f', $_[0] / (1024*1024*1024.0)) : '';
 }
 
+
+my $conf = new FS::Conf;
+my $order_by_sql = {
+  'name'            => "CASE WHEN cust_main.company IS NOT NULL
+                                  AND cust_main.company != ''
+                             THEN CONCAT(cust_main.company,' (',cust_main.last,', ',cust_main.first,')')
+                             ELSE CONCAT(cust_main.last,', ',cust_main.first)
+                        END",
+  'display_custnum' => $conf->exists('cust_main-default_agent_custid')
+                       ? "CASE WHEN cust_main.agent_custid IS NOT NULL
+                                    AND cust_main.agent_custid != ''
+                                    AND cust_main.agent_custid ". regexp_sql. " '^[0-9]+\$'
+                               THEN CAST(cust_main.agent_custid AS BIGINT)
+                               ELSE cust_main.custnum
+                          END"
+                       : "custnum",
+};
+
+#warn Dumper \%usage_by_username;
+
 </%init>