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
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;
124 $search->{'keyword'}=~ s/ +$//;
125 $search->{'keyword'}=~ s/'/\\'/;
126 my @key=split(' ',$search->{'keyword'});
130 my $query="Select biblionumber from biblio
131 where ((title like '$key[0]%' or title like '% $key[0]%')";
133 $query=$query." and (title like '$key[$i]%' or title like '% $key[$i]%')";
136 $query.= ") or ((biblio.notes like '$key[0]%' or biblio.notes like '% $key[0]%')";
137 for ($i=1;$i<$count;$i++){
138 $query.=" and (biblio.notes like '$key[$i]%' or biblio.notes like '% $key[$i]%')";
140 $query.= ") or ((seriestitle like '$key[0]%' or seriestitle like '% $key[0]%')";
141 for ($i=1;$i<$count;$i++){
142 $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]%')";
160 $sth=$dbh->prepare($query);
163 while (my @res=$sth->fetchrow_array){
164 $results[$i]=$res[0];
168 my $set2=Set::Scalar->new(@results);
172 $query ="Select biblionumber from biblioitems where
173 ((biblioitems.notes like '$key[0]%' or biblioitems.notes like '% $key[0]%')";
174 for ($i=1;$i<$count;$i++){
175 $query.=" and (biblioitems.notes like '$key[$i]%' or biblioitems.notes like '% $key[$i]%')";
178 $sth=$dbh->prepare($query);
181 while (my @res=$sth->fetchrow_array){
182 $results[$i]=$res[0];
186 my $set3=Set::Scalar->new(@results);
190 $query="Select biblionumber from bibliosubject where subject like '%$search->{'keyword'}%' group by biblionumber";
191 $sth=$dbh->prepare($query);
194 while (my @res=$sth->fetchrow_array){
195 $results[$i]=$res[0];
199 my $set4=Set::Scalar->new(@results);
208 my @res = $set1->members;
212 # print "count $count";
213 if ($search->{'class'} ne ''){
215 my $query="select * from biblio,biblioitems where
216 biblio.biblionumber='$res[$i2]' and
217 biblio.biblionumber=biblioitems.biblionumber ";
218 if ($search->{'class'} ne ''){
219 my @temp=split(/\|/,$search->{'class'});
221 $query.= "and ( itemtype='$temp[0]'";
222 for (my $i=1;$i<$count;$i++){
223 $query.=" or itemtype='$temp[$i]'";
227 my $sth=$dbh->prepare($query);
229 if (my $data2=$sth->fetchrow_hashref){
230 my $dewey= $data2->{'dewey'};
231 my $subclass=$data2->{'subclass'};
233 ($dewey == 0) && ($dewey='');
234 ($dewey) && ($dewey.=" $subclass") ;
236 my $end=$offset +$num;
241 if ($i4 <=$end && $i4 > $offset){
242 $res2[$i3]="$data2->{'author'}\t$data2->{'title'}\t$data2->{'biblionumber'}\t$data2->{'copyrightdate'}\t$dewey";
245 # print "in here $i3<br>";
256 while ($i2 < $num && $i2 < $count){
257 my $query="select * from biblio,biblioitems where
258 biblio.biblionumber='$res[$i2+$offset]' and
259 biblio.biblionumber=biblioitems.biblionumber ";
260 if ($search->{'class'} ne ''){
261 my @temp=split(/\|/,$search->{'class'});
263 $query.= "and ( itemtype='$temp[0]'";
264 for (my $i=1;$i<$count;$i++){
265 $query.=" or itemtype='$temp[$i]'";
269 if ($search->{'dewey'} ne ''){
270 $query.= "and (dewey like '$search->{'dewey'}%') ";
273 my $sth=$dbh->prepare($query);
275 if (my $data2=$sth->fetchrow_hashref){
276 my $dewey= $data2->{'dewey'};
277 my $subclass=$data2->{'subclass'};
279 ($dewey == 0) && ($dewey='');
280 ($dewey) && ($dewey.=" $subclass") ;
282 $data2->{dewey}=~s/[\.0]*$//;
283 ($data2->{dewey}==0) && ($data2->{dewey}='');
284 push @$resulthash, $data2;
285 $res2[$i]="$data2->{'author'}\t$data2->{'title'}\t$data2->{'biblionumber'}\t$data2->{'copyrightdate'}\t$dewey";
294 return($count,$resulthash,@res2);
298 my ($env,$type,$search,$num,$offset)=@_;
299 my $dbh = &C4Connect;
300 $search->{'keyword'}=~ s/ +$//;
301 $search->{'keyword'}=~ s/'/\\'/;
302 my @key=split(' ',$search->{'keyword'});
306 my $query ="Select * from biblio,bibliosubtitle,biblioitems where
307 biblio.biblionumber=biblioitems.biblionumber and
308 biblio.biblionumber=bibliosubtitle.biblionumber and
309 (((title like '$key[0]%' or title like '% $key[0]%')";
311 $query=$query." and (title like '$key[$i]%' or title like '% $key[$i]%')";
314 $query.= ") or ((subtitle like '$key[0]%' or subtitle like '% $key[0]%')";
315 for ($i=1;$i<$count;$i++){
316 $query.= " and (subtitle like '$key[$i]%' or subtitle like '% $key[$i]%')";
318 $query.= ") or ((seriestitle like '$key[0]%' or seriestitle like '% $key[0]%')";
319 for ($i=1;$i<$count;$i++){
320 $query.=" and (seriestitle like '$key[$i]%' or seriestitle like '% $key[$i]%')";
322 $query.= ") or ((biblio.notes like '$key[0]%' or biblio.notes like '% $key[0]%')";
323 for ($i=1;$i<$count;$i++){
324 $query.=" and (biblio.notes like '$key[$i]%' or biblio.notes like '% $key[$i]%')";
326 $query.= ") or ((biblioitems.notes like '$key[0]%' or biblioitems.notes like '% $key[0]%')";
327 for ($i=1;$i<$count;$i++){
328 $query.=" and (biblioitems.notes like '$key[$i]%' or biblioitems.notes like '% $key[$i]%')";
330 if ($search->{'keyword'} =~ /new zealand/i){
331 $query.= "or (title like 'nz%' or title like '% nz %' or title like '% nz' or subtitle like 'nz%'
332 or subtitle like '% nz %' or subtitle like '% nz' or author like 'nz %'
333 or author like '% nz %' or author like '% nz')"
335 if ($search->{'keyword'} eq 'nz' || $search->{'keyword'} eq 'NZ' ||
336 $search->{'keyword'} =~ /nz /i || $search->{'keyword'} =~ / nz /i ||
337 $search->{'keyword'} =~ / nz/i){
338 $query.= "or (title like 'new zealand%' or title like '% new zealand %'
339 or title like '% new zealand' or subtitle like 'new zealand%' or
340 subtitle like '% new zealand %'
341 or subtitle like '% new zealand' or author like 'new zealand%'
342 or author like '% new zealand %' or author like '% new zealand' or
343 seriestitle like 'new zealand%' or seriestitle like '% new zealand %'
344 or seriestitle like '% new zealand')"
347 if ($search->{'class'} ne ''){
348 my @temp=split(/\|/,$search->{'class'});
350 $query.= "and ( itemtype='$temp[0]'";
351 for (my $i=1;$i<$count;$i++){
352 $query.=" or itemtype='$temp[$i]'";
356 if ($search->{'dewey'} ne ''){
357 $query.= "and (dewey like '$search->{'dewey'}%') ";
359 $query.="group by biblio.biblionumber";
360 #$query.=" order by author,title";
361 my $sth=$dbh->prepare($query);
364 while (my $data=$sth->fetchrow_hashref){
365 # my $sti=$dbh->prepare("select dewey,subclass from biblioitems where biblionumber=$data->{'biblionumber'}
368 # my ($dewey, $subclass) = $sti->fetchrow;
369 my $dewey=$data->{'dewey'};
370 my $subclass=$data->{'subclass'};
372 ($dewey == 0) && ($dewey='');
373 ($dewey) && ($dewey.=" $subclass");
375 $results[$i]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey";
376 # print $results[$i];
380 $sth=$dbh->prepare("Select biblionumber from bibliosubject where subject
381 like '%$search->{'keyword'}%' group by biblionumber");
383 while (my $data=$sth->fetchrow_hashref){
384 $query="Select * from biblio,biblioitems where
385 biblio.biblionumber=$data->{'biblionumber'} and
386 biblio.biblionumber=biblioitems.biblionumber ";
387 if ($search->{'class'} ne ''){
388 my @temp=split(/\|/,$search->{'class'});
390 $query.= " and ( itemtype='$temp[0]'";
391 for (my $i=1;$i<$count;$i++){
392 $query.=" or itemtype='$temp[$i]'";
397 if ($search->{'dewey'} ne ''){
398 $query.= "and (dewey like '$search->{'dewey'}%') ";
400 my $sth2=$dbh->prepare($query);
402 while (my $data2=$sth2->fetchrow_hashref){
403 my $dewey= $data2->{'dewey'};
404 my $subclass=$data2->{'subclass'};
406 ($dewey == 0) && ($dewey='');
407 ($dewey) && ($dewey.=" $subclass") ;
409 $results[$i]="$data2->{'author'}\t$data2->{'title'}\t$data2->{'biblionumber'}\t$data2->{'copyrightdate'}\t$dewey";
410 # print $results[$i];
416 @results=sort @results;
423 while ($i2 < $count){
424 if ($results[$i2] ne $res[$i-1]){
425 $res[$i]=$results[$i2];
433 while ($i2 < $num && $i2 < $count){
434 $res2[$i2]=$res[$i2+$offset];
446 my ($env,$type,$search,$num,$offset)=@_;
447 my $dbh = &C4Connect;
450 $search->{'title'}=~ s/'/\\'/g;
451 $search->{'author'}=~ s/'/\\'/g;
452 $search->{'illustrator'}=~ s/'/\\'/g;
453 my $title = lc($search->{'title'});
455 if ($type eq 'loose') {
456 if ($search->{'author'} ne ''){
457 my @key=split(' ',$search->{'author'});
460 $query="select *,biblio.author,biblio.biblionumber from
462 left join additionalauthors
463 on additionalauthors.biblionumber =biblio.biblionumber
465 ((biblio.author like '$key[0]%' or biblio.author like '% $key[0]%' or
466 additionalauthors.author like '$key[0]%' or additionalauthors.author
470 $query=$query." and (
471 biblio.author like '$key[$i]%' or biblio.author like '% $key[$i]%' or
472 additionalauthors.author like '$key[$i]%' or additionalauthors.author like '% $key[$i]%'
477 if ($search->{'title'} ne ''){
478 my @key=split(' ',$search->{'title'});
481 $query.= " and (((title like '$key[0]%' or title like '% $key[0]%' or title like '% $key[0]')";
483 $query=$query." and (title like '$key[$i]%' or title like '% $key[$i]%' or title like '% $key[$i]')";
486 # $query.=") or ((subtitle like '$key[0]%' or subtitle like '% $key[0] %' or subtitle like '% $key[0]')";
487 # for ($i=1;$i<$count;$i++){
488 # $query.=" and (subtitle like '$key[$i]%' or subtitle like '% $key[$i] %' or subtitle like '% $key[$i]')";
490 $query.=") or ((seriestitle like '$key[0]%' or seriestitle like '% $key[0]%' or seriestitle like '% $key[0]')";
491 for ($i=1;$i<$count;$i++){
492 $query.=" and (seriestitle like '$key[$i]%' or seriestitle like '% $key[$i]%')";
494 $query.=") or ((unititle like '$key[0]%' or unititle like '% $key[0]%' or unititle like '% $key[0]')";
495 for ($i=1;$i<$count;$i++){
496 $query.=" and (unititle like '$key[$i]%' or unititle like '% $key[$i]%')";
499 #$query=$query. " and (title like '%$search->{'title'}%'
500 #or seriestitle like '%$search->{'title'}%')";
503 $query.=" group by biblio.biblionumber";
505 if ($search->{'title'} ne '') {
506 if ($search->{'ttype'} eq 'exact'){
507 $query="select * from biblio
509 (biblio.title='$search->{'title'}' or (biblio.unititle = '$search->{'title'}'
510 or biblio.unititle like '$search->{'title'} |%' or
511 biblio.unititle like '%| $search->{'title'} |%' or
512 biblio.unititle like '%| $search->{'title'}') or
513 (biblio.seriestitle = '$search->{'title'}' or
514 biblio.seriestitle like '$search->{'title'} |%' or
515 biblio.seriestitle like '%| $search->{'title'} |%' or
516 biblio.seriestitle like '%| $search->{'title'}')
519 my @key=split(' ',$search->{'title'});
522 $query="select * from biblio
523 left join bibliosubtitle on
524 biblio.biblionumber=bibliosubtitle.biblionumber
526 (((title like '$key[0]%' or title like '% $key[0]%' or title like '% $key[0]')";
528 $query=$query." and (title like '$key[$i]%' or title like '% $key[$i]%' or title like '% $key[$i]')";
531 $query.=") or ((subtitle like '$key[0]%' or subtitle like '% $key[0]%' or subtitle like '% $key[0]')";
532 for ($i=1;$i<$count;$i++){
533 $query.=" and (subtitle like '$key[$i]%' or subtitle like '% $key[$i]%' or subtitle like '% $key[$i]')";
535 $query.=") or ((seriestitle like '$key[0]%' or seriestitle like '% $key[0]%' or seriestitle like '% $key[0]')";
536 for ($i=1;$i<$count;$i++){
537 $query.=" and (seriestitle like '$key[$i]%' or seriestitle like '% $key[$i]%')";
539 $query.=") or ((unititle like '$key[0]%' or unititle like '% $key[0]%' or unititle like '% $key[0]')";
540 for ($i=1;$i<$count;$i++){
541 $query.=" and (unititle like '$key[$i]%' or unititle like '% $key[$i]%')";
545 } elsif ($search->{'class'} ne ''){
546 $query="select * from biblioitems,biblio where biblio.biblionumber=biblioitems.biblionumber";
547 my @temp=split(/\|/,$search->{'class'});
549 $query.= " and ( itemtype='$temp[0]'";
550 for (my $i=1;$i<$count;$i++){
551 $query.=" or itemtype='$temp[$i]'";
554 if ($search->{'illustrator'} ne ''){
555 $query.=" and illus like '%".$search->{'illustrator'}."%' ";
557 if ($search->{'dewey'} ne ''){
558 $query.=" and biblioitems.dewey like '$search->{'dewey'}%'";
560 } elsif ($search->{'dewey'} ne ''){
561 $query="select * from biblioitems,biblio
562 where biblio.biblionumber=biblioitems.biblionumber
563 and biblioitems.dewey like '$search->{'dewey'}%'";
564 } elsif ($search->{'illustrator'} ne '') {
565 if ($search->{'illustrator'} ne ''){
566 $query="select * from biblioitems,biblio
567 where biblio.biblionumber=biblioitems.biblionumber
568 and biblioitems.illus like '%".$search->{'illustrator'}."%'";
571 $query .=" group by biblio.biblionumber";
574 if ($type eq 'subject'){
575 my @key=split(' ',$search->{'subject'});
578 $query="select distinct(subject) from bibliosubject where( subject like
579 '$key[0]%' or subject like '% $key[0]%' or subject like '% $key[0]' or subject like '%($key[0])%')";
581 $query.=" and (subject like '$key[$i]%' or subject like '% $key[$i]%'
582 or subject like '% $key[$i]'
583 or subject like '%($key[$i])%')";
586 if ($search->{'subject'} eq 'NZ' || $search->{'subject'} eq 'nz'){
587 $query.= " or (subject like 'NEW ZEALAND %' or subject like '% NEW ZEALAND %'
588 or subject like '% NEW ZEALAND' or subject like '%(NEW ZEALAND)%' ) ";
589 } elsif ( $search->{'subject'} =~ /^nz /i || $search->{'subject'} =~ / nz /i || $search->{'subject'} =~ / nz$/i){
590 $query=~ s/ nz/ NEW ZEALAND/ig;
591 $query=~ s/nz /NEW ZEALAND /ig;
592 $query=~ s/\(nz\)/\(NEW ZEALAND\)/gi;
595 if ($type eq 'precise'){
596 $query="select * from items,biblio ";
597 if ($search->{'item'} ne ''){
598 my $search2=uc $search->{'item'};
599 $query=$query." where
600 items.biblionumber=biblio.biblionumber
601 and barcode='$search2'";
603 if ($search->{'isbn'} ne ''){
604 my $search2=uc $search->{'isbn'};
605 my $query1 = "select * from biblioitems where isbn='$search2'";
606 my $sth1=$dbh->prepare($query1);
609 while (my $data=$sth1->fetchrow_hashref) {
610 $query="select * from biblioitems,biblio where
611 biblio.biblionumber = $data->{'biblionumber'}
612 and biblioitems.biblionumber = biblio.biblionumber";
613 my $sth=$dbh->prepare($query);
615 my $data=$sth->fetchrow_hashref;
616 my ($dewey, $subclass) = ($data->{'dewey'}, $data->{'subclass'});
618 ($dewey == 0) && ($dewey='');
619 ($dewey) && ($dewey.=" $subclass");
620 $results[$i2]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey\t$data->{'isbn'}\t$data->{'itemtype'}";
627 if ($type ne 'precise' && $type ne 'subject'){
628 if ($search->{'author'} ne ''){
629 $query=$query." order by biblio.author,title";
631 $query=$query." order by title";
634 if ($type eq 'subject'){
635 $query=$query." order by subject";
638 my $sth=$dbh->prepare($query);
642 my $limit= $num+$offset;
643 while (my $data=$sth->fetchrow_hashref){
644 my $query="select dewey,subclass from biblioitems where biblionumber=$data->{'biblionumber'}";
645 if ($search->{'class'} ne ''){
646 my @temp=split(/\|/,$search->{'class'});
648 $query.= " and ( itemtype='$temp[0]'";
649 for (my $i=1;$i<$count;$i++){
650 $query.=" or itemtype='$temp[$i]'";
654 if ($search->{'dewey'} ne ''){
655 $query.=" and dewey='$search->{'dewey'}' ";
657 if ($search->{'illustrator'} ne ''){
658 $query.=" and illus like '%".$search->{'illustrator'}."%' ";
660 my $sti=$dbh->prepare($query);
665 if (($dewey, $subclass) = $sti->fetchrow || $type eq 'subject'){
669 ($dewey == 0) && ($dewey='');
670 ($dewey) && ($dewey.=" $subclass");
673 if ($count > $offset && $count <= $limit){
674 if ($type ne 'subject' && $type ne 'precise'){
675 $results[$i]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey\t$data->{'illus'}";
676 } elsif ($search->{'isbn'} ne '' || $search->{'item'} ne ''){
677 $results[$i]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey\t$data->{'illus'}";
679 $results[$i]="$data->{'author'}\t$data->{'subject'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey\t$data->{'illus'}";
687 #if ($type ne 'precise'){
691 return($count,@results);
694 sub updatesearchstats{
700 my ($env,$subject)=@_;
702 $subject=$dbh->quote($subject);
703 my $query="Select * from biblio,bibliosubject where
704 biblio.biblionumber=bibliosubject.biblionumber and
705 bibliosubject.subject=$subject group by biblio.biblionumber
706 order by biblio.title";
707 my $sth=$dbh->prepare($query);
711 while (my $data=$sth->fetchrow_hashref){
712 $results[$i]="$data->{'title'}\t$data->{'author'}\t$data->{'biblionumber'}";
722 my ($env,$biblionumber,$type)=@_;
723 my $dbh = &C4Connect;
724 my $query="Select * from items,biblio,biblioitems,branches
725 where (items.biblioitemnumber = biblioitems.biblioitemnumber)
726 and biblioitems.biblionumber=biblio.biblionumber
727 and biblio.biblionumber='$biblionumber' and branches.branchcode=
728 items.holdingbranch ";
730 if ($type ne 'intra'){
731 $query.=" and (items.itemlost<>1 or items.itemlost is NULL)
732 and (wthdrawn <> 1 or wthdrawn is NULL)";
734 $query=$query."order by items.dateaccessioned desc";
735 my $sth=$dbh->prepare($query);
739 while (my $data=$sth->fetchrow_hashref){
740 my $iquery = "Select * from issues
741 where itemnumber = '$data->{'itemnumber'}'
742 and returndate is null";
744 my $isth=$dbh->prepare($iquery);
746 if (my $idata=$isth->fetchrow_hashref){
747 my @temp=split('-',$idata->{'date_due'});
748 $datedue = "$temp[2]/$temp[1]/$temp[0]";
750 if ($data->{'itemlost'} eq '1'){
753 if ($data->{'wthdrawn'} eq '1'){
754 $datedue="Cancelled";
757 my ($rescount,$reserves)=Findgroupreserve($data->{'biblioitemnumber'},$biblionumber);
764 my $class = $data->{'classification'};
765 my $dewey = $data->{'dewey'};
767 if ($dewey eq "000.") { $dewey = "";};
768 if ($dewey < 10){$dewey='00'.$dewey;}
769 if ($dewey < 100 && $dewey > 10){$dewey='0'.$dewey;}
774 $class = $class.$dewey;
776 $class = $class.$data->{'subclass'};
778 # $results[$i]="$data->{'title'}\t$data->{'barcode'}\t$datedue\t$data->{'branchname'}\t$data->{'dewey'}";
779 my @temp=split('-',$data->{'datelastseen'});
780 my $date="$temp[2]/$temp[1]/$temp[0]";
781 $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'}";
782 # print "$results[$i] <br>";
786 my $query2="Select * from aqorders where biblionumber=$biblionumber";
787 my $sth2=$dbh->prepare($query2);
791 if ($data=$sth2->fetchrow_hashref){
792 $ocount=$data->{'quantity'} - $data->{'quantityreceived'};
794 $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'}";
804 my ($env,$biblionumber)=@_;
805 #debug_msg($env,"GetItems");
806 my $dbh = &C4Connect;
807 my $query = "Select * from biblioitems where (biblionumber = $biblionumber)";
808 #debug_msg($env,$query);
809 my $sth=$dbh->prepare($query);
811 #debug_msg($env,"executed query");
814 while (my $data=$sth->fetchrow_hashref) {
815 #debug_msg($env,$data->{'biblioitemnumber'});
816 my $dewey = $data->{'dewey'};
818 my $line = $data->{'biblioitemnumber'}."\t".$data->{'itemtype'};
819 $line = $line."\t$data->{'classification'}\t$dewey";
820 $line = $line."\t$data->{'subclass'}\t$data->{isbn}";
821 $line = $line."\t$data->{'volume'}\t$data->{number}";
822 my $isth= $dbh->prepare("select * from items where biblioitemnumber = $data->{'biblioitemnumber'}");
824 while (my $idata = $isth->fetchrow_hashref) {
825 my $iline = $idata->{'barcode'}."[".$idata->{'holdingbranch'}."[";
826 if ($idata->{'notforloan'} == 1) {
827 $iline = $iline."NFL ";
829 if ($idata->{'itemlost'} == 1) {
830 $iline = $iline."LOST ";
832 $line = $line."\t$iline";
835 $results[$i] = $line;
846 my $query="Select * from items,biblioitems where barcode='$barcode'
847 and items.biblioitemnumber=biblioitems.biblioitemnumber";
848 my $sth=$dbh->prepare($query);
850 my $data=$sth->fetchrow_hashref;
857 my ($bibnum,$type)=@_;
859 my $query="Select *,biblio.notes
860 from biblio,biblioitems
861 left join bibliosubtitle on
862 biblio.biblionumber=bibliosubtitle.biblionumber
864 where biblio.biblionumber=$bibnum
865 and biblioitems.biblionumber=$bibnum";
866 my $sth=$dbh->prepare($query);
868 my $data=$sth->fetchrow_hashref;
870 $query="Select * from bibliosubject where biblionumber='$bibnum'";
871 $sth=$dbh->prepare($query);
873 while (my $dat=$sth->fetchrow_hashref){
874 $data->{'subject'}.=" | $dat->{'subject'}";
885 my $query="Select *,biblioitems.notes as bnotes from biblio,biblioitems,itemtypes where biblio.biblionumber=
886 biblioitems.biblionumber and biblioitemnumber=$bibitem and
887 biblioitems.itemtype=itemtypes.itemtype";
888 my $sth=$dbh->prepare($query);
890 my $data=$sth->fetchrow_hashref;
899 my $query="Select * from bibliosubject where biblionumber=$bibnum";
900 my $sth=$dbh->prepare($query);
904 while (my $data=$sth->fetchrow_hashref){
910 return($i,\@results);
916 my $query="Select * from additionalauthors where biblionumber=$bibnum";
917 my $sth=$dbh->prepare($query);
921 while (my $data=$sth->fetchrow_hashref){
927 return($i,\@results);
933 my $query="Select * from bibliosubtitle where biblionumber=$bibnum";
934 my $sth=$dbh->prepare($query);
938 while (my $data=$sth->fetchrow_hashref){
944 return($i,\@results);
950 my ($bibitem,$biblio)=@_;
952 my $query="Select * from items where
953 items.biblioitemnumber='$bibitem'";
954 my $sth=$dbh->prepare($query) || die $dbh->errstr;
955 $sth->execute || die $sth->errstr;
958 while (my $data=$sth->fetchrow_hashref) {
959 my $query2="select * from issues,borrowers where itemnumber=$data->{'itemnumber'}
960 and returndate is NULL and issues.borrowernumber=borrowers.borrowernumber";
961 my $sth2=$dbh->prepare($query2);
963 if (my $data2=$sth2->fetchrow_hashref) {
964 $data->{'date_due'}=$data2->{'date_due'};
965 $data->{'card'}=$data2->{'cardnumber'};
967 if ($data->{'wthdrawn'} eq '1') {
968 $data->{'date_due'}='Cancelled';
970 $data->{'date_due'}='Available';
974 $query2="select * from issues,borrowers where itemnumber='$data->{'itemnumber'}'
975 and issues.borrowernumber=borrowers.borrowernumber
976 order by date_due desc";
977 my $sth2=$dbh->prepare($query2) || die $dbh->errstr;
978 $sth2->execute || die $sth2->errstr;
979 for (my $i2=0;$i2<2;$i2++){
980 if (my $data2=$sth2->fetchrow_hashref){
981 $data->{"timestamp$i2"}=$data2->{'timestamp'};
982 $data->{"card$i2"}=$data2->{'cardnumber'};
983 $data->{"borrower$i2"}=$data2->{'borrowernumber'};
996 my ($env,$dbh,$itemnumber) = @_;
998 my $query="Select * from biblio,items,biblioitems
999 where items.itemnumber = '$itemnumber'
1000 and biblio.biblionumber = items.biblionumber
1001 and biblioitems.biblioitemnumber = items.biblioitemnumber";
1002 my $sth=$dbh->prepare($query);
1004 my $data=$sth->fetchrow_hashref;
1010 #used by member enquiries from the intranet
1011 #called by member.pl
1013 my ($env,$searchstring,$type)=@_;
1014 my $dbh = &C4Connect;
1015 $searchstring=~ s/\'/\\\'/g;
1016 my @data=split(' ',$searchstring);
1018 my $query="Select * from borrowers
1019 where ((surname like \"$data[0]%\" or surname like \"% $data[0]%\"
1020 or firstname like \"$data[0]%\" or firstname like \"% $data[0]%\"
1021 or othernames like \"$data[0]%\" or othernames like \"% $data[0]%\")
1023 for (my $i=1;$i<$count;$i++){
1024 $query=$query." and (surname like \"$data[$i]%\" or surname like \"% $data[$i]%\"
1025 or firstname like \"$data[$i]%\" or firstname like \"% $data[$i]%\"
1026 or othernames like \"$data[$i]%\" or othernames like \"% $data[$i]%\")";
1028 $query=$query.") or cardnumber = \"$searchstring\"
1029 order by surname,firstname";
1030 my $sth=$dbh->prepare($query);
1034 while (my $data=$sth->fetchrow_hashref){
1035 push(@results,$data);
1041 return ($cnt,\@results);
1045 my ($cardnumber,$bornum)=@_;
1046 $cardnumber = uc $cardnumber;
1050 $query="Select * from borrowers where cardnumber='$cardnumber'";
1052 $query="Select * from borrowers where borrowernumber='$bornum'";
1054 my $sth=$dbh->prepare($query);
1056 my $data=$sth->fetchrow_hashref;
1066 $query="Select * from issues,biblio,items where borrowernumber='$bornum' and
1067 items.itemnumber=issues.itemnumber and
1068 items.biblionumber=biblio.biblionumber and issues.returndate is NULL order
1070 my $sth=$dbh->prepare($query);
1074 while (my $data=$sth->fetchrow_hashref){
1080 return($i,\@result);
1084 my ($bornum,$order,$limit)=@_;
1087 $query="Select * from issues,biblio,items,biblioitems
1088 where borrowernumber='$bornum' and
1089 items.biblioitemnumber=biblioitems.biblioitemnumber and
1090 items.itemnumber=issues.itemnumber and
1091 items.biblionumber=biblio.biblionumber";
1092 $query.=" order by $order";
1094 $query.=" limit $limit";
1096 my $sth=$dbh->prepare($query);
1100 while (my $data=$sth->fetchrow_hashref){
1106 return($i,\@result);
1110 my ($env,$bornum)=@_;
1112 my $query="Select count(*) from issues where borrowernumber='$bornum' and
1113 returndate is NULL";
1114 my $sth=$dbh->prepare($query);
1116 my $data=$sth->fetchrow_hashref;
1118 $sth=$dbh->prepare("Select count(*) from issues where
1119 borrowernumber='$bornum' and date_due < now() and returndate is NULL");
1121 my $data2=$sth->fetchrow_hashref;
1123 $sth=$dbh->prepare("Select sum(amountoutstanding) from accountlines where
1124 borrowernumber='$bornum'");
1126 my $data3=$sth->fetchrow_hashref;
1130 return($data2->{'count(*)'},$data->{'count(*)'},$data3->{'sum(amountoutstanding)'});
1134 sub getboracctrecord {
1135 my ($env,$params) = @_;
1139 my $query= "Select * from accountlines where
1140 borrowernumber=$params->{'borrowernumber'} order by date desc,timestamp desc";
1141 my $sth=$dbh->prepare($query);
1144 while (my $data=$sth->fetchrow_hashref){
1145 # if ($data->{'itemnumber'} ne ''){
1146 # $query="Select * from items,biblio where items.itemnumber=
1147 # '$data->{'itemnumber'}' and biblio.biblionumber=items.biblionumber";
1148 # my $sth2=$dbh->prepare($query);
1150 # my $data2=$sth2->fetchrow_hashref;
1154 $acctlines[$numlines] = $data;
1156 $total = $total+ $data->{'amountoutstanding'};
1160 return ($numlines,\@acctlines,$total);
1164 my ($env,$bibnum,$type)=@_;
1166 my $query="Select * from items where
1167 biblionumber=$bibnum ";
1168 if ($type ne 'intra'){
1169 $query.=" and (itemlost <>1 or itemlost is NULL) and
1170 (wthdrawn <> 1 or wthdrawn is NULL)";
1172 my $sth=$dbh->prepare($query);
1183 while (my $data=$sth->fetchrow_hashref){
1185 my $query2="select * from issues,items where issues.itemnumber=
1186 '$data->{'itemnumber'}' and returndate is NULL
1187 and items.itemnumber=issues.itemnumber and (items.itemlost <>1 or
1188 items.itemlost is NULL)";
1189 my $sth2=$dbh->prepare($query2);
1191 if (my $data2=$sth2->fetchrow_hashref){
1194 if ($data->{'holdingbranch'} eq 'C'){
1197 if ($data->{'holdingbranch'} eq 'F' || $data->{'holdingbranch'} eq 'FP'){
1200 if ($data->{'holdingbranch'} eq 'S' || $data->{'holdingbranch'} eq 'SP'){
1203 if ($data->{'itemlost'} eq '1'){
1206 if ($data->{'holdingbranch'} eq 'FM'){
1209 if ($data->{'holdingbranch'} eq 'TR'){
1216 my $query2="Select * from aqorders where biblionumber=$bibnum";
1217 my $sth2=$dbh->prepare($query2);
1219 if (my $data=$sth2->fetchrow_hashref){
1220 $ocount=$data->{'quantity'} - $data->{'quantityreceived'};
1226 return ($count,$lcount,$nacount,$fcount,$scount,$lostcount,$mending,$transit,$ocount);
1232 my $query="select description from itemtypes where itemtype='$type'";
1233 my $sth=$dbh->prepare($query);
1235 my $dat=$sth->fetchrow_hashref;
1238 return ($dat->{'description'});
1244 my $query="Select * from biblioitems,itemtypes,items where
1245 biblioitems.biblionumber='$bibnum' and biblioitems.itemtype=itemtypes.itemtype and
1246 biblioitems.biblioitemnumber=items.biblioitemnumber group by
1247 items.biblioitemnumber";
1248 my $sth=$dbh->prepare($query);
1252 while (my $data=$sth->fetchrow_hashref){
1258 return($i,@results);
1262 my ($biblioitemnumber)=@_;
1264 my $query="Select barcode from items where
1265 biblioitemnumber='$biblioitemnumber'";
1266 my $sth=$dbh->prepare($query);
1270 while (my $data=$sth->fetchrow_hashref){
1271 $barcodes[$i]=$data->{'barcode'};
1279 END { } # module clean-up code here (global destructor)