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
385 my $dbh = C4::Context->dbh;
386 my $sth=$dbh->prepare("Select * from issues,biblio,items where borrowernumber=?
387 and items.itemnumber=issues.itemnumber
388 and items.biblionumber=biblio.biblionumber
389 and issues.returndate is NULL order by date_due");
390 $sth->execute($bornum);
392 while (my $data = $sth->fetchrow_hashref) {
396 return(scalar(@result), \@result);
401 ($count, $issues) = &allissues($borrowernumber, $sortkey, $limit);
403 Looks up what the patron with the given borrowernumber has borrowed,
404 and sorts the results.
406 C<$sortkey> is the name of a field on which to sort the results. This
407 should be the name of a field in the C<issues>, C<biblio>,
408 C<biblioitems>, or C<items> table in the Koha database.
410 C<$limit> is the maximum number of results to return.
412 C<&allissues> returns a two-element array. C<$issues> is a
413 reference-to-array, where each element is a reference-to-hash; the
414 keys are the fields from the C<issues>, C<biblio>, C<biblioitems>, and
415 C<items> tables of the Koha database. C<$count> is the number of
416 elements in C<$issues>
421 my ($bornum,$order,$limit)=@_;
422 #FIXME: sanity-check order and limit
423 my $dbh = C4::Context->dbh;
424 my $query="Select * from issues,biblio,items
425 where borrowernumber=? and
426 items.itemnumber=issues.itemnumber and
427 items.biblionumber=biblio.biblionumber order by $order";
429 $query.=" limit $limit";
432 my $sth=$dbh->prepare($query);
433 $sth->execute($bornum);
436 while (my $data=$sth->fetchrow_hashref){
446 ## NEU specific. used in Reserve section issues
447 my ($env,$bornum)=@_;
448 my $dbh = C4::Context->dbh;
449 my $query="Select count(*) from reserveissue as r where r.borrowernumber='$bornum'
450 and rettime is null";
452 my $sth=$dbh->prepare($query);
454 my $data=$sth->fetchrow_hashref;
456 $sth=$dbh->prepare("Select count(*),timediff(now(), duetime ) as elapsed, hour(timediff(now(), duetime )) as hours, MINUTE(timediff(now(), duetime )) as min from
457 reserveissue as r where r.borrowernumber='$bornum' and rettime is null and duetime< now() group by r.borrowernumber");
460 my $data2=$sth->fetchrow_hashref;
462 my $rescharge=C4::Context->preference('resmaterialcharge');
466 if ($data2->{'elapsed'}>0){
467 $resfine=($data2->{'hours'}+$data2->{'min'}/60)*$rescharge;
468 $resfine=sprintf ("%.1f",$resfine);
471 $sth=$dbh->prepare("Select sum(amountoutstanding) from accountlines where
472 borrowernumber='$bornum'");
474 my $data3=$sth->fetchrow_hashref;
478 return($data2->{'count(*)'},$data->{'count(*)'},$data3->{'sum(amountoutstanding)'},$resfine);
480 =item getboracctrecord
482 ($count, $acctlines, $total) = &getboracctrecord($env, $borrowernumber);
484 Looks up accounting data for the patron with the given borrowernumber.
489 C<&getboracctrecord> returns a three-element array. C<$acctlines> is a
490 reference-to-array, where each element is a reference-to-hash; the
491 keys are the fields of the C<accountlines> table in the Koha database.
492 C<$count> is the number of elements in C<$acctlines>. C<$total> is the
493 total amount outstanding for all of the account lines.
497 sub getboracctrecord {
498 my ($env,$params) = @_;
499 my $dbh = C4::Context->dbh;
502 my $sth=$dbh->prepare("Select * from accountlines where
503 borrowernumber=? order by date desc,timestamp desc");
505 $sth->execute($params->{'borrowernumber'});
507 while (my $data=$sth->fetchrow_hashref){
508 $acctlines[$numlines] = $data;
510 $total += $data->{'amountoutstanding'};
513 return ($numlines,\@acctlines,$total);
516 sub getborrowercategory{
518 my $dbh = C4::Context->dbh;
519 my $sth = $dbh->prepare("SELECT description FROM categories WHERE categorycode = ?");
520 $sth->execute($catcode);
521 my $description = $sth->fetchrow();
524 } # sub getborrowercategory
526 sub getborrowercategoryinfo{
528 my $dbh = C4::Context->dbh;
529 my $sth = $dbh->prepare("SELECT * FROM categories WHERE categorycode = ?");
530 $sth->execute($catcode);
531 my $category = $sth->fetchrow_hashref;
534 } # sub getborrowercategoryinfo
537 sub GetFlagsAndBranchFromBorrower {
538 my $loggedinuser = @_;
539 my $dbh = C4::Context->dbh;
541 SELECT flags, branchcode
543 WHERE borrowernumber = ?
545 my $sth = $dbh->prepare($query);
546 $sth->execute($loggedinuser);
548 return $sth->fetchrow;
553 my ( $cardnumber, $bornum ) = @_;
554 $cardnumber = uc $cardnumber;
555 my $dbh = C4::Context->dbh;
557 if ( $bornum eq '' ) {
558 $sth = $dbh->prepare("Select * from borrowers where cardnumber=?");
559 $sth->execute($cardnumber);
561 $sth = $dbh->prepare("Select * from borrowers where borrowernumber=?");
562 $sth->execute($bornum);
564 my $data = $sth->fetchrow_hashref;
569 else { # try with firstname
572 $dbh->prepare("select * from borrowers where firstname=?");
573 $sth->execute($cardnumber);
574 my $data = $sth->fetchrow_hashref;
584 $borrower = &borrdata($cardnumber, $borrowernumber);
586 Looks up information about a patron (borrower) by either card number
587 or borrower number. If $borrowernumber is specified, C<&borrdata>
588 searches by borrower number; otherwise, it searches by card number.
590 C<&borrdata> returns a reference-to-hash whose keys are the fields of
591 the C<borrowers> table in the Koha database.
597 my ( $cardnumber, $bornum ) = @_;
598 $cardnumber = uc $cardnumber;
599 my $dbh = C4::Context->dbh;
601 if ( $bornum eq '' ) {
604 "Select borrowers.*,categories.category_type from borrowers left join categories on borrowers.categorycode=categories.categorycode where cardnumber=?"
606 $sth->execute($cardnumber);
611 "Select borrowers.*,categories.category_type from borrowers left join categories on borrowers.categorycode=categories.categorycode where borrowernumber=?"
613 $sth->execute($bornum);
615 my $data = $sth->fetchrow_hashref;
616 # warn "DATA" . $data->{category_type};
621 else { # try with firstname
625 "Select borrowers.*,categories.category_type from borrowers left join categories on borrowers.categorycode=categories.categorycode where firstname=?"
627 $sth->execute($cardnumber);
628 my $data = $sth->fetchrow_hashref;
638 ($borrowed, $due, $fine) = &borrdata2($env, $borrowernumber);
640 Returns aggregate data about items borrowed by the patron with the
641 given borrowernumber.
645 C<&borrdata2> returns a three-element array. C<$borrowed> is the
646 number of books the patron currently has borrowed. C<$due> is the
647 number of overdue items the patron currently has borrowed. C<$fine> is
648 the total fine currently due by the borrower.
654 my ( $env, $bornum ) = @_;
655 my $dbh = C4::Context->dbh;
656 my $query = "Select count(*) from issues where borrowernumber='$bornum' and
660 my $sth = $dbh->prepare($query);
662 my $data = $sth->fetchrow_hashref;
664 $sth = $dbh->prepare(
665 "Select count(*) from issues where
666 borrowernumber='$bornum' and date_due < now() and returndate is NULL"
669 my $data2 = $sth->fetchrow_hashref;
671 $sth = $dbh->prepare(
672 "Select sum(amountoutstanding) from accountlines where
673 borrowernumber='$bornum'"
676 my $data3 = $sth->fetchrow_hashref;
679 return ( $data2->{'count(*)'}, $data->{'count(*)'},
680 $data3->{'sum(amountoutstanding)'} );
685 my $dbh = C4::Context->dbh;
686 $data{'dateofbirth'}=format_date_in_iso($data{'dateofbirth'});
689 $data{'joining'}=format_date_in_iso($data{'joining'});
691 if ($data{'expiry'} eq '') {
693 my $sth = $dbh->prepare("select enrolmentperiod from categories where categorycode=?");
694 $sth->execute($data{'categorycode'});
695 my ($enrolmentperiod) = $sth->fetchrow;
696 $enrolmentperiod = 12 unless ($enrolmentperiod);
697 $data{'expiry'} = &DateCalc($data{'joining'},"$enrolmentperiod years");
699 $data{'expiry'}=format_date_in_iso($data{'expiry'});
700 my $query= "UPDATE borrowers SET
701 cardnumber = '$data{'cardnumber'}' ,
702 surname = '$data{'surname'}' ,
703 firstname = '$data{'firstname'}' ,
704 title = '$data{'title'}' ,
705 initials = '$data{'initials'}' ,
706 dateofbirth = '$data{'dateofbirth'}' ,
707 sex = '$data{'sex'}' ,
708 streetaddress = '$data{'streetaddress'}' ,
709 streetcity = '$data{'streetcity'}' ,
710 zipcode = '$data{'zipcode'}' ,
711 phoneday = '$data{'phoneday'}' ,
712 physstreet = '$data{'physstreet'}' ,
713 city = '$data{'city'}' ,
714 homezipcode = '$data{'homezipcode'}' ,
715 phone = '$data{'phone'}' ,
716 emailaddress = '$data{'emailaddress'}' ,
717 faxnumber = '$data{'faxnumber'}' ,
718 textmessaging = '$data{'textmessaging'}' ,
719 categorycode = '$data{'categorycode'}' ,
720 branchcode = '$data{'branchcode'}' ,
721 borrowernotes = '$data{'borrowernotes'}' ,
722 ethnicity = '$data{'ethnicity'}' ,
723 ethnotes = '$data{'ethnotes'}' ,
724 expiry = '$data{'expiry'}' ,
725 dateenrolled = '$data{'joining'}' ,
726 sort1 = '$data{'sort1'}' ,
727 sort2 = '$data{'sort2'}' ,
728 debarred = '$data{'debarred'}' ,
729 lost = '$data{'lost'}' ,
730 gonenoaddress = '$data{'gna'}'
731 WHERE borrowernumber = $data{'borrowernumber'}";
732 my $sth = $dbh->prepare($query);
735 # ok if its an adult (type) it may have borrowers that depend on it as a guarantor
736 # so when we update information for an adult we should check for guarantees and update the relevant part
737 # of their records, ie addresses and phone numbers
738 if ($data{'categorycode'} eq 'A' || $data{'categorycode'} eq 'W'){
739 # is adult check guarantees;
740 updateguarantees(%data);
746 my $dbh = C4::Context->dbh;
747 $data{'dateofbirth'}=format_date_in_iso($data{'dateofbirth'});
748 $data{'joining'} = &ParseDate("today") unless $data{'joining'};
749 $data{'joining'}=format_date_in_iso($data{'joining'});
750 # if expirydate is not set, calculate it from borrower category subscription duration
751 unless ($data{'expiry'}) {
752 my $sth = $dbh->prepare("select enrolmentperiod from categories where categorycode=?");
753 $sth->execute($data{'categorycode'});
754 my ($enrolmentperiod) = $sth->fetchrow;
755 $enrolmentperiod = 12 unless ($enrolmentperiod);
756 $data{'expiry'} = &DateCalc($data{'joining'},"$enrolmentperiod years");
758 $data{'expiry'}=format_date_in_iso($data{'expiry'});
759 my $query= "INSERT INTO borrowers (
789 '$data{'cardnumber'}',
791 '$data{'firstname'}',
794 '$data{'dateofbirth'}',
797 '$data{'streetaddress'}',
798 '$data{'streetcity'}',
802 '$data{'physstreet'}',
804 '$data{'homezipcode'}',
807 '$data{'emailaddress'}',
808 '$data{'faxnumber'}',
809 '$data{'textmessaging'}',
811 '$data{'categorycode'}',
812 '$data{'branchcode'}',
813 '$data{'borrowernotes'}',
814 '$data{'ethnicity'}',
821 my $sth=$dbh->prepare($query);
824 $data{'bornum'} =$dbh->{'mysql_insertid'};
825 return $data{'bornum'};
829 my ( $categorycode, $dateenrolled ) = @_;
830 my $dbh = C4::Context->dbh;
833 "select enrolmentperiod from categories where categorycode=?");
834 $sth->execute($categorycode);
835 my ($enrolmentperiod) = $sth->fetchrow;
836 $enrolmentperiod = 12 unless ($enrolmentperiod);
837 return format_date_in_iso(
838 &DateCalc( $dateenrolled, "$enrolmentperiod months" ) );
841 =head2 checkuserpassword (OUEST-PROVENCE)
843 check for the password and login are not used
844 return the number of record
845 0=> NOT USED 1=> USED
849 sub checkuserpassword {
850 my ( $borrowernumber, $userid, $password ) = @_;
851 $password = md5_base64($password);
852 my $dbh = C4::Context->dbh;
855 "Select count(*) from borrowers where borrowernumber !=? and userid =? and password=? "
857 $sth->execute( $borrowernumber, $userid, $password );
858 my $number_rows = $sth->fetchrow;
862 sub getmemberfromuserid {
864 my $dbh = C4::Context->dbh;
865 my $sth = $dbh->prepare("select * from borrowers where userid=?");
866 $sth->execute($userid);
867 return $sth->fetchrow_hashref;
869 sub updateguarantees {
871 my $dbh = C4::Context->dbh;
872 my ( $count, $guarantees ) = findguarantees( $data{'borrowernumber'} );
873 for ( my $i = 0 ; $i < $count ; $i++ ) {
876 # It looks like the $i is only being returned to handle walking through
877 # the array, which is probably better done as a foreach loop.
880 "update borrowers set streetaddress='$data{'address'}',faxnumber='$data{'faxnumber'}',
881 streetcity='$data{'streetcity'}',phoneday='$data{'phoneday'}',city='$data{'city'}',area='$data{'area'}',phone='$data{'phone'}'
882 ,streetaddress='$data{'address'}'
883 where borrowernumber='$guarantees->[$i]->{'borrowernumber'}'";
884 my $sth3 = $dbh->prepare($guaquery);
889 ################################################################################
891 =item fixup_cardnumber
893 Warning: The caller is responsible for locking the members table in write
894 mode, to avoid database corruption.
898 use vars qw( @weightings );
899 my @weightings = ( 8, 4, 6, 3, 5, 2, 1 );
901 sub fixup_cardnumber ($) {
902 my ($cardnumber) = @_;
903 my $autonumber_members = C4::Context->boolean_preference('autoMemberNum');
904 $autonumber_members = 0 unless defined $autonumber_members;
906 # Find out whether member numbers should be generated
907 # automatically. Should be either "1" or something else.
908 # Defaults to "0", which is interpreted as "no".
910 # if ($cardnumber !~ /\S/ && $autonumber_members) {
911 if ($autonumber_members) {
912 my $dbh = C4::Context->dbh;
913 if ( C4::Context->preference('checkdigit') eq 'katipo' ) {
915 # if checkdigit is selected, calculate katipo-style cardnumber.
916 # otherwise, just use the max()
917 # purpose: generate checksum'd member numbers.
918 # We'll assume we just got the max value of digits 2-8 of member #'s
919 # from the database and our job is to increment that by one,
920 # determine the 1st and 9th digits and return the full string.
923 "select max(substring(borrowers.cardnumber,2,7)) from borrowers"
927 my $data = $sth->fetchrow_hashref;
928 $cardnumber = $data->{'max(substring(borrowers.cardnumber,2,7))'};
931 if ( !$cardnumber ) { # If DB has no values,
932 $cardnumber = 1000000; # start at 1000000
938 for ( my $i = 0 ; $i < 8 ; $i += 1 ) {
940 # read weightings, left to right, 1 char at a time
941 my $temp1 = $weightings[$i];
943 # sequence left to right, 1 char at a time
944 my $temp2 = substr( $cardnumber, $i, 1 );
946 # mult each char 1-7 by its corresponding weighting
947 $sum += $temp1 * $temp2;
950 $rem = ( $sum % 11 );
951 $rem = 'X' if $rem == 10;
953 $cardnumber = "V$cardnumber$rem";
957 # MODIFIED BY JF: mysql4.1 allows casting as an integer, which is probably
958 # better. I'll leave the original in in case it needs to be changed for you
961 "select max(cast(cardnumber as signed)) from borrowers");
963 #my $sth=$dbh->prepare("select max(borrowers.cardnumber) from borrowers");
967 $cardnumber="V$cardnumber$rem";
972 sub fixupneu_cardnumber{
973 my($cardnumber,$categorycode) = @_;
974 my $autonumber_members = C4::Context->boolean_preference('autoMemberNum');
975 $autonumber_members = 0 unless defined $autonumber_members;
976 # Find out whether member numbers should be generated
977 # automatically. Should be either "1" or something else.
978 # Defaults to "0", which is interpreted as "no".
979 my $dbh = C4::Context->dbh;
981 if (! $cardnumber && $autonumber_members && $categorycode) {
982 if ($categorycode eq "A" || $categorycode eq "W" ){
983 $sth=$dbh->prepare("select max(borrowers.cardnumber) from borrowers where borrowers.cardnumber like '5%' ");
984 }elsif ($categorycode eq "L"){
985 $sth=$dbh->prepare("select max(borrowers.cardnumber) from borrowers where borrowers.cardnumber like '10%' ");
986 }elsif ($categorycode eq "F" || $categorycode eq "E") {
987 $sth=$dbh->prepare("select max(borrowers.cardnumber) from borrowers where borrowers.cardnumber like '30%' ");
988 }elsif ($categorycode eq "N"){
989 $sth=$dbh->prepare("select max(borrowers.cardnumber) from borrowers where borrowers.cardnumber like '40%' ");
990 }elsif ($categorycode eq "C"){
991 $sth=$dbh->prepare("select max(borrowers.cardnumber) from borrowers where borrowers.cardnumber like '80%' ");
994 $sth=$dbh->prepare("select max(borrowers.cardnumber) from borrowers where borrowers.cardnumber like '6%' ");
998 my $data=$sth->fetchrow_hashref;
999 $cardnumber=$data->{'max(borrowers.cardnumber)'};
1002 # purpose: generate checksum'd member numbers.
1003 # We'll assume we just got the max value of digits 2-8 of member #'s
1004 # from the database and our job is to increment that by one,
1005 # determine the 1st and 9th digits and return the full string.
1007 if (! $cardnumber) { # If DB has no values,
1008 if ($categorycode eq "A" || $categorycode eq "W" ){ $cardnumber = 5000000;}
1009 elsif ($categorycode eq "L"){ $cardnumber = 1000000;}
1010 elsif ($categorycode eq "F"){ $cardnumber = 3000000;}
1011 elsif ($categorycode eq "C"){ $cardnumber = 8000000;}
1012 else{$cardnumber = 6000000;}
1013 # start at 1000000 or 3000000 or 5000000
1023 =item GuarantornameSearch
1025 ($count, $borrowers) = &GuarantornameSearch($env, $searchstring, $type);
1027 Looks up guarantor by name.
1031 BUGFIX 499: C<$type> is now used to determine type of search.
1032 if $type is "simple", search is performed on the first letter of the
1035 C<$searchstring> is a space-separated list of search terms. Each term
1036 must match the beginning a borrower's surname, first name, or other
1039 C<&GuarantornameSearch> returns a two-element list. C<$borrowers> is a
1040 reference-to-array; each element is a reference-to-hash, whose keys
1041 are the fields of the C<borrowers> table in the Koha database.
1042 C<$count> is the number of elements in C<$borrowers>.
1044 return all info from guarantor =>only category_type A
1049 #used by member enquiries from the intranet
1050 #called by guarantor_search.pl
1051 sub GuarantornameSearch {
1052 my ( $env, $searchstring, $orderby, $type ) = @_;
1053 my $dbh = C4::Context->dbh;
1059 if ( $type eq "simple" ) # simple search for one letter only
1062 "Select * from borrowers,categories where borrowers.categorycode=categories.categorycode and category_type='A' and surname like ? order by $orderby";
1063 @bind = ("$searchstring%");
1065 else # advanced search looking in surname, firstname and othernames
1067 @data = split( ' ', $searchstring );
1069 $query = "Select * from borrowers,categories
1070 where ((surname like ? or surname like ?
1071 or firstname like ? or firstname like ?
1072 or othernames like ? or othernames like ?) and borrowers.categorycode=categories.categorycode and category_type='A'
1075 "$data[0]%", "% $data[0]%", "$data[0]%", "% $data[0]%",
1076 "$data[0]%", "% $data[0]%"
1078 for ( my $i = 1 ; $i < $count ; $i++ ) {
1079 $query = $query . " and (" . " surname like ? or surname like ?
1080 or firstname like ? or firstname like ?
1081 or othernames like ? or othernames like ?)";
1083 "$data[$i]%", "% $data[$i]%", "$data[$i]%",
1084 "% $data[$i]%", "$data[$i]%", "% $data[$i]%" );
1088 $query = $query . ") or cardnumber like ?
1090 push( @bind, $searchstring );
1095 my $sth = $dbh->prepare($query);
1096 $sth->execute(@bind);
1098 my $cnt = $sth->rows;
1099 while ( my $data = $sth->fetchrow_hashref ) {
1100 push( @results, $data );
1105 return ( $cnt, \@results );
1109 =item findguarantees
1111 ($num_children, $children_arrayref) = &findguarantees($parent_borrno);
1112 $child0_cardno = $children_arrayref->[0]{"cardnumber"};
1113 $child0_borrno = $children_arrayref->[0]{"borrowernumber"};
1115 C<&findguarantees> takes a borrower number (e.g., that of a patron
1116 with children) and looks up the borrowers who are guaranteed by that
1117 borrower (i.e., the patron's children).
1119 C<&findguarantees> returns two values: an integer giving the number of
1120 borrowers guaranteed by C<$parent_borrno>, and a reference to an array
1121 of references to hash, which gives the actual results.
1127 my $dbh = C4::Context->dbh;
1128 my $sth=$dbh->prepare("select cardnumber,borrowernumber, firstname, surname from borrowers where guarantor=?");
1129 $sth->execute($bornum);
1132 while (my $data = $sth->fetchrow_hashref)
1137 return (scalar(@dat), \@dat);
1142 $guarantor = &findguarantor($borrower_no);
1143 $guarantor_cardno = $guarantor->{"cardnumber"};
1144 $guarantor_surname = $guarantor->{"surname"};
1147 C<&findguarantor> takes a borrower number (presumably that of a child
1148 patron), finds the guarantor for C<$borrower_no> (the child's parent),
1149 and returns the record for the guarantor.
1151 C<&findguarantor> returns a reference-to-hash. Its keys are the fields
1152 from the C<borrowers> database table;
1158 my $dbh = C4::Context->dbh;
1159 my $sth=$dbh->prepare("select guarantor from borrowers where borrowernumber=?");
1160 $sth->execute($bornum);
1161 my $data=$sth->fetchrow_hashref;
1163 $sth=$dbh->prepare("Select * from borrowers where borrowernumber=?");
1164 $sth->execute($data->{'guarantor'});
1165 $data=$sth->fetchrow_hashref;
1170 sub borrowercard_active {
1172 my $dbh = C4::Context->dbh;
1173 my $sth = $dbh->prepare("SELECT expiry FROM borrowers WHERE (borrowernumber = ?) AND (NOW() <= expiry)");
1174 $sth->execute($bornum);
1175 if (my $data=$sth->fetchrow_hashref){
1182 # Search the member photo, in case that photo doesn´t exists, return a default photo.for NEU
1183 sub getMemberPhoto {
1184 my $cardnumber = shift @_;
1185 my $htdocs = C4::Context->config('opacdir');
1186 my $dirname = $htdocs."/htdocs/uploaded-files/users-photo/";
1187 # my $dirname = "$ENV{'DOCUMENT_ROOT'}/uploaded-files/users-photo";
1188 opendir(DIR, $dirname) or die "Can't open directory $dirname: $!";
1189 while (defined(my $file = readdir(DIR))) {
1190 if ($file =~ /^$cardnumber\..+/){
1191 return "/uploaded-files/users-photo/$file";
1195 return "http://cc.neu.edu.tr/stdpictures/".$cardnumber.".jpg";
1198 sub change_user_pass {
1199 my ($uid,$member,$digest) = @_;
1200 my $dbh = C4::Context->dbh;
1201 #Make sure the userid chosen is unique and not theirs if non-empty. If it is not,
1202 #Then we need to tell the user and have them create a new one.
1203 my $sth=$dbh->prepare("select * from borrowers where userid=? and borrowernumber <> ?");
1204 $sth->execute($uid,$member);
1205 if ( ($uid ne '') && ($sth->fetchrow) ) {
1209 #Everything is good so we can update the information.
1210 $sth=$dbh->prepare("update borrowers set userid=?, password=? where borrowernumber=?");
1211 $sth->execute($uid, $digest, $member);
1214 =head2 checkuniquemember (OUEST-PROVENCE)
1216 $result = &checkuniquemember($collectivity,$surname,$categorycode,$firstname,$dateofbirth);
1218 Checks that a member exists or not in the database.
1220 C<&result> is 1 (=exist) or 0 (=does not exist)
1221 C<&collectivity> is 1 (= we add a collectivity) or 0 (= we add a physical member)
1222 C<&surname> is the surname
1223 C<&categorycode> is from categorycode table
1224 C<&firstname> is the firstname (only if collectivity=0)
1225 C<&dateofbirth> is the date of birth (only if collectivity=0)
1229 sub checkuniquemember {
1230 my ( $collectivity, $surname, $firstname, $dateofbirth ) = @_;
1231 my $dbh = C4::Context->dbh;
1233 if ($collectivity) {
1235 # $request="select count(*) from borrowers where surname=? and categorycode=?";
1237 "select borrowernumber,categorycode from borrowers where surname=? ";
1241 # $request="select count(*) from borrowers where surname=? and categorycode=? and firstname=? and dateofbirth=?";
1243 "select borrowernumber,categorycode from borrowers where surname=? and firstname=? and dateofbirth=?";
1245 my $sth = $dbh->prepare($request);
1246 if ($collectivity) {
1247 $sth->execute( uc($surname) );
1250 $sth->execute( uc($surname), ucfirst($firstname), $dateofbirth );
1252 my @data = $sth->fetchrow;
1255 return $data[0], $data[1];
1264 =head2 getzipnamecity (OUEST-PROVENCE)
1266 take all info from table city for the fields city and zip
1267 check for the name and the zip code of the city selected
1271 sub getzipnamecity {
1273 my $dbh = C4::Context->dbh;
1276 "select city_name,city_zipcode from cities where cityid=? ");
1277 $sth->execute($cityid);
1278 my @data = $sth->fetchrow;
1279 return $data[0], $data[1];
1282 =head2 updatechildguarantor (OUEST-PROVENCE)
1284 check for title,firstname,surname,adress,zip code and city from guarantor to
1291 sub getguarantordata {
1292 my ($borrowerid) = @_;
1293 my $dbh = C4::Context->dbh;
1296 "Select title,firstname,surname,streetnumber,address,streettype,address2,zipcode,city,phone,phonepro,mobile,email,emailpro,fax from borrowers where borrowernumber =? "
1298 $sth->execute($borrowerid);
1299 my $guarantor_data = $sth->fetchrow_hashref;
1301 return $guarantor_data;
1304 =head2 getdcity (OUEST-PROVENCE)
1305 recover cityid with city_name condition
1309 my ($city_name) = @_;
1310 my $dbh = C4::Context->dbh;
1311 my $sth = $dbh->prepare("select cityid from cities where city_name=? ");
1312 $sth->execute($city_name);
1313 my $data = $sth->fetchrow;
1317 =head2 getcategorytype (OUEST-PROVENCE)
1319 check for the category_type with categorycode
1320 and return the category_type
1324 sub getcategorytype {
1325 my ($categorycode) = @_;
1326 my $dbh = C4::Context->dbh;
1329 "Select category_type,description from categories where categorycode=? "
1331 $sth->execute($categorycode);
1332 my ( $category_type, $description ) = $sth->fetchrow;
1333 return $category_type, $description;
1342 # # A better approach might be to set borrowernumber autoincrement and
1344 sub NewBorrowerNumber {
1345 my $dbh = C4::Context->dbh;
1346 my $sth=$dbh->prepare("Select max(borrowernumber) from borrowers");
1348 my $data=$sth->fetchrow_hashref;
1350 $data->{'max(borrowernumber)'}++;
1351 return($data->{'max(borrowernumber)'});
1354 =head2 ethnicitycategories
1356 ($codes_arrayref, $labels_hashref) = ðnicitycategories();
1358 Looks up the different ethnic types in the database. Returns two
1359 elements: a reference-to-array, which lists the ethnicity codes, and a
1360 reference-to-hash, which maps the ethnicity codes to ethnicity
1367 sub ethnicitycategories {
1368 my $dbh = C4::Context->dbh;
1369 my $sth = $dbh->prepare("Select code,name from ethnicity order by name");
1373 while ( my $data = $sth->fetchrow_hashref ) {
1374 push @codes, $data->{'code'};
1375 $labels{ $data->{'code'} } = $data->{'name'};
1378 return ( \@codes, \%labels );
1383 $ethn_name = &fixEthnicity($ethn_code);
1385 Takes an ethnicity code (e.g., "european" or "pi") and returns the
1386 corresponding descriptive name from the C<ethnicity> table in the
1387 Koha database ("European" or "Pacific Islander").
1393 sub fixEthnicity($) {
1395 my $ethnicity = shift;
1396 my $dbh = C4::Context->dbh;
1397 my $sth = $dbh->prepare("Select name from ethnicity where code = ?");
1398 $sth->execute($ethnicity);
1399 my $data = $sth->fetchrow_hashref;
1401 return $data->{'name'};
1402 } # sub fixEthnicity
1408 $dateofbirth,$date = &get_age($date);
1410 this function return the borrowers age with the value of dateofbirth
1415 my ($date, $date_ref) = @_;
1417 if (not defined $date_ref) {
1418 $date_ref = sprintf('%04d-%02d-%02d', Today());
1421 my ($year1, $month1, $day1) = split /-/, $date;
1422 my ($year2, $month2, $day2) = split /-/, $date_ref;
1424 my $age = $year2 - $year1;
1425 if ($month1.$day1 > $month2.$day2) {
1434 =head2 get_institutions
1435 $insitutions = get_institutions();
1437 Just returns a list of all the borrowers of type I, borrownumber and name
1441 sub get_institutions {
1442 my $dbh = C4::Context->dbh();
1445 "SELECT borrowernumber,surname FROM borrowers WHERE categorycode=? ORDER BY surname"
1449 while ( my $data = $sth->fetchrow_hashref() ) {
1450 $orgs{ $data->{'borrowernumber'} } = $data;
1455 } # sub get_institutions
1457 =head2 add_member_orgs
1459 add_member_orgs($borrowernumber,$borrowernumbers);
1461 Takes a borrowernumber and a list of other borrowernumbers and inserts them into the borrowers_to_borrowers table
1466 sub add_member_orgs {
1467 my ( $borrowernumber, $otherborrowers ) = @_;
1468 my $dbh = C4::Context->dbh();
1470 "INSERT INTO borrowers_to_borrowers (borrower1,borrower2) VALUES (?,?)";
1471 my $sth = $dbh->prepare($query);
1472 foreach my $bornum (@$otherborrowers) {
1473 $sth->execute( $borrowernumber, $bornum );
1477 } # sub add_member_orgs
1479 =head2 GetBorrowersFromSurname
1483 \@resutlts = GetBorrowersFromSurname($surname)
1484 this function get the list of borrower names like $surname.
1486 the table of results in @results
1491 sub GetBorrowersFromSurname {
1492 my ($searchstring)=@_;
1493 my $dbh = C4::Context->dbh;
1494 $searchstring=~ s/\'/\\\'/g;
1495 my @data=split(' ',$searchstring);
1498 SELECT surname,firstname
1500 WHERE (surname like ?)
1503 my $sth=$dbh->prepare($query);
1504 $sth->execute("$data[0]%");
1507 while (my $data=$sth->fetchrow_hashref){
1508 push(@results,$data);
1512 return ($count,\@results);
1515 =head2 expand_sex_into_predicate
1517 $data{&expand_sex_into_predicate($data{sex})} = 1;
1519 Converts a single 'M' or 'F' into 'sex_M_p' or 'sex_F_p'
1522 In some languages, 'M' and 'F' are not appropriate. However,
1523 with HTML::Template, there is no way to localize 'M' or 'F'
1524 unless these are converted into variables that TMPL_IF can
1525 understand. This function provides this conversion.
1529 sub expand_sex_into_predicate ($) {
1531 return "sex_${sex}_p";
1532 } # expand_sex_into_predicate