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 or a comma-separated list (where 0 is "no report class"), or the
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('salesnum') =~ /^(\d+)$/ ) {
267 my $cmp_salesnum = $cgi->param('cust_main_sales')
268 ? ' COALESCE( cust_pkg.salesnum, cust_main.salesnum )'
269 : ' cust_pkg.salesnum ';
271 push @where, "$cmp_salesnum = $salesnum";
273 $cgi->param('classnum', 0) unless $cgi->param('classnum');
277 if ( $cgi->param('refnum') =~ /^(\d+)$/ ) {
278 push @where, "cust_main.refnum = $1";
281 # 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)
282 if ( grep { $_ eq 'cust_classnum' } $cgi->param ) {
283 my @classnums = grep /^\d*$/, $cgi->param('cust_classnum');
284 push @where, 'COALESCE( cust_main.classnum, 0) IN ( '.
285 join(',', map { $_ || '0' } @classnums ).
292 if ( $cgi->param('custnum') =~ /^(\d+)$/ ) {
293 push @where, "cust_main.custnum = $1";
296 # we want the package and its definition if available
298 ' LEFT JOIN cust_pkg USING (pkgnum)
299 LEFT JOIN part_pkg USING (pkgpart)';
301 my $part_pkg = 'part_pkg';
302 if ( $cgi->param('use_override') ) { #"Separate sub-packages from parents"
303 # still need the real part_pkg for tax applicability,
305 $join_pkg .= " LEFT JOIN part_pkg AS override ON (
306 COALESCE(cust_bill_pkg.pkgpart_override, cust_pkg.pkgpart, 0) = override.pkgpart
308 $part_pkg = 'override';
310 push @select, 'part_pkg.pkgpart', 'part_pkg.pkg'; # or should this use override?
313 if ( $cgi->param('nottax') ) {
315 push @where, 'cust_bill_pkg.pkgnum > 0';
317 my @tax_where; # will go into a subquery
318 my @exempt_where; # will also go into a subquery
320 # classnum (of override pkgpart if applicable)
321 # not specified: all classes
324 if ( grep { $_ eq 'classnum' } $cgi->param ) {
325 my @classnums = grep /^\d+$/, $cgi->param('classnum');
326 push @where, "COALESCE($part_pkg.classnum, 0) IN ( ".
327 join(',', @classnums ).
332 if ( grep { $_ eq 'report_optionnum' } $cgi->param ) {
333 my @nums = grep /^\w+$/, $cgi->param('report_optionnum');
334 my $num = join(',', @nums);
335 push @where, # code reuse FTW
336 FS::Report::Table->with_report_option( $num, $cgi->param('use_override'));
339 if ( $cgi->param('report_optionnum') =~ /^(\w+)$/ ) {
344 if ( $cgi->param('taxclassNULL') ) {
345 # a little different from 'taxclass' in that it applies to the
346 # effective taxclass, not the real one
347 push @tax_where, 'cust_main_county.taxclass IS NULL'
348 } elsif ( $cgi->param('taxclass') ) {
349 push @tax_where, "$part_pkg.taxclass IN (" .
350 join(', ', map {dbh->quote($_)} $cgi->param('taxclass') ).
354 if ( $cgi->param('exempt_cust') eq 'Y' ) {
355 # tax-exempt customers
356 push @exempt_where, "(exempt_cust = 'Y' OR exempt_cust_taxname = 'Y')";
358 } elsif ( $cgi->param('exempt_pkg') eq 'Y' ) { # non-taxable package
359 # non-taxable package charges
360 push @exempt_where, "(exempt_setup = 'Y' OR exempt_recur = 'Y')";
362 # we don't handle exempt_monthly here
364 if ( $cgi->param('taxname') ) { # specific taxname
365 push @tax_where, 'cust_main_county.taxname = '.
366 dbh->quote($cgi->param('taxname'));
367 } elsif ( $cgi->param('taxnameNULL') ) {
368 push @tax_where, 'cust_main_county.taxname IS NULL OR '.
369 'cust_main_county.taxname = \'Tax\'';
372 # country:state:county:city:district (may be repeated)
373 # You can also pass a big list of taxnums but that leads to huge URLs.
374 # Note that this means "packages whose tax is in this region", not
375 # "packages in this region". It's meant for links from the tax report.
376 if ( $cgi->param('region') ) {
378 foreach ( $cgi->param('region') ) {
380 @loc{qw(country state county city district)} =
381 split(':', $cgi->param('region'));
382 my $string = join(' AND ',
385 "$_ = ".dbh->quote($loc{$_});
391 push @orwhere, "($string)";
393 push @tax_where, '(' . join(' OR ', @orwhere) . ')' if @orwhere;
397 if ( $cgi->param('taxnum') ) {
398 my $taxnum_in = join(',',
399 grep /^\d+$/, $cgi->param('taxnum')
401 push @tax_where, "cust_main_county.taxnum IN ($taxnum_in)"
405 # If we're showing exempt items, we need to find those with
406 # cust_tax_exempt_pkg records matching the selected taxes.
407 # If we're showing taxable items, we need to find those with
408 # cust_bill_pkg_tax_location records. We also need to find the
409 # exemption records so that we can show the taxable amount.
410 # If we're showing all items, we need the union of those.
411 # If we're showing 'out' (items that aren't region/class taxable),
412 # then we need the set of all items minus the union of those.
416 if ( @exempt_where or @tax_where
417 or $cgi->param('taxable') or $cgi->param('out') )
419 # process exemption restrictions, including @tax_where
420 my $exempt_sub = 'SELECT SUM(amount) as exempt_amount, billpkgnum
421 FROM cust_tax_exempt_pkg JOIN cust_main_county USING (taxnum)';
423 $exempt_sub .= ' WHERE '.join(' AND ', @tax_where, @exempt_where)
424 if (@tax_where or @exempt_where);
426 $exempt_sub .= ' GROUP BY billpkgnum';
428 $join_pkg .= " LEFT JOIN ($exempt_sub) AS item_exempt
432 if ( @tax_where or $cgi->param('taxable') or $cgi->param('out') ) {
433 # process tax restrictions
435 'cust_main_county.tax > 0';
437 my $tax_sub = "SELECT invnum, cust_bill_pkg_tax_location.pkgnum
438 FROM cust_bill_pkg_tax_location
439 JOIN cust_bill_pkg AS tax_item USING (billpkgnum)
440 JOIN cust_main_county USING (taxnum)
441 WHERE ". join(' AND ', @tax_where).
442 " GROUP BY invnum, cust_bill_pkg_tax_location.pkgnum";
444 $join_pkg .= " LEFT JOIN ($tax_sub) AS item_tax
445 ON (item_tax.invnum = cust_bill_pkg.invnum AND
446 item_tax.pkgnum = cust_bill_pkg.pkgnum)";
449 # now do something with that
450 if ( @exempt_where ) {
452 push @where, 'item_exempt.billpkgnum IS NOT NULL';
453 push @select, 'item_exempt.exempt_amount';
454 push @peritem, 'exempt_amount';
455 push @peritem_desc, 'Exempt';
456 push @total, 'SUM(exempt_amount)';
457 push @total_desc, "$money_char%.2f tax-exempt";
459 } elsif ( $cgi->param('taxable') ) {
461 my $taxable = 'cust_bill_pkg.setup + cust_bill_pkg.recur '.
462 '- COALESCE(item_exempt.exempt_amount, 0)';
464 push @where, 'item_tax.invnum IS NOT NULL';
465 push @select, "($taxable) AS taxable_amount";
466 push @peritem, 'taxable_amount';
467 push @peritem_desc, 'Taxable';
468 push @total, "SUM($taxable)";
469 push @total_desc, "$money_char%.2f taxable";
471 } elsif ( $cgi->param('out') ) {
473 push @where, 'item_tax.invnum IS NULL',
474 'item_exempt.billpkgnum IS NULL';
476 } elsif ( @tax_where ) {
478 # union of taxable + all exempt_ cases
480 '(item_tax.invnum IS NOT NULL OR item_exempt.billpkgnum IS NOT NULL)';
484 # recur/usage separation
485 if ( $cgi->param('usage') eq 'recurring' ) {
487 my $recur_no_usage = FS::cust_bill_pkg->charged_sql('', '', no_usage => 1);
488 push @select, "($recur_no_usage) AS recur_no_usage";
489 $peritem[1] = 'recur_no_usage';
490 $total[1] = "SUM(cust_bill_pkg.setup + $recur_no_usage)";
491 $total_desc[0] .= ' (excluding usage)';
493 } elsif ( $cgi->param('usage') eq 'usage' ) {
495 my $usage = FS::cust_bill_pkg->usage_sql();
496 push @select, "($usage) AS _usage";
497 # there's already a method named 'usage'
498 $peritem[1] = '_usage';
499 $peritem_desc[1] = 'Usage charge';
500 $total[1] = "SUM($usage)";
501 $total_desc[0] .= ' usage charges';
504 } elsif ( $cgi->param('istax') ) {
506 @peritem = ( 'setup' ); # taxes only have setup
507 @peritem_desc = ( 'Tax charge' );
509 push @where, 'cust_bill_pkg.pkgnum = 0';
511 # tax location when using tax_rate_location
512 if ( scalar( grep( /locationtaxid/, $cgi->param ) ) ) {
514 $join_pkg .= ' LEFT JOIN cust_bill_pkg_tax_rate_location USING ( billpkgnum ) '.
515 ' LEFT JOIN tax_rate_location USING ( taxratelocationnum )';
516 push @where, FS::tax_rate_location->location_sql(
517 map { $_ => (scalar($cgi->param($_)) || '') }
518 qw( district city county state locationtaxid )
522 COALESCE(cust_bill_pkg_tax_rate_location.amount,
523 cust_bill_pkg.setup + cust_bill_pkg.recur)
526 } elsif ( $cgi->param('out') ) {
529 LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum)
531 push @where, 'cust_bill_pkg_tax_location.billpkgnum IS NULL';
533 # each billpkgnum should appear only once
534 $total[0] = 'COUNT(*)';
535 $total[1] = 'SUM(cust_bill_pkg.setup)';
537 } else { # not locationtaxid or 'out'--the normal case
540 LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum)
541 JOIN cust_main_county USING (taxnum)
544 # don't double-count the components of consolidated taxes
545 $total[0] = 'COUNT(DISTINCT cust_bill_pkg.billpkgnum)';
546 $total[1] = 'SUM(cust_bill_pkg_tax_location.amount)';
550 if ( $cgi->param('taxclassNULL') ) {
551 push @where, 'cust_main_county.taxclass IS NULL';
555 if ( $cgi->param('taxnameNULL') ) {
556 push @where, 'cust_main_county.taxname IS NULL OR '.
557 'cust_main_county.taxname = \'Tax\'';
558 } elsif ( $cgi->param('taxname') ) {
559 push @where, 'cust_main_county.taxname = '.
560 dbh->quote($cgi->param('taxname'));
564 if ( $cgi->param('taxnum') ) {
565 my $taxnum_in = join(',',
566 grep /^\d+$/, $cgi->param('taxnum')
568 push @where, "cust_main_county.taxnum IN ($taxnum_in)"
572 # report group (itemdesc)
573 if ( $cgi->param('report_group') =~ /^(=|!=) (.*)$/ ) {
574 my ( $group_op, $group_value ) = ( $1, $2 );
575 if ( $group_op eq '=' ) {
576 #push @where, 'itemdesc LIKE '. dbh->quote($group_value.'%');
577 push @where, 'itemdesc = '. dbh->quote($group_value);
578 } elsif ( $group_op eq '!=' ) {
579 push @where, '( itemdesc != '. dbh->quote($group_value) .' OR itemdesc IS NULL )';
581 die "guru meditation #00de: group_op $group_op\n";
585 # itemdesc, for some reason
586 if ( $cgi->param('itemdesc') ) {
587 if ( $cgi->param('itemdesc') eq 'Tax' ) {
588 push @where, "(itemdesc='Tax' OR itemdesc is null)";
590 push @where, 'itemdesc='. dbh->quote($cgi->param('itemdesc'));
598 my $pay_sub = "SELECT SUM(cust_bill_pay_pkg.amount)
599 FROM cust_bill_pay_pkg
600 WHERE cust_bill_pkg.billpkgnum = cust_bill_pay_pkg.billpkgnum
602 push @select, "($pay_sub) AS pay_amount";
606 if ( $cgi->param('credit') ) {
610 if ( $cgi->param('istax') ) {
611 # then we need to group/join by billpkgtaxlocationnum, to get only the
612 # relevant part of partial taxes
613 my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
614 reason.reason as reason_text, access_user.username AS username_text,
615 billpkgtaxlocationnum, billpkgnum
616 FROM cust_credit_bill_pkg
617 JOIN cust_credit_bill USING (creditbillnum)
618 JOIN cust_credit USING (crednum)
619 LEFT JOIN reason USING (reasonnum)
620 LEFT JOIN access_user USING (usernum)
621 GROUP BY billpkgnum, billpkgtaxlocationnum, reason.reason,
622 access_user.username";
624 if ( $cgi->param('out') ) {
626 # find credits that are applied to the line items, but not to
627 # a cust_bill_pkg_tax_location link
628 $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
630 push @where, 'item_credit.billpkgtaxlocationnum IS NULL';
634 # find credits that are applied to the CBPTL links that are
635 # considered "interesting" by the report criteria
636 $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
637 USING (billpkgtaxlocationnum)";
642 # then only group by billpkgnum
643 my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
644 reason.reason as reason_text, access_user.username AS username_text,
646 FROM cust_credit_bill_pkg
647 JOIN cust_credit_bill USING (creditbillnum)
648 JOIN cust_credit USING (crednum)
649 LEFT JOIN reason USING (reasonnum)
650 LEFT JOIN access_user USING (usernum)
651 GROUP BY billpkgnum, reason.reason, access_user.username";
652 $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit USING (billpkgnum)";
655 push @where, 'item_credit.billpkgnum IS NOT NULL';
656 push @select, 'item_credit.credit_amount',
657 'item_credit.username_text',
658 'item_credit.reason_text';
659 push @peritem, 'credit_amount', 'username_text', 'reason_text';
660 push @peritem_desc, 'Credited', 'By', 'Reason';
661 push @total, 'SUM(credit_amount)';
662 push @total_desc, "$money_char%.2f credited";
666 #still want a credit total column
669 SELECT SUM(cust_credit_bill_pkg.amount)
670 FROM cust_credit_bill_pkg
671 WHERE cust_bill_pkg.billpkgnum = cust_credit_bill_pkg.billpkgnum
673 push @select, "($credit_sub) AS credit_amount";
677 push @select, 'cust_main.custnum', FS::UI::Web::cust_sql_fields();
679 my $where = join(' AND ', @where);
680 $where &&= "WHERE $where";
683 'table' => 'cust_bill_pkg',
684 'addl_from' => "$join_pkg $join_cust",
686 'select' => join(",\n", @select ),
687 'extra_sql' => $where,
688 'order_by' => 'ORDER BY cust_bill._date, cust_bill_pkg.billpkgnum',
692 'SELECT ' . join(',', @total) .
693 " FROM cust_bill_pkg $join_pkg $join_cust
696 @peritem_desc = map {emt($_)} @peritem_desc;
697 my @peritem_sub = map {
699 if ($field =~ /_text$/) { # kludge for credit reason/username fields
700 sub {$_[0]->get($field)};
702 sub { sprintf($money_char.'%.2f', $_[0]->get($field)) }
705 my @peritem_null = map { '' } @peritem; # placeholders
706 my $peritem_align = 'r' x scalar(@peritem);
708 my $ilink = [ "${p}view/cust_bill.cgi?", 'invnum' ];
709 my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ];
711 my $pay_link = ''; #[, 'billpkgnum', ];
712 my $credit_link = [ "${p}search/cust_credit_bill_pkg.html?billpkgnum=", 'billpkgnum', ];
714 warn "\n\nQUERY:\n".Dumper($query)."\n\nCOUNT_QUERY:\n$count_query\n\n"
715 if $cgi->param('debug');