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 use strict;
  23 require Exporter;
  24 use C4::Context;
  25 use Date::Manip;
  26 use C4::Date;
  27 use Digest::MD5 qw(md5_base64);
  28
  29 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK);
  30
  31 $VERSION = 0.01;
  32
  33 =head1 NAME
  34
  35 C4::Members - Perl Module containing convenience functions for member handling
  36
  37 =head1 SYNOPSIS
  38
  39
  40 =head1 DESCRIPTION
  41
  42
  43 =head1 FUNCTIONS
  44
  45 =over 2
  46
  47 =cut
  48
  49 @ISA = qw(Exporter);
  50 @EXPORT = qw();
  51
  52 @EXPORT = qw(
  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
  56         &getboracctrecord
  57         &borrowercategories &getborrowercategory
  58         &fixEthnicity
  59         &ethnicitycategories
  60     );
  61
  62
  63 =item BornameSearch
  64
  65   ($count, $borrowers) = &BornameSearch($env, $searchstring, $type);
  66
  67 Looks up patrons (borrowers) by name.
  68
  69 C<$env> is ignored.
  70
  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
  73 surname only.
  74
  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
  77 name.
  78
  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>.
  83
  84 =cut
  85 #'
  86 #used by member enquiries from the intranet
  87 #called by member.pl
  88 sub BornameSearch  {
  89         my ($env,$searchstring,$orderby,$type)=@_;
  90         my $dbh = C4::Context->dbh;
  91         my $query = ""; my $count; my @data;
  92         my @bind=();
  93
  94         if($type eq "simple")   # simple search for one letter only
  95         {
  96                 $query="Select * from borrowers where surname like ? order by $orderby";
  97                 @bind=("$searchstring%");
  98         }
  99         else    # advanced search looking in surname, firstname and othernames
 100         {
 101                 @data=split(' ',$searchstring);
 102                 $count=@data;
 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 ?)
 107                 ";
 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]%");
 115                                         # FIXME - .= <<EOT;
 116                 }
 117                 $query=$query.") or cardnumber like ?
 118                 order by $orderby";
 119                 push(@bind,$searchstring);
 120                                         # FIXME - .= <<EOT;
 121         }
 122
 123         my $sth=$dbh->prepare($query);
 124 #       warn "Q $orderby : $query";
 125         $sth->execute(@bind);
 126         my @results;
 127         my $cnt=$sth->rows;
 128         while (my $data=$sth->fetchrow_hashref){
 129         push(@results,$data);
 130         }
 131         #  $sth->execute;
 132         $sth->finish;
 133         return ($cnt,\@results);
 134 }
 135
 136 =item getmember
 137
 138   $borrower = &getmember($cardnumber, $borrowernumber);
 139
 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.
 143
 144 C<&getmember> returns a reference-to-hash whose keys are the fields of
 145 the C<borrowers> table in the Koha database.
 146
 147 =cut
 148 #'
 149 sub getmember {
 150   my ($cardnumber,$bornum)=@_;
 151   $cardnumber = uc $cardnumber;
 152   my $dbh = C4::Context->dbh;
 153   my $sth;
 154   if ($bornum eq ''){
 155     $sth=$dbh->prepare("Select * from borrowers where cardnumber=?");
 156     $sth->execute($cardnumber);
 157   } else {
 158     $sth=$dbh->prepare("Select * from borrowers where borrowernumber=?");
 159   $sth->execute($bornum);
 160   }
 161   my $data=$sth->fetchrow_hashref;
 162   $sth->finish;
 163   if ($data) {
 164         return($data);
 165         } else { # try with firstname
 166                 if ($cardnumber) {
 167                         my $sth=$dbh->prepare("select * from borrowers where firstname=?");
 168                         $sth->execute($cardnumber);
 169                         my $data=$sth->fetchrow_hashref;
 170                         $sth->finish;
 171                         return($data);
 172                 }
 173         }
 174         return undef;
 175 }
 176
 177 =item borrdata
 178
 179   $borrower = &borrdata($cardnumber, $borrowernumber);
 180
 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.
 184
 185 C<&borrdata> returns a reference-to-hash whose keys are the fields of
 186 the C<borrowers> table in the Koha database.
 187
 188 =cut
 189 #'
 190 sub borrdata {
 191   my ($cardnumber,$bornum)=@_;
 192   $cardnumber = uc $cardnumber;
 193   my $dbh = C4::Context->dbh;
 194   my $sth;
 195   if ($bornum eq ''){
 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);
 198   } else {
 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);
 201   }
 202   my $data=$sth->fetchrow_hashref;
 203   warn "DATA".$data->{category_type};
 204   $sth->finish;
 205   if ($data) {
 206         return($data);
 207         } else { # try with firstname
 208                 if ($cardnumber) {
 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;
 212                         $sth->finish;
 213                         return($data);
 214                 }
 215         }
 216         return undef;
 217 }
 218
 219
 220 =item borrdata2
 221
 222   ($borrowed, $due, $fine) = &borrdata2($env, $borrowernumber);
 223
 224 Returns aggregate data about items borrowed by the patron with the
 225 given borrowernumber.
 226
 227 C<$env> is ignored.
 228
 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.
 233
 234 =cut
 235 #'
 236 sub borrdata2 {
 237   my ($env,$bornum)=@_;
 238   my $dbh = C4::Context->dbh;
 239   my $query="Select count(*) from issues where borrowernumber='$bornum' and
 240     returndate is NULL";
 241     # print $query;
 242   my $sth=$dbh->prepare($query);
 243   $sth->execute;
 244   my $data=$sth->fetchrow_hashref;
 245   $sth->finish;
 246   $sth=$dbh->prepare("Select count(*) from issues where
 247     borrowernumber='$bornum' and date_due < now() and returndate is NULL");
 248   $sth->execute;
 249   my $data2=$sth->fetchrow_hashref;
 250   $sth->finish;
 251   $sth=$dbh->prepare("Select sum(amountoutstanding) from accountlines where
 252     borrowernumber='$bornum'");
 253   $sth->execute;
 254   my $data3=$sth->fetchrow_hashref;
 255   $sth->finish;
 256
 257 return($data2->{'count(*)'},$data->{'count(*)'},$data3->{'sum(amountoutstanding)'});
 258 }
 259
 260 sub modmember {
 261         my (%data) = @_;
 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);
 279         $sth->execute;
 280         $sth->finish;
 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);
 287         }
 288 }
 289
 290 sub newmember {
 291         my (%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'})
 340                                                                         ;
 341         my $sth=$dbh->prepare($query);
 342         $sth->execute;
 343         $sth->finish;
 344         $data{'borrowerid'} =$dbh->{'mysql_insertid'};
 345         return $data{'borrowerid'};
 346 }
 347
 348 sub changepassword {
 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) ) {
 356                 return 0;
 357     } else {
 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);
 361                 return 1;
 362         }
 363 }
 364
 365 sub getmemberfromuserid {
 366         my ($userid) = @_;
 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;
 371 }
 372 sub updateguarantees {
 373         my (%data) = @_;
 374         my $dbh = C4::Context->dbh;
 375         my ($count,$guarantees)=findguarantees($data{'borrowernumber'});
 376         for (my $i=0;$i<$count;$i++){
 377                 # FIXME
 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.
 380                 #
 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);
 386                 $sth3->execute;
 387                 $sth3->finish;
 388         }
 389 }
 390 ################################################################################
 391
 392 =item fixup_cardnumber
 393
 394 Warning: The caller is responsible for locking the members table in write
 395 mode, to avoid database corruption.
 396
 397 =cut
 398
 399 use vars qw( @weightings );
 400 my @weightings = (8,4,6,3,5,2,1);
 401
 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".
 409
 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");
 421                         $sth->execute;
 422
 423                         my $data=$sth->fetchrow_hashref;
 424                         $cardnumber=$data->{'max(substring(borrowers.cardnumber,2,7))'};
 425                         $sth->finish;
 426                         if (! $cardnumber) {                    # If DB has no values,
 427                                 $cardnumber = 1000000;          # start at 1000000
 428                         } else {
 429                                 $cardnumber += 1;
 430                         }
 431
 432                         my $sum = 0;
 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];
 436
 437                                 # sequence left to right, 1 char at a time
 438                                 my $temp2 = substr($cardnumber,$i,1);
 439
 440                                 # mult each char 1-7 by its corresponding weighting
 441                                 $sum += $temp1 * $temp2;
 442                         }
 443
 444                         my $rem = ($sum%11);
 445                         $rem = 'X' if $rem == 10;
 446
 447                         $cardnumber="V$cardnumber$rem";
 448                 } else {
 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");
 453
 454                         $sth->execute;
 455
 456                         my ($result)=$sth->fetchrow;
 457                         $sth->finish;
 458                         $cardnumber=$result+1;
 459                 }
 460         }
 461     return $cardnumber;
 462 }
 463
 464 sub findguarantees {
 465   my ($bornum)=@_;
 466   my $dbh = C4::Context->dbh;
 467   my $sth=$dbh->prepare("select cardnumber,borrowernumber from borrowers where
 468   guarantorid=?");
 469   $sth->execute($bornum);
 470   my @dat;
 471   my $i=0;
 472   while (my $data=$sth->fetchrow_hashref){
 473     $dat[$i]=$data;
 474     $i++;
 475   }
 476   $sth->finish;
 477   return($i,\@dat);
 478 }
 479
 480 =item findguarantor
 481
 482   $guarantor = &findguarantor($borrower_no);
 483   $guarantor_cardno = $guarantor->{"cardnumber"};
 484   $guarantor_surname = $guarantor->{"surname"};
 485   ...
 486
 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.
 490
 491 C<&findguarantor> returns a reference-to-hash. Its keys are the fields
 492 from the C<borrowers> database table;
 493
 494 =cut
 495 #'
 496 sub findguarantor{
 497   my ($bornum)=@_;
 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;
 502   $sth->finish;
 503   return($data);
 504 }
 505
 506 =item GuarantornameSearch
 507
 508   ($count, $borrowers) = &GuarantornameSearch($env, $searchstring, $type);
 509
 510 Looks up guarantor  by name.
 511
 512 C<$env> is ignored.
 513
 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
 516 surname only.
 517
 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
 520 name.
 521
 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>.
 526
 527 return all info from guarantor =>only category_type A
 528
 529 =cut
 530 #'
 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;
 537         my @bind=();
 538
 539         if($type eq "simple")   # simple search for one letter only
 540         {
 541                 $query="Select * from borrowers,categories  where borrowers.categorycode=categories.categorycode and category_type='A'  and  surname like ? order by $orderby";
 542                 @bind=("$searchstring%");
 543         }
 544         else    # advanced search looking in surname, firstname and othernames
 545         {
 546                 @data=split(' ',$searchstring);
 547                 $count=@data;
 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'
 552                 ";
 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]%");
 560                                         # FIXME - .= <<EOT;
 561                 }
 562                 $query=$query.") or cardnumber like ?
 563                 order by $orderby";
 564                 push(@bind,$searchstring);
 565                                         # FIXME - .= <<EOT;
 566         }
 567
 568         my $sth=$dbh->prepare($query);
 569         $sth->execute(@bind);
 570         my @results;
 571         my $cnt=$sth->rows;
 572         while (my $data=$sth->fetchrow_hashref){
 573         push(@results,$data);
 574         }
 575         #  $sth->execute;
 576         $sth->finish;
 577         return ($cnt,\@results);
 578 }
 579
 580 =item NewBorrowerNumber
 581
 582   $num = &NewBorrowerNumber();
 583
 584 Allocates a new, unused borrower number, and returns it.
 585
 586 =cut
 587 #'
 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");
 594   $sth->execute;
 595   my $data=$sth->fetchrow_hashref;
 596   $sth->finish;
 597   $data->{'max(borrowernumber)'}++;
 598   return($data->{'max(borrowernumber)'});
 599 }
 600
 601 =item borrissues
 602
 603   ($count, $issues) = &borrissues($borrowernumber);
 604
 605 Looks up what the patron with the given borrowernumber has borrowed.
 606
 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
 611 C<$issues>.
 612
 613 =cut
 614 #'
 615 sub borrissues {
 616   my ($bornum)=@_;
 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);
 623   my @result;
 624   while (my $data = $sth->fetchrow_hashref) {
 625     push @result, $data;
 626   }
 627   $sth->finish;
 628   return(scalar(@result), \@result);
 629 }
 630
 631 =item allissues
 632
 633   ($count, $issues) = &allissues($borrowernumber, $sortkey, $limit);
 634
 635 Looks up what the patron with the given borrowernumber has borrowed,
 636 and sorts the results.
 637
 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.
 641
 642 C<$limit> is the maximum number of results to return.
 643
 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>
 649
 650 =cut
 651 #'
 652 sub allissues {
 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";
 661   if ($limit !=0){
 662     $query.=" limit $limit";
 663   }
 664   #print $query;
 665   my $sth=$dbh->prepare($query);
 666   $sth->execute($bornum);
 667   my @result;
 668   my $i=0;
 669   while (my $data=$sth->fetchrow_hashref){
 670     $result[$i]=$data;;
 671     $i++;
 672   }
 673   $sth->finish;
 674   return($i,\@result);
 675 }
 676
 677 =item getboracctrecord
 678
 679   ($count, $acctlines, $total) = &getboracctrecord($env, $borrowernumber);
 680
 681 Looks up accounting data for the patron with the given borrowernumber.
 682
 683 C<$env> is ignored.
 684
 685 (FIXME - I'm not at all sure what this is about.)
 686
 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.
 692
 693 =cut
 694 #'
 695 sub getboracctrecord {
 696    my ($env,$params) = @_;
 697    my $dbh = C4::Context->dbh;
 698    my @acctlines;
 699    my $numlines=0;
 700    my $sth=$dbh->prepare("Select * from accountlines where
 701 borrowernumber=? order by date desc,timestamp desc");
 702 #   print $query;
 703    $sth->execute($params->{'borrowernumber'});
 704    my $total=0;
 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);
 711 #       $sth2->execute;
 712 #       my $data2=$sth2->fetchrow_hashref;
 713 #       $sth2->finish;
 714 #       $data=$data2;
 715  #     }
 716       $acctlines[$numlines] = $data;
 717       $numlines++;
 718       $total += $data->{'amountoutstanding'};
 719    }
 720    $sth->finish;
 721    return ($numlines,\@acctlines,$total);
 722 }
 723
 724
 725 =head2 checkuniquemember (OUEST-PROVENCE)
 726
 727   $result = &checkuniquemember($collectivity,$surname,$categorycode,$firstname,$dateofbirth);
 728
 729 Checks that a member exists or not in the database.
 730
 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)
 737
 738 =cut
 739
 740 sub checkuniquemember{
 741         my ($collectivity,$surname,$firstname,$dateofbirth)=@_;
 742         my $dbh = C4::Context->dbh;
 743         my $request;
 744         if ($collectivity ) {
 745 #                               $request="select count(*) from borrowers where surname=? and categorycode=?";
 746                 $request="select borrowernumber,categorycode from borrowers where surname=? ";
 747         } else {
 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=?";
 750         }
 751         my $sth=$dbh->prepare($request);
 752         if ($collectivity) {
 753                 $sth->execute(uc($surname));
 754         } else {
 755                 $sth->execute(uc($surname),ucfirst($firstname),$dateofbirth);
 756         }
 757         my @data= $sth->fetchrow;
 758         if ($data[0]){
 759                 $sth->finish;
 760         return $data[0],$data[1];
 761 #
 762         }else{
 763                 $sth->finish;
 764         return 0;
 765         }
 766 }
 767
 768 =head2 getzipnamecity (OUEST-PROVENCE)
 769
 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
 772
 773 =cut
 774 sub getzipnamecity {
 775         my ($cityid)=@_;
 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];
 781 }
 782
 783 =head2 updatechildguarantor (OUEST-PROVENCE)
 784
 785 check for title,firstname,surname,adress,zip code and city  from guarantor to
 786 guarantorchild
 787
 788 =cut
 789 sub getguarantordata{
 790         my ($borrowerid)=@_;
 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;
 795         $sth->finish;
 796         return $guarantor_data;
 797 }
 798
 799 =head2 getdcity (OUEST-PROVENCE)
 800 recover cityid  with city_name condition
 801 =cut
 802 sub getidcity {
 803         my ($city_name)=@_;
 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;
 808         return $data;
 809 }
 810
 811
 812 =head2 getcategorytype (OUEST-PROVENCE)
 813
 814 check for the category_type with categorycode
 815 and return the category_type
 816
 817 =cut
 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;
 825 }
 826
 827 sub calcexpirydate {
 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"));
 835 }
 836
 837 =head2 checkuserpassword (OUEST-PROVENCE)
 838
 839 check for the password and login are not used
 840 return the number of record
 841 0=> NOT USED 1=> USED
 842
 843 =cut
 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;
 851   return $number_rows;
 852
 853 }
 854
 855 =head2 borrowercategories
 856
 857   ($codes_arrayref, $labels_hashref) = &borrowercategories();
 858
 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.
 863
 864 =cut
 865 #'
 866 sub borrowercategories {
 867         my ($category_type,$action)=@_;
 868         my $dbh = C4::Context->dbh;
 869         my $request;
 870         $request="Select categorycode,description from categories where category_type=? order by categorycode";
 871         my $sth=$dbh->prepare($request);
 872         $sth->execute($category_type);
 873         my %labels;
 874         my @codes;
 875         while (my $data=$sth->fetchrow_hashref){
 876                 push @codes,$data->{'categorycode'};
 877                 $labels{$data->{'categorycode'}}=$data->{'description'};
 878         }
 879         $sth->finish;
 880         return(\@codes,\%labels);
 881 }
 882
 883 =item getborrowercategory
 884
 885   $description = &getborrowercategory($categorycode);
 886
 887 Given the borrower's category code, the function returns the corresponding
 888 description for a comprehensive information display.
 889
 890 =cut
 891
 892 sub getborrowercategory
 893 {
 894         my ($catcode) = @_;
 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();
 899         $sth->finish();
 900         return $description;
 901 } # sub getborrowercategory
 902
 903
 904 =head2 ethnicitycategories
 905
 906   ($codes_arrayref, $labels_hashref) = &ethnicitycategories();
 907
 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
 911 descriptions.
 912
 913 =cut
 914 #'
 915
 916 sub ethnicitycategories {
 917     my $dbh = C4::Context->dbh;
 918     my $sth=$dbh->prepare("Select code,name from ethnicity order by name");
 919     $sth->execute;
 920     my %labels;
 921     my @codes;
 922     while (my $data=$sth->fetchrow_hashref){
 923       push @codes,$data->{'code'};
 924       $labels{$data->{'code'}}=$data->{'name'};
 925     }
 926     $sth->finish;
 927     return(\@codes,\%labels);
 928 }
 929
 930 =head2 fixEthnicity
 931
 932   $ethn_name = &fixEthnicity($ethn_code);
 933
 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").
 937
 938 =cut
 939 #'
 940
 941 sub fixEthnicity($) {
 942
 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;
 948     $sth->finish;
 949     return $data->{'name'};
 950 }
 951
 952 1;