option to handle credited tax date range differently, #28497

4 files changed, 66 insertions, 25 deletions

diff --git a/FS/FS/Report/Tax.pm b/FS/FS/Report/Tax.pm
index fbb98c6..d3322af 100644
--- a/FS/FS/Report/Tax.pm
+++ b/FS/FS/Report/Tax.pm
@@ -93,7 +93,7 @@ sub report_internal {
   my $pkg_tax_exempt = "SELECT SUM(amount) AS exempt_charged, billpkgnum, taxnum ".
     "FROM cust_tax_exempt_pkg EXEMPT_WHERE GROUP BY billpkgnum, taxnum";
 
-  my $where = "WHERE _date >= $beginning AND _date <= $ending ".
+  my $where = "WHERE cust_bill._date >= $beginning AND cust_bill._date <= $ending ".
               "AND COALESCE(cust_main_county.taxname,'Tax') = '$taxname' ".
               "AND cust_main_county.country = '$country'";
   # SELECT/GROUP clauses for first-level queries
@@ -238,9 +238,16 @@ sub report_internal {
   # ($creditfrom includes join of taxable item to part_pkg if with_pkgclass
   # is on)
   my $creditfrom = $taxfrom .
-     ' JOIN cust_credit_bill_pkg USING (billpkgtaxlocationnum)';
+    ' JOIN cust_credit_bill_pkg USING (billpkgtaxlocationnum)' .
+    ' JOIN cust_credit_bill     USING (creditbillnum)';
   my $creditwhere = $where . 
-     ' AND billpkgtaxratelocationnum IS NULL';
+    ' AND billpkgtaxratelocationnum IS NULL';
+
+  # if the credit_date option is set to application date, change
+  # $creditwhere accordingly
+  if ( $opt{credit_date} eq 'cust_credit_bill' ) {
+    $creditwhere     =~ s/cust_bill._date/cust_credit_bill._date/g;
+  }
 
   $sql{credit} = "$select SUM(cust_credit_bill_pkg.amount)
                   $creditfrom
diff --git a/httemplate/search/cust_bill_pkg.cgi b/httemplate/search/cust_bill_pkg.cgi
index b1086e3..0ce141b 100644
--- a/httemplate/search/cust_bill_pkg.cgi
+++ b/httemplate/search/cust_bill_pkg.cgi
@@ -265,8 +265,7 @@ if ( $cgi->param('distribute') == 1 ) {
   push @where, "sdate <= $ending",
                "edate >  $beginning",
   ;
-}
-else {
+} else {
   push @where, "cust_bill._date >= $beginning",
                "cust_bill._date <= $ending";
 }
@@ -561,30 +560,36 @@ if ( $cgi->param('nottax') ) {
                    ' )'
         if @classnums;
     }
-  }
 
-  # taxclass
-  if ( $cgi->param('taxclassNULL') ) {
-    push @where, 'cust_main_county.taxclass IS NULL';
-  }
+    # taxclass
+    if ( $cgi->param('taxclassNULL') ) {
+      push @where, 'cust_main_county.taxclass IS NULL';
+    }
 
-  # taxname
-  if ( $cgi->param('taxnameNULL') ) {
-    push @where, 'cust_main_county.taxname IS NULL OR '.
-                 'cust_main_county.taxname = \'Tax\'';
-  } elsif ( $cgi->param('taxname') ) {
-    push @where, 'cust_main_county.taxname = '.
-                  dbh->quote($cgi->param('taxname'));
-  }
+    # taxname
+    if ( $cgi->param('taxnameNULL') ) {
+      push @where, 'cust_main_county.taxname IS NULL OR '.
+                   'cust_main_county.taxname = \'Tax\'';
+    } elsif ( $cgi->param('taxname') ) {
+      push @where, 'cust_main_county.taxname = '.
+                    dbh->quote($cgi->param('taxname'));
+    }
 
-  # itemdesc, for breakdown from the vendor tax report
-  if ( $cgi->param('itemdesc') ) {
-    if ( $cgi->param('itemdesc') eq 'Tax' ) {
-      push @where, "($itemdesc = 'Tax' OR $itemdesc is null)";
-    } else {
-      push @where, "$itemdesc = ". dbh->quote($cgi->param('itemdesc'));
+    # itemdesc, for breakdown from the vendor tax report
+    if ( $cgi->param('itemdesc') ) {
+      if ( $cgi->param('itemdesc') eq 'Tax' ) {
+        push @where, "($itemdesc = 'Tax' OR $itemdesc is null)";
+      } else {
+        push @where, "$itemdesc = ". dbh->quote($cgi->param('itemdesc'));
+      }
     }
-  }
+
+    # specific taxnums
+    if ( $cgi->param('taxnum') =~ /^([\d,]+)$/) {
+      push @where, "cust_main_county.taxnum IN ($1)";
+    }
+
+  } #end of "normal case"
 
 } # nottax / istax
 
