X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=FS%2FFS%2FReport%2FTable.pm;h=1d60b6420a228988819e2733e71a2cd2867e52f2;hb=df1ebf662a9fc3f89503036e0dbf6833c1b95f9e;hp=3942543b5445332d5578e093cc9d8ff368915a02;hpb=9f97c81b19a3184ea68df32aaea43808b22e10f0;p=freeside.git

diff --git a/FS/FS/Report/Table.pm b/FS/FS/Report/Table.pm
index 3942543b5..1d60b6420 100644
--- a/FS/FS/Report/Table.pm
+++ b/FS/FS/Report/Table.pm
@@ -32,21 +32,34 @@ options in %opt.
 
 =over 4
 
-=item signups: The number of customers signed up.
+=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, 'signupdate')
+  my @where = ( $self->in_time_period_and_agent($speriod, $eperiod, $agentnum, 
+      'cust_main.signupdate')
   );
-  if ( $opt{'refnum'} ) {
+  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'};
   }
 
+  push @where, $self->with_cust_classnum(%opt);
+
   $self->scalar_sql(
-    "SELECT COUNT(*) FROM cust_main WHERE ".join(' AND ', @where)
+    "SELECT COUNT(*) FROM cust_main $join WHERE ".join(' AND ', @where)
   );
 }
 
@@ -57,12 +70,18 @@ sub signups {
 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)
   );
   
 }
@@ -74,8 +93,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
@@ -94,10 +113,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.
@@ -110,8 +129,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)
   );
 }
 
@@ -120,12 +139,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)
   );
 }
 
@@ -139,8 +159,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)
   );
 }
 
@@ -150,17 +170,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)
   );
 }
 
@@ -169,17 +196,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)
   );
 }
 
@@ -188,7 +223,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
@@ -198,7 +233,8 @@ sub netrefunds {
                                                 $eperiod,
                                                 $agentnum,
                                                 'cust_credit._date'
-                                              )
+                                              ).
+               $self->for_opts(%opt)
   );
 }
 
