+ "SELECT COUNT(*), SUM( $setup_taxable + $recur_taxable - $exempt )";
+
+ push @where,
+ #not tax-exempt package (setup or recur)
+ "(
+ ( ( part_pkg.setuptax != 'Y' OR part_pkg.setuptax IS NULL )
+ AND cust_bill_pkg.setup > 0 )
+ OR
+ ( ( part_pkg.recurtax != 'Y' OR part_pkg.recurtax IS NULL )
+ AND cust_bill_pkg.recur > 0 )
+ )",
+ #not a tax_exempt customer
+ "( tax != 'Y' OR tax IS NULL )", # assume this was intended?
+ #not covered in full by a monthly tax exemption (texas tax)
+ "0 < ( $setup_taxable + $recur_taxable - $exempt )";
+
+} else {
+
+ if ( $use_usage ) {
+ $count_query = "SELECT COUNT(*), ";
+ } else {
+ $count_query = "SELECT COUNT(DISTINCT billpkgnum), ";
+ }
+
+ if ( $use_usage eq 'recurring' ) {
+ $count_query .= "SUM(cust_bill_pkg.setup + cust_bill_pkg.recur - usage)";
+ } elsif ( $use_usage eq 'usage' ) {
+ $count_query .= "SUM(usage)";
+ } elsif ( scalar( grep( /locationtaxid/, $cgi->param ) ) ) {
+ $count_query .= "SUM( COALESCE(cust_bill_pkg_tax_rate_location.amount, cust_bill_pkg.setup + cust_bill_pkg.recur))";
+ } elsif ( $cgi->param('iscredit') eq 'rate') {
+ $count_query .= "SUM( cust_credit_bill_pkg.amount )";
+ } else {
+ $count_query .= "SUM(cust_bill_pkg.setup + cust_bill_pkg.recur)";
+ }
+
+}
+
+$join_cust = ' JOIN cust_bill USING ( invnum )
+ LEFT JOIN cust_main USING ( custnum ) ';
+
+if ( $cgi->param('nottax') ) {
+
+ $join_pkg .= ' LEFT JOIN cust_pkg USING ( pkgnum )
+ LEFT JOIN part_pkg USING ( pkgpart )
+ LEFT JOIN part_pkg AS override
+ ON pkgpart_override = override.pkgpart
+ LEFT JOIN cust_location
+ ON cust_location.locationnum = '.
+ FS::cust_pkg->tax_locationnum_sql;
+
+} elsif ( $cgi->param('istax') ) {
+
+ #false laziness w/report_tax.cgi $taxfromwhere
+ if ( scalar( grep( /locationtaxid/, $cgi->param ) ) ||
+ $cgi->param('iscredit') eq 'rate') {
+
+ # using tax_rate_location and friends
+ $join_pkg .=
+ ' LEFT JOIN cust_bill_pkg_tax_rate_location USING ( billpkgnum ) '.
+ ' LEFT JOIN tax_rate_location USING ( taxratelocationnum ) ';
+
+ #} elsif ( $conf->exists('tax-pkg_address') ) {
+ } else {
+
+ # using cust_bill_pkg_tax_location to relate tax items to locations
+ # ...but for consolidated taxes we don't want to duplicate this
+ my $tax_item_location = '(SELECT DISTINCT billpkgnum, locationnum
+ FROM cust_bill_pkg_tax_location) AS tax_item_location';
+
+ $join_pkg .= " LEFT JOIN $tax_item_location USING ( billpkgnum )
+ LEFT JOIN cust_location
+ ON tax_item_location.locationnum =
+ cust_location.locationnum ";
+
+ #quelle kludge, somewhat false laziness w/report_tax.cgi
+ s/cust_pkg\.locationnum/tax_item_location.locationnum/g for @where;
+ }
+
+ if ( $cgi->param('iscredit') ) {
+ $join_pkg .= ' JOIN cust_credit_bill_pkg USING ( billpkgnum';
+ if ( $cgi->param('iscredit') eq 'rate' ) {
+ $join_pkg .= ', billpkgtaxratelocationnum )';
+ } elsif ( $conf->exists('tax-pkg_address') ) {
+ $join_pkg .= ', billpkgtaxlocationnum )';
+ push @where, "billpkgtaxratelocationnum IS NULL";
+ } else {
+ $join_pkg .= ' )';
+ push @where, "billpkgtaxratelocationnum IS NULL";
+ }
+ }
+
+} else {
+
+ #die?
+ warn "neither nottax nor istax parameters specified";
+ #same as before?
+ $join_pkg = ' LEFT JOIN cust_pkg USING ( pkgnum )
+ LEFT JOIN part_pkg USING ( pkgpart ) ';