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 if ( $cgi->param('use_override') ) { #"Separate sub-packages from parents"
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'; # or should this use override?
325 if ( $cgi->param('nottax') ) {
327 push @where, 'cust_bill_pkg.pkgnum > 0';
329 my @tax_where; # will go into a subquery
330 my @exempt_where; # will also go into a subquery
332 # classnum (of override pkgpart if applicable)
333 # not specified: all classes
336 if ( grep { $_ eq 'classnum' } $cgi->param ) {
337 my @classnums = grep /^\d*$/, $cgi->param('classnum');
338 push @where, "COALESCE($part_pkg.classnum, 0) IN ( ".
339 join(',', @classnums ).
344 if ( grep { $_ eq 'report_optionnum' } $cgi->param ) {
345 my @nums = grep /^\w+$/, $cgi->param('report_optionnum');
346 my $num = join(',', @nums);
347 push @where, # code reuse FTW
348 FS::Report::Table->with_report_option( $num, $cgi->param('use_override'));
351 if ( $cgi->param('report_optionnum') =~ /^(\w+)$/ ) {
356 if ( $cgi->param('taxclassNULL') ) {
357 # a little different from 'taxclass' in that it applies to the
358 # effective taxclass, not the real one
359 push @tax_where, 'cust_main_county.taxclass IS NULL'
360 } elsif ( $cgi->param('taxclass') ) {
361 push @tax_where, "$part_pkg.taxclass IN (" .
362 join(', ', map {dbh->quote($_)} $cgi->param('taxclass') ).
366 if ( $cgi->param('exempt_cust') eq 'Y' ) {
367 # tax-exempt customers
368 push @exempt_where, "(exempt_cust = 'Y' OR exempt_cust_taxname = 'Y')";
370 } elsif ( $cgi->param('exempt_pkg') eq 'Y' ) { # non-taxable package
371 # non-taxable package charges
372 push @exempt_where, "(exempt_setup = 'Y' OR exempt_recur = 'Y')";
374 # we don't handle exempt_monthly here
376 if ( $cgi->param('taxname') ) { # specific taxname
377 push @tax_where, "COALESCE(cust_main_county.taxname, 'Tax') = ".
378 dbh->quote($cgi->param('taxname'));
381 # country:state:county:city:district (may be repeated)
382 # You can also pass a big list of taxnums but that leads to huge URLs.
383 # Note that this means "packages whose tax is in this region", not
384 # "packages in this region". It's meant for links from the tax report.
385 if ( $cgi->param('region') ) {
387 foreach ( $cgi->param('region') ) {
389 @loc{qw(country state county city district)} =
390 split(':', $cgi->param('region'));
391 my $string = join(' AND ',
394 "$_ = ".dbh->quote($loc{$_});
400 push @orwhere, "($string)";
402 push @tax_where, '(' . join(' OR ', @orwhere) . ')' if @orwhere;
406 if ( $cgi->param('taxnum') =~ /^([\d,]+)$/) {
407 push @tax_where, "cust_main_county.taxnum IN ($1)";
410 # If we're showing exempt items, we need to find those with
411 # cust_tax_exempt_pkg records matching the selected taxes.
412 # If we're showing taxable items, we need to find those with
413 # cust_bill_pkg_tax_location records. We also need to find the
414 # exemption records so that we can show the taxable amount.
415 # If we're showing all items, we need the union of those.
416 # If we're showing 'out' (items that aren't region/class taxable),
417 # then we need the set of all items minus the union of those.
421 if ( @exempt_where or @tax_where
422 or $cgi->param('taxable') or $cgi->param('out') )
424 # process exemption restrictions, including @tax_where
425 my $exempt_sub = 'SELECT SUM(amount) as exempt_amount, billpkgnum
426 FROM cust_tax_exempt_pkg JOIN cust_main_county USING (taxnum)';
428 $exempt_sub .= ' WHERE '.join(' AND ', @tax_where, @exempt_where)
429 if (@tax_where or @exempt_where);
431 $exempt_sub .= ' GROUP BY billpkgnum';
433 $join_pkg .= " LEFT JOIN ($exempt_sub) AS item_exempt
437 # process tax restrictions
439 'cust_bill_pkg_tax_location.taxable_billpkgnum = cust_bill_pkg.billpkgnum',
440 'cust_main_county.tax > 0';
442 my $tax_sub = "SELECT 1
443 FROM cust_bill_pkg_tax_location
444 JOIN cust_bill_pkg AS tax_item USING (billpkgnum)
445 JOIN cust_main_county USING (taxnum)
446 WHERE ". join(' AND ', @tax_where);
448 # now do something with that
449 if ( @exempt_where ) {
451 push @where, 'item_exempt.billpkgnum IS NOT NULL';
452 push @select, 'item_exempt.exempt_amount';
453 push @peritem, 'exempt_amount';
454 push @peritem_desc, 'Exempt';
455 push @total, 'SUM(exempt_amount)';
456 push @total_desc, "$money_char%.2f tax-exempt";
458 } elsif ( $cgi->param('taxable') ) {
460 my $taxable = 'cust_bill_pkg.setup + cust_bill_pkg.recur '.
461 '- COALESCE(item_exempt.exempt_amount, 0)';
463 push @select, "($taxable) AS taxable_amount";
464 push @where, "EXISTS($tax_sub)";
465 push @peritem, 'taxable_amount';
466 push @peritem_desc, 'Taxable';
467 push @total, "SUM($taxable)";
468 push @total_desc, "$money_char%.2f taxable";
470 } elsif ( @tax_where ) {
472 # union of taxable + all exempt_ cases
473 push @where, "(EXISTS($tax_sub) OR item_exempt.billpkgnum IS NOT NULL)";
477 # recur/usage separation
478 if ( $cgi->param('usage') eq 'recurring' ) {
480 my $recur_no_usage = FS::cust_bill_pkg->charged_sql('', '', no_usage => 1);
481 push @select, "($recur_no_usage) AS recur_no_usage";
482 $peritem[1] = 'recur_no_usage';
483 $total[1] = "SUM(cust_bill_pkg.setup + $recur_no_usage)";
484 $total_desc[0] .= ' (excluding usage)';
486 } elsif ( $cgi->param('usage') eq 'usage' ) {
488 my $usage = FS::cust_bill_pkg->usage_sql();
489 push @select, "($usage) AS _usage";
490 # there's already a method named 'usage'
491 $peritem[1] = '_usage';
492 $peritem_desc[1] = 'Usage charge';
493 $total[1] = "SUM($usage)";
494 $total_desc[0] .= ' usage charges';
497 } elsif ( $cgi->param('istax') ) {
499 @peritem = ( 'setup' ); # taxes only have setup
500 @peritem_desc = ( 'Tax charge' );
502 push @where, 'cust_bill_pkg.pkgnum = 0';
504 # tax location when using tax_rate_location
505 if ( scalar( grep( /locationtaxid/, $cgi->param ) ) ) {
507 $join_pkg .= ' LEFT JOIN cust_bill_pkg_tax_rate_location USING ( billpkgnum ) '.
508 ' LEFT JOIN tax_rate_location USING ( taxratelocationnum )';
509 push @where, FS::tax_rate_location->location_sql(
510 map { $_ => (scalar($cgi->param($_)) || '') }
511 qw( district city county state locationtaxid )
515 COALESCE(cust_bill_pkg_tax_rate_location.amount,
516 cust_bill_pkg.setup + cust_bill_pkg.recur)
519 } elsif ( $cgi->param('out') ) {
522 LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum)
524 push @where, 'cust_bill_pkg_tax_location.billpkgnum IS NULL';
526 # each billpkgnum should appear only once
527 $total[0] = 'COUNT(*)';
528 $total[1] = 'SUM(cust_bill_pkg.setup)';
530 } else { # not locationtaxid or 'out'--the normal case
533 LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum)
534 JOIN cust_main_county USING (taxnum)
537 # don't double-count the components of consolidated taxes
538 $total[0] = 'COUNT(DISTINCT cust_bill_pkg.billpkgnum)';
539 $total[1] = 'SUM(cust_bill_pkg_tax_location.amount)';
542 if ( grep { $_ eq 'classnum' } $cgi->param ) {
543 my @classnums = grep /^\d*$/, $cgi->param('classnum');
545 JOIN cust_pkg AS taxed_pkg
546 ON (cust_bill_pkg_tax_location.pkgnum = taxed_pkg.pkgnum)
547 JOIN part_pkg AS taxed_part_pkg
548 ON (taxed_pkg.pkgpart = taxed_part_pkg.pkgpart)
550 push @where, "COALESCE(taxed_part_pkg.classnum, 0) IN ( ".
551 join(',', @classnums ).
558 if ( $cgi->param('taxclassNULL') ) {
559 push @where, 'cust_main_county.taxclass IS NULL';
563 if ( $cgi->param('taxnameNULL') ) {
564 push @where, 'cust_main_county.taxname IS NULL OR '.
565 'cust_main_county.taxname = \'Tax\'';
566 } elsif ( $cgi->param('taxname') ) {
567 push @where, 'cust_main_county.taxname = '.
568 dbh->quote($cgi->param('taxname'));
572 if ( $cgi->param('taxnum') =~ /^([\d,]+)$/) {
573 push @where, "cust_main_county.taxnum IN ($1)";
576 # itemdesc, for some reason
577 if ( $cgi->param('itemdesc') ) {
578 if ( $cgi->param('itemdesc') eq 'Tax' ) {
579 push @where, "(itemdesc='Tax' OR itemdesc is null)";
581 push @where, 'itemdesc='. dbh->quote($cgi->param('itemdesc'));
589 my $pay_sub = "SELECT SUM(cust_bill_pay_pkg.amount)
590 FROM cust_bill_pay_pkg
591 WHERE cust_bill_pkg.billpkgnum = cust_bill_pay_pkg.billpkgnum
593 push @select, "($pay_sub) AS pay_amount";
597 if ( $cgi->param('credit') ) {
601 if ( $cgi->param('istax') ) {
602 # then we need to group/join by billpkgtaxlocationnum, to get only the
603 # relevant part of partial taxes
604 my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
605 reason.reason as reason_text, access_user.username AS username_text,
606 billpkgtaxlocationnum, billpkgnum
607 FROM cust_credit_bill_pkg
608 JOIN cust_credit_bill USING (creditbillnum)
609 JOIN cust_credit USING (crednum)
610 LEFT JOIN reason USING (reasonnum)
611 LEFT JOIN access_user USING (usernum)
612 GROUP BY billpkgnum, billpkgtaxlocationnum, reason.reason,
613 access_user.username";
615 if ( $cgi->param('out') ) {
617 # find credits that are applied to the line items, but not to
618 # a cust_bill_pkg_tax_location link
619 $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
621 push @where, 'item_credit.billpkgtaxlocationnum IS NULL';
625 # find credits that are applied to the CBPTL links that are
626 # considered "interesting" by the report criteria
627 $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
628 USING (billpkgtaxlocationnum)";
633 # then only group by billpkgnum
634 my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
635 reason.reason as reason_text, access_user.username AS username_text,
637 FROM cust_credit_bill_pkg
638 JOIN cust_credit_bill USING (creditbillnum)
639 JOIN cust_credit USING (crednum)
640 LEFT JOIN reason USING (reasonnum)
641 LEFT JOIN access_user USING (usernum)
642 GROUP BY billpkgnum, reason.reason, access_user.username";
643 $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit USING (billpkgnum)";
646 push @where, 'item_credit.billpkgnum IS NOT NULL';
647 push @select, 'item_credit.credit_amount',
648 'item_credit.username_text',
649 'item_credit.reason_text';
650 push @peritem, 'credit_amount', 'username_text', 'reason_text';
651 push @peritem_desc, 'Credited', 'By', 'Reason';
652 push @total, 'SUM(credit_amount)';
653 push @total_desc, "$money_char%.2f credited";
657 #still want a credit total column
660 SELECT SUM(cust_credit_bill_pkg.amount)
661 FROM cust_credit_bill_pkg
662 WHERE cust_bill_pkg.billpkgnum = cust_credit_bill_pkg.billpkgnum
664 push @select, "($credit_sub) AS credit_amount";
668 push @select, 'cust_main.custnum', FS::UI::Web::cust_sql_fields();
671 if ( $cgi->param('salesnum') =~ /^(\d+)$/ ) {
674 my $sales = FS::sales->by_key($salesnum)
675 or die "salesnum $salesnum not found";
677 my $subsearch = $sales->cust_bill_pkg_search('', '',
678 'cust_main_sales' => ($cgi->param('cust_main_sales') ? 1 : 0),
679 'paid' => ($cgi->param('paid') ? 1 : 0),
680 'classnum' => scalar($cgi->param('classnum'))
682 $join_pkg .= " JOIN sales_pkg_class ON ( COALESCE(sales_pkg_class.classnum, 0) = COALESCE( part_pkg.classnum, 0) )";
684 my $extra_sql = $subsearch->{extra_sql};
685 $extra_sql =~ s/^WHERE//;
686 push @where, $extra_sql;
688 $cgi->param('classnum', 0) unless $cgi->param('classnum');
692 my $where = join(' AND ', @where);
693 $where &&= "WHERE $where";
696 'table' => 'cust_bill_pkg',
697 'addl_from' => "$join_pkg $join_cust",
699 'select' => join(",\n", @select ),
700 'extra_sql' => $where,
701 'order_by' => 'ORDER BY cust_bill._date, cust_bill_pkg.billpkgnum',
705 'SELECT ' . join(',', @total) .
706 " FROM cust_bill_pkg $join_pkg $join_cust
709 @peritem_desc = map {emt($_)} @peritem_desc;
710 my @peritem_sub = map {
712 if ($field =~ /_text$/) { # kludge for credit reason/username fields
713 sub {$_[0]->get($field)};
715 sub { sprintf($money_char.'%.2f', $_[0]->get($field)) }
718 my @peritem_null = map { '' } @peritem; # placeholders
719 my $peritem_align = 'r' x scalar(@peritem);
721 @currency_desc = map {emt($_)} @currency_desc;
722 my @currency_null = map { '' } @currency; # placeholders
723 my $currency_align = 'r' x scalar(@currency);
725 my $ilink = [ "${p}view/cust_bill.cgi?", 'invnum' ];
726 my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ];
728 my $pay_link = ''; #[, 'billpkgnum', ];
729 my $credit_link = [ "${p}search/cust_credit_bill_pkg.html?billpkgnum=", 'billpkgnum', ];
731 warn "\n\nQUERY:\n".Dumper($query)."\n\nCOUNT_QUERY:\n$count_query\n\n"
732 if $cgi->param('debug');