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 'order_by_sql' => $order_by_sql,
43 'links' => [ #( map { $_ ne 'Cust. Status' ? $link_cust : '' }
44 # FS::UI::Web::cust_header() ),
48 ( map { $link_svc } @svc_header ),
53 'align' => #FS::UI::Web::cust_aligns() .
54 'rlc' . ('l' x scalar(@svc_header)) . 'rrr' ,
55 'nohtmlheader' => ($opt{'nohtmlheader'} || 0),
56 'download_label' => $opt{'download_label'},
63 my $curuser = $FS::CurrentUser::CurrentUser;
64 die "access denied" unless $curuser->access_right('List services');
66 my $title = 'Data Usage Report - ';
70 if ( $opt{'agentnum'} ) {
71 $agentnum = $opt{'agentnum'};
72 } elsif ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
77 my $agent = FS::agent->by_key($agentnum);
78 $title = $agent->agent." $title";
80 @include_agents = qsearch('agent', {});
84 my( $beginning, $ending ) = FS::UI::Web::parse_beginning_ending($cgi);
87 $title .= time2str('%h %o %Y ', $beginning);
90 if ( $ending == 4294967295 ) {
93 $title .= time2str('%h %o %Y', $ending);
96 # can also show a specific customer / service. the main query will handle
97 # agent restrictions, but we need a list of the services to ask the export
99 my ($cust_main, @svc_x);
100 if ( $cgi->param('custnum') =~ /^(\d+)$/ ) {
101 $cust_main = qsearchs( {
102 'table' => 'cust_main',
103 'hashref' => { 'custnum' => $1 },
104 'extra_sql' => ' AND '. $curuser->agentnums_sql,
106 die "Customer not found!" unless $cust_main;
107 # then only report on this agent
108 $agentnum = $cust_main->agentnum;
109 @include_agents = ();
110 # and announce that we're doing it
111 $title .= ' - ' . $cust_main->name_short;
113 # yes, we'll query the database once for each service the customer has,
114 # even non-radacct'd services. probably less bad than a single query that
115 # pulls records for every service for every customer.
116 foreach my $cust_pkg ($cust_main->all_pkgs) {
117 foreach my $cust_svc ($cust_pkg->cust_svc) {
118 push @svc_x, $cust_svc->svc_x;
122 foreach ($cgi->param('svcnum')) {
124 my $cust_svc = FS::cust_svc->by_key($1)
125 or die "service #$1 not found."; # or continue?
126 push @svc_x, $cust_svc->svc_x;
131 my %usage_by_username;
132 if ( exists($opt{usage_by_username}) ) {
133 # There's no agent separation in the radacct data. So in the jumbo report
134 # do this procedure once, and pass the hash into all the per-agent sections.
135 %usage_by_username = %{ $opt{usage_by_username} };
136 $export = $opt{export};
139 $cgi->param('exportnum') =~ /^(\d+)$/
140 or die "illegal export: '".$cgi->param('exportnum')."'";
141 $export = FS::part_export->by_key($1)
142 or die "exportnum $1 not found";
143 $export->exporttype =~ /sqlradius/
144 or die "exportnum ".$export->exportnum." is type ".$export->exporttype.
148 stoptime_start => $beginning,
149 stoptime_end => $ending,
152 # usage_sessions() returns an arrayref of hashrefs of
153 # (username, acctsessiontime, acctinputoctets, acctoutputoctets)
154 # (XXX needs to include 'realm' for sqlradius_withdomain)
157 # then query once per service
159 foreach my $svc ( @svc_x ) {
160 $usage_param{'svc'} = $svc;
161 push @$usage, @{ $export->usage_sessions(\%usage_param) };
164 # one query, get everyone's data
165 $usage = $export->usage_sessions(\%usage_param);
168 # rearrange to be indexed by username.
170 my $username = $_->{'username'};
172 $_->{'acctinputoctets'},
173 $_->{'acctoutputoctets'},
174 $_->{'acctinputoctets'} + $_->{'acctoutputoctets'}
176 $usage_by_username{$username} = \@row;
180 #warn Dumper(\%usage_by_username);
181 my @total_usage = (0, 0, 0, 0); # session time, input, output, input + output
182 my @svc_usage = map {
185 my $username = $export->export_username(shift);
186 return '' if !exists($usage_by_username{$username});
187 my $value = $usage_by_username{ $username }->[$i];
188 $total_usage[$i] += $value;
189 # for now, always show in GB, rounded to 3 digits
194 # set up svcdb-specific stuff
195 my $export_username = sub {
196 $export->export_username(shift); # countrycode + phone, formatted MAC, etc.
200 svc_acct => [ 'Username' ],
201 svc_broadband => [ 'MAC address', 'IP address' ],
202 # svc_phone => [ 'Phone' ], #not yet supported, no search method
203 # (not sure input/output octets is relevant)
206 svc_acct => [ $export_username ],
207 svc_broadband => [ $export_username, 'ip_addr' ],
208 # svc_phone => [ $export_username ],
211 # what kind of service we're operating on
212 my $svcdb = FS::part_export::export_info()->{$export->exporttype}->{'svc'};
213 my $class = "FS::$svcdb";
214 my @svc_header = @{ $svc_header{$svcdb} };
215 my @svc_fields = @{ $svc_fields{$svcdb} };
217 # svc_x search params
218 my %search_hash = ( 'agentnum' => $agentnum,
219 'exportnum' => $export->exportnum );
222 $search_hash{'custnum'} = $cust_main->custnum;
225 $search_hash{'svcnum'} = [ map { $_->get('svcnum') } @svc_x ];
228 my $sql_query = $class->search(\%search_hash);
229 $sql_query->{'select'} .= ', part_pkg.pkg';
230 $sql_query->{'addl_from'} .= ' LEFT JOIN part_pkg USING (pkgpart)';
233 my $svcnums = join(',', map { $_->get('svcnum') } @svc_x);
234 $sql_query->{'extra_sql'} .= ' AND svcnum IN('.$svcnums.')';
237 my $link_svc = [ $p.'view/cust_svc.cgi?', 'svcnum' ];
239 my $link_cust = [ $p.'view/cust_main.cgi?', 'custnum' ];
241 # columns between the customer name and the usage fields
242 my $skip_cols = 1 + scalar(@svc_header);
244 my $num_rows = FS::Record->scalar_sql($sql_query->{count_query});
247 emt('[quant,_1,service]', $num_rows),
251 sub { # defer this until the rows have been processed
252 bytes_to_gb($total_usage[$i])
258 $_[0] ? sprintf('%.3f', $_[0] / (1024*1024*1024.0)) : '';
261 my $conf = new FS::Conf;
263 'name' => "CASE WHEN cust_main.company IS NOT NULL
264 AND cust_main.company != ''
265 THEN CONCAT(cust_main.company,' (',cust_main.last,', ',cust_main.first,')')
266 ELSE CONCAT(cust_main.last,', ',cust_main.first)
268 'display_custnum' => $conf->exists('cust_main-default_agent_custid')
269 ? "CASE WHEN cust_main.agent_custid IS NOT NULL
270 AND cust_main.agent_custid != ''
271 AND cust_main.agent_custid ". regexp_sql. " '^[0-9]+\$'
272 THEN CAST(cust_main.agent_custid AS BIGINT)
273 ELSE cust_main.custnum
278 #warn Dumper \%usage_by_username;