From: Mark Wells Date: Tue, 1 Jul 2014 06:27:42 +0000 (-0700) Subject: start of FCC 477 report rewrite, #24047 and #28020 X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=commitdiff_plain;h=447f1a906b47868497485edc3ba190a808539956 start of FCC 477 report rewrite, #24047 and #28020 --- diff --git a/FS/FS/Mason.pm b/FS/FS/Mason.pm index ede7259fb..5476fd83e 100644 --- a/FS/FS/Mason.pm +++ b/FS/FS/Mason.pm @@ -381,6 +381,7 @@ if ( -e $addl_handler_use_file ) { use FS::sched_avail; use FS::export_batch; use FS::export_batch_item; + use FS::part_pkg_fcc_option; # Sammath Naur if ( $FS::Mason::addl_handler_use ) { diff --git a/FS/FS/Report/FCC_477.pm b/FS/FS/Report/FCC_477.pm index fd088148b..9c038420b 100644 --- a/FS/FS/Report/FCC_477.pm +++ b/FS/FS/Report/FCC_477.pm @@ -4,9 +4,12 @@ use base qw( FS::Report ); use strict; use vars qw( @upload @download @technology @part2aoption @part2boption %states + $DEBUG ); use FS::Record qw( dbh ); +$DEBUG = 1; + =head1 NAME FS::Report::FCC_477 - Routines for FCC Form 477 reports @@ -201,10 +204,142 @@ sub statenum2state { $states{$num}; } -#sub statenum2abbr { -# my $num = shift; -# $states{$num} =~ /\((\w\w)\)$/ or return ''; -# $1; -#} +sub join_optionnames { + join(' ', map { join_optionname($_) } @_); +} + +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 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 = '1' AND technology::integer IN(".join(',', + 10, 11, 12, 20, 30, 40, 41, 42, 50, 60, 70, 90, 0 + ).")"; +} + +=item part6 OPTIONS + +Returns Part 6 of the 2014 FCC 477 data, as an arrayref of arrayrefs. +OPTIONS may contain "date" => a timestamp to run the report as of that +date. + +=cut + +sub part6 { + my $class = shift; + my %opt = shift; + my $date = $opt{date} || time; + + my @select = ( + 'cust_location.censustract', + 'technology', + 'broadband_downstream', + 'broadband_upstream', + 'COUNT(*)', + 'COUNT(is_consumer)', + ); + my $from = + 'cust_pkg + JOIN cust_location USING (locationnum) + JOIN part_pkg USING (pkgpart) '. + join_optionnames(qw( + is_broadband technology + broadband_downstream broadband_upstream + is_consumer + )) + ; + my @where = ( + active_on($date), + is_fixed_broadband() + ); + my $group_by = 'cust_location.censustract, technology, '. + 'broadband_downstream, broadband_upstream '; + my $order_by = $group_by; + + my $statement = "SELECT ".join(', ', @select) . " + FROM $from + WHERE ".join(' AND ', @where)." + GROUP BY $group_by + ORDER BY $order_by + "; + + warn $statement if $DEBUG; + dbh->selectall_arrayref($statement); +} + +=item part9 OPTIONS + +Returns Part 9 of the 2014 FCC 477 data, as above. + +=cut + +sub part9 { + my $class = shift; + my %opt = shift; + my $date = $opt{date} || time; + + my @select = ( + "cust_location.state", + "SUM(COALESCE(phone_vges::int,0))", + "SUM(COALESCE(phone_circuits::int,0))", + "SUM(COALESCE(phone_lines::int,0))", + "SUM(CASE WHEN is_broadband = '1' THEN phone_lines::int ELSE 0 END)", + "SUM(CASE WHEN is_consumer = '1' AND is_longdistance IS NULL THEN phone_lines::int ELSE 0 END)", + "SUM(CASE WHEN is_consumer = '1' AND is_longdistance = '1' THEN phone_lines::int ELSE 0 END)", + "SUM(CASE WHEN is_consumer IS NULL AND is_longdistance IS NULL THEN phone_lines::int ELSE 0 END)", + "SUM(CASE WHEN is_consumer IS NULL AND is_longdistance = '1' THEN phone_lines::int ELSE 0 END)", + "SUM(CASE WHEN phone_localloop = 'owned' THEN phone_lines::int ELSE 0 END)", + "SUM(CASE WHEN phone_localloop = 'leased' THEN phone_lines::int ELSE 0 END)", + "SUM(CASE WHEN phone_localloop = 'resale' THEN phone_lines::int ELSE 0 END)", + "SUM(CASE WHEN media = 'Fiber' THEN phone_lines::int ELSE 0 END)", + "SUM(CASE WHEN media = 'Cable Modem' THEN phone_lines::int ELSE 0 END)", + "SUM(CASE WHEN media = 'Fixed Wireless' THEN phone_lines::int ELSE 0 END)", + ); + my $from = + 'cust_pkg + JOIN cust_location USING (locationnum) + JOIN part_pkg USING (pkgpart) '. + join_optionnames(qw( + is_phone is_broadband media + phone_vges phone_circuits phone_lines + is_consumer is_longdistance phone_localloop + )) + ; + my @where = ( + active_on($date), + "is_phone::int = 1", + ); + my $group_by = 'cust_location.state'; + my $order_by = $group_by; + + my $statement = "SELECT ".join(', ', @select) . " + FROM $from + WHERE ".join(' AND ', @where)." + GROUP BY $group_by + ORDER BY $order_by + "; + + warn $statement if $DEBUG; + dbh->selectall_arrayref($statement); +} + 1; diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm index b0980e8ef..1b76a23e9 100644 --- a/FS/FS/Schema.pm +++ b/FS/FS/Schema.pm @@ -4755,6 +4755,18 @@ sub tables_hashref { ], }, + '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', '', '', '', '', diff --git a/FS/FS/part_pkg.pm b/FS/FS/part_pkg.pm index ffbeba2d8..2f0646740 100644 --- a/FS/FS/part_pkg.pm +++ b/FS/FS/part_pkg.pm @@ -17,6 +17,9 @@ use FS::cust_pkg; use FS::agent_type; use FS::type_pkgs; use FS::part_pkg_option; +use FS::part_pkg_fcc_option; +use FS::pkg_class; +use FS::agent; use FS::part_pkg_msgcat; use FS::part_pkg_taxrate; use FS::part_pkg_taxoverride; @@ -331,6 +334,11 @@ sub insert { } } + if ( $options{fcc_options} ) { + warn " updating fcc options " if $DEBUG; + $self->process_fcc_options( $options{fcc_options} ); + } + warn " committing transaction" if $DEBUG and $oldAutoCommit; $dbh->commit or die $dbh->errstr if $oldAutoCommit; @@ -612,6 +620,11 @@ sub replace { } } + if ( $options->{fcc_options} ) { + warn " updating fcc options " if $DEBUG; + $new->process_fcc_options( $options->{fcc_options} ); + } + warn " committing transaction" if $DEBUG and $oldAutoCommit; $dbh->commit or die $dbh->errstr if $oldAutoCommit; ''; @@ -772,6 +785,43 @@ sub propagate { join("\n", @error); } +=item process_fcc_options HASHREF + +Sets the FCC options on this package definition to the values specified +in HASHREF. Names are as in L. + +=cut + +sub process_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 }); + + # 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 @@ -1301,6 +1351,35 @@ sub part_pkg_currency_option { $part_pkg_currency->optionvalue; } +=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). diff --git a/FS/FS/part_pkg_fcc_option.pm b/FS/FS/part_pkg_fcc_option.pm new file mode 100644 index 000000000..0a288def5 --- /dev/null +++ b/FS/FS/part_pkg_fcc_option.pm @@ -0,0 +1,159 @@ +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 method. + +=item pkgpart + +L 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 ], +); + +our %technology_labels = ( + 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' +); + +sub media_types { + Storable::dclone(\%media_types); +} + +sub technology_labels { + +{ %technology_labels }; +} + +=head1 BUGS + +=head1 SEE ALSO + +L, schema.html from the base documentation. + +=cut + +1; + diff --git a/FS/MANIFEST b/FS/MANIFEST index 2116442d2..ed8fd9b24 100644 --- a/FS/MANIFEST +++ b/FS/MANIFEST @@ -804,3 +804,5 @@ FS/export_batch.pm t/export_batch.t FS/export_batch_item.pm t/export_batch_item.t +FS/part_pkg_fcc_option.pm +t/part_pkg_fcc_option.t diff --git a/FS/t/part_pkg_fcc_option.t b/FS/t/part_pkg_fcc_option.t new file mode 100644 index 000000000..8f781c866 --- /dev/null +++ b/FS/t/part_pkg_fcc_option.t @@ -0,0 +1,5 @@ +BEGIN { $| = 1; print "1..1\n" } +END {print "not ok 1\n" unless $loaded;} +use FS::part_pkg_fcc_option; +$loaded=1; +print "ok 1\n"; diff --git a/bin/convert-477-options b/bin/convert-477-options new file mode 100755 index 000000000..a52c56cc1 --- /dev/null +++ b/bin/convert-477-options @@ -0,0 +1,125 @@ +#!/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; + +adminsuidsetup($user) or die "invalid user '$user'"; +$FS::UID::AutoCommit = 1; +$FS::Record::nowarn_classload = 1; + +print "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. + - The 'consumer grade' vs. 'business grade' classification + was introduced in 2014 and will not be set. + +Configuring packages... +"; + +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 = map { $_->formvalue => $_->formkey } + qsearch('fcc477map', {}); + +sub report_option_to_fcc_option { + my $report_option_num = shift; + my $formkey = $num_to_formkey{$report_option_num} + or return; + if ($formkey =~ /^part1_column_option_(\d+)/) { + #download speed + return (broadband_downstream => $min_download_speed[$1]); + } elsif ($formkey =~ /^part1_row_option_(\d+)/) { + #upload speed + return (broadband_upstream => $min_upload_speed[$1]); + } elsif ($formkey =~ /^part1_technology_option_(\d+)/) { + #broadband tech + return (is_broadband => 1, + media => $media_type[$1], + technology => $technology[$1]); + } elsif ($formkey =~ /^part2a_row_option_(\d+)/) { + #local phone options + return (split(':', $phone_option[$1])); + } elsif ($formkey =~ /^part2b_row_option_(\d+)/) { + #VoIP options (are all media types) + 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_lines' => $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"; + diff --git a/httemplate/edit/part_pkg.cgi b/httemplate/edit/part_pkg.cgi index 61994f99a..9e55d9f42 100755 --- a/httemplate/edit/part_pkg.cgi +++ b/httemplate/edit/part_pkg.cgi @@ -232,18 +232,23 @@ }, }, - ( $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 }, - ) - : () - ), + { 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 => 'tablebreak-tr-title', value => 'External Links', #better name? diff --git a/httemplate/edit/process/part_pkg.cgi b/httemplate/edit/process/part_pkg.cgi index 8ea39e1a2..d27ddb0b0 100755 --- a/httemplate/edit/process/part_pkg.cgi +++ b/httemplate/edit/process/part_pkg.cgi @@ -127,6 +127,14 @@ my $args_callback = sub { push @args, 'part_pkg_currency' => \%part_pkg_currency; ### + # 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 ### diff --git a/httemplate/elements/tr-input-fcc_options.html b/httemplate/elements/tr-input-fcc_options.html new file mode 100644 index 000000000..bd5083075 --- /dev/null +++ b/httemplate/elements/tr-input-fcc_options.html @@ -0,0 +1,25 @@ + + + <& hidden.html, 'id' => $id, @_ &> +%# <& input-text.html, 'id' => $id, @_ &> +%# XXX debugging + +% # show some kind of useful summary of the FCC options here + + + +<%init> +my %opt = @_; +my $id = $opt{id} || $opt{field}; + diff --git a/httemplate/misc/part_pkg_fcc_options.html b/httemplate/misc/part_pkg_fcc_options.html new file mode 100644 index 000000000..1f5d4a8bf --- /dev/null +++ b/httemplate/misc/part_pkg_fcc_options.html @@ -0,0 +1,200 @@ +<& /elements/header-popup.html &> + + + +<%def .checkbox> +% my $field = shift; +% my $extra = shift || ''; +> + + +
+% # 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. +

+ + +

+

+ <& .checkbox, 'is_consumer' &> + +

+

+ <& .checkbox, 'is_broadband' &> + +

+ + +
+ + +
+ + +
+

+

+ <& .checkbox, 'is_phone' &> + +

+ + +
+ + +
+ + <& .checkbox, 'phone_longdistance' &> +
+ + +
+
+ + +
+ + +
+
+

+

+ <& .checkbox, 'is_voip' &> + +

+ + <& .checkbox, 'voip_ott' &> +
+

+
+ +
+
+ + +<& /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'); + diff --git a/httemplate/search/477partIA.html b/httemplate/search/477partIA.html index 97f8ac0aa..55e901bb3 100755 --- a/httemplate/search/477partIA.html +++ b/httemplate/search/477partIA.html @@ -80,13 +80,17 @@ die "access denied" 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');