diff options
| -rw-r--r-- | httemplate/search/cust_bill_pkg.cgi | 122 | ||||
| -rwxr-xr-x | httemplate/search/report_tax.cgi | 63 |
2 files changed, 138 insertions, 47 deletions
diff --git a/httemplate/search/cust_bill_pkg.cgi b/httemplate/search/cust_bill_pkg.cgi index 57a1951d6..89901ac40 100644 --- a/httemplate/search/cust_bill_pkg.cgi +++ b/httemplate/search/cust_bill_pkg.cgi @@ -91,24 +91,24 @@ if ( $cgi->param('classnum') =~ /^(\d+)$/ ) { } } -sub _where { - my $table = shift; - my $prefix = @_ ? shift : ''; - " - ( cust_main_county.county = $table.${prefix}.county - OR ( cust_main_county.county IS NULL AND $table.${prefix}.county = '' ) - OR ( cust_main_county.county = '' AND $table.${prefix}.county IS NULL) - OR ( cust_main_county.county IS NULL AND $table.${prefix}.county IS NULL) - ) - AND ( cust_main_county.state = $table.${prefix}.state - OR ( cust_main_county.state IS NULL AND $table.${prefix}.state = '' ) - OR ( cust_main_county.state = '' AND $table.${prefix}.state IS NULL ) - OR ( cust_main_county.state IS NULL AND $table.${prefix}.state IS NULL ) - ) - AND cust_main_county.country = $table.${prefix}.country - "; - -} +#sub _where { +# my $table = shift; +# my $prefix = @_ ? shift : ''; +# " +# ( cust_main_county.county = $table.${prefix}.county +# OR ( cust_main_county.county IS NULL AND $table.${prefix}.county = '' ) +# OR ( cust_main_county.county = '' AND $table.${prefix}.county IS NULL) +# OR ( cust_main_county.county IS NULL AND $table.${prefix}.county IS NULL) +# ) +# AND ( cust_main_county.state = $table.${prefix}.state +# OR ( cust_main_county.state IS NULL AND $table.${prefix}.state = '' ) +# OR ( cust_main_county.state = '' AND $table.${prefix}.state IS NULL ) +# OR ( cust_main_county.state IS NULL AND $table.${prefix}.state IS NULL ) +# ) +# AND cust_main_county.country = $table.${prefix}.country +# "; +# +#} if ( $cgi->param('out') ) { @@ -128,6 +128,21 @@ if ( $cgi->param('out') ) { ) "; + #not linked to by anything, but useful for debugging "out of taxable region" + if ( grep $cgi->param($_), qw( county state country ) ) { + + my %ph = map { $_ => dbh->quote( $cgi->param($_) ) } + qw( county state country ); + + my ( $loc_sql, @param ) = FS::cust_pkg->location_sql; + while ( $loc_sql =~ /\?/ ) { #easier to do our own substitution + $loc_sql =~ s/\?/$ph{shift(@param)}/e; + } + + push @where, $loc_sql; + + } + } elsif ( $cgi->param('country' ) ) { my %ph = map { $_ => dbh->quote( $cgi->param($_) ) } @@ -154,7 +169,11 @@ if ( $cgi->param('out') ) { } push @where, ' taxclass = '. dbh->quote( $cgi->param('taxclass') ) - if $cgi->param('taxclass'); + if $cgi->param('taxclass') + && ! $cgi->param('istax'); #no part_pkg.taxclass in this case + #(should we save a taxclass or a link to taxnum + # in cust_bill_pkg or something like + # cust_bill_pkg_tax_location?) if ( $cgi->param('taxclassNULL') ) { @@ -188,25 +207,64 @@ my $count_query; if ( $cgi->param('pkg_tax') ) { $count_query = - "SELECT COUNT(*), SUM( - ( CASE WHEN part_pkg.setuptax = 'Y' - THEN cust_bill_pkg.setup - ELSE 0 - END - ) - + - ( CASE WHEN part_pkg.recurtax = 'Y' - THEN cust_bill_pkg.recur - ELSE 0 - END - ) - ) + "SELECT COUNT(*), + SUM( + ( CASE WHEN part_pkg.setuptax = 'Y' + THEN cust_bill_pkg.setup + ELSE 0 + END + ) + + + ( CASE WHEN part_pkg.recurtax = 'Y' + THEN cust_bill_pkg.recur + ELSE 0 + END + ) + ) "; push @where, "( ( part_pkg.setuptax = 'Y' AND cust_bill_pkg.setup > 0 ) OR ( part_pkg.recurtax = 'Y' AND cust_bill_pkg.recur > 0 ) )", "( tax != 'Y' OR tax IS NULL )"; +} elsif ( $cgi->param('taxable') ) { + + my $setup_taxable = "( + CASE WHEN part_pkg.setuptax = 'Y' + THEN 0 + ELSE cust_bill_pkg.setup + END + )"; + + my $recur_taxable = "( + CASE WHEN part_pkg.recurtax = 'Y' + THEN 0 + ELSE cust_bill_pkg.recur + END + )"; + + my $exempt = "( + SELECT COALESCE( SUM(amount), 0 ) FROM cust_tax_exempt_pkg + WHERE cust_tax_exempt_pkg.billpkgnum = cust_bill_pkg.billpkgnum + )"; + + $count_query = + "SELECT COUNT(*), SUM( $setup_taxable + $recur_taxable - $exempt )"; + + push @where, + #not tax-exempt package (setup or recur) + "( + ( ( part_pkg.setuptax != 'Y' OR part_pkg.setuptax IS NULL ) + AND cust_bill_pkg.setup > 0 ) + OR + ( ( part_pkg.recurtax != 'Y' OR part_pkg.recurtax IS NULL ) + AND cust_bill_pkg.recur > 0 ) + )", + #not a tax_exempt customer + "( tax != 'Y' OR tax IS NULL )"; + #not covered in full by a monthly tax exemption (texas tax) + "0 < ( $setup_taxable + $recur_taxable - $exempt )", + } else { $count_query = diff --git a/httemplate/search/report_tax.cgi b/httemplate/search/report_tax.cgi index 7eb07cf3c..100d4f10f 100755 --- a/httemplate/search/report_tax.cgi +++ b/httemplate/search/report_tax.cgi @@ -58,7 +58,22 @@ % $bgcolor = $bgcolor1; % } % +% #my $diff = 0; +% my $hicolor = $bgcolor; +% unless ( $cgi->param('show_taxclasses') ) { +% my $diff = abs( sprintf( '%.2f', $region->{'owed'} ) +% - sprintf( '%.2f', $region->{'tax'} ) +% ); +% if ( $diff > 0.02 ) { +% # $hicolor = $hicolor eq '#eeeeee' ? '#eeee66' : '#ffff99'; +% #} elsif ( $diff ) { +% $hicolor = $hicolor eq '#eeeeee' ? '#eeee99' : '#ffffcc'; +% } +% } +% +% % my $td = qq(TD CLASS="grid" BGCOLOR="$bgcolor"); +% my $tdh = qq(TD CLASS="grid" BGCOLOR="$hicolor"); % my $bigmath = '<FONT FACE="sans-serif" SIZE="+1"><B>'; % my $bme = '</B></FONT>'; @@ -85,17 +100,18 @@ </TD> <<%$td%>><FONT SIZE="+1"><B> = </B></FONT></TD> <<%$td%> ALIGN="right"> - <% &$money_sprintf( $region->{'taxable'} ) %></A> + <A HREF="<% $baselink. $link %>;nottax=1;taxable=1" + ><% &$money_sprintf( $region->{'taxable'} ) %></A> </TD> <<%$td%>><% $region->{'label'} eq 'Total' ? '' : "$bigmath X $bme" %></TD> <<%$td%> ALIGN="right"><% $region->{'rate'} %></TD> <<%$td%>><% $region->{'label'} eq 'Total' ? '' : "$bigmath = $bme" %></TD> - <<%$td%> ALIGN="right"> + <<%$tdh%> ALIGN="right"> <% &$money_sprintf( $region->{'owed'} ) %> </TD> % unless ( $cgi->param('show_taxclasses') ) { - <<%$td%> ALIGN="right"> + <<%$tdh%> ALIGN="right"> <A HREF="<% $baselink. $link %>;istax=1" ><% &$money_sprintf( $region->{'tax'} ) %></A> </TD> @@ -285,8 +301,8 @@ foreach my $r ( qsearch({ 'table' => 'cust_main_county', $mywhere .= " AND taxclass = ? "; push @param, 'taxclass'; - $regions{$label}->{'url_param'} .= ';taxclass='. uri_escape($r->taxclass) - if $cgi->param('show_taxclasses'); + $regions{$label}->{'url_param'} .= ';taxclass='. uri_escape($r->taxclass); + #no, always# if $cgi->param('show_taxclasses'); } else { @@ -298,7 +314,7 @@ foreach my $r ( qsearch({ 'table' => 'cust_main_county', } - my $fromwhere = "$from_join_cust_pkg $mywhere AND payby != 'COMP' "; + my $fromwhere = "$from_join_cust_pkg $mywhere"; # AND payby != 'COMP' "; # my $label = getlabel($r); # $regions{$label}->{'label'} = $label; @@ -307,12 +323,18 @@ foreach my $r ( qsearch({ 'table' => 'cust_main_county', ## calculate total for this region - my $t = scalar_sql($r, \@param, - "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur) $fromwhere AND $nottax" - ); + my $t_sql = + "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur) $fromwhere AND $nottax"; + my $t = scalar_sql($r, \@param, $t_sql); $total += $t; $regions{$label}->{'total'} += $t; + if ( $label eq $out ) {# && $t ) { + warn "adding $t for ". + join('/', map $r->$_, qw( taxclass county state country ) ). "\n"; + #warn $t_sql if $r->state eq 'FL'; + } + ## calculate customer-exemption for this region ## my $taxable = $t; @@ -432,7 +454,7 @@ if ( $conf->exists('tax-pkg_address') ) { $taxwhere =~ s/cust_pkg\.locationnum/cust_bill_pkg_tax_location.locationnum/g; } -$taxfromwhere .= " $taxwhere AND payby != 'COMP' "; +$taxfromwhere .= " $taxwhere "; #AND payby != 'COMP' "; my @taxparam = @base_param; #should i be a cust_main_county method or something @@ -542,10 +564,10 @@ sub getlabel { #kludge to avoid "will not stay shared" warning my $out = 'Out of taxable region(s)'; $label = $out; - } elsif ( $r->taxname ) { - $label = $r->taxname; -# $regions{$label}->{'taxname'} = $label; -# push @{$regions{$label}->{$_}}, $r->$_() foreach qw( county state country ); +# } elsif ( $r->taxname && count_taxname($r->taxname) == 1 ) { +# $label = $r->taxname; +## $regions{$label}->{'taxname'} = $label; +## push @{$regions{$label}->{$_}}, $r->$_() foreach qw( county state country ); } else { $label = $r->country; $label = $r->state.", $label" if $r->state; @@ -554,11 +576,22 @@ sub getlabel { if $r->taxclass && $cgi->param('show_taxclasses') && ! $opt{'no_taxclass'}; - #$label = $r->taxname. " ($label)" if $r->taxname; + $label = $r->taxname. " ($label)" if $r->taxname; } return $label; } +#my %count_taxname = (); #cache +#sub count_taxname { +# my $taxname = shift; +# return $count_taxname{$taxname} if exists $count_taxname{$taxname}; +# my $sql = 'SELECT COUNT(*) FROM cust_main_county WHERE taxname = ?'; +# my $sth = dbh->prepare($sql) or die dbh->errstr; +# $sth->execute( $taxname ) +# or die "Unexpected error executing statement $sql: ". $sth->errstr; +# $count_taxname{$taxname} = $sth->fetchrow_arrayref->[0]; +#} + #false laziness w/FS::Report::Table::Monthly (sub should probably be moved up #to FS::Report or FS::Record or who the fuck knows where) sub scalar_sql { |