$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
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";
$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";
' 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";
$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';
'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',
],
'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 },
],
'sort_fields' => [
'amount',
+ 'exempt_credited',
'cust_credit_date',
'', #'otaker',
'', #reason
'',
'',
'',
+ '',
@post_desc_null,
$ilink,
$ilink,
FS::UI::Web::cust_header()
),
],
- 'align' => 'rrllll'.
+ 'align' => 'rrrllll'.
$post_desc_align.
'rr'.
FS::UI::Web::cust_aligns(),
- 'color' => [
+ 'color' => [
+ '',
'',
'',
'',
'',
'',
'',
+ '',
@post_desc_null,
'',
'',
#}
push @where, $loc_sql;
-warn $loc_sql;
}
my($title, $name);
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 )'.
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
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 = ();
};
+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>
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
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
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}
);
}
$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++;
}
$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));
.bigmath { font-size: large; font-weight: bold; font: sans-serif; text-align: center }
.total { font-style: italic }
</STYLE>
+
<& /elements/table-grid.html &>
<THEAD>
<TR>
% $rowlink .= ';classnum=' . ($row->{pkgclass} || 0);
% $rowregion .= ';classnum=' . ($row->{pkgclass} || 0);
% }
-%warn $rowregion;
%
% if ( $row->{total} ) {
</TBODY><TBODY CLASS="total">
% }
</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>