1 <& elements/search.html,
2 'title' => emt('Line items'),
3 'name' => emt('line items'),
5 'count_query' => $count_query,
6 'count_addl' => \@total_desc,
17 FS::UI::Web::cust_header(),
21 sub { $_[0]->pkgnum > 0
22 # possibly use override.pkg but i think this correct
23 ? $_[0]->get('pkgpart')
26 sub { $_[0]->pkgnum > 0
27 # possibly use override.pkg but i think this correct
29 : $_[0]->get('itemdesc')
32 #strikethrough or "N/A ($amount)" or something these when
33 # 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,
62 ( map { $_ ne 'Cust. Status' ? $clink : '' }
63 FS::UI::Web::cust_header()
66 #'align' => 'rlrrrc'.FS::UI::Web::cust_aligns(),
67 'align' => $pkgnum_align.
72 FS::UI::Web::cust_aligns(),
83 FS::UI::Web::cust_colors(),
95 FS::UI::Web::cust_styles(),
100 Output control parameters:
101 - distribute: Boolean. If true, recurring fees will be "prorated" for the
102 portion of the package date range (sdate-edate) that falls within the date
103 range of the report. Line items will be limited to those for which this
104 portion is > 0. This disables filtering on invoice date.
106 - usage: Separate usage (cust_bill_pkg_detail records) from
107 recurring charges. If set to "usage", will show usage instead of
108 recurring charges. If set to "recurring", will deduct usage and only
109 show the flat rate charge. If not passed, the "recurring charge" column
110 will include usage charges also.
112 Filtering parameters:
113 - begin, end: Date range. Applies to invoice date, not necessarily package
114 date range. But see "distribute".
116 - status: Customer status (active, suspended, etc.). This will filter on
117 _current_ customer status, not status at the time the invoice was generated.
119 - agentnum: Filter on customer agent.
121 - refnum: Filter on customer reference source.
123 - cust_classnum: Filter on customer class.
125 - classnum: Filter on package class.
127 - report_optionnum: Filter on package report class. Can be a single report
128 class number, a comma-separated list, the word "multiple", or an empty
129 string (for "no report class").
131 - use_override: Apply "classnum" and "taxclass" filtering based on the
132 override (bundle) pkgpart, rather than always using the true pkgpart.
134 - nottax: Limit to items that are not taxes (pkgnum > 0).
136 - istax: Limit to items that are taxes (pkgnum == 0).
138 - taxnum: Limit to items whose tax definition matches this taxnum.
139 With "nottax" that means items that are subject to that tax;
140 with "istax" it's the tax charges themselves. Can be specified
141 more than once to include multiple taxes.
143 - country, state, county, city: Limit to items whose tax location
144 matches these fields. If "nottax" it's the tax location of the package;
145 if "istax" the location of the tax.
147 - taxname, taxnameNULL: With "nottax", limit to items whose tax location
148 matches a tax with this name. With "istax", limit to items that have
149 this tax name. taxnameNULL is equivalent to "taxname = '' OR taxname
152 - out: With "nottax", limit to items that don't match any tax definition.
153 With "istax", find tax items that are unlinked to their tax definitions.
154 Current Freeside (> July 2012) always creates tax links, but unlinked
155 items may result from an incomplete upgrade of legacy data.
157 - locationtaxid: With "nottax", limit to packages matching this
158 tax_rate_location ID; with "tax", limit to taxes generated from that
161 - taxclass: Filter on package taxclass.
163 - taxclassNULL: With "nottax", limit to items that would be subject to the
164 tax with taxclass = NULL. This doesn't necessarily mean part_pkg.taxclass
165 is NULL; it also includes taxclasses that don't have a tax in this region.
167 - itemdesc: Limit to line items with this description. Note that non-tax
168 packages usually have a description of NULL. (Deprecated.)
170 - report_group: Can contain '=' or '!=' followed by a string to limit to
171 line items where itemdesc starts with, or doesn't start with, the string.
173 - cust_tax: Limit to customers who are tax-exempt. If "taxname" is also
174 specified, limit to customers who are also specifically exempt from that
177 - pkg_tax: Limit to packages that are tax-exempt, and only include the
178 exempt portion (setup, recurring, or both) when calculating totals.
180 - taxable: Limit to packages that are subject to tax, i.e. where a
181 cust_bill_pkg_tax_location record exists.
183 - credit: Limit to line items that received a credit application. The
184 amount of the credit will also be shown.
189 my $curuser = $FS::CurrentUser::CurrentUser;
191 die "access denied" unless $curuser->access_right('Financial reports');
193 my $conf = new FS::Conf;
194 my $money_char = $conf->config('money_char') || '$';
196 my @select = ( 'cust_bill_pkg.*', 'cust_bill._date' );
197 my @total = ( 'COUNT(*)', 'SUM(cust_bill_pkg.setup + cust_bill_pkg.recur)');
198 my @total_desc = ( $money_char.'%.2f total' ); # sprintf strings
200 my @peritem = ( 'setup', 'recur' );
201 my @peritem_desc = ( 'Setup charge', 'Recurring charge' );
203 my @pkgnum_header = ();
206 my $pkgnum_align = '';
207 if ( $curuser->option('show_pkgnum') ) {
208 push @select, 'cust_bill_pkg.pkgnum';
209 push @pkgnum_header, 'Pkg Num';
210 push @pkgnum, sub { $_[0]->pkgnum > 0 ? $_[0]->pkgnum : '' };
211 push @pkgnum_null, '';
212 $pkgnum_align .= 'r';
215 my @post_desc_header = ();
217 my @post_desc_null = ();
218 my $post_desc_align = '';
219 if ( $conf->exists('enable_taxclasses') ) {
220 push @post_desc_header, 'Tax class';
221 push @post_desc, 'taxclass';
222 push @post_desc_null, '';
223 $post_desc_align .= 'l';
224 push @select, 'part_pkg.taxclass'; # or should this use override?
227 # valid in both the tax and non-tax cases
229 " LEFT JOIN cust_bill ON (cust_bill_pkg.invnum = cust_bill.invnum)".
230 # use cust_pkg.locationnum if it exists
231 FS::UI::Web::join_cust_main('cust_bill', 'cust_pkg');
233 #agent virtualization
235 $FS::CurrentUser::CurrentUser->agentnums_sql( 'table' => 'cust_main' );
237 my @where = ( $agentnums_sql );
240 my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
242 if ( $cgi->param('distribute') == 1 ) {
243 push @where, "sdate <= $ending",
244 "edate > $beginning",
248 push @where, "cust_bill._date >= $beginning",
249 "cust_bill._date <= $ending";
253 if ( $cgi->param('status') =~ /^([a-z]+)$/ ) {
254 push @where, FS::cust_main->cust_status_sql . " = '$1'";
258 if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
259 push @where, "cust_main.agentnum = $1";
263 if ( $cgi->param('refnum') =~ /^(\d+)$/ ) {
264 push @where, "cust_main.refnum = $1";
267 # 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)
268 if ( grep { $_ eq 'cust_classnum' } $cgi->param ) {
269 my @classnums = grep /^\d*$/, $cgi->param('cust_classnum');
270 push @where, 'COALESCE( cust_main.classnum, 0) IN ( '.
271 join(',', map { $_ || '0' } @classnums ).
278 if ( $cgi->param('custnum') =~ /^(\d+)$/ ) {
279 push @where, "cust_main.custnum = $1";
282 # we want the package and its definition if available
284 ' LEFT JOIN cust_pkg USING (pkgnum)
285 LEFT JOIN part_pkg USING (pkgpart)';
287 my $part_pkg = 'part_pkg';
288 if ( $cgi->param('use_override') ) { #"Separate sub-packages from parents"
289 # still need the real part_pkg for tax applicability,
291 $join_pkg .= " LEFT JOIN part_pkg AS override ON (
292 COALESCE(cust_bill_pkg.pkgpart_override, cust_pkg.pkgpart, 0) = override.pkgpart
294 $part_pkg = 'override';
296 push @select, 'part_pkg.pkgpart', 'part_pkg.pkg'; # or should this use override?
299 if ( $cgi->param('nottax') ) {
301 push @where, 'cust_bill_pkg.pkgnum > 0';
303 my @tax_where; # will go into a subquery
304 my @exempt_where; # will also go into a subquery
306 # classnum (of override pkgpart if applicable)
307 # not specified: all classes
310 if ( $cgi->param('classnum') =~ /^(\d+)$/ ) {
311 push @where, "COALESCE($part_pkg.classnum, 0) = $1";
314 if ( $cgi->param('report_optionnum') =~ /^(\w+)$/ ) {
318 FS::Report::Table->with_report_option( $1, $cgi->param('use_override') )
323 if ( $cgi->param('taxclassNULL') ) {
324 # a little different from 'taxclass' in that it applies to the
325 # effective taxclass, not the real one
326 push @tax_where, 'cust_main_county.taxclass IS NULL'
327 } elsif ( $cgi->param('taxclass') ) {
328 push @tax_where, "$part_pkg.taxclass IN (" .
329 join(', ', map {dbh->quote($_)} $cgi->param('taxclass') ).
333 if ( $cgi->param('exempt_cust') eq 'Y' ) {
334 # tax-exempt customers
335 push @exempt_where, "(exempt_cust = 'Y' OR exempt_cust_taxname = 'Y')";
337 } elsif ( $cgi->param('exempt_pkg') eq 'Y' ) { # non-taxable package
338 # non-taxable package charges
339 push @exempt_where, "(exempt_setup = 'Y' OR exempt_recur = 'Y')";
341 # we don't handle exempt_monthly here
343 if ( $cgi->param('taxname') ) { # specific taxname
344 push @tax_where, 'cust_main_county.taxname = '.
345 dbh->quote($cgi->param('taxname'));
346 } elsif ( $cgi->param('taxnameNULL') ) {
347 push @tax_where, 'cust_main_county.taxname IS NULL OR '.
348 'cust_main_county.taxname = \'Tax\'';
351 # country:state:county:city:district (may be repeated)
352 # You can also pass a big list of taxnums but that leads to huge URLs.
353 # Note that this means "packages whose tax is in this region", not
354 # "packages in this region". It's meant for links from the tax report.
355 if ( $cgi->param('region') ) {
357 foreach ( $cgi->param('region') ) {
359 @loc{qw(country state county city district)} =
360 split(':', $cgi->param('region'));
361 my $string = join(' AND ',
364 "$_ = ".dbh->quote($loc{$_});
370 push @orwhere, "($string)";
372 push @tax_where, '(' . join(' OR ', @orwhere) . ')' if @orwhere;
376 if ( $cgi->param('taxnum') ) {
377 my $taxnum_in = join(',',
378 grep /^\d+$/, $cgi->param('taxnum')
380 push @tax_where, "cust_main_county.taxnum IN ($taxnum_in)"
384 # If we're showing exempt items, we need to find those with
385 # cust_tax_exempt_pkg records matching the selected taxes.
386 # If we're showing taxable items, we need to find those with
387 # cust_bill_pkg_tax_location records. We also need to find the
388 # exemption records so that we can show the taxable amount.
389 # If we're showing all items, we need the union of those.
390 # If we're showing 'out' (items that aren't region/class taxable),
391 # then we need the set of all items minus the union of those.
395 if ( @exempt_where or @tax_where
396 or $cgi->param('taxable') or $cgi->param('out') )
398 # process exemption restrictions, including @tax_where
399 my $exempt_sub = 'SELECT SUM(amount) as exempt_amount, billpkgnum
400 FROM cust_tax_exempt_pkg JOIN cust_main_county USING (taxnum)';
402 $exempt_sub .= ' WHERE '.join(' AND ', @tax_where, @exempt_where)
403 if (@tax_where or @exempt_where);
405 $exempt_sub .= ' GROUP BY billpkgnum';
407 $join_pkg .= " LEFT JOIN ($exempt_sub) AS item_exempt
411 if ( @tax_where or $cgi->param('taxable') or $cgi->param('out') ) {
412 # process tax restrictions
414 'cust_main_county.tax > 0';
416 my $tax_sub = "SELECT invnum, cust_bill_pkg_tax_location.pkgnum
417 FROM cust_bill_pkg_tax_location
418 JOIN cust_bill_pkg AS tax_item USING (billpkgnum)
419 JOIN cust_main_county USING (taxnum)
420 WHERE ". join(' AND ', @tax_where).
421 " GROUP BY invnum, cust_bill_pkg_tax_location.pkgnum";
423 $join_pkg .= " LEFT JOIN ($tax_sub) AS item_tax
424 ON (item_tax.invnum = cust_bill_pkg.invnum AND
425 item_tax.pkgnum = cust_bill_pkg.pkgnum)";
428 # now do something with that
429 if ( @exempt_where ) {
431 push @where, 'item_exempt.billpkgnum IS NOT NULL';
432 push @select, 'item_exempt.exempt_amount';
433 push @peritem, 'exempt_amount';
434 push @peritem_desc, 'Exempt';
435 push @total, 'SUM(exempt_amount)';
436 push @total_desc, "$money_char%.2f tax-exempt";
438 } elsif ( $cgi->param('taxable') ) {
440 my $taxable = 'cust_bill_pkg.setup + cust_bill_pkg.recur '.
441 '- COALESCE(item_exempt.exempt_amount, 0)';
443 push @where, 'item_tax.invnum IS NOT NULL';
444 push @select, "($taxable) AS taxable_amount";
445 push @peritem, 'taxable_amount';
446 push @peritem_desc, 'Taxable';
447 push @total, "SUM($taxable)";
448 push @total_desc, "$money_char%.2f taxable";
450 } elsif ( $cgi->param('out') ) {
452 push @where, 'item_tax.invnum IS NULL',
453 'item_exempt.billpkgnum IS NULL';
455 } elsif ( @tax_where ) {
457 # union of taxable + all exempt_ cases
459 '(item_tax.invnum IS NOT NULL OR item_exempt.billpkgnum IS NOT NULL)';
463 # recur/usage separation
464 if ( $cgi->param('usage') eq 'recurring' ) {
466 my $recur_no_usage = FS::cust_bill_pkg->charged_sql('', '', no_usage => 1);
467 push @select, "($recur_no_usage) AS recur_no_usage";
468 $peritem[1] = 'recur_no_usage';
469 $total[1] = "SUM(cust_bill_pkg.setup + $recur_no_usage)";
470 $total_desc[0] .= ' (excluding usage)';
472 } elsif ( $cgi->param('usage') eq 'usage' ) {
474 my $usage = FS::cust_bill_pkg->usage_sql();
475 push @select, "($usage) AS _usage";
476 # there's already a method named 'usage'
477 $peritem[1] = '_usage';
478 $peritem_desc[1] = 'Usage charge';
479 $total[1] = "SUM($usage)";
480 $total_desc[0] .= ' usage charges';
483 } elsif ( $cgi->param('istax') ) {
485 @peritem = ( 'setup' ); # taxes only have setup
486 @peritem_desc = ( 'Tax charge' );
488 push @where, 'cust_bill_pkg.pkgnum = 0';
490 # tax location when using tax_rate_location
491 if ( scalar( grep( /locationtaxid/, $cgi->param ) ) ) {
493 $join_pkg .= ' LEFT JOIN cust_bill_pkg_tax_rate_location USING ( billpkgnum ) '.
494 ' LEFT JOIN tax_rate_location USING ( taxratelocationnum )';
495 push @where, FS::tax_rate_location->location_sql(
496 map { $_ => (scalar($cgi->param($_)) || '') }
497 qw( district city county state locationtaxid )
501 COALESCE(cust_bill_pkg_tax_rate_location.amount,
502 cust_bill_pkg.setup + cust_bill_pkg.recur)
505 } elsif ( $cgi->param('out') ) {
508 LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum)
510 push @where, 'cust_bill_pkg_tax_location.billpkgnum IS NULL';
512 # each billpkgnum should appear only once
513 $total[0] = 'COUNT(*)';
514 $total[1] = 'SUM(cust_bill_pkg.setup)';
516 } else { # not locationtaxid or 'out'--the normal case
519 LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum)
520 JOIN cust_main_county USING (taxnum)
523 # don't double-count the components of consolidated taxes
524 $total[0] = 'COUNT(DISTINCT cust_bill_pkg.billpkgnum)';
525 $total[1] = 'SUM(cust_bill_pkg_tax_location.amount)';
529 if ( $cgi->param('taxclassNULL') ) {
530 push @where, 'cust_main_county.taxclass IS NULL';
534 if ( $cgi->param('taxnameNULL') ) {
535 push @where, 'cust_main_county.taxname IS NULL OR '.
536 'cust_main_county.taxname = \'Tax\'';
537 } elsif ( $cgi->param('taxname') ) {
538 push @where, 'cust_main_county.taxname = '.
539 dbh->quote($cgi->param('taxname'));
543 if ( $cgi->param('taxnum') ) {
544 my $taxnum_in = join(',',
545 grep /^\d+$/, $cgi->param('taxnum')
547 push @where, "cust_main_county.taxnum IN ($taxnum_in)"
551 # report group (itemdesc)
552 if ( $cgi->param('report_group') =~ /^(=|!=) (.*)$/ ) {
553 my ( $group_op, $group_value ) = ( $1, $2 );
554 if ( $group_op eq '=' ) {
555 #push @where, 'itemdesc LIKE '. dbh->quote($group_value.'%');
556 push @where, 'itemdesc = '. dbh->quote($group_value);
557 } elsif ( $group_op eq '!=' ) {
558 push @where, '( itemdesc != '. dbh->quote($group_value) .' OR itemdesc IS NULL )';
560 die "guru meditation #00de: group_op $group_op\n";
564 # itemdesc, for some reason
565 if ( $cgi->param('itemdesc') ) {
566 if ( $cgi->param('itemdesc') eq 'Tax' ) {
567 push @where, "(itemdesc='Tax' OR itemdesc is null)";
569 push @where, 'itemdesc='. dbh->quote($cgi->param('itemdesc'));
577 my $pay_sub = "SELECT SUM(cust_bill_pay_pkg.amount)
578 FROM cust_bill_pay_pkg
579 WHERE cust_bill_pkg.billpkgnum = cust_bill_pay_pkg.billpkgnum
581 push @select, "($pay_sub) AS pay_amount";
585 if ( $cgi->param('credit') ) {
589 if ( $cgi->param('istax') ) {
590 # then we need to group/join by billpkgtaxlocationnum, to get only the
591 # relevant part of partial taxes
592 my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
593 reason.reason as reason_text, access_user.username AS username_text,
594 billpkgtaxlocationnum, billpkgnum
595 FROM cust_credit_bill_pkg
596 JOIN cust_credit_bill USING (creditbillnum)
597 JOIN cust_credit USING (crednum)
598 LEFT JOIN reason USING (reasonnum)
599 LEFT JOIN access_user USING (usernum)
600 GROUP BY billpkgnum, billpkgtaxlocationnum, reason.reason,
601 access_user.username";
603 if ( $cgi->param('out') ) {
605 # find credits that are applied to the line items, but not to
606 # a cust_bill_pkg_tax_location link
607 $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
609 push @where, 'item_credit.billpkgtaxlocationnum IS NULL';
613 # find credits that are applied to the CBPTL links that are
614 # considered "interesting" by the report criteria
615 $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
616 USING (billpkgtaxlocationnum)";
621 # then only group by billpkgnum
622 my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
623 reason.reason as reason_text, access_user.username AS username_text,
625 FROM cust_credit_bill_pkg
626 JOIN cust_credit_bill USING (creditbillnum)
627 JOIN cust_credit USING (crednum)
628 LEFT JOIN reason USING (reasonnum)
629 LEFT JOIN access_user USING (usernum)
630 GROUP BY billpkgnum, reason.reason, access_user.username";
631 $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit USING (billpkgnum)";
634 push @where, 'item_credit.billpkgnum IS NOT NULL';
635 push @select, 'item_credit.credit_amount',
636 'item_credit.username_text',
637 'item_credit.reason_text';
638 push @peritem, 'credit_amount', 'username_text', 'reason_text';
639 push @peritem_desc, 'Credited', 'By', 'Reason';
640 push @total, 'SUM(credit_amount)';
641 push @total_desc, "$money_char%.2f credited";
645 #still want a credit total column
648 SELECT SUM(cust_credit_bill_pkg.amount)
649 FROM cust_credit_bill_pkg
650 WHERE cust_bill_pkg.billpkgnum = cust_credit_bill_pkg.billpkgnum
652 push @select, "($credit_sub) AS credit_amount";
656 push @select, 'cust_main.custnum', FS::UI::Web::cust_sql_fields();
658 my $where = join(' AND ', @where);
659 $where &&= "WHERE $where";
662 'table' => 'cust_bill_pkg',
663 'addl_from' => "$join_pkg $join_cust",
665 'select' => join(",\n", @select ),
666 'extra_sql' => $where,
667 'order_by' => 'ORDER BY cust_bill._date, cust_bill_pkg.billpkgnum',
671 'SELECT ' . join(',', @total) .
672 " FROM cust_bill_pkg $join_pkg $join_cust
675 @peritem_desc = map {emt($_)} @peritem_desc;
676 my @peritem_sub = map {
678 if ($field =~ /_text$/) { # kludge for credit reason/username fields
679 sub {$_[0]->get($field)};
681 sub { sprintf($money_char.'%.2f', $_[0]->get($field)) }
684 my @peritem_null = map { '' } @peritem; # placeholders
685 my $peritem_align = 'r' x scalar(@peritem);
687 my $ilink = [ "${p}view/cust_bill.cgi?", 'invnum' ];
688 my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ];
690 my $pay_link = ''; #[, 'billpkgnum', ];
691 my $credit_link = [ "${p}search/cust_credit_bill_pkg.html?billpkgnum=", 'billpkgnum', ];
693 warn "\n\nQUERY:\n".Dumper($query)."\n\nCOUNT_QUERY:\n$count_query\n\n"
694 if $cgi->param('debug');