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 foreach my $key (keys %$counts){
114 if ($key ne 'total'){
115 $data->{'location'}.="$key $counts->{$key} ";
118 $data->{'subject2'}=$subject2;
121 return ($count,@results);
126 my ($env,$type,$search,$num,$offset)=@_;
127 my $dbh = &C4Connect;
128 $search->{'keyword'}=~ s/ +$//;
129 $search->{'keyword'}=~ s/'/\\'/;
130 my @key=split(' ',$search->{'keyword'});
134 my $query="Select biblionumber from biblio
135 where ((title like '$key[0]%' or title like '% $key[0]%')";
137 $query=$query." and (title like '$key[$i]%' or title like '% $key[$i]%')";
140 $query.= ") or ((biblio.notes like '$key[0]%' or biblio.notes like '% $key[0]%')";
141 for ($i=1;$i<$count;$i++){
142 $query.=" and (biblio.notes like '$key[$i]%' or biblio.notes like '% $key[$i]%')";
144 $query.= ") or ((seriestitle like '$key[0]%' or seriestitle like '% $key[0]%')";
145 for ($i=1;$i<$count;$i++){
146 $query.=" and (seriestitle like '$key[$i]%' or seriestitle like '% $key[$i]%')";
150 my $sth=$dbh->prepare($query);
153 while (my @res=$sth->fetchrow_array){
154 $results[$i]=$res[0];
158 my $set1=Set::Scalar->new(@results);
159 $query="Select biblionumber from bibliosubtitle where
160 ((subtitle like '$key[0]%' or subtitle like '% $key[0]%')";
161 for ($i=1;$i<$count;$i++){
162 $query.= " and (subtitle like '$key[$i]%' or subtitle like '% $key[$i]%')";
166 $sth=$dbh->prepare($query);
169 while (my @res=$sth->fetchrow_array){
170 $results[$i]=$res[0];
174 my $set2=Set::Scalar->new(@results);
178 $query ="Select biblionumber from biblioitems where
179 ((biblioitems.notes like '$key[0]%' or biblioitems.notes like '% $key[0]%')";
180 for ($i=1;$i<$count;$i++){
181 $query.=" and (biblioitems.notes like '$key[$i]%' or biblioitems.notes like '% $key[$i]%')";
185 $sth=$dbh->prepare($query);
188 while (my @res=$sth->fetchrow_array){
189 $results[$i]=$res[0];
193 my $set3=Set::Scalar->new(@results);
197 $sth=$dbh->prepare("Select biblionumber from bibliosubject where subject
198 like '%$search->{'keyword'}%' group by biblionumber");
201 while (my @res=$sth->fetchrow_array){
202 $results[$i]=$res[0];
206 my $set4=Set::Scalar->new(@results);
215 my @res = $set1->members;
219 # print "count $count";
220 if ($search->{'class'} ne ''){
222 my $query="select * from biblio,biblioitems where
223 biblio.biblionumber='$res[$i2]' and
224 biblio.biblionumber=biblioitems.biblionumber ";
225 if ($search->{'class'} ne ''){
226 my @temp=split(/\|/,$search->{'class'});
228 $query.= "and ( itemtype='$temp[0]'";
229 for (my $i=1;$i<$count;$i++){
230 $query.=" or itemtype='$temp[$i]'";
234 my $sth=$dbh->prepare($query);
237 if (my $data2=$sth->fetchrow_hashref){
238 my $dewey= $data2->{'dewey'};
239 my $subclass=$data2->{'subclass'};
241 ($dewey == 0) && ($dewey='');
242 ($dewey) && ($dewey.=" $subclass") ;
244 my $end=$offset +$num;
249 if ($i4 <=$end && $i4 > $offset){
250 $data2->{'dewey'}=$dewey;
253 # $res2[$i3]="$data2->{'author'}\t$data2->{'title'}\t$data2->{'biblionumber'}\t$data2->{'copyrightdate'}\t$dewey";
256 # print "in here $i3<br>";
267 while ($i2 < $num && $i2 < $count){
268 my $query="select * from biblio,biblioitems where
269 biblio.biblionumber='$res[$i2+$offset]' and
270 biblio.biblionumber=biblioitems.biblionumber ";
271 if ($search->{'class'} ne ''){
272 my @temp=split(/\|/,$search->{'class'});
274 $query.= "and ( itemtype='$temp[0]'";
275 for (my $i=1;$i<$count;$i++){
276 $query.=" or itemtype='$temp[$i]'";
280 if ($search->{'dewey'} ne ''){
281 $query.= "and (dewey like '$search->{'dewey'}%') ";
284 my $sth=$dbh->prepare($query);
287 if (my $data2=$sth->fetchrow_hashref){
288 my $dewey= $data2->{'dewey'};
289 my $subclass=$data2->{'subclass'};
291 ($dewey == 0) && ($dewey='');
292 ($dewey) && ($dewey.=" $subclass") ;
294 $data2->{'dewey'}=$dewey;
296 # $res2[$i]="$data2->{'author'}\t$data2->{'title'}\t$data2->{'biblionumber'}\t$data2->{'copyrightdate'}\t$dewey";
306 return($count,@res2);
310 my ($env,$type,$search,$num,$offset)=@_;
311 my $dbh = &C4Connect;
312 $search->{'keyword'}=~ s/ +$//;
313 $search->{'keyword'}=~ s/'/\\'/;
314 my @key=split(' ',$search->{'keyword'});
318 my $query ="Select * from biblio,bibliosubtitle,biblioitems where
319 biblio.biblionumber=biblioitems.biblionumber and
320 biblio.biblionumber=bibliosubtitle.biblionumber and
321 (((title like '$key[0]%' or title like '% $key[0]%')";
323 $query=$query." and (title like '$key[$i]%' or title like '% $key[$i]%')";
326 $query.= ") or ((subtitle like '$key[0]%' or subtitle like '% $key[0]%')";
327 for ($i=1;$i<$count;$i++){
328 $query.= " and (subtitle like '$key[$i]%' or subtitle like '% $key[$i]%')";
330 $query.= ") or ((seriestitle like '$key[0]%' or seriestitle like '% $key[0]%')";
331 for ($i=1;$i<$count;$i++){
332 $query.=" and (seriestitle like '$key[$i]%' or seriestitle like '% $key[$i]%')";
334 $query.= ") or ((biblio.notes like '$key[0]%' or biblio.notes like '% $key[0]%')";
335 for ($i=1;$i<$count;$i++){
336 $query.=" and (biblio.notes like '$key[$i]%' or biblio.notes like '% $key[$i]%')";
338 $query.= ") or ((biblioitems.notes like '$key[0]%' or biblioitems.notes like '% $key[0]%')";
339 for ($i=1;$i<$count;$i++){
340 $query.=" and (biblioitems.notes like '$key[$i]%' or biblioitems.notes like '% $key[$i]%')";
342 if ($search->{'keyword'} =~ /new zealand/i){
343 $query.= "or (title like 'nz%' or title like '% nz %' or title like '% nz' or subtitle like 'nz%'
344 or subtitle like '% nz %' or subtitle like '% nz' or author like 'nz %'
345 or author like '% nz %' or author like '% nz')"
347 if ($search->{'keyword'} eq 'nz' || $search->{'keyword'} eq 'NZ' ||
348 $search->{'keyword'} =~ /nz /i || $search->{'keyword'} =~ / nz /i ||
349 $search->{'keyword'} =~ / nz/i){
350 $query.= "or (title like 'new zealand%' or title like '% new zealand %'
351 or title like '% new zealand' or subtitle like 'new zealand%' or
352 subtitle like '% new zealand %'
353 or subtitle like '% new zealand' or author like 'new zealand%'
354 or author like '% new zealand %' or author like '% new zealand' or
355 seriestitle like 'new zealand%' or seriestitle like '% new zealand %'
356 or seriestitle like '% new zealand')"
359 if ($search->{'class'} ne ''){
360 my @temp=split(/\|/,$search->{'class'});
362 $query.= "and ( itemtype='$temp[0]'";
363 for (my $i=1;$i<$count;$i++){
364 $query.=" or itemtype='$temp[$i]'";
368 if ($search->{'dewey'} ne ''){
369 $query.= "and (dewey like '$search->{'dewey'}%') ";
371 $query.="group by biblio.biblionumber";
372 #$query.=" order by author,title";
374 my $sth=$dbh->prepare($query);
377 while (my $data=$sth->fetchrow_hashref){
378 # my $sti=$dbh->prepare("select dewey,subclass from biblioitems where biblionumber=$data->{'biblionumber'}
381 # my ($dewey, $subclass) = $sti->fetchrow;
382 my $dewey=$data->{'dewey'};
383 my $subclass=$data->{'subclass'};
385 ($dewey == 0) && ($dewey='');
386 ($dewey) && ($dewey.=" $subclass");
388 $results[$i]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey";
389 # print $results[$i];
393 $sth=$dbh->prepare("Select biblionumber from bibliosubject where subject
394 like '%$search->{'keyword'}%' group by biblionumber");
396 while (my $data=$sth->fetchrow_hashref){
397 $query="Select * from biblio,biblioitems where
398 biblio.biblionumber=$data->{'biblionumber'} and
399 biblio.biblionumber=biblioitems.biblionumber ";
400 if ($search->{'class'} ne ''){
401 my @temp=split(/\|/,$search->{'class'});
403 $query.= " and ( itemtype='$temp[0]'";
404 for (my $i=1;$i<$count;$i++){
405 $query.=" or itemtype='$temp[$i]'";
410 if ($search->{'dewey'} ne ''){
411 $query.= "and (dewey like '$search->{'dewey'}%') ";
413 my $sth2=$dbh->prepare($query);
416 while (my $data2=$sth2->fetchrow_hashref){
417 my $dewey= $data2->{'dewey'};
418 my $subclass=$data2->{'subclass'};
420 ($dewey == 0) && ($dewey='');
421 ($dewey) && ($dewey.=" $subclass") ;
423 $results[$i]="$data2->{'author'}\t$data2->{'title'}\t$data2->{'biblionumber'}\t$data2->{'copyrightdate'}\t$dewey";
424 # print $results[$i];
430 @results=sort @results;
437 while ($i2 < $count){
438 if ($results[$i2] ne $res[$i-1]){
439 $res[$i]=$results[$i2];
447 while ($i2 < $num && $i2 < $count){
448 $res2[$i2]=$res[$i2+$offset];
460 my ($env,$type,$search,$num,$offset)=@_;
461 my $dbh = &C4Connect;
464 $search->{'title'}=~ s/'/\\'/g;
465 $search->{'author'}=~ s/'/\\'/g;
466 $search->{'illustrator'}=~ s/'/\\'/g;
467 my $title = lc($search->{'title'});
469 if ($type eq 'loose') {
470 if ($search->{'author'} ne ''){
471 my @key=split(' ',$search->{'author'});
474 $query="select *,biblio.author,biblio.biblionumber from
476 left join additionalauthors
477 on additionalauthors.biblionumber =biblio.biblionumber
479 ((biblio.author like '$key[0]%' or biblio.author like '% $key[0]%' or
480 additionalauthors.author like '$key[0]%' or additionalauthors.author
484 $query=$query." and (
485 biblio.author like '$key[$i]%' or biblio.author like '% $key[$i]%' or
486 additionalauthors.author like '$key[$i]%' or additionalauthors.author like '% $key[$i]%'
491 if ($search->{'title'} ne ''){
492 my @key=split(' ',$search->{'title'});
495 $query.= " and (((title like '$key[0]%' or title like '% $key[0]%' or title like '% $key[0]')";
497 $query=$query." and (title like '$key[$i]%' or title like '% $key[$i]%' or title like '% $key[$i]')";
500 # $query.=") or ((subtitle like '$key[0]%' or subtitle like '% $key[0] %' or subtitle like '% $key[0]')";
501 # for ($i=1;$i<$count;$i++){
502 # $query.=" and (subtitle like '$key[$i]%' or subtitle like '% $key[$i] %' or subtitle like '% $key[$i]')";
504 $query.=") or ((seriestitle like '$key[0]%' or seriestitle like '% $key[0]%' or seriestitle like '% $key[0]')";
505 for ($i=1;$i<$count;$i++){
506 $query.=" and (seriestitle like '$key[$i]%' or seriestitle like '% $key[$i]%')";
508 $query.=") or ((unititle like '$key[0]%' or unititle like '% $key[0]%' or unititle like '% $key[0]')";
509 for ($i=1;$i<$count;$i++){
510 $query.=" and (unititle like '$key[$i]%' or unititle like '% $key[$i]%')";
513 #$query=$query. " and (title like '%$search->{'title'}%'
514 #or seriestitle like '%$search->{'title'}%')";
516 if ($search->{'abstract'} ne ''){
517 $query.= " and (abstract like '%$search->{'abstract'}%')";
521 $query.=" group by biblio.biblionumber";
523 if ($search->{'title'} ne '') {
524 if ($search->{'ttype'} eq 'exact'){
525 $query="select * from biblio
527 (biblio.title='$search->{'title'}' or (biblio.unititle = '$search->{'title'}'
528 or biblio.unititle like '$search->{'title'} |%' or
529 biblio.unititle like '%| $search->{'title'} |%' or
530 biblio.unititle like '%| $search->{'title'}') or
531 (biblio.seriestitle = '$search->{'title'}' or
532 biblio.seriestitle like '$search->{'title'} |%' or
533 biblio.seriestitle like '%| $search->{'title'} |%' or
534 biblio.seriestitle like '%| $search->{'title'}')
537 my @key=split(' ',$search->{'title'});
540 $query="select * from biblio
541 left join bibliosubtitle on
542 biblio.biblionumber=bibliosubtitle.biblionumber
544 (((title like '$key[0]%' or title like '% $key[0]%' or title like '% $key[0]')";
546 $query=$query." and (title like '$key[$i]%' or title like '% $key[$i]%' or title like '% $key[$i]')";
549 $query.=") or ((subtitle like '$key[0]%' or subtitle like '% $key[0]%' or subtitle like '% $key[0]')";
550 for ($i=1;$i<$count;$i++){
551 $query.=" and (subtitle like '$key[$i]%' or subtitle like '% $key[$i]%' or subtitle like '% $key[$i]')";
553 $query.=") or ((seriestitle like '$key[0]%' or seriestitle like '% $key[0]%' or seriestitle like '% $key[0]')";
554 for ($i=1;$i<$count;$i++){
555 $query.=" and (seriestitle like '$key[$i]%' or seriestitle like '% $key[$i]%')";
557 $query.=") or ((unititle like '$key[0]%' or unititle like '% $key[0]%' or unititle like '% $key[0]')";
558 for ($i=1;$i<$count;$i++){
559 $query.=" and (unititle like '$key[$i]%' or unititle like '% $key[$i]%')";
563 if ($search->{'abstract'} ne ''){
564 $query.= " and (abstract like '%$search->{'abstract'}%')";
566 } elsif ($search->{'class'} ne ''){
567 $query="select * from biblioitems,biblio where biblio.biblionumber=biblioitems.biblionumber";
568 my @temp=split(/\|/,$search->{'class'});
570 $query.= " and ( itemtype='$temp[0]'";
571 for (my $i=1;$i<$count;$i++){
572 $query.=" or itemtype='$temp[$i]'";
575 if ($search->{'illustrator'} ne ''){
576 $query.=" and illus like '%".$search->{'illustrator'}."%' ";
578 if ($search->{'dewey'} ne ''){
579 $query.=" and biblioitems.dewey like '$search->{'dewey'}%'";
581 } elsif ($search->{'dewey'} ne ''){
582 $query="select * from biblioitems,biblio
583 where biblio.biblionumber=biblioitems.biblionumber
584 and biblioitems.dewey like '$search->{'dewey'}%'";
585 } elsif ($search->{'illustrator'} ne '') {
586 $query="select * from biblioitems,biblio
587 where biblio.biblionumber=biblioitems.biblionumber
588 and biblioitems.illus like '%".$search->{'illustrator'}."%'";
589 } elsif ($search->{'publisher'} ne ''){
590 $query.= "Select * from biblio,biblioitems where biblio.biblionumber
591 =biblioitems.biblionumber and (publishercode like '%$search->{'publisher'}%')";
592 } elsif ($search->{'abstract'} ne ''){
593 $query.= "Select * from biblio where abstract like '%$search->{'abstract'}%'";
595 $query .=" group by biblio.biblionumber";
598 if ($type eq 'subject'){
599 my @key=split(' ',$search->{'subject'});
602 $query="select distinct(subject) from bibliosubject where( subject like
603 '$key[0]%' or subject like '% $key[0]%' or subject like '% $key[0]' or subject like '%($key[0])%')";
605 $query.=" and (subject like '$key[$i]%' or subject like '% $key[$i]%'
606 or subject like '% $key[$i]'
607 or subject like '%($key[$i])%')";
610 if ($search->{'subject'} eq 'NZ' || $search->{'subject'} eq 'nz'){
611 $query.= " or (subject like 'NEW ZEALAND %' or subject like '% NEW ZEALAND %'
612 or subject like '% NEW ZEALAND' or subject like '%(NEW ZEALAND)%' ) ";
613 } elsif ( $search->{'subject'} =~ /^nz /i || $search->{'subject'} =~ / nz /i || $search->{'subject'} =~ / nz$/i){
614 $query=~ s/ nz/ NEW ZEALAND/ig;
615 $query=~ s/nz /NEW ZEALAND /ig;
616 $query=~ s/\(nz\)/\(NEW ZEALAND\)/gi;
619 if ($type eq 'precise'){
621 if ($search->{'item'} ne ''){
622 $query="select * from items,biblio ";
623 my $search2=uc $search->{'item'};
624 $query=$query." where
625 items.biblionumber=biblio.biblionumber
626 and barcode='$search2'";
628 if ($search->{'isbn'} ne ''){
629 my $search2=uc $search->{'isbn'};
630 my $query1 = "select * from biblioitems where isbn='$search2'";
631 my $sth1=$dbh->prepare($query1);
632 # print STDERR "$query1\n";
635 while (my $data=$sth1->fetchrow_hashref) {
636 $query="select * from biblioitems,biblio where
637 biblio.biblionumber = $data->{'biblionumber'}
638 and biblioitems.biblionumber = biblio.biblionumber";
639 my $sth=$dbh->prepare($query);
641 my $data=$sth->fetchrow_hashref;
642 my ($dewey, $subclass) = ($data->{'dewey'}, $data->{'subclass'});
644 ($dewey == 0) && ($dewey='');
645 ($dewey) && ($dewey.=" $subclass");
646 $data->{'dewey'}=$dewey;
648 # $results[$i2]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey\t$data->{'isbn'}\t$data->{'itemtype'}";
656 if ($type ne 'precise' && $type ne 'subject'){
657 if ($search->{'author'} ne ''){
658 $query=$query." order by biblio.author,title";
660 $query=$query." order by title";
663 if ($type eq 'subject'){
664 $query=$query." order by subject";
667 #print STDERR "$query\n";
668 my $sth=$dbh->prepare($query);
672 my $limit= $num+$offset;
673 while (my $data=$sth->fetchrow_hashref){
674 my $query="select dewey,subclass from biblioitems where biblionumber=$data->{'biblionumber'}";
675 if ($search->{'class'} ne ''){
676 my @temp=split(/\|/,$search->{'class'});
678 $query.= " and ( itemtype='$temp[0]'";
679 for (my $i=1;$i<$count;$i++){
680 $query.=" or itemtype='$temp[$i]'";
684 if ($search->{'dewey'} ne ''){
685 $query.=" and dewey='$search->{'dewey'}' ";
687 if ($search->{'illustrator'} ne ''){
688 $query.=" and illus like '%".$search->{'illustrator'}."%' ";
690 if ($search->{'publisher'} ne ''){
691 $query.= " and (publishercode like '%$search->{'publisher'}%')";
693 #print STDERR "$query\n";
694 my $sti=$dbh->prepare($query);
699 if (($dewey, $subclass) = $sti->fetchrow || $type eq 'subject'){
703 ($dewey == 0) && ($dewey='');
704 ($dewey) && ($dewey.=" $subclass");
705 $data->{'dewey'}=$dewey;
708 if ($count > $offset && $count <= $limit){
716 #if ($type ne 'precise'){
720 return($count,@results);
723 sub updatesearchstats{
729 my ($env,$subject)=@_;
731 $subject=$dbh->quote($subject);
732 my $query="Select * from biblio,bibliosubject where
733 biblio.biblionumber=bibliosubject.biblionumber and
734 bibliosubject.subject=$subject group by biblio.biblionumber
735 order by biblio.title";
736 my $sth=$dbh->prepare($query);
741 while (my $data=$sth->fetchrow_hashref){
742 $results[$i]="$data->{'title'}\t$data->{'author'}\t$data->{'biblionumber'}";
752 my ($env,$biblionumber,$type) = @_;
753 my $dbh = &C4Connect;
754 my $query = "SELECT * FROM items, biblio, biblioitems
755 WHERE items.biblionumber = '$biblionumber'
756 AND biblioitems.biblioitemnumber = items.biblioitemnumber
757 AND biblio.biblionumber = items.biblionumber";
758 if ($type ne 'intra'){
759 $query .= " and ((items.itemlost<>1 and items.itemlost <> 2)
760 or items.itemlost is NULL)
761 and (wthdrawn <> 1 or wthdrawn is NULL)";
763 $query .= " order by items.dateaccessioned desc";
765 my $sth=$dbh->prepare($query);
770 while (my $data=$sth->fetchrow_hashref){
771 my $iquery = "Select * from issues
772 where itemnumber = '$data->{'itemnumber'}'
773 and returndate is null";
775 my $isth=$dbh->prepare($iquery);
777 if (my $idata=$isth->fetchrow_hashref){
778 my @temp=split('-',$idata->{'date_due'});
779 $datedue = "$temp[2]/$temp[1]/$temp[0]";
781 if ($data->{'itemlost'} eq '1' || $data->{'itemlost'} eq '2'){
784 if ($data->{'wthdrawn'} eq '1'){
785 $datedue="Cancelled";
788 $datedue="Available";
789 my ($restype,$reserves)=CheckReserves($data->{'itemnumber'});
795 #get branch information.....
796 my $bquery = "SELECT * FROM branches
797 WHERE branchcode = '$data->{'holdingbranch'}'";
798 my $bsth=$dbh->prepare($bquery);
800 if (my $bdata=$bsth->fetchrow_hashref){
801 $data->{'branchname'} = $bdata->{'branchname'};
804 my $class = $data->{'classification'};
805 my $dewey = $data->{'dewey'};
807 if ($dewey eq "000.") { $dewey = "";};
808 if ($dewey < 10){$dewey='00'.$dewey;}
809 if ($dewey < 100 && $dewey > 10){$dewey='0'.$dewey;}
814 $class = $class.$dewey;
816 $class = $class.$data->{'subclass'};
818 # $results[$i]="$data->{'title'}\t$data->{'barcode'}\t$datedue\t$data->{'branchname'}\t$data->{'dewey'}";
819 my @temp=split('-',$data->{'datelastseen'});
820 my $date="$temp[2]/$temp[1]/$temp[0]";
821 $data->{'datelastseen'}=$date;
822 $data->{'datedue'}=$datedue;
823 $data->{'class'}=$class;
828 my $query2="Select * from aqorders where biblionumber=$biblionumber";
829 my $sth2=$dbh->prepare($query2);
833 if ($data=$sth2->fetchrow_hashref){
834 $ocount=$data->{'quantity'} - $data->{'quantityreceived'};
836 $data->{'ocount'}=$ocount;
837 $data->{'order'}="One Order";
848 my ($env,$biblionumber)=@_;
849 #debug_msg($env,"GetItems");
850 my $dbh = &C4Connect;
851 my $query = "Select * from biblioitems where (biblionumber = $biblionumber)";
852 #debug_msg($env,$query);
853 my $sth=$dbh->prepare($query);
855 #debug_msg($env,"executed query");
858 while (my $data=$sth->fetchrow_hashref) {
859 #debug_msg($env,$data->{'biblioitemnumber'});
860 my $dewey = $data->{'dewey'};
862 my $line = $data->{'biblioitemnumber'}."\t".$data->{'itemtype'};
863 $line = $line."\t$data->{'classification'}\t$dewey";
864 $line = $line."\t$data->{'subclass'}\t$data->{isbn}";
865 $line = $line."\t$data->{'volume'}\t$data->{number}";
866 my $isth= $dbh->prepare("select * from items where biblioitemnumber = $data->{'biblioitemnumber'}");
868 while (my $idata = $isth->fetchrow_hashref) {
869 my $iline = $idata->{'barcode'}."[".$idata->{'holdingbranch'}."[";
870 if ($idata->{'notforloan'} == 1) {
871 $iline = $iline."NFL ";
873 if ($idata->{'itemlost'} == 1) {
874 $iline = $iline."LOST ";
876 $line = $line."\t$iline";
879 $results[$i] = $line;
890 my $query="Select * from items,biblioitems where barcode='$barcode'
891 and items.biblioitemnumber=biblioitems.biblioitemnumber";
893 my $sth=$dbh->prepare($query);
895 my $data=$sth->fetchrow_hashref;
903 my ($bibnum, $type) = @_;
905 my $query = "Select *, biblio.notes
906 from biblio, biblioitems
907 left join bibliosubtitle on
908 biblio.biblionumber = bibliosubtitle.biblionumber
909 where biblio.biblionumber = $bibnum
910 and biblioitems.biblionumber = $bibnum";
911 my $sth = $dbh->prepare($query);
915 $data = $sth->fetchrow_hashref;
918 $query = "Select * from bibliosubject where biblionumber = '$bibnum'";
919 $sth = $dbh->prepare($query);
921 while (my $dat = $sth->fetchrow_hashref){
922 $data->{'subject'} .= " | $dat->{'subject'}";
934 my $query = "Select *,biblioitems.notes as bnotes from biblio, biblioitems,itemtypes
935 where biblio.biblionumber = biblioitems.biblionumber
936 and biblioitemnumber = $bibitem
937 and biblioitems.itemtype = itemtypes.itemtype";
938 my $sth = $dbh->prepare($query);
943 $data = $sth->fetchrow_hashref;
954 my $query="Select * from bibliosubject where biblionumber=$bibnum";
955 my $sth=$dbh->prepare($query);
959 while (my $data=$sth->fetchrow_hashref){
965 return($i,\@results);
971 my $query="Select * from additionalauthors where biblionumber=$bibnum";
972 my $sth=$dbh->prepare($query);
976 while (my $data=$sth->fetchrow_hashref){
982 return($i,\@results);
988 my $query="Select * from bibliosubtitle where biblionumber=$bibnum";
989 my $sth=$dbh->prepare($query);
993 while (my $data=$sth->fetchrow_hashref){
999 return($i,\@results);
1005 my ($bibitem, $biblio)=@_;
1006 my $dbh = C4Connect;
1007 my $query = "Select * from items where
1008 items.biblioitemnumber = '$bibitem'";
1009 my $sth = $dbh->prepare($query)
1010 || die $dbh->errstr;
1015 || die $sth->errstr;
1017 while (my $data = $sth->fetchrow_hashref) {
1018 my $query2 = "select * from issues,borrowers
1019 where itemnumber = $data->{'itemnumber'}
1020 and returndate is NULL
1021 and issues.borrowernumber = borrowers.borrowernumber";
1022 my $sth2 = $dbh->prepare($query2);
1025 if (my $data2 = $sth2->fetchrow_hashref) {
1026 $data->{'date_due'} = $data2->{'date_due'};
1027 $data->{'card'} = $data2->{'cardnumber'};
1029 if ($data->{'wthdrawn'} eq '1') {
1030 $data->{'date_due'} = 'Cancelled';
1032 $data->{'date_due'} = 'Available';
1037 $query2 = "select * from issues, borrowers
1038 where itemnumber = '$data->{'itemnumber'}'
1039 and issues.borrowernumber = borrowers.borrowernumber
1040 order by date_due desc";
1041 $sth2 = $dbh->prepare($query2)
1042 || die $dbh->errstr;
1044 || die $sth2->errstr;
1046 for (my $i2 = 0; $i2 < 2; $i2++) {
1047 if (my $data2 = $sth2->fetchrow_hashref) {
1048 $data->{"timestamp$i2"} = $data2->{'timestamp'};
1049 $data->{"card$i2"} = $data2->{'cardnumber'};
1050 $data->{"borrower$i2"} = $data2->{'borrowernumber'};
1055 $results[$i] = $data;
1066 my ($env,$dbh,$itemnumber) = @_;
1068 my $query="Select * from biblio,items,biblioitems
1069 where items.itemnumber = '$itemnumber'
1070 and biblio.biblionumber = items.biblionumber
1071 and biblioitems.biblioitemnumber = items.biblioitemnumber";
1072 my $sth=$dbh->prepare($query);
1075 my $data=$sth->fetchrow_hashref;
1081 #used by member enquiries from the intranet
1082 #called by member.pl
1084 my ($env,$searchstring,$type)=@_;
1085 my $dbh = &C4Connect;
1086 $searchstring=~ s/\'/\\\'/g;
1087 my @data=split(' ',$searchstring);
1089 my $query="Select * from borrowers
1090 where ((surname like \"$data[0]%\" or surname like \"% $data[0]%\"
1091 or firstname like \"$data[0]%\" or firstname like \"% $data[0]%\"
1092 or othernames like \"$data[0]%\" or othernames like \"% $data[0]%\")
1094 for (my $i=1;$i<$count;$i++){
1095 $query=$query." and (surname like \"$data[$i]%\" or surname like \"% $data[$i]%\"
1096 or firstname like \"$data[$i]%\" or firstname like \"% $data[$i]%\"
1097 or othernames like \"$data[$i]%\" or othernames like \"% $data[$i]%\")";
1099 $query=$query.") or cardnumber = \"$searchstring\"
1100 order by surname,firstname";
1101 # print $query,"\n";
1102 my $sth=$dbh->prepare($query);
1106 while (my $data=$sth->fetchrow_hashref){
1107 push(@results,$data);
1113 return ($cnt,\@results);
1117 my ($cardnumber,$bornum)=@_;
1118 $cardnumber = uc $cardnumber;
1122 $query="Select * from borrowers where cardnumber='$cardnumber'";
1124 $query="Select * from borrowers where borrowernumber='$bornum'";
1127 my $sth=$dbh->prepare($query);
1129 my $data=$sth->fetchrow_hashref;
1139 $query="Select * from issues,biblio,items where borrowernumber='$bornum' and
1140 items.itemnumber=issues.itemnumber and
1141 items.biblionumber=biblio.biblionumber and issues.returndate is NULL order
1144 my $sth=$dbh->prepare($query);
1148 while (my $data=$sth->fetchrow_hashref){
1154 return($i,\@result);
1158 my ($bornum,$order,$limit)=@_;
1161 $query="Select * from issues,biblio,items,biblioitems
1162 where borrowernumber='$bornum' and
1163 items.biblioitemnumber=biblioitems.biblioitemnumber and
1164 items.itemnumber=issues.itemnumber and
1165 items.biblionumber=biblio.biblionumber";
1166 $query.=" order by $order";
1168 $query.=" limit $limit";
1171 my $sth=$dbh->prepare($query);
1175 while (my $data=$sth->fetchrow_hashref){
1181 return($i,\@result);
1185 my ($env,$bornum)=@_;
1187 my $query="Select count(*) from issues where borrowernumber='$bornum' and
1188 returndate is NULL";
1190 my $sth=$dbh->prepare($query);
1192 my $data=$sth->fetchrow_hashref;
1194 $sth=$dbh->prepare("Select count(*) from issues where
1195 borrowernumber='$bornum' and date_due < now() and returndate is NULL");
1197 my $data2=$sth->fetchrow_hashref;
1199 $sth=$dbh->prepare("Select sum(amountoutstanding) from accountlines where
1200 borrowernumber='$bornum'");
1202 my $data3=$sth->fetchrow_hashref;
1206 return($data2->{'count(*)'},$data->{'count(*)'},$data3->{'sum(amountoutstanding)'});
1210 sub getboracctrecord {
1211 my ($env,$params) = @_;
1215 my $query= "Select * from accountlines where
1216 borrowernumber=$params->{'borrowernumber'} order by date desc,timestamp desc";
1217 my $sth=$dbh->prepare($query);
1221 while (my $data=$sth->fetchrow_hashref){
1222 # if ($data->{'itemnumber'} ne ''){
1223 # $query="Select * from items,biblio where items.itemnumber=
1224 # '$data->{'itemnumber'}' and biblio.biblionumber=items.biblionumber";
1225 # my $sth2=$dbh->prepare($query);
1227 # my $data2=$sth2->fetchrow_hashref;
1231 $acctlines[$numlines] = $data;
1233 $total = $total+ $data->{'amountoutstanding'};
1237 return ($numlines,\@acctlines,$total);
1242 my ($env,$bibnum,$type)=@_;
1244 my $query="Select * from items where
1245 biblionumber=$bibnum ";
1246 if ($type ne 'intra'){
1247 $query.=" and ((itemlost <>1 and itemlost <> 2) or itemlost is NULL) and
1248 (wthdrawn <> 1 or wthdrawn is NULL)";
1250 my $sth=$dbh->prepare($query);
1262 while (my $data=$sth->fetchrow_hashref){
1264 my $query2="select * from issues,items where issues.itemnumber=
1265 '$data->{'itemnumber'}' and returndate is NULL
1266 and items.itemnumber=issues.itemnumber and ((items.itemlost <>1 and
1267 items.itemlost <> 2) or items.itemlost is NULL)
1268 and (wthdrawn <> 1 or wthdrawn is NULL)";
1270 my $sth2=$dbh->prepare($query2);
1272 if (my $data2=$sth2->fetchrow_hashref){
1275 if ($data->{'holdingbranch'} eq 'C'){
1278 if ($data->{'holdingbranch'} eq 'F' || $data->{'holdingbranch'} eq 'FP'){
1281 if ($data->{'holdingbranch'} eq 'S' || $data->{'holdingbranch'} eq 'SP'){
1284 if ($data->{'itemlost'} eq '1'){
1287 if ($data->{'itemlost'} eq '2'){
1290 if ($data->{'holdingbranch'} eq 'FM'){
1293 if ($data->{'holdingbranch'} eq 'TR'){
1300 my $query2="Select * from aqorders where biblionumber=$bibnum";
1301 my $sth2=$dbh->prepare($query2);
1303 if (my $data=$sth2->fetchrow_hashref){
1304 $ocount=$data->{'quantity'} - $data->{'quantityreceived'};
1310 return ($count,$lcount,$nacount,$fcount,$scount,$lostcount,$mending,$transit,$ocount);
1315 my ($env,$bibnum,$type)=@_;
1317 my $query="Select * from items,branches where
1318 biblionumber=$bibnum and items.holdingbranch=branches.branchcode";
1319 if ($type ne 'intra'){
1320 $query.=" and ((itemlost <>1 and itemlost <> 2) or itemlost is NULL) and
1321 (wthdrawn <> 1 or wthdrawn is NULL)";
1323 my $sth=$dbh->prepare($query);
1328 while (my $data=$sth->fetchrow_hashref){
1330 my $query2="select * from issues,items where issues.itemnumber=
1331 '$data->{'itemnumber'}' and returndate is NULL
1332 and items.itemnumber=issues.itemnumber and ((items.itemlost <>1 and
1333 items.itemlost <> 2) or items.itemlost is NULL)
1334 and (wthdrawn <> 1 or wthdrawn is NULL)";
1336 my $sth2=$dbh->prepare($query2);
1338 if (my $data2=$sth2->fetchrow_hashref){
1339 $counts{'notavailable'}++;
1341 $counts{$data->{'branchname'}}++;
1345 my $query2="Select * from aqorders where biblionumber=$bibnum and
1346 datecancellationprinted is NULL and quantity > quantityreceived";
1347 my $sth2=$dbh->prepare($query2);
1349 if (my $data=$sth2->fetchrow_hashref){
1350 $counts{'order'}=$data->{'quantity'} - $data->{'quantityreceived'};
1362 my $query="select description from itemtypes where itemtype='$type'";
1363 my $sth=$dbh->prepare($query);
1365 my $dat=$sth->fetchrow_hashref;
1368 return ($dat->{'description'});
1374 my $dbh = C4Connect;
1375 my $query = "Select * from biblioitems, itemtypes, items
1376 where biblioitems.biblionumber = '$bibnum'
1377 and biblioitems.itemtype = itemtypes.itemtype
1378 and biblioitems.biblioitemnumber = items.biblioitemnumber
1379 group by items.biblioitemnumber";
1380 my $sth = $dbh->prepare($query);
1386 while (my $data = $sth->fetchrow_hashref) {
1387 $results[$count] = $data;
1393 return($count, @results);
1398 #called from request.pl
1399 my ($biblioitemnumber)=@_;
1401 my $query="Select barcode from items where
1402 biblioitemnumber='$biblioitemnumber'
1403 and ((itemlost <> 1 and itemlost <> 2) or itemlost is NULL) and
1404 (wthdrawn <> 1 or wthdrawn is NULL)";
1406 my $sth=$dbh->prepare($query);
1410 while (my $data=$sth->fetchrow_hashref){
1411 $barcodes[$i]=$data->{'barcode'};
1422 my ($biblionumber) = @_;
1423 my $dbh = C4Connect;
1424 my $query = "Select * from websites where biblionumber = $biblionumber";
1425 my $sth = $dbh->prepare($query);
1430 while (my $data = $sth->fetchrow_hashref) {
1431 $data->{'url'} =~ s/^http:\/\///;
1432 $results[$count] = $data;
1438 return($count, @results);
1442 sub getwebbiblioitems {
1443 my ($biblionumber) = @_;
1444 my $dbh = C4Connect;
1445 my $query = "Select * from biblioitems where biblionumber = $biblionumber
1446 and itemtype = 'WEB'";
1447 my $sth = $dbh->prepare($query);
1452 while (my $data = $sth->fetchrow_hashref) {
1453 $data->{'url'} =~ s/^http:\/\///;
1454 $results[$count] = $data;
1460 return($count, @results);
1461 } # sub getwebbiblioitems
1464 END { } # module clean-up code here (global destructor)
1468 C4::Search - Module that provides Catalog searching for Koha
1473 my ($count,@results)=catalogsearch($env,$type,$search,$num,$offset);
1477 This module provides the searching facilities for the Catalog.
1478 Here I should go through and document each function thats exported and what it does. But I havent yet.
1480 my ($count,@results)=catalogsearch($env,$type,$search,$num,$offset);
1481 This is a front end to all the other searches, depending on what is passed
1482 to it, it calls the appropriate search
1490 Koha Developement team <info@koha.org>