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 - ';
68 my @include_agents = ();
70 if ( $opt{'agentnum'} =~ /^(\d+)$/ ) {
71 $agentnum = $opt{'agentnum'};
74 my @agentnums = grep /^(\d+)$/, $cgi->param('agentnum');
77 @include_agents = qsearch({ 'table' => 'agent',
78 'hashref' => { 'disabled'=>'' },
79 'extra_sql' => ' AND '. $curuser->agentnums_sql,
81 } elsif ( scalar(@agentnums) == 1 ) {
82 $agentnum = $agentnums[0];
84 @include_agents = qsearch({ 'table' => 'agent',
85 'hashref' => { 'disabled' => '', },
86 'extra_sql' => 'AND agentnum IN ('.
87 join(',',@agentnums). ') '.
88 ' AND '. $curuser->agentnums_sql,
95 my $agent = FS::agent->by_key($agentnum);
96 $title = $agent->agent." $title";
100 my( $beginning, $ending ) = FS::UI::Web::parse_beginning_ending($cgi);
103 $title .= time2str('%h %o %Y ', $beginning);
105 $title .= 'through ';
106 if ( $ending == 4294967295 ) {
109 $title .= time2str('%h %o %Y', $ending);
112 # can also show a specific customer / service. the main query will handle
113 # agent restrictions, but we need a list of the services to ask the export
115 my ($cust_main, @svc_x);
116 if ( $cgi->param('custnum') =~ /^(\d+)$/ ) {
117 $cust_main = qsearchs( {
118 'table' => 'cust_main',
119 'hashref' => { 'custnum' => $1 },
120 'extra_sql' => ' AND '. $curuser->agentnums_sql,
122 die "Customer not found!" unless $cust_main;
123 # then only report on this agent
124 $agentnum = $cust_main->agentnum;
125 @include_agents = ();
126 # and announce that we're doing it
127 $title .= ' - ' . $cust_main->name_short;
129 # yes, we'll query the database once for each service the customer has,
130 # even non-radacct'd services. probably less bad than a single query that
131 # pulls records for every service for every customer.
132 foreach my $cust_pkg ($cust_main->all_pkgs) {
133 foreach my $cust_svc ($cust_pkg->cust_svc) {
134 push @svc_x, $cust_svc->svc_x;
138 foreach ($cgi->param('svcnum')) {
140 my $cust_svc = FS::cust_svc->by_key($1)
141 or die "service #$1 not found."; # or continue?
142 push @svc_x, $cust_svc->svc_x;
147 my %usage_by_username;
148 if ( exists($opt{usage_by_username}) ) {
149 # There's no agent separation in the radacct data. So in the jumbo report
150 # do this procedure once, and pass the hash into all the per-agent sections.
151 %usage_by_username = %{ $opt{usage_by_username} };
152 $export = $opt{export};
155 $cgi->param('exportnum') =~ /^(\d+)$/
156 or die "illegal export: '".$cgi->param('exportnum')."'";
157 $export = FS::part_export->by_key($1)
158 or die "exportnum $1 not found";
159 $export->exporttype =~ /sqlradius/
160 or die "exportnum ".$export->exportnum." is type ".$export->exporttype.
164 stoptime_start => $beginning,
165 stoptime_end => $ending,
168 # usage_sessions() returns an arrayref of hashrefs of
169 # (username, acctsessiontime, acctinputoctets, acctoutputoctets)
170 # (XXX needs to include 'realm' for sqlradius_withdomain)
173 # then query once per service
175 foreach my $svc ( @svc_x ) {
176 $usage_param{'svc'} = $svc;
177 push @$usage, @{ $export->usage_sessions(\%usage_param) };
180 # one query, get everyone's data
181 $usage = $export->usage_sessions(\%usage_param);
184 # rearrange to be indexed by username.
186 my $username = $_->{'username'};
188 $_->{'acctinputoctets'},
189 $_->{'acctoutputoctets'},
190 $_->{'acctinputoctets'} + $_->{'acctoutputoctets'}
192 $usage_by_username{$username} = \@row;
196 my @total_usage = (0, 0, 0, 0); # session time, input, output, input + output
197 my @svc_usage = map {
200 my $username = $export->export_username(shift);
201 return '' if !exists($usage_by_username{$username});
202 my $value = $usage_by_username{ $username }->[$i];
203 $total_usage[$i] += $value;
204 # for now, always show in GB, rounded to 3 digits
209 # set up svcdb-specific stuff
210 my $export_username = sub {
211 $export->export_username(shift); # countrycode + phone, formatted MAC, etc.
215 svc_acct => [ 'Username' ],
216 svc_broadband => [ 'MAC address', 'IP address' ],
217 # svc_phone => [ 'Phone' ], #not yet supported, no search method
218 # (not sure input/output octets is relevant)
221 svc_acct => [ $export_username ],
222 svc_broadband => [ $export_username, 'ip_addr' ],
223 # svc_phone => [ $export_username ],
226 # what kind of service we're operating on
227 my $svcdb = FS::part_export::export_info()->{$export->exporttype}->{'svc'};
228 my $class = "FS::$svcdb";
229 my @svc_header = @{ $svc_header{$svcdb} };
230 my @svc_fields = @{ $svc_fields{$svcdb} };
232 # svc_x search params
233 my %search_hash = ( 'agentnum' => $agentnum,
234 'exportnum' => $export->exportnum );
237 $search_hash{'custnum'} = $cust_main->custnum;
240 $search_hash{'svcnum'} = [ map { $_->get('svcnum') } @svc_x ];
243 my $sql_query = $class->search(\%search_hash);
244 $sql_query->{'select'} .= ', part_pkg.pkg';
245 $sql_query->{'addl_from'} .= ' LEFT JOIN part_pkg USING (pkgpart)';
248 my $svcnums = join(',', map { $_->get('svcnum') } @svc_x);
249 $sql_query->{'extra_sql'} .= ' AND svcnum IN('.$svcnums.')';
252 my $link_svc = [ $p.'view/cust_svc.cgi?', 'svcnum' ];
254 my $link_cust = [ $p.'view/cust_main.cgi?', 'custnum' ];
256 # columns between the customer name and the usage fields
257 my $skip_cols = 1 + scalar(@svc_header);
259 my $num_rows = FS::Record->scalar_sql($sql_query->{count_query});
262 emt('[quant,_1,service]', $num_rows),
266 sub { # defer this until the rows have been processed
267 bytes_to_gb($total_usage[$i])
273 $_[0] ? sprintf('%.3f', $_[0] / (1024*1024*1024.0)) : '';
277 my $conf = new FS::Conf;
279 'name' => "CASE WHEN cust_main.company IS NOT NULL
280 AND cust_main.company != ''
281 THEN CONCAT(cust_main.company,' (',cust_main.last,', ',cust_main.first,')')
282 ELSE CONCAT(cust_main.last,', ',cust_main.first)
284 'display_custnum' => $conf->exists('cust_main-default_agent_custid')
285 ? "CASE WHEN cust_main.agent_custid IS NOT NULL
286 AND cust_main.agent_custid != ''
287 AND cust_main.agent_custid ". regexp_sql. " '^[0-9]+\$'
288 THEN CAST(cust_main.agent_custid AS BIGINT)
289 ELSE cust_main.custnum
294 #warn Dumper \%usage_by_username;