agent-specific sales/credit/receipts summary

1 files changed, 61 insertions, 41 deletions

diff --git a/FS/FS/Report/Table/Monthly.pm b/FS/FS/Report/Table/Monthly.pm
index 89d44f92f..9ef108c44 100644
--- a/FS/FS/Report/Table/Monthly.pm
+++ b/FS/FS/Report/Table/Monthly.pm
@@ -45,6 +45,7 @@ sub data {
   my $syear = $self->{'start_year'};
   my $emonth = $self->{'end_month'};
   my $eyear = $self->{'end_year'};
+  my $agentnum = $self->{'agentnum'};
 
   my %data;
 
@@ -59,7 +60,7 @@ sub data {
     push @{$data{eperiod}}, $eperiod;
   
     foreach my $item ( @{$self->{'items'}} ) {
-      push @{$data{$item}}, $self->$item($speriod, $eperiod);
+      push @{$data{$item}}, $self->$item($speriod, $eperiod, $agentnum);
     }
 
   }
@@ -69,53 +70,60 @@ sub data {
 }
 
 sub invoiced { #invoiced
-  my( $self, $speriod, $eperiod ) = ( shift, shift, shift );
+  my( $self, $speriod, $eperiod, $agentnum ) = @_;
   $self->scalar_sql("
-    SELECT SUM(charged) FROM cust_bill
-    WHERE ". $self->in_time_period($speriod, $eperiod)
+    SELECT SUM(charged)
+      FROM cust_bill
+        LEFT JOIN cust_main USING ( custnum )
+      WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum)
   );
 }
 
 sub netsales { #net sales
-  my( $self, $speriod, $eperiod ) = ( shift, shift, shift );
+  my( $self, $speriod, $eperiod, $agentnum ) = @_;
 
   my $credited = $self->scalar_sql("
     SELECT SUM(cust_credit_bill.amount)
-    FROM cust_credit_bill, cust_bill
-    WHERE cust_bill.invnum = cust_credit_bill.invnum
-      AND ".  $self->in_time_period($speriod, $eperiod, 'cust_bill')
+      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')
   );
 
   #horrible local kludge
   my $expenses = !$expenses_kludge ? 0 : $self->scalar_sql("
     SELECT SUM(cust_bill_pkg.setup)
-    FROM cust_bill_pkg, cust_bill, cust_pkg, part_pkg
-    WHERE cust_bill.invnum = cust_bill_pkg.invnum
-    AND ". $self->in_time_period($speriod, $eperiod, 'cust_bill'). "
-    AND cust_pkg.pkgnum = cust_bill_pkg.pkgnum
-    AND cust_pkg.pkgpart = part_pkg.pkgpart
-    AND LOWER(part_pkg.pkg) LIKE 'expense _%'
+      FROM cust_bill_pkg
+        LEFT JOIN cust_bill USING ( invnum  )
+        LEFT JOIN cust_main USING ( custnum )
+        LEFT JOIN cust_pkg  USING ( pkgnum  )
+        LEFT JOIN part_pkg  USING ( pkgpart )
+      WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum, 'cust_bill'). "
+        AND LOWER(part_pkg.pkg) LIKE 'expense _%'
   ");
 
-  $self->invoiced($speriod,$eperiod) - $credited - $expenses;
+  $self->invoiced($speriod,$eperiod,$agentnum) - $credited - $expenses;
 }
 
 #deferred revenue
 
 sub receipts { #cashflow
-  my( $self, $speriod, $eperiod ) = ( shift, shift, shift );
+  my( $self, $speriod, $eperiod, $agentnum ) = @_;
 
   my $refunded = $self->scalar_sql("
-    SELECT SUM(refund) FROM cust_refund
-    WHERE ". $self->in_time_period($speriod, $eperiod)
+    SELECT SUM(refund)
+      FROM cust_refund
+        LEFT JOIN cust_main USING ( custnum )
+      WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum)
   );
 
   #horrible local kludge that doesn't even really work right
   my $expenses = !$expenses_kludge ? 0 : $self->scalar_sql("
     SELECT SUM(cust_bill_pay.amount)
-    FROM cust_bill_pay, cust_bill
-    WHERE cust_bill_pay.invnum = cust_bill.invnum
-    AND ". $self->in_time_period($speriod, $eperiod, 'cust_bill_pay'). "
+      FROM cust_bill_pay
+        LEFT JOIN cust_bill USING ( invnum  )
+        LEFT JOIN cust_main USING ( custnum )
+    WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum, 'cust_bill_pay'). "
     AND 0 < ( SELECT COUNT(*) from cust_bill_pkg, cust_pkg, part_pkg
               WHERE cust_bill.invnum = cust_bill_pkg.invnum
               AND cust_pkg.pkgnum = cust_bill_pkg.pkgnum
@@ -125,40 +133,48 @@ sub receipts { #cashflow
   ");
   #    my $expenses_sql2 = "SELECT SUM(cust_bill_pay.amount) FROM cust_bill_pay, cust_bill_pkg, cust_bill, cust_pkg, part_pkg WHERE cust_bill_pay.invnum = cust_bill.invnum AND cust_bill.invnum = cust_bill_pkg.invnum AND cust_bill_pay._date >= $speriod AND cust_bill_pay._date < $eperiod AND cust_pkg.pkgnum = cust_bill_pkg.pkgnum AND cust_pkg.pkgpart = part_pkg.pkgpart AND LOWER(part_pkg.pkg) LIKE 'expense _%'";
   
-  $self->payments($speriod, $eperiod) - $refunded - $expenses;
+  $self->payments($speriod, $eperiod, $agentnum) - $refunded - $expenses;
 }
 
 sub payments {
-  my( $self, $speriod, $eperiod ) = ( shift, shift, shift );
+  my( $self, $speriod, $eperiod, $agentnum ) = @_;
   $self->scalar_sql("
-    SELECT SUM(paid) FROM cust_pay
-    WHERE ". $self->in_time_period($speriod, $eperiod)
+    SELECT SUM(paid)
+      FROM cust_pay
+        LEFT JOIN cust_main USING ( custnum )
+      WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum)
   );
 }
 
 sub credits {
-  my( $self, $speriod, $eperiod ) = ( shift, shift, shift );
+  my( $self, $speriod, $eperiod, $agentnum ) = @_;
   $self->scalar_sql("
-    SELECT SUM(amount) FROM cust_credit
-    WHERE ". $self->in_time_period($speriod, $eperiod)
+    SELECT SUM(amount)
+      FROM cust_credit
+        LEFT JOIN cust_main USING ( custnum )
+      WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum)
   );
 }
 
