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;
37 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK);
39 $VERSION = do { my @v = '$Revision$' =~ /\d+/g; shift(@v) . "." . join( "_", map { sprintf "%03d", $_ } @v ); };
43 C4::Members - Perl Module containing convenience functions for member handling
51 This module contains routines for adding, modifying and deleting members/patrons/borrowers
95 &getborrowercategoryinfo
98 &GetBorrowersFromSurname
99 &GetBranchCodeFromBorrowers
100 &GetFlagsAndBranchFromBorrower
105 &expand_sex_into_predicate
110 =head2 borrowercategories
112 ($codes_arrayref, $labels_hashref) = &borrowercategories();
114 Looks up the different types of borrowers in the database. Returns two
115 elements: a reference-to-array, which lists the borrower category
116 codes, and a reference-to-hash, which maps the borrower category codes
117 to category descriptions.
122 sub borrowercategories {
123 my $dbh = C4::Context->dbh;
124 my $sth=$dbh->prepare("Select categorycode,description from categories order by description");
128 while (my $data=$sth->fetchrow_hashref){
129 push @codes,$data->{'categorycode'};
130 $labels{$data->{'categorycode'}}=$data->{'description'};
133 return(\@codes,\%labels);
138 ($count, $borrowers) = &BornameSearch($env, $searchstring, $type);
140 Looks up patrons (borrowers) by name.
144 BUGFIX 499: C<$type> is now used to determine type of search.
145 if $type is "simple", search is performed on the first letter of the
148 C<$searchstring> is a space-separated list of search terms. Each term
149 must match the beginning a borrower's surname, first name, or other
152 C<&BornameSearch> returns a two-element list. C<$borrowers> is a
153 reference-to-array; each element is a reference-to-hash, whose keys
154 are the fields of the C<borrowers> table in the Koha database.
155 C<$count> is the number of elements in C<$borrowers>.
159 #used by member enquiries from the intranet
162 my ($env,$searchstring,$orderby,$type)=@_;
163 my $dbh = C4::Context->dbh;
164 my $query = ""; my $count;
168 if($type eq "simple") # simple search for one letter only
170 $query="Select * from borrowers where surname like '$searchstring%' order by $orderby";
171 # @bind=("$searchstring%");
173 else # advanced search looking in surname, firstname and othernames
175 ### Try to determine whether numeric like cardnumber
176 if ($searchstring+1>1) {
177 $query="Select * from borrowers where cardnumber like '$searchstring%' ";
181 my @words=split / /,$searchstring;
182 foreach my $word(@words){
186 $searchstring=join " ",@words;
188 $query="Select * from borrowers where MATCH(surname,firstname,othernames) AGAINST('$searchstring' in boolean mode)";
191 $query=$query." order by $orderby";
194 my $sth=$dbh->prepare($query);
195 # warn "Q $orderby : $query";
199 while (my $data=$sth->fetchrow_hashref){
200 push(@results,$data);
204 return ($cnt,\@results);
206 =head2 getpatroninformation
208 ($borrower, $flags) = &getpatroninformation($env, $borrowernumber, $cardnumber);
209 Looks up a patron and returns information about him or her. If
210 C<$borrowernumber> is true (nonzero), C<&getpatroninformation> looks
211 up the borrower by number; otherwise, it looks up the borrower by card
213 C<$env> is effectively ignored, but should be a reference-to-hash.
214 C<$borrower> is a reference-to-hash whose keys are the fields of the
215 borrowers table in the Koha database. In addition,
216 C<$borrower-E<gt>{flags}> is a hash giving more detailed information
217 about the patron. Its keys act as flags :
219 if $borrower->{flags}->{LOST} {
220 # Patron's card was reported lost
223 Each flag has a C<message> key, giving a human-readable explanation of
224 the flag. If the state of a flag means that the patron should not be
225 allowed to borrow any more books, then it will have a C<noissues> key
228 The possible flags are:
234 Shows the patron's credit or debt, if any.
242 (Gone, no address.) Set if the patron has left without giving a
251 Set if the patron's card has been reported as lost.
259 Set if the patron has been debarred.
267 Any additional notes about the patron.
275 Set if the patron has overdue items. This flag has several keys:
277 C<$flags-E<gt>{ODUES}{itemlist}> is a reference-to-array listing the
278 overdue items. Its elements are references-to-hash, each describing an
279 overdue item. The keys are selected fields from the issues, biblio,
280 biblioitems, and items tables of the Koha database.
282 C<$flags-E<gt>{ODUES}{itemlist}> is a string giving a text listing of
283 the overdue items, one per line.
291 Set if any items that the patron has reserved are available.
293 C<$flags-E<gt>{WAITING}{itemlist}> is a reference-to-array listing the
294 available items. Each element is a reference-to-hash whose keys are
295 fields from the reserves table of the Koha database.
303 sub getpatroninformation {
305 my ($env, $borrowernumber,$cardnumber) = @_;
306 my $dbh = C4::Context->dbh;
309 if ($borrowernumber) {
310 $sth = $dbh->prepare("select * from borrowers where borrowernumber=?");
311 $sth->execute($borrowernumber);
312 } elsif ($cardnumber) {
313 $sth = $dbh->prepare("select * from borrowers where cardnumber=?");
314 $sth->execute($cardnumber);
316 $env->{'apierror'} = "invalid borrower information passed to getpatroninformation subroutine";
319 my $borrower = $sth->fetchrow_hashref;
320 my $amount = C4::Accounts2::checkaccount($env, $borrowernumber, $dbh);
321 $borrower->{'amountoutstanding'} = $amount;
322 my $flags = C4::Circulation::Circ2::patronflags($env, $borrower, $dbh);
325 $sth=$dbh->prepare("select bit,flag from userflags");
327 while (my ($bit, $flag) = $sth->fetchrow) {
328 if ($borrower->{'flags'} & 2**$bit) {
329 $accessflagshash->{$flag}=1;
333 $borrower->{'flags'}=$flags;
334 $borrower->{'authflags'} = $accessflagshash;
335 return ($borrower); #, $flags, $accessflagshash);
340 $borrower = &getmember($cardnumber, $borrowernumber);
342 Looks up information about a patron (borrower) by either card number
343 or borrower number. If $borrowernumber is specified, C<&borrdata>
344 searches by borrower number; otherwise, it searches by card number.
346 C<&getmember> returns a reference-to-hash whose keys are the fields of
347 the C<borrowers> table in the Koha database.
351 =head3 GetFlagsAndBranchFromBorrower
355 ($flags, $homebranch) = GetFlagsAndBranchFromBorrower($loggedinuser);
357 this function read on the database to get flags and homebranch for a user
361 it returns the $flags & the homebranch in scalar context.
371 ($count, $issues) = &borrissues($borrowernumber);
373 Looks up what the patron with the given borrowernumber has borrowed.
375 C<&borrissues> returns a two-element array. C<$issues> is a
376 reference-to-array, where each element is a reference-to-hash; the
377 keys are the fields from the C<issues>, C<biblio>, and C<items> tables
378 in the Koha database. C<$count> is the number of elements in
387 my $dbh = C4::Context->dbh;
388 my $sth=$dbh->prepare("Select * from issues,biblio,items where borrowernumber=?
389 and items.itemnumber=issues.itemnumber
390 and items.biblionumber=biblio.biblionumber
391 and issues.returndate is NULL order by date_due");
392 $sth->execute($bornum);
394 while (my $data = $sth->fetchrow_hashref) {
398 return(scalar(@result), \@result);
403 ($count, $issues) = &allissues($borrowernumber, $sortkey, $limit);
405 Looks up what the patron with the given borrowernumber has borrowed,
406 and sorts the results.
408 C<$sortkey> is the name of a field on which to sort the results. This
409 should be the name of a field in the C<issues>, C<biblio>,
410 C<biblioitems>, or C<items> table in the Koha database.
412 C<$limit> is the maximum number of results to return.
414 C<&allissues> returns a two-element array. C<$issues> is a
415 reference-to-array, where each element is a reference-to-hash; the
416 keys are the fields from the C<issues>, C<biblio>, C<biblioitems>, and
417 C<items> tables of the Koha database. C<$count> is the number of
418 elements in C<$issues>
423 my ($bornum,$order,$limit)=@_;
424 #FIXME: sanity-check order and limit
425 my $dbh = C4::Context->dbh;
426 my $query="Select * from issues,biblio,items
427 where borrowernumber=? and
428 items.itemnumber=issues.itemnumber and
429 items.biblionumber=biblio.biblionumber order by $order";
431 $query.=" limit $limit";
434 my $sth=$dbh->prepare($query);
435 $sth->execute($bornum);
438 while (my $data=$sth->fetchrow_hashref){
448 ## NEU specific. used in Reserve section issues
449 my ($env,$bornum)=@_;
450 my $dbh = C4::Context->dbh;
451 my $query="Select count(*) from reserveissue as r where r.borrowernumber='$bornum'
452 and rettime is null";
454 my $sth=$dbh->prepare($query);
456 my $data=$sth->fetchrow_hashref;
458 $sth=$dbh->prepare("Select count(*),timediff(now(), duetime ) as elapsed, hour(timediff(now(), duetime )) as hours, MINUTE(timediff(now(), duetime )) as min from
459 reserveissue as r where r.borrowernumber='$bornum' and rettime is null and duetime< now() group by r.borrowernumber");
462 my $data2=$sth->fetchrow_hashref;
464 my $rescharge=C4::Context->preference('resmaterialcharge');
468 if ($data2->{'elapsed'}>0){
469 $resfine=($data2->{'hours'}+$data2->{'min'}/60)*$rescharge;
470 $resfine=sprintf ("%.1f",$resfine);
473 $sth=$dbh->prepare("Select sum(amountoutstanding) from accountlines where
474 borrowernumber='$bornum'");
476 my $data3=$sth->fetchrow_hashref;
480 return($data2->{'count(*)'},$data->{'count(*)'},$data3->{'sum(amountoutstanding)'},$resfine);
482 =item getboracctrecord
484 ($count, $acctlines, $total) = &getboracctrecord($env, $borrowernumber);
486 Looks up accounting data for the patron with the given borrowernumber.
491 C<&getboracctrecord> returns a three-element array. C<$acctlines> is a
492 reference-to-array, where each element is a reference-to-hash; the
493 keys are the fields of the C<accountlines> table in the Koha database.
494 C<$count> is the number of elements in C<$acctlines>. C<$total> is the
495 total amount outstanding for all of the account lines.
499 sub getboracctrecord {
500 my ($env,$params) = @_;
501 my $dbh = C4::Context->dbh;
504 my $sth=$dbh->prepare("Select * from accountlines where
505 borrowernumber=? order by date desc,timestamp desc");
507 $sth->execute($params->{'borrowernumber'});
509 while (my $data=$sth->fetchrow_hashref){
510 $acctlines[$numlines] = $data;
512 $total += $data->{'amountoutstanding'};
515 return ($numlines,\@acctlines,$total);
518 sub getborrowercategory{
520 my $dbh = C4::Context->dbh;
521 my $sth = $dbh->prepare("SELECT description FROM categories WHERE categorycode = ?");
522 $sth->execute($catcode);
523 my $description = $sth->fetchrow();
526 } # sub getborrowercategory
528 sub getborrowercategoryinfo{
530 my $dbh = C4::Context->dbh;
531 my $sth = $dbh->prepare("SELECT * FROM categories WHERE categorycode = ?");
532 $sth->execute($catcode);
533 my $category = $sth->fetchrow_hashref;
536 } # sub getborrowercategoryinfo
539 sub GetFlagsAndBranchFromBorrower {
540 my $loggedinuser = @_;
541 my $dbh = C4::Context->dbh;
543 SELECT flags, branchcode
545 WHERE borrowernumber = ?
547 my $sth = $dbh->prepare($query);
548 $sth->execute($loggedinuser);
550 return $sth->fetchrow;
555 my ( $cardnumber, $bornum ) = @_;
556 $cardnumber = uc $cardnumber;
557 my $dbh = C4::Context->dbh;
559 if ( $bornum eq '' ) {
560 $sth = $dbh->prepare("Select * from borrowers where cardnumber=?");
561 $sth->execute($cardnumber);
563 $sth = $dbh->prepare("Select * from borrowers where borrowernumber=?");
564 $sth->execute($bornum);
566 my $data = $sth->fetchrow_hashref;
571 else { # try with firstname
574 $dbh->prepare("select * from borrowers where firstname=?");
575 $sth->execute($cardnumber);
576 my $data = $sth->fetchrow_hashref;
586 $borrower = &borrdata($cardnumber, $borrowernumber);
588 Looks up information about a patron (borrower) by either card number
589 or borrower number. If $borrowernumber is specified, C<&borrdata>
590 searches by borrower number; otherwise, it searches by card number.
592 C<&borrdata> returns a reference-to-hash whose keys are the fields of
593 the C<borrowers> table in the Koha database.
599 my ( $cardnumber, $bornum ) = @_;
600 $cardnumber = uc $cardnumber;
601 my $dbh = C4::Context->dbh;
603 if ( $bornum eq '' ) {
606 "Select borrowers.*,categories.category_type from borrowers left join categories on borrowers.categorycode=categories.categorycode where cardnumber=?"
608 $sth->execute($cardnumber);
613 "Select borrowers.*,categories.category_type from borrowers left join categories on borrowers.categorycode=categories.categorycode where borrowernumber=?"
615 $sth->execute($bornum);
617 my $data = $sth->fetchrow_hashref;
618 # warn "DATA" . $data->{category_type};
623 else { # try with firstname
627 "Select borrowers.*,categories.category_type from borrowers left join categories on borrowers.categorycode=categories.categorycode where firstname=?"
629 $sth->execute($cardnumber);
630 my $data = $sth->fetchrow_hashref;
640 ($borrowed, $due, $fine) = &borrdata2($env, $borrowernumber);
642 Returns aggregate data about items borrowed by the patron with the
643 given borrowernumber.
647 C<&borrdata2> returns a three-element array. C<$borrowed> is the
648 number of books the patron currently has borrowed. C<$due> is the
649 number of overdue items the patron currently has borrowed. C<$fine> is
650 the total fine currently due by the borrower.
656 my ( $env, $bornum ) = @_;
657 my $dbh = C4::Context->dbh;
658 my $query = "Select count(*) from issues where borrowernumber='$bornum' and
662 my $sth = $dbh->prepare($query);
664 my $data = $sth->fetchrow_hashref;
666 $sth = $dbh->prepare(
667 "Select count(*) from issues where
668 borrowernumber='$bornum' and date_due < now() and returndate is NULL"
671 my $data2 = $sth->fetchrow_hashref;
673 $sth = $dbh->prepare(
674 "Select sum(amountoutstanding) from accountlines where
675 borrowernumber='$bornum'"
678 my $data3 = $sth->fetchrow_hashref;
681 return ( $data2->{'count(*)'}, $data->{'count(*)'},
682 $data3->{'sum(amountoutstanding)'} );
687 my $dbh = C4::Context->dbh;
688 $data{'dateofbirth'}=format_date_in_iso($data{'dateofbirth'});
691 $data{'joining'}=format_date_in_iso($data{'joining'});
693 if ($data{'expiry'} eq '') {
695 my $sth = $dbh->prepare("select enrolmentperiod from categories where categorycode=?");
696 $sth->execute($data{'categorycode'});
697 my ($enrolmentperiod) = $sth->fetchrow;
698 $enrolmentperiod = 12 unless ($enrolmentperiod);
699 $data{'expiry'} = &DateCalc($data{'joining'},"$enrolmentperiod years");
701 $data{'expiry'}=format_date_in_iso($data{'expiry'});
702 my $query= "UPDATE borrowers SET
703 cardnumber = '$data{'cardnumber'}' ,
704 surname = '$data{'surname'}' ,
705 firstname = '$data{'firstname'}' ,
706 title = '$data{'title'}' ,
707 initials = '$data{'initials'}' ,
708 dateofbirth = '$data{'dateofbirth'}' ,
709 sex = '$data{'sex'}' ,
710 streetaddress = '$data{'streetaddress'}' ,
711 streetcity = '$data{'streetcity'}' ,
712 zipcode = '$data{'zipcode'}' ,
713 phoneday = '$data{'phoneday'}' ,
714 physstreet = '$data{'physstreet'}' ,
715 city = '$data{'city'}' ,
716 homezipcode = '$data{'homezipcode'}' ,
717 phone = '$data{'phone'}' ,
718 emailaddress = '$data{'emailaddress'}' ,
719 faxnumber = '$data{'faxnumber'}' ,
720 textmessaging = '$data{'textmessaging'}' ,
721 categorycode = '$data{'categorycode'}' ,
722 branchcode = '$data{'branchcode'}' ,
723 borrowernotes = '$data{'borrowernotes'}' ,
724 ethnicity = '$data{'ethnicity'}' ,
725 ethnotes = '$data{'ethnotes'}' ,
726 expiry = '$data{'expiry'}' ,
727 dateenrolled = '$data{'joining'}' ,
728 sort1 = '$data{'sort1'}' ,
729 sort2 = '$data{'sort2'}' ,
730 debarred = '$data{'debarred'}' ,
731 lost = '$data{'lost'}' ,
732 gonenoaddress = '$data{'gna'}'
733 WHERE borrowernumber = $data{'borrowernumber'}";
734 my $sth = $dbh->prepare($query);
737 # ok if its an adult (type) it may have borrowers that depend on it as a guarantor
738 # so when we update information for an adult we should check for guarantees and update the relevant part
739 # of their records, ie addresses and phone numbers
740 if ($data{'categorycode'} eq 'A' || $data{'categorycode'} eq 'W'){
741 # is adult check guarantees;
742 updateguarantees(%data);
748 my $dbh = C4::Context->dbh;
749 $data{'dateofbirth'}=format_date_in_iso($data{'dateofbirth'});
750 $data{'joining'} = &ParseDate("today") unless $data{'joining'};
751 $data{'joining'}=format_date_in_iso($data{'joining'});
752 # if expirydate is not set, calculate it from borrower category subscription duration
753 unless ($data{'expiry'}) {
754 my $sth = $dbh->prepare("select enrolmentperiod from categories where categorycode=?");
755 $sth->execute($data{'categorycode'});
756 my ($enrolmentperiod) = $sth->fetchrow;
757 $enrolmentperiod = 12 unless ($enrolmentperiod);
758 $data{'expiry'} = &DateCalc($data{'joining'},"$enrolmentperiod years");
760 $data{'expiry'}=format_date_in_iso($data{'expiry'});
761 my $query= "INSERT INTO borrowers (
791 '$data{'cardnumber'}',
793 '$data{'firstname'}',
796 '$data{'dateofbirth'}',
799 '$data{'streetaddress'}',
800 '$data{'streetcity'}',
804 '$data{'physstreet'}',
806 '$data{'homezipcode'}',
809 '$data{'emailaddress'}',
810 '$data{'faxnumber'}',
811 '$data{'textmessaging'}',
813 '$data{'categorycode'}',
814 '$data{'branchcode'}',
815 '$data{'borrowernotes'}',
816 '$data{'ethnicity'}',
823 my $sth=$dbh->prepare($query);
826 $data{'bornum'} =$dbh->{'mysql_insertid'};
827 return $data{'bornum'};
831 my ( $categorycode, $dateenrolled ) = @_;
832 my $dbh = C4::Context->dbh;
835 "select enrolmentperiod from categories where categorycode=?");
836 $sth->execute($categorycode);
837 my ($enrolmentperiod) = $sth->fetchrow;
838 $enrolmentperiod = 12 unless ($enrolmentperiod);
839 return format_date_in_iso(
840 &DateCalc( $dateenrolled, "$enrolmentperiod months" ) );
843 =head2 checkuserpassword (OUEST-PROVENCE)
845 check for the password and login are not used
846 return the number of record
847 0=> NOT USED 1=> USED
851 sub checkuserpassword {
852 my ( $borrowernumber, $userid, $password ) = @_;
853 $password = md5_base64($password);
854 my $dbh = C4::Context->dbh;
857 "Select count(*) from borrowers where borrowernumber !=? and userid =? and password=? "
859 $sth->execute( $borrowernumber, $userid, $password );
860 my $number_rows = $sth->fetchrow;
864 sub getmemberfromuserid {
866 my $dbh = C4::Context->dbh;
867 my $sth = $dbh->prepare("select * from borrowers where userid=?");
868 $sth->execute($userid);
869 return $sth->fetchrow_hashref;
871 sub updateguarantees {
873 my $dbh = C4::Context->dbh;
874 my ( $count, $guarantees ) = findguarantees( $data{'borrowernumber'} );
875 for ( my $i = 0 ; $i < $count ; $i++ ) {
878 # It looks like the $i is only being returned to handle walking through
879 # the array, which is probably better done as a foreach loop.
882 "update borrowers set streetaddress='$data{'address'}',faxnumber='$data{'faxnumber'}',
883 streetcity='$data{'streetcity'}',phoneday='$data{'phoneday'}',city='$data{'city'}',area='$data{'area'}',phone='$data{'phone'}'
884 ,streetaddress='$data{'address'}'
885 where borrowernumber='$guarantees->[$i]->{'borrowernumber'}'";
886 my $sth3 = $dbh->prepare($guaquery);
891 ################################################################################
893 =item fixup_cardnumber
895 Warning: The caller is responsible for locking the members table in write
896 mode, to avoid database corruption.
900 use vars qw( @weightings );
901 my @weightings = ( 8, 4, 6, 3, 5, 2, 1 );
903 sub fixup_cardnumber ($) {
904 my ($cardnumber) = @_;
905 my $autonumber_members = C4::Context->boolean_preference('autoMemberNum');
906 $autonumber_members = 0 unless defined $autonumber_members;
908 # Find out whether member numbers should be generated
909 # automatically. Should be either "1" or something else.
910 # Defaults to "0", which is interpreted as "no".
912 # if ($cardnumber !~ /\S/ && $autonumber_members) {
913 if ($autonumber_members) {
914 my $dbh = C4::Context->dbh;
915 if ( C4::Context->preference('checkdigit') eq 'katipo' ) {
917 # if checkdigit is selected, calculate katipo-style cardnumber.
918 # otherwise, just use the max()
919 # purpose: generate checksum'd member numbers.
920 # We'll assume we just got the max value of digits 2-8 of member #'s
921 # from the database and our job is to increment that by one,
922 # determine the 1st and 9th digits and return the full string.
925 "select max(substring(borrowers.cardnumber,2,7)) from borrowers"
929 my $data = $sth->fetchrow_hashref;
930 $cardnumber = $data->{'max(substring(borrowers.cardnumber,2,7))'};
933 if ( !$cardnumber ) { # If DB has no values,
934 $cardnumber = 1000000; # start at 1000000
940 for ( my $i = 0 ; $i < 8 ; $i += 1 ) {
942 # read weightings, left to right, 1 char at a time
943 my $temp1 = $weightings[$i];
945 # sequence left to right, 1 char at a time
946 my $temp2 = substr( $cardnumber, $i, 1 );
948 # mult each char 1-7 by its corresponding weighting
949 $sum += $temp1 * $temp2;
952 $rem = ( $sum % 11 );
953 $rem = 'X' if $rem == 10;
955 $cardnumber = "V$cardnumber$rem";
959 # MODIFIED BY JF: mysql4.1 allows casting as an integer, which is probably
960 # better. I'll leave the original in in case it needs to be changed for you
963 "select max(cast(cardnumber as signed)) from borrowers");
965 #my $sth=$dbh->prepare("select max(borrowers.cardnumber) from borrowers");
969 $cardnumber="V$cardnumber$rem";
974 sub fixupneu_cardnumber{
975 my($cardnumber,$categorycode) = @_;
976 my $autonumber_members = C4::Context->boolean_preference('autoMemberNum');
977 $autonumber_members = 0 unless defined $autonumber_members;
978 # Find out whether member numbers should be generated
979 # automatically. Should be either "1" or something else.
980 # Defaults to "0", which is interpreted as "no".
981 my $dbh = C4::Context->dbh;
983 if (! $cardnumber && $autonumber_members && $categorycode) {
984 if ($categorycode eq "A" || $categorycode eq "W" || $categorycode eq "C"){
985 $sth=$dbh->prepare("select max(borrowers.cardnumber) from borrowers where borrowers.cardnumber like '5%' ");
986 }elsif ($categorycode eq "L"){
987 $sth=$dbh->prepare("select max(borrowers.cardnumber) from borrowers where borrowers.cardnumber like '10%' ");
988 }elsif ($categorycode eq "F" || $categorycode eq "E") {
989 $sth=$dbh->prepare("select max(borrowers.cardnumber) from borrowers where borrowers.cardnumber like '30%' ");
990 }elsif ($categorycode eq "N"){
991 $sth=$dbh->prepare("select max(borrowers.cardnumber) from borrowers where borrowers.cardnumber like '40%' ");
993 $sth=$dbh->prepare("select max(borrowers.cardnumber) from borrowers where borrowers.cardnumber like '6%' ");
997 my $data=$sth->fetchrow_hashref;
998 $cardnumber=$data->{'max(borrowers.cardnumber)'};
1001 # purpose: generate checksum'd member numbers.
1002 # We'll assume we just got the max value of digits 2-8 of member #'s
1003 # from the database and our job is to increment that by one,
1004 # determine the 1st and 9th digits and return the full string.
1006 if (! $cardnumber) { # If DB has no values,
1007 if ($categorycode eq "A" || $categorycode eq "W" || $categorycode eq "C"){ $cardnumber = 5000000;}
1008 elsif ($categorycode eq "L"){ $cardnumber = 1000000;}
1009 elsif ($categorycode eq "F"){ $cardnumber = 3000000;}
1010 else{$cardnumber = 6000000;}
1011 # start at 1000000 or 3000000 or 5000000
1021 =item GuarantornameSearch
1023 ($count, $borrowers) = &GuarantornameSearch($env, $searchstring, $type);
1025 Looks up guarantor by name.
1029 BUGFIX 499: C<$type> is now used to determine type of search.
1030 if $type is "simple", search is performed on the first letter of the
1033 C<$searchstring> is a space-separated list of search terms. Each term
1034 must match the beginning a borrower's surname, first name, or other
1037 C<&GuarantornameSearch> returns a two-element list. C<$borrowers> is a
1038 reference-to-array; each element is a reference-to-hash, whose keys
1039 are the fields of the C<borrowers> table in the Koha database.
1040 C<$count> is the number of elements in C<$borrowers>.
1042 return all info from guarantor =>only category_type A
1047 #used by member enquiries from the intranet
1048 #called by guarantor_search.pl
1049 sub GuarantornameSearch {
1050 my ( $env, $searchstring, $orderby, $type ) = @_;
1051 my $dbh = C4::Context->dbh;
1057 if ( $type eq "simple" ) # simple search for one letter only
1060 "Select * from borrowers,categories where borrowers.categorycode=categories.categorycode and category_type='A' and surname like ? order by $orderby";
1061 @bind = ("$searchstring%");
1063 else # advanced search looking in surname, firstname and othernames
1065 @data = split( ' ', $searchstring );
1067 $query = "Select * from borrowers,categories
1068 where ((surname like ? or surname like ?
1069 or firstname like ? or firstname like ?
1070 or othernames like ? or othernames like ?) and borrowers.categorycode=categories.categorycode and category_type='A'
1073 "$data[0]%", "% $data[0]%", "$data[0]%", "% $data[0]%",
1074 "$data[0]%", "% $data[0]%"
1076 for ( my $i = 1 ; $i < $count ; $i++ ) {
1077 $query = $query . " and (" . " surname like ? or surname like ?
1078 or firstname like ? or firstname like ?
1079 or othernames like ? or othernames like ?)";
1081 "$data[$i]%", "% $data[$i]%", "$data[$i]%",
1082 "% $data[$i]%", "$data[$i]%", "% $data[$i]%" );
1086 $query = $query . ") or cardnumber like ?
1088 push( @bind, $searchstring );
1093 my $sth = $dbh->prepare($query);
1094 $sth->execute(@bind);
1096 my $cnt = $sth->rows;
1097 while ( my $data = $sth->fetchrow_hashref ) {
1098 push( @results, $data );
1103 return ( $cnt, \@results );
1107 =item findguarantees
1109 ($num_children, $children_arrayref) = &findguarantees($parent_borrno);
1110 $child0_cardno = $children_arrayref->[0]{"cardnumber"};
1111 $child0_borrno = $children_arrayref->[0]{"borrowernumber"};
1113 C<&findguarantees> takes a borrower number (e.g., that of a patron
1114 with children) and looks up the borrowers who are guaranteed by that
1115 borrower (i.e., the patron's children).
1117 C<&findguarantees> returns two values: an integer giving the number of
1118 borrowers guaranteed by C<$parent_borrno>, and a reference to an array
1119 of references to hash, which gives the actual results.
1125 my $dbh = C4::Context->dbh;
1126 my $sth=$dbh->prepare("select cardnumber,borrowernumber, firstname, surname from borrowers where guarantor=?");
1127 $sth->execute($bornum);
1130 while (my $data = $sth->fetchrow_hashref)
1135 return (scalar(@dat), \@dat);
1140 $guarantor = &findguarantor($borrower_no);
1141 $guarantor_cardno = $guarantor->{"cardnumber"};
1142 $guarantor_surname = $guarantor->{"surname"};
1145 C<&findguarantor> takes a borrower number (presumably that of a child
1146 patron), finds the guarantor for C<$borrower_no> (the child's parent),
1147 and returns the record for the guarantor.
1149 C<&findguarantor> returns a reference-to-hash. Its keys are the fields
1150 from the C<borrowers> database table;
1156 my $dbh = C4::Context->dbh;
1157 my $sth=$dbh->prepare("select guarantor from borrowers where borrowernumber=?");
1158 $sth->execute($bornum);
1159 my $data=$sth->fetchrow_hashref;
1161 $sth=$dbh->prepare("Select * from borrowers where borrowernumber=?");
1162 $sth->execute($data->{'guarantor'});
1163 $data=$sth->fetchrow_hashref;
1168 sub borrowercard_active {
1170 my $dbh = C4::Context->dbh;
1171 my $sth = $dbh->prepare("SELECT expiry FROM borrowers WHERE (borrowernumber = ?) AND (NOW() <= expiry)");
1172 $sth->execute($bornum);
1173 if (my $data=$sth->fetchrow_hashref){
1180 # Search the member photo, in case that photo doesn´t exists, return a default photo.for NEU
1181 sub getMemberPhoto {
1182 my $cardnumber = shift @_;
1183 my $htdocs = C4::Context->config('opacdir');
1184 my $dirname = $htdocs."/htdocs/uploaded-files/users-photo/";
1185 # my $dirname = "$ENV{'DOCUMENT_ROOT'}/uploaded-files/users-photo";
1186 opendir(DIR, $dirname) or die "Can't open directory $dirname: $!";
1187 while (defined(my $file = readdir(DIR))) {
1188 if ($file =~ /^$cardnumber\..+/){
1189 return "/uploaded-files/users-photo/$file";
1193 return "http://cc.neu.edu.tr/stdpictures/".$cardnumber.".jpg";
1196 sub change_user_pass {
1197 my ($uid,$member,$digest) = @_;
1198 my $dbh = C4::Context->dbh;
1199 #Make sure the userid chosen is unique and not theirs if non-empty. If it is not,
1200 #Then we need to tell the user and have them create a new one.
1201 my $sth=$dbh->prepare("select * from borrowers where userid=? and borrowernumber <> ?");
1202 $sth->execute($uid,$member);
1203 if ( ($uid ne '') && ($sth->fetchrow) ) {
1207 #Everything is good so we can update the information.
1208 $sth=$dbh->prepare("update borrowers set userid=?, password=? where borrowernumber=?");
1209 $sth->execute($uid, $digest, $member);
1219 # # A better approach might be to set borrowernumber autoincrement and
1221 sub NewBorrowerNumber {
1222 my $dbh = C4::Context->dbh;
1223 my $sth=$dbh->prepare("Select max(borrowernumber) from borrowers");
1225 my $data=$sth->fetchrow_hashref;
1227 $data->{'max(borrowernumber)'}++;
1228 return($data->{'max(borrowernumber)'});
1231 =head2 ethnicitycategories
1233 ($codes_arrayref, $labels_hashref) = ðnicitycategories();
1235 Looks up the different ethnic types in the database. Returns two
1236 elements: a reference-to-array, which lists the ethnicity codes, and a
1237 reference-to-hash, which maps the ethnicity codes to ethnicity
1244 sub ethnicitycategories {
1245 my $dbh = C4::Context->dbh;
1246 my $sth = $dbh->prepare("Select code,name from ethnicity order by name");
1250 while ( my $data = $sth->fetchrow_hashref ) {
1251 push @codes, $data->{'code'};
1252 $labels{ $data->{'code'} } = $data->{'name'};
1255 return ( \@codes, \%labels );
1260 $ethn_name = &fixEthnicity($ethn_code);
1262 Takes an ethnicity code (e.g., "european" or "pi") and returns the
1263 corresponding descriptive name from the C<ethnicity> table in the
1264 Koha database ("European" or "Pacific Islander").
1270 sub fixEthnicity($) {
1272 my $ethnicity = shift;
1273 my $dbh = C4::Context->dbh;
1274 my $sth = $dbh->prepare("Select name from ethnicity where code = ?");
1275 $sth->execute($ethnicity);
1276 my $data = $sth->fetchrow_hashref;
1278 return $data->{'name'};
1279 } # sub fixEthnicity
1285 $dateofbirth,$date = &get_age($date);
1287 this function return the borrowers age with the value of dateofbirth
1292 my ($date, $date_ref) = @_;
1294 if (not defined $date_ref) {
1295 $date_ref = sprintf('%04d-%02d-%02d', Today());
1298 my ($year1, $month1, $day1) = split /-/, $date;
1299 my ($year2, $month2, $day2) = split /-/, $date_ref;
1301 my $age = $year2 - $year1;
1302 if ($month1.$day1 > $month2.$day2) {
1311 =head2 get_institutions
1312 $insitutions = get_institutions();
1314 Just returns a list of all the borrowers of type I, borrownumber and name
1318 sub get_institutions {
1319 my $dbh = C4::Context->dbh();
1322 "SELECT borrowernumber,surname FROM borrowers WHERE categorycode=? ORDER BY surname"
1326 while ( my $data = $sth->fetchrow_hashref() ) {
1327 $orgs{ $data->{'borrowernumber'} } = $data;
1332 } # sub get_institutions
1334 =head2 add_member_orgs
1336 add_member_orgs($borrowernumber,$borrowernumbers);
1338 Takes a borrowernumber and a list of other borrowernumbers and inserts them into the borrowers_to_borrowers table
1343 sub add_member_orgs {
1344 my ( $borrowernumber, $otherborrowers ) = @_;
1345 my $dbh = C4::Context->dbh();
1347 "INSERT INTO borrowers_to_borrowers (borrower1,borrower2) VALUES (?,?)";
1348 my $sth = $dbh->prepare($query);
1349 foreach my $bornum (@$otherborrowers) {
1350 $sth->execute( $borrowernumber, $bornum );
1354 } # sub add_member_orgs
1356 =head2 GetBorrowersFromSurname
1360 \@resutlts = GetBorrowersFromSurname($surname)
1361 this function get the list of borrower names like $surname.
1363 the table of results in @results
1368 sub GetBorrowersFromSurname {
1369 my ($searchstring)=@_;
1370 my $dbh = C4::Context->dbh;
1371 $searchstring=~ s/\'/\\\'/g;
1372 my @data=split(' ',$searchstring);
1375 SELECT surname,firstname
1377 WHERE (surname like ?)
1380 my $sth=$dbh->prepare($query);
1381 $sth->execute("$data[0]%");
1384 while (my $data=$sth->fetchrow_hashref){
1385 push(@results,$data);
1389 return ($count,\@results);
1392 =head2 expand_sex_into_predicate
1394 $data{&expand_sex_into_predicate($data{sex})} = 1;
1396 Converts a single 'M' or 'F' into 'sex_M_p' or 'sex_F_p'
1399 In some languages, 'M' and 'F' are not appropriate. However,
1400 with HTML::Template, there is no way to localize 'M' or 'F'
1401 unless these are converted into variables that TMPL_IF can
1402 understand. This function provides this conversion.
1406 sub expand_sex_into_predicate ($) {
1408 return "sex_${sex}_p";
1409 } # expand_sex_into_predicate