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
27 use Digest::MD5 qw(md5_base64);
29 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK);
35 C4::Members - Perl Module containing convenience functions for member handling
53 &BornameSearch &getmember &borrdata &borrdata2 &fixup_cardnumber &findguarantees &findguarantor &GuarantornameSearch &NewBorrowerNumber &modmember &newmember &changepassword &borrissues &allissues
54 &checkuniquemember &getzipnamecity &getidcity &getguarantordata &getcategorytype
55 &calcexpirydate &checkuserpassword
57 &borrowercategories &getborrowercategory
65 ($count, $borrowers) = &BornameSearch($env, $searchstring, $type);
67 Looks up patrons (borrowers) by name.
71 BUGFIX 499: C<$type> is now used to determine type of search.
72 if $type is "simple", search is performed on the first letter of the
75 C<$searchstring> is a space-separated list of search terms. Each term
76 must match the beginning a borrower's surname, first name, or other
79 C<&BornameSearch> returns a two-element list. C<$borrowers> is a
80 reference-to-array; each element is a reference-to-hash, whose keys
81 are the fields of the C<borrowers> table in the Koha database.
82 C<$count> is the number of elements in C<$borrowers>.
86 #used by member enquiries from the intranet
89 my ($env,$searchstring,$orderby,$type)=@_;
90 my $dbh = C4::Context->dbh;
91 my $query = ""; my $count; my @data;
94 if($type eq "simple") # simple search for one letter only
96 $query="Select * from borrowers where surname like ? order by $orderby";
97 @bind=("$searchstring%");
99 else # advanced search looking in surname, firstname and othernames
101 @data=split(' ',$searchstring);
103 $query="Select * from borrowers
104 where ((surname like ? or surname like ?
105 or firstname like ? or firstname like ?
106 or othernames like ? or othernames like ?)
108 @bind=("$data[0]%","% $data[0]%","$data[0]%","% $data[0]%","$data[0]%","% $data[0]%");
109 for (my $i=1;$i<$count;$i++){
110 $query=$query." and (".
111 " surname like ? or surname like ?
112 or firstname like ? or firstname like ?
113 or othernames like ? or othernames like ?)";
114 push(@bind,"$data[$i]%","% $data[$i]%","$data[$i]%","% $data[$i]%","$data[$i]%","% $data[$i]%");
117 $query=$query.") or cardnumber like ?
119 push(@bind,$searchstring);
123 my $sth=$dbh->prepare($query);
124 # warn "Q $orderby : $query";
125 $sth->execute(@bind);
128 while (my $data=$sth->fetchrow_hashref){
129 push(@results,$data);
133 return ($cnt,\@results);
138 $borrower = &getmember($cardnumber, $borrowernumber);
140 Looks up information about a patron (borrower) by either card number
141 or borrower number. If $borrowernumber is specified, C<&borrdata>
142 searches by borrower number; otherwise, it searches by card number.
144 C<&getmember> returns a reference-to-hash whose keys are the fields of
145 the C<borrowers> table in the Koha database.
150 my ($cardnumber,$bornum)=@_;
151 $cardnumber = uc $cardnumber;
152 my $dbh = C4::Context->dbh;
155 $sth=$dbh->prepare("Select * from borrowers where cardnumber=?");
156 $sth->execute($cardnumber);
158 $sth=$dbh->prepare("Select * from borrowers where borrowernumber=?");
159 $sth->execute($bornum);
161 my $data=$sth->fetchrow_hashref;
165 } else { # try with firstname
167 my $sth=$dbh->prepare("select * from borrowers where firstname=?");
168 $sth->execute($cardnumber);
169 my $data=$sth->fetchrow_hashref;
179 $borrower = &borrdata($cardnumber, $borrowernumber);
181 Looks up information about a patron (borrower) by either card number
182 or borrower number. If $borrowernumber is specified, C<&borrdata>
183 searches by borrower number; otherwise, it searches by card number.
185 C<&borrdata> returns a reference-to-hash whose keys are the fields of
186 the C<borrowers> table in the Koha database.
191 my ($cardnumber,$bornum)=@_;
192 $cardnumber = uc $cardnumber;
193 my $dbh = C4::Context->dbh;
196 $sth=$dbh->prepare("Select borrowers.*,categories.category_type from borrowers left join categories on borrowers.categorycode=categories.categorycode where cardnumber=?");
197 $sth->execute($cardnumber);
199 $sth=$dbh->prepare("Select borrowers.*,categories.category_type from borrowers left join categories on borrowers.categorycode=categories.categorycode where borrowernumber=?");
200 $sth->execute($bornum);
202 my $data=$sth->fetchrow_hashref;
203 warn "DATA".$data->{category_type};
207 } else { # try with firstname
209 my $sth=$dbh->prepare("Select borrowers.*,categories.category_type from borrowers left join categories on borrowers.categorycode=categories.categorycode where firstname=?");
210 $sth->execute($cardnumber);
211 my $data=$sth->fetchrow_hashref;
222 ($borrowed, $due, $fine) = &borrdata2($env, $borrowernumber);
224 Returns aggregate data about items borrowed by the patron with the
225 given borrowernumber.
229 C<&borrdata2> returns a three-element array. C<$borrowed> is the
230 number of books the patron currently has borrowed. C<$due> is the
231 number of overdue items the patron currently has borrowed. C<$fine> is
232 the total fine currently due by the borrower.
237 my ($env,$bornum)=@_;
238 my $dbh = C4::Context->dbh;
239 my $query="Select count(*) from issues where borrowernumber='$bornum' and
242 my $sth=$dbh->prepare($query);
244 my $data=$sth->fetchrow_hashref;
246 $sth=$dbh->prepare("Select count(*) from issues where
247 borrowernumber='$bornum' and date_due < now() and returndate is NULL");
249 my $data2=$sth->fetchrow_hashref;
251 $sth=$dbh->prepare("Select sum(amountoutstanding) from accountlines where
252 borrowernumber='$bornum'");
254 my $data3=$sth->fetchrow_hashref;
257 return($data2->{'count(*)'},$data->{'count(*)'},$data3->{'sum(amountoutstanding)'});
262 my $dbh = C4::Context->dbh;
263 $data{'dateofbirth'}=format_date_in_iso($data{'dateofbirth'});
264 $data{'expiry'}=format_date_in_iso($data{'expiry'});
265 my $query="update borrowers set title='$data{'title'}',expiry='$data{'expiry'}',
266 cardnumber='$data{'cardnumber'}',sex='$data{'sex'}',ethnotes='$data{'ethnicnotes'}',
267 streetaddress='$data{'streetaddress'}',faxnumber='$data{'faxnumber'}',firstname='$data{'firstname'}',
268 altnotes='$data{'altnotes'}',dateofbirth='$data{'dateofbirth'}',contactname='$data{'contactname'}',
269 emailaddress='$data{'emailaddress'}',streetcity='$data{'streetcity'}',
270 altrelationship='$data{'altrelationship'}',othernames='$data{'othernames'}',phoneday='$data{'phoneday'}',
271 categorycode='$data{'categorycode'}',city='$data{'city'}',area='$data{'area'}',phone='$data{'phone'}',
272 borrowernotes='$data{'borrowernotes'}',altphone='$data{'altphone'}',surname='$data{'surname'}',
273 initials='$data{'initials'}',physstreet='$data{'physstreet'}',ethnicity='$data{'ethnicity'}',
274 gonenoaddress='$data{'gna'}',lost='$data{'lost'}',debarred='$data{'debarred'}',
275 textmessaging='$data{'textmessaging'}', branchcode = '$data{'branchcode'}',
276 zipcode = '$data{'zipcode'}',homezipcode='$data{'homezipcode'}', sort1='$data{'sort1'}', sort2='$data{'sort2'}'
277 where borrowernumber=$data{'borrowernumber'}";
278 my $sth=$dbh->prepare($query);
281 # ok if its an adult (type) it may have borrowers that depend on it as a guarantor
282 # so when we update information for an adult we should check for guarantees and update the relevant part
283 # of their records, ie addresses and phone numbers
284 if ($data{'categorycode'} eq 'A' || $data{'categorycode'} eq 'W'){
285 # is adult check guarantees;
286 updateguarantees(%data);
292 my $dbh = C4::Context->dbh;
293 $data{'userid'}='' unless $data{'password'};
294 $data{'password'}=md5_base64($data{'password'}) if $data{'password'};
295 $data{'dateofbirth'}=format_date_in_iso($data{'dateofbirth'});
296 $data{'dateenrolled'}=format_date_in_iso($data{'dateenrolled'});
297 $data{expiry}=format_date_in_iso($data{expiry});
298 my $query="insert into borrowers set cardnumber=".$dbh->quote($data{'cardnumber'}).
299 ",surname=".$dbh->quote($data{'surname'}).
300 ",firstname=".$dbh->quote($data{'firstname'}).
301 ",title=".$dbh->quote($data{'title'}).
302 ",othernames=".$dbh->quote($data{'othernames'}).
303 ",initials=".$dbh->quote($data{'initials'}).
304 ",streetnumber=".$dbh->quote($data{'streetnumber'}).
305 ",streettype=".$dbh->quote($data{'streettype'}).
306 ",address=".$dbh->quote($data{'address'}).
307 ",address2=".$dbh->quote($data{'address2'}).
308 ",zipcode=".$dbh->quote($data{'zipcode'}).
309 ",city=".$dbh->quote($data{'city'}).
310 ",phone=".$dbh->quote($data{'phone'}).
311 ",email=".$dbh->quote($data{'email'}).
312 ",mobile=".$dbh->quote($data{'mobile'}).
313 ",phonepro=".$dbh->quote($data{'phonepro'}).
314 ",opacnote=".$dbh->quote($data{'opacnote'}).
315 ",guarantorid=".$dbh->quote($data{'guarantorid'}).
316 ",dateofbirth=".$dbh->quote($data{'dateofbirth'}).
317 ",branchcode=".$dbh->quote($data{'branchcode'}).
318 ",categorycode=".$dbh->quote($data{'categorycode'}).
319 ",dateenrolled=".$dbh->quote($data{'dateenrolled'}).
320 ",contactname=".$dbh->quote($data{'contactname'}).
321 ",borrowernotes=".$dbh->quote($data{'borrowernotes'}).
322 ",dateexpiry=".$dbh->quote($data{'dateexpiry'}).
323 ",contactnote=".$dbh->quote($data{'contactnote'}).
324 ",b_address=".$dbh->quote($data{'b_address'}).
325 ",b_zipcode=".$dbh->quote($data{'b_zipcode'}).
326 ",b_city=".$dbh->quote($data{'b_city'}).
327 ",b_phone=".$dbh->quote($data{'b_phone'}).
328 ",b_email=".$dbh->quote($data{'b_email'},).
329 ",password=".$dbh->quote($data{'password'}).
330 ",userid=".$dbh->quote($data{'userid'}).
331 ",sort1=".$dbh->quote($data{'sort1'}).
332 ",sort2=".$dbh->quote($data{'sort2'}).
333 ",contacttitle=".$dbh->quote($data{'contacttitle'}).
334 ",emailpro=".$dbh->quote($data{'emailpro'}).
335 ",contactfirstname=".$dbh->quote($data{'contactfirstname'}).
336 ",sex=".$dbh->quote($data{'sex'}).
337 ",fax=".$dbh->quote($data{'fax'}).
338 ",flags=".$dbh->quote($data{'flags'}).
339 ",relationship=".$dbh->quote($data{'relationship'})
341 my $sth=$dbh->prepare($query);
344 $data{'borrowerid'} =$dbh->{'mysql_insertid'};
345 return $data{'borrowerid'};
349 my ($uid,$member,$digest) = @_;
350 my $dbh = C4::Context->dbh;
351 #Make sure the userid chosen is unique and not theirs if non-empty. If it is not,
352 #Then we need to tell the user and have them create a new one.
353 my $sth=$dbh->prepare("select * from borrowers where userid=? and borrowernumber != ?");
354 $sth->execute($uid,$member);
355 if ( ($uid ne '') && ($sth->fetchrow) ) {
358 #Everything is good so we can update the information.
359 $sth=$dbh->prepare("update borrowers set userid=?, password=? where borrowernumber=?");
360 $sth->execute($uid, $digest, $member);
365 sub getmemberfromuserid {
367 my $dbh = C4::Context->dbh;
368 my $sth = $dbh->prepare("select * from borrowers where userid=?");
369 $sth->execute($userid);
370 return $sth->fetchrow_hashref;
372 sub updateguarantees {
374 my $dbh = C4::Context->dbh;
375 my ($count,$guarantees)=findguarantees($data{'borrowernumber'});
376 for (my $i=0;$i<$count;$i++){
378 # It looks like the $i is only being returned to handle walking through
379 # the array, which is probably better done as a foreach loop.
381 my $guaquery="update borrowers set streetaddress='$data{'address'}',faxnumber='$data{'faxnumber'}',
382 streetcity='$data{'streetcity'}',phoneday='$data{'phoneday'}',city='$data{'city'}',area='$data{'area'}',phone='$data{'phone'}'
383 ,streetaddress='$data{'address'}'
384 where borrowernumber='$guarantees->[$i]->{'borrowernumber'}'";
385 my $sth3=$dbh->prepare($guaquery);
390 ################################################################################
392 =item fixup_cardnumber
394 Warning: The caller is responsible for locking the members table in write
395 mode, to avoid database corruption.
399 use vars qw( @weightings );
400 my @weightings = (8,4,6,3,5,2,1);
402 sub fixup_cardnumber ($) {
403 my($cardnumber) = @_;
404 my $autonumber_members = C4::Context->boolean_preference('autoMemberNum');
405 $autonumber_members = 0 unless defined $autonumber_members;
406 # Find out whether member numbers should be generated
407 # automatically. Should be either "1" or something else.
408 # Defaults to "0", which is interpreted as "no".
410 # if ($cardnumber !~ /\S/ && $autonumber_members) {
411 if ($autonumber_members) {
412 my $dbh = C4::Context->dbh;
413 if (C4::Context->preference('checkdigit') eq 'katipo') {
414 # if checkdigit is selected, calculate katipo-style cardnumber.
415 # otherwise, just use the max()
416 # purpose: generate checksum'd member numbers.
417 # We'll assume we just got the max value of digits 2-8 of member #'s
418 # from the database and our job is to increment that by one,
419 # determine the 1st and 9th digits and return the full string.
420 my $sth=$dbh->prepare("select max(substring(borrowers.cardnumber,2,7)) from borrowers");
423 my $data=$sth->fetchrow_hashref;
424 $cardnumber=$data->{'max(substring(borrowers.cardnumber,2,7))'};
426 if (! $cardnumber) { # If DB has no values,
427 $cardnumber = 1000000; # start at 1000000
433 for (my $i = 0; $i < 8; $i += 1) {
434 # read weightings, left to right, 1 char at a time
435 my $temp1 = $weightings[$i];
437 # sequence left to right, 1 char at a time
438 my $temp2 = substr($cardnumber,$i,1);
440 # mult each char 1-7 by its corresponding weighting
441 $sum += $temp1 * $temp2;
445 $rem = 'X' if $rem == 10;
447 $cardnumber="V$cardnumber$rem";
449 # MODIFIED BY JF: mysql4.1 allows casting as an integer, which is probably
450 # better. I'll leave the original in in case it needs to be changed for you
451 my $sth=$dbh->prepare("select max(cast(cardnumber as signed)) from borrowers");
452 #my $sth=$dbh->prepare("select max(borrowers.cardnumber) from borrowers");
456 my ($result)=$sth->fetchrow;
458 $cardnumber=$result+1;
466 my $dbh = C4::Context->dbh;
467 my $sth=$dbh->prepare("select cardnumber,borrowernumber from borrowers where
469 $sth->execute($bornum);
472 while (my $data=$sth->fetchrow_hashref){
482 $guarantor = &findguarantor($borrower_no);
483 $guarantor_cardno = $guarantor->{"cardnumber"};
484 $guarantor_surname = $guarantor->{"surname"};
487 C<&findguarantor> takes a borrower number (presumably that of a child
488 patron), finds the guarantor for C<$borrower_no> (the child's parent),
489 and returns the record for the guarantor.
491 C<&findguarantor> returns a reference-to-hash. Its keys are the fields
492 from the C<borrowers> database table;
498 my $dbh = C4::Context->dbh;
499 my $sth=$dbh->prepare("Select * from borrowers where borrowernumber=?");
500 $sth->execute($bornum);
501 my $data=$sth->fetchrow_hashref;
506 =item GuarantornameSearch
508 ($count, $borrowers) = &GuarantornameSearch($env, $searchstring, $type);
510 Looks up guarantor by name.
514 BUGFIX 499: C<$type> is now used to determine type of search.
515 if $type is "simple", search is performed on the first letter of the
518 C<$searchstring> is a space-separated list of search terms. Each term
519 must match the beginning a borrower's surname, first name, or other
522 C<&GuarantornameSearch> returns a two-element list. C<$borrowers> is a
523 reference-to-array; each element is a reference-to-hash, whose keys
524 are the fields of the C<borrowers> table in the Koha database.
525 C<$count> is the number of elements in C<$borrowers>.
527 return all info from guarantor =>only category_type A
531 #used by member enquiries from the intranet
532 #called by guarantor_search.pl
533 sub GuarantornameSearch {
534 my ($env,$searchstring,$orderby,$type)=@_;
535 my $dbh = C4::Context->dbh;
536 my $query = ""; my $count; my @data;
539 if($type eq "simple") # simple search for one letter only
541 $query="Select * from borrowers,categories where borrowers.categorycode=categories.categorycode and category_type='A' and surname like ? order by $orderby";
542 @bind=("$searchstring%");
544 else # advanced search looking in surname, firstname and othernames
546 @data=split(' ',$searchstring);
548 $query="Select * from borrowers,categories
549 where ((surname like ? or surname like ?
550 or firstname like ? or firstname like ?
551 or othernames like ? or othernames like ?) and borrowers.categorycode=categories.categorycode and category_type='A'
553 @bind=("$data[0]%","% $data[0]%","$data[0]%","% $data[0]%","$data[0]%","% $data[0]%");
554 for (my $i=1;$i<$count;$i++){
555 $query=$query." and (".
556 " surname like ? or surname like ?
557 or firstname like ? or firstname like ?
558 or othernames like ? or othernames like ?)";
559 push(@bind,"$data[$i]%","% $data[$i]%","$data[$i]%","% $data[$i]%","$data[$i]%","% $data[$i]%");
562 $query=$query.") or cardnumber like ?
564 push(@bind,$searchstring);
568 my $sth=$dbh->prepare($query);
569 $sth->execute(@bind);
572 while (my $data=$sth->fetchrow_hashref){
573 push(@results,$data);
577 return ($cnt,\@results);
580 =item NewBorrowerNumber
582 $num = &NewBorrowerNumber();
584 Allocates a new, unused borrower number, and returns it.
588 # FIXME - This is identical to C4::Circulation::Borrower::NewBorrowerNumber.
589 # Pick one and stick with it. Preferably use the other one. This function
590 # doesn't belong in C4::Search.
591 sub NewBorrowerNumber {
592 my $dbh = C4::Context->dbh;
593 my $sth=$dbh->prepare("Select max(borrowernumber) from borrowers");
595 my $data=$sth->fetchrow_hashref;
597 $data->{'max(borrowernumber)'}++;
598 return($data->{'max(borrowernumber)'});
603 ($count, $issues) = &borrissues($borrowernumber);
605 Looks up what the patron with the given borrowernumber has borrowed.
607 C<&borrissues> returns a two-element array. C<$issues> is a
608 reference-to-array, where each element is a reference-to-hash; the
609 keys are the fields from the C<issues>, C<biblio>, and C<items> tables
610 in the Koha database. C<$count> is the number of elements in
617 my $dbh = C4::Context->dbh;
618 my $sth=$dbh->prepare("Select * from issues,biblio,items where borrowernumber=?
619 and items.itemnumber=issues.itemnumber
620 and items.biblionumber=biblio.biblionumber
621 and issues.returndate is NULL order by date_due");
622 $sth->execute($bornum);
624 while (my $data = $sth->fetchrow_hashref) {
628 return(scalar(@result), \@result);
633 ($count, $issues) = &allissues($borrowernumber, $sortkey, $limit);
635 Looks up what the patron with the given borrowernumber has borrowed,
636 and sorts the results.
638 C<$sortkey> is the name of a field on which to sort the results. This
639 should be the name of a field in the C<issues>, C<biblio>,
640 C<biblioitems>, or C<items> table in the Koha database.
642 C<$limit> is the maximum number of results to return.
644 C<&allissues> returns a two-element array. C<$issues> is a
645 reference-to-array, where each element is a reference-to-hash; the
646 keys are the fields from the C<issues>, C<biblio>, C<biblioitems>, and
647 C<items> tables of the Koha database. C<$count> is the number of
648 elements in C<$issues>
653 my ($bornum,$order,$limit)=@_;
654 #FIXME: sanity-check order and limit
655 my $dbh = C4::Context->dbh;
656 my $query="Select * from issues,biblio,items,biblioitems
657 where borrowernumber=? and
658 items.biblioitemnumber=biblioitems.biblioitemnumber and
659 items.itemnumber=issues.itemnumber and
660 items.biblionumber=biblio.biblionumber order by $order";
662 $query.=" limit $limit";
665 my $sth=$dbh->prepare($query);
666 $sth->execute($bornum);
669 while (my $data=$sth->fetchrow_hashref){
677 =item getboracctrecord
679 ($count, $acctlines, $total) = &getboracctrecord($env, $borrowernumber);
681 Looks up accounting data for the patron with the given borrowernumber.
685 (FIXME - I'm not at all sure what this is about.)
687 C<&getboracctrecord> returns a three-element array. C<$acctlines> is a
688 reference-to-array, where each element is a reference-to-hash; the
689 keys are the fields of the C<accountlines> table in the Koha database.
690 C<$count> is the number of elements in C<$acctlines>. C<$total> is the
691 total amount outstanding for all of the account lines.
695 sub getboracctrecord {
696 my ($env,$params) = @_;
697 my $dbh = C4::Context->dbh;
700 my $sth=$dbh->prepare("Select * from accountlines where
701 borrowernumber=? order by date desc,timestamp desc");
703 $sth->execute($params->{'borrowernumber'});
705 while (my $data=$sth->fetchrow_hashref){
706 #FIXME before reinstating: insecure?
707 # if ($data->{'itemnumber'} ne ''){
708 # $query="Select * from items,biblio where items.itemnumber=
709 # '$data->{'itemnumber'}' and biblio.biblionumber=items.biblionumber";
710 # my $sth2=$dbh->prepare($query);
712 # my $data2=$sth2->fetchrow_hashref;
716 $acctlines[$numlines] = $data;
718 $total += $data->{'amountoutstanding'};
721 return ($numlines,\@acctlines,$total);
725 =head2 checkuniquemember (OUEST-PROVENCE)
727 $result = &checkuniquemember($collectivity,$surname,$categorycode,$firstname,$dateofbirth);
729 Checks that a member exists or not in the database.
731 C<&result> is 1 (=exist) or 0 (=does not exist)
732 C<&collectivity> is 1 (= we add a collectivity) or 0 (= we add a physical member)
733 C<&surname> is the surname
734 C<&categorycode> is from categorycode table
735 C<&firstname> is the firstname (only if collectivity=0)
736 C<&dateofbirth> is the date of birth (only if collectivity=0)
740 sub checkuniquemember{
741 my ($collectivity,$surname,$firstname,$dateofbirth)=@_;
742 my $dbh = C4::Context->dbh;
744 if ($collectivity ) {
745 # $request="select count(*) from borrowers where surname=? and categorycode=?";
746 $request="select borrowernumber,categorycode from borrowers where surname=? ";
748 # $request="select count(*) from borrowers where surname=? and categorycode=? and firstname=? and dateofbirth=?";
749 $request="select borrowernumber,categorycode from borrowers where surname=? and firstname=? and dateofbirth=?";
751 my $sth=$dbh->prepare($request);
753 $sth->execute(uc($surname));
755 $sth->execute(uc($surname),ucfirst($firstname),$dateofbirth);
757 my @data= $sth->fetchrow;
760 return $data[0],$data[1];
768 =head2 getzipnamecity (OUEST-PROVENCE)
770 take all info from table city for the fields city and zip
771 check for the name and the zip code of the city selected
776 my $dbh = C4::Context->dbh;
777 my $sth=$dbh->prepare("select city_name,city_zipcode from cities where cityid=? ");
778 $sth->execute($cityid);
779 my @data = $sth->fetchrow;
780 return $data[0],$data[1];
783 =head2 updatechildguarantor (OUEST-PROVENCE)
785 check for title,firstname,surname,adress,zip code and city from guarantor to
789 sub getguarantordata{
791 my $dbh = C4::Context->dbh;
792 my $sth=$dbh->prepare("Select title,firstname,surname,streetnumber,address,streettype,address2,zipcode,city,phone,phonepro,mobile,email,emailpro from borrowers where borrowernumber =? ");
793 $sth->execute($borrowerid);
794 my $guarantor_data=$sth->fetchrow_hashref;
796 return $guarantor_data;
799 =head2 getdcity (OUEST-PROVENCE)
800 recover cityid with city_name condition
804 my $dbh = C4::Context->dbh;
805 my $sth=$dbh->prepare("select cityid from cities where city_name=? ");
806 $sth->execute($city_name);
807 my $data = $sth->fetchrow;
812 =head2 getcategorytype (OUEST-PROVENCE)
814 check for the category_type with categorycode
815 and return the category_type
818 sub getcategorytype {
819 my ($categorycode)=@_;
820 my $dbh = C4::Context->dbh;
821 my $sth=$dbh->prepare("Select category_type,description from categories where categorycode=? ");
822 $sth->execute($categorycode);
823 my ($category_type,$description) = $sth->fetchrow;
824 return $category_type,$description;
828 my ($categorycode,$dateenrolled)=@_;
829 my $dbh=C4::Context->dbh;
830 my $sth = $dbh->prepare("select enrolmentperiod from categories where categorycode=?");
831 $sth->execute($categorycode);
832 my ($enrolmentperiod) = $sth->fetchrow;
833 $enrolmentperiod = 12 unless ($enrolmentperiod);
834 return format_date_in_iso(&DateCalc($dateenrolled,"$enrolmentperiod months"));
837 =head2 checkuserpassword (OUEST-PROVENCE)
839 check for the password and login are not used
840 return the number of record
841 0=> NOT USED 1=> USED
844 sub checkuserpassword{
845 my ($borrowerid,$userid,$password)=@_;
846 $password=md5_base64($password);
847 my $dbh = C4::Context->dbh;
848 my $sth=$dbh->prepare("Select count(*) from borrowers where borrowernumber !=? and userid =? and password=? ");
849 $sth->execute($borrowerid,$userid,$password);
850 my $number_rows=$sth->fetchrow;
855 =head2 borrowercategories
857 ($codes_arrayref, $labels_hashref) = &borrowercategories();
859 Looks up the different types of borrowers in the database. Returns two
860 elements: a reference-to-array, which lists the borrower category
861 codes, and a reference-to-hash, which maps the borrower category codes
862 to category descriptions.
866 sub borrowercategories {
867 my ($category_type,$action)=@_;
868 my $dbh = C4::Context->dbh;
870 $request="Select categorycode,description from categories where category_type=? order by categorycode";
871 my $sth=$dbh->prepare($request);
872 $sth->execute($category_type);
875 while (my $data=$sth->fetchrow_hashref){
876 push @codes,$data->{'categorycode'};
877 $labels{$data->{'categorycode'}}=$data->{'description'};
880 return(\@codes,\%labels);
883 =item getborrowercategory
885 $description = &getborrowercategory($categorycode);
887 Given the borrower's category code, the function returns the corresponding
888 description for a comprehensive information display.
892 sub getborrowercategory
895 my $dbh = C4::Context->dbh;
896 my $sth = $dbh->prepare("SELECT description FROM categories WHERE categorycode = ?");
897 $sth->execute($catcode);
898 my $description = $sth->fetchrow();
901 } # sub getborrowercategory
904 =head2 ethnicitycategories
906 ($codes_arrayref, $labels_hashref) = ðnicitycategories();
908 Looks up the different ethnic types in the database. Returns two
909 elements: a reference-to-array, which lists the ethnicity codes, and a
910 reference-to-hash, which maps the ethnicity codes to ethnicity
916 sub ethnicitycategories {
917 my $dbh = C4::Context->dbh;
918 my $sth=$dbh->prepare("Select code,name from ethnicity order by name");
922 while (my $data=$sth->fetchrow_hashref){
923 push @codes,$data->{'code'};
924 $labels{$data->{'code'}}=$data->{'name'};
927 return(\@codes,\%labels);
932 $ethn_name = &fixEthnicity($ethn_code);
934 Takes an ethnicity code (e.g., "european" or "pi") and returns the
935 corresponding descriptive name from the C<ethnicity> table in the
936 Koha database ("European" or "Pacific Islander").
941 sub fixEthnicity($) {
943 my $ethnicity = shift;
944 my $dbh = C4::Context->dbh;
945 my $sth=$dbh->prepare("Select name from ethnicity where code = ?");
946 $sth->execute($ethnicity);
947 my $data=$sth->fetchrow_hashref;
949 return $data->{'name'};