);
}
+=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
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;
}
-##### churn report #####
+##### package churn report #####
=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:
@where;
}
-##### end of churn report stuff #####
+##### 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);
@$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 ) {
- $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 {
projects / freeside.git / blobdiff