From c5386b4378bcad35867beabec1c344c0a596aecb Mon Sep 17 00:00:00 2001
From: Mitch Jackson <mitch@freeside.biz>
Date: Sun, 15 Apr 2018 22:48:58 -0500
Subject: [PATCH] RT# 79353 Update discount graph - include waived setup fees

---
 FS/FS/Report/Table.pm                              | 60 ++++++++++++++++++++++
 httemplate/graph/cust_bill_pkg_discount.html       | 11 +++-
 .../graph/report_cust_bill_pkg_discount.html       |  6 +++
 httemplate/search/cust_bill_pkg_discount.html      |  6 ++-
 4 files changed, 80 insertions(+), 3 deletions(-)

diff --git a/FS/FS/Report/Table.pm b/FS/FS/Report/Table.pm
index 0c4d9bfa6..cef7813af 100644
--- a/FS/FS/Report/Table.pm
+++ b/FS/FS/Report/Table.pm
@@ -745,6 +745,12 @@ sub cust_bill_pkg_detail {
   
 }
 
+=item cust_bill_pkg_discount: Discounts issued
+
+Arguments: agentnum, refnum, cust_classnum
+
+=cut
+
 sub cust_bill_pkg_discount {
   my $self = shift;
   my ($speriod, $eperiod, $agentnum, %opt) = @_;
@@ -770,6 +776,60 @@ sub cust_bill_pkg_discount {
   $self->scalar_sql($total_sql);
 }
 
+=item cust_bill_pkg_discount_or_waived: Discounts and waived fees issued
+
+Arguments: agentnum, refnum, cust_classnum
+
+=cut
+
+sub cust_bill_pkg_discount_or_waived {
+
+  my $self = shift;
+  my ($speriod, $eperiod, $agentnum, %opt) = @_;
+
+  $agentnum ||= $opt{'agentnum'};
+
+  my $total_sql = "
+    SELECT
+      COALESCE(
+          SUM(
+            COALESCE(
+              cust_bill_pkg_discount.amount,
+              CAST((  SELECT optionvalue
+                 FROM part_pkg_option
+                 WHERE
+                    part_pkg_option.pkgpart = cust_pkg.pkgpart
+                    AND optionname = 'setup_fee'
+              ) AS NUMERIC )
+            )
+          ),
+          0
+       )
+    FROM cust_bill_pkg
+    LEFT JOIN cust_bill_pkg_discount USING (billpkgnum)
+    LEFT JOIN cust_pkg ON cust_bill_pkg.pkgnum = cust_pkg.pkgnum
+    LEFT JOIN part_pkg USING (pkgpart)
+    LEFT JOIN cust_bill USING ( invnum )
+    LEFT JOIN cust_main ON cust_pkg.custnum = cust_main.custnum
+    WHERE
+    (
+        cust_bill_pkg_discount.billpkgdiscountnum IS NOT NULL
+        OR (
+            cust_pkg.setup = cust_bill_pkg.sdate
+            AND cust_pkg.waive_setup = 'Y'
+        )
+    )
+    AND cust_bill_pkg.pkgpart_override IS NULL
+  " . join "\n",
+      map  { " AND ( $_ ) " }
+      grep { $_ }
+      $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
+      $self->with_report_option(%opt),
+      $self->in_time_period_and_agent($speriod, $eperiod, $agentnum);
+
+  $self->scalar_sql($total_sql);
+}
+
 sub cust_bill_pkg_taxes {
   my $self = shift;
   my ($speriod, $eperiod, $agentnum, %opt) = @_;
diff --git a/httemplate/graph/cust_bill_pkg_discount.html b/httemplate/graph/cust_bill_pkg_discount.html
index 5074aa35c..22327e05b 100644
--- a/httemplate/graph/cust_bill_pkg_discount.html
+++ b/httemplate/graph/cust_bill_pkg_discount.html
@@ -20,7 +20,10 @@
 die "access denied"
   unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
 
+my $include_waived_setup = $cgi->param('include_waived_setup') || 0;
+
 my $link = "${p}search/cust_bill_pkg_discount.html?";
+$link .= "include_waived_setup=Y&" if $include_waived_setup;
 my $bottom_link = $link;
 
 #XXX or virtual
@@ -35,7 +38,8 @@ my $title = $sel_agent ? $sel_agent->agent.' ' : '';
 $title .= 'Discount Overview';
 
 
-my $hue = 0;
+#my $hue = 0; # Start with illegible yellow-on-white
+my $hue = 255; # Start with red-on-white
 #my $hue_increment = 170;
 #my $hue_increment = 145;
 my $hue_increment = 125;
@@ -56,7 +60,10 @@ foreach my $agent ( $sel_agent || qsearch('agent', { 'disabled' => '' } ) ) {
 
   #foreach my $pkg_class ( @pkg_class ) {
 
-      push @items, 'cust_bill_pkg_discount';
+      push @items,
+        $include_waived_setup
+        ? 'cust_bill_pkg_discount_or_waived'
+        : 'cust_bill_pkg_discount';
 
       push @labels,
         ( $sel_agent ? '' : $agent->agent.' ' );
diff --git a/httemplate/graph/report_cust_bill_pkg_discount.html b/httemplate/graph/report_cust_bill_pkg_discount.html
index 36ad78252..094c652c7 100644
--- a/httemplate/graph/report_cust_bill_pkg_discount.html
+++ b/httemplate/graph/report_cust_bill_pkg_discount.html
@@ -19,6 +19,12 @@
        'disable_empty' => 0,
   &>
 
+  <& /elements/tr-checkbox.html,
+     label => 'Include waived setup fees:',
+     field => 'include_waived_setup',
+     value => 'Y',
+  &>
+
 % # anything about line items, discounts or packages really
 % # otaker?
 % # package class?
diff --git a/httemplate/search/cust_bill_pkg_discount.html b/httemplate/search/cust_bill_pkg_discount.html
index f4fbd561b..d31614af3 100644
--- a/httemplate/search/cust_bill_pkg_discount.html
+++ b/httemplate/search/cust_bill_pkg_discount.html
@@ -228,7 +228,11 @@ if ( $cgi->param('usernum') =~ /^(\d+)$/ ) {
 }
 
 # Filter: Include waived setup fees
-if ( !$cgi->param('include_waived_setup') ) {
+if ( $cgi->param('include_waived_setup') ) {
+  # Filter a hidden fee attached to a package with a waived setup fee from
+  # causing the waived-fee for that package to be double-counted
+  push @where, 'cust_bill_pkg.pkgpart_override IS NULL';
+} else {
   push @where, "cust_bill_pkg_discount.pkgdiscountnum IS NOT NULL";
 }
 
-- 
2.11.0