RT#25026: Option to include taxes in sales report

4 files changed, 49 insertions, 20 deletions

diff --git a/FS/FS/Report/Table.pm b/FS/FS/Report/Table.pm
index 63e5318..4b1ad05 100644
--- a/FS/FS/Report/Table.pm
+++ b/FS/FS/Report/Table.pm
@@ -485,9 +485,9 @@ sub cust_pkg_recur_cost {
 
 =item cust_bill_pkg: the total package charges on invoice line items.
 
-'charges': limit the type of charges included (setup, recur, usage, discount).
-Should be a string containing one or more of 'S', 'R', 'U', or 'D'; if 
-unspecified, defaults to all three.
+'charges': limit the type of charges included (setup, recur, usage, discount, taxes).
+Should be a string containing one or more of 'S', 'R', or 'U'; or 'D' or 'T' (discount
+and taxes should not be combined with the others.)  If unspecified, defaults to 'SRU'.
 
 'classnum': limit to this package class.
 
@@ -517,6 +517,7 @@ sub cust_bill_pkg {
   $sum += $self->cust_bill_pkg_recur(@_) if $charges{R};
   $sum += $self->cust_bill_pkg_detail(@_) if $charges{U};
   $sum += $self->cust_bill_pkg_discount(@_) if $charges{D};
+  $sum += $self->cust_bill_pkg_taxes(@_) if $charges{T};
 
   if ($opt{'average_per_cust_pkg'}) {
     my $count = $self->cust_bill_pkg_count_pkgnum(@_);
@@ -727,6 +728,31 @@ sub cust_bill_pkg_discount {
   $self->scalar_sql($total_sql);
 }
 
+sub cust_bill_pkg_taxes {
+  my $self = shift;
+  my ($speriod, $eperiod, $agentnum, %opt) = @_;
+
+  $agentnum ||= $opt{'agentnum'};
+
+  my @where = (
+    '(cust_bill_pkg.pkgnum != 0 OR feepart IS NOT NULL)',
+    $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
+    $self->with_report_option(%opt),
+    $self->in_time_period_and_agent($speriod, $eperiod, $agentnum),
+    $self->with_refnum(%opt),
+    $self->with_cust_classnum(%opt)
+  );
+
+  my $total_sql = "SELECT COALESCE(SUM(cust_bill_pkg_tax_location.amount),0)
+    FROM cust_bill_pkg
+    $cust_bill_pkg_join
+    LEFT JOIN cust_bill_pkg_tax_location 
+      ON (cust_bill_pkg.billpkgnum = cust_bill_pkg_tax_location.taxable_billpkgnum)
+    WHERE " . join(' AND ', grep $_, @where);
+
+  $self->scalar_sql($total_sql);
+}
+
 ##### package churn report #####
 
 =item active_pkg: The number of packages that were active at the start of 
diff --git a/httemplate/graph/cust_bill_pkg.cgi b/httemplate/graph/cust_bill_pkg.cgi
index b5486f4..83eb0e8 100644
--- a/httemplate/graph/cust_bill_pkg.cgi
+++ b/httemplate/graph/cust_bill_pkg.cgi
@@ -1,5 +1,4 @@
 <% include('elements/monthly.html',
-   #Dumper(
                 'title'        => $title,
                 'graph_type'   => $graph_type,
                 'items'        => \@items,
@@ -28,6 +27,7 @@ my $bottom_link = "$link;";
 my $use_usage = $cgi->param('use_usage') || 0;
 my $use_setup = $cgi->param('use_setup') || 0;
 my $use_discount = $cgi->param('use_discount') || 2;
+my $use_taxes = $cgi->param('use_taxes') || 0;
 
 my $use_override         = $cgi->param('use_override')         ? 1 : 0;
 my $average_per_cust_pkg = $cgi->param('average_per_cust_pkg') ? 1 : 0;
@@ -50,6 +50,7 @@ my %charge_labels = (
   'R'  => 'recurring',
   'U'  => 'usage',
   'D'  => 'discount',
+  'T'  => 'taxes',
 );
 
 #XXX or virtual
@@ -194,8 +195,14 @@ if ( $use_discount == 1 ) {
   push @components, 'D';
 } # else leave discounts off entirely; never combine them with setup/recur
 
+# could in theory combine with setup/recur/usage,
+# but would require reverse engineering the tax calculation
+if ( $use_taxes == 1 ) {
+  push @components, 'T';
+} 
+
 # Categorization of line items goes
-# Agent -> Referral -> Package class -> Component (setup/recur/usage)
+# Agent -> Referral -> Package class -> Component (setup/recur/usage/discount/taxes)
 # If per-agent totals are enabled, they go under the Agent level.
 # There aren't any other kinds of subtotals.
 
@@ -255,6 +262,8 @@ foreach my $agent ( $all_agent || $sel_agent || $FS::CurrentUser::CurrentUser->a
         if ( $component eq 'D' ) {
           # discounts ignore 'charges' and 'distribute'
           $row_link = "${p}search/cust_bill_pkg_discount.html?";
+        } elsif ( $component eq 'T' ) {
+          $row_link = "${p}search/cust_bill_pkg.cgi?istax=1;";
         }
 
         $row_link .=  ($all_agent ? '' : "agentnum=$row_agentnum;").
@@ -314,6 +323,8 @@ foreach my $agent ( $all_agent || $sel_agent || $FS::CurrentUser::CurrentUser->a
           if ( $component eq 'D' ) {
             # discounts ignore 'charges' and 'distribute'
             $row_link ="${p}search/cust_bill_pkg_discount.html?";
+          } elsif ( $component eq 'T' ) {
+            $row_link = "${p}search/cust_bill_pkg.cgi?istax=1;";
           }
 
           $row_link .= ($all_agent ? '' : "agentnum=$row_agentnum;").
@@ -386,9 +397,8 @@ foreach my $agent ( $all_agent || $sel_agent || $FS::CurrentUser::CurrentUser->a
 
   $anum++;
 
-}
+} # foreach $agent
 
-#use Data::Dumper;
 if ( $cgi->param('debug') == 1 ) {
   $FS::Report::Table::DEBUG = 1;
 }
diff --git a/httemplate/graph/report_cust_bill_pkg.html b/httemplate/graph/report_cust_bill_pkg.html
index 1db86e3..96bfdc0 100644
--- a/httemplate/graph/report_cust_bill_pkg.html
+++ b/httemplate/graph/report_cust_bill_pkg.html
@@ -165,7 +165,6 @@ window.onload = class_mode_changed;
 
 </TR>
 
-
 <TR>
   <TH CLASS="background" COLSPAN=2>&nbsp;</TH>
 </TR>
@@ -196,6 +195,12 @@ window.onload = class_mode_changed;
     'options' => [ 1, 2 ],
     'labels'  => { 1 => 'Separate', 2 => 'Do not show' },
 &>
+<& /elements/tr-select.html,
+    'label'   => 'Taxes',
+    'field'   => 'use_taxes',
+    'options' => [ 1, 2 ],
+    'labels'  => { 1 => 'Separate', 2 => 'Do not show' },
+&>
 
 <TR>
   <TD ALIGN="right">Colors</TD>
diff --git a/httemplate/search/cust_bill_pkg.cgi b/httemplate/search/cust_bill_pkg.cgi
index 82e87fb..c59a6d0 100644
--- a/httemplate/search/cust_bill_pkg.cgi
+++ b/httemplate/search/cust_bill_pkg.cgi
@@ -659,18 +659,6 @@ if ( $cgi->param('nottax') ) {
 
   } #end of "normal case"
 
-  # classnum (of underlying package)
-  # not specified: all classes
-  # 0: empty class
-  # N: classnum
-  if ( grep { $_ eq 'classnum' } $cgi->param ) {
-    my @classnums = grep /^\d+$/, $cgi->param('classnum');
-    push @where, "COALESCE(part_fee.classnum, $part_pkg.classnum, 0) IN ( ".
-                     join(',', @classnums ).
-                 ' )'
-      if @classnums;
-  }
-
 } # nottax / istax