X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=FS%2FFS%2FReport%2FTable.pm;h=696940679f64d523328f1ddeab1cc4b7bee5a296;hb=044e4ea5533f1c14697b7ad408dc0cf0e0327abb;hp=c9ad7c65516f7cbd4729d2973df12cb6430eec91;hpb=aec23d6f04cffd44e3137efc6aa212825baf46a5;p=freeside.git

diff --git a/FS/FS/Report/Table.pm b/FS/FS/Report/Table.pm
index c9ad7c655..696940679 100644
--- a/FS/FS/Report/Table.pm
+++ b/FS/FS/Report/Table.pm
@@ -4,7 +4,7 @@ use strict;
 use vars qw( @ISA $DEBUG );
 use FS::Report;
 use Time::Local qw( timelocal );
-use FS::UID qw( dbh );
+use FS::UID qw( dbh driver_name );
 use FS::Report::Table;
 use FS::CurrentUser;
 
@@ -32,6 +32,35 @@ options in %opt.
 
 =over 4
 
+=item signups: The number of customers signed up.  Options are "refnum" 
+(limit by advertising source) and "indirect" (boolean, tells us to limit 
+to customers that have a referral_custnum that matches the advertising source).
+
+=cut
+
+sub signups {
+  my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
+  my @where = ( $self->in_time_period_and_agent($speriod, $eperiod, $agentnum, 
+      'cust_main.signupdate')
+  );
+  my $join = '';
+  if ( $opt{'indirect'} ) {
+    $join = " JOIN cust_main AS referring_cust_main".
+            " ON (cust_main.referral_custnum = referring_cust_main.custnum)";
+
+    if ( $opt{'refnum'} ) {
+      push @where, "referring_cust_main.refnum = ".$opt{'refnum'};
+    }
+  }
+  elsif ( $opt{'refnum'} ) {
+    push @where, "refnum = ".$opt{'refnum'};
+  }
+
+  $self->scalar_sql(
+    "SELECT COUNT(*) FROM cust_main $join WHERE ".join(' AND ', @where)
+  );
+}
+
 =item invoiced: The total amount charged on all invoices.
 
 =cut
@@ -39,12 +68,18 @@ options in %opt.
 sub invoiced { #invoiced
   my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
 
+  my $sql = 'SELECT SUM(cust_bill.charged) FROM cust_bill';
+  if ( $opt{'setuprecur'} ) {
+    $sql = 'SELECT SUM('.
+            FS::cust_bill_pkg->charged_sql($speriod, $eperiod, %opt).
+           ') FROM cust_bill_pkg JOIN cust_bill USING (invnum)';
+  }
+
   $self->scalar_sql("
-    SELECT SUM(charged)
-      FROM cust_bill
+      $sql
         LEFT JOIN cust_main USING ( custnum )
-      WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum)
-      . (%opt ? $self->for_custnum(%opt) : '')
+      WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum).
+               $self->for_opts(%opt)
   );
   
 }
@@ -56,8 +91,8 @@ sub invoiced { #invoiced
 sub netsales { #net sales
   my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
 
-    $self->invoiced($speriod,$eperiod,$agentnum,%opt)
-  - $self->netcredits($speriod,$eperiod,$agentnum,%opt);
+    $self->invoiced(  $speriod, $eperiod, $agentnum, %opt)
+  - $self->netcredits($speriod, $eperiod, $agentnum, %opt);
 }
 
 =item cashflow: payments - refunds
@@ -76,10 +111,10 @@ sub cashflow {
 =cut
 
 sub netcashflow {
-  my( $self, $speriod, $eperiod, $agentnum ) = @_;
+  my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
 
-    $self->receipts($speriod, $eperiod, $agentnum)
-  - $self->netrefunds( $speriod, $eperiod, $agentnum);
+    $self->receipts(   $speriod, $eperiod, $agentnum, %opt)
+  - $self->netrefunds( $speriod, $eperiod, $agentnum, %opt);
 }
 
 =item payments: The sum of payments received in the period.
@@ -92,8 +127,8 @@ sub payments {
     SELECT SUM(paid)
       FROM cust_pay
         LEFT JOIN cust_main USING ( custnum )
-      WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum)
-      . (%opt ? $self->for_custnum(%opt) : '')
+      WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum).
+               $self->for_opts(%opt)
   );
 }
 
@@ -102,12 +137,13 @@ sub payments {
 =cut
 
 sub credits {
-  my( $self, $speriod, $eperiod, $agentnum ) = @_;
+  my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
   $self->scalar_sql("
     SELECT SUM(amount)
       FROM cust_credit
         LEFT JOIN cust_main USING ( custnum )
-      WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum)
+      WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum).
+               $self->for_opts(%opt)
   );
 }
 
