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
10 # under the terms of the GNU General Public License as published by
11 # the Free Software Foundation; either version 3 of the License, or
12 # (at your option) any later version.
14 # Koha is distributed in the hope that it will be useful, but
15 # WITHOUT ANY WARRANTY; without even the implied warranty of
16 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
17 # GNU General Public License for more details.
19 # You should have received a copy of the GNU General Public License
20 # along with Koha; if not, see <http://www.gnu.org/licenses>.
24 #use warnings; FIXME - Bug 2505
26 use String::Random qw( random_string );
27 use Scalar::Util qw( looks_like_number );
28 use Date::Calc qw/Today check_date Date_to_Days/;
29 use C4::Log; # logaction
35 use C4::Members::Attributes qw(SearchIdMatchingAttribute UpdateBorrowerAttribute);
36 use C4::NewsChannels; #get slip news
40 use Text::Unaccent qw( unac_string );
41 use Koha::AuthUtils qw(hash_password);
44 use Koha::List::Patron;
46 use Koha::Patron::Categories;
49 our (@ISA,@EXPORT,@EXPORT_OK,$debug);
51 use Module::Load::Conditional qw( can_load );
52 if ( ! can_load( modules => { 'Koha::NorwegianPatronDB' => undef } ) ) {
53 $debug && warn "Unable to load Koha::NorwegianPatronDB";
58 $debug = $ENV{DEBUG} || 0;
66 &GetMemberIssuesAndFines
70 &GetFirstValidEmailAddress
71 &GetNoticeEmailAddress
73 &GetMemberAccountRecords
74 &GetBorNotifyAcctRecord
76 &GetBorrowersToExpunge
77 &GetBorrowersWhoHaveNeverBorrowed
78 &GetBorrowersWithIssuesHistoryOlderThan
80 &GetUpcomingMembershipExpires
113 C4::Members - Perl Module containing convenience functions for member handling
121 This module contains routines for adding, modifying and deleting members/patrons/borrowers
125 =head2 GetMemberDetails
127 ($borrower) = &GetMemberDetails($borrowernumber, $cardnumber);
129 Looks up a patron and returns information about him or her. If
130 C<$borrowernumber> is true (nonzero), C<&GetMemberDetails> looks
131 up the borrower by number; otherwise, it looks up the borrower by card
134 C<$borrower> is a reference-to-hash whose keys are the fields of the
135 borrowers table in the Koha database. In addition,
139 sub GetMemberDetails {
140 my ( $borrowernumber, $cardnumber ) = @_;
141 my $dbh = C4::Context->dbh;
144 if ($borrowernumber) {
145 $sth = $dbh->prepare("
148 categories.description,
152 LEFT JOIN categories ON borrowers.categorycode=categories.categorycode
153 WHERE borrowernumber = ?
155 $sth->execute($borrowernumber);
157 elsif ($cardnumber) {
158 $sth = $dbh->prepare("
161 categories.description,
165 LEFT JOIN categories ON borrowers.categorycode = categories.categorycode
168 $sth->execute($cardnumber);
173 my $borrower = $sth->fetchrow_hashref;
174 return unless $borrower;
181 $flags = &patronflags($patron);
183 This function is not exported.
185 The following will be set where applicable:
186 $flags->{CHARGES}->{amount} Amount of debt
187 $flags->{CHARGES}->{noissues} Set if debt amount >$5.00 (or syspref noissuescharge)
188 $flags->{CHARGES}->{message} Message -- deprecated
190 $flags->{CREDITS}->{amount} Amount of credit
191 $flags->{CREDITS}->{message} Message -- deprecated
193 $flags->{ GNA } Patron has no valid address
194 $flags->{ GNA }->{noissues} Set for each GNA
195 $flags->{ GNA }->{message} "Borrower has no valid address" -- deprecated
197 $flags->{ LOST } Patron's card reported lost
198 $flags->{ LOST }->{noissues} Set for each LOST
199 $flags->{ LOST }->{message} Message -- deprecated
201 $flags->{DBARRED} Set if patron debarred, no access
202 $flags->{DBARRED}->{noissues} Set for each DBARRED
203 $flags->{DBARRED}->{message} Message -- deprecated
206 $flags->{ NOTES }->{message} The note itself. NOT deprecated
208 $flags->{ ODUES } Set if patron has overdue books.
209 $flags->{ ODUES }->{message} "Yes" -- deprecated
210 $flags->{ ODUES }->{itemlist} ref-to-array: list of overdue books
211 $flags->{ ODUES }->{itemlisttext} Text list of overdue items -- deprecated
213 $flags->{WAITING} Set if any of patron's reserves are available
214 $flags->{WAITING}->{message} Message -- deprecated
215 $flags->{WAITING}->{itemlist} ref-to-array: list of available items
219 =item C<$flags-E<gt>{ODUES}-E<gt>{itemlist}> is a reference-to-array listing the
220 overdue items. Its elements are references-to-hash, each describing an
221 overdue item. The keys are selected fields from the issues, biblio,
222 biblioitems, and items tables of the Koha database.
224 =item C<$flags-E<gt>{ODUES}-E<gt>{itemlisttext}> is a string giving a text listing of
225 the overdue items, one per line. Deprecated.
227 =item C<$flags-E<gt>{WAITING}-E<gt>{itemlist}> is a reference-to-array listing the
228 available items. Each element is a reference-to-hash whose keys are
229 fields from the reserves table of the Koha database.
233 All the "message" fields that include language generated in this function are deprecated,
234 because such strings belong properly in the display layer.
236 The "message" field that comes from the DB is OK.
240 # TODO: use {anonymous => hashes} instead of a dozen %flaginfo
241 # FIXME rename this function.
244 my ( $patroninformation) = @_;
245 my $dbh=C4::Context->dbh;
246 my ($balance, $owing) = GetMemberAccountBalance( $patroninformation->{'borrowernumber'});
249 my $noissuescharge = C4::Context->preference("noissuescharge") || 5;
250 $flaginfo{'message'} = sprintf 'Patron owes %.02f', $owing;
251 $flaginfo{'amount'} = sprintf "%.02f", $owing;
252 if ( $owing > $noissuescharge && !C4::Context->preference("AllowFineOverride") ) {
253 $flaginfo{'noissues'} = 1;
255 $flags{'CHARGES'} = \%flaginfo;
257 elsif ( $balance < 0 ) {
259 $flaginfo{'message'} = sprintf 'Patron has credit of %.02f', -$balance;
260 $flaginfo{'amount'} = sprintf "%.02f", $balance;
261 $flags{'CREDITS'} = \%flaginfo;
264 # Check the debt of the guarntees of this patron
265 my $no_issues_charge_guarantees = C4::Context->preference("NoIssuesChargeGuarantees");
266 $no_issues_charge_guarantees = undef unless looks_like_number( $no_issues_charge_guarantees );
267 if ( defined $no_issues_charge_guarantees ) {
268 my $p = Koha::Patrons->find( $patroninformation->{borrowernumber} );
269 my @guarantees = $p->guarantees();
270 my $guarantees_non_issues_charges;
271 foreach my $g ( @guarantees ) {
272 my ( $b, $n, $o ) = C4::Members::GetMemberAccountBalance( $g->id );
273 $guarantees_non_issues_charges += $n;
276 if ( $guarantees_non_issues_charges > $no_issues_charge_guarantees ) {
278 $flaginfo{'message'} = sprintf 'patron guarantees owe %.02f', $guarantees_non_issues_charges;
279 $flaginfo{'amount'} = $guarantees_non_issues_charges;
280 $flaginfo{'noissues'} = 1 unless C4::Context->preference("allowfineoverride");
281 $flags{'CHARGES_GUARANTEES'} = \%flaginfo;
285 if ( $patroninformation->{'gonenoaddress'}
286 && $patroninformation->{'gonenoaddress'} == 1 )
289 $flaginfo{'message'} = 'Borrower has no valid address.';
290 $flaginfo{'noissues'} = 1;
291 $flags{'GNA'} = \%flaginfo;
293 if ( $patroninformation->{'lost'} && $patroninformation->{'lost'} == 1 ) {
295 $flaginfo{'message'} = 'Borrower\'s card reported lost.';
296 $flaginfo{'noissues'} = 1;
297 $flags{'LOST'} = \%flaginfo;
299 if ( $patroninformation->{'debarred'} && check_date( split( /-/, $patroninformation->{'debarred'} ) ) ) {
300 if ( Date_to_Days(Date::Calc::Today) < Date_to_Days( split( /-/, $patroninformation->{'debarred'} ) ) ) {
302 $flaginfo{'debarredcomment'} = $patroninformation->{'debarredcomment'};
303 $flaginfo{'message'} = $patroninformation->{'debarredcomment'};
304 $flaginfo{'noissues'} = 1;
305 $flaginfo{'dateend'} = $patroninformation->{'debarred'};
306 $flags{'DBARRED'} = \%flaginfo;
309 if ( $patroninformation->{'borrowernotes'}
310 && $patroninformation->{'borrowernotes'} )
313 $flaginfo{'message'} = $patroninformation->{'borrowernotes'};
314 $flags{'NOTES'} = \%flaginfo;
316 my ( $odues, $itemsoverdue ) = C4::Overdues::checkoverdues($patroninformation->{'borrowernumber'});
317 if ( $odues && $odues > 0 ) {
319 $flaginfo{'message'} = "Yes";
320 $flaginfo{'itemlist'} = $itemsoverdue;
321 foreach ( sort { $a->{'date_due'} cmp $b->{'date_due'} }
324 $flaginfo{'itemlisttext'} .=
325 "$_->{'date_due'} $_->{'barcode'} $_->{'title'} \n"; # newline is display layer
327 $flags{'ODUES'} = \%flaginfo;
329 my @itemswaiting = C4::Reserves::GetReservesFromBorrowernumber( $patroninformation->{'borrowernumber'},'W' );
330 my $nowaiting = scalar @itemswaiting;
331 if ( $nowaiting > 0 ) {
333 $flaginfo{'message'} = "Reserved items available";
334 $flaginfo{'itemlist'} = \@itemswaiting;
335 $flags{'WAITING'} = \%flaginfo;
343 $borrower = &GetMember(%information);
345 Retrieve the first patron record meeting on criteria listed in the
346 C<%information> hash, which should contain one or more
347 pairs of borrowers column names and values, e.g.,
349 $borrower = GetMember(borrowernumber => id);
351 C<&GetBorrower> returns a reference-to-hash whose keys are the fields of
352 the C<borrowers> table in the Koha database.
354 FIXME: GetMember() is used throughout the code as a lookup
355 on a unique key such as the borrowernumber, but this meaning is not
356 enforced in the routine itself.
362 my ( %information ) = @_;
363 if (exists $information{borrowernumber} && !defined $information{borrowernumber}) {
364 #passing mysql's kohaadmin?? Makes no sense as a query
367 my $dbh = C4::Context->dbh;
369 q{SELECT borrowers.*, categories.category_type, categories.description
371 LEFT JOIN categories on borrowers.categorycode=categories.categorycode WHERE };
374 for (keys %information ) {
382 if (defined $information{$_}) {
384 push @values, $information{$_};
387 $select .= "$_ IS NULL";
390 $debug && warn $select, " ",values %information;
391 my $sth = $dbh->prepare("$select");
392 $sth->execute(@values);
393 my $data = $sth->fetchall_arrayref({});
394 #FIXME interface to this routine now allows generation of a result set
395 #so whole array should be returned but bowhere in the current code expects this
403 =head2 GetMemberIssuesAndFines
405 ($overdue_count, $issue_count, $total_fines) = &GetMemberIssuesAndFines($borrowernumber);
407 Returns aggregate data about items borrowed by the patron with the
408 given borrowernumber.
410 C<&GetMemberIssuesAndFines> returns a three-element array. C<$overdue_count> is the
411 number of overdue items the patron currently has borrowed. C<$issue_count> is the
412 number of books the patron currently has borrowed. C<$total_fines> is
413 the total fine currently due by the borrower.
418 sub GetMemberIssuesAndFines {
419 my ( $borrowernumber ) = @_;
420 my $dbh = C4::Context->dbh;
421 my $query = "SELECT COUNT(*) FROM issues WHERE borrowernumber = ?";
423 $debug and warn $query."\n";
424 my $sth = $dbh->prepare($query);
425 $sth->execute($borrowernumber);
426 my $issue_count = $sth->fetchrow_arrayref->[0];
428 $sth = $dbh->prepare(
429 "SELECT COUNT(*) FROM issues
430 WHERE borrowernumber = ?
431 AND date_due < now()"
433 $sth->execute($borrowernumber);
434 my $overdue_count = $sth->fetchrow_arrayref->[0];
436 $sth = $dbh->prepare("SELECT SUM(amountoutstanding) FROM accountlines WHERE borrowernumber = ?");
437 $sth->execute($borrowernumber);
438 my $total_fines = $sth->fetchrow_arrayref->[0];
440 return ($overdue_count, $issue_count, $total_fines);
446 my $success = ModMember(borrowernumber => $borrowernumber,
447 [ field => value ]... );
449 Modify borrower's data. All date fields should ALREADY be in ISO format.
452 true on success, or false on failure
458 # test to know if you must update or not the borrower password
459 if (exists $data{password}) {
460 if ($data{password} eq '****' or $data{password} eq '') {
461 delete $data{password};
463 if ( C4::Context->preference('NorwegianPatronDBEnable') && C4::Context->preference('NorwegianPatronDBEnable') == 1 ) {
464 # Update the hashed PIN in borrower_sync.hashed_pin, before Koha hashes it
465 Koha::NorwegianPatronDB::NLUpdateHashedPIN( $data{'borrowernumber'}, $data{password} );
467 $data{password} = hash_password($data{password});
471 my $old_categorycode = Koha::Patrons->find( $data{borrowernumber} )->categorycode;
473 # get only the columns of a borrower
474 my $schema = Koha::Database->new()->schema;
475 my @columns = $schema->source('Borrower')->columns;
476 my $new_borrower = { map { join(' ', @columns) =~ /$_/ ? ( $_ => $data{$_} ) : () } keys(%data) };
478 $new_borrower->{dateofbirth} ||= undef if exists $new_borrower->{dateofbirth};
479 $new_borrower->{dateenrolled} ||= undef if exists $new_borrower->{dateenrolled};
480 $new_borrower->{dateexpiry} ||= undef if exists $new_borrower->{dateexpiry};
481 $new_borrower->{debarred} ||= undef if exists $new_borrower->{debarred};
482 $new_borrower->{sms_provider_id} ||= undef if exists $new_borrower->{sms_provider_id};
483 $new_borrower->{guarantorid} ||= undef if exists $new_borrower->{guarantorid};
485 my $patron = Koha::Patrons->find( $new_borrower->{borrowernumber} );
487 delete $new_borrower->{userid} if exists $new_borrower->{userid} and not $new_borrower->{userid};
489 my $execute_success = $patron->store if $patron->set($new_borrower);
491 if ($execute_success) { # only proceed if the update was a success
492 # If the patron changes to a category with enrollment fee, we add a fee
493 if ( $data{categorycode} and $data{categorycode} ne $old_categorycode ) {
494 if ( C4::Context->preference('FeeOnChangePatronCategory') ) {
495 $patron->add_enrolment_fee_if_needed;
499 # If NorwegianPatronDBEnable is enabled, we set syncstatus to something that a
500 # cronjob will use for syncing with NL
501 if ( C4::Context->preference('NorwegianPatronDBEnable') && C4::Context->preference('NorwegianPatronDBEnable') == 1 ) {
502 my $borrowersync = Koha::Database->new->schema->resultset('BorrowerSync')->find({
503 'synctype' => 'norwegianpatrondb',
504 'borrowernumber' => $data{'borrowernumber'}
506 # Do not set to "edited" if syncstatus is "new". We need to sync as new before
507 # we can sync as changed. And the "new sync" will pick up all changes since
508 # the patron was created anyway.
509 if ( $borrowersync->syncstatus ne 'new' && $borrowersync->syncstatus ne 'delete' ) {
510 $borrowersync->update( { 'syncstatus' => 'edited' } );
512 # Set the value of 'sync'
513 $borrowersync->update( { 'sync' => $data{'sync'} } );
514 # Try to do the live sync
515 Koha::NorwegianPatronDB::NLSync({ 'borrowernumber' => $data{'borrowernumber'} });
518 logaction("MEMBERS", "MODIFY", $data{'borrowernumber'}, "UPDATE (executed w/ arg: $data{'borrowernumber'})") if C4::Context->preference("BorrowersLog");
520 return $execute_success;
525 $borrowernumber = &AddMember(%borrower);
527 insert new borrower into table
529 (%borrower keys are database columns. Database columns could be
530 different in different versions. Please look into database for correct
533 Returns the borrowernumber upon success
535 Returns as undef upon any db error without further processing
542 my $dbh = C4::Context->dbh;
543 my $schema = Koha::Database->new()->schema;
545 # generate a proper login if none provided
546 $data{'userid'} = Generate_Userid( $data{'borrowernumber'}, $data{'firstname'}, $data{'surname'} )
547 if ( $data{'userid'} eq '' || !Check_Userid( $data{'userid'} ) );
549 # add expiration date if it isn't already there
550 $data{dateexpiry} ||= Koha::Patron::Categories->find( $data{categorycode} )->get_expiry_date;
552 # add enrollment date if it isn't already there
553 unless ( $data{'dateenrolled'} ) {
554 $data{'dateenrolled'} = output_pref( { dt => dt_from_string, dateonly => 1, dateformat => 'iso' } );
557 my $patron_category = $schema->resultset('Category')->find( $data{'categorycode'} );
559 $patron_category->default_privacy() eq 'default' ? 1
560 : $patron_category->default_privacy() eq 'never' ? 2
561 : $patron_category->default_privacy() eq 'forever' ? 0
564 $data{'privacy_guarantor_checkouts'} = 0 unless defined( $data{'privacy_guarantor_checkouts'} );
566 # Make a copy of the plain text password for later use
567 my $plain_text_password = $data{'password'};
569 # create a disabled account if no password provided
570 $data{'password'} = ($data{'password'})? hash_password($data{'password'}) : '!';
572 # we don't want invalid dates in the db (mysql has a bad habit of inserting 0000-00-00
573 $data{'dateofbirth'} = undef if ( not $data{'dateofbirth'} );
574 $data{'debarred'} = undef if ( not $data{'debarred'} );
575 $data{'sms_provider_id'} = undef if ( not $data{'sms_provider_id'} );
577 # get only the columns of Borrower
578 # FIXME Do we really need this check?
579 my @columns = $schema->source('Borrower')->columns;
580 my $new_member = { map { join(' ',@columns) =~ /$_/ ? ( $_ => $data{$_} ) : () } keys(%data) } ;
582 delete $new_member->{borrowernumber};
584 my $patron = Koha::Patron->new( $new_member )->store;
585 $data{borrowernumber} = $patron->borrowernumber;
587 # If NorwegianPatronDBEnable is enabled, we set syncstatus to something that a
588 # cronjob will use for syncing with NL
589 if ( exists $data{'borrowernumber'} && C4::Context->preference('NorwegianPatronDBEnable') && C4::Context->preference('NorwegianPatronDBEnable') == 1 ) {
590 Koha::Database->new->schema->resultset('BorrowerSync')->create({
591 'borrowernumber' => $data{'borrowernumber'},
592 'synctype' => 'norwegianpatrondb',
594 'syncstatus' => 'new',
595 'hashed_pin' => Koha::NorwegianPatronDB::NLEncryptPIN( $plain_text_password ),
599 logaction("MEMBERS", "CREATE", $data{'borrowernumber'}, "") if C4::Context->preference("BorrowersLog");
601 $patron->add_enrolment_fee_if_needed;
603 return $data{borrowernumber};
608 my $uniqueness = Check_Userid($userid,$borrowernumber);
610 $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 != '').
612 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.
615 0 for not unique (i.e. this $userid already exists)
616 1 for unique (i.e. this $userid does not exist, or this $userid/$borrowernumber combination already exists)
621 my ( $uid, $borrowernumber ) = @_;
623 return 0 unless ($uid); # userid is a unique column, we should assume NULL is not unique
625 return 0 if ( $uid eq C4::Context->config('user') );
627 my $rs = Koha::Database->new()->schema()->resultset('Borrower');
630 $params->{userid} = $uid;
631 $params->{borrowernumber} = { '!=' => $borrowernumber } if ($borrowernumber);
633 my $count = $rs->count( $params );
635 return $count ? 0 : 1;
638 =head2 Generate_Userid
640 my $newuid = Generate_Userid($borrowernumber, $firstname, $surname);
642 Generate a userid using the $surname and the $firstname (if there is a value in $firstname).
644 $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.
647 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).
651 sub Generate_Userid {
652 my ($borrowernumber, $firstname, $surname) = @_;
655 #The script will "do" the following code and increment the $offset until Check_Userid = 1 (i.e. until $newuid comes back as unique)
657 $firstname =~ s/[[:digit:][:space:][:blank:][:punct:][:cntrl:]]//g;
658 $surname =~ s/[[:digit:][:space:][:blank:][:punct:][:cntrl:]]//g;
659 $newuid = lc(($firstname)? "$firstname.$surname" : $surname);
660 $newuid = unac_string('utf-8',$newuid);
661 $newuid .= $offset unless $offset == 0;
664 } while (!Check_Userid($newuid,$borrowernumber));
669 =head2 fixup_cardnumber
671 Warning: The caller is responsible for locking the members table in write
672 mode, to avoid database corruption.
676 use vars qw( @weightings );
677 my @weightings = ( 8, 4, 6, 3, 5, 2, 1 );
679 sub fixup_cardnumber {
680 my ($cardnumber) = @_;
681 my $autonumber_members = C4::Context->boolean_preference('autoMemberNum') || 0;
683 # Find out whether member numbers should be generated
684 # automatically. Should be either "1" or something else.
685 # Defaults to "0", which is interpreted as "no".
687 # if ($cardnumber !~ /\S/ && $autonumber_members) {
688 ($autonumber_members) or return $cardnumber;
689 my $checkdigit = C4::Context->preference('checkdigit');
690 my $dbh = C4::Context->dbh;
691 if ( $checkdigit and $checkdigit eq 'katipo' ) {
693 # if checkdigit is selected, calculate katipo-style cardnumber.
694 # otherwise, just use the max()
695 # purpose: generate checksum'd member numbers.
696 # We'll assume we just got the max value of digits 2-8 of member #'s
697 # from the database and our job is to increment that by one,
698 # determine the 1st and 9th digits and return the full string.
699 my $sth = $dbh->prepare(
700 "select max(substring(borrowers.cardnumber,2,7)) as new_num from borrowers"
703 my $data = $sth->fetchrow_hashref;
704 $cardnumber = $data->{new_num};
705 if ( !$cardnumber ) { # If DB has no values,
706 $cardnumber = 1000000; # start at 1000000
712 for ( my $i = 0 ; $i < 8 ; $i += 1 ) {
713 # read weightings, left to right, 1 char at a time
714 my $temp1 = $weightings[$i];
716 # sequence left to right, 1 char at a time
717 my $temp2 = substr( $cardnumber, $i, 1 );
719 # mult each char 1-7 by its corresponding weighting
720 $sum += $temp1 * $temp2;
723 my $rem = ( $sum % 11 );
724 $rem = 'X' if $rem == 10;
726 return "V$cardnumber$rem";
729 my $sth = $dbh->prepare(
730 'SELECT MAX( CAST( cardnumber AS SIGNED ) ) FROM borrowers WHERE cardnumber REGEXP "^-?[0-9]+$"'
733 my ($result) = $sth->fetchrow;
736 return $cardnumber; # just here as a fallback/reminder
739 =head2 GetPendingIssues
741 my $issues = &GetPendingIssues(@borrowernumber);
743 Looks up what the patron with the given borrowernumber has borrowed.
745 C<&GetPendingIssues> returns a
746 reference-to-array where each element is a reference-to-hash; the
747 keys are the fields from the C<issues>, C<biblio>, and C<items> tables.
748 The keys include C<biblioitems> fields except marc and marcxml.
752 sub GetPendingIssues {
753 my @borrowernumbers = @_;
755 unless (@borrowernumbers ) { # return a ref_to_array
756 return \@borrowernumbers; # to not cause surprise to caller
759 # Borrowers part of the query
761 for (my $i = 0; $i < @borrowernumbers; $i++) {
762 $bquery .= ' issues.borrowernumber = ?';
763 if ($i < $#borrowernumbers ) {
768 # must avoid biblioitems.* to prevent large marc and marcxml fields from killing performance
769 # FIXME: namespace collision: each table has "timestamp" fields. Which one is "timestamp" ?
770 # FIXME: circ/ciculation.pl tries to sort by timestamp!
771 # FIXME: namespace collision: other collisions possible.
772 # FIXME: most of this data isn't really being used by callers.
779 biblioitems.itemtype,
782 biblioitems.publicationyear,
783 biblioitems.publishercode,
784 biblioitems.volumedate,
785 biblioitems.volumedesc,
790 borrowers.cardnumber,
791 issues.timestamp AS timestamp,
792 issues.renewals AS renewals,
793 issues.borrowernumber AS borrowernumber,
794 items.renewals AS totalrenewals
796 LEFT JOIN items ON items.itemnumber = issues.itemnumber
797 LEFT JOIN biblio ON items.biblionumber = biblio.biblionumber
798 LEFT JOIN biblioitems ON items.biblioitemnumber = biblioitems.biblioitemnumber
799 LEFT JOIN borrowers ON issues.borrowernumber = borrowers.borrowernumber
802 ORDER BY issues.issuedate"
805 my $sth = C4::Context->dbh->prepare($query);
806 $sth->execute(@borrowernumbers);
807 my $data = $sth->fetchall_arrayref({});
808 my $today = dt_from_string;
810 if ($_->{issuedate}) {
811 $_->{issuedate} = dt_from_string($_->{issuedate}, 'sql');
813 $_->{date_due_sql} = $_->{date_due};
814 # FIXME no need to have this value
815 $_->{date_due} or next;
816 $_->{date_due_sql} = $_->{date_due};
817 # FIXME no need to have this value
818 $_->{date_due} = dt_from_string($_->{date_due}, 'sql');
819 if ( DateTime->compare($_->{date_due}, $today) == -1 ) {
828 $issues = &GetAllIssues($borrowernumber, $sortkey, $limit);
830 Looks up what the patron with the given borrowernumber has borrowed,
831 and sorts the results.
833 C<$sortkey> is the name of a field on which to sort the results. This
834 should be the name of a field in the C<issues>, C<biblio>,
835 C<biblioitems>, or C<items> table in the Koha database.
837 C<$limit> is the maximum number of results to return.
839 C<&GetAllIssues> an arrayref, C<$issues>, of hashrefs, the keys of which
840 are the fields from the C<issues>, C<biblio>, C<biblioitems>, and
841 C<items> tables of the Koha database.
847 my ( $borrowernumber, $order, $limit ) = @_;
849 return unless $borrowernumber;
850 $order = 'date_due desc' unless $order;
852 my $dbh = C4::Context->dbh;
854 'SELECT *, issues.timestamp as issuestimestamp, issues.renewals AS renewals,items.renewals AS totalrenewals,items.timestamp AS itemstimestamp
856 LEFT JOIN items on items.itemnumber=issues.itemnumber
857 LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber
858 LEFT JOIN biblioitems ON items.biblioitemnumber=biblioitems.biblioitemnumber
859 WHERE borrowernumber=?
861 SELECT *, old_issues.timestamp as issuestimestamp, old_issues.renewals AS renewals,items.renewals AS totalrenewals,items.timestamp AS itemstimestamp
863 LEFT JOIN items on items.itemnumber=old_issues.itemnumber
864 LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber
865 LEFT JOIN biblioitems ON items.biblioitemnumber=biblioitems.biblioitemnumber
866 WHERE borrowernumber=? AND old_issues.itemnumber IS NOT NULL
869 $query .= " limit $limit";
872 my $sth = $dbh->prepare($query);
873 $sth->execute( $borrowernumber, $borrowernumber );
874 return $sth->fetchall_arrayref( {} );
878 =head2 GetMemberAccountRecords
880 ($total, $acctlines, $count) = &GetMemberAccountRecords($borrowernumber);
882 Looks up accounting data for the patron with the given borrowernumber.
884 C<&GetMemberAccountRecords> returns a three-element array. C<$acctlines> is a
885 reference-to-array, where each element is a reference-to-hash; the
886 keys are the fields of the C<accountlines> table in the Koha database.
887 C<$count> is the number of elements in C<$acctlines>. C<$total> is the
888 total amount outstanding for all of the account lines.
892 sub GetMemberAccountRecords {
893 my ($borrowernumber) = @_;
894 my $dbh = C4::Context->dbh;
900 WHERE borrowernumber=?);
901 $strsth.=" ORDER BY accountlines_id desc";
902 my $sth= $dbh->prepare( $strsth );
903 $sth->execute( $borrowernumber );
906 while ( my $data = $sth->fetchrow_hashref ) {
907 if ( $data->{itemnumber} ) {
908 my $biblio = GetBiblioFromItemNumber( $data->{itemnumber} );
909 $data->{biblionumber} = $biblio->{biblionumber};
910 $data->{title} = $biblio->{title};
912 $acctlines[$numlines] = $data;
914 $total += sprintf "%.0f", 1000*$data->{amountoutstanding}; # convert float to integer to avoid round-off errors
917 return ( $total, \@acctlines,$numlines);
920 =head2 GetMemberAccountBalance
922 ($total_balance, $non_issue_balance, $other_charges) = &GetMemberAccountBalance($borrowernumber);
924 Calculates amount immediately owing by the patron - non-issue charges.
925 Based on GetMemberAccountRecords.
926 Charges exempt from non-issue are:
928 * Rent (rental) if RentalsInNoissuesCharge syspref is set to false
929 * Manual invoices if ManInvInNoissuesCharge syspref is set to false
933 sub GetMemberAccountBalance {
934 my ($borrowernumber) = @_;
936 my $ACCOUNT_TYPE_LENGTH = 5; # this is plain ridiculous...
939 push @not_fines, 'Res' unless C4::Context->preference('HoldsInNoissuesCharge');
940 push @not_fines, 'Rent' unless C4::Context->preference('RentalsInNoissuesCharge');
941 unless ( C4::Context->preference('ManInvInNoissuesCharge') ) {
942 my $dbh = C4::Context->dbh;
943 my $man_inv_types = $dbh->selectcol_arrayref(qq{SELECT authorised_value FROM authorised_values WHERE category = 'MANUAL_INV'});
944 push @not_fines, map substr($_, 0, $ACCOUNT_TYPE_LENGTH), @$man_inv_types;
946 my %not_fine = map {$_ => 1} @not_fines;
948 my ($total, $acctlines) = GetMemberAccountRecords($borrowernumber);
949 my $other_charges = 0;
950 foreach (@$acctlines) {
951 $other_charges += $_->{amountoutstanding} if $not_fine{ substr($_->{accounttype}, 0, $ACCOUNT_TYPE_LENGTH) };
954 return ( $total, $total - $other_charges, $other_charges);
957 =head2 GetBorNotifyAcctRecord
959 ($total, $acctlines, $count) = &GetBorNotifyAcctRecord($params,$notifyid);
961 Looks up accounting data for the patron with the given borrowernumber per file number.
963 C<&GetBorNotifyAcctRecord> returns a three-element array. C<$acctlines> is a
964 reference-to-array, where each element is a reference-to-hash; the
965 keys are the fields of the C<accountlines> table in the Koha database.
966 C<$count> is the number of elements in C<$acctlines>. C<$total> is the
967 total amount outstanding for all of the account lines.
971 sub GetBorNotifyAcctRecord {
972 my ( $borrowernumber, $notifyid ) = @_;
973 my $dbh = C4::Context->dbh;
976 my $sth = $dbh->prepare(
979 WHERE borrowernumber=?
981 AND amountoutstanding != '0'
982 ORDER BY notify_id,accounttype
985 $sth->execute( $borrowernumber, $notifyid );
987 while ( my $data = $sth->fetchrow_hashref ) {
988 if ( $data->{itemnumber} ) {
989 my $biblio = GetBiblioFromItemNumber( $data->{itemnumber} );
990 $data->{biblionumber} = $biblio->{biblionumber};
991 $data->{title} = $biblio->{title};
993 $acctlines[$numlines] = $data;
995 $total += int(100 * $data->{'amountoutstanding'});
998 return ( $total, \@acctlines, $numlines );
1001 sub checkcardnumber {
1002 my ( $cardnumber, $borrowernumber ) = @_;
1004 # If cardnumber is null, we assume they're allowed.
1005 return 0 unless defined $cardnumber;
1007 my $dbh = C4::Context->dbh;
1008 my $query = "SELECT * FROM borrowers WHERE cardnumber=?";
1009 $query .= " AND borrowernumber <> ?" if ($borrowernumber);
1010 my $sth = $dbh->prepare($query);
1013 ( $borrowernumber ? $borrowernumber : () )
1016 return 1 if $sth->fetchrow_hashref;
1018 my ( $min_length, $max_length ) = get_cardnumber_length();
1020 if length $cardnumber > $max_length
1021 or length $cardnumber < $min_length;
1026 =head2 get_cardnumber_length
1028 my ($min, $max) = C4::Members::get_cardnumber_length()
1030 Returns the minimum and maximum length for patron cardnumbers as
1031 determined by the CardnumberLength system preference, the
1032 BorrowerMandatoryField system preference, and the width of the
1037 sub get_cardnumber_length {
1038 my ( $min, $max ) = ( 0, 16 ); # borrowers.cardnumber is a nullable varchar(16)
1039 $min = 1 if C4::Context->preference('BorrowerMandatoryField') =~ /cardnumber/;
1040 if ( my $cardnumber_length = C4::Context->preference('CardnumberLength') ) {
1041 # Is integer and length match
1042 if ( $cardnumber_length =~ m|^\d+$| ) {
1043 $min = $max = $cardnumber_length
1044 if $cardnumber_length >= $min
1045 and $cardnumber_length <= $max;
1047 # Else assuming it is a range
1048 elsif ( $cardnumber_length =~ m|(\d*),(\d*)| ) {
1049 $min = $1 if $1 and $min < $1;
1050 $max = $2 if $2 and $max > $2;
1054 my $borrower = Koha::Schema->resultset('Borrower');
1055 my $field_size = $borrower->result_source->column_info('cardnumber')->{size};
1056 $min = $field_size if $min > $field_size;
1057 return ( $min, $max );
1060 =head2 GetFirstValidEmailAddress
1062 $email = GetFirstValidEmailAddress($borrowernumber);
1064 Return the first valid email address for a borrower, given the borrowernumber. For now, the order
1065 is defined as email, emailpro, B_email. Returns the empty string if the borrower has no email
1070 sub GetFirstValidEmailAddress {
1071 my $borrowernumber = shift;
1072 my $dbh = C4::Context->dbh;
1073 my $sth = $dbh->prepare( "SELECT email, emailpro, B_email FROM borrowers where borrowernumber = ? ");
1074 $sth->execute( $borrowernumber );
1075 my $data = $sth->fetchrow_hashref;
1077 if ($data->{'email'}) {
1078 return $data->{'email'};
1079 } elsif ($data->{'emailpro'}) {
1080 return $data->{'emailpro'};
1081 } elsif ($data->{'B_email'}) {
1082 return $data->{'B_email'};
1088 =head2 GetNoticeEmailAddress
1090 $email = GetNoticeEmailAddress($borrowernumber);
1092 Return the email address of borrower used for notices, given the borrowernumber.
1093 Returns the empty string if no email address.
1097 sub GetNoticeEmailAddress {
1098 my $borrowernumber = shift;
1100 my $which_address = C4::Context->preference("AutoEmailPrimaryAddress");
1101 # if syspref is set to 'first valid' (value == OFF), look up email address
1102 if ( $which_address eq 'OFF' ) {
1103 return GetFirstValidEmailAddress($borrowernumber);
1105 # specified email address field
1106 my $dbh = C4::Context->dbh;
1107 my $sth = $dbh->prepare( qq{
1108 SELECT $which_address AS primaryemail
1110 WHERE borrowernumber=?
1112 $sth->execute($borrowernumber);
1113 my $data = $sth->fetchrow_hashref;
1114 return $data->{'primaryemail'} || '';
1117 =head2 GetUpcomingMembershipExpires
1119 my $expires = GetUpcomingMembershipExpires({
1120 branch => $branch, before => $before, after => $after,
1123 $branch is an optional branch code.
1124 $before/$after is an optional number of days before/after the date that
1125 is set by the preference MembershipExpiryDaysNotice.
1126 If the pref would be 14, before 2 and after 3, you will get all expires
1131 sub GetUpcomingMembershipExpires {
1132 my ( $params ) = @_;
1133 my $before = $params->{before} || 0;
1134 my $after = $params->{after} || 0;
1135 my $branch = $params->{branch};
1137 my $dbh = C4::Context->dbh;
1138 my $days = C4::Context->preference("MembershipExpiryDaysNotice") || 0;
1139 my $date1 = dt_from_string->add( days => $days - $before );
1140 my $date2 = dt_from_string->add( days => $days + $after );
1141 $date1= output_pref({ dt => $date1, dateformat => 'iso', dateonly => 1 });
1142 $date2= output_pref({ dt => $date2, dateformat => 'iso', dateonly => 1 });
1145 SELECT borrowers.*, categories.description,
1146 branches.branchname, branches.branchemail FROM borrowers
1147 LEFT JOIN branches USING (branchcode)
1148 LEFT JOIN categories USING (categorycode)
1151 $query.= 'WHERE branchcode=? AND dateexpiry BETWEEN ? AND ?';
1153 $query.= 'WHERE dateexpiry BETWEEN ? AND ?';
1156 my $sth = $dbh->prepare( $query );
1157 my @pars = $branch? ( $branch ): ();
1158 push @pars, $date1, $date2;
1159 $sth->execute( @pars );
1160 my $results = $sth->fetchall_arrayref( {} );
1164 =head2 GetBorrowersToExpunge
1166 $borrowers = &GetBorrowersToExpunge(
1167 not_borrowed_since => $not_borrowed_since,
1168 expired_before => $expired_before,
1169 category_code => $category_code,
1170 patron_list_id => $patron_list_id,
1171 branchcode => $branchcode
1174 This function get all borrowers based on the given criteria.
1178 sub GetBorrowersToExpunge {
1181 my $filterdate = $params->{'not_borrowed_since'};
1182 my $filterexpiry = $params->{'expired_before'};
1183 my $filterlastseen = $params->{'last_seen'};
1184 my $filtercategory = $params->{'category_code'};
1185 my $filterbranch = $params->{'branchcode'} ||
1186 ((C4::Context->preference('IndependentBranches')
1187 && C4::Context->userenv
1188 && !C4::Context->IsSuperLibrarian()
1189 && C4::Context->userenv->{branch})
1190 ? C4::Context->userenv->{branch}
1192 my $filterpatronlist = $params->{'patron_list_id'};
1194 my $dbh = C4::Context->dbh;
1196 SELECT borrowers.borrowernumber,
1197 MAX(old_issues.timestamp) AS latestissue,
1198 MAX(issues.timestamp) AS currentissue
1200 JOIN categories USING (categorycode)
1204 WHERE guarantorid IS NOT NULL
1205 AND guarantorid <> 0
1206 ) as tmp ON borrowers.borrowernumber=tmp.guarantorid
1207 LEFT JOIN old_issues USING (borrowernumber)
1208 LEFT JOIN issues USING (borrowernumber)|;
1209 if ( $filterpatronlist ){
1210 $query .= q| LEFT JOIN patron_list_patrons USING (borrowernumber)|;
1212 $query .= q| WHERE category_type <> 'S'
1213 AND tmp.guarantorid IS NULL
1216 if ( $filterbranch && $filterbranch ne "" ) {
1217 $query.= " AND borrowers.branchcode = ? ";
1218 push( @query_params, $filterbranch );
1220 if ( $filterexpiry ) {
1221 $query .= " AND dateexpiry < ? ";
1222 push( @query_params, $filterexpiry );
1224 if ( $filterlastseen ) {
1225 $query .= ' AND lastseen < ? ';
1226 push @query_params, $filterlastseen;
1228 if ( $filtercategory ) {
1229 $query .= " AND categorycode = ? ";
1230 push( @query_params, $filtercategory );
1232 if ( $filterpatronlist ){
1233 $query.=" AND patron_list_id = ? ";
1234 push( @query_params, $filterpatronlist );
1236 $query.=" GROUP BY borrowers.borrowernumber HAVING currentissue IS NULL ";
1237 if ( $filterdate ) {
1238 $query.=" AND ( latestissue < ? OR latestissue IS NULL ) ";
1239 push @query_params,$filterdate;
1241 warn $query if $debug;
1243 my $sth = $dbh->prepare($query);
1244 if (scalar(@query_params)>0){
1245 $sth->execute(@query_params);
1252 while ( my $data = $sth->fetchrow_hashref ) {
1253 push @results, $data;
1258 =head2 GetBorrowersWhoHaveNeverBorrowed
1260 $results = &GetBorrowersWhoHaveNeverBorrowed
1262 This function get all borrowers who have never borrowed.
1264 I<$result> is a ref to an array which all elements are a hasref.
1268 sub GetBorrowersWhoHaveNeverBorrowed {
1269 my $filterbranch = shift ||
1270 ((C4::Context->preference('IndependentBranches')
1271 && C4::Context->userenv
1272 && !C4::Context->IsSuperLibrarian()
1273 && C4::Context->userenv->{branch})
1274 ? C4::Context->userenv->{branch}
1276 my $dbh = C4::Context->dbh;
1278 SELECT borrowers.borrowernumber,max(timestamp) as latestissue
1280 LEFT JOIN issues ON borrowers.borrowernumber = issues.borrowernumber
1281 WHERE issues.borrowernumber IS NULL
1284 if ($filterbranch && $filterbranch ne ""){
1285 $query.=" AND borrowers.branchcode= ?";
1286 push @query_params,$filterbranch;
1288 warn $query if $debug;
1290 my $sth = $dbh->prepare($query);
1291 if (scalar(@query_params)>0){
1292 $sth->execute(@query_params);
1299 while ( my $data = $sth->fetchrow_hashref ) {
1300 push @results, $data;
1305 =head2 GetBorrowersWithIssuesHistoryOlderThan
1307 $results = &GetBorrowersWithIssuesHistoryOlderThan($date)
1309 this function get all borrowers who has an issue history older than I<$date> given on input arg.
1311 I<$result> is a ref to an array which all elements are a hashref.
1312 This hashref is containt the number of time this borrowers has borrowed before I<$date> and the borrowernumber.
1316 sub GetBorrowersWithIssuesHistoryOlderThan {
1317 my $dbh = C4::Context->dbh;
1318 my $date = shift ||POSIX::strftime("%Y-%m-%d",localtime());
1319 my $filterbranch = shift ||
1320 ((C4::Context->preference('IndependentBranches')
1321 && C4::Context->userenv
1322 && !C4::Context->IsSuperLibrarian()
1323 && C4::Context->userenv->{branch})
1324 ? C4::Context->userenv->{branch}
1327 SELECT count(borrowernumber) as n,borrowernumber
1329 WHERE returndate < ?
1330 AND borrowernumber IS NOT NULL
1333 push @query_params, $date;
1335 $query.=" AND branchcode = ?";
1336 push @query_params, $filterbranch;
1338 $query.=" GROUP BY borrowernumber ";
1339 warn $query if $debug;
1340 my $sth = $dbh->prepare($query);
1341 $sth->execute(@query_params);
1344 while ( my $data = $sth->fetchrow_hashref ) {
1345 push @results, $data;
1352 IssueSlip($branchcode, $borrowernumber, $quickslip)
1354 Returns letter hash ( see C4::Letters::GetPreparedLetter )
1356 $quickslip is boolean, to indicate whether we want a quick slip
1358 IssueSlip populates ISSUESLIP and ISSUEQSLIP, and will make the following expansions:
1394 NOTE: Not all table fields are available, pleasee see GetPendingIssues for a list of available fields.
1399 my ($branch, $borrowernumber, $quickslip) = @_;
1401 # FIXME Check callers before removing this statement
1402 #return unless $borrowernumber;
1404 my @issues = @{ GetPendingIssues($borrowernumber) };
1406 for my $issue (@issues) {
1407 $issue->{date_due} = $issue->{date_due_sql};
1409 my $today = output_pref({ dt => dt_from_string, dateformat => 'iso', dateonly => 1 });
1410 if ( substr( $issue->{issuedate}, 0, 10 ) eq $today
1411 or substr( $issue->{lastreneweddate}, 0, 10 ) eq $today ) {
1417 # Sort on timestamp then on issuedate (useful for tests and could be if modified in a batch
1419 my $s = $b->{timestamp} <=> $a->{timestamp};
1421 $b->{issuedate} <=> $a->{issuedate} : $s;
1424 my ($letter_code, %repeat);
1426 $letter_code = 'ISSUEQSLIP';
1428 'checkedout' => [ map {
1431 'biblioitems' => $_,
1433 }, grep { $_->{'now'} } @issues ],
1437 $letter_code = 'ISSUESLIP';
1439 'checkedout' => [ map {
1442 'biblioitems' => $_,
1444 }, grep { !$_->{'overdue'} } @issues ],
1446 'overdue' => [ map {
1449 'biblioitems' => $_,
1451 }, grep { $_->{'overdue'} } @issues ],
1454 $_->{'timestamp'} = $_->{'newdate'};
1456 } @{ GetNewsToDisplay("slip",$branch) } ],
1460 return C4::Letters::GetPreparedLetter (
1461 module => 'circulation',
1462 letter_code => $letter_code,
1463 branchcode => $branch,
1465 'branches' => $branch,
1466 'borrowers' => $borrowernumber,
1472 =head2 GetBorrowersWithEmail
1474 ([$borrnum,$userid], ...) = GetBorrowersWithEmail('me@example.com');
1476 This gets a list of users and their basic details from their email address.
1477 As it's possible for multiple user to have the same email address, it provides
1478 you with all of them. If there is no userid for the user, there will be an
1479 C<undef> there. An empty list will be returned if there are no matches.
1483 sub GetBorrowersWithEmail {
1486 my $dbh = C4::Context->dbh;
1488 my $query = "SELECT borrowernumber, userid FROM borrowers WHERE email=?";
1489 my $sth=$dbh->prepare($query);
1490 $sth->execute($email);
1492 while (my $ref = $sth->fetch) {
1495 die "Failure searching for borrowers by email address: $sth->errstr" if $sth->err;
1499 =head2 AddMember_Opac
1503 sub AddMember_Opac {
1504 my ( %borrower ) = @_;
1506 $borrower{'categorycode'} //= C4::Context->preference('PatronSelfRegistrationDefaultCategory');
1507 if (not defined $borrower{'password'}){
1508 my $sr = new String::Random;
1509 $sr->{'A'} = [ 'A'..'Z', 'a'..'z' ];
1510 my $password = $sr->randpattern("AAAAAAAAAA");
1511 $borrower{'password'} = $password;
1514 $borrower{'cardnumber'} = fixup_cardnumber( $borrower{'cardnumber'} );
1516 my $borrowernumber = AddMember(%borrower);
1518 return ( $borrowernumber, $borrower{'password'} );
1521 =head2 DeleteExpiredOpacRegistrations
1523 Delete accounts that haven't been upgraded from the 'temporary' category
1524 Returns the number of removed patrons
1528 sub DeleteExpiredOpacRegistrations {
1530 my $delay = C4::Context->preference('PatronSelfRegistrationExpireTemporaryAccountsDelay');
1531 my $category_code = C4::Context->preference('PatronSelfRegistrationDefaultCategory');
1533 return 0 if not $category_code or not defined $delay or $delay eq q||;
1536 SELECT borrowernumber
1538 WHERE categorycode = ? AND DATEDIFF( NOW(), dateenrolled ) > ? |;
1540 my $dbh = C4::Context->dbh;
1541 my $sth = $dbh->prepare($query);
1542 $sth->execute( $category_code, $delay );
1544 while ( my ($borrowernumber) = $sth->fetchrow_array() ) {
1545 Koha::Patrons->find($borrowernumber)->delete;
1551 =head2 DeleteUnverifiedOpacRegistrations
1553 Delete all unverified self registrations in borrower_modifications,
1554 older than the specified number of days.
1558 sub DeleteUnverifiedOpacRegistrations {
1560 my $dbh = C4::Context->dbh;
1562 DELETE FROM borrower_modifications
1563 WHERE borrowernumber = 0 AND DATEDIFF( NOW(), timestamp ) > ?|;
1564 my $cnt=$dbh->do($sql, undef, ($days) );
1565 return $cnt eq '0E0'? 0: $cnt;
1568 sub GetOverduesForPatron {
1569 my ( $borrowernumber ) = @_;
1573 FROM issues, items, biblio, biblioitems
1574 WHERE items.itemnumber=issues.itemnumber
1575 AND biblio.biblionumber = items.biblionumber
1576 AND biblio.biblionumber = biblioitems.biblionumber
1577 AND issues.borrowernumber = ?
1578 AND date_due < NOW()
1581 my $sth = C4::Context->dbh->prepare( $sql );
1582 $sth->execute( $borrowernumber );
1584 return $sth->fetchall_arrayref({});
1587 END { } # module clean-up code here (global destructor)