fix sales tax report w/part_pkg overrides, RT#6197

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

diff --git a/httemplate/search/cust_bill_pkg.cgi b/httemplate/search/cust_bill_pkg.cgi
index 8654585..52f59de 100644 (file)
--- a/httemplate/search/cust_bill_pkg.cgi
+++ b/httemplate/search/cust_bill_pkg.cgi
@@ -8,7 +8,10 @@
                    '#',
                    'Description',
                    'Setup charge',
-                   'Recurring charge',
+                   ( $use_usage eq 'usage'
+                     ? 'Usage charge'
+                     : 'Recurring charge'
+                   ),
                    'Invoice',
                    'Date',
                    FS::UI::Web::cust_header(),
@@ -22,7 +25,17 @@
                    #strikethrough or "N/A ($amount)" or something these when
                    # they're not applicable to pkg_tax search
                    sub { sprintf($money_char.'%.2f', shift->setup ) },
-                   sub { sprintf($money_char.'%.2f', shift->recur ) },
+                   sub { my $row = shift;
+                         my $value = 0;
+                         if ( $use_usage eq 'recurring' ) {
+                           $value = $row->recur - $row->usage;
+                         } elsif ( $use_usage eq 'usage' ) {
+                           $value = $row->usage;
+                         } else {
+                           $value = $row->recur;
+                         }
+                         sprintf($money_char.'%.2f', $value );
+                       },
                    'invnum',
                    sub { time2str('%b %d %Y', shift->_date ) },
                    \&FS::UI::Web::cust_fields,
@@ -106,34 +119,43 @@ if ( $cgi->param('classnum') =~ /^(\d+)$/ ) {
   }
 }
 
-push @where, ' ( '. join(' OR ',
-                      map ' taxclass = '.dbh->quote($_), $cgi->param('taxclass')
-                    ).
-             ' ) '
-  if $cgi->param('taxclass')
-  && ! $cgi->param('istax'); #no part_pkg.taxclass in this case
-                             #(should we save a taxclass or a link to taxnum
-                             # in cust_bill_pkg or something like
-                             # cust_bill_pkg_tax_location?)
-
-#sub _where {
-# my $table = shift;
-# my $prefix = @_ ? shift : '';
-# "
-#      (    cust_main_county.county  = $table.${prefix}.county
-#      OR ( cust_main_county.county IS NULL AND $table.${prefix}.county  =  '' )
-#      OR ( cust_main_county.county  =  ''  AND $table.${prefix}.county IS NULL)
-#      OR ( cust_main_county.county IS NULL AND $table.${prefix}.county IS NULL)
-#      )
-#  AND (    cust_main_county.state   = $table.${prefix}.state
-#      OR ( cust_main_county.state  IS NULL AND $table.${prefix}.state  =  ''  )
-#      OR ( cust_main_county.state   =  ''  AND $table.${prefix}.state IS NULL )
-#      OR ( cust_main_county.state  IS NULL AND $table.${prefix}.state IS NULL )
-#      )
-#  AND cust_main_county.country = $table.${prefix}.country
-# ";
-#
-#}
+if ( $cgi->param('taxclass')
+     && ! $cgi->param('istax')  #no part_pkg.taxclass in this case
+                                #(should we save a taxclass or a link to taxnum
+                                # in cust_bill_pkg or something like
+                                # cust_bill_pkg_tax_location?)
+   )
+{
+
+  #override taxclass when use_override is specified?  probably
+  #if ( $use_override ) {
+  #
+  #  push @where,
+  #    ' ( '. join(' OR ',
+  #                  map {
+  #                        ' (    part_pkg.taxclass = '. dbh->quote($_).
+  #                        '      AND pkgpart_override IS NULL '.
+  #                        '   OR '.
+  #                        '      override.taxclass = '. dbh->quote($_).
+  #                        '      AND pkgpart_override IS NOT NULL '.
+  #                        ' ) '
+  #                      }
+  #                      $cgi->param('taxclass')
+  #               ).
+  #    ' ) ';
+  #
+  #} else {
+
+    push @where,
+      ' ( '. join(' OR ',
+                    map ' part_pkg.taxclass = '.dbh->quote($_),
+                        $cgi->param('taxclass')
+                 ).
+      ' ) ';
+
+  #}
+
+}
 
 if ( $cgi->param('out') ) {
 
@@ -304,6 +326,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') ) {
 
@@ -368,8 +392,15 @@ if ( $cgi->param('pkg_tax') ) {
 
 } else {
 
-  $count_query =
-    "SELECT COUNT(*), SUM(cust_bill_pkg.setup + cust_bill_pkg.recur)";
+  $count_query = "SELECT COUNT(*), ";
+
+  if ( $use_usage eq 'recurring' ) {
+    $count_query .= "SUM(setup + recur - usage)";
+  } elsif ( $use_usage eq 'usage' ) {
+    $count_query .= "SUM(usage)";
+  } else {
+    $count_query .= "SUM(cust_bill_pkg.setup + cust_bill_pkg.recur)";
+  }
 
 }
 
@@ -414,7 +445,17 @@ if ( $cgi->param('nottax') ) {
 
 }
 
-$count_query .= " FROM cust_bill_pkg $join_cust $join_pkg $where";
+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_bill_pkg $join_cust $join_pkg $where";
+}
+warn "count_query is $count_query\n";
 
 my @select = (
                'cust_bill_pkg.*',