@@ -600,6 +605,12 @@ push @select, "($pay_sub) AS pay_amount";
 # credit
 if ( $cgi->param('credit') ) {
 
+  my $credit_where;
+
+  my($cr_begin, $cr_end) = FS::UI::Web::parse_beginning_ending($cgi, 'credit');
+  $credit_where = "WHERE cust_credit_bill._date >= $cr_begin " .
+                  "AND cust_credit_bill._date <= $cr_end";
+
   my $credit_sub;
 
   if ( $cgi->param('istax') ) {
@@ -613,6 +624,7 @@ if ( $cgi->param('credit') ) {
       JOIN cust_credit USING (crednum)
       LEFT JOIN reason USING (reasonnum)
       LEFT JOIN access_user USING (usernum)
+    $credit_where
     GROUP BY billpkgnum, billpkgtaxlocationnum, reason.reason, 
       access_user.username";
 
@@ -643,6 +655,7 @@ if ( $cgi->param('credit') ) {
       JOIN cust_credit USING (crednum)
       LEFT JOIN reason USING (reasonnum)
       LEFT JOIN access_user USING (usernum)
+    $credit_where
     GROUP BY billpkgnum, reason.reason, access_user.username";
     $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit USING (billpkgnum)";
   }
diff --git a/httemplate/search/report_tax.cgi b/httemplate/search/report_tax.cgi
index 08f255f..124e5d8 100755
--- a/httemplate/search/report_tax.cgi
+++ b/httemplate/search/report_tax.cgi
@@ -176,6 +176,12 @@ if ( $cgi->param('taxname') =~ /^([\w ]+)$/ ) {
   die "taxname required";
 }
 
+if ( $cgi->param('credit_date') eq 'cust_credit_bill' ) {
+  $params{credit_date} = 'cust_credit_bill';
+} else {
+  $params{credit_date} = 'cust_bill';
+}
+
 warn "PARAMS:\n".Dumper(\%params)."\n\n" if $DEBUG;
 
 my $report = FS::Report::Tax->report_internal(%params);
@@ -193,6 +199,11 @@ my $taxlink    = $p. "search/cust_bill_pkg.cgi?$dateagentlink;istax=1";
 my $exemptlink = $p. "search/cust_tax_exempt_pkg.cgi?$dateagentlink";
 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/;
+  $creditlink =~ s/end/credit_end/;
+}
+
 my %pkgclass_name = map { $_->classnum, $_->classname } qsearch('pkg_class');
 $pkgclass_name{''} = 'Unclassified';
 
diff --git a/httemplate/search/report_tax.html b/httemplate/search/report_tax.html
index ee25f62..8d8d108 100755
--- a/httemplate/search/report_tax.html
+++ b/httemplate/search/report_tax.html
@@ -33,6 +33,16 @@
       district  => 'District',
     },
   &>
+  <TR>
+    <TD></TD>
+    <TD>Deduct credited tax if it was 
+      <SELECT NAME="credit_date">
+        <OPTION VALUE="cust_bill" SELECTED>invoiced in this period</OPTION>
+        <OPTION VALUE="cust_credit_bill">credited in this period</OPTION>
+      </SELECT>
+    </TD>
+  </TR>
+
 </TABLE>
 
 <BR><INPUT TYPE="submit" VALUE="Get Report">