FS/FS/cust_pkg/Search.pm

   1 package FS::cust_pkg::Search;
   2
   3 use strict;
   4 use FS::CurrentUser;
   5 use FS::UI::Web;
   6 use FS::cust_main;
   7 use FS::cust_pkg;
   8
   9 =item search HASHREF
  10
  11 (Class method)
  12
  13 Returns a qsearch hash expression to search for parameters specified in HASHREF.
  14 Valid parameters are
  15
  16 =over 4
  17
  18 =item agentnum
  19
  20 =item status
  21
  22 on hold, active, inactive (or one-time charge), suspended, canceled (or cancelled)
  23
  24 =item magic
  25
  26 Equivalent to "status", except that "canceled"/"cancelled" will exclude
  27 packages that were changed into a new package with the same pkgpart (i.e.
  28 location or quantity changes).
  29
  30 =item custom
  31
  32  boolean selects custom packages
  33
  34 =item classnum
  35
  36 =item pkgpart
  37
  38 pkgpart or arrayref or hashref of pkgparts
  39
  40 =item setup
  41
  42 arrayref of beginning and ending epoch date
  43
  44 =item last_bill
  45
  46 arrayref of beginning and ending epoch date
  47
  48 =item bill
  49
  50 arrayref of beginning and ending epoch date
  51
  52 =item adjourn
  53
  54 arrayref of beginning and ending epoch date
  55
  56 =item susp
  57
  58 arrayref of beginning and ending epoch date
  59
  60 =item expire
  61
  62 arrayref of beginning and ending epoch date
  63
  64 =item cancel
  65
  66 arrayref of beginning and ending epoch date
  67
  68 =item query
  69
  70 pkgnum or APKG_pkgnum
  71
  72 =item cust_fields
  73
  74 a value suited to passing to FS::UI::Web::cust_header
  75
  76 =item CurrentUser
  77
  78 specifies the user for agent virtualization
  79
  80 =item fcc_line
  81
  82 boolean; if true, returns only packages with more than 0 FCC phone lines.
  83
  84 =item state, country
  85
  86 Limit to packages with a service location in the specified state and country.
  87 For FCC 477 reporting, mostly.
  88
  89 =item location_cust
  90
  91 Limit to packages whose service locations are the same as the customer's
  92 default service location.
  93
  94 =item location_nocust
  95
  96 Limit to packages whose service locations are not the customer's default
  97 service location.
  98
  99 =item location_census
 100
 101 Limit to packages whose service locations have census tracts.
 102
 103 =item location_nocensus
 104
 105 Limit to packages whose service locations do not have a census tract.
 106
 107 =item location_geocode
 108
 109 Limit to packages whose locations have geocodes.
 110
 111 =item location_geocode
 112
 113 Limit to packages whose locations do not have geocodes.
 114
 115 =item towernum
 116
 117 Limit to packages associated with a svc_broadband, associated with a sector,
 118 associated with this towernum (or any of these, if it's an arrayref) (or NO
 119 towernum, if it's zero). This is an extreme niche case.
 120
 121 =item 477part, 477rownum, date
 122
 123 Limit to packages included in a specific row of one of the FCC 477 reports.
 124 '477part' is the section name (see L<FS::Report::FCC_477> methods), 'date'
 125 is the report as-of date (completely unrelated to the package setup/bill/
 126 other date fields), and '477rownum' is the row number of the report starting
 127 with zero. Row numbers have no inherent meaning, so this is useful only
 128 for explaining a 477 report you've already run.
 129
 130 =back
 131
 132 =cut
 133
 134 sub search {
 135   my ($class, $params) = @_;
 136   my @where = ();
 137
 138   ##
 139   # parse agent
 140   ##
 141
 142   if ( $params->{'agentnum'} =~ /^(\d+)$/ and $1 ) {
 143     push @where,
 144       "cust_main.agentnum = $1";
 145   }
 146
 147   ##
 148   # parse cust_status
 149   ##
 150
 151   if ( $params->{'cust_status'} =~ /^([a-z]+)$/ ) {
 152     push @where, FS::cust_main->cust_status_sql . " = '$1' ";
 153   }
 154
 155   ##
 156   # parse customer sales person
 157   ##
 158
 159   if ( $params->{'cust_main_salesnum'} =~ /^(\d+)$/ ) {
 160     push @where, ($1 > 0) ? "cust_main.salesnum = $1"
 161                           : 'cust_main.salesnum IS NULL';
 162   }
 163
 164
 165   ##
 166   # parse sales person
 167   ##
 168
 169   if ( $params->{'salesnum'} =~ /^(\d+)$/ ) {
 170     push @where, ($1 > 0) ? "cust_pkg.salesnum = $1"
 171                           : 'cust_pkg.salesnum IS NULL';
 172   }
 173
 174   ##
 175   # parse custnum
 176   ##
 177
 178   if ( $params->{'custnum'} =~ /^(\d+)$/ and $1 ) {
 179     push @where,
 180       "cust_pkg.custnum = $1";
 181   }
 182
 183   ##
 184   # custbatch
 185   ##
 186
 187   if ( $params->{'pkgbatch'} =~ /^([\w\/\-\:\.]+)$/ and $1 ) {
 188     push @where,
 189       "cust_pkg.pkgbatch = '$1'";
 190   }
 191
 192   ##
 193   # parse status
 194   ##
 195
 196   if (    $params->{'magic'}  eq 'active'
 197        || $params->{'status'} eq 'active' ) {
 198
 199     push @where, FS::cust_pkg->active_sql();
 200
 201   } elsif (    $params->{'magic'}  =~ /^not[ _]yet[ _]billed$/
 202             || $params->{'status'} =~ /^not[ _]yet[ _]billed$/ ) {
 203
 204     push @where, FS::cust_pkg->not_yet_billed_sql();
 205
 206   } elsif (    $params->{'magic'}  =~ /^(one-time charge|inactive)/
 207             || $params->{'status'} =~ /^(one-time charge|inactive)/ ) {
 208
 209     push @where, FS::cust_pkg->inactive_sql();
 210
 211   } elsif (    $params->{'magic'}  =~ /^on[ _]hold$/
 212             || $params->{'status'} =~ /^on[ _]hold$/ ) {
 213
 214     push @where, FS::cust_pkg->on_hold_sql();
 215
 216
 217   } elsif (    $params->{'magic'}  eq 'suspended'
 218             || $params->{'status'} eq 'suspended'  ) {
 219
 220     push @where, FS::cust_pkg->suspended_sql();
 221
 222   } elsif (    $params->{'magic'}  =~ /^cancell?ed$/
 223             || $params->{'status'} =~ /^cancell?ed$/ ) {
 224
 225     push @where, FS::cust_pkg->cancelled_sql();
 226
 227   }
 228
 229   ### special case: "magic" is used in detail links from browse/part_pkg,
 230   # where "cancelled" has the restriction "and not replaced with a package
 231   # of the same pkgpart".  Be consistent with that.
 232   ###
 233
 234   if ( $params->{'magic'} =~ /^cancell?ed$/ ) {
 235     my $new_pkgpart = "SELECT pkgpart FROM cust_pkg AS cust_pkg_next ".
 236                       "WHERE cust_pkg_next.change_pkgnum = cust_pkg.pkgnum";
 237     # ...may not exist, if this was just canceled and not changed; in that
 238     # case give it a "new pkgpart" that never equals the old pkgpart
 239     push @where, "COALESCE(($new_pkgpart), 0) != cust_pkg.pkgpart";
 240   }
 241
 242   ###
 243   # parse package class
 244   ###
 245
 246   if ( exists($params->{'classnum'}) ) {
 247
 248     my @classnum = ();
 249     if ( ref($params->{'classnum'}) ) {
 250
 251       if ( ref($params->{'classnum'}) eq 'HASH' ) {
 252         @classnum = grep $params->{'classnum'}{$_}, keys %{ $params->{'classnum'} };
 253       } elsif ( ref($params->{'classnum'}) eq 'ARRAY' ) {
 254         @classnum = @{ $params->{'classnum'} };
 255       } else {
 256         die 'unhandled classnum ref '. $params->{'classnum'};
 257       }
 258
 259
 260     } elsif ( $params->{'classnum'} =~ /^(\d*)$/ && $1 ne '0' ) {
 261       @classnum = ( $1 );
 262     }
 263
 264     if ( @classnum ) {
 265
 266       my @c_where = ();
 267       my @nums = grep $_, @classnum;
 268       push @c_where, 'part_pkg.classnum IN ('. join(',',@nums). ')' if @nums;
 269       my $null = scalar( grep { $_ eq '' } @classnum );
 270       push @c_where, 'part_pkg.classnum IS NULL' if $null;
 271
 272       if ( scalar(@c_where) == 1 ) {
 273         push @where, @c_where;
 274       } elsif ( @c_where ) {
 275         push @where, ' ( '. join(' OR ', @c_where). ' ) ';
 276       }
 277
 278     }
 279
 280
 281   }
 282
 283   ###
 284   # parse refnum (advertising source)
 285   ###
 286
 287   if ( exists($params->{'refnum'}) ) {
 288     my @refnum;
 289     if (ref $params->{'refnum'}) {
 290       @refnum = @{ $params->{'refnum'} };
 291     } else {
 292       @refnum = ( $params->{'refnum'} );
 293     }
 294     my $in = join(',', grep /^\d+$/, @refnum);
 295     push @where, "refnum IN($in)" if length $in;
 296   }
 297
 298   ###
 299   # parse package report options
 300   ###
 301
 302   my @report_option = ();
 303   if ( exists($params->{'report_option'}) ) {
 304     if ( ref($params->{'report_option'}) eq 'ARRAY' ) {
 305       @report_option = @{ $params->{'report_option'} };
 306     } elsif ( $params->{'report_option'} =~ /^([,\d]*)$/ ) {
 307       @report_option = split(',', $1);
 308     }
 309
 310   }
 311
 312   if (@report_option) {
 313     # this will result in the empty set for the dangling comma case as it should
 314     push @where,
 315       map{ "EXISTS ( SELECT 1 FROM part_pkg_option
 316                        WHERE part_pkg_option.pkgpart = part_pkg.pkgpart
 317                        AND optionname = 'report_option_$_'
 318                        AND optionvalue = '1' )"
 319          } @report_option;
 320   }
 321
 322   foreach my $any ( grep /^report_option_any/, keys %$params ) {
 323
 324     my @report_option_any = ();
 325     if ( ref($params->{$any}) eq 'ARRAY' ) {
 326       @report_option_any = @{ $params->{$any} };
 327     } elsif ( $params->{$any} =~ /^([,\d]*)$/ ) {
 328       @report_option_any = split(',', $1);
 329     }
 330
 331     if (@report_option_any) {
 332       # this will result in the empty set for the dangling comma case as it should
 333       push @where, ' ( '. join(' OR ',
 334         map{ "EXISTS ( SELECT 1 FROM part_pkg_option
 335                          WHERE part_pkg_option.pkgpart = part_pkg.pkgpart
 336                          AND optionname = 'report_option_$_'
 337                          AND optionvalue = '1' )"
 338            } @report_option_any
 339       ). ' ) ';
 340     }
 341
 342   }
 343
 344   ###
 345   # parse custom
 346   ###
 347
 348   push @where,  "part_pkg.custom = 'Y'" if $params->{custom};
 349
 350   ###
 351   # parse fcc_line
 352   ###
 353
 354   push @where,  "(part_pkg.fcc_ds0s > 0 OR pkg_class.fcc_ds0s > 0)"
 355                                                         if $params->{fcc_line};
 356
 357   ###
 358   # parse censustract
 359   ###
 360
 361   if ( exists($params->{'censustract'}) ) {
 362     $params->{'censustract'} =~ /^([.\d]*)$/;
 363     my $censustract = "cust_location.censustract = '$1'";
 364     $censustract .= ' OR cust_location.censustract is NULL' unless $1;
 365     push @where,  "( $censustract )";
 366   }
 367
 368   ###
 369   # parse censustract2
 370   ###
 371   if ( exists($params->{'censustract2'})
 372        && $params->{'censustract2'} =~ /^(\d*)$/
 373      )
 374   {
 375     if ($1) {
 376       push @where, "cust_location.censustract LIKE '$1%'";
 377     } else {
 378       push @where,
 379         "( cust_location.censustract = '' OR cust_location.censustract IS NULL )";
 380     }
 381   }
 382
 383   ###
 384   # parse country/state/zip
 385   ###
 386   for (qw(state country)) { # parsing rules are the same for these
 387   if ( exists($params->{$_})
 388     && uc($params->{$_}) =~ /^([A-Z]{2})$/ )
 389     {
 390       # XXX post-2.3 only--before that, state/country may be in cust_main
 391       push @where, "cust_location.$_ = '$1'";
 392     }
 393   }
 394   if ( exists($params->{zip}) ) {
 395     push @where, "cust_location.zip = " . dbh->quote($params->{zip});
 396   }
 397
 398   ###
 399   # location_* flags
 400   ###
 401   if ( $params->{location_cust} xor $params->{location_nocust} ) {
 402     my $op = $params->{location_cust} ? '=' : '!=';
 403     push @where, "cust_location.locationnum $op cust_main.ship_locationnum";
 404   }
 405   if ( $params->{location_census} xor $params->{location_nocensus} ) {
 406     my $op = $params->{location_census} ? "IS NOT NULL" : "IS NULL";
 407     push @where, "cust_location.censustract $op";
 408   }
 409   if ( $params->{location_geocode} xor $params->{location_nogeocode} ) {
 410     my $op = $params->{location_geocode} ? "IS NOT NULL" : "IS NULL";
 411     push @where, "cust_location.geocode $op";
 412   }
 413
 414   ###
 415   # parse part_pkg
 416   ###
 417
 418   if ( ref($params->{'pkgpart'}) ) {
 419
 420     my @pkgpart = ();
 421     if ( ref($params->{'pkgpart'}) eq 'HASH' ) {
 422       @pkgpart = grep $params->{'pkgpart'}{$_}, keys %{ $params->{'pkgpart'} };
 423     } elsif ( ref($params->{'pkgpart'}) eq 'ARRAY' ) {
 424       @pkgpart = @{ $params->{'pkgpart'} };
 425     } else {
 426       die 'unhandled pkgpart ref '. $params->{'pkgpart'};
 427     }
 428
 429     @pkgpart = grep /^(\d+)$/, @pkgpart;
 430
 431     push @where, 'pkgpart IN ('. join(',', @pkgpart). ')' if scalar(@pkgpart);
 432
 433   } elsif ( $params->{'pkgpart'} =~ /^(\d+)$/ ) {
 434     push @where, "pkgpart = $1";
 435   }
 436
 437   ###
 438   # parse dates
 439   ###
 440
 441   my $orderby = '';
 442
 443   #false laziness w/report_cust_pkg.html
 444   my %disable = (
 445     'all'             => {},
 446     'one-time charge' => { 'last_bill'=>1, 'bill'=>1, 'adjourn'=>1, 'susp'=>1, 'expire'=>1, 'cancel'=>1, },
 447     'active'          => { 'susp'=>1, 'cancel'=>1 },
 448     'suspended'       => { 'cancel' => 1 },
 449     'cancelled'       => {},
 450     ''                => {},
 451   );
 452
 453   if( exists($params->{'active'} ) ) {
 454     # This overrides all the other date-related fields, and includes packages
 455     # that were active at some time during the interval.  It excludes:
 456     # - packages that were set up after the end of the interval
 457     # - packages that were canceled before the start of the interval
 458     # - packages that were suspended before the start of the interval
 459     #   and are still suspended now
 460     my($beginning, $ending) = @{$params->{'active'}};
 461     push @where,
 462       "cust_pkg.setup IS NOT NULL",
 463       "cust_pkg.setup <= $ending",
 464       "(cust_pkg.cancel IS NULL OR cust_pkg.cancel >= $beginning )",
 465       "(cust_pkg.susp   IS NULL OR cust_pkg.susp   >= $beginning )",
 466       "NOT (".FS::cust_pkg->onetime_sql . ")";
 467   }
 468   else {
 469     my $exclude_change_from = 0;
 470     my $exclude_change_to = 0;
 471
 472     foreach my $field (qw( setup last_bill bill adjourn susp expire contract_end change_date cancel )) {
 473
 474       next unless exists($params->{$field});
 475
 476       my($beginning, $ending) = @{$params->{$field}};
 477
 478       next if $beginning == 0 && $ending == 4294967295;
 479
 480       push @where,
 481         "cust_pkg.$field IS NOT NULL",
 482         "cust_pkg.$field >= $beginning",
 483         "cust_pkg.$field <= $ending";
 484
 485       $orderby ||= "ORDER BY cust_pkg.$field";
 486
 487       if ( $field eq 'setup' ) {
 488         $exclude_change_from = 1;
 489       } elsif ( $field eq 'cancel' ) {
 490         $exclude_change_to = 1;
 491       } elsif ( $field eq 'change_date' ) {
 492         # if we are given setup and change_date ranges, and the setup date
 493         # falls in _both_ ranges, then include the package whether it was
 494         # a change or not
 495         $exclude_change_from = 0;
 496       }
 497     }
 498
 499     if ($exclude_change_from) {
 500       push @where, "change_pkgnum IS NULL";
 501     }
 502     if ($exclude_change_to) {
 503       # a join might be more efficient here
 504       push @where, "NOT EXISTS(
 505         SELECT 1 FROM cust_pkg AS changed_to_pkg
 506         WHERE cust_pkg.pkgnum = changed_to_pkg.change_pkgnum
 507       )";
 508     }
 509   }
 510
 511   $orderby ||= 'ORDER BY bill';
 512
 513   ###
 514   # parse magic, legacy, etc.
 515   ###
 516
 517   if ( $params->{'magic'} &&
 518        $params->{'magic'} =~ /^(active|inactive|suspended|cancell?ed)$/
 519   ) {
 520
 521     $orderby = 'ORDER BY pkgnum';
 522
 523     if ( $params->{'pkgpart'} =~ /^(\d+)$/ ) {
 524       push @where, "pkgpart = $1";
 525     }
 526
 527   } elsif ( $params->{'query'} eq 'pkgnum' ) {
 528
 529     $orderby = 'ORDER BY pkgnum';
 530
 531   } elsif ( $params->{'query'} eq 'APKG_pkgnum' ) {
 532
 533     $orderby = 'ORDER BY pkgnum';
 534
 535     push @where, '0 < (
 536       SELECT count(*) FROM pkg_svc
 537        WHERE pkg_svc.pkgpart =  cust_pkg.pkgpart
 538          AND pkg_svc.quantity > ( SELECT count(*) FROM cust_svc
 539                                    WHERE cust_svc.pkgnum  = cust_pkg.pkgnum
 540                                      AND cust_svc.svcpart = pkg_svc.svcpart
 541                                 )
 542     )';
 543
 544   }
 545
 546   ##
 547   # parse the extremely weird 'towernum' param
 548   ##
 549
 550   if ($params->{towernum}) {
 551     my $towernum = $params->{towernum};
 552     $towernum = [ $towernum ] if !ref($towernum);
 553     my $in = join(',', grep /^\d+$/, @$towernum);
 554     if (length $in) {
 555       # inefficient, but this is an obscure feature
 556       eval "use FS::Report::Table";
 557       FS::Report::Table->_init_tower_pkg_cache; # probably does nothing
 558       push @where, "EXISTS(
 559       SELECT 1 FROM tower_pkg_cache
 560       WHERE tower_pkg_cache.pkgnum = cust_pkg.pkgnum
 561         AND tower_pkg_cache.towernum IN ($in)
 562       )"
 563     }
 564   }
 565
 566   ##
 567   # parse the 477 report drill-down options
 568   ##
 569
 570   if ($params->{'477part'} =~ /^([a-z]+)$/) {
 571     my $section = $1;
 572     my ($date, $rownum, $agentnum);
 573     if ($params->{'date'} =~ /^(\d+)$/) {
 574       $date = $1;
 575     }
 576     if ($params->{'477rownum'} =~ /^(\d+)$/) {
 577       $rownum = $1;
 578     }
 579     if ($params->{'agentnum'} =~ /^(\d+)$/) {
 580       $agentnum = $1;
 581     }
 582     if ($date and defined($rownum)) {
 583       my $report = FS::Report::FCC_477->report($section,
 584         'date'      => $date,
 585         'agentnum'  => $agentnum,
 586         'detail'    => 1
 587       );
 588       my $pkgnums = $report->{detail}->[$rownum]
 589         or die "row $rownum is past the end of the report";
 590         # '0' so that if there are no pkgnums (empty string) it will create
 591         # a valid query that returns nothing
 592       warn "PKGNUMS:\n$pkgnums\n\n"; # XXX debug
 593
 594       # and this overrides everything
 595       @where = ( "cust_pkg.pkgnum IN($pkgnums)" );
 596     } # else we're missing some params, ignore the whole business
 597   }
 598
 599   ##
 600   # setup queries, links, subs, etc. for the search
 601   ##
 602
 603   # here is the agent virtualization
 604   if ($params->{CurrentUser}) {
 605     my $access_user =
 606       qsearchs('access_user', { username => $params->{CurrentUser} });
 607
 608     if ($access_user) {
 609       push @where, $access_user->agentnums_sql('table'=>'cust_main');
 610     } else {
 611       push @where, "1=0";
 612     }
 613   } else {
 614     push @where, $FS::CurrentUser::CurrentUser->agentnums_sql('table'=>'cust_main');
 615   }
 616
 617   my $extra_sql = scalar(@where) ? ' WHERE '. join(' AND ', @where) : '';
 618
 619   my $addl_from = 'LEFT JOIN part_pkg  USING ( pkgpart  ) '.
 620                   'LEFT JOIN pkg_class ON ( part_pkg.classnum = pkg_class.classnum ) '.
 621                   'LEFT JOIN cust_location USING ( locationnum ) '.
 622                   FS::UI::Web::join_cust_main('cust_pkg', 'cust_pkg');
 623
 624   my $select;
 625   my $count_query;
 626   if ( $params->{'select_zip5'} ) {
 627     my $zip = 'cust_location.zip';
 628
 629     $select = "DISTINCT substr($zip,1,5) as zip";
 630     $orderby = "ORDER BY substr($zip,1,5)";
 631     $count_query = "SELECT COUNT( DISTINCT substr($zip,1,5) )";
 632   } else {
 633     $select = join(', ',
 634                          'cust_pkg.*',
 635                          ( map "part_pkg.$_", qw( pkg freq ) ),
 636                          'pkg_class.classname',
 637                          'cust_main.custnum AS cust_main_custnum',
 638                          FS::UI::Web::cust_sql_fields(
 639                            $params->{'cust_fields'}
 640                          ),
 641                   );
 642     $count_query = 'SELECT COUNT(*)';
 643   }
 644
 645   $count_query .= " FROM cust_pkg $addl_from $extra_sql";
 646
 647   my $sql_query = {
 648     'table'       => 'cust_pkg',
 649     'hashref'     => {},
 650     'select'      => $select,
 651     'extra_sql'   => $extra_sql,
 652     'order_by'    => $orderby,
 653     'addl_from'   => $addl_from,
 654     'count_query' => $count_query,
 655   };
 656
 657 }
 658
 659 1;
 660