credits return taxes, but the magic calculation button does not yet work properly (grrr - more sleep required) RT#4729

3 files changed, 92 insertions, 6 deletions

diff --git a/httemplate/search/cust_bill_pkg.cgi b/httemplate/search/cust_bill_pkg.cgi
index 52f59de1e..975a30713 100644
--- a/httemplate/search/cust_bill_pkg.cgi
+++ b/httemplate/search/cust_bill_pkg.cgi
@@ -404,8 +404,6 @@ if ( $cgi->param('pkg_tax') ) {
 
 }
 
-my $where = ' WHERE '. join(' AND ', @where);
-
 my $join_cust =  '      JOIN cust_bill USING ( invnum ) 
                    LEFT JOIN cust_main USING ( custnum ) ';
 
@@ -427,14 +425,28 @@ if ( $cgi->param('nottax') ) {
     $join_pkg .= ' LEFT JOIN cust_bill_pkg_tax_location USING ( billpkgnum )
                    LEFT JOIN cust_location              USING ( locationnum ) ';
 
-    #quelle kludge, false laziness w/report_tax.cgi
-    $where =~ s/cust_pkg\.locationnum/cust_bill_pkg_tax_location.locationnum/g; 
-  } elsif ( scalar( grep( /locationtaxid/, $cgi->param ) ) ) {
+    #quelle kludge, somewhat false laziness w/report_tax.cgi
+    s/cust_pkg\.locationnum/cust_bill_pkg_tax_location.locationnum/g for @where;
+  } elsif ( scalar( grep( /locationtaxid/, $cgi->param ) ) ||
+            $cgi->param('iscredit') eq 'rate') {
     $join_pkg .=
       ' LEFT JOIN cust_bill_pkg_tax_rate_location USING ( billpkgnum ) '.
       ' LEFT JOIN tax_rate_location USING ( taxratelocationnum ) ';
   }
 
