1 <& elements/search.html,
2 'title' => emt('Line items'),
3 'name' => emt('line items'),
5 'count_query' => $count_query,
6 'count_addl' => \@total_desc,
18 FS::UI::Web::cust_header(),
22 sub { $_[0]->pkgnum > 0
23 ? $_[0]->get('pkgpart')
26 'itemdesc', # is part_pkg.pkg if applicable
28 #strikethrough or "N/A ($amount)" or something these when
29 # they're not applicable to pkg_tax search
33 sub { time2str('%b %d %Y', shift->_date ) },
34 sub { sprintf($money_char.'%.2f', shift->get('pay_amount')) },
35 sub { sprintf($money_char.'%.2f', shift->get('credit_amount')) },
36 \&FS::UI::Web::cust_fields,
49 FS::UI::Web::cust_sort_fields(),
62 ( map { $_ ne 'Cust. Status' ? $clink : '' }
63 FS::UI::Web::cust_header()
66 #'align' => 'rlrrrc'.FS::UI::Web::cust_aligns(),
67 'align' => $pkgnum_align.
73 FS::UI::Web::cust_aligns(),
85 FS::UI::Web::cust_colors(),
98 FS::UI::Web::cust_styles(),
103 Output control parameters:
104 - distribute: Boolean. If true, recurring fees will be "prorated" for the
105 portion of the package date range (sdate-edate) that falls within the date
106 range of the report. Line items will be limited to those for which this
107 portion is > 0. This disables filtering on invoice date.
109 - usage: Separate usage (cust_bill_pkg_detail records) from
110 recurring charges. If set to "usage", will show usage instead of
111 recurring charges. If set to "recurring", will deduct usage and only
112 show the flat rate charge. If not passed, the "recurring charge" column
113 will include usage charges also.
115 Filtering parameters:
116 - begin, end: Date range. Applies to invoice date, not necessarily package
117 date range. But see "distribute".
119 - status: Customer status (active, suspended, etc.). This will filter on
120 _current_ customer status, not status at the time the invoice was generated.
122 - agentnum: Filter on customer agent.
124 - refnum: Filter on customer reference source.
126 - cust_classnum: Filter on customer class.
128 - classnum: Filter on package class.
130 - report_optionnum: Filter on package report class. Can be a single report
131 class number or a comma-separated list (where 0 is "no report class"), or the
134 - use_override: Apply "classnum" and "taxclass" filtering based on the
135 override (bundle) pkgpart, rather than always using the true pkgpart.
137 - nottax: Limit to items that are not taxes (pkgnum > 0 or feepart > 0).
139 - istax: Limit to items that are taxes (pkgnum == 0 and feepart = null).
141 - taxnum: Limit to items whose tax definition matches this taxnum.
142 With "nottax" that means items that are subject to that tax;
143 with "istax" it's the tax charges themselves. Can be a comma-separated
144 list to include multiple taxes.
146 - country, state, county, city: Limit to items whose tax location
147 matches these fields. If "nottax" it's the tax location of the package;
148 if "istax" the location of the tax.
150 - taxname, taxnameNULL: With "nottax", limit to items whose tax location
151 matches a tax with this name. With "istax", limit to items that have
152 this tax name. taxnameNULL is equivalent to "taxname = '' OR taxname
155 - out: With "nottax", limit to items that don't match any tax definition.
156 With "istax", find tax items that are unlinked to their tax definitions.
157 Current Freeside (> July 2012) always creates tax links, but unlinked
158 items may result from an incomplete upgrade of legacy data.
160 - locationtaxid: With "nottax", limit to packages matching this
161 tax_rate_location ID; with "tax", limit to taxes generated from that
164 - taxclass: Filter on package taxclass.
166 - taxclassNULL: With "nottax", limit to items that would be subject to the
167 tax with taxclass = NULL. This doesn't necessarily mean part_pkg.taxclass
168 is NULL; it also includes taxclasses that don't have a tax in this region.
170 - itemdesc: Limit to line items with this description. Note that non-tax
171 packages usually have a description of NULL. (Deprecated.)
173 - report_group: Can contain '=' or '!=' followed by a string to limit to
174 line items where itemdesc starts with, or doesn't start with, the string.
176 - cust_tax: Limit to customers who are tax-exempt. If "taxname" is also
177 specified, limit to customers who are also specifically exempt from that
180 - pkg_tax: Limit to packages that are tax-exempt, and only include the
181 exempt portion (setup, recurring, or both) when calculating totals.
183 - taxable: Limit to packages that are subject to tax, i.e. where a
184 cust_bill_pkg_tax_location record exists.
186 - credit: Limit to line items that received a credit application. The
187 amount of the credit will also be shown.
192 my $curuser = $FS::CurrentUser::CurrentUser;
194 die "access denied" unless $curuser->access_right('Financial reports');
196 my $conf = new FS::Conf;
197 my $money_char = $conf->config('money_char') || '$';
199 my @select = ( 'cust_bill_pkg.*', 'cust_bill._date' );
200 my @total = ( 'COUNT(*)', 'SUM(cust_bill_pkg.setup + cust_bill_pkg.recur)');
201 my @total_desc = ( $money_char.'%.2f total' ); # sprintf strings
203 my @peritem = ( 'setup', 'recur' );
204 my @peritem_desc = ( 'Setup charge', 'Recurring charge' );
206 my @currency_desc = ();
207 my @currency_sub = ();
209 if ( $conf->config('currencies') ) {
210 @currency_desc = ( 'Setup billed', 'Recurring billed' );
214 sub { my $currency = $_[0]->get($what.'_billed_currency') or return '';
215 $currency. ' '. currency_symbol($currency, SYM_HTML).
216 $_[0]->get($what.'_billed_amount');
220 @currency = ( 'setup_billed_amount', 'recur_billed_amount' ); #for sorting
223 my @pkgnum_header = ();
226 my $pkgnum_align = '';
227 if ( $curuser->option('show_pkgnum') ) {
228 push @select, 'cust_bill_pkg.pkgnum';
229 push @pkgnum_header, 'Pkg Num';
230 push @pkgnum, sub { $_[0]->pkgnum > 0 ? $_[0]->pkgnum : '' };
231 push @pkgnum_null, '';
232 $pkgnum_align .= 'r';
235 my @post_desc_header = ();
237 my @post_desc_null = ();
238 my $post_desc_align = '';
239 if ( $conf->exists('enable_taxclasses') ) {
240 push @post_desc_header, 'Tax class';
241 push @post_desc, 'taxclass';
242 push @post_desc_null, '';
243 $post_desc_align .= 'l';
246 # used in several places
247 my $itemdesc = 'COALESCE(part_fee.itemdesc, part_pkg.pkg, cust_bill_pkg.itemdesc)';
249 # valid in both the tax and non-tax cases
251 " LEFT JOIN cust_bill ON (cust_bill_pkg.invnum = cust_bill.invnum)".
252 # use cust_pkg.locationnum if it exists
253 FS::UI::Web::join_cust_main('cust_bill', 'cust_pkg');
255 #agent virtualization
257 $FS::CurrentUser::CurrentUser->agentnums_sql( 'table' => 'cust_main' );
259 my @where = ( $agentnums_sql );
262 my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
264 if ( $cgi->param('distribute') == 1 ) {
265 push @where, "sdate <= $ending",
266 "edate > $beginning",
270 push @where, "cust_bill._date >= $beginning",
271 "cust_bill._date <= $ending";
275 if ( $cgi->param('status') =~ /^([a-z]+)$/ ) {
276 push @where, FS::cust_main->cust_status_sql . " = '$1'";
280 if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
281 push @where, "cust_main.agentnum = $1";
284 # salesnum--see below
286 if ( $cgi->param('refnum') =~ /^(\d+)$/ ) {
287 push @where, "cust_main.refnum = $1";
290 # cust_classnum (false laziness w/ elements/cust_main_dayranges.html, elements/cust_pay_or_refund.html, prepaid_income.html, cust_bill_pay.html, cust_bill_pkg_referral.html, unearned_detail.html, cust_credit.html, cust_credit_refund.html, cust_main::Search::search_sql)
291 if ( grep { $_ eq 'cust_classnum' } $cgi->param ) {
292 my @classnums = grep /^\d*$/, $cgi->param('cust_classnum');
293 push @where, 'COALESCE( cust_main.classnum, 0) IN ( '.
294 join(',', map { $_ || '0' } @classnums ).
301 if ( $cgi->param('custnum') =~ /^(\d+)$/ ) {
302 push @where, "cust_main.custnum = $1";
305 # we want the package and its definition if available
307 ' LEFT JOIN cust_pkg USING (pkgnum)
308 LEFT JOIN part_pkg USING (pkgpart)
309 LEFT JOIN part_fee USING (feepart)';
311 my $part_pkg = 'part_pkg';
312 # "Separate sub-packages from parents"
313 my $use_override = $cgi->param('use_override') ? 1 : 0;
314 if ( $use_override ) {
315 # still need the real part_pkg for tax applicability,
317 $join_pkg .= " LEFT JOIN part_pkg AS override ON (
318 COALESCE(cust_bill_pkg.pkgpart_override, cust_pkg.pkgpart, 0) = override.pkgpart
320 $part_pkg = 'override';
322 push @select, "$part_pkg.pkgpart", "$part_pkg.pkg";
323 push @select, "COALESCE($part_pkg.taxclass, part_fee.taxclass) AS taxclass"
324 if $conf->exists('enable_taxclasses');
327 if ( $cgi->param('nottax') ) {
329 push @select, "($itemdesc) AS itemdesc";
332 '(cust_bill_pkg.pkgnum > 0 OR cust_bill_pkg.feepart IS NOT NULL)';
334 my @tax_where; # will go into a subquery
335 my @exempt_where; # will also go into a subquery
337 # classnum (of override pkgpart if applicable)
338 # not specified: all classes
341 if ( grep { $_ eq 'classnum' } $cgi->param ) {
342 my @classnums = grep /^\d*$/, $cgi->param('classnum');
343 push @where, "COALESCE(part_fee.classnum, $part_pkg.classnum, 0) IN ( ".
344 join(',', @classnums ).
349 if ( grep { $_ eq 'report_optionnum' } $cgi->param ) {
350 my $num = join(',', grep /^[\d,]+$/, $cgi->param('report_optionnum'));
351 my $not_num = join(',', grep /^[\d,]+$/, $cgi->param('not_report_optionnum'));
352 my $all = $cgi->param('all_report_options') ? 1 : 0;
353 push @where, # code reuse FTW
354 FS::Report::Table->with_report_option(
355 report_optionnum => $num,
356 not_report_optionnum => $not_num,
357 use_override => $use_override,
358 all_report_options => $all,
363 if ( $cgi->param('taxclassNULL') ) {
364 # a little different from 'taxclass' in that it applies to the
365 # effective taxclass, not the real one
366 push @tax_where, 'cust_main_county.taxclass IS NULL'
367 } elsif ( $cgi->param('taxclass') ) {
368 push @tax_where, "COALESCE(part_fee.taxclass, $part_pkg.taxclass) IN (" .
369 join(', ', map {dbh->quote($_)} $cgi->param('taxclass') ).
373 if ( $cgi->param('exempt_cust') eq 'Y' ) {
374 # tax-exempt customers
375 push @exempt_where, "(exempt_cust = 'Y' OR exempt_cust_taxname = 'Y')";
377 } elsif ( $cgi->param('exempt_pkg') eq 'Y' ) { # non-taxable package
378 # non-taxable package charges
379 push @exempt_where, "(exempt_setup = 'Y' OR exempt_recur = 'Y')";
381 # we don't handle exempt_monthly here
383 if ( $cgi->param('taxname') ) { # specific taxname
384 push @tax_where, "COALESCE(cust_main_county.taxname, 'Tax') = ".
385 dbh->quote($cgi->param('taxname'));
388 # country:state:county:city:district (may be repeated)
389 # You can also pass a big list of taxnums but that leads to huge URLs.
390 # Note that this means "packages whose tax is in this region", not
391 # "packages in this region". It's meant for links from the tax report.
392 if ( $cgi->param('region') ) {
394 foreach ( $cgi->param('region') ) {
396 @loc{qw(country state county city district)} =
397 split(':', $cgi->param('region'));
398 my $string = join(' AND ',
401 "$_ = ".dbh->quote($loc{$_});
407 push @orwhere, "($string)";
409 push @tax_where, '(' . join(' OR ', @orwhere) . ')' if @orwhere;
413 if ( $cgi->param('taxnum') =~ /^([\d,]+)$/) {
414 push @tax_where, "cust_main_county.taxnum IN ($1)";
417 # If we're showing exempt items, we need to find those with
418 # cust_tax_exempt_pkg records matching the selected taxes.
419 # If we're showing taxable items, we need to find those with
420 # cust_bill_pkg_tax_location records. We also need to find the
421 # exemption records so that we can show the taxable amount.
422 # If we're showing all items, we need the union of those.
423 # If we're showing 'out' (items that aren't region/class taxable),
424 # then we need the set of all items minus the union of those.
428 if ( @exempt_where or @tax_where
429 or $cgi->param('taxable') or $cgi->param('out') )
431 # process exemption restrictions, including @tax_where
432 my $exempt_sub = 'SELECT SUM(amount) as exempt_amount, billpkgnum
433 FROM cust_tax_exempt_pkg JOIN cust_main_county USING (taxnum)';
435 $exempt_sub .= ' WHERE '.join(' AND ', @tax_where, @exempt_where)
436 if (@tax_where or @exempt_where);
438 $exempt_sub .= ' GROUP BY billpkgnum';
440 $join_pkg .= " LEFT JOIN ($exempt_sub) AS item_exempt
443 # process tax restrictions
445 'cust_bill_pkg_tax_location.taxable_billpkgnum = cust_bill_pkg.billpkgnum',
446 'cust_main_county.tax > 0';
449 my $tax_sub = "SELECT 1
450 FROM cust_bill_pkg_tax_location
451 JOIN cust_bill_pkg AS tax_item USING (billpkgnum)
452 JOIN cust_main_county USING (taxnum)
453 WHERE ". join(' AND ', @tax_where);
455 # now do something with that
456 if ( @exempt_where ) {
458 push @where, 'item_exempt.billpkgnum IS NOT NULL';
459 push @select, 'item_exempt.exempt_amount';
460 push @peritem, 'exempt_amount';
461 push @peritem_desc, 'Exempt';
462 push @total, 'SUM(exempt_amount)';
463 push @total_desc, "$money_char%.2f tax-exempt";
465 } elsif ( $cgi->param('taxable') ) {
467 my $taxable = 'cust_bill_pkg.setup + cust_bill_pkg.recur '.
468 '- COALESCE(item_exempt.exempt_amount, 0)';
470 push @select, "($taxable) AS taxable_amount";
471 push @where, "EXISTS($tax_sub)";
472 push @peritem, 'taxable_amount';
473 push @peritem_desc, 'Taxable';
474 push @total, "SUM($taxable)";
475 push @total_desc, "$money_char%.2f taxable";
477 } elsif ( @tax_where ) {
479 # union of taxable + all exempt_ cases
480 push @where, "(EXISTS($tax_sub) OR item_exempt.billpkgnum IS NOT NULL)";
484 # recur/usage separation
485 if ( $cgi->param('usage') eq 'recurring' ) {
487 my $recur_no_usage = FS::cust_bill_pkg->charged_sql('', '', no_usage => 1);
488 push @select, "($recur_no_usage) AS recur_no_usage";
489 $peritem[1] = 'recur_no_usage';
490 $total[1] = "SUM(cust_bill_pkg.setup + $recur_no_usage)";
491 $total_desc[0] .= ' (excluding usage)';
493 } elsif ( $cgi->param('usage') eq 'usage' ) {
495 my $usage = FS::cust_bill_pkg->usage_sql();
496 push @select, "($usage) AS _usage";
497 # there's already a method named 'usage'
498 $peritem[1] = '_usage';
499 $peritem_desc[1] = 'Usage charge';
500 $total[1] = "SUM($usage)";
501 $total_desc[0] .= ' usage charges';
504 } elsif ( $cgi->param('istax') ) {
506 @peritem = ( 'setup' ); # taxes only have setup
507 @peritem_desc = ( 'Tax charge' );
509 push @where, 'cust_bill_pkg.pkgnum = 0';
511 # tax location when using tax_rate_location
512 if ( $cgi->param('vendortax') ) {
514 $join_pkg .= ' LEFT JOIN cust_bill_pkg_tax_rate_location USING ( billpkgnum ) '.
515 ' LEFT JOIN tax_rate_location USING ( taxratelocationnum )';
516 foreach (qw( state county city locationtaxid)) {
517 if ( scalar($cgi->param($_)) ) {
518 my $place = dbh->quote( $cgi->param($_) );
519 push @where, "tax_rate_location.$_ = $place";
524 COALESCE(cust_bill_pkg_tax_rate_location.amount,
525 cust_bill_pkg.setup + cust_bill_pkg.recur)
528 } elsif ( $cgi->param('out') ) {
531 LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum)
533 push @where, 'cust_bill_pkg_tax_location.billpkgnum IS NULL';
535 # each billpkgnum should appear only once
536 $total[0] = 'COUNT(*)';
537 $total[1] = 'SUM(cust_bill_pkg.setup)';
539 } else { # not locationtaxid or 'out'--the normal case
542 LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum)
543 JOIN cust_main_county USING (taxnum)
546 # don't double-count the components of consolidated taxes
547 $total[0] = 'COUNT(DISTINCT cust_bill_pkg.billpkgnum)';
548 $total[1] = 'SUM(cust_bill_pkg_tax_location.amount)';
551 if ( grep { $_ eq 'classnum' } $cgi->param ) {
552 my @classnums = grep /^\d*$/, $cgi->param('classnum');
554 JOIN cust_pkg AS taxed_pkg
555 ON (cust_bill_pkg_tax_location.pkgnum = taxed_pkg.pkgnum)
556 JOIN part_pkg AS taxed_part_pkg
557 ON (taxed_pkg.pkgpart = taxed_part_pkg.pkgpart)
559 push @where, "COALESCE(taxed_part_pkg.classnum, 0) IN ( ".
560 join(',', @classnums ).
567 if ( $cgi->param('taxclassNULL') ) {
568 push @where, 'cust_main_county.taxclass IS NULL';
572 if ( $cgi->param('taxnameNULL') ) {
573 push @where, 'cust_main_county.taxname IS NULL OR '.
574 'cust_main_county.taxname = \'Tax\'';
575 } elsif ( $cgi->param('taxname') ) {
576 push @where, 'cust_main_county.taxname = '.
577 dbh->quote($cgi->param('taxname'));
580 # itemdesc, for breakdown from the vendor tax report
581 if ( $cgi->param('itemdesc') ) {
582 if ( $cgi->param('itemdesc') eq 'Tax' ) {
583 push @where, "($itemdesc = 'Tax' OR $itemdesc is null)";
585 push @where, "$itemdesc = ". dbh->quote($cgi->param('itemdesc'));
593 my $pay_sub = "SELECT SUM(cust_bill_pay_pkg.amount)
594 FROM cust_bill_pay_pkg
595 WHERE cust_bill_pkg.billpkgnum = cust_bill_pay_pkg.billpkgnum
597 push @select, "($pay_sub) AS pay_amount";
601 if ( $cgi->param('credit') ) {
605 if ( $cgi->param('istax') ) {
606 # then we need to group/join by billpkgtaxlocationnum, to get only the
607 # relevant part of partial taxes
608 my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
609 reason.reason as reason_text, access_user.username AS username_text,
610 billpkgtaxlocationnum, billpkgnum
611 FROM cust_credit_bill_pkg
612 JOIN cust_credit_bill USING (creditbillnum)
613 JOIN cust_credit USING (crednum)
614 LEFT JOIN reason USING (reasonnum)
615 LEFT JOIN access_user USING (usernum)
616 GROUP BY billpkgnum, billpkgtaxlocationnum, reason.reason,
617 access_user.username";
619 if ( $cgi->param('out') ) {
621 # find credits that are applied to the line items, but not to
622 # a cust_bill_pkg_tax_location link
623 $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
625 push @where, 'item_credit.billpkgtaxlocationnum IS NULL';
629 # find credits that are applied to the CBPTL links that are
630 # considered "interesting" by the report criteria
631 $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
632 USING (billpkgtaxlocationnum)";
637 # then only group by billpkgnum
638 my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
639 reason.reason as reason_text, access_user.username AS username_text,
641 FROM cust_credit_bill_pkg
642 JOIN cust_credit_bill USING (creditbillnum)
643 JOIN cust_credit USING (crednum)
644 LEFT JOIN reason USING (reasonnum)
645 LEFT JOIN access_user USING (usernum)
646 GROUP BY billpkgnum, reason.reason, access_user.username";
647 $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit USING (billpkgnum)";
650 push @where, 'item_credit.billpkgnum IS NOT NULL';
651 push @select, 'item_credit.credit_amount',
652 'item_credit.username_text',
653 'item_credit.reason_text';
654 push @peritem, 'credit_amount', 'username_text', 'reason_text';
655 push @peritem_desc, 'Credited', 'By', 'Reason';
656 push @total, 'SUM(credit_amount)';
657 push @total_desc, "$money_char%.2f credited";
661 #still want a credit total column
664 SELECT SUM(cust_credit_bill_pkg.amount)
665 FROM cust_credit_bill_pkg
666 WHERE cust_bill_pkg.billpkgnum = cust_credit_bill_pkg.billpkgnum
668 push @select, "($credit_sub) AS credit_amount";
672 push @select, 'cust_main.custnum', FS::UI::Web::cust_sql_fields();
675 if ( $cgi->param('salesnum') =~ /^(\d+)$/ ) {
678 my $sales = FS::sales->by_key($salesnum)
679 or die "salesnum $salesnum not found";
681 my $subsearch = $sales->cust_bill_pkg_search('', '',
682 'cust_main_sales' => ($cgi->param('cust_main_sales') ? 1 : 0),
683 'paid' => ($cgi->param('paid') ? 1 : 0),
684 'classnum' => scalar($cgi->param('classnum'))
686 $join_pkg .= " JOIN sales_pkg_class ON ( COALESCE(sales_pkg_class.classnum, 0) = COALESCE( part_fee.classnum, part_pkg.classnum, 0) )";
688 my $extra_sql = $subsearch->{extra_sql};
689 $extra_sql =~ s/^WHERE//;
690 push @where, $extra_sql;
692 $cgi->param('classnum', 0) unless $cgi->param('classnum');
696 my $where = join(' AND ', @where);
697 $where &&= "WHERE $where";
700 'table' => 'cust_bill_pkg',
701 'addl_from' => "$join_pkg $join_cust",
703 'select' => join(",\n", @select ),
704 'extra_sql' => $where,
705 'order_by' => 'ORDER BY cust_bill._date, cust_bill_pkg.billpkgnum',
709 'SELECT ' . join(',', @total) .
710 " FROM cust_bill_pkg $join_pkg $join_cust
713 @peritem_desc = map {emt($_)} @peritem_desc;
714 my @peritem_sub = map {
716 if ($field =~ /_text$/) { # kludge for credit reason/username fields
717 sub {$_[0]->get($field)};
719 sub { sprintf($money_char.'%.2f', $_[0]->get($field)) }
722 my @peritem_null = map { '' } @peritem; # placeholders
723 my $peritem_align = 'r' x scalar(@peritem);
725 @currency_desc = map {emt($_)} @currency_desc;
726 my @currency_null = map { '' } @currency; # placeholders
727 my $currency_align = 'r' x scalar(@currency);
729 my $ilink = [ "${p}view/cust_bill.cgi?", 'invnum' ];
730 my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ];
732 my $pay_link = ''; #[, 'billpkgnum', ];
733 my $credit_link = [ "${p}search/cust_credit_bill_pkg.html?billpkgnum=", 'billpkgnum', ];
735 warn "\n\nQUERY:\n".Dumper($query)."\n\nCOUNT_QUERY:\n$count_query\n\n"
736 if $cgi->param('debug');