(a timestamp value to run the report as of this date) and "agentnum"
(to limit to a single agent).
+OPTIONS may also contain "detail", a flag that tells the report to return
+a comma-separated list of the detail records included in each row count.
+
=cut
sub report {
'cir_speed_down',
'cir_speed_up',
);
- my $from =
- 'deploy_zone_block
+ push @select, 'blocknum' if $opt{detail};
+
+ my $from = 'deploy_zone_block
JOIN deploy_zone USING (zonenum)
JOIN agent USING (agentnum)';
my @where = (
'COUNT(*)',
'COUNT(is_consumer)',
);
+ push @select, "array_to_string(array_agg(pkgnum), ',')" if $opt{detail};
+
my $from =
'cust_pkg
JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
# number of lines/subscriptions
'SUM(CASE WHEN is_voip = 1 THEN 1 ELSE phone_lines END)',
# consumer grade lines/subscriptions
- 'SUM(CASE WHEN is_consumer = 1 THEN ( CASE WHEN is_voip = 1 THEN voip_sessions ELSE phone_lines END) ELSE 0 END)'
+ 'SUM(CASE WHEN is_consumer = 1 THEN ( CASE WHEN is_voip = 1 THEN voip_sessions ELSE phone_lines END) ELSE 0 END)',
);
+ push @select, "array_to_string(array_agg(pkgnum), ',')" if $opt{detail};
my $from = 'cust_pkg
JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
"SUM(CASE WHEN media = 'Cable Modem' THEN phone_lines ELSE 0 END)",
"SUM(CASE WHEN media = 'Fixed Wireless' THEN phone_lines ELSE 0 END)",
);
+ push @select, "array_to_string(array_agg(pkgnum),',')" if $opt{detail};
+
my $from =
'cust_pkg
JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
"SUM(CASE WHEN (voip_lastmile = 1 AND media = 'Fixed Wireless') THEN 1 ELSE 0 END)",
"SUM(CASE WHEN (voip_lastmile = 1 AND media NOT IN('Copper', 'Fiber', 'Cable Modem', 'Fixed Wireless') ) THEN 1 ELSE 0 END)",
);
+ push @select, "array_to_string(array_agg(pkgnum),',')" if $opt{detail};
my $from =
'cust_pkg
'COUNT(*)',
'COUNT(is_consumer)',
);
+ push @select, "array_to_string(array_agg(pkgnum),',')" if $opt{detail};
+
my $from =
'cust_pkg
JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
'COUNT(*)',
'COUNT(mobile_direct)',
);
+ push @select, "array_to_string(array_agg(pkgnum),',')" if $opt{detail};
+
my $from =
'cust_pkg
JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
Storable::dclone(\%parts);
}
+=item part_table SECTION
+
+Returns the name of the primary table that's aggregated in the report section
+SECTION. The last column of the report returned by the L</report> method is
+a comma-separated list of record numbers, in this table, that are included in
+the report line item.
+
+=cut
+
+sub part_table {
+ my ($class, $part) = @_;
+ if ($part eq 'fbd') {
+ return 'deploy_zone_block';
+ } else {
+ return 'cust_pkg';
+ } # add other cases as we add more of the deployment/availability reports
+}
+
1;
associated with this towernum (or any of these, if it's an arrayref) (or NO
towernum, if it's zero). This is an extreme niche case.
+=item 477part, 477rownum, date
+
+Limit to packages included in a specific row of one of the FCC 477 reports.
+'477part' is the section name (see L<FS::Report::FCC_477> methods), 'date'
+is the report as-of date (completely unrelated to the package setup/bill/
+other date fields), and '477rownum' is the row number of the report starting
+with zero. Row numbers have no inherent meaning, so this is useful only
+for explaining a 477 report you've already run.
+
=back
=cut
}
##
+ # parse the 477 report drill-down options
+ ##
+
+ if ($params->{'477part'} =~ /^([a-z]+)$/) {
+ my $section = $1;
+ my ($date, $rownum, $agentnum);
+ if ($params->{'date'} =~ /^(\d+)$/) {
+ $date = $1;
+ }
+ if ($params->{'477rownum'} =~ /^(\d+)$/) {
+ $rownum = $1;
+ }
+ if ($params->{'agentnum'} =~ /^(\d+)$/) {
+ $agentnum = $1;
+ }
+ if ($date and defined($rownum)) {
+ my $report = FS::Report::FCC_477->report($section,
+ 'date' => $date,
+ 'agentnum' => $agentnum,
+ 'detail' => 1
+ );
+ my $row = $report->[$rownum]
+ or die "row $rownum is past the end of the report";
+ my $pkgnums = $row->[-1] || '0';
+ # '0' so that if there are no pkgnums (empty string) it will create
+ # a valid query that returns nothing
+ warn "PKGNUMS:\n$pkgnums\n\n"; # XXX debug
+
+ # and this overrides everything
+ @where = ( "cust_pkg.pkgnum IN($pkgnums)" );
+ } # else we're missing some params, ignore the whole business
+ }
+
+ ##
# setup queries, links, subs, etc. for the search
##
<thead>
<& $header &>
</thead>
+% my $rownum = 0;
% foreach my $row (@$data) {
<tr>
+% my $first = 1;
% foreach my $item (@$row) {
- <td><% $item %></td>
+ <td>
+% if ($first and $part_link{$partname}) {
+ <a href="<% $part_link{$partname} . "477rownum=$rownum" %>"><% $item || '(empty)' %></a>
+% $first = 0;
+% } else {
+ <% $item %>
% }
+ </td>
+% } #foreach $item
</tr>
-% }
+% $rownum++;
+% } #foreach $row
</table>
% } # foreach $partname
<& /elements/footer.html &>
unless $FS::CurrentUser::CurrentUser->access_right('List packages');
my %parts;
+my %part_link;
# load from cache if possible
my $session;
if ( $cgi->param('session') =~ /^(\d+)$/ ) {
foreach my $partname (@partnames) {
$parts{$partname} ||= FS::Report::FCC_477->report( $partname,
date => $date,
- agentnum => $agentnum
+ agentnum => $agentnum,
);
+ my $detail_table = FS::Report::FCC_477->part_table($partname);
+ if ($detail_table eq 'cust_pkg') {
+ my $link = popurl(1).'cust_pkg.cgi?477part='.$partname.";date=$date;";
+ if ($agentnum) {
+ $link .= "agentnum=$agentnum;";
+ }
+ $part_link{$partname} = $link;
+ } # don't include detail links to deploy_blocks, that's pointless
}
$m->cache->set($session, \%parts, '1h');
#scalars
for (qw( agentnum cust_status cust_main_salesnum salesnum custnum magic status
custom cust_fields pkgbatch zip
+ 477part 477rownum date
))
{
$search_hash{$_} = $cgi->param($_) if length($cgi->param($_));