@@ -121,8 +157,8 @@ sub refunds {
     SELECT SUM(refund)
       FROM cust_refund
         LEFT JOIN cust_main USING ( custnum )
-      WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum)
-      . (%opt ? $self->for_custnum(%opt) : '')
+      WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum).
+               $self->for_opts(%opt)
   );
 }
 
@@ -132,17 +168,24 @@ sub refunds {
 
 sub netcredits {
   my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
+
+  my $sql = 'SELECT SUM(cust_credit_bill.amount) FROM cust_credit_bill';
+  if ( $opt{'setuprecur'} ) {
+    $sql = 'SELECT SUM('.
+            FS::cust_bill_pkg->credited_sql($speriod, $eperiod, %opt).
+           ') FROM cust_bill_pkg';
+  }
+
   $self->scalar_sql("
-    SELECT SUM(cust_credit_bill.amount)
-      FROM cust_credit_bill
+    $sql
         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'
-                                              )
-      . (%opt ? $self->for_custnum(%opt) : '')
+                                              ).
+               $self->for_opts(%opt)
   );
 }
 
@@ -151,17 +194,25 @@ sub netcredits {
 =cut
 
 sub receipts { #net payments
-  my( $self, $speriod, $eperiod, $agentnum ) = @_;
+  my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
+
+  my $sql = 'SELECT SUM(cust_bill_pay.amount) FROM cust_bill_pay';
+  if ( $opt{'setuprecur'} ) {
+    $sql = 'SELECT SUM('.
+            FS::cust_bill_pkg->paid_sql($speriod, $eperiod, %opt).
+           ') FROM cust_bill_pkg';
+  }
+
   $self->scalar_sql("
-    SELECT SUM(cust_bill_pay.amount)
-      FROM cust_bill_pay
+    $sql
         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'
-                                              )
+                                              ).
+               $self->for_opts(%opt)
   );
 }
 
@@ -170,7 +221,7 @@ sub receipts { #net payments
 =cut
 
 sub netrefunds {
-  my( $self, $speriod, $eperiod, $agentnum ) = @_;
+  my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
   $self->scalar_sql("
     SELECT SUM(cust_credit_refund.amount)
       FROM cust_credit_refund
@@ -180,7 +231,8 @@ sub netrefunds {
                                                 $eperiod,
                                                 $agentnum,
                                                 'cust_credit._date'
-                                              )
+                                              ).
+               $self->for_opts(%opt)
   );
 }
 
@@ -347,6 +399,8 @@ the fraction of the line item duration that falls within the specified
 interval and return that fraction of the recurring charges.  This is 
 somewhat experimental.
 
