X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=FS%2FFS%2FReport%2FTable%2FMonthly.pm;h=6daa02637a0751957c88b1a4f0b60b1ebbc943d0;hb=1783fdb03ace7f8bc0b5236d1b3cd842753fd16d;hp=1b7e93abad32df85fdf729e4b8439546af884c19;hpb=896ef9b1a5302dc2d96d351702be94184825cc91;p=freeside.git

diff --git a/FS/FS/Report/Table/Monthly.pm b/FS/FS/Report/Table/Monthly.pm
index 1b7e93aba..6daa02637 100644
--- a/FS/FS/Report/Table/Monthly.pm
+++ b/FS/FS/Report/Table/Monthly.pm
@@ -5,6 +5,7 @@ use vars qw( @ISA $expenses_kludge );
 use Time::Local;
 use FS::UID qw( dbh );
 use FS::Report::Table;
+use FS::CurrentUser;
 
 @ISA = qw( FS::Report::Table );
 
@@ -146,7 +147,11 @@ sub netsales { #net sales
       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')
+    WHERE ".  $self->in_time_period_and_agent( $speriod,
+                                               $eperiod,
+                                               $agentnum,
+                                               'cust_bill._date'
+                                             )
   );
 
   #horrible local kludge
@@ -157,7 +162,11 @@ sub netsales { #net sales
         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'). "
+      WHERE ". $self->in_time_period_and_agent( $speriod,
+                                                $eperiod,
+                                                $agentnum,
+                                                'cust_bill._date'
+                                              ). "
         AND LOWER(part_pkg.pkg) LIKE 'expense _%'
   ");
 
@@ -182,7 +191,11 @@ sub receipts { #cashflow
       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'). "
+    WHERE ". $self->in_time_period_and_agent( $speriod,
+                                              $eperiod,
+                                              $agentnum,
+                                              'cust_bill_pay._date'
+                                            ). "
     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
@@ -215,7 +228,22 @@ sub credits {
   );
 }
 
-#these should be auto-generated
+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 ) = @_;
   $speriod = $self->_subtract_11mo($speriod);
@@ -246,6 +274,12 @@ sub credits_12mo {
   $self->credits($speriod, $eperiod, $agentnum);
 }
 
+sub netcredits_12mo {
+  my( $self, $speriod, $eperiod, $agentnum ) = @_;
+  $speriod = $self->_subtract_11mo($speriod);
+  $self->netcredits($speriod, $eperiod, $agentnum);
+}
+
 #not being too bad with the false laziness
 use Time::Local qw(timelocal);
 sub _subtract_11mo {
@@ -284,55 +318,47 @@ sub cust_bill_pkg {
   
 }
 
-# NEEDS TO BE AGENTNUM-capable
-sub canceled { #active
-  my( $self, $speriod, $eperiod, $agentnum ) = @_;
-  $self->scalar_sql("
-    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, $agentnum ) = @_;
-  $self->scalar_sql("
-     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.susp IS NULL OR cust_pkg.susp = 0 )
-     AND cust_pkg.setup > $speriod AND cust_pkg.setup < $eperiod
-  ");
-}
+sub setup_pkg  { shift->pkg_field( @_, 'setup' ); }
+sub susp_pkg   { shift->pkg_field( @_, 'susp'  ); }
+sub cancel_pkg { shift->pkg_field( @_, 'cancel'); }
  
-# NEEDS TO BE AGENTNUM-capable
-sub suspended { #suspended
-  my( $self, $speriod, $eperiod, $agentnum ) = @_;
+sub pkg_field {
+  my( $self, $speriod, $eperiod, $agentnum, $field ) = @_;
   $self->scalar_sql("
-     SELECT COUNT(*) FROM cust_pkg
-     WHERE cust_pkg.custnum = cust_main.custnum
-     AND ( cust_pkg.cancel IS NULL OR cust_pkg.cancel = 0 )
-     AND 0 = ( SELECT COUNT(*) FROM cust_pkg
-               WHERE cust_pkg.custnum = cust_main.custnum
-               AND ( cust_pkg.susp IS NULL OR cust_pkg.susp = 0 )
-             )
-     AND cust_pkg.susp > $speriod AND cust_pkg.susp < $eperiod
-  ");
+    SELECT COUNT(*) FROM cust_pkg
+        LEFT JOIN cust_main USING ( custnum )
+      WHERE ". $self->in_time_period_and_agent( $speriod,
+                                                $eperiod,
+                                                $agentnum,
+                                                "cust_pkg.$field",
+                                              )
+  );
+
 }
 
+#this is going to be harder..
+#sub unsusp_pkg {
+#  my( $self, $speriod, $eperiod, $agentnum ) = @_;
+#  $self->scalar_sql("
+#    SELECT COUNT(*) FROM h_cust_pkg
+#      WHERE 
+#
+#}
+
 sub in_time_period_and_agent {
   my( $self, $speriod, $eperiod, $agentnum ) = splice(@_, 0, 4);
-  my $table = @_ ? shift().'.' : '';
-  my $sql = "${table}_date >= $speriod AND ${table}_date < $eperiod";
-  $sql .= " AND agentnum = $agentnum"
+  my $col = @_ ? shift() : '_date';
+
+  my $sql = "$col >= $speriod AND $col < $eperiod";
+
+  #agent selection
+  $sql .= " AND cust_main.agentnum = $agentnum"
     if $agentnum;
+
+  #agent virtualization
+  $sql .= ' AND '.
+          $FS::CurrentUser::CurrentUser->agentnums_sql( 'table'=>'cust_main' );
+
   $sql;
 }