X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=FS%2FFS%2Fcust_main%2FSearch.pm;h=1e9eee79da15f0dbd7c36441b4a86d9b3badf774;hb=18459c4369f63b7fda11a3ff7a0af067bed0c9bf;hp=3813f891a90b680d664d9842fa6365fbeb5e34c7;hpb=9b05a3660eabd3215bdebf9429eb490b41570436;p=freeside.git diff --git a/FS/FS/cust_main/Search.pm b/FS/FS/cust_main/Search.pm index 3813f891a..1e9eee79d 100644 --- a/FS/FS/cust_main/Search.pm +++ b/FS/FS/cust_main/Search.pm @@ -18,7 +18,7 @@ use FS::svc_acct; $DEBUG = 0; $me = '[FS::cust_main::Search]'; -@fuzzyfields = @FS::cust_main::fuzzyfields; +@fuzzyfields = ( 'first', 'last', 'company', 'address1' ); install_callback FS::UID sub { $conf = new FS::Conf; @@ -64,7 +64,8 @@ sub smart_search { my %options = @_; #here is the agent virtualization - my $agentnums_sql = $FS::CurrentUser::CurrentUser->agentnums_sql; + my $agentnums_sql = + $FS::CurrentUser::CurrentUser->agentnums_sql(table => 'cust_main'); my @cust_main = (); @@ -112,11 +113,26 @@ sub smart_search { # custnum search (also try agent_custid), with some tweaking options if your # legacy cust "numbers" have letters } - - if ( $search =~ /^\s*(\d+)\s*$/ + + + if ( $search =~ /@/ ) { + push @cust_main, + map $_->cust_main, + qsearch( { + 'table' => 'cust_main_invoice', + 'hashref' => { 'dest' => $search }, + } + ); + } elsif ( $search =~ /^\s*(\d+)\s*$/ || ( $conf->config('cust_main-agent_custid-format') eq 'ww?d+' && $search =~ /^\s*(\w\w?\d+)\s*$/ ) + || ( $conf->config('cust_main-custnum-display_special') + # it's not currently possible for special prefixes to contain + # digits, so just strip off any alphabetic prefix and match + # the rest to custnum + && $search =~ /^\s*[[:alpha:]]*(\d+)\s*$/ + ) || ( $conf->exists('address1-search' ) && $search =~ /^\s*(\d+\-?\w*)\s*$/ #i.e. 1234A or 9432-D ) @@ -133,11 +149,22 @@ sub smart_search { } ); } - push @cust_main, qsearch( { - 'table' => 'cust_main', - 'hashref' => { 'agent_custid' => $num, %options }, - 'extra_sql' => " AND $agentnums_sql", #agent virtualization - } ); + # for all agents this user can see, if any of them have custnum prefixes + # that match the search string, include customers that match the rest + # of the custnum and belong to that agent + foreach my $agentnum ( $FS::CurrentUser::CurrentUser->agentnums ) { + my $p = $conf->config('cust_main-custnum-display_prefix', $agentnum); + next if !$p; + if ( $p eq substr($num, 0, length($p)) ) { + push @cust_main, qsearch( { + 'table' => 'cust_main', + 'hashref' => { 'custnum' => 0 + substr($num, length($p)), + 'agentnum' => $agentnum, + %options, + }, + } ); + } + } if ( $conf->exists('address1-search') ) { my $len = length($num); @@ -203,7 +230,7 @@ sub smart_search { } elsif ( ! $NameParse->parse($value) ) { my %name = $NameParse->components; - $first = $name{'given_name_1'}; + $first = $name{'given_name_1'} || $name{'initials_1'}; #wtf NameParse, Ed? $last = $name{'surname_1'}; } @@ -422,6 +449,10 @@ HASHREF. Valid parameters are =item status +=item address + +=item refnum + =item cancelled_pkgs bool @@ -460,6 +491,33 @@ sub search { my @where = (); my $orderby; + # initialize these to prevent warnings + $params = { + 'custnum' => '', + 'agentnum' => '', + 'usernum' => '', + 'status' => '', + 'address' => '', + 'paydate_year' => '', + 'invoice_terms' => '', + 'custbatch' => '', + %$params + }; + + ## + # explicit custnum(s) + ## + + if ( $params->{'custnum'} ) { + my @custnums = ref($params->{'custnum'}) ? + @{ $params->{'custnum'} } : + $params->{'custnum'}; + push @where, + 'cust_main.custnum IN (' . + join(',', map { $_ =~ /^(\d+)$/ ? $1 : () } @custnums ) . + ')' if scalar(@custnums) > 0; + } + ## # parse agent ## @@ -488,7 +546,26 @@ sub search { #push @where, $class->$method(); push @where, FS::cust_main->$method(); } - + + ## + # address + ## + if ( $params->{'address'} =~ /\S/ ) { + my $address = dbh->quote('%'. lc($params->{'address'}). '%'); + push @where, '('. join(' OR ', + map "LOWER($_) LIKE $address", + qw(address1 address2 ship_address1 ship_address2) + ). + ')'; + } + + ### + # refnum + ### + if ( $params->{'refnum'} =~ /^(\d+)$/ ) { + push @where, "refnum = $1"; + } + ## # parse cancelled package checkbox ## @@ -506,6 +583,13 @@ sub search { if $params->{'no_censustract'}; ## + # parse with hardcoded tax location checkbox + ## + + push @where, "geocode is not null" + if $params->{'with_geocode'}; + + ## # dates ## @@ -520,11 +604,13 @@ sub search { "cust_main.$field >= $beginning", "cust_main.$field <= $ending"; - # XXX: do this for mysql and/or pull it out of here if(defined $hour) { - if ($dbh->{Driver}->{Name} eq 'Pg') { + if ($dbh->{Driver}->{Name} =~ /Pg/i) { push @where, "extract(hour from to_timestamp(cust_main.$field)) = $hour"; } + elsif( $dbh->{Driver}->{Name} =~ /mysql/i) { + push @where, "hour(from_unixtime(cust_main.$field)) = $hour" + } else { warn "search by time of day not supported on ".$dbh->{Driver}->{Name}." databases"; } @@ -635,6 +721,21 @@ sub search { push @where, "cust_main.custbatch = '$1'"; } + + if ( $params->{'tagnum'} ) { + my @tagnums = ref( $params->{'tagnum'} ) ? @{ $params->{'tagnum'} } : ( $params->{'tagnum'} ); + + @tagnums = grep /^(\d+)$/, @tagnums; + + if ( @tagnums ) { + my $tags_where = "0 < (select count(1) from cust_tag where " + . " cust_tag.custnum = cust_main.custnum and tagnum in (" + . join(',', @tagnums) . "))"; + + push @where, $tags_where; + } + } + ## # setup queries, subs, etc. for the search @@ -643,15 +744,16 @@ sub search { $orderby ||= 'ORDER BY custnum'; # here is the agent virtualization - push @where, $FS::CurrentUser::CurrentUser->agentnums_sql; + push @where, + $FS::CurrentUser::CurrentUser->agentnums_sql(table => 'cust_main'); my $extra_sql = scalar(@where) ? ' WHERE '. join(' AND ', @where) : ''; - my $addl_from = 'LEFT JOIN cust_pkg USING ( custnum ) '; + my $addl_from = ''; my $count_query = "SELECT COUNT(*) FROM cust_main $extra_sql"; - my $select = join(', ', + my @select = ( 'cust_main.custnum', FS::UI::Web::cust_sql_fields($params->{'cust_fields'}), ); @@ -661,14 +763,18 @@ sub search { if ($params->{'flattened_pkgs'}) { + #my $pkg_join = ''; + if ($dbh->{Driver}->{Name} eq 'Pg') { - $select .= ", array_to_string(array(select pkg from cust_pkg left join part_pkg using ( pkgpart ) where cust_main.custnum = cust_pkg.custnum $pkgwhere),'|') as magic"; + push @select, "array_to_string(array(select pkg from cust_pkg left join part_pkg using ( pkgpart ) where cust_main.custnum = cust_pkg.custnum $pkgwhere),'|') as magic"; - }elsif ($dbh->{Driver}->{Name} =~ /^mysql/i) { - $select .= ", GROUP_CONCAT(pkg SEPARATOR '|') as magic"; - $addl_from .= " LEFT JOIN part_pkg using ( pkgpart )"; - }else{ + } elsif ($dbh->{Driver}->{Name} =~ /^mysql/i) { + push @select, "GROUP_CONCAT(part_pkg.pkg SEPARATOR '|') as magic"; + $addl_from .= ' LEFT JOIN cust_pkg USING ( custnum ) '; #Pg too w/flatpkg? + $addl_from .= ' LEFT JOIN part_pkg USING ( pkgpart ) '; + #$pkg_join .= ' LEFT JOIN part_pkg USING ( pkgpart ) '; + } else { warn "warning: unknown database type ". $dbh->{Driver}->{Name}. "omitting packing information from report."; } @@ -690,9 +796,25 @@ sub search { } + if ( $params->{'with_geocode'} ) { + + unshift @extra_headers, 'Tax location override', 'Calculated tax location'; + unshift @extra_fields, sub { my $c = shift; $c->get('geocode'); }, + sub { my $c = shift; + $c->set('geocode', ''); + $c->geocode('cch'); #XXX only cch right now + }; + push @select, 'geocode'; + push @select, 'zip' unless grep { $_ eq 'zip' } @select; + push @select, 'ship_zip' unless grep { $_ eq 'ship_zip' } @select; + } + + my $select = join(', ', @select); + my $sql_query = { 'table' => 'cust_main', 'select' => $select, + 'addl_from' => $addl_from, 'hashref' => {}, 'extra_sql' => $extra_sql, 'order_by' => $orderby, @@ -757,7 +879,7 @@ sub fuzzy_search { sub check_and_rebuild_fuzzyfiles { my $dir = $FS::UID::conf_dir. "/cache.". $FS::UID::datasrc; - rebuild_fuzzyfiles() if grep { ! -e "$dir/cust_main.$_" } @fuzzyfields + rebuild_fuzzyfiles() if grep { ! -e "$dir/cust_main.$_" } @fuzzyfields; } =item rebuild_fuzzyfiles @@ -778,7 +900,7 @@ sub rebuild_fuzzyfiles { flock(LOCK,LOCK_EX) or die "can't lock $dir/cust_main.$fuzzy: $!"; - open (CACHE,">$dir/cust_main.$fuzzy.tmp") + open (CACHE, '>:encoding(UTF-8)', "$dir/cust_main.$fuzzy.tmp") or die "can't open $dir/cust_main.$fuzzy.tmp: $!"; foreach my $field ( $fuzzy, "ship_$fuzzy" ) { @@ -800,6 +922,41 @@ sub rebuild_fuzzyfiles { } +=item append_fuzzyfiles FIRSTNAME LASTNAME COMPANY ADDRESS1 + +=cut + +sub append_fuzzyfiles { + #my( $first, $last, $company ) = @_; + + check_and_rebuild_fuzzyfiles(); + + use Fcntl qw(:flock); + + my $dir = $FS::UID::conf_dir. "/cache.". $FS::UID::datasrc; + + foreach my $field (@fuzzyfields) { + my $value = shift; + + if ( $value ) { + + open(CACHE, '>>:encoding(UTF-8)', "$dir/cust_main.$field" ) + or die "can't open $dir/cust_main.$field: $!"; + flock(CACHE,LOCK_EX) + or die "can't lock $dir/cust_main.$field: $!"; + + print CACHE "$value\n"; + + flock(CACHE,LOCK_UN) + or die "can't unlock $dir/cust_main.$field: $!"; + close CACHE; + } + + } + + 1; +} + =item all_X =cut @@ -807,7 +964,7 @@ sub rebuild_fuzzyfiles { sub all_X { my( $self, $field ) = @_; my $dir = $FS::UID::conf_dir. "/cache.". $FS::UID::datasrc; - open(CACHE,"<$dir/cust_main.$field") + open(CACHE, '<:encoding(UTF-8)', "$dir/cust_main.$field") or die "can't open $dir/cust_main.$field: $!"; my @array = map { chomp; $_; } ; close CACHE;