diff options
| -rw-r--r-- | FS/FS/Report/Table.pm | 48 | ||||
| -rw-r--r-- | FS/FS/h_cust_pkg.pm | 6 | ||||
| -rw-r--r-- | httemplate/graph/cust_pkg.html | 13 | ||||
| -rw-r--r-- | httemplate/graph/elements/monthly.html | 2 | ||||
| -rw-r--r-- | httemplate/graph/elements/report.html | 3 |
5 files changed, 65 insertions, 7 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 diff --git a/httemplate/graph/cust_pkg.html b/httemplate/graph/cust_pkg.html index 3b6552b..68c5b21 100644 --- 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 --- 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 --- 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' ) { |