1 package C4::Search; #asummes C4/Search
3 #requires DBI.pm to be installed
12 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
14 # set the version for version checking
18 @EXPORT = qw(&CatSearch &BornameSearch &ItemInfo &KeywordSearch &subsearch
19 &itemdata &bibdata &GetItems &borrdata &getacctlist &itemnodata &itemcount
20 &OpacSearch &borrdata2 &NewBorrowerNumber &bibitemdata &borrissues
21 &getboracctrecord &ItemType &itemissues &FrontSearch &subject &subtitle
22 &addauthor &bibitems &barcodes &findguarantees &allissues &systemprefs
24 %EXPORT_TAGS = ( ); # eg: TAG => [ qw!name1 name2! ],
26 # your exported package globals go here,
27 # as well as any optionally exported functions
29 @EXPORT_OK = qw($Var1 %Hashit);
32 # non-exported package globals go here
33 use vars qw(@more $stuff);
35 # initalize package globals, first exported ones
40 # then the others (which are still accessible as $Some::Module::stuff)
44 # all file-scoped lexicals must be created before
45 # the functions below that use them.
47 # file-private lexicals go here
51 # here's a file-private function as a closure,
52 # callable as &$priv_func; it cannot be prototyped.
57 # make all your functions, whether exported or not;
61 my $query="select cardnumber,borrowernumber from borrowers where
63 my $sth=$dbh->prepare($query);
67 while (my $data=$sth->fetchrow_hashref){
78 my $query="select guarantor from borrowers where
79 borrowernumber='$bornum'";
80 my $sth=$dbh->prepare($query);
82 my $data=$sth->fetchrow_hashref;
84 $query="Select * from borrowers where
85 borrowernumber='$data->{'guarantor'}'";
86 $sth=$dbh->prepare($query);
88 $data=$sth->fetchrow_hashref;
97 my $sth=$dbh->prepare("select variable,value from systempreferences");
99 while (my ($variable,$value)=$sth->fetchrow) {
100 $systemprefs{$variable}=$value;
104 return(%systemprefs);
107 sub NewBorrowerNumber {
109 my $sth=$dbh->prepare("Select max(borrowernumber) from borrowers");
111 my $data=$sth->fetchrow_hashref;
113 $data->{'max(borrowernumber)'}++;
115 return($data->{'max(borrowernumber)'});
119 my ($env,$type,$search,$num,$offset)=@_;
120 my $dbh = &C4Connect;
121 $search->{'keyword'}=~ s/'/\\'/g;
122 my @key=split(' ',$search->{'keyword'});
126 my $query ="Select count(*) from biblio where
127 ((title like '$key[0]%' or title like '% $key[0]%')";
129 $query=$query." and (title like '$key[$i]%' or title like '% $key[$i]%')";
132 $query=$query.") or ((author like '$key[0]%' or author like '% $key[0]%')";
135 $query=$query." and (author like '$key[$i]%' or author like '% $key[$i]%')";
138 $query.=") or ((seriestitle like '$key[0]%' or seriestitle like '% $key[0]%')";
139 for ($i=1;$i<$count;$i++){
140 $query.=" and (seriestitle like '$key[$i]%' or seriestitle like '% $key[$i]%')";
142 $query.= ") or ((notes like '$key[0]%' or notes like '% $key[0]%')";
143 for ($i=1;$i<$count;$i++){
144 $query.=" and (notes like '$key[$i]%' or notes like '% $key[$i]%')";
146 $query=$query.") order by title";
147 my $sth=$dbh->prepare($query);
149 my $data=$sth->fetchrow_hashref;
150 my $count=$data->{'count(*)'};
152 $query=~ s/count\(\*\)/\*/;
153 $query= $query." limit $offset,$num";
154 $sth=$dbh->prepare($query);
158 while (my $data=$sth->fetchrow_hashref){
159 my $sti=$dbh->prepare("select dewey,subclass from biblioitems where biblionumber=$data->{'biblionumber'}");
161 my ($dewey, $subclass) = $sti->fetchrow;
163 ($dewey == 0) && ($dewey='');
164 ($dewey) && ($dewey.=" $subclass");
166 $results[$i]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$dewey";
171 return($count,@results);
177 my ($env,$type,$search,$num,$offset)=@_;
178 my $dbh = &C4Connect;
179 $search->{'front'}=~ s/ +$//;
180 $search->{'front'}=~ s/'/\\'/;
181 my @key=split(' ',$search->{'front'});
185 my $query ="Select * from biblio,bibliosubtitle where
186 biblio.biblionumber=bibliosubtitle.biblionumber and
187 ((title like '$key[0]%' or title like '% $key[0]%'
188 or subtitle like '$key[0]%' or subtitle like '% $key[0]%'
189 or author like '$key[0]%' or author like '% $key[0]%')";
191 $query=$query." and (title like '%$key[$i]%' or subtitle like '%$key[$i]%')";
194 $query=$query.") group by biblio.biblionumber order by author,title";
196 my $sth=$dbh->prepare($query);
199 while (my $data=$sth->fetchrow_hashref){
200 my $sti=$dbh->prepare("select dewey,subclass from biblioitems where biblionumber=$data->{'biblionumber'}");
202 my ($dewey, $subclass) = $sti->fetchrow;
204 ($dewey == 0) && ($dewey='');
205 ($dewey) && ($dewey.=" $subclass");
207 $results[$i]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey";
208 # print $results[$i];
212 $sth=$dbh->prepare("Select biblionumber from bibliosubject where subject
213 like '%$search->{'keyword'}%'");
215 while (my $data=$sth->fetchrow_hashref){
216 my $sth2=$dbh->prepare("Select * from biblio where
217 biblionumber=$data->{'biblionumber'}");
219 while (my $data2=$sth2->fetchrow_hashref){
221 $results[$i]="$data2->{'author'}\t$data2->{'title'}\t$data2->{'biblionumber'}\t$data->{'copyrightdate'}";
222 # print $results[$i];
228 @results=sort @results;
233 while ($i2 < $count){
234 if ($results[$i2] ne $res[$i-1]){
235 $res[$i]=$results[$i2];
243 while ($i2 < $num && $i2 < $count){
244 $res2[$i2]=$res[$i2+$offset];
255 my ($env,$type,$search,$num,$offset)=@_;
256 my $dbh = &C4Connect;
257 $search->{'keyword'}=~ s/ +$//;
258 $search->{'keyword'}=~ s/'/\\'/;
259 my @key=split(' ',$search->{'keyword'});
263 my $query ="Select * from biblio,bibliosubtitle,biblioitems where
264 biblio.biblionumber=bibliosubtitle.biblionumber and
265 biblioitems.biblionumber=biblio.biblionumber and
266 (((title like '$key[0]%' or title like '% $key[0]%')";
268 $query=$query." and (title like '$key[$i]%' or title like '% $key[$i]%')";
271 $query.= ") or ((subtitle like '$key[0]%' or subtitle like '% $key[0]%')";
272 for ($i=1;$i<$count;$i++){
273 $query.= " and (subtitle like '$key[$i]%' or subtitle like '% $key[$i]%')";
275 $query.= ") or ((seriestitle like '$key[0]%' or seriestitle like '% $key[0]%')";
276 for ($i=1;$i<$count;$i++){
277 $query.=" and (seriestitle like '$key[$i]%' or seriestitle like '% $key[$i]%')";
279 $query.= ") or ((biblio.notes like '$key[0]%' or biblio.notes like '% $key[0]%')";
280 for ($i=1;$i<$count;$i++){
281 $query.=" and (biblio.notes like '$key[$i]%' or biblio.notes like '% $key[$i]%')";
283 $query.= ") or ((biblioitems.notes like '$key[0]%' or biblioitems.notes like '% $key[0]%')";
284 for ($i=1;$i<$count;$i++){
285 $query.=" and (biblioitems.notes like '$key[$i]%' or biblioitems.notes like '% $key[$i]%')";
287 if ($search->{'keyword'} =~ /new zealand/i){
288 $query.= "or (title like 'nz%' or title like '% nz %' or title like '% nz' or subtitle like 'nz%'
289 or subtitle like '% nz %' or subtitle like '% nz' or author like 'nz %'
290 or author like '% nz %' or author like '% nz')"
292 if ($search->{'keyword'} eq 'nz' || $search->{'keyword'} eq 'NZ' ||
293 $search->{'keyword'} =~ /nz /i || $search->{'keyword'} =~ / nz /i ||
294 $search->{'keyword'} =~ / nz/i){
295 $query.= "or (title like 'new zealand%' or title like '% new zealand %'
296 or title like '% new zealand' or subtitle like 'new zealand%' or
297 subtitle like '% new zealand %'
298 or subtitle like '% new zealand' or author like 'new zealand%'
299 or author like '% new zealand %' or author like '% new zealand' or
300 seriestitle like 'new zealand%' or seriestitle like '% new zealand %'
301 or seriestitle like '% new zealand')"
304 if ($search->{'class'} ne ''){
305 my @temp=split(/\|/,$search->{'class'});
307 $query.= "and ( itemtype='$temp[0]'";
308 for (my $i=1;$i<$count;$i++){
309 $query.=" or itemtype='$temp[$i]'";
313 $query.="group by biblio.biblionumber order by author,title";
315 my $sth=$dbh->prepare($query);
318 while (my $data=$sth->fetchrow_hashref){
319 my $sti=$dbh->prepare("select dewey,subclass from biblioitems where biblionumber=$data->{'biblionumber'}
322 my ($dewey, $subclass) = $sti->fetchrow;
324 ($dewey == 0) && ($dewey='');
325 ($dewey) && ($dewey.=" $subclass");
327 $results[$i]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey";
328 # print $results[$i];
332 $sth=$dbh->prepare("Select biblionumber from bibliosubject where subject
333 like '%$search->{'keyword'}%' group by biblionumber");
335 while (my $data=$sth->fetchrow_hashref){
336 $query="Select * from biblio,biblioitems where
337 biblio.biblionumber=$data->{'biblionumber'} and
338 biblio.biblionumber=biblioitems.biblionumber ";
339 if ($search->{'class'} ne ''){
340 my @temp=split(/\|/,$search->{'class'});
342 $query.= " and ( itemtype='$temp[0]'";
343 for (my $i=1;$i<$count;$i++){
344 $query.=" or itemtype='$temp[$i]'";
348 my $sth2=$dbh->prepare($query);
351 while (my $data2=$sth2->fetchrow_hashref){
352 my $dewey= $data2->{'dewey'};
353 my $subclass=$data2->{'subclass'};
355 ($dewey == 0) && ($dewey='');
356 ($dewey) && ($dewey.=" $subclass") ;
358 $results[$i]="$data2->{'author'}\t$data2->{'title'}\t$data2->{'biblionumber'}\t$data2->{'copyrightdate'}\t$dewey";
359 # print $results[$i];
365 @results=sort @results;
372 while ($i2 < $count){
373 if ($results[$i2] ne $res[$i-1]){
374 $res[$i]=$results[$i2];
382 while ($i2 < $num && $i2 < $count){
383 $res2[$i2]=$res[$i2+$offset];
394 my ($env,$type,$search,$num,$offset)=@_;
395 my $dbh = &C4Connect;
398 $search->{'title'}=~ s/'/\\'/g;
399 $search->{'author'}=~ s/'/\\'/g;
400 $search->{'illustrator'}=~ s/'/\\'/g;
401 my $title = lc($search->{'title'});
403 if ($type eq 'loose') {
404 if ($search->{'author'} ne ''){
405 my @key=split(' ',$search->{'author'});
408 $query="select *,biblio.author,biblio.biblionumber from
410 left join additionalauthors
411 on additionalauthors.biblionumber =biblio.biblionumber
412 where biblioitems.biblionumber=biblio.biblionumber
414 ((biblio.author like '$key[0]%' or biblio.author like '% $key[0]%' or
415 additionalauthors.author like '$key[0]%' or additionalauthors.author
419 $query=$query." and (
420 biblio.author like '$key[$i]%' or biblio.author like '% $key[$i]%' or
421 additionalauthors.author like '$key[$i]%' or additionalauthors.author like '% $key[$i]%'
426 if ($search->{'title'} ne ''){
427 $query=$query. " and (title like '%$search->{'title'}%'
428 or seriestitle like '%$search->{'title'}%')";
430 if ($search->{'class'} ne ''){
431 my @temp=split(/\|/,$search->{'class'});
433 $query.= "and ( itemtype='$temp[0]'";
434 for (my $i=1;$i<$count;$i++){
435 $query.=" or itemtype='$temp[$i]'";
439 if ($search->{'dewey'} ne ''){
440 $query.=" and dewey='$search->{'dewey'}' ";
442 if ($search->{'illustrator'} ne ''){
443 $query.=" and illus like '%".$search->{'illustrator'}."%' ";
446 $query.=" group by biblio.biblionumber";
448 if ($search->{'title'} ne '') {
449 if ($search->{'ttype'} eq 'exact'){
450 $query="select * from biblio
452 (biblio.title='$search->{'title'}' or (biblio.unititle = '$search->{'title'}'
453 or biblio.unititle like '$search->{'title'} |%' or
454 biblio.unititle like '%| $search->{'title'} |%' or
455 biblio.unititle like '%| $search->{'title'}') or
456 (biblio.seriestitle = '$search->{'title'}' or
457 biblio.seriestitle like '$search->{'title'} |%' or
458 biblio.seriestitle like '%| $search->{'title'} |%' or
459 biblio.seriestitle like '%| $search->{'title'}')
462 my @key=split(' ',$search->{'title'});
465 $query="select * from biblio,bibliosubtitle,biblioitems
467 (biblio.biblionumber=bibliosubtitle.biblionumber and
468 biblioitems.biblionumber=biblio.biblionumber) and
469 (((title like '$key[0]%' or title like '% $key[0]%' or title like '% $key[0]')";
471 $query=$query." and (title like '$key[$i]%' or title like '% $key[$i]%' or title like '% $key[$i]')";
474 $query.=") or ((subtitle like '$key[0]%' or subtitle like '% $key[0] %' or subtitle like '% $key[0]')";
475 for ($i=1;$i<$count;$i++){
476 $query.=" and (subtitle like '$key[$i]%' or subtitle like '% $key[$i] %' or subtitle like '% $key[$i]')";
478 $query.=") or ((seriestitle like '$key[0]%' or seriestitle like '% $key[0] %' or seriestitle like '% $key[0]')";
479 for ($i=1;$i<$count;$i++){
480 $query.=" and (seriestitle like '$key[$i]%' or seriestitle like '% $key[$i] %')";
482 $query.=") or ((unititle like '$key[0]%' or unititle like '% $key[0] %' or unititle like '% $key[0]')";
483 for ($i=1;$i<$count;$i++){
484 $query.=" and (unititle like '$key[$i]%' or unititle like '% $key[$i] %')";
487 if ($search->{'class'} ne ''){
488 my @temp=split(/\|/,$search->{'class'});
490 $query.= " and ( itemtype='$temp[0]'";
491 for (my $i=1;$i<$count;$i++){
492 $query.=" or itemtype='$temp[$i]'";
496 if ($search->{'dewey'} ne ''){
497 $query.=" and dewey='$search->{'dewey'}' ";
499 if ($search->{'illustrator'} ne ''){
500 $query.=" and illus like '%".$search->{'illustrator'}."%' ";
503 } elsif ($search->{'class'} ne ''){
504 $query="select * from biblioitems,biblio where biblio.biblionumber=biblioitems.biblionumber";
505 my @temp=split(/\|/,$search->{'class'});
507 $query.= " and ( itemtype='$temp[0]'";
508 for (my $i=1;$i<$count;$i++){
509 $query.=" or itemtype='$temp[$i]'";
512 if ($search->{'illustrator'} ne ''){
513 $query.=" and illus like '%".$search->{'illustrator'}."%' ";
515 if ($search->{'dewey'} ne ''){
516 $query.=" and biblioitems.dewey like '$search->{'dewey'}%'";
518 } elsif ($search->{'dewey'} ne ''){
519 $query="select * from biblioitems,biblio
520 where biblio.biblionumber=biblioitems.biblionumber
521 and biblioitems.dewey like '$search->{'dewey'}%'";
522 } elsif ($search->{'illustrator'} ne '') {
523 if ($search->{'illustrator'} ne ''){
524 $query="select * from biblioitems,biblio
525 where biblio.biblionumber=biblioitems.biblionumber
526 and biblioitems.illus like '%".$search->{'illustrator'}."%'";
529 $query .=" group by biblio.biblionumber";
532 if ($type eq 'subject'){
533 my @key=split(' ',$search->{'subject'});
536 $query="select distinct(subject) from bibliosubject where( subject like
537 '$key[0]%' or subject like '% $key[0]%' or subject like '% $key[0]' or subject like '%($key[0])%')";
539 $query.=" and (subject like '$key[$i]%' or subject like '% $key[$i]%'
540 or subject like '% $key[$i]'
541 or subject like '%($key[$i])%')";
544 if ($search->{'subject'} eq 'NZ' || $search->{'subject'} eq 'nz'){
545 $query.= " or (subject like 'NEW ZEALAND %' or subject like '% NEW ZEALAND %'
546 or subject like '% NEW ZEALAND' or subject like '%(NEW ZEALAND)%' ) ";
547 } elsif ( $search->{'subject'} =~ /^nz /i || $search->{'subject'} =~ / nz /i || $search->{'subject'} =~ / nz$/i){
548 $query=~ s/ nz/ NEW ZEALAND/ig;
549 $query=~ s/nz /NEW ZEALAND /ig;
550 $query=~ s/\(nz\)/\(NEW ZEALAND\)/gi;
553 if ($type eq 'precise'){
554 $query="select * from items,biblio ";
555 if ($search->{'item'} ne ''){
556 my $search2=uc $search->{'item'};
557 $query=$query." where
558 items.biblionumber=biblio.biblionumber
559 and barcode='$search2'";
561 if ($search->{'isbn'} ne ''){
562 my $search2=uc $search->{'isbn'};
563 my $query1 = "select * from biblioitems where isbn='$search2'";
564 my $sth1=$dbh->prepare($query1);
568 while (my $data=$sth1->fetchrow_hashref) {
569 $query="select * from biblioitems,biblio where
570 biblio.biblionumber = $data->{'biblionumber'}
571 and biblioitems.biblionumber = biblio.biblionumber";
572 my $sth=$dbh->prepare($query);
574 my $data=$sth->fetchrow_hashref;
575 my ($dewey, $subclass) = ($data->{'dewey'}, $data->{'subclass'});
577 ($dewey == 0) && ($dewey='');
578 ($dewey) && ($dewey.=" $subclass");
579 $results[$i2]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey\t$data->{'isbn'}\t$data->{'itemtype'}";
587 if ($type ne 'precise' && $type ne 'subject'){
588 if ($search->{'author'} ne ''){
589 $query=$query." order by biblio.author,title";
591 $query=$query." order by title";
594 if ($type eq 'subject'){
595 $query=$query." order by subject";
598 my $sth=$dbh->prepare($query);
602 my $limit= $num+$offset;
603 while (my $data=$sth->fetchrow_hashref){
604 my $sti=$dbh->prepare("select dewey,subclass from biblioitems where biblionumber=$data->{'biblionumber'}");
606 my ($dewey, $subclass) = $sti->fetchrow;
608 ($dewey == 0) && ($dewey='');
609 ($dewey) && ($dewey.=" $subclass");
611 if ($count > $offset && $count <= $limit){
612 if ($type ne 'subject' && $type ne 'precise'){
613 $results[$i]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey\t$data->{'illus'}";
614 } elsif ($search->{'isbn'} ne '' || $search->{'item'} ne ''){
615 $results[$i]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey\t$data->{'illus'}";
617 $results[$i]="$data->{'author'}\t$data->{'subject'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey\t$data->{'illus'}";
624 #if ($type ne 'precise'){
628 return($count,@results);
631 sub updatesearchstats{
637 my ($env,$subject)=@_;
639 my $query="Select * from biblio,bibliosubject where
640 biblio.biblionumber=bibliosubject.biblionumber and
641 bibliosubject.subject='$subject' group by biblio.biblionumber
642 order by biblio.title";
643 my $sth=$dbh->prepare($query);
648 while (my $data=$sth->fetchrow_hashref){
649 $results[$i]="$data->{'title'}\t$data->{'author'}\t$data->{'biblionumber'}";
659 my ($env,$biblionumber,$type)=@_;
660 my $dbh = &C4Connect;
661 my $query="Select * from items,biblio,biblioitems,branches
662 where (items.biblioitemnumber = biblioitems.biblioitemnumber)
663 and biblioitems.biblionumber=biblio.biblionumber
664 and biblio.biblionumber='$biblionumber' and branches.branchcode=
665 items.holdingbranch ";
667 if ($type ne 'intra'){
668 $query.=" and (items.itemlost<>1 or items.itemlost is NULL)
669 and (wthdrawn <> 1 or wthdrawn is NULL)";
671 $query=$query."order by items.dateaccessioned desc";
672 my $sth=$dbh->prepare($query);
677 while (my $data=$sth->fetchrow_hashref){
678 my $iquery = "Select * from issues
679 where itemnumber = '$data->{'itemnumber'}'
680 and returndate is null";
682 my $isth=$dbh->prepare($iquery);
684 if (my $idata=$isth->fetchrow_hashref){
685 my @temp=split('-',$idata->{'date_due'});
686 $datedue = "$temp[2]/$temp[1]/$temp[0]";
688 if ($data->{'itemlost'} eq '1'){
691 if ($data->{'wthdrawn'} eq '1'){
692 $datedue="Cancelled";
695 my ($rescount,$reserves)=FindReserves($biblionumber,'');
701 my $class = $data->{'classification'};
702 my $dewey = $data->{'dewey'};
704 if ($dewey eq "000.") { $dewey = "";};
705 if ($dewey < 10){$dewey='00'.$dewey;}
706 if ($dewey < 100 && $dewey > 10){$dewey='0'.$dewey;}
711 $class = $class.$dewey;
712 $class = $class.$data->{'subclass'};
713 # $results[$i]="$data->{'title'}\t$data->{'barcode'}\t$datedue\t$data->{'branchname'}\t$data->{'dewey'}";
714 my @temp=split('-',$data->{'datelastseen'});
715 my $date="$temp[2]/$temp[1]/$temp[0]";
716 $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'}";
717 # print "$results[$i] <br>";
726 my ($env,$biblionumber)=@_;
727 #debug_msg($env,"GetItems");
728 my $dbh = &C4Connect;
729 my $query = "Select * from biblioitems where (biblionumber = $biblionumber)";
730 #debug_msg($env,$query);
731 my $sth=$dbh->prepare($query);
733 #debug_msg($env,"executed query");
736 while (my $data=$sth->fetchrow_hashref) {
737 #debug_msg($env,$data->{'biblioitemnumber'});
738 my $dewey = $data->{'dewey'};
740 my $line = $data->{'biblioitemnumber'}."\t".$data->{'itemtype'};
741 $line = $line."\t$data->{'classification'}\t$dewey";
742 $line = $line."\t$data->{'subclass'}\t$data->{isbn}";
743 $line = $line."\t$data->{'volume'}\t$data->{number}";
744 my $isth= $dbh->prepare("select * from items where biblioitemnumber = $data->{'biblioitemnumber'}");
746 while (my $idata = $isth->fetchrow_hashref) {
747 my $iline = $idata->{'barcode'}."[".$idata->{'holdingbranch'}."[";
748 if ($idata->{'notforloan'} == 1) {
749 $iline = $iline."NFL ";
751 if ($idata->{'itemlost'} == 1) {
752 $iline = $iline."LOST ";
754 $line = $line."\t$iline";
757 $results[$i] = $line;
768 my $query="Select * from items,biblioitems where barcode='$barcode'
769 and items.biblioitemnumber=biblioitems.biblioitemnumber";
771 my $sth=$dbh->prepare($query);
773 my $data=$sth->fetchrow_hashref;
780 my ($bibnum,$type)=@_;
782 my $query="Select *,biblio.notes from biblio,biblioitems,bibliosubtitle where biblio.biblionumber=$bibnum
783 and biblioitems.biblionumber=$bibnum and
784 (bibliosubtitle.biblionumber=$bibnum)";
786 my $sth=$dbh->prepare($query);
788 my $data=$sth->fetchrow_hashref;
790 $query="Select * from bibliosubject where biblionumber='$bibnum'";
791 $sth=$dbh->prepare($query);
793 while (my $dat=$sth->fetchrow_hashref){
794 $data->{'subject'}.=" | $dat->{'subject'}";
806 my $query="Select * from biblio,biblioitems,itemtypes where biblio.biblionumber=
807 biblioitems.biblionumber and biblioitemnumber=$bibitem and
808 biblioitems.itemtype=itemtypes.itemtype";
810 my $sth=$dbh->prepare($query);
812 my $data=$sth->fetchrow_hashref;
821 my $query="Select * from bibliosubject 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 additionalauthors where biblionumber=$bibnum";
839 my $sth=$dbh->prepare($query);
843 while (my $data=$sth->fetchrow_hashref){
849 return($i,\@results);
855 my $query="Select * from bibliosubtitle where biblionumber=$bibnum";
856 my $sth=$dbh->prepare($query);
860 while (my $data=$sth->fetchrow_hashref){
866 return($i,\@results);
872 my ($bibitem,$biblio)=@_;
874 my $query="Select * from items where
875 items.biblioitemnumber='$bibitem'";
876 my $sth=$dbh->prepare($query) || die $dbh->errstr;
877 $sth->execute || die $sth->errstr;
880 while (my $data=$sth->fetchrow_hashref) {
881 my $query2="select * from issues,borrowers where itemnumber=$data->{'itemnumber'}
882 and returndate is NULL and issues.borrowernumber=borrowers.borrowernumber";
883 my $sth2=$dbh->prepare($query2);
885 if (my $data2=$sth2->fetchrow_hashref) {
886 $data->{'date_due'}=$data2->{'date_due'};
887 $data->{'card'}=$data2->{'cardnumber'};
889 if ($data->{'wthdrawn'} eq '1') {
890 $data->{'date_due'}='Cancelled';
892 $data->{'date_due'}='Available';
896 $query2="select * from issues,borrowers where itemnumber='$data->{'itemnumber'}'
897 and issues.borrowernumber=borrowers.borrowernumber
898 order by date_due desc";
899 my $sth2=$dbh->prepare($query2) || die $dbh->errstr;
900 $sth2->execute || die $sth2->errstr;
901 for (my $i2=0;$i2<2;$i2++){
902 if (my $data2=$sth2->fetchrow_hashref){
903 $data->{"timestamp$i2"}=$data2->{'timestamp'};
904 $data->{"card$i2"}=$data2->{'cardnumber'};
905 $data->{"borrower$i2"}=$data2->{'borrowernumber'};
918 my ($env,$dbh,$itemnumber) = @_;
920 my $query="Select * from biblio,items,biblioitems
921 where items.itemnumber = '$itemnumber'
922 and biblio.biblionumber = items.biblionumber
923 and biblioitems.biblioitemnumber = items.biblioitemnumber";
924 my $sth=$dbh->prepare($query);
927 my $data=$sth->fetchrow_hashref;
933 #used by member enquiries from the intranet
936 my ($env,$searchstring,$type)=@_;
937 my $dbh = &C4Connect;
938 $searchstring=~ s/\'/\\\'/g;
939 my @data=split(' ',$searchstring);
941 my $query="Select * from borrowers
942 where ((surname like \"$data[0]%\" or surname like \"% $data[0]%\"
943 or firstname like \"$data[0]%\" or firstname like \"% $data[0]%\"
944 or othernames like \"$data[0]%\" or othernames like \"% $data[0]%\")
946 for (my $i=1;$i<$count;$i++){
947 $query=$query." and (surname like \"$data[$i]%\" or surname like \"% $data[$i]%\"
948 or firstname like \"$data[$i]%\" or firstname like \"% $data[$i]%\"
949 or othernames like \"$data[$i]%\" or othernames like \"% $data[$i]%\")";
951 $query=$query.") or cardnumber = \"$searchstring\"
952 order by surname,firstname";
954 my $sth=$dbh->prepare($query);
958 while (my $data=$sth->fetchrow_hashref){
959 push(@results,$data);
965 return ($cnt,\@results);
969 my ($cardnumber,$bornum)=@_;
970 $cardnumber = uc $cardnumber;
974 $query="Select * from borrowers where cardnumber='$cardnumber'";
976 $query="Select * from borrowers where borrowernumber='$bornum'";
979 my $sth=$dbh->prepare($query);
981 my $data=$sth->fetchrow_hashref;
991 $query="Select * from issues,biblio,items where borrowernumber='$bornum' and
992 items.itemnumber=issues.itemnumber and
993 items.biblionumber=biblio.biblionumber and issues.returndate is NULL order
996 my $sth=$dbh->prepare($query);
1000 while (my $data=$sth->fetchrow_hashref){
1006 return($i,\@result);
1013 $query="Select * from issues,biblio,items where borrowernumber='$bornum' and
1014 items.itemnumber=issues.itemnumber and
1015 items.biblionumber=biblio.biblionumber order
1018 my $sth=$dbh->prepare($query);
1022 while (my $data=$sth->fetchrow_hashref){
1028 return($i,\@result);
1034 my ($env,$bornum)=@_;
1036 my $query="Select count(*) from issues where borrowernumber='$bornum' and
1037 returndate is NULL";
1039 my $sth=$dbh->prepare($query);
1041 my $data=$sth->fetchrow_hashref;
1043 $sth=$dbh->prepare("Select count(*) from issues where
1044 borrowernumber='$bornum' and date_due < now() and returndate is NULL");
1046 my $data2=$sth->fetchrow_hashref;
1048 $sth=$dbh->prepare("Select sum(amountoutstanding) from accountlines where
1049 borrowernumber='$bornum'");
1051 my $data3=$sth->fetchrow_hashref;
1055 return($data2->{'count(*)'},$data->{'count(*)'},$data3->{'sum(amountoutstanding)'});
1059 my ($env,$params) = @_;
1063 my $query = "Select borrowernumber, accountno, date, amount, description,
1064 dispute, accounttype, amountoutstanding, barcode, title
1065 from accountlines,items,biblio
1066 where borrowernumber = $params->{'borrowernumber'} ";
1067 if ($params->{'acctno'} ne "") {
1068 my $query = $query." and accountlines.accountno = $params->{'acctno'} ";
1070 my $query = $query." and accountlines.itemnumber = items.itemnumber
1071 and items.biblionumber = biblio.biblionumber
1072 and accountlines.amountoutstanding<>0 order by date";
1073 my $sth=$dbh->prepare($query);
1077 while (my $data=$sth->fetchrow_hashref){
1078 $acctlines[$numlines] = $data;
1080 $total = $total+ $data->{'amountoutstanding'};
1082 return ($numlines,\@acctlines,$total);
1087 sub getboracctrecord {
1088 my ($env,$params) = @_;
1092 my $query= "Select * from accountlines where
1093 borrowernumber=$params->{'borrowernumber'} order by date desc,timestamp desc";
1094 my $sth=$dbh->prepare($query);
1098 while (my $data=$sth->fetchrow_hashref){
1099 # if ($data->{'itemnumber'} ne ''){
1100 # $query="Select * from items,biblio where items.itemnumber=
1101 # '$data->{'itemnumber'}' and biblio.biblionumber=items.biblionumber";
1102 # my $sth2=$dbh->prepare($query);
1104 # my $data2=$sth2->fetchrow_hashref;
1108 $acctlines[$numlines] = $data;
1110 $total = $total+ $data->{'amountoutstanding'};
1114 return ($numlines,\@acctlines,$total);
1118 my ($env,$bibnum,$type)=@_;
1120 my $query="Select * from items where
1121 biblionumber=$bibnum ";
1122 if ($type ne 'intra'){
1123 $query.=" and (itemlost <>1 or itemlost is NULL) and
1124 (wthdrawn <> 1 or wthdrawn is NULL)";
1126 my $sth=$dbh->prepare($query);
1138 while (my $data=$sth->fetchrow_hashref){
1140 my $query2="select * from issues,items where issues.itemnumber=
1141 '$data->{'itemnumber'}' and returndate is NULL
1142 and items.itemnumber=issues.itemnumber and (items.itemlost <>1 or
1143 items.itemlost is NULL)";
1144 my $sth2=$dbh->prepare($query2);
1146 if (my $data2=$sth2->fetchrow_hashref){
1149 if ($data->{'holdingbranch'} eq 'C'){
1152 if ($data->{'holdingbranch'} eq 'F' || $data->{'holdingbranch'} eq 'FP'){
1155 if ($data->{'holdingbranch'} eq 'S' || $data->{'holdingbranch'} eq 'SP'){
1158 if ($data->{'itemlost'} eq '1'){
1161 if ($data->{'holdingbranch'} eq 'FM'){
1164 if ($data->{'holdingbranch'} eq 'TR'){
1171 my $query2="Select * from aqorders where biblionumber=$bibnum";
1172 my $sth2=$dbh->prepare($query2);
1174 if (my $data=$sth2->fetchrow_hashref){
1175 $ocount=$data->{'quantity'} - $data->{'quantityreceived'};
1181 return ($count,$lcount,$nacount,$fcount,$scount,$lostcount,$mending,$transit,$ocount);
1187 my $query="select description from itemtypes where itemtype='$type'";
1188 my $sth=$dbh->prepare($query);
1190 my $dat=$sth->fetchrow_hashref;
1193 return ($dat->{'description'});
1199 my $query="Select * from biblioitems,itemtypes,items where
1200 biblioitems.biblionumber='$bibnum' and biblioitems.itemtype=itemtypes.itemtype and
1201 biblioitems.biblioitemnumber=items.biblioitemnumber group by
1202 items.biblioitemnumber";
1203 my $sth=$dbh->prepare($query);
1207 while (my $data=$sth->fetchrow_hashref){
1213 return($i,@results);
1217 my ($biblioitemnumber)=@_;
1219 my $query="Select barcode from items where
1220 biblioitemnumber='$biblioitemnumber'";
1221 my $sth=$dbh->prepare($query);
1225 while (my $data=$sth->fetchrow_hashref){
1226 $barcodes[$i]=$data->{'barcode'};
1234 END { } # module clean-up code here (global destructor)