1 package C4::Search; #assumes C4/Search
4 # Copyright 2000-2002 Katipo Communications
6 # This file is part of Koha.
8 # Koha is free software; you can redistribute it and/or modify it under the
9 # terms of the GNU General Public License as published by the Free Software
10 # Foundation; either version 2 of the License, or (at your option) any later
13 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
14 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
15 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
17 # You should have received a copy of the GNU General Public License along with
18 # Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
19 # Suite 330, Boston, MA 02111-1307 USA
28 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
30 # set the version for version checking
34 @EXPORT = qw(&CatSearch &BornameSearch &ItemInfo &KeywordSearch &subsearch
35 &itemdata &bibdata &GetItems &borrdata &itemnodata &itemcount
36 &borrdata2 &NewBorrowerNumber &bibitemdata &borrissues
37 &getboracctrecord &ItemType &itemissues &subject &subtitle
38 &addauthor &bibitems &barcodes &findguarantees &allissues &systemprefs
39 &findguarantor &getwebsites &getwebbiblioitems &catalogsearch itemcount2);
40 # make all your functions, whether exported or not;
44 my $query="select cardnumber,borrowernumber from borrowers where
46 my $sth=$dbh->prepare($query);
50 while (my $data=$sth->fetchrow_hashref){
61 my $query="select guarantor from borrowers where
62 borrowernumber='$bornum'";
63 my $sth=$dbh->prepare($query);
65 my $data=$sth->fetchrow_hashref;
67 $query="Select * from borrowers where
68 borrowernumber='$data->{'guarantor'}'";
69 $sth=$dbh->prepare($query);
71 $data=$sth->fetchrow_hashref;
80 my $sth=$dbh->prepare("select variable,value from systempreferences");
82 while (my ($variable,$value)=$sth->fetchrow) {
83 $systemprefs{$variable}=$value;
90 sub NewBorrowerNumber {
92 my $sth=$dbh->prepare("Select max(borrowernumber) from borrowers");
94 my $data=$sth->fetchrow_hashref;
96 $data->{'max(borrowernumber)'}++;
98 return($data->{'max(borrowernumber)'});
102 my ($env,$type,$search,$num,$offset)=@_;
103 my $dbh = C4Connect();
104 # foreach my $key (%$search){
105 # $search->{$key}=$dbh->quote($search->{$key});
107 my ($count,@results);
108 # print STDERR "Doing a search \n";
109 if ($search->{'itemnumber'} ne '' || $search->{'isbn'} ne ''){
110 print STDERR "Doing a precise search\n";
111 ($count,@results)=CatSearch($env,'precise',$search,$num,$offset);
114 if ($search->{'subject'} ne ''){
115 ($count,@results)=CatSearch($env,'subject',$search,$num,$offset);
117 if ($search->{'keyword'} ne ''){
118 ($count,@results)=&KeywordSearch($env,'keyword',$search,$num,$offset);
120 ($count,@results)=CatSearch($env,'loose',$search,$num,$offset);
125 if ($env->{itemcount} eq '1') {
126 foreach my $data (@results){
127 my ($counts) = itemcount2($env, $data->{'biblionumber'}, 'intra');
128 my $subject2=$data->{'subject'};
129 $subject2=~ s/ /%20/g;
130 $data->{'itemcount'}=$counts->{'total'};
131 my $totalitemcounts=0;
132 foreach my $key (keys %$counts){
133 if ($key ne 'total'){
134 #$data->{'location'}.="$key $counts->{$key} ";
135 $totalitemcounts+=$counts->{$key};
136 $data->{'locationhash'}->{$key}=$counts->{$key};
140 my $notavailabletext='';
141 foreach (sort keys %{$data->{'locationhash'}}) {
142 if ($_ eq 'notavailable') {
143 $notavailabletext="Not available";
144 my $c=$data->{'locationhash'}->{$_};
145 if ($totalitemcounts>1) {
146 $notavailabletext.=" ($c)";
150 my $c=$data->{'locationhash'}->{$_};
151 if ($totalitemcounts>1) {
152 $locationtext.=" ($c), ";
156 if ($notavailabletext) {
157 $locationtext.=$notavailabletext;
159 $locationtext=~s/, $//;
161 $data->{'location'}=$locationtext;
162 $data->{'subject2'}=$subject2;
165 return ($count,@results);
170 my ($env,$type,$search,$num,$offset)=@_;
171 my $dbh = &C4Connect;
172 $search->{'keyword'}=~ s/ +$//;
173 $search->{'keyword'}=~ s/'/\\'/;
174 my @key=split(' ',$search->{'keyword'});
178 my $query="Select biblionumber from biblio
179 where ((title like '$key[0]%' or title like '% $key[0]%')";
181 $query=$query." and (title like '$key[$i]%' or title like '% $key[$i]%')";
184 $query.= ") or ((biblio.notes like '$key[0]%' or biblio.notes like '% $key[0]%')";
185 for ($i=1;$i<$count;$i++){
186 $query.=" and (biblio.notes like '$key[$i]%' or biblio.notes like '% $key[$i]%')";
188 $query.= ") or ((seriestitle like '$key[0]%' or seriestitle like '% $key[0]%')";
189 for ($i=1;$i<$count;$i++){
190 $query.=" and (seriestitle like '$key[$i]%' or seriestitle like '% $key[$i]%')";
194 my $sth=$dbh->prepare($query);
197 while (my @res=$sth->fetchrow_array){
198 $results[$i]=$res[0];
202 my $set1=Set::Scalar->new(@results);
203 $query="Select biblionumber from bibliosubtitle where
204 ((subtitle like '$key[0]%' or subtitle like '% $key[0]%')";
205 for ($i=1;$i<$count;$i++){
206 $query.= " and (subtitle like '$key[$i]%' or subtitle like '% $key[$i]%')";
210 $sth=$dbh->prepare($query);
213 while (my @res=$sth->fetchrow_array){
214 $results[$i]=$res[0];
218 my $set2=Set::Scalar->new(@results);
222 $query ="Select biblionumber from biblioitems where
223 ((biblioitems.notes like '$key[0]%' or biblioitems.notes like '% $key[0]%')";
224 for ($i=1;$i<$count;$i++){
225 $query.=" and (biblioitems.notes like '$key[$i]%' or biblioitems.notes like '% $key[$i]%')";
229 $sth=$dbh->prepare($query);
232 while (my @res=$sth->fetchrow_array){
233 $results[$i]=$res[0];
237 my $set3=Set::Scalar->new(@results);
241 $sth=$dbh->prepare("Select biblionumber from bibliosubject where subject
242 like '%$search->{'keyword'}%' group by biblionumber");
245 while (my @res=$sth->fetchrow_array){
246 $results[$i]=$res[0];
250 my $set4=Set::Scalar->new(@results);
259 my @res = $set1->members;
263 # print "count $count";
264 if ($search->{'class'} ne ''){
266 my $query="select * from biblio,biblioitems where
267 biblio.biblionumber='$res[$i2]' and
268 biblio.biblionumber=biblioitems.biblionumber ";
269 if ($search->{'class'} ne ''){
270 my @temp=split(/\|/,$search->{'class'});
272 $query.= "and ( itemtype='$temp[0]'";
273 for (my $i=1;$i<$count;$i++){
274 $query.=" or itemtype='$temp[$i]'";
278 my $sth=$dbh->prepare($query);
281 if (my $data2=$sth->fetchrow_hashref){
282 my $dewey= $data2->{'dewey'};
283 my $subclass=$data2->{'subclass'};
285 ($dewey == 0) && ($dewey='');
286 ($dewey) && ($dewey.=" $subclass") ;
288 my $end=$offset +$num;
293 if ($i4 <=$end && $i4 > $offset){
294 $data2->{'dewey'}=$dewey;
297 # $res2[$i3]="$data2->{'author'}\t$data2->{'title'}\t$data2->{'biblionumber'}\t$data2->{'copyrightdate'}\t$dewey";
300 # print "in here $i3<br>";
311 while ($i2 < $num && $i2 < $count){
312 my $query="select * from biblio,biblioitems where
313 biblio.biblionumber='$res[$i2+$offset]' and
314 biblio.biblionumber=biblioitems.biblionumber ";
315 if ($search->{'class'} ne ''){
316 my @temp=split(/\|/,$search->{'class'});
318 $query.= "and ( itemtype='$temp[0]'";
319 for (my $i=1;$i<$count;$i++){
320 $query.=" or itemtype='$temp[$i]'";
324 if ($search->{'dewey'} ne ''){
325 $query.= "and (dewey like '$search->{'dewey'}%') ";
328 my $sth=$dbh->prepare($query);
331 if (my $data2=$sth->fetchrow_hashref){
332 my $dewey= $data2->{'dewey'};
333 my $subclass=$data2->{'subclass'};
335 ($dewey == 0) && ($dewey='');
336 ($dewey) && ($dewey.=" $subclass") ;
338 $data2->{'dewey'}=$dewey;
341 # $res2[$i]="$data2->{'author'}\t$data2->{'title'}\t$data2->{'biblionumber'}\t$data2->{'copyrightdate'}\t$dewey";
351 return($count,@res2);
355 my ($env,$type,$search,$num,$offset)=@_;
356 my $dbh = &C4Connect;
357 $search->{'keyword'}=~ s/ +$//;
358 $search->{'keyword'}=~ s/'/\\'/;
359 my @key=split(' ',$search->{'keyword'});
363 my $query ="Select * from biblio,bibliosubtitle,biblioitems where
364 biblio.biblionumber=biblioitems.biblionumber and
365 biblio.biblionumber=bibliosubtitle.biblionumber and
366 (((title like '$key[0]%' or title like '% $key[0]%')";
368 $query=$query." and (title like '$key[$i]%' or title like '% $key[$i]%')";
371 $query.= ") or ((subtitle like '$key[0]%' or subtitle like '% $key[0]%')";
372 for ($i=1;$i<$count;$i++){
373 $query.= " and (subtitle like '$key[$i]%' or subtitle like '% $key[$i]%')";
375 $query.= ") or ((seriestitle like '$key[0]%' or seriestitle like '% $key[0]%')";
376 for ($i=1;$i<$count;$i++){
377 $query.=" and (seriestitle like '$key[$i]%' or seriestitle like '% $key[$i]%')";
379 $query.= ") or ((biblio.notes like '$key[0]%' or biblio.notes like '% $key[0]%')";
380 for ($i=1;$i<$count;$i++){
381 $query.=" and (biblio.notes like '$key[$i]%' or biblio.notes like '% $key[$i]%')";
383 $query.= ") or ((biblioitems.notes like '$key[0]%' or biblioitems.notes like '% $key[0]%')";
384 for ($i=1;$i<$count;$i++){
385 $query.=" and (biblioitems.notes like '$key[$i]%' or biblioitems.notes like '% $key[$i]%')";
387 if ($search->{'keyword'} =~ /new zealand/i){
388 $query.= "or (title like 'nz%' or title like '% nz %' or title like '% nz' or subtitle like 'nz%'
389 or subtitle like '% nz %' or subtitle like '% nz' or author like 'nz %'
390 or author like '% nz %' or author like '% nz')"
392 if ($search->{'keyword'} eq 'nz' || $search->{'keyword'} eq 'NZ' ||
393 $search->{'keyword'} =~ /nz /i || $search->{'keyword'} =~ / nz /i ||
394 $search->{'keyword'} =~ / nz/i){
395 $query.= "or (title like 'new zealand%' or title like '% new zealand %'
396 or title like '% new zealand' or subtitle like 'new zealand%' or
397 subtitle like '% new zealand %'
398 or subtitle like '% new zealand' or author like 'new zealand%'
399 or author like '% new zealand %' or author like '% new zealand' or
400 seriestitle like 'new zealand%' or seriestitle like '% new zealand %'
401 or seriestitle like '% new zealand')"
404 if ($search->{'class'} ne ''){
405 my @temp=split(/\|/,$search->{'class'});
407 $query.= "and ( itemtype='$temp[0]'";
408 for (my $i=1;$i<$count;$i++){
409 $query.=" or itemtype='$temp[$i]'";
413 if ($search->{'dewey'} ne ''){
414 $query.= "and (dewey like '$search->{'dewey'}%') ";
416 $query.="group by biblio.biblionumber";
417 #$query.=" order by author,title";
419 my $sth=$dbh->prepare($query);
422 while (my $data=$sth->fetchrow_hashref){
423 # my $sti=$dbh->prepare("select dewey,subclass from biblioitems where biblionumber=$data->{'biblionumber'}
426 # my ($dewey, $subclass) = $sti->fetchrow;
427 my $dewey=$data->{'dewey'};
428 my $subclass=$data->{'subclass'};
430 ($dewey == 0) && ($dewey='');
431 ($dewey) && ($dewey.=" $subclass");
433 $results[$i]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey";
434 # print $results[$i];
438 $sth=$dbh->prepare("Select biblionumber from bibliosubject where subject
439 like '%$search->{'keyword'}%' group by biblionumber");
441 while (my $data=$sth->fetchrow_hashref){
442 $query="Select * from biblio,biblioitems where
443 biblio.biblionumber=$data->{'biblionumber'} and
444 biblio.biblionumber=biblioitems.biblionumber ";
445 if ($search->{'class'} ne ''){
446 my @temp=split(/\|/,$search->{'class'});
448 $query.= " and ( itemtype='$temp[0]'";
449 for (my $i=1;$i<$count;$i++){
450 $query.=" or itemtype='$temp[$i]'";
455 if ($search->{'dewey'} ne ''){
456 $query.= "and (dewey like '$search->{'dewey'}%') ";
458 my $sth2=$dbh->prepare($query);
461 while (my $data2=$sth2->fetchrow_hashref){
462 my $dewey= $data2->{'dewey'};
463 my $subclass=$data2->{'subclass'};
465 ($dewey == 0) && ($dewey='');
466 ($dewey) && ($dewey.=" $subclass") ;
468 $results[$i]="$data2->{'author'}\t$data2->{'title'}\t$data2->{'biblionumber'}\t$data2->{'copyrightdate'}\t$dewey";
469 # print $results[$i];
475 @results=sort @results;
482 while ($i2 < $count){
483 if ($results[$i2] ne $res[$i-1]){
484 $res[$i]=$results[$i2];
492 while ($i2 < $num && $i2 < $count){
493 $res2[$i2]=$res[$i2+$offset];
505 my ($env,$type,$search,$num,$offset)=@_;
506 my $dbh = &C4Connect;
509 $search->{'title'}=~ s/'/\\'/g;
510 $search->{'author'}=~ s/'/\\'/g;
511 $search->{'illustrator'}=~ s/'/\\'/g;
512 my $title = lc($search->{'title'});
514 if ($type eq 'loose') {
515 if ($search->{'author'} ne ''){
516 my @key=split(' ',$search->{'author'});
519 $query="select *,biblio.author,biblio.biblionumber from
521 left join additionalauthors
522 on additionalauthors.biblionumber =biblio.biblionumber
524 ((biblio.author like '$key[0]%' or biblio.author like '% $key[0]%' or
525 additionalauthors.author like '$key[0]%' or additionalauthors.author
529 $query=$query." and (
530 biblio.author like '$key[$i]%' or biblio.author like '% $key[$i]%' or
531 additionalauthors.author like '$key[$i]%' or additionalauthors.author like '% $key[$i]%'
536 if ($search->{'title'} ne ''){
537 my @key=split(' ',$search->{'title'});
540 $query.= " and (((title like '$key[0]%' or title like '% $key[0]%' or title like '% $key[0]')";
542 $query=$query." and (title like '$key[$i]%' or title like '% $key[$i]%' or title like '% $key[$i]')";
545 # $query.=") or ((subtitle like '$key[0]%' or subtitle like '% $key[0] %' or subtitle like '% $key[0]')";
546 # for ($i=1;$i<$count;$i++){
547 # $query.=" and (subtitle like '$key[$i]%' or subtitle like '% $key[$i] %' or subtitle like '% $key[$i]')";
549 $query.=") or ((seriestitle like '$key[0]%' or seriestitle like '% $key[0]%' or seriestitle like '% $key[0]')";
550 for ($i=1;$i<$count;$i++){
551 $query.=" and (seriestitle like '$key[$i]%' or seriestitle like '% $key[$i]%')";
553 $query.=") or ((unititle like '$key[0]%' or unititle like '% $key[0]%' or unititle like '% $key[0]')";
554 for ($i=1;$i<$count;$i++){
555 $query.=" and (unititle like '$key[$i]%' or unititle like '% $key[$i]%')";
558 #$query=$query. " and (title like '%$search->{'title'}%'
559 #or seriestitle like '%$search->{'title'}%')";
561 if ($search->{'abstract'} ne ''){
562 $query.= " and (abstract like '%$search->{'abstract'}%')";
564 if ($search->{'date-before'} ne ''){
565 $query.= " and (copyrightdate like '%$search->{'date-before'}%')";
568 $query.=" group by biblio.biblionumber";
570 if ($search->{'title'} ne '') {
571 if ($search->{'ttype'} eq 'exact'){
572 $query="select * from biblio
574 (biblio.title='$search->{'title'}' or (biblio.unititle = '$search->{'title'}'
575 or biblio.unititle like '$search->{'title'} |%' or
576 biblio.unititle like '%| $search->{'title'} |%' or
577 biblio.unititle like '%| $search->{'title'}') or
578 (biblio.seriestitle = '$search->{'title'}' or
579 biblio.seriestitle like '$search->{'title'} |%' or
580 biblio.seriestitle like '%| $search->{'title'} |%' or
581 biblio.seriestitle like '%| $search->{'title'}')
584 my @key=split(' ',$search->{'title'});
587 $query="select * from biblio
588 left join bibliosubtitle on
589 biblio.biblionumber=bibliosubtitle.biblionumber
591 (((title like '$key[0]%' or title like '% $key[0]%' or title like '% $key[0]')";
593 $query=$query." and (title like '$key[$i]%' or title like '% $key[$i]%' or title like '% $key[$i]')";
596 $query.=") or ((subtitle like '$key[0]%' or subtitle like '% $key[0]%' or subtitle like '% $key[0]')";
597 for ($i=1;$i<$count;$i++){
598 $query.=" and (subtitle like '$key[$i]%' or subtitle like '% $key[$i]%' or subtitle like '% $key[$i]')";
600 $query.=") or ((seriestitle like '$key[0]%' or seriestitle like '% $key[0]%' or seriestitle like '% $key[0]')";
601 for ($i=1;$i<$count;$i++){
602 $query.=" and (seriestitle like '$key[$i]%' or seriestitle like '% $key[$i]%')";
604 $query.=") or ((unititle like '$key[0]%' or unititle like '% $key[0]%' or unititle like '% $key[0]')";
605 for ($i=1;$i<$count;$i++){
606 $query.=" and (unititle like '$key[$i]%' or unititle like '% $key[$i]%')";
610 if ($search->{'abstract'} ne ''){
611 $query.= " and (abstract like '%$search->{'abstract'}%')";
613 if ($search->{'date-before'} ne ''){
614 $query.= " and (copyrightdate like '%$search->{'date-before'}%')";
616 } elsif ($search->{'class'} ne ''){
617 $query="select * from biblioitems,biblio where biblio.biblionumber=biblioitems.biblionumber";
618 my @temp=split(/\|/,$search->{'class'});
620 $query.= " and ( itemtype='$temp[0]'";
621 for (my $i=1;$i<$count;$i++){
622 $query.=" or itemtype='$temp[$i]'";
625 if ($search->{'illustrator'} ne ''){
626 $query.=" and illus like '%".$search->{'illustrator'}."%' ";
628 if ($search->{'dewey'} ne ''){
629 $query.=" and biblioitems.dewey like '$search->{'dewey'}%'";
631 } elsif ($search->{'dewey'} ne ''){
632 $query="select * from biblioitems,biblio
633 where biblio.biblionumber=biblioitems.biblionumber
634 and biblioitems.dewey like '$search->{'dewey'}%'";
635 } elsif ($search->{'illustrator'} ne '') {
636 $query="select * from biblioitems,biblio
637 where biblio.biblionumber=biblioitems.biblionumber
638 and biblioitems.illus like '%".$search->{'illustrator'}."%'";
639 } elsif ($search->{'publisher'} ne ''){
640 $query.= "Select * from biblio,biblioitems where biblio.biblionumber
641 =biblioitems.biblionumber and (publishercode like '%$search->{'publisher'}%')";
642 } elsif ($search->{'abstract'} ne ''){
643 $query.= "Select * from biblio where abstract like '%$search->{'abstract'}%'";
645 } elsif ($search->{'date-before'} ne ''){
646 $query.= "Select * from biblio where copyrightdate like '%$search->{'date-before'}%'";
648 $query .=" group by biblio.biblionumber";
651 if ($type eq 'subject'){
652 my @key=split(' ',$search->{'subject'});
655 $query="select distinct(subject) from bibliosubject where( subject like
656 '$key[0]%' or subject like '% $key[0]%' or subject like '% $key[0]' or subject like '%($key[0])%')";
658 $query.=" and (subject like '$key[$i]%' or subject like '% $key[$i]%'
659 or subject like '% $key[$i]'
660 or subject like '%($key[$i])%')";
663 if ($search->{'subject'} eq 'NZ' || $search->{'subject'} eq 'nz'){
664 $query.= " or (subject like 'NEW ZEALAND %' or subject like '% NEW ZEALAND %'
665 or subject like '% NEW ZEALAND' or subject like '%(NEW ZEALAND)%' ) ";
666 } elsif ( $search->{'subject'} =~ /^nz /i || $search->{'subject'} =~ / nz /i || $search->{'subject'} =~ / nz$/i){
667 $query=~ s/ nz/ NEW ZEALAND/ig;
668 $query=~ s/nz /NEW ZEALAND /ig;
669 $query=~ s/\(nz\)/\(NEW ZEALAND\)/gi;
672 if ($type eq 'precise'){
674 if ($search->{'item'} ne ''){
675 $query="select * from items,biblio ";
676 my $search2=uc $search->{'item'};
677 $query=$query." where
678 items.biblionumber=biblio.biblionumber
679 and barcode='$search2'";
681 if ($search->{'isbn'} ne ''){
682 my $search2=uc $search->{'isbn'};
683 my $query1 = "select * from biblioitems where isbn='$search2'";
684 my $sth1=$dbh->prepare($query1);
685 # print STDERR "$query1\n";
688 while (my $data=$sth1->fetchrow_hashref) {
689 $query="select * from biblioitems,biblio where
690 biblio.biblionumber = $data->{'biblionumber'}
691 and biblioitems.biblionumber = biblio.biblionumber";
692 my $sth=$dbh->prepare($query);
694 my $data=$sth->fetchrow_hashref;
695 my ($dewey, $subclass) = ($data->{'dewey'}, $data->{'subclass'});
697 ($dewey == 0) && ($dewey='');
698 ($dewey) && ($dewey.=" $subclass");
699 $data->{'dewey'}=$dewey;
701 # $results[$i2]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey\t$data->{'isbn'}\t$data->{'itemtype'}";
709 if ($type ne 'precise' && $type ne 'subject'){
710 if ($search->{'author'} ne ''){
711 $query=$query." order by biblio.author,title";
713 $query=$query." order by title";
716 if ($type eq 'subject'){
717 $query=$query." order by subject";
720 #print STDERR "$query\n";
721 my $sth=$dbh->prepare($query);
725 my $limit= $num+$offset;
726 while (my $data=$sth->fetchrow_hashref){
727 my $query="select dewey,subclass,publishercode from biblioitems where biblionumber=$data->{'biblionumber'}";
728 if ($search->{'class'} ne ''){
729 my @temp=split(/\|/,$search->{'class'});
731 $query.= " and ( itemtype='$temp[0]'";
732 for (my $i=1;$i<$count;$i++){
733 $query.=" or itemtype='$temp[$i]'";
737 if ($search->{'dewey'} ne ''){
738 $query.=" and dewey='$search->{'dewey'}' ";
740 if ($search->{'illustrator'} ne ''){
741 $query.=" and illus like '%".$search->{'illustrator'}."%' ";
743 if ($search->{'publisher'} ne ''){
744 $query.= " and (publishercode like '%$search->{'publisher'}%')";
747 my $sti=$dbh->prepare($query);
754 if ($bibitemdata = $sti->fetchrow_hashref() || $type eq 'subject'){
756 $dewey=$bibitemdata->{'dewey'};
757 $subclass=$bibitemdata->{'subclass'};
758 $publishercode=$bibitemdata->{'publishercode'};
760 print STDERR "$dewey $subclass $publishercode\n";
762 ($dewey == 0) && ($dewey='');
763 ($dewey) && ($dewey.=" $subclass");
764 $data->{'dewey'}=$dewey;
765 $data->{'publishercode'}=$publishercode;
768 if ($count > $offset && $count <= $limit){
776 #if ($type ne 'precise'){
780 return($count,@results);
783 sub updatesearchstats{
789 my ($env,$subject)=@_;
791 $subject=$dbh->quote($subject);
792 my $query="Select * from biblio,bibliosubject where
793 biblio.biblionumber=bibliosubject.biblionumber and
794 bibliosubject.subject=$subject group by biblio.biblionumber
795 order by biblio.title";
796 my $sth=$dbh->prepare($query);
801 while (my $data=$sth->fetchrow_hashref){
802 $results[$i]="$data->{'title'}\t$data->{'author'}\t$data->{'biblionumber'}";
812 my ($env,$biblionumber,$type) = @_;
813 my $dbh = &C4Connect;
814 my $query = "SELECT * FROM items, biblio, biblioitems, itemtypes
815 WHERE items.biblionumber = ?
816 AND biblioitems.biblioitemnumber = items.biblioitemnumber
817 AND biblioitems.itemtype = itemtypes.itemtype
818 AND biblio.biblionumber = items.biblionumber";
819 if ($type ne 'intra'){
820 $query .= " and ((items.itemlost<>1 and items.itemlost <> 2)
821 or items.itemlost is NULL)
822 and (wthdrawn <> 1 or wthdrawn is NULL)";
824 $query .= " order by items.dateaccessioned desc";
826 my $sth=$dbh->prepare($query);
827 $sth->execute($biblionumber);
831 while (my $data=$sth->fetchrow_hashref){
832 my $iquery = "Select * from issues
833 where itemnumber = '$data->{'itemnumber'}'
834 and returndate is null";
836 my $isth=$dbh->prepare($iquery);
838 if (my $idata=$isth->fetchrow_hashref){
839 my @temp=split('-',$idata->{'date_due'});
840 $datedue = "$temp[2]/$temp[1]/$temp[0]";
842 if ($data->{'itemlost'} eq '2'){
843 $datedue='Very Overdue';
845 if ($data->{'itemlost'} eq '1'){
848 if ($data->{'wthdrawn'} eq '1'){
849 $datedue="Cancelled";
852 $datedue="Available";
853 my ($restype,$reserves)=CheckReserves($data->{'itemnumber'});
859 #get branch information.....
860 my $bquery = "SELECT * FROM branches
861 WHERE branchcode = '$data->{'holdingbranch'}'";
862 my $bsth=$dbh->prepare($bquery);
864 if (my $bdata=$bsth->fetchrow_hashref){
865 $data->{'branchname'} = $bdata->{'branchname'};
868 my $class = $data->{'classification'};
869 my $dewey = $data->{'dewey'};
871 if ($dewey eq "000.") { $dewey = "";};
872 if ($dewey < 10){$dewey='00'.$dewey;}
873 if ($dewey < 100 && $dewey > 10){$dewey='0'.$dewey;}
878 $class = $class.$dewey;
880 $class = $class.$data->{'subclass'};
882 # $results[$i]="$data->{'title'}\t$data->{'barcode'}\t$datedue\t$data->{'branchname'}\t$data->{'dewey'}";
883 my @temp=split('-',$data->{'datelastseen'});
884 my $date="$temp[2]/$temp[1]/$temp[0]";
885 $data->{'datelastseen'}=$date;
886 $data->{'datedue'}=$datedue;
887 $data->{'class'}=$class;
892 my $query2="Select * from aqorders where biblionumber=$biblionumber";
893 my $sth2=$dbh->prepare($query2);
897 if ($data=$sth2->fetchrow_hashref){
898 $ocount=$data->{'quantity'} - $data->{'quantityreceived'};
900 $data->{'ocount'}=$ocount;
901 $data->{'order'}="One Order";
912 my ($env,$biblionumber)=@_;
913 #debug_msg($env,"GetItems");
914 my $dbh = &C4Connect;
915 my $query = "Select * from biblioitems where (biblionumber = $biblionumber)";
916 #debug_msg($env,$query);
917 my $sth=$dbh->prepare($query);
919 #debug_msg($env,"executed query");
922 while (my $data=$sth->fetchrow_hashref) {
923 #debug_msg($env,$data->{'biblioitemnumber'});
924 my $dewey = $data->{'dewey'};
926 my $line = $data->{'biblioitemnumber'}."\t".$data->{'itemtype'};
927 $line = $line."\t$data->{'classification'}\t$dewey";
928 $line = $line."\t$data->{'subclass'}\t$data->{isbn}";
929 $line = $line."\t$data->{'volume'}\t$data->{number}";
930 my $isth= $dbh->prepare("select * from items where biblioitemnumber = $data->{'biblioitemnumber'}");
932 while (my $idata = $isth->fetchrow_hashref) {
933 my $iline = $idata->{'barcode'}."[".$idata->{'holdingbranch'}."[";
934 if ($idata->{'notforloan'} == 1) {
935 $iline = $iline."NFL ";
937 if ($idata->{'itemlost'} == 1) {
938 $iline = $iline."LOST ";
940 $line = $line."\t$iline";
943 $results[$i] = $line;
954 my $query="Select * from items,biblioitems where barcode='$barcode'
955 and items.biblioitemnumber=biblioitems.biblioitemnumber";
957 my $sth=$dbh->prepare($query);
959 my $data=$sth->fetchrow_hashref;
967 my ($bibnum, $type) = @_;
969 my $query = "Select *, biblio.notes
970 from biblio, biblioitems
971 left join bibliosubtitle on
972 biblio.biblionumber = bibliosubtitle.biblionumber
973 where biblio.biblionumber = $bibnum
974 and biblioitems.biblionumber = $bibnum";
975 my $sth = $dbh->prepare($query);
979 $data = $sth->fetchrow_hashref;
982 $query = "Select * from bibliosubject where biblionumber = '$bibnum'";
983 $sth = $dbh->prepare($query);
985 while (my $dat = $sth->fetchrow_hashref){
986 $data->{'subject'} .= " , $dat->{'subject'}";
998 my $query = "Select *,biblioitems.notes as bnotes from biblio, biblioitems,itemtypes
999 where biblio.biblionumber = biblioitems.biblionumber
1000 and biblioitemnumber = $bibitem
1001 and biblioitems.itemtype = itemtypes.itemtype";
1002 my $sth = $dbh->prepare($query);
1007 $data = $sth->fetchrow_hashref;
1018 my $query="Select * from bibliosubject where biblionumber=$bibnum";
1019 my $sth=$dbh->prepare($query);
1023 while (my $data=$sth->fetchrow_hashref){
1029 return($i,\@results);
1035 my $query="Select * from additionalauthors where biblionumber=$bibnum";
1036 my $sth=$dbh->prepare($query);
1040 while (my $data=$sth->fetchrow_hashref){
1046 return($i,\@results);
1052 my $query="Select * from bibliosubtitle where biblionumber=$bibnum";
1053 my $sth=$dbh->prepare($query);
1057 while (my $data=$sth->fetchrow_hashref){
1063 return($i,\@results);
1069 my ($bibitem, $biblio)=@_;
1070 my $dbh = C4Connect;
1071 my $query = "Select * from items where
1072 items.biblioitemnumber = '$bibitem'";
1073 my $sth = $dbh->prepare($query)
1074 || die $dbh->errstr;
1079 || die $sth->errstr;
1081 while (my $data = $sth->fetchrow_hashref) {
1082 my $query2 = "select * from issues,borrowers
1083 where itemnumber = $data->{'itemnumber'}
1084 and returndate is NULL
1085 and issues.borrowernumber = borrowers.borrowernumber";
1086 my $sth2 = $dbh->prepare($query2);
1089 if (my $data2 = $sth2->fetchrow_hashref) {
1090 $data->{'date_due'} = $data2->{'date_due'};
1091 $data->{'card'} = $data2->{'cardnumber'};
1093 if ($data->{'wthdrawn'} eq '1') {
1094 $data->{'date_due'} = 'Cancelled';
1096 $data->{'date_due'} = 'Available';
1101 $query2 = "select * from issues, borrowers
1102 where itemnumber = '$data->{'itemnumber'}'
1103 and issues.borrowernumber = borrowers.borrowernumber
1104 and returndate is not NULL
1105 order by returndate desc,timestamp desc";
1106 $sth2 = $dbh->prepare($query2)
1107 || die $dbh->errstr;
1109 || die $sth2->errstr;
1111 for (my $i2 = 0; $i2 < 2; $i2++) {
1112 if (my $data2 = $sth2->fetchrow_hashref) {
1113 $data->{"timestamp$i2"} = $data2->{'timestamp'};
1114 $data->{"card$i2"} = $data2->{'cardnumber'};
1115 $data->{"borrower$i2"} = $data2->{'borrowernumber'};
1120 $results[$i] = $data;
1131 my ($env,$dbh,$itemnumber) = @_;
1133 my $query="Select * from biblio,items,biblioitems
1134 where items.itemnumber = '$itemnumber'
1135 and biblio.biblionumber = items.biblionumber
1136 and biblioitems.biblioitemnumber = items.biblioitemnumber";
1137 my $sth=$dbh->prepare($query);
1140 my $data=$sth->fetchrow_hashref;
1146 #used by member enquiries from the intranet
1147 #called by member.pl
1149 my ($env,$searchstring,$type)=@_;
1150 my $dbh = &C4Connect;
1151 $searchstring=~ s/\'/\\\'/g;
1152 my @data=split(' ',$searchstring);
1154 my $query="Select * from borrowers
1155 where ((surname like \"$data[0]%\" or surname like \"% $data[0]%\"
1156 or firstname like \"$data[0]%\" or firstname like \"% $data[0]%\"
1157 or othernames like \"$data[0]%\" or othernames like \"% $data[0]%\")
1159 for (my $i=1;$i<$count;$i++){
1160 $query=$query." and (surname like \"$data[$i]%\" or surname like \"% $data[$i]%\"
1161 or firstname like \"$data[$i]%\" or firstname like \"% $data[$i]%\"
1162 or othernames like \"$data[$i]%\" or othernames like \"% $data[$i]%\")";
1164 $query=$query.") or cardnumber = \"$searchstring\"
1165 order by surname,firstname";
1166 # print $query,"\n";
1167 my $sth=$dbh->prepare($query);
1171 while (my $data=$sth->fetchrow_hashref){
1172 push(@results,$data);
1178 return ($cnt,\@results);
1182 my ($cardnumber,$bornum)=@_;
1183 $cardnumber = uc $cardnumber;
1187 $query="Select * from borrowers where cardnumber='$cardnumber'";
1189 $query="Select * from borrowers where borrowernumber='$bornum'";
1192 my $sth=$dbh->prepare($query);
1194 my $data=$sth->fetchrow_hashref;
1204 $query="Select * from issues,biblio,items where borrowernumber='$bornum' and
1205 items.itemnumber=issues.itemnumber and
1206 items.biblionumber=biblio.biblionumber and issues.returndate is NULL order
1209 my $sth=$dbh->prepare($query);
1213 while (my $data=$sth->fetchrow_hashref){
1219 return($i,\@result);
1223 my ($bornum,$order,$limit)=@_;
1226 $query="Select * from issues,biblio,items,biblioitems
1227 where borrowernumber='$bornum' and
1228 items.biblioitemnumber=biblioitems.biblioitemnumber and
1229 items.itemnumber=issues.itemnumber and
1230 items.biblionumber=biblio.biblionumber";
1231 $query.=" order by $order";
1233 $query.=" limit $limit";
1236 my $sth=$dbh->prepare($query);
1240 while (my $data=$sth->fetchrow_hashref){
1246 return($i,\@result);
1250 my ($env,$bornum)=@_;
1252 my $query="Select count(*) from issues where borrowernumber='$bornum' and
1253 returndate is NULL";
1255 my $sth=$dbh->prepare($query);
1257 my $data=$sth->fetchrow_hashref;
1259 $sth=$dbh->prepare("Select count(*) from issues where
1260 borrowernumber='$bornum' and date_due < now() and returndate is NULL");
1262 my $data2=$sth->fetchrow_hashref;
1264 $sth=$dbh->prepare("Select sum(amountoutstanding) from accountlines where
1265 borrowernumber='$bornum'");
1267 my $data3=$sth->fetchrow_hashref;
1271 return($data2->{'count(*)'},$data->{'count(*)'},$data3->{'sum(amountoutstanding)'});
1275 sub getboracctrecord {
1276 my ($env,$params) = @_;
1280 my $query= "Select * from accountlines where
1281 borrowernumber=$params->{'borrowernumber'} order by date desc,timestamp desc";
1282 my $sth=$dbh->prepare($query);
1286 while (my $data=$sth->fetchrow_hashref){
1287 # if ($data->{'itemnumber'} ne ''){
1288 # $query="Select * from items,biblio where items.itemnumber=
1289 # '$data->{'itemnumber'}' and biblio.biblionumber=items.biblionumber";
1290 # my $sth2=$dbh->prepare($query);
1292 # my $data2=$sth2->fetchrow_hashref;
1296 $acctlines[$numlines] = $data;
1298 $total = $total+ $data->{'amountoutstanding'};
1302 return ($numlines,\@acctlines,$total);
1307 my ($env,$bibnum,$type)=@_;
1309 my $query="Select * from items where
1310 biblionumber=$bibnum ";
1311 if ($type ne 'intra'){
1312 $query.=" and ((itemlost <>1 and itemlost <> 2) or itemlost is NULL) and
1313 (wthdrawn <> 1 or wthdrawn is NULL)";
1315 my $sth=$dbh->prepare($query);
1327 while (my $data=$sth->fetchrow_hashref){
1329 my $query2="select * from issues,items where issues.itemnumber=
1330 '$data->{'itemnumber'}' and returndate is NULL
1331 and items.itemnumber=issues.itemnumber and ((items.itemlost <>1 and
1332 items.itemlost <> 2) or items.itemlost is NULL)
1333 and (wthdrawn <> 1 or wthdrawn is NULL)";
1335 my $sth2=$dbh->prepare($query2);
1337 if (my $data2=$sth2->fetchrow_hashref){
1340 if ($data->{'holdingbranch'} eq 'C'){
1343 if ($data->{'holdingbranch'} eq 'F' || $data->{'holdingbranch'} eq 'FP'){
1346 if ($data->{'holdingbranch'} eq 'S' || $data->{'holdingbranch'} eq 'SP'){
1349 if ($data->{'itemlost'} eq '1'){
1352 if ($data->{'itemlost'} eq '2'){
1355 if ($data->{'holdingbranch'} eq 'FM'){
1358 if ($data->{'holdingbranch'} eq 'TR'){
1365 my $query2="Select * from aqorders where biblionumber=$bibnum";
1366 my $sth2=$dbh->prepare($query2);
1368 if (my $data=$sth2->fetchrow_hashref){
1369 $ocount=$data->{'quantity'} - $data->{'quantityreceived'};
1375 return ($count,$lcount,$nacount,$fcount,$scount,$lostcount,$mending,$transit,$ocount);
1380 my ($env,$bibnum,$type)=@_;
1382 my $query="Select * from items,branches where
1383 biblionumber=$bibnum and items.holdingbranch=branches.branchcode";
1384 if ($type ne 'intra'){
1385 $query.=" and ((itemlost <>1 and itemlost <> 2) or itemlost is NULL) and
1386 (wthdrawn <> 1 or wthdrawn is NULL)";
1388 my $sth=$dbh->prepare($query);
1393 while (my $data=$sth->fetchrow_hashref){
1395 my $query2="select * from issues,items where issues.itemnumber=
1396 '$data->{'itemnumber'}' and returndate is NULL
1397 and items.itemnumber=issues.itemnumber and ((items.itemlost <>1 and
1398 items.itemlost <> 2) or items.itemlost is NULL)
1399 and (wthdrawn <> 1 or wthdrawn is NULL)";
1401 my $sth2=$dbh->prepare($query2);
1403 if (my $data2=$sth2->fetchrow_hashref){
1404 $counts{'not available'}++;
1406 $counts{$data->{'branchname'}}++;
1410 my $query2="Select * from aqorders where biblionumber=$bibnum and
1411 datecancellationprinted is NULL and quantity > quantityreceived";
1412 my $sth2=$dbh->prepare($query2);
1414 if (my $data=$sth2->fetchrow_hashref){
1415 $counts{'order'}=$data->{'quantity'} - $data->{'quantityreceived'};
1427 my $query="select description from itemtypes where itemtype='$type'";
1428 my $sth=$dbh->prepare($query);
1430 my $dat=$sth->fetchrow_hashref;
1433 return ($dat->{'description'});
1439 my $dbh = C4Connect;
1440 my $query = "SELECT biblioitems.*, itemtypes.*, MIN(items.itemlost) as itemlost
1441 FROM biblioitems, itemtypes, items
1442 WHERE biblioitems.biblionumber = ?
1443 AND biblioitems.itemtype = itemtypes.itemtype
1444 AND biblioitems.biblioitemnumber = items.biblioitemnumber
1445 GROUP BY items.biblioitemnumber";
1446 my $sth = $dbh->prepare($query);
1449 $sth->execute($bibnum);
1450 while (my $data = $sth->fetchrow_hashref) {
1451 $results[$count] = $data;
1456 return($count, @results);
1460 #called from request.pl
1461 my ($biblioitemnumber)=@_;
1463 my $query="SELECT barcode, itemlost FROM items
1464 WHERE biblioitemnumber = ?
1465 AND (wthdrawn <> 1 OR wthdrawn IS NULL)";
1466 my $sth=$dbh->prepare($query);
1467 $sth->execute($biblioitemnumber);
1470 while (my $data=$sth->fetchrow_hashref){
1471 $barcodes[$i]=$data;
1481 my ($biblionumber) = @_;
1482 my $dbh = C4Connect;
1483 my $query = "Select * from websites where biblionumber = $biblionumber";
1484 my $sth = $dbh->prepare($query);
1489 while (my $data = $sth->fetchrow_hashref) {
1490 $data->{'url'} =~ s/^http:\/\///;
1491 $results[$count] = $data;
1497 return($count, @results);
1501 sub getwebbiblioitems {
1502 my ($biblionumber) = @_;
1503 my $dbh = C4Connect;
1504 my $query = "Select * from biblioitems where biblionumber = $biblionumber
1505 and itemtype = 'WEB'";
1506 my $sth = $dbh->prepare($query);
1511 while (my $data = $sth->fetchrow_hashref) {
1512 $data->{'url'} =~ s/^http:\/\///;
1513 $results[$count] = $data;
1519 return($count, @results);
1520 } # sub getwebbiblioitems
1523 END { } # module clean-up code here (global destructor)
1527 C4::Search - Module that provides Catalog searching for Koha
1532 my ($count,@results)=catalogsearch($env,$type,$search,$num,$offset);
1536 This module provides the searching facilities for the Catalog.
1537 Here I should go through and document each function thats exported and what it does. But I havent yet.
1539 my ($count,@results)=catalogsearch($env,$type,$search,$num,$offset);
1540 This is a front end to all the other searches, depending on what is passed
1541 to it, it calls the appropriate search
1549 Koha Developement team <info@koha.org>