+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);
+}
+