+ } elsif ( $cgi->param('out') ) {
+
+ $join_pkg .= '
+ LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum)
+ ';
+ push @where, 'cust_bill_pkg_tax_location.billpkgnum IS NULL';
+
+ # each billpkgnum should appear only once
+ $total[0] = 'COUNT(*)';
+ $total[1] = 'SUM(cust_bill_pkg.setup)';
+
+ } else { # not locationtaxid or 'out'--the normal case
+
+ $join_pkg .= '
+ LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum)
+ JOIN cust_main_county USING (taxnum)
+ ';
+
+ # don't double-count the components of consolidated taxes
+ $total[0] = 'COUNT(DISTINCT cust_bill_pkg.billpkgnum)';
+ $total[1] = 'SUM(cust_bill_pkg_tax_location.amount)';
+ }
+
+ # taxclass
+ if ( $cgi->param('taxclassNULL') ) {
+ push @where, 'cust_main_county.taxclass IS NULL';
+ }
+
+ # taxname
+ if ( $cgi->param('taxnameNULL') ) {
+ push @where, 'cust_main_county.taxname IS NULL OR '.
+ 'cust_main_county.taxname = \'Tax\'';
+ } elsif ( $cgi->param('taxname') ) {
+ push @where, 'cust_main_county.taxname = '.
+ dbh->quote($cgi->param('taxname'));
+ }
+
+ # specific taxnums
+ if ( $cgi->param('taxnum') ) {
+ my $taxnum_in = join(',',
+ grep /^\d+$/, $cgi->param('taxnum')
+ );
+ push @where, "cust_main_county.taxnum IN ($taxnum_in)"
+ if $taxnum_in;
+ }
+
+ # report group (itemdesc)
+ if ( $cgi->param('report_group') =~ /^(=|!=) (.*)$/ ) {
+ my ( $group_op, $group_value ) = ( $1, $2 );
+ if ( $group_op eq '=' ) {
+ #push @where, 'itemdesc LIKE '. dbh->quote($group_value.'%');
+ push @where, 'itemdesc = '. dbh->quote($group_value);
+ } elsif ( $group_op eq '!=' ) {
+ push @where, '( itemdesc != '. dbh->quote($group_value) .' OR itemdesc IS NULL )';
+ } else {
+ die "guru meditation #00de: group_op $group_op\n";
+ }
+ }
+
+ # itemdesc, for some reason
+ 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'));
+ }
+ }
+
+} # 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";
+
+
+# credit
+if ( $cgi->param('credit') ) {
+
+ 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)
+ 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';
+
+ } else {
+
+ # 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";
+
+} else {
+
+ #still want a credit total column
+
+ my $credit_sub = "
+ SELECT SUM(cust_credit_bill_pkg.amount)
+ FROM cust_credit_bill_pkg
+ WHERE cust_bill_pkg.billpkgnum = cust_credit_bill_pkg.billpkgnum
+ ";
+ push @select, "($credit_sub) AS credit_amount";
+
+}
+
+push @select, 'cust_main.custnum', FS::UI::Web::cust_sql_fields();
+
+my $where = join(' AND ', @where);
+$where &&= "WHERE $where";