use Tie::IxHash;
use Storable;
-$DEBUG = 1;
+$DEBUG = 0;
=head1 NAME
" ON (part_pkg.pkgpart = t_$name.pkgpart)";
}
+sub dbaname {
+ # Returns an sql expression for the DBA name
+ "COALESCE( deploy_zone.dbaname,
+ (SELECT value FROM conf WHERE conf.name = 'company_name'
+ AND (conf.agentnum = deploy_zone.agentnum
+ OR conf.agentnum IS NULL)
+ ORDER BY conf.agentnum IS NOT NULL DESC
+ LIMIT 1)
+ ) AS dbaname"
+}
+
sub active_on {
# Returns a condition to limit packages to those that were setup before a
# certain date, and not canceled before that date.
}
sub is_fixed_broadband {
- "is_broadband::int = 1 AND technology::int IN(".join(',',
- 10, 11, 12, 20, 30, 40, 41, 42, 50, 60, 70, 90, 0
- ).")";
+ "is_broadband::int = 1 AND technology::int IN( 10, 11, 12, 20, 30, 40, 41, 42, 50, 60, 70, 90, 0 )"
+}
+
+sub is_mobile_broadband {
+ "is_broadband::int = 1 AND technology::int IN( 80, 81, 82, 83, 84, 85, 86, 87, 88)"
}
=item report SECTION, OPTIONS
(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 {
sub fbd_sql {
my $class = shift;
- my %opt = shift;
+ my %opt = @_;
my $date = $opt{date} || time;
warn $date;
my $agentnum = $opt{agentnum};
my @select = (
'censusblock',
- 'COALESCE(dbaname, agent.agent)',
+ dbaname(),
'technology',
'CASE WHEN is_consumer IS NOT NULL THEN 1 ELSE 0 END',
'adv_speed_down',
'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 = (
);
push @where, "agentnum = $agentnum" if $agentnum;
- my $order_by = 'censusblock, dbaname, technology, is_consumer, is_business';
+ my $order_by = 'censusblock, agentnum, technology, is_consumer, is_business';
"SELECT ".join(', ', @select) . "
FROM $from
sub fbs_sql {
my $class = shift;
- my %opt = shift;
+ my %opt = @_;
my $date = $opt{date} || time;
my $agentnum = $opt{agentnum};
'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)
sub fvs_sql {
my $class = shift;
- my %opt = shift;
+ my %opt = @_;
my $date = $opt{date} || time;
my $agentnum = $opt{agentnum};
# 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)
sub lts_sql {
my $class = shift;
- my %opt = shift;
+ my %opt = @_;
my $date = $opt{date} || time;
my $agentnum = $opt{agentnum};
"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)
sub voip_sql {
my $class = shift;
- my %opt = shift;
+ my %opt = @_;
my $date = $opt{date} || time;
my $agentnum = $opt{agentnum};
"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
GROUP BY $group_by
ORDER BY $order_by
";
+}
+
+sub mbs_sql {
+ my $class = shift;
+ my %opt = @_;
+ my $date = $opt{date} || time;
+ my $agentnum = $opt{agentnum};
+
+ my @select = (
+ 'state.fips',
+ 'broadband_downstream',
+ 'broadband_upstream',
+ '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)
+ JOIN state USING (country, state)
+ JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
+ JOIN part_pkg USING (pkgpart) '.
+ join_optionnames_int(qw(
+ is_broadband technology
+ is_consumer
+ )).
+ join_optionnames(qw(broadband_downstream broadband_upstream))
+ ;
+ my @where = (
+ active_on($date),
+ is_mobile_broadband()
+ );
+ push @where, "cust_main.agentnum = $agentnum" if $agentnum;
+ my $group_by = 'state.fips, broadband_downstream, broadband_upstream ';
+ my $order_by = $group_by;
+ "SELECT ".join(', ', @select) . "
+ FROM $from
+ WHERE ".join(' AND ', @where)."
+ GROUP BY $group_by
+ ORDER BY $order_by
+ ";
+}
+
+sub mvs_sql {
+ my $class = shift;
+ my %opt = @_;
+ my $date = $opt{date} || time;
+ my $agentnum = $opt{agentnum};
+
+ my @select = (
+ 'state.fips',
+ '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)
+ JOIN state USING (country, state)
+ JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
+ JOIN part_pkg USING (pkgpart) '.
+ join_optionnames_int(qw( is_mobile mobile_direct) )
+ ;
+ my @where = (
+ active_on($date),
+ 'is_mobile = 1'
+ );
+ push @where, "cust_main.agentnum = $agentnum" if $agentnum;
+ my $group_by = 'state.fips';
+ my $order_by = $group_by;
+
+ "SELECT ".join(', ', @select) . "
+ FROM $from
+ WHERE ".join(' AND ', @where)."
+ GROUP BY $group_by
+ ORDER BY $order_by
+ ";
}
=item parts
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;
projects / freeside.git / blobdiff