diff options
| -rw-r--r-- | FS/FS/Report/FCC_477.pm | 94 | ||||
| -rw-r--r-- | FS/FS/Report/Table.pm | 22 | ||||
| -rw-r--r-- | FS/FS/Report/Tax.pm | 79 | ||||
| -rw-r--r-- | FS/FS/Template_Mixin.pm | 44 | ||||
| -rw-r--r-- | FS/FS/cust_pkg/Search.pm | 3 | ||||
| -rw-r--r-- | FS/FS/discount.pm | 4 | ||||
| -rw-r--r-- | FS/FS/part_event/Condition/signupdate_age.pm | 1 | ||||
| -rw-r--r-- | httemplate/edit/cust_location-censustract.html | 66 | ||||
| -rw-r--r-- | httemplate/edit/process/bulk-477_cust_pkg.html | 20 | ||||
| -rw-r--r-- | httemplate/edit/process/cust_location-censustract.html | 34 | ||||
| -rw-r--r-- | httemplate/graph/cust_bill_pkg_discount.html | 2 | ||||
| -rw-r--r-- | httemplate/graph/report_cust_bill_pkg_discount.html | 2 | ||||
| -rw-r--r-- | httemplate/search/477.html | 46 | ||||
| -rw-r--r-- | httemplate/search/477_cust_pkg.html | 228 | ||||
| -rw-r--r-- | httemplate/search/cust_bill_pkg.cgi | 62 | ||||
| -rw-r--r-- | httemplate/search/report_tax.cgi | 9 |
16 files changed, 620 insertions, 96 deletions
diff --git a/FS/FS/Report/FCC_477.pm b/FS/FS/Report/FCC_477.pm index 20d402d7d..af45b2dac 100644 --- a/FS/FS/Report/FCC_477.pm +++ b/FS/FS/Report/FCC_477.pm @@ -265,7 +265,8 @@ sub active_on { # "suspended as of some past date" is a complicated query.) my $date = shift; "cust_pkg.setup <= $date AND ". - "(cust_pkg.cancel IS NULL OR cust_pkg.cancel > $date)"; + "(cust_pkg.cancel IS NULL OR cust_pkg.cancel > $date) AND ". + "(cust_pkg.change_date IS NULL OR cust_pkg.change_date <= $date)" } sub is_fixed_broadband { @@ -276,28 +277,68 @@ 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 Returns the report section SECTION (see the C<parts> method for section -name strings) as an arrayref of arrayrefs. OPTIONS may contain the following: +name strings). OPTIONS may contain the following: - date: a timestamp value. Packages that were active on that date will be counted. - agentnum: limit to packages with this agent. -- detail: if true, the report will contain an additional column which contains -the keys of all objects aggregated in the row. - - ignore_quantity: if true, package quantities will be ignored (only distinct packages will be counted). +The result will be a hashref containing three parallel arrayrefs: +- "data", the columns required by the FCC. +- "detail", a list of the package numbers included in each row's aggregation +- "error", a hashref containing any error status strings in that row. Keys +are error identifiers, values are the messages to show the user. +as well as an informational item: +- "num_errors", the number of rows that contain errors + +=item report_data SECTION, OPTIONS + +Returns only the data, not the detail or error columns. This is the part that +will be submitted to the FCC. + =cut sub report { my $class = shift; my $section = shift; my %opt = @_; + $opt{detail} = 1; + + # add the error column + my $data = $class->report_data($section, %opt); + my $error = []; + my $detail = []; + my $check_method = $section.'_check'; + my $num_errors = 0; + foreach my $row (@$data) { + if ( $class->can($check_method) ) { # they don't all have these + my $eh = $class->$check_method( $row ); + $num_errors++ if keys(%$eh); + push $error, $eh + } + push @$detail, pop @$row; # this comes from the query + } + + return +{ + data => $data, + error => $error, + detail => $detail, + num_errors => $num_errors, + }; +} + +sub report_data { + my $class = shift; + my $section = shift; + my %opt = @_; my $method = $section.'_sql'; die "Report section '$section' is not implemented\n" @@ -307,7 +348,7 @@ sub report { warn $statement if $DEBUG; my $sth = dbh->prepare($statement); $sth->execute or die $sth->errstr; - $sth->fetchall_arrayref; + return $sth->fetchall_arrayref; } sub fbd_sql { @@ -395,6 +436,34 @@ sub fbs_sql { } +sub fbs_check { + my $class = shift; + my $row = shift; + my %e; + #censustract + if ( length($row->[0]) == 0 ) { + $e{'censustract_null'} = 'The package location has no census tract.'; + } elsif ($row->[0] !~ /^\d{11}$/) { + $e{'censustract_bad'} = 'The census tract must be exactly 11 digits.'; + } + + #technology + if ( length($row->[1]) == 0 ) { + $e{'technology_null'} = 'The package has no technology type.'; + } + + #speeds + if ( length($row->[2]) == 0 or length($row->[3]) == 0 ) { + $e{'speed_null'} = 'The package is missing downstream or upstream speeds.'; + } elsif ( $row->[2] !~ /^\d*(\.\d+)?$/ or $row->[3] !~ /^\d*(\.\d+)?$/ ) { + $e{'speed_bad'} = 'The downstream and upstream speeds must be decimal numbers in Mbps.'; + } elsif ( $row->[2] == 0 or $row->[3] == 0 ) { + $e{'speed_zero'} = 'The downstream and upstream speeds cannot be zero.'; + } + + return \%e; +} + sub fvs_sql { my $class = shift; my %opt = @_; @@ -440,6 +509,19 @@ sub fvs_sql { } +sub fvs_check { + my $class = shift; + my $row = shift; + my %e; + #censustract + if ( length($row->[0]) == 0 ) { + $e{'censustract_null'} = 'The package location has no census tract.'; + } elsif ($row->[0] !~ /^\d{11}$/) { + $e{'censustract_bad'} = 'The census tract must be exactly 11 digits.'; + } + return \%e; +} + sub lts_sql { my $class = shift; my %opt = @_; diff --git a/FS/FS/Report/Table.pm b/FS/FS/Report/Table.pm index 934287a15..69686df9b 100644 --- a/FS/FS/Report/Table.pm +++ b/FS/FS/Report/Table.pm @@ -886,14 +886,24 @@ sub with_classnum { @$classnum = grep /^\d+$/, @$classnum; my $in = 'IN ('. join(',', @$classnum). ')'; - my $expr = " - ( COALESCE(part_pkg.classnum, 0) $in AND pkgpart_override IS NULL) - OR ( COALESCE(part_fee.classnum, 0) $in AND feepart IS NOT NULL )"; if ( $use_override ) { - $expr .= " - OR ( COALESCE(override.classnum, 0) $in AND pkgpart_override IS NOT NULL )"; + # then include packages if their base package is in the set and they are + # not overridden, + # or if they are overridden and their override package is in the set, + # or fees if they are in the set + return "( + ( COALESCE(part_pkg.classnum, 0) $in AND cust_pkg.pkgpart IS NOT NULL AND pkgpart_override IS NULL ) + OR ( COALESCE(override.classnum, 0) $in AND pkgpart_override IS NOT NULL ) + OR ( COALESCE(part_fee.classnum, 0) $in AND cust_bill_pkg.feepart IS NOT NULL ) + )"; + } else { + # include packages if their base package is in the set, + # or fees if they are in the set + return "( + ( COALESCE(part_pkg.classnum, 0) $in AND cust_pkg.pkgpart IS NOT NULL ) + OR ( COALESCE(part_fee.classnum, 0) $in AND cust_bill_pkg.feepart IS NOT NULL ) + )"; } - "( $expr )"; } sub with_usageclass { diff --git a/FS/FS/Report/Tax.pm b/FS/FS/Report/Tax.pm index 713be02df..43337a621 100644 --- a/FS/FS/Report/Tax.pm +++ b/FS/FS/Report/Tax.pm @@ -95,6 +95,10 @@ sub report_internal { my $pkg_tax_exempt = "SELECT SUM(amount) AS exempt_charged, billpkgnum, taxnum ". "FROM cust_tax_exempt_pkg EXEMPT_WHERE GROUP BY billpkgnum, taxnum"; + # This just calculates the sum of credit applications to a line item. + my $pkg_credited = "SELECT SUM(amount) AS credited, billpkgnum ". + "FROM cust_credit_bill_pkg GROUP BY billpkgnum"; + my $where = "WHERE cust_bill._date >= $beginning AND cust_bill._date <= $ending ". "AND COALESCE(cust_main_county.taxname,'Tax') = '$taxname' ". "AND cust_main_county.country = '$country'"; @@ -176,25 +180,40 @@ sub report_internal { $all_sql{exempt_monthly} =~ s/EXEMPT_WHERE/WHERE exempt_monthly = 'Y'/; # taxable sales + # (sale - exemptions - credits, except not negative) $sql{taxable} = "$select - SUM(cust_bill_pkg.setup + cust_bill_pkg.recur - COALESCE(exempt_charged, 0)) - FROM cust_main_county - JOIN ($pkg_tax) AS pkg_tax USING (taxnum) - JOIN cust_bill_pkg USING (billpkgnum) + SUM( + cust_bill_pkg.setup + cust_bill_pkg.recur + - COALESCE(exempt_charged, 0) + - COALESCE(credited, 0) + ) + FROM cust_bill_pkg + LEFT JOIN ($pkg_tax) AS pkg_tax + ON (cust_bill_pkg.billpkgnum = pkg_tax.billpkgnum) LEFT JOIN ($pkg_tax_exempt) AS pkg_tax_exempt - ON (pkg_tax_exempt.billpkgnum = cust_bill_pkg.billpkgnum - AND pkg_tax_exempt.taxnum = cust_main_county.taxnum) + ON (cust_bill_pkg.billpkgnum = pkg_tax_exempt.billpkgnum) + LEFT JOIN ($pkg_credited) AS pkg_credited + ON (cust_bill_pkg.billpkgnum = pkg_credited.billpkgnum) + LEFT JOIN cust_main_county + ON (COALESCE(pkg_tax.taxnum, pkg_tax_exempt.taxnum) = cust_main_county.taxnum) $join_cust_pkg $where AND $nottax $group"; $all_sql{taxable} = "$select_all - SUM(cust_bill_pkg.setup + cust_bill_pkg.recur - COALESCE(exempt_charged, 0)) - FROM cust_main_county - JOIN ($pkg_tax) AS pkg_tax USING (taxnum) - JOIN cust_bill_pkg USING (billpkgnum) + SUM( + cust_bill_pkg.setup + cust_bill_pkg.recur + - COALESCE(exempt_charged, 0) + - COALESCE(credited, 0) + ) + FROM cust_bill_pkg + LEFT JOIN ($pkg_tax) AS pkg_tax + ON (cust_bill_pkg.billpkgnum = pkg_tax.billpkgnum) LEFT JOIN ($pkg_tax_exempt) AS pkg_tax_exempt - ON (pkg_tax_exempt.billpkgnum = cust_bill_pkg.billpkgnum - AND pkg_tax_exempt.taxnum = cust_main_county.taxnum) + ON (cust_bill_pkg.billpkgnum = pkg_tax_exempt.billpkgnum) + LEFT JOIN ($pkg_credited) AS pkg_credited + ON (cust_bill_pkg.billpkgnum = pkg_credited.billpkgnum) + LEFT JOIN cust_main_county + ON (COALESCE(pkg_tax.taxnum, pkg_tax_exempt.taxnum) = cust_main_county.taxnum) $join_cust_pkg $where AND $nottax $group_all"; @@ -204,27 +223,39 @@ sub report_internal { # estimated tax (taxable * rate) $sql{estimated} = "$select SUM(cust_main_county.tax / 100 * - (cust_bill_pkg.setup + cust_bill_pkg.recur - COALESCE(exempt_charged, 0)) + ( cust_bill_pkg.setup + cust_bill_pkg.recur + - COALESCE(exempt_charged, 0) + - COALESCE(credited, 0) + ) ) - FROM cust_main_county - JOIN ($pkg_tax) AS pkg_tax USING (taxnum) - JOIN cust_bill_pkg USING (billpkgnum) + FROM cust_bill_pkg + LEFT JOIN ($pkg_tax) AS pkg_tax + ON (cust_bill_pkg.billpkgnum = pkg_tax.billpkgnum) LEFT JOIN ($pkg_tax_exempt) AS pkg_tax_exempt - ON (pkg_tax_exempt.billpkgnum = cust_bill_pkg.billpkgnum - AND pkg_tax_exempt.taxnum = cust_main_county.taxnum) + ON (cust_bill_pkg.billpkgnum = pkg_tax_exempt.billpkgnum) + LEFT JOIN ($pkg_credited) AS pkg_credited + ON (cust_bill_pkg.billpkgnum = pkg_credited.billpkgnum) + LEFT JOIN cust_main_county + ON (COALESCE(pkg_tax.taxnum, pkg_tax_exempt.taxnum) = cust_main_county.taxnum) $join_cust_pkg $where AND $nottax $group"; $all_sql{estimated} = "$select_all SUM(cust_main_county.tax / 100 * - (cust_bill_pkg.setup + cust_bill_pkg.recur - COALESCE(exempt_charged, 0)) + ( cust_bill_pkg.setup + cust_bill_pkg.recur + - COALESCE(exempt_charged, 0) + - COALESCE(credited, 0) + ) ) - FROM cust_main_county - JOIN ($pkg_tax) AS pkg_tax USING (taxnum) - JOIN cust_bill_pkg USING (billpkgnum) + FROM cust_bill_pkg + LEFT JOIN ($pkg_tax) AS pkg_tax + ON (cust_bill_pkg.billpkgnum = pkg_tax.billpkgnum) LEFT JOIN ($pkg_tax_exempt) AS pkg_tax_exempt - ON (pkg_tax_exempt.billpkgnum = cust_bill_pkg.billpkgnum - AND pkg_tax_exempt.taxnum = cust_main_county.taxnum) + ON (cust_bill_pkg.billpkgnum = pkg_tax_exempt.billpkgnum) + LEFT JOIN ($pkg_credited) AS pkg_credited + ON (cust_bill_pkg.billpkgnum = pkg_credited.billpkgnum) + LEFT JOIN cust_main_county + ON (COALESCE(pkg_tax.taxnum, pkg_tax_exempt.taxnum) = cust_main_county.taxnum) $join_cust_pkg $where AND $nottax $group_all"; diff --git a/FS/FS/Template_Mixin.pm b/FS/FS/Template_Mixin.pm index f67de76fc..ebc977a35 100644 --- a/FS/FS/Template_Mixin.pm +++ b/FS/FS/Template_Mixin.pm @@ -346,8 +346,8 @@ sub print_generic { if ( $format eq 'latex' && grep { /^%%Detail/ } @invoice_template ) { #change this to a die when the old code is removed - # it's been almost ten years, changing it to a die. - die "old-style invoice template $templatefile; ". + # it's been almost ten years, changing it to a die on the next release. + warn "old-style invoice template $templatefile; ". "patch with conf/invoice_latex.diff or use new conf/invoice_latex*\n"; #$old_latex = 'true'; #@invoice_template = _translate_old_latex_format(@invoice_template); @@ -2638,14 +2638,14 @@ sub _items_cust_bill_pkg { # and location labels my @b = (); # accumulator for the line item hashes that we'll return - my ($s, $r, $u, $d) = ( undef, undef, undef ); + my ($s, $r, $u, $d) = ( undef, undef, undef, undef ); # the 'current' line item hashes for setup, recur, usage, discount foreach my $cust_bill_pkg ( @$cust_bill_pkgs ) { # if the current line item is waiting to go out, and the one we're about # to start is not bundled, then push out the current one and start a new # one. - foreach ( $s, $r, ($opt{skip_usage} ? () : $u ) , $d ) { + foreach ( $s, $r, ($opt{skip_usage} ? () : $u ), $d ) { if ( $_ && !$cust_bill_pkg->hidden ) { $_->{amount} = sprintf( "%.2f", $_->{amount} ); $_->{amount} =~ s/^\-0\.00$/0.00/; @@ -3047,6 +3047,15 @@ sub _items_cust_bill_pkg { or ( $type eq 'R' and $cust_bill_pkg->unitrecur > 0 ) ) { + # the line item hashref for the line that will show the original + # price + # (use the recur or single line for the package, unless we're + # showing a setup line for a package with no recurring fee) + my $active_line = $r; + if ( $type eq 'S' ) { + $active_line = $s; + } + my @discounts = $cust_bill_pkg->cust_bill_pkg_discount; # special case: if there are old "discount details" on this line # item, don't show discount line items @@ -3061,23 +3070,18 @@ sub _items_cust_bill_pkg { $cust_bill_pkg->billpkgnum."\n" if $DEBUG; my $discount_amount = sum( map {$_->amount} @discounts ); - my $orig_amount = $cust_bill_pkg->setup + $cust_bill_pkg->recur - + $discount_amount; # if multiple discounts apply to the same package, how to display # them? ext_description lines, apparently + # + # # discount amounts are negative if ( $d and $cust_bill_pkg->hidden ) { - $d->{amount} += $discount_amount; - $d->{orig_amount} += $orig_amount; + $d->{amount} -= $discount_amount; } else { my @ext; - # make a placeholder for the original price, if necessary - # (if unit prices are enabled, it won't be necessary) - push @ext, '' if !$conf->exists('invoice-unitprice'); $d = { _is_discount => 1, - description => $self->mt('Discount included'), - amount => $discount_amount, - orig_amount => $orig_amount, + description => $self->mt('Discount'), + amount => -1 * $discount_amount, ext_description => \@ext, }; foreach my $cust_bill_pkg_discount (@discounts) { @@ -3086,12 +3090,10 @@ sub _items_cust_bill_pkg { } } - # update the placeholder to show the original price in the - # first ext_description line - if ( !$conf->exists('invoice-unitprice') ) { - $d->{ext_description}->[0] = - sprintf('Original price: %.2f', $d->{orig_amount}); - } + # update the active line (before the discount) to show the + # original price (whether this is a hidden line or not) + $active_line->{amount} += $discount_amount; + } # if there are any discounts } # if this is an appropriate place to show discounts @@ -3116,7 +3118,7 @@ sub _items_cust_bill_pkg { } - foreach ( $s, $r, ($opt{skip_usage} ? () : $u, $d ) ) { + foreach ( $s, $r, ($opt{skip_usage} ? () : $u ), $d ) { if ( $_ ) { $_->{amount} = sprintf( "%.2f", $_->{amount} ), if exists($_->{amount}); diff --git a/FS/FS/cust_pkg/Search.pm b/FS/FS/cust_pkg/Search.pm index aacd387a6..89809de6c 100644 --- a/FS/FS/cust_pkg/Search.pm +++ b/FS/FS/cust_pkg/Search.pm @@ -585,9 +585,8 @@ sub search { 'agentnum' => $agentnum, 'detail' => 1 ); - my $row = $report->[$rownum] + my $pkgnums = $report->{detail}->[$rownum] or die "row $rownum is past the end of the report"; - my $pkgnums = $row->[-1] || '0'; # '0' so that if there are no pkgnums (empty string) it will create # a valid query that returns nothing warn "PKGNUMS:\n$pkgnums\n\n"; # XXX debug diff --git a/FS/FS/discount.pm b/FS/FS/discount.pm index 43ad4909e..0561f9ca0 100644 --- a/FS/FS/discount.pm +++ b/FS/FS/discount.pm @@ -174,12 +174,12 @@ sub description_short { my $money_char = $conf->config('money_char') || '$'; my $desc = $self->name ? $self->name.': ' : ''; - $desc .= $money_char. sprintf('%.2f/month ', $self->amount) + $desc .= $money_char. sprintf('%.2f/month', $self->amount) if $self->amount > 0; ( my $percent = $self->percent ) =~ s/\.0+$//; $percent =~ s/(\.\d*[1-9])0+$/$1/; - $desc .= $percent. '% ' + $desc .= $percent. '%' if $self->percent > 0; $desc; diff --git a/FS/FS/part_event/Condition/signupdate_age.pm b/FS/FS/part_event/Condition/signupdate_age.pm index 70b4bbd7b..0c78b4c10 100644 --- a/FS/FS/part_event/Condition/signupdate_age.pm +++ b/FS/FS/part_event/Condition/signupdate_age.pm @@ -18,6 +18,7 @@ sub condition { my $age = $self->option_age_from('age', $opt{'time'} ); + my $cust_main = $cust_bill->cust_main; ( $cust_main->signupdate - 60 ) <= $age; } diff --git a/httemplate/edit/cust_location-censustract.html b/httemplate/edit/cust_location-censustract.html new file mode 100644 index 000000000..bdb9823fa --- /dev/null +++ b/httemplate/edit/cust_location-censustract.html @@ -0,0 +1,66 @@ +<% include('/elements/header-popup.html', "Edit Census Tract") %> + +<% include('/elements/error.html') %> + +<FORM NAME="EditLocationForm" +ACTION="<% $p %>edit/process/cust_location-censustract.html" METHOD=POST> +<INPUT TYPE="hidden" NAME="locationnum" VALUE="<% $locationnum %>"> + +<% ntable('#cccccc') %> +<& /elements/location.html, + 'object' => $cust_location, + 'no_asterisks' => 1, + 'enable_censustract' => 1, + 'disabled' => 'DISABLED', +&> +<& /elements/standardize_locations.html, + 'form' => 'EditLocationForm', + 'callback' => 'document.EditLocationForm.submit();', + 'with_census' => 1, + 'with_census_functions' => 1, +&> +</TABLE> + +<BR> +<SCRIPT TYPE="text/javascript"> +<&| /elements/onload.js &> + document.getElementById('enter_censustract').disabled = false; +</&> +function go() { + confirm_censustract(); +} + +function submit_abort() { + nd(1); +} +</SCRIPT> +<INPUT TYPE="button" NAME="submitButton" VALUE="Submit" onclick="go()"> +</FORM> +</BODY> +</HTML> + +<%init> + +my $conf = new FS::Conf; + +my $curuser = $FS::CurrentUser::CurrentUser; + +# it's the same access right you'd need to do this by editing packages +die "access denied" + unless $curuser->access_right('Change customer package'); + +my $locationnum = scalar($cgi->param('locationnum')); +my $cust_location = qsearchs({ + 'select' => 'cust_location.*', + 'table' => 'cust_location', + 'addl_from' => 'LEFT JOIN cust_main USING ( custnum )', + 'hashref' => { 'locationnum' => $locationnum }, + 'extra_sql' => ' AND '. $curuser->agentnums_sql, + }) or die "unknown locationnum $locationnum"; + +# unlike the regular one, this allows editing disabled locations + +my $cust_main = qsearchs('cust_main', { 'custnum' => $cust_location->custnum }) + or die "can't get cust_main record for custnum ". $cust_location->custnum; + +</%init> diff --git a/httemplate/edit/process/bulk-477_cust_pkg.html b/httemplate/edit/process/bulk-477_cust_pkg.html new file mode 100644 index 000000000..064f73b60 --- /dev/null +++ b/httemplate/edit/process/bulk-477_cust_pkg.html @@ -0,0 +1,20 @@ +<% $cgi->redirect($fsurl.'search/477_cust_pkg.html?redirect='.$session) %> +<%init> +my $curuser = $FS::CurrentUser::CurrentUser; +my $edit_acl = $curuser->access_right('Edit FCC report configuration'); +my $global_edit_acl = $curuser->access_right('Edit FCC report configuration for all agents'); +die "access denied" unless $edit_acl or $global_edit_acl; + +my %error; +foreach my $param ($cgi->param) { + $param =~ /^pkgnum(\d+)pkgpart(\d+)$/ or next; + my $pkgpart = $2; + 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; # XXX report this somehow +} + +my $session = $cgi->param('redirect'); + +</%init> diff --git a/httemplate/edit/process/cust_location-censustract.html b/httemplate/edit/process/cust_location-censustract.html new file mode 100644 index 000000000..bc9cd4f31 --- /dev/null +++ b/httemplate/edit/process/cust_location-censustract.html @@ -0,0 +1,34 @@ +% if ($error) { +% $cgi->param('error', $error); +% $cgi->redirect(popurl(3). 'edit/cust_location-censustract.html?'. $cgi->query_string ); +% } else { + + <% header("Census tract changed") %> + <SCRIPT TYPE="text/javascript"> + window.top.location.reload(); + </SCRIPT> + </BODY> + </HTML> + +% } +<%init> + +my $curuser = $FS::CurrentUser::CurrentUser; + +die "access denied" + unless $curuser->access_right('Change customer package'); + +my $locationnum = $cgi->param('locationnum'); +my $cust_location = qsearchs({ + 'select' => 'cust_location.*', + 'table' => 'cust_location', + 'addl_from' => 'LEFT JOIN cust_main USING ( custnum )', + 'hashref' => { 'locationnum' => $locationnum }, + 'extra_sql' => ' AND '. $curuser->agentnums_sql, +}); +die "unknown locationnum $locationnum" unless $cust_location; + +$cust_location->set('censustract', $cgi->param('censustract')); +my $error = $cust_location->replace; + +</%init> diff --git a/httemplate/graph/cust_bill_pkg_discount.html b/httemplate/graph/cust_bill_pkg_discount.html index 0d66799a9..5074aa35c 100644 --- a/httemplate/graph/cust_bill_pkg_discount.html +++ b/httemplate/graph/cust_bill_pkg_discount.html @@ -74,7 +74,7 @@ foreach my $agent ( $sel_agent || qsearch('agent', { 'disabled' => '' } ) ) { #'average_per_cust_pkg' => $average_per_cust_pkg, ]; - push @links, "$link?agentnum=$row_agentnum"; #;classnum=$row_classnum;"; + push @links, $link . "agentnum=$row_agentnum;"; @_colors = ($col_scheme->colors)[0,4,8,1,5,9,2,6,10,3,7,11]; push @colors, shift @_colors; diff --git a/httemplate/graph/report_cust_bill_pkg_discount.html b/httemplate/graph/report_cust_bill_pkg_discount.html index 6de84f80b..36ad78252 100644 --- a/httemplate/graph/report_cust_bill_pkg_discount.html +++ b/httemplate/graph/report_cust_bill_pkg_discount.html @@ -1,4 +1,4 @@ -<& /elements/header.html', 'Discount Report' &> +<& /elements/header.html, 'Discount Report' &> <FORM ACTION="cust_bill_pkg_discount.html" METHOD="GET"> diff --git a/httemplate/search/477.html b/httemplate/search/477.html index ff2ac8638..2e9f9428e 100644 --- a/httemplate/search/477.html +++ b/httemplate/search/477.html @@ -32,26 +32,54 @@ table.fcc477part thead tr.subhead { font-size: large; float: left; } +.errortitle { + font-weight: bold; + color: #ff0000; +} +tr.error td { + background-color: #ffdddd; +} +tr.error td.error { + text-align: left; + border: none; +} +tr.error ul { + margin: 0px; + list-style-image: url("<% $fsurl %>images/cross.png"); +} a.download { float: right; } </STYLE> % foreach my $partname (@partnames) { +% my $this_part = $parts{$partname}; % $cgi->param('parts', $partname); % $cgi->param('type', 'csv'); <table class="fcc477part"> <caption> <span class="parttitle"><% $part_titles->{$partname} %></span> +% if ( $this_part->{num_errors} > 0 ) { +% # disable downloading while it contains errors + <span class="errortitle"> + <% emt('This section contains [quant,_1,error].', $this_part->{num_errors}) %> + </span> +% } else { <a class="download" href="<% $cgi->self_url %>">Download</a> +% } </caption> % my $header = ".header_$partname"; -% my $data = $parts{$partname}; +% my $data = $this_part->{data}; +% my $error = $this_part->{error}; <thead> <& $header &> </thead> % my $rownum = 0; % foreach my $row (@$data) { - <tr> +% my %eh; # error hash +% if ( $error->[$rownum] ) { +% %eh = %{ $error->[$rownum] }; +% } + <tr<% keys(%eh) ? ' class="error"' : ''%>> % my $first = 1; % foreach my $item (@$row) { <td> @@ -63,6 +91,14 @@ a.download { % } </td> % } #foreach $item +% # display errors +% if ( keys %eh ) { + <td class="error"><ul> +% foreach my $key (sort keys %eh) { + <li><% $eh{$key} %></li> +% } + </ul></td> +% } # if there are errors </tr> % $rownum++; % } #foreach $row @@ -98,10 +134,10 @@ foreach my $partname (@partnames) { date => $date, agentnum => $agentnum, ignore_quantity => $ignore_quantity, - ); + ); # includes error, detail, and data parts my $detail_table = FS::Report::FCC_477->part_table($partname); if ($detail_table eq 'cust_pkg') { - my $link = popurl(1).'cust_pkg.cgi?477part='.$partname.";date=$date;"; + my $link = popurl(1).'477_cust_pkg.html?477part='.$partname.";date=$date;"; if ($agentnum) { $link .= "agentnum=$agentnum;"; } @@ -114,7 +150,7 @@ my $title = 'FCC Form 477 Data - ' . time2str('%b %o, %Y', $date); if ( $cgi->param('type') eq 'csv' ) { my $partname = $partnames[0]; # ignore any beyond the first - my $data = $parts{$partname}; + my $data = $parts{$partname}->{data}; my $csv = Text::CSV_XS->new({ eol => "\r\n" }); # i think my $filename = time2str('%Y-%m-%d', $date) . '-'. $partname . '.csv'; diff --git a/httemplate/search/477_cust_pkg.html b/httemplate/search/477_cust_pkg.html new file mode 100644 index 000000000..b8df9fd0f --- /dev/null +++ b/httemplate/search/477_cust_pkg.html @@ -0,0 +1,228 @@ +<& elements/search.html, + 'html_init' => $html_init, + 'html_form' => $html_form, + 'html_foot' => '</FORM>', + 'title' => emt('Package Search Results'), + 'name' => 'packages', + 'query' => $query, + 'count_query' => $count_query, + 'header' => [ emt('#'), + emt('Quan.'), + emt('Package'), + emt('Class'), + emt('Status'), + emt('Freq.'), + emt('Setup'), + emt('Next bill'), + emt('Susp.'), + emt('Changed'), + emt('Cancel'), + FS::UI::Web::cust_header(), + emt('Census tract'), + emt('Package options'), + ], + 'fields' => [ + 'pkgnum', + 'quantity', + sub { $_[0]->pkg; }, + 'classname', + sub { ucfirst(shift->status); }, + sub { FS::part_pkg::freq_pretty(shift); }, + + ( map { time_or_blank($_) } + qw( setup bill susp change_date cancel ) ), + + \&FS::UI::Web::cust_fields, + + sub { # census tract + my $cust_pkg = shift; + my $cust_location = $cust_pkg->cust_location; + ($cust_location->censustract || '<b>unknown</b>'). + '<font size="-1"> (edit)</font>'; + }, + + # a hidden input in each row with the pkgnum, so that + # we can refresh back to this list of pkgnums + sub { + my $cust_pkg = shift; + my $part_pkg = $cust_pkg->part_pkg; + my %hash = $part_pkg->fcc_options; + '<INPUT NAME="pkgnum" TYPE="hidden" VALUE="' . + $cust_pkg->pkgnum . '">' . + include('/elements/input-fcc_options.html', + id => 'pkgnum'.$cust_pkg->pkgnum. + 'pkgpart'.$part_pkg->pkgpart, + curr_value => encode_json(\%hash), + html_only => 1 + ) + }, + ], + 'color' => [ + '', + '', + '', + '', + sub { shift->statuscolor; }, + '', '', '', '', '', '', + FS::UI::Web::cust_colors(), + '', + '', + ], + 'style' => [ '', '', '', '', 'b', + '', '', '', '', '', '', + FS::UI::Web::cust_styles() ], + 'size' => [ '', '', '', '', '-1' ], + 'align' => 'rrlcccrrrrr'. FS::UI::Web::cust_aligns(). 'cl', + 'links' => [ + $link, + $link, + $link, + '', '', '', '', '', '', '', '', + ( map { $_ ne 'Cust. Status' ? $clink : '' } + FS::UI::Web::cust_header() + ), + '', + '', + ], + 'link_onclicks' => [ + (('') x 11), + (map { '' } FS::UI::Web::cust_header()), + $pkg_edit_location_link, + '', + ], + +&> +<%init> + +my $curuser = $FS::CurrentUser::CurrentUser; +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); + +die "access denied" + unless $acl_edit || $acl_edit_global; + +my $conf = new FS::Conf; + +my $session; + +my ($query, $count_query); + +if ( $cgi->param('redirect') ) { # then restore the pkgnum list + $session = $cgi->param('redirect'); + my $pref = $curuser->option("redirect$session"); # contains a list of pkgnums + die "unknown redirect session $session\n" unless length($pref); + my @pkgnums = grep /^\d+$/, split(',', $pref); + + $query = FS::cust_pkg->search({}); + $count_query = delete($query->{count_query}); + + my $where = "cust_pkg.pkgnum IN (".join(',', @pkgnums).")"; + if ( $count_query =~ /WHERE/i ) { + $where = " AND ($where) "; + } else { + $where = " WHERE ($where) "; + } + $query->{extra_sql} .= $where; + $count_query .= $where; +} else { + # build and run the query right now, and then cache the pkgnums it returned + my %search_hash = (); + + #scalars + for (qw( agentnum 477part 477rownum date )) { + $search_hash{$_} = $cgi->param($_) if length($cgi->param($_)); + } + + $query = FS::cust_pkg->search(\%search_hash); + $count_query = delete($query->{'count_query'}); + + my @cust_pkg = qsearch($query); + + my $pkgnums = join(',', map { $_->pkgnum } @cust_pkg); + $session = int(rand(4294967296)); #XXX + my $pref = new FS::access_user_pref({ + 'usernum' => $FS::CurrentUser::CurrentUser->usernum, + 'prefname' => "redirect$session", + 'prefvalue' => $pkgnums, + 'expiration' => time + 3600, #1h? 1m? + }); + my $pref_error = $pref->insert; + if ($pref_error) { + die "couldn't even set redirect cookie: $pref_error\n"; + } + + # and then bail out and reload using the redirect cookie + $cgi->delete_all(); + $cgi->param("redirect", $session); + $m->clear_buffer; + $m->print( $cgi->redirect($cgi->self_url) ); + $m->abort; +} + +my $show = $curuser->default_customer_view =~ /^(jumbo|packages)$/ + ? '' + : ';show=packages'; + +my $link = sub { + my $self = shift; + my $frag = 'cust_pkg'. $self->pkgnum; #hack for IE ignoring real #fragment + [ "${p}view/cust_main.cgi?custnum=".$self->custnum. + "$show;fragment=$frag#cust_pkg", + 'pkgnum' + ]; +}; + +my $html_init = + include('/elements/init_overlib.html') . + include('/elements/input-fcc_options.html', js_only => 1) . + include('.style') . + include('.script'); + +my $clink = sub { + my $cust_pkg = shift; + $cust_pkg->cust_main_custnum + ? [ "${p}view/cust_main.cgi?", 'custnum' ] + : ''; +}; + +my $html_form = qq! + <FORM ACTION="${p}edit/process/bulk-477_cust_pkg.html" METHOD="POST" NAME="477_cust_pkg"> + <INPUT NAME="redirect" TYPE="hidden" VALUE="$session"> +!; + +my $pkg_edit_location_link = sub { + my $cust_pkg = shift; + my $locationnum = $cust_pkg->locationnum; + include('/elements/popup_link_onclick.html', + 'action' => $p. "edit/cust_location-censustract.html?locationnum=$locationnum", + 'actionlabel' => emt('Edit census tract'), + 'width' => 700, + 'height' => 355, + ); +}; + +sub time_or_blank { + my $column = shift; + return sub { + my $record = shift; + my $value = $record->get($column); #mmm closures + $value ? time2str('%b %d %Y', $value ) : ''; + }; +} + +</%init> +<%def .style> +<style> + button.edit_fcc_button { float: right; } +</style> +</%def> +<%def .script> +<script type="text/javascript"> + function finish_edit_fcc(id) { + cClick(); + document.forms['477_cust_pkg'].submit(); //immediately save/refresh + } +</script> +</%def> diff --git a/httemplate/search/cust_bill_pkg.cgi b/httemplate/search/cust_bill_pkg.cgi index 67f618cdb..2d64466e1 100644 --- a/httemplate/search/cust_bill_pkg.cgi +++ b/httemplate/search/cust_bill_pkg.cgi @@ -338,7 +338,7 @@ if ( $cgi->param('nottax') ) { # 0: empty class # N: classnum if ( grep { $_ eq 'classnum' } $cgi->param ) { - my @classnums = grep /^\d*$/, $cgi->param('classnum'); + my @classnums = grep /^\d+$/, $cgi->param('classnum'); push @where, "COALESCE(part_fee.classnum, $part_pkg.classnum, 0) IN ( ". join(',', @classnums ). ' )' @@ -422,7 +422,6 @@ if ( $cgi->param('nottax') ) { # If we're showing 'out' (items that aren't region/class taxable), # then we need the set of all items minus the union of those. - if ( $cgi->param('out') ) { # separate from the rest, in that we're not going to join cust_main_county # in the outer query @@ -448,31 +447,39 @@ if ( $cgi->param('nottax') ) { # everything that returns things joined to a tax definition my $exempt_sub; - if ( @exempt_where or @tax_where or $cgi->param('taxable') ) { - # process exemption restrictions, including @tax_where - my $exempt_sub = 'SELECT SUM(amount) as exempt_amount, billpkgnum - FROM cust_tax_exempt_pkg JOIN cust_main_county USING (taxnum)'; + # process exemption restrictions, including @tax_where + my $exempt_sub = 'SELECT SUM(amount) as exempt_amount, billpkgnum + FROM cust_tax_exempt_pkg JOIN cust_main_county USING (taxnum)'; - $exempt_sub .= ' WHERE '.join(' AND ', @tax_where, @exempt_where) - if (@tax_where or @exempt_where); + $exempt_sub .= ' WHERE '.join(' AND ', @tax_where, @exempt_where) + if (@tax_where or @exempt_where); - $exempt_sub .= ' GROUP BY billpkgnum'; + $exempt_sub .= ' GROUP BY billpkgnum'; - $join_pkg .= " LEFT JOIN ($exempt_sub) AS item_exempt - USING (billpkgnum)"; + $join_pkg .= " LEFT JOIN ($exempt_sub) AS item_exempt + ON (cust_bill_pkg.billpkgnum = item_exempt.billpkgnum)"; + + my $credit_sub = 'SELECT SUM(amount) AS credit_amount, billpkgnum + FROM cust_credit_bill_pkg GROUP BY billpkgnum'; + + $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit + ON (cust_bill_pkg.billpkgnum = item_credit.billpkgnum)"; + if ( @tax_where or $cgi->param('taxable') ) { # process tax restrictions unshift @tax_where, - 'cust_bill_pkg_tax_location.taxable_billpkgnum = cust_bill_pkg.billpkgnum', 'cust_main_county.tax > 0'; - } - my $tax_sub = "SELECT 1 + my $tax_sub = "SELECT taxable_billpkgnum AS billpkgnum FROM cust_bill_pkg_tax_location - JOIN cust_bill_pkg AS tax_item USING (billpkgnum) JOIN cust_main_county USING (taxnum) - WHERE ". join(' AND ', @tax_where); + WHERE ". join(' AND ', @tax_where). + " GROUP BY taxable_billpkgnum"; + + $join_pkg .= " LEFT JOIN ($tax_sub) AS item_tax + ON (cust_bill_pkg.billpkgnum = item_tax.billpkgnum)" + } # now do something with that if ( @exempt_where ) { @@ -484,23 +491,28 @@ if ( $cgi->param('nottax') ) { push @total, 'SUM(exempt_amount)'; push @total_desc, "$money_char%.2f tax-exempt"; - } elsif ( $cgi->param('taxable') ) { + } elsif ( @tax_where or $cgi->param('taxable') ) { my $taxable = 'cust_bill_pkg.setup + cust_bill_pkg.recur '. - '- COALESCE(item_exempt.exempt_amount, 0)'; + '- COALESCE(item_exempt.exempt_amount, 0) '. + '- COALESCE(item_credit.credit_amount, 0)'; + push @where, "(item_tax.billpkgnum IS NOT NULL OR item_exempt.billpkgnum IS NOT NULL)"; push @select, "($taxable) AS taxable_amount"; - push @where, "EXISTS($tax_sub)"; push @peritem, 'taxable_amount'; push @peritem_desc, 'Taxable'; - push @total, "SUM($taxable)"; - push @total_desc, "$money_char%.2f taxable"; - - } elsif ( @tax_where ) { - # union of taxable + all exempt_ cases - push @where, "(EXISTS($tax_sub) OR item_exempt.billpkgnum IS NOT NULL)"; + if ( $cgi->param('taxable') ) { + push @where, "($taxable) > 0"; + } else { + push @total, 'SUM('. + 'cust_bill_pkg.setup + cust_bill_pkg.recur '. + '- COALESCE(item_credit.credit_amount, 0) )'; + push @total_desc, "$money_char%.2f net sales"; + } + push @total, "SUM($taxable)"; + push @total_desc, "$money_char%.2f taxable"; } } # handle all joins to cust_main_county diff --git a/httemplate/search/report_tax.cgi b/httemplate/search/report_tax.cgi index 3e9d7653a..83f2fc5d3 100644 --- a/httemplate/search/report_tax.cgi +++ b/httemplate/search/report_tax.cgi @@ -73,8 +73,9 @@ TD.rowhead { font-weight: bold; text-align: left; padding: 0px 3px } % # construct base links that limit to the tax rates described by this row % my $rowlink = ';taxnum=' . $row->{taxnums}; % # and also the package class, if we're limiting package class -% $rowlink .= ';pkgclass='.$row->{pkgclass} -% if $params{breakdown}->{pkgclass}; +% if ( $params{breakdown}->{pkgclass} ) { +% $rowlink .= ';classnum=' . ($row->{pkgclass} || 0); +% } % % if ( $row->{total} ) { </TBODY><TBODY CLASS="total"> @@ -210,7 +211,9 @@ my $money_sprintf = sub { }; my $dateagentlink = "begin=$beginning;end=$ending"; -$dateagentlink .= $params{agentnum} if $params{agentnum}; +if ( $params{agentnum} ) { + $dateagentlink .= ';agentnum=' . $params{agentnum}; +} my $saleslink = $p. "search/cust_bill_pkg.cgi?$dateagentlink;nottax=1"; my $taxlink = $p. "search/cust_bill_pkg.cgi?$dateagentlink;istax=1"; my $exemptlink = $p. "search/cust_tax_exempt_pkg.cgi?$dateagentlink"; |