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 usage_by_username => \%usage_by_username,
13 download_label => 'Download this section',
18 <& /elements/footer.html &>
20 <& elements/search.html,
23 'query' => $sql_query,
24 'count_query' => $sql_query->{'count_query'},
25 'header' => [ #FS::UI::Web::cust_header(),
35 'fields' => [ #\&FS::UI::Web::cust_fields,
42 'links' => [ #( map { $_ ne 'Cust. Status' ? $link_cust : '' }
43 # FS::UI::Web::cust_header() ),
47 ( map { $link_svc } @svc_header ),
52 'align' => #FS::UI::Web::cust_aligns() .
53 'rlc' . ('l' x scalar(@svc_header)) . 'rrr' ,
54 'nohtmlheader' => ($opt{'nohtmlheader'} || 0),
55 'download_label' => $opt{'download_label'},
62 my $curuser = $FS::CurrentUser::CurrentUser;
63 die "access denied" unless $curuser->access_right('List services');
65 my $title = 'Data Usage Report - ';
69 if ( $opt{'agentnum'} ) {
70 $agentnum = $opt{'agentnum'};
71 } elsif ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
76 my $agent = FS::agent->by_key($agentnum);
77 $title = $agent->agent." $title";
79 @include_agents = qsearch('agent', {});
83 my( $beginning, $ending ) = FS::UI::Web::parse_beginning_ending($cgi);
86 $title .= time2str('%h %o %Y ', $beginning);
89 if ( $ending == 4294967295 ) {
92 $title .= time2str('%h %o %Y', $ending);
95 # can also show a specific customer / service. the main query will handle
96 # agent restrictions, but we need a list of the services to ask the export
98 my ($cust_main, @svc_x);
99 if ( $cgi->param('custnum') =~ /^(\d+)$/ ) {
100 $cust_main = qsearchs( {
101 'table' => 'cust_main',
102 'hashref' => { 'custnum' => $1 },
103 'extra_sql' => ' AND '. $curuser->agentnums_sql,
105 die "Customer not found!" unless $cust_main;
106 # then only report on this agent
107 $agentnum = $cust_main->agentnum;
108 @include_agents = ();
109 # and announce that we're doing it
110 $title .= ' - ' . $cust_main->name_short;
112 # yes, we'll query the database once for each service the customer has,
113 # even non-radacct'd services. probably less bad than a single query that
114 # pulls records for every service for every customer.
115 foreach my $cust_pkg ($cust_main->all_pkgs) {
116 foreach my $cust_svc ($cust_pkg->cust_svc) {
117 push @svc_x, $cust_svc->svc_x;
121 foreach ($cgi->param('svcnum')) {
123 my $cust_svc = FS::cust_svc->by_key($1)
124 or die "service #$1 not found."; # or continue?
125 push @svc_x, $cust_svc->svc_x;
130 my %usage_by_username;
131 if ( exists($opt{usage_by_username}) ) {
132 # There's no agent separation in the radacct data. So in the jumbo report
133 # do this procedure once, and pass the hash into all the per-agent sections.
134 %usage_by_username = %{ $opt{usage_by_username} };
135 $export = $opt{export};
138 $cgi->param('exportnum') =~ /^(\d+)$/
139 or die "illegal export: '".$cgi->param('exportnum')."'";
140 $export = FS::part_export->by_key($1)
141 or die "exportnum $1 not found";
142 $export->exporttype =~ /sqlradius/
143 or die "exportnum ".$export->exportnum." is type ".$export->exporttype.
147 stoptime_start => $beginning,
148 stoptime_end => $ending,
151 # usage_sessions() returns an arrayref of hashrefs of
152 # (username, acctsessiontime, acctinputoctets, acctoutputoctets)
153 # (XXX needs to include 'realm' for sqlradius_withdomain)
156 # then query once per service
158 foreach my $svc ( @svc_x ) {
159 $usage_param{'svc'} = $svc;
160 push @$usage, @{ $export->usage_sessions(\%usage_param) };
163 # one query, get everyone's data
164 my $usage = $export->usage_sessions(\%usage_param);
167 # rearrange to be indexed by username.
169 my $username = $_->{'username'};
171 $_->{'acctinputoctets'},
172 $_->{'acctoutputoctets'},
173 $_->{'acctinputoctets'} + $_->{'acctoutputoctets'}
175 $usage_by_username{$username} = \@row;
179 my @total_usage = (0, 0, 0, 0); # session time, input, output, input + output
180 my @svc_usage = map {
183 my $username = $export->export_username(shift);
184 return '' if !exists($usage_by_username{$username});
185 my $value = $usage_by_username{ $username }->[$i];
186 $total_usage[$i] += $value;
187 # for now, always show in GB, rounded to 3 digits
192 # set up svcdb-specific stuff
193 my $export_username = sub {
194 $export->export_username(shift); # countrycode + phone, formatted MAC, etc.
198 svc_acct => [ 'Username' ],
199 svc_broadband => [ 'MAC address', 'IP address' ],
200 # svc_phone => [ 'Phone' ], #not yet supported, no search method
201 # (not sure input/output octets is relevant)
204 svc_acct => [ $export_username ],
205 svc_broadband => [ $export_username, 'ip_addr' ],
206 # svc_phone => [ $export_username ],
209 # what kind of service we're operating on
210 my $svcdb = FS::part_export::export_info()->{$export->exporttype}->{'svc'};
211 my $class = "FS::$svcdb";
212 my @svc_header = @{ $svc_header{$svcdb} };
213 my @svc_fields = @{ $svc_fields{$svcdb} };
215 # svc_x search params
216 my %search_hash = ( 'agentnum' => $agentnum,
217 'exportnum' => $export->exportnum );
220 $search_hash{'custnum'} = $cust_main->custnum;
223 $search_hash{'svcnum'} = [ map { $_->get('svcnum') } @svc_x ];
226 my $sql_query = $class->search(\%search_hash);
227 $sql_query->{'select'} .= ', part_pkg.pkg';
228 $sql_query->{'addl_from'} .= ' LEFT JOIN part_pkg USING (pkgpart)';
231 my $svcnums = join(',', map { $_->get('svcnum') } @svc_x);
232 $sql_query->{'extra_sql'} .= ' AND svcnum IN('.$svcnums.')';
235 my $link_svc = [ $p.'view/cust_svc.cgi?', 'svcnum' ];
237 my $link_cust = [ $p.'view/cust_main.cgi?', 'custnum' ];
239 # columns between the customer name and the usage fields
240 my $skip_cols = 1 + scalar(@svc_header);
242 my $num_rows = FS::Record->scalar_sql($sql_query->{count_query});
245 emt('[quant,_1,service]', $num_rows),
249 sub { # defer this until the rows have been processed
250 bytes_to_gb($total_usage[$i])
256 $_[0] ? sprintf('%.3f', $_[0] / (1024*1024*1024.0)) : '';