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