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 <& cust_pkg_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 # a single sub to collect data for each package, aggregated across both
158 # services and exports. when we add per-service breakdown, this should also
159 # keep the per-service data, but not needed yet
160 my $cust_pkg_stats_sub = sub {
161 my $cust_pkg = shift;
162 if (! $cust_pkg->get('_stats') ) {
163 my ($upbytes, $downbytes, $totalbytes) = (0, 0, 0);
164 my $display_username;
165 foreach my $svcnum ( split(',', $cust_pkg->get('svcnums_concat')) ) {
166 foreach my $export (@exports) {
167 my $svc = FS::cust_svc->by_key($svcnum)->svc_x;
168 my $username = $export->export_username($svc);
169 my $usage = $export->usage_sessions({ %usage_param, 'svc' => $svc });
170 # returns arrayref with one row
171 $upbytes += $usage->[0]->{'acctinputoctets'};
172 $downbytes += $usage->[0]->{'acctoutputoctets'};
173 # in combined services mode with multiple users/MAC addresses per
174 # package, this will just show one of them arbitrarily.
175 $display_username ||= $username;
178 $total_usage[1] += $upbytes;
179 $total_usage[2] += $downbytes;
180 $total_usage[3] += $upbytes + $downbytes;
181 $cust_pkg->set('_stats', [ $display_username,
184 $upbytes + $downbytes ]);
186 return $cust_pkg->get('_stats');
190 $pkg_usage[0] = sub { # username
191 return &{ $cust_pkg_stats_sub }(shift)->[0];
193 foreach my $i (1, 2, 3) { # numeric fields
194 $pkg_usage[$i] = sub { # cust_pkg arg
195 my $value = &{ $cust_pkg_stats_sub }(shift)->[$i];
196 # for now, always show in GB, rounded to 3 digits
197 $value ? bytes_to_gb($value) : '';
201 my $link_cust = [ $p.'view/cust_main.cgi?', 'custnum' ];
203 # cust_pkg search params
204 my %search_hash = ( 'agentnum' => $agentnum );
206 $search_hash{'custnum'} = $cust_main->custnum;
209 # construct a subquery for services/packages with relevant exports
211 my $group_by = ' GROUP BY pkgnum';
212 if ( !$combine_svcs ) {
213 $group_by .= ', svcnum';
216 my $exportnums = join(',', map { $_->get('exportnum') } @exports);
217 my $svcnums_table = 'SELECT pkgnum, ' . FS::Record::group_concat_sql('DISTINCT svcnum', ',') . ' AS svcnums_concat
219 JOIN part_svc USING (svcpart)
220 JOIN export_svc USING (svcpart)
221 WHERE exportnum IN(' . $exportnums . ')' . $group_by;
223 my $sql_query = FS::cust_pkg->search( \%search_hash );
224 # also get the svcnum-list column
225 $sql_query->{'select'} .= ', svcnums_concat' .
226 # and a workaround for the implicit DISTINCTing that happens in qsearch
227 ', NULL AS pkgnum, pkgnum AS real_pkgnum';
228 $sql_query->{'addl_from'} .= " JOIN ($svcnums_table) AS svcnums
230 $sql_query->{'order_by'} = ' ORDER BY cust_pkg.pkgnum, svcnums_concat'; # for stability
232 my $count_query = "SELECT COUNT(*) FROM cust_pkg ".
233 $sql_query->{addl_from} .
234 $sql_query->{extra_sql};
236 my $num_rows = FS::Record->scalar_sql($count_query);
237 my $itemname = $combine_svcs ? 'package' : 'service';
240 emt("[quant,_1,$itemname]", $num_rows),
245 sub { # defer this until the rows have been processed
246 bytes_to_gb($total_usage[$i])
252 $_[0] ? sprintf('%.3f', $_[0] / (1024*1024*1024.0)) : '';
256 my $conf = new FS::Conf;
258 'name' => "CASE WHEN cust_main.company IS NOT NULL
259 AND cust_main.company != ''
260 THEN CONCAT(cust_main.company,' (',cust_main.last,', ',cust_main.first,')')
261 ELSE CONCAT(cust_main.last,', ',cust_main.first)
263 'display_custnum' => $conf->exists('cust_main-default_agent_custid')
264 ? "CASE WHEN cust_main.agent_custid IS NOT NULL
265 AND cust_main.agent_custid != ''
266 AND cust_main.agent_custid ". regexp_sql. " '^[0-9]+\$'
267 THEN CAST(cust_main.agent_custid AS BIGINT)
268 ELSE cust_main.custnum
275 my $frag = 'cust_pkg'. $self->pkgnum; #hack for IE ignoring real #fragment
276 [ "${p}view/cust_main.cgi?custnum=".$self->custnum.
277 "show=packages;fragment=$frag#cust_pkg",
284 if ($self->svcnums_concat =~ /^(\d+)$/) {
285 return [ $p.'view/cust_svc.cgi?' . $1 ];