1 package FS::cust_pkg::Search;
13 Returns a qsearch hash expression to search for parameters specified in HASHREF.
22 on hold, active, inactive (or one-time charge), suspended, canceled (or cancelled)
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).
32 boolean selects custom packages
38 pkgpart or arrayref or hashref of pkgparts
42 arrayref of beginning and ending epoch date
46 arrayref of beginning and ending epoch date
50 arrayref of beginning and ending epoch date
54 arrayref of beginning and ending epoch date
58 arrayref of beginning and ending epoch date
62 arrayref of beginning and ending epoch date
66 arrayref of beginning and ending epoch date
74 a value suited to passing to FS::UI::Web::cust_header
78 specifies the user for agent virtualization
82 boolean; if true, returns only packages with more than 0 FCC phone lines.
86 Limit to packages with a service location in the specified state and country.
87 For FCC 477 reporting, mostly.
91 Limit to packages whose service locations are the same as the customer's
92 default service location.
96 Limit to packages whose service locations are not the customer's default
101 Limit to packages whose service locations have census tracts.
103 =item location_nocensus
105 Limit to packages whose service locations do not have a census tract.
107 =item location_geocode
109 Limit to packages whose locations have geocodes.
111 =item location_geocode
113 Limit to packages whose locations do not have geocodes.
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.
121 =item 477part, 477rownum, date
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.
135 my ($class, $params) = @_;
142 if ( $params->{'agentnum'} =~ /^(\d+)$/ and $1 ) {
144 "cust_main.agentnum = $1";
151 if ( $params->{'cust_status'} =~ /^([a-z]+)$/ ) {
152 push @where, FS::cust_main->cust_status_sql . " = '$1' ";
156 # parse customer sales person
159 if ( $params->{'cust_main_salesnum'} =~ /^(\d+)$/ ) {
160 push @where, ($1 > 0) ? "cust_main.salesnum = $1"
161 : 'cust_main.salesnum IS NULL';
169 if ( $params->{'salesnum'} =~ /^(\d+)$/ ) {
170 push @where, ($1 > 0) ? "cust_pkg.salesnum = $1"
171 : 'cust_pkg.salesnum IS NULL';
178 if ( $params->{'custnum'} =~ /^(\d+)$/ and $1 ) {
180 "cust_pkg.custnum = $1";
187 if ( $params->{'pkgbatch'} =~ /^([\w\/\-\:\.]+)$/ and $1 ) {
189 "cust_pkg.pkgbatch = '$1'";
196 if ( $params->{'magic'} eq 'active'
197 || $params->{'status'} eq 'active' ) {
199 push @where, FS::cust_pkg->active_sql();
201 } elsif ( $params->{'magic'} =~ /^not[ _]yet[ _]billed$/
202 || $params->{'status'} =~ /^not[ _]yet[ _]billed$/ ) {
204 push @where, FS::cust_pkg->not_yet_billed_sql();
206 } elsif ( $params->{'magic'} =~ /^(one-time charge|inactive)/
207 || $params->{'status'} =~ /^(one-time charge|inactive)/ ) {
209 push @where, FS::cust_pkg->inactive_sql();
211 } elsif ( $params->{'magic'} =~ /^on[ _]hold$/
212 || $params->{'status'} =~ /^on[ _]hold$/ ) {
214 push @where, FS::cust_pkg->on_hold_sql();
217 } elsif ( $params->{'magic'} eq 'suspended'
218 || $params->{'status'} eq 'suspended' ) {
220 push @where, FS::cust_pkg->suspended_sql();
222 } elsif ( $params->{'magic'} =~ /^cancell?ed$/
223 || $params->{'status'} =~ /^cancell?ed$/ ) {
225 push @where, FS::cust_pkg->cancelled_sql();
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.
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";
243 # parse package class
246 if ( exists($params->{'classnum'}) ) {
249 if ( ref($params->{'classnum'}) ) {
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'} };
256 die 'unhandled classnum ref '. $params->{'classnum'};
260 } elsif ( $params->{'classnum'} =~ /^(\d*)$/ && $1 ne '0' ) {
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;
272 if ( scalar(@c_where) == 1 ) {
273 push @where, @c_where;
274 } elsif ( @c_where ) {
275 push @where, ' ( '. join(' OR ', @c_where). ' ) ';
284 # parse package report options
287 my @report_option = ();
288 if ( exists($params->{'report_option'}) ) {
289 if ( ref($params->{'report_option'}) eq 'ARRAY' ) {
290 @report_option = @{ $params->{'report_option'} };
291 } elsif ( $params->{'report_option'} =~ /^([,\d]*)$/ ) {
292 @report_option = split(',', $1);
297 if (@report_option) {
298 # this will result in the empty set for the dangling comma case as it should
300 map{ "0 < ( SELECT count(*) FROM part_pkg_option
301 WHERE part_pkg_option.pkgpart = part_pkg.pkgpart
302 AND optionname = 'report_option_$_'
303 AND optionvalue = '1' )"
307 foreach my $any ( grep /^report_option_any/, keys %$params ) {
309 my @report_option_any = ();
310 if ( ref($params->{$any}) eq 'ARRAY' ) {
311 @report_option_any = @{ $params->{$any} };
312 } elsif ( $params->{$any} =~ /^([,\d]*)$/ ) {
313 @report_option_any = split(',', $1);
316 if (@report_option_any) {
317 # this will result in the empty set for the dangling comma case as it should
318 push @where, ' ( '. join(' OR ',
319 map{ "0 < ( SELECT count(*) FROM part_pkg_option
320 WHERE part_pkg_option.pkgpart = part_pkg.pkgpart
321 AND optionname = 'report_option_$_'
322 AND optionvalue = '1' )"
333 push @where, "part_pkg.custom = 'Y'" if $params->{custom};
339 push @where, "(part_pkg.fcc_ds0s > 0 OR pkg_class.fcc_ds0s > 0)"
340 if $params->{fcc_line};
346 if ( exists($params->{'censustract'}) ) {
347 $params->{'censustract'} =~ /^([.\d]*)$/;
348 my $censustract = "cust_location.censustract = '$1'";
349 $censustract .= ' OR cust_location.censustract is NULL' unless $1;
350 push @where, "( $censustract )";
356 if ( exists($params->{'censustract2'})
357 && $params->{'censustract2'} =~ /^(\d*)$/
361 push @where, "cust_location.censustract LIKE '$1%'";
364 "( cust_location.censustract = '' OR cust_location.censustract IS NULL )";
369 # parse country/state/zip
371 for (qw(state country)) { # parsing rules are the same for these
372 if ( exists($params->{$_})
373 && uc($params->{$_}) =~ /^([A-Z]{2})$/ )
375 # XXX post-2.3 only--before that, state/country may be in cust_main
376 push @where, "cust_location.$_ = '$1'";
379 if ( exists($params->{zip}) ) {
380 push @where, "cust_location.zip = " . dbh->quote($params->{zip});
386 if ( $params->{location_cust} xor $params->{location_nocust} ) {
387 my $op = $params->{location_cust} ? '=' : '!=';
388 push @where, "cust_location.locationnum $op cust_main.ship_locationnum";
390 if ( $params->{location_census} xor $params->{location_nocensus} ) {
391 my $op = $params->{location_census} ? "IS NOT NULL" : "IS NULL";
392 push @where, "cust_location.censustract $op";
394 if ( $params->{location_geocode} xor $params->{location_nogeocode} ) {
395 my $op = $params->{location_geocode} ? "IS NOT NULL" : "IS NULL";
396 push @where, "cust_location.geocode $op";
403 if ( ref($params->{'pkgpart'}) ) {
406 if ( ref($params->{'pkgpart'}) eq 'HASH' ) {
407 @pkgpart = grep $params->{'pkgpart'}{$_}, keys %{ $params->{'pkgpart'} };
408 } elsif ( ref($params->{'pkgpart'}) eq 'ARRAY' ) {
409 @pkgpart = @{ $params->{'pkgpart'} };
411 die 'unhandled pkgpart ref '. $params->{'pkgpart'};
414 @pkgpart = grep /^(\d+)$/, @pkgpart;
416 push @where, 'pkgpart IN ('. join(',', @pkgpart). ')' if scalar(@pkgpart);
418 } elsif ( $params->{'pkgpart'} =~ /^(\d+)$/ ) {
419 push @where, "pkgpart = $1";
428 #false laziness w/report_cust_pkg.html
431 'one-time charge' => { 'last_bill'=>1, 'bill'=>1, 'adjourn'=>1, 'susp'=>1, 'expire'=>1, 'cancel'=>1, },
432 'active' => { 'susp'=>1, 'cancel'=>1 },
433 'suspended' => { 'cancel' => 1 },
438 if( exists($params->{'active'} ) ) {
439 # This overrides all the other date-related fields, and includes packages
440 # that were active at some time during the interval. It excludes:
441 # - packages that were set up after the end of the interval
442 # - packages that were canceled before the start of the interval
443 # - packages that were suspended before the start of the interval
444 # and are still suspended now
445 my($beginning, $ending) = @{$params->{'active'}};
447 "cust_pkg.setup IS NOT NULL",
448 "cust_pkg.setup <= $ending",
449 "(cust_pkg.cancel IS NULL OR cust_pkg.cancel >= $beginning )",
450 "(cust_pkg.susp IS NULL OR cust_pkg.susp >= $beginning )",
451 "NOT (".FS::cust_pkg->onetime_sql . ")";
454 my $exclude_change_from = 0;
455 my $exclude_change_to = 0;
457 foreach my $field (qw( setup last_bill bill adjourn susp expire contract_end change_date cancel )) {
459 next unless exists($params->{$field});
461 my($beginning, $ending) = @{$params->{$field}};
463 next if $beginning == 0 && $ending == 4294967295;
466 "cust_pkg.$field IS NOT NULL",
467 "cust_pkg.$field >= $beginning",
468 "cust_pkg.$field <= $ending";
470 $orderby ||= "ORDER BY cust_pkg.$field";
472 if ( $field eq 'setup' ) {
473 $exclude_change_from = 1;
474 } elsif ( $field eq 'cancel' ) {
475 $exclude_change_to = 1;
476 } elsif ( $field eq 'change_date' ) {
477 # if we are given setup and change_date ranges, and the setup date
478 # falls in _both_ ranges, then include the package whether it was
480 $exclude_change_from = 0;
484 if ($exclude_change_from) {
485 push @where, "change_pkgnum IS NULL";
487 if ($exclude_change_to) {
488 # a join might be more efficient here
489 push @where, "NOT EXISTS(
490 SELECT 1 FROM cust_pkg AS changed_to_pkg
491 WHERE cust_pkg.pkgnum = changed_to_pkg.change_pkgnum
496 $orderby ||= 'ORDER BY bill';
499 # parse magic, legacy, etc.
502 if ( $params->{'magic'} &&
503 $params->{'magic'} =~ /^(active|inactive|suspended|cancell?ed)$/
506 $orderby = 'ORDER BY pkgnum';
508 if ( $params->{'pkgpart'} =~ /^(\d+)$/ ) {
509 push @where, "pkgpart = $1";
512 } elsif ( $params->{'query'} eq 'pkgnum' ) {
514 $orderby = 'ORDER BY pkgnum';
516 } elsif ( $params->{'query'} eq 'APKG_pkgnum' ) {
518 $orderby = 'ORDER BY pkgnum';
521 SELECT count(*) FROM pkg_svc
522 WHERE pkg_svc.pkgpart = cust_pkg.pkgpart
523 AND pkg_svc.quantity > ( SELECT count(*) FROM cust_svc
524 WHERE cust_svc.pkgnum = cust_pkg.pkgnum
525 AND cust_svc.svcpart = pkg_svc.svcpart
532 # parse the extremely weird 'towernum' param
535 if ($params->{towernum}) {
536 my $towernum = $params->{towernum};
537 $towernum = [ $towernum ] if !ref($towernum);
538 my $in = join(',', grep /^\d+$/, @$towernum);
540 # inefficient, but this is an obscure feature
541 eval "use FS::Report::Table";
542 FS::Report::Table->_init_tower_pkg_cache; # probably does nothing
543 push @where, "EXISTS(
544 SELECT 1 FROM tower_pkg_cache
545 WHERE tower_pkg_cache.pkgnum = cust_pkg.pkgnum
546 AND tower_pkg_cache.towernum IN ($in)
552 # parse the 477 report drill-down options
555 if ($params->{'477part'} =~ /^([a-z]+)$/) {
557 my ($date, $rownum, $agentnum);
558 if ($params->{'date'} =~ /^(\d+)$/) {
561 if ($params->{'477rownum'} =~ /^(\d+)$/) {
564 if ($params->{'agentnum'} =~ /^(\d+)$/) {
567 if ($date and defined($rownum)) {
568 my $report = FS::Report::FCC_477->report($section,
570 'agentnum' => $agentnum,
573 my $row = $report->[$rownum]
574 or die "row $rownum is past the end of the report";
575 my $pkgnums = $row->[-1] || '0';
576 # '0' so that if there are no pkgnums (empty string) it will create
577 # a valid query that returns nothing
578 warn "PKGNUMS:\n$pkgnums\n\n"; # XXX debug
580 # and this overrides everything
581 @where = ( "cust_pkg.pkgnum IN($pkgnums)" );
582 } # else we're missing some params, ignore the whole business
586 # setup queries, links, subs, etc. for the search
589 # here is the agent virtualization
590 if ($params->{CurrentUser}) {
592 qsearchs('access_user', { username => $params->{CurrentUser} });
595 push @where, $access_user->agentnums_sql('table'=>'cust_main');
600 push @where, $FS::CurrentUser::CurrentUser->agentnums_sql('table'=>'cust_main');
603 my $extra_sql = scalar(@where) ? ' WHERE '. join(' AND ', @where) : '';
605 my $addl_from = 'LEFT JOIN part_pkg USING ( pkgpart ) '.
606 'LEFT JOIN pkg_class ON ( part_pkg.classnum = pkg_class.classnum ) '.
607 'LEFT JOIN cust_location USING ( locationnum ) '.
608 FS::UI::Web::join_cust_main('cust_pkg', 'cust_pkg');
612 if ( $params->{'select_zip5'} ) {
613 my $zip = 'cust_location.zip';
615 $select = "DISTINCT substr($zip,1,5) as zip";
616 $orderby = "ORDER BY substr($zip,1,5)";
617 $count_query = "SELECT COUNT( DISTINCT substr($zip,1,5) )";
621 ( map "part_pkg.$_", qw( pkg freq ) ),
622 'pkg_class.classname',
623 'cust_main.custnum AS cust_main_custnum',
624 FS::UI::Web::cust_sql_fields(
625 $params->{'cust_fields'}
628 $count_query = 'SELECT COUNT(*)';
631 $count_query .= " FROM cust_pkg $addl_from $extra_sql";
634 'table' => 'cust_pkg',
637 'extra_sql' => $extra_sql,
638 'order_by' => $orderby,
639 'addl_from' => $addl_from,
640 'count_query' => $count_query,