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 Koha::AuthUtils qw(hash_password);
43 use Koha::List::Patron;
45 use Koha::Patron::Categories;
47 our (@ISA,@EXPORT,@EXPORT_OK,$debug);
49 use Module::Load::Conditional qw( can_load );
50 if ( ! can_load( modules => { 'Koha::NorwegianPatronDB' => undef } ) ) {
51 $debug && warn "Unable to load Koha::NorwegianPatronDB";
56 $debug = $ENV{DEBUG} || 0;
65 &GetFirstValidEmailAddress
66 &GetNoticeEmailAddress
68 &GetMemberAccountRecords
69 &GetBorNotifyAcctRecord
71 &GetBorrowersToExpunge
103 C4::Members - Perl Module containing convenience functions for member handling
111 This module contains routines for adding, modifying and deleting members/patrons/borrowers
117 $flags = &patronflags($patron);
119 This function is not exported.
121 The following will be set where applicable:
122 $flags->{CHARGES}->{amount} Amount of debt
123 $flags->{CHARGES}->{noissues} Set if debt amount >$5.00 (or syspref noissuescharge)
124 $flags->{CHARGES}->{message} Message -- deprecated
126 $flags->{CREDITS}->{amount} Amount of credit
127 $flags->{CREDITS}->{message} Message -- deprecated
129 $flags->{ GNA } Patron has no valid address
130 $flags->{ GNA }->{noissues} Set for each GNA
131 $flags->{ GNA }->{message} "Borrower has no valid address" -- deprecated
133 $flags->{ LOST } Patron's card reported lost
134 $flags->{ LOST }->{noissues} Set for each LOST
135 $flags->{ LOST }->{message} Message -- deprecated
137 $flags->{DBARRED} Set if patron debarred, no access
138 $flags->{DBARRED}->{noissues} Set for each DBARRED
139 $flags->{DBARRED}->{message} Message -- deprecated
142 $flags->{ NOTES }->{message} The note itself. NOT deprecated
144 $flags->{ ODUES } Set if patron has overdue books.
145 $flags->{ ODUES }->{message} "Yes" -- deprecated
146 $flags->{ ODUES }->{itemlist} ref-to-array: list of overdue books
147 $flags->{ ODUES }->{itemlisttext} Text list of overdue items -- deprecated
149 $flags->{WAITING} Set if any of patron's reserves are available
150 $flags->{WAITING}->{message} Message -- deprecated
151 $flags->{WAITING}->{itemlist} ref-to-array: list of available items
155 =item C<$flags-E<gt>{ODUES}-E<gt>{itemlist}> is a reference-to-array listing the
156 overdue items. Its elements are references-to-hash, each describing an
157 overdue item. The keys are selected fields from the issues, biblio,
158 biblioitems, and items tables of the Koha database.
160 =item C<$flags-E<gt>{ODUES}-E<gt>{itemlisttext}> is a string giving a text listing of
161 the overdue items, one per line. Deprecated.
163 =item C<$flags-E<gt>{WAITING}-E<gt>{itemlist}> is a reference-to-array listing the
164 available items. Each element is a reference-to-hash whose keys are
165 fields from the reserves table of the Koha database.
169 All the "message" fields that include language generated in this function are deprecated,
170 because such strings belong properly in the display layer.
172 The "message" field that comes from the DB is OK.
176 # TODO: use {anonymous => hashes} instead of a dozen %flaginfo
177 # FIXME rename this function.
180 my ( $patroninformation) = @_;
181 my $dbh=C4::Context->dbh;
183 my $patron = Koha::Patrons->find( $patroninformation->{borrowernumber} );
184 my $account = $patron->account;
185 my $owing = $account->non_issues_charges;
189 my $noissuescharge = C4::Context->preference("noissuescharge") || 5;
190 $flaginfo{'message'} = sprintf 'Patron owes %.02f', $owing;
191 $flaginfo{'amount'} = sprintf "%.02f", $owing;
192 if ( $owing > $noissuescharge && !C4::Context->preference("AllowFineOverride") ) {
193 $flaginfo{'noissues'} = 1;
195 $flags{'CHARGES'} = \%flaginfo;
197 elsif ( ( my $balance = $account->balance ) < 0 ) {
199 $flaginfo{'message'} = sprintf 'Patron has credit of %.02f', -$balance;
200 $flaginfo{'amount'} = sprintf "%.02f", $balance;
201 $flags{'CREDITS'} = \%flaginfo;
204 # Check the debt of the guarntees of this patron
205 my $no_issues_charge_guarantees = C4::Context->preference("NoIssuesChargeGuarantees");
206 $no_issues_charge_guarantees = undef unless looks_like_number( $no_issues_charge_guarantees );
207 if ( defined $no_issues_charge_guarantees ) {
208 my $p = Koha::Patrons->find( $patroninformation->{borrowernumber} );
209 my @guarantees = $p->guarantees();
210 my $guarantees_non_issues_charges;
211 foreach my $g ( @guarantees ) {
212 my ( $b, $n, $o ) = C4::Members::GetMemberAccountBalance( $g->id );
213 $guarantees_non_issues_charges += $n;
216 if ( $guarantees_non_issues_charges > $no_issues_charge_guarantees ) {
218 $flaginfo{'message'} = sprintf 'patron guarantees owe %.02f', $guarantees_non_issues_charges;
219 $flaginfo{'amount'} = $guarantees_non_issues_charges;
220 $flaginfo{'noissues'} = 1 unless C4::Context->preference("allowfineoverride");
221 $flags{'CHARGES_GUARANTEES'} = \%flaginfo;
225 if ( $patroninformation->{'gonenoaddress'}
226 && $patroninformation->{'gonenoaddress'} == 1 )
229 $flaginfo{'message'} = 'Borrower has no valid address.';
230 $flaginfo{'noissues'} = 1;
231 $flags{'GNA'} = \%flaginfo;
233 if ( $patroninformation->{'lost'} && $patroninformation->{'lost'} == 1 ) {
235 $flaginfo{'message'} = 'Borrower\'s card reported lost.';
236 $flaginfo{'noissues'} = 1;
237 $flags{'LOST'} = \%flaginfo;
239 if ( $patroninformation->{'debarred'} && check_date( split( /-/, $patroninformation->{'debarred'} ) ) ) {
240 if ( Date_to_Days(Date::Calc::Today) < Date_to_Days( split( /-/, $patroninformation->{'debarred'} ) ) ) {
242 $flaginfo{'debarredcomment'} = $patroninformation->{'debarredcomment'};
243 $flaginfo{'message'} = $patroninformation->{'debarredcomment'};
244 $flaginfo{'noissues'} = 1;
245 $flaginfo{'dateend'} = $patroninformation->{'debarred'};
246 $flags{'DBARRED'} = \%flaginfo;
249 if ( $patroninformation->{'borrowernotes'}
250 && $patroninformation->{'borrowernotes'} )
253 $flaginfo{'message'} = $patroninformation->{'borrowernotes'};
254 $flags{'NOTES'} = \%flaginfo;
256 my ( $odues, $itemsoverdue ) = C4::Overdues::checkoverdues($patroninformation->{'borrowernumber'});
257 if ( $odues && $odues > 0 ) {
259 $flaginfo{'message'} = "Yes";
260 $flaginfo{'itemlist'} = $itemsoverdue;
261 foreach ( sort { $a->{'date_due'} cmp $b->{'date_due'} }
264 $flaginfo{'itemlisttext'} .=
265 "$_->{'date_due'} $_->{'barcode'} $_->{'title'} \n"; # newline is display layer
267 $flags{'ODUES'} = \%flaginfo;
270 my $patron = Koha::Patrons->find( $patroninformation->{borrowernumber} );
271 my $waiting_holds = $patron->holds->search({ found => 'W' });
272 my $nowaiting = $waiting_holds->count;
273 if ( $nowaiting > 0 ) {
275 $flaginfo{'message'} = "Reserved items available";
276 $flaginfo{'itemlist'} = $waiting_holds->unblessed;
277 $flags{'WAITING'} = \%flaginfo;
285 my $success = ModMember(borrowernumber => $borrowernumber,
286 [ field => value ]... );
288 Modify borrower's data. All date fields should ALREADY be in ISO format.
291 true on success, or false on failure
298 # trim whitespace from data which has some non-whitespace in it.
299 foreach my $field_name (keys(%data)) {
300 if ( defined $data{$field_name} && $data{$field_name} =~ /\S/ ) {
301 $data{$field_name} =~ s/^\s*|\s*$//g;
305 # test to know if you must update or not the borrower password
306 if (exists $data{password}) {
307 if ($data{password} eq '****' or $data{password} eq '') {
308 delete $data{password};
310 if ( C4::Context->preference('NorwegianPatronDBEnable') && C4::Context->preference('NorwegianPatronDBEnable') == 1 ) {
311 # Update the hashed PIN in borrower_sync.hashed_pin, before Koha hashes it
312 Koha::NorwegianPatronDB::NLUpdateHashedPIN( $data{'borrowernumber'}, $data{password} );
314 $data{password} = hash_password($data{password});
318 my $old_categorycode = Koha::Patrons->find( $data{borrowernumber} )->categorycode;
320 # get only the columns of a borrower
321 my $schema = Koha::Database->new()->schema;
322 my @columns = $schema->source('Borrower')->columns;
323 my $new_borrower = { map { join(' ', @columns) =~ /$_/ ? ( $_ => $data{$_} ) : () } keys(%data) };
325 $new_borrower->{dateofbirth} ||= undef if exists $new_borrower->{dateofbirth};
326 $new_borrower->{dateenrolled} ||= undef if exists $new_borrower->{dateenrolled};
327 $new_borrower->{dateexpiry} ||= undef if exists $new_borrower->{dateexpiry};
328 $new_borrower->{debarred} ||= undef if exists $new_borrower->{debarred};
329 $new_borrower->{sms_provider_id} ||= undef if exists $new_borrower->{sms_provider_id};
330 $new_borrower->{guarantorid} ||= undef if exists $new_borrower->{guarantorid};
332 my $patron = Koha::Patrons->find( $new_borrower->{borrowernumber} );
334 delete $new_borrower->{userid} if exists $new_borrower->{userid} and not $new_borrower->{userid};
336 my $execute_success = $patron->store if $patron->set($new_borrower);
338 if ($execute_success) { # only proceed if the update was a success
339 # If the patron changes to a category with enrollment fee, we add a fee
340 if ( $data{categorycode} and $data{categorycode} ne $old_categorycode ) {
341 if ( C4::Context->preference('FeeOnChangePatronCategory') ) {
342 $patron->add_enrolment_fee_if_needed;
346 # If NorwegianPatronDBEnable is enabled, we set syncstatus to something that a
347 # cronjob will use for syncing with NL
348 if ( C4::Context->preference('NorwegianPatronDBEnable') && C4::Context->preference('NorwegianPatronDBEnable') == 1 ) {
349 my $borrowersync = Koha::Database->new->schema->resultset('BorrowerSync')->find({
350 'synctype' => 'norwegianpatrondb',
351 'borrowernumber' => $data{'borrowernumber'}
353 # Do not set to "edited" if syncstatus is "new". We need to sync as new before
354 # we can sync as changed. And the "new sync" will pick up all changes since
355 # the patron was created anyway.
356 if ( $borrowersync->syncstatus ne 'new' && $borrowersync->syncstatus ne 'delete' ) {
357 $borrowersync->update( { 'syncstatus' => 'edited' } );
359 # Set the value of 'sync'
360 $borrowersync->update( { 'sync' => $data{'sync'} } );
361 # Try to do the live sync
362 Koha::NorwegianPatronDB::NLSync({ 'borrowernumber' => $data{'borrowernumber'} });
365 logaction("MEMBERS", "MODIFY", $data{'borrowernumber'}, "UPDATE (executed w/ arg: $data{'borrowernumber'})") if C4::Context->preference("BorrowersLog");
367 return $execute_success;
372 $borrowernumber = &AddMember(%borrower);
374 insert new borrower into table
376 (%borrower keys are database columns. Database columns could be
377 different in different versions. Please look into database for correct
380 Returns the borrowernumber upon success
382 Returns as undef upon any db error without further processing
389 my $dbh = C4::Context->dbh;
390 my $schema = Koha::Database->new()->schema;
392 # trim whitespace from data which has some non-whitespace in it.
393 foreach my $field_name (keys(%data)) {
394 if ( defined $data{$field_name} && $data{$field_name} =~ /\S/ ) {
395 $data{$field_name} =~ s/^\s*|\s*$//g;
399 # generate a proper login if none provided
400 $data{'userid'} = Generate_Userid( $data{'borrowernumber'}, $data{'firstname'}, $data{'surname'} )
401 if ( $data{'userid'} eq '' || !Check_Userid( $data{'userid'} ) );
403 # add expiration date if it isn't already there
404 $data{dateexpiry} ||= Koha::Patron::Categories->find( $data{categorycode} )->get_expiry_date;
406 # add enrollment date if it isn't already there
407 unless ( $data{'dateenrolled'} ) {
408 $data{'dateenrolled'} = output_pref( { dt => dt_from_string, dateonly => 1, dateformat => 'iso' } );
411 if ( C4::Context->preference("autoMemberNum") ) {
412 if ( not exists $data{cardnumber} or not defined $data{cardnumber} or $data{cardnumber} eq '' ) {
413 $data{cardnumber} = fixup_cardnumber( $data{cardnumber} );
417 my $patron_category = $schema->resultset('Category')->find( $data{'categorycode'} );
419 $patron_category->default_privacy() eq 'default' ? 1
420 : $patron_category->default_privacy() eq 'never' ? 2
421 : $patron_category->default_privacy() eq 'forever' ? 0
424 $data{'privacy_guarantor_checkouts'} = 0 unless defined( $data{'privacy_guarantor_checkouts'} );
426 # Make a copy of the plain text password for later use
427 my $plain_text_password = $data{'password'};
429 # create a disabled account if no password provided
430 $data{'password'} = ($data{'password'})? hash_password($data{'password'}) : '!';
432 # we don't want invalid dates in the db (mysql has a bad habit of inserting 0000-00-00
433 $data{'dateofbirth'} = undef if ( not $data{'dateofbirth'} );
434 $data{'debarred'} = undef if ( not $data{'debarred'} );
435 $data{'sms_provider_id'} = undef if ( not $data{'sms_provider_id'} );
436 $data{'guarantorid'} = undef if ( not $data{'guarantorid'} );
438 # get only the columns of Borrower
439 # FIXME Do we really need this check?
440 my @columns = $schema->source('Borrower')->columns;
441 my $new_member = { map { join(' ',@columns) =~ /$_/ ? ( $_ => $data{$_} ) : () } keys(%data) } ;
443 delete $new_member->{borrowernumber};
445 my $patron = Koha::Patron->new( $new_member )->store;
446 $data{borrowernumber} = $patron->borrowernumber;
448 # If NorwegianPatronDBEnable is enabled, we set syncstatus to something that a
449 # cronjob will use for syncing with NL
450 if ( exists $data{'borrowernumber'} && C4::Context->preference('NorwegianPatronDBEnable') && C4::Context->preference('NorwegianPatronDBEnable') == 1 ) {
451 Koha::Database->new->schema->resultset('BorrowerSync')->create({
452 'borrowernumber' => $data{'borrowernumber'},
453 'synctype' => 'norwegianpatrondb',
455 'syncstatus' => 'new',
456 'hashed_pin' => Koha::NorwegianPatronDB::NLEncryptPIN( $plain_text_password ),
460 logaction("MEMBERS", "CREATE", $data{'borrowernumber'}, "") if C4::Context->preference("BorrowersLog");
462 $patron->add_enrolment_fee_if_needed;
464 return $data{borrowernumber};
469 my $uniqueness = Check_Userid($userid,$borrowernumber);
471 $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 != '').
473 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.
476 0 for not unique (i.e. this $userid already exists)
477 1 for unique (i.e. this $userid does not exist, or this $userid/$borrowernumber combination already exists)
482 my ( $uid, $borrowernumber ) = @_;
484 return 0 unless ($uid); # userid is a unique column, we should assume NULL is not unique
486 return 0 if ( $uid eq C4::Context->config('user') );
488 my $rs = Koha::Database->new()->schema()->resultset('Borrower');
491 $params->{userid} = $uid;
492 $params->{borrowernumber} = { '!=' => $borrowernumber } if ($borrowernumber);
494 my $count = $rs->count( $params );
496 return $count ? 0 : 1;
499 =head2 Generate_Userid
501 my $newuid = Generate_Userid($borrowernumber, $firstname, $surname);
503 Generate a userid using the $surname and the $firstname (if there is a value in $firstname).
505 $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.
508 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).
512 sub Generate_Userid {
513 my ($borrowernumber, $firstname, $surname) = @_;
516 #The script will "do" the following code and increment the $offset until Check_Userid = 1 (i.e. until $newuid comes back as unique)
518 $firstname =~ s/[[:digit:][:space:][:blank:][:punct:][:cntrl:]]//g;
519 $surname =~ s/[[:digit:][:space:][:blank:][:punct:][:cntrl:]]//g;
520 $newuid = lc(($firstname)? "$firstname.$surname" : $surname);
521 $newuid = unac_string('utf-8',$newuid);
522 $newuid .= $offset unless $offset == 0;
525 } while (!Check_Userid($newuid,$borrowernumber));
530 =head2 fixup_cardnumber
532 Warning: The caller is responsible for locking the members table in write
533 mode, to avoid database corruption.
537 use vars qw( @weightings );
538 my @weightings = ( 8, 4, 6, 3, 5, 2, 1 );
540 sub fixup_cardnumber {
541 my ($cardnumber) = @_;
542 my $autonumber_members = C4::Context->boolean_preference('autoMemberNum') || 0;
544 # Find out whether member numbers should be generated
545 # automatically. Should be either "1" or something else.
546 # Defaults to "0", which is interpreted as "no".
548 # if ($cardnumber !~ /\S/ && $autonumber_members) {
549 ($autonumber_members) or return $cardnumber;
550 my $checkdigit = C4::Context->preference('checkdigit');
551 my $dbh = C4::Context->dbh;
552 if ( $checkdigit and $checkdigit eq 'katipo' ) {
554 # if checkdigit is selected, calculate katipo-style cardnumber.
555 # otherwise, just use the max()
556 # purpose: generate checksum'd member numbers.
557 # We'll assume we just got the max value of digits 2-8 of member #'s
558 # from the database and our job is to increment that by one,
559 # determine the 1st and 9th digits and return the full string.
560 my $sth = $dbh->prepare(
561 "select max(substring(borrowers.cardnumber,2,7)) as new_num from borrowers"
564 my $data = $sth->fetchrow_hashref;
565 $cardnumber = $data->{new_num};
566 if ( !$cardnumber ) { # If DB has no values,
567 $cardnumber = 1000000; # start at 1000000
573 for ( my $i = 0 ; $i < 8 ; $i += 1 ) {
574 # read weightings, left to right, 1 char at a time
575 my $temp1 = $weightings[$i];
577 # sequence left to right, 1 char at a time
578 my $temp2 = substr( $cardnumber, $i, 1 );
580 # mult each char 1-7 by its corresponding weighting
581 $sum += $temp1 * $temp2;
584 my $rem = ( $sum % 11 );
585 $rem = 'X' if $rem == 10;
587 return "V$cardnumber$rem";
590 my $sth = $dbh->prepare(
591 'SELECT MAX( CAST( cardnumber AS SIGNED ) ) FROM borrowers WHERE cardnumber REGEXP "^-?[0-9]+$"'
594 my ($result) = $sth->fetchrow;
597 return $cardnumber; # just here as a fallback/reminder
600 =head2 GetPendingIssues
602 my $issues = &GetPendingIssues(@borrowernumber);
604 Looks up what the patron with the given borrowernumber has borrowed.
606 C<&GetPendingIssues> returns a
607 reference-to-array where each element is a reference-to-hash; the
608 keys are the fields from the C<issues>, C<biblio>, and C<items> tables.
609 The keys include C<biblioitems> fields.
613 sub GetPendingIssues {
614 my @borrowernumbers = @_;
616 unless (@borrowernumbers ) { # return a ref_to_array
617 return \@borrowernumbers; # to not cause surprise to caller
620 # Borrowers part of the query
622 for (my $i = 0; $i < @borrowernumbers; $i++) {
623 $bquery .= ' issues.borrowernumber = ?';
624 if ($i < $#borrowernumbers ) {
629 # FIXME: namespace collision: each table has "timestamp" fields. Which one is "timestamp" ?
630 # FIXME: circ/ciculation.pl tries to sort by timestamp!
631 # FIXME: namespace collision: other collisions possible.
632 # FIXME: most of this data isn't really being used by callers.
639 biblioitems.itemtype,
642 biblioitems.publicationyear,
643 biblioitems.publishercode,
644 biblioitems.volumedate,
645 biblioitems.volumedesc,
650 borrowers.cardnumber,
651 issues.timestamp AS timestamp,
652 issues.renewals AS renewals,
653 issues.borrowernumber AS borrowernumber,
654 items.renewals AS totalrenewals
656 LEFT JOIN items ON items.itemnumber = issues.itemnumber
657 LEFT JOIN biblio ON items.biblionumber = biblio.biblionumber
658 LEFT JOIN biblioitems ON items.biblioitemnumber = biblioitems.biblioitemnumber
659 LEFT JOIN borrowers ON issues.borrowernumber = borrowers.borrowernumber
662 ORDER BY issues.issuedate"
665 my $sth = C4::Context->dbh->prepare($query);
666 $sth->execute(@borrowernumbers);
667 my $data = $sth->fetchall_arrayref({});
668 my $today = dt_from_string;
670 if ($_->{issuedate}) {
671 $_->{issuedate} = dt_from_string($_->{issuedate}, 'sql');
673 $_->{date_due_sql} = $_->{date_due};
674 # FIXME no need to have this value
675 $_->{date_due} or next;
676 $_->{date_due_sql} = $_->{date_due};
677 # FIXME no need to have this value
678 $_->{date_due} = dt_from_string($_->{date_due}, 'sql');
679 if ( DateTime->compare($_->{date_due}, $today) == -1 ) {
688 $issues = &GetAllIssues($borrowernumber, $sortkey, $limit);
690 Looks up what the patron with the given borrowernumber has borrowed,
691 and sorts the results.
693 C<$sortkey> is the name of a field on which to sort the results. This
694 should be the name of a field in the C<issues>, C<biblio>,
695 C<biblioitems>, or C<items> table in the Koha database.
697 C<$limit> is the maximum number of results to return.
699 C<&GetAllIssues> an arrayref, C<$issues>, of hashrefs, the keys of which
700 are the fields from the C<issues>, C<biblio>, C<biblioitems>, and
701 C<items> tables of the Koha database.
707 my ( $borrowernumber, $order, $limit ) = @_;
709 return unless $borrowernumber;
710 $order = 'date_due desc' unless $order;
712 my $dbh = C4::Context->dbh;
714 'SELECT *, issues.timestamp as issuestimestamp, issues.renewals AS renewals,items.renewals AS totalrenewals,items.timestamp AS itemstimestamp
716 LEFT JOIN items on items.itemnumber=issues.itemnumber
717 LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber
718 LEFT JOIN biblioitems ON items.biblioitemnumber=biblioitems.biblioitemnumber
719 WHERE borrowernumber=?
721 SELECT *, old_issues.timestamp as issuestimestamp, old_issues.renewals AS renewals,items.renewals AS totalrenewals,items.timestamp AS itemstimestamp
723 LEFT JOIN items on items.itemnumber=old_issues.itemnumber
724 LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber
725 LEFT JOIN biblioitems ON items.biblioitemnumber=biblioitems.biblioitemnumber
726 WHERE borrowernumber=? AND old_issues.itemnumber IS NOT NULL
729 $query .= " limit $limit";
732 my $sth = $dbh->prepare($query);
733 $sth->execute( $borrowernumber, $borrowernumber );
734 return $sth->fetchall_arrayref( {} );
738 =head2 GetMemberAccountRecords
740 ($total, $acctlines, $count) = &GetMemberAccountRecords($borrowernumber);
742 Looks up accounting data for the patron with the given borrowernumber.
744 C<&GetMemberAccountRecords> returns a three-element array. C<$acctlines> is a
745 reference-to-array, where each element is a reference-to-hash; the
746 keys are the fields of the C<accountlines> table in the Koha database.
747 C<$count> is the number of elements in C<$acctlines>. C<$total> is the
748 total amount outstanding for all of the account lines.
752 sub GetMemberAccountRecords {
753 my ($borrowernumber) = @_;
754 my $dbh = C4::Context->dbh;
760 WHERE borrowernumber=?);
761 $strsth.=" ORDER BY accountlines_id desc";
762 my $sth= $dbh->prepare( $strsth );
763 $sth->execute( $borrowernumber );
766 while ( my $data = $sth->fetchrow_hashref ) {
767 if ( $data->{itemnumber} ) {
768 my $item = Koha::Items->find( $data->{itemnumber} );
769 my $biblio = $item->biblio;
770 $data->{biblionumber} = $biblio->biblionumber;
771 $data->{title} = $biblio->title;
773 $acctlines[$numlines] = $data;
775 $total += sprintf "%.0f", 1000*$data->{amountoutstanding}; # convert float to integer to avoid round-off errors
778 return ( $total, \@acctlines,$numlines);
781 =head2 GetMemberAccountBalance
783 ($total_balance, $non_issue_balance, $other_charges) = &GetMemberAccountBalance($borrowernumber);
785 Calculates amount immediately owing by the patron - non-issue charges.
786 Based on GetMemberAccountRecords.
787 Charges exempt from non-issue are:
789 * Rent (rental) if RentalsInNoissuesCharge syspref is set to false
790 * Manual invoices if ManInvInNoissuesCharge syspref is set to false
794 sub GetMemberAccountBalance {
795 my ($borrowernumber) = @_;
797 my $ACCOUNT_TYPE_LENGTH = 5; # this is plain ridiculous...
800 push @not_fines, 'Res' unless C4::Context->preference('HoldsInNoissuesCharge');
801 push @not_fines, 'Rent' unless C4::Context->preference('RentalsInNoissuesCharge');
802 unless ( C4::Context->preference('ManInvInNoissuesCharge') ) {
803 my $dbh = C4::Context->dbh;
804 my $man_inv_types = $dbh->selectcol_arrayref(qq{SELECT authorised_value FROM authorised_values WHERE category = 'MANUAL_INV'});
805 push @not_fines, map substr($_, 0, $ACCOUNT_TYPE_LENGTH), @$man_inv_types;
807 my %not_fine = map {$_ => 1} @not_fines;
809 my ($total, $acctlines) = GetMemberAccountRecords($borrowernumber);
810 my $other_charges = 0;
811 foreach (@$acctlines) {
812 $other_charges += $_->{amountoutstanding} if $not_fine{ substr($_->{accounttype}, 0, $ACCOUNT_TYPE_LENGTH) };
815 return ( $total, $total - $other_charges, $other_charges);
818 =head2 GetBorNotifyAcctRecord
820 ($total, $acctlines, $count) = &GetBorNotifyAcctRecord($params,$notifyid);
822 Looks up accounting data for the patron with the given borrowernumber per file number.
824 C<&GetBorNotifyAcctRecord> returns a three-element array. C<$acctlines> is a
825 reference-to-array, where each element is a reference-to-hash; the
826 keys are the fields of the C<accountlines> table in the Koha database.
827 C<$count> is the number of elements in C<$acctlines>. C<$total> is the
828 total amount outstanding for all of the account lines.
832 sub GetBorNotifyAcctRecord {
833 my ( $borrowernumber, $notifyid ) = @_;
834 my $dbh = C4::Context->dbh;
837 my $sth = $dbh->prepare(
840 WHERE borrowernumber=?
842 AND amountoutstanding != '0'
843 ORDER BY notify_id,accounttype
846 $sth->execute( $borrowernumber, $notifyid );
848 while ( my $data = $sth->fetchrow_hashref ) {
849 if ( $data->{itemnumber} ) {
850 my $item = Koha::Items->find( $data->{itemnumber} );
851 my $biblio = $item->biblio;
852 $data->{biblionumber} = $biblio->biblionumber;
853 $data->{title} = $biblio->title;
855 $acctlines[$numlines] = $data;
857 $total += int(100 * $data->{'amountoutstanding'});
860 return ( $total, \@acctlines, $numlines );
863 sub checkcardnumber {
864 my ( $cardnumber, $borrowernumber ) = @_;
866 # If cardnumber is null, we assume they're allowed.
867 return 0 unless defined $cardnumber;
869 my $dbh = C4::Context->dbh;
870 my $query = "SELECT * FROM borrowers WHERE cardnumber=?";
871 $query .= " AND borrowernumber <> ?" if ($borrowernumber);
872 my $sth = $dbh->prepare($query);
875 ( $borrowernumber ? $borrowernumber : () )
878 return 1 if $sth->fetchrow_hashref;
880 my ( $min_length, $max_length ) = get_cardnumber_length();
882 if length $cardnumber > $max_length
883 or length $cardnumber < $min_length;
888 =head2 get_cardnumber_length
890 my ($min, $max) = C4::Members::get_cardnumber_length()
892 Returns the minimum and maximum length for patron cardnumbers as
893 determined by the CardnumberLength system preference, the
894 BorrowerMandatoryField system preference, and the width of the
899 sub get_cardnumber_length {
900 my $borrower = Koha::Database->new->schema->resultset('Borrower');
901 my $field_size = $borrower->result_source->column_info('cardnumber')->{size};
902 my ( $min, $max ) = ( 0, $field_size ); # borrowers.cardnumber is a nullable varchar(20)
903 $min = 1 if C4::Context->preference('BorrowerMandatoryField') =~ /cardnumber/;
904 if ( my $cardnumber_length = C4::Context->preference('CardnumberLength') ) {
905 # Is integer and length match
906 if ( $cardnumber_length =~ m|^\d+$| ) {
907 $min = $max = $cardnumber_length
908 if $cardnumber_length >= $min
909 and $cardnumber_length <= $max;
911 # Else assuming it is a range
912 elsif ( $cardnumber_length =~ m|(\d*),(\d*)| ) {
913 $min = $1 if $1 and $min < $1;
914 $max = $2 if $2 and $max > $2;
918 $min = $max if $min > $max;
919 return ( $min, $max );
922 =head2 GetFirstValidEmailAddress
924 $email = GetFirstValidEmailAddress($borrowernumber);
926 Return the first valid email address for a borrower, given the borrowernumber. For now, the order
927 is defined as email, emailpro, B_email. Returns the empty string if the borrower has no email
932 sub GetFirstValidEmailAddress {
933 my $borrowernumber = shift;
935 my $borrower = Koha::Patrons->find( $borrowernumber );
937 return $borrower->first_valid_email_address();
940 =head2 GetNoticeEmailAddress
942 $email = GetNoticeEmailAddress($borrowernumber);
944 Return the email address of borrower used for notices, given the borrowernumber.
945 Returns the empty string if no email address.
949 sub GetNoticeEmailAddress {
950 my $borrowernumber = shift;
952 my $which_address = C4::Context->preference("AutoEmailPrimaryAddress");
953 # if syspref is set to 'first valid' (value == OFF), look up email address
954 if ( $which_address eq 'OFF' ) {
955 return GetFirstValidEmailAddress($borrowernumber);
957 # specified email address field
958 my $dbh = C4::Context->dbh;
959 my $sth = $dbh->prepare( qq{
960 SELECT $which_address AS primaryemail
962 WHERE borrowernumber=?
964 $sth->execute($borrowernumber);
965 my $data = $sth->fetchrow_hashref;
966 return $data->{'primaryemail'} || '';
969 =head2 GetBorrowersToExpunge
971 $borrowers = &GetBorrowersToExpunge(
972 not_borrowed_since => $not_borrowed_since,
973 expired_before => $expired_before,
974 category_code => $category_code,
975 patron_list_id => $patron_list_id,
976 branchcode => $branchcode
979 This function get all borrowers based on the given criteria.
983 sub GetBorrowersToExpunge {
986 my $filterdate = $params->{'not_borrowed_since'};
987 my $filterexpiry = $params->{'expired_before'};
988 my $filterlastseen = $params->{'last_seen'};
989 my $filtercategory = $params->{'category_code'};
990 my $filterbranch = $params->{'branchcode'} ||
991 ((C4::Context->preference('IndependentBranches')
992 && C4::Context->userenv
993 && !C4::Context->IsSuperLibrarian()
994 && C4::Context->userenv->{branch})
995 ? C4::Context->userenv->{branch}
997 my $filterpatronlist = $params->{'patron_list_id'};
999 my $dbh = C4::Context->dbh;
1001 SELECT borrowers.borrowernumber,
1002 MAX(old_issues.timestamp) AS latestissue,
1003 MAX(issues.timestamp) AS currentissue
1005 JOIN categories USING (categorycode)
1009 WHERE guarantorid IS NOT NULL
1010 AND guarantorid <> 0
1011 ) as tmp ON borrowers.borrowernumber=tmp.guarantorid
1012 LEFT JOIN old_issues USING (borrowernumber)
1013 LEFT JOIN issues USING (borrowernumber)|;
1014 if ( $filterpatronlist ){
1015 $query .= q| LEFT JOIN patron_list_patrons USING (borrowernumber)|;
1017 $query .= q| WHERE category_type <> 'S'
1018 AND tmp.guarantorid IS NULL
1021 if ( $filterbranch && $filterbranch ne "" ) {
1022 $query.= " AND borrowers.branchcode = ? ";
1023 push( @query_params, $filterbranch );
1025 if ( $filterexpiry ) {
1026 $query .= " AND dateexpiry < ? ";
1027 push( @query_params, $filterexpiry );
1029 if ( $filterlastseen ) {
1030 $query .= ' AND lastseen < ? ';
1031 push @query_params, $filterlastseen;
1033 if ( $filtercategory ) {
1034 $query .= " AND categorycode = ? ";
1035 push( @query_params, $filtercategory );
1037 if ( $filterpatronlist ){
1038 $query.=" AND patron_list_id = ? ";
1039 push( @query_params, $filterpatronlist );
1041 $query.=" GROUP BY borrowers.borrowernumber HAVING currentissue IS NULL ";
1042 if ( $filterdate ) {
1043 $query.=" AND ( latestissue < ? OR latestissue IS NULL ) ";
1044 push @query_params,$filterdate;
1046 warn $query if $debug;
1048 my $sth = $dbh->prepare($query);
1049 if (scalar(@query_params)>0){
1050 $sth->execute(@query_params);
1057 while ( my $data = $sth->fetchrow_hashref ) {
1058 push @results, $data;
1065 IssueSlip($branchcode, $borrowernumber, $quickslip)
1067 Returns letter hash ( see C4::Letters::GetPreparedLetter )
1069 $quickslip is boolean, to indicate whether we want a quick slip
1071 IssueSlip populates ISSUESLIP and ISSUEQSLIP, and will make the following expansions:
1107 NOTE: Not all table fields are available, pleasee see GetPendingIssues for a list of available fields.
1112 my ($branch, $borrowernumber, $quickslip) = @_;
1114 # FIXME Check callers before removing this statement
1115 #return unless $borrowernumber;
1117 my $patron = Koha::Patrons->find( $borrowernumber );
1118 return unless $patron;
1120 my @issues = @{ GetPendingIssues($borrowernumber) };
1122 for my $issue (@issues) {
1123 $issue->{date_due} = $issue->{date_due_sql};
1125 my $today = output_pref({ dt => dt_from_string, dateformat => 'iso', dateonly => 1 });
1126 if ( substr( $issue->{issuedate}, 0, 10 ) eq $today
1127 or substr( $issue->{lastreneweddate}, 0, 10 ) eq $today ) {
1133 # Sort on timestamp then on issuedate then on issue_id
1134 # useful for tests and could be if modified in a batch
1136 $b->{timestamp} <=> $a->{timestamp}
1137 or $b->{issuedate} <=> $a->{issuedate}
1138 or $b->{issue_id} <=> $a->{issue_id}
1141 my ($letter_code, %repeat, %loops);
1143 $letter_code = 'ISSUEQSLIP';
1144 my @checkouts = map {
1147 'biblioitems' => $_,
1149 }, grep { $_->{'now'} } @issues;
1151 checkedout => \@checkouts, # History syntax
1154 issues => [ map { $_->{issues}{itemnumber} } @checkouts ], # TT syntax
1158 my @checkouts = map {
1161 'biblioitems' => $_,
1163 }, grep { !$_->{'overdue'} } @issues;
1164 my @overdues = map {
1167 'biblioitems' => $_,
1169 }, grep { $_->{'overdue'} } @issues;
1170 my $news = GetNewsToDisplay( "slip", $branch );
1172 $_->{'timestamp'} = $_->{'newdate'};
1175 $letter_code = 'ISSUESLIP';
1177 checkedout => \@checkouts,
1178 overdue => \@overdues,
1182 issues => [ map { $_->{issues}{itemnumber} } @checkouts ],
1183 overdues => [ map { $_->{issues}{itemnumber} } @overdues ],
1184 opac_news => [ map { $_->{opac_news}{idnew} } @news ],
1188 return C4::Letters::GetPreparedLetter (
1189 module => 'circulation',
1190 letter_code => $letter_code,
1191 branchcode => $branch,
1192 lang => $patron->lang,
1194 'branches' => $branch,
1195 'borrowers' => $borrowernumber,
1202 =head2 AddMember_Auto
1206 sub AddMember_Auto {
1207 my ( %borrower ) = @_;
1209 $borrower{'cardnumber'} ||= fixup_cardnumber();
1211 $borrower{'borrowernumber'} = AddMember(%borrower);
1213 return ( %borrower );
1216 =head2 AddMember_Opac
1220 sub AddMember_Opac {
1221 my ( %borrower ) = @_;
1223 $borrower{'categorycode'} //= C4::Context->preference('PatronSelfRegistrationDefaultCategory');
1224 if (not defined $borrower{'password'}){
1225 my $sr = new String::Random;
1226 $sr->{'A'} = [ 'A'..'Z', 'a'..'z' ];
1227 my $password = $sr->randpattern("AAAAAAAAAA");
1228 $borrower{'password'} = $password;
1231 %borrower = AddMember_Auto(%borrower);
1233 return ( $borrower{'borrowernumber'}, $borrower{'password'} );
1236 =head2 DeleteExpiredOpacRegistrations
1238 Delete accounts that haven't been upgraded from the 'temporary' category
1239 Returns the number of removed patrons
1243 sub DeleteExpiredOpacRegistrations {
1245 my $delay = C4::Context->preference('PatronSelfRegistrationExpireTemporaryAccountsDelay');
1246 my $category_code = C4::Context->preference('PatronSelfRegistrationDefaultCategory');
1248 return 0 if not $category_code or not defined $delay or $delay eq q||;
1251 SELECT borrowernumber
1253 WHERE categorycode = ? AND DATEDIFF( NOW(), dateenrolled ) > ? |;
1255 my $dbh = C4::Context->dbh;
1256 my $sth = $dbh->prepare($query);
1257 $sth->execute( $category_code, $delay );
1259 while ( my ($borrowernumber) = $sth->fetchrow_array() ) {
1260 Koha::Patrons->find($borrowernumber)->delete;
1266 =head2 DeleteUnverifiedOpacRegistrations
1268 Delete all unverified self registrations in borrower_modifications,
1269 older than the specified number of days.
1273 sub DeleteUnverifiedOpacRegistrations {
1275 my $dbh = C4::Context->dbh;
1277 DELETE FROM borrower_modifications
1278 WHERE borrowernumber = 0 AND DATEDIFF( NOW(), timestamp ) > ?|;
1279 my $cnt=$dbh->do($sql, undef, ($days) );
1280 return $cnt eq '0E0'? 0: $cnt;
1283 sub GetOverduesForPatron {
1284 my ( $borrowernumber ) = @_;
1288 FROM issues, items, biblio, biblioitems
1289 WHERE items.itemnumber=issues.itemnumber
1290 AND biblio.biblionumber = items.biblionumber
1291 AND biblio.biblionumber = biblioitems.biblionumber
1292 AND issues.borrowernumber = ?
1293 AND date_due < NOW()
1296 my $sth = C4::Context->dbh->prepare( $sql );
1297 $sth->execute( $borrowernumber );
1299 return $sth->fetchall_arrayref({});
1302 END { } # module clean-up code here (global destructor)