Merge branch 'master' of git.freeside.biz:/home/git/freeside

6 files changed, 49 insertions, 17 deletions

diff --git a/FS/FS/Conf.pm b/FS/FS/Conf.pm
index 321ba0b89..b72cf0313 100644
--- a/FS/FS/Conf.pm
+++ b/FS/FS/Conf.pm
@@ -3303,7 +3303,7 @@ and customer address. Include units.',
   {
     'key'         => 'cust_pkg-show_fcc_voice_grade_equivalent',
     'section'     => 'UI',
-    'description' => "Show a field on package definitions for assigning a DS0 equivalency number suitable for use on FCC form 477.",
+    'description' => "Show fields on package definitions for FCC Form 477 classification",
     'type'        => 'checkbox',
   },
 
diff --git a/FS/FS/Mason.pm b/FS/FS/Mason.pm
index b3c2d4a5b..39c7dfdc6 100644
--- a/FS/FS/Mason.pm
+++ b/FS/FS/Mason.pm
@@ -93,7 +93,7 @@ if ( -e $addl_handler_use_file ) {
   use Spreadsheet::WriteExcel::Utility;
   use OLE::Storage_Lite;
   use Excel::Writer::XLSX;
-  use Excel::Writer::XLSX::Utility;
+  #use Excel::Writer::XLSX::Utility; #redundant with above
 
   use Business::CreditCard 0.30; #for mask-aware cardtype()
   use NetAddr::IP;
diff --git a/FS/FS/Report/FCC_477.pm b/FS/FS/Report/FCC_477.pm
index 4c94fff2e..49bb8a852 100644
--- a/FS/FS/Report/FCC_477.pm
+++ b/FS/FS/Report/FCC_477.pm
@@ -45,8 +45,8 @@ Documentation.
 );
 
 @technology = (
-  'Asymetric xDSL',
-  'Symetric xDSL',
+  'Asymmetric xDSL',
+  'Symmetric xDSL',
   'Other Wireline',
   'Cable Modem',
   'Optical Carrier',
diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm
index b1e7a9c5d..37bba68cb 100644
--- a/FS/FS/Schema.pm
+++ b/FS/FS/Schema.pm
@@ -1912,6 +1912,7 @@ sub tables_hashref {
         'credit_weight', 'real',    'NULL', '', '', '',
         'agentnum',      'int',     'NULL', '', '', '', 
         'fcc_ds0s',      'int',     'NULL', '', '', '', 
+        'fcc_voip_class','char',    'NULL',  1, '', '',
         'no_auto',          'char', 'NULL',  1, '', '', 
         'recur_show_zero',  'char', 'NULL',  1, '', '',
         'setup_show_zero',  'char', 'NULL',  1, '', '',
diff --git a/FS/FS/cust_pkg.pm b/FS/FS/cust_pkg.pm
index ea29a2c68..c34eb43b5 100644
--- a/FS/FS/cust_pkg.pm
+++ b/FS/FS/cust_pkg.pm
@@ -3317,7 +3317,12 @@ specifies the user for agent virtualization
 
 =item fcc_line
 
- boolean selects packages containing fcc form 477 telco lines
+boolean; if true, returns only packages with more than 0 FCC phone lines.
+
+=item state, country
+
+Limit to packages with a service location in the specified state and country.
+For FCC 477 reporting, mostly.
 
 =back
 
@@ -3491,8 +3496,8 @@ sub search {
 
   if ( exists($params->{'censustract'}) ) {
     $params->{'censustract'} =~ /^([.\d]*)$/;
-    my $censustract = "cust_main.censustract = '$1'";
-    $censustract .= ' OR cust_main.censustract is NULL' unless $1;
+    my $censustract = "cust_location.censustract = '$1'";
+    $censustract .= ' OR cust_location.censustract is NULL' unless $1;
     push @where,  "( $censustract )";
   }
 
@@ -3504,10 +3509,22 @@ sub search {
      )
   {
     if ($1) {
-      push @where, "cust_main.censustract LIKE '$1%'";
+      push @where, "cust_location.censustract LIKE '$1%'";
     } else {
       push @where,
-        "( cust_main.censustract = '' OR cust_main.censustract IS NULL )";
+        "( cust_location.censustract = '' OR cust_location.censustract IS NULL )";
+    }
+  }
+
+  ###
+  # parse country/state
+  ###
+  for (qw(state country)) { # parsing rules are the same for these
+  if ( exists($params->{$_}) 
+    && uc($params->{$_}) =~ /^([A-Z]{2})$/ )
+    {
+      # XXX post-2.3 only--before that, state/country may be in cust_main
+      push @where, "cust_location.$_ = '$1'";
     }
   }
 
@@ -3635,7 +3652,8 @@ sub search {
 
   my $addl_from = 'LEFT JOIN cust_main USING ( custnum  ) '.
                   'LEFT JOIN part_pkg  USING ( pkgpart  ) '.
-                  'LEFT JOIN pkg_class ON ( part_pkg.classnum = pkg_class.classnum ) ';
+                  'LEFT JOIN pkg_class ON ( part_pkg.classnum = pkg_class.classnum ) '.
+                  'LEFT JOIN cust_location USING ( locationnum ) ';
 
   my $select;
   my $count_query;
@@ -3644,13 +3662,6 @@ sub search {
 
     $select = "DISTINCT substr($zip,1,5) as zip";
     $orderby = "ORDER BY substr($zip,1,5)";
-    $addl_from .= 'LEFT JOIN cust_location ON (
-                     cust_location.locationnum = COALESCE(
-                                                   cust_pkg.locationnum,
-                                                   cust_main.ship_locationnum,
-                                                   cust_main.bill_locationnum
-                                                 )
-                                              )';
     $count_query = "SELECT COUNT( DISTINCT substr($zip,1,5) )";
   } else {
     $select = join(', ',
diff --git a/FS/FS/part_pkg.pm b/FS/FS/part_pkg.pm
index 061001bdc..91bcdc5b5 100644
--- a/FS/FS/part_pkg.pm
+++ b/FS/FS/part_pkg.pm
@@ -103,6 +103,9 @@ inherits from FS::Record.  The following fields are currently supported:
 
 =item fcc_ds0s - Optional DS0 equivalency number for FCC form 477
 
+=item fcc_voip_class - Which column of FCC form 477 part II.B this package 
+belongs in.
+
 =item successor - Foreign key for the part_pkg that replaced this record.
 If this record is not obsolete, will be null.
 
@@ -622,6 +625,7 @@ sub check {
            : $self->ut_agentnum_acl('agentnum', \@null_agentnum_right)
        )
     || $self->ut_numbern('fcc_ds0s')
+    || $self->ut_numbern('fcc_voip_class')
     || $self->ut_foreign_keyn('successor', 'part_pkg', 'pkgpart')
     || $self->ut_foreign_keyn('family_pkgpart', 'part_pkg', 'pkgpart')
     || $self->SUPER::check
@@ -1592,6 +1596,22 @@ sub _upgrade_data { # class method
         }
   }
 
+  # set any package with FCC voice lines to the "VoIP with broadband" category
+  # for backward compatibility
+  my $journal = 'part_pkg_fcc_voip_class';
+  if (!FS::upgrade_journal->is_done($journal)) {
+    @part_pkg = qsearch('part_pkg', { 
+        fcc_ds0s        => { op => '>', value => 0 },
+        fcc_voip_class  => ''
+    });
+    foreach my $part_pkg (@part_pkg) {
+      $part_pkg->set(fcc_voip_class => 2);
+      my $error = $part_pkg->replace;
+      die $error if $error;
+    }
+    FS::upgrade_journal->set_done($journal);
+  }
+
 }
 
 =item curuser_pkgs_sql