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

10 files changed, 406 insertions, 46 deletions

diff --git a/FS/FS/Conf.pm b/FS/FS/Conf.pm
index 581333291..c0955b115 100644
--- a/FS/FS/Conf.pm
+++ b/FS/FS/Conf.pm
@@ -3465,13 +3465,6 @@ and customer address. Include units.',
   },
 
   {
-    'key'         => 'cust_pkg-show_fcc_voice_grade_equivalent',
-    'section'     => 'UI',
-    'description' => "Show fields on package definitions for FCC Form 477 classification",
-    'type'        => 'checkbox',
-  },
-
-  {
     'key'         => 'cust_pkg-large_pkg_size',
     'section'     => 'UI',
     'description' => "In customer view, summarize packages with more than this many services.  Set to zero to never summarize packages.",
@@ -3486,6 +3479,13 @@ and customer address. Include units.',
   },
 
   {
+    'key'         => 'part_pkg-show_fcc_options',
+    'section'     => 'UI',
+    'description' => "Show fields on package definitions for FCC Form 477 classification",
+    'type'        => 'checkbox',
+  },
+
+  {
     'key'         => 'svc_acct-edit_uid',
     'section'     => 'shell',
     'description' => 'Allow UID editing.',
@@ -5773,6 +5773,13 @@ and customer address. Include units.',
                      ],
   },
 
+  {
+    'key'         => 'old_fcc_report',
+    'section'     => '',
+    'description' => 'Use the old (pre-2014) FCC Form 477 report format.',
+    'type'        => 'checkbox',
+  },
+
   { key => "apacheroot", section => "deprecated", description => "<b>DEPRECATED</b>", type => "text" },
   { key => "apachemachine", section => "deprecated", description => "<b>DEPRECATED</b>", type => "text" },
   { key => "apachemachines", section => "deprecated", description => "<b>DEPRECATED</b>", type => "text" },
