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,
66 ( map { $_ ne 'Cust. Status' ? $clink : '' }
67 FS::UI::Web::cust_header()
70 #'align' => 'rlrrrc'.FS::UI::Web::cust_aligns(),
71 'align' => $pkgnum_align.
77 FS::UI::Web::cust_aligns(),
89 FS::UI::Web::cust_colors(),
102 FS::UI::Web::cust_styles(),
107 Output control parameters:
108 - distribute: Boolean. If true, recurring fees will be "prorated" for the
109 portion of the package date range (sdate-edate) that falls within the date
110 range of the report. Line items will be limited to those for which this
111 portion is > 0. This disables filtering on invoice date.
113 - usage: Separate usage (cust_bill_pkg_detail records) from
114 recurring charges. If set to "usage", will show usage instead of
115 recurring charges. If set to "recurring", will deduct usage and only
116 show the flat rate charge. If not passed, the "recurring charge" column
117 will include usage charges also.
119 Filtering parameters:
120 - begin, end: Date range. Applies to invoice date, not necessarily package
121 date range. But see "distribute".
123 - status: Customer status (active, suspended, etc.). This will filter on
124 _current_ customer status, not status at the time the invoice was generated.
126 - agentnum: Filter on customer agent.
128 - refnum: Filter on customer reference source.
130 - cust_classnum: Filter on customer class.
132 - classnum: Filter on package class.
134 - report_optionnum: Filter on package report class. Can be a single report
135 class number or a comma-separated list (where 0 is "no report class"), or the
138 - use_override: Apply "classnum" and "taxclass" filtering based on the
139 override (bundle) pkgpart, rather than always using the true pkgpart.
141 - nottax: Limit to items that are not taxes (pkgnum > 0).
143 - istax: Limit to items that are taxes (pkgnum == 0).
145 - taxnum: Limit to items whose tax definition matches this taxnum.
146 With "nottax" that means items that are subject to that tax;
147 with "istax" it's the tax charges themselves. Can be specified
148 more than once to include multiple taxes.
150 - country, state, county, city: Limit to items whose tax location
151 matches these fields. If "nottax" it's the tax location of the package;
152 if "istax" the location of the tax.
154 - taxname, taxnameNULL: With "nottax", limit to items whose tax location
155 matches a tax with this name. With "istax", limit to items that have
156 this tax name. taxnameNULL is equivalent to "taxname = '' OR taxname
159 - out: With "nottax", limit to items that don't match any tax definition.
160 With "istax", find tax items that are unlinked to their tax definitions.
161 Current Freeside (> July 2012) always creates tax links, but unlinked
162 items may result from an incomplete upgrade of legacy data.
164 - locationtaxid: With "nottax", limit to packages matching this
165 tax_rate_location ID; with "tax", limit to taxes generated from that
168 - taxclass: Filter on package taxclass.
170 - taxclassNULL: With "nottax", limit to items that would be subject to the
171 tax with taxclass = NULL. This doesn't necessarily mean part_pkg.taxclass
172 is NULL; it also includes taxclasses that don't have a tax in this region.
174 - itemdesc: Limit to line items with this description. Note that non-tax
175 packages usually have a description of NULL. (Deprecated.)
177 - report_group: Can contain '=' or '!=' followed by a string to limit to
178 line items where itemdesc starts with, or doesn't start with, the string.
180 - cust_tax: Limit to customers who are tax-exempt. If "taxname" is also
181 specified, limit to customers who are also specifically exempt from that
184 - pkg_tax: Limit to packages that are tax-exempt, and only include the
185 exempt portion (setup, recurring, or both) when calculating totals.
187 - taxable: Limit to packages that are subject to tax, i.e. where a
188 cust_bill_pkg_tax_location record exists.
190 - credit: Limit to line items that received a credit application. The
191 amount of the credit will also be shown.
196 my $curuser = $FS::CurrentUser::CurrentUser;
198 die "access denied" unless $curuser->access_right('Financial reports');
200 my $conf = new FS::Conf;
201 my $money_char = $conf->config('money_char') || '$';
203 my @select = ( 'cust_bill_pkg.*', 'cust_bill._date' );
204 my @total = ( 'COUNT(*)', 'SUM(cust_bill_pkg.setup + cust_bill_pkg.recur)');
205 my @total_desc = ( $money_char.'%.2f total' ); # sprintf strings
207 my @peritem = ( 'setup', 'recur' );
208 my @peritem_desc = ( 'Setup charge', 'Recurring charge' );
210 my @currency_desc = ();
211 my @currency_sub = ();
213 if ( $conf->config('currencies') ) {
214 @currency_desc = ( 'Setup billed', 'Recurring billed' );
218 sub { my $currency = $_[0]->get($what.'_billed_currency') or return '';
219 $currency. ' '. currency_symbol($currency, SYM_HTML).
220 $_[0]->get($what.'_billed_amount');
224 @currency = ( 'setup_billed_amount', 'recur_billed_amount' ); #for sorting
227 my @pkgnum_header = ();
230 my $pkgnum_align = '';
231 if ( $curuser->option('show_pkgnum') ) {
232 push @select, 'cust_bill_pkg.pkgnum';
233 push @pkgnum_header, 'Pkg Num';
234 push @pkgnum, sub { $_[0]->pkgnum > 0 ? $_[0]->pkgnum : '' };
235 push @pkgnum_null, '';
236 $pkgnum_align .= 'r';
239 my @post_desc_header = ();
241 my @post_desc_null = ();
242 my $post_desc_align = '';
243 if ( $conf->exists('enable_taxclasses') ) {
244 push @post_desc_header, 'Tax class';
245 push @post_desc, 'taxclass';
246 push @post_desc_null, '';
247 $post_desc_align .= 'l';
248 push @select, 'part_pkg.taxclass'; # or should this use override?
251 # valid in both the tax and non-tax cases
253 " LEFT JOIN cust_bill ON (cust_bill_pkg.invnum = cust_bill.invnum)".
254 # use cust_pkg.locationnum if it exists
255 FS::UI::Web::join_cust_main('cust_bill', 'cust_pkg');
257 #agent virtualization
259 $FS::CurrentUser::CurrentUser->agentnums_sql( 'table' => 'cust_main' );
261 my @where = ( $agentnums_sql );
264 my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
266 if ( $cgi->param('distribute') == 1 ) {
267 push @where, "sdate <= $ending",
268 "edate > $beginning",
272 push @where, "cust_bill._date >= $beginning",
273 "cust_bill._date <= $ending";
277 if ( $cgi->param('status') =~ /^([a-z]+)$/ ) {
278 push @where, FS::cust_main->cust_status_sql . " = '$1'";
282 if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
283 push @where, "cust_main.agentnum = $1";
287 if ( $cgi->param('salesnum') =~ /^(\d+)$/ ) {
291 my $cmp_salesnum = $cgi->param('cust_main_sales')
292 ? ' COALESCE( cust_pkg.salesnum, cust_main.salesnum )'
293 : ' cust_pkg.salesnum ';
295 push @where, "$cmp_salesnum = $salesnum";
297 #because currently we're called from sales_pkg_class.html for a specific
298 # class (or empty class) but not for all classes
299 #will have to do something to distinguish if someone wants the sales report
300 # (report_cust_bill_pkg.html) to have a sales person dropdown
301 $cgi->param('classnum', 0) unless $cgi->param('classnum');
305 if ( $cgi->param('refnum') =~ /^(\d+)$/ ) {
306 push @where, "cust_main.refnum = $1";
309 # 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)
310 if ( grep { $_ eq 'cust_classnum' } $cgi->param ) {
311 my @classnums = grep /^\d*$/, $cgi->param('cust_classnum');
312 push @where, 'COALESCE( cust_main.classnum, 0) IN ( '.
313 join(',', map { $_ || '0' } @classnums ).
320 if ( $cgi->param('custnum') =~ /^(\d+)$/ ) {
321 push @where, "cust_main.custnum = $1";
324 # we want the package and its definition if available
326 ' LEFT JOIN cust_pkg USING (pkgnum)
327 LEFT JOIN part_pkg USING (pkgpart)';
329 my $part_pkg = 'part_pkg';
330 if ( $cgi->param('use_override') ) { #"Separate sub-packages from parents"
331 # still need the real part_pkg for tax applicability,
333 $join_pkg .= " LEFT JOIN part_pkg AS override ON (
334 COALESCE(cust_bill_pkg.pkgpart_override, cust_pkg.pkgpart, 0) = override.pkgpart
336 $part_pkg = 'override';
338 push @select, 'part_pkg.pkgpart', 'part_pkg.pkg'; # or should this use override?
341 if ( $cgi->param('nottax') ) {
343 push @where, 'cust_bill_pkg.pkgnum > 0';
345 my @tax_where; # will go into a subquery
346 my @exempt_where; # will also go into a subquery
348 # classnum (of override pkgpart if applicable)
349 # not specified: all classes
352 if ( grep { $_ eq 'classnum' } $cgi->param ) {
353 my @classnums = grep /^\d+$/, $cgi->param('classnum');
354 push @where, "COALESCE($part_pkg.classnum, 0) IN ( ".
355 join(',', @classnums ).
360 if ( grep { $_ eq 'report_optionnum' } $cgi->param ) {
361 my @nums = grep /^\w+$/, $cgi->param('report_optionnum');
362 my $num = join(',', @nums);
363 push @where, # code reuse FTW
364 FS::Report::Table->with_report_option( $num, $cgi->param('use_override'));
367 if ( $cgi->param('report_optionnum') =~ /^(\w+)$/ ) {
372 if ( $cgi->param('taxclassNULL') ) {
373 # a little different from 'taxclass' in that it applies to the
374 # effective taxclass, not the real one
375 push @tax_where, 'cust_main_county.taxclass IS NULL'
376 } elsif ( $cgi->param('taxclass') ) {
377 push @tax_where, "$part_pkg.taxclass IN (" .
378 join(', ', map {dbh->quote($_)} $cgi->param('taxclass') ).
382 if ( $cgi->param('exempt_cust') eq 'Y' ) {
383 # tax-exempt customers
384 push @exempt_where, "(exempt_cust = 'Y' OR exempt_cust_taxname = 'Y')";
386 } elsif ( $cgi->param('exempt_pkg') eq 'Y' ) { # non-taxable package
387 # non-taxable package charges
388 push @exempt_where, "(exempt_setup = 'Y' OR exempt_recur = 'Y')";
390 # we don't handle exempt_monthly here
392 if ( $cgi->param('taxname') ) { # specific taxname
393 push @tax_where, 'cust_main_county.taxname = '.
394 dbh->quote($cgi->param('taxname'));
395 } elsif ( $cgi->param('taxnameNULL') ) {
396 push @tax_where, 'cust_main_county.taxname IS NULL OR '.
397 'cust_main_county.taxname = \'Tax\'';
400 # country:state:county:city:district (may be repeated)
401 # You can also pass a big list of taxnums but that leads to huge URLs.
402 # Note that this means "packages whose tax is in this region", not
403 # "packages in this region". It's meant for links from the tax report.
404 if ( $cgi->param('region') ) {
406 foreach ( $cgi->param('region') ) {
408 @loc{qw(country state county city district)} =
409 split(':', $cgi->param('region'));
410 my $string = join(' AND ',
413 "$_ = ".dbh->quote($loc{$_});
419 push @orwhere, "($string)";
421 push @tax_where, '(' . join(' OR ', @orwhere) . ')' if @orwhere;
425 if ( $cgi->param('taxnum') ) {
426 my $taxnum_in = join(',',
427 grep /^\d+$/, $cgi->param('taxnum')
429 push @tax_where, "cust_main_county.taxnum IN ($taxnum_in)"
433 # If we're showing exempt items, we need to find those with
434 # cust_tax_exempt_pkg records matching the selected taxes.
435 # If we're showing taxable items, we need to find those with
436 # cust_bill_pkg_tax_location records. We also need to find the
437 # exemption records so that we can show the taxable amount.
438 # If we're showing all items, we need the union of those.
439 # If we're showing 'out' (items that aren't region/class taxable),
440 # then we need the set of all items minus the union of those.
444 if ( @exempt_where or @tax_where
445 or $cgi->param('taxable') or $cgi->param('out') )
447 # process exemption restrictions, including @tax_where
448 my $exempt_sub = 'SELECT SUM(amount) as exempt_amount, billpkgnum
449 FROM cust_tax_exempt_pkg JOIN cust_main_county USING (taxnum)';
451 $exempt_sub .= ' WHERE '.join(' AND ', @tax_where, @exempt_where)
452 if (@tax_where or @exempt_where);
454 $exempt_sub .= ' GROUP BY billpkgnum';
456 $join_pkg .= " LEFT JOIN ($exempt_sub) AS item_exempt
460 if ( @tax_where or $cgi->param('taxable') or $cgi->param('out') ) {
461 # process tax restrictions
463 'cust_main_county.tax > 0';
465 my $tax_sub = "SELECT invnum, cust_bill_pkg_tax_location.pkgnum
466 FROM cust_bill_pkg_tax_location
467 JOIN cust_bill_pkg AS tax_item USING (billpkgnum)
468 JOIN cust_main_county USING (taxnum)
469 WHERE ". join(' AND ', @tax_where).
470 " GROUP BY invnum, cust_bill_pkg_tax_location.pkgnum";
472 $join_pkg .= " LEFT JOIN ($tax_sub) AS item_tax
473 ON (item_tax.invnum = cust_bill_pkg.invnum AND
474 item_tax.pkgnum = cust_bill_pkg.pkgnum)";
477 # now do something with that
478 if ( @exempt_where ) {
480 push @where, 'item_exempt.billpkgnum IS NOT NULL';
481 push @select, 'item_exempt.exempt_amount';
482 push @peritem, 'exempt_amount';
483 push @peritem_desc, 'Exempt';
484 push @total, 'SUM(exempt_amount)';
485 push @total_desc, "$money_char%.2f tax-exempt";
487 } elsif ( $cgi->param('taxable') ) {
489 my $taxable = 'cust_bill_pkg.setup + cust_bill_pkg.recur '.
490 '- COALESCE(item_exempt.exempt_amount, 0)';
492 push @where, 'item_tax.invnum IS NOT NULL';
493 push @select, "($taxable) AS taxable_amount";
494 push @peritem, 'taxable_amount';
495 push @peritem_desc, 'Taxable';
496 push @total, "SUM($taxable)";
497 push @total_desc, "$money_char%.2f taxable";
499 } elsif ( $cgi->param('out') ) {
501 push @where, 'item_tax.invnum IS NULL',
502 'item_exempt.billpkgnum IS NULL';
504 } elsif ( @tax_where ) {
506 # union of taxable + all exempt_ cases
508 '(item_tax.invnum IS NOT NULL OR item_exempt.billpkgnum IS NOT NULL)';
512 # recur/usage separation
513 if ( $cgi->param('usage') eq 'recurring' ) {
515 my $recur_no_usage = FS::cust_bill_pkg->charged_sql('', '', no_usage => 1);
516 push @select, "($recur_no_usage) AS recur_no_usage";
517 $peritem[1] = 'recur_no_usage';
518 $total[1] = "SUM(cust_bill_pkg.setup + $recur_no_usage)";
519 $total_desc[0] .= ' (excluding usage)';
521 } elsif ( $cgi->param('usage') eq 'usage' ) {
523 my $usage = FS::cust_bill_pkg->usage_sql();
524 push @select, "($usage) AS _usage";
525 # there's already a method named 'usage'
526 $peritem[1] = '_usage';
527 $peritem_desc[1] = 'Usage charge';
528 $total[1] = "SUM($usage)";
529 $total_desc[0] .= ' usage charges';
532 } elsif ( $cgi->param('istax') ) {
534 @peritem = ( 'setup' ); # taxes only have setup
535 @peritem_desc = ( 'Tax charge' );
537 push @where, 'cust_bill_pkg.pkgnum = 0';
539 # tax location when using tax_rate_location
540 if ( scalar( grep( /locationtaxid/, $cgi->param ) ) ) {
542 $join_pkg .= ' LEFT JOIN cust_bill_pkg_tax_rate_location USING ( billpkgnum ) '.
543 ' LEFT JOIN tax_rate_location USING ( taxratelocationnum )';
544 push @where, FS::tax_rate_location->location_sql(
545 map { $_ => (scalar($cgi->param($_)) || '') }
546 qw( district city county state locationtaxid )
550 COALESCE(cust_bill_pkg_tax_rate_location.amount,
551 cust_bill_pkg.setup + cust_bill_pkg.recur)
554 } elsif ( $cgi->param('out') ) {
557 LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum)
559 push @where, 'cust_bill_pkg_tax_location.billpkgnum IS NULL';
561 # each billpkgnum should appear only once
562 $total[0] = 'COUNT(*)';
563 $total[1] = 'SUM(cust_bill_pkg.setup)';
565 } else { # not locationtaxid or 'out'--the normal case
568 LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum)
569 JOIN cust_main_county USING (taxnum)
572 # don't double-count the components of consolidated taxes
573 $total[0] = 'COUNT(DISTINCT cust_bill_pkg.billpkgnum)';
574 $total[1] = 'SUM(cust_bill_pkg_tax_location.amount)';
578 if ( $cgi->param('taxclassNULL') ) {
579 push @where, 'cust_main_county.taxclass IS NULL';
583 if ( $cgi->param('taxnameNULL') ) {
584 push @where, 'cust_main_county.taxname IS NULL OR '.
585 'cust_main_county.taxname = \'Tax\'';
586 } elsif ( $cgi->param('taxname') ) {
587 push @where, 'cust_main_county.taxname = '.
588 dbh->quote($cgi->param('taxname'));
592 if ( $cgi->param('taxnum') ) {
593 my $taxnum_in = join(',',
594 grep /^\d+$/, $cgi->param('taxnum')
596 push @where, "cust_main_county.taxnum IN ($taxnum_in)"
600 # report group (itemdesc)
601 if ( $cgi->param('report_group') =~ /^(=|!=) (.*)$/ ) {
602 my ( $group_op, $group_value ) = ( $1, $2 );
603 if ( $group_op eq '=' ) {
604 #push @where, 'itemdesc LIKE '. dbh->quote($group_value.'%');
605 push @where, 'itemdesc = '. dbh->quote($group_value);
606 } elsif ( $group_op eq '!=' ) {
607 push @where, '( itemdesc != '. dbh->quote($group_value) .' OR itemdesc IS NULL )';
609 die "guru meditation #00de: group_op $group_op\n";
613 # itemdesc, for some reason
614 if ( $cgi->param('itemdesc') ) {
615 if ( $cgi->param('itemdesc') eq 'Tax' ) {
616 push @where, "(itemdesc='Tax' OR itemdesc is null)";
618 push @where, 'itemdesc='. dbh->quote($cgi->param('itemdesc'));
626 my $pay_sub = "SELECT SUM(cust_bill_pay_pkg.amount)
627 FROM cust_bill_pay_pkg
628 WHERE cust_bill_pkg.billpkgnum = cust_bill_pay_pkg.billpkgnum
630 push @select, "($pay_sub) AS pay_amount";
634 if ( $cgi->param('credit') ) {
638 if ( $cgi->param('istax') ) {
639 # then we need to group/join by billpkgtaxlocationnum, to get only the
640 # relevant part of partial taxes
641 my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
642 reason.reason as reason_text, access_user.username AS username_text,
643 billpkgtaxlocationnum, billpkgnum
644 FROM cust_credit_bill_pkg
645 JOIN cust_credit_bill USING (creditbillnum)
646 JOIN cust_credit USING (crednum)
647 LEFT JOIN reason USING (reasonnum)
648 LEFT JOIN access_user USING (usernum)
649 GROUP BY billpkgnum, billpkgtaxlocationnum, reason.reason,
650 access_user.username";
652 if ( $cgi->param('out') ) {
654 # find credits that are applied to the line items, but not to
655 # a cust_bill_pkg_tax_location link
656 $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
658 push @where, 'item_credit.billpkgtaxlocationnum IS NULL';
662 # find credits that are applied to the CBPTL links that are
663 # considered "interesting" by the report criteria
664 $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
665 USING (billpkgtaxlocationnum)";
670 # then only group by billpkgnum
671 my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
672 reason.reason as reason_text, access_user.username AS username_text,
674 FROM cust_credit_bill_pkg
675 JOIN cust_credit_bill USING (creditbillnum)
676 JOIN cust_credit USING (crednum)
677 LEFT JOIN reason USING (reasonnum)
678 LEFT JOIN access_user USING (usernum)
679 GROUP BY billpkgnum, reason.reason, access_user.username";
680 $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit USING (billpkgnum)";
683 push @where, 'item_credit.billpkgnum IS NOT NULL';
684 push @select, 'item_credit.credit_amount',
685 'item_credit.username_text',
686 'item_credit.reason_text';
687 push @peritem, 'credit_amount', 'username_text', 'reason_text';
688 push @peritem_desc, 'Credited', 'By', 'Reason';
689 push @total, 'SUM(credit_amount)';
690 push @total_desc, "$money_char%.2f credited";
694 #still want a credit total column
697 SELECT SUM(cust_credit_bill_pkg.amount)
698 FROM cust_credit_bill_pkg
699 WHERE cust_bill_pkg.billpkgnum = cust_credit_bill_pkg.billpkgnum
701 push @select, "($credit_sub) AS credit_amount";
705 push @select, 'cust_main.custnum', FS::UI::Web::cust_sql_fields();
707 my $where = join(' AND ', @where);
708 $where &&= "WHERE $where";
711 'table' => 'cust_bill_pkg',
712 'addl_from' => "$join_pkg $join_cust",
714 'select' => join(",\n", @select ),
715 'extra_sql' => $where,
716 'order_by' => 'ORDER BY cust_bill._date, cust_bill_pkg.billpkgnum',
720 'SELECT ' . join(',', @total) .
721 " FROM cust_bill_pkg $join_pkg $join_cust
724 @peritem_desc = map {emt($_)} @peritem_desc;
725 my @peritem_sub = map {
727 if ($field =~ /_text$/) { # kludge for credit reason/username fields
728 sub {$_[0]->get($field)};
730 sub { sprintf($money_char.'%.2f', $_[0]->get($field)) }
733 my @peritem_null = map { '' } @peritem; # placeholders
734 my $peritem_align = 'r' x scalar(@peritem);
736 @currency_desc = map {emt($_)} @currency_desc;
737 my @currency_null = map { '' } @currency; # placeholders
738 my $currency_align = 'r' x scalar(@currency);
740 my $ilink = [ "${p}view/cust_bill.cgi?", 'invnum' ];
741 my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ];
743 my $pay_link = ''; #[, 'billpkgnum', ];
744 my $credit_link = [ "${p}search/cust_credit_bill_pkg.html?billpkgnum=", 'billpkgnum', ];
746 warn "\n\nQUERY:\n".Dumper($query)."\n\nCOUNT_QUERY:\n$count_query\n\n"
747 if $cgi->param('debug');