1 package C4::Search; #assumes C4/Search
10 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
12 # set the version for version checking
16 @EXPORT = qw(&CatSearch &BornameSearch &ItemInfo &KeywordSearch &subsearch
17 &itemdata &bibdata &GetItems &borrdata &itemnodata &itemcount
18 &borrdata2 &NewBorrowerNumber &bibitemdata &borrissues
19 &getboracctrecord &ItemType &itemissues &subject &subtitle
20 &addauthor &bibitems &barcodes &findguarantees &allissues &systemprefs
21 &findguarantor &getwebsites &getwebbiblioitems &catalogsearch itemcount2);
22 # make all your functions, whether exported or not;
26 my $query="select cardnumber,borrowernumber from borrowers where
28 my $sth=$dbh->prepare($query);
32 while (my $data=$sth->fetchrow_hashref){
43 my $query="select guarantor from borrowers where
44 borrowernumber='$bornum'";
45 my $sth=$dbh->prepare($query);
47 my $data=$sth->fetchrow_hashref;
49 $query="Select * from borrowers where
50 borrowernumber='$data->{'guarantor'}'";
51 $sth=$dbh->prepare($query);
53 $data=$sth->fetchrow_hashref;
62 my $sth=$dbh->prepare("select variable,value from systempreferences");
64 while (my ($variable,$value)=$sth->fetchrow) {
65 $systemprefs{$variable}=$value;
72 sub NewBorrowerNumber {
74 my $sth=$dbh->prepare("Select max(borrowernumber) from borrowers");
76 my $data=$sth->fetchrow_hashref;
78 $data->{'max(borrowernumber)'}++;
80 return($data->{'max(borrowernumber)'});
84 my ($env,$type,$search,$num,$offset)=@_;
85 my $dbh = C4Connect();
86 # foreach my $key (%$search){
87 # $search->{$key}=$dbh->quote($search->{$key});
90 # print STDERR "Doing a search \n";
91 if ($search->{'itemnumber'} ne '' || $search->{'isbn'} ne ''){
92 print STDERR "Doing a precise search\n";
93 ($count,@results)=CatSearch($env,'precise',$search,$num,$offset);
96 if ($search->{'subject'} ne ''){
97 ($count,@results)=CatSearch($env,'subject',$search,$num,$offset);
99 if ($search->{'keyword'} ne ''){
100 ($count,@results)=&KeywordSearch($env,'keyword',$search,$num,$offset);
102 ($count,@results)=CatSearch($env,'loose',$search,$num,$offset);
107 if ($env->{itemcount}) {
108 foreach my $data (@results){
109 my ($counts) = itemcount2($env, $data->{'biblionumber'}, 'intra');
110 my $subject2=$data->{'subject'};
111 $subject2=~ s/ /%20/g;
112 $data->{'itemcount'}=$counts->{'total'};
113 my $totalitemcounts=0;
114 foreach my $key (keys %$counts){
115 if ($key ne 'total'){
116 #$data->{'location'}.="$key $counts->{$key} ";
117 $totalitemcounts+=$counts->{$key};
118 $data->{'locationhash'}->{$key}=$counts->{$key};
122 my $notavailabletext='';
123 foreach (sort keys %{$data->{'locationhash'}}) {
124 if ($_ eq 'notavailable') {
125 $notavailabletext="Not available";
126 my $c=$data->{'locationhash'}->{$_};
127 if ($totalitemcounts>1) {
128 $notavailabletext.=" ($c)";
132 my $c=$data->{'locationhash'}->{$_};
133 if ($totalitemcounts>1) {
134 $locationtext.=" ($c), ";
138 if ($notavailabletext) {
139 $locationtext.=$notavailabletext;
141 $locationtext=~s/, $//;
143 $data->{'location'}=$locationtext;
144 $data->{'subject2'}=$subject2;
147 return ($count,@results);
152 my ($env,$type,$search,$num,$offset)=@_;
153 my $dbh = &C4Connect;
154 $search->{'keyword'}=~ s/ +$//;
155 $search->{'keyword'}=~ s/'/\\'/;
156 my @key=split(' ',$search->{'keyword'});
160 my $query="Select biblionumber from biblio
161 where ((title like '$key[0]%' or title like '% $key[0]%')";
163 $query=$query." and (title like '$key[$i]%' or title like '% $key[$i]%')";
166 $query.= ") or ((biblio.notes like '$key[0]%' or biblio.notes like '% $key[0]%')";
167 for ($i=1;$i<$count;$i++){
168 $query.=" and (biblio.notes like '$key[$i]%' or biblio.notes like '% $key[$i]%')";
170 $query.= ") or ((seriestitle like '$key[0]%' or seriestitle like '% $key[0]%')";
171 for ($i=1;$i<$count;$i++){
172 $query.=" and (seriestitle like '$key[$i]%' or seriestitle like '% $key[$i]%')";
176 my $sth=$dbh->prepare($query);
179 while (my @res=$sth->fetchrow_array){
180 $results[$i]=$res[0];
184 my $set1=Set::Scalar->new(@results);
185 $query="Select biblionumber from bibliosubtitle where
186 ((subtitle like '$key[0]%' or subtitle like '% $key[0]%')";
187 for ($i=1;$i<$count;$i++){
188 $query.= " and (subtitle like '$key[$i]%' or subtitle like '% $key[$i]%')";
192 $sth=$dbh->prepare($query);
195 while (my @res=$sth->fetchrow_array){
196 $results[$i]=$res[0];
200 my $set2=Set::Scalar->new(@results);
204 $query ="Select biblionumber from biblioitems where
205 ((biblioitems.notes like '$key[0]%' or biblioitems.notes like '% $key[0]%')";
206 for ($i=1;$i<$count;$i++){
207 $query.=" and (biblioitems.notes like '$key[$i]%' or biblioitems.notes like '% $key[$i]%')";
211 $sth=$dbh->prepare($query);
214 while (my @res=$sth->fetchrow_array){
215 $results[$i]=$res[0];
219 my $set3=Set::Scalar->new(@results);
223 $sth=$dbh->prepare("Select biblionumber from bibliosubject where subject
224 like '%$search->{'keyword'}%' group by biblionumber");
227 while (my @res=$sth->fetchrow_array){
228 $results[$i]=$res[0];
232 my $set4=Set::Scalar->new(@results);
241 my @res = $set1->members;
245 # print "count $count";
246 if ($search->{'class'} ne ''){
248 my $query="select * from biblio,biblioitems where
249 biblio.biblionumber='$res[$i2]' and
250 biblio.biblionumber=biblioitems.biblionumber ";
251 if ($search->{'class'} ne ''){
252 my @temp=split(/\|/,$search->{'class'});
254 $query.= "and ( itemtype='$temp[0]'";
255 for (my $i=1;$i<$count;$i++){
256 $query.=" or itemtype='$temp[$i]'";
260 my $sth=$dbh->prepare($query);
263 if (my $data2=$sth->fetchrow_hashref){
264 my $dewey= $data2->{'dewey'};
265 my $subclass=$data2->{'subclass'};
267 ($dewey == 0) && ($dewey='');
268 ($dewey) && ($dewey.=" $subclass") ;
270 my $end=$offset +$num;
275 if ($i4 <=$end && $i4 > $offset){
276 $data2->{'dewey'}=$dewey;
279 # $res2[$i3]="$data2->{'author'}\t$data2->{'title'}\t$data2->{'biblionumber'}\t$data2->{'copyrightdate'}\t$dewey";
282 # print "in here $i3<br>";
293 while ($i2 < $num && $i2 < $count){
294 my $query="select * from biblio,biblioitems where
295 biblio.biblionumber='$res[$i2+$offset]' and
296 biblio.biblionumber=biblioitems.biblionumber ";
297 if ($search->{'class'} ne ''){
298 my @temp=split(/\|/,$search->{'class'});
300 $query.= "and ( itemtype='$temp[0]'";
301 for (my $i=1;$i<$count;$i++){
302 $query.=" or itemtype='$temp[$i]'";
306 if ($search->{'dewey'} ne ''){
307 $query.= "and (dewey like '$search->{'dewey'}%') ";
310 my $sth=$dbh->prepare($query);
313 if (my $data2=$sth->fetchrow_hashref){
314 my $dewey= $data2->{'dewey'};
315 my $subclass=$data2->{'subclass'};
317 ($dewey == 0) && ($dewey='');
318 ($dewey) && ($dewey.=" $subclass") ;
320 $data2->{'dewey'}=$dewey;
322 # $res2[$i]="$data2->{'author'}\t$data2->{'title'}\t$data2->{'biblionumber'}\t$data2->{'copyrightdate'}\t$dewey";
332 return($count,@res2);
336 my ($env,$type,$search,$num,$offset)=@_;
337 my $dbh = &C4Connect;
338 $search->{'keyword'}=~ s/ +$//;
339 $search->{'keyword'}=~ s/'/\\'/;
340 my @key=split(' ',$search->{'keyword'});
344 my $query ="Select * from biblio,bibliosubtitle,biblioitems where
345 biblio.biblionumber=biblioitems.biblionumber and
346 biblio.biblionumber=bibliosubtitle.biblionumber and
347 (((title like '$key[0]%' or title like '% $key[0]%')";
349 $query=$query." and (title like '$key[$i]%' or title like '% $key[$i]%')";
352 $query.= ") or ((subtitle like '$key[0]%' or subtitle like '% $key[0]%')";
353 for ($i=1;$i<$count;$i++){
354 $query.= " and (subtitle like '$key[$i]%' or subtitle like '% $key[$i]%')";
356 $query.= ") or ((seriestitle like '$key[0]%' or seriestitle like '% $key[0]%')";
357 for ($i=1;$i<$count;$i++){
358 $query.=" and (seriestitle like '$key[$i]%' or seriestitle like '% $key[$i]%')";
360 $query.= ") or ((biblio.notes like '$key[0]%' or biblio.notes like '% $key[0]%')";
361 for ($i=1;$i<$count;$i++){
362 $query.=" and (biblio.notes like '$key[$i]%' or biblio.notes like '% $key[$i]%')";
364 $query.= ") or ((biblioitems.notes like '$key[0]%' or biblioitems.notes like '% $key[0]%')";
365 for ($i=1;$i<$count;$i++){
366 $query.=" and (biblioitems.notes like '$key[$i]%' or biblioitems.notes like '% $key[$i]%')";
368 if ($search->{'keyword'} =~ /new zealand/i){
369 $query.= "or (title like 'nz%' or title like '% nz %' or title like '% nz' or subtitle like 'nz%'
370 or subtitle like '% nz %' or subtitle like '% nz' or author like 'nz %'
371 or author like '% nz %' or author like '% nz')"
373 if ($search->{'keyword'} eq 'nz' || $search->{'keyword'} eq 'NZ' ||
374 $search->{'keyword'} =~ /nz /i || $search->{'keyword'} =~ / nz /i ||
375 $search->{'keyword'} =~ / nz/i){
376 $query.= "or (title like 'new zealand%' or title like '% new zealand %'
377 or title like '% new zealand' or subtitle like 'new zealand%' or
378 subtitle like '% new zealand %'
379 or subtitle like '% new zealand' or author like 'new zealand%'
380 or author like '% new zealand %' or author like '% new zealand' or
381 seriestitle like 'new zealand%' or seriestitle like '% new zealand %'
382 or seriestitle like '% new zealand')"
385 if ($search->{'class'} ne ''){
386 my @temp=split(/\|/,$search->{'class'});
388 $query.= "and ( itemtype='$temp[0]'";
389 for (my $i=1;$i<$count;$i++){
390 $query.=" or itemtype='$temp[$i]'";
394 if ($search->{'dewey'} ne ''){
395 $query.= "and (dewey like '$search->{'dewey'}%') ";
397 $query.="group by biblio.biblionumber";
398 #$query.=" order by author,title";
400 my $sth=$dbh->prepare($query);
403 while (my $data=$sth->fetchrow_hashref){
404 # my $sti=$dbh->prepare("select dewey,subclass from biblioitems where biblionumber=$data->{'biblionumber'}
407 # my ($dewey, $subclass) = $sti->fetchrow;
408 my $dewey=$data->{'dewey'};
409 my $subclass=$data->{'subclass'};
411 ($dewey == 0) && ($dewey='');
412 ($dewey) && ($dewey.=" $subclass");
414 $results[$i]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey";
415 # print $results[$i];
419 $sth=$dbh->prepare("Select biblionumber from bibliosubject where subject
420 like '%$search->{'keyword'}%' group by biblionumber");
422 while (my $data=$sth->fetchrow_hashref){
423 $query="Select * from biblio,biblioitems where
424 biblio.biblionumber=$data->{'biblionumber'} and
425 biblio.biblionumber=biblioitems.biblionumber ";
426 if ($search->{'class'} ne ''){
427 my @temp=split(/\|/,$search->{'class'});
429 $query.= " and ( itemtype='$temp[0]'";
430 for (my $i=1;$i<$count;$i++){
431 $query.=" or itemtype='$temp[$i]'";
436 if ($search->{'dewey'} ne ''){
437 $query.= "and (dewey like '$search->{'dewey'}%') ";
439 my $sth2=$dbh->prepare($query);
442 while (my $data2=$sth2->fetchrow_hashref){
443 my $dewey= $data2->{'dewey'};
444 my $subclass=$data2->{'subclass'};
446 ($dewey == 0) && ($dewey='');
447 ($dewey) && ($dewey.=" $subclass") ;
449 $results[$i]="$data2->{'author'}\t$data2->{'title'}\t$data2->{'biblionumber'}\t$data2->{'copyrightdate'}\t$dewey";
450 # print $results[$i];
456 @results=sort @results;
463 while ($i2 < $count){
464 if ($results[$i2] ne $res[$i-1]){
465 $res[$i]=$results[$i2];
473 while ($i2 < $num && $i2 < $count){
474 $res2[$i2]=$res[$i2+$offset];
486 my ($env,$type,$search,$num,$offset)=@_;
487 my $dbh = &C4Connect;
490 $search->{'title'}=~ s/'/\\'/g;
491 $search->{'author'}=~ s/'/\\'/g;
492 $search->{'illustrator'}=~ s/'/\\'/g;
493 my $title = lc($search->{'title'});
495 if ($type eq 'loose') {
496 if ($search->{'author'} ne ''){
497 my @key=split(' ',$search->{'author'});
500 $query="select *,biblio.author,biblio.biblionumber from
502 left join additionalauthors
503 on additionalauthors.biblionumber =biblio.biblionumber
505 ((biblio.author like '$key[0]%' or biblio.author like '% $key[0]%' or
506 additionalauthors.author like '$key[0]%' or additionalauthors.author
510 $query=$query." and (
511 biblio.author like '$key[$i]%' or biblio.author like '% $key[$i]%' or
512 additionalauthors.author like '$key[$i]%' or additionalauthors.author like '% $key[$i]%'
517 if ($search->{'title'} ne ''){
518 my @key=split(' ',$search->{'title'});
521 $query.= " and (((title like '$key[0]%' or title like '% $key[0]%' or title like '% $key[0]')";
523 $query=$query." and (title like '$key[$i]%' or title like '% $key[$i]%' or title like '% $key[$i]')";
526 # $query.=") or ((subtitle like '$key[0]%' or subtitle like '% $key[0] %' or subtitle like '% $key[0]')";
527 # for ($i=1;$i<$count;$i++){
528 # $query.=" and (subtitle like '$key[$i]%' or subtitle like '% $key[$i] %' or subtitle like '% $key[$i]')";
530 $query.=") or ((seriestitle like '$key[0]%' or seriestitle like '% $key[0]%' or seriestitle like '% $key[0]')";
531 for ($i=1;$i<$count;$i++){
532 $query.=" and (seriestitle like '$key[$i]%' or seriestitle like '% $key[$i]%')";
534 $query.=") or ((unititle like '$key[0]%' or unititle like '% $key[0]%' or unititle like '% $key[0]')";
535 for ($i=1;$i<$count;$i++){
536 $query.=" and (unititle like '$key[$i]%' or unititle like '% $key[$i]%')";
539 #$query=$query. " and (title like '%$search->{'title'}%'
540 #or seriestitle like '%$search->{'title'}%')";
542 if ($search->{'abstract'} ne ''){
543 $query.= " and (abstract like '%$search->{'abstract'}%')";
547 $query.=" group by biblio.biblionumber";
549 if ($search->{'title'} ne '') {
550 if ($search->{'ttype'} eq 'exact'){
551 $query="select * from biblio
553 (biblio.title='$search->{'title'}' or (biblio.unititle = '$search->{'title'}'
554 or biblio.unititle like '$search->{'title'} |%' or
555 biblio.unititle like '%| $search->{'title'} |%' or
556 biblio.unititle like '%| $search->{'title'}') or
557 (biblio.seriestitle = '$search->{'title'}' or
558 biblio.seriestitle like '$search->{'title'} |%' or
559 biblio.seriestitle like '%| $search->{'title'} |%' or
560 biblio.seriestitle like '%| $search->{'title'}')
563 my @key=split(' ',$search->{'title'});
566 $query="select * from biblio
567 left join bibliosubtitle on
568 biblio.biblionumber=bibliosubtitle.biblionumber
570 (((title like '$key[0]%' or title like '% $key[0]%' or title like '% $key[0]')";
572 $query=$query." and (title like '$key[$i]%' or title like '% $key[$i]%' or title like '% $key[$i]')";
575 $query.=") or ((subtitle like '$key[0]%' or subtitle like '% $key[0]%' or subtitle like '% $key[0]')";
576 for ($i=1;$i<$count;$i++){
577 $query.=" and (subtitle like '$key[$i]%' or subtitle like '% $key[$i]%' or subtitle like '% $key[$i]')";
579 $query.=") or ((seriestitle like '$key[0]%' or seriestitle like '% $key[0]%' or seriestitle like '% $key[0]')";
580 for ($i=1;$i<$count;$i++){
581 $query.=" and (seriestitle like '$key[$i]%' or seriestitle like '% $key[$i]%')";
583 $query.=") or ((unititle like '$key[0]%' or unititle like '% $key[0]%' or unititle like '% $key[0]')";
584 for ($i=1;$i<$count;$i++){
585 $query.=" and (unititle like '$key[$i]%' or unititle like '% $key[$i]%')";
589 if ($search->{'abstract'} ne ''){
590 $query.= " and (abstract like '%$search->{'abstract'}%')";
592 } elsif ($search->{'class'} ne ''){
593 $query="select * from biblioitems,biblio where biblio.biblionumber=biblioitems.biblionumber";
594 my @temp=split(/\|/,$search->{'class'});
596 $query.= " and ( itemtype='$temp[0]'";
597 for (my $i=1;$i<$count;$i++){
598 $query.=" or itemtype='$temp[$i]'";
601 if ($search->{'illustrator'} ne ''){
602 $query.=" and illus like '%".$search->{'illustrator'}."%' ";
604 if ($search->{'dewey'} ne ''){
605 $query.=" and biblioitems.dewey like '$search->{'dewey'}%'";
607 } elsif ($search->{'dewey'} ne ''){
608 $query="select * from biblioitems,biblio
609 where biblio.biblionumber=biblioitems.biblionumber
610 and biblioitems.dewey like '$search->{'dewey'}%'";
611 } elsif ($search->{'illustrator'} ne '') {
612 $query="select * from biblioitems,biblio
613 where biblio.biblionumber=biblioitems.biblionumber
614 and biblioitems.illus like '%".$search->{'illustrator'}."%'";
615 } elsif ($search->{'publisher'} ne ''){
616 $query.= "Select * from biblio,biblioitems where biblio.biblionumber
617 =biblioitems.biblionumber and (publishercode like '%$search->{'publisher'}%')";
618 } elsif ($search->{'abstract'} ne ''){
619 $query.= "Select * from biblio where abstract like '%$search->{'abstract'}%'";
621 $query .=" group by biblio.biblionumber";
624 if ($type eq 'subject'){
625 my @key=split(' ',$search->{'subject'});
628 $query="select distinct(subject) from bibliosubject where( subject like
629 '$key[0]%' or subject like '% $key[0]%' or subject like '% $key[0]' or subject like '%($key[0])%')";
631 $query.=" and (subject like '$key[$i]%' or subject like '% $key[$i]%'
632 or subject like '% $key[$i]'
633 or subject like '%($key[$i])%')";
636 if ($search->{'subject'} eq 'NZ' || $search->{'subject'} eq 'nz'){
637 $query.= " or (subject like 'NEW ZEALAND %' or subject like '% NEW ZEALAND %'
638 or subject like '% NEW ZEALAND' or subject like '%(NEW ZEALAND)%' ) ";
639 } elsif ( $search->{'subject'} =~ /^nz /i || $search->{'subject'} =~ / nz /i || $search->{'subject'} =~ / nz$/i){
640 $query=~ s/ nz/ NEW ZEALAND/ig;
641 $query=~ s/nz /NEW ZEALAND /ig;
642 $query=~ s/\(nz\)/\(NEW ZEALAND\)/gi;
645 if ($type eq 'precise'){
647 if ($search->{'item'} ne ''){
648 $query="select * from items,biblio ";
649 my $search2=uc $search->{'item'};
650 $query=$query." where
651 items.biblionumber=biblio.biblionumber
652 and barcode='$search2'";
654 if ($search->{'isbn'} ne ''){
655 my $search2=uc $search->{'isbn'};
656 my $query1 = "select * from biblioitems where isbn='$search2'";
657 my $sth1=$dbh->prepare($query1);
658 # print STDERR "$query1\n";
661 while (my $data=$sth1->fetchrow_hashref) {
662 $query="select * from biblioitems,biblio where
663 biblio.biblionumber = $data->{'biblionumber'}
664 and biblioitems.biblionumber = biblio.biblionumber";
665 my $sth=$dbh->prepare($query);
667 my $data=$sth->fetchrow_hashref;
668 my ($dewey, $subclass) = ($data->{'dewey'}, $data->{'subclass'});
670 ($dewey == 0) && ($dewey='');
671 ($dewey) && ($dewey.=" $subclass");
672 $data->{'dewey'}=$dewey;
674 # $results[$i2]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey\t$data->{'isbn'}\t$data->{'itemtype'}";
682 if ($type ne 'precise' && $type ne 'subject'){
683 if ($search->{'author'} ne ''){
684 $query=$query." order by biblio.author,title";
686 $query=$query." order by title";
689 if ($type eq 'subject'){
690 $query=$query." order by subject";
693 #print STDERR "$query\n";
694 my $sth=$dbh->prepare($query);
698 my $limit= $num+$offset;
699 while (my $data=$sth->fetchrow_hashref){
700 my $query="select dewey,subclass from biblioitems where biblionumber=$data->{'biblionumber'}";
701 if ($search->{'class'} ne ''){
702 my @temp=split(/\|/,$search->{'class'});
704 $query.= " and ( itemtype='$temp[0]'";
705 for (my $i=1;$i<$count;$i++){
706 $query.=" or itemtype='$temp[$i]'";
710 if ($search->{'dewey'} ne ''){
711 $query.=" and dewey='$search->{'dewey'}' ";
713 if ($search->{'illustrator'} ne ''){
714 $query.=" and illus like '%".$search->{'illustrator'}."%' ";
716 if ($search->{'publisher'} ne ''){
717 $query.= " and (publishercode like '%$search->{'publisher'}%')";
719 #print STDERR "$query\n";
720 my $sti=$dbh->prepare($query);
725 if (($dewey, $subclass) = $sti->fetchrow || $type eq 'subject'){
729 ($dewey == 0) && ($dewey='');
730 ($dewey) && ($dewey.=" $subclass");
731 $data->{'dewey'}=$dewey;
734 if ($count > $offset && $count <= $limit){
742 #if ($type ne 'precise'){
746 return($count,@results);
749 sub updatesearchstats{
755 my ($env,$subject)=@_;
757 $subject=$dbh->quote($subject);
758 my $query="Select * from biblio,bibliosubject where
759 biblio.biblionumber=bibliosubject.biblionumber and
760 bibliosubject.subject=$subject group by biblio.biblionumber
761 order by biblio.title";
762 my $sth=$dbh->prepare($query);
767 while (my $data=$sth->fetchrow_hashref){
768 $results[$i]="$data->{'title'}\t$data->{'author'}\t$data->{'biblionumber'}";
778 my ($env,$biblionumber,$type) = @_;
779 my $dbh = &C4Connect;
780 my $query = "SELECT * FROM items, biblio, biblioitems, itemtypes
781 WHERE items.biblionumber = ?
782 AND biblioitems.biblioitemnumber = items.biblioitemnumber
783 AND biblioitems.itemtype = itemtypes.itemtype
784 AND biblio.biblionumber = items.biblionumber";
785 if ($type ne 'intra'){
786 $query .= " and ((items.itemlost<>1 and items.itemlost <> 2)
787 or items.itemlost is NULL)
788 and (wthdrawn <> 1 or wthdrawn is NULL)";
790 $query .= " order by items.dateaccessioned desc";
792 my $sth=$dbh->prepare($query);
793 $sth->execute($biblionumber);
797 while (my $data=$sth->fetchrow_hashref){
798 my $iquery = "Select * from issues
799 where itemnumber = '$data->{'itemnumber'}'
800 and returndate is null";
802 my $isth=$dbh->prepare($iquery);
804 if (my $idata=$isth->fetchrow_hashref){
805 my @temp=split('-',$idata->{'date_due'});
806 $datedue = "$temp[2]/$temp[1]/$temp[0]";
808 if ($data->{'itemlost'} eq '2'){
809 $datedue='Very Overdue';
811 if ($data->{'itemlost'} eq '1'){
814 if ($data->{'wthdrawn'} eq '1'){
815 $datedue="Cancelled";
818 $datedue="Available";
819 my ($restype,$reserves)=CheckReserves($data->{'itemnumber'});
825 #get branch information.....
826 my $bquery = "SELECT * FROM branches
827 WHERE branchcode = '$data->{'holdingbranch'}'";
828 my $bsth=$dbh->prepare($bquery);
830 if (my $bdata=$bsth->fetchrow_hashref){
831 $data->{'branchname'} = $bdata->{'branchname'};
834 my $class = $data->{'classification'};
835 my $dewey = $data->{'dewey'};
837 if ($dewey eq "000.") { $dewey = "";};
838 if ($dewey < 10){$dewey='00'.$dewey;}
839 if ($dewey < 100 && $dewey > 10){$dewey='0'.$dewey;}
844 $class = $class.$dewey;
846 $class = $class.$data->{'subclass'};
848 # $results[$i]="$data->{'title'}\t$data->{'barcode'}\t$datedue\t$data->{'branchname'}\t$data->{'dewey'}";
849 my @temp=split('-',$data->{'datelastseen'});
850 my $date="$temp[2]/$temp[1]/$temp[0]";
851 $data->{'datelastseen'}=$date;
852 $data->{'datedue'}=$datedue;
853 $data->{'class'}=$class;
858 my $query2="Select * from aqorders where biblionumber=$biblionumber";
859 my $sth2=$dbh->prepare($query2);
863 if ($data=$sth2->fetchrow_hashref){
864 $ocount=$data->{'quantity'} - $data->{'quantityreceived'};
866 $data->{'ocount'}=$ocount;
867 $data->{'order'}="One Order";
878 my ($env,$biblionumber)=@_;
879 #debug_msg($env,"GetItems");
880 my $dbh = &C4Connect;
881 my $query = "Select * from biblioitems where (biblionumber = $biblionumber)";
882 #debug_msg($env,$query);
883 my $sth=$dbh->prepare($query);
885 #debug_msg($env,"executed query");
888 while (my $data=$sth->fetchrow_hashref) {
889 #debug_msg($env,$data->{'biblioitemnumber'});
890 my $dewey = $data->{'dewey'};
892 my $line = $data->{'biblioitemnumber'}."\t".$data->{'itemtype'};
893 $line = $line."\t$data->{'classification'}\t$dewey";
894 $line = $line."\t$data->{'subclass'}\t$data->{isbn}";
895 $line = $line."\t$data->{'volume'}\t$data->{number}";
896 my $isth= $dbh->prepare("select * from items where biblioitemnumber = $data->{'biblioitemnumber'}");
898 while (my $idata = $isth->fetchrow_hashref) {
899 my $iline = $idata->{'barcode'}."[".$idata->{'holdingbranch'}."[";
900 if ($idata->{'notforloan'} == 1) {
901 $iline = $iline."NFL ";
903 if ($idata->{'itemlost'} == 1) {
904 $iline = $iline."LOST ";
906 $line = $line."\t$iline";
909 $results[$i] = $line;
920 my $query="Select * from items,biblioitems where barcode='$barcode'
921 and items.biblioitemnumber=biblioitems.biblioitemnumber";
923 my $sth=$dbh->prepare($query);
925 my $data=$sth->fetchrow_hashref;
933 my ($bibnum, $type) = @_;
935 my $query = "Select *, biblio.notes
936 from biblio, biblioitems
937 left join bibliosubtitle on
938 biblio.biblionumber = bibliosubtitle.biblionumber
939 where biblio.biblionumber = $bibnum
940 and biblioitems.biblionumber = $bibnum";
941 my $sth = $dbh->prepare($query);
945 $data = $sth->fetchrow_hashref;
948 $query = "Select * from bibliosubject where biblionumber = '$bibnum'";
949 $sth = $dbh->prepare($query);
951 while (my $dat = $sth->fetchrow_hashref){
952 $data->{'subject'} .= " | $dat->{'subject'}";
964 my $query = "Select *,biblioitems.notes as bnotes from biblio, biblioitems,itemtypes
965 where biblio.biblionumber = biblioitems.biblionumber
966 and biblioitemnumber = $bibitem
967 and biblioitems.itemtype = itemtypes.itemtype";
968 my $sth = $dbh->prepare($query);
973 $data = $sth->fetchrow_hashref;
984 my $query="Select * from bibliosubject where biblionumber=$bibnum";
985 my $sth=$dbh->prepare($query);
989 while (my $data=$sth->fetchrow_hashref){
995 return($i,\@results);
1001 my $query="Select * from additionalauthors where biblionumber=$bibnum";
1002 my $sth=$dbh->prepare($query);
1006 while (my $data=$sth->fetchrow_hashref){
1012 return($i,\@results);
1018 my $query="Select * from bibliosubtitle where biblionumber=$bibnum";
1019 my $sth=$dbh->prepare($query);
1023 while (my $data=$sth->fetchrow_hashref){
1029 return($i,\@results);
1035 my ($bibitem, $biblio)=@_;
1036 my $dbh = C4Connect;
1037 my $query = "Select * from items where
1038 items.biblioitemnumber = '$bibitem'";
1039 my $sth = $dbh->prepare($query)
1040 || die $dbh->errstr;
1045 || die $sth->errstr;
1047 while (my $data = $sth->fetchrow_hashref) {
1048 my $query2 = "select * from issues,borrowers
1049 where itemnumber = $data->{'itemnumber'}
1050 and returndate is NULL
1051 and issues.borrowernumber = borrowers.borrowernumber";
1052 my $sth2 = $dbh->prepare($query2);
1055 if (my $data2 = $sth2->fetchrow_hashref) {
1056 $data->{'date_due'} = $data2->{'date_due'};
1057 $data->{'card'} = $data2->{'cardnumber'};
1059 if ($data->{'wthdrawn'} eq '1') {
1060 $data->{'date_due'} = 'Cancelled';
1062 $data->{'date_due'} = 'Available';
1067 $query2 = "select * from issues, borrowers
1068 where itemnumber = '$data->{'itemnumber'}'
1069 and issues.borrowernumber = borrowers.borrowernumber
1070 order by date_due desc";
1071 $sth2 = $dbh->prepare($query2)
1072 || die $dbh->errstr;
1074 || die $sth2->errstr;
1076 for (my $i2 = 0; $i2 < 2; $i2++) {
1077 if (my $data2 = $sth2->fetchrow_hashref) {
1078 $data->{"timestamp$i2"} = $data2->{'timestamp'};
1079 $data->{"card$i2"} = $data2->{'cardnumber'};
1080 $data->{"borrower$i2"} = $data2->{'borrowernumber'};
1085 $results[$i] = $data;
1096 my ($env,$dbh,$itemnumber) = @_;
1098 my $query="Select * from biblio,items,biblioitems
1099 where items.itemnumber = '$itemnumber'
1100 and biblio.biblionumber = items.biblionumber
1101 and biblioitems.biblioitemnumber = items.biblioitemnumber";
1102 my $sth=$dbh->prepare($query);
1105 my $data=$sth->fetchrow_hashref;
1111 #used by member enquiries from the intranet
1112 #called by member.pl
1114 my ($env,$searchstring,$type)=@_;
1115 my $dbh = &C4Connect;
1116 $searchstring=~ s/\'/\\\'/g;
1117 my @data=split(' ',$searchstring);
1119 my $query="Select * from borrowers
1120 where ((surname like \"$data[0]%\" or surname like \"% $data[0]%\"
1121 or firstname like \"$data[0]%\" or firstname like \"% $data[0]%\"
1122 or othernames like \"$data[0]%\" or othernames like \"% $data[0]%\")
1124 for (my $i=1;$i<$count;$i++){
1125 $query=$query." and (surname like \"$data[$i]%\" or surname like \"% $data[$i]%\"
1126 or firstname like \"$data[$i]%\" or firstname like \"% $data[$i]%\"
1127 or othernames like \"$data[$i]%\" or othernames like \"% $data[$i]%\")";
1129 $query=$query.") or cardnumber = \"$searchstring\"
1130 order by surname,firstname";
1131 # print $query,"\n";
1132 my $sth=$dbh->prepare($query);
1136 while (my $data=$sth->fetchrow_hashref){
1137 push(@results,$data);
1143 return ($cnt,\@results);
1147 my ($cardnumber,$bornum)=@_;
1148 $cardnumber = uc $cardnumber;
1152 $query="Select * from borrowers where cardnumber='$cardnumber'";
1154 $query="Select * from borrowers where borrowernumber='$bornum'";
1157 my $sth=$dbh->prepare($query);
1159 my $data=$sth->fetchrow_hashref;
1169 $query="Select * from issues,biblio,items where borrowernumber='$bornum' and
1170 items.itemnumber=issues.itemnumber and
1171 items.biblionumber=biblio.biblionumber and issues.returndate is NULL order
1174 my $sth=$dbh->prepare($query);
1178 while (my $data=$sth->fetchrow_hashref){
1184 return($i,\@result);
1188 my ($bornum,$order,$limit)=@_;
1191 $query="Select * from issues,biblio,items,biblioitems
1192 where borrowernumber='$bornum' and
1193 items.biblioitemnumber=biblioitems.biblioitemnumber and
1194 items.itemnumber=issues.itemnumber and
1195 items.biblionumber=biblio.biblionumber";
1196 $query.=" order by $order";
1198 $query.=" limit $limit";
1201 my $sth=$dbh->prepare($query);
1205 while (my $data=$sth->fetchrow_hashref){
1211 return($i,\@result);
1215 my ($env,$bornum)=@_;
1217 my $query="Select count(*) from issues where borrowernumber='$bornum' and
1218 returndate is NULL";
1220 my $sth=$dbh->prepare($query);
1222 my $data=$sth->fetchrow_hashref;
1224 $sth=$dbh->prepare("Select count(*) from issues where
1225 borrowernumber='$bornum' and date_due < now() and returndate is NULL");
1227 my $data2=$sth->fetchrow_hashref;
1229 $sth=$dbh->prepare("Select sum(amountoutstanding) from accountlines where
1230 borrowernumber='$bornum'");
1232 my $data3=$sth->fetchrow_hashref;
1236 return($data2->{'count(*)'},$data->{'count(*)'},$data3->{'sum(amountoutstanding)'});
1240 sub getboracctrecord {
1241 my ($env,$params) = @_;
1245 my $query= "Select * from accountlines where
1246 borrowernumber=$params->{'borrowernumber'} order by date desc,timestamp desc";
1247 my $sth=$dbh->prepare($query);
1251 while (my $data=$sth->fetchrow_hashref){
1252 # if ($data->{'itemnumber'} ne ''){
1253 # $query="Select * from items,biblio where items.itemnumber=
1254 # '$data->{'itemnumber'}' and biblio.biblionumber=items.biblionumber";
1255 # my $sth2=$dbh->prepare($query);
1257 # my $data2=$sth2->fetchrow_hashref;
1261 $acctlines[$numlines] = $data;
1263 $total = $total+ $data->{'amountoutstanding'};
1267 return ($numlines,\@acctlines,$total);
1272 my ($env,$bibnum,$type)=@_;
1274 my $query="Select * from items where
1275 biblionumber=$bibnum ";
1276 if ($type ne 'intra'){
1277 $query.=" and ((itemlost <>1 and itemlost <> 2) or itemlost is NULL) and
1278 (wthdrawn <> 1 or wthdrawn is NULL)";
1280 my $sth=$dbh->prepare($query);
1292 while (my $data=$sth->fetchrow_hashref){
1294 my $query2="select * from issues,items where issues.itemnumber=
1295 '$data->{'itemnumber'}' and returndate is NULL
1296 and items.itemnumber=issues.itemnumber and ((items.itemlost <>1 and
1297 items.itemlost <> 2) or items.itemlost is NULL)
1298 and (wthdrawn <> 1 or wthdrawn is NULL)";
1300 my $sth2=$dbh->prepare($query2);
1302 if (my $data2=$sth2->fetchrow_hashref){
1305 if ($data->{'holdingbranch'} eq 'C'){
1308 if ($data->{'holdingbranch'} eq 'F' || $data->{'holdingbranch'} eq 'FP'){
1311 if ($data->{'holdingbranch'} eq 'S' || $data->{'holdingbranch'} eq 'SP'){
1314 if ($data->{'itemlost'} eq '1'){
1317 if ($data->{'itemlost'} eq '2'){
1320 if ($data->{'holdingbranch'} eq 'FM'){
1323 if ($data->{'holdingbranch'} eq 'TR'){
1330 my $query2="Select * from aqorders where biblionumber=$bibnum";
1331 my $sth2=$dbh->prepare($query2);
1333 if (my $data=$sth2->fetchrow_hashref){
1334 $ocount=$data->{'quantity'} - $data->{'quantityreceived'};
1340 return ($count,$lcount,$nacount,$fcount,$scount,$lostcount,$mending,$transit,$ocount);
1345 my ($env,$bibnum,$type)=@_;
1347 my $query="Select * from items,branches where
1348 biblionumber=$bibnum and items.holdingbranch=branches.branchcode";
1349 if ($type ne 'intra'){
1350 $query.=" and ((itemlost <>1 and itemlost <> 2) or itemlost is NULL) and
1351 (wthdrawn <> 1 or wthdrawn is NULL)";
1353 my $sth=$dbh->prepare($query);
1358 while (my $data=$sth->fetchrow_hashref){
1360 my $query2="select * from issues,items where issues.itemnumber=
1361 '$data->{'itemnumber'}' and returndate is NULL
1362 and items.itemnumber=issues.itemnumber and ((items.itemlost <>1 and
1363 items.itemlost <> 2) or items.itemlost is NULL)
1364 and (wthdrawn <> 1 or wthdrawn is NULL)";
1366 my $sth2=$dbh->prepare($query2);
1368 if (my $data2=$sth2->fetchrow_hashref){
1369 $counts{'notavailable'}++;
1371 $counts{$data->{'branchname'}}++;
1375 my $query2="Select * from aqorders where biblionumber=$bibnum and
1376 datecancellationprinted is NULL and quantity > quantityreceived";
1377 my $sth2=$dbh->prepare($query2);
1379 if (my $data=$sth2->fetchrow_hashref){
1380 $counts{'order'}=$data->{'quantity'} - $data->{'quantityreceived'};
1392 my $query="select description from itemtypes where itemtype='$type'";
1393 my $sth=$dbh->prepare($query);
1395 my $dat=$sth->fetchrow_hashref;
1398 return ($dat->{'description'});
1404 my $dbh = C4Connect;
1405 my $query = "Select * from biblioitems, itemtypes, items
1406 where biblioitems.biblionumber = '$bibnum'
1407 and biblioitems.itemtype = itemtypes.itemtype
1408 and biblioitems.biblioitemnumber = items.biblioitemnumber
1409 group by items.biblioitemnumber";
1410 my $sth = $dbh->prepare($query);
1416 while (my $data = $sth->fetchrow_hashref) {
1417 $results[$count] = $data;
1423 return($count, @results);
1428 #called from request.pl
1429 my ($biblioitemnumber)=@_;
1431 my $query="Select barcode from items where
1432 biblioitemnumber='$biblioitemnumber'
1433 and ((itemlost <> 1 and itemlost <> 2) or itemlost is NULL) and
1434 (wthdrawn <> 1 or wthdrawn is NULL)";
1436 my $sth=$dbh->prepare($query);
1440 while (my $data=$sth->fetchrow_hashref){
1441 $barcodes[$i]=$data->{'barcode'};
1452 my ($biblionumber) = @_;
1453 my $dbh = C4Connect;
1454 my $query = "Select * from websites where biblionumber = $biblionumber";
1455 my $sth = $dbh->prepare($query);
1460 while (my $data = $sth->fetchrow_hashref) {
1461 $data->{'url'} =~ s/^http:\/\///;
1462 $results[$count] = $data;
1468 return($count, @results);
1472 sub getwebbiblioitems {
1473 my ($biblionumber) = @_;
1474 my $dbh = C4Connect;
1475 my $query = "Select * from biblioitems where biblionumber = $biblionumber
1476 and itemtype = 'WEB'";
1477 my $sth = $dbh->prepare($query);
1482 while (my $data = $sth->fetchrow_hashref) {
1483 $data->{'url'} =~ s/^http:\/\///;
1484 $results[$count] = $data;
1490 return($count, @results);
1491 } # sub getwebbiblioitems
1494 END { } # module clean-up code here (global destructor)
1498 C4::Search - Module that provides Catalog searching for Koha
1503 my ($count,@results)=catalogsearch($env,$type,$search,$num,$offset);
1507 This module provides the searching facilities for the Catalog.
1508 Here I should go through and document each function thats exported and what it does. But I havent yet.
1510 my ($count,@results)=catalogsearch($env,$type,$search,$num,$offset);
1511 This is a front end to all the other searches, depending on what is passed
1512 to it, it calls the appropriate search
1520 Koha Developement team <info@koha.org>