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>.
25 use String::Random qw( random_string );
26 use Scalar::Util qw( looks_like_number );
27 use Date::Calc qw/Today check_date Date_to_Days/;
28 use List::MoreUtils qw( uniq );
30 use C4::Log; # logaction
36 use C4::NewsChannels; #get slip news
40 use Koha::AuthUtils qw(hash_password);
43 use Koha::List::Patron;
45 use Koha::Patron::Categories;
47 our (@ISA,@EXPORT,@EXPORT_OK,$debug);
50 $debug = $ENV{DEBUG} || 0;
58 &GetBorrowersToExpunge
72 C4::Members - Perl Module containing convenience functions for member handling
80 This module contains routines for adding, modifying and deleting members/patrons/borrowers
86 $flags = &patronflags($patron);
88 This function is not exported.
90 The following will be set where applicable:
91 $flags->{CHARGES}->{amount} Amount of debt
92 $flags->{CHARGES}->{noissues} Set if debt amount >$5.00 (or syspref noissuescharge)
93 $flags->{CHARGES}->{message} Message -- deprecated
95 $flags->{CREDITS}->{amount} Amount of credit
96 $flags->{CREDITS}->{message} Message -- deprecated
98 $flags->{ GNA } Patron has no valid address
99 $flags->{ GNA }->{noissues} Set for each GNA
100 $flags->{ GNA }->{message} "Borrower has no valid address" -- deprecated
102 $flags->{ LOST } Patron's card reported lost
103 $flags->{ LOST }->{noissues} Set for each LOST
104 $flags->{ LOST }->{message} Message -- deprecated
106 $flags->{DBARRED} Set if patron debarred, no access
107 $flags->{DBARRED}->{noissues} Set for each DBARRED
108 $flags->{DBARRED}->{message} Message -- deprecated
111 $flags->{ NOTES }->{message} The note itself. NOT deprecated
113 $flags->{ ODUES } Set if patron has overdue books.
114 $flags->{ ODUES }->{message} "Yes" -- deprecated
115 $flags->{ ODUES }->{itemlist} ref-to-array: list of overdue books
116 $flags->{ ODUES }->{itemlisttext} Text list of overdue items -- deprecated
118 $flags->{WAITING} Set if any of patron's reserves are available
119 $flags->{WAITING}->{message} Message -- deprecated
120 $flags->{WAITING}->{itemlist} ref-to-array: list of available items
124 =item C<$flags-E<gt>{ODUES}-E<gt>{itemlist}> is a reference-to-array listing the
125 overdue items. Its elements are references-to-hash, each describing an
126 overdue item. The keys are selected fields from the issues, biblio,
127 biblioitems, and items tables of the Koha database.
129 =item C<$flags-E<gt>{ODUES}-E<gt>{itemlisttext}> is a string giving a text listing of
130 the overdue items, one per line. Deprecated.
132 =item C<$flags-E<gt>{WAITING}-E<gt>{itemlist}> is a reference-to-array listing the
133 available items. Each element is a reference-to-hash whose keys are
134 fields from the reserves table of the Koha database.
138 All the "message" fields that include language generated in this function are deprecated,
139 because such strings belong properly in the display layer.
141 The "message" field that comes from the DB is OK.
145 # TODO: use {anonymous => hashes} instead of a dozen %flaginfo
146 # FIXME rename this function.
147 # DEPRECATED Do not use this subroutine!
150 my ( $patroninformation) = @_;
151 my $dbh=C4::Context->dbh;
152 my $patron = Koha::Patrons->find( $patroninformation->{borrowernumber} );
153 my $account = $patron->account;
154 my $owing = $account->non_issues_charges;
157 my $noissuescharge = C4::Context->preference("noissuescharge") || 5;
158 $flaginfo{'message'} = sprintf 'Patron owes %.02f', $owing;
159 $flaginfo{'amount'} = sprintf "%.02f", $owing;
160 if ( $owing > $noissuescharge && !C4::Context->preference("AllowFineOverride") ) {
161 $flaginfo{'noissues'} = 1;
163 $flags{'CHARGES'} = \%flaginfo;
165 elsif ( ( my $balance = $account->balance ) < 0 ) {
167 $flaginfo{'message'} = sprintf 'Patron has credit of %.02f', -$balance;
168 $flaginfo{'amount'} = sprintf "%.02f", $balance;
169 $flags{'CREDITS'} = \%flaginfo;
172 # Check the debt of the guarntees of this patron
173 my $no_issues_charge_guarantees = C4::Context->preference("NoIssuesChargeGuarantees");
174 $no_issues_charge_guarantees = undef unless looks_like_number( $no_issues_charge_guarantees );
175 if ( defined $no_issues_charge_guarantees ) {
176 my $p = Koha::Patrons->find( $patroninformation->{borrowernumber} );
177 my @guarantees = map { $_->guarantee } $p->guarantee_relationships;
178 my $guarantees_non_issues_charges = 0;
179 foreach my $g ( @guarantees ) {
180 $guarantees_non_issues_charges += $g->account->non_issues_charges;
183 if ( $guarantees_non_issues_charges > $no_issues_charge_guarantees ) {
185 $flaginfo{'message'} = sprintf 'patron guarantees owe %.02f', $guarantees_non_issues_charges;
186 $flaginfo{'amount'} = $guarantees_non_issues_charges;
187 $flaginfo{'noissues'} = 1 unless C4::Context->preference("allowfineoverride");
188 $flags{'CHARGES_GUARANTEES'} = \%flaginfo;
192 if ( $patroninformation->{'gonenoaddress'}
193 && $patroninformation->{'gonenoaddress'} == 1 )
196 $flaginfo{'message'} = 'Borrower has no valid address.';
197 $flaginfo{'noissues'} = 1;
198 $flags{'GNA'} = \%flaginfo;
200 if ( $patroninformation->{'lost'} && $patroninformation->{'lost'} == 1 ) {
202 $flaginfo{'message'} = 'Borrower\'s card reported lost.';
203 $flaginfo{'noissues'} = 1;
204 $flags{'LOST'} = \%flaginfo;
206 if ( $patroninformation->{'debarred'} && check_date( split( /-/, $patroninformation->{'debarred'} ) ) ) {
207 if ( Date_to_Days(Date::Calc::Today) < Date_to_Days( split( /-/, $patroninformation->{'debarred'} ) ) ) {
209 $flaginfo{'debarredcomment'} = $patroninformation->{'debarredcomment'};
210 $flaginfo{'message'} = $patroninformation->{'debarredcomment'};
211 $flaginfo{'noissues'} = 1;
212 $flaginfo{'dateend'} = $patroninformation->{'debarred'};
213 $flags{'DBARRED'} = \%flaginfo;
216 if ( $patroninformation->{'borrowernotes'}
217 && $patroninformation->{'borrowernotes'} )
220 $flaginfo{'message'} = $patroninformation->{'borrowernotes'};
221 $flags{'NOTES'} = \%flaginfo;
223 my ( $odues, $itemsoverdue ) = C4::Overdues::checkoverdues($patroninformation->{'borrowernumber'});
224 if ( $odues && $odues > 0 ) {
226 $flaginfo{'message'} = "Yes";
227 $flaginfo{'itemlist'} = $itemsoverdue;
228 foreach ( sort { $a->{'date_due'} cmp $b->{'date_due'} }
231 $flaginfo{'itemlisttext'} .=
232 "$_->{'date_due'} $_->{'barcode'} $_->{'title'} \n"; # newline is display layer
234 $flags{'ODUES'} = \%flaginfo;
237 my $waiting_holds = $patron->holds->search({ found => 'W' });
238 my $nowaiting = $waiting_holds->count;
239 if ( $nowaiting > 0 ) {
241 $flaginfo{'message'} = "Reserved items available";
242 $flaginfo{'itemlist'} = $waiting_holds->unblessed;
243 $flags{'WAITING'} = \%flaginfo;
250 $issues = &GetAllIssues($borrowernumber, $sortkey, $limit);
252 Looks up what the patron with the given borrowernumber has borrowed,
253 and sorts the results.
255 C<$sortkey> is the name of a field on which to sort the results. This
256 should be the name of a field in the C<issues>, C<biblio>,
257 C<biblioitems>, or C<items> table in the Koha database.
259 C<$limit> is the maximum number of results to return.
261 C<&GetAllIssues> an arrayref, C<$issues>, of hashrefs, the keys of which
262 are the fields from the C<issues>, C<biblio>, C<biblioitems>, and
263 C<items> tables of the Koha database.
269 my ( $borrowernumber, $order, $limit ) = @_;
271 return unless $borrowernumber;
272 $order = 'date_due desc' unless $order;
274 my $dbh = C4::Context->dbh;
276 'SELECT issues.*, items.*, biblio.*, biblioitems.*, issues.timestamp as issuestimestamp, issues.renewals AS renewals,items.renewals AS totalrenewals,items.timestamp AS itemstimestamp,borrowers.firstname,borrowers.surname
278 LEFT JOIN items on items.itemnumber=issues.itemnumber
279 LEFT JOIN borrowers on borrowers.borrowernumber=issues.issuer_id
280 LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber
281 LEFT JOIN biblioitems ON items.biblioitemnumber=biblioitems.biblioitemnumber
282 WHERE issues.borrowernumber=?
284 SELECT old_issues.*, items.*, biblio.*, biblioitems.*, old_issues.timestamp as issuestimestamp, old_issues.renewals AS renewals,items.renewals AS totalrenewals,items.timestamp AS itemstimestamp,borrowers.firstname,borrowers.surname
286 LEFT JOIN items on items.itemnumber=old_issues.itemnumber
287 LEFT JOIN borrowers on borrowers.borrowernumber=old_issues.issuer_id
288 LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber
289 LEFT JOIN biblioitems ON items.biblioitemnumber=biblioitems.biblioitemnumber
290 WHERE old_issues.borrowernumber=? AND old_issues.itemnumber IS NOT NULL
293 $query .= " limit $limit";
296 my $sth = $dbh->prepare($query);
297 $sth->execute( $borrowernumber, $borrowernumber );
298 return $sth->fetchall_arrayref( {} );
301 sub checkcardnumber {
302 my ( $cardnumber, $borrowernumber ) = @_;
304 # If cardnumber is null, we assume they're allowed.
305 return 0 unless defined $cardnumber;
307 my $dbh = C4::Context->dbh;
308 my $query = "SELECT * FROM borrowers WHERE cardnumber=?";
309 $query .= " AND borrowernumber <> ?" if ($borrowernumber);
310 my $sth = $dbh->prepare($query);
313 ( $borrowernumber ? $borrowernumber : () )
316 return 1 if $sth->fetchrow_hashref;
318 my ( $min_length, $max_length ) = get_cardnumber_length();
320 if length $cardnumber > $max_length
321 or length $cardnumber < $min_length;
326 =head2 get_cardnumber_length
328 my ($min, $max) = C4::Members::get_cardnumber_length()
330 Returns the minimum and maximum length for patron cardnumbers as
331 determined by the CardnumberLength system preference, the
332 BorrowerMandatoryField system preference, and the width of the
337 sub get_cardnumber_length {
338 my $borrower = Koha::Database->new->schema->resultset('Borrower');
339 my $field_size = $borrower->result_source->column_info('cardnumber')->{size};
340 my ( $min, $max ) = ( 0, $field_size ); # borrowers.cardnumber is a nullable varchar(20)
341 $min = 1 if C4::Context->preference('BorrowerMandatoryField') =~ /cardnumber/;
342 if ( my $cardnumber_length = C4::Context->preference('CardnumberLength') ) {
343 # Is integer and length match
344 if ( $cardnumber_length =~ m|^\d+$| ) {
345 $min = $max = $cardnumber_length
346 if $cardnumber_length >= $min
347 and $cardnumber_length <= $max;
349 # Else assuming it is a range
350 elsif ( $cardnumber_length =~ m|(\d*),(\d*)| ) {
351 $min = $1 if $1 and $min < $1;
352 $max = $2 if $2 and $max > $2;
356 $min = $max if $min > $max;
357 return ( $min, $max );
360 =head2 GetBorrowersToExpunge
362 $borrowers = &GetBorrowersToExpunge(
363 not_borrowed_since => $not_borrowed_since,
364 expired_before => $expired_before,
365 category_code => \@category_code,
366 patron_list_id => $patron_list_id,
367 branchcode => $branchcode
370 This function get all borrowers based on the given criteria.
374 sub GetBorrowersToExpunge {
377 my $filterdate = $params->{'not_borrowed_since'};
378 my $filterexpiry = $params->{'expired_before'};
379 my $filterlastseen = $params->{'last_seen'};
380 my $filtercategory = $params->{'category_code'};
381 my $filterbranch = $params->{'branchcode'} ||
382 ((C4::Context->preference('IndependentBranches')
383 && C4::Context->userenv
384 && !C4::Context->IsSuperLibrarian()
385 && C4::Context->userenv->{branch})
386 ? C4::Context->userenv->{branch}
388 my $filterpatronlist = $params->{'patron_list_id'};
390 my $dbh = C4::Context->dbh;
394 SELECT borrowers.borrowernumber,
395 MAX(old_issues.timestamp) AS latestissue,
396 MAX(issues.timestamp) AS currentissue
398 JOIN categories USING (categorycode)
401 FROM borrower_relationships
402 WHERE guarantor_id IS NOT NULL
403 AND guarantor_id <> 0
404 ) as tmp ON borrowers.borrowernumber=tmp.guarantor_id
405 LEFT JOIN old_issues USING (borrowernumber)
406 LEFT JOIN issues USING (borrowernumber)|;
407 if ( $filterpatronlist ){
408 $query .= q| LEFT JOIN patron_list_patrons USING (borrowernumber)|;
410 $query .= q| WHERE category_type <> 'S'
411 AND ( borrowers.flags IS NULL OR borrowers.flags = 0 )
412 AND tmp.guarantor_id IS NULL
415 if ( $filterbranch && $filterbranch ne "" ) {
416 $query.= " AND borrowers.branchcode = ? ";
417 push( @query_params, $filterbranch );
419 if ( $filterexpiry ) {
420 $query .= " AND dateexpiry < ? ";
421 push( @query_params, $filterexpiry );
423 if ( $filterlastseen ) {
424 $query .= ' AND lastseen < ? ';
425 push @query_params, $filterlastseen;
427 if ( $filtercategory ) {
428 if (ref($filtercategory) ne 'ARRAY' ) {
429 $filtercategory = [ $filtercategory ];
431 if ( @$filtercategory ) {
432 $query .= " AND categorycode IN (" . join(',', ('?') x @$filtercategory) . ") ";
433 push( @query_params, @$filtercategory );
436 if ( $filterpatronlist ){
437 $query.=" AND patron_list_id = ? ";
438 push( @query_params, $filterpatronlist );
440 $query .= " GROUP BY borrowers.borrowernumber";
442 ) xxx WHERE currentissue IS NULL|;
444 $query.=" AND ( latestissue < ? OR latestissue IS NULL ) ";
445 push @query_params,$filterdate;
448 if ( my $anonymous_patron = C4::Context->preference("AnonymousPatron") ) {
449 $query .= q{ AND borrowernumber != ? };
450 push( @query_params, $anonymous_patron );
453 warn $query if $debug;
455 my $sth = $dbh->prepare($query);
456 if (scalar(@query_params)>0){
457 $sth->execute(@query_params);
464 while ( my $data = $sth->fetchrow_hashref ) {
465 push @results, $data;
472 IssueSlip($branchcode, $borrowernumber, $quickslip)
474 Returns letter hash ( see C4::Letters::GetPreparedLetter )
476 $quickslip is boolean, to indicate whether we want a quick slip
478 IssueSlip populates ISSUESLIP and ISSUEQSLIP, and will make the following expansions:
514 NOTE: Fields from tables issues, items, biblio and biblioitems are available
519 my ($branch, $borrowernumber, $quickslip) = @_;
521 # FIXME Check callers before removing this statement
522 #return unless $borrowernumber;
524 my $patron = Koha::Patrons->find( $borrowernumber );
525 return unless $patron;
527 my $pending_checkouts = $patron->pending_checkouts; # Should be $patron->checkouts->pending?
529 my ($letter_code, %repeat, %loops);
531 my $today_start = dt_from_string->set( hour => 0, minute => 0, second => 0 );
532 my $today_end = dt_from_string->set( hour => 23, minute => 59, second => 0 );
533 $today_start = Koha::Database->new->schema->storage->datetime_parser->format_datetime( $today_start );
534 $today_end = Koha::Database->new->schema->storage->datetime_parser->format_datetime( $today_end );
535 $letter_code = 'ISSUEQSLIP';
537 # issue date or lastreneweddate is today
538 my $todays_checkouts = $pending_checkouts->search(
542 '>=' => $today_start,
546 { '>=' => $today_start, '<=' => $today_end, }
551 while ( my $c = $todays_checkouts->next ) {
552 my $all = $c->unblessed_all_relateds;
562 checkedout => \@checkouts, # Historical syntax
565 issues => [ map { $_->{issues}{itemnumber} } @checkouts ], # TT syntax
569 my $today = Koha::Database->new->schema->storage->datetime_parser->format_datetime( dt_from_string );
570 # Checkouts due in the future
571 my $checkouts = $pending_checkouts->search({ date_due => { '>' => $today } });
572 my @checkouts; my @overdues;
573 while ( my $c = $checkouts->next ) {
574 my $all = $c->unblessed_all_relateds;
583 # Checkouts due in the past are overdues
584 my $overdues = $pending_checkouts->search({ date_due => { '<=' => $today } });
585 while ( my $o = $overdues->next ) {
586 my $all = $o->unblessed_all_relateds;
594 my $news = GetNewsToDisplay( "slip", $branch );
596 $_->{'timestamp'} = $_->{'newdate'};
599 $letter_code = 'ISSUESLIP';
601 checkedout => \@checkouts,
602 overdue => \@overdues,
606 issues => [ map { $_->{issues}{itemnumber} } @checkouts ],
607 overdues => [ map { $_->{issues}{itemnumber} } @overdues ],
608 opac_news => [ map { $_->{opac_news}{idnew} } @news ],
612 return C4::Letters::GetPreparedLetter (
613 module => 'circulation',
614 letter_code => $letter_code,
615 branchcode => $branch,
616 lang => $patron->lang,
618 'branches' => $branch,
619 'borrowers' => $borrowernumber,
626 =head2 DeleteExpiredOpacRegistrations
628 Delete accounts that haven't been upgraded from the 'temporary' category
629 Returns the number of removed patrons
633 sub DeleteExpiredOpacRegistrations {
635 my $delay = C4::Context->preference('PatronSelfRegistrationExpireTemporaryAccountsDelay');
636 my $category_code = C4::Context->preference('PatronSelfRegistrationDefaultCategory');
638 return 0 if not $category_code or not defined $delay or $delay eq q||;
639 my $date_enrolled = dt_from_string();
640 $date_enrolled->subtract( days => $delay );
642 my $registrations_to_del = Koha::Patrons->search({
643 dateenrolled => {'<=' => $date_enrolled->ymd},
644 categorycode => $category_code,
648 while ( my $registration = $registrations_to_del->next() ) {
649 next if $registration->checkouts->count || $registration->account->balance;
650 $registration->delete;
656 =head2 DeleteUnverifiedOpacRegistrations
658 Delete all unverified self registrations in borrower_modifications,
659 older than the specified number of days.
663 sub DeleteUnverifiedOpacRegistrations {
665 my $dbh = C4::Context->dbh;
667 DELETE FROM borrower_modifications
668 WHERE borrowernumber = 0 AND DATEDIFF( NOW(), timestamp ) > ?|;
669 my $cnt=$dbh->do($sql, undef, ($days) );
670 return $cnt eq '0E0'? 0: $cnt;
673 END { } # module clean-up code here (global destructor)