add net credits to sales/credits/receipts report

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

diff --git a/FS/FS/Report/Table/Monthly.pm b/FS/FS/Report/Table/Monthly.pm
index a44cafe..7463620 100644 (file)
--- a/FS/FS/Report/Table/Monthly.pm
+++ b/FS/FS/Report/Table/Monthly.pm
@@ -228,6 +228,21 @@ sub credits {
   );
 }
 
+sub netcredits {
+  my( $self, $speriod, $eperiod, $agentnum ) = @_;
+  $self->scalar_sql("
+    SELECT SUM(cust_credit_bill.amount)
+      FROM cust_credit_bill
+        LEFT JOIN cust_bill USING ( invnum  )
+        LEFT JOIN cust_main USING ( custnum )
+      WHERE ". $self->in_time_period_and_agent( $speriod,
+                                                $eperiod,
+                                                $agentnum,
+                                                'cust_bill._date'
+                                              )
+  );
+}
+
 #these should be auto-generated or $AUTOLOADed or something
 sub invoiced_12mo {
   my( $self, $speriod, $eperiod, $agentnum ) = @_;
@@ -331,11 +346,12 @@ sub in_time_period_and_agent {
   my $sql = "$col >= $speriod AND $col < $eperiod";
 
   #agent selection
-  $sql .= " AND agentnum = $agentnum"
+  $sql .= " AND cust_main.agentnum = $agentnum"
     if $agentnum;
 
   #agent virtualization
-  $sql .= ' AND '. $FS::CurrentUser::CurrentUser->agentnums_sql;
+  $sql .= ' AND '.
+          $FS::CurrentUser::CurrentUser->agentnums_sql( 'table'=>'cust_main' );
 
   $sql;
 }