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