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'  => [ $money_char. '%.2f total',
   7                                     $unearned ? ( $money_char. '%.2f unearned revenue' ) : (),
   8                                   ],
   9                  'header'      => [
  10                    @pkgnum_header,
  11                    emt('Pkg Def'),
  12                    emt('Description'),
  13                    @post_desc_header,
  14                    ( $unearned
  15                      ? ( emt('Unearned'),
  16                          emt('Owed'), # useful in 'paid' mode?
  17                          emt('Payment date') )
  18                      : ( emt('Setup charge') )
  19                    ),
  20                    ( $use_usage eq 'usage'
  21                      ? emt('Usage charge')
  22                      : emt('Recurring charge')
  23                    ),
  24                    ( $unearned
  25                      ? ( emt('Charge start'), emt('Charge end') )
  26                      : ()
  27                    ),
  28                    emt('Invoice'),
  29                    emt('Date'),
  30                    emt('Paid'),
  31                    emt('Credited'),
  32                    FS::UI::Web::cust_header(),
  33                  ],
  34                  'fields'      => [
  35                    @pkgnum,
  36                    sub { $_[0]->pkgnum > 0
  37                            # possibly use override.pkg but i think this correct
  38                            ? $_[0]->get('pkgpart')
  39                            : ''
  40                        },
  41                    sub { $_[0]->pkgnum > 0
  42                            # possibly use override.pkg but i think this correct
  43                            ? $_[0]->get('pkg')
  44                            : $_[0]->get('itemdesc')
  45                        },
  46                    @post_desc,
  47                    #strikethrough or "N/A ($amount)" or something these when
  48                    # they're not applicable to pkg_tax search
  49                    sub { my $cust_bill_pkg = shift;
  50                          if ( $unearned ) {
  51
  52                            sprintf($money_char.'%.2f',
  53                              $cust_bill_pkg->unearned_revenue)
  54
  55                          } else {
  56                            sprintf($money_char.'%.2f', $cust_bill_pkg->setup );
  57                          }
  58                        },
  59                    ( $unearned
  60                      ? ( $owed_sub, $payment_date_sub, )
  61                      : ()
  62                    ),
  63                    sub { my $row = shift;
  64                          my $value = 0;
  65                          if ( $use_usage eq 'recurring' or $unearned ) {
  66                            $value = $row->recur - $row->usage;
  67                          } elsif ( $use_usage eq 'usage' ) {
  68                            $value = $row->usage;
  69                          } else {
  70                            $value = $row->recur;
  71                          }
  72                          sprintf($money_char.'%.2f', $value );
  73                        },
  74                    ( $unearned
  75                      ? ( sub { time2str('%b %d %Y', shift->sdate ) },
  76                        # shift edate back a day
  77                        # 82799 = 3600*23 - 1
  78                        # (to avoid skipping a day during DST)
  79                          sub { time2str('%b %d %Y', shift->edate - 82799 ) },
  80                        )
  81                      : ()
  82                    ),
  83                    'invnum',
  84                    sub { time2str('%b %d %Y', shift->_date ) },
  85                    sub { sprintf($money_char.'%.2f', shift->get('pay_amount')) },
  86                    sub { sprintf($money_char.'%.2f', shift->get('credit_amount')) },
  87                    \&FS::UI::Web::cust_fields,
  88                  ],
  89                  'sort_fields' => [
  90                    @pkgnum_null,
  91                    '',
  92                    '',
  93                    @post_desc_null,
  94                    'setup', #broken in $unearned case i guess
  95                    ( $unearned ? ('', '') : () ),
  96                    ( $use_usage eq 'recurring' or $unearned
  97                         ? 'recur - usage' :
  98                      $use_usage eq 'usage'
  99                         ? 'usage'
 100                         : 'recur'
 101                    ),
 102                    ( $unearned ? ('sdate', 'edate') : () ),
 103                    'invnum',
 104                    '_date',
 105                    #'pay_amount',
 106                    #'credit_amount',
 107                  ],
 108                  'links'       => [
 109                    @pkgnum_null,
 110                    '',
 111                    '',
 112                    @post_desc_null,
 113                    '',
 114                    ( $unearned ? ( '', '' ) : () ),
 115                    '',
 116                    ( $unearned ? ( '', '' ) : () ),
 117                    $ilink,
 118                    $ilink,
 119                    $pay_link,
 120                    $credit_link,
 121                    ( map { $_ ne 'Cust. Status' ? $clink : '' }
 122                          FS::UI::Web::cust_header()
 123                    ),
 124                  ],
 125                  #'align' => 'rlrrrc'.FS::UI::Web::cust_aligns(),
 126                  'align' => $pkgnum_align.
 127                             'rl'.
 128                             $post_desc_align.
 129                             'r'.
 130                             ( $unearned ? 'rc' : '' ).
 131                             'r'.
 132                             ( $unearned ? 'cc' : '' ).
 133                             'rcrr'.
 134                             FS::UI::Web::cust_aligns(),
 135                  'color' => [
 136                               @pkgnum_null,
 137                               '',
 138                               '',
 139                               @post_desc_null,
 140                               '',
 141                               ( $unearned ? ( '', '' ) : () ),
 142                               '',
 143                               ( $unearned ? ( '', '' ) : () ),
 144                               '',
 145                               '',
 146                               '',
 147                               '',
 148                               FS::UI::Web::cust_colors(),
 149                             ],
 150                  'style' => [
 151                               @pkgnum_null,
 152                               '',
 153                               '',
 154                               @post_desc_null,
 155                               '',
 156                               ( $unearned ? ( '', '' ) : () ),
 157                               '',
 158                               ( $unearned ? ( '', '' ) : () ),
 159                               '',
 160                               '',
 161                               '',
 162                               '',
 163                               FS::UI::Web::cust_styles(),
 164                             ],
 165 &>
 166 <%init>
 167
 168 #LOTS of false laziness below w/cust_credit_bill_pkg.cgi
 169
 170 my $curuser = $FS::CurrentUser::CurrentUser;
 171
 172 die "access denied" unless $curuser->access_right('Financial reports');
 173
 174 my $conf = new FS::Conf;
 175
 176 my $unearned = '';
 177 my $unearned_mode = '';
 178 my $unearned_base = '';
 179 my $unearned_sql = '';
 180
 181 my @select = ( 'cust_bill_pkg.*', 'cust_bill._date' );
 182
 183 my @pkgnum_header = ();
 184 my @pkgnum = ();
 185 my @pkgnum_null;
 186 my $pkgnum_align = '';
 187 if ( $curuser->option('show_pkgnum') ) {
 188   push @select, 'cust_bill_pkg.pkgnum';
 189   push @pkgnum_header, 'Pkg Num';
 190   push @pkgnum, sub { $_[0]->pkgnum > 0 ? $_[0]->pkgnum : '' };
 191   push @pkgnum_null, '';
 192   $pkgnum_align .= 'r';
 193 }
 194
 195 my @post_desc_header = ();
 196 my @post_desc = ();
 197 my @post_desc_null = ();
 198 my $post_desc_align = '';
 199 if ( $conf->exists('enable_taxclasses') ) {
 200   push @post_desc_header, 'Tax class';
 201   push @post_desc, 'taxclass';
 202   push @post_desc_null, '';
 203   $post_desc_align .= 'l';
 204   push @select, 'part_pkg.taxclass'; # or should this use override?
 205 }
 206
 207 #here is the agent virtualization
 208 my $agentnums_sql =
 209   $FS::CurrentUser::CurrentUser->agentnums_sql( 'table' => 'cust_main' );
 210
 211 my @where = ( $agentnums_sql );
 212
 213 my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
 214
 215 if ( $cgi->param('status') =~ /^([a-z]+)$/ ) {
 216   push @where, FS::cust_main->cust_status_sql . " = '$1'";
 217 }
 218
 219 if ( $cgi->param('distribute') == 1 ) {
 220   push @where, "sdate <= $ending",
 221                "edate >  $beginning",
 222   ;
 223 }
 224 else {
 225   push @where, "cust_bill._date >= $beginning",
 226                "cust_bill._date <= $ending";
 227 }
 228
 229 if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
 230   push @where, "cust_main.agentnum = $1";
 231 }
 232
 233 if ( $cgi->param('refnum') =~ /^(\d+)$/ ) {
 234   push @where, "cust_main.refnum = $1";
 235 }
 236
 237 # 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)
 238 if ( grep { $_ eq 'cust_classnum' } $cgi->param ) {
 239   my @classnums = grep /^\d*$/, $cgi->param('cust_classnum');
 240   push @where, 'COALESCE( cust_main.classnum, 0) IN ( '.
 241                    join(',', map { $_ || '0' } @classnums ).
 242                ' )'
 243     if @classnums;
 244 }
 245
 246
 247 # custnum
 248 if ( $cgi->param('custnum') =~ /^(\d+)$/ ) {
 249   push @where, "cust_main.custnum = $1";
 250 }
 251
 252 #classnum
 253 # not specified: all classes
 254 # 0: empty class
 255 # N: classnum
 256 my $use_override = $cgi->param('use_override');
 257 if ( $cgi->param('classnum') =~ /^(\d+)$/ ) {
 258   my $comparison = '';
 259   if ( $1 == 0 ) {
 260     $comparison = "IS NULL";
 261   } else {
 262     $comparison = "= $1";
 263   }
 264
 265   if ( $use_override ) {
 266     push @where, "(
 267       part_pkg.classnum $comparison AND pkgpart_override IS NULL OR
 268       override.classnum $comparison AND pkgpart_override IS NOT NULL
 269     )";
 270   } else {
 271     push @where, "part_pkg.classnum $comparison";
 272   }
 273 }
 274
 275 if ( $cgi->param('taxclass')
 276      && ! $cgi->param('istax')  #no part_pkg.taxclass in this case
 277                                 #(should we save a taxclass or a link to taxnum
 278                                 # in cust_bill_pkg or something like
 279                                 # cust_bill_pkg_tax_location?)
 280    )
 281 {
 282
 283   #override taxclass when use_override is specified?  probably
 284   #if ( $use_override ) {
 285   #
 286   #  push @where,
 287   #    ' ( '. join(' OR ',
 288   #                  map {
 289   #                        ' (    part_pkg.taxclass = '. dbh->quote($_).
 290   #                        '      AND pkgpart_override IS NULL '.
 291   #                        '   OR '.
 292   #                        '      override.taxclass = '. dbh->quote($_).
 293   #                        '      AND pkgpart_override IS NOT NULL '.
 294   #                        ' ) '
 295   #                      }
 296   #                      $cgi->param('taxclass')
 297   #               ).
 298   #    ' ) ';
 299   #
 300   #} else {
 301
 302     push @where, ' part_pkg.taxclass IN ( '.
 303                    join(', ', map dbh->quote($_), $cgi->param('taxclass') ).
 304                  ' ) ';
 305
 306   #}
 307
 308 }
 309
 310 my @loc_param = qw( district city county state country );
 311
 312 if ( $cgi->param('out') ) {
 313
 314   my ( $loc_sql, @param ) = FS::cust_pkg->location_sql( 'ornull' => 1 );
 315   while ( $loc_sql =~ /\?/ ) { #easier to do our own substitution
 316     $loc_sql =~ s/\?/'cust_main_county.'.shift(@param)/e;
 317   }
 318
 319   $loc_sql =~ s/cust_pkg\.locationnum/cust_bill_pkg_tax_location.locationnum/g
 320     if $cgi->param('istax');
 321
 322   push @where, "
 323     0 = (
 324           SELECT COUNT(*) FROM cust_main_county
 325            WHERE cust_main_county.tax > 0
 326              AND $loc_sql
 327         )
 328   ";
 329
 330   #not linked to by anything, but useful for debugging "out of taxable region"
 331   if ( grep $cgi->param($_), @loc_param ) {
 332
 333     my %ph = map { $_ => dbh->quote( scalar($cgi->param($_)) ) } @loc_param;
 334
 335     my ( $loc_sql, @param ) = FS::cust_pkg->location_sql;
 336     while ( $loc_sql =~ /\?/ ) { #easier to do our own substitution
 337       $loc_sql =~ s/\?/$ph{shift(@param)}/e;
 338     }
 339
 340     push @where, $loc_sql;
 341
 342   }
 343
 344 } elsif ( $cgi->param('country') ) {
 345
 346   my @counties = $cgi->param('county');
 347
 348   if ( scalar(@counties) > 1 ) {
 349
 350     #hacky, could be more efficient.  care if it is ever used for more than the
 351     # tax-report_groups filtering kludge
 352
 353     my $locs_sql =
 354       ' ( '. join(' OR ', map {
 355
 356           my %ph = ( 'county' => dbh->quote($_),
 357                      map { $_ => dbh->quote( $cgi->param($_) ) }
 358                        qw( district city state country )
 359                    );
 360
 361           my ( $loc_sql, @param ) = FS::cust_pkg->location_sql;
 362           while ( $loc_sql =~ /\?/ ) { #easier to do our own substitution
 363             $loc_sql =~ s/\?/$ph{shift(@param)}/e;
 364           }
 365
 366           $loc_sql;
 367
 368         } @counties
 369
 370       ). ' ) ';
 371
 372     push @where, $locs_sql;
 373
 374   } else {
 375
 376     my %ph = map { $_ => dbh->quote( scalar($cgi->param($_)) ) } @loc_param;
 377
 378     my ( $loc_sql, @param ) = FS::cust_pkg->location_sql;
 379     while ( $loc_sql =~ /\?/ ) { #easier to do our own substitution
 380       $loc_sql =~ s/\?/$ph{shift(@param)}/e;
 381     }
 382
 383     push @where, $loc_sql;
 384
 385   }
 386
 387   if ( $cgi->param('istax') ) {
 388     if ( $cgi->param('taxname') ) {
 389       push @where, 'itemdesc = '. dbh->quote( $cgi->param('taxname') );
 390     #} elsif ( $cgi->param('taxnameNULL') {
 391     } else {
 392       push @where, "( itemdesc IS NULL OR itemdesc = '' OR itemdesc = 'Tax' )";
 393     }
 394   } elsif ( $cgi->param('nottax') ) {
 395     #what can we usefully do with "taxname" ????  look up a class???
 396   } else {
 397     #warn "neither nottax nor istax parameters specified";
 398   }
 399
 400   if ( $cgi->param('taxclassNULL')
 401        && ! $cgi->param('istax')  #no part_pkg.taxclass in this case
 402                                   #(see comment above?)
 403      )
 404   {
 405     my %hash = ( 'country' => scalar($cgi->param('country')) );
 406     foreach (qw( state county )) {
 407       $hash{$_} = scalar($cgi->param($_)) if $cgi->param($_);
 408     }
 409     my $cust_main_county = qsearchs('cust_main_county', \%hash);
 410     die "unknown base region for empty taxclass" unless $cust_main_county;
 411
 412     my $same_sql = $cust_main_county->sql_taxclass_sameregion;
 413     $same_sql =~ s/taxclass/part_pkg.taxclass/g;
 414     push @where, $same_sql if $same_sql;
 415
 416   }
 417
 418 } elsif ( scalar( grep( /locationtaxid/, $cgi->param ) ) ) {
 419
 420   push @where, FS::tax_rate_location->location_sql(
 421                  map { $_ => (scalar($cgi->param($_)) || '') }
 422                    qw( district city county state locationtaxid )
 423                );
 424
 425 }
 426
 427 # unearned revenue mode
 428 if ( $cgi->param('unearned_now') =~ /^(\d+)$/ ) {
 429
 430   $unearned = $1;
 431   $unearned_mode = $cgi->param('mode');
 432
 433   push @where, "cust_bill_pkg.sdate < $unearned",
 434                "cust_bill_pkg.edate > $unearned",
 435                "cust_bill_pkg.recur != 0",
 436                "part_pkg.freq != '0'";
 437
 438   if ( !$cgi->param('include_monthly') ) {
 439     push @where,
 440                "part_pkg.freq != '1'",
 441                "part_pkg.freq NOT LIKE '%h'",
 442                "part_pkg.freq NOT LIKE '%d'",
 443                "part_pkg.freq NOT LIKE '%w'";
 444   }
 445
 446   my $usage_sql = FS::cust_bill_pkg->usage_sql;
 447   push @select, "($usage_sql) AS usage"; # we need this
 448   my $paid_sql = 'GREATEST(' .
 449     FS::cust_bill_pkg->paid_sql($unearned, '', setuprecur => 'recur') .
 450     " - $usage_sql, 0)";
 451
 452   push @select, "$paid_sql AS paid_no_usage"; # need this either way
 453
 454   if ( $unearned_mode eq 'paid' ) {
 455     # then use the amount paid, minus usage charges
 456     $unearned_base = $paid_sql;
 457   }
 458   else {
 459     # use the amount billed, minus usage charges and credits
 460     $unearned_base = "GREATEST( cust_bill_pkg.recur - ".
 461       FS::cust_bill_pkg->credited_sql($unearned, '', setuprecur => 'recur') .
 462       " - $usage_sql, 0)";
 463       # include only rows that have some non-usage, non-credited portion
 464   }
 465   # whatever we're using as the base, only show rows where it's positive
 466   push @where, "$unearned_base > 0";
 467
 468   my $period = "CAST(cust_bill_pkg.edate - cust_bill_pkg.sdate AS REAL)";
 469   my $elapsed = "GREATEST( $unearned - cust_bill_pkg.sdate, 0 )";
 470   my $remaining = "(1 - $elapsed/$period)";
 471
 472   $unearned_sql = "CAST( $unearned_base * $remaining AS DECIMAL(10,2) )";
 473   push @select, "$unearned_sql AS unearned_revenue";
 474
 475   # last payment/credit date
 476   my %t = (pay => 'cust_bill_pay', credit => 'cust_credit_bill');
 477   foreach my $x (qw(pay credit)) {
 478     my $table = $t{$x};
 479     my $link = $table.'_pkg';
 480     my $pkey = dbdef->table($table)->primary_key;
 481     my $last_date_sql = "SELECT MAX(_date)
 482     FROM $table JOIN $link USING ($pkey)
 483     WHERE $link.billpkgnum = cust_bill_pkg.billpkgnum
 484     AND $table._date <= $unearned";
 485     push @select, "($last_date_sql) AS last_$x";
 486   }
 487
 488 }
 489
 490 if ( $cgi->param('itemdesc') ) {
 491   if ( $cgi->param('itemdesc') eq 'Tax' ) {
 492     push @where, "(itemdesc='Tax' OR itemdesc is null)";
 493   } else {
 494     push @where, 'itemdesc='. dbh->quote($cgi->param('itemdesc'));
 495   }
 496 }
 497
 498 if ( $cgi->param('report_group') =~ /^(=|!=) (.*)$/ && $cgi->param('istax') ) {
 499   my ( $group_op, $group_value ) = ( $1, $2 );
 500   if ( $group_op eq '=' ) {
 501     #push @where, 'itemdesc LIKE '. dbh->quote($group_value.'%');
 502     push @where, 'itemdesc = '. dbh->quote($group_value);
 503   } elsif ( $group_op eq '!=' ) {
 504     push @where, '( itemdesc != '. dbh->quote($group_value) .' OR itemdesc IS NULL )';
 505   } else {
 506     die "guru meditation #00de: group_op $group_op\n";
 507   }
 508
 509 }
 510
 511 push @where, 'cust_bill_pkg.pkgnum != 0' if $cgi->param('nottax');
 512 push @where, 'cust_bill_pkg.pkgnum  = 0' if $cgi->param('istax');
 513
 514 if ( $cgi->param('cust_tax') ) {
 515   #false laziness -ish w/report_tax.cgi
 516   my $cust_exempt;
 517   if ( $cgi->param('taxname') ) {
 518     my $q_taxname = dbh->quote($cgi->param('taxname'));
 519     $cust_exempt =
 520       "( tax = 'Y'
 521          OR EXISTS ( SELECT 1 FROM cust_main_exemption
 522                        WHERE cust_main_exemption.custnum = cust_main.custnum
 523                          AND cust_main_exemption.taxname = $q_taxname )
 524        )
 525       ";
 526   } else {
 527     $cust_exempt = " tax = 'Y' ";
 528   }
 529
 530   push @where, $cust_exempt;
 531 }
 532
 533 my $use_usage = $cgi->param('use_usage');
 534
 535 my $count_query;
 536 if ( $cgi->param('pkg_tax') ) {
 537
 538   $count_query =
 539     "SELECT COUNT(*),
 540             SUM(
 541                  ( CASE WHEN part_pkg.setuptax = 'Y'
 542                         THEN cust_bill_pkg.setup
 543                         ELSE 0
 544                    END
 545                  )
 546                  +
 547                  ( CASE WHEN part_pkg.recurtax = 'Y'
 548                         THEN cust_bill_pkg.recur
 549                         ELSE 0
 550                    END
 551                  )
 552                )
 553     ";
 554
 555   push @where, "(    ( part_pkg.setuptax = 'Y' AND cust_bill_pkg.setup > 0 )
 556                   OR ( part_pkg.recurtax = 'Y' AND cust_bill_pkg.recur > 0 ) )",
 557                "( tax != 'Y' OR tax IS NULL )";
 558
 559 } elsif ( $cgi->param('taxable') ) {
 560
 561   my $setup_taxable = "(
 562     CASE WHEN part_pkg.setuptax = 'Y'
 563          THEN 0
 564          ELSE cust_bill_pkg.setup
 565     END
 566   )";
 567
 568   my $recur_taxable = "(
 569     CASE WHEN part_pkg.recurtax = 'Y'
 570          THEN 0
 571          ELSE cust_bill_pkg.recur
 572     END
 573   )";
 574
 575   my $exempt = "(
 576     SELECT COALESCE( SUM(amount), 0 ) FROM cust_tax_exempt_pkg
 577       WHERE cust_tax_exempt_pkg.billpkgnum = cust_bill_pkg.billpkgnum
 578   )";
 579
 580   $count_query =
 581     "SELECT COUNT(*), SUM( $setup_taxable + $recur_taxable - $exempt )";
 582
 583   push @where,
 584     #not tax-exempt package (setup or recur)
 585     "(
 586           ( ( part_pkg.setuptax != 'Y' OR part_pkg.setuptax IS NULL )
 587             AND cust_bill_pkg.setup > 0 )
 588        OR
 589           ( ( part_pkg.recurtax != 'Y' OR part_pkg.recurtax IS NULL )
 590             AND cust_bill_pkg.recur > 0 )
 591     )",
 592     #not a tax_exempt customer
 593     "( tax != 'Y' OR tax IS NULL )";
 594     #not covered in full by a monthly tax exemption (texas tax)
 595     "0 < ( $setup_taxable + $recur_taxable - $exempt )",
 596
 597 } else {
 598
 599   if ( $use_usage ) {
 600     $count_query = "SELECT COUNT(*), ";
 601   } else {
 602     $count_query = "SELECT COUNT(DISTINCT billpkgnum), ";
 603   }
 604
 605   if ( $unearned ) {
 606     $count_query .= "SUM( $unearned_base ), SUM( $unearned_sql )";
 607   } elsif ( $use_usage eq 'recurring' ) {
 608     $count_query .= "SUM(cust_bill_pkg.setup + cust_bill_pkg.recur - usage)";
 609   } elsif ( $use_usage eq 'usage' ) {
 610     $count_query .= "SUM(usage)";
 611   } elsif ( scalar( grep( /locationtaxid/, $cgi->param ) ) ) {
 612     $count_query .= "SUM( COALESCE(cust_bill_pkg_tax_rate_location.amount, cust_bill_pkg.setup + cust_bill_pkg.recur))";
 613   } elsif ( $cgi->param('iscredit') eq 'rate') {
 614     $count_query .= "SUM( cust_credit_bill_pkg.amount )";
 615   } else {
 616     $count_query .= "SUM(cust_bill_pkg.setup + cust_bill_pkg.recur)";
 617   }
 618
 619 }
 620
 621 my $join_cust =  '        JOIN cust_bill USING ( invnum )
 622                      LEFT JOIN cust_main USING ( custnum ) ';
 623
 624 # we want the package and its definition if available
 625 my $join_pkg =
 626 ' LEFT JOIN cust_pkg      USING (pkgnum)
 627   LEFT JOIN part_pkg      USING (pkgpart)';
 628
 629 my $part_pkg = 'part_pkg';
 630 if ( $cgi->param('use_override') ) { #"Separate sub-packages from parents"
 631   # still need the real part_pkg for tax applicability,
 632   # so alias this one
 633   $join_pkg .= " LEFT JOIN part_pkg AS override ON (
 634   COALESCE(cust_bill_pkg.pkgpart_override, cust_pkg.pkgpart, 0) = override.pkgpart
 635   )";
 636   $part_pkg = 'override';
 637 }
 638
 639 if ( $cgi->param('nottax') ) {
 640
 641   $join_pkg .= ' LEFT JOIN cust_location USING ( locationnum ) '
 642     if $conf->exists('tax-pkg_address');
 643
 644 } elsif ( $cgi->param('istax') ) {
 645
 646   #false laziness w/report_tax.cgi $taxfromwhere
 647   if ( scalar( grep( /locationtaxid/, $cgi->param ) ) ||
 648             $cgi->param('iscredit') eq 'rate') {
 649
 650     $join_pkg .=
 651       ' LEFT JOIN cust_bill_pkg_tax_rate_location USING ( billpkgnum ) '.
 652       ' LEFT JOIN tax_rate_location USING ( taxratelocationnum ) ';
 653
 654   } elsif ( $conf->exists('tax-pkg_address') ) {
 655
 656     $join_pkg .= '
 657       LEFT JOIN cust_bill_pkg_tax_location USING ( billpkgnum )
 658       LEFT JOIN cust_location ON cust_bill_pkg_tax_location.locationnum
 659                                  = cust_location.locationnum
 660     ';
 661
 662     #quelle kludge, somewhat false laziness w/report_tax.cgi
 663     s/cust_pkg\.locationnum/cust_bill_pkg_tax_location.locationnum/g for @where;
 664   }
 665
 666   if ( $cgi->param('iscredit') ) {
 667     $join_pkg .= ' JOIN cust_credit_bill_pkg USING ( billpkgnum';
 668     if ( $cgi->param('iscredit') eq 'rate' ) {
 669       $join_pkg .= ', billpkgtaxratelocationnum )';
 670     } elsif ( $conf->exists('tax-pkg_address') ) {
 671       $join_pkg .= ', billpkgtaxlocationnum )';
 672       push @where, "billpkgtaxratelocationnum IS NULL";
 673     } else {
 674       $join_pkg .= ' )';
 675       push @where, "billpkgtaxratelocationnum IS NULL";
 676     }
 677   }
 678
 679 } # nottax / istax
 680
 681
 682 #total payments
 683 my $pay_sub = "SELECT SUM(cust_bill_pay_pkg.amount)
 684                  FROM cust_bill_pay_pkg
 685                    WHERE cust_bill_pkg.billpkgnum = cust_bill_pay_pkg.billpkgnum
 686               ";
 687 push @select, "($pay_sub) AS pay_amount";
 688
 689 #total credits
 690 my $credit_sub = "
 691   SELECT SUM(cust_credit_bill_pkg.amount)
 692     FROM cust_credit_bill_pkg
 693       WHERE cust_bill_pkg.billpkgnum = cust_credit_bill_pkg.billpkgnum
 694 ";
 695 push @select, "($credit_sub) AS credit_amount";
 696
 697 my $where = ' WHERE '. join(' AND ', @where);
 698
 699 if ($use_usage) {
 700   $count_query .=
 701     " FROM (SELECT cust_bill_pkg.setup, cust_bill_pkg.recur,
 702              ( SELECT COALESCE( SUM(amount), 0 ) FROM cust_bill_pkg_detail
 703                WHERE cust_bill_pkg.billpkgnum = cust_bill_pkg_detail.billpkgnum
 704              ) AS usage FROM cust_bill_pkg  $join_cust $join_pkg $where
 705            ) AS countquery";
 706 } else {
 707   $count_query .= " FROM cust_bill_pkg $join_cust $join_pkg $where";
 708 }
 709
 710 push @select, 'part_pkg.pkgpart',
 711               'part_pkg.pkg',
 712               'part_pkg.freq';
 713
 714 push @select, 'cust_main.custnum',
 715               FS::UI::Web::cust_sql_fields();
 716
 717 my $query = {
 718   'table'     => 'cust_bill_pkg',
 719   'addl_from' => "$join_cust $join_pkg",
 720   'hashref'   => {},
 721   'select'    => join(",\n", @select ),
 722   'extra_sql' => $where,
 723   'order_by'  => 'ORDER BY cust_bill._date, billpkgnum',
 724 };
 725
 726 my $ilink = [ "${p}view/cust_bill.cgi?", 'invnum' ];
 727 my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ];
 728 my $pay_link    = ''; #[, 'billpkgnum', ];
 729 my $credit_link = [ "${p}search/cust_credit_bill_pkg.html?billpkgnum=", 'billpkgnum', ];
 730
 731 my $conf = new FS::Conf;
 732 my $money_char = $conf->config('money_char') || '$';
 733
 734 my $owed_sub = sub {
 735   $money_char . shift->get('owed') # owed_recur is not correct here
 736 };
 737 my $payment_date_sub = sub {
 738   #my $cust_bill_pkg = shift;
 739   my @cust_pay = sort { $a->_date <=> $b->_date }
 740                       map $_->cust_bill_pay->cust_pay,
 741                           shift->cust_bill_pay_pkg('recur') #recur :/
 742     or return '';
 743   time2str('%b %d %Y', $cust_pay[-1]->_date );
 744 };
 745
 746 warn "\n\nQUERY:\n".Dumper($query)."\n\nCOUNT_QUERY:\n$count_query\n\n"
 747   if $cgi->param('debug');
 748
 749 </%init>