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;
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 tmp
.guarantor_id IS NULL
414 if ( $filterbranch && $filterbranch ne "" ) {
415 $query.= " AND borrowers.branchcode = ? ";
416 push( @query_params, $filterbranch );
418 if ( $filterexpiry ) {
419 $query .= " AND dateexpiry < ? ";
420 push( @query_params, $filterexpiry );
422 if ( $filterlastseen ) {
423 $query .= ' AND lastseen < ? ';
424 push @query_params, $filterlastseen;
426 if ( $filtercategory ) {
427 $query .= " AND categorycode = ? ";
428 push( @query_params, $filtercategory );
430 if ( $filterpatronlist ){
431 $query.=" AND patron_list_id = ? ";
432 push( @query_params, $filterpatronlist );
434 $query .= " GROUP BY borrowers.borrowernumber";
436 ) xxx WHERE currentissue IS NULL
|;
438 $query.=" AND ( latestissue < ? OR latestissue IS NULL ) ";
439 push @query_params,$filterdate;
442 if ( my $anonymous_patron = C4
::Context
->preference("AnonymousPatron") ) {
443 $query .= q{ AND borrowernumber != ? };
444 push( @query_params, $anonymous_patron );
447 warn $query if $debug;
449 my $sth = $dbh->prepare($query);
450 if (scalar(@query_params)>0){
451 $sth->execute(@query_params);
458 while ( my $data = $sth->fetchrow_hashref ) {
459 push @results, $data;
466 IssueSlip($branchcode, $borrowernumber, $quickslip)
468 Returns letter hash ( see C4::Letters::GetPreparedLetter )
470 $quickslip is boolean, to indicate whether we want a quick slip
472 IssueSlip populates ISSUESLIP and ISSUEQSLIP, and will make the following expansions:
508 NOTE: Fields from tables issues, items, biblio and biblioitems are available
513 my ($branch, $borrowernumber, $quickslip) = @_;
515 # FIXME Check callers before removing this statement
516 #return unless $borrowernumber;
518 my $patron = Koha
::Patrons
->find( $borrowernumber );
519 return unless $patron;
521 my $pending_checkouts = $patron->pending_checkouts; # Should be $patron->checkouts->pending?
523 my ($letter_code, %repeat, %loops);
525 my $today_start = dt_from_string
->set( hour
=> 0, minute
=> 0, second
=> 0 );
526 my $today_end = dt_from_string
->set( hour
=> 23, minute
=> 59, second
=> 0 );
527 $today_start = Koha
::Database
->new->schema->storage->datetime_parser->format_datetime( $today_start );
528 $today_end = Koha
::Database
->new->schema->storage->datetime_parser->format_datetime( $today_end );
529 $letter_code = 'ISSUEQSLIP';
531 # issue date or lastreneweddate is today
532 my $todays_checkouts = $pending_checkouts->search(
536 '>=' => $today_start,
540 { '>=' => $today_start, '<=' => $today_end, }
545 while ( my $c = $todays_checkouts->next ) {
546 my $all = $c->unblessed_all_relateds;
556 checkedout
=> \
@checkouts, # Historical syntax
559 issues
=> [ map { $_->{issues
}{itemnumber
} } @checkouts ], # TT syntax
563 my $today = Koha
::Database
->new->schema->storage->datetime_parser->format_datetime( dt_from_string
);
564 # Checkouts due in the future
565 my $checkouts = $pending_checkouts->search({ date_due
=> { '>' => $today } });
566 my @checkouts; my @overdues;
567 while ( my $c = $checkouts->next ) {
568 my $all = $c->unblessed_all_relateds;
577 # Checkouts due in the past are overdues
578 my $overdues = $pending_checkouts->search({ date_due
=> { '<=' => $today } });
579 while ( my $o = $overdues->next ) {
580 my $all = $o->unblessed_all_relateds;
588 my $news = GetNewsToDisplay
( "slip", $branch );
590 $_->{'timestamp'} = $_->{'newdate'};
593 $letter_code = 'ISSUESLIP';
595 checkedout
=> \
@checkouts,
596 overdue
=> \
@overdues,
600 issues
=> [ map { $_->{issues
}{itemnumber
} } @checkouts ],
601 overdues
=> [ map { $_->{issues
}{itemnumber
} } @overdues ],
602 opac_news
=> [ map { $_->{opac_news
}{idnew
} } @news ],
606 return C4
::Letters
::GetPreparedLetter
(
607 module
=> 'circulation',
608 letter_code
=> $letter_code,
609 branchcode
=> $branch,
610 lang
=> $patron->lang,
612 'branches' => $branch,
613 'borrowers' => $borrowernumber,
620 =head2 DeleteExpiredOpacRegistrations
622 Delete accounts that haven't been upgraded from the 'temporary' category
623 Returns the number of removed patrons
627 sub DeleteExpiredOpacRegistrations
{
629 my $delay = C4
::Context
->preference('PatronSelfRegistrationExpireTemporaryAccountsDelay');
630 my $category_code = C4
::Context
->preference('PatronSelfRegistrationDefaultCategory');
632 return 0 if not $category_code or not defined $delay or $delay eq q
||;
633 my $date_enrolled = dt_from_string
();
634 $date_enrolled->subtract( days
=> $delay );
636 my $registrations_to_del = Koha
::Patrons
->search({
637 dateenrolled
=> {'<=' => $date_enrolled->ymd},
638 categorycode
=> $category_code,
642 while ( my $registration = $registrations_to_del->next() ) {
643 next if $registration->checkouts->count || $registration->account->balance;
644 $registration->delete;
650 =head2 DeleteUnverifiedOpacRegistrations
652 Delete all unverified self registrations in borrower_modifications,
653 older than the specified number of days.
657 sub DeleteUnverifiedOpacRegistrations
{
659 my $dbh = C4
::Context
->dbh;
661 DELETE FROM borrower_modifications
662 WHERE borrowernumber
= 0 AND DATEDIFF
( NOW
(), timestamp
) > ?
|;
663 my $cnt=$dbh->do($sql, undef, ($days) );
664 return $cnt eq '0E0'?
0: $cnt;
667 END { } # module clean-up code here (global destructor)