From d07c72046444319e0811c6a00b504885da091992 Mon Sep 17 00:00:00 2001 From: Mark Wells Date: Wed, 30 Sep 2015 22:49:38 -0700 Subject: [PATCH] graphical selection of deployment zones and automatic block lookup, #30260 --- FS/FS/Schema.pm | 3 +- FS/FS/deploy_zone.pm | 187 ++++++++++++++++++--- FS/FS/deploy_zone_block.pm | 5 - FS/FS/o2m_Common.pm | 18 +- FS/FS/part_pkg_fcc_option.pm | 2 +- httemplate/browse/deploy_zone.html | 6 +- httemplate/edit/deploy_zone-fixed.html | 85 +++++----- httemplate/edit/deploy_zone-mobile.html | 21 ++- httemplate/edit/process/deploy_zone-fixed.html | 35 +++- httemplate/edit/process/deploy_zone-mobile.html | 19 ++- httemplate/edit/process/elements/process.html | 21 ++- httemplate/elements/polygon.html | 127 ++++++++++++++ httemplate/elements/tr-polygon.html | 5 + .../misc/process/deploy_zone-block_lookup.cgi | 13 ++ 14 files changed, 450 insertions(+), 97 deletions(-) create mode 100644 httemplate/elements/polygon.html create mode 100644 httemplate/elements/tr-polygon.html create mode 100644 httemplate/misc/process/deploy_zone-block_lookup.cgi diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm index 85fbbeb8a..486860ff6 100644 --- a/FS/FS/Schema.pm +++ b/FS/FS/Schema.pm @@ -7038,6 +7038,7 @@ sub tables_hashref { 'zonenum', 'serial', '', '', '', '', 'description', 'char', 'NULL', $char_d, '', '', 'agentnum', 'int', '', '', '', '', + 'censusyear', 'char', 'NULL', 4, '', '', 'dbaname', 'char', 'NULL', $char_d, '', '', 'zonetype', 'char', '', 1, '', '', 'technology', 'int', '', '', '', '', @@ -7069,7 +7070,7 @@ sub tables_hashref { 'blocknum', 'serial', '', '', '', '', 'zonenum', 'int', '', '', '', '', 'censusblock', 'char', '', 15, '', '', - 'censusyear', 'char', '', 4, '', '', + 'censusyear', 'char','NULL', 4, '', '', ], 'primary_key' => 'blocknum', 'unique' => [], diff --git a/FS/FS/deploy_zone.pm b/FS/FS/deploy_zone.pm index 38dd7dc2d..71129cf44 100644 --- a/FS/FS/deploy_zone.pm +++ b/FS/FS/deploy_zone.pm @@ -6,6 +6,13 @@ use FS::Record qw( qsearch qsearchs dbh ); use Storable qw(thaw); use MIME::Base64; +use JSON qw(encode_json decode_json) ; +use LWP::UserAgent; +use HTTP::Request::Common; + +# update this in 2020, along with the URL for the TIGERweb service +our $CENSUS_YEAR = 2010; + =head1 NAME FS::deploy_zone - Object methods for deploy_zone records @@ -48,6 +55,12 @@ Optional text describing the zone. The agent that serves this zone. +=item censusyear + +The census map year for which this zone was last updated. May be null for +zones that contain no census blocks (mobile zones, or fixed zones that haven't +had their block lists filled in yet). + =item dbaname The name under which service is marketed in this zone. If null, will @@ -58,6 +71,8 @@ default to the agent name. 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 and L. +Note that block-type zones are still allowed to have a vertex list, for +use by the map editor. =item technology @@ -147,12 +162,16 @@ sub delete { 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(@_); + my $error; + foreach (qw(deploy_zone_block deploy_zone_vertex)) { + $error ||= $self->process_o2m( + 'table' => $_, + 'num_col' => 'zonenum', + 'fields' => 'zonenum', + 'params' => {}, + ); + } + $error ||= $self->SUPER::delete(@_); if ($error) { dbh->rollback if $oldAutoCommit; @@ -185,6 +204,7 @@ sub check { $self->ut_numbern('zonenum') || $self->ut_text('description') || $self->ut_number('agentnum') + || $self->ut_numbern('censusyear') || $self->ut_foreign_key('agentnum', 'agent', 'agentnum') || $self->ut_textn('dbaname') || $self->ut_enum('zonetype', [ 'B', 'P' ]) @@ -219,24 +239,6 @@ sub check { $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 @@ -244,8 +246,7 @@ 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. +Returns the vertex records for this zone, in order by sequence number. =cut @@ -267,7 +268,19 @@ sub deploy_zone_vertex { }); } -=back +=item vertices_json + +Returns the vertex list for this zone, as a JSON string of + +[ [ latitude0, longitude0 ], [ latitude1, longitude1 ] ... ] + +=cut + +sub vertices_json { + my $self = shift; + my @vertices = map { [ $_->latitude, $_->longitude ] } $self->deploy_zone_vertex; + encode_json(\@vertices); +} =head2 SUBROUTINES @@ -315,7 +328,125 @@ sub process_batch_import { FS::Record::process_batch_import( $job, $opt, $param ); } - + +=item process_block_lookup JOB, ZONENUM + +Look up all the census blocks in the zone's footprint, and insert them. +This will replace any existing block list. + +=cut + +sub process_block_lookup { + my $job = shift; + my $param = shift; + if (!ref($param)) { + $param = thaw(decode_base64($param)); + } + my $zonenum = $param->{zonenum}; + my $zone = FS::deploy_zone->by_key($zonenum) + or die "zone $zonenum not found\n"; + + # wipe the existing list of blocks + my $error = $zone->process_o2m( + 'table' => 'deploy_zone_block', + 'num_col' => 'zonenum', + 'fields' => 'zonenum', + 'params' => {}, + ); + die $error if $error; + + $job->update_statustext('0,querying census database') if $job; + + # negotiate the rugged jungle trails of the ArcGIS REST protocol: + # 1. unlike most places, longitude first. + my @zone_vertices = map { [ $_->longitude, $_->latitude ] } + $zone->deploy_zone_vertex; + + return if scalar(@zone_vertices) < 3; # then don't bother + + # 2. package this as "rings", inside a JSON geometry object + # 3. announce loudly and frequently that we are using spatial reference + # 4326, "true GPS coordinates" + my $geometry = encode_json({ + 'rings' => [ \@zone_vertices ], + 'wkid' => 4326, + }); + + my %query = ( + f => 'json', # duh + geometry => $geometry, + geometryType => 'esriGeometryPolygon', # as opposed to a bounding box + inSR => 4326, + outSR => 4326, + spatialRel => 'esriSpatialRelIntersects', # the test to perform + outFields => 'OID,GEOID', + returnGeometry => 'false', + orderByFields => 'OID', + ); + my $url = 'http://tigerweb.geo.census.gov/arcgis/rest/services/TIGERweb/Tracts_Blocks/MapServer/12/query'; + my $ua = LWP::UserAgent->new; + + # first find out how many of these we're dealing with + my $response = $ua->request( + POST $url, Content => [ + %query, + returnCountOnly => 1, + ] + ); + die $response->status_line unless $response->is_success; + my $data = decode_json($response->content); + # their error messages are mostly useless, but don't just blindly continue + die $data->{error}{message} if $data->{error}; + + my $count = $data->{count}; + my $inserted = 0; + + #warn "Census block lookup: $count\n"; + + # we have to do our own pagination on this, because the census bureau + # doesn't support resultOffset (maybe they don't have ArcGIS 10.3 yet). + # that's why we're ordering by OID, it's globally unique + my $last_oid = 0; + my $done = 0; + while (!$done) { + $response = $ua->request( + POST $url, Content => [ + %query, + where => "OID>$last_oid", + ] + ); + die $response->status_line unless $response->is_success; + $data = decode_json($response->content); + die $data->{error}{message} if $data->{error}; + + foreach my $feature (@{ $data->{features} }) { + my $geoid = $feature->{attributes}{GEOID}; # the prize + my $block = FS::deploy_zone_block->new({ + zonenum => $zonenum, + censusblock => $geoid + }); + $error = $block->insert; + die "$error (inserting census block $geoid)" if $error; + + $inserted++; + if ($job and $inserted % 100 == 0) { + my $percent = sprintf('%.0f', $inserted / $count * 100); + $job->update_statustext("$percent,creating block records"); + } + } + + #warn "Inserted $inserted records\n"; + $last_oid = $data->{features}[-1]{attributes}{OID}; + $done = 1 unless $data->{exceededTransferLimit}; + } + + $zone->set('censusyear', $CENSUS_YEAR); + $error = $zone->replace; + warn "$error (updating zone census year)" if $error; # whatever, continue + + return; +} + =head1 BUGS =head1 SEE ALSO diff --git a/FS/FS/deploy_zone_block.pm b/FS/FS/deploy_zone_block.pm index 757af7e3d..2ac18e2fe 100644 --- a/FS/FS/deploy_zone_block.pm +++ b/FS/FS/deploy_zone_block.pm @@ -43,10 +43,6 @@ L foreign key for the zone. 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 @@ -107,7 +103,6 @@ sub check { $self->ut_numbern('blocknum') || $self->ut_number('zonenum') || $self->ut_number('censusblock') - || $self->ut_number('censusyear') ; return $error if $error; diff --git a/FS/FS/o2m_Common.pm b/FS/FS/o2m_Common.pm index 4f6d2e781..430f00bbb 100644 --- a/FS/FS/o2m_Common.pm +++ b/FS/FS/o2m_Common.pm @@ -35,11 +35,19 @@ Available options: table (required) - Table into which the records are inserted. -num_col (optional) - Column in table which links to the primary key of the base table. If not specified, it is assumed this has the same name. - -params (required) - Hashref of keys and values, often passed as CVars)> from a form. - -fields (required) - Arrayref of field names for each record in table. Pulled from params as "pkeyNN_field" where pkey is table's primary key and NN is the entry's numeric identifier. +fields (required) - Arrayref of the field names in the "many" table. + +params (required) - Hashref of keys and values, often passed as +CVars)> from a form. This will be scanned for keys of the form +"pkeyNN" (where pkey is the primary key column name, and NN is an integer). +Each of these designates one record in the "many" table. The contents of +that record will be taken from other parameters with the names +"pkeyNN_myfield" (where myfield is one of the fields in the 'fields' +array). + +num_col (optional) - Name of the foreign key column in the "many" table, which +links to the primary key of the base table. If not specified, it is assumed +this has the same name as in the base table. =cut diff --git a/FS/FS/part_pkg_fcc_option.pm b/FS/FS/part_pkg_fcc_option.pm index 5c78e5f9e..3d821f502 100644 --- a/FS/FS/part_pkg_fcc_option.pm +++ b/FS/FS/part_pkg_fcc_option.pm @@ -148,7 +148,7 @@ tie our %spectrum_labels, 'Tie::IxHash', ( 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', + 98 => 'Unlicensed (including broadcast television "white spaces") Bands', 99 => '600 MHz', 100 => 'H Block', 101 => 'Advanced Wireless Services (AWS) 3 Band', diff --git a/httemplate/browse/deploy_zone.html b/httemplate/browse/deploy_zone.html index 3bd9d07dd..02ebb8b8c 100644 --- a/httemplate/browse/deploy_zone.html +++ b/httemplate/browse/deploy_zone.html @@ -17,6 +17,7 @@ 'Market', 'Advertised Mbps', 'Contractual Mbps', + 'Vertices', 'Census blocks', ], fields => [ 'zonenum', @@ -42,6 +43,9 @@ ) }, sub { my $self = shift; + FS::deploy_zone_vertex->count('zonenum = '.$self->zonenum) + }, + sub { my $self = shift; FS::deploy_zone_block->count('zonenum = '.$self->zonenum) }, ], @@ -53,7 +57,7 @@ '(cir_speed_down, cir_speed_up)', ], links => [ $link_fixed, $link_fixed, ], - align => 'clllllr', + align => 'cllllrr', nohtmlheader => 1, disable_maxselect => 1, disable_total => 1, diff --git a/httemplate/edit/deploy_zone-fixed.html b/httemplate/edit/deploy_zone-fixed.html index 90d1b6667..b8d9f8bbc 100644 --- a/httemplate/edit/deploy_zone-fixed.html +++ b/httemplate/edit/deploy_zone-fixed.html @@ -54,29 +54,36 @@ value => 'Contractually guaranteed speed (Mbps)' }, 'cir_speed_down', 'cir_speed_up', - - { type => 'tablebreak-tr-title', value => 'Census blocks'}, - { field => 'file', - type => 'file-upload', - }, - { field => 'format', - type => 'hidden', - value => 'plain', - }, - { field => 'censusyear', - type => 'select', - options => [ '', qw( 2013 2012 2011 ) ], - }, - - { type => 'tablebreak-tr-title', value => '', }, - { field => 'blocknum', - type => 'deploy_zone_block', - o2m_table => 'deploy_zone_block', - m2_label => ' ', - m2_error_callback => $m2_error_callback, - }, + { type => 'tablebreak-tr-title', value => 'Footprint'}, + { field => 'vertices', + type => 'polygon', + curr_value_callback => sub { + my ($cgi, $object) = @_; + $cgi->param('vertices') || $object->vertices_json; + }, + } +# +# { type => 'tablebreak-tr-title', value => 'Census blocks'}, +# { field => 'file', +# type => 'file-upload', +# }, +# { field => 'format', +# type => 'hidden', +# value => 'plain', +# }, +# { field => 'censusyear', +# type => 'hidden', +# options => [ '', qw( 2013 2012 2011 ) ], +# }, +# +# { type => 'tablebreak-tr-title', value => '', }, +# { 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; @@ -90,22 +97,22 @@ 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; -}; +#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; +#}; diff --git a/httemplate/edit/deploy_zone-mobile.html b/httemplate/edit/deploy_zone-mobile.html index d049cb018..8cec298bf 100644 --- a/httemplate/edit/deploy_zone-mobile.html +++ b/httemplate/edit/deploy_zone-mobile.html @@ -49,14 +49,21 @@ '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, - }, - ], + { field => 'vertices', + type => 'polygon', + curr_value_callback => sub { + my ($cgi, $object) = @_; + $cgi->param('vertices') || $object->vertices_json; + }, + } +# { 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; diff --git a/httemplate/edit/process/deploy_zone-fixed.html b/httemplate/edit/process/deploy_zone-fixed.html index eae3a746d..0033bbe52 100644 --- a/httemplate/edit/process/deploy_zone-fixed.html +++ b/httemplate/edit/process/deploy_zone-fixed.html @@ -3,12 +3,31 @@ error_redirect => popurl(2).'deploy_zone-fixed.html', table => 'deploy_zone', viewall_dir => 'browse', - process_o2m => { - 'table' => 'deploy_zone_block', - 'fields' => [qw( censusblock censusyear )] - }, - process_upload => { - 'process' => 'misc/process/deploy_zone-import.html', - 'fields' => [qw( censusyear format )], - }, + precheck_callback => $precheck_callback, + process_o2m => + { 'table' => 'deploy_zone_vertex', + 'fields' => [qw( latitude longitude )] + }, + progress_init => [ + 'PostForm', + [ 'zonenum' ], + $fsurl.'misc/process/deploy_zone-block_lookup.cgi', + $fsurl.'browse/deploy_zone.html', + ], &> +<%init> +my $precheck_callback = sub { + # convert the vertex list into a process_o2m-style parameter list + if ( $cgi->param('vertices') ) { + my $vertices = decode_json($cgi->param('vertices')); + my $i = 0; + foreach (@$vertices) { + $cgi->param("vertexnum${i}", ''); + $cgi->param("vertexnum${i}_latitude", $_->[0]); + $cgi->param("vertexnum${i}_longitude", $_->[1]); + $i++; + } + } + ''; +}; + diff --git a/httemplate/edit/process/deploy_zone-mobile.html b/httemplate/edit/process/deploy_zone-mobile.html index 7b8f911ec..d36d5d448 100644 --- a/httemplate/edit/process/deploy_zone-mobile.html +++ b/httemplate/edit/process/deploy_zone-mobile.html @@ -2,8 +2,25 @@ error_redirect => popurl(2).'deploy_zone-mobile.html', table => 'deploy_zone', viewall_dir => 'browse', - process_o2m => + precheck_callback => $precheck_callback, + process_o2m => { 'table' => 'deploy_zone_vertex', 'fields' => [qw( latitude longitude )] }, &> +<%init> +my $precheck_callback = sub { + # convert the vertex list into a process_o2m-style parameter list + if ( $cgi->param('vertices') ) { + my $vertices = decode_json($cgi->param('vertices')); + my $i = 0; + foreach (@$vertices) { + $cgi->param("vertexnum${i}", ''); + $cgi->param("vertexnum${i}_latitude", $_->[0]); + $cgi->param("vertexnum${i}_longitude", $_->[1]); + $i++; + } + } + ''; +}; + diff --git a/httemplate/edit/process/elements/process.html b/httemplate/edit/process/elements/process.html index 69bd605f6..a76f4befb 100644 --- a/httemplate/edit/process/elements/process.html +++ b/httemplate/edit/process/elements/process.html @@ -160,7 +160,26 @@ process(); <& /elements/footer.html &> -%} elsif ( $opt{'popup_reload'} ) { +% } elsif ( $opt{'progress_init'} ) { +% # some false laziness with the above +% my ($form_name, $job_fields) = @{ $opt{'progress_init'} }; +
+% foreach my $field (@$job_fields) { + +% } +<& /elements/progress-init.html, + @{ $opt{'progress_init'} } +&> + +
+ +<& /elements/footer.html &> + +% } elsif ( $opt{'popup_reload'} ) { <% include('/elements/header-popup.html', $opt{'popup_reload'} ) %> diff --git a/httemplate/elements/polygon.html b/httemplate/elements/polygon.html new file mode 100644 index 000000000..c26e98546 --- /dev/null +++ b/httemplate/elements/polygon.html @@ -0,0 +1,127 @@ +<%init> +my %opt = @_; +my $field = $opt{'field'}; +my $id = $opt{'id'} || $opt{'field'}; +my $div_id = "div_$id"; + +my $vertices_json = $opt{'curr_value'} || '[]'; + +<& hidden.html, %opt &> +
+ + + + + diff --git a/httemplate/elements/tr-polygon.html b/httemplate/elements/tr-polygon.html new file mode 100644 index 000000000..6990d3da6 --- /dev/null +++ b/httemplate/elements/tr-polygon.html @@ -0,0 +1,5 @@ + + +<& polygon.html, @_ &> + + diff --git a/httemplate/misc/process/deploy_zone-block_lookup.cgi b/httemplate/misc/process/deploy_zone-block_lookup.cgi new file mode 100644 index 000000000..8f4eac7e9 --- /dev/null +++ b/httemplate/misc/process/deploy_zone-block_lookup.cgi @@ -0,0 +1,13 @@ +<% $server->process %> +<%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 $server = FS::UI::Web::JSRPC->new( + 'FS::deploy_zone::process_block_lookup', $cgi +); + -- 2.11.0