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 a comma-separated
148 list 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";
286 # salesnum--see below
288 if ( $cgi->param('refnum') =~ /^(\d+)$/ ) {
289 push @where, "cust_main.refnum = $1";
292 # 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)
293 if ( grep { $_ eq 'cust_classnum' } $cgi->param ) {
294 my @classnums = grep /^\d*$/, $cgi->param('cust_classnum');
295 push @where, 'COALESCE( cust_main.classnum, 0) IN ( '.
296 join(',', map { $_ || '0' } @classnums ).
303 if ( $cgi->param('custnum') =~ /^(\d+)$/ ) {
304 push @where, "cust_main.custnum = $1";
307 # we want the package and its definition if available
309 ' LEFT JOIN cust_pkg USING (pkgnum)
310 LEFT JOIN part_pkg USING (pkgpart)';
312 my $part_pkg = 'part_pkg';
313 if ( $cgi->param('use_override') ) { #"Separate sub-packages from parents"
314 # still need the real part_pkg for tax applicability,
316 $join_pkg .= " LEFT JOIN part_pkg AS override ON (
317 COALESCE(cust_bill_pkg.pkgpart_override, cust_pkg.pkgpart, 0) = override.pkgpart
319 $part_pkg = 'override';
321 push @select, 'part_pkg.pkgpart', 'part_pkg.pkg'; # or should this use override?
324 if ( $cgi->param('nottax') ) {
326 push @where, 'cust_bill_pkg.pkgnum > 0';
328 my @tax_where; # will go into a subquery
329 my @exempt_where; # will also go into a subquery
331 # classnum (of override pkgpart if applicable)
332 # not specified: all classes
335 if ( grep { $_ eq 'classnum' } $cgi->param ) {
336 my @classnums = grep /^\d*$/, $cgi->param('classnum');
337 push @where, "COALESCE($part_pkg.classnum, 0) IN ( ".
338 join(',', @classnums ).
343 if ( grep { $_ eq 'report_optionnum' } $cgi->param ) {
344 my @nums = grep /^\w+$/, $cgi->param('report_optionnum');
345 my $num = join(',', @nums);
346 push @where, # code reuse FTW
347 FS::Report::Table->with_report_option( $num, $cgi->param('use_override'));
350 if ( $cgi->param('report_optionnum') =~ /^(\w+)$/ ) {
355 if ( $cgi->param('taxclassNULL') ) {
356 # a little different from 'taxclass' in that it applies to the
357 # effective taxclass, not the real one
358 push @tax_where, 'cust_main_county.taxclass IS NULL'
359 } elsif ( $cgi->param('taxclass') ) {
360 push @tax_where, "$part_pkg.taxclass IN (" .
361 join(', ', map {dbh->quote($_)} $cgi->param('taxclass') ).
365 if ( $cgi->param('exempt_cust') eq 'Y' ) {
366 # tax-exempt customers
367 push @exempt_where, "(exempt_cust = 'Y' OR exempt_cust_taxname = 'Y')";
369 } elsif ( $cgi->param('exempt_pkg') eq 'Y' ) { # non-taxable package
370 # non-taxable package charges
371 push @exempt_where, "(exempt_setup = 'Y' OR exempt_recur = 'Y')";
373 # we don't handle exempt_monthly here
375 if ( $cgi->param('taxname') ) { # specific taxname
376 push @tax_where, "COALESCE(cust_main_county.taxname, 'Tax') = ".
377 dbh->quote($cgi->param('taxname'));
380 # country:state:county:city:district (may be repeated)
381 # You can also pass a big list of taxnums but that leads to huge URLs.
382 # Note that this means "packages whose tax is in this region", not
383 # "packages in this region". It's meant for links from the tax report.
384 if ( $cgi->param('region') ) {
386 foreach ( $cgi->param('region') ) {
388 @loc{qw(country state county city district)} =
389 split(':', $cgi->param('region'));
390 my $string = join(' AND ',
393 "$_ = ".dbh->quote($loc{$_});
399 push @orwhere, "($string)";
401 push @tax_where, '(' . join(' OR ', @orwhere) . ')' if @orwhere;
405 if ( $cgi->param('taxnum') =~ /^([\d,]+)$/) {
406 push @tax_where, "cust_main_county.taxnum IN ($1)";
409 # If we're showing exempt items, we need to find those with
410 # cust_tax_exempt_pkg records matching the selected taxes.
411 # If we're showing taxable items, we need to find those with
412 # cust_bill_pkg_tax_location records. We also need to find the
413 # exemption records so that we can show the taxable amount.
414 # If we're showing all items, we need the union of those.
415 # If we're showing 'out' (items that aren't region/class taxable),
416 # then we need the set of all items minus the union of those.
420 if ( @exempt_where or @tax_where
421 or $cgi->param('taxable') or $cgi->param('out') )
423 # process exemption restrictions, including @tax_where
424 my $exempt_sub = 'SELECT SUM(amount) as exempt_amount, billpkgnum
425 FROM cust_tax_exempt_pkg JOIN cust_main_county USING (taxnum)';
427 $exempt_sub .= ' WHERE '.join(' AND ', @tax_where, @exempt_where)
428 if (@tax_where or @exempt_where);
430 $exempt_sub .= ' GROUP BY billpkgnum';
432 $join_pkg .= " LEFT JOIN ($exempt_sub) AS item_exempt
436 # process tax restrictions
438 'cust_bill_pkg_tax_location.taxable_billpkgnum = cust_bill_pkg.billpkgnum',
439 'cust_main_county.tax > 0';
441 my $tax_sub = "SELECT 1
442 FROM cust_bill_pkg_tax_location
443 JOIN cust_bill_pkg AS tax_item USING (billpkgnum)
444 JOIN cust_main_county USING (taxnum)
445 WHERE ". join(' AND ', @tax_where);
447 # now do something with that
448 if ( @exempt_where ) {
450 push @where, 'item_exempt.billpkgnum IS NOT NULL';
451 push @select, 'item_exempt.exempt_amount';
452 push @peritem, 'exempt_amount';
453 push @peritem_desc, 'Exempt';
454 push @total, 'SUM(exempt_amount)';
455 push @total_desc, "$money_char%.2f tax-exempt";
457 } elsif ( $cgi->param('taxable') ) {
459 my $taxable = 'cust_bill_pkg.setup + cust_bill_pkg.recur '.
460 '- COALESCE(item_exempt.exempt_amount, 0)';
462 push @select, "($taxable) AS taxable_amount";
463 push @where, "EXISTS($tax_sub)";
464 push @peritem, 'taxable_amount';
465 push @peritem_desc, 'Taxable';
466 push @total, "SUM($taxable)";
467 push @total_desc, "$money_char%.2f taxable";
469 } elsif ( @tax_where ) {
471 # union of taxable + all exempt_ cases
472 push @where, "(EXISTS($tax_sub) OR item_exempt.billpkgnum IS NOT NULL)";
476 # recur/usage separation
477 if ( $cgi->param('usage') eq 'recurring' ) {
479 my $recur_no_usage = FS::cust_bill_pkg->charged_sql('', '', no_usage => 1);
480 push @select, "($recur_no_usage) AS recur_no_usage";
481 $peritem[1] = 'recur_no_usage';
482 $total[1] = "SUM(cust_bill_pkg.setup + $recur_no_usage)";
483 $total_desc[0] .= ' (excluding usage)';
485 } elsif ( $cgi->param('usage') eq 'usage' ) {
487 my $usage = FS::cust_bill_pkg->usage_sql();
488 push @select, "($usage) AS _usage";
489 # there's already a method named 'usage'
490 $peritem[1] = '_usage';
491 $peritem_desc[1] = 'Usage charge';
492 $total[1] = "SUM($usage)";
493 $total_desc[0] .= ' usage charges';
496 } elsif ( $cgi->param('istax') ) {
498 @peritem = ( 'setup' ); # taxes only have setup
499 @peritem_desc = ( 'Tax charge' );
501 push @where, 'cust_bill_pkg.pkgnum = 0';
503 # tax location when using tax_rate_location
504 if ( scalar( grep( /locationtaxid/, $cgi->param ) ) ) {
506 $join_pkg .= ' LEFT JOIN cust_bill_pkg_tax_rate_location USING ( billpkgnum ) '.
507 ' LEFT JOIN tax_rate_location USING ( taxratelocationnum )';
508 push @where, FS::tax_rate_location->location_sql(
509 map { $_ => (scalar($cgi->param($_)) || '') }
510 qw( district city county state locationtaxid )
514 COALESCE(cust_bill_pkg_tax_rate_location.amount,
515 cust_bill_pkg.setup + cust_bill_pkg.recur)
518 } elsif ( $cgi->param('out') ) {
521 LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum)
523 push @where, 'cust_bill_pkg_tax_location.billpkgnum IS NULL';
525 # each billpkgnum should appear only once
526 $total[0] = 'COUNT(*)';
527 $total[1] = 'SUM(cust_bill_pkg.setup)';
529 } else { # not locationtaxid or 'out'--the normal case
532 LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum)
533 JOIN cust_main_county USING (taxnum)
536 # don't double-count the components of consolidated taxes
537 $total[0] = 'COUNT(DISTINCT cust_bill_pkg.billpkgnum)';
538 $total[1] = 'SUM(cust_bill_pkg_tax_location.amount)';
541 if ( grep { $_ eq 'classnum' } $cgi->param ) {
542 my @classnums = grep /^\d*$/, $cgi->param('classnum');
544 JOIN cust_pkg AS taxed_pkg
545 ON (cust_bill_pkg_tax_location.pkgnum = taxed_pkg.pkgnum)
546 JOIN part_pkg AS taxed_part_pkg
547 ON (taxed_pkg.pkgpart = taxed_part_pkg.pkgpart)
549 push @where, "COALESCE(taxed_part_pkg.classnum, 0) IN ( ".
550 join(',', @classnums ).
557 if ( $cgi->param('taxclassNULL') ) {
558 push @where, 'cust_main_county.taxclass IS NULL';
562 if ( $cgi->param('taxnameNULL') ) {
563 push @where, 'cust_main_county.taxname IS NULL OR '.
564 'cust_main_county.taxname = \'Tax\'';
565 } elsif ( $cgi->param('taxname') ) {
566 push @where, 'cust_main_county.taxname = '.
567 dbh->quote($cgi->param('taxname'));
571 if ( $cgi->param('taxnum') =~ /^([\d,]+)$/) {
572 push @where, "cust_main_county.taxnum IN ($1)";
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 @currency_desc = map {emt($_)} @currency_desc;
721 my @currency_null = map { '' } @currency; # placeholders
722 my $currency_align = 'r' x scalar(@currency);
724 my $ilink = [ "${p}view/cust_bill.cgi?", 'invnum' ];
725 my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ];
727 my $pay_link = ''; #[, 'billpkgnum', ];
728 my $credit_link = [ "${p}search/cust_credit_bill_pkg.html?billpkgnum=", 'billpkgnum', ];
730 warn "\n\nQUERY:\n".Dumper($query)."\n\nCOUNT_QUERY:\n$count_query\n\n"
731 if $cgi->param('debug');