+'project': enable if this is a projected period.  This is very experimental.
+
 =cut
 
 sub cust_bill_pkg {
@@ -362,8 +416,7 @@ sub cust_bill_pkg {
   $sum;
 }
 
-my $cust_bill_pkg_from =
-  ' cust_bill_pkg
+my $cust_bill_pkg_join = '
     LEFT JOIN cust_bill USING ( invnum )
     LEFT JOIN cust_main USING ( custnum )
     LEFT JOIN cust_pkg USING ( pkgnum )
@@ -373,6 +426,10 @@ my $cust_bill_pkg_from =
 sub cust_bill_pkg_setup {
   my $self = shift;
   my ($speriod, $eperiod, $agentnum, %opt) = @_;
+  # no projecting setup fees--use real invoices only
+  # but evaluate this anyway, because the design of projection is that
+  # if there are somehow real setup fees in the future, we want to count
+  # them
 
   $agentnum ||= $opt{'agentnum'};
 
@@ -382,8 +439,11 @@ sub cust_bill_pkg_setup {
     $self->in_time_period_and_agent($speriod, $eperiod, $agentnum),
   );
 
+  push @where, "cust_main.refnum = ". $opt{'refnum'} if $opt{'refnum'};
+
   my $total_sql = "SELECT COALESCE(SUM(cust_bill_pkg.setup),0)
-  FROM $cust_bill_pkg_from
+  FROM cust_bill_pkg
+  $cust_bill_pkg_join
   WHERE " . join(' AND ', grep $_, @where);
 
   $self->scalar_sql($total_sql);
@@ -394,38 +454,50 @@ sub cust_bill_pkg_recur {
   my ($speriod, $eperiod, $agentnum, %opt) = @_;
 
   $agentnum ||= $opt{'agentnum'};
+  my $cust_bill_pkg = $opt{'project'} ? 'v_cust_bill_pkg' : 'cust_bill_pkg';
 
   my @where = (
     'pkgnum != 0',
     $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
   );
 
+  push @where, 'cust_main.refnum = '. $opt{'refnum'} if $opt{'refnum'};
+
   # subtract all usage from the line item regardless of date
-  my $item_usage = '( SELECT COALESCE(SUM(cust_bill_pkg_detail.amount),0)
-    FROM cust_bill_pkg_detail
-    WHERE cust_bill_pkg_detail.billpkgnum = cust_bill_pkg.billpkgnum )';
+  my $item_usage;
+  if ( $opt{'project'} ) {
+    $item_usage = 'usage'; #already calculated
+  }
+  else {
+    $item_usage = '( SELECT COALESCE(SUM(amount),0)
+      FROM cust_bill_pkg_detail
+      WHERE cust_bill_pkg_detail.billpkgnum = cust_bill_pkg.billpkgnum )';
+  }
   my $recur_fraction = '';
 
   if ( $opt{'distribute'} ) {
     push @where, "cust_main.agentnum = $agentnum" if $agentnum;
     push @where,
-      "cust_bill_pkg.sdate < $eperiod",
-      "cust_bill_pkg.edate > $speriod",
+      "$cust_bill_pkg.sdate <  $eperiod",
+      "$cust_bill_pkg.edate >= $speriod",
     ;
     # the fraction of edate - sdate that's within [speriod, eperiod]
     $recur_fraction = " * 
-      CAST(LEAST($eperiod, cust_bill_pkg.edate) - 
-       GREATEST($speriod, cust_bill_pkg.sdate) AS DECIMAL) / 
-      (cust_bill_pkg.edate - cust_bill_pkg.sdate)";
+      CAST(LEAST($eperiod, $cust_bill_pkg.edate) - 
+       GREATEST($speriod, $cust_bill_pkg.sdate) AS DECIMAL) / 
+      ($cust_bill_pkg.edate - $cust_bill_pkg.sdate)";
   }
   else {
+    # we don't want to have to create v_cust_bill
+    my $_date = $opt{'project'} ? 'v_cust_bill_pkg._date' : 'cust_bill._date';
     push @where, 
-      $self->in_time_period_and_agent($speriod, $eperiod, $agentnum);
+      $self->in_time_period_and_agent($speriod, $eperiod, $agentnum, $_date);
   }
 
-  my $total_sql = "SELECT COALESCE(SUM(
-  (cust_bill_pkg.recur - $item_usage) $recur_fraction),0)
-  FROM $cust_bill_pkg_from
+  my $total_sql = 'SELECT '.
+  "COALESCE(SUM(($cust_bill_pkg.recur - $item_usage) $recur_fraction),0)
+  FROM $cust_bill_pkg 
+  $cust_bill_pkg_join
   WHERE ".join(' AND ', grep $_, @where);
 
   $self->scalar_sql($total_sql);
