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