+  if ( $cgi->param('iscredit') ) {
+    $join_pkg .= ' JOIN cust_credit_bill_pkg USING ( billpkgnum';
+    if ( $conf->exists('tax-pkg_address') ) {
+      $join_pkg .= ', billpkgtaxlocationnum )';
+      push @where, "billpkgtaxratelocationnum IS NULL";
+    } elsif ( $cgi->param('iscredit') eq 'rate' ) {
+      $join_pkg .= ', billpkgtaxratelocationnum )';
+    } else {
+      $join_pkg .= ' )';
+      push @where, "billpkgtaxratelocationnum IS NULL";
+    }
+  }
+
 } else { 
 
   #die?
@@ -445,6 +457,8 @@ if ( $cgi->param('nottax') ) {
 
 }
 
+my $where = ' WHERE '. join(' AND ', @where);
+
 if ($use_usage) {
   $count_query .=
     " FROM (SELECT cust_bill_pkg.setup, cust_bill_pkg.recur, 
diff --git a/httemplate/search/report_newtax.cgi b/httemplate/search/report_newtax.cgi
index 0fb548352..6a2cbb0d1 100755
--- a/httemplate/search/report_newtax.cgi
+++ b/httemplate/search/report_newtax.cgi
@@ -17,6 +17,9 @@
     <TH CLASS="grid" BGCOLOR="#cccccc"></TH>
     <TH CLASS="grid" BGCOLOR="#cccccc"></TH>
     <TH CLASS="grid" BGCOLOR="#cccccc">Tax collected</TH>
+    <TH CLASS="grid" BGCOLOR="#cccccc">&nbsp;&nbsp;&nbsp;&nbsp;</TH>
+    <TH CLASS="grid" BGCOLOR="#cccccc"></TH>
+    <TH CLASS="grid" BGCOLOR="#cccccc">Tax credited</TH>
   </TR>
 % my $bgcolor1 = '#eeeeee';
 % my $bgcolor2 = '#ffffff';
@@ -43,6 +46,12 @@
         <A HREF="<% $baselink. $link %>;istax=1"><% $money_char %><% sprintf('%.2f', $tax->{'tax'} ) %></A>
       </TD>
       <% !($tax->{base}) ? qq!<TD CLASS="grid" BGCOLOR="$bgcolor"></TD>! : '' %>
+      <TD CLASS="grid" BGCOLOR="<% $bgcolor %>"></TD>
+      <% $tax->{base} ? qq!<TD CLASS="grid" BGCOLOR="$bgcolor"></TD>! : '' %>
+      <TD CLASS="grid" BGCOLOR="<% $bgcolor %>" ALIGN="right">
+        <A HREF="<% $baselink. $link %>;istax=1;iscredit=rate"><% $money_char %><% sprintf('%.2f', $tax->{'credit'} ) %></A>
+      </TD>
+      <% !($tax->{base}) ? qq!<TD CLASS="grid" BGCOLOR="$bgcolor"></TD>! : '' %>
     </TR>
 % } 
 
@@ -90,6 +99,7 @@ my @taxparam = ( 'itemdesc', 'tax_rate_location.state', 'tax_rate_location.count
 my $select = 'DISTINCT itemdesc,locationtaxid,tax_rate_location.state,tax_rate_location.county,tax_rate_location.city';
 
 my $tax = 0;
+my $credit = 0;
 my %taxes = ();
 my %basetaxes = ();
 foreach my $t (qsearch({ table     => 'cust_bill_pkg',
@@ -120,6 +130,18 @@ foreach my $t (qsearch({ table     => 'cust_bill_pkg',
     $tax += $x;
     $taxes{$label}->{'tax'} += $x;
 
+    my $creditfrom = " JOIN cust_credit_bill_pkg USING (billpkgnum,billpkgtaxratelocationnum) ";
+    my $creditwhere = "FROM cust_bill_pkg $addl_from $creditfrom $where ".
+      "AND payby != 'COMP' ".
+      "AND ". join( ' AND ', map { "( $_ = ? OR ? = '' AND $_ IS NULL)" } @taxparam );
+
+    $sql = "SELECT SUM(cust_credit_bill_pkg.amount) ".
+           " $creditwhere AND cust_bill_pkg.pkgnum = 0";
+
+    my $y = scalar_sql($t, [ map { $_, $_ } @params ], $sql );
+    $credit += $y;
+    $taxes{$label}->{'credit'} += $y;
+
     unless ( exists( $taxes{$baselabel} ) ) {
 
       $basetaxes{$baselabel}->{'label'} = $baselabel;
@@ -129,6 +151,7 @@ foreach my $t (qsearch({ table     => 'cust_bill_pkg',
     }
 
     $basetaxes{$baselabel}->{'tax'} += $x;
+    $basetaxes{$baselabel}->{'credit'} += $y;
       
   }
 
@@ -160,6 +183,7 @@ push @taxes, {
   'label'          => 'Total',
   'url_param'      => '',
   'tax'            => $tax,
+  'credit'         => $credit,
   'base'           => 1,
 };
 
diff --git a/httemplate/search/report_tax.cgi b/httemplate/search/report_tax.cgi
index e89c66536..557c29cfa 100755
--- a/httemplate/search/report_tax.cgi
+++ b/httemplate/search/report_tax.cgi
@@ -22,6 +22,7 @@
     <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2>Tax owed</TH>
 % unless ( $cgi->param('show_taxclasses') ) { 
       <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2>Tax invoiced</TH>
+      <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2>Tax credited</TH>
 % } 
   </TR>
 
@@ -118,6 +119,10 @@
           <A HREF="<% $baselink. $invlink %>;istax=1"
           ><% &$money_sprintf( $region->{'tax'} ) %></A>
         </TD>
+        <<%$tdh%> ALIGN="right">
+          <A HREF="<% $baselink. $invlink %>;istax=1;iscredit=1"
+          ><% &$money_sprintf( $region->{'credit'} ) %></A>
+        </TD>
 % } 
 
     </TR>
@@ -132,6 +137,7 @@
     <TR>
       <TH CLASS="grid" BGCOLOR="#cccccc"></TH>
       <TH CLASS="grid" BGCOLOR="#cccccc">Tax invoiced</TH>
+      <TH CLASS="grid" BGCOLOR="#cccccc">Tax credited</TH>
     </TR>
 
 %   #some false laziness w/above
@@ -463,9 +469,18 @@ if ( $conf->exists('tax-pkg_address') ) {
   $taxwhere =~ s/cust_pkg\.locationnum/cust_bill_pkg_tax_location.locationnum/g;
 
 }
+my $creditfromwhere = $taxfromwhere. 
+   " JOIN cust_credit_bill_pkg USING (billpkgnum";
+$creditfromwhere .= " ,billpkgtaxlocationnum"
+   if $conf->exists('tax-pkg_address');
+$creditfromwhere .= ")";
+
 $taxfromwhere .= " $taxwhere "; #AND payby != 'COMP' ";
+$creditfromwhere .= " $taxwhere AND billpkgtaxratelocationnum IS NULL"; #AND payby != 'COMP' ";
+
 my @taxparam = @base_param;
 
+
 #should i be a cust_main_county method or something
 #need to pass in $taxfromwhere & @taxparam???
 my $_taxamount_sub = sub {
@@ -483,6 +498,21 @@ my $_taxamount_sub = sub {
   scalar_sql($r, \@taxparam, $sql );
 };
 
+my $_creditamount_sub = sub {
+  my $r = shift;
+
+  #match itemdesc if necessary!
+  my $named_tax =
+    $r->taxname
+      ? 'AND itemdesc = '. dbh->quote($r->taxname)
+      : "AND ( itemdesc IS NULL OR itemdesc = '' OR itemdesc = 'Tax' )";
+
+  my $sql = "SELECT SUM(cust_credit_bill_pkg.amount) ".
+            " $creditfromwhere AND cust_bill_pkg.pkgnum = 0 $named_tax";
+
+  scalar_sql($r, \@taxparam, $sql );
+};
+
 #tax-report_groups filtering
 my($group_op, $group_value) = ( '', '' );
 if ( $cgi->param('report_group') =~ /^(=|!=) (.*)$/ ) {
@@ -503,6 +533,7 @@ my $group_test = sub {
 };
 
 my $tot_tax = 0;
+my $tot_credit = 0;
 #foreach my $label ( keys %regions ) {
 foreach my $r ( qsearch(\%qsearch) ) {
 
@@ -521,6 +552,13 @@ foreach my $r ( qsearch(\%qsearch) ) {
   $regions{$label}->{'tax'} += $x;
   $tot_tax += $x unless $cgi->param('show_taxclasses');
 
+  ## calculate credit for this region
+
+  $x = &{$_creditamount_sub}($r);
+
+  $regions{$label}->{'credit'} += $x;
+  $tot_credit += $x unless $cgi->param('show_taxclasses');
+
 }
 
 my %base_regions = ();
@@ -541,6 +579,14 @@ if ( $cgi->param('show_taxclasses') ) {
 
     $base_regions{$base_label}->{'tax'} += $x;
     $tot_tax += $x;
+
+    ## calculate credit for this region
+
+    $x = &{$_creditamount_sub}($r);
+
+    $base_regions{$base_label}->{'credit'} += $x;
+    $tot_credit += $x;
+
   }
 
 }
@@ -553,7 +599,7 @@ my @regions = keys %regions;
 
 #calculate totals
 my( $total, $tot_taxable, $tot_owed ) = ( 0, 0, 0 );
-my( $exempt_cust, $exempt_pkg, $exempt_monthly ) = ( 0, 0, 0 );
+my( $exempt_cust, $exempt_pkg, $exempt_monthly, $tot_credit ) = ( 0, 0, 0, 0 );
 my %taxclasses = ();
 my %county = ();
 my %state = ();
@@ -565,6 +611,7 @@ foreach (@regions) {
   $exempt_cust    += $regions{$_}->{'exempt_cust'};
   $exempt_pkg     += $regions{$_}->{'exempt_pkg'};
   $exempt_monthly += $regions{$_}->{'exempt_monthly'};
+  $tot_credit     += $regions{$_}->{'credit'};
   $taxclasses{$regions{$_}->{'taxclass'}} = 1
     if $regions{$_}->{'taxclass'};
   $county{$regions{$_}->{'county'}} = 1;
@@ -620,6 +667,7 @@ push @regions, {
   'rate'           => '',
   'owed'           => $tot_owed,
   'tax'            => $tot_tax,
+  'credit'         => $tot_credit,
 };
 
 #--