'fields' => [
@pkgnum,
sub { $_[0]->pkgnum > 0
- # possibly use override.pkg but i think this correct
? $_[0]->get('pkgpart')
: ''
},
sub { $_[0]->pkgnum > 0
- # possibly use override.pkg but i think this correct
? $_[0]->get('pkg')
: $_[0]->get('itemdesc')
},
@peritem,
'invnum',
'_date',
- #'pay_amount',
- #'credit_amount',
+ '', #'pay_amount',
+ '', #'credit_amount',
+ FS::UI::Web::cust_sort_fields(),
],
'links' => [
@pkgnum_null,
- use_override: Apply "classnum" and "taxclass" filtering based on the
override (bundle) pkgpart, rather than always using the true pkgpart.
-- nottax: Limit to items that are not taxes (pkgnum > 0).
+- nottax: Limit to items that are not taxes (pkgnum > 0 or feepart > 0).
-- istax: Limit to items that are taxes (pkgnum == 0).
+- istax: Limit to items that are taxes (pkgnum == 0 and feepart = null).
- 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 a comma-separated
- list to include multiple taxes.
+ with "istax" it's the tax charges themselves. Can be specified
+ more than once 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 @post_desc, 'taxclass';
push @post_desc_null, '';
$post_desc_align .= 'l';
- push @select, 'part_pkg.taxclass'; # or should this use override?
}
# valid in both the tax and non-tax cases
# we want the package and its definition if available
my $join_pkg =
' LEFT JOIN cust_pkg USING (pkgnum)
- LEFT JOIN part_pkg USING (pkgpart)';
+ LEFT JOIN part_pkg USING (pkgpart)
+ LEFT JOIN part_fee USING (feepart)';
my $part_pkg = 'part_pkg';
-if ( $cgi->param('use_override') ) { #"Separate sub-packages from parents"
+# "Separate sub-packages from parents"
+my $use_override = $cgi->param('use_override') ? 1 : 0;
+if ( $use_override ) {
# still need the real part_pkg for tax applicability,
# so alias this one
$join_pkg .= " LEFT JOIN part_pkg AS override ON (
)";
$part_pkg = 'override';
}
-push @select, 'part_pkg.pkgpart', 'part_pkg.pkg'; # or should this use override?
+push @select, "$part_pkg.pkgpart", "$part_pkg.pkg";
+push @select, "COALESCE($part_pkg.taxclass, part_fee.taxclass) AS taxclass"
+ if $conf->exists('enable_taxclasses');
# the non-tax case
if ( $cgi->param('nottax') ) {
- push @where, 'cust_bill_pkg.pkgnum > 0';
+ push @select, "part_fee.itemdesc";
+
+ push @where,
+ '(cust_bill_pkg.pkgnum > 0 OR cust_bill_pkg.feepart IS NOT NULL)';
my @tax_where; # will go into a subquery
my @exempt_where; # will also go into a subquery
# 0: empty class
# N: classnum
if ( grep { $_ eq 'classnum' } $cgi->param ) {
- my @classnums = grep /^\d*$/, $cgi->param('classnum');
- push @where, "COALESCE($part_pkg.classnum, 0) IN ( ".
+ my @classnums = grep /^\d+$/, $cgi->param('classnum');
+ push @where, "COALESCE(part_fee.classnum, $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);
+ my $num = join(',', grep /^[\d,]+$/, $cgi->param('report_optionnum'));
+ my $not_num = join(',', grep /^[\d,]+$/, $cgi->param('not_report_optionnum'));
+ my $all = $cgi->param('all_report_options') ? 1 : 0;
push @where, # code reuse FTW
- FS::Report::Table->with_report_option( $num, $cgi->param('use_override'));
- }
-
- if ( $cgi->param('report_optionnum') =~ /^(\w+)$/ ) {
- ;
+ FS::Report::Table->with_report_option(
+ report_optionnum => $num,
+ not_report_optionnum => $not_num,
+ use_override => $use_override,
+ all_report_options => $all,
+ );
}
# taxclass
# effective taxclass, not the real one
push @tax_where, 'cust_main_county.taxclass IS NULL'
} elsif ( $cgi->param('taxclass') ) {
- push @tax_where, "$part_pkg.taxclass IN (" .
+ push @tax_where, "COALESCE(part_fee.taxclass, $part_pkg.taxclass) IN (" .
join(', ', map {dbh->quote($_)} $cgi->param('taxclass') ).
')';
}
# we don't handle exempt_monthly here
if ( $cgi->param('taxname') ) { # specific taxname
- push @tax_where, "COALESCE(cust_main_county.taxname, 'Tax') = ".
+ push @tax_where, 'cust_main_county.taxname = '.
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') =~ /^([\d,]+)$/) {
- push @tax_where, "cust_main_county.taxnum IN ($1)";
+ 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 we're showing exempt items, we need to find those with
USING (billpkgnum)";
}
- # process tax restrictions
- unshift @tax_where,
- 'cust_bill_pkg_tax_location.taxable_billpkgnum = cust_bill_pkg.billpkgnum',
- 'cust_main_county.tax > 0';
+ if ( @tax_where or $cgi->param('taxable') or $cgi->param('out') ) {
+ # process tax restrictions
+ unshift @tax_where,
+ 'cust_main_county.tax > 0';
- my $tax_sub = "SELECT 1
+ my $tax_sub = "SELECT invnum, cust_bill_pkg_tax_location.pkgnum
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);
+ 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)";
+ }
# 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, "(EXISTS($tax_sub) OR item_exempt.billpkgnum IS NOT NULL)";
+ push @where,
+ '(item_tax.invnum IS NOT NULL 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') =~ /^([\d,]+)$/) {
- push @where, "cust_main_county.taxnum IN ($1)";
+ 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
'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) )";
+ $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//;
projects / freeside.git / blobdiff