1 package FS::cust_pkg::Search;
13 Returns a qsearch hash expression to search for parameters specified in HASHREF.
22 active, inactive, suspended, cancel (or cancelled)
26 active, inactive, suspended, one-time charge, inactive, cancel (or cancelled)
30 boolean selects custom packages
36 pkgpart or arrayref or hashref of pkgparts
40 arrayref of beginning and ending epoch date
44 arrayref of beginning and ending epoch date
48 arrayref of beginning and ending epoch date
52 arrayref of beginning and ending epoch date
56 arrayref of beginning and ending epoch date
60 arrayref of beginning and ending epoch date
64 arrayref of beginning and ending epoch date
72 a value suited to passing to FS::UI::Web::cust_header
76 specifies the user for agent virtualization
80 boolean; if true, returns only packages with more than 0 FCC phone lines.
84 Limit to packages with a service location in the specified state and country.
85 For FCC 477 reporting, mostly.
89 Limit to packages whose service locations are the same as the customer's
90 default service location.
94 Limit to packages whose service locations are not the customer's default
99 Limit to packages whose service locations have census tracts.
101 =item location_nocensus
103 Limit to packages whose service locations do not have a census tract.
105 =item location_geocode
107 Limit to packages whose locations have geocodes.
109 =item location_geocode
111 Limit to packages whose locations do not have geocodes.
118 my ($class, $params) = @_;
125 if ( $params->{'agentnum'} =~ /^(\d+)$/ and $1 ) {
127 "cust_main.agentnum = $1";
134 if ( $params->{'cust_status'} =~ /^([a-z]+)$/ ) {
135 push @where, FS::cust_main->cust_status_sql . " = '$1' ";
139 # parse customer sales person
142 if ( $params->{'cust_main_salesnum'} =~ /^(\d+)$/ ) {
143 push @where, ($1 > 0) ? "cust_main.salesnum = $1"
144 : 'cust_main.salesnum IS NULL';
152 if ( $params->{'salesnum'} =~ /^(\d+)$/ ) {
153 push @where, ($1 > 0) ? "cust_pkg.salesnum = $1"
154 : 'cust_pkg.salesnum IS NULL';
161 if ( $params->{'custnum'} =~ /^(\d+)$/ and $1 ) {
163 "cust_pkg.custnum = $1";
170 if ( $params->{'pkgbatch'} =~ /^([\w\/\-\:\.]+)$/ and $1 ) {
172 "cust_pkg.pkgbatch = '$1'";
179 if ( $params->{'magic'} eq 'active'
180 || $params->{'status'} eq 'active' ) {
182 push @where, FS::cust_pkg->active_sql();
184 } elsif ( $params->{'magic'} =~ /^not[ _]yet[ _]billed$/
185 || $params->{'status'} =~ /^not[ _]yet[ _]billed$/ ) {
187 push @where, FS::cust_pkg->not_yet_billed_sql();
189 } elsif ( $params->{'magic'} =~ /^(one-time charge|inactive)/
190 || $params->{'status'} =~ /^(one-time charge|inactive)/ ) {
192 push @where, FS::cust_pkg->inactive_sql();
194 } elsif ( $params->{'magic'} eq 'suspended'
195 || $params->{'status'} eq 'suspended' ) {
197 push @where, FS::cust_pkg->suspended_sql();
199 } elsif ( $params->{'magic'} =~ /^cancell?ed$/
200 || $params->{'status'} =~ /^cancell?ed$/ ) {
202 push @where, FS::cust_pkg->cancelled_sql();
207 # parse package class
210 if ( exists($params->{'classnum'}) ) {
213 if ( ref($params->{'classnum'}) ) {
215 if ( ref($params->{'classnum'}) eq 'HASH' ) {
216 @classnum = grep $params->{'classnum'}{$_}, keys %{ $params->{'classnum'} };
217 } elsif ( ref($params->{'classnum'}) eq 'ARRAY' ) {
218 @classnum = @{ $params->{'classnum'} };
220 die 'unhandled classnum ref '. $params->{'classnum'};
224 } elsif ( $params->{'classnum'} =~ /^(\d*)$/ && $1 ne '0' ) {
231 my @nums = grep $_, @classnum;
232 push @c_where, 'part_pkg.classnum IN ('. join(',',@nums). ')' if @nums;
233 my $null = scalar( grep { $_ eq '' } @classnum );
234 push @c_where, 'part_pkg.classnum IS NULL' if $null;
236 if ( scalar(@c_where) == 1 ) {
237 push @where, @c_where;
238 } elsif ( @c_where ) {
239 push @where, ' ( '. join(' OR ', @c_where). ' ) ';
248 # parse package report options
251 my @report_option = ();
252 if ( exists($params->{'report_option'}) ) {
253 if ( ref($params->{'report_option'}) eq 'ARRAY' ) {
254 @report_option = @{ $params->{'report_option'} };
255 } elsif ( $params->{'report_option'} =~ /^([,\d]*)$/ ) {
256 @report_option = split(',', $1);
261 if (@report_option) {
262 # this will result in the empty set for the dangling comma case as it should
264 map{ "0 < ( SELECT count(*) FROM part_pkg_option
265 WHERE part_pkg_option.pkgpart = part_pkg.pkgpart
266 AND optionname = 'report_option_$_'
267 AND optionvalue = '1' )"
271 foreach my $any ( grep /^report_option_any/, keys %$params ) {
273 my @report_option_any = ();
274 if ( ref($params->{$any}) eq 'ARRAY' ) {
275 @report_option_any = @{ $params->{$any} };
276 } elsif ( $params->{$any} =~ /^([,\d]*)$/ ) {
277 @report_option_any = split(',', $1);
280 if (@report_option_any) {
281 # this will result in the empty set for the dangling comma case as it should
282 push @where, ' ( '. join(' OR ',
283 map{ "0 < ( SELECT count(*) FROM part_pkg_option
284 WHERE part_pkg_option.pkgpart = part_pkg.pkgpart
285 AND optionname = 'report_option_$_'
286 AND optionvalue = '1' )"
297 push @where, "part_pkg.custom = 'Y'" if $params->{custom};
303 push @where, "(part_pkg.fcc_ds0s > 0 OR pkg_class.fcc_ds0s > 0)"
304 if $params->{fcc_line};
310 if ( exists($params->{'censustract'}) ) {
311 $params->{'censustract'} =~ /^([.\d]*)$/;
312 my $censustract = "cust_location.censustract = '$1'";
313 $censustract .= ' OR cust_location.censustract is NULL' unless $1;
314 push @where, "( $censustract )";
320 if ( exists($params->{'censustract2'})
321 && $params->{'censustract2'} =~ /^(\d*)$/
325 push @where, "cust_location.censustract LIKE '$1%'";
328 "( cust_location.censustract = '' OR cust_location.censustract IS NULL )";
333 # parse country/state
335 for (qw(state country)) { # parsing rules are the same for these
336 if ( exists($params->{$_})
337 && uc($params->{$_}) =~ /^([A-Z]{2})$/ )
339 # XXX post-2.3 only--before that, state/country may be in cust_main
340 push @where, "cust_location.$_ = '$1'";
347 if ( $params->{location_cust} xor $params->{location_nocust} ) {
348 my $op = $params->{location_cust} ? '=' : '!=';
349 push @where, "cust_location.locationnum $op cust_main.ship_locationnum";
351 if ( $params->{location_census} xor $params->{location_nocensus} ) {
352 my $op = $params->{location_census} ? "IS NOT NULL" : "IS NULL";
353 push @where, "cust_location.censustract $op";
355 if ( $params->{location_geocode} xor $params->{location_nogeocode} ) {
356 my $op = $params->{location_geocode} ? "IS NOT NULL" : "IS NULL";
357 push @where, "cust_location.geocode $op";
364 if ( ref($params->{'pkgpart'}) ) {
367 if ( ref($params->{'pkgpart'}) eq 'HASH' ) {
368 @pkgpart = grep $params->{'pkgpart'}{$_}, keys %{ $params->{'pkgpart'} };
369 } elsif ( ref($params->{'pkgpart'}) eq 'ARRAY' ) {
370 @pkgpart = @{ $params->{'pkgpart'} };
372 die 'unhandled pkgpart ref '. $params->{'pkgpart'};
375 @pkgpart = grep /^(\d+)$/, @pkgpart;
377 push @where, 'pkgpart IN ('. join(',', @pkgpart). ')' if scalar(@pkgpart);
379 } elsif ( $params->{'pkgpart'} =~ /^(\d+)$/ ) {
380 push @where, "pkgpart = $1";
389 #false laziness w/report_cust_pkg.html
392 'one-time charge' => { 'last_bill'=>1, 'bill'=>1, 'adjourn'=>1, 'susp'=>1, 'expire'=>1, 'cancel'=>1, },
393 'active' => { 'susp'=>1, 'cancel'=>1 },
394 'suspended' => { 'cancel' => 1 },
399 if( exists($params->{'active'} ) ) {
400 # This overrides all the other date-related fields
401 my($beginning, $ending) = @{$params->{'active'}};
403 "cust_pkg.setup IS NOT NULL",
404 "cust_pkg.setup <= $ending",
405 "(cust_pkg.cancel IS NULL OR cust_pkg.cancel >= $beginning )",
406 "NOT (".FS::cust_pkg->onetime_sql . ")";
409 foreach my $field (qw( setup last_bill bill adjourn susp expire contract_end change_date cancel )) {
411 next unless exists($params->{$field});
413 my($beginning, $ending) = @{$params->{$field}};
415 next if $beginning == 0 && $ending == 4294967295;
418 "cust_pkg.$field IS NOT NULL",
419 "cust_pkg.$field >= $beginning",
420 "cust_pkg.$field <= $ending";
422 $orderby ||= "ORDER BY cust_pkg.$field";
427 $orderby ||= 'ORDER BY bill';
430 # parse magic, legacy, etc.
433 if ( $params->{'magic'} &&
434 $params->{'magic'} =~ /^(active|inactive|suspended|cancell?ed)$/
437 $orderby = 'ORDER BY pkgnum';
439 if ( $params->{'pkgpart'} =~ /^(\d+)$/ ) {
440 push @where, "pkgpart = $1";
443 } elsif ( $params->{'query'} eq 'pkgnum' ) {
445 $orderby = 'ORDER BY pkgnum';
447 } elsif ( $params->{'query'} eq 'APKG_pkgnum' ) {
449 $orderby = 'ORDER BY pkgnum';
452 SELECT count(*) FROM pkg_svc
453 WHERE pkg_svc.pkgpart = cust_pkg.pkgpart
454 AND pkg_svc.quantity > ( SELECT count(*) FROM cust_svc
455 WHERE cust_svc.pkgnum = cust_pkg.pkgnum
456 AND cust_svc.svcpart = pkg_svc.svcpart
463 # setup queries, links, subs, etc. for the search
466 # here is the agent virtualization
467 if ($params->{CurrentUser}) {
469 qsearchs('access_user', { username => $params->{CurrentUser} });
472 push @where, $access_user->agentnums_sql('table'=>'cust_main');
477 push @where, $FS::CurrentUser::CurrentUser->agentnums_sql('table'=>'cust_main');
480 my $extra_sql = scalar(@where) ? ' WHERE '. join(' AND ', @where) : '';
482 my $addl_from = 'LEFT JOIN part_pkg USING ( pkgpart ) '.
483 'LEFT JOIN pkg_class ON ( part_pkg.classnum = pkg_class.classnum ) '.
484 'LEFT JOIN cust_location USING ( locationnum ) '.
485 FS::UI::Web::join_cust_main('cust_pkg', 'cust_pkg');
489 if ( $params->{'select_zip5'} ) {
490 my $zip = 'cust_location.zip';
492 $select = "DISTINCT substr($zip,1,5) as zip";
493 $orderby = "ORDER BY substr($zip,1,5)";
494 $count_query = "SELECT COUNT( DISTINCT substr($zip,1,5) )";
498 ( map "part_pkg.$_", qw( pkg freq ) ),
499 'pkg_class.classname',
500 'cust_main.custnum AS cust_main_custnum',
501 FS::UI::Web::cust_sql_fields(
502 $params->{'cust_fields'}
505 $count_query = 'SELECT COUNT(*)';
508 $count_query .= " FROM cust_pkg $addl_from $extra_sql";
511 'table' => 'cust_pkg',
514 'extra_sql' => $extra_sql,
515 'order_by' => $orderby,
516 'addl_from' => $addl_from,
517 'count_query' => $count_query,