From 294e2ce31d6bbd2784a812d20438f9b223de0490 Mon Sep 17 00:00:00 2001 From: Mark Wells Date: Thu, 8 Oct 2015 15:31:31 -0700 Subject: [PATCH] more detailed tax-credit report, #37088 --- FS/FS/Report/Tax.pm | 36 +++++++++-- httemplate/search/cust_credit_bill_pkg.html | 49 +++++++++++++-- httemplate/search/report_tax-xls.cgi | 94 +++++++++++++++++++++++++---- httemplate/search/report_tax.cgi | 86 +++++++++++++++++++++++++- 4 files changed, 240 insertions(+), 25 deletions(-) diff --git a/FS/FS/Report/Tax.pm b/FS/FS/Report/Tax.pm index 2480a45b9..a892a6b87 100644 --- a/FS/FS/Report/Tax.pm +++ b/FS/FS/Report/Tax.pm @@ -240,6 +240,25 @@ sub report_internal { $group "; + # also include the exempt-sales credit amount, for the credit report + $sql{exempt_credited} = "$select + SUM(COALESCE(exempt_credited, 0)) + FROM cust_main_county + LEFT JOIN ($exempt_credit) AS exempt_credit USING (taxnum) + JOIN cust_bill_pkg USING (billpkgnum) + $join_cust_pkg $where AND $nottax + $group + "; + + $all_sql{exempt_credited} = "$select_all + SUM(COALESCE(exempt_credited, 0)) + FROM cust_main_county + LEFT JOIN ($exempt_credit) AS exempt_credit USING (taxnum) + JOIN cust_bill_pkg USING (billpkgnum) + $join_cust_pkg $where AND $nottax + $group + "; + # taxable sales $sql{taxable} = "$select SUM(cust_bill_pkg.setup + cust_bill_pkg.recur @@ -339,12 +358,12 @@ sub report_internal { my $istax = "cust_bill_pkg.pkgnum = 0 and cust_bill_pkg.feepart is null"; - $sql{tax} = "$select SUM(cust_bill_pkg_tax_location.amount) + $sql{tax} = "$select COALESCE(SUM(cust_bill_pkg_tax_location.amount),0) $taxfrom $where AND $istax $group"; - $all_sql{tax} = "$select_all SUM(cust_bill_pkg_tax_location.amount) + $all_sql{tax} = "$select_all COALESCE(SUM(cust_bill_pkg_tax_location.amount),0) $taxfrom $where AND $istax $group_all"; @@ -364,12 +383,12 @@ sub report_internal { $creditwhere =~ s/cust_bill._date/cust_credit_bill._date/g; } - $sql{tax_credited} = "$select SUM(cust_credit_bill_pkg.amount) + $sql{tax_credited} = "$select COALESCE(SUM(cust_credit_bill_pkg.amount),0) $creditfrom $creditwhere AND $istax $group"; - $all_sql{tax_credited} = "$select_all SUM(cust_credit_bill_pkg.amount) + $all_sql{tax_credited} = "$select_all COALESCE(SUM(cust_credit_bill_pkg.amount),0) $creditfrom $creditwhere AND $istax $group_all"; @@ -385,12 +404,12 @@ sub report_internal { ' ON (cust_bill_pay_pkg.billpkgtaxlocationnum ='. ' cust_bill_pkg_tax_location.billpkgtaxlocationnum)'; - $sql{tax_paid} = "$select SUM(cust_bill_pay_pkg.amount) + $sql{tax_paid} = "$select COALESCE(SUM(cust_bill_pay_pkg.amount),0) $paidfrom $where AND $istax $group"; - $all_sql{tax_paid} = "$select_all SUM(cust_bill_pay_pkg.amount) + $all_sql{tax_paid} = "$select_all COALESCE(SUM(cust_bill_pay_pkg.amount),0) $paidfrom $where AND $istax $group_all"; @@ -562,6 +581,11 @@ sub table { $this_row{exempt_pkg} + $this_row{exempt_monthly} ); + $this_row{credits} = sprintf('%.2f', + $this_row{sales_credited} + + $this_row{exempt_credited} + + $this_row{tax_credited} + ); # and give it a label if ( $this_row{total} ) { $this_row{label} = 'Total'; diff --git a/httemplate/search/cust_credit_bill_pkg.html b/httemplate/search/cust_credit_bill_pkg.html index b5a0ee9f6..5e70c23bd 100644 --- a/httemplate/search/cust_credit_bill_pkg.html +++ b/httemplate/search/cust_credit_bill_pkg.html @@ -3,11 +3,12 @@ 'name_singular' => 'credit application', 'query' => $query, 'count_query' => $count_query, - 'count_addl' => [ $money_char. '%.2f total', ], + 'count_addl' => \@count_addl, 'header' => [ #'#', 'Amount', + 'Tax exempt', #credit 'Date', @@ -26,7 +27,9 @@ ], 'fields' => [ #'creditbillpkgnum', - sub { sprintf($money_char.'%.2f', shift->amount ) }, + sub { sprintf($money_char.'%.2f', shift->amount ) }, + + sub { sprintf($money_char.'%.2f', shift->get('exempt_credited') ) }, sub { time2str('%b %d %Y', shift->get('cust_credit_date') ) }, sub { shift->cust_credit_bill->cust_credit->otaker }, @@ -44,6 +47,7 @@ ], 'sort_fields' => [ 'amount', + 'exempt_credited', 'cust_credit_date', '', #'otaker', '', #reason @@ -61,6 +65,7 @@ '', '', '', + '', @post_desc_null, $ilink, $ilink, @@ -68,11 +73,12 @@ FS::UI::Web::cust_header() ), ], - 'align' => 'rrllll'. + 'align' => 'rrrllll'. $post_desc_align. 'rr'. FS::UI::Web::cust_aligns(), - 'color' => [ + 'color' => [ + '', '', '', '', @@ -91,6 +97,7 @@ '', '', '', + '', @post_desc_null, '', '', @@ -286,7 +293,6 @@ if ( $cgi->param('out') ) { #} push @where, $loc_sql; -warn $loc_sql; } my($title, $name); @@ -393,6 +399,9 @@ if ( $cgi->param('cust_tax') ) { my $count_query = "SELECT COUNT(DISTINCT creditbillpkgnum), SUM(cust_credit_bill_pkg.amount)"; +if ( $cgi->param('nottax') ) { + $count_query .= ", SUM(exempt_credited)"; +} my $join_cust = ' JOIN cust_bill ON ( cust_bill_pkg.invnum = cust_bill.invnum )'. @@ -405,6 +414,21 @@ my $join_cust_bill_pkg = 'LEFT JOIN cust_bill_pkg USING ( billpkgnum )'; if ( $cgi->param('nottax') ) { + # There can be multiple cust_tax_exempt_pkg records with the same + # creditbillpkgnum iff the line item is exempt from multiple taxes. + # They will all have the same amount, except in the case where there are + # different exemption types and so the exemption amounts are different. + # In that case, show the amount of the largest exemption. + + $join_cust_bill_pkg .= ' + LEFT JOIN( + SELECT creditbillpkgnum, + MAX(0 - cust_tax_exempt_pkg.amount) AS exempt_credited + FROM cust_tax_exempt_pkg + WHERE creditbillpkgnum IS NOT NULL + GROUP BY creditbillpkgnum + ) AS exempt_credit USING (creditbillpkgnum) + '; $join_pkg = ' LEFT JOIN cust_pkg USING ( pkgnum ) LEFT JOIN part_pkg USING ( pkgpart ) LEFT JOIN part_pkg AS override @@ -472,6 +496,12 @@ push @select, 'part_pkg.pkg' unless $cgi->param('istax'); push @select, 'cust_main.custnum', FS::UI::Web::cust_sql_fields(); +if ( $cgi->param('istax') ) { + push @select, 'NULL AS exempt_credited'; # just display zero +} elsif ( $cgi->param('nottax') ) { + push @select, 'exempt_credited'; +} + my @post_desc_header = (); my @post_desc = (); my @post_desc_null = (); @@ -555,4 +585,13 @@ my $location_sub = sub { }; +my @count_addl = ( $money_char. '%.2f total', ); +if ( $cgi->param('nottax') ) { + push @count_addl, ( $money_char. '%.2f tax exempt' ); +} + +if ( $cgi->param('debug') ) { + warn "\nQUERY:\n" . Dumper($query) . "\nCOUNT_QUERY:\n$count_query\n\n"; +} + diff --git a/httemplate/search/report_tax-xls.cgi b/httemplate/search/report_tax-xls.cgi index 743f14788..773b403f1 100755 --- a/httemplate/search/report_tax-xls.cgi +++ b/httemplate/search/report_tax-xls.cgi @@ -122,7 +122,7 @@ my %default = ( border => 1, ); my @widths = ( #ick - 30, (13) x 5, 3, 7.5, 3, 11, 11, 3, 11, 3, 11 + 30, (13) x 6, 3, 7.5, 3, 11, 11, 3, 11, 3, 11 ); my @format = ( {}, {}, {} ); # white row, gray row, yellow (totals) row @@ -134,29 +134,34 @@ foreach (keys(%formatdef)) { italic => 1, %f); } -my $ws = $workbook->add_worksheet('taxreport'); +my $ws = $workbook->add_worksheet('Sales and Tax'); # main title $ws->merge_range(0, 0, 0, 14, $report->title, $format[0]->{title}); +$ws->set_row(0, 30); # excel position my $x = 0; my $y = 2; my $colhead = $format[0]->{colhead}; # print header -$ws->merge_range($y, 1, $y, 5, 'Sales', $colhead); -$ws->merge_range($y, 6, $y+1, 8, 'Rate', $colhead); -$ws->merge_range($y, 9, $y, 15, 'Tax', $colhead); +$ws->merge_range($y, 1, $y, 6, 'Sales', $colhead); +$ws->merge_range($y, 7, $y+1, 9, 'Rate', $colhead); +$ws->merge_range($y, 10, $y, 16, 'Tax', $colhead); $y++; $colhead = $format[0]->{colhead_small}; -$ws->write($y, 1, [ 'Total', 'Exempt customer', 'Exempt package', 'Monthly exemption', +$ws->write($y, 1, [ 'Total', + 'Exempt customer', + 'Exempt package', + 'Monthly exemption', + 'Credited', 'Taxable' ], $colhead); -$ws->write($y, 9, 'Estimated', $colhead); -$ws->write($y, 10, 'Invoiced', $colhead); -$ws->write($y, 12, 'Credited', $colhead); -$ws->write($y, 14, 'Net due', $colhead); -$ws->write($y, 15, 'Collected',$colhead); +$ws->write($y, 10, 'Estimated', $colhead); +$ws->write($y, 11, 'Invoiced', $colhead); +$ws->write($y, 13, 'Credited', $colhead); +$ws->write($y, 15, 'Net due', $colhead); +$ws->write($y, 16, 'Collected',$colhead); $y++; # print data @@ -168,7 +173,7 @@ foreach my $row (@rows) { if ( $row->{pkgclass} ne $prev_row->{pkgclass} ) { $rownum = 1; if ( $params{breakdown}->{pkgclass} ) { - $ws->merge_range($y, 0, $y, 14, + $ws->merge_range($y, 0, $y, 15, $pkgclass_name{$row->{pkgclass}}, $format[0]->{sectionhead} ); @@ -182,7 +187,7 @@ foreach my $row (@rows) { } $ws->write($y, $x, $row->{label}, $f->{rowhead}); $x++; - foreach (qw(sales exempt_cust exempt_pkg exempt_monthly taxable)) { + foreach (qw(sales exempt_cust exempt_pkg exempt_monthly sales_credited taxable)) { $ws->write($y, $x, $row->{$_} || 0, $f->{currency}); $x++; } @@ -229,6 +234,69 @@ for my $x (0..scalar(@widths)-1) { $ws->set_column($x, $x, $widths[$x]); } +# do the same for the credit worksheet +$ws = $workbook->add_worksheet('Credits'); + +my $title = $report->title; +$title =~ s/Tax Report/Credits/; +# main title +$ws->merge_range(0, 0, 0, 14, $title, $format[0]->{title}); +$ws->set_row(0, 30); # height +# excel position +$x = 0; +$y = 2; + +$colhead = $format[0]->{colhead}; +# print header +$ws->merge_range($y, 1, $y+1, 1, 'Total', $colhead); +$ws->merge_range($y, 2, $y, 4, 'Applied to', $colhead); + +$y++; +$colhead = $format[0]->{colhead_small}; +$ws->write($y, 2, [ 'Taxable sales', + 'Tax-exempt sales', + 'Taxes' + ], $colhead); +$y++; + +# print data +$rownum = 1; +$prev_row = { pkgclass => 'DUMMY PKGCLASS' }; + +foreach my $row (@rows) { + $x = 0; + if ( $row->{pkgclass} ne $prev_row->{pkgclass} ) { + $rownum = 1; + if ( $params{breakdown}->{pkgclass} ) { + $ws->merge_range($y, 0, $y, 4, + $pkgclass_name{$row->{pkgclass}}, + $format[0]->{sectionhead} + ); + $y++; + } + } + # pick a format set + my $f = $format[$rownum % 2]; + if ( $row->{total} ) { + $f = $format[2]; + } + $ws->write($y, $x, $row->{label}, $f->{rowhead}); + $x++; + foreach (qw(credits sales_credited exempt_credited tax_credited)) { + $ws->write($y, $x, $row->{$_} || 0, $f->{currency}); + $x++; + } + + $rownum++; + $y++; + $prev_row = $row; +} + +for my $x (0..4) { + $ws->set_column($x, $x, $widths[$x]); +} + + $workbook->close; http_header('Content-Length' => length($data)); diff --git a/httemplate/search/report_tax.cgi b/httemplate/search/report_tax.cgi index 0ad143f01..2b531ea46 100644 --- a/httemplate/search/report_tax.cgi +++ b/httemplate/search/report_tax.cgi @@ -18,6 +18,7 @@ TD.rowhead { font-weight: bold; text-align: left; padding: 0px 3px } .bigmath { font-size: large; font-weight: bold; font: sans-serif; text-align: center } .total { font-style: italic } + <& /elements/table-grid.html &> @@ -88,7 +89,6 @@ TD.rowhead { font-weight: bold; text-align: left; padding: 0px 3px } % $rowlink .= ';classnum=' . ($row->{pkgclass} || 0); % $rowregion .= ';classnum=' . ($row->{pkgclass} || 0); % } -%warn $rowregion; % % if ( $row->{total} ) { @@ -183,6 +183,90 @@ TD.rowhead { font-weight: bold; text-align: left; padding: 0px 3px } % } +
+<& /elements/table-grid.html &> + + + + Total credits + Applied to + + + Taxable sales + Tax-exempt sales + Taxes + + + +% $rownum = 0; +% $prev_row = { pkgclass => 'DUMMY PKGCLASS' }; + + +% # mostly duplicates the stuff above... +% # but putting it all in one giant table is no good +% foreach my $row (@rows) { +% if ( $row->{pkgclass} ne $prev_row->{pkgclass} ) { +% if ( $rownum > 0 ) { # start a new section +% $rownum = 0; + +% } +% if ( $params{breakdown}->{pkgclass} ) { # and caption the new section + + + <% $pkgclass_name{$row->{pkgclass}} %> + + +% } +% } # if $row->{pkgclass} ne ... + +% my $rowlink = ';taxnum=' . $row->{taxnums}; +% my $rowregion = ';country=' . $cgi->param('country'); +% foreach my $loc (qw(state county city district)) { +% if ( $row->{$loc} ) { +% $rowregion .= ";$loc=" . uri_escape($row->{$loc}); +% } +% } +% if ( $params{breakdown}->{pkgclass} ) { +% $rowlink .= ';classnum=' . ($row->{pkgclass} || 0); +% $rowregion .= ';classnum=' . ($row->{pkgclass} || 0); +% } +% +% if ( $row->{total} ) { + +% } + + <% $row->{label} |h %> + +% # Total credits + <% $money_sprintf->( $row->{credits} ) %> + +% # Credits to taxable sales + + + <% $money_sprintf->( $row->{sales_credited} ) %> + + +% # ... to exempt sales (link is the same, it shows both exempt and taxable) + + + <% $money_sprintf->( $row->{exempt_credited} ) %> + + +% # ... to taxes + +%# currently broken + <% $money_sprintf->( $row->{tax_credited} ) %> +%# + + +% $rownum++; +% $prev_row = $row; +% } # foreach my $row +% # no "out of taxable region" for credits (yet) + + + + <& /elements/footer.html &> <%init> -- 2.11.0