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 - use_override: Apply "classnum" and "taxclass" filtering based on the
135 override (bundle) pkgpart, rather than always using the true pkgpart.
137 - nottax: Limit to items that are not taxes (pkgnum > 0).
139 - istax: Limit to items that are taxes (pkgnum == 0).
141 - taxnum: Limit to items whose tax definition matches this taxnum.
142 With "nottax" that means items that are subject to that tax;
143 with "istax" it's the tax charges themselves. Can be specified
144 more than once to include multiple taxes.
146 - country, state, county, city: Limit to items whose tax location
147 matches these fields. If "nottax" it's the tax location of the package;
148 if "istax" the location of the tax.
150 - taxname, taxnameNULL: With "nottax", limit to items whose tax location
151 matches a tax with this name. With "istax", limit to items that have
152 this tax name. taxnameNULL is equivalent to "taxname = '' OR taxname
155 - out: With "nottax", limit to items that don't match any tax definition.
156 With "istax", find tax items that are unlinked to their tax definitions.
157 Current Freeside (> July 2012) always creates tax links, but unlinked
158 items may result from an incomplete upgrade of legacy data.
160 - locationtaxid: With "nottax", limit to packages matching this
161 tax_rate_location ID; with "tax", limit to taxes generated from that
164 - taxclass: Filter on package taxclass.
166 - taxclassNULL: With "nottax", limit to items that would be subject to the
167 tax with taxclass = NULL. This doesn't necessarily mean part_pkg.taxclass
168 is NULL; it also includes taxclasses that don't have a tax in this region.
170 - itemdesc: Limit to line items with this description. Note that non-tax
171 packages usually have a description of NULL. (Deprecated.)
173 - report_group: Can contain '=' or '!=' followed by a string to limit to
174 line items where itemdesc starts with, or doesn't start with, the string.
176 - cust_tax: Limit to customers who are tax-exempt. If "taxname" is also
177 specified, limit to customers who are also specifically exempt from that
180 - pkg_tax: Limit to packages that are tax-exempt, and only include the
181 exempt portion (setup, recurring, or both) when calculating totals.
183 - taxable: Limit to packages that are subject to tax, i.e. where a
184 cust_bill_pkg_tax_location record exists.
186 - credit: Limit to line items that received a credit application. The
187 amount of the credit will also be shown.
192 my $curuser = $FS::CurrentUser::CurrentUser;
194 die "access denied" unless $curuser->access_right('Financial reports');
196 my $conf = new FS::Conf;
197 my $money_char = $conf->config('money_char') || '$';
199 my @select = ( 'cust_bill_pkg.*', 'cust_bill._date' );
200 my @total = ( 'COUNT(*)', 'SUM(cust_bill_pkg.setup + cust_bill_pkg.recur)');
201 my @total_desc = ( $money_char.'%.2f total' ); # sprintf strings
203 my @peritem = ( 'setup', 'recur' );
204 my @peritem_desc = ( 'Setup charge', 'Recurring charge' );
206 my @currency_desc = ();
207 my @currency_sub = ();
209 if ( $conf->config('currencies') ) {
210 @currency_desc = ( 'Setup billed', 'Recurring billed' );
214 sub { my $currency = $_[0]->get($what.'_billed_currency') or return '';
215 $currency. ' '. currency_symbol($currency, SYM_HTML).
216 $_[0]->get($what.'_billed_amount');
220 @currency = ( 'setup_billed_amount', 'recur_billed_amount' ); #for sorting
223 my @pkgnum_header = ();
226 my $pkgnum_align = '';
227 if ( $curuser->option('show_pkgnum') ) {
228 push @select, 'cust_bill_pkg.pkgnum';
229 push @pkgnum_header, 'Pkg Num';
230 push @pkgnum, sub { $_[0]->pkgnum > 0 ? $_[0]->pkgnum : '' };
231 push @pkgnum_null, '';
232 $pkgnum_align .= 'r';
235 my @post_desc_header = ();
237 my @post_desc_null = ();
238 my $post_desc_align = '';
239 if ( $conf->exists('enable_taxclasses') ) {
240 push @post_desc_header, 'Tax class';
241 push @post_desc, 'taxclass';
242 push @post_desc_null, '';
243 $post_desc_align .= 'l';
244 push @select, 'part_pkg.taxclass'; # or should this use override?
247 # valid in both the tax and non-tax cases
249 " LEFT JOIN cust_bill ON (cust_bill_pkg.invnum = cust_bill.invnum)".
250 # use cust_pkg.locationnum if it exists
251 FS::UI::Web::join_cust_main('cust_bill', 'cust_pkg');
253 #agent virtualization
255 $FS::CurrentUser::CurrentUser->agentnums_sql( 'table' => 'cust_main' );
257 my @where = ( $agentnums_sql );
260 my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
262 if ( $cgi->param('distribute') == 1 ) {
263 push @where, "sdate <= $ending",
264 "edate > $beginning",
268 push @where, "cust_bill._date >= $beginning",
269 "cust_bill._date <= $ending";
273 if ( $cgi->param('status') =~ /^([a-z]+)$/ ) {
274 push @where, FS::cust_main->cust_status_sql . " = '$1'";
278 if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
279 push @where, "cust_main.agentnum = $1";
283 if ( $cgi->param('refnum') =~ /^(\d+)$/ ) {
284 push @where, "cust_main.refnum = $1";
287 # 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)
288 if ( grep { $_ eq 'cust_classnum' } $cgi->param ) {
289 my @classnums = grep /^\d*$/, $cgi->param('cust_classnum');
290 push @where, 'COALESCE( cust_main.classnum, 0) IN ( '.
291 join(',', map { $_ || '0' } @classnums ).
298 if ( $cgi->param('custnum') =~ /^(\d+)$/ ) {
299 push @where, "cust_main.custnum = $1";
302 # we want the package and its definition if available
304 ' LEFT JOIN cust_pkg USING (pkgnum)
305 LEFT JOIN part_pkg USING (pkgpart)';
307 my $part_pkg = 'part_pkg';
308 if ( $cgi->param('use_override') ) { #"Separate sub-packages from parents"
309 # still need the real part_pkg for tax applicability,
311 $join_pkg .= " LEFT JOIN part_pkg AS override ON (
312 COALESCE(cust_bill_pkg.pkgpart_override, cust_pkg.pkgpart, 0) = override.pkgpart
314 $part_pkg = 'override';
316 push @select, 'part_pkg.pkgpart', 'part_pkg.pkg'; # or should this use override?
319 if ( $cgi->param('nottax') ) {
321 push @where, 'cust_bill_pkg.pkgnum > 0';
323 my @tax_where; # will go into a subquery
324 my @exempt_where; # will also go into a subquery
326 # classnum (of override pkgpart if applicable)
327 # not specified: all classes
330 if ( $cgi->param('classnum') =~ /^(\d+)$/ ) {
331 push @where, "COALESCE($part_pkg.classnum, 0) = $1";
335 if ( $cgi->param('taxclassNULL') ) {
336 # a little different from 'taxclass' in that it applies to the
337 # effective taxclass, not the real one
338 push @tax_where, 'cust_main_county.taxclass IS NULL'
339 } elsif ( $cgi->param('taxclass') ) {
340 push @tax_where, "$part_pkg.taxclass IN (" .
341 join(', ', map {dbh->quote($_)} $cgi->param('taxclass') ).
345 if ( $cgi->param('exempt_cust') eq 'Y' ) {
346 # tax-exempt customers
347 push @exempt_where, "(exempt_cust = 'Y' OR exempt_cust_taxname = 'Y')";
349 } elsif ( $cgi->param('exempt_pkg') eq 'Y' ) { # non-taxable package
350 # non-taxable package charges
351 push @exempt_where, "(exempt_setup = 'Y' OR exempt_recur = 'Y')";
353 # we don't handle exempt_monthly here
355 if ( $cgi->param('taxname') ) { # specific taxname
356 push @tax_where, 'cust_main_county.taxname = '.
357 dbh->quote($cgi->param('taxname'));
358 } elsif ( $cgi->param('taxnameNULL') ) {
359 push @tax_where, 'cust_main_county.taxname IS NULL OR '.
360 'cust_main_county.taxname = \'Tax\'';
363 # country:state:county:city:district (may be repeated)
364 # You can also pass a big list of taxnums but that leads to huge URLs.
365 # Note that this means "packages whose tax is in this region", not
366 # "packages in this region". It's meant for links from the tax report.
367 if ( $cgi->param('region') ) {
369 foreach ( $cgi->param('region') ) {
371 @loc{qw(country state county city district)} =
372 split(':', $cgi->param('region'));
373 my $string = join(' AND ',
376 "$_ = ".dbh->quote($loc{$_});
382 push @orwhere, "($string)";
384 push @tax_where, '(' . join(' OR ', @orwhere) . ')' if @orwhere;
388 if ( $cgi->param('taxnum') ) {
389 my $taxnum_in = join(',',
390 grep /^\d+$/, $cgi->param('taxnum')
392 push @tax_where, "cust_main_county.taxnum IN ($taxnum_in)"
396 # If we're showing exempt items, we need to find those with
397 # cust_tax_exempt_pkg records matching the selected taxes.
398 # If we're showing taxable items, we need to find those with
399 # cust_bill_pkg_tax_location records. We also need to find the
400 # exemption records so that we can show the taxable amount.
401 # If we're showing all items, we need the union of those.
402 # If we're showing 'out' (items that aren't region/class taxable),
403 # then we need the set of all items minus the union of those.
407 if ( @exempt_where or @tax_where
408 or $cgi->param('taxable') or $cgi->param('out') )
410 # process exemption restrictions, including @tax_where
411 my $exempt_sub = 'SELECT SUM(amount) as exempt_amount, billpkgnum
412 FROM cust_tax_exempt_pkg JOIN cust_main_county USING (taxnum)';
414 $exempt_sub .= ' WHERE '.join(' AND ', @tax_where, @exempt_where)
415 if (@tax_where or @exempt_where);
417 $exempt_sub .= ' GROUP BY billpkgnum';
419 $join_pkg .= " LEFT JOIN ($exempt_sub) AS item_exempt
423 if ( @tax_where or $cgi->param('taxable') or $cgi->param('out') ) {
424 # process tax restrictions
426 'cust_main_county.tax > 0';
428 my $tax_sub = "SELECT invnum, cust_bill_pkg_tax_location.pkgnum
429 FROM cust_bill_pkg_tax_location
430 JOIN cust_bill_pkg AS tax_item USING (billpkgnum)
431 JOIN cust_main_county USING (taxnum)
432 WHERE ". join(' AND ', @tax_where).
433 " GROUP BY invnum, cust_bill_pkg_tax_location.pkgnum";
435 $join_pkg .= " LEFT JOIN ($tax_sub) AS item_tax
436 ON (item_tax.invnum = cust_bill_pkg.invnum AND
437 item_tax.pkgnum = cust_bill_pkg.pkgnum)";
440 # now do something with that
441 if ( @exempt_where ) {
443 push @where, 'item_exempt.billpkgnum IS NOT NULL';
444 push @select, 'item_exempt.exempt_amount';
445 push @peritem, 'exempt_amount';
446 push @peritem_desc, 'Exempt';
447 push @total, 'SUM(exempt_amount)';
448 push @total_desc, "$money_char%.2f tax-exempt";
450 } elsif ( $cgi->param('taxable') ) {
452 my $taxable = 'cust_bill_pkg.setup + cust_bill_pkg.recur '.
453 '- COALESCE(item_exempt.exempt_amount, 0)';
455 push @where, 'item_tax.invnum IS NOT NULL';
456 push @select, "($taxable) AS taxable_amount";
457 push @peritem, 'taxable_amount';
458 push @peritem_desc, 'Taxable';
459 push @total, "SUM($taxable)";
460 push @total_desc, "$money_char%.2f taxable";
462 } elsif ( $cgi->param('out') ) {
464 push @where, 'item_tax.invnum IS NULL',
465 'item_exempt.billpkgnum IS NULL';
467 } elsif ( @tax_where ) {
469 # union of taxable + all exempt_ cases
471 '(item_tax.invnum IS NOT NULL OR item_exempt.billpkgnum IS NOT NULL)';
475 # recur/usage separation
476 if ( $cgi->param('usage') eq 'recurring' ) {
478 my $recur_no_usage = FS::cust_bill_pkg->charged_sql('', '', no_usage => 1);
479 push @select, "($recur_no_usage) AS recur_no_usage";
480 $peritem[1] = 'recur_no_usage';
481 $total[1] = "SUM(cust_bill_pkg.setup + $recur_no_usage)";
482 $total_desc[0] .= ' (excluding usage)';
484 } elsif ( $cgi->param('usage') eq 'usage' ) {
486 my $usage = FS::cust_bill_pkg->usage_sql();
487 push @select, "($usage) AS _usage";
488 # there's already a method named 'usage'
489 $peritem[1] = '_usage';
490 $peritem_desc[1] = 'Usage charge';
491 $total[1] = "SUM($usage)";
492 $total_desc[0] .= ' usage charges';
495 } elsif ( $cgi->param('istax') ) {
497 @peritem = ( 'setup' ); # taxes only have setup
498 @peritem_desc = ( 'Tax charge' );
500 push @where, 'cust_bill_pkg.pkgnum = 0';
502 # tax location when using tax_rate_location
503 if ( scalar( grep( /locationtaxid/, $cgi->param ) ) ) {
505 $join_pkg .= ' LEFT JOIN cust_bill_pkg_tax_rate_location USING ( billpkgnum ) '.
506 ' LEFT JOIN tax_rate_location USING ( taxratelocationnum )';
507 push @where, FS::tax_rate_location->location_sql(
508 map { $_ => (scalar($cgi->param($_)) || '') }
509 qw( district city county state locationtaxid )
513 COALESCE(cust_bill_pkg_tax_rate_location.amount,
514 cust_bill_pkg.setup + cust_bill_pkg.recur)
517 } elsif ( $cgi->param('out') ) {
520 LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum)
522 push @where, 'cust_bill_pkg_tax_location.billpkgnum IS NULL';
524 # each billpkgnum should appear only once
525 $total[0] = 'COUNT(*)';
526 $total[1] = 'SUM(cust_bill_pkg.setup)';
528 } else { # not locationtaxid or 'out'--the normal case
531 LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum)
532 JOIN cust_main_county USING (taxnum)
535 # don't double-count the components of consolidated taxes
536 $total[0] = 'COUNT(DISTINCT cust_bill_pkg.billpkgnum)';
537 $total[1] = 'SUM(cust_bill_pkg_tax_location.amount)';
541 if ( $cgi->param('taxclassNULL') ) {
542 push @where, 'cust_main_county.taxclass IS NULL';
546 if ( $cgi->param('taxnameNULL') ) {
547 push @where, 'cust_main_county.taxname IS NULL OR '.
548 'cust_main_county.taxname = \'Tax\'';
549 } elsif ( $cgi->param('taxname') ) {
550 push @where, 'cust_main_county.taxname = '.
551 dbh->quote($cgi->param('taxname'));
555 if ( $cgi->param('taxnum') ) {
556 my $taxnum_in = join(',',
557 grep /^\d+$/, $cgi->param('taxnum')
559 push @where, "cust_main_county.taxnum IN ($taxnum_in)"
563 # report group (itemdesc)
564 if ( $cgi->param('report_group') =~ /^(=|!=) (.*)$/ ) {
565 my ( $group_op, $group_value ) = ( $1, $2 );
566 if ( $group_op eq '=' ) {
567 #push @where, 'itemdesc LIKE '. dbh->quote($group_value.'%');
568 push @where, 'itemdesc = '. dbh->quote($group_value);
569 } elsif ( $group_op eq '!=' ) {
570 push @where, '( itemdesc != '. dbh->quote($group_value) .' OR itemdesc IS NULL )';
572 die "guru meditation #00de: group_op $group_op\n";
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();
670 my $where = join(' AND ', @where);
671 $where &&= "WHERE $where";
674 'table' => 'cust_bill_pkg',
675 'addl_from' => "$join_pkg $join_cust",
677 'select' => join(",\n", @select ),
678 'extra_sql' => $where,
679 'order_by' => 'ORDER BY cust_bill._date, cust_bill_pkg.billpkgnum',
683 'SELECT ' . join(',', @total) .
684 " FROM cust_bill_pkg $join_pkg $join_cust
687 @peritem_desc = map {emt($_)} @peritem_desc;
688 my @peritem_sub = map {
690 if ($field =~ /_text$/) { # kludge for credit reason/username fields
691 sub {$_[0]->get($field)};
693 sub { sprintf($money_char.'%.2f', $_[0]->get($field)) }
696 my @peritem_null = map { '' } @peritem; # placeholders
697 my $peritem_align = 'r' x scalar(@peritem);
699 @currency_desc = map {emt($_)} @currency_desc;
700 my @currency_null = map { '' } @currency; # placeholders
701 my $currency_align = 'r' x scalar(@currency);
703 my $ilink = [ "${p}view/cust_bill.cgi?", 'invnum' ];
704 my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ];
706 my $pay_link = ''; #[, 'billpkgnum', ];
707 my $credit_link = [ "${p}search/cust_credit_bill_pkg.html?billpkgnum=", 'billpkgnum', ];
709 warn "\n\nQUERY:\n".Dumper($query)."\n\nCOUNT_QUERY:\n$count_query\n\n"
710 if $cgi->param('debug');