X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=httemplate%2Fsearch%2Fcust_bill_pkg.cgi;h=769a13d823e2359223ac0beb7eac11608d803797;hb=ea9f1b295e5d14b92323b80cc5b4899a26f768ec;hp=3a3b0feb9d65cdafeb27c79fb92fe02c7d19ae25;hpb=f3e0ac2b009c4edd5692cb587ff709dac2223ebe;p=freeside.git diff --git a/httemplate/search/cust_bill_pkg.cgi b/httemplate/search/cust_bill_pkg.cgi index 3a3b0feb9..769a13d82 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 @@ -46,8 +41,9 @@ @peritem, 'invnum', '_date', - #'pay_amount', - #'credit_amount', + '', #'pay_amount', + '', #'credit_amount', + FS::UI::Web::cust_sort_fields(), ], 'links' => [ @pkgnum_null, @@ -124,12 +120,16 @@ Filtering parameters: - classnum: Filter on package class. +- report_optionnum: Filter on package report class. Can be a single report + 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. -- 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; @@ -217,12 +217,14 @@ 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 USING (invnum)". + " LEFT JOIN cust_bill ON (cust_bill_pkg.invnum = cust_bill.invnum)". # use cust_pkg.locationnum if it exists FS::UI::Web::join_cust_main('cust_bill', 'cust_pkg'); @@ -255,6 +257,7 @@ if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { push @where, "cust_main.agentnum = $1"; } +# salesnum--see below # refnum if ( $cgi->param('refnum') =~ /^(\d+)$/ ) { push @where, "cust_main.refnum = $1"; @@ -278,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 ( @@ -289,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; + + 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 @@ -303,8 +314,25 @@ if ( $cgi->param('nottax') ) { # 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_fee.classnum, $part_pkg.classnum, 0) IN ( ". + join(',', @classnums ). + ' )' + if @classnums; + } + + if ( grep { $_ eq 'report_optionnum' } $cgi->param ) { + 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( + report_optionnum => $num, + not_report_optionnum => $not_num, + use_override => $use_override, + all_report_options => $all, + ); } # taxclass @@ -313,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') ). ')'; } @@ -476,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, @@ -549,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')); } } @@ -643,6 +673,28 @@ if ( $cgi->param('credit') ) { 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_fee.classnum, 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";