X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=httemplate%2Fsearch%2Fcust_bill_pkg.cgi;h=6cda34a7fa72a250ce5e978537f935bb2405a3f4;hb=16055ddbd7cf41399df4a24a337584225c34c91b;hp=6e110d64ceef9d2c1565fb358140db40a60ecc90;hpb=d705687fb371ac04ed371ce2de8a4bb2fc25ee18;p=freeside.git diff --git a/httemplate/search/cust_bill_pkg.cgi b/httemplate/search/cust_bill_pkg.cgi index 6e110d64c..6cda34a7f 100644 --- a/httemplate/search/cust_bill_pkg.cgi +++ b/httemplate/search/cust_bill_pkg.cgi @@ -19,15 +19,10 @@ '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') - }, + 'itemdesc', # is part_pkg.pkg if applicable @post_desc, #strikethrough or "N/A ($amount)" or something these when # they're not applicable to pkg_tax search @@ -132,9 +127,9 @@ Filtering parameters: - 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; @@ -222,9 +217,11 @@ if ( $conf->exists('enable_taxclasses') ) { push @post_desc, 'taxclass'; push @post_desc_null, ''; $post_desc_align .= 'l'; - push @select, 'part_pkg.taxclass'; # or should this use override? } +# used in several places +my $itemdesc = 'COALESCE(part_fee.itemdesc, part_pkg.pkg, cust_bill_pkg.itemdesc)'; + # valid in both the tax and non-tax cases my $join_cust = " LEFT JOIN cust_bill ON (cust_bill_pkg.invnum = cust_bill.invnum)". @@ -284,10 +281,13 @@ if ( $cgi->param('custnum') =~ /^(\d+)$/ ) { # 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 ( @@ -295,12 +295,17 @@ if ( $cgi->param('use_override') ) { #"Separate sub-packages from parents" )"; $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, "($itemdesc) AS 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 @@ -311,21 +316,23 @@ if ( $cgi->param('nottax') ) { # N: classnum if ( grep { $_ eq 'classnum' } $cgi->param ) { my @classnums = grep /^\d+$/, $cgi->param('classnum'); - push @where, "COALESCE($part_pkg.classnum, 0) IN ( ". + 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 @@ -334,7 +341,7 @@ if ( $cgi->param('nottax') ) { # 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') ). ')'; } @@ -497,14 +504,16 @@ if ( $cgi->param('nottax') ) { push @where, 'cust_bill_pkg.pkgnum = 0'; # tax location when using tax_rate_location - if ( scalar( grep( /locationtaxid/, $cgi->param ) ) ) { + if ( $cgi->param('vendortax') ) { $join_pkg .= ' LEFT JOIN cust_bill_pkg_tax_rate_location USING ( billpkgnum ) '. ' LEFT JOIN tax_rate_location USING ( taxratelocationnum )'; - push @where, FS::tax_rate_location->location_sql( - map { $_ => (scalar($cgi->param($_)) || '') } - qw( district city county state locationtaxid ) - ); + foreach (qw( state county city locationtaxid)) { + if ( scalar($cgi->param($_)) ) { + my $place = dbh->quote( $cgi->param($_) ); + push @where, "tax_rate_location.$_ = $place"; + } + } $total[1] = 'SUM( COALESCE(cust_bill_pkg_tax_rate_location.amount, @@ -570,12 +579,12 @@ if ( $cgi->param('nottax') ) { } } - # itemdesc, for some reason + # itemdesc, for breakdown from the vendor tax report if ( $cgi->param('itemdesc') ) { if ( $cgi->param('itemdesc') eq 'Tax' ) { - push @where, "(itemdesc='Tax' OR itemdesc is null)"; + push @where, "($itemdesc = 'Tax' OR $itemdesc is null)"; } else { - push @where, 'itemdesc='. dbh->quote($cgi->param('itemdesc')); + push @where, "$itemdesc = ". dbh->quote($cgi->param('itemdesc')); } } @@ -676,7 +685,7 @@ if ( $cgi->param('salesnum') =~ /^(\d+)$/ ) { '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//;