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 # possibly use override.pkg but i think this correct
24 ? $_[0]->get('pkgpart')
27 sub { $_[0]->pkgnum > 0
28 # possibly use override.pkg but i think this correct
30 : $_[0]->get('itemdesc')
33 #strikethrough or "N/A ($amount)" or something these when
34 # they're not applicable to pkg_tax search
38 sub { time2str('%b %d %Y', shift->_date ) },
39 sub { sprintf($money_char.'%.2f', shift->get('pay_amount')) },
40 sub { sprintf($money_char.'%.2f', shift->get('credit_amount')) },
41 \&FS::UI::Web::cust_fields,
54 FS::UI::Web::cust_sort_fields(),
67 ( map { $_ ne 'Cust. Status' ? $clink : '' }
68 FS::UI::Web::cust_header()
71 #'align' => 'rlrrrc'.FS::UI::Web::cust_aligns(),
72 'align' => $pkgnum_align.
78 FS::UI::Web::cust_aligns(),
90 FS::UI::Web::cust_colors(),
103 FS::UI::Web::cust_styles(),
108 Output control parameters:
109 - distribute: Boolean. If true, recurring fees will be "prorated" for the
110 portion of the package date range (sdate-edate) that falls within the date
111 range of the report. Line items will be limited to those for which this
112 portion is > 0. This disables filtering on invoice date.
114 - usage: Separate usage (cust_bill_pkg_detail records) from
115 recurring charges. If set to "usage", will show usage instead of
116 recurring charges. If set to "recurring", will deduct usage and only
117 show the flat rate charge. If not passed, the "recurring charge" column
118 will include usage charges also.
120 Filtering parameters:
121 - begin, end: Date range. Applies to invoice date, not necessarily package
122 date range. But see "distribute".
124 - status: Customer status (active, suspended, etc.). This will filter on
125 _current_ customer status, not status at the time the invoice was generated.
127 - agentnum: Filter on customer agent.
129 - refnum: Filter on customer reference source.
131 - cust_classnum: Filter on customer class.
133 - classnum: Filter on package class.
135 - report_optionnum: Filter on package report class. Can be a single report
136 class number or a comma-separated list (where 0 is "no report class"), or the
139 - use_override: Apply "classnum" and "taxclass" filtering based on the
140 override (bundle) pkgpart, rather than always using the true pkgpart.
142 - nottax: Limit to items that are not taxes (pkgnum > 0).
144 - istax: Limit to items that are taxes (pkgnum == 0).
146 - taxnum: Limit to items whose tax definition matches this taxnum.
147 With "nottax" that means items that are subject to that tax;
148 with "istax" it's the tax charges themselves. Can be a comma-separated
149 list to include multiple taxes.
151 - country, state, county, city: Limit to items whose tax location
152 matches these fields. If "nottax" it's the tax location of the package;
153 if "istax" the location of the tax.
155 - taxname, taxnameNULL: With "nottax", limit to items whose tax location
156 matches a tax with this name. With "istax", limit to items that have
157 this tax name. taxnameNULL is equivalent to "taxname = '' OR taxname
160 - out: With "nottax", limit to items that don't match any tax definition.
161 With "istax", find tax items that are unlinked to their tax definitions.
162 Current Freeside (> July 2012) always creates tax links, but unlinked
163 items may result from an incomplete upgrade of legacy data.
165 - locationtaxid: With "nottax", limit to packages matching this
166 tax_rate_location ID; with "tax", limit to taxes generated from that
169 - taxclass: Filter on package taxclass.
171 - taxclassNULL: With "nottax", limit to items that would be subject to the
172 tax with taxclass = NULL. This doesn't necessarily mean part_pkg.taxclass
173 is NULL; it also includes taxclasses that don't have a tax in this region.
175 - itemdesc: Limit to line items with this description. Note that non-tax
176 packages usually have a description of NULL. (Deprecated.)
178 - report_group: Can contain '=' or '!=' followed by a string to limit to
179 line items where itemdesc starts with, or doesn't start with, the string.
181 - cust_tax: Limit to customers who are tax-exempt. If "taxname" is also
182 specified, limit to customers who are also specifically exempt from that
185 - pkg_tax: Limit to packages that are tax-exempt, and only include the
186 exempt portion (setup, recurring, or both) when calculating totals.
188 - taxable: Limit to packages that are subject to tax, i.e. where a
189 cust_bill_pkg_tax_location record exists.
191 - credit: Limit to line items that received a credit application. The
192 amount of the credit will also be shown.
197 my $curuser = $FS::CurrentUser::CurrentUser;
199 die "access denied" unless $curuser->access_right('Financial reports');
201 my $conf = new FS::Conf;
202 my $money_char = $conf->config('money_char') || '$';
204 my @select = ( 'cust_bill_pkg.*', 'cust_bill._date' );
205 my @total = ( 'COUNT(*)', 'SUM(cust_bill_pkg.setup + cust_bill_pkg.recur)');
206 my @total_desc = ( $money_char.'%.2f total' ); # sprintf strings
208 my @peritem = ( 'setup', 'recur' );
209 my @peritem_desc = ( 'Setup charge', 'Recurring charge' );
211 my @currency_desc = ();
212 my @currency_sub = ();
214 if ( $conf->config('currencies') ) {
215 @currency_desc = ( 'Setup billed', 'Recurring billed' );
219 sub { my $currency = $_[0]->get($what.'_billed_currency') or return '';
220 $currency. ' '. currency_symbol($currency, SYM_HTML).
221 $_[0]->get($what.'_billed_amount');
225 @currency = ( 'setup_billed_amount', 'recur_billed_amount' ); #for sorting
228 my @pkgnum_header = ();
231 my $pkgnum_align = '';
232 if ( $curuser->option('show_pkgnum') ) {
233 push @select, 'cust_bill_pkg.pkgnum';
234 push @pkgnum_header, 'Pkg Num';
235 push @pkgnum, sub { $_[0]->pkgnum > 0 ? $_[0]->pkgnum : '' };
236 push @pkgnum_null, '';
237 $pkgnum_align .= 'r';
240 my @post_desc_header = ();
242 my @post_desc_null = ();
243 my $post_desc_align = '';
244 if ( $conf->exists('enable_taxclasses') ) {
245 push @post_desc_header, 'Tax class';
246 push @post_desc, 'taxclass';
247 push @post_desc_null, '';
248 $post_desc_align .= 'l';
249 push @select, 'part_pkg.taxclass'; # or should this use override?
252 # valid in both the tax and non-tax cases
254 " LEFT JOIN cust_bill ON (cust_bill_pkg.invnum = cust_bill.invnum)".
255 # use cust_pkg.locationnum if it exists
256 FS::UI::Web::join_cust_main('cust_bill', 'cust_pkg');
258 #agent virtualization
260 $FS::CurrentUser::CurrentUser->agentnums_sql( 'table' => 'cust_main' );
262 my @where = ( $agentnums_sql );
265 my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
267 if ( $cgi->param('distribute') == 1 ) {
268 push @where, "sdate <= $ending",
269 "edate > $beginning",
273 push @where, "cust_bill._date >= $beginning",
274 "cust_bill._date <= $ending";
278 if ( $cgi->param('status') =~ /^([a-z]+)$/ ) {
279 push @where, FS::cust_main->cust_status_sql . " = '$1'";
283 if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
284 push @where, "cust_main.agentnum = $1";
287 # salesnum--see below
289 if ( $cgi->param('refnum') =~ /^(\d+)$/ ) {
290 push @where, "cust_main.refnum = $1";
293 # 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)
294 if ( grep { $_ eq 'cust_classnum' } $cgi->param ) {
295 my @classnums = grep /^\d*$/, $cgi->param('cust_classnum');
296 push @where, 'COALESCE( cust_main.classnum, 0) IN ( '.
297 join(',', map { $_ || '0' } @classnums ).
304 if ( $cgi->param('custnum') =~ /^(\d+)$/ ) {
305 push @where, "cust_main.custnum = $1";
308 # we want the package and its definition if available
310 ' LEFT JOIN cust_pkg USING (pkgnum)
311 LEFT JOIN part_pkg USING (pkgpart)';
313 my $part_pkg = 'part_pkg';
314 # "Separate sub-packages from parents"
315 my $use_override = $cgi->param('use_override') ? 1 : 0;
316 if ( $use_override ) {
317 # still need the real part_pkg for tax applicability,
319 $join_pkg .= " LEFT JOIN part_pkg AS override ON (
320 COALESCE(cust_bill_pkg.pkgpart_override, cust_pkg.pkgpart, 0) = override.pkgpart
322 $part_pkg = 'override';
324 push @select, 'part_pkg.pkgpart', 'part_pkg.pkg'; # or should this use override?
327 if ( $cgi->param('nottax') ) {
329 push @where, 'cust_bill_pkg.pkgnum > 0';
331 my @tax_where; # will go into a subquery
332 my @exempt_where; # will also go into a subquery
334 # classnum (of override pkgpart if applicable)
335 # not specified: all classes
338 if ( grep { $_ eq 'classnum' } $cgi->param ) {
339 my @classnums = grep /^\d*$/, $cgi->param('classnum');
340 push @where, "COALESCE($part_pkg.classnum, 0) IN ( ".
341 join(',', @classnums ).
346 if ( grep { $_ eq 'report_optionnum' } $cgi->param ) {
347 my $num = join(',', grep /^[\d,]+$/, $cgi->param('report_optionnum'));
348 my $not_num = join(',', grep /^[\d,]+$/, $cgi->param('not_report_optionnum'));
349 my $all = $cgi->param('all_report_options') ? 1 : 0;
350 push @where, # code reuse FTW
351 FS::Report::Table->with_report_option(
352 report_optionnum => $num,
353 not_report_optionnum => $not_num,
354 use_override => $use_override,
355 all_report_options => $all,
360 if ( $cgi->param('taxclassNULL') ) {
361 # a little different from 'taxclass' in that it applies to the
362 # effective taxclass, not the real one
363 push @tax_where, 'cust_main_county.taxclass IS NULL'
364 } elsif ( $cgi->param('taxclass') ) {
365 push @tax_where, "$part_pkg.taxclass IN (" .
366 join(', ', map {dbh->quote($_)} $cgi->param('taxclass') ).
370 if ( $cgi->param('exempt_cust') eq 'Y' ) {
371 # tax-exempt customers
372 push @exempt_where, "(exempt_cust = 'Y' OR exempt_cust_taxname = 'Y')";
374 } elsif ( $cgi->param('exempt_pkg') eq 'Y' ) { # non-taxable package
375 # non-taxable package charges
376 push @exempt_where, "(exempt_setup = 'Y' OR exempt_recur = 'Y')";
378 # we don't handle exempt_monthly here
380 if ( $cgi->param('taxname') ) { # specific taxname
381 push @tax_where, "COALESCE(cust_main_county.taxname, 'Tax') = ".
382 dbh->quote($cgi->param('taxname'));
385 # country:state:county:city:district (may be repeated)
386 # You can also pass a big list of taxnums but that leads to huge URLs.
387 # Note that this means "packages whose tax is in this region", not
388 # "packages in this region". It's meant for links from the tax report.
389 if ( $cgi->param('region') ) {
391 foreach ( $cgi->param('region') ) {
393 @loc{qw(country state county city district)} =
394 split(':', $cgi->param('region'));
395 my $string = join(' AND ',
398 "$_ = ".dbh->quote($loc{$_});
404 push @orwhere, "($string)";
406 push @tax_where, '(' . join(' OR ', @orwhere) . ')' if @orwhere;
410 if ( $cgi->param('taxnum') =~ /^([\d,]+)$/) {
411 push @tax_where, "cust_main_county.taxnum IN ($1)";
414 # If we're showing exempt items, we need to find those with
415 # cust_tax_exempt_pkg records matching the selected taxes.
416 # If we're showing taxable items, we need to find those with
417 # cust_bill_pkg_tax_location records. We also need to find the
418 # exemption records so that we can show the taxable amount.
419 # If we're showing all items, we need the union of those.
420 # If we're showing 'out' (items that aren't region/class taxable),
421 # then we need the set of all items minus the union of those.
425 if ( @exempt_where or @tax_where
426 or $cgi->param('taxable') or $cgi->param('out') )
428 # process exemption restrictions, including @tax_where
429 my $exempt_sub = 'SELECT SUM(amount) as exempt_amount, billpkgnum
430 FROM cust_tax_exempt_pkg JOIN cust_main_county USING (taxnum)';
432 $exempt_sub .= ' WHERE '.join(' AND ', @tax_where, @exempt_where)
433 if (@tax_where or @exempt_where);
435 $exempt_sub .= ' GROUP BY billpkgnum';
437 $join_pkg .= " LEFT JOIN ($exempt_sub) AS item_exempt
441 # process tax restrictions
443 'cust_bill_pkg_tax_location.taxable_billpkgnum = cust_bill_pkg.billpkgnum',
444 'cust_main_county.tax > 0';
446 my $tax_sub = "SELECT 1
447 FROM cust_bill_pkg_tax_location
448 JOIN cust_bill_pkg AS tax_item USING (billpkgnum)
449 JOIN cust_main_county USING (taxnum)
450 WHERE ". join(' AND ', @tax_where);
452 # now do something with that
453 if ( @exempt_where ) {
455 push @where, 'item_exempt.billpkgnum IS NOT NULL';
456 push @select, 'item_exempt.exempt_amount';
457 push @peritem, 'exempt_amount';
458 push @peritem_desc, 'Exempt';
459 push @total, 'SUM(exempt_amount)';
460 push @total_desc, "$money_char%.2f tax-exempt";
462 } elsif ( $cgi->param('taxable') ) {
464 my $taxable = 'cust_bill_pkg.setup + cust_bill_pkg.recur '.
465 '- COALESCE(item_exempt.exempt_amount, 0)';
467 push @select, "($taxable) AS taxable_amount";
468 push @where, "EXISTS($tax_sub)";
469 push @peritem, 'taxable_amount';
470 push @peritem_desc, 'Taxable';
471 push @total, "SUM($taxable)";
472 push @total_desc, "$money_char%.2f taxable";
474 } elsif ( @tax_where ) {
476 # union of taxable + all exempt_ cases
477 push @where, "(EXISTS($tax_sub) OR item_exempt.billpkgnum IS NOT NULL)";
481 # recur/usage separation
482 if ( $cgi->param('usage') eq 'recurring' ) {
484 my $recur_no_usage = FS::cust_bill_pkg->charged_sql('', '', no_usage => 1);
485 push @select, "($recur_no_usage) AS recur_no_usage";
486 $peritem[1] = 'recur_no_usage';
487 $total[1] = "SUM(cust_bill_pkg.setup + $recur_no_usage)";
488 $total_desc[0] .= ' (excluding usage)';
490 } elsif ( $cgi->param('usage') eq 'usage' ) {
492 my $usage = FS::cust_bill_pkg->usage_sql();
493 push @select, "($usage) AS _usage";
494 # there's already a method named 'usage'
495 $peritem[1] = '_usage';
496 $peritem_desc[1] = 'Usage charge';
497 $total[1] = "SUM($usage)";
498 $total_desc[0] .= ' usage charges';
501 } elsif ( $cgi->param('istax') ) {
503 @peritem = ( 'setup' ); # taxes only have setup
504 @peritem_desc = ( 'Tax charge' );
506 push @where, 'cust_bill_pkg.pkgnum = 0';
508 # tax location when using tax_rate_location
509 if ( scalar( grep( /locationtaxid/, $cgi->param ) ) ) {
511 $join_pkg .= ' LEFT JOIN cust_bill_pkg_tax_rate_location USING ( billpkgnum ) '.
512 ' LEFT JOIN tax_rate_location USING ( taxratelocationnum )';
513 push @where, FS::tax_rate_location->location_sql(
514 map { $_ => (scalar($cgi->param($_)) || '') }
515 qw( district city county state locationtaxid )
519 COALESCE(cust_bill_pkg_tax_rate_location.amount,
520 cust_bill_pkg.setup + cust_bill_pkg.recur)
523 } elsif ( $cgi->param('out') ) {
526 LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum)
528 push @where, 'cust_bill_pkg_tax_location.billpkgnum IS NULL';
530 # each billpkgnum should appear only once
531 $total[0] = 'COUNT(*)';
532 $total[1] = 'SUM(cust_bill_pkg.setup)';
534 } else { # not locationtaxid or 'out'--the normal case
537 LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum)
538 JOIN cust_main_county USING (taxnum)
541 # don't double-count the components of consolidated taxes
542 $total[0] = 'COUNT(DISTINCT cust_bill_pkg.billpkgnum)';
543 $total[1] = 'SUM(cust_bill_pkg_tax_location.amount)';
546 if ( grep { $_ eq 'classnum' } $cgi->param ) {
547 my @classnums = grep /^\d*$/, $cgi->param('classnum');
549 JOIN cust_pkg AS taxed_pkg
550 ON (cust_bill_pkg_tax_location.pkgnum = taxed_pkg.pkgnum)
551 JOIN part_pkg AS taxed_part_pkg
552 ON (taxed_pkg.pkgpart = taxed_part_pkg.pkgpart)
554 push @where, "COALESCE(taxed_part_pkg.classnum, 0) IN ( ".
555 join(',', @classnums ).
562 if ( $cgi->param('taxclassNULL') ) {
563 push @where, 'cust_main_county.taxclass IS NULL';
567 if ( $cgi->param('taxnameNULL') ) {
568 push @where, 'cust_main_county.taxname IS NULL OR '.
569 'cust_main_county.taxname = \'Tax\'';
570 } elsif ( $cgi->param('taxname') ) {
571 push @where, 'cust_main_county.taxname = '.
572 dbh->quote($cgi->param('taxname'));
576 if ( $cgi->param('taxnum') =~ /^([\d,]+)$/) {
577 push @where, "cust_main_county.taxnum IN ($1)";
580 # itemdesc, for some reason
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_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');