+ # find credits that are applied to the CBPTL links that are
+ # considered "interesting" by the report criteria
+ $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
+ USING (billpkgtaxlocationnum)";
+
+ }
+
+ } else {
+ # then only group by billpkgnum
+ my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
+ reason.reason as reason_text, access_user.username AS username_text,
+ billpkgnum
+ FROM cust_credit_bill_pkg
+ JOIN cust_credit_bill USING (creditbillnum)
+ JOIN cust_credit USING (crednum)
+ LEFT JOIN reason USING (reasonnum)
+ LEFT JOIN access_user USING (usernum)
+ GROUP BY billpkgnum, reason.reason, access_user.username";
+ $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit USING (billpkgnum)";
+ }
+
+ push @where, 'item_credit.billpkgnum IS NOT NULL';
+ push @select, 'item_credit.credit_amount',
+ 'item_credit.username_text',
+ 'item_credit.reason_text';
+ push @peritem, 'credit_amount', 'username_text', 'reason_text';
+ push @peritem_desc, 'Credited', 'By', 'Reason';
+ push @total, 'SUM(credit_amount)';
+ push @total_desc, "$money_char%.2f credited";
+} # if credit
+
+push @select, 'cust_main.custnum', FS::UI::Web::cust_sql_fields();
+
+my $where = join(' AND ', @where);
+$where &&= "WHERE $where";