+sub mvs_sql {
+ my $class = shift;
+ my %opt = @_;
+ my $date = $opt{date} || time;
+ my $agentnum = $opt{agentnum};
+ my $q = $opt{ignore_quantity} ? '1' : 'COALESCE(cust_pkg.quantity, 1)';
+
+ my @select = (
+ 'state.fips',
+ "SUM($q)",
+ "SUM($q * COALESCE(mobile_direct,0))",
+ );
+ 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
+
+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);
+}
+
+=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
+}