+ } # handle all joins to cust_main_county
+
+ # setup/recur/usage separation
+ my %charges = map { $_ => 1 } split('', $cgi->param('charges') || 'SRU');
+
+ if ( $charges{S} and $charges{R} and $charges{U} ) {
+ # in this case, show discounts
+
+ $join_pkg .= ' JOIN (
+ SELECT billpkgnum, COALESCE(SUM(amount), 0) AS discounted
+ FROM cust_bill_pkg_discount RIGHT JOIN cust_bill_pkg USING (billpkgnum)
+ GROUP BY billpkgnum
+ ) AS _discount ON (cust_bill_pkg.billpkgnum = _discount.billpkgnum)
+ ';
+ push @select, '_discount.discounted';
+
+ push @peritem, 'discounted';
+ push @peritem_desc, 'Discount';
+ push @total, 'SUM(cust_bill_pkg.setup + cust_bill_pkg.recur + discounted)',
+ 'SUM(discounted)',
+ 'SUM(cust_bill_pkg.setup + cust_bill_pkg.recur)';
+ push @total_desc, "$money_char%.2f gross sales",
+ "− $money_char%.2f discounted",
+ "= $money_char%.2f invoiced";
+
+ } elsif ( $charges{R} and $charges{U} ) {
+
+ # hide rows with no recurring fee, and show the sum of recurring fees only
+ push @where, 'cust_bill_pkg.recur > 0';
+ push @total, "SUM(cust_bill_pkg.recur)";
+ push @total_desc, "$money_char%.2f recurring";
+
+ } elsif ( $charges{R} and !$charges{U} ) {
+
+ my $recur_no_usage = FS::cust_bill_pkg->charged_sql('', '',
+ setuprecur => 'recur', no_usage => 1);
+ push @select, "($recur_no_usage) AS recur_no_usage";
+ $peritem[1] = 'recur_no_usage';
+ $peritem_desc[1] = 'Recurring charges (excluding usage)';
+ push @total, "SUM($recur_no_usage)";
+ push @total_desc, "$money_char%.2f recurring";
+ if ( !$charges{S} ) {
+ push @where, "($recur_no_usage) > 0";
+ }