X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=httemplate%2Fsearch%2Fcust_credit_bill_pkg.html;h=f34603508c5969b7d5d3589bb1c36da5ca077d9c;hb=636dd1f25af52d35efe7a323a5765ff5adeadf83;hp=52e0ac6fee9cf2cc4ffdcb3cae634a3dba2c90f2;hpb=3fa2bc364fc6810b7ce8a02d27e7062ff850ee9d;p=freeside.git

diff --git a/httemplate/search/cust_credit_bill_pkg.html b/httemplate/search/cust_credit_bill_pkg.html
index 52e0ac6fe..f34603508 100644
--- a/httemplate/search/cust_credit_bill_pkg.html
+++ b/httemplate/search/cust_credit_bill_pkg.html
@@ -38,6 +38,8 @@
                    sub { time2str('%b %d %Y', shift->_date ) },
                    \&FS::UI::Web::cust_fields,
                  ],
+                 #'sort_fields' => [ #XXX add
+                 #],
                  'links'       => [
                    '',
                    '',
@@ -92,9 +94,6 @@ my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
 push @where, "cust_bill._date >= $beginning",
              "cust_bill._date <= $ending";
 
-push @where , " payby != 'COMP' "
-  unless $cgi->param('include_comp_cust');
-
 if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
   push @where, "cust_main.agentnum = $1";
 }
@@ -336,83 +335,8 @@ if ( $cgi->param('cust_tax') ) {
   push @where, $cust_exempt;
 }
 
-my $use_usage = $cgi->param('use_usage');
-
-my $count_query;
-if ( $cgi->param('pkg_tax') ) { #does this mean anything here?
-
-  $count_query =
-    "SELECT COUNT(*),
-            SUM(
-                 ( CASE WHEN part_pkg.setuptax = 'Y'
-                        THEN cust_bill_pkg.setup
-                        ELSE 0
-                   END
-                 )
-                 +
-                 ( CASE WHEN part_pkg.recurtax = 'Y'
-                        THEN cust_bill_pkg.recur
-                        ELSE 0
-                   END
-                 )
-               )
-    ";
-
-  push @where, "(    ( part_pkg.setuptax = 'Y' AND cust_bill_pkg.setup > 0 )
-                  OR ( part_pkg.recurtax = 'Y' AND cust_bill_pkg.recur > 0 ) )",
-               "( tax != 'Y' OR tax IS NULL )";
-
-} elsif ( $cgi->param('taxable') ) { #again, meaningful?
-
-  my $setup_taxable = "(
-    CASE WHEN part_pkg.setuptax = 'Y'
-         THEN 0
-         ELSE cust_bill_pkg.setup
-    END
-  )";
-
-  my $recur_taxable = "(
-    CASE WHEN part_pkg.recurtax = 'Y'
-         THEN 0
-         ELSE cust_bill_pkg.recur
-    END
-  )";
-
-  my $exempt = "(
-    SELECT COALESCE( SUM(amount), 0 ) FROM cust_tax_exempt_pkg
-      WHERE cust_tax_exempt_pkg.billpkgnum = cust_bill_pkg.billpkgnum
-  )";
-
-  $count_query =
-    "SELECT COUNT(*), SUM( $setup_taxable + $recur_taxable - $exempt )";
-
-  push @where,
-    #not tax-exempt package (setup or recur)
-    "(
-          ( ( part_pkg.setuptax != 'Y' OR part_pkg.setuptax IS NULL )
-            AND cust_bill_pkg.setup > 0 )
-       OR
-          ( ( part_pkg.recurtax != 'Y' OR part_pkg.recurtax IS NULL )
-            AND cust_bill_pkg.recur > 0 )
-    )",
-    #not a tax_exempt customer
-    "( tax != 'Y' OR tax IS NULL )";
-    #not covered in full by a monthly tax exemption (texas tax)
-    "0 < ( $setup_taxable + $recur_taxable - $exempt )",
-
-} else {
-
-  $count_query = "SELECT COUNT(*), ";
-
-  if ( $use_usage eq 'recurring' ) { #mean anything?
-    $count_query .= "SUM(setup + recur - usage)";
-  } elsif ( $use_usage eq 'usage' ) { #mean anything?
-    $count_query .= "SUM(usage)";
-  } else {
-    $count_query .= "SUM(cust_credit_bill_pkg.amount)";
-  }
-
-}
+my $count_query = "SELECT COUNT(DISTINCT billpkgnum),
+                          SUM(cust_credit_bill_pkg.amount)";
 
 my $join_cust =
   '      JOIN cust_bill ON ( cust_bill_pkg.invnum = cust_bill.invnum ) 