@@ -284,16 +320,15 @@ sub _subtract_11mo {
 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";
+
+  if ( $opt{'classnum'} ne '' ) {
+    my $classnums = $opt{'classnum'};
+    $classnums = [ $classnums ] if !ref($classnums);
+    @$classnums = grep /^\d+$/, @$classnums;
+    $where .= ' AND COALESCE(part_pkg.classnum,0) IN ('. join(',', @$classnums).
+                                                    ')';
   }
+
   $agentnum ||= $opt{'agentnum'};
 
   my $total_sql = " SELECT SUM(part_pkg.setup_cost) ";
@@ -316,16 +351,15 @@ sub cust_pkg_setup_cost {
 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";
+
+  if ( $opt{'classnum'} ne '' ) {
+    my $classnums = $opt{'classnum'};
+    $classnums = [ $classnums ] if !ref($classnums);
+    @$classnums = grep /^\d+$/, @$classnums;
+    $where .= ' AND COALESCE(part_pkg.classnum,0) IN ('. join(',', @$classnums).
+                                                    ')';
   }
+
   $agentnum ||= $opt{'agentnum'};
   # duplication of in_time_period_and_agent
   # because we do it a little differently here
@@ -402,9 +436,15 @@ sub cust_bill_pkg_setup {
   my @where = (
     'pkgnum != 0',
     $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
+    $self->with_report_option($opt{'report_optionnum'}, $opt{'use_override'}),
     $self->in_time_period_and_agent($speriod, $eperiod, $agentnum),
   );
 
+  # yuck, false laziness
+  push @where, "cust_main.refnum = ". $opt{'refnum'} if $opt{'refnum'};
+
+  push @where, $self->with_cust_classnum(%opt);
+
   my $total_sql = "SELECT COALESCE(SUM(cust_bill_pkg.setup),0)
   FROM cust_bill_pkg
   $cust_bill_pkg_join
@@ -423,8 +463,13 @@ sub cust_bill_pkg_recur {
   my @where = (
     'pkgnum != 0',
     $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
+    $self->with_report_option($opt{'report_optionnum'}, $opt{'use_override'}),
   );
 
+  push @where, 'cust_main.refnum = '. $opt{'refnum'} if $opt{'refnum'};
+
+  push @where, $self->with_cust_classnum(%opt);
+
   # subtract all usage from the line item regardless of date
   my $item_usage;
   if ( $opt{'project'} ) {
@@ -478,11 +523,16 @@ sub cust_bill_pkg_detail {
 
   my @where = ( "cust_bill_pkg.pkgnum != 0" );
 
+  push @where, 'cust_main.refnum = '. $opt{'refnum'} if $opt{'refnum'};
+
+  push @where, $self->with_cust_classnum(%opt);
+
   $agentnum ||= $opt{'agentnum'};
 
   push @where,
     $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
     $self->with_usageclass($opt{'usageclass'}),
+    $self->with_report_option($opt{'report_optionnum'}, $opt{'use_override'}),
     ;
 
   if ( $opt{'distribute'} ) {
@@ -520,6 +570,7 @@ sub cust_bill_pkg_detail {
 sub cust_bill_pkg_discount {
   my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
 
+  #need to do this the new multi-classnum way if it gets re-enabled
   #my $where = '';
   #my $comparison = '';
   #if ( $opt{'classnum'} =~ /^(\d+)$/ ) {
@@ -608,31 +659,37 @@ 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 ";
+    }
+    if ( my $where = $self->with_cust_classnum(%opt) ) {
+      $sql .= " and $where";
+    }
+
+    $sql;
 }
 
 sub with_classnum {
-  my $self = shift;
-  my ($classnum, $use_override) = @_;
-  return '' unless $classnum =~ /^\d+$/;
-  my $comparison;
-  if ( $classnum == 0 ) {
-    $comparison = 'IS NULL';
-  }
-  else {
-    $comparison = "= $classnum";
-  }
+  my ($self, $classnum, $use_override) = @_;
+  return '' if $classnum eq '';
+
+  $classnum = [ $classnum ] if !ref($classnum);
+  @$classnum = grep /^\d+$/, @$classnum;
+  my $in = 'IN ('. join(',', @$classnum). ')';
+
   if ( $use_override ) {
-    return "(
-      part_pkg.classnum $comparison AND pkgpart_override IS NULL OR
-      override.classnum $comparison AND pkgpart_override IS NOT NULL
+    "(
+         ( COALESCE(part_pkg.classnum, 0) $in AND pkgpart_override IS NULL)
+      OR ( COALESCE(override.classnum, 0) $in AND pkgpart_override IS NOT NULL )
     )";
-  }
-  else {
-    return "part_pkg.classnum $comparison";
+  } else {
+    "COALESCE(part_pkg.classnum, 0) $in";
   }
 }
 
@@ -650,6 +707,60 @@ sub with_usageclass {
   return "cust_bill_pkg_detail.classnum $comparison";
 }
 
+sub with_report_option {
+  my ($self, $num, $use_override) = @_;
+  # $num can be a single number, or a comma-delimited list of numbers,
+  # or an arrayref.  0 matches the empty set
+  # or the word 'multiple' for all packages with more than one report class
+  return '' if !defined($num);
+
+  $num = join(',', @$num) if ref($num);
+
+  # stringify the set of report options for each pkgpart
+  my $table = $use_override ? 'override' : 'part_pkg';
+  my $subselect = "
+    SELECT replace(optionname, 'report_option_', '') AS num
+      FROM part_pkg_option
+      WHERE optionname like 'report_option_%' 
+        AND part_pkg_option.pkgpart = $table.pkgpart
+      ORDER BY num";
+  
+  my $comparison;
+  if ( $num eq 'multiple' ) {
+    $comparison = "(SELECT COUNT(*) FROM ($subselect) AS x) > 1";
+  } else {
+
+    my @num = split(/\s*,\s*/, $num);
+
+    #$comparison = "(SELECT COALESCE(string_agg(num, ','), '') FROM ( #Pg 9-ism
+    $comparison = "(SELECT COALESCE(array_to_string(array_agg(num), ','), '')
+                      FROM ($subselect) AS x
+                   ) = '". join(',', grep $_, @num). "'";
+
+    $comparison = "( $comparison OR NOT EXISTS ($subselect) )"
+      if grep !$_, @num;
+
+  }
+  if ( $use_override ) {
+    # then also allow the non-override package to match
+    $comparison = "( $comparison OR " . $self->with_report_option($num) . ")";
+  }
+  $comparison;
+}
+
+sub with_cust_classnum {
+  my ($self, %opt) = @_;
+  if ( $opt{'cust_classnum'} ) {
+    my $classnums = $opt{'cust_classnum'};
+    $classnums = [ $classnums ] if !ref($classnums);
+    @$classnums = grep /^\d+$/, @$classnums;
+    return 'cust_main.classnum in('. join(',',@$classnums) .')'
+      if @$classnums;
+  }
+  '';
+}
+
+
 sub scalar_sql {
   my( $self, $sql ) = ( shift, shift );
   my $sth = dbh->prepare($sql) or die dbh->errstr;