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 - use_override: Apply "classnum" and "taxclass" filtering based on the
128 override (bundle) pkgpart, rather than always using the true pkgpart.
130 - nottax: Limit to items that are not taxes (pkgnum > 0).
132 - istax: Limit to items that are taxes (pkgnum == 0).
134 - taxnum: Limit to items whose tax definition matches this taxnum.
135 With "nottax" that means items that are subject to that tax;
136 with "istax" it's the tax charges themselves. Can be specified
137 more than once to include multiple taxes.
139 - country, state, county, city: Limit to items whose tax location
140 matches these fields. If "nottax" it's the tax location of the package;
141 if "istax" the location of the tax.
143 - taxname, taxnameNULL: With "nottax", limit to items whose tax location
144 matches a tax with this name. With "istax", limit to items that have
145 this tax name. taxnameNULL is equivalent to "taxname = '' OR taxname
148 - out: With "nottax", limit to items that don't match any tax definition.
149 With "istax", find tax items that are unlinked to their tax definitions.
150 Current Freeside (> July 2012) always creates tax links, but unlinked
151 items may result from an incomplete upgrade of legacy data.
153 - locationtaxid: With "nottax", limit to packages matching this
154 tax_rate_location ID; with "tax", limit to taxes generated from that
157 - taxclass: Filter on package taxclass.
159 - taxclassNULL: With "nottax", limit to items that would be subject to the
160 tax with taxclass = NULL. This doesn't necessarily mean part_pkg.taxclass
161 is NULL; it also includes taxclasses that don't have a tax in this region.
163 - itemdesc: Limit to line items with this description. Note that non-tax
164 packages usually have a description of NULL. (Deprecated.)
166 - report_group: Can contain '=' or '!=' followed by a string to limit to
167 line items where itemdesc starts with, or doesn't start with, the string.
169 - cust_tax: Limit to customers who are tax-exempt. If "taxname" is also
170 specified, limit to customers who are also specifically exempt from that
173 - pkg_tax: Limit to packages that are tax-exempt, and only include the
174 exempt portion (setup, recurring, or both) when calculating totals.
176 - taxable: Limit to packages that are subject to tax, i.e. where a
177 cust_bill_pkg_tax_location record exists.
179 - credit: Limit to line items that received a credit application. The
180 amount of the credit will also be shown.
185 my $curuser = $FS::CurrentUser::CurrentUser;
187 die "access denied" unless $curuser->access_right('Financial reports');
189 my $conf = new FS::Conf;
190 my $money_char = $conf->config('money_char') || '$';
192 my @select = ( 'cust_bill_pkg.*', 'cust_bill._date' );
193 my @total = ( 'COUNT(*)', 'SUM(cust_bill_pkg.setup + cust_bill_pkg.recur)');
194 my @total_desc = ( $money_char.'%.2f total' ); # sprintf strings
196 my @peritem = ( 'setup', 'recur' );
197 my @peritem_desc = ( 'Setup charge', 'Recurring charge' );
199 my @pkgnum_header = ();
202 my $pkgnum_align = '';
203 if ( $curuser->option('show_pkgnum') ) {
204 push @select, 'cust_bill_pkg.pkgnum';
205 push @pkgnum_header, 'Pkg Num';
206 push @pkgnum, sub { $_[0]->pkgnum > 0 ? $_[0]->pkgnum : '' };
207 push @pkgnum_null, '';
208 $pkgnum_align .= 'r';
211 my @post_desc_header = ();
213 my @post_desc_null = ();
214 my $post_desc_align = '';
215 if ( $conf->exists('enable_taxclasses') ) {
216 push @post_desc_header, 'Tax class';
217 push @post_desc, 'taxclass';
218 push @post_desc_null, '';
219 $post_desc_align .= 'l';
220 push @select, 'part_pkg.taxclass'; # or should this use override?
223 # valid in both the tax and non-tax cases
225 " LEFT JOIN cust_bill USING (invnum)
226 LEFT JOIN cust_main USING (custnum)
229 #agent virtualization
231 $FS::CurrentUser::CurrentUser->agentnums_sql( 'table' => 'cust_main' );
233 my @where = ( $agentnums_sql );
236 my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
238 if ( $cgi->param('distribute') == 1 ) {
239 push @where, "sdate <= $ending",
240 "edate > $beginning",
244 push @where, "cust_bill._date >= $beginning",
245 "cust_bill._date <= $ending";
249 if ( $cgi->param('status') =~ /^([a-z]+)$/ ) {
250 push @where, FS::cust_main->cust_status_sql . " = '$1'";
254 if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
255 push @where, "cust_main.agentnum = $1";
259 if ( $cgi->param('refnum') =~ /^(\d+)$/ ) {
260 push @where, "cust_main.refnum = $1";
264 if ( $cgi->param('cust_classnum') ) {
265 my @classnums = grep /^\d+$/, $cgi->param('cust_classnum');
266 push @where, 'cust_main.classnum IN('.join(',',@classnums).')'
271 if ( $cgi->param('custnum') =~ /^(\d+)$/ ) {
272 push @where, "cust_main.custnum = $1";
275 # we want the package and its definition if available
277 ' LEFT JOIN cust_pkg USING (pkgnum)
278 LEFT JOIN part_pkg USING (pkgpart)';
280 my $part_pkg = 'part_pkg';
281 if ( $cgi->param('use_override') ) {
282 # still need the real part_pkg for tax applicability,
284 $join_pkg .= " LEFT JOIN part_pkg AS override ON (
285 COALESCE(cust_bill_pkg.pkgpart_override, cust_pkg.pkgpart, 0) = part_pkg.pkgpart
287 $part_pkg = 'override';
289 push @select, 'part_pkg.pkgpart', 'part_pkg.pkg'; # or should this use override?
292 if ( $cgi->param('nottax') ) {
294 push @where, 'cust_bill_pkg.pkgnum > 0';
296 my @tax_where; # will go into a subquery
297 my @exempt_where; # will also go into a subquery
299 # classnum (of override pkgpart if applicable)
300 # not specified: all classes
303 if ( $cgi->param('classnum') =~ /^(\d+)$/ ) {
304 push @where, "COALESCE($part_pkg.classnum, 0) = $1";
308 if ( $cgi->param('taxclassNULL') ) {
309 # a little different from 'taxclass' in that it applies to the
310 # effective taxclass, not the real one
311 push @tax_where, 'cust_main_county.taxclass IS NULL'
312 } elsif ( $cgi->param('taxclass') ) {
313 push @tax_where, "$part_pkg.taxclass IN (" .
314 join(', ', map {dbh->quote($_)} $cgi->param('taxclass') ).
318 if ( $cgi->param('exempt_cust') eq 'Y' ) {
319 # tax-exempt customers
320 push @exempt_where, "(exempt_cust = 'Y' OR exempt_cust_taxname = 'Y')";
322 } elsif ( $cgi->param('exempt_pkg') eq 'Y' ) { # non-taxable package
323 # non-taxable package charges
324 push @exempt_where, "(exempt_setup = 'Y' OR exempt_recur = 'Y')";
326 # we don't handle exempt_monthly here
328 if ( $cgi->param('taxname') ) { # specific taxname
329 push @tax_where, 'cust_main_county.taxname = '.
330 dbh->quote($cgi->param('taxname'));
331 } elsif ( $cgi->param('taxnameNULL') ) {
332 push @tax_where, 'cust_main_county.taxname IS NULL OR '.
333 'cust_main_county.taxname = \'Tax\'';
336 # country:state:county:city:district (may be repeated)
337 # You can also pass a big list of taxnums but that leads to huge URLs.
338 # Note that this means "packages whose tax is in this region", not
339 # "packages in this region". It's meant for links from the tax report.
340 if ( $cgi->param('region') ) {
342 foreach ( $cgi->param('region') ) {
344 @loc{qw(country state county city district)} =
345 split(':', $cgi->param('region'));
346 my $string = join(' AND ',
349 "$_ = ".dbh->quote($loc{$_});
355 push @orwhere, "($string)";
357 push @tax_where, '(' . join(' OR ', @orwhere) . ')' if @orwhere;
361 if ( $cgi->param('taxnum') ) {
362 my $taxnum_in = join(',',
363 grep /^\d+$/, $cgi->param('taxnum')
365 push @tax_where, "cust_main_county.taxnum IN ($taxnum_in)"
369 # If we're showing exempt items, we need to find those with
370 # cust_tax_exempt_pkg records matching the selected taxes.
371 # If we're showing taxable items, we need to find those with
372 # cust_bill_pkg_tax_location records. We also need to find the
373 # exemption records so that we can show the taxable amount.
374 # If we're showing all items, we need the union of those.
375 # If we're showing 'out' (items that aren't region/class taxable),
376 # then we need the set of all items minus the union of those.
380 if ( @exempt_where or @tax_where
381 or $cgi->param('taxable') or $cgi->param('out') )
383 # process exemption restrictions, including @tax_where
384 my $exempt_sub = 'SELECT SUM(amount) as exempt_amount, billpkgnum
385 FROM cust_tax_exempt_pkg JOIN cust_main_county USING (taxnum)';
387 $exempt_sub .= ' WHERE '.join(' AND ', @tax_where, @exempt_where)
388 if (@tax_where or @exempt_where);
390 $exempt_sub .= ' GROUP BY billpkgnum';
392 $join_pkg .= " LEFT JOIN ($exempt_sub) AS item_exempt
396 if ( @tax_where or $cgi->param('taxable') or $cgi->param('out') ) {
397 # process tax restrictions
399 'cust_main_county.tax > 0';
401 my $tax_sub = "SELECT invnum, cust_bill_pkg_tax_location.pkgnum
402 FROM cust_bill_pkg_tax_location
403 JOIN cust_bill_pkg AS tax_item USING (billpkgnum)
404 JOIN cust_main_county USING (taxnum)
405 WHERE ". join(' AND ', @tax_where).
406 " GROUP BY invnum, cust_bill_pkg_tax_location.pkgnum";
408 $join_pkg .= " LEFT JOIN ($tax_sub) AS item_tax
409 ON (item_tax.invnum = cust_bill_pkg.invnum AND
410 item_tax.pkgnum = cust_bill_pkg.pkgnum)";
413 # now do something with that
414 if ( @exempt_where ) {
416 push @where, 'item_exempt.billpkgnum IS NOT NULL';
417 push @select, 'item_exempt.exempt_amount';
418 push @peritem, 'exempt_amount';
419 push @peritem_desc, 'Exempt';
420 push @total, 'SUM(exempt_amount)';
421 push @total_desc, "$money_char%.2f tax-exempt";
423 } elsif ( $cgi->param('taxable') ) {
425 my $taxable = 'cust_bill_pkg.setup + cust_bill_pkg.recur '.
426 '- COALESCE(item_exempt.exempt_amount, 0)';
428 push @where, 'item_tax.invnum IS NOT NULL';
429 push @select, "($taxable) AS taxable_amount";
430 push @peritem, 'taxable_amount';
431 push @peritem_desc, 'Taxable';
432 push @total, "SUM($taxable)";
433 push @total_desc, "$money_char%.2f taxable";
435 } elsif ( $cgi->param('out') ) {
437 push @where, 'item_tax.invnum IS NULL',
438 'item_exempt.billpkgnum IS NULL';
440 } elsif ( @tax_where ) {
442 # union of taxable + all exempt_ cases
444 '(item_tax.invnum IS NOT NULL OR item_exempt.billpkgnum IS NOT NULL)';
448 # recur/usage separation
449 if ( $cgi->param('usage') eq 'recurring' ) {
451 my $recur_no_usage = FS::cust_bill_pkg->charged_sql('', '', no_usage => 1);
452 push @select, "($recur_no_usage) AS recur_no_usage";
453 $peritem[1] = 'recur_no_usage';
454 $total[1] = "SUM(cust_bill_pkg.setup + $recur_no_usage)";
455 $total_desc[0] .= ' (excluding usage)';
457 } elsif ( $cgi->param('usage') eq 'usage' ) {
459 my $usage = FS::cust_bill_pkg->usage_sql();
460 push @select, "($usage) AS _usage";
461 # there's already a method named 'usage'
462 $peritem[1] = '_usage';
463 $peritem_desc[1] = 'Usage charge';
464 $total[1] = "SUM($usage)";
465 $total_desc[0] .= ' usage charges';
468 } elsif ( $cgi->param('istax') ) {
470 @peritem = ( 'setup' ); # taxes only have setup
471 @peritem_desc = ( 'Tax charge' );
473 push @where, 'cust_bill_pkg.pkgnum = 0';
475 # tax location when using tax_rate_location
476 if ( scalar( grep( /locationtaxid/, $cgi->param ) ) ) {
478 $join_pkg .= ' LEFT JOIN cust_bill_pkg_tax_rate_location USING ( billpkgnum ) '.
479 ' LEFT JOIN tax_rate_location USING ( taxratelocationnum )';
480 push @where, FS::tax_rate_location->location_sql(
481 map { $_ => (scalar($cgi->param($_)) || '') }
482 qw( district city county state locationtaxid )
486 COALESCE(cust_bill_pkg_tax_rate_location.amount,
487 cust_bill_pkg.setup + cust_bill_pkg.recur)
490 } elsif ( $cgi->param('out') ) {
493 LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum)
495 push @where, 'cust_bill_pkg_tax_location.billpkgnum IS NULL';
497 # each billpkgnum should appear only once
498 $total[0] = 'COUNT(*)';
499 $total[1] = 'SUM(cust_bill_pkg.setup)';
501 } else { # not locationtaxid or 'out'--the normal case
504 LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum)
505 JOIN cust_main_county USING (taxnum)
508 # don't double-count the components of consolidated taxes
509 $total[0] = 'COUNT(DISTINCT cust_bill_pkg.billpkgnum)';
510 $total[1] = 'SUM(cust_bill_pkg_tax_location.amount)';
514 if ( $cgi->param('taxclassNULL') ) {
515 push @where, 'cust_main_county.taxclass IS NULL';
519 if ( $cgi->param('taxnameNULL') ) {
520 push @where, 'cust_main_county.taxname IS NULL OR '.
521 'cust_main_county.taxname = \'Tax\'';
522 } elsif ( $cgi->param('taxname') ) {
523 push @where, 'cust_main_county.taxname = '.
524 dbh->quote($cgi->param('taxname'));
528 if ( $cgi->param('taxnum') ) {
529 my $taxnum_in = join(',',
530 grep /^\d+$/, $cgi->param('taxnum')
532 push @where, "cust_main_county.taxnum IN ($taxnum_in)"
536 # report group (itemdesc)
537 if ( $cgi->param('report_group') =~ /^(=|!=) (.*)$/ ) {
538 my ( $group_op, $group_value ) = ( $1, $2 );
539 if ( $group_op eq '=' ) {
540 #push @where, 'itemdesc LIKE '. dbh->quote($group_value.'%');
541 push @where, 'itemdesc = '. dbh->quote($group_value);
542 } elsif ( $group_op eq '!=' ) {
543 push @where, '( itemdesc != '. dbh->quote($group_value) .' OR itemdesc IS NULL )';
545 die "guru meditation #00de: group_op $group_op\n";
549 # itemdesc, for some reason
550 if ( $cgi->param('itemdesc') ) {
551 if ( $cgi->param('itemdesc') eq 'Tax' ) {
552 push @where, "(itemdesc='Tax' OR itemdesc is null)";
554 push @where, 'itemdesc='. dbh->quote($cgi->param('itemdesc'));
562 my $pay_sub = "SELECT SUM(cust_bill_pay_pkg.amount) AS pay_amount,
564 FROM cust_bill_pay_pkg
565 GROUP BY billpkgnum";
566 $join_pkg .= " LEFT JOIN ($pay_sub) AS item_pay USING (billpkgnum)";
567 push @select, 'item_pay.pay_amount';
571 if ( $cgi->param('credit') ) {
575 if ( $cgi->param('istax') ) {
576 # then we need to group/join by billpkgtaxlocationnum, to get only the
577 # relevant part of partial taxes
578 my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
579 reason.reason as reason_text, access_user.username AS username_text,
580 billpkgtaxlocationnum, billpkgnum
581 FROM cust_credit_bill_pkg
582 JOIN cust_credit_bill USING (creditbillnum)
583 JOIN cust_credit USING (crednum)
584 LEFT JOIN reason USING (reasonnum)
585 LEFT JOIN access_user USING (usernum)
586 GROUP BY billpkgnum, billpkgtaxlocationnum, reason.reason,
587 access_user.username";
589 if ( $cgi->param('out') ) {
591 # find credits that are applied to the line items, but not to
592 # a cust_bill_pkg_tax_location link
593 $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
595 push @where, 'item_credit.billpkgtaxlocationnum IS NULL';
599 # find credits that are applied to the CBPTL links that are
600 # considered "interesting" by the report criteria
601 $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
602 USING (billpkgtaxlocationnum)";
607 # then only group by billpkgnum
608 my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
609 reason.reason as reason_text, access_user.username AS username_text,
611 FROM cust_credit_bill_pkg
612 JOIN cust_credit_bill USING (creditbillnum)
613 JOIN cust_credit USING (crednum)
614 LEFT JOIN reason USING (reasonnum)
615 LEFT JOIN access_user USING (usernum)
616 GROUP BY billpkgnum, reason.reason, access_user.username";
617 $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit USING (billpkgnum)";
620 push @where, 'item_credit.billpkgnum IS NOT NULL';
621 push @select, 'item_credit.credit_amount',
622 'item_credit.username_text',
623 'item_credit.reason_text';
624 push @peritem, 'credit_amount', 'username_text', 'reason_text';
625 push @peritem_desc, 'Credited', 'By', 'Reason';
626 push @total, 'SUM(credit_amount)';
627 push @total_desc, "$money_char%.2f credited";
631 #still want a credit total column
633 my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
635 FROM cust_credit_bill_pkg
636 GROUP BY billpkgnum";
637 $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit USING (billpkgnum)";
639 push @select, 'item_credit.credit_amount';
643 push @select, 'cust_main.custnum', FS::UI::Web::cust_sql_fields();
645 my $where = join(' AND ', @where);
646 $where &&= "WHERE $where";
649 'table' => 'cust_bill_pkg',
650 'addl_from' => "$join_cust $join_pkg",
652 'select' => join(",\n", @select ),
653 'extra_sql' => $where,
654 'order_by' => 'ORDER BY cust_bill._date, cust_bill_pkg.billpkgnum',
658 'SELECT ' . join(',', @total) .
659 " FROM cust_bill_pkg $join_cust $join_pkg
662 @peritem_desc = map {emt($_)} @peritem_desc;
663 my @peritem_sub = map {
665 if ($field =~ /_text$/) { # kludge for credit reason/username fields
666 sub {$_[0]->get($field)};
668 sub { sprintf($money_char.'%.2f', $_[0]->get($field)) }
671 my @peritem_null = map { '' } @peritem; # placeholders
672 my $peritem_align = 'r' x scalar(@peritem);
674 my $ilink = [ "${p}view/cust_bill.cgi?", 'invnum' ];
675 my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ];
677 my $pay_link = ''; #[, 'billpkgnum', ];
678 my $credit_link = [ "${p}search/cust_credit_bill_pkg.html?billpkgnum=", 'billpkgnum', ];
680 warn "\n\nQUERY:\n".Dumper($query)."\n\nCOUNT_QUERY:\n$count_query\n\n"
681 if $cgi->param('debug');