Merge branch 'master' of https://github.com/jgoodman/Freeside

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

diff --git a/FS/FS/Report/Table.pm b/FS/FS/Report/Table.pm
index 7f59384..17b12ae 100644 (file)
--- a/FS/FS/Report/Table.pm
+++ b/FS/FS/Report/Table.pm
@@ -141,7 +141,7 @@ sub payments {
 sub credits {
   my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
   $self->scalar_sql("
-    SELECT SUM(amount)
+    SELECT SUM(cust_credit.amount)
       FROM cust_credit
         LEFT JOIN cust_main USING ( custnum )
       WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum).
@@ -390,9 +390,6 @@ unspecified, defaults to all three.
 'use_override': for line items generated by an add-on package, use the class
 of the add-on rather than the base package.
 
-'freq': limit to packages with this frequency.  Currently uses the part_pkg 
-frequency, so term discounted packages may give odd results.
-
 'distribute': for non-monthly recurring charges, ignore the invoice 
 date.  Instead, consider the line item's starting/ending dates.  Determine 
 the fraction of the line item duration that falls within the specified 
@@ -421,7 +418,8 @@ my $cust_bill_pkg_join = '
     LEFT JOIN cust_main USING ( custnum )
     LEFT JOIN cust_pkg USING ( pkgnum )
     LEFT JOIN part_pkg USING ( pkgpart )
-    LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart';
+    LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart
+    LEFT JOIN part_fee USING ( feepart )';
 
 sub cust_bill_pkg_setup {
   my $self = shift;
@@ -434,7 +432,7 @@ sub cust_bill_pkg_setup {
   $agentnum ||= $opt{'agentnum'};
 
   my @where = (
-    'pkgnum != 0',
+    '(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),
@@ -461,7 +459,7 @@ sub cust_bill_pkg_recur {
   my $cust_bill_pkg = $opt{'project'} ? 'v_cust_bill_pkg' : 'cust_bill_pkg';
 
   my @where = (
-    'pkgnum != 0',
+    '(pkgnum != 0 OR feepart IS NOT NULL)',
     $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
     $self->with_report_option(%opt),
   );
@@ -476,13 +474,14 @@ sub cust_bill_pkg_recur {
     $item_usage = 'usage'; #already calculated
   }
   else {
-    $item_usage = '( SELECT COALESCE(SUM(amount),0)
+    $item_usage = '( SELECT COALESCE(SUM(cust_bill_pkg_detail.amount),0)
       FROM cust_bill_pkg_detail
       WHERE cust_bill_pkg_detail.billpkgnum = cust_bill_pkg.billpkgnum )';
   }
   my $recur_fraction = '';
 
   if ( $opt{'distribute'} ) {
+    $where[0] = 'pkgnum != 0'; # specifically exclude fees
     push @where, "cust_main.agentnum = $agentnum" if $agentnum;
     push @where,
       "$cust_bill_pkg.sdate <  $eperiod",
@@ -521,7 +520,8 @@ Arguments as for C<cust_bill_pkg>, plus:
 sub cust_bill_pkg_detail {
   my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
 
-  my @where = ( "cust_bill_pkg.pkgnum != 0" );
+  my @where = 
+    ( "(cust_bill_pkg.pkgnum != 0 OR cust_bill_pkg.feepart IS NOT NULL)" );
 
   push @where, 'cust_main.refnum = '. $opt{'refnum'} if $opt{'refnum'};
 
@@ -536,7 +536,9 @@ sub cust_bill_pkg_detail {
     ;
 
   if ( $opt{'distribute'} ) {
-    # then limit according to the usage time, not the billing date
+    # exclude fees
+    $where[0] = 'cust_bill_pkg.pkgnum != 0';
+    # and limit according to the usage time, not the billing date
     push @where, $self->in_time_period_and_agent($speriod, $eperiod, $agentnum,
       'cust_bill_pkg_detail.startdate'
     );
@@ -547,7 +549,7 @@ sub cust_bill_pkg_detail {
     );
   }
 
-  my $total_sql = " SELECT SUM(amount) ";
+  my $total_sql = " SELECT SUM(cust_bill_pkg_detail.amount) ";
 
   $total_sql .=
     " / CASE COUNT(cust_pkg.*) WHEN 0 THEN 1 ELSE COUNT(cust_pkg.*) END "
@@ -561,6 +563,7 @@ 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 );
 
   $self->scalar_sql($total_sql);
@@ -683,14 +686,14 @@ sub with_classnum {
   @$classnum = grep /^\d+$/, @$classnum;
   my $in = 'IN ('. join(',', @$classnum). ')';
 
-  if ( $use_override ) {
-    "(
+  my $expr = "
          ( COALESCE(part_pkg.classnum, 0) $in AND pkgpart_override IS NULL)
-      OR ( COALESCE(override.classnum, 0) $in AND pkgpart_override IS NOT NULL )
-    )";
-  } else {
-    "COALESCE(part_pkg.classnum, 0) $in";
+      OR ( COALESCE(part_fee.classnum, 0) $in AND feepart IS NOT NULL )";
+  if ( $use_override ) {
+    $expr .= "
+      OR ( COALESCE(override.classnum, 0) $in AND pkgpart_override IS NOT NULL )";
   }
+  "( $expr )";
 }
 
 sub with_usageclass {
@@ -834,7 +837,8 @@ sub init_projection {
       # sdate/edate overlapping the ROI, for performance
       "INSERT INTO v_cust_bill_pkg ( 
         SELECT cust_bill_pkg.*,
-          (SELECT COALESCE(SUM(amount),0) FROM cust_bill_pkg_detail 
+          (SELECT COALESCE(SUM(cust_bill_pkg_detail.amount),0)
+          FROM cust_bill_pkg_detail 
           WHERE cust_bill_pkg_detail.billpkgnum = cust_bill_pkg.billpkgnum),
           cust_bill._date,
           cust_pkg.expire