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){
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 borrowernumber='$data->{'guarantor'}'";
86 $sth=$dbh->prepare($query);
88 $data=$sth->fetchrow_hashref;
94 sub NewBorrowerNumber {
96 my $sth=$dbh->prepare("Select max(borrowernumber) from borrowers");
98 my $data=$sth->fetchrow_hashref;
100 $data->{'max(borrowernumber)'}++;
102 return($data->{'max(borrowernumber)'});
106 my ($env,$type,$search,$num,$offset)=@_;
107 my $dbh = &C4Connect;
108 $search->{'keyword'}=~ s/'/\\'/g;
109 my @key=split(' ',$search->{'keyword'});
113 my $query ="Select count(*) from biblio where
114 ((title like '$key[0]%' or title like '% $key[0]%')";
116 $query=$query." and (title like '$key[$i]%' or title like '% $key[$i]%')";
119 $query=$query.") or ((author like '$key[0]%' or author like '% $key[0]%')";
122 $query=$query." and (author like '$key[$i]%' or author like '% $key[$i]%')";
125 $query.=") or ((seriestitle like '$key[0]%' or seriestitle like '% $key[0]%')";
126 for ($i=1;$i<$count;$i++){
127 $query.=" and (seriestitle like '$key[$i]%' or seriestitle like '% $key[$i]%')";
129 $query.= ") or ((notes like '$key[0]%' or notes like '% $key[0]%')";
130 for ($i=1;$i<$count;$i++){
131 $query.=" and (notes like '$key[$i]%' or notes like '% $key[$i]%')";
133 $query=$query.") order by title";
134 my $sth=$dbh->prepare($query);
136 my $data=$sth->fetchrow_hashref;
137 my $count=$data->{'count(*)'};
139 $query=~ s/count\(\*\)/\*/;
140 $query= $query." limit $offset,$num";
141 $sth=$dbh->prepare($query);
145 while (my $data=$sth->fetchrow_hashref){
146 $results[$i]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}";
151 return($count,@results);
157 my ($env,$type,$search,$num,$offset)=@_;
158 my $dbh = &C4Connect;
159 $search->{'front'}=~ s/ +$//;
160 $search->{'front'}=~ s/'/\\'/;
161 my @key=split(' ',$search->{'front'});
165 my $query ="Select * from biblio,bibliosubtitle where
166 biblio.biblionumber=bibliosubtitle.biblionumber and
167 ((title like '$key[0]%' or title like '% $key[0]%'
168 or subtitle like '$key[0]%' or subtitle like '% $key[0]%'
169 or author like '$key[0]%' or author like '% $key[0]%')";
171 $query=$query." and (title like '%$key[$i]%' or subtitle like '%$key[$i]%')";
174 $query=$query.") group by biblio.biblionumber order by author,title";
176 my $sth=$dbh->prepare($query);
179 while (my $data=$sth->fetchrow_hashref){
180 $results[$i]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}";
181 # print $results[$i];
185 $sth=$dbh->prepare("Select biblionumber from bibliosubject where subject
186 like '%$search->{'keyword'}%'");
188 while (my $data=$sth->fetchrow_hashref){
189 my $sth2=$dbh->prepare("Select * from biblio where
190 biblionumber=$data->{'biblionumber'}");
192 while (my $data2=$sth2->fetchrow_hashref){
194 $results[$i]="$data2->{'author'}\t$data2->{'title'}\t$data2->{'biblionumber'}\t$data->{'copyrightdate'}";
195 # print $results[$i];
201 @results=sort @results;
206 while ($i2 < $count){
207 if ($results[$i2] ne $res[$i-1]){
208 $res[$i]=$results[$i2];
216 while ($i2 < $num && $i2 < $count){
217 $res2[$i2]=$res[$i2+$offset];
228 my ($env,$type,$search,$num,$offset)=@_;
229 my $dbh = &C4Connect;
230 $search->{'keyword'}=~ s/ +$//;
231 $search->{'keyword'}=~ s/'/\\'/;
232 my @key=split(' ',$search->{'keyword'});
236 my $query ="Select * from biblio
237 left join bibliosubtitle on
238 bibliosubtitle.biblionumber=biblio.biblionumber
239 left join biblioitems on
240 biblioitems.biblionumber=biblio.biblionumber
242 (((title like '$key[0]%' or title like '% $key[0]%')";
244 $query=$query." and (title like '$key[$i]%' or title like '% $key[$i]%')";
247 $query.= ") or ((subtitle like '$key[0]%' or subtitle like '% $key[0]%')";
248 for ($i=1;$i<$count;$i++){
249 $query.= " and (subtitle like '$key[$i]%' or subtitle like '% $key[$i]%')";
251 $query.= ") or ((seriestitle like '$key[0]%' or seriestitle like '% $key[0]%')";
252 for ($i=1;$i<$count;$i++){
253 $query.=" and (seriestitle like '$key[$i]%' or seriestitle like '% $key[$i]%')";
255 $query.= ") or ((biblio.notes like '$key[0]%' or biblio.notes like '% $key[0]%')";
256 for ($i=1;$i<$count;$i++){
257 $query.=" and (biblio.notes like '$key[$i]%' or biblio.notes like '% $key[$i]%')";
259 $query.= ") or ((biblioitems.notes like '$key[0]%' or biblioitems.notes like '% $key[0]%')";
260 for ($i=1;$i<$count;$i++){
261 $query.=" and (biblioitems.notes like '$key[$i]%' or biblioitems.notes like '% $key[$i]%')";
263 if ($search->{'keyword'} =~ /new zealand/i){
264 $query.= "or (title like 'nz%' or title like '% nz %' or title like '% nz' or subtitle like 'nz%'
265 or subtitle like '% nz %' or subtitle like '% nz' or author like 'nz %'
266 or author like '% nz %' or author like '% nz')"
268 if ($search->{'keyword'} eq 'nz' || $search->{'keyword'} eq 'NZ' ||
269 $search->{'keyword'} =~ /nz /i || $search->{'keyword'} =~ / nz /i ||
270 $search->{'keyword'} =~ / nz/i){
271 $query.= "or (title like 'new zealand%' or title like '% new zealand %'
272 or title like '% new zealand' or subtitle like 'new zealand%' or
273 subtitle like '% new zealand %'
274 or subtitle like '% new zealand' or author like 'new zealand%'
275 or author like '% new zealand %' or author like '% new zealand' or
276 seriestitle like 'new zealand%' or seriestitle like '% new zealand %'
277 or seriestitle like '% new zealand')"
280 if ($search->{'class'} ne ''){
281 my @temp=split(/\|/,$search->{'class'});
283 $query.= "and ( itemtype='$temp[0]'";
284 for (my $i=1;$i<$count;$i++){
285 $query.=" or itemtype='$temp[$i]'";
289 $query.="group by biblio.biblionumber order by author,title";
291 my $sth=$dbh->prepare($query);
294 while (my $data=$sth->fetchrow_hashref){
295 $results[$i]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}";
296 # print $results[$i];
300 $sth=$dbh->prepare("Select biblionumber from bibliosubject where subject
301 like '%$search->{'keyword'}%'");
303 while (my $data=$sth->fetchrow_hashref){
304 $query="Select * from biblio,biblioitems where
305 biblio.biblionumber=$data->{'biblionumber'} and biblio.biblionumber=biblioitems.biblionumber";
306 if ($search->{'class'} ne ''){
307 my @temp=split(/\|/,$search->{'class'});
309 $query.= " and ( itemtype='$temp[0]'";
310 for (my $i=1;$i<$count;$i++){
311 $query.=" or itemtype='$temp[$i]'";
315 my $sth2=$dbh->prepare($query);
318 while (my $data2=$sth2->fetchrow_hashref){
319 $results[$i]="$data2->{'author'}\t$data2->{'title'}\t$data2->{'biblionumber'}\t$data2->{'copyrightdate'}";
320 # print $results[$i];
326 @results=sort @results;
333 while ($i2 < $count){
334 if ($results[$i2] ne $res[$i-1]){
335 $res[$i]=$results[$i2];
343 while ($i2 < $num && $i2 < $count){
344 $res2[$i2]=$res[$i2+$offset];
355 my ($env,$type,$search,$num,$offset)=@_;
356 my $dbh = &C4Connect;
359 $search->{'title'}=~ s/'/\\'/g;
360 $search->{'author'}=~ s/'/\\'/g;
361 my $title = lc($search->{'title'});
363 if ($type eq 'loose') {
364 if ($search->{'author'} ne ''){
365 my @key=split(' ',$search->{'author'});
368 $query="select *,biblio.author,biblio.biblionumber from
370 left join additionalauthors
371 on additionalauthors.biblionumber =biblio.biblionumber
372 left join biblioitems on biblioitems.biblionumber=biblio.biblionumber
374 ((biblio.author like '$key[0]%' or biblio.author like '% $key[0]%' or
375 additionalauthors.author like '$key[0]%' or additionalauthors.author
379 $query=$query." and (
380 biblio.author like '$key[$i]%' or biblio.author like '% $key[$i]%' or
381 additionalauthors.author like '$key[$i]%' or additionalauthors.author like '% $key[$i]%'
386 if ($search->{'title'} ne ''){
387 $query=$query. " and title like '%$search->{'title'}%'";
389 if ($search->{'class'} ne ''){
390 my @temp=split(/\|/,$search->{'class'});
392 $query.= "and ( itemtype='$temp[0]'";
393 for (my $i=1;$i<$count;$i++){
394 $query.=" or itemtype='$temp[$i]'";
398 if ($search->{'dewey'} ne ''){
399 $query.=" and dewey='$search->{'dewey'}' ";
402 $query.=" group by biblio.biblionumber";
404 if ($search->{'title'} ne ''){
405 if ($search->{'ttype'} eq 'exact'){
406 $query="select * from biblio
408 (biblio.title='$search->{'title'}' or (biblio.unititle = '$search->{'title'}'
409 or biblio.unititle like '$search->{'title'} |%' or
410 biblio.unititle like '%| $search->{'title'} |%' or
411 biblio.unititle like '%| $search->{'title'}') or
412 (biblio.seriestitle = '$search->{'title'}' or
413 biblio.seriestitle like '$search->{'title'} |%' or
414 biblio.seriestitle like '%| $search->{'title'} |%' or
415 biblio.seriestitle like '%| $search->{'title'}')
418 my @key=split(' ',$search->{'title'});
421 $query="select * from biblio
422 left join bibliosubtitle on
423 bibliosubtitle.biblionumber=biblio.biblionumber
424 left join biblioitems on biblioitems.biblionumber=biblio.biblionumber
426 (((title like '$key[0]%' or title like '% $key[0]%' or title like '% $key[0]')";
428 $query=$query." and (title like '$key[$i]%' or title like '% $key[$i]%' or title like '% $key[$i]')";
431 $query.=") or ((subtitle like '$key[0]%' or subtitle like '% $key[0] %' or subtitle like '% $key[0]')";
432 for ($i=1;$i<$count;$i++){
433 $query.=" and (subtitle like '$key[$i]%' or subtitle like '% $key[$i] %' or subtitle like '% $key[$i]')";
435 $query.=") or ((seriestitle like '$key[0]%' or seriestitle like '% $key[0] %' or seriestitle like '% $key[0]')";
436 for ($i=1;$i<$count;$i++){
437 $query.=" and (seriestitle like '$key[$i]%' or seriestitle like '% $key[$i] %')";
439 $query.=") or ((unititle like '$key[0]%' or unititle like '% $key[0] %' or unititle like '% $key[0]')";
440 for ($i=1;$i<$count;$i++){
441 $query.=" and (unititle like '$key[$i]%' or unititle like '% $key[$i] %')";
444 if ($search->{'class'} ne ''){
445 my @temp=split(/\|/,$search->{'class'});
447 $query.= " and ( itemtype='$temp[0]'";
448 for (my $i=1;$i<$count;$i++){
449 $query.=" or itemtype='$temp[$i]'";
453 if ($search->{'dewey'} ne ''){
454 $query.=" and dewey='$search->{'dewey'}' ";
457 } elsif ($search->{'class'} ne ''){
458 $query="select * from biblioitems,biblio where biblio.biblionumber=biblioitems.biblionumber";
459 my @temp=split(/\|/,$search->{'class'});
461 $query.= " and ( itemtype='$temp[0]'";
462 for (my $i=1;$i<$count;$i++){
463 $query.=" or itemtype='$temp[$i]'";
466 } elsif ($search->{'dewey'} ne ''){
467 $query="select * from biblioitems,biblio
468 where biblio.biblionumber=biblioitems.biblionumber
469 and biblioitems.dewey like '$search->{'dewey'}%'";
471 $query .=" group by biblio.biblionumber";
475 if ($type eq 'subject'){
476 my @key=split(' ',$search->{'subject'});
479 $query="select distinct(subject) from bibliosubject where( subject like
480 '$key[0]%' or subject like '% $key[0]%' or subject like '% $key[0]' or subject like '%($key[0])%')";
482 $query.=" and (subject like '$key[$i]%' or subject like '% $key[$i]%'
483 or subject like '% $key[$i]'
484 or subject like '%($key[$i])%')";
487 if ($search->{'subject'} eq 'NZ' || $search->{'subject'} eq 'nz'){
488 $query.= " or (subject like 'NEW ZEALAND %' or subject like '% NEW ZEALAND %'
489 or subject like '% NEW ZEALAND' or subject like '%(NEW ZEALAND)%' ) ";
490 } elsif ( $search->{'subject'} =~ /^nz /i || $search->{'subject'} =~ / nz /i || $search->{'subject'} =~ / nz$/i){
491 $query=~ s/ nz/ NEW ZEALAND/ig;
492 $query=~ s/nz /NEW ZEALAND /ig;
493 $query=~ s/\(nz\)/\(NEW ZEALAND\)/gi;
496 if ($type eq 'precise'){
497 $query="select * from items,biblio ";
498 if ($search->{'item'} ne ''){
499 my $search2=uc $search->{'item'};
500 $query=$query." where
501 items.biblionumber=biblio.biblionumber
502 and barcode='$search2'";
504 if ($search->{'isbn'} ne ''){
505 my $search2=uc $search->{'isbn'};
506 my $query1 = "select * from biblioitems where isbn='$search2'";
507 my $sth1=$dbh->prepare($query1);
511 while (my $data=$sth1->fetchrow_hashref) {
512 $query="select * from biblioitems,biblio where
513 biblio.biblionumber = $data->{'biblionumber'}
514 and biblioitems.biblionumber = biblio.biblionumber";
515 my $sth=$dbh->prepare($query);
517 my $data=$sth->fetchrow_hashref;
518 $results[$i2]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$data->{'isbn'}\t$data->{'itemtype'}";
526 if ($type ne 'precise' && $type ne 'subject'){
527 if ($search->{'author'} ne ''){
528 $query=$query." order by biblio.author,title";
530 $query=$query." order by title";
533 if ($type eq 'subject'){
534 $query=$query." order by subject";
538 my $sth=$dbh->prepare($query);
542 my $limit= $num+$offset;
543 while (my $data=$sth->fetchrow_hashref){
544 if ($count > $offset && $count <= $limit){
545 if ($type ne 'subject' && $type ne 'precise'){
546 $results[$i]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}";
547 } elsif ($search->{'isbn'} ne '' || $search->{'item'} ne ''){
548 $results[$i]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}";
550 $results[$i]="$data->{'author'}\t$data->{'subject'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}";
557 #if ($type ne 'precise'){
561 return($count,@results);
564 sub updatesearchstats{
570 my ($env,$subject)=@_;
572 my $query="Select * from biblio,bibliosubject where
573 biblio.biblionumber=bibliosubject.biblionumber and
574 bibliosubject.subject='$subject' group by biblio.biblionumber
575 order by biblio.title";
576 my $sth=$dbh->prepare($query);
581 while (my $data=$sth->fetchrow_hashref){
582 $results[$i]="$data->{'title'}\t$data->{'author'}\t$data->{'biblionumber'}";
592 my ($env,$biblionumber,$type)=@_;
593 my $dbh = &C4Connect;
594 my $query="Select * from items,biblio,biblioitems,branches
595 where (items.biblioitemnumber = biblioitems.biblioitemnumber)
596 and biblioitems.biblionumber=biblio.biblionumber
597 and biblio.biblionumber='$biblionumber' and branches.branchcode=
598 items.holdingbranch ";
600 if ($type eq 'opac'){
601 $query.=" and (items.itemlost<>1 or items.itemlost is NULL)
602 and (wthdrawn <> 1 or wthdrawn is NULL)";
604 $query=$query."order by items.dateaccessioned desc";
605 my $sth=$dbh->prepare($query);
610 while (my $data=$sth->fetchrow_hashref){
611 my $iquery = "Select * from issues
612 where itemnumber = '$data->{'itemnumber'}'
613 and returndate is null";
615 my $isth=$dbh->prepare($iquery);
617 if (my $idata=$isth->fetchrow_hashref){
618 my @temp=split('-',$idata->{'date_due'});
619 $datedue = "$temp[2]/$temp[1]/$temp[0]";
621 if ($data->{'itemlost'} eq '1'){
624 if ($data->{'wthdrawn'} eq '1'){
625 $datedue="Cancelled";
628 my ($rescount,$reserves)=FindReserves($biblionumber,'');
634 my $class = $data->{'classification'};
635 my $dewey = $data->{'dewey'};
637 if ($dewey eq "000.") { $dewey = "";};
638 if ($dewey < 10){$dewey='00'.$dewey;}
639 if ($dewey < 100 && $dewey > 10){$dewey='0'.$dewey;}
644 $class = $class.$dewey;
645 $class = $class.$data->{'subclass'};
646 # $results[$i]="$data->{'title'}\t$data->{'barcode'}\t$datedue\t$data->{'branchname'}\t$data->{'dewey'}";
647 my @temp=split('-',$data->{'datelastseen'});
648 my $date="$temp[2]/$temp[1]/$temp[0]";
649 $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'}";
650 # print "$results[$i] <br>";
659 my ($env,$biblionumber)=@_;
660 #debug_msg($env,"GetItems");
661 my $dbh = &C4Connect;
662 my $query = "Select * from biblioitems where (biblionumber = $biblionumber)";
663 #debug_msg($env,$query);
664 my $sth=$dbh->prepare($query);
666 #debug_msg($env,"executed query");
669 while (my $data=$sth->fetchrow_hashref) {
670 #debug_msg($env,$data->{'biblioitemnumber'});
671 my $dewey = $data->{'dewey'};
673 my $line = $data->{'biblioitemnumber'}."\t".$data->{'itemtype'};
674 $line = $line."\t$data->{'classification'}\t$dewey";
675 $line = $line."\t$data->{'subclass'}\t$data->{isbn}";
676 $line = $line."\t$data->{'volume'}\t$data->{number}";
677 my $isth= $dbh->prepare("select * from items where biblioitemnumber = $data->{'biblioitemnumber'}");
679 while (my $idata = $isth->fetchrow_hashref) {
680 my $iline = $idata->{'barcode'}."[".$idata->{'holdingbranch'}."[";
681 if ($idata->{'notforloan'} == 1) {
682 $iline = $iline."NFL ";
684 if ($idata->{'itemlost'} == 1) {
685 $iline = $iline."LOST ";
687 $line = $line."\t$iline";
690 $results[$i] = $line;
701 my $query="Select * from items,biblioitems where barcode='$barcode'
702 and items.biblioitemnumber=biblioitems.biblioitemnumber";
704 my $sth=$dbh->prepare($query);
706 my $data=$sth->fetchrow_hashref;
713 my ($bibnum,$type)=@_;
715 my $query="Select *,biblio.notes from biblio,biblioitems,bibliosubtitle where biblio.biblionumber=$bibnum
716 and biblioitems.biblionumber=$bibnum and
717 (bibliosubtitle.biblionumber=$bibnum)";
719 my $sth=$dbh->prepare($query);
721 my $data=$sth->fetchrow_hashref;
723 $query="Select * from bibliosubject where biblionumber='$bibnum'";
724 $sth=$dbh->prepare($query);
726 while (my $dat=$sth->fetchrow_hashref){
727 $data->{'subject'}.=" | $dat->{'subject'}";
739 my $query="Select *, biblioitems.notes as bnotes
740 from biblio,biblioitems,itemtypes where biblio.biblionumber=
741 biblioitems.biblionumber and biblioitemnumber=$bibitem and
742 biblioitems.itemtype=itemtypes.itemtype";
744 my $sth=$dbh->prepare($query);
746 my $data=$sth->fetchrow_hashref;
755 my $query="Select * from bibliosubject where biblionumber=$bibnum";
756 my $sth=$dbh->prepare($query);
760 while (my $data=$sth->fetchrow_hashref){
766 return($i,\@results);
772 my $query="Select * from additionalauthors where biblionumber=$bibnum";
773 my $sth=$dbh->prepare($query);
777 while (my $data=$sth->fetchrow_hashref){
783 return($i,\@results);
789 my $query="Select * from bibliosubtitle where biblionumber=$bibnum";
790 my $sth=$dbh->prepare($query);
794 while (my $data=$sth->fetchrow_hashref){
800 return($i,\@results);
806 my ($bibitem,$biblio)=@_;
808 my $query="Select * from items where
809 items.biblioitemnumber='$bibitem'";
811 my $sth=$dbh->prepare($query) || die $dbh->errstr;
812 $sth->execute || die $sth->errstr;
815 while (my $data=$sth->fetchrow_hashref){
816 my $query2="select * from issues,borrowers where itemnumber=$data->{'itemnumber'}
817 and returndate is NULL and issues.borrowernumber=borrowers.borrowernumber";
818 my $sth2=$dbh->prepare($query2);
820 if (my $data2=$sth2->fetchrow_hashref){
821 $data->{'date_due'}=$data2->{'date_due'};
822 $data->{'card'}=$data2->{'cardnumber'};
824 if ($data->{'wthdrawn'} eq '1'){
825 $data->{'date_due'}='Cancelled';
827 # my ($rescount,$reserves)=FindReserves($biblio,'');
828 # if ($rescount >0){#
829 # $data->{'date_due'}='Request';
831 $data->{'date_due'}='Available';
836 $query2="select * from issues,borrowers where itemnumber='$data->{'itemnumber'}'
837 and issues.borrowernumber=borrowers.borrowernumber
838 order by date_due desc";
839 my $sth2=$dbh->prepare($query2) || die $dbh->errstr;
841 $sth2->execute || die $sth2->errstr;
842 for (my $i2=0;$i2<2;$i2++){
843 if (my $data2=$sth2->fetchrow_hashref){
844 $data->{"timestamp$i2"}=$data2->{'timestamp'};
845 $data->{"card$i2"}=$data2->{'cardnumber'};
846 $data->{"borrower$i2"}=$data2->{'borrowernumber'};
859 my ($env,$dbh,$itemnumber) = @_;
861 my $query="Select * from biblio,items,biblioitems
862 where items.itemnumber = '$itemnumber'
863 and biblio.biblionumber = items.biblionumber
864 and biblioitems.biblioitemnumber = items.biblioitemnumber";
865 my $sth=$dbh->prepare($query);
868 my $data=$sth->fetchrow_hashref;
874 #used by member enquiries from the intranet
877 my ($env,$searchstring,$type)=@_;
878 my $dbh = &C4Connect;
879 $searchstring=~ s/\'/\\\'/g;
880 my @data=split(' ',$searchstring);
882 my $query="Select * from borrowers
883 where ((surname like \"$data[0]%\" or surname like \"% $data[0]%\"
884 or firstname like \"$data[0]%\" or firstname like \"% $data[0]%\"
885 or othernames like \"$data[0]%\" or othernames like \"% $data[0]%\")
887 for (my $i=1;$i<$count;$i++){
888 $query=$query." and (surname like \"$data[$i]%\" or surname like \"% $data[$i]%\"
889 or firstname like \"$data[$i]%\" or firstname like \"% $data[$i]%\"
890 or othernames like \"$data[$i]%\" or othernames like \"% $data[$i]%\")";
892 $query=$query.") or cardnumber = \"$searchstring\"
893 order by surname,firstname";
895 my $sth=$dbh->prepare($query);
899 while (my $data=$sth->fetchrow_hashref){
900 push(@results,$data);
906 return ($cnt,\@results);
910 my ($cardnumber,$bornum)=@_;
911 $cardnumber = uc $cardnumber;
915 $query="Select * from borrowers where cardnumber='$cardnumber'";
917 $query="Select * from borrowers where borrowernumber='$bornum'";
920 my $sth=$dbh->prepare($query);
922 my $data=$sth->fetchrow_hashref;
932 $query="Select * from issues,biblio,items where borrowernumber='$bornum' and
933 items.itemnumber=issues.itemnumber and
934 items.biblionumber=biblio.biblionumber and issues.returndate is NULL order
937 my $sth=$dbh->prepare($query);
941 while (my $data=$sth->fetchrow_hashref){
951 my ($bornum,$order,$limit)=@_;
954 $query="Select * from issues,biblio,items,biblioitems
955 where borrowernumber='$bornum' and
956 items.biblioitemnumber=biblioitems.biblioitemnumber and
957 items.itemnumber=issues.itemnumber and
958 items.biblionumber=biblio.biblionumber";
959 $query.=" order by $order";
961 $query.=" limit $limit";
964 my $sth=$dbh->prepare($query);
968 while (my $data=$sth->fetchrow_hashref){
980 my ($env,$bornum)=@_;
982 my $query="Select count(*) from issues where borrowernumber='$bornum' and
985 my $sth=$dbh->prepare($query);
987 my $data=$sth->fetchrow_hashref;
989 $sth=$dbh->prepare("Select count(*) from issues where
990 borrowernumber='$bornum' and date_due < now() and returndate is NULL");
992 my $data2=$sth->fetchrow_hashref;
994 $sth=$dbh->prepare("Select sum(amountoutstanding) from accountlines where
995 borrowernumber='$bornum'");
997 my $data3=$sth->fetchrow_hashref;
1001 return($data2->{'count(*)'},$data->{'count(*)'},$data3->{'sum(amountoutstanding)'});
1005 my ($env,$params) = @_;
1009 my $query = "Select borrowernumber, accountno, date, amount, description,
1010 dispute, accounttype, amountoutstanding, barcode, title
1011 from accountlines,items,biblio
1012 where borrowernumber = $params->{'borrowernumber'} ";
1013 if ($params->{'acctno'} ne "") {
1014 my $query = $query." and accountlines.accountno = $params->{'acctno'} ";
1016 my $query = $query." and accountlines.itemnumber = items.itemnumber
1017 and items.biblionumber = biblio.biblionumber
1018 and accountlines.amountoutstanding<>0 order by date";
1019 my $sth=$dbh->prepare($query);
1023 while (my $data=$sth->fetchrow_hashref){
1024 $acctlines[$numlines] = $data;
1026 $total = $total+ $data->{'amountoutstanding'};
1028 return ($numlines,\@acctlines,$total);
1033 sub getboracctrecord {
1034 my ($env,$params) = @_;
1038 my $query= "Select * from accountlines where
1039 borrowernumber=$params->{'borrowernumber'} order by date desc,timestamp desc";
1040 my $sth=$dbh->prepare($query);
1044 while (my $data=$sth->fetchrow_hashref){
1045 # if ($data->{'itemnumber'} ne ''){
1046 # $query="Select * from items,biblio where items.itemnumber=
1047 # '$data->{'itemnumber'}' and biblio.biblionumber=items.biblionumber";
1048 # my $sth2=$dbh->prepare($query);
1050 # my $data2=$sth2->fetchrow_hashref;
1054 $acctlines[$numlines] = $data;
1056 $total = $total+ $data->{'amountoutstanding'};
1060 return ($numlines,\@acctlines,$total);
1064 my ($env,$bibnum,$type)=@_;
1066 my $query="Select * from items where
1067 biblionumber=$bibnum ";
1068 if ($type eq 'opac'){
1069 $query.=" and (itemlost =0 or itemlost is NULL) and
1070 (wthdrawn = 0 or wthdrawn is NULL)";
1072 my $sth=$dbh->prepare($query);
1084 while (my $data=$sth->fetchrow_hashref){
1086 my $query2="select * from issues,items where issues.itemnumber=
1087 '$data->{'itemnumber'}' and returndate is NULL
1088 and items.itemnumber=issues.itemnumber and (items.itemlost <>1 or
1089 items.itemlost is NULL)";
1090 my $sth2=$dbh->prepare($query2);
1092 if (my $data2=$sth2->fetchrow_hashref){
1095 if ($data->{'holdingbranch'} eq 'C'){
1098 if ($data->{'holdingbranch'} eq 'F' || $data->{'holdingbranch'} eq 'FP'){
1101 if ($data->{'holdingbranch'} eq 'S' || $data->{'holdingbranch'} eq 'SP'){
1104 if ($data->{'itemlost'} eq '1' || $data->{'itemlost'} eq '2'){
1107 if ($data->{'holdingbranch'} eq 'FM'){
1110 if ($data->{'holdingbranch'} eq 'TR'){
1117 my $query2="Select * from aqorders where biblionumber=$bibnum and
1118 datecancellationprinted is NULL";
1119 my $sth2=$dbh->prepare($query2);
1121 if (my $data=$sth2->fetchrow_hashref){
1122 $ocount=$data->{'quantity'} - $data->{'quantityreceived'};
1128 return ($count,$lcount,$nacount,$fcount,$scount,$lostcount,$mending,$transit,$ocount);
1134 my $query="select description from itemtypes where itemtype='$type'";
1135 my $sth=$dbh->prepare($query);
1137 my $dat=$sth->fetchrow_hashref;
1140 return ($dat->{'description'});
1146 my $query="Select * from biblioitems,itemtypes,items where
1147 biblioitems.biblionumber='$bibnum' and biblioitems.itemtype=itemtypes.itemtype and
1148 biblioitems.biblioitemnumber=items.biblioitemnumber group by
1149 items.biblioitemnumber";
1150 my $sth=$dbh->prepare($query);
1154 while (my $data=$sth->fetchrow_hashref){
1160 return($i,@results);
1164 my ($biblioitemnumber)=@_;
1166 my $query="Select barcode from items where
1167 biblioitemnumber='$biblioitemnumber'";
1168 my $sth=$dbh->prepare($query);
1172 while (my $data=$sth->fetchrow_hashref){
1173 $barcodes[$i]=$data->{'barcode'};
1185 my $sth=$dbh->prepare("select variable,value from systempreferences");
1187 while (my ($variable,$value)=$sth->fetchrow) {
1188 $systemprefs{$variable}=$value;
1192 return(%systemprefs);
1195 END { } # module clean-up code here (global destructor)