-if ($use_usage) {
- $count_query .=
- " FROM (SELECT cust_bill_pkg.setup, cust_bill_pkg.recur,
- ( SELECT COALESCE( SUM(amount), 0 ) FROM cust_bill_pkg_detail
- WHERE cust_bill_pkg.billpkgnum = cust_bill_pkg_detail.billpkgnum
- ) AS usage FROM cust_bill_pkg $join_cust $join_pkg $where
- ) AS countquery";
-} else {
- $count_query .= " FROM cust_bill_pkg $join_cust $join_pkg $where";
+ @total = ( 'COUNT(cust_bill_pkg.billpkgnum)',
+ 'SUM(cust_bill_pkg.setup)' );
+ @total_desc = ( "$money_char%.2f total tax" );
+
+ if ( @tax_where ) {
+ # then also show the filtered tax
+ push @peritem, 'tax_total';
+ push @peritem_desc, 'Tax in category';
+ push @total, 'SUM(tax_total)';
+ push @total_desc, "$money_char%.2f tax in this category";
+ # would also be nice to include a line explaining what the category is
+ }
+
+} # nottax / istax
+
+#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";
+
+#total credits
+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)";
+push @select, 'credit_amount';
+
+# standard customer fields
+push @select, 'cust_main.custnum', FS::UI::Web::cust_sql_fields();
+
+#salesnum
+if ( $cgi->param('salesnum') =~ /^(\d+)$/ ) {
+
+ my $salesnum = $1;
+ my $sales = FS::sales->by_key($salesnum)
+ or die "salesnum $salesnum not found";
+
+ my $subsearch = $sales->cust_bill_pkg_search('', '',
+ 'cust_main_sales' => ($cgi->param('cust_main_sales') ? 1 : 0),
+ 'paid' => ($cgi->param('paid') ? 1 : 0),
+ 'classnum' => scalar($cgi->param('classnum'))
+ );
+ $join_pkg .= " JOIN sales_pkg_class ON ( COALESCE(sales_pkg_class.classnum, 0) = COALESCE( part_fee.classnum, part_pkg.classnum, 0) )";
+
+ my $extra_sql = $subsearch->{extra_sql};
+ $extra_sql =~ s/^WHERE//;
+ push @where, $extra_sql;
+
+ $cgi->param('classnum', 0) unless $cgi->param('classnum');