X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=FS%2FFS%2FReport%2FTable.pm;h=d7b100df6c06a2080261cf378696e075d9f25884;hb=103dfde149eec5034696d073253255f508a1be78;hp=0c4d9bfa6b664393ba4102de044b72a87f732f29;hpb=ef7e87f054b8dc7c68caa020ddc9876b4e09641f;p=freeside.git

diff --git a/FS/FS/Report/Table.pm b/FS/FS/Report/Table.pm
index 0c4d9bfa6..d7b100df6 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) = @_;
@@ -913,12 +973,15 @@ sub revenue_pkg {
 
   my $sql;
 
+## if package has changed and has not reached next due date it will not be in h_cust_bill.
+## this causes problems with future months, needed to use change_pkgnum instead.
+
   if ($status eq "active") {
     $sql = "SELECT DISTINCT ON (revenue.pkgnum) revenue.pkgnum AS pkgnum, revenue.recur AS revenue
       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 < $speriod )
+      JOIN h_cust_bill_pkg AS revenue ON ((cust_pkg.pkgnum = revenue.pkgnum OR cust_pkg.change_pkgnum = revenue.pkgnum) AND cust_pkg.history_date < $speriod )
     ";
   }
   elsif ($status eq "setup") {
@@ -1055,7 +1118,7 @@ sub calculate_churn_cust {
                                                       as suspended,
            SUM((s_active = 0 and s_suspended > 0 and e_active > 0)::int)
                                                       as resumed,
-           SUM((s_active > 0 and e_active = 0 and e_suspended = 0)::int)
+           SUM((e_active = 0 and e_cancelled > s_cancelled)::int)
                                                       as cancelled
     FROM ($cust_sql) AS x
   ";