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);
34 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
94 &getborrowercategoryinfo
97 &GetBorrowersFromSurname
98 &GetBranchCodeFromBorrowers
99 &GetFlagsAndBranchFromBorrower
104 &expand_sex_into_predicate
109 =head2 borrowercategories
111 ($codes_arrayref, $labels_hashref) = &borrowercategories();
113 Looks up the different types of borrowers in the database. Returns two
114 elements: a reference-to-array, which lists the borrower category
115 codes, and a reference-to-hash, which maps the borrower category codes
116 to category descriptions.
121 sub borrowercategories {
122 my $dbh = C4::Context->dbh;
123 my $sth=$dbh->prepare("Select categorycode,description from categories order by description");
127 while (my $data=$sth->fetchrow_hashref){
128 push @codes,$data->{'categorycode'};
129 $labels{$data->{'categorycode'}}=$data->{'description'};
132 return(\@codes,\%labels);
137 ($count, $borrowers) = &BornameSearch($env, $searchstring, $type);
139 Looks up patrons (borrowers) by name.
143 BUGFIX 499: C<$type> is now used to determine type of search.
144 if $type is "simple", search is performed on the first letter of the
147 C<$searchstring> is a space-separated list of search terms. Each term
148 must match the beginning a borrower's surname, first name, or other
151 C<&BornameSearch> returns a two-element list. C<$borrowers> is a
152 reference-to-array; each element is a reference-to-hash, whose keys
153 are the fields of the C<borrowers> table in the Koha database.
154 C<$count> is the number of elements in C<$borrowers>.
158 #used by member enquiries from the intranet
161 my ($env,$searchstring,$orderby,$type)=@_;
162 my $dbh = C4::Context->dbh;
163 my $query = ""; my $count;
167 if($type eq "simple") # simple search for one letter only
169 $query="Select * from borrowers where surname like '$searchstring%' order by $orderby";
170 # @bind=("$searchstring%");
172 else # advanced search looking in surname, firstname and othernames
174 ### Try to determine whether numeric like cardnumber
175 if ($searchstring+1>1) {
176 $query="Select * from borrowers where cardnumber like '$searchstring%' ";
180 my @words=split / /,$searchstring;
181 foreach my $word(@words){
185 $searchstring=join " ",@words;
187 $query="Select * from borrowers where MATCH(surname,firstname,othernames) AGAINST('$searchstring' in boolean mode)";
190 $query=$query." order by $orderby";
193 my $sth=$dbh->prepare($query);
194 # warn "Q $orderby : $query";
198 while (my $data=$sth->fetchrow_hashref){
199 push(@results,$data);
203 return ($cnt,\@results);
205 =head2 getpatroninformation
207 ($borrower, $flags) = &getpatroninformation($env, $borrowernumber, $cardnumber);
208 Looks up a patron and returns information about him or her. If
209 C<$borrowernumber> is true (nonzero), C<&getpatroninformation> looks
210 up the borrower by number; otherwise, it looks up the borrower by card
212 C<$env> is effectively ignored, but should be a reference-to-hash.
213 C<$borrower> is a reference-to-hash whose keys are the fields of the
214 borrowers table in the Koha database. In addition,
215 C<$borrower-E<gt>{flags}> is a hash giving more detailed information
216 about the patron. Its keys act as flags :
218 if $borrower->{flags}->{LOST} {
219 # Patron's card was reported lost
222 Each flag has a C<message> key, giving a human-readable explanation of
223 the flag. If the state of a flag means that the patron should not be
224 allowed to borrow any more books, then it will have a C<noissues> key
227 The possible flags are:
233 Shows the patron's credit or debt, if any.
241 (Gone, no address.) Set if the patron has left without giving a
250 Set if the patron's card has been reported as lost.
258 Set if the patron has been debarred.
266 Any additional notes about the patron.
274 Set if the patron has overdue items. This flag has several keys:
276 C<$flags-E<gt>{ODUES}{itemlist}> is a reference-to-array listing the
277 overdue items. Its elements are references-to-hash, each describing an
278 overdue item. The keys are selected fields from the issues, biblio,
279 biblioitems, and items tables of the Koha database.
281 C<$flags-E<gt>{ODUES}{itemlist}> is a string giving a text listing of
282 the overdue items, one per line.
290 Set if any items that the patron has reserved are available.
292 C<$flags-E<gt>{WAITING}{itemlist}> is a reference-to-array listing the
293 available items. Each element is a reference-to-hash whose keys are
294 fields from the reserves table of the Koha database.
302 sub getpatroninformation {
304 my ($env, $borrowernumber,$cardnumber) = @_;
305 my $dbh = C4::Context->dbh;
308 if ($borrowernumber) {
309 $sth = $dbh->prepare("select * from borrowers where borrowernumber=?");
310 $sth->execute($borrowernumber);
311 } elsif ($cardnumber) {
312 $sth = $dbh->prepare("select * from borrowers where cardnumber=?");
313 $sth->execute($cardnumber);
315 $env->{'apierror'} = "invalid borrower information passed to getpatroninformation subroutine";
318 my $borrower = $sth->fetchrow_hashref;
319 my $amount = C4::Accounts2::checkaccount($env, $borrowernumber, $dbh);
320 $borrower->{'amountoutstanding'} = $amount;
321 my $flags = C4::Circulation::Circ2::patronflags($env, $borrower, $dbh);
324 $sth=$dbh->prepare("select bit,flag from userflags");
326 while (my ($bit, $flag) = $sth->fetchrow) {
327 if ($borrower->{'flags'} & 2**$bit) {
328 $accessflagshash->{$flag}=1;
332 $borrower->{'flags'}=$flags;
333 $borrower->{'authflags'} = $accessflagshash;
334 return ($borrower); #, $flags, $accessflagshash);
339 $borrower = &getmember($cardnumber, $borrowernumber);
341 Looks up information about a patron (borrower) by either card number
342 or borrower number. If $borrowernumber is specified, C<&borrdata>
343 searches by borrower number; otherwise, it searches by card number.
345 C<&getmember> returns a reference-to-hash whose keys are the fields of
346 the C<borrowers> table in the Koha database.
350 =head3 GetFlagsAndBranchFromBorrower
354 ($flags, $homebranch) = GetFlagsAndBranchFromBorrower($loggedinuser);
356 this function read on the database to get flags and homebranch for a user
360 it returns the $flags & the homebranch in scalar context.
370 ($count, $issues) = &borrissues($borrowernumber);
372 Looks up what the patron with the given borrowernumber has borrowed.
374 C<&borrissues> returns a two-element array. C<$issues> is a
375 reference-to-array, where each element is a reference-to-hash; the
376 keys are the fields from the C<issues>, C<biblio>, and C<items> tables
377 in the Koha database. C<$count> is the number of elements in
384 my $dbh = C4::Context->dbh;
385 my $sth=$dbh->prepare("Select * from issues,biblio,items where borrowernumber=?
386 and items.itemnumber=issues.itemnumber
387 and items.biblionumber=biblio.biblionumber
388 and issues.returndate is NULL order by date_due");
389 $sth->execute($bornum);
391 while (my $data = $sth->fetchrow_hashref) {
395 return(scalar(@result), \@result);
400 ($count, $issues) = &allissues($borrowernumber, $sortkey, $limit);
402 Looks up what the patron with the given borrowernumber has borrowed,
403 and sorts the results.
405 C<$sortkey> is the name of a field on which to sort the results. This
406 should be the name of a field in the C<issues>, C<biblio>,
407 C<biblioitems>, or C<items> table in the Koha database.
409 C<$limit> is the maximum number of results to return.
411 C<&allissues> returns a two-element array. C<$issues> is a
412 reference-to-array, where each element is a reference-to-hash; the
413 keys are the fields from the C<issues>, C<biblio>, C<biblioitems>, and
414 C<items> tables of the Koha database. C<$count> is the number of
415 elements in C<$issues>
420 my ($bornum,$order,$limit)=@_;
421 #FIXME: sanity-check order and limit
422 my $dbh = C4::Context->dbh;
423 my $query="Select * from issues,biblio,items
424 where borrowernumber=? and
425 items.itemnumber=issues.itemnumber and
426 items.biblionumber=biblio.biblionumber order by $order";
428 $query.=" limit $limit";
431 my $sth=$dbh->prepare($query);
432 $sth->execute($bornum);
435 while (my $data=$sth->fetchrow_hashref){
445 ## NEU specific. used in Reserve section issues
446 my ($env,$bornum)=@_;
447 my $dbh = C4::Context->dbh;
448 my $query="Select count(*) from reserveissue as r where r.borrowernumber='$bornum'
449 and rettime is null";
451 my $sth=$dbh->prepare($query);
453 my $data=$sth->fetchrow_hashref;
455 $sth=$dbh->prepare("Select count(*),timediff(now(), duetime ) as elapsed, hour(timediff(now(), duetime )) as hours, MINUTE(timediff(now(), duetime )) as min from
456 reserveissue as r where r.borrowernumber='$bornum' and rettime is null and duetime< now() group by r.borrowernumber");
459 my $data2=$sth->fetchrow_hashref;
461 my $rescharge=C4::Context->preference('resmaterialcharge');
465 if ($data2->{'elapsed'}>0){
466 $resfine=($data2->{'hours'}+$data2->{'min'}/60)*$rescharge;
467 $resfine=sprintf ("%.1f",$resfine);
470 $sth=$dbh->prepare("Select sum(amountoutstanding) from accountlines where
471 borrowernumber='$bornum'");
473 my $data3=$sth->fetchrow_hashref;
477 return($data2->{'count(*)'},$data->{'count(*)'},$data3->{'sum(amountoutstanding)'},$resfine);
479 =item getboracctrecord
481 ($count, $acctlines, $total) = &getboracctrecord($env, $borrowernumber);
483 Looks up accounting data for the patron with the given borrowernumber.
488 C<&getboracctrecord> returns a three-element array. C<$acctlines> is a
489 reference-to-array, where each element is a reference-to-hash; the
490 keys are the fields of the C<accountlines> table in the Koha database.
491 C<$count> is the number of elements in C<$acctlines>. C<$total> is the
492 total amount outstanding for all of the account lines.
496 sub getboracctrecord {
497 my ($env,$params) = @_;
498 my $dbh = C4::Context->dbh;
501 my $sth=$dbh->prepare("Select * from accountlines where
502 borrowernumber=? order by date desc,timestamp desc");
504 $sth->execute($params->{'borrowernumber'});
506 while (my $data=$sth->fetchrow_hashref){
507 $acctlines[$numlines] = $data;
509 $total += $data->{'amountoutstanding'};
512 return ($numlines,\@acctlines,$total);
515 sub getborrowercategory{
517 my $dbh = C4::Context->dbh;
518 my $sth = $dbh->prepare("SELECT description FROM categories WHERE categorycode = ?");
519 $sth->execute($catcode);
520 my $description = $sth->fetchrow();
523 } # sub getborrowercategory
525 sub getborrowercategoryinfo{
527 my $dbh = C4::Context->dbh;
528 my $sth = $dbh->prepare("SELECT * FROM categories WHERE categorycode = ?");
529 $sth->execute($catcode);
530 my $category = $sth->fetchrow_hashref;
533 } # sub getborrowercategoryinfo
536 sub GetFlagsAndBranchFromBorrower {
537 my $loggedinuser = @_;
538 my $dbh = C4::Context->dbh;
540 SELECT flags, branchcode
542 WHERE borrowernumber = ?
544 my $sth = $dbh->prepare($query);
545 $sth->execute($loggedinuser);
547 return $sth->fetchrow;
552 my ( $cardnumber, $bornum ) = @_;
553 $cardnumber = uc $cardnumber;
554 my $dbh = C4::Context->dbh;
556 if ( $bornum eq '' ) {
557 $sth = $dbh->prepare("Select * from borrowers where cardnumber=?");
558 $sth->execute($cardnumber);
560 $sth = $dbh->prepare("Select * from borrowers where borrowernumber=?");
561 $sth->execute($bornum);
563 my $data = $sth->fetchrow_hashref;
568 else { # try with firstname
571 $dbh->prepare("select * from borrowers where firstname=?");
572 $sth->execute($cardnumber);
573 my $data = $sth->fetchrow_hashref;
583 $borrower = &borrdata($cardnumber, $borrowernumber);
585 Looks up information about a patron (borrower) by either card number
586 or borrower number. If $borrowernumber is specified, C<&borrdata>
587 searches by borrower number; otherwise, it searches by card number.
589 C<&borrdata> returns a reference-to-hash whose keys are the fields of
590 the C<borrowers> table in the Koha database.
596 my ( $cardnumber, $bornum ) = @_;
597 $cardnumber = uc $cardnumber;
598 my $dbh = C4::Context->dbh;
600 if ( $bornum eq '' ) {
603 "Select borrowers.*,categories.category_type from borrowers left join categories on borrowers.categorycode=categories.categorycode where cardnumber=?"
605 $sth->execute($cardnumber);
610 "Select borrowers.*,categories.category_type from borrowers left join categories on borrowers.categorycode=categories.categorycode where borrowernumber=?"
612 $sth->execute($bornum);
614 my $data = $sth->fetchrow_hashref;
615 # warn "DATA" . $data->{category_type};
620 else { # try with firstname
624 "Select borrowers.*,categories.category_type from borrowers left join categories on borrowers.categorycode=categories.categorycode where firstname=?"
626 $sth->execute($cardnumber);
627 my $data = $sth->fetchrow_hashref;
637 ($borrowed, $due, $fine) = &borrdata2($env, $borrowernumber);
639 Returns aggregate data about items borrowed by the patron with the
640 given borrowernumber.
644 C<&borrdata2> returns a three-element array. C<$borrowed> is the
645 number of books the patron currently has borrowed. C<$due> is the
646 number of overdue items the patron currently has borrowed. C<$fine> is
647 the total fine currently due by the borrower.
653 my ( $env, $bornum ) = @_;
654 my $dbh = C4::Context->dbh;
655 my $query = "Select count(*) from issues where borrowernumber='$bornum' and
659 my $sth = $dbh->prepare($query);
661 my $data = $sth->fetchrow_hashref;
663 $sth = $dbh->prepare(
664 "Select count(*) from issues where
665 borrowernumber='$bornum' and date_due < now() and returndate is NULL"
668 my $data2 = $sth->fetchrow_hashref;
670 $sth = $dbh->prepare(
671 "Select sum(amountoutstanding) from accountlines where
672 borrowernumber='$bornum'"
675 my $data3 = $sth->fetchrow_hashref;
678 return ( $data2->{'count(*)'}, $data->{'count(*)'},
679 $data3->{'sum(amountoutstanding)'} );
684 my $dbh = C4::Context->dbh;
685 $data{'dateofbirth'}=format_date_in_iso($data{'dateofbirth'});
688 $data{'joining'}=format_date_in_iso($data{'joining'});
690 if ($data{'expiry'}) {
691 $data{'expiry'}=format_date_in_iso($data{'expiry'});
694 my $sth = $dbh->prepare("select enrolmentperiod from categories where categorycode=?");
695 $sth->execute($data{'categorycode'});
696 my ($enrolmentperiod) = $sth->fetchrow;
697 $enrolmentperiod = 1 unless ($enrolmentperiod);#enrolmentperiod in years
698 my $duration=get_duration($enrolmentperiod." years");
699 $data{'expiry'} = &DATE_Add_Duration($data{'joining'},$duration );
703 my $query= "UPDATE borrowers SET
704 cardnumber = '$data{'cardnumber'}' ,
705 surname = '$data{'surname'}' ,
706 firstname = '$data{'firstname'}' ,
707 title = '$data{'title'}' ,
708 initials = '$data{'initials'}' ,
709 dateofbirth = '$data{'dateofbirth'}' ,
710 sex = '$data{'sex'}' ,
711 streetaddress = '$data{'streetaddress'}' ,
712 streetcity = '$data{'streetcity'}' ,
713 zipcode = '$data{'zipcode'}' ,
714 phoneday = '$data{'phoneday'}' ,
715 physstreet = '$data{'physstreet'}' ,
716 city = '$data{'city'}' ,
717 homezipcode = '$data{'homezipcode'}' ,
718 phone = '$data{'phone'}' ,
719 emailaddress = '$data{'emailaddress'}' ,
720 preferredcont = '$data{'preferredcont'}',
721 faxnumber = '$data{'faxnumber'}' ,
722 textmessaging = '$data{'textmessaging'}' ,
723 categorycode = '$data{'categorycode'}' ,
724 branchcode = '$data{'branchcode'}' ,
725 borrowernotes = '$data{'borrowernotes'}' ,
726 ethnicity = '$data{'ethnicity'}' ,
727 ethnotes = '$data{'ethnotes'}' ,
728 expiry = '$data{'expiry'}' ,
729 dateenrolled = '$data{'joining'}' ,
730 sort1 = '$data{'sort1'}' ,
731 sort2 = '$data{'sort2'}' ,
732 debarred = '$data{'debarred'}' ,
733 lost = '$data{'lost'}' ,
734 gonenoaddress = '$data{'gna'}'
735 WHERE borrowernumber = $data{'borrowernumber'}";
736 my $sth = $dbh->prepare($query);
739 # ok if its an adult (type) it may have borrowers that depend on it as a guarantor
740 # so when we update information for an adult we should check for guarantees and update the relevant part
741 # of their records, ie addresses and phone numbers
742 if ($data{'categorycode'} eq 'A' || $data{'categorycode'} eq 'W'){
743 # is adult check guarantees;
744 updateguarantees(%data);
750 my $dbh = C4::Context->dbh;
751 $data{'dateofbirth'}=format_date_in_iso($data{'dateofbirth'});
754 if ($data{'joining'}){
755 $data{'joining'}=format_date_in_iso($data{'joining'});
757 $data{'joining'} = get_today();
759 # if expirydate is not set, calculate it from borrower category subscription duration
760 if ($data{'expiry'}) {
761 $data{'expiry'}=format_date_in_iso($data{'expiry'});
763 my $sth = $dbh->prepare("select enrolmentperiod from categories where categorycode=?");
764 $sth->execute($data{'categorycode'});
765 my ($enrolmentperiod) = $sth->fetchrow;
766 $enrolmentperiod = 1 unless ($enrolmentperiod);#enrolmentperiod in years
767 my $duration=get_duration($enrolmentperiod." years");
768 $data{'expiry'} = &DATE_Add_Duration($data{'joining'},$duration);
771 my $query= "INSERT INTO borrowers (
802 '$data{'cardnumber'}',
804 '$data{'firstname'}',
807 '$data{'dateofbirth'}',
810 '$data{'streetaddress'}',
811 '$data{'streetcity'}',
815 '$data{'physstreet'}',
817 '$data{'homezipcode'}',
820 '$data{'emailaddress'}',
821 '$data{'faxnumber'}',
822 '$data{'textmessaging'}',
823 '$data{'preferredcont'}',
824 '$data{'categorycode'}',
825 '$data{'branchcode'}',
826 '$data{'borrowernotes'}',
827 '$data{'ethnicity'}',
834 my $sth=$dbh->prepare($query);
837 $data{'bornum'} =$dbh->{'mysql_insertid'};
838 return $data{'bornum'};
842 my ( $categorycode, $dateenrolled ) = @_;
843 my $dbh = C4::Context->dbh;
846 "select enrolmentperiod from categories where categorycode=?");
847 $sth->execute($categorycode);
848 my ($enrolmentperiod) = $sth->fetchrow;
849 $enrolmentperiod = 12 unless ($enrolmentperiod);
850 return format_date_in_iso(
851 &DateCalc( $dateenrolled, "$enrolmentperiod months" ) );
854 =head2 checkuserpassword (OUEST-PROVENCE)
856 check for the password and login are not used
857 return the number of record
858 0=> NOT USED 1=> USED
862 sub checkuserpassword {
863 my ( $borrowernumber, $userid, $password ) = @_;
864 $password = md5_base64($password);
865 my $dbh = C4::Context->dbh;
868 "Select count(*) from borrowers where borrowernumber !=? and userid =? and password=? "
870 $sth->execute( $borrowernumber, $userid, $password );
871 my $number_rows = $sth->fetchrow;
875 sub getmemberfromuserid {
877 my $dbh = C4::Context->dbh;
878 my $sth = $dbh->prepare("select * from borrowers where userid=?");
879 $sth->execute($userid);
880 return $sth->fetchrow_hashref;
882 sub updateguarantees {
884 my $dbh = C4::Context->dbh;
885 my ( $count, $guarantees ) = findguarantees( $data{'borrowernumber'} );
886 for ( my $i = 0 ; $i < $count ; $i++ ) {
889 # It looks like the $i is only being returned to handle walking through
890 # the array, which is probably better done as a foreach loop.
893 "update borrowers set streetaddress='$data{'address'}',faxnumber='$data{'faxnumber'}',
894 streetcity='$data{'streetcity'}',phoneday='$data{'phoneday'}',city='$data{'city'}',area='$data{'area'}',phone='$data{'phone'}'
895 ,streetaddress='$data{'address'}'
896 where borrowernumber='$guarantees->[$i]->{'borrowernumber'}'";
897 my $sth3 = $dbh->prepare($guaquery);
902 ################################################################################
904 =item fixup_cardnumber
906 Warning: The caller is responsible for locking the members table in write
907 mode, to avoid database corruption.
911 use vars qw( @weightings );
912 my @weightings = ( 8, 4, 6, 3, 5, 2, 1 );
914 sub fixup_cardnumber ($) {
915 my ($cardnumber) = @_;
916 my $autonumber_members = C4::Context->boolean_preference('autoMemberNum');
917 $autonumber_members = 0 unless defined $autonumber_members;
919 # Find out whether member numbers should be generated
920 # automatically. Should be either "1" or something else.
921 # Defaults to "0", which is interpreted as "no".
923 # if ($cardnumber !~ /\S/ && $autonumber_members) {
924 if ($autonumber_members) {
925 my $dbh = C4::Context->dbh;
926 if ( C4::Context->preference('checkdigit') eq 'katipo' ) {
928 # if checkdigit is selected, calculate katipo-style cardnumber.
929 # otherwise, just use the max()
930 # purpose: generate checksum'd member numbers.
931 # We'll assume we just got the max value of digits 2-8 of member #'s
932 # from the database and our job is to increment that by one,
933 # determine the 1st and 9th digits and return the full string.
936 "select max(substring(borrowers.cardnumber,2,7)) from borrowers"
940 my $data = $sth->fetchrow_hashref;
941 $cardnumber = $data->{'max(substring(borrowers.cardnumber,2,7))'};
944 if ( !$cardnumber ) { # If DB has no values,
945 $cardnumber = 1000000; # start at 1000000
951 for ( my $i = 0 ; $i < 8 ; $i += 1 ) {
953 # read weightings, left to right, 1 char at a time
954 my $temp1 = $weightings[$i];
956 # sequence left to right, 1 char at a time
957 my $temp2 = substr( $cardnumber, $i, 1 );
959 # mult each char 1-7 by its corresponding weighting
960 $sum += $temp1 * $temp2;
963 $rem = ( $sum % 11 );
964 $rem = 'X' if $rem == 10;
966 $cardnumber = "V$cardnumber$rem";
970 # MODIFIED BY JF: mysql4.1 allows casting as an integer, which is probably
971 # better. I'll leave the original in in case it needs to be changed for you
974 "select max(cast(cardnumber as signed)) from borrowers");
976 #my $sth=$dbh->prepare("select max(borrowers.cardnumber) from borrowers");
980 $cardnumber="V$cardnumber$rem";
985 sub fixupneu_cardnumber{
986 my($cardnumber,$categorycode) = @_;
987 my $autonumber_members = C4::Context->boolean_preference('autoMemberNum');
988 $autonumber_members = 0 unless defined $autonumber_members;
989 # Find out whether member numbers should be generated
990 # automatically. Should be either "1" or something else.
991 # Defaults to "0", which is interpreted as "no".
992 my $dbh = C4::Context->dbh;
994 if (! $cardnumber && $autonumber_members && $categorycode) {
995 if ($categorycode eq "A" || $categorycode eq "W" ){
996 $sth=$dbh->prepare("select max(borrowers.cardnumber) from borrowers where borrowers.cardnumber like '5%' ");
997 }elsif ($categorycode eq "L"){
998 $sth=$dbh->prepare("select max(borrowers.cardnumber) from borrowers where borrowers.cardnumber like '10%' ");
999 }elsif ($categorycode eq "F" || $categorycode eq "E") {
1000 $sth=$dbh->prepare("select max(borrowers.cardnumber) from borrowers where borrowers.cardnumber like '30%' ");
1001 }elsif ($categorycode eq "N"){
1002 $sth=$dbh->prepare("select max(borrowers.cardnumber) from borrowers where borrowers.cardnumber like '40%' ");
1003 }elsif ($categorycode eq "C"){
1004 $sth=$dbh->prepare("select max(borrowers.cardnumber) from borrowers where borrowers.cardnumber like '80%' ");
1007 $sth=$dbh->prepare("select max(borrowers.cardnumber) from borrowers where borrowers.cardnumber like '6%' ");
1011 my $data=$sth->fetchrow_hashref;
1012 $cardnumber=$data->{'max(borrowers.cardnumber)'};
1015 # purpose: generate checksum'd member numbers.
1016 # We'll assume we just got the max value of digits 2-8 of member #'s
1017 # from the database and our job is to increment that by one,
1018 # determine the 1st and 9th digits and return the full string.
1020 if (! $cardnumber) { # If DB has no values,
1021 if ($categorycode eq "A" || $categorycode eq "W" ){ $cardnumber = 5000000;}
1022 elsif ($categorycode eq "L"){ $cardnumber = 1000000;}
1023 elsif ($categorycode eq "F"){ $cardnumber = 3000000;}
1024 elsif ($categorycode eq "C"){ $cardnumber = 8000000;}
1025 else{$cardnumber = 6000000;}
1026 # start at 1000000 or 3000000 or 5000000
1036 =item GuarantornameSearch
1038 ($count, $borrowers) = &GuarantornameSearch($env, $searchstring, $type);
1040 Looks up guarantor by name.
1044 BUGFIX 499: C<$type> is now used to determine type of search.
1045 if $type is "simple", search is performed on the first letter of the
1048 C<$searchstring> is a space-separated list of search terms. Each term
1049 must match the beginning a borrower's surname, first name, or other
1052 C<&GuarantornameSearch> returns a two-element list. C<$borrowers> is a
1053 reference-to-array; each element is a reference-to-hash, whose keys
1054 are the fields of the C<borrowers> table in the Koha database.
1055 C<$count> is the number of elements in C<$borrowers>.
1057 return all info from guarantor =>only category_type A
1062 #used by member enquiries from the intranet
1063 #called by guarantor_search.pl
1064 sub GuarantornameSearch {
1065 my ( $env, $searchstring, $orderby, $type ) = @_;
1066 my $dbh = C4::Context->dbh;
1072 if ( $type eq "simple" ) # simple search for one letter only
1075 "Select * from borrowers,categories where borrowers.categorycode=categories.categorycode and category_type='A' and surname like ? order by $orderby";
1076 @bind = ("$searchstring%");
1078 else # advanced search looking in surname, firstname and othernames
1080 @data = split( ' ', $searchstring );
1082 $query = "Select * from borrowers,categories
1083 where ((surname like ? or surname like ?
1084 or firstname like ? or firstname like ?
1085 or othernames like ? or othernames like ?) and borrowers.categorycode=categories.categorycode and category_type='A'
1088 "$data[0]%", "% $data[0]%", "$data[0]%", "% $data[0]%",
1089 "$data[0]%", "% $data[0]%"
1091 for ( my $i = 1 ; $i < $count ; $i++ ) {
1092 $query = $query . " and (" . " surname like ? or surname like ?
1093 or firstname like ? or firstname like ?
1094 or othernames like ? or othernames like ?)";
1096 "$data[$i]%", "% $data[$i]%", "$data[$i]%",
1097 "% $data[$i]%", "$data[$i]%", "% $data[$i]%" );
1101 $query = $query . ") or cardnumber like ?
1103 push( @bind, $searchstring );
1108 my $sth = $dbh->prepare($query);
1109 $sth->execute(@bind);
1111 my $cnt = $sth->rows;
1112 while ( my $data = $sth->fetchrow_hashref ) {
1113 push( @results, $data );
1118 return ( $cnt, \@results );
1122 =item findguarantees
1124 ($num_children, $children_arrayref) = &findguarantees($parent_borrno);
1125 $child0_cardno = $children_arrayref->[0]{"cardnumber"};
1126 $child0_borrno = $children_arrayref->[0]{"borrowernumber"};
1128 C<&findguarantees> takes a borrower number (e.g., that of a patron
1129 with children) and looks up the borrowers who are guaranteed by that
1130 borrower (i.e., the patron's children).
1132 C<&findguarantees> returns two values: an integer giving the number of
1133 borrowers guaranteed by C<$parent_borrno>, and a reference to an array
1134 of references to hash, which gives the actual results.
1140 my $dbh = C4::Context->dbh;
1141 my $sth=$dbh->prepare("select cardnumber,borrowernumber, firstname, surname from borrowers where guarantor=?");
1142 $sth->execute($bornum);
1145 while (my $data = $sth->fetchrow_hashref)
1150 return (scalar(@dat), \@dat);
1155 $guarantor = &findguarantor($borrower_no);
1156 $guarantor_cardno = $guarantor->{"cardnumber"};
1157 $guarantor_surname = $guarantor->{"surname"};
1160 C<&findguarantor> takes a borrower number (presumably that of a child
1161 patron), finds the guarantor for C<$borrower_no> (the child's parent),
1162 and returns the record for the guarantor.
1164 C<&findguarantor> returns a reference-to-hash. Its keys are the fields
1165 from the C<borrowers> database table;
1171 my $dbh = C4::Context->dbh;
1172 my $sth=$dbh->prepare("select guarantor from borrowers where borrowernumber=?");
1173 $sth->execute($bornum);
1174 my $data=$sth->fetchrow_hashref;
1176 $sth=$dbh->prepare("Select * from borrowers where borrowernumber=?");
1177 $sth->execute($data->{'guarantor'});
1178 $data=$sth->fetchrow_hashref;
1183 sub borrowercard_active {
1185 my $dbh = C4::Context->dbh;
1186 my $sth = $dbh->prepare("SELECT expiry FROM borrowers WHERE (borrowernumber = ?) AND (NOW() <= expiry)");
1187 $sth->execute($bornum);
1188 if (my $data=$sth->fetchrow_hashref){
1195 # Search the member photo, in case that photo doesn´t exists, return a default photo.for NEU
1196 sub getMemberPhoto {
1197 my $cardnumber = shift @_;
1198 my $htdocs = C4::Context->config('opacdir');
1199 my $dirname = $htdocs."/htdocs/uploaded-files/users-photo/";
1200 # my $dirname = "$ENV{'DOCUMENT_ROOT'}/uploaded-files/users-photo";
1201 opendir(DIR, $dirname) or die "Can't open directory $dirname: $!";
1202 while (defined(my $file = readdir(DIR))) {
1203 if ($file =~ /^$cardnumber\..+/){
1204 return "/uploaded-files/users-photo/$file";
1208 return "http://cc.neu.edu.tr/stdpictures/".$cardnumber.".jpg";
1211 sub change_user_pass {
1212 my ($uid,$member,$digest) = @_;
1213 my $dbh = C4::Context->dbh;
1214 #Make sure the userid chosen is unique and not theirs if non-empty. If it is not,
1215 #Then we need to tell the user and have them create a new one.
1216 my $sth=$dbh->prepare("select * from borrowers where userid=? and borrowernumber <> ?");
1217 $sth->execute($uid,$member);
1218 if ( ($uid ne '') && ($sth->fetchrow) ) {
1222 #Everything is good so we can update the information.
1223 $sth=$dbh->prepare("update borrowers set userid=?, password=? where borrowernumber=?");
1224 $sth->execute($uid, $digest, $member);
1227 =head2 checkuniquemember (OUEST-PROVENCE)
1229 $result = &checkuniquemember($collectivity,$surname,$categorycode,$firstname,$dateofbirth);
1231 Checks that a member exists or not in the database.
1233 C<&result> is 1 (=exist) or 0 (=does not exist)
1234 C<&collectivity> is 1 (= we add a collectivity) or 0 (= we add a physical member)
1235 C<&surname> is the surname
1236 C<&categorycode> is from categorycode table
1237 C<&firstname> is the firstname (only if collectivity=0)
1238 C<&dateofbirth> is the date of birth (only if collectivity=0)
1242 sub checkuniquemember {
1243 my ( $collectivity, $surname, $firstname, $dateofbirth ) = @_;
1244 my $dbh = C4::Context->dbh;
1246 if ($collectivity) {
1248 # $request="select count(*) from borrowers where surname=? and categorycode=?";
1250 "select borrowernumber,categorycode from borrowers where surname=? ";
1254 # $request="select count(*) from borrowers where surname=? and categorycode=? and firstname=? and dateofbirth=?";
1256 "select borrowernumber,categorycode from borrowers where surname=? and firstname=? and dateofbirth=?";
1258 my $sth = $dbh->prepare($request);
1259 if ($collectivity) {
1260 $sth->execute( uc($surname) );
1263 $sth->execute( uc($surname), ucfirst($firstname), $dateofbirth );
1265 my @data = $sth->fetchrow;
1268 return $data[0], $data[1];
1277 =head2 getzipnamecity (OUEST-PROVENCE)
1279 take all info from table city for the fields city and zip
1280 check for the name and the zip code of the city selected
1284 sub getzipnamecity {
1286 my $dbh = C4::Context->dbh;
1289 "select city_name,city_zipcode from cities where cityid=? ");
1290 $sth->execute($cityid);
1291 my @data = $sth->fetchrow;
1292 return $data[0], $data[1];
1295 =head2 updatechildguarantor (OUEST-PROVENCE)
1297 check for title,firstname,surname,adress,zip code and city from guarantor to
1304 sub getguarantordata {
1305 my ($borrowerid) = @_;
1306 my $dbh = C4::Context->dbh;
1309 "Select title,firstname,surname,streetnumber,address,streettype,address2,zipcode,city,phone,phonepro,mobile,email,emailpro,fax from borrowers where borrowernumber =? "
1311 $sth->execute($borrowerid);
1312 my $guarantor_data = $sth->fetchrow_hashref;
1314 return $guarantor_data;
1317 =head2 getdcity (OUEST-PROVENCE)
1318 recover cityid with city_name condition
1322 my ($city_name) = @_;
1323 my $dbh = C4::Context->dbh;
1324 my $sth = $dbh->prepare("select cityid from cities where city_name=? ");
1325 $sth->execute($city_name);
1326 my $data = $sth->fetchrow;
1330 =head2 getcategorytype (OUEST-PROVENCE)
1332 check for the category_type with categorycode
1333 and return the category_type
1337 sub getcategorytype {
1338 my ($categorycode) = @_;
1339 my $dbh = C4::Context->dbh;
1342 "Select category_type,description from categories where categorycode=? "
1344 $sth->execute($categorycode);
1345 my ( $category_type, $description ) = $sth->fetchrow;
1346 return $category_type, $description;
1355 # # A better approach might be to set borrowernumber autoincrement and
1357 sub NewBorrowerNumber {
1358 my $dbh = C4::Context->dbh;
1359 my $sth=$dbh->prepare("Select max(borrowernumber) from borrowers");
1361 my $data=$sth->fetchrow_hashref;
1363 $data->{'max(borrowernumber)'}++;
1364 return($data->{'max(borrowernumber)'});
1367 =head2 ethnicitycategories
1369 ($codes_arrayref, $labels_hashref) = ðnicitycategories();
1371 Looks up the different ethnic types in the database. Returns two
1372 elements: a reference-to-array, which lists the ethnicity codes, and a
1373 reference-to-hash, which maps the ethnicity codes to ethnicity
1380 sub ethnicitycategories {
1381 my $dbh = C4::Context->dbh;
1382 my $sth = $dbh->prepare("Select code,name from ethnicity order by name");
1386 while ( my $data = $sth->fetchrow_hashref ) {
1387 push @codes, $data->{'code'};
1388 $labels{ $data->{'code'} } = $data->{'name'};
1391 return ( \@codes, \%labels );
1396 $ethn_name = &fixEthnicity($ethn_code);
1398 Takes an ethnicity code (e.g., "european" or "pi") and returns the
1399 corresponding descriptive name from the C<ethnicity> table in the
1400 Koha database ("European" or "Pacific Islander").
1406 sub fixEthnicity($) {
1408 my $ethnicity = shift;
1409 my $dbh = C4::Context->dbh;
1410 my $sth = $dbh->prepare("Select name from ethnicity where code = ?");
1411 $sth->execute($ethnicity);
1412 my $data = $sth->fetchrow_hashref;
1414 return $data->{'name'};
1415 } # sub fixEthnicity
1421 $dateofbirth,$date = &get_age($date);
1423 this function return the borrowers age with the value of dateofbirth
1428 my ($date, $date_ref) = @_;
1430 if (not defined $date_ref) {
1431 $date_ref = get_today();
1434 my ($year1, $month1, $day1) = split /-/, $date;
1435 my ($year2, $month2, $day2) = split /-/, $date_ref;
1437 my $age = $year2 - $year1;
1438 if ($month1.$day1 > $month2.$day2) {
1447 =head2 get_institutions
1448 $insitutions = get_institutions();
1450 Just returns a list of all the borrowers of type I, borrownumber and name
1454 sub get_institutions {
1455 my $dbh = C4::Context->dbh();
1458 "SELECT borrowernumber,surname FROM borrowers WHERE categorycode=? ORDER BY surname"
1462 while ( my $data = $sth->fetchrow_hashref() ) {
1463 $orgs{ $data->{'borrowernumber'} } = $data;
1468 } # sub get_institutions
1470 =head2 add_member_orgs
1472 add_member_orgs($borrowernumber,$borrowernumbers);
1474 Takes a borrowernumber and a list of other borrowernumbers and inserts them into the borrowers_to_borrowers table
1479 sub add_member_orgs {
1480 my ( $borrowernumber, $otherborrowers ) = @_;
1481 my $dbh = C4::Context->dbh();
1483 "INSERT INTO borrowers_to_borrowers (borrower1,borrower2) VALUES (?,?)";
1484 my $sth = $dbh->prepare($query);
1485 foreach my $bornum (@$otherborrowers) {
1486 $sth->execute( $borrowernumber, $bornum );
1490 } # sub add_member_orgs
1492 =head2 GetBorrowersFromSurname
1496 \@resutlts = GetBorrowersFromSurname($surname)
1497 this function get the list of borrower names like $surname.
1499 the table of results in @results
1504 sub GetBorrowersFromSurname {
1505 my ($searchstring)=@_;
1506 my $dbh = C4::Context->dbh;
1507 $searchstring=~ s/\'/\\\'/g;
1508 my @data=split(' ',$searchstring);
1511 SELECT surname,firstname
1513 WHERE (surname like ?)
1516 my $sth=$dbh->prepare($query);
1517 $sth->execute("$data[0]%");
1520 while (my $data=$sth->fetchrow_hashref){
1521 push(@results,$data);
1525 return ($count,\@results);
1528 =head2 expand_sex_into_predicate
1530 $data{&expand_sex_into_predicate($data{sex})} = 1;
1532 Converts a single 'M' or 'F' into 'sex_M_p' or 'sex_F_p'
1535 In some languages, 'M' and 'F' are not appropriate. However,
1536 with HTML::Template, there is no way to localize 'M' or 'F'
1537 unless these are converted into variables that TMPL_IF can
1538 understand. This function provides this conversion.
1542 sub expand_sex_into_predicate ($) {
1544 return "sex_${sex}_p";
1545 } # expand_sex_into_predicate