diff options
author | Mark Wells <mark@freeside.biz> | 2014-08-05 15:54:51 -0700 |
---|---|---|
committer | Mark Wells <mark@freeside.biz> | 2014-08-05 15:54:51 -0700 |
commit | d7cf0d6bb3b81b1c91ef1bcc3252d56f96b65b0f (patch) | |
tree | 1a527c06c1336bb6a81b3839bbd480122cec73de /FS | |
parent | 0f359d5480aa1621d73ee802f420e8951abc620d (diff) |
477 report: improve browse-edit UI
Diffstat (limited to 'FS')
-rw-r--r-- | FS/FS/Report/FCC_477.pm | 180 | ||||
-rw-r--r-- | FS/FS/Schema.pm | 10 | ||||
-rw-r--r-- | FS/FS/deploy_zone.pm | 19 | ||||
-rw-r--r-- | FS/FS/part_pkg.pm | 8 |
4 files changed, 123 insertions, 94 deletions
diff --git a/FS/FS/Report/FCC_477.pm b/FS/FS/Report/FCC_477.pm index bf4754d..86fa0a6 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<parts> 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 830b39a..2b6dc6d 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 3caeda2..227a022 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 741eb87..06f304a 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; |