@@ -421,7 +345,18 @@ my $join_cust =
 
 my $join_pkg;
 
-my $join_cust_bill_pkg = 'LEFT JOIN cust_bill_pkg USING ( billpkgnum ';
+my $join_cust_bill_pkg = 'LEFT JOIN cust_bill_pkg USING ( billpkgnum';
+if ( $cgi->param('iscredit') eq 'rate' ) {
+  $join_pkg .= ', billpkgtaxratelocationnum )';
+} elsif ( $conf->exists('tax-pkg_address') ) {
+  $join_pkg .= ', billpkgtaxlocationnum )';
+  push @where, "billpkgtaxratelocationnum IS NULL";
+} else {
+  $join_pkg .= ' )';
+  push @where, "billpkgtaxratelocationnum IS NULL";
+}
+
+ )';
 
 if ( $cgi->param('nottax') ) {
 
@@ -435,29 +370,22 @@ if ( $cgi->param('nottax') ) {
 } elsif ( $cgi->param('istax') ) {
 
   #false laziness w/report_tax.cgi $taxfromwhere
-  if ( $conf->exists('tax-pkg_address') ) {
+  if ( 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 ) ';
+
+  } elsif ( $conf->exists('tax-pkg_address') ) {
+
     $join_pkg .= ' LEFT JOIN cust_bill_pkg_tax_location USING ( billpkgnum )
                    LEFT JOIN cust_location              USING ( locationnum ) ';
 
     #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 ( $conf->exists('tax-pkg_address') ) {
-      $join_cust_bill_pkg .= ', billpkgtaxlocationnum )';
-      push @where, "billpkgtaxratelocationnum IS NULL";
-    #} elsif ( $cgi->param('iscredit') eq 'rate' ) {
-    #  $join_pkg .= ', billpkgtaxratelocationnum )';
-    } else {
-      $join_cust_bill_pkg .= ' )';
-      push @where, "billpkgtaxratelocationnum IS NULL";
-    }
-
 } else { 
 
   #die?
@@ -473,21 +401,12 @@ my $where = ' WHERE '. join(' AND ', @where);
 my $join_credit = ' LEFT JOIN cust_credit_bill USING ( creditbillnum )
                     LEFT JOIN cust_credit      USING ( crednum ) ';
 
-#if ($use_usage) {
-#  $count_query .=
-#    " FROM (SELECT cust_bill_pkg.setup, cust_bill_pkg.recur, 
-#             ( SELECT COALESCE( SUM(amount), 0 ) FROM cust_bill_pkg_detail
-#               WHERE cust_bill_pkg.billpkgnum = cust_bill_pkg_detail.billpkgnum
-#             ) AS usage FROM cust_bill_pkg  $join_cust $join_pkg $where
-#           ) AS countquery";
-#} else {
-  $count_query .= " FROM cust_credit_bill_pkg
-                        $join_pkg
-                        $join_cust_bill_pkg
-                        $join_credit
-                        $join_cust
-                      $where";
-#}
+$count_query .= " FROM cust_credit_bill_pkg
+                         $join_pkg
+                         $join_cust_bill_pkg
+                         $join_credit
+                         $join_cust
+                       $where";
 
 my @select = ( 'cust_credit_bill_pkg.*',
                'cust_bill_pkg.*',