X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=httemplate%2Fsearch%2Fcust_credit_bill_pkg.html;h=5e70c23bdf1d1c6412752da6530e6584166d19c8;hb=cf17ad2a9c82d07412409b6dfea9bfb9be7d830c;hp=b0dbc7000b77715907a016297d3be7bb2ab83608;hpb=e55892bdf6dc95710a19876087690a9664421215;p=freeside.git

diff --git a/httemplate/search/cust_credit_bill_pkg.html b/httemplate/search/cust_credit_bill_pkg.html
index b0dbc7000..5e70c23bd 100644
--- 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,
                               '',
                               '',
@@ -203,6 +210,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 +250,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 +276,23 @@ 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;
   }
    
   my($title, $name);
@@ -381,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 )'.
@@ -393,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
@@ -429,7 +465,7 @@ if ( $cgi->param('nottax') ) {
     s/cust_pkg\.locationnum/cust_bill_pkg_tax_location.locationnum/g for @where;
   }
 
-} else { 
+} else {
 
   #warn "neither nottax nor istax parameters specified";
   #same as before?
@@ -460,11 +496,17 @@ 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 = ();
 my $post_desc_align = '';
-if ( $conf->exists('enable_taxclasses') ) {
+if ( $conf->exists('enable_taxclasses') && ! $cgi->param('istax') ) {
   push @post_desc_header, 'Tax class';
   push @post_desc, 'taxclass';
   push @post_desc_null, '';
@@ -543,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>