3 # Copyright 2000-2002 Katipo Communications
5 # This file is part of Koha.
7 # Koha is free software; you can redistribute it and/or modify it under the
8 # terms of the GNU General Public License as published by the Free Software
9 # Foundation; either version 2 of the License, or (at your option) any later
12 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
13 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
14 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
16 # You should have received a copy of the GNU General Public License along with
17 # Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
18 # Suite 330, Boston, MA 02111-1307 USA
25 # FIXME - C4::Search uses C4::Reserves2, which uses C4::Search.
26 # So Perl complains that all of the functions here get redefined.
29 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
31 # set the version for version checking
32 $VERSION = do { my @v = '$Revision$' =~ /\d+/g;
33 shift(@v) . "." . join("_", map {sprintf "%03d", $_ } @v); };
37 C4::Search - Functions for searching the Koha catalog and other databases
43 my ($count, @results) = catalogsearch($env, $type, $search, $num, $offset);
47 This module provides the searching facilities for the Koha catalog and
50 C<&catalogsearch> is a front end to all the other searches. Depending
51 on what is passed to it, it calls the appropriate search function.
61 &CatSearch &BornameSearch &ItemInfo &KeywordSearch &subsearch
62 &itemdata &bibdata &GetItems &borrdata &itemnodata
63 &borrdata2 &NewBorrowerNumber &bibitemdata &borrissues
64 &getboracctrecord &ItemType &itemissues &subject &subtitle
65 &addauthor &bibitems &barcodes &allissues
66 &findguarantor &getwebsites &getwebbiblioitems &catalogsearch &itemcount2
67 &isbnsearch &getbranchname &getborrowercategory);
68 # make all your functions, whether exported or not;
72 $guarantor = &findguarantor($borrower_no);
73 $guarantor_cardno = $guarantor->{"cardnumber"};
74 $guarantor_surname = $guarantor->{"surname"};
77 C<&findguarantor> takes a borrower number (presumably that of a child
78 patron), finds the guarantor for C<$borrower_no> (the child's parent),
79 and returns the record for the guarantor.
81 C<&findguarantor> returns a reference-to-hash. Its keys are the fields
82 from the C<borrowers> database table;
88 my $dbh = C4::Context->dbh;
89 my $sth=$dbh->prepare("select guarantor from borrowers where borrowernumber=?");
90 $sth->execute($bornum);
91 my $data=$sth->fetchrow_hashref;
93 $sth=$dbh->prepare("Select * from borrowers where borrowernumber=?");
94 $sth->execute($data->{'guarantor'});
95 $data=$sth->fetchrow_hashref;
100 =item NewBorrowerNumber
102 $num = &NewBorrowerNumber();
104 Allocates a new, unused borrower number, and returns it.
108 # FIXME - This is identical to C4::Circulation::Borrower::NewBorrowerNumber.
109 # Pick one and stick with it. Preferably use the other one. This function
110 # doesn't belong in C4::Search.
111 sub NewBorrowerNumber {
112 my $dbh = C4::Context->dbh;
113 my $sth=$dbh->prepare("Select max(borrowernumber) from borrowers");
115 my $data=$sth->fetchrow_hashref;
117 $data->{'max(borrowernumber)'}++;
118 return($data->{'max(borrowernumber)'});
123 ($count, @results) = &catalogsearch($env, $type, $search, $num, $offset);
125 This is primarily a front-end to other, more specialized catalog
126 search functions: if C<$search-E<gt>{itemnumber}> or
127 C<$search-E<gt>{isbn}> is given, C<&catalogsearch> uses a precise
128 C<&CatSearch>. If $search->{subject} is given, it runs a subject
129 C<&CatSearch>. If C<$search-E<gt>{keyword}> is given, it runs a
130 C<&KeywordSearch>. Otherwise, it runs a loose C<&CatSearch>.
132 If C<$env-E<gt>{itemcount}> is 1, then C<&catalogsearch> also counts
133 the items for each result, and adds several keys:
139 The total number of copies of this book.
141 =item C<locationhash>
143 This is a reference-to-hash; the keys are the names of branches where
144 this book may be found, and the values are the number of copies at
149 A descriptive string saying where the book is located, and how many
150 copies there are, if greater than 1.
154 The book's subject, with spaces replaced with C<%20>, presumably for
162 my ($env,$type,$search,$num,$offset)=@_;
163 my $dbh = C4::Context->dbh;
164 # foreach my $key (%$search){
165 # $search->{$key}=$dbh->quote($search->{$key});
167 my ($count,@results);
168 if ($search->{'itemnumber'} ne '' || $search->{'isbn'} ne ''){
169 print STDERR "Doing a precise search\n";
170 ($count,@results)=CatSearch($env,'precise',$search,$num,$offset);
171 } elsif ($search->{'subject'} ne ''){
172 ($count,@results)=CatSearch($env,'subject',$search,$num,$offset);
173 } elsif ($search->{'keyword'} ne ''){
174 ($count,@results)=&KeywordSearch($env,'keyword',$search,$num,$offset);
176 ($count,@results)=CatSearch($env,'loose',$search,$num,$offset);
179 if ($env->{itemcount} eq '1') {
180 foreach my $data (@results){
181 my ($counts) = itemcount2($env, $data->{'biblionumber'}, 'intra');
182 my $subject2=$data->{'subject'};
183 $subject2=~ s/ /%20/g;
184 $data->{'itemcount'}=$counts->{'total'};
185 my $totalitemcounts=0;
186 foreach my $key (keys %$counts){
187 if ($key ne 'total'){ # FIXME - Should ignore 'order', too.
188 #$data->{'location'}.="$key $counts->{$key} ";
189 $totalitemcounts+=$counts->{$key};
190 $data->{'locationhash'}->{$key}=$counts->{$key};
194 my $locationtextonly='';
195 my $notavailabletext='';
196 foreach (sort keys %{$data->{'locationhash'}}) {
197 if ($_ eq 'notavailable') {
198 $notavailabletext="Not available";
199 my $c=$data->{'locationhash'}->{$_};
200 $data->{'not-available-p'}=$totalitemcounts;
201 if ($totalitemcounts>1) {
202 $notavailabletext.=" ($c)";
203 $data->{'not-available-plural-p'}=1;
207 my $c=$data->{'locationhash'}->{$_};
208 if ($_ eq 'Item Lost') {
209 $data->{'lost-p'}=$totalitemcounts;
210 $data->{'lost-plural-p'}=1
211 if $totalitemcounts > 1;
212 } elsif ($_ eq 'Withdrawn') {
213 $data->{'withdrawn-p'}=$totalitemcounts;
214 $data->{'withdrawn-plural-p'}=1
215 if $totalitemcounts > 1;
216 } elsif ($_ eq 'On Loan') {
217 $data->{'on-loan-p'}=$totalitemcounts;
218 $data->{'on-loan-plural-p'}=1
219 if $totalitemcounts > 1;
221 $locationtextonly.=$_;
222 $locationtextonly.=" ($c), "
223 if $totalitemcounts>1;
225 if ($totalitemcounts>1) {
226 $locationtext.=" ($c), ";
230 if ($notavailabletext) {
231 $locationtext.=$notavailabletext;
233 $locationtext=~s/, $//;
235 $data->{'location'}=$locationtext;
236 $data->{'location-only'}=$locationtextonly;
237 $data->{'subject2'}=$subject2;
238 $data->{'use-location-flags-p'}=1; # XXX
241 return ($count,@results);
246 $search = { "keyword" => "One or more keywords",
247 "class" => "VID|CD", # Limit search to fiction and CDs
250 ($count, @results) = &KeywordSearch($env, $type, $search, $num, $offset);
252 C<&KeywordSearch> searches the catalog by keyword: given a string
253 (C<$search-E<gt>{"keyword"}> consisting of a space-separated list of
254 keywords, it looks for books that contain any of those keywords in any
255 of a number of places.
257 C<&KeywordSearch> looks for keywords in the book title (and subtitle),
258 series name, notes (both C<biblio.notes> and C<biblioitems.notes>),
261 C<$search-E<gt>{"class"}> can be set to a C<|> (pipe)-separated list of
262 item class codes (e.g., "F" for fiction, "JNF" for junior nonfiction,
263 etc.). In this case, the search will be restricted to just those
266 If C<$search-E<gt>{"class"}> is not specified, you may specify
267 C<$search-E<gt>{"dewey"}>. This will restrict the search to that
268 particular Dewey Decimal Classification category. Setting
269 C<$search-E<gt>{"dewey"}> to "513" will return books about arithmetic,
270 whereas setting it to "5" will return all books with Dewey code 5I<xx>
271 (Science and Mathematics).
273 C<$env> and C<$type> are ignored.
275 C<$offset> and C<$num> specify the subset of results to return.
276 C<$num> specifies the number of results to return, and C<$offset> is
277 the number of the first result. Thus, setting C<$offset> to 100 and
278 C<$num> to 5 will return results 100 through 104 inclusive.
283 my ($env,$type,$search,$num,$offset)=@_;
284 my $dbh = C4::Context->dbh;
285 $search->{'keyword'}=~ s/ +$//;
286 my @key=split(' ',$search->{'keyword'});
287 # FIXME - Naive users might enter comma-separated
288 # words, e.g., "training, animal". Ought to cope with
292 my %biblionumbers; # Set of biblionumbers returned by the
295 # FIXME - Ought to filter the stopwords out of the list of keywords.
296 # @key = map { !defined($stopwords{$_}) } @key;
298 # FIXME - The way this code is currently set up, it looks for all of
299 # the keywords first in (title, notes, seriestitle), then in the
300 # subtitle, then in the subject. Thus, if you look for keywords
301 # "science fiction", this search won't find a book with
302 # title = "How to write fiction"
303 # subtitle = "A science-based approach"
304 # Is this the desired effect? If not, then the first SQL query
305 # should look in the biblio, subtitle, and subject tables all at
306 # once. The way the first query is built can accomodate this easily.
308 # Look for keywords in table 'biblio'.
310 # Build an SQL query that finds each of the keywords in any of the
311 # title, biblio.notes, or seriestitle. To do this, we'll build up an
312 # array of clauses, one for each keyword.
313 my $query; # The SQL query
314 my @clauses = (); # The search clauses
315 my @bind = (); # The term bindings
317 $query = <<EOT; # Beginning of the query
322 foreach my $keyword (@key)
324 my @subclauses = (); # Subclauses, one for each field we're
327 # For each field we're searching on, create a subclause that'll
328 # match the current keyword in the current field.
329 foreach my $field (qw(title notes seriestitle author))
332 "$field LIKE ? OR $field LIKE ?";
333 push(@bind,"\Q$keyword\E%","% \Q$keyword\E%");
335 # (Yes, this could have been done as
336 # @subclauses = map {...} qw(field1 field2 ...)
337 # )but I think this way is more readable.
339 # Construct the current clause by joining the subclauses.
340 push @clauses, "(" . join(")\n\tOR (", @subclauses) . ")";
342 # Now join all of the clauses together and append to the query.
343 $query .= "(" . join(")\nAND (", @clauses) . ")";
345 # FIXME - Perhaps use $sth->bind_columns() ? Documented as the most
346 # efficient way to fetch data.
347 my $sth=$dbh->prepare($query);
348 $sth->execute(@bind);
349 while (my @res = $sth->fetchrow_array) {
352 $biblionumbers{$_} = 1; # Add these results to the set
357 # Now look for keywords in the 'bibliosubtitle' table.
359 # Again, we build a list of clauses from the keywords.
362 $query = "SELECT biblionumber FROM bibliosubtitle WHERE ";
363 foreach my $keyword (@key)
366 "subtitle LIKE ? OR subtitle like ?";
367 push(@bind,"\Q$keyword\E%","% \Q$keyword\E%");
369 $query .= "(" . join(") AND (", @clauses) . ")";
371 $sth=$dbh->prepare($query);
372 $sth->execute(@bind);
373 while (my @res = $sth->fetchrow_array) {
376 $biblionumbers{$_} = 1; # Add these results to the set
381 # Look for the keywords in the notes for individual items
382 # ('biblioitems.notes')
384 # Again, we build a list of clauses from the keywords.
387 $query = "SELECT biblionumber FROM biblioitems WHERE ";
388 foreach my $keyword (@key)
391 "notes LIKE ? OR notes like ?";
392 push(@bind,"\Q$keyword\E%","% \Q$keyword\E%");
394 $query .= "(" . join(") AND (", @clauses) . ")";
396 $sth=$dbh->prepare($query);
397 $sth->execute(@bind);
398 while (my @res = $sth->fetchrow_array) {
401 $biblionumbers{$_} = 1; # Add these results to the set
406 # Look for keywords in the 'bibliosubject' table.
408 # FIXME - The other queries look for words in the desired field that
409 # begin with the individual keywords the user entered. This one
410 # searches for the literal string the user entered. Is this the
412 # Note in particular that spaces are retained: if the user typed
414 # (with two spaces), this won't find the subject "science fiction"
415 # (one space). Likewise, a search for "%" will return absolutely
417 # If this isn't the desired effect, see the previous searches for
420 $sth=$dbh->prepare("Select biblionumber from bibliosubject where subject
421 like ? group by biblionumber");
422 $sth->execute("%$search->{'keyword'}%");
424 while (my @res = $sth->fetchrow_array) {
427 $biblionumbers{$_} = 1; # Add these results to the set
437 my @res = keys %biblionumbers;
441 # print "count $count";
442 if ($search->{'class'} ne ''){
444 my $query="select * from biblio,biblioitems where
445 biblio.biblionumber=? and
446 biblio.biblionumber=biblioitems.biblionumber ";
447 my @bind = ($res[$i2]);
448 if ($search->{'class'} ne ''){ # FIXME - Redundant
449 my @temp=split(/\|/,$search->{'class'});
451 $query.= "and ( itemtype=?";
452 push(@bind,$temp[0]);
453 for (my $i=1;$i<$count;$i++){
454 $query.=" or itemtype=?";
455 push(@bind,$temp[$i]);
459 my $sth=$dbh->prepare($query);
461 $sth->execute(@bind);
462 if (my $data2=$sth->fetchrow_hashref){
463 my $dewey= $data2->{'dewey'};
464 my $subclass=$data2->{'subclass'};
465 # FIXME - This next bit is bogus, because it assumes that the
466 # Dewey code is a floating-point number. It isn't. It's
467 # actually a string that mainly consists of numbers. In
468 # particular, "4" is not a valid Dewey code, although "004"
469 # is ("Data processing; Computer science"). Likewise, zeros
470 # after the decimal are significant ("575" is not the same as
471 # "575.0"; the latter is more specific). And "000" is a
472 # perfectly good Dewey code ("General works; computer
473 # science") and should not be interpreted to mean "this
474 # database entry does not have a Dewey code". That's what
477 ($dewey == 0) && ($dewey='');
478 ($dewey) && ($dewey.=" $subclass") ;
480 my $end=$offset +$num;
485 if ($i4 <=$end && $i4 > $offset){
486 $data2->{'dewey'}=$dewey;
489 # $res2[$i3]="$data2->{'author'}\t$data2->{'title'}\t$data2->{'biblionumber'}\t$data2->{'copyrightdate'}\t$dewey";
492 # print "in here $i3<br>";
503 # $search->{'class'} was not specified
505 # FIXME - This is bogus: it makes a separate query for each
506 # biblioitem, and returns results in apparently random order. It'd
507 # be much better to combine all of the previous queries into one big
508 # one (building it up a little at a time, of course), and have that
509 # big query select all of the desired fields, instead of just
512 while ($i2 < $num && $i2 < $count){
513 my $query="select * from biblio,biblioitems where
514 biblio.biblionumber=? and
515 biblio.biblionumber=biblioitems.biblionumber ";
516 my @bind=($res[$i2+$offset]);
518 if ($search->{'dewey'} ne ''){
519 $query.= "and (dewey like ?)";
520 push(@bind,"$search->{'dewey'}%");
523 my $sth=$dbh->prepare($query);
525 $sth->execute(@bind);
526 if (my $data2=$sth->fetchrow_hashref){
527 my $dewey= $data2->{'dewey'};
528 my $subclass=$data2->{'subclass'};
530 ($dewey == 0) && ($dewey='');
531 ($dewey) && ($dewey.=" $subclass") ;
533 $data2->{'dewey'}=$dewey;
536 # $res2[$i]="$data2->{'author'}\t$data2->{'title'}\t$data2->{'biblionumber'}\t$data2->{'copyrightdate'}\t$dewey";
545 return($count,@res2);
549 my ($env,$type,$search,$num,$offset)=@_;
550 my $dbh = C4::Context->dbh;
551 $search->{'keyword'}=~ s/ +$//;
552 my @key=split(' ',$search->{'keyword'});
556 my $query ="Select * from biblio,bibliosubtitle,biblioitems where
557 biblio.biblionumber=biblioitems.biblionumber and
558 biblio.biblionumber=bibliosubtitle.biblionumber and
559 (((title like ? or title like ?)";
560 my @bind=("$key[0]%","% $key[0]%");
562 $query .= " and (title like ? or title like ?)";
563 push(@bind,"$key[$i]%","% $key[$i]%");
566 $query.= ") or ((subtitle like ? or subtitle like ?)";
567 push(@bind,"$key[0]%","% $key[0]%");
568 for ($i=1;$i<$count;$i++){
569 $query.= " and (subtitle like ? or subtitle like ?)";
570 push(@bind,"$key[$i]%","% $key[$i]%");
572 $query.= ") or ((seriestitle like ? or seriestitle like ?)";
573 push(@bind,"$key[0]%","% $key[0]%");
574 for ($i=1;$i<$count;$i++){
575 $query.=" and (seriestitle like ? or seriestitle like ?)";
576 push(@bind,"$key[$i]%","% $key[$i]%");
578 $query.= ") or ((biblio.notes like ? or biblio.notes like ?)";
579 push(@bind,"$key[0]%","% $key[0]%");
580 for ($i=1;$i<$count;$i++){
581 $query.=" and (biblio.notes like ? or biblio.notes like ?)";
582 push(@bind,"$key[$i]%","% $key[$i]%");
584 $query.= ") or ((biblioitems.notes like ? or biblioitems.notes like ?)";
585 push(@bind,"$key[0]%","% $key[0]%");
586 for ($i=1;$i<$count;$i++){
587 $query.=" and (biblioitems.notes like ? or biblioitems.notes like ?)";
588 push(@bind,"$key[$i]%","% $key[$i]%");
590 if ($search->{'keyword'} =~ /new zealand/i){
591 $query.= "or (title like 'nz%' or title like '% nz %' or title like '% nz' or subtitle like 'nz%'
592 or subtitle like '% nz %' or subtitle like '% nz' or author like 'nz %'
593 or author like '% nz %' or author like '% nz')"
595 if ($search->{'keyword'} eq 'nz' || $search->{'keyword'} eq 'NZ' ||
596 $search->{'keyword'} =~ /nz /i || $search->{'keyword'} =~ / nz /i ||
597 $search->{'keyword'} =~ / nz/i){
598 $query.= "or (title like 'new zealand%' or title like '% new zealand %'
599 or title like '% new zealand' or subtitle like 'new zealand%' or
600 subtitle like '% new zealand %'
601 or subtitle like '% new zealand' or author like 'new zealand%'
602 or author like '% new zealand %' or author like '% new zealand' or
603 seriestitle like 'new zealand%' or seriestitle like '% new zealand %'
604 or seriestitle like '% new zealand')"
607 if ($search->{'class'} ne ''){
608 my @temp=split(/\|/,$search->{'class'});
610 $query.= "and ( itemtype=?";
611 push(@bind,"$temp[0]");
612 for (my $i=1;$i<$count;$i++){
613 $query.=" or itemtype=?";
614 push(@bind,"$temp[$i]");
618 if ($search->{'dewey'} ne ''){
619 $query.= "and (dewey like '$search->{'dewey'}%') ";
621 $query.="group by biblio.biblionumber";
622 #$query.=" order by author,title";
624 my $sth=$dbh->prepare($query);
625 $sth->execute(@bind);
627 while (my $data=$sth->fetchrow_hashref){
628 #FIXME: rewrite to use ? before uncomment
629 # my $sti=$dbh->prepare("select dewey,subclass from biblioitems where biblionumber=$data->{'biblionumber'}
632 # my ($dewey, $subclass) = $sti->fetchrow;
633 my $dewey=$data->{'dewey'};
634 my $subclass=$data->{'subclass'};
636 ($dewey == 0) && ($dewey='');
637 ($dewey) && ($dewey.=" $subclass");
639 $results[$i]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey";
640 # print $results[$i];
644 $sth=$dbh->prepare("Select biblionumber from bibliosubject where subject
645 like ? group by biblionumber");
646 $sth->execute("%".$search->{'keyword'}."%");
647 while (my $data=$sth->fetchrow_hashref){
648 $query="Select * from biblio,biblioitems where
649 biblio.biblionumber=? and
650 biblio.biblionumber=biblioitems.biblionumber ";
651 @bind=($data->{'biblionumber'});
652 if ($search->{'class'} ne ''){
653 my @temp=split(/\|/,$search->{'class'});
655 $query.= " and ( itemtype=?";
656 push(@bind,$temp[0]);
657 for (my $i=1;$i<$count;$i++){
658 $query.=" or itemtype=?";
659 push(@bind,$temp[$i]);
664 if ($search->{'dewey'} ne ''){
665 $query.= "and (dewey like ?)";
666 push(@bind,"$search->{'dewey'}%");
668 my $sth2=$dbh->prepare($query);
669 $sth2->execute(@bind);
671 while (my $data2=$sth2->fetchrow_hashref){
672 my $dewey= $data2->{'dewey'};
673 my $subclass=$data2->{'subclass'};
675 ($dewey == 0) && ($dewey='');
676 ($dewey) && ($dewey.=" $subclass") ;
678 $results[$i]="$data2->{'author'}\t$data2->{'title'}\t$data2->{'biblionumber'}\t$data2->{'copyrightdate'}\t$dewey";
679 # print $results[$i];
685 @results=sort @results;
692 while ($i2 < $count){
693 if ($results[$i2] ne $res[$i-1]){
694 $res[$i]=$results[$i2];
702 while ($i2 < $num && $i2 < $count){
703 $res2[$i2]=$res[$i2+$offset];
715 ($count, @results) = &CatSearch($env, $type, $search, $num, $offset);
717 C<&CatSearch> searches the Koha catalog. It returns a list whose first
718 element is the number of returned results, and whose subsequent
719 elements are the results themselves.
721 Each returned element is a reference-to-hash. Most of the keys are
722 simply the fields from the C<biblio> table in the Koha database, but
723 the following keys may also be present:
729 The book's illustrator.
739 C<$type> may be C<subject>, C<loose>, or C<precise>. This controls the
740 high-level behavior of C<&CatSearch>, as described below.
742 In many cases, the description below says that a certain field in the
743 database must match the search string. In these cases, it means that
744 the beginning of some word in the field must match the search string.
745 Thus, an author search for "sm" will return books whose author is
746 "John Smith" or "Mike Smalls", but not "Paul Grossman", since the "sm"
747 does not occur at the beginning of a word.
749 Note that within each search mode, the criteria are and-ed together.
750 That is, if you perform a loose search on the author "Jerome" and the
751 title "Boat", the search will only return books by Jerome containing
754 It is not possible to cross modes, e.g., set the author to "Asimov"
755 and the subject to "Math" in hopes of finding books on math by Asimov.
759 If C<$type> is set to C<loose>, the following search criteria may be
764 =item C<$search-E<gt>{author}>
766 The search string is a space-separated list of words. Each word must
767 match either the C<author> or C<additionalauthors> field.
769 =item C<$search-E<gt>{title}>
771 Each word in the search string must match the book title. If no author
772 is specified, the book subtitle will also be searched.
774 =item C<$search-E<gt>{abstract}>
776 Searches for the given search string in the book's abstract.
778 =item C<$search-E<gt>{'date-before'}>
780 Searches for books whose copyright date matches the search string.
781 That is, setting C<$search-E<gt>{'date-before'}> to "1985" will find
782 books written in 1985, and setting it to "198" will find books written
783 between 1980 and 1989.
785 =item C<$search-E<gt>{title}>
787 Searches by title are also affected by the value of
788 C<$search-E<gt>{"ttype"}>; if it is set to C<exact>, then the book
789 title, (one of) the series titleZ<>(s), or (one of) the unititleZ<>(s) must
790 match the search string exactly (the subtitle is not searched).
792 If C<$search-E<gt>{"ttype"}> is set to anything other than C<exact>,
793 each word in the search string must match the title, subtitle,
794 unititle, or series title.
796 =item C<$search-E<gt>{class}>
798 Restricts the search to certain item classes. The value of
799 C<$search-E<gt>{"class"}> is a | (pipe)-separated list of item types.
800 Thus, setting it to "F" restricts the search to fiction, and setting
801 it to "CD|CAS" will only look in compact disks and cassettes.
803 =item C<$search-E<gt>{dewey}>
805 Searches for books whose Dewey Decimal Classification code matches the
806 search string. That is, setting C<$search-E<gt>{"dewey"}> to "5" will
807 search for all books in 5I<xx> (Science and mathematics), setting it
808 to "54" will search for all books in 54I<x> (Chemistry), and setting
809 it to "546" will search for books on inorganic chemistry.
811 =item C<$search-E<gt>{publisher}>
813 Searches for books whose publisher contains the search string (unlike
814 other search criteria, C<$search-E<gt>{publisher}> is a string, not a
819 =head2 Subject search
821 If C<$type> is set to C<subject>, the following search criterion may
826 =item C<$search-E<gt>{subject}>
828 The search string is a space-separated list of words, each of which
829 must match the book's subject.
831 Special case: if C<$search-E<gt>{subject}> is set to C<nz>,
832 C<&CatSearch> will search for books whose subject is "New Zealand".
833 However, setting C<$search-E<gt>{subject}> to C<"nz football"> will
834 search for books on "nz" and "football", not books on "New Zealand"
839 =head2 Precise search
841 If C<$type> is set to C<precise>, the following search criteria may be
846 =item C<$search-E<gt>{item}>
848 Searches for books whose barcode exactly matches the search string.
850 =item C<$search-E<gt>{isbn}>
852 Searches for books whose ISBN exactly matches the search string.
856 For a loose search, if an author was specified, the results are
857 ordered by author and title. If no author was specified, the results
858 are ordered by title.
860 For other (non-loose) searches, if a subject was specified, the
861 results are ordered alphabetically by subject.
863 In all other cases (e.g., loose search by keyword), the results are
869 my ($env,$type,$search,$num,$offset)=@_;
870 my $dbh = C4::Context->dbh;
875 my $title = lc($search->{'title'});
877 if ($type eq 'loose') {
878 if ($search->{'author'} ne ''){
879 my @key=split(' ',$search->{'author'});
882 $query="select *,biblio.author,biblio.biblionumber from
884 left join additionalauthors
885 on additionalauthors.biblionumber =biblio.biblionumber
887 ((biblio.author like ? or biblio.author like ? or
888 additionalauthors.author like ? or additionalauthors.author
891 @bind=("$key[0]%","% $key[0]%","$key[0]%","% $key[0]%");
894 biblio.author like ? or biblio.author like ? or
895 additionalauthors.author like ? or additionalauthors.author like ?
897 push(@bind,"$key[$i]%","% $key[$i]%","$key[$i]%","% $key[$i]%");
901 if ($search->{'title'} ne ''){
902 my @key=split(' ',$search->{'title'});
905 $query.= " and (((title like ? or title like ?)";
906 push(@bind,"$key[0]%","% $key[0]%");
908 $query .= " and (title like ? or title like ?)";
909 push(@bind,"$key[$i]%","% $key[$i]%");
912 $query.=") or ((seriestitle like ? or seriestitle like ?)";
913 push(@bind,"$key[0]%","% $key[0]%");
914 for ($i=1;$i<$count;$i++){
915 $query.=" and (seriestitle like ? or seriestitle like ?)";
916 push(@bind,"$key[$i]%","% $key[$i]%");
918 $query.=") or ((unititle like ? or unititle like ?)";
919 push(@bind,"$key[0]%","% $key[0]%");
920 for ($i=1;$i<$count;$i++){
921 $query.=" and (unititle like ? or unititle like ?)";
922 push(@bind,"$key[$i]%","% $key[$i]%");
926 if ($search->{'abstract'} ne ''){
927 $query.= " and (abstract like ?)";
928 push(@bind,"%$search->{'abstract'}%");
930 if ($search->{'date-before'} ne ''){
931 $query.= " and (copyrightdate like ?)";
932 push(@bind,"%$search->{'date-before'}%");
934 $query.=" group by biblio.biblionumber";
936 if ($search->{'title'} ne '') {
937 if ($search->{'ttype'} eq 'exact'){
938 $query="select * from biblio
940 (biblio.title=? or (biblio.unititle = ?
941 or biblio.unititle like ? or
942 biblio.unititle like ? or
943 biblio.unititle like ?) or
944 (biblio.seriestitle = ? or
945 biblio.seriestitle like ? or
946 biblio.seriestitle like ? or
947 biblio.seriestitle like ?)
949 @bind=($search->{'title'},$search->{'title'},"$search->{'title'} |%","%| $search->{'title'} |%","%| $search->{'title'}",$search->{'title'},"$search->{'title'} |%","%| $search->{'title'} |%","%| $search->{'title'}");
951 my @key=split(' ',$search->{'title'});
954 $query="select biblio.biblionumber,author,title,unititle,notes,abstract,serial,seriestitle,copyrightdate,timestamp,subtitle from biblio
955 left join bibliosubtitle on
956 biblio.biblionumber=bibliosubtitle.biblionumber
958 (((title like ? or title like ?)";
959 @bind=("$key[0]%","% $key[0]%");
961 $query .= " and (title like ? or title like ?)";
962 push(@bind,"$key[$i]%","% $key[$i]%");
965 $query.=") or ((subtitle like ? or subtitle like ?)";
966 push(@bind,"$key[0]%","% $key[0]%");
967 for ($i=1;$i<$count;$i++){
968 $query.=" and (subtitle like ? or subtitle like ?)";
969 push(@bind,"$key[$i]%","% $key[$i]%");
971 $query.=") or ((seriestitle like ? or seriestitle like ?)";
972 push(@bind,"$key[0]%","% $key[0]%");
973 for ($i=1;$i<$count;$i++){
974 $query.=" and (seriestitle like ? or seriestitle like ?)";
975 push(@bind,"$key[$i]%","% $key[$i]%");
977 $query.=") or ((unititle like ? or unititle like ?)";
978 push(@bind,"$key[0]%","% $key[0]%");
979 for ($i=1;$i<$count;$i++){
980 $query.=" and (unititle like ? or unititle like ?)";
981 push(@bind,"$key[$i]%","% $key[$i]%");
985 if ($search->{'abstract'} ne ''){
986 $query.= " and (abstract like ?)";
987 push(@bind,"%$search->{'abstract'}%");
989 if ($search->{'date-before'} ne ''){
990 $query.= " and (copyrightdate like ?)";
991 push(@bind,"%$search->{'date-before'}%");
993 } elsif ($search->{'class'} ne ''){
994 $query="select * from biblioitems,biblio where biblio.biblionumber=biblioitems.biblionumber";
995 my @temp=split(/\|/,$search->{'class'});
997 $query.= " and ( itemtype= ?)";
999 for (my $i=1;$i<$count;$i++){
1000 $query.=" or itemtype=?";
1001 push(@bind,$temp[$i]);
1004 if ($search->{'illustrator'} ne ''){
1005 $query.=" and illus like ?";
1006 push(@bind,"%".$search->{'illustrator'}."%");
1008 if ($search->{'dewey'} ne ''){
1009 $query.=" and biblioitems.dewey like ?";
1010 push(@bind,"$search->{'dewey'}%");
1012 } elsif ($search->{'dewey'} ne ''){
1013 $query="select * from biblioitems,biblio
1014 where biblio.biblionumber=biblioitems.biblionumber
1015 and biblioitems.dewey like ?";
1016 @bind=("$search->{'dewey'}%");
1017 } elsif ($search->{'illustrator'} ne '') {
1018 $query="select * from biblioitems,biblio
1019 where biblio.biblionumber=biblioitems.biblionumber
1020 and biblioitems.illus like ?";
1021 @bind=("%".$search->{'illustrator'}."%");
1022 } elsif ($search->{'publisher'} ne ''){
1023 $query = "Select * from biblio,biblioitems where biblio.biblionumber
1024 =biblioitems.biblionumber and (publishercode like ?)";
1025 @bind=("%$search->{'publisher'}%");
1026 } elsif ($search->{'abstract'} ne ''){
1027 $query = "Select * from biblio where abstract like ?";
1028 @bind=("%$search->{'abstract'}%");
1029 } elsif ($search->{'date-before'} ne ''){
1030 $query = "Select * from biblio where copyrightdate like ?";
1031 @bind=("%$search->{'date-before'}%");
1033 $query .=" group by biblio.biblionumber";
1036 if ($type eq 'subject'){
1037 my @key=split(' ',$search->{'subject'});
1040 $query="select * from bibliosubject, biblioitems where
1041 (bibliosubject.biblionumber = biblioitems.biblionumber) and ( subject like ? or subject like ? or subject like ?)";
1042 @bind=("$key[0]%","% $key[0]%","%($key[0])%");
1044 $query.=" and (subject like ? or subject like ? or subject like ?)";
1045 push(@bind,"$key[$i]%","% $key[$i]%","%($key[$i])%");
1049 # FIXME - Wouldn't it be better to fix the database so that if a
1050 # book has a subject "NZ", then it also gets added the subject
1052 # This can also be generalized by adding a table of subject
1053 # synonyms to the database: just declare "NZ" to be a synonym for
1054 # "New Zealand", "SF" a synonym for both "Science fiction" and
1055 # "Fantastic fiction", etc.
1057 if (lc($search->{'subject'}) eq 'nz'){
1058 $query.= " or (subject like 'NEW ZEALAND %' or subject like '% NEW ZEALAND %'
1059 or subject like '% NEW ZEALAND' or subject like '%(NEW ZEALAND)%' ) ";
1060 } elsif ( $search->{'subject'} =~ /^nz /i || $search->{'subject'} =~ / nz /i || $search->{'subject'} =~ / nz$/i){
1061 $query=~ s/ nz/ NEW ZEALAND/ig;
1062 $query=~ s/nz /NEW ZEALAND /ig;
1063 $query=~ s/\(nz\)/\(NEW ZEALAND\)/gi;
1066 if ($type eq 'precise'){
1067 if ($search->{'itemnumber'} ne ''){
1068 $query="select * from items,biblio ";
1069 my $search2=uc $search->{'itemnumber'};
1070 $query=$query." where
1071 items.biblionumber=biblio.biblionumber
1076 if ($search->{'isbn'} ne ''){
1077 my $search2=uc $search->{'isbn'};
1078 my $sth1=$dbh->prepare("select * from biblioitems where isbn=?");
1079 $sth1->execute($search2);
1081 while (my $data=$sth1->fetchrow_hashref) {
1082 my $sth=$dbh->prepare("select * from biblioitems,biblio where
1083 biblio.biblionumber = ?
1084 and biblioitems.biblionumber = biblio.biblionumber");
1085 $sth->execute($data->{'biblionumber'});
1086 # FIXME - There's already a $data in this scope.
1087 my $data=$sth->fetchrow_hashref;
1088 my ($dewey, $subclass) = ($data->{'dewey'}, $data->{'subclass'});
1089 # FIXME - The following assumes that the Dewey code is a
1090 # floating-point number. It isn't: it's a string.
1092 ($dewey == 0) && ($dewey='');
1093 ($dewey) && ($dewey.=" $subclass");
1094 $data->{'dewey'}=$dewey;
1095 $results[$i2]=$data;
1096 # $results[$i2]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey\t$data->{'isbn'}\t$data->{'itemtype'}";
1103 if ($type ne 'precise' && $type ne 'subject'){
1104 if ($search->{'author'} ne ''){
1105 $query .= " order by biblio.author,title";
1107 $query .= " order by title";
1110 if ($type eq 'subject'){
1111 $query .= " group by subject ";
1114 my $sth=$dbh->prepare($query);
1115 $sth->execute(@bind);
1118 my $limit= $num+$offset;
1119 while (my $data=$sth->fetchrow_hashref){
1120 my $query="select classification,dewey,subclass,publishercode from biblioitems where biblionumber=?";
1121 my @bind=($data->{'biblionumber'});
1122 if ($search->{'class'} ne ''){
1123 my @temp=split(/\|/,$search->{'class'});
1125 $query.= " and ( itemtype= ?";
1126 push(@bind,$temp[0]);
1127 for (my $i=1;$i<$count;$i++){
1128 $query.=" or itemtype=?";
1129 push(@bind,$temp[$i]);
1133 if ($search->{'dewey'} ne ''){
1134 $query.=" and dewey=? ";
1135 push(@bind,$search->{'dewey'});
1137 if ($search->{'illustrator'} ne ''){
1138 $query.=" and illus like ?";
1139 push(@bind,"%$search->{'illustrator'}%");
1141 if ($search->{'publisher'} ne ''){
1142 $query.= " and (publishercode like ?)";
1143 push(@bind,"%$search->{'publisher'}%");
1145 my $sti=$dbh->prepare($query);
1146 $sti->execute(@bind);
1153 if ($bibitemdata = $sti->fetchrow_hashref()){
1155 $classification=$bibitemdata->{'classification'};
1156 $dewey=$bibitemdata->{'dewey'};
1157 $subclass=$bibitemdata->{'subclass'};
1158 $publishercode=$bibitemdata->{'publishercode'};
1160 # print STDERR "$dewey $subclass $publishercode\n";
1161 # FIXME - The Dewey code is a string, not a number.
1163 ($dewey == 0) && ($dewey='');
1164 ($dewey) && ($dewey.=" $subclass");
1165 $data->{'classification'}=$classification;
1166 $data->{'dewey'}=$dewey;
1167 $data->{'publishercode'}=$publishercode;
1170 if ($count > $offset && $count <= $limit){
1179 return($count,@results);
1182 sub updatesearchstats{
1183 my ($dbh,$query)=@_;
1189 @results = &subsearch($env, $subject);
1191 Searches for books that have a subject that exactly matches
1194 C<&subsearch> returns an array of results. Each element of this array
1195 is a string, containing the book's title, author, and biblionumber,
1203 my ($env,$subject)=@_;
1204 my $dbh = C4::Context->dbh;
1205 my $sth=$dbh->prepare("Select * from biblio,bibliosubject where
1206 biblio.biblionumber=bibliosubject.biblionumber and
1207 bibliosubject.subject=? group by biblio.biblionumber
1208 order by biblio.title");
1209 $sth->execute($subject);
1212 while (my $data=$sth->fetchrow_hashref){
1213 push @results, $data;
1222 @results = &ItemInfo($env, $biblionumber, $type);
1224 Returns information about books with the given biblionumber.
1226 C<$type> may be either C<intra> or anything else. If it is not set to
1227 C<intra>, then the search will exclude lost, very overdue, and
1232 C<&ItemInfo> returns a list of references-to-hash. Each element
1233 contains a number of keys. Most of them are table items from the
1234 C<biblio>, C<biblioitems>, C<items>, and C<itemtypes> tables in the
1235 Koha database. Other keys include:
1239 =item C<$data-E<gt>{branchname}>
1241 The name (not the code) of the branch to which the book belongs.
1243 =item C<$data-E<gt>{datelastseen}>
1245 This is simply C<items.datelastseen>, except that while the date is
1246 stored in YYYY-MM-DD format in the database, here it is converted to
1247 DD/MM/YYYY format. A NULL date is returned as C<//>.
1249 =item C<$data-E<gt>{datedue}>
1251 =item C<$data-E<gt>{class}>
1253 This is the concatenation of C<biblioitems.classification>, the book's
1254 Dewey code, and C<biblioitems.subclass>.
1256 =item C<$data-E<gt>{ocount}>
1258 I think this is the number of copies of the book available.
1260 =item C<$data-E<gt>{order}>
1262 If this is set, it is set to C<One Order>.
1269 my ($env,$biblionumber,$type) = @_;
1270 my $dbh = C4::Context->dbh;
1271 my $query = "SELECT *,items.notforloan as itemnotforloan FROM items, biblio, biblioitems
1272 left join itemtypes on biblioitems.itemtype = itemtypes.itemtype
1273 WHERE items.biblionumber = ?
1274 AND biblioitems.biblioitemnumber = items.biblioitemnumber
1275 AND biblio.biblionumber = items.biblionumber";
1276 # buggy : opac & librarian interface can show the same info level & itemstatus should not be hardcoded
1277 # if ($type ne 'intra'){
1278 # $query .= " and ((items.itemlost<>1 and items.itemlost <> 2)
1279 # or items.itemlost is NULL)
1280 # and (wthdrawn <> 1 or wthdrawn is NULL)";
1282 $query .= " order by items.dateaccessioned desc";
1283 my $sth=$dbh->prepare($query);
1284 $sth->execute($biblionumber);
1287 while (my $data=$sth->fetchrow_hashref){
1289 my $isth=$dbh->prepare("Select issues.*,borrowers.cardnumber from issues,borrowers where itemnumber = ? and returndate is null and issues.borrowernumber=borrowers.borrowernumber");
1290 $isth->execute($data->{'itemnumber'});
1291 if (my $idata=$isth->fetchrow_hashref){
1292 $data->{borrowernumber} = $idata->{borrowernumber};
1293 $data->{cardnumber} = $idata->{cardnumber};
1294 $datedue = format_date($idata->{'date_due'});
1296 # buggy : hardcoded & non-translatable
1297 # more : why don't you want to show the datedue if it's very very overdue ?
1298 # if ($data->{'itemlost'} eq '2'){
1299 # $datedue='Very Overdue';
1301 # if ($data->{'itemlost'} eq '1'){
1304 # if ($data->{'wthdrawn'} eq '1'){
1305 # $datedue="Cancelled";
1307 if ($datedue eq ''){
1308 # $datedue="Available";
1309 my ($restype,$reserves)=C4::Reserves2::CheckReserves($data->{'itemnumber'});
1315 #get branch information.....
1316 my $bsth=$dbh->prepare("SELECT * FROM branches WHERE branchcode = ?");
1317 $bsth->execute($data->{'holdingbranch'});
1318 if (my $bdata=$bsth->fetchrow_hashref){
1319 $data->{'branchname'} = $bdata->{'branchname'};
1321 my $date=format_date($data->{'datelastseen'});
1322 $data->{'datelastseen'}=$date;
1323 $data->{'datedue'}=$datedue;
1324 # get notforloan complete status if applicable
1325 my $sthnflstatus = $dbh->prepare('select authorised_value from marc_subfield_structure where kohafield="items.notforloan"');
1326 $sthnflstatus->execute;
1327 my ($authorised_valuecode) = $sthnflstatus->fetchrow;
1328 if ($authorised_valuecode) {
1329 $sthnflstatus = $dbh->prepare("select lib from authorised_values where category=? and authorised_value=?");
1330 $sthnflstatus->execute($authorised_valuecode,$data->{itemnotforloan});
1331 my ($lib) = $sthnflstatus->fetchrow;
1332 $data->{notforloan} = $lib;
1338 #FIXME: ordering/indentation here looks wrong
1339 # buggy : count in $i+1 the info on qty ordered for $i : total shown is real total +1
1340 # useless : Koha 2.2.2 now automatically show the existing number of items
1341 # and if there is no items, and at least one is on order, show "on order".
1342 # my $sth2=$dbh->prepare("Select * from aqorders where biblionumber=?");
1343 # $sth2->execute($biblionumber);
1346 # if ($data=$sth2->fetchrow_hashref){
1347 # $ocount=$data->{'quantity'} - $data->{'quantityreceived'};
1349 # $data->{'ocount'}=$ocount;
1350 # $data->{'order'}="One Order";
1351 # $results[$i]=$data;
1360 @results = &GetItems($env, $biblionumber);
1362 Returns information about books with the given biblionumber.
1366 C<&GetItems> returns an array of strings. Each element is a
1367 tab-separated list of values: biblioitemnumber, itemtype,
1368 classification, Dewey number, subclass, ISBN, volume, number, and
1371 Itemdata, in turn, is a string of the form
1372 "I<barcode>C<[>I<holdingbranch>C<[>I<flags>" where I<flags> contains
1373 the string C<NFL> if the item is not for loan, and C<LOST> if the item
1379 my ($env,$biblionumber)=@_;
1380 #debug_msg($env,"GetItems");
1381 my $dbh = C4::Context->dbh;
1382 my $sth=$dbh->prepare("Select * from biblioitems where (biblionumber = ?)");
1383 $sth->execute($biblionumber);
1384 #debug_msg($env,"executed query");
1387 while (my $data=$sth->fetchrow_hashref) {
1388 #debug_msg($env,$data->{'biblioitemnumber'});
1389 my $dewey = $data->{'dewey'};
1391 my $line = $data->{'biblioitemnumber'}."\t".$data->{'itemtype'};
1392 $line .= "\t$data->{'classification'}\t$dewey";
1393 $line .= "\t$data->{'subclass'}\t$data->{isbn}";
1394 $line .= "\t$data->{'volume'}\t$data->{number}";
1395 my $isth= $dbh->prepare("select * from items where biblioitemnumber = ?");
1396 $isth->execute($data->{'biblioitemnumber'});
1397 while (my $idata = $isth->fetchrow_hashref) {
1398 my $iline = $idata->{'barcode'}."[".$idata->{'holdingbranch'}."[";
1399 if ($idata->{'notforloan'} == 1) {
1402 if ($idata->{'itemlost'} == 1) {
1405 $line .= "\t$iline";
1408 $results[$i] = $line;
1417 $item = &itemdata($barcode);
1419 Looks up the item with the given barcode, and returns a
1420 reference-to-hash containing information about that item. The keys of
1421 the hash are the fields from the C<items> and C<biblioitems> tables in
1428 my $dbh = C4::Context->dbh;
1429 my $sth=$dbh->prepare("Select * from items,biblioitems where barcode=?
1430 and items.biblioitemnumber=biblioitems.biblioitemnumber");
1431 $sth->execute($barcode);
1432 my $data=$sth->fetchrow_hashref;
1439 $data = &bibdata($biblionumber, $type);
1441 Returns information about the book with the given biblionumber.
1443 C<$type> is ignored.
1445 C<&bibdata> returns a reference-to-hash. The keys are the fields in
1446 the C<biblio>, C<biblioitems>, and C<bibliosubtitle> tables in the
1449 In addition, C<$data-E<gt>{subject}> is the list of the book's
1450 subjects, separated by C<" , "> (space, comma, space).
1452 If there are multiple biblioitems with the given biblionumber, only
1453 the first one is considered.
1458 my ($bibnum, $type) = @_;
1459 my $dbh = C4::Context->dbh;
1460 my $sth = $dbh->prepare("Select *, biblioitems.notes AS bnotes, biblio.notes
1461 from biblio, biblioitems
1462 left join bibliosubtitle on
1463 biblio.biblionumber = bibliosubtitle.biblionumber
1464 left join itemtypes on biblioitems.itemtype=itemtypes.itemtype
1465 where biblio.biblionumber = ?
1466 and biblioitems.biblionumber = biblio.biblionumber");
1467 $sth->execute($bibnum);
1469 $data = $sth->fetchrow_hashref;
1471 # handle management of repeated subtitle
1472 $sth = $dbh->prepare("Select * from bibliosubtitle where biblionumber = ?");
1473 $sth->execute($bibnum);
1475 while (my $dat = $sth->fetchrow_hashref){
1477 $line{subtitle} = $dat->{subtitle};
1478 push @subtitles, \%line;
1480 $data->{subtitles} = \@subtitles;
1482 $sth = $dbh->prepare("Select * from bibliosubject where biblionumber = ?");
1483 $sth->execute($bibnum);
1485 while (my $dat = $sth->fetchrow_hashref){
1487 $line{subject} = $dat->{'subject'};
1488 push @subjects, \%line;
1490 $data->{subjects} = \@subjects;
1492 $sth = $dbh->prepare("Select * from additionalauthors where biblionumber = ?");
1493 $sth->execute($bibnum);
1494 while (my $dat = $sth->fetchrow_hashref){
1495 $data->{'additionalauthors'} .= "$dat->{'author'} - ";
1497 chop $data->{'additionalauthors'};
1498 chop $data->{'additionalauthors'};
1499 chop $data->{'additionalauthors'};
1506 $itemdata = &bibitemdata($biblioitemnumber);
1508 Looks up the biblioitem with the given biblioitemnumber. Returns a
1509 reference-to-hash. The keys are the fields from the C<biblio>,
1510 C<biblioitems>, and C<itemtypes> tables in the Koha database, except
1511 that C<biblioitems.notes> is given as C<$itemdata-E<gt>{bnotes}>.
1517 my $dbh = C4::Context->dbh;
1518 my $sth = $dbh->prepare("Select *,biblioitems.notes as bnotes from biblio, biblioitems,itemtypes where biblio.biblionumber = biblioitems.biblionumber and biblioitemnumber = ? and biblioitems.itemtype = itemtypes.itemtype");
1521 $sth->execute($bibitem);
1523 $data = $sth->fetchrow_hashref;
1531 ($count, $subjects) = &subject($biblionumber);
1533 Looks up the subjects of the book with the given biblionumber. Returns
1534 a two-element list. C<$subjects> is a reference-to-array, where each
1535 element is a subject of the book, and C<$count> is the number of
1536 elements in C<$subjects>.
1542 my $dbh = C4::Context->dbh;
1543 my $sth=$dbh->prepare("Select * from bibliosubject where biblionumber=?");
1544 $sth->execute($bibnum);
1547 while (my $data=$sth->fetchrow_hashref){
1552 return($i,\@results);
1557 ($count, $authors) = &addauthors($biblionumber);
1559 Looks up the additional authors for the book with the given
1562 Returns a two-element list. C<$authors> is a reference-to-array, where
1563 each element is an additional author, and C<$count> is the number of
1564 elements in C<$authors>.
1570 my $dbh = C4::Context->dbh;
1571 my $sth=$dbh->prepare("Select * from additionalauthors where biblionumber=?");
1572 $sth->execute($bibnum);
1575 while (my $data=$sth->fetchrow_hashref){
1580 return($i,\@results);
1585 ($count, $subtitles) = &subtitle($biblionumber);
1587 Looks up the subtitles for the book with the given biblionumber.
1589 Returns a two-element list. C<$subtitles> is a reference-to-array,
1590 where each element is a subtitle, and C<$count> is the number of
1591 elements in C<$subtitles>.
1597 my $dbh = C4::Context->dbh;
1598 my $sth=$dbh->prepare("Select * from bibliosubtitle where biblionumber=?");
1599 $sth->execute($bibnum);
1602 while (my $data=$sth->fetchrow_hashref){
1607 return($i,\@results);
1612 @issues = &itemissues($biblioitemnumber, $biblio);
1614 Looks up information about who has borrowed the bookZ<>(s) with the
1615 given biblioitemnumber.
1617 C<$biblio> is ignored.
1619 C<&itemissues> returns an array of references-to-hash. The keys
1620 include the fields from the C<items> table in the Koha database.
1621 Additional keys include:
1627 If the item is currently on loan, this gives the due date.
1629 If the item is not on loan, then this is either "Available" or
1630 "Cancelled", if the item has been withdrawn.
1634 If the item is currently on loan, this gives the card number of the
1635 patron who currently has the item.
1637 =item C<timestamp0>, C<timestamp1>, C<timestamp2>
1639 These give the timestamp for the last three times the item was
1642 =item C<card0>, C<card1>, C<card2>
1644 The card number of the last three patrons who borrowed this item.
1646 =item C<borrower0>, C<borrower1>, C<borrower2>
1648 The borrower number of the last three patrons who borrowed this item.
1655 my ($bibitem, $biblio)=@_;
1656 my $dbh = C4::Context->dbh;
1657 # FIXME - If this function die()s, the script will abort, and the
1658 # user won't get anything; depending on how far the script has
1659 # gotten, the user might get a blank page. It would be much better
1660 # to at least print an error message. The easiest way to do this
1661 # is to set $SIG{__DIE__}.
1662 my $sth = $dbh->prepare("Select * from items where
1663 items.biblioitemnumber = ?")
1664 || die $dbh->errstr;
1668 $sth->execute($bibitem)
1669 || die $sth->errstr;
1671 while (my $data = $sth->fetchrow_hashref) {
1672 # Find out who currently has this item.
1673 # FIXME - Wouldn't it be better to do this as a left join of
1674 # some sort? Currently, this code assumes that if
1675 # fetchrow_hashref() fails, then the book is on the shelf.
1676 # fetchrow_hashref() can fail for any number of reasons (e.g.,
1677 # database server crash), not just because no items match the
1679 my $sth2 = $dbh->prepare("select * from issues,borrowers
1680 where itemnumber = ?
1681 and returndate is NULL
1682 and issues.borrowernumber = borrowers.borrowernumber");
1684 $sth2->execute($data->{'itemnumber'});
1685 if (my $data2 = $sth2->fetchrow_hashref) {
1686 $data->{'date_due'} = $data2->{'date_due'};
1687 $data->{'card'} = $data2->{'cardnumber'};
1688 $data->{'borrower'} = $data2->{'borrowernumber'};
1690 if ($data->{'wthdrawn'} eq '1') {
1691 $data->{'date_due'} = 'Cancelled';
1693 $data->{'date_due'} = 'Available';
1699 # Find the last 3 people who borrowed this item.
1700 $sth2 = $dbh->prepare("select * from issues, borrowers
1701 where itemnumber = ?
1702 and issues.borrowernumber = borrowers.borrowernumber
1703 and returndate is not NULL
1704 order by returndate desc,timestamp desc") || die $dbh->errstr;
1705 $sth2->execute($data->{'itemnumber'}) || die $sth2->errstr;
1706 for (my $i2 = 0; $i2 < 2; $i2++) { # FIXME : error if there is less than 3 pple borrowing this item
1707 if (my $data2 = $sth2->fetchrow_hashref) {
1708 $data->{"timestamp$i2"} = $data2->{'timestamp'};
1709 $data->{"card$i2"} = $data2->{'cardnumber'};
1710 $data->{"borrower$i2"} = $data2->{'borrowernumber'};
1715 $results[$i] = $data;
1725 $item = &itemnodata($env, $dbh, $biblioitemnumber);
1727 Looks up the item with the given biblioitemnumber.
1729 C<$env> and C<$dbh> are ignored.
1731 C<&itemnodata> returns a reference-to-hash whose keys are the fields
1732 from the C<biblio>, C<biblioitems>, and C<items> tables in the Koha
1738 my ($env,$dbh,$itemnumber) = @_;
1739 $dbh = C4::Context->dbh;
1740 my $sth=$dbh->prepare("Select * from biblio,items,biblioitems
1741 where items.itemnumber = ?
1742 and biblio.biblionumber = items.biblionumber
1743 and biblioitems.biblioitemnumber = items.biblioitemnumber");
1745 $sth->execute($itemnumber);
1746 my $data=$sth->fetchrow_hashref;
1753 ($count, $borrowers) = &BornameSearch($env, $searchstring, $type);
1755 Looks up patrons (borrowers) by name.
1759 BUGFIX 499: C<$type> is now used to determine type of search.
1760 if $type is "simple", search is performed on the first letter of the
1763 C<$searchstring> is a space-separated list of search terms. Each term
1764 must match the beginning a borrower's surname, first name, or other
1767 C<&BornameSearch> returns a two-element list. C<$borrowers> is a
1768 reference-to-array; each element is a reference-to-hash, whose keys
1769 are the fields of the C<borrowers> table in the Koha database.
1770 C<$count> is the number of elements in C<$borrowers>.
1774 #used by member enquiries from the intranet
1775 #called by member.pl
1777 my ($env,$searchstring,$orderby,$type)=@_;
1778 my $dbh = C4::Context->dbh;
1779 my $query = ""; my $count; my @data;
1782 if($type eq "simple") # simple search for one letter only
1784 $query="Select * from borrowers where surname like ? order by $orderby";
1785 @bind=("$searchstring%");
1787 else # advanced search looking in surname, firstname and othernames
1789 @data=split(' ',$searchstring);
1791 $query="Select * from borrowers
1792 where ((surname like ? or surname like ?
1793 or firstname like ? or firstname like ?
1794 or othernames like ? or othernames like ?)
1796 @bind=("$data[0]%","% $data[0]%","$data[0]%","% $data[0]%","$data[0]%","% $data[0]%");
1797 for (my $i=1;$i<$count;$i++){
1798 $query=$query." and (".
1799 " surname like ? or surname like ?
1800 or firstname like ? or firstname like ?
1801 or othernames like ? or othernames like ?)";
1802 push(@bind,"$data[$i]%","% $data[$i]%","$data[$i]%","% $data[$i]%","$data[$i]%","% $data[$i]%");
1805 $query=$query.") or cardnumber like ?
1807 push(@bind,$searchstring);
1811 my $sth=$dbh->prepare($query);
1812 # warn "Q $orderby : $query";
1813 $sth->execute(@bind);
1816 while (my $data=$sth->fetchrow_hashref){
1817 push(@results,$data);
1821 return ($cnt,\@results);
1826 $borrower = &borrdata($cardnumber, $borrowernumber);
1828 Looks up information about a patron (borrower) by either card number
1829 or borrower number. If $borrowernumber is specified, C<&borrdata>
1830 searches by borrower number; otherwise, it searches by card number.
1832 C<&borrdata> returns a reference-to-hash whose keys are the fields of
1833 the C<borrowers> table in the Koha database.
1838 my ($cardnumber,$bornum)=@_;
1839 $cardnumber = uc $cardnumber;
1840 my $dbh = C4::Context->dbh;
1843 $sth=$dbh->prepare("Select * from borrowers where cardnumber=?");
1844 $sth->execute($cardnumber);
1846 $sth=$dbh->prepare("Select * from borrowers where borrowernumber=?");
1847 $sth->execute($bornum);
1849 my $data=$sth->fetchrow_hashref;
1853 } else { # try with firstname
1855 my $sth=$dbh->prepare("select * from borrowers where firstname=?");
1856 $sth->execute($cardnumber);
1857 my $data=$sth->fetchrow_hashref;
1867 ($count, $issues) = &borrissues($borrowernumber);
1869 Looks up what the patron with the given borrowernumber has borrowed.
1871 C<&borrissues> returns a two-element array. C<$issues> is a
1872 reference-to-array, where each element is a reference-to-hash; the
1873 keys are the fields from the C<issues>, C<biblio>, and C<items> tables
1874 in the Koha database. C<$count> is the number of elements in
1881 my $dbh = C4::Context->dbh;
1882 my $sth=$dbh->prepare("Select * from issues,biblio,items where borrowernumber=?
1883 and items.itemnumber=issues.itemnumber
1884 and items.biblionumber=biblio.biblionumber
1885 and issues.returndate is NULL order by date_due");
1886 $sth->execute($bornum);
1888 while (my $data = $sth->fetchrow_hashref) {
1889 push @result, $data;
1892 return(scalar(@result), \@result);
1897 ($count, $issues) = &allissues($borrowernumber, $sortkey, $limit);
1899 Looks up what the patron with the given borrowernumber has borrowed,
1900 and sorts the results.
1902 C<$sortkey> is the name of a field on which to sort the results. This
1903 should be the name of a field in the C<issues>, C<biblio>,
1904 C<biblioitems>, or C<items> table in the Koha database.
1906 C<$limit> is the maximum number of results to return.
1908 C<&allissues> returns a two-element array. C<$issues> is a
1909 reference-to-array, where each element is a reference-to-hash; the
1910 keys are the fields from the C<issues>, C<biblio>, C<biblioitems>, and
1911 C<items> tables of the Koha database. C<$count> is the number of
1912 elements in C<$issues>
1917 my ($bornum,$order,$limit)=@_;
1918 #FIXME: sanity-check order and limit
1919 my $dbh = C4::Context->dbh;
1920 my $query="Select * from issues,biblio,items,biblioitems
1921 where borrowernumber=? and
1922 items.biblioitemnumber=biblioitems.biblioitemnumber and
1923 items.itemnumber=issues.itemnumber and
1924 items.biblionumber=biblio.biblionumber order by $order";
1926 $query.=" limit $limit";
1929 my $sth=$dbh->prepare($query);
1930 $sth->execute($bornum);
1933 while (my $data=$sth->fetchrow_hashref){
1938 return($i,\@result);
1943 ($borrowed, $due, $fine) = &borrdata2($env, $borrowernumber);
1945 Returns aggregate data about items borrowed by the patron with the
1946 given borrowernumber.
1950 C<&borrdata2> returns a three-element array. C<$borrowed> is the
1951 number of books the patron currently has borrowed. C<$due> is the
1952 number of overdue items the patron currently has borrowed. C<$fine> is
1953 the total fine currently due by the borrower.
1958 my ($env,$bornum)=@_;
1959 my $dbh = C4::Context->dbh;
1960 my $query="Select count(*) from issues where borrowernumber='$bornum' and
1961 returndate is NULL";
1963 my $sth=$dbh->prepare($query);
1965 my $data=$sth->fetchrow_hashref;
1967 $sth=$dbh->prepare("Select count(*) from issues where
1968 borrowernumber='$bornum' and date_due < now() and returndate is NULL");
1970 my $data2=$sth->fetchrow_hashref;
1972 $sth=$dbh->prepare("Select sum(amountoutstanding) from accountlines where
1973 borrowernumber='$bornum'");
1975 my $data3=$sth->fetchrow_hashref;
1978 return($data2->{'count(*)'},$data->{'count(*)'},$data3->{'sum(amountoutstanding)'});
1981 =item getboracctrecord
1983 ($count, $acctlines, $total) = &getboracctrecord($env, $borrowernumber);
1985 Looks up accounting data for the patron with the given borrowernumber.
1989 (FIXME - I'm not at all sure what this is about.)
1991 C<&getboracctrecord> returns a three-element array. C<$acctlines> is a
1992 reference-to-array, where each element is a reference-to-hash; the
1993 keys are the fields of the C<accountlines> table in the Koha database.
1994 C<$count> is the number of elements in C<$acctlines>. C<$total> is the
1995 total amount outstanding for all of the account lines.
1999 sub getboracctrecord {
2000 my ($env,$params) = @_;
2001 my $dbh = C4::Context->dbh;
2004 my $sth=$dbh->prepare("Select * from accountlines where
2005 borrowernumber=? order by date desc,timestamp desc");
2007 $sth->execute($params->{'borrowernumber'});
2009 while (my $data=$sth->fetchrow_hashref){
2010 #FIXME before reinstating: insecure?
2011 # if ($data->{'itemnumber'} ne ''){
2012 # $query="Select * from items,biblio where items.itemnumber=
2013 # '$data->{'itemnumber'}' and biblio.biblionumber=items.biblionumber";
2014 # my $sth2=$dbh->prepare($query);
2016 # my $data2=$sth2->fetchrow_hashref;
2020 $acctlines[$numlines] = $data;
2022 $total += $data->{'amountoutstanding'};
2025 return ($numlines,\@acctlines,$total);
2030 $counts = &itemcount2($env, $biblionumber, $type);
2032 Counts the number of items with the given biblionumber, broken down by
2037 C<$type> may be either C<intra> or anything else. If it is not set to
2038 C<intra>, then the search will exclude lost, very overdue, and
2041 C<$&itemcount2> returns a reference-to-hash, with the following fields:
2047 The total number of items with this biblionumber.
2051 The number of items on order (aqorders.quantity -
2052 aqorders.quantityreceived).
2056 For each branch that has at least one copy of the book, C<$counts>
2057 will have a key with the branch name, giving the number of copies at
2065 my ($env,$bibnum,$type)=@_;
2066 my $dbh = C4::Context->dbh;
2067 my $query="Select * from items,branches where
2068 biblionumber=? and items.holdingbranch=branches.branchcode";
2069 if ($type ne 'intra'){
2070 $query.=" and ((itemlost <>1 and itemlost <> 2) or itemlost is NULL) and
2071 (wthdrawn <> 1 or wthdrawn is NULL)";
2073 my $sth=$dbh->prepare($query);
2075 $sth->execute($bibnum);
2078 while (my $data=$sth->fetchrow_hashref){
2085 'select * from items
2087 and not ((items.itemlost <>1 and items.itemlost <> 2)
2088 or items.itemlost is NULL)'
2091 'select * from items
2092 where itemnumber=? and not (wthdrawn <> 1 or wthdrawn is NULL)'
2094 'On Loan', "select * from issues,items
2095 where issues.itemnumber=? and returndate is NULL
2096 and items.itemnumber=issues.itemnumber"
2099 my($testlabel, $query2) = @$test;
2101 my $sth2=$dbh->prepare($query2);
2102 $sth2->execute($data->{'itemnumber'});
2104 # FIXME - fetchrow_hashref() can fail for any number of reasons
2105 # (e.g., a database server crash). Perhaps use a left join of some
2107 $status = $testlabel if $sth2->fetchrow_hashref;
2109 last if defined $status;
2111 $status = $data->{'branchname'} unless defined $status;
2114 my $sth2=$dbh->prepare("Select * from aqorders where biblionumber=? and
2115 datecancellationprinted is NULL and quantity > quantityreceived");
2116 $sth2->execute($bibnum);
2117 if (my $data=$sth2->fetchrow_hashref){
2118 $counts{'order'}=$data->{'quantity'} - $data->{'quantityreceived'};
2127 $description = &ItemType($itemtype);
2129 Given an item type code, returns the description for that type.
2134 # FIXME - I'm pretty sure that after the initial setup, the list of
2135 # item types doesn't change very often. Hence, it seems slow and
2136 # inefficient to make yet another database call to look up information
2137 # that'll only change every few months or years.
2139 # Much better, I think, to automatically build a Perl file that can be
2140 # included in those scripts that require it, e.g.:
2141 # @itemtypes = qw( ART BCD CAS CD F ... );
2143 # ART => "Art Prints",
2144 # BCD => "CD-ROM from book",
2145 # CD => "Compact disc (WN)",
2146 # F => "Free Fiction",
2149 # The web server can then run a cron job to rebuild this file from the
2150 # database every hour or so.
2152 # The same thing goes for branches, book funds, book sellers, currency
2153 # rates, printers, stopwords, and perhaps others.
2156 my $dbh = C4::Context->dbh;
2157 my $sth=$dbh->prepare("select description from itemtypes where itemtype=?");
2158 $sth->execute($type);
2159 my $dat=$sth->fetchrow_hashref;
2161 return ($dat->{'description'});
2166 ($count, @results) = &bibitems($biblionumber);
2168 Given the biblionumber for a book, C<&bibitems> looks up that book's
2169 biblioitems (different publications of the same book, the audio book
2170 and film versions, etc.).
2172 C<$count> is the number of elements in C<@results>.
2174 C<@results> is an array of references-to-hash; the keys are the fields
2175 of the C<biblioitems> and C<itemtypes> tables of the Koha database. In
2176 addition, C<itemlost> indicates the availability of the item: if it is
2177 "2", then all copies of the item are long overdue; if it is "1", then
2178 all copies are lost; otherwise, there is at least one copy available.
2184 my $dbh = C4::Context->dbh;
2185 my $sth = $dbh->prepare("SELECT biblioitems.*,
2187 MIN(items.itemlost) as itemlost,
2188 MIN(items.dateaccessioned) as dateaccessioned
2189 FROM biblioitems, itemtypes, items
2190 WHERE biblioitems.biblionumber = ?
2191 AND biblioitems.itemtype = itemtypes.itemtype
2192 AND biblioitems.biblioitemnumber = items.biblioitemnumber
2193 GROUP BY items.biblioitemnumber");
2196 $sth->execute($bibnum);
2197 while (my $data = $sth->fetchrow_hashref) {
2198 $results[$count] = $data;
2202 return($count, @results);
2207 @barcodes = &barcodes($biblioitemnumber);
2209 Given a biblioitemnumber, looks up the corresponding items.
2211 Returns an array of references-to-hash; the keys are C<barcode> and
2214 The returned items include very overdue items, but not lost ones.
2219 #called from request.pl
2220 my ($biblioitemnumber)=@_;
2221 my $dbh = C4::Context->dbh;
2222 my $sth=$dbh->prepare("SELECT barcode, itemlost, holdingbranch FROM items
2223 WHERE biblioitemnumber = ?
2224 AND (wthdrawn <> 1 OR wthdrawn IS NULL)");
2225 $sth->execute($biblioitemnumber);
2228 while (my $data=$sth->fetchrow_hashref){
2229 $barcodes[$i]=$data;
2238 ($count, @websites) = &getwebsites($biblionumber);
2240 Looks up the web sites pertaining to the book with the given
2243 C<$count> is the number of elements in C<@websites>.
2245 C<@websites> is an array of references-to-hash; the keys are the
2246 fields from the C<websites> table in the Koha database.
2251 my ($biblionumber) = @_;
2252 my $dbh = C4::Context->dbh;
2253 my $sth = $dbh->prepare("Select * from websites where biblionumber = ?");
2257 $sth->execute($biblionumber);
2258 while (my $data = $sth->fetchrow_hashref) {
2259 # FIXME - The URL scheme shouldn't be stripped off, at least
2260 # not here, since it's part of the URL, and will be useful in
2261 # constructing a link to the site. If you don't want the user
2262 # to see the "http://" part, strip that off when building the
2264 $data->{'url'} =~ s/^http:\/\///; # FIXME - Leaning toothpick
2266 $results[$count] = $data;
2271 return($count, @results);
2274 =item getwebbiblioitems
2276 ($count, @results) = &getwebbiblioitems($biblionumber);
2278 Given a book's biblionumber, looks up the web versions of the book
2279 (biblioitems with itemtype C<WEB>).
2281 C<$count> is the number of items in C<@results>. C<@results> is an
2282 array of references-to-hash; the keys are the items from the
2283 C<biblioitems> table of the Koha database.
2287 sub getwebbiblioitems {
2288 my ($biblionumber) = @_;
2289 my $dbh = C4::Context->dbh;
2290 my $sth = $dbh->prepare("Select * from biblioitems where biblionumber = ?
2291 and itemtype = 'WEB'");
2295 $sth->execute($biblionumber);
2296 while (my $data = $sth->fetchrow_hashref) {
2297 $data->{'url'} =~ s/^http:\/\///;
2298 $results[$count] = $data;
2303 return($count, @results);
2304 } # sub getwebbiblioitems
2310 ($count, @results) = &isbnsearch($isbn,$title);
2312 Given an isbn and/or a title, returns the biblios having it.
2313 Used in acqui.simple, isbnsearch.pl only
2315 C<$count> is the number of items in C<@results>. C<@results> is an
2316 array of references-to-hash; the keys are the items from the
2317 C<biblioitems> table of the Koha database.
2322 my ($isbn,$title) = @_;
2323 my $dbh = C4::Context->dbh;
2329 $query = "Select distinct biblio.*, biblioitems.classification from biblio, biblioitems where
2330 biblio.biblionumber = biblioitems.biblionumber";
2333 $query .= " and isbn like ?";
2334 @bind=(uc($isbn)."%");
2337 $query .= " and title like ?";
2340 $sth = $dbh->prepare($query);
2342 $sth->execute(@bind);
2343 while (my $data = $sth->fetchrow_hashref) {
2344 $results[$count] = $data;
2349 return($count, @results);
2354 $branchname = &getbranchname($branchcode);
2356 Given the branch code, the function returns the corresponding
2357 branch name for a comprehensive information display
2363 my ($branchcode) = @_;
2364 my $dbh = C4::Context->dbh;
2365 my $sth = $dbh->prepare("SELECT branchname FROM branches WHERE branchcode = ?");
2366 $sth->execute($branchcode);
2367 my $branchname = $sth->fetchrow();
2370 } # sub getbranchname
2372 =item getborrowercategory
2374 $description = &getborrowercategory($categorycode);
2376 Given the borrower's category code, the function returns the corresponding
2377 description for a comprehensive information display.
2381 sub getborrowercategory
2384 my $dbh = C4::Context->dbh;
2385 my $sth = $dbh->prepare("SELECT description FROM categories WHERE categorycode = ?");
2386 $sth->execute($catcode);
2387 my $description = $sth->fetchrow();
2389 return $description;
2390 } # sub getborrowercategory
2393 END { } # module clean-up code here (global destructor)
2402 Koha Developement team <info@koha.org>