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