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 =back
 116
 117 =cut
 118
 119 sub search {
 120   my ($class, $params) = @_;
 121   my @where = ();
 122
 123   ##
 124   # parse agent
 125   ##
 126
 127   if ( $params->{'agentnum'} =~ /^(\d+)$/ and $1 ) {
 128     push @where,
 129       "cust_main.agentnum = $1";
 130   }
 131
 132   ##
 133   # parse cust_status
 134   ##
 135
 136   if ( $params->{'cust_status'} =~ /^([a-z]+)$/ ) {
 137     push @where, FS::cust_main->cust_status_sql . " = '$1' ";
 138   }
 139
 140   ##
 141   # parse customer sales person
 142   ##
 143
 144   if ( $params->{'cust_main_salesnum'} =~ /^(\d+)$/ ) {
 145     push @where, ($1 > 0) ? "cust_main.salesnum = $1"
 146                           : 'cust_main.salesnum IS NULL';
 147   }
 148
 149
 150   ##
 151   # parse sales person
 152   ##
 153
 154   if ( $params->{'salesnum'} =~ /^(\d+)$/ ) {
 155     push @where, ($1 > 0) ? "cust_pkg.salesnum = $1"
 156                           : 'cust_pkg.salesnum IS NULL';
 157   }
 158
 159   ##
 160   # parse custnum
 161   ##
 162
 163   if ( $params->{'custnum'} =~ /^(\d+)$/ and $1 ) {
 164     push @where,
 165       "cust_pkg.custnum = $1";
 166   }
 167
 168   ##
 169   # custbatch
 170   ##
 171
 172   if ( $params->{'pkgbatch'} =~ /^([\w\/\-\:\.]+)$/ and $1 ) {
 173     push @where,
 174       "cust_pkg.pkgbatch = '$1'";
 175   }
 176
 177   ##
 178   # parse status
 179   ##
 180
 181   if (    $params->{'magic'}  eq 'active'
 182        || $params->{'status'} eq 'active' ) {
 183
 184     push @where, FS::cust_pkg->active_sql();
 185
 186   } elsif (    $params->{'magic'}  =~ /^not[ _]yet[ _]billed$/
 187             || $params->{'status'} =~ /^not[ _]yet[ _]billed$/ ) {
 188
 189     push @where, FS::cust_pkg->not_yet_billed_sql();
 190
 191   } elsif (    $params->{'magic'}  =~ /^(one-time charge|inactive)/
 192             || $params->{'status'} =~ /^(one-time charge|inactive)/ ) {
 193
 194     push @where, FS::cust_pkg->inactive_sql();
 195
 196   } elsif (    $params->{'magic'}  =~ /^on[ _]hold$/
 197             || $params->{'status'} =~ /^on[ _]hold$/ ) {
 198
 199     push @where, FS::cust_pkg->on_hold_sql();
 200
 201
 202   } elsif (    $params->{'magic'}  eq 'suspended'
 203             || $params->{'status'} eq 'suspended'  ) {
 204
 205     push @where, FS::cust_pkg->suspended_sql();
 206
 207   } elsif (    $params->{'magic'}  =~ /^cancell?ed$/
 208             || $params->{'status'} =~ /^cancell?ed$/ ) {
 209
 210     push @where, FS::cust_pkg->cancelled_sql();
 211
 212   }
 213
 214   ### special case: "magic" is used in detail links from browse/part_pkg,
 215   # where "cancelled" has the restriction "and not replaced with a package
 216   # of the same pkgpart".  Be consistent with that.
 217   ###
 218
 219   if ( $params->{'magic'} =~ /^cancell?ed$/ ) {
 220     my $new_pkgpart = "SELECT pkgpart FROM cust_pkg AS cust_pkg_next ".
 221                       "WHERE cust_pkg_next.change_pkgnum = cust_pkg.pkgnum";
 222     # ...may not exist, if this was just canceled and not changed; in that
 223     # case give it a "new pkgpart" that never equals the old pkgpart
 224     push @where, "COALESCE(($new_pkgpart), 0) != cust_pkg.pkgpart";
 225   }
 226
 227   ###
 228   # parse package class
 229   ###
 230
 231   if ( exists($params->{'classnum'}) ) {
 232
 233     my @classnum = ();
 234     if ( ref($params->{'classnum'}) ) {
 235
 236       if ( ref($params->{'classnum'}) eq 'HASH' ) {
 237         @classnum = grep $params->{'classnum'}{$_}, keys %{ $params->{'classnum'} };
 238       } elsif ( ref($params->{'classnum'}) eq 'ARRAY' ) {
 239         @classnum = @{ $params->{'classnum'} };
 240       } else {
 241         die 'unhandled classnum ref '. $params->{'classnum'};
 242       }
 243
 244
 245     } elsif ( $params->{'classnum'} =~ /^(\d*)$/ && $1 ne '0' ) {
 246       @classnum = ( $1 );
 247     }
 248
 249     if ( @classnum ) {
 250
 251       my @c_where = ();
 252       my @nums = grep $_, @classnum;
 253       push @c_where, 'part_pkg.classnum IN ('. join(',',@nums). ')' if @nums;
 254       my $null = scalar( grep { $_ eq '' } @classnum );
 255       push @c_where, 'part_pkg.classnum IS NULL' if $null;
 256
 257       if ( scalar(@c_where) == 1 ) {
 258         push @where, @c_where;
 259       } elsif ( @c_where ) {
 260         push @where, ' ( '. join(' OR ', @c_where). ' ) ';
 261       }
 262
 263     }
 264
 265
 266   }
 267
 268   ###
 269   # parse package report options
 270   ###
 271
 272   my @report_option = ();
 273   if ( exists($params->{'report_option'}) ) {
 274     if ( ref($params->{'report_option'}) eq 'ARRAY' ) {
 275       @report_option = @{ $params->{'report_option'} };
 276     } elsif ( $params->{'report_option'} =~ /^([,\d]*)$/ ) {
 277       @report_option = split(',', $1);
 278     }
 279
 280   }
 281
 282   if (@report_option) {
 283     # this will result in the empty set for the dangling comma case as it should
 284     push @where,
 285       map{ "0 < ( SELECT count(*) FROM part_pkg_option
 286                     WHERE part_pkg_option.pkgpart = part_pkg.pkgpart
 287                     AND optionname = 'report_option_$_'
 288                     AND optionvalue = '1' )"
 289          } @report_option;
 290   }
 291
 292   foreach my $any ( grep /^report_option_any/, keys %$params ) {
 293
 294     my @report_option_any = ();
 295     if ( ref($params->{$any}) eq 'ARRAY' ) {
 296       @report_option_any = @{ $params->{$any} };
 297     } elsif ( $params->{$any} =~ /^([,\d]*)$/ ) {
 298       @report_option_any = split(',', $1);
 299     }
 300
 301     if (@report_option_any) {
 302       # this will result in the empty set for the dangling comma case as it should
 303       push @where, ' ( '. join(' OR ',
 304         map{ "0 < ( SELECT count(*) FROM part_pkg_option
 305                       WHERE part_pkg_option.pkgpart = part_pkg.pkgpart
 306                       AND optionname = 'report_option_$_'
 307                       AND optionvalue = '1' )"
 308            } @report_option_any
 309       ). ' ) ';
 310     }
 311
 312   }
 313
 314   ###
 315   # parse custom
 316   ###
 317
 318   push @where,  "part_pkg.custom = 'Y'" if $params->{custom};
 319
 320   ###
 321   # parse fcc_line
 322   ###
 323
 324   push @where,  "(part_pkg.fcc_ds0s > 0 OR pkg_class.fcc_ds0s > 0)"
 325                                                         if $params->{fcc_line};
 326
 327   ###
 328   # parse censustract
 329   ###
 330
 331   if ( exists($params->{'censustract'}) ) {
 332     $params->{'censustract'} =~ /^([.\d]*)$/;
 333     my $censustract = "cust_location.censustract = '$1'";
 334     $censustract .= ' OR cust_location.censustract is NULL' unless $1;
 335     push @where,  "( $censustract )";
 336   }
 337
 338   ###
 339   # parse censustract2
 340   ###
 341   if ( exists($params->{'censustract2'})
 342        && $params->{'censustract2'} =~ /^(\d*)$/
 343      )
 344   {
 345     if ($1) {
 346       push @where, "cust_location.censustract LIKE '$1%'";
 347     } else {
 348       push @where,
 349         "( cust_location.censustract = '' OR cust_location.censustract IS NULL )";
 350     }
 351   }
 352
 353   ###
 354   # parse country/state
 355   ###
 356   for (qw(state country)) { # parsing rules are the same for these
 357   if ( exists($params->{$_})
 358     && uc($params->{$_}) =~ /^([A-Z]{2})$/ )
 359     {
 360       # XXX post-2.3 only--before that, state/country may be in cust_main
 361       push @where, "cust_location.$_ = '$1'";
 362     }
 363   }
 364
 365   ###
 366   # location_* flags
 367   ###
 368   if ( $params->{location_cust} xor $params->{location_nocust} ) {
 369     my $op = $params->{location_cust} ? '=' : '!=';
 370     push @where, "cust_location.locationnum $op cust_main.ship_locationnum";
 371   }
 372   if ( $params->{location_census} xor $params->{location_nocensus} ) {
 373     my $op = $params->{location_census} ? "IS NOT NULL" : "IS NULL";
 374     push @where, "cust_location.censustract $op";
 375   }
 376   if ( $params->{location_geocode} xor $params->{location_nogeocode} ) {
 377     my $op = $params->{location_geocode} ? "IS NOT NULL" : "IS NULL";
 378     push @where, "cust_location.geocode $op";
 379   }
 380
 381   ###
 382   # parse part_pkg
 383   ###
 384
 385   if ( ref($params->{'pkgpart'}) ) {
 386
 387     my @pkgpart = ();
 388     if ( ref($params->{'pkgpart'}) eq 'HASH' ) {
 389       @pkgpart = grep $params->{'pkgpart'}{$_}, keys %{ $params->{'pkgpart'} };
 390     } elsif ( ref($params->{'pkgpart'}) eq 'ARRAY' ) {
 391       @pkgpart = @{ $params->{'pkgpart'} };
 392     } else {
 393       die 'unhandled pkgpart ref '. $params->{'pkgpart'};
 394     }
 395
 396     @pkgpart = grep /^(\d+)$/, @pkgpart;
 397
 398     push @where, 'pkgpart IN ('. join(',', @pkgpart). ')' if scalar(@pkgpart);
 399
 400   } elsif ( $params->{'pkgpart'} =~ /^(\d+)$/ ) {
 401     push @where, "pkgpart = $1";
 402   }
 403
 404   ###
 405   # parse dates
 406   ###
 407
 408   my $orderby = '';
 409
 410   #false laziness w/report_cust_pkg.html
 411   my %disable = (
 412     'all'             => {},
 413     'one-time charge' => { 'last_bill'=>1, 'bill'=>1, 'adjourn'=>1, 'susp'=>1, 'expire'=>1, 'cancel'=>1, },
 414     'active'          => { 'susp'=>1, 'cancel'=>1 },
 415     'suspended'       => { 'cancel' => 1 },
 416     'cancelled'       => {},
 417     ''                => {},
 418   );
 419
 420   if( exists($params->{'active'} ) ) {
 421     # This overrides all the other date-related fields, and includes packages
 422     # that were active at some time during the interval.  It excludes:
 423     # - packages that were set up after the end of the interval
 424     # - packages that were canceled before the start of the interval
 425     # - packages that were suspended before the start of the interval
 426     #   and are still suspended now
 427     my($beginning, $ending) = @{$params->{'active'}};
 428     push @where,
 429       "cust_pkg.setup IS NOT NULL",
 430       "cust_pkg.setup <= $ending",
 431       "(cust_pkg.cancel IS NULL OR cust_pkg.cancel >= $beginning )",
 432       "(cust_pkg.susp   IS NULL OR cust_pkg.susp   >= $beginning )",
 433       "NOT (".FS::cust_pkg->onetime_sql . ")";
 434   }
 435   else {
 436     foreach my $field (qw( setup last_bill bill adjourn susp expire contract_end change_date cancel )) {
 437
 438       next unless exists($params->{$field});
 439
 440       my($beginning, $ending) = @{$params->{$field}};
 441
 442       next if $beginning == 0 && $ending == 4294967295;
 443
 444       push @where,
 445         "cust_pkg.$field IS NOT NULL",
 446         "cust_pkg.$field >= $beginning",
 447         "cust_pkg.$field <= $ending";
 448
 449       $orderby ||= "ORDER BY cust_pkg.$field";
 450
 451     }
 452   }
 453
 454   $orderby ||= 'ORDER BY bill';
 455
 456   ###
 457   # parse magic, legacy, etc.
 458   ###
 459
 460   if ( $params->{'magic'} &&
 461        $params->{'magic'} =~ /^(active|inactive|suspended|cancell?ed)$/
 462   ) {
 463
 464     $orderby = 'ORDER BY pkgnum';
 465
 466     if ( $params->{'pkgpart'} =~ /^(\d+)$/ ) {
 467       push @where, "pkgpart = $1";
 468     }
 469
 470   } elsif ( $params->{'query'} eq 'pkgnum' ) {
 471
 472     $orderby = 'ORDER BY pkgnum';
 473
 474   } elsif ( $params->{'query'} eq 'APKG_pkgnum' ) {
 475
 476     $orderby = 'ORDER BY pkgnum';
 477
 478     push @where, '0 < (
 479       SELECT count(*) FROM pkg_svc
 480        WHERE pkg_svc.pkgpart =  cust_pkg.pkgpart
 481          AND pkg_svc.quantity > ( SELECT count(*) FROM cust_svc
 482                                    WHERE cust_svc.pkgnum  = cust_pkg.pkgnum
 483                                      AND cust_svc.svcpart = pkg_svc.svcpart
 484                                 )
 485     )';
 486
 487   }
 488
 489   ##
 490   # setup queries, links, subs, etc. for the search
 491   ##
 492
 493   # here is the agent virtualization
 494   if ($params->{CurrentUser}) {
 495     my $access_user =
 496       qsearchs('access_user', { username => $params->{CurrentUser} });
 497
 498     if ($access_user) {
 499       push @where, $access_user->agentnums_sql('table'=>'cust_main');
 500     } else {
 501       push @where, "1=0";
 502     }
 503   } else {
 504     push @where, $FS::CurrentUser::CurrentUser->agentnums_sql('table'=>'cust_main');
 505   }
 506
 507   my $extra_sql = scalar(@where) ? ' WHERE '. join(' AND ', @where) : '';
 508
 509   my $addl_from = 'LEFT JOIN part_pkg  USING ( pkgpart  ) '.
 510                   'LEFT JOIN pkg_class ON ( part_pkg.classnum = pkg_class.classnum ) '.
 511                   'LEFT JOIN cust_location USING ( locationnum ) '.
 512                   FS::UI::Web::join_cust_main('cust_pkg', 'cust_pkg');
 513
 514   my $select;
 515   my $count_query;
 516   if ( $params->{'select_zip5'} ) {
 517     my $zip = 'cust_location.zip';
 518
 519     $select = "DISTINCT substr($zip,1,5) as zip";
 520     $orderby = "ORDER BY substr($zip,1,5)";
 521     $count_query = "SELECT COUNT( DISTINCT substr($zip,1,5) )";
 522   } else {
 523     $select = join(', ',
 524                          'cust_pkg.*',
 525                          ( map "part_pkg.$_", qw( pkg freq ) ),
 526                          'pkg_class.classname',
 527                          'cust_main.custnum AS cust_main_custnum',
 528                          FS::UI::Web::cust_sql_fields(
 529                            $params->{'cust_fields'}
 530                          ),
 531                   );
 532     $count_query = 'SELECT COUNT(*)';
 533   }
 534
 535   $count_query .= " FROM cust_pkg $addl_from $extra_sql";
 536
 537   my $sql_query = {
 538     'table'       => 'cust_pkg',
 539     'hashref'     => {},
 540     'select'      => $select,
 541     'extra_sql'   => $extra_sql,
 542     'order_by'    => $orderby,
 543     'addl_from'   => $addl_from,
 544     'count_query' => $count_query,
 545   };
 546
 547 }
 548
 549 1;
 550