From d7cf0d6bb3b81b1c91ef1bcc3252d56f96b65b0f Mon Sep 17 00:00:00 2001 From: Mark Wells Date: Tue, 5 Aug 2014 15:54:51 -0700 Subject: [PATCH] 477 report: improve browse-edit UI --- FS/FS/Report/FCC_477.pm | 180 +++++++++++++------------ FS/FS/Schema.pm | 10 +- FS/FS/deploy_zone.pm | 19 +++ FS/FS/part_pkg.pm | 8 +- httemplate/browse/part_pkg-fcc.html | 67 +++++---- httemplate/edit/deploy_zone-fixed.html | 5 + httemplate/edit/process/bulk-part_pkg-fcc.html | 42 ++++++ httemplate/elements/input-fcc_options.html | 2 +- httemplate/search/477.html | 34 +++-- httemplate/search/report_477.html | 21 +-- 10 files changed, 247 insertions(+), 141 deletions(-) create mode 100644 httemplate/edit/process/bulk-part_pkg-fcc.html diff --git a/FS/FS/Report/FCC_477.pm b/FS/FS/Report/FCC_477.pm index bf4754d0e..86fa0a616 100644 --- a/FS/FS/Report/FCC_477.pm +++ b/FS/FS/Report/FCC_477.pm @@ -8,6 +8,9 @@ use vars qw( @upload @download @technology @part2aoption @part2boption ); use FS::Record qw( dbh ); +use Tie::IxHash; +use Storable; + $DEBUG = 1; =head1 NAME @@ -213,6 +216,8 @@ sub statenum2state { =cut +# functions for internal use + sub join_optionnames { join(' ', map { join_optionname($_) } @_); } @@ -259,25 +264,69 @@ sub is_fixed_broadband { ).")"; } -=item report_fixed_broadband OPTIONS - -Returns the Fixed Broadband Subscription report (section 5.4), as an arrayref -of an arrayrefs. OPTIONS may contain: -- date: a timestamp value to count active packages as of that date -- agentnum: limit to customers of that agent +=item report SECTION, OPTIONS -Columns of this report are: -- census tract -- technology code -- downstream speed -- upstream speed -(the above columns form a key) -- number of subscriptions -- number of consumer-grade subscriptions +Returns the report section SECTION (see the C 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 report_fixed_broadband { +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 = shift; + 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 = shift; my $date = $opt{date} || time; @@ -311,31 +360,16 @@ sub report_fixed_broadband { 'broadband_downstream, broadband_upstream '; my $order_by = $group_by; - my $statement = "SELECT ".join(', ', @select) . " + "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 report_fixed_voice OPTIONS - -Returns the Fixed Voice Subscription Detail report (section 5.5). OPTIONS -are as above. Columns are: - -- census tract -- service type (0 for non-VoIP, 1 for VoIP) -(the above columns form a key) -- VGE lines/VoIP subscriptions in service -- consumer grade VGE lines/VoIP subscriptions - -=cut - -sub report_fixed_voice { +sub fvs_sql { my $class = shift; my %opt = shift; my $date = $opt{date} || time; @@ -368,41 +402,16 @@ sub report_fixed_voice { my $group_by = 'cust_location.censustract, COALESCE(is_voip, 0)'; my $order_by = $group_by; - my $statement = "SELECT ".join(', ', @select) . " + "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 report_local_phone OPTIONS - -Returns the Local Exchange Telephone Subscription report (section 5.6). -OPTIONS are as above. Each row is data for one state. Columns are: - -- state FIPS code (key) -- wholesale switched voice lines -- wholesale unswitched local loops -- end-user total lines -- end-user lines sold in a package with broadband -- consumer-grade lines where you are not the long-distance carrier -- consumer-grade lines where the carrier IS the long-distance carrier -- business-grade lines where you are not the long-distance carrier -- business-grade lines where the carrier IS the long-distance carrier -- end-user lines where you own the local loop facility -- end-user lines where you lease an unswitched local loop from a LEC -- end-user lines resold from another carrier -- end-user lines provided over fiber to the premises -- end-user lines provided over coaxial -- end-user lines provided over fixed wireless - -=cut - -sub report_local_phone { +sub lts_sql { my $class = shift; my %opt = shift; my $date = $opt{date} || time; @@ -446,37 +455,15 @@ sub report_local_phone { my $group_by = 'state.fips'; my $order_by = $group_by; - my $statement = "SELECT ".join(', ', @select) . " + "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 report_voip OPTIONS - -Returns the Interconnected VoIP Subscription report (section 5.7). -OPTIONS are as above. Columns are: - -- state FIPS code (key) -- OTT subscriptions (non-last-mile) -- OTT subscriptions sold to consumers -- last-mile subscriptions -- last-mile subscriptions sold to consumers -- last-mile subscriptions bundled with broadband Internet -- last-mile subscriptions over copper pairs -- last-mile subscriptions over coaxial -- last-mile subscriptions over fiber -- last-mile subscriptions over fixed wireless -- last-mile subscriptions over other media - -=cut - -sub report_voip { +sub voip_sql { my $class = shift; my %opt = shift; my $date = $opt{date} || time; @@ -517,16 +504,37 @@ sub report_voip { my $group_by = 'state.fips'; my $order_by = $group_by; - my $statement = "SELECT ".join(', ', @select) . " + "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 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; diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm index 830b39a84..2b6dc6db2 100644 --- a/FS/FS/Schema.pm +++ b/FS/FS/Schema.pm @@ -6683,12 +6683,14 @@ sub tables_hashref { 'technology', 'int', '', '', '', '', 'spectrum', 'int', 'NULL', '', '', '', 'servicetype', 'char', '', '12', '', '', - 'adv_speed_up', 'decimal', 'NULL', '10,3', '', '', - 'adv_speed_down', 'decimal', 'NULL', '10,3', '', '', - 'cir_speed_up', 'decimal', 'NULL', '10,3', '', '', - 'cir_speed_down', 'decimal', 'NULL', '10,3', '', '', + '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_consumer', 'char', 'NULL', 1, '', '', 'is_business', 'char', 'NULL', 1, '', '', + 'active_date', @date_type, '', '', + 'expire_date', @date_type, '', '', ], 'primary_key' => 'zonenum', 'unique' => [], diff --git a/FS/FS/deploy_zone.pm b/FS/FS/deploy_zone.pm index 3caeda24b..227a02236 100644 --- a/FS/FS/deploy_zone.pm +++ b/FS/FS/deploy_zone.pm @@ -97,6 +97,14 @@ type of service is sold. 'Y' if this service is sold to business or institutional use. Not mutually exclusive with is_consumer. +=item active_date + +The date this zone became active. + +=item expire_date + +The date this zone became inactive, if any. + =back =head1 METHODS @@ -183,9 +191,20 @@ sub check { || $self->ut_decimaln('cir_speed_down', 3) || $self->ut_flag('is_consumer') || $self->ut_flag('is_business') + || $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('active_date')) { + $self->set('active_date', time); + } + $self->SUPER::check; } diff --git a/FS/FS/part_pkg.pm b/FS/FS/part_pkg.pm index 741eb8741..06f304a22 100644 --- a/FS/FS/part_pkg.pm +++ b/FS/FS/part_pkg.pm @@ -338,7 +338,7 @@ sub insert { if ( $options{fcc_options} ) { warn " updating fcc options " if $DEBUG; - $self->process_fcc_options( $options{fcc_options} ); + $self->set_fcc_options( $options{fcc_options} ); } warn " committing transaction" if $DEBUG and $oldAutoCommit; @@ -624,7 +624,7 @@ sub replace { if ( $options->{fcc_options} ) { warn " updating fcc options " if $DEBUG; - $new->process_fcc_options( $options->{fcc_options} ); + $new->set_fcc_options( $options->{fcc_options} ); } warn " committing transaction" if $DEBUG and $oldAutoCommit; @@ -787,14 +787,14 @@ sub propagate { join("\n", @error); } -=item process_fcc_options HASHREF +=item set_fcc_options HASHREF Sets the FCC options on this package definition to the values specified in HASHREF. =cut -sub process_fcc_options { +sub set_fcc_options { my $self = shift; my $pkgpart = $self->pkgpart; my $options; diff --git a/httemplate/browse/part_pkg-fcc.html b/httemplate/browse/part_pkg-fcc.html index 9462c3248..9facd10dc 100755 --- a/httemplate/browse/part_pkg-fcc.html +++ b/httemplate/browse/part_pkg-fcc.html @@ -3,14 +3,13 @@ 'menubar' => \@menubar, 'html_init' => $html_init, 'html_form' => $html_form, - 'html_posttotal' => $html_posttotal, 'name' => 'package definitions', 'disableable' => 1, 'disabled_statuspos' => 4, 'agent_virt' => 1, 'agent_null_right' => [ $edit, $edit_global ], 'agent_null_right_link' => $edit_global, - 'agent_pos' => 6, + 'agent_pos' => 3, 'query' => { 'select' => $select, 'table' => 'part_pkg', @@ -39,6 +38,14 @@ 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'; @@ -88,22 +95,8 @@ my $select = join(',', my $addl_from = FS::Report::FCC_477::join_optionnames(@optionnames); -#restore this so pagination works $cgi->param('classnum', $classnum) if length($classnum); -#should hide this if there aren't any classes -my $html_posttotal = - "
( 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, - ). - ' )'; - my $link = [ $p.'edit/part_pkg.cgi?', 'pkgpart' ]; my @header = ( '#', 'Package', 'Comment' ); @@ -176,24 +169,42 @@ $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; + '' . $cgi->param("error$pkgpart") || '' . '' + }; + $align .= 'l'; +} + my $html_init = include('/elements/init_overlib.html') . include('/elements/input-fcc_options.html', js_only => 1) . include('.style'); -my $html_form = ''; -my $html_foot = ''; -# insert a checkbox column -unshift @header, ''; -unshift @fields, sub { - ''; -}; -unshift @links, ''; -$align = 'c'.$align; - +my $html_form = qq!
+ ( 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, + ). + ' ) +

