add ability to search on ranges of charged, owed to adv. invoice report, RT#6407

[freeside.git] / httemplate / search / report_tax.cgi

diff --git a/httemplate/search/report_tax.cgi b/httemplate/search/report_tax.cgi
index ed5ebef..557c29c 100755 (executable)
--- 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>
 
@@ -110,10 +111,18 @@
       </TD>
 
 % unless ( $cgi->param('show_taxclasses') ) { 
+%       my $invlink = $region->{'url_param_inv'}
+%                       ? ';'. $region->{'url_param_inv'}
+%                       : $link;
+
         <<%$tdh%> ALIGN="right">
-          <A HREF="<% $baselink. $link %>;istax=1"
+          <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>
@@ -128,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
@@ -285,6 +295,9 @@ foreach my $r ( qsearch({ 'table'     => 'cust_main_county',
 
   my $label = getlabel($r);
   $regions{$label}->{'label'} = $label;
+
+  $regions{$label}->{$_} = $r->$_() for (qw( county state country )); #taxname?
+
   $regions{$label}->{'url_param'} =
     join(';', map "$_=".uri_escape($r->$_()),
                   qw( county state country taxname )
@@ -348,9 +361,25 @@ foreach my $r ( qsearch({ 'table'     => 'cust_main_county',
 #    );
 #  }
 
+  #false laziness -ish w/report_tax.cgi
+  my $cust_exempt;
+  if ( $r->taxname ) {
+    my $q_taxname = dbh->quote($r->taxname);
+    $cust_exempt =
+      "( tax = 'Y'
+         OR EXISTS ( SELECT 1 FROM cust_main_exemption
+                       WHERE cust_main_exemption.custnum = cust_main.custnum
+                         AND cust_main_exemption.taxname = $q_taxname
+                   )
+       )
+      ";
+  } else {
+    $cust_exempt = " tax = 'Y' ";
+  }
+
   my $x_cust = scalar_sql($r, \@param,
     "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur)
-     $fromwhere AND $nottax AND tax = 'Y' "
+     $fromwhere AND $nottax AND $cust_exempt "
   );
 
   $regions{$label}->{'exempt_cust'} += $x_cust;
@@ -440,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 {
@@ -460,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') =~ /^(=|!=) (.*)$/ ) {
@@ -480,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) ) {
 
@@ -498,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 = ();
@@ -518,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;
+
   }
 
 }
@@ -530,8 +599,11 @@ 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 = ();
+my %country = ();
 foreach (@regions) {
   $total          += $regions{$_}->{'total'};
   $tot_taxable    += $regions{$_}->{'taxable'};
@@ -539,15 +611,36 @@ 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;
+  $state{$regions{$_}->{'state'}} = 1;
+  $country{$regions{$_}->{'country'}} = 1;
 }
 
 my $total_url_param = '';
+my $total_url_param_invoiced = '';
 if ( $group_op ) {
+
+  my @country = keys %country;
+  warn "WARNING: multiple countries on this grouped report; total links broken"
+    if scalar(@country) > 1;
+  my $country = $country[0];
+
+  my @state = keys %state;
+  warn "WARNING: multiple countries on this grouped report; total links broken"
+    if scalar(@state) > 1;
+  my $state = $state[0];
+
+  $total_url_param_invoiced =
   $total_url_param =
     'report_group='.uri_escape("$group_op $group_value").';'.
-    join(';', map 'taxclass='.dbh->quote($_), keys %taxclasses );
+    join(';', map 'taxclass='.uri_escape($_), keys %taxclasses );
+  $total_url_param .= ';'.
+    "country=$country;state=".uri_escape($state).';'.
+    join(';', map 'county='.uri_escape($_), keys %county ) ;
+
 }
 
 #ordering
@@ -565,6 +658,7 @@ my @base_regions =
 push @regions, {
   'label'          => 'Total',
   'url_param'      => $total_url_param,
+  'url_param_inv'  => $total_url_param_invoiced,
   'total'          => $total,
   'exempt_cust'    => $exempt_cust,
   'exempt_pkg'     => $exempt_pkg,
@@ -573,6 +667,7 @@ push @regions, {
   'rate'           => '',
   'owed'           => $tot_owed,
   'tax'            => $tot_tax,
+  'credit'         => $tot_credit,
 };
 
 #--