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

diff --git a/FS/FS/Report/Table/Monthly.pm b/FS/FS/Report/Table/Monthly.pm
index 6daa02637..19c0b216d 100644
--- a/FS/FS/Report/Table/Monthly.pm
+++ b/FS/FS/Report/Table/Monthly.pm
@@ -1,15 +1,14 @@
 package FS::Report::Table::Monthly;
 
 use strict;
-use vars qw( @ISA $expenses_kludge );
+use vars qw( @ISA $DEBUG );
 use Time::Local;
 use FS::UID qw( dbh );
 use FS::Report::Table;
 use FS::CurrentUser;
 
 @ISA = qw( FS::Report::Table );
-
-$expenses_kludge = 0;
+$DEBUG = 0; # turning this on will trace all SQL statements, VERY noisy
 
 =head1 NAME
 
@@ -142,70 +141,24 @@ sub invoiced { #invoiced
 sub netsales { #net sales
   my( $self, $speriod, $eperiod, $agentnum ) = @_;
 
-  my $credited = $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'
-                                             )
-  );
-
-  #horrible local kludge
-  my $expenses = !$expenses_kludge ? 0 : $self->scalar_sql("
-    SELECT SUM(cust_bill_pkg.setup)
-      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._date'
-                                              ). "
-        AND LOWER(part_pkg.pkg) LIKE 'expense _%'
-  ");
-
-  $self->invoiced($speriod,$eperiod,$agentnum) - $credited - $expenses;
+    $self->invoiced($speriod,$eperiod,$agentnum)
+  - $self->credits( $speriod,$eperiod,$agentnum);
 }
 
 #deferred revenue
 