@@ -444,6 +516,8 @@ sub cust_bill_pkg_detail {
 
   my @where = ( "cust_bill_pkg.pkgnum != 0" );
 
+  push @where, 'cust_main.refnum = '. $opt{'refnum'} if $opt{'refnum'};
+
   $agentnum ||= $opt{'agentnum'};
 
   push @where,
@@ -530,12 +604,12 @@ sub cust_bill_pkg_discount {
 
 }
 
-sub setup_pkg  { shift->pkg_field( @_, 'setup' ); }
-sub susp_pkg   { shift->pkg_field( @_, 'susp'  ); }
-sub cancel_pkg { shift->pkg_field( @_, 'cancel'); }
+sub setup_pkg  { shift->pkg_field( 'setup',  @_ ); }
+sub susp_pkg   { shift->pkg_field( 'susp',   @_ ); }
+sub cancel_pkg { shift->pkg_field( 'cancel', @_ ); }
  
 sub pkg_field {
-  my( $self, $speriod, $eperiod, $agentnum, $field ) = @_;
+  my( $self, $field, $speriod, $eperiod, $agentnum ) = @_;
   $self->scalar_sql("
     SELECT COUNT(*) FROM cust_pkg
         LEFT JOIN cust_main USING ( custnum )
@@ -574,10 +648,16 @@ sub in_time_period_and_agent {
   $sql;
 }
 
-sub for_custnum {
+sub for_opts {
     my ( $self, %opt ) = @_;
-    return '' unless $opt{'custnum'};
-    $opt{'custnum'} =~ /^\d+$/ ? " and custnum = $opt{custnum} " : '';
+    my $sql = '';
+    if ( $opt{'custnum'} =~ /^(\d+)$/ ) {
+      $sql .= " and custnum = $1 ";
+    }
+    if ( $opt{'refnum'} =~ /^(\d+)$/ ) {
+      $sql .= " and refnum = $1 ";
+    }
+    $sql;
 }
 
 sub with_classnum {
@@ -627,6 +707,124 @@ sub scalar_sql {
 
 =back
 
+=head1 METHODS
+
+=over 4
+
+=item init_projection
+
+Sets up for future projection of all observables on the report.  Currently 
+this is limited to 'cust_bill_pkg'.
+
+=cut
+
+sub init_projection {
+  # this is weird special case stuff--some redesign may be needed 
+  # to use it for anything else
+  my $self = shift;
+
+  if ( driver_name ne 'Pg' ) {
+    # also database-specific for now
+    die "projection reports not supported on this platform";
+  }
+
+  my %items = map {$_ => 1} @{ $self->{items} };
+  if ($items{'cust_bill_pkg'}) {
+    my $dbh = dbh;
+    # v_ for 'virtual'
+    my @sql = (
+      # could use TEMPORARY TABLE but we're already transaction-protected
+      'DROP TABLE IF EXISTS v_cust_bill_pkg',
+      'CREATE TABLE v_cust_bill_pkg ' . 
+       '(LIKE cust_bill_pkg,
+          usage numeric(10,2), _date integer, expire integer)',
+      # XXX this should be smart enough to take only the ones with 
+      # sdate/edate overlapping the ROI, for performance
+      "INSERT INTO v_cust_bill_pkg ( 
+        SELECT cust_bill_pkg.*,
+          (SELECT COALESCE(SUM(amount),0) FROM cust_bill_pkg_detail 
+          WHERE cust_bill_pkg_detail.billpkgnum = cust_bill_pkg.billpkgnum),
+          cust_bill._date,
+          cust_pkg.expire
+        FROM cust_bill_pkg $cust_bill_pkg_join
+      )",
+    );
+    foreach my $sql (@sql) {
+      warn "[init_projection] $sql\n" if $DEBUG;
+      $dbh->do($sql) or die $dbh->errstr;
+    }
+  }
+}
+
+=item extend_projection START END
+
+Generates data for the next period of projection.  This will be called 
+for sequential periods where the END of one equals the START of the next
+(with no gaps).
+
+=cut
+
+sub extend_projection {
+  my $self = shift;
+  my ($speriod, $eperiod) = @_;
+  my %items = map {$_ => 1} @{ $self->{items} };
+  if ($items{'cust_bill_pkg'}) {
+    # What we do here:
+    # Find all line items that end after the start of the period (and have 
+    # recurring fees, and don't expire before they end).  Choose the latest 
+    # one for each package.  If it ends before the end of the period, copy
+    # it forward by one billing period.
+    # Repeat this until the latest line item for each package no longer ends
+    # within the period.  This is certain to happen in finitely many 
+    # iterations as long as freq > 0.
+    # - Pg only, obviously.
+    # - Gives bad results if freq_override is used.
+    my @fields = ( FS::cust_bill_pkg->fields, qw( usage _date expire ) );
+    my $insert_fields = join(',', @fields);
+    my $add_freq = sub { # emulate FS::part_pkg::add_freq
+      my $field = shift;
+      "EXTRACT( EPOCH FROM TO_TIMESTAMP($field) + (CASE WHEN freq ~ E'\\\\D' ".
+      "THEN freq ELSE freq || 'mon' END)::INTERVAL) AS $field";
+    };
+    foreach (@fields) {
+      if ($_ eq 'edate') {
+        $_ = $add_freq->('edate');
+      }
+      elsif ($_ eq 'sdate') {
+        $_ = 'edate AS sdate'
+      }
+      elsif ($_ eq 'setup') {
+        $_ = '0 AS setup' #because recurring only
+      }
+      elsif ($_ eq '_date') {
+        $_ = $add_freq->('_date');
+      }
+    }
+    my $select_fields = join(',', @fields);
+    my $dbh = dbh;
+    my $sql =
+    # Subquery here because we need to DISTINCT the whole set, select the 
+    # latest charge per pkgnum, and _then_ check edate < $eperiod 
+    # and edate < expire.
+      "INSERT INTO v_cust_bill_pkg ($insert_fields)
+        SELECT $select_fields FROM (
+          SELECT DISTINCT ON (pkgnum) * FROM v_cust_bill_pkg
+            WHERE edate >= $speriod 
+              AND recur > 0
+              AND freq IS NOT NULL
+              AND freq != '0'
+            ORDER BY pkgnum, edate DESC
+          ) AS v1 
+          WHERE edate < $eperiod AND (edate < expire OR expire IS NULL)";
+    my $rows;
+    do {
+      warn "[extend_projection] $sql\n" if $DEBUG;
+      $rows = $dbh->do($sql) or die $dbh->errstr;
+      warn "[extend_projection] $rows rows\n" if $DEBUG;
+    } until $rows == 0;
+  }
+}
+
 =head1 BUGS
 
 Documentation.