X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=FS%2FFS%2FReport%2FTable.pm;h=d7b100df6c06a2080261cf378696e075d9f25884;hb=e4c1781de43835fea0f2997c35de2395d06f220f;hp=4b1ad05d64a3df262b6fc8020bd9deae70ce34df;hpb=4a6b0868fabbc617f05b1f9981c52b28d3cb2bcb;p=freeside.git

diff --git a/FS/FS/Report/Table.pm b/FS/FS/Report/Table.pm
index 4b1ad05d6..d7b100df6 100644
--- 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.
@@ -599,6 +611,10 @@ sub _cust_bill_pkg_recurring {
       $self->in_time_period_and_agent($speriod, $eperiod, $agentnum, $_date);
   }
 
+  if ( $opt{'custnum'} =~ /^(\d+)$/ ) {
+    push @where, "(cust_main.custnum = $1)";
+  }
+
   return "
   FROM $cust_bill_pkg 
   $cust_bill_pkg_join
@@ -606,6 +622,16 @@ sub _cust_bill_pkg_recurring {
 
 }
 
+=item cust_bill_pkg_recur: the total recur charges
+
+Most arguments as for C<cust_bill_pkg>, plus:
+
+'custnum': limit to this customer
+
+'cost': if true, return total recur costs instead
+
+=cut
+
 sub cust_bill_pkg_recur {
   my $self = shift;
   my ($speriod, $eperiod, $agentnum, %opt) = @_;
@@ -632,9 +658,11 @@ sub cust_bill_pkg_recur {
       ($cust_bill_pkg.edate - $cust_bill_pkg.sdate)";
   }
 
-  my $total_sql = 
-    "SELECT COALESCE(SUM(($cust_bill_pkg.recur - $item_usage) $recur_fraction),0)" .
-    $self->_cust_bill_pkg_recurring(@_);
+  my $total_sql = $opt{'cost'}
+    ? "SELECT SUM(part_pkg.recur_cost)"
+    : "SELECT COALESCE(SUM(($cust_bill_pkg.recur - $item_usage) $recur_fraction),0)";
+
+  $total_sql .= $self->_cust_bill_pkg_recurring(@_);
 
   $self->scalar_sql($total_sql);
 }
@@ -650,10 +678,14 @@ sub cust_bill_pkg_count_pkgnum {
 
 =item cust_bill_pkg_detail: the total usage charges in detail lines.
 
-Arguments as for C<cust_bill_pkg>, plus:
+Most arguments as for C<cust_bill_pkg>, plus:
 
 'usageclass': limit to this usage class number.
 
+'custnum': limit to this customer
+
+'cost': if true, return total usage costs instead
+
 =cut
 
 sub cust_bill_pkg_detail {
@@ -686,7 +718,16 @@ sub cust_bill_pkg_detail {
     );
   }
 
+  if ( $opt{'custnum'} =~ /^(\d+)$/ ) {
+    push @where, "(cust_main.custnum = $1)";
+  }
+
   my $total_sql = " SELECT SUM(cust_bill_pkg_detail.amount) ";
+  my $extra_join = '';
+  if ($opt{'cost'}) {
+    $extra_join = "   JOIN cdr USING ( detailnum ) ";
+    $total_sql  = " SELECT SUM(cdr.rated_cost) ";
+  }
 
   $total_sql .=
     " FROM cust_bill_pkg_detail
@@ -696,13 +737,20 @@ sub cust_bill_pkg_detail {
         LEFT JOIN cust_pkg ON cust_bill_pkg.pkgnum = cust_pkg.pkgnum
         LEFT JOIN part_pkg USING ( pkgpart )
         LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart
-        LEFT JOIN part_fee USING ( feepart )
-      WHERE ".join( ' AND ', grep $_, @where );
+        LEFT JOIN part_fee USING ( feepart ) 
+    ".$extra_join.
+    " WHERE ".join( ' AND ', grep $_, @where );
 
   $self->scalar_sql($total_sql);
   
 }
 
+=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) = @_;
@@ -728,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) = @_;
@@ -753,6 +855,33 @@ sub cust_bill_pkg_taxes {
   $self->scalar_sql($total_sql);
 }
 
+#all credits applied to matching pkg line items (ie not taxes)
+
+sub cust_bill_pkg_credits {
+  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_credit_bill_pkg.amount),0)
+    FROM cust_bill_pkg
+    $cust_bill_pkg_join
+    LEFT JOIN cust_credit_bill_pkg 
+      USING ( 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 
@@ -806,6 +935,75 @@ sub unsusp_pkg {
   $self->churn_pkg('unsusp', @_);
 }
 
+sub total_revenue_pkg {
+  my $self = shift;
+  my $active_revenue = $self->revenue_pkg('active', @_);
+  my $setup_revenue = $self->revenue_pkg('setup', @_);
+  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;
+}
+
+sub revenue_pkg {
+  my $self = shift;
+  my ( $status, $speriod, $eperiod, $agentnum, %opt ) = @_;
+  my $totalrevenue;
+
+  my ($from, @where) =
+    FS::h_cust_pkg->churn_fromwhere_sql( $status, $speriod, $eperiod);
+
+  push @where, $self->pkg_where(%opt, 'agentnum' => $agentnum);
+
+  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 OR cust_pkg.change_pkgnum = revenue.pkgnum) AND cust_pkg.history_date < $speriod )
+    ";
+  }
+  elsif ($status eq "setup") {
+    $sql = "SELECT DISTINCT ON (revenue.pkgnum) revenue.pkgnum AS pkgnum, revenue.setup 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.setup > $speriod AND cust_pkg.setup < $eperiod) )
+    ";
+  }
+
+  $sql .= ' WHERE '.join(' AND ', @where)
+    if scalar(@where);
+
+  $sql .= "ORDER BY revenue.pkgnum ASC, revenue.history_date DESC";
+
+  my $revenue_sql = "SELECT sum(rev.revenue) AS total_revenue FROM ( $sql ) AS rev";
+
+  $self->scalar_sql($revenue_sql);
+}
+
 sub churn_pkg {
   my $self = shift;
   my ( $status, $speriod, $eperiod, $agentnum, %opt ) = @_;
@@ -920,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
   ";