X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=FS%2FFS%2Fdeploy_zone.pm;h=306b4fb444ee04e4c022fbbc18296acbf61fa04e;hp=3caeda24b42d8cc643b06678811a2d1bbfbcc692;hb=ffa18709ee8a4d05e18d2d406cf73afe79e52524;hpb=0f359d5480aa1621d73ee802f420e8951abc620d diff --git a/FS/FS/deploy_zone.pm b/FS/FS/deploy_zone.pm index 3caeda24b..306b4fb44 100644 --- a/FS/FS/deploy_zone.pm +++ b/FS/FS/deploy_zone.pm @@ -3,6 +3,15 @@ package FS::deploy_zone; use strict; use base qw( FS::o2m_Common FS::Record ); use FS::Record qw( qsearch qsearchs dbh ); +use Storable qw(thaw); +use MIME::Base64; + +use Cpanel::JSON::XS; +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 @@ -46,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 @@ -56,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 @@ -65,10 +82,6 @@ The FCC technology code for the type of service available. For mobile service zones, the FCC code for the RF band. -=item servicetype - -"broadband" or "voice" - =item adv_speed_up For broadband, the advertised upstream bandwidth in the zone. If multiple @@ -97,6 +110,22 @@ type of service is sold. '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 @@ -133,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; @@ -169,45 +202,251 @@ sub check { my $error = $self->ut_numbern('zonenum') - || $self->ut_textn('description') + || $self->ut_text('description') || $self->ut_number('agentnum') + || $self->ut_numbern('censusyear') || $self->ut_foreign_key('agentnum', 'agent', 'agentnum') - || $self->ut_alphan('dbaname') + || $self->ut_textn('dbaname') || $self->ut_enum('zonetype', [ 'B', 'P' ]) || $self->ut_number('technology') || $self->ut_numbern('spectrum') - || $self->ut_enum('servicetype', [ 'broadband', 'voice' ]) || $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 +=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 name of the table that contains the zone's elements (blocks or -vertices). +Returns the vertex records for this zone, in order by sequence number. =cut -sub element_table { +sub deploy_zone_block { my $self = shift; - if ($self->zonetype eq 'B') { - return 'deploy_zone_block'; - } elsif ( $self->zonetype eq 'P') { - return 'deploy_zone_vertex'; + 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', + }); +} + +=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 + +=over 4 + +=item process_batch_import JOB, PARAMS + +=cut + +sub process_batch_import { + eval { + use FS::deploy_zone_block; + use FS::deploy_zone_vertex; + }; + my $job = shift; + my $param = shift; + if (!ref($param)) { + $param = thaw(decode_base64($param)); + } + + # even if creating a new zone, the deploy_zone object should already + # be inserted by this point + my $zonenum = $param->{zonenum} + or die "zonenum required"; + my $zone = FS::deploy_zone->by_key($zonenum) + or die "deploy_zone #$zonenum not found"; + my $opt; + if ( $zone->zonetype eq 'B' ) { + $opt = { 'table' => 'deploy_zone_block', + 'params' => [ 'zonenum', 'censusyear' ], + 'formats' => { 'plain' => [ 'censusblock' ] }, + 'default_csv' => 1, + }; + $job->update_statustext('1,Inserting census blocks'); + } elsif ( $zone->zonetype eq 'P' ) { + $opt = { 'table' => 'deploy_zone_vertex', + 'params' => [ 'zonenum' ], + 'formats' => { 'plain' => [ 'latitude', 'longitude' ] }, + 'default_csv' => 1, + }; } else { - die 'unknown zonetype'; + die "don't know how to import to zonetype ".$zone->zonetype; } + + FS::Record::process_batch_import( $job, $opt, $param ); + } -=back +=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 = 'https://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}; + last unless scalar @{$data->{features}}; #Nothing to insert + + 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