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