C4/Members.pm

   1 # -*- tab-width: 8 -*-
   2
   3 package C4::Members;
   4
   5 # Copyright 2000-2003 Katipo Communications
   6 #
   7 # This file is part of Koha.
   8 #
   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
  12 # version.
  13 #
  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.
  17 #
  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
  21
  22 # $Id$
  23
  24 use strict;
  25 require Exporter;
  26 use C4::Context;
  27 use Date::Manip;
  28 use C4::Date;
  29 use Digest::MD5 qw(md5_base64);
  30
  31 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK);
  32
  33 $VERSION = do { my @v = '$Revision$' =~ /\d+/g; shift(@v) . "." . join("_", map {sprintf "%03d", $_ } @v); };
  34
  35 =head1 NAME
  36
  37 C4::Members - Perl Module containing convenience functions for member handling
  38
  39 =head1 SYNOPSIS
  40
  41
  42 =head1 DESCRIPTION
  43
  44
  45 =head1 FUNCTIONS
  46
  47 =over 2
  48
  49 =cut
  50
  51 @ISA = qw(Exporter);
  52 @EXPORT = qw();
  53
  54 @EXPORT = qw(
  55         &BornameSearch &getmember &borrdata &borrdata2 &fixup_cardnumber &findguarantees &findguarantor &GuarantornameSearch &NewBorrowerNumber &modmember &newmember &changepassword &borrissues &allissues
  56         &checkuniquemember &getzipnamecity &getidcity &getguarantordata &getcategorytype
  57         &calcexpirydate &checkuserpassword
  58         &getboracctrecord
  59         &borrowercategories &getborrowercategory
  60         &fixEthnicity
  61         &ethnicitycategories get_institutions
  62     );
  63
  64
  65 =item BornameSearch
  66
  67   ($count, $borrowers) = &BornameSearch($env, $searchstring, $type);
  68
  69 Looks up patrons (borrowers) by name.
  70
  71 C<$env> is ignored.
  72
  73 BUGFIX 499: C<$type> is now used to determine type of search.
  74 if $type is "simple", search is performed on the first letter of the
  75 surname only.
  76
  77 C<$searchstring> is a space-separated list of search terms. Each term
  78 must match the beginning a borrower's surname, first name, or other
  79 name.
  80
  81 C<&BornameSearch> returns a two-element list. C<$borrowers> is a
  82 reference-to-array; each element is a reference-to-hash, whose keys
  83 are the fields of the C<borrowers> table in the Koha database.
  84 C<$count> is the number of elements in C<$borrowers>.
  85
  86 =cut
  87 #'
  88 #used by member enquiries from the intranet
  89 #called by member.pl
  90 sub BornameSearch  {
  91         my ($env,$searchstring,$orderby,$type)=@_;
  92         my $dbh = C4::Context->dbh;
  93         my $query = ""; my $count; my @data;
  94         my @bind=();
  95
  96         if($type eq "simple")   # simple search for one letter only
  97         {
  98                 $query="Select * from borrowers where surname like ? order by $orderby";
  99                 @bind=("$searchstring%");
 100         }
 101         else    # advanced search looking in surname, firstname and othernames
 102         {
 103                 @data=split(' ',$searchstring);
 104                 $count=@data;
 105                 $query="Select * from borrowers
 106                 where ((surname like ? or surname like ?
 107                 or firstname  like ? or firstname like ?
 108                 or othernames like ? or othernames like ?)
 109                 ";
 110                 @bind=("$data[0]%","% $data[0]%","$data[0]%","% $data[0]%","$data[0]%","% $data[0]%");
 111                 for (my $i=1;$i<$count;$i++){
 112                         $query=$query." and (".
 113                         " surname like ? or surname like ?
 114                         or firstname  like ? or firstname like ?
 115                         or othernames like ? or othernames like ?)";
 116                         push(@bind,"$data[$i]%","% $data[$i]%","$data[$i]%","% $data[$i]%","$data[$i]%","% $data[$i]%");
 117                                         # FIXME - .= <<EOT;
 118                 }
 119                 $query=$query.") or cardnumber like ?
 120                 order by $orderby";
 121                 push(@bind,$searchstring);
 122                                         # FIXME - .= <<EOT;
 123         }
 124
 125         my $sth=$dbh->prepare($query);
 126 #       warn "Q $orderby : $query";
 127         $sth->execute(@bind);
 128         my @results;
 129         my $cnt=$sth->rows;
 130         while (my $data=$sth->fetchrow_hashref){
 131         push(@results,$data);
 132         }
 133         #  $sth->execute;
 134         $sth->finish;
 135         return ($cnt,\@results);
 136 }
 137
 138 =item getmember
 139
 140   $borrower = &getmember($cardnumber, $borrowernumber);
 141
 142 Looks up information about a patron (borrower) by either card number
 143 or borrower number. If $borrowernumber is specified, C<&borrdata>
 144 searches by borrower number; otherwise, it searches by card number.
 145
 146 C<&getmember> returns a reference-to-hash whose keys are the fields of
 147 the C<borrowers> table in the Koha database.
 148
 149 =cut
 150 #'
 151 sub getmember {
 152   my ($cardnumber,$bornum)=@_;
 153   $cardnumber = uc $cardnumber;
 154   my $dbh = C4::Context->dbh;
 155   my $sth;
 156   if ($bornum eq ''){
 157     $sth=$dbh->prepare("Select * from borrowers where cardnumber=?");
 158     $sth->execute($cardnumber);
 159   } else {
 160     $sth=$dbh->prepare("Select * from borrowers where borrowernumber=?");
 161   $sth->execute($bornum);
 162   }
 163   my $data=$sth->fetchrow_hashref;
 164   $sth->finish;
 165   if ($data) {
 166         return($data);
 167         } else { # try with firstname
 168                 if ($cardnumber) {
 169                         my $sth=$dbh->prepare("select * from borrowers where firstname=?");
 170                         $sth->execute($cardnumber);
 171                         my $data=$sth->fetchrow_hashref;
 172                         $sth->finish;
 173                         return($data);
 174                 }
 175         }
 176         return undef;
 177 }
 178
 179 =item borrdata
 180
 181   $borrower = &borrdata($cardnumber, $borrowernumber);
 182
 183 Looks up information about a patron (borrower) by either card number
 184 or borrower number. If $borrowernumber is specified, C<&borrdata>
 185 searches by borrower number; otherwise, it searches by card number.
 186
 187 C<&borrdata> returns a reference-to-hash whose keys are the fields of
 188 the C<borrowers> table in the Koha database.
 189
 190 =cut
 191 #'
 192 sub borrdata {
 193   my ($cardnumber,$bornum)=@_;
 194   $cardnumber = uc $cardnumber;
 195   my $dbh = C4::Context->dbh;
 196   my $sth;
 197   if ($bornum eq ''){
 198     $sth=$dbh->prepare("Select borrowers.*,categories.category_type from borrowers left join categories on borrowers.categorycode=categories.categorycode where cardnumber=?");
 199     $sth->execute($cardnumber);
 200   } else {
 201     $sth=$dbh->prepare("Select borrowers.*,categories.category_type from borrowers left join categories on borrowers.categorycode=categories.categorycode where borrowernumber=?");
 202   $sth->execute($bornum);
 203   }
 204   my $data=$sth->fetchrow_hashref;
 205   warn "DATA".$data->{category_type};
 206   $sth->finish;
 207   if ($data) {
 208         return($data);
 209         } else { # try with firstname
 210                 if ($cardnumber) {
 211                         my $sth=$dbh->prepare("Select borrowers.*,categories.category_type from borrowers left join categories on borrowers.categorycode=categories.categorycode  where firstname=?");
 212                         $sth->execute($cardnumber);
 213                         my $data=$sth->fetchrow_hashref;
 214                         $sth->finish;
 215                         return($data);
 216                 }
 217         }
 218         return undef;
 219 }
 220
 221
 222 =item borrdata2
 223
 224   ($borrowed, $due, $fine) = &borrdata2($env, $borrowernumber);
 225
 226 Returns aggregate data about items borrowed by the patron with the
 227 given borrowernumber.
 228
 229 C<$env> is ignored.
 230
 231 C<&borrdata2> returns a three-element array. C<$borrowed> is the
 232 number of books the patron currently has borrowed. C<$due> is the
 233 number of overdue items the patron currently has borrowed. C<$fine> is
 234 the total fine currently due by the borrower.
 235
 236 =cut
 237 #'
 238 sub borrdata2 {
 239   my ($env,$bornum)=@_;
 240   my $dbh = C4::Context->dbh;
 241   my $query="Select count(*) from issues where borrowernumber='$bornum' and
 242     returndate is NULL";
 243     # print $query;
 244   my $sth=$dbh->prepare($query);
 245   $sth->execute;
 246   my $data=$sth->fetchrow_hashref;
 247   $sth->finish;
 248   $sth=$dbh->prepare("Select count(*) from issues where
 249     borrowernumber='$bornum' and date_due < now() and returndate is NULL");
 250   $sth->execute;
 251   my $data2=$sth->fetchrow_hashref;
 252   $sth->finish;
 253   $sth=$dbh->prepare("Select sum(amountoutstanding) from accountlines where
 254     borrowernumber='$bornum'");
 255   $sth->execute;
 256   my $data3=$sth->fetchrow_hashref;
 257   $sth->finish;
 258
 259 return($data2->{'count(*)'},$data->{'count(*)'},$data3->{'sum(amountoutstanding)'});
 260 }
 261
 262 sub modmember {
 263         my (%data) = @_;
 264         my $dbh = C4::Context->dbh;
 265         $data{'dateofbirth'}=format_date_in_iso($data{'dateofbirth'});
 266         $data{'expiry'}=format_date_in_iso($data{'expiry'});
 267         my $query="update borrowers set title='$data{'title'}',expiry='$data{'expiry'}',
 268         cardnumber='$data{'cardnumber'}',sex='$data{'sex'}',ethnotes='$data{'ethnicnotes'}',
 269         streetaddress='$data{'streetaddress'}',faxnumber='$data{'faxnumber'}',firstname='$data{'firstname'}',
 270         altnotes='$data{'altnotes'}',dateofbirth='$data{'dateofbirth'}',contactname='$data{'contactname'}',
 271         emailaddress='$data{'emailaddress'}',streetcity='$data{'streetcity'}',
 272         altrelationship='$data{'altrelationship'}',othernames='$data{'othernames'}',phoneday='$data{'phoneday'}',
 273         categorycode='$data{'categorycode'}',city='$data{'city'}',area='$data{'area'}',phone='$data{'phone'}',
 274         borrowernotes='$data{'borrowernotes'}',altphone='$data{'altphone'}',surname='$data{'surname'}',
 275         initials='$data{'initials'}',physstreet='$data{'physstreet'}',ethnicity='$data{'ethnicity'}',
 276         gonenoaddress='$data{'gna'}',lost='$data{'lost'}',debarred='$data{'debarred'}',
 277         textmessaging='$data{'textmessaging'}', branchcode = '$data{'branchcode'}',
 278         zipcode = '$data{'zipcode'}',homezipcode='$data{'homezipcode'}', sort1='$data{'sort1'}', sort2='$data{'sort2'}'
 279         where borrowernumber=$data{'borrowernumber'}";
 280         my $sth=$dbh->prepare($query);
 281         $sth->execute;
 282         $sth->finish;
 283         # ok if its an adult (type) it may have borrowers that depend on it as a guarantor
 284         # so when we update information for an adult we should check for guarantees and update the relevant part
 285         # of their records, ie addresses and phone numbers
 286         if ($data{'categorycode'} eq 'A' || $data{'categorycode'} eq 'W'){
 287                 # is adult check guarantees;
 288                 updateguarantees(%data);
 289         }
 290 }
 291
 292 sub newmember {
 293         my (%data) = @_;
 294         my $dbh = C4::Context->dbh;
 295         $data{'userid'}='' unless $data{'password'};
 296         $data{'password'}=md5_base64($data{'password'}) if $data{'password'};
 297         $data{'dateofbirth'}=format_date_in_iso($data{'dateofbirth'});
 298         $data{'dateenrolled'}=format_date_in_iso($data{'dateenrolled'});
 299         $data{expiry}=format_date_in_iso($data{expiry});
 300 my $query="insert into borrowers set cardnumber=".$dbh->quote($data{'cardnumber'}).
 301                                                                         ",surname=".$dbh->quote($data{'surname'}).
 302                                                                         ",firstname=".$dbh->quote($data{'firstname'}).
 303                                                                         ",title=".$dbh->quote($data{'title'}).
 304                                                                         ",othernames=".$dbh->quote($data{'othernames'}).
 305                                                                         ",initials=".$dbh->quote($data{'initials'}).
 306                                                                         ",streetnumber=".$dbh->quote($data{'streetnumber'}).
 307                                                                         ",streettype=".$dbh->quote($data{'streettype'}).
 308                                                                         ",address=".$dbh->quote($data{'address'}).
 309                                                                         ",address2=".$dbh->quote($data{'address2'}).
 310                                                                         ",zipcode=".$dbh->quote($data{'zipcode'}).
 311                                                                         ",city=".$dbh->quote($data{'city'}).
 312                                                                         ",phone=".$dbh->quote($data{'phone'}).
 313                                                                         ",email=".$dbh->quote($data{'email'}).
 314                                                                         ",mobile=".$dbh->quote($data{'mobile'}).
 315                                                                         ",phonepro=".$dbh->quote($data{'phonepro'}).
 316                                                                         ",opacnote=".$dbh->quote($data{'opacnote'}).
 317                                                                         ",guarantorid=".$dbh->quote($data{'guarantorid'}).
 318                                                                         ",dateofbirth=".$dbh->quote($data{'dateofbirth'}).
 319                                                                         ",branchcode=".$dbh->quote($data{'branchcode'}).
 320                                                                         ",categorycode=".$dbh->quote($data{'categorycode'}).
 321                                                                         ",dateenrolled=".$dbh->quote($data{'dateenrolled'}).
 322                                                                         ",contactname=".$dbh->quote($data{'contactname'}).
 323                                                                         ",borrowernotes=".$dbh->quote($data{'borrowernotes'}).
 324                                                                         ",dateexpiry=".$dbh->quote($data{'dateexpiry'}).
 325                                                                         ",contactnote=".$dbh->quote($data{'contactnote'}).
 326                                                                         ",b_address=".$dbh->quote($data{'b_address'}).
 327                                                                         ",b_zipcode=".$dbh->quote($data{'b_zipcode'}).
 328                                                                         ",b_city=".$dbh->quote($data{'b_city'}).
 329                                                                         ",b_phone=".$dbh->quote($data{'b_phone'}).
 330                                                                         ",b_email=".$dbh->quote($data{'b_email'},).
 331                                                                         ",password=".$dbh->quote($data{'password'}).
 332                                                                         ",userid=".$dbh->quote($data{'userid'}).
 333                                                                         ",sort1=".$dbh->quote($data{'sort1'}).
 334                                                                         ",sort2=".$dbh->quote($data{'sort2'}).
 335                                                                         ",contacttitle=".$dbh->quote($data{'contacttitle'}).
 336                                                                         ",emailpro=".$dbh->quote($data{'emailpro'}).
 337                                                                         ",contactfirstname=".$dbh->quote($data{'contactfirstname'}).
 338                                                                         ",sex=".$dbh->quote($data{'sex'}).
 339                                                                         ",fax=".$dbh->quote($data{'fax'}).
 340                                                                         ",flags=".$dbh->quote($data{'flags'}).
 341                                                                         ",relationship=".$dbh->quote($data{'relationship'})
 342                                                                         ;
 343         my $sth=$dbh->prepare($query);
 344         $sth->execute;
 345         $sth->finish;
 346         $data{'borrowerid'} =$dbh->{'mysql_insertid'};
 347         return $data{'borrowerid'};
 348 }
 349
 350 sub changepassword {
 351         my ($uid,$member,$digest) = @_;
 352         my $dbh = C4::Context->dbh;
 353         #Make sure the userid chosen is unique and not theirs if non-empty. If it is not,
 354         #Then we need to tell the user and have them create a new one.
 355         my $sth=$dbh->prepare("select * from borrowers where userid=? and borrowernumber != ?");
 356         $sth->execute($uid,$member);
 357         if ( ($uid ne '') && ($sth->fetchrow) ) {
 358                 return 0;
 359     } else {
 360                 #Everything is good so we can update the information.
 361                 $sth=$dbh->prepare("update borrowers set userid=?, password=? where borrowernumber=?");
 362                 $sth->execute($uid, $digest, $member);
 363                 return 1;
 364         }
 365 }
 366
 367 sub getmemberfromuserid {
 368         my ($userid) = @_;
 369         my $dbh = C4::Context->dbh;
 370         my $sth = $dbh->prepare("select * from borrowers where userid=?");
 371         $sth->execute($userid);
 372         return $sth->fetchrow_hashref;
 373 }
 374 sub updateguarantees {
 375         my (%data) = @_;
 376         my $dbh = C4::Context->dbh;
 377         my ($count,$guarantees)=findguarantees($data{'borrowernumber'});
 378         for (my $i=0;$i<$count;$i++){
 379                 # FIXME
 380                 # It looks like the $i is only being returned to handle walking through
 381                 # the array, which is probably better done as a foreach loop.
 382                 #
 383                 my $guaquery="update borrowers set streetaddress='$data{'address'}',faxnumber='$data{'faxnumber'}',
 384                 streetcity='$data{'streetcity'}',phoneday='$data{'phoneday'}',city='$data{'city'}',area='$data{'area'}',phone='$data{'phone'}'
 385                 ,streetaddress='$data{'address'}'
 386                 where borrowernumber='$guarantees->[$i]->{'borrowernumber'}'";
 387                 my $sth3=$dbh->prepare($guaquery);
 388                 $sth3->execute;
 389                 $sth3->finish;
 390         }
 391 }
 392 ################################################################################
 393
 394 =item fixup_cardnumber
 395
 396 Warning: The caller is responsible for locking the members table in write
 397 mode, to avoid database corruption.
 398
 399 =cut
 400
 401 use vars qw( @weightings );
 402 my @weightings = (8,4,6,3,5,2,1);
 403
 404 sub fixup_cardnumber ($) {
 405     my($cardnumber) = @_;
 406     my $autonumber_members = C4::Context->boolean_preference('autoMemberNum');
 407     $autonumber_members = 0 unless defined $autonumber_members;
 408     # Find out whether member numbers should be generated
 409     # automatically. Should be either "1" or something else.
 410     # Defaults to "0", which is interpreted as "no".
 411
 412 #     if ($cardnumber !~ /\S/ && $autonumber_members) {
 413     if ($autonumber_members) {
 414                 my $dbh = C4::Context->dbh;
 415                 if (C4::Context->preference('checkdigit') eq 'katipo') {
 416                         # if checkdigit is selected, calculate katipo-style cardnumber.
 417                         # otherwise, just use the max()
 418                         # purpose: generate checksum'd member numbers.
 419                         # We'll assume we just got the max value of digits 2-8 of member #'s
 420                         # from the database and our job is to increment that by one,
 421                         # determine the 1st and 9th digits and return the full string.
 422                         my $sth=$dbh->prepare("select max(substring(borrowers.cardnumber,2,7)) from borrowers");
 423                         $sth->execute;
 424
 425                         my $data=$sth->fetchrow_hashref;
 426                         $cardnumber=$data->{'max(substring(borrowers.cardnumber,2,7))'};
 427                         $sth->finish;
 428                         if (! $cardnumber) {                    # If DB has no values,
 429                                 $cardnumber = 1000000;          # start at 1000000
 430                         } else {
 431                                 $cardnumber += 1;
 432                         }
 433
 434                         my $sum = 0;
 435                         for (my $i = 0; $i < 8; $i += 1) {
 436                                 # read weightings, left to right, 1 char at a time
 437                                 my $temp1 = $weightings[$i];
 438
 439                                 # sequence left to right, 1 char at a time
 440                                 my $temp2 = substr($cardnumber,$i,1);
 441
 442                                 # mult each char 1-7 by its corresponding weighting
 443                                 $sum += $temp1 * $temp2;
 444                         }
 445
 446                         my $rem = ($sum%11);
 447                         $rem = 'X' if $rem == 10;
 448
 449                         $cardnumber="V$cardnumber$rem";
 450                 } else {
 451                         # MODIFIED BY JF: mysql4.1 allows casting as an integer, which is probably
 452             # better. I'll leave the original in in case it needs to be changed for you
 453             my $sth=$dbh->prepare("select max(cast(cardnumber as signed)) from borrowers");
 454             #my $sth=$dbh->prepare("select max(borrowers.cardnumber) from borrowers");
 455
 456                         $sth->execute;
 457
 458                         my ($result)=$sth->fetchrow;
 459                         $sth->finish;
 460                         $cardnumber=$result+1;
 461                 }
 462         }
 463     return $cardnumber;
 464 }
 465
 466 sub findguarantees {
 467   my ($bornum)=@_;
 468   my $dbh = C4::Context->dbh;
 469   my $sth=$dbh->prepare("select cardnumber,borrowernumber from borrowers where
 470   guarantorid=?");
 471   $sth->execute($bornum);
 472   my @dat;
 473   my $i=0;
 474   while (my $data=$sth->fetchrow_hashref){
 475     $dat[$i]=$data;
 476     $i++;
 477   }
 478   $sth->finish;
 479   return($i,\@dat);
 480 }
 481
 482 =item findguarantor
 483
 484   $guarantor = &findguarantor($borrower_no);
 485   $guarantor_cardno = $guarantor->{"cardnumber"};
 486   $guarantor_surname = $guarantor->{"surname"};
 487   ...
 488
 489 C<&findguarantor> takes a borrower number (presumably that of a child
 490 patron), finds the guarantor for C<$borrower_no> (the child's parent),
 491 and returns the record for the guarantor.
 492
 493 C<&findguarantor> returns a reference-to-hash. Its keys are the fields
 494 from the C<borrowers> database table;
 495
 496 =cut
 497 #'
 498 sub findguarantor{
 499   my ($bornum)=@_;
 500   my $dbh = C4::Context->dbh;
 501   my $sth=$dbh->prepare("Select * from borrowers where borrowernumber=?");
 502   $sth->execute($bornum);
 503   my $data=$sth->fetchrow_hashref;
 504   $sth->finish;
 505   return($data);
 506 }
 507
 508 =item GuarantornameSearch
 509
 510   ($count, $borrowers) = &GuarantornameSearch($env, $searchstring, $type);
 511
 512 Looks up guarantor  by name.
 513
 514 C<$env> is ignored.
 515
 516 BUGFIX 499: C<$type> is now used to determine type of search.
 517 if $type is "simple", search is performed on the first letter of the
 518 surname only.
 519
 520 C<$searchstring> is a space-separated list of search terms. Each term
 521 must match the beginning a borrower's surname, first name, or other
 522 name.
 523
 524 C<&GuarantornameSearch> returns a two-element list. C<$borrowers> is a
 525 reference-to-array; each element is a reference-to-hash, whose keys
 526 are the fields of the C<borrowers> table in the Koha database.
 527 C<$count> is the number of elements in C<$borrowers>.
 528
 529 return all info from guarantor =>only category_type A
 530
 531 =cut
 532 #'
 533 #used by member enquiries from the intranet
 534 #called by guarantor_search.pl
 535 sub GuarantornameSearch  {
 536         my ($env,$searchstring,$orderby,$type)=@_;
 537         my $dbh = C4::Context->dbh;
 538         my $query = ""; my $count; my @data;
 539         my @bind=();
 540
 541         if($type eq "simple")   # simple search for one letter only
 542         {
 543                 $query="Select * from borrowers,categories  where borrowers.categorycode=categories.categorycode and category_type='A'  and  surname like ? order by $orderby";
 544                 @bind=("$searchstring%");
 545         }
 546         else    # advanced search looking in surname, firstname and othernames
 547         {
 548                 @data=split(' ',$searchstring);
 549                 $count=@data;
 550                 $query="Select * from borrowers,categories
 551                 where ((surname like ? or surname like ?
 552                 or firstname  like ? or firstname like ?
 553                 or othernames like ? or othernames like ?) and borrowers.categorycode=categories.categorycode and category_type='A'
 554                 ";
 555                 @bind=("$data[0]%","% $data[0]%","$data[0]%","% $data[0]%","$data[0]%","% $data[0]%");
 556                 for (my $i=1;$i<$count;$i++){
 557                         $query=$query." and (".
 558                         " surname like ? or surname like ?
 559                         or firstname  like ? or firstname like ?
 560                         or othernames like ? or othernames like ?)";
 561                         push(@bind,"$data[$i]%","% $data[$i]%","$data[$i]%","% $data[$i]%","$data[$i]%","% $data[$i]%");
 562                                         # FIXME - .= <<EOT;
 563                 }
 564                 $query=$query.") or cardnumber like ?
 565                 order by $orderby";
 566                 push(@bind,$searchstring);
 567                                         # FIXME - .= <<EOT;
 568         }
 569
 570         my $sth=$dbh->prepare($query);
 571         $sth->execute(@bind);
 572         my @results;
 573         my $cnt=$sth->rows;
 574         while (my $data=$sth->fetchrow_hashref){
 575         push(@results,$data);
 576         }
 577         #  $sth->execute;
 578         $sth->finish;
 579         return ($cnt,\@results);
 580 }
 581
 582 =item NewBorrowerNumber
 583
 584   $num = &NewBorrowerNumber();
 585
 586 Allocates a new, unused borrower number, and returns it.
 587
 588 =cut
 589 #'
 590 # FIXME - This is identical to C4::Circulation::Borrower::NewBorrowerNumber.
 591 # Pick one and stick with it. Preferably use the other one. This function
 592 # doesn't belong in C4::Search.
 593 sub NewBorrowerNumber {
 594   my $dbh = C4::Context->dbh;
 595   my $sth=$dbh->prepare("Select max(borrowernumber) from borrowers");
 596   $sth->execute;
 597   my $data=$sth->fetchrow_hashref;
 598   $sth->finish;
 599   $data->{'max(borrowernumber)'}++;
 600   return($data->{'max(borrowernumber)'});
 601 }
 602
 603 =item borrissues
 604
 605   ($count, $issues) = &borrissues($borrowernumber);
 606
 607 Looks up what the patron with the given borrowernumber has borrowed.
 608
 609 C<&borrissues> returns a two-element array. C<$issues> is a
 610 reference-to-array, where each element is a reference-to-hash; the
 611 keys are the fields from the C<issues>, C<biblio>, and C<items> tables
 612 in the Koha database. C<$count> is the number of elements in
 613 C<$issues>.
 614
 615 =cut
 616 #'
 617 sub borrissues {
 618   my ($bornum)=@_;
 619   my $dbh = C4::Context->dbh;
 620   my $sth=$dbh->prepare("Select * from issues,biblio,items where borrowernumber=?
 621    and items.itemnumber=issues.itemnumber
 622         and items.biblionumber=biblio.biblionumber
 623         and issues.returndate is NULL order by date_due");
 624     $sth->execute($bornum);
 625   my @result;
 626   while (my $data = $sth->fetchrow_hashref) {
 627     push @result, $data;
 628   }
 629   $sth->finish;
 630   return(scalar(@result), \@result);
 631 }
 632
 633 =item allissues
 634
 635   ($count, $issues) = &allissues($borrowernumber, $sortkey, $limit);
 636
 637 Looks up what the patron with the given borrowernumber has borrowed,
 638 and sorts the results.
 639
 640 C<$sortkey> is the name of a field on which to sort the results. This
 641 should be the name of a field in the C<issues>, C<biblio>,
 642 C<biblioitems>, or C<items> table in the Koha database.
 643
 644 C<$limit> is the maximum number of results to return.
 645
 646 C<&allissues> returns a two-element array. C<$issues> is a
 647 reference-to-array, where each element is a reference-to-hash; the
 648 keys are the fields from the C<issues>, C<biblio>, C<biblioitems>, and
 649 C<items> tables of the Koha database. C<$count> is the number of
 650 elements in C<$issues>
 651
 652 =cut
 653 #'
 654 sub allissues {
 655   my ($bornum,$order,$limit)=@_;
 656   #FIXME: sanity-check order and limit
 657   my $dbh = C4::Context->dbh;
 658   my $query="Select * from issues,biblio,items,biblioitems
 659   where borrowernumber=? and
 660   items.biblioitemnumber=biblioitems.biblioitemnumber and
 661   items.itemnumber=issues.itemnumber and
 662   items.biblionumber=biblio.biblionumber order by $order";
 663   if ($limit !=0){
 664     $query.=" limit $limit";
 665   }
 666   #print $query;
 667   my $sth=$dbh->prepare($query);
 668   $sth->execute($bornum);
 669   my @result;
 670   my $i=0;
 671   while (my $data=$sth->fetchrow_hashref){
 672     $result[$i]=$data;;
 673     $i++;
 674   }
 675   $sth->finish;
 676   return($i,\@result);
 677 }
 678
 679 =item getboracctrecord
 680
 681   ($count, $acctlines, $total) = &getboracctrecord($env, $borrowernumber);
 682
 683 Looks up accounting data for the patron with the given borrowernumber.
 684
 685 C<$env> is ignored.
 686
 687 (FIXME - I'm not at all sure what this is about.)
 688
 689 C<&getboracctrecord> returns a three-element array. C<$acctlines> is a
 690 reference-to-array, where each element is a reference-to-hash; the
 691 keys are the fields of the C<accountlines> table in the Koha database.
 692 C<$count> is the number of elements in C<$acctlines>. C<$total> is the
 693 total amount outstanding for all of the account lines.
 694
 695 =cut
 696 #'
 697 sub getboracctrecord {
 698    my ($env,$params) = @_;
 699    my $dbh = C4::Context->dbh;
 700    my @acctlines;
 701    my $numlines=0;
 702    my $sth=$dbh->prepare("Select * from accountlines where
 703 borrowernumber=? order by date desc,timestamp desc");
 704 #   print $query;
 705    $sth->execute($params->{'borrowernumber'});
 706    my $total=0;
 707    while (my $data=$sth->fetchrow_hashref){
 708    #FIXME before reinstating: insecure?
 709 #      if ($data->{'itemnumber'} ne ''){
 710 #        $query="Select * from items,biblio where items.itemnumber=
 711 #       '$data->{'itemnumber'}' and biblio.biblionumber=items.biblionumber";
 712 #       my $sth2=$dbh->prepare($query);
 713 #       $sth2->execute;
 714 #       my $data2=$sth2->fetchrow_hashref;
 715 #       $sth2->finish;
 716 #       $data=$data2;
 717  #     }
 718       $acctlines[$numlines] = $data;
 719       $numlines++;
 720       $total += $data->{'amountoutstanding'};
 721    }
 722    $sth->finish;
 723    return ($numlines,\@acctlines,$total);
 724 }
 725
 726
 727 =head2 checkuniquemember (OUEST-PROVENCE)
 728
 729   $result = &checkuniquemember($collectivity,$surname,$categorycode,$firstname,$dateofbirth);
 730
 731 Checks that a member exists or not in the database.
 732
 733 C<&result> is 1 (=exist) or 0 (=does not exist)
 734 C<&collectivity> is 1 (= we add a collectivity) or 0 (= we add a physical member)
 735 C<&surname> is the surname
 736 C<&categorycode> is from categorycode table
 737 C<&firstname> is the firstname (only if collectivity=0)
 738 C<&dateofbirth> is the date of birth (only if collectivity=0)
 739
 740 =cut
 741
 742 sub checkuniquemember{
 743         my ($collectivity,$surname,$firstname,$dateofbirth)=@_;
 744         my $dbh = C4::Context->dbh;
 745         my $request;
 746         if ($collectivity ) {
 747 #                               $request="select count(*) from borrowers where surname=? and categorycode=?";
 748                 $request="select borrowernumber,categorycode from borrowers where surname=? ";
 749         } else {
 750 #                               $request="select count(*) from borrowers where surname=? and categorycode=? and firstname=? and dateofbirth=?";
 751                 $request="select borrowernumber,categorycode from borrowers where surname=?  and firstname=? and dateofbirth=?";
 752         }
 753         my $sth=$dbh->prepare($request);
 754         if ($collectivity) {
 755                 $sth->execute(uc($surname));
 756         } else {
 757                 $sth->execute(uc($surname),ucfirst($firstname),$dateofbirth);
 758         }
 759         my @data= $sth->fetchrow;
 760         if ($data[0]){
 761                 $sth->finish;
 762         return $data[0],$data[1];
 763 #
 764         }else{
 765                 $sth->finish;
 766         return 0;
 767         }
 768 }
 769
 770 =head2 getzipnamecity (OUEST-PROVENCE)
 771
 772 take all info from table city for the fields city and  zip
 773 check for the name and the zip code of the city selected
 774
 775 =cut
 776 sub getzipnamecity {
 777         my ($cityid)=@_;
 778         my $dbh = C4::Context->dbh;
 779         my $sth=$dbh->prepare("select city_name,city_zipcode from cities where cityid=? ");
 780         $sth->execute($cityid);
 781         my @data = $sth->fetchrow;
 782         return $data[0],$data[1];
 783 }
 784
 785 =head2 updatechildguarantor (OUEST-PROVENCE)
 786
 787 check for title,firstname,surname,adress,zip code and city  from guarantor to
 788 guarantorchild
 789
 790 =cut
 791 sub getguarantordata{
 792         my ($borrowerid)=@_;
 793         my $dbh = C4::Context->dbh;
 794         my $sth=$dbh->prepare("Select title,firstname,surname,streetnumber,address,streettype,address2,zipcode,city,phone,phonepro,mobile,email,emailpro  from borrowers where borrowernumber =? ");
 795         $sth->execute($borrowerid);
 796         my $guarantor_data=$sth->fetchrow_hashref;
 797         $sth->finish;
 798         return $guarantor_data;
 799 }
 800
 801 =head2 getdcity (OUEST-PROVENCE)
 802 recover cityid  with city_name condition
 803 =cut
 804 sub getidcity {
 805         my ($city_name)=@_;
 806         my $dbh = C4::Context->dbh;
 807         my $sth=$dbh->prepare("select cityid from cities where city_name=? ");
 808         $sth->execute($city_name);
 809         my $data = $sth->fetchrow;
 810         return $data;
 811 }
 812
 813
 814 =head2 getcategorytype (OUEST-PROVENCE)
 815
 816 check for the category_type with categorycode
 817 and return the category_type
 818
 819 =cut
 820 sub getcategorytype {
 821                         my ($categorycode)=@_;
 822                         my $dbh = C4::Context->dbh;
 823                         my $sth=$dbh->prepare("Select category_type,description from categories where categorycode=?  ");
 824                         $sth->execute($categorycode);
 825                         my ($category_type,$description) = $sth->fetchrow;
 826                         return $category_type,$description;
 827 }
 828
 829 sub calcexpirydate {
 830         my ($categorycode,$dateenrolled)=@_;
 831         my $dbh=C4::Context->dbh;
 832         my $sth = $dbh->prepare("select enrolmentperiod from categories where categorycode=?");
 833         $sth->execute($categorycode);
 834         my ($enrolmentperiod) = $sth->fetchrow;
 835         $enrolmentperiod = 12 unless ($enrolmentperiod);
 836         return format_date_in_iso(&DateCalc($dateenrolled,"$enrolmentperiod months"));
 837 }
 838
 839 =head2 checkuserpassword (OUEST-PROVENCE)
 840
 841 check for the password and login are not used
 842 return the number of record
 843 0=> NOT USED 1=> USED
 844
 845 =cut
 846 sub checkuserpassword{
 847                         my ($borrowerid,$userid,$password)=@_;
 848                         $password=md5_base64($password);
 849                         my $dbh = C4::Context->dbh;
 850                         my $sth=$dbh->prepare("Select count(*) from borrowers where borrowernumber !=? and userid =? and password=? ");
 851                         $sth->execute($borrowerid,$userid,$password);
 852                         my $number_rows=$sth->fetchrow;
 853   return $number_rows;
 854
 855 }
 856
 857 =head2 borrowercategories
 858
 859   ($codes_arrayref, $labels_hashref) = &borrowercategories();
 860
 861 Looks up the different types of borrowers in the database. Returns two
 862 elements: a reference-to-array, which lists the borrower category
 863 codes, and a reference-to-hash, which maps the borrower category codes
 864 to category descriptions.
 865
 866 =cut
 867 #'
 868 sub borrowercategories {
 869         my ($category_type,$action)=@_;
 870         my $dbh = C4::Context->dbh;
 871         my $request;
 872         $request="Select categorycode,description from categories where category_type=? order by categorycode";
 873         my $sth=$dbh->prepare($request);
 874         $sth->execute($category_type);
 875         my %labels;
 876         my @codes;
 877         while (my $data=$sth->fetchrow_hashref){
 878                 push @codes,$data->{'categorycode'};
 879                 $labels{$data->{'categorycode'}}=$data->{'description'};
 880         }
 881         $sth->finish;
 882         return(\@codes,\%labels);
 883 }
 884
 885 =item getborrowercategory
 886
 887   $description = &getborrowercategory($categorycode);
 888
 889 Given the borrower's category code, the function returns the corresponding
 890 description for a comprehensive information display.
 891
 892 =cut
 893
 894 sub getborrowercategory
 895 {
 896         my ($catcode) = @_;
 897         my $dbh = C4::Context->dbh;
 898         my $sth = $dbh->prepare("SELECT description FROM categories WHERE categorycode = ?");
 899         $sth->execute($catcode);
 900         my $description = $sth->fetchrow();
 901         $sth->finish();
 902         return $description;
 903 } # sub getborrowercategory
 904
 905
 906 =head2 ethnicitycategories
 907
 908   ($codes_arrayref, $labels_hashref) = &ethnicitycategories();
 909
 910 Looks up the different ethnic types in the database. Returns two
 911 elements: a reference-to-array, which lists the ethnicity codes, and a
 912 reference-to-hash, which maps the ethnicity codes to ethnicity
 913 descriptions.
 914
 915 =cut
 916 #'
 917
 918 sub ethnicitycategories {
 919     my $dbh = C4::Context->dbh;
 920     my $sth=$dbh->prepare("Select code,name from ethnicity order by name");
 921     $sth->execute;
 922     my %labels;
 923     my @codes;
 924     while (my $data=$sth->fetchrow_hashref){
 925       push @codes,$data->{'code'};
 926       $labels{$data->{'code'}}=$data->{'name'};
 927     }
 928     $sth->finish;
 929     return(\@codes,\%labels);
 930 }
 931
 932 =head2 fixEthnicity
 933
 934   $ethn_name = &fixEthnicity($ethn_code);
 935
 936 Takes an ethnicity code (e.g., "european" or "pi") and returns the
 937 corresponding descriptive name from the C<ethnicity> table in the
 938 Koha database ("European" or "Pacific Islander").
 939
 940 =cut
 941 #'
 942
 943 sub fixEthnicity($) {
 944
 945     my $ethnicity = shift;
 946     my $dbh = C4::Context->dbh;
 947     my $sth=$dbh->prepare("Select name from ethnicity where code = ?");
 948     $sth->execute($ethnicity);
 949     my $data=$sth->fetchrow_hashref;
 950     $sth->finish;
 951     return $data->{'name'};
 952 } # sub fixEthnicity
 953
 954 =head2 get_institutions
 955
 956   $insitutions = get_institutions();
 957
 958 Just returns a list of all the borrowers of type I, borrownumber and name
 959
 960 =cut
 961 #'
 962
 963 sub get_institutions {
 964     my $dbh = C4::Context->dbh();
 965     my $sth = $dbh->prepare("SELECT borrowernumber,surname FROM borrowers WHERE categorycode=? ORDER BY surname");
 966     $sth->execute('I');
 967     my %orgs;
 968     while (my $data = $sth->fetchrow_hashref()){
 969         $orgs{$data->{'borrowernumber'}}=$data;
 970     }
 971     $sth->finish();
 972     return(\%orgs);
 973
 974 } # sub get_institutions
 975 1;