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);
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 if ($search->{'itemnumber'} ne '' || $search->{'isbn'} ne ''){
127 ($count,@results)=CatSearch($env,'precise',$search,$num,$offset);
129 if ($search->{'subject'} ne ''){
130 ($count,@results)=CatSearch($env,'subject',$search,$num,$offset);
132 if ($search->{'keyword'} ne ''){
133 ($count,@results)=&KeywordSearch($env,'keyword',$search,$num,$offset);
134 } elsif ($search->{'title'} ne '' || $search->{'author'} ne '' ||
135 $search->{'illustrator'} ne '' || $search->{'dewey'} ne '' ||
136 $search->{'class'} ne ''){
137 ($count,@results)=CatSearch($env,'loose',$search,$num,$offset);
141 if ($env->{itemcount}) {
142 foreach my $data (@results){
143 my ($count, $lcount, $nacount, $fcount, $scount, $lostcount, $mending, $transit, $ocount) = itemcount($env, $data->{'biblionumber'}, 'intra');
144 $data->{'itemcount'}=$count;
147 return ($count,@results);
152 my ($env,$type,$search,$num,$offset)=@_;
153 my $dbh = &C4Connect;
154 $search->{'keyword'}=~ s/ +$//;
155 $search->{'keyword'}=~ s/'/\\'/;
156 my @key=split(' ',$search->{'keyword'});
160 my $query="Select biblionumber from biblio
161 where ((title like '$key[0]%' or title like '% $key[0]%')";
163 $query=$query." and (title like '$key[$i]%' or title like '% $key[$i]%')";
166 $query.= ") or ((biblio.notes like '$key[0]%' or biblio.notes like '% $key[0]%')";
167 for ($i=1;$i<$count;$i++){
168 $query.=" and (biblio.notes like '$key[$i]%' or biblio.notes like '% $key[$i]%')";
170 $query.= ") or ((seriestitle like '$key[0]%' or seriestitle like '% $key[0]%')";
171 for ($i=1;$i<$count;$i++){
172 $query.=" and (seriestitle like '$key[$i]%' or seriestitle like '% $key[$i]%')";
176 my $sth=$dbh->prepare($query);
179 while (my @res=$sth->fetchrow_array){
180 $results[$i]=$res[0];
184 my $set1=Set::Scalar->new(@results);
185 $query="Select biblionumber from bibliosubtitle where
186 ((subtitle like '$key[0]%' or subtitle like '% $key[0]%')";
187 for ($i=1;$i<$count;$i++){
188 $query.= " and (subtitle like '$key[$i]%' or subtitle like '% $key[$i]%')";
192 $sth=$dbh->prepare($query);
195 while (my @res=$sth->fetchrow_array){
196 $results[$i]=$res[0];
200 my $set2=Set::Scalar->new(@results);
204 $query ="Select biblionumber from biblioitems where
205 ((biblioitems.notes like '$key[0]%' or biblioitems.notes like '% $key[0]%')";
206 for ($i=1;$i<$count;$i++){
207 $query.=" and (biblioitems.notes like '$key[$i]%' or biblioitems.notes like '% $key[$i]%')";
211 $sth=$dbh->prepare($query);
214 while (my @res=$sth->fetchrow_array){
215 $results[$i]=$res[0];
219 my $set3=Set::Scalar->new(@results);
223 $sth=$dbh->prepare("Select biblionumber from bibliosubject where subject
224 like '%$search->{'keyword'}%' group by biblionumber");
227 while (my @res=$sth->fetchrow_array){
228 $results[$i]=$res[0];
232 my $set4=Set::Scalar->new(@results);
241 my @res = $set1->members;
245 # print "count $count";
246 if ($search->{'class'} ne ''){
248 my $query="select * from biblio,biblioitems where
249 biblio.biblionumber='$res[$i2]' and
250 biblio.biblionumber=biblioitems.biblionumber ";
251 if ($search->{'class'} ne ''){
252 my @temp=split(/\|/,$search->{'class'});
254 $query.= "and ( itemtype='$temp[0]'";
255 for (my $i=1;$i<$count;$i++){
256 $query.=" or itemtype='$temp[$i]'";
260 my $sth=$dbh->prepare($query);
263 if (my $data2=$sth->fetchrow_hashref){
264 my $dewey= $data2->{'dewey'};
265 my $subclass=$data2->{'subclass'};
267 ($dewey == 0) && ($dewey='');
268 ($dewey) && ($dewey.=" $subclass") ;
270 my $end=$offset +$num;
275 if ($i4 <=$end && $i4 > $offset){
276 $data2->{'dewey'}=$dewey;
279 # $res2[$i3]="$data2->{'author'}\t$data2->{'title'}\t$data2->{'biblionumber'}\t$data2->{'copyrightdate'}\t$dewey";
282 # print "in here $i3<br>";
293 while ($i2 < $num && $i2 < $count){
294 my $query="select * from biblio,biblioitems where
295 biblio.biblionumber='$res[$i2+$offset]' and
296 biblio.biblionumber=biblioitems.biblionumber ";
297 if ($search->{'class'} ne ''){
298 my @temp=split(/\|/,$search->{'class'});
300 $query.= "and ( itemtype='$temp[0]'";
301 for (my $i=1;$i<$count;$i++){
302 $query.=" or itemtype='$temp[$i]'";
306 if ($search->{'dewey'} ne ''){
307 $query.= "and (dewey like '$search->{'dewey'}%') ";
310 my $sth=$dbh->prepare($query);
313 if (my $data2=$sth->fetchrow_hashref){
314 my $dewey= $data2->{'dewey'};
315 my $subclass=$data2->{'subclass'};
317 ($dewey == 0) && ($dewey='');
318 ($dewey) && ($dewey.=" $subclass") ;
320 $data2->{'dewey'}=$dewey;
322 # $res2[$i]="$data2->{'author'}\t$data2->{'title'}\t$data2->{'biblionumber'}\t$data2->{'copyrightdate'}\t$dewey";
332 return($count,@res2);
336 my ($env,$type,$search,$num,$offset)=@_;
337 my $dbh = &C4Connect;
340 $search->{'title'}=~ s/'/\\'/g;
341 $search->{'author'}=~ s/'/\\'/g;
342 $search->{'illustrator'}=~ s/'/\\'/g;
343 my $title = lc($search->{'title'});
345 if ($type eq 'loose') {
346 if ($search->{'author'} ne ''){
347 my @key=split(' ',$search->{'author'});
350 $query="select *,biblio.author,biblio.biblionumber from
352 left join additionalauthors
353 on additionalauthors.biblionumber =biblio.biblionumber
355 ((biblio.author like '$key[0]%' or biblio.author like '% $key[0]%' or
356 additionalauthors.author like '$key[0]%' or additionalauthors.author
360 $query=$query." and (
361 biblio.author like '$key[$i]%' or biblio.author like '% $key[$i]%' or
362 additionalauthors.author like '$key[$i]%' or additionalauthors.author like '% $key[$i]%'
367 if ($search->{'title'} ne ''){
368 my @key=split(' ',$search->{'title'});
371 $query.= " and (((title like '$key[0]%' or title like '% $key[0]%' or title like '% $key[0]')";
373 $query=$query." and (title like '$key[$i]%' or title like '% $key[$i]%' or title like '% $key[$i]')";
376 # $query.=") or ((subtitle like '$key[0]%' or subtitle like '% $key[0] %' or subtitle like '% $key[0]')";
377 # for ($i=1;$i<$count;$i++){
378 # $query.=" and (subtitle like '$key[$i]%' or subtitle like '% $key[$i] %' or subtitle like '% $key[$i]')";
380 $query.=") or ((seriestitle like '$key[0]%' or seriestitle like '% $key[0]%' or seriestitle like '% $key[0]')";
381 for ($i=1;$i<$count;$i++){
382 $query.=" and (seriestitle like '$key[$i]%' or seriestitle like '% $key[$i]%')";
384 $query.=") or ((unititle like '$key[0]%' or unititle like '% $key[0]%' or unititle like '% $key[0]')";
385 for ($i=1;$i<$count;$i++){
386 $query.=" and (unititle like '$key[$i]%' or unititle like '% $key[$i]%')";
389 #$query=$query. " and (title like '%$search->{'title'}%'
390 #or seriestitle like '%$search->{'title'}%')";
393 $query.=" group by biblio.biblionumber";
395 if ($search->{'title'} ne '') {
396 if ($search->{'ttype'} eq 'exact'){
397 $query="select * from biblio
399 (biblio.title='$search->{'title'}' or (biblio.unititle = '$search->{'title'}'
400 or biblio.unititle like '$search->{'title'} |%' or
401 biblio.unititle like '%| $search->{'title'} |%' or
402 biblio.unititle like '%| $search->{'title'}') or
403 (biblio.seriestitle = '$search->{'title'}' or
404 biblio.seriestitle like '$search->{'title'} |%' or
405 biblio.seriestitle like '%| $search->{'title'} |%' or
406 biblio.seriestitle like '%| $search->{'title'}')
409 my @key=split(' ',$search->{'title'});
412 $query="select * from biblio
413 left join bibliosubtitle on
414 biblio.biblionumber=bibliosubtitle.biblionumber
416 (((title like '$key[0]%' or title like '% $key[0]%' or title like '% $key[0]')";
418 $query=$query." and (title like '$key[$i]%' or title like '% $key[$i]%' or title like '% $key[$i]')";
421 $query.=") or ((subtitle like '$key[0]%' or subtitle like '% $key[0]%' or subtitle like '% $key[0]')";
422 for ($i=1;$i<$count;$i++){
423 $query.=" and (subtitle like '$key[$i]%' or subtitle like '% $key[$i]%' or subtitle like '% $key[$i]')";
425 $query.=") or ((seriestitle like '$key[0]%' or seriestitle like '% $key[0]%' or seriestitle like '% $key[0]')";
426 for ($i=1;$i<$count;$i++){
427 $query.=" and (seriestitle like '$key[$i]%' or seriestitle like '% $key[$i]%')";
429 $query.=") or ((unititle like '$key[0]%' or unititle like '% $key[0]%' or unititle like '% $key[0]')";
430 for ($i=1;$i<$count;$i++){
431 $query.=" and (unititle like '$key[$i]%' or unititle like '% $key[$i]%')";
435 } elsif ($search->{'class'} ne ''){
436 $query="select * from biblioitems,biblio where biblio.biblionumber=biblioitems.biblionumber";
437 my @temp=split(/\|/,$search->{'class'});
439 $query.= " and ( itemtype='$temp[0]'";
440 for (my $i=1;$i<$count;$i++){
441 $query.=" or itemtype='$temp[$i]'";
444 if ($search->{'illustrator'} ne ''){
445 $query.=" and illus like '%".$search->{'illustrator'}."%' ";
447 if ($search->{'dewey'} ne ''){
448 $query.=" and biblioitems.dewey like '$search->{'dewey'}%'";
450 } elsif ($search->{'dewey'} ne ''){
451 $query="select * from biblioitems,biblio
452 where biblio.biblionumber=biblioitems.biblionumber
453 and biblioitems.dewey like '$search->{'dewey'}%'";
454 } elsif ($search->{'illustrator'} ne '') {
455 if ($search->{'illustrator'} ne ''){
456 $query="select * from biblioitems,biblio
457 where biblio.biblionumber=biblioitems.biblionumber
458 and biblioitems.illus like '%".$search->{'illustrator'}."%'";
461 $query .=" group by biblio.biblionumber";
464 if ($type eq 'subject'){
465 my @key=split(' ',$search->{'subject'});
468 $query="select distinct(subject) from bibliosubject where( subject like
469 '$key[0]%' or subject like '% $key[0]%' or subject like '% $key[0]' or subject like '%($key[0])%')";
471 $query.=" and (subject like '$key[$i]%' or subject like '% $key[$i]%'
472 or subject like '% $key[$i]'
473 or subject like '%($key[$i])%')";
476 if ($search->{'subject'} eq 'NZ' || $search->{'subject'} eq 'nz'){
477 $query.= " or (subject like 'NEW ZEALAND %' or subject like '% NEW ZEALAND %'
478 or subject like '% NEW ZEALAND' or subject like '%(NEW ZEALAND)%' ) ";
479 } elsif ( $search->{'subject'} =~ /^nz /i || $search->{'subject'} =~ / nz /i || $search->{'subject'} =~ / nz$/i){
480 $query=~ s/ nz/ NEW ZEALAND/ig;
481 $query=~ s/nz /NEW ZEALAND /ig;
482 $query=~ s/\(nz\)/\(NEW ZEALAND\)/gi;
485 if ($type eq 'precise'){
486 $query="select * from items,biblio ";
487 if ($search->{'item'} ne ''){
488 my $search2=uc $search->{'item'};
489 $query=$query." where
490 items.biblionumber=biblio.biblionumber
491 and barcode='$search2'";
493 if ($search->{'isbn'} ne ''){
494 my $search2=uc $search->{'isbn'};
495 my $query1 = "select * from biblioitems where isbn='$search2'";
496 my $sth1=$dbh->prepare($query1);
500 while (my $data=$sth1->fetchrow_hashref) {
501 $query="select * from biblioitems,biblio where
502 biblio.biblionumber = $data->{'biblionumber'}
503 and biblioitems.biblionumber = biblio.biblionumber";
504 my $sth=$dbh->prepare($query);
506 my $data=$sth->fetchrow_hashref;
507 my ($dewey, $subclass) = ($data->{'dewey'}, $data->{'subclass'});
509 ($dewey == 0) && ($dewey='');
510 ($dewey) && ($dewey.=" $subclass");
511 $data->{'dewey'}=$dewey;
513 # $results[$i2]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey\t$data->{'isbn'}\t$data->{'itemtype'}";
521 if ($type ne 'precise' && $type ne 'subject'){
522 if ($search->{'author'} ne ''){
523 $query=$query." order by biblio.author,title";
525 $query=$query." order by title";
528 if ($type eq 'subject'){
529 $query=$query." order by subject";
533 my $sth=$dbh->prepare($query);
537 my $limit= $num+$offset;
538 while (my $data=$sth->fetchrow_hashref){
539 my $query="select dewey,subclass from biblioitems where biblionumber=$data->{'biblionumber'}";
540 if ($search->{'class'} ne ''){
541 my @temp=split(/\|/,$search->{'class'});
543 $query.= " and ( itemtype='$temp[0]'";
544 for (my $i=1;$i<$count;$i++){
545 $query.=" or itemtype='$temp[$i]'";
549 if ($search->{'dewey'} ne ''){
550 $query.=" and dewey='$search->{'dewey'}' ";
552 if ($search->{'illustrator'} ne ''){
553 $query.=" and illus like '%".$search->{'illustrator'}."%' ";
556 my $sti=$dbh->prepare($query);
561 if (($dewey, $subclass) = $sti->fetchrow || $type eq 'subject'){
565 ($dewey == 0) && ($dewey='');
566 ($dewey) && ($dewey.=" $subclass");
567 $data->{'dewey'}=$dewey;
570 if ($count > $offset && $count <= $limit){
578 #if ($type ne 'precise'){
582 return($count,@results);
585 sub updatesearchstats{
591 my ($env,$subject)=@_;
593 $subject=$dbh->quote($subject);
594 my $query="Select * from biblio,bibliosubject where
595 biblio.biblionumber=bibliosubject.biblionumber and
596 bibliosubject.subject=$subject group by biblio.biblionumber
597 order by biblio.title";
598 my $sth=$dbh->prepare($query);
603 while (my $data=$sth->fetchrow_hashref){
604 $results[$i]="$data->{'title'}\t$data->{'author'}\t$data->{'biblionumber'}";
614 my ($env,$biblionumber,$type) = @_;
615 my $dbh = &C4Connect;
616 my $query = "Select * from items, biblio, biblioitems, branches
617 where (items.biblioitemnumber = biblioitems.biblioitemnumber)
618 and biblioitems.biblionumber = biblio.biblionumber
619 and biblio.biblionumber = '$biblionumber'
620 and branches.branchcode = items.holdingbranch";
622 if ($type ne 'intra'){
623 $query .= " and ((items.itemlost<>1 and items.itemlost <> 2)
624 or items.itemlost is NULL)
625 and (wthdrawn <> 1 or wthdrawn is NULL)";
627 $query .= " order by items.dateaccessioned desc";
628 my $sth=$dbh->prepare($query);
633 while (my $data=$sth->fetchrow_hashref){
634 my $iquery = "Select * from issues
635 where itemnumber = '$data->{'itemnumber'}'
636 and returndate is null";
638 my $isth=$dbh->prepare($iquery);
640 if (my $idata=$isth->fetchrow_hashref){
641 my @temp=split('-',$idata->{'date_due'});
642 $datedue = "$temp[2]/$temp[1]/$temp[0]";
644 if ($data->{'itemlost'} eq '1' || $data->{'itemlost'} eq '2'){
647 if ($data->{'wthdrawn'} eq '1'){
648 $datedue="Cancelled";
651 my ($rescount,$reserves)=Findgroupreserve($data->{'biblioitemnumber'},$biblionumber);
658 my $class = $data->{'classification'};
659 my $dewey = $data->{'dewey'};
661 if ($dewey eq "000.") { $dewey = "";};
662 if ($dewey < 10){$dewey='00'.$dewey;}
663 if ($dewey < 100 && $dewey > 10){$dewey='0'.$dewey;}
668 $class = $class.$dewey;
670 $class = $class.$data->{'subclass'};
672 # $results[$i]="$data->{'title'}\t$data->{'barcode'}\t$datedue\t$data->{'branchname'}\t$data->{'dewey'}";
673 my @temp=split('-',$data->{'datelastseen'});
674 my $date="$temp[2]/$temp[1]/$temp[0]";
675 $results[$i]="$data->{'title'}\t$data->{'barcode'}\t$datedue\t$data->{'branchname'}\t$class\t$data->{'itemnumber'}\t$data->{'itemtype'}\t$date\t$data->{'biblioitemnumber'}\t$data->{'volumeddesc'}";
676 # print "$results[$i] <br>";
680 my $query2="Select * from aqorders where biblionumber=$biblionumber";
681 my $sth2=$dbh->prepare($query2);
685 if ($data=$sth2->fetchrow_hashref){
686 $ocount=$data->{'quantity'} - $data->{'quantityreceived'};
688 $results[$i]="$data->{'title'}\t$data->{'barcode'}\t$ocount\tOn Order\t\t$data->{'itemnumber'}\t$data->{'itemtype'}\t\t$data->{'biblioitemnumber'}\t$data->{'volumeddesc'}";
698 my ($env,$biblionumber)=@_;
699 #debug_msg($env,"GetItems");
700 my $dbh = &C4Connect;
701 my $query = "Select * from biblioitems where (biblionumber = $biblionumber)";
702 #debug_msg($env,$query);
703 my $sth=$dbh->prepare($query);
705 #debug_msg($env,"executed query");
708 while (my $data=$sth->fetchrow_hashref) {
709 #debug_msg($env,$data->{'biblioitemnumber'});
710 my $dewey = $data->{'dewey'};
712 my $line = $data->{'biblioitemnumber'}."\t".$data->{'itemtype'};
713 $line = $line."\t$data->{'classification'}\t$dewey";
714 $line = $line."\t$data->{'subclass'}\t$data->{isbn}";
715 $line = $line."\t$data->{'volume'}\t$data->{number}";
716 my $isth= $dbh->prepare("select * from items where biblioitemnumber = $data->{'biblioitemnumber'}");
718 while (my $idata = $isth->fetchrow_hashref) {
719 my $iline = $idata->{'barcode'}."[".$idata->{'holdingbranch'}."[";
720 if ($idata->{'notforloan'} == 1) {
721 $iline = $iline."NFL ";
723 if ($idata->{'itemlost'} == 1) {
724 $iline = $iline."LOST ";
726 $line = $line."\t$iline";
729 $results[$i] = $line;
740 my $query="Select * from items,biblioitems where barcode='$barcode'
741 and items.biblioitemnumber=biblioitems.biblioitemnumber";
743 my $sth=$dbh->prepare($query);
745 my $data=$sth->fetchrow_hashref;
753 my ($bibnum, $type) = @_;
755 my $query = "Select *, biblio.notes
756 from biblio, biblioitems
757 left join bibliosubtitle on
758 biblio.biblionumber = bibliosubtitle.biblionumber
759 where biblio.biblionumber = $bibnum
760 and biblioitems.biblionumber = $bibnum";
761 my $sth = $dbh->prepare($query);
765 $data = $sth->fetchrow_hashref;
768 $query = "Select * from bibliosubject where biblionumber = '$bibnum'";
769 $sth = $dbh->prepare($query);
771 while (my $dat = $sth->fetchrow_hashref){
772 $data->{'subject'} .= " | $dat->{'subject'}";
784 my $query = "Select *,biblioitems.notes as bnotes from biblio, biblioitems,itemtypes
785 where biblio.biblionumber = biblioitems.biblionumber
786 and biblioitemnumber = $bibitem
787 and biblioitems.itemtype = itemtypes.itemtype";
788 my $sth = $dbh->prepare($query);
793 $data = $sth->fetchrow_hashref;
804 my $query="Select * from bibliosubject where biblionumber=$bibnum";
805 my $sth=$dbh->prepare($query);
809 while (my $data=$sth->fetchrow_hashref){
815 return($i,\@results);
821 my $query="Select * from additionalauthors where biblionumber=$bibnum";
822 my $sth=$dbh->prepare($query);
826 while (my $data=$sth->fetchrow_hashref){
832 return($i,\@results);
838 my $query="Select * from bibliosubtitle where biblionumber=$bibnum";
839 my $sth=$dbh->prepare($query);
843 while (my $data=$sth->fetchrow_hashref){
849 return($i,\@results);
855 my ($bibitem, $biblio)=@_;
857 my $query = "Select * from items where
858 items.biblioitemnumber = '$bibitem'";
859 my $sth = $dbh->prepare($query)
867 while (my $data = $sth->fetchrow_hashref) {
868 my $query2 = "select * from issues,borrowers
869 where itemnumber = $data->{'itemnumber'}
870 and returndate is NULL
871 and issues.borrowernumber = borrowers.borrowernumber";
872 my $sth2 = $dbh->prepare($query2);
875 if (my $data2 = $sth2->fetchrow_hashref) {
876 $data->{'date_due'} = $data2->{'date_due'};
877 $data->{'card'} = $data2->{'cardnumber'};
879 if ($data->{'wthdrawn'} eq '1') {
880 $data->{'date_due'} = 'Cancelled';
882 $data->{'date_due'} = 'Available';
887 $query2 = "select * from issues, borrowers
888 where itemnumber = '$data->{'itemnumber'}'
889 and issues.borrowernumber = borrowers.borrowernumber
890 order by date_due desc";
891 $sth2 = $dbh->prepare($query2)
894 || die $sth2->errstr;
896 for (my $i2 = 0; $i2 < 2; $i2++) {
897 if (my $data2 = $sth2->fetchrow_hashref) {
898 $data->{"timestamp$i2"} = $data2->{'timestamp'};
899 $data->{"card$i2"} = $data2->{'cardnumber'};
900 $data->{"borrower$i2"} = $data2->{'borrowernumber'};
905 $results[$i] = $data;
916 my ($env,$dbh,$itemnumber) = @_;
918 my $query="Select * from biblio,items,biblioitems
919 where items.itemnumber = '$itemnumber'
920 and biblio.biblionumber = items.biblionumber
921 and biblioitems.biblioitemnumber = items.biblioitemnumber";
922 my $sth=$dbh->prepare($query);
925 my $data=$sth->fetchrow_hashref;
931 #used by member enquiries from the intranet
934 my ($env,$searchstring,$type)=@_;
935 my $dbh = &C4Connect;
936 $searchstring=~ s/\'/\\\'/g;
937 my @data=split(' ',$searchstring);
939 my $query="Select * from borrowers
940 where ((surname like \"$data[0]%\" or surname like \"% $data[0]%\"
941 or firstname like \"$data[0]%\" or firstname like \"% $data[0]%\"
942 or othernames like \"$data[0]%\" or othernames like \"% $data[0]%\")
944 for (my $i=1;$i<$count;$i++){
945 $query=$query." and (surname like \"$data[$i]%\" or surname like \"% $data[$i]%\"
946 or firstname like \"$data[$i]%\" or firstname like \"% $data[$i]%\"
947 or othernames like \"$data[$i]%\" or othernames like \"% $data[$i]%\")";
949 $query=$query.") or cardnumber = \"$searchstring\"
950 order by surname,firstname";
952 my $sth=$dbh->prepare($query);
956 while (my $data=$sth->fetchrow_hashref){
957 push(@results,$data);
963 return ($cnt,\@results);
967 my ($cardnumber,$bornum)=@_;
968 $cardnumber = uc $cardnumber;
972 $query="Select * from borrowers where cardnumber='$cardnumber'";
974 $query="Select * from borrowers where borrowernumber='$bornum'";
977 my $sth=$dbh->prepare($query);
979 my $data=$sth->fetchrow_hashref;
989 $query="Select * from issues,biblio,items where borrowernumber='$bornum' and
990 items.itemnumber=issues.itemnumber and
991 items.biblionumber=biblio.biblionumber and issues.returndate is NULL order
994 my $sth=$dbh->prepare($query);
998 while (my $data=$sth->fetchrow_hashref){
1004 return($i,\@result);
1008 my ($bornum,$order,$limit)=@_;
1011 $query="Select * from issues,biblio,items,biblioitems
1012 where borrowernumber='$bornum' and
1013 items.biblioitemnumber=biblioitems.biblioitemnumber and
1014 items.itemnumber=issues.itemnumber and
1015 items.biblionumber=biblio.biblionumber";
1016 $query.=" order by $order";
1018 $query.=" limit $limit";
1021 my $sth=$dbh->prepare($query);
1025 while (my $data=$sth->fetchrow_hashref){
1031 return($i,\@result);
1035 my ($env,$bornum)=@_;
1037 my $query="Select count(*) from issues where borrowernumber='$bornum' and
1038 returndate is NULL";
1040 my $sth=$dbh->prepare($query);
1042 my $data=$sth->fetchrow_hashref;
1044 $sth=$dbh->prepare("Select count(*) from issues where
1045 borrowernumber='$bornum' and date_due < now() and returndate is NULL");
1047 my $data2=$sth->fetchrow_hashref;
1049 $sth=$dbh->prepare("Select sum(amountoutstanding) from accountlines where
1050 borrowernumber='$bornum'");
1052 my $data3=$sth->fetchrow_hashref;
1056 return($data2->{'count(*)'},$data->{'count(*)'},$data3->{'sum(amountoutstanding)'});
1060 sub getboracctrecord {
1061 my ($env,$params) = @_;
1065 my $query= "Select * from accountlines where
1066 borrowernumber=$params->{'borrowernumber'} order by date desc,timestamp desc";
1067 my $sth=$dbh->prepare($query);
1071 while (my $data=$sth->fetchrow_hashref){
1072 # if ($data->{'itemnumber'} ne ''){
1073 # $query="Select * from items,biblio where items.itemnumber=
1074 # '$data->{'itemnumber'}' and biblio.biblionumber=items.biblionumber";
1075 # my $sth2=$dbh->prepare($query);
1077 # my $data2=$sth2->fetchrow_hashref;
1081 $acctlines[$numlines] = $data;
1083 $total = $total+ $data->{'amountoutstanding'};
1087 return ($numlines,\@acctlines,$total);
1091 my ($env,$bibnum,$type)=@_;
1093 my $query="Select * from items where
1094 biblionumber=$bibnum ";
1095 if ($type ne 'intra'){
1096 $query.=" and ((itemlost <>1 and itemlost <> 2) or itemlost is NULL) and
1097 (wthdrawn <> 1 or wthdrawn is NULL)";
1099 my $sth=$dbh->prepare($query);
1111 while (my $data=$sth->fetchrow_hashref){
1113 my $query2="select * from issues,items where issues.itemnumber=
1114 '$data->{'itemnumber'}' and returndate is NULL
1115 and items.itemnumber=issues.itemnumber and ((items.itemlost <>1 and
1116 items.itemlost <> 2) or items.itemlost is NULL)
1117 and (wthdrawn <> 1 or wthdrawn is NULL)";
1119 my $sth2=$dbh->prepare($query2);
1121 if (my $data2=$sth2->fetchrow_hashref){
1124 if ($data->{'holdingbranch'} eq 'C'){
1127 if ($data->{'holdingbranch'} eq 'F' || $data->{'holdingbranch'} eq 'FP'){
1130 if ($data->{'holdingbranch'} eq 'S' || $data->{'holdingbranch'} eq 'SP'){
1133 if ($data->{'itemlost'} eq '1'){
1136 if ($data->{'itemlost'} eq '2'){
1139 if ($data->{'holdingbranch'} eq 'FM'){
1142 if ($data->{'holdingbranch'} eq 'TR'){
1149 my $query2="Select * from aqorders where biblionumber=$bibnum";
1150 my $sth2=$dbh->prepare($query2);
1152 if (my $data=$sth2->fetchrow_hashref){
1153 $ocount=$data->{'quantity'} - $data->{'quantityreceived'};
1159 return ($count,$lcount,$nacount,$fcount,$scount,$lostcount,$mending,$transit,$ocount);
1165 my $query="select description from itemtypes where itemtype='$type'";
1166 my $sth=$dbh->prepare($query);
1168 my $dat=$sth->fetchrow_hashref;
1171 return ($dat->{'description'});
1177 my $dbh = C4Connect;
1178 my $query = "Select * from biblioitems, itemtypes, items
1179 where biblioitems.biblionumber = '$bibnum'
1180 and biblioitems.itemtype = itemtypes.itemtype
1181 and biblioitems.biblioitemnumber = items.biblioitemnumber
1182 group by items.biblioitemnumber";
1183 my $sth = $dbh->prepare($query);
1189 while (my $data = $sth->fetchrow_hashref) {
1190 $results[$count] = $data;
1196 return($count, @results);
1201 #called from request.pl
1202 my ($biblioitemnumber)=@_;
1204 my $query="Select barcode from items where
1205 biblioitemnumber='$biblioitemnumber'
1206 and ((itemlost <> 1 and itemlost <> 2) or itemlost is NULL) and
1207 (wthdrawn <> 1 or wthdrawn is NULL)";
1209 my $sth=$dbh->prepare($query);
1213 while (my $data=$sth->fetchrow_hashref){
1214 $barcodes[$i]=$data->{'barcode'};
1225 my ($biblionumber) = @_;
1226 my $dbh = C4Connect;
1227 my $query = "Select * from websites where biblionumber = $biblionumber";
1228 my $sth = $dbh->prepare($query);
1233 while (my $data = $sth->fetchrow_hashref) {
1234 $results[$count] = $data;
1240 return($count, @results);
1244 sub getwebbiblioitems {
1245 my ($biblionumber) = @_;
1246 my $dbh = C4Connect;
1247 my $query = "Select * from biblioitems where biblionumber = $biblionumber
1248 and itemtype = 'WEB'";
1249 my $sth = $dbh->prepare($query);
1254 while (my $data = $sth->fetchrow_hashref) {
1255 $results[$count] = $data;
1261 return($count, @results);
1262 } # sub getwebbiblioitems
1265 END { } # module clean-up code here (global destructor)
1269 C4::Search - Module that provides Catalog searching for Koha
1274 my ($count,@results)=catalogsearch($env,$type,$search,$num,$offset);
1278 This module provides the searching facilities for the Catalog.
1279 Here I should go through and document each function thats exported and what it does. But I havent yet.
1281 my ($count,@results)=catalogsearch($env,$type,$search,$num,$offset);
1282 This is a front end to all the other searches, depending on what is passed
1283 to it, it calls the appropriate search
1291 Koha Developement team <info@koha.org>