);
use FS::Record qw( dbh );
+use Tie::IxHash;
+use Storable;
+
$DEBUG = 1;
=head1 NAME
=cut
+# functions for internal use
+
sub join_optionnames {
join(' ', map { join_optionname($_) } @_);
}
).")";
}
-=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;
'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;
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;
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;
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;
'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' => [],
'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
|| $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;
}
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;
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;
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;
'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',
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';
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 =
- "<BR>( 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' );
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;
+ '<B><FONT COLOR="#ffffff">' . $cgi->param("error$pkgpart") || '' . '</FONT></B>'
+ };
+ $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 {
- '<INPUT TYPE="checkbox" NAME="pkgpart" VALUE=' . $_[0]->pkgpart .'>';
-};
-unshift @links, '';
-$align = 'c'.$align;
-
+my $html_form = qq!<FORM ACTION="${p}edit/process/bulk-part_pkg-fcc.html" METHOD="POST">
+ ( 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,
+ ).
+ ' )
+ <BR><BR>' .
+ qq!<SCRIPT TYPE="text/javascript">
+ function filter_change() {
+ window.location = '! . $cgi->self_url . qq!?classnum='
+ + document.getElementById('classnum').value;
+ }
+ </SCRIPT>!;
-$html_form = qq!<FORM ACTION="${p}edit/process/bulk-part_pkg-fcc.html" METHOD="POST">!;
-$html_foot = qq!
+my $html_foot = qq!
<INPUT TYPE="submit" VALUE="Save changes">
</FORM>!;
'is_consumer' => 'Consumer/mass market',
'is_business' => 'Business/government',
'blocknum' => '',
+ 'active_date' => 'Active since',
},
'fields' => [
{ field => 'zonetype',
value => 'broadband'
},
'description',
+ { field => 'active_date',
+ type => 'fixed-date',
+ value => time,
+ },
{ field => 'agentnum',
type => 'select-agent',
disable_empty => 1,
--- /dev/null
+% 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');
+
+</%init>
}
var media = String.toLowerCase(curr_values['media'] || 'unknown media');
if ( curr_values['is_consumer'] ) {
- out += '<li><strong>Consumer-grade</strong> service</li>>';
+ out += '<li><strong>Consumer-grade</strong> service</li>';
} else {
out += '<li><strong>Business-grade</strong> service</li>';
}
% $cgi->param('type', 'csv');
<table class="fcc477part">
<caption>
- <span class="parttitle"><% $parttitle{$partname} %></span>
+ <span class="parttitle"><% $part_titles->{$partname} %></span>
<a class="download" href="<% $cgi->self_url %>">Download</a>
</caption>
% my $header = ".header_$partname";
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
);
$m->abort;
}
+my $part_titles = FS::Report::FCC_477->parts;
+
</%init>
-<%def .header_fixed_broadband>
+<%def .header_fbd>
+ <TR CLASS="head">
+ <TD ROWSPAN=2>Census Block</TD>
+ <TD ROWSPAN=2>DBA Name</TD>
+ <TD ROWSPAN=2>Technology</TD>
+ <TD ROWSPAN=2>Consumer?</TD>
+ <TD COLSPAN=2>Advertised Speed (Mbps)</TD>
+ <TD ROWSPAN=2>Business?</TD>
+ <TD COLSPAN=2>Contractual Speed (Mbps)</TD>
+ </TR>
+ <TR CLASS="subhead">
+ <TD>Down</TD>
+ <TD>Up</TD>
+ <TD>Down</TD>
+ <TD>Up</TD>
+ </TR>
+</%def>
+<%def .header_fbs>
<TR CLASS="head">
<TD ROWSPAN=2>Census Tract</TD>
<TD ROWSPAN=2>Technology</TD>
<TD>Consumer</TD>
</TR>
</%def>
-<%def .header_fixed_voice>
+<%def .header_fvs>
<TR CLASS="head">
<TD ROWSPAN=2>Census Tract</TD>
<TD ROWSPAN=2>VoIP?</TD>
<TD>Consumer</TD>
</TR>
</%def>
-<%def .header_local_phone>
+<%def .header_lts>
<TR CLASS="head">
<TD ROWSPAN=3>State</TD>
<TD COLSPAN=2>Wholesale</TD>
<TD>Other</TD>
</TR>
</%def>
-<%def .header_mobile_broadband>
+<%def .header_mbs>
%# unimplemented
<TR CLASS="head">
<TD ROWSPAN=2>State</TD>
<TD>Consumer</TD>
</TR>
</%def>
-<%def .header_mobile_voice>
+<%def .header_mvs>
%# unimplemented
<TR CLASS="head">
<TD ROWSPAN=2>State</TD>
% $m->abort;
% }
<& /elements/header.html, 'FCC Form 477 Report' &>
-
+<FONT SIZE="+1"><STRONG>Preparation</STRONG></FONT>
+<UL>
+ <LI> <A HREF="<% $p %>browse/part_pkg-fcc.html">Configure packages</A> for FCC reporting categories.</LI>
+ <LI> <A HREF="<% $p %>browse/deploy_zone.html">Enter deployment zones</A> for broadband Internet or mobile phone.</LI>
+</UL>
+
<FORM ACTION="477.html" METHOD="GET">
<TABLE BGCOLOR="#cccccc" CELLSPACING=0>
<& /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 ]
&>
</TABLE>
unless $FS::CurrentUser::CurrentUser->access_right('List packages');
my $conf = FS::Conf->new;
+
+my $part_titles = FS::Report::FCC_477->parts;
+
</%init>
projects / freeside.git / commitdiff