1 package C4::Acquisitions; #assumes C4/Acquisitions.pm
7 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
9 # set the version for version checking
13 @EXPORT = qw(&getorders &bookseller &breakdown &basket &newbasket &bookfunds
14 &ordersearch &newbiblio &newbiblioitem &newsubject &newsubtitle &neworder
15 &newordernum &modbiblio &modorder &getsingleorder &invoice &receiveorder
16 &bookfundbreakdown &curconvert &updatesup &insertsup &newitems &modbibitem
17 &getcurrencies &modsubtitle &modsubject &modaddauthor &moditem &countitems
18 &findall &needsmod &delitem &delbibitem &delbiblio &delorder &branches
19 &getallorders &getrecorders &updatecurrencies &getorder &getcurrency &updaterecorder
20 &updatecost &checkitems &modnote &getitemtypes &getbiblio
21 &getbiblioitem &getitemsbybiblioitem &isbnsearch &keywordsearch
22 &websitesearch &addwebsite &updatewebsite &deletewebsite);
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
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;
63 my $query = "Select count(*),authorisedby,entrydate,basketno from aqorders where
64 booksellerid='$supplierid' and (quantity > quantityreceived or
65 quantityreceived is NULL)
66 and (datecancellationprinted is NULL or datecancellationprinted = '0000-00-00')";
67 $query.=" group by basketno order by entrydate desc";
69 my $sth=$dbh->prepare($query);
73 while (my $data=$sth->fetchrow_hashref){
79 return ($i,\@results);
85 my $query="Select count(*) from items where biblionumber=$biblio";
87 my $sth=$dbh->prepare($query);
89 my $data=$sth->fetchrow_hashref;
92 return($data->{'count(*)'});
98 my $query="Select ordernumber
100 where biblionumber=? and biblioitemnumber=?";
101 my $sth=$dbh->prepare($query);
102 $sth->execute($bib,$bi);
103 my $ordnum=$sth->fetchrow_hashref;
105 my $order=getsingleorder($ordnum->{'ordernumber'});
108 return ($order,$ordnum->{'ordernumber'});
114 my $query="Select * from biblio,biblioitems,aqorders,aqorderbreakdown
115 where aqorders.ordernumber=?
116 and biblio.biblionumber=aqorders.biblionumber and
117 biblioitems.biblioitemnumber=aqorders.biblioitemnumber and
118 aqorders.ordernumber=aqorderbreakdown.ordernumber";
119 my $sth=$dbh->prepare($query);
120 $sth->execute($ordnum);
121 my $data=$sth->fetchrow_hashref;
130 my $query="Select * from aqorders,biblio,biblioitems where
131 booksellerinvoicenumber='$invoice'
132 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber=
133 aqorders.biblioitemnumber group by aqorders.ordernumber,aqorders.biblioitemnumber";
136 my $sth=$dbh->prepare($query);
138 while (my $data=$sth->fetchrow_hashref){
148 #gets all orders from a certain supplier, orders them alphabetically
151 my $query="Select * from aqorders,biblio,biblioitems where booksellerid='$supid'
152 and (cancelledby is NULL or cancelledby = '')
153 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber=
154 aqorders.biblioitemnumber
155 group by aqorders.biblioitemnumber
160 my $sth=$dbh->prepare($query);
162 while (my $data=$sth->fetchrow_hashref){
172 #gets all orders from a certain supplier, orders them alphabetically
175 my $query="Select * from aqorders,biblio,biblioitems where booksellerid='$supid'
176 and (cancelledby is NULL or cancelledby = '')
177 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber=
178 aqorders.biblioitemnumber and
179 aqorders.quantityreceived>0
180 and aqorders.datereceived >=now()
181 group by aqorders.biblioitemnumber
186 my $sth=$dbh->prepare($query);
188 while (my $data=$sth->fetchrow_hashref){
198 my ($search,$biblio,$catview)=@_;
200 my $query="Select *,biblio.title from aqorders,biblioitems,biblio
201 where aqorders.biblioitemnumber = biblioitems.biblioitemnumber
202 and biblio.biblionumber=aqorders.biblionumber
203 and ((datecancellationprinted is NULL)
204 or (datecancellationprinted = '0000-00-00')
206 my @data=split(' ',$search);
208 for (my $i=0;$i<$count;$i++){
209 $query.= "(biblio.title like '$data[$i]%' or biblio.title like '% $data[$i]%') and ";
212 $query.=" ) or biblioitems.isbn='$search'
213 or (aqorders.ordernumber='$search' and aqorders.biblionumber='$biblio')) ";
214 if ($catview ne 'yes'){
215 $query.=" and (quantityreceived < quantity or quantityreceived is NULL)";
217 $query.=" group by aqorders.ordernumber";
218 my $sth=$dbh->prepare($query);
223 while (my $data=$sth->fetchrow_hashref){
224 my $sth2=$dbh->prepare("Select * from biblio where
225 biblionumber='$data->{'biblionumber'}'");
227 my $data2=$sth2->fetchrow_hashref;
229 $data->{'author'}=$data2->{'author'};
230 $data->{'seriestitle'}=$data2->{'seriestitle'};
231 $sth2=$dbh->prepare("Select * from aqorderbreakdown where
232 ordernumber=$data->{'ordernumber'}");
234 $data2=$sth2->fetchrow_hashref;
236 $data->{'branchcode'}=$data2->{'branchcode'};
237 $data->{'bookfundid'}=$data2->{'bookfundid'};
248 my ($searchstring)=@_;
250 my $query="Select * from aqbooksellers where name like '%$searchstring%' or
251 id = '$searchstring'";
252 my $sth=$dbh->prepare($query);
256 while (my $data=$sth->fetchrow_hashref){
268 my $query="Select * from aqorderbreakdown where ordernumber='$id'";
269 my $sth=$dbh->prepare($query);
273 while (my $data=$sth->fetchrow_hashref){
279 return($i,\@results);
283 my ($basketno,$supplier)=@_;
285 my $query="Select *,biblio.title from aqorders,biblio,biblioitems
286 where basketno='$basketno'
287 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber
288 =aqorders.biblioitemnumber
289 and (datecancellationprinted is NULL or datecancellationprinted =
291 if ($supplier ne ''){
292 $query.=" and aqorders.booksellerid='$supplier'";
294 $query.=" group by aqorders.ordernumber";
295 my $sth=$dbh->prepare($query);
300 while (my $data=$sth->fetchrow_hashref){
311 my $query="Select max(basketno) from aqorders";
312 my $sth=$dbh->prepare($query);
314 my $data=$sth->fetchrow_arrayref;
315 my $basket=$$data[0];
324 my $query="Select * from aqbookfund,aqbudget where aqbookfund.bookfundid
326 and aqbudget.startdate='2001=07-01'
327 group by aqbookfund.bookfundid order by bookfundname";
328 my $sth=$dbh->prepare($query);
332 while (my $data=$sth->fetchrow_hashref){
343 my $query="Select * from branches";
344 my $sth=$dbh->prepare($query);
349 while (my $data=$sth->fetchrow_hashref){
359 sub bookfundbreakdown {
362 my $query="Select quantity,datereceived,freight,unitprice,listprice,ecost,quantityreceived,subscription
363 from aqorders,aqorderbreakdown where bookfundid='$id' and
364 aqorders.ordernumber=aqorderbreakdown.ordernumber and ((budgetdate >=
365 '2001-07-01' and budgetdate <'2002-07-01') or
366 (datereceived >= '2001-07-01' and datereceived < '2002-07-01'))
367 and (datecancellationprinted is NULL or
368 datecancellationprinted='0000-00-00')";
369 my $sth=$dbh->prepare($query);
373 while (my $data=$sth->fetchrow_hashref){
374 if ($data->{'subscription'} == 1){
375 $spent+=$data->{'quantity'}*$data->{'unitprice'};
377 my $leftover=$data->{'quantity'}-$data->{'quantityreceived'};
378 $comtd+=($data->{'ecost'})*$leftover;
379 $spent+=($data->{'unitprice'})*$data->{'quantityreceived'};
384 return($spent,$comtd);
388 # Create a new biblio entry
390 my ($biblio) = @_; # input is ref to hash of fields
391 my ($bibnum,$error); # return resulting biblio number
395 my $dbh = &C4Connect;
397 # Get next biblionumber in sequence
398 my $query = "Select max(biblionumber) from biblio";
399 my $sth = $dbh->prepare($query);
401 my $data = $sth->fetchrow_arrayref;
402 $bibnum = $$data[0] + 1;
404 my $serial; # is this item part of a series?
406 if ($biblio->{'seriestitle'}) { $serial = 1 } else { $serial = 0 };
409 $query = "insert into biblio set
419 $sth = $dbh->prepare($query);
424 $biblio->{'copyright'},
426 $biblio->{'seriestitle'} ,
438 return($bibnum,$error);
442 my ($bibnum,$title,$author,$copyright,$seriestitle,$serial,$unititle,$notes)=@_;
444 my $query = "Update biblio set
447 copyrightdate = '$copyright',
448 seriestitle = '$seriestitle',
450 unititle = '$unititle',
452 where biblionumber = $bibnum";
453 my $sth=$dbh->prepare($query);
463 my ($bibnum,$subtitle)=@_;
465 my $query="update bibliosubtitle set subtitle='$subtitle' where biblionumber=$bibnum";
466 my $sth=$dbh->prepare($query);
473 my ($bibnum,$author)=@_;
475 my $query="Delete from additionalauthors where biblionumber=$bibnum";
476 my $sth=$dbh->prepare($query);
482 $query = "Insert into additionalauthors set
484 biblionumber = '$bibnum'";
485 $sth=$dbh->prepare($query);
497 my ($bibnum,$force,@subject)=@_;
501 for (my $i=0;$i<$count;$i++){
502 $subject[$i]=~ s/^ //g;
503 $subject[$i]=~ s/ $//g;
504 my $query = "select * from catalogueentry
505 where entrytype = 's'
506 and catalogueentry = '$subject[$i]'";
507 my $sth=$dbh->prepare($query);
510 if (my $data = $sth->fetchrow_hashref) {
512 if ($force eq $subject[$i]){
514 #subject not in aut, chosen to force anway
515 #so insert into cataloguentry so its in auth file
516 $query = "Insert into catalogueentry
517 (entrytype,catalogueentry)
518 values ('s','$subject[$i]')";
519 my $sth2=$dbh->prepare($query);
526 $error="$subject[$i]\n does not exist in the subject authority file";
527 $query = "Select * from catalogueentry
528 where entrytype = 's'
529 and (catalogueentry like '$subject[$i] %'
530 or catalogueentry like '% $subject[$i] %'
531 or catalogueentry like '% $subject[$i]')";
532 my $sth2=$dbh->prepare($query);
535 while (my $data=$sth2->fetchrow_hashref){
536 $error=$error."<br>$data->{'catalogueentry'}";
545 my $query="Delete from bibliosubject where biblionumber=$bibnum";
546 my $sth=$dbh->prepare($query);
551 for (my $i=0;$i<$count;$i++){
552 $sth = $dbh->prepare("Insert into bibliosubject
553 values ('$subject[$i]', $bibnum)");
565 my ($bibitemnum,$itemtype,$isbn,$publishercode,$publicationdate,$classification,$dewey,$subclass,$illus,$pages,$volumeddesc,$notes,$size,$place)=@_;
567 my $query="update biblioitems set itemtype='$itemtype',
568 isbn='$isbn',publishercode='$publishercode',publicationyear='$publicationdate',
569 classification='$classification',dewey='$dewey',subclass='$subclass',illus='$illus',
570 pages='$pages',volumeddesc='$volumeddesc',notes='$notes',size='$size',place='$place'
572 biblioitemnumber=$bibitemnum";
573 my $sth=$dbh->prepare($query);
581 my ($bibitemnum,$note)=@_;
583 my $query="update biblioitems set notes='$note' where
584 biblioitemnumber='$bibitemnum'";
585 my $sth=$dbh->prepare($query);
592 my ($biblioitem) = @_;
594 my $query = "Select max(biblioitemnumber) from biblioitems";
595 my $sth = $dbh->prepare($query);
599 $biblioitem->{'volume'} = $dbh->quote($biblioitem->{'volume'});
600 $biblioitem->{'number'} = $dbh->quote($biblioitem->{'number'});
601 $biblioitem->{'classification'} = $dbh->quote($biblioitem->{'classification'});
602 $biblioitem->{'itemtype'} = $dbh->quote($biblioitem->{'itemtype'});
603 $biblioitem->{'url'} = $dbh->quote($biblioitem->{'url'});
604 $biblioitem->{'isbn'} = $dbh->quote($biblioitem->{'isbn'});
605 $biblioitem->{'issn'} = $dbh->quote($biblioitem->{'issn'});
606 $biblioitem->{'dewey'} = $dbh->quote($biblioitem->{'dewey'});
607 $biblioitem->{'subclass'} = $dbh->quote($biblioitem->{'subclass'});
608 $biblioitem->{'publicationyear'} = $dbh->quote($biblioitem->{'publicationyear'});
609 $biblioitem->{'publishercode'} = $dbh->quote($biblioitem->{'publishercode'});
610 $biblioitem->{'volumedate'} = $dbh->quote($biblioitem->{'volumedate'});
611 $biblioitem->{'volumeddesc'} = $dbh->quote($biblioitem->{'volumeddesc'}); $biblioitem->{'illus'} = $dbh->quote($biblioitem->{'illus'});
612 $biblioitem->{'pages'} = $dbh->quote($biblioitem->{'pages'});
613 $biblioitem->{'notes'} = $dbh->quote($biblioitem->{'notes'});
614 $biblioitem->{'size'} = $dbh->quote($biblioitem->{'size'});
615 $biblioitem->{'place'} = $dbh->quote($biblioitem->{'place'});
618 $data = $sth->fetchrow_arrayref;
619 $bibitemnum = $$data[0] + 1;
623 $query = "insert into biblioitems set
624 biblioitemnumber = $bibitemnum,
625 biblionumber = $biblioitem->{'biblionumber'},
626 volume = $biblioitem->{'volume'},
627 number = $biblioitem->{'number'},
628 classification = $biblioitem->{'classification'},
629 itemtype = $biblioitem->{'itemtype'},
630 url = $biblioitem->{'url'},
631 isbn = $biblioitem->{'isbn'},
632 issn = $biblioitem->{'issn'},
633 dewey = $biblioitem->{'dewey'},
634 subclass = $biblioitem->{'subclass'},
635 publicationyear = $biblioitem->{'publicationyear'},
636 publishercode = $biblioitem->{'publishercode'},
637 volumedate = $biblioitem->{'volumedate'},
638 volumeddesc = $biblioitem->{'volumeddesc'},
639 illus = $biblioitem->{'illus'},
640 pages = $biblioitem->{'pages'},
641 notes = $biblioitem->{'notes'},
642 size = $biblioitem->{'size'},
643 place = $biblioitem->{'place'}";
645 $sth = $dbh->prepare($query);
656 my $query="insert into bibliosubject (biblionumber) values
658 my $sth=$dbh->prepare($query);
666 my ($bibnum, $subtitle) = @_;
668 $subtitle = $dbh->quote($subtitle);
669 my $query = "insert into bibliosubtitle set
670 biblionumber = $bibnum,
671 subtitle = $subtitle";
672 my $sth = $dbh->prepare($query);
681 my ($bibnum,$title,$ordnum,$basket,$quantity,$listprice,$supplier,$who,$notes,$bookfund,$bibitemnum,$rrp,$ecost,$gst,$budget,$cost,$sub,$invoice)=@_;
682 if ($budget eq 'now'){
685 $budget="'2001-07-01'";
693 my $query="insert into aqorders (biblionumber,title,basketno,
694 quantity,listprice,booksellerid,entrydate,requisitionedby,authorisedby,notes,
695 biblioitemnumber,rrp,ecost,gst,budgetdate,unitprice,subscription,booksellerinvoicenumber)
698 ($bibnum,'$title',$basket,$quantity,$listprice,'$supplier',now(),
699 '$who','$who','$notes',$bibitemnum,'$rrp','$ecost','$gst',$budget,'$cost',
701 my $sth=$dbh->prepare($query);
705 $query="select * from aqorders where
706 biblionumber=$bibnum and basketno=$basket and ordernumber >=$ordnum";
707 $sth=$dbh->prepare($query);
709 my $data=$sth->fetchrow_hashref;
711 $ordnum=$data->{'ordernumber'};
712 $query="insert into aqorderbreakdown (ordernumber,bookfundid) values
713 ($ordnum,'$bookfund')";
714 $sth=$dbh->prepare($query);
722 my ($bibnum,$ordnum)=@_;
724 my $query="update aqorders set datecancellationprinted=now()
725 where biblionumber='$bibnum' and
726 ordernumber='$ordnum'";
727 my $sth=$dbh->prepare($query);
731 my $count=itemcount($bibnum);
739 my ($title,$ordnum,$quantity,$listprice,$bibnum,$basketno,$supplier,$who,$notes,$bookfund,$bibitemnum,$rrp,$ecost,$gst,$budget,$cost,$invoice)=@_;
741 my $query="update aqorders set title='$title',
742 quantity='$quantity',listprice='$listprice',basketno='$basketno',
743 rrp='$rrp',ecost='$ecost',unitprice='$cost',
744 booksellerinvoicenumber='$invoice'
746 ordernumber=$ordnum and biblionumber=$bibnum";
747 my $sth=$dbh->prepare($query);
751 $query="update aqorderbreakdown set bookfundid=$bookfund where
752 ordernumber=$ordnum";
753 $sth=$dbh->prepare($query);
762 my $query="Select max(ordernumber) from aqorders";
763 my $sth=$dbh->prepare($query);
765 my $data=$sth->fetchrow_arrayref;
766 my $ordnum=$$data[0];
774 my ($biblio,$ordnum,$quantrec,$user,$cost,$invoiceno,$bibitemno,$freight,$bookfund,$rrp)=@_;
776 my $query="update aqorders set quantityreceived='$quantrec',
777 datereceived=now(),booksellerinvoicenumber='$invoiceno',
778 biblioitemnumber=$bibitemno,unitprice='$cost',freight='$freight',
780 where biblionumber=$biblio and ordernumber=$ordnum
783 my $sth=$dbh->prepare($query);
786 $query="update aqorderbreakdown set bookfundid=$bookfund where
787 ordernumber=$ordnum";
788 $sth=$dbh->prepare($query);
795 my($biblio,$ordnum,$user,$cost,$bookfund,$rrp)=@_;
797 my $query="update aqorders set
798 unitprice='$cost', rrp='$rrp'
799 where biblionumber=$biblio and ordernumber=$ordnum
802 my $sth=$dbh->prepare($query);
805 $query="update aqorderbreakdown set bookfundid=$bookfund where
806 ordernumber=$ordnum";
807 $sth=$dbh->prepare($query);
815 my ($currency,$price)=@_;
818 my $query="Select rate from currency where currency='$currency'";
819 my $sth=$dbh->prepare($query);
821 my $data=$sth->fetchrow_hashref;
824 my $cur=$data->{'rate'};
828 $convertedprice=$price / $cur;
829 return($convertedprice);
834 my $query="Select * from currency";
835 my $sth=$dbh->prepare($query);
839 while (my $data=$sth->fetchrow_hashref){
845 return($i,\@results);
851 my $query="Select * from currency where currency='$cur'";
852 my $sth=$dbh->prepare($query);
855 my $data=$sth->fetchrow_hashref;
861 sub updatecurrencies {
862 my ($currency,$rate)=@_;
864 my $query="update currency set rate=$rate where currency='$currency'";
865 my $sth=$dbh->prepare($query);
874 my $query="Update aqbooksellers set
875 name='$data->{'name'}',address1='$data->{'address1'}',address2='$data->{'address2'}',
876 address3='$data->{'address3'}',address4='$data->{'address4'}',postal='$data->{'postal'}',
877 phone='$data->{'phone'}',fax='$data->{'fax'}',url='$data->{'url'}',
878 contact='$data->{'contact'}',contpos='$data->{'contpos'}',
879 contphone='$data->{'contphone'}', contfax='$data->{'contfax'}', contaltphone=
880 '$data->{'contaltphone'}', contemail='$data->{'contemail'}', contnotes=
881 '$data->{'contnotes'}', active=$data->{'active'},
882 listprice='$data->{'listprice'}', invoiceprice='$data->{'invoiceprice'}',
883 gstreg=$data->{'gstreg'}, listincgst=$data->{'listincgst'},
884 invoiceincgst=$data->{'invoiceincgst'}, specialty='$data->{'specialty'}',
885 discount='$data->{'discount'}',invoicedisc='$data->{'invoicedisc'}',
886 nocalc='$data->{'nocalc'}'
887 where id='$data->{'id'}'";
888 my $sth=$dbh->prepare($query);
898 my $sth=$dbh->prepare("Select max(id) from aqbooksellers");
900 my $data2=$sth->fetchrow_hashref;
902 $data2->{'max(id)'}++;
903 $sth=$dbh->prepare("Insert into aqbooksellers (id) values ($data2->{'max(id)'})");
906 $data->{'id'}=$data2->{'max(id)'};
909 return($data->{'id'});
914 my ($item, @barcodes) = @_;
916 my $query = "Select max(itemnumber) from items";
917 my $sth = $dbh->prepare($query);
923 $data = $sth->fetchrow_hashref;
924 $itemnumber = $data->{'max(itemnumber)'} + 1;
927 $item->{'booksellerid'} = $dbh->quote($item->{'bookselletid'});
928 $item->{'homebranch'} = $dbh->quote($item->{'homebranch'});
929 $item->{'price'} = $dbh->quote($item->{'price'});
930 $item->{'replacementprice'} = $dbh->quote($item->{'replacementprice'});
931 $item->{'itemnotes'} = $dbh->quote($item->{'itemnotes'});
933 foreach my $barcode (@barcodes) {
934 $barcode = uc($barcode);
935 $query = "Insert into items set
936 itemnumber = $itemnumber,
937 biblionumber = $item->{'biblionumber'},
938 biblioitemnumber = $item->{'biblioitemnumber'},
940 booksellerid = $item->{'booksellerid'},
941 dateaccessioned = NOW(),
942 homebranch = $item->{'homebranch'},
943 holdingbranch = $item->{'homebranch'},
944 price = $item->{'price'},
945 replacementprice = $item->{'replacementprice'},
946 replacementpricedate = NOW(),
947 itemnotes = $item->{'itemnotes'}";
949 if ($item->{'loan'}) {
951 notforloan = $item->{'loan'}";
954 $sth = $dbh->prepare($query);
968 my ($count,@barcodes)=@_;
971 for (my $i=0;$i<$count;$i++){
972 $barcodes[$i]=uc $barcodes[$i];
973 my $query="Select * from items where barcode='$barcodes[$i]'";
974 my $sth=$dbh->prepare($query);
976 if (my $data=$sth->fetchrow_hashref){
977 $error.=" Duplicate Barcode: $barcodes[$i]";
986 my ($loan,$itemnum,$bibitemnum,$barcode,$notes,$homebranch,$lost,$wthdrawn,$replacement)=@_;
988 my $query="update items set biblioitemnumber=$bibitemnum,
989 barcode='$barcode',itemnotes='$notes'
990 where itemnumber=$itemnum";
992 $query="update items set biblioitemnumber=$bibitemnum,notforloan=$loan where itemnumber=$itemnum";
995 $query="update items set biblioitemnumber=$bibitemnum,
996 barcode='$barcode',itemnotes='$notes',homebranch='$homebranch',
997 itemlost='$lost',wthdrawn='$wthdrawn' where itemnumber=$itemnum";
999 if ($replacement ne ''){
1000 $query=~ s/ where/,replacementprice='$replacement' where/;
1003 my $sth=$dbh->prepare($query);
1010 my($price,$rrp,$itemnum)=@_;
1012 my $query="update items set price='$price',replacementprice='$rrp'
1013 where itemnumber=$itemnum";
1014 my $sth=$dbh->prepare($query);
1020 my ($bibitemnum)=@_;
1022 my $query="Select count(*) from items where biblioitemnumber='$bibitemnum'";
1023 my $sth=$dbh->prepare($query);
1025 my $data=$sth->fetchrow_hashref;
1028 return($data->{'count(*)'});
1032 my ($biblionumber)=@_;
1034 my $query="Select * from biblioitems,items,itemtypes where
1035 biblioitems.biblionumber=$biblionumber
1036 and biblioitems.biblioitemnumber=items.biblioitemnumber and
1037 itemtypes.itemtype=biblioitems.itemtype
1038 order by items.biblioitemnumber";
1039 my $sth=$dbh->prepare($query);
1043 while (my $data=$sth->fetchrow_hashref){
1053 my ($bibitemnum,$itemtype)=@_;
1055 my $query="Select * from biblioitems where biblioitemnumber=$bibitemnum
1056 and itemtype='$itemtype'";
1057 my $sth=$dbh->prepare($query);
1060 if (my $data=$sth->fetchrow_hashref){
1071 my $query="select * from items where itemnumber=$itemnum";
1072 my $sth=$dbh->prepare($query);
1074 my @data=$sth->fetchrow_array;
1076 $query="Insert into deleteditems values (";
1077 foreach my $temp (@data){
1078 $query=$query."'$temp',";
1082 $sth=$dbh->prepare($query);
1085 $query = "Delete from items where itemnumber=$itemnum";
1086 $sth=$dbh->prepare($query);
1095 my $query="select * from biblioitems where biblioitemnumber=$itemnum";
1096 my $sth=$dbh->prepare($query);
1098 if (my @data=$sth->fetchrow_array){
1100 $query="Insert into deletedbiblioitems values (";
1101 foreach my $temp (@data){
1102 $temp=~ s/\'/\\\'/g;
1103 $query=$query."'$temp',";
1107 $sth=$dbh->prepare($query);
1110 $query = "Delete from biblioitems where biblioitemnumber=$itemnum";
1111 $sth=$dbh->prepare($query);
1122 my $query="select * from biblio where biblionumber=$biblio";
1123 my $sth=$dbh->prepare($query);
1125 if (my @data=$sth->fetchrow_array){
1127 $query="Insert into deletedbiblio values (";
1128 foreach my $temp (@data){
1129 $temp=~ s/\'/\\\'/g;
1130 $query=$query."'$temp',";
1134 $sth=$dbh->prepare($query);
1137 $query = "Delete from biblio where biblionumber=$biblio";
1138 $sth=$dbh->prepare($query);
1148 my $dbh = C4Connect;
1149 my $query = "select * from itemtypes";
1150 my $sth = $dbh->prepare($query);
1151 # || die "Cannot prepare $query" . $dbh->errstr;
1156 # || die "Cannot execute $query\n" . $sth->errstr;
1157 while (my $data = $sth->fetchrow_hashref) {
1158 $results[$count] = $data;
1164 return($count, @results);
1165 } # sub getitemtypes
1169 my ($biblionumber) = @_;
1170 my $dbh = C4Connect;
1171 my $query = "Select * from biblio where biblionumber = $biblionumber";
1172 my $sth = $dbh->prepare($query);
1173 # || die "Cannot prepare $query\n" . $dbh->errstr;
1178 # || die "Cannot execute $query\n" . $sth->errstr;
1179 while (my $data = $sth->fetchrow_hashref) {
1180 $results[$count] = $data;
1186 return($count, @results);
1191 my ($biblioitemnum) = @_;
1192 my $dbh = C4Connect;
1193 my $query = "Select * from biblioitems where
1194 biblioitemnumber = $biblioitemnum";
1195 my $sth = $dbh->prepare($query);
1201 while (my $data = $sth->fetchrow_hashref) {
1202 $results[$count] = $data;
1208 return($count, @results);
1209 } # sub getbiblioitem
1212 sub getitemsbybiblioitem {
1213 my ($biblioitemnum) = @_;
1214 my $dbh = C4Connect;
1215 my $query = "Select * from items, biblio where
1216 biblio.biblionumber = items.biblionumber and biblioitemnumber
1218 my $sth = $dbh->prepare($query);
1219 # || die "Cannot prepare $query\n" . $dbh->errstr;
1224 # || die "Cannot execute $query\n" . $sth->errstr;
1225 while (my $data = $sth->fetchrow_hashref) {
1226 $results[$count] = $data;
1232 return($count, @results);
1233 } # sub getitemsbybiblioitem
1238 my $dbh = C4Connect;
1244 $isbn = $dbh->quote($isbn);
1245 $query = "Select * from biblioitems where isbn = $isbn";
1246 $sth = $dbh->prepare($query);
1249 while (my $data = $sth->fetchrow_hashref) {
1250 $results[$count] = $data;
1256 return($count, @results);
1261 my ($keywordlist) = @_;
1262 my $dbh = C4Connect;
1263 my $query = "Select * from biblio where";
1267 my @keywords = split(/ +/, $keywordlist);
1268 my $keyword = shift(@keywords);
1270 $keyword =~ s/%/\\%/g;
1271 $keyword =~ s/_/\\_/;
1272 $keyword = "%" . $keyword . "%";
1273 $keyword = $dbh->quote($keyword);
1274 $query .= " (author like $keyword) or
1275 (title like $keyword) or (unititle like $keyword) or
1276 (notes like $keyword) or (seriestitle like $keyword) or
1277 (abstract like $keyword)";
1279 foreach $keyword (@keywords) {
1280 $keyword =~ s/%/\\%/;
1281 $keyword =~ s/_/\\_/;
1282 $keyword = "%" . $keyword . "%";
1283 $keyword = $dbh->quote($keyword);
1284 $query .= " or (author like $keyword) or
1285 (title like $keyword) or (unititle like $keyword) or
1286 (notes like $keyword) or (seriestitle like $keyword) or
1287 (abstract like $keyword)";
1290 $sth = $dbh->prepare($query);
1293 while (my $data = $sth->fetchrow_hashref) {
1294 $results[$count] = $data;
1300 return($count, @results);
1301 } # sub keywordsearch
1305 my ($keywordlist) = @_;
1306 my $dbh = C4Connect;
1307 my $query = "Select distinct biblio.* from biblio, biblioitems where
1308 biblio.biblionumber = biblioitems.biblionumber and (";
1312 my @keywords = split(/ +/, $keywordlist);
1313 my $keyword = shift(@keywords);
1315 $keyword =~ s/%/\\%/g;
1316 $keyword =~ s/_/\\_/;
1317 $keyword = "%" . $keyword . "%";
1318 $keyword = $dbh->quote($keyword);
1319 $query .= " (url like $keyword)";
1321 foreach $keyword (@keywords) {
1322 $keyword =~ s/%/\\%/;
1323 $keyword =~ s/_/\\_/;
1324 $keyword = "%" . $keyword . "%";
1325 $keyword = $dbh->quote($keyword);
1326 $query .= " or (url like $keyword)";
1330 $sth = $dbh->prepare($query);
1333 while (my $data = $sth->fetchrow_hashref) {
1334 $results[$count] = $data;
1340 return($count, @results);
1341 } # sub websitesearch
1346 my $dbh = C4Connect;
1349 $website->{'biblionumber'} = $dbh->quote($website->{'biblionumber'});
1350 $website->{'title'} = $dbh->quote($website->{'title'});
1351 $website->{'description'} = $dbh->quote($website->{'description'});
1352 $website->{'url'} = $dbh->quote($website->{'url'});
1354 $query = "Insert into websites set
1355 biblionumber = $website->{'biblionumber'},
1356 title = $website->{'title'},
1357 description = $website->{'description'},
1358 url = $website->{'url'}";
1368 my $dbh = C4Connect;
1371 $website->{'title'} = $dbh->quote($website->{'title'});
1372 $website->{'description'} = $dbh->quote($website->{'description'});
1373 $website->{'url'} = $dbh->quote($website->{'url'});
1375 $query = "Update websites set
1376 title = $website->{'title'},
1377 description = $website->{'description'},
1378 url = $website->{'url'}
1379 where websitenumber = $website->{'websitenumber'}";
1384 } # sub updatewebsite
1388 my ($websitenumber) = @_;
1389 my $dbh = C4Connect;
1390 my $query = "Delete from websites where websitenumber = $websitenumber";
1395 } # sub deletewebsite
1398 END { } # module clean-up code here (global destructor)