diff options
author | Christopher Burger <burgerc@freeside.biz> | 2017-10-17 09:41:12 -0400 |
---|---|---|
committer | Christopher Burger <burgerc@freeside.biz> | 2017-12-13 14:38:11 -0500 |
commit | 08ac1d0dc44626f442f8287c497eca9e98ab92b7 (patch) | |
tree | 2e1cd043635c7dd66fb08f5f06ee82210f57e269 /FS/FS | |
parent | eb95e404a35f9f1b7125e67d1e539ada860fcdf0 (diff) |
RT# 78019 - Added total revenue line to Package churn report
Diffstat (limited to 'FS/FS')
-rw-r--r-- | FS/FS/Report/Table.pm | 48 | ||||
-rw-r--r-- | FS/FS/h_cust_pkg.pm | 6 |
2 files changed, 51 insertions, 3 deletions
diff --git a/FS/FS/Report/Table.pm b/FS/FS/Report/Table.pm index 5fb5640..e3e0854 100644 --- a/FS/FS/Report/Table.pm +++ b/FS/FS/Report/Table.pm @@ -863,6 +863,54 @@ sub unsusp_pkg { $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 = sprintf("%.2f", $active_revenue + $setup_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 + 5 > revenue.history_date) + "; + } + 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 + 15 > revenue.history_date) + "; + } + + $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 ) = @_; diff --git a/FS/FS/h_cust_pkg.pm b/FS/FS/h_cust_pkg.pm index 423b442..f074647 100644 --- a/FS/FS/h_cust_pkg.pm +++ b/FS/FS/h_cust_pkg.pm @@ -140,9 +140,9 @@ sub churn_fromwhere_sql { # XXX or should we include if they were created by a pkgpart change? $from = "cust_pkg"; @where = ( - "setup >= $speriod", - "setup < $eperiod", - "change_pkgnum IS NULL" + "cust_pkg.setup >= $speriod", + "cust_pkg.setup < $eperiod", + "cust_pkg.change_pkgnum IS NULL" ); } elsif ( $status eq 'cancel' ) { # also simple, because packages should only be canceled once |