1 <& elements/search.html,
2 'title' => emt('Line items'),
3 'name' => emt('line items'),
5 'count_query' => $count_query,
6 'count_addl' => \@total_desc,
17 FS::UI::Web::cust_header(),
21 sub { $_[0]->pkgnum > 0
22 ? $_[0]->get('pkgpart')
25 sub { $_[0]->pkgnum > 0
27 : $_[0]->get('itemdesc')
30 #strikethrough or "N/A ($amount)" or something these when
31 # they're not applicable to pkg_tax search
34 sub { time2str('%b %d %Y', shift->_date ) },
35 sub { sprintf($money_char.'%.2f', shift->get('pay_amount')) },
36 sub { sprintf($money_char.'%.2f', shift->get('credit_amount')) },
37 \&FS::UI::Web::cust_fields,
49 FS::UI::Web::cust_sort_fields(),
61 ( map { $_ ne 'Cust. Status' ? $clink : '' }
62 FS::UI::Web::cust_header()
65 #'align' => 'rlrrrc'.FS::UI::Web::cust_aligns(),
66 'align' => $pkgnum_align.
71 FS::UI::Web::cust_aligns(),
82 FS::UI::Web::cust_colors(),
94 FS::UI::Web::cust_styles(),
99 Output control parameters:
100 - distribute: Boolean. If true, recurring fees will be "prorated" for the
101 portion of the package date range (sdate-edate) that falls within the date
102 range of the report. Line items will be limited to those for which this
103 portion is > 0. This disables filtering on invoice date.
105 - usage: Separate usage (cust_bill_pkg_detail records) from
106 recurring charges. If set to "usage", will show usage instead of
107 recurring charges. If set to "recurring", will deduct usage and only
108 show the flat rate charge. If not passed, the "recurring charge" column
109 will include usage charges also.
111 Filtering parameters:
112 - begin, end: Date range. Applies to invoice date, not necessarily package
113 date range. But see "distribute".
115 - status: Customer status (active, suspended, etc.). This will filter on
116 _current_ customer status, not status at the time the invoice was generated.
118 - agentnum: Filter on customer agent.
120 - refnum: Filter on customer reference source.
122 - cust_classnum: Filter on customer class.
124 - classnum: Filter on package class.
126 - report_optionnum: Filter on package report class. Can be a single report
127 class number or a comma-separated list (where 0 is "no report class"), or the
130 - use_override: Apply "classnum" and "taxclass" filtering based on the
131 override (bundle) pkgpart, rather than always using the true pkgpart.
133 - nottax: Limit to items that are not taxes (pkgnum > 0).
135 - istax: Limit to items that are taxes (pkgnum == 0).
137 - taxnum: Limit to items whose tax definition matches this taxnum.
138 With "nottax" that means items that are subject to that tax;
139 with "istax" it's the tax charges themselves. Can be specified
140 more than once to include multiple taxes.
142 - country, state, county, city: Limit to items whose tax location
143 matches these fields. If "nottax" it's the tax location of the package;
144 if "istax" the location of the tax.
146 - taxname, taxnameNULL: With "nottax", limit to items whose tax location
147 matches a tax with this name. With "istax", limit to items that have
148 this tax name. taxnameNULL is equivalent to "taxname = '' OR taxname
151 - out: With "nottax", limit to items that don't match any tax definition.
152 With "istax", find tax items that are unlinked to their tax definitions.
153 Current Freeside (> July 2012) always creates tax links, but unlinked
154 items may result from an incomplete upgrade of legacy data.
156 - locationtaxid: With "nottax", limit to packages matching this
157 tax_rate_location ID; with "tax", limit to taxes generated from that
160 - taxclass: Filter on package taxclass.
162 - taxclassNULL: With "nottax", limit to items that would be subject to the
163 tax with taxclass = NULL. This doesn't necessarily mean part_pkg.taxclass
164 is NULL; it also includes taxclasses that don't have a tax in this region.
166 - itemdesc: Limit to line items with this description. Note that non-tax
167 packages usually have a description of NULL. (Deprecated.)
169 - report_group: Can contain '=' or '!=' followed by a string to limit to
170 line items where itemdesc starts with, or doesn't start with, the string.
172 - cust_tax: Limit to customers who are tax-exempt. If "taxname" is also
173 specified, limit to customers who are also specifically exempt from that
176 - pkg_tax: Limit to packages that are tax-exempt, and only include the
177 exempt portion (setup, recurring, or both) when calculating totals.
179 - taxable: Limit to packages that are subject to tax, i.e. where a
180 cust_bill_pkg_tax_location record exists.
182 - credit: Limit to line items that received a credit application. The
183 amount of the credit will also be shown.
188 my $curuser = $FS::CurrentUser::CurrentUser;
190 die "access denied" unless $curuser->access_right('Financial reports');
192 my $conf = new FS::Conf;
193 my $money_char = $conf->config('money_char') || '$';
195 my @select = ( 'cust_bill_pkg.*', 'cust_bill._date' );
196 my @total = ( 'COUNT(*)', 'SUM(cust_bill_pkg.setup + cust_bill_pkg.recur)');
197 my @total_desc = ( $money_char.'%.2f total' ); # sprintf strings
199 my @peritem = ( 'setup', 'recur' );
200 my @peritem_desc = ( 'Setup charge', 'Recurring charge' );
202 my @pkgnum_header = ();
205 my $pkgnum_align = '';
206 if ( $curuser->option('show_pkgnum') ) {
207 push @select, 'cust_bill_pkg.pkgnum';
208 push @pkgnum_header, 'Pkg Num';
209 push @pkgnum, sub { $_[0]->pkgnum > 0 ? $_[0]->pkgnum : '' };
210 push @pkgnum_null, '';
211 $pkgnum_align .= 'r';
214 my @post_desc_header = ();
216 my @post_desc_null = ();
217 my $post_desc_align = '';
218 if ( $conf->exists('enable_taxclasses') ) {
219 push @post_desc_header, 'Tax class';
220 push @post_desc, 'taxclass';
221 push @post_desc_null, '';
222 $post_desc_align .= 'l';
225 # valid in both the tax and non-tax cases
227 " LEFT JOIN cust_bill ON (cust_bill_pkg.invnum = cust_bill.invnum)".
228 # use cust_pkg.locationnum if it exists
229 FS::UI::Web::join_cust_main('cust_bill', 'cust_pkg');
231 #agent virtualization
233 $FS::CurrentUser::CurrentUser->agentnums_sql( 'table' => 'cust_main' );
235 my @where = ( $agentnums_sql );
238 my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
240 if ( $cgi->param('distribute') == 1 ) {
241 push @where, "sdate <= $ending",
242 "edate > $beginning",
246 push @where, "cust_bill._date >= $beginning",
247 "cust_bill._date <= $ending";
251 if ( $cgi->param('status') =~ /^([a-z]+)$/ ) {
252 push @where, FS::cust_main->cust_status_sql . " = '$1'";
256 if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
257 push @where, "cust_main.agentnum = $1";
260 # salesnum--see below
262 if ( $cgi->param('refnum') =~ /^(\d+)$/ ) {
263 push @where, "cust_main.refnum = $1";
266 # 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)
267 if ( grep { $_ eq 'cust_classnum' } $cgi->param ) {
268 my @classnums = grep /^\d*$/, $cgi->param('cust_classnum');
269 push @where, 'COALESCE( cust_main.classnum, 0) IN ( '.
270 join(',', map { $_ || '0' } @classnums ).
277 if ( $cgi->param('custnum') =~ /^(\d+)$/ ) {
278 push @where, "cust_main.custnum = $1";
281 # we want the package and its definition if available
283 ' LEFT JOIN cust_pkg USING (pkgnum)
284 LEFT JOIN part_pkg USING (pkgpart)';
286 my $part_pkg = 'part_pkg';
287 # "Separate sub-packages from parents"
288 my $use_override = $cgi->param('use_override') ? 1 : 0;
289 if ( $use_override ) {
290 # still need the real part_pkg for tax applicability,
292 $join_pkg .= " LEFT JOIN part_pkg AS override ON (
293 COALESCE(cust_bill_pkg.pkgpart_override, cust_pkg.pkgpart, 0) = override.pkgpart
295 $part_pkg = 'override';
297 push @select, "$part_pkg.pkgpart", "$part_pkg.pkg";
298 push @select, "$part_pkg.taxclass" if $conf->exists('enable_taxclasses');
301 if ( $cgi->param('nottax') ) {
303 push @where, 'cust_bill_pkg.pkgnum > 0';
305 my @tax_where; # will go into a subquery
306 my @exempt_where; # will also go into a subquery
308 # classnum (of override pkgpart if applicable)
309 # not specified: all classes
312 if ( grep { $_ eq 'classnum' } $cgi->param ) {
313 my @classnums = grep /^\d+$/, $cgi->param('classnum');
314 push @where, "COALESCE($part_pkg.classnum, 0) IN ( ".
315 join(',', @classnums ).
320 if ( grep { $_ eq 'report_optionnum' } $cgi->param ) {
321 my $num = join(',', grep /^[\d,]+$/, $cgi->param('report_optionnum'));
322 my $not_num = join(',', grep /^[\d,]+$/, $cgi->param('not_report_optionnum'));
323 my $all = $cgi->param('all_report_options') ? 1 : 0;
324 push @where, # code reuse FTW
325 FS::Report::Table->with_report_option(
326 report_optionnum => $num,
327 not_report_optionnum => $not_num,
328 use_override => $use_override,
329 all_report_options => $all,
334 if ( $cgi->param('taxclassNULL') ) {
335 # a little different from 'taxclass' in that it applies to the
336 # effective taxclass, not the real one
337 push @tax_where, 'cust_main_county.taxclass IS NULL'
338 } elsif ( $cgi->param('taxclass') ) {
339 push @tax_where, "$part_pkg.taxclass IN (" .
340 join(', ', map {dbh->quote($_)} $cgi->param('taxclass') ).
344 if ( $cgi->param('exempt_cust') eq 'Y' ) {
345 # tax-exempt customers
346 push @exempt_where, "(exempt_cust = 'Y' OR exempt_cust_taxname = 'Y')";
348 } elsif ( $cgi->param('exempt_pkg') eq 'Y' ) { # non-taxable package
349 # non-taxable package charges
350 push @exempt_where, "(exempt_setup = 'Y' OR exempt_recur = 'Y')";
352 # we don't handle exempt_monthly here
354 if ( $cgi->param('taxname') ) { # specific taxname
355 push @tax_where, 'cust_main_county.taxname = '.
356 dbh->quote($cgi->param('taxname'));
357 } elsif ( $cgi->param('taxnameNULL') ) {
358 push @tax_where, 'cust_main_county.taxname IS NULL OR '.
359 'cust_main_county.taxname = \'Tax\'';
362 # country:state:county:city:district (may be repeated)
363 # You can also pass a big list of taxnums but that leads to huge URLs.
364 # Note that this means "packages whose tax is in this region", not
365 # "packages in this region". It's meant for links from the tax report.
366 if ( $cgi->param('region') ) {
368 foreach ( $cgi->param('region') ) {
370 @loc{qw(country state county city district)} =
371 split(':', $cgi->param('region'));
372 my $string = join(' AND ',
375 "$_ = ".dbh->quote($loc{$_});
381 push @orwhere, "($string)";
383 push @tax_where, '(' . join(' OR ', @orwhere) . ')' if @orwhere;
387 if ( $cgi->param('taxnum') ) {
388 my $taxnum_in = join(',',
389 grep /^\d+$/, $cgi->param('taxnum')
391 push @tax_where, "cust_main_county.taxnum IN ($taxnum_in)"
395 # If we're showing exempt items, we need to find those with
396 # cust_tax_exempt_pkg records matching the selected taxes.
397 # If we're showing taxable items, we need to find those with
398 # cust_bill_pkg_tax_location records. We also need to find the
399 # exemption records so that we can show the taxable amount.
400 # If we're showing all items, we need the union of those.
401 # If we're showing 'out' (items that aren't region/class taxable),
402 # then we need the set of all items minus the union of those.
406 if ( @exempt_where or @tax_where
407 or $cgi->param('taxable') or $cgi->param('out') )
409 # process exemption restrictions, including @tax_where
410 my $exempt_sub = 'SELECT SUM(amount) as exempt_amount, billpkgnum
411 FROM cust_tax_exempt_pkg JOIN cust_main_county USING (taxnum)';
413 $exempt_sub .= ' WHERE '.join(' AND ', @tax_where, @exempt_where)
414 if (@tax_where or @exempt_where);
416 $exempt_sub .= ' GROUP BY billpkgnum';
418 $join_pkg .= " LEFT JOIN ($exempt_sub) AS item_exempt
422 if ( @tax_where or $cgi->param('taxable') or $cgi->param('out') ) {
423 # process tax restrictions
425 'cust_main_county.tax > 0';
427 my $tax_sub = "SELECT invnum, cust_bill_pkg_tax_location.pkgnum
428 FROM cust_bill_pkg_tax_location
429 JOIN cust_bill_pkg AS tax_item USING (billpkgnum)
430 JOIN cust_main_county USING (taxnum)
431 WHERE ". join(' AND ', @tax_where).
432 " GROUP BY invnum, cust_bill_pkg_tax_location.pkgnum";
434 $join_pkg .= " LEFT JOIN ($tax_sub) AS item_tax
435 ON (item_tax.invnum = cust_bill_pkg.invnum AND
436 item_tax.pkgnum = cust_bill_pkg.pkgnum)";
439 # now do something with that
440 if ( @exempt_where ) {
442 push @where, 'item_exempt.billpkgnum IS NOT NULL';
443 push @select, 'item_exempt.exempt_amount';
444 push @peritem, 'exempt_amount';
445 push @peritem_desc, 'Exempt';
446 push @total, 'SUM(exempt_amount)';
447 push @total_desc, "$money_char%.2f tax-exempt";
449 } elsif ( $cgi->param('taxable') ) {
451 my $taxable = 'cust_bill_pkg.setup + cust_bill_pkg.recur '.
452 '- COALESCE(item_exempt.exempt_amount, 0)';
454 push @where, 'item_tax.invnum IS NOT NULL';
455 push @select, "($taxable) AS taxable_amount";
456 push @peritem, 'taxable_amount';
457 push @peritem_desc, 'Taxable';
458 push @total, "SUM($taxable)";
459 push @total_desc, "$money_char%.2f taxable";
461 } elsif ( $cgi->param('out') ) {
463 push @where, 'item_tax.invnum IS NULL',
464 'item_exempt.billpkgnum IS NULL';
466 } elsif ( @tax_where ) {
468 # union of taxable + all exempt_ cases
470 '(item_tax.invnum IS NOT NULL OR item_exempt.billpkgnum IS NOT NULL)';
474 # recur/usage separation
475 if ( $cgi->param('usage') eq 'recurring' ) {
477 my $recur_no_usage = FS::cust_bill_pkg->charged_sql('', '', no_usage => 1);
478 push @select, "($recur_no_usage) AS recur_no_usage";
479 $peritem[1] = 'recur_no_usage';
480 $total[1] = "SUM(cust_bill_pkg.setup + $recur_no_usage)";
481 $total_desc[0] .= ' (excluding usage)';
483 } elsif ( $cgi->param('usage') eq 'usage' ) {
485 my $usage = FS::cust_bill_pkg->usage_sql();
486 push @select, "($usage) AS _usage";
487 # there's already a method named 'usage'
488 $peritem[1] = '_usage';
489 $peritem_desc[1] = 'Usage charge';
490 $total[1] = "SUM($usage)";
491 $total_desc[0] .= ' usage charges';
494 } elsif ( $cgi->param('istax') ) {
496 @peritem = ( 'setup' ); # taxes only have setup
497 @peritem_desc = ( 'Tax charge' );
499 push @where, 'cust_bill_pkg.pkgnum = 0';
501 # tax location when using tax_rate_location
502 if ( scalar( grep( /locationtaxid/, $cgi->param ) ) ) {
504 $join_pkg .= ' LEFT JOIN cust_bill_pkg_tax_rate_location USING ( billpkgnum ) '.
505 ' LEFT JOIN tax_rate_location USING ( taxratelocationnum )';
506 push @where, FS::tax_rate_location->location_sql(
507 map { $_ => (scalar($cgi->param($_)) || '') }
508 qw( district city county state locationtaxid )
512 COALESCE(cust_bill_pkg_tax_rate_location.amount,
513 cust_bill_pkg.setup + cust_bill_pkg.recur)
516 } elsif ( $cgi->param('out') ) {
519 LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum)
521 push @where, 'cust_bill_pkg_tax_location.billpkgnum IS NULL';
523 # each billpkgnum should appear only once
524 $total[0] = 'COUNT(*)';
525 $total[1] = 'SUM(cust_bill_pkg.setup)';
527 } else { # not locationtaxid or 'out'--the normal case
530 LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum)
531 JOIN cust_main_county USING (taxnum)
534 # don't double-count the components of consolidated taxes
535 $total[0] = 'COUNT(DISTINCT cust_bill_pkg.billpkgnum)';
536 $total[1] = 'SUM(cust_bill_pkg_tax_location.amount)';
540 if ( $cgi->param('taxclassNULL') ) {
541 push @where, 'cust_main_county.taxclass IS NULL';
545 if ( $cgi->param('taxnameNULL') ) {
546 push @where, 'cust_main_county.taxname IS NULL OR '.
547 'cust_main_county.taxname = \'Tax\'';
548 } elsif ( $cgi->param('taxname') ) {
549 push @where, 'cust_main_county.taxname = '.
550 dbh->quote($cgi->param('taxname'));
554 if ( $cgi->param('taxnum') ) {
555 my $taxnum_in = join(',',
556 grep /^\d+$/, $cgi->param('taxnum')
558 push @where, "cust_main_county.taxnum IN ($taxnum_in)"
562 # report group (itemdesc)
563 if ( $cgi->param('report_group') =~ /^(=|!=) (.*)$/ ) {
564 my ( $group_op, $group_value ) = ( $1, $2 );
565 if ( $group_op eq '=' ) {
566 #push @where, 'itemdesc LIKE '. dbh->quote($group_value.'%');
567 push @where, 'itemdesc = '. dbh->quote($group_value);
568 } elsif ( $group_op eq '!=' ) {
569 push @where, '( itemdesc != '. dbh->quote($group_value) .' OR itemdesc IS NULL )';
571 die "guru meditation #00de: group_op $group_op\n";
575 # itemdesc, for some reason
576 if ( $cgi->param('itemdesc') ) {
577 if ( $cgi->param('itemdesc') eq 'Tax' ) {
578 push @where, "(itemdesc='Tax' OR itemdesc is null)";
580 push @where, 'itemdesc='. dbh->quote($cgi->param('itemdesc'));
588 my $pay_sub = "SELECT SUM(cust_bill_pay_pkg.amount)
589 FROM cust_bill_pay_pkg
590 WHERE cust_bill_pkg.billpkgnum = cust_bill_pay_pkg.billpkgnum
592 push @select, "($pay_sub) AS pay_amount";
596 if ( $cgi->param('credit') ) {
600 if ( $cgi->param('istax') ) {
601 # then we need to group/join by billpkgtaxlocationnum, to get only the
602 # relevant part of partial taxes
603 my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
604 reason.reason as reason_text, access_user.username AS username_text,
605 billpkgtaxlocationnum, billpkgnum
606 FROM cust_credit_bill_pkg
607 JOIN cust_credit_bill USING (creditbillnum)
608 JOIN cust_credit USING (crednum)
609 LEFT JOIN reason USING (reasonnum)
610 LEFT JOIN access_user USING (usernum)
611 GROUP BY billpkgnum, billpkgtaxlocationnum, reason.reason,
612 access_user.username";
614 if ( $cgi->param('out') ) {
616 # find credits that are applied to the line items, but not to
617 # a cust_bill_pkg_tax_location link
618 $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
620 push @where, 'item_credit.billpkgtaxlocationnum IS NULL';
624 # find credits that are applied to the CBPTL links that are
625 # considered "interesting" by the report criteria
626 $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
627 USING (billpkgtaxlocationnum)";
632 # then only group by billpkgnum
633 my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
634 reason.reason as reason_text, access_user.username AS username_text,
636 FROM cust_credit_bill_pkg
637 JOIN cust_credit_bill USING (creditbillnum)
638 JOIN cust_credit USING (crednum)
639 LEFT JOIN reason USING (reasonnum)
640 LEFT JOIN access_user USING (usernum)
641 GROUP BY billpkgnum, reason.reason, access_user.username";
642 $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit USING (billpkgnum)";
645 push @where, 'item_credit.billpkgnum IS NOT NULL';
646 push @select, 'item_credit.credit_amount',
647 'item_credit.username_text',
648 'item_credit.reason_text';
649 push @peritem, 'credit_amount', 'username_text', 'reason_text';
650 push @peritem_desc, 'Credited', 'By', 'Reason';
651 push @total, 'SUM(credit_amount)';
652 push @total_desc, "$money_char%.2f credited";
656 #still want a credit total column
659 SELECT SUM(cust_credit_bill_pkg.amount)
660 FROM cust_credit_bill_pkg
661 WHERE cust_bill_pkg.billpkgnum = cust_credit_bill_pkg.billpkgnum
663 push @select, "($credit_sub) AS credit_amount";
667 push @select, 'cust_main.custnum', FS::UI::Web::cust_sql_fields();
670 if ( $cgi->param('salesnum') =~ /^(\d+)$/ ) {
673 my $sales = FS::sales->by_key($salesnum)
674 or die "salesnum $salesnum not found";
676 my $subsearch = $sales->cust_bill_pkg_search('', '',
677 'cust_main_sales' => ($cgi->param('cust_main_sales') ? 1 : 0),
678 'paid' => ($cgi->param('paid') ? 1 : 0),
679 'classnum' => scalar($cgi->param('classnum'))
681 $join_pkg .= " JOIN sales_pkg_class ON ( COALESCE(sales_pkg_class.classnum, 0) = COALESCE( part_pkg.classnum, 0) )";
683 my $extra_sql = $subsearch->{extra_sql};
684 $extra_sql =~ s/^WHERE//;
685 push @where, $extra_sql;
687 $cgi->param('classnum', 0) unless $cgi->param('classnum');
691 my $where = join(' AND ', @where);
692 $where &&= "WHERE $where";
695 'table' => 'cust_bill_pkg',
696 'addl_from' => "$join_pkg $join_cust",
698 'select' => join(",\n", @select ),
699 'extra_sql' => $where,
700 'order_by' => 'ORDER BY cust_bill._date, cust_bill_pkg.billpkgnum',
704 'SELECT ' . join(',', @total) .
705 " FROM cust_bill_pkg $join_pkg $join_cust
708 @peritem_desc = map {emt($_)} @peritem_desc;
709 my @peritem_sub = map {
711 if ($field =~ /_text$/) { # kludge for credit reason/username fields
712 sub {$_[0]->get($field)};
714 sub { sprintf($money_char.'%.2f', $_[0]->get($field)) }
717 my @peritem_null = map { '' } @peritem; # placeholders
718 my $peritem_align = 'r' x scalar(@peritem);
720 my $ilink = [ "${p}view/cust_bill.cgi?", 'invnum' ];
721 my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ];
723 my $pay_link = ''; #[, 'billpkgnum', ];
724 my $credit_link = [ "${p}search/cust_credit_bill_pkg.html?billpkgnum=", 'billpkgnum', ];
726 warn "\n\nQUERY:\n".Dumper($query)."\n\nCOUNT_QUERY:\n$count_query\n\n"
727 if $cgi->param('debug');