+#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
+the period. The end date of the period is ignored. Options:
+
+- refnum: Limit to customers with this advertising source.
+- classnum: Limit to packages with this class.
+- towernum: Limit to packages that have a broadband service with this tower.
+- zip: Limit to packages with this service location zip code.
+
+Except for zip, any of these can be an arrayref to allow multiple values for
+the field.
+
+=item setup_pkg: The number of packages with setup dates in the period. This
+excludes packages created by package changes. Options are as for active_pkg.
+
+=item susp_pkg: The number of packages that were suspended in the period
+(and not canceled). Options are as for active_pkg.
+
+=item unsusp_pkg: The number of packages that were unsuspended in the period.
+Options are as for active_pkg.
+
+=item cancel_pkg: The number of packages with cancel dates in the period.
+Excludes packages that were canceled to be changed to a new package. Options
+are as for active_pkg.
+
+=cut
+
+sub active_pkg {
+ my $self = shift;
+ $self->churn_pkg('active', @_);
+}
+
+sub setup_pkg {
+ my $self = shift;
+ $self->churn_pkg('setup', @_);
+}
+
+sub cancel_pkg {
+ my $self = shift;
+ $self->churn_pkg('cancel', @_);
+}
+
+sub susp_pkg {
+ my $self = shift;
+ $self->churn_pkg('susp', @_);
+}
+
+sub unsusp_pkg {
+ my $self = shift;
+ $self->churn_pkg('unsusp', @_);
+}
+
+sub churn_pkg {
+ my $self = shift;
+ my ( $status, $speriod, $eperiod, $agentnum, %opt ) = @_;
+ my ($from, @where) =
+ FS::h_cust_pkg->churn_fromwhere_sql( $status, $speriod, $eperiod);
+
+ push @where, $self->pkg_where(%opt, 'agentnum' => $agentnum);
+
+ my $sql = "SELECT COUNT(*) FROM $from
+ JOIN part_pkg ON (cust_pkg.pkgpart = part_pkg.pkgpart)
+ JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)";
+ $sql .= ' WHERE '.join(' AND ', @where)
+ if scalar(@where);
+
+ $self->scalar_sql($sql);
+}
+
+sub pkg_where {
+ my $self = shift;
+ my %opt = @_;
+ my @where = (
+ "part_pkg.freq != '0'",
+ $self->with_refnum(%opt),
+ $self->with_towernum(%opt),
+ $self->with_zip(%opt),