+}
+
+sub cust_bill_pkg_recur {
+ my $self = shift;
+ my ($speriod, $eperiod, $agentnum, %opt) = @_;
+
+ # subtract all usage from the line item regardless of date
+ my $item_usage;
+ if ( $opt{'project'} ) {
+ $item_usage = 'usage'; #already calculated
+ }
+ else {
+ $item_usage = '( SELECT COALESCE(SUM(cust_bill_pkg_detail.amount),0)
+ FROM cust_bill_pkg_detail
+ WHERE cust_bill_pkg_detail.billpkgnum = cust_bill_pkg.billpkgnum )';
+ }
+
+ my $cust_bill_pkg = $opt{'project'} ? 'v_cust_bill_pkg' : 'cust_bill_pkg';
+
+ my $recur_fraction = '';
+ if ($opt{'distribute'}) {
+ # the fraction of edate - sdate that's within [speriod, eperiod]
+ $recur_fraction = " *
+ CAST(LEAST($eperiod, $cust_bill_pkg.edate) -
+ GREATEST($speriod, $cust_bill_pkg.sdate) AS DECIMAL) /
+ ($cust_bill_pkg.edate - $cust_bill_pkg.sdate)";
+ }
+
+ my $total_sql =
+ "SELECT COALESCE(SUM(($cust_bill_pkg.recur - $item_usage) $recur_fraction),0)" .
+ $self->_cust_bill_pkg_recurring(@_);
+
+ $self->scalar_sql($total_sql);
+}
+
+sub cust_bill_pkg_count_pkgnum {
+ # for ARPU calculation
+ my $self = shift;
+ my $total_sql = 'SELECT COUNT(DISTINCT pkgnum) '.
+ $self->_cust_bill_pkg_recurring(@_);
+