# they're not applicable to pkg_tax search
sub { my $cust_bill_pkg = shift;
if ( $unearned ) {
- my $period =
- $cust_bill_pkg->edate - $cust_bill_pkg->sdate;
- my $elapsed = $unearned - $cust_bill_pkg->sdate;
- $elapsed = 0 if $elapsed < 0;
- my $remaining = 1 - $elapsed/$period;
- my $base = ($unearned_mode eq 'paid' ? 'total_paid' : 'recur');
-
- sprintf($money_char. '%.2f',
- $remaining * $cust_bill_pkg->$base );
+ sprintf($money_char.'%.2f',
+ $cust_bill_pkg->unearned_revenue)
} else {
sprintf($money_char.'%.2f', $cust_bill_pkg->setup );
),
sub { my $row = shift;
my $value = 0;
- if ( $use_usage eq 'recurring' ) {
+ if ( $use_usage eq 'recurring' or $unearned ) {
$value = $row->recur - $row->usage;
} elsif ( $use_usage eq 'usage' ) {
$value = $row->usage;
},
( $unearned
? ( sub { time2str('%b %d %Y', shift->sdate ) },
- sub { time2str('%b %d %Y', shift->edate ) },
+ # shift edate back a day
+ # 82799 = 3600*23 - 1
+ # (to avoid skipping a day during DST)
+ sub { time2str('%b %d %Y', shift->edate - 82799 ) },
)
: ()
),
'',
'setup', #broken in $unearned case i guess
( $unearned ? ('', '') : () ),
- ( $use_usage eq 'recurring' ? 'recur - usage' :
- $use_usage eq 'usage' ? 'usage'
- : 'recur'
+ ( $use_usage eq 'recurring' or $unearned
+ ? 'recur - usage' :
+ $use_usage eq 'usage'
+ ? 'usage'
+ : 'recur'
),
( $unearned ? ('sdate', 'edate') : () ),
'invnum',
my $unearned = '';
my $unearned_mode = '';
my $unearned_base = '';
+my $unearned_sql = '';
-my @select = ( 'cust_bill_pkg.*',
- 'cust_bill._date', );
+my @select = ( 'cust_bill_pkg.*', 'cust_bill._date' );
my ($join_cust, $join_pkg ) = ('', '');
#here is the agent virtualization
push @where, "cust_main.agentnum = $1";
}
+if ( $cgi->param('refnum') =~ /^(\d+)$/ ) {
+ push @where, "cust_main.refnum = $1";
+}
+
#classnum
# not specified: all classes
# 0: empty class
qw( district city county state locationtaxid )
);
-} elsif ( $cgi->param('unearned_now') =~ /^(\d+)$/ ) {
+}
+
+# unearned revenue mode
+if ( $cgi->param('unearned_now') =~ /^(\d+)$/ ) {
$unearned = $1;
$unearned_mode = $cgi->param('mode');
"part_pkg.freq NOT LIKE '%w'";
}
- if ( !$unearned_mode or $unearned_mode eq 'billed' ) {
- $unearned_base = 'cust_bill_pkg.recur';
- }
- elsif ( $unearned_mode eq 'paid' ) {
- $join_pkg .= "JOIN (
- SELECT billpkgnum, SUM(cust_bill_pay_pkg.amount) AS total_paid
- FROM cust_bill_pay_pkg
- JOIN cust_bill_pay USING (billpaynum)
- JOIN cust_pay USING (paynum)
- WHERE cust_bill_pay_pkg.setuprecur = 'recur'
- AND cust_pay._date <= $unearned
- GROUP BY billpkgnum
- ) AS cust_bill_pkg_paid USING (billpkgnum)";
- $unearned_base = 'total_paid';
- push @select, 'total_paid';
+ my $usage_sql = FS::cust_bill_pkg->usage_sql;
+ push @select, "($usage_sql) AS usage"; # we need this
+ my $paid_sql = 'GREATEST(' .
+ FS::cust_bill_pkg->paid_sql($unearned, '', setuprecur => 'recur') .
+ " - $usage_sql, 0)";
+
+ push @select, "$paid_sql AS paid_no_usage"; # need this either way
+
+ if ( $unearned_mode eq 'paid' ) {
+ # then use the amount paid, minus usage charges
+ $unearned_base = $paid_sql;
}
else {
- die "invalid mode '$unearned_mode'";
+ # use the amount billed, minus usage charges and credits
+ $unearned_base = "GREATEST( cust_bill_pkg.recur - ".
+ FS::cust_bill_pkg->credited_sql($unearned, '', setuprecur => 'recur') .
+ " - $usage_sql, 0)";
+ # include only rows that have some non-usage, non-credited portion
}
+ # whatever we're using as the base, only show rows where it's positive
+ push @where, "$unearned_base > 0";
+
+ my $period = "CAST(cust_bill_pkg.edate - cust_bill_pkg.sdate AS REAL)";
+ my $elapsed = "GREATEST( $unearned - cust_bill_pkg.sdate, 0 )";
+ my $remaining = "(1 - $elapsed/$period)";
+
+ $unearned_sql = "CAST( $unearned_base * $remaining AS DECIMAL(10,2) )";
+ push @select, "$unearned_sql AS unearned_revenue";
+
+ # last payment/credit date
+ my %t = (pay => 'cust_bill_pay', credit => 'cust_credit_bill');
+ foreach my $x (qw(pay credit)) {
+ my $table = $t{$x};
+ my $link = $table.'_pkg';
+ my $pkey = dbdef->table($table)->primary_key;
+ my $last_date_sql = "SELECT MAX(_date)
+ FROM $table JOIN $link USING ($pkey)
+ WHERE $link.billpkgnum = cust_bill_pkg.billpkgnum
+ AND $table._date <= $unearned";
+ push @select, "($last_date_sql) AS last_$x";
+ }
+
}
if ( $cgi->param('itemdesc') ) {
$count_query = "SELECT COUNT(DISTINCT billpkgnum), ";
}
- if ( $use_usage eq 'recurring' ) {
- $count_query .= "SUM(setup + recur - usage)";
+ if ( $unearned ) {
+ $count_query .= "SUM( $unearned_base ), SUM( $unearned_sql )";
+ } elsif ( $use_usage eq 'recurring' ) {
+ $count_query .= "SUM(cust_bill_pkg.setup + cust_bill_pkg.recur - usage)";
} elsif ( $use_usage eq 'usage' ) {
$count_query .= "SUM(usage)";
- } elsif ( $unearned ) {
- $count_query .= "SUM($unearned_base)";
} elsif ( scalar( grep( /locationtaxid/, $cgi->param ) ) ) {
$count_query .= "SUM( COALESCE(cust_bill_pkg_tax_rate_location.amount, cust_bill_pkg.setup + cust_bill_pkg.recur))";
} elsif ( $cgi->param('iscredit') eq 'rate') {
$count_query .= "SUM(cust_bill_pkg.setup + cust_bill_pkg.recur)";
}
- if ( $unearned ) {
-
- #false laziness w/report_prepaid_income.cgi
-
- my $float = 'REAL'; #'DOUBLE PRECISION';
-
- my $period = "CAST(cust_bill_pkg.edate - cust_bill_pkg.sdate AS $float)";
- my $elapsed = "(CASE WHEN cust_bill_pkg.sdate > $unearned
- THEN 0
- ELSE ($unearned - cust_bill_pkg.sdate)
- END)";
- #my $elapsed = "CAST($unearned - cust_bill_pkg.sdate AS $float)";
-
- my $remaining = "(1 - $elapsed/$period)";
-
- $count_query .= ", SUM($remaining * $unearned_base)";
-
- }
-
}
-$join_cust = ' JOIN cust_bill USING ( invnum )
- LEFT JOIN cust_main USING ( custnum ) ';
+$join_cust = ' JOIN cust_bill USING ( invnum )
+ LEFT JOIN cust_main USING ( custnum ) ';
if ( $cgi->param('nottax') ) {
'table' => 'cust_bill_pkg',
'addl_from' => "$join_cust $join_pkg",
'hashref' => {},
- 'select' => join(', ', @select ),
+ 'select' => join(",\n", @select ),
'extra_sql' => $where,
'order_by' => 'ORDER BY cust_bill._date, billpkgnum',
};
my $money_char = $conf->config('money_char') || '$';
my $owed_sub = sub {
- $money_char . shift->owed_recur;
+ $money_char . shift->get('owed') # owed_recur is not correct here
};
my $payment_date_sub = sub {
#my $cust_bill_pkg = shift;
projects / freeside.git / blobdiff