X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=FS%2FFS%2FReport%2FTable%2FMonthly.pm;h=89d44f92fb06aa8858bbec4728ef16a0bcf55b9b;hb=02f0f4ce6f01b9f6d67c44b8884320e6204a95a1;hp=3bbf01f39f32da9c63c974b59eecbc90af8f2f29;hpb=5b27fba78299a534d6d75a7322f26cf6af402b19;p=freeside.git

diff --git a/FS/FS/Report/Table/Monthly.pm b/FS/FS/Report/Table/Monthly.pm
index 3bbf01f39..89d44f92f 100644
--- a/FS/FS/Report/Table/Monthly.pm
+++ b/FS/FS/Report/Table/Monthly.pm
@@ -18,7 +18,7 @@ FS::Report::Table::Monthly - Tables of report data, indexed monthly
 
   use FS::Report::Table::Monthly;
 
-  my $report = new FS::Report::Table (
+  my $report = new FS::Report::Table::Monthly (
     'items' => [ 'invoiced', 'netsales', 'credits', 'receipts', ],
     'start_month' => 4,
     'start_year'  => 2000,
@@ -53,8 +53,10 @@ sub data {
     push @{$data{label}}, "$smonth/$syear";
 
     my $speriod = timelocal(0,0,0,1,$smonth-1,$syear);
+    push @{$data{speriod}}, $speriod;
     if ( ++$smonth == 13 ) { $syear++; $smonth=1; }
     my $eperiod = timelocal(0,0,0,1,$smonth-1,$syear);
+    push @{$data{eperiod}}, $eperiod;
   
     foreach my $item ( @{$self->{'items'}} ) {
       push @{$data{$item}}, $self->$item($speriod, $eperiod);
@@ -95,7 +97,7 @@ sub netsales { #net sales
     AND LOWER(part_pkg.pkg) LIKE 'expense _%'
   ");
 
-  $self->invoiced($speriod,$eperiod)-$credited-$expenses;
+  $self->invoiced($speriod,$eperiod) - $credited - $expenses;
 }
 
 #deferred revenue
@@ -103,12 +105,6 @@ sub netsales { #net sales
 sub receipts { #cashflow
   my( $self, $speriod, $eperiod ) = ( shift, shift, shift );
 
-  #cashflow
-  my $paid = $self->scalar_sql("
-    SELECT SUM(paid) FROM cust_pay
-    WHERE ". $self->in_time_period($speriod, $eperiod)
-  );
-
   my $refunded = $self->scalar_sql("
     SELECT SUM(refund) FROM cust_refund
     WHERE ". $self->in_time_period($speriod, $eperiod)
@@ -129,7 +125,15 @@ 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 _%'";
   
-  $paid-$refunded-$expenses;
+  $self->payments($speriod, $eperiod) - $refunded - $expenses;
+}
+
+sub payments {
+  my( $self, $speriod, $eperiod ) = ( shift, shift, shift );
+  $self->scalar_sql("
+    SELECT SUM(paid) FROM cust_pay
+    WHERE ". $self->in_time_period($speriod, $eperiod)
+  );
 }
 
 sub credits {
@@ -140,6 +144,44 @@ sub credits {
   );
 }
 
+sub canceled { #active
+  my( $self, $speriod, $eperiod ) = ( shift, shift, shift );
+  $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
+  ");
+}
+ 
+sub newaccount { #newaccount
+  my( $self, $speriod, $eperiod ) = ( shift, shift, shift );
+  $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 suspended { #suspended
+  my( $self, $speriod, $eperiod ) = ( shift, shift, shift );
+  $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
+  ");
+}
+
 sub in_time_period {
   my( $self, $speriod, $eperiod ) = ( shift, shift, shift );
   my $table = @_ ? shift().'.' : '';