X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=FS%2FFS%2FReport%2FTable.pm;h=17b12ae2340880e15ae661e1f61eeccaaddb6cdd;hp=2e202e5d966c00ca408999f88a3cf2a76eacf9ee;hb=e9e0cf0989259b94d9758eceff448666a2e5a5cc;hpb=7a618d4493ab7a87f6eabfd1ce3630a6b3436c2f

diff --git a/FS/FS/Report/Table.pm b/FS/FS/Report/Table.pm
index 2e202e5d9..17b12ae23 100644
--- a/FS/FS/Report/Table.pm
+++ b/FS/FS/Report/Table.pm
@@ -56,12 +56,7 @@ 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) .')';
-  }
+  push @where, $self->with_cust_classnum(%opt);
 
   $self->scalar_sql(
     "SELECT COUNT(*) FROM cust_main $join WHERE ".join(' AND ', @where)
@@ -146,7 +141,7 @@ sub payments {
 sub credits {
   my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
   $self->scalar_sql("
-    SELECT SUM(amount)
+    SELECT SUM(cust_credit.amount)
       FROM cust_credit
         LEFT JOIN cust_main USING ( custnum )
       WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum).
@@ -325,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) ";
@@ -357,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
@@ -397,9 +390,6 @@ unspecified, defaults to all three.
 'use_override': for line items generated by an add-on package, use the class
 of the add-on rather than the base package.
 
-'freq': limit to packages with this frequency.  Currently uses the part_pkg 
-frequency, so term discounted packages may give odd results.
-
 'distribute': for non-monthly recurring charges, ignore the invoice 
 date.  Instead, consider the line item's starting/ending dates.  Determine 
 the fraction of the line item duration that falls within the specified 
@@ -428,7 +418,8 @@ my $cust_bill_pkg_join = '
     LEFT JOIN cust_main USING ( custnum )
     LEFT JOIN cust_pkg USING ( pkgnum )
     LEFT JOIN part_pkg USING ( pkgpart )
-    LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart';
+    LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart
+    LEFT JOIN part_fee USING ( feepart )';
 
 sub cust_bill_pkg_setup {
   my $self = shift;
@@ -441,20 +432,16 @@ sub cust_bill_pkg_setup {
   $agentnum ||= $opt{'agentnum'};
 
   my @where = (
-    'pkgnum != 0',
+    '(pkgnum != 0 OR feepart IS NOT NULL)',
     $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
+    $self->with_report_option(%opt),
     $self->in_time_period_and_agent($speriod, $eperiod, $agentnum),
   );
 
   # 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) .')';
-  }
+  push @where, $self->with_cust_classnum(%opt);
 
   my $total_sql = "SELECT COALESCE(SUM(cust_bill_pkg.setup),0)
   FROM cust_bill_pkg
@@ -472,18 +459,14 @@ sub cust_bill_pkg_recur {
   my $cust_bill_pkg = $opt{'project'} ? 'v_cust_bill_pkg' : 'cust_bill_pkg';
 
   my @where = (
-    'pkgnum != 0',
+    '(pkgnum != 0 OR feepart IS NOT NULL)',
     $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
+    $self->with_report_option(%opt),
   );
 
   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) .')';
-  }
+  push @where, $self->with_cust_classnum(%opt);
 
   # subtract all usage from the line item regardless of date
   my $item_usage;
@@ -491,13 +474,14 @@ sub cust_bill_pkg_recur {
     $item_usage = 'usage'; #already calculated
   }
   else {
-    $item_usage = '( SELECT COALESCE(SUM(amount),0)
+    $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 $recur_fraction = '';
 
   if ( $opt{'distribute'} ) {
+    $where[0] = 'pkgnum != 0'; # specifically exclude fees
     push @where, "cust_main.agentnum = $agentnum" if $agentnum;
     push @where,
       "$cust_bill_pkg.sdate <  $eperiod",
@@ -536,26 +520,25 @@ Arguments as for C<cust_bill_pkg>, plus:
 sub cust_bill_pkg_detail {
   my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
 
-  my @where = ( "cust_bill_pkg.pkgnum != 0" );
+  my @where = 
+    ( "(cust_bill_pkg.pkgnum != 0 OR cust_bill_pkg.feepart IS NOT NULL)" );
 
   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) .')';
-  }
+  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),
     ;
 
   if ( $opt{'distribute'} ) {
-    # then limit according to the usage time, not the billing date
+    # exclude fees
+    $where[0] = 'cust_bill_pkg.pkgnum != 0';
+    # and limit according to the usage time, not the billing date
     push @where, $self->in_time_period_and_agent($speriod, $eperiod, $agentnum,
       'cust_bill_pkg_detail.startdate'
     );
@@ -566,7 +549,7 @@ sub cust_bill_pkg_detail {
     );
   }
 
-  my $total_sql = " SELECT SUM(amount) ";
+  my $total_sql = " SELECT SUM(cust_bill_pkg_detail.amount) ";
 
   $total_sql .=
     " / CASE COUNT(cust_pkg.*) WHEN 0 THEN 1 ELSE COUNT(cust_pkg.*) END "
@@ -580,6 +563,7 @@ sub cust_bill_pkg_detail {
         LEFT JOIN cust_pkg ON cust_bill_pkg.pkgnum = cust_pkg.pkgnum
         LEFT JOIN part_pkg USING ( pkgpart )
         LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart
+        LEFT JOIN part_fee USING ( feepart )
       WHERE ".join( ' AND ', grep $_, @where );
 
   $self->scalar_sql($total_sql);
@@ -589,6 +573,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+)$/ ) {
@@ -686,37 +671,29 @@ 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;
+    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). ')';
+
+  my $expr = "
+         ( COALESCE(part_pkg.classnum, 0) $in AND pkgpart_override IS NULL)
+      OR ( COALESCE(part_fee.classnum, 0) $in AND feepart IS NOT NULL )";
   if ( $use_override ) {
-    return "(
-      part_pkg.classnum $comparison AND pkgpart_override IS NULL OR
-      override.classnum $comparison AND pkgpart_override IS NOT NULL
-    )";
-  }
-  else {
-    return "part_pkg.classnum $comparison";
+    $expr .= "
+      OR ( COALESCE(override.classnum, 0) $in AND pkgpart_override IS NOT NULL )";
   }
