include discounts in gross sales reports, #25943

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

diff --git a/FS/FS/Report/Table.pm b/FS/FS/Report/Table.pm
index 934287a..eef983d 100644 (file)
--- a/FS/FS/Report/Table.pm
+++ b/FS/FS/Report/Table.pm
@@ -266,6 +266,36 @@ sub netrefunds {
   );
 }
 
+=item discounted: The sum of discounts on invoices in the period.
+
+=cut
+
+sub discounted {
+  my( $self, $speriod, $eperiod, $agentnum, %opt) = @_;
+  $self->scalar_sql('SELECT SUM(cust_bill_pkg_discount.amount)
+    FROM cust_bill_pkg_discount
+      JOIN cust_bill_pkg USING  ( billpkgnum )
+      JOIN cust_bill     USING  ( invnum )
+      JOIN cust_main     USING  ( custnum )
+    WHERE '. $self->in_time_period_and_agent( $speriod,
+                                              $eperiod,
+                                              $agentnum,
+                                              'cust_bill._date'
+                                            ).
+              $self->for_opts(%opt)
+  );
+}
+
+=item gross: invoiced + discounted
+
+=cut
+
+sub gross {
+  my( $self, $speriod, $eperiod, $agentnum, %opt) = @_;
+    $self->invoiced(   $speriod, $eperiod, $agentnum, %opt)
+  + $self->discounted( $speriod, $eperiod, $agentnum, %opt);
+}
+
 #XXX docs
 
 #these should be auto-generated or $AUTOLOADed or something
@@ -495,12 +525,17 @@ sub _cust_bill_pkg_recurring {
 
   my @where = (
     '(pkgnum != 0 OR feepart IS NOT NULL)',
-    $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
     $self->with_report_option(%opt),
     $self->with_refnum(%opt),
     $self->with_cust_classnum(%opt)
   );
 
+  my $where_classnum = $self->with_classnum($opt{'classnum'}, $opt{'use_override'});
+  if ($opt{'project'}) {
+    $where_classnum =~ s/\bcust_bill_pkg/v_cust_bill_pkg/g;
+  }
+  push @where, $where_classnum;
+
   if ( $opt{'distribute'} ) {
     $where[0] = 'pkgnum != 0'; # specifically exclude fees
     push @where, "cust_main.agentnum = $agentnum" if $agentnum;
@@ -886,14 +921,24 @@ sub with_classnum {
   @$classnum = grep /^\d+$/, @$classnum;
   my $in = 'IN ('. join(',', @$classnum). ')';
 
-  my $expr = "
-         ( COALESCE(part_pkg.classnum, 0) $in AND pkgpart_override IS NULL)
-      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 )";
+    # then include packages if their base package is in the set and they are 
+    # not overridden,
+    # or if they are overridden and their override package is in the set,
+    # or fees if they are in the set
+    return "(
+         ( COALESCE(part_pkg.classnum, 0) $in AND cust_pkg.pkgpart IS NOT NULL AND pkgpart_override IS NULL )
+      OR ( COALESCE(override.classnum, 0) $in AND pkgpart_override IS NOT NULL )
+      OR ( COALESCE(part_fee.classnum, 0) $in AND cust_bill_pkg.feepart IS NOT NULL )
+    )";
+  } else {
+    # include packages if their base package is in the set,
+    # or fees if they are in the set
+    return "(
+         ( COALESCE(part_pkg.classnum, 0) $in AND cust_pkg.pkgpart IS NOT NULL )
+      OR ( COALESCE(part_fee.classnum, 0) $in AND cust_bill_pkg.feepart IS NOT NULL )
+    )";
   }
-  "( $expr )";
 }
 
 sub with_usageclass {