1 % if ( @include_agents ) {
3 <& /elements/header.html, $title &>
4 % foreach my $agent ( @include_agents ) {
5 % $cgi->param('agentnum', $agent->agentnum); #for download links
6 <DIV WIDTH="100%" STYLE="page-break-after: always">
7 <FONT SIZE=6><% $agent->agent %></FONT><BR><BR>
8 <& sqlradius_usage.html,
10 agentnum => $agent->agentnum,
12 download_label => 'Download this section',
17 <& /elements/footer.html &>
19 <& elements/search.html,
21 'name' => $combine_svcs ? 'packages' : 'services',
22 'query' => $sql_query,
23 'count_query' => $count_query,
24 'header' => [ #FS::UI::Web::cust_header(),
34 'fields' => [ #\&FS::UI::Web::cust_fields,
38 @pkg_usage, # username, upload, download, total
40 'order_by_sql' => $order_by_sql,
41 'links' => [ #( map { $_ ne 'Cust. Status' ? $link_cust : '' }
42 # FS::UI::Web::cust_header() ),
50 'align' => #FS::UI::Web::cust_aligns() .
52 'nohtmlheader' => ($opt{'nohtmlheader'} || 0),
53 'download_label' => $opt{'download_label'},
60 my $curuser = $FS::CurrentUser::CurrentUser;
61 die "access denied" unless $curuser->access_right('List packages');
63 my $title = 'Data Usage Report - ';
65 my @include_agents = ();
67 my $combine_svcs = $cgi->param('combine_svcs') ? 1 : 0;
69 if ( $opt{'agentnum'} =~ /^(\d+)$/ ) {
70 $agentnum = $opt{'agentnum'};
73 my @agentnums = grep /^(\d+)$/, $cgi->param('agentnum');
76 @include_agents = qsearch({ 'table' => 'agent',
77 'hashref' => { 'disabled'=>'' },
78 'extra_sql' => ' AND '. $curuser->agentnums_sql,
80 } elsif ( scalar(@agentnums) == 1 ) {
81 $agentnum = $agentnums[0];
83 @include_agents = qsearch({ 'table' => 'agent',
84 'hashref' => { 'disabled' => '', },
85 'extra_sql' => 'AND agentnum IN ('.
86 join(',',@agentnums). ') '.
87 ' AND '. $curuser->agentnums_sql,
94 my $agent = FS::agent->by_key($agentnum);
95 $title = $agent->agent." $title";
99 my( $beginning, $ending ) = FS::UI::Web::parse_beginning_ending($cgi);
102 $title .= time2str('%h %o %Y ', $beginning);
104 $title .= 'through ';
105 if ( $ending == 4294967295 ) {
108 $title .= time2str('%h %o %Y', $ending);
111 # can also show a specific customer / service. the main query will handle
112 # agent restrictions, but we need a list of the services to ask the export
115 if ( $cgi->param('custnum') =~ /^(\d+)$/ ) {
116 $cust_main = qsearchs( {
117 'table' => 'cust_main',
118 'hashref' => { 'custnum' => $1 },
119 'extra_sql' => ' AND '. $curuser->agentnums_sql,
121 die "Customer not found!" unless $cust_main;
122 # then only report on this agent
123 $agentnum = $cust_main->agentnum;
124 @include_agents = ();
125 # and announce that we're doing it
126 $title .= ' - ' . $cust_main->name_short;
130 if ( $opt{exports} ) {
131 @exports = @{ $opt{exports} };
132 } elsif ( $cgi->param('exportnum') ) {
133 foreach my $exportnum ($cgi->param('exportnum')) {
134 $exportnum =~ /^(\d+)$/
135 or die "illegal export: '".$cgi->param('exportnum')."'";
136 my $export = FS::part_export->by_key($1)
137 or die "exportnum $1 not found";
138 $export->exporttype =~ /sqlradius/
139 or die "exportnum ".$export->exportnum." is type ".$export->exporttype.
141 push @exports, $export;
143 die "exportnum required" unless @exports;
145 # do something sensible if no exports were selected
146 @exports = FS::part_export::sqlradius->all_sqlradius;
150 stoptime_start => $beginning,
151 stoptime_end => $ending,
155 my @total_usage = ('', 0, 0, 0); # username, input, output, input + output
157 # remember which exports apply to which services, so we don't inappropriately
158 # ask the wrong ones for usage stats
160 foreach my $export (@exports) {
162 foreach ($export->export_svc) {
163 $seen{ $_->svcpart } = 1;
165 $export_svcparts{ $export->exportnum } = \%seen;
168 # a single sub to collect data for each package, aggregated across both
169 # services and exports. when we add per-service breakdown, this should also
170 # keep the per-service data, but not needed yet
171 my $cust_pkg_stats_sub = sub {
172 my $cust_pkg = shift;
173 if (! $cust_pkg->get('_stats') ) {
174 my ($upbytes, $downbytes, $totalbytes) = (0, 0, 0);
175 my $display_username;
176 foreach my $svcnum ( split(',', $cust_pkg->get('svcnums_concat')) ) {
177 my $cust_svc = FS::cust_svc->by_key($svcnum);
178 my $svc = $cust_svc->svc_x;
179 foreach my $export (@exports) {
180 if ( $export_svcparts{ $export->exportnum }{ $cust_svc->svcpart } ) {
181 my $username = $export->export_username($svc);
182 my $usage = $export->usage_sessions({ %usage_param, 'svc' => $svc });
183 # returns arrayref with one row
184 $upbytes += $usage->[0]->{'acctinputoctets'};
185 $downbytes += $usage->[0]->{'acctoutputoctets'};
186 # in combined services mode with multiple users/MAC addresses per
187 # package, this will just show one of them arbitrarily.
188 $display_username ||= $username;
190 # else this export doesn't apply so skip it
193 $total_usage[1] += $upbytes;
194 $total_usage[2] += $downbytes;
195 $total_usage[3] += $upbytes + $downbytes;
196 $cust_pkg->set('_stats', [ $display_username,
199 $upbytes + $downbytes ]);
201 return $cust_pkg->get('_stats');
205 $pkg_usage[0] = sub { # username
206 return &{ $cust_pkg_stats_sub }(shift)->[0];
208 foreach my $i (1, 2, 3) { # numeric fields
209 $pkg_usage[$i] = sub { # cust_pkg arg
210 my $value = &{ $cust_pkg_stats_sub }(shift)->[$i];
211 # for now, always show in GB, rounded to 3 digits
212 $value ? bytes_to_gb($value) : '';
216 my $link_cust = [ $p.'view/cust_main.cgi?', 'custnum' ];
218 # cust_pkg search params
219 my %search_hash = ( 'agentnum' => $agentnum );
221 $search_hash{'custnum'} = $cust_main->custnum;
224 # construct a subquery for services/packages with relevant exports
226 my $group_by = ' GROUP BY pkgnum';
227 if ( !$combine_svcs ) {
228 $group_by .= ', svcnum';
231 my $exportnums = join(',', map { $_->get('exportnum') } @exports);
232 my $svcnums_table = 'SELECT pkgnum, ' . FS::Record::group_concat_sql('DISTINCT svcnum', ',') . ' AS svcnums_concat
234 JOIN part_svc USING (svcpart)
235 JOIN export_svc USING (svcpart)
236 WHERE exportnum IN(' . $exportnums . ')' . $group_by;
238 my $sql_query = FS::cust_pkg->search( \%search_hash );
239 # also get the svcnum-list column
240 $sql_query->{'select'} .= ', svcnums_concat' .
241 # and a workaround for the implicit DISTINCTing that happens in qsearch
242 ', NULL AS pkgnum, pkgnum AS real_pkgnum';
243 $sql_query->{'addl_from'} .= " JOIN ($svcnums_table) AS svcnums
245 $sql_query->{'order_by'} = ' ORDER BY cust_pkg.pkgnum, svcnums_concat'; # for stability
247 my $count_query = "SELECT COUNT(*) FROM cust_pkg ".
248 $sql_query->{addl_from} .
249 $sql_query->{extra_sql};
251 my $num_rows = FS::Record->scalar_sql($count_query);
252 my $itemname = $combine_svcs ? 'package' : 'service';
255 emt("[quant,_1,$itemname]", $num_rows),
260 sub { # defer this until the rows have been processed
261 bytes_to_gb($total_usage[$i])
267 $_[0] ? sprintf('%.3f', $_[0] / (1024*1024*1024.0)) : '';
270 my $conf = new FS::Conf;
272 'name' => "CASE WHEN cust_main.company IS NOT NULL
273 AND cust_main.company != ''
274 THEN CONCAT(cust_main.company,' (',cust_main.last,', ',cust_main.first,')')
275 ELSE CONCAT(cust_main.last,', ',cust_main.first)
277 'display_custnum' => $conf->exists('cust_main-default_agent_custid')
278 ? "CASE WHEN cust_main.agent_custid IS NOT NULL
279 AND cust_main.agent_custid != ''
280 AND cust_main.agent_custid ". regexp_sql. " '^[0-9]+\$'
281 THEN CAST(cust_main.agent_custid AS BIGINT)
282 ELSE cust_main.custnum
289 [ "${p}view/cust_pkg.cgi?", 'real_pkgnum' ];
294 if ($self->svcnums_concat =~ /^(\d+)$/) {
295 return [ $p.'view/cust_svc.cgi?' . $1 ];