477 report: mobile deployment info

5 files changed, 156 insertions, 26 deletions

diff --git a/FS/FS/Report/FCC_477.pm b/FS/FS/Report/FCC_477.pm
index 86fa0a6..0f3dfb1 100644
--- a/FS/FS/Report/FCC_477.pm
+++ b/FS/FS/Report/FCC_477.pm
@@ -11,7 +11,7 @@ use FS::Record qw( dbh );
 use Tie::IxHash;
 use Storable;
 
-$DEBUG = 1;
+$DEBUG = 0;
 
 =head1 NAME
 
@@ -259,9 +259,11 @@ sub active_on {
 }
 
 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
@@ -290,7 +292,7 @@ sub report {
 
 sub fbd_sql {
   my $class = shift;
-  my %opt = shift;
+  my %opt = @_;
   my $date = $opt{date} || time;
   warn $date;
   my $agentnum = $opt{agentnum};
@@ -328,7 +330,7 @@ sub fbd_sql {
 
 sub fbs_sql {
   my $class = shift;
-  my %opt = shift;
+  my %opt = @_;
   my $date = $opt{date} || time;
   my $agentnum = $opt{agentnum};
 
@@ -371,7 +373,7 @@ sub fbs_sql {
 
 sub fvs_sql {
   my $class = shift;
-  my %opt = shift;
+  my %opt = @_;
   my $date = $opt{date} || time;
   my $agentnum = $opt{agentnum};
 
@@ -413,7 +415,7 @@ sub fvs_sql {
 
 sub lts_sql {
   my $class = shift;
-  my %opt = shift;
+  my %opt = @_;
   my $date = $opt{date} || time;
   my $agentnum = $opt{agentnum};
 
@@ -465,7 +467,7 @@ sub lts_sql {
 
 sub voip_sql {
   my $class = shift;
-  my %opt = shift;
+  my %opt = @_;
   my $date = $opt{date} || time;
   my $agentnum = $opt{agentnum};
 
@@ -510,7 +512,82 @@ sub voip_sql {
   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)',
+  );
+  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
diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm
index 2b6dc6d..1b82e0e 100644
--- a/FS/FS/Schema.pm
+++ b/FS/FS/Schema.pm
@@ -6682,11 +6682,12 @@ sub tables_hashref {
         'zonetype',       'char',    '',     1,       '', '',
         'technology',     'int',     '',     '',      '', '',
         'spectrum',       'int',     'NULL', '',      '', '',
-        'servicetype',    'char',    '',     '12',    '', '',
         'adv_speed_up',   'decimal', '',     '10,3', '0', '',
         'adv_speed_down', 'decimal', '',     '10,3', '0', '',
         'cir_speed_up',   'decimal', '',     '10,3', '0', '',
         'cir_speed_down', 'decimal', '',     '10,3', '0', '',
+        'is_broadband',   'char',    'NULL', 1,       '', '',
+        'is_voice',       'char',    'NULL', 1,       '', '',
         'is_consumer',    'char',    'NULL', 1,       '', '',
         'is_business',    'char',    'NULL', 1,       '', '',
         'active_date',    @date_type,                 '', '',
@@ -6727,10 +6728,9 @@ sub tables_hashref {
         'zonenum',        'int',     '',     '',      '', '',
         'latitude',       'decimal', '',     '10,7',  '', '', 
         'longitude',      'decimal', '',     '10,7',  '', '', 
-        'sequence',       'int',     '',     '',      '', '',
       ],
       'primary_key' => 'vertexnum',
-      'unique' => [ [ 'zonenum', 'sequence' ] ],
+      'unique' => [ ],
       'index'  => [ ],
       'foreign_keys' => [
                           { columns     => [ 'zonenum' ],
diff --git a/FS/FS/deploy_zone.pm b/FS/FS/deploy_zone.pm
index 227a022..16f59c8 100644
--- a/FS/FS/deploy_zone.pm
+++ b/FS/FS/deploy_zone.pm
@@ -65,10 +65,6 @@ The FCC technology code for the type of service available.
 
 For mobile service zones, the FCC code for the RF band.
 
-=item servicetype
-
-"broadband" or "voice"
-
 =item adv_speed_up
 
 For broadband, the advertised upstream bandwidth in the zone.  If multiple
@@ -97,6 +93,14 @@ type of service is sold.
 'Y' if this service is sold to business or institutional use.  Not mutually
 exclusive with is_consumer.
 
+=item is_broadband
+
+'Y' if this service includes broadband Internet.
+
+=item is_voice
+
+'Y' if this service includes voice communication.
+
 =item active_date
 
 The date this zone became active.
@@ -180,25 +184,30 @@ sub check {
     || $self->ut_textn('description')
     || $self->ut_number('agentnum')
     || $self->ut_foreign_key('agentnum', 'agent', 'agentnum')
-    || $self->ut_alphan('dbaname')
+    || $self->ut_textn('dbaname')
     || $self->ut_enum('zonetype', [ 'B', 'P' ])
     || $self->ut_number('technology')
     || $self->ut_numbern('spectrum')
-    || $self->ut_enum('servicetype', [ 'broadband', 'voice' ])
     || $self->ut_decimaln('adv_speed_up', 3)
     || $self->ut_decimaln('adv_speed_down', 3)
     || $self->ut_decimaln('cir_speed_up', 3)
     || $self->ut_decimaln('cir_speed_down', 3)
     || $self->ut_flag('is_consumer')
     || $self->ut_flag('is_business')
+    || $self->ut_flag('is_broadband')
+    || $self->ut_flag('is_voice')
     || $self->ut_numbern('active_date')
     || $self->ut_numbern('expire_date')
   ;
   return $error if $error;
 
   foreach(qw(adv_speed_down adv_speed_up cir_speed_down cir_speed_up)) {
-    if (!$self->get($_)) {
-      $self->set($_, 0);
+    if ($self->get('is_broadband')) {
+      if (!$self->get($_)) {
+        $self->set($_, 0);
+      }
+    } else {
+      $self->set($_, '');
     }
   }
   if (!$self->get('active_date')) {
@@ -226,7 +235,35 @@ sub element_table {
   }
 }
 
-=back
+=item deploy_zone_block
+
+Returns the census block records in this zone, in order by census block
+number.  Only appropriate to block-type zones.
+
+=item deploy_zone_vertex
+
+Returns the vertex records for this zone, in order by sequence number.  Only
+appropriate to polygon-type zones.
+
+=cut
+
+sub deploy_zone_block {
+  my $self = shift;
+  qsearch({
+      table     => 'deploy_zone_block',
+      hashref   => { zonenum => $self->zonenum },
+      order_by  => ' ORDER BY censusblock',
+  });
+}
+
+sub deploy_zone_vertex {
+  my $self = shift;
+  qsearch({
+      table     => 'deploy_zone_vertex',
+      hashref   => { zonenum => $self->zonenum },
+      order_by  => ' ORDER BY vertexnum',
+  });
+}
 
 =head1 BUGS
 
diff --git a/FS/FS/deploy_zone_vertex.pm b/FS/FS/deploy_zone_vertex.pm
index a25bfde..078b326 100644
--- a/FS/FS/deploy_zone_vertex.pm
+++ b/FS/FS/deploy_zone_vertex.pm
@@ -47,10 +47,6 @@ Latitude, as a decimal; positive values are north of the Equator.
 
 Longitude, as a decimal; positive values are east of Greenwich.
 
-=item sequence
-
-The ordinal position of this vertex, starting with zero.
-
 =back
 
 =head1 METHODS
@@ -104,7 +100,6 @@ sub check {
     || $self->ut_number('zonenum')
     || $self->ut_coord('latitude')
     || $self->ut_coord('longitude')
-    || $self->ut_number('sequence')
   ;
   return $error if $error;
 
diff --git a/FS/FS/part_pkg_fcc_option.pm b/FS/FS/part_pkg_fcc_option.pm
index a090b96..5c78e5f 100644
--- a/FS/FS/part_pkg_fcc_option.pm
+++ b/FS/FS/part_pkg_fcc_option.pm
@@ -139,6 +139,23 @@ tie our %technology_labels, 'Tie::IxHash',  (
   0  => 'Other'
 );
 
+tie our %spectrum_labels, 'Tie::IxHash', (
+  90 => '700 MHz Band',
+  91 => 'Cellular Band',
+  92 => 'Specialized Mobile Radio (SMR) Band',
+  93 => 'Advanced Wireless Services (AWS) 1 Band',
+  94 => 'Broadband Personal Communications Service (PCS) Band',
+  95 => 'Wireless Communications Service (WCS) Band',
+  96 => 'Broadband Radio Service/Educational Broadband Service Band',
+  97 => 'Satellite (e.g. L-band, Big LEO, Little LEO)',
+  98 => 'Unlicensed (including broadcast television “white spaces”) Bands',
+  99 => '600 MHz',
+  100 => 'H Block',
+  101 => 'Advanced Wireless Services (AWS) 3 Band',
+  102 => 'Advanced Wireless Services (AWS) 4 Band',
+  103 => 'Other',
+);
+
 sub media_types {
   Storable::dclone(\%media_types);
 }
@@ -147,6 +164,10 @@ sub technology_labels {
   Storable::dclone(\%technology_labels);
 }
 
+sub spectrum_labels {
+  Storable::dclone(\%spectrum_labels);
+}
+
 =head1 BUGS
 
 =head1 SEE ALSO