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 );
30 use C4::Log; # logaction
36 use C4::Members::Attributes qw(SearchIdMatchingAttribute UpdateBorrowerAttribute);
37 use C4::NewsChannels; #get slip news
41 use Text::Unaccent qw( unac_string );
42 use Koha::AuthUtils qw(hash_password);
45 use Koha::List::Patron;
47 use Koha::Patron::Categories;
50 our (@ISA,@EXPORT,@EXPORT_OK,$debug);
52 use Module::Load::Conditional qw( can_load );
53 if ( ! can_load( modules => { 'Koha::NorwegianPatronDB' => undef } ) ) {
54 $debug && warn "Unable to load Koha::NorwegianPatronDB";
59 $debug = $ENV{DEBUG} || 0;
68 &GetBorrowersToExpunge
100 C4::Members - Perl Module containing convenience functions for member handling
108 This module contains routines for adding, modifying and deleting members/patrons/borrowers
114 $flags = &patronflags($patron);
116 This function is not exported.
118 The following will be set where applicable:
119 $flags->{CHARGES}->{amount} Amount of debt
120 $flags->{CHARGES}->{noissues} Set if debt amount >$5.00 (or syspref noissuescharge)
121 $flags->{CHARGES}->{message} Message -- deprecated
123 $flags->{CREDITS}->{amount} Amount of credit
124 $flags->{CREDITS}->{message} Message -- deprecated
126 $flags->{ GNA } Patron has no valid address
127 $flags->{ GNA }->{noissues} Set for each GNA
128 $flags->{ GNA }->{message} "Borrower has no valid address" -- deprecated
130 $flags->{ LOST } Patron's card reported lost
131 $flags->{ LOST }->{noissues} Set for each LOST
132 $flags->{ LOST }->{message} Message -- deprecated
134 $flags->{DBARRED} Set if patron debarred, no access
135 $flags->{DBARRED}->{noissues} Set for each DBARRED
136 $flags->{DBARRED}->{message} Message -- deprecated
139 $flags->{ NOTES }->{message} The note itself. NOT deprecated
141 $flags->{ ODUES } Set if patron has overdue books.
142 $flags->{ ODUES }->{message} "Yes" -- deprecated
143 $flags->{ ODUES }->{itemlist} ref-to-array: list of overdue books
144 $flags->{ ODUES }->{itemlisttext} Text list of overdue items -- deprecated
146 $flags->{WAITING} Set if any of patron's reserves are available
147 $flags->{WAITING}->{message} Message -- deprecated
148 $flags->{WAITING}->{itemlist} ref-to-array: list of available items
152 =item C<$flags-E<gt>{ODUES}-E<gt>{itemlist}> is a reference-to-array listing the
153 overdue items. Its elements are references-to-hash, each describing an
154 overdue item. The keys are selected fields from the issues, biblio,
155 biblioitems, and items tables of the Koha database.
157 =item C<$flags-E<gt>{ODUES}-E<gt>{itemlisttext}> is a string giving a text listing of
158 the overdue items, one per line. Deprecated.
160 =item C<$flags-E<gt>{WAITING}-E<gt>{itemlist}> is a reference-to-array listing the
161 available items. Each element is a reference-to-hash whose keys are
162 fields from the reserves table of the Koha database.
166 All the "message" fields that include language generated in this function are deprecated,
167 because such strings belong properly in the display layer.
169 The "message" field that comes from the DB is OK.
173 # TODO: use {anonymous => hashes} instead of a dozen %flaginfo
174 # FIXME rename this function.
177 my ( $patroninformation) = @_;
178 my $dbh=C4::Context->dbh;
179 my ($balance, $owing) = GetMemberAccountBalance( $patroninformation->{'borrowernumber'});
182 my $noissuescharge = C4::Context->preference("noissuescharge") || 5;
183 $flaginfo{'message'} = sprintf 'Patron owes %.02f', $owing;
184 $flaginfo{'amount'} = sprintf "%.02f", $owing;
185 if ( $owing > $noissuescharge && !C4::Context->preference("AllowFineOverride") ) {
186 $flaginfo{'noissues'} = 1;
188 $flags{'CHARGES'} = \%flaginfo;
190 elsif ( $balance < 0 ) {
192 $flaginfo{'message'} = sprintf 'Patron has credit of %.02f', -$balance;
193 $flaginfo{'amount'} = sprintf "%.02f", $balance;
194 $flags{'CREDITS'} = \%flaginfo;
197 # Check the debt of the guarntees of this patron
198 my $no_issues_charge_guarantees = C4::Context->preference("NoIssuesChargeGuarantees");
199 $no_issues_charge_guarantees = undef unless looks_like_number( $no_issues_charge_guarantees );
200 if ( defined $no_issues_charge_guarantees ) {
201 my $p = Koha::Patrons->find( $patroninformation->{borrowernumber} );
202 my @guarantees = $p->guarantees();
203 my $guarantees_non_issues_charges;
204 foreach my $g ( @guarantees ) {
205 my ( $b, $n, $o ) = C4::Members::GetMemberAccountBalance( $g->id );
206 $guarantees_non_issues_charges += $n;
209 if ( $guarantees_non_issues_charges > $no_issues_charge_guarantees ) {
211 $flaginfo{'message'} = sprintf 'patron guarantees owe %.02f', $guarantees_non_issues_charges;
212 $flaginfo{'amount'} = $guarantees_non_issues_charges;
213 $flaginfo{'noissues'} = 1 unless C4::Context->preference("allowfineoverride");
214 $flags{'CHARGES_GUARANTEES'} = \%flaginfo;
218 if ( $patroninformation->{'gonenoaddress'}
219 && $patroninformation->{'gonenoaddress'} == 1 )
222 $flaginfo{'message'} = 'Borrower has no valid address.';
223 $flaginfo{'noissues'} = 1;
224 $flags{'GNA'} = \%flaginfo;
226 if ( $patroninformation->{'lost'} && $patroninformation->{'lost'} == 1 ) {
228 $flaginfo{'message'} = 'Borrower\'s card reported lost.';
229 $flaginfo{'noissues'} = 1;
230 $flags{'LOST'} = \%flaginfo;
232 if ( $patroninformation->{'debarred'} && check_date( split( /-/, $patroninformation->{'debarred'} ) ) ) {
233 if ( Date_to_Days(Date::Calc::Today) < Date_to_Days( split( /-/, $patroninformation->{'debarred'} ) ) ) {
235 $flaginfo{'debarredcomment'} = $patroninformation->{'debarredcomment'};
236 $flaginfo{'message'} = $patroninformation->{'debarredcomment'};
237 $flaginfo{'noissues'} = 1;
238 $flaginfo{'dateend'} = $patroninformation->{'debarred'};
239 $flags{'DBARRED'} = \%flaginfo;
242 if ( $patroninformation->{'borrowernotes'}
243 && $patroninformation->{'borrowernotes'} )
246 $flaginfo{'message'} = $patroninformation->{'borrowernotes'};
247 $flags{'NOTES'} = \%flaginfo;
249 my ( $odues, $itemsoverdue ) = C4::Overdues::checkoverdues($patroninformation->{'borrowernumber'});
250 if ( $odues && $odues > 0 ) {
252 $flaginfo{'message'} = "Yes";
253 $flaginfo{'itemlist'} = $itemsoverdue;
254 foreach ( sort { $a->{'date_due'} cmp $b->{'date_due'} }
257 $flaginfo{'itemlisttext'} .=
258 "$_->{'date_due'} $_->{'barcode'} $_->{'title'} \n"; # newline is display layer
260 $flags{'ODUES'} = \%flaginfo;
263 my $patron = Koha::Patrons->find( $patroninformation->{borrowernumber} );
264 my $waiting_holds = $patron->holds->search({ found => 'W' });
265 my $nowaiting = $waiting_holds->count;
266 if ( $nowaiting > 0 ) {
268 $flaginfo{'message'} = "Reserved items available";
269 $flaginfo{'itemlist'} = $waiting_holds->unblessed;
270 $flags{'WAITING'} = \%flaginfo;
278 my $success = ModMember(borrowernumber => $borrowernumber,
279 [ field => value ]... );
281 Modify borrower's data. All date fields should ALREADY be in ISO format.
284 true on success, or false on failure
291 # trim whitespace from data which has some non-whitespace in it.
292 foreach my $field_name (keys(%data)) {
293 if ( defined $data{$field_name} && $data{$field_name} =~ /\S/ ) {
294 $data{$field_name} =~ s/^\s*|\s*$//g;
298 # test to know if you must update or not the borrower password
299 if (exists $data{password}) {
300 if ($data{password} eq '****' or $data{password} eq '') {
301 delete $data{password};
303 if ( C4::Context->preference('NorwegianPatronDBEnable') && C4::Context->preference('NorwegianPatronDBEnable') == 1 ) {
304 # Update the hashed PIN in borrower_sync.hashed_pin, before Koha hashes it
305 Koha::NorwegianPatronDB::NLUpdateHashedPIN( $data{'borrowernumber'}, $data{password} );
307 $data{password} = hash_password($data{password});
311 my $old_categorycode = Koha::Patrons->find( $data{borrowernumber} )->categorycode;
313 # get only the columns of a borrower
314 my $schema = Koha::Database->new()->schema;
315 my @columns = $schema->source('Borrower')->columns;
316 my $new_borrower = { map { join(' ', @columns) =~ /$_/ ? ( $_ => $data{$_} ) : () } keys(%data) };
318 $new_borrower->{dateofbirth} ||= undef if exists $new_borrower->{dateofbirth};
319 $new_borrower->{dateenrolled} ||= undef if exists $new_borrower->{dateenrolled};
320 $new_borrower->{dateexpiry} ||= undef if exists $new_borrower->{dateexpiry};
321 $new_borrower->{debarred} ||= undef if exists $new_borrower->{debarred};
322 $new_borrower->{sms_provider_id} ||= undef if exists $new_borrower->{sms_provider_id};
323 $new_borrower->{guarantorid} ||= undef if exists $new_borrower->{guarantorid};
325 my $patron = Koha::Patrons->find( $new_borrower->{borrowernumber} );
327 delete $new_borrower->{userid} if exists $new_borrower->{userid} and not $new_borrower->{userid};
329 my $execute_success = $patron->store if $patron->set($new_borrower);
331 if ($execute_success) { # only proceed if the update was a success
332 # If the patron changes to a category with enrollment fee, we add a fee
333 if ( $data{categorycode} and $data{categorycode} ne $old_categorycode ) {
334 if ( C4::Context->preference('FeeOnChangePatronCategory') ) {
335 $patron->add_enrolment_fee_if_needed;
339 # If NorwegianPatronDBEnable is enabled, we set syncstatus to something that a
340 # cronjob will use for syncing with NL
341 if ( C4::Context->preference('NorwegianPatronDBEnable') && C4::Context->preference('NorwegianPatronDBEnable') == 1 ) {
342 my $borrowersync = Koha::Database->new->schema->resultset('BorrowerSync')->find({
343 'synctype' => 'norwegianpatrondb',
344 'borrowernumber' => $data{'borrowernumber'}
346 # Do not set to "edited" if syncstatus is "new". We need to sync as new before
347 # we can sync as changed. And the "new sync" will pick up all changes since
348 # the patron was created anyway.
349 if ( $borrowersync->syncstatus ne 'new' && $borrowersync->syncstatus ne 'delete' ) {
350 $borrowersync->update( { 'syncstatus' => 'edited' } );
352 # Set the value of 'sync'
353 $borrowersync->update( { 'sync' => $data{'sync'} } );
354 # Try to do the live sync
355 Koha::NorwegianPatronDB::NLSync({ 'borrowernumber' => $data{'borrowernumber'} });
358 logaction("MEMBERS", "MODIFY", $data{'borrowernumber'}, "UPDATE (executed w/ arg: $data{'borrowernumber'})") if C4::Context->preference("BorrowersLog");
360 return $execute_success;
365 $borrowernumber = &AddMember(%borrower);
367 insert new borrower into table
369 (%borrower keys are database columns. Database columns could be
370 different in different versions. Please look into database for correct
373 Returns the borrowernumber upon success
375 Returns as undef upon any db error without further processing
382 my $dbh = C4::Context->dbh;
383 my $schema = Koha::Database->new()->schema;
385 my $category = Koha::Patron::Categories->find( $data{categorycode} );
387 Koha::Exceptions::BadParameter->throw(
388 error => 'Invalid parameter passed',
389 parameter => 'categorycode'
393 # trim whitespace from data which has some non-whitespace in it.
394 foreach my $field_name (keys(%data)) {
395 if ( defined $data{$field_name} && $data{$field_name} =~ /\S/ ) {
396 $data{$field_name} =~ s/^\s*|\s*$//g;
400 # generate a proper login if none provided
401 $data{'userid'} = Generate_Userid( $data{'borrowernumber'}, $data{'firstname'}, $data{'surname'} )
402 if ( $data{'userid'} eq '' || !Check_Userid( $data{'userid'} ) );
404 # add expiration date if it isn't already there
405 $data{dateexpiry} ||= $category->get_expiry_date;
407 # add enrollment date if it isn't already there
408 unless ( $data{'dateenrolled'} ) {
409 $data{'dateenrolled'} = output_pref( { dt => dt_from_string, dateonly => 1, dateformat => 'iso' } );
412 if ( C4::Context->preference("autoMemberNum") ) {
413 if ( not exists $data{cardnumber} or not defined $data{cardnumber} or $data{cardnumber} eq '' ) {
414 $data{cardnumber} = fixup_cardnumber( $data{cardnumber} );
419 $category->default_privacy() eq 'default' ? 1
420 : $category->default_privacy() eq 'never' ? 2
421 : $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 GetMemberAccountBalance
740 ($total_balance, $non_issue_balance, $other_charges) = &GetMemberAccountBalance($borrowernumber);
742 Calculates amount immediately owing by the patron - non-issue charges.
743 Based on GetMemberAccountRecords.
744 Charges exempt from non-issue are:
746 * Rent (rental) if RentalsInNoissuesCharge syspref is set to false
747 * Manual invoices if ManInvInNoissuesCharge syspref is set to false
751 sub GetMemberAccountBalance {
752 my ($borrowernumber) = @_;
754 # FIXME REMOVE And add a warning in the about page + update DB if length(MANUAL_INV) > 5
755 my $ACCOUNT_TYPE_LENGTH = 5; # this is plain ridiculous...
758 push @not_fines, 'Res' unless C4::Context->preference('HoldsInNoissuesCharge');
759 push @not_fines, 'Rent' unless C4::Context->preference('RentalsInNoissuesCharge');
760 unless ( C4::Context->preference('ManInvInNoissuesCharge') ) {
761 my $dbh = C4::Context->dbh;
762 push @not_fines, @{ $dbh->selectcol_arrayref(qq{SELECT authorised_value FROM authorised_values WHERE category = 'MANUAL_INV'}) };
764 @not_fines = map { substr($_, 0, $ACCOUNT_TYPE_LENGTH) } uniq (@not_fines);
766 my $patron = Koha::Patrons->find( $borrowernumber );
767 my $total = $patron->account->balance;
768 my $other_charges = Koha::Account::Lines->search({ borrowernumber => $patron->borrowernumber, accounttype => { -in => \@not_fines } }, {
769 select => [ { sum => 'amountoutstanding' } ],
770 as => ['total_other_charges'],
772 $other_charges = $other_charges->count ? $other_charges->next->get_column('total_other_charges') : 0;
774 return ( $total, $total - $other_charges, $other_charges);
777 sub checkcardnumber {
778 my ( $cardnumber, $borrowernumber ) = @_;
780 # If cardnumber is null, we assume they're allowed.
781 return 0 unless defined $cardnumber;
783 my $dbh = C4::Context->dbh;
784 my $query = "SELECT * FROM borrowers WHERE cardnumber=?";
785 $query .= " AND borrowernumber <> ?" if ($borrowernumber);
786 my $sth = $dbh->prepare($query);
789 ( $borrowernumber ? $borrowernumber : () )
792 return 1 if $sth->fetchrow_hashref;
794 my ( $min_length, $max_length ) = get_cardnumber_length();
796 if length $cardnumber > $max_length
797 or length $cardnumber < $min_length;
802 =head2 get_cardnumber_length
804 my ($min, $max) = C4::Members::get_cardnumber_length()
806 Returns the minimum and maximum length for patron cardnumbers as
807 determined by the CardnumberLength system preference, the
808 BorrowerMandatoryField system preference, and the width of the
813 sub get_cardnumber_length {
814 my $borrower = Koha::Schema->resultset('Borrower');
815 my $field_size = $borrower->result_source->column_info('cardnumber')->{size};
816 my ( $min, $max ) = ( 0, $field_size ); # borrowers.cardnumber is a nullable varchar(20)
817 $min = 1 if C4::Context->preference('BorrowerMandatoryField') =~ /cardnumber/;
818 if ( my $cardnumber_length = C4::Context->preference('CardnumberLength') ) {
819 # Is integer and length match
820 if ( $cardnumber_length =~ m|^\d+$| ) {
821 $min = $max = $cardnumber_length
822 if $cardnumber_length >= $min
823 and $cardnumber_length <= $max;
825 # Else assuming it is a range
826 elsif ( $cardnumber_length =~ m|(\d*),(\d*)| ) {
827 $min = $1 if $1 and $min < $1;
828 $max = $2 if $2 and $max > $2;
832 $min = $max if $min > $max;
833 return ( $min, $max );
836 =head2 GetBorrowersToExpunge
838 $borrowers = &GetBorrowersToExpunge(
839 not_borrowed_since => $not_borrowed_since,
840 expired_before => $expired_before,
841 category_code => $category_code,
842 patron_list_id => $patron_list_id,
843 branchcode => $branchcode
846 This function get all borrowers based on the given criteria.
850 sub GetBorrowersToExpunge {
853 my $filterdate = $params->{'not_borrowed_since'};
854 my $filterexpiry = $params->{'expired_before'};
855 my $filterlastseen = $params->{'last_seen'};
856 my $filtercategory = $params->{'category_code'};
857 my $filterbranch = $params->{'branchcode'} ||
858 ((C4::Context->preference('IndependentBranches')
859 && C4::Context->userenv
860 && !C4::Context->IsSuperLibrarian()
861 && C4::Context->userenv->{branch})
862 ? C4::Context->userenv->{branch}
864 my $filterpatronlist = $params->{'patron_list_id'};
866 my $dbh = C4::Context->dbh;
870 SELECT borrowers.borrowernumber,
871 MAX(old_issues.timestamp) AS latestissue,
872 MAX(issues.timestamp) AS currentissue
874 JOIN categories USING (categorycode)
878 WHERE guarantorid IS NOT NULL
880 ) as tmp ON borrowers.borrowernumber=tmp.guarantorid
881 LEFT JOIN old_issues USING (borrowernumber)
882 LEFT JOIN issues USING (borrowernumber)|;
883 if ( $filterpatronlist ){
884 $query .= q| LEFT JOIN patron_list_patrons USING (borrowernumber)|;
886 $query .= q| WHERE category_type <> 'S'
887 AND tmp.guarantorid IS NULL
890 if ( $filterbranch && $filterbranch ne "" ) {
891 $query.= " AND borrowers.branchcode = ? ";
892 push( @query_params, $filterbranch );
894 if ( $filterexpiry ) {
895 $query .= " AND dateexpiry < ? ";
896 push( @query_params, $filterexpiry );
898 if ( $filterlastseen ) {
899 $query .= ' AND lastseen < ? ';
900 push @query_params, $filterlastseen;
902 if ( $filtercategory ) {
903 $query .= " AND categorycode = ? ";
904 push( @query_params, $filtercategory );
906 if ( $filterpatronlist ){
907 $query.=" AND patron_list_id = ? ";
908 push( @query_params, $filterpatronlist );
910 $query .= " GROUP BY borrowers.borrowernumber";
912 ) xxx WHERE currentissue IS NULL|;
914 $query.=" AND ( latestissue < ? OR latestissue IS NULL ) ";
915 push @query_params,$filterdate;
918 warn $query if $debug;
920 my $sth = $dbh->prepare($query);
921 if (scalar(@query_params)>0){
922 $sth->execute(@query_params);
929 while ( my $data = $sth->fetchrow_hashref ) {
930 push @results, $data;
937 IssueSlip($branchcode, $borrowernumber, $quickslip)
939 Returns letter hash ( see C4::Letters::GetPreparedLetter )
941 $quickslip is boolean, to indicate whether we want a quick slip
943 IssueSlip populates ISSUESLIP and ISSUEQSLIP, and will make the following expansions:
979 NOTE: Not all table fields are available, pleasee see GetPendingIssues for a list of available fields.
984 my ($branch, $borrowernumber, $quickslip) = @_;
986 # FIXME Check callers before removing this statement
987 #return unless $borrowernumber;
989 my $patron = Koha::Patrons->find( $borrowernumber );
990 return unless $patron;
992 my @issues = @{ GetPendingIssues($borrowernumber) };
994 for my $issue (@issues) {
995 $issue->{date_due} = $issue->{date_due_sql};
997 my $today = output_pref({ dt => dt_from_string, dateformat => 'iso', dateonly => 1 });
998 if ( substr( $issue->{issuedate}, 0, 10 ) eq $today
999 or substr( $issue->{lastreneweddate}, 0, 10 ) eq $today ) {
1005 # Sort on timestamp then on issuedate then on issue_id
1006 # useful for tests and could be if modified in a batch
1008 $b->{timestamp} <=> $a->{timestamp}
1009 or $b->{issuedate} <=> $a->{issuedate}
1010 or $b->{issue_id} <=> $a->{issue_id}
1013 my ($letter_code, %repeat, %loops);
1015 $letter_code = 'ISSUEQSLIP';
1016 my @checkouts = map {
1019 'biblioitems' => $_,
1021 }, grep { $_->{'now'} } @issues;
1023 checkedout => \@checkouts, # History syntax
1026 issues => [ map { $_->{issues}{itemnumber} } @checkouts ], # TT syntax
1030 my @checkouts = map {
1033 'biblioitems' => $_,
1035 }, grep { !$_->{'overdue'} } @issues;
1036 my @overdues = map {
1039 'biblioitems' => $_,
1041 }, grep { $_->{'overdue'} } @issues;
1042 my $news = GetNewsToDisplay( "slip", $branch );
1044 $_->{'timestamp'} = $_->{'newdate'};
1047 $letter_code = 'ISSUESLIP';
1049 checkedout => \@checkouts,
1050 overdue => \@overdues,
1054 issues => [ map { $_->{issues}{itemnumber} } @checkouts ],
1055 overdues => [ map { $_->{issues}{itemnumber} } @overdues ],
1056 opac_news => [ map { $_->{opac_news}{idnew} } @news ],
1060 return C4::Letters::GetPreparedLetter (
1061 module => 'circulation',
1062 letter_code => $letter_code,
1063 branchcode => $branch,
1064 lang => $patron->lang,
1066 'branches' => $branch,
1067 'borrowers' => $borrowernumber,
1074 =head2 AddMember_Auto
1078 sub AddMember_Auto {
1079 my ( %borrower ) = @_;
1081 $borrower{'cardnumber'} ||= fixup_cardnumber();
1083 $borrower{'borrowernumber'} = AddMember(%borrower);
1085 return ( %borrower );
1088 =head2 AddMember_Opac
1092 sub AddMember_Opac {
1093 my ( %borrower ) = @_;
1095 $borrower{'categorycode'} //= C4::Context->preference('PatronSelfRegistrationDefaultCategory');
1096 if (not defined $borrower{'password'}){
1097 my $sr = new String::Random;
1098 $sr->{'A'} = [ 'A'..'Z', 'a'..'z' ];
1099 my $password = $sr->randpattern("AAAAAAAAAA");
1100 $borrower{'password'} = $password;
1103 %borrower = AddMember_Auto(%borrower);
1105 return ( $borrower{'borrowernumber'}, $borrower{'password'} );
1108 =head2 DeleteExpiredOpacRegistrations
1110 Delete accounts that haven't been upgraded from the 'temporary' category
1111 Returns the number of removed patrons
1115 sub DeleteExpiredOpacRegistrations {
1117 my $delay = C4::Context->preference('PatronSelfRegistrationExpireTemporaryAccountsDelay');
1118 my $category_code = C4::Context->preference('PatronSelfRegistrationDefaultCategory');
1120 return 0 if not $category_code or not defined $delay or $delay eq q||;
1123 SELECT borrowernumber
1125 WHERE categorycode = ? AND DATEDIFF( NOW(), dateenrolled ) > ? |;
1127 my $dbh = C4::Context->dbh;
1128 my $sth = $dbh->prepare($query);
1129 $sth->execute( $category_code, $delay );
1131 while ( my ($borrowernumber) = $sth->fetchrow_array() ) {
1132 Koha::Patrons->find($borrowernumber)->delete;
1138 =head2 DeleteUnverifiedOpacRegistrations
1140 Delete all unverified self registrations in borrower_modifications,
1141 older than the specified number of days.
1145 sub DeleteUnverifiedOpacRegistrations {
1147 my $dbh = C4::Context->dbh;
1149 DELETE FROM borrower_modifications
1150 WHERE borrowernumber = 0 AND DATEDIFF( NOW(), timestamp ) > ?|;
1151 my $cnt=$dbh->do($sql, undef, ($days) );
1152 return $cnt eq '0E0'? 0: $cnt;
1155 sub GetOverduesForPatron {
1156 my ( $borrowernumber ) = @_;
1160 FROM issues, items, biblio, biblioitems
1161 WHERE items.itemnumber=issues.itemnumber
1162 AND biblio.biblionumber = items.biblionumber
1163 AND biblio.biblionumber = biblioitems.biblionumber
1164 AND issues.borrowernumber = ?
1165 AND date_due < NOW()
1168 my $sth = C4::Context->dbh->prepare( $sql );
1169 $sth->execute( $borrowernumber );
1171 return $sth->fetchall_arrayref({});
1174 END { } # module clean-up code here (global destructor)