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';
}
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)
+
+ 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)
);
}
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, discount).
-Should be a string containing one or more of 'S', 'R', 'U', or 'D'; 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_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(@_);
$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 = shift;
my ($speriod, $eperiod, $agentnum, %opt) = @_;
$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(
+ 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);
+
+ $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);
+}
+
+#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 = (
+ '(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_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);
+}
+
##### package churn report #####
=item active_pkg: The number of packages that were active at the start of
$self->churn_pkg('unsusp', @_);
}
+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 ($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 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 ( $status, $speriod, $eperiod, $agentnum, %opt ) = @_;
projects / freeside.git / blobdiff