X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=FS%2FFS%2FReport%2FTable.pm;h=0a0d24a7985ac1c4f4adbbcf93451199a35931d4;hb=9f4410954ff03fb12eade34f92c31a5ce9018f4b;hp=98f66e9041dd12f3d728728d6c5facecb737fa90;hpb=69bdaccf38c8f1b7471ff13354ccbcbb6aa20096;p=freeside.git

diff --git a/FS/FS/Report/Table.pm b/FS/FS/Report/Table.pm
index 98f66e904..0a0d24a79 100644
--- a/FS/FS/Report/Table.pm
+++ b/FS/FS/Report/Table.pm
@@ -266,6 +266,36 @@ sub netrefunds {
   );
 }
 
+=item discounted: The sum of discounts on invoices in the period.
+
+=cut
+
+sub discounted {
+  my( $self, $speriod, $eperiod, $agentnum, %opt) = @_;
+  $self->scalar_sql('SELECT SUM(cust_bill_pkg_discount.amount)
+    FROM cust_bill_pkg_discount
+      JOIN cust_bill_pkg USING  ( billpkgnum )
+      JOIN cust_bill     USING  ( invnum )
+      JOIN cust_main     USING  ( custnum )
+    WHERE '. $self->in_time_period_and_agent( $speriod,
+                                              $eperiod,
+                                              $agentnum,
+                                              'cust_bill._date'
+                                            ).
+              $self->for_opts(%opt)
+  );
+}
+
+=item gross: invoiced + discounted
+
+=cut
+
+sub gross {
+  my( $self, $speriod, $eperiod, $agentnum, %opt) = @_;
+    $self->invoiced(   $speriod, $eperiod, $agentnum, %opt)
+  + $self->discounted( $speriod, $eperiod, $agentnum, %opt);
+}
+
 #XXX docs
 
 #these should be auto-generated or $AUTOLOADed or something
