separate one-time from recurring charges in Customer Accounting Summary, #19732

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

diff --git a/FS/FS/Report/Table.pm b/FS/FS/Report/Table.pm
index e8971ec..6969406 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
@@ -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)
   );
 }
 
@@ -387,6 +439,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 +461,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'} ) {
@@ -460,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,
@@ -590,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 {