X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=FS%2FFS%2FReport%2FTable.pm;h=e3e0854ecef4cfdc81af30865a2389497ca76ef5;hb=f654e068b6e7be55bdbd749293c1bda7737cf870;hp=ffa11724041c56e92cf370bad9bd12bd39f9b9e0;hpb=edba2cf4853c062f7f8bf4a41cd429559b548ffc;p=freeside.git

diff --git a/FS/FS/Report/Table.pm b/FS/FS/Report/Table.pm
index ffa117240..e3e0854ec 100644
--- a/FS/FS/Report/Table.pm
+++ b/FS/FS/Report/Table.pm
@@ -229,7 +229,8 @@ sub receipts { #net payments
   my $sql = 'SELECT SUM(cust_bill_pay.amount) FROM cust_bill_pay';
   if ( $opt{'setuprecur'} ) {
     $sql = 'SELECT SUM('.
-            FS::cust_bill_pkg->paid_sql($speriod, $eperiod, %opt).
+            #in practice, but not appearance, paid_sql accepts end before start
+            FS::cust_bill_pkg->paid_sql($eperiod, $speriod, %opt).
            ') FROM cust_bill_pkg';
   }
 
@@ -275,6 +276,11 @@ sub discounted {
 
   my $sql = 'SELECT SUM(';
   if ($opt{'setuprecur'}) {
+    # (This isn't exact but it works in most cases.)
+    # When splitting into setup/recur values, 
+    # if the discount is allowed to apply to setup fees (discount.setup = 'Y')
+    # then split it between the "setup" and "recurring" rows in proportion to 
+    # the "unitsetup" and "unitrecur" fields of the line item. 
     $sql .= <<EOF;
 CASE
   WHEN discount.setup = 'Y' 
@@ -291,11 +297,15 @@ EOF
     }
     $sql .= ' / (COALESCE(cust_bill_pkg.unitsetup,0) + COALESCE(cust_bill_pkg.unitrecur,0)))';
     $sql .= " * cust_bill_pkg_discount.amount\n";
-    $sql .= <<EOF;
-  ELSE cust_bill_pkg_discount.amount
-END
-EOF
+    # Otherwise, show it all as "recurring"
+    if ($opt{'setuprecur'} eq 'setup') {
+      $sql .= "  ELSE 0\n";
+    } elsif ($opt{'setuprecur'} eq 'recur') {
+      $sql .= "  ELSE cust_bill_pkg_discount.amount\n";
+    }
+    $sql .= "END\n";
   } else {
+    # simple case, no setuprecur
     $sql .= "cust_bill_pkg_discount.amount\n";
   }
   $sql .= <<EOF;
@@ -475,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.
 
@@ -507,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(@_);
@@ -588,6 +599,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
@@ -595,6 +610,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) = @_;
@@ -621,9 +646,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);
 }
@@ -639,10 +666,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 {
@@ -675,7 +706,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
@@ -685,8 +725,9 @@ 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);
   
@@ -717,6 +758,58 @@ 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);
+}
+
+#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 
@@ -770,6 +863,54 @@ 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 = sprintf("%.2f", $active_revenue + $setup_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 ($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 + 5 > revenue.history_date)
+    ";
+  }
+  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 + 15 > revenue.history_date)
+    ";
+  }
+
+  $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 ) = @_;