LEFT JOIN part_pkg USING (pkgpart)';
my $part_pkg = 'part_pkg';
-if ( $cgi->param('use_override') ) {
+if ( $cgi->param('use_override') ) { #"Separate sub-packages from parents"
# still need the real part_pkg for tax applicability,
# so alias this one
$join_pkg .= " LEFT JOIN part_pkg AS override ON (
- COALESCE(cust_bill_pkg.pkgpart_override, cust_pkg.pkgpart, 0) = part_pkg.pkgpart
+ COALESCE(cust_bill_pkg.pkgpart_override, cust_pkg.pkgpart, 0) = override.pkgpart
)";
$part_pkg = 'override';
}
#total payments
-my $pay_sub = "SELECT SUM(cust_bill_pay_pkg.amount) AS pay_amount,
- billpkgnum
- FROM cust_bill_pay_pkg
- GROUP BY billpkgnum";
-$join_pkg .= " LEFT JOIN ($pay_sub) AS item_pay USING (billpkgnum)";
-push @select, 'item_pay.pay_amount';
+my $pay_sub = "SELECT SUM(cust_bill_pay_pkg.amount)
+ FROM cust_bill_pay_pkg
+ WHERE cust_bill_pkg.billpkgnum = cust_bill_pay_pkg.billpkgnum
+ ";
+push @select, "($pay_sub) AS pay_amount";
# credit
#still want a credit total column
- my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
- billpkgnum
- FROM cust_credit_bill_pkg
- GROUP BY billpkgnum";
- $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit USING (billpkgnum)";
-
- push @select, 'item_credit.credit_amount';
+ my $credit_sub = "
+ SELECT SUM(cust_credit_bill_pkg.amount)
+ FROM cust_credit_bill_pkg
+ WHERE cust_bill_pkg.billpkgnum = cust_credit_bill_pkg.billpkgnum
+ ";
+ push @select, "($credit_sub) AS credit_amount";
}