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.
34 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
36 # set the version for version checking
37 $VERSION = do { my @v = '$Revision$' =~ /\d+/g;
38 shift(@v) . "." . join("_", map {sprintf "%03d", $_ } @v); };
42 C4::Search - Functions for searching the Koha catalog and other databases
48 my ($count, @results) = catalogsearch($env, $type, $search, $num, $offset);
52 This module provides the searching facilities for the Koha catalog and
55 C<&catalogsearch> is a front end to all the other searches. Depending
56 on what is passed to it, it calls the appropriate search function.
66 &CatSearch &BornameSearch &ItemInfo &KeywordSearch &subsearch
67 &itemdata &bibdata &GetItems &borrdata &itemnodata &itemcount
68 &borrdata2 &NewBorrowerNumber &bibitemdata &borrissues
69 &getboracctrecord &ItemType &itemissues &subject &subtitle
70 &addauthor &bibitems &barcodes &findguarantees &allissues
71 &findguarantor &getwebsites &getwebbiblioitems &catalogsearch &itemcount2
72 &isbnsearch &getbranchname &getborrowercategory
73 search get_record get_xml_record
75 # make all your functions, whether exported or not;
80 my ( $search, $type, $number ) = @_;
81 my $dbh = C4::Context->dbh();
83 my $Zconn = C4::Context->Zconn;
86 if ( $type eq 'CQL' ) {
88 if ( $search->{'cql'} ) {
89 $string = $search->{'cql'};
92 foreach my $var ( keys %$search ) {
93 $string .= "$var=\"$search->{$var}\" ";
96 $q = new ZOOM::Query::CQL2RPN( $string, $Zconn );
101 $rs = $Zconn->search($q);
105 print "Error ", $@->code(), ": ", $@->message(), "\n";
109 while ( $i < $n && $i < $number ) {
110 $raw = $rs->record($i)->raw();
111 my $record = MARC::Record->new_from_xml($raw, 'UTF-8');
112 my $line = MARCmarc2koha( $dbh, $record );
113 push @results, $line;
114 # push @results,$raw;
117 return ( \@results );
122 # pass in an id (biblionumber at this stage) and get back a MARC record
125 my $Zconn = C4::Context->Zconn;
127 my $string = "identifier=$id";
128 # my $string = "title=delete";
131 $q = new ZOOM::Query::CQL2RPN( $string, $Zconn);
133 # my $rs = $Zconn->search_pqf("\@attr 1=12 $id");
134 my $rs = $Zconn->search($q);
137 $raw = $rs->record(0)->raw();
142 warn "Error ", $@->code(), ": ", $@->message(), "\n";
145 my $record = MARC::Record->new_from_xml($raw, 'UTF-8');
151 # pass in an id (biblionumber at this stage) and get back a MARC record
154 my $Zconn = C4::Context->Zconn;
156 my $string = "identifier=$id";
157 # my $string = "title=delete";
160 $q = new ZOOM::Query::CQL2RPN( $string, $Zconn);
162 # my $rs = $Zconn->search_pqf("\@attr 1=12 $id");
163 my $rs = $Zconn->search($q);
166 $raw = $rs->record(0)->raw();
171 warn "Error ", $@->code(), ": ", $@->message(), "\n";
181 ($num_children, $children_arrayref) = &findguarantees($parent_borrno);
182 $child0_cardno = $children_arrayref->[0]{"cardnumber"};
183 $child0_borrno = $children_arrayref->[0]{"borrowernumber"};
185 C<&findguarantees> takes a borrower number (e.g., that of a patron
186 with children) and looks up the borrowers who are guaranteed by that
187 borrower (i.e., the patron's children).
189 C<&findguarantees> returns two values: an integer giving the number of
190 borrowers guaranteed by C<$parent_borrno>, and a reference to an array
191 of references to hash, which gives the actual results.
197 my $dbh = C4::Context->dbh;
198 my $sth=$dbh->prepare("select cardnumber,borrowernumber, firstname, surname from borrowers where guarantor=?");
199 $sth->execute($bornum);
202 while (my $data = $sth->fetchrow_hashref)
207 return (scalar(@dat), \@dat);
212 $guarantor = &findguarantor($borrower_no);
213 $guarantor_cardno = $guarantor->{"cardnumber"};
214 $guarantor_surname = $guarantor->{"surname"};
217 C<&findguarantor> takes a borrower number (presumably that of a child
218 patron), finds the guarantor for C<$borrower_no> (the child's parent),
219 and returns the record for the guarantor.
221 C<&findguarantor> returns a reference-to-hash. Its keys are the fields
222 from the C<borrowers> database table;
228 my $dbh = C4::Context->dbh;
229 my $sth=$dbh->prepare("select guarantor from borrowers where borrowernumber=?");
230 $sth->execute($bornum);
231 my $data=$sth->fetchrow_hashref;
233 $sth=$dbh->prepare("Select * from borrowers where borrowernumber=?");
234 $sth->execute($data->{'guarantor'});
235 $data=$sth->fetchrow_hashref;
240 =item NewBorrowerNumber
242 $num = &NewBorrowerNumber();
244 Allocates a new, unused borrower number, and returns it.
248 # FIXME - This is identical to C4::Circulation::Borrower::NewBorrowerNumber.
249 # Pick one and stick with it. Preferably use the other one. This function
250 # doesn't belong in C4::Search.
251 sub NewBorrowerNumber {
252 my $dbh = C4::Context->dbh;
253 my $sth=$dbh->prepare("Select max(borrowernumber) from borrowers");
255 my $data=$sth->fetchrow_hashref;
257 $data->{'max(borrowernumber)'}++;
258 return($data->{'max(borrowernumber)'});
263 ($count, @results) = &catalogsearch($env, $type, $search, $num, $offset);
265 This is primarily a front-end to other, more specialized catalog
266 search functions: if C<$search-E<gt>{itemnumber}> or
267 C<$search-E<gt>{isbn}> is given, C<&catalogsearch> uses a precise
268 C<&CatSearch>. If $search->{subject} is given, it runs a subject
269 C<&CatSearch>. If C<$search-E<gt>{keyword}> is given, it runs a
270 C<&KeywordSearch>. Otherwise, it runs a loose C<&CatSearch>.
272 If C<$env-E<gt>{itemcount}> is 1, then C<&catalogsearch> also counts
273 the items for each result, and adds several keys:
279 The total number of copies of this book.
281 =item C<locationhash>
283 This is a reference-to-hash; the keys are the names of branches where
284 this book may be found, and the values are the number of copies at
289 A descriptive string saying where the book is located, and how many
290 copies there are, if greater than 1.
294 The book's subject, with spaces replaced with C<%20>, presumably for
302 my ($env,$type,$search,$num,$offset)=@_;
303 my $dbh = C4::Context->dbh;
304 # foreach my $key (%$search){
305 # $search->{$key}=$dbh->quote($search->{$key});
307 my ($count,@results);
308 if ($search->{'itemnumber'} ne '' || $search->{'isbn'} ne ''){
309 print STDERR "Doing a precise search\n";
310 ($count,@results)=CatSearch($env,'precise',$search,$num,$offset);
311 } elsif ($search->{'subject'} ne ''){
312 ($count,@results)=CatSearch($env,'subject',$search,$num,$offset);
313 } elsif ($search->{'keyword'} ne ''){
314 ($count,@results)=&KeywordSearch($env,'keyword',$search,$num,$offset);
316 ($count,@results)=CatSearch($env,'loose',$search,$num,$offset);
319 if ($env->{itemcount} eq '1') {
320 foreach my $data (@results){
321 my ($counts) = itemcount2($env, $data->{'biblionumber'}, 'intra');
322 my $subject2=$data->{'subject'};
323 $subject2=~ s/ /%20/g;
324 $data->{'itemcount'}=$counts->{'total'};
325 my $totalitemcounts=0;
326 foreach my $key (keys %$counts){
327 if ($key ne 'total'){ # FIXME - Should ignore 'order', too.
328 #$data->{'location'}.="$key $counts->{$key} ";
329 $totalitemcounts+=$counts->{$key};
330 $data->{'locationhash'}->{$key}=$counts->{$key};
334 my $locationtextonly='';
335 my $notavailabletext='';
336 foreach (sort keys %{$data->{'locationhash'}}) {
337 if ($_ eq 'notavailable') {
338 $notavailabletext="Not available";
339 my $c=$data->{'locationhash'}->{$_};
340 $data->{'not-available-p'}=$totalitemcounts;
341 if ($totalitemcounts>1) {
342 $notavailabletext.=" ($c)";
343 $data->{'not-available-plural-p'}=1;
347 my $c=$data->{'locationhash'}->{$_};
348 if ($_ eq 'Item Lost') {
349 $data->{'lost-p'}=$totalitemcounts;
350 $data->{'lost-plural-p'}=1
351 if $totalitemcounts > 1;
352 } elsif ($_ eq 'Withdrawn') {
353 $data->{'withdrawn-p'}=$totalitemcounts;
354 $data->{'withdrawn-plural-p'}=1
355 if $totalitemcounts > 1;
356 } elsif ($_ eq 'On Loan') {
357 $data->{'on-loan-p'}=$totalitemcounts;
358 $data->{'on-loan-plural-p'}=1
359 if $totalitemcounts > 1;
361 $locationtextonly.=$_;
362 $locationtextonly.=" ($c), "
363 if $totalitemcounts>1;
365 if ($totalitemcounts>1) {
366 $locationtext.=" ($c), ";
370 if ($notavailabletext) {
371 $locationtext.=$notavailabletext;
373 $locationtext=~s/, $//;
375 $data->{'location'}=$locationtext;
376 $data->{'location-only'}=$locationtextonly;
377 $data->{'subject2'}=$subject2;
378 $data->{'use-location-flags-p'}=1; # XXX
381 return ($count,@results);
386 $search = { "keyword" => "One or more keywords",
387 "class" => "VID|CD", # Limit search to fiction and CDs
390 ($count, @results) = &KeywordSearch($env, $type, $search, $num, $offset);
392 C<&KeywordSearch> searches the catalog by keyword: given a string
393 (C<$search-E<gt>{"keyword"}> consisting of a space-separated list of
394 keywords, it looks for books that contain any of those keywords in any
395 of a number of places.
397 C<&KeywordSearch> looks for keywords in the book title (and subtitle),
398 series name, notes (both C<biblio.notes> and C<biblioitems.notes>),
401 C<$search-E<gt>{"class"}> can be set to a C<|> (pipe)-separated list of
402 item class codes (e.g., "F" for fiction, "JNF" for junior nonfiction,
403 etc.). In this case, the search will be restricted to just those
406 If C<$search-E<gt>{"class"}> is not specified, you may specify
407 C<$search-E<gt>{"dewey"}>. This will restrict the search to that
408 particular Dewey Decimal Classification category. Setting
409 C<$search-E<gt>{"dewey"}> to "513" will return books about arithmetic,
410 whereas setting it to "5" will return all books with Dewey code 5I<xx>
411 (Science and Mathematics).
413 C<$env> and C<$type> are ignored.
415 C<$offset> and C<$num> specify the subset of results to return.
416 C<$num> specifies the number of results to return, and C<$offset> is
417 the number of the first result. Thus, setting C<$offset> to 100 and
418 C<$num> to 5 will return results 100 through 104 inclusive.
423 my ($env,$type,$search,$num,$offset)=@_;
424 my $dbh = C4::Context->dbh;
425 $search->{'keyword'}=~ s/ +$//;
426 my @key=split(' ',$search->{'keyword'});
427 # FIXME - Naive users might enter comma-separated
428 # words, e.g., "training, animal". Ought to cope with
432 my %biblionumbers; # Set of biblionumbers returned by the
435 # FIXME - Ought to filter the stopwords out of the list of keywords.
436 # @key = map { !defined($stopwords{$_}) } @key;
438 # FIXME - The way this code is currently set up, it looks for all of
439 # the keywords first in (title, notes, seriestitle), then in the
440 # subtitle, then in the subject. Thus, if you look for keywords
441 # "science fiction", this search won't find a book with
442 # title = "How to write fiction"
443 # subtitle = "A science-based approach"
444 # Is this the desired effect? If not, then the first SQL query
445 # should look in the biblio, subtitle, and subject tables all at
446 # once. The way the first query is built can accomodate this easily.
448 # Look for keywords in table 'biblio'.
450 # Build an SQL query that finds each of the keywords in any of the
451 # title, biblio.notes, or seriestitle. To do this, we'll build up an
452 # array of clauses, one for each keyword.
453 my $query; # The SQL query
454 my @clauses = (); # The search clauses
455 my @bind = (); # The term bindings
457 $query = <<EOT; # Beginning of the query
462 foreach my $keyword (@key)
464 my @subclauses = (); # Subclauses, one for each field we're
467 # For each field we're searching on, create a subclause that'll
468 # match the current keyword in the current field.
469 foreach my $field (qw(title notes seriestitle author))
472 "$field LIKE ? OR $field LIKE ?";
473 push(@bind,"\Q$keyword\E%","% \Q$keyword\E%");
475 # (Yes, this could have been done as
476 # @subclauses = map {...} qw(field1 field2 ...)
477 # )but I think this way is more readable.
479 # Construct the current clause by joining the subclauses.
480 push @clauses, "(" . join(")\n\tOR (", @subclauses) . ")";
482 # Now join all of the clauses together and append to the query.
483 $query .= "(" . join(")\nAND (", @clauses) . ")";
485 # FIXME - Perhaps use $sth->bind_columns() ? Documented as the most
486 # efficient way to fetch data.
487 my $sth=$dbh->prepare($query);
488 $sth->execute(@bind);
489 while (my @res = $sth->fetchrow_array) {
492 $biblionumbers{$_} = 1; # Add these results to the set
497 # Now look for keywords in the 'bibliosubtitle' table.
499 # Again, we build a list of clauses from the keywords.
502 $query = "SELECT biblionumber FROM bibliosubtitle WHERE ";
503 foreach my $keyword (@key)
506 "subtitle LIKE ? OR subtitle like ?";
507 push(@bind,"\Q$keyword\E%","% \Q$keyword\E%");
509 $query .= "(" . join(") AND (", @clauses) . ")";
511 $sth=$dbh->prepare($query);
512 $sth->execute(@bind);
513 while (my @res = $sth->fetchrow_array) {
516 $biblionumbers{$_} = 1; # Add these results to the set
521 # Look for the keywords in the notes for individual items
522 # ('biblioitems.notes')
524 # Again, we build a list of clauses from the keywords.
527 $query = "SELECT biblionumber FROM biblioitems WHERE ";
528 foreach my $keyword (@key)
531 "notes LIKE ? OR notes like ?";
532 push(@bind,"\Q$keyword\E%","% \Q$keyword\E%");
534 $query .= "(" . join(") AND (", @clauses) . ")";
536 $sth=$dbh->prepare($query);
537 $sth->execute(@bind);
538 while (my @res = $sth->fetchrow_array) {
541 $biblionumbers{$_} = 1; # Add these results to the set
546 # Look for keywords in the 'bibliosubject' table.
548 # FIXME - The other queries look for words in the desired field that
549 # begin with the individual keywords the user entered. This one
550 # searches for the literal string the user entered. Is this the
552 # Note in particular that spaces are retained: if the user typed
554 # (with two spaces), this won't find the subject "science fiction"
555 # (one space). Likewise, a search for "%" will return absolutely
557 # If this isn't the desired effect, see the previous searches for
560 $sth=$dbh->prepare("Select biblionumber from bibliosubject where subject
561 like ? group by biblionumber");
562 $sth->execute("%$search->{'keyword'}%");
564 while (my @res = $sth->fetchrow_array) {
567 $biblionumbers{$_} = 1; # Add these results to the set
577 my @res = keys %biblionumbers;
581 # print "count $count";
582 if ($search->{'class'} ne ''){
584 my $query="select * from biblio,biblioitems where
585 biblio.biblionumber=? and
586 biblio.biblionumber=biblioitems.biblionumber ";
587 my @bind = ($res[$i2]);
588 if ($search->{'class'} ne ''){ # FIXME - Redundant
589 my @temp=split(/\|/,$search->{'class'});
591 $query.= "and ( itemtype=?";
592 push(@bind,$temp[0]);
593 for (my $i=1;$i<$count;$i++){
594 $query.=" or itemtype=?";
595 push(@bind,$temp[$i]);
599 my $sth=$dbh->prepare($query);
601 $sth->execute(@bind);
602 if (my $data2=$sth->fetchrow_hashref){
603 my $dewey= $data2->{'dewey'};
604 my $subclass=$data2->{'subclass'};
605 # FIXME - This next bit is bogus, because it assumes that the
606 # Dewey code is a floating-point number. It isn't. It's
607 # actually a string that mainly consists of numbers. In
608 # particular, "4" is not a valid Dewey code, although "004"
609 # is ("Data processing; Computer science"). Likewise, zeros
610 # after the decimal are significant ("575" is not the same as
611 # "575.0"; the latter is more specific). And "000" is a
612 # perfectly good Dewey code ("General works; computer
613 # science") and should not be interpreted to mean "this
614 # database entry does not have a Dewey code". That's what
617 ($dewey == 0) && ($dewey='');
618 ($dewey) && ($dewey.=" $subclass") ;
620 my $end=$offset +$num;
625 if ($i4 <=$end && $i4 > $offset){
626 $data2->{'dewey'}=$dewey;
629 # $res2[$i3]="$data2->{'author'}\t$data2->{'title'}\t$data2->{'biblionumber'}\t$data2->{'copyrightdate'}\t$dewey";
632 # print "in here $i3<br>";
643 # $search->{'class'} was not specified
645 # FIXME - This is bogus: it makes a separate query for each
646 # biblioitem, and returns results in apparently random order. It'd
647 # be much better to combine all of the previous queries into one big
648 # one (building it up a little at a time, of course), and have that
649 # big query select all of the desired fields, instead of just
652 while ($i2 < $num && $i2 < $count){
653 my $query="select * from biblio,biblioitems where
654 biblio.biblionumber=? and
655 biblio.biblionumber=biblioitems.biblionumber ";
656 my @bind=($res[$i2+$offset]);
658 if ($search->{'dewey'} ne ''){
659 $query.= "and (dewey like ?)";
660 push(@bind,"$search->{'dewey'}%");
663 my $sth=$dbh->prepare($query);
665 $sth->execute(@bind);
666 if (my $data2=$sth->fetchrow_hashref){
667 my $dewey= $data2->{'dewey'};
668 my $subclass=$data2->{'subclass'};
670 ($dewey == 0) && ($dewey='');
671 ($dewey) && ($dewey.=" $subclass") ;
673 $data2->{'dewey'}=$dewey;
676 # $res2[$i]="$data2->{'author'}\t$data2->{'title'}\t$data2->{'biblionumber'}\t$data2->{'copyrightdate'}\t$dewey";
685 return($count,@res2);
689 my ($env,$type,$search,$num,$offset)=@_;
690 my $dbh = C4::Context->dbh;
691 $search->{'keyword'}=~ s/ +$//;
692 my @key=split(' ',$search->{'keyword'});
696 my $query ="Select * from biblio,bibliosubtitle,biblioitems where
697 biblio.biblionumber=biblioitems.biblionumber and
698 biblio.biblionumber=bibliosubtitle.biblionumber and
699 (((title like ? or title like ?)";
700 my @bind=("$key[0]%","% $key[0]%");
702 $query .= " and (title like ? or title like ?)";
703 push(@bind,"$key[$i]%","% $key[$i]%");
706 $query.= ") or ((subtitle like ? or subtitle like ?)";
707 push(@bind,"$key[0]%","% $key[0]%");
708 for ($i=1;$i<$count;$i++){
709 $query.= " and (subtitle like ? or subtitle like ?)";
710 push(@bind,"$key[$i]%","% $key[$i]%");
712 $query.= ") or ((seriestitle like ? or seriestitle like ?)";
713 push(@bind,"$key[0]%","% $key[0]%");
714 for ($i=1;$i<$count;$i++){
715 $query.=" and (seriestitle like ? or seriestitle like ?)";
716 push(@bind,"$key[$i]%","% $key[$i]%");
718 $query.= ") or ((biblio.notes like ? or biblio.notes like ?)";
719 push(@bind,"$key[0]%","% $key[0]%");
720 for ($i=1;$i<$count;$i++){
721 $query.=" and (biblio.notes like ? or biblio.notes like ?)";
722 push(@bind,"$key[$i]%","% $key[$i]%");
724 $query.= ") or ((biblioitems.notes like ? or biblioitems.notes like ?)";
725 push(@bind,"$key[0]%","% $key[0]%");
726 for ($i=1;$i<$count;$i++){
727 $query.=" and (biblioitems.notes like ? or biblioitems.notes like ?)";
728 push(@bind,"$key[$i]%","% $key[$i]%");
730 if ($search->{'keyword'} =~ /new zealand/i){
731 $query.= "or (title like 'nz%' or title like '% nz %' or title like '% nz' or subtitle like 'nz%'
732 or subtitle like '% nz %' or subtitle like '% nz' or author like 'nz %'
733 or author like '% nz %' or author like '% nz')"
735 if ($search->{'keyword'} eq 'nz' || $search->{'keyword'} eq 'NZ' ||
736 $search->{'keyword'} =~ /nz /i || $search->{'keyword'} =~ / nz /i ||
737 $search->{'keyword'} =~ / nz/i){
738 $query.= "or (title like 'new zealand%' or title like '% new zealand %'
739 or title like '% new zealand' or subtitle like 'new zealand%' or
740 subtitle like '% new zealand %'
741 or subtitle like '% new zealand' or author like 'new zealand%'
742 or author like '% new zealand %' or author like '% new zealand' or
743 seriestitle like 'new zealand%' or seriestitle like '% new zealand %'
744 or seriestitle like '% new zealand')"
747 if ($search->{'class'} ne ''){
748 my @temp=split(/\|/,$search->{'class'});
750 $query.= "and ( itemtype=?";
751 push(@bind,"$temp[0]");
752 for (my $i=1;$i<$count;$i++){
753 $query.=" or itemtype=?";
754 push(@bind,"$temp[$i]");
758 if ($search->{'dewey'} ne ''){
759 $query.= "and (dewey like '$search->{'dewey'}%') ";
761 $query.="group by biblio.biblionumber";
762 #$query.=" order by author,title";
764 my $sth=$dbh->prepare($query);
765 $sth->execute(@bind);
767 while (my $data=$sth->fetchrow_hashref){
768 #FIXME: rewrite to use ? before uncomment
769 # my $sti=$dbh->prepare("select dewey,subclass from biblioitems where biblionumber=$data->{'biblionumber'}
772 # my ($dewey, $subclass) = $sti->fetchrow;
773 my $dewey=$data->{'dewey'};
774 my $subclass=$data->{'subclass'};
776 ($dewey == 0) && ($dewey='');
777 ($dewey) && ($dewey.=" $subclass");
779 $results[$i]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey";
780 # print $results[$i];
784 $sth=$dbh->prepare("Select biblionumber from bibliosubject where subject
785 like ? group by biblionumber");
786 $sth->execute("%".$search->{'keyword'}."%");
787 while (my $data=$sth->fetchrow_hashref){
788 $query="Select * from biblio,biblioitems where
789 biblio.biblionumber=? and
790 biblio.biblionumber=biblioitems.biblionumber ";
791 @bind=($data->{'biblionumber'});
792 if ($search->{'class'} ne ''){
793 my @temp=split(/\|/,$search->{'class'});
795 $query.= " and ( itemtype=?";
796 push(@bind,$temp[0]);
797 for (my $i=1;$i<$count;$i++){
798 $query.=" or itemtype=?";
799 push(@bind,$temp[$i]);
804 if ($search->{'dewey'} ne ''){
805 $query.= "and (dewey like ?)";
806 push(@bind,"$search->{'dewey'}%");
808 my $sth2=$dbh->prepare($query);
809 $sth2->execute(@bind);
811 while (my $data2=$sth2->fetchrow_hashref){
812 my $dewey= $data2->{'dewey'};
813 my $subclass=$data2->{'subclass'};
815 ($dewey == 0) && ($dewey='');
816 ($dewey) && ($dewey.=" $subclass") ;
818 $results[$i]="$data2->{'author'}\t$data2->{'title'}\t$data2->{'biblionumber'}\t$data2->{'copyrightdate'}\t$dewey";
819 # print $results[$i];
825 @results=sort @results;
832 while ($i2 < $count){
833 if ($results[$i2] ne $res[$i-1]){
834 $res[$i]=$results[$i2];
842 while ($i2 < $num && $i2 < $count){
843 $res2[$i2]=$res[$i2+$offset];
855 ($count, @results) = &CatSearch($env, $type, $search, $num, $offset);
857 C<&CatSearch> searches the Koha catalog. It returns a list whose first
858 element is the number of returned results, and whose subsequent
859 elements are the results themselves.
861 Each returned element is a reference-to-hash. Most of the keys are
862 simply the fields from the C<biblio> table in the Koha database, but
863 the following keys may also be present:
869 The book's illustrator.
879 C<$type> may be C<subject>, C<loose>, or C<precise>. This controls the
880 high-level behavior of C<&CatSearch>, as described below.
882 In many cases, the description below says that a certain field in the
883 database must match the search string. In these cases, it means that
884 the beginning of some word in the field must match the search string.
885 Thus, an author search for "sm" will return books whose author is
886 "John Smith" or "Mike Smalls", but not "Paul Grossman", since the "sm"
887 does not occur at the beginning of a word.
889 Note that within each search mode, the criteria are and-ed together.
890 That is, if you perform a loose search on the author "Jerome" and the
891 title "Boat", the search will only return books by Jerome containing
894 It is not possible to cross modes, e.g., set the author to "Asimov"
895 and the subject to "Math" in hopes of finding books on math by Asimov.
899 If C<$type> is set to C<loose>, the following search criteria may be
904 =item C<$search-E<gt>{author}>
906 The search string is a space-separated list of words. Each word must
907 match either the C<author> or C<additionalauthors> field.
909 =item C<$search-E<gt>{title}>
911 Each word in the search string must match the book title. If no author
912 is specified, the book subtitle will also be searched.
914 =item C<$search-E<gt>{abstract}>
916 Searches for the given search string in the book's abstract.
918 =item C<$search-E<gt>{'date-before'}>
920 Searches for books whose copyright date matches the search string.
921 That is, setting C<$search-E<gt>{'date-before'}> to "1985" will find
922 books written in 1985, and setting it to "198" will find books written
923 between 1980 and 1989.
925 =item C<$search-E<gt>{title}>
927 Searches by title are also affected by the value of
928 C<$search-E<gt>{"ttype"}>; if it is set to C<exact>, then the book
929 title, (one of) the series titleZ<>(s), or (one of) the unititleZ<>(s) must
930 match the search string exactly (the subtitle is not searched).
932 If C<$search-E<gt>{"ttype"}> is set to anything other than C<exact>,
933 each word in the search string must match the title, subtitle,
934 unititle, or series title.
936 =item C<$search-E<gt>{class}>
938 Restricts the search to certain item classes. The value of
939 C<$search-E<gt>{"class"}> is a | (pipe)-separated list of item types.
940 Thus, setting it to "F" restricts the search to fiction, and setting
941 it to "CD|CAS" will only look in compact disks and cassettes.
943 =item C<$search-E<gt>{dewey}>
945 Searches for books whose Dewey Decimal Classification code matches the
946 search string. That is, setting C<$search-E<gt>{"dewey"}> to "5" will
947 search for all books in 5I<xx> (Science and mathematics), setting it
948 to "54" will search for all books in 54I<x> (Chemistry), and setting
949 it to "546" will search for books on inorganic chemistry.
951 =item C<$search-E<gt>{publisher}>
953 Searches for books whose publisher contains the search string (unlike
954 other search criteria, C<$search-E<gt>{publisher}> is a string, not a
959 =head2 Subject search
961 If C<$type> is set to C<subject>, the following search criterion may
966 =item C<$search-E<gt>{subject}>
968 The search string is a space-separated list of words, each of which
969 must match the book's subject.
971 Special case: if C<$search-E<gt>{subject}> is set to C<nz>,
972 C<&CatSearch> will search for books whose subject is "New Zealand".
973 However, setting C<$search-E<gt>{subject}> to C<"nz football"> will
974 search for books on "nz" and "football", not books on "New Zealand"
979 =head2 Precise search
981 If C<$type> is set to C<precise>, the following search criteria may be
986 =item C<$search-E<gt>{item}>
988 Searches for books whose barcode exactly matches the search string.
990 =item C<$search-E<gt>{isbn}>
992 Searches for books whose ISBN exactly matches the search string.
996 For a loose search, if an author was specified, the results are
997 ordered by author and title. If no author was specified, the results
998 are ordered by title.
1000 For other (non-loose) searches, if a subject was specified, the
1001 results are ordered alphabetically by subject.
1003 In all other cases (e.g., loose search by keyword), the results are
1009 my ($env,$type,$search,$num,$offset)=@_;
1010 my $dbh = C4::Context->dbh;
1015 my $title = lc($search->{'title'});
1017 if ($type eq 'loose' || $type eq 'loose_acq') {
1018 if ($search->{'author'} ne ''){
1019 my @key=split(' ',$search->{'author'});
1022 $query="select *,biblio.author,biblio.biblionumber from
1024 left join additionalauthors
1025 on additionalauthors.biblionumber =biblio.biblionumber
1027 ((biblio.author like ? or biblio.author like ? or
1028 additionalauthors.author like ? or additionalauthors.author
1031 @bind=("$key[0]%","% $key[0]%","$key[0]%","% $key[0]%");
1032 while ($i < $count){
1034 biblio.author like ? or biblio.author like ? or
1035 additionalauthors.author like ? or additionalauthors.author like ?
1037 push(@bind,"$key[$i]%","% $key[$i]%","$key[$i]%","% $key[$i]%");
1041 if ($search->{'title'} ne ''){
1042 my @key=split(' ',$search->{'title'});
1045 $query.= " and (((title like ? or title like ?)";
1046 push(@bind,"$key[0]%","% $key[0]%");
1048 $query .= " and (title like ? or title like ?)";
1049 push(@bind,"$key[$i]%","% $key[$i]%");
1052 $query.=") or ((seriestitle like ? or seriestitle like ?)";
1053 push(@bind,"$key[0]%","% $key[0]%");
1054 for ($i=1;$i<$count;$i++){
1055 $query.=" and (seriestitle like ? or seriestitle like ?)";
1056 push(@bind,"$key[$i]%","% $key[$i]%");
1058 $query.=") or ((unititle like ? or unititle like ?)";
1059 push(@bind,"$key[0]%","% $key[0]%");
1060 for ($i=1;$i<$count;$i++){
1061 $query.=" and (unititle like ? or unititle like ?)";
1062 push(@bind,"$key[$i]%","% $key[$i]%");
1066 if ($search->{'abstract'} ne ''){
1067 $query.= " and (abstract like ?)";
1068 push(@bind,"%$search->{'abstract'}%");
1070 if ($search->{'date-before'} ne ''){
1071 $query.= " and (copyrightdate like ?)";
1072 push(@bind,"%$search->{'date-before'}%");
1074 $query.=" group by biblio.biblionumber";
1076 if ($search->{'title'} ne '') {
1077 if ($search->{'ttype'} eq 'exact'){
1078 $query="select * from biblio
1080 (biblio.title=? or (biblio.unititle = ?
1081 or biblio.unititle like ? or
1082 biblio.unititle like ? or
1083 biblio.unititle like ?) or
1084 (biblio.seriestitle = ? or
1085 biblio.seriestitle like ? or
1086 biblio.seriestitle like ? or
1087 biblio.seriestitle like ?)
1089 @bind=($search->{'title'},$search->{'title'},"$search->{'title'} |%","%| $search->{'title'} |%","%| $search->{'title'}",$search->{'title'},"$search->{'title'} |%","%| $search->{'title'} |%","%| $search->{'title'}");
1091 my @key=split(' ',$search->{'title'});
1094 $query="select biblio.biblionumber,author,title,unititle,notes,abstract,serial,seriestitle,copyrightdate,timestamp,subtitle from biblio
1095 left join bibliosubtitle on
1096 biblio.biblionumber=bibliosubtitle.biblionumber
1098 (((title like ? or title like ?)";
1099 @bind=("$key[0]%","% $key[0]%");
1101 $query .= " and (title like ? or title like ?)";
1102 push(@bind,"$key[$i]%","% $key[$i]%");
1105 $query.=") or ((subtitle like ? or subtitle like ?)";
1106 push(@bind,"$key[0]%","% $key[0]%");
1107 for ($i=1;$i<$count;$i++){
1108 $query.=" and (subtitle like ? or subtitle like ?)";
1109 push(@bind,"$key[$i]%","% $key[$i]%");
1111 $query.=") or ((seriestitle like ? or seriestitle like ?)";
1112 push(@bind,"$key[0]%","% $key[0]%");
1113 for ($i=1;$i<$count;$i++){
1114 $query.=" and (seriestitle like ? or seriestitle like ?)";
1115 push(@bind,"$key[$i]%","% $key[$i]%");
1117 $query.=") or ((unititle like ? or unititle like ?)";
1118 push(@bind,"$key[0]%","% $key[0]%");
1119 for ($i=1;$i<$count;$i++){
1120 $query.=" and (unititle like ? or unititle like ?)";
1121 push(@bind,"$key[$i]%","% $key[$i]%");
1125 if ($search->{'abstract'} ne ''){
1126 $query.= " and (abstract like ?)";
1127 push(@bind,"%$search->{'abstract'}%");
1129 if ($search->{'date-before'} ne ''){
1130 $query.= " and (copyrightdate like ?)";
1131 push(@bind,"%$search->{'date-before'}%");
1133 } elsif ($search->{'class'} ne ''){
1134 $query="select * from biblioitems,biblio where biblio.biblionumber=biblioitems.biblionumber";
1135 my @temp=split(/\|/,$search->{'class'});
1137 $query.= " and ( itemtype= ?)";
1139 for (my $i=1;$i<$count;$i++){
1140 $query.=" or itemtype=?";
1141 push(@bind,$temp[$i]);
1144 if ($search->{'illustrator'} ne ''){
1145 $query.=" and illus like ?";
1146 push(@bind,"%".$search->{'illustrator'}."%");
1148 if ($search->{'dewey'} ne ''){
1149 $query.=" and biblioitems.dewey like ?";
1150 push(@bind,"$search->{'dewey'}%");
1152 } elsif ($search->{'dewey'} ne ''){
1153 $query="select * from biblioitems,biblio
1154 where biblio.biblionumber=biblioitems.biblionumber
1155 and biblioitems.dewey like ?";
1156 @bind=("$search->{'dewey'}%");
1157 } elsif ($search->{'illustrator'} ne '') {
1158 $query="select * from biblioitems,biblio
1159 where biblio.biblionumber=biblioitems.biblionumber
1160 and biblioitems.illus like ?";
1161 @bind=("%".$search->{'illustrator'}."%");
1162 } elsif ($search->{'publisher'} ne ''){
1163 $query = "Select * from biblio,biblioitems where biblio.biblionumber
1164 =biblioitems.biblionumber and (publishercode like ?)";
1165 @bind=("%$search->{'publisher'}%");
1166 } elsif ($search->{'abstract'} ne ''){
1167 $query = "Select * from biblio where abstract like ?";
1168 @bind=("%$search->{'abstract'}%");
1169 } elsif ($search->{'date-before'} ne ''){
1170 $query = "Select * from biblio where copyrightdate like ?";
1171 @bind=("%$search->{'date-before'}%");
1173 $query .=" group by biblio.biblionumber";
1176 if ($type eq 'subject'){
1177 my @key=split(' ',$search->{'subject'});
1180 $query="select * from bibliosubject, biblioitems where
1181 (bibliosubject.biblionumber = biblioitems.biblionumber) and ( subject like ? or subject like ? or subject like ?)";
1182 @bind=("$key[0]%","% $key[0]%","%($key[0])%");
1184 $query.=" and (subject like ? or subject like ? or subject like ?)";
1185 push(@bind,"$key[$i]%","% $key[$i]%","%($key[$i])%");
1189 # FIXME - Wouldn't it be better to fix the database so that if a
1190 # book has a subject "NZ", then it also gets added the subject
1192 # This can also be generalized by adding a table of subject
1193 # synonyms to the database: just declare "NZ" to be a synonym for
1194 # "New Zealand", "SF" a synonym for both "Science fiction" and
1195 # "Fantastic fiction", etc.
1197 if (lc($search->{'subject'}) eq 'nz'){
1198 $query.= " or (subject like 'NEW ZEALAND %' or subject like '% NEW ZEALAND %'
1199 or subject like '% NEW ZEALAND' or subject like '%(NEW ZEALAND)%' ) ";
1200 } elsif ( $search->{'subject'} =~ /^nz /i || $search->{'subject'} =~ / nz /i || $search->{'subject'} =~ / nz$/i){
1201 $query=~ s/ nz/ NEW ZEALAND/ig;
1202 $query=~ s/nz /NEW ZEALAND /ig;
1203 $query=~ s/\(nz\)/\(NEW ZEALAND\)/gi;
1206 if ($type eq 'precise'){
1207 if ($search->{'itemnumber'} ne ''){
1208 $query="select * from items,biblio ";
1209 my $search2=uc $search->{'itemnumber'};
1210 $query=$query." where
1211 items.biblionumber=biblio.biblionumber
1216 if ($search->{'isbn'} ne ''){
1217 my $search2=uc $search->{'isbn'};
1218 my $sth1=$dbh->prepare("select * from biblioitems where isbn=?");
1219 $sth1->execute($search2);
1221 while (my $data=$sth1->fetchrow_hashref) {
1222 my $sth=$dbh->prepare("select * from biblioitems,biblio where
1223 biblio.biblionumber = ?
1224 and biblioitems.biblionumber = biblio.biblionumber");
1225 $sth->execute($data->{'biblionumber'});
1226 # FIXME - There's already a $data in this scope.
1227 my $data=$sth->fetchrow_hashref;
1228 my ($dewey, $subclass) = ($data->{'dewey'}, $data->{'subclass'});
1229 # FIXME - The following assumes that the Dewey code is a
1230 # floating-point number. It isn't: it's a string.
1232 ($dewey == 0) && ($dewey='');
1233 ($dewey) && ($dewey.=" $subclass");
1234 $data->{'dewey'}=$dewey;
1235 $results[$i2]=$data;
1236 # $results[$i2]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey\t$data->{'isbn'}\t$data->{'itemtype'}";
1243 if ($type ne 'precise' && $type ne 'subject'){
1244 if ($search->{'author'} ne ''){
1245 $query .= " order by biblio.author,title";
1247 $query .= " order by title";
1250 if ($type eq 'subject'){
1251 $query .= " group by subject ";
1254 my $sth=$dbh->prepare($query);
1255 $sth->execute(@bind);
1258 my $limit= $num+$offset;
1259 while (my $data=$sth->fetchrow_hashref){
1260 my $query="select classification,dewey,subclass,publishercode from biblioitems where biblionumber=?";
1261 my @bind=($data->{'biblionumber'});
1262 if ($search->{'class'} ne ''){
1263 my @temp=split(/\|/,$search->{'class'});
1265 $query.= " and ( itemtype= ?";
1266 push(@bind,$temp[0]);
1267 for (my $i=1;$i<$count;$i++){
1268 $query.=" or itemtype=?";
1269 push(@bind,$temp[$i]);
1273 if ($search->{'dewey'} ne ''){
1274 $query.=" and dewey=? ";
1275 push(@bind,$search->{'dewey'});
1277 if ($search->{'illustrator'} ne ''){
1278 $query.=" and illus like ?";
1279 push(@bind,"%$search->{'illustrator'}%");
1281 if ($search->{'publisher'} ne ''){
1282 $query.= " and (publishercode like ?)";
1283 push(@bind,"%$search->{'publisher'}%");
1285 my $sti=$dbh->prepare($query);
1286 $sti->execute(@bind);
1293 if ($bibitemdata = $sti->fetchrow_hashref()){
1295 $classification=$bibitemdata->{'classification'};
1296 $dewey=$bibitemdata->{'dewey'};
1297 $subclass=$bibitemdata->{'subclass'};
1298 $publishercode=$bibitemdata->{'publishercode'};
1300 if($type eq 'loose_acq'){ # want to return biblio info for biblios that do not have attached biblioitems
1303 # print STDERR "$dewey $subclass $publishercode\n";
1304 # FIXME - The Dewey code is a string, not a number.
1306 ($dewey == 0) && ($dewey='');
1307 ($dewey) && ($dewey.=" $subclass");
1308 $data->{'classification'}=$classification;
1309 $data->{'dewey'}=$dewey;
1310 $data->{'publishercode'}=$publishercode;
1313 if ($count > $offset && $count <= $limit){
1322 return($count,@results);
1325 sub updatesearchstats{
1326 my ($dbh,$query)=@_;
1332 @results = &subsearch($env, $subject);
1334 Searches for books that have a subject that exactly matches
1337 C<&subsearch> returns an array of results. Each element of this array
1338 is a string, containing the book's title, author, and biblionumber,
1346 my ($env,$subject)=@_;
1347 my $dbh = C4::Context->dbh;
1348 my $sth=$dbh->prepare("Select * from biblio,bibliosubject where
1349 biblio.biblionumber=bibliosubject.biblionumber and
1350 bibliosubject.subject=? group by biblio.biblionumber
1351 order by biblio.title");
1352 $sth->execute($subject);
1355 while (my $data=$sth->fetchrow_hashref){
1356 push @results, $data;
1365 @results = &ItemInfo($env, $biblionumber, $type);
1367 Returns information about books with the given biblionumber.
1369 C<$type> may be either C<intra> or anything else. If it is not set to
1370 C<intra>, then the search will exclude lost, very overdue, and
1375 C<&ItemInfo> returns a list of references-to-hash. Each element
1376 contains a number of keys. Most of them are table items from the
1377 C<biblio>, C<biblioitems>, C<items>, and C<itemtypes> tables in the
1378 Koha database. Other keys include:
1382 =item C<$data-E<gt>{branchname}>
1384 The name (not the code) of the branch to which the book belongs.
1386 =item C<$data-E<gt>{datelastseen}>
1388 This is simply C<items.datelastseen>, except that while the date is
1389 stored in YYYY-MM-DD format in the database, here it is converted to
1390 DD/MM/YYYY format. A NULL date is returned as C<//>.
1392 =item C<$data-E<gt>{datedue}>
1394 =item C<$data-E<gt>{class}>
1396 This is the concatenation of C<biblioitems.classification>, the book's
1397 Dewey code, and C<biblioitems.subclass>.
1399 =item C<$data-E<gt>{ocount}>
1401 I think this is the number of copies of the book available.
1403 =item C<$data-E<gt>{order}>
1405 If this is set, it is set to C<One Order>.
1412 my ($env,$biblionumber,$type) = @_;
1413 my $dbh = C4::Context->dbh;
1414 my $query = "SELECT *,items.notforloan as itemnotforloan FROM items, biblio, biblioitems
1415 left join itemtypes on biblioitems.itemtype = itemtypes.itemtype
1416 WHERE items.biblionumber = ?
1417 AND biblioitems.biblioitemnumber = items.biblioitemnumber
1418 AND biblio.biblionumber = items.biblionumber";
1419 # buggy : opac & librarian interface can show the same info level & itemstatus should not be hardcoded
1420 # if ($type ne 'intra'){
1421 # $query .= " and ((items.itemlost<>1 and items.itemlost <> 2)
1422 # or items.itemlost is NULL)
1423 # and (wthdrawn <> 1 or wthdrawn is NULL)";
1425 $query .= " order by items.homebranch, items.dateaccessioned desc";
1426 my $sth=$dbh->prepare($query);
1427 $sth->execute($biblionumber);
1430 while (my $data=$sth->fetchrow_hashref){
1432 my $isth=$dbh->prepare("Select issues.*,borrowers.cardnumber from issues,borrowers where itemnumber = ? and returndate is null and issues.borrowernumber=borrowers.borrowernumber");
1433 $isth->execute($data->{'itemnumber'});
1434 if (my $idata=$isth->fetchrow_hashref){
1435 $data->{borrowernumber} = $idata->{borrowernumber};
1436 $data->{cardnumber} = $idata->{cardnumber};
1437 $datedue = format_date($idata->{'date_due'});
1439 # buggy : hardcoded & non-translatable
1440 # more : why don't you want to show the datedue if it's very very overdue ?
1441 # if ($data->{'itemlost'} eq '2'){
1442 # $datedue='Very Overdue';
1444 # if ($data->{'itemlost'} eq '1'){
1447 # if ($data->{'wthdrawn'} eq '1'){
1448 # $datedue="Cancelled";
1450 if ($datedue eq ''){
1451 # $datedue="Available";
1452 my ($restype,$reserves)=C4::Reserves2::CheckReserves($data->{'itemnumber'});
1458 #get branch information.....
1459 my $bsth=$dbh->prepare("SELECT * FROM branches WHERE branchcode = ?");
1460 $bsth->execute($data->{'holdingbranch'});
1461 if (my $bdata=$bsth->fetchrow_hashref){
1462 $data->{'branchname'} = $bdata->{'branchname'};
1464 my $date=format_date($data->{'datelastseen'});
1465 $data->{'datelastseen'}=$date;
1466 $data->{'datedue'}=$datedue;
1467 # get notforloan complete status if applicable
1468 my $sthnflstatus = $dbh->prepare('select authorised_value from marc_subfield_structure where kohafield="items.notforloan"');
1469 $sthnflstatus->execute;
1470 my ($authorised_valuecode) = $sthnflstatus->fetchrow;
1471 if ($authorised_valuecode) {
1472 $sthnflstatus = $dbh->prepare("select lib from authorised_values where category=? and authorised_value=?");
1473 $sthnflstatus->execute($authorised_valuecode,$data->{itemnotforloan});
1474 my ($lib) = $sthnflstatus->fetchrow;
1475 $data->{notforloantext} = $lib;
1481 #FIXME: ordering/indentation here looks wrong
1482 # buggy : count in $i+1 the info on qty ordered for $i : total shown is real total +1
1483 # useless : Koha 2.2.2 now automatically show the existing number of items
1484 # and if there is no items, and at least one is on order, show "on order".
1485 # my $sth2=$dbh->prepare("Select * from aqorders where biblionumber=?");
1486 # $sth2->execute($biblionumber);
1489 # if ($data=$sth2->fetchrow_hashref){
1490 # $ocount=$data->{'quantity'} - $data->{'quantityreceived'};
1492 # $data->{'ocount'}=$ocount;
1493 # $data->{'order'}="One Order";
1494 # $results[$i]=$data;
1503 @results = &GetItems($env, $biblionumber);
1505 Returns information about books with the given biblionumber.
1509 C<&GetItems> returns an array of strings. Each element is a
1510 tab-separated list of values: biblioitemnumber, itemtype,
1511 classification, Dewey number, subclass, ISBN, volume, number, and
1514 Itemdata, in turn, is a string of the form
1515 "I<barcode>C<[>I<holdingbranch>C<[>I<flags>" where I<flags> contains
1516 the string C<NFL> if the item is not for loan, and C<LOST> if the item
1522 my ($env,$biblionumber)=@_;
1523 #debug_msg($env,"GetItems");
1524 my $dbh = C4::Context->dbh;
1525 my $sth=$dbh->prepare("Select * from biblioitems where (biblionumber = ?)");
1526 $sth->execute($biblionumber);
1527 #debug_msg($env,"executed query");
1530 while (my $data=$sth->fetchrow_hashref) {
1531 #debug_msg($env,$data->{'biblioitemnumber'});
1532 my $dewey = $data->{'dewey'};
1534 my $line = $data->{'biblioitemnumber'}."\t".$data->{'itemtype'};
1535 $line .= "\t$data->{'classification'}\t$dewey";
1536 $line .= "\t$data->{'subclass'}\t$data->{isbn}";
1537 $line .= "\t$data->{'volume'}\t$data->{number}";
1538 my $isth= $dbh->prepare("select * from items where biblioitemnumber = ?");
1539 $isth->execute($data->{'biblioitemnumber'});
1540 while (my $idata = $isth->fetchrow_hashref) {
1541 my $iline = $idata->{'barcode'}."[".$idata->{'holdingbranch'}."[";
1542 if ($idata->{'notforloan'} == 1) {
1545 if ($idata->{'itemlost'} == 1) {
1548 $line .= "\t$iline";
1551 $results[$i] = $line;
1560 $item = &itemdata($barcode);
1562 Looks up the item with the given barcode, and returns a
1563 reference-to-hash containing information about that item. The keys of
1564 the hash are the fields from the C<items> and C<biblioitems> tables in
1571 my $dbh = C4::Context->dbh;
1572 my $sth=$dbh->prepare("Select * from items,biblioitems where barcode=?
1573 and items.biblioitemnumber=biblioitems.biblioitemnumber");
1574 $sth->execute($barcode);
1575 my $data=$sth->fetchrow_hashref;
1582 $data = &bibdata($biblionumber, $type);
1584 Returns information about the book with the given biblionumber.
1586 C<$type> is ignored.
1588 C<&bibdata> returns a reference-to-hash. The keys are the fields in
1589 the C<biblio>, C<biblioitems>, and C<bibliosubtitle> tables in the
1592 In addition, C<$data-E<gt>{subject}> is the list of the book's
1593 subjects, separated by C<" , "> (space, comma, space).
1595 If there are multiple biblioitems with the given biblionumber, only
1596 the first one is considered.
1601 my ($bibnum, $type) = @_;
1602 my $dbh = C4::Context->dbh;
1603 my $sth = $dbh->prepare("Select *, biblioitems.notes AS bnotes, biblio.notes
1604 from biblio, biblioitems
1605 left join bibliosubtitle on
1606 biblio.biblionumber = bibliosubtitle.biblionumber
1607 left join itemtypes on biblioitems.itemtype=itemtypes.itemtype
1608 where biblio.biblionumber = ?
1609 and biblioitems.biblionumber = biblio.biblionumber");
1610 $sth->execute($bibnum);
1612 $data = $sth->fetchrow_hashref;
1614 # move url to an array, splitting it on every |
1616 foreach (split /\|/,$data->{url}) {
1621 $data->{URLS} = \@URLS;
1622 # handle management of repeated subtitle
1623 $sth = $dbh->prepare("Select * from bibliosubtitle where biblionumber = ?");
1624 $sth->execute($bibnum);
1626 while (my $dat = $sth->fetchrow_hashref){
1628 $line{subtitle} = $dat->{subtitle};
1629 push @subtitles, \%line;
1631 $data->{subtitles} = \@subtitles;
1633 $sth = $dbh->prepare("Select * from bibliosubject where biblionumber = ?");
1634 $sth->execute($bibnum);
1635 # handle subjects : DEPRECATED ?
1637 while (my $dat = $sth->fetchrow_hashref){
1639 $line{subject} = $dat->{'subject'};
1640 push @subjects, \%line;
1642 $data->{subjects} = \@subjects;
1644 # handle additional authors
1645 $sth = $dbh->prepare("Select * from additionalauthors where biblionumber = ?");
1646 $sth->execute($bibnum);
1647 while (my $dat = $sth->fetchrow_hashref){
1648 $data->{'additionalauthors'} .= "$dat->{'author'} - ";
1650 chop $data->{'additionalauthors'};
1651 chop $data->{'additionalauthors'};
1652 chop $data->{'additionalauthors'};
1653 # handle ISBN : reintroduce - if there are none
1654 $data->{'isbn'} = DisplayISBN($data->{'isbn'});
1661 $itemdata = &bibitemdata($biblioitemnumber);
1663 Looks up the biblioitem with the given biblioitemnumber. Returns a
1664 reference-to-hash. The keys are the fields from the C<biblio>,
1665 C<biblioitems>, and C<itemtypes> tables in the Koha database, except
1666 that C<biblioitems.notes> is given as C<$itemdata-E<gt>{bnotes}>.
1672 my $dbh = C4::Context->dbh;
1673 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");
1676 $sth->execute($bibitem);
1678 $data = $sth->fetchrow_hashref;
1686 ($count, $subjects) = &subject($biblionumber);
1688 Looks up the subjects of the book with the given biblionumber. Returns
1689 a two-element list. C<$subjects> is a reference-to-array, where each
1690 element is a subject of the book, and C<$count> is the number of
1691 elements in C<$subjects>.
1697 my $dbh = C4::Context->dbh;
1698 my $sth=$dbh->prepare("Select * from bibliosubject where biblionumber=?");
1699 $sth->execute($bibnum);
1702 while (my $data=$sth->fetchrow_hashref){
1707 return($i,\@results);
1712 ($count, $authors) = &addauthors($biblionumber);
1714 Looks up the additional authors for the book with the given
1717 Returns a two-element list. C<$authors> is a reference-to-array, where
1718 each element is an additional author, and C<$count> is the number of
1719 elements in C<$authors>.
1725 my $dbh = C4::Context->dbh;
1726 my $sth=$dbh->prepare("Select * from additionalauthors where biblionumber=?");
1727 $sth->execute($bibnum);
1730 while (my $data=$sth->fetchrow_hashref){
1735 return($i,\@results);
1740 ($count, $subtitles) = &subtitle($biblionumber);
1742 Looks up the subtitles for the book with the given biblionumber.
1744 Returns a two-element list. C<$subtitles> is a reference-to-array,
1745 where each element is a subtitle, and C<$count> is the number of
1746 elements in C<$subtitles>.
1752 my $dbh = C4::Context->dbh;
1753 my $sth=$dbh->prepare("Select * from bibliosubtitle where biblionumber=?");
1754 $sth->execute($bibnum);
1757 while (my $data=$sth->fetchrow_hashref){
1762 return($i,\@results);
1767 @issues = &itemissues($biblioitemnumber, $biblio);
1769 Looks up information about who has borrowed the bookZ<>(s) with the
1770 given biblioitemnumber.
1772 C<$biblio> is ignored.
1774 C<&itemissues> returns an array of references-to-hash. The keys
1775 include the fields from the C<items> table in the Koha database.
1776 Additional keys include:
1782 If the item is currently on loan, this gives the due date.
1784 If the item is not on loan, then this is either "Available" or
1785 "Cancelled", if the item has been withdrawn.
1789 If the item is currently on loan, this gives the card number of the
1790 patron who currently has the item.
1792 =item C<timestamp0>, C<timestamp1>, C<timestamp2>
1794 These give the timestamp for the last three times the item was
1797 =item C<card0>, C<card1>, C<card2>
1799 The card number of the last three patrons who borrowed this item.
1801 =item C<borrower0>, C<borrower1>, C<borrower2>
1803 The borrower number of the last three patrons who borrowed this item.
1810 my ($bibitem, $biblio)=@_;
1811 my $dbh = C4::Context->dbh;
1812 # FIXME - If this function die()s, the script will abort, and the
1813 # user won't get anything; depending on how far the script has
1814 # gotten, the user might get a blank page. It would be much better
1815 # to at least print an error message. The easiest way to do this
1816 # is to set $SIG{__DIE__}.
1817 my $sth = $dbh->prepare("Select * from items where
1818 items.biblioitemnumber = ?")
1819 || die $dbh->errstr;
1823 $sth->execute($bibitem)
1824 || die $sth->errstr;
1826 while (my $data = $sth->fetchrow_hashref) {
1827 # Find out who currently has this item.
1828 # FIXME - Wouldn't it be better to do this as a left join of
1829 # some sort? Currently, this code assumes that if
1830 # fetchrow_hashref() fails, then the book is on the shelf.
1831 # fetchrow_hashref() can fail for any number of reasons (e.g.,
1832 # database server crash), not just because no items match the
1834 my $sth2 = $dbh->prepare("select * from issues,borrowers
1835 where itemnumber = ?
1836 and returndate is NULL
1837 and issues.borrowernumber = borrowers.borrowernumber");
1839 $sth2->execute($data->{'itemnumber'});
1840 if (my $data2 = $sth2->fetchrow_hashref) {
1841 $data->{'date_due'} = $data2->{'date_due'};
1842 $data->{'card'} = $data2->{'cardnumber'};
1843 $data->{'borrower'} = $data2->{'borrowernumber'};
1845 if ($data->{'wthdrawn'} eq '1') {
1846 $data->{'date_due'} = 'Cancelled';
1848 $data->{'date_due'} = 'Available';
1854 # Find the last 3 people who borrowed this item.
1855 $sth2 = $dbh->prepare("select * from issues, borrowers
1856 where itemnumber = ?
1857 and issues.borrowernumber = borrowers.borrowernumber
1858 and returndate is not NULL
1859 order by returndate desc,timestamp desc") || die $dbh->errstr;
1860 $sth2->execute($data->{'itemnumber'}) || die $sth2->errstr;
1861 for (my $i2 = 0; $i2 < 2; $i2++) { # FIXME : error if there is less than 3 pple borrowing this item
1862 if (my $data2 = $sth2->fetchrow_hashref) {
1863 $data->{"timestamp$i2"} = $data2->{'timestamp'};
1864 $data->{"card$i2"} = $data2->{'cardnumber'};
1865 $data->{"borrower$i2"} = $data2->{'borrowernumber'};
1870 $results[$i] = $data;
1880 $item = &itemnodata($env, $dbh, $biblioitemnumber);
1882 Looks up the item with the given biblioitemnumber.
1884 C<$env> and C<$dbh> are ignored.
1886 C<&itemnodata> returns a reference-to-hash whose keys are the fields
1887 from the C<biblio>, C<biblioitems>, and C<items> tables in the Koha
1893 my ($env,$dbh,$itemnumber) = @_;
1894 $dbh = C4::Context->dbh;
1895 my $sth=$dbh->prepare("Select * from biblio,items,biblioitems
1896 where items.itemnumber = ?
1897 and biblio.biblionumber = items.biblionumber
1898 and biblioitems.biblioitemnumber = items.biblioitemnumber");
1900 $sth->execute($itemnumber);
1901 my $data=$sth->fetchrow_hashref;
1908 ($count, $borrowers) = &BornameSearch($env, $searchstring, $type);
1910 Looks up patrons (borrowers) by name.
1914 BUGFIX 499: C<$type> is now used to determine type of search.
1915 if $type is "simple", search is performed on the first letter of the
1918 C<$searchstring> is a space-separated list of search terms. Each term
1919 must match the beginning a borrower's surname, first name, or other
1922 C<&BornameSearch> returns a two-element list. C<$borrowers> is a
1923 reference-to-array; each element is a reference-to-hash, whose keys
1924 are the fields of the C<borrowers> table in the Koha database.
1925 C<$count> is the number of elements in C<$borrowers>.
1929 #used by member enquiries from the intranet
1930 #called by member.pl
1932 my ($env,$searchstring,$orderby,$type)=@_;
1933 my $dbh = C4::Context->dbh;
1934 my $query = ""; my $count; my @data;
1937 if($type eq "simple") # simple search for one letter only
1939 $query="Select * from borrowers where surname like ? order by $orderby";
1940 @bind=("$searchstring%");
1942 else # advanced search looking in surname, firstname and othernames
1944 @data=split(' ',$searchstring);
1946 $query="Select * from borrowers
1947 where ((surname like ? or surname like ?
1948 or firstname like ? or firstname like ?
1949 or othernames like ? or othernames like ?)
1951 @bind=("$data[0]%","% $data[0]%","$data[0]%","% $data[0]%","$data[0]%","% $data[0]%");
1952 for (my $i=1;$i<$count;$i++){
1953 $query=$query." and (".
1954 " surname like ? or surname like ?
1955 or firstname like ? or firstname like ?
1956 or othernames like ? or othernames like ?)";
1957 push(@bind,"$data[$i]%","% $data[$i]%","$data[$i]%","% $data[$i]%","$data[$i]%","% $data[$i]%");
1960 $query=$query.") or cardnumber like ?
1962 push(@bind,$searchstring);
1966 my $sth=$dbh->prepare($query);
1967 $sth->execute(@bind);
1970 while (my $data=$sth->fetchrow_hashref){
1971 push(@results,$data);
1975 return ($cnt,\@results);
1980 $borrower = &borrdata($cardnumber, $borrowernumber);
1982 Looks up information about a patron (borrower) by either card number
1983 or borrower number. If $borrowernumber is specified, C<&borrdata>
1984 searches by borrower number; otherwise, it searches by card number.
1986 C<&borrdata> returns a reference-to-hash whose keys are the fields of
1987 the C<borrowers> table in the Koha database.
1992 my ($cardnumber,$bornum)=@_;
1993 $cardnumber = uc $cardnumber;
1994 my $dbh = C4::Context->dbh;
1997 $sth=$dbh->prepare("Select * from borrowers where cardnumber=?");
1998 $sth->execute($cardnumber);
2000 $sth=$dbh->prepare("Select * from borrowers where borrowernumber=?");
2001 $sth->execute($bornum);
2003 my $data=$sth->fetchrow_hashref;
2007 } else { # try with firstname
2009 my $sth=$dbh->prepare("select * from borrowers where firstname=?");
2010 $sth->execute($cardnumber);
2011 my $data=$sth->fetchrow_hashref;
2021 ($count, $issues) = &borrissues($borrowernumber);
2023 Looks up what the patron with the given borrowernumber has borrowed.
2025 C<&borrissues> returns a two-element array. C<$issues> is a
2026 reference-to-array, where each element is a reference-to-hash; the
2027 keys are the fields from the C<issues>, C<biblio>, and C<items> tables
2028 in the Koha database. C<$count> is the number of elements in
2035 my $dbh = C4::Context->dbh;
2036 my $sth=$dbh->prepare("Select * from issues,biblio,items where borrowernumber=?
2037 and items.itemnumber=issues.itemnumber
2038 and items.biblionumber=biblio.biblionumber
2039 and issues.returndate is NULL order by date_due");
2040 $sth->execute($bornum);
2042 while (my $data = $sth->fetchrow_hashref) {
2043 push @result, $data;
2046 return(scalar(@result), \@result);
2051 ($count, $issues) = &allissues($borrowernumber, $sortkey, $limit);
2053 Looks up what the patron with the given borrowernumber has borrowed,
2054 and sorts the results.
2056 C<$sortkey> is the name of a field on which to sort the results. This
2057 should be the name of a field in the C<issues>, C<biblio>,
2058 C<biblioitems>, or C<items> table in the Koha database.
2060 C<$limit> is the maximum number of results to return.
2062 C<&allissues> returns a two-element array. C<$issues> is a
2063 reference-to-array, where each element is a reference-to-hash; the
2064 keys are the fields from the C<issues>, C<biblio>, C<biblioitems>, and
2065 C<items> tables of the Koha database. C<$count> is the number of
2066 elements in C<$issues>
2071 my ($bornum,$order,$limit)=@_;
2072 #FIXME: sanity-check order and limit
2073 my $dbh = C4::Context->dbh;
2074 my $query="Select * from issues,biblio,items,biblioitems
2075 where borrowernumber=? and
2076 items.biblioitemnumber=biblioitems.biblioitemnumber and
2077 items.itemnumber=issues.itemnumber and
2078 items.biblionumber=biblio.biblionumber order by $order";
2080 $query.=" limit $limit";
2083 my $sth=$dbh->prepare($query);
2084 $sth->execute($bornum);
2087 while (my $data=$sth->fetchrow_hashref){
2092 return($i,\@result);
2097 ($borrowed, $due, $fine) = &borrdata2($env, $borrowernumber);
2099 Returns aggregate data about items borrowed by the patron with the
2100 given borrowernumber.
2104 C<&borrdata2> returns a three-element array. C<$borrowed> is the
2105 number of books the patron currently has borrowed. C<$due> is the
2106 number of overdue items the patron currently has borrowed. C<$fine> is
2107 the total fine currently due by the borrower.
2112 my ($env,$bornum)=@_;
2113 my $dbh = C4::Context->dbh;
2114 my $query="Select count(*) from issues where borrowernumber='$bornum' and
2115 returndate is NULL";
2117 my $sth=$dbh->prepare($query);
2119 my $data=$sth->fetchrow_hashref;
2121 $sth=$dbh->prepare("Select count(*) from issues where
2122 borrowernumber='$bornum' and date_due < now() and returndate is NULL");
2124 my $data2=$sth->fetchrow_hashref;
2126 $sth=$dbh->prepare("Select sum(amountoutstanding) from accountlines where
2127 borrowernumber='$bornum'");
2129 my $data3=$sth->fetchrow_hashref;
2132 return($data2->{'count(*)'},$data->{'count(*)'},$data3->{'sum(amountoutstanding)'});
2135 =item getboracctrecord
2137 ($count, $acctlines, $total) = &getboracctrecord($env, $borrowernumber);
2139 Looks up accounting data for the patron with the given borrowernumber.
2143 (FIXME - I'm not at all sure what this is about.)
2145 C<&getboracctrecord> returns a three-element array. C<$acctlines> is a
2146 reference-to-array, where each element is a reference-to-hash; the
2147 keys are the fields of the C<accountlines> table in the Koha database.
2148 C<$count> is the number of elements in C<$acctlines>. C<$total> is the
2149 total amount outstanding for all of the account lines.
2153 sub getboracctrecord {
2154 my ($env,$params) = @_;
2155 my $dbh = C4::Context->dbh;
2158 my $sth=$dbh->prepare("Select * from accountlines where
2159 borrowernumber=? order by date desc,timestamp desc");
2161 $sth->execute($params->{'borrowernumber'});
2163 while (my $data=$sth->fetchrow_hashref){
2164 #FIXME before reinstating: insecure?
2165 # if ($data->{'itemnumber'} ne ''){
2166 # $query="Select * from items,biblio where items.itemnumber=
2167 # '$data->{'itemnumber'}' and biblio.biblionumber=items.biblionumber";
2168 # my $sth2=$dbh->prepare($query);
2170 # my $data2=$sth2->fetchrow_hashref;
2174 $acctlines[$numlines] = $data;
2176 $total += $data->{'amountoutstanding'};
2179 return ($numlines,\@acctlines,$total);
2184 ($count, $lcount, $nacount, $fcount, $scount, $lostcount,
2185 $mending, $transit,$ocount) =
2186 &itemcount($env, $biblionumber, $type);
2188 Counts the number of items with the given biblionumber, broken down by
2193 If C<$type> is not set to C<intra>, lost, very overdue, and withdrawn
2194 items will not be counted.
2196 C<&itemcount> returns a nine-element list:
2198 C<$count> is the total number of items with the given biblionumber.
2200 C<$lcount> is the number of items at the Levin branch.
2202 C<$nacount> is the number of items that are neither borrowed, lost,
2203 nor withdrawn (and are therefore presumably on a shelf somewhere).
2205 C<$fcount> is the number of items at the Foxton branch.
2207 C<$scount> is the number of items at the Shannon branch.
2209 C<$lostcount> is the number of lost and very overdue items.
2211 C<$mending> is the number of items at the Mending branch (being
2214 C<$transit> is the number of items at the Transit branch (in transit
2217 C<$ocount> is the number of items that haven't arrived yet
2218 (aqorders.quantity - aqorders.quantityreceived).
2223 # FIXME - There's also a &C4::Biblio::itemcount.
2224 # Since they're all exported, acqui/acquire.pl doesn't compile with -w.
2226 my ($env,$bibnum,$type)=@_;
2227 my $dbh = C4::Context->dbh;
2228 my $query="Select * from items where
2230 if ($type ne 'intra'){
2231 $query.=" and ((itemlost <>1 and itemlost <> 2) or itemlost is NULL) and
2232 (wthdrawn <> 1 or wthdrawn is NULL)";
2234 my $sth=$dbh->prepare($query);
2236 $sth->execute($bibnum);
2246 while (my $data=$sth->fetchrow_hashref){
2249 my $sth2=$dbh->prepare("select * from issues,items where issues.itemnumber=
2250 ? and returndate is NULL
2251 and items.itemnumber=issues.itemnumber and ((items.itemlost <>1 and
2252 items.itemlost <> 2) or items.itemlost is NULL)
2253 and (wthdrawn <> 1 or wthdrawn is NULL)");
2254 $sth2->execute($data->{'itemnumber'});
2255 if (my $data2=$sth2->fetchrow_hashref){
2258 if ($data->{'holdingbranch'} eq 'C' || $data->{'holdingbranch'} eq 'LT'){
2261 if ($data->{'holdingbranch'} eq 'F' || $data->{'holdingbranch'} eq 'FP'){
2264 if ($data->{'holdingbranch'} eq 'S' || $data->{'holdingbranch'} eq 'SP'){
2267 if ($data->{'itemlost'} eq '1'){
2270 if ($data->{'itemlost'} eq '2'){
2273 if ($data->{'holdingbranch'} eq 'FM'){
2276 if ($data->{'holdingbranch'} eq 'TR'){
2283 my $sth2=$dbh->prepare("Select * from aqorders where biblionumber=?");
2284 $sth2->execute($bibnum);
2285 if (my $data=$sth2->fetchrow_hashref){
2286 $ocount=$data->{'quantity'} - $data->{'quantityreceived'};
2291 return ($count,$lcount,$nacount,$fcount,$scount,$lostcount,$mending,$transit,$ocount);
2296 $counts = &itemcount2($env, $biblionumber, $type);
2298 Counts the number of items with the given biblionumber, broken down by
2303 C<$type> may be either C<intra> or anything else. If it is not set to
2304 C<intra>, then the search will exclude lost, very overdue, and
2307 C<$&itemcount2> returns a reference-to-hash, with the following fields:
2313 The total number of items with this biblionumber.
2317 The number of items on order (aqorders.quantity -
2318 aqorders.quantityreceived).
2322 For each branch that has at least one copy of the book, C<$counts>
2323 will have a key with the branch name, giving the number of copies at
2331 my ($env,$bibnum,$type)=@_;
2332 my $dbh = C4::Context->dbh;
2333 my $query="Select * from items,branches where
2334 biblionumber=? and items.holdingbranch=branches.branchcode";
2335 if ($type ne 'intra'){
2336 $query.=" and ((itemlost <>1 and itemlost <> 2) or itemlost is NULL) and
2337 (wthdrawn <> 1 or wthdrawn is NULL)";
2339 my $sth=$dbh->prepare($query);
2341 $sth->execute($bibnum);
2344 while (my $data=$sth->fetchrow_hashref){
2351 'select * from items
2353 and not ((items.itemlost <>1 and items.itemlost <> 2)
2354 or items.itemlost is NULL)'
2357 'select * from items
2358 where itemnumber=? and not (wthdrawn <> 1 or wthdrawn is NULL)'
2360 'On Loan', "select * from issues,items
2361 where issues.itemnumber=? and returndate is NULL
2362 and items.itemnumber=issues.itemnumber"
2365 my($testlabel, $query2) = @$test;
2367 my $sth2=$dbh->prepare($query2);
2368 $sth2->execute($data->{'itemnumber'});
2370 # FIXME - fetchrow_hashref() can fail for any number of reasons
2371 # (e.g., a database server crash). Perhaps use a left join of some
2373 $status = $testlabel if $sth2->fetchrow_hashref;
2375 last if defined $status;
2377 $status = $data->{'branchname'} unless defined $status;
2380 my $sth2=$dbh->prepare("Select * from aqorders where biblionumber=? and
2381 datecancellationprinted is NULL and quantity > quantityreceived");
2382 $sth2->execute($bibnum);
2383 if (my $data=$sth2->fetchrow_hashref){
2384 $counts{'order'}=$data->{'quantity'} - $data->{'quantityreceived'};
2393 $description = &ItemType($itemtype);
2395 Given an item type code, returns the description for that type.
2400 # FIXME - I'm pretty sure that after the initial setup, the list of
2401 # item types doesn't change very often. Hence, it seems slow and
2402 # inefficient to make yet another database call to look up information
2403 # that'll only change every few months or years.
2405 # Much better, I think, to automatically build a Perl file that can be
2406 # included in those scripts that require it, e.g.:
2407 # @itemtypes = qw( ART BCD CAS CD F ... );
2409 # ART => "Art Prints",
2410 # BCD => "CD-ROM from book",
2411 # CD => "Compact disc (WN)",
2412 # F => "Free Fiction",
2415 # The web server can then run a cron job to rebuild this file from the
2416 # database every hour or so.
2418 # The same thing goes for branches, book funds, book sellers, currency
2419 # rates, printers, stopwords, and perhaps others.
2422 my $dbh = C4::Context->dbh;
2423 my $sth=$dbh->prepare("select description from itemtypes where itemtype=?");
2424 $sth->execute($type);
2425 my $dat=$sth->fetchrow_hashref;
2427 return ($dat->{'description'});
2432 ($count, @results) = &bibitems($biblionumber);
2434 Given the biblionumber for a book, C<&bibitems> looks up that book's
2435 biblioitems (different publications of the same book, the audio book
2436 and film versions, etc.).
2438 C<$count> is the number of elements in C<@results>.
2440 C<@results> is an array of references-to-hash; the keys are the fields
2441 of the C<biblioitems> and C<itemtypes> tables of the Koha database. In
2442 addition, C<itemlost> indicates the availability of the item: if it is
2443 "2", then all copies of the item are long overdue; if it is "1", then
2444 all copies are lost; otherwise, there is at least one copy available.
2450 my $dbh = C4::Context->dbh;
2451 my $sth = $dbh->prepare("SELECT biblioitems.*,
2453 MIN(items.itemlost) as itemlost,
2454 MIN(items.dateaccessioned) as dateaccessioned
2455 FROM biblioitems, itemtypes, items
2456 WHERE biblioitems.biblionumber = ?
2457 AND biblioitems.itemtype = itemtypes.itemtype
2458 AND biblioitems.biblioitemnumber = items.biblioitemnumber
2459 GROUP BY items.biblioitemnumber");
2462 $sth->execute($bibnum);
2463 while (my $data = $sth->fetchrow_hashref) {
2464 $results[$count] = $data;
2468 return($count, @results);
2473 @barcodes = &barcodes($biblioitemnumber);
2475 Given a biblioitemnumber, looks up the corresponding items.
2477 Returns an array of references-to-hash; the keys are C<barcode> and
2480 The returned items include very overdue items, but not lost ones.
2485 #called from request.pl
2486 my ($biblioitemnumber)=@_;
2487 my $dbh = C4::Context->dbh;
2488 my $sth=$dbh->prepare("SELECT barcode, itemlost, holdingbranch FROM items
2489 WHERE biblioitemnumber = ?
2490 AND (wthdrawn <> 1 OR wthdrawn IS NULL)");
2491 $sth->execute($biblioitemnumber);
2494 while (my $data=$sth->fetchrow_hashref){
2495 $barcodes[$i]=$data;
2504 ($count, @websites) = &getwebsites($biblionumber);
2506 Looks up the web sites pertaining to the book with the given
2509 C<$count> is the number of elements in C<@websites>.
2511 C<@websites> is an array of references-to-hash; the keys are the
2512 fields from the C<websites> table in the Koha database.
2517 my ($biblionumber) = @_;
2518 my $dbh = C4::Context->dbh;
2519 my $sth = $dbh->prepare("Select * from websites where biblionumber = ?");
2523 $sth->execute($biblionumber);
2524 while (my $data = $sth->fetchrow_hashref) {
2525 # FIXME - The URL scheme shouldn't be stripped off, at least
2526 # not here, since it's part of the URL, and will be useful in
2527 # constructing a link to the site. If you don't want the user
2528 # to see the "http://" part, strip that off when building the
2530 $data->{'url'} =~ s/^http:\/\///; # FIXME - Leaning toothpick
2532 $results[$count] = $data;
2537 return($count, @results);
2540 =item getwebbiblioitems
2542 ($count, @results) = &getwebbiblioitems($biblionumber);
2544 Given a book's biblionumber, looks up the web versions of the book
2545 (biblioitems with itemtype C<WEB>).
2547 C<$count> is the number of items in C<@results>. C<@results> is an
2548 array of references-to-hash; the keys are the items from the
2549 C<biblioitems> table of the Koha database.
2553 sub getwebbiblioitems {
2554 my ($biblionumber) = @_;
2555 my $dbh = C4::Context->dbh;
2556 my $sth = $dbh->prepare("Select * from biblioitems where biblionumber = ?
2557 and itemtype = 'WEB'");
2561 $sth->execute($biblionumber);
2562 while (my $data = $sth->fetchrow_hashref) {
2563 $data->{'url'} =~ s/^http:\/\///;
2564 $results[$count] = $data;
2569 return($count, @results);
2570 } # sub getwebbiblioitems
2576 ($count, @results) = &isbnsearch($isbn,$title);
2578 Given an isbn and/or a title, returns the biblios having it.
2579 Used in acqui.simple, isbnsearch.pl only
2581 C<$count> is the number of items in C<@results>. C<@results> is an
2582 array of references-to-hash; the keys are the items from the
2583 C<biblioitems> table of the Koha database.
2588 my ($isbn,$title) = @_;
2589 my $dbh = C4::Context->dbh;
2595 $query = "Select distinct biblio.*, biblioitems.classification from biblio, biblioitems where
2596 biblio.biblionumber = biblioitems.biblionumber";
2599 $query .= " and isbn like ?";
2600 @bind=(uc($isbn)."%");
2603 $query .= " and title like ?";
2606 $sth = $dbh->prepare($query);
2608 $sth->execute(@bind);
2609 while (my $data = $sth->fetchrow_hashref) {
2610 $results[$count] = $data;
2615 return($count, @results);
2620 $branchname = &getbranchname($branchcode);
2622 Given the branch code, the function returns the corresponding
2623 branch name for a comprehensive information display
2629 my ($branchcode) = @_;
2630 my $dbh = C4::Context->dbh;
2631 my $sth = $dbh->prepare("SELECT branchname FROM branches WHERE branchcode = ?");
2632 $sth->execute($branchcode);
2633 my $branchname = $sth->fetchrow();
2636 } # sub getbranchname
2638 =item getborrowercategory
2640 $description = &getborrowercategory($categorycode);
2642 Given the borrower's category code, the function returns the corresponding
2643 description for a comprehensive information display.
2647 sub getborrowercategory
2650 my $dbh = C4::Context->dbh;
2651 my $sth = $dbh->prepare("SELECT description FROM categories WHERE categorycode = ?");
2652 $sth->execute($catcode);
2653 my $description = $sth->fetchrow();
2655 return $description;
2656 } # sub getborrowercategory
2659 END { } # module clean-up code here (global destructor)
2668 Koha Developement team <info@koha.org>