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);
23 %EXPORT_TAGS = ( ); # eg: TAG => [ qw!name1 name2! ],
25 # your exported package globals go here,
26 # as well as any optionally exported functions
28 @EXPORT_OK = qw($Var1 %Hashit);
31 # non-exported package globals go here
32 use vars qw(@more $stuff);
34 # initalize package globals, first exported ones
39 # then the others (which are still accessible as $Some::Module::stuff)
43 # all file-scoped lexicals must be created before
44 # the functions below that use them.
46 # file-private lexicals go here
50 # here's a file-private function as a closure,
51 # callable as &$priv_func; it cannot be prototyped.
56 # make all your functions, whether exported or not;
60 my $query="select cardnumber,borrowernumber from borrowers where
62 my $sth=$dbh->prepare($query);
66 while (my $data=$sth->fetchrow_hashref){
79 my $sth=$dbh->prepare("select variable,value from systempreferences");
81 while (my ($variable,$value)=$sth->fetchrow) {
82 $systemprefs{$variable}=$value;
89 sub NewBorrowerNumber {
91 my $sth=$dbh->prepare("Select max(borrowernumber) from borrowers");
93 my $data=$sth->fetchrow_hashref;
95 $data->{'max(borrowernumber)'}++;
97 return($data->{'max(borrowernumber)'});
101 my ($env,$type,$search,$num,$offset)=@_;
102 my $dbh = &C4Connect;
103 $search->{'keyword'}=~ s/'/\\'/g;
104 my @key=split(' ',$search->{'keyword'});
108 my $query ="Select count(*) from biblio where
109 ((title like '$key[0]%' or title like '% $key[0]%')";
111 $query=$query." and (title like '$key[$i]%' or title like '% $key[$i]%')";
114 $query=$query.") or ((author like '$key[0]%' or author like '% $key[0]%')";
117 $query=$query." and (author like '$key[$i]%' or author like '% $key[$i]%')";
120 $query.=") or ((seriestitle like '$key[0]%' or seriestitle like '% $key[0]%')";
121 for ($i=1;$i<$count;$i++){
122 $query.=" and (seriestitle like '$key[$i]%' or seriestitle like '% $key[$i]%')";
124 $query.= ") or ((notes like '$key[0]%' or notes like '% $key[0]%')";
125 for ($i=1;$i<$count;$i++){
126 $query.=" and (notes like '$key[$i]%' or notes like '% $key[$i]%')";
128 $query=$query.") order by title";
129 my $sth=$dbh->prepare($query);
131 my $data=$sth->fetchrow_hashref;
132 my $count=$data->{'count(*)'};
134 $query=~ s/count\(\*\)/\*/;
135 $query= $query." limit $offset,$num";
136 $sth=$dbh->prepare($query);
140 while (my $data=$sth->fetchrow_hashref){
141 my $sti=$dbh->prepare("select dewey,subclass from biblioitems where biblionumber=$data->{'biblionumber'}");
143 my ($dewey, $subclass) = $sti->fetchrow;
145 ($dewey == 0) && ($dewey='');
146 ($dewey) && ($dewey.=" $subclass");
148 $results[$i]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$dewey";
153 return($count,@results);
159 my ($env,$type,$search,$num,$offset)=@_;
160 my $dbh = &C4Connect;
161 $search->{'front'}=~ s/ +$//;
162 $search->{'front'}=~ s/'/\\'/;
163 my @key=split(' ',$search->{'front'});
167 my $query ="Select * from biblio,bibliosubtitle where
168 biblio.biblionumber=bibliosubtitle.biblionumber and
169 ((title like '$key[0]%' or title like '% $key[0]%'
170 or subtitle like '$key[0]%' or subtitle like '% $key[0]%'
171 or author like '$key[0]%' or author like '% $key[0]%')";
173 $query=$query." and (title like '%$key[$i]%' or subtitle like '%$key[$i]%')";
176 $query=$query.") group by biblio.biblionumber order by author,title";
178 my $sth=$dbh->prepare($query);
181 while (my $data=$sth->fetchrow_hashref){
182 my $sti=$dbh->prepare("select dewey,subclass from biblioitems where biblionumber=$data->{'biblionumber'}");
184 my ($dewey, $subclass) = $sti->fetchrow;
186 ($dewey == 0) && ($dewey='');
187 ($dewey) && ($dewey.=" $subclass");
189 $results[$i]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey";
190 # print $results[$i];
194 $sth=$dbh->prepare("Select biblionumber from bibliosubject where subject
195 like '%$search->{'keyword'}%'");
197 while (my $data=$sth->fetchrow_hashref){
198 my $sth2=$dbh->prepare("Select * from biblio where
199 biblionumber=$data->{'biblionumber'}");
201 while (my $data2=$sth2->fetchrow_hashref){
203 $results[$i]="$data2->{'author'}\t$data2->{'title'}\t$data2->{'biblionumber'}\t$data->{'copyrightdate'}";
204 # print $results[$i];
210 @results=sort @results;
215 while ($i2 < $count){
216 if ($results[$i2] ne $res[$i-1]){
217 $res[$i]=$results[$i2];
225 while ($i2 < $num && $i2 < $count){
226 $res2[$i2]=$res[$i2+$offset];
237 my ($env,$type,$search,$num,$offset)=@_;
238 my $dbh = &C4Connect;
239 $search->{'keyword'}=~ s/ +$//;
240 $search->{'keyword'}=~ s/'/\\'/;
241 my @key=split(' ',$search->{'keyword'});
245 my $query ="Select * from biblio,bibliosubtitle,biblioitems where
246 biblio.biblionumber=bibliosubtitle.biblionumber and
247 biblioitems.biblionumber=biblio.biblionumber and
248 (((title like '$key[0]%' or title like '% $key[0]%')";
250 $query=$query." and (title like '$key[$i]%' or title like '% $key[$i]%')";
253 $query.= ") or ((subtitle like '$key[0]%' or subtitle like '% $key[0]%')";
254 for ($i=1;$i<$count;$i++){
255 $query.= " and (subtitle like '$key[$i]%' or subtitle like '% $key[$i]%')";
257 $query.= ") or ((seriestitle like '$key[0]%' or seriestitle like '% $key[0]%')";
258 for ($i=1;$i<$count;$i++){
259 $query.=" and (seriestitle like '$key[$i]%' or seriestitle like '% $key[$i]%')";
261 $query.= ") or ((biblio.notes like '$key[0]%' or biblio.notes like '% $key[0]%')";
262 for ($i=1;$i<$count;$i++){
263 $query.=" and (biblio.notes like '$key[$i]%' or biblio.notes like '% $key[$i]%')";
265 $query.= ") or ((biblioitems.notes like '$key[0]%' or biblioitems.notes like '% $key[0]%')";
266 for ($i=1;$i<$count;$i++){
267 $query.=" and (biblioitems.notes like '$key[$i]%' or biblioitems.notes like '% $key[$i]%')";
269 if ($search->{'keyword'} =~ /new zealand/i){
270 $query.= "or (title like 'nz%' or title like '% nz %' or title like '% nz' or subtitle like 'nz%'
271 or subtitle like '% nz %' or subtitle like '% nz' or author like 'nz %'
272 or author like '% nz %' or author like '% nz')"
274 if ($search->{'keyword'} eq 'nz' || $search->{'keyword'} eq 'NZ' ||
275 $search->{'keyword'} =~ /nz /i || $search->{'keyword'} =~ / nz /i ||
276 $search->{'keyword'} =~ / nz/i){
277 $query.= "or (title like 'new zealand%' or title like '% new zealand %'
278 or title like '% new zealand' or subtitle like 'new zealand%' or
279 subtitle like '% new zealand %'
280 or subtitle like '% new zealand' or author like 'new zealand%'
281 or author like '% new zealand %' or author like '% new zealand' or
282 seriestitle like 'new zealand%' or seriestitle like '% new zealand %'
283 or seriestitle like '% new zealand')"
286 if ($search->{'class'} ne ''){
287 my @temp=split(/\|/,$search->{'class'});
289 $query.= "and ( itemtype='$temp[0]'";
290 for (my $i=1;$i<$count;$i++){
291 $query.=" or itemtype='$temp[$i]'";
295 $query.="group by biblio.biblionumber order by author,title";
297 my $sth=$dbh->prepare($query);
300 while (my $data=$sth->fetchrow_hashref){
301 my $sti=$dbh->prepare("select dewey,subclass from biblioitems where biblionumber=$data->{'biblionumber'}");
303 my ($dewey, $subclass) = $sti->fetchrow;
305 ($dewey == 0) && ($dewey='');
306 ($dewey) && ($dewey.=" $subclass");
308 $results[$i]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey";
309 # print $results[$i];
313 $sth=$dbh->prepare("Select biblionumber from bibliosubject where subject
314 like '%$search->{'keyword'}%'");
316 while (my $data=$sth->fetchrow_hashref){
317 $query="Select * from biblio,biblioitems where
318 biblio.biblionumber=$data->{'biblionumber'} and biblio.biblionumber=biblioitems.biblionumber";
319 if ($search->{'class'} ne ''){
320 my @temp=split(/\|/,$search->{'class'});
322 $query.= " and ( itemtype='$temp[0]'";
323 for (my $i=1;$i<$count;$i++){
324 $query.=" or itemtype='$temp[$i]'";
328 my $sth2=$dbh->prepare($query);
331 while (my $data2=$sth2->fetchrow_hashref){
332 $results[$i]="$data2->{'author'}\t$data2->{'title'}\t$data2->{'biblionumber'}\t$data2->{'copyrightdate'}";
333 # print $results[$i];
339 @results=sort @results;
346 while ($i2 < $count){
347 if ($results[$i2] ne $res[$i-1]){
348 $res[$i]=$results[$i2];
356 while ($i2 < $num && $i2 < $count){
357 $res2[$i2]=$res[$i2+$offset];
368 my ($env,$type,$search,$num,$offset)=@_;
369 my $dbh = &C4Connect;
372 $search->{'title'}=~ s/'/\\'/g;
373 $search->{'author'}=~ s/'/\\'/g;
374 $search->{'illustrator'}=~ s/'/\\'/g;
375 my $title = lc($search->{'title'});
377 if ($type eq 'loose') {
378 if ($search->{'author'} ne ''){
379 my @key=split(' ',$search->{'author'});
382 $query="select *,biblio.author,biblio.biblionumber from
384 left join additionalauthors
385 on additionalauthors.biblionumber =biblio.biblionumber
386 where biblioitems.biblionumber=biblio.biblionumber
388 ((biblio.author like '$key[0]%' or biblio.author like '% $key[0]%' or
389 additionalauthors.author like '$key[0]%' or additionalauthors.author
393 $query=$query." and (
394 biblio.author like '$key[$i]%' or biblio.author like '% $key[$i]%' or
395 additionalauthors.author like '$key[$i]%' or additionalauthors.author like '% $key[$i]%'
400 if ($search->{'title'} ne ''){
401 $query=$query. " and title like '%$search->{'title'}%'";
403 if ($search->{'class'} ne ''){
404 my @temp=split(/\|/,$search->{'class'});
406 $query.= "and ( itemtype='$temp[0]'";
407 for (my $i=1;$i<$count;$i++){
408 $query.=" or itemtype='$temp[$i]'";
412 if ($search->{'dewey'} ne ''){
413 $query.=" and dewey='$search->{'dewey'}' ";
415 if ($search->{'illustrator'} ne ''){
416 $query.=" and illus like '%".$search->{'illustrator'}."%' ";
419 $query.=" group by biblio.biblionumber";
421 if ($search->{'title'} ne '') {
422 if ($search->{'ttype'} eq 'exact'){
423 $query="select * from biblio
425 (biblio.title='$search->{'title'}' or (biblio.unititle = '$search->{'title'}'
426 or biblio.unititle like '$search->{'title'} |%' or
427 biblio.unititle like '%| $search->{'title'} |%' or
428 biblio.unititle like '%| $search->{'title'}') or
429 (biblio.seriestitle = '$search->{'title'}' or
430 biblio.seriestitle like '$search->{'title'} |%' or
431 biblio.seriestitle like '%| $search->{'title'} |%' or
432 biblio.seriestitle like '%| $search->{'title'}')
435 my @key=split(' ',$search->{'title'});
438 $query="select * from biblio,bibliosubtitle,biblioitems
440 (biblio.biblionumber=bibliosubtitle.biblionumber and
441 biblioitems.biblionumber=biblio.biblionumber) and
442 (((title like '$key[0]%' or title like '% $key[0]%' or title like '% $key[0]')";
444 $query=$query." and (title like '$key[$i]%' or title like '% $key[$i]%' or title like '% $key[$i]')";
447 $query.=") or ((subtitle like '$key[0]%' or subtitle like '% $key[0] %' or subtitle like '% $key[0]')";
448 for ($i=1;$i<$count;$i++){
449 $query.=" and (subtitle like '$key[$i]%' or subtitle like '% $key[$i] %' or subtitle like '% $key[$i]')";
451 $query.=") or ((seriestitle like '$key[0]%' or seriestitle like '% $key[0] %' or seriestitle like '% $key[0]')";
452 for ($i=1;$i<$count;$i++){
453 $query.=" and (seriestitle like '$key[$i]%' or seriestitle like '% $key[$i] %')";
455 $query.=") or ((unititle like '$key[0]%' or unititle like '% $key[0] %' or unititle like '% $key[0]')";
456 for ($i=1;$i<$count;$i++){
457 $query.=" and (unititle like '$key[$i]%' or unititle like '% $key[$i] %')";
460 if ($search->{'class'} ne ''){
461 my @temp=split(/\|/,$search->{'class'});
463 $query.= " and ( itemtype='$temp[0]'";
464 for (my $i=1;$i<$count;$i++){
465 $query.=" or itemtype='$temp[$i]'";
469 if ($search->{'dewey'} ne ''){
470 $query.=" and dewey='$search->{'dewey'}' ";
472 if ($search->{'illustrator'} ne ''){
473 $query.=" and illus like '%".$search->{'illustrator'}."%' ";
476 } elsif ($search->{'class'} ne ''){
477 $query="select * from biblioitems,biblio where biblio.biblionumber=biblioitems.biblionumber";
478 my @temp=split(/\|/,$search->{'class'});
480 $query.= " and ( itemtype='$temp[0]'";
481 for (my $i=1;$i<$count;$i++){
482 $query.=" or itemtype='$temp[$i]'";
485 if ($search->{'illustrator'} ne ''){
486 $query.=" and illus like '%".$search->{'illustrator'}."%' ";
488 if ($search->{'dewey'} ne ''){
489 $query.=" and biblioitems.dewey like '$search->{'dewey'}%'";
491 } elsif ($search->{'dewey'} ne ''){
492 $query="select * from biblioitems,biblio
493 where biblio.biblionumber=biblioitems.biblionumber
494 and biblioitems.dewey like '$search->{'dewey'}%'";
495 } elsif ($search->{'illustrator'} ne '') {
496 if ($search->{'illustrator'} ne ''){
497 $query="select * from biblioitems,biblio
498 where biblio.biblionumber=biblioitems.biblionumber
499 and biblioitems.illus like '%".$search->{'illustrator'}."%'";
502 $query .=" group by biblio.biblionumber";
505 if ($type eq 'subject'){
506 my @key=split(' ',$search->{'subject'});
509 $query="select distinct(subject) from bibliosubject where( subject like
510 '$key[0]%' or subject like '% $key[0]%' or subject like '% $key[0]' or subject like '%($key[0])%')";
512 $query.=" and (subject like '$key[$i]%' or subject like '% $key[$i]%'
513 or subject like '% $key[$i]'
514 or subject like '%($key[$i])%')";
517 if ($search->{'subject'} eq 'NZ' || $search->{'subject'} eq 'nz'){
518 $query.= " or (subject like 'NEW ZEALAND %' or subject like '% NEW ZEALAND %'
519 or subject like '% NEW ZEALAND' or subject like '%(NEW ZEALAND)%' ) ";
520 } elsif ( $search->{'subject'} =~ /^nz /i || $search->{'subject'} =~ / nz /i || $search->{'subject'} =~ / nz$/i){
521 $query=~ s/ nz/ NEW ZEALAND/ig;
522 $query=~ s/nz /NEW ZEALAND /ig;
523 $query=~ s/\(nz\)/\(NEW ZEALAND\)/gi;
526 if ($type eq 'precise'){
527 $query="select * from items,biblio ";
528 if ($search->{'item'} ne ''){
529 my $search2=uc $search->{'item'};
530 $query=$query." where
531 items.biblionumber=biblio.biblionumber
532 and barcode='$search2'";
534 if ($search->{'isbn'} ne ''){
535 my $search2=uc $search->{'isbn'};
536 my $query1 = "select * from biblioitems where isbn='$search2'";
537 my $sth1=$dbh->prepare($query1);
541 while (my $data=$sth1->fetchrow_hashref) {
542 $query="select * from biblioitems,biblio where
543 biblio.biblionumber = $data->{'biblionumber'}
544 and biblioitems.biblionumber = biblio.biblionumber";
545 my $sth=$dbh->prepare($query);
547 my $data=$sth->fetchrow_hashref;
548 my ($dewey, $subclass) = ($data->{'dewey'}, $data->{'subclass'});
550 ($dewey == 0) && ($dewey='');
551 ($dewey) && ($dewey.=" $subclass");
552 $results[$i2]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey\t$data->{'isbn'}\t$data->{'itemtype'}";
560 if ($type ne 'precise' && $type ne 'subject'){
561 if ($search->{'author'} ne ''){
562 $query=$query." order by biblio.author,title";
564 $query=$query." order by title";
567 if ($type eq 'subject'){
568 $query=$query." order by subject";
571 my $sth=$dbh->prepare($query);
575 my $limit= $num+$offset;
576 while (my $data=$sth->fetchrow_hashref){
577 my $sti=$dbh->prepare("select dewey,subclass from biblioitems where biblionumber=$data->{'biblionumber'}");
579 my ($dewey, $subclass) = $sti->fetchrow;
581 ($dewey == 0) && ($dewey='');
582 ($dewey) && ($dewey.=" $subclass");
584 if ($count > $offset && $count <= $limit){
585 if ($type ne 'subject' && $type ne 'precise'){
586 $results[$i]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey\t$data->{'illus'}";
587 } elsif ($search->{'isbn'} ne '' || $search->{'item'} ne ''){
588 $results[$i]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey\t$data->{'illus'}";
590 $results[$i]="$data->{'author'}\t$data->{'subject'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey\t$data->{'illus'}";
597 #if ($type ne 'precise'){
601 return($count,@results);
604 sub updatesearchstats{
610 my ($env,$subject)=@_;
612 my $query="Select * from biblio,bibliosubject where
613 biblio.biblionumber=bibliosubject.biblionumber and
614 bibliosubject.subject='$subject' group by biblio.biblionumber
615 order by biblio.title";
616 my $sth=$dbh->prepare($query);
621 while (my $data=$sth->fetchrow_hashref){
622 $results[$i]="$data->{'title'}\t$data->{'author'}\t$data->{'biblionumber'}";
632 my ($env,$biblionumber,$type)=@_;
633 my $dbh = &C4Connect;
634 my $query="Select * from items,biblio,biblioitems,branches
635 where (items.biblioitemnumber = biblioitems.biblioitemnumber)
636 and biblioitems.biblionumber=biblio.biblionumber
637 and biblio.biblionumber='$biblionumber' and branches.branchcode=
638 items.holdingbranch ";
640 if ($type ne 'intra'){
641 $query.=" and (items.itemlost<>1 or items.itemlost is NULL)
642 and (wthdrawn <> 1 or wthdrawn is NULL)";
644 $query=$query."order by items.dateaccessioned desc";
645 my $sth=$dbh->prepare($query);
650 while (my $data=$sth->fetchrow_hashref){
651 my $iquery = "Select * from issues
652 where itemnumber = '$data->{'itemnumber'}'
653 and returndate is null";
655 my $isth=$dbh->prepare($iquery);
657 if (my $idata=$isth->fetchrow_hashref){
658 my @temp=split('-',$idata->{'date_due'});
659 $datedue = "$temp[2]/$temp[1]/$temp[0]";
661 if ($data->{'itemlost'} eq '1'){
664 if ($data->{'wthdrawn'} eq '1'){
665 $datedue="Cancelled";
668 my ($rescount,$reserves)=FindReserves($biblionumber,'');
674 my $class = $data->{'classification'};
675 my $dewey = $data->{'dewey'};
677 if ($dewey eq "000.") { $dewey = "";};
678 if ($dewey < 10){$dewey='00'.$dewey;}
679 if ($dewey < 100 && $dewey > 10){$dewey='0'.$dewey;}
684 $class = $class.$dewey;
685 $class = $class.$data->{'subclass'};
686 # $results[$i]="$data->{'title'}\t$data->{'barcode'}\t$datedue\t$data->{'branchname'}\t$data->{'dewey'}";
687 my @temp=split('-',$data->{'datelastseen'});
688 my $date="$temp[2]/$temp[1]/$temp[0]";
689 $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'}";
690 # print "$results[$i] <br>";
699 my ($env,$biblionumber)=@_;
700 #debug_msg($env,"GetItems");
701 my $dbh = &C4Connect;
702 my $query = "Select * from biblioitems where (biblionumber = $biblionumber)";
703 #debug_msg($env,$query);
704 my $sth=$dbh->prepare($query);
706 #debug_msg($env,"executed query");
709 while (my $data=$sth->fetchrow_hashref) {
710 #debug_msg($env,$data->{'biblioitemnumber'});
711 my $dewey = $data->{'dewey'};
713 my $line = $data->{'biblioitemnumber'}."\t".$data->{'itemtype'};
714 $line = $line."\t$data->{'classification'}\t$dewey";
715 $line = $line."\t$data->{'subclass'}\t$data->{isbn}";
716 $line = $line."\t$data->{'volume'}\t$data->{number}";
717 my $isth= $dbh->prepare("select * from items where biblioitemnumber = $data->{'biblioitemnumber'}");
719 while (my $idata = $isth->fetchrow_hashref) {
720 my $iline = $idata->{'barcode'}."[".$idata->{'holdingbranch'}."[";
721 if ($idata->{'notforloan'} == 1) {
722 $iline = $iline."NFL ";
724 if ($idata->{'itemlost'} == 1) {
725 $iline = $iline."LOST ";
727 $line = $line."\t$iline";
730 $results[$i] = $line;
741 my $query="Select * from items,biblioitems where barcode='$barcode'
742 and items.biblioitemnumber=biblioitems.biblioitemnumber";
744 my $sth=$dbh->prepare($query);
746 my $data=$sth->fetchrow_hashref;
753 my ($bibnum,$type)=@_;
755 my $query="Select *,biblio.notes from biblio,biblioitems,bibliosubtitle where biblio.biblionumber=$bibnum
756 and biblioitems.biblionumber=$bibnum and
757 (bibliosubtitle.biblionumber=$bibnum)";
759 my $sth=$dbh->prepare($query);
761 my $data=$sth->fetchrow_hashref;
763 $query="Select * from bibliosubject where biblionumber='$bibnum'";
764 $sth=$dbh->prepare($query);
766 while (my $dat=$sth->fetchrow_hashref){
767 $data->{'subject'}.=" | $dat->{'subject'}";
779 my $query="Select * from biblio,biblioitems,itemtypes where biblio.biblionumber=
780 biblioitems.biblionumber and biblioitemnumber=$bibitem and
781 biblioitems.itemtype=itemtypes.itemtype";
783 my $sth=$dbh->prepare($query);
785 my $data=$sth->fetchrow_hashref;
794 my $query="Select * from bibliosubject where biblionumber=$bibnum";
795 my $sth=$dbh->prepare($query);
799 while (my $data=$sth->fetchrow_hashref){
805 return($i,\@results);
811 my $query="Select * from additionalauthors where biblionumber=$bibnum";
812 my $sth=$dbh->prepare($query);
816 while (my $data=$sth->fetchrow_hashref){
822 return($i,\@results);
828 my $query="Select * from bibliosubtitle where biblionumber=$bibnum";
829 my $sth=$dbh->prepare($query);
833 while (my $data=$sth->fetchrow_hashref){
839 return($i,\@results);
845 my ($bibitem,$biblio)=@_;
847 my $query="Select * from items where
848 items.biblioitemnumber='$bibitem'";
849 my $sth=$dbh->prepare($query) || die $dbh->errstr;
850 $sth->execute || die $sth->errstr;
853 while (my $data=$sth->fetchrow_hashref) {
854 my $query2="select * from issues,borrowers where itemnumber=$data->{'itemnumber'}
855 and returndate is NULL and issues.borrowernumber=borrowers.borrowernumber";
856 my $sth2=$dbh->prepare($query2);
858 if (my $data2=$sth2->fetchrow_hashref) {
859 $data->{'date_due'}=$data2->{'date_due'};
860 $data->{'card'}=$data2->{'cardnumber'};
862 if ($data->{'wthdrawn'} eq '1') {
863 $data->{'date_due'}='Cancelled';
865 $data->{'date_due'}='Available';
869 $query2="select * from issues,borrowers where itemnumber='$data->{'itemnumber'}'
870 and issues.borrowernumber=borrowers.borrowernumber
871 order by date_due desc";
872 my $sth2=$dbh->prepare($query2) || die $dbh->errstr;
873 $sth2->execute || die $sth2->errstr;
874 for (my $i2=0;$i2<2;$i2++){
875 if (my $data2=$sth2->fetchrow_hashref){
876 $data->{"timestamp$i2"}=$data2->{'timestamp'};
877 $data->{"card$i2"}=$data2->{'cardnumber'};
878 $data->{"borrower$i2"}=$data2->{'borrowernumber'};
891 my ($env,$dbh,$itemnumber) = @_;
893 my $query="Select * from biblio,items,biblioitems
894 where items.itemnumber = '$itemnumber'
895 and biblio.biblionumber = items.biblionumber
896 and biblioitems.biblioitemnumber = items.biblioitemnumber";
897 my $sth=$dbh->prepare($query);
900 my $data=$sth->fetchrow_hashref;
906 #used by member enquiries from the intranet
909 my ($env,$searchstring,$type)=@_;
910 my $dbh = &C4Connect;
911 $searchstring=~ s/\'/\\\'/g;
912 my @data=split(' ',$searchstring);
914 my $query="Select * from borrowers
915 where ((surname like \"$data[0]%\" or surname like \"% $data[0]%\"
916 or firstname like \"$data[0]%\" or firstname like \"% $data[0]%\"
917 or othernames like \"$data[0]%\" or othernames like \"% $data[0]%\")
919 for (my $i=1;$i<$count;$i++){
920 $query=$query." and (surname like \"$data[$i]%\" or surname like \"% $data[$i]%\"
921 or firstname like \"$data[$i]%\" or firstname like \"% $data[$i]%\"
922 or othernames like \"$data[$i]%\" or othernames like \"% $data[$i]%\")";
924 $query=$query.") or cardnumber = \"$searchstring\"
925 order by surname,firstname";
927 my $sth=$dbh->prepare($query);
931 while (my $data=$sth->fetchrow_hashref){
932 push(@results,$data);
938 return ($cnt,\@results);
942 my ($cardnumber,$bornum)=@_;
943 $cardnumber = uc $cardnumber;
947 $query="Select * from borrowers where cardnumber='$cardnumber'";
949 $query="Select * from borrowers where borrowernumber='$bornum'";
952 my $sth=$dbh->prepare($query);
954 my $data=$sth->fetchrow_hashref;
964 $query="Select * from issues,biblio,items where borrowernumber='$bornum' and
965 items.itemnumber=issues.itemnumber and
966 items.biblionumber=biblio.biblionumber and issues.returndate is NULL order
969 my $sth=$dbh->prepare($query);
973 while (my $data=$sth->fetchrow_hashref){
986 $query="Select * from issues,biblio,items where borrowernumber='$bornum' and
987 items.itemnumber=issues.itemnumber and
988 items.biblionumber=biblio.biblionumber order
991 my $sth=$dbh->prepare($query);
995 while (my $data=$sth->fetchrow_hashref){
1001 return($i,\@result);
1007 my ($env,$bornum)=@_;
1009 my $query="Select count(*) from issues where borrowernumber='$bornum' and
1010 returndate is NULL";
1012 my $sth=$dbh->prepare($query);
1014 my $data=$sth->fetchrow_hashref;
1016 $sth=$dbh->prepare("Select count(*) from issues where
1017 borrowernumber='$bornum' and date_due < now() and returndate is NULL");
1019 my $data2=$sth->fetchrow_hashref;
1021 $sth=$dbh->prepare("Select sum(amountoutstanding) from accountlines where
1022 borrowernumber='$bornum'");
1024 my $data3=$sth->fetchrow_hashref;
1028 return($data2->{'count(*)'},$data->{'count(*)'},$data3->{'sum(amountoutstanding)'});
1032 my ($env,$params) = @_;
1036 my $query = "Select borrowernumber, accountno, date, amount, description,
1037 dispute, accounttype, amountoutstanding, barcode, title
1038 from accountlines,items,biblio
1039 where borrowernumber = $params->{'borrowernumber'} ";
1040 if ($params->{'acctno'} ne "") {
1041 my $query = $query." and accountlines.accountno = $params->{'acctno'} ";
1043 my $query = $query." and accountlines.itemnumber = items.itemnumber
1044 and items.biblionumber = biblio.biblionumber
1045 and accountlines.amountoutstanding<>0 order by date";
1046 my $sth=$dbh->prepare($query);
1050 while (my $data=$sth->fetchrow_hashref){
1051 $acctlines[$numlines] = $data;
1053 $total = $total+ $data->{'amountoutstanding'};
1055 return ($numlines,\@acctlines,$total);
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 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 or
1116 items.itemlost is NULL)";
1117 my $sth2=$dbh->prepare($query2);
1119 if (my $data2=$sth2->fetchrow_hashref){
1122 if ($data->{'holdingbranch'} eq 'C'){
1125 if ($data->{'holdingbranch'} eq 'F' || $data->{'holdingbranch'} eq 'FP'){
1128 if ($data->{'holdingbranch'} eq 'S' || $data->{'holdingbranch'} eq 'SP'){
1131 if ($data->{'itemlost'} eq '1'){
1134 if ($data->{'holdingbranch'} eq 'FM'){
1137 if ($data->{'holdingbranch'} eq 'TR'){
1144 my $query2="Select * from aqorders where biblionumber=$bibnum";
1145 my $sth2=$dbh->prepare($query2);
1147 if (my $data=$sth2->fetchrow_hashref){
1148 $ocount=$data->{'quantity'} - $data->{'quantityreceived'};
1154 return ($count,$lcount,$nacount,$fcount,$scount,$lostcount,$mending,$transit,$ocount);
1160 my $query="select description from itemtypes where itemtype='$type'";
1161 my $sth=$dbh->prepare($query);
1163 my $dat=$sth->fetchrow_hashref;
1166 return ($dat->{'description'});
1172 my $query="Select * from biblioitems,itemtypes,items where
1173 biblioitems.biblionumber='$bibnum' and biblioitems.itemtype=itemtypes.itemtype and
1174 biblioitems.biblioitemnumber=items.biblioitemnumber group by
1175 items.biblioitemnumber";
1176 my $sth=$dbh->prepare($query);
1180 while (my $data=$sth->fetchrow_hashref){
1186 return($i,@results);
1190 my ($biblioitemnumber)=@_;
1192 my $query="Select barcode from items where
1193 biblioitemnumber='$biblioitemnumber'";
1194 my $sth=$dbh->prepare($query);
1198 while (my $data=$sth->fetchrow_hashref){
1199 $barcodes[$i]=$data->{'barcode'};
1207 END { } # module clean-up code here (global destructor)