+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)',
+ );
+ 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)',
+ );
+ 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
+
+Returns a Tie::IxHash reference of the internal short names used for the
+report sections ('fbd', 'mbs', etc.) to the full names.
+
+=cut
+
+tie our %parts, 'Tie::IxHash', (
+ fbd => 'Fixed Broadband Deployment',
+ fbs => 'Fixed Broadband Subscription',
+ fvs => 'Fixed Voice Subscription',
+ lts => 'Local Exchange Telephone Subscription',
+ voip => 'Interconnected VoIP Subscription',
+ mbd => 'Mobile Broadband Deployment',
+ mbsa => 'Mobile Broadband Service Availability',
+ mbs => 'Mobile Broadband Subscription',
+ mvd => 'Mobile Voice Deployment',
+ mvs => 'Mobile Voice Subscription',
+);
+
+sub parts {
+ Storable::dclone(\%parts);
+}