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