X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=httemplate%2Fsearch%2Fcust_bill_pkg.cgi;h=b1086e3dc59b524b746edf9e5330fe1733c1d3a3;hb=84abddf4bf0bc90e4a8f6e8e546e3cf4ee2786a2;hp=7d9172acaae1494e5bdd467c5133700b0aba0a5b;hpb=3d0a1bb06b895c5be6e3f0517d355442a6b1e125;p=freeside.git diff --git a/httemplate/search/cust_bill_pkg.cgi b/httemplate/search/cust_bill_pkg.cgi index 7d9172aca..b1086e3dc 100644 --- a/httemplate/search/cust_bill_pkg.cgi +++ b/httemplate/search/cust_bill_pkg.cgi @@ -20,15 +20,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 @@ -49,8 +44,9 @@ @currency, 'invnum', '_date', - #'pay_amount', - #'credit_amount', + '', #'pay_amount', + '', #'credit_amount', + FS::UI::Web::cust_sort_fields(), ], 'links' => [ @pkgnum_null, @@ -131,17 +127,21 @@ 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; - 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; @@ -241,9 +241,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)". @@ -279,6 +281,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"; @@ -302,10 +305,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 ( @@ -313,12 +319,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 @@ -327,8 +338,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 @@ -337,7 +365,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') ). ')'; } @@ -353,11 +381,8 @@ if ( $cgi->param('nottax') ) { # 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) @@ -385,12 +410,8 @@ if ( $cgi->param('nottax') ) { } # 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 @@ -418,24 +439,18 @@ if ( $cgi->param('nottax') ) { $join_pkg .= " LEFT JOIN ($exempt_sub) AS item_exempt USING (billpkgnum)"; - } - if ( @tax_where or $cgi->param('taxable') or $cgi->param('out') ) { # 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 ) { @@ -452,23 +467,17 @@ if ( $cgi->param('nottax') ) { 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)"; } @@ -500,14 +509,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, @@ -535,6 +546,21 @@ if ( $cgi->param('nottax') ) { # 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 @@ -551,34 +577,12 @@ if ( $cgi->param('nottax') ) { dbh->quote($cgi->param('taxname')); } - # 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"; - } - } - - # 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')); } } @@ -667,6 +671,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";