+# NEEDS TO BE AGENTNUM-capable
 sub canceled { #active
-  my( $self, $speriod, $eperiod ) = ( shift, shift, shift );
+  my( $self, $speriod, $eperiod, $agentnum ) = @_;
   $self->scalar_sql("
-    SELECT COUNT(*) FROM cust_pkg
-    WHERE cust_pkg.custnum = cust_main.custnum
-    AND 0 = ( SELECT COUNT(*) FROM cust_pkg
-              WHERE cust_pkg.custnum = cust_main.custnum
-              AND ( cust_pkg.cancel IS NULL OR cust_pkg.cancel = 0 )
-            )
-    AND cust_pkg.cancel > $speriod AND cust_pkg.cancel < $eperiod
+    SELECT COUNT(*)
+      FROM cust_pkg
+        LEFT JOIN cust_main USING ( custnum )
+      WHERE 0 = ( SELECT COUNT(*)
+                    FROM cust_pkg
+                    WHERE cust_pkg.custnum = cust_main.custnum
+                      AND ( cust_pkg.cancel IS NULL OR cust_pkg.cancel = 0 )
+                )
+        AND cust_pkg.cancel > $speriod AND cust_pkg.cancel < $eperiod
   ");
 }
  
+# NEEDS TO BE AGENTNUM-capable
 sub newaccount { #newaccount
-  my( $self, $speriod, $eperiod ) = ( shift, shift, shift );
+  my( $self, $speriod, $eperiod, $agentnum ) = @_;
   $self->scalar_sql("
      SELECT COUNT(*) FROM cust_pkg
      WHERE cust_pkg.custnum = cust_main.custnum
@@ -168,8 +184,9 @@ sub newaccount { #newaccount
   ");
 }
  
+# NEEDS TO BE AGENTNUM-capable
 sub suspended { #suspended
-  my( $self, $speriod, $eperiod ) = ( shift, shift, shift );
+  my( $self, $speriod, $eperiod, $agentnum ) = @_;
   $self->scalar_sql("
      SELECT COUNT(*) FROM cust_pkg
      WHERE cust_pkg.custnum = cust_main.custnum
@@ -182,10 +199,13 @@ sub suspended { #suspended
   ");
 }
 
-sub in_time_period {
-  my( $self, $speriod, $eperiod ) = ( shift, shift, shift );
+sub in_time_period_and_agent {
+  my( $self, $speriod, $eperiod, $agentnum ) = splice(@_, 0, 4);
   my $table = @_ ? shift().'.' : '';
-  "${table}_date >= $speriod AND ${table}_date < $eperiod";
+  my $sql = "${table}_date >= $speriod AND ${table}_date < $eperiod";
+  $sql .= " AND agentnum = $agentnum"
+    if $agentnum;
+  $sql;
 }
 
 sub scalar_sql {