1 package C4::Search; #asummes C4/Search
3 #requires DBI.pm to be installed
13 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
15 # set the version for version checking
19 @EXPORT = qw(&CatSearch &BornameSearch &ItemInfo &KeywordSearch &subsearch
20 &itemdata &bibdata &GetItems &borrdata &itemnodata &itemcount
21 &borrdata2 &NewBorrowerNumber &bibitemdata &borrissues
22 &getboracctrecord &ItemType &itemissues &subject &subtitle
23 &addauthor &bibitems &barcodes &findguarantees &allissues &systemprefs
24 &findguarantor &getwebsites &getwebbiblioitems &catalogsearch itemcount2);
25 %EXPORT_TAGS = ( ); # eg: TAG => [ qw!name1 name2! ],
27 # your exported package globals go here,
28 # as well as any optionally exported functions
30 @EXPORT_OK = qw($Var1 %Hashit);
33 # non-exported package globals go here
34 use vars qw(@more $stuff);
36 # initalize package globals, first exported ones
41 # then the others (which are still accessible as $Some::Module::stuff)
45 # all file-scoped lexicals must be created before
46 # the functions below that use them.
48 # file-private lexicals go here
52 # here's a file-private function as a closure,
53 # callable as &$priv_func; it cannot be prototyped.
58 # make all your functions, whether exported or not;
62 my $query="select cardnumber,borrowernumber from borrowers where
64 my $sth=$dbh->prepare($query);
68 while (my $data=$sth->fetchrow_hashref){
79 my $query="select guarantor from borrowers where
80 borrowernumber='$bornum'";
81 my $sth=$dbh->prepare($query);
83 my $data=$sth->fetchrow_hashref;
85 $query="Select * from borrowers where
86 borrowernumber='$data->{'guarantor'}'";
87 $sth=$dbh->prepare($query);
89 $data=$sth->fetchrow_hashref;
98 my $sth=$dbh->prepare("select variable,value from systempreferences");
100 while (my ($variable,$value)=$sth->fetchrow) {
101 $systemprefs{$variable}=$value;
105 return(%systemprefs);
108 sub NewBorrowerNumber {
110 my $sth=$dbh->prepare("Select max(borrowernumber) from borrowers");
112 my $data=$sth->fetchrow_hashref;
114 $data->{'max(borrowernumber)'}++;
116 return($data->{'max(borrowernumber)'});
120 my ($env,$type,$search,$num,$offset)=@_;
121 my $dbh = C4Connect();
122 # foreach my $key (%$search){
123 # $search->{$key}=$dbh->quote($search->{$key});
125 my ($count,@results);
126 print STDERR "Doing a search \n";
127 if ($search->{'itemnumber'} ne '' || $search->{'isbn'} ne ''){
128 print STDERR "Doing a precise search\n";
129 ($count,@results)=CatSearch($env,'precise',$search,$num,$offset);
132 if ($search->{'subject'} ne ''){
133 ($count,@results)=CatSearch($env,'subject',$search,$num,$offset);
135 if ($search->{'keyword'} ne ''){
136 ($count,@results)=&KeywordSearch($env,'keyword',$search,$num,$offset);
138 ($count,@results)=CatSearch($env,'loose',$search,$num,$offset);
143 if ($env->{itemcount}) {
144 foreach my $data (@results){
145 my ($counts) = itemcount2($env, $data->{'biblionumber'}, 'intra');
146 my $subject2=$data->{'subject'};
147 $subject2=~ s/ /%20/g;
148 $data->{'itemcount'}=$counts->{'total'};
149 foreach my $key (keys %$counts){
150 if ($key ne 'total'){
151 $data->{'location'}.="$key $counts->{$key} ";
154 $data->{'subject2'}=$subject2;
157 return ($count,@results);
162 my ($env,$type,$search,$num,$offset)=@_;
163 my $dbh = &C4Connect;
164 $search->{'keyword'}=~ s/ +$//;
165 $search->{'keyword'}=~ s/'/\\'/;
166 my @key=split(' ',$search->{'keyword'});
170 my $query="Select biblionumber from biblio
171 where ((title like '$key[0]%' or title like '% $key[0]%')";
173 $query=$query." and (title like '$key[$i]%' or title like '% $key[$i]%')";
176 $query.= ") or ((biblio.notes like '$key[0]%' or biblio.notes like '% $key[0]%')";
177 for ($i=1;$i<$count;$i++){
178 $query.=" and (biblio.notes like '$key[$i]%' or biblio.notes like '% $key[$i]%')";
180 $query.= ") or ((seriestitle like '$key[0]%' or seriestitle like '% $key[0]%')";
181 for ($i=1;$i<$count;$i++){
182 $query.=" and (seriestitle like '$key[$i]%' or seriestitle like '% $key[$i]%')";
186 my $sth=$dbh->prepare($query);
189 while (my @res=$sth->fetchrow_array){
190 $results[$i]=$res[0];
194 my $set1=Set::Scalar->new(@results);
195 $query="Select biblionumber from bibliosubtitle where
196 ((subtitle like '$key[0]%' or subtitle like '% $key[0]%')";
197 for ($i=1;$i<$count;$i++){
198 $query.= " and (subtitle like '$key[$i]%' or subtitle like '% $key[$i]%')";
202 $sth=$dbh->prepare($query);
205 while (my @res=$sth->fetchrow_array){
206 $results[$i]=$res[0];
210 my $set2=Set::Scalar->new(@results);
214 $query ="Select biblionumber from biblioitems where
215 ((biblioitems.notes like '$key[0]%' or biblioitems.notes like '% $key[0]%')";
216 for ($i=1;$i<$count;$i++){
217 $query.=" and (biblioitems.notes like '$key[$i]%' or biblioitems.notes like '% $key[$i]%')";
221 $sth=$dbh->prepare($query);
224 while (my @res=$sth->fetchrow_array){
225 $results[$i]=$res[0];
229 my $set3=Set::Scalar->new(@results);
233 $sth=$dbh->prepare("Select biblionumber from bibliosubject where subject
234 like '%$search->{'keyword'}%' group by biblionumber");
237 while (my @res=$sth->fetchrow_array){
238 $results[$i]=$res[0];
242 my $set4=Set::Scalar->new(@results);
251 my @res = $set1->members;
255 # print "count $count";
256 if ($search->{'class'} ne ''){
258 my $query="select * from biblio,biblioitems where
259 biblio.biblionumber='$res[$i2]' and
260 biblio.biblionumber=biblioitems.biblionumber ";
261 if ($search->{'class'} ne ''){
262 my @temp=split(/\|/,$search->{'class'});
264 $query.= "and ( itemtype='$temp[0]'";
265 for (my $i=1;$i<$count;$i++){
266 $query.=" or itemtype='$temp[$i]'";
270 my $sth=$dbh->prepare($query);
273 if (my $data2=$sth->fetchrow_hashref){
274 my $dewey= $data2->{'dewey'};
275 my $subclass=$data2->{'subclass'};
277 ($dewey == 0) && ($dewey='');
278 ($dewey) && ($dewey.=" $subclass") ;
280 my $end=$offset +$num;
285 if ($i4 <=$end && $i4 > $offset){
286 $data2->{'dewey'}=$dewey;
289 # $res2[$i3]="$data2->{'author'}\t$data2->{'title'}\t$data2->{'biblionumber'}\t$data2->{'copyrightdate'}\t$dewey";
292 # print "in here $i3<br>";
303 while ($i2 < $num && $i2 < $count){
304 my $query="select * from biblio,biblioitems where
305 biblio.biblionumber='$res[$i2+$offset]' and
306 biblio.biblionumber=biblioitems.biblionumber ";
307 if ($search->{'class'} ne ''){
308 my @temp=split(/\|/,$search->{'class'});
310 $query.= "and ( itemtype='$temp[0]'";
311 for (my $i=1;$i<$count;$i++){
312 $query.=" or itemtype='$temp[$i]'";
316 if ($search->{'dewey'} ne ''){
317 $query.= "and (dewey like '$search->{'dewey'}%') ";
320 my $sth=$dbh->prepare($query);
323 if (my $data2=$sth->fetchrow_hashref){
324 my $dewey= $data2->{'dewey'};
325 my $subclass=$data2->{'subclass'};
327 ($dewey == 0) && ($dewey='');
328 ($dewey) && ($dewey.=" $subclass") ;
330 $data2->{'dewey'}=$dewey;
332 # $res2[$i]="$data2->{'author'}\t$data2->{'title'}\t$data2->{'biblionumber'}\t$data2->{'copyrightdate'}\t$dewey";
342 return($count,@res2);
346 my ($env,$type,$search,$num,$offset)=@_;
347 my $dbh = &C4Connect;
350 $search->{'title'}=~ s/'/\\'/g;
351 $search->{'author'}=~ s/'/\\'/g;
352 $search->{'illustrator'}=~ s/'/\\'/g;
353 my $title = lc($search->{'title'});
355 if ($type eq 'loose') {
356 if ($search->{'author'} ne ''){
357 my @key=split(' ',$search->{'author'});
360 $query="select *,biblio.author,biblio.biblionumber from
362 left join additionalauthors
363 on additionalauthors.biblionumber =biblio.biblionumber
365 ((biblio.author like '$key[0]%' or biblio.author like '% $key[0]%' or
366 additionalauthors.author like '$key[0]%' or additionalauthors.author
370 $query=$query." and (
371 biblio.author like '$key[$i]%' or biblio.author like '% $key[$i]%' or
372 additionalauthors.author like '$key[$i]%' or additionalauthors.author like '% $key[$i]%'
377 if ($search->{'title'} ne ''){
378 my @key=split(' ',$search->{'title'});
381 $query.= " and (((title like '$key[0]%' or title like '% $key[0]%' or title like '% $key[0]')";
383 $query=$query." and (title like '$key[$i]%' or title like '% $key[$i]%' or title like '% $key[$i]')";
386 # $query.=") or ((subtitle like '$key[0]%' or subtitle like '% $key[0] %' or subtitle like '% $key[0]')";
387 # for ($i=1;$i<$count;$i++){
388 # $query.=" and (subtitle like '$key[$i]%' or subtitle like '% $key[$i] %' or subtitle like '% $key[$i]')";
390 $query.=") or ((seriestitle like '$key[0]%' or seriestitle like '% $key[0]%' or seriestitle like '% $key[0]')";
391 for ($i=1;$i<$count;$i++){
392 $query.=" and (seriestitle like '$key[$i]%' or seriestitle like '% $key[$i]%')";
394 $query.=") or ((unititle like '$key[0]%' or unititle like '% $key[0]%' or unititle like '% $key[0]')";
395 for ($i=1;$i<$count;$i++){
396 $query.=" and (unititle like '$key[$i]%' or unititle like '% $key[$i]%')";
399 #$query=$query. " and (title like '%$search->{'title'}%'
400 #or seriestitle like '%$search->{'title'}%')";
402 if ($search->{'abstract'} ne ''){
403 $query.= " and (abstract like '%$search->{'abstract'}%')";
407 $query.=" group by biblio.biblionumber";
409 if ($search->{'title'} ne '') {
410 if ($search->{'ttype'} eq 'exact'){
411 $query="select * from biblio
413 (biblio.title='$search->{'title'}' or (biblio.unititle = '$search->{'title'}'
414 or biblio.unititle like '$search->{'title'} |%' or
415 biblio.unititle like '%| $search->{'title'} |%' or
416 biblio.unititle like '%| $search->{'title'}') or
417 (biblio.seriestitle = '$search->{'title'}' or
418 biblio.seriestitle like '$search->{'title'} |%' or
419 biblio.seriestitle like '%| $search->{'title'} |%' or
420 biblio.seriestitle like '%| $search->{'title'}')
423 my @key=split(' ',$search->{'title'});
426 $query="select * from biblio
427 left join bibliosubtitle on
428 biblio.biblionumber=bibliosubtitle.biblionumber
430 (((title like '$key[0]%' or title like '% $key[0]%' or title like '% $key[0]')";
432 $query=$query." and (title like '$key[$i]%' or title like '% $key[$i]%' or title like '% $key[$i]')";
435 $query.=") or ((subtitle like '$key[0]%' or subtitle like '% $key[0]%' or subtitle like '% $key[0]')";
436 for ($i=1;$i<$count;$i++){
437 $query.=" and (subtitle like '$key[$i]%' or subtitle like '% $key[$i]%' or subtitle like '% $key[$i]')";
439 $query.=") or ((seriestitle like '$key[0]%' or seriestitle like '% $key[0]%' or seriestitle like '% $key[0]')";
440 for ($i=1;$i<$count;$i++){
441 $query.=" and (seriestitle like '$key[$i]%' or seriestitle like '% $key[$i]%')";
443 $query.=") or ((unititle like '$key[0]%' or unititle like '% $key[0]%' or unititle like '% $key[0]')";
444 for ($i=1;$i<$count;$i++){
445 $query.=" and (unititle like '$key[$i]%' or unititle like '% $key[$i]%')";
449 if ($search->{'abstract'} ne ''){
450 $query.= " and (abstract like '%$search->{'abstract'}%')";
452 } elsif ($search->{'class'} ne ''){
453 $query="select * from biblioitems,biblio where biblio.biblionumber=biblioitems.biblionumber";
454 my @temp=split(/\|/,$search->{'class'});
456 $query.= " and ( itemtype='$temp[0]'";
457 for (my $i=1;$i<$count;$i++){
458 $query.=" or itemtype='$temp[$i]'";
461 if ($search->{'illustrator'} ne ''){
462 $query.=" and illus like '%".$search->{'illustrator'}."%' ";
464 if ($search->{'dewey'} ne ''){
465 $query.=" and biblioitems.dewey like '$search->{'dewey'}%'";
467 } elsif ($search->{'dewey'} ne ''){
468 $query="select * from biblioitems,biblio
469 where biblio.biblionumber=biblioitems.biblionumber
470 and biblioitems.dewey like '$search->{'dewey'}%'";
471 } elsif ($search->{'illustrator'} ne '') {
472 $query="select * from biblioitems,biblio
473 where biblio.biblionumber=biblioitems.biblionumber
474 and biblioitems.illus like '%".$search->{'illustrator'}."%'";
475 } elsif ($search->{'publisher'} ne ''){
476 $query.= "Select * from biblio,biblioitems where biblio.biblionumber
477 =biblioitems.biblionumber and (publishercode like '%$search->{'publisher'}%')";
478 } elsif ($search->{'abstract'} ne ''){
479 $query.= "Select * from biblio where abstract like '%$search->{'abstract'}%'";
483 $query .=" group by biblio.biblionumber";
486 if ($type eq 'subject'){
487 my @key=split(' ',$search->{'subject'});
490 $query="select distinct(subject) from bibliosubject where( subject like
491 '$key[0]%' or subject like '% $key[0]%' or subject like '% $key[0]' or subject like '%($key[0])%')";
493 $query.=" and (subject like '$key[$i]%' or subject like '% $key[$i]%'
494 or subject like '% $key[$i]'
495 or subject like '%($key[$i])%')";
498 if ($search->{'subject'} eq 'NZ' || $search->{'subject'} eq 'nz'){
499 $query.= " or (subject like 'NEW ZEALAND %' or subject like '% NEW ZEALAND %'
500 or subject like '% NEW ZEALAND' or subject like '%(NEW ZEALAND)%' ) ";
501 } elsif ( $search->{'subject'} =~ /^nz /i || $search->{'subject'} =~ / nz /i || $search->{'subject'} =~ / nz$/i){
502 $query=~ s/ nz/ NEW ZEALAND/ig;
503 $query=~ s/nz /NEW ZEALAND /ig;
504 $query=~ s/\(nz\)/\(NEW ZEALAND\)/gi;
507 if ($type eq 'precise'){
509 if ($search->{'item'} ne ''){
510 $query="select * from items,biblio ";
511 my $search2=uc $search->{'item'};
512 $query=$query." where
513 items.biblionumber=biblio.biblionumber
514 and barcode='$search2'";
516 if ($search->{'isbn'} ne ''){
517 my $search2=uc $search->{'isbn'};
518 my $query1 = "select * from biblioitems where isbn='$search2'";
519 my $sth1=$dbh->prepare($query1);
520 print STDERR "$query1\n";
523 while (my $data=$sth1->fetchrow_hashref) {
524 $query="select * from biblioitems,biblio where
525 biblio.biblionumber = $data->{'biblionumber'}
526 and biblioitems.biblionumber = biblio.biblionumber";
527 my $sth=$dbh->prepare($query);
529 my $data=$sth->fetchrow_hashref;
530 my ($dewey, $subclass) = ($data->{'dewey'}, $data->{'subclass'});
532 ($dewey == 0) && ($dewey='');
533 ($dewey) && ($dewey.=" $subclass");
534 $data->{'dewey'}=$dewey;
536 # $results[$i2]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey\t$data->{'isbn'}\t$data->{'itemtype'}";
544 if ($type ne 'precise' && $type ne 'subject'){
545 if ($search->{'author'} ne ''){
546 $query=$query." order by biblio.author,title";
548 $query=$query." order by title";
551 if ($type eq 'subject'){
552 $query=$query." order by subject";
555 print STDERR "$query\n";
556 my $sth=$dbh->prepare($query);
560 my $limit= $num+$offset;
561 while (my $data=$sth->fetchrow_hashref){
562 my $query="select dewey,subclass from biblioitems where biblionumber=$data->{'biblionumber'}";
563 if ($search->{'class'} ne ''){
564 my @temp=split(/\|/,$search->{'class'});
566 $query.= " and ( itemtype='$temp[0]'";
567 for (my $i=1;$i<$count;$i++){
568 $query.=" or itemtype='$temp[$i]'";
572 if ($search->{'dewey'} ne ''){
573 $query.=" and dewey='$search->{'dewey'}' ";
575 if ($search->{'illustrator'} ne ''){
576 $query.=" and illus like '%".$search->{'illustrator'}."%' ";
578 if ($search->{'publisher'} ne ''){
579 $query.= " and (publishercode like '%$search->{'publisher'}%')";
581 print STDERR "$query\n";
582 my $sti=$dbh->prepare($query);
587 if (($dewey, $subclass) = $sti->fetchrow || $type eq 'subject'){
591 ($dewey == 0) && ($dewey='');
592 ($dewey) && ($dewey.=" $subclass");
593 $data->{'dewey'}=$dewey;
596 if ($count > $offset && $count <= $limit){
604 #if ($type ne 'precise'){
608 return($count,@results);
611 sub updatesearchstats{
617 my ($env,$subject)=@_;
619 $subject=$dbh->quote($subject);
620 my $query="Select * from biblio,bibliosubject where
621 biblio.biblionumber=bibliosubject.biblionumber and
622 bibliosubject.subject=$subject group by biblio.biblionumber
623 order by biblio.title";
624 my $sth=$dbh->prepare($query);
629 while (my $data=$sth->fetchrow_hashref){
630 $results[$i]="$data->{'title'}\t$data->{'author'}\t$data->{'biblionumber'}";
640 my ($env,$biblionumber,$type) = @_;
641 my $dbh = &C4Connect;
642 my $query = "SELECT * FROM items, biblio, biblioitems
643 WHERE items.biblionumber = '$biblionumber'
644 AND biblioitems.biblioitemnumber = items.biblioitemnumber
645 AND biblio.biblionumber = items.biblionumber";
646 if ($type ne 'intra'){
647 $query .= " and ((items.itemlost<>1 and items.itemlost <> 2)
648 or items.itemlost is NULL)
649 and (wthdrawn <> 1 or wthdrawn is NULL)";
651 $query .= " order by items.dateaccessioned desc";
653 my $sth=$dbh->prepare($query);
658 while (my $data=$sth->fetchrow_hashref){
659 my $iquery = "Select * from issues
660 where itemnumber = '$data->{'itemnumber'}'
661 and returndate is null";
663 my $isth=$dbh->prepare($iquery);
665 if (my $idata=$isth->fetchrow_hashref){
666 my @temp=split('-',$idata->{'date_due'});
667 $datedue = "$temp[2]/$temp[1]/$temp[0]";
669 if ($data->{'itemlost'} eq '1' || $data->{'itemlost'} eq '2'){
672 if ($data->{'wthdrawn'} eq '1'){
673 $datedue="Cancelled";
676 $datedue="Available";
677 my ($restype,$reserves)=CheckReserves($data->{'itemnumber'});
683 #get branch information.....
684 my $bquery = "SELECT * FROM branches
685 WHERE branchcode = '$data->{'holdingbranch'}'";
686 my $bsth=$dbh->prepare($bquery);
688 if (my $bdata=$bsth->fetchrow_hashref){
689 $data->{'branchname'} = $bdata->{'branchname'};
692 my $class = $data->{'classification'};
693 my $dewey = $data->{'dewey'};
695 if ($dewey eq "000.") { $dewey = "";};
696 if ($dewey < 10){$dewey='00'.$dewey;}
697 if ($dewey < 100 && $dewey > 10){$dewey='0'.$dewey;}
702 $class = $class.$dewey;
704 $class = $class.$data->{'subclass'};
706 # $results[$i]="$data->{'title'}\t$data->{'barcode'}\t$datedue\t$data->{'branchname'}\t$data->{'dewey'}";
707 my @temp=split('-',$data->{'datelastseen'});
708 my $date="$temp[2]/$temp[1]/$temp[0]";
709 $data->{'datelastseen'}=$date;
710 $data->{'datedue'}=$datedue;
711 $data->{'class'}=$class;
716 my $query2="Select * from aqorders where biblionumber=$biblionumber";
717 my $sth2=$dbh->prepare($query2);
721 if ($data=$sth2->fetchrow_hashref){
722 $ocount=$data->{'quantity'} - $data->{'quantityreceived'};
724 $data->{'ocount'}=$ocount;
725 $data->{'order'}="One Order";
736 my ($env,$biblionumber)=@_;
737 #debug_msg($env,"GetItems");
738 my $dbh = &C4Connect;
739 my $query = "Select * from biblioitems where (biblionumber = $biblionumber)";
740 #debug_msg($env,$query);
741 my $sth=$dbh->prepare($query);
743 #debug_msg($env,"executed query");
746 while (my $data=$sth->fetchrow_hashref) {
747 #debug_msg($env,$data->{'biblioitemnumber'});
748 my $dewey = $data->{'dewey'};
750 my $line = $data->{'biblioitemnumber'}."\t".$data->{'itemtype'};
751 $line = $line."\t$data->{'classification'}\t$dewey";
752 $line = $line."\t$data->{'subclass'}\t$data->{isbn}";
753 $line = $line."\t$data->{'volume'}\t$data->{number}";
754 my $isth= $dbh->prepare("select * from items where biblioitemnumber = $data->{'biblioitemnumber'}");
756 while (my $idata = $isth->fetchrow_hashref) {
757 my $iline = $idata->{'barcode'}."[".$idata->{'holdingbranch'}."[";
758 if ($idata->{'notforloan'} == 1) {
759 $iline = $iline."NFL ";
761 if ($idata->{'itemlost'} == 1) {
762 $iline = $iline."LOST ";
764 $line = $line."\t$iline";
767 $results[$i] = $line;
778 my $query="Select * from items,biblioitems where barcode='$barcode'
779 and items.biblioitemnumber=biblioitems.biblioitemnumber";
781 my $sth=$dbh->prepare($query);
783 my $data=$sth->fetchrow_hashref;
791 my ($bibnum, $type) = @_;
793 my $query = "Select *, biblio.notes
794 from biblio, biblioitems
795 left join bibliosubtitle on
796 biblio.biblionumber = bibliosubtitle.biblionumber
797 where biblio.biblionumber = $bibnum
798 and biblioitems.biblionumber = $bibnum";
799 my $sth = $dbh->prepare($query);
803 $data = $sth->fetchrow_hashref;
806 $query = "Select * from bibliosubject where biblionumber = '$bibnum'";
807 $sth = $dbh->prepare($query);
809 while (my $dat = $sth->fetchrow_hashref){
810 $data->{'subject'} .= " | $dat->{'subject'}";
822 my $query = "Select *,biblioitems.notes as bnotes from biblio, biblioitems,itemtypes
823 where biblio.biblionumber = biblioitems.biblionumber
824 and biblioitemnumber = $bibitem
825 and biblioitems.itemtype = itemtypes.itemtype";
826 my $sth = $dbh->prepare($query);
831 $data = $sth->fetchrow_hashref;
842 my $query="Select * from bibliosubject where biblionumber=$bibnum";
843 my $sth=$dbh->prepare($query);
847 while (my $data=$sth->fetchrow_hashref){
853 return($i,\@results);
859 my $query="Select * from additionalauthors where biblionumber=$bibnum";
860 my $sth=$dbh->prepare($query);
864 while (my $data=$sth->fetchrow_hashref){
870 return($i,\@results);
876 my $query="Select * from bibliosubtitle where biblionumber=$bibnum";
877 my $sth=$dbh->prepare($query);
881 while (my $data=$sth->fetchrow_hashref){
887 return($i,\@results);
893 my ($bibitem, $biblio)=@_;
895 my $query = "Select * from items where
896 items.biblioitemnumber = '$bibitem'";
897 my $sth = $dbh->prepare($query)
905 while (my $data = $sth->fetchrow_hashref) {
906 my $query2 = "select * from issues,borrowers
907 where itemnumber = $data->{'itemnumber'}
908 and returndate is NULL
909 and issues.borrowernumber = borrowers.borrowernumber";
910 my $sth2 = $dbh->prepare($query2);
913 if (my $data2 = $sth2->fetchrow_hashref) {
914 $data->{'date_due'} = $data2->{'date_due'};
915 $data->{'card'} = $data2->{'cardnumber'};
917 if ($data->{'wthdrawn'} eq '1') {
918 $data->{'date_due'} = 'Cancelled';
920 $data->{'date_due'} = 'Available';
925 $query2 = "select * from issues, borrowers
926 where itemnumber = '$data->{'itemnumber'}'
927 and issues.borrowernumber = borrowers.borrowernumber
928 order by date_due desc";
929 $sth2 = $dbh->prepare($query2)
932 || die $sth2->errstr;
934 for (my $i2 = 0; $i2 < 2; $i2++) {
935 if (my $data2 = $sth2->fetchrow_hashref) {
936 $data->{"timestamp$i2"} = $data2->{'timestamp'};
937 $data->{"card$i2"} = $data2->{'cardnumber'};
938 $data->{"borrower$i2"} = $data2->{'borrowernumber'};
943 $results[$i] = $data;
954 my ($env,$dbh,$itemnumber) = @_;
956 my $query="Select * from biblio,items,biblioitems
957 where items.itemnumber = '$itemnumber'
958 and biblio.biblionumber = items.biblionumber
959 and biblioitems.biblioitemnumber = items.biblioitemnumber";
960 my $sth=$dbh->prepare($query);
963 my $data=$sth->fetchrow_hashref;
969 #used by member enquiries from the intranet
972 my ($env,$searchstring,$type)=@_;
973 my $dbh = &C4Connect;
974 $searchstring=~ s/\'/\\\'/g;
975 my @data=split(' ',$searchstring);
977 my $query="Select * from borrowers
978 where ((surname like \"$data[0]%\" or surname like \"% $data[0]%\"
979 or firstname like \"$data[0]%\" or firstname like \"% $data[0]%\"
980 or othernames like \"$data[0]%\" or othernames like \"% $data[0]%\")
982 for (my $i=1;$i<$count;$i++){
983 $query=$query." and (surname like \"$data[$i]%\" or surname like \"% $data[$i]%\"
984 or firstname like \"$data[$i]%\" or firstname like \"% $data[$i]%\"
985 or othernames like \"$data[$i]%\" or othernames like \"% $data[$i]%\")";
987 $query=$query.") or cardnumber = \"$searchstring\"
988 order by surname,firstname";
990 my $sth=$dbh->prepare($query);
994 while (my $data=$sth->fetchrow_hashref){
995 push(@results,$data);
1001 return ($cnt,\@results);
1005 my ($cardnumber,$bornum)=@_;
1006 $cardnumber = uc $cardnumber;
1010 $query="Select * from borrowers where cardnumber='$cardnumber'";
1012 $query="Select * from borrowers where borrowernumber='$bornum'";
1015 my $sth=$dbh->prepare($query);
1017 my $data=$sth->fetchrow_hashref;
1027 $query="Select * from issues,biblio,items where borrowernumber='$bornum' and
1028 items.itemnumber=issues.itemnumber and
1029 items.biblionumber=biblio.biblionumber and issues.returndate is NULL order
1032 my $sth=$dbh->prepare($query);
1036 while (my $data=$sth->fetchrow_hashref){
1042 return($i,\@result);
1046 my ($bornum,$order,$limit)=@_;
1049 $query="Select * from issues,biblio,items,biblioitems
1050 where borrowernumber='$bornum' and
1051 items.biblioitemnumber=biblioitems.biblioitemnumber and
1052 items.itemnumber=issues.itemnumber and
1053 items.biblionumber=biblio.biblionumber";
1054 $query.=" order by $order";
1056 $query.=" limit $limit";
1059 my $sth=$dbh->prepare($query);
1063 while (my $data=$sth->fetchrow_hashref){
1069 return($i,\@result);
1073 my ($env,$bornum)=@_;
1075 my $query="Select count(*) from issues where borrowernumber='$bornum' and
1076 returndate is NULL";
1078 my $sth=$dbh->prepare($query);
1080 my $data=$sth->fetchrow_hashref;
1082 $sth=$dbh->prepare("Select count(*) from issues where
1083 borrowernumber='$bornum' and date_due < now() and returndate is NULL");
1085 my $data2=$sth->fetchrow_hashref;
1087 $sth=$dbh->prepare("Select sum(amountoutstanding) from accountlines where
1088 borrowernumber='$bornum'");
1090 my $data3=$sth->fetchrow_hashref;
1094 return($data2->{'count(*)'},$data->{'count(*)'},$data3->{'sum(amountoutstanding)'});
1098 sub getboracctrecord {
1099 my ($env,$params) = @_;
1103 my $query= "Select * from accountlines where
1104 borrowernumber=$params->{'borrowernumber'} order by date desc,timestamp desc";
1105 my $sth=$dbh->prepare($query);
1109 while (my $data=$sth->fetchrow_hashref){
1110 # if ($data->{'itemnumber'} ne ''){
1111 # $query="Select * from items,biblio where items.itemnumber=
1112 # '$data->{'itemnumber'}' and biblio.biblionumber=items.biblionumber";
1113 # my $sth2=$dbh->prepare($query);
1115 # my $data2=$sth2->fetchrow_hashref;
1119 $acctlines[$numlines] = $data;
1121 $total = $total+ $data->{'amountoutstanding'};
1125 return ($numlines,\@acctlines,$total);
1130 my ($env,$bibnum,$type)=@_;
1132 my $query="Select * from items where
1133 biblionumber=$bibnum ";
1134 if ($type ne 'intra'){
1135 $query.=" and ((itemlost <>1 and itemlost <> 2) or itemlost is NULL) and
1136 (wthdrawn <> 1 or wthdrawn is NULL)";
1138 my $sth=$dbh->prepare($query);
1150 while (my $data=$sth->fetchrow_hashref){
1152 my $query2="select * from issues,items where issues.itemnumber=
1153 '$data->{'itemnumber'}' and returndate is NULL
1154 and items.itemnumber=issues.itemnumber and ((items.itemlost <>1 and
1155 items.itemlost <> 2) or items.itemlost is NULL)
1156 and (wthdrawn <> 1 or wthdrawn is NULL)";
1158 my $sth2=$dbh->prepare($query2);
1160 if (my $data2=$sth2->fetchrow_hashref){
1163 if ($data->{'holdingbranch'} eq 'C'){
1166 if ($data->{'holdingbranch'} eq 'F' || $data->{'holdingbranch'} eq 'FP'){
1169 if ($data->{'holdingbranch'} eq 'S' || $data->{'holdingbranch'} eq 'SP'){
1172 if ($data->{'itemlost'} eq '1'){
1175 if ($data->{'itemlost'} eq '2'){
1178 if ($data->{'holdingbranch'} eq 'FM'){
1181 if ($data->{'holdingbranch'} eq 'TR'){
1188 my $query2="Select * from aqorders where biblionumber=$bibnum";
1189 my $sth2=$dbh->prepare($query2);
1191 if (my $data=$sth2->fetchrow_hashref){
1192 $ocount=$data->{'quantity'} - $data->{'quantityreceived'};
1198 return ($count,$lcount,$nacount,$fcount,$scount,$lostcount,$mending,$transit,$ocount);
1203 my ($env,$bibnum,$type)=@_;
1205 my $query="Select * from items,branches where
1206 biblionumber=$bibnum and items.holdingbranch=branches.branchcode";
1207 if ($type ne 'intra'){
1208 $query.=" and ((itemlost <>1 and itemlost <> 2) or itemlost is NULL) and
1209 (wthdrawn <> 1 or wthdrawn is NULL)";
1211 my $sth=$dbh->prepare($query);
1216 while (my $data=$sth->fetchrow_hashref){
1218 my $query2="select * from issues,items where issues.itemnumber=
1219 '$data->{'itemnumber'}' and returndate is NULL
1220 and items.itemnumber=issues.itemnumber and ((items.itemlost <>1 and
1221 items.itemlost <> 2) or items.itemlost is NULL)
1222 and (wthdrawn <> 1 or wthdrawn is NULL)";
1224 my $sth2=$dbh->prepare($query2);
1226 if (my $data2=$sth2->fetchrow_hashref){
1227 $counts{'notavailable'}++;
1229 $counts{$data->{'branchname'}}++;
1233 my $query2="Select * from aqorders where biblionumber=$bibnum and
1234 datecancellationprinted is NULL and quantity > quantityreceived";
1235 my $sth2=$dbh->prepare($query2);
1237 if (my $data=$sth2->fetchrow_hashref){
1238 $counts{'order'}=$data->{'quantity'} - $data->{'quantityreceived'};
1250 my $query="select description from itemtypes where itemtype='$type'";
1251 my $sth=$dbh->prepare($query);
1253 my $dat=$sth->fetchrow_hashref;
1256 return ($dat->{'description'});
1262 my $dbh = C4Connect;
1263 my $query = "Select * from biblioitems, itemtypes, items
1264 where biblioitems.biblionumber = '$bibnum'
1265 and biblioitems.itemtype = itemtypes.itemtype
1266 and biblioitems.biblioitemnumber = items.biblioitemnumber
1267 group by items.biblioitemnumber";
1268 my $sth = $dbh->prepare($query);
1274 while (my $data = $sth->fetchrow_hashref) {
1275 $results[$count] = $data;
1281 return($count, @results);
1286 #called from request.pl
1287 my ($biblioitemnumber)=@_;
1289 my $query="Select barcode from items where
1290 biblioitemnumber='$biblioitemnumber'
1291 and ((itemlost <> 1 and itemlost <> 2) or itemlost is NULL) and
1292 (wthdrawn <> 1 or wthdrawn is NULL)";
1294 my $sth=$dbh->prepare($query);
1298 while (my $data=$sth->fetchrow_hashref){
1299 $barcodes[$i]=$data->{'barcode'};
1310 my ($biblionumber) = @_;
1311 my $dbh = C4Connect;
1312 my $query = "Select * from websites where biblionumber = $biblionumber";
1313 my $sth = $dbh->prepare($query);
1318 while (my $data = $sth->fetchrow_hashref) {
1319 $data->{'url'} =~ s/^http:\/\///;
1320 $results[$count] = $data;
1326 return($count, @results);
1330 sub getwebbiblioitems {
1331 my ($biblionumber) = @_;
1332 my $dbh = C4Connect;
1333 my $query = "Select * from biblioitems where biblionumber = $biblionumber
1334 and itemtype = 'WEB'";
1335 my $sth = $dbh->prepare($query);
1340 while (my $data = $sth->fetchrow_hashref) {
1341 $data->{'url'} =~ s/^http:\/\///;
1342 $results[$count] = $data;
1348 return($count, @results);
1349 } # sub getwebbiblioitems
1352 END { } # module clean-up code here (global destructor)
1356 C4::Search - Module that provides Catalog searching for Koha
1361 my ($count,@results)=catalogsearch($env,$type,$search,$num,$offset);
1365 This module provides the searching facilities for the Catalog.
1366 Here I should go through and document each function thats exported and what it does. But I havent yet.
1368 my ($count,@results)=catalogsearch($env,$type,$search,$num,$offset);
1369 This is a front end to all the other searches, depending on what is passed
1370 to it, it calls the appropriate search
1378 Koha Developement team <info@koha.org>