diff options
| -rw-r--r-- | FS/FS/Conf.pm | 2 | ||||
| -rw-r--r-- | FS/FS/Schema.pm | 1 | ||||
| -rw-r--r-- | FS/FS/part_pkg.pm | 20 | ||||
| -rwxr-xr-x | httemplate/edit/part_pkg.cgi | 4 | ||||
| -rw-r--r-- | httemplate/elements/tr-select-voip_class.html | 24 | ||||
| -rwxr-xr-x | httemplate/search/477partIIB.html | 194 |
6 files changed, 171 insertions, 74 deletions
diff --git a/FS/FS/Conf.pm b/FS/FS/Conf.pm index 42432b2a4..667263f9c 100644 --- a/FS/FS/Conf.pm +++ b/FS/FS/Conf.pm @@ -3301,7 +3301,7 @@ and customer address. Include units.', { 'key' => 'cust_pkg-show_fcc_voice_grade_equivalent', 'section' => 'UI', - 'description' => "Show a field on package definitions for assigning a DS0 equivalency number suitable for use on FCC form 477.", + 'description' => "Show fields on package definitions for FCC Form 477 classification", 'type' => 'checkbox', }, diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm index 709e9f9c6..ab679c491 100644 --- a/FS/FS/Schema.pm +++ b/FS/FS/Schema.pm @@ -1911,6 +1911,7 @@ sub tables_hashref { 'credit_weight', 'real', 'NULL', '', '', '', 'agentnum', 'int', 'NULL', '', '', '', 'fcc_ds0s', 'int', 'NULL', '', '', '', + 'fcc_voip_class','char', 'NULL', 1, '', '', 'no_auto', 'char', 'NULL', 1, '', '', 'recur_show_zero', 'char', 'NULL', 1, '', '', 'setup_show_zero', 'char', 'NULL', 1, '', '', diff --git a/FS/FS/part_pkg.pm b/FS/FS/part_pkg.pm index 061001bdc..91bcdc5b5 100644 --- a/FS/FS/part_pkg.pm +++ b/FS/FS/part_pkg.pm @@ -103,6 +103,9 @@ inherits from FS::Record. The following fields are currently supported: =item fcc_ds0s - Optional DS0 equivalency number for FCC form 477 +=item fcc_voip_class - Which column of FCC form 477 part II.B this package +belongs in. + =item successor - Foreign key for the part_pkg that replaced this record. If this record is not obsolete, will be null. @@ -622,6 +625,7 @@ sub check { : $self->ut_agentnum_acl('agentnum', \@null_agentnum_right) ) || $self->ut_numbern('fcc_ds0s') + || $self->ut_numbern('fcc_voip_class') || $self->ut_foreign_keyn('successor', 'part_pkg', 'pkgpart') || $self->ut_foreign_keyn('family_pkgpart', 'part_pkg', 'pkgpart') || $self->SUPER::check @@ -1592,6 +1596,22 @@ sub _upgrade_data { # class method } } + # set any package with FCC voice lines to the "VoIP with broadband" category + # for backward compatibility + my $journal = 'part_pkg_fcc_voip_class'; + if (!FS::upgrade_journal->is_done($journal)) { + @part_pkg = qsearch('part_pkg', { + fcc_ds0s => { op => '>', value => 0 }, + fcc_voip_class => '' + }); + foreach my $part_pkg (@part_pkg) { + $part_pkg->set(fcc_voip_class => 2); + my $error = $part_pkg->replace; + die $error if $error; + } + FS::upgrade_journal->set_done($journal); + } + } =item curuser_pkgs_sql diff --git a/httemplate/edit/part_pkg.cgi b/httemplate/edit/part_pkg.cgi index cd0731370..f3ad8f52d 100755 --- a/httemplate/edit/part_pkg.cgi +++ b/httemplate/edit/part_pkg.cgi @@ -55,6 +55,7 @@ 'svc_dst_pkgpart' => 'Include services of package', 'report_option' => 'Report classes', 'fcc_ds0s' => 'Voice-grade equivalents', + 'fcc_voip_class' => 'Category', }, 'fields' => [ @@ -196,6 +197,9 @@ { type => 'tablebreak-tr-title', value => 'FCC Form 477 information', }, + { field=>'fcc_voip_class', + type=>'select-voip_class', + }, { field=>'fcc_ds0s', type=>'text', size=>6 }, ) : () diff --git a/httemplate/elements/tr-select-voip_class.html b/httemplate/elements/tr-select-voip_class.html new file mode 100644 index 000000000..dcc1487cc --- /dev/null +++ b/httemplate/elements/tr-select-voip_class.html @@ -0,0 +1,24 @@ +<& tr-td-label.html, label => 'Category', @_ &> +<TD> +<SELECT NAME="<% $opt{'field'} %>"> +% while(@options) { +% my $value = shift @options; +% my $selected = ($value eq $opt{'curr_value'}) ? 'SELECTED' : ''; + <OPTION VALUE="<% $value %>" <% $selected %>><% shift @options %></OPTION> +% } +</SELECT> +</TD></TR> +<%init> +my %opt = ( + field => 'fcc_voip_class', + label => 'Category', + @_ +); +my @options = ( + '' => '', + 1 => 'VoIP without Broadband', + 2 => 'VoIP with Broadband', + 3 => 'Wholesale VoIP' +); + +</%init> diff --git a/httemplate/search/477partIIB.html b/httemplate/search/477partIIB.html index 94aa818fb..c58310d36 100755 --- a/httemplate/search/477partIIB.html +++ b/httemplate/search/477partIIB.html @@ -1,17 +1,44 @@ -<% include( 'elements/search.html', - 'html_init' => $html_init, - 'name' => 'lines', - 'query' => $query, - 'count_query' => 'SELECT 11', - 'really_disable_download' => 1, - 'disable_download' => 1, - 'nohtmlheader' => 1, - 'disable_total' => 1, - 'header' => [ @headers ], - 'xml_elements' => [ @xml_elements ], - 'fields' => [ @fields ], - ) -%> +% if ( $cgi->param('_type') eq 'xml' ) { +% my @cols = qw(a b c); +% for ( my $row = 0; $row < scalar(@rows); $row++ ) { +% for my $col (0..2) { +% if ( exists($data[$col][$row]) ) { +<PartII_<% $row %><% $cols[$col] %>> +% } +</PartII_<% $row %><% $cols[$col] %>> +% } #for $col +% } #for $row +% } else { # HTML mode +% # fake up the search-html.html header +<H2>Part IIB</H2> +<TABLE> + <TR><TD VALIGN="bottom"><BR></TD></TR> + <TR><TD COLSPAN=2> + <TABLE CLASS="grid" CELLSPACING=0 STYLE="border: 1px solid #cccccc;" BGCOLOR="#cccccc"> + <TR> +% foreach (@headers) { + <TH class="grid"><% $_ %></TH> +% } + </TR> +% my @bgcolor = ('eeeeee','ffffff'); +% my $row = 0; +% foreach my $rowhead (@rows) { + <TR> + <TD CLASS="grid" BGCOLOR="#<% $bgcolor[$row % 2] %>"><% $rowhead %></TD> +% for my $col (0..2) { + <TD CLASS="grid" BGCOLOR="#<% $bgcolor[$row % 2] %>"> +% if ( exists($data[$col][$row]) ) { + <% $data[$col][$row] %> +% } + </TD> +% } # for $col + </TR> +% $row++; +% } #for $rowhead + </TABLE> + </TD></TR> +</TABLE> +% } #XML/HTML <%init> my $curuser = $FS::CurrentUser::CurrentUser; @@ -19,67 +46,89 @@ my $curuser = $FS::CurrentUser::CurrentUser; die "access denied" unless $curuser->access_right('List packages'); -my $html_init = '<H2>Part IIB</H2>'; my %search_hash = (); - -for ( qw(agentnum magic) ) { - $search_hash{$_} = $cgi->param($_) if $cgi->param($_); -} -$search_hash{'classnum'} = [ $cgi->param('classnum') ]; - -my @row_option = grep { /^\d+$/ } $cgi->param('part2b_row_option') - if $cgi->param('part2b_row_option'); - -# fudge in 2nd row -unshift @row_option, $row_option[0]; - -my $query = 'SELECT '. join(' UNION SELECT ', 1..8); - -my $total_count = 0; -my $column_value = sub { - my $row = shift; - - my @report_option = ( $row_option[$row - 1] || '' ); - my $sql_query = FS::cust_pkg->search( - { %search_hash, 'report_option' => join(',', @report_option) } - ); - - my $count_sql = delete($sql_query->{'count_query'}); - if ( $row == 2 ) { - $count_sql =~ s/COUNT\(\*\) FROM/sum(COALESCE(CASE WHEN cust_main.company IS NULL OR cust_main.company = '' THEN CASE WHEN part_pkg.fcc_ds0s IS NOT NULL AND part_pkg.fcc_ds0s > 0 THEN part_pkg.fcc_ds0s WHEN pkg_class.fcc_ds0s IS NOT NULL AND pkg_class.fcc_ds0s > 0 THEN pkg_class.fcc_ds0s ELSE 0 END ELSE 0 END, 0) ) FROM/ - or die "couldn't parse count_sql"; - } else { - $count_sql =~ s/COUNT\(\*\) FROM/sum(COALESCE(CASE WHEN part_pkg.fcc_ds0s IS NOT NULL AND part_pkg.fcc_ds0s > 0 THEN part_pkg.fcc_ds0s WHEN pkg_class.fcc_ds0s IS NOT NULL AND pkg_class.fcc_ds0s > 0 THEN pkg_class.fcc_ds0s ELSE 0 END, 0)) FROM/ - or die "couldn't parse count_sql"; - } - - my $count_sth = dbh->prepare($count_sql) - or die "Error preparing $count_sql: ". dbh->errstr; - $count_sth->execute - or die "Error executing $count_sql: ". $count_sth->errstr; - my $count_arrayref = $count_sth->fetchrow_arrayref; - my $count = $count_arrayref->[0]; +$search_hash{'agentnum'} = $cgi->param('agentnum'); +$search_hash{'state'} = $cgi->param('state'); +$search_hash{'classnum'} = [ $cgi->param('classnum') ]; +$search_hash{'status'} = 'active'; - $total_count = $count if $row == 1; - $count = sprintf('%.2f', $total_count ? 100*$count/$total_count : 0) - if $row != 1; +my @row_option; +foreach ($cgi->param('part2b_row_option')) { + push @row_option, (/^\d+$/ ? $_ : undef); +} - return "$count"; +my $is_residential = "AND COALESCE(cust_main.company, '') = ''"; +my $has_report_option = sub { + map { + defined($row_option[$_]) ? + "AND EXISTS( + SELECT 1 FROM part_pkg_option + WHERE part_pkg_option.pkgpart = part_pkg.pkgpart + AND optionname = 'report_option_" . $row_option[$_]."' + AND optionvalue = '1' + )" : 'AND FALSE' + } @_ }; -my @headers = ( - '', - 'without broadband', - 'with broadband', - 'wholesale', +# an arrayref for each column +my @data; +# get the skeleton of the query +my $sql_query = FS::cust_pkg->search(\%search_hash); +my $from_where = $sql_query->{'count_query'}; +$from_where =~ s/^SELECT COUNT\(\*\) //; +# columns 1 and 2 +my $query_ds0 = "SELECT SUM(COALESCE(part_pkg.fcc_ds0s, pkg_class.fcc_ds0s, 0)) + $from_where"; +# column 3 +my $query_custnum = "SELECT COUNT(DISTINCT cust_pkg.custnum) $from_where"; + +my @base_queries = ($query_ds0, $query_ds0, $query_custnum); +my @col_conds = ( + # column 1 + [ + '', + $is_residential, + $has_report_option->(0), # nomadic + ], + # column 2 + [ + '', + $is_residential, + $has_report_option->(0..5), + ], + # column 3 + [ + '' + ] ); -my @xml_elements = ( - sub { my $row = shift; my $rownum = $row->[0] + 1; "PartII_${rownum}a" }, - sub { my $row = shift; my $rownum = $row->[0] + 1; "PartII_${rownum}b" }, - sub { my $row = shift; my $rownum = $row->[0] + 1; "PartII_${rownum}c" }, -); +my $col = 0; +foreach (@col_conds) { + my @col_data; + my $row = 0; + foreach my $cond (@{ $col_conds[$col] }) { + # three parts: the select expression, the VoIP class (column selection), + # and the row selection + my $query = $base_queries[$col] . + " AND part_pkg.fcc_voip_class = '".($col+1)."' + $cond"; + my $count = FS::Record->scalar_sql($query) || 0; + if ( $row == 0 ) { + $col_data[$row] = $count; # the raw count + } else { + if ( $col_data[0] == 0 ) { + $col_data[$row] = ''; # show nothing in this row, then + } else { + $col_data[$row] = sprintf('%.2f', 100 * $count / $col_data[0]) . '%'; + } + } #if $row == 0 + $row++; + } + $data[$col] = \@col_data; + $col++; +} + my @rows = ( 'total number', @@ -92,12 +141,11 @@ my @rows = ( '% other broadband', ); -my @fields = ( - sub { my $row = shift; $rows[$row->[0] - 1]; }, - sub { 0; }, - sub { my $row = shift; &{$column_value}($row->[0]); }, - sub { 0; }, +my @headers = ( + '', + 'without broadband', + 'with broadband', + 'wholesale', ); -shift @fields if $cgi->param('_type') eq 'xml'; </%init> |