From 01698260f2624212ab71be26bb4c644c0aeea2e4 Mon Sep 17 00:00:00 2001 From: Mark Wells Date: Sat, 19 Sep 2015 10:56:59 -0700 Subject: [PATCH] add credited sales column to tax report, #37088 --- FS/FS/Report/Tax.pm | 110 +++++++++++++++++++++++----- httemplate/search/cust_credit_bill_pkg.html | 24 ++++-- httemplate/search/report_tax.cgi | 31 ++++++-- 3 files changed, 136 insertions(+), 29 deletions(-) diff --git a/FS/FS/Report/Tax.pm b/FS/FS/Report/Tax.pm index 0923d55cf..f114c1c6b 100644 --- a/FS/FS/Report/Tax.pm +++ b/FS/FS/Report/Tax.pm @@ -182,26 +182,92 @@ sub report_internal { $all_sql{exempt_monthly} = $all_exempt; $all_sql{exempt_monthly} =~ s/EXEMPT_WHERE/exempt_monthly = 'Y'/; + # credits applied to taxable sales + # Note that negative exemptions (from exempt sales being credited) are NOT + # counted when calculating the exempt amount. (See above.) Therefore we need + # to NOT include any credits against exempt sales in this amount, either. + # These two subqueries implement that. They have joins to cust_credit_bill + # and cust_bill so that credits can be filtered by application date if + # requested. + + # Each row here is the sum of credits applied to a line item. + my $sales_credit = + "SELECT billpkgnum, SUM(cust_credit_bill_pkg.amount) AS credited + FROM cust_credit_bill_pkg + JOIN cust_credit_bill USING (creditbillnum) + JOIN cust_bill USING (invnum) + WHERE cust_bill._date >= $beginning AND cust_bill._date <= $ending + GROUP BY billpkgnum + "; + + # Each row here is the sum of negative exemptions applied to a combination + # of line item and tax definition. + my $exempt_credit = + "SELECT cust_credit_bill_pkg.billpkgnum, taxnum, + 0 - SUM(cust_tax_exempt_pkg.amount) AS exempt_credited + FROM cust_credit_bill_pkg + LEFT JOIN cust_tax_exempt_pkg USING (creditbillpkgnum) + JOIN cust_credit_bill USING (creditbillnum) + JOIN cust_bill USING (invnum) + WHERE cust_bill._date >= $beginning AND cust_bill._date <= $ending + GROUP BY cust_credit_bill_pkg.billpkgnum, taxnum + "; + + if ( $opt{credit_date} eq 'cust_credit_bill' ) { + $sales_credit =~ s/cust_bill._date/cust_credit_bill._date/g; + $exempt_credit =~ s/cust_bill._date/cust_credit_bill._date/g; + } + + $sql{sales_credited} = "$select + SUM(COALESCE(credited, 0) - COALESCE(exempt_credited, 0)) + FROM cust_main_county + JOIN ($pkg_tax) AS pkg_tax USING (taxnum) + JOIN cust_bill_pkg USING (billpkgnum) + LEFT JOIN ($sales_credit) AS sales_credit USING (billpkgnum) + LEFT JOIN ($exempt_credit) AS exempt_credit USING (billpkgnum, taxnum) + $join_cust_pkg $where AND $nottax + $group + "; + + $all_sql{sales_credited} = "$select_all + SUM(COALESCE(credited, 0) - COALESCE(exempt_credited, 0)) + FROM cust_main_county + JOIN ($pkg_tax) AS pkg_tax USING (taxnum) + JOIN cust_bill_pkg USING (billpkgnum) + LEFT JOIN ($sales_credit) AS sales_credit USING (billpkgnum) + LEFT JOIN ($exempt_credit) AS exempt_credit USING (billpkgnum, taxnum) + $join_cust_pkg $where AND $nottax + $group + "; + # taxable sales $sql{taxable} = "$select - SUM(cust_bill_pkg.setup + cust_bill_pkg.recur - COALESCE(exempt_charged, 0)) + SUM(cust_bill_pkg.setup + cust_bill_pkg.recur + - COALESCE(exempt_charged, 0) + - COALESCE(credited, 0) + + COALESCE(exempt_credited, 0) + ) FROM cust_main_county JOIN ($pkg_tax) AS pkg_tax USING (taxnum) JOIN cust_bill_pkg USING (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) + LEFT JOIN ($pkg_tax_exempt) AS pkg_tax_exempt USING (billpkgnum, taxnum) + LEFT JOIN ($sales_credit) AS sales_credit USING (billpkgnum) + LEFT JOIN ($exempt_credit) AS exempt_credit USING (billpkgnum, 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)) + SUM(cust_bill_pkg.setup + cust_bill_pkg.recur + - COALESCE(exempt_charged, 0) + - COALESCE(credited, 0) + + COALESCE(exempt_credited, 0) + ) FROM cust_main_county JOIN ($pkg_tax) AS pkg_tax USING (taxnum) JOIN cust_bill_pkg USING (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) + LEFT JOIN ($pkg_tax_exempt) AS pkg_tax_exempt USING (billpkgnum, taxnum) + LEFT JOIN ($sales_credit) AS sales_credit USING (billpkgnum) + LEFT JOIN ($exempt_credit) AS exempt_credit USING (billpkgnum, taxnum) $join_cust_pkg $where AND $nottax $group_all"; @@ -211,27 +277,35 @@ 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) + + COALESCE(exempt_credited, 0) + ) ) FROM cust_main_county JOIN ($pkg_tax) AS pkg_tax USING (taxnum) JOIN cust_bill_pkg USING (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) + LEFT JOIN ($pkg_tax_exempt) AS pkg_tax_exempt USING (billpkgnum, taxnum) + LEFT JOIN ($sales_credit) AS sales_credit USING (billpkgnum) + LEFT JOIN ($exempt_credit) AS exempt_credit USING (billpkgnum, 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) + + COALESCE(exempt_credited, 0) + ) ) FROM cust_main_county JOIN ($pkg_tax) AS pkg_tax USING (taxnum) JOIN cust_bill_pkg USING (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) + LEFT JOIN ($pkg_tax_exempt) AS pkg_tax_exempt USING (billpkgnum, taxnum) + LEFT JOIN ($sales_credit) AS sales_credit USING (billpkgnum) + LEFT JOIN ($exempt_credit) AS exempt_credit USING (billpkgnum, taxnum) $join_cust_pkg $where AND $nottax $group_all"; @@ -290,12 +364,12 @@ sub report_internal { $creditwhere =~ s/cust_bill._date/cust_credit_bill._date/g; } - $sql{credit} = "$select SUM(cust_credit_bill_pkg.amount) + $sql{tax_credited} = "$select SUM(cust_credit_bill_pkg.amount) $creditfrom $creditwhere AND $istax $group"; - $all_sql{credit} = "$select_all SUM(cust_credit_bill_pkg.amount) + $all_sql{tax_credited} = "$select_all SUM(cust_credit_bill_pkg.amount) $creditfrom $creditwhere AND $istax $group_all"; diff --git a/httemplate/search/cust_credit_bill_pkg.html b/httemplate/search/cust_credit_bill_pkg.html index 63d70c27e..b5a0ee9f6 100644 --- a/httemplate/search/cust_credit_bill_pkg.html +++ b/httemplate/search/cust_credit_bill_pkg.html @@ -203,6 +203,7 @@ if ( $cgi->param('taxclass') my @loc_param = qw( district city county state country ); if ( $cgi->param('out') ) { + # probably don't need this part my ( $loc_sql, @param ) = FS::cust_pkg->location_sql( 'ornull' => 1 ); while ( $loc_sql =~ /\?/ ) { #easier to do our own substitution @@ -242,6 +243,8 @@ if ( $cgi->param('out') ) { #hacky, could be more efficient. care if it is ever used for more than the # tax-report_groups filtering kludge + # (does that even still exist? if so, correct this (or location_sql itself) + # to work with modern cust_location links) my $locs_sql = ' ( '. join(' OR ', map { @@ -266,15 +269,24 @@ if ( $cgi->param('out') ) { } else { - my %ph = map { $_ => dbh->quote( scalar($cgi->param($_)) ) } @loc_param; - - 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; + my @loc_where; + foreach (@loc_param) { + if ( length($cgi->param($_)) ) { + my $quoted = dbh->quote($cgi->param($_)); + push @loc_where, "(COALESCE(cust_location.$_, '') = $quoted)"; + } } + my $loc_sql = join(' AND ', @loc_where); - push @where, $loc_sql; + #my %ph = map { $_ => dbh->quote( scalar($cgi->param($_)) ) } @loc_param; + # + #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; +warn $loc_sql; } my($title, $name); diff --git a/httemplate/search/report_tax.cgi b/httemplate/search/report_tax.cgi index 1d906473e..6d0e95d2a 100644 --- a/httemplate/search/report_tax.cgi +++ b/httemplate/search/report_tax.cgi @@ -22,7 +22,7 @@ TD.rowhead { font-weight: bold; text-align: left; padding: 0px 3px } - Sales + Sales Rate @@ -41,6 +41,7 @@ TD.rowhead { font-weight: bold; text-align: left; padding: 0px 3px } Non-taxable Non-taxable Non-taxable + Credited Taxable @@ -73,10 +74,19 @@ TD.rowhead { font-weight: bold; text-align: left; padding: 0px 3px } % } # if $row->{pkgclass} ne ... % # construct base links that limit to the tax rates described by this row +% # cust_bill_pkg.cgi wants a list of specific taxnums (and package class) +% # cust_credit_bill_pkg.html wants a geographic scope (and package class) % my $rowlink = ';taxnum=' . $row->{taxnums}; +% my $rowregion = ''; +% foreach my $loc (qw(state county city district)) { +% if ( $row->{$loc} ) { +% $rowregion .= ";$loc=" . uri_escape($row->{$loc}); +% } +% } % # and also the package class, if we're limiting package class % if ( $params{breakdown}->{pkgclass} ) { % $rowlink .= ';classnum=' . ($row->{pkgclass} || 0); +% $rowregion .= ';classnum=' . ($row->{pkgclass} || 0); % } % % if ( $row->{total} ) { @@ -109,14 +119,20 @@ TD.rowhead { font-weight: bold; text-align: left; padding: 0px 3px } <% $money_sprintf->( $row->{exempt_monthly} ) %> +% # credited sales + + + <% $money_sprintf->( $row->{sales_credited} ) %> + + + × + <% $row->{rate} %> % # taxable sales "> <% $money_sprintf->( $row->{taxable} ) %> - × - <% $row->{rate} %> % # estimated tax = @@ -134,12 +150,12 @@ TD.rowhead { font-weight: bold; text-align: left; padding: 0px 3px } − %# currently broken - <% $money_sprintf->( $row->{credit} ) %> + <% $money_sprintf->( $row->{tax_credited} ) %> %# % # net tax due = - <% $money_sprintf->( $row->{tax} - $row->{credit} ) %> + <% $money_sprintf->( $row->{tax} - $row->{tax_credited} ) %> % # tax collected   <% $money_sprintf->( $row->{tax_paid} ) %> @@ -223,6 +239,11 @@ if ( $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"; +my $salescreditlink = $p. "search/cust_credit_bill_pkg.html?$dateagentlink;nottax=1"; +if ( $params{'credit_date'} eq 'cust_credit_bill' ) { + $salescreditlink =~ s/begin/credit_begin/; + $salescreditlink =~ s/end/credit_end/; +} #my $creditlink = $p. "search/cust_bill_pkg.cgi?$dateagentlink;credit=1;istax=1"; #if ( $params{'credit_date'} eq 'cust_credit_bill' ) { # $creditlink =~ s/begin/credit_begin/; -- 2.11.0