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 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--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, 'cust_main_county.taxname = '.
 377                         dbh->quote($cgi->param('taxname'));
 378   } elsif ( $cgi->param('taxnameNULL') ) {
 379       push @tax_where, 'cust_main_county.taxname IS NULL OR '.
 380                        'cust_main_county.taxname = \'Tax\'';
 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') ) {
 409     my $taxnum_in = join(',',
 410       grep /^\d+$/, $cgi->param('taxnum')
 411     );
 412     push @tax_where, "cust_main_county.taxnum IN ($taxnum_in)"
 413       if $taxnum_in;
 414   }
 415
 416   # If we're showing exempt items, we need to find those with
 417   # cust_tax_exempt_pkg records matching the selected taxes.
 418   # If we're showing taxable items, we need to find those with
 419   # cust_bill_pkg_tax_location records.  We also need to find the
 420   # exemption records so that we can show the taxable amount.
 421   # If we're showing all items, we need the union of those.
 422   # If we're showing 'out' (items that aren't region/class taxable),
 423   # then we need the set of all items minus the union of those.
 424
 425   my $exempt_sub;
 426
 427   if ( @exempt_where or @tax_where
 428     or $cgi->param('taxable') or $cgi->param('out') )
 429   {
 430     # process exemption restrictions, including @tax_where
 431     my $exempt_sub = 'SELECT SUM(amount) as exempt_amount, billpkgnum
 432     FROM cust_tax_exempt_pkg JOIN cust_main_county USING (taxnum)';
 433
 434     $exempt_sub .= ' WHERE '.join(' AND ', @tax_where, @exempt_where)
 435       if (@tax_where or @exempt_where);
 436
 437     $exempt_sub .= ' GROUP BY billpkgnum';
 438
 439     $join_pkg .= " LEFT JOIN ($exempt_sub) AS item_exempt
 440     USING (billpkgnum)";
 441   }
 442
 443   if ( @tax_where or $cgi->param('taxable') or $cgi->param('out') ) {
 444     # process tax restrictions
 445     unshift @tax_where,
 446       'cust_main_county.tax > 0';
 447
 448     my $tax_sub = "SELECT invnum, cust_bill_pkg_tax_location.pkgnum
 449     FROM cust_bill_pkg_tax_location
 450     JOIN cust_bill_pkg AS tax_item USING (billpkgnum)
 451     JOIN cust_main_county USING (taxnum)
 452     WHERE ". join(' AND ', @tax_where).
 453     " GROUP BY invnum, cust_bill_pkg_tax_location.pkgnum";
 454
 455     $join_pkg .= " LEFT JOIN ($tax_sub) AS item_tax
 456     ON (item_tax.invnum = cust_bill_pkg.invnum AND
 457         item_tax.pkgnum = cust_bill_pkg.pkgnum)";
 458   }
 459
 460   # now do something with that
 461   if ( @exempt_where ) {
 462
 463     push @where,    'item_exempt.billpkgnum IS NOT NULL';
 464     push @select,   'item_exempt.exempt_amount';
 465     push @peritem,  'exempt_amount';
 466     push @peritem_desc, 'Exempt';
 467     push @total,    'SUM(exempt_amount)';
 468     push @total_desc, "$money_char%.2f tax-exempt";
 469
 470   } elsif ( $cgi->param('taxable') ) {
 471
 472     my $taxable = 'cust_bill_pkg.setup + cust_bill_pkg.recur '.
 473                   '- COALESCE(item_exempt.exempt_amount, 0)';
 474
 475     push @where,    'item_tax.invnum IS NOT NULL';
 476     push @select,   "($taxable) AS taxable_amount";
 477     push @peritem,  'taxable_amount';
 478     push @peritem_desc, 'Taxable';
 479     push @total,    "SUM($taxable)";
 480     push @total_desc, "$money_char%.2f taxable";
 481
 482   } elsif ( $cgi->param('out') ) {
 483
 484     push @where,    'item_tax.invnum IS NULL',
 485                     'item_exempt.billpkgnum IS NULL';
 486
 487   } elsif ( @tax_where ) {
 488
 489     # union of taxable + all exempt_ cases
 490     push @where,
 491       '(item_tax.invnum IS NOT NULL OR item_exempt.billpkgnum IS NOT NULL)';
 492
 493   }
 494
 495   # recur/usage separation
 496   if ( $cgi->param('usage') eq 'recurring' ) {
 497
 498     my $recur_no_usage = FS::cust_bill_pkg->charged_sql('', '', no_usage => 1);
 499     push @select, "($recur_no_usage) AS recur_no_usage";
 500     $peritem[1] = 'recur_no_usage';
 501     $total[1] = "SUM(cust_bill_pkg.setup + $recur_no_usage)";
 502     $total_desc[0] .= ' (excluding usage)';
 503
 504   } elsif ( $cgi->param('usage') eq 'usage' ) {
 505
 506     my $usage = FS::cust_bill_pkg->usage_sql();
 507     push @select, "($usage) AS _usage";
 508     # there's already a method named 'usage'
 509     $peritem[1] = '_usage';
 510     $peritem_desc[1] = 'Usage charge';
 511     $total[1] = "SUM($usage)";
 512     $total_desc[0] .= ' usage charges';
 513   }
 514
 515 } elsif ( $cgi->param('istax') ) {
 516
 517   @peritem = ( 'setup' ); # taxes only have setup
 518   @peritem_desc = ( 'Tax charge' );
 519
 520   push @where, 'cust_bill_pkg.pkgnum = 0';
 521
 522   # tax location when using tax_rate_location
 523   if ( scalar( grep( /locationtaxid/, $cgi->param ) ) ) {
 524
 525     $join_pkg .= ' LEFT JOIN cust_bill_pkg_tax_rate_location USING ( billpkgnum ) '.
 526                  ' LEFT JOIN tax_rate_location USING ( taxratelocationnum )';
 527     push @where, FS::tax_rate_location->location_sql(
 528                    map { $_ => (scalar($cgi->param($_)) || '') }
 529                      qw( district city county state locationtaxid )
 530                  );
 531
 532     $total[1] = 'SUM(
 533       COALESCE(cust_bill_pkg_tax_rate_location.amount,
 534                cust_bill_pkg.setup + cust_bill_pkg.recur)
 535     )';
 536
 537   } elsif ( $cgi->param('out') ) {
 538
 539     $join_pkg .= '
 540       LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum)
 541     ';
 542     push @where, 'cust_bill_pkg_tax_location.billpkgnum IS NULL';
 543
 544     # each billpkgnum should appear only once
 545     $total[0] = 'COUNT(*)';
 546     $total[1] = 'SUM(cust_bill_pkg.setup)';
 547
 548   } else { # not locationtaxid or 'out'--the normal case
 549
 550     $join_pkg .= '
 551       LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum)
 552       JOIN cust_main_county           USING (taxnum)
 553     ';
 554
 555     # don't double-count the components of consolidated taxes
 556     $total[0] = 'COUNT(DISTINCT cust_bill_pkg.billpkgnum)';
 557     $total[1] = 'SUM(cust_bill_pkg_tax_location.amount)';
 558   }
 559
 560   # taxclass
 561   if ( $cgi->param('taxclassNULL') ) {
 562     push @where, 'cust_main_county.taxclass IS NULL';
 563   }
 564
 565   # taxname
 566   if ( $cgi->param('taxnameNULL') ) {
 567     push @where, 'cust_main_county.taxname IS NULL OR '.
 568                  'cust_main_county.taxname = \'Tax\'';
 569   } elsif ( $cgi->param('taxname') ) {
 570     push @where, 'cust_main_county.taxname = '.
 571                   dbh->quote($cgi->param('taxname'));
 572   }
 573
 574   # specific taxnums
 575   if ( $cgi->param('taxnum') ) {
 576     my $taxnum_in = join(',',
 577       grep /^\d+$/, $cgi->param('taxnum')
 578     );
 579     push @where, "cust_main_county.taxnum IN ($taxnum_in)"
 580       if $taxnum_in;
 581   }
 582
 583   # report group (itemdesc)
 584   if ( $cgi->param('report_group') =~ /^(=|!=) (.*)$/ ) {
 585     my ( $group_op, $group_value ) = ( $1, $2 );
 586     if ( $group_op eq '=' ) {
 587       #push @where, 'itemdesc LIKE '. dbh->quote($group_value.'%');
 588       push @where, 'itemdesc = '. dbh->quote($group_value);
 589     } elsif ( $group_op eq '!=' ) {
 590       push @where, '( itemdesc != '. dbh->quote($group_value) .' OR itemdesc IS NULL )';
 591     } else {
 592       die "guru meditation #00de: group_op $group_op\n";
 593     }
 594   }
 595
 596   # itemdesc, for some reason
 597   if ( $cgi->param('itemdesc') ) {
 598     if ( $cgi->param('itemdesc') eq 'Tax' ) {
 599       push @where, "(itemdesc='Tax' OR itemdesc is null)";
 600     } else {
 601       push @where, 'itemdesc='. dbh->quote($cgi->param('itemdesc'));
 602     }
 603   }
 604
 605 } # nottax / istax
 606
 607
 608 #total payments
 609 my $pay_sub = "SELECT SUM(cust_bill_pay_pkg.amount)
 610                  FROM cust_bill_pay_pkg
 611                    WHERE cust_bill_pkg.billpkgnum = cust_bill_pay_pkg.billpkgnum
 612               ";
 613 push @select, "($pay_sub) AS pay_amount";
 614
 615
 616 # credit
 617 if ( $cgi->param('credit') ) {
 618
 619   my $credit_sub;
 620
 621   if ( $cgi->param('istax') ) {
 622     # then we need to group/join by billpkgtaxlocationnum, to get only the
 623     # relevant part of partial taxes
 624     my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
 625       reason.reason as reason_text, access_user.username AS username_text,
 626       billpkgtaxlocationnum, billpkgnum
 627     FROM cust_credit_bill_pkg
 628       JOIN cust_credit_bill USING (creditbillnum)
 629       JOIN cust_credit USING (crednum)
 630       LEFT JOIN reason USING (reasonnum)
 631       LEFT JOIN access_user USING (usernum)
 632     GROUP BY billpkgnum, billpkgtaxlocationnum, reason.reason,
 633       access_user.username";
 634
 635     if ( $cgi->param('out') ) {
 636
 637       # find credits that are applied to the line items, but not to
 638       # a cust_bill_pkg_tax_location link
 639       $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
 640         USING (billpkgnum)";
 641       push @where, 'item_credit.billpkgtaxlocationnum IS NULL';
 642
 643     } else {
 644
 645       # find credits that are applied to the CBPTL links that are
 646       # considered "interesting" by the report criteria
 647       $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
 648         USING (billpkgtaxlocationnum)";
 649
 650     }
 651
 652   } else {
 653     # then only group by billpkgnum
 654     my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
 655       reason.reason as reason_text, access_user.username AS username_text,
 656       billpkgnum
 657     FROM cust_credit_bill_pkg
 658       JOIN cust_credit_bill USING (creditbillnum)
 659       JOIN cust_credit USING (crednum)
 660       LEFT JOIN reason USING (reasonnum)
 661       LEFT JOIN access_user USING (usernum)
 662     GROUP BY billpkgnum, reason.reason, access_user.username";
 663     $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit USING (billpkgnum)";
 664   }
 665
 666   push @where,    'item_credit.billpkgnum IS NOT NULL';
 667   push @select,   'item_credit.credit_amount',
 668                   'item_credit.username_text',
 669                   'item_credit.reason_text';
 670   push @peritem,  'credit_amount', 'username_text', 'reason_text';
 671   push @peritem_desc, 'Credited', 'By', 'Reason';
 672   push @total,    'SUM(credit_amount)';
 673   push @total_desc, "$money_char%.2f credited";
 674
 675 } else {
 676
 677   #still want a credit total column
 678
 679   my $credit_sub = "
 680     SELECT SUM(cust_credit_bill_pkg.amount)
 681       FROM cust_credit_bill_pkg
 682         WHERE cust_bill_pkg.billpkgnum = cust_credit_bill_pkg.billpkgnum
 683   ";
 684   push @select, "($credit_sub) AS credit_amount";
 685
 686 }
 687
 688 push @select, 'cust_main.custnum', FS::UI::Web::cust_sql_fields();
 689
 690 #salesnum
 691 if ( $cgi->param('salesnum') =~ /^(\d+)$/ ) {
 692
 693   my $salesnum = $1;
 694   my $sales = FS::sales->by_key($salesnum)
 695     or die "salesnum $salesnum not found";
 696
 697   my $subsearch = $sales->cust_bill_pkg_search('', '',
 698     'cust_main_sales' => ($cgi->param('cust_main_sales') ? 1 : 0),
 699     'paid'            => ($cgi->param('paid') ? 1 : 0),
 700     'classnum'        => scalar($cgi->param('classnum'))
 701   );
 702   $join_pkg .= " JOIN sales_pkg_class ON ( COALESCE(sales_pkg_class.classnum, 0) = COALESCE( part_pkg.classnum, 0) )";
 703
 704   my $extra_sql = $subsearch->{extra_sql};
 705   $extra_sql =~ s/^WHERE//;
 706   push @where, $extra_sql;
 707
 708   $cgi->param('classnum', 0) unless $cgi->param('classnum');
 709 }
 710
 711
 712 my $where = join(' AND ', @where);
 713 $where &&= "WHERE $where";
 714
 715 my $query = {
 716   'table'     => 'cust_bill_pkg',
 717   'addl_from' => "$join_pkg $join_cust",
 718   'hashref'   => {},
 719   'select'    => join(",\n", @select ),
 720   'extra_sql' => $where,
 721   'order_by'  => 'ORDER BY cust_bill._date, cust_bill_pkg.billpkgnum',
 722 };
 723
 724 my $count_query =
 725   'SELECT ' . join(',', @total) .
 726   " FROM cust_bill_pkg $join_pkg $join_cust
 727   $where";
 728
 729 @peritem_desc = map {emt($_)} @peritem_desc;
 730 my @peritem_sub = map {
 731   my $field = $_;
 732   if ($field =~ /_text$/) { # kludge for credit reason/username fields
 733     sub {$_[0]->get($field)};
 734   } else {
 735     sub { sprintf($money_char.'%.2f', $_[0]->get($field)) }
 736   }
 737 } @peritem;
 738 my @peritem_null = map { '' } @peritem; # placeholders
 739 my $peritem_align = 'r' x scalar(@peritem);
 740
 741 @currency_desc = map {emt($_)} @currency_desc;
 742 my @currency_null = map { '' } @currency; # placeholders
 743 my $currency_align = 'r' x scalar(@currency);
 744
 745 my $ilink = [ "${p}view/cust_bill.cgi?", 'invnum' ];
 746 my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ];
 747
 748 my $pay_link    = ''; #[, 'billpkgnum', ];
 749 my $credit_link = [ "${p}search/cust_credit_bill_pkg.html?billpkgnum=", 'billpkgnum', ];
 750
 751 warn "\n\nQUERY:\n".Dumper($query)."\n\nCOUNT_QUERY:\n$count_query\n\n"
 752   if $cgi->param('debug');
 753
 754 </%init>