diff --git a/FS/FS/Mason.pm b/FS/FS/Mason.pm
index b7aa35543..1ae60edef 100644
--- a/FS/FS/Mason.pm
+++ b/FS/FS/Mason.pm
@@ -383,6 +383,8 @@ if ( -e $addl_handler_use_file ) {
   use FS::export_batch;
   use FS::export_batch_item;
   use FS::part_pkg_fcc_option;
+  use FS::state;
+  use FS::state;
   # Sammath Naur
 
   if ( $FS::Mason::addl_handler_use ) {
diff --git a/FS/FS/Report/FCC_477.pm b/FS/FS/Report/FCC_477.pm
index 9c038420b..79f00e371 100644
--- a/FS/FS/Report/FCC_477.pm
+++ b/FS/FS/Report/FCC_477.pm
@@ -81,6 +81,7 @@ Documentation.
 );
 
 #from the select at http://www.ffiec.gov/census/default.aspx
+#though this is now in the database, also
 %states = (
   '01' => 'ALABAMA (AL)',
   '02' => 'ALASKA (AK)',
@@ -204,10 +205,22 @@ sub statenum2state {
   $states{$num};
 }
 
+=head1 THE "NEW" REPORT (October 2014 and later)
+
+=head2 METHODS
+
+=over 4
+
+=cut
+
 sub join_optionnames {
   join(' ', map { join_optionname($_) } @_);
 }
 
+sub join_optionnames_int {
+  join(' ', map { join_optionname_int($_) } @_);
+}
+
 sub join_optionname {
   # Returns a FROM phrase to join a specific option into the query (via 
   # part_pkg).  The option value will appear as a field with the same name
@@ -218,6 +231,17 @@ sub join_optionname {
     " ON (part_pkg.pkgpart = t_$name.pkgpart)";
 }
 
+sub join_optionname_int {
+  # Returns a FROM phrase to join a specific option into the query (via 
+  # part_pkg) and cast it to integer..  Note this does not convert nulls
+  # to zero.
+  my $name = shift;
+  "LEFT JOIN (SELECT pkgpart, CAST(optionvalue AS int) AS $name
+   FROM part_pkg_fcc_option".
+    " WHERE fccoptionname = '$name') AS t_$name".
+    " ON (part_pkg.pkgpart = t_$name.pkgpart)";
+}
+
 sub active_on {
   # Returns a condition to limit packages to those that were setup before a 
   # certain date, and not canceled before that date.
@@ -230,7 +254,7 @@ sub active_on {
 }
 
 sub is_fixed_broadband {
-  "is_broadband = '1' AND technology::integer IN(".join(',',
+  "is_broadband::int = 1 AND technology::int IN(".join(',',
     10, 11, 12, 20, 30, 40, 41, 42, 50, 60, 70, 90, 0
   ).")";
 }
@@ -238,8 +262,19 @@ sub is_fixed_broadband {
 =item part6 OPTIONS
 
 Returns Part 6 of the 2014 FCC 477 data, as an arrayref of arrayrefs.
-OPTIONS may contain "date" => a timestamp to run the report as of that
-date.
+OPTIONS may contain:
+- date: a timestamp value to count active packages as of that date
+- agentnum: limit to customers of that agent
+
+Part 6 is the broadband subscription detail report.  Columns of the 
+report are:
+- census tract
+- technology code
+- downstream speed
+- upstream speed
+(the above columns form a key)
+- number of subscriptions
+- number of consumer-grade subscriptions
 
 =cut
 
@@ -247,6 +282,7 @@ sub part6 {
   my $class = shift;
   my %opt = shift;
   my $date = $opt{date} || time;
+  my $agentnum = $opt{agentnum};
 
   my @select = (
     'cust_location.censustract',
@@ -258,18 +294,20 @@ sub part6 {
   );
   my $from =
     'cust_pkg
-      JOIN cust_location USING (locationnum)
+      JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
+      JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
       JOIN part_pkg USING (pkgpart) '.
-      join_optionnames(qw(
+      join_optionnames_int(qw(
         is_broadband technology 
-        broadband_downstream broadband_upstream
         is_consumer
-        ))
+        )).
+      join_optionnames(qw(broadband_downstream broadband_upstream))
   ;
   my @where = (
     active_on($date),
     is_fixed_broadband()
   );
+  push @where, "cust_main.agentnum = $agentnum" if $agentnum;
   my $group_by = 'cust_location.censustract, technology, '.
                    'broadband_downstream, broadband_upstream ';
   my $order_by = $group_by;
@@ -287,7 +325,24 @@ sub part6 {
 
 =item part9 OPTIONS
 
-Returns Part 9 of the 2014 FCC 477 data, as above.
+Returns Part 9 of the 2014 FCC 477 data.  Part 9 is the Local Exchange 
+Telephone Subscription report.  Columns are:
+
+- state FIPS code (key)
+- wholesale switched voice lines
+- wholesale unswitched local loops
+- end-user total lines
+- end-user lines sold in a package with broadband
+- consumer-grade lines where you are not the long-distance carrier
+- consumer-grade lines where the carrier IS the long-distance carrier
+- business-grade lines where you are not the long-distance carrier
+- business-grade lines where the carrier IS the long-distance carrier
+- end-user lines where you own the local loop facility
+- end-user lines where you lease an unswitched local loop from a LEC
+- end-user lines resold from another carrier
+- end-user lines provided over fiber to the premises
+- end-user lines provided over coaxial
+- end-user lines provided over fixed wireless
 
 =cut
 
@@ -295,39 +350,44 @@ sub part9 {
   my $class = shift;
   my %opt = shift;
   my $date = $opt{date} || time;
+  my $agentnum = $opt{agentnum};
 
   my @select = (
-    "cust_location.state",
-    "SUM(COALESCE(phone_vges::int,0))",
-    "SUM(COALESCE(phone_circuits::int,0))",
-    "SUM(COALESCE(phone_lines::int,0))",
-    "SUM(CASE WHEN is_broadband = '1' THEN phone_lines::int ELSE 0 END)",
-    "SUM(CASE WHEN is_consumer = '1' AND is_longdistance IS NULL THEN phone_lines::int ELSE 0 END)",
-    "SUM(CASE WHEN is_consumer = '1' AND is_longdistance = '1' THEN phone_lines::int ELSE 0 END)",
-    "SUM(CASE WHEN is_consumer IS NULL AND is_longdistance IS NULL THEN phone_lines::int ELSE 0 END)",
-    "SUM(CASE WHEN is_consumer IS NULL AND is_longdistance = '1' THEN phone_lines::int ELSE 0 END)",
-    "SUM(CASE WHEN phone_localloop = 'owned' THEN phone_lines::int ELSE 0 END)",
-    "SUM(CASE WHEN phone_localloop = 'leased' THEN phone_lines::int ELSE 0 END)",
-    "SUM(CASE WHEN phone_localloop = 'resale' THEN phone_lines::int ELSE 0 END)",
-    "SUM(CASE WHEN media = 'Fiber' THEN phone_lines::int ELSE 0 END)",
-    "SUM(CASE WHEN media = 'Cable Modem' THEN phone_lines::int ELSE 0 END)",
-    "SUM(CASE WHEN media = 'Fixed Wireless' THEN phone_lines::int ELSE 0 END)",
+    "state.fips",
+    "SUM(phone_vges)",
+    "SUM(phone_circuits)",
+    "SUM(phone_lines)",
+    "SUM(CASE WHEN is_broadband = 1 THEN phone_lines ELSE 0 END)",
+    "SUM(CASE WHEN is_consumer = 1 AND is_longdistance IS NULL THEN phone_lines ELSE 0 END)",
+    "SUM(CASE WHEN is_consumer = 1 AND is_longdistance = 1 THEN phone_lines ELSE 0 END)",
+    "SUM(CASE WHEN is_consumer IS NULL AND is_longdistance IS NULL THEN phone_lines ELSE 0 END)",
+    "SUM(CASE WHEN is_consumer IS NULL AND is_longdistance = 1 THEN phone_lines ELSE 0 END)",
+    "SUM(CASE WHEN phone_localloop = 'owned' THEN phone_lines ELSE 0 END)",
+    "SUM(CASE WHEN phone_localloop = 'leased' THEN phone_lines ELSE 0 END)",
+    "SUM(CASE WHEN phone_localloop = 'resale' THEN phone_lines ELSE 0 END)",
+    "SUM(CASE WHEN media = 'Fiber' THEN phone_lines ELSE 0 END)",
+    "SUM(CASE WHEN media = 'Cable Modem' THEN phone_lines ELSE 0 END)",
+    "SUM(CASE WHEN media = 'Fixed Wireless' THEN phone_lines ELSE 0 END)",
   );
   my $from =
     'cust_pkg
-      JOIN cust_location USING (locationnum)
+      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(qw(
-        is_phone is_broadband media
+      join_optionnames_int(qw(
+        is_phone is_broadband
         phone_vges phone_circuits phone_lines
-        is_consumer is_longdistance phone_localloop 
-        ))
+        is_consumer is_longdistance
+        )).
+      join_optionnames('media', 'phone_localloop')
   ;
   my @where = (
     active_on($date),
-    "is_phone::int = 1",
+    "is_phone = 1",
   );
-  my $group_by = 'cust_location.state';
+  push @where, "cust_main.agentnum = $agentnum" if $agentnum;
+  my $group_by = 'state.fips';
   my $order_by = $group_by;
 
   my $statement = "SELECT ".join(', ', @select) . "
@@ -341,5 +401,106 @@ sub part9 {
   dbh->selectall_arrayref($statement);
 }
 
+sub part10 {
+  my $class = shift;
+  my %opt = shift;
+  my $date = $opt{date} || time;
+  my $agentnum = $opt{agentnum};
+
+  my @select = (
+    "state.fips",
+    # OTT, OTT + consumer
+    "SUM(CASE WHEN (voip_lastmile IS NULL) THEN 1 ELSE 0 END)",
+    "SUM(CASE WHEN (voip_lastmile IS NULL AND is_consumer = 1) THEN 1 ELSE 0 END)",
+    # non-OTT: total, consumer, broadband bundle, media types
+    "SUM(CASE WHEN (voip_lastmile = 1) THEN 1 ELSE 0 END)",
+    "SUM(CASE WHEN (voip_lastmile = 1 AND is_consumer = 1) THEN 1 ELSE 0 END)",
+    "SUM(CASE WHEN (voip_lastmile = 1 AND is_broadband = 1) THEN 1 ELSE 0 END)",
+    "SUM(CASE WHEN (voip_lastmile = 1 AND media = 'Copper') THEN 1 ELSE 0 END)",
+    "SUM(CASE WHEN (voip_lastmile = 1 AND media = 'Cable Modem') THEN 1 ELSE 0 END)",
+    "SUM(CASE WHEN (voip_lastmile = 1 AND media = 'Fiber') THEN 1 ELSE 0 END)",
+    "SUM(CASE WHEN (voip_lastmile = 1 AND media = 'Fixed Wireless') THEN 1 ELSE 0 END)",
+    "SUM(CASE WHEN (voip_lastmile = 1 AND media NOT IN('Copper', 'Fiber', 'Cable Modem', 'Fixed Wireless') ) THEN 1 ELSE 0 END)",
+  );
+
+  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_voip is_broadband is_consumer voip_lastmile)
+      ).
+      join_optionnames('media')
+  ;
+  my @where = (
+    active_on($date),
+    "is_voip = 1",
+  );
+  push @where, "cust_main.agentnum = $agentnum" if $agentnum;
+  my $group_by = 'state.fips';
+  my $order_by = $group_by;
+
+  my $statement = "SELECT ".join(', ', @select) . "
+  FROM $from
+  WHERE ".join(' AND ', @where)."
+  GROUP BY $group_by
+  ORDER BY $order_by
+  ";
+
+  warn $statement if $DEBUG;
+  dbh->selectall_arrayref($statement);
+}
+
+=item part11 OPTIONS
+
+Returns part 11 (voice subscription detail), as above.
+
+=cut
+
+sub part11 {
+  my $class = shift;
+  my %opt = shift;
+  my $date = $opt{date} || time;
+  my $agentnum = $opt{agentnum};
+
+  my @select = (
+    'cust_location.censustract',
+    # VoIP indicator (0 for non-VoIP, 1 for VoIP)
+    'COALESCE(is_voip, 0)',
+    # number of lines/subscriptions
+    'SUM(CASE WHEN is_voip = 1 THEN 1 ELSE phone_lines END)',
+    # consumer grade lines/subscriptions
+    'SUM(CASE WHEN is_consumer = 1 THEN ( CASE WHEN is_voip = 1 THEN 1 ELSE phone_lines END) ELSE 0 END)'
+  );
+
+  my $from = 'cust_pkg
+    JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
+    JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
+    JOIN part_pkg USING (pkgpart) '.
+    join_optionnames_int(qw(
+      is_phone is_voip is_consumer phone_lines
+      ))
+  ;
+
+  my @where = (
+    active_on($date),
+    "(is_voip = 1 OR is_phone = 1)",
+  );
+  push @where, "cust_main.agentnum = $agentnum" if $agentnum;
+  my $group_by = 'cust_location.censustract, COALESCE(is_voip, 0)';
+  my $order_by = $group_by;
+
+  my $statement = "SELECT ".join(', ', @select) . "
+  FROM $from
+  WHERE ".join(' AND ', @where)."
+  GROUP BY $group_by
+  ORDER BY $order_by
+  ";
+
+  warn $statement if $DEBUG;
+  dbh->selectall_arrayref($statement);
+}
 
 1;
diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm
index 1c9c4a200..40248ddfc 100644
--- a/FS/FS/Schema.pm
+++ b/FS/FS/Schema.pm
@@ -6643,6 +6643,20 @@ sub tables_hashref {
                         ],
     },
 
+    # lookup table for states, similar to msa and lata
+    'state' => {
+      'columns' => [
+        'statenum', 'int',  '', '', '', '', 
+        'country',  'char', '',  2, '', '',
+        'state',    'char', '', $char_d, '', '', 
+        'fips',     'char', '',  3, '', '',
+      ],
+      'primary_key' => 'statenum',
+      'unique' => [ [ 'country', 'state' ], ],
+      'index' => [],
+    },
+
+
     # name type nullability length default local
 
     #'new_table' => {
diff --git a/FS/FS/Upgrade.pm b/FS/FS/Upgrade.pm
index 6785a1375..ce0e328fd 100644
--- a/FS/FS/Upgrade.pm
+++ b/FS/FS/Upgrade.pm
@@ -97,6 +97,22 @@ sub upgrade_config {
     $conf->touch('cust_main-enable_spouse');
     $conf->delete('cust_main-enable_spouse_birthdate');
   }
+
+  # renamed/repurposed
+  if ( $conf->exists('cust_pkg-show_fcc_voice_grade_equivalent') ) {
+    $conf->touch('part_pkg-show_fcc_options');
+    $conf->delete('cust_pkg-show_fcc_voice_grade_equivalent');
+    warn "
+You have FCC Form 477 package options enabled.
+
+Starting with the October 2014 filing date, the FCC has redesigned 
+Form 477 and introduced new service categories.  See bin/convert-477-options
+to update your package configuration for the new report.
+
+If you need to continue using the old Form 477 report, turn on the
+'old_fcc_report' configuration option.
+";
+  }
 }
 
 sub upgrade_overlimit_groups {
@@ -343,6 +359,9 @@ sub upgrade_data {
 
     #fix taxable line item links
     'cust_bill_pkg_tax_location' => [],
+
+    #populate state FIPS codes if not already done
+    'state' => [],
   ;
 
   \%hash;
diff --git a/FS/FS/cdr/cisco.pm b/FS/FS/cdr/cisco.pm
index 335d4832a..2da5a94e1 100644
--- a/FS/FS/cdr/cisco.pm
+++ b/FS/FS/cdr/cisco.pm
@@ -80,7 +80,7 @@ use Date::Parse;
 					 skip(2),	#finalCalledPartyNumberPartition
 							#lastRedirectDnPartition
 				       'billsec',	#duration
-					skip(22),	#origDeviceName
+					skip(23),	#origDeviceName
 							#destDeviceName
 							#origCallTerminationOnBehalfOf
 							#destCallTerminationOnBehalfOf
@@ -102,11 +102,7 @@ use Date::Parse;
 							#origMediaCap_Bandwidth
 							#destMediaCap_Bandwidth	
 							#authorizationCodeValue
-					sub { my ($cdr, $number) = @_;
-						if ($number){
-						$cdr->set('src',$number);
-						}
-						},	#outpulsedCallingPartyNumber
+					        	#outpulsedCallingPartyNumber
 					      skip(23), #outpulsedCalledPartyNumber
 							#origIpv4v6Addr	
 							#destIpv4v6Addr	
@@ -138,7 +134,6 @@ use Date::Parse;
 
 
 
-
 sub skip { map {''} (1..$_[0]) }
 
 1;
diff --git a/FS/FS/cust_pay.pm b/FS/FS/cust_pay.pm
index 153390b59..54b8e9002 100644
--- a/FS/FS/cust_pay.pm
+++ b/FS/FS/cust_pay.pm
@@ -11,6 +11,7 @@ use Business::CreditCard;
 use Text::Template;
 use FS::Misc::DateTime qw( parse_datetime ); #for batch_import
 use FS::Record qw( dbh qsearch qsearchs );
+use FS::UID qw( driver_name );
 use FS::CurrentUser;
 use FS::payby;
 use FS::cust_main_Mixin;
@@ -1050,9 +1051,10 @@ sub process_upgrade_paybatch {
   ###
   # migrate batchnums from the misused 'paybatch' field to 'batchnum'
   ###
+  my $text = (driver_name =~ /^mysql/i) ? 'char' : 'text';
   my $search = FS::Cursor->new( {
     'table'     => 'cust_pay',
-    'addl_from' => ' JOIN pay_batch ON cust_pay.paybatch = CONCAT(pay_batch.batchnum) ',
+    'addl_from' => " JOIN pay_batch ON cust_pay.paybatch = CAST(pay_batch.batchnum AS $text) ",
   } );
   while (my $cust_pay = $search->fetch) {
     $cust_pay->set('batchnum' => $cust_pay->paybatch);
diff --git a/FS/FS/part_export/northern_911.pm b/FS/FS/part_export/northern_911.pm
index 27f150cba..027a52d21 100644
--- a/FS/FS/part_export/northern_911.pm
+++ b/FS/FS/part_export/northern_911.pm
@@ -52,6 +52,7 @@ sub export_insert {
 
   my %location_hash = $svc_phone->location_hash;
   $location_hash{address1} =~ /^(\w+) +(.*)$/;
+
   my %customer = (
     'PHONE_NUMBER'        => $svc_phone->phonenum,
     'STREET_NUMBER'       => $1,
@@ -61,6 +62,31 @@ sub export_insert {
     'POSTAL_CODE_ZIP'     => $location_hash{zip},
     'OTHER_ADDRESS_INFO'  => $location_hash{address2},
   );
+  my $phone_name = $svc_phone->phone_name;
+  if ( $phone_name ) {
+    # could be a personal name or a business...
+    if ( $svc_phone->e911_class and
+        grep { $_ eq $svc_phone->e911_class }
+         ( 2, 4, 5, 6, 7, 0, 'A', 'D', 'E', 'K')
+       )
+    {
+      # one of the "Business" classes, Centrex, a payphone, or 
+      # VoIP Enterprise class
+      $customer{'LAST_NAME'} = $phone_name;
+    } else {
+      # assume residential, and try (inaccurately) to make a first/last
+      # name out of it.
+      @customer{'FIRST_NAME', 'LAST_NAME'} = split(' ', $phone_name, 2);
+    }
+  } else {
+    my $cust_main = $svc_phone->cust_svc->cust_pkg->cust_main;
+    if ($cust_main->company) {
+      $customer{'LAST_NAME'} = $cust_main->company;
+    } else {
+      $customer{'LAST_NAME'} = $cust_main->last;
+      $customer{'FIRST_NAME'} = $cust_main->first;
+    }
+  }
 
   if ($self->option('debug')) {
     warn "\nAddorUpdateCustomer:\n".Dumper(\%customer)."\n\n";
diff --git a/FS/FS/part_pkg.pm b/FS/FS/part_pkg.pm
index 2ad785939..741eb8741 100644
--- a/FS/FS/part_pkg.pm
+++ b/FS/FS/part_pkg.pm
@@ -790,7 +790,7 @@ sub propagate {
 =item process_fcc_options HASHREF
 
 Sets the FCC options on this package definition to the values specified
-in HASHREF.  Names are as in L<FS::part_pkg_fcc_option/info>.
+in HASHREF.
 
 =cut
 
@@ -807,6 +807,7 @@ sub process_fcc_options {
   my %existing_num = map { $_->fccoptionname => $_->num }
                      qsearch('part_pkg_fcc_option', { pkgpart => $pkgpart });
 
+  local $FS::Record::nowarn_identical = 1;
   # set up params for process_o2m
   my $i = 0;
   my $params = {};
diff --git a/FS/FS/state.pm b/FS/FS/state.pm
new file mode 100644
index 000000000..671a93b44
--- /dev/null
+++ b/FS/FS/state.pm
@@ -0,0 +1,133 @@
+package FS::state;
+
+use strict;
+use base qw( FS::Record );
+use FS::Record qw( qsearch qsearchs );
+use Locale::SubCountry;
+
+=head1 NAME
+
+FS::state - Object methods for state/province records
+
+=head1 SYNOPSIS
+
+  use FS::state;
+
+  $record = new FS::state \%hash;
+  $record = new FS::state { 'column' => 'value' };
+
+  $error = $record->insert;
+
+  $error = $new_record->replace($old_record);
+
+  $error = $record->delete;
+
+  $error = $record->check;
+
+=head1 DESCRIPTION
+
+An FS::state object represents a state, province, or other top-level 
+subdivision of a sovereign nation.  FS::state inherits from FS::Record.  
+The following fields are currently supported:
+
+=over 4
+
+=item statenum
+
+primary key
+
+=item country
+
+two-letter country code
+
+=item state
+
+state code/abbreviation/name (as used in cust_location.state)
+
+=item fips
+
+FIPS 10-4 code (not including country code)
+
+=back
+
+=head1 METHODS
+
+=cut
+
+sub table { 'state'; }
+
+# no external API; this table maintains itself
+
+sub check {
+  my $self = shift;
+
+  my $error = 
+    $self->ut_numbern('statenum')
+    || $self->ut_alpha('country')
+    || $self->ut_alpha('state')
+    || $self->ut_alpha('fips')
+  ;
+  return $error if $error;
+
+  $self->SUPER::check;
+}
+
+=back
+
+=cut
+
+sub _upgrade_data {
+  warn "Updating state and country codes...\n";
+  my %existing;
+  foreach my $state (qsearch('state')) {
+    $existing{$state->country} ||= {};
+    $existing{$state->country}{$state->state} = $state;
+  }
+  my $world = Locale::SubCountry::World->new;
+  foreach my $country_code ($world->all_codes) {
+    my $country = Locale::SubCountry->new($country_code);
+    next unless $country->has_sub_countries;
+    $existing{$country} ||= {};
+    foreach my $state_code ($country->all_codes) {
+      my $fips = $country->FIPS10_4_code($state_code);
+      # we really only need U.S. state codes at this point, so if there's
+      # no FIPS code, ignore it.
+      next if !$fips or $fips eq 'unknown' or $fips =~ /\W/;
+      my $this_state = $existing{$country_code}{$state_code};
+      if ($this_state) {
+        if ($this_state->fips ne $fips) { # this should never happen...
+          $this_state->set(fips => $fips);
+          my $error = $this_state->replace;
+          die "error updating $country_code/$state_code:\n$error\n" if $error;
+        }
+        delete $existing{$country_code}{$state_code};
+      } else {
+        $this_state = FS::state->new({
+          country => $country_code,
+          state   => $state_code,
+          fips    => $fips,
+        });
+        my $error = $this_state->insert;
+        die "error inserting $country_code/$state_code:\n$error\n" if $error;
+      }
+    }
+    # clean up states that no longer exist (does this ever happen?)
+    foreach my $state (values %{ $existing{$country_code} }) {
+      my $error = $state->delete;
+      die "error removing expired state ".$state->country.'/'.$state->state.
+          "\n$error\n" if $error;
+    }
+  } # foreach $country_code
+  '';
+}
+
+=head1 BUGS
+
+=head1 SEE ALSO
+
+L<FS::Record>
+
+=cut
+
+1;
+