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 &getwebsites &getwebbiblioitems &catalogsearch &itemcount2
66 &isbnsearch &getbranchname &getborrowercategory);
67 # make all your functions, whether exported or not;
70 =item NewBorrowerNumber
72 $num = &NewBorrowerNumber();
74 Allocates a new, unused borrower number, and returns it.
78 # FIXME - This is identical to C4::Circulation::Borrower::NewBorrowerNumber.
79 # Pick one and stick with it. Preferably use the other one. This function
80 # doesn't belong in C4::Search.
81 sub NewBorrowerNumber {
82 my $dbh = C4::Context->dbh;
83 my $sth=$dbh->prepare("Select max(borrowernumber) from borrowers");
85 my $data=$sth->fetchrow_hashref;
87 $data->{'max(borrowernumber)'}++;
88 return($data->{'max(borrowernumber)'});
93 ($count, @results) = &catalogsearch($env, $type, $search, $num, $offset);
95 This is primarily a front-end to other, more specialized catalog
96 search functions: if C<$search-E<gt>{itemnumber}> or
97 C<$search-E<gt>{isbn}> is given, C<&catalogsearch> uses a precise
98 C<&CatSearch>. If $search->{subject} is given, it runs a subject
99 C<&CatSearch>. If C<$search-E<gt>{keyword}> is given, it runs a
100 C<&KeywordSearch>. Otherwise, it runs a loose C<&CatSearch>.
102 If C<$env-E<gt>{itemcount}> is 1, then C<&catalogsearch> also counts
103 the items for each result, and adds several keys:
109 The total number of copies of this book.
111 =item C<locationhash>
113 This is a reference-to-hash; the keys are the names of branches where
114 this book may be found, and the values are the number of copies at
119 A descriptive string saying where the book is located, and how many
120 copies there are, if greater than 1.
124 The book's subject, with spaces replaced with C<%20>, presumably for
132 my ($env,$type,$search,$num,$offset)=@_;
133 my $dbh = C4::Context->dbh;
134 # foreach my $key (%$search){
135 # $search->{$key}=$dbh->quote($search->{$key});
137 my ($count,@results);
138 if ($search->{'itemnumber'} ne '' || $search->{'isbn'} ne ''){
139 print STDERR "Doing a precise search\n";
140 ($count,@results)=CatSearch($env,'precise',$search,$num,$offset);
141 } elsif ($search->{'subject'} ne ''){
142 ($count,@results)=CatSearch($env,'subject',$search,$num,$offset);
143 } elsif ($search->{'keyword'} ne ''){
144 ($count,@results)=&KeywordSearch($env,'keyword',$search,$num,$offset);
146 ($count,@results)=CatSearch($env,'loose',$search,$num,$offset);
149 if ($env->{itemcount} eq '1') {
150 foreach my $data (@results){
151 my ($counts) = itemcount2($env, $data->{'biblionumber'}, 'intra');
152 my $subject2=$data->{'subject'};
153 $subject2=~ s/ /%20/g;
154 $data->{'itemcount'}=$counts->{'total'};
155 my $totalitemcounts=0;
156 foreach my $key (keys %$counts){
157 if ($key ne 'total'){ # FIXME - Should ignore 'order', too.
158 #$data->{'location'}.="$key $counts->{$key} ";
159 $totalitemcounts+=$counts->{$key};
160 $data->{'locationhash'}->{$key}=$counts->{$key};
164 my $locationtextonly='';
165 my $notavailabletext='';
166 foreach (sort keys %{$data->{'locationhash'}}) {
167 if ($_ eq 'notavailable') {
168 $notavailabletext="Not available";
169 my $c=$data->{'locationhash'}->{$_};
170 $data->{'not-available-p'}=$totalitemcounts;
171 if ($totalitemcounts>1) {
172 $notavailabletext.=" ($c)";
173 $data->{'not-available-plural-p'}=1;
177 my $c=$data->{'locationhash'}->{$_};
178 if ($_ eq 'Item Lost') {
179 $data->{'lost-p'}=$totalitemcounts;
180 $data->{'lost-plural-p'}=1
181 if $totalitemcounts > 1;
182 } elsif ($_ eq 'Withdrawn') {
183 $data->{'withdrawn-p'}=$totalitemcounts;
184 $data->{'withdrawn-plural-p'}=1
185 if $totalitemcounts > 1;
186 } elsif ($_ eq 'On Loan') {
187 $data->{'on-loan-p'}=$totalitemcounts;
188 $data->{'on-loan-plural-p'}=1
189 if $totalitemcounts > 1;
191 $locationtextonly.=$_;
192 $locationtextonly.=" ($c), "
193 if $totalitemcounts>1;
195 if ($totalitemcounts>1) {
196 $locationtext.=" ($c), ";
200 if ($notavailabletext) {
201 $locationtext.=$notavailabletext;
203 $locationtext=~s/, $//;
205 $data->{'location'}=$locationtext;
206 $data->{'location-only'}=$locationtextonly;
207 $data->{'subject2'}=$subject2;
208 $data->{'use-location-flags-p'}=1; # XXX
211 return ($count,@results);
216 $search = { "keyword" => "One or more keywords",
217 "class" => "VID|CD", # Limit search to fiction and CDs
220 ($count, @results) = &KeywordSearch($env, $type, $search, $num, $offset);
222 C<&KeywordSearch> searches the catalog by keyword: given a string
223 (C<$search-E<gt>{"keyword"}> consisting of a space-separated list of
224 keywords, it looks for books that contain any of those keywords in any
225 of a number of places.
227 C<&KeywordSearch> looks for keywords in the book title (and subtitle),
228 series name, notes (both C<biblio.notes> and C<biblioitems.notes>),
231 C<$search-E<gt>{"class"}> can be set to a C<|> (pipe)-separated list of
232 item class codes (e.g., "F" for fiction, "JNF" for junior nonfiction,
233 etc.). In this case, the search will be restricted to just those
236 If C<$search-E<gt>{"class"}> is not specified, you may specify
237 C<$search-E<gt>{"dewey"}>. This will restrict the search to that
238 particular Dewey Decimal Classification category. Setting
239 C<$search-E<gt>{"dewey"}> to "513" will return books about arithmetic,
240 whereas setting it to "5" will return all books with Dewey code 5I<xx>
241 (Science and Mathematics).
243 C<$env> and C<$type> are ignored.
245 C<$offset> and C<$num> specify the subset of results to return.
246 C<$num> specifies the number of results to return, and C<$offset> is
247 the number of the first result. Thus, setting C<$offset> to 100 and
248 C<$num> to 5 will return results 100 through 104 inclusive.
253 my ($env,$type,$search,$num,$offset)=@_;
254 my $dbh = C4::Context->dbh;
255 $search->{'keyword'}=~ s/ +$//;
256 my @key=split(' ',$search->{'keyword'});
257 # FIXME - Naive users might enter comma-separated
258 # words, e.g., "training, animal". Ought to cope with
262 my %biblionumbers; # Set of biblionumbers returned by the
265 # FIXME - Ought to filter the stopwords out of the list of keywords.
266 # @key = map { !defined($stopwords{$_}) } @key;
268 # FIXME - The way this code is currently set up, it looks for all of
269 # the keywords first in (title, notes, seriestitle), then in the
270 # subtitle, then in the subject. Thus, if you look for keywords
271 # "science fiction", this search won't find a book with
272 # title = "How to write fiction"
273 # subtitle = "A science-based approach"
274 # Is this the desired effect? If not, then the first SQL query
275 # should look in the biblio, subtitle, and subject tables all at
276 # once. The way the first query is built can accomodate this easily.
278 # Look for keywords in table 'biblio'.
280 # Build an SQL query that finds each of the keywords in any of the
281 # title, biblio.notes, or seriestitle. To do this, we'll build up an
282 # array of clauses, one for each keyword.
283 my $query; # The SQL query
284 my @clauses = (); # The search clauses
285 my @bind = (); # The term bindings
287 $query = <<EOT; # Beginning of the query
292 foreach my $keyword (@key)
294 my @subclauses = (); # Subclauses, one for each field we're
297 # For each field we're searching on, create a subclause that'll
298 # match the current keyword in the current field.
299 foreach my $field (qw(title notes seriestitle author))
302 "$field LIKE ? OR $field LIKE ?";
303 push(@bind,"\Q$keyword\E%","% \Q$keyword\E%");
305 # (Yes, this could have been done as
306 # @subclauses = map {...} qw(field1 field2 ...)
307 # )but I think this way is more readable.
309 # Construct the current clause by joining the subclauses.
310 push @clauses, "(" . join(")\n\tOR (", @subclauses) . ")";
312 # Now join all of the clauses together and append to the query.
313 $query .= "(" . join(")\nAND (", @clauses) . ")";
315 # FIXME - Perhaps use $sth->bind_columns() ? Documented as the most
316 # efficient way to fetch data.
317 my $sth=$dbh->prepare($query);
318 $sth->execute(@bind);
319 while (my @res = $sth->fetchrow_array) {
322 $biblionumbers{$_} = 1; # Add these results to the set
327 # Now look for keywords in the 'bibliosubtitle' table.
329 # Again, we build a list of clauses from the keywords.
332 $query = "SELECT biblionumber FROM bibliosubtitle WHERE ";
333 foreach my $keyword (@key)
336 "subtitle LIKE ? OR subtitle like ?";
337 push(@bind,"\Q$keyword\E%","% \Q$keyword\E%");
339 $query .= "(" . join(") AND (", @clauses) . ")";
341 $sth=$dbh->prepare($query);
342 $sth->execute(@bind);
343 while (my @res = $sth->fetchrow_array) {
346 $biblionumbers{$_} = 1; # Add these results to the set
351 # Look for the keywords in the notes for individual items
352 # ('biblioitems.notes')
354 # Again, we build a list of clauses from the keywords.
357 $query = "SELECT biblionumber FROM biblioitems WHERE ";
358 foreach my $keyword (@key)
361 "notes LIKE ? OR notes like ?";
362 push(@bind,"\Q$keyword\E%","% \Q$keyword\E%");
364 $query .= "(" . join(") AND (", @clauses) . ")";
366 $sth=$dbh->prepare($query);
367 $sth->execute(@bind);
368 while (my @res = $sth->fetchrow_array) {
371 $biblionumbers{$_} = 1; # Add these results to the set
376 # Look for keywords in the 'bibliosubject' table.
378 # FIXME - The other queries look for words in the desired field that
379 # begin with the individual keywords the user entered. This one
380 # searches for the literal string the user entered. Is this the
382 # Note in particular that spaces are retained: if the user typed
384 # (with two spaces), this won't find the subject "science fiction"
385 # (one space). Likewise, a search for "%" will return absolutely
387 # If this isn't the desired effect, see the previous searches for
390 $sth=$dbh->prepare("Select biblionumber from bibliosubject where subject
391 like ? group by biblionumber");
392 $sth->execute("%$search->{'keyword'}%");
394 while (my @res = $sth->fetchrow_array) {
397 $biblionumbers{$_} = 1; # Add these results to the set
407 my @res = keys %biblionumbers;
411 # print "count $count";
412 if ($search->{'class'} ne ''){
414 my $query="select * from biblio,biblioitems where
415 biblio.biblionumber=? and
416 biblio.biblionumber=biblioitems.biblionumber ";
417 my @bind = ($res[$i2]);
418 if ($search->{'class'} ne ''){ # FIXME - Redundant
419 my @temp=split(/\|/,$search->{'class'});
421 $query.= "and ( itemtype=?";
422 push(@bind,$temp[0]);
423 for (my $i=1;$i<$count;$i++){
424 $query.=" or itemtype=?";
425 push(@bind,$temp[$i]);
429 my $sth=$dbh->prepare($query);
431 $sth->execute(@bind);
432 if (my $data2=$sth->fetchrow_hashref){
433 my $dewey= $data2->{'dewey'};
434 my $subclass=$data2->{'subclass'};
435 # FIXME - This next bit is bogus, because it assumes that the
436 # Dewey code is a floating-point number. It isn't. It's
437 # actually a string that mainly consists of numbers. In
438 # particular, "4" is not a valid Dewey code, although "004"
439 # is ("Data processing; Computer science"). Likewise, zeros
440 # after the decimal are significant ("575" is not the same as
441 # "575.0"; the latter is more specific). And "000" is a
442 # perfectly good Dewey code ("General works; computer
443 # science") and should not be interpreted to mean "this
444 # database entry does not have a Dewey code". That's what
447 ($dewey == 0) && ($dewey='');
448 ($dewey) && ($dewey.=" $subclass") ;
450 my $end=$offset +$num;
455 if ($i4 <=$end && $i4 > $offset){
456 $data2->{'dewey'}=$dewey;
459 # $res2[$i3]="$data2->{'author'}\t$data2->{'title'}\t$data2->{'biblionumber'}\t$data2->{'copyrightdate'}\t$dewey";
462 # print "in here $i3<br>";
473 # $search->{'class'} was not specified
475 # FIXME - This is bogus: it makes a separate query for each
476 # biblioitem, and returns results in apparently random order. It'd
477 # be much better to combine all of the previous queries into one big
478 # one (building it up a little at a time, of course), and have that
479 # big query select all of the desired fields, instead of just
482 while ($i2 < $num && $i2 < $count){
483 my $query="select * from biblio,biblioitems where
484 biblio.biblionumber=? and
485 biblio.biblionumber=biblioitems.biblionumber ";
486 my @bind=($res[$i2+$offset]);
488 if ($search->{'dewey'} ne ''){
489 $query.= "and (dewey like ?)";
490 push(@bind,"$search->{'dewey'}%");
493 my $sth=$dbh->prepare($query);
495 $sth->execute(@bind);
496 if (my $data2=$sth->fetchrow_hashref){
497 my $dewey= $data2->{'dewey'};
498 my $subclass=$data2->{'subclass'};
500 ($dewey == 0) && ($dewey='');
501 ($dewey) && ($dewey.=" $subclass") ;
503 $data2->{'dewey'}=$dewey;
506 # $res2[$i]="$data2->{'author'}\t$data2->{'title'}\t$data2->{'biblionumber'}\t$data2->{'copyrightdate'}\t$dewey";
515 return($count,@res2);
519 my ($env,$type,$search,$num,$offset)=@_;
520 my $dbh = C4::Context->dbh;
521 $search->{'keyword'}=~ s/ +$//;
522 my @key=split(' ',$search->{'keyword'});
526 my $query ="Select * from biblio,bibliosubtitle,biblioitems where
527 biblio.biblionumber=biblioitems.biblionumber and
528 biblio.biblionumber=bibliosubtitle.biblionumber and
529 (((title like ? or title like ?)";
530 my @bind=("$key[0]%","% $key[0]%");
532 $query .= " and (title like ? or title like ?)";
533 push(@bind,"$key[$i]%","% $key[$i]%");
536 $query.= ") or ((subtitle like ? or subtitle like ?)";
537 push(@bind,"$key[0]%","% $key[0]%");
538 for ($i=1;$i<$count;$i++){
539 $query.= " and (subtitle like ? or subtitle like ?)";
540 push(@bind,"$key[$i]%","% $key[$i]%");
542 $query.= ") or ((seriestitle like ? or seriestitle like ?)";
543 push(@bind,"$key[0]%","% $key[0]%");
544 for ($i=1;$i<$count;$i++){
545 $query.=" and (seriestitle like ? or seriestitle like ?)";
546 push(@bind,"$key[$i]%","% $key[$i]%");
548 $query.= ") or ((biblio.notes like ? or biblio.notes like ?)";
549 push(@bind,"$key[0]%","% $key[0]%");
550 for ($i=1;$i<$count;$i++){
551 $query.=" and (biblio.notes like ? or biblio.notes like ?)";
552 push(@bind,"$key[$i]%","% $key[$i]%");
554 $query.= ") or ((biblioitems.notes like ? or biblioitems.notes like ?)";
555 push(@bind,"$key[0]%","% $key[0]%");
556 for ($i=1;$i<$count;$i++){
557 $query.=" and (biblioitems.notes like ? or biblioitems.notes like ?)";
558 push(@bind,"$key[$i]%","% $key[$i]%");
560 if ($search->{'keyword'} =~ /new zealand/i){
561 $query.= "or (title like 'nz%' or title like '% nz %' or title like '% nz' or subtitle like 'nz%'
562 or subtitle like '% nz %' or subtitle like '% nz' or author like 'nz %'
563 or author like '% nz %' or author like '% nz')"
565 if ($search->{'keyword'} eq 'nz' || $search->{'keyword'} eq 'NZ' ||
566 $search->{'keyword'} =~ /nz /i || $search->{'keyword'} =~ / nz /i ||
567 $search->{'keyword'} =~ / nz/i){
568 $query.= "or (title like 'new zealand%' or title like '% new zealand %'
569 or title like '% new zealand' or subtitle like 'new zealand%' or
570 subtitle like '% new zealand %'
571 or subtitle like '% new zealand' or author like 'new zealand%'
572 or author like '% new zealand %' or author like '% new zealand' or
573 seriestitle like 'new zealand%' or seriestitle like '% new zealand %'
574 or seriestitle like '% new zealand')"
577 if ($search->{'class'} ne ''){
578 my @temp=split(/\|/,$search->{'class'});
580 $query.= "and ( itemtype=?";
581 push(@bind,"$temp[0]");
582 for (my $i=1;$i<$count;$i++){
583 $query.=" or itemtype=?";
584 push(@bind,"$temp[$i]");
588 if ($search->{'dewey'} ne ''){
589 $query.= "and (dewey like '$search->{'dewey'}%') ";
591 $query.="group by biblio.biblionumber";
592 #$query.=" order by author,title";
594 my $sth=$dbh->prepare($query);
595 $sth->execute(@bind);
597 while (my $data=$sth->fetchrow_hashref){
598 #FIXME: rewrite to use ? before uncomment
599 # my $sti=$dbh->prepare("select dewey,subclass from biblioitems where biblionumber=$data->{'biblionumber'}
602 # my ($dewey, $subclass) = $sti->fetchrow;
603 my $dewey=$data->{'dewey'};
604 my $subclass=$data->{'subclass'};
606 ($dewey == 0) && ($dewey='');
607 ($dewey) && ($dewey.=" $subclass");
609 $results[$i]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey";
610 # print $results[$i];
614 $sth=$dbh->prepare("Select biblionumber from bibliosubject where subject
615 like ? group by biblionumber");
616 $sth->execute("%".$search->{'keyword'}."%");
617 while (my $data=$sth->fetchrow_hashref){
618 $query="Select * from biblio,biblioitems where
619 biblio.biblionumber=? and
620 biblio.biblionumber=biblioitems.biblionumber ";
621 @bind=($data->{'biblionumber'});
622 if ($search->{'class'} ne ''){
623 my @temp=split(/\|/,$search->{'class'});
625 $query.= " and ( itemtype=?";
626 push(@bind,$temp[0]);
627 for (my $i=1;$i<$count;$i++){
628 $query.=" or itemtype=?";
629 push(@bind,$temp[$i]);
634 if ($search->{'dewey'} ne ''){
635 $query.= "and (dewey like ?)";
636 push(@bind,"$search->{'dewey'}%");
638 my $sth2=$dbh->prepare($query);
639 $sth2->execute(@bind);
641 while (my $data2=$sth2->fetchrow_hashref){
642 my $dewey= $data2->{'dewey'};
643 my $subclass=$data2->{'subclass'};
645 ($dewey == 0) && ($dewey='');
646 ($dewey) && ($dewey.=" $subclass") ;
648 $results[$i]="$data2->{'author'}\t$data2->{'title'}\t$data2->{'biblionumber'}\t$data2->{'copyrightdate'}\t$dewey";
649 # print $results[$i];
655 @results=sort @results;
662 while ($i2 < $count){
663 if ($results[$i2] ne $res[$i-1]){
664 $res[$i]=$results[$i2];
672 while ($i2 < $num && $i2 < $count){
673 $res2[$i2]=$res[$i2+$offset];
685 ($count, @results) = &CatSearch($env, $type, $search, $num, $offset);
687 C<&CatSearch> searches the Koha catalog. It returns a list whose first
688 element is the number of returned results, and whose subsequent
689 elements are the results themselves.
691 Each returned element is a reference-to-hash. Most of the keys are
692 simply the fields from the C<biblio> table in the Koha database, but
693 the following keys may also be present:
699 The book's illustrator.
709 C<$type> may be C<subject>, C<loose>, or C<precise>. This controls the
710 high-level behavior of C<&CatSearch>, as described below.
712 In many cases, the description below says that a certain field in the
713 database must match the search string. In these cases, it means that
714 the beginning of some word in the field must match the search string.
715 Thus, an author search for "sm" will return books whose author is
716 "John Smith" or "Mike Smalls", but not "Paul Grossman", since the "sm"
717 does not occur at the beginning of a word.
719 Note that within each search mode, the criteria are and-ed together.
720 That is, if you perform a loose search on the author "Jerome" and the
721 title "Boat", the search will only return books by Jerome containing
724 It is not possible to cross modes, e.g., set the author to "Asimov"
725 and the subject to "Math" in hopes of finding books on math by Asimov.
729 If C<$type> is set to C<loose>, the following search criteria may be
734 =item C<$search-E<gt>{author}>
736 The search string is a space-separated list of words. Each word must
737 match either the C<author> or C<additionalauthors> field.
739 =item C<$search-E<gt>{title}>
741 Each word in the search string must match the book title. If no author
742 is specified, the book subtitle will also be searched.
744 =item C<$search-E<gt>{abstract}>
746 Searches for the given search string in the book's abstract.
748 =item C<$search-E<gt>{'date-before'}>
750 Searches for books whose copyright date matches the search string.
751 That is, setting C<$search-E<gt>{'date-before'}> to "1985" will find
752 books written in 1985, and setting it to "198" will find books written
753 between 1980 and 1989.
755 =item C<$search-E<gt>{title}>
757 Searches by title are also affected by the value of
758 C<$search-E<gt>{"ttype"}>; if it is set to C<exact>, then the book
759 title, (one of) the series titleZ<>(s), or (one of) the unititleZ<>(s) must
760 match the search string exactly (the subtitle is not searched).
762 If C<$search-E<gt>{"ttype"}> is set to anything other than C<exact>,
763 each word in the search string must match the title, subtitle,
764 unititle, or series title.
766 =item C<$search-E<gt>{class}>
768 Restricts the search to certain item classes. The value of
769 C<$search-E<gt>{"class"}> is a | (pipe)-separated list of item types.
770 Thus, setting it to "F" restricts the search to fiction, and setting
771 it to "CD|CAS" will only look in compact disks and cassettes.
773 =item C<$search-E<gt>{dewey}>
775 Searches for books whose Dewey Decimal Classification code matches the
776 search string. That is, setting C<$search-E<gt>{"dewey"}> to "5" will
777 search for all books in 5I<xx> (Science and mathematics), setting it
778 to "54" will search for all books in 54I<x> (Chemistry), and setting
779 it to "546" will search for books on inorganic chemistry.
781 =item C<$search-E<gt>{publisher}>
783 Searches for books whose publisher contains the search string (unlike
784 other search criteria, C<$search-E<gt>{publisher}> is a string, not a
789 =head2 Subject search
791 If C<$type> is set to C<subject>, the following search criterion may
796 =item C<$search-E<gt>{subject}>
798 The search string is a space-separated list of words, each of which
799 must match the book's subject.
801 Special case: if C<$search-E<gt>{subject}> is set to C<nz>,
802 C<&CatSearch> will search for books whose subject is "New Zealand".
803 However, setting C<$search-E<gt>{subject}> to C<"nz football"> will
804 search for books on "nz" and "football", not books on "New Zealand"
809 =head2 Precise search
811 If C<$type> is set to C<precise>, the following search criteria may be
816 =item C<$search-E<gt>{item}>
818 Searches for books whose barcode exactly matches the search string.
820 =item C<$search-E<gt>{isbn}>
822 Searches for books whose ISBN exactly matches the search string.
826 For a loose search, if an author was specified, the results are
827 ordered by author and title. If no author was specified, the results
828 are ordered by title.
830 For other (non-loose) searches, if a subject was specified, the
831 results are ordered alphabetically by subject.
833 In all other cases (e.g., loose search by keyword), the results are
839 my ($env,$type,$search,$num,$offset)=@_;
840 my $dbh = C4::Context->dbh;
845 my $title = lc($search->{'title'});
847 if ($type eq 'loose') {
848 if ($search->{'author'} ne ''){
849 my @key=split(' ',$search->{'author'});
852 $query="select *,biblio.author,biblio.biblionumber from
854 left join additionalauthors
855 on additionalauthors.biblionumber =biblio.biblionumber
857 ((biblio.author like ? or biblio.author like ? or
858 additionalauthors.author like ? or additionalauthors.author
861 @bind=("$key[0]%","% $key[0]%","$key[0]%","% $key[0]%");
864 biblio.author like ? or biblio.author like ? or
865 additionalauthors.author like ? or additionalauthors.author like ?
867 push(@bind,"$key[$i]%","% $key[$i]%","$key[$i]%","% $key[$i]%");
871 if ($search->{'title'} ne ''){
872 my @key=split(' ',$search->{'title'});
875 $query.= " and (((title like ? or title like ?)";
876 push(@bind,"$key[0]%","% $key[0]%");
878 $query .= " and (title like ? or title like ?)";
879 push(@bind,"$key[$i]%","% $key[$i]%");
882 $query.=") or ((seriestitle like ? or seriestitle like ?)";
883 push(@bind,"$key[0]%","% $key[0]%");
884 for ($i=1;$i<$count;$i++){
885 $query.=" and (seriestitle like ? or seriestitle like ?)";
886 push(@bind,"$key[$i]%","% $key[$i]%");
888 $query.=") or ((unititle like ? or unititle like ?)";
889 push(@bind,"$key[0]%","% $key[0]%");
890 for ($i=1;$i<$count;$i++){
891 $query.=" and (unititle like ? or unititle like ?)";
892 push(@bind,"$key[$i]%","% $key[$i]%");
896 if ($search->{'abstract'} ne ''){
897 $query.= " and (abstract like ?)";
898 push(@bind,"%$search->{'abstract'}%");
900 if ($search->{'date-before'} ne ''){
901 $query.= " and (copyrightdate like ?)";
902 push(@bind,"%$search->{'date-before'}%");
904 $query.=" group by biblio.biblionumber";
906 if ($search->{'title'} ne '') {
907 if ($search->{'ttype'} eq 'exact'){
908 $query="select * from biblio
910 (biblio.title=? or (biblio.unititle = ?
911 or biblio.unititle like ? or
912 biblio.unititle like ? or
913 biblio.unititle like ?) or
914 (biblio.seriestitle = ? or
915 biblio.seriestitle like ? or
916 biblio.seriestitle like ? or
917 biblio.seriestitle like ?)
919 @bind=($search->{'title'},$search->{'title'},"$search->{'title'} |%","%| $search->{'title'} |%","%| $search->{'title'}",$search->{'title'},"$search->{'title'} |%","%| $search->{'title'} |%","%| $search->{'title'}");
921 my @key=split(' ',$search->{'title'});
924 $query="select biblio.biblionumber,author,title,unititle,notes,abstract,serial,seriestitle,copyrightdate,timestamp,subtitle from biblio
925 left join bibliosubtitle on
926 biblio.biblionumber=bibliosubtitle.biblionumber
928 (((title like ? or title like ?)";
929 @bind=("$key[0]%","% $key[0]%");
931 $query .= " and (title like ? or title like ?)";
932 push(@bind,"$key[$i]%","% $key[$i]%");
935 $query.=") or ((subtitle like ? or subtitle like ?)";
936 push(@bind,"$key[0]%","% $key[0]%");
937 for ($i=1;$i<$count;$i++){
938 $query.=" and (subtitle like ? or subtitle like ?)";
939 push(@bind,"$key[$i]%","% $key[$i]%");
941 $query.=") or ((seriestitle like ? or seriestitle like ?)";
942 push(@bind,"$key[0]%","% $key[0]%");
943 for ($i=1;$i<$count;$i++){
944 $query.=" and (seriestitle like ? or seriestitle like ?)";
945 push(@bind,"$key[$i]%","% $key[$i]%");
947 $query.=") or ((unititle like ? or unititle like ?)";
948 push(@bind,"$key[0]%","% $key[0]%");
949 for ($i=1;$i<$count;$i++){
950 $query.=" and (unititle like ? or unititle like ?)";
951 push(@bind,"$key[$i]%","% $key[$i]%");
955 if ($search->{'abstract'} ne ''){
956 $query.= " and (abstract like ?)";
957 push(@bind,"%$search->{'abstract'}%");
959 if ($search->{'date-before'} ne ''){
960 $query.= " and (copyrightdate like ?)";
961 push(@bind,"%$search->{'date-before'}%");
963 } elsif ($search->{'class'} ne ''){
964 $query="select * from biblioitems,biblio where biblio.biblionumber=biblioitems.biblionumber";
965 my @temp=split(/\|/,$search->{'class'});
967 $query.= " and ( itemtype= ?)";
969 for (my $i=1;$i<$count;$i++){
970 $query.=" or itemtype=?";
971 push(@bind,$temp[$i]);
974 if ($search->{'illustrator'} ne ''){
975 $query.=" and illus like ?";
976 push(@bind,"%".$search->{'illustrator'}."%");
978 if ($search->{'dewey'} ne ''){
979 $query.=" and biblioitems.dewey like ?";
980 push(@bind,"$search->{'dewey'}%");
982 } elsif ($search->{'dewey'} ne ''){
983 $query="select * from biblioitems,biblio
984 where biblio.biblionumber=biblioitems.biblionumber
985 and biblioitems.dewey like ?";
986 @bind=("$search->{'dewey'}%");
987 } elsif ($search->{'illustrator'} ne '') {
988 $query="select * from biblioitems,biblio
989 where biblio.biblionumber=biblioitems.biblionumber
990 and biblioitems.illus like ?";
991 @bind=("%".$search->{'illustrator'}."%");
992 } elsif ($search->{'publisher'} ne ''){
993 $query = "Select * from biblio,biblioitems where biblio.biblionumber
994 =biblioitems.biblionumber and (publishercode like ?)";
995 @bind=("%$search->{'publisher'}%");
996 } elsif ($search->{'abstract'} ne ''){
997 $query = "Select * from biblio where abstract like ?";
998 @bind=("%$search->{'abstract'}%");
999 } elsif ($search->{'date-before'} ne ''){
1000 $query = "Select * from biblio where copyrightdate like ?";
1001 @bind=("%$search->{'date-before'}%");
1003 $query .=" group by biblio.biblionumber";
1006 if ($type eq 'subject'){
1007 my @key=split(' ',$search->{'subject'});
1010 $query="select * from bibliosubject, biblioitems where
1011 (bibliosubject.biblionumber = biblioitems.biblionumber) and ( subject like ? or subject like ? or subject like ?)";
1012 @bind=("$key[0]%","% $key[0]%","%($key[0])%");
1014 $query.=" and (subject like ? or subject like ? or subject like ?)";
1015 push(@bind,"$key[$i]%","% $key[$i]%","%($key[$i])%");
1019 # FIXME - Wouldn't it be better to fix the database so that if a
1020 # book has a subject "NZ", then it also gets added the subject
1022 # This can also be generalized by adding a table of subject
1023 # synonyms to the database: just declare "NZ" to be a synonym for
1024 # "New Zealand", "SF" a synonym for both "Science fiction" and
1025 # "Fantastic fiction", etc.
1027 if (lc($search->{'subject'}) eq 'nz'){
1028 $query.= " or (subject like 'NEW ZEALAND %' or subject like '% NEW ZEALAND %'
1029 or subject like '% NEW ZEALAND' or subject like '%(NEW ZEALAND)%' ) ";
1030 } elsif ( $search->{'subject'} =~ /^nz /i || $search->{'subject'} =~ / nz /i || $search->{'subject'} =~ / nz$/i){
1031 $query=~ s/ nz/ NEW ZEALAND/ig;
1032 $query=~ s/nz /NEW ZEALAND /ig;
1033 $query=~ s/\(nz\)/\(NEW ZEALAND\)/gi;
1036 if ($type eq 'precise'){
1037 if ($search->{'itemnumber'} ne ''){
1038 $query="select * from items,biblio ";
1039 my $search2=uc $search->{'itemnumber'};
1040 $query=$query." where
1041 items.biblionumber=biblio.biblionumber
1046 if ($search->{'isbn'} ne ''){
1047 my $search2=uc $search->{'isbn'};
1048 my $sth1=$dbh->prepare("select * from biblioitems where isbn=?");
1049 $sth1->execute($search2);
1051 while (my $data=$sth1->fetchrow_hashref) {
1052 my $sth=$dbh->prepare("select * from biblioitems,biblio where
1053 biblio.biblionumber = ?
1054 and biblioitems.biblionumber = biblio.biblionumber");
1055 $sth->execute($data->{'biblionumber'});
1056 # FIXME - There's already a $data in this scope.
1057 my $data=$sth->fetchrow_hashref;
1058 my ($dewey, $subclass) = ($data->{'dewey'}, $data->{'subclass'});
1059 # FIXME - The following assumes that the Dewey code is a
1060 # floating-point number. It isn't: it's a string.
1062 ($dewey == 0) && ($dewey='');
1063 ($dewey) && ($dewey.=" $subclass");
1064 $data->{'dewey'}=$dewey;
1065 $results[$i2]=$data;
1066 # $results[$i2]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey\t$data->{'isbn'}\t$data->{'itemtype'}";
1073 if ($type ne 'precise' && $type ne 'subject'){
1074 if ($search->{'author'} ne ''){
1075 $query .= " order by biblio.author,title";
1077 $query .= " order by title";
1080 if ($type eq 'subject'){
1081 $query .= " group by subject ";
1084 my $sth=$dbh->prepare($query);
1085 $sth->execute(@bind);
1088 my $limit= $num+$offset;
1089 while (my $data=$sth->fetchrow_hashref){
1090 my $query="select classification,dewey,subclass,publishercode from biblioitems where biblionumber=?";
1091 my @bind=($data->{'biblionumber'});
1092 if ($search->{'class'} ne ''){
1093 my @temp=split(/\|/,$search->{'class'});
1095 $query.= " and ( itemtype= ?";
1096 push(@bind,$temp[0]);
1097 for (my $i=1;$i<$count;$i++){
1098 $query.=" or itemtype=?";
1099 push(@bind,$temp[$i]);
1103 if ($search->{'dewey'} ne ''){
1104 $query.=" and dewey=? ";
1105 push(@bind,$search->{'dewey'});
1107 if ($search->{'illustrator'} ne ''){
1108 $query.=" and illus like ?";
1109 push(@bind,"%$search->{'illustrator'}%");
1111 if ($search->{'publisher'} ne ''){
1112 $query.= " and (publishercode like ?)";
1113 push(@bind,"%$search->{'publisher'}%");
1115 my $sti=$dbh->prepare($query);
1116 $sti->execute(@bind);
1123 if ($bibitemdata = $sti->fetchrow_hashref()){
1125 $classification=$bibitemdata->{'classification'};
1126 $dewey=$bibitemdata->{'dewey'};
1127 $subclass=$bibitemdata->{'subclass'};
1128 $publishercode=$bibitemdata->{'publishercode'};
1130 # print STDERR "$dewey $subclass $publishercode\n";
1131 # FIXME - The Dewey code is a string, not a number.
1133 ($dewey == 0) && ($dewey='');
1134 ($dewey) && ($dewey.=" $subclass");
1135 $data->{'classification'}=$classification;
1136 $data->{'dewey'}=$dewey;
1137 $data->{'publishercode'}=$publishercode;
1140 if ($count > $offset && $count <= $limit){
1149 return($count,@results);
1152 sub updatesearchstats{
1153 my ($dbh,$query)=@_;
1159 @results = &subsearch($env, $subject);
1161 Searches for books that have a subject that exactly matches
1164 C<&subsearch> returns an array of results. Each element of this array
1165 is a string, containing the book's title, author, and biblionumber,
1173 my ($env,$subject)=@_;
1174 my $dbh = C4::Context->dbh;
1175 my $sth=$dbh->prepare("Select * from biblio,bibliosubject where
1176 biblio.biblionumber=bibliosubject.biblionumber and
1177 bibliosubject.subject=? group by biblio.biblionumber
1178 order by biblio.title");
1179 $sth->execute($subject);
1182 while (my $data=$sth->fetchrow_hashref){
1183 push @results, $data;
1192 @results = &ItemInfo($env, $biblionumber, $type);
1194 Returns information about books with the given biblionumber.
1196 C<$type> may be either C<intra> or anything else. If it is not set to
1197 C<intra>, then the search will exclude lost, very overdue, and
1202 C<&ItemInfo> returns a list of references-to-hash. Each element
1203 contains a number of keys. Most of them are table items from the
1204 C<biblio>, C<biblioitems>, C<items>, and C<itemtypes> tables in the
1205 Koha database. Other keys include:
1209 =item C<$data-E<gt>{branchname}>
1211 The name (not the code) of the branch to which the book belongs.
1213 =item C<$data-E<gt>{datelastseen}>
1215 This is simply C<items.datelastseen>, except that while the date is
1216 stored in YYYY-MM-DD format in the database, here it is converted to
1217 DD/MM/YYYY format. A NULL date is returned as C<//>.
1219 =item C<$data-E<gt>{datedue}>
1221 =item C<$data-E<gt>{class}>
1223 This is the concatenation of C<biblioitems.classification>, the book's
1224 Dewey code, and C<biblioitems.subclass>.
1226 =item C<$data-E<gt>{ocount}>
1228 I think this is the number of copies of the book available.
1230 =item C<$data-E<gt>{order}>
1232 If this is set, it is set to C<One Order>.
1239 my ($env,$biblionumber,$type) = @_;
1240 my $dbh = C4::Context->dbh;
1241 my $query = "SELECT *,items.notforloan as itemnotforloan FROM items, biblio, biblioitems
1242 left join itemtypes on biblioitems.itemtype = itemtypes.itemtype
1243 WHERE items.biblionumber = ?
1244 AND biblioitems.biblioitemnumber = items.biblioitemnumber
1245 AND biblio.biblionumber = items.biblionumber";
1246 # buggy : opac & librarian interface can show the same info level & itemstatus should not be hardcoded
1247 # if ($type ne 'intra'){
1248 # $query .= " and ((items.itemlost<>1 and items.itemlost <> 2)
1249 # or items.itemlost is NULL)
1250 # and (wthdrawn <> 1 or wthdrawn is NULL)";
1252 $query .= " order by items.dateaccessioned desc";
1253 my $sth=$dbh->prepare($query);
1254 $sth->execute($biblionumber);
1257 while (my $data=$sth->fetchrow_hashref){
1259 my $isth=$dbh->prepare("Select issues.*,borrowers.cardnumber from issues,borrowers where itemnumber = ? and returndate is null and issues.borrowernumber=borrowers.borrowernumber");
1260 $isth->execute($data->{'itemnumber'});
1261 if (my $idata=$isth->fetchrow_hashref){
1262 $data->{borrowernumber} = $idata->{borrowernumber};
1263 $data->{cardnumber} = $idata->{cardnumber};
1264 $datedue = format_date($idata->{'date_due'});
1266 # buggy : hardcoded & non-translatable
1267 # more : why don't you want to show the datedue if it's very very overdue ?
1268 # if ($data->{'itemlost'} eq '2'){
1269 # $datedue='Very Overdue';
1271 # if ($data->{'itemlost'} eq '1'){
1274 # if ($data->{'wthdrawn'} eq '1'){
1275 # $datedue="Cancelled";
1277 if ($datedue eq ''){
1278 # $datedue="Available";
1279 my ($restype,$reserves)=C4::Reserves2::CheckReserves($data->{'itemnumber'});
1285 #get branch information.....
1286 my $bsth=$dbh->prepare("SELECT * FROM branches WHERE branchcode = ?");
1287 $bsth->execute($data->{'holdingbranch'});
1288 if (my $bdata=$bsth->fetchrow_hashref){
1289 $data->{'branchname'} = $bdata->{'branchname'};
1291 my $date=format_date($data->{'datelastseen'});
1292 $data->{'datelastseen'}=$date;
1293 $data->{'datedue'}=$datedue;
1294 # get notforloan complete status if applicable
1295 my $sthnflstatus = $dbh->prepare('select authorised_value from marc_subfield_structure where kohafield="items.notforloan"');
1296 $sthnflstatus->execute;
1297 my ($authorised_valuecode) = $sthnflstatus->fetchrow;
1298 if ($authorised_valuecode) {
1299 $sthnflstatus = $dbh->prepare("select lib from authorised_values where category=? and authorised_value=?");
1300 $sthnflstatus->execute($authorised_valuecode,$data->{itemnotforloan});
1301 my ($lib) = $sthnflstatus->fetchrow;
1302 $data->{notforloan} = $lib;
1308 #FIXME: ordering/indentation here looks wrong
1309 # buggy : count in $i+1 the info on qty ordered for $i : total shown is real total +1
1310 # useless : Koha 2.2.2 now automatically show the existing number of items
1311 # and if there is no items, and at least one is on order, show "on order".
1312 # my $sth2=$dbh->prepare("Select * from aqorders where biblionumber=?");
1313 # $sth2->execute($biblionumber);
1316 # if ($data=$sth2->fetchrow_hashref){
1317 # $ocount=$data->{'quantity'} - $data->{'quantityreceived'};
1319 # $data->{'ocount'}=$ocount;
1320 # $data->{'order'}="One Order";
1321 # $results[$i]=$data;
1330 @results = &GetItems($env, $biblionumber);
1332 Returns information about books with the given biblionumber.
1336 C<&GetItems> returns an array of strings. Each element is a
1337 tab-separated list of values: biblioitemnumber, itemtype,
1338 classification, Dewey number, subclass, ISBN, volume, number, and
1341 Itemdata, in turn, is a string of the form
1342 "I<barcode>C<[>I<holdingbranch>C<[>I<flags>" where I<flags> contains
1343 the string C<NFL> if the item is not for loan, and C<LOST> if the item
1349 my ($env,$biblionumber)=@_;
1350 #debug_msg($env,"GetItems");
1351 my $dbh = C4::Context->dbh;
1352 my $sth=$dbh->prepare("Select * from biblioitems where (biblionumber = ?)");
1353 $sth->execute($biblionumber);
1354 #debug_msg($env,"executed query");
1357 while (my $data=$sth->fetchrow_hashref) {
1358 #debug_msg($env,$data->{'biblioitemnumber'});
1359 my $dewey = $data->{'dewey'};
1361 my $line = $data->{'biblioitemnumber'}."\t".$data->{'itemtype'};
1362 $line .= "\t$data->{'classification'}\t$dewey";
1363 $line .= "\t$data->{'subclass'}\t$data->{isbn}";
1364 $line .= "\t$data->{'volume'}\t$data->{number}";
1365 my $isth= $dbh->prepare("select * from items where biblioitemnumber = ?");
1366 $isth->execute($data->{'biblioitemnumber'});
1367 while (my $idata = $isth->fetchrow_hashref) {
1368 my $iline = $idata->{'barcode'}."[".$idata->{'holdingbranch'}."[";
1369 if ($idata->{'notforloan'} == 1) {
1372 if ($idata->{'itemlost'} == 1) {
1375 $line .= "\t$iline";
1378 $results[$i] = $line;
1387 $item = &itemdata($barcode);
1389 Looks up the item with the given barcode, and returns a
1390 reference-to-hash containing information about that item. The keys of
1391 the hash are the fields from the C<items> and C<biblioitems> tables in
1398 my $dbh = C4::Context->dbh;
1399 my $sth=$dbh->prepare("Select * from items,biblioitems where barcode=?
1400 and items.biblioitemnumber=biblioitems.biblioitemnumber");
1401 $sth->execute($barcode);
1402 my $data=$sth->fetchrow_hashref;
1409 $data = &bibdata($biblionumber, $type);
1411 Returns information about the book with the given biblionumber.
1413 C<$type> is ignored.
1415 C<&bibdata> returns a reference-to-hash. The keys are the fields in
1416 the C<biblio>, C<biblioitems>, and C<bibliosubtitle> tables in the
1419 In addition, C<$data-E<gt>{subject}> is the list of the book's
1420 subjects, separated by C<" , "> (space, comma, space).
1422 If there are multiple biblioitems with the given biblionumber, only
1423 the first one is considered.
1428 my ($bibnum, $type) = @_;
1429 my $dbh = C4::Context->dbh;
1430 my $sth = $dbh->prepare("Select *, biblioitems.notes AS bnotes, biblio.notes
1431 from biblio, biblioitems
1432 left join bibliosubtitle on
1433 biblio.biblionumber = bibliosubtitle.biblionumber
1434 left join itemtypes on biblioitems.itemtype=itemtypes.itemtype
1435 where biblio.biblionumber = ?
1436 and biblioitems.biblionumber = biblio.biblionumber");
1437 $sth->execute($bibnum);
1439 $data = $sth->fetchrow_hashref;
1441 # handle management of repeated subtitle
1442 $sth = $dbh->prepare("Select * from bibliosubtitle where biblionumber = ?");
1443 $sth->execute($bibnum);
1445 while (my $dat = $sth->fetchrow_hashref){
1447 $line{subtitle} = $dat->{subtitle};
1448 push @subtitles, \%line;
1450 $data->{subtitles} = \@subtitles;
1452 $sth = $dbh->prepare("Select * from bibliosubject where biblionumber = ?");
1453 $sth->execute($bibnum);
1455 while (my $dat = $sth->fetchrow_hashref){
1457 $line{subject} = $dat->{'subject'};
1458 push @subjects, \%line;
1460 $data->{subjects} = \@subjects;
1462 $sth = $dbh->prepare("Select * from additionalauthors where biblionumber = ?");
1463 $sth->execute($bibnum);
1464 while (my $dat = $sth->fetchrow_hashref){
1465 $data->{'additionalauthors'} .= "$dat->{'author'} - ";
1467 chop $data->{'additionalauthors'};
1468 chop $data->{'additionalauthors'};
1469 chop $data->{'additionalauthors'};
1476 $itemdata = &bibitemdata($biblioitemnumber);
1478 Looks up the biblioitem with the given biblioitemnumber. Returns a
1479 reference-to-hash. The keys are the fields from the C<biblio>,
1480 C<biblioitems>, and C<itemtypes> tables in the Koha database, except
1481 that C<biblioitems.notes> is given as C<$itemdata-E<gt>{bnotes}>.
1487 my $dbh = C4::Context->dbh;
1488 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");
1491 $sth->execute($bibitem);
1493 $data = $sth->fetchrow_hashref;
1501 ($count, $subjects) = &subject($biblionumber);
1503 Looks up the subjects of the book with the given biblionumber. Returns
1504 a two-element list. C<$subjects> is a reference-to-array, where each
1505 element is a subject of the book, and C<$count> is the number of
1506 elements in C<$subjects>.
1512 my $dbh = C4::Context->dbh;
1513 my $sth=$dbh->prepare("Select * from bibliosubject where biblionumber=?");
1514 $sth->execute($bibnum);
1517 while (my $data=$sth->fetchrow_hashref){
1522 return($i,\@results);
1527 ($count, $authors) = &addauthors($biblionumber);
1529 Looks up the additional authors for the book with the given
1532 Returns a two-element list. C<$authors> is a reference-to-array, where
1533 each element is an additional author, and C<$count> is the number of
1534 elements in C<$authors>.
1540 my $dbh = C4::Context->dbh;
1541 my $sth=$dbh->prepare("Select * from additionalauthors where biblionumber=?");
1542 $sth->execute($bibnum);
1545 while (my $data=$sth->fetchrow_hashref){
1550 return($i,\@results);
1555 ($count, $subtitles) = &subtitle($biblionumber);
1557 Looks up the subtitles for the book with the given biblionumber.
1559 Returns a two-element list. C<$subtitles> is a reference-to-array,
1560 where each element is a subtitle, and C<$count> is the number of
1561 elements in C<$subtitles>.
1567 my $dbh = C4::Context->dbh;
1568 my $sth=$dbh->prepare("Select * from bibliosubtitle where biblionumber=?");
1569 $sth->execute($bibnum);
1572 while (my $data=$sth->fetchrow_hashref){
1577 return($i,\@results);
1582 @issues = &itemissues($biblioitemnumber, $biblio);
1584 Looks up information about who has borrowed the bookZ<>(s) with the
1585 given biblioitemnumber.
1587 C<$biblio> is ignored.
1589 C<&itemissues> returns an array of references-to-hash. The keys
1590 include the fields from the C<items> table in the Koha database.
1591 Additional keys include:
1597 If the item is currently on loan, this gives the due date.
1599 If the item is not on loan, then this is either "Available" or
1600 "Cancelled", if the item has been withdrawn.
1604 If the item is currently on loan, this gives the card number of the
1605 patron who currently has the item.
1607 =item C<timestamp0>, C<timestamp1>, C<timestamp2>
1609 These give the timestamp for the last three times the item was
1612 =item C<card0>, C<card1>, C<card2>
1614 The card number of the last three patrons who borrowed this item.
1616 =item C<borrower0>, C<borrower1>, C<borrower2>
1618 The borrower number of the last three patrons who borrowed this item.
1625 my ($bibitem, $biblio)=@_;
1626 my $dbh = C4::Context->dbh;
1627 # FIXME - If this function die()s, the script will abort, and the
1628 # user won't get anything; depending on how far the script has
1629 # gotten, the user might get a blank page. It would be much better
1630 # to at least print an error message. The easiest way to do this
1631 # is to set $SIG{__DIE__}.
1632 my $sth = $dbh->prepare("Select * from items where
1633 items.biblioitemnumber = ?")
1634 || die $dbh->errstr;
1638 $sth->execute($bibitem)
1639 || die $sth->errstr;
1641 while (my $data = $sth->fetchrow_hashref) {
1642 # Find out who currently has this item.
1643 # FIXME - Wouldn't it be better to do this as a left join of
1644 # some sort? Currently, this code assumes that if
1645 # fetchrow_hashref() fails, then the book is on the shelf.
1646 # fetchrow_hashref() can fail for any number of reasons (e.g.,
1647 # database server crash), not just because no items match the
1649 my $sth2 = $dbh->prepare("select * from issues,borrowers
1650 where itemnumber = ?
1651 and returndate is NULL
1652 and issues.borrowernumber = borrowers.borrowernumber");
1654 $sth2->execute($data->{'itemnumber'});
1655 if (my $data2 = $sth2->fetchrow_hashref) {
1656 $data->{'date_due'} = $data2->{'date_due'};
1657 $data->{'card'} = $data2->{'cardnumber'};
1658 $data->{'borrower'} = $data2->{'borrowernumber'};
1660 if ($data->{'wthdrawn'} eq '1') {
1661 $data->{'date_due'} = 'Cancelled';
1663 $data->{'date_due'} = 'Available';
1669 # Find the last 3 people who borrowed this item.
1670 $sth2 = $dbh->prepare("select * from issues, borrowers
1671 where itemnumber = ?
1672 and issues.borrowernumber = borrowers.borrowernumber
1673 and returndate is not NULL
1674 order by returndate desc,timestamp desc") || die $dbh->errstr;
1675 $sth2->execute($data->{'itemnumber'}) || die $sth2->errstr;
1676 for (my $i2 = 0; $i2 < 2; $i2++) { # FIXME : error if there is less than 3 pple borrowing this item
1677 if (my $data2 = $sth2->fetchrow_hashref) {
1678 $data->{"timestamp$i2"} = $data2->{'timestamp'};
1679 $data->{"card$i2"} = $data2->{'cardnumber'};
1680 $data->{"borrower$i2"} = $data2->{'borrowernumber'};
1685 $results[$i] = $data;
1695 $item = &itemnodata($env, $dbh, $biblioitemnumber);
1697 Looks up the item with the given biblioitemnumber.
1699 C<$env> and C<$dbh> are ignored.
1701 C<&itemnodata> returns a reference-to-hash whose keys are the fields
1702 from the C<biblio>, C<biblioitems>, and C<items> tables in the Koha
1708 my ($env,$dbh,$itemnumber) = @_;
1709 $dbh = C4::Context->dbh;
1710 my $sth=$dbh->prepare("Select * from biblio,items,biblioitems
1711 where items.itemnumber = ?
1712 and biblio.biblionumber = items.biblionumber
1713 and biblioitems.biblioitemnumber = items.biblioitemnumber");
1715 $sth->execute($itemnumber);
1716 my $data=$sth->fetchrow_hashref;
1723 ($count, $borrowers) = &BornameSearch($env, $searchstring, $type);
1725 Looks up patrons (borrowers) by name.
1729 BUGFIX 499: C<$type> is now used to determine type of search.
1730 if $type is "simple", search is performed on the first letter of the
1733 C<$searchstring> is a space-separated list of search terms. Each term
1734 must match the beginning a borrower's surname, first name, or other
1737 C<&BornameSearch> returns a two-element list. C<$borrowers> is a
1738 reference-to-array; each element is a reference-to-hash, whose keys
1739 are the fields of the C<borrowers> table in the Koha database.
1740 C<$count> is the number of elements in C<$borrowers>.
1744 #used by member enquiries from the intranet
1745 #called by member.pl
1747 my ($env,$searchstring,$orderby,$type)=@_;
1748 my $dbh = C4::Context->dbh;
1749 my $query = ""; my $count; my @data;
1752 if($type eq "simple") # simple search for one letter only
1754 $query="Select * from borrowers where surname like ? order by $orderby";
1755 @bind=("$searchstring%");
1757 else # advanced search looking in surname, firstname and othernames
1759 @data=split(' ',$searchstring);
1761 $query="Select * from borrowers
1762 where ((surname like ? or surname like ?
1763 or firstname like ? or firstname like ?
1764 or othernames like ? or othernames like ?)
1766 @bind=("$data[0]%","% $data[0]%","$data[0]%","% $data[0]%","$data[0]%","% $data[0]%");
1767 for (my $i=1;$i<$count;$i++){
1768 $query=$query." and (".
1769 " surname like ? or surname like ?
1770 or firstname like ? or firstname like ?
1771 or othernames like ? or othernames like ?)";
1772 push(@bind,"$data[$i]%","% $data[$i]%","$data[$i]%","% $data[$i]%","$data[$i]%","% $data[$i]%");
1775 $query=$query.") or cardnumber like ?
1777 push(@bind,$searchstring);
1781 my $sth=$dbh->prepare($query);
1782 # warn "Q $orderby : $query";
1783 $sth->execute(@bind);
1786 while (my $data=$sth->fetchrow_hashref){
1787 push(@results,$data);
1791 return ($cnt,\@results);
1796 $borrower = &borrdata($cardnumber, $borrowernumber);
1798 Looks up information about a patron (borrower) by either card number
1799 or borrower number. If $borrowernumber is specified, C<&borrdata>
1800 searches by borrower number; otherwise, it searches by card number.
1802 C<&borrdata> returns a reference-to-hash whose keys are the fields of
1803 the C<borrowers> table in the Koha database.
1808 my ($cardnumber,$bornum)=@_;
1809 $cardnumber = uc $cardnumber;
1810 my $dbh = C4::Context->dbh;
1813 $sth=$dbh->prepare("Select * from borrowers where cardnumber=?");
1814 $sth->execute($cardnumber);
1816 $sth=$dbh->prepare("Select * from borrowers where borrowernumber=?");
1817 $sth->execute($bornum);
1819 my $data=$sth->fetchrow_hashref;
1823 } else { # try with firstname
1825 my $sth=$dbh->prepare("select * from borrowers where firstname=?");
1826 $sth->execute($cardnumber);
1827 my $data=$sth->fetchrow_hashref;
1837 ($count, $issues) = &borrissues($borrowernumber);
1839 Looks up what the patron with the given borrowernumber has borrowed.
1841 C<&borrissues> returns a two-element array. C<$issues> is a
1842 reference-to-array, where each element is a reference-to-hash; the
1843 keys are the fields from the C<issues>, C<biblio>, and C<items> tables
1844 in the Koha database. C<$count> is the number of elements in
1851 my $dbh = C4::Context->dbh;
1852 my $sth=$dbh->prepare("Select * from issues,biblio,items where borrowernumber=?
1853 and items.itemnumber=issues.itemnumber
1854 and items.biblionumber=biblio.biblionumber
1855 and issues.returndate is NULL order by date_due");
1856 $sth->execute($bornum);
1858 while (my $data = $sth->fetchrow_hashref) {
1859 push @result, $data;
1862 return(scalar(@result), \@result);
1867 ($count, $issues) = &allissues($borrowernumber, $sortkey, $limit);
1869 Looks up what the patron with the given borrowernumber has borrowed,
1870 and sorts the results.
1872 C<$sortkey> is the name of a field on which to sort the results. This
1873 should be the name of a field in the C<issues>, C<biblio>,
1874 C<biblioitems>, or C<items> table in the Koha database.
1876 C<$limit> is the maximum number of results to return.
1878 C<&allissues> returns a two-element array. C<$issues> is a
1879 reference-to-array, where each element is a reference-to-hash; the
1880 keys are the fields from the C<issues>, C<biblio>, C<biblioitems>, and
1881 C<items> tables of the Koha database. C<$count> is the number of
1882 elements in C<$issues>
1887 my ($bornum,$order,$limit)=@_;
1888 #FIXME: sanity-check order and limit
1889 my $dbh = C4::Context->dbh;
1890 my $query="Select * from issues,biblio,items,biblioitems
1891 where borrowernumber=? and
1892 items.biblioitemnumber=biblioitems.biblioitemnumber and
1893 items.itemnumber=issues.itemnumber and
1894 items.biblionumber=biblio.biblionumber order by $order";
1896 $query.=" limit $limit";
1899 my $sth=$dbh->prepare($query);
1900 $sth->execute($bornum);
1903 while (my $data=$sth->fetchrow_hashref){
1908 return($i,\@result);
1913 ($borrowed, $due, $fine) = &borrdata2($env, $borrowernumber);
1915 Returns aggregate data about items borrowed by the patron with the
1916 given borrowernumber.
1920 C<&borrdata2> returns a three-element array. C<$borrowed> is the
1921 number of books the patron currently has borrowed. C<$due> is the
1922 number of overdue items the patron currently has borrowed. C<$fine> is
1923 the total fine currently due by the borrower.
1928 my ($env,$bornum)=@_;
1929 my $dbh = C4::Context->dbh;
1930 my $query="Select count(*) from issues where borrowernumber='$bornum' and
1931 returndate is NULL";
1933 my $sth=$dbh->prepare($query);
1935 my $data=$sth->fetchrow_hashref;
1937 $sth=$dbh->prepare("Select count(*) from issues where
1938 borrowernumber='$bornum' and date_due < now() and returndate is NULL");
1940 my $data2=$sth->fetchrow_hashref;
1942 $sth=$dbh->prepare("Select sum(amountoutstanding) from accountlines where
1943 borrowernumber='$bornum'");
1945 my $data3=$sth->fetchrow_hashref;
1948 return($data2->{'count(*)'},$data->{'count(*)'},$data3->{'sum(amountoutstanding)'});
1951 =item getboracctrecord
1953 ($count, $acctlines, $total) = &getboracctrecord($env, $borrowernumber);
1955 Looks up accounting data for the patron with the given borrowernumber.
1959 (FIXME - I'm not at all sure what this is about.)
1961 C<&getboracctrecord> returns a three-element array. C<$acctlines> is a
1962 reference-to-array, where each element is a reference-to-hash; the
1963 keys are the fields of the C<accountlines> table in the Koha database.
1964 C<$count> is the number of elements in C<$acctlines>. C<$total> is the
1965 total amount outstanding for all of the account lines.
1969 sub getboracctrecord {
1970 my ($env,$params) = @_;
1971 my $dbh = C4::Context->dbh;
1974 my $sth=$dbh->prepare("Select * from accountlines where
1975 borrowernumber=? order by date desc,timestamp desc");
1977 $sth->execute($params->{'borrowernumber'});
1979 while (my $data=$sth->fetchrow_hashref){
1980 #FIXME before reinstating: insecure?
1981 # if ($data->{'itemnumber'} ne ''){
1982 # $query="Select * from items,biblio where items.itemnumber=
1983 # '$data->{'itemnumber'}' and biblio.biblionumber=items.biblionumber";
1984 # my $sth2=$dbh->prepare($query);
1986 # my $data2=$sth2->fetchrow_hashref;
1990 $acctlines[$numlines] = $data;
1992 $total += $data->{'amountoutstanding'};
1995 return ($numlines,\@acctlines,$total);
2000 $counts = &itemcount2($env, $biblionumber, $type);
2002 Counts the number of items with the given biblionumber, broken down by
2007 C<$type> may be either C<intra> or anything else. If it is not set to
2008 C<intra>, then the search will exclude lost, very overdue, and
2011 C<$&itemcount2> returns a reference-to-hash, with the following fields:
2017 The total number of items with this biblionumber.
2021 The number of items on order (aqorders.quantity -
2022 aqorders.quantityreceived).
2026 For each branch that has at least one copy of the book, C<$counts>
2027 will have a key with the branch name, giving the number of copies at
2035 my ($env,$bibnum,$type)=@_;
2036 my $dbh = C4::Context->dbh;
2037 my $query="Select * from items,branches where
2038 biblionumber=? and items.holdingbranch=branches.branchcode";
2039 if ($type ne 'intra'){
2040 $query.=" and ((itemlost <>1 and itemlost <> 2) or itemlost is NULL) and
2041 (wthdrawn <> 1 or wthdrawn is NULL)";
2043 my $sth=$dbh->prepare($query);
2045 $sth->execute($bibnum);
2048 while (my $data=$sth->fetchrow_hashref){
2055 'select * from items
2057 and not ((items.itemlost <>1 and items.itemlost <> 2)
2058 or items.itemlost is NULL)'
2061 'select * from items
2062 where itemnumber=? and not (wthdrawn <> 1 or wthdrawn is NULL)'
2064 'On Loan', "select * from issues,items
2065 where issues.itemnumber=? and returndate is NULL
2066 and items.itemnumber=issues.itemnumber"
2069 my($testlabel, $query2) = @$test;
2071 my $sth2=$dbh->prepare($query2);
2072 $sth2->execute($data->{'itemnumber'});
2074 # FIXME - fetchrow_hashref() can fail for any number of reasons
2075 # (e.g., a database server crash). Perhaps use a left join of some
2077 $status = $testlabel if $sth2->fetchrow_hashref;
2079 last if defined $status;
2081 $status = $data->{'branchname'} unless defined $status;
2084 my $sth2=$dbh->prepare("Select * from aqorders where biblionumber=? and
2085 datecancellationprinted is NULL and quantity > quantityreceived");
2086 $sth2->execute($bibnum);
2087 if (my $data=$sth2->fetchrow_hashref){
2088 $counts{'order'}=$data->{'quantity'} - $data->{'quantityreceived'};
2097 $description = &ItemType($itemtype);
2099 Given an item type code, returns the description for that type.
2104 # FIXME - I'm pretty sure that after the initial setup, the list of
2105 # item types doesn't change very often. Hence, it seems slow and
2106 # inefficient to make yet another database call to look up information
2107 # that'll only change every few months or years.
2109 # Much better, I think, to automatically build a Perl file that can be
2110 # included in those scripts that require it, e.g.:
2111 # @itemtypes = qw( ART BCD CAS CD F ... );
2113 # ART => "Art Prints",
2114 # BCD => "CD-ROM from book",
2115 # CD => "Compact disc (WN)",
2116 # F => "Free Fiction",
2119 # The web server can then run a cron job to rebuild this file from the
2120 # database every hour or so.
2122 # The same thing goes for branches, book funds, book sellers, currency
2123 # rates, printers, stopwords, and perhaps others.
2126 my $dbh = C4::Context->dbh;
2127 my $sth=$dbh->prepare("select description from itemtypes where itemtype=?");
2128 $sth->execute($type);
2129 my $dat=$sth->fetchrow_hashref;
2131 return ($dat->{'description'});
2136 ($count, @results) = &bibitems($biblionumber);
2138 Given the biblionumber for a book, C<&bibitems> looks up that book's
2139 biblioitems (different publications of the same book, the audio book
2140 and film versions, etc.).
2142 C<$count> is the number of elements in C<@results>.
2144 C<@results> is an array of references-to-hash; the keys are the fields
2145 of the C<biblioitems> and C<itemtypes> tables of the Koha database. In
2146 addition, C<itemlost> indicates the availability of the item: if it is
2147 "2", then all copies of the item are long overdue; if it is "1", then
2148 all copies are lost; otherwise, there is at least one copy available.
2154 my $dbh = C4::Context->dbh;
2155 my $sth = $dbh->prepare("SELECT biblioitems.*,
2157 MIN(items.itemlost) as itemlost,
2158 MIN(items.dateaccessioned) as dateaccessioned
2159 FROM biblioitems, itemtypes, items
2160 WHERE biblioitems.biblionumber = ?
2161 AND biblioitems.itemtype = itemtypes.itemtype
2162 AND biblioitems.biblioitemnumber = items.biblioitemnumber
2163 GROUP BY items.biblioitemnumber");
2166 $sth->execute($bibnum);
2167 while (my $data = $sth->fetchrow_hashref) {
2168 $results[$count] = $data;
2172 return($count, @results);
2177 @barcodes = &barcodes($biblioitemnumber);
2179 Given a biblioitemnumber, looks up the corresponding items.
2181 Returns an array of references-to-hash; the keys are C<barcode> and
2184 The returned items include very overdue items, but not lost ones.
2189 #called from request.pl
2190 my ($biblioitemnumber)=@_;
2191 my $dbh = C4::Context->dbh;
2192 my $sth=$dbh->prepare("SELECT barcode, itemlost, holdingbranch FROM items
2193 WHERE biblioitemnumber = ?
2194 AND (wthdrawn <> 1 OR wthdrawn IS NULL)");
2195 $sth->execute($biblioitemnumber);
2198 while (my $data=$sth->fetchrow_hashref){
2199 $barcodes[$i]=$data;
2208 ($count, @websites) = &getwebsites($biblionumber);
2210 Looks up the web sites pertaining to the book with the given
2213 C<$count> is the number of elements in C<@websites>.
2215 C<@websites> is an array of references-to-hash; the keys are the
2216 fields from the C<websites> table in the Koha database.
2221 my ($biblionumber) = @_;
2222 my $dbh = C4::Context->dbh;
2223 my $sth = $dbh->prepare("Select * from websites where biblionumber = ?");
2227 $sth->execute($biblionumber);
2228 while (my $data = $sth->fetchrow_hashref) {
2229 # FIXME - The URL scheme shouldn't be stripped off, at least
2230 # not here, since it's part of the URL, and will be useful in
2231 # constructing a link to the site. If you don't want the user
2232 # to see the "http://" part, strip that off when building the
2234 $data->{'url'} =~ s/^http:\/\///; # FIXME - Leaning toothpick
2236 $results[$count] = $data;
2241 return($count, @results);
2244 =item getwebbiblioitems
2246 ($count, @results) = &getwebbiblioitems($biblionumber);
2248 Given a book's biblionumber, looks up the web versions of the book
2249 (biblioitems with itemtype C<WEB>).
2251 C<$count> is the number of items in C<@results>. C<@results> is an
2252 array of references-to-hash; the keys are the items from the
2253 C<biblioitems> table of the Koha database.
2257 sub getwebbiblioitems {
2258 my ($biblionumber) = @_;
2259 my $dbh = C4::Context->dbh;
2260 my $sth = $dbh->prepare("Select * from biblioitems where biblionumber = ?
2261 and itemtype = 'WEB'");
2265 $sth->execute($biblionumber);
2266 while (my $data = $sth->fetchrow_hashref) {
2267 $data->{'url'} =~ s/^http:\/\///;
2268 $results[$count] = $data;
2273 return($count, @results);
2274 } # sub getwebbiblioitems
2280 ($count, @results) = &isbnsearch($isbn,$title);
2282 Given an isbn and/or a title, returns the biblios having it.
2283 Used in acqui.simple, isbnsearch.pl only
2285 C<$count> is the number of items in C<@results>. C<@results> is an
2286 array of references-to-hash; the keys are the items from the
2287 C<biblioitems> table of the Koha database.
2292 my ($isbn,$title) = @_;
2293 my $dbh = C4::Context->dbh;
2299 $query = "Select distinct biblio.*, biblioitems.classification from biblio, biblioitems where
2300 biblio.biblionumber = biblioitems.biblionumber";
2303 $query .= " and isbn like ?";
2304 @bind=(uc($isbn)."%");
2307 $query .= " and title like ?";
2310 $sth = $dbh->prepare($query);
2312 $sth->execute(@bind);
2313 while (my $data = $sth->fetchrow_hashref) {
2314 $results[$count] = $data;
2319 return($count, @results);
2324 $branchname = &getbranchname($branchcode);
2326 Given the branch code, the function returns the corresponding
2327 branch name for a comprehensive information display
2333 my ($branchcode) = @_;
2334 my $dbh = C4::Context->dbh;
2335 my $sth = $dbh->prepare("SELECT branchname FROM branches WHERE branchcode = ?");
2336 $sth->execute($branchcode);
2337 my $branchname = $sth->fetchrow();
2340 } # sub getbranchname
2342 =item getborrowercategory
2344 $description = &getborrowercategory($categorycode);
2346 Given the borrower's category code, the function returns the corresponding
2347 description for a comprehensive information display.
2351 sub getborrowercategory
2354 my $dbh = C4::Context->dbh;
2355 my $sth = $dbh->prepare("SELECT description FROM categories WHERE categorycode = ?");
2356 $sth->execute($catcode);
2357 my $description = $sth->fetchrow();
2359 return $description;
2360 } # sub getborrowercategory
2363 END { } # module clean-up code here (global destructor)
2372 Koha Developement team <info@koha.org>