diff options
Diffstat (limited to 'FS')
| -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 |
7 files changed, 186 insertions, 61 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; } |