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 (customer) refnum (advertising source)
287 if ( exists($params->{'refnum'}) ) {
289 if (ref $params->{'refnum'}) {
290 @refnum = @{ $params->{'refnum'} };
292 @refnum = ( $params->{'refnum'} );
294 my $in = join(',', grep /^\d+$/, @refnum);
295 push @where, "cust_main.refnum IN($in)" if length $in;
299 # parse package report options
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);
312 if (@report_option) {
313 # this will result in the empty set for the dangling comma case as it should
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' )"
322 foreach my $any ( grep /^report_option_any/, keys %$params ) {
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);
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' )"
348 push @where, "part_pkg.custom = 'Y'" if $params->{custom};
354 push @where, "(part_pkg.fcc_ds0s > 0 OR pkg_class.fcc_ds0s > 0)"
355 if $params->{fcc_line};
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 )";
371 if ( exists($params->{'censustract2'})
372 && $params->{'censustract2'} =~ /^(\d*)$/
376 push @where, "cust_location.censustract LIKE '$1%'";
379 "( cust_location.censustract = '' OR cust_location.censustract IS NULL )";
384 # parse country/state/zip
386 for (qw(state country)) { # parsing rules are the same for these
387 if ( exists($params->{$_})
388 && uc($params->{$_}) =~ /^([A-Z]{2})$/ )
390 # XXX post-2.3 only--before that, state/country may be in cust_main
391 push @where, "cust_location.$_ = '$1'";
394 if ( exists($params->{zip}) ) {
395 push @where, "cust_location.zip = " . dbh->quote($params->{zip});
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";
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";
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";
418 if ( ref($params->{'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'} };
426 die 'unhandled pkgpart ref '. $params->{'pkgpart'};
429 @pkgpart = grep /^(\d+)$/, @pkgpart;
431 push @where, 'pkgpart IN ('. join(',', @pkgpart). ')' if scalar(@pkgpart);
433 } elsif ( $params->{'pkgpart'} =~ /^(\d+)$/ ) {
434 push @where, "pkgpart = $1";
443 #false laziness w/report_cust_pkg.html
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 },
453 if ( exists($params->{'active'} ) ) {
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'}};
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 . ")";
471 my $exclude_change_from = 0;
472 my $exclude_change_to = 0;
474 foreach my $field (qw( setup last_bill bill adjourn susp expire contract_end change_date cancel )) {
476 if ( $params->{$field.'_null'} ) {
478 push @where, "cust_pkg.$field IS NULL";
479 # this should surely be obsoleted by now: OR cust_pkg.$field == 0 )
483 next unless exists($params->{$field});
485 my($beginning, $ending) = @{$params->{$field}};
487 next if $beginning == 0 && $ending == 4294967295;
490 "cust_pkg.$field IS NOT NULL",
491 "cust_pkg.$field >= $beginning",
492 "cust_pkg.$field <= $ending";
494 $orderby ||= "ORDER BY cust_pkg.$field";
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
504 $exclude_change_from = 0;
511 if ($exclude_change_from) {
512 push @where, "cust_pkg.change_pkgnum IS NULL";
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
524 $orderby ||= 'ORDER BY bill';
527 # parse magic, legacy, etc.
530 if ( $params->{'magic'} &&
531 $params->{'magic'} =~ /^(active|inactive|suspended|cancell?ed)$/
534 $orderby = 'ORDER BY pkgnum';
536 if ( $params->{'pkgpart'} =~ /^(\d+)$/ ) {
537 push @where, "pkgpart = $1";
540 } elsif ( $params->{'query'} eq 'pkgnum' ) {
542 $orderby = 'ORDER BY pkgnum';
544 } elsif ( $params->{'query'} eq 'APKG_pkgnum' ) {
546 $orderby = 'ORDER BY pkgnum';
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
560 # parse the extremely weird 'towernum' param
563 if ($params->{towernum}) {
564 my $towernum = $params->{towernum};
565 $towernum = [ $towernum ] if !ref($towernum);
566 my $in = join(',', grep /^\d+$/, @$towernum);
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)
580 # parse the 477 report drill-down options
583 if ($params->{'477part'} =~ /^([a-z]+)$/) {
585 my ($date, $rownum, $agentnum);
586 if ($params->{'date'} =~ /^(\d+)$/) {
589 if ($params->{'477rownum'} =~ /^(\d+)$/) {
592 if ($params->{'agentnum'} =~ /^(\d+)$/) {
595 if ($date and defined($rownum)) {
596 my $report = FS::Report::FCC_477->report($section,
598 'agentnum' => $agentnum,
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
607 # and this overrides everything
608 @where = ( "cust_pkg.pkgnum IN($pkgnums)" );
609 } # else we're missing some params, ignore the whole business
613 # setup queries, links, subs, etc. for the search
616 # here is the agent virtualization
617 if ($params->{CurrentUser}) {
619 qsearchs('access_user', { username => $params->{CurrentUser} });
622 push @where, $access_user->agentnums_sql('table'=>'cust_main');
627 push @where, $FS::CurrentUser::CurrentUser->agentnums_sql('table'=>'cust_main');
630 my $extra_sql = scalar(@where) ? ' WHERE '. join(' AND ', @where) : '';
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');
639 if ( $params->{'select_zip5'} ) {
640 my $zip = 'cust_location.zip';
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) )";
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'}
655 $count_query = 'SELECT COUNT(*)';
658 $count_query .= " FROM cust_pkg $addl_from $extra_sql";
661 'table' => 'cust_pkg',
664 'extra_sql' => $extra_sql,
665 'order_by' => $orderby,
666 'addl_from' => $addl_from,
667 'count_query' => $count_query,