diff options
author | Mark Wells <mark@freeside.biz> | 2015-10-08 15:31:31 -0700 |
---|---|---|
committer | Mark Wells <mark@freeside.biz> | 2015-10-08 23:47:57 -0700 |
commit | 5f9878e211ede0cbfa16e0b9834cd4b338e8238d (patch) | |
tree | 89176340f4604439a459fff7cdee6fa6fc688c39 /httemplate | |
parent | 208cb71d0313f755107fa677998c8f5073d2cedf (diff) |
more detailed tax-credit report, #37088
Diffstat (limited to 'httemplate')
-rw-r--r-- | httemplate/search/cust_credit_bill_pkg.html | 49 | ||||
-rwxr-xr-x | httemplate/search/report_tax-xls.cgi | 94 | ||||
-rw-r--r-- | httemplate/search/report_tax.cgi | 86 |
3 files changed, 210 insertions, 19 deletions
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"; +} + </%init> 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 } </STYLE> + <& /elements/table-grid.html &> <THEAD> <TR> @@ -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} ) { </TBODY><TBODY CLASS="total"> @@ -183,6 +183,90 @@ TD.rowhead { font-weight: bold; text-align: left; padding: 0px 3px } % } </TABLE> +<BR> +<& /elements/table-grid.html &> + <THEAD> + <TR> + <TH ROwSPAN=2></TH> + <TH ROWSPAN=2>Total credits</TH> + <TH COLSPAN=3>Applied to</TH> + </TR> + <TR STYLE="font-size: small"> + <TH>Taxable sales</TH> + <TH>Tax-exempt sales</TH> + <TH>Taxes</TH> + </TR> + </THEAD> + +% $rownum = 0; +% $prev_row = { pkgclass => 'DUMMY PKGCLASS' }; + + <TBODY> +% # 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; + </TBODY><TBODY> +% } +% if ( $params{breakdown}->{pkgclass} ) { # and caption the new section + <TR> + <TD COLSPAN=5 CLASS="sectionhead"> + <% $pkgclass_name{$row->{pkgclass}} %> + </TD> + </TR> +% } +% } # 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} ) { + </TBODY><TBODY CLASS="total"> +% } + <TR CLASS="row<% $rownum % 2 %>"> + <TD CLASS="rowhead"><% $row->{label} |h %></TD> + <TD> +% # Total credits + <% $money_sprintf->( $row->{credits} ) %> + </TD> +% # Credits to taxable sales + <TD> + <A HREF="<% $salescreditlink . $rowregion %>"> + <% $money_sprintf->( $row->{sales_credited} ) %> + </A> + </TD> +% # ... to exempt sales (link is the same, it shows both exempt and taxable) + <TD> + <A HREF="<% $salescreditlink . $rowregion %>"> + <% $money_sprintf->( $row->{exempt_credited} ) %> + </A> + </TD> +% # ... to taxes + <TD> +%# <A HREF="<% $creditlink . $rowlink %>"> currently broken + <% $money_sprintf->( $row->{tax_credited} ) %> +%# </A> + </TD> + </TR> +% $rownum++; +% $prev_row = $row; +% } # foreach my $row +% # no "out of taxable region" for credits (yet) + </TBODY> +</TABLE> + + <& /elements/footer.html &> <%init> |