From: Mark Wells Date: Wed, 6 Aug 2014 21:11:01 +0000 (-0700) Subject: 477 report: mobile deployment info X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=commitdiff_plain;h=1ebcca94aba75c5901c6eefaf373f39e94b03cf0 477 report: mobile deployment info --- diff --git a/FS/FS/Report/FCC_477.pm b/FS/FS/Report/FCC_477.pm index 86fa0a616..0f3dfb143 100644 --- a/FS/FS/Report/FCC_477.pm +++ b/FS/FS/Report/FCC_477.pm @@ -11,7 +11,7 @@ use FS::Record qw( dbh ); use Tie::IxHash; use Storable; -$DEBUG = 1; +$DEBUG = 0; =head1 NAME @@ -259,9 +259,11 @@ sub active_on { } sub is_fixed_broadband { - "is_broadband::int = 1 AND technology::int IN(".join(',', - 10, 11, 12, 20, 30, 40, 41, 42, 50, 60, 70, 90, 0 - ).")"; + "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 @@ -290,7 +292,7 @@ sub report { sub fbd_sql { my $class = shift; - my %opt = shift; + my %opt = @_; my $date = $opt{date} || time; warn $date; my $agentnum = $opt{agentnum}; @@ -328,7 +330,7 @@ sub fbd_sql { sub fbs_sql { my $class = shift; - my %opt = shift; + my %opt = @_; my $date = $opt{date} || time; my $agentnum = $opt{agentnum}; @@ -371,7 +373,7 @@ sub fbs_sql { sub fvs_sql { my $class = shift; - my %opt = shift; + my %opt = @_; my $date = $opt{date} || time; my $agentnum = $opt{agentnum}; @@ -413,7 +415,7 @@ sub fvs_sql { sub lts_sql { my $class = shift; - my %opt = shift; + my %opt = @_; my $date = $opt{date} || time; my $agentnum = $opt{agentnum}; @@ -465,7 +467,7 @@ sub lts_sql { sub voip_sql { my $class = shift; - my %opt = shift; + my %opt = @_; my $date = $opt{date} || time; my $agentnum = $opt{agentnum}; @@ -510,7 +512,82 @@ sub voip_sql { 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 diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm index 2b6dc6db2..1b82e0ec7 100644 --- a/FS/FS/Schema.pm +++ b/FS/FS/Schema.pm @@ -6682,11 +6682,12 @@ sub tables_hashref { 'zonetype', 'char', '', 1, '', '', 'technology', 'int', '', '', '', '', 'spectrum', 'int', 'NULL', '', '', '', - 'servicetype', 'char', '', '12', '', '', '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, '', '', @@ -6727,10 +6728,9 @@ sub tables_hashref { 'zonenum', 'int', '', '', '', '', 'latitude', 'decimal', '', '10,7', '', '', 'longitude', 'decimal', '', '10,7', '', '', - 'sequence', 'int', '', '', '', '', ], 'primary_key' => 'vertexnum', - 'unique' => [ [ 'zonenum', 'sequence' ] ], + 'unique' => [ ], 'index' => [ ], 'foreign_keys' => [ { columns => [ 'zonenum' ], diff --git a/FS/FS/deploy_zone.pm b/FS/FS/deploy_zone.pm index 227a02236..16f59c81d 100644 --- a/FS/FS/deploy_zone.pm +++ b/FS/FS/deploy_zone.pm @@ -65,10 +65,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 +93,14 @@ 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. @@ -180,25 +184,30 @@ sub check { || $self->ut_textn('description') || $self->ut_number('agentnum') || $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($_)) { - $self->set($_, 0); + if ($self->get('is_broadband')) { + if (!$self->get($_)) { + $self->set($_, 0); + } + } else { + $self->set($_, ''); } } if (!$self->get('active_date')) { @@ -226,7 +235,35 @@ sub element_table { } } -=back +=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 diff --git a/FS/FS/deploy_zone_vertex.pm b/FS/FS/deploy_zone_vertex.pm index a25bfde23..078b32640 100644 --- a/FS/FS/deploy_zone_vertex.pm +++ b/FS/FS/deploy_zone_vertex.pm @@ -47,10 +47,6 @@ Latitude, as a decimal; positive values are north of the Equator. Longitude, as a decimal; positive values are east of Greenwich. -=item sequence - -The ordinal position of this vertex, starting with zero. - =back =head1 METHODS @@ -104,7 +100,6 @@ sub check { || $self->ut_number('zonenum') || $self->ut_coord('latitude') || $self->ut_coord('longitude') - || $self->ut_number('sequence') ; return $error if $error; diff --git a/FS/FS/part_pkg_fcc_option.pm b/FS/FS/part_pkg_fcc_option.pm index a090b96ae..5c78e5f9e 100644 --- a/FS/FS/part_pkg_fcc_option.pm +++ b/FS/FS/part_pkg_fcc_option.pm @@ -139,6 +139,23 @@ tie our %technology_labels, 'Tie::IxHash', ( 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); } @@ -147,6 +164,10 @@ sub technology_labels { Storable::dclone(\%technology_labels); } +sub spectrum_labels { + Storable::dclone(\%spectrum_labels); +} + =head1 BUGS =head1 SEE ALSO diff --git a/httemplate/browse/deploy_zone.html b/httemplate/browse/deploy_zone.html index 489a22658..ddfbde43d 100644 --- a/httemplate/browse/deploy_zone.html +++ b/httemplate/browse/deploy_zone.html @@ -48,9 +48,9 @@ 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', + '(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', @@ -58,6 +58,59 @@ disable_maxselect => 1, disable_total => 1, &> +

Mobile Zones

+<& 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'); @@ -66,7 +119,8 @@ 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 $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; diff --git a/httemplate/browse/part_pkg-fcc.html b/httemplate/browse/part_pkg-fcc.html index 9facd10dc..14dfcba08 100755 --- a/httemplate/browse/part_pkg-fcc.html +++ b/httemplate/browse/part_pkg-fcc.html @@ -30,8 +30,8 @@ my $curuser = $FS::CurrentUser::CurrentUser; -my $edit = 'Edit package definitions'; -my $edit_global = 'Edit global package definitions'; +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); diff --git a/httemplate/edit/deploy_zone-fixed.html b/httemplate/edit/deploy_zone-fixed.html index 8c6d54e5d..1a79500ff 100644 --- a/httemplate/edit/deploy_zone-fixed.html +++ b/httemplate/edit/deploy_zone-fixed.html @@ -21,9 +21,9 @@ type => 'hidden', value => 'B' }, - { field => 'servicetype', + { field => 'is_broadband', type => 'hidden', - value => 'broadband' + value => 'Y', }, 'description', { field => 'active_date', @@ -38,7 +38,7 @@ 'dbaname', { field => 'technology', type => 'select', - options => [ keys(%$technology_labels) ], + options => [ map { @$_ } values(%$media_types) ], labels => $technology_labels, }, { field => 'is_consumer', type => 'checkbox', value=>'Y' }, @@ -71,6 +71,8 @@ die "access denied" ]); 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) = @_; @@ -78,6 +80,7 @@ my $m2_error_callback = sub { /^blocknum\d+/ and length($cgi->param($_.'_censusblock')) } $cgi->param; + sort { $a->censusblock <=> $b->censusblock } map { my $k = $_; FS::deploy_zone_block->new({ diff --git a/httemplate/edit/deploy_zone-mobile.html b/httemplate/edit/deploy_zone-mobile.html new file mode 100644 index 000000000..8e985b1c9 --- /dev/null +++ b/httemplate/edit/deploy_zone-mobile.html @@ -0,0 +1,90 @@ +<& 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; +}; + + diff --git a/httemplate/edit/process/deploy_zone-mobile.html b/httemplate/edit/process/deploy_zone-mobile.html new file mode 100644 index 000000000..c913c5cd6 --- /dev/null +++ b/httemplate/edit/process/deploy_zone-mobile.html @@ -0,0 +1,9 @@ +<& 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 )] + }, +&> diff --git a/httemplate/elements/deploy_zone_vertex.html b/httemplate/elements/deploy_zone_vertex.html new file mode 100644 index 000000000..b3c8b31ea --- /dev/null +++ b/httemplate/elements/deploy_zone_vertex.html @@ -0,0 +1,45 @@ +% unless ( $opt{'js_only'} ) { + + + Latitude  + + > +   + Longitude  + + > +% } +<%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; + + diff --git a/httemplate/misc/part_pkg_fcc_options.html b/httemplate/misc/part_pkg_fcc_options.html index a5ecb12bc..27b45e003 100644 --- a/httemplate/misc/part_pkg_fcc_options.html +++ b/httemplate/misc/part_pkg_fcc_options.html @@ -92,10 +92,19 @@
+
<& .checkbox, 'voip_lastmile' &>

+

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

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

@@ -168,7 +177,7 @@ function enable_fieldset(fieldset_id) { // set up all event handlers addEventListener(form, 'submit', save_changes); - var sections = [ 'broadband', 'phone', 'voip' ]; + 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])); diff --git a/httemplate/search/report_477.html b/httemplate/search/report_477.html index 78ba35cfc..cbbd5d902 100755 --- a/httemplate/search/report_477.html +++ b/httemplate/search/report_477.html @@ -4,11 +4,13 @@ % $m->abort; % } <& /elements/header.html, 'FCC Form 477 Report' &> +% if ( $curuser->access_right('Edit FCC report configuration') ) { Preparation +% }
@@ -48,8 +50,9 @@ <& /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;