httemplate/search/cust_bill_pkg.cgi

   1 <& elements/search.html,
   2                  'title'       => emt('Line items'),
   3                  'name'        => emt('line items'),
   4                  'query'       => $query,
   5                  'count_query' => $count_query,
   6                  'count_addl'  => \@total_desc,
   7                  'header'      => [
   8                    @pkgnum_header,
   9                    emt('Pkg Def'),
  10                    emt('Description'),
  11                    @post_desc_header,
  12                    @peritem_desc,
  13                    @currency_desc,
  14                    emt('Invoice'),
  15                    emt('Date'),
  16                    emt('Paid'),
  17                    emt('Credited'),
  18                    FS::UI::Web::cust_header(),
  19                  ],
  20                  'fields'      => [
  21                    @pkgnum,
  22                    sub { $_[0]->pkgnum > 0
  23                            ? $_[0]->get('pkgpart')
  24                            : ''
  25                        },
  26                    'itemdesc', # is part_pkg.pkg if applicable
  27                    @post_desc,
  28                    #strikethrough or "N/A ($amount)" or something these when
  29                    # they're not applicable to pkg_tax search
  30                    @peritem_sub,
  31                    @currency_sub,
  32                    'invnum',
  33                    sub { time2str('%b %d %Y', shift->_date ) },
  34                    sub { sprintf($money_char.'%.2f', shift->get('pay_amount')) },
  35                    sub { sprintf($money_char.'%.2f', shift->get('credit_amount')) },
  36                    \&FS::UI::Web::cust_fields,
  37                  ],
  38                  'sort_fields' => [
  39                    @pkgnum_null,
  40                    '',
  41                    '',
  42                    @post_desc_null,
  43                    @peritem,
  44                    @currency,
  45                    'invnum',
  46                    '_date',
  47                    '', #'pay_amount',
  48                    '', #'credit_amount',
  49                    FS::UI::Web::cust_sort_fields(),
  50                  ],
  51                  'links'       => [
  52                    @pkgnum_null,
  53                    '',
  54                    '',
  55                    @post_desc_null,
  56                    @peritem_null,
  57                    @currency_null,
  58                    $ilink,
  59                    $ilink,
  60                    $pay_link,
  61                    $credit_link,
  62                    ( map { $_ ne 'Cust. Status' ? $clink : '' }
  63                          FS::UI::Web::cust_header()
  64                    ),
  65                  ],
  66                  #'align' => 'rlrrrc'.FS::UI::Web::cust_aligns(),
  67                  'align' => $pkgnum_align.
  68                             'rl'.
  69                             $post_desc_align.
  70                             $peritem_align.
  71                             $currency_align.
  72                             'rcrr'.
  73                             FS::UI::Web::cust_aligns(),
  74                  'color' => [
  75                               @pkgnum_null,
  76                               '',
  77                               '',
  78                               @post_desc_null,
  79                               @peritem_null,
  80                               @currency_null,
  81                               '',
  82                               '',
  83                               '',
  84                               '',
  85                               FS::UI::Web::cust_colors(),
  86                             ],
  87                  'style' => [
  88                               @pkgnum_null,
  89                               '',
  90                               '',
  91                               @post_desc_null,
  92                               @peritem_null,
  93                               @currency_null,
  94                               '',
  95                               '',
  96                               '',
  97                               '',
  98                               FS::UI::Web::cust_styles(),
  99                             ],
 100 &>
 101 <%doc>
 102
 103 Output control parameters:
 104 - distribute: Boolean.  If true, recurring fees will be "prorated" for the
 105   portion of the package date range (sdate-edate) that falls within the date
 106   range of the report.  Line items will be limited to those for which this
 107   portion is > 0.  This disables filtering on invoice date.
 108
 109 - charges: 'S'etup, 'R'ecur, 'U'sage, or any string combination of those.
 110
 111 Filtering parameters:
 112 - begin, end: Date range.  Applies to invoice date, not necessarily package
 113   date range.  But see "distribute".
 114
 115 - status: Customer status (active, suspended, etc.).  This will filter on
 116   _current_ customer status, not status at the time the invoice was generated.
 117
 118 - agentnum: Filter on customer agent.
 119
 120 - refnum: Filter on customer reference source.
 121
 122 - cust_classnum: Filter on customer class.
 123
 124 - classnum: Filter on package class.
 125
 126 - report_optionnum: Filter on package report class.  Can be a single report
 127   class number or a comma-separated list (where 0 is "no report class"), or the
 128   word "multiple".
 129
 130 - use_override: Apply "classnum" and "taxclass" filtering based on the
 131   override (bundle) pkgpart, rather than always using the true pkgpart.
 132
 133 - nottax: Limit to items that are not taxes (pkgnum > 0 or feepart > 0).
 134
 135 - istax: Limit to items that are taxes (pkgnum == 0 and feepart = null).
 136
 137 - taxnum: Limit to items whose tax definition matches this taxnum.
 138   With "nottax" that means items that are subject to that tax;
 139   with "istax" it's the tax charges themselves.  Can be a comma-separated
 140   list to include multiple taxes.
 141
 142 - country, state, county, city: Limit to items whose tax location
 143   matches these fields.  If "nottax" it's the tax location of the package;
 144   if "istax" the location of the tax.
 145
 146 - taxname, taxnameNULL: With "nottax", limit to items whose tax location
 147   matches a tax with this name.  With "istax", limit to items that have
 148   this tax name.  taxnameNULL is equivalent to "taxname = '' OR taxname
 149   = 'Tax'".
 150
 151 - out: With "nottax", limit to items that don't match any tax definition.
 152   With "istax", find tax items that are unlinked to their tax definitions.
 153   Current Freeside (> July 2012) always creates tax links, but unlinked
 154   items may result from an incomplete upgrade of legacy data.
 155
 156 - locationtaxid: With "nottax", limit to packages matching this
 157   tax_rate_location ID; with "tax", limit to taxes generated from that
 158   location.
 159
 160 - taxclass: Filter on package taxclass.
 161
 162 - taxclassNULL: With "nottax", limit to items that would be subject to the
 163   tax with taxclass = NULL.  This doesn't necessarily mean part_pkg.taxclass
 164   is NULL; it also includes taxclasses that don't have a tax in this region.
 165
 166 - itemdesc: Limit to line items with this description.  Note that non-tax
 167   packages usually have a description of NULL.  (Deprecated.)
 168
 169 - report_group: Can contain '=' or '!=' followed by a string to limit to
 170   line items where itemdesc starts with, or doesn't start with, the string.
 171
 172 - cust_tax: Limit to customers who are tax-exempt.  If "taxname" is also
 173   specified, limit to customers who are also specifically exempt from that
 174   tax.
 175
 176 - pkg_tax: Limit to packages that are tax-exempt, and only include the
 177   exempt portion (setup, recurring, or both) when calculating totals.
 178
 179 - taxable: Limit to packages that are subject to tax, i.e. where a
 180   cust_bill_pkg_tax_location record exists.
 181
 182 - credit: Limit to line items that received a credit application.  The
 183   amount of the credit will also be shown.
 184
 185 </%doc>
 186 <%init>
 187
 188 my $curuser = $FS::CurrentUser::CurrentUser;
 189
 190 die "access denied" unless $curuser->access_right('Financial reports');
 191
 192 my $conf = new FS::Conf;
 193 my $money_char = $conf->config('money_char') || '$';
 194
 195 my @select = ( 'cust_bill_pkg.*', 'cust_bill._date' );
 196 my @total = ( 'COUNT(*)' );
 197 my @total_desc = ();
 198
 199 my @peritem = ( 'setup', 'recur' );
 200 my @peritem_desc = ( 'Setup charges', 'Recurring charges' );
 201
 202 my @currency_desc = ();
 203 my @currency_sub = ();
 204 my @currency = ();
 205 if ( $conf->config('currencies') ) {
 206   @currency_desc = ( 'Setup billed', 'Recurring billed' );
 207   @currency_sub = (
 208     map {
 209       my $what = $_;
 210       sub { my $currency = $_[0]->get($what.'_billed_currency') or return '';
 211             $currency. ' '. currency_symbol($currency, SYM_HTML).
 212               $_[0]->get($what.'_billed_amount');
 213           };
 214     } qw( setup recur )
 215   );
 216   @currency = ( 'setup_billed_amount', 'recur_billed_amount' ); #for sorting
 217 }
 218
 219 my @pkgnum_header = ();
 220 my @pkgnum = ();
 221 my @pkgnum_null;
 222 my $pkgnum_align = '';
 223 if ( $curuser->option('show_pkgnum') ) {
 224   push @select, 'cust_bill_pkg.pkgnum';
 225   push @pkgnum_header, 'Pkg Num';
 226   push @pkgnum, sub { $_[0]->pkgnum > 0 ? $_[0]->pkgnum : '' };
 227   push @pkgnum_null, '';
 228   $pkgnum_align .= 'r';
 229 }
 230
 231 my @post_desc_header = ();
 232 my @post_desc = ();
 233 my @post_desc_null = ();
 234 my $post_desc_align = '';
 235 if ( $conf->exists('enable_taxclasses') ) {
 236   push @post_desc_header, 'Tax class';
 237   push @post_desc, 'taxclass';
 238   push @post_desc_null, '';
 239   $post_desc_align .= 'l';
 240 }
 241
 242 # used in several places
 243 my $itemdesc = 'COALESCE(cust_bill_pkg.itemdesc, part_fee.itemdesc, part_pkg.pkg, cust_bill_pkg.itemdesc)';
 244
 245 # valid in both the tax and non-tax cases
 246 my $join_cust =
 247   " LEFT JOIN cust_bill ON (cust_bill_pkg.invnum = cust_bill.invnum)".
 248   # use cust_pkg.locationnum if it exists
 249   FS::UI::Web::join_cust_main('cust_bill', 'cust_pkg');
 250
 251 #agent virtualization
 252 my $agentnums_sql =
 253   $FS::CurrentUser::CurrentUser->agentnums_sql( 'table' => 'cust_main' );
 254
 255 my @where = ( $agentnums_sql );
 256
 257 # date range
 258 my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
 259
 260 if ( $cgi->param('distribute') == 1 ) {
 261   push @where, "sdate <= $ending",
 262                "edate >  $beginning",
 263   ;
 264 } else {
 265   push @where, "cust_bill._date >= $beginning",
 266                "cust_bill._date <= $ending";
 267 }
 268
 269 # status
 270 if ( $cgi->param('status') =~ /^([a-z]+)$/ ) {
 271   push @where, FS::cust_main->cust_status_sql . " = '$1'";
 272 }
 273
 274 # agentnum
 275 if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
 276   push @where, "cust_main.agentnum = $1";
 277 }
 278
 279 # salesnum--see below
 280 # refnum
 281 if ( $cgi->param('refnum') =~ /^(\d+)$/ ) {
 282   push @where, "cust_main.refnum = $1";
 283 }
 284
 285 # 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)
 286 if ( grep { $_ eq 'cust_classnum' } $cgi->param ) {
 287   my @classnums = grep /^\d*$/, $cgi->param('cust_classnum');
 288   push @where, 'COALESCE( cust_main.classnum, 0) IN ( '.
 289                    join(',', map { $_ || '0' } @classnums ).
 290                ' )'
 291     if @classnums;
 292 }
 293
 294
 295 # custnum
 296 if ( $cgi->param('custnum') =~ /^(\d+)$/ ) {
 297   push @where, "cust_main.custnum = $1";
 298 }
 299
 300 # we want the package and its definition if available
 301 my $join_pkg =
 302 ' LEFT JOIN cust_pkg      USING (pkgnum)
 303   LEFT JOIN part_pkg      USING (pkgpart)
 304   LEFT JOIN part_fee      USING (feepart)';
 305
 306 my $part_pkg = 'part_pkg';
 307 # "Separate sub-packages from parents"
 308 my $use_override = $cgi->param('use_override') ? 1 : 0;
 309 if ( $use_override ) {
 310   # still need the real part_pkg for tax applicability,
 311   # so alias this one
 312   $join_pkg .= " LEFT JOIN part_pkg AS override ON (
 313   COALESCE(cust_bill_pkg.pkgpart_override, cust_pkg.pkgpart, 0) = override.pkgpart
 314   )";
 315   $part_pkg = 'override';
 316 }
 317 push @select, "$part_pkg.pkgpart", "$part_pkg.pkg";
 318 push @select, "($itemdesc) AS itemdesc"; # available in all report modes
 319
 320 push @select, "COALESCE($part_pkg.taxclass, part_fee.taxclass) AS taxclass"
 321   if $conf->exists('enable_taxclasses');
 322
 323 # the non-tax case
 324 if ( $cgi->param('nottax') ) {
 325
 326   push @where,
 327     '(cust_bill_pkg.pkgnum > 0 OR cust_bill_pkg.feepart IS NOT NULL)';
 328
 329   my @tax_where; # will go into a subquery
 330   my @exempt_where; # will also go into a subquery
 331
 332   # classnum (of override pkgpart if applicable)
 333   # not specified: all classes
 334   # 0: empty class
 335   # N: classnum
 336   if ( grep { $_ eq 'classnum' } $cgi->param ) {
 337     my @classnums = grep /^\d+$/, $cgi->param('classnum');
 338     push @where, "COALESCE(part_fee.classnum, $part_pkg.classnum, 0) IN ( ".
 339                      join(',', @classnums ).
 340                  ' )'
 341       if @classnums;
 342   }
 343
 344   if ( grep { $_ eq 'report_optionnum' } $cgi->param ) {
 345     my $num = join(',', grep /^[\d,]+$/, $cgi->param('report_optionnum'));
 346     my $not_num = join(',', grep /^[\d,]+$/, $cgi->param('not_report_optionnum'));
 347     my $all = $cgi->param('all_report_options') ? 1 : 0;
 348     push @where, # code reuse FTW
 349       FS::Report::Table->with_report_option(
 350         report_optionnum      => $num,
 351         not_report_optionnum  => $not_num,
 352         use_override          => $use_override,
 353         all_report_options    => $all,
 354       );
 355   }
 356
 357   # taxclass
 358   if ( $cgi->param('taxclassNULL') ) {
 359     # a little different from 'taxclass' in that it applies to the
 360     # effective taxclass, not the real one
 361     push @tax_where, 'cust_main_county.taxclass IS NULL'
 362   } elsif ( $cgi->param('taxclass') ) {
 363     push @tax_where, "COALESCE(part_fee.taxclass, $part_pkg.taxclass) IN (" .
 364                  join(', ', map {dbh->quote($_)} $cgi->param('taxclass') ).
 365                  ')';
 366   }
 367
 368   if ( $cgi->param('exempt_cust') eq 'Y' ) {
 369     # tax-exempt customers
 370     push @exempt_where, "(exempt_cust = 'Y' OR exempt_cust_taxname = 'Y')";
 371
 372   } elsif ( $cgi->param('exempt_pkg') eq 'Y' ) { # non-taxable package
 373     # non-taxable package charges
 374     push @exempt_where, "(exempt_setup = 'Y' OR exempt_recur = 'Y')";
 375   }
 376   # we don't handle exempt_monthly here
 377
 378   if ( $cgi->param('taxname') ) { # specific taxname
 379       push @tax_where, "COALESCE(cust_main_county.taxname, 'Tax') = ".
 380                         dbh->quote($cgi->param('taxname'));
 381   }
 382
 383   # country:state:county:city:district (may be repeated)
 384   # You can also pass a big list of taxnums but that leads to huge URLs.
 385   # Note that this means "packages whose tax is in this region", not
 386   # "packages in this region".  It's meant for links from the tax report.
 387   if ( $cgi->param('region') ) {
 388     my @orwhere;
 389     foreach ( $cgi->param('region') ) {
 390       my %loc;
 391       @loc{qw(country state county city district)} =
 392         split(':', $cgi->param('region'));
 393       my $string = join(' AND ',
 394             map {
 395               if ( $loc{$_} ) {
 396                 "$_ = ".dbh->quote($loc{$_});
 397               } else {
 398                 "$_ IS NULL";
 399               }
 400             } keys(%loc)
 401       );
 402       push @orwhere, "($string)";
 403     }
 404     push @tax_where, '(' . join(' OR ', @orwhere) . ')' if @orwhere;
 405   }
 406
 407   # specific taxnums
 408   if ( $cgi->param('taxnum') =~ /^([\d,]+)$/) {
 409     push @tax_where, "cust_main_county.taxnum IN ($1)";
 410   }
 411
 412   # If we're showing exempt items, we need to find those with
 413   # cust_tax_exempt_pkg records matching the selected taxes.
 414   # If we're showing taxable items, we need to find those with
 415   # cust_bill_pkg_tax_location records.  We also need to find the
 416   # exemption records so that we can show the taxable amount.
 417   # If we're showing all items, we need the union of those.
 418   # If we're showing 'out' (items that aren't region/class taxable),
 419   # then we need the set of all items minus the union of those.
 420
 421   # Always exclude cust_tax_exempt_pkg records with non-NULL creditbillpkgnum.
 422
 423   if ( $cgi->param('out') ) {
 424     # separate from the rest, in that we're not going to join cust_main_county
 425     # in the outer query
 426
 427     my @exclude = ( 'cust_tax_exempt_pkg.billpkgnum',
 428                     'cust_bill_pkg_tax_location.taxable_billpkgnum'
 429                   );
 430     foreach my $col (@exclude) {
 431       my ($table) = split(/\./, $col);
 432       my $this_where = 'WHERE ' .  join(' AND ',
 433         "$col = cust_bill_pkg.billpkgnum",
 434         @tax_where
 435       );
 436
 437       push @where,
 438       "NOT EXISTS(SELECT 1 FROM $table
 439         JOIN cust_main_county USING (taxnum)
 440         $this_where
 441       )";
 442     }
 443
 444   } else {
 445     # everything that returns things joined to a tax definition
 446
 447     if ( @exempt_where or @tax_where or $cgi->param('taxable') ) {
 448
 449       push @exempt_where, "cust_tax_exempt_pkg.creditbillpkgnum IS NULL";
 450
 451       # process exemption restrictions, including @tax_where
 452       my $exempt_sub = 'SELECT SUM(amount) as exempt_amount, billpkgnum
 453       FROM cust_tax_exempt_pkg JOIN cust_main_county USING (taxnum)';
 454
 455       $exempt_sub .= ' WHERE '.join(' AND ', @tax_where, @exempt_where);
 456
 457       $exempt_sub .= ' GROUP BY billpkgnum';
 458
 459       $join_pkg .= " LEFT JOIN ($exempt_sub) AS item_exempt
 460       ON (cust_bill_pkg.billpkgnum = item_exempt.billpkgnum)";
 461
 462     }
 463
 464     my $credit_sub = 'SELECT SUM(amount) AS credit_amount, billpkgnum
 465     FROM cust_credit_bill_pkg GROUP BY billpkgnum';
 466
 467     $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
 468     ON (cust_bill_pkg.billpkgnum = item_credit.billpkgnum)";
 469
 470     if ( @tax_where or $cgi->param('taxable') ) {
 471       # process tax restrictions
 472       unshift @tax_where,
 473         'cust_main_county.tax > 0';
 474
 475       my $tax_sub = "SELECT taxable_billpkgnum
 476       FROM cust_bill_pkg_tax_location
 477       JOIN cust_main_county USING (taxnum)
 478       WHERE ". join(' AND ', @tax_where).
 479       " GROUP BY taxable_billpkgnum";
 480
 481       $join_pkg .= " LEFT JOIN ($tax_sub) AS item_tax
 482       ON (cust_bill_pkg.billpkgnum = item_tax.taxable_billpkgnum)"
 483     }
 484
 485     # now do something with that
 486     if ( $cgi->param('taxable') ) {
 487       # taxable query: needs sale amount - exempt amount
 488       my $taxable = 'cust_bill_pkg.setup + cust_bill_pkg.recur '.
 489                     '- COALESCE(item_exempt.exempt_amount, 0)';
 490
 491       push @where, "item_tax.taxable_billpkgnum IS NOT NULL";
 492       push @select,   "($taxable) AS taxable_amount";
 493       push @peritem,  'taxable_amount';
 494       push @peritem_desc, 'Taxable';
 495       push @total,    "SUM($taxable)";
 496       push @total_desc, "$money_char%.2f taxable";
 497
 498     } elsif ( $cgi->param('exempt_cust') or $cgi->param('exempt_pkg') ) {
 499
 500       push @where,    'item_exempt.billpkgnum IS NOT NULL';
 501       push @select,   'item_exempt.exempt_amount';
 502       push @peritem,  'exempt_amount';
 503       push @peritem_desc, 'Exempt';
 504       push @total,    'SUM(exempt_amount)';
 505       push @total_desc, "$money_char%.2f tax-exempt";
 506
 507     } elsif ( @tax_where ) {
 508       # union of taxable + all exempt_ cases
 509       push @where,
 510         '(item_tax.taxable_billpkgnum IS NOT NULL OR item_exempt.billpkgnum IS NOT NULL)';
 511
 512     }
 513
 514   } # handle all joins to cust_main_county
 515
 516   # setup/recur/usage separation
 517   my %charges = map { $_ => 1 } split('', $cgi->param('charges') || 'SRU');
 518
 519   if ( $charges{S} and $charges{R} and $charges{U} ) {
 520     # in this case, show discounts
 521
 522     $join_pkg .= ' JOIN (
 523     SELECT billpkgnum, COALESCE(SUM(amount), 0) AS discounted
 524       FROM cust_bill_pkg_discount RIGHT JOIN cust_bill_pkg USING (billpkgnum)
 525       GROUP BY billpkgnum
 526     ) AS _discount ON (cust_bill_pkg.billpkgnum = _discount.billpkgnum)
 527     ';
 528     push @select, '_discount.discounted';
 529
 530     push @peritem, 'discounted';
 531     push @peritem_desc, 'Discount';
 532     push @total, 'SUM(cust_bill_pkg.setup + cust_bill_pkg.recur + discounted)',
 533                  'SUM(discounted)',
 534                  'SUM(cust_bill_pkg.setup + cust_bill_pkg.recur)';
 535     push @total_desc, "$money_char%.2f gross sales",
 536                       "&minus; $money_char%.2f discounted",
 537                       "= $money_char%.2f invoiced";
 538
 539   } elsif ( $charges{R} and $charges{U} ) {
 540
 541     # hide rows with no recurring fee, and show the sum of recurring fees only
 542     push @where, 'cust_bill_pkg.recur > 0';
 543     push @total, "SUM(cust_bill_pkg.recur)";
 544     push @total_desc, "$money_char%.2f recurring";
 545
 546   } elsif ( $charges{R} and !$charges{U} ) {
 547
 548     my $recur_no_usage = FS::cust_bill_pkg->charged_sql('', '',
 549       setuprecur => 'recur', no_usage => 1);
 550     push @select, "($recur_no_usage) AS recur_no_usage";
 551     $peritem[1] = 'recur_no_usage';
 552     $peritem_desc[1] = 'Recurring charges (excluding usage)';
 553     push @total, "SUM($recur_no_usage)";
 554     push @total_desc, "$money_char%.2f recurring";
 555     if ( !$charges{S} ) {
 556       push @where, "($recur_no_usage) > 0";
 557     }
 558
 559   } elsif ( !$charges{R} and $charges{U} ) {
 560
 561     my $usage = FS::cust_bill_pkg->usage_sql();
 562     push @select, "($usage) AS _usage";
 563     # there's already a method named 'usage'
 564     $peritem[1] = '_usage';
 565     $peritem_desc[1] = 'Usage charge';
 566     push @total, "SUM($usage)";
 567     push @total_desc, "$money_char%.2f usage charges";
 568     if ( !$charges{S} ) {
 569       push @where, "($usage) > 0";
 570     }
 571
 572   } elsif ( $charges{S} ) {
 573
 574     push @where, "cust_bill_pkg.setup > 0";
 575     push @total, "SUM(cust_bill_pkg.setup)";
 576     push @total_desc, "$money_char%.2f setup";
 577
 578   } # else huh? you have to have SOME charges
 579
 580 } elsif ( $cgi->param('istax') ) {
 581
 582   @peritem = ( 'setup' ); # taxes only have setup
 583   @peritem_desc = ( 'Tax charge' );
 584
 585   push @where, 'cust_bill_pkg.pkgnum = 0';
 586
 587   # tax location when using tax_rate_location
 588   if ( $cgi->param('vendortax') ) {
 589
 590     $join_pkg .= ' LEFT JOIN cust_bill_pkg_tax_rate_location USING ( billpkgnum ) '.
 591                  ' LEFT JOIN tax_rate_location USING ( taxratelocationnum )';
 592     foreach (qw( state county city locationtaxid)) {
 593       if ( scalar($cgi->param($_)) ) {
 594         my $place = dbh->quote( $cgi->param($_) );
 595         push @where, "tax_rate_location.$_ = $place";
 596       }
 597     }
 598
 599     push @total, 'SUM(
 600       COALESCE(cust_bill_pkg_tax_rate_location.amount,
 601                cust_bill_pkg.setup + cust_bill_pkg.recur)
 602     )';
 603     push @total_desc, "$money_char%.2f total";
 604
 605   } else { # the internal-tax case
 606
 607     $join_pkg .= '
 608       LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum)
 609       JOIN cust_main_county           USING (taxnum)
 610     ';
 611
 612     # don't double-count the components of consolidated taxes
 613     @total = ( 'COUNT(DISTINCT cust_bill_pkg.billpkgnum)',
 614                'SUM(cust_bill_pkg_tax_location.amount)' );
 615     @total_desc = "$money_char%.2f total";
 616
 617     # package classnum
 618     if ( grep { $_ eq 'classnum' } $cgi->param ) {
 619       my @classnums = grep /^\d*$/, $cgi->param('classnum');
 620       $join_pkg .= '
 621         JOIN cust_pkg AS taxed_pkg
 622           ON (cust_bill_pkg_tax_location.pkgnum = taxed_pkg.pkgnum)
 623         JOIN part_pkg AS taxed_part_pkg
 624           ON (taxed_pkg.pkgpart = taxed_part_pkg.pkgpart)
 625       ';
 626       push @where, "COALESCE(taxed_part_pkg.classnum, 0) IN ( ".
 627                        join(',', @classnums ).
 628                    ' )'
 629         if @classnums;
 630     }
 631
 632     # taxclass
 633     if ( $cgi->param('taxclassNULL') ) {
 634       push @where, 'cust_main_county.taxclass IS NULL';
 635     }
 636
 637     # taxname
 638     if ( $cgi->param('taxnameNULL') ) {
 639       push @where, 'cust_main_county.taxname IS NULL OR '.
 640                    'cust_main_county.taxname = \'Tax\'';
 641     } elsif ( $cgi->param('taxname') ) {
 642       push @where, 'cust_main_county.taxname = '.
 643                     dbh->quote($cgi->param('taxname'));
 644     }
 645
 646     # itemdesc, for breakdown from the vendor tax report
 647     if ( $cgi->param('itemdesc') ) {
 648       if ( $cgi->param('itemdesc') eq 'Tax' ) {
 649         push @where, "($itemdesc = 'Tax' OR $itemdesc is null)";
 650       } else {
 651         push @where, "$itemdesc = ". dbh->quote($cgi->param('itemdesc'));
 652       }
 653     }
 654
 655     # specific taxnums
 656     if ( $cgi->param('taxnum') =~ /^([\d,]+)$/) {
 657       push @where, "cust_main_county.taxnum IN ($1)";
 658     }
 659
 660   } #end of "normal case"
 661
 662   # classnum (of underlying package)
 663   # not specified: all classes
 664   # 0: empty class
 665   # N: classnum
 666   if ( grep { $_ eq 'classnum' } $cgi->param ) {
 667     my @classnums = grep /^\d+$/, $cgi->param('classnum');
 668     push @where, "COALESCE(part_fee.classnum, $part_pkg.classnum, 0) IN ( ".
 669                      join(',', @classnums ).
 670                  ' )'
 671       if @classnums;
 672   }
 673
 674 } # nottax / istax
 675
 676
 677 #total payments
 678 my $pay_sub = "SELECT SUM(cust_bill_pay_pkg.amount)
 679                  FROM cust_bill_pay_pkg
 680                    WHERE cust_bill_pkg.billpkgnum = cust_bill_pay_pkg.billpkgnum
 681               ";
 682 push @select, "($pay_sub) AS pay_amount";
 683
 684
 685 # credit
 686 if ( $cgi->param('credit') ) {
 687
 688   my $credit_where;
 689
 690   my($cr_begin, $cr_end) = FS::UI::Web::parse_beginning_ending($cgi, 'credit');
 691   $credit_where = "WHERE cust_credit_bill._date >= $cr_begin " .
 692                   "AND cust_credit_bill._date <= $cr_end";
 693
 694   my $credit_sub;
 695
 696   if ( $cgi->param('istax') ) {
 697     # then we need to group/join by billpkgtaxlocationnum, to get only the
 698     # relevant part of partial taxes
 699     my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
 700       reason.reason as reason_text, access_user.username AS username_text,
 701       billpkgtaxlocationnum, billpkgnum
 702     FROM cust_credit_bill_pkg
 703       JOIN cust_credit_bill USING (creditbillnum)
 704       JOIN cust_credit USING (crednum)
 705       LEFT JOIN reason USING (reasonnum)
 706       LEFT JOIN access_user USING (usernum)
 707     $credit_where
 708     GROUP BY billpkgnum, billpkgtaxlocationnum, reason.reason,
 709       access_user.username";
 710
 711     if ( $cgi->param('out') ) {
 712
 713       # find credits that are applied to the line items, but not to
 714       # a cust_bill_pkg_tax_location link
 715       $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
 716         USING (billpkgnum)";
 717       push @where, 'item_credit.billpkgtaxlocationnum IS NULL';
 718
 719     } else {
 720
 721       # find credits that are applied to the CBPTL links that are
 722       # considered "interesting" by the report criteria
 723       $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
 724         USING (billpkgtaxlocationnum)";
 725
 726     }
 727
 728   } else {
 729     # then only group by billpkgnum
 730     my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
 731       reason.reason as reason_text, access_user.username AS username_text,
 732       billpkgnum
 733     FROM cust_credit_bill_pkg
 734       JOIN cust_credit_bill USING (creditbillnum)
 735       JOIN cust_credit USING (crednum)
 736       LEFT JOIN reason USING (reasonnum)
 737       LEFT JOIN access_user USING (usernum)
 738     $credit_where
 739     GROUP BY billpkgnum, reason.reason, access_user.username";
 740     $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit USING (billpkgnum)";
 741   }
 742
 743   push @where,    'item_credit.billpkgnum IS NOT NULL';
 744   push @select,   'item_credit.credit_amount',
 745                   'item_credit.username_text',
 746                   'item_credit.reason_text';
 747   push @peritem,  'credit_amount', 'username_text', 'reason_text';
 748   push @peritem_desc, 'Credited', 'By', 'Reason';
 749   push @total,    'SUM(credit_amount)';
 750   push @total_desc, "$money_char%.2f credited";
 751
 752 } else {
 753
 754   #still want a credit total column
 755
 756   my $credit_sub = "
 757     SELECT SUM(cust_credit_bill_pkg.amount)
 758       FROM cust_credit_bill_pkg
 759         WHERE cust_bill_pkg.billpkgnum = cust_credit_bill_pkg.billpkgnum
 760   ";
 761   push @select, "($credit_sub) AS credit_amount";
 762
 763 }
 764
 765 push @select, 'cust_main.custnum', FS::UI::Web::cust_sql_fields();
 766
 767 #salesnum
 768 if ( $cgi->param('salesnum') =~ /^(\d+)$/ ) {
 769
 770   my $salesnum = $1;
 771   my $sales = FS::sales->by_key($salesnum)
 772     or die "salesnum $salesnum not found";
 773
 774   my $subsearch = $sales->cust_bill_pkg_search('', '',
 775     'cust_main_sales' => ($cgi->param('cust_main_sales') ? 1 : 0),
 776     'paid'            => ($cgi->param('paid') ? 1 : 0),
 777     'classnum'        => scalar($cgi->param('classnum'))
 778   );
 779   $join_pkg .= " JOIN sales_pkg_class ON ( COALESCE(sales_pkg_class.classnum, 0) = COALESCE( part_fee.classnum, part_pkg.classnum, 0) )";
 780
 781   my $extra_sql = $subsearch->{extra_sql};
 782   $extra_sql =~ s/^WHERE//;
 783   push @where, $extra_sql;
 784
 785   $cgi->param('classnum', 0) unless $cgi->param('classnum');
 786 }
 787
 788
 789 my $where = join(' AND ', @where);
 790 $where &&= "WHERE $where";
 791
 792 my $query = {
 793   'table'     => 'cust_bill_pkg',
 794   'addl_from' => "$join_pkg $join_cust",
 795   'hashref'   => {},
 796   'select'    => join(",\n", @select ),
 797   'extra_sql' => $where,
 798   'order_by'  => 'ORDER BY cust_bill._date, cust_bill_pkg.billpkgnum',
 799 };
 800
 801 my $count_query =
 802   'SELECT ' . join(',', @total) .
 803   " FROM cust_bill_pkg $join_pkg $join_cust
 804   $where";
 805
 806 @peritem_desc = map {emt($_)} @peritem_desc;
 807 my @peritem_sub = map {
 808   my $field = $_;
 809   if ($field =~ /_text$/) { # kludge for credit reason/username fields
 810     sub {$_[0]->get($field)};
 811   } else {
 812     sub { sprintf($money_char.'%.2f', $_[0]->get($field)) }
 813   }
 814 } @peritem;
 815 my @peritem_null = map { '' } @peritem; # placeholders
 816 my $peritem_align = 'r' x scalar(@peritem);
 817
 818 @currency_desc = map {emt($_)} @currency_desc;
 819 my @currency_null = map { '' } @currency; # placeholders
 820 my $currency_align = 'r' x scalar(@currency);
 821
 822 my $ilink = [ "${p}view/cust_bill.cgi?", 'invnum' ];
 823 my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ];
 824
 825 my $pay_link    = ''; #[, 'billpkgnum', ];
 826 my $credit_link = [ "${p}search/cust_credit_bill_pkg.html?billpkgnum=", 'billpkgnum', ];
 827
 828 warn "\n\nQUERY:\n".Dumper($query)."\n\nCOUNT_QUERY:\n$count_query\n\n"
 829   if $cgi->param('debug');
 830
 831 </%init>