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).
+ #in practice, but not appearance, paid_sql accepts end before start
+ FS::cust_bill_pkg->paid_sql($eperiod, $speriod, %opt).
') FROM cust_bill_pkg';
}
);
}
+=item discounted: The sum of discounts on invoices in the period.
+
+=cut
+
+sub discounted {
+ my( $self, $speriod, $eperiod, $agentnum, %opt) = @_;
+
+ my $sql = 'SELECT SUM(';
+ if ($opt{'setuprecur'}) {
+ # (This isn't exact but it works in most cases.)
+ # When splitting into setup/recur values,
+ # if the discount is allowed to apply to setup fees (discount.setup = 'Y')
+ # then split it between the "setup" and "recurring" rows in proportion to
+ # the "unitsetup" and "unitrecur" fields of the line item.
+ $sql .= <<EOF;
+CASE
+ WHEN discount.setup = 'Y'
+ AND ((COALESCE(cust_bill_pkg.unitsetup,0) > 0)
+ OR (COALESCE(cust_bill_pkg.unitrecur,0) > 0))
+ THEN
+EOF
+ if ($opt{'setuprecur'} eq 'setup') {
+ $sql .= ' (COALESCE(cust_bill_pkg.unitsetup,0)';
+ } elsif ($opt{'setuprecur'} eq 'recur') {
+ $sql .= ' (COALESCE(cust_bill_pkg.unitrecur,0)';
+ } else {
+ die 'Unrecognized value for setuprecur';
+ }
+ $sql .= ' / (COALESCE(cust_bill_pkg.unitsetup,0) + COALESCE(cust_bill_pkg.unitrecur,0)))';
+ $sql .= " * cust_bill_pkg_discount.amount\n";
+ # Otherwise, show it all as "recurring"
+ if ($opt{'setuprecur'} eq 'setup') {
+ $sql .= " ELSE 0\n";
+ } elsif ($opt{'setuprecur'} eq 'recur') {
+ $sql .= " ELSE cust_bill_pkg_discount.amount\n";
+ }
+ $sql .= "END\n";
+ } else {
+ # simple case, no setuprecur
+ $sql .= "cust_bill_pkg_discount.amount\n";
+ }
+ $sql .= <<EOF;
+) FROM cust_bill_pkg_discount
+ JOIN cust_bill_pkg USING ( billpkgnum )
+ JOIN cust_bill USING ( invnum )
+ JOIN cust_main USING ( custnum )
+EOF
+ if ($opt{'setuprecur'}) {
+ $sql .= <<EOF;
+ JOIN cust_pkg_discount USING ( pkgdiscountnum )
+ LEFT JOIN discount USING ( discountnum )
+EOF
+ }
+ $self->scalar_sql(
+ $sql
+ . '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
timelocal($sec,$min,$hour,$mday,$mon,$year);
}
+=item _subtract_months: subtracts the number of months from a given unix date stamp
+
+=cut
+
+sub _subtract_months {
+ my($self, $number_of_months, $time) = @_;
+ my ($sec,$min,$hour,$mday,$mon,$year) = (localtime($time) )[0,1,2,3,4,5];
+ $mon -= $number_of_months;
+ if ( $mon < 0 ) { $mon+=12; $year--; }
+ timelocal($sec,$min,$hour,$mday,$mon,$year);
+}
+
=item cust_pkg_setup_cost: The total setup costs of packages setup in the period
'classnum': limit to this package class.
=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
-unspecified, defaults to all three.
+'charges': limit the type of charges included (setup, recur, usage, discount, taxes).
+Should be a string containing one or more of 'S', 'R', or 'U'; or 'D' or 'T' (discount
+and taxes should not be combined with the others.) If unspecified, defaults to 'SRU'.
'classnum': limit to this package class.
$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};
+ $sum += $self->cust_bill_pkg_taxes(@_) if $charges{T};
if ($opt{'average_per_cust_pkg'}) {
my $count = $self->cust_bill_pkg_count_pkgnum(@_);
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;
$self->in_time_period_and_agent($speriod, $eperiod, $agentnum, $_date);
}
+ if ( $opt{'custnum'} =~ /^(\d+)$/ ) {
+ push @where, "(cust_main.custnum = $1)";
+ }
+
return "
FROM $cust_bill_pkg
$cust_bill_pkg_join
}
+=item cust_bill_pkg_recur: the total recur charges
+
+Most arguments as for C<cust_bill_pkg>, plus:
+
+'custnum': limit to this customer
+
+'cost': if true, return total recur costs instead
+
+=cut
+
sub cust_bill_pkg_recur {
my $self = shift;
my ($speriod, $eperiod, $agentnum, %opt) = @_;
($cust_bill_pkg.edate - $cust_bill_pkg.sdate)";
}
- my $total_sql =
- "SELECT COALESCE(SUM(($cust_bill_pkg.recur - $item_usage) $recur_fraction),0)" .
- $self->_cust_bill_pkg_recurring(@_);
+ my $total_sql = $opt{'cost'}
+ ? "SELECT SUM(part_pkg.recur_cost)"
+ : "SELECT COALESCE(SUM(($cust_bill_pkg.recur - $item_usage) $recur_fraction),0)";
+
+ $total_sql .= $self->_cust_bill_pkg_recurring(@_);
$self->scalar_sql($total_sql);
}
=item cust_bill_pkg_detail: the total usage charges in detail lines.
-Arguments as for C<cust_bill_pkg>, plus:
+Most arguments as for C<cust_bill_pkg>, plus:
'usageclass': limit to this usage class number.
+'custnum': limit to this customer
+
+'cost': if true, return total usage costs instead
+
=cut
sub cust_bill_pkg_detail {
);
}
+ if ( $opt{'custnum'} =~ /^(\d+)$/ ) {
+ push @where, "(cust_main.custnum = $1)";
+ }
+
my $total_sql = " SELECT SUM(cust_bill_pkg_detail.amount) ";
+ my $extra_join = '';
+ if ($opt{'cost'}) {
+ $extra_join = " JOIN cdr USING ( detailnum ) ";
+ $total_sql = " SELECT SUM(cdr.rated_cost) ";
+ }
$total_sql .=
" FROM 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 );
+ LEFT JOIN part_fee USING ( feepart )
+ ".$extra_join.
+ " WHERE ".join( ' AND ', grep $_, @where );
$self->scalar_sql($total_sql);
}
+=item cust_bill_pkg_discount: Discounts issued
+
+Arguments: agentnum, refnum, cust_classnum
+
+=cut
+
sub cust_bill_pkg_discount {
- my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
+ my $self = shift;
+ my ($speriod, $eperiod, $agentnum, %opt) = @_;
+ # apply all the same constraints here as for setup/recur
- #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";
- # }
- #}
+ $agentnum ||= $opt{'agentnum'};
+
+ my @where = (
+ '(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_cust_classnum(%opt)
+ );
+
+ 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 cust_bill_pkg_discount_or_waived: Discounts and waived fees issued
+
+Arguments: agentnum, refnum, cust_classnum
+
+=cut
+
+sub cust_bill_pkg_discount_or_waived {
+
+ my $self = shift;
+ my ($speriod, $eperiod, $agentnum, %opt) = @_;
$agentnum ||= $opt{'agentnum'};
- my $total_sql =
- " SELECT COALESCE( SUM( cust_bill_pkg_discount.amount ), 0 ) ";
+ my $total_sql = "
+ SELECT
+ COALESCE(
+ SUM(
+ COALESCE(
+ cust_bill_pkg_discount.amount,
+ CAST(( SELECT optionvalue
+ FROM part_pkg_option
+ WHERE
+ part_pkg_option.pkgpart = cust_pkg.pkgpart
+ AND optionname = 'setup_fee'
+ ) AS NUMERIC )
+ )
+ ),
+ 0
+ )
+ FROM cust_bill_pkg
+ LEFT JOIN cust_bill_pkg_discount USING (billpkgnum)
+ LEFT JOIN cust_pkg ON cust_bill_pkg.pkgnum = cust_pkg.pkgnum
+ LEFT JOIN part_pkg USING (pkgpart)
+ LEFT JOIN cust_bill USING ( invnum )
+ LEFT JOIN cust_main ON cust_pkg.custnum = cust_main.custnum
+ WHERE
+ (
+ cust_bill_pkg_discount.billpkgdiscountnum IS NOT NULL
+ OR (
+ cust_pkg.setup = cust_bill_pkg.sdate
+ AND cust_pkg.waive_setup = 'Y'
+ )
+ )
+ AND cust_bill_pkg.pkgpart_override IS NULL
+ " . join "\n",
+ map { " AND ( $_ ) " }
+ grep { $_ }
+ $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
+ $self->with_report_option(%opt),
+ $self->in_time_period_and_agent($speriod, $eperiod, $agentnum);
- $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);
+ $self->scalar_sql($total_sql);
+}
+sub cust_bill_pkg_taxes {
+ my $self = shift;
+ my ($speriod, $eperiod, $agentnum, %opt) = @_;
+
+ $agentnum ||= $opt{'agentnum'};
+
+ my @where = (
+ '(cust_bill_pkg.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_cust_classnum(%opt)
+ );
+
+ my $total_sql = "SELECT COALESCE(SUM(cust_bill_pkg_tax_location.amount),0)
+ FROM cust_bill_pkg
+ $cust_bill_pkg_join
+ LEFT JOIN cust_bill_pkg_tax_location
+ ON (cust_bill_pkg.billpkgnum = cust_bill_pkg_tax_location.taxable_billpkgnum)
+ WHERE " . join(' AND ', grep $_, @where);
+
+ $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
+#all credits applied to matching pkg line items (ie not taxes)
+
+sub cust_bill_pkg_credits {
+ my $self = shift;
+ my ($speriod, $eperiod, $agentnum, %opt) = @_;
+
+ $agentnum ||= $opt{'agentnum'};
+
my @where = (
- $self->in_time_period_and_agent($speriod,
- $eperiod,
- $agentnum,
- "cust_pkg.$field",
- ),
+ '(cust_bill_pkg.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_credit_bill_pkg.amount),0)
+ FROM cust_bill_pkg
+ $cust_bill_pkg_join
+ LEFT JOIN cust_credit_bill_pkg
+ USING ( billpkgnum )
+ 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.
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 total_revenue_pkg {
+ my $self = shift;
+ my $active_revenue = $self->revenue_pkg('active', @_);
+ my $setup_revenue = $self->revenue_pkg('setup', @_);
+ my $return = $active_revenue + $setup_revenue;
+
+ return $return;
+}
+
+sub total_revenue_diff {
+ my $self = shift;
+
+ my @current_month = @_;
+ my @previous_month = @current_month;
+
+ $previous_month[0] = $self->_subtract_months(1,$current_month[0]);
+ $previous_month[1] = $self->_subtract_months(1,$current_month[1]);
+
+ my $previous_revenue = $self->revenue_pkg('active', @previous_month) + $self->revenue_pkg('setup', @previous_month);
+ my $current_revenue = $self->revenue_pkg('active', @current_month) + $self->revenue_pkg('setup', @current_month);
+
+ my $return = $current_revenue - $previous_revenue;
+
+ return $return;
+}
+
+sub revenue_pkg {
+ my $self = shift;
+ my ( $status, $speriod, $eperiod, $agentnum, %opt ) = @_;
+ my $totalrevenue;
+
+ my ($from, @where) =
+ FS::h_cust_pkg->churn_fromwhere_sql( $status, $speriod, $eperiod);
+
+ push @where, $self->pkg_where(%opt, 'agentnum' => $agentnum);
+
+ my $sql;
+
+## if package has changed and has not reached next due date it will not be in h_cust_bill.
+## this causes problems with future months, needed to use change_pkgnum instead.
+
+ if ($status eq "active") {
+ $sql = "SELECT DISTINCT ON (revenue.pkgnum) revenue.pkgnum AS pkgnum, revenue.recur AS revenue
+ FROM $from
+ JOIN part_pkg ON (cust_pkg.pkgpart = part_pkg.pkgpart)
+ JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
+ JOIN h_cust_bill_pkg AS revenue ON ((cust_pkg.pkgnum = revenue.pkgnum OR cust_pkg.change_pkgnum = revenue.pkgnum) AND cust_pkg.history_date < $speriod )
+ ";
+ }
+ elsif ($status eq "setup") {
+ $sql = "SELECT DISTINCT ON (revenue.pkgnum) revenue.pkgnum AS pkgnum, revenue.setup AS revenue
+ FROM $from
+ JOIN part_pkg ON (cust_pkg.pkgpart = part_pkg.pkgpart)
+ JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
+ JOIN h_cust_bill_pkg AS revenue ON (cust_pkg.pkgnum = revenue.pkgnum AND
+ ( cust_pkg.setup > $speriod AND cust_pkg.setup < $eperiod) )
+ ";
+ }
+
+ $sql .= ' WHERE '.join(' AND ', @where)
+ if scalar(@where);
+
+ $sql .= "ORDER BY revenue.pkgnum ASC, revenue.history_date DESC";
+
+ my $revenue_sql = "SELECT sum(rev.revenue) AS total_revenue FROM ( $sql ) AS rev";
+
+ $self->scalar_sql($revenue_sql);
+}
+
+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((e_active = 0 and e_cancelled > s_cancelled)::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 = [ $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 {