' . + qq!!; -$html_form = qq!!; -$html_foot = qq! +my $html_foot = qq!
!; diff --git a/httemplate/edit/deploy_zone-fixed.html b/httemplate/edit/deploy_zone-fixed.html index ecec9c434..8c6d54e5d 100644 --- a/httemplate/edit/deploy_zone-fixed.html +++ b/httemplate/edit/deploy_zone-fixed.html @@ -14,6 +14,7 @@ 'is_consumer' => 'Consumer/mass market', 'is_business' => 'Business/government', 'blocknum' => '', + 'active_date' => 'Active since', }, 'fields' => [ { field => 'zonetype', @@ -25,6 +26,10 @@ value => 'broadband' }, 'description', + { field => 'active_date', + type => 'fixed-date', + value => time, + }, { field => 'agentnum', type => 'select-agent', disable_empty => 1, diff --git a/httemplate/edit/process/bulk-part_pkg-fcc.html b/httemplate/edit/process/bulk-part_pkg-fcc.html new file mode 100644 index 000000000..17579aa61 --- /dev/null +++ b/httemplate/edit/process/bulk-part_pkg-fcc.html @@ -0,0 +1,42 @@ +% 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; +die "access denied" + unless $curuser->access_right('Bulk edit package definitions'); + +# 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'); + + diff --git a/httemplate/elements/input-fcc_options.html b/httemplate/elements/input-fcc_options.html index b191e1c07..1d56cf274 100644 --- a/httemplate/elements/input-fcc_options.html +++ b/httemplate/elements/input-fcc_options.html @@ -39,7 +39,7 @@ function show_fcc_options(id) { } var media = String.toLowerCase(curr_values['media'] || 'unknown media'); if ( curr_values['is_consumer'] ) { - out += '
  • Consumer-grade service
    • >'; + out += '
  • Consumer-grade service
    • '; } else { out += '
  • Business-grade service
    • '; } diff --git a/httemplate/search/477.html b/httemplate/search/477.html index 26bd9f33b..fb85f1e09 100644 --- a/httemplate/search/477.html +++ b/httemplate/search/477.html @@ -41,7 +41,7 @@ a.download { % $cgi->param('type', 'csv'); % my $header = ".header_$partname"; @@ -81,8 +81,7 @@ if ($cgi->param('agentnum') =~ /^(\d+)$/ ) { my $date = parse_datetime($cgi->param('date')) || time; my @partnames = grep /^\w+$/, $cgi->param('parts'); foreach my $partname (@partnames) { - my $method = "report_$partname"; - $parts{$partname} ||= FS::Report::FCC_477->$method( + $parts{$partname} ||= FS::Report::FCC_477->report( $partname, date => $date, agentnum => $agentnum ); @@ -109,8 +108,27 @@ if ( $cgi->param('type') eq 'csv' ) { $m->abort; } +my $part_titles = FS::Report::FCC_477->parts; + -<%def .header_fixed_broadband> +<%def .header_fbd> + + + + + + + + + + + + + + + + +<%def .header_fbs> @@ -124,7 +142,7 @@ if ( $cgi->param('type') eq 'csv' ) { -<%def .header_fixed_voice> +<%def .header_fvs> @@ -135,7 +153,7 @@ if ( $cgi->param('type') eq 'csv' ) { -<%def .header_local_phone> +<%def .header_lts> @@ -193,7 +211,7 @@ if ( $cgi->param('type') eq 'csv' ) { -<%def .header_mobile_broadband> +<%def .header_mbs> %# unimplemented @@ -207,7 +225,7 @@ if ( $cgi->param('type') eq 'csv' ) { -<%def .header_mobile_voice> +<%def .header_mvs> %# unimplemented diff --git a/httemplate/search/report_477.html b/httemplate/search/report_477.html index 2a6878ef4..78ba35cfc 100755 --- a/httemplate/search/report_477.html +++ b/httemplate/search/report_477.html @@ -4,7 +4,12 @@ % $m->abort; % } <& /elements/header.html, 'FCC Form 477 Report' &> - +Preparation + +
    - <% $parttitle{$partname} %> + <% $part_titles->{$partname} %> Download
    Census BlockDBA NameTechnologyConsumer?Advertised Speed (Mbps)Business?Contractual Speed (Mbps)
    DownUpDownUp
    Census Tract TechnologyConsumer
    Census Tract VoIP?Consumer
    State WholesaleOther
    StateConsumer
    State
    @@ -30,15 +35,8 @@ <& /elements/tr-checkbox-multiple.html, 'label' => 'Enable parts', 'field' => 'parts', - 'labels' => { - fixed_broadband => 'Fixed Broadband Subscription', - #7 => 'Part 7 (Mobile Wireless Broadband Subscription), - #8 => 'Part 8 (Mobile Local Telephone Subscription), - fixed_voice => 'Voice Telephone Subscription', - local_phone => 'Local Exchange Telephone Subscription', - voip => 'Interconnected VoIP Subscription', - }, - options => [ 6, 9, 10, 11 ], + 'labels' => $part_titles, + 'options' => [ keys %$part_titles ] &>
    @@ -54,4 +52,7 @@ die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('List packages'); my $conf = FS::Conf->new; + +my $part_titles = FS::Report::FCC_477->parts; + -- 2.11.0