-sub receipts { #cashflow
+sub cashflow {
   my( $self, $speriod, $eperiod, $agentnum ) = @_;
 
-  my $refunded = $self->scalar_sql("
-    SELECT SUM(refund)
-      FROM cust_refund
-        LEFT JOIN cust_main USING ( custnum )
-      WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum)
-  );
+    $self->payments($speriod, $eperiod, $agentnum)
+  - $self->refunds( $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
-        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._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
-              AND cust_pkg.pkgpart = part_pkg.pkgpart
-              AND LOWER(part_pkg.pkg) LIKE 'expense _%'
-            )
-  ");
-  #    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, $agentnum) - $refunded - $expenses;
+sub netcashflow {
+  my( $self, $speriod, $eperiod, $agentnum ) = @_;
+
+    $self->receipts($speriod, $eperiod, $agentnum)
+  - $self->netrefunds( $speriod, $eperiod, $agentnum);
 }
 
 sub payments {
@@ -228,6 +181,16 @@ sub credits {
   );
 }
 
+sub refunds {
+  my( $self, $speriod, $eperiod, $agentnum ) = @_;
+  $self->scalar_sql("
+    SELECT SUM(refund)
+      FROM cust_refund
+        LEFT JOIN cust_main USING ( custnum )
+      WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum)
+  );
+}
+
 sub netcredits {
   my( $self, $speriod, $eperiod, $agentnum ) = @_;
   $self->scalar_sql("
@@ -243,6 +206,36 @@ sub netcredits {
   );
 }
 
+sub receipts { #net payments
+  my( $self, $speriod, $eperiod, $agentnum ) = @_;
+  $self->scalar_sql("
+    SELECT SUM(cust_bill_pay.amount)
+      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._date'
+                                              )
+  );
+}
+
+sub netrefunds {
+  my( $self, $speriod, $eperiod, $agentnum ) = @_;
+  $self->scalar_sql("
+    SELECT SUM(cust_credit_refund.amount)
+      FROM cust_credit_refund
+        LEFT JOIN cust_credit USING ( crednum  )
+        LEFT JOIN cust_main   USING ( custnum )
+      WHERE ". $self->in_time_period_and_agent( $speriod,
+                                                $eperiod,
+                                                $agentnum,
+                                                'cust_credit._date'
+                                              )
+  );
+}
+
 #these should be auto-generated or $AUTOLOADed or something
 sub invoiced_12mo {
   my( $self, $speriod, $eperiod, $agentnum ) = @_;
@@ -280,6 +273,31 @@ sub netcredits_12mo {
   $self->netcredits($speriod, $eperiod, $agentnum);
 }
 
+sub cashflow_12mo {
+  my( $self, $speriod, $eperiod, $agentnum ) = @_;
+  $speriod = $self->_subtract_11mo($speriod);
+  $self->cashflow($speriod, $eperiod, $agentnum);
+}
+
+sub netcashflow_12mo {
+  my( $self, $speriod, $eperiod, $agentnum ) = @_;
+  $speriod = $self->_subtract_11mo($speriod);
+  $self->cashflow($speriod, $eperiod, $agentnum);
+}
+
+sub refunds_12mo {
+  my( $self, $speriod, $eperiod, $agentnum ) = @_;
+  $speriod = $self->_subtract_11mo($speriod);
+  $self->refunds($speriod, $eperiod, $agentnum);
+}
+
+sub netrefunds_12mo {
+  my( $self, $speriod, $eperiod, $agentnum ) = @_;
+  $speriod = $self->_subtract_11mo($speriod);
+  $self->netrefunds($speriod, $eperiod, $agentnum);
+}
+
+
 #not being too bad with the false laziness
 use Time::Local qw(timelocal);
 sub _subtract_11mo {
@@ -290,31 +308,171 @@ sub _subtract_11mo {
   timelocal($sec,$min,$hour,$mday,$mon,$year);
 }
 
+sub cust_pkg_setup_cost {
+  my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
+  my $where = '';
+  my $comparison = '';
+  if ( $opt{'classnum'} =~ /^(\d+)$/ ) {
+    if ( $1 == 0 ) {
+      $comparison = 'IS NULL';
+    }
+    else {
+      $comparison = "= $1";
+    }
+    $where = "AND part_pkg.classnum $comparison";
+  }
+  $agentnum ||= $opt{'agentnum'};
+
+  my $total_sql = " SELECT SUM(part_pkg.setup_cost) ";
+  $total_sql .= " FROM cust_pkg 
+             LEFT JOIN cust_main USING ( custnum )
+             LEFT JOIN part_pkg  USING ( pkgpart )
+                  WHERE pkgnum != 0
+                  $where
+                  AND ".$self->in_time_period_and_agent(
+                    $speriod, $eperiod, $agentnum, 'cust_pkg.setup');
+  return $self->scalar_sql($total_sql);
+}
+
+sub cust_pkg_recur_cost {
+  my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
+  my $where = '';
+  my $comparison = '';
+  if ( $opt{'classnum'} =~ /^(\d+)$/ ) {
+    if ( $1 == 0 ) {
+      $comparison = 'IS NULL';
+    }
+    else {
+      $comparison = "= $1";
+    }
+    $where = " AND part_pkg.classnum $comparison";
+  }
+  $agentnum ||= $opt{'agentnum'};
+  # duplication of in_time_period_and_agent
+  # because we do it a little differently here
+  $where .= " AND cust_main.agentnum = $agentnum" if $agentnum;
+  $where .= " AND ".
+          $FS::CurrentUser::CurrentUser->agentnums_sql('table' => 'cust_main');
+
+  my $total_sql = " SELECT SUM(part_pkg.recur_cost) ";
+  $total_sql .= " FROM cust_pkg
+             LEFT JOIN cust_main USING ( custnum )
+             LEFT JOIN part_pkg  USING ( pkgpart )
+                  WHERE pkgnum != 0
+                  $where
+                  AND cust_pkg.setup < $eperiod
+                  AND (cust_pkg.cancel > $speriod OR cust_pkg.cancel IS NULL)
+                  ";
+  return $self->scalar_sql($total_sql);
+}
+ 
 sub cust_bill_pkg {
   my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
 
   my $where = '';
+  my $comparison = '';
   if ( $opt{'classnum'} =~ /^(\d+)$/ ) {
     if ( $1 == 0 ) {
-      $where = "classnum IS NULL";
+      $comparison = "IS NULL";
+    } else {
+      $comparison = "= $1";
+    }
+
+    if ( $opt{'use_override'} ) {
+      $where = "AND (
+        part_pkg.classnum $comparison AND pkgpart_override IS NULL OR
+        override.classnum $comparison AND pkgpart_override IS NOT NULL
+      )";
     } else {
-      $where = "classnum = $1";
+      $where = "AND part_pkg.classnum $comparison";
     }
   }
 
   $agentnum ||= $opt{'agentnum'};
 
-  $self->scalar_sql("
-    SELECT SUM(cust_bill_pkg.setup + cust_bill_pkg.recur)
-      FROM cust_bill_pkg
+  my $total_sql =
+    " SELECT COALESCE( SUM(cust_bill_pkg.setup + cust_bill_pkg.recur), 0 ) ";
+
+  $total_sql .=
+    " / CASE COUNT(cust_pkg.*) WHEN 0 THEN 1 ELSE COUNT(cust_pkg.*) END "
+      if $opt{average_per_cust_pkg};
+
+  $total_sql .=
+    " 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 )
+        LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart
       WHERE pkgnum != 0
-        AND $where
-        AND ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum)
-  );
+        $where
+        AND ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum);
+  
+  if ($opt{use_usage} && $opt{use_usage} eq 'recurring') {
+    my $total = $self->scalar_sql($total_sql);
+    my $usage = cust_bill_pkg_detail(@_); #$speriod, $eperiod, $agentnum, %opt 
+    return $total-$usage;
+  } elsif ($opt{use_usage} && $opt{use_usage} eq 'usage') {
+    return cust_bill_pkg_detail(@_); #$speriod, $eperiod, $agentnum, %opt 
+  } else {
+    return $self->scalar_sql($total_sql);
+  }
+}
+
+sub cust_bill_pkg_detail {
+  my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
+
+  my @where = ( "cust_bill_pkg.pkgnum != 0" );
+  my $comparison = '';
+  if ( $opt{'classnum'} =~ /^(\d+)$/ ) {
+    if ( $1 == 0 ) {
+      $comparison = "IS NULL";
+    } else {
+      $comparison = "= $1";
+    }
+
+    if ( $opt{'use_override'} ) {
+      push @where, "(
+        part_pkg.classnum $comparison AND pkgpart_override IS NULL OR
+        override.classnum $comparison AND pkgpart_override IS NOT NULL
+      )";
+    } else {
+      push @where, "part_pkg.classnum $comparison";
+    }
+  }
+
+  if ( $opt{'usageclass'} =~ /^(\d+)$/ ) {
+    if ( $1 == 0 ) {
+      $comparison = "IS NULL";
+    } else {
+      $comparison = "= $1";
+    }
+
+    push @where, "cust_bill_pkg_detail.classnum $comparison";
+  }
+
+  $agentnum ||= $opt{'agentnum'};
+
+  my $where = join( ' AND ', @where );
+
+  my $total_sql = " SELECT SUM(amount) ";
+
+  $total_sql .=
+    " / CASE COUNT(cust_pkg.*) WHEN 0 THEN 1 ELSE COUNT(cust_pkg.*) END "
+      if $opt{average_per_cust_pkg};
+
+  $total_sql .=
+    " FROM cust_bill_pkg_detail
+        LEFT JOIN cust_bill_pkg USING ( billpkgnum )
+        LEFT JOIN cust_bill ON cust_bill_pkg.invnum = cust_bill.invnum
+        LEFT JOIN cust_main USING ( custnum )
+        LEFT JOIN cust_pkg ON cust_bill_pkg.pkgnum = cust_pkg.pkgnum
+        LEFT JOIN part_pkg USING ( pkgpart )
+        LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart
+      WHERE $where
+        AND ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum);
+
+  $self->scalar_sql($total_sql);
   
 }
 
@@ -364,6 +522,7 @@ sub in_time_period_and_agent {
 
 sub scalar_sql {
   my( $self, $sql ) = ( shift, shift );
+  warn "FS::Report::Table::Monthly\n$sql\n" if $DEBUG;
   my $sth = dbh->prepare($sql) or die dbh->errstr;
   $sth->execute
     or die "Unexpected error executing statement $sql: ". $sth->errstr;