+#total payments
+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
+if ( $cgi->param('credit') ) {
+
+ my $credit_where;
+
+ my($cr_begin, $cr_end) = FS::UI::Web::parse_beginning_ending($cgi, 'credit');
+ $credit_where = "WHERE cust_credit_bill._date >= $cr_begin " .
+ "AND cust_credit_bill._date <= $cr_end";
+
+ my $credit_sub;
+
+ if ( $cgi->param('istax') ) {
+ # then we need to group/join by billpkgtaxlocationnum, to get only the
+ # relevant part of partial taxes
+ my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
+ reason.reason as reason_text, access_user.username AS username_text,
+ billpkgtaxlocationnum, 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)
+ $credit_where
+ GROUP BY billpkgnum, billpkgtaxlocationnum, reason.reason,
+ access_user.username";
+
+ if ( $cgi->param('out') ) {
+
+ # find credits that are applied to the line items, but not to
+ # a cust_bill_pkg_tax_location link
+ $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
+ USING (billpkgnum)";
+ push @where, 'item_credit.billpkgtaxlocationnum IS NULL';