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