add advertising source to sales report, RT#18349

[freeside.git] / FS / FS / Report / Table.pm

diff --git a/FS/FS/Report/Table.pm b/FS/FS/Report/Table.pm
index b5805e3..e1aec05 100644 (file)
--- a/FS/FS/Report/Table.pm
+++ b/FS/FS/Report/Table.pm
@@ -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
@@ -387,6 +416,8 @@ 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
   $cust_bill_pkg_join
@@ -407,6 +438,8 @@ sub cust_bill_pkg_recur {
     $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;
   if ( $opt{'project'} ) {
@@ -422,8 +455,8 @@ sub cust_bill_pkg_recur {
   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 = " * 
@@ -460,6 +493,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,
@@ -546,12 +581,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 )
@@ -705,14 +740,26 @@ sub extend_projection {
   my ($speriod, $eperiod) = @_;
   my %items = map {$_ => 1} @{ $self->{items} };
   if ($items{'cust_bill_pkg'}) {
-    # append, head-to-tail, new line items identical to any that end within the 
-    # period (and aren't expiring)
+    # 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);
-    #advance (sdate, edate) by one billing period
+    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') {
-        $_ = '(edate + (edate - sdate)) AS edate' #careful of integer overflow
+        $_ = $add_freq->('edate');
       }
       elsif ($_ eq 'sdate') {
         $_ = 'edate AS sdate'
@@ -721,20 +768,31 @@ sub extend_projection {
         $_ = '0 AS setup' #because recurring only
       }
       elsif ($_ eq '_date') {
-        $_ = '(_date + (edate - sdate)) AS _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 v_cust_bill_pkg
-        WHERE edate >= $speriod AND edate < $eperiod 
+        SELECT $select_fields FROM (
+          SELECT DISTINCT ON (pkgnum) * FROM v_cust_bill_pkg
+            WHERE edate >= $speriod 
               AND recur > 0
-              AND (expire IS NULL OR expire > edate)";
-    warn "[extend_projection] $sql\n" if $DEBUG;
-    my $rows = $dbh->do($sql) or die $dbh->errstr;
-    warn "[extend_projection] $rows rows\n" if $DEBUG;
+              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;
   }
 }