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