@peritem,
'invnum',
'_date',
- '', #'pay_amount',
- '', #'credit_amount',
+ 'pay_amount',
+ 'credit_amount',
FS::UI::Web::cust_sort_fields(),
],
'links' => [
USING (billpkgnum)";
}
- # This is the only place we should attempt to show credits on here:
- # the total of credit applications to the line item.
-
- my $credit_sub = 'SELECT SUM(amount) AS credit_amount, billpkgnum
- FROM cust_credit_bill_pkg GROUP BY billpkgnum';
-
- $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
- ON (cust_bill_pkg.billpkgnum = item_credit.billpkgnum)";
-
if ( @tax_where or $cgi->param('taxable') ) {
# process tax restrictions
unshift @tax_where,
}
}
+ # itemdesc, for breakdown from the vendor tax report
+ # (this is definitely used)
+ if ( $cgi->param('itemdesc') ) {
+ if ( $cgi->param('itemdesc') eq 'Tax' ) {
+ push @where, "($itemdesc = 'Tax' OR $itemdesc is null)";
+ } else {
+ push @where, "$itemdesc = ". dbh->quote($cgi->param('itemdesc'));
+ }
+ }
+
} else { # the internal-tax case
my $tax_select = 'SELECT tax.billpkgnum, SUM(tax.amount) as tax_total';
} # nottax / istax
-
#total payments
my $pay_sub = "SELECT SUM(cust_bill_pay_pkg.amount)
FROM cust_bill_pay_pkg
";
push @select, "($pay_sub) AS pay_amount";
+# showing credited amount, optionally with date filtering
+my $credit_where = '';
+if ( $cgi->param('credit_begin') or $cgi->param('credit_end') ) {
+ 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 = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount, billpkgnum
+ FROM cust_credit_bill_pkg
+ JOIN cust_credit_bill USING (creditbillnum)
+ $credit_where
+ GROUP BY billpkgnum";
+
+$join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
+ ON (cust_bill_pkg.billpkgnum = item_credit.billpkgnum)";
+push @select, 'credit_amount';
+
+# standard customer fields
push @select, 'cust_main.custnum', FS::UI::Web::cust_sql_fields();
#salesnum
$cgi->param('classnum', 0) unless $cgi->param('classnum');
}
+#credit flag (include only those that have credit(s) applied)
+if ( $cgi->param('credit') ) {
+ push @where, 'credit_amount > 0';
+}
my $where = join(' AND ', @where);
$where &&= "WHERE $where";
my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ];
my $pay_link = ''; #[, 'billpkgnum', ];
-my $credit_link = [ "${p}search/cust_credit_bill_pkg.html?billpkgnum=", 'billpkgnum', ];
+my $credit_param = '';
+foreach ('credit_begin', 'credit_end') {
+ if ( $cgi->param($_) ) {
+ $credit_param .= "$_=" . $cgi->param($_) . ';';
+ }
+}
+my $credit_link = [ "${p}search/cust_credit_bill_pkg.html?${credit_param}billpkgnum=", 'billpkgnum', ];
warn "\n\nQUERY:\n".Dumper($query)."\n\nCOUNT_QUERY:\n$count_query\n\n"
if $cgi->param('debug');