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 # use cust_pkg.locationnum if it exists
227 FS::UI::Web::join_cust_main('cust_bill', 'cust_pkg');
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";
263 # 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)
264 if ( grep { $_ eq 'cust_classnum' } $cgi->param ) {
265 my @classnums = grep /^\d*$/, $cgi->param('cust_classnum');
266 push @where, 'COALESCE( cust_main.classnum, 0) IN ( '.
267 join(',', map { $_ || '0' } @classnums ).
274 if ( $cgi->param('custnum') =~ /^(\d+)$/ ) {
275 push @where, "cust_main.custnum = $1";
278 # we want the package and its definition if available
280 ' LEFT JOIN cust_pkg USING (pkgnum)
281 LEFT JOIN part_pkg USING (pkgpart)';
283 my $part_pkg = 'part_pkg';
284 if ( $cgi->param('use_override') ) { #"Separate sub-packages from parents"
285 # still need the real part_pkg for tax applicability,
287 $join_pkg .= " LEFT JOIN part_pkg AS override ON (
288 COALESCE(cust_bill_pkg.pkgpart_override, cust_pkg.pkgpart, 0) = override.pkgpart
290 $part_pkg = 'override';
292 push @select, 'part_pkg.pkgpart', 'part_pkg.pkg'; # or should this use override?
295 if ( $cgi->param('nottax') ) {
297 push @where, 'cust_bill_pkg.pkgnum > 0';
299 my @tax_where; # will go into a subquery
300 my @exempt_where; # will also go into a subquery
302 # classnum (of override pkgpart if applicable)
303 # not specified: all classes
306 if ( $cgi->param('classnum') =~ /^(\d+)$/ ) {
307 push @where, "COALESCE($part_pkg.classnum, 0) = $1";
311 if ( $cgi->param('taxclassNULL') ) {
312 # a little different from 'taxclass' in that it applies to the
313 # effective taxclass, not the real one
314 push @tax_where, 'cust_main_county.taxclass IS NULL'
315 } elsif ( $cgi->param('taxclass') ) {
316 push @tax_where, "$part_pkg.taxclass IN (" .
317 join(', ', map {dbh->quote($_)} $cgi->param('taxclass') ).
321 if ( $cgi->param('exempt_cust') eq 'Y' ) {
322 # tax-exempt customers
323 push @exempt_where, "(exempt_cust = 'Y' OR exempt_cust_taxname = 'Y')";
325 } elsif ( $cgi->param('exempt_pkg') eq 'Y' ) { # non-taxable package
326 # non-taxable package charges
327 push @exempt_where, "(exempt_setup = 'Y' OR exempt_recur = 'Y')";
329 # we don't handle exempt_monthly here
331 if ( $cgi->param('taxname') ) { # specific taxname
332 push @tax_where, 'cust_main_county.taxname = '.
333 dbh->quote($cgi->param('taxname'));
334 } elsif ( $cgi->param('taxnameNULL') ) {
335 push @tax_where, 'cust_main_county.taxname IS NULL OR '.
336 'cust_main_county.taxname = \'Tax\'';
339 # country:state:county:city:district (may be repeated)
340 # You can also pass a big list of taxnums but that leads to huge URLs.
341 # Note that this means "packages whose tax is in this region", not
342 # "packages in this region". It's meant for links from the tax report.
343 if ( $cgi->param('region') ) {
345 foreach ( $cgi->param('region') ) {
347 @loc{qw(country state county city district)} =
348 split(':', $cgi->param('region'));
349 my $string = join(' AND ',
352 "$_ = ".dbh->quote($loc{$_});
358 push @orwhere, "($string)";
360 push @tax_where, '(' . join(' OR ', @orwhere) . ')' if @orwhere;
364 if ( $cgi->param('taxnum') ) {
365 my $taxnum_in = join(',',
366 grep /^\d+$/, $cgi->param('taxnum')
368 push @tax_where, "cust_main_county.taxnum IN ($taxnum_in)"
372 # If we're showing exempt items, we need to find those with
373 # cust_tax_exempt_pkg records matching the selected taxes.
374 # If we're showing taxable items, we need to find those with
375 # cust_bill_pkg_tax_location records. We also need to find the
376 # exemption records so that we can show the taxable amount.
377 # If we're showing all items, we need the union of those.
378 # If we're showing 'out' (items that aren't region/class taxable),
379 # then we need the set of all items minus the union of those.
383 if ( @exempt_where or @tax_where
384 or $cgi->param('taxable') or $cgi->param('out') )
386 # process exemption restrictions, including @tax_where
387 my $exempt_sub = 'SELECT SUM(amount) as exempt_amount, billpkgnum
388 FROM cust_tax_exempt_pkg JOIN cust_main_county USING (taxnum)';
390 $exempt_sub .= ' WHERE '.join(' AND ', @tax_where, @exempt_where)
391 if (@tax_where or @exempt_where);
393 $exempt_sub .= ' GROUP BY billpkgnum';
395 $join_pkg .= " LEFT JOIN ($exempt_sub) AS item_exempt
399 if ( @tax_where or $cgi->param('taxable') or $cgi->param('out') ) {
400 # process tax restrictions
402 'cust_main_county.tax > 0';
404 my $tax_sub = "SELECT invnum, cust_bill_pkg_tax_location.pkgnum
405 FROM cust_bill_pkg_tax_location
406 JOIN cust_bill_pkg AS tax_item USING (billpkgnum)
407 JOIN cust_main_county USING (taxnum)
408 WHERE ". join(' AND ', @tax_where).
409 " GROUP BY invnum, cust_bill_pkg_tax_location.pkgnum";
411 $join_pkg .= " LEFT JOIN ($tax_sub) AS item_tax
412 ON (item_tax.invnum = cust_bill_pkg.invnum AND
413 item_tax.pkgnum = cust_bill_pkg.pkgnum)";
416 # now do something with that
417 if ( @exempt_where ) {
419 push @where, 'item_exempt.billpkgnum IS NOT NULL';
420 push @select, 'item_exempt.exempt_amount';
421 push @peritem, 'exempt_amount';
422 push @peritem_desc, 'Exempt';
423 push @total, 'SUM(exempt_amount)';
424 push @total_desc, "$money_char%.2f tax-exempt";
426 } elsif ( $cgi->param('taxable') ) {
428 my $taxable = 'cust_bill_pkg.setup + cust_bill_pkg.recur '.
429 '- COALESCE(item_exempt.exempt_amount, 0)';
431 push @where, 'item_tax.invnum IS NOT NULL';
432 push @select, "($taxable) AS taxable_amount";
433 push @peritem, 'taxable_amount';
434 push @peritem_desc, 'Taxable';
435 push @total, "SUM($taxable)";
436 push @total_desc, "$money_char%.2f taxable";
438 } elsif ( $cgi->param('out') ) {
440 push @where, 'item_tax.invnum IS NULL',
441 'item_exempt.billpkgnum IS NULL';
443 } elsif ( @tax_where ) {
445 # union of taxable + all exempt_ cases
447 '(item_tax.invnum IS NOT NULL OR item_exempt.billpkgnum IS NOT NULL)';
451 # recur/usage separation
452 if ( $cgi->param('usage') eq 'recurring' ) {
454 my $recur_no_usage = FS::cust_bill_pkg->charged_sql('', '', no_usage => 1);
455 push @select, "($recur_no_usage) AS recur_no_usage";
456 $peritem[1] = 'recur_no_usage';
457 $total[1] = "SUM(cust_bill_pkg.setup + $recur_no_usage)";
458 $total_desc[0] .= ' (excluding usage)';
460 } elsif ( $cgi->param('usage') eq 'usage' ) {
462 my $usage = FS::cust_bill_pkg->usage_sql();
463 push @select, "($usage) AS _usage";
464 # there's already a method named 'usage'
465 $peritem[1] = '_usage';
466 $peritem_desc[1] = 'Usage charge';
467 $total[1] = "SUM($usage)";
468 $total_desc[0] .= ' usage charges';
471 } elsif ( $cgi->param('istax') ) {
473 @peritem = ( 'setup' ); # taxes only have setup
474 @peritem_desc = ( 'Tax charge' );
476 push @where, 'cust_bill_pkg.pkgnum = 0';
478 # tax location when using tax_rate_location
479 if ( scalar( grep( /locationtaxid/, $cgi->param ) ) ) {
481 $join_pkg .= ' LEFT JOIN cust_bill_pkg_tax_rate_location USING ( billpkgnum ) '.
482 ' LEFT JOIN tax_rate_location USING ( taxratelocationnum )';
483 push @where, FS::tax_rate_location->location_sql(
484 map { $_ => (scalar($cgi->param($_)) || '') }
485 qw( district city county state locationtaxid )
489 COALESCE(cust_bill_pkg_tax_rate_location.amount,
490 cust_bill_pkg.setup + cust_bill_pkg.recur)
493 } elsif ( $cgi->param('out') ) {
496 LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum)
498 push @where, 'cust_bill_pkg_tax_location.billpkgnum IS NULL';
500 # each billpkgnum should appear only once
501 $total[0] = 'COUNT(*)';
502 $total[1] = 'SUM(cust_bill_pkg.setup)';
504 } else { # not locationtaxid or 'out'--the normal case
507 LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum)
508 JOIN cust_main_county USING (taxnum)
511 # don't double-count the components of consolidated taxes
512 $total[0] = 'COUNT(DISTINCT cust_bill_pkg.billpkgnum)';
513 $total[1] = 'SUM(cust_bill_pkg_tax_location.amount)';
517 if ( $cgi->param('taxclassNULL') ) {
518 push @where, 'cust_main_county.taxclass IS NULL';
522 if ( $cgi->param('taxnameNULL') ) {
523 push @where, 'cust_main_county.taxname IS NULL OR '.
524 'cust_main_county.taxname = \'Tax\'';
525 } elsif ( $cgi->param('taxname') ) {
526 push @where, 'cust_main_county.taxname = '.
527 dbh->quote($cgi->param('taxname'));
531 if ( $cgi->param('taxnum') ) {
532 my $taxnum_in = join(',',
533 grep /^\d+$/, $cgi->param('taxnum')
535 push @where, "cust_main_county.taxnum IN ($taxnum_in)"
539 # report group (itemdesc)
540 if ( $cgi->param('report_group') =~ /^(=|!=) (.*)$/ ) {
541 my ( $group_op, $group_value ) = ( $1, $2 );
542 if ( $group_op eq '=' ) {
543 #push @where, 'itemdesc LIKE '. dbh->quote($group_value.'%');
544 push @where, 'itemdesc = '. dbh->quote($group_value);
545 } elsif ( $group_op eq '!=' ) {
546 push @where, '( itemdesc != '. dbh->quote($group_value) .' OR itemdesc IS NULL )';
548 die "guru meditation #00de: group_op $group_op\n";
552 # itemdesc, for some reason
553 if ( $cgi->param('itemdesc') ) {
554 if ( $cgi->param('itemdesc') eq 'Tax' ) {
555 push @where, "(itemdesc='Tax' OR itemdesc is null)";
557 push @where, 'itemdesc='. dbh->quote($cgi->param('itemdesc'));
565 my $pay_sub = "SELECT SUM(cust_bill_pay_pkg.amount)
566 FROM cust_bill_pay_pkg
567 WHERE cust_bill_pkg.billpkgnum = cust_bill_pay_pkg.billpkgnum
569 push @select, "($pay_sub) AS pay_amount";
573 if ( $cgi->param('credit') ) {
577 if ( $cgi->param('istax') ) {
578 # then we need to group/join by billpkgtaxlocationnum, to get only the
579 # relevant part of partial taxes
580 my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
581 reason.reason as reason_text, access_user.username AS username_text,
582 billpkgtaxlocationnum, billpkgnum
583 FROM cust_credit_bill_pkg
584 JOIN cust_credit_bill USING (creditbillnum)
585 JOIN cust_credit USING (crednum)
586 LEFT JOIN reason USING (reasonnum)
587 LEFT JOIN access_user USING (usernum)
588 GROUP BY billpkgnum, billpkgtaxlocationnum, reason.reason,
589 access_user.username";
591 if ( $cgi->param('out') ) {
593 # find credits that are applied to the line items, but not to
594 # a cust_bill_pkg_tax_location link
595 $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
597 push @where, 'item_credit.billpkgtaxlocationnum IS NULL';
601 # find credits that are applied to the CBPTL links that are
602 # considered "interesting" by the report criteria
603 $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
604 USING (billpkgtaxlocationnum)";
609 # then only group by billpkgnum
610 my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
611 reason.reason as reason_text, access_user.username AS username_text,
613 FROM cust_credit_bill_pkg
614 JOIN cust_credit_bill USING (creditbillnum)
615 JOIN cust_credit USING (crednum)
616 LEFT JOIN reason USING (reasonnum)
617 LEFT JOIN access_user USING (usernum)
618 GROUP BY billpkgnum, reason.reason, access_user.username";
619 $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit USING (billpkgnum)";
622 push @where, 'item_credit.billpkgnum IS NOT NULL';
623 push @select, 'item_credit.credit_amount',
624 'item_credit.username_text',
625 'item_credit.reason_text';
626 push @peritem, 'credit_amount', 'username_text', 'reason_text';
627 push @peritem_desc, 'Credited', 'By', 'Reason';
628 push @total, 'SUM(credit_amount)';
629 push @total_desc, "$money_char%.2f credited";
633 #still want a credit total column
636 SELECT SUM(cust_credit_bill_pkg.amount)
637 FROM cust_credit_bill_pkg
638 WHERE cust_bill_pkg.billpkgnum = cust_credit_bill_pkg.billpkgnum
640 push @select, "($credit_sub) AS credit_amount";
644 push @select, 'cust_main.custnum', FS::UI::Web::cust_sql_fields();
646 my $where = join(' AND ', @where);
647 $where &&= "WHERE $where";
650 'table' => 'cust_bill_pkg',
651 'addl_from' => "$join_pkg $join_cust",
653 'select' => join(",\n", @select ),
654 'extra_sql' => $where,
655 'order_by' => 'ORDER BY cust_bill._date, cust_bill_pkg.billpkgnum',
659 'SELECT ' . join(',', @total) .
660 " FROM cust_bill_pkg $join_pkg $join_cust
663 @peritem_desc = map {emt($_)} @peritem_desc;
664 my @peritem_sub = map {
666 if ($field =~ /_text$/) { # kludge for credit reason/username fields
667 sub {$_[0]->get($field)};
669 sub { sprintf($money_char.'%.2f', $_[0]->get($field)) }
672 my @peritem_null = map { '' } @peritem; # placeholders
673 my $peritem_align = 'r' x scalar(@peritem);
675 my $ilink = [ "${p}view/cust_bill.cgi?", 'invnum' ];
676 my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ];
678 my $pay_link = ''; #[, 'billpkgnum', ];
679 my $credit_link = [ "${p}search/cust_credit_bill_pkg.html?billpkgnum=", 'billpkgnum', ];
681 warn "\n\nQUERY:\n".Dumper($query)."\n\nCOUNT_QUERY:\n$count_query\n\n"
682 if $cgi->param('debug');