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);
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)'});
121 my ($env,$type,$search,$num,$offset)=@_;
122 my $dbh = &C4Connect;
123 $search->{'keyword'}=~ s/ +$//;
124 $search->{'keyword'}=~ s/'/\\'/;
125 my @key=split(' ',$search->{'keyword'});
129 my $query="Select biblionumber from biblio
130 where ((title like '$key[0]%' or title like '% $key[0]%')";
132 $query=$query." and (title like '$key[$i]%' or title like '% $key[$i]%')";
135 $query.= ") or ((biblio.notes like '$key[0]%' or biblio.notes like '% $key[0]%')";
136 for ($i=1;$i<$count;$i++){
137 $query.=" and (biblio.notes like '$key[$i]%' or biblio.notes like '% $key[$i]%')";
139 $query.= ") or ((seriestitle like '$key[0]%' or seriestitle like '% $key[0]%')";
140 for ($i=1;$i<$count;$i++){
141 $query.=" and (seriestitle like '$key[$i]%' or seriestitle like '% $key[$i]%')";
145 my $sth=$dbh->prepare($query);
148 while (my @res=$sth->fetchrow_array){
149 $results[$i]=$res[0];
153 my $set1=Set::Scalar->new(@results);
154 $query="Select biblionumber from bibliosubtitle where
155 ((subtitle like '$key[0]%' or subtitle like '% $key[0]%')";
156 for ($i=1;$i<$count;$i++){
157 $query.= " and (subtitle like '$key[$i]%' or subtitle like '% $key[$i]%')";
161 $sth=$dbh->prepare($query);
164 while (my @res=$sth->fetchrow_array){
165 $results[$i]=$res[0];
169 my $set2=Set::Scalar->new(@results);
173 $query ="Select biblionumber from biblioitems where
174 ((biblioitems.notes like '$key[0]%' or biblioitems.notes like '% $key[0]%')";
175 for ($i=1;$i<$count;$i++){
176 $query.=" and (biblioitems.notes like '$key[$i]%' or biblioitems.notes like '% $key[$i]%')";
180 $sth=$dbh->prepare($query);
183 while (my @res=$sth->fetchrow_array){
184 $results[$i]=$res[0];
188 my $set3=Set::Scalar->new(@results);
192 $sth=$dbh->prepare("Select biblionumber from bibliosubject where subject
193 like '%$search->{'keyword'}%' group by biblionumber");
196 while (my @res=$sth->fetchrow_array){
197 $results[$i]=$res[0];
201 my $set4=Set::Scalar->new(@results);
210 my @res = $set1->members;
214 # print "count $count";
215 if ($search->{'class'} ne ''){
217 my $query="select * from biblio,biblioitems where
218 biblio.biblionumber='$res[$i2]' and
219 biblio.biblionumber=biblioitems.biblionumber ";
220 if ($search->{'class'} ne ''){
221 my @temp=split(/\|/,$search->{'class'});
223 $query.= "and ( itemtype='$temp[0]'";
224 for (my $i=1;$i<$count;$i++){
225 $query.=" or itemtype='$temp[$i]'";
229 my $sth=$dbh->prepare($query);
232 if (my $data2=$sth->fetchrow_hashref){
233 my $dewey= $data2->{'dewey'};
234 my $subclass=$data2->{'subclass'};
236 ($dewey == 0) && ($dewey='');
237 ($dewey) && ($dewey.=" $subclass") ;
239 my $end=$offset +$num;
244 if ($i4 <=$end && $i4 > $offset){
245 $data2->{'dewey'}=$dewey;
248 # $res2[$i3]="$data2->{'author'}\t$data2->{'title'}\t$data2->{'biblionumber'}\t$data2->{'copyrightdate'}\t$dewey";
251 # print "in here $i3<br>";
262 while ($i2 < $num && $i2 < $count){
263 my $query="select * from biblio,biblioitems where
264 biblio.biblionumber='$res[$i2+$offset]' and
265 biblio.biblionumber=biblioitems.biblionumber ";
266 if ($search->{'class'} ne ''){
267 my @temp=split(/\|/,$search->{'class'});
269 $query.= "and ( itemtype='$temp[0]'";
270 for (my $i=1;$i<$count;$i++){
271 $query.=" or itemtype='$temp[$i]'";
275 if ($search->{'dewey'} ne ''){
276 $query.= "and (dewey like '$search->{'dewey'}%') ";
279 my $sth=$dbh->prepare($query);
282 if (my $data2=$sth->fetchrow_hashref){
283 my $dewey= $data2->{'dewey'};
284 my $subclass=$data2->{'subclass'};
286 ($dewey == 0) && ($dewey='');
287 ($dewey) && ($dewey.=" $subclass") ;
289 $data2->{'dewey'}=$dewey;
290 if ($env->{itemcount}) {
291 my ($count, $lcount, $nacount, $fcount, $scount, $lostcount, $mending, $transit, $ocount) = itemcount($env, $data2->{'biblionumber'}, 'intra');
292 $data2->{'itemcount'}=$count;
295 # $res2[$i]="$data2->{'author'}\t$data2->{'title'}\t$data2->{'biblionumber'}\t$data2->{'copyrightdate'}\t$dewey";
305 return($count,@res2);
309 my ($env,$type,$search,$num,$offset)=@_;
310 my $dbh = &C4Connect;
313 $search->{'title'}=~ s/'/\\'/g;
314 $search->{'author'}=~ s/'/\\'/g;
315 $search->{'illustrator'}=~ s/'/\\'/g;
316 my $title = lc($search->{'title'});
318 if ($type eq 'loose') {
319 if ($search->{'author'} ne ''){
320 my @key=split(' ',$search->{'author'});
323 $query="select *,biblio.author,biblio.biblionumber from
325 left join additionalauthors
326 on additionalauthors.biblionumber =biblio.biblionumber
328 ((biblio.author like '$key[0]%' or biblio.author like '% $key[0]%' or
329 additionalauthors.author like '$key[0]%' or additionalauthors.author
333 $query=$query." and (
334 biblio.author like '$key[$i]%' or biblio.author like '% $key[$i]%' or
335 additionalauthors.author like '$key[$i]%' or additionalauthors.author like '% $key[$i]%'
340 if ($search->{'title'} ne ''){
341 my @key=split(' ',$search->{'title'});
344 $query.= " and (((title like '$key[0]%' or title like '% $key[0]%' or title like '% $key[0]')";
346 $query=$query." and (title like '$key[$i]%' or title like '% $key[$i]%' or title like '% $key[$i]')";
349 # $query.=") or ((subtitle like '$key[0]%' or subtitle like '% $key[0] %' or subtitle like '% $key[0]')";
350 # for ($i=1;$i<$count;$i++){
351 # $query.=" and (subtitle like '$key[$i]%' or subtitle like '% $key[$i] %' or subtitle like '% $key[$i]')";
353 $query.=") or ((seriestitle like '$key[0]%' or seriestitle like '% $key[0]%' or seriestitle like '% $key[0]')";
354 for ($i=1;$i<$count;$i++){
355 $query.=" and (seriestitle like '$key[$i]%' or seriestitle like '% $key[$i]%')";
357 $query.=") or ((unititle like '$key[0]%' or unititle like '% $key[0]%' or unititle like '% $key[0]')";
358 for ($i=1;$i<$count;$i++){
359 $query.=" and (unititle like '$key[$i]%' or unititle like '% $key[$i]%')";
362 #$query=$query. " and (title like '%$search->{'title'}%'
363 #or seriestitle like '%$search->{'title'}%')";
366 $query.=" group by biblio.biblionumber";
368 if ($search->{'title'} ne '') {
369 if ($search->{'ttype'} eq 'exact'){
370 $query="select * from biblio
372 (biblio.title='$search->{'title'}' or (biblio.unititle = '$search->{'title'}'
373 or biblio.unititle like '$search->{'title'} |%' or
374 biblio.unititle like '%| $search->{'title'} |%' or
375 biblio.unititle like '%| $search->{'title'}') or
376 (biblio.seriestitle = '$search->{'title'}' or
377 biblio.seriestitle like '$search->{'title'} |%' or
378 biblio.seriestitle like '%| $search->{'title'} |%' or
379 biblio.seriestitle like '%| $search->{'title'}')
382 my @key=split(' ',$search->{'title'});
385 $query="select * from biblio
386 left join bibliosubtitle on
387 biblio.biblionumber=bibliosubtitle.biblionumber
389 (((title like '$key[0]%' or title like '% $key[0]%' or title like '% $key[0]')";
391 $query=$query." and (title like '$key[$i]%' or title like '% $key[$i]%' or title like '% $key[$i]')";
394 $query.=") or ((subtitle like '$key[0]%' or subtitle like '% $key[0]%' or subtitle like '% $key[0]')";
395 for ($i=1;$i<$count;$i++){
396 $query.=" and (subtitle like '$key[$i]%' or subtitle like '% $key[$i]%' or subtitle like '% $key[$i]')";
398 $query.=") or ((seriestitle like '$key[0]%' or seriestitle like '% $key[0]%' or seriestitle like '% $key[0]')";
399 for ($i=1;$i<$count;$i++){
400 $query.=" and (seriestitle like '$key[$i]%' or seriestitle like '% $key[$i]%')";
402 $query.=") or ((unititle like '$key[0]%' or unititle like '% $key[0]%' or unititle like '% $key[0]')";
403 for ($i=1;$i<$count;$i++){
404 $query.=" and (unititle like '$key[$i]%' or unititle like '% $key[$i]%')";
408 } elsif ($search->{'class'} ne ''){
409 $query="select * from biblioitems,biblio where biblio.biblionumber=biblioitems.biblionumber";
410 my @temp=split(/\|/,$search->{'class'});
412 $query.= " and ( itemtype='$temp[0]'";
413 for (my $i=1;$i<$count;$i++){
414 $query.=" or itemtype='$temp[$i]'";
417 if ($search->{'illustrator'} ne ''){
418 $query.=" and illus like '%".$search->{'illustrator'}."%' ";
420 if ($search->{'dewey'} ne ''){
421 $query.=" and biblioitems.dewey like '$search->{'dewey'}%'";
423 } elsif ($search->{'dewey'} ne ''){
424 $query="select * from biblioitems,biblio
425 where biblio.biblionumber=biblioitems.biblionumber
426 and biblioitems.dewey like '$search->{'dewey'}%'";
427 } elsif ($search->{'illustrator'} ne '') {
428 if ($search->{'illustrator'} ne ''){
429 $query="select * from biblioitems,biblio
430 where biblio.biblionumber=biblioitems.biblionumber
431 and biblioitems.illus like '%".$search->{'illustrator'}."%'";
434 $query .=" group by biblio.biblionumber";
437 if ($type eq 'subject'){
438 my @key=split(' ',$search->{'subject'});
441 $query="select distinct(subject) from bibliosubject where( subject like
442 '$key[0]%' or subject like '% $key[0]%' or subject like '% $key[0]' or subject like '%($key[0])%')";
444 $query.=" and (subject like '$key[$i]%' or subject like '% $key[$i]%'
445 or subject like '% $key[$i]'
446 or subject like '%($key[$i])%')";
449 if ($search->{'subject'} eq 'NZ' || $search->{'subject'} eq 'nz'){
450 $query.= " or (subject like 'NEW ZEALAND %' or subject like '% NEW ZEALAND %'
451 or subject like '% NEW ZEALAND' or subject like '%(NEW ZEALAND)%' ) ";
452 } elsif ( $search->{'subject'} =~ /^nz /i || $search->{'subject'} =~ / nz /i || $search->{'subject'} =~ / nz$/i){
453 $query=~ s/ nz/ NEW ZEALAND/ig;
454 $query=~ s/nz /NEW ZEALAND /ig;
455 $query=~ s/\(nz\)/\(NEW ZEALAND\)/gi;
458 if ($type eq 'precise'){
459 $query="select * from items,biblio ";
460 if ($search->{'item'} ne ''){
461 my $search2=uc $search->{'item'};
462 $query=$query." where
463 items.biblionumber=biblio.biblionumber
464 and barcode='$search2'";
466 if ($search->{'isbn'} ne ''){
467 my $search2=uc $search->{'isbn'};
468 my $query1 = "select * from biblioitems where isbn='$search2'";
469 my $sth1=$dbh->prepare($query1);
473 while (my $data=$sth1->fetchrow_hashref) {
474 $query="select * from biblioitems,biblio where
475 biblio.biblionumber = $data->{'biblionumber'}
476 and biblioitems.biblionumber = biblio.biblionumber";
477 my $sth=$dbh->prepare($query);
479 my $data=$sth->fetchrow_hashref;
480 my ($dewey, $subclass) = ($data->{'dewey'}, $data->{'subclass'});
482 ($dewey == 0) && ($dewey='');
483 ($dewey) && ($dewey.=" $subclass");
484 $data->{'dewey'}=$dewey;
486 # $results[$i2]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey\t$data->{'isbn'}\t$data->{'itemtype'}";
494 if ($type ne 'precise' && $type ne 'subject'){
495 if ($search->{'author'} ne ''){
496 $query=$query." order by biblio.author,title";
498 $query=$query." order by title";
501 if ($type eq 'subject'){
502 $query=$query." order by subject";
506 my $sth=$dbh->prepare($query);
510 my $limit= $num+$offset;
511 while (my $data=$sth->fetchrow_hashref){
512 my $query="select dewey,subclass from biblioitems where biblionumber=$data->{'biblionumber'}";
513 if ($search->{'class'} ne ''){
514 my @temp=split(/\|/,$search->{'class'});
516 $query.= " and ( itemtype='$temp[0]'";
517 for (my $i=1;$i<$count;$i++){
518 $query.=" or itemtype='$temp[$i]'";
522 if ($search->{'dewey'} ne ''){
523 $query.=" and dewey='$search->{'dewey'}' ";
525 if ($search->{'illustrator'} ne ''){
526 $query.=" and illus like '%".$search->{'illustrator'}."%' ";
529 my $sti=$dbh->prepare($query);
534 if (($dewey, $subclass) = $sti->fetchrow || $type eq 'subject'){
538 ($dewey == 0) && ($dewey='');
539 ($dewey) && ($dewey.=" $subclass");
540 $data->{'dewey'}=$dewey;
543 if ($count > $offset && $count <= $limit){
544 # if ($type ne 'subject' && $type ne 'precise'){
545 # $results[$i]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey\t$data->{'illus'}";
546 # } elsif ($search->{'isbn'} ne '' || $search->{'item'} ne ''){
547 # $results[$i]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey\t$data->{'illus'}";
549 # $results[$i]="$data->{'author'}\t$data->{'subject'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey\t$data->{'illus'}";
558 #if ($type ne 'precise'){
562 return($count,@results);
565 sub updatesearchstats{
571 my ($env,$subject)=@_;
573 $subject=$dbh->quote($subject);
574 my $query="Select * from biblio,bibliosubject where
575 biblio.biblionumber=bibliosubject.biblionumber and
576 bibliosubject.subject=$subject group by biblio.biblionumber
577 order by biblio.title";
578 my $sth=$dbh->prepare($query);
583 while (my $data=$sth->fetchrow_hashref){
584 $results[$i]="$data->{'title'}\t$data->{'author'}\t$data->{'biblionumber'}";
594 my ($env,$biblionumber,$type) = @_;
595 my $dbh = &C4Connect;
596 my $query = "Select * from items, biblio, biblioitems, branches
597 where (items.biblioitemnumber = biblioitems.biblioitemnumber)
598 and biblioitems.biblionumber = biblio.biblionumber
599 and biblio.biblionumber = '$biblionumber'
600 and branches.branchcode = items.holdingbranch";
602 if ($type ne 'intra'){
603 $query .= " and ((items.itemlost<>1 and items.itemlost <> 2)
604 or items.itemlost is NULL)
605 and (wthdrawn <> 1 or wthdrawn is NULL)";
607 $query .= " order by items.dateaccessioned desc";
608 my $sth=$dbh->prepare($query);
613 while (my $data=$sth->fetchrow_hashref){
614 my $iquery = "Select * from issues
615 where itemnumber = '$data->{'itemnumber'}'
616 and returndate is null";
618 my $isth=$dbh->prepare($iquery);
620 if (my $idata=$isth->fetchrow_hashref){
621 my @temp=split('-',$idata->{'date_due'});
622 $datedue = "$temp[2]/$temp[1]/$temp[0]";
624 if ($data->{'itemlost'} eq '1' || $data->{'itemlost'} eq '2'){
627 if ($data->{'wthdrawn'} eq '1'){
628 $datedue="Cancelled";
631 my ($rescount,$reserves)=Findgroupreserve($data->{'biblioitemnumber'},$biblionumber);
638 my $class = $data->{'classification'};
639 my $dewey = $data->{'dewey'};
641 if ($dewey eq "000.") { $dewey = "";};
642 if ($dewey < 10){$dewey='00'.$dewey;}
643 if ($dewey < 100 && $dewey > 10){$dewey='0'.$dewey;}
648 $class = $class.$dewey;
650 $class = $class.$data->{'subclass'};
652 # $results[$i]="$data->{'title'}\t$data->{'barcode'}\t$datedue\t$data->{'branchname'}\t$data->{'dewey'}";
653 my @temp=split('-',$data->{'datelastseen'});
654 my $date="$temp[2]/$temp[1]/$temp[0]";
655 $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'}";
656 # print "$results[$i] <br>";
660 my $query2="Select * from aqorders where biblionumber=$biblionumber";
661 my $sth2=$dbh->prepare($query2);
665 if ($data=$sth2->fetchrow_hashref){
666 $ocount=$data->{'quantity'} - $data->{'quantityreceived'};
668 $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'}";
678 my ($env,$biblionumber)=@_;
679 #debug_msg($env,"GetItems");
680 my $dbh = &C4Connect;
681 my $query = "Select * from biblioitems where (biblionumber = $biblionumber)";
682 #debug_msg($env,$query);
683 my $sth=$dbh->prepare($query);
685 #debug_msg($env,"executed query");
688 while (my $data=$sth->fetchrow_hashref) {
689 #debug_msg($env,$data->{'biblioitemnumber'});
690 my $dewey = $data->{'dewey'};
692 my $line = $data->{'biblioitemnumber'}."\t".$data->{'itemtype'};
693 $line = $line."\t$data->{'classification'}\t$dewey";
694 $line = $line."\t$data->{'subclass'}\t$data->{isbn}";
695 $line = $line."\t$data->{'volume'}\t$data->{number}";
696 my $isth= $dbh->prepare("select * from items where biblioitemnumber = $data->{'biblioitemnumber'}");
698 while (my $idata = $isth->fetchrow_hashref) {
699 my $iline = $idata->{'barcode'}."[".$idata->{'holdingbranch'}."[";
700 if ($idata->{'notforloan'} == 1) {
701 $iline = $iline."NFL ";
703 if ($idata->{'itemlost'} == 1) {
704 $iline = $iline."LOST ";
706 $line = $line."\t$iline";
709 $results[$i] = $line;
720 my $query="Select * from items,biblioitems where barcode='$barcode'
721 and items.biblioitemnumber=biblioitems.biblioitemnumber";
723 my $sth=$dbh->prepare($query);
725 my $data=$sth->fetchrow_hashref;
733 my ($bibnum, $type) = @_;
735 my $query = "Select *, biblio.notes
736 from biblio, biblioitems
737 left join bibliosubtitle on
738 biblio.biblionumber = bibliosubtitle.biblionumber
739 where biblio.biblionumber = $bibnum
740 and biblioitems.biblionumber = $bibnum";
741 my $sth = $dbh->prepare($query);
745 $data = $sth->fetchrow_hashref;
748 $query = "Select * from bibliosubject where biblionumber = '$bibnum'";
749 $sth = $dbh->prepare($query);
751 while (my $dat = $sth->fetchrow_hashref){
752 $data->{'subject'} .= " | $dat->{'subject'}";
764 my $query = "Select *,biblioitems.notes as bnotes from biblio, biblioitems,itemtypes
765 where biblio.biblionumber = biblioitems.biblionumber
766 and biblioitemnumber = $bibitem
767 and biblioitems.itemtype = itemtypes.itemtype";
768 my $sth = $dbh->prepare($query);
773 $data = $sth->fetchrow_hashref;
784 my $query="Select * from bibliosubject where biblionumber=$bibnum";
785 my $sth=$dbh->prepare($query);
789 while (my $data=$sth->fetchrow_hashref){
795 return($i,\@results);
801 my $query="Select * from additionalauthors where biblionumber=$bibnum";
802 my $sth=$dbh->prepare($query);
806 while (my $data=$sth->fetchrow_hashref){
812 return($i,\@results);
818 my $query="Select * from bibliosubtitle where biblionumber=$bibnum";
819 my $sth=$dbh->prepare($query);
823 while (my $data=$sth->fetchrow_hashref){
829 return($i,\@results);
835 my ($bibitem, $biblio)=@_;
837 my $query = "Select * from items where
838 items.biblioitemnumber = '$bibitem'";
839 my $sth = $dbh->prepare($query)
847 while (my $data = $sth->fetchrow_hashref) {
848 my $query2 = "select * from issues,borrowers
849 where itemnumber = $data->{'itemnumber'}
850 and returndate is NULL
851 and issues.borrowernumber = borrowers.borrowernumber";
852 my $sth2 = $dbh->prepare($query2);
855 if (my $data2 = $sth2->fetchrow_hashref) {
856 $data->{'date_due'} = $data2->{'date_due'};
857 $data->{'card'} = $data2->{'cardnumber'};
859 if ($data->{'wthdrawn'} eq '1') {
860 $data->{'date_due'} = 'Cancelled';
862 $data->{'date_due'} = 'Available';
867 $query2 = "select * from issues, borrowers
868 where itemnumber = '$data->{'itemnumber'}'
869 and issues.borrowernumber = borrowers.borrowernumber
870 order by date_due desc";
871 $sth2 = $dbh->prepare($query2)
874 || die $sth2->errstr;
876 for (my $i2 = 0; $i2 < 2; $i2++) {
877 if (my $data2 = $sth2->fetchrow_hashref) {
878 $data->{"timestamp$i2"} = $data2->{'timestamp'};
879 $data->{"card$i2"} = $data2->{'cardnumber'};
880 $data->{"borrower$i2"} = $data2->{'borrowernumber'};
885 $results[$i] = $data;
896 my ($env,$dbh,$itemnumber) = @_;
898 my $query="Select * from biblio,items,biblioitems
899 where items.itemnumber = '$itemnumber'
900 and biblio.biblionumber = items.biblionumber
901 and biblioitems.biblioitemnumber = items.biblioitemnumber";
902 my $sth=$dbh->prepare($query);
905 my $data=$sth->fetchrow_hashref;
911 #used by member enquiries from the intranet
914 my ($env,$searchstring,$type)=@_;
915 my $dbh = &C4Connect;
916 $searchstring=~ s/\'/\\\'/g;
917 my @data=split(' ',$searchstring);
919 my $query="Select * from borrowers
920 where ((surname like \"$data[0]%\" or surname like \"% $data[0]%\"
921 or firstname like \"$data[0]%\" or firstname like \"% $data[0]%\"
922 or othernames like \"$data[0]%\" or othernames like \"% $data[0]%\")
924 for (my $i=1;$i<$count;$i++){
925 $query=$query." and (surname like \"$data[$i]%\" or surname like \"% $data[$i]%\"
926 or firstname like \"$data[$i]%\" or firstname like \"% $data[$i]%\"
927 or othernames like \"$data[$i]%\" or othernames like \"% $data[$i]%\")";
929 $query=$query.") or cardnumber = \"$searchstring\"
930 order by surname,firstname";
932 my $sth=$dbh->prepare($query);
936 while (my $data=$sth->fetchrow_hashref){
937 push(@results,$data);
943 return ($cnt,\@results);
947 my ($cardnumber,$bornum)=@_;
948 $cardnumber = uc $cardnumber;
952 $query="Select * from borrowers where cardnumber='$cardnumber'";
954 $query="Select * from borrowers where borrowernumber='$bornum'";
957 my $sth=$dbh->prepare($query);
959 my $data=$sth->fetchrow_hashref;
969 $query="Select * from issues,biblio,items where borrowernumber='$bornum' and
970 items.itemnumber=issues.itemnumber and
971 items.biblionumber=biblio.biblionumber and issues.returndate is NULL order
974 my $sth=$dbh->prepare($query);
978 while (my $data=$sth->fetchrow_hashref){
988 my ($bornum,$order,$limit)=@_;
991 $query="Select * from issues,biblio,items,biblioitems
992 where borrowernumber='$bornum' and
993 items.biblioitemnumber=biblioitems.biblioitemnumber and
994 items.itemnumber=issues.itemnumber and
995 items.biblionumber=biblio.biblionumber";
996 $query.=" order by $order";
998 $query.=" limit $limit";
1001 my $sth=$dbh->prepare($query);
1005 while (my $data=$sth->fetchrow_hashref){
1011 return($i,\@result);
1015 my ($env,$bornum)=@_;
1017 my $query="Select count(*) from issues where borrowernumber='$bornum' and
1018 returndate is NULL";
1020 my $sth=$dbh->prepare($query);
1022 my $data=$sth->fetchrow_hashref;
1024 $sth=$dbh->prepare("Select count(*) from issues where
1025 borrowernumber='$bornum' and date_due < now() and returndate is NULL");
1027 my $data2=$sth->fetchrow_hashref;
1029 $sth=$dbh->prepare("Select sum(amountoutstanding) from accountlines where
1030 borrowernumber='$bornum'");
1032 my $data3=$sth->fetchrow_hashref;
1036 return($data2->{'count(*)'},$data->{'count(*)'},$data3->{'sum(amountoutstanding)'});
1040 sub getboracctrecord {
1041 my ($env,$params) = @_;
1045 my $query= "Select * from accountlines where
1046 borrowernumber=$params->{'borrowernumber'} order by date desc,timestamp desc";
1047 my $sth=$dbh->prepare($query);
1051 while (my $data=$sth->fetchrow_hashref){
1052 # if ($data->{'itemnumber'} ne ''){
1053 # $query="Select * from items,biblio where items.itemnumber=
1054 # '$data->{'itemnumber'}' and biblio.biblionumber=items.biblionumber";
1055 # my $sth2=$dbh->prepare($query);
1057 # my $data2=$sth2->fetchrow_hashref;
1061 $acctlines[$numlines] = $data;
1063 $total = $total+ $data->{'amountoutstanding'};
1067 return ($numlines,\@acctlines,$total);
1071 my ($env,$bibnum,$type)=@_;
1073 my $query="Select * from items where
1074 biblionumber=$bibnum ";
1075 if ($type ne 'intra'){
1076 $query.=" and ((itemlost <>1 and itemlost <> 2) or itemlost is NULL) and
1077 (wthdrawn <> 1 or wthdrawn is NULL)";
1079 my $sth=$dbh->prepare($query);
1091 while (my $data=$sth->fetchrow_hashref){
1093 my $query2="select * from issues,items where issues.itemnumber=
1094 '$data->{'itemnumber'}' and returndate is NULL
1095 and items.itemnumber=issues.itemnumber and ((items.itemlost <>1 and
1096 items.itemlost <> 2) or items.itemlost is NULL)
1097 and (wthdrawn <> 1 or wthdrawn is NULL)";
1099 my $sth2=$dbh->prepare($query2);
1101 if (my $data2=$sth2->fetchrow_hashref){
1104 if ($data->{'holdingbranch'} eq 'C'){
1107 if ($data->{'holdingbranch'} eq 'F' || $data->{'holdingbranch'} eq 'FP'){
1110 if ($data->{'holdingbranch'} eq 'S' || $data->{'holdingbranch'} eq 'SP'){
1113 if ($data->{'itemlost'} eq '1'){
1116 if ($data->{'itemlost'} eq '2'){
1119 if ($data->{'holdingbranch'} eq 'FM'){
1122 if ($data->{'holdingbranch'} eq 'TR'){
1129 my $query2="Select * from aqorders where biblionumber=$bibnum";
1130 my $sth2=$dbh->prepare($query2);
1132 if (my $data=$sth2->fetchrow_hashref){
1133 $ocount=$data->{'quantity'} - $data->{'quantityreceived'};
1139 return ($count,$lcount,$nacount,$fcount,$scount,$lostcount,$mending,$transit,$ocount);
1145 my $query="select description from itemtypes where itemtype='$type'";
1146 my $sth=$dbh->prepare($query);
1148 my $dat=$sth->fetchrow_hashref;
1151 return ($dat->{'description'});
1157 my $dbh = C4Connect;
1158 my $query = "Select * from biblioitems, itemtypes, items
1159 where biblioitems.biblionumber = '$bibnum'
1160 and biblioitems.itemtype = itemtypes.itemtype
1161 and biblioitems.biblioitemnumber = items.biblioitemnumber
1162 group by items.biblioitemnumber";
1163 my $sth = $dbh->prepare($query);
1169 while (my $data = $sth->fetchrow_hashref) {
1170 $results[$count] = $data;
1176 return($count, @results);
1181 #called from request.pl
1182 my ($biblioitemnumber)=@_;
1184 my $query="Select barcode from items where
1185 biblioitemnumber='$biblioitemnumber'
1186 and ((itemlost <> 1 and itemlost <> 2) or itemlost is NULL) and
1187 (wthdrawn <> 1 or wthdrawn is NULL)";
1189 my $sth=$dbh->prepare($query);
1193 while (my $data=$sth->fetchrow_hashref){
1194 $barcodes[$i]=$data->{'barcode'};
1205 my ($biblionumber) = @_;
1206 my $dbh = C4Connect;
1207 my $query = "Select * from websites where biblionumber = $biblionumber";
1208 my $sth = $dbh->prepare($query);
1213 while (my $data = $sth->fetchrow_hashref) {
1214 $results[$count] = $data;
1220 return($count, @results);
1224 sub getwebbiblioitems {
1225 my ($biblionumber) = @_;
1226 my $dbh = C4Connect;
1227 my $query = "Select * from biblioitems where biblionumber = $biblionumber
1228 and itemtype = 'WEB'";
1229 my $sth = $dbh->prepare($query);
1234 while (my $data = $sth->fetchrow_hashref) {
1235 $results[$count] = $data;
1241 return($count, @results);
1242 } # sub getwebbiblioitems
1245 END { } # module clean-up code here (global destructor)
1249 C4::Search - Module that provides Catalog searching for Koha
1254 my ($count,@results)=KeywordSearch($env,$type,$search,$num,$offset);
1255 my ($count,@results)=CatSearch($env,$type,$search,$num,$offset);
1259 This module provides the searching facilities for the Catalog.
1260 Here I should go through and document each function thats exported and what it does. But I havent yet.
1270 Koha Developement team <info@koha.org>