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('agent', {});
78 } elsif ( scalar(@agentnums) == 1 ) {
79 $agentnum = $agentnums[0];
81 @include_agents = qsearch({ 'table' => 'agent',
82 'extra_sql' => 'WHERE agentnum IN ('.
83 join(',',@agentnums). ') '.
84 ' AND '. $curuser->agentnums_sql,
91 my $agent = FS::agent->by_key($agentnum);
92 $title = $agent->agent." $title";
96 my( $beginning, $ending ) = FS::UI::Web::parse_beginning_ending($cgi);
99 $title .= time2str('%h %o %Y ', $beginning);
101 $title .= 'through ';
102 if ( $ending == 4294967295 ) {
105 $title .= time2str('%h %o %Y', $ending);
108 # can also show a specific customer / service. the main query will handle
109 # agent restrictions, but we need a list of the services to ask the export
111 my ($cust_main, @svc_x);
112 if ( $cgi->param('custnum') =~ /^(\d+)$/ ) {
113 $cust_main = qsearchs( {
114 'table' => 'cust_main',
115 'hashref' => { 'custnum' => $1 },
116 'extra_sql' => ' AND '. $curuser->agentnums_sql,
118 die "Customer not found!" unless $cust_main;
119 # then only report on this agent
120 $agentnum = $cust_main->agentnum;
121 @include_agents = ();
122 # and announce that we're doing it
123 $title .= ' - ' . $cust_main->name_short;
125 # yes, we'll query the database once for each service the customer has,
126 # even non-radacct'd services. probably less bad than a single query that
127 # pulls records for every service for every customer.
128 foreach my $cust_pkg ($cust_main->all_pkgs) {
129 foreach my $cust_svc ($cust_pkg->cust_svc) {
130 push @svc_x, $cust_svc->svc_x;
134 foreach ($cgi->param('svcnum')) {
136 my $cust_svc = FS::cust_svc->by_key($1)
137 or die "service #$1 not found."; # or continue?
138 push @svc_x, $cust_svc->svc_x;
143 my %usage_by_username;
144 if ( exists($opt{usage_by_username}) ) {
145 # There's no agent separation in the radacct data. So in the jumbo report
146 # do this procedure once, and pass the hash into all the per-agent sections.
147 %usage_by_username = %{ $opt{usage_by_username} };
148 $export = $opt{export};
151 $cgi->param('exportnum') =~ /^(\d+)$/
152 or die "illegal export: '".$cgi->param('exportnum')."'";
153 $export = FS::part_export->by_key($1)
154 or die "exportnum $1 not found";
155 $export->exporttype =~ /sqlradius/
156 or die "exportnum ".$export->exportnum." is type ".$export->exporttype.
160 stoptime_start => $beginning,
161 stoptime_end => $ending,
164 # usage_sessions() returns an arrayref of hashrefs of
165 # (username, acctsessiontime, acctinputoctets, acctoutputoctets)
166 # (XXX needs to include 'realm' for sqlradius_withdomain)
169 # then query once per service
171 foreach my $svc ( @svc_x ) {
172 $usage_param{'svc'} = $svc;
173 push @$usage, @{ $export->usage_sessions(\%usage_param) };
176 # one query, get everyone's data
177 $usage = $export->usage_sessions(\%usage_param);
180 # rearrange to be indexed by username.
182 my $username = $_->{'username'};
184 $_->{'acctinputoctets'},
185 $_->{'acctoutputoctets'},
186 $_->{'acctinputoctets'} + $_->{'acctoutputoctets'}
188 $usage_by_username{$username} = \@row;
192 #warn Dumper(\%usage_by_username);
193 my @total_usage = (0, 0, 0, 0); # session time, input, output, input + output
194 my @svc_usage = map {
197 my $username = $export->export_username(shift);
198 return '' if !exists($usage_by_username{$username});
199 my $value = $usage_by_username{ $username }->[$i];
200 $total_usage[$i] += $value;
201 # for now, always show in GB, rounded to 3 digits
206 # set up svcdb-specific stuff
207 my $export_username = sub {
208 $export->export_username(shift); # countrycode + phone, formatted MAC, etc.
212 svc_acct => [ 'Username' ],
213 svc_broadband => [ 'MAC address', 'IP address' ],
214 # svc_phone => [ 'Phone' ], #not yet supported, no search method
215 # (not sure input/output octets is relevant)
218 svc_acct => [ $export_username ],
219 svc_broadband => [ $export_username, 'ip_addr' ],
220 # svc_phone => [ $export_username ],
223 # what kind of service we're operating on
224 my $svcdb = FS::part_export::export_info()->{$export->exporttype}->{'svc'};
225 my $class = "FS::$svcdb";
226 my @svc_header = @{ $svc_header{$svcdb} };
227 my @svc_fields = @{ $svc_fields{$svcdb} };
229 # svc_x search params
230 my %search_hash = ( 'agentnum' => $agentnum,
231 'exportnum' => $export->exportnum );
234 $search_hash{'custnum'} = $cust_main->custnum;
237 $search_hash{'svcnum'} = [ map { $_->get('svcnum') } @svc_x ];
240 my $sql_query = $class->search(\%search_hash);
241 $sql_query->{'select'} .= ', part_pkg.pkg';
242 $sql_query->{'addl_from'} .= ' LEFT JOIN part_pkg USING (pkgpart)';
245 my $svcnums = join(',', map { $_->get('svcnum') } @svc_x);
246 $sql_query->{'extra_sql'} .= ' AND svcnum IN('.$svcnums.')';
249 my $link_svc = [ $p.'view/cust_svc.cgi?', 'svcnum' ];
251 my $link_cust = [ $p.'view/cust_main.cgi?', 'custnum' ];
253 # columns between the customer name and the usage fields
254 my $skip_cols = 1 + scalar(@svc_header);
256 my $num_rows = FS::Record->scalar_sql($sql_query->{count_query});
259 emt('[quant,_1,service]', $num_rows),
263 sub { # defer this until the rows have been processed
264 bytes_to_gb($total_usage[$i])
270 $_[0] ? sprintf('%.3f', $_[0] / (1024*1024*1024.0)) : '';
273 my $conf = new FS::Conf;
275 'name' => "CASE WHEN cust_main.company IS NOT NULL
276 AND cust_main.company != ''
277 THEN CONCAT(cust_main.company,' (',cust_main.last,', ',cust_main.first,')')
278 ELSE CONCAT(cust_main.last,', ',cust_main.first)
280 'display_custnum' => $conf->exists('cust_main-default_agent_custid')
281 ? "CASE WHEN cust_main.agent_custid IS NOT NULL
282 AND cust_main.agent_custid != ''
283 AND cust_main.agent_custid ". regexp_sql. " '^[0-9]+\$'
284 THEN CAST(cust_main.agent_custid AS BIGINT)
285 ELSE cust_main.custnum
290 #warn Dumper \%usage_by_username;