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 - classnum: Filter on package class.
125 - use_override: Apply "classnum" and "taxclass" filtering based on the
126 override (bundle) pkgpart, rather than always using the true pkgpart.
128 - nottax: Limit to items that are not taxes (pkgnum > 0).
130 - istax: Limit to items that are taxes (pkgnum == 0).
132 - taxnum: Limit to items whose tax definition matches this taxnum.
133 With "nottax" that means items that are subject to that tax;
134 with "istax" it's the tax charges themselves. Can be specified
135 more than once to include multiple taxes.
137 - country, state, county, city: Limit to items whose tax location
138 matches these fields. If "nottax" it's the tax location of the package;
139 if "istax" the location of the tax.
141 - taxname, taxnameNULL: With "nottax", limit to items whose tax location
142 matches a tax with this name. With "istax", limit to items that have
143 this tax name. taxnameNULL is equivalent to "taxname = '' OR taxname
146 - out: With "nottax", limit to items that don't match any tax definition.
147 With "istax", find tax items that are unlinked to their tax definitions.
148 Current Freeside (> July 2012) always creates tax links, but unlinked
149 items may result from an incomplete upgrade of legacy data.
151 - locationtaxid: With "nottax", limit to packages matching this
152 tax_rate_location ID; with "tax", limit to taxes generated from that
155 - taxclass: Filter on package taxclass.
157 - taxclassNULL: With "nottax", limit to items that would be subject to the
158 tax with taxclass = NULL. This doesn't necessarily mean part_pkg.taxclass
159 is NULL; it also includes taxclasses that don't have a tax in this region.
161 - itemdesc: Limit to line items with this description. Note that non-tax
162 packages usually have a description of NULL. (Deprecated.)
164 - report_group: Can contain '=' or '!=' followed by a string to limit to
165 line items where itemdesc starts with, or doesn't start with, the string.
167 - cust_tax: Limit to customers who are tax-exempt. If "taxname" is also
168 specified, limit to customers who are also specifically exempt from that
171 - pkg_tax: Limit to packages that are tax-exempt, and only include the
172 exempt portion (setup, recurring, or both) when calculating totals.
174 - taxable: Limit to packages that are subject to tax, i.e. where a
175 cust_bill_pkg_tax_location record exists.
177 - credit: Limit to line items that received a credit application. The
178 amount of the credit will also be shown.
183 my $curuser = $FS::CurrentUser::CurrentUser;
185 die "access denied" unless $curuser->access_right('Financial reports');
187 my $conf = new FS::Conf;
188 my $money_char = $conf->config('money_char') || '$';
190 my @select = ( 'cust_bill_pkg.*', 'cust_bill._date' );
191 my @total = ( 'COUNT(*)', 'SUM(cust_bill_pkg.setup + cust_bill_pkg.recur)');
192 my @total_desc = ( '%d line items', $money_char.'%.2f total' ); # sprintf strings
194 my @peritem = ( 'setup', 'recur' );
195 my @peritem_desc = ( 'Setup charge', 'Recurring charge' );
197 my @pkgnum_header = ();
200 my $pkgnum_align = '';
201 if ( $curuser->option('show_pkgnum') ) {
202 push @select, 'cust_bill_pkg.pkgnum';
203 push @pkgnum_header, 'Pkg Num';
204 push @pkgnum, sub { $_[0]->pkgnum > 0 ? $_[0]->pkgnum : '' };
205 push @pkgnum_null, '';
206 $pkgnum_align .= 'r';
209 my @post_desc_header = ();
211 my @post_desc_null = ();
212 my $post_desc_align = '';
213 if ( $conf->exists('enable_taxclasses') ) {
214 push @post_desc_header, 'Tax class';
215 push @post_desc, 'taxclass';
216 push @post_desc_null, '';
217 $post_desc_align .= 'l';
218 push @select, 'part_pkg.taxclass'; # or should this use override?
221 # valid in both the tax and non-tax cases
223 " LEFT JOIN cust_bill USING (invnum)
224 LEFT JOIN cust_main USING (custnum)
227 #agent virtualization
229 $FS::CurrentUser::CurrentUser->agentnums_sql( 'table' => 'cust_main' );
231 my @where = ( $agentnums_sql );
234 my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
236 if ( $cgi->param('distribute') == 1 ) {
237 push @where, "sdate <= $ending",
238 "edate > $beginning",
242 push @where, "cust_bill._date >= $beginning",
243 "cust_bill._date <= $ending";
247 if ( $cgi->param('status') =~ /^([a-z]+)$/ ) {
248 push @where, FS::cust_main->cust_status_sql . " = '$1'";
252 if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
253 push @where, "cust_main.agentnum = $1";
257 if ( $cgi->param('refnum') =~ /^(\d+)$/ ) {
258 push @where, "cust_main.refnum = $1";
262 if ( $cgi->param('custnum') =~ /^(\d+)$/ ) {
263 push @where, "cust_main.custnum = $1";
266 # we want the package and its definition if available
268 ' LEFT JOIN cust_pkg USING (pkgnum)
269 LEFT JOIN part_pkg USING (pkgpart)';
271 my $part_pkg = 'part_pkg';
272 if ( $cgi->param('use_override') ) {
273 # still need the real part_pkg for tax applicability,
275 $join_pkg .= " LEFT JOIN part_pkg AS override ON (
276 COALESCE(cust_bill_pkg.pkgpart_override, cust_pkg.pkgpart, 0) = part_pkg.pkgpart
278 $part_pkg = 'override';
280 push @select, 'part_pkg.pkgpart', 'part_pkg.pkg'; # or should this use override?
283 if ( $cgi->param('nottax') ) {
285 push @where, 'cust_bill_pkg.pkgnum > 0';
287 my @tax_where; # will go into a subquery
288 my @exempt_where; # will also go into a subquery
290 # classnum (of override pkgpart if applicable)
291 # not specified: all classes
294 if ( $cgi->param('classnum') =~ /^(\d+)$/ ) {
295 push @where, "COALESCE($part_pkg.classnum, 0) = $1";
299 if ( $cgi->param('taxclassNULL') ) {
300 # a little different from 'taxclass' in that it applies to the
301 # effective taxclass, not the real one
302 push @tax_where, 'cust_main_county.taxclass IS NULL'
303 } elsif ( $cgi->param('taxclass') ) {
304 push @tax_where, "$part_pkg.taxclass IN (" .
305 join(', ', map {dbh->quote($_)} $cgi->param('taxclass') ).
309 if ( $cgi->param('exempt_cust') eq 'Y' ) {
310 # tax-exempt customers
311 push @exempt_where, "(exempt_cust = 'Y' OR exempt_cust_taxname = 'Y')";
313 } elsif ( $cgi->param('exempt_pkg') eq 'Y' ) { # non-taxable package
314 # non-taxable package charges
315 push @exempt_where, "(exempt_setup = 'Y' OR exempt_recur = 'Y')";
317 # we don't handle exempt_monthly here
319 if ( $cgi->param('taxname') ) { # specific taxname
320 push @tax_where, 'cust_main_county.taxname = '.
321 dbh->quote($cgi->param('taxname'));
322 } elsif ( $cgi->param('taxnameNULL') ) {
323 push @tax_where, 'cust_main_county.taxname IS NULL OR '.
324 'cust_main_county.taxname = \'Tax\'';
327 # country:state:county:city:district (may be repeated)
328 # You can also pass a big list of taxnums but that leads to huge URLs.
329 # Note that this means "packages whose tax is in this region", not
330 # "packages in this region". It's meant for links from the tax report.
331 if ( $cgi->param('region') ) {
333 foreach ( $cgi->param('region') ) {
335 @loc{qw(country state county city district)} =
336 split(':', $cgi->param('region'));
337 my $string = join(' AND ',
340 "$_ = ".dbh->quote($loc{$_});
346 push @orwhere, "($string)";
348 push @tax_where, '(' . join(' OR ', @orwhere) . ')' if @orwhere;
352 if ( $cgi->param('taxnum') ) {
353 my $taxnum_in = join(',',
354 grep /^\d+$/, $cgi->param('taxnum')
356 push @tax_where, "cust_main_county.taxnum IN ($taxnum_in)"
360 # If we're showing exempt items, we need to find those with
361 # cust_tax_exempt_pkg records matching the selected taxes.
362 # If we're showing taxable items, we need to find those with
363 # cust_bill_pkg_tax_location records. We also need to find the
364 # exemption records so that we can show the taxable amount.
365 # If we're showing all items, we need the union of those.
366 # If we're showing 'out' (items that aren't region/class taxable),
367 # then we need the set of all items minus the union of those.
371 if ( @exempt_where or @tax_where
372 or $cgi->param('taxable') or $cgi->param('out') )
374 # process exemption restrictions, including @tax_where
375 my $exempt_sub = 'SELECT SUM(amount) as exempt_amount, billpkgnum
376 FROM cust_tax_exempt_pkg JOIN cust_main_county USING (taxnum)';
378 $exempt_sub .= ' WHERE '.join(' AND ', @tax_where, @exempt_where)
379 if (@tax_where or @exempt_where);
381 $exempt_sub .= ' GROUP BY billpkgnum';
383 $join_pkg .= " LEFT JOIN ($exempt_sub) AS item_exempt
387 if ( @tax_where or $cgi->param('taxable') or $cgi->param('out') ) {
388 # process tax restrictions
390 'cust_main_county.tax > 0';
392 my $tax_sub = "SELECT invnum, cust_bill_pkg_tax_location.pkgnum
393 FROM cust_bill_pkg_tax_location
394 JOIN cust_bill_pkg AS tax_item USING (billpkgnum)
395 JOIN cust_main_county USING (taxnum)
396 WHERE ". join(' AND ', @tax_where).
397 " GROUP BY invnum, cust_bill_pkg_tax_location.pkgnum";
399 $join_pkg .= " LEFT JOIN ($tax_sub) AS item_tax
400 ON (item_tax.invnum = cust_bill_pkg.invnum AND
401 item_tax.pkgnum = cust_bill_pkg.pkgnum)";
404 # now do something with that
405 if ( @exempt_where ) {
407 push @where, 'item_exempt.billpkgnum IS NOT NULL';
408 push @select, 'item_exempt.exempt_amount';
409 push @peritem, 'exempt_amount';
410 push @peritem_desc, 'Exempt';
411 push @total, 'SUM(exempt_amount)';
412 push @total_desc, "$money_char%.2f tax-exempt";
414 } elsif ( $cgi->param('taxable') ) {
416 my $taxable = 'cust_bill_pkg.setup + cust_bill_pkg.recur '.
417 '- COALESCE(item_exempt.exempt_amount, 0)';
419 push @where, 'item_tax.invnum IS NOT NULL';
420 push @select, "($taxable) AS taxable_amount";
421 push @peritem, 'taxable_amount';
422 push @peritem_desc, 'Taxable';
423 push @total, "SUM($taxable)";
424 push @total_desc, "$money_char%.2f taxable";
426 } elsif ( $cgi->param('out') ) {
428 push @where, 'item_tax.invnum IS NULL',
429 'item_exempt.billpkgnum IS NULL';
431 } elsif ( @tax_where ) {
433 # union of taxable + all exempt_ cases
435 '(item_tax.invnum IS NOT NULL OR item_exempt.billpkgnum IS NOT NULL)';
439 # recur/usage separation
440 if ( $cgi->param('usage') eq 'recurring' ) {
442 my $recur_no_usage = FS::cust_bill_pkg->charged_sql('', '', no_usage => 1);
443 push @select, "($recur_no_usage) AS recur_no_usage";
444 $peritem[1] = 'recur_no_usage';
445 $total[1] = "SUM(cust_bill_pkg.setup + $recur_no_usage)";
446 $total_desc[1] .= ' (excluding usage)';
448 } elsif ( $cgi->param('usage') eq 'usage' ) {
450 my $usage = FS::cust_bill_pkg->usage_sql();
451 push @select, "($usage) AS _usage";
452 # there's already a method named 'usage'
453 $peritem[1] = '_usage';
454 $peritem_desc[1] = 'Usage charge';
455 $total[1] = "SUM($usage)";
456 $total_desc[1] .= ' usage charges';
459 } elsif ( $cgi->param('istax') ) {
461 @peritem = ( 'setup' ); # taxes only have setup
462 @peritem_desc = ( 'Tax charge' );
464 push @where, 'cust_bill_pkg.pkgnum = 0';
466 # tax location when using tax_rate_location
467 if ( scalar( grep( /locationtaxid/, $cgi->param ) ) ) {
469 $join_pkg .= ' LEFT JOIN cust_bill_pkg_tax_rate_location USING ( billpkgnum ) '.
470 ' LEFT JOIN tax_rate_location USING ( taxratelocationnum )';
471 push @where, FS::tax_rate_location->location_sql(
472 map { $_ => (scalar($cgi->param($_)) || '') }
473 qw( district city county state locationtaxid )
477 COALESCE(cust_bill_pkg_tax_rate_location.amount,
478 cust_bill_pkg.setup + cust_bill_pkg.recur)
481 } elsif ( $cgi->param('out') ) {
484 LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum)
486 push @where, 'cust_bill_pkg_tax_location.billpkgnum IS NULL';
488 # each billpkgnum should appear only once
489 $total[0] = 'COUNT(*)';
490 $total[1] = 'SUM(cust_bill_pkg.setup)';
492 } else { # not locationtaxid or 'out'--the normal case
495 LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum)
496 JOIN cust_main_county USING (taxnum)
499 # don't double-count the components of consolidated taxes
500 $total[0] = 'COUNT(DISTINCT cust_bill_pkg.billpkgnum)';
501 $total[1] = 'SUM(cust_bill_pkg_tax_location.amount)';
505 if ( $cgi->param('taxclassNULL') ) {
506 push @where, 'cust_main_county.taxclass IS NULL';
510 if ( $cgi->param('taxnameNULL') ) {
511 push @where, 'cust_main_county.taxname IS NULL OR '.
512 'cust_main_county.taxname = \'Tax\'';
513 } elsif ( $cgi->param('taxname') ) {
514 push @where, 'cust_main_county.taxname = '.
515 dbh->quote($cgi->param('taxname'));
519 if ( $cgi->param('taxnum') ) {
520 my $taxnum_in = join(',',
521 grep /^\d+$/, $cgi->param('taxnum')
523 push @where, "cust_main_county.taxnum IN ($taxnum_in)"
527 # report group (itemdesc)
528 if ( $cgi->param('report_group') =~ /^(=|!=) (.*)$/ ) {
529 my ( $group_op, $group_value ) = ( $1, $2 );
530 if ( $group_op eq '=' ) {
531 #push @where, 'itemdesc LIKE '. dbh->quote($group_value.'%');
532 push @where, 'itemdesc = '. dbh->quote($group_value);
533 } elsif ( $group_op eq '!=' ) {
534 push @where, '( itemdesc != '. dbh->quote($group_value) .' OR itemdesc IS NULL )';
536 die "guru meditation #00de: group_op $group_op\n";
540 # itemdesc, for some reason
541 if ( $cgi->param('itemdesc') ) {
542 if ( $cgi->param('itemdesc') eq 'Tax' ) {
543 push @where, "(itemdesc='Tax' OR itemdesc is null)";
545 push @where, 'itemdesc='. dbh->quote($cgi->param('itemdesc'));
553 my $pay_sub = "SELECT SUM(cust_bill_pay_pkg.amount) AS pay_amount,
555 FROM cust_bill_pay_pkg
556 GROUP BY billpkgnum";
557 $join_pkg .= " LEFT JOIN ($pay_sub) AS item_pay USING (billpkgnum)";
558 push @select, 'item_pay.pay_amount';
562 if ( $cgi->param('credit') ) {
566 if ( $cgi->param('istax') ) {
567 # then we need to group/join by billpkgtaxlocationnum, to get only the
568 # relevant part of partial taxes
569 my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
570 reason.reason as reason_text, access_user.username AS username_text,
571 billpkgtaxlocationnum, billpkgnum
572 FROM cust_credit_bill_pkg
573 JOIN cust_credit_bill USING (creditbillnum)
574 JOIN cust_credit USING (crednum)
575 LEFT JOIN reason USING (reasonnum)
576 LEFT JOIN access_user USING (usernum)
577 GROUP BY billpkgnum, billpkgtaxlocationnum, reason.reason,
578 access_user.username";
580 if ( $cgi->param('out') ) {
582 # find credits that are applied to the line items, but not to
583 # a cust_bill_pkg_tax_location link
584 $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
586 push @where, 'item_credit.billpkgtaxlocationnum IS NULL';
590 # find credits that are applied to the CBPTL links that are
591 # considered "interesting" by the report criteria
592 $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
593 USING (billpkgtaxlocationnum)";
598 # then only group by billpkgnum
599 my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
600 reason.reason as reason_text, access_user.username AS username_text,
602 FROM cust_credit_bill_pkg
603 JOIN cust_credit_bill USING (creditbillnum)
604 JOIN cust_credit USING (crednum)
605 LEFT JOIN reason USING (reasonnum)
606 LEFT JOIN access_user USING (usernum)
607 GROUP BY billpkgnum, reason.reason, access_user.username";
608 $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit USING (billpkgnum)";
611 push @where, 'item_credit.billpkgnum IS NOT NULL';
612 push @select, 'item_credit.credit_amount',
613 'item_credit.username_text',
614 'item_credit.reason_text';
615 push @peritem, 'credit_amount', 'username_text', 'reason_text';
616 push @peritem_desc, 'Credited', 'By', 'Reason';
617 push @total, 'SUM(credit_amount)';
618 push @total_desc, "$money_char%.2f credited";
622 #still want a credit total column
624 my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
626 FROM cust_credit_bill_pkg
627 GROUP BY billpkgnum";
628 $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit USING (billpkgnum)";
630 push @select, 'item_credit.credit_amount';
634 push @select, 'cust_main.custnum', FS::UI::Web::cust_sql_fields();
636 my $where = join(' AND ', @where);
637 $where &&= "WHERE $where";
640 'table' => 'cust_bill_pkg',
641 'addl_from' => "$join_cust $join_pkg",
643 'select' => join(",\n", @select ),
644 'extra_sql' => $where,
645 'order_by' => 'ORDER BY cust_bill._date, cust_bill_pkg.billpkgnum',
649 'SELECT ' . join(',', @total) .
650 " FROM cust_bill_pkg $join_cust $join_pkg
653 shift @total_desc; #the first one is implicit
655 @peritem_desc = map {emt($_)} @peritem_desc;
656 my @peritem_sub = map {
658 if ($field =~ /_text$/) { # kludge for credit reason/username fields
659 sub {$_[0]->get($field)};
661 sub { sprintf($money_char.'%.2f', $_[0]->get($field)) }
664 my @peritem_null = map { '' } @peritem; # placeholders
665 my $peritem_align = 'r' x scalar(@peritem);
667 my $ilink = [ "${p}view/cust_bill.cgi?", 'invnum' ];
668 my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ];
670 my $pay_link = ''; #[, 'billpkgnum', ];
671 my $credit_link = [ "${p}search/cust_credit_bill_pkg.html?billpkgnum=", 'billpkgnum', ];
673 warn "\n\nQUERY:\n".Dumper($query)."\n\nCOUNT_QUERY:\n$count_query\n\n"
674 if $cgi->param('debug');