summaryrefslogtreecommitdiff
path: root/FS
diff options
context:
space:
mode:
Diffstat (limited to 'FS')
-rw-r--r--FS/FS/AccessRight.pm3
-rw-r--r--FS/FS/Conf.pm21
-rw-r--r--FS/FS/Mason.pm5
-rw-r--r--FS/FS/Record.pm31
-rw-r--r--FS/FS/Report/FCC_477.pm421
-rw-r--r--FS/FS/Schema.pm97
-rw-r--r--FS/FS/Upgrade.pm19
-rw-r--r--FS/FS/deploy_zone.pm277
-rw-r--r--FS/FS/deploy_zone_block.pm130
-rw-r--r--FS/FS/deploy_zone_vertex.pm120
-rw-r--r--FS/FS/part_pkg.pm78
-rw-r--r--FS/FS/part_pkg_fcc_option.pm180
-rw-r--r--FS/FS/state.pm133
-rw-r--r--FS/MANIFEST11
-rw-r--r--FS/t/deploy_zone.t5
-rw-r--r--FS/t/deploy_zone_block.t5
-rw-r--r--FS/t/deploy_zone_vertex.t5
-rw-r--r--FS/t/part_pkg_fcc_option.t5
-rw-r--r--FS/t/state.t5
19 files changed, 1537 insertions, 14 deletions
diff --git a/FS/FS/AccessRight.pm b/FS/FS/AccessRight.pm
index 7a0d0e994..59edc905f 100644
--- a/FS/FS/AccessRight.pm
+++ b/FS/FS/AccessRight.pm
@@ -357,6 +357,9 @@ tie my %rights, 'Tie::IxHash',
'Bulk edit package definitions',
+ 'Edit FCC report configuration',
+ { rightname => 'Edit FCC report configuration for all agents', global=>1 },
+
'Edit CDR rates',
#{ rightname=>'Edit global CDR rates', global=>1, },
diff --git a/FS/FS/Conf.pm b/FS/FS/Conf.pm
index 3ca032551..5ed78c924 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.',
@@ -5759,6 +5759,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 2db693627..660ae2597 100644
--- a/FS/FS/Mason.pm
+++ b/FS/FS/Mason.pm
@@ -363,7 +363,12 @@ if ( -e $addl_handler_use_file ) {
use FS::sched_avail;
use FS::export_batch;
use FS::export_batch_item;
+ use FS::part_pkg_fcc_option;
+ use FS::state;
use FS::queue_stat;
+ use FS::deploy_zone;
+ use FS::deploy_zone_block;
+ use FS::deploy_zone_vertex;
# Sammath Naur
if ( $FS::Mason::addl_handler_use ) {
diff --git a/FS/FS/Record.pm b/FS/FS/Record.pm
index 88e54115d..734d61aaf 100644
--- a/FS/FS/Record.pm
+++ b/FS/FS/Record.pm
@@ -125,6 +125,8 @@ FS::Record - Database record objects
$error = $record->ut_floatn('column');
$error = $record->ut_number('column');
$error = $record->ut_numbern('column');
+ $error = $record->ut_decimal('column');
+ $error = $record->ut_decimaln('column');
$error = $record->ut_snumber('column');
$error = $record->ut_snumbern('column');
$error = $record->ut_money('column');
@@ -2312,6 +2314,35 @@ sub ut_numbern {
'';
}
+=item ut_decimal COLUMN[, DIGITS]
+
+Check/untaint decimal numbers (up to DIGITS decimal places. If there is an
+error, returns the error, otherwise returns false.
+
+=item ut_decimaln COLUMN[, DIGITS]
+
+Check/untaint decimal numbers. May be null. If there is an error, returns
+the error, otherwise returns false.
+
+=cut
+
+sub ut_decimal {
+ my($self, $field, $digits) = @_;
+ $digits ||= '';
+ $self->getfield($field) =~ /^\s*(\d+(\.\d{0,$digits})?)\s*$/
+ or return "Illegal or empty (decimal) $field: ".$self->getfield($field);
+ $self->setfield($field, $1);
+ '';
+}
+
+sub ut_decimaln {
+ my($self, $field, $digits) = @_;
+ $self->getfield($field) =~ /^\s*(\d*(\.\d{0,$digits})?)\s*$/
+ or return "Illegal (decimal) $field: ".$self->getfield($field);
+ $self->setfield($field, $1);
+ '';
+}
+
=item ut_money COLUMN
Check/untaint monetary numbers. May be negative. Set to 0 if null. If there
diff --git a/FS/FS/Report/FCC_477.pm b/FS/FS/Report/FCC_477.pm
index fd088148b..0f3dfb143 100644
--- a/FS/FS/Report/FCC_477.pm
+++ b/FS/FS/Report/FCC_477.pm
@@ -4,9 +4,15 @@ use base qw( FS::Report );
use strict;
use vars qw( @upload @download @technology @part2aoption @part2boption
%states
+ $DEBUG
);
use FS::Record qw( dbh );
+use Tie::IxHash;
+use Storable;
+
+$DEBUG = 0;
+
=head1 NAME
FS::Report::FCC_477 - Routines for FCC Form 477 reports
@@ -78,6 +84,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)',
@@ -161,8 +168,6 @@ sub save_fcc477map {
local $FS::UID::AutoCommit = 0;
my $dbh = dbh;
- # lame (should be normal FS::Record access)
-
my $sql = "delete from fcc477map where formkey = ?";
my $sth = dbh->prepare($sql) or die dbh->errstr;
$sth->execute($key) or do {
@@ -200,11 +205,413 @@ sub statenum2state {
my $num = shift;
$states{$num};
}
+### everything above this point is unmaintained ###
+
+
+=head1 THE "NEW" REPORT (October 2014 and later)
+
+=head2 METHODS
+
+=over 4
+
+=cut
+
+# functions for internal use
+
+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
+ # as the option.
+ my $name = shift;
+ "LEFT JOIN (SELECT pkgpart, optionvalue AS $name FROM part_pkg_fcc_option".
+ " WHERE fccoptionname = '$name') AS t_$name".
+ " 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.
+ #
+ # (Strictly speaking this should also exclude suspended packages but
+ # "suspended as of some past date" is a complicated query.)
+ my $date = shift;
+ "cust_pkg.setup <= $date AND ".
+ "(cust_pkg.cancel IS NULL OR cust_pkg.cancel > $date)";
+}
+
+sub is_fixed_broadband {
+ "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
+
+Returns the report section SECTION (see the C<parts> method for section
+name strings) as an arrayref of arrayrefs. OPTIONS may contain "date"
+(a timestamp value to run the report as of this date) and "agentnum"
+(to limit to a single agent).
+
+=cut
-#sub statenum2abbr {
-# my $num = shift;
-# $states{$num} =~ /\((\w\w)\)$/ or return '';
-# $1;
-#}
+sub report {
+ my $class = shift;
+ my $section = shift;
+ my %opt = @_;
+
+ my $method = $section.'_sql';
+ die "Report section '$section' is not implemented\n"
+ unless $class->can($method);
+ my $statement = $class->$method(%opt);
+
+ my $sth = dbh->prepare($statement);
+ $sth->execute or die $sth->errstr;
+ $sth->fetchall_arrayref;
+}
+
+sub fbd_sql {
+ my $class = shift;
+ my %opt = @_;
+ my $date = $opt{date} || time;
+ warn $date;
+ my $agentnum = $opt{agentnum};
+
+ my @select = (
+ 'censusblock',
+ 'COALESCE(dbaname, agent.agent)',
+ 'technology',
+ 'CASE WHEN is_consumer IS NOT NULL THEN 1 ELSE 0 END',
+ 'adv_speed_down',
+ 'adv_speed_up',
+ 'CASE WHEN is_business IS NOT NULL THEN 1 ELSE 0 END',
+ 'cir_speed_down',
+ 'cir_speed_up',
+ );
+ my $from =
+ 'deploy_zone_block
+ JOIN deploy_zone USING (zonenum)
+ JOIN agent USING (agentnum)';
+ my @where = (
+ "zonetype = 'B'",
+ "active_date < $date",
+ "(expire_date > $date OR expire_date IS NULL)",
+ );
+ push @where, "agentnum = $agentnum" if $agentnum;
+
+ my $order_by = 'censusblock, dbaname, technology, is_consumer, is_business';
+
+ "SELECT ".join(', ', @select) . "
+ FROM $from
+ WHERE ".join(' AND ', @where)."
+ ORDER BY $order_by
+ ";
+}
+
+sub fbs_sql {
+ my $class = shift;
+ my %opt = @_;
+ my $date = $opt{date} || time;
+ my $agentnum = $opt{agentnum};
+
+ my @select = (
+ 'cust_location.censustract',
+ 'technology',
+ 'broadband_downstream',
+ 'broadband_upstream',
+ 'COUNT(*)',
+ 'COUNT(is_consumer)',
+ );
+ 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_broadband technology
+ 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;
+
+ "SELECT ".join(', ', @select) . "
+ FROM $from
+ WHERE ".join(' AND ', @where)."
+ GROUP BY $group_by
+ ORDER BY $order_by
+ ";
+
+}
+
+sub fvs_sql {
+ my $class = shift;
+ my %opt = @_;
+ 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 voip_sessions 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 voip_sessions
+ ))
+ ;
+
+ 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;
+
+ "SELECT ".join(', ', @select) . "
+ FROM $from
+ WHERE ".join(' AND ', @where)."
+ GROUP BY $group_by
+ ORDER BY $order_by
+ ";
+
+}
+
+sub lts_sql {
+ my $class = shift;
+ my %opt = @_;
+ my $date = $opt{date} || time;
+ my $agentnum = $opt{agentnum};
+
+ my @select = (
+ "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 phone_longdistance IS NULL THEN phone_lines ELSE 0 END)",
+ "SUM(CASE WHEN is_consumer = 1 AND phone_longdistance = 1 THEN phone_lines ELSE 0 END)",
+ "SUM(CASE WHEN is_consumer IS NULL AND phone_longdistance IS NULL THEN phone_lines ELSE 0 END)",
+ "SUM(CASE WHEN is_consumer IS NULL AND phone_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 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_phone is_broadband
+ phone_vges phone_circuits phone_lines
+ is_consumer phone_longdistance
+ )).
+ join_optionnames('media', 'phone_localloop')
+ ;
+ my @where = (
+ active_on($date),
+ "is_phone = 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
+ ";
+}
+
+sub voip_sql {
+ my $class = shift;
+ my %opt = @_;
+ 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;
+
+ "SELECT ".join(', ', @select) . "
+ FROM $from
+ WHERE ".join(' AND ', @where)."
+ 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
+
+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);
+}
1;
diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm
index 473532180..64f8ac9cf 100644
--- a/FS/FS/Schema.pm
+++ b/FS/FS/Schema.pm
@@ -3331,6 +3331,18 @@ sub tables_hashref {
'index' => [],
},
+ 'part_pkg_fcc_option' => {
+ 'columns' => [
+ 'num', 'serial', '', '', '', '',
+ 'fccoptionname', 'varchar', '', $char_d, '', '',
+ 'pkgpart', 'int', '', '', '', '',
+ 'optionvalue', 'varchar', 'NULL', $char_d, '', '',
+ ],
+ 'primary_key' => 'num',
+ 'unique' => [ [ 'fccoptionname', 'pkgpart' ] ],
+ 'index' => [],
+ },
+
'rate' => {
'columns' => [
'ratenum', 'serial', '', '', '', '',
@@ -4600,6 +4612,91 @@ 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' => [],
+ },
+
+ # eventually link to tower/sector?
+ 'deploy_zone' => {
+ 'columns' => [
+ 'zonenum', 'serial', '', '', '', '',
+ 'description', 'char', 'NULL', $char_d, '', '',
+ 'agentnum', 'int', '', '', '', '',
+ 'dbaname', 'char', 'NULL', $char_d, '', '',
+ 'zonetype', 'char', '', 1, '', '',
+ 'technology', 'int', '', '', '', '',
+ 'spectrum', 'int', 'NULL', '', '', '',
+ '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, '', '',
+ 'expire_date', @date_type, '', '',
+ ],
+ 'primary_key' => 'zonenum',
+ 'unique' => [],
+ 'index' => [ [ 'agentnum' ] ],
+ 'foreign_keys' => [
+ { columns => [ 'agentnum' ],
+ table => 'agent',
+ references => [ 'agentnum' ],
+ },
+ ],
+ },
+
+ 'deploy_zone_block' => {
+ 'columns' => [
+ 'blocknum', 'serial', '', '', '', '',
+ 'zonenum', 'int', '', '', '', '',
+ 'censusblock', 'char', '', 15, '', '',
+ 'censusyear', 'char', '', 4, '', '',
+ ],
+ 'primary_key' => 'blocknum',
+ 'unique' => [],
+ 'index' => [ [ 'zonenum' ] ],
+ 'foreign_keys' => [
+ { columns => [ 'zonenum' ],
+ table => 'deploy_zone',
+ references => [ 'zonenum' ],
+ },
+ ],
+ },
+
+ 'deploy_zone_vertex' => {
+ 'columns' => [
+ 'vertexnum', 'serial', '', '', '', '',
+ 'zonenum', 'int', '', '', '', '',
+ 'latitude', 'decimal', '', '10,7', '', '',
+ 'longitude', 'decimal', '', '10,7', '', '',
+ ],
+ 'primary_key' => 'vertexnum',
+ 'unique' => [ ],
+ 'index' => [ ],
+ 'foreign_keys' => [
+ { columns => [ 'zonenum' ],
+ table => 'deploy_zone',
+ references => [ 'zonenum' ],
+ },
+ ],
+ },
+
+
+
+
+
# name type nullability length default local
#'new_table' => {
diff --git a/FS/FS/Upgrade.pm b/FS/FS/Upgrade.pm
index d212d451d..db24215a7 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 {
@@ -340,6 +356,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/deploy_zone.pm b/FS/FS/deploy_zone.pm
new file mode 100644
index 000000000..16f59c81d
--- /dev/null
+++ b/FS/FS/deploy_zone.pm
@@ -0,0 +1,277 @@
+package FS::deploy_zone;
+
+use strict;
+use base qw( FS::o2m_Common FS::Record );
+use FS::Record qw( qsearch qsearchs dbh );
+
+=head1 NAME
+
+FS::deploy_zone - Object methods for deploy_zone records
+
+=head1 SYNOPSIS
+
+ use FS::deploy_zone;
+
+ $record = new FS::deploy_zone \%hash;
+ $record = new FS::deploy_zone { 'column' => 'value' };
+
+ $error = $record->insert;
+
+ $error = $new_record->replace($old_record);
+
+ $error = $record->delete;
+
+ $error = $record->check;
+
+=head1 DESCRIPTION
+
+An FS::deploy_zone object represents a geographic zone where a certain kind
+of service is available. Currently we store this information to generate
+the FCC Form 477 deployment reports, but it may find other uses later.
+
+FS::deploy_zone inherits from FS::Record. The following fields are currently
+supported:
+
+=over 4
+
+=item zonenum
+
+primary key
+
+=item description
+
+Optional text describing the zone.
+
+=item agentnum
+
+The agent that serves this zone.
+
+=item dbaname
+
+The name under which service is marketed in this zone. If null, will
+default to the agent name.
+
+=item zonetype
+
+The way the zone geography is defined: "B" for a list of census blocks
+(used by the FCC for fixed broadband service), "P" for a polygon (for
+mobile services). See L<FS::deploy_zone_block> and L<FS::deploy_zone_vertex>.
+
+=item technology
+
+The FCC technology code for the type of service available.
+
+=item spectrum
+
+For mobile service zones, the FCC code for the RF band.
+
+=item adv_speed_up
+
+For broadband, the advertised upstream bandwidth in the zone. If multiple
+speed tiers are advertised, use the highest.
+
+=item adv_speed_down
+
+For broadband, the advertised downstream bandwidth in the zone.
+
+=item cir_speed_up
+
+For broadband, the contractually guaranteed upstream bandwidth, if that type
+of service is sold.
+
+=item cir_speed_down
+
+For broadband, the contractually guaranteed downstream bandwidth, if that
+type of service is sold.
+
+=item is_consumer
+
+'Y' if this service is sold for consumer/household use.
+
+=item is_business
+
+'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.
+
+=item expire_date
+
+The date this zone became inactive, if any.
+
+=back
+
+=head1 METHODS
+
+=over 4
+
+=item new HASHREF
+
+Creates a new zone. To add the zone to the database, see L<"insert">.
+
+=cut
+
+# the new method can be inherited from FS::Record, if a table method is defined
+
+sub table { 'deploy_zone'; }
+
+=item insert ELEMENTS
+
+Adds this record to the database. If there is an error, returns the error,
+otherwise returns false.
+
+=cut
+
+# the insert method can be inherited from FS::Record
+
+=item delete
+
+Delete this record from the database.
+
+=cut
+
+sub delete {
+ my $oldAutoCommit = $FS::UID::AutoCommit;
+ local $FS::UID::AutoCommit = 0;
+ # clean up linked records
+ my $self = shift;
+ my $error = $self->process_o2m(
+ 'table' => $self->element_table,
+ 'num_col' => 'zonenum',
+ 'fields' => 'zonenum',
+ 'params' => {},
+ ) || $self->SUPER::delete(@_);
+
+ if ($error) {
+ dbh->rollback if $oldAutoCommit;
+ return $error;
+ }
+ '';
+}
+
+=item replace OLD_RECORD
+
+Replaces the OLD_RECORD with this one in the database. If there is an error,
+returns the error, otherwise returns false.
+
+=cut
+
+# the replace method can be inherited from FS::Record
+
+=item check
+
+Checks all fields to make sure this is a valid zone record. If there is
+an error, returns the error, otherwise returns false. Called by the insert
+and replace methods.
+
+=cut
+
+sub check {
+ my $self = shift;
+
+ my $error =
+ $self->ut_numbern('zonenum')
+ || $self->ut_textn('description')
+ || $self->ut_number('agentnum')
+ || $self->ut_foreign_key('agentnum', 'agent', 'agentnum')
+ || $self->ut_textn('dbaname')
+ || $self->ut_enum('zonetype', [ 'B', 'P' ])
+ || $self->ut_number('technology')
+ || $self->ut_numbern('spectrum')
+ || $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('is_broadband')) {
+ if (!$self->get($_)) {
+ $self->set($_, 0);
+ }
+ } else {
+ $self->set($_, '');
+ }
+ }
+ if (!$self->get('active_date')) {
+ $self->set('active_date', time);
+ }
+
+ $self->SUPER::check;
+}
+
+=item element_table
+
+Returns the name of the table that contains the zone's elements (blocks or
+vertices).
+
+=cut
+
+sub element_table {
+ my $self = shift;
+ if ($self->zonetype eq 'B') {
+ return 'deploy_zone_block';
+ } elsif ( $self->zonetype eq 'P') {
+ return 'deploy_zone_vertex';
+ } else {
+ die 'unknown zonetype';
+ }
+}
+
+=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
+
+=head1 SEE ALSO
+
+L<FS::Record>
+
+=cut
+
+1;
+
diff --git a/FS/FS/deploy_zone_block.pm b/FS/FS/deploy_zone_block.pm
new file mode 100644
index 000000000..757af7e3d
--- /dev/null
+++ b/FS/FS/deploy_zone_block.pm
@@ -0,0 +1,130 @@
+package FS::deploy_zone_block;
+
+use strict;
+use base qw( FS::Record );
+use FS::Record qw( qsearch qsearchs );
+
+=head1 NAME
+
+FS::deploy_zone_block - Object methods for deploy_zone_block records
+
+=head1 SYNOPSIS
+
+ use FS::deploy_zone_block;
+
+ $record = new FS::deploy_zone_block \%hash;
+ $record = new FS::deploy_zone_block { 'column' => 'value' };
+
+ $error = $record->insert;
+
+ $error = $new_record->replace($old_record);
+
+ $error = $record->delete;
+
+ $error = $record->check;
+
+=head1 DESCRIPTION
+
+An FS::deploy_zone_block object represents a census block that's part of
+a deployment zone. FS::deploy_zone_block inherits from FS::Record. The
+following fields are currently supported:
+
+=over 4
+
+=item blocknum
+
+primary key
+
+=item zonenum
+
+L<FS::deploy_zone> foreign key for the zone.
+
+=item censusblock
+
+U.S. census block number (15 digits).
+
+=item censusyear
+
+The year of the census map where the block appeared or was last verified.
+
+=back
+
+=head1 METHODS
+
+=over 4
+
+=item new HASHREF
+
+Creates a new block entry. To add the recordto the database, see L<"insert">.
+
+Note that this stores the hash reference, not a distinct copy of the hash it
+points to. You can ask the object for a copy with the I<hash> method.
+
+=cut
+
+# the new method can be inherited from FS::Record, if a table method is defined
+
+sub table { 'deploy_zone_block'; }
+
+=item insert
+
+Adds this record to the database. If there is an error, returns the error,
+otherwise returns false.
+
+=cut
+
+# the insert method can be inherited from FS::Record
+
+=item delete
+
+Delete this record from the database.
+
+=cut
+
+# the delete method can be inherited from FS::Record
+
+=item replace OLD_RECORD
+
+Replaces the OLD_RECORD with this one in the database. If there is an error,
+returns the error, otherwise returns false.
+
+=cut
+
+# the replace method can be inherited from FS::Record
+
+=item check
+
+Checks all fields to make sure this is a valid record. If there is
+an error, returns the error, otherwise returns false. Called by the insert
+and replace methods.
+
+=cut
+
+sub check {
+ my $self = shift;
+
+ my $error =
+ $self->ut_numbern('blocknum')
+ || $self->ut_number('zonenum')
+ || $self->ut_number('censusblock')
+ || $self->ut_number('censusyear')
+ ;
+ return $error if $error;
+
+ if ($self->get('censusblock') !~ /^(\d{15})$/) {
+ return "Illegal census block number (must be 15 digits)";
+ }
+
+ $self->SUPER::check;
+}
+
+=back
+
+=head1 SEE ALSO
+
+L<FS::Record>
+
+=cut
+
+1;
+
diff --git a/FS/FS/deploy_zone_vertex.pm b/FS/FS/deploy_zone_vertex.pm
new file mode 100644
index 000000000..078b32640
--- /dev/null
+++ b/FS/FS/deploy_zone_vertex.pm
@@ -0,0 +1,120 @@
+package FS::deploy_zone_vertex;
+
+use strict;
+use base qw( FS::Record );
+use FS::Record qw( qsearch qsearchs );
+
+=head1 NAME
+
+FS::deploy_zone_vertex - Object methods for deploy_zone_vertex records
+
+=head1 SYNOPSIS
+
+ use FS::deploy_zone_vertex;
+
+ $record = new FS::deploy_zone_vertex \%hash;
+ $record = new FS::deploy_zone_vertex { 'column' => 'value' };
+
+ $error = $record->insert;
+
+ $error = $new_record->replace($old_record);
+
+ $error = $record->delete;
+
+ $error = $record->check;
+
+=head1 DESCRIPTION
+
+An FS::deploy_zone_vertex object represents a vertex of a polygonal
+deployment zone (L<FS::deploy_zone>). FS::deploy_zone_vertex inherits from
+FS::Record. The following fields are currently supported:
+
+=over 4
+
+=item vertexnum
+
+primary key
+
+=item zonenum
+
+Foreign key to L<FS::deploy_zone>.
+
+=item latitude
+
+Latitude, as a decimal; positive values are north of the Equator.
+
+=item longitude
+
+Longitude, as a decimal; positive values are east of Greenwich.
+
+=back
+
+=head1 METHODS
+
+=over 4
+
+=item new HASHREF
+
+Creates a new vertex record. To add the record to the database, see L<"insert">.
+
+=cut
+
+sub table { 'deploy_zone_vertex'; }
+
+=item insert
+
+Adds this record to the database. If there is an error, returns the error,
+otherwise returns false.
+
+=cut
+
+=item delete
+
+Delete this record from the database.
+
+=cut
+
+=item replace OLD_RECORD
+
+Replaces the OLD_RECORD with this one in the database. If there is an error,
+returns the error, otherwise returns false.
+
+=cut
+
+=item check
+
+Checks all fields to make sure this is a valid vertex. If there is
+an error, returns the error, otherwise returns false. Called by the insert
+and replace methods.
+
+=cut
+
+# the check method should currently be supplied - FS::Record contains some
+# data checking routines
+
+sub check {
+ my $self = shift;
+
+ my $error =
+ $self->ut_numbern('vertexnum')
+ || $self->ut_number('zonenum')
+ || $self->ut_coord('latitude')
+ || $self->ut_coord('longitude')
+ ;
+ return $error if $error;
+
+ $self->SUPER::check;
+}
+
+=back
+
+=head1 BUGS
+
+=head1 SEE ALSO
+
+L<FS::Record>
+
+=cut
+
+1;
+
diff --git a/FS/FS/part_pkg.pm b/FS/FS/part_pkg.pm
index 036daf705..f90e3eec5 100644
--- a/FS/FS/part_pkg.pm
+++ b/FS/FS/part_pkg.pm
@@ -17,6 +17,7 @@ use FS::cust_pkg;
use FS::agent_type;
use FS::type_pkgs;
use FS::part_pkg_option;
+use FS::part_pkg_fcc_option;
use FS::pkg_class;
use FS::agent;
use FS::part_pkg_msgcat;
@@ -303,6 +304,11 @@ sub insert {
}
}
+ if ( $options{fcc_options} ) {
+ warn " updating fcc options " if $DEBUG;
+ $self->set_fcc_options( $options{fcc_options} );
+ }
+
warn " committing transaction" if $DEBUG and $oldAutoCommit;
$dbh->commit or die $dbh->errstr if $oldAutoCommit;
@@ -545,6 +551,11 @@ sub replace {
}
}
+ if ( $options->{fcc_options} ) {
+ warn " updating fcc options " if $DEBUG;
+ $new->set_fcc_options( $options->{fcc_options} );
+ }
+
warn " committing transaction" if $DEBUG and $oldAutoCommit;
$dbh->commit or die $dbh->errstr if $oldAutoCommit;
'';
@@ -705,6 +716,44 @@ sub propagate {
join("\n", @error);
}
+=item set_fcc_options HASHREF
+
+Sets the FCC options on this package definition to the values specified
+in HASHREF.
+
+=cut
+
+sub set_fcc_options {
+ my $self = shift;
+ my $pkgpart = $self->pkgpart;
+ my $options;
+ if (ref $_[0]) {
+ $options = shift;
+ } else {
+ $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 = {};
+ foreach my $name (keys %$options ) {
+ $params->{ "num$i" } = $existing_num{$name} || '';
+ $params->{ "num$i".'_fccoptionname' } = $name;
+ $params->{ "num$i".'_optionvalue' } = $options->{$name};
+ $i++;
+ }
+
+ $self->process_o2m(
+ table => 'part_pkg_fcc_option',
+ fields => [qw( fccoptionname optionvalue )],
+ params => $params,
+ );
+}
+
=item pkg_locale LOCALE
Returns a customer-viewable string representing this package for the given
@@ -1216,6 +1265,35 @@ sub option {
'';
}
+=item fcc_option OPTIONNAME
+
+Returns the FCC 477 report option value for the given name, or the empty
+string.
+
+=cut
+
+sub fcc_option {
+ my ($self, $name) = @_;
+ my $part_pkg_fcc_option =
+ qsearchs('part_pkg_fcc_option', {
+ pkgpart => $self->pkgpart,
+ fccoptionname => $name,
+ });
+ $part_pkg_fcc_option ? $part_pkg_fcc_option->optionvalue : '';
+}
+
+=item fcc_options
+
+Returns all FCC 477 report options for this package, as a hash-like list.
+
+=cut
+
+sub fcc_options {
+ my $self = shift;
+ map { $_->fccoptionname => $_->optionvalue }
+ qsearch('part_pkg_fcc_option', { pkgpart => $self->pkgpart });
+}
+
=item bill_part_pkg_link
Returns the associated part_pkg_link records (see L<FS::part_pkg_link>).
diff --git a/FS/FS/part_pkg_fcc_option.pm b/FS/FS/part_pkg_fcc_option.pm
new file mode 100644
index 000000000..5c78e5f9e
--- /dev/null
+++ b/FS/FS/part_pkg_fcc_option.pm
@@ -0,0 +1,180 @@
+package FS::part_pkg_fcc_option;
+
+use strict;
+use base qw( FS::Record );
+use FS::Record qw( qsearch qsearchs );
+use Storable qw(dclone);
+use Tie::IxHash;
+
+sub table { 'part_pkg_fcc_option'; }
+
+=head1 NAME
+
+FS::part_pkg_fcc_option - Object methods for part_pkg_fcc_option records
+
+=head1 SYNOPSIS
+
+ use FS::part_pkg_fcc_option;
+
+ $record = new FS::part_pkg_fcc_option \%hash;
+ $record = new FS::part_pkg_fcc_option { 'column' => 'value' };
+
+ $error = $record->insert;
+
+ $error = $new_record->replace($old_record);
+
+ $error = $record->delete;
+
+ $error = $record->check;
+
+=head1 DESCRIPTION
+
+An FS::part_pkg_fcc_option object represents an option that classifies a
+package definition on the FCC Form 477 report. FS::part_pkg_fcc_option
+inherits from FS::Record. The following fields are currently supported:
+
+=over 4
+
+=item num
+
+primary key
+
+=item fccoptionname
+
+A string identifying a report option, as an element of a static data
+structure found within this module. See the C<part> method.
+
+=item pkgpart
+
+L<FS::part_pkg> foreign key.
+
+=item optionvalue
+
+The value of the report option, as an integer. Boolean options use 1
+and NULL. Most other options have some kind of lookup table.
+
+=back
+
+=head1 METHODS
+
+=over 4
+
+=item check
+
+Checks all fields to make sure this is a valid FCC option. If there is
+an error, returns the error, otherwise returns false. Called by the insert
+and replace methods.
+
+=cut
+
+sub check {
+ my $self = shift;
+
+ my $error =
+ $self->ut_numbern('num')
+ || $self->ut_alpha('fccoptionname')
+ || $self->ut_number('pkgpart')
+ || $self->ut_foreign_key('pkgpart', 'part_pkg', 'pkgpart')
+ || $self->ut_textn('optionvalue')
+ ;
+ return $error if $error;
+
+ $self->SUPER::check;
+}
+
+=back
+
+=head1 CLASS METHODS
+
+=over 4
+
+=item media_types
+
+Returns a Tie::IxHash hashref of the media type strings (which are not
+part of the report definition, per se) to arrayrefs of the technology
+codes included in each one.
+
+=item technology_labels
+
+Returns a hashref relating each technology code to a label. Unlike the
+media type strings, the technology codes are part of the formal report
+definition.
+
+=cut
+
+tie our %media_types, 'Tie::IxHash', (
+ 'Copper' => [ 11, 12, 10, 20, 30 ],
+ 'Cable Modem' => [ 41, 42, 40 ],
+ 'Fiber' => [ 50 ],
+ 'Satellite' => [ 60 ],
+ 'Fixed Wireless' => [ 70 ],
+ 'Mobile Wireless' => [ 80, 81, 82, 83, 84, 85, 86, 87, 88 ],
+ 'Other' => [ 90, 0 ],
+);
+
+tie our %technology_labels, 'Tie::IxHash', (
+ 10 => 'Other ADSL',
+ 11 => 'ADSL2',
+ 12 => 'VDSL',
+ 20 => 'SDSL',
+ 30 => 'Other Copper Wireline',
+ 40 => 'Other Cable Modem',
+ 41 => 'Cable - DOCSIS 1, 1.1, 2.0',
+ 42 => 'Cable - DOCSIS 3.0',
+ 50 => 'Fiber',
+ 60 => 'Satellite',
+ 70 => 'Terrestrial Fixed Wireless',
+ # mobile wireless
+ 80 => 'Mobile - WCDMA/UMTS/HSPA',
+ 81 => 'Mobile - HSPA+',
+ 82 => 'Mobile - EVDO/EVDO Rev A',
+ 83 => 'Mobile - LTE',
+ 84 => 'Mobile - WiMAX',
+ 85 => 'Mobile - CDMA',
+ 86 => 'Mobile - GSM',
+ 87 => 'Mobile - Analog',
+ 88 => 'Other Mobile',
+
+ 90 => 'Electric Power Line',
+ 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);
+}
+
+sub technology_labels {
+ Storable::dclone(\%technology_labels);
+}
+
+sub spectrum_labels {
+ Storable::dclone(\%spectrum_labels);
+}
+
+=head1 BUGS
+
+=head1 SEE ALSO
+
+L<FS::Record>, schema.html from the base documentation.
+
+=cut
+
+1;
+
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;
+
diff --git a/FS/MANIFEST b/FS/MANIFEST
index a8dfbc1e1..0698ba2e1 100644
--- a/FS/MANIFEST
+++ b/FS/MANIFEST
@@ -762,5 +762,16 @@ FS/export_batch.pm
t/export_batch.t
FS/export_batch_item.pm
t/export_batch_item.t
+FS/part_pkg_fcc_option.pm
+t/part_pkg_fcc_option.t
+FS/state.pm
+t/state.t
FS/queue_stat.pm
t/queue_stat.t
+FS/deploy_zone.pm
+t/deploy_zone.t
+FS/deploy_zone_block.pm
+t/deploy_zone_block.t
+FS/deploy_zone_vertex.pm
+t/deploy_zone_vertex.t
+
diff --git a/FS/t/deploy_zone.t b/FS/t/deploy_zone.t
new file mode 100644
index 000000000..d220e81c7
--- /dev/null
+++ b/FS/t/deploy_zone.t
@@ -0,0 +1,5 @@
+BEGIN { $| = 1; print "1..1\n" }
+END {print "not ok 1\n" unless $loaded;}
+use FS::deploy_zone;
+$loaded=1;
+print "ok 1\n";
diff --git a/FS/t/deploy_zone_block.t b/FS/t/deploy_zone_block.t
new file mode 100644
index 000000000..c3241b158
--- /dev/null
+++ b/FS/t/deploy_zone_block.t
@@ -0,0 +1,5 @@
+BEGIN { $| = 1; print "1..1\n" }
+END {print "not ok 1\n" unless $loaded;}
+use FS::deploy_zone_block;
+$loaded=1;
+print "ok 1\n";
diff --git a/FS/t/deploy_zone_vertex.t b/FS/t/deploy_zone_vertex.t
new file mode 100644
index 000000000..78c079ffd
--- /dev/null
+++ b/FS/t/deploy_zone_vertex.t
@@ -0,0 +1,5 @@
+BEGIN { $| = 1; print "1..1\n" }
+END {print "not ok 1\n" unless $loaded;}
+use FS::deploy_zone_vertex;
+$loaded=1;
+print "ok 1\n";
diff --git a/FS/t/part_pkg_fcc_option.t b/FS/t/part_pkg_fcc_option.t
new file mode 100644
index 000000000..8f781c866
--- /dev/null
+++ b/FS/t/part_pkg_fcc_option.t
@@ -0,0 +1,5 @@
+BEGIN { $| = 1; print "1..1\n" }
+END {print "not ok 1\n" unless $loaded;}
+use FS::part_pkg_fcc_option;
+$loaded=1;
+print "ok 1\n";
diff --git a/FS/t/state.t b/FS/t/state.t
new file mode 100644
index 000000000..a21137fd5
--- /dev/null
+++ b/FS/t/state.t
@@ -0,0 +1,5 @@
+BEGIN { $| = 1; print "1..1\n" }
+END {print "not ok 1\n" unless $loaded;}
+use FS::state;
+$loaded=1;
+print "ok 1\n";
generated by cgit v1.2.1 (git 2.18.0) at 2025-07-18 06:13:18 +0000