diff options
| -rw-r--r-- | FS/FS/tax_rate.pm | 23 | ||||
| -rwxr-xr-x | httemplate/search/report_newtax.cgi | 36 |
2 files changed, 33 insertions, 26 deletions
diff --git a/FS/FS/tax_rate.pm b/FS/FS/tax_rate.pm index 12cdc4ff3..c0e8c890e 100644 --- a/FS/FS/tax_rate.pm +++ b/FS/FS/tax_rate.pm @@ -1892,10 +1892,15 @@ sub generate_liability_report { $taxes{$label}->{'url_param'} = join(';', map { "$_=". uri_escape($t->$_) } @params); - my $taxwhere = "FROM cust_bill_pkg $addl_from $where AND payby != 'COMP' ". - "AND ". FS::tax_rate_location->location_sql( map { $_ => $t->$_ } - @taxparams - ); + my $payby_itemdesc_loc = + " payby != 'COMP' ". + "AND itemdesc = ? OR ? = '' AND itemdesc IS NULL ". + "AND ". FS::tax_rate_location->location_sql( map { $_ => $t->$_ } + @taxparams + ); + + my $taxwhere = + "FROM cust_bill_pkg $addl_from $where AND $payby_itemdesc_loc"; my $sql = "SELECT SUM(amount) $taxwhere AND cust_bill_pkg.pkgnum = 0"; @@ -1903,12 +1908,10 @@ sub generate_liability_report { $tax += $x; $taxes{$label}->{'tax'} += $x; - my $creditfrom = " JOIN cust_credit_bill_pkg USING (billpkgnum,billpkgtaxratelocationnum) "; - my $creditwhere = "FROM cust_bill_pkg $addl_from $creditfrom $where ". - "AND payby != 'COMP' ". - "AND ". FS::tax_rate_location->location_sql( map { $_ => $t->$_ } - @taxparams - ); + my $creditfrom = + "JOIN cust_credit_bill_pkg USING (billpkgnum,billpkgtaxratelocationnum)"; + my $creditwhere = + "FROM cust_bill_pkg $addl_from $creditfrom $where $payby_itemdesc_loc"; $sql = "SELECT SUM(cust_credit_bill_pkg.amount) ". " $creditwhere AND cust_bill_pkg.pkgnum = 0"; diff --git a/httemplate/search/report_newtax.cgi b/httemplate/search/report_newtax.cgi index fee88c995..b5371a642 100755 --- a/httemplate/search/report_newtax.cgi +++ b/httemplate/search/report_newtax.cgi @@ -16,7 +16,7 @@ <TR> <TH CLASS="grid" BGCOLOR="#cccccc"></TH> <TH CLASS="grid" BGCOLOR="#cccccc"></TH> - <TH CLASS="grid" BGCOLOR="#cccccc">Tax collected</TH> + <TH CLASS="grid" BGCOLOR="#cccccc">Tax invoiced</TH> <TH CLASS="grid" BGCOLOR="#cccccc"> </TH> <TH CLASS="grid" BGCOLOR="#cccccc"></TH> <TH CLASS="grid" BGCOLOR="#cccccc">Tax credited</TH> @@ -91,12 +91,9 @@ if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { $where .= ' AND cust_main.agentnum = '. $agent->agentnum; } -# my ( $location_sql, @location_param ) = FS::cust_pkg->location_sql; -# $where .= " AND $location_sql"; -#my @taxparam = ( 'itemdesc', @location_param ); -# now something along the lines of geocode matching ? -#$where .= FS::cust_pkg->_location_sql_where('cust_tax_location');; -my @taxparam = ( 'itemdesc', 'tax_rate_location.state', 'tax_rate_location.county', 'tax_rate_location.city', 'cust_bill_pkg_tax_rate_location.locationtaxid' ); +#my @taxparam = ( 'itemdesc', 'tax_rate_location.state', 'tax_rate_location.county', 'tax_rate_location.city', 'cust_bill_pkg_tax_rate_location.locationtaxid' ); +my @taxparams = qw( city county state locationtaxid ); +my @params = ('itemdesc', @taxparams); my $select = 'DISTINCT itemdesc,locationtaxid,tax_rate_location.state,tax_rate_location.county,tax_rate_location.city'; @@ -112,7 +109,7 @@ foreach my $t (qsearch({ table => 'cust_bill_pkg', }) ) { - my @params = map { my $f = $_; $f =~ s/.*\.//; $f } @taxparam; + #my @params = map { my $f = $_; $f =~ s/.*\.//; $f } @taxparam; my $label = join('~', map { $t->$_ } @params); $label = 'Tax'. $label if $label =~ /^~/; unless ( exists( $taxes{$label} ) ) { @@ -122,24 +119,31 @@ foreach my $t (qsearch({ table => 'cust_bill_pkg', $taxes{$label}->{'url_param'} = join(';', map { "$_=". uri_escape($t->$_) } @params); - my $taxwhere = "FROM cust_bill_pkg $addl_from $where AND payby != 'COMP' ". - "AND ". join( ' AND ', map { "( $_ = ? OR ? = '' AND $_ IS NULL)" } @taxparam ); + my $payby_itemdesc_loc = + " payby != 'COMP' ". + "AND itemdesc = ? OR ? = '' AND itemdesc IS NULL ". + "AND ". FS::tax_rate_location->location_sql( map { $_ => $t->$_ } + @taxparams + ); + + my $taxwhere = + "FROM cust_bill_pkg $addl_from $where AND $payby_itemdesc_loc"; my $sql = "SELECT SUM(amount) $taxwhere AND cust_bill_pkg.pkgnum = 0"; - my $x = scalar_sql($t, [ map { $_, $_ } @params ], $sql ); + my $x = scalar_sql($t, [ $itemdesc, $itemdesc ], $sql ); $tax += $x; $taxes{$label}->{'tax'} += $x; - my $creditfrom = " JOIN cust_credit_bill_pkg USING (billpkgnum,billpkgtaxratelocationnum) "; - my $creditwhere = "FROM cust_bill_pkg $addl_from $creditfrom $where ". - "AND payby != 'COMP' ". - "AND ". join( ' AND ', map { "( $_ = ? OR ? = '' AND $_ IS NULL)" } @taxparam ); + my $creditfrom = + "JOIN cust_credit_bill_pkg USING (billpkgnum,billpkgtaxratelocationnum)"; + my $creditwhere = + "FROM cust_bill_pkg $addl_from $creditfrom $where $payby_itemdesc_loc"; $sql = "SELECT SUM(cust_credit_bill_pkg.amount) ". " $creditwhere AND cust_bill_pkg.pkgnum = 0"; - my $y = scalar_sql($t, [ map { $_, $_ } @params ], $sql ); + my $y = scalar_sql($t, [], $sql ); $credit += $y; $taxes{$label}->{'credit'} += $y; |