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