diff options
| author | Jonathan Prykop <jonathan@freeside.biz> | 2016-07-29 23:53:35 -0500 |
|---|---|---|
| committer | Jonathan Prykop <jonathan@freeside.biz> | 2016-07-29 23:53:35 -0500 |
| commit | d406868577ff4df701bc3fe6ef9c0ab6f0e72999 (patch) | |
| tree | e3783fe2f3b18303ded95cbea689049b8ac6f55b | |
| parent | d39978bc228a538071e062329532e33df4a6dc9d (diff) | |
RT#42724: Data Usage Report - System Error on Column Sort
| -rw-r--r-- | httemplate/search/elements/search.html | 13 | ||||
| -rw-r--r-- | httemplate/search/sqlradius_usage.html | 19 |
2 files changed, 29 insertions, 3 deletions
diff --git a/httemplate/search/elements/search.html b/httemplate/search/elements/search.html index a279f5327..b6ee7b373 100644 --- 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 b5551ade6..08f9b6ba1 100644 --- 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, @@ -257,7 +258,23 @@ sub bytes_to_gb { $_[0] ? sprintf('%.3f', $_[0] / (1024*1024*1024.0)) : ''; } -warn Dumper \%usage_by_username; +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> |