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;
69 &GetBorrowersToExpunge
101 C4::Members - Perl Module containing convenience functions for member handling
109 This module contains routines for adding, modifying and deleting members/patrons/borrowers
115 $flags = &patronflags($patron);
117 This function is not exported.
119 The following will be set where applicable:
120 $flags->{CHARGES}->{amount} Amount of debt
121 $flags->{CHARGES}->{noissues} Set if debt amount >$5.00 (or syspref noissuescharge)
122 $flags->{CHARGES}->{message} Message -- deprecated
124 $flags->{CREDITS}->{amount} Amount of credit
125 $flags->{CREDITS}->{message} Message -- deprecated
127 $flags->{ GNA } Patron has no valid address
128 $flags->{ GNA }->{noissues} Set for each GNA
129 $flags->{ GNA }->{message} "Borrower has no valid address" -- deprecated
131 $flags->{ LOST } Patron's card reported lost
132 $flags->{ LOST }->{noissues} Set for each LOST
133 $flags->{ LOST }->{message} Message -- deprecated
135 $flags->{DBARRED} Set if patron debarred, no access
136 $flags->{DBARRED}->{noissues} Set for each DBARRED
137 $flags->{DBARRED}->{message} Message -- deprecated
140 $flags->{ NOTES }->{message} The note itself. NOT deprecated
142 $flags->{ ODUES } Set if patron has overdue books.
143 $flags->{ ODUES }->{message} "Yes" -- deprecated
144 $flags->{ ODUES }->{itemlist} ref-to-array: list of overdue books
145 $flags->{ ODUES }->{itemlisttext} Text list of overdue items -- deprecated
147 $flags->{WAITING} Set if any of patron's reserves are available
148 $flags->{WAITING}->{message} Message -- deprecated
149 $flags->{WAITING}->{itemlist} ref-to-array: list of available items
153 =item C<$flags-E<gt>{ODUES}-E<gt>{itemlist}> is a reference-to-array listing the
154 overdue items. Its elements are references-to-hash, each describing an
155 overdue item. The keys are selected fields from the issues, biblio,
156 biblioitems, and items tables of the Koha database.
158 =item C<$flags-E<gt>{ODUES}-E<gt>{itemlisttext}> is a string giving a text listing of
159 the overdue items, one per line. Deprecated.
161 =item C<$flags-E<gt>{WAITING}-E<gt>{itemlist}> is a reference-to-array listing the
162 available items. Each element is a reference-to-hash whose keys are
163 fields from the reserves table of the Koha database.
167 All the "message" fields that include language generated in this function are deprecated,
168 because such strings belong properly in the display layer.
170 The "message" field that comes from the DB is OK.
174 # TODO: use {anonymous => hashes} instead of a dozen %flaginfo
175 # FIXME rename this function.
178 my ( $patroninformation) = @_;
179 my $dbh=C4::Context->dbh;
180 my $patron = Koha::Patrons->find( $patroninformation->{borrowernumber} );
181 my $account = $patron->account;
182 my $owing = $account->non_issues_charges;
185 my $noissuescharge = C4::Context->preference("noissuescharge") || 5;
186 $flaginfo{'message'} = sprintf 'Patron owes %.02f', $owing;
187 $flaginfo{'amount'} = sprintf "%.02f", $owing;
188 if ( $owing > $noissuescharge && !C4::Context->preference("AllowFineOverride") ) {
189 $flaginfo{'noissues'} = 1;
191 $flags{'CHARGES'} = \%flaginfo;
193 elsif ( ( my $balance = $account->balance ) < 0 ) {
195 $flaginfo{'message'} = sprintf 'Patron has credit of %.02f', -$balance;
196 $flaginfo{'amount'} = sprintf "%.02f", $balance;
197 $flags{'CREDITS'} = \%flaginfo;
200 # Check the debt of the guarntees of this patron
201 my $no_issues_charge_guarantees = C4::Context->preference("NoIssuesChargeGuarantees");
202 $no_issues_charge_guarantees = undef unless looks_like_number( $no_issues_charge_guarantees );
203 if ( defined $no_issues_charge_guarantees ) {
204 my $p = Koha::Patrons->find( $patroninformation->{borrowernumber} );
205 my @guarantees = $p->guarantees();
206 my $guarantees_non_issues_charges;
207 foreach my $g ( @guarantees ) {
208 $guarantees_non_issues_charges += $g->account->non_issues_charges;
211 if ( $guarantees_non_issues_charges > $no_issues_charge_guarantees ) {
213 $flaginfo{'message'} = sprintf 'patron guarantees owe %.02f', $guarantees_non_issues_charges;
214 $flaginfo{'amount'} = $guarantees_non_issues_charges;
215 $flaginfo{'noissues'} = 1 unless C4::Context->preference("allowfineoverride");
216 $flags{'CHARGES_GUARANTEES'} = \%flaginfo;
220 if ( $patroninformation->{'gonenoaddress'}
221 && $patroninformation->{'gonenoaddress'} == 1 )
224 $flaginfo{'message'} = 'Borrower has no valid address.';
225 $flaginfo{'noissues'} = 1;
226 $flags{'GNA'} = \%flaginfo;
228 if ( $patroninformation->{'lost'} && $patroninformation->{'lost'} == 1 ) {
230 $flaginfo{'message'} = 'Borrower\'s card reported lost.';
231 $flaginfo{'noissues'} = 1;
232 $flags{'LOST'} = \%flaginfo;
234 if ( $patroninformation->{'debarred'} && check_date( split( /-/, $patroninformation->{'debarred'} ) ) ) {
235 if ( Date_to_Days(Date::Calc::Today) < Date_to_Days( split( /-/, $patroninformation->{'debarred'} ) ) ) {
237 $flaginfo{'debarredcomment'} = $patroninformation->{'debarredcomment'};
238 $flaginfo{'message'} = $patroninformation->{'debarredcomment'};
239 $flaginfo{'noissues'} = 1;
240 $flaginfo{'dateend'} = $patroninformation->{'debarred'};
241 $flags{'DBARRED'} = \%flaginfo;
244 if ( $patroninformation->{'borrowernotes'}
245 && $patroninformation->{'borrowernotes'} )
248 $flaginfo{'message'} = $patroninformation->{'borrowernotes'};
249 $flags{'NOTES'} = \%flaginfo;
251 my ( $odues, $itemsoverdue ) = C4::Overdues::checkoverdues($patroninformation->{'borrowernumber'});
252 if ( $odues && $odues > 0 ) {
254 $flaginfo{'message'} = "Yes";
255 $flaginfo{'itemlist'} = $itemsoverdue;
256 foreach ( sort { $a->{'date_due'} cmp $b->{'date_due'} }
259 $flaginfo{'itemlisttext'} .=
260 "$_->{'date_due'} $_->{'barcode'} $_->{'title'} \n"; # newline is display layer
262 $flags{'ODUES'} = \%flaginfo;
265 my $waiting_holds = $patron->holds->search({ found => 'W' });
266 my $nowaiting = $waiting_holds->count;
267 if ( $nowaiting > 0 ) {
269 $flaginfo{'message'} = "Reserved items available";
270 $flaginfo{'itemlist'} = $waiting_holds->unblessed;
271 $flags{'WAITING'} = \%flaginfo;
279 my $success = ModMember(borrowernumber => $borrowernumber,
280 [ field => value ]... );
282 Modify borrower's data. All date fields should ALREADY be in ISO format.
285 true on success, or false on failure
292 # trim whitespace from data which has some non-whitespace in it.
293 foreach my $field_name (keys(%data)) {
294 if ( defined $data{$field_name} && $data{$field_name} =~ /\S/ ) {
295 $data{$field_name} =~ s/^\s*|\s*$//g;
299 # test to know if you must update or not the borrower password
300 if (exists $data{password}) {
301 if ($data{password} eq '****' or $data{password} eq '') {
302 delete $data{password};
304 if ( C4::Context->preference('NorwegianPatronDBEnable') && C4::Context->preference('NorwegianPatronDBEnable') == 1 ) {
305 # Update the hashed PIN in borrower_sync.hashed_pin, before Koha hashes it
306 Koha::NorwegianPatronDB::NLUpdateHashedPIN( $data{'borrowernumber'}, $data{password} );
308 $data{password} = hash_password($data{password});
312 my $old_categorycode = Koha::Patrons->find( $data{borrowernumber} )->categorycode;
314 # get only the columns of a borrower
315 my $schema = Koha::Database->new()->schema;
316 my @columns = $schema->source('Borrower')->columns;
317 my $new_borrower = { map { join(' ', @columns) =~ /$_/ ? ( $_ => $data{$_} ) : () } keys(%data) };
319 $new_borrower->{dateofbirth} ||= undef if exists $new_borrower->{dateofbirth};
320 $new_borrower->{dateenrolled} ||= undef if exists $new_borrower->{dateenrolled};
321 $new_borrower->{dateexpiry} ||= undef if exists $new_borrower->{dateexpiry};
322 $new_borrower->{debarred} ||= undef if exists $new_borrower->{debarred};
323 $new_borrower->{sms_provider_id} ||= undef if exists $new_borrower->{sms_provider_id};
324 $new_borrower->{guarantorid} ||= undef if exists $new_borrower->{guarantorid};
326 my $patron = Koha::Patrons->find( $new_borrower->{borrowernumber} );
328 my $borrowers_log = C4::Context->preference("BorrowersLog");
329 if ( $borrowers_log && $patron->cardnumber ne $new_borrower->{cardnumber} )
334 $data{'borrowernumber'},
337 cardnumber_replaced => {
338 previous_cardnumber => $patron->cardnumber,
339 new_cardnumber => $new_borrower->{cardnumber},
342 { utf8 => 1, pretty => 1 }
347 delete $new_borrower->{userid} if exists $new_borrower->{userid} and not $new_borrower->{userid};
349 my $execute_success = $patron->store if $patron->set($new_borrower);
351 if ($execute_success) { # only proceed if the update was a success
352 # If the patron changes to a category with enrollment fee, we add a fee
353 if ( $data{categorycode} and $data{categorycode} ne $old_categorycode ) {
354 if ( C4::Context->preference('FeeOnChangePatronCategory') ) {
355 $patron->add_enrolment_fee_if_needed;
359 # If NorwegianPatronDBEnable is enabled, we set syncstatus to something that a
360 # cronjob will use for syncing with NL
361 if ( C4::Context->preference('NorwegianPatronDBEnable') && C4::Context->preference('NorwegianPatronDBEnable') == 1 ) {
362 my $borrowersync = Koha::Database->new->schema->resultset('BorrowerSync')->find({
363 'synctype' => 'norwegianpatrondb',
364 'borrowernumber' => $data{'borrowernumber'}
366 # Do not set to "edited" if syncstatus is "new". We need to sync as new before
367 # we can sync as changed. And the "new sync" will pick up all changes since
368 # the patron was created anyway.
369 if ( $borrowersync->syncstatus ne 'new' && $borrowersync->syncstatus ne 'delete' ) {
370 $borrowersync->update( { 'syncstatus' => 'edited' } );
372 # Set the value of 'sync'
373 $borrowersync->update( { 'sync' => $data{'sync'} } );
374 # Try to do the live sync
375 Koha::NorwegianPatronDB::NLSync({ 'borrowernumber' => $data{'borrowernumber'} });
378 logaction("MEMBERS", "MODIFY", $data{'borrowernumber'}, "UPDATE (executed w/ arg: $data{'borrowernumber'})") if $borrowers_log;
380 return $execute_success;
385 $borrowernumber = &AddMember(%borrower);
387 insert new borrower into table
389 (%borrower keys are database columns. Database columns could be
390 different in different versions. Please look into database for correct
393 Returns the borrowernumber upon success
395 Returns as undef upon any db error without further processing
402 my $dbh = C4::Context->dbh;
403 my $schema = Koha::Database->new()->schema;
405 my $category = Koha::Patron::Categories->find( $data{categorycode} );
407 Koha::Exceptions::BadParameter->throw(
408 error => 'Invalid parameter passed',
409 parameter => 'categorycode'
413 # trim whitespace from data which has some non-whitespace in it.
414 foreach my $field_name (keys(%data)) {
415 if ( defined $data{$field_name} && $data{$field_name} =~ /\S/ ) {
416 $data{$field_name} =~ s/^\s*|\s*$//g;
420 # generate a proper login if none provided
421 $data{'userid'} = Generate_Userid( $data{'borrowernumber'}, $data{'firstname'}, $data{'surname'} )
422 if ( $data{'userid'} eq '' || !Check_Userid( $data{'userid'} ) );
424 # add expiration date if it isn't already there
425 $data{dateexpiry} ||= $category->get_expiry_date;
427 # add enrollment date if it isn't already there
428 unless ( $data{'dateenrolled'} ) {
429 $data{'dateenrolled'} = output_pref( { dt => dt_from_string, dateonly => 1, dateformat => 'iso' } );
432 if ( C4::Context->preference("autoMemberNum") ) {
433 if ( not exists $data{cardnumber} or not defined $data{cardnumber} or $data{cardnumber} eq '' ) {
434 $data{cardnumber} = fixup_cardnumber( $data{cardnumber} );
439 $category->default_privacy() eq 'default' ? 1
440 : $category->default_privacy() eq 'never' ? 2
441 : $category->default_privacy() eq 'forever' ? 0
444 $data{'privacy_guarantor_checkouts'} = 0 unless defined( $data{'privacy_guarantor_checkouts'} );
446 # Make a copy of the plain text password for later use
447 my $plain_text_password = $data{'password'};
449 # create a disabled account if no password provided
450 $data{'password'} = ($data{'password'})? hash_password($data{'password'}) : '!';
452 # we don't want invalid dates in the db (mysql has a bad habit of inserting 0000-00-00
453 $data{'dateofbirth'} = undef if ( not $data{'dateofbirth'} );
454 $data{'debarred'} = undef if ( not $data{'debarred'} );
455 $data{'sms_provider_id'} = undef if ( not $data{'sms_provider_id'} );
456 $data{'guarantorid'} = undef if ( not $data{'guarantorid'} );
458 # get only the columns of Borrower
459 # FIXME Do we really need this check?
460 my @columns = $schema->source('Borrower')->columns;
461 my $new_member = { map { join(' ',@columns) =~ /$_/ ? ( $_ => $data{$_} ) : () } keys(%data) } ;
463 delete $new_member->{borrowernumber};
465 my $patron = Koha::Patron->new( $new_member )->store;
466 $data{borrowernumber} = $patron->borrowernumber;
468 # If NorwegianPatronDBEnable is enabled, we set syncstatus to something that a
469 # cronjob will use for syncing with NL
470 if ( exists $data{'borrowernumber'} && C4::Context->preference('NorwegianPatronDBEnable') && C4::Context->preference('NorwegianPatronDBEnable') == 1 ) {
471 Koha::Database->new->schema->resultset('BorrowerSync')->create({
472 'borrowernumber' => $data{'borrowernumber'},
473 'synctype' => 'norwegianpatrondb',
475 'syncstatus' => 'new',
476 'hashed_pin' => Koha::NorwegianPatronDB::NLEncryptPIN( $plain_text_password ),
480 logaction("MEMBERS", "CREATE", $data{'borrowernumber'}, "") if C4::Context->preference("BorrowersLog");
482 $patron->add_enrolment_fee_if_needed;
484 return $data{borrowernumber};
489 my $uniqueness = Check_Userid($userid,$borrowernumber);
491 $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 != '').
493 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.
496 0 for not unique (i.e. this $userid already exists)
497 1 for unique (i.e. this $userid does not exist, or this $userid/$borrowernumber combination already exists)
502 my ( $uid, $borrowernumber ) = @_;
504 return 0 unless ($uid); # userid is a unique column, we should assume NULL is not unique
506 return 0 if ( $uid eq C4::Context->config('user') );
508 my $rs = Koha::Database->new()->schema()->resultset('Borrower');
511 $params->{userid} = $uid;
512 $params->{borrowernumber} = { '!=' => $borrowernumber } if ($borrowernumber);
514 my $count = $rs->count( $params );
516 return $count ? 0 : 1;
519 =head2 Generate_Userid
521 my $newuid = Generate_Userid($borrowernumber, $firstname, $surname);
523 Generate a userid using the $surname and the $firstname (if there is a value in $firstname).
525 $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.
528 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).
532 sub Generate_Userid {
533 my ($borrowernumber, $firstname, $surname) = @_;
536 #The script will "do" the following code and increment the $offset until Check_Userid = 1 (i.e. until $newuid comes back as unique)
538 $firstname =~ s/[[:digit:][:space:][:blank:][:punct:][:cntrl:]]//g;
539 $surname =~ s/[[:digit:][:space:][:blank:][:punct:][:cntrl:]]//g;
540 $newuid = lc(($firstname)? "$firstname.$surname" : $surname);
541 $newuid = unac_string('utf-8',$newuid);
542 $newuid .= $offset unless $offset == 0;
545 } while (!Check_Userid($newuid,$borrowernumber));
550 =head2 fixup_cardnumber
552 Warning: The caller is responsible for locking the members table in write
553 mode, to avoid database corruption.
557 sub fixup_cardnumber {
558 my ($cardnumber) = @_;
559 my $autonumber_members = C4::Context->boolean_preference('autoMemberNum') || 0;
561 # Find out whether member numbers should be generated
562 # automatically. Should be either "1" or something else.
563 # Defaults to "0", which is interpreted as "no".
565 ($autonumber_members) or return $cardnumber;
566 my $dbh = C4::Context->dbh;
568 my $sth = $dbh->prepare(
569 'SELECT MAX( CAST( cardnumber AS SIGNED ) ) FROM borrowers WHERE cardnumber REGEXP "^-?[0-9]+$"'
572 my ($result) = $sth->fetchrow;
576 =head2 GetPendingIssues
578 my $issues = &GetPendingIssues(@borrowernumber);
580 Looks up what the patron with the given borrowernumber has borrowed.
582 C<&GetPendingIssues> returns a
583 reference-to-array where each element is a reference-to-hash; the
584 keys are the fields from the C<issues>, C<biblio>, and C<items> tables.
585 The keys include C<biblioitems> fields.
589 sub GetPendingIssues {
590 my @borrowernumbers = @_;
592 unless (@borrowernumbers ) { # return a ref_to_array
593 return \@borrowernumbers; # to not cause surprise to caller
596 # Borrowers part of the query
598 for (my $i = 0; $i < @borrowernumbers; $i++) {
599 $bquery .= ' issues.borrowernumber = ?';
600 if ($i < $#borrowernumbers ) {
605 # FIXME: namespace collision: each table has "timestamp" fields. Which one is "timestamp" ?
606 # FIXME: circ/ciculation.pl tries to sort by timestamp!
607 # FIXME: namespace collision: other collisions possible.
608 # FIXME: most of this data isn't really being used by callers.
615 biblioitems.itemtype,
618 biblioitems.publicationyear,
619 biblioitems.publishercode,
620 biblioitems.volumedate,
621 biblioitems.volumedesc,
626 borrowers.cardnumber,
627 issues.timestamp AS timestamp,
628 issues.renewals AS renewals,
629 issues.borrowernumber AS borrowernumber,
630 items.renewals AS totalrenewals
632 LEFT JOIN items ON items.itemnumber = issues.itemnumber
633 LEFT JOIN biblio ON items.biblionumber = biblio.biblionumber
634 LEFT JOIN biblioitems ON items.biblioitemnumber = biblioitems.biblioitemnumber
635 LEFT JOIN borrowers ON issues.borrowernumber = borrowers.borrowernumber
638 ORDER BY issues.issuedate"
641 my $sth = C4::Context->dbh->prepare($query);
642 $sth->execute(@borrowernumbers);
643 my $data = $sth->fetchall_arrayref({});
644 my $today = dt_from_string;
646 if ($_->{issuedate}) {
647 $_->{issuedate} = dt_from_string($_->{issuedate}, 'sql');
649 $_->{date_due_sql} = $_->{date_due};
650 # FIXME no need to have this value
651 $_->{date_due} or next;
652 $_->{date_due_sql} = $_->{date_due};
653 # FIXME no need to have this value
654 $_->{date_due} = dt_from_string($_->{date_due}, 'sql');
655 if ( DateTime->compare($_->{date_due}, $today) == -1 ) {
664 $issues = &GetAllIssues($borrowernumber, $sortkey, $limit);
666 Looks up what the patron with the given borrowernumber has borrowed,
667 and sorts the results.
669 C<$sortkey> is the name of a field on which to sort the results. This
670 should be the name of a field in the C<issues>, C<biblio>,
671 C<biblioitems>, or C<items> table in the Koha database.
673 C<$limit> is the maximum number of results to return.
675 C<&GetAllIssues> an arrayref, C<$issues>, of hashrefs, the keys of which
676 are the fields from the C<issues>, C<biblio>, C<biblioitems>, and
677 C<items> tables of the Koha database.
683 my ( $borrowernumber, $order, $limit ) = @_;
685 return unless $borrowernumber;
686 $order = 'date_due desc' unless $order;
688 my $dbh = C4::Context->dbh;
690 'SELECT *, issues.timestamp as issuestimestamp, issues.renewals AS renewals,items.renewals AS totalrenewals,items.timestamp AS itemstimestamp
692 LEFT JOIN items on items.itemnumber=issues.itemnumber
693 LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber
694 LEFT JOIN biblioitems ON items.biblioitemnumber=biblioitems.biblioitemnumber
695 WHERE borrowernumber=?
697 SELECT *, old_issues.timestamp as issuestimestamp, old_issues.renewals AS renewals,items.renewals AS totalrenewals,items.timestamp AS itemstimestamp
699 LEFT JOIN items on items.itemnumber=old_issues.itemnumber
700 LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber
701 LEFT JOIN biblioitems ON items.biblioitemnumber=biblioitems.biblioitemnumber
702 WHERE borrowernumber=? AND old_issues.itemnumber IS NOT NULL
705 $query .= " limit $limit";
708 my $sth = $dbh->prepare($query);
709 $sth->execute( $borrowernumber, $borrowernumber );
710 return $sth->fetchall_arrayref( {} );
713 sub checkcardnumber {
714 my ( $cardnumber, $borrowernumber ) = @_;
716 # If cardnumber is null, we assume they're allowed.
717 return 0 unless defined $cardnumber;
719 my $dbh = C4::Context->dbh;
720 my $query = "SELECT * FROM borrowers WHERE cardnumber=?";
721 $query .= " AND borrowernumber <> ?" if ($borrowernumber);
722 my $sth = $dbh->prepare($query);
725 ( $borrowernumber ? $borrowernumber : () )
728 return 1 if $sth->fetchrow_hashref;
730 my ( $min_length, $max_length ) = get_cardnumber_length();
732 if length $cardnumber > $max_length
733 or length $cardnumber < $min_length;
738 =head2 get_cardnumber_length
740 my ($min, $max) = C4::Members::get_cardnumber_length()
742 Returns the minimum and maximum length for patron cardnumbers as
743 determined by the CardnumberLength system preference, the
744 BorrowerMandatoryField system preference, and the width of the
749 sub get_cardnumber_length {
750 my $borrower = Koha::Schema->resultset('Borrower');
751 my $field_size = $borrower->result_source->column_info('cardnumber')->{size};
752 my ( $min, $max ) = ( 0, $field_size ); # borrowers.cardnumber is a nullable varchar(20)
753 $min = 1 if C4::Context->preference('BorrowerMandatoryField') =~ /cardnumber/;
754 if ( my $cardnumber_length = C4::Context->preference('CardnumberLength') ) {
755 # Is integer and length match
756 if ( $cardnumber_length =~ m|^\d+$| ) {
757 $min = $max = $cardnumber_length
758 if $cardnumber_length >= $min
759 and $cardnumber_length <= $max;
761 # Else assuming it is a range
762 elsif ( $cardnumber_length =~ m|(\d*),(\d*)| ) {
763 $min = $1 if $1 and $min < $1;
764 $max = $2 if $2 and $max > $2;
768 $min = $max if $min > $max;
769 return ( $min, $max );
772 =head2 GetBorrowersToExpunge
774 $borrowers = &GetBorrowersToExpunge(
775 not_borrowed_since => $not_borrowed_since,
776 expired_before => $expired_before,
777 category_code => $category_code,
778 patron_list_id => $patron_list_id,
779 branchcode => $branchcode
782 This function get all borrowers based on the given criteria.
786 sub GetBorrowersToExpunge {
789 my $filterdate = $params->{'not_borrowed_since'};
790 my $filterexpiry = $params->{'expired_before'};
791 my $filterlastseen = $params->{'last_seen'};
792 my $filtercategory = $params->{'category_code'};
793 my $filterbranch = $params->{'branchcode'} ||
794 ((C4::Context->preference('IndependentBranches')
795 && C4::Context->userenv
796 && !C4::Context->IsSuperLibrarian()
797 && C4::Context->userenv->{branch})
798 ? C4::Context->userenv->{branch}
800 my $filterpatronlist = $params->{'patron_list_id'};
802 my $dbh = C4::Context->dbh;
806 SELECT borrowers.borrowernumber,
807 MAX(old_issues.timestamp) AS latestissue,
808 MAX(issues.timestamp) AS currentissue
810 JOIN categories USING (categorycode)
814 WHERE guarantorid IS NOT NULL
816 ) as tmp ON borrowers.borrowernumber=tmp.guarantorid
817 LEFT JOIN old_issues USING (borrowernumber)
818 LEFT JOIN issues USING (borrowernumber)|;
819 if ( $filterpatronlist ){
820 $query .= q| LEFT JOIN patron_list_patrons USING (borrowernumber)|;
822 $query .= q| WHERE category_type <> 'S'
823 AND tmp.guarantorid IS NULL
826 if ( $filterbranch && $filterbranch ne "" ) {
827 $query.= " AND borrowers.branchcode = ? ";
828 push( @query_params, $filterbranch );
830 if ( $filterexpiry ) {
831 $query .= " AND dateexpiry < ? ";
832 push( @query_params, $filterexpiry );
834 if ( $filterlastseen ) {
835 $query .= ' AND lastseen < ? ';
836 push @query_params, $filterlastseen;
838 if ( $filtercategory ) {
839 $query .= " AND categorycode = ? ";
840 push( @query_params, $filtercategory );
842 if ( $filterpatronlist ){
843 $query.=" AND patron_list_id = ? ";
844 push( @query_params, $filterpatronlist );
846 $query .= " GROUP BY borrowers.borrowernumber";
848 ) xxx WHERE currentissue IS NULL|;
850 $query.=" AND ( latestissue < ? OR latestissue IS NULL ) ";
851 push @query_params,$filterdate;
854 warn $query if $debug;
856 my $sth = $dbh->prepare($query);
857 if (scalar(@query_params)>0){
858 $sth->execute(@query_params);
865 while ( my $data = $sth->fetchrow_hashref ) {
866 push @results, $data;
873 IssueSlip($branchcode, $borrowernumber, $quickslip)
875 Returns letter hash ( see C4::Letters::GetPreparedLetter )
877 $quickslip is boolean, to indicate whether we want a quick slip
879 IssueSlip populates ISSUESLIP and ISSUEQSLIP, and will make the following expansions:
915 NOTE: Not all table fields are available, pleasee see GetPendingIssues for a list of available fields.
920 my ($branch, $borrowernumber, $quickslip) = @_;
922 # FIXME Check callers before removing this statement
923 #return unless $borrowernumber;
925 my $patron = Koha::Patrons->find( $borrowernumber );
926 return unless $patron;
928 my @issues = @{ GetPendingIssues($borrowernumber) };
930 for my $issue (@issues) {
931 $issue->{date_due} = $issue->{date_due_sql};
933 my $today = output_pref({ dt => dt_from_string, dateformat => 'iso', dateonly => 1 });
934 if ( substr( $issue->{issuedate}, 0, 10 ) eq $today
935 or substr( $issue->{lastreneweddate}, 0, 10 ) eq $today ) {
941 # Sort on timestamp then on issuedate then on issue_id
942 # useful for tests and could be if modified in a batch
944 $b->{timestamp} <=> $a->{timestamp}
945 or $b->{issuedate} <=> $a->{issuedate}
946 or $b->{issue_id} <=> $a->{issue_id}
949 my ($letter_code, %repeat, %loops);
951 $letter_code = 'ISSUEQSLIP';
952 my @checkouts = map {
957 }, grep { $_->{'now'} } @issues;
959 checkedout => \@checkouts, # History syntax
962 issues => [ map { $_->{issues}{itemnumber} } @checkouts ], # TT syntax
966 my @checkouts = map {
971 }, grep { !$_->{'overdue'} } @issues;
977 }, grep { $_->{'overdue'} } @issues;
978 my $news = GetNewsToDisplay( "slip", $branch );
980 $_->{'timestamp'} = $_->{'newdate'};
983 $letter_code = 'ISSUESLIP';
985 checkedout => \@checkouts,
986 overdue => \@overdues,
990 issues => [ map { $_->{issues}{itemnumber} } @checkouts ],
991 overdues => [ map { $_->{issues}{itemnumber} } @overdues ],
992 opac_news => [ map { $_->{opac_news}{idnew} } @news ],
996 return C4::Letters::GetPreparedLetter (
997 module => 'circulation',
998 letter_code => $letter_code,
999 branchcode => $branch,
1000 lang => $patron->lang,
1002 'branches' => $branch,
1003 'borrowers' => $borrowernumber,
1010 =head2 AddMember_Auto
1014 sub AddMember_Auto {
1015 my ( %borrower ) = @_;
1017 $borrower{'cardnumber'} ||= fixup_cardnumber();
1019 $borrower{'borrowernumber'} = AddMember(%borrower);
1021 return ( %borrower );
1024 =head2 AddMember_Opac
1028 sub AddMember_Opac {
1029 my ( %borrower ) = @_;
1031 $borrower{'categorycode'} //= C4::Context->preference('PatronSelfRegistrationDefaultCategory');
1032 if (not defined $borrower{'password'}){
1033 my $sr = new String::Random;
1034 $sr->{'A'} = [ 'A'..'Z', 'a'..'z' ];
1035 my $password = $sr->randpattern("AAAAAAAAAA");
1036 $borrower{'password'} = $password;
1039 %borrower = AddMember_Auto(%borrower);
1041 return ( $borrower{'borrowernumber'}, $borrower{'password'} );
1044 =head2 DeleteExpiredOpacRegistrations
1046 Delete accounts that haven't been upgraded from the 'temporary' category
1047 Returns the number of removed patrons
1051 sub DeleteExpiredOpacRegistrations {
1053 my $delay = C4::Context->preference('PatronSelfRegistrationExpireTemporaryAccountsDelay');
1054 my $category_code = C4::Context->preference('PatronSelfRegistrationDefaultCategory');
1056 return 0 if not $category_code or not defined $delay or $delay eq q||;
1059 SELECT borrowernumber
1061 WHERE categorycode = ? AND DATEDIFF( NOW(), dateenrolled ) > ? |;
1063 my $dbh = C4::Context->dbh;
1064 my $sth = $dbh->prepare($query);
1065 $sth->execute( $category_code, $delay );
1067 while ( my ($borrowernumber) = $sth->fetchrow_array() ) {
1068 Koha::Patrons->find($borrowernumber)->delete;
1074 =head2 DeleteUnverifiedOpacRegistrations
1076 Delete all unverified self registrations in borrower_modifications,
1077 older than the specified number of days.
1081 sub DeleteUnverifiedOpacRegistrations {
1083 my $dbh = C4::Context->dbh;
1085 DELETE FROM borrower_modifications
1086 WHERE borrowernumber = 0 AND DATEDIFF( NOW(), timestamp ) > ?|;
1087 my $cnt=$dbh->do($sql, undef, ($days) );
1088 return $cnt eq '0E0'? 0: $cnt;
1091 sub GetOverduesForPatron {
1092 my ( $borrowernumber ) = @_;
1096 FROM issues, items, biblio, biblioitems
1097 WHERE items.itemnumber=issues.itemnumber
1098 AND biblio.biblionumber = items.biblionumber
1099 AND biblio.biblionumber = biblioitems.biblionumber
1100 AND issues.borrowernumber = ?
1101 AND date_due < NOW()
1104 my $sth = C4::Context->dbh->prepare( $sql );
1105 $sth->execute( $borrowernumber );
1107 return $sth->fetchall_arrayref({});
1110 END { } # module clean-up code here (global destructor)