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, a comma-separated list, the word "multiple", or an empty
129 string (for "no report class").
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 ( $cgi->param('classnum') =~ /^(\d+)$/ ) {
325 push @where, "COALESCE($part_pkg.classnum, 0) = $1";
328 if ( $cgi->param('report_optionnum') =~ /^(\w+)$/ ) {
332 FS::Report::Table->with_report_option( $1, $cgi->param('use_override') )
337 if ( $cgi->param('taxclassNULL') ) {
338 # a little different from 'taxclass' in that it applies to the
339 # effective taxclass, not the real one
340 push @tax_where, 'cust_main_county.taxclass IS NULL'
341 } elsif ( $cgi->param('taxclass') ) {
342 push @tax_where, "$part_pkg.taxclass IN (" .
343 join(', ', map {dbh->quote($_)} $cgi->param('taxclass') ).
347 if ( $cgi->param('exempt_cust') eq 'Y' ) {
348 # tax-exempt customers
349 push @exempt_where, "(exempt_cust = 'Y' OR exempt_cust_taxname = 'Y')";
351 } elsif ( $cgi->param('exempt_pkg') eq 'Y' ) { # non-taxable package
352 # non-taxable package charges
353 push @exempt_where, "(exempt_setup = 'Y' OR exempt_recur = 'Y')";
355 # we don't handle exempt_monthly here
357 if ( $cgi->param('taxname') ) { # specific taxname
358 push @tax_where, 'cust_main_county.taxname = '.
359 dbh->quote($cgi->param('taxname'));
360 } elsif ( $cgi->param('taxnameNULL') ) {
361 push @tax_where, 'cust_main_county.taxname IS NULL OR '.
362 'cust_main_county.taxname = \'Tax\'';
365 # country:state:county:city:district (may be repeated)
366 # You can also pass a big list of taxnums but that leads to huge URLs.
367 # Note that this means "packages whose tax is in this region", not
368 # "packages in this region". It's meant for links from the tax report.
369 if ( $cgi->param('region') ) {
371 foreach ( $cgi->param('region') ) {
373 @loc{qw(country state county city district)} =
374 split(':', $cgi->param('region'));
375 my $string = join(' AND ',
378 "$_ = ".dbh->quote($loc{$_});
384 push @orwhere, "($string)";
386 push @tax_where, '(' . join(' OR ', @orwhere) . ')' if @orwhere;
390 if ( $cgi->param('taxnum') ) {
391 my $taxnum_in = join(',',
392 grep /^\d+$/, $cgi->param('taxnum')
394 push @tax_where, "cust_main_county.taxnum IN ($taxnum_in)"
398 # If we're showing exempt items, we need to find those with
399 # cust_tax_exempt_pkg records matching the selected taxes.
400 # If we're showing taxable items, we need to find those with
401 # cust_bill_pkg_tax_location records. We also need to find the
402 # exemption records so that we can show the taxable amount.
403 # If we're showing all items, we need the union of those.
404 # If we're showing 'out' (items that aren't region/class taxable),
405 # then we need the set of all items minus the union of those.
409 if ( @exempt_where or @tax_where
410 or $cgi->param('taxable') or $cgi->param('out') )
412 # process exemption restrictions, including @tax_where
413 my $exempt_sub = 'SELECT SUM(amount) as exempt_amount, billpkgnum
414 FROM cust_tax_exempt_pkg JOIN cust_main_county USING (taxnum)';
416 $exempt_sub .= ' WHERE '.join(' AND ', @tax_where, @exempt_where)
417 if (@tax_where or @exempt_where);
419 $exempt_sub .= ' GROUP BY billpkgnum';
421 $join_pkg .= " LEFT JOIN ($exempt_sub) AS item_exempt
425 if ( @tax_where or $cgi->param('taxable') or $cgi->param('out') ) {
426 # process tax restrictions
428 'cust_main_county.tax > 0';
430 my $tax_sub = "SELECT invnum, cust_bill_pkg_tax_location.pkgnum
431 FROM cust_bill_pkg_tax_location
432 JOIN cust_bill_pkg AS tax_item USING (billpkgnum)
433 JOIN cust_main_county USING (taxnum)
434 WHERE ". join(' AND ', @tax_where).
435 " GROUP BY invnum, cust_bill_pkg_tax_location.pkgnum";
437 $join_pkg .= " LEFT JOIN ($tax_sub) AS item_tax
438 ON (item_tax.invnum = cust_bill_pkg.invnum AND
439 item_tax.pkgnum = cust_bill_pkg.pkgnum)";
442 # now do something with that
443 if ( @exempt_where ) {
445 push @where, 'item_exempt.billpkgnum IS NOT NULL';
446 push @select, 'item_exempt.exempt_amount';
447 push @peritem, 'exempt_amount';
448 push @peritem_desc, 'Exempt';
449 push @total, 'SUM(exempt_amount)';
450 push @total_desc, "$money_char%.2f tax-exempt";
452 } elsif ( $cgi->param('taxable') ) {
454 my $taxable = 'cust_bill_pkg.setup + cust_bill_pkg.recur '.
455 '- COALESCE(item_exempt.exempt_amount, 0)';
457 push @where, 'item_tax.invnum IS NOT NULL';
458 push @select, "($taxable) AS taxable_amount";
459 push @peritem, 'taxable_amount';
460 push @peritem_desc, 'Taxable';
461 push @total, "SUM($taxable)";
462 push @total_desc, "$money_char%.2f taxable";
464 } elsif ( $cgi->param('out') ) {
466 push @where, 'item_tax.invnum IS NULL',
467 'item_exempt.billpkgnum IS NULL';
469 } elsif ( @tax_where ) {
471 # union of taxable + all exempt_ cases
473 '(item_tax.invnum IS NOT NULL OR item_exempt.billpkgnum IS NOT NULL)';
477 # recur/usage separation
478 if ( $cgi->param('usage') eq 'recurring' ) {
480 my $recur_no_usage = FS::cust_bill_pkg->charged_sql('', '', no_usage => 1);
481 push @select, "($recur_no_usage) AS recur_no_usage";
482 $peritem[1] = 'recur_no_usage';
483 $total[1] = "SUM(cust_bill_pkg.setup + $recur_no_usage)";
484 $total_desc[0] .= ' (excluding usage)';
486 } elsif ( $cgi->param('usage') eq 'usage' ) {
488 my $usage = FS::cust_bill_pkg->usage_sql();
489 push @select, "($usage) AS _usage";
490 # there's already a method named 'usage'
491 $peritem[1] = '_usage';
492 $peritem_desc[1] = 'Usage charge';
493 $total[1] = "SUM($usage)";
494 $total_desc[0] .= ' usage charges';
497 } elsif ( $cgi->param('istax') ) {
499 @peritem = ( 'setup' ); # taxes only have setup
500 @peritem_desc = ( 'Tax charge' );
502 push @where, 'cust_bill_pkg.pkgnum = 0';
504 # tax location when using tax_rate_location
505 if ( scalar( grep( /locationtaxid/, $cgi->param ) ) ) {
507 $join_pkg .= ' LEFT JOIN cust_bill_pkg_tax_rate_location USING ( billpkgnum ) '.
508 ' LEFT JOIN tax_rate_location USING ( taxratelocationnum )';
509 push @where, FS::tax_rate_location->location_sql(
510 map { $_ => (scalar($cgi->param($_)) || '') }
511 qw( district city county state locationtaxid )
515 COALESCE(cust_bill_pkg_tax_rate_location.amount,
516 cust_bill_pkg.setup + cust_bill_pkg.recur)
519 } elsif ( $cgi->param('out') ) {
522 LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum)
524 push @where, 'cust_bill_pkg_tax_location.billpkgnum IS NULL';
526 # each billpkgnum should appear only once
527 $total[0] = 'COUNT(*)';
528 $total[1] = 'SUM(cust_bill_pkg.setup)';
530 } else { # not locationtaxid or 'out'--the normal case
533 LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum)
534 JOIN cust_main_county USING (taxnum)
537 # don't double-count the components of consolidated taxes
538 $total[0] = 'COUNT(DISTINCT cust_bill_pkg.billpkgnum)';
539 $total[1] = 'SUM(cust_bill_pkg_tax_location.amount)';
543 if ( $cgi->param('taxclassNULL') ) {
544 push @where, 'cust_main_county.taxclass IS NULL';
548 if ( $cgi->param('taxnameNULL') ) {
549 push @where, 'cust_main_county.taxname IS NULL OR '.
550 'cust_main_county.taxname = \'Tax\'';
551 } elsif ( $cgi->param('taxname') ) {
552 push @where, 'cust_main_county.taxname = '.
553 dbh->quote($cgi->param('taxname'));
557 if ( $cgi->param('taxnum') ) {
558 my $taxnum_in = join(',',
559 grep /^\d+$/, $cgi->param('taxnum')
561 push @where, "cust_main_county.taxnum IN ($taxnum_in)"
565 # report group (itemdesc)
566 if ( $cgi->param('report_group') =~ /^(=|!=) (.*)$/ ) {
567 my ( $group_op, $group_value ) = ( $1, $2 );
568 if ( $group_op eq '=' ) {
569 #push @where, 'itemdesc LIKE '. dbh->quote($group_value.'%');
570 push @where, 'itemdesc = '. dbh->quote($group_value);
571 } elsif ( $group_op eq '!=' ) {
572 push @where, '( itemdesc != '. dbh->quote($group_value) .' OR itemdesc IS NULL )';
574 die "guru meditation #00de: group_op $group_op\n";
578 # itemdesc, for some reason
579 if ( $cgi->param('itemdesc') ) {
580 if ( $cgi->param('itemdesc') eq 'Tax' ) {
581 push @where, "(itemdesc='Tax' OR itemdesc is null)";
583 push @where, 'itemdesc='. dbh->quote($cgi->param('itemdesc'));
591 my $pay_sub = "SELECT SUM(cust_bill_pay_pkg.amount)
592 FROM cust_bill_pay_pkg
593 WHERE cust_bill_pkg.billpkgnum = cust_bill_pay_pkg.billpkgnum
595 push @select, "($pay_sub) AS pay_amount";
599 if ( $cgi->param('credit') ) {
603 if ( $cgi->param('istax') ) {
604 # then we need to group/join by billpkgtaxlocationnum, to get only the
605 # relevant part of partial taxes
606 my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
607 reason.reason as reason_text, access_user.username AS username_text,
608 billpkgtaxlocationnum, billpkgnum
609 FROM cust_credit_bill_pkg
610 JOIN cust_credit_bill USING (creditbillnum)
611 JOIN cust_credit USING (crednum)
612 LEFT JOIN reason USING (reasonnum)
613 LEFT JOIN access_user USING (usernum)
614 GROUP BY billpkgnum, billpkgtaxlocationnum, reason.reason,
615 access_user.username";
617 if ( $cgi->param('out') ) {
619 # find credits that are applied to the line items, but not to
620 # a cust_bill_pkg_tax_location link
621 $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
623 push @where, 'item_credit.billpkgtaxlocationnum IS NULL';
627 # find credits that are applied to the CBPTL links that are
628 # considered "interesting" by the report criteria
629 $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
630 USING (billpkgtaxlocationnum)";
635 # then only group by billpkgnum
636 my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
637 reason.reason as reason_text, access_user.username AS username_text,
639 FROM cust_credit_bill_pkg
640 JOIN cust_credit_bill USING (creditbillnum)
641 JOIN cust_credit USING (crednum)
642 LEFT JOIN reason USING (reasonnum)
643 LEFT JOIN access_user USING (usernum)
644 GROUP BY billpkgnum, reason.reason, access_user.username";
645 $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit USING (billpkgnum)";
648 push @where, 'item_credit.billpkgnum IS NOT NULL';
649 push @select, 'item_credit.credit_amount',
650 'item_credit.username_text',
651 'item_credit.reason_text';
652 push @peritem, 'credit_amount', 'username_text', 'reason_text';
653 push @peritem_desc, 'Credited', 'By', 'Reason';
654 push @total, 'SUM(credit_amount)';
655 push @total_desc, "$money_char%.2f credited";
659 #still want a credit total column
662 SELECT SUM(cust_credit_bill_pkg.amount)
663 FROM cust_credit_bill_pkg
664 WHERE cust_bill_pkg.billpkgnum = cust_credit_bill_pkg.billpkgnum
666 push @select, "($credit_sub) AS credit_amount";
670 push @select, 'cust_main.custnum', FS::UI::Web::cust_sql_fields();
672 my $where = join(' AND ', @where);
673 $where &&= "WHERE $where";
676 'table' => 'cust_bill_pkg',
677 'addl_from' => "$join_pkg $join_cust",
679 'select' => join(",\n", @select ),
680 'extra_sql' => $where,
681 'order_by' => 'ORDER BY cust_bill._date, cust_bill_pkg.billpkgnum',
685 'SELECT ' . join(',', @total) .
686 " FROM cust_bill_pkg $join_pkg $join_cust
689 @peritem_desc = map {emt($_)} @peritem_desc;
690 my @peritem_sub = map {
692 if ($field =~ /_text$/) { # kludge for credit reason/username fields
693 sub {$_[0]->get($field)};
695 sub { sprintf($money_char.'%.2f', $_[0]->get($field)) }
698 my @peritem_null = map { '' } @peritem; # placeholders
699 my $peritem_align = 'r' x scalar(@peritem);
701 my $ilink = [ "${p}view/cust_bill.cgi?", 'invnum' ];
702 my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ];
704 my $pay_link = ''; #[, 'billpkgnum', ];
705 my $credit_link = [ "${p}search/cust_credit_bill_pkg.html?billpkgnum=", 'billpkgnum', ];
707 warn "\n\nQUERY:\n".Dumper($query)."\n\nCOUNT_QUERY:\n$count_query\n\n"
708 if $cgi->param('debug');