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