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, a comma-separated list, the word "multiple", or an empty
136 string (for "no report class").
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('refnum') =~ /^(\d+)$/ ) {
288 push @where, "cust_main.refnum = $1";
291 # 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)
292 if ( grep { $_ eq 'cust_classnum' } $cgi->param ) {
293 my @classnums = grep /^\d*$/, $cgi->param('cust_classnum');
294 push @where, 'COALESCE( cust_main.classnum, 0) IN ( '.
295 join(',', map { $_ || '0' } @classnums ).
302 if ( $cgi->param('custnum') =~ /^(\d+)$/ ) {
303 push @where, "cust_main.custnum = $1";
306 # we want the package and its definition if available
308 ' LEFT JOIN cust_pkg USING (pkgnum)
309 LEFT JOIN part_pkg USING (pkgpart)';
311 my $part_pkg = 'part_pkg';
312 if ( $cgi->param('use_override') ) { #"Separate sub-packages from parents"
313 # still need the real part_pkg for tax applicability,
315 $join_pkg .= " LEFT JOIN part_pkg AS override ON (
316 COALESCE(cust_bill_pkg.pkgpart_override, cust_pkg.pkgpart, 0) = override.pkgpart
318 $part_pkg = 'override';
320 push @select, 'part_pkg.pkgpart', 'part_pkg.pkg'; # or should this use override?
323 if ( $cgi->param('nottax') ) {
325 push @where, 'cust_bill_pkg.pkgnum > 0';
327 my @tax_where; # will go into a subquery
328 my @exempt_where; # will also go into a subquery
330 # classnum (of override pkgpart if applicable)
331 # not specified: all classes
334 if ( $cgi->param('classnum') =~ /^(\d+)$/ ) {
335 push @where, "COALESCE($part_pkg.classnum, 0) = $1";
338 if ( $cgi->param('report_optionnum') =~ /^(\w+)$/ ) {
342 FS::Report::Table->with_report_option( $1, $cgi->param('use_override') )
347 if ( $cgi->param('taxclassNULL') ) {
348 # a little different from 'taxclass' in that it applies to the
349 # effective taxclass, not the real one
350 push @tax_where, 'cust_main_county.taxclass IS NULL'
351 } elsif ( $cgi->param('taxclass') ) {
352 push @tax_where, "$part_pkg.taxclass IN (" .
353 join(', ', map {dbh->quote($_)} $cgi->param('taxclass') ).
357 if ( $cgi->param('exempt_cust') eq 'Y' ) {
358 # tax-exempt customers
359 push @exempt_where, "(exempt_cust = 'Y' OR exempt_cust_taxname = 'Y')";
361 } elsif ( $cgi->param('exempt_pkg') eq 'Y' ) { # non-taxable package
362 # non-taxable package charges
363 push @exempt_where, "(exempt_setup = 'Y' OR exempt_recur = 'Y')";
365 # we don't handle exempt_monthly here
367 if ( $cgi->param('taxname') ) { # specific taxname
368 push @tax_where, 'cust_main_county.taxname = '.
369 dbh->quote($cgi->param('taxname'));
370 } elsif ( $cgi->param('taxnameNULL') ) {
371 push @tax_where, 'cust_main_county.taxname IS NULL OR '.
372 'cust_main_county.taxname = \'Tax\'';
375 # country:state:county:city:district (may be repeated)
376 # You can also pass a big list of taxnums but that leads to huge URLs.
377 # Note that this means "packages whose tax is in this region", not
378 # "packages in this region". It's meant for links from the tax report.
379 if ( $cgi->param('region') ) {
381 foreach ( $cgi->param('region') ) {
383 @loc{qw(country state county city district)} =
384 split(':', $cgi->param('region'));
385 my $string = join(' AND ',
388 "$_ = ".dbh->quote($loc{$_});
394 push @orwhere, "($string)";
396 push @tax_where, '(' . join(' OR ', @orwhere) . ')' if @orwhere;
400 if ( $cgi->param('taxnum') ) {
401 my $taxnum_in = join(',',
402 grep /^\d+$/, $cgi->param('taxnum')
404 push @tax_where, "cust_main_county.taxnum IN ($taxnum_in)"
408 # If we're showing exempt items, we need to find those with
409 # cust_tax_exempt_pkg records matching the selected taxes.
410 # If we're showing taxable items, we need to find those with
411 # cust_bill_pkg_tax_location records. We also need to find the
412 # exemption records so that we can show the taxable amount.
413 # If we're showing all items, we need the union of those.
414 # If we're showing 'out' (items that aren't region/class taxable),
415 # then we need the set of all items minus the union of those.
419 if ( @exempt_where or @tax_where
420 or $cgi->param('taxable') or $cgi->param('out') )
422 # process exemption restrictions, including @tax_where
423 my $exempt_sub = 'SELECT SUM(amount) as exempt_amount, billpkgnum
424 FROM cust_tax_exempt_pkg JOIN cust_main_county USING (taxnum)';
426 $exempt_sub .= ' WHERE '.join(' AND ', @tax_where, @exempt_where)
427 if (@tax_where or @exempt_where);
429 $exempt_sub .= ' GROUP BY billpkgnum';
431 $join_pkg .= " LEFT JOIN ($exempt_sub) AS item_exempt
435 if ( @tax_where or $cgi->param('taxable') or $cgi->param('out') ) {
436 # process tax restrictions
438 'cust_main_county.tax > 0';
440 my $tax_sub = "SELECT invnum, cust_bill_pkg_tax_location.pkgnum
441 FROM cust_bill_pkg_tax_location
442 JOIN cust_bill_pkg AS tax_item USING (billpkgnum)
443 JOIN cust_main_county USING (taxnum)
444 WHERE ". join(' AND ', @tax_where).
445 " GROUP BY invnum, cust_bill_pkg_tax_location.pkgnum";
447 $join_pkg .= " LEFT JOIN ($tax_sub) AS item_tax
448 ON (item_tax.invnum = cust_bill_pkg.invnum AND
449 item_tax.pkgnum = cust_bill_pkg.pkgnum)";
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 @where, 'item_tax.invnum IS NOT NULL';
468 push @select, "($taxable) AS taxable_amount";
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 ( $cgi->param('out') ) {
476 push @where, 'item_tax.invnum IS NULL',
477 'item_exempt.billpkgnum IS NULL';
479 } elsif ( @tax_where ) {
481 # union of taxable + all exempt_ cases
483 '(item_tax.invnum IS NOT NULL OR item_exempt.billpkgnum IS NOT NULL)';
487 # recur/usage separation
488 if ( $cgi->param('usage') eq 'recurring' ) {
490 my $recur_no_usage = FS::cust_bill_pkg->charged_sql('', '', no_usage => 1);
491 push @select, "($recur_no_usage) AS recur_no_usage";
492 $peritem[1] = 'recur_no_usage';
493 $total[1] = "SUM(cust_bill_pkg.setup + $recur_no_usage)";
494 $total_desc[0] .= ' (excluding usage)';
496 } elsif ( $cgi->param('usage') eq 'usage' ) {
498 my $usage = FS::cust_bill_pkg->usage_sql();
499 push @select, "($usage) AS _usage";
500 # there's already a method named 'usage'
501 $peritem[1] = '_usage';
502 $peritem_desc[1] = 'Usage charge';
503 $total[1] = "SUM($usage)";
504 $total_desc[0] .= ' usage charges';
507 } elsif ( $cgi->param('istax') ) {
509 @peritem = ( 'setup' ); # taxes only have setup
510 @peritem_desc = ( 'Tax charge' );
512 push @where, 'cust_bill_pkg.pkgnum = 0';
514 # tax location when using tax_rate_location
515 if ( scalar( grep( /locationtaxid/, $cgi->param ) ) ) {
517 $join_pkg .= ' LEFT JOIN cust_bill_pkg_tax_rate_location USING ( billpkgnum ) '.
518 ' LEFT JOIN tax_rate_location USING ( taxratelocationnum )';
519 push @where, FS::tax_rate_location->location_sql(
520 map { $_ => (scalar($cgi->param($_)) || '') }
521 qw( district city county state locationtaxid )
525 COALESCE(cust_bill_pkg_tax_rate_location.amount,
526 cust_bill_pkg.setup + cust_bill_pkg.recur)
529 } elsif ( $cgi->param('out') ) {
532 LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum)
534 push @where, 'cust_bill_pkg_tax_location.billpkgnum IS NULL';
536 # each billpkgnum should appear only once
537 $total[0] = 'COUNT(*)';
538 $total[1] = 'SUM(cust_bill_pkg.setup)';
540 } else { # not locationtaxid or 'out'--the normal case
543 LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum)
544 JOIN cust_main_county USING (taxnum)
547 # don't double-count the components of consolidated taxes
548 $total[0] = 'COUNT(DISTINCT cust_bill_pkg.billpkgnum)';
549 $total[1] = 'SUM(cust_bill_pkg_tax_location.amount)';
553 if ( $cgi->param('taxclassNULL') ) {
554 push @where, 'cust_main_county.taxclass IS NULL';
558 if ( $cgi->param('taxnameNULL') ) {
559 push @where, 'cust_main_county.taxname IS NULL OR '.
560 'cust_main_county.taxname = \'Tax\'';
561 } elsif ( $cgi->param('taxname') ) {
562 push @where, 'cust_main_county.taxname = '.
563 dbh->quote($cgi->param('taxname'));
567 if ( $cgi->param('taxnum') ) {
568 my $taxnum_in = join(',',
569 grep /^\d+$/, $cgi->param('taxnum')
571 push @where, "cust_main_county.taxnum IN ($taxnum_in)"
575 # report group (itemdesc)
576 if ( $cgi->param('report_group') =~ /^(=|!=) (.*)$/ ) {
577 my ( $group_op, $group_value ) = ( $1, $2 );
578 if ( $group_op eq '=' ) {
579 #push @where, 'itemdesc LIKE '. dbh->quote($group_value.'%');
580 push @where, 'itemdesc = '. dbh->quote($group_value);
581 } elsif ( $group_op eq '!=' ) {
582 push @where, '( itemdesc != '. dbh->quote($group_value) .' OR itemdesc IS NULL )';
584 die "guru meditation #00de: group_op $group_op\n";
588 # itemdesc, for some reason
589 if ( $cgi->param('itemdesc') ) {
590 if ( $cgi->param('itemdesc') eq 'Tax' ) {
591 push @where, "(itemdesc='Tax' OR itemdesc is null)";
593 push @where, 'itemdesc='. dbh->quote($cgi->param('itemdesc'));
601 my $pay_sub = "SELECT SUM(cust_bill_pay_pkg.amount)
602 FROM cust_bill_pay_pkg
603 WHERE cust_bill_pkg.billpkgnum = cust_bill_pay_pkg.billpkgnum
605 push @select, "($pay_sub) AS pay_amount";
609 if ( $cgi->param('credit') ) {
613 if ( $cgi->param('istax') ) {
614 # then we need to group/join by billpkgtaxlocationnum, to get only the
615 # relevant part of partial taxes
616 my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
617 reason.reason as reason_text, access_user.username AS username_text,
618 billpkgtaxlocationnum, billpkgnum
619 FROM cust_credit_bill_pkg
620 JOIN cust_credit_bill USING (creditbillnum)
621 JOIN cust_credit USING (crednum)
622 LEFT JOIN reason USING (reasonnum)
623 LEFT JOIN access_user USING (usernum)
624 GROUP BY billpkgnum, billpkgtaxlocationnum, reason.reason,
625 access_user.username";
627 if ( $cgi->param('out') ) {
629 # find credits that are applied to the line items, but not to
630 # a cust_bill_pkg_tax_location link
631 $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
633 push @where, 'item_credit.billpkgtaxlocationnum IS NULL';
637 # find credits that are applied to the CBPTL links that are
638 # considered "interesting" by the report criteria
639 $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
640 USING (billpkgtaxlocationnum)";
645 # then only group by billpkgnum
646 my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
647 reason.reason as reason_text, access_user.username AS username_text,
649 FROM cust_credit_bill_pkg
650 JOIN cust_credit_bill USING (creditbillnum)
651 JOIN cust_credit USING (crednum)
652 LEFT JOIN reason USING (reasonnum)
653 LEFT JOIN access_user USING (usernum)
654 GROUP BY billpkgnum, reason.reason, access_user.username";
655 $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit USING (billpkgnum)";
658 push @where, 'item_credit.billpkgnum IS NOT NULL';
659 push @select, 'item_credit.credit_amount',
660 'item_credit.username_text',
661 'item_credit.reason_text';
662 push @peritem, 'credit_amount', 'username_text', 'reason_text';
663 push @peritem_desc, 'Credited', 'By', 'Reason';
664 push @total, 'SUM(credit_amount)';
665 push @total_desc, "$money_char%.2f credited";
669 #still want a credit total column
672 SELECT SUM(cust_credit_bill_pkg.amount)
673 FROM cust_credit_bill_pkg
674 WHERE cust_bill_pkg.billpkgnum = cust_credit_bill_pkg.billpkgnum
676 push @select, "($credit_sub) AS credit_amount";
680 push @select, 'cust_main.custnum', FS::UI::Web::cust_sql_fields();
682 my $where = join(' AND ', @where);
683 $where &&= "WHERE $where";
686 'table' => 'cust_bill_pkg',
687 'addl_from' => "$join_pkg $join_cust",
689 'select' => join(",\n", @select ),
690 'extra_sql' => $where,
691 'order_by' => 'ORDER BY cust_bill._date, cust_bill_pkg.billpkgnum',
695 'SELECT ' . join(',', @total) .
696 " FROM cust_bill_pkg $join_pkg $join_cust
699 @peritem_desc = map {emt($_)} @peritem_desc;
700 my @peritem_sub = map {
702 if ($field =~ /_text$/) { # kludge for credit reason/username fields
703 sub {$_[0]->get($field)};
705 sub { sprintf($money_char.'%.2f', $_[0]->get($field)) }
708 my @peritem_null = map { '' } @peritem; # placeholders
709 my $peritem_align = 'r' x scalar(@peritem);
711 @currency_desc = map {emt($_)} @currency_desc;
712 my @currency_null = map { '' } @currency; # placeholders
713 my $currency_align = 'r' x scalar(@currency);
715 my $ilink = [ "${p}view/cust_bill.cgi?", 'invnum' ];
716 my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ];
718 my $pay_link = ''; #[, 'billpkgnum', ];
719 my $credit_link = [ "${p}search/cust_credit_bill_pkg.html?billpkgnum=", 'billpkgnum', ];
721 warn "\n\nQUERY:\n".Dumper($query)."\n\nCOUNT_QUERY:\n$count_query\n\n"
722 if $cgi->param('debug');