'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, },
},
{
- '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.",
},
{
+ '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.',
],
},
+ {
+ '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" },
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 ) {
$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');
'';
}
+=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
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
);
#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)',
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 {
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;
'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', '', '', '', '',
],
},
+ # 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' => {
$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 {
#fix taxable line item links
'cust_bill_pkg_tax_location' => [],
+
+ #populate state FIPS codes if not already done
+ 'state' => [],
;
\%hash;
--- /dev/null
+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;
+
--- /dev/null
+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;
+
--- /dev/null
+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;
+
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;
}
}
+ 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;
}
}
+ 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;
'';
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
'';
}
+=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>).
--- /dev/null
+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;
+
--- /dev/null
+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;
+
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
+
--- /dev/null
+BEGIN { $| = 1; print "1..1\n" }
+END {print "not ok 1\n" unless $loaded;}
+use FS::deploy_zone;
+$loaded=1;
+print "ok 1\n";
--- /dev/null
+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";
--- /dev/null
+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";
--- /dev/null
+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";
--- /dev/null
+BEGIN { $| = 1; print "1..1\n" }
+END {print "not ok 1\n" unless $loaded;}
+use FS::state;
+$loaded=1;
+print "ok 1\n";
--- /dev/null
+#!/usr/bin/perl
+
+my $user = shift;
+use FS::UID 'adminsuidsetup';
+use FS::Record qw(qsearch qsearchs dbh);
+use FS::part_pkg_report_option;
+use Text::CSV;
+
+if (!$user) {
+ print "
+Usage: bin/convert-477-options <username>
+
+This script will convert your per-package FCC 477 report options
+from the classic style (part IA, IB, IIA...) to the 2014 style.
+This is an approximate conversion, and you should review the
+resulting package settings for accuracy. In particular:
+ - Broadband speeds will be set to the lowest speed in their
+ tier.
+ - Broadband technologies for ADSL and cable modem will be set
+ to 'other ADSL' and 'other cable modem'. You should set
+ them to the specific ADSL or cable encapsulation in use.
+ - All packages will be set to 'business grade'. The 'consumer grade'
+ category did not exist in previous versions of the report.
+";
+ exit(1);
+}
+
+adminsuidsetup($user) or die "invalid user '$user'";
+$FS::UID::AutoCommit = 1;
+$FS::Record::nowarn_classload = 1;
+
+print "Configuring packages...\n";
+
+my @min_download_speed = ( 0.2, 0.768, 1.5, 3, 6, 10, 25, 100 );
+my @min_upload_speed = ( 0.1, @min_download_speed );
+my @media_type = ( 'Copper', 'Copper', 'Copper', 'Cable Modem',
+ 'Fiber', 'Satellite', 'Fixed Wireless', 'Mobile Wireless',
+ 'Other', 'Other' );
+my @technology = ( 10, 20, 30, 40,
+ 50, 60, 70, 80,
+ 90, 0 );
+
+my @phone_option = (
+ 'phone_longdistance:1', # LD carrier
+ 'phone_localloop:owned', # owned loops
+ 'phone_localloop:leased', # unswitched UNE loops
+ 'phone_localloop:resale', # UNE-P (is pretty much extinct...)
+ 'phone_localloop:resale', # UNE-P replacement
+ 'media:Fiber',
+ 'media:Cable Modem',
+ 'media:Fixed Wireless',
+);
+
+my @voip_option = (
+ '', #nomadic; no longer reported
+ 'media:Copper',
+ 'media:Fiber',
+ 'media:Cable Modem',
+ 'media:Fixed Wireless',
+ 'media:Other'
+);
+
+my %num_to_formkey; # o2m
+foreach ( qsearch('fcc477map', {}) ) {
+ push @{ $num_to_formkey{$_->formvalue} ||= [] }, $_->formkey;
+}
+
+sub report_option_to_fcc_option {
+ my $report_option_num = shift;
+ my $formkeys = $num_to_formkey{$report_option_num}
+ or return;
+ my @return;
+ foreach my $formkey (@$formkeys) {
+ if ($formkey =~ /^part1_column_option_(\d+)/) {
+ #download speed
+ push @return, (broadband_downstream => $min_download_speed[$1]);
+ } elsif ($formkey =~ /^part1_row_option_(\d+)/) {
+ #upload speed
+ push @return, (broadband_upstream => $min_upload_speed[$1]);
+ } elsif ($formkey =~ /^part1_technology_option_(\d+)/) {
+ #broadband tech
+ push @return,
+ (is_broadband => 1,
+ media => $media_type[$1],
+ technology => $technology[$1]);
+ } elsif ($formkey =~ /^part2a_row_option_(\d+)/) {
+ #local phone options
+ push @return,
+ (media => 'Copper', # sensible default
+ split(':', $phone_option[$1])
+ );
+ } elsif ($formkey =~ /^part2b_row_option_(\d+)/) {
+ #VoIP options (are all media types)
+ push @return, (split(':', $voip_option[$1]));
+ } else {
+ warn "can't parse option with formkey '$formkey'\n";
+ }
+ }
+ @return;
+}
+
+for my $part_pkg (qsearch('part_pkg', { freq => {op => '!=', value => '0'}})) {
+ my $pkgpart = $part_pkg->pkgpart;
+ #print "#$pkgpart\n";
+ my %report_opts = $part_pkg->options;
+ my @fcc_opts;
+ foreach my $optionname (keys(%report_opts)) {
+ $optionname =~ /^report_option_(\d+)$/ or next;
+ my $num = $1;
+ push @fcc_opts, report_option_to_fcc_option($num);
+ }
+ # other special stuff:
+ # FCC voice class (VoIP OTT, VoIP + broadband)
+ if ($part_pkg->fcc_voip_class == 1) {
+ push @fcc_opts, 'is_voip' => 1;
+ } elsif ( $part_pkg->fcc_voip_class == 2) {
+ push @fcc_opts, 'is_voip' => 1, 'is_broadband' => 1;
+ }
+ # DS0 equivalent lines
+ if ( $part_pkg->fcc_ds0s ) {
+ if ($part_pkg->fcc_voip_class) {
+ # there's no such thing as a VoIP DS0 equivalent, but this is
+ # what we used the field for
+ push @fcc_opts, 'voip_sessions' => $part_pkg->fcc_ds0s;
+ } else {
+ push @fcc_opts, 'phone_lines' => $part_pkg->fcc_ds0s, 'is_phone' => 1;
+ }
+ }
+
+ my %fcc_opts = @fcc_opts;
+ #print map {"\t$_\t".$fcc_opts{$_}."\n"} keys %fcc_opts;
+ my $error = $part_pkg->process_fcc_options(\%fcc_opts);
+ if ( $error ) {
+ die "$error\n";
+ }
+ #print "\n";
+}
+
+print "Finished.\n";
+
--- /dev/null
+<& /elements/header.html, 'Deployment zones' &>
+<& /elements/menubar.html,
+ 'Add a new fixed broadband zone' => $p.'edit/deploy_zone-fixed.html',
+ 'Add a new mobile zone' => $p.'edit/deploy_zone-mobile.html',
+&>
+<P><FONT SIZE="+1"><B>Fixed Broadband Zones</B></FONT></P>
+<& elements/browse.html,
+ name_singular => 'zone',
+ query => { table => 'deploy_zone',
+ hashref => { zonetype => 'B' },
+ },
+ count_query => "SELECT COUNT(*) FROM deploy_zone WHERE zonetype = 'B'",
+ agent_virt => 1,
+ header => [ '#',
+ 'Description',
+ 'Technology',
+ 'Market',
+ 'Advertised Mbps',
+ 'Contractual Mbps',
+ 'Census blocks',
+ ],
+ fields => [ 'zonenum',
+ 'description',
+ sub { my $self = shift;
+ $tech_label->{$self->technology} },
+ sub { my $self = shift;
+ join( ' / ',
+ $self->is_consumer ? 'consumer' : (),
+ $self->is_business ? 'business' : ()
+ )
+ },
+ sub { my $self = shift;
+ join( ' / ', grep $_,
+ $self->adv_speed_down,
+ $self->adv_speed_up
+ )
+ },
+ sub { my $self = shift;
+ join( ' / ', grep $_,
+ $self->cir_speed_down,
+ $self->cir_speed_up
+ )
+ },
+ sub { my $self = shift;
+ FS::deploy_zone_block->count('zonenum = '.$self->zonenum)
+ },
+ ],
+ sort_fields => [ 'zonenum',
+ 'description',
+ 'technology',
+ '(is_consumer is not null, is_business is not null)',
+ '(adv_speed_down, adv_speed_up)',
+ '(cir_speed_down, cir_speed_up)',
+ ],
+ links => [ '', $link_fixed, ],
+ align => 'clllllr',
+ nohtmlheader => 1,
+ disable_maxselect => 1,
+ disable_total => 1,
+&>
+<P><FONT SIZE="+1"><B>Mobile Zones</B></FONT></P>
+<& elements/browse.html,
+ name_singular => 'zone',
+ query => { table => 'deploy_zone',
+ hashref => { zonetype => 'P' },
+ },
+ count_query => "SELECT COUNT(*) FROM deploy_zone WHERE zonetype = 'P'",
+ agent_virt => 1,
+ header => [ '#',
+ 'Description',
+ 'Technology',
+ 'Spectrum',
+ 'Service Type',
+ 'Advertised Mbps',
+ 'Vertices', # number of vertices? not so useful
+ ],
+ fields => [ 'zonenum',
+ 'description',
+ sub { my $self = shift;
+ $tech_label->{$self->technology} },
+ sub { my $self = shift;
+ $spec_label->{$self->spectrum} },
+ sub { my $self = shift;
+ join( ' / ',
+ $self->is_voice ? 'voice' : (),
+ $self->is_broadband ? 'broadband' : (),
+ )
+ },
+ sub { my $self = shift;
+ join( ' / ', grep $_,
+ $self->adv_speed_down,
+ $self->adv_speed_up
+ )
+ },
+ sub { my $self = shift;
+ FS::deploy_zone_vertex->count('zonenum = '.$self->zonenum)
+ },
+ ],
+ sort_fields => [ 'zonenum',
+ 'description',
+ 'technology',
+ 'spectrum',
+ '(is_voice is not null, is_broadband is not null)',
+ '(adv_speed_down, adv_speed_up)',
+ ],
+ links => [ '', $link_mobile, ],
+ align => 'clllllr',
+ nohtmlheader => 1,
+ disable_maxselect => 1,
+ disable_total => 1,
+&>
+
+<& /elements/footer.html &>
+<%init>
+my $curuser = $FS::CurrentUser::CurrentUser;
+my $acl_edit = $curuser->access_right('Edit FCC report configuration');
+my $acl_edit_global = $curuser->access_right('Edit FCC report configuration for all agents');
+die "access denied"
+ unless $acl_edit or $acl_edit_global;
+
+my $link_fixed = [ $p.'edit/deploy_zone-fixed.html?', 'zonenum' ];
+my $link_mobile= [ $p.'edit/deploy_zone-mobile.html?', 'zonenum' ];
+
+my $tech_label = FS::part_pkg_fcc_option->technology_labels;
+my $spec_label = FS::part_pkg_fcc_option->spectrum_labels;
+</%init>
--- /dev/null
+<& elements/browse.html,
+ 'title' => 'Package Definitions - FCC Options',
+ 'menubar' => \@menubar,
+ 'html_init' => $html_init,
+ 'html_form' => $html_form,
+ 'name' => 'package definitions',
+ 'disableable' => 1,
+ 'disabled_statuspos' => 4,
+ 'agent_virt' => 1,
+ 'agent_null_right' => $edit_global,
+ 'agent_pos' => 3,
+ 'query' =>
+ { 'select' => $select,
+ 'table' => 'part_pkg',
+ 'addl_from' => $addl_from,
+ 'hashref' => \%hash,
+ 'extra_sql' => $extra_sql,
+ 'order_by' => "ORDER BY $orderby"
+ },
+ 'count_query' => $count_query,
+ 'header' => \@header,
+ 'fields' => \@fields,
+ 'links' => \@links,
+ 'align' => $align,
+ 'link_field' => 'pkgpart',
+ 'html_foot' => $html_foot,
+&>
+<%init>
+
+my $curuser = $FS::CurrentUser::CurrentUser;
+
+my $edit = 'Edit FCC report configuration';
+my $edit_global = 'Edit FCC report configuration for all agents';
+my $acl_edit = $curuser->access_right($edit);
+my $acl_edit_global = $curuser->access_right($edit_global);
+
+die "access denied"
+ unless $acl_edit || $acl_edit_global;
+
+if ( $cgi->param('redirect') ) {
+ my $session = $cgi->param('redirect');
+ my $pref = $curuser->option("redirect$session");
+ die "unknown redirect session $session\n" unless length($pref);
+ $cgi = new CGI($pref);
+ $cgi->param('redirect', $session);
+}
+
+my $conf = new FS::Conf;
+
+my $orderby = 'pkgpart';
+my %hash = ();
+my $extra_count = '';
+
+my @where = ();
+
+# only ever show recurring packages here
+$hash{'freq'} = { op=>'!=', value=>'0' };
+$extra_count = " freq != '0' ";
+
+# filter by classnum
+my $classnum = '';
+if ( $cgi->param('classnum') =~ /^(\d+)$/ ) {
+ $classnum = $1;
+ push @where, $classnum ? "classnum = $classnum"
+ : "classnum IS NULL";
+}
+$cgi->delete('classnum');
+
+# filter by agent permissions
+push @where, FS::part_pkg->curuser_pkgs_sql
+ unless $acl_edit_global;
+
+my $extra_sql = scalar(@where)
+ ? ( scalar(keys %hash) ? ' AND ' : ' WHERE ' ).
+ join( 'AND ', @where)
+ : '';
+
+# pull option values into the select
+my @optionnames = ( qw(
+ media
+ is_consumer
+ is_broadband technology broadband_upstream broadband_downstream
+ is_phone phone_wholesale phone_vges phone_circuits
+ phone_lines phone_longdistance phone_localloop
+ is_voip voip_lastmile voip_sessions
+) );
+
+my $select = join(',',
+ 'part_pkg.*',
+ '(SELECT classname FROM pkg_class WHERE pkg_class.classnum = part_pkg.classnum) AS classname', # grr, disableable...
+ @optionnames
+);
+
+my $addl_from =
+ FS::Report::FCC_477::join_optionnames(@optionnames);
+
+$cgi->param('classnum', $classnum) if length($classnum);
+
+my $link = [ $p.'edit/part_pkg.cgi?', 'pkgpart' ];
+
+my @header = ( '#', 'Package', 'Comment' );
+my @fields = ( 'pkgpart', 'pkg', 'comment' ,);
+my $align = 'rll';
+my @links = ( $link, $link, '', );
+
+unless ( length($classnum) ) {
+ push @header, 'Class';
+ push @fields, 'classname';
+ $align .= 'l';
+}
+
+# still include the report_option classes, to help with migration
+# but not other plan options
+
+my %report_optionname_name = map { 'report_option_'.$_->num, $_->name }
+ qsearch('part_pkg_report_option', { disabled => '' });
+
+push @header, 'Report classes';
+
+push @fields,
+ sub {
+ my $part_pkg = shift;
+ my %options = $part_pkg->options;
+ # gather any options that are really report options,
+ # convert them to their user-friendly names,
+ # and sort them (I think?)
+ my @report_options =
+ sort { $a cmp $b }
+ map { $report_optionname_name{$_} }
+ grep { $options{$_}
+ and exists($report_optionname_name{$_}) }
+ keys %options;
+
+ my @rows;
+ foreach (@report_options) {
+ push @rows, [
+ { 'data' => $_,
+ 'align' => 'center',
+ 'colspan' => 2
+ }
+ ];
+ } # foreach @report_options
+ \@rows;
+ };
+
+$align .= 'cr';
+
+# --------
+# now the FCC option part
+# --------
+
+my @pkgparts;
+push @header, 'FCC report parameters';
+push @fields, sub {
+ my $part_pkg = shift;
+ my %hash = $part_pkg->fcc_options;
+ include('/elements/input-fcc_options.html',
+ id => 'pkgpart'.$part_pkg->pkgpart,
+ curr_value => encode_json(\%hash),
+ html_only => 1
+ );
+};
+$align .= 'l';
+
+my $count_extra_sql = $extra_sql;
+$count_extra_sql =~ s/^\s*AND /WHERE /i;
+$extra_count = ( $count_extra_sql ? ' AND ' : ' WHERE ' ). $extra_count
+ if $extra_count;
+my $count_query = "SELECT COUNT(*) FROM part_pkg $count_extra_sql $extra_count";
+
+# in case of error redirect
+if ( $cgi->param('redirect') ) {
+ push @header, '';
+ push @fields, sub {
+ my $part_pkg = shift;
+ my $pkgpart = $part_pkg->pkgpart;
+ '<B><FONT COLOR="#ffffff">' . $cgi->param("error$pkgpart") || '' . '</FONT></B>'
+ };
+ $align .= 'l';
+}
+
+my $html_init =
+ include('/elements/init_overlib.html') .
+ include('/elements/input-fcc_options.html', js_only => 1) .
+ include('.style');
+
+my $html_form = qq!<FORM ACTION="${p}edit/process/bulk-part_pkg-fcc.html" METHOD="POST">
+ ( show class: !.
+ include('/elements/select-pkg_class.html',
+ #'curr_value' => $classnum,
+ 'value' => $classnum, #insist on 0 :/
+ 'onchange' => 'filter_change()',
+ 'pre_options' => [ '-1' => 'all',
+ '0' => '(none)', ],
+ 'disable_empty' => 1,
+ ).
+ ' )
+ <BR><BR>' .
+ qq!<SCRIPT TYPE="text/javascript">
+ function filter_change() {
+ window.location = '! . $cgi->self_url . qq!?classnum='
+ + document.getElementById('classnum').value;
+ }
+ </SCRIPT>!;
+
+my $html_foot = qq!
+ <INPUT TYPE="submit" VALUE="Save changes">
+ </FORM>!;
+
+my @menubar =
+ ( 'Package definitions' => $p.'browse/part_pkg.cgi' );
+
+</%init>
+<%def .style>
+<style>
+ ul.fcc_options {
+ text-align: left;
+ }
+ ul.fcc_options li {
+ }
+ button.edit_fcc_options {
+ float: right;
+ }
+</style>
+</%def>
--- /dev/null
+<& elements/edit.html,
+ 'name_singular' => 'deployment zone',
+ 'table' => 'deploy_zone',
+ 'post_url' => popurl(1).'process/deploy_zone-fixed.html',
+ 'labels' => {
+ 'description' => 'Description',
+ 'agentnum' => 'Agent',
+ 'dbaname' => 'Business name (if different from agent)',
+ 'technology' => 'Technology',
+ 'adv_speed_up' => 'Upstream',
+ 'adv_speed_down' => 'Downstream',
+ 'cir_speed_up' => 'Upstream',
+ 'cir_speed_down' => 'Downstream',
+ 'is_consumer' => 'Consumer/mass market',
+ 'is_business' => 'Business/government',
+ 'blocknum' => '',
+ 'active_date' => 'Active since',
+ },
+ 'fields' => [
+ { field => 'zonetype',
+ type => 'hidden',
+ value => 'B'
+ },
+ { field => 'is_broadband',
+ type => 'hidden',
+ value => 'Y',
+ },
+ 'description',
+ { field => 'active_date',
+ type => 'fixed-date',
+ value => time,
+ },
+ { field => 'agentnum',
+ type => 'select-agent',
+ disable_empty => 1,
+ viewall_right => 'Edit FCC report configuration for all agents',
+ },
+ 'dbaname',
+ { field => 'technology',
+ type => 'select',
+ options => [ map { @$_ } values(%$media_types) ],
+ labels => $technology_labels,
+ },
+ { field => 'is_consumer', type => 'checkbox', value=>'Y' },
+ { field => 'is_business', type => 'checkbox', value=>'Y' },
+ { type => 'tablebreak-tr-title',
+ value => 'Advertised maximum speed (Mbps)' },
+ 'adv_speed_down',
+ 'adv_speed_up',
+ { type => 'tablebreak-tr-title',
+ value => 'Contractually guaranteed speed (Mbps)' },
+ 'cir_speed_down',
+ 'cir_speed_up',
+
+ { type => 'tablebreak-tr-title', value => 'Census blocks'},
+ { field => 'blocknum',
+ type => 'deploy_zone_block',
+ o2m_table => 'deploy_zone_block',
+ m2_label => ' ',
+ m2_error_callback => $m2_error_callback,
+ },
+ ],
+
+&>
+<%init>
+my $curuser = $FS::CurrentUser::CurrentUser;
+die "access denied"
+ unless $curuser->access_right([
+ 'Edit FCC report configuration',
+ 'Edit FCC report configuration for all agents',
+ ]);
+
+my $technology_labels = FS::part_pkg_fcc_option->technology_labels;
+my $media_types = FS::part_pkg_fcc_option->media_types;
+delete $media_types->{'Mobile Wireless'}; # cause this is the fixed zone page
+
+my $m2_error_callback = sub {
+ my ($cgi, $deploy_zone) = @_;
+ my @blocknums = grep {
+ /^blocknum\d+/ and length($cgi->param($_.'_censusblock'))
+ } $cgi->param;
+
+ sort { $a->censusblock <=> $b->censusblock }
+ map {
+ my $k = $_;
+ FS::deploy_zone_block->new({
+ blocknum => scalar($cgi->param($k)),
+ zonenum => $deploy_zone->zonenum,
+ censusblock => scalar($cgi->param($k.'_censusblock')),
+ censusyear => scalar($cgi->param($k.'_censusyear')),
+ })
+ } @blocknums;
+};
+
+</%init>
--- /dev/null
+<& elements/edit.html,
+ 'name_singular' => 'deployment zone',
+ 'table' => 'deploy_zone',
+ 'post_url' => popurl(1).'process/deploy_zone-mobile.html',
+ 'labels' => {
+ 'description' => 'Description',
+ 'agentnum' => 'Agent',
+ 'dbaname' => 'Business name (if different from agent)',
+ 'technology' => 'Technology',
+ 'spectrum' => 'Spectrum',
+ 'is_broadband', => 'Broadband Internet',
+ 'adv_speed_up' => 'Upstream',
+ 'adv_speed_down' => 'Downstream',
+ 'is_voice', => 'Voice',
+ 'vertexnum' => '',
+ 'active_date' => 'Active since',
+ },
+ 'fields' => [
+ { field => 'zonetype',
+ type => 'hidden',
+ value => 'P'
+ },
+ 'description',
+ { field => 'active_date',
+ type => 'fixed-date',
+ value => time,
+ },
+ { field => 'agentnum',
+ type => 'select-agent',
+ disable_empty => 1,
+ viewall_right => 'Edit FCC report configuration for all agents',
+ },
+ 'dbaname',
+ { field => 'technology',
+ type => 'select',
+ options => $media_types->{'Mobile Wireless'},
+ labels => $technology_labels,
+ },
+ { field => 'spectrum',
+ type => 'select',
+ options => [ keys %$spectrum_labels ],
+ labels => $spectrum_labels,
+ },
+ { field => 'is_broadband', type => 'checkbox', value=>'Y' },
+ { field => 'is_voice', type => 'checkbox', value=>'Y' },
+ { type => 'tablebreak-tr-title',
+ value => 'Advertised minimum speed (Mbps)' },
+ 'adv_speed_down',
+ 'adv_speed_up',
+ { type => 'tablebreak-tr-title', value => 'Footprint'},
+ { field => 'vertexnum',
+ type => 'deploy_zone_vertex',
+ o2m_table => 'deploy_zone_vertex',
+ m2_label => ' ',
+ m2_error_callback => $m2_error_callback,
+ },
+ ],
+
+&>
+<%init>
+my $curuser = $FS::CurrentUser::CurrentUser;
+die "access denied"
+ unless $curuser->access_right([
+ 'Edit FCC report configuration',
+ 'Edit FCC report configuration for all agents',
+ ]);
+
+my $technology_labels = FS::part_pkg_fcc_option->technology_labels;
+my $spectrum_labels = FS::part_pkg_fcc_option->spectrum_labels;
+my $media_types = FS::part_pkg_fcc_option->media_types;
+
+my $m2_error_callback = sub {
+ my ($cgi, $deploy_zone) = @_;
+ my @vertexnums = sort { $a <=> $b } grep {
+ /^vertexnum\d+/ and length($cgi->param($_.'_latitude'))
+ } $cgi->param;
+
+ map {
+ my $k = $_;
+ my $s = 0;
+ FS::deploy_zone_vertex->new({
+ vertexnum => scalar($cgi->param($k)),
+ zonenum => $deploy_zone->zonenum,
+ latitude => scalar($cgi->param($k.'_latitude')),
+ longitude => scalar($cgi->param($k.'_longitude')),
+ })
+ } @vertexnums;
+};
+
+</%init>
% 'disable_empty' => $f->{'disable_empty'},
% #select-reason
% 'reason_class' => $f->{'reason_class'},
+% #select-agent
+% 'viewall_right' => $f->{'viewall_right'},
%
% #selectlayers
% 'layer_fields' => $f->{'layer_fields'},
},
{ field=>'pay_weight', type=>'text', size=>6 },
{ field=>'credit_weight', type=>'text', size=>6 },
-
- ( $conf->exists('cust_pkg-show_fcc_voice_grade_equivalent')
- ? (
- { type => 'tablebreak-tr-title',
- value => 'FCC Form 477 information',
- },
- { field=>'fcc_voip_class',
- type=>'select-voip_class',
- },
- { field=>'fcc_ds0s', type=>'text', size=>6 },
- )
- : ()
- ),
-
+ ($fcc_opts ? (
+ { type => 'tablebreak-tr-title',
+ value => 'FCC Form 477 information',
+ },
+ { field => 'fcc_options_string',
+ type => 'input-fcc_options',
+ curr_value_callback => sub {
+ my ($cgi, $part_pkg, $fref) = @_;
+ if ( $cgi->param('fcc_options_string') ) {
+ # error redirect
+ return $cgi->param('fcc_options_string');
+ }
+ my %hash;
+ %hash = $part_pkg->fcc_options
+ if ($part_pkg->pkgpart);
+ return encode_json(\%hash);
+ },
+ },
+ ) : ()
+ ),
{ type => 'columnend' },
{ 'type' => $report_option ? 'tablebreak-tr-title'
my $conf = new FS::Conf;
my $taxproducts = $conf->exists('enable_taxproducts');
+my $fcc_opts = $conf->exists('part_pkg-show_fcc_options');
+
my @locales = grep { ! /^en_/i } $conf->config('available-locales'); #should filter from the default locale lang instead of en_
my %locale_labels = map {
( $_ => 'Package -- '. FS::Locales->description($_) )
--- /dev/null
+% if ( keys %error ) {
+% foreach my $pkgpart (keys %error) {
+% # stuff all the errors back into $cgi
+% $cgi->param("error$pkgpart", $error{$pkgpart});
+% }
+% my $session = int(rand(4294967296)); #XXX
+% my $pref = new FS::access_user_pref({
+% 'usernum' => $FS::CurrentUser::CurrentUser->usernum,
+% 'prefname' => "redirect$session",
+% 'prefvalue' => $cgi->query_string,
+% 'expiration' => time + 3600, #1h? 1m?
+% });
+% my $pref_error = $pref->insert;
+% if ( $pref_error ) {
+% die "FATAL: couldn't even set redirect cookie: $pref_error".
+% " attempting to set redirect$session to ". $cgi->query_string."\n";
+% }
+<% $cgi->redirect($fsurl.'browse/part_pkg-fcc.html?redirect='.$session) %>
+% } else {
+<% $cgi->redirect($fsurl.'browse/part_pkg-fcc.html?classnum='.$classnum) %>
+% }
+<%init>
+my $curuser = $FS::CurrentUser::CurrentUser;
+my $edit_acl = $curuser->access_right('Edit FCC report configuration');
+my $global_edit_acl = $curuser->access_right('Edit FCC report configuration for all agents');
+die "access denied" unless $edit_acl or $global_edit_acl;
+
+# non-atomic; report errors but allow successful changes to go through
+# not that I even know how you'd get an error doing this
+
+my %error;
+foreach my $param ($cgi->param) {
+ $param =~ /^pkgpart(\d+)$/ or next;
+ my $pkgpart = $1;
+ my $part_pkg = FS::part_pkg->by_key($pkgpart);
+ my $hashref = decode_json( $cgi->param($param) );
+ my $error = $part_pkg->set_fcc_options($hashref);
+ $error{$pkgpart} = $error if $error;
+}
+
+my $classnum = $cgi->param('classnum');
+
+</%init>
--- /dev/null
+<& elements/process.html,
+ error_redirect => popurl(2).'deploy_zone-fixed.html?',
+ table => 'deploy_zone',
+ viewall_dir => 'browse',
+ process_o2m =>
+ { 'table' => 'deploy_zone_block',
+ 'fields' => [qw( censusblock censusyear )]
+ },
+&>
--- /dev/null
+<& elements/process.html,
+ error_redirect => popurl(2).'deploy_zone-mobile.html?',
+ table => 'deploy_zone',
+ viewall_dir => 'browse',
+ process_o2m =>
+ { 'table' => 'deploy_zone_vertex',
+ 'fields' => [qw( latitude longitude )]
+ },
+&>
push @args, 'options' => \%options;
###
+ # fcc options
+ ###
+ my $fcc_options_string = $cgi->param('fcc_options_string');
+ if ($fcc_options_string) {
+ push @args, 'fcc_options' => decode_json($fcc_options_string);
+ }
+
+ ###
#pkg_svc
###
--- /dev/null
+% unless ( $opt{'js_only'} ) {
+
+ <INPUT TYPE="hidden" NAME="<%$name%>" ID="<%$id%>" VALUE="<% $curr_value %>">
+ Block
+ <INPUT TYPE = "text"
+ NAME = "<%$name%>_censusblock"
+ ID = "<%$id%>_censusblock"
+ VALUE = "<% scalar($cgi->param($name.'_censusblock'))
+ || $deploy_zone_block->censusblock
+ %>"
+ SIZE = 17
+ MAXLENGTH = 15
+ <% $onchange %>
+ >
+
+ Year
+ <INPUT TYPE = "text"
+ NAME = "<%$name%>_censusyear"
+ ID = "<%$id%>_censusyear"
+ VALUE = "<% scalar($cgi->param($name.'_censusyear'))
+ || $deploy_zone_block->censusyear
+ %>"
+ SIZE = 5
+ MAXLENGTH = 4
+ <% $onchange %>
+ >
+% }
+<%init>
+
+my( %opt ) = @_;
+
+my $name = $opt{'element_name'} || $opt{'field'} || 'blocknum';
+my $id = $opt{'id'} || 'blocknum';
+
+my $curr_value = $opt{'curr_value'} || $opt{'value'};
+
+my $onchange = $opt{'onchange'};
+if ( $onchange ) {
+ $onchange =~ s/\(what\);/(this);/;
+ $onchange = 'onchange="'.$onchange.'"';
+}
+
+my $deploy_zone_block = $curr_value
+ ? FS::deploy_zone_block->by_key($curr_value)
+ : FS::deploy_zone_block->new;
+
+</%init>
--- /dev/null
+% unless ( $opt{'js_only'} ) {
+
+ <INPUT TYPE="hidden" NAME="<%$name%>" ID="<%$id%>" VALUE="<% $curr_value %>">
+ Latitude
+ <INPUT TYPE = "text"
+ NAME = "<%$name%>_latitude"
+ ID = "<%$id%>_latitude"
+ VALUE = "<% scalar($cgi->param($name.'_latitude'))
+ || $deploy_zone_vertex->latitude
+ %>"
+ SIZE = 18
+ <% $onchange %>
+ >
+
+ Longitude
+ <INPUT TYPE = "text"
+ NAME = "<%$name%>_longitude"
+ ID = "<%$id%>_longitude"
+ VALUE = "<% scalar($cgi->param($name.'_longitude'))
+ || $deploy_zone_vertex->longitude
+ %>"
+ SIZE = 18
+ <% $onchange %>
+ >
+% }
+<%init>
+
+my( %opt ) = @_;
+
+my $name = $opt{'element_name'} || $opt{'field'} || 'vertexnum';
+my $id = $opt{'id'} || 'vertexnum';
+
+my $curr_value = $opt{'curr_value'} || $opt{'value'};
+
+my $onchange = $opt{'onchange'};
+if ( $onchange ) {
+ $onchange =~ s/\(what\);/(this);/;
+ $onchange = 'onchange="'.$onchange.'"';
+}
+
+my $deploy_zone_vertex = $curr_value
+ ? FS::deploy_zone_vertex->by_key($curr_value)
+ : FS::deploy_zone_vertex->new;
+
+</%init>
--- /dev/null
+% unless ($opt{js_only}) {
+<& hidden.html, 'field' => $id, @_ &>
+%# <& input-text.html, 'id' => $id, @_ &> # XXX debugging
+<UL ID="<%$id%>_display_fcc_options" CLASS="fcc_options">
+</UL>
+<button type="button" class="edit_fcc_button" data-target="<% $id %>">
+ Edit
+</button>
+% }
+% unless ($opt{html_only}) {
+% my $popup = $fsurl.'misc/part_pkg_fcc_options.html?id=';
+% my $popup_name = 'popup-'.time. "-$$-". rand() * 2**32;
+<SCRIPT TYPE="text/javascript">
+function edit_fcc_options() {
+ var id = this.dataset['target'];
+ overlib(
+ OLiframeContent( '<% $popup %>' + id,
+ 760, 600, '<% $popup_name %>', 0, 'auto' ),
+ CAPTION, 'FCC Form 477 options',
+ STICKY, AUTOSTATUSCAP, MIDX, 0, MIDY, 0,
+ DRAGGABLE, CLOSECLICK,
+ BGCOLOR, '#333399', CGCOLOR, '#333399',
+ CLOSETEXT, 'Close'
+ );
+}
+
+var technology_labels = <% encode_json(FS::part_pkg_fcc_option->technology_labels) %>;
+function show_fcc_options(id) {
+ var curr_values = JSON.parse(document.getElementById(id).value);
+ // hardcoded for the same reasons as misc/part_pkg_fcc_options
+ var out = '';
+ var tech = curr_values['technology'];
+ if ( tech ) {
+ if (technology_labels[tech]) {
+ tech = technology_labels[tech];
+ } else {
+ tech = 'Technology '+tech; // unknown?
+ }
+ }
+ var media = String.toLowerCase(curr_values['media'] || 'unknown media');
+ if ( curr_values['is_consumer'] ) {
+ out += '<li><strong>Consumer-grade</strong> service</li>';
+ } else {
+ out += '<li><strong>Business-grade</strong> service</li>';
+ }
+ if ( curr_values['is_broadband'] ) {
+ out += '<li>Broadband via <strong>' + tech + '</strong>'
+ + '<li><strong>' + curr_values['broadband_downstream']
+ + 'Mbps </strong> down / '
+ + '<strong>' + curr_values['broadband_upstream']
+ + 'Mbps </strong> up</li>';
+ }
+ if ( curr_values['is_phone'] ) {
+ if ( curr_values['phone_wholesale'] ) {
+ out += '<li>Wholesale telephone</li>';
+ if ( curr_values['phone_vges'] ) {
+ out += '<li><strong>' + curr_values['phone_vges'] + '</strong>'
+ + ' switched voice-grade lines</li>';
+ }
+ if ( curr_values['phone_circuits'] ) {
+ out += '<li><strong>' + curr_values['phone_circuits'] + '</strong>'
+ + ' unswitched circuits</li>';
+ }
+ } else {
+ // enduser service
+ out += '<li>Local telephone over <strong>' + media + '</strong></li>'
+ + '<li><strong>' + curr_values['phone_lines']
+ + '</strong> voice-grade lines</li>';
+ if ( curr_values['phone_localloop'] == 'resale' ) {
+ out += '<li><strong>Resold</strong> from another carrier</li>>';
+ } else if ( curr_values['phone_localloop'] == 'leased' ) {
+ out += '<li>Using <strong>leased circuits</strong> from another carrier</li>';
+ } else if ( curr_values['phone_localloop'] == 'owned' ) {
+ out += '<li>Using <strong>our own circuits</strong></li>';
+ }
+ if ( curr_values['phone_longdistance'] ) {
+ out += '<li>Includes <strong>long-distance service</strong></li>';
+ }
+ }
+ } // is_phone
+ if ( curr_values['is_voip'] ) {
+ out += '<li><strong>VoIP</strong> telephone service</li>';
+ out += '<li><strong>' + curr_values['voip_sessions'] +
+ '</strong> sessions allowed</li>';
+ if ( curr_values['voip_lastmile'] ) {
+ out += '<li><strong>Including</strong> last-mile connection</li>';
+ } else {
+ out += '<li>Using a <strong>separate</strong> last-mile connection</li>';
+ }
+ } // is_voip
+ if ( curr_values['is_mobile'] ) {
+ out += '<li><strong>Mobile</strong> telephone service</li>';
+ if ( curr_values['mobile_direct'] ) {
+ out += '<li>Billed <strong>directly to the user</strong></li>';
+ }
+ } // is_mobile
+
+ var out_ul = document.getElementById(id + '_display_fcc_options');
+ out_ul.innerHTML = out;
+}
+<&| onload.js &>
+ var edit_fcc_buttons = document.getElementsByClassName('edit_fcc_button');
+ for(var i = 0; i < edit_fcc_buttons.length; i++) {
+ var button = edit_fcc_buttons[i];
+ show_fcc_options( button.dataset['target'] );
+ button.addEventListener('click', edit_fcc_options);
+ }
+</&>
+</SCRIPT>
+% }
+<%init>
+my %opt = @_;
+my $id = $opt{id} || $opt{field};
+</%init>
$report_packages{'Suspension summary'} = [ $fsurl.'search/cust_pkg_susp.html', 'Show suspension activity', ]
if $curuser->access_right('Summarize packages');
$report_packages{'Customer packages with unconfigured services'} = [ $fsurl.'search/cust_pkg.cgi?APKG_pkgnum', 'List packages which have provisionable services' ];
-$report_packages{'FCC Form 477 packages'} = [ $fsurl.'search/report_477.html', 'Summarize packages by census tract for particular types' ]
- if $conf->exists('cust_main-require_censustract');
+$report_packages{'FCC Form 477'} = [ $fsurl.'search/report_477.html' ]
+ if $conf->exists('part_pkg-show_fcc_options');
$report_packages{'Advanced package reports'} = [ $fsurl.'search/report_cust_pkg.html', 'by agent, date range, status, package definition' ];
tie my %report_inventory, 'Tie::IxHash',
--- /dev/null
+<STYLE>
+ ul.fcc_options {
+ font-weight: normal;
+ text-align: left;
+ padding: 0em 1em 0em 2em;
+ }
+</STYLE>
+<TR>
+ <TH COLSPAN=2>
+ <& hidden.html, 'id' => $id, @_ &>
+%# <& input-text.html, 'id' => $id, @_ &> # XXX debugging
+ <UL ID="<%$id%>_display_fcc_options" CLASS="fcc_options">
+ </UL>
+ <BUTTON TYPE="button" onclick="edit_fcc_options()">
+ Edit
+ </BUTTON>
+% # show some kind of useful summary of the FCC options here
+ </TH>
+</TR>
+<SCRIPT TYPE="text/javascript">
+function edit_fcc_options() {
+ <& popup_link_onclick.html,
+ 'action' => $fsurl.'misc/part_pkg_fcc_options.html?id=' . $id,
+ 'actionlabel' => 'FCC Form 477 options',
+ 'width' => 760,
+ 'height' => 600,
+ &>
+}
+var technology_labels = <% encode_json(FS::part_pkg_fcc_option->technology_labels) %>;
+function show_fcc_options() {
+ var curr_values = JSON.parse(document.getElementById('<% $id %>').value);
+ // hardcoded for the same reasons as misc/part_pkg_fcc_options
+ var out = '';
+ var tech = curr_values['technology'];
+ if ( tech ) {
+ if (technology_labels[tech]) {
+ tech = technology_labels[tech];
+ } else {
+ tech = 'Technology '+tech; // unknown?
+ }
+ }
+ var media = String.toLowerCase(curr_values['media'] || 'unknown media');
+ if ( curr_values['is_consumer'] ) {
+ out += '<li><strong>Consumer-grade</strong></li>>';
+ } else {
+ out += '<li><strong>Business-grade</strong></li>';
+ }
+ if ( curr_values['is_broadband'] ) {
+ out += '<li>Broadband via <strong>' + tech + '</strong>'
+ + '<li><strong>' + curr_values['broadband_downstream']
+ + 'Mbps </strong> down / '
+ + '<strong>' + curr_values['broadband_upstream']
+ + 'Mbps </strong> up</li>';
+ }
+ if ( curr_values['is_phone'] ) {
+ if ( curr_values['phone_wholesale'] ) {
+ out += '<li>Wholesale telephone</li>';
+ if ( curr_values['phone_vges'] ) {
+ out += '<li><strong>' + curr_values['phone_vges'] + '</strong>'
+ + ' switched voice-grade lines</li>';
+ }
+ if ( curr_values['phone_circuits'] ) {
+ out += '<li><strong>' + curr_values['phone_circuits'] + '</strong>'
+ + ' unswitched circuits</li>';
+ }
+ } else {
+ // enduser service
+ out += '<li>Local telephone over <strong>' + media + '</strong></li>'
+ + '<li><strong>' + curr_values['phone_lines']
+ + '</strong> voice-grade lines</li>';
+ if ( curr_values['phone_localloop'] == 'resale' ) {
+ out += '<li><strong>Resold</strong> from another carrier</li>>';
+ } else if ( curr_values['phone_localloop'] == 'leased' ) {
+ out += '<li>Using <strong>leased circuits</strong> from another carrier</li>';
+ } else if ( curr_values['phone_localloop'] == 'owned' ) {
+ out += '<li>Using <strong>our own circuits</strong></li>';
+ }
+ if ( curr_values['phone_longdistance'] ) {
+ out += '<li>Includes <strong>long-distance service</strong></li>';
+ }
+ }
+ } // is_phone
+ if ( curr_values['is_voip'] ) {
+ out += '<li><strong>VoIP</strong> telephone service</li>';
+ if ( curr_values['voip_ott'] ) {
+ out += '<li>Using a <strong>separate</strong> last-mile connection</li>';
+ } else {
+ out += '<li><strong>Including</strong> last-mile connection</li>';
+ }
+ } // is_voip
+
+ var out_ul = document.getElementById('<% $id %>_display_fcc_options');
+ out_ul.innerHTML = out;
+}
+<&| onload.js &>
+ show_fcc_options();
+</&>
+</SCRIPT>
+<%init>
+my %opt = @_;
+my $id = $opt{id} || $opt{field};
+</%init>
--- /dev/null
+<& /elements/header-popup.html &>
+
+<STYLE>
+ fieldset {
+ border: 1px solid #7e0079;
+ border-radius: 8px;
+ background-color: #fff;
+ }
+ fieldset.inv {
+ border: none;
+ }
+ fieldset:disabled {
+ display: none;
+ }
+</STYLE>
+
+<%def .checkbox>
+% my $field = shift;
+% my $extra = shift || '';
+<INPUT TYPE="checkbox" NAME="<% $field %>" ID="<% $field %>" VALUE="1" <%$extra%>>
+</%def>
+
+<FORM NAME="fcc_option_form">
+% # The option structure is hardcoded. The FCC rules changed enough from
+% # the original 477 report to the 2013 revision that any data-driven
+% # mechanism for expressing the original rules would likely fail to
+% # accommodate the new ones. Therefore, we assume that whatever the FCC
+% # does NEXT will also require a rewrite of this form, and we'll deal with
+% # that when it arrives.
+ <P>
+ <LABEL FOR="media">Media type</LABEL>
+ <SELECT NAME="media" ID="media">
+ <OPTION VALUE=""></OPTION>
+% foreach (keys(%$media_types)) {
+ <OPTION VALUE="<% $_ %>"><% $_ %></OPTION>
+% }
+ </SELECT>
+ </P>
+ <P>
+ <& .checkbox, 'is_consumer' &>
+ <LABEL FOR="is_consumer">This is a consumer-grade package</LABEL>
+ </P>
+ <P>
+ <& .checkbox, 'is_broadband' &>
+ <LABEL FOR="is_broadband">This package provides broadband service</LABEL>
+ <FIELDSET ID="broadband">
+ <LABEL FOR="technology">Technology of transmission</LABEL>
+ <SELECT NAME="technology" ID="technology"> </SELECT>
+ <BR>
+ <LABEL FOR="broadband_downstream">Downstream speed (Mbps)</LABEL>
+ <INPUT NAME="broadband_downstream" ID="broadband_downstream">
+ <BR>
+ <LABEL FOR="broadband_upstream">Upstream speed (Mbps)</LABEL>
+ <INPUT NAME="broadband_upstream" ID="broadband_upstream">
+ </FIELDSET>
+ </P>
+ <P>
+ <& .checkbox, 'is_phone' &>
+ <LABEL FOR="is_phone">This package provides local telephone service</LABEL>
+ <FIELDSET ID="phone">
+ <LABEL FOR="phone_wholesale">Marketed as</LABEL>
+ <SELECT NAME="phone_wholesale" ID="phone_wholesale">
+ <OPTION VALUE="">end user</OPTION>
+ <OPTION VALUE="1">wholesale</OPTION>
+ </SELECT>
+ <FIELDSET CLASS="inv" ID="phone0" DISABLED="1">
+ <LABEL FOR="phone_lines">Number of voice-grade equivalents</LABEL>
+ <INPUT NAME="phone_lines" ID="phone_lines">
+ <BR>
+ <LABEL FOR="phone_longdistance">Are you the presubscribed long-distance carrier?</LABEL>
+ <& .checkbox, 'phone_longdistance' &>
+ <BR>
+ <LABEL FOR="phone_localloop">Local loop arrangement</LABEL>
+ <SELECT NAME="phone_localloop" ID="phone_localloop">
+ <OPTION VALUE="owned">You own the local loop</OPTION>
+ <OPTION VALUE="leased">You lease UNE-L from another carrier</OPTION>
+ <OPTION VALUE="resale">You resell another carrier's service</OPTION>
+ </SELECT>
+ </FIELDSET>
+ <FIELDSET CLASS="inv" ID="phone1" DISABLED="1">
+ <LABEL FOR="phone_vges">Number of voice-grade equivalents (if any)</LABEL>
+ <INPUT NAME="phone_vges" ID="phone_vges">
+ <BR>
+ <LABEL FOR="phone_circuits">Number of unswitched circuits (if any)</LABEL>
+ <INPUT NAME="phone_circuits" ID="phone_circuits">
+ </FIELDSET>
+ </FIELDSET>
+ </P>
+ <P>
+ <& .checkbox, 'is_voip' &>
+ <LABEL FOR="is_voip">This package provides VoIP telephone service</LABEL>
+ <FIELDSET ID="voip">
+ <LABEL FOR="voip_sessions">Number of simultaneous calls possible</LABEL>
+ <INPUT NAME="voip_sessions" ID="voip_sessions">
+ <BR>
+ <& .checkbox, 'voip_lastmile' &>
+ <LABEL FOR="voip_lastmile">Do you also provide last-mile connectivity?</LABEL>
+ </FIELDSET>
+ </P>
+ <P>
+ <& .checkbox, 'is_mobile' &>
+ <LABEL FOR="is_mobile">This package provides mobile telephone service</LABEL>
+ <FIELDSET ID="mobile">
+ <LABEL FOR="mobile_direct">Do you bill the customer directly?</LABEL>
+ <& .checkbox, 'mobile_direct' &>
+ </FIELDSET>
+ </P>
+ <DIV WIDTH="100%" STYLE="text-align:center">
+ <INPUT TYPE="submit" VALUE="Save changes">
+ </DIV>
+</FORM>
+
+<SCRIPT TYPE="text/javascript">
+// this form is invoked as a popup; the current values of the parent
+// object are in the form field ID passed as the 'id' param
+
+var parent_input = window.parent.document.getElementById('<% $parent_id %>');
+var curr_values = JSON.parse(window.parent_input.value);
+var form = document.forms['fcc_option_form'];
+var media_types = <% encode_json($media_types) %>
+var technology_labels = <% encode_json($technology_labels) %>
+
+function set_tech_options() {
+ var form = document.forms['fcc_option_form'];
+ var curr_type = form.elements['media'].value;
+ var technology_obj = form.elements['technology'];
+ technology_obj.options.length = 0;
+ if (media_types[curr_type]) {
+ for( var i = 0; i < media_types[curr_type].length; i++ ) {
+ var value = media_types[curr_type][i];
+ var o = document.createElement('OPTION');
+ o.text = technology_labels[value]
+ o.value = value;
+ technology_obj.add(o);
+ }
+ }
+}
+
+function save_changes() {
+ var form = document.forms['fcc_option_form'];
+ var data = {};
+ for (var i = 0; i < form.elements.length; i++) {
+ if (form.elements[i].type == 'submit')
+ continue;
+
+ // quick and dirty test for whether the element is displayed
+ if (form.elements[i].clientHeight > 0) {
+ if (form.elements[i].type == 'checkbox') {
+ if (form.elements[i].checked) {
+ data[ form.elements[i].name ] = 1;
+ }
+ } else {
+ data[ form.elements[i].name ] = form.elements[i].value;
+ }
+ }
+ }
+ parent_input.value = JSON.stringify(data);
+ // update the display
+ parent.show_fcc_options(parent_input.id);
+ parent.cClick(); //overlib
+}
+
+function enable_fieldset(fieldset_id) {
+ var fieldset = document.getElementById(fieldset_id);
+ return function () { fieldset.disabled = !this.checked; };
+}
+
+<&| /elements/onload.js &>
+ function addEventListener(target, action, listener) {
+ if (target.addEventListener) {
+ target.addEventListener(action, listener);
+ } else if (target.attachEvent) { // IE 8 fails at everything
+ target.attachEvent('on'+action, listener);
+ }
+ }
+
+ // set up all event handlers
+ addEventListener(form, 'submit', save_changes);
+
+ var sections = [ 'broadband', 'phone', 'voip', 'mobile' ];
+ for(var i = 0; i < sections.length; i++) {
+ var toggle = form.elements['is_'+sections[i]];
+ addEventListener(toggle, 'change', enable_fieldset(sections[i]));
+ }
+
+ addEventListener(form.elements['media'], 'change', set_tech_options);
+ addEventListener(form.elements['phone_wholesale'], 'change',
+ function () {
+ form.elements['phone0'].disabled = (this.value == 1);
+ form.elements['phone1'].disabled = (this.value == '');
+ }
+ );
+ addEventListener(form.elements['is_phone'], 'change',
+ function() {
+ form.elements['phone_wholesale'].dispatchEvent( new Event('change') );
+ }
+ );
+
+ // load data from the parent form and trigger handlers
+ for(var i = 0; i < form.elements.length; i++) {
+ var input_obj = form.elements[i];
+ if (input_obj.type == 'submit') {
+ //nothing
+ } else if (input_obj.type == 'checkbox') {
+ input_obj.checked = (curr_values[input_obj.name] > 0);
+ } else {
+ input_obj.value = curr_values[input_obj.name] || '';
+ }
+ input_obj.dispatchEvent( new Event('change') );
+ }
+
+</&>
+
+</SCRIPT>
+<& /elements/footer.html &>
+<%init>
+my $media_types = FS::part_pkg_fcc_option->media_types;
+my $technology_labels = FS::part_pkg_fcc_option->technology_labels;
+
+my $parent_id = $cgi->param('id');
+</%init>
-% if ( $type eq 'xml' ) {
-% $filename = "fcc_477_$state" . '_' . time2str('%Y%m%d', $date) . '.xml';
-% http_header('Content-Type' => 'application/XML' ); # So saith RFC 4180
-% http_header('Content-Disposition' => 'attachment;filename="'.$filename.'"');
-<?xml version="1.0" encoding="ISO-8859-1"?>
-<Form_477_submission xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="https://specialreports.fcc.gov/wcb/Form477/XMLSchema-instance/form_477_upload_Schema.xsd" >
-% } else { #html
-<& /elements/header.html, "FCC Form 477 Results - $state" &>
-%# XXX when we stop supporting IE8, add this to freeside.css using :nth-child
-%# selectors, and remove it from everywhere else
+<& /elements/header.html, $title &>
<STYLE TYPE="text/css">
-.grid TH { background-color: #cccccc; padding: 0px 3px 2px; text-align: right }
-.row0 TD { background-color: #eeeeee; padding: 0px 3px 2px; text-align: right }
-.row1 TD { background-color: #ffffff; padding: 0px 3px 2px; text-align: right }
+table.fcc477part {
+ border-collapse: collapse;
+ border: 1px #777 solid;
+ margin-bottom: 20px;
+}
+table.fcc477part td {
+ padding: 0px 4px;
+ border-left: 1px #777 solid;
+ border-right: 1px #777 solid;
+}
+table.fcc477part tbody td {
+ text-align: right;
+}
+table.fcc477part thead tr.head {
+ text-align: center;
+ vertical-align: top;
+ font-weight: bold;
+ border-top: 1px #777 solid;
+ border-bottom: 1px #777 solid;
+}
+table.fcc477part thead tr.subhead {
+ text-align: center;
+ font-weight: bold;
+ font-size: small;
+ border-top: 1px #777 solid;
+ border-bottom: 1px #777 solid;
+}
+.parttitle {
+ font-weight: bold;
+ font-size: large;
+ float: left;
+}
+a.download {
+ float: right;
+}
</STYLE>
-
-<TABLE WIDTH="100%">
- <TR>
- <TD></TD>
- <TD ALIGN="right" CLASS="noprint">
- Download full results<BR>
-% $cgi->param('_type', 'xml');
- as <A HREF="<% $cgi->self_url %>">XML file</A><BR>
-
-% $cgi->param('_type', 'html-print');
- as <A HREF="<% $cgi->self_url %>">printable copy</A>
-
- </TD>
-% $cgi->param('_type', $type );
- </TR>
-</TABLE>
-% } #html
-% foreach my $part ( @parts ) {
-% if ( $part{$part} ) {
-%
-% if ( $part eq 'V' ) {
-% next unless ( $part{'IIA'} || $part{'IIB'} );
-% }
-%
-% if ( $part eq 'VI_census' ) {
-% next unless $part{'IA'};
-% }
-%
-% my @reports = ();
-% if ( $part eq 'IA' ) {
-% for ( my $tech = 0; $tech < scalar(@technology_option); $tech++ ) {
-% next unless $technology_option[$tech];
-% my $url = &{$url_mangler}($part);
-% if ( $type eq 'xml' ) {
-<<% 'Part_IA_'. chr(65 + $tech) %>>
-% }
-<& "477part${part}.html",
- 'tech_code' => $tech,
- 'url' => $url,
- 'type' => $type,
- 'date' => $date,
-&>
-% if ( $type eq 'xml' ) {
-</<% 'Part_IA_'. chr(65 + $tech) %>>
-% }
-% }
-% } else { # not part IA
-% if ( $type eq 'xml' ) {
-<<% 'Part_'. $part %>>
-% }
-% my $url = &{$url_mangler}($part);
-<& "477part${part}.html",
- 'url' => $url,
- 'date' => $date,
- 'filename' => $filename,
-&>
-% if ( $type eq 'xml' ) {
-</<% 'Part_'. $part %>>
-% }
+% foreach my $partname (@partnames) {
+% $cgi->param('parts', $partname);
+% $cgi->param('type', 'csv');
+<table class="fcc477part">
+ <caption>
+ <span class="parttitle"><% $part_titles->{$partname} %></span>
+ <a class="download" href="<% $cgi->self_url %>">Download</a>
+ </caption>
+% my $header = ".header_$partname";
+% my $data = $parts{$partname};
+ <thead>
+ <& $header &>
+ </thead>
+% foreach my $row (@$data) {
+ <tr>
+% foreach my $item (@$row) {
+ <td><% $item %></td>
% }
+ </tr>
% }
-% }
-%
-% if ( $type eq 'xml' ) {
-</Form_477_submission>
-% } else {
+</table>
+% } # foreach $partname
<& /elements/footer.html &>
-% }
<%init>
-
-my $curuser = $FS::CurrentUser::CurrentUser;
-
die "access denied"
- unless $curuser->access_right('List packages');
-
-my $date = $cgi->param('date') ? parse_datetime($cgi->param('date'))
- : time;
-
-my $state = uc($cgi->param('state'));
-$state =~ /^[A-Z]{2}$/ or die "illegal state: $state";
+ unless $FS::CurrentUser::CurrentUser->access_right('List packages');
-my %part = map { $_ => 1 } grep { /^\w+$/ } $cgi->param('part');
-my $type = $cgi->param('_type') || 'html';
-my $filename;
-my @technology_option = &FS::Report::FCC_477::parse_technology_option($cgi,1);
+my %parts;
+# load from cache if possible
+my $session;
+if ( $cgi->param('session') =~ /^(\d+)$/ ) {
+ $session = $1;
+ %parts = %{ $m->cache->get($session) };
+} else {
+ $session = sprintf('%010d%06d', time, int(rand(1000000)));
+ $cgi->param('session', $session);
+}
-# save upload and download mappings
-my @download = $cgi->param('part1_column_option');
-my @upload = $cgi->param('part1_row_option');
-for(my $i=0; $i < scalar(@download); $i++) {
- &FS::Report::FCC_477::save_fcc477map("part1_column_option_$i",$download[$i]);
+my $agentnum;
+if ($cgi->param('agentnum') =~ /^(\d+)$/ ) {
+ $agentnum = $1;
}
-for(my $i=0; $i < scalar(@upload); $i++) {
- &FS::Report::FCC_477::save_fcc477map("part1_row_option_$i",$upload[$i]);
+my $date = parse_datetime($cgi->param('date')) || time;
+my @partnames = grep /^\w+$/, $cgi->param('parts');
+foreach my $partname (@partnames) {
+ $parts{$partname} ||= FS::Report::FCC_477->report( $partname,
+ date => $date,
+ agentnum => $agentnum
+ );
}
+$m->cache->set($session, \%parts, '1h');
-my @part2a_row_option = $cgi->param('part2a_row_option');
-for(my $i=0; $i < scalar(@part2a_row_option); $i++) {
- &FS::Report::FCC_477::save_fcc477map("part2a_row_option_$i",$part2a_row_option[$i]);
-}
+my $title = 'FCC Form 477 Data - ' . time2str('%b %o, %Y', $date);
-my @part2b_row_option = $cgi->param('part2b_row_option');
-for(my $i=0; $i < scalar(@part2b_row_option); $i++) {
- &FS::Report::FCC_477::save_fcc477map("part2b_row_option_$i",$part2b_row_option[$i]);
-}
+if ( $cgi->param('type') eq 'csv' ) {
+ my $partname = $partnames[0]; # ignore any beyond the first
+ my $data = $parts{$partname};
+ my $csv = Text::CSV_XS->new({ eol => "\r\n" }); # i think
-my $part5_report_option = $cgi->param('part5_report_option');
-if ( $part5_report_option ) {
- FS::Report::FCC_477::save_fcc477map('part5_report_option', $part5_report_option);
+ my $filename = time2str('%Y-%m-%d', $date) . '-'. $partname . '.csv';
+ http_header('Content-Type' => 'text/csv');
+ http_header('Content-Disposition' => qq(attachment;filename="$filename"));
+
+ $m->clear_buffer;
+
+ foreach my $row (@$data) {
+ $csv->combine(@$row);
+ $m->print($csv->string);
+ }
+ $m->abort;
}
-my $url_mangler = sub {
- my $part = shift;
- my $url = $cgi->url('-path_info' => 1, '-full' => 1);
- $url =~ s/477\./477part$part./;
- $url;
-};
-my @parts = qw( IA IIA IIB IV V VI_census );
+my $part_titles = FS::Report::FCC_477->parts;
</%init>
+<%def .header_fbd>
+ <TR CLASS="head">
+ <TD ROWSPAN=2>Census Block</TD>
+ <TD ROWSPAN=2>DBA Name</TD>
+ <TD ROWSPAN=2>Technology</TD>
+ <TD ROWSPAN=2>Consumer?</TD>
+ <TD COLSPAN=2>Advertised Speed (Mbps)</TD>
+ <TD ROWSPAN=2>Business?</TD>
+ <TD COLSPAN=2>Contractual Speed (Mbps)</TD>
+ </TR>
+ <TR CLASS="subhead">
+ <TD>Down</TD>
+ <TD>Up</TD>
+ <TD>Down</TD>
+ <TD>Up</TD>
+ </TR>
+</%def>
+<%def .header_fbs>
+ <TR CLASS="head">
+ <TD ROWSPAN=2>Census Tract</TD>
+ <TD ROWSPAN=2>Technology</TD>
+ <TD COLSPAN=2>Speed (Mbps)</TD>
+ <TD COLSPAN=2>Subscriptions</TD>
+ </TR>
+ <TR CLASS="subhead">
+ <TD>Down</TD>
+ <TD>Up</TD>
+ <TD>Total</TD>
+ <TD>Consumer</TD>
+ </TR>
+</%def>
+<%def .header_fvs>
+ <TR CLASS="head">
+ <TD ROWSPAN=2>Census Tract</TD>
+ <TD ROWSPAN=2>VoIP?</TD>
+ <TD COLSPAN=2>Lines/Subscriptions</TD>
+ </TR>
+ <TR CLASS="subhead">
+ <TD>Total</TD>
+ <TD>Consumer</TD>
+ </TR>
+</%def>
+<%def .header_lts>
+ <TR CLASS="head">
+ <TD ROWSPAN=3>State</TD>
+ <TD COLSPAN=2>Wholesale</TD>
+ <TD COLSPAN=12>End User Lines</TD>
+ </TR>
+ <TR CLASS="subhead">
+ <TD ROWSPAN=2>VGEs</TD>
+ <TD ROWSPAN=2>UNE-Ls</TD>
+
+ <TD ROWSPAN=2>Total</TD>
+ <TD ROWSPAN=2>With Broadband</TD>
+ <TD COLSPAN=2>Consumer</TD>
+ <TD COLSPAN=2>Business</TD>
+
+ <TD COLSPAN=3>Local Loop</TD>
+
+ <TD COLSPAN=3>Special Media</TD>
+ </TR>
+
+ <TR CLASS="subhead">
+ <TD> </TD>
+ <TD>+LD</TD>
+ <TD> </TD>
+ <TD>+LD</TD>
+
+ <TD>Owned</TD>
+ <TD>UNE-L</TD>
+ <TD>Resale</TD>
+
+ <TD>Fiber</TD>
+ <TD>Coaxial</TD>
+ <TD>Wireless</TD>
+ </TR>
+</%def>
+<%def .header_voip>
+ <TR CLASS="head">
+ <TD ROWSPAN=2>State</TD>
+ <TD COLSPAN=2>VoIP OTT</TD>
+ <TD COLSPAN=8>VoIP Non-OTT</TD>
+ </TR>
+ <TR CLASS="subhead">
+ <TD ROWSPAN=2>Total</TD>
+ <TD ROWSPAN=2>Consumer</TD>
+
+ <TD ROWSPAN=2>Total</TD>
+ <TD ROWSPAN=2>Consumer</TD>
+ <TD ROWSPAN=2>Bundled</TD>
+ <TD COLSPAN=5>Media Type</TD>
+ </TR>
+ <TR CLASS="subhead">
+ <TD>Copper</TD>
+ <TD>Fiber</TD>
+ <TD>Coaxial</TD>
+ <TD>Wireless</TD>
+ <TD>Other</TD>
+ </TR>
+</%def>
+<%def .header_mbs>
+%# unimplemented
+ <TR CLASS="head">
+ <TD ROWSPAN=2>State</TD>
+ <TD COLSPAN=2>Speed (Mbps)</TD>
+ <TD COLSPAN=2>Subscriptions</TD>
+ </TR>
+ <TR CLASS="subhead">
+ <TD>Down</TD>
+ <TD>Up</TD>
+ <TD>Total</TD>
+ <TD>Consumer</TD>
+ </TR>
+</%def>
+<%def .header_mvs>
+%# unimplemented
+ <TR CLASS="head">
+ <TD ROWSPAN=2>State</TD>
+ <TD COLSPAN=2>Subscriptions</TD>
+ </TR>
+ <TR CLASS="subhead">
+ <TD>Total</TD>
+ <TD>Direct</TD>
+ </TR>
+</%def>
+
+++ /dev/null
-% if ( $opt{'type'} eq 'xml' ) {
-%# container element <Part_IA_$tech> is in 477.html
-% my $col = 'a';
-% foreach ( @summary_row ) {
-% my $el = $xml_prefix . $col . '1'; # PartIA_Aa1, PartIA_Ab1, etc.
- <<% $el %>><% $_ %><<% "/$el" %>>
-% $col++;
-% }
-% foreach my $col_data ( @data ) {
-% my $row = 1;
-% foreach my $cell ( @$col_data ) {
-% my $el = $xml_prefix . $col . $row; # PartIA_Af1, PartIA_Af2...
-% if ( $cell->[0] > 0 ) {
- <<% $el %>><% $cell->[0] %><<% "/$el" %>>
-% if ( $percentages ) {
-% $el = $xml_percent . $col . $row; # Part_p_IA_Af1, ...
- <<% $el %>><% $cell->[1] %><<% "/$el" %>>
-% }
-% }
-% $row++;
-% } # foreach $cell
-% $col++;
-% } # foreach $col_data
-% } else { # not XML
-
-<H2><% $title %> totals</H2>
-<& /elements/table-grid.html &>
- <TR>
-% foreach ( 'Total Connections',
-% '% owned loop',
-% '% billed to end users',
-% '% residential',
-% '% residential > 200 kbps') {
- <TH WIDTH="20%"><% $_ |h %></TH>
-% }
- </TR>
- <TR CLASS="row0">
-% foreach ( @summary_row ) {
- <TD><% $_ %></TD>
-% }
- </TR>
-</TABLE>
-<H2><% $title %> breakdown by speed</H2>
-<TABLE CLASS="grid" CELLSPACING=0>
- <TR>
- <TH WIDTH="12%"></TH>
-% for (my $col = 0; $col < scalar(@download_option); $col++) {
- <TH WIDTH="11%">
- <% $FS::Report::FCC_477::download[$col] |h %>
- </TH>
-% }
- </TR>
-% for (my $row = 0; $row < scalar(@upload_option); $row++) {
- <TR CLASS="row<% $row % 2%>">
- <TD STYLE="text-align: left; font-weight: bold">
-% if ( $asymmetric ) {
- <% $FS::Report::FCC_477::upload[$row] |h %>
-% }
- </TD>
-% for (my $col = 0; $col < scalar(@download_option); $col++) {
- <TD>
-% if ( $data[$col][$row][0] > 0 ) {
- <% $data[$col][$row][0] %>
-% if ( $percentages ) {
- <BR><% $data[$col][$row][1] %>
-% }
-% }
- </TD>
-% } # for $col
- </TR>
-% } # for $row
-</TABLE>
-% }
-<%init>
-
-my $curuser = $FS::CurrentUser::CurrentUser;
-
-die "access denied"
- unless $curuser->access_right('List packages');
-
-my %opt = @_;
-my %search_hash;
-
-for ( qw(agentnum state) ) {
- $search_hash{$_} = $cgi->param($_) if $cgi->param($_);
-}
-$search_hash{'country'} = 'US';
-$search_hash{'classnum'} = [ $cgi->param('classnum') ];
-
-# arrays of report_option_ numbers, running parallel to
-# the download and upload speed arrays
-my @download_option = $cgi->param('part1_column_option');
-my @upload_option = $cgi->param('part1_row_option');
-
-my @technology_option = &FS::Report::FCC_477::parse_technology_option($cgi);
-
-my $total_count = 0;
-my $total_residential = 0;
-my $above_200 = 0;
-my $tech_code = $opt{tech_code};
-my $technology = $FS::Report::FCC_477::technology[$tech_code] || 'unknown';
-my $title = "Part IA $technology";
-my $xml_prefix = 'PartIA_'. chr(65 + $tech_code);
-my $xml_percent = 'Part_p_IA_'. chr(65 + $tech_code); # yes, seriously
-
-# whether to show the results as a matrix (upload speeds in rows) or a single
-# row
-my $asymmetric = 1;
-if ( $technology eq 'Symmetric xDSL' or $technology eq 'Other Wireline' ) {
- $asymmetric = 0;
- @upload_option = ( undef );
-}
-# whether to show residential percentages in each cell of the matrix
-my $percentages = ($technology eq 'Terrestrial Mobile Wireless');
-
-# as of date
-# FCC 477 instructions: "Only count connections that are in service."
-# So we count packages that were in active status as of the specified date,
-# not over any sort of range.
-$search_hash{'active'} = [ $opt{date}, $opt{date} ];
-
-my $query = FS::cust_pkg->search(\%search_hash);
-my $count_query = $query->{'count_query'};
-
-my $is_residential = " AND COALESCE(cust_main.company, '') = ''";
-my $has_option = sub {
- my $optionnum = shift;
- $optionnum =~ /^\d+$/ ?
- " AND EXISTS(
- SELECT 1 FROM part_pkg_option
- WHERE part_pkg_option.pkgpart = part_pkg.pkgpart
- AND optionname = 'report_option_$optionnum'
- AND optionvalue = '1'
- )" : '';
-};
-
-# limit to those that have technology option $tech_code
-$count_query .= $has_option->($technology_option[$tech_code]);
-
-my @data;
-for ( my $row = 0; $row < scalar @upload_option; $row++ ) {
- for ( my $col = 0; $col < scalar @download_option; $col++ ) {
-
- my $this_count_query = $count_query .
- $has_option->($upload_option[$row]) .
- $has_option->($download_option[$col]);
-
- my $count = FS::Record->scalar_sql($this_count_query);
- my $residential = FS::Record->scalar_sql($this_count_query . $is_residential);
-
- my $percent = sprintf('%.3f', $count ? 100 * $residential / $count : 0);
- $data[$col][$row] = [ $count, $percent ];
-
- $total_count += $count;
- $total_residential += $residential;
- $above_200 += $residential if $row > 0 or !$asymmetric;
- }
-}
-
-my $total_percentage =
- sprintf("%.3f", $total_count ? 100*$total_residential/$total_count : 0);
-
-my $above_200_percentage =
- sprintf("%.3f", $total_count ? 100*$above_200/$total_count : 0);
-
-my @summary_row = (
- $total_count,
- 100.00, # own local loop--consistent with previous practice, but probably wrong
- 100.00, # billed to end user--also wrong
- $total_percentage, # residential percentage
- $above_200_percentage,
-);
-
-</%init>
+++ /dev/null
-% if ( $cgi->param('_type') eq 'xml' ) {
-% my @cols = qw(a b c d);
-% for ( my $row = 0; $row < scalar(@rows); $row++ ) {
-% for my $col (0..3) {
-<PartII_<% $row + 1 %><% $cols[$col] %>>\
-<% $data[$col][$row] || 0 %>\
-</PartII_<% $row + 1 %><% $cols[$col] %>>
-% } #for $col
-% } #for $row
-% } else { # HTML mode
-% # fake up the search-html.html header
-<H2>Part IIA</H2>
-<TABLE>
- <TR><TD VALIGN="bottom"><BR></TD></TR>
- <TR><TD COLSPAN=2>
- <TABLE CLASS="grid" CELLSPACING=0>
- <TR>
-% foreach (@row1_headers) {
- <TH><% $_ %></TH>
-% }
- </TR>
-% my $row = 0;
-% foreach my $rowhead (@rows) {
- <TR CLASS="row<%$row % 2%>">
- <TD STYLE="text-align: left; font-weight: bold"><% $rowhead %></TD>
-% for my $col (0..3) {
- <TD>
-% if ( exists($data[$col][$row]) ) {
- <% $data[$col][$row] %>
-% }
- </TD>
-% } # for $col
- </TR>
-% $row++;
-% } #for $rowhead
- </TABLE>
- </TD></TR>
-</TABLE>
-% } #XML/HTML
-<%init>
-my $curuser = $FS::CurrentUser::CurrentUser;
-
-die "access denied"
- unless $curuser->access_right('List packages');
-
-my %opt = @_;
-my %search_hash = ();
-
-$search_hash{'agentnum'} = $cgi->param('agentnum');
-$search_hash{'state'} = $cgi->param('state');
-$search_hash{'classnum'} = [ $cgi->param('classnum') ];
-$search_hash{'active'} = [ $opt{date}, $opt{date} ];
-
-my @row_option;
-foreach ($cgi->param('part2a_row_option')) {
- push @row_option, (/^\d+$/ ? $_ : undef);
-}
-
-my $is_residential = " AND COALESCE(cust_main.company, '') = ''";
-my $has_report_option = sub {
- map {
- defined($row_option[$_]) ?
- " AND EXISTS(
- SELECT 1 FROM part_pkg_option
- WHERE part_pkg_option.pkgpart = part_pkg.pkgpart
- AND optionname = 'report_option_" . $row_option[$_]."'
- AND optionvalue = '1'
- )" : ' AND FALSE'
- } @_
-};
-
-# an arrayref for each column
-my @data;
-# get the skeleton of the query
-my $sql_query = FS::cust_pkg->search(\%search_hash);
-my $from_where = $sql_query->{'count_query'};
-$from_where =~ s/^SELECT COUNT\(\*\) //;
-
-# for row 1
-my $query_ds0 = "SELECT SUM(COALESCE(part_pkg.fcc_ds0s, pkg_class.fcc_ds0s, 0))
- $from_where AND fcc_voip_class = '4'"; # 4 = Local Exchange
-
-my $total_lines = FS::Record->scalar_sql($query_ds0);
-# always return zero for the number of resold lines, until an actual ILEC
-# starts using this report
-
-@data = (
- [ $total_lines ],
- [ 0 ],
- [ 0 ],
- [ 0 ],
-);
-
-my @row_conds = (
- $is_residential,
- $has_report_option->(0), # LD carrier
- ($has_report_option->(0))[0] . $is_residential,
- $has_report_option->(1..7),
-);
-
-if ( $total_lines > 0 ) {
- foreach (@row_conds) {
- my $sql = $query_ds0 . $_;
- my $lines = FS::Record->scalar_sql($sql);
- my $percent = sprintf('%.3f', 100 * $lines / $total_lines);
- push @{ $data[0] }, $percent;
- }
-}
-
-my @rows = (
- 'lines',
- '% residential',
- '% LD carrier',
- '% residential and LD',
- '% owned loops',
- '% unswitched UNE',
- '% UNE-P',
- '% UNE-P replacement',
- '% FTTP',
- '% coax',
- '% wireless',
-);
-
-my @row1_headers = (
- '',
- 'End user lines',
- 'UNE-P replacement',
- 'unswitched UNE',
- 'UNE-P',
-);
-
-</%init>
+++ /dev/null
-% if ( $cgi->param('_type') eq 'xml' ) {
-% my @cols = qw(a b c);
-% for ( my $row = 0; $row < scalar(@rows); $row++ ) {
-% for my $col (0..2) {
-% if ( exists($data[$col][$row]) ) {
-<PartII_<% $row + 1 %><% $cols[$col] %>>\
-<% $data[$col][$row] %>\
-</PartII_<% $row + 1 %><% $cols[$col] %>>
-% }
-% } #for $col
-% } #for $row
-% } else { # HTML mode
-% # fake up the search-html.html header
-<H2>Part IIB</H2>
-<TABLE>
- <TR><TD VALIGN="bottom"><BR></TD></TR>
- <TR><TD COLSPAN=2>
- <TABLE CLASS="grid" CELLSPACING=0>
- <TR>
-% foreach (@headers) {
- <TH><% $_ %></TH>
-% }
- </TR>
-% my $row = 0;
-% foreach my $rowhead (@rows) {
- <TR CLASS="row<% $row % 2 %>">
- <TD STYLE="text-align: left; font-weight: bold"><% $rowhead %></TD>
-% for my $col (0..2) {
- <TD>
-% if ( exists($data[$col][$row]) ) {
- <% $data[$col][$row] %>
-% }
- </TD>
-% } # for $col
- </TR>
-% $row++;
-% } #for $rowhead
- </TABLE>
- </TD></TR>
-</TABLE>
-% } #XML/HTML
-<%init>
-
-my $curuser = $FS::CurrentUser::CurrentUser;
-
-die "access denied"
- unless $curuser->access_right('List packages');
-
-my %opt = @_;
-my %search_hash = ();
-
-$search_hash{'agentnum'} = $cgi->param('agentnum');
-$search_hash{'state'} = $cgi->param('state');
-$search_hash{'classnum'} = [ $cgi->param('classnum') ];
-$search_hash{'active'} = [ $opt{date}, $opt{date} ];
-
-my @row_option;
-foreach ($cgi->param('part2b_row_option')) {
- push @row_option, (/^\d+$/ ? $_ : undef);
-}
-
-my $is_residential = " AND COALESCE(cust_main.company, '') = ''";
-my $has_report_option = sub {
- map {
- defined($row_option[$_]) ?
- " AND EXISTS(
- SELECT 1 FROM part_pkg_option
- WHERE part_pkg_option.pkgpart = part_pkg.pkgpart
- AND optionname = 'report_option_" . $row_option[$_]."'
- AND optionvalue = '1'
- )" : ' AND FALSE'
- } @_
-};
-
-# an arrayref for each column
-my @data;
-# get the skeleton of the query
-my $sql_query = FS::cust_pkg->search(\%search_hash);
-my $from_where = $sql_query->{'count_query'};
-$from_where =~ s/^SELECT COUNT\(\*\) //;
-# columns 1 and 2
-my $query_ds0 = "SELECT SUM(COALESCE(part_pkg.fcc_ds0s, pkg_class.fcc_ds0s, 0))
- $from_where";
-# column 3
-my $query_custnum = "SELECT COUNT(DISTINCT cust_pkg.custnum) $from_where";
-
-my @base_queries = ($query_ds0, $query_ds0, $query_custnum);
-my @col_conds = (
- # column 1
- [
- '',
- $is_residential,
- $has_report_option->(0), # nomadic
- ],
- # column 2
- [
- '',
- $is_residential,
- $has_report_option->(0..5),
- ],
- # column 3
- [
- ''
- ]
-);
-
-my $col = 0;
-foreach (@col_conds) {
- my @col_data;
- my $row = 0;
- foreach my $cond (@{ $col_conds[$col] }) {
- # three parts: the select expression, the VoIP class (column selection),
- # and the row selection
- my $query = $base_queries[$col] .
- " AND part_pkg.fcc_voip_class = '".($col+1)."'
- $cond";
- my $count = FS::Record->scalar_sql($query) || 0;
- if ( $row == 0 ) {
- $col_data[$row] = $count; # the raw count
- } else {
- # the rows that are percentages of the raw count
- if ( $col_data[0] == 0 ) {
- # 0 out of 0 is not 0%, but supposedly this works
- $col_data[$row] = '0.000';
- } else {
- $col_data[$row] = sprintf('%.3f', 100 * $count / $col_data[0]);
- }
- $col_data[$row] .= '%' unless $cgi->param('_type') eq 'xml';
- } #if $row == 0
- $row++;
- }
- $data[$col] = \@col_data;
- $col++;
-}
-
-
-my @rows = (
- 'total number',
- '% residential',
- '% nomadic',
- '% copper',
- '% FTTP',
- '% coax',
- '% wireless',
- '% other broadband',
-);
-
-my @headers = (
- '',
- 'without broadband',
- 'with broadband',
- 'wholesale',
-);
-
-</%init>
+++ /dev/null
-%if ( $cgi->param('_type') eq 'html' || $cgi->param('_type') eq 'html-print' ) {
-<H2>Part IV</H2>
-%} elsif ( $cgi->param('_type') eq 'xml' ) {
-<notes>
-%}
-<% $cgi->param('notes') |h %>
-%if ( $cgi->param('_type') eq 'xml' ) {
-</notes>
-%}
-<%init>
-
-my $curuser = $FS::CurrentUser::CurrentUser;
-
-die "access denied"
- unless $curuser->access_right('List packages');
-
-</%init>
+++ /dev/null
-% if ( $cgi->param('_type') =~ /^xml$/ ) {
-<zip_codes>
-% }
-<& elements/search.html,
- 'html_init' => $html_init,
- 'name' => 'zip code',
- 'query' => $sql_query,
- 'count_query' => $count_query,
- 'nohtmlheader' => 1,
- 'disable_total' => 1,
- 'header' => [ 'zip code' ],
- 'xml_elements' => [ 'zip codes' ],
- 'no_field_elements' => 1,
- 'fields' => [ 'zip' ],
- 'url' => $opt{url} || '',
- 'really_disable_download' => 1,
-
-
-&>
-% if ( $cgi->param('_type') =~ /^xml$/ ) {
-</zip_codes>
-% }
-<%init>
-
-my $curuser = $FS::CurrentUser::CurrentUser;
-
-die "access denied"
- unless $curuser->access_right('List packages');
-
-my %opt = @_;
-my $html_init = '<H2>Part V</H2>';
-my %search_hash = ();
-my @sql_query = ();
-my @count_query = ();
-
-for ( qw(agentnum state) ) {
- $search_hash{$_} = $cgi->param($_) if $cgi->param($_);
-}
-$search_hash{'country'} = 'US';
-$search_hash{'classnum'} = [ $cgi->param('classnum') ];
-$search_hash{report_option} = $cgi->param('part5_report_option')
- if $cgi->param('part5_report_option');
-$search_hash{'active'} = [ $opt{date}, $opt{date} ];
-
-my $sql_query = FS::cust_pkg->search( { %search_hash,
- 'fcc_line' => 1,
- 'select_zip5' => 1,
- }
- );
-my $count_query = delete($sql_query->{'count_query'});
-$count_query =~ s/COUNT\(\*\)/count(DISTINCT substr(zip,1,5))/;
-$count_query =~ s/ORDER BY [.\w]+//;
-
-</%init>
+++ /dev/null
-<& elements/search.html,
- 'html_init' => '<H2>Part VI</H2>',
- 'html_foot' => $html_foot,
- 'name' => 'regions',
- 'query' => [ @sql_query ],
- 'count_query' => $count_query,
- 'order_by' => 'ORDER BY censustract',
- 'avoid_quote' => 1,
- 'no_csv_header' => 1,
- 'nohtmlheader' => 1,
- 'header' => \@header,
- 'xml_elements' => [
- 'county_fips',
- 'census_tract',
- 'upload_rate_code',
- 'download_rate_code',
- 'technology_code',
- 'technology_code_other',
- 'value',
- 'percentage',
- ],
- 'fields' => \@fields,
- 'links' => \@links,
- 'url' => $opt{url} || '',
- 'xml_row_element' => 'Datarow',
- 'really_disable_download' => 1,
- 'filename' => $opt{filename},
-&>
-<%init>
-
-my $curuser = $FS::CurrentUser::CurrentUser;
-
-die "access denied"
- unless $curuser->access_right('List packages');
-
-my %opt = @_;
-
-my %state_hash = ();
-my %state_pkgcount = ();
-
-my @header = ();
-my @fields = ();
-my @links = ();
-my $num = ($cgi->param('offset') =~ /^(\d+)$/) ? $1 : 0;
-unless ( $cgi->param('_type') eq 'xml' ) {
- push @header, '#';
- push @fields, sub { ++$num };
- push @links, '';
-}
-push @header,
- 'County code',
- 'Census tract code',
- 'Upload rate',
- 'Download rate',
- 'Technology code',
- 'Technology code other',
- 'Quantity',
- 'Percentage residential',
-;
-push @fields,
- sub { my $row = shift;
- my $state = substr($row->censustract, 0, 2);
- $state_hash{$state}++;
- substr($row->censustract, 2, 3) || 'None';
- },
- sub { my $row = shift;
- substr($row->censustract, 5) || 'None';
- },
- 'upload',
- 'download',
- 'technology_code',
- sub { $cgi->param('_type') eq 'xml' ? '0' : '' },#broken
- sub { my $row = shift;
- my $state = substr($row->censustract, 0, 2);
- $state_pkgcount{$state} += $row->quantity;
- $row->quantity;
- },
- sub { my $row = shift; sprintf "%.3f", $row->residential },
-;
-
-my %search_hash = ();
-my @sql_query = ();
-
-for ( qw(agentnum state) ) {
- $search_hash{$_} = $cgi->param($_) if $cgi->param($_);
-}
-
-$search_hash{'active'} = [ $opt{date}, $opt{date} ];
-$search_hash{'country'} = 'US';
-$search_hash{'classnum'} = [ $cgi->param('classnum') ]
- if grep { $_ eq 'classnum' } $cgi->param;
-
-my @column_option = grep { /^\d+$/ } $cgi->param('part1_column_option')
- if $cgi->param('part1_column_option');
-
-my @row_option = grep { /^\d+$/ } $cgi->param('part1_row_option')
- if $cgi->param('part1_row_option');
-
-my @technology_option = &FS::Report::FCC_477::parse_technology_option($cgi);
-
-my $rowcount = 1;
-foreach my $row ( @row_option ) {
- my $columncount = 2;
- foreach my $column ( @column_option ) {
- my $tech_code = 0;
- foreach my $technology ( @technology_option ) {
- $tech_code++;
- next unless $technology;
- my @report_option = ();
- push @report_option, $row if $row;
- push @report_option, $column if $column;
- push @report_option, $technology;
- my $report_option = join(',', @report_option) if @report_option;
-
- my $sql_query = FS::cust_pkg->search(
- { %search_hash,
- ($report_option ? ( 'report_option' => $report_option ) : () ),
- }
- );
- my $extracolumns = "$rowcount AS upload, $columncount AS download, $tech_code as technology_code";
- my $percent = "CASE WHEN count(*) > 0 THEN 100-100*cast(count(cust_main.company) as numeric)/cast(count(*) as numeric) ELSE cast(0 as numeric) END AS residential";
- $sql_query->{select} = "count(*) AS quantity, $extracolumns, cust_location.censustract, $percent";
- $sql_query->{order_by} = " GROUP BY cust_location.censustract ";
- push @sql_query, $sql_query;
- }
- $columncount++;
- }
- $rowcount++;
-}
-
-my $count_query = 'SELECT count(*) FROM ( ('.
- join( ') UNION ALL (',
- map { my $addl_from = $_->{addl_from};
- my $extra_sql = $_->{extra_sql};
- my $order_by = $_->{order_by};
- "SELECT cust_location.censustract from cust_pkg $addl_from
- $extra_sql $order_by";
- }
- @sql_query
- ). ') ) AS foo';
-
-
-my $link = 'cust_pkg.cgi?'. join(';',
- map { my $key = $_;
- my @values = ref($search_hash{$_})
- ? @{ $search_hash{$_} }
- : $search_hash{$_};
- map { "$key=$_" } @values;
- }
- keys %search_hash
- ). ';';
-
-my $link_suffix = sub { my $row = shift;
- my $result = 'censustract='. $row->censustract. ';';
- my @ro = grep $_,
- @row_option[$row->upload - 1],
- @column_option[$row->download - 2],
- @technology_option[$row->technology_code - 1],
- ;
- $result .= 'report_option='. join(',',@ro) if @ro;
- $result;
- };
-
-for (1..8) { push @links, [ $link, $link_suffix ]; }
-
-
-my $html_foot = sub {
- if (scalar(keys %state_hash) > 1) {
-
- my $roa_r = join(',', grep $_, @row_option);
- $roa_r = ";report_option_any_r=$roa_r" if $roa_r;
-
- my $roa_c = join(',', grep $_, @column_option);
- $roa_c = ";report_option_any_c=$roa_c" if $roa_c;
-
- my $roa_t = join(',', grep $_, @technology_option);
- $roa_t = ";report_option_any_t=$roa_t" if $roa_t;
-
- '<BR><B>WARNING: multiple states found</B><BR>'.
- '<TABLE BORDER=0>'. #nicer formatting someday
- join('', map { '<TR>'.
- '<TD>'.
- ( &FS::Report::FCC_477::statenum2state($_) || 'None' ).
- '</TD>'.
- '<TD>'.
- qq(<A HREF="${link}censustract2=$_$roa_r$roa_c$roa_t">).
- $state_pkgcount{$_}.
- ' packages</A> in '.
- $state_hash{$_}. ' census tracts'.
- '</TD>'.
- '</TR>';
- }
- keys %state_hash
- ).
- '</TABLE>';
- } else {
- '';
- }
-};
-
-</%init>
--- /dev/null
+% if ( $type eq 'xml' ) {
+% $filename = "fcc_477_$state" . '_' . time2str('%Y%m%d', $date) . '.xml';
+% http_header('Content-Type' => 'application/XML' ); # So saith RFC 4180
+% http_header('Content-Disposition' => 'attachment;filename="'.$filename.'"');
+<?xml version="1.0" encoding="ISO-8859-1"?>
+<Form_477_submission xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="https://specialreports.fcc.gov/wcb/Form477/XMLSchema-instance/form_477_upload_Schema.xsd" >
+% } else { #html
+<& /elements/header.html, "FCC Form 477 Results - $state" &>
+%# XXX when we stop supporting IE8, add this to freeside.css using :nth-child
+%# selectors, and remove it from everywhere else
+<STYLE TYPE="text/css">
+.grid TH { background-color: #cccccc; padding: 0px 3px 2px; text-align: right }
+.row0 TD { background-color: #eeeeee; padding: 0px 3px 2px; text-align: right }
+.row1 TD { background-color: #ffffff; padding: 0px 3px 2px; text-align: right }
+</STYLE>
+
+<TABLE WIDTH="100%">
+ <TR>
+ <TD></TD>
+ <TD ALIGN="right" CLASS="noprint">
+ Download full results<BR>
+% $cgi->param('_type', 'xml');
+ as <A HREF="<% $cgi->self_url %>">XML file</A><BR>
+
+% $cgi->param('_type', 'html-print');
+ as <A HREF="<% $cgi->self_url %>">printable copy</A>
+
+ </TD>
+% $cgi->param('_type', $type );
+ </TR>
+</TABLE>
+% } #html
+% foreach my $part ( @parts ) {
+% if ( $part{$part} ) {
+%
+% if ( $part eq 'V' ) {
+% next unless ( $part{'IIA'} || $part{'IIB'} );
+% }
+%
+% if ( $part eq 'VI_census' ) {
+% next unless $part{'IA'};
+% }
+%
+% my @reports = ();
+% if ( $part eq 'IA' ) {
+% for ( my $tech = 0; $tech < scalar(@technology_option); $tech++ ) {
+% next unless $technology_option[$tech];
+% my $url = &{$url_mangler}($part);
+% if ( $type eq 'xml' ) {
+<<% 'Part_IA_'. chr(65 + $tech) %>>
+% }
+<& "477part${part}.html",
+ 'tech_code' => $tech,
+ 'url' => $url,
+ 'type' => $type,
+ 'date' => $date,
+&>
+% if ( $type eq 'xml' ) {
+</<% 'Part_IA_'. chr(65 + $tech) %>>
+% }
+% }
+% } else { # not part IA
+% if ( $type eq 'xml' ) {
+<<% 'Part_'. $part %>>
+% }
+% my $url = &{$url_mangler}($part);
+<& "477part${part}.html",
+ 'url' => $url,
+ 'date' => $date,
+ 'filename' => $filename,
+&>
+% if ( $type eq 'xml' ) {
+</<% 'Part_'. $part %>>
+% }
+% }
+% }
+% }
+%
+% if ( $type eq 'xml' ) {
+</Form_477_submission>
+% } else {
+<& /elements/footer.html &>
+% }
+<%init>
+
+my $curuser = $FS::CurrentUser::CurrentUser;
+
+die "access denied"
+ unless $curuser->access_right('List packages');
+
+my $date = $cgi->param('date') ? parse_datetime($cgi->param('date'))
+ : time;
+
+my $state = uc($cgi->param('state'));
+$state =~ /^[A-Z]{2}$/ or die "illegal state: $state";
+
+my %part = map { $_ => 1 } grep { /^\w+$/ } $cgi->param('part');
+my $type = $cgi->param('_type') || 'html';
+my $filename;
+my @technology_option = &FS::Report::FCC_477::parse_technology_option($cgi,1);
+
+# save upload and download mappings
+my @download = $cgi->param('part1_column_option');
+my @upload = $cgi->param('part1_row_option');
+for(my $i=0; $i < scalar(@download); $i++) {
+ &FS::Report::FCC_477::save_fcc477map("part1_column_option_$i",$download[$i]);
+}
+for(my $i=0; $i < scalar(@upload); $i++) {
+ &FS::Report::FCC_477::save_fcc477map("part1_row_option_$i",$upload[$i]);
+}
+
+my @part2a_row_option = $cgi->param('part2a_row_option');
+for(my $i=0; $i < scalar(@part2a_row_option); $i++) {
+ &FS::Report::FCC_477::save_fcc477map("part2a_row_option_$i",$part2a_row_option[$i]);
+}
+
+my @part2b_row_option = $cgi->param('part2b_row_option');
+for(my $i=0; $i < scalar(@part2b_row_option); $i++) {
+ &FS::Report::FCC_477::save_fcc477map("part2b_row_option_$i",$part2b_row_option[$i]);
+}
+
+my $part5_report_option = $cgi->param('part5_report_option');
+if ( $part5_report_option ) {
+ FS::Report::FCC_477::save_fcc477map('part5_report_option', $part5_report_option);
+}
+
+my $url_mangler = sub {
+ my $part = shift;
+ my $url = $cgi->url('-path_info' => 1, '-full' => 1);
+ $url =~ s/477\./477part$part./;
+ $url;
+};
+my @parts = qw( IA IIA IIB IV V VI_census );
+
+</%init>
--- /dev/null
+% if ( $opt{'type'} eq 'xml' ) {
+%# container element <Part_IA_$tech> is in 477.html
+% my $col = 'a';
+% foreach ( @summary_row ) {
+% my $el = $xml_prefix . $col . '1'; # PartIA_Aa1, PartIA_Ab1, etc.
+ <<% $el %>><% $_ %><<% "/$el" %>>
+% $col++;
+% }
+% foreach my $col_data ( @data ) {
+% my $row = 1;
+% foreach my $cell ( @$col_data ) {
+% my $el = $xml_prefix . $col . $row; # PartIA_Af1, PartIA_Af2...
+% if ( $cell->[0] > 0 ) {
+ <<% $el %>><% $cell->[0] %><<% "/$el" %>>
+% if ( $percentages ) {
+% $el = $xml_percent . $col . $row; # Part_p_IA_Af1, ...
+ <<% $el %>><% $cell->[1] %><<% "/$el" %>>
+% }
+% }
+% $row++;
+% } # foreach $cell
+% $col++;
+% } # foreach $col_data
+% } else { # not XML
+
+<H2><% $title %> totals</H2>
+<& /elements/table-grid.html &>
+ <TR>
+% foreach ( 'Total Connections',
+% '% owned loop',
+% '% billed to end users',
+% '% residential',
+% '% residential > 200 kbps') {
+ <TH WIDTH="20%"><% $_ |h %></TH>
+% }
+ </TR>
+ <TR CLASS="row0">
+% foreach ( @summary_row ) {
+ <TD><% $_ %></TD>
+% }
+ </TR>
+</TABLE>
+<H2><% $title %> breakdown by speed</H2>
+<TABLE CLASS="grid" CELLSPACING=0>
+ <TR>
+ <TH WIDTH="12%"></TH>
+% for (my $col = 0; $col < scalar(@download_option); $col++) {
+ <TH WIDTH="11%">
+ <% $FS::Report::FCC_477::download[$col] |h %>
+ </TH>
+% }
+ </TR>
+% for (my $row = 0; $row < scalar(@upload_option); $row++) {
+ <TR CLASS="row<% $row % 2%>">
+ <TD STYLE="text-align: left; font-weight: bold">
+% if ( $asymmetric ) {
+ <% $FS::Report::FCC_477::upload[$row] |h %>
+% }
+ </TD>
+% for (my $col = 0; $col < scalar(@download_option); $col++) {
+ <TD>
+% if ( $data[$col][$row][0] > 0 ) {
+ <% $data[$col][$row][0] %>
+% if ( $percentages ) {
+ <BR><% $data[$col][$row][1] %>
+% }
+% }
+ </TD>
+% } # for $col
+ </TR>
+% } # for $row
+</TABLE>
+% }
+<%init>
+
+my $curuser = $FS::CurrentUser::CurrentUser;
+
+die "access denied"
+ unless $curuser->access_right('List packages');
+
+my %opt = @_;
+my %search_hash;
+
+for ( qw(agentnum state) ) {
+ $search_hash{$_} = $cgi->param($_) if $cgi->param($_);
+} # note that separation by state is no longer required after July 2014
+$search_hash{'country'} = 'US';
+$search_hash{'classnum'} = [ $cgi->param('classnum') ];
+
+my $info = FS::part_pkg_fcc_option->info;
+
+
+
+# arrays of report_option_ numbers, running parallel to
+# the download and upload speed arrays
+my @download_option = $cgi->param('part1_column_option');
+my @upload_option = $cgi->param('part1_row_option');
+
+my @technology_option = &FS::Report::FCC_477::parse_technology_option($cgi);
+
+my $total_count = 0;
+my $total_residential = 0;
+my $above_200 = 0;
+my $tech_code = $opt{tech_code};
+my $technology = $FS::Report::FCC_477::technology[$tech_code] || 'unknown';
+my $title = "Part IA $technology";
+my $xml_prefix = 'PartIA_'. chr(65 + $tech_code);
+my $xml_percent = 'Part_p_IA_'. chr(65 + $tech_code); # yes, seriously
+
+# whether to show the results as a matrix (upload speeds in rows) or a single
+# row
+my $asymmetric = 1;
+if ( $technology eq 'Symmetric xDSL' or $technology eq 'Other Wireline' ) {
+ $asymmetric = 0;
+ @upload_option = ( undef );
+}
+# whether to show residential percentages in each cell of the matrix
+my $percentages = ($technology eq 'Terrestrial Mobile Wireless');
+
+# as of date
+# FCC 477 instructions: "Only count connections that are in service."
+# So we count packages that were in active status as of the specified date,
+# not over any sort of range.
+$search_hash{'active'} = [ $opt{date}, $opt{date} ];
+
+my $query = FS::cust_pkg->search(\%search_hash);
+my $count_query = $query->{'count_query'};
+
+my $is_residential = " AND COALESCE(cust_main.company, '') = ''";
+my $has_option = sub {
+ my $optionnum = shift;
+ $optionnum =~ /^\d+$/ ?
+ " AND EXISTS(
+ SELECT 1 FROM part_pkg_option
+ WHERE part_pkg_option.pkgpart = part_pkg.pkgpart
+ AND optionname = 'report_option_$optionnum'
+ AND optionvalue = '1'
+ )" : '';
+};
+
+# limit to those that have technology option $tech_code
+$count_query .= $has_option->($technology_option[$tech_code]);
+
+my @data;
+for ( my $row = 0; $row < scalar @upload_option; $row++ ) {
+ for ( my $col = 0; $col < scalar @download_option; $col++ ) {
+
+ my $this_count_query = $count_query .
+ $has_option->($upload_option[$row]) .
+ $has_option->($download_option[$col]);
+
+ my $count = FS::Record->scalar_sql($this_count_query);
+ my $residential = FS::Record->scalar_sql($this_count_query . $is_residential);
+
+ my $percent = sprintf('%.3f', $count ? 100 * $residential / $count : 0);
+ $data[$col][$row] = [ $count, $percent ];
+
+ $total_count += $count;
+ $total_residential += $residential;
+ $above_200 += $residential if $row > 0 or !$asymmetric;
+ }
+}
+
+my $total_percentage =
+ sprintf("%.3f", $total_count ? 100*$total_residential/$total_count : 0);
+
+my $above_200_percentage =
+ sprintf("%.3f", $total_count ? 100*$above_200/$total_count : 0);
+
+my @summary_row = (
+ $total_count,
+ 100.00, # own local loop--consistent with previous practice, but probably wrong
+ 100.00, # billed to end user--also wrong
+ $total_percentage, # residential percentage
+ $above_200_percentage,
+);
+
+</%init>
--- /dev/null
+% if ( $cgi->param('_type') eq 'xml' ) {
+% my @cols = qw(a b c d);
+% for ( my $row = 0; $row < scalar(@rows); $row++ ) {
+% for my $col (0..3) {
+<PartII_<% $row + 1 %><% $cols[$col] %>>\
+<% $data[$col][$row] || 0 %>\
+</PartII_<% $row + 1 %><% $cols[$col] %>>
+% } #for $col
+% } #for $row
+% } else { # HTML mode
+% # fake up the search-html.html header
+<H2>Part IIA</H2>
+<TABLE>
+ <TR><TD VALIGN="bottom"><BR></TD></TR>
+ <TR><TD COLSPAN=2>
+ <TABLE CLASS="grid" CELLSPACING=0>
+ <TR>
+% foreach (@row1_headers) {
+ <TH><% $_ %></TH>
+% }
+ </TR>
+% my $row = 0;
+% foreach my $rowhead (@rows) {
+ <TR CLASS="row<%$row % 2%>">
+ <TD STYLE="text-align: left; font-weight: bold"><% $rowhead %></TD>
+% for my $col (0..3) {
+ <TD>
+% if ( exists($data[$col][$row]) ) {
+ <% $data[$col][$row] %>
+% }
+ </TD>
+% } # for $col
+ </TR>
+% $row++;
+% } #for $rowhead
+ </TABLE>
+ </TD></TR>
+</TABLE>
+% } #XML/HTML
+<%init>
+my $curuser = $FS::CurrentUser::CurrentUser;
+
+die "access denied"
+ unless $curuser->access_right('List packages');
+
+my %opt = @_;
+my %search_hash = ();
+
+$search_hash{'agentnum'} = $cgi->param('agentnum');
+$search_hash{'state'} = $cgi->param('state');
+$search_hash{'classnum'} = [ $cgi->param('classnum') ];
+$search_hash{'active'} = [ $opt{date}, $opt{date} ];
+
+my @row_option;
+foreach ($cgi->param('part2a_row_option')) {
+ push @row_option, (/^\d+$/ ? $_ : undef);
+}
+
+my $is_residential = " AND COALESCE(cust_main.company, '') = ''";
+my $has_report_option = sub {
+ map {
+ defined($row_option[$_]) ?
+ " AND EXISTS(
+ SELECT 1 FROM part_pkg_option
+ WHERE part_pkg_option.pkgpart = part_pkg.pkgpart
+ AND optionname = 'report_option_" . $row_option[$_]."'
+ AND optionvalue = '1'
+ )" : ' AND FALSE'
+ } @_
+};
+
+# an arrayref for each column
+my @data;
+# get the skeleton of the query
+my $sql_query = FS::cust_pkg->search(\%search_hash);
+my $from_where = $sql_query->{'count_query'};
+$from_where =~ s/^SELECT COUNT\(\*\) //;
+
+# for row 1
+my $query_ds0 = "SELECT SUM(COALESCE(part_pkg.fcc_ds0s, pkg_class.fcc_ds0s, 0))
+ $from_where AND fcc_voip_class = '4'"; # 4 = Local Exchange
+
+my $total_lines = FS::Record->scalar_sql($query_ds0);
+# always return zero for the number of resold lines, until an actual ILEC
+# starts using this report
+
+@data = (
+ [ $total_lines ],
+ [ 0 ],
+ [ 0 ],
+ [ 0 ],
+);
+
+my @row_conds = (
+ $is_residential,
+ $has_report_option->(0), # LD carrier
+ ($has_report_option->(0))[0] . $is_residential,
+ $has_report_option->(1..7),
+);
+
+if ( $total_lines > 0 ) {
+ foreach (@row_conds) {
+ my $sql = $query_ds0 . $_;
+ my $lines = FS::Record->scalar_sql($sql);
+ my $percent = sprintf('%.3f', 100 * $lines / $total_lines);
+ push @{ $data[0] }, $percent;
+ }
+}
+
+my @rows = (
+ 'lines',
+ '% residential',
+ '% LD carrier',
+ '% residential and LD',
+ '% owned loops',
+ '% unswitched UNE',
+ '% UNE-P',
+ '% UNE-P replacement',
+ '% FTTP',
+ '% coax',
+ '% wireless',
+);
+
+my @row1_headers = (
+ '',
+ 'End user lines',
+ 'UNE-P replacement',
+ 'unswitched UNE',
+ 'UNE-P',
+);
+
+</%init>
--- /dev/null
+% if ( $cgi->param('_type') eq 'xml' ) {
+% my @cols = qw(a b c);
+% for ( my $row = 0; $row < scalar(@rows); $row++ ) {
+% for my $col (0..2) {
+% if ( exists($data[$col][$row]) ) {
+<PartII_<% $row + 1 %><% $cols[$col] %>>\
+<% $data[$col][$row] %>\
+</PartII_<% $row + 1 %><% $cols[$col] %>>
+% }
+% } #for $col
+% } #for $row
+% } else { # HTML mode
+% # fake up the search-html.html header
+<H2>Part IIB</H2>
+<TABLE>
+ <TR><TD VALIGN="bottom"><BR></TD></TR>
+ <TR><TD COLSPAN=2>
+ <TABLE CLASS="grid" CELLSPACING=0>
+ <TR>
+% foreach (@headers) {
+ <TH><% $_ %></TH>
+% }
+ </TR>
+% my $row = 0;
+% foreach my $rowhead (@rows) {
+ <TR CLASS="row<% $row % 2 %>">
+ <TD STYLE="text-align: left; font-weight: bold"><% $rowhead %></TD>
+% for my $col (0..2) {
+ <TD>
+% if ( exists($data[$col][$row]) ) {
+ <% $data[$col][$row] %>
+% }
+ </TD>
+% } # for $col
+ </TR>
+% $row++;
+% } #for $rowhead
+ </TABLE>
+ </TD></TR>
+</TABLE>
+% } #XML/HTML
+<%init>
+
+my $curuser = $FS::CurrentUser::CurrentUser;
+
+die "access denied"
+ unless $curuser->access_right('List packages');
+
+my %opt = @_;
+my %search_hash = ();
+
+$search_hash{'agentnum'} = $cgi->param('agentnum');
+$search_hash{'state'} = $cgi->param('state');
+$search_hash{'classnum'} = [ $cgi->param('classnum') ];
+$search_hash{'active'} = [ $opt{date}, $opt{date} ];
+
+my @row_option;
+foreach ($cgi->param('part2b_row_option')) {
+ push @row_option, (/^\d+$/ ? $_ : undef);
+}
+
+my $is_residential = " AND COALESCE(cust_main.company, '') = ''";
+my $has_report_option = sub {
+ map {
+ defined($row_option[$_]) ?
+ " AND EXISTS(
+ SELECT 1 FROM part_pkg_option
+ WHERE part_pkg_option.pkgpart = part_pkg.pkgpart
+ AND optionname = 'report_option_" . $row_option[$_]."'
+ AND optionvalue = '1'
+ )" : ' AND FALSE'
+ } @_
+};
+
+# an arrayref for each column
+my @data;
+# get the skeleton of the query
+my $sql_query = FS::cust_pkg->search(\%search_hash);
+my $from_where = $sql_query->{'count_query'};
+$from_where =~ s/^SELECT COUNT\(\*\) //;
+# columns 1 and 2
+my $query_ds0 = "SELECT SUM(COALESCE(part_pkg.fcc_ds0s, pkg_class.fcc_ds0s, 0))
+ $from_where";
+# column 3
+my $query_custnum = "SELECT COUNT(DISTINCT cust_pkg.custnum) $from_where";
+
+my @base_queries = ($query_ds0, $query_ds0, $query_custnum);
+my @col_conds = (
+ # column 1
+ [
+ '',
+ $is_residential,
+ $has_report_option->(0), # nomadic
+ ],
+ # column 2
+ [
+ '',
+ $is_residential,
+ $has_report_option->(0..5),
+ ],
+ # column 3
+ [
+ ''
+ ]
+);
+
+my $col = 0;
+foreach (@col_conds) {
+ my @col_data;
+ my $row = 0;
+ foreach my $cond (@{ $col_conds[$col] }) {
+ # three parts: the select expression, the VoIP class (column selection),
+ # and the row selection
+ my $query = $base_queries[$col] .
+ " AND part_pkg.fcc_voip_class = '".($col+1)."'
+ $cond";
+ my $count = FS::Record->scalar_sql($query) || 0;
+ if ( $row == 0 ) {
+ $col_data[$row] = $count; # the raw count
+ } else {
+ # the rows that are percentages of the raw count
+ if ( $col_data[0] == 0 ) {
+ # 0 out of 0 is not 0%, but supposedly this works
+ $col_data[$row] = '0.000';
+ } else {
+ $col_data[$row] = sprintf('%.3f', 100 * $count / $col_data[0]);
+ }
+ $col_data[$row] .= '%' unless $cgi->param('_type') eq 'xml';
+ } #if $row == 0
+ $row++;
+ }
+ $data[$col] = \@col_data;
+ $col++;
+}
+
+
+my @rows = (
+ 'total number',
+ '% residential',
+ '% nomadic',
+ '% copper',
+ '% FTTP',
+ '% coax',
+ '% wireless',
+ '% other broadband',
+);
+
+my @headers = (
+ '',
+ 'without broadband',
+ 'with broadband',
+ 'wholesale',
+);
+
+</%init>
--- /dev/null
+%if ( $cgi->param('_type') eq 'html' || $cgi->param('_type') eq 'html-print' ) {
+<H2>Part IV</H2>
+%} elsif ( $cgi->param('_type') eq 'xml' ) {
+<notes>
+%}
+<% $cgi->param('notes') |h %>
+%if ( $cgi->param('_type') eq 'xml' ) {
+</notes>
+%}
+<%init>
+
+my $curuser = $FS::CurrentUser::CurrentUser;
+
+die "access denied"
+ unless $curuser->access_right('List packages');
+
+</%init>
--- /dev/null
+% if ( $cgi->param('_type') =~ /^xml$/ ) {
+<zip_codes>
+% }
+<& /search/elements/search.html,
+ 'html_init' => $html_init,
+ 'name' => 'zip code',
+ 'query' => $sql_query,
+ 'count_query' => $count_query,
+ 'nohtmlheader' => 1,
+ 'disable_total' => 1,
+ 'header' => [ 'zip code' ],
+ 'xml_elements' => [ 'zip codes' ],
+ 'no_field_elements' => 1,
+ 'fields' => [ 'zip' ],
+ 'url' => $opt{url} || '',
+ 'really_disable_download' => 1,
+
+
+&>
+% if ( $cgi->param('_type') =~ /^xml$/ ) {
+</zip_codes>
+% }
+<%init>
+
+my $curuser = $FS::CurrentUser::CurrentUser;
+
+die "access denied"
+ unless $curuser->access_right('List packages');
+
+my %opt = @_;
+my $html_init = '<H2>Part V</H2>';
+my %search_hash = ();
+my @sql_query = ();
+my @count_query = ();
+
+for ( qw(agentnum state) ) {
+ $search_hash{$_} = $cgi->param($_) if $cgi->param($_);
+}
+$search_hash{'country'} = 'US';
+$search_hash{'classnum'} = [ $cgi->param('classnum') ];
+$search_hash{report_option} = $cgi->param('part5_report_option')
+ if $cgi->param('part5_report_option');
+$search_hash{'active'} = [ $opt{date}, $opt{date} ];
+
+my $sql_query = FS::cust_pkg->search( { %search_hash,
+ 'fcc_line' => 1,
+ 'select_zip5' => 1,
+ }
+ );
+my $count_query = delete($sql_query->{'count_query'});
+$count_query =~ s/COUNT\(\*\)/count(DISTINCT substr(zip,1,5))/;
+$count_query =~ s/ORDER BY [.\w]+//;
+
+</%init>
--- /dev/null
+<& /search/elements/search.html,
+ 'html_init' => '<H2>Part VI</H2>',
+ 'html_foot' => $html_foot,
+ 'name' => 'regions',
+ 'query' => [ @sql_query ],
+ 'count_query' => $count_query,
+ 'order_by' => 'ORDER BY censustract',
+ 'avoid_quote' => 1,
+ 'no_csv_header' => 1,
+ 'nohtmlheader' => 1,
+ 'header' => \@header,
+ 'xml_elements' => [
+ 'county_fips',
+ 'census_tract',
+ 'upload_rate_code',
+ 'download_rate_code',
+ 'technology_code',
+ 'technology_code_other',
+ 'value',
+ 'percentage',
+ ],
+ 'fields' => \@fields,
+ 'links' => \@links,
+ 'url' => $opt{url} || '',
+ 'xml_row_element' => 'Datarow',
+ 'really_disable_download' => 1,
+ 'filename' => $opt{filename},
+&>
+<%init>
+
+my $curuser = $FS::CurrentUser::CurrentUser;
+
+die "access denied"
+ unless $curuser->access_right('List packages');
+
+my %opt = @_;
+
+my %state_hash = ();
+my %state_pkgcount = ();
+
+my @header = ();
+my @fields = ();
+my @links = ();
+my $num = ($cgi->param('offset') =~ /^(\d+)$/) ? $1 : 0;
+unless ( $cgi->param('_type') eq 'xml' ) {
+ push @header, '#';
+ push @fields, sub { ++$num };
+ push @links, '';
+}
+push @header,
+ 'County code',
+ 'Census tract code',
+ 'Upload rate',
+ 'Download rate',
+ 'Technology code',
+ 'Technology code other',
+ 'Quantity',
+ 'Percentage residential',
+;
+push @fields,
+ sub { my $row = shift;
+ my $state = substr($row->censustract, 0, 2);
+ $state_hash{$state}++;
+ substr($row->censustract, 2, 3) || 'None';
+ },
+ sub { my $row = shift;
+ substr($row->censustract, 5) || 'None';
+ },
+ 'upload',
+ 'download',
+ 'technology_code',
+ sub { $cgi->param('_type') eq 'xml' ? '0' : '' },#broken
+ sub { my $row = shift;
+ my $state = substr($row->censustract, 0, 2);
+ $state_pkgcount{$state} += $row->quantity;
+ $row->quantity;
+ },
+ sub { my $row = shift; sprintf "%.3f", $row->residential },
+;
+
+my %search_hash = ();
+my @sql_query = ();
+
+for ( qw(agentnum state) ) {
+ $search_hash{$_} = $cgi->param($_) if $cgi->param($_);
+}
+
+$search_hash{'active'} = [ $opt{date}, $opt{date} ];
+$search_hash{'country'} = 'US';
+$search_hash{'classnum'} = [ $cgi->param('classnum') ]
+ if grep { $_ eq 'classnum' } $cgi->param;
+
+my @column_option = grep { /^\d+$/ } $cgi->param('part1_column_option')
+ if $cgi->param('part1_column_option');
+
+my @row_option = grep { /^\d+$/ } $cgi->param('part1_row_option')
+ if $cgi->param('part1_row_option');
+
+my @technology_option = &FS::Report::FCC_477::parse_technology_option($cgi);
+
+my $rowcount = 1;
+foreach my $row ( @row_option ) {
+ my $columncount = 2;
+ foreach my $column ( @column_option ) {
+ my $tech_code = 0;
+ foreach my $technology ( @technology_option ) {
+ $tech_code++;
+ next unless $technology;
+ my @report_option = ();
+ push @report_option, $row if $row;
+ push @report_option, $column if $column;
+ push @report_option, $technology;
+ my $report_option = join(',', @report_option) if @report_option;
+
+ my $sql_query = FS::cust_pkg->search(
+ { %search_hash,
+ ($report_option ? ( 'report_option' => $report_option ) : () ),
+ }
+ );
+ my $extracolumns = "$rowcount AS upload, $columncount AS download, $tech_code as technology_code";
+ my $percent = "CASE WHEN count(*) > 0 THEN 100-100*cast(count(cust_main.company) as numeric)/cast(count(*) as numeric) ELSE cast(0 as numeric) END AS residential";
+ $sql_query->{select} = "count(*) AS quantity, $extracolumns, cust_location.censustract, $percent";
+ $sql_query->{order_by} = " GROUP BY cust_location.censustract ";
+ push @sql_query, $sql_query;
+ }
+ $columncount++;
+ }
+ $rowcount++;
+}
+
+my $count_query = 'SELECT count(*) FROM ( ('.
+ join( ') UNION ALL (',
+ map { my $addl_from = $_->{addl_from};
+ my $extra_sql = $_->{extra_sql};
+ my $order_by = $_->{order_by};
+ "SELECT cust_location.censustract from cust_pkg $addl_from
+ $extra_sql $order_by";
+ }
+ @sql_query
+ ). ') ) AS foo';
+
+
+my $link = 'cust_pkg.cgi?'. join(';',
+ map { my $key = $_;
+ my @values = ref($search_hash{$_})
+ ? @{ $search_hash{$_} }
+ : $search_hash{$_};
+ map { "$key=$_" } @values;
+ }
+ keys %search_hash
+ ). ';';
+
+my $link_suffix = sub { my $row = shift;
+ my $result = 'censustract='. $row->censustract. ';';
+ my @ro = grep $_,
+ @row_option[$row->upload - 1],
+ @column_option[$row->download - 2],
+ @technology_option[$row->technology_code - 1],
+ ;
+ $result .= 'report_option='. join(',',@ro) if @ro;
+ $result;
+ };
+
+for (1..8) { push @links, [ $link, $link_suffix ]; }
+
+
+my $html_foot = sub {
+ if (scalar(keys %state_hash) > 1) {
+
+ my $roa_r = join(',', grep $_, @row_option);
+ $roa_r = ";report_option_any_r=$roa_r" if $roa_r;
+
+ my $roa_c = join(',', grep $_, @column_option);
+ $roa_c = ";report_option_any_c=$roa_c" if $roa_c;
+
+ my $roa_t = join(',', grep $_, @technology_option);
+ $roa_t = ";report_option_any_t=$roa_t" if $roa_t;
+
+ '<BR><B>WARNING: multiple states found</B><BR>'.
+ '<TABLE BORDER=0>'. #nicer formatting someday
+ join('', map { '<TR>'.
+ '<TD>'.
+ ( &FS::Report::FCC_477::statenum2state($_) || 'None' ).
+ '</TD>'.
+ '<TD>'.
+ qq(<A HREF="${link}censustract2=$_$roa_r$roa_c$roa_t">).
+ $state_pkgcount{$_}.
+ ' packages</A> in '.
+ $state_hash{$_}. ' census tracts'.
+ '</TD>'.
+ '</TR>';
+ }
+ keys %state_hash
+ ).
+ '</TABLE>';
+ } else {
+ '';
+ }
+};
+
+</%init>
--- /dev/null
+<% include('/elements/header.html', 'FCC Form 477 Report' ) %>
+
+<FORM ACTION="477.html" METHOD="GET">
+<INPUT TYPE="hidden" NAME="magic" VALUE="active">
+
+ <TABLE BGCOLOR="#cccccc" CELLSPACING=0>
+
+ <TR>
+ <TH CLASS="background" COLSPAN=2 ALIGN="left">
+ <FONT SIZE="+1">Search options</FONT>
+ </TH>
+ </TR>
+
+ <% include( '/elements/tr-select-agent.html',
+ 'curr_value' => scalar( $cgi->param('agentnum') ),
+ 'disable_empty' => 0,
+ )
+ %>
+
+% # not tr-select-state, we only want to choose from among those that
+% # have customers
+ <& /elements/tr-select-table.html,
+ 'label' => 'State',
+ 'field' => 'state',
+ 'table' => 'cust_location',
+ 'name_col' => 'state',
+ 'value_col' => 'state',
+ 'disable_empty' => 1,
+ 'records' => \@states,
+ &>
+
+ <& /elements/tr-input-date-field.html, {
+ 'label' => 'As of date',
+ 'name' => 'date',
+ 'value' => '',
+ 'format' => '%m/%d/%Y'
+ } &>
+
+ <% include( '/elements/tr-select-pkg_class.html',
+ 'multiple' => 1,
+ 'empty_label' => '(empty class)',
+ )
+ %>
+
+ <SCRIPT type="text/javascript">
+ function partchange(what) {
+ var id = 'part' + what.value;
+ var element = document.getElementById(id);
+ if (what.checked) {
+ element.style.display = '';
+ } else {
+ element.style.display = 'none';
+ }
+ }
+ function toggleV() {
+ document.getElementById('enableV').disabled =
+ ! (document.getElementById('enableIIA').checked ||
+ document.getElementById('enableIIB').checked);
+ }
+ function toggleVI() {
+ document.getElementById('enableVI').disabled =
+ ! document.getElementById('enableIA').checked;
+ }
+ </SCRIPT>
+
+ <% include( '/elements/tr-checkbox.html',
+ 'label' => 'Enable part IA?',
+ 'field' => 'part',
+ 'id' => 'enableIA',
+ 'value' => 'IA',
+ 'onchange' => 'partchange(this); toggleVI();',
+ )
+ %>
+
+ <TR id='partIA' style="display:none"><TD>Part IA</TD><TD><TABLE>
+ <TR><TD>Download speeds</TD><TD>
+ <TABLE>
+% my $i = 0;
+% foreach my $speed ( @FS::Report::FCC_477::download ) {
+ <TR>
+ <TH><% $speed %></TH>
+ <TD>
+ <% include( '/elements/select-table.html',
+ 'table' => 'part_pkg_report_option',
+ 'name_col' => 'name',
+ 'hashref' => { 'disabled' => '' },
+ 'element_name' => 'part1_column_option',
+ 'disable_empty' => 1,
+ 'curr_value' =>
+ FS::Report::FCC_477::restore_fcc477map("part1_column_option_$i"),
+ )
+ %>
+ </TD>
+ </TR>
+% $i++
+% }
+ </TABLE></TD>
+ <TD>Upload speeds</TD><TD>
+ <TABLE>
+% $i = 0;
+% foreach my $speed ( @FS::Report::FCC_477::upload ) {
+ <TR>
+ <TH><% $speed %></TH>
+ <TD>
+ <% include( '/elements/select-table.html',
+ 'table' => 'part_pkg_report_option',
+ 'name_col' => 'name',
+ 'hashref' => { 'disabled' => '' },
+ 'element_name' => 'part1_row_option',
+ 'disable_empty' => 1,
+ 'curr_value' =>
+ FS::Report::FCC_477::restore_fcc477map("part1_row_option_$i"),
+ )
+ %>
+ </TD>
+ </TR>
+% $i++
+% }
+ </TABLE></TD></TR>
+ <TR><TD>Technologies</TD><TD>
+ <TABLE>
+% $i = 0;
+% foreach my $tech ( @FS::Report::FCC_477::technology ) {
+ <TR>
+ <TH><% $tech %></TH>
+ <TD>
+ <% include( '/elements/select-table.html',
+ 'table' => 'part_pkg_report_option',
+ 'name_col' => 'name',
+ 'hashref' => { 'disabled' => '' },
+ 'element_name' => "part1_technology_option_$i",
+ 'empty_label' => '(omit)',
+ 'curr_value' =>
+ FS::Report::FCC_477::restore_fcc477map("part1_technology_option_$i"),
+ )
+ %>
+ </TD>
+ </TR>
+% $i++
+% }
+ </TABLE></TD></TR>
+ </TABLE></TD></TR>
+
+ <% include( '/elements/tr-checkbox.html',
+ 'label' => 'Enable part IIA?',
+ 'field' => 'part',
+ 'id' => 'enableIIA',
+ 'value' => 'IIA',
+ 'onchange' => 'partchange(this); toggleV();',
+ )
+ %>
+
+ <TR id='partIIA' style="display:none"><TD>Part IIA</TD><TD><TABLE>
+% $i = 0;
+% foreach my $option ( @FS::Report::FCC_477::part2aoption ) {
+ <TR>
+ <TH><% $option %></TH>
+ <TD>
+ <% include( '/elements/select-table.html',
+ 'table' => 'part_pkg_report_option',
+ 'name_col' => 'name',
+ 'hashref' => { 'disabled' => '' },
+ 'element_name' => 'part2a_row_option',
+ 'curr_value' =>
+ FS::Report::FCC_477::restore_fcc477map("part2a_row_option_$i"),
+ )
+ %>
+ </TD>
+ </TR>
+% $i++
+% }
+ </TABLE></TD></TR>
+
+ <% include( '/elements/tr-checkbox.html',
+ 'label' => 'Enable part IIB?',
+ 'field' => 'part',
+ 'id' => 'enableIIB',
+ 'value' => 'IIB',
+ 'onchange' => 'partchange(this); toggleV();',
+ )
+ %>
+
+ <TR id='partIIB' style="display:none"><TD>Part IIB</TD><TD><TABLE>
+% $i = 0;
+% foreach my $option ( @FS::Report::FCC_477::part2boption ) {
+ <TR>
+ <TH><% $option %></TH>
+ <TD>
+ <% include( '/elements/select-table.html',
+ 'table' => 'part_pkg_report_option',
+ 'name_col' => 'name',
+ 'hashref' => { 'disabled' => '' },
+ 'element_name' => 'part2b_row_option',
+ 'curr_value' =>
+ FS::Report::FCC_477::restore_fcc477map("part2b_row_option_$i"),
+ )
+ %>
+ </TD>
+ </TR>
+% $i++
+% }
+ </TABLE></TD></TR>
+
+ <% include( '/elements/tr-checkbox.html',
+ 'label' => 'Enable part IV?',
+ 'field' => 'part',
+ 'id' => 'enableIV', #unused
+ 'value' => 'IV',
+ 'onchange' => 'partchange(this)',
+ )
+ %>
+
+ <TR id='partIV' style="display:none"><TD>Part IV</TD><TD><TABLE>
+ <% include( '/elements/tr-textarea.html',
+ 'label' => 'Explanatory notes',
+ 'id' => 'partIV',
+ 'field' => 'notes',
+ 'rows' => 15,
+ 'cols' => 80,
+ )
+ %>
+ </TABLE></TD></TR>
+
+ <% include( '/elements/tr-checkbox.html',
+ 'label' => 'Enable part V?',
+ 'field' => 'part',
+ 'value' => 'V',
+ 'id' => 'enableV',
+ 'onchange' => 'partchange(this)',
+ 'postfix' =>
+ ' <FONT SIZE="-1">(requires Part IIA or IIB)</FONT>',
+ )
+ %>
+ <TR id='partV' style="display:none">
+ <TD>Part V</TD>
+ <TD>
+ <% include( '/elements/select-table.html',
+ 'table' => 'part_pkg_report_option',
+ 'name_col' => 'name',
+ 'hashref' => { 'disabled' => '' },
+ 'element_name' => 'part5_report_option',
+ 'curr_value' =>
+ FS::Report::FCC_477::restore_fcc477map("part5_report_option"),
+ )
+ %>
+ </TD>
+ </TR>
+
+
+ <% include( '/elements/tr-checkbox.html',
+ 'label' => 'Enable part VI?',
+ 'field' => 'part',
+ 'id' => 'enableVI',
+ 'value' => 'VI_census',
+ 'postfix' =>
+ ' <FONT SIZE="-1">(requires part IA)</FONT>',
+ )
+ %>
+ <SCRIPT TYPE="text/javascript">
+ toggleV();
+ toggleVI();
+ </SCRIPT>
+ </TABLE>
+
+<BR>
+<INPUT TYPE="submit" VALUE="Get Report">
+
+</FORM>
+
+<% include('/elements/footer.html') %>
+<%init>
+
+die "access denied"
+ unless $FS::CurrentUser::CurrentUser->access_right('List packages');
+
+my @states = qsearch({
+ 'table' => 'cust_location',
+ 'select' => 'DISTINCT(state)',
+ 'hashref' => { 'country' => 'US' }, # 477 report isn't relevant elsewhere
+});
+
+</%init>
-<% include('/elements/header.html', 'FCC Form 477 Report' ) %>
-
+% if ( $conf->exists('old_fcc_report') ) {
+% $m->clear_buffer;
+% $m->print($cgi->redirect($fsurl . 'search/old477/report_477.html'));
+% $m->abort;
+% }
+<& /elements/header.html, 'FCC Form 477 Report' &>
+% if ( $curuser->access_right('Edit FCC report configuration') ) {
+<FONT SIZE="+1"><STRONG>Preparation</STRONG></FONT>
+<UL>
+ <LI> <A HREF="<% $p %>browse/part_pkg-fcc.html">Configure packages</A> for FCC reporting categories.</LI>
+ <LI> <A HREF="<% $p %>browse/deploy_zone.html">Enter deployment zones</A> for broadband Internet or mobile phone.</LI>
+</UL>
+% }
+
<FORM ACTION="477.html" METHOD="GET">
-<INPUT TYPE="hidden" NAME="magic" VALUE="active">
<TABLE BGCOLOR="#cccccc" CELLSPACING=0>
<TR>
<TH CLASS="background" COLSPAN=2 ALIGN="left">
- <FONT SIZE="+1">Search options</FONT>
+ <FONT SIZE="+1">Report options</FONT>
</TH>
</TR>
- <% include( '/elements/tr-select-agent.html',
- 'curr_value' => scalar( $cgi->param('agentnum') ),
- 'disable_empty' => 0,
- )
- %>
-
-% # not tr-select-state, we only want to choose from among those that
-% # have customers
- <& /elements/tr-select-table.html,
- 'label' => 'State',
- 'field' => 'state',
- 'table' => 'cust_location',
- 'name_col' => 'state',
- 'value_col' => 'state',
- 'disable_empty' => 1,
- 'records' => \@states,
+ <& /elements/tr-select-agent.html,
+ 'curr_value' => scalar( $cgi->param('agentnum') ),
+ 'disable_empty' => 0,
&>
<& /elements/tr-input-date-field.html, {
'format' => '%m/%d/%Y'
} &>
- <% include( '/elements/tr-select-pkg_class.html',
- 'multiple' => 1,
- 'empty_label' => '(empty class)',
- )
- %>
-
- <SCRIPT type="text/javascript">
- function partchange(what) {
- var id = 'part' + what.value;
- var element = document.getElementById(id);
- if (what.checked) {
- element.style.display = '';
- } else {
- element.style.display = 'none';
- }
- }
- function toggleV() {
- document.getElementById('enableV').disabled =
- ! (document.getElementById('enableIIA').checked ||
- document.getElementById('enableIIB').checked);
- }
- function toggleVI() {
- document.getElementById('enableVI').disabled =
- ! document.getElementById('enableIA').checked;
- }
- </SCRIPT>
-
- <% include( '/elements/tr-checkbox.html',
- 'label' => 'Enable part IA?',
- 'field' => 'part',
- 'id' => 'enableIA',
- 'value' => 'IA',
- 'onchange' => 'partchange(this); toggleVI();',
- )
- %>
-
- <TR id='partIA' style="display:none"><TD>Part IA</TD><TD><TABLE>
- <TR><TD>Download speeds</TD><TD>
- <TABLE>
-% my $i = 0;
-% foreach my $speed ( @FS::Report::FCC_477::download ) {
- <TR>
- <TH><% $speed %></TH>
- <TD>
- <% include( '/elements/select-table.html',
- 'table' => 'part_pkg_report_option',
- 'name_col' => 'name',
- 'hashref' => { 'disabled' => '' },
- 'element_name' => 'part1_column_option',
- 'disable_empty' => 1,
- 'curr_value' =>
- FS::Report::FCC_477::restore_fcc477map("part1_column_option_$i"),
- )
- %>
- </TD>
- </TR>
-% $i++
-% }
- </TABLE></TD>
- <TD>Upload speeds</TD><TD>
- <TABLE>
-% $i = 0;
-% foreach my $speed ( @FS::Report::FCC_477::upload ) {
- <TR>
- <TH><% $speed %></TH>
- <TD>
- <% include( '/elements/select-table.html',
- 'table' => 'part_pkg_report_option',
- 'name_col' => 'name',
- 'hashref' => { 'disabled' => '' },
- 'element_name' => 'part1_row_option',
- 'disable_empty' => 1,
- 'curr_value' =>
- FS::Report::FCC_477::restore_fcc477map("part1_row_option_$i"),
- )
- %>
- </TD>
- </TR>
-% $i++
-% }
- </TABLE></TD></TR>
- <TR><TD>Technologies</TD><TD>
- <TABLE>
-% $i = 0;
-% foreach my $tech ( @FS::Report::FCC_477::technology ) {
- <TR>
- <TH><% $tech %></TH>
- <TD>
- <% include( '/elements/select-table.html',
- 'table' => 'part_pkg_report_option',
- 'name_col' => 'name',
- 'hashref' => { 'disabled' => '' },
- 'element_name' => "part1_technology_option_$i",
- 'empty_label' => '(omit)',
- 'curr_value' =>
- FS::Report::FCC_477::restore_fcc477map("part1_technology_option_$i"),
- )
- %>
- </TD>
- </TR>
-% $i++
-% }
- </TABLE></TD></TR>
- </TABLE></TD></TR>
-
- <% include( '/elements/tr-checkbox.html',
- 'label' => 'Enable part IIA?',
- 'field' => 'part',
- 'id' => 'enableIIA',
- 'value' => 'IIA',
- 'onchange' => 'partchange(this); toggleV();',
- )
- %>
-
- <TR id='partIIA' style="display:none"><TD>Part IIA</TD><TD><TABLE>
-% $i = 0;
-% foreach my $option ( @FS::Report::FCC_477::part2aoption ) {
- <TR>
- <TH><% $option %></TH>
- <TD>
- <% include( '/elements/select-table.html',
- 'table' => 'part_pkg_report_option',
- 'name_col' => 'name',
- 'hashref' => { 'disabled' => '' },
- 'element_name' => 'part2a_row_option',
- 'curr_value' =>
- FS::Report::FCC_477::restore_fcc477map("part2a_row_option_$i"),
- )
- %>
- </TD>
- </TR>
-% $i++
-% }
- </TABLE></TD></TR>
-
- <% include( '/elements/tr-checkbox.html',
- 'label' => 'Enable part IIB?',
- 'field' => 'part',
- 'id' => 'enableIIB',
- 'value' => 'IIB',
- 'onchange' => 'partchange(this); toggleV();',
- )
- %>
-
- <TR id='partIIB' style="display:none"><TD>Part IIB</TD><TD><TABLE>
-% $i = 0;
-% foreach my $option ( @FS::Report::FCC_477::part2boption ) {
- <TR>
- <TH><% $option %></TH>
- <TD>
- <% include( '/elements/select-table.html',
- 'table' => 'part_pkg_report_option',
- 'name_col' => 'name',
- 'hashref' => { 'disabled' => '' },
- 'element_name' => 'part2b_row_option',
- 'curr_value' =>
- FS::Report::FCC_477::restore_fcc477map("part2b_row_option_$i"),
- )
- %>
- </TD>
- </TR>
-% $i++
-% }
- </TABLE></TD></TR>
-
- <% include( '/elements/tr-checkbox.html',
- 'label' => 'Enable part IV?',
- 'field' => 'part',
- 'id' => 'enableIV', #unused
- 'value' => 'IV',
- 'onchange' => 'partchange(this)',
- )
- %>
-
- <TR id='partIV' style="display:none"><TD>Part IV</TD><TD><TABLE>
- <% include( '/elements/tr-textarea.html',
- 'label' => 'Explanatory notes',
- 'id' => 'partIV',
- 'field' => 'notes',
- 'rows' => 15,
- 'cols' => 80,
- )
- %>
- </TABLE></TD></TR>
-
- <% include( '/elements/tr-checkbox.html',
- 'label' => 'Enable part V?',
- 'field' => 'part',
- 'value' => 'V',
- 'id' => 'enableV',
- 'onchange' => 'partchange(this)',
- 'postfix' =>
- ' <FONT SIZE="-1">(requires Part IIA or IIB)</FONT>',
- )
- %>
- <TR id='partV' style="display:none">
- <TD>Part V</TD>
- <TD>
- <% include( '/elements/select-table.html',
- 'table' => 'part_pkg_report_option',
- 'name_col' => 'name',
- 'hashref' => { 'disabled' => '' },
- 'element_name' => 'part5_report_option',
- 'curr_value' =>
- FS::Report::FCC_477::restore_fcc477map("part5_report_option"),
- )
- %>
- </TD>
- </TR>
-
-
- <% include( '/elements/tr-checkbox.html',
- 'label' => 'Enable part VI?',
- 'field' => 'part',
- 'id' => 'enableVI',
- 'value' => 'VI_census',
- 'postfix' =>
- ' <FONT SIZE="-1">(requires part IA)</FONT>',
- )
- %>
- <SCRIPT TYPE="text/javascript">
- toggleV();
- toggleVI();
- </SCRIPT>
+ <& /elements/tr-checkbox-multiple.html,
+ 'label' => 'Enable parts',
+ 'field' => 'parts',
+ 'labels' => $part_titles,
+ 'options' => [ keys %$part_titles ]
+ &>
</TABLE>
-<BR>
-<INPUT TYPE="submit" VALUE="Get Report">
+ <BR>
+ <INPUT TYPE="submit" VALUE="Get Report">
</FORM>
-<% include('/elements/footer.html') %>
+<& /elements/footer.html &>
<%init>
+my $curuser = $FS::CurrentUser::CurrentUser;
die "access denied"
- unless $FS::CurrentUser::CurrentUser->access_right('List packages');
+ unless $curuser->access_right('List packages');
+
+my $conf = FS::Conf->new;
-my @states = qsearch({
- 'table' => 'cust_location',
- 'select' => 'DISTINCT(state)',
- 'hashref' => { 'country' => 'US' }, # 477 report isn't relevant elsewhere
-});
+my $part_titles = FS::Report::FCC_477->parts;
</%init>
projects / freeside.git / commitdiff