RT# 74918 - Added total revenue line to Package churn report

diff --git a/FS/FS/Report/Table.pm b/FS/FS/Report/Table.pm
index 5fb5640..e3e0854 100644 (file)
--- 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 (file)
--- 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

diff --git a/httemplate/graph/cust_pkg.html b/httemplate/graph/cust_pkg.html
index 3b6552b..68c5b21 100644 (file)
--- a/httemplate/graph/cust_pkg.html
+++ b/httemplate/graph/cust_pkg.html
@@ -7,12 +7,13 @@
   'links'         => \@links,
   'params'        => \@params,
   'agentnum'      => $agentnum,
-  'sprintf'       => ( $normalize ? '%0.1f%%' : '%u'), 
+  'sprintf'       => ( $normalize ? '%0.1f%%' : '%u'),
+  'sprintf_fields' => $sprintf_fields,
   'normalize'     => ( $normalize ? 0 : undef ),
   'disable_money' => 1,
   'remove_empty'  => (scalar(@group_keys) > 1 ? 1 : 0),
   'nototal'       => 1,
-  'no_graph'      => [ 1, 0, 0, 0, 0 ], # don't graph 'active'
+  'no_graph'      => [ 1, 0, 0, 0, 0, 1 ], # don't graph 'active, total_revenue'
 &>
 <%init>
 
@@ -33,7 +34,7 @@ if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
 
 my $agentname = $agent ? $agent->agent.' ' : '';
 
-my @base_items = qw( active_pkg setup_pkg susp_pkg unsusp_pkg cancel_pkg );
+my @base_items = qw( active_pkg setup_pkg susp_pkg unsusp_pkg cancel_pkg total_revenue_pkg );
 
 my %base_labels = (
   'active_pkg' => 'Active packages',
@@ -41,6 +42,7 @@ my %base_labels = (
   'susp_pkg'   => 'Suspensions',
   'unsusp_pkg' => 'Unsuspensions',
   'cancel_pkg' => 'Cancellations',
+  'total_revenue_pkg' => 'Total Revenue'
 );
 
 my %base_colors = (
@@ -49,8 +51,13 @@ my %base_colors = (
   'susp_pkg'    => 'ff9900', #yellow
   'unsusp_pkg'  => '44ff44', #light green
   'cancel_pkg'  => 'cc0000', #red 
+  'total_revenue_pkg'  => '0000ff', #blue
 );
 
+my $sprintf_fields = {
+  'total_revenue_pkg' => '%.2f', #format to 2 decimal places
+};
+
 my %base_links;
 foreach my $status (qw(active setup cancel susp unsusp)) {
   $base_links{$status.'_pkg'} =

diff --git a/httemplate/graph/elements/monthly.html b/httemplate/graph/elements/monthly.html
index 1a94281..cfe5a3c 100644 (file)
--- a/httemplate/graph/elements/monthly.html
+++ b/httemplate/graph/elements/monthly.html
@@ -59,6 +59,7 @@ Example:
             'no_graph'      => \@no_graph,
             'bottom_link'   => \@bottom_link,
             'transpose'     => $opt{'daily'},
+            'sprintf_fields' => $sprintf_fields,
             map { $_, $opt{$_} } (qw(title
                                     nototal
                                     graph_type
@@ -79,6 +80,7 @@ my $fromparam = $opt{'link_fromparam'} || 'begin';
 my $toparam   = $opt{'link_toparam'} || 'end';
 
 my @items = @{ $opt{'items'} };
+my $sprintf_fields = $opt{'sprintf_fields'};
 
 foreach my $other (qw( labels graph_labels colors links )) {
   if ( ref($opt{$other}) eq 'HASH' ) {

diff --git a/httemplate/graph/elements/report.html b/httemplate/graph/elements/report.html
index b5d2148..70c3a9e 100644 (file)
--- a/httemplate/graph/elements/report.html
+++ b/httemplate/graph/elements/report.html
@@ -249,7 +249,7 @@ any delimiter and linked from the elements in @data.
 %   my $e = 0;
 %   foreach ( @$data_row ) {
 %     my $entry = $_;
-%     $entry = $money_char . sprintf($sprintf, $entry);
+%     $entry = $money_char . sprintf($sprintf_fields->{$row} ? $sprintf_fields->{$row} : $sprintf, $entry);
 %     $entry = $link_prefix . shift(@$links) . "\">$entry</A>" if $link_prefix;
 %     push @{$cell[$i]}, $entry;
 %     $bottom_total[$e++] += $_ unless $opt{no_graph}[$i-1];
@@ -343,6 +343,7 @@ my $conf = new FS::Conf;
 my $money_char = $opt{'disable_money'} ? '' : $conf->config('money_char');
 
 my @items = @{ $opt{'items'} };
+my $sprintf_fields = $opt{'sprintf_fields'};
 
 foreach my $other (qw( col_labels row_labels graph_labels axis_labels colors links )) {
   if ( ref($opt{$other}) eq 'HASH' ) {