- classnum: Filter on package class.
- report_optionnum: Filter on package report class. Can be a single report
- class number, a comma-separated list, the word "multiple", or an empty
- string (for "no report class").
+ class number or a comma-separated list (where 0 is "no report class"), or the
+ word "multiple".
- use_override: Apply "classnum" and "taxclass" filtering based on the
override (bundle) pkgpart, rather than always using the true pkgpart.
- taxnum: Limit to items whose tax definition matches this taxnum.
With "nottax" that means items that are subject to that tax;
- with "istax" it's the tax charges themselves. Can be specified
- more than once to include multiple taxes.
+ with "istax" it's the tax charges themselves. Can be a comma-separated
+ list to include multiple taxes.
- country, state, county, city: Limit to items whose tax location
matches these fields. If "nottax" it's the tax location of the package;
push @where, "cust_main.agentnum = $1";
}
-# salesnum
-if ( $cgi->param('salesnum') =~ /^(\d+)$/ ) {
-
- my $salesnum = $1;
-
- my $cmp_salesnum = $cgi->param('cust_main_sales')
- ? ' COALESCE( cust_pkg.salesnum, cust_main.salesnum )'
- : ' cust_pkg.salesnum ';
-
- push @where, "$cmp_salesnum = $salesnum";
-
- #because currently we're called from sales_pkg_class.html for a specific
- # class (or empty class) but not for all classes
- #will have to do something to distinguish if someone wants the sales report
- # (report_cust_bill_pkg.html) to have a sales person dropdown
- $cgi->param('classnum', 0) unless $cgi->param('classnum');
-}
-
+# salesnum--see below
# refnum
if ( $cgi->param('refnum') =~ /^(\d+)$/ ) {
push @where, "cust_main.refnum = $1";
# not specified: all classes
# 0: empty class
# N: classnum
- if ( $cgi->param('classnum') =~ /^(\d+)$/ ) {
- push @where, "COALESCE($part_pkg.classnum, 0) = $1";
+ if ( grep { $_ eq 'classnum' } $cgi->param ) {
+ my @classnums = grep /^\d*$/, $cgi->param('classnum');
+ push @where, "COALESCE($part_pkg.classnum, 0) IN ( ".
+ join(',', @classnums ).
+ ' )'
+ if @classnums;
+ }
+
+ if ( grep { $_ eq 'report_optionnum' } $cgi->param ) {
+ my @nums = grep /^\w+$/, $cgi->param('report_optionnum');
+ my $num = join(',', @nums);
+ push @where, # code reuse FTW
+ FS::Report::Table->with_report_option( $num, $cgi->param('use_override'));
}
if ( $cgi->param('report_optionnum') =~ /^(\w+)$/ ) {
- # code reuse FTW
- my $num = $1;
- push @where,
- FS::Report::Table->with_report_option( $1, $cgi->param('use_override') )
;
}
# we don't handle exempt_monthly here
if ( $cgi->param('taxname') ) { # specific taxname
- push @tax_where, 'cust_main_county.taxname = '.
+ push @tax_where, "COALESCE(cust_main_county.taxname, 'Tax') = ".
dbh->quote($cgi->param('taxname'));
- } elsif ( $cgi->param('taxnameNULL') ) {
- push @tax_where, 'cust_main_county.taxname IS NULL OR '.
- 'cust_main_county.taxname = \'Tax\'';
}
# country:state:county:city:district (may be repeated)
}
# specific taxnums
- if ( $cgi->param('taxnum') ) {
- my $taxnum_in = join(',',
- grep /^\d+$/, $cgi->param('taxnum')
- );
- push @tax_where, "cust_main_county.taxnum IN ($taxnum_in)"
- if $taxnum_in;
+ if ( $cgi->param('taxnum') =~ /^([\d,]+)$/) {
+ push @tax_where, "cust_main_county.taxnum IN ($1)";
}
# If we're showing exempt items, we need to find those with
USING (billpkgnum)";
}
- if ( @tax_where or $cgi->param('taxable') or $cgi->param('out') ) {
- # process tax restrictions
- unshift @tax_where,
- 'cust_main_county.tax > 0';
+ # process tax restrictions
+ unshift @tax_where,
+ 'cust_bill_pkg_tax_location.taxable_billpkgnum = cust_bill_pkg.billpkgnum',
+ 'cust_main_county.tax > 0';
- my $tax_sub = "SELECT invnum, cust_bill_pkg_tax_location.pkgnum
+ my $tax_sub = "SELECT 1
FROM cust_bill_pkg_tax_location
JOIN cust_bill_pkg AS tax_item USING (billpkgnum)
JOIN cust_main_county USING (taxnum)
- WHERE ". join(' AND ', @tax_where).
- " GROUP BY invnum, cust_bill_pkg_tax_location.pkgnum";
-
- $join_pkg .= " LEFT JOIN ($tax_sub) AS item_tax
- ON (item_tax.invnum = cust_bill_pkg.invnum AND
- item_tax.pkgnum = cust_bill_pkg.pkgnum)";
- }
+ WHERE ". join(' AND ', @tax_where);
# now do something with that
if ( @exempt_where ) {
my $taxable = 'cust_bill_pkg.setup + cust_bill_pkg.recur '.
'- COALESCE(item_exempt.exempt_amount, 0)';
- push @where, 'item_tax.invnum IS NOT NULL';
push @select, "($taxable) AS taxable_amount";
+ push @where, "EXISTS($tax_sub)";
push @peritem, 'taxable_amount';
push @peritem_desc, 'Taxable';
push @total, "SUM($taxable)";
push @total_desc, "$money_char%.2f taxable";
- } elsif ( $cgi->param('out') ) {
-
- push @where, 'item_tax.invnum IS NULL',
- 'item_exempt.billpkgnum IS NULL';
-
} elsif ( @tax_where ) {
# union of taxable + all exempt_ cases
- push @where,
- '(item_tax.invnum IS NOT NULL OR item_exempt.billpkgnum IS NOT NULL)';
+ push @where, "(EXISTS($tax_sub) OR item_exempt.billpkgnum IS NOT NULL)";
}
# 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)';
+
+ # package classnum
+ if ( grep { $_ eq 'classnum' } $cgi->param ) {
+ my @classnums = grep /^\d*$/, $cgi->param('classnum');
+ $join_pkg .= '
+ JOIN cust_pkg AS taxed_pkg
+ ON (cust_bill_pkg_tax_location.pkgnum = taxed_pkg.pkgnum)
+ JOIN part_pkg AS taxed_part_pkg
+ ON (taxed_pkg.pkgpart = taxed_part_pkg.pkgpart)
+ ';
+ push @where, "COALESCE(taxed_part_pkg.classnum, 0) IN ( ".
+ join(',', @classnums ).
+ ' )'
+ if @classnums;
+ }
}
# taxclass
}
# 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";
- }
+ if ( $cgi->param('taxnum') =~ /^([\d,]+)$/) {
+ push @where, "cust_main_county.taxnum IN ($1)";
}
# itemdesc, for some reason
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_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');
+}
+
+
my $where = join(' AND ', @where);
$where &&= "WHERE $where";
projects / freeside.git / blobdiff