more detailed tax-credit report, #37088

diff --git a/FS/FS/Report/Tax.pm b/FS/FS/Report/Tax.pm
index 2480a45..a892a6b 100644 (file)
--- 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 b5a0ee9..5e70c23 100644 (file)
--- 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 743f147..773b403 100755 (executable)
--- 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 0ad143f..2b531ea 100644 (file)
--- 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>