Attachment #5529 for bug #6253

View | Details | Raw Unified | Return to bug 6253
Collapse All | Expand All




	#Get data
	push @EXPORT, qw(
		&Search
		&SearchMember 
		&GetMemberDetails
        &GetMemberRelatives
		&GetMember


=head1 FUNCTIONS

=head2 Search

  ($count, $borrowers) = &SearchMember($searchstring, $type, 
                     $category_type, $filter, $showallbranches);

  $borrowers_result_array_ref = &Search($filter,$orderby, $limit, 
                       $columns_out, $search_on_fields,$searchtype);

Looks up patrons (borrowers) on filter. A wrapper for SearchInTable('borrowers').
if $type is "simple", search is performed on the first letter of the
surname only.

For C<$filter>, C<$orderby>, C<$limit>, C<&columns_out>, C<&search_on_fields> and C<&searchtype>
refer to C4::SQLHelper:SearchInTable().

Special C<$filter> key '' is effectively expanded to search on surname firstname othernamescw
and cardnumber unless C<&search_on_fields> is defined
name.

Examples:

  $borrowers = Search('abcd', 'cardnumber');

  $borrowers = Search({''=>'abcd', category_type=>'I'}, 'surname');
reference-to-array; each element is a reference-to-hash, whose keys
are the fields of the C<borrowers> table in the Koha database.
C<$count> is the number of elements in C<$borrowers>.

=cut

sub _express_member_find {
    my ($filter) = @_;

    my ($searchstring, $orderby, $type,$category_type,$filter,$showallbranches ) = @_;
    my $dbh   = C4::Context->dbh;
    my $query = "";
    my $count;
    my @data;
    my @bind = ();
    
    # this is used by circulation everytime a new borrowers cardnumber is scanned
    # so we can check an exact match first, if that works return, otherwise do the rest
    my $dbh   = C4::Context->dbh;
    my $query = "SELECT borrowernumber FROM borrowers WHERE cardnumber = ?";
    if ( my $borrowernumber = $dbh->selectrow_array($query, undef, $filter) ) {
        return( {"borrowernumber"=>$borrowernumber} );
    $sth->execute($searchstring);
    my $data = $sth->fetchall_arrayref({});
    if (@$data){
        return ( scalar(@$data), $data );
    }

    if ( $type eq "simple" )    # simple search for one letter only
    {
        $query .= ($category_type ? " AND category_type = ".$dbh->quote($category_type) : ""); 
        $query .= " WHERE (surname LIKE ? OR cardnumber like ?) ";
        if (C4::Context->preference("IndependantBranches") && !$showallbranches){
          if (C4::Context->userenv && C4::Context->userenv->{flags} % 2 !=1 && C4::Context->userenv->{'branch'}){
            $query.=" AND borrowers.branchcode =".$dbh->quote(C4::Context->userenv->{'branch'}) unless (C4::Context->userenv->{'branch'} eq "insecure");
          }
        }
        $query.=" ORDER BY $orderby";
        @bind = ("$searchstring%","$searchstring");
    }
    else    # advanced search looking in surname, firstname and othernames
    {
        @data  = split( ' ', $searchstring );
        $count = @data;
        $query .= " WHERE ";
        if (C4::Context->preference("IndependantBranches") && !$showallbranches){
          if (C4::Context->userenv && C4::Context->userenv->{flags} % 2 !=1 && C4::Context->userenv->{'branch'}){
            $query.=" borrowers.branchcode =".$dbh->quote(C4::Context->userenv->{'branch'})." AND " unless (C4::Context->userenv->{'branch'} eq "insecure");
          }      
        }     
        $query.="((surname LIKE ? OR (surname LIKE ? AND surname REGEXP ?)
                OR firstname  LIKE ? OR (firstname LIKE ? AND firstname REGEXP ?)
                OR othernames LIKE ? OR (othernames LIKE ? AND othernames REGEXP ?))
        " .
        ($category_type?" AND category_type = ".$dbh->quote($category_type):"");
        my $regex = '[[:punct:][:space:]]'.$data[0];
        @bind = (
            "$data[0]%", "%$data[0]%", $regex, 
            "$data[0]%", "%$data[0]%", $regex, 
            "$data[0]%", "%$data[0]%", $regex 
        );
        for ( my $i = 1 ; $i < $count ; $i++ ) {
            $query = $query . " AND (" . " surname LIKE ? OR (surname LIKE ? AND surname REGEXP ?)
                OR firstname  LIKE ? OR (firstname LIKE ? AND firstname REGEXP ?)
                OR othernames LIKE ? OR (othernames LIKE ? AND othernames REGEXP ?))";
            $regex = '[[:punct:][:space:]]'.$data[$i];
            push( @bind,
              "$data[$i]%", "%$data[$i]%", $regex,
              "$data[$i]%", "%$data[$i]%", $regex,
              "$data[$i]%", "%$data[$i]%", $regex
            );


            # FIXME - .= <<EOT;
        }
        $query = $query . ") OR cardnumber LIKE ? ";
        push( @bind, $searchstring );
        $query .= "order by $orderby";

    my ($search_on_fields, $searchtype);
    if ( length($filter) == 1 ) {
        $search_on_fields = [ qw(surname) ];
        $searchtype = 'start_with';
    } else {
        $search_on_fields = [ qw(surname firstname othernames cardnumber) ];
        $searchtype = 'contain';
    }

    return (undef, $search_on_fields, $searchtype);

    $debug and print STDERR "Q $orderby : $query\n";
    $sth->execute(@bind);
    my @results;
    $data = $sth->fetchall_arrayref({});

    return ( scalar(@$data), $data );
}

=head2 Search

  $borrowers_result_array_ref = &Search($filter,$orderby, $limit, 
                       $columns_out, $search_on_fields,$searchtype);

Looks up patrons (borrowers) on filter.

BUGFIX 499: C<$type> is now used to determine type of search.
if $type is "simple", search is performed on the first letter of the
surname only.

$category_type is used to get a specified type of user. 
(mainly adults when creating a child.)

C<$filter> can be
   - a space-separated list of search terms. Implicit AND is done on them
   - a hash ref containing fieldnames associated with queried value
   - an array ref combining the two previous elements Implicit OR is done between each array element


C<$orderby> is an arrayref of hashref. Contains the name of the field and 0 or 1 depending if order is ascending or descending

C<$limit> is there to allow limiting number of results returned

C<&columns_out> is an array ref to the fieldnames you want to see in the result list

C<&search_on_fields> is an array ref to the fieldnames you want to limit search on when you are using string search

C<&searchtype> is a string telling the type of search you want todo : start_with, exact or contains are allowed

=cut

sub Search {
    my ( $filter, $orderby, $limit, $columns_out, $search_on_fields, $searchtype ) = @_;

    my $search_string;
    my $found_borrower;

    if ( my $fr = ref $filter ) {
        if ( $fr eq "HASH" ) {
            if ( my $search_string = $filter->{''} ) {
                my ($member_filter, $member_search_on_fields, $member_searchtype) = _express_member_find($search_string);
                if ($member_filter) {
                    $filter = $member_filter;
                    $found_borrower = 1;
                } else {
                    $search_on_fields ||= $member_search_on_fields;
                    $searchtype ||= $member_searchtype;
                }
            }
        }
        else {
            $search_string = $filter;
        }
    }
    else {
        $search_string = $filter;
        my ($member_filter, $member_search_on_fields, $member_searchtype) = _express_member_find($search_string);
        if ($member_filter) {
            $filter = $member_filter;
            $found_borrower = 1;
        } else {
            $search_on_fields ||= $member_search_on_fields;
            $searchtype ||= $member_searchtype;
        }
    }

    if ( !$found_borrower && C4::Context->preference('ExtendedPatronAttributes') && $search_string ) {
        my $matching_records = C4::Members::Attributes::SearchIdMatchingAttribute($search_string);
        if(scalar(@$matching_records)>0) {
            if ( my $fr = ref $filter ) {
                if ( $fr eq "HASH" ) {
                    my %f = %$filter;
                    $filter = [ $filter ];
                    delete $f{''};
                    push @$filter, { %f, "borrowernumber"=>$$matching_records };
                }
                else {
                    push @$filter, {"borrowernumber"=>$matching_records};
                }
            }
            else {
                $filter = [ $filter ];
                push @$filter, {"borrowernumber"=>$matching_records};
            }
		}
    }

    # $showallbranches was not used at the time SearchMember() was mainstreamed into Search().
    # Mentioning for the reference

    if ( C4::Context->preference("IndependantBranches") ) { # && !$showallbranches){
        if ( my $userenv = C4::Context->userenv ) {
            my $branch =  $userenv->{'branch'};
            if ( ($userenv->{flags} % 2 !=1) &&
                 $branch && $branch ne "insecure" ){

                if (my $fr = ref $filter) {
                    if ( $fr eq "HASH" ) {
                        $filter->{branchcode} = $branch;
                    }
                    else {
                        foreach (@$filter) {
                            $_ = { '' => $_ } unless ref $_;
                            $_->{branchcode} = $branch;
                        }
                    }
                }
                else {
                    $filter = { '' => $filter, branchcode => $branch };
                }
            }      
        }
    }

    if ($found_borrower) {
        $searchtype = "exact";
    }
    $searchtype ||= "start_with";

	return SearchInTable( "borrowers", $filter, $orderby, $limit, $columns_out, $search_on_fields, $searchtype );
    return ($data);
}

=head2 GetMemberDetails





=head2 SearchIdMatchingAttribute

  my $matching_borrowernumbers = C4::Members::Attributes::SearchIdMatchingAttribute($filter);

=cut

sub SearchIdMatchingAttribute{
    my $filter = shift;
    $filter = [$filter] unless ref $filter;

    my $dbh   = C4::Context->dbh();
    my $query = qq{
SELECT DISTINCT borrowernumber
FROM borrower_attributes
JOIN borrower_attribute_types USING (code)
WHERE staff_searchable = 1
AND (} . join (" OR ", map "attribute like ?", @$filter) .qq{)};
    my $sth = $dbh->prepare_cached($query);
    $sth->execute(map "%$_%", @$filter);
    return [map $_->[0], @{ $sth->fetchall_arrayref }];
}

=head2 CheckUniqueness





$searchtype is string Can be "start_with" or "exact" 

This query builder is very limited, it should be replaced with DBIx::Class
or similar  very soon
Meanwhile adding support for special key '' in case of a data_hashref to
support filters of type

  ( f1 = a OR f2 = a ) AND fx = b AND fy = c

Call for the query above is:

  SearchInTable($tablename, {'' => a, fx => b, fy => c}, $orderby, $limit,
                $columns_out, [f1, f2], 'exact');

NOTE: Current implementation may remove parts of the iinput hashrefs. If that is a problem
a copy needs to be created in _filter_fields() below

=cut

sub SearchInTable{

	if ($keys){
		my @criteria=grep{defined($_) && $_ !~/^\W$/ }@$keys;
		if (@criteria) { 
			$sql.= do { local $"=') OR ('; 
					qq{ WHERE (@criteria) } 
				   }; 
		} 

    if ($orderby){ 
		#Order by desc by default
		my @orders;
		foreach my $order ( ref($orderby) ? @$orderby : $orderby ){
            if (ref $order) {
			    push @orders,map{ "$_".($order->{$_}? " DESC " : "") } keys %$order; 
            } else {
			    push @orders,$order; 
            }
		}
		$sql.= do { local $"=', '; 
				qq{ ORDER BY @orders} 

    my @keys; 
	my @values;
	if (ref($filter_input) eq "HASH"){
		my ($keys, $values);
        if (my $special = delete $filter_input->{''}) { # XXX destroyes '' key
		    ($keys, $values) = _filter_fields($tablename,$special, $searchtype,$filtercolumns);
        }
		my ($hkeys, $hvalues) = _filter_hash($tablename,$filter_input, $searchtype);
		if ($hkeys){
            push @$keys, @$hkeys;
            push @$values, @$hvalues;
        }
		if ($keys){
		    my $stringkey="(".join (") AND (",@$keys).")";
		    return [$stringkey],$values;
		}
		else {
		    return ();
		}
	} elsif (ref($filter_input) eq "ARRAY"){
		foreach my $element_data (@$filter_input){

    my $elements=join "|",@columns_filtered;
	foreach my $field (grep {/\b($elements)\b/} keys %$filter_input){
		## supposed to be a hash of simple values, hashes of arrays could be implemented
		$filter_input->{$field}=format_date_in_iso($filter_input->{$field})
          if $columns->{$field}{Type}=~/date/ &&
             $filter_input->{$field} && $filter_input->{$field} !~C4::Dates->regexp("iso");
		my ($tmpkeys, $localvalues)=_Process_Operands($filter_input->{$field},"$tablename.$field",$searchtype,$columns);
		if (@$tmpkeys){
			push @values, @$localvalues;

sub _filter_string{
	my ($tablename,$filter_input, $searchtype,$filtercolumns)=@_;
	return () unless($filter_input);
	my @operands=split /\s+/,$filter_input;

    # An act of desperation
    $searchtype = 'contain' if @operands > 1 && $searchtype =~ /start_with/o;

	my @columns_filtered= _filter_columns($tablename,$searchtype,$filtercolumns);
	my $columns= _get_columns($tablename);
	my (@values,@keys);

	my @values;
	my @tmpkeys;
	my @localkeys;

    $operand = [$operand] unless ref $operand;
    foreach (@$operand) {
	    push @tmpkeys, " $field = ? ";
	    push @values, $_;
    }
	#By default, exact search
	if (!$searchtype ||$searchtype eq "exact"){
		return \@tmpkeys,\@values;

	if ($columns->{$col_field}->{Type}=~/varchar|text/i){
		my @localvaluesextended;
		if ($searchtype eq "contain"){
            foreach (@$operand) {
			    push @tmpkeys,(" $field LIKE ? ");
			    push @localvaluesextended,("\%$_\%") ;
            }
		}
		if ($searchtype eq "field_start_with"){
            foreach (@$operand) {
			    push @tmpkeys,("$field LIKE ?");
			    push @localvaluesextended, ("$_\%") ;
            }
		}
		if ($searchtype eq "start_with"){
            foreach (@$operand) {
			    push @tmpkeys,("$field LIKE ?","$field LIKE ?");
			    push @localvaluesextended, ("$_\%", " $_\%") ;
            }
		}
		push @values,@localvaluesextended;
	}

Lines 64-70 my @resultsdata; Link Here

(-)a/admin/aqbudget_owner_search.pl (-1 / +1 lines)
64	my $toggle = 0;	64	my $toggle = 0;
65		65
66	if ( $member ) {	66	if ( $member ) {
67	my $results= SearchMember($member,"surname",undef,undef,undef);	67	my $results= Search($member,"surname");
68		68
69	foreach my $res (@$results) {	69	foreach my $res (@$results) {
70		70

Lines 186-192 if ( $print eq 'yes' && $borrowernumber ne '' ) { Link Here

(-)a/circ/circulation.pl (-1 / +1 lines)
186	my $borrowerslist;	186	my $borrowerslist;
187	my $message;	187	my $message;
188	if ($findborrower) {	188	if ($findborrower) {
189	my ($count, $borrowers) = SearchMember($findborrower, 'cardnumber', 'web');	189	my $borrowers = Search($findborrower, 'cardnumber');
190	my @borrowers = @$borrowers;	190	my @borrowers = @$borrowers;
191	if (C4::Context->preference("AddPatronLists")) {	191	if (C4::Context->preference("AddPatronLists")) {
192	$template->param(	192	$template->param(




#use warnings; FIXME - Bug 2505
use CGI;
use C4::Context;
use C4::Members;
use C4::Auth qw/check_cookie_auth/;

my $input   = new CGI;

    exit 0;
}

print map $_->{surname} . ", " . $_->{firstname} . "\t" .
          $_->{cardnumber} . "\t" .
          $_->{address} . "\t" .
          $_->{city} . "\t" .
          $_->{zipcode} . "\t" .
          $_->{country} .
          "\n",
          @{ Search($query, [qw(surname firstname)], [20], [qw(surname firstname cardnumber address city zipcode country)]) };
$sth->execute("$query%", "$query%", "$query%");

while ( my $rec = $sth->fetchrow_hashref ) {
    print $rec->{surname} . ", " . $rec->{firstname} . "\t" .
          $rec->{cardnumber} . "\t" .
          $rec->{address} . "\t" .
          $rec->{city} . "\t" .
          $rec->{zip} . "\t" .
          $rec->{country} .
          "\n";
}




my $background = 0;

if ($member ne ''){
    $results = Search({''=>$member, category_type=>$search_category},$orderby);
    $count = $results ? @$results : 0;

	}
	else
	{
		($count,$results)=SearchMember($member,$orderby,"advanced",$search_category);
	}
	for (my $i=0; $i < $count; $i++){
	#find out stats
	my ($od,$issue,$fines)=GetMemberIssuesAndFines($results->[$i]{'borrowerid'});




			 };
	$categories_dislay{$$category{categorycode}} = $hash;
}
my $AddPatronLists = C4::Context->preference("AddPatronLists") || '';
$template->param( 
        "AddPatronLists_$AddPatronLists" => "1",
            );
if ($AddPatronLists=~/code/){
    $categories[0]->{'first'}=1;
}  


$member =~ s/,//g;   #remove any commas from search string
$member =~ s/\*/%/g;

my ($count,$results);

my @searchpatron;
push @searchpatron, $member if ($member);
push @searchpatron, $patron if ( keys %$patron );
my $from = ( $startfrom - 1 ) * $resultsperpage;
my $to   = $from + $resultsperpage;

my ($count,$results);
if ($member || keys %$patron) {
    #($results)=Search($member || $patron,{surname=>1,firstname=>1},[$from,$to],undef,["firstname","surname","email","othernames"]  );
    my $search_scope = ( $quicksearch ? "field_start_with" : "start_with" );
    ($results) = Search( $member || $patron, \@orderby, undef, undef, [ "firstname", "surname", "othernames", "cardnumber", "userid" ], $search_scope );
}

if ($results) {
	for my $field ('categorycode','branchcode'){




$member =~ s/\*/%/g;

if ($member || $category) {
    my $results = $category ? Search({''=>$member, category_type=>$category}, $orderby)
                            : Search($member, $orderby);
    my $count = $results ? @$results : 0;
    {
        ($count,$results) = SearchMember($member,$orderby,"simple");
    }
    else
    {
        ($count,$results) = SearchMember($member,$orderby,"advanced",$category);
    }


    my @resultsdata = ();
    my $to = ($count>($startfrom * $resultsperpage)?$startfrom * $resultsperpage:$count);




}

if ($findborrower) {
    my $borrowers = Search($findborrower, 'cardnumber');
      SearchMember($findborrower, 'cardnumber', 'web' );

    if ($borrowers && @$borrowers) {
        if ( @$borrowers == 1 ) {
            $borrowernumber_hold = $borrowers->[0]->{'borrowernumber'};
        }
        else {
            $borrowerslist = $borrowers;
        }
    } else {
        $messageborrower = "'$findborrower'";
    }
    elsif ( @borrowers == 1 ) {
        $borrowernumber_hold = $borrowers[0]->{'borrowernumber'};
    }
    else {
        $borrowerslist = \@borrowers;
    }
}

# If we have the borrowernumber because we've performed an action, then we




use warnings;
use C4::Context;

use Test::More tests => 8;

BEGIN {
    use_ok('C4::Koha');
    use_ok('C4::Members');
}

my $data = {


# Tests
SKIP: {
    skip "INSERT failed", 5 unless $insert_success;
    
    is ( GetAuthorisedValueByCode($data->{category}, $data->{authorised_value}), $data->{lib}, "GetAuthorisedValueByCode" );
    is ( GetKohaImageurlFromAuthorisedValues($data->{category}, $data->{lib}), $data->{imageurl}, "GetKohaImageurlFromAuthorisedValues" );

    my $sortdet=C4::Members::GetSortDetails("lost", "3");
    is ($sortdet, "Lost and Paid For", "lost and paid works");

    my $sortdet2=C4::Members::GetSortDetails("loc", "child");
    is ($sortdet2, "Children's Area", "Child area works");

    my $sortdet3=C4::Members::GetSortDetails("withdrawn", "1");
    is ($sortdet3, "Withdrawn", "Withdrawn works");
}

# Clean up




use strict;
use warnings;

use Test::More tests => 20;
use Data::Dumper;

BEGIN {
        use_ok('C4::Members');
}


my $CARDNUMBER   = 'TESTCARD01';
my $FIRSTNAME    = 'Marie';
my $SURNAME      = 'Mcknight';
my $CATEGORYCODE = 'S';
my $BRANCHCODE   = 's';
    branchcode => 's'
    };

my $CHANGED_FIRSTNAME = "Marry Ann";
my $EMAIL             = "Marie\@email.com";
my $ETHNICITY         = "German";
my $PHONE             = "555-12123";

# XXX should be randomised and checked against the database
my $IMPOSSIBLE_CARDNUMBER = "XYZZZ999";

my $INDEPENDENT_BRANCHES_PREF = 'IndependantBranches';
is ($member->{firstname}, "Marie", "Got member");

# XXX make a non-commit transaction and rollback rather than insert/delete
ModMember(%$member);
my $changedmember=GetMemberDetails("","TESTCARD01");
is ($changedmember->{firstname}, "Claire", "Member Changed");

#my ($usernum, $userid, $usercnum, $userfirstname, $usersurname, $userbranch, $branchname, $userflags, $emailaddress, $branchprinter)= @_;
my @USERENV = (
    1,
    'test',
    'MASTERTEST',
    'Test',
    'Test',
    't',
    'Test',
    0,
);
my $BRANCH_IDX = 5;

C4::Context->_new_userenv ('DUMMY_SESSION_ID');
C4::Context->set_userenv ( @USERENV );
$changedmember=GetMemberDetails("","TESTCARD01");
is ($changedmember->{email}, "Marie\@email.com", "Email Set works");

my $userenv = C4::Context->userenv
  or BAIL_OUT("No userenv");
$changedmember=GetMemberDetails("","TESTCARD01");
is ($changedmember->{ethnicity}, "German", "Ethnicity Works");

my @searchstring=("Mcknight");
my ($results) = Search(\@searchstring,undef,undef,undef,["surname"]);
is ($results->[0]->{surname}, "Mcknight", "Surname Search works");

# Make a borrower for testing
my %data = (
    cardnumber => $CARDNUMBER,
    firstname =>  $FIRSTNAME,
    surname => $SURNAME,
    categorycode => $CATEGORYCODE,
    branchcode => $BRANCHCODE,
);

my $addmem=AddMember(%data);
ok($addmem, "AddMember()");

my $member=GetMemberDetails("",$CARDNUMBER)
  or BAIL_OUT("Cannot read member with card $CARDNUMBER");

ok ( $member->{firstname}    eq $FIRSTNAME    &&
     $member->{surname}      eq $SURNAME      &&
     $member->{categorycode} eq $CATEGORYCODE &&
     $member->{branchcode}   eq $BRANCHCODE
     , "Got member")
  or diag("Mismatching member details: ".Dumper(\%data, $member));

$member->{firstname} = $CHANGED_FIRSTNAME;
$member->{email}     = $EMAIL;
$member->{ethnicity} = $ETHNICITY;
$member->{phone}     = $PHONE;
ModMember(%$member);
my $changedmember=GetMemberDetails("",$CARDNUMBER);
ok ( $changedmember->{firstname} eq $CHANGED_FIRSTNAME &&
     $changedmember->{email}     eq $EMAIL             &&
     $changedmember->{ethnicity} eq $ETHNICITY         &&
     $changedmember->{phone}     eq $PHONE
     , "Member Changed")
  or diag("Mismatching member details: ".Dumper($member, $changedmember));

C4::Context->set_preference( $INDEPENDENT_BRANCHES_PREF, '0' );
C4::Context->clear_syspref_cache();

my $results = Search($CARDNUMBER);
ok (@$results == 1, "Search cardnumber returned only one result")
  or diag("Multiple members with Card $CARDNUMBER: ".Dumper($results));
ok (_find_member($results), "Search cardnumber")
  or diag("Card $CARDNUMBER not found in the resultset: ".Dumper($results));

my @searchstring=($SURNAME);
$results = Search(\@searchstring);
ok (_find_member($results), "Search (arrayref)")
  or diag("Card $CARDNUMBER not found in the resultset: ".Dumper($results));

$results = Search(\@searchstring,undef,undef,undef,["surname"]);
ok (_find_member($results), "Surname Search (arrayref)")
  or diag("Card $CARDNUMBER not found in the resultset: ".Dumper($results));

$results = Search("$CHANGED_FIRSTNAME $SURNAME", "surname");
ok (_find_member($results), "Full name  Search (string)")
  or diag("Card $CARDNUMBER not found in the resultset: ".Dumper($results));

@searchstring=($PHONE);
$results = Search(\@searchstring,undef,undef,undef,["phone"]);
ok (_find_member($results), "Phone Search (arrayref)")
  or diag("Card $CARDNUMBER not found in the resultset: ".Dumper($results));

$results = Search($PHONE,undef,undef,undef,["phone"]);
ok (_find_member($results), "Phone Search (string)")
  or diag("Card $CARDNUMBER not found in the resultset: ".Dumper($results));

C4::Context->set_preference( $INDEPENDENT_BRANCHES_PREF, '1' );
C4::Context->clear_syspref_cache();

$results = Search("$CHANGED_FIRSTNAME $SURNAME", "surname");
ok (!_find_member($results), "Full name  Search (string) for independent branches, different branch")
  or diag("Card $CARDNUMBER found in the resultset for independent branches: ".Dumper(C4::Context->preference($INDEPENDENT_BRANCHES_PREF), $results));

@searchstring=($SURNAME);
$results = Search(\@searchstring);
ok (!_find_member($results), "Search (arrayref) for independent branches, different branch")
  or diag("Card $CARDNUMBER found in the resultset for independent branches: ".Dumper(C4::Context->preference($INDEPENDENT_BRANCHES_PREF), $results));

$USERENV[$BRANCH_IDX] = $BRANCHCODE;
C4::Context->set_userenv ( @USERENV );

$results = Search("$CHANGED_FIRSTNAME $SURNAME", "surname");
ok (_find_member($results), "Full name  Search (string) for independent branches, same branch")
  or diag("Card $CARDNUMBER not found in the resultset for independent branches: ".Dumper(C4::Context->preference($INDEPENDENT_BRANCHES_PREF), $results));

@searchstring=($SURNAME);
$results = Search(\@searchstring);
ok (_find_member($results), "Search (arrayref) for independent branches, same branch")
  or diag("Card $CARDNUMBER not found in the resultset for independent branches: ".Dumper(C4::Context->preference($INDEPENDENT_BRANCHES_PREF), $results));


my $checkcardnum=C4::Members::checkcardnumber($CARDNUMBER, "");
is ($checkcardnum, "1", "Card No. in use");

$checkcardnum=C4::Members::checkcardnumber($IMPOSSIBLE_CARDNUMBER, "");
is ($checkcardnum, "0", "Card No. not used");

my $age=GetAge("1992-08-14", "2011-01-19");

$age=GetAge("2011-01-19", "1992-01-19");
is ($age, "-19", "Birthday In the Future");

# clean up 
DelMember($member->{borrowernumber});
$results = Search($CARDNUMBER,undef,undef,undef,["cardnumber"]);
ok (!_find_member($results), "Delete member")
  or diag("Card $CARDNUMBER found for the deleted member in the resultset: ".Dumper($results));

my $sortdet2=C4::Members::GetSortDetails("loc", "child");
is ($sortdet2, "Children's Area", "Child area works");

exit;
is ($sortdet3, "Withdrawn", "Withdrawn works");

sub _find_member {
    my ($resultset) = @_;
    my $found = $resultset && grep( { $_->{cardnumber} && $_->{cardnumber} eq $CARDNUMBER } @$resultset );
    return $found;
}

Lines 12-18 sub testing_class { 'C4::Members' }; Link Here

(-)a/t/db_dependent/lib/KohaTest/Members.pm (-2 / +1 lines)
12		12
13	sub methods : Test( 1 ) {	13	sub methods : Test( 1 ) {
14	my $self = shift;	14	my $self = shift;
15	my @methods = qw( SearchMember	15	my @methods = qw( Search
16	GetMemberDetails	16	GetMemberDetails
17	patronflags	17	patronflags
18	GetMember	18	GetMember
19	-

Return to bug 6253

Lines 118-140 sub GetBorrowerAttributeValue { Link Here

(-)a/C4/Members/Attributes.pm (-6 / +7 lines)
118		118
119	=head2 SearchIdMatchingAttribute	119	=head2 SearchIdMatchingAttribute
120		120
121	my $matching_records = C4::Members::Attributes::SearchIdMatchingAttribute($filter);	121	my $matching_borrowernumbers = C4::Members::Attributes::SearchIdMatchingAttribute($filter);
122		122
123	=cut	123	=cut
124		124
125	sub SearchIdMatchingAttribute{	125	sub SearchIdMatchingAttribute{
126	my $filter = shift;	126	my $filter = shift;
127	my $finalfilter=$filter->[0];	127	$filter = [$filter] unless ref $filter;
		128
128	my $dbh = C4::Context->dbh();	129	my $dbh = C4::Context->dbh();
129	my $query = qq{	130	my $query = qq{
130	SELECT borrowernumber	131	SELECT DISTINCT borrowernumber
131	FROM borrower_attributes	132	FROM borrower_attributes
132	JOIN borrower_attribute_types USING (code)	133	JOIN borrower_attribute_types USING (code)
133	WHERE staff_searchable = 1	134	WHERE staff_searchable = 1
134	AND attribute like ?};	135	AND (} . join (" OR ", map "attribute like ?", @$filter) .qq{)};
135	my $sth = $dbh->prepare_cached($query);	136	my $sth = $dbh->prepare_cached($query);
136	$sth->execute("%$finalfilter%");	137	$sth->execute(map "%$_%", @$filter);
137	return $sth->fetchall_arrayref;	138	return [map $_->[0], @{ $sth->fetchall_arrayref }];
138	}	139	}
139		140
140	=head2 CheckUniqueness	141	=head2 CheckUniqueness

Lines 85-90 $filtercolums is an array ref on field names : is used to limit expansion of res Link Here

(-)a/C4/SQLHelper.pm (-17 / +60 lines)
85		85
86	$searchtype is string Can be "start_with" or "exact"	86	$searchtype is string Can be "start_with" or "exact"
87		87
		88	This query builder is very limited, it should be replaced with DBIx::Class
		89	or similar very soon
		90	Meanwhile adding support for special key '' in case of a data_hashref to
		91	support filters of type
		92
		93	( f1 = a OR f2 = a ) AND fx = b AND fy = c
		94
		95	Call for the query above is:
		96
		97	SearchInTable($tablename, {'' => a, fx => b, fy => c}, $orderby, $limit,
		98	$columns_out, [f1, f2], 'exact');
		99
		100	NOTE: Current implementation may remove parts of the iinput hashrefs. If that is a problem
		101	a copy needs to be created in _filter_fields() below
		102
88	=cut	103	=cut
89		104
90	sub SearchInTable{	105	sub SearchInTable{
Lines 101-107 sub SearchInTable{ Link Here
101	if ($keys){	116	if ($keys){
102	my @criteria=grep{defined($_) && $_ !~/^\W$/ }@$keys;	117	my @criteria=grep{defined($_) && $_ !~/^\W$/ }@$keys;
103	if (@criteria) {	118	if (@criteria) {
104	$sql.= do { local $"=') AND (';	119	$sql.= do { local $"=') OR (';
105	qq{ WHERE (@criteria) }	120	qq{ WHERE (@criteria) }
106	};	121	};
107	}	122	}
Lines 109-116 sub SearchInTable{ Link Here
109	if ($orderby){	124	if ($orderby){
110	#Order by desc by default	125	#Order by desc by default
111	my @orders;	126	my @orders;
112	foreach my $order (@$orderby){	127	foreach my $order ( ref($orderby) ? @$orderby : $orderby ){
113	push @orders,map{ "$_".($order->{$_}? " DESC " : "") } keys %$order;	128	if (ref $order) {
		129	push @orders,map{ "$_".($order->{$_}? " DESC " : "") } keys %$order;
		130	} else {
		131	push @orders,$order;
		132	}
114	}	133	}
115	$sql.= do { local $"=', ';	134	$sql.= do { local $"=', ';
116	qq{ ORDER BY @orders}	135	qq{ ORDER BY @orders}
Lines 287-299 sub _filter_fields{ Link Here
287	my @keys;	306	my @keys;
288	my @values;	307	my @values;
289	if (ref($filter_input) eq "HASH"){	308	if (ref($filter_input) eq "HASH"){
290	my ($keys, $values) = _filter_hash($tablename,$filter_input, $searchtype);	309	my ($keys, $values);
		310	if (my $special = delete $filter_input->{''}) { # XXX destroyes '' key
		311	($keys, $values) = _filter_fields($tablename,$special, $searchtype,$filtercolumns);
		312	}
		313	my ($hkeys, $hvalues) = _filter_hash($tablename,$filter_input, $searchtype);
		314	if ($hkeys){
		315	push @$keys, @$hkeys;
		316	push @$values, @$hvalues;
		317	}
291	if ($keys){	318	if ($keys){
292	my $stringkey="(".join (") AND (",@$keys).")";	319	my $stringkey="(".join (") AND (",@$keys).")";
293	return [$stringkey],$values;	320	return [$stringkey],$values;
294	}	321	}
295	else {	322	else {
296	return ();	323	return ();
297	}	324	}
298	} elsif (ref($filter_input) eq "ARRAY"){	325	} elsif (ref($filter_input) eq "ARRAY"){
299	foreach my $element_data (@$filter_input){	326	foreach my $element_data (@$filter_input){
Lines 334-340 sub _filter_hash{ Link Here
334	my $elements=join "\|",@columns_filtered;	361	my $elements=join "\|",@columns_filtered;
335	foreach my $field (grep {/\b($elements)\b/} keys %$filter_input){	362	foreach my $field (grep {/\b($elements)\b/} keys %$filter_input){
336	## supposed to be a hash of simple values, hashes of arrays could be implemented	363	## supposed to be a hash of simple values, hashes of arrays could be implemented
337	$filter_input->{$field}=format_date_in_iso($filter_input->{$field}) if ($columns->{$field}{Type}=~/date/ && $filter_input->{$field} !~C4::Dates->regexp("iso"));	364	$filter_input->{$field}=format_date_in_iso($filter_input->{$field})
		365	if $columns->{$field}{Type}=~/date/ &&
		366	$filter_input->{$field} && $filter_input->{$field} !~C4::Dates->regexp("iso");
338	my ($tmpkeys, $localvalues)=_Process_Operands($filter_input->{$field},"$tablename.$field",$searchtype,$columns);	367	my ($tmpkeys, $localvalues)=_Process_Operands($filter_input->{$field},"$tablename.$field",$searchtype,$columns);
339	if (@$tmpkeys){	368	if (@$tmpkeys){
340	push @values, @$localvalues;	369	push @values, @$localvalues;
Lines 352-358 sub _filter_hash{ Link Here
352	sub _filter_string{	381	sub _filter_string{
353	my ($tablename,$filter_input, $searchtype,$filtercolumns)=@_;	382	my ($tablename,$filter_input, $searchtype,$filtercolumns)=@_;
354	return () unless($filter_input);	383	return () unless($filter_input);
355	my @operands=split / /,$filter_input;	384	my @operands=split /\s+/,$filter_input;
		385
		386	# An act of desperation
		387	$searchtype = 'contain' if @operands > 1 && $searchtype =~ /start_with/o;
		388
356	my @columns_filtered= _filter_columns($tablename,$searchtype,$filtercolumns);	389	my @columns_filtered= _filter_columns($tablename,$searchtype,$filtercolumns);
357	my $columns= _get_columns($tablename);	390	my $columns= _get_columns($tablename);
358	my (@values,@keys);	391	my (@values,@keys);
Lines 381-388 sub _Process_Operands{ Link Here
381	my @values;	414	my @values;
382	my @tmpkeys;	415	my @tmpkeys;
383	my @localkeys;	416	my @localkeys;
384	push @tmpkeys, " $field = ? ";	417
385	push @values, $operand;	418	$operand = [$operand] unless ref $operand;
		419	foreach (@$operand) {
		420	push @tmpkeys, " $field = ? ";
		421	push @values, $_;
		422	}
386	#By default, exact search	423	#By default, exact search
387	if (!$searchtype \|\|$searchtype eq "exact"){	424	if (!$searchtype \|\|$searchtype eq "exact"){
388	return \@tmpkeys,\@values;	425	return \@tmpkeys,\@values;
Lines 394-409 sub _Process_Operands{ Link Here
394	if ($columns->{$col_field}->{Type}=~/varchar\|text/i){	431	if ($columns->{$col_field}->{Type}=~/varchar\|text/i){
395	my @localvaluesextended;	432	my @localvaluesextended;
396	if ($searchtype eq "contain"){	433	if ($searchtype eq "contain"){
397	push @tmpkeys,(" $field LIKE ? ");	434	foreach (@$operand) {
398	push @localvaluesextended,("\%$operand\%") ;	435	push @tmpkeys,(" $field LIKE ? ");
		436	push @localvaluesextended,("\%$_\%") ;
		437	}
399	}	438	}
400	if ($searchtype eq "field_start_with"){	439	if ($searchtype eq "field_start_with"){
401	push @tmpkeys,("$field LIKE ?");	440	foreach (@$operand) {
402	push @localvaluesextended, ("$operand\%") ;	441	push @tmpkeys,("$field LIKE ?");
		442	push @localvaluesextended, ("$_\%") ;
		443	}
403	}	444	}
404	if ($searchtype eq "start_with"){	445	if ($searchtype eq "start_with"){
405	push @tmpkeys,("$field LIKE ?","$field LIKE ?");	446	foreach (@$operand) {
406	push @localvaluesextended, ("$operand\%", " $operand\%") ;	447	push @tmpkeys,("$field LIKE ?","$field LIKE ?");
		448	push @localvaluesextended, ("$_\%", " $_\%") ;
		449	}
407	}	450	}
408	push @values,@localvaluesextended;	451	push @values,@localvaluesextended;
409	}	452	}

Lines 75-84 foreach my $category (@categories){ Link Here

(-)a/members/member.pl (-10 / +9 lines)
75	};	75	};
76	$categories_dislay{$$category{categorycode}} = $hash;	76	$categories_dislay{$$category{categorycode}} = $hash;
77	}	77	}
		78	my $AddPatronLists = C4::Context->preference("AddPatronLists") \|\| '';
78	$template->param(	79	$template->param(
79	"AddPatronLists_".C4::Context->preference("AddPatronLists")=> "1",	80	"AddPatronLists_$AddPatronLists" => "1",
80	);	81	);
81	if (C4::Context->preference("AddPatronLists")=~/code/){	82	if ($AddPatronLists=~/code/){
82	$categories[0]->{'first'}=1;	83	$categories[0]->{'first'}=1;
83	}	84	}
84		85
Lines 96-112 else { Link Here
96	$member =~ s/,//g; #remove any commas from search string	97	$member =~ s/,//g; #remove any commas from search string
97	$member =~ s/\*/%/g;	98	$member =~ s/\*/%/g;
98		99
99	my ($count,$results);
100
101	my @searchpatron;
102	push @searchpatron, $member if ($member);
103	push @searchpatron, $patron if ( keys %$patron );
104	my $from = ( $startfrom - 1 ) * $resultsperpage;	100	my $from = ( $startfrom - 1 ) * $resultsperpage;
105	my $to = $from + $resultsperpage;	101	my $to = $from + $resultsperpage;
106		102
107	#($results)=Search(\@searchpatron,{surname=>1,firstname=>1},[$from,$to],undef,["firstname","surname","email","othernames"] ) if (@searchpatron);	103	my ($count,$results);
108	my $search_scope = ( $quicksearch ? "field_start_with" : "start_with" );	104	if ($member \|\| keys %$patron) {
109	($results) = Search( \@searchpatron, \@orderby, undef, undef, [ "firstname", "surname", "othernames", "cardnumber", "userid" ], $search_scope ) if (@searchpatron);	105	#($results)=Search($member \|\| $patron,{surname=>1,firstname=>1},[$from,$to],undef,["firstname","surname","email","othernames"] );
		106	my $search_scope = ( $quicksearch ? "field_start_with" : "start_with" );
		107	($results) = Search( $member \|\| $patron, \@orderby, undef, undef, [ "firstname", "surname", "othernames", "cardnumber", "userid" ], $search_scope );
		108	}
110		109
111	if ($results) {	110	if ($results) {
112	for my $field ('categorycode','branchcode'){	111	for my $field ('categorycode','branchcode'){

Lines 112-131 if ( $action eq 'move' ) { Link Here

(-)a/reserve/request.pl (-11 / +9 lines)
112	}	112	}
113		113
114	if ($findborrower) {	114	if ($findborrower) {
115	my ( $count, $borrowers ) =	115	my $borrowers = Search($findborrower, 'cardnumber');
116	SearchMember($findborrower, 'cardnumber', 'web' );
117		116
118	my @borrowers = @$borrowers;	117	if ($borrowers && @$borrowers) {
119		118	if ( @$borrowers == 1 ) {
120	if ( !@borrowers ) {	119	$borrowernumber_hold = $borrowers->[0]->{'borrowernumber'};
		120	}
		121	else {
		122	$borrowerslist = $borrowers;
		123	}
		124	} else {
121	$messageborrower = "'$findborrower'";	125	$messageborrower = "'$findborrower'";
122	}	126	}
123	elsif ( @borrowers == 1 ) {
124	$borrowernumber_hold = $borrowers[0]->{'borrowernumber'};
125	}
126	else {
127	$borrowerslist = \@borrowers;
128	}
129	}	127	}
130		128
131	# If we have the borrowernumber because we've performed an action, then we	129	# If we have the borrowernumber because we've performed an action, then we

Lines 44-50 BEGIN { Link Here

(-)a/C4/Members.pm (-148 / +108 lines)
44	#Get data	44	#Get data
45	push @EXPORT, qw(	45	push @EXPORT, qw(
46	&Search	46	&Search
47	&SearchMember
48	&GetMemberDetails	47	&GetMemberDetails
49	&GetMemberRelatives	48	&GetMemberRelatives
50	&GetMember	49	&GetMember
Lines 141-318 This module contains routines for adding, modifying and deleting members/patrons Link Here
141		140
142	=head1 FUNCTIONS	141	=head1 FUNCTIONS
143		142
144	=head2 SearchMember	143	=head2 Search
145
146	($count, $borrowers) = &SearchMember($searchstring, $type,
147	$category_type, $filter, $showallbranches);
148		144
149	Looks up patrons (borrowers) by name.	145	$borrowers_result_array_ref = &Search($filter,$orderby, $limit,
		146	$columns_out, $search_on_fields,$searchtype);
150		147
151	BUGFIX 499: C<$type> is now used to determine type of search.	148	Looks up patrons (borrowers) on filter. A wrapper for SearchInTable('borrowers').
152	if $type is "simple", search is performed on the first letter of the
153	surname only.
154		149
155	$category_type is used to get a specified type of user.	150	For C<$filter>, C<$orderby>, C<$limit>, C<&columns_out>, C<&search_on_fields> and C<&searchtype>
156	(mainly adults when creating a child.)	151	refer to C4::SQLHelper:SearchInTable().
157		152
158	C<$searchstring> is a space-separated list of search terms. Each term	153	Special C<$filter> key '' is effectively expanded to search on surname firstname othernamescw
159	must match the beginning a borrower's surname, first name, or other	154	and cardnumber unless C<&search_on_fields> is defined
160	name.
161		155
162	C<$filter> is assumed to be a list of elements to filter results on	156	Examples:
163		157
164	C<$showallbranches> is used in IndependantBranches Context to display all branches results.	158	$borrowers = Search('abcd', 'cardnumber');
165		159
166	C<&SearchMember> returns a two-element list. C<$borrowers> is a	160	$borrowers = Search({''=>'abcd', category_type=>'I'}, 'surname');
167	reference-to-array; each element is a reference-to-hash, whose keys
168	are the fields of the C<borrowers> table in the Koha database.
169	C<$count> is the number of elements in C<$borrowers>.
170		161
171	=cut	162	=cut
172		163
173	#'	164	sub _express_member_find {
174	#used by member enquiries from the intranet	165	my ($filter) = @_;
175	sub SearchMember {	166
176	my ($searchstring, $orderby, $type,$category_type,$filter,$showallbranches ) = @_;
177	my $dbh = C4::Context->dbh;
178	my $query = "";
179	my $count;
180	my @data;
181	my @bind = ();
182
183	# this is used by circulation everytime a new borrowers cardnumber is scanned	167	# this is used by circulation everytime a new borrowers cardnumber is scanned
184	# so we can check an exact match first, if that works return, otherwise do the rest	168	# so we can check an exact match first, if that works return, otherwise do the rest
185	$query = "SELECT * FROM borrowers	169	my $dbh = C4::Context->dbh;
186	LEFT JOIN categories ON borrowers.categorycode=categories.categorycode	170	my $query = "SELECT borrowernumber FROM borrowers WHERE cardnumber = ?";
187	";	171	if ( my $borrowernumber = $dbh->selectrow_array($query, undef, $filter) ) {
188	my $sth = $dbh->prepare("$query WHERE cardnumber = ?");	172	return( {"borrowernumber"=>$borrowernumber} );
189	$sth->execute($searchstring);
190	my $data = $sth->fetchall_arrayref({});
191	if (@$data){
192	return ( scalar(@$data), $data );
193	}
194
195	if ( $type eq "simple" ) # simple search for one letter only
196	{
197	$query .= ($category_type ? " AND category_type = ".$dbh->quote($category_type) : "");
198	$query .= " WHERE (surname LIKE ? OR cardnumber like ?) ";
199	if (C4::Context->preference("IndependantBranches") && !$showallbranches){
200	if (C4::Context->userenv && C4::Context->userenv->{flags} % 2 !=1 && C4::Context->userenv->{'branch'}){
201	$query.=" AND borrowers.branchcode =".$dbh->quote(C4::Context->userenv->{'branch'}) unless (C4::Context->userenv->{'branch'} eq "insecure");
202	}
203	}
204	$query.=" ORDER BY $orderby";
205	@bind = ("$searchstring%","$searchstring");
206	}	173	}
207	else # advanced search looking in surname, firstname and othernames
208	{
209	@data = split( ' ', $searchstring );
210	$count = @data;
211	$query .= " WHERE ";
212	if (C4::Context->preference("IndependantBranches") && !$showallbranches){
213	if (C4::Context->userenv && C4::Context->userenv->{flags} % 2 !=1 && C4::Context->userenv->{'branch'}){
214	$query.=" borrowers.branchcode =".$dbh->quote(C4::Context->userenv->{'branch'})." AND " unless (C4::Context->userenv->{'branch'} eq "insecure");
215	}
216	}
217	$query.="((surname LIKE ? OR (surname LIKE ? AND surname REGEXP ?)
218	OR firstname LIKE ? OR (firstname LIKE ? AND firstname REGEXP ?)
219	OR othernames LIKE ? OR (othernames LIKE ? AND othernames REGEXP ?))
220	" .
221	($category_type?" AND category_type = ".$dbh->quote($category_type):"");
222	my $regex = '[[:punct:][:space:]]'.$data[0];
223	@bind = (
224	"$data[0]%", "%$data[0]%", $regex,
225	"$data[0]%", "%$data[0]%", $regex,
226	"$data[0]%", "%$data[0]%", $regex
227	);
228	for ( my $i = 1 ; $i < $count ; $i++ ) {
229	$query = $query . " AND (" . " surname LIKE ? OR (surname LIKE ? AND surname REGEXP ?)
230	OR firstname LIKE ? OR (firstname LIKE ? AND firstname REGEXP ?)
231	OR othernames LIKE ? OR (othernames LIKE ? AND othernames REGEXP ?))";
232	$regex = '[[:punct:][:space:]]'.$data[$i];
233	push( @bind,
234	"$data[$i]%", "%$data[$i]%", $regex,
235	"$data[$i]%", "%$data[$i]%", $regex,
236	"$data[$i]%", "%$data[$i]%", $regex
237	);
238
239
240	# FIXME - .= <<EOT;
241	}
242	$query = $query . ") OR cardnumber LIKE ? ";
243	push( @bind, $searchstring );
244	$query .= "order by $orderby";
245		174
246	# FIXME - .= <<EOT;	175	my ($search_on_fields, $searchtype);
		176	if ( length($filter) == 1 ) {
		177	$search_on_fields = [ qw(surname) ];
		178	$searchtype = 'start_with';
		179	} else {
		180	$search_on_fields = [ qw(surname firstname othernames cardnumber) ];
		181	$searchtype = 'contain';
247	}	182	}
248		183
249	$sth = $dbh->prepare($query);	184	return (undef, $search_on_fields, $searchtype);
250
251	$debug and print STDERR "Q $orderby : $query\n";
252	$sth->execute(@bind);
253	my @results;
254	$data = $sth->fetchall_arrayref({});
255
256	return ( scalar(@$data), $data );
257	}	185	}
258		186
259	=head2 Search
260
261	$borrowers_result_array_ref = &Search($filter,$orderby, $limit,
262	$columns_out, $search_on_fields,$searchtype);
263
264	Looks up patrons (borrowers) on filter.
265
266	BUGFIX 499: C<$type> is now used to determine type of search.
267	if $type is "simple", search is performed on the first letter of the
268	surname only.
269
270	$category_type is used to get a specified type of user.
271	(mainly adults when creating a child.)
272
273	C<$filter> can be
274	- a space-separated list of search terms. Implicit AND is done on them
275	- a hash ref containing fieldnames associated with queried value
276	- an array ref combining the two previous elements Implicit OR is done between each array element
277
278
279	C<$orderby> is an arrayref of hashref. Contains the name of the field and 0 or 1 depending if order is ascending or descending
280
281	C<$limit> is there to allow limiting number of results returned
282
283	C<&columns_out> is an array ref to the fieldnames you want to see in the result list
284
285	C<&search_on_fields> is an array ref to the fieldnames you want to limit search on when you are using string search
286
287	C<&searchtype> is a string telling the type of search you want todo : start_with, exact or contains are allowed
288
289	=cut
290
291	sub Search {	187	sub Search {
292	my ( $filter, $orderby, $limit, $columns_out, $search_on_fields, $searchtype ) = @_;	188	my ( $filter, $orderby, $limit, $columns_out, $search_on_fields, $searchtype ) = @_;
293	my @filters;	189
294	my %filtersmatching_record;	190	my $search_string;
295	my @finalfilter;	191	my $found_borrower;
296	if ( ref($filter) eq "ARRAY" ) {	192
297	push @filters, @$filter;	193	if ( my $fr = ref $filter ) {
298	} else {	194	if ( $fr eq "HASH" ) {
299	push @filters, $filter;	195	if ( my $search_string = $filter->{''} ) {
		196	my ($member_filter, $member_search_on_fields, $member_searchtype) = _express_member_find($search_string);
		197	if ($member_filter) {
		198	$filter = $member_filter;
		199	$found_borrower = 1;
		200	} else {
		201	$search_on_fields \|\|= $member_search_on_fields;
		202	$searchtype \|\|= $member_searchtype;
		203	}
		204	}
		205	}
		206	else {
		207	$search_string = $filter;
		208	}
		209	}
		210	else {
		211	$search_string = $filter;
		212	my ($member_filter, $member_search_on_fields, $member_searchtype) = _express_member_find($search_string);
		213	if ($member_filter) {
		214	$filter = $member_filter;
		215	$found_borrower = 1;
		216	} else {
		217	$search_on_fields \|\|= $member_search_on_fields;
		218	$searchtype \|\|= $member_searchtype;
		219	}
300	}	220	}
301	if ( C4::Context->preference('ExtendedPatronAttributes') ) {	221
302	my $matching_records = C4::Members::Attributes::SearchIdMatchingAttribute($filter);	222	if ( !$found_borrower && C4::Context->preference('ExtendedPatronAttributes') && $search_string ) {
		223	my $matching_records = C4::Members::Attributes::SearchIdMatchingAttribute($search_string);
303	if(scalar(@$matching_records)>0) {	224	if(scalar(@$matching_records)>0) {
304	foreach my $matching_record (@$matching_records) {	225	if ( my $fr = ref $filter ) {
305	$filtersmatching_record{$$matching_record[0]}=1;	226	if ( $fr eq "HASH" ) {
306	}	227	my %f = %$filter;
307	foreach my $k (keys(%filtersmatching_record)) {	228	$filter = [ $filter ];
308	push @filters, {"borrowernumber"=>$k};	229	delete $f{''};
309	}	230	push @$filter, { %f, "borrowernumber"=>$$matching_records };
		231	}
		232	else {
		233	push @$filter, {"borrowernumber"=>$matching_records};
		234	}
		235	}
		236	else {
		237	$filter = [ $filter ];
		238	push @$filter, {"borrowernumber"=>$matching_records};
		239	}
310	}	240	}
311	}	241	}
		242
		243	# $showallbranches was not used at the time SearchMember() was mainstreamed into Search().
		244	# Mentioning for the reference
		245
		246	if ( C4::Context->preference("IndependantBranches") ) { # && !$showallbranches){
		247	if ( my $userenv = C4::Context->userenv ) {
		248	my $branch = $userenv->{'branch'};
		249	if ( ($userenv->{flags} % 2 !=1) &&
		250	$branch && $branch ne "insecure" ){
		251
		252	if (my $fr = ref $filter) {
		253	if ( $fr eq "HASH" ) {
		254	$filter->{branchcode} = $branch;
		255	}
		256	else {
		257	foreach (@$filter) {
		258	$_ = { '' => $_ } unless ref $_;
		259	$_->{branchcode} = $branch;
		260	}
		261	}
		262	}
		263	else {
		264	$filter = { '' => $filter, branchcode => $branch };
		265	}
		266	}
		267	}
		268	}
		269
		270	if ($found_borrower) {
		271	$searchtype = "exact";
		272	}
312	$searchtype \|\|= "start_with";	273	$searchtype \|\|= "start_with";
313	push @finalfilter, \@filters;	274
314	my $data = SearchInTable( "borrowers", \@finalfilter, $orderby, $limit, $columns_out, $search_on_fields, $searchtype );	275	return SearchInTable( "borrowers", $filter, $orderby, $limit, $columns_out, $search_on_fields, $searchtype );
315	return ($data);
316	}	276	}
317		277
318	=head2 GetMemberDetails	278	=head2 GetMemberDetails

Lines 28-33 use strict; Link Here

(-)a/circ/ysearch.pl (-19 / +9 lines)
28	#use warnings; FIXME - Bug 2505	28	#use warnings; FIXME - Bug 2505
29	use CGI;	29	use CGI;
30	use C4::Context;	30	use C4::Context;
		31	use C4::Members;
31	use C4::Auth qw/check_cookie_auth/;	32	use C4::Auth qw/check_cookie_auth/;
32		33
33	my $input = new CGI;	34	my $input = new CGI;
Lines 41-62 if ($auth_status ne "ok") { Link Here
41	exit 0;	42	exit 0;
42	}	43	}
43		44
44	my $dbh = C4::Context->dbh;	45	print map $_->{surname} . ", " . $_->{firstname} . "\t" .
45	my $sql = qq(SELECT surname, firstname, cardnumber, address, city, zipcode, country	46	$_->{cardnumber} . "\t" .
46	FROM borrowers	47	$_->{address} . "\t" .
47	WHERE surname LIKE ?	48	$_->{city} . "\t" .
48	OR firstname LIKE ?	49	$_->{zipcode} . "\t" .
49	OR cardnumber LIKE ?	50	$_->{country} .
50	ORDER BY surname, firstname);	51	"\n",
51	my $sth = $dbh->prepare( $sql );	52	@{ Search($query, [qw(surname firstname)], [20], [qw(surname firstname cardnumber address city zipcode country)]) };
52	$sth->execute("$query%", "$query%", "$query%");
53
54	while ( my $rec = $sth->fetchrow_hashref ) {
55	print $rec->{surname} . ", " . $rec->{firstname} . "\t" .
56	$rec->{cardnumber} . "\t" .
57	$rec->{address} . "\t" .
58	$rec->{city} . "\t" .
59	$rec->{zip} . "\t" .
60	$rec->{country} .
61	"\n";
62	}

Lines 66-79 my @resultsdata; Link Here

(-)a/members/guarantor_search.pl (-8 / +3 lines)
66	my $background = 0;	66	my $background = 0;
67		67
68	if ($member ne ''){	68	if ($member ne ''){
69	if(length($member) == 1)	69	$results = Search({''=>$member, category_type=>$search_category},$orderby);
70	{	70	$count = $results ? @$results : 0;
71	($count,$results)=SearchMember($member,$orderby,"simple",$search_category);	71
72	}
73	else
74	{
75	($count,$results)=SearchMember($member,$orderby,"advanced",$search_category);
76	}
77	for (my $i=0; $i < $count; $i++){	72	for (my $i=0; $i < $count; $i++){
78	#find out stats	73	#find out stats
79	my ($od,$issue,$fines)=GetMemberIssuesAndFines($results->[$i]{'borrowerid'});	74	my ($od,$issue,$fines)=GetMemberIssuesAndFines($results->[$i]{'borrowerid'});

Lines 49-65 $member =~ s/,//g; #remove any commas from search string Link Here

(-)a/patroncards/members-search.pl (-11 / +3 lines)
49	$member =~ s/\*/%/g;	49	$member =~ s/\*/%/g;
50		50
51	if ($member \|\| $category) {	51	if ($member \|\| $category) {
52	my ($count,$results) = 0,0;	52	my $results = $category ? Search({''=>$member, category_type=>$category}, $orderby)
53		53	: Search($member, $orderby);
54	if(length($member) == 1)	54	my $count = $results ? @$results : 0;
55	{
56	($count,$results) = SearchMember($member,$orderby,"simple");
57	}
58	else
59	{
60	($count,$results) = SearchMember($member,$orderby,"advanced",$category);
61	}
62
63		55
64	my @resultsdata = ();	56	my @resultsdata = ();
65	my $to = ($count>($startfrom * $resultsperpage)?$startfrom * $resultsperpage:$count);	57	my $to = ($count>($startfrom * $resultsperpage)?$startfrom * $resultsperpage:$count);

Lines 7-16 use strict; Link Here

(-)a/t/db_dependent/Koha.t (-2 / +12 lines)
7	use warnings;	7	use warnings;
8	use C4::Context;	8	use C4::Context;
9		9
10	use Test::More tests => 4;	10	use Test::More tests => 8;
11		11
12	BEGIN {	12	BEGIN {
13	use_ok('C4::Koha');	13	use_ok('C4::Koha');
		14	use_ok('C4::Members');
14	}	15	}
15		16
16	my $data = {	17	my $data = {
Lines 32-41 ok($insert_success, "Insert data in database"); Link Here
32		33
33	# Tests	34	# Tests
34	SKIP: {	35	SKIP: {
35	skip "INSERT failed", 2 unless $insert_success;	36	skip "INSERT failed", 5 unless $insert_success;
36		37
37	is ( GetAuthorisedValueByCode($data->{category}, $data->{authorised_value}), $data->{lib}, "GetAuthorisedValueByCode" );	38	is ( GetAuthorisedValueByCode($data->{category}, $data->{authorised_value}), $data->{lib}, "GetAuthorisedValueByCode" );
38	is ( GetKohaImageurlFromAuthorisedValues($data->{category}, $data->{lib}), $data->{imageurl}, "GetKohaImageurlFromAuthorisedValues" );	39	is ( GetKohaImageurlFromAuthorisedValues($data->{category}, $data->{lib}), $data->{imageurl}, "GetKohaImageurlFromAuthorisedValues" );
		40
		41	my $sortdet=C4::Members::GetSortDetails("lost", "3");
		42	is ($sortdet, "Lost and Paid For", "lost and paid works");
		43
		44	my $sortdet2=C4::Members::GetSortDetails("loc", "child");
		45	is ($sortdet2, "Children's Area", "Child area works");
		46
		47	my $sortdet3=C4::Members::GetSortDetails("withdrawn", "1");
		48	is ($sortdet3, "Withdrawn", "Withdrawn works");
39	}	49	}
40		50
41	# Clean up	51	# Clean up

Lines 6-67 Link Here

(-)a/t/db_dependent/Members.t (-47 / +134 lines)
6	use strict;	6	use strict;
7	use warnings;	7	use warnings;
8		8
9	use Test::More tests => 15;	9	use Test::More tests => 20;
		10	use Data::Dumper;
10		11
11	BEGIN {	12	BEGIN {
12	use_ok('C4::Members');	13	use_ok('C4::Members');
13	}	14	}
14		15
15		16
16	# Make a borrower for testing	17	my $CARDNUMBER = 'TESTCARD01';
17	my $data = { cardnumber => 'TESTCARD01',	18	my $FIRSTNAME = 'Marie';
18	firstname => 'Marie',	19	my $SURNAME = 'Mcknight';
19	surname => 'Mcknight',	20	my $CATEGORYCODE = 'S';
20	categorycode => 'S',	21	my $BRANCHCODE = 's';
21	branchcode => 's'
22	};
23		22
24	my $addmem=AddMember(%$data);	23	my $CHANGED_FIRSTNAME = "Marry Ann";
		24	my $EMAIL = "Marie\@email.com";
		25	my $ETHNICITY = "German";
		26	my $PHONE = "555-12123";
25		27
		28	# XXX should be randomised and checked against the database
		29	my $IMPOSSIBLE_CARDNUMBER = "XYZZZ999";
26		30
27	my $member=GetMemberDetails("","TESTCARD01");	31	my $INDEPENDENT_BRANCHES_PREF = 'IndependantBranches';
28	is ($member->{firstname}, "Marie", "Got member");
29		32
30	$member->{firstname}="Claire";	33	# XXX make a non-commit transaction and rollback rather than insert/delete
31	ModMember(%$member);
32	my $changedmember=GetMemberDetails("","TESTCARD01");
33	is ($changedmember->{firstname}, "Claire", "Member Changed");
34		34
35	$member->{firstname}="Marie";	35	#my ($usernum, $userid, $usercnum, $userfirstname, $usersurname, $userbranch, $branchname, $userflags, $emailaddress, $branchprinter)= @_;
36	ModMember(%$member);	36	my @USERENV = (
37	$changedmember=GetMemberDetails("","TESTCARD01");	37	1,
38	is ($changedmember->{firstname}, "Marie", "Member Returned");	38	'test',
		39	'MASTERTEST',
		40	'Test',
		41	'Test',
		42	't',
		43	'Test',
		44	0,
		45	);
		46	my $BRANCH_IDX = 5;
39		47
40	$member->{email}="Marie\@email.com";	48	C4::Context->_new_userenv ('DUMMY_SESSION_ID');
41	ModMember(%$member);	49	C4::Context->set_userenv ( @USERENV );
42	$changedmember=GetMemberDetails("","TESTCARD01");
43	is ($changedmember->{email}, "Marie\@email.com", "Email Set works");
44		50
45	$member->{ethnicity}="German";	51	my $userenv = C4::Context->userenv
46	ModMember(%$member);	52	or BAIL_OUT("No userenv");
47	$changedmember=GetMemberDetails("","TESTCARD01");
48	is ($changedmember->{ethnicity}, "German", "Ethnicity Works");
49
50	my @searchstring=("Mcknight");
51	my ($results) = Search(\@searchstring,undef,undef,undef,["surname"]);
52	is ($results->[0]->{surname}, "Mcknight", "Surname Search works");
53		53
54	$member->{phone}="555-12123";	54	# Make a borrower for testing
		55	my %data = (
		56	cardnumber => $CARDNUMBER,
		57	firstname => $FIRSTNAME,
		58	surname => $SURNAME,
		59	categorycode => $CATEGORYCODE,
		60	branchcode => $BRANCHCODE,
		61	);
		62
		63	my $addmem=AddMember(%data);
		64	ok($addmem, "AddMember()");
		65
		66	my $member=GetMemberDetails("",$CARDNUMBER)
		67	or BAIL_OUT("Cannot read member with card $CARDNUMBER");
		68
		69	ok ( $member->{firstname} eq $FIRSTNAME &&
		70	$member->{surname} eq $SURNAME &&
		71	$member->{categorycode} eq $CATEGORYCODE &&
		72	$member->{branchcode} eq $BRANCHCODE
		73	, "Got member")
		74	or diag("Mismatching member details: ".Dumper(\%data, $member));
		75
		76	$member->{firstname} = $CHANGED_FIRSTNAME;
		77	$member->{email} = $EMAIL;
		78	$member->{ethnicity} = $ETHNICITY;
		79	$member->{phone} = $PHONE;
55	ModMember(%$member);	80	ModMember(%$member);
56		81	my $changedmember=GetMemberDetails("",$CARDNUMBER);
57	@searchstring=("555-12123");	82	ok ( $changedmember->{firstname} eq $CHANGED_FIRSTNAME &&
58	($results) = Search(\@searchstring,undef,undef,undef,["phone"]);	83	$changedmember->{email} eq $EMAIL &&
59	is ($results->[0]->{phone}, "555-12123", "phone Search works");	84	$changedmember->{ethnicity} eq $ETHNICITY &&
60		85	$changedmember->{phone} eq $PHONE
61	my $checkcardnum=C4::Members::checkcardnumber("TESTCARD01", "");	86	, "Member Changed")
		87	or diag("Mismatching member details: ".Dumper($member, $changedmember));
		88
		89	C4::Context->set_preference( $INDEPENDENT_BRANCHES_PREF, '0' );
		90	C4::Context->clear_syspref_cache();
		91
		92	my $results = Search($CARDNUMBER);
		93	ok (@$results == 1, "Search cardnumber returned only one result")
		94	or diag("Multiple members with Card $CARDNUMBER: ".Dumper($results));
		95	ok (_find_member($results), "Search cardnumber")
		96	or diag("Card $CARDNUMBER not found in the resultset: ".Dumper($results));
		97
		98	my @searchstring=($SURNAME);
		99	$results = Search(\@searchstring);
		100	ok (_find_member($results), "Search (arrayref)")
		101	or diag("Card $CARDNUMBER not found in the resultset: ".Dumper($results));
		102
		103	$results = Search(\@searchstring,undef,undef,undef,["surname"]);
		104	ok (_find_member($results), "Surname Search (arrayref)")
		105	or diag("Card $CARDNUMBER not found in the resultset: ".Dumper($results));
		106
		107	$results = Search("$CHANGED_FIRSTNAME $SURNAME", "surname");
		108	ok (_find_member($results), "Full name Search (string)")
		109	or diag("Card $CARDNUMBER not found in the resultset: ".Dumper($results));
		110
		111	@searchstring=($PHONE);
		112	$results = Search(\@searchstring,undef,undef,undef,["phone"]);
		113	ok (_find_member($results), "Phone Search (arrayref)")
		114	or diag("Card $CARDNUMBER not found in the resultset: ".Dumper($results));
		115
		116	$results = Search($PHONE,undef,undef,undef,["phone"]);
		117	ok (_find_member($results), "Phone Search (string)")
		118	or diag("Card $CARDNUMBER not found in the resultset: ".Dumper($results));
		119
		120	C4::Context->set_preference( $INDEPENDENT_BRANCHES_PREF, '1' );
		121	C4::Context->clear_syspref_cache();
		122
		123	$results = Search("$CHANGED_FIRSTNAME $SURNAME", "surname");
		124	ok (!_find_member($results), "Full name Search (string) for independent branches, different branch")
		125	or diag("Card $CARDNUMBER found in the resultset for independent branches: ".Dumper(C4::Context->preference($INDEPENDENT_BRANCHES_PREF), $results));
		126
		127	@searchstring=($SURNAME);
		128	$results = Search(\@searchstring);
		129	ok (!_find_member($results), "Search (arrayref) for independent branches, different branch")
		130	or diag("Card $CARDNUMBER found in the resultset for independent branches: ".Dumper(C4::Context->preference($INDEPENDENT_BRANCHES_PREF), $results));
		131
		132	$USERENV[$BRANCH_IDX] = $BRANCHCODE;
		133	C4::Context->set_userenv ( @USERENV );
		134
		135	$results = Search("$CHANGED_FIRSTNAME $SURNAME", "surname");
		136	ok (_find_member($results), "Full name Search (string) for independent branches, same branch")
		137	or diag("Card $CARDNUMBER not found in the resultset for independent branches: ".Dumper(C4::Context->preference($INDEPENDENT_BRANCHES_PREF), $results));
		138
		139	@searchstring=($SURNAME);
		140	$results = Search(\@searchstring);
		141	ok (_find_member($results), "Search (arrayref) for independent branches, same branch")
		142	or diag("Card $CARDNUMBER not found in the resultset for independent branches: ".Dumper(C4::Context->preference($INDEPENDENT_BRANCHES_PREF), $results));
		143
		144
		145	my $checkcardnum=C4::Members::checkcardnumber($CARDNUMBER, "");
62	is ($checkcardnum, "1", "Card No. in use");	146	is ($checkcardnum, "1", "Card No. in use");
63		147
64	$checkcardnum=C4::Members::checkcardnumber("67", "");	148	$checkcardnum=C4::Members::checkcardnumber($IMPOSSIBLE_CARDNUMBER, "");
65	is ($checkcardnum, "0", "Card No. not used");	149	is ($checkcardnum, "0", "Card No. not used");
66		150
67	my $age=GetAge("1992-08-14", "2011-01-19");	151	my $age=GetAge("1992-08-14", "2011-01-19");
Lines 70-83 is ($age, "18", "Age correct"); Link Here
70	$age=GetAge("2011-01-19", "1992-01-19");	154	$age=GetAge("2011-01-19", "1992-01-19");
71	is ($age, "-19", "Birthday In the Future");	155	is ($age, "-19", "Birthday In the Future");
72		156
73	my $sortdet=C4::Members::GetSortDetails("lost", "3");	157	# clean up
74	is ($sortdet, "Lost and Paid For", "lost and paid works");	158	DelMember($member->{borrowernumber});
		159	$results = Search($CARDNUMBER,undef,undef,undef,["cardnumber"]);
		160	ok (!_find_member($results), "Delete member")
		161	or diag("Card $CARDNUMBER found for the deleted member in the resultset: ".Dumper($results));
75		162
76	my $sortdet2=C4::Members::GetSortDetails("loc", "child");
77	is ($sortdet2, "Children's Area", "Child area works");
78		163
79	my $sortdet3=C4::Members::GetSortDetails("withdrawn", "1");	164	exit;
80	is ($sortdet3, "Withdrawn", "Withdrawn works");
81		165
82	# clean up	166	sub _find_member {
83	DelMember($member->{borrowernumber});	167	my ($resultset) = @_;
		168	my $found = $resultset && grep( { $_->{cardnumber} && $_->{cardnumber} eq $CARDNUMBER } @$resultset );
		169	return $found;
		170	}