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 ? $_[0]->get('pkgpart')
26 sub { $_[0]->pkgnum > 0
28 : $_[0]->get('itemdesc')
31 #strikethrough or "N/A ($amount)" or something these when
32 # they're not applicable to pkg_tax search
36 sub { time2str('%b %d %Y', shift->_date ) },
37 sub { sprintf($money_char.'%.2f', shift->get('pay_amount')) },
38 sub { sprintf($money_char.'%.2f', shift->get('credit_amount')) },
39 \&FS::UI::Web::cust_fields,
52 FS::UI::Web::cust_sort_fields(),
65 ( map { $_ ne 'Cust. Status' ? $clink : '' }
66 FS::UI::Web::cust_header()
69 #'align' => 'rlrrrc'.FS::UI::Web::cust_aligns(),
70 'align' => $pkgnum_align.
76 FS::UI::Web::cust_aligns(),
88 FS::UI::Web::cust_colors(),
101 FS::UI::Web::cust_styles(),
106 Output control parameters:
107 - distribute: Boolean. If true, recurring fees will be "prorated" for the
108 portion of the package date range (sdate-edate) that falls within the date
109 range of the report. Line items will be limited to those for which this
110 portion is > 0. This disables filtering on invoice date.
112 - usage: Separate usage (cust_bill_pkg_detail records) from
113 recurring charges. If set to "usage", will show usage instead of
114 recurring charges. If set to "recurring", will deduct usage and only
115 show the flat rate charge. If not passed, the "recurring charge" column
116 will include usage charges also.
118 Filtering parameters:
119 - begin, end: Date range. Applies to invoice date, not necessarily package
120 date range. But see "distribute".
122 - status: Customer status (active, suspended, etc.). This will filter on
123 _current_ customer status, not status at the time the invoice was generated.
125 - agentnum: Filter on customer agent.
127 - refnum: Filter on customer reference source.
129 - cust_classnum: Filter on customer class.
131 - classnum: Filter on package class.
133 - report_optionnum: Filter on package report class. Can be a single report
134 class number or a comma-separated list (where 0 is "no report class"), or the
137 - use_override: Apply "classnum" and "taxclass" filtering based on the
138 override (bundle) pkgpart, rather than always using the true pkgpart.
140 - nottax: Limit to items that are not taxes (pkgnum > 0).
142 - istax: Limit to items that are taxes (pkgnum == 0).
144 - taxnum: Limit to items whose tax definition matches this taxnum.
145 With "nottax" that means items that are subject to that tax;
146 with "istax" it's the tax charges themselves. Can be a comma-separated
147 list to include multiple taxes.
149 - country, state, county, city: Limit to items whose tax location
150 matches these fields. If "nottax" it's the tax location of the package;
151 if "istax" the location of the tax.
153 - taxname, taxnameNULL: With "nottax", limit to items whose tax location
154 matches a tax with this name. With "istax", limit to items that have
155 this tax name. taxnameNULL is equivalent to "taxname = '' OR taxname
158 - out: With "nottax", limit to items that don't match any tax definition.
159 With "istax", find tax items that are unlinked to their tax definitions.
160 Current Freeside (> July 2012) always creates tax links, but unlinked
161 items may result from an incomplete upgrade of legacy data.
163 - locationtaxid: With "nottax", limit to packages matching this
164 tax_rate_location ID; with "tax", limit to taxes generated from that
167 - taxclass: Filter on package taxclass.
169 - taxclassNULL: With "nottax", limit to items that would be subject to the
170 tax with taxclass = NULL. This doesn't necessarily mean part_pkg.taxclass
171 is NULL; it also includes taxclasses that don't have a tax in this region.
173 - itemdesc: Limit to line items with this description. Note that non-tax
174 packages usually have a description of NULL. (Deprecated.)
176 - report_group: Can contain '=' or '!=' followed by a string to limit to
177 line items where itemdesc starts with, or doesn't start with, the string.
179 - cust_tax: Limit to customers who are tax-exempt. If "taxname" is also
180 specified, limit to customers who are also specifically exempt from that
183 - pkg_tax: Limit to packages that are tax-exempt, and only include the
184 exempt portion (setup, recurring, or both) when calculating totals.
186 - taxable: Limit to packages that are subject to tax, i.e. where a
187 cust_bill_pkg_tax_location record exists.
189 - credit: Limit to line items that received a credit application. The
190 amount of the credit will also be shown.
195 my $curuser = $FS::CurrentUser::CurrentUser;
197 die "access denied" unless $curuser->access_right('Financial reports');
199 my $conf = new FS::Conf;
200 my $money_char = $conf->config('money_char') || '$';
202 my @select = ( 'cust_bill_pkg.*', 'cust_bill._date' );
203 my @total = ( 'COUNT(*)', 'SUM(cust_bill_pkg.setup + cust_bill_pkg.recur)');
204 my @total_desc = ( $money_char.'%.2f total' ); # sprintf strings
206 my @peritem = ( 'setup', 'recur' );
207 my @peritem_desc = ( 'Setup charge', 'Recurring charge' );
209 my @currency_desc = ();
210 my @currency_sub = ();
212 if ( $conf->config('currencies') ) {
213 @currency_desc = ( 'Setup billed', 'Recurring billed' );
217 sub { my $currency = $_[0]->get($what.'_billed_currency') or return '';
218 $currency. ' '. currency_symbol($currency, SYM_HTML).
219 $_[0]->get($what.'_billed_amount');
223 @currency = ( 'setup_billed_amount', 'recur_billed_amount' ); #for sorting
226 my @pkgnum_header = ();
229 my $pkgnum_align = '';
230 if ( $curuser->option('show_pkgnum') ) {
231 push @select, 'cust_bill_pkg.pkgnum';
232 push @pkgnum_header, 'Pkg Num';
233 push @pkgnum, sub { $_[0]->pkgnum > 0 ? $_[0]->pkgnum : '' };
234 push @pkgnum_null, '';
235 $pkgnum_align .= 'r';
238 my @post_desc_header = ();
240 my @post_desc_null = ();
241 my $post_desc_align = '';
242 if ( $conf->exists('enable_taxclasses') ) {
243 push @post_desc_header, 'Tax class';
244 push @post_desc, 'taxclass';
245 push @post_desc_null, '';
246 $post_desc_align .= 'l';
249 # valid in both the tax and non-tax cases
251 " LEFT JOIN cust_bill ON (cust_bill_pkg.invnum = cust_bill.invnum)".
252 # use cust_pkg.locationnum if it exists
253 FS::UI::Web::join_cust_main('cust_bill', 'cust_pkg');
255 #agent virtualization
257 $FS::CurrentUser::CurrentUser->agentnums_sql( 'table' => 'cust_main' );
259 my @where = ( $agentnums_sql );
262 my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
264 if ( $cgi->param('distribute') == 1 ) {
265 push @where, "sdate <= $ending",
266 "edate > $beginning",
270 push @where, "cust_bill._date >= $beginning",
271 "cust_bill._date <= $ending";
275 if ( $cgi->param('status') =~ /^([a-z]+)$/ ) {
276 push @where, FS::cust_main->cust_status_sql . " = '$1'";
280 if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
281 push @where, "cust_main.agentnum = $1";
284 # salesnum--see below
286 if ( $cgi->param('refnum') =~ /^(\d+)$/ ) {
287 push @where, "cust_main.refnum = $1";
290 # 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)
291 if ( grep { $_ eq 'cust_classnum' } $cgi->param ) {
292 my @classnums = grep /^\d*$/, $cgi->param('cust_classnum');
293 push @where, 'COALESCE( cust_main.classnum, 0) IN ( '.
294 join(',', map { $_ || '0' } @classnums ).
301 if ( $cgi->param('custnum') =~ /^(\d+)$/ ) {
302 push @where, "cust_main.custnum = $1";
305 # we want the package and its definition if available
307 ' LEFT JOIN cust_pkg USING (pkgnum)
308 LEFT JOIN part_pkg USING (pkgpart)';
310 my $part_pkg = 'part_pkg';
311 # "Separate sub-packages from parents"
312 my $use_override = $cgi->param('use_override') ? 1 : 0;
313 if ( $use_override ) {
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";
322 push @select, "$part_pkg.taxclass" if $conf->exists('enable_taxclasses');
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 $num = join(',', grep /^[\d,]+$/, $cgi->param('report_optionnum'));
346 my $not_num = join(',', grep /^[\d,]+$/, $cgi->param('not_report_optionnum'));
347 my $all = $cgi->param('all_report_options') ? 1 : 0;
348 push @where, # code reuse FTW
349 FS::Report::Table->with_report_option(
350 report_optionnum => $num,
351 not_report_optionnum => $not_num,
352 use_override => $use_override,
353 all_report_options => $all,
358 if ( $cgi->param('taxclassNULL') ) {
359 # a little different from 'taxclass' in that it applies to the
360 # effective taxclass, not the real one
361 push @tax_where, 'cust_main_county.taxclass IS NULL'
362 } elsif ( $cgi->param('taxclass') ) {
363 push @tax_where, "$part_pkg.taxclass IN (" .
364 join(', ', map {dbh->quote($_)} $cgi->param('taxclass') ).
368 if ( $cgi->param('exempt_cust') eq 'Y' ) {
369 # tax-exempt customers
370 push @exempt_where, "(exempt_cust = 'Y' OR exempt_cust_taxname = 'Y')";
372 } elsif ( $cgi->param('exempt_pkg') eq 'Y' ) { # non-taxable package
373 # non-taxable package charges
374 push @exempt_where, "(exempt_setup = 'Y' OR exempt_recur = 'Y')";
376 # we don't handle exempt_monthly here
378 if ( $cgi->param('taxname') ) { # specific taxname
379 push @tax_where, "COALESCE(cust_main_county.taxname, 'Tax') = ".
380 dbh->quote($cgi->param('taxname'));
383 # country:state:county:city:district (may be repeated)
384 # You can also pass a big list of taxnums but that leads to huge URLs.
385 # Note that this means "packages whose tax is in this region", not
386 # "packages in this region". It's meant for links from the tax report.
387 if ( $cgi->param('region') ) {
389 foreach ( $cgi->param('region') ) {
391 @loc{qw(country state county city district)} =
392 split(':', $cgi->param('region'));
393 my $string = join(' AND ',
396 "$_ = ".dbh->quote($loc{$_});
402 push @orwhere, "($string)";
404 push @tax_where, '(' . join(' OR ', @orwhere) . ')' if @orwhere;
408 if ( $cgi->param('taxnum') =~ /^([\d,]+)$/) {
409 push @tax_where, "cust_main_county.taxnum IN ($1)";
412 # If we're showing exempt items, we need to find those with
413 # cust_tax_exempt_pkg records matching the selected taxes.
414 # If we're showing taxable items, we need to find those with
415 # cust_bill_pkg_tax_location records. We also need to find the
416 # exemption records so that we can show the taxable amount.
417 # If we're showing all items, we need the union of those.
418 # If we're showing 'out' (items that aren't region/class taxable),
419 # then we need the set of all items minus the union of those.
423 if ( @exempt_where or @tax_where
424 or $cgi->param('taxable') or $cgi->param('out') )
426 # process exemption restrictions, including @tax_where
427 my $exempt_sub = 'SELECT SUM(amount) as exempt_amount, billpkgnum
428 FROM cust_tax_exempt_pkg JOIN cust_main_county USING (taxnum)';
430 $exempt_sub .= ' WHERE '.join(' AND ', @tax_where, @exempt_where)
431 if (@tax_where or @exempt_where);
433 $exempt_sub .= ' GROUP BY billpkgnum';
435 $join_pkg .= " LEFT JOIN ($exempt_sub) AS item_exempt
438 # process tax restrictions
440 'cust_bill_pkg_tax_location.taxable_billpkgnum = cust_bill_pkg.billpkgnum',
441 'cust_main_county.tax > 0';
444 my $tax_sub = "SELECT 1
445 FROM cust_bill_pkg_tax_location
446 JOIN cust_bill_pkg AS tax_item USING (billpkgnum)
447 JOIN cust_main_county USING (taxnum)
448 WHERE ". join(' AND ', @tax_where);
450 # now do something with that
451 if ( @exempt_where ) {
453 push @where, 'item_exempt.billpkgnum IS NOT NULL';
454 push @select, 'item_exempt.exempt_amount';
455 push @peritem, 'exempt_amount';
456 push @peritem_desc, 'Exempt';
457 push @total, 'SUM(exempt_amount)';
458 push @total_desc, "$money_char%.2f tax-exempt";
460 } elsif ( $cgi->param('taxable') ) {
462 my $taxable = 'cust_bill_pkg.setup + cust_bill_pkg.recur '.
463 '- COALESCE(item_exempt.exempt_amount, 0)';
465 push @select, "($taxable) AS taxable_amount";
466 push @where, "EXISTS($tax_sub)";
467 push @peritem, 'taxable_amount';
468 push @peritem_desc, 'Taxable';
469 push @total, "SUM($taxable)";
470 push @total_desc, "$money_char%.2f taxable";
472 } elsif ( @tax_where ) {
474 # union of taxable + all exempt_ cases
475 push @where, "(EXISTS($tax_sub) OR item_exempt.billpkgnum IS NOT NULL)";
479 # recur/usage separation
480 if ( $cgi->param('usage') eq 'recurring' ) {
482 my $recur_no_usage = FS::cust_bill_pkg->charged_sql('', '', no_usage => 1);
483 push @select, "($recur_no_usage) AS recur_no_usage";
484 $peritem[1] = 'recur_no_usage';
485 $total[1] = "SUM(cust_bill_pkg.setup + $recur_no_usage)";
486 $total_desc[0] .= ' (excluding usage)';
488 } elsif ( $cgi->param('usage') eq 'usage' ) {
490 my $usage = FS::cust_bill_pkg->usage_sql();
491 push @select, "($usage) AS _usage";
492 # there's already a method named 'usage'
493 $peritem[1] = '_usage';
494 $peritem_desc[1] = 'Usage charge';
495 $total[1] = "SUM($usage)";
496 $total_desc[0] .= ' usage charges';
499 } elsif ( $cgi->param('istax') ) {
501 @peritem = ( 'setup' ); # taxes only have setup
502 @peritem_desc = ( 'Tax charge' );
504 push @where, 'cust_bill_pkg.pkgnum = 0';
506 # tax location when using tax_rate_location
507 if ( scalar( grep( /locationtaxid/, $cgi->param ) ) ) {
509 $join_pkg .= ' LEFT JOIN cust_bill_pkg_tax_rate_location USING ( billpkgnum ) '.
510 ' LEFT JOIN tax_rate_location USING ( taxratelocationnum )';
511 push @where, FS::tax_rate_location->location_sql(
512 map { $_ => (scalar($cgi->param($_)) || '') }
513 qw( district city county state locationtaxid )
517 COALESCE(cust_bill_pkg_tax_rate_location.amount,
518 cust_bill_pkg.setup + cust_bill_pkg.recur)
521 } elsif ( $cgi->param('out') ) {
524 LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum)
526 push @where, 'cust_bill_pkg_tax_location.billpkgnum IS NULL';
528 # each billpkgnum should appear only once
529 $total[0] = 'COUNT(*)';
530 $total[1] = 'SUM(cust_bill_pkg.setup)';
532 } else { # not locationtaxid or 'out'--the normal case
535 LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum)
536 JOIN cust_main_county USING (taxnum)
539 # don't double-count the components of consolidated taxes
540 $total[0] = 'COUNT(DISTINCT cust_bill_pkg.billpkgnum)';
541 $total[1] = 'SUM(cust_bill_pkg_tax_location.amount)';
544 if ( grep { $_ eq 'classnum' } $cgi->param ) {
545 my @classnums = grep /^\d*$/, $cgi->param('classnum');
547 JOIN cust_pkg AS taxed_pkg
548 ON (cust_bill_pkg_tax_location.pkgnum = taxed_pkg.pkgnum)
549 JOIN part_pkg AS taxed_part_pkg
550 ON (taxed_pkg.pkgpart = taxed_part_pkg.pkgpart)
552 push @where, "COALESCE(taxed_part_pkg.classnum, 0) IN ( ".
553 join(',', @classnums ).
560 if ( $cgi->param('taxclassNULL') ) {
561 push @where, 'cust_main_county.taxclass IS NULL';
565 if ( $cgi->param('taxnameNULL') ) {
566 push @where, 'cust_main_county.taxname IS NULL OR '.
567 'cust_main_county.taxname = \'Tax\'';
568 } elsif ( $cgi->param('taxname') ) {
569 push @where, 'cust_main_county.taxname = '.
570 dbh->quote($cgi->param('taxname'));
574 if ( $cgi->param('taxnum') =~ /^([\d,]+)$/) {
575 push @where, "cust_main_county.taxnum IN ($1)";
578 # itemdesc, for some reason
579 if ( $cgi->param('itemdesc') ) {
580 if ( $cgi->param('itemdesc') eq 'Tax' ) {
581 push @where, "(itemdesc='Tax' OR itemdesc is null)";
583 push @where, 'itemdesc='. dbh->quote($cgi->param('itemdesc'));
591 my $pay_sub = "SELECT SUM(cust_bill_pay_pkg.amount)
592 FROM cust_bill_pay_pkg
593 WHERE cust_bill_pkg.billpkgnum = cust_bill_pay_pkg.billpkgnum
595 push @select, "($pay_sub) AS pay_amount";
599 if ( $cgi->param('credit') ) {
603 if ( $cgi->param('istax') ) {
604 # then we need to group/join by billpkgtaxlocationnum, to get only the
605 # relevant part of partial taxes
606 my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
607 reason.reason as reason_text, access_user.username AS username_text,
608 billpkgtaxlocationnum, billpkgnum
609 FROM cust_credit_bill_pkg
610 JOIN cust_credit_bill USING (creditbillnum)
611 JOIN cust_credit USING (crednum)
612 LEFT JOIN reason USING (reasonnum)
613 LEFT JOIN access_user USING (usernum)
614 GROUP BY billpkgnum, billpkgtaxlocationnum, reason.reason,
615 access_user.username";
617 if ( $cgi->param('out') ) {
619 # find credits that are applied to the line items, but not to
620 # a cust_bill_pkg_tax_location link
621 $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
623 push @where, 'item_credit.billpkgtaxlocationnum IS NULL';
627 # find credits that are applied to the CBPTL links that are
628 # considered "interesting" by the report criteria
629 $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
630 USING (billpkgtaxlocationnum)";
635 # then only group by billpkgnum
636 my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
637 reason.reason as reason_text, access_user.username AS username_text,
639 FROM cust_credit_bill_pkg
640 JOIN cust_credit_bill USING (creditbillnum)
641 JOIN cust_credit USING (crednum)
642 LEFT JOIN reason USING (reasonnum)
643 LEFT JOIN access_user USING (usernum)
644 GROUP BY billpkgnum, reason.reason, access_user.username";
645 $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit USING (billpkgnum)";
648 push @where, 'item_credit.billpkgnum IS NOT NULL';
649 push @select, 'item_credit.credit_amount',
650 'item_credit.username_text',
651 'item_credit.reason_text';
652 push @peritem, 'credit_amount', 'username_text', 'reason_text';
653 push @peritem_desc, 'Credited', 'By', 'Reason';
654 push @total, 'SUM(credit_amount)';
655 push @total_desc, "$money_char%.2f credited";
659 #still want a credit total column
662 SELECT SUM(cust_credit_bill_pkg.amount)
663 FROM cust_credit_bill_pkg
664 WHERE cust_bill_pkg.billpkgnum = cust_credit_bill_pkg.billpkgnum
666 push @select, "($credit_sub) AS credit_amount";
670 push @select, 'cust_main.custnum', FS::UI::Web::cust_sql_fields();
673 if ( $cgi->param('salesnum') =~ /^(\d+)$/ ) {
676 my $sales = FS::sales->by_key($salesnum)
677 or die "salesnum $salesnum not found";
679 my $subsearch = $sales->cust_bill_pkg_search('', '',
680 'cust_main_sales' => ($cgi->param('cust_main_sales') ? 1 : 0),
681 'paid' => ($cgi->param('paid') ? 1 : 0),
682 'classnum' => scalar($cgi->param('classnum'))
684 $join_pkg .= " JOIN sales_pkg_class ON ( COALESCE(sales_pkg_class.classnum, 0) = COALESCE( part_pkg.classnum, 0) )";
686 my $extra_sql = $subsearch->{extra_sql};
687 $extra_sql =~ s/^WHERE//;
688 push @where, $extra_sql;
690 $cgi->param('classnum', 0) unless $cgi->param('classnum');
694 my $where = join(' AND ', @where);
695 $where &&= "WHERE $where";
698 'table' => 'cust_bill_pkg',
699 'addl_from' => "$join_pkg $join_cust",
701 'select' => join(",\n", @select ),
702 'extra_sql' => $where,
703 'order_by' => 'ORDER BY cust_bill._date, cust_bill_pkg.billpkgnum',
707 'SELECT ' . join(',', @total) .
708 " FROM cust_bill_pkg $join_pkg $join_cust
711 @peritem_desc = map {emt($_)} @peritem_desc;
712 my @peritem_sub = map {
714 if ($field =~ /_text$/) { # kludge for credit reason/username fields
715 sub {$_[0]->get($field)};
717 sub { sprintf($money_char.'%.2f', $_[0]->get($field)) }
720 my @peritem_null = map { '' } @peritem; # placeholders
721 my $peritem_align = 'r' x scalar(@peritem);
723 @currency_desc = map {emt($_)} @currency_desc;
724 my @currency_null = map { '' } @currency; # placeholders
725 my $currency_align = 'r' x scalar(@currency);
727 my $ilink = [ "${p}view/cust_bill.cgi?", 'invnum' ];
728 my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ];
730 my $pay_link = ''; #[, 'billpkgnum', ];
731 my $credit_link = [ "${p}search/cust_credit_bill_pkg.html?billpkgnum=", 'billpkgnum', ];
733 warn "\n\nQUERY:\n".Dumper($query)."\n\nCOUNT_QUERY:\n$count_query\n\n"
734 if $cgi->param('debug');