my $sql;
+## if package has changed and has not reached next due date it will not be in h_cust_bill.
+## this causes problems with future months, needed to use change_pkgnum instead.
+
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 )
+ JOIN h_cust_bill_pkg AS revenue ON ((cust_pkg.pkgnum = revenue.pkgnum OR cust_pkg.change_pkgnum = revenue.pkgnum) AND cust_pkg.history_date < $speriod )
";
}
elsif ($status eq "setup") {
as suspended,
SUM((s_active = 0 and s_suspended > 0 and e_active > 0)::int)
as resumed,
- SUM((s_active > 0 and e_active = 0 and e_suspended = 0)::int)
+ SUM((e_active = 0 and e_cancelled > s_cancelled)::int)
as cancelled
FROM ($cust_sql) AS x
";