1 <& elements/search.html,
2 'title' => emt('Line items'),
3 'name' => emt('line items'),
5 'count_query' => $count_query,
6 'count_addl' => \@total_desc,
15 FS::UI::Web::cust_header(),
18 sub { $_[0]->pkgnum > 0
19 ? $_[0]->get('pkg') # possibly use override.pkg
20 : $_[0]->get('itemdesc') # but i think this correct
23 #strikethrough or "N/A ($amount)" or something these when
24 # they're not applicable to pkg_tax search
27 sub { time2str('%b %d %Y', shift->_date ) },
28 sub { sprintf($money_char.'%.2f', shift->get('pay_amount')) },
29 sub { sprintf($money_char.'%.2f', shift->get('credit_amount')) },
30 \&FS::UI::Web::cust_fields,
50 ( map { $_ ne 'Cust. Status' ? $clink : '' }
51 FS::UI::Web::cust_header()
54 #'align' => 'rlrrrc'.FS::UI::Web::cust_aligns(),
59 FS::UI::Web::cust_aligns(),
69 FS::UI::Web::cust_colors(),
80 FS::UI::Web::cust_styles(),
85 Output control parameters:
86 - distribute: Boolean. If true, recurring fees will be "prorated" for the
87 portion of the package date range (sdate-edate) that falls within the date
88 range of the report. Line items will be limited to those for which this
89 portion is > 0. This disables filtering on invoice date.
91 - usage: Separate usage (cust_bill_pkg_detail records) from
92 recurring charges. If set to "usage", will show usage instead of
93 recurring charges. If set to "recurring", will deduct usage and only
94 show the flat rate charge. If not passed, the "recurring charge" column
95 will include usage charges also.
98 - begin, end: Date range. Applies to invoice date, not necessarily package
99 date range. But see "distribute".
101 - status: Customer status (active, suspended, etc.). This will filter on
102 _current_ customer status, not status at the time the invoice was generated.
104 - agentnum: Filter on customer agent.
106 - refnum: Filter on customer reference source.
108 - classnum: Filter on package class.
110 - use_override: Apply "classnum" and "taxclass" filtering based on the
111 override (bundle) pkgpart, rather than always using the true pkgpart.
113 - nottax: Limit to items that are not taxes (pkgnum > 0).
115 - istax: Limit to items that are taxes (pkgnum == 0).
117 - taxnum: Limit to items whose tax definition matches this taxnum.
118 With "nottax" that means items that are subject to that tax;
119 with "istax" it's the tax charges themselves. Can be specified
120 more than once to include multiple taxes.
122 - country, state, county, city: Limit to items whose tax location
123 matches these fields. If "nottax" it's the tax location of the package;
124 if "istax" the location of the tax.
126 - taxname, taxnameNULL: With "nottax", limit to items whose tax location
127 matches a tax with this name. With "istax", limit to items that have
128 this tax name. taxnameNULL is equivalent to "taxname = '' OR taxname
131 - out: With "nottax", limit to items that don't match any tax definition.
132 With "istax", find tax items that are unlinked to their tax definitions.
133 Current Freeside (> July 2012) always creates tax links, but unlinked
134 items may result from an incomplete upgrade of legacy data.
136 - locationtaxid: With "nottax", limit to packages matching this
137 tax_rate_location ID; with "tax", limit to taxes generated from that
140 - taxclass: Filter on package taxclass.
142 - taxclassNULL: With "nottax", limit to items that would be subject to the
143 tax with taxclass = NULL. This doesn't necessarily mean part_pkg.taxclass
144 is NULL; it also includes taxclasses that don't have a tax in this region.
146 - itemdesc: Limit to line items with this description. Note that non-tax
147 packages usually have a description of NULL. (Deprecated.)
149 - report_group: Can contain '=' or '!=' followed by a string to limit to
150 line items where itemdesc starts with, or doesn't start with, the string.
152 - cust_tax: Limit to customers who are tax-exempt. If "taxname" is also
153 specified, limit to customers who are also specifically exempt from that
156 - pkg_tax: Limit to packages that are tax-exempt, and only include the
157 exempt portion (setup, recurring, or both) when calculating totals.
159 - taxable: Limit to packages that are subject to tax, i.e. where a
160 cust_bill_pkg_tax_location record exists.
162 - credit: Limit to line items that received a credit application. The
163 amount of the credit will also be shown.
169 unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
171 my $conf = new FS::Conf;
172 my $money_char = $conf->config('money_char') || '$';
174 my @select = ( 'cust_bill_pkg.*', 'cust_bill._date' );
175 my @total = ( 'COUNT(*)', 'SUM(cust_bill_pkg.setup + cust_bill_pkg.recur)');
176 my @total_desc = ( '%d line items', $money_char.'%.2f total' ); # sprintf strings
178 my @peritem = ( 'setup', 'recur' );
179 my @peritem_desc = ( 'Setup charge', 'Recurring charge' );
181 my @post_desc_header = ();
183 my @post_desc_null = ();
184 my $post_desc_align = '';
185 if ( $conf->exists('enable_taxclasses') ) {
186 push @post_desc_header, 'Tax class';
187 push @post_desc, 'taxclass';
188 push @post_desc_null, '';
189 $post_desc_align .= 'l';
190 push @select, 'part_pkg.taxclass'; # or should this use override?
193 # valid in both the tax and non-tax cases
195 " LEFT JOIN cust_bill USING (invnum)
196 LEFT JOIN cust_main USING (custnum)
199 #agent virtualization
201 $FS::CurrentUser::CurrentUser->agentnums_sql( 'table' => 'cust_main' );
203 my @where = ( $agentnums_sql );
206 my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
208 if ( $cgi->param('distribute') == 1 ) {
209 push @where, "sdate <= $ending",
210 "edate > $beginning",
214 push @where, "cust_bill._date >= $beginning",
215 "cust_bill._date <= $ending";
219 if ( $cgi->param('status') =~ /^([a-z]+)$/ ) {
220 push @where, FS::cust_main->cust_status_sql . " = '$1'";
224 if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
225 push @where, "cust_main.agentnum = $1";
229 if ( $cgi->param('refnum') =~ /^(\d+)$/ ) {
230 push @where, "cust_main.refnum = $1";
234 if ( $cgi->param('custnum') =~ /^(\d+)$/ ) {
235 push @where, "cust_main.custnum = $1";
238 # we want the package and its definition if available
240 ' LEFT JOIN cust_pkg USING (pkgnum)
241 LEFT JOIN part_pkg USING (pkgpart)';
243 my $part_pkg = 'part_pkg';
244 if ( $cgi->param('use_override') ) {
245 # still need the real part_pkg for tax applicability,
247 $join_pkg .= " LEFT JOIN part_pkg AS override ON (
248 COALESCE(cust_bill_pkg.pkgpart_override, cust_pkg.pkgpart, 0) = part_pkg.pkgpart
250 $part_pkg = 'override';
252 push @select, 'part_pkg.pkg'; # or should this use override?
255 if ( $cgi->param('nottax') ) {
257 push @where, 'cust_bill_pkg.pkgnum > 0';
259 my @tax_where; # will go into a subquery
260 my @exempt_where; # will also go into a subquery
262 # classnum (of override pkgpart if applicable)
263 # not specified: all classes
266 if ( $cgi->param('classnum') =~ /^(\d+)$/ ) {
267 push @where, "COALESCE($part_pkg.classnum, 0) = $1";
271 if ( $cgi->param('taxclassNULL') ) {
272 # a little different from 'taxclass' in that it applies to the
273 # effective taxclass, not the real one
274 push @tax_where, 'cust_main_county.taxclass IS NULL'
275 } elsif ( $cgi->param('taxclass') ) {
276 push @tax_where, "$part_pkg.taxclass IN (" .
277 join(', ', map {dbh->quote($_)} $cgi->param('taxclass') ).
281 if ( $cgi->param('exempt_cust') eq 'Y' ) {
282 # tax-exempt customers
283 push @exempt_where, "(exempt_cust = 'Y' OR exempt_cust_taxname = 'Y')";
285 } elsif ( $cgi->param('exempt_pkg') eq 'Y' ) { # non-taxable package
286 # non-taxable package charges
287 push @exempt_where, "(exempt_setup = 'Y' OR exempt_recur = 'Y')";
289 # we don't handle exempt_monthly here
291 if ( $cgi->param('taxname') ) { # specific taxname
292 push @tax_where, 'cust_main_county.taxname = '.
293 dbh->quote($cgi->param('taxname'));
294 } elsif ( $cgi->param('taxnameNULL') ) {
295 push @tax_where, 'cust_main_county.taxname IS NULL OR '.
296 'cust_main_county.taxname = \'Tax\'';
299 # country:state:county:city:district (may be repeated)
300 # You can also pass a big list of taxnums but that leads to huge URLs.
301 # Note that this means "packages whose tax is in this region", not
302 # "packages in this region". It's meant for links from the tax report.
303 if ( $cgi->param('region') ) {
305 foreach ( $cgi->param('region') ) {
307 @loc{qw(country state county city district)} =
308 split(':', $cgi->param('region'));
309 my $string = join(' AND ',
312 "$_ = ".dbh->quote($loc{$_});
318 push @orwhere, "($string)";
320 push @tax_where, '(' . join(' OR ', @orwhere) . ')' if @orwhere;
324 if ( $cgi->param('taxnum') ) {
325 my $taxnum_in = join(',',
326 grep /^\d+$/, $cgi->param('taxnum')
328 push @tax_where, "cust_main_county.taxnum IN ($taxnum_in)"
332 # If we're showing exempt items, we need to find those with
333 # cust_tax_exempt_pkg records matching the selected taxes.
334 # If we're showing taxable items, we need to find those with
335 # cust_bill_pkg_tax_location records. We also need to find the
336 # exemption records so that we can show the taxable amount.
337 # If we're showing all items, we need the union of those.
338 # If we're showing 'out' (items that aren't region/class taxable),
339 # then we need the set of all items minus the union of those.
343 if ( @exempt_where or @tax_where
344 or $cgi->param('taxable') or $cgi->param('out') )
346 # process exemption restrictions, including @tax_where
347 my $exempt_sub = 'SELECT SUM(amount) as exempt_amount, billpkgnum
348 FROM cust_tax_exempt_pkg JOIN cust_main_county USING (taxnum)';
350 $exempt_sub .= ' WHERE '.join(' AND ', @tax_where, @exempt_where)
351 if (@tax_where or @exempt_where);
353 $exempt_sub .= ' GROUP BY billpkgnum';
355 $join_pkg .= " LEFT JOIN ($exempt_sub) AS item_exempt
359 if ( @tax_where or $cgi->param('taxable') or $cgi->param('out') ) {
360 # process tax restrictions
362 'cust_main_county.tax > 0';
364 my $tax_sub = "SELECT invnum, cust_bill_pkg_tax_location.pkgnum
365 FROM cust_bill_pkg_tax_location
366 JOIN cust_bill_pkg AS tax_item USING (billpkgnum)
367 JOIN cust_main_county USING (taxnum)
368 WHERE ". join(' AND ', @tax_where).
369 " GROUP BY invnum, cust_bill_pkg_tax_location.pkgnum";
371 $join_pkg .= " LEFT JOIN ($tax_sub) AS item_tax
372 ON (item_tax.invnum = cust_bill_pkg.invnum AND
373 item_tax.pkgnum = cust_bill_pkg.pkgnum)";
376 # now do something with that
377 if ( @exempt_where ) {
379 push @where, 'item_exempt.billpkgnum IS NOT NULL';
380 push @select, 'item_exempt.exempt_amount';
381 push @peritem, 'exempt_amount';
382 push @peritem_desc, 'Exempt';
383 push @total, 'SUM(exempt_amount)';
384 push @total_desc, "$money_char%.2f tax-exempt";
386 } elsif ( $cgi->param('taxable') ) {
388 my $taxable = 'cust_bill_pkg.setup + cust_bill_pkg.recur '.
389 '- COALESCE(item_exempt.exempt_amount, 0)';
391 push @where, 'item_tax.invnum IS NOT NULL';
392 push @select, "($taxable) AS taxable_amount";
393 push @peritem, 'taxable_amount';
394 push @peritem_desc, 'Taxable';
395 push @total, "SUM($taxable)";
396 push @total_desc, "$money_char%.2f taxable";
398 } elsif ( $cgi->param('out') ) {
400 push @where, 'item_tax.invnum IS NULL',
401 'item_exempt.billpkgnum IS NULL';
403 } elsif ( @tax_where ) {
405 # union of taxable + all exempt_ cases
407 '(item_tax.invnum IS NOT NULL OR item_exempt.billpkgnum IS NOT NULL)';
411 # recur/usage separation
412 if ( $cgi->param('usage') eq 'recurring' ) {
414 my $recur_no_usage = FS::cust_bill_pkg->charged_sql('', '', no_usage => 1);
415 push @select, "($recur_no_usage) AS recur_no_usage";
416 $peritem[1] = 'recur_no_usage';
417 $total[1] = "SUM(cust_bill_pkg.setup + $recur_no_usage)";
418 $total_desc[1] .= ' (excluding usage)';
420 } elsif ( $cgi->param('usage') eq 'usage' ) {
422 my $usage = FS::cust_bill_pkg->usage_sql();
423 push @select, "($usage) AS _usage";
424 # there's already a method named 'usage'
425 $peritem[1] = '_usage';
426 $peritem_desc[1] = 'Usage charge';
427 $total[1] = "SUM($usage)";
428 $total_desc[1] .= ' usage charges';
431 } elsif ( $cgi->param('istax') ) {
433 @peritem = ( 'setup' ); # taxes only have setup
434 @peritem_desc = ( 'Tax charge' );
436 push @where, 'cust_bill_pkg.pkgnum = 0';
438 # tax location when using tax_rate_location
439 if ( scalar( grep( /locationtaxid/, $cgi->param ) ) ) {
441 $join_pkg .= ' LEFT JOIN cust_bill_pkg_tax_rate_location USING ( billpkgnum ) '.
442 ' LEFT JOIN tax_rate_location USING ( taxratelocationnum )';
443 push @where, FS::tax_rate_location->location_sql(
444 map { $_ => (scalar($cgi->param($_)) || '') }
445 qw( district city county state locationtaxid )
449 COALESCE(cust_bill_pkg_tax_rate_location.amount,
450 cust_bill_pkg.setup + cust_bill_pkg.recur)
453 } elsif ( $cgi->param('out') ) {
456 LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum)
458 push @where, 'cust_bill_pkg_tax_location.billpkgnum IS NULL';
460 # each billpkgnum should appear only once
461 $total[0] = 'COUNT(*)';
462 $total[1] = 'SUM(cust_bill_pkg.setup)';
464 } else { # not locationtaxid or 'out'--the normal case
467 LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum)
468 JOIN cust_main_county USING (taxnum)
471 # don't double-count the components of consolidated taxes
472 $total[0] = 'COUNT(DISTINCT cust_bill_pkg.billpkgnum)';
473 $total[1] = 'SUM(cust_bill_pkg_tax_location.amount)';
477 if ( $cgi->param('taxclassNULL') ) {
478 push @where, 'cust_main_county.taxclass IS NULL';
482 if ( $cgi->param('taxnameNULL') ) {
483 push @where, 'cust_main_county.taxname IS NULL OR '.
484 'cust_main_county.taxname = \'Tax\'';
485 } elsif ( $cgi->param('taxname') ) {
486 push @where, 'cust_main_county.taxname = '.
487 dbh->quote($cgi->param('taxname'));
491 if ( $cgi->param('taxnum') ) {
492 my $taxnum_in = join(',',
493 grep /^\d+$/, $cgi->param('taxnum')
495 push @where, "cust_main_county.taxnum IN ($taxnum_in)"
499 # report group (itemdesc)
500 if ( $cgi->param('report_group') =~ /^(=|!=) (.*)$/ ) {
501 my ( $group_op, $group_value ) = ( $1, $2 );
502 if ( $group_op eq '=' ) {
503 #push @where, 'itemdesc LIKE '. dbh->quote($group_value.'%');
504 push @where, 'itemdesc = '. dbh->quote($group_value);
505 } elsif ( $group_op eq '!=' ) {
506 push @where, '( itemdesc != '. dbh->quote($group_value) .' OR itemdesc IS NULL )';
508 die "guru meditation #00de: group_op $group_op\n";
512 # itemdesc, for some reason
513 if ( $cgi->param('itemdesc') ) {
514 if ( $cgi->param('itemdesc') eq 'Tax' ) {
515 push @where, "(itemdesc='Tax' OR itemdesc is null)";
517 push @where, 'itemdesc='. dbh->quote($cgi->param('itemdesc'));
525 my $pay_sub = "SELECT SUM(cust_bill_pay_pkg.amount) AS pay_amount,
527 FROM cust_bill_pay_pkg
528 GROUP BY billpkgnum";
529 $join_pkg .= " LEFT JOIN ($pay_sub) AS item_pay USING (billpkgnum)";
530 push @select, 'item_pay.pay_amount';
534 if ( $cgi->param('credit') ) {
538 if ( $cgi->param('istax') ) {
539 # then we need to group/join by billpkgtaxlocationnum, to get only the
540 # relevant part of partial taxes
541 my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
542 reason.reason as reason_text, access_user.username AS username_text,
543 billpkgtaxlocationnum, billpkgnum
544 FROM cust_credit_bill_pkg
545 JOIN cust_credit_bill USING (creditbillnum)
546 JOIN cust_credit USING (crednum)
547 LEFT JOIN reason USING (reasonnum)
548 LEFT JOIN access_user USING (usernum)
549 GROUP BY billpkgnum, billpkgtaxlocationnum, reason.reason,
550 access_user.username";
552 if ( $cgi->param('out') ) {
554 # find credits that are applied to the line items, but not to
555 # a cust_bill_pkg_tax_location link
556 $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
558 push @where, 'item_credit.billpkgtaxlocationnum IS NULL';
562 # find credits that are applied to the CBPTL links that are
563 # considered "interesting" by the report criteria
564 $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
565 USING (billpkgtaxlocationnum)";
570 # then only group by billpkgnum
571 my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
572 reason.reason as reason_text, access_user.username AS username_text,
574 FROM cust_credit_bill_pkg
575 JOIN cust_credit_bill USING (creditbillnum)
576 JOIN cust_credit USING (crednum)
577 LEFT JOIN reason USING (reasonnum)
578 LEFT JOIN access_user USING (usernum)
579 GROUP BY billpkgnum, reason.reason, access_user.username";
580 $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit USING (billpkgnum)";
583 push @where, 'item_credit.billpkgnum IS NOT NULL';
584 push @select, 'item_credit.credit_amount',
585 'item_credit.username_text',
586 'item_credit.reason_text';
587 push @peritem, 'credit_amount', 'username_text', 'reason_text';
588 push @peritem_desc, 'Credited', 'By', 'Reason';
589 push @total, 'SUM(credit_amount)';
590 push @total_desc, "$money_char%.2f credited";
594 #still want a credit total column
596 my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
598 FROM cust_credit_bill_pkg
599 GROUP BY billpkgnum";
600 $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit USING (billpkgnum)";
602 push @select, 'item_credit.credit_amount';
606 push @select, 'cust_main.custnum', FS::UI::Web::cust_sql_fields();
608 my $where = join(' AND ', @where);
609 $where &&= "WHERE $where";
612 'table' => 'cust_bill_pkg',
613 'addl_from' => "$join_cust $join_pkg",
615 'select' => join(",\n", @select ),
616 'extra_sql' => $where,
617 'order_by' => 'ORDER BY cust_bill._date, cust_bill_pkg.billpkgnum',
621 'SELECT ' . join(',', @total) .
622 " FROM cust_bill_pkg $join_cust $join_pkg
625 shift @total_desc; #the first one is implicit
627 @peritem_desc = map {emt($_)} @peritem_desc;
628 my @peritem_sub = map {
630 if ($field =~ /_text$/) { # kludge for credit reason/username fields
631 sub {$_[0]->get($field)};
633 sub { sprintf($money_char.'%.2f', $_[0]->get($field)) }
636 my @peritem_null = map { '' } @peritem; # placeholders
637 my $peritem_align = 'r' x scalar(@peritem);
639 my $ilink = [ "${p}view/cust_bill.cgi?", 'invnum' ];
640 my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ];
642 my $pay_link = ''; #[, 'billpkgnum', ];
643 my $credit_link = [ "${p}search/cust_credit_bill_pkg.html?billpkgnum=", 'billpkgnum', ];
645 warn "\n\nQUERY:\n".Dumper($query)."\n\nCOUNT_QUERY:\n$count_query\n\n"
646 if $cgi->param('debug');