add credited sales column to tax report, #37088

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 }
   <THEAD>
   <TR>
     <TH ROWSPAN=3></TH>
-    <TH COLSPAN=5>Sales</TH>
+    <TH COLSPAN=6>Sales</TH>
     <TH ROWSPAN=3></TH>
     <TH ROWSPAN=3>Rate</TH>
     <TH ROWSPAN=3></TH>
@@ -41,6 +41,7 @@ TD.rowhead { font-weight: bold; text-align: left; padding: 0px 3px }
     <TH ROWSPAN=1>Non-taxable</TH>
     <TH ROWSPAN=1>Non-taxable</TH>
     <TH ROWSPAN=1>Non-taxable</TH>
+    <TH ROWSPAN=2>Credited</TH>
     <TH ROWSPAN=2>Taxable</TH>
   </TR>
 
@@ -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} ) %>
       </A>
     </TD>
+%   # credited sales
+    <TD>
+      <A HREF="<% $salescreditlink . $rowregion %>">
+        <% $money_sprintf->( $row->{sales_credited} ) %>
+      </A>
+    </TD>
+    <TD CLASS="bigmath"> &times; </TD>
+    <TD><% $row->{rate} %></TD>
 %   # taxable sales
     <TD>
       <A HREF="<% $saleslink . $rowlink . ";taxable=1" %>">
         <% $money_sprintf->( $row->{taxable} ) %>
       </A>
     </TD>
-    <TD CLASS="bigmath"> &times; </TD>
-    <TD><% $row->{rate} %></TD>
 %   # estimated tax
     <TD CLASS="bigmath"> = </TD>
     <TD>
@@ -134,12 +150,12 @@ TD.rowhead { font-weight: bold; text-align: left; padding: 0px 3px }
     <TD CLASS="bigmath"> &minus; </TD>
     <TD>
 %#      <A HREF="<% $creditlink . $rowlink %>"> currently broken
-        <% $money_sprintf->( $row->{credit} ) %>
+        <% $money_sprintf->( $row->{tax_credited} ) %>
 %#      </A>
     </TD>
 %   # net tax due
     <TD CLASS="bigmath"> = </TD>
-    <TD><% $money_sprintf->( $row->{tax} - $row->{credit} ) %></TD>
+    <TD><% $money_sprintf->( $row->{tax} - $row->{tax_credited} ) %></TD>
 %   # tax collected
     <TD>&nbsp;</TD>
     <TD><% $money_sprintf->( $row->{tax_paid} ) %></TD>
@@ -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/;