5 # Copyright 2000-2003 Katipo Communications
7 # This file is part of Koha.
9 # Koha is free software; you can redistribute it and/or modify it under the
10 # terms of the GNU General Public License as published by the Free Software
11 # Foundation; either version 2 of the License, or (at your option) any later
14 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
15 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
16 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
18 # You should have received a copy of the GNU General Public License along with
19 # Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
20 # Suite 330, Boston, MA 02111-1307 USA
28 use Digest::MD5 qw(md5_base64);
29 use Date::Calc qw/Today/;
35 use C4::Circulation::Circ2;
36 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK);
38 $VERSION = do { my @v = '$Revision$' =~ /\d+/g; shift(@v) . "." . join( "_", map { sprintf "%03d", $_ } @v ); };
42 C4::Members - Perl Module containing convenience functions for member handling
50 This module contains routines for adding, modifying and deleting members/patrons/borrowers
93 &getborrowercategoryinfo
96 &GetBorrowersFromSurname
97 &GetBranchCodeFromBorrowers
98 &GetFlagsAndBranchFromBorrower
106 =head2 borrowercategories
108 ($codes_arrayref, $labels_hashref) = &borrowercategories();
110 Looks up the different types of borrowers in the database. Returns two
111 elements: a reference-to-array, which lists the borrower category
112 codes, and a reference-to-hash, which maps the borrower category codes
113 to category descriptions.
118 sub borrowercategories {
119 my $dbh = C4::Context->dbh;
120 my $sth=$dbh->prepare("Select categorycode,description from categories order by description");
124 while (my $data=$sth->fetchrow_hashref){
125 push @codes,$data->{'categorycode'};
126 $labels{$data->{'categorycode'}}=$data->{'description'};
129 return(\@codes,\%labels);
134 ($count, $borrowers) = &BornameSearch($env, $searchstring, $type);
136 Looks up patrons (borrowers) by name.
140 BUGFIX 499: C<$type> is now used to determine type of search.
141 if $type is "simple", search is performed on the first letter of the
144 C<$searchstring> is a space-separated list of search terms. Each term
145 must match the beginning a borrower's surname, first name, or other
148 C<&BornameSearch> returns a two-element list. C<$borrowers> is a
149 reference-to-array; each element is a reference-to-hash, whose keys
150 are the fields of the C<borrowers> table in the Koha database.
151 C<$count> is the number of elements in C<$borrowers>.
155 #used by member enquiries from the intranet
158 my ($env,$searchstring,$orderby,$type)=@_;
159 my $dbh = C4::Context->dbh;
160 my $query = ""; my $count;
164 if($type eq "simple") # simple search for one letter only
166 $query="Select * from borrowers where surname like '$searchstring%' order by $orderby";
167 # @bind=("$searchstring%");
169 else # advanced search looking in surname, firstname and othernames
171 ### Try to determine whether numeric like cardnumber
172 if ($searchstring+1>1) {
173 $query="Select * from borrowers where cardnumber like '$searchstring%' ";
177 my @words=split / /,$searchstring;
178 foreach my $word(@words){
182 $searchstring=join " ",@words;
184 $query="Select * from borrowers where MATCH(surname,firstname,othernames) AGAINST('$searchstring' in boolean mode)";
187 $query=$query." order by $orderby";
190 my $sth=$dbh->prepare($query);
191 # warn "Q $orderby : $query";
195 while (my $data=$sth->fetchrow_hashref){
196 push(@results,$data);
200 return ($cnt,\@results);
202 =head2 getpatroninformation
204 ($borrower, $flags) = &getpatroninformation($env, $borrowernumber, $cardnumber);
205 Looks up a patron and returns information about him or her. If
206 C<$borrowernumber> is true (nonzero), C<&getpatroninformation> looks
207 up the borrower by number; otherwise, it looks up the borrower by card
209 C<$env> is effectively ignored, but should be a reference-to-hash.
210 C<$borrower> is a reference-to-hash whose keys are the fields of the
211 borrowers table in the Koha database. In addition,
212 C<$borrower-E<gt>{flags}> is a hash giving more detailed information
213 about the patron. Its keys act as flags :
215 if $borrower->{flags}->{LOST} {
216 # Patron's card was reported lost
219 Each flag has a C<message> key, giving a human-readable explanation of
220 the flag. If the state of a flag means that the patron should not be
221 allowed to borrow any more books, then it will have a C<noissues> key
224 The possible flags are:
230 Shows the patron's credit or debt, if any.
238 (Gone, no address.) Set if the patron has left without giving a
247 Set if the patron's card has been reported as lost.
255 Set if the patron has been debarred.
263 Any additional notes about the patron.
271 Set if the patron has overdue items. This flag has several keys:
273 C<$flags-E<gt>{ODUES}{itemlist}> is a reference-to-array listing the
274 overdue items. Its elements are references-to-hash, each describing an
275 overdue item. The keys are selected fields from the issues, biblio,
276 biblioitems, and items tables of the Koha database.
278 C<$flags-E<gt>{ODUES}{itemlist}> is a string giving a text listing of
279 the overdue items, one per line.
287 Set if any items that the patron has reserved are available.
289 C<$flags-E<gt>{WAITING}{itemlist}> is a reference-to-array listing the
290 available items. Each element is a reference-to-hash whose keys are
291 fields from the reserves table of the Koha database.
299 sub getpatroninformation {
301 my ($env, $borrowernumber,$cardnumber) = @_;
302 my $dbh = C4::Context->dbh;
305 if ($borrowernumber) {
306 $sth = $dbh->prepare("select * from borrowers where borrowernumber=?");
307 $sth->execute($borrowernumber);
308 } elsif ($cardnumber) {
309 $sth = $dbh->prepare("select * from borrowers where cardnumber=?");
310 $sth->execute($cardnumber);
312 $env->{'apierror'} = "invalid borrower information passed to getpatroninformation subroutine";
315 my $borrower = $sth->fetchrow_hashref;
316 my $amount = C4::Accounts2::checkaccount($env, $borrowernumber, $dbh);
317 $borrower->{'amountoutstanding'} = $amount;
318 my $flags = C4::Circulation::Circ2::patronflags($env, $borrower, $dbh);
321 $sth=$dbh->prepare("select bit,flag from userflags");
323 while (my ($bit, $flag) = $sth->fetchrow) {
324 if ($borrower->{'flags'} & 2**$bit) {
325 $accessflagshash->{$flag}=1;
329 $borrower->{'flags'}=$flags;
330 $borrower->{'authflags'} = $accessflagshash;
331 return ($borrower); #, $flags, $accessflagshash);
336 $borrower = &getmember($cardnumber, $borrowernumber);
338 Looks up information about a patron (borrower) by either card number
339 or borrower number. If $borrowernumber is specified, C<&borrdata>
340 searches by borrower number; otherwise, it searches by card number.
342 C<&getmember> returns a reference-to-hash whose keys are the fields of
343 the C<borrowers> table in the Koha database.
347 =head3 GetFlagsAndBranchFromBorrower
351 ($flags, $homebranch) = GetFlagsAndBranchFromBorrower($loggedinuser);
353 this function read on the database to get flags and homebranch for a user
357 it returns the $flags & the homebranch in scalar context.
367 ($count, $issues) = &borrissues($borrowernumber);
369 Looks up what the patron with the given borrowernumber has borrowed.
371 C<&borrissues> returns a two-element array. C<$issues> is a
372 reference-to-array, where each element is a reference-to-hash; the
373 keys are the fields from the C<issues>, C<biblio>, and C<items> tables
374 in the Koha database. C<$count> is the number of elements in
381 my $dbh = C4::Context->dbh;
382 my $sth=$dbh->prepare("Select * from issues,biblio,items where borrowernumber=?
383 and items.itemnumber=issues.itemnumber
384 and items.biblionumber=biblio.biblionumber
385 and issues.returndate is NULL order by date_due");
386 $sth->execute($bornum);
388 while (my $data = $sth->fetchrow_hashref) {
392 return(scalar(@result), \@result);
397 ($count, $issues) = &allissues($borrowernumber, $sortkey, $limit);
399 Looks up what the patron with the given borrowernumber has borrowed,
400 and sorts the results.
402 C<$sortkey> is the name of a field on which to sort the results. This
403 should be the name of a field in the C<issues>, C<biblio>,
404 C<biblioitems>, or C<items> table in the Koha database.
406 C<$limit> is the maximum number of results to return.
408 C<&allissues> returns a two-element array. C<$issues> is a
409 reference-to-array, where each element is a reference-to-hash; the
410 keys are the fields from the C<issues>, C<biblio>, C<biblioitems>, and
411 C<items> tables of the Koha database. C<$count> is the number of
412 elements in C<$issues>
417 my ($bornum,$order,$limit)=@_;
418 #FIXME: sanity-check order and limit
419 my $dbh = C4::Context->dbh;
420 my $query="Select * from issues,biblio,items
421 where borrowernumber=? and
422 items.itemnumber=issues.itemnumber and
423 items.biblionumber=biblio.biblionumber order by $order";
425 $query.=" limit $limit";
428 my $sth=$dbh->prepare($query);
429 $sth->execute($bornum);
432 while (my $data=$sth->fetchrow_hashref){
442 ## NEU specific. used in Reserve section issues
443 my ($env,$bornum)=@_;
444 my $dbh = C4::Context->dbh;
445 my $query="Select count(*) from reserveissue as r where r.borrowernumber='$bornum'
446 and rettime is null";
448 my $sth=$dbh->prepare($query);
450 my $data=$sth->fetchrow_hashref;
452 $sth=$dbh->prepare("Select count(*),timediff(now(), duetime ) as elapsed, hour(timediff(now(), duetime )) as hours, MINUTE(timediff(now(), duetime )) as min from
453 reserveissue as r where r.borrowernumber='$bornum' and rettime is null and duetime< now() group by r.borrowernumber");
456 my $data2=$sth->fetchrow_hashref;
458 my $rescharge=C4::Context->preference('resmaterialcharge');
462 if ($data2->{'elapsed'}>0){
463 $resfine=($data2->{'hours'}+$data2->{'min'}/60)*$rescharge;
464 $resfine=sprintf ("%.1f",$resfine);
467 $sth=$dbh->prepare("Select sum(amountoutstanding) from accountlines where
468 borrowernumber='$bornum'");
470 my $data3=$sth->fetchrow_hashref;
474 return($data2->{'count(*)'},$data->{'count(*)'},$data3->{'sum(amountoutstanding)'},$resfine);
476 =item getboracctrecord
478 ($count, $acctlines, $total) = &getboracctrecord($env, $borrowernumber);
480 Looks up accounting data for the patron with the given borrowernumber.
485 C<&getboracctrecord> returns a three-element array. C<$acctlines> is a
486 reference-to-array, where each element is a reference-to-hash; the
487 keys are the fields of the C<accountlines> table in the Koha database.
488 C<$count> is the number of elements in C<$acctlines>. C<$total> is the
489 total amount outstanding for all of the account lines.
493 sub getboracctrecord {
494 my ($env,$params) = @_;
495 my $dbh = C4::Context->dbh;
498 my $sth=$dbh->prepare("Select * from accountlines where
499 borrowernumber=? order by date desc,timestamp desc");
501 $sth->execute($params->{'borrowernumber'});
503 while (my $data=$sth->fetchrow_hashref){
504 $acctlines[$numlines] = $data;
506 $total += $data->{'amountoutstanding'};
509 return ($numlines,\@acctlines,$total);
512 sub getborrowercategory{
514 my $dbh = C4::Context->dbh;
515 my $sth = $dbh->prepare("SELECT description FROM categories WHERE categorycode = ?");
516 $sth->execute($catcode);
517 my $description = $sth->fetchrow();
520 } # sub getborrowercategory
522 sub getborrowercategoryinfo{
524 my $dbh = C4::Context->dbh;
525 my $sth = $dbh->prepare("SELECT * FROM categories WHERE categorycode = ?");
526 $sth->execute($catcode);
527 my $category = $sth->fetchrow_hashref;
530 } # sub getborrowercategoryinfo
533 sub GetFlagsAndBranchFromBorrower {
534 my $loggedinuser = @_;
535 my $dbh = C4::Context->dbh;
537 SELECT flags, branchcode
539 WHERE borrowernumber = ?
541 my $sth = $dbh->prepare($query);
542 $sth->execute($loggedinuser);
544 return $sth->fetchrow;
549 my ( $cardnumber, $bornum ) = @_;
550 $cardnumber = uc $cardnumber;
551 my $dbh = C4::Context->dbh;
553 if ( $bornum eq '' ) {
554 $sth = $dbh->prepare("Select * from borrowers where cardnumber=?");
555 $sth->execute($cardnumber);
557 $sth = $dbh->prepare("Select * from borrowers where borrowernumber=?");
558 $sth->execute($bornum);
560 my $data = $sth->fetchrow_hashref;
565 else { # try with firstname
568 $dbh->prepare("select * from borrowers where firstname=?");
569 $sth->execute($cardnumber);
570 my $data = $sth->fetchrow_hashref;
580 $borrower = &borrdata($cardnumber, $borrowernumber);
582 Looks up information about a patron (borrower) by either card number
583 or borrower number. If $borrowernumber is specified, C<&borrdata>
584 searches by borrower number; otherwise, it searches by card number.
586 C<&borrdata> returns a reference-to-hash whose keys are the fields of
587 the C<borrowers> table in the Koha database.
593 my ( $cardnumber, $bornum ) = @_;
594 $cardnumber = uc $cardnumber;
595 my $dbh = C4::Context->dbh;
597 if ( $bornum eq '' ) {
600 "Select borrowers.*,categories.category_type from borrowers left join categories on borrowers.categorycode=categories.categorycode where cardnumber=?"
602 $sth->execute($cardnumber);
607 "Select borrowers.*,categories.category_type from borrowers left join categories on borrowers.categorycode=categories.categorycode where borrowernumber=?"
609 $sth->execute($bornum);
611 my $data = $sth->fetchrow_hashref;
612 # warn "DATA" . $data->{category_type};
617 else { # try with firstname
621 "Select borrowers.*,categories.category_type from borrowers left join categories on borrowers.categorycode=categories.categorycode where firstname=?"
623 $sth->execute($cardnumber);
624 my $data = $sth->fetchrow_hashref;
634 ($borrowed, $due, $fine) = &borrdata2($env, $borrowernumber);
636 Returns aggregate data about items borrowed by the patron with the
637 given borrowernumber.
641 C<&borrdata2> returns a three-element array. C<$borrowed> is the
642 number of books the patron currently has borrowed. C<$due> is the
643 number of overdue items the patron currently has borrowed. C<$fine> is
644 the total fine currently due by the borrower.
650 my ( $env, $bornum ) = @_;
651 my $dbh = C4::Context->dbh;
652 my $query = "Select count(*) from issues where borrowernumber='$bornum' and
656 my $sth = $dbh->prepare($query);
658 my $data = $sth->fetchrow_hashref;
660 $sth = $dbh->prepare(
661 "Select count(*) from issues where
662 borrowernumber='$bornum' and date_due < now() and returndate is NULL"
665 my $data2 = $sth->fetchrow_hashref;
667 $sth = $dbh->prepare(
668 "Select sum(amountoutstanding) from accountlines where
669 borrowernumber='$bornum'"
672 my $data3 = $sth->fetchrow_hashref;
675 return ( $data2->{'count(*)'}, $data->{'count(*)'},
676 $data3->{'sum(amountoutstanding)'} );
681 my $dbh = C4::Context->dbh;
682 $data{'dateofbirth'}=format_date_in_iso($data{'dateofbirth'});
685 $data{'joining'}=format_date_in_iso($data{'joining'});
687 if ($data{'expiry'} eq '') {
689 my $sth = $dbh->prepare("select enrolmentperiod from categories where categorycode=?");
690 $sth->execute($data{'categorycode'});
691 my ($enrolmentperiod) = $sth->fetchrow;
692 $enrolmentperiod = 12 unless ($enrolmentperiod);
693 $data{'expiry'} = &DateCalc($data{'joining'},"$enrolmentperiod years");
695 $data{'expiry'}=format_date_in_iso($data{'expiry'});
696 my $query= "UPDATE borrowers SET
697 cardnumber = '$data{'cardnumber'}' ,
698 surname = '$data{'surname'}' ,
699 firstname = '$data{'firstname'}' ,
700 title = '$data{'title'}' ,
701 initials = '$data{'initials'}' ,
702 dateofbirth = '$data{'dateofbirth'}' ,
703 sex = '$data{'sex'}' ,
704 streetaddress = '$data{'streetaddress'}' ,
705 streetcity = '$data{'streetcity'}' ,
706 zipcode = '$data{'zipcode'}' ,
707 phoneday = '$data{'phoneday'}' ,
708 physstreet = '$data{'physstreet'}' ,
709 city = '$data{'city'}' ,
710 homezipcode = '$data{'homezipcode'}' ,
711 phone = '$data{'phone'}' ,
712 emailaddress = '$data{'emailaddress'}' ,
713 faxnumber = '$data{'faxnumber'}' ,
714 textmessaging = '$data{'textmessaging'}' ,
715 categorycode = '$data{'categorycode'}' ,
716 branchcode = '$data{'branchcode'}' ,
717 borrowernotes = '$data{'borrowernotes'}' ,
718 ethnicity = '$data{'ethnicity'}' ,
719 ethnotes = '$data{'ethnotes'}' ,
720 expiry = '$data{'expiry'}' ,
721 dateenrolled = '$data{'joining'}' ,
722 sort1 = '$data{'sort1'}' ,
723 sort2 = '$data{'sort2'}' ,
724 debarred = '$data{'debarred'}' ,
725 lost = '$data{'lost'}' ,
726 gonenoaddress = '$data{'gna'}'
727 WHERE borrowernumber = $data{'borrowernumber'}";
728 my $sth = $dbh->prepare($query);
731 # ok if its an adult (type) it may have borrowers that depend on it as a guarantor
732 # so when we update information for an adult we should check for guarantees and update the relevant part
733 # of their records, ie addresses and phone numbers
734 if ($data{'categorycode'} eq 'A' || $data{'categorycode'} eq 'W'){
735 # is adult check guarantees;
736 updateguarantees(%data);
742 my $dbh = C4::Context->dbh;
743 $data{'dateofbirth'}=format_date_in_iso($data{'dateofbirth'});
744 $data{'joining'} = &ParseDate("today") unless $data{'joining'};
745 $data{'joining'}=format_date_in_iso($data{'joining'});
746 # if expirydate is not set, calculate it from borrower category subscription duration
747 unless ($data{'expiry'}) {
748 my $sth = $dbh->prepare("select enrolmentperiod from categories where categorycode=?");
749 $sth->execute($data{'categorycode'});
750 my ($enrolmentperiod) = $sth->fetchrow;
751 $enrolmentperiod = 12 unless ($enrolmentperiod);
752 $data{'expiry'} = &DateCalc($data{'joining'},"$enrolmentperiod years");
754 $data{'expiry'}=format_date_in_iso($data{'expiry'});
755 my $query= "INSERT INTO borrowers (
785 '$data{'cardnumber'}',
787 '$data{'firstname'}',
790 '$data{'dateofbirth'}',
793 '$data{'streetaddress'}',
794 '$data{'streetcity'}',
798 '$data{'physstreet'}',
800 '$data{'homezipcode'}',
803 '$data{'emailaddress'}',
804 '$data{'faxnumber'}',
805 '$data{'textmessaging'}',
807 '$data{'categorycode'}',
808 '$data{'branchcode'}',
809 '$data{'borrowernotes'}',
810 '$data{'ethnicity'}',
817 my $sth=$dbh->prepare($query);
820 $data{'bornum'} =$dbh->{'mysql_insertid'};
821 return $data{'bornum'};
825 my ( $categorycode, $dateenrolled ) = @_;
826 my $dbh = C4::Context->dbh;
829 "select enrolmentperiod from categories where categorycode=?");
830 $sth->execute($categorycode);
831 my ($enrolmentperiod) = $sth->fetchrow;
832 $enrolmentperiod = 12 unless ($enrolmentperiod);
833 return format_date_in_iso(
834 &DateCalc( $dateenrolled, "$enrolmentperiod months" ) );
837 =head2 checkuserpassword (OUEST-PROVENCE)
839 check for the password and login are not used
840 return the number of record
841 0=> NOT USED 1=> USED
845 sub checkuserpassword {
846 my ( $borrowernumber, $userid, $password ) = @_;
847 $password = md5_base64($password);
848 my $dbh = C4::Context->dbh;
851 "Select count(*) from borrowers where borrowernumber !=? and userid =? and password=? "
853 $sth->execute( $borrowernumber, $userid, $password );
854 my $number_rows = $sth->fetchrow;
858 sub getmemberfromuserid {
860 my $dbh = C4::Context->dbh;
861 my $sth = $dbh->prepare("select * from borrowers where userid=?");
862 $sth->execute($userid);
863 return $sth->fetchrow_hashref;
865 sub updateguarantees {
867 my $dbh = C4::Context->dbh;
868 my ( $count, $guarantees ) = findguarantees( $data{'borrowernumber'} );
869 for ( my $i = 0 ; $i < $count ; $i++ ) {
872 # It looks like the $i is only being returned to handle walking through
873 # the array, which is probably better done as a foreach loop.
876 "update borrowers set streetaddress='$data{'address'}',faxnumber='$data{'faxnumber'}',
877 streetcity='$data{'streetcity'}',phoneday='$data{'phoneday'}',city='$data{'city'}',area='$data{'area'}',phone='$data{'phone'}'
878 ,streetaddress='$data{'address'}'
879 where borrowernumber='$guarantees->[$i]->{'borrowernumber'}'";
880 my $sth3 = $dbh->prepare($guaquery);
885 ################################################################################
887 =item fixup_cardnumber
889 Warning: The caller is responsible for locking the members table in write
890 mode, to avoid database corruption.
894 use vars qw( @weightings );
895 my @weightings = ( 8, 4, 6, 3, 5, 2, 1 );
897 sub fixup_cardnumber ($) {
898 my ($cardnumber) = @_;
899 my $autonumber_members = C4::Context->boolean_preference('autoMemberNum');
900 $autonumber_members = 0 unless defined $autonumber_members;
902 # Find out whether member numbers should be generated
903 # automatically. Should be either "1" or something else.
904 # Defaults to "0", which is interpreted as "no".
906 # if ($cardnumber !~ /\S/ && $autonumber_members) {
907 if ($autonumber_members) {
908 my $dbh = C4::Context->dbh;
909 if ( C4::Context->preference('checkdigit') eq 'katipo' ) {
911 # if checkdigit is selected, calculate katipo-style cardnumber.
912 # otherwise, just use the max()
913 # purpose: generate checksum'd member numbers.
914 # We'll assume we just got the max value of digits 2-8 of member #'s
915 # from the database and our job is to increment that by one,
916 # determine the 1st and 9th digits and return the full string.
919 "select max(substring(borrowers.cardnumber,2,7)) from borrowers"
923 my $data = $sth->fetchrow_hashref;
924 $cardnumber = $data->{'max(substring(borrowers.cardnumber,2,7))'};
927 if ( !$cardnumber ) { # If DB has no values,
928 $cardnumber = 1000000; # start at 1000000
934 for ( my $i = 0 ; $i < 8 ; $i += 1 ) {
936 # read weightings, left to right, 1 char at a time
937 my $temp1 = $weightings[$i];
939 # sequence left to right, 1 char at a time
940 my $temp2 = substr( $cardnumber, $i, 1 );
942 # mult each char 1-7 by its corresponding weighting
943 $sum += $temp1 * $temp2;
946 $rem = ( $sum % 11 );
947 $rem = 'X' if $rem == 10;
949 $cardnumber = "V$cardnumber$rem";
953 # MODIFIED BY JF: mysql4.1 allows casting as an integer, which is probably
954 # better. I'll leave the original in in case it needs to be changed for you
957 "select max(cast(cardnumber as signed)) from borrowers");
959 #my $sth=$dbh->prepare("select max(borrowers.cardnumber) from borrowers");
963 $cardnumber="V$cardnumber$rem";
968 sub fixupneu_cardnumber{
969 my($cardnumber,$categorycode) = @_;
970 my $autonumber_members = C4::Context->boolean_preference('autoMemberNum');
971 $autonumber_members = 0 unless defined $autonumber_members;
972 # Find out whether member numbers should be generated
973 # automatically. Should be either "1" or something else.
974 # Defaults to "0", which is interpreted as "no".
975 my $dbh = C4::Context->dbh;
977 if (! $cardnumber && $autonumber_members && $categorycode) {
978 if ($categorycode eq "A" || $categorycode eq "W" || $categorycode eq "C"){
979 $sth=$dbh->prepare("select max(borrowers.cardnumber) from borrowers where borrowers.cardnumber like '5%' ");
980 }elsif ($categorycode eq "L"){
981 $sth=$dbh->prepare("select max(borrowers.cardnumber) from borrowers where borrowers.cardnumber like '10%' ");
982 }elsif ($categorycode eq "F" || $categorycode eq "E") {
983 $sth=$dbh->prepare("select max(borrowers.cardnumber) from borrowers where borrowers.cardnumber like '30%' ");
984 }elsif ($categorycode eq "N"){
985 $sth=$dbh->prepare("select max(borrowers.cardnumber) from borrowers where borrowers.cardnumber like '40%' ");
987 $sth=$dbh->prepare("select max(borrowers.cardnumber) from borrowers where borrowers.cardnumber like '6%' ");
991 my $data=$sth->fetchrow_hashref;
992 $cardnumber=$data->{'max(borrowers.cardnumber)'};
995 # purpose: generate checksum'd member numbers.
996 # We'll assume we just got the max value of digits 2-8 of member #'s
997 # from the database and our job is to increment that by one,
998 # determine the 1st and 9th digits and return the full string.
1000 if (! $cardnumber) { # If DB has no values,
1001 if ($categorycode eq "A" || $categorycode eq "W" || $categorycode eq "C"){ $cardnumber = 5000000;}
1002 elsif ($categorycode eq "L"){ $cardnumber = 1000000;}
1003 elsif ($categorycode eq "F"){ $cardnumber = 3000000;}
1004 else{$cardnumber = 6000000;}
1005 # start at 1000000 or 3000000 or 5000000
1015 =item GuarantornameSearch
1017 ($count, $borrowers) = &GuarantornameSearch($env, $searchstring, $type);
1019 Looks up guarantor by name.
1023 BUGFIX 499: C<$type> is now used to determine type of search.
1024 if $type is "simple", search is performed on the first letter of the
1027 C<$searchstring> is a space-separated list of search terms. Each term
1028 must match the beginning a borrower's surname, first name, or other
1031 C<&GuarantornameSearch> returns a two-element list. C<$borrowers> is a
1032 reference-to-array; each element is a reference-to-hash, whose keys
1033 are the fields of the C<borrowers> table in the Koha database.
1034 C<$count> is the number of elements in C<$borrowers>.
1036 return all info from guarantor =>only category_type A
1041 #used by member enquiries from the intranet
1042 #called by guarantor_search.pl
1043 sub GuarantornameSearch {
1044 my ( $env, $searchstring, $orderby, $type ) = @_;
1045 my $dbh = C4::Context->dbh;
1051 if ( $type eq "simple" ) # simple search for one letter only
1054 "Select * from borrowers,categories where borrowers.categorycode=categories.categorycode and category_type='A' and surname like ? order by $orderby";
1055 @bind = ("$searchstring%");
1057 else # advanced search looking in surname, firstname and othernames
1059 @data = split( ' ', $searchstring );
1061 $query = "Select * from borrowers,categories
1062 where ((surname like ? or surname like ?
1063 or firstname like ? or firstname like ?
1064 or othernames like ? or othernames like ?) and borrowers.categorycode=categories.categorycode and category_type='A'
1067 "$data[0]%", "% $data[0]%", "$data[0]%", "% $data[0]%",
1068 "$data[0]%", "% $data[0]%"
1070 for ( my $i = 1 ; $i < $count ; $i++ ) {
1071 $query = $query . " and (" . " surname like ? or surname like ?
1072 or firstname like ? or firstname like ?
1073 or othernames like ? or othernames like ?)";
1075 "$data[$i]%", "% $data[$i]%", "$data[$i]%",
1076 "% $data[$i]%", "$data[$i]%", "% $data[$i]%" );
1080 $query = $query . ") or cardnumber like ?
1082 push( @bind, $searchstring );
1087 my $sth = $dbh->prepare($query);
1088 $sth->execute(@bind);
1090 my $cnt = $sth->rows;
1091 while ( my $data = $sth->fetchrow_hashref ) {
1092 push( @results, $data );
1097 return ( $cnt, \@results );
1101 =item findguarantees
1103 ($num_children, $children_arrayref) = &findguarantees($parent_borrno);
1104 $child0_cardno = $children_arrayref->[0]{"cardnumber"};
1105 $child0_borrno = $children_arrayref->[0]{"borrowernumber"};
1107 C<&findguarantees> takes a borrower number (e.g., that of a patron
1108 with children) and looks up the borrowers who are guaranteed by that
1109 borrower (i.e., the patron's children).
1111 C<&findguarantees> returns two values: an integer giving the number of
1112 borrowers guaranteed by C<$parent_borrno>, and a reference to an array
1113 of references to hash, which gives the actual results.
1119 my $dbh = C4::Context->dbh;
1120 my $sth=$dbh->prepare("select cardnumber,borrowernumber, firstname, surname from borrowers where guarantor=?");
1121 $sth->execute($bornum);
1124 while (my $data = $sth->fetchrow_hashref)
1129 return (scalar(@dat), \@dat);
1134 $guarantor = &findguarantor($borrower_no);
1135 $guarantor_cardno = $guarantor->{"cardnumber"};
1136 $guarantor_surname = $guarantor->{"surname"};
1139 C<&findguarantor> takes a borrower number (presumably that of a child
1140 patron), finds the guarantor for C<$borrower_no> (the child's parent),
1141 and returns the record for the guarantor.
1143 C<&findguarantor> returns a reference-to-hash. Its keys are the fields
1144 from the C<borrowers> database table;
1150 my $dbh = C4::Context->dbh;
1151 my $sth=$dbh->prepare("select guarantor from borrowers where borrowernumber=?");
1152 $sth->execute($bornum);
1153 my $data=$sth->fetchrow_hashref;
1155 $sth=$dbh->prepare("Select * from borrowers where borrowernumber=?");
1156 $sth->execute($data->{'guarantor'});
1157 $data=$sth->fetchrow_hashref;
1162 sub borrowercard_active {
1164 my $dbh = C4::Context->dbh;
1165 my $sth = $dbh->prepare("SELECT expiry FROM borrowers WHERE (borrowernumber = ?) AND (NOW() <= expiry)");
1166 $sth->execute($bornum);
1167 if (my $data=$sth->fetchrow_hashref){
1174 # Search the member photo, in case that photo doesn´t exists, return a default photo.for NEU
1175 sub getMemberPhoto {
1176 my $cardnumber = shift @_;
1177 my $htdocs = C4::Context->config('opacdir');
1178 my $dirname = $htdocs."/htdocs/uploaded-files/users-photo/";
1179 # my $dirname = "$ENV{'DOCUMENT_ROOT'}/uploaded-files/users-photo";
1180 opendir(DIR, $dirname) or die "Can't open directory $dirname: $!";
1181 while (defined(my $file = readdir(DIR))) {
1182 if ($file =~ /^$cardnumber\..+/){
1183 return "/uploaded-files/users-photo/$file";
1187 return "http://cc.neu.edu.tr/stdpictures/".$cardnumber.".jpg";
1190 sub change_user_pass {
1191 my ($uid,$member,$digest) = @_;
1192 my $dbh = C4::Context->dbh;
1193 #Make sure the userid chosen is unique and not theirs if non-empty. If it is not,
1194 #Then we need to tell the user and have them create a new one.
1195 my $sth=$dbh->prepare("select * from borrowers where userid=? and borrowernumber <> ?");
1196 $sth->execute($uid,$member);
1197 if ( ($uid ne '') && ($sth->fetchrow) ) {
1201 #Everything is good so we can update the information.
1202 $sth=$dbh->prepare("update borrowers set userid=?, password=? where borrowernumber=?");
1203 $sth->execute($uid, $digest, $member);
1213 # # A better approach might be to set borrowernumber autoincrement and
1215 sub NewBorrowerNumber {
1216 my $dbh = C4::Context->dbh;
1217 my $sth=$dbh->prepare("Select max(borrowernumber) from borrowers");
1219 my $data=$sth->fetchrow_hashref;
1221 $data->{'max(borrowernumber)'}++;
1222 return($data->{'max(borrowernumber)'});
1225 =head2 ethnicitycategories
1227 ($codes_arrayref, $labels_hashref) = ðnicitycategories();
1229 Looks up the different ethnic types in the database. Returns two
1230 elements: a reference-to-array, which lists the ethnicity codes, and a
1231 reference-to-hash, which maps the ethnicity codes to ethnicity
1238 sub ethnicitycategories {
1239 my $dbh = C4::Context->dbh;
1240 my $sth = $dbh->prepare("Select code,name from ethnicity order by name");
1244 while ( my $data = $sth->fetchrow_hashref ) {
1245 push @codes, $data->{'code'};
1246 $labels{ $data->{'code'} } = $data->{'name'};
1249 return ( \@codes, \%labels );
1254 $ethn_name = &fixEthnicity($ethn_code);
1256 Takes an ethnicity code (e.g., "european" or "pi") and returns the
1257 corresponding descriptive name from the C<ethnicity> table in the
1258 Koha database ("European" or "Pacific Islander").
1264 sub fixEthnicity($) {
1266 my $ethnicity = shift;
1267 my $dbh = C4::Context->dbh;
1268 my $sth = $dbh->prepare("Select name from ethnicity where code = ?");
1269 $sth->execute($ethnicity);
1270 my $data = $sth->fetchrow_hashref;
1272 return $data->{'name'};
1273 } # sub fixEthnicity
1279 $dateofbirth,$date = &get_age($date);
1281 this function return the borrowers age with the value of dateofbirth
1286 my ($date, $date_ref) = @_;
1288 if (not defined $date_ref) {
1289 $date_ref = sprintf('%04d-%02d-%02d', Today());
1292 my ($year1, $month1, $day1) = split /-/, $date;
1293 my ($year2, $month2, $day2) = split /-/, $date_ref;
1295 my $age = $year2 - $year1;
1296 if ($month1.$day1 > $month2.$day2) {
1305 =head2 get_institutions
1306 $insitutions = get_institutions();
1308 Just returns a list of all the borrowers of type I, borrownumber and name
1312 sub get_institutions {
1313 my $dbh = C4::Context->dbh();
1316 "SELECT borrowernumber,surname FROM borrowers WHERE categorycode=? ORDER BY surname"
1320 while ( my $data = $sth->fetchrow_hashref() ) {
1321 $orgs{ $data->{'borrowernumber'} } = $data;
1326 } # sub get_institutions
1328 =head2 add_member_orgs
1330 add_member_orgs($borrowernumber,$borrowernumbers);
1332 Takes a borrowernumber and a list of other borrowernumbers and inserts them into the borrowers_to_borrowers table
1337 sub add_member_orgs {
1338 my ( $borrowernumber, $otherborrowers ) = @_;
1339 my $dbh = C4::Context->dbh();
1341 "INSERT INTO borrowers_to_borrowers (borrower1,borrower2) VALUES (?,?)";
1342 my $sth = $dbh->prepare($query);
1343 foreach my $bornum (@$otherborrowers) {
1344 $sth->execute( $borrowernumber, $bornum );
1348 } # sub add_member_orgs
1350 =head2 GetBorrowersFromSurname
1354 \@resutlts = GetBorrowersFromSurname($surname)
1355 this function get the list of borrower names like $surname.
1357 the table of results in @results
1362 sub GetBorrowersFromSurname {
1363 my ($searchstring)=@_;
1364 my $dbh = C4::Context->dbh;
1365 $searchstring=~ s/\'/\\\'/g;
1366 my @data=split(' ',$searchstring);
1369 SELECT surname,firstname
1371 WHERE (surname like ?)
1374 my $sth=$dbh->prepare($query);
1375 $sth->execute("$data[0]%");
1378 while (my $data=$sth->fetchrow_hashref){
1379 push(@results,$data);
1383 return ($count,\@results);