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