+ # get discounted, credited, and paid amounts here, for use in report
+ #
+ # Testing shows that this is by far the most efficient way to do the
+ # joins. In particular it's almost 100x faster to join to an aggregate
+ # query than to put the subquery in a select expression. It also makes
+ # it more convenient to do arithmetic between columns, use them as sort
+ # keys, etc.
+ #
+ # Each ends with a RIGHT JOIN cust_bill so that it includes all invnums,
+ # even if they have no discounts/credits/payments; the total amount is then
+ # coalesced to zero.
+ my $join = "$join_cust_main
+ JOIN (
+ SELECT COALESCE(SUM(cust_bill_pkg_discount.amount), 0) AS discounted,
+ invnum
+ FROM cust_bill_pkg_discount
+ JOIN cust_bill_pkg USING (billpkgnum)
+ RIGHT JOIN cust_bill USING (invnum)
+ GROUP BY invnum
+ ) AS _discount USING (invnum)
+ JOIN (
+ SELECT COALESCE(SUM(cust_credit_bill.amount), 0) AS credited, invnum
+ FROM cust_credit_bill
+ RIGHT JOIN cust_bill USING (invnum)
+ GROUP BY invnum
+ ) AS _credit USING (invnum)
+ JOIN (
+ SELECT COALESCE(SUM(cust_bill_pay.amount), 0) AS paid, invnum
+ FROM cust_bill_pay
+ RIGHT JOIN cust_bill USING (invnum)
+ GROUP BY invnum
+ ) AS _pay USING (invnum)
+ ";
+