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 a comma-separated
141 list 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, "COALESCE(cust_main_county.taxname, 'Tax') = ".
353 dbh->quote($cgi->param('taxname'));
356 # country:state:county:city:district (may be repeated)
357 # You can also pass a big list of taxnums but that leads to huge URLs.
358 # Note that this means "packages whose tax is in this region", not
359 # "packages in this region". It's meant for links from the tax report.
360 if ( $cgi->param('region') ) {
362 foreach ( $cgi->param('region') ) {
364 @loc{qw(country state county city district)} =
365 split(':', $cgi->param('region'));
366 my $string = join(' AND ',
369 "$_ = ".dbh->quote($loc{$_});
375 push @orwhere, "($string)";
377 push @tax_where, '(' . join(' OR ', @orwhere) . ')' if @orwhere;
381 if ( $cgi->param('taxnum') =~ /^([\d,]+)$/) {
382 push @tax_where, "cust_main_county.taxnum IN ($1)";
385 # If we're showing exempt items, we need to find those with
386 # cust_tax_exempt_pkg records matching the selected taxes.
387 # If we're showing taxable items, we need to find those with
388 # cust_bill_pkg_tax_location records. We also need to find the
389 # exemption records so that we can show the taxable amount.
390 # If we're showing all items, we need the union of those.
391 # If we're showing 'out' (items that aren't region/class taxable),
392 # then we need the set of all items minus the union of those.
396 if ( @exempt_where or @tax_where
397 or $cgi->param('taxable') or $cgi->param('out') )
399 # process exemption restrictions, including @tax_where
400 my $exempt_sub = 'SELECT SUM(amount) as exempt_amount, billpkgnum
401 FROM cust_tax_exempt_pkg JOIN cust_main_county USING (taxnum)';
403 $exempt_sub .= ' WHERE '.join(' AND ', @tax_where, @exempt_where)
404 if (@tax_where or @exempt_where);
406 $exempt_sub .= ' GROUP BY billpkgnum';
408 $join_pkg .= " LEFT JOIN ($exempt_sub) AS item_exempt
412 # process tax restrictions
414 'cust_bill_pkg_tax_location.taxable_billpkgnum = cust_bill_pkg.billpkgnum',
415 'cust_main_county.tax > 0';
417 my $tax_sub = "SELECT 1
418 FROM cust_bill_pkg_tax_location
419 JOIN cust_bill_pkg AS tax_item USING (billpkgnum)
420 JOIN cust_main_county USING (taxnum)
421 WHERE ". join(' AND ', @tax_where);
423 # now do something with that
424 if ( @exempt_where ) {
426 push @where, 'item_exempt.billpkgnum IS NOT NULL';
427 push @select, 'item_exempt.exempt_amount';
428 push @peritem, 'exempt_amount';
429 push @peritem_desc, 'Exempt';
430 push @total, 'SUM(exempt_amount)';
431 push @total_desc, "$money_char%.2f tax-exempt";
433 } elsif ( $cgi->param('taxable') ) {
435 my $taxable = 'cust_bill_pkg.setup + cust_bill_pkg.recur '.
436 '- COALESCE(item_exempt.exempt_amount, 0)';
438 push @select, "($taxable) AS taxable_amount";
439 push @where, "EXISTS($tax_sub)";
440 push @peritem, 'taxable_amount';
441 push @peritem_desc, 'Taxable';
442 push @total, "SUM($taxable)";
443 push @total_desc, "$money_char%.2f taxable";
445 } elsif ( @tax_where ) {
447 # union of taxable + all exempt_ cases
448 push @where, "(EXISTS($tax_sub) OR item_exempt.billpkgnum IS NOT NULL)";
452 # recur/usage separation
453 if ( $cgi->param('usage') eq 'recurring' ) {
455 my $recur_no_usage = FS::cust_bill_pkg->charged_sql('', '', no_usage => 1);
456 push @select, "($recur_no_usage) AS recur_no_usage";
457 $peritem[1] = 'recur_no_usage';
458 $total[1] = "SUM(cust_bill_pkg.setup + $recur_no_usage)";
459 $total_desc[0] .= ' (excluding usage)';
461 } elsif ( $cgi->param('usage') eq 'usage' ) {
463 my $usage = FS::cust_bill_pkg->usage_sql();
464 push @select, "($usage) AS _usage";
465 # there's already a method named 'usage'
466 $peritem[1] = '_usage';
467 $peritem_desc[1] = 'Usage charge';
468 $total[1] = "SUM($usage)";
469 $total_desc[0] .= ' usage charges';
472 } elsif ( $cgi->param('istax') ) {
474 @peritem = ( 'setup' ); # taxes only have setup
475 @peritem_desc = ( 'Tax charge' );
477 push @where, 'cust_bill_pkg.pkgnum = 0';
479 # tax location when using tax_rate_location
480 if ( scalar( grep( /locationtaxid/, $cgi->param ) ) ) {
482 $join_pkg .= ' LEFT JOIN cust_bill_pkg_tax_rate_location USING ( billpkgnum ) '.
483 ' LEFT JOIN tax_rate_location USING ( taxratelocationnum )';
484 push @where, FS::tax_rate_location->location_sql(
485 map { $_ => (scalar($cgi->param($_)) || '') }
486 qw( district city county state locationtaxid )
490 COALESCE(cust_bill_pkg_tax_rate_location.amount,
491 cust_bill_pkg.setup + cust_bill_pkg.recur)
494 } elsif ( $cgi->param('out') ) {
497 LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum)
499 push @where, 'cust_bill_pkg_tax_location.billpkgnum IS NULL';
501 # each billpkgnum should appear only once
502 $total[0] = 'COUNT(*)';
503 $total[1] = 'SUM(cust_bill_pkg.setup)';
505 } else { # not locationtaxid or 'out'--the normal case
508 LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum)
509 JOIN cust_main_county USING (taxnum)
512 # don't double-count the components of consolidated taxes
513 $total[0] = 'COUNT(DISTINCT cust_bill_pkg.billpkgnum)';
514 $total[1] = 'SUM(cust_bill_pkg_tax_location.amount)';
517 if ( grep { $_ eq 'classnum' } $cgi->param ) {
518 my @classnums = grep /^\d*$/, $cgi->param('classnum');
520 JOIN cust_pkg AS taxed_pkg
521 ON (cust_bill_pkg_tax_location.pkgnum = taxed_pkg.pkgnum)
522 JOIN part_pkg AS taxed_part_pkg
523 ON (taxed_pkg.pkgpart = taxed_part_pkg.pkgpart)
525 push @where, "COALESCE(taxed_part_pkg.classnum, 0) IN ( ".
526 join(',', @classnums ).
533 if ( $cgi->param('taxclassNULL') ) {
534 push @where, 'cust_main_county.taxclass IS NULL';
538 if ( $cgi->param('taxnameNULL') ) {
539 push @where, 'cust_main_county.taxname IS NULL OR '.
540 'cust_main_county.taxname = \'Tax\'';
541 } elsif ( $cgi->param('taxname') ) {
542 push @where, 'cust_main_county.taxname = '.
543 dbh->quote($cgi->param('taxname'));
547 if ( $cgi->param('taxnum') =~ /^([\d,]+)$/) {
548 push @where, "cust_main_county.taxnum IN ($1)";
551 # itemdesc, for some reason
552 if ( $cgi->param('itemdesc') ) {
553 if ( $cgi->param('itemdesc') eq 'Tax' ) {
554 push @where, "(itemdesc='Tax' OR itemdesc is null)";
556 push @where, 'itemdesc='. dbh->quote($cgi->param('itemdesc'));
564 my $pay_sub = "SELECT SUM(cust_bill_pay_pkg.amount)
565 FROM cust_bill_pay_pkg
566 WHERE cust_bill_pkg.billpkgnum = cust_bill_pay_pkg.billpkgnum
568 push @select, "($pay_sub) AS pay_amount";
572 if ( $cgi->param('credit') ) {
576 if ( $cgi->param('istax') ) {
577 # then we need to group/join by billpkgtaxlocationnum, to get only the
578 # relevant part of partial taxes
579 my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
580 reason.reason as reason_text, access_user.username AS username_text,
581 billpkgtaxlocationnum, billpkgnum
582 FROM cust_credit_bill_pkg
583 JOIN cust_credit_bill USING (creditbillnum)
584 JOIN cust_credit USING (crednum)
585 LEFT JOIN reason USING (reasonnum)
586 LEFT JOIN access_user USING (usernum)
587 GROUP BY billpkgnum, billpkgtaxlocationnum, reason.reason,
588 access_user.username";
590 if ( $cgi->param('out') ) {
592 # find credits that are applied to the line items, but not to
593 # a cust_bill_pkg_tax_location link
594 $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
596 push @where, 'item_credit.billpkgtaxlocationnum IS NULL';
600 # find credits that are applied to the CBPTL links that are
601 # considered "interesting" by the report criteria
602 $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
603 USING (billpkgtaxlocationnum)";
608 # then only group by billpkgnum
609 my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
610 reason.reason as reason_text, access_user.username AS username_text,
612 FROM cust_credit_bill_pkg
613 JOIN cust_credit_bill USING (creditbillnum)
614 JOIN cust_credit USING (crednum)
615 LEFT JOIN reason USING (reasonnum)
616 LEFT JOIN access_user USING (usernum)
617 GROUP BY billpkgnum, reason.reason, access_user.username";
618 $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit USING (billpkgnum)";
621 push @where, 'item_credit.billpkgnum IS NOT NULL';
622 push @select, 'item_credit.credit_amount',
623 'item_credit.username_text',
624 'item_credit.reason_text';
625 push @peritem, 'credit_amount', 'username_text', 'reason_text';
626 push @peritem_desc, 'Credited', 'By', 'Reason';
627 push @total, 'SUM(credit_amount)';
628 push @total_desc, "$money_char%.2f credited";
632 #still want a credit total column
635 SELECT SUM(cust_credit_bill_pkg.amount)
636 FROM cust_credit_bill_pkg
637 WHERE cust_bill_pkg.billpkgnum = cust_credit_bill_pkg.billpkgnum
639 push @select, "($credit_sub) AS credit_amount";
643 push @select, 'cust_main.custnum', FS::UI::Web::cust_sql_fields();
646 if ( $cgi->param('salesnum') =~ /^(\d+)$/ ) {
649 my $sales = FS::sales->by_key($salesnum)
650 or die "salesnum $salesnum not found";
652 my $subsearch = $sales->cust_bill_pkg_search('', '',
653 'cust_main_sales' => ($cgi->param('cust_main_sales') ? 1 : 0),
654 'paid' => ($cgi->param('paid') ? 1 : 0),
655 'classnum' => scalar($cgi->param('classnum'))
657 $join_pkg .= " JOIN sales_pkg_class ON ( COALESCE(sales_pkg_class.classnum, 0) = COALESCE( part_pkg.classnum, 0) )";
659 my $extra_sql = $subsearch->{extra_sql};
660 $extra_sql =~ s/^WHERE//;
661 push @where, $extra_sql;
663 $cgi->param('classnum', 0) unless $cgi->param('classnum');
667 my $where = join(' AND ', @where);
668 $where &&= "WHERE $where";
671 'table' => 'cust_bill_pkg',
672 'addl_from' => "$join_pkg $join_cust",
674 'select' => join(",\n", @select ),
675 'extra_sql' => $where,
676 'order_by' => 'ORDER BY cust_bill._date, cust_bill_pkg.billpkgnum',
680 'SELECT ' . join(',', @total) .
681 " FROM cust_bill_pkg $join_pkg $join_cust
684 @peritem_desc = map {emt($_)} @peritem_desc;
685 my @peritem_sub = map {
687 if ($field =~ /_text$/) { # kludge for credit reason/username fields
688 sub {$_[0]->get($field)};
690 sub { sprintf($money_char.'%.2f', $_[0]->get($field)) }
693 my @peritem_null = map { '' } @peritem; # placeholders
694 my $peritem_align = 'r' x scalar(@peritem);
696 my $ilink = [ "${p}view/cust_bill.cgi?", 'invnum' ];
697 my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ];
699 my $pay_link = ''; #[, 'billpkgnum', ];
700 my $credit_link = [ "${p}search/cust_credit_bill_pkg.html?billpkgnum=", 'billpkgnum', ];
702 warn "\n\nQUERY:\n".Dumper($query)."\n\nCOUNT_QUERY:\n$count_query\n\n"
703 if $cgi->param('debug');