+  "( $expr )";
 }
 
 sub with_usageclass {
@@ -733,6 +710,87 @@ sub with_usageclass {
   return "cust_bill_pkg_detail.classnum $comparison";
 }
 
+sub with_report_option {
+  my ($self, %opt) = @_;
+  # %opt can contain:
+  # - report_optionnum: a comma-separated list of numbers.  Zero means to 
+  #   include packages with _no_ report classes.
+  # - not_report_optionnum: a comma-separated list.  Packages that have 
+  #   any of these report options will be excluded from the result.
+  #   Zero does nothing.
+  # - use_override: also matches line items that are add-ons to a package
+  #   matching the report class.
+  # - all_report_options: returns only packages that have ALL of the
+  #   report classes listed in $num.  Otherwise, will return packages that 
+  #   have ANY of those classes.
+
+  my @num = ref($opt{'report_optionnum'})
+                  ? @{ $opt{'report_optionnum'} }
+                  : split(/\s*,\s*/, $opt{'report_optionnum'});
+  my @not_num = ref($opt{'not_report_optionnum'})
+                      ? @{ $opt{'not_report_optionnum'} }
+                      : split(/\s*,\s*/, $opt{'not_report_optionnum'});
+  my $null;
+  $null = 1 if ( grep {$_ == 0} @num );
+  @num = grep {$_ > 0} @num;
+  @not_num = grep {$_ > 0} @not_num;
+
+  # brute force
+  my $table = $opt{'use_override'} ? 'override' : 'part_pkg';
+  my $op = ' OR ';
+  if ( $opt{'all_report_options'} ) {
+    if ( @num and $null ) {
+      return 'false'; # mutually exclusive criteria, so just bail out
+    }
+    $op = ' AND ';
+  }
+  my @where_num = map {
+    "EXISTS(SELECT 1 FROM part_pkg_option ".
+    "WHERE optionname = 'report_option_$_' ".
+    "AND part_pkg_option.pkgpart = $table.pkgpart)"
+  } @num;
+  if ( $null ) {
+    push @where_num, "NOT EXISTS(SELECT 1 FROM part_pkg_option ".
+                     "WHERE optionname LIKE 'report_option_%' ".
+                     "AND part_pkg_option.pkgpart = $table.pkgpart)";
+  }
+  my @where_not_num = map {
+    "NOT EXISTS(SELECT 1 FROM part_pkg_option ".
+    "WHERE optionname = 'report_option_$_' ".
+    "AND part_pkg_option.pkgpart = $table.pkgpart)"
+  } @not_num;
+
+  my @where;
+  if (@where_num) {
+    push @where, '( '.join($op, @where_num).' )';
+  }
+  if (@where_not_num) {
+    push @where, '( '.join(' AND ', @where_not_num).' )';
+  }
+
+  return @where;
+  # this messes up totals
+  #if ( $opt{'use_override'} ) {
+  #  # then also allow the non-override package to match
+  #  delete $opt{'use_override'};
+  #  $comparison = "( $comparison OR " . $self->with_report_option(%opt) . ")";
+  #}
+
+}
+
+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;
@@ -779,7 +837,8 @@ sub init_projection {
       # 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 
+          (SELECT COALESCE(SUM(cust_bill_pkg_detail.amount),0)
+          FROM cust_bill_pkg_detail 
           WHERE cust_bill_pkg_detail.billpkgnum = cust_bill_pkg.billpkgnum),
           cust_bill._date,
           cust_pkg.expire