3 # Copyright 2000-2003 Katipo Communications
4 # Copyright 2010 BibLibre
5 # Parts Copyright 2010 Catalyst IT
7 # This file is part of Koha.
9 # Koha is free software; you can redistribute it and/or modify it under the
10 # terms of the GNU General Public License as published by the Free Software
11 # Foundation; either version 2 of the License, or (at your option) any later
14 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
15 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
16 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
18 # You should have received a copy of the GNU General Public License along
19 # with Koha; if not, write to the Free Software Foundation, Inc.,
20 # 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
24 #use warnings; FIXME - Bug 2505
26 use C4::Dates qw(format_date_in_iso format_date);
27 use String::Random qw( random_string );
28 use Date::Calc qw/Today Add_Delta_YM check_date Date_to_Days/;
29 use C4::Log; # logaction
35 use C4::SQLHelper qw(InsertInTable UpdateInTable SearchInTable);
36 use C4::Members::Attributes qw(SearchIdMatchingAttribute);
37 use C4::NewsChannels; #get slip news
39 use DateTime::Format::DateParse;
41 use Koha::Borrower::Debarments qw(IsDebarred);
42 use Text::Unaccent qw( unac_string );
43 use Koha::AuthUtils qw(hash_password);
45 our ($VERSION,@ISA,@EXPORT,@EXPORT_OK,$debug);
48 $VERSION = 3.07.00.049;
49 $debug = $ENV{DEBUG} || 0;
61 &GetMemberIssuesAndFines
68 &GetFirstValidEmailAddress
69 &GetNoticeEmailAddress
80 &GetHideLostItemsPreference
83 &GetMemberAccountRecords
84 &GetBorNotifyAcctRecord
88 GetBorrowerCategorycode
89 &GetBorrowercategoryList
91 &GetBorrowersToExpunge
92 &GetBorrowersWhoHaveNeverBorrowed
93 &GetBorrowersWithIssuesHistoryOlderThan
103 GetBorrowersWithEmail
125 &ExtendMemberSubscriptionTo
143 C4::Members - Perl Module containing convenience functions for member handling
151 This module contains routines for adding, modifying and deleting members/patrons/borrowers
157 $borrowers_result_array_ref = &Search($filter,$orderby, $limit,
158 $columns_out, $search_on_fields,$searchtype);
160 Looks up patrons (borrowers) on filter. A wrapper for SearchInTable('borrowers').
162 For C<$filter>, C<$orderby>, C<$limit>, C<&columns_out>, C<&search_on_fields> and C<&searchtype>
163 refer to C4::SQLHelper:SearchInTable().
165 Special C<$filter> key '' is effectively expanded to search on surname firstname othernamescw
166 and cardnumber unless C<&search_on_fields> is defined
170 $borrowers = Search('abcd', 'cardnumber');
172 $borrowers = Search({''=>'abcd', category_type=>'I'}, 'surname');
176 sub _express_member_find {
179 # this is used by circulation everytime a new borrowers cardnumber is scanned
180 # so we can check an exact match first, if that works return, otherwise do the rest
181 my $dbh = C4::Context->dbh;
182 my $query = "SELECT borrowernumber FROM borrowers WHERE cardnumber = ?";
183 if ( my $borrowernumber = $dbh->selectrow_array($query, undef, $filter) ) {
184 return( {"borrowernumber"=>$borrowernumber} );
187 my ($search_on_fields, $searchtype);
188 if ( length($filter) == 1 ) {
189 $search_on_fields = [ qw(surname) ];
190 $searchtype = 'start_with';
192 $search_on_fields = [ qw(surname firstname othernames cardnumber) ];
193 $searchtype = 'contain';
196 return (undef, $search_on_fields, $searchtype);
200 my ( $filter, $orderby, $limit, $columns_out, $search_on_fields, $searchtype ) = @_;
205 if ( my $fr = ref $filter ) {
206 if ( $fr eq "HASH" ) {
207 if ( my $search_string = $filter->{''} ) {
208 my ($member_filter, $member_search_on_fields, $member_searchtype) = _express_member_find($search_string);
209 if ($member_filter) {
210 $filter = $member_filter;
213 $search_on_fields ||= $member_search_on_fields;
214 $searchtype ||= $member_searchtype;
219 $search_string = $filter;
223 $search_string = $filter;
224 my ($member_filter, $member_search_on_fields, $member_searchtype) = _express_member_find($search_string);
225 if ($member_filter) {
226 $filter = $member_filter;
229 $search_on_fields ||= $member_search_on_fields;
230 $searchtype ||= $member_searchtype;
234 if ( !$found_borrower && C4::Context->preference('ExtendedPatronAttributes') && $search_string ) {
235 my $matching_records = C4::Members::Attributes::SearchIdMatchingAttribute($search_string);
236 if(scalar(@$matching_records)>0) {
237 if ( my $fr = ref $filter ) {
238 if ( $fr eq "HASH" ) {
240 $filter = [ $filter ];
242 push @$filter, { %f, "borrowernumber"=>$$matching_records };
245 push @$filter, {"borrowernumber"=>$matching_records};
249 $filter = [ $filter ];
250 push @$filter, {"borrowernumber"=>$matching_records};
255 # $showallbranches was not used at the time SearchMember() was mainstreamed into Search().
256 # Mentioning for the reference
258 if ( C4::Context->preference("IndependentBranches") ) { # && !$showallbranches){
259 if ( my $userenv = C4::Context->userenv ) {
260 my $branch = $userenv->{'branch'};
261 if ( !C4::Context->IsSuperLibrarian() && $branch ){
262 if (my $fr = ref $filter) {
263 if ( $fr eq "HASH" ) {
264 $filter->{branchcode} = $branch;
268 $_ = { '' => $_ } unless ref $_;
269 $_->{branchcode} = $branch;
274 $filter = { '' => $filter, branchcode => $branch };
280 if ($found_borrower) {
281 $searchtype = "exact";
283 $searchtype ||= "start_with";
285 return SearchInTable( "borrowers", $filter, $orderby, $limit, $columns_out, $search_on_fields, $searchtype );
288 =head2 GetMemberDetails
290 ($borrower) = &GetMemberDetails($borrowernumber, $cardnumber);
292 Looks up a patron and returns information about him or her. If
293 C<$borrowernumber> is true (nonzero), C<&GetMemberDetails> looks
294 up the borrower by number; otherwise, it looks up the borrower by card
297 C<$borrower> is a reference-to-hash whose keys are the fields of the
298 borrowers table in the Koha database. In addition,
299 C<$borrower-E<gt>{flags}> is a hash giving more detailed information
300 about the patron. Its keys act as flags :
302 if $borrower->{flags}->{LOST} {
303 # Patron's card was reported lost
306 If the state of a flag means that the patron should not be
307 allowed to borrow any more books, then it will have a C<noissues> key
310 See patronflags for more details.
312 C<$borrower-E<gt>{authflags}> is a hash giving more detailed information
313 about the top-level permissions flags set for the borrower. For example,
314 if a user has the "editcatalogue" permission,
315 C<$borrower-E<gt>{authflags}-E<gt>{editcatalogue}> will exist and have
320 sub GetMemberDetails {
321 my ( $borrowernumber, $cardnumber ) = @_;
322 my $dbh = C4::Context->dbh;
325 if ($borrowernumber) {
326 $sth = $dbh->prepare("
329 categories.description,
330 categories.BlockExpiredPatronOpacActions,
334 LEFT JOIN categories ON borrowers.categorycode=categories.categorycode
335 WHERE borrowernumber = ?
337 $sth->execute($borrowernumber);
339 elsif ($cardnumber) {
340 $sth = $dbh->prepare("
343 categories.description,
344 categories.BlockExpiredPatronOpacActions,
348 LEFT JOIN categories ON borrowers.categorycode = categories.categorycode
351 $sth->execute($cardnumber);
356 my $borrower = $sth->fetchrow_hashref;
357 my ($amount) = GetMemberAccountRecords( $borrowernumber);
358 $borrower->{'amountoutstanding'} = $amount;
359 # FIXME - patronflags calls GetMemberAccountRecords... just have patronflags return $amount
360 my $flags = patronflags( $borrower);
363 $sth = $dbh->prepare("select bit,flag from userflags");
365 while ( my ( $bit, $flag ) = $sth->fetchrow ) {
366 if ( $borrower->{'flags'} && $borrower->{'flags'} & 2**$bit ) {
367 $accessflagshash->{$flag} = 1;
370 $borrower->{'flags'} = $flags;
371 $borrower->{'authflags'} = $accessflagshash;
373 # For the purposes of making templates easier, we'll define a
374 # 'showname' which is the alternate form the user's first name if
375 # 'other name' is defined.
376 if ($borrower->{category_type} eq 'I') {
377 $borrower->{'showname'} = $borrower->{'othernames'};
378 $borrower->{'showname'} .= " $borrower->{'firstname'}" if $borrower->{'firstname'};
380 $borrower->{'showname'} = $borrower->{'firstname'};
383 # Handle setting the true behavior for BlockExpiredPatronOpacActions
384 $borrower->{'BlockExpiredPatronOpacActions'} =
385 C4::Context->preference('BlockExpiredPatronOpacActions')
386 if ( $borrower->{'BlockExpiredPatronOpacActions'} == -1 );
388 $borrower->{'is_expired'} =
389 Date_to_Days( Today() ) >
390 Date_to_Days( split /-/, $borrower->{'dateexpiry'} );
392 return ($borrower); #, $flags, $accessflagshash);
397 $flags = &patronflags($patron);
399 This function is not exported.
401 The following will be set where applicable:
402 $flags->{CHARGES}->{amount} Amount of debt
403 $flags->{CHARGES}->{noissues} Set if debt amount >$5.00 (or syspref noissuescharge)
404 $flags->{CHARGES}->{message} Message -- deprecated
406 $flags->{CREDITS}->{amount} Amount of credit
407 $flags->{CREDITS}->{message} Message -- deprecated
409 $flags->{ GNA } Patron has no valid address
410 $flags->{ GNA }->{noissues} Set for each GNA
411 $flags->{ GNA }->{message} "Borrower has no valid address" -- deprecated
413 $flags->{ LOST } Patron's card reported lost
414 $flags->{ LOST }->{noissues} Set for each LOST
415 $flags->{ LOST }->{message} Message -- deprecated
417 $flags->{DBARRED} Set if patron debarred, no access
418 $flags->{DBARRED}->{noissues} Set for each DBARRED
419 $flags->{DBARRED}->{message} Message -- deprecated
422 $flags->{ NOTES }->{message} The note itself. NOT deprecated
424 $flags->{ ODUES } Set if patron has overdue books.
425 $flags->{ ODUES }->{message} "Yes" -- deprecated
426 $flags->{ ODUES }->{itemlist} ref-to-array: list of overdue books
427 $flags->{ ODUES }->{itemlisttext} Text list of overdue items -- deprecated
429 $flags->{WAITING} Set if any of patron's reserves are available
430 $flags->{WAITING}->{message} Message -- deprecated
431 $flags->{WAITING}->{itemlist} ref-to-array: list of available items
435 =item C<$flags-E<gt>{ODUES}-E<gt>{itemlist}> is a reference-to-array listing the
436 overdue items. Its elements are references-to-hash, each describing an
437 overdue item. The keys are selected fields from the issues, biblio,
438 biblioitems, and items tables of the Koha database.
440 =item C<$flags-E<gt>{ODUES}-E<gt>{itemlisttext}> is a string giving a text listing of
441 the overdue items, one per line. Deprecated.
443 =item C<$flags-E<gt>{WAITING}-E<gt>{itemlist}> is a reference-to-array listing the
444 available items. Each element is a reference-to-hash whose keys are
445 fields from the reserves table of the Koha database.
449 All the "message" fields that include language generated in this function are deprecated,
450 because such strings belong properly in the display layer.
452 The "message" field that comes from the DB is OK.
456 # TODO: use {anonymous => hashes} instead of a dozen %flaginfo
457 # FIXME rename this function.
460 my ( $patroninformation) = @_;
461 my $dbh=C4::Context->dbh;
462 my ($balance, $owing) = GetMemberAccountBalance( $patroninformation->{'borrowernumber'});
465 my $noissuescharge = C4::Context->preference("noissuescharge") || 5;
466 $flaginfo{'message'} = sprintf 'Patron owes %.02f', $owing;
467 $flaginfo{'amount'} = sprintf "%.02f", $owing;
468 if ( $owing > $noissuescharge && !C4::Context->preference("AllowFineOverride") ) {
469 $flaginfo{'noissues'} = 1;
471 $flags{'CHARGES'} = \%flaginfo;
473 elsif ( $balance < 0 ) {
475 $flaginfo{'message'} = sprintf 'Patron has credit of %.02f', -$balance;
476 $flaginfo{'amount'} = sprintf "%.02f", $balance;
477 $flags{'CREDITS'} = \%flaginfo;
479 if ( $patroninformation->{'gonenoaddress'}
480 && $patroninformation->{'gonenoaddress'} == 1 )
483 $flaginfo{'message'} = 'Borrower has no valid address.';
484 $flaginfo{'noissues'} = 1;
485 $flags{'GNA'} = \%flaginfo;
487 if ( $patroninformation->{'lost'} && $patroninformation->{'lost'} == 1 ) {
489 $flaginfo{'message'} = 'Borrower\'s card reported lost.';
490 $flaginfo{'noissues'} = 1;
491 $flags{'LOST'} = \%flaginfo;
493 if ( $patroninformation->{'debarred'} && check_date( split( /-/, $patroninformation->{'debarred'} ) ) ) {
494 if ( Date_to_Days(Date::Calc::Today) < Date_to_Days( split( /-/, $patroninformation->{'debarred'} ) ) ) {
496 $flaginfo{'debarredcomment'} = $patroninformation->{'debarredcomment'};
497 $flaginfo{'message'} = $patroninformation->{'debarredcomment'};
498 $flaginfo{'noissues'} = 1;
499 $flaginfo{'dateend'} = $patroninformation->{'debarred'};
500 $flags{'DBARRED'} = \%flaginfo;
503 if ( $patroninformation->{'borrowernotes'}
504 && $patroninformation->{'borrowernotes'} )
507 $flaginfo{'message'} = $patroninformation->{'borrowernotes'};
508 $flags{'NOTES'} = \%flaginfo;
510 my ( $odues, $itemsoverdue ) = C4::Overdues::checkoverdues($patroninformation->{'borrowernumber'});
511 if ( $odues && $odues > 0 ) {
513 $flaginfo{'message'} = "Yes";
514 $flaginfo{'itemlist'} = $itemsoverdue;
515 foreach ( sort { $a->{'date_due'} cmp $b->{'date_due'} }
518 $flaginfo{'itemlisttext'} .=
519 "$_->{'date_due'} $_->{'barcode'} $_->{'title'} \n"; # newline is display layer
521 $flags{'ODUES'} = \%flaginfo;
523 my @itemswaiting = C4::Reserves::GetReservesFromBorrowernumber( $patroninformation->{'borrowernumber'},'W' );
524 my $nowaiting = scalar @itemswaiting;
525 if ( $nowaiting > 0 ) {
527 $flaginfo{'message'} = "Reserved items available";
528 $flaginfo{'itemlist'} = \@itemswaiting;
529 $flags{'WAITING'} = \%flaginfo;
537 $borrower = &GetMember(%information);
539 Retrieve the first patron record meeting on criteria listed in the
540 C<%information> hash, which should contain one or more
541 pairs of borrowers column names and values, e.g.,
543 $borrower = GetMember(borrowernumber => id);
545 C<&GetBorrower> returns a reference-to-hash whose keys are the fields of
546 the C<borrowers> table in the Koha database.
548 FIXME: GetMember() is used throughout the code as a lookup
549 on a unique key such as the borrowernumber, but this meaning is not
550 enforced in the routine itself.
556 my ( %information ) = @_;
557 if (exists $information{borrowernumber} && !defined $information{borrowernumber}) {
558 #passing mysql's kohaadmin?? Makes no sense as a query
561 my $dbh = C4::Context->dbh;
563 q{SELECT borrowers.*, categories.category_type, categories.description
565 LEFT JOIN categories on borrowers.categorycode=categories.categorycode WHERE };
568 for (keys %information ) {
576 if (defined $information{$_}) {
578 push @values, $information{$_};
581 $select .= "$_ IS NULL";
584 $debug && warn $select, " ",values %information;
585 my $sth = $dbh->prepare("$select");
586 $sth->execute(map{$information{$_}} keys %information);
587 my $data = $sth->fetchall_arrayref({});
588 #FIXME interface to this routine now allows generation of a result set
589 #so whole array should be returned but bowhere in the current code expects this
597 =head2 GetMemberRelatives
599 @borrowernumbers = GetMemberRelatives($borrowernumber);
601 C<GetMemberRelatives> returns a borrowersnumber's list of guarantor/guarantees of the member given in parameter
604 sub GetMemberRelatives {
605 my $borrowernumber = shift;
606 my $dbh = C4::Context->dbh;
610 my $query = "SELECT guarantorid FROM borrowers WHERE borrowernumber=?";
611 my $sth = $dbh->prepare($query);
612 $sth->execute($borrowernumber);
613 my $data = $sth->fetchrow_arrayref();
614 push @glist, $data->[0] if $data->[0];
615 my $guarantor = $data->[0] ? $data->[0] : undef;
618 $query = "SELECT borrowernumber FROM borrowers WHERE guarantorid=?";
619 $sth = $dbh->prepare($query);
620 $sth->execute($borrowernumber);
621 while ($data = $sth->fetchrow_arrayref()) {
622 push @glist, $data->[0];
625 # Getting sibling guarantees
627 $query = "SELECT borrowernumber FROM borrowers WHERE guarantorid=?";
628 $sth = $dbh->prepare($query);
629 $sth->execute($guarantor);
630 while ($data = $sth->fetchrow_arrayref()) {
631 push @glist, $data->[0] if ($data->[0] != $borrowernumber);
638 =head2 IsMemberBlocked
640 my ($block_status, $count) = IsMemberBlocked( $borrowernumber );
642 Returns whether a patron has overdue items that may result
643 in a block or whether the patron has active fine days
644 that would block circulation privileges.
646 C<$block_status> can have the following values:
648 1 if the patron has outstanding fine days, in which case C<$count> is the number of them
650 -1 if the patron has overdue items, in which case C<$count> is the number of them
652 0 if the patron has no overdue items or outstanding fine days, in which case C<$count> is 0
654 Outstanding fine days are checked before current overdue items
657 FIXME: this needs to be split into two functions; a potential block
658 based on the number of current overdue items could be orthogonal
659 to a block based on whether the patron has any fine days accrued.
663 sub IsMemberBlocked {
664 my $borrowernumber = shift;
665 my $dbh = C4::Context->dbh;
667 my $blockeddate = Koha::Borrower::Debarments::IsDebarred($borrowernumber);
669 return ( 1, $blockeddate ) if $blockeddate;
671 # if he have late issues
672 my $sth = $dbh->prepare(
673 "SELECT COUNT(*) as latedocs
675 WHERE borrowernumber = ?
676 AND date_due < now()"
678 $sth->execute($borrowernumber);
679 my $latedocs = $sth->fetchrow_hashref->{'latedocs'};
681 return ( -1, $latedocs ) if $latedocs > 0;
686 =head2 GetMemberIssuesAndFines
688 ($overdue_count, $issue_count, $total_fines) = &GetMemberIssuesAndFines($borrowernumber);
690 Returns aggregate data about items borrowed by the patron with the
691 given borrowernumber.
693 C<&GetMemberIssuesAndFines> returns a three-element array. C<$overdue_count> is the
694 number of overdue items the patron currently has borrowed. C<$issue_count> is the
695 number of books the patron currently has borrowed. C<$total_fines> is
696 the total fine currently due by the borrower.
701 sub GetMemberIssuesAndFines {
702 my ( $borrowernumber ) = @_;
703 my $dbh = C4::Context->dbh;
704 my $query = "SELECT COUNT(*) FROM issues WHERE borrowernumber = ?";
706 $debug and warn $query."\n";
707 my $sth = $dbh->prepare($query);
708 $sth->execute($borrowernumber);
709 my $issue_count = $sth->fetchrow_arrayref->[0];
711 $sth = $dbh->prepare(
712 "SELECT COUNT(*) FROM issues
713 WHERE borrowernumber = ?
714 AND date_due < now()"
716 $sth->execute($borrowernumber);
717 my $overdue_count = $sth->fetchrow_arrayref->[0];
719 $sth = $dbh->prepare("SELECT SUM(amountoutstanding) FROM accountlines WHERE borrowernumber = ?");
720 $sth->execute($borrowernumber);
721 my $total_fines = $sth->fetchrow_arrayref->[0];
723 return ($overdue_count, $issue_count, $total_fines);
729 my @columns = C4::Member::columns();
731 Returns an array of borrowers' table columns on success,
732 and an empty array on failure.
738 # Pure ANSI SQL goodness.
739 my $sql = 'SELECT * FROM borrowers WHERE 1=0;';
741 # Get the database handle.
742 my $dbh = C4::Context->dbh;
744 # Run the SQL statement to load STH's readonly properties.
745 my $sth = $dbh->prepare($sql);
746 my $rv = $sth->execute();
748 # This only fails if the table doesn't exist.
749 # This will always be called AFTER an install or upgrade,
750 # so borrowers will exist!
752 if ($sth->{NUM_OF_FIELDS}>0) {
753 @data = @{$sth->{NAME}};
764 my $success = ModMember(borrowernumber => $borrowernumber,
765 [ field => value ]... );
767 Modify borrower's data. All date fields should ALREADY be in ISO format.
770 true on success, or false on failure
776 # test to know if you must update or not the borrower password
777 if (exists $data{password}) {
778 if ($data{password} eq '****' or $data{password} eq '') {
779 delete $data{password};
781 $data{password} = hash_password($data{password});
784 my $old_categorycode = GetBorrowerCategorycode( $data{borrowernumber} );
785 my $execute_success=UpdateInTable("borrowers",\%data);
786 if ($execute_success) { # only proceed if the update was a success
787 # ok if its an adult (type) it may have borrowers that depend on it as a guarantor
788 # so when we update information for an adult we should check for guarantees and update the relevant part
789 # of their records, ie addresses and phone numbers
790 my $borrowercategory= GetBorrowercategory( $data{'category_type'} );
791 if ( exists $borrowercategory->{'category_type'} && $borrowercategory->{'category_type'} eq ('A' || 'S') ) {
792 # is adult check guarantees;
793 UpdateGuarantees(%data);
796 # If the patron changes to a category with enrollment fee, we add a fee
797 if ( $data{categorycode} and $data{categorycode} ne $old_categorycode ) {
798 AddEnrolmentFeeIfNeeded( $data{categorycode}, $data{borrowernumber} );
801 logaction("MEMBERS", "MODIFY", $data{'borrowernumber'}, "UPDATE (executed w/ arg: $data{'borrowernumber'})") if C4::Context->preference("BorrowersLog");
803 return $execute_success;
808 $borrowernumber = &AddMember(%borrower);
810 insert new borrower into table
811 Returns the borrowernumber upon success
813 Returns as undef upon any db error without further processing
820 my $dbh = C4::Context->dbh;
822 # generate a proper login if none provided
823 $data{'userid'} = Generate_Userid($data{'borrowernumber'}, $data{'firstname'}, $data{'surname'}) if $data{'userid'} eq '';
825 # add expiration date if it isn't already there
826 unless ( $data{'dateexpiry'} ) {
827 $data{'dateexpiry'} = GetExpiryDate( $data{'categorycode'}, C4::Dates->new()->output("iso") );
830 # add enrollment date if it isn't already there
831 unless ( $data{'dateenrolled'} ) {
832 $data{'dateenrolled'} = C4::Dates->new()->output("iso");
835 # create a disabled account if no password provided
836 $data{'password'} = ($data{'password'})? hash_password($data{'password'}) : '!';
837 $data{'borrowernumber'}=InsertInTable("borrowers",\%data);
839 # mysql_insertid is probably bad. not necessarily accurate and mysql-specific at best.
840 logaction("MEMBERS", "CREATE", $data{'borrowernumber'}, "") if C4::Context->preference("BorrowersLog");
842 AddEnrolmentFeeIfNeeded( $data{categorycode}, $data{borrowernumber} );
844 return $data{'borrowernumber'};
849 my $uniqueness = Check_Userid($userid,$borrowernumber);
851 $borrowernumber is optional (i.e. it can contain a blank value). If $userid is passed with a blank $borrowernumber variable, the database will be checked for all instances of that userid (i.e. userid=? AND borrowernumber != '').
853 If $borrowernumber is provided, the database will be checked for every instance of that userid coupled with a different borrower(number) than the one provided.
856 0 for not unique (i.e. this $userid already exists)
857 1 for unique (i.e. this $userid does not exist, or this $userid/$borrowernumber combination already exists)
862 my ($uid,$member) = @_;
863 my $dbh = C4::Context->dbh;
866 "SELECT * FROM borrowers WHERE userid=? AND borrowernumber != ?");
867 $sth->execute( $uid, $member );
868 if ( ( $uid ne '' ) && ( my $row = $sth->fetchrow_hashref ) ) {
876 =head2 Generate_Userid
878 my $newuid = Generate_Userid($borrowernumber, $firstname, $surname);
880 Generate a userid using the $surname and the $firstname (if there is a value in $firstname).
882 $borrowernumber is optional (i.e. it can contain a blank value). A value is passed when generating a new userid for an existing borrower. When a new userid is created for a new borrower, a blank value is passed to this sub.
885 new userid ($firstname.$surname if there is a $firstname, or $surname if there is no value in $firstname) plus offset (0 if the $newuid is unique, or a higher numeric value if Check_Userid finds an existing match for the $newuid in the database).
889 sub Generate_Userid {
890 my ($borrowernumber, $firstname, $surname) = @_;
893 #The script will "do" the following code and increment the $offset until Check_Userid = 1 (i.e. until $newuid comes back as unique)
895 $firstname =~ s/[[:digit:][:space:][:blank:][:punct:][:cntrl:]]//g;
896 $surname =~ s/[[:digit:][:space:][:blank:][:punct:][:cntrl:]]//g;
897 $newuid = lc(($firstname)? "$firstname.$surname" : $surname);
898 $newuid = unac_string('utf-8',$newuid);
899 $newuid .= $offset unless $offset == 0;
902 } while (!Check_Userid($newuid,$borrowernumber));
908 my ( $uid, $member, $digest ) = @_;
909 my $dbh = C4::Context->dbh;
911 #Make sure the userid chosen is unique and not theirs if non-empty. If it is not,
912 #Then we need to tell the user and have them create a new one.
916 "SELECT * FROM borrowers WHERE userid=? AND borrowernumber != ?");
917 $sth->execute( $uid, $member );
918 if ( ( $uid ne '' ) && ( my $row = $sth->fetchrow_hashref ) ) {
922 #Everything is good so we can update the information.
925 "update borrowers set userid=?, password=? where borrowernumber=?");
926 $sth->execute( $uid, $digest, $member );
930 logaction("MEMBERS", "CHANGE PASS", $member, "") if C4::Context->preference("BorrowersLog");
936 =head2 fixup_cardnumber
938 Warning: The caller is responsible for locking the members table in write
939 mode, to avoid database corruption.
943 use vars qw( @weightings );
944 my @weightings = ( 8, 4, 6, 3, 5, 2, 1 );
946 sub fixup_cardnumber {
947 my ($cardnumber) = @_;
948 my $autonumber_members = C4::Context->boolean_preference('autoMemberNum') || 0;
950 # Find out whether member numbers should be generated
951 # automatically. Should be either "1" or something else.
952 # Defaults to "0", which is interpreted as "no".
954 # if ($cardnumber !~ /\S/ && $autonumber_members) {
955 ($autonumber_members) or return $cardnumber;
956 my $checkdigit = C4::Context->preference('checkdigit');
957 my $dbh = C4::Context->dbh;
958 if ( $checkdigit and $checkdigit eq 'katipo' ) {
960 # if checkdigit is selected, calculate katipo-style cardnumber.
961 # otherwise, just use the max()
962 # purpose: generate checksum'd member numbers.
963 # We'll assume we just got the max value of digits 2-8 of member #'s
964 # from the database and our job is to increment that by one,
965 # determine the 1st and 9th digits and return the full string.
966 my $sth = $dbh->prepare(
967 "select max(substring(borrowers.cardnumber,2,7)) as new_num from borrowers"
970 my $data = $sth->fetchrow_hashref;
971 $cardnumber = $data->{new_num};
972 if ( !$cardnumber ) { # If DB has no values,
973 $cardnumber = 1000000; # start at 1000000
979 for ( my $i = 0 ; $i < 8 ; $i += 1 ) {
980 # read weightings, left to right, 1 char at a time
981 my $temp1 = $weightings[$i];
983 # sequence left to right, 1 char at a time
984 my $temp2 = substr( $cardnumber, $i, 1 );
986 # mult each char 1-7 by its corresponding weighting
987 $sum += $temp1 * $temp2;
990 my $rem = ( $sum % 11 );
991 $rem = 'X' if $rem == 10;
993 return "V$cardnumber$rem";
996 my $sth = $dbh->prepare(
997 'SELECT MAX( CAST( cardnumber AS SIGNED ) ) FROM borrowers WHERE cardnumber REGEXP "^-?[0-9]+$"'
1000 my ($result) = $sth->fetchrow;
1003 return $cardnumber; # just here as a fallback/reminder
1006 =head2 GetGuarantees
1008 ($num_children, $children_arrayref) = &GetGuarantees($parent_borrno);
1009 $child0_cardno = $children_arrayref->[0]{"cardnumber"};
1010 $child0_borrno = $children_arrayref->[0]{"borrowernumber"};
1012 C<&GetGuarantees> takes a borrower number (e.g., that of a patron
1013 with children) and looks up the borrowers who are guaranteed by that
1014 borrower (i.e., the patron's children).
1016 C<&GetGuarantees> returns two values: an integer giving the number of
1017 borrowers guaranteed by C<$parent_borrno>, and a reference to an array
1018 of references to hash, which gives the actual results.
1024 my ($borrowernumber) = @_;
1025 my $dbh = C4::Context->dbh;
1028 "select cardnumber,borrowernumber, firstname, surname from borrowers where guarantorid=?"
1030 $sth->execute($borrowernumber);
1033 my $data = $sth->fetchall_arrayref({});
1034 return ( scalar(@$data), $data );
1037 =head2 UpdateGuarantees
1039 &UpdateGuarantees($parent_borrno);
1042 C<&UpdateGuarantees> borrower data for an adult and updates all the guarantees
1043 with the modified information
1048 sub UpdateGuarantees {
1050 my $dbh = C4::Context->dbh;
1051 my ( $count, $guarantees ) = GetGuarantees( $data{'borrowernumber'} );
1052 foreach my $guarantee (@$guarantees){
1053 my $guaquery = qq|UPDATE borrowers
1054 SET address=?,fax=?,B_city=?,mobile=?,city=?,phone=?
1055 WHERE borrowernumber=?
1057 my $sth = $dbh->prepare($guaquery);
1058 $sth->execute($data{'address'},$data{'fax'},$data{'B_city'},$data{'mobile'},$data{'city'},$data{'phone'},$guarantee->{'borrowernumber'});
1061 =head2 GetPendingIssues
1063 my $issues = &GetPendingIssues(@borrowernumber);
1065 Looks up what the patron with the given borrowernumber has borrowed.
1067 C<&GetPendingIssues> returns a
1068 reference-to-array where each element is a reference-to-hash; the
1069 keys are the fields from the C<issues>, C<biblio>, and C<items> tables.
1070 The keys include C<biblioitems> fields except marc and marcxml.
1075 sub GetPendingIssues {
1076 my @borrowernumbers = @_;
1078 unless (@borrowernumbers ) { # return a ref_to_array
1079 return \@borrowernumbers; # to not cause surprise to caller
1082 # Borrowers part of the query
1084 for (my $i = 0; $i < @borrowernumbers; $i++) {
1085 $bquery .= ' issues.borrowernumber = ?';
1086 if ($i < $#borrowernumbers ) {
1091 # must avoid biblioitems.* to prevent large marc and marcxml fields from killing performance
1092 # FIXME: namespace collision: each table has "timestamp" fields. Which one is "timestamp" ?
1093 # FIXME: circ/ciculation.pl tries to sort by timestamp!
1094 # FIXME: namespace collision: other collisions possible.
1095 # FIXME: most of this data isn't really being used by callers.
1102 biblioitems.itemtype,
1105 biblioitems.publicationyear,
1106 biblioitems.publishercode,
1107 biblioitems.volumedate,
1108 biblioitems.volumedesc,
1111 borrowers.firstname,
1113 borrowers.cardnumber,
1114 issues.timestamp AS timestamp,
1115 issues.renewals AS renewals,
1116 issues.borrowernumber AS borrowernumber,
1117 items.renewals AS totalrenewals
1119 LEFT JOIN items ON items.itemnumber = issues.itemnumber
1120 LEFT JOIN biblio ON items.biblionumber = biblio.biblionumber
1121 LEFT JOIN biblioitems ON items.biblioitemnumber = biblioitems.biblioitemnumber
1122 LEFT JOIN borrowers ON issues.borrowernumber = borrowers.borrowernumber
1125 ORDER BY issues.issuedate"
1128 my $sth = C4::Context->dbh->prepare($query);
1129 $sth->execute(@borrowernumbers);
1130 my $data = $sth->fetchall_arrayref({});
1131 my $tz = C4::Context->tz();
1132 my $today = DateTime->now( time_zone => $tz);
1133 foreach (@{$data}) {
1134 if ($_->{issuedate}) {
1135 $_->{issuedate} = dt_from_string($_->{issuedate}, 'sql');
1137 $_->{date_due} or next;
1138 $_->{date_due} = DateTime::Format::DateParse->parse_datetime($_->{date_due}, $tz->name());
1139 if ( DateTime->compare($_->{date_due}, $today) == -1 ) {
1148 $issues = &GetAllIssues($borrowernumber, $sortkey, $limit);
1150 Looks up what the patron with the given borrowernumber has borrowed,
1151 and sorts the results.
1153 C<$sortkey> is the name of a field on which to sort the results. This
1154 should be the name of a field in the C<issues>, C<biblio>,
1155 C<biblioitems>, or C<items> table in the Koha database.
1157 C<$limit> is the maximum number of results to return.
1159 C<&GetAllIssues> an arrayref, C<$issues>, of hashrefs, the keys of which
1160 are the fields from the C<issues>, C<biblio>, C<biblioitems>, and
1161 C<items> tables of the Koha database.
1167 my ( $borrowernumber, $order, $limit ) = @_;
1169 my $dbh = C4::Context->dbh;
1171 'SELECT *, issues.timestamp as issuestimestamp, issues.renewals AS renewals,items.renewals AS totalrenewals,items.timestamp AS itemstimestamp
1173 LEFT JOIN items on items.itemnumber=issues.itemnumber
1174 LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber
1175 LEFT JOIN biblioitems ON items.biblioitemnumber=biblioitems.biblioitemnumber
1176 WHERE borrowernumber=?
1178 SELECT *, old_issues.timestamp as issuestimestamp, old_issues.renewals AS renewals,items.renewals AS totalrenewals,items.timestamp AS itemstimestamp
1180 LEFT JOIN items on items.itemnumber=old_issues.itemnumber
1181 LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber
1182 LEFT JOIN biblioitems ON items.biblioitemnumber=biblioitems.biblioitemnumber
1183 WHERE borrowernumber=? AND old_issues.itemnumber IS NOT NULL
1184 order by ' . $order;
1186 $query .= " limit $limit";
1189 my $sth = $dbh->prepare($query);
1190 $sth->execute( $borrowernumber, $borrowernumber );
1191 return $sth->fetchall_arrayref( {} );
1195 =head2 GetMemberAccountRecords
1197 ($total, $acctlines, $count) = &GetMemberAccountRecords($borrowernumber);
1199 Looks up accounting data for the patron with the given borrowernumber.
1201 C<&GetMemberAccountRecords> returns a three-element array. C<$acctlines> is a
1202 reference-to-array, where each element is a reference-to-hash; the
1203 keys are the fields of the C<accountlines> table in the Koha database.
1204 C<$count> is the number of elements in C<$acctlines>. C<$total> is the
1205 total amount outstanding for all of the account lines.
1209 sub GetMemberAccountRecords {
1210 my ($borrowernumber) = @_;
1211 my $dbh = C4::Context->dbh;
1217 WHERE borrowernumber=?);
1218 $strsth.=" ORDER BY date desc,timestamp DESC";
1219 my $sth= $dbh->prepare( $strsth );
1220 $sth->execute( $borrowernumber );
1223 while ( my $data = $sth->fetchrow_hashref ) {
1224 if ( $data->{itemnumber} ) {
1225 my $biblio = GetBiblioFromItemNumber( $data->{itemnumber} );
1226 $data->{biblionumber} = $biblio->{biblionumber};
1227 $data->{title} = $biblio->{title};
1229 $acctlines[$numlines] = $data;
1231 $total += int(1000 * $data->{'amountoutstanding'}); # convert float to integer to avoid round-off errors
1234 return ( $total, \@acctlines,$numlines);
1237 =head2 GetMemberAccountBalance
1239 ($total_balance, $non_issue_balance, $other_charges) = &GetMemberAccountBalance($borrowernumber);
1241 Calculates amount immediately owing by the patron - non-issue charges.
1242 Based on GetMemberAccountRecords.
1243 Charges exempt from non-issue are:
1245 * Rent (rental) if RentalsInNoissuesCharge syspref is set to false
1246 * Manual invoices if ManInvInNoissuesCharge syspref is set to false
1250 sub GetMemberAccountBalance {
1251 my ($borrowernumber) = @_;
1253 my $ACCOUNT_TYPE_LENGTH = 5; # this is plain ridiculous...
1255 my @not_fines = ('Res');
1256 push @not_fines, 'Rent' unless C4::Context->preference('RentalsInNoissuesCharge');
1257 unless ( C4::Context->preference('ManInvInNoissuesCharge') ) {
1258 my $dbh = C4::Context->dbh;
1259 my $man_inv_types = $dbh->selectcol_arrayref(qq{SELECT authorised_value FROM authorised_values WHERE category = 'MANUAL_INV'});
1260 push @not_fines, map substr($_, 0, $ACCOUNT_TYPE_LENGTH), @$man_inv_types;
1262 my %not_fine = map {$_ => 1} @not_fines;
1264 my ($total, $acctlines) = GetMemberAccountRecords($borrowernumber);
1265 my $other_charges = 0;
1266 foreach (@$acctlines) {
1267 $other_charges += $_->{amountoutstanding} if $not_fine{ substr($_->{accounttype}, 0, $ACCOUNT_TYPE_LENGTH) };
1270 return ( $total, $total - $other_charges, $other_charges);
1273 =head2 GetBorNotifyAcctRecord
1275 ($total, $acctlines, $count) = &GetBorNotifyAcctRecord($params,$notifyid);
1277 Looks up accounting data for the patron with the given borrowernumber per file number.
1279 C<&GetBorNotifyAcctRecord> returns a three-element array. C<$acctlines> is a
1280 reference-to-array, where each element is a reference-to-hash; the
1281 keys are the fields of the C<accountlines> table in the Koha database.
1282 C<$count> is the number of elements in C<$acctlines>. C<$total> is the
1283 total amount outstanding for all of the account lines.
1287 sub GetBorNotifyAcctRecord {
1288 my ( $borrowernumber, $notifyid ) = @_;
1289 my $dbh = C4::Context->dbh;
1292 my $sth = $dbh->prepare(
1295 WHERE borrowernumber=?
1297 AND amountoutstanding != '0'
1298 ORDER BY notify_id,accounttype
1301 $sth->execute( $borrowernumber, $notifyid );
1303 while ( my $data = $sth->fetchrow_hashref ) {
1304 if ( $data->{itemnumber} ) {
1305 my $biblio = GetBiblioFromItemNumber( $data->{itemnumber} );
1306 $data->{biblionumber} = $biblio->{biblionumber};
1307 $data->{title} = $biblio->{title};
1309 $acctlines[$numlines] = $data;
1311 $total += int(100 * $data->{'amountoutstanding'});
1314 return ( $total, \@acctlines, $numlines );
1317 =head2 checkuniquemember (OUEST-PROVENCE)
1319 ($result,$categorycode) = &checkuniquemember($collectivity,$surname,$firstname,$dateofbirth);
1321 Checks that a member exists or not in the database.
1323 C<&result> is nonzero (=exist) or 0 (=does not exist)
1324 C<&categorycode> is from categorycode table
1325 C<&collectivity> is 1 (= we add a collectivity) or 0 (= we add a physical member)
1326 C<&surname> is the surname
1327 C<&firstname> is the firstname (only if collectivity=0)
1328 C<&dateofbirth> is the date of birth in ISO format (only if collectivity=0)
1332 # FIXME: This function is not legitimate. Multiple patrons might have the same first/last name and birthdate.
1333 # This is especially true since first name is not even a required field.
1335 sub checkuniquemember {
1336 my ( $collectivity, $surname, $firstname, $dateofbirth ) = @_;
1337 my $dbh = C4::Context->dbh;
1338 my $request = ($collectivity) ?
1339 "SELECT borrowernumber,categorycode FROM borrowers WHERE surname=? " :
1341 "SELECT borrowernumber,categorycode FROM borrowers WHERE surname=? and firstname=? and dateofbirth=?" :
1342 "SELECT borrowernumber,categorycode FROM borrowers WHERE surname=? and firstname=?";
1343 my $sth = $dbh->prepare($request);
1344 if ($collectivity) {
1345 $sth->execute( uc($surname) );
1346 } elsif($dateofbirth){
1347 $sth->execute( uc($surname), ucfirst($firstname), $dateofbirth );
1349 $sth->execute( uc($surname), ucfirst($firstname));
1351 my @data = $sth->fetchrow;
1352 ( $data[0] ) and return $data[0], $data[1];
1356 sub checkcardnumber {
1357 my ( $cardnumber, $borrowernumber ) = @_;
1359 # If cardnumber is null, we assume they're allowed.
1360 return 0 unless defined $cardnumber;
1362 my $dbh = C4::Context->dbh;
1363 my $query = "SELECT * FROM borrowers WHERE cardnumber=?";
1364 $query .= " AND borrowernumber <> ?" if ($borrowernumber);
1365 my $sth = $dbh->prepare($query);
1368 ( $borrowernumber ? $borrowernumber : () )
1371 return 1 if $sth->fetchrow_hashref;
1373 my ( $min_length, $max_length ) = get_cardnumber_length();
1375 if length $cardnumber > $max_length
1376 or length $cardnumber < $min_length;
1381 =head2 get_cardnumber_length
1383 my ($min, $max) = C4::Members::get_cardnumber_length()
1385 Returns the minimum and maximum length for patron cardnumbers as
1386 determined by the CardnumberLength system preference, the
1387 BorrowerMandatoryField system preference, and the width of the
1392 sub get_cardnumber_length {
1393 my ( $min, $max ) = ( 0, 16 ); # borrowers.cardnumber is a nullable varchar(16)
1394 $min = 1 if C4::Context->preference('BorrowerMandatoryField') =~ /cardnumber/;
1395 if ( my $cardnumber_length = C4::Context->preference('CardnumberLength') ) {
1396 # Is integer and length match
1397 if ( $cardnumber_length =~ m|^\d+$| ) {
1398 $min = $max = $cardnumber_length
1399 if $cardnumber_length >= $min
1400 and $cardnumber_length <= $max;
1402 # Else assuming it is a range
1403 elsif ( $cardnumber_length =~ m|(\d*),(\d*)| ) {
1404 $min = $1 if $1 and $min < $1;
1405 $max = $2 if $2 and $max > $2;
1409 return ( $min, $max );
1412 =head2 getzipnamecity (OUEST-PROVENCE)
1414 take all info from table city for the fields city and zip
1415 check for the name and the zip code of the city selected
1419 sub getzipnamecity {
1421 my $dbh = C4::Context->dbh;
1424 "select city_name,city_state,city_zipcode,city_country from cities where cityid=? ");
1425 $sth->execute($cityid);
1426 my @data = $sth->fetchrow;
1427 return $data[0], $data[1], $data[2], $data[3];
1431 =head2 getdcity (OUEST-PROVENCE)
1433 recover cityid with city_name condition
1438 my ($city_name) = @_;
1439 my $dbh = C4::Context->dbh;
1440 my $sth = $dbh->prepare("select cityid from cities where city_name=? ");
1441 $sth->execute($city_name);
1442 my $data = $sth->fetchrow;
1446 =head2 GetFirstValidEmailAddress
1448 $email = GetFirstValidEmailAddress($borrowernumber);
1450 Return the first valid email address for a borrower, given the borrowernumber. For now, the order
1451 is defined as email, emailpro, B_email. Returns the empty string if the borrower has no email
1456 sub GetFirstValidEmailAddress {
1457 my $borrowernumber = shift;
1458 my $dbh = C4::Context->dbh;
1459 my $sth = $dbh->prepare( "SELECT email, emailpro, B_email FROM borrowers where borrowernumber = ? ");
1460 $sth->execute( $borrowernumber );
1461 my $data = $sth->fetchrow_hashref;
1463 if ($data->{'email'}) {
1464 return $data->{'email'};
1465 } elsif ($data->{'emailpro'}) {
1466 return $data->{'emailpro'};
1467 } elsif ($data->{'B_email'}) {
1468 return $data->{'B_email'};
1474 =head2 GetNoticeEmailAddress
1476 $email = GetNoticeEmailAddress($borrowernumber);
1478 Return the email address of borrower used for notices, given the borrowernumber.
1479 Returns the empty string if no email address.
1483 sub GetNoticeEmailAddress {
1484 my $borrowernumber = shift;
1486 my $which_address = C4::Context->preference("AutoEmailPrimaryAddress");
1487 # if syspref is set to 'first valid' (value == OFF), look up email address
1488 if ( $which_address eq 'OFF' ) {
1489 return GetFirstValidEmailAddress($borrowernumber);
1491 # specified email address field
1492 my $dbh = C4::Context->dbh;
1493 my $sth = $dbh->prepare( qq{
1494 SELECT $which_address AS primaryemail
1496 WHERE borrowernumber=?
1498 $sth->execute($borrowernumber);
1499 my $data = $sth->fetchrow_hashref;
1500 return $data->{'primaryemail'} || '';
1503 =head2 GetExpiryDate
1505 $expirydate = GetExpiryDate($categorycode, $dateenrolled);
1507 Calculate expiry date given a categorycode and starting date. Date argument must be in ISO format.
1508 Return date is also in ISO format.
1513 my ( $categorycode, $dateenrolled ) = @_;
1515 if ($categorycode) {
1516 my $dbh = C4::Context->dbh;
1517 my $sth = $dbh->prepare("SELECT enrolmentperiod,enrolmentperioddate FROM categories WHERE categorycode=?");
1518 $sth->execute($categorycode);
1519 $enrolments = $sth->fetchrow_hashref;
1521 # die "GetExpiryDate: for enrollmentperiod $enrolmentperiod (category '$categorycode') starting $dateenrolled.\n";
1522 my @date = split (/-/,$dateenrolled);
1523 if($enrolments->{enrolmentperiod}){
1524 return sprintf("%04d-%02d-%02d", Add_Delta_YM(@date,0,$enrolments->{enrolmentperiod}));
1526 return $enrolments->{enrolmentperioddate};
1530 =head2 GetborCatFromCatType
1532 ($codes_arrayref, $labels_hashref) = &GetborCatFromCatType();
1534 Looks up the different types of borrowers in the database. Returns two
1535 elements: a reference-to-array, which lists the borrower category
1536 codes, and a reference-to-hash, which maps the borrower category codes
1537 to category descriptions.
1542 sub GetborCatFromCatType {
1543 my ( $category_type, $action, $no_branch_limit ) = @_;
1545 my $branch_limit = $no_branch_limit
1547 : C4::Context->userenv ? C4::Context->userenv->{"branch"} : "";
1549 # FIXME - This API seems both limited and dangerous.
1550 my $dbh = C4::Context->dbh;
1553 SELECT categories.categorycode, categories.description
1557 LEFT JOIN categories_branches ON categories.categorycode = categories_branches.categorycode
1560 $request .= " $action ";
1561 $request .= " AND (branchcode = ? OR branchcode IS NULL) GROUP BY description" if $branch_limit;
1563 $request .= " WHERE branchcode = ? OR branchcode IS NULL GROUP BY description" if $branch_limit;
1565 $request .= " ORDER BY categorycode";
1567 my $sth = $dbh->prepare($request);
1569 $action ? $category_type : (),
1570 $branch_limit ? $branch_limit : ()
1576 while ( my $data = $sth->fetchrow_hashref ) {
1577 push @codes, $data->{'categorycode'};
1578 $labels{ $data->{'categorycode'} } = $data->{'description'};
1581 return ( \@codes, \%labels );
1584 =head2 GetBorrowercategory
1586 $hashref = &GetBorrowercategory($categorycode);
1588 Given the borrower's category code, the function returns the corresponding
1589 data hashref for a comprehensive information display.
1593 sub GetBorrowercategory {
1595 my $dbh = C4::Context->dbh;
1599 "SELECT description,dateofbirthrequired,upperagelimit,category_type
1601 WHERE categorycode = ?"
1603 $sth->execute($catcode);
1605 $sth->fetchrow_hashref;
1609 } # sub getborrowercategory
1612 =head2 GetBorrowerCategorycode
1614 $categorycode = &GetBorrowerCategoryCode( $borrowernumber );
1616 Given the borrowernumber, the function returns the corresponding categorycode
1619 sub GetBorrowerCategorycode {
1620 my ( $borrowernumber ) = @_;
1621 my $dbh = C4::Context->dbh;
1622 my $sth = $dbh->prepare( qq{
1625 WHERE borrowernumber = ?
1627 $sth->execute( $borrowernumber );
1628 return $sth->fetchrow;
1631 =head2 GetBorrowercategoryList
1633 $arrayref_hashref = &GetBorrowercategoryList;
1634 If no category code provided, the function returns all the categories.
1638 sub GetBorrowercategoryList {
1639 my $no_branch_limit = @_ ? shift : 0;
1640 my $branch_limit = $no_branch_limit
1642 : C4::Context->userenv ? C4::Context->userenv->{"branch"} : "";
1643 my $dbh = C4::Context->dbh;
1644 my $query = "SELECT categories.* FROM categories";
1646 LEFT JOIN categories_branches ON categories.categorycode = categories_branches.categorycode
1647 WHERE branchcode = ? OR branchcode IS NULL GROUP BY description
1649 $query .= " ORDER BY description";
1650 my $sth = $dbh->prepare( $query );
1651 $sth->execute( $branch_limit ? $branch_limit : () );
1652 my $data = $sth->fetchall_arrayref( {} );
1655 } # sub getborrowercategory
1657 =head2 ethnicitycategories
1659 ($codes_arrayref, $labels_hashref) = ðnicitycategories();
1661 Looks up the different ethnic types in the database. Returns two
1662 elements: a reference-to-array, which lists the ethnicity codes, and a
1663 reference-to-hash, which maps the ethnicity codes to ethnicity
1670 sub ethnicitycategories {
1671 my $dbh = C4::Context->dbh;
1672 my $sth = $dbh->prepare("Select code,name from ethnicity order by name");
1676 while ( my $data = $sth->fetchrow_hashref ) {
1677 push @codes, $data->{'code'};
1678 $labels{ $data->{'code'} } = $data->{'name'};
1680 return ( \@codes, \%labels );
1685 $ethn_name = &fixEthnicity($ethn_code);
1687 Takes an ethnicity code (e.g., "european" or "pi") and returns the
1688 corresponding descriptive name from the C<ethnicity> table in the
1689 Koha database ("European" or "Pacific Islander").
1696 my $ethnicity = shift;
1697 return unless $ethnicity;
1698 my $dbh = C4::Context->dbh;
1699 my $sth = $dbh->prepare("Select name from ethnicity where code = ?");
1700 $sth->execute($ethnicity);
1701 my $data = $sth->fetchrow_hashref;
1702 return $data->{'name'};
1703 } # sub fixEthnicity
1707 $dateofbirth,$date = &GetAge($date);
1709 this function return the borrowers age with the value of dateofbirth
1715 my ( $date, $date_ref ) = @_;
1717 if ( not defined $date_ref ) {
1718 $date_ref = sprintf( '%04d-%02d-%02d', Today() );
1721 my ( $year1, $month1, $day1 ) = split /-/, $date;
1722 my ( $year2, $month2, $day2 ) = split /-/, $date_ref;
1724 my $age = $year2 - $year1;
1725 if ( $month1 . $day1 > $month2 . $day2 ) {
1734 $cityarrayref = GetCities();
1736 Returns an array_ref of the entries in the cities table
1737 If there are entries in the table an empty row is returned
1738 This is currently only used to populate a popup in memberentry
1744 my $dbh = C4::Context->dbh;
1745 my $city_arr = $dbh->selectall_arrayref(
1746 q|SELECT cityid,city_zipcode,city_name,city_state,city_country FROM cities ORDER BY city_name|,
1748 if ( @{$city_arr} ) {
1749 unshift @{$city_arr}, {
1750 city_zipcode => q{},
1754 city_country => q{},
1761 =head2 GetSortDetails (OUEST-PROVENCE)
1763 ($lib) = &GetSortDetails($category,$sortvalue);
1765 Returns the authorized value details
1766 C<&$lib>return value of authorized value details
1767 C<&$sortvalue>this is the value of authorized value
1768 C<&$category>this is the value of authorized value category
1772 sub GetSortDetails {
1773 my ( $category, $sortvalue ) = @_;
1774 my $dbh = C4::Context->dbh;
1775 my $query = qq|SELECT lib
1776 FROM authorised_values
1778 AND authorised_value=? |;
1779 my $sth = $dbh->prepare($query);
1780 $sth->execute( $category, $sortvalue );
1781 my $lib = $sth->fetchrow;
1782 return ($lib) if ($lib);
1783 return ($sortvalue) unless ($lib);
1786 =head2 MoveMemberToDeleted
1788 $result = &MoveMemberToDeleted($borrowernumber);
1790 Copy the record from borrowers to deletedborrowers table.
1794 # FIXME: should do it in one SQL statement w/ subquery
1795 # Otherwise, we should return the @data on success
1797 sub MoveMemberToDeleted {
1798 my ($member) = shift or return;
1799 my $dbh = C4::Context->dbh;
1800 my $query = qq|SELECT *
1802 WHERE borrowernumber=?|;
1803 my $sth = $dbh->prepare($query);
1804 $sth->execute($member);
1805 my @data = $sth->fetchrow_array;
1806 (@data) or return; # if we got a bad borrowernumber, there's nothing to insert
1808 $dbh->prepare( "INSERT INTO deletedborrowers VALUES ("
1809 . ( "?," x ( scalar(@data) - 1 ) )
1811 $sth->execute(@data);
1816 DelMember($borrowernumber);
1818 This function remove directly a borrower whitout writing it on deleteborrower.
1819 + Deletes reserves for the borrower
1824 my $dbh = C4::Context->dbh;
1825 my $borrowernumber = shift;
1826 #warn "in delmember with $borrowernumber";
1827 return unless $borrowernumber; # borrowernumber is mandatory.
1829 my $query = qq|DELETE
1831 WHERE borrowernumber=?|;
1832 my $sth = $dbh->prepare($query);
1833 $sth->execute($borrowernumber);
1837 WHERE borrowernumber = ?
1839 $sth = $dbh->prepare($query);
1840 $sth->execute($borrowernumber);
1841 logaction("MEMBERS", "DELETE", $borrowernumber, "") if C4::Context->preference("BorrowersLog");
1845 =head2 ExtendMemberSubscriptionTo (OUEST-PROVENCE)
1847 $date = ExtendMemberSubscriptionTo($borrowerid, $date);
1849 Extending the subscription to a given date or to the expiry date calculated on ISO date.
1854 sub ExtendMemberSubscriptionTo {
1855 my ( $borrowerid,$date) = @_;
1856 my $dbh = C4::Context->dbh;
1857 my $borrower = GetMember('borrowernumber'=>$borrowerid);
1859 $date = (C4::Context->preference('BorrowerRenewalPeriodBase') eq 'dateexpiry') ?
1860 C4::Dates->new($borrower->{'dateexpiry'}, 'iso')->output("iso") :
1861 C4::Dates->new()->output("iso");
1862 $date = GetExpiryDate( $borrower->{'categorycode'}, $date );
1864 my $sth = $dbh->do(<<EOF);
1866 SET dateexpiry='$date'
1867 WHERE borrowernumber='$borrowerid'
1870 AddEnrolmentFeeIfNeeded( $borrower->{categorycode}, $borrower->{borrowernumber} );
1872 logaction("MEMBERS", "RENEW", $borrower->{'borrowernumber'}, "Membership renewed")if C4::Context->preference("BorrowersLog");
1873 return $date if ($sth);
1877 =head2 GetTitles (OUEST-PROVENCE)
1879 ($borrowertitle)= &GetTitles();
1881 Looks up the different title . Returns array with all borrowers title
1886 my @borrowerTitle = split (/,|\|/,C4::Context->preference('BorrowersTitles'));
1887 unshift( @borrowerTitle, "" );
1888 my $count=@borrowerTitle;
1893 return ( \@borrowerTitle);
1897 =head2 GetPatronImage
1899 my ($imagedata, $dberror) = GetPatronImage($borrowernumber);
1901 Returns the mimetype and binary image data of the image for the patron with the supplied borrowernumber.
1905 sub GetPatronImage {
1906 my ($borrowernumber) = @_;
1907 warn "Borrowernumber passed to GetPatronImage is $borrowernumber" if $debug;
1908 my $dbh = C4::Context->dbh;
1909 my $query = 'SELECT mimetype, imagefile FROM patronimage WHERE borrowernumber = ?';
1910 my $sth = $dbh->prepare($query);
1911 $sth->execute($borrowernumber);
1912 my $imagedata = $sth->fetchrow_hashref;
1913 warn "Database error!" if $sth->errstr;
1914 return $imagedata, $sth->errstr;
1917 =head2 PutPatronImage
1919 PutPatronImage($cardnumber, $mimetype, $imgfile);
1921 Stores patron binary image data and mimetype in database.
1922 NOTE: This function is good for updating images as well as inserting new images in the database.
1926 sub PutPatronImage {
1927 my ($cardnumber, $mimetype, $imgfile) = @_;
1928 warn "Parameters passed in: Cardnumber=$cardnumber, Mimetype=$mimetype, " . ($imgfile ? "Imagefile" : "No Imagefile") if $debug;
1929 my $dbh = C4::Context->dbh;
1930 my $query = "INSERT INTO patronimage (borrowernumber, mimetype, imagefile) VALUES ( ( SELECT borrowernumber from borrowers WHERE cardnumber = ? ),?,?) ON DUPLICATE KEY UPDATE imagefile = ?;";
1931 my $sth = $dbh->prepare($query);
1932 $sth->execute($cardnumber,$mimetype,$imgfile,$imgfile);
1933 warn "Error returned inserting $cardnumber.$mimetype." if $sth->errstr;
1934 return $sth->errstr;
1937 =head2 RmPatronImage
1939 my ($dberror) = RmPatronImage($borrowernumber);
1941 Removes the image for the patron with the supplied borrowernumber.
1946 my ($borrowernumber) = @_;
1947 warn "Borrowernumber passed to GetPatronImage is $borrowernumber" if $debug;
1948 my $dbh = C4::Context->dbh;
1949 my $query = "DELETE FROM patronimage WHERE borrowernumber = ?;";
1950 my $sth = $dbh->prepare($query);
1951 $sth->execute($borrowernumber);
1952 my $dberror = $sth->errstr;
1953 warn "Database error!" if $sth->errstr;
1957 =head2 GetHideLostItemsPreference
1959 $hidelostitemspref = &GetHideLostItemsPreference($borrowernumber);
1961 Returns the HideLostItems preference for the patron category of the supplied borrowernumber
1962 C<&$hidelostitemspref>return value of function, 0 or 1
1966 sub GetHideLostItemsPreference {
1967 my ($borrowernumber) = @_;
1968 my $dbh = C4::Context->dbh;
1969 my $query = "SELECT hidelostitems FROM borrowers,categories WHERE borrowers.categorycode = categories.categorycode AND borrowernumber = ?";
1970 my $sth = $dbh->prepare($query);
1971 $sth->execute($borrowernumber);
1972 my $hidelostitems = $sth->fetchrow;
1973 return $hidelostitems;
1976 =head2 GetBorrowersToExpunge
1978 $borrowers = &GetBorrowersToExpunge(
1979 not_borrowered_since => $not_borrowered_since,
1980 expired_before => $expired_before,
1981 category_code => $category_code,
1982 branchcode => $branchcode
1985 This function get all borrowers based on the given criteria.
1989 sub GetBorrowersToExpunge {
1992 my $filterdate = $params->{'not_borrowered_since'};
1993 my $filterexpiry = $params->{'expired_before'};
1994 my $filtercategory = $params->{'category_code'};
1995 my $filterbranch = $params->{'branchcode'} ||
1996 ((C4::Context->preference('IndependentBranches')
1997 && C4::Context->userenv
1998 && !C4::Context->IsSuperLibrarian()
1999 && C4::Context->userenv->{branch})
2000 ? C4::Context->userenv->{branch}
2003 my $dbh = C4::Context->dbh;
2005 SELECT borrowers.borrowernumber,
2006 MAX(old_issues.timestamp) AS latestissue,
2007 MAX(issues.timestamp) AS currentissue
2009 JOIN categories USING (categorycode)
2010 LEFT JOIN old_issues USING (borrowernumber)
2011 LEFT JOIN issues USING (borrowernumber)
2012 WHERE category_type <> 'S'
2013 AND borrowernumber NOT IN (SELECT guarantorid FROM borrowers WHERE guarantorid IS NOT NULL AND guarantorid <> 0)
2016 if ( $filterbranch && $filterbranch ne "" ) {
2017 $query.= " AND borrowers.branchcode = ? ";
2018 push( @query_params, $filterbranch );
2020 if ( $filterexpiry ) {
2021 $query .= " AND dateexpiry < ? ";
2022 push( @query_params, $filterexpiry );
2024 if ( $filtercategory ) {
2025 $query .= " AND categorycode = ? ";
2026 push( @query_params, $filtercategory );
2028 $query.=" GROUP BY borrowers.borrowernumber HAVING currentissue IS NULL ";
2029 if ( $filterdate ) {
2030 $query.=" AND ( latestissue < ? OR latestissue IS NULL ) ";
2031 push @query_params,$filterdate;
2033 warn $query if $debug;
2035 my $sth = $dbh->prepare($query);
2036 if (scalar(@query_params)>0){
2037 $sth->execute(@query_params);
2044 while ( my $data = $sth->fetchrow_hashref ) {
2045 push @results, $data;
2050 =head2 GetBorrowersWhoHaveNeverBorrowed
2052 $results = &GetBorrowersWhoHaveNeverBorrowed
2054 This function get all borrowers who have never borrowed.
2056 I<$result> is a ref to an array which all elements are a hasref.
2060 sub GetBorrowersWhoHaveNeverBorrowed {
2061 my $filterbranch = shift ||
2062 ((C4::Context->preference('IndependentBranches')
2063 && C4::Context->userenv
2064 && !C4::Context->IsSuperLibrarian()
2065 && C4::Context->userenv->{branch})
2066 ? C4::Context->userenv->{branch}
2068 my $dbh = C4::Context->dbh;
2070 SELECT borrowers.borrowernumber,max(timestamp) as latestissue
2072 LEFT JOIN issues ON borrowers.borrowernumber = issues.borrowernumber
2073 WHERE issues.borrowernumber IS NULL
2076 if ($filterbranch && $filterbranch ne ""){
2077 $query.=" AND borrowers.branchcode= ?";
2078 push @query_params,$filterbranch;
2080 warn $query if $debug;
2082 my $sth = $dbh->prepare($query);
2083 if (scalar(@query_params)>0){
2084 $sth->execute(@query_params);
2091 while ( my $data = $sth->fetchrow_hashref ) {
2092 push @results, $data;
2097 =head2 GetBorrowersWithIssuesHistoryOlderThan
2099 $results = &GetBorrowersWithIssuesHistoryOlderThan($date)
2101 this function get all borrowers who has an issue history older than I<$date> given on input arg.
2103 I<$result> is a ref to an array which all elements are a hashref.
2104 This hashref is containt the number of time this borrowers has borrowed before I<$date> and the borrowernumber.
2108 sub GetBorrowersWithIssuesHistoryOlderThan {
2109 my $dbh = C4::Context->dbh;
2110 my $date = shift ||POSIX::strftime("%Y-%m-%d",localtime());
2111 my $filterbranch = shift ||
2112 ((C4::Context->preference('IndependentBranches')
2113 && C4::Context->userenv
2114 && !C4::Context->IsSuperLibrarian()
2115 && C4::Context->userenv->{branch})
2116 ? C4::Context->userenv->{branch}
2119 SELECT count(borrowernumber) as n,borrowernumber
2121 WHERE returndate < ?
2122 AND borrowernumber IS NOT NULL
2125 push @query_params, $date;
2127 $query.=" AND branchcode = ?";
2128 push @query_params, $filterbranch;
2130 $query.=" GROUP BY borrowernumber ";
2131 warn $query if $debug;
2132 my $sth = $dbh->prepare($query);
2133 $sth->execute(@query_params);
2136 while ( my $data = $sth->fetchrow_hashref ) {
2137 push @results, $data;
2142 =head2 GetBorrowersNamesAndLatestIssue
2144 $results = &GetBorrowersNamesAndLatestIssueList(@borrowernumbers)
2146 this function get borrowers Names and surnames and Issue information.
2148 I<@borrowernumbers> is an array which all elements are borrowernumbers.
2149 This hashref is containt the number of time this borrowers has borrowed before I<$date> and the borrowernumber.
2153 sub GetBorrowersNamesAndLatestIssue {
2154 my $dbh = C4::Context->dbh;
2155 my @borrowernumbers=@_;
2157 SELECT surname,lastname, phone, email,max(timestamp)
2159 LEFT JOIN issues ON borrowers.borrowernumber=issues.borrowernumber
2160 GROUP BY borrowernumber
2162 my $sth = $dbh->prepare($query);
2164 my $results = $sth->fetchall_arrayref({});
2172 my $success = ModPrivacy( $borrowernumber, $privacy );
2174 Update the privacy of a patron.
2177 true on success, false on failure
2184 my $borrowernumber = shift;
2185 my $privacy = shift;
2186 return unless defined $borrowernumber;
2187 return unless $borrowernumber =~ /^\d+$/;
2189 return ModMember( borrowernumber => $borrowernumber,
2190 privacy => $privacy );
2195 AddMessage( $borrowernumber, $message_type, $message, $branchcode );
2197 Adds a message to the messages table for the given borrower.
2206 my ( $borrowernumber, $message_type, $message, $branchcode ) = @_;
2208 my $dbh = C4::Context->dbh;
2210 if ( ! ( $borrowernumber && $message_type && $message && $branchcode ) ) {
2214 my $query = "INSERT INTO messages ( borrowernumber, branchcode, message_type, message ) VALUES ( ?, ?, ?, ? )";
2215 my $sth = $dbh->prepare($query);
2216 $sth->execute( $borrowernumber, $branchcode, $message_type, $message );
2217 logaction("MEMBERS", "ADDCIRCMESSAGE", $borrowernumber, $message) if C4::Context->preference("BorrowersLog");
2223 GetMessages( $borrowernumber, $type );
2225 $type is message type, B for borrower, or L for Librarian.
2226 Empty type returns all messages of any type.
2228 Returns all messages for the given borrowernumber
2233 my ( $borrowernumber, $type, $branchcode ) = @_;
2239 my $dbh = C4::Context->dbh;
2242 branches.branchname,
2245 messages.branchcode LIKE '$branchcode' AS can_delete
2246 FROM messages, branches
2247 WHERE borrowernumber = ?
2248 AND message_type LIKE ?
2249 AND messages.branchcode = branches.branchcode
2250 ORDER BY message_date DESC";
2251 my $sth = $dbh->prepare($query);
2252 $sth->execute( $borrowernumber, $type ) ;
2255 while ( my $data = $sth->fetchrow_hashref ) {
2256 my $d = C4::Dates->new( $data->{message_date}, 'iso' );
2257 $data->{message_date_formatted} = $d->output;
2258 push @results, $data;
2266 GetMessagesCount( $borrowernumber, $type );
2268 $type is message type, B for borrower, or L for Librarian.
2269 Empty type returns all messages of any type.
2271 Returns the number of messages for the given borrowernumber
2275 sub GetMessagesCount {
2276 my ( $borrowernumber, $type, $branchcode ) = @_;
2282 my $dbh = C4::Context->dbh;
2284 my $query = "SELECT COUNT(*) as MsgCount FROM messages WHERE borrowernumber = ? AND message_type LIKE ?";
2285 my $sth = $dbh->prepare($query);
2286 $sth->execute( $borrowernumber, $type ) ;
2289 my $data = $sth->fetchrow_hashref;
2290 my $count = $data->{'MsgCount'};
2297 =head2 DeleteMessage
2299 DeleteMessage( $message_id );
2304 my ( $message_id ) = @_;
2306 my $dbh = C4::Context->dbh;
2307 my $query = "SELECT * FROM messages WHERE message_id = ?";
2308 my $sth = $dbh->prepare($query);
2309 $sth->execute( $message_id );
2310 my $message = $sth->fetchrow_hashref();
2312 $query = "DELETE FROM messages WHERE message_id = ?";
2313 $sth = $dbh->prepare($query);
2314 $sth->execute( $message_id );
2315 logaction("MEMBERS", "DELCIRCMESSAGE", $message->{'borrowernumber'}, $message->{'message'}) if C4::Context->preference("BorrowersLog");
2320 IssueSlip($branchcode, $borrowernumber, $quickslip)
2322 Returns letter hash ( see C4::Letters::GetPreparedLetter )
2324 $quickslip is boolean, to indicate whether we want a quick slip
2329 my ($branch, $borrowernumber, $quickslip) = @_;
2331 # return unless ( C4::Context->boolean_preference('printcirculationslips') );
2333 my $now = POSIX::strftime("%Y-%m-%d", localtime);
2335 my $issueslist = GetPendingIssues($borrowernumber);
2336 foreach my $it (@$issueslist){
2337 if ((substr $it->{'issuedate'}, 0, 10) eq $now || (substr $it->{'lastreneweddate'}, 0, 10) eq $now) {
2340 elsif ((substr $it->{'date_due'}, 0, 10) le $now) {
2341 $it->{'overdue'} = 1;
2343 my $dt = dt_from_string( $it->{'date_due'} );
2344 $it->{'date_due'} = output_pref( $dt );;
2346 my @issues = sort { $b->{'timestamp'} <=> $a->{'timestamp'} } @$issueslist;
2348 my ($letter_code, %repeat);
2350 $letter_code = 'ISSUEQSLIP';
2352 'checkedout' => [ map {
2356 }, grep { $_->{'now'} } @issues ],
2360 $letter_code = 'ISSUESLIP';
2362 'checkedout' => [ map {
2366 }, grep { !$_->{'overdue'} } @issues ],
2368 'overdue' => [ map {
2372 }, grep { $_->{'overdue'} } @issues ],
2375 $_->{'timestamp'} = $_->{'newdate'};
2377 } @{ GetNewsToDisplay("slip",$branch) } ],
2381 return C4::Letters::GetPreparedLetter (
2382 module => 'circulation',
2383 letter_code => $letter_code,
2384 branchcode => $branch,
2386 'branches' => $branch,
2387 'borrowers' => $borrowernumber,
2393 =head2 GetBorrowersWithEmail
2395 ([$borrnum,$userid], ...) = GetBorrowersWithEmail('me@example.com');
2397 This gets a list of users and their basic details from their email address.
2398 As it's possible for multiple user to have the same email address, it provides
2399 you with all of them. If there is no userid for the user, there will be an
2400 C<undef> there. An empty list will be returned if there are no matches.
2404 sub GetBorrowersWithEmail {
2407 my $dbh = C4::Context->dbh;
2409 my $query = "SELECT borrowernumber, userid FROM borrowers WHERE email=?";
2410 my $sth=$dbh->prepare($query);
2411 $sth->execute($email);
2413 while (my $ref = $sth->fetch) {
2416 die "Failure searching for borrowers by email address: $sth->errstr" if $sth->err;
2420 sub AddMember_Opac {
2421 my ( %borrower ) = @_;
2423 $borrower{'categorycode'} = C4::Context->preference('PatronSelfRegistrationDefaultCategory');
2425 my $sr = new String::Random;
2426 $sr->{'A'} = [ 'A'..'Z', 'a'..'z' ];
2427 my $password = $sr->randpattern("AAAAAAAAAA");
2428 $borrower{'password'} = $password;
2430 $borrower{'cardnumber'} = fixup_cardnumber();
2432 my $borrowernumber = AddMember(%borrower);
2434 return ( $borrowernumber, $password );
2437 =head2 AddEnrolmentFeeIfNeeded
2439 AddEnrolmentFeeIfNeeded( $borrower->{categorycode}, $borrower->{borrowernumber} );
2441 Add enrolment fee for a patron if needed.
2445 sub AddEnrolmentFeeIfNeeded {
2446 my ( $categorycode, $borrowernumber ) = @_;
2447 # check for enrollment fee & add it if needed
2448 my $dbh = C4::Context->dbh;
2449 my $sth = $dbh->prepare(q{
2452 WHERE categorycode=?
2454 $sth->execute( $categorycode );
2456 warn sprintf('Database returned the following error: %s', $sth->errstr);
2459 my ($enrolmentfee) = $sth->fetchrow;
2460 if ($enrolmentfee && $enrolmentfee > 0) {
2461 # insert fee in patron debts
2462 C4::Accounts::manualinvoice( $borrowernumber, '', '', 'A', $enrolmentfee );
2467 my ( $borrowernumber ) = @_;
2469 my $sql = "SELECT COUNT(*) FROM issues WHERE date_due < NOW() AND borrowernumber = ?";
2470 my $sth = C4::Context->dbh->prepare( $sql );
2471 $sth->execute( $borrowernumber );
2472 my ( $count ) = $sth->fetchrow_array();
2477 END { } # module clean-up code here (global destructor)