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
 455     # This overrides all the other date-related fields, and includes packages
 456     # that were active at some time during the interval.  It excludes:
 457     # - packages that were set up after the end of the interval
 458     # - packages that were canceled before the start of the interval
 459     # - packages that were suspended before the start of the interval
 460     #   and are still suspended now
 461     my($beginning, $ending) = @{$params->{'active'}};
 462     push @where,
 463       "cust_pkg.setup IS NOT NULL",
 464       "cust_pkg.setup <= $ending",
 465       "(cust_pkg.cancel IS NULL OR cust_pkg.cancel >= $beginning )",
 466       "(cust_pkg.susp   IS NULL OR cust_pkg.susp   >= $beginning )",
 467       "NOT (".FS::cust_pkg->onetime_sql . ")";
 468
 469   } else {
 470
 471     my $exclude_change_from = 0;
 472     my $exclude_change_to = 0;
 473
 474     foreach my $field (qw( setup last_bill bill adjourn susp expire contract_end change_date cancel )) {
 475
 476       if ( $params->{$field.'_null'} ) {
 477
 478         push @where, "cust_pkg.$field IS NULL";
 479              # this should surely be obsoleted by now: OR cust_pkg.$field == 0 )
 480
 481       } else {
 482
 483         next unless exists($params->{$field});
 484
 485         my($beginning, $ending) = @{$params->{$field}};
 486
 487         next if $beginning == 0 && $ending == 4294967295;
 488
 489         push @where,
 490           "cust_pkg.$field IS NOT NULL",
 491           "cust_pkg.$field >= $beginning",
 492           "cust_pkg.$field <= $ending";
 493
 494         $orderby ||= "ORDER BY cust_pkg.$field";
 495
 496         if ( $field eq 'setup' ) {
 497           $exclude_change_from = 1;
 498         } elsif ( $field eq 'cancel' ) {
 499           $exclude_change_to = 1;
 500         } elsif ( $field eq 'change_date' ) {
 501           # if we are given setup and change_date ranges, and the setup date
 502           # falls in _both_ ranges, then include the package whether it was
 503           # a change or not
 504           $exclude_change_from = 0;
 505         }
 506
 507       }
 508
 509     }
 510
 511     if ($exclude_change_from) {
 512       push @where, "cust_pkg.change_pkgnum IS NULL";
 513     }
 514     if ($exclude_change_to) {
 515       # a join might be more efficient here
 516       push @where, "NOT EXISTS(
 517         SELECT 1 FROM cust_pkg AS changed_to_pkg
 518         WHERE cust_pkg.pkgnum = changed_to_pkg.change_pkgnum
 519       )";
 520     }
 521
 522   }
 523
 524   $orderby ||= 'ORDER BY bill';
 525
 526   ###
 527   # parse magic, legacy, etc.
 528   ###
 529
 530   if ( $params->{'magic'} &&
 531        $params->{'magic'} =~ /^(active|inactive|suspended|cancell?ed)$/
 532   ) {
 533
 534     $orderby = 'ORDER BY pkgnum';
 535
 536     if ( $params->{'pkgpart'} =~ /^(\d+)$/ ) {
 537       push @where, "pkgpart = $1";
 538     }
 539
 540   } elsif ( $params->{'query'} eq 'pkgnum' ) {
 541
 542     $orderby = 'ORDER BY pkgnum';
 543
 544   } elsif ( $params->{'query'} eq 'APKG_pkgnum' ) {
 545
 546     $orderby = 'ORDER BY pkgnum';
 547
 548     push @where, '0 < (
 549       SELECT count(*) FROM pkg_svc
 550        WHERE pkg_svc.pkgpart =  cust_pkg.pkgpart
 551          AND pkg_svc.quantity > ( SELECT count(*) FROM cust_svc
 552                                    WHERE cust_svc.pkgnum  = cust_pkg.pkgnum
 553                                      AND cust_svc.svcpart = pkg_svc.svcpart
 554                                 )
 555     )';
 556
 557   }
 558
 559   ##
 560   # parse the extremely weird 'towernum' param
 561   ##
 562
 563   if ($params->{towernum}) {
 564     my $towernum = $params->{towernum};
 565     $towernum = [ $towernum ] if !ref($towernum);
 566     my $in = join(',', grep /^\d+$/, @$towernum);
 567     if (length $in) {
 568       # inefficient, but this is an obscure feature
 569       eval "use FS::Report::Table";
 570       FS::Report::Table->_init_tower_pkg_cache; # probably does nothing
 571       push @where, "EXISTS(
 572       SELECT 1 FROM tower_pkg_cache
 573       WHERE tower_pkg_cache.pkgnum = cust_pkg.pkgnum
 574         AND tower_pkg_cache.towernum IN ($in)
 575       )"
 576     }
 577   }
 578
 579   ##
 580   # parse the 477 report drill-down options
 581   ##
 582
 583   if ($params->{'477part'} =~ /^([a-z]+)$/) {
 584     my $section = $1;
 585     my ($date, $rownum, $agentnum);
 586     if ($params->{'date'} =~ /^(\d+)$/) {
 587       $date = $1;
 588     }
 589     if ($params->{'477rownum'} =~ /^(\d+)$/) {
 590       $rownum = $1;
 591     }
 592     if ($params->{'agentnum'} =~ /^(\d+)$/) {
 593       $agentnum = $1;
 594     }
 595     if ($date and defined($rownum)) {
 596       my $report = FS::Report::FCC_477->report($section,
 597         'date'      => $date,
 598         'agentnum'  => $agentnum,
 599         'detail'    => 1
 600       );
 601       my $pkgnums = $report->{detail}->[$rownum]
 602         or die "row $rownum is past the end of the report";
 603         # '0' so that if there are no pkgnums (empty string) it will create
 604         # a valid query that returns nothing
 605       warn "PKGNUMS:\n$pkgnums\n\n"; # XXX debug
 606
 607       # and this overrides everything
 608       @where = ( "cust_pkg.pkgnum IN($pkgnums)" );
 609     } # else we're missing some params, ignore the whole business
 610   }
 611
 612   ##
 613   # setup queries, links, subs, etc. for the search
 614   ##
 615
 616   # here is the agent virtualization
 617   if ($params->{CurrentUser}) {
 618     my $access_user =
 619       qsearchs('access_user', { username => $params->{CurrentUser} });
 620
 621     if ($access_user) {
 622       push @where, $access_user->agentnums_sql('table'=>'cust_main');
 623     } else {
 624       push @where, "1=0";
 625     }
 626   } else {
 627     push @where, $FS::CurrentUser::CurrentUser->agentnums_sql('table'=>'cust_main');
 628   }
 629
 630   my $extra_sql = scalar(@where) ? ' WHERE '. join(' AND ', @where) : '';
 631
 632   my $addl_from = 'LEFT JOIN part_pkg  USING ( pkgpart  ) '.
 633                   'LEFT JOIN pkg_class ON ( part_pkg.classnum = pkg_class.classnum ) '.
 634                   'LEFT JOIN cust_location USING ( locationnum ) '.
 635                   FS::UI::Web::join_cust_main('cust_pkg', 'cust_pkg');
 636
 637   my $select;
 638   my $count_query;
 639   if ( $params->{'select_zip5'} ) {
 640     my $zip = 'cust_location.zip';
 641
 642     $select = "DISTINCT substr($zip,1,5) as zip";
 643     $orderby = "ORDER BY substr($zip,1,5)";
 644     $count_query = "SELECT COUNT( DISTINCT substr($zip,1,5) )";
 645   } else {
 646     $select = join(', ',
 647                          'cust_pkg.*',
 648                          ( map "part_pkg.$_", qw( pkg freq ) ),
 649                          'pkg_class.classname',
 650                          'cust_main.custnum AS cust_main_custnum',
 651                          FS::UI::Web::cust_sql_fields(
 652                            $params->{'cust_fields'}
 653                          ),
 654                   );
 655     $count_query = 'SELECT COUNT(*)';
 656   }
 657
 658   $count_query .= " FROM cust_pkg $addl_from $extra_sql";
 659
 660   my $sql_query = {
 661     'table'       => 'cust_pkg',
 662     'hashref'     => {},
 663     'select'      => $select,
 664     'extra_sql'   => $extra_sql,
 665     'order_by'    => $orderby,
 666     'addl_from'   => $addl_from,
 667     'count_query' => $count_query,
 668   };
 669
 670 }
 671
 672 1;
 673