<& elements/search.html, 'title' => 'Access Log', 'name' => 'entries', 'query' => $query, 'count_query' => $count_query, 'header' => [ 'Path', '#', 'Avg time', ], 'fields' => [ sub { shift->pretty_path }, sub { shift->num_entries }, sub { shift->avg_render_seconds }, ], 'sort_fields' => [qw( pretty_path num_entries avg_render_seconds )], &> <%init> die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('Employee Reports'); my %hashref = (); my @extra_sql = (); my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); push @extra_sql, "_date >= $beginning" if $beginning; push @extra_sql, "_date <= $ending" if $ending; my $count_hr = ''; if ( $cgi->param('usernum') =~ /^\d+$/ ) { $hashref{usernum} = $cgi->param('usernum'); $count_hr = "WHERE usernum = ".$hashref{usernum}; } if ( $cgi->param('skip_components') ) { push @extra_sql, "path NOT IN ( '". join("','", map "/var/www/html/freeside/$_", qw( view/REAL_logo.cgi misc/jsrsServer.html misc/xmlhttp-reason-hint.html config/config-image.cgi misc/xmlhttp-part_pkg_taxproduct.html misc/progress-popup.html misc/file-upload.html misc/confirm-censustract.html misc/xmlhttp-address_standardize.html misc/confirm-address_standardize.html misc/xmlhttp-part_pkg_usageprice.html ) ). "' )"; } my $extra_sql = ''; $extra_sql .= (keys(%hashref) ? ' AND ' : ' WHERE ' ). join(' AND ', @extra_sql) if @extra_sql; my $count_extra = $extra_sql; $extra_sql .= ' GROUP BY pretty_path ' if $cgi->param('group_by') eq 'path'; my $query = { 'select' => join(' , ', "regexp_replace(path, '^/var/www/(html/)?freeside/', '') as pretty_path", 'COUNT(*) AS num_entries', 'ROUND(AVG(COALESCE(render_seconds,0)),1) '. ' AS avg_render_seconds', ), 'table' => 'access_user_log', 'hashref' => \%hashref, 'extra_sql' => $extra_sql, }; my $count_query = "SELECT COUNT(DISTINCT path) FROM access_user_log $count_hr $count_extra";