improved fcc 477 report #7783

8 files changed, 147 insertions, 0 deletions

diff --git a/FS/FS/Conf.pm b/FS/FS/Conf.pm
index 7f914901d..6658704cc 100644
--- a/FS/FS/Conf.pm
+++ b/FS/FS/Conf.pm
@@ -2369,6 +2369,13 @@ worry that config_items is freeside-specific and icky.
   },
 
   {
+    'key'         => 'cust_pkg-show_fcc_voice_grade_equivalent',
+    'section'     => 'UI',
+    'description' => "Show a field on package definitions for assigning a DSO equivalency number suitable for use on FCC form 477.",
+    'type'        => 'checkbox',
+  },
+
+  {
     'key'         => 'svc_acct-edit_uid',
     'section'     => 'shell',
     'description' => 'Allow UID editing.',
diff --git a/FS/FS/Mason.pm b/FS/FS/Mason.pm
index 155a80966..a0a5b4b34 100644
--- a/FS/FS/Mason.pm
+++ b/FS/FS/Mason.pm
@@ -121,6 +121,7 @@ if ( -e $addl_handler_use_file ) {
   use FS::Msgcat qw(gettext geterror);
   use FS::Misc qw( send_email send_fax states_hash counties state_label );
   use FS::Misc::eps2png qw( eps2png );
+  use FS::Report::FCC_477;
   use FS::Report::Table::Monthly;
   use FS::TicketSystem;
   use FS::Tron qw( tron_lint );
diff --git a/FS/FS/Report/FCC_477.pm b/FS/FS/Report/FCC_477.pm
new file mode 100644
index 000000000..518b9f0e1
--- /dev/null
+++ b/FS/FS/Report/FCC_477.pm
@@ -0,0 +1,90 @@
+package FS::Report::FCC_477;
+
+use strict;
+use vars qw( @ISA @upload @download @technology @part2aoption @part2boption );
+use FS::Report;
+
+@ISA = qw( FS::Report );
+
+=head1 NAME
+
+FS::Report::FCC_477 - Routines for FCC Form 477 reports
+
+=head1 SYNOPSIS
+
+=head1 BUGS
+
+Documentation.
+
+=head1 SEE ALSO
+
+=cut
+
+@upload = qw(
+ <200kpbs
+ 200-768kpbs
+ 768kbps-1.5mbps
+ 1.5-3mpbs
+ 3-6mbps
+ 6-10mbps
+ 10-25mbps
+ 25-100mbps
+ >100bmps
+);
+
+@download = qw(
+ 200-768kpbs
+ 768kbps-1.5mbps
+ 1.5-3mpbs
+ 3-6mbps
+ 6-10mbps
+ 10-25mbps
+ 25-100mbps
+ >100bmps
+);
+
+@technology = (
+  'Asymetric xDSL',
+  'Symetric xDSL',
+  'Other Wireline',
+  'Cable Modem',
+  'Optical Carrier',
+  'Satellite',
+  'Terrestrial Fixed Wireless',
+  'Terrestrial Mobile Wireless',
+  'Electric Power Line',
+  'Other Technology',
+);
+
+@part2aoption = (
+ 'LD carrier',
+ 'owned loops',
+ 'unswitched UNE loops',
+ 'UNE-P',
+ 'UNE-P replacement',
+ 'FTTP',
+ 'coax',
+ 'wireless',
+);
+
+@part2boption = (
+ 'nomadic',
+ 'copper',
+ 'FTTP',
+ 'coax',
+ 'wireless',
+ 'other broadband',
+);
+
+sub parse_technology_option {
+  my $cgi = shift;
+  my @result = ();
+  my $i = 0;
+  for (my $i = 0; $i < scalar(@technology); $i++) {
+    my $value = $cgi->param("part1_technology_option_$i"); #lame
+    push @result, $value =~ /^\d+$/ ? $value : 0;
+  }
+  return (@result);
+}
+
+1;
diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm
index 78f3e44a9..3cc853f20 100644
--- a/FS/FS/Schema.pm
+++ b/FS/FS/Schema.pm
@@ -1298,6 +1298,7 @@ sub tables_hashref {
         'pay_weight',    'real',    'NULL', '', '', '',
         'credit_weight', 'real',    'NULL', '', '', '',
         'agentnum',      'int',     'NULL', '', '', '', 
+        'fcc_ds0s',      'int',     'NULL', '', '', '', 
 
       ],
       'primary_key' => 'pkgpart',
diff --git a/FS/FS/cust_pkg.pm b/FS/FS/cust_pkg.pm
index 89eadd599..80c8a32fb 100644
--- a/FS/FS/cust_pkg.pm
+++ b/FS/FS/cust_pkg.pm
@@ -2381,6 +2381,10 @@ a value suited to passing to FS::UI::Web::cust_header
 
 specifies the user for agent virtualization
 
+=item fcc_line
+
+ boolean selects packages containing fcc form 477 telco lines
+
 =back
 
 =cut
@@ -2502,6 +2506,12 @@ sub search {
   push @where,  "part_pkg.custom = 'Y'" if $params->{custom};
 
   ###
+  # parse fcc_line
+  ###
+
+  push @where,  "part_pkg.fcc_ds0s > 0" if $params->{fcc_line};
+
+  ###
   # parse censustract
   ###
 
@@ -2648,6 +2658,35 @@ sub search {
 
 }
 
+=item fcc_477_count
+
+Returns a list of two package counts.  The first is a count of packages
+based on the supplied criteria and the second is the count of residential
+packages with those same criteria.  Criteria are specified as in the search
+method.
+
+=cut
+
+sub fcc_477_count {
+  my ($class, $params) = @_;
+
+  my $sql_query = $class->search( $params );
+
+  my $count_sql = delete($sql_query->{'count_query'});
+  $count_sql =~ s/ FROM/,count(CASE WHEN cust_main.company IS NULL OR cust_main.company = '' THEN 1 END) FROM/
+    or die "couldn't parse count_sql";
+
+  my $count_sth = dbh->prepare($count_sql)
+    or die "Error preparing $count_sql: ". dbh->errstr;
+  $count_sth->execute
+    or die "Error executing $count_sql: ". $count_sth->errstr;
+  my $count_arrayref = $count_sth->fetchrow_arrayref;
+
+  return ( @$count_arrayref );
+
+}
+
+
 =item location_sql
 
 Returns a list: the first item is an SQL fragment identifying matching 
diff --git a/FS/FS/part_pkg.pm b/FS/FS/part_pkg.pm
index 276889d62..95bc56a13 100644
--- a/FS/FS/part_pkg.pm
+++ b/FS/FS/part_pkg.pm
@@ -98,6 +98,8 @@ inherits from FS::Record.  The following fields are currently supported:
 
 =item agentnum - Optional agentnum (see L<FS::agent>)
 
+=item fcc_ds0s - Optional DS0 equivalency number for FCC form 477
+
 =back
 
 =head1 METHODS
@@ -477,6 +479,7 @@ sub check {
            ? $self->ut_foreign_keyn('agentnum', 'agent', 'agentnum' )
            : $self->ut_agentnum_acl('agentnum', \@null_agentnum_right)
        )
+    || $self->ut_numbern('fcc_ds0s')
     || $self->SUPER::check
   ;
   return $error if $error;
diff --git a/FS/MANIFEST b/FS/MANIFEST
index 5bcb8534f..e348b8bf7 100644
--- a/FS/MANIFEST
+++ b/FS/MANIFEST
@@ -39,6 +39,7 @@ FS/Daemon.pm
 FS/Misc.pm
 FS/Record.pm
 FS/Report.pm
+FS/Report/FCC_477.pm
 FS/Report/Table.pm
 FS/Report/Table/Monthly.pm
 FS/SearchCache.pm
diff --git a/FS/t/Report-FCC_477.t b/FS/t/Report-FCC_477.t
new file mode 100644
index 000000000..b7359aa83
--- /dev/null
+++ b/FS/t/Report-FCC_477.t
@@ -0,0 +1,5 @@
+BEGIN { $| = 1; print "1..1\n" }
+END {print "not ok 1\n" unless $loaded;}
+use FS::Report::FCC_477;
+$loaded=1;
+print "ok 1\n";