X-Git-Url: http://git.rot13.org/?a=blobdiff_plain;f=C4%2FMembers.pm;h=508618d45bce1397847dad0124c943af06c6daea;hb=9a32fe85c27196de96770eea8f7a34bd24d706a6;hp=74852abed795ab4563dcf85c3636b75231b1ca99;hpb=57d5b19dcaa8243138deb65ecac03859e4ac7684;p=koha.git diff --git a/C4/Members.pm b/C4/Members.pm index 74852abed7..508618d45b 100644 --- a/C4/Members.pm +++ b/C4/Members.pm @@ -1,5 +1,3 @@ -# -*- tab-width: 8 -*- - package C4::Members; # Copyright 2000-2003 Katipo Communications @@ -26,15 +24,12 @@ require Exporter; use C4::Context; use C4::Date; use Digest::MD5 qw(md5_base64); -use Date::Calc qw/Today/; -use C4::Biblio; -use C4::Stats; -use C4::Reserves2; -use C4::Koha; -use C4::Accounts2; -use C4::Circulation::Circ2; -use Date::Manip; -use vars qw($VERSION @ISA @EXPORT @EXPORT_OK); +use Date::Calc qw/Today Add_Delta_YM/; +use C4::Log; # logaction +use C4::Overdues; +use C4::Reserves; + +our ($VERSION,@ISA,@EXPORT,@EXPORT_OK); $VERSION = do { my @v = '$Revision$' =~ /\d+/g; shift(@v) . "." . join( "_", map { sprintf "%03d", $_ } @v ); }; @@ -56,169 +51,178 @@ This module contains routines for adding, modifying and deleting members/patrons =cut -#' - -@ISA = qw(Exporter); - -@EXPORT = qw( - -&allissues -&add_member_orgs -&borrdata -&borrdata2 -&borrdata3 -&BornameSearch -&borrissues -&borrowercard_active -&borrowercategories -&change_user_pass -&checkuniquemember -&calcexpirydate -&checkuserpassword - -ðnicitycategories -&fixEthnicity -&fixup_cardnumber -&findguarantees -&findguarantor -&fixupneu_cardnumber - -&getmember -&getMemberPhoto -&get_institutions -&getzipnamecity -&getidcity -&getguarantordata -&getcategorytype -&getboracctrecord -&getborrowercategory -&getborrowercategoryinfo -&get_age -&getpatroninformation -&GetBorrowersFromSurname -&GetBranchCodeFromBorrowers -&GetFlagsAndBranchFromBorrower -&GuarantornameSearch -&NewBorrowerNumber -&modmember -&newmember -&expand_sex_into_predicate - ); - - - -=head2 borrowercategories - - ($codes_arrayref, $labels_hashref) = &borrowercategories(); - -Looks up the different types of borrowers in the database. Returns two -elements: a reference-to-array, which lists the borrower category -codes, and a reference-to-hash, which maps the borrower category codes -to category descriptions. - -=cut -#' - -sub borrowercategories { - my $dbh = C4::Context->dbh; - my $sth=$dbh->prepare("Select categorycode,description from categories order by description"); - $sth->execute; - my %labels; - my @codes; - while (my $data=$sth->fetchrow_hashref){ - push @codes,$data->{'categorycode'}; - $labels{$data->{'categorycode'}}=$data->{'description'}; - } - $sth->finish; - return(\@codes,\%labels); -} - -=item BornameSearch - - ($count, $borrowers) = &BornameSearch($env, $searchstring, $type); +@ISA = qw(Exporter); + +#Get data +push @EXPORT, qw( + &SearchMember + &GetMemberDetails + &GetMember + + &GetGuarantees + + &GetMemberIssuesAndFines + &GetPendingIssues + &GetAllIssues + + &get_institutions + &getzipnamecity + &getidcity + + &GetAge + &GetCities + &GetRoadTypes + &GetRoadTypeDetails + &GetSortDetails + &GetTitles + + &GetMemberAccountRecords + &GetBorNotifyAcctRecord + + &GetborCatFromCatType + &GetBorrowercategory + + + &GetBorrowersWhoHaveNotBorrowedSince + &GetBorrowersWhoHaveNeverBorrowed + &GetBorrowersWithIssuesHistoryOlderThan + + &GetExpiryDate +); + +#Modify data +push @EXPORT, qw( + &ModMember + &changepassword +); + +#Delete data +push @EXPORT, qw( + &DelMember +); + +#Insert data +push @EXPORT, qw( + &AddMember + &add_member_orgs + &MoveMemberToDeleted + &ExtendMemberSubscriptionTo +); + +#Check data +push @EXPORT, qw( + &checkuniquemember + &checkuserpassword + &fixEthnicity + ðnicitycategories + &fixup_cardnumber + &checkcardnumber +); + +=item SearchMember + + ($count, $borrowers) = &SearchMember($searchstring, $type,$category_type); Looks up patrons (borrowers) by name. -C<$env> is ignored. - BUGFIX 499: C<$type> is now used to determine type of search. if $type is "simple", search is performed on the first letter of the surname only. +$category_type is used to get a specified type of user. +(mainly adults when creating a child.) + C<$searchstring> is a space-separated list of search terms. Each term must match the beginning a borrower's surname, first name, or other name. -C<&BornameSearch> returns a two-element list. C<$borrowers> is a +C<&SearchMember> returns a two-element list. C<$borrowers> is a reference-to-array; each element is a reference-to-hash, whose keys are the fields of the C table in the Koha database. C<$count> is the number of elements in C<$borrowers>. =cut + #' #used by member enquiries from the intranet #called by member.pl -sub BornameSearch { - my ($env,$searchstring,$orderby,$type)=@_; - my $dbh = C4::Context->dbh; - my $query = ""; my $count; - my @data; - my @bind=(); - - if($type eq "simple") # simple search for one letter only - { - $query="Select * from borrowers where surname like '$searchstring%' order by $orderby"; -# @bind=("$searchstring%"); - } - else # advanced search looking in surname, firstname and othernames - { -### Try to determine whether numeric like cardnumber - if ($searchstring+1>1) { - $query="Select * from borrowers where cardnumber like '$searchstring%' "; - - }else{ - - my @words=split / /,$searchstring; - foreach my $word(@words){ - $word="+".$word; - - } - $searchstring=join " ",@words; - - $query="Select * from borrowers where MATCH(surname,firstname,othernames) AGAINST('$searchstring' in boolean mode)"; +sub SearchMember { + my ($searchstring, $orderby, $type,$category_type ) = @_; + my $dbh = C4::Context->dbh; + my $query = ""; + my $count; + my @data; + my @bind = (); - } - $query=$query." order by $orderby"; - } + if ( $type eq "simple" ) # simple search for one letter only + { + $query = + "SELECT * FROM borrowers + LEFT JOIN categories ON borrowers.categorycode=categories.categorycode ". + ($category_type?" AND category_type = ".$dbh->quote($category_type):""). + " WHERE surname LIKE ? ORDER BY $orderby"; + @bind = ("$searchstring%"); + } + else # advanced search looking in surname, firstname and othernames + { + @data = split( ' ', $searchstring ); + $count = @data; + $query = "SELECT * FROM borrowers + LEFT JOIN categories ON borrowers.categorycode=categories.categorycode + WHERE ((surname LIKE ? OR surname LIKE ? + OR firstname LIKE ? OR firstname LIKE ? + OR othernames LIKE ? OR othernames LIKE ?) + ". + ($category_type?" AND category_type = ".$dbh->quote($category_type):""); + @bind = ( + "$data[0]%", "% $data[0]%", "$data[0]%", "% $data[0]%", + "$data[0]%", "% $data[0]%" + ); + for ( my $i = 1 ; $i < $count ; $i++ ) { + $query = $query . " AND (" . " surname LIKE ? OR surname LIKE ? + OR firstname LIKE ? OR firstname LIKE ? + OR othernames LIKE ? OR othernames LIKE ?)"; + push( @bind, + "$data[$i]%", "% $data[$i]%", "$data[$i]%", + "% $data[$i]%", "$data[$i]%", "% $data[$i]%" ); - my $sth=$dbh->prepare($query); -# warn "Q $orderby : $query"; - $sth->execute(); - my @results; - my $cnt=$sth->rows; - while (my $data=$sth->fetchrow_hashref){ - push(@results,$data); - } - # $sth->execute; - $sth->finish; - return ($cnt,\@results); + # FIXME - .= <prepare($query); + + # warn "Q $orderby : $query"; + $sth->execute(@bind); + my @results; + my $data = $sth->fetchall_arrayref({}); + + $sth->finish; + return ( scalar(@$data), $data ); } -=head2 getpatroninformation - ($borrower, $flags) = &getpatroninformation($env, $borrowernumber, $cardnumber); +=head2 GetMemberDetails + +($borrower, $flags) = &GetMemberDetails($borrowernumber, $cardnumber); + Looks up a patron and returns information about him or her. If -C<$borrowernumber> is true (nonzero), C<&getpatroninformation> looks +C<$borrowernumber> is true (nonzero), C<&GetMemberDetails> looks up the borrower by number; otherwise, it looks up the borrower by card number. -C<$env> is effectively ignored, but should be a reference-to-hash. + C<$borrower> is a reference-to-hash whose keys are the fields of the borrowers table in the Koha database. In addition, C<$borrower-E{flags}> is a hash giving more detailed information about the patron. Its keys act as flags : - if $borrower->{flags}->{LOST} { - # Patron's card was reported lost - } + if $borrower->{flags}->{LOST} { + # Patron's card was reported lost + } Each flag has a C key, giving a human-readable explanation of the flag. If the state of a flag means that the patron should not be @@ -231,7 +235,7 @@ The possible flags are: =over 4 -Shows the patron's credit or debt, if any. +=item Shows the patron's credit or debt, if any. =back @@ -239,7 +243,7 @@ Shows the patron's credit or debt, if any. =over 4 -(Gone, no address.) Set if the patron has left without giving a +=item (Gone, no address.) Set if the patron has left without giving a forwarding address. =back @@ -248,7 +252,7 @@ forwarding address. =over 4 -Set if the patron's card has been reported as lost. +=item Set if the patron's card has been reported as lost. =back @@ -256,7 +260,7 @@ Set if the patron's card has been reported as lost. =over 4 -Set if the patron has been debarred. +=item Set if the patron has been debarred. =back @@ -264,7 +268,7 @@ Set if the patron has been debarred. =over 4 -Any additional notes about the patron. +=item Any additional notes about the patron. =back @@ -272,7 +276,7 @@ Any additional notes about the patron. =over 4 -Set if the patron has overdue items. This flag has several keys: +=item Set if the patron has overdue items. This flag has several keys: C<$flags-E{ODUES}{itemlist}> is a reference-to-array listing the overdue items. Its elements are references-to-hash, each describing an @@ -288,7 +292,7 @@ the overdue items, one per line. =over 4 -Set if any items that the patron has reserved are available. +=item Set if any items that the patron has reserved are available. C<$flags-E{WAITING}{itemlist}> is a reference-to-array listing the available items. Each element is a reference-to-hash whose keys are @@ -296,353 +300,223 @@ fields from the reserves table of the Koha database. =back -=back - -=cut - -sub getpatroninformation { -# returns - my ($env, $borrowernumber,$cardnumber) = @_; - my $dbh = C4::Context->dbh; - my $query; - my $sth; - if ($borrowernumber) { - $sth = $dbh->prepare("select * from borrowers where borrowernumber=?"); - $sth->execute($borrowernumber); - } elsif ($cardnumber) { - $sth = $dbh->prepare("select * from borrowers where cardnumber=?"); - $sth->execute($cardnumber); - } else { - $env->{'apierror'} = "invalid borrower information passed to getpatroninformation subroutine"; - return(); - } - my $borrower = $sth->fetchrow_hashref; - my $amount = C4::Accounts2::checkaccount($env, $borrowernumber, $dbh); - $borrower->{'amountoutstanding'} = $amount; - my $flags = C4::Circulation::Circ2::patronflags($env, $borrower, $dbh); - my $accessflagshash; - - $sth=$dbh->prepare("select bit,flag from userflags"); - $sth->execute; - while (my ($bit, $flag) = $sth->fetchrow) { - if ($borrower->{'flags'} & 2**$bit) { - $accessflagshash->{$flag}=1; - } - } - $sth->finish; - $borrower->{'flags'}=$flags; - $borrower->{'authflags'} = $accessflagshash; - return ($borrower); #, $flags, $accessflagshash); -} - -=item getmember - - $borrower = &getmember($cardnumber, $borrowernumber); - -Looks up information about a patron (borrower) by either card number -or borrower number. If $borrowernumber is specified, C<&borrdata> -searches by borrower number; otherwise, it searches by card number. - -C<&getmember> returns a reference-to-hash whose keys are the fields of -the C table in the Koha database. - -=cut - -=head3 GetFlagsAndBranchFromBorrower - -=over 4 - -($flags, $homebranch) = GetFlagsAndBranchFromBorrower($loggedinuser); - -this function read on the database to get flags and homebranch for a user -given on input arg. - -return : -it returns the $flags & the homebranch in scalar context. - -=back - -=cut - - - -=item borrissues - - ($count, $issues) = &borrissues($borrowernumber); - -Looks up what the patron with the given borrowernumber has borrowed. - -C<&borrissues> returns a two-element array. C<$issues> is a -reference-to-array, where each element is a reference-to-hash; the -keys are the fields from the C, C, and C tables -in the Koha database. C<$count> is the number of elements in -C<$issues>. - -=cut -#' -sub borrissues { - my ($bornum)=@_; - my $dbh = C4::Context->dbh; - my $sth=$dbh->prepare("Select * from issues,biblio,items where borrowernumber=? - and items.itemnumber=issues.itemnumber - and items.biblionumber=biblio.biblionumber - and issues.returndate is NULL order by date_due"); - $sth->execute($bornum); - my @result; - while (my $data = $sth->fetchrow_hashref) { - push @result, $data; - } - $sth->finish; - return(scalar(@result), \@result); -} - -=item allissues - - ($count, $issues) = &allissues($borrowernumber, $sortkey, $limit); - -Looks up what the patron with the given borrowernumber has borrowed, -and sorts the results. - -C<$sortkey> is the name of a field on which to sort the results. This -should be the name of a field in the C, C, -C, or C table in the Koha database. - -C<$limit> is the maximum number of results to return. - -C<&allissues> returns a two-element array. C<$issues> is a -reference-to-array, where each element is a reference-to-hash; the -keys are the fields from the C, C, C, and -C tables of the Koha database. C<$count> is the number of -elements in C<$issues> - =cut -#' -sub allissues { - my ($bornum,$order,$limit)=@_; - #FIXME: sanity-check order and limit - my $dbh = C4::Context->dbh; - my $query="Select * from issues,biblio,items - where borrowernumber=? and - items.itemnumber=issues.itemnumber and - items.biblionumber=biblio.biblionumber order by $order"; - if ($limit !=0){ - $query.=" limit $limit"; - } - #print $query; - my $sth=$dbh->prepare($query); - $sth->execute($bornum); - my @result; - my $i=0; - while (my $data=$sth->fetchrow_hashref){ - $result[$i]=$data;; - $i++; - } - $sth->finish; - return($i,\@result); -} - -sub borrdata3 { -## NEU specific. used in Reserve section issues - my ($env,$bornum)=@_; - my $dbh = C4::Context->dbh; - my $query="Select count(*) from reserveissue as r where r.borrowernumber='$bornum' - and rettime is null"; - # print $query; - my $sth=$dbh->prepare($query); - $sth->execute; - my $data=$sth->fetchrow_hashref; - $sth->finish; - $sth=$dbh->prepare("Select count(*),timediff(now(), duetime ) as elapsed, hour(timediff(now(), duetime )) as hours, MINUTE(timediff(now(), duetime )) as min from - reserveissue as r where r.borrowernumber='$bornum' and rettime is null and duetime< now() group by r.borrowernumber"); - $sth->execute; - - my $data2=$sth->fetchrow_hashref; -my $resfine; -my $rescharge=C4::Context->preference('resmaterialcharge'); - if (!$rescharge){ - $rescharge=1; - } - if ($data2->{'elapsed'}>0){ - $resfine=($data2->{'hours'}+$data2->{'min'}/60)*$rescharge; - $resfine=sprintf ("%.1f",$resfine); - } - $sth->finish; - $sth=$dbh->prepare("Select sum(amountoutstanding) from accountlines where - borrowernumber='$bornum'"); - $sth->execute; - my $data3=$sth->fetchrow_hashref; - $sth->finish; +sub GetMemberDetails { + my ( $borrowernumber, $cardnumber ) = @_; + my $dbh = C4::Context->dbh; + my $query; + my $sth; + if ($borrowernumber) { + $sth = $dbh->prepare("select * from borrowers where borrowernumber=?"); + $sth->execute($borrowernumber); + } + elsif ($cardnumber) { + $sth = $dbh->prepare("select * from borrowers where cardnumber=?"); + $sth->execute($cardnumber); + } + else { + return undef; + } + my $borrower = $sth->fetchrow_hashref; + my ($amount) = GetMemberAccountRecords( $borrowernumber); + $borrower->{'amountoutstanding'} = $amount; + my $flags = patronflags( $borrower); + my $accessflagshash; + $sth = $dbh->prepare("select bit,flag from userflags"); + $sth->execute; + while ( my ( $bit, $flag ) = $sth->fetchrow ) { + if ( $borrower->{'flags'} && $borrower->{'flags'} & 2**$bit ) { + $accessflagshash->{$flag} = 1; + } + } + $sth->finish; + $borrower->{'flags'} = $flags; + $borrower->{'authflags'} = $accessflagshash; -return($data2->{'count(*)'},$data->{'count(*)'},$data3->{'sum(amountoutstanding)'},$resfine); + # find out how long the membership lasts + $sth = + $dbh->prepare( + "select enrolmentperiod from categories where categorycode = ?"); + $sth->execute( $borrower->{'categorycode'} ); + my $enrolment = $sth->fetchrow; + $borrower->{'enrolmentperiod'} = $enrolment; + return ($borrower); #, $flags, $accessflagshash); } -=item getboracctrecord - - ($count, $acctlines, $total) = &getboracctrecord($env, $borrowernumber); -Looks up accounting data for the patron with the given borrowernumber. - -C<$env> is ignored. - - -C<&getboracctrecord> returns a three-element array. C<$acctlines> is a -reference-to-array, where each element is a reference-to-hash; the -keys are the fields of the C table in the Koha database. -C<$count> is the number of elements in C<$acctlines>. C<$total> is the -total amount outstanding for all of the account lines. +=head2 patronflags + + Not exported + + NOTE!: If you change this function, be sure to update the POD for + &GetMemberDetails. + + $flags = &patronflags($patron); + + $flags->{CHARGES} + {message} Message showing patron's credit or debt + {noissues} Set if patron owes >$5.00 + {GNA} Set if patron gone w/o address + {message} "Borrower has no valid address" + {noissues} Set. + {LOST} Set if patron's card reported lost + {message} Message to this effect + {noissues} Set. + {DBARRED} Set is patron is debarred + {message} Message to this effect + {noissues} Set. + {NOTES} Set if patron has notes + {message} Notes about patron + {ODUES} Set if patron has overdue books + {message} "Yes" + {itemlist} ref-to-array: list of overdue books + {itemlisttext} Text list of overdue items + {WAITING} Set if there are items available that the + patron reserved + {message} Message to this effect + {itemlist} ref-to-array: list of available items =cut -#' -sub getboracctrecord { - my ($env,$params) = @_; - my $dbh = C4::Context->dbh; - my @acctlines; - my $numlines=0; - my $sth=$dbh->prepare("Select * from accountlines where -borrowernumber=? order by date desc,timestamp desc"); -# print $query; - $sth->execute($params->{'borrowernumber'}); - my $total=0; - while (my $data=$sth->fetchrow_hashref){ - $acctlines[$numlines] = $data; - $numlines++; - $total += $data->{'amountoutstanding'}; - } - $sth->finish; - return ($numlines,\@acctlines,$total); -} - -sub getborrowercategory{ - my ($catcode) = @_; - my $dbh = C4::Context->dbh; - my $sth = $dbh->prepare("SELECT description FROM categories WHERE categorycode = ?"); - $sth->execute($catcode); - my $description = $sth->fetchrow(); - $sth->finish(); - return $description; -} # sub getborrowercategory - -sub getborrowercategoryinfo{ - my ($catcode) = @_; - my $dbh = C4::Context->dbh; - my $sth = $dbh->prepare("SELECT * FROM categories WHERE categorycode = ?"); - $sth->execute($catcode); - my $category = $sth->fetchrow_hashref; - $sth->finish(); - return $category; -} # sub getborrowercategoryinfo - - -sub GetFlagsAndBranchFromBorrower { - my $loggedinuser = @_; - my $dbh = C4::Context->dbh; - my $query = " - SELECT flags, branchcode - FROM borrowers - WHERE borrowernumber = ? - "; - my $sth = $dbh->prepare($query); - $sth->execute($loggedinuser); - - return $sth->fetchrow; -} - -sub getmember { - my ( $cardnumber, $bornum ) = @_; - $cardnumber = uc $cardnumber; - my $dbh = C4::Context->dbh; - my $sth; - if ( $bornum eq '' ) { - $sth = $dbh->prepare("Select * from borrowers where cardnumber=?"); - $sth->execute($cardnumber); - } else { - $sth = $dbh->prepare("Select * from borrowers where borrowernumber=?"); - $sth->execute($bornum); +sub patronflags { + my %flags; + my ( $patroninformation) = @_; + my $dbh=C4::Context->dbh; + my ($amount) = GetMemberAccountRecords( $patroninformation->{'borrowernumber'}); + if ( $amount > 0 ) { + my %flaginfo; + my $noissuescharge = C4::Context->preference("noissuescharge"); + $flaginfo{'message'} = sprintf "Patron owes \$%.02f", $amount; + if ( $amount > $noissuescharge ) { + $flaginfo{'noissues'} = 1; + } + $flags{'CHARGES'} = \%flaginfo; } - my $data = $sth->fetchrow_hashref; - $sth->finish; - if ($data) { - return ($data); + elsif ( $amount < 0 ) { + my %flaginfo; + $flaginfo{'message'} = sprintf "Patron has credit of \$%.02f", -$amount; + $flags{'CHARGES'} = \%flaginfo; } - else { # try with firstname - if ($cardnumber) { - my $sth = - $dbh->prepare("select * from borrowers where firstname=?"); - $sth->execute($cardnumber); - my $data = $sth->fetchrow_hashref; - $sth->finish; - return ($data); + if ( $patroninformation->{'gonenoaddress'} + && $patroninformation->{'gonenoaddress'} == 1 ) + { + my %flaginfo; + $flaginfo{'message'} = 'Borrower has no valid address.'; + $flaginfo{'noissues'} = 1; + $flags{'GNA'} = \%flaginfo; + } + if ( $patroninformation->{'lost'} && $patroninformation->{'lost'} == 1 ) { + my %flaginfo; + $flaginfo{'message'} = 'Borrower\'s card reported lost.'; + $flaginfo{'noissues'} = 1; + $flags{'LOST'} = \%flaginfo; + } + if ( $patroninformation->{'debarred'} + && $patroninformation->{'debarred'} == 1 ) + { + my %flaginfo; + $flaginfo{'message'} = 'Borrower is Debarred.'; + $flaginfo{'noissues'} = 1; + $flags{'DBARRED'} = \%flaginfo; + } + if ( $patroninformation->{'borrowernotes'} + && $patroninformation->{'borrowernotes'} ) + { + my %flaginfo; + $flaginfo{'message'} = "$patroninformation->{'borrowernotes'}"; + $flags{'NOTES'} = \%flaginfo; + } + my ( $odues, $itemsoverdue ) = + checkoverdues( $patroninformation->{'borrowernumber'}, $dbh ); + if ( $odues > 0 ) { + my %flaginfo; + $flaginfo{'message'} = "Yes"; + $flaginfo{'itemlist'} = $itemsoverdue; + foreach ( sort { $a->{'date_due'} cmp $b->{'date_due'} } + @$itemsoverdue ) + { + $flaginfo{'itemlisttext'} .= + "$_->{'date_due'} $_->{'barcode'} $_->{'title'} \n"; } + $flags{'ODUES'} = \%flaginfo; + } + my @itemswaiting = GetReservesFromBorrowernumber( $patroninformation->{'borrowernumber'},'W' ); + my $nowaiting = scalar @itemswaiting; + if ( $nowaiting > 0 ) { + my %flaginfo; + $flaginfo{'message'} = "Reserved items available"; + $flaginfo{'itemlist'} = \@itemswaiting; + $flags{'WAITING'} = \%flaginfo; } - return undef; + return ( \%flags ); } -=item borrdata - $borrower = &borrdata($cardnumber, $borrowernumber); +=item GetMember + + $borrower = &GetMember($information, $type); Looks up information about a patron (borrower) by either card number -or borrower number. If $borrowernumber is specified, C<&borrdata> -searches by borrower number; otherwise, it searches by card number. +,firstname, or borrower number, depending on $type value. +If C<$type> == 'cardnumber', C<&GetBorrower> +searches by cardnumber then by firstname if not found in cardnumber; +otherwise, it searches by borrowernumber. -C<&borrdata> returns a reference-to-hash whose keys are the fields of +C<&GetBorrower> returns a reference-to-hash whose keys are the fields of the C table in the Koha database. =cut #' -sub borrdata { - my ( $cardnumber, $bornum ) = @_; - $cardnumber = uc $cardnumber; +sub GetMember { + my ( $information, $type ) = @_; my $dbh = C4::Context->dbh; my $sth; - if ( $bornum eq '' ) { - $sth = + if ($type eq 'cardnumber' || $type eq 'firstname'|| $type eq 'userid'|| $type eq 'borrowernumber'){ + $information = uc $information; + $sth = $dbh->prepare( -"Select borrowers.*,categories.category_type from borrowers left join categories on borrowers.categorycode=categories.categorycode where cardnumber=?" +"Select borrowers.*,categories.category_type,categories.description from borrowers left join categories on borrowers.categorycode=categories.categorycode where $type=?" ); - $sth->execute($cardnumber); + $sth->execute($information); } else { $sth = $dbh->prepare( -"Select borrowers.*,categories.category_type from borrowers left join categories on borrowers.categorycode=categories.categorycode where borrowernumber=?" +"Select borrowers.*,categories.category_type, categories.description from borrowers left join categories on borrowers.categorycode=categories.categorycode where borrowernumber=?" ); - $sth->execute($bornum); + $sth->execute($information); } my $data = $sth->fetchrow_hashref; -# warn "DATA" . $data->{category_type}; + $sth->finish; if ($data) { return ($data); } - else { # try with firstname - if ($cardnumber) { - my $sth = + elsif ($type eq 'cardnumber' ||$type eq 'firstname') { # try with firstname + my $sth = $dbh->prepare( -"Select borrowers.*,categories.category_type from borrowers left join categories on borrowers.categorycode=categories.categorycode where firstname=?" - ); - $sth->execute($cardnumber); +"Select borrowers.*,categories.category_type,categories.description from borrowers left join categories on borrowers.categorycode=categories.categorycode where firstname like ?" + ); + $sth->execute($information); my $data = $sth->fetchrow_hashref; $sth->finish; return ($data); - } } - return undef; + else { + return undef; + } } -=item borrdata2 +=item GetMemberIssuesAndFines - ($borrowed, $due, $fine) = &borrdata2($env, $borrowernumber); + ($borrowed, $due, $fine) = &GetMemberIssuesAndFines($borrowernumber); Returns aggregate data about items borrowed by the patron with the given borrowernumber. -C<$env> is ignored. - -C<&borrdata2> returns a three-element array. C<$borrowed> is the +C<&GetMemberIssuesAndFines> returns a three-element array. C<$borrowed> is the number of books the patron currently has borrowed. C<$due> is the number of overdue items the patron currently has borrowed. C<$fine> is the total fine currently due by the borrower. @@ -650,10 +524,11 @@ the total fine currently due by the borrower. =cut #' -sub borrdata2 { - my ( $env, $bornum ) = @_; +sub GetMemberIssuesAndFines { + my ( $borrowernumber ) = @_; my $dbh = C4::Context->dbh; - my $query = "Select count(*) from issues where borrowernumber='$bornum' and + my $query = + "Select count(*) from issues where borrowernumber='$borrowernumber' and returndate is NULL"; # print $query; @@ -663,14 +538,14 @@ sub borrdata2 { $sth->finish; $sth = $dbh->prepare( "Select count(*) from issues where - borrowernumber='$bornum' and date_due < now() and returndate is NULL" + borrowernumber='$borrowernumber' and date_due < now() and returndate is NULL" ); $sth->execute; my $data2 = $sth->fetchrow_hashref; $sth->finish; $sth = $dbh->prepare( "Select sum(amountoutstanding) from accountlines where - borrowernumber='$bornum'" + borrowernumber='$borrowernumber'" ); $sth->execute; my $data3 = $sth->fetchrow_hashref; @@ -680,213 +555,279 @@ sub borrdata2 { $data3->{'sum(amountoutstanding)'} ); } -sub modmember { - my (%data) = @_; - my $dbh = C4::Context->dbh; - $data{'dateofbirth'}=format_date_in_iso($data{'dateofbirth'}); - - - $data{'joining'}=format_date_in_iso($data{'joining'}); - - if ($data{'expiry'} eq '') { - - my $sth = $dbh->prepare("select enrolmentperiod from categories where categorycode=?"); - $sth->execute($data{'categorycode'}); - my ($enrolmentperiod) = $sth->fetchrow; - $enrolmentperiod = 12 unless ($enrolmentperiod); - $data{'expiry'} = &DateCalc($data{'joining'},"$enrolmentperiod years"); - } - $data{'expiry'}=format_date_in_iso($data{'expiry'}); - my $query= "UPDATE borrowers SET - cardnumber = '$data{'cardnumber'}' , - surname = '$data{'surname'}' , - firstname = '$data{'firstname'}' , - title = '$data{'title'}' , - initials = '$data{'initials'}' , - dateofbirth = '$data{'dateofbirth'}' , - sex = '$data{'sex'}' , - streetaddress = '$data{'streetaddress'}' , - streetcity = '$data{'streetcity'}' , - zipcode = '$data{'zipcode'}' , - phoneday = '$data{'phoneday'}' , - physstreet = '$data{'physstreet'}' , - city = '$data{'city'}' , - homezipcode = '$data{'homezipcode'}' , - phone = '$data{'phone'}' , - emailaddress = '$data{'emailaddress'}' , - faxnumber = '$data{'faxnumber'}' , - textmessaging = '$data{'textmessaging'}' , - categorycode = '$data{'categorycode'}' , - branchcode = '$data{'branchcode'}' , - borrowernotes = '$data{'borrowernotes'}' , - ethnicity = '$data{'ethnicity'}' , - ethnotes = '$data{'ethnotes'}' , - expiry = '$data{'expiry'}' , - dateenrolled = '$data{'joining'}' , - sort1 = '$data{'sort1'}' , - sort2 = '$data{'sort2'}' , - debarred = '$data{'debarred'}' , - lost = '$data{'lost'}' , - gonenoaddress = '$data{'gna'}' - WHERE borrowernumber = $data{'borrowernumber'}"; - my $sth = $dbh->prepare($query); - $sth->execute; - $sth->finish; - # ok if its an adult (type) it may have borrowers that depend on it as a guarantor - # so when we update information for an adult we should check for guarantees and update the relevant part - # of their records, ie addresses and phone numbers - if ($data{'categorycode'} eq 'A' || $data{'categorycode'} eq 'W'){ - # is adult check guarantees; - updateguarantees(%data); - } -} - -sub newmember { - my (%data) = @_; - my $dbh = C4::Context->dbh; - $data{'dateofbirth'}=format_date_in_iso($data{'dateofbirth'}); - $data{'joining'} = &ParseDate("today") unless $data{'joining'}; - $data{'joining'}=format_date_in_iso($data{'joining'}); - # if expirydate is not set, calculate it from borrower category subscription duration - unless ($data{'expiry'}) { - my $sth = $dbh->prepare("select enrolmentperiod from categories where categorycode=?"); - $sth->execute($data{'categorycode'}); - my ($enrolmentperiod) = $sth->fetchrow; - $enrolmentperiod = 12 unless ($enrolmentperiod); - $data{'expiry'} = &DateCalc($data{'joining'},"$enrolmentperiod years"); - } - $data{'expiry'}=format_date_in_iso($data{'expiry'}); - my $query= "INSERT INTO borrowers ( - cardnumber, - surname, - firstname, - title, - initials, - dateofbirth, - sex, - streetaddress, - streetcity, - zipcode, - phoneday, - physstreet, - city, - homezipcode, - phone, - emailaddress, - faxnumber, - textmessaging, - categorycode, - branchcode, - borrowernotes, - ethnicity, - ethnotes, - expiry, - dateenrolled, - sort1, - sort2 - ) - VALUES ( - '$data{'cardnumber'}', - '$data{'surname'}', - '$data{'firstname'}', - '$data{'title'}', - '$data{'initials'}', - '$data{'dateofbirth'}', - '$data{'sex'}', - - '$data{'streetaddress'}', - '$data{'streetcity'}', - '$data{'zipcode'}', - '$data{'phoneday'}', - - '$data{'physstreet'}', - '$data{'city'}', - '$data{'homezipcode'}', - '$data{'phone'}', - - '$data{'emailaddress'}', - '$data{'faxnumber'}', - '$data{'textmessaging'}', - - '$data{'categorycode'}', - '$data{'branchcode'}', - '$data{'borrowernotes'}', - '$data{'ethnicity'}', - '$data{'ethnotes'}', - '$data{'expiry'}', - '$data{'joining'}', - '$data{'sort1'}', - '$data{'sort2'}' - )"; - my $sth=$dbh->prepare($query); - $sth->execute; - $sth->finish; - $data{'bornum'} =$dbh->{'mysql_insertid'}; - return $data{'bornum'}; -} +=head2 -sub calcexpirydate { - my ( $categorycode, $dateenrolled ) = @_; - my $dbh = C4::Context->dbh; - my $sth = - $dbh->prepare( - "select enrolmentperiod from categories where categorycode=?"); - $sth->execute($categorycode); - my ($enrolmentperiod) = $sth->fetchrow; - $enrolmentperiod = 12 unless ($enrolmentperiod); - return format_date_in_iso( - &DateCalc( $dateenrolled, "$enrolmentperiod months" ) ); -} +=item ModMember -=head2 checkuserpassword (OUEST-PROVENCE) + &ModMember($borrowernumber); -check for the password and login are not used -return the number of record -0=> NOT USED 1=> USED +Modify borrower's data =cut -sub checkuserpassword { - my ( $borrowernumber, $userid, $password ) = @_; - $password = md5_base64($password); - my $dbh = C4::Context->dbh; - my $sth = - $dbh->prepare( -"Select count(*) from borrowers where borrowernumber !=? and userid =? and password=? " - ); - $sth->execute( $borrowernumber, $userid, $password ); - my $number_rows = $sth->fetchrow; - return $number_rows; - -} -sub getmemberfromuserid { - my ($userid) = @_; - my $dbh = C4::Context->dbh; - my $sth = $dbh->prepare("select * from borrowers where userid=?"); - $sth->execute($userid); - return $sth->fetchrow_hashref; -} -sub updateguarantees { +#' +sub ModMember { my (%data) = @_; my $dbh = C4::Context->dbh; - my ( $count, $guarantees ) = findguarantees( $data{'borrowernumber'} ); - for ( my $i = 0 ; $i < $count ; $i++ ) { + $data{'dateofbirth'} = format_date_in_iso( $data{'dateofbirth'} ); + $data{'dateexpiry'} = format_date_in_iso( $data{'dateexpiry'} ); + $data{'dateenrolled'} = format_date_in_iso( $data{'dateenrolled'} ); - # FIXME - # It looks like the $i is only being returned to handle walking through - # the array, which is probably better done as a foreach loop. - # - my $guaquery = -"update borrowers set streetaddress='$data{'address'}',faxnumber='$data{'faxnumber'}', - streetcity='$data{'streetcity'}',phoneday='$data{'phoneday'}',city='$data{'city'}',area='$data{'area'}',phone='$data{'phone'}' - ,streetaddress='$data{'address'}' - where borrowernumber='$guarantees->[$i]->{'borrowernumber'}'"; - my $sth3 = $dbh->prepare($guaquery); - $sth3->execute; - $sth3->finish; + # warn "num user".$data{'borrowernumber'}; + my $query; + my $sth; + $data{'userid'} = '' if ( $data{'password'} eq '' ); + + # test to know if u must update or not the borrower password + if ( $data{'password'} eq '****' ) { + + $query = "UPDATE borrowers SET + cardnumber = ?,surname = ?,firstname = ?,title = ?,othernames = ?,initials = ?, + streetnumber = ?,streettype = ?,address = ?,address2 = ?,city = ?,zipcode = ?, + email = ?,phone = ?,mobile = ?,fax = ?,emailpro = ?,phonepro = ?,B_streetnumber = ?, + B_streettype = ?,B_address = ?,B_city = ?,B_zipcode = ?,B_email = ?,B_phone = ?,dateofbirth = ?,branchcode = ?, + categorycode = ?,dateenrolled = ?,dateexpiry = ?,gonenoaddress = ?,lost = ?,debarred = ?,contactname = ?, + contactfirstname = ?,contacttitle = ?,guarantorid = ?,borrowernotes = ?,relationship = ?,ethnicity = ?, + ethnotes = ?,sex = ?,userid = ?,opacnote = ?,contactnote = ?,sort1 = ?,sort2 = ? + WHERE borrowernumber=$data{'borrowernumber'}"; + $sth = $dbh->prepare($query); + $sth->execute( + $data{'cardnumber'}, $data{'surname'}, + $data{'firstname'}, $data{'title'}, + $data{'othernames'}, $data{'initials'}, + $data{'streetnumber'}, $data{'streettype'}, + $data{'address'}, $data{'address2'}, + $data{'city'}, $data{'zipcode'}, + $data{'email'}, $data{'phone'}, + $data{'mobile'}, $data{'fax'}, + $data{'emailpro'}, $data{'phonepro'}, + $data{'B_streetnumber'}, $data{'B_streettype'}, + $data{'B_address'}, $data{'B_city'}, + $data{'B_zipcode'}, $data{'B_email'}, + $data{'B_phone'}, $data{'dateofbirth'}, + $data{'branchcode'}, $data{'categorycode'}, + $data{'dateenrolled'}, $data{'dateexpiry'}, + $data{'gonenoaddress'}, $data{'lost'}, + $data{'debarred'}, $data{'contactname'}, + $data{'contactfirstname'}, $data{'contacttitle'}, + $data{'guarantorid'}, $data{'borrowernotes'}, + $data{'relationship'}, $data{'ethnicity'}, + $data{'ethnotes'}, $data{'sex'}, + $data{'userid'}, $data{'opacnote'}, + $data{'contactnote'}, $data{'sort1'}, + $data{'sort2'} + ); } -} -################################################################################ + else { + + ( $data{'password'} = md5_base64( $data{'password'} ) ) + if ( $data{'password'} ne '' ); + $query = "UPDATE borrowers SET + cardnumber = ?,surname = ?,firstname = ?,title = ?,othernames = ?,initials = ?, + streetnumber = ?,streettype = ?,address = ?,address2 = ?,city = ?,zipcode = ?, + email = ?,phone = ?,mobile = ?,fax = ?,emailpro = ?,phonepro = ?,B_streetnumber = ?, + B_streettype = ?,B_address = ?,B_city = ?,B_zipcode = ?,B_email = ?,B_phone = ?,dateofbirth = ?,branchcode = ?, + categorycode = ?,dateenrolled = ?,dateexpiry = ?,gonenoaddress = ?,lost = ?,debarred = ?,contactname = ?, + contactfirstname = ?,contacttitle = ?,guarantorid = ?,borrowernotes = ?,relationship = ?,ethnicity = ?, + ethnotes = ?,sex = ?,password = ?,userid = ?,opacnote = ?,contactnote = ?,sort1 = ?,sort2 = ? + WHERE borrowernumber=$data{'borrowernumber'}"; + $sth = $dbh->prepare($query); + $sth->execute( + $data{'cardnumber'}, $data{'surname'}, + $data{'firstname'}, $data{'title'}, + $data{'othernames'}, $data{'initials'}, + $data{'streetnumber'}, $data{'streettype'}, + $data{'address'}, $data{'address2'}, + $data{'city'}, $data{'zipcode'}, + $data{'email'}, $data{'phone'}, + $data{'mobile'}, $data{'fax'}, + $data{'emailpro'}, $data{'phonepro'}, + $data{'B_streetnumber'}, $data{'B_streettype'}, + $data{'B_address'}, $data{'B_city'}, + $data{'B_zipcode'}, $data{'B_email'}, + $data{'B_phone'}, $data{'dateofbirth'}, + $data{'branchcode'}, $data{'categorycode'}, + $data{'dateenrolled'}, $data{'dateexpiry'}, + $data{'gonenoaddress'}, $data{'lost'}, + $data{'debarred'}, $data{'contactname'}, + $data{'contactfirstname'}, $data{'contacttitle'}, + $data{'guarantorid'}, $data{'borrowernotes'}, + $data{'relationship'}, $data{'ethnicity'}, + $data{'ethnotes'}, $data{'sex'}, + $data{'password'}, $data{'userid'}, + $data{'opacnote'}, $data{'contactnote'}, + $data{'sort1'}, $data{'sort2'} + ); + } + $sth->finish; + +# ok if its an adult (type) it may have borrowers that depend on it as a guarantor +# so when we update information for an adult we should check for guarantees and update the relevant part +# of their records, ie addresses and phone numbers + my $borrowercategory= GetBorrowercategory( $data{'category_type'} ); + if ( $borrowercategory->{'category_type'} eq 'A' ) { + # is adult check guarantees; + UpdateGuarantees(%data); + + } + &logaction(C4::Context->userenv->{'number'},"MEMBERS","MODIFY",$data{'borrowernumber'},"") + if C4::Context->preference("BorrowersLog"); +} + +=head2 + +=item AddMember + + $borrowernumber = &AddMember(%borrower); + +insert new borrower into table +Returns the borrowernumber + +=cut + +#' +sub AddMember { + my (%data) = @_; + my $dbh = C4::Context->dbh; + $data{'userid'} = '' unless $data{'password'}; + $data{'password'} = md5_base64( $data{'password'} ) if $data{'password'}; + $data{'dateofbirth'} = format_date_in_iso( $data{'dateofbirth'} ); + $data{'dateenrolled'} = format_date_in_iso( $data{'dateenrolled'} ); + $data{'dateexpiry'} = format_date_in_iso( $data{'dateexpiry'} ); + my $query = + "insert into borrowers set cardnumber=" + . $dbh->quote( $data{'cardnumber'} ) + . ",surname=" + . $dbh->quote( $data{'surname'} ) + . ",firstname=" + . $dbh->quote( $data{'firstname'} ) + . ",title=" + . $dbh->quote( $data{'title'} ) + . ",othernames=" + . $dbh->quote( $data{'othernames'} ) + . ",initials=" + . $dbh->quote( $data{'initials'} ) + . ",streetnumber=" + . $dbh->quote( $data{'streetnumber'} ) + . ",streettype=" + . $dbh->quote( $data{'streettype'} ) + . ",address=" + . $dbh->quote( $data{'address'} ) + . ",address2=" + . $dbh->quote( $data{'address2'} ) + . ",zipcode=" + . $dbh->quote( $data{'zipcode'} ) + . ",city=" + . $dbh->quote( $data{'city'} ) + . ",phone=" + . $dbh->quote( $data{'phone'} ) + . ",email=" + . $dbh->quote( $data{'email'} ) + . ",mobile=" + . $dbh->quote( $data{'mobile'} ) + . ",phonepro=" + . $dbh->quote( $data{'phonepro'} ) + . ",opacnote=" + . $dbh->quote( $data{'opacnote'} ) + . ",guarantorid=" + . $dbh->quote( $data{'guarantorid'} ) + . ",dateofbirth=" + . $dbh->quote( $data{'dateofbirth'} ) + . ",branchcode=" + . $dbh->quote( $data{'branchcode'} ) + . ",categorycode=" + . $dbh->quote( $data{'categorycode'} ) + . ",dateenrolled=" + . $dbh->quote( $data{'dateenrolled'} ) + . ",contactname=" + . $dbh->quote( $data{'contactname'} ) + . ",borrowernotes=" + . $dbh->quote( $data{'borrowernotes'} ) + . ",dateexpiry=" + . $dbh->quote( $data{'dateexpiry'} ) + . ",contactnote=" + . $dbh->quote( $data{'contactnote'} ) + . ",B_address=" + . $dbh->quote( $data{'B_address'} ) + . ",B_zipcode=" + . $dbh->quote( $data{'B_zipcode'} ) + . ",B_city=" + . $dbh->quote( $data{'B_city'} ) + . ",B_phone=" + . $dbh->quote( $data{'B_phone'} ) + . ",B_email=" + . $dbh->quote( $data{'B_email'}, ) + . ",password=" + . $dbh->quote( $data{'password'} ) + . ",userid=" + . $dbh->quote( $data{'userid'} ) + . ",sort1=" + . $dbh->quote( $data{'sort1'} ) + . ",sort2=" + . $dbh->quote( $data{'sort2'} ) + . ",contacttitle=" + . $dbh->quote( $data{'contacttitle'} ) + . ",emailpro=" + . $dbh->quote( $data{'emailpro'} ) + . ",contactfirstname=" + . $dbh->quote( $data{'contactfirstname'} ) . ",sex=" + . $dbh->quote( $data{'sex'} ) . ",fax=" + . $dbh->quote( $data{'fax'} ) + . ",relationship=" + . $dbh->quote( $data{'relationship'} ) + . ",B_streetnumber=" + . $dbh->quote( $data{'B_streetnumber'} ) + . ",B_streettype=" + . $dbh->quote( $data{'B_streettype'} ) + . ",gonenoaddress=" + . $dbh->quote( $data{'gonenoaddress'} ) + . ",lost=" + . $dbh->quote( $data{'lost'} ) + . ",debarred=" + . $dbh->quote( $data{'debarred'} ) + . ",ethnicity=" + . $dbh->quote( $data{'ethnicity'} ) + . ",ethnotes=" + . $dbh->quote( $data{'ethnotes'} ); + + my $sth = $dbh->prepare($query); + $sth->execute; + $sth->finish; + $data{'borrowernumber'} = $dbh->{'mysql_insertid'}; + + &logaction(C4::Context->userenv->{'number'},"MEMBERS","CREATE",$data{'borrowernumber'},"") + if C4::Context->preference("BorrowersLog"); + + return $data{'borrowernumber'}; +} + +sub changepassword { + my ( $uid, $member, $digest ) = @_; + my $dbh = C4::Context->dbh; + +#Make sure the userid chosen is unique and not theirs if non-empty. If it is not, +#Then we need to tell the user and have them create a new one. + my $sth = + $dbh->prepare( + "select * from borrowers where userid=? and borrowernumber != ?"); + $sth->execute( $uid, $member ); + if ( ( $uid ne '' ) && ( $sth->fetchrow ) ) { + return 0; + } + else { + #Everything is good so we can update the information. + $sth = + $dbh->prepare( + "update borrowers set userid=?, password=? where borrowernumber=?"); + $sth->execute( $uid, $digest, $member ); + return 1; + } + + &logaction(C4::Context->userenv->{'number'},"MEMBERS","CHANGE PASS",$member,"") + if C4::Context->preference("BorrowersLog"); +} + + =item fixup_cardnumber @@ -902,7 +843,7 @@ sub fixup_cardnumber ($) { my ($cardnumber) = @_; my $autonumber_members = C4::Context->boolean_preference('autoMemberNum'); $autonumber_members = 0 unless defined $autonumber_members; -my $rem; + # Find out whether member numbers should be generated # automatically. Should be either "1" or something else. # Defaults to "0", which is interpreted as "no". @@ -927,15 +868,15 @@ my $rem; my $data = $sth->fetchrow_hashref; $cardnumber = $data->{'max(substring(borrowers.cardnumber,2,7))'}; $sth->finish; - - if ( !$cardnumber ) { # If DB has no values, + if ( !$cardnumber ) { # If DB has no values, $cardnumber = 1000000; # start at 1000000 - } else { + } + else { $cardnumber += 1; - } + } my $sum = 0; - for ( my $i = 0 ; $i < 8 ; $i += 1 ) { + for ( my $i = 0 ; $i < 8 ; $i += 1 ) { # read weightings, left to right, 1 char at a time my $temp1 = $weightings[$i]; @@ -945,9 +886,9 @@ my $rem; # mult each char 1-7 by its corresponding weighting $sum += $temp1 * $temp2; - } + } - $rem = ( $sum % 11 ); + my $rem = ( $sum % 11 ); $rem = 'X' if $rem == 10; $cardnumber = "V$cardnumber$rem"; @@ -964,253 +905,285 @@ my $rem; $sth->execute; - $cardnumber="V$cardnumber$rem"; + my ($result) = $sth->fetchrow; + $sth->finish; + $cardnumber = $result + 1; + } } return $cardnumber; } -} -sub fixupneu_cardnumber{ - my($cardnumber,$categorycode) = @_; - my $autonumber_members = C4::Context->boolean_preference('autoMemberNum'); - $autonumber_members = 0 unless defined $autonumber_members; - # Find out whether member numbers should be generated - # automatically. Should be either "1" or something else. - # Defaults to "0", which is interpreted as "no". -my $dbh = C4::Context->dbh; -my $sth; - if (! $cardnumber && $autonumber_members && $categorycode) { - if ($categorycode eq "A" || $categorycode eq "W" ){ - $sth=$dbh->prepare("select max(borrowers.cardnumber) from borrowers where borrowers.cardnumber like '5%' "); - }elsif ($categorycode eq "L"){ - $sth=$dbh->prepare("select max(borrowers.cardnumber) from borrowers where borrowers.cardnumber like '10%' "); - }elsif ($categorycode eq "F" || $categorycode eq "E") { - $sth=$dbh->prepare("select max(borrowers.cardnumber) from borrowers where borrowers.cardnumber like '30%' "); - }elsif ($categorycode eq "N"){ - $sth=$dbh->prepare("select max(borrowers.cardnumber) from borrowers where borrowers.cardnumber like '40%' "); - }elsif ($categorycode eq "C"){ - $sth=$dbh->prepare("select max(borrowers.cardnumber) from borrowers where borrowers.cardnumber like '80%' "); - - }else{ - $sth=$dbh->prepare("select max(borrowers.cardnumber) from borrowers where borrowers.cardnumber like '6%' "); - } - $sth->execute; - - my $data=$sth->fetchrow_hashref; - $cardnumber=$data->{'max(borrowers.cardnumber)'}; - $sth->finish; - - # purpose: generate checksum'd member numbers. - # We'll assume we just got the max value of digits 2-8 of member #'s - # from the database and our job is to increment that by one, - # determine the 1st and 9th digits and return the full string. - - if (! $cardnumber) { # If DB has no values, - if ($categorycode eq "A" || $categorycode eq "W" ){ $cardnumber = 5000000;} - elsif ($categorycode eq "L"){ $cardnumber = 1000000;} - elsif ($categorycode eq "F"){ $cardnumber = 3000000;} - elsif ($categorycode eq "C"){ $cardnumber = 8000000;} - else{$cardnumber = 6000000;} - # start at 1000000 or 3000000 or 5000000 - } else { - $cardnumber += 1; - } - - } - return $cardnumber; -} +=head2 GetGuarantees -=item GuarantornameSearch + ($num_children, $children_arrayref) = &GetGuarantees($parent_borrno); + $child0_cardno = $children_arrayref->[0]{"cardnumber"}; + $child0_borrno = $children_arrayref->[0]{"borrowernumber"}; - ($count, $borrowers) = &GuarantornameSearch($env, $searchstring, $type); +C<&GetGuarantees> takes a borrower number (e.g., that of a patron +with children) and looks up the borrowers who are guaranteed by that +borrower (i.e., the patron's children). -Looks up guarantor by name. +C<&GetGuarantees> returns two values: an integer giving the number of +borrowers guaranteed by C<$parent_borrno>, and a reference to an array +of references to hash, which gives the actual results. -C<$env> is ignored. +=cut -BUGFIX 499: C<$type> is now used to determine type of search. -if $type is "simple", search is performed on the first letter of the -surname only. +#' +sub GetGuarantees { + my ($borrowernumber) = @_; + my $dbh = C4::Context->dbh; + my $sth = + $dbh->prepare( +"select cardnumber,borrowernumber, firstname, surname from borrowers where guarantorid=?" + ); + $sth->execute($borrowernumber); -C<$searchstring> is a space-separated list of search terms. Each term -must match the beginning a borrower's surname, first name, or other -name. + my @dat; + my $data = $sth->fetchall_arrayref({}); + $sth->finish; + return ( scalar(@$data), $data ); +} -C<&GuarantornameSearch> returns a two-element list. C<$borrowers> is a -reference-to-array; each element is a reference-to-hash, whose keys -are the fields of the C table in the Koha database. -C<$count> is the number of elements in C<$borrowers>. +=head2 UpdateGuarantees + + &UpdateGuarantees($parent_borrno); + -return all info from guarantor =>only category_type A +C<&UpdateGuarantees> borrower data for an adulte and updates all the guarantees +with the modified information =cut #' -#used by member enquiries from the intranet -#called by guarantor_search.pl -sub GuarantornameSearch { - my ( $env, $searchstring, $orderby, $type ) = @_; - my $dbh = C4::Context->dbh; - my $query = ""; - my $count; - my @data; - my @bind = (); +sub UpdateGuarantees { + my (%data) = @_; + my $dbh = C4::Context->dbh; + my ( $count, $guarantees ) = GetGuarantees( $data{'borrowernumber'} ); + for ( my $i = 0 ; $i < $count ; $i++ ) { - if ( $type eq "simple" ) # simple search for one letter only - { - $query = -"Select * from borrowers,categories where borrowers.categorycode=categories.categorycode and category_type='A' and surname like ? order by $orderby"; - @bind = ("$searchstring%"); + # FIXME + # It looks like the $i is only being returned to handle walking through + # the array, which is probably better done as a foreach loop. + # + my $guaquery = qq|UPDATE borrowers + SET address='$data{'address'}',fax='$data{'fax'}', + B_city='$data{'B_city'}',mobile='$data{'mobile'}',city='$data{'city'}',phone='$data{'phone'}' + WHERE borrowernumber='$guarantees->[$i]->{'borrowernumber'}' + |; + my $sth3 = $dbh->prepare($guaquery); + $sth3->execute; + $sth3->finish; } - else # advanced search looking in surname, firstname and othernames - { - @data = split( ' ', $searchstring ); - $count = @data; - $query = "Select * from borrowers,categories - where ((surname like ? or surname like ? - or firstname like ? or firstname like ? - or othernames like ? or othernames like ?) and borrowers.categorycode=categories.categorycode and category_type='A' - "; - @bind = ( - "$data[0]%", "% $data[0]%", "$data[0]%", "% $data[0]%", - "$data[0]%", "% $data[0]%" - ); - for ( my $i = 1 ; $i < $count ; $i++ ) { - $query = $query . " and (" . " surname like ? or surname like ? - or firstname like ? or firstname like ? - or othernames like ? or othernames like ?)"; - push( @bind, - "$data[$i]%", "% $data[$i]%", "$data[$i]%", - "% $data[$i]%", "$data[$i]%", "% $data[$i]%" ); +} +=head2 GetPendingIssues - # FIXME - .= < returns a two-element array. C<$issues> is a +reference-to-array, where each element is a reference-to-hash; the +keys are the fields from the C, C, and C tables +in the Koha database. C<$count> is the number of elements in +C<$issues>. + +=cut + +#' +sub GetPendingIssues { + my ($borrowernumber) = @_; + my $dbh = C4::Context->dbh; + + my $sth = $dbh->prepare( + "SELECT * FROM issues + LEFT JOIN items ON issues.itemnumber=items.itemnumber + LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber + LEFT JOIN biblioitems ON items.biblioitemnumber=biblioitems.biblioitemnumber + WHERE + borrowernumber=? + AND returndate IS NULL + ORDER BY issues.date_due" + ); + $sth->execute($borrowernumber); + my $data = $sth->fetchall_arrayref({}); + my $today = POSIX::strftime("%Y%m%d", localtime); + foreach( @$data ) { + my $datedue = $_->{'date_due'}; + $datedue =~ s/-//g; + if ( $datedue < $today ) { + $_->{'overdue'} = 1; } - $query = $query . ") or cardnumber like ? - order by $orderby"; - push( @bind, $searchstring ); + } + $sth->finish; + return ( scalar(@$data), $data ); +} - # FIXME - .= < is the name of a field on which to sort the results. This +should be the name of a field in the C, C, +C, or C table in the Koha database. + +C<$limit> is the maximum number of results to return. + +C<&GetAllIssues> returns a two-element array. C<$issues> is a +reference-to-array, where each element is a reference-to-hash; the +keys are the fields from the C, C, C, and +C tables of the Koha database. C<$count> is the number of +elements in C<$issues> + +=cut + +#' +sub GetAllIssues { + my ( $borrowernumber, $order, $limit ) = @_; + + #FIXME: sanity-check order and limit + my $dbh = C4::Context->dbh; + my $count = 0; + my $query = +"Select *,items.timestamp AS itemstimestamp from issues,biblio,items,biblioitems + where borrowernumber=? and + items.biblioitemnumber=biblioitems.biblioitemnumber and + items.itemnumber=issues.itemnumber and + items.biblionumber=biblio.biblionumber order by $order"; + if ( $limit != 0 ) { + $query .= " limit $limit"; } + #print $query; my $sth = $dbh->prepare($query); - $sth->execute(@bind); - my @results; - my $cnt = $sth->rows; + $sth->execute($borrowernumber); + my @result; + my $i = 0; while ( my $data = $sth->fetchrow_hashref ) { - push( @results, $data ); + $result[$i] = $data; + $i++; + $count++; } - # $sth->execute; + # get all issued items for borrowernumber from oldissues table + # large chunk of older issues data put into table oldissues + # to speed up db calls for issuing items + if ( C4::Context->preference("ReadingHistory") ) { + my $query2 = "SELECT * FROM oldissues,biblio,items,biblioitems + WHERE borrowernumber=? + AND items.biblioitemnumber=biblioitems.biblioitemnumber + AND items.itemnumber=oldissues.itemnumber + AND items.biblionumber=biblio.biblionumber + ORDER BY $order"; + if ( $limit != 0 ) { + $limit = $limit - $count; + $query2 .= " limit $limit"; + } + + my $sth2 = $dbh->prepare($query2); + $sth2->execute($borrowernumber); + + while ( my $data2 = $sth2->fetchrow_hashref ) { + $result[$i] = $data2; + $i++; + } + $sth2->finish; + } $sth->finish; - return ( $cnt, \@results ); + + return ( $i, \@result ); } -=item findguarantees +=head2 GetMemberAccountRecords - ($num_children, $children_arrayref) = &findguarantees($parent_borrno); - $child0_cardno = $children_arrayref->[0]{"cardnumber"}; - $child0_borrno = $children_arrayref->[0]{"borrowernumber"}; + ($total, $acctlines, $count) = &GetMemberAccountRecords($borrowernumber); -C<&findguarantees> takes a borrower number (e.g., that of a patron -with children) and looks up the borrowers who are guaranteed by that -borrower (i.e., the patron's children). +Looks up accounting data for the patron with the given borrowernumber. -C<&findguarantees> returns two values: an integer giving the number of -borrowers guaranteed by C<$parent_borrno>, and a reference to an array -of references to hash, which gives the actual results. +C<&GetMemberAccountRecords> returns a three-element array. C<$acctlines> is a +reference-to-array, where each element is a reference-to-hash; the +keys are the fields of the C table in the Koha database. +C<$count> is the number of elements in C<$acctlines>. C<$total> is the +total amount outstanding for all of the account lines. =cut + #' -sub findguarantees{ - my ($bornum)=@_; - my $dbh = C4::Context->dbh; - my $sth=$dbh->prepare("select cardnumber,borrowernumber, firstname, surname from borrowers where guarantor=?"); - $sth->execute($bornum); - - my @dat; - while (my $data = $sth->fetchrow_hashref) - { - push @dat, $data; - } - $sth->finish; - return (scalar(@dat), \@dat); +sub GetMemberAccountRecords { + my ($borrowernumber,$date) = @_; + my $dbh = C4::Context->dbh; + my @acctlines; + my $numlines = 0; + my $strsth = qq( +SELECT * +FROM accountlines +WHERE borrowernumber=?); + my @bind = ($borrowernumber); + if ($date && $date ne ''){ + $strsth.=" +AND date < ? "; + push(@bind,$date); + } + $strsth.=" +ORDER BY date desc,timestamp DESC"; + my $sth= $dbh->prepare( $strsth ); + $sth->execute( @bind ); + my $total = 0; + while ( my $data = $sth->fetchrow_hashref ) { + $acctlines[$numlines] = $data; + $numlines++; + $total += $data->{'amountoutstanding'}; + } + $sth->finish; + return ( $total, \@acctlines,$numlines); } -=item findguarantor +=head2 GetBorNotifyAcctRecord - $guarantor = &findguarantor($borrower_no); - $guarantor_cardno = $guarantor->{"cardnumber"}; - $guarantor_surname = $guarantor->{"surname"}; - ... + ($count, $acctlines, $total) = &GetBorNotifyAcctRecord($params,$notifyid); -C<&findguarantor> takes a borrower number (presumably that of a child -patron), finds the guarantor for C<$borrower_no> (the child's parent), -and returns the record for the guarantor. +Looks up accounting data for the patron with the given borrowernumber per file number. -C<&findguarantor> returns a reference-to-hash. Its keys are the fields -from the C database table; +(FIXME - I'm not at all sure what this is about.) + +C<&GetBorNotifyAcctRecord> returns a three-element array. C<$acctlines> is a +reference-to-array, where each element is a reference-to-hash; the +keys are the fields of the C table in the Koha database. +C<$count> is the number of elements in C<$acctlines>. C<$total> is the +total amount outstanding for all of the account lines. =cut -#' -sub findguarantor{ - my ($bornum)=@_; - my $dbh = C4::Context->dbh; - my $sth=$dbh->prepare("select guarantor from borrowers where borrowernumber=?"); - $sth->execute($bornum); - my $data=$sth->fetchrow_hashref; - $sth->finish; - $sth=$dbh->prepare("Select * from borrowers where borrowernumber=?"); - $sth->execute($data->{'guarantor'}); - $data=$sth->fetchrow_hashref; - $sth->finish; - return($data); -} -sub borrowercard_active { - my ($bornum) = @_; - my $dbh = C4::Context->dbh; - my $sth = $dbh->prepare("SELECT expiry FROM borrowers WHERE (borrowernumber = ?) AND (NOW() <= expiry)"); - $sth->execute($bornum); - if (my $data=$sth->fetchrow_hashref){ - return ('1'); - }else{ - return ('0'); - } -} +sub GetBorNotifyAcctRecord { + my ( $borrowernumber, $notifyid ) = @_; + my $dbh = C4::Context->dbh; + my @acctlines; + my $numlines = 0; + my $query = qq| SELECT * + FROM accountlines + WHERE borrowernumber=? + AND notify_id=? + AND (accounttype='FU' OR accounttype='N' OR accounttype='M'OR accounttype='A'OR accounttype='F'OR accounttype='L' OR accounttype='IP' OR accounttype='CH' OR accounttype='RE' OR accounttype='RL') + AND amountoutstanding != '0' + ORDER BY notify_id,accounttype + |; + my $sth = $dbh->prepare($query); -# Search the member photo, in case that photo doesn´t exists, return a default photo.for NEU -sub getMemberPhoto { - my $cardnumber = shift @_; - my $htdocs = C4::Context->config('opacdir'); -my $dirname = $htdocs."/htdocs/uploaded-files/users-photo/"; -# my $dirname = "$ENV{'DOCUMENT_ROOT'}/uploaded-files/users-photo"; - opendir(DIR, $dirname) or die "Can't open directory $dirname: $!"; - while (defined(my $file = readdir(DIR))) { - if ($file =~ /^$cardnumber\..+/){ - return "/uploaded-files/users-photo/$file"; - } - } - closedir(DIR); - return "http://cc.neu.edu.tr/stdpictures/".$cardnumber.".jpg"; + $sth->execute( $borrowernumber, $notifyid ); + my $total = 0; + while ( my $data = $sth->fetchrow_hashref ) { + $acctlines[$numlines] = $data; + $numlines++; + $total += $data->{'amountoutstanding'}; + } + $sth->finish; + return ( $total, \@acctlines, $numlines ); } -sub change_user_pass { - my ($uid,$member,$digest) = @_; - my $dbh = C4::Context->dbh; - #Make sure the userid chosen is unique and not theirs if non-empty. If it is not, - #Then we need to tell the user and have them create a new one. - my $sth=$dbh->prepare("select * from borrowers where userid=? and borrowernumber <> ?"); - $sth->execute($uid,$member); - if ( ($uid ne '') && ($sth->fetchrow) ) { - - return 0; - } else { - #Everything is good so we can update the information. - $sth=$dbh->prepare("update borrowers set userid=?, password=? where borrowernumber=?"); - $sth->execute($uid, $digest, $member); - return 1; - } =head2 checkuniquemember (OUEST-PROVENCE) $result = &checkuniquemember($collectivity,$surname,$categorycode,$firstname,$dateofbirth); @@ -1261,6 +1234,23 @@ sub checkuniquemember { return 0; } } + +sub checkcardnumber { + my ($cardnumber) = @_; + my $dbh = C4::Context->dbh; + my $query = "SELECT * FROM borrowers WHERE cardnumber=?"; + my $sth = $dbh->prepare($query); + $sth->execute($cardnumber); + if (my $data= $sth->fetchrow_hashref()){ + return 1; + } + else { + return 0; + } + $sth->finish(); +} + + =head2 getzipnamecity (OUEST-PROVENCE) take all info from table city for the fields city and zip @@ -1279,30 +1269,11 @@ sub getzipnamecity { return $data[0], $data[1]; } -=head2 updatechildguarantor (OUEST-PROVENCE) - -check for title,firstname,surname,adress,zip code and city from guarantor to -guarantorchild - -=cut - -#' - -sub getguarantordata { - my ($borrowerid) = @_; - my $dbh = C4::Context->dbh; - my $sth = - $dbh->prepare( -"Select title,firstname,surname,streetnumber,address,streettype,address2,zipcode,city,phone,phonepro,mobile,email,emailpro,fax from borrowers where borrowernumber =? " - ); - $sth->execute($borrowerid); - my $guarantor_data = $sth->fetchrow_hashref; - $sth->finish; - return $guarantor_data; -} =head2 getdcity (OUEST-PROVENCE) + recover cityid with city_name condition + =cut sub getidcity { @@ -1314,42 +1285,111 @@ sub getidcity { return $data; } -=head2 getcategorytype (OUEST-PROVENCE) -check for the category_type with categorycode -and return the category_type +=head2 GetExpiryDate -=cut + $expirydate = GetExpiryDate($categorycode, $dateenrolled); +process expiry date given a date and a categorycode -sub getcategorytype { - my ($categorycode) = @_; - my $dbh = C4::Context->dbh; - my $sth = +=cut +sub GetExpiryDate { + my ( $categorycode, $dateenrolled ) = @_; + my $dbh = C4::Context->dbh; + my $sth = $dbh->prepare( -"Select category_type,description from categories where categorycode=? " - ); + "select enrolmentperiod from categories where categorycode=?"); $sth->execute($categorycode); - my ( $category_type, $description ) = $sth->fetchrow; - return $category_type, $description; + my ($enrolmentperiod) = $sth->fetchrow; + $enrolmentperiod = 12 unless ($enrolmentperiod); + my @date=split /-/,format_date_in_iso($dateenrolled); + @date=Add_Delta_YM($date[0],$date[1],$date[2],0,$enrolmentperiod); + return sprintf("%04d-%02d-%02d",$date[0],$date[1],$date[2]); } + +=head2 checkuserpassword (OUEST-PROVENCE) + +check for the password and login are not used +return the number of record +0=> NOT USED 1=> USED + +=cut + +sub checkuserpassword { + my ( $borrowernumber, $userid, $password ) = @_; + $password = md5_base64($password); + my $dbh = C4::Context->dbh; + my $sth = + $dbh->prepare( +"Select count(*) from borrowers where borrowernumber !=? and userid =? and password=? " + ); + $sth->execute( $borrowernumber, $userid, $password ); + my $number_rows = $sth->fetchrow; + return $number_rows; + } +=head2 GetborCatFromCatType + ($codes_arrayref, $labels_hashref) = &GetborCatFromCatType(); +Looks up the different types of borrowers in the database. Returns two +elements: a reference-to-array, which lists the borrower category +codes, and a reference-to-hash, which maps the borrower category codes +to category descriptions. +=cut +#' +sub GetborCatFromCatType { + my ( $category_type, $action ) = @_; + my $dbh = C4::Context->dbh; + my $request = qq| SELECT categorycode,description + FROM categories + $action + ORDER BY categorycode|; + my $sth = $dbh->prepare($request); + if ($action) { + $sth->execute($category_type); + } + else { + $sth->execute(); + } -# # A better approach might be to set borrowernumber autoincrement and -# - sub NewBorrowerNumber { - my $dbh = C4::Context->dbh; - my $sth=$dbh->prepare("Select max(borrowernumber) from borrowers"); - $sth->execute; - my $data=$sth->fetchrow_hashref; - $sth->finish; - $data->{'max(borrowernumber)'}++; - return($data->{'max(borrowernumber)'}); - } + my %labels; + my @codes; + + while ( my $data = $sth->fetchrow_hashref ) { + push @codes, $data->{'categorycode'}; + $labels{ $data->{'categorycode'} } = $data->{'description'}; + } + $sth->finish; + return ( \@codes, \%labels ); +} + +=head2 GetBorrowercategory + + $hashref = &GetBorrowercategory($categorycode); + +Given the borrower's category code, the function returns the corresponding +data hashref for a comprehensive information display. + +=cut + +sub GetBorrowercategory { + my ($catcode) = @_; + my $dbh = C4::Context->dbh; + my $sth = + $dbh->prepare( +"SELECT description,dateofbirthrequired,upperagelimit,category_type + FROM categories + WHERE categorycode = ?" + ); + $sth->execute($catcode); + my $data = + $sth->fetchrow_hashref; + $sth->finish(); + return $data; +} # sub getborrowercategory =head2 ethnicitycategories @@ -1390,9 +1430,9 @@ Koha database ("European" or "Pacific Islander"). #' -sub fixEthnicity($) { - +sub fixEthnicity { my $ethnicity = shift; + return unless $ethnicity; my $dbh = C4::Context->dbh; my $sth = $dbh->prepare("Select name from ethnicity where code = ?"); $sth->execute($ethnicity); @@ -1401,40 +1441,38 @@ sub fixEthnicity($) { return $data->{'name'}; } # sub fixEthnicity +=head2 GetAge - -=head2 get_age - - $dateofbirth,$date = &get_age($date); + $dateofbirth,$date = &GetAge($date); this function return the borrowers age with the value of dateofbirth =cut + #' -sub get_age { - my ($date, $date_ref) = @_; +sub GetAge{ + my ( $date, $date_ref ) = @_; - if (not defined $date_ref) { - $date_ref = sprintf('%04d-%02d-%02d', Today()); + if ( not defined $date_ref ) { + $date_ref = sprintf( '%04d-%02d-%02d', Today() ); } - my ($year1, $month1, $day1) = split /-/, $date; - my ($year2, $month2, $day2) = split /-/, $date_ref; + my ( $year1, $month1, $day1 ) = split /-/, $date; + my ( $year2, $month2, $day2 ) = split /-/, $date_ref; my $age = $year2 - $year1; - if ($month1.$day1 > $month2.$day2) { + if ( $month1 . $day1 > $month2 . $day2 ) { $age--; } return $age; -}# sub get_age - - +} # sub get_age =head2 get_institutions $insitutions = get_institutions(); Just returns a list of all the borrowers of type I, borrownumber and name + =cut #' @@ -1469,65 +1507,349 @@ sub add_member_orgs { my $query = "INSERT INTO borrowers_to_borrowers (borrower1,borrower2) VALUES (?,?)"; my $sth = $dbh->prepare($query); - foreach my $bornum (@$otherborrowers) { - $sth->execute( $borrowernumber, $bornum ); + foreach my $otherborrowernumber (@$otherborrowers) { + $sth->execute( $borrowernumber, $otherborrowernumber ); } $sth->finish(); } # sub add_member_orgs -=head2 GetBorrowersFromSurname +=head2 GetCities (OUEST-PROVENCE) -=over 4 + ($id_cityarrayref, $city_hashref) = &GetCities(); -\@resutlts = GetBorrowersFromSurname($surname) -this function get the list of borrower names like $surname. -return : -the table of results in @results +Looks up the different city and zip in the database. Returns two +elements: a reference-to-array, which lists the zip city +codes, and a reference-to-hash, which maps the name of the city. +WHERE =>OUEST PROVENCE OR EXTERIEUR -=back +=cut + +sub GetCities { + + #my ($type_city) = @_; + my $dbh = C4::Context->dbh; + my $query = qq|SELECT cityid,city_name + FROM cities + ORDER BY city_name|; + my $sth = $dbh->prepare($query); + + #$sth->execute($type_city); + $sth->execute(); + my %city; + my @id; + + # insert empty value to create a empty choice in cgi popup + + while ( my $data = $sth->fetchrow_hashref ) { + + push @id, $data->{'cityid'}; + $city{ $data->{'cityid'} } = $data->{'city_name'}; + } + +#test to know if the table contain some records if no the function return nothing + my $id = @id; + $sth->finish; + if ( $id eq 0 ) { + return (); + } + else { + unshift( @id, "" ); + return ( \@id, \%city ); + } +} + +=head2 GetSortDetails (OUEST-PROVENCE) + + ($lib) = &GetSortDetails($category,$sortvalue); + +Returns the authorized value details +C<&$lib>return value of authorized value details +C<&$sortvalue>this is the value of authorized value +C<&$category>this is the value of authorized value category =cut -sub GetBorrowersFromSurname { - my ($searchstring)=@_; + +sub GetSortDetails { + my ( $category, $sortvalue ) = @_; + my $dbh = C4::Context->dbh; + my $query = qq|SELECT lib + FROM authorised_values + WHERE category=? + AND authorised_value=? |; + my $sth = $dbh->prepare($query); + $sth->execute( $category, $sortvalue ); + my $lib = $sth->fetchrow; + return ($lib); +} + +=head2 DeleteBorrower + + () = &DeleteBorrower($member); + +delete all data fo borrowers and add record to deletedborrowers table +C<&$member>this is the borrowernumber + +=cut + +sub MoveMemberToDeleted { + my ($member) = @_; my $dbh = C4::Context->dbh; - $searchstring=~ s/\'/\\\'/g; - my @data=split(' ',$searchstring); - my $count=@data; + my $query; + $query = qq|SELECT * + FROM borrowers + WHERE borrowernumber=?|; + my $sth = $dbh->prepare($query); + $sth->execute($member); + my @data = $sth->fetchrow_array; + $sth->finish; + $sth = + $dbh->prepare( "INSERT INTO deletedborrowers VALUES (" + . ( "?," x ( scalar(@data) - 1 ) ) + . "?)" ); + $sth->execute(@data); + $sth->finish; +} + +=head2 DelMember + +DelMember($borrowernumber); + +This function remove directly a borrower whitout writing it on deleteborrower. ++ Deletes reserves for the borrower + +=cut + +sub DelMember { + my $dbh = C4::Context->dbh; + my $borrowernumber = shift; + warn "in delmember with $borrowernumber"; + return unless $borrowernumber; # borrowernumber is mandatory. + + my $query = qq|DELETE + FROM reserves + WHERE borrowernumber=?|; + my $sth = $dbh->prepare($query); + $sth->execute($borrowernumber); + $sth->finish; + $query = " + DELETE + FROM borrowers + WHERE borrowernumber = ? + "; + $sth = $dbh->prepare($query); + $sth->execute($borrowernumber); + $sth->finish; + &logaction(C4::Context->userenv->{'number'},"MEMBERS","DELETE",$borrowernumber,"") + if C4::Context->preference("BorrowersLog"); + return $sth->rows; +} + +=head2 ExtendMemberSubscriptionTo (OUEST-PROVENCE) + +$date= ExtendMemberSubscriptionTo($borrowerid, $date); +Extending the subscription to a given date or to the expiry date calculated on local date. +returns date +=cut + +sub ExtendMemberSubscriptionTo { + my ( $borrowerid,$date) = @_; + my $dbh = C4::Context->dbh; + unless ($date){ + $date=POSIX::strftime("%Y-%m-%d",localtime(time)); + my $borrower = GetMember($borrowerid,'borrowernumber'); + $date = GetExpiryDate( $borrower->{'categorycode'}, $date ); + } + my $sth = $dbh->do(<dbh; my $query = qq| - SELECT surname,firstname - FROM borrowers - WHERE (surname like ?) - ORDER BY surname - |; - my $sth=$dbh->prepare($query); - $sth->execute("$data[0]%"); +SELECT roadtypeid,road_type +FROM roadtype +ORDER BY road_type|; + my $sth = $dbh->prepare($query); + $sth->execute(); + my %roadtype; + my @id; + + # insert empty value to create a empty choice in cgi popup + + while ( my $data = $sth->fetchrow_hashref ) { + + push @id, $data->{'roadtypeid'}; + $roadtype{ $data->{'roadtypeid'} } = $data->{'road_type'}; + } + +#test to know if the table contain some records if no the function return nothing + my $id = @id; + $sth->finish; + if ( $id eq 0 ) { + return (); + } + else { + unshift( @id, "" ); + return ( \@id, \%roadtype ); + } +} + + + +=head2 GetTitles (OUEST-PROVENCE) + + ($borrowertitle)= &GetTitles(); + +Looks up the different title . Returns array with all borrowers title + +=cut + +sub GetTitles { + my @borrowerTitle = split /,|\|/,C4::Context->preference('BorrowersTitles'); + unshift( @borrowerTitle, "" ); + return ( \@borrowerTitle); + } + + + +=head2 GetRoadTypeDetails (OUEST-PROVENCE) + + ($roadtype) = &GetRoadTypeDetails($roadtypeid); + +Returns the description of roadtype +C<&$roadtype>return description of road type +C<&$roadtypeid>this is the value of roadtype s + +=cut + +sub GetRoadTypeDetails { + my ($roadtypeid) = @_; + my $dbh = C4::Context->dbh; + my $query = qq| +SELECT road_type +FROM roadtype +WHERE roadtypeid=?|; + my $sth = $dbh->prepare($query); + $sth->execute($roadtypeid); + my $roadtype = $sth->fetchrow; + return ($roadtype); +} + +=head2 GetBorrowersWhoHaveNotBorrowedSince + +&GetBorrowersWhoHaveNotBorrowedSince($date) + +this function get all borrowers who haven't borrowed since the date given on input arg. + +=cut + +sub GetBorrowersWhoHaveNotBorrowedSince { + my $date = shift; + return unless $date; # date is mandatory. + my $dbh = C4::Context->dbh; + my $query = " + SELECT borrowers.borrowernumber,max(timestamp) + FROM borrowers + LEFT JOIN issues ON borrowers.borrowernumber = issues.borrowernumber + WHERE issues.borrowernumber IS NOT NULL + GROUP BY borrowers.borrowernumber + "; + my $sth = $dbh->prepare($query); + $sth->execute; my @results; - my $count = 0; - while (my $data=$sth->fetchrow_hashref){ - push(@results,$data); - $count++; + + while ( my $data = $sth->fetchrow_hashref ) { + push @results, $data; } - $sth->finish; - return ($count,\@results); + return \@results; } -=head2 expand_sex_into_predicate +=head2 GetBorrowersWhoHaveNeverBorrowed - $data{&expand_sex_into_predicate($data{sex})} = 1; +$results = &GetBorrowersWhoHaveNeverBorrowed -Converts a single 'M' or 'F' into 'sex_M_p' or 'sex_F_p' -respectively. +this function get all borrowers who have never borrowed. -In some languages, 'M' and 'F' are not appropriate. However, -with HTML::Template, there is no way to localize 'M' or 'F' -unless these are converted into variables that TMPL_IF can -understand. This function provides this conversion. +I<$result> is a ref to an array which all elements are a hasref. =cut -sub expand_sex_into_predicate ($) { - my($sex) = @_; - return "sex_${sex}_p"; -} # expand_sex_into_predicate +sub GetBorrowersWhoHaveNeverBorrowed { + my $dbh = C4::Context->dbh; + my $query = " + SELECT borrowers.borrowernumber,max(timestamp) + FROM borrowers + LEFT JOIN issues ON borrowers.borrowernumber = issues.borrowernumber + WHERE issues.borrowernumber IS NULL + "; + my $sth = $dbh->prepare($query); + $sth->execute; + my @results; + while ( my $data = $sth->fetchrow_hashref ) { + push @results, $data; + } + return \@results; +} + +=head2 GetBorrowersWithIssuesHistoryOlderThan + +$results = &GetBorrowersWithIssuesHistoryOlderThan($date) + +this function get all borrowers who has an issue history older than I<$date> given on input arg. + +I<$result> is a ref to an array which all elements are a hashref. +This hashref is containt the number of time this borrowers has borrowed before I<$date> and the borrowernumber. + +=cut + +sub GetBorrowersWithIssuesHistoryOlderThan { + my $dbh = C4::Context->dbh; + my $date = shift; + return unless $date; # date is mandatory. + my $query = " + SELECT count(borrowernumber) as n,borrowernumber + FROM issues + WHERE returndate < ? + AND borrowernumber IS NOT NULL + GROUP BY borrowernumber + "; + my $sth = $dbh->prepare($query); + $sth->execute($date); + my @results; + + while ( my $data = $sth->fetchrow_hashref ) { + push @results, $data; + } + return \@results; +} + +END { } # module clean-up code here (global destructor) + 1; + +__END__ + +=back + +=head1 AUTHOR + +Koha Team + +=cut