@@ -409,8 +439,8 @@ sub cust_pkg_recur_cost {
 
 =item cust_bill_pkg: the total package charges on invoice line items.
 
-'charges': limit the type of charges included (setup, recur, usage).
-Should be a string containing one or more of 'S', 'R', or 'U'; if 
+'charges': limit the type of charges included (setup, recur, usage, discount).
+Should be a string containing one or more of 'S', 'R', 'U', or 'D'; if 
 unspecified, defaults to all three.
 
 'classnum': limit to this package class.
@@ -440,6 +470,7 @@ sub cust_bill_pkg {
   $sum += $self->cust_bill_pkg_setup(@_) if $charges{S};
   $sum += $self->cust_bill_pkg_recur(@_) if $charges{R};
   $sum += $self->cust_bill_pkg_detail(@_) if $charges{U};
+  $sum += $self->cust_bill_pkg_discount(@_) if $charges{D};
 
   if ($opt{'average_per_cust_pkg'}) {
     my $count = $self->cust_bill_pkg_count_pkgnum(@_);
@@ -495,12 +526,17 @@ sub _cust_bill_pkg_recurring {
 
   my @where = (
     '(pkgnum != 0 OR feepart IS NOT NULL)',
-    $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
     $self->with_report_option(%opt),
     $self->with_refnum(%opt),
     $self->with_cust_classnum(%opt)
   );
 
+  my $where_classnum = $self->with_classnum($opt{'classnum'}, $opt{'use_override'});
+  if ($opt{'project'}) {
+    $where_classnum =~ s/\bcust_bill_pkg/v_cust_bill_pkg/g;
+  }
+  push @where, $where_classnum;
+
   if ( $opt{'distribute'} ) {
     $where[0] = 'pkgnum != 0'; # specifically exclude fees
     push @where, "cust_main.agentnum = $agentnum" if $agentnum;
@@ -621,80 +657,34 @@ 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+)$/ ) {
-  #  if ( $1 == 0 ) {
-  #    $comparison = "IS NULL";
-  #  } else {
-  #    $comparison = "= $1";
-  #  }
-  #
-  #  if ( $opt{'use_override'} ) {
-  #    $where = "(
-  #      part_pkg.classnum $comparison AND pkgpart_override IS NULL OR
-  #      override.classnum $comparison AND pkgpart_override IS NOT NULL
-  #    )";
-  #  } else {
-  #    $where = "part_pkg.classnum $comparison";
-  #  }
-  #}
+  my $self = shift;
+  my ($speriod, $eperiod, $agentnum, %opt) = @_;
+  # apply all the same constraints here as for setup/recur
 
   $agentnum ||= $opt{'agentnum'};
 
-  my $total_sql =
-    " SELECT COALESCE( SUM( cust_bill_pkg_discount.amount ), 0 ) ";
-
-  $total_sql .=
-    " FROM cust_bill_pkg_discount
-        LEFT JOIN cust_bill_pkg USING ( billpkgnum )
-        LEFT JOIN cust_bill USING ( invnum )
-        LEFT JOIN cust_main USING ( custnum )
-      WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum);
-  #      LEFT JOIN cust_pkg_discount USING ( pkgdiscountnum )
-  #      LEFT JOIN discount USING ( discountnum )
-  #      LEFT JOIN cust_pkg USING ( pkgnum )
-  #      LEFT JOIN part_pkg USING ( pkgpart )
-  #      LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart
-  
-  return $self->scalar_sql($total_sql);
-
-}
-
-sub pkg_field_where {
-  my( $self, $field, $speriod, $eperiod, $agentnum, %opt ) = @_;
-  # someday this will use an aggregate query and return all the columns
-  # at once
-  # and I will drive a Tesla and have a live-in sushi chef who is also a 
-  # ninja bodyguard
   my @where = (
-    $self->in_time_period_and_agent($speriod,
-                                    $eperiod,
-                                    $agentnum,
-                                    "cust_pkg.$field",
-                                   ),
+    '(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),
     $self->with_refnum(%opt),
-    $self->with_towernum(%opt),
-    $self->with_zip(%opt),
-    # can't use with_classnum here...
+    $self->with_cust_classnum(%opt)
   );
-  if ($opt{classnum}) {
-    my $classnum = $opt{classnum};
-    $classnum = [ $classnum ] if !ref($classnum);
-    @$classnum = grep /^\d+$/, @$classnum;
-    my $in = 'IN ('. join(',', @$classnum). ')';
-    push @where, "COALESCE(part_pkg.classnum, 0) $in" if scalar @$classnum;
-  }
 
-  ' WHERE ' . join(' AND ', grep $_, @where);
+  my $total_sql = "SELECT COALESCE(SUM(cust_bill_pkg_discount.amount), 0)
+  FROM cust_bill_pkg_discount
+  JOIN cust_bill_pkg USING (billpkgnum)
+  $cust_bill_pkg_join
+  WHERE " . join(' AND ', grep $_, @where);
+
+  $self->scalar_sql($total_sql);
 }
 
-=item setup_pkg: The number of packages with setup dates in the period.
+##### package churn report #####
 
-This excludes packages created by package changes. Options:
+=item active_pkg: The number of packages that were active at the start of 
+the period. The end date of the period is ignored. Options:
 
 - refnum: Limit to customers with this advertising source.
 - classnum: Limit to packages with this class.
@@ -704,61 +694,170 @@ This excludes packages created by package changes. Options:
 Except for zip, any of these can be an arrayref to allow multiple values for
 the field.
 
-=item susp_pkg: The number of suspended packages that were last suspended
-in the period. Options are as for setup_pkg.
+=item setup_pkg: The number of packages with setup dates in the period. This 
+excludes packages created by package changes. Options are as for active_pkg.
+
+=item susp_pkg: The number of packages that were suspended in the period
+(and not canceled).  Options are as for active_pkg.
+
+=item unsusp_pkg: The number of packages that were unsuspended in the period.
+Options are as for active_pkg.
 
 =item cancel_pkg: The number of packages with cancel dates in the period.
 Excludes packages that were canceled to be changed to a new package. Options
-are as for setup_pkg.
+are as for active_pkg.
 
 =cut
 
+sub active_pkg {
+  my $self = shift;
+  $self->churn_pkg('active', @_);
+}
+
 sub setup_pkg {
   my $self = shift;
-  my $sql = 'SELECT COUNT(*) FROM cust_pkg
-              LEFT JOIN part_pkg USING (pkgpart)
-              LEFT JOIN cust_main USING (custnum)'.
-              $self->pkg_field_where('setup', @_) .
-              ' AND change_pkgnum IS NULL';
+  $self->churn_pkg('setup', @_);
+}
 
-  $self->scalar_sql($sql);
+sub cancel_pkg {
+  my $self = shift;
+  $self->churn_pkg('cancel', @_);
 }
 
 sub susp_pkg {
-  # number of currently suspended packages that were suspended in the period
   my $self = shift;
-  my $sql = 'SELECT COUNT(*) FROM cust_pkg
-              LEFT JOIN part_pkg USING (pkgpart)
-              LEFT JOIN cust_main USING (custnum) '.
-              $self->pkg_field_where('susp', @_);
+  $self->churn_pkg('susp', @_);
+}
 
-  $self->scalar_sql($sql);
+sub unsusp_pkg {
+  my $self = shift;
+  $self->churn_pkg('unsusp', @_);
 }
 
-sub cancel_pkg {
-  # number of packages canceled in the period and not changed to another
-  # package
+sub churn_pkg {
   my $self = shift;
-  my $sql = 'SELECT COUNT(*) FROM cust_pkg
-              LEFT JOIN part_pkg USING (pkgpart)
-              LEFT JOIN cust_main USING (custnum)
-              LEFT JOIN cust_pkg changed_to_pkg ON(
-                cust_pkg.pkgnum = changed_to_pkg.change_pkgnum
-              ) '.
-              $self->pkg_field_where('cancel', @_) .
-              ' AND changed_to_pkg.pkgnum IS NULL';
+  my ( $status, $speriod, $eperiod, $agentnum, %opt ) = @_;
+  my ($from, @where) =
+    FS::h_cust_pkg->churn_fromwhere_sql( $status, $speriod, $eperiod);
+
+  push @where, $self->pkg_where(%opt, 'agentnum' => $agentnum);
+
+  my $sql = "SELECT COUNT(*) FROM $from
+    JOIN part_pkg ON (cust_pkg.pkgpart = part_pkg.pkgpart)
+    JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)";
+  $sql .= ' WHERE '.join(' AND ', @where)
+    if scalar(@where);
 
   $self->scalar_sql($sql);
 }
 
-#this is going to be harder..
-#sub unsusp_pkg {
-#  my( $self, $speriod, $eperiod, $agentnum ) = @_;
-#  $self->scalar_sql("
-#    SELECT COUNT(*) FROM h_cust_pkg
-#      WHERE 
-#
-#}
+sub pkg_where {
+  my $self = shift;
+  my %opt = @_;
+  my @where = (
+    "part_pkg.freq != '0'",
+    $self->with_refnum(%opt),
+    $self->with_towernum(%opt),
+    $self->with_zip(%opt),
+  );
+  if ($opt{agentnum} =~ /^(\d+)$/) {
+    push @where, "cust_main.agentnum = $1";
+  }
+  if ($opt{classnum}) {
+    my $classnum = $opt{classnum};
+    $classnum = [ $classnum ] if !ref($classnum);
+    @$classnum = grep /^\d+$/, @$classnum;
+    my $in = 'IN ('. join(',', @$classnum). ')';
+    push @where, "COALESCE(part_pkg.classnum, 0) $in" if scalar @$classnum;
+  }
+  @where;
+}
+
+##### end of package churn report stuff #####
+
+##### customer churn report #####
+
+=item active_cust: The number of customers who had any active recurring 
+packages at the start of the period. The end date is ignored, agentnum is 
+mandatory, and no other parameters are accepted.
+
+=item started_cust: The number of customers who had no active packages at 
+the start of the period, but had active packages at the end. Like
+active_cust, agentnum is mandatory and no other parameters are accepted.
+
+=item suspended_cust: The number of customers who had active packages at
+the start of the period, and at the end had no active packages but some
+suspended packages. Note that this does not necessarily mean that their 
+packages were suspended during the period.
+
+=item resumed_cust: The inverse of suspended_cust: the number of customers
+who had suspended packages and no active packages at the start of the 
+period, and active packages at the end.
+
+=item cancelled_cust: The number of customers who had active packages
+at the start of the period, and only cancelled packages at the end.
+
+=cut
+
+sub active_cust {
+  my $self = shift;
+  $self->churn_cust(@_)->{active};
+}
+sub started_cust {
+  my $self = shift;
+  $self->churn_cust(@_)->{started};
+}
+sub suspended_cust {
+  my $self = shift;
+  $self->churn_cust(@_)->{suspended};
+}
+sub resumed_cust {
+  my $self = shift;
+  $self->churn_cust(@_)->{resumed};
+}
+sub cancelled_cust {
+  my $self = shift;
+  $self->churn_cust(@_)->{cancelled};
+}
+
+sub churn_cust {
+  my $self = shift;
+  my ( $speriod ) = @_;
+
+  # run one query for each interval
+  return $self->{_interval}{$speriod} ||= $self->calculate_churn_cust(@_);
+}
+
+sub calculate_churn_cust {
+  my $self = shift;
+  my ($speriod, $eperiod, $agentnum, %opt) = @_;
+
+  my $churn_sql = FS::cust_main::Status->churn_sql($speriod, $eperiod);
+  my $where = '';
+  $where = " WHERE cust_main.agentnum = $agentnum " if $agentnum;
+  my $cust_sql =
+    "SELECT churn.* ".
+    "FROM cust_main JOIN ($churn_sql) AS churn USING (custnum)".
+    $where;
+
+  # query to count the ones with certain status combinations
+  my $total_sql = "
+    SELECT SUM((s_active > 0)::int)                   as active,
+           SUM((s_active = 0 and e_active > 0)::int)  as started,
+           SUM((s_active > 0 and e_active = 0 and e_suspended > 0)::int)
+                                                      as suspended,
+           SUM((s_active = 0 and s_suspended > 0 and e_active > 0)::int)
+                                                      as resumed,
+           SUM((s_active > 0 and e_active = 0 and e_suspended = 0)::int)
+                                                      as cancelled
+    FROM ($cust_sql) AS x
+  ";
+
+  my $sth = dbh->prepare($total_sql);
+  $sth->execute or die "failed to execute churn query: " . $sth->errstr;
+
+  $self->{_interval}{$speriod} = $sth->fetchrow_hashref;
+}
 
 sub in_time_period_and_agent {
   my( $self, $speriod, $eperiod, $agentnum ) = splice(@_, 0, 4);
@@ -802,16 +901,27 @@ sub with_classnum {
 
   $classnum = [ $classnum ] if !ref($classnum);
   @$classnum = grep /^\d+$/, @$classnum;
+  return '' if !@$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 ) {
-    $expr .= "
-      OR ( COALESCE(override.classnum, 0) $in AND pkgpart_override IS NOT NULL )";
+    # then include packages if their base package is in the set and they are 
+    # not overridden,
+    # or if they are overridden and their override package is in the set,
+    # or fees if they are in the set
+    return "(
+         ( COALESCE(part_pkg.classnum, 0) $in AND cust_pkg.pkgpart IS NOT NULL AND pkgpart_override IS NULL )
+      OR ( COALESCE(override.classnum, 0) $in AND pkgpart_override IS NOT NULL )
+      OR ( COALESCE(part_fee.classnum, 0) $in AND cust_bill_pkg.feepart IS NOT NULL )
+    )";
+  } else {
+    # include packages if their base package is in the set,
+    # or fees if they are in the set
+    return "(
+         ( COALESCE(part_pkg.classnum, 0) $in AND cust_pkg.pkgpart IS NOT NULL )
+      OR ( COALESCE(part_fee.classnum, 0) $in AND cust_bill_pkg.feepart IS NOT NULL )
+    )";
   }
-  "( $expr )";
 }
 
 sub with_usageclass {