RT# 79353 Update discount graph - include waived setup fees

[freeside.git] / FS / FS / Report / Table.pm

diff --git a/FS/FS/Report/Table.pm b/FS/FS/Report/Table.pm
index e3e0854..cef7813 100644 (file)
--- a/FS/FS/Report/Table.pm
+++ b/FS/FS/Report/Table.pm
@@ -415,6 +415,18 @@ sub _subtract_11mo {
   timelocal($sec,$min,$hour,$mday,$mon,$year);
 }
 
+=item _subtract_months: subtracts the number of months from a given unix date stamp
+
+=cut
+
+sub _subtract_months {
+  my($self, $number_of_months, $time) = @_;
+  my ($sec,$min,$hour,$mday,$mon,$year) = (localtime($time) )[0,1,2,3,4,5];
+  $mon -= $number_of_months;
+  if ( $mon < 0 ) { $mon+=12; $year--; }
+  timelocal($sec,$min,$hour,$mday,$mon,$year);
+}
+
 =item cust_pkg_setup_cost: The total setup costs of packages setup in the period
 
 'classnum': limit to this package class.
@@ -733,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) = @_;
@@ -758,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) = @_;
@@ -867,7 +939,24 @@ sub total_revenue_pkg {
   my $self = shift;
   my $active_revenue = $self->revenue_pkg('active', @_);
   my $setup_revenue = $self->revenue_pkg('setup', @_);
-  my $return = sprintf("%.2f", $active_revenue + $setup_revenue);
+  my $return = $active_revenue + $setup_revenue;
+
+  return $return;
+}
+
+sub total_revenue_diff {
+  my $self = shift;
+
+  my @current_month = @_;
+  my @previous_month = @current_month;
+
+  $previous_month[0] = $self->_subtract_months(1,$current_month[0]);
+  $previous_month[1] = $self->_subtract_months(1,$current_month[1]);
+
+  my $previous_revenue = $self->revenue_pkg('active', @previous_month) + $self->revenue_pkg('setup', @previous_month);
+  my $current_revenue  = $self->revenue_pkg('active', @current_month) + $self->revenue_pkg('setup', @current_month);
+
+  my $return = $current_revenue - $previous_revenue;
 
   return $return;
 }
@@ -889,7 +978,7 @@ sub revenue_pkg {
       FROM $from
       JOIN part_pkg ON (cust_pkg.pkgpart = part_pkg.pkgpart)
       JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
-      JOIN h_cust_bill_pkg AS revenue ON (cust_pkg.pkgnum = revenue.pkgnum AND cust_pkg.history_date + 5 > revenue.history_date)
+      JOIN h_cust_bill_pkg AS revenue ON (cust_pkg.pkgnum = revenue.pkgnum AND cust_pkg.history_date < $speriod )
     ";
   }
   elsif ($status eq "setup") {
@@ -897,7 +986,8 @@ sub revenue_pkg {
       FROM $from
       JOIN part_pkg ON (cust_pkg.pkgpart = part_pkg.pkgpart)
       JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
-      JOIN h_cust_bill_pkg AS revenue ON (cust_pkg.pkgnum = revenue.pkgnum AND cust_pkg.setup + 15 > revenue.history_date)
+      JOIN h_cust_bill_pkg AS revenue ON (cust_pkg.pkgnum = revenue.pkgnum AND
+      ( cust_pkg.setup > $speriod AND cust_pkg.setup < $eperiod) )
     ";
   }