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, branches
643 where (items.biblioitemnumber = biblioitems.biblioitemnumber)
644 and biblioitems.biblionumber = biblio.biblionumber
645 and biblio.biblionumber = '$biblionumber'
646 and branches.branchcode = items.holdingbranch";
648 if ($type ne 'intra'){
649 $query .= " and ((items.itemlost<>1 and items.itemlost <> 2)
650 or items.itemlost is NULL)
651 and (wthdrawn <> 1 or wthdrawn is NULL)";
653 $query .= " order by items.dateaccessioned desc";
654 my $sth=$dbh->prepare($query);
659 while (my $data=$sth->fetchrow_hashref){
660 my $iquery = "Select * from issues
661 where itemnumber = '$data->{'itemnumber'}'
662 and returndate is null";
664 my $isth=$dbh->prepare($iquery);
666 if (my $idata=$isth->fetchrow_hashref){
667 my @temp=split('-',$idata->{'date_due'});
668 $datedue = "$temp[2]/$temp[1]/$temp[0]";
670 if ($data->{'itemlost'} eq '1' || $data->{'itemlost'} eq '2'){
673 if ($data->{'wthdrawn'} eq '1'){
674 $datedue="Cancelled";
677 $datedue="Available";
678 my ($rescount,$reserves)=Findgroupreserve($data->{'biblioitemnumber'},$biblionumber);
685 my $class = $data->{'classification'};
686 my $dewey = $data->{'dewey'};
688 if ($dewey eq "000.") { $dewey = "";};
689 if ($dewey < 10){$dewey='00'.$dewey;}
690 if ($dewey < 100 && $dewey > 10){$dewey='0'.$dewey;}
695 $class = $class.$dewey;
697 $class = $class.$data->{'subclass'};
699 # $results[$i]="$data->{'title'}\t$data->{'barcode'}\t$datedue\t$data->{'branchname'}\t$data->{'dewey'}";
700 my @temp=split('-',$data->{'datelastseen'});
701 my $date="$temp[2]/$temp[1]/$temp[0]";
702 $data->{'datelastseen'}=$date;
703 $data->{'datedue'}=$datedue;
704 $data->{'class'}=$class;
709 my $query2="Select * from aqorders where biblionumber=$biblionumber";
710 my $sth2=$dbh->prepare($query2);
714 if ($data=$sth2->fetchrow_hashref){
715 $ocount=$data->{'quantity'} - $data->{'quantityreceived'};
717 $data->{'ocount'}=$ocount;
718 $data->{'order'}="One Order";
729 my ($env,$biblionumber)=@_;
730 #debug_msg($env,"GetItems");
731 my $dbh = &C4Connect;
732 my $query = "Select * from biblioitems where (biblionumber = $biblionumber)";
733 #debug_msg($env,$query);
734 my $sth=$dbh->prepare($query);
736 #debug_msg($env,"executed query");
739 while (my $data=$sth->fetchrow_hashref) {
740 #debug_msg($env,$data->{'biblioitemnumber'});
741 my $dewey = $data->{'dewey'};
743 my $line = $data->{'biblioitemnumber'}."\t".$data->{'itemtype'};
744 $line = $line."\t$data->{'classification'}\t$dewey";
745 $line = $line."\t$data->{'subclass'}\t$data->{isbn}";
746 $line = $line."\t$data->{'volume'}\t$data->{number}";
747 my $isth= $dbh->prepare("select * from items where biblioitemnumber = $data->{'biblioitemnumber'}");
749 while (my $idata = $isth->fetchrow_hashref) {
750 my $iline = $idata->{'barcode'}."[".$idata->{'holdingbranch'}."[";
751 if ($idata->{'notforloan'} == 1) {
752 $iline = $iline."NFL ";
754 if ($idata->{'itemlost'} == 1) {
755 $iline = $iline."LOST ";
757 $line = $line."\t$iline";
760 $results[$i] = $line;
771 my $query="Select * from items,biblioitems where barcode='$barcode'
772 and items.biblioitemnumber=biblioitems.biblioitemnumber";
774 my $sth=$dbh->prepare($query);
776 my $data=$sth->fetchrow_hashref;
784 my ($bibnum, $type) = @_;
786 my $query = "Select *, biblio.notes
787 from biblio, biblioitems
788 left join bibliosubtitle on
789 biblio.biblionumber = bibliosubtitle.biblionumber
790 where biblio.biblionumber = $bibnum
791 and biblioitems.biblionumber = $bibnum";
792 my $sth = $dbh->prepare($query);
796 $data = $sth->fetchrow_hashref;
799 $query = "Select * from bibliosubject where biblionumber = '$bibnum'";
800 $sth = $dbh->prepare($query);
802 while (my $dat = $sth->fetchrow_hashref){
803 $data->{'subject'} .= " | $dat->{'subject'}";
815 my $query = "Select *,biblioitems.notes as bnotes from biblio, biblioitems,itemtypes
816 where biblio.biblionumber = biblioitems.biblionumber
817 and biblioitemnumber = $bibitem
818 and biblioitems.itemtype = itemtypes.itemtype";
819 my $sth = $dbh->prepare($query);
824 $data = $sth->fetchrow_hashref;
835 my $query="Select * from bibliosubject where biblionumber=$bibnum";
836 my $sth=$dbh->prepare($query);
840 while (my $data=$sth->fetchrow_hashref){
846 return($i,\@results);
852 my $query="Select * from additionalauthors where biblionumber=$bibnum";
853 my $sth=$dbh->prepare($query);
857 while (my $data=$sth->fetchrow_hashref){
863 return($i,\@results);
869 my $query="Select * from bibliosubtitle where biblionumber=$bibnum";
870 my $sth=$dbh->prepare($query);
874 while (my $data=$sth->fetchrow_hashref){
880 return($i,\@results);
886 my ($bibitem, $biblio)=@_;
888 my $query = "Select * from items where
889 items.biblioitemnumber = '$bibitem'";
890 my $sth = $dbh->prepare($query)
898 while (my $data = $sth->fetchrow_hashref) {
899 my $query2 = "select * from issues,borrowers
900 where itemnumber = $data->{'itemnumber'}
901 and returndate is NULL
902 and issues.borrowernumber = borrowers.borrowernumber";
903 my $sth2 = $dbh->prepare($query2);
906 if (my $data2 = $sth2->fetchrow_hashref) {
907 $data->{'date_due'} = $data2->{'date_due'};
908 $data->{'card'} = $data2->{'cardnumber'};
910 if ($data->{'wthdrawn'} eq '1') {
911 $data->{'date_due'} = 'Cancelled';
913 $data->{'date_due'} = 'Available';
918 $query2 = "select * from issues, borrowers
919 where itemnumber = '$data->{'itemnumber'}'
920 and issues.borrowernumber = borrowers.borrowernumber
921 order by date_due desc";
922 $sth2 = $dbh->prepare($query2)
925 || die $sth2->errstr;
927 for (my $i2 = 0; $i2 < 2; $i2++) {
928 if (my $data2 = $sth2->fetchrow_hashref) {
929 $data->{"timestamp$i2"} = $data2->{'timestamp'};
930 $data->{"card$i2"} = $data2->{'cardnumber'};
931 $data->{"borrower$i2"} = $data2->{'borrowernumber'};
936 $results[$i] = $data;
947 my ($env,$dbh,$itemnumber) = @_;
949 my $query="Select * from biblio,items,biblioitems
950 where items.itemnumber = '$itemnumber'
951 and biblio.biblionumber = items.biblionumber
952 and biblioitems.biblioitemnumber = items.biblioitemnumber";
953 my $sth=$dbh->prepare($query);
956 my $data=$sth->fetchrow_hashref;
962 #used by member enquiries from the intranet
965 my ($env,$searchstring,$type)=@_;
966 my $dbh = &C4Connect;
967 $searchstring=~ s/\'/\\\'/g;
968 my @data=split(' ',$searchstring);
970 my $query="Select * from borrowers
971 where ((surname like \"$data[0]%\" or surname like \"% $data[0]%\"
972 or firstname like \"$data[0]%\" or firstname like \"% $data[0]%\"
973 or othernames like \"$data[0]%\" or othernames like \"% $data[0]%\")
975 for (my $i=1;$i<$count;$i++){
976 $query=$query." and (surname like \"$data[$i]%\" or surname like \"% $data[$i]%\"
977 or firstname like \"$data[$i]%\" or firstname like \"% $data[$i]%\"
978 or othernames like \"$data[$i]%\" or othernames like \"% $data[$i]%\")";
980 $query=$query.") or cardnumber = \"$searchstring\"
981 order by surname,firstname";
983 my $sth=$dbh->prepare($query);
987 while (my $data=$sth->fetchrow_hashref){
988 push(@results,$data);
994 return ($cnt,\@results);
998 my ($cardnumber,$bornum)=@_;
999 $cardnumber = uc $cardnumber;
1003 $query="Select * from borrowers where cardnumber='$cardnumber'";
1005 $query="Select * from borrowers where borrowernumber='$bornum'";
1008 my $sth=$dbh->prepare($query);
1010 my $data=$sth->fetchrow_hashref;
1020 $query="Select * from issues,biblio,items where borrowernumber='$bornum' and
1021 items.itemnumber=issues.itemnumber and
1022 items.biblionumber=biblio.biblionumber and issues.returndate is NULL order
1025 my $sth=$dbh->prepare($query);
1029 while (my $data=$sth->fetchrow_hashref){
1035 return($i,\@result);
1039 my ($bornum,$order,$limit)=@_;
1042 $query="Select * from issues,biblio,items,biblioitems
1043 where borrowernumber='$bornum' and
1044 items.biblioitemnumber=biblioitems.biblioitemnumber and
1045 items.itemnumber=issues.itemnumber and
1046 items.biblionumber=biblio.biblionumber";
1047 $query.=" order by $order";
1049 $query.=" limit $limit";
1052 my $sth=$dbh->prepare($query);
1056 while (my $data=$sth->fetchrow_hashref){
1062 return($i,\@result);
1066 my ($env,$bornum)=@_;
1068 my $query="Select count(*) from issues where borrowernumber='$bornum' and
1069 returndate is NULL";
1071 my $sth=$dbh->prepare($query);
1073 my $data=$sth->fetchrow_hashref;
1075 $sth=$dbh->prepare("Select count(*) from issues where
1076 borrowernumber='$bornum' and date_due < now() and returndate is NULL");
1078 my $data2=$sth->fetchrow_hashref;
1080 $sth=$dbh->prepare("Select sum(amountoutstanding) from accountlines where
1081 borrowernumber='$bornum'");
1083 my $data3=$sth->fetchrow_hashref;
1087 return($data2->{'count(*)'},$data->{'count(*)'},$data3->{'sum(amountoutstanding)'});
1091 sub getboracctrecord {
1092 my ($env,$params) = @_;
1096 my $query= "Select * from accountlines where
1097 borrowernumber=$params->{'borrowernumber'} order by date desc,timestamp desc";
1098 my $sth=$dbh->prepare($query);
1102 while (my $data=$sth->fetchrow_hashref){
1103 # if ($data->{'itemnumber'} ne ''){
1104 # $query="Select * from items,biblio where items.itemnumber=
1105 # '$data->{'itemnumber'}' and biblio.biblionumber=items.biblionumber";
1106 # my $sth2=$dbh->prepare($query);
1108 # my $data2=$sth2->fetchrow_hashref;
1112 $acctlines[$numlines] = $data;
1114 $total = $total+ $data->{'amountoutstanding'};
1118 return ($numlines,\@acctlines,$total);
1123 my ($env,$bibnum,$type)=@_;
1125 my $query="Select * from items where
1126 biblionumber=$bibnum ";
1127 if ($type ne 'intra'){
1128 $query.=" and ((itemlost <>1 and itemlost <> 2) or itemlost is NULL) and
1129 (wthdrawn <> 1 or wthdrawn is NULL)";
1131 my $sth=$dbh->prepare($query);
1143 while (my $data=$sth->fetchrow_hashref){
1145 my $query2="select * from issues,items where issues.itemnumber=
1146 '$data->{'itemnumber'}' and returndate is NULL
1147 and items.itemnumber=issues.itemnumber and ((items.itemlost <>1 and
1148 items.itemlost <> 2) or items.itemlost is NULL)
1149 and (wthdrawn <> 1 or wthdrawn is NULL)";
1151 my $sth2=$dbh->prepare($query2);
1153 if (my $data2=$sth2->fetchrow_hashref){
1156 if ($data->{'holdingbranch'} eq 'C'){
1159 if ($data->{'holdingbranch'} eq 'F' || $data->{'holdingbranch'} eq 'FP'){
1162 if ($data->{'holdingbranch'} eq 'S' || $data->{'holdingbranch'} eq 'SP'){
1165 if ($data->{'itemlost'} eq '1'){
1168 if ($data->{'itemlost'} eq '2'){
1171 if ($data->{'holdingbranch'} eq 'FM'){
1174 if ($data->{'holdingbranch'} eq 'TR'){
1181 my $query2="Select * from aqorders where biblionumber=$bibnum";
1182 my $sth2=$dbh->prepare($query2);
1184 if (my $data=$sth2->fetchrow_hashref){
1185 $ocount=$data->{'quantity'} - $data->{'quantityreceived'};
1191 return ($count,$lcount,$nacount,$fcount,$scount,$lostcount,$mending,$transit,$ocount);
1196 my ($env,$bibnum,$type)=@_;
1198 my $query="Select * from items,branches where
1199 biblionumber=$bibnum and items.holdingbranch=branches.branchcode";
1200 if ($type ne 'intra'){
1201 $query.=" and ((itemlost <>1 and itemlost <> 2) or itemlost is NULL) and
1202 (wthdrawn <> 1 or wthdrawn is NULL)";
1204 my $sth=$dbh->prepare($query);
1209 while (my $data=$sth->fetchrow_hashref){
1211 my $query2="select * from issues,items where issues.itemnumber=
1212 '$data->{'itemnumber'}' and returndate is NULL
1213 and items.itemnumber=issues.itemnumber and ((items.itemlost <>1 and
1214 items.itemlost <> 2) or items.itemlost is NULL)
1215 and (wthdrawn <> 1 or wthdrawn is NULL)";
1217 my $sth2=$dbh->prepare($query2);
1219 if (my $data2=$sth2->fetchrow_hashref){
1220 $counts{'notavailable'}++;
1222 $counts{$data->{'branchname'}}++;
1226 my $query2="Select * from aqorders where biblionumber=$bibnum and
1227 datecancellationprinted is NULL and quantity > quantityreceived";
1228 my $sth2=$dbh->prepare($query2);
1230 if (my $data=$sth2->fetchrow_hashref){
1231 $counts{'order'}=$data->{'quantity'} - $data->{'quantityreceived'};
1243 my $query="select description from itemtypes where itemtype='$type'";
1244 my $sth=$dbh->prepare($query);
1246 my $dat=$sth->fetchrow_hashref;
1249 return ($dat->{'description'});
1255 my $dbh = C4Connect;
1256 my $query = "Select * from biblioitems, itemtypes, items
1257 where biblioitems.biblionumber = '$bibnum'
1258 and biblioitems.itemtype = itemtypes.itemtype
1259 and biblioitems.biblioitemnumber = items.biblioitemnumber
1260 group by items.biblioitemnumber";
1261 my $sth = $dbh->prepare($query);
1267 while (my $data = $sth->fetchrow_hashref) {
1268 $results[$count] = $data;
1274 return($count, @results);
1279 #called from request.pl
1280 my ($biblioitemnumber)=@_;
1282 my $query="Select barcode from items where
1283 biblioitemnumber='$biblioitemnumber'
1284 and ((itemlost <> 1 and itemlost <> 2) or itemlost is NULL) and
1285 (wthdrawn <> 1 or wthdrawn is NULL)";
1287 my $sth=$dbh->prepare($query);
1291 while (my $data=$sth->fetchrow_hashref){
1292 $barcodes[$i]=$data->{'barcode'};
1303 my ($biblionumber) = @_;
1304 my $dbh = C4Connect;
1305 my $query = "Select * from websites where biblionumber = $biblionumber";
1306 my $sth = $dbh->prepare($query);
1311 while (my $data = $sth->fetchrow_hashref) {
1312 $data->{'url'} =~ s/^http:\/\///;
1313 $results[$count] = $data;
1319 return($count, @results);
1323 sub getwebbiblioitems {
1324 my ($biblionumber) = @_;
1325 my $dbh = C4Connect;
1326 my $query = "Select * from biblioitems where biblionumber = $biblionumber
1327 and itemtype = 'WEB'";
1328 my $sth = $dbh->prepare($query);
1333 while (my $data = $sth->fetchrow_hashref) {
1334 $data->{'url'} =~ s/^http:\/\///;
1335 $results[$count] = $data;
1341 return($count, @results);
1342 } # sub getwebbiblioitems
1345 END { } # module clean-up code here (global destructor)
1349 C4::Search - Module that provides Catalog searching for Koha
1354 my ($count,@results)=catalogsearch($env,$type,$search,$num,$offset);
1358 This module provides the searching facilities for the Catalog.
1359 Here I should go through and document each function thats exported and what it does. But I havent yet.
1361 my ($count,@results)=catalogsearch($env,$type,$search,$num,$offset);
1362 This is a front end to all the other searches, depending on what is passed
1363 to it, it calls the appropriate search
1371 Koha Developement team <info@koha.org>