Merge branch 'master' of git.freeside.biz:/home/git/freeside

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

diff --git a/FS/FS/Report/Table.pm b/FS/FS/Report/Table.pm
index 73eed6e..c5a6503 100644 (file)
--- a/FS/FS/Report/Table.pm
+++ b/FS/FS/Report/Table.pm
@@ -56,6 +56,13 @@ sub signups {
     push @where, "refnum = ".$opt{'refnum'};
   }
 
+  if ( $opt{'cust_classnum'} ) {
+    my $classnums = $opt{'cust_classnum'};
+    $classnums = [ $classnums ] if !ref($classnums);
+    @$classnums = grep /^\d+$/, @$classnums;
+    push @where, 'cust_main.classnum in('. join(',',@$classnums) .')';
+  }
+
   $self->scalar_sql(
     "SELECT COUNT(*) FROM cust_main $join WHERE ".join(' AND ', @where)
   );
@@ -68,9 +75,15 @@ 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).
                $self->for_opts(%opt)
@@ -162,9 +175,16 @@ 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,
@@ -182,9 +202,16 @@ sub netcredits {
 
 sub receipts { #net payments
   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,
@@ -416,10 +443,19 @@ 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),
   );
 
-  push @where, 'cust_main.refnum = '. $opt{'refnum'} if $opt{'refnum'};
+  # yuck, false laziness
+  push @where, "cust_main.refnum = ". $opt{'refnum'} if $opt{'refnum'};
+
+  if ( $opt{'cust_classnum'} ) {
+    my $classnums = $opt{'cust_classnum'};
+    $classnums = [ $classnums ] if !ref($classnums);
+    @$classnums = grep /^\d+$/, @$classnums;
+    push @where, 'cust_main.classnum in('. join(',',@$classnums) .')';
+  }
 
   my $total_sql = "SELECT COALESCE(SUM(cust_bill_pkg.setup),0)
   FROM cust_bill_pkg
@@ -439,10 +475,18 @@ 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'};
 
+  if ( $opt{'cust_classnum'} ) {
+    my $classnums = $opt{'cust_classnum'};
+    $classnums = [ $classnums ] if !ref($classnums);
+    @$classnums = grep /^\d+$/, @$classnums;
+    push @where, 'cust_main.classnum in('. join(',',@$classnums) .')';
+  }
+
   # subtract all usage from the line item regardless of date
   my $item_usage;
   if ( $opt{'project'} ) {
@@ -498,11 +542,19 @@ sub cust_bill_pkg_detail {
 
   push @where, 'cust_main.refnum = '. $opt{'refnum'} if $opt{'refnum'};
 
+  if ( $opt{'cust_classnum'} ) {
+    my $classnums = $opt{'cust_classnum'};
+    $classnums = [ $classnums ] if !ref($classnums);
+    @$classnums = grep /^\d+$/, @$classnums;
+    push @where, 'cust_main.classnum in('. join(',',@$classnums) .')';
+  }
+
   $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'} ) {
@@ -637,6 +689,14 @@ sub for_opts {
     if ( $opt{'refnum'} =~ /^(\d+)$/ ) {
       $sql .= " and refnum = $1 ";
     }
+    if ( $opt{'cust_classnum'} ) {
+      my $classnums = $opt{'cust_classnum'};
+      $classnums = [ $classnums ] if !ref($classnums);
+      @$classnums = grep /^\d+$/, @$classnums;
+      $sql .= ' and cust_main.classnum in('. join(',',@$classnums) .')'
+        if @$classnums;
+    }
+
     $sql;
 }
 
@@ -676,6 +736,41 @@ sub with_usageclass {
   return "cust_bill_pkg_detail.classnum $comparison";
 }
 
+sub with_report_option {
+  my $self = shift;
+  # $num can be a single number, or a comma-delimited list of numbers,
+  # or '0' to match only the empty set.
+  #
+  # or the word 'multiple' for all packages with more than one report class
+  my ($num, $use_override) = @_;
+  return '' if !defined($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";
+  } elsif ( $num eq '0' ) {
+    $comparison = "NOT EXISTS ($subselect)";
+  } else {
+    $comparison = "(SELECT COALESCE(string_agg(num, ','), '') FROM (
+    $subselect
+    ) AS x) = '$num'";
+  }
+  if ( $use_override ) {
+    # then also allow the non-override package to match
+    $comparison = "( $comparison OR " . $self->with_report_option($num) . ")";
+  }
+  $comparison;
+}
+
 sub scalar_sql {
   my( $self, $sql ) = ( shift, shift );
   my $sth = dbh->prepare($sql) or die dbh->errstr;