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 List::MoreUtils qw( uniq );
31 use C4::Log; # logaction
37 use C4::Members::Attributes qw(SearchIdMatchingAttribute UpdateBorrowerAttribute);
38 use C4::NewsChannels; #get slip news
42 use Text::Unaccent qw( unac_string );
43 use Koha::AuthUtils qw(hash_password);
46 use Koha::List::Patron;
48 use Koha::Patron::Categories;
51 our (@ISA,@EXPORT,@EXPORT_OK,$debug);
53 use Module::Load::Conditional qw( can_load );
54 if ( ! can_load( modules => { 'Koha::NorwegianPatronDB' => undef } ) ) {
55 $debug && warn "Unable to load Koha::NorwegianPatronDB";
60 $debug = $ENV{DEBUG} || 0;
68 &GetBorrowersToExpunge
93 C4::Members - Perl Module containing convenience functions for member handling
101 This module contains routines for adding, modifying and deleting members/patrons/borrowers
107 $flags = &patronflags($patron);
109 This function is not exported.
111 The following will be set where applicable:
112 $flags->{CHARGES}->{amount} Amount of debt
113 $flags->{CHARGES}->{noissues} Set if debt amount >$5.00 (or syspref noissuescharge)
114 $flags->{CHARGES}->{message} Message -- deprecated
116 $flags->{CREDITS}->{amount} Amount of credit
117 $flags->{CREDITS}->{message} Message -- deprecated
119 $flags->{ GNA } Patron has no valid address
120 $flags->{ GNA }->{noissues} Set for each GNA
121 $flags->{ GNA }->{message} "Borrower has no valid address" -- deprecated
123 $flags->{ LOST } Patron's card reported lost
124 $flags->{ LOST }->{noissues} Set for each LOST
125 $flags->{ LOST }->{message} Message -- deprecated
127 $flags->{DBARRED} Set if patron debarred, no access
128 $flags->{DBARRED}->{noissues} Set for each DBARRED
129 $flags->{DBARRED}->{message} Message -- deprecated
132 $flags->{ NOTES }->{message} The note itself. NOT deprecated
134 $flags->{ ODUES } Set if patron has overdue books.
135 $flags->{ ODUES }->{message} "Yes" -- deprecated
136 $flags->{ ODUES }->{itemlist} ref-to-array: list of overdue books
137 $flags->{ ODUES }->{itemlisttext} Text list of overdue items -- deprecated
139 $flags->{WAITING} Set if any of patron's reserves are available
140 $flags->{WAITING}->{message} Message -- deprecated
141 $flags->{WAITING}->{itemlist} ref-to-array: list of available items
145 =item C<$flags-E<gt>{ODUES}-E<gt>{itemlist}> is a reference-to-array listing the
146 overdue items. Its elements are references-to-hash, each describing an
147 overdue item. The keys are selected fields from the issues, biblio,
148 biblioitems, and items tables of the Koha database.
150 =item C<$flags-E<gt>{ODUES}-E<gt>{itemlisttext}> is a string giving a text listing of
151 the overdue items, one per line. Deprecated.
153 =item C<$flags-E<gt>{WAITING}-E<gt>{itemlist}> is a reference-to-array listing the
154 available items. Each element is a reference-to-hash whose keys are
155 fields from the reserves table of the Koha database.
159 All the "message" fields that include language generated in this function are deprecated,
160 because such strings belong properly in the display layer.
162 The "message" field that comes from the DB is OK.
166 # TODO: use {anonymous => hashes} instead of a dozen %flaginfo
167 # FIXME rename this function.
168 # DEPRECATED Do not use this subroutine!
171 my ( $patroninformation) = @_;
172 my $dbh=C4::Context->dbh;
173 my $patron = Koha::Patrons->find( $patroninformation->{borrowernumber} );
174 my $account = $patron->account;
175 my $owing = $account->non_issues_charges;
178 my $noissuescharge = C4::Context->preference("noissuescharge") || 5;
179 $flaginfo{'message'} = sprintf 'Patron owes %.02f', $owing;
180 $flaginfo{'amount'} = sprintf "%.02f", $owing;
181 if ( $owing > $noissuescharge && !C4::Context->preference("AllowFineOverride") ) {
182 $flaginfo{'noissues'} = 1;
184 $flags{'CHARGES'} = \%flaginfo;
186 elsif ( ( my $balance = $account->balance ) < 0 ) {
188 $flaginfo{'message'} = sprintf 'Patron has credit of %.02f', -$balance;
189 $flaginfo{'amount'} = sprintf "%.02f", $balance;
190 $flags{'CREDITS'} = \%flaginfo;
193 # Check the debt of the guarntees of this patron
194 my $no_issues_charge_guarantees = C4::Context->preference("NoIssuesChargeGuarantees");
195 $no_issues_charge_guarantees = undef unless looks_like_number( $no_issues_charge_guarantees );
196 if ( defined $no_issues_charge_guarantees ) {
197 my $p = Koha::Patrons->find( $patroninformation->{borrowernumber} );
198 my @guarantees = $p->guarantees();
199 my $guarantees_non_issues_charges;
200 foreach my $g ( @guarantees ) {
201 $guarantees_non_issues_charges += $g->account->non_issues_charges;
204 if ( $guarantees_non_issues_charges > $no_issues_charge_guarantees ) {
206 $flaginfo{'message'} = sprintf 'patron guarantees owe %.02f', $guarantees_non_issues_charges;
207 $flaginfo{'amount'} = $guarantees_non_issues_charges;
208 $flaginfo{'noissues'} = 1 unless C4::Context->preference("allowfineoverride");
209 $flags{'CHARGES_GUARANTEES'} = \%flaginfo;
213 if ( $patroninformation->{'gonenoaddress'}
214 && $patroninformation->{'gonenoaddress'} == 1 )
217 $flaginfo{'message'} = 'Borrower has no valid address.';
218 $flaginfo{'noissues'} = 1;
219 $flags{'GNA'} = \%flaginfo;
221 if ( $patroninformation->{'lost'} && $patroninformation->{'lost'} == 1 ) {
223 $flaginfo{'message'} = 'Borrower\'s card reported lost.';
224 $flaginfo{'noissues'} = 1;
225 $flags{'LOST'} = \%flaginfo;
227 if ( $patroninformation->{'debarred'} && check_date( split( /-/, $patroninformation->{'debarred'} ) ) ) {
228 if ( Date_to_Days(Date::Calc::Today) < Date_to_Days( split( /-/, $patroninformation->{'debarred'} ) ) ) {
230 $flaginfo{'debarredcomment'} = $patroninformation->{'debarredcomment'};
231 $flaginfo{'message'} = $patroninformation->{'debarredcomment'};
232 $flaginfo{'noissues'} = 1;
233 $flaginfo{'dateend'} = $patroninformation->{'debarred'};
234 $flags{'DBARRED'} = \%flaginfo;
237 if ( $patroninformation->{'borrowernotes'}
238 && $patroninformation->{'borrowernotes'} )
241 $flaginfo{'message'} = $patroninformation->{'borrowernotes'};
242 $flags{'NOTES'} = \%flaginfo;
244 my ( $odues, $itemsoverdue ) = C4::Overdues::checkoverdues($patroninformation->{'borrowernumber'});
245 if ( $odues && $odues > 0 ) {
247 $flaginfo{'message'} = "Yes";
248 $flaginfo{'itemlist'} = $itemsoverdue;
249 foreach ( sort { $a->{'date_due'} cmp $b->{'date_due'} }
252 $flaginfo{'itemlisttext'} .=
253 "$_->{'date_due'} $_->{'barcode'} $_->{'title'} \n"; # newline is display layer
255 $flags{'ODUES'} = \%flaginfo;
258 my $waiting_holds = $patron->holds->search({ found => 'W' });
259 my $nowaiting = $waiting_holds->count;
260 if ( $nowaiting > 0 ) {
262 $flaginfo{'message'} = "Reserved items available";
263 $flaginfo{'itemlist'} = $waiting_holds->unblessed;
264 $flags{'WAITING'} = \%flaginfo;
272 my $success = ModMember(borrowernumber => $borrowernumber,
273 [ field => value ]... );
275 Modify borrower's data. All date fields should ALREADY be in ISO format.
278 true on success, or false on failure
285 # trim whitespace from data which has some non-whitespace in it.
286 foreach my $field_name (keys(%data)) {
287 if ( defined $data{$field_name} && $data{$field_name} =~ /\S/ ) {
288 $data{$field_name} =~ s/^\s*|\s*$//g;
292 # test to know if you must update or not the borrower password
293 if (exists $data{password}) {
294 if ($data{password} eq '****' or $data{password} eq '') {
295 delete $data{password};
297 if ( C4::Context->preference('NorwegianPatronDBEnable') && C4::Context->preference('NorwegianPatronDBEnable') == 1 ) {
298 # Update the hashed PIN in borrower_sync.hashed_pin, before Koha hashes it
299 Koha::NorwegianPatronDB::NLUpdateHashedPIN( $data{'borrowernumber'}, $data{password} );
301 $data{password} = hash_password($data{password});
305 my $old_categorycode = Koha::Patrons->find( $data{borrowernumber} )->categorycode;
307 # get only the columns of a borrower
308 my $schema = Koha::Database->new()->schema;
309 my @columns = $schema->source('Borrower')->columns;
310 my $new_borrower = { map { join(' ', @columns) =~ /$_/ ? ( $_ => $data{$_} ) : () } keys(%data) };
312 $new_borrower->{dateofbirth} ||= undef if exists $new_borrower->{dateofbirth};
313 $new_borrower->{dateenrolled} ||= undef if exists $new_borrower->{dateenrolled};
314 $new_borrower->{dateexpiry} ||= undef if exists $new_borrower->{dateexpiry};
315 $new_borrower->{debarred} ||= undef if exists $new_borrower->{debarred};
316 $new_borrower->{sms_provider_id} ||= undef if exists $new_borrower->{sms_provider_id};
317 $new_borrower->{guarantorid} ||= undef if exists $new_borrower->{guarantorid};
319 my $patron = Koha::Patrons->find( $new_borrower->{borrowernumber} );
321 my $borrowers_log = C4::Context->preference("BorrowersLog");
322 if ( $borrowers_log && $patron->cardnumber ne $new_borrower->{cardnumber} )
327 $data{'borrowernumber'},
330 cardnumber_replaced => {
331 previous_cardnumber => $patron->cardnumber,
332 new_cardnumber => $new_borrower->{cardnumber},
335 { utf8 => 1, pretty => 1 }
340 delete $new_borrower->{userid} if exists $new_borrower->{userid} and not $new_borrower->{userid};
342 my $execute_success = $patron->store if $patron->set($new_borrower);
344 if ($execute_success) { # only proceed if the update was a success
345 # If the patron changes to a category with enrollment fee, we add a fee
346 if ( $data{categorycode} and $data{categorycode} ne $old_categorycode ) {
347 if ( C4::Context->preference('FeeOnChangePatronCategory') ) {
348 $patron->add_enrolment_fee_if_needed;
352 # If NorwegianPatronDBEnable is enabled, we set syncstatus to something that a
353 # cronjob will use for syncing with NL
354 if ( C4::Context->preference('NorwegianPatronDBEnable') && C4::Context->preference('NorwegianPatronDBEnable') == 1 ) {
355 my $borrowersync = Koha::Database->new->schema->resultset('BorrowerSync')->find({
356 'synctype' => 'norwegianpatrondb',
357 'borrowernumber' => $data{'borrowernumber'}
359 # Do not set to "edited" if syncstatus is "new". We need to sync as new before
360 # we can sync as changed. And the "new sync" will pick up all changes since
361 # the patron was created anyway.
362 if ( $borrowersync->syncstatus ne 'new' && $borrowersync->syncstatus ne 'delete' ) {
363 $borrowersync->update( { 'syncstatus' => 'edited' } );
365 # Set the value of 'sync'
366 $borrowersync->update( { 'sync' => $data{'sync'} } );
367 # Try to do the live sync
368 Koha::NorwegianPatronDB::NLSync({ 'borrowernumber' => $data{'borrowernumber'} });
371 logaction("MEMBERS", "MODIFY", $data{'borrowernumber'}, "UPDATE (executed w/ arg: $data{'borrowernumber'})") if $borrowers_log;
373 return $execute_success;
378 $issues = &GetAllIssues($borrowernumber, $sortkey, $limit);
380 Looks up what the patron with the given borrowernumber has borrowed,
381 and sorts the results.
383 C<$sortkey> is the name of a field on which to sort the results. This
384 should be the name of a field in the C<issues>, C<biblio>,
385 C<biblioitems>, or C<items> table in the Koha database.
387 C<$limit> is the maximum number of results to return.
389 C<&GetAllIssues> an arrayref, C<$issues>, of hashrefs, the keys of which
390 are the fields from the C<issues>, C<biblio>, C<biblioitems>, and
391 C<items> tables of the Koha database.
397 my ( $borrowernumber, $order, $limit ) = @_;
399 return unless $borrowernumber;
400 $order = 'date_due desc' unless $order;
402 my $dbh = C4::Context->dbh;
404 'SELECT *, issues.timestamp as issuestimestamp, issues.renewals AS renewals,items.renewals AS totalrenewals,items.timestamp AS itemstimestamp
406 LEFT JOIN items on items.itemnumber=issues.itemnumber
407 LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber
408 LEFT JOIN biblioitems ON items.biblioitemnumber=biblioitems.biblioitemnumber
409 WHERE borrowernumber=?
411 SELECT *, old_issues.timestamp as issuestimestamp, old_issues.renewals AS renewals,items.renewals AS totalrenewals,items.timestamp AS itemstimestamp
413 LEFT JOIN items on items.itemnumber=old_issues.itemnumber
414 LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber
415 LEFT JOIN biblioitems ON items.biblioitemnumber=biblioitems.biblioitemnumber
416 WHERE borrowernumber=? AND old_issues.itemnumber IS NOT NULL
419 $query .= " limit $limit";
422 my $sth = $dbh->prepare($query);
423 $sth->execute( $borrowernumber, $borrowernumber );
424 return $sth->fetchall_arrayref( {} );
427 sub checkcardnumber {
428 my ( $cardnumber, $borrowernumber ) = @_;
430 # If cardnumber is null, we assume they're allowed.
431 return 0 unless defined $cardnumber;
433 my $dbh = C4::Context->dbh;
434 my $query = "SELECT * FROM borrowers WHERE cardnumber=?";
435 $query .= " AND borrowernumber <> ?" if ($borrowernumber);
436 my $sth = $dbh->prepare($query);
439 ( $borrowernumber ? $borrowernumber : () )
442 return 1 if $sth->fetchrow_hashref;
444 my ( $min_length, $max_length ) = get_cardnumber_length();
446 if length $cardnumber > $max_length
447 or length $cardnumber < $min_length;
452 =head2 get_cardnumber_length
454 my ($min, $max) = C4::Members::get_cardnumber_length()
456 Returns the minimum and maximum length for patron cardnumbers as
457 determined by the CardnumberLength system preference, the
458 BorrowerMandatoryField system preference, and the width of the
463 sub get_cardnumber_length {
464 my $borrower = Koha::Schema->resultset('Borrower');
465 my $field_size = $borrower->result_source->column_info('cardnumber')->{size};
466 my ( $min, $max ) = ( 0, $field_size ); # borrowers.cardnumber is a nullable varchar(20)
467 $min = 1 if C4::Context->preference('BorrowerMandatoryField') =~ /cardnumber/;
468 if ( my $cardnumber_length = C4::Context->preference('CardnumberLength') ) {
469 # Is integer and length match
470 if ( $cardnumber_length =~ m|^\d+$| ) {
471 $min = $max = $cardnumber_length
472 if $cardnumber_length >= $min
473 and $cardnumber_length <= $max;
475 # Else assuming it is a range
476 elsif ( $cardnumber_length =~ m|(\d*),(\d*)| ) {
477 $min = $1 if $1 and $min < $1;
478 $max = $2 if $2 and $max > $2;
482 $min = $max if $min > $max;
483 return ( $min, $max );
486 =head2 GetBorrowersToExpunge
488 $borrowers = &GetBorrowersToExpunge(
489 not_borrowed_since => $not_borrowed_since,
490 expired_before => $expired_before,
491 category_code => $category_code,
492 patron_list_id => $patron_list_id,
493 branchcode => $branchcode
496 This function get all borrowers based on the given criteria.
500 sub GetBorrowersToExpunge {
503 my $filterdate = $params->{'not_borrowed_since'};
504 my $filterexpiry = $params->{'expired_before'};
505 my $filterlastseen = $params->{'last_seen'};
506 my $filtercategory = $params->{'category_code'};
507 my $filterbranch = $params->{'branchcode'} ||
508 ((C4::Context->preference('IndependentBranches')
509 && C4::Context->userenv
510 && !C4::Context->IsSuperLibrarian()
511 && C4::Context->userenv->{branch})
512 ? C4::Context->userenv->{branch}
514 my $filterpatronlist = $params->{'patron_list_id'};
516 my $dbh = C4::Context->dbh;
520 SELECT borrowers.borrowernumber,
521 MAX(old_issues.timestamp) AS latestissue,
522 MAX(issues.timestamp) AS currentissue
524 JOIN categories USING (categorycode)
528 WHERE guarantorid IS NOT NULL
530 ) as tmp ON borrowers.borrowernumber=tmp.guarantorid
531 LEFT JOIN old_issues USING (borrowernumber)
532 LEFT JOIN issues USING (borrowernumber)|;
533 if ( $filterpatronlist ){
534 $query .= q| LEFT JOIN patron_list_patrons USING (borrowernumber)|;
536 $query .= q| WHERE category_type <> 'S'
537 AND tmp.guarantorid IS NULL
540 if ( $filterbranch && $filterbranch ne "" ) {
541 $query.= " AND borrowers.branchcode = ? ";
542 push( @query_params, $filterbranch );
544 if ( $filterexpiry ) {
545 $query .= " AND dateexpiry < ? ";
546 push( @query_params, $filterexpiry );
548 if ( $filterlastseen ) {
549 $query .= ' AND lastseen < ? ';
550 push @query_params, $filterlastseen;
552 if ( $filtercategory ) {
553 $query .= " AND categorycode = ? ";
554 push( @query_params, $filtercategory );
556 if ( $filterpatronlist ){
557 $query.=" AND patron_list_id = ? ";
558 push( @query_params, $filterpatronlist );
560 $query .= " GROUP BY borrowers.borrowernumber";
562 ) xxx WHERE currentissue IS NULL|;
564 $query.=" AND ( latestissue < ? OR latestissue IS NULL ) ";
565 push @query_params,$filterdate;
568 warn $query if $debug;
570 my $sth = $dbh->prepare($query);
571 if (scalar(@query_params)>0){
572 $sth->execute(@query_params);
579 while ( my $data = $sth->fetchrow_hashref ) {
580 push @results, $data;
587 IssueSlip($branchcode, $borrowernumber, $quickslip)
589 Returns letter hash ( see C4::Letters::GetPreparedLetter )
591 $quickslip is boolean, to indicate whether we want a quick slip
593 IssueSlip populates ISSUESLIP and ISSUEQSLIP, and will make the following expansions:
629 NOTE: Fields from tables issues, items, biblio and biblioitems are available
634 my ($branch, $borrowernumber, $quickslip) = @_;
636 # FIXME Check callers before removing this statement
637 #return unless $borrowernumber;
639 my $patron = Koha::Patrons->find( $borrowernumber );
640 return unless $patron;
642 my $pending_checkouts = $patron->pending_checkouts; # Should be $patron->checkouts->pending?
644 my ($letter_code, %repeat, %loops);
646 my $today_start = dt_from_string->set( hour => 0, minute => 0, second => 0 );
647 my $today_end = dt_from_string->set( hour => 23, minute => 59, second => 0 );
648 $today_start = Koha::Database->new->schema->storage->datetime_parser->format_datetime( $today_start );
649 $today_end = Koha::Database->new->schema->storage->datetime_parser->format_datetime( $today_end );
650 $letter_code = 'ISSUEQSLIP';
652 # issue date or lastreneweddate is today
653 my $todays_checkouts = $pending_checkouts->search(
657 '>=' => $today_start,
661 { '>=' => $today_start, '<=' => $today_end, }
666 while ( my $c = $todays_checkouts->next ) {
667 my $all = $c->unblessed_all_relateds;
677 checkedout => \@checkouts, # Historical syntax
680 issues => [ map { $_->{issues}{itemnumber} } @checkouts ], # TT syntax
684 my $today = Koha::Database->new->schema->storage->datetime_parser->format_datetime( dt_from_string );
685 # Checkouts due in the future
686 my $checkouts = $pending_checkouts->search({ date_due => { '>' => $today } });
687 my @checkouts; my @overdues;
688 while ( my $c = $checkouts->next ) {
689 my $all = $c->unblessed_all_relateds;
698 # Checkouts due in the past are overdues
699 my $overdues = $pending_checkouts->search({ date_due => { '<=' => $today } });
700 while ( my $o = $overdues->next ) {
701 my $all = $o->unblessed_all_relateds;
709 my $news = GetNewsToDisplay( "slip", $branch );
711 $_->{'timestamp'} = $_->{'newdate'};
714 $letter_code = 'ISSUESLIP';
716 checkedout => \@checkouts,
717 overdue => \@overdues,
721 issues => [ map { $_->{issues}{itemnumber} } @checkouts ],
722 overdues => [ map { $_->{issues}{itemnumber} } @overdues ],
723 opac_news => [ map { $_->{opac_news}{idnew} } @news ],
727 return C4::Letters::GetPreparedLetter (
728 module => 'circulation',
729 letter_code => $letter_code,
730 branchcode => $branch,
731 lang => $patron->lang,
733 'branches' => $branch,
734 'borrowers' => $borrowernumber,
741 =head2 AddMember_Opac
746 my ( %borrower ) = @_;
748 $borrower{'categorycode'} //= C4::Context->preference('PatronSelfRegistrationDefaultCategory');
749 my $password = $borrower{password};
750 if (not defined $password){
751 my $sr = new String::Random;
752 $sr->{'A'} = [ 'A'..'Z', 'a'..'z' ];
753 $password = $sr->randpattern("AAAAAAAAAA");
754 $borrower{'password'} = $password;
757 my $patron = Koha::Patron->new(\%borrower)->store;
759 return ( $patron->borrowernumber, $password );
762 =head2 DeleteExpiredOpacRegistrations
764 Delete accounts that haven't been upgraded from the 'temporary' category
765 Returns the number of removed patrons
769 sub DeleteExpiredOpacRegistrations {
771 my $delay = C4::Context->preference('PatronSelfRegistrationExpireTemporaryAccountsDelay');
772 my $category_code = C4::Context->preference('PatronSelfRegistrationDefaultCategory');
774 return 0 if not $category_code or not defined $delay or $delay eq q||;
777 SELECT borrowernumber
779 WHERE categorycode = ? AND DATEDIFF( NOW(), dateenrolled ) > ? |;
781 my $dbh = C4::Context->dbh;
782 my $sth = $dbh->prepare($query);
783 $sth->execute( $category_code, $delay );
785 while ( my ($borrowernumber) = $sth->fetchrow_array() ) {
786 Koha::Patrons->find($borrowernumber)->delete;
792 =head2 DeleteUnverifiedOpacRegistrations
794 Delete all unverified self registrations in borrower_modifications,
795 older than the specified number of days.
799 sub DeleteUnverifiedOpacRegistrations {
801 my $dbh = C4::Context->dbh;
803 DELETE FROM borrower_modifications
804 WHERE borrowernumber = 0 AND DATEDIFF( NOW(), timestamp ) > ?|;
805 my $cnt=$dbh->do($sql, undef, ($days) );
806 return $cnt eq '0E0'? 0: $cnt;
809 END { } # module clean-up code here (global destructor)