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";
262 # salesnum--see below
264 if ( $cgi->param('refnum') =~ /^(\d+)$/ ) {
265 push @where, "cust_main.refnum = $1";
268 # 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)
269 if ( grep { $_ eq 'cust_classnum' } $cgi->param ) {
270 my @classnums = grep /^\d*$/, $cgi->param('cust_classnum');
271 push @where, 'COALESCE( cust_main.classnum, 0) IN ( '.
272 join(',', map { $_ || '0' } @classnums ).
279 if ( $cgi->param('custnum') =~ /^(\d+)$/ ) {
280 push @where, "cust_main.custnum = $1";
283 # we want the package and its definition if available
285 ' LEFT JOIN cust_pkg USING (pkgnum)
286 LEFT JOIN part_pkg USING (pkgpart)';
288 my $part_pkg = 'part_pkg';
289 if ( $cgi->param('use_override') ) { #"Separate sub-packages from parents"
290 # still need the real part_pkg for tax applicability,
292 $join_pkg .= " LEFT JOIN part_pkg AS override ON (
293 COALESCE(cust_bill_pkg.pkgpart_override, cust_pkg.pkgpart, 0) = override.pkgpart
295 $part_pkg = 'override';
297 push @select, 'part_pkg.pkgpart', 'part_pkg.pkg'; # or should this use override?
300 if ( $cgi->param('nottax') ) {
302 push @where, 'cust_bill_pkg.pkgnum > 0';
304 my @tax_where; # will go into a subquery
305 my @exempt_where; # will also go into a subquery
307 # classnum (of override pkgpart if applicable)
308 # not specified: all classes
311 if ( grep { $_ eq 'classnum' } $cgi->param ) {
312 my @classnums = grep /^\d+$/, $cgi->param('classnum');
313 push @where, "COALESCE($part_pkg.classnum, 0) IN ( ".
314 join(',', @classnums ).
319 if ( grep { $_ eq 'report_optionnum' } $cgi->param ) {
320 my @nums = grep /^\w+$/, $cgi->param('report_optionnum');
321 my $num = join(',', @nums);
322 push @where, # code reuse FTW
323 FS::Report::Table->with_report_option( $num, $cgi->param('use_override'));
326 if ( $cgi->param('report_optionnum') =~ /^(\w+)$/ ) {
331 if ( $cgi->param('taxclassNULL') ) {
332 # a little different from 'taxclass' in that it applies to the
333 # effective taxclass, not the real one
334 push @tax_where, 'cust_main_county.taxclass IS NULL'
335 } elsif ( $cgi->param('taxclass') ) {
336 push @tax_where, "$part_pkg.taxclass IN (" .
337 join(', ', map {dbh->quote($_)} $cgi->param('taxclass') ).
341 if ( $cgi->param('exempt_cust') eq 'Y' ) {
342 # tax-exempt customers
343 push @exempt_where, "(exempt_cust = 'Y' OR exempt_cust_taxname = 'Y')";
345 } elsif ( $cgi->param('exempt_pkg') eq 'Y' ) { # non-taxable package
346 # non-taxable package charges
347 push @exempt_where, "(exempt_setup = 'Y' OR exempt_recur = 'Y')";
349 # we don't handle exempt_monthly here
351 if ( $cgi->param('taxname') ) { # specific taxname
352 push @tax_where, 'cust_main_county.taxname = '.
353 dbh->quote($cgi->param('taxname'));
354 } elsif ( $cgi->param('taxnameNULL') ) {
355 push @tax_where, 'cust_main_county.taxname IS NULL OR '.
356 'cust_main_county.taxname = \'Tax\'';
359 # country:state:county:city:district (may be repeated)
360 # You can also pass a big list of taxnums but that leads to huge URLs.
361 # Note that this means "packages whose tax is in this region", not
362 # "packages in this region". It's meant for links from the tax report.
363 if ( $cgi->param('region') ) {
365 foreach ( $cgi->param('region') ) {
367 @loc{qw(country state county city district)} =
368 split(':', $cgi->param('region'));
369 my $string = join(' AND ',
372 "$_ = ".dbh->quote($loc{$_});
378 push @orwhere, "($string)";
380 push @tax_where, '(' . join(' OR ', @orwhere) . ')' if @orwhere;
384 if ( $cgi->param('taxnum') ) {
385 my $taxnum_in = join(',',
386 grep /^\d+$/, $cgi->param('taxnum')
388 push @tax_where, "cust_main_county.taxnum IN ($taxnum_in)"
392 # If we're showing exempt items, we need to find those with
393 # cust_tax_exempt_pkg records matching the selected taxes.
394 # If we're showing taxable items, we need to find those with
395 # cust_bill_pkg_tax_location records. We also need to find the
396 # exemption records so that we can show the taxable amount.
397 # If we're showing all items, we need the union of those.
398 # If we're showing 'out' (items that aren't region/class taxable),
399 # then we need the set of all items minus the union of those.
403 if ( @exempt_where or @tax_where
404 or $cgi->param('taxable') or $cgi->param('out') )
406 # process exemption restrictions, including @tax_where
407 my $exempt_sub = 'SELECT SUM(amount) as exempt_amount, billpkgnum
408 FROM cust_tax_exempt_pkg JOIN cust_main_county USING (taxnum)';
410 $exempt_sub .= ' WHERE '.join(' AND ', @tax_where, @exempt_where)
411 if (@tax_where or @exempt_where);
413 $exempt_sub .= ' GROUP BY billpkgnum';
415 $join_pkg .= " LEFT JOIN ($exempt_sub) AS item_exempt
419 if ( @tax_where or $cgi->param('taxable') or $cgi->param('out') ) {
420 # process tax restrictions
422 'cust_main_county.tax > 0';
424 my $tax_sub = "SELECT invnum, cust_bill_pkg_tax_location.pkgnum
425 FROM cust_bill_pkg_tax_location
426 JOIN cust_bill_pkg AS tax_item USING (billpkgnum)
427 JOIN cust_main_county USING (taxnum)
428 WHERE ". join(' AND ', @tax_where).
429 " GROUP BY invnum, cust_bill_pkg_tax_location.pkgnum";
431 $join_pkg .= " LEFT JOIN ($tax_sub) AS item_tax
432 ON (item_tax.invnum = cust_bill_pkg.invnum AND
433 item_tax.pkgnum = cust_bill_pkg.pkgnum)";
436 # now do something with that
437 if ( @exempt_where ) {
439 push @where, 'item_exempt.billpkgnum IS NOT NULL';
440 push @select, 'item_exempt.exempt_amount';
441 push @peritem, 'exempt_amount';
442 push @peritem_desc, 'Exempt';
443 push @total, 'SUM(exempt_amount)';
444 push @total_desc, "$money_char%.2f tax-exempt";
446 } elsif ( $cgi->param('taxable') ) {
448 my $taxable = 'cust_bill_pkg.setup + cust_bill_pkg.recur '.
449 '- COALESCE(item_exempt.exempt_amount, 0)';
451 push @where, 'item_tax.invnum IS NOT NULL';
452 push @select, "($taxable) AS taxable_amount";
453 push @peritem, 'taxable_amount';
454 push @peritem_desc, 'Taxable';
455 push @total, "SUM($taxable)";
456 push @total_desc, "$money_char%.2f taxable";
458 } elsif ( $cgi->param('out') ) {
460 push @where, 'item_tax.invnum IS NULL',
461 'item_exempt.billpkgnum IS NULL';
463 } elsif ( @tax_where ) {
465 # union of taxable + all exempt_ cases
467 '(item_tax.invnum IS NOT NULL OR item_exempt.billpkgnum IS NOT NULL)';
471 # recur/usage separation
472 if ( $cgi->param('usage') eq 'recurring' ) {
474 my $recur_no_usage = FS::cust_bill_pkg->charged_sql('', '', no_usage => 1);
475 push @select, "($recur_no_usage) AS recur_no_usage";
476 $peritem[1] = 'recur_no_usage';
477 $total[1] = "SUM(cust_bill_pkg.setup + $recur_no_usage)";
478 $total_desc[0] .= ' (excluding usage)';
480 } elsif ( $cgi->param('usage') eq 'usage' ) {
482 my $usage = FS::cust_bill_pkg->usage_sql();
483 push @select, "($usage) AS _usage";
484 # there's already a method named 'usage'
485 $peritem[1] = '_usage';
486 $peritem_desc[1] = 'Usage charge';
487 $total[1] = "SUM($usage)";
488 $total_desc[0] .= ' usage charges';
491 } elsif ( $cgi->param('istax') ) {
493 @peritem = ( 'setup' ); # taxes only have setup
494 @peritem_desc = ( 'Tax charge' );
496 push @where, 'cust_bill_pkg.pkgnum = 0';
498 # tax location when using tax_rate_location
499 if ( scalar( grep( /locationtaxid/, $cgi->param ) ) ) {
501 $join_pkg .= ' LEFT JOIN cust_bill_pkg_tax_rate_location USING ( billpkgnum ) '.
502 ' LEFT JOIN tax_rate_location USING ( taxratelocationnum )';
503 push @where, FS::tax_rate_location->location_sql(
504 map { $_ => (scalar($cgi->param($_)) || '') }
505 qw( district city county state locationtaxid )
509 COALESCE(cust_bill_pkg_tax_rate_location.amount,
510 cust_bill_pkg.setup + cust_bill_pkg.recur)
513 } elsif ( $cgi->param('out') ) {
516 LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum)
518 push @where, 'cust_bill_pkg_tax_location.billpkgnum IS NULL';
520 # each billpkgnum should appear only once
521 $total[0] = 'COUNT(*)';
522 $total[1] = 'SUM(cust_bill_pkg.setup)';
524 } else { # not locationtaxid or 'out'--the normal case
527 LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum)
528 JOIN cust_main_county USING (taxnum)
531 # don't double-count the components of consolidated taxes
532 $total[0] = 'COUNT(DISTINCT cust_bill_pkg.billpkgnum)';
533 $total[1] = 'SUM(cust_bill_pkg_tax_location.amount)';
537 if ( $cgi->param('taxclassNULL') ) {
538 push @where, 'cust_main_county.taxclass IS NULL';
542 if ( $cgi->param('taxnameNULL') ) {
543 push @where, 'cust_main_county.taxname IS NULL OR '.
544 'cust_main_county.taxname = \'Tax\'';
545 } elsif ( $cgi->param('taxname') ) {
546 push @where, 'cust_main_county.taxname = '.
547 dbh->quote($cgi->param('taxname'));
551 if ( $cgi->param('taxnum') ) {
552 my $taxnum_in = join(',',
553 grep /^\d+$/, $cgi->param('taxnum')
555 push @where, "cust_main_county.taxnum IN ($taxnum_in)"
559 # report group (itemdesc)
560 if ( $cgi->param('report_group') =~ /^(=|!=) (.*)$/ ) {
561 my ( $group_op, $group_value ) = ( $1, $2 );
562 if ( $group_op eq '=' ) {
563 #push @where, 'itemdesc LIKE '. dbh->quote($group_value.'%');
564 push @where, 'itemdesc = '. dbh->quote($group_value);
565 } elsif ( $group_op eq '!=' ) {
566 push @where, '( itemdesc != '. dbh->quote($group_value) .' OR itemdesc IS NULL )';
568 die "guru meditation #00de: group_op $group_op\n";
572 # itemdesc, for some reason
573 if ( $cgi->param('itemdesc') ) {
574 if ( $cgi->param('itemdesc') eq 'Tax' ) {
575 push @where, "(itemdesc='Tax' OR itemdesc is null)";
577 push @where, 'itemdesc='. dbh->quote($cgi->param('itemdesc'));
585 my $pay_sub = "SELECT SUM(cust_bill_pay_pkg.amount)
586 FROM cust_bill_pay_pkg
587 WHERE cust_bill_pkg.billpkgnum = cust_bill_pay_pkg.billpkgnum
589 push @select, "($pay_sub) AS pay_amount";
593 if ( $cgi->param('credit') ) {
597 if ( $cgi->param('istax') ) {
598 # then we need to group/join by billpkgtaxlocationnum, to get only the
599 # relevant part of partial taxes
600 my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
601 reason.reason as reason_text, access_user.username AS username_text,
602 billpkgtaxlocationnum, billpkgnum
603 FROM cust_credit_bill_pkg
604 JOIN cust_credit_bill USING (creditbillnum)
605 JOIN cust_credit USING (crednum)
606 LEFT JOIN reason USING (reasonnum)
607 LEFT JOIN access_user USING (usernum)
608 GROUP BY billpkgnum, billpkgtaxlocationnum, reason.reason,
609 access_user.username";
611 if ( $cgi->param('out') ) {
613 # find credits that are applied to the line items, but not to
614 # a cust_bill_pkg_tax_location link
615 $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
617 push @where, 'item_credit.billpkgtaxlocationnum IS NULL';
621 # find credits that are applied to the CBPTL links that are
622 # considered "interesting" by the report criteria
623 $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
624 USING (billpkgtaxlocationnum)";
629 # then only group by billpkgnum
630 my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
631 reason.reason as reason_text, access_user.username AS username_text,
633 FROM cust_credit_bill_pkg
634 JOIN cust_credit_bill USING (creditbillnum)
635 JOIN cust_credit USING (crednum)
636 LEFT JOIN reason USING (reasonnum)
637 LEFT JOIN access_user USING (usernum)
638 GROUP BY billpkgnum, reason.reason, access_user.username";
639 $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit USING (billpkgnum)";
642 push @where, 'item_credit.billpkgnum IS NOT NULL';
643 push @select, 'item_credit.credit_amount',
644 'item_credit.username_text',
645 'item_credit.reason_text';
646 push @peritem, 'credit_amount', 'username_text', 'reason_text';
647 push @peritem_desc, 'Credited', 'By', 'Reason';
648 push @total, 'SUM(credit_amount)';
649 push @total_desc, "$money_char%.2f credited";
653 #still want a credit total column
656 SELECT SUM(cust_credit_bill_pkg.amount)
657 FROM cust_credit_bill_pkg
658 WHERE cust_bill_pkg.billpkgnum = cust_credit_bill_pkg.billpkgnum
660 push @select, "($credit_sub) AS credit_amount";
664 push @select, 'cust_main.custnum', FS::UI::Web::cust_sql_fields();
667 if ( $cgi->param('salesnum') =~ /^(\d+)$/ ) {
670 my $sales = FS::sales->by_key($salesnum)
671 or die "salesnum $salesnum not found";
673 my $subsearch = $sales->cust_bill_pkg_search('', '',
674 'cust_main_sales' => ($cgi->param('cust_main_sales') ? 1 : 0),
675 'paid' => ($cgi->param('paid') ? 1 : 0),
676 'classnum' => scalar($cgi->param('classnum'))
678 $join_pkg .= " JOIN sales_pkg_class ON ( COALESCE(sales_pkg_class.classnum, 0) = COALESCE( part_pkg.classnum, 0) )";
680 my $extra_sql = $subsearch->{extra_sql};
681 $extra_sql =~ s/^WHERE//;
682 push @where, $extra_sql;
684 $cgi->param('classnum', 0) unless $cgi->param('classnum');
688 my $where = join(' AND ', @where);
689 $where &&= "WHERE $where";
692 'table' => 'cust_bill_pkg',
693 'addl_from' => "$join_pkg $join_cust",
695 'select' => join(",\n", @select ),
696 'extra_sql' => $where,
697 'order_by' => 'ORDER BY cust_bill._date, cust_bill_pkg.billpkgnum',
701 'SELECT ' . join(',', @total) .
702 " FROM cust_bill_pkg $join_pkg $join_cust
705 @peritem_desc = map {emt($_)} @peritem_desc;
706 my @peritem_sub = map {
708 if ($field =~ /_text$/) { # kludge for credit reason/username fields
709 sub {$_[0]->get($field)};
711 sub { sprintf($money_char.'%.2f', $_[0]->get($field)) }
714 my @peritem_null = map { '' } @peritem; # placeholders
715 my $peritem_align = 'r' x scalar(@peritem);
717 my $ilink = [ "${p}view/cust_bill.cgi?", 'invnum' ];
718 my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ];
720 my $pay_link = ''; #[, 'billpkgnum', ];
721 my $credit_link = [ "${p}search/cust_credit_bill_pkg.html?billpkgnum=", 'billpkgnum', ];
723 warn "\n\nQUERY:\n".Dumper($query)."\n\nCOUNT_QUERY:\n$count_query\n\n"
724 if $cgi->param('debug');