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,
51 FS::UI::Web::cust_sort_fields(),
63 ( map { $_ ne 'Cust. Status' ? $clink : '' }
64 FS::UI::Web::cust_header()
67 #'align' => 'rlrrrc'.FS::UI::Web::cust_aligns(),
68 'align' => $pkgnum_align.
73 FS::UI::Web::cust_aligns(),
84 FS::UI::Web::cust_colors(),
96 FS::UI::Web::cust_styles(),
101 Output control parameters:
102 - distribute: Boolean. If true, recurring fees will be "prorated" for the
103 portion of the package date range (sdate-edate) that falls within the date
104 range of the report. Line items will be limited to those for which this
105 portion is > 0. This disables filtering on invoice date.
107 - usage: Separate usage (cust_bill_pkg_detail records) from
108 recurring charges. If set to "usage", will show usage instead of
109 recurring charges. If set to "recurring", will deduct usage and only
110 show the flat rate charge. If not passed, the "recurring charge" column
111 will include usage charges also.
113 Filtering parameters:
114 - begin, end: Date range. Applies to invoice date, not necessarily package
115 date range. But see "distribute".
117 - status: Customer status (active, suspended, etc.). This will filter on
118 _current_ customer status, not status at the time the invoice was generated.
120 - agentnum: Filter on customer agent.
122 - refnum: Filter on customer reference source.
124 - cust_classnum: Filter on customer class.
126 - classnum: Filter on package class.
128 - report_optionnum: Filter on package report class. Can be a single report
129 class number or a comma-separated list (where 0 is "no report class"), or the
132 - use_override: Apply "classnum" and "taxclass" filtering based on the
133 override (bundle) pkgpart, rather than always using the true pkgpart.
135 - nottax: Limit to items that are not taxes (pkgnum > 0).
137 - istax: Limit to items that are taxes (pkgnum == 0).
139 - taxnum: Limit to items whose tax definition matches this taxnum.
140 With "nottax" that means items that are subject to that tax;
141 with "istax" it's the tax charges themselves. Can be specified
142 more than once to include multiple taxes.
144 - country, state, county, city: Limit to items whose tax location
145 matches these fields. If "nottax" it's the tax location of the package;
146 if "istax" the location of the tax.
148 - taxname, taxnameNULL: With "nottax", limit to items whose tax location
149 matches a tax with this name. With "istax", limit to items that have
150 this tax name. taxnameNULL is equivalent to "taxname = '' OR taxname
153 - out: With "nottax", limit to items that don't match any tax definition.
154 With "istax", find tax items that are unlinked to their tax definitions.
155 Current Freeside (> July 2012) always creates tax links, but unlinked
156 items may result from an incomplete upgrade of legacy data.
158 - locationtaxid: With "nottax", limit to packages matching this
159 tax_rate_location ID; with "tax", limit to taxes generated from that
162 - taxclass: Filter on package taxclass.
164 - taxclassNULL: With "nottax", limit to items that would be subject to the
165 tax with taxclass = NULL. This doesn't necessarily mean part_pkg.taxclass
166 is NULL; it also includes taxclasses that don't have a tax in this region.
168 - itemdesc: Limit to line items with this description. Note that non-tax
169 packages usually have a description of NULL. (Deprecated.)
171 - report_group: Can contain '=' or '!=' followed by a string to limit to
172 line items where itemdesc starts with, or doesn't start with, the string.
174 - cust_tax: Limit to customers who are tax-exempt. If "taxname" is also
175 specified, limit to customers who are also specifically exempt from that
178 - pkg_tax: Limit to packages that are tax-exempt, and only include the
179 exempt portion (setup, recurring, or both) when calculating totals.
181 - taxable: Limit to packages that are subject to tax, i.e. where a
182 cust_bill_pkg_tax_location record exists.
184 - credit: Limit to line items that received a credit application. The
185 amount of the credit will also be shown.
190 my $curuser = $FS::CurrentUser::CurrentUser;
192 die "access denied" unless $curuser->access_right('Financial reports');
194 my $conf = new FS::Conf;
195 my $money_char = $conf->config('money_char') || '$';
197 my @select = ( 'cust_bill_pkg.*', 'cust_bill._date' );
198 my @total = ( 'COUNT(*)', 'SUM(cust_bill_pkg.setup + cust_bill_pkg.recur)');
199 my @total_desc = ( $money_char.'%.2f total' ); # sprintf strings
201 my @peritem = ( 'setup', 'recur' );
202 my @peritem_desc = ( 'Setup charge', 'Recurring charge' );
204 my @pkgnum_header = ();
207 my $pkgnum_align = '';
208 if ( $curuser->option('show_pkgnum') ) {
209 push @select, 'cust_bill_pkg.pkgnum';
210 push @pkgnum_header, 'Pkg Num';
211 push @pkgnum, sub { $_[0]->pkgnum > 0 ? $_[0]->pkgnum : '' };
212 push @pkgnum_null, '';
213 $pkgnum_align .= 'r';
216 my @post_desc_header = ();
218 my @post_desc_null = ();
219 my $post_desc_align = '';
220 if ( $conf->exists('enable_taxclasses') ) {
221 push @post_desc_header, 'Tax class';
222 push @post_desc, 'taxclass';
223 push @post_desc_null, '';
224 $post_desc_align .= 'l';
225 push @select, 'part_pkg.taxclass'; # or should this use override?
228 # valid in both the tax and non-tax cases
230 " LEFT JOIN cust_bill ON (cust_bill_pkg.invnum = cust_bill.invnum)".
231 # use cust_pkg.locationnum if it exists
232 FS::UI::Web::join_cust_main('cust_bill', 'cust_pkg');
234 #agent virtualization
236 $FS::CurrentUser::CurrentUser->agentnums_sql( 'table' => 'cust_main' );
238 my @where = ( $agentnums_sql );
241 my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
243 if ( $cgi->param('distribute') == 1 ) {
244 push @where, "sdate <= $ending",
245 "edate > $beginning",
249 push @where, "cust_bill._date >= $beginning",
250 "cust_bill._date <= $ending";
254 if ( $cgi->param('status') =~ /^([a-z]+)$/ ) {
255 push @where, FS::cust_main->cust_status_sql . " = '$1'";
259 if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
260 push @where, "cust_main.agentnum = $1";
263 # salesnum--see below
265 if ( $cgi->param('refnum') =~ /^(\d+)$/ ) {
266 push @where, "cust_main.refnum = $1";
269 # 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)
270 if ( grep { $_ eq 'cust_classnum' } $cgi->param ) {
271 my @classnums = grep /^\d*$/, $cgi->param('cust_classnum');
272 push @where, 'COALESCE( cust_main.classnum, 0) IN ( '.
273 join(',', map { $_ || '0' } @classnums ).
280 if ( $cgi->param('custnum') =~ /^(\d+)$/ ) {
281 push @where, "cust_main.custnum = $1";
284 # we want the package and its definition if available
286 ' LEFT JOIN cust_pkg USING (pkgnum)
287 LEFT JOIN part_pkg USING (pkgpart)';
289 my $part_pkg = 'part_pkg';
290 # "Separate sub-packages from parents"
291 my $use_override = $cgi->param('use_override') ? 1 : 0;
292 if ( $use_override ) {
293 # still need the real part_pkg for tax applicability,
295 $join_pkg .= " LEFT JOIN part_pkg AS override ON (
296 COALESCE(cust_bill_pkg.pkgpart_override, cust_pkg.pkgpart, 0) = override.pkgpart
298 $part_pkg = 'override';
300 push @select, 'part_pkg.pkgpart', 'part_pkg.pkg'; # or should this use override?
303 if ( $cgi->param('nottax') ) {
305 push @where, 'cust_bill_pkg.pkgnum > 0';
307 my @tax_where; # will go into a subquery
308 my @exempt_where; # will also go into a subquery
310 # classnum (of override pkgpart if applicable)
311 # not specified: all classes
314 if ( grep { $_ eq 'classnum' } $cgi->param ) {
315 my @classnums = grep /^\d+$/, $cgi->param('classnum');
316 push @where, "COALESCE($part_pkg.classnum, 0) IN ( ".
317 join(',', @classnums ).
322 if ( grep { $_ eq 'report_optionnum' } $cgi->param ) {
323 my $num = join(',', grep /^[\d,]+$/, $cgi->param('report_optionnum'));
324 my $not_num = join(',', grep /^[\d,]+$/, $cgi->param('not_report_optionnum'));
325 my $all = $cgi->param('all_report_options') ? 1 : 0;
326 push @where, # code reuse FTW
327 FS::Report::Table->with_report_option(
328 report_optionnum => $num,
329 not_report_optionnum => $not_num,
330 use_override => $use_override,
331 all_report_options => $all,
336 if ( $cgi->param('taxclassNULL') ) {
337 # a little different from 'taxclass' in that it applies to the
338 # effective taxclass, not the real one
339 push @tax_where, 'cust_main_county.taxclass IS NULL'
340 } elsif ( $cgi->param('taxclass') ) {
341 push @tax_where, "$part_pkg.taxclass IN (" .
342 join(', ', map {dbh->quote($_)} $cgi->param('taxclass') ).
346 if ( $cgi->param('exempt_cust') eq 'Y' ) {
347 # tax-exempt customers
348 push @exempt_where, "(exempt_cust = 'Y' OR exempt_cust_taxname = 'Y')";
350 } elsif ( $cgi->param('exempt_pkg') eq 'Y' ) { # non-taxable package
351 # non-taxable package charges
352 push @exempt_where, "(exempt_setup = 'Y' OR exempt_recur = 'Y')";
354 # we don't handle exempt_monthly here
356 if ( $cgi->param('taxname') ) { # specific taxname
357 push @tax_where, 'cust_main_county.taxname = '.
358 dbh->quote($cgi->param('taxname'));
359 } elsif ( $cgi->param('taxnameNULL') ) {
360 push @tax_where, 'cust_main_county.taxname IS NULL OR '.
361 'cust_main_county.taxname = \'Tax\'';
364 # country:state:county:city:district (may be repeated)
365 # You can also pass a big list of taxnums but that leads to huge URLs.
366 # Note that this means "packages whose tax is in this region", not
367 # "packages in this region". It's meant for links from the tax report.
368 if ( $cgi->param('region') ) {
370 foreach ( $cgi->param('region') ) {
372 @loc{qw(country state county city district)} =
373 split(':', $cgi->param('region'));
374 my $string = join(' AND ',
377 "$_ = ".dbh->quote($loc{$_});
383 push @orwhere, "($string)";
385 push @tax_where, '(' . join(' OR ', @orwhere) . ')' if @orwhere;
389 if ( $cgi->param('taxnum') ) {
390 my $taxnum_in = join(',',
391 grep /^\d+$/, $cgi->param('taxnum')
393 push @tax_where, "cust_main_county.taxnum IN ($taxnum_in)"
397 # If we're showing exempt items, we need to find those with
398 # cust_tax_exempt_pkg records matching the selected taxes.
399 # If we're showing taxable items, we need to find those with
400 # cust_bill_pkg_tax_location records. We also need to find the
401 # exemption records so that we can show the taxable amount.
402 # If we're showing all items, we need the union of those.
403 # If we're showing 'out' (items that aren't region/class taxable),
404 # then we need the set of all items minus the union of those.
408 if ( @exempt_where or @tax_where
409 or $cgi->param('taxable') or $cgi->param('out') )
411 # process exemption restrictions, including @tax_where
412 my $exempt_sub = 'SELECT SUM(amount) as exempt_amount, billpkgnum
413 FROM cust_tax_exempt_pkg JOIN cust_main_county USING (taxnum)';
415 $exempt_sub .= ' WHERE '.join(' AND ', @tax_where, @exempt_where)
416 if (@tax_where or @exempt_where);
418 $exempt_sub .= ' GROUP BY billpkgnum';
420 $join_pkg .= " LEFT JOIN ($exempt_sub) AS item_exempt
424 if ( @tax_where or $cgi->param('taxable') or $cgi->param('out') ) {
425 # process tax restrictions
427 'cust_main_county.tax > 0';
429 my $tax_sub = "SELECT invnum, cust_bill_pkg_tax_location.pkgnum
430 FROM cust_bill_pkg_tax_location
431 JOIN cust_bill_pkg AS tax_item USING (billpkgnum)
432 JOIN cust_main_county USING (taxnum)
433 WHERE ". join(' AND ', @tax_where).
434 " GROUP BY invnum, cust_bill_pkg_tax_location.pkgnum";
436 $join_pkg .= " LEFT JOIN ($tax_sub) AS item_tax
437 ON (item_tax.invnum = cust_bill_pkg.invnum AND
438 item_tax.pkgnum = cust_bill_pkg.pkgnum)";
441 # now do something with that
442 if ( @exempt_where ) {
444 push @where, 'item_exempt.billpkgnum IS NOT NULL';
445 push @select, 'item_exempt.exempt_amount';
446 push @peritem, 'exempt_amount';
447 push @peritem_desc, 'Exempt';
448 push @total, 'SUM(exempt_amount)';
449 push @total_desc, "$money_char%.2f tax-exempt";
451 } elsif ( $cgi->param('taxable') ) {
453 my $taxable = 'cust_bill_pkg.setup + cust_bill_pkg.recur '.
454 '- COALESCE(item_exempt.exempt_amount, 0)';
456 push @where, 'item_tax.invnum IS NOT NULL';
457 push @select, "($taxable) AS taxable_amount";
458 push @peritem, 'taxable_amount';
459 push @peritem_desc, 'Taxable';
460 push @total, "SUM($taxable)";
461 push @total_desc, "$money_char%.2f taxable";
463 } elsif ( $cgi->param('out') ) {
465 push @where, 'item_tax.invnum IS NULL',
466 'item_exempt.billpkgnum IS NULL';
468 } elsif ( @tax_where ) {
470 # union of taxable + all exempt_ cases
472 '(item_tax.invnum IS NOT NULL 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)';
542 if ( $cgi->param('taxclassNULL') ) {
543 push @where, 'cust_main_county.taxclass IS NULL';
547 if ( $cgi->param('taxnameNULL') ) {
548 push @where, 'cust_main_county.taxname IS NULL OR '.
549 'cust_main_county.taxname = \'Tax\'';
550 } elsif ( $cgi->param('taxname') ) {
551 push @where, 'cust_main_county.taxname = '.
552 dbh->quote($cgi->param('taxname'));
556 if ( $cgi->param('taxnum') ) {
557 my $taxnum_in = join(',',
558 grep /^\d+$/, $cgi->param('taxnum')
560 push @where, "cust_main_county.taxnum IN ($taxnum_in)"
564 # report group (itemdesc)
565 if ( $cgi->param('report_group') =~ /^(=|!=) (.*)$/ ) {
566 my ( $group_op, $group_value ) = ( $1, $2 );
567 if ( $group_op eq '=' ) {
568 #push @where, 'itemdesc LIKE '. dbh->quote($group_value.'%');
569 push @where, 'itemdesc = '. dbh->quote($group_value);
570 } elsif ( $group_op eq '!=' ) {
571 push @where, '( itemdesc != '. dbh->quote($group_value) .' OR itemdesc IS NULL )';
573 die "guru meditation #00de: group_op $group_op\n";
577 # itemdesc, for some reason
578 if ( $cgi->param('itemdesc') ) {
579 if ( $cgi->param('itemdesc') eq 'Tax' ) {
580 push @where, "(itemdesc='Tax' OR itemdesc is null)";
582 push @where, 'itemdesc='. dbh->quote($cgi->param('itemdesc'));
590 my $pay_sub = "SELECT SUM(cust_bill_pay_pkg.amount)
591 FROM cust_bill_pay_pkg
592 WHERE cust_bill_pkg.billpkgnum = cust_bill_pay_pkg.billpkgnum
594 push @select, "($pay_sub) AS pay_amount";
598 if ( $cgi->param('credit') ) {
602 if ( $cgi->param('istax') ) {
603 # then we need to group/join by billpkgtaxlocationnum, to get only the
604 # relevant part of partial taxes
605 my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
606 reason.reason as reason_text, access_user.username AS username_text,
607 billpkgtaxlocationnum, billpkgnum
608 FROM cust_credit_bill_pkg
609 JOIN cust_credit_bill USING (creditbillnum)
610 JOIN cust_credit USING (crednum)
611 LEFT JOIN reason USING (reasonnum)
612 LEFT JOIN access_user USING (usernum)
613 GROUP BY billpkgnum, billpkgtaxlocationnum, reason.reason,
614 access_user.username";
616 if ( $cgi->param('out') ) {
618 # find credits that are applied to the line items, but not to
619 # a cust_bill_pkg_tax_location link
620 $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
622 push @where, 'item_credit.billpkgtaxlocationnum IS NULL';
626 # find credits that are applied to the CBPTL links that are
627 # considered "interesting" by the report criteria
628 $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
629 USING (billpkgtaxlocationnum)";
634 # then only group by billpkgnum
635 my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
636 reason.reason as reason_text, access_user.username AS username_text,
638 FROM cust_credit_bill_pkg
639 JOIN cust_credit_bill USING (creditbillnum)
640 JOIN cust_credit USING (crednum)
641 LEFT JOIN reason USING (reasonnum)
642 LEFT JOIN access_user USING (usernum)
643 GROUP BY billpkgnum, reason.reason, access_user.username";
644 $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit USING (billpkgnum)";
647 push @where, 'item_credit.billpkgnum IS NOT NULL';
648 push @select, 'item_credit.credit_amount',
649 'item_credit.username_text',
650 'item_credit.reason_text';
651 push @peritem, 'credit_amount', 'username_text', 'reason_text';
652 push @peritem_desc, 'Credited', 'By', 'Reason';
653 push @total, 'SUM(credit_amount)';
654 push @total_desc, "$money_char%.2f credited";
658 #still want a credit total column
661 SELECT SUM(cust_credit_bill_pkg.amount)
662 FROM cust_credit_bill_pkg
663 WHERE cust_bill_pkg.billpkgnum = cust_credit_bill_pkg.billpkgnum
665 push @select, "($credit_sub) AS credit_amount";
669 push @select, 'cust_main.custnum', FS::UI::Web::cust_sql_fields();
672 if ( $cgi->param('salesnum') =~ /^(\d+)$/ ) {
675 my $sales = FS::sales->by_key($salesnum)
676 or die "salesnum $salesnum not found";
678 my $subsearch = $sales->cust_bill_pkg_search('', '',
679 'cust_main_sales' => ($cgi->param('cust_main_sales') ? 1 : 0),
680 'paid' => ($cgi->param('paid') ? 1 : 0),
681 'classnum' => scalar($cgi->param('classnum'))
683 $join_pkg .= " JOIN sales_pkg_class ON ( COALESCE(sales_pkg_class.classnum, 0) = COALESCE( part_pkg.classnum, 0) )";
685 my $extra_sql = $subsearch->{extra_sql};
686 $extra_sql =~ s/^WHERE//;
687 push @where, $extra_sql;
689 $cgi->param('classnum', 0) unless $cgi->param('classnum');
693 my $where = join(' AND ', @where);
694 $where &&= "WHERE $where";
697 'table' => 'cust_bill_pkg',
698 'addl_from' => "$join_pkg $join_cust",
700 'select' => join(",\n", @select ),
701 'extra_sql' => $where,
702 'order_by' => 'ORDER BY cust_bill._date, cust_bill_pkg.billpkgnum',
706 'SELECT ' . join(',', @total) .
707 " FROM cust_bill_pkg $join_pkg $join_cust
710 @peritem_desc = map {emt($_)} @peritem_desc;
711 my @peritem_sub = map {
713 if ($field =~ /_text$/) { # kludge for credit reason/username fields
714 sub {$_[0]->get($field)};
716 sub { sprintf($money_char.'%.2f', $_[0]->get($field)) }
719 my @peritem_null = map { '' } @peritem; # placeholders
720 my $peritem_align = 'r' x scalar(@peritem);
722 my $ilink = [ "${p}view/cust_bill.cgi?", 'invnum' ];
723 my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ];
725 my $pay_link = ''; #[, 'billpkgnum', ];
726 my $credit_link = [ "${p}search/cust_credit_bill_pkg.html?billpkgnum=", 'billpkgnum', ];
728 warn "\n\nQUERY:\n".Dumper($query)."\n\nCOUNT_QUERY:\n$count_query\n\n"
729 if $cgi->param('debug');