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 or a comma-separated list (where 0 is "no report class"), or the
 136   word "multiple".
 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 a comma-separated
 148   list 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--see below
 287 # refnum
 288 if ( $cgi->param('refnum') =~ /^(\d+)$/ ) {
 289   push @where, "cust_main.refnum = $1";
 290 }
 291
 292 # 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)
 293 if ( grep { $_ eq 'cust_classnum' } $cgi->param ) {
 294   my @classnums = grep /^\d*$/, $cgi->param('cust_classnum');
 295   push @where, 'COALESCE( cust_main.classnum, 0) IN ( '.
 296                    join(',', map { $_ || '0' } @classnums ).
 297                ' )'
 298     if @classnums;
 299 }
 300
 301
 302 # custnum
 303 if ( $cgi->param('custnum') =~ /^(\d+)$/ ) {
 304   push @where, "cust_main.custnum = $1";
 305 }
 306
 307 # we want the package and its definition if available
 308 my $join_pkg =
 309 ' LEFT JOIN cust_pkg      USING (pkgnum)
 310   LEFT JOIN part_pkg      USING (pkgpart)';
 311
 312 my $part_pkg = 'part_pkg';
 313 if ( $cgi->param('use_override') ) { #"Separate sub-packages from parents"
 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'; # or should this use override?
 322
 323 # the non-tax case
 324 if ( $cgi->param('nottax') ) {
 325
 326   push @where, 'cust_bill_pkg.pkgnum > 0';
 327
 328   my @tax_where; # will go into a subquery
 329   my @exempt_where; # will also go into a subquery
 330
 331   # classnum (of override pkgpart if applicable)
 332   # not specified: all classes
 333   # 0: empty class
 334   # N: classnum
 335   if ( grep { $_ eq 'classnum' } $cgi->param ) {
 336     my @classnums = grep /^\d*$/, $cgi->param('classnum');
 337     push @where, "COALESCE($part_pkg.classnum, 0) IN ( ".
 338                      join(',', @classnums ).
 339                  ' )'
 340       if @classnums;
 341   }
 342
 343   if ( grep { $_ eq 'report_optionnum' } $cgi->param ) {
 344     my @nums = grep /^\w+$/, $cgi->param('report_optionnum');
 345     my $num = join(',', @nums);
 346     push @where, # code reuse FTW
 347       FS::Report::Table->with_report_option( $num, $cgi->param('use_override'));
 348   }
 349
 350   if ( $cgi->param('report_optionnum') =~ /^(\w+)$/ ) {
 351     ;
 352   }
 353
 354   # taxclass
 355   if ( $cgi->param('taxclassNULL') ) {
 356     # a little different from 'taxclass' in that it applies to the
 357     # effective taxclass, not the real one
 358     push @tax_where, 'cust_main_county.taxclass IS NULL'
 359   } elsif ( $cgi->param('taxclass') ) {
 360     push @tax_where, "$part_pkg.taxclass IN (" .
 361                  join(', ', map {dbh->quote($_)} $cgi->param('taxclass') ).
 362                  ')';
 363   }
 364
 365   if ( $cgi->param('exempt_cust') eq 'Y' ) {
 366     # tax-exempt customers
 367     push @exempt_where, "(exempt_cust = 'Y' OR exempt_cust_taxname = 'Y')";
 368
 369   } elsif ( $cgi->param('exempt_pkg') eq 'Y' ) { # non-taxable package
 370     # non-taxable package charges
 371     push @exempt_where, "(exempt_setup = 'Y' OR exempt_recur = 'Y')";
 372   }
 373   # we don't handle exempt_monthly here
 374
 375   if ( $cgi->param('taxname') ) { # specific taxname
 376       push @tax_where, "COALESCE(cust_main_county.taxname, 'Tax') = ".
 377                         dbh->quote($cgi->param('taxname'));
 378   }
 379
 380   # country:state:county:city:district (may be repeated)
 381   # You can also pass a big list of taxnums but that leads to huge URLs.
 382   # Note that this means "packages whose tax is in this region", not
 383   # "packages in this region".  It's meant for links from the tax report.
 384   if ( $cgi->param('region') ) {
 385     my @orwhere;
 386     foreach ( $cgi->param('region') ) {
 387       my %loc;
 388       @loc{qw(country state county city district)} =
 389         split(':', $cgi->param('region'));
 390       my $string = join(' AND ',
 391             map {
 392               if ( $loc{$_} ) {
 393                 "$_ = ".dbh->quote($loc{$_});
 394               } else {
 395                 "$_ IS NULL";
 396               }
 397             } keys(%loc)
 398       );
 399       push @orwhere, "($string)";
 400     }
 401     push @tax_where, '(' . join(' OR ', @orwhere) . ')' if @orwhere;
 402   }
 403
 404   # specific taxnums
 405   if ( $cgi->param('taxnum') =~ /^([\d,]+)$/) {
 406     push @tax_where, "cust_main_county.taxnum IN ($1)";
 407   }
 408
 409   # If we're showing exempt items, we need to find those with
 410   # cust_tax_exempt_pkg records matching the selected taxes.
 411   # If we're showing taxable items, we need to find those with
 412   # cust_bill_pkg_tax_location records.  We also need to find the
 413   # exemption records so that we can show the taxable amount.
 414   # If we're showing all items, we need the union of those.
 415   # If we're showing 'out' (items that aren't region/class taxable),
 416   # then we need the set of all items minus the union of those.
 417
 418   my $exempt_sub;
 419
 420   if ( @exempt_where or @tax_where
 421     or $cgi->param('taxable') or $cgi->param('out') )
 422   {
 423     # process exemption restrictions, including @tax_where
 424     my $exempt_sub = 'SELECT SUM(amount) as exempt_amount, billpkgnum
 425     FROM cust_tax_exempt_pkg JOIN cust_main_county USING (taxnum)';
 426
 427     $exempt_sub .= ' WHERE '.join(' AND ', @tax_where, @exempt_where)
 428       if (@tax_where or @exempt_where);
 429
 430     $exempt_sub .= ' GROUP BY billpkgnum';
 431
 432     $join_pkg .= " LEFT JOIN ($exempt_sub) AS item_exempt
 433     USING (billpkgnum)";
 434   }
 435
 436   # process tax restrictions
 437   unshift @tax_where,
 438     'cust_bill_pkg_tax_location.taxable_billpkgnum = cust_bill_pkg.billpkgnum',
 439     'cust_main_county.tax > 0';
 440
 441   my $tax_sub = "SELECT 1
 442     FROM cust_bill_pkg_tax_location
 443     JOIN cust_bill_pkg AS tax_item USING (billpkgnum)
 444     JOIN cust_main_county USING (taxnum)
 445     WHERE ". join(' AND ', @tax_where);
 446
 447   # now do something with that
 448   if ( @exempt_where ) {
 449
 450     push @where,    'item_exempt.billpkgnum IS NOT NULL';
 451     push @select,   'item_exempt.exempt_amount';
 452     push @peritem,  'exempt_amount';
 453     push @peritem_desc, 'Exempt';
 454     push @total,    'SUM(exempt_amount)';
 455     push @total_desc, "$money_char%.2f tax-exempt";
 456
 457   } elsif ( $cgi->param('taxable') ) {
 458
 459     my $taxable = 'cust_bill_pkg.setup + cust_bill_pkg.recur '.
 460                   '- COALESCE(item_exempt.exempt_amount, 0)';
 461
 462     push @select,   "($taxable) AS taxable_amount";
 463     push @where,    "EXISTS($tax_sub)";
 464     push @peritem,  'taxable_amount';
 465     push @peritem_desc, 'Taxable';
 466     push @total,    "SUM($taxable)";
 467     push @total_desc, "$money_char%.2f taxable";
 468
 469   } elsif ( @tax_where ) {
 470
 471     # union of taxable + all exempt_ cases
 472     push @where, "(EXISTS($tax_sub) OR item_exempt.billpkgnum IS NOT NULL)";
 473
 474   }
 475
 476   # recur/usage separation
 477   if ( $cgi->param('usage') eq 'recurring' ) {
 478
 479     my $recur_no_usage = FS::cust_bill_pkg->charged_sql('', '', no_usage => 1);
 480     push @select, "($recur_no_usage) AS recur_no_usage";
 481     $peritem[1] = 'recur_no_usage';
 482     $total[1] = "SUM(cust_bill_pkg.setup + $recur_no_usage)";
 483     $total_desc[0] .= ' (excluding usage)';
 484
 485   } elsif ( $cgi->param('usage') eq 'usage' ) {
 486
 487     my $usage = FS::cust_bill_pkg->usage_sql();
 488     push @select, "($usage) AS _usage";
 489     # there's already a method named 'usage'
 490     $peritem[1] = '_usage';
 491     $peritem_desc[1] = 'Usage charge';
 492     $total[1] = "SUM($usage)";
 493     $total_desc[0] .= ' usage charges';
 494   }
 495
 496 } elsif ( $cgi->param('istax') ) {
 497
 498   @peritem = ( 'setup' ); # taxes only have setup
 499   @peritem_desc = ( 'Tax charge' );
 500
 501   push @where, 'cust_bill_pkg.pkgnum = 0';
 502
 503   # tax location when using tax_rate_location
 504   if ( scalar( grep( /locationtaxid/, $cgi->param ) ) ) {
 505
 506     $join_pkg .= ' LEFT JOIN cust_bill_pkg_tax_rate_location USING ( billpkgnum ) '.
 507                  ' LEFT JOIN tax_rate_location USING ( taxratelocationnum )';
 508     push @where, FS::tax_rate_location->location_sql(
 509                    map { $_ => (scalar($cgi->param($_)) || '') }
 510                      qw( district city county state locationtaxid )
 511                  );
 512
 513     $total[1] = 'SUM(
 514       COALESCE(cust_bill_pkg_tax_rate_location.amount,
 515                cust_bill_pkg.setup + cust_bill_pkg.recur)
 516     )';
 517
 518   } elsif ( $cgi->param('out') ) {
 519
 520     $join_pkg .= '
 521       LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum)
 522     ';
 523     push @where, 'cust_bill_pkg_tax_location.billpkgnum IS NULL';
 524
 525     # each billpkgnum should appear only once
 526     $total[0] = 'COUNT(*)';
 527     $total[1] = 'SUM(cust_bill_pkg.setup)';
 528
 529   } else { # not locationtaxid or 'out'--the normal case
 530
 531     $join_pkg .= '
 532       LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum)
 533       JOIN cust_main_county           USING (taxnum)
 534     ';
 535
 536     # don't double-count the components of consolidated taxes
 537     $total[0] = 'COUNT(DISTINCT cust_bill_pkg.billpkgnum)';
 538     $total[1] = 'SUM(cust_bill_pkg_tax_location.amount)';
 539
 540     # package classnum
 541     if ( grep { $_ eq 'classnum' } $cgi->param ) {
 542       my @classnums = grep /^\d*$/, $cgi->param('classnum');
 543       $join_pkg .= '
 544         JOIN cust_pkg AS taxed_pkg
 545           ON (cust_bill_pkg_tax_location.pkgnum = taxed_pkg.pkgnum)
 546         JOIN part_pkg AS taxed_part_pkg
 547           ON (taxed_pkg.pkgpart = taxed_part_pkg.pkgpart)
 548       ';
 549       push @where, "COALESCE(taxed_part_pkg.classnum, 0) IN ( ".
 550                        join(',', @classnums ).
 551                    ' )'
 552         if @classnums;
 553     }
 554   }
 555
 556   # taxclass
 557   if ( $cgi->param('taxclassNULL') ) {
 558     push @where, 'cust_main_county.taxclass IS NULL';
 559   }
 560
 561   # taxname
 562   if ( $cgi->param('taxnameNULL') ) {
 563     push @where, 'cust_main_county.taxname IS NULL OR '.
 564                  'cust_main_county.taxname = \'Tax\'';
 565   } elsif ( $cgi->param('taxname') ) {
 566     push @where, 'cust_main_county.taxname = '.
 567                   dbh->quote($cgi->param('taxname'));
 568   }
 569
 570   # specific taxnums
 571   if ( $cgi->param('taxnum') =~ /^([\d,]+)$/) {
 572     push @where, "cust_main_county.taxnum IN ($1)";
 573   }
 574
 575   # itemdesc, for some reason
 576   if ( $cgi->param('itemdesc') ) {
 577     if ( $cgi->param('itemdesc') eq 'Tax' ) {
 578       push @where, "(itemdesc='Tax' OR itemdesc is null)";
 579     } else {
 580       push @where, 'itemdesc='. dbh->quote($cgi->param('itemdesc'));
 581     }
 582   }
 583
 584 } # nottax / istax
 585
 586
 587 #total payments
 588 my $pay_sub = "SELECT SUM(cust_bill_pay_pkg.amount)
 589                  FROM cust_bill_pay_pkg
 590                    WHERE cust_bill_pkg.billpkgnum = cust_bill_pay_pkg.billpkgnum
 591               ";
 592 push @select, "($pay_sub) AS pay_amount";
 593
 594
 595 # credit
 596 if ( $cgi->param('credit') ) {
 597
 598   my $credit_sub;
 599
 600   if ( $cgi->param('istax') ) {
 601     # then we need to group/join by billpkgtaxlocationnum, to get only the
 602     # relevant part of partial taxes
 603     my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
 604       reason.reason as reason_text, access_user.username AS username_text,
 605       billpkgtaxlocationnum, billpkgnum
 606     FROM cust_credit_bill_pkg
 607       JOIN cust_credit_bill USING (creditbillnum)
 608       JOIN cust_credit USING (crednum)
 609       LEFT JOIN reason USING (reasonnum)
 610       LEFT JOIN access_user USING (usernum)
 611     GROUP BY billpkgnum, billpkgtaxlocationnum, reason.reason,
 612       access_user.username";
 613
 614     if ( $cgi->param('out') ) {
 615
 616       # find credits that are applied to the line items, but not to
 617       # a cust_bill_pkg_tax_location link
 618       $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
 619         USING (billpkgnum)";
 620       push @where, 'item_credit.billpkgtaxlocationnum IS NULL';
 621
 622     } else {
 623
 624       # find credits that are applied to the CBPTL links that are
 625       # considered "interesting" by the report criteria
 626       $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
 627         USING (billpkgtaxlocationnum)";
 628
 629     }
 630
 631   } else {
 632     # then only group by billpkgnum
 633     my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
 634       reason.reason as reason_text, access_user.username AS username_text,
 635       billpkgnum
 636     FROM cust_credit_bill_pkg
 637       JOIN cust_credit_bill USING (creditbillnum)
 638       JOIN cust_credit USING (crednum)
 639       LEFT JOIN reason USING (reasonnum)
 640       LEFT JOIN access_user USING (usernum)
 641     GROUP BY billpkgnum, reason.reason, access_user.username";
 642     $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit USING (billpkgnum)";
 643   }
 644
 645   push @where,    'item_credit.billpkgnum IS NOT NULL';
 646   push @select,   'item_credit.credit_amount',
 647                   'item_credit.username_text',
 648                   'item_credit.reason_text';
 649   push @peritem,  'credit_amount', 'username_text', 'reason_text';
 650   push @peritem_desc, 'Credited', 'By', 'Reason';
 651   push @total,    'SUM(credit_amount)';
 652   push @total_desc, "$money_char%.2f credited";
 653
 654 } else {
 655
 656   #still want a credit total column
 657
 658   my $credit_sub = "
 659     SELECT SUM(cust_credit_bill_pkg.amount)
 660       FROM cust_credit_bill_pkg
 661         WHERE cust_bill_pkg.billpkgnum = cust_credit_bill_pkg.billpkgnum
 662   ";
 663   push @select, "($credit_sub) AS credit_amount";
 664
 665 }
 666
 667 push @select, 'cust_main.custnum', FS::UI::Web::cust_sql_fields();
 668
 669 #salesnum
 670 if ( $cgi->param('salesnum') =~ /^(\d+)$/ ) {
 671
 672   my $salesnum = $1;
 673   my $sales = FS::sales->by_key($salesnum)
 674     or die "salesnum $salesnum not found";
 675
 676   my $subsearch = $sales->cust_bill_pkg_search('', '',
 677     'cust_main_sales' => ($cgi->param('cust_main_sales') ? 1 : 0),
 678     'paid'            => ($cgi->param('paid') ? 1 : 0),
 679     'classnum'        => scalar($cgi->param('classnum'))
 680   );
 681   $join_pkg .= " JOIN sales_pkg_class ON ( COALESCE(sales_pkg_class.classnum, 0) = COALESCE( part_pkg.classnum, 0) )";
 682
 683   my $extra_sql = $subsearch->{extra_sql};
 684   $extra_sql =~ s/^WHERE//;
 685   push @where, $extra_sql;
 686
 687   $cgi->param('classnum', 0) unless $cgi->param('classnum');
 688 }
 689
 690
 691 my $where = join(' AND ', @where);
 692 $where &&= "WHERE $where";
 693
 694 my $query = {
 695   'table'     => 'cust_bill_pkg',
 696   'addl_from' => "$join_pkg $join_cust",
 697   'hashref'   => {},
 698   'select'    => join(",\n", @select ),
 699   'extra_sql' => $where,
 700   'order_by'  => 'ORDER BY cust_bill._date, cust_bill_pkg.billpkgnum',
 701 };
 702
 703 my $count_query =
 704   'SELECT ' . join(',', @total) .
 705   " FROM cust_bill_pkg $join_pkg $join_cust
 706   $where";
 707
 708 @peritem_desc = map {emt($_)} @peritem_desc;
 709 my @peritem_sub = map {
 710   my $field = $_;
 711   if ($field =~ /_text$/) { # kludge for credit reason/username fields
 712     sub {$_[0]->get($field)};
 713   } else {
 714     sub { sprintf($money_char.'%.2f', $_[0]->get($field)) }
 715   }
 716 } @peritem;
 717 my @peritem_null = map { '' } @peritem; # placeholders
 718 my $peritem_align = 'r' x scalar(@peritem);
 719
 720 @currency_desc = map {emt($_)} @currency_desc;
 721 my @currency_null = map { '' } @currency; # placeholders
 722 my $currency_align = 'r' x scalar(@currency);
 723
 724 my $ilink = [ "${p}view/cust_bill.cgi?", 'invnum' ];
 725 my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ];
 726
 727 my $pay_link    = ''; #[, 'billpkgnum', ];
 728 my $credit_link = [ "${p}search/cust_credit_bill_pkg.html?billpkgnum=", 'billpkgnum', ];
 729
 730 warn "\n\nQUERY:\n".Dumper($query)."\n\nCOUNT_QUERY:\n$count_query\n\n"
 731   if $cgi->param('debug');
 732
 733 </%init>