1 package C4::Acquisitions; #assumes C4/Acquisitions.pm
8 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
10 # set the version for version checking
14 @EXPORT = qw(&getorders &bookseller &breakdown &basket &newbasket &bookfunds
15 &ordersearch &newbiblio &newbiblioitem &newsubject &newsubtitle &neworder
16 &newordernum &modbiblio &modorder &getsingleorder &invoice &receiveorder
17 &bookfundbreakdown &curconvert &updatesup &insertsup &newitems &modbibitem
18 &getcurrencies &modsubtitle &modsubject &modaddauthor &moditem &countitems
19 &findall &needsmod &delitem &deletebiblioitem &delbiblio &delorder &branches
20 &getallorders &getrecorders &updatecurrencies &getorder &getcurrency &updaterecorder
21 &updatecost &checkitems &modnote &getitemtypes &getbiblio
22 &getbiblioitembybiblionumber
23 &getbiblioitem &getitemsbybiblioitem &isbnsearch
24 &websitesearch &addwebsite &updatewebsite &deletewebsite);
25 %EXPORT_TAGS = ( ); # eg: TAG => [ qw!name1 name2! ],
27 # your exported package globals go here,
28 # as well as any optionally exported functions
30 @EXPORT_OK = qw($Var1 %Hashit);
33 # non-exported package globals go here
34 use vars qw(@more $stuff);
36 # initalize package globals, first exported ones
43 # then the others (which are still accessible as $Some::Module::stuff)
47 # all file-scoped lexicals must be created before
48 # the functions below that use them.
50 # file-private lexicals go here
54 # here's a file-private function as a closure,
55 # callable as &$priv_func; it cannot be prototyped.
60 # make all your functions, whether exported or not;
65 my $query = "Select count(*),authorisedby,entrydate,basketno from aqorders where
66 booksellerid='$supplierid' and (quantity > quantityreceived or
67 quantityreceived is NULL)
68 and (datecancellationprinted is NULL or datecancellationprinted = '0000-00-00')";
69 $query.=" group by basketno order by entrydate desc";
71 my $sth=$dbh->prepare($query);
75 while (my $data=$sth->fetchrow_hashref){
81 return ($i,\@results);
87 my $query="Select count(*) from items where biblionumber=$biblio";
89 my $sth=$dbh->prepare($query);
91 my $data=$sth->fetchrow_hashref;
94 return($data->{'count(*)'});
100 my $query="Select ordernumber
102 where biblionumber=? and biblioitemnumber=?";
103 my $sth=$dbh->prepare($query);
104 $sth->execute($bib,$bi);
105 my $ordnum=$sth->fetchrow_hashref;
107 my $order=getsingleorder($ordnum->{'ordernumber'});
110 return ($order,$ordnum->{'ordernumber'});
116 my $query="Select * from biblio,biblioitems,aqorders,aqorderbreakdown
117 where aqorders.ordernumber=?
118 and biblio.biblionumber=aqorders.biblionumber and
119 biblioitems.biblioitemnumber=aqorders.biblioitemnumber and
120 aqorders.ordernumber=aqorderbreakdown.ordernumber";
121 my $sth=$dbh->prepare($query);
122 $sth->execute($ordnum);
123 my $data=$sth->fetchrow_hashref;
132 my $query="Select * from aqorders,biblio,biblioitems where
133 booksellerinvoicenumber='$invoice'
134 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber=
135 aqorders.biblioitemnumber group by aqorders.ordernumber,aqorders.biblioitemnumber";
138 my $sth=$dbh->prepare($query);
140 while (my $data=$sth->fetchrow_hashref){
150 #gets all orders from a certain supplier, orders them alphabetically
153 my $query="Select * from aqorders,biblio,biblioitems where booksellerid='$supid'
154 and (cancelledby is NULL or cancelledby = '')
155 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber=
156 aqorders.biblioitemnumber
157 group by aqorders.biblioitemnumber
162 my $sth=$dbh->prepare($query);
164 while (my $data=$sth->fetchrow_hashref){
174 #gets all orders from a certain supplier, orders them alphabetically
177 my $query="Select * from aqorders,biblio,biblioitems where booksellerid='$supid'
178 and (cancelledby is NULL or cancelledby = '')
179 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber=
180 aqorders.biblioitemnumber and
181 aqorders.quantityreceived>0
182 and aqorders.datereceived >=now()
183 group by aqorders.biblioitemnumber
188 my $sth=$dbh->prepare($query);
190 while (my $data=$sth->fetchrow_hashref){
200 my ($search,$biblio,$catview)=@_;
202 my $query="Select *,biblio.title from aqorders,biblioitems,biblio
203 where aqorders.biblioitemnumber = biblioitems.biblioitemnumber
204 and biblio.biblionumber=aqorders.biblionumber
205 and ((datecancellationprinted is NULL)
206 or (datecancellationprinted = '0000-00-00')
208 my @data=split(' ',$search);
210 for (my $i=0;$i<$count;$i++){
211 $query.= "(biblio.title like '$data[$i]%' or biblio.title like '% $data[$i]%') and ";
214 $query.=" ) or biblioitems.isbn='$search'
215 or (aqorders.ordernumber='$search' and aqorders.biblionumber='$biblio')) ";
216 if ($catview ne 'yes'){
217 $query.=" and (quantityreceived < quantity or quantityreceived is NULL)";
219 $query.=" group by aqorders.ordernumber";
220 my $sth=$dbh->prepare($query);
225 while (my $data=$sth->fetchrow_hashref){
226 my $sth2=$dbh->prepare("Select * from biblio where
227 biblionumber='$data->{'biblionumber'}'");
229 my $data2=$sth2->fetchrow_hashref;
231 $data->{'author'}=$data2->{'author'};
232 $data->{'seriestitle'}=$data2->{'seriestitle'};
233 $sth2=$dbh->prepare("Select * from aqorderbreakdown where
234 ordernumber=$data->{'ordernumber'}");
236 $data2=$sth2->fetchrow_hashref;
238 $data->{'branchcode'}=$data2->{'branchcode'};
239 $data->{'bookfundid'}=$data2->{'bookfundid'};
250 my ($searchstring)=@_;
252 my $query="Select * from aqbooksellers where name like '%$searchstring%' or
253 id = '$searchstring'";
254 my $sth=$dbh->prepare($query);
258 while (my $data=$sth->fetchrow_hashref){
270 my $query="Select * from aqorderbreakdown where ordernumber='$id'";
271 my $sth=$dbh->prepare($query);
275 while (my $data=$sth->fetchrow_hashref){
281 return($i,\@results);
285 my ($basketno,$supplier)=@_;
287 my $query="Select *,biblio.title from aqorders,biblio,biblioitems
288 where basketno='$basketno'
289 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber
290 =aqorders.biblioitemnumber
291 and (datecancellationprinted is NULL or datecancellationprinted =
293 if ($supplier ne ''){
294 $query.=" and aqorders.booksellerid='$supplier'";
296 $query.=" group by aqorders.ordernumber";
297 my $sth=$dbh->prepare($query);
302 while (my $data=$sth->fetchrow_hashref){
313 my $query="Select max(basketno) from aqorders";
314 my $sth=$dbh->prepare($query);
316 my $data=$sth->fetchrow_arrayref;
317 my $basket=$$data[0];
326 my $query="Select * from aqbookfund,aqbudget where aqbookfund.bookfundid
328 and aqbudget.startdate='2001-07-01'
329 group by aqbookfund.bookfundid order by bookfundname";
330 my $sth=$dbh->prepare($query);
334 while (my $data=$sth->fetchrow_hashref){
345 my $query="Select * from branches";
346 my $sth=$dbh->prepare($query);
351 while (my $data = $sth->fetchrow_hashref){
358 return($i, @results);
361 sub bookfundbreakdown {
364 my $query="Select quantity,datereceived,freight,unitprice,listprice,ecost,quantityreceived,subscription
365 from aqorders,aqorderbreakdown where bookfundid='$id' and
366 aqorders.ordernumber=aqorderbreakdown.ordernumber and ((budgetdate >=
367 '2001-07-01' and budgetdate <'2002-07-01') or
368 (datereceived >= '2001-07-01' and datereceived < '2002-07-01'))
369 and (datecancellationprinted is NULL or
370 datecancellationprinted='0000-00-00')";
371 my $sth=$dbh->prepare($query);
375 while (my $data=$sth->fetchrow_hashref){
376 if ($data->{'subscription'} == 1){
377 $spent+=$data->{'quantity'}*$data->{'unitprice'};
379 my $leftover=$data->{'quantity'}-$data->{'quantityreceived'};
380 $comtd+=($data->{'ecost'})*$leftover;
381 $spent+=($data->{'unitprice'})*$data->{'quantityreceived'};
386 return($spent,$comtd);
392 my $dbh = &C4Connect;
393 my $query = "Select max(biblionumber) from biblio";
394 my $sth = $dbh->prepare($query);
396 my $data = $sth->fetchrow_arrayref;
397 my $bibnum = $$data[0] + 1;
400 $biblio->{'title'} = $dbh->quote($biblio->{'title'});
401 $biblio->{'author'} = $dbh->quote($biblio->{'author'});
402 $biblio->{'copyright'} = $dbh->quote($biblio->{'copyright'});
403 $biblio->{'seriestitle'} = $dbh->quote($biblio->{'seriestitle'});
404 $biblio->{'notes'} = $dbh->quote($biblio->{'notes'});
405 $biblio->{'abstract'} = $dbh->quote($biblio->{'abstract'});
406 if ($biblio->{'seriestitle'}) { $series = 1 };
409 $query = "insert into biblio set
410 biblionumber = $bibnum,
411 title = $biblio->{'title'},
412 author = $biblio->{'author'},
413 copyrightdate = $biblio->{'copyright'},
415 seriestitle = $biblio->{'seriestitle'},
416 notes = $biblio->{'notes'},
417 abstract = $biblio->{'abstract'}";
419 $sth = $dbh->prepare($query);
434 $biblio->{'title'} = $dbh->quote($biblio->{'title'});
435 $biblio->{'author'} = $dbh->quote($biblio->{'author'});
436 $biblio->{'abstract'} = $dbh->quote($biblio->{'abstract'});
437 $biblio->{'copyrightdate'} = $dbh->quote($biblio->{'copyrightdate'});
438 $biblio->{'seriestitle'} = $dbh->quote($biblio->{'serirestitle'});
439 $biblio->{'serial'} = $dbh->quote($biblio->{'serial'});
440 $biblio->{'unititle'} = $dbh->quote($biblio->{'unititle'});
441 $biblio->{'notes'} = $dbh->quote($biblio->{'notes'});
443 $query = "Update biblio set
444 title = $biblio->{'title'},
445 author = $biblio->{'author'},
446 abstract = $biblio->{'abstract'},
447 copyrightdate = $biblio->{'copyrightdate'},
448 seriestitle = $biblio->{'seriestitle'},
449 serial = $biblio->{'serial'},
450 unititle = $biblio->{'unititle'},
451 notes = $biblio->{'notes'}
452 where biblionumber = $biblio->{'biblionumber'}";
453 $sth = $dbh->prepare($query);
459 return($biblio->{'biblionumber'});
464 my ($bibnum, $subtitle) = @_;
466 my $query = "update bibliosubtitle set
467 subtitle = '$subtitle'
468 where biblionumber = $bibnum";
469 my $sth = $dbh->prepare($query);
478 my ($bibnum, $author) = @_;
480 my $query = "Delete from additionalauthors where biblionumber = $bibnum";
481 my $sth = $dbh->prepare($query);
487 $query = "Insert into additionalauthors set
489 biblionumber = '$bibnum'";
490 $sth = $dbh->prepare($query);
502 my ($bibnum, $force, @subject) = @_;
504 my $count = @subject;
506 for (my $i = 0; $i < $count; $i++) {
507 $subject[$i] =~ s/^ //g;
508 $subject[$i] =~ s/ $//g;
509 my $query = "select * from catalogueentry
510 where entrytype = 's'
511 and catalogueentry = '$subject[$i]'";
512 my $sth = $dbh->prepare($query);
515 if (my $data = $sth->fetchrow_hashref) {
517 if ($force eq $subject[$i]) {
519 # subject not in aut, chosen to force anway
520 # so insert into cataloguentry so its in auth file
521 $query = "Insert into catalogueentry
522 (entrytype,catalogueentry)
523 values ('s','$subject[$i]')";
524 my $sth2 = $dbh->prepare($query);
531 $error = "$subject[$i]\n does not exist in the subject authority file";
532 $query = "Select * from catalogueentry
533 where entrytype = 's'
534 and (catalogueentry like '$subject[$i] %'
535 or catalogueentry like '% $subject[$i] %'
536 or catalogueentry like '% $subject[$i]')";
537 my $sth2 = $dbh->prepare($query);
540 while (my $data = $sth2->fetchrow_hashref) {
541 $error = $error."<br>$data->{'catalogueentry'}";
550 my $query = "Delete from bibliosubject where biblionumber = $bibnum";
551 my $sth = $dbh->prepare($query);
556 for (my $i = 0; $i < $count; $i++) {
557 $sth = $dbh->prepare("Insert into bibliosubject
558 values ('$subject[$i]', $bibnum)");
571 my ($biblioitem) = @_;
575 $biblioitem->{'itemtype'} = $dbh->quote($biblioitem->{'itemtype'});
576 $biblioitem->{'url'} = $dbh->quote($biblioitem->{'url'});
577 $biblioitem->{'isbn'} = $dbh->quote($biblioitem->{'isbn'});
578 $biblioitem->{'publishercode'} = $dbh->quote($biblioitem->{'publishercode'});
579 $biblioitem->{'publicationyear'} = $dbh->quote($biblioitem->{'publicationyear'});
580 $biblioitem->{'classification'} = $dbh->quote($biblioitem->{'classification'});
581 $biblioitem->{'dewey'} = $dbh->quote($biblioitem->{'dewey'});
582 $biblioitem->{'subclass'} = $dbh->quote($biblioitem->{'subclass'});
583 $biblioitem->{'illus'} = $dbh->quote($biblioitem->{'illus'});
584 $biblioitem->{'pages'} = $dbh->quote($biblioitem->{'pages'});
585 $biblioitem->{'volumeddesc'} = $dbh->quote($biblioitem->{'volumeddesc'});
586 $biblioitem->{'notes'} = $dbh->quote($biblioitem->{'notes'});
587 $biblioitem->{'size'} = $dbh->quote($biblioitem->{'size'});
588 $biblioitem->{'place'} = $dbh->quote($biblioitem->{'place'});
590 $query = "Update biblioitems set
591 itemtype = $biblioitem->{'itemtype'},
592 url = $biblioitem->{'url'},
593 isbn = $biblioitem->{'isbn'},
594 publishercode = $biblioitem->{'publishercode'},
595 publicationyear = $biblioitem->{'publicationyear'},
596 classification = $biblioitem->{'classification'},
597 dewey = $biblioitem->{'dewey'},
598 subclass = $biblioitem->{'subclass'},
599 illus = $biblioitem->{'illus'},
600 pages = $biblioitem->{'pages'},
601 volumeddesc = $biblioitem->{'volumeddesc'},
602 notes = $biblioitem->{'notes'},
603 size = $biblioitem->{'size'},
604 place = $biblioitem->{'place'}
605 where biblioitemnumber = $biblioitem->{'biblioitemnumber'}";
614 my ($bibitemnum,$note)=@_;
616 my $query="update biblioitems set notes='$note' where
617 biblioitemnumber='$bibitemnum'";
618 my $sth=$dbh->prepare($query);
625 my ($biblioitem) = @_;
627 my $query = "Select max(biblioitemnumber) from biblioitems";
628 my $sth = $dbh->prepare($query);
632 $biblioitem->{'volume'} = $dbh->quote($biblioitem->{'volume'});
633 $biblioitem->{'number'} = $dbh->quote($biblioitem->{'number'});
634 $biblioitem->{'classification'} = $dbh->quote($biblioitem->{'classification'});
635 $biblioitem->{'itemtype'} = $dbh->quote($biblioitem->{'itemtype'});
636 $biblioitem->{'url'} = $dbh->quote($biblioitem->{'url'});
637 $biblioitem->{'isbn'} = $dbh->quote($biblioitem->{'isbn'});
638 $biblioitem->{'issn'} = $dbh->quote($biblioitem->{'issn'});
639 $biblioitem->{'dewey'} = $dbh->quote($biblioitem->{'dewey'});
640 $biblioitem->{'subclass'} = $dbh->quote($biblioitem->{'subclass'});
641 $biblioitem->{'publicationyear'} = $dbh->quote($biblioitem->{'publicationyear'});
642 $biblioitem->{'publishercode'} = $dbh->quote($biblioitem->{'publishercode'});
643 $biblioitem->{'volumedate'} = $dbh->quote($biblioitem->{'volumedate'});
644 $biblioitem->{'volumeddesc'} = $dbh->quote($biblioitem->{'volumeddesc'}); $biblioitem->{'illus'} = $dbh->quote($biblioitem->{'illus'});
645 $biblioitem->{'illus'} = $dbh->quote($biblioitem->{'illus'});
646 $biblioitem->{'pages'} = $dbh->quote($biblioitem->{'pages'});
647 $biblioitem->{'notes'} = $dbh->quote($biblioitem->{'notes'});
648 $biblioitem->{'size'} = $dbh->quote($biblioitem->{'size'});
649 $biblioitem->{'place'} = $dbh->quote($biblioitem->{'place'});
650 $biblioitem->{'lccn'} = $dbh->quote($biblioitem->{'lccn'});
651 $biblioitem->{'marc'} = $dbh->quote($biblioitem->{'marc'});
654 $data = $sth->fetchrow_arrayref;
655 $bibitemnum = $$data[0] + 1;
659 $query = "insert into biblioitems set
660 biblioitemnumber = $bibitemnum,
661 biblionumber = $biblioitem->{'biblionumber'},
662 volume = $biblioitem->{'volume'},
663 number = $biblioitem->{'number'},
664 classification = $biblioitem->{'classification'},
665 itemtype = $biblioitem->{'itemtype'},
666 url = $biblioitem->{'url'},
667 isbn = $biblioitem->{'isbn'},
668 issn = $biblioitem->{'issn'},
669 dewey = $biblioitem->{'dewey'},
670 subclass = $biblioitem->{'subclass'},
671 publicationyear = $biblioitem->{'publicationyear'},
672 publishercode = $biblioitem->{'publishercode'},
673 volumedate = $biblioitem->{'volumedate'},
674 volumeddesc = $biblioitem->{'volumeddesc'},
675 illus = $biblioitem->{'illus'},
676 pages = $biblioitem->{'pages'},
677 notes = $biblioitem->{'notes'},
678 size = $biblioitem->{'size'},
679 lccn = $biblioitem->{'lccn'},
680 marc = $biblioitem->{'marc'},
681 place = $biblioitem->{'place'}";
683 $sth = $dbh->prepare($query);
694 my $query="insert into bibliosubject (biblionumber) values
696 my $sth=$dbh->prepare($query);
704 my ($bibnum, $subtitle) = @_;
706 $subtitle = $dbh->quote($subtitle);
707 my $query = "insert into bibliosubtitle set
708 biblionumber = $bibnum,
709 subtitle = $subtitle";
710 my $sth = $dbh->prepare($query);
719 my ($bibnum,$title,$ordnum,$basket,$quantity,$listprice,$supplier,$who,$notes,$bookfund,$bibitemnum,$rrp,$ecost,$gst,$budget,$cost,$sub,$invoice)=@_;
720 if ($budget eq 'now'){
723 $budget="'2001-07-01'";
731 my $query="insert into aqorders (biblionumber,title,basketno,
732 quantity,listprice,booksellerid,entrydate,requisitionedby,authorisedby,notes,
733 biblioitemnumber,rrp,ecost,gst,unitprice,subscription,booksellerinvoicenumber)
736 ($bibnum,'$title',$basket,$quantity,$listprice,'$supplier',now(),
737 '$who','$who','$notes',$bibitemnum,'$rrp','$ecost','$gst','$cost',
739 my $sth=$dbh->prepare($query);
743 $query="select * from aqorders where
744 biblionumber=$bibnum and basketno=$basket and ordernumber >=$ordnum";
745 $sth=$dbh->prepare($query);
747 my $data=$sth->fetchrow_hashref;
749 $ordnum=$data->{'ordernumber'};
750 $query="insert into aqorderbreakdown (ordernumber,bookfundid) values
751 ($ordnum,'$bookfund')";
752 $sth=$dbh->prepare($query);
760 my ($bibnum,$ordnum)=@_;
762 my $query="update aqorders set datecancellationprinted=now()
763 where biblionumber='$bibnum' and
764 ordernumber='$ordnum'";
765 my $sth=$dbh->prepare($query);
769 my $count=itemcount($bibnum);
777 my ($title,$ordnum,$quantity,$listprice,$bibnum,$basketno,$supplier,$who,$notes,$bookfund,$bibitemnum,$rrp,$ecost,$gst,$budget,$cost,$invoice)=@_;
779 my $query="update aqorders set title='$title',
780 quantity='$quantity',listprice='$listprice',basketno='$basketno',
781 rrp='$rrp',ecost='$ecost',unitprice='$cost',
782 booksellerinvoicenumber='$invoice'
784 ordernumber=$ordnum and biblionumber=$bibnum";
785 my $sth=$dbh->prepare($query);
789 $query="update aqorderbreakdown set bookfundid=$bookfund where
790 ordernumber=$ordnum";
791 $sth=$dbh->prepare($query);
800 my $query="Select max(ordernumber) from aqorders";
801 my $sth=$dbh->prepare($query);
803 my $data=$sth->fetchrow_arrayref;
804 my $ordnum=$$data[0];
812 my ($biblio,$ordnum,$quantrec,$user,$cost,$invoiceno,$bibitemno,$freight,$bookfund,$rrp)=@_;
814 my $query="update aqorders set quantityreceived='$quantrec',
815 datereceived=now(),booksellerinvoicenumber='$invoiceno',
816 biblioitemnumber=$bibitemno,unitprice='$cost',freight='$freight',
818 where biblionumber=$biblio and ordernumber=$ordnum
821 my $sth=$dbh->prepare($query);
824 $query="update aqorderbreakdown set bookfundid=$bookfund where
825 ordernumber=$ordnum";
826 $sth=$dbh->prepare($query);
833 my($biblio,$ordnum,$user,$cost,$bookfund,$rrp)=@_;
835 my $query="update aqorders set
836 unitprice='$cost', rrp='$rrp'
837 where biblionumber=$biblio and ordernumber=$ordnum
840 my $sth=$dbh->prepare($query);
843 $query="update aqorderbreakdown set bookfundid=$bookfund where
844 ordernumber=$ordnum";
845 $sth=$dbh->prepare($query);
853 my ($currency,$price)=@_;
855 my $query="Select rate from currency where currency='$currency'";
856 my $sth=$dbh->prepare($query);
858 my $data=$sth->fetchrow_hashref;
861 my $cur=$data->{'rate'};
865 my $price=$price / $cur;
871 my $query="Select * from currency";
872 my $sth=$dbh->prepare($query);
876 while (my $data=$sth->fetchrow_hashref){
882 return($i,\@results);
888 my $query="Select * from currency where currency='$cur'";
889 my $sth=$dbh->prepare($query);
892 my $data=$sth->fetchrow_hashref;
898 sub updatecurrencies {
899 my ($currency,$rate)=@_;
901 my $query="update currency set rate=$rate where currency='$currency'";
902 my $sth=$dbh->prepare($query);
911 my $query="Update aqbooksellers set
912 name='$data->{'name'}',address1='$data->{'address1'}',address2='$data->{'address2'}',
913 address3='$data->{'address3'}',address4='$data->{'address4'}',postal='$data->{'postal'}',
914 phone='$data->{'phone'}',fax='$data->{'fax'}',url='$data->{'url'}',
915 contact='$data->{'contact'}',contpos='$data->{'contpos'}',
916 contphone='$data->{'contphone'}', contfax='$data->{'contfax'}', contaltphone=
917 '$data->{'contaltphone'}', contemail='$data->{'contemail'}', contnotes=
918 '$data->{'contnotes'}', active=$data->{'active'},
919 listprice='$data->{'listprice'}', invoiceprice='$data->{'invoiceprice'}',
920 gstreg=$data->{'gstreg'}, listincgst=$data->{'listincgst'},
921 invoiceincgst=$data->{'invoiceincgst'}, specialty='$data->{'specialty'}',
922 discount='$data->{'discount'}',invoicedisc='$data->{'invoicedisc'}',
923 nocalc='$data->{'nocalc'}'
924 where id='$data->{'id'}'";
925 my $sth=$dbh->prepare($query);
935 my $sth=$dbh->prepare("Select max(id) from aqbooksellers");
937 my $data2=$sth->fetchrow_hashref;
939 $data2->{'max(id)'}++;
940 $sth=$dbh->prepare("Insert into aqbooksellers (id) values ($data2->{'max(id)'})");
943 $data->{'id'}=$data2->{'max(id)'};
946 return($data->{'id'});
951 my ($item, @barcodes) = @_;
953 my $query = "Select max(itemnumber) from items";
954 my $sth = $dbh->prepare($query);
960 $data = $sth->fetchrow_hashref;
961 $itemnumber = $data->{'max(itemnumber)'} + 1;
964 $item->{'booksellerid'} = $dbh->quote($item->{'booksellerid'});
965 $item->{'homebranch'} = $dbh->quote($item->{'homebranch'});
966 $item->{'price'} = $dbh->quote($item->{'price'});
967 $item->{'replacementprice'} = $dbh->quote($item->{'replacementprice'});
968 $item->{'itemnotes'} = $dbh->quote($item->{'itemnotes'});
970 foreach my $barcode (@barcodes) {
971 $barcode = uc($barcode);
972 $barcode = $dbh->quote($barcode);
973 $query = "Insert into items set
974 itemnumber = $itemnumber,
975 biblionumber = $item->{'biblionumber'},
976 biblioitemnumber = $item->{'biblioitemnumber'},
978 booksellerid = $item->{'booksellerid'},
979 dateaccessioned = NOW(),
980 homebranch = $item->{'homebranch'},
981 holdingbranch = $item->{'homebranch'},
982 price = $item->{'price'},
983 replacementprice = $item->{'replacementprice'},
984 replacementpricedate = NOW(),
985 itemnotes = $item->{'itemnotes'}";
987 if ($item->{'loan'}) {
989 notforloan = $item->{'loan'}";
992 $sth = $dbh->prepare($query);
995 $error .= $sth->errstr;
1006 my ($count,@barcodes)=@_;
1009 for (my $i=0;$i<$count;$i++){
1010 $barcodes[$i]=uc $barcodes[$i];
1011 my $query="Select * from items where barcode='$barcodes[$i]'";
1012 my $sth=$dbh->prepare($query);
1014 if (my $data=$sth->fetchrow_hashref){
1015 $error.=" Duplicate Barcode: $barcodes[$i]";
1024 my ($loan,$itemnum,$bibitemnum,$barcode,$notes,$homebranch,$lost,$wthdrawn,$replacement)=@_;
1026 my $query="update items set biblioitemnumber=$bibitemnum,
1027 barcode='$barcode',itemnotes='$notes'
1028 where itemnumber=$itemnum";
1029 if ($barcode eq ''){
1030 $query="update items set biblioitemnumber=$bibitemnum,notforloan=$loan where itemnumber=$itemnum";
1033 $query="update items set biblioitemnumber=$bibitemnum,
1034 barcode='$barcode',itemnotes='$notes',homebranch='$homebranch',
1035 itemlost='$lost',wthdrawn='$wthdrawn' where itemnumber=$itemnum";
1037 if ($replacement ne ''){
1038 $query=~ s/ where/,replacementprice='$replacement' where/;
1041 my $sth=$dbh->prepare($query);
1048 my($price,$rrp,$itemnum)=@_;
1050 my $query="update items set price='$price',replacementprice='$rrp'
1051 where itemnumber=$itemnum";
1052 my $sth=$dbh->prepare($query);
1058 my ($bibitemnum)=@_;
1060 my $query="Select count(*) from items where biblioitemnumber='$bibitemnum'";
1061 my $sth=$dbh->prepare($query);
1063 my $data=$sth->fetchrow_hashref;
1066 return($data->{'count(*)'});
1070 my ($biblionumber)=@_;
1072 my $query="Select * from biblioitems,items,itemtypes where
1073 biblioitems.biblionumber=$biblionumber
1074 and biblioitems.biblioitemnumber=items.biblioitemnumber and
1075 itemtypes.itemtype=biblioitems.itemtype
1076 order by items.biblioitemnumber";
1077 my $sth=$dbh->prepare($query);
1081 while (my $data=$sth->fetchrow_hashref){
1091 my ($bibitemnum,$itemtype)=@_;
1093 my $query="Select * from biblioitems where biblioitemnumber=$bibitemnum
1094 and itemtype='$itemtype'";
1095 my $sth=$dbh->prepare($query);
1098 if (my $data=$sth->fetchrow_hashref){
1109 my $query="select * from items where itemnumber=$itemnum";
1110 my $sth=$dbh->prepare($query);
1112 my @data=$sth->fetchrow_array;
1114 $query="Insert into deleteditems values (";
1115 foreach my $temp (@data){
1116 $query=$query."'$temp',";
1120 $sth=$dbh->prepare($query);
1123 $query = "Delete from items where itemnumber=$itemnum";
1124 $sth=$dbh->prepare($query);
1131 sub deletebiblioitem {
1132 my ($biblioitemnumber) = @_;
1133 my $dbh = C4Connect;
1134 my $query = "Select * from biblioitems
1135 where biblioitemnumber = $biblioitemnumber";
1136 my $sth = $dbh->prepare($query);
1141 if (@results = $sth->fetchrow_array) {
1143 $query = "Insert into deletedbiblioitems values (";
1144 foreach my $value (@results) {
1145 $value = $dbh->quote($value);
1146 $query .= "$value,";
1149 $query =~ s/\,$/\)/;
1152 $query = "Delete from biblioitems
1153 where biblioitemnumber = $biblioitemnumber";
1159 # Now delete all the items attached to the biblioitem
1161 $query = "Select * from items where biblioitemnumber = $biblioitemnumber";
1162 $sth = $dbh->prepare($query);
1166 while (@results = $sth->fetchrow_array) {
1168 $query = "Insert into deleteditems values (";
1169 foreach my $value (@results) {
1170 $value = $dbh->quote($value);
1171 $query .= "$value,";
1174 $query =~ s/\,$/\)/;
1180 $query = "Delete from items where biblioitemnumber = $biblioitemnumber";
1184 } # sub deletebiblioitem
1190 my $query="select * from biblio where biblionumber=$biblio";
1191 my $sth=$dbh->prepare($query);
1193 if (my @data=$sth->fetchrow_array){
1195 $query="Insert into deletedbiblio values (";
1196 foreach my $temp (@data){
1197 $temp=~ s/\'/\\\'/g;
1198 $query=$query."'$temp',";
1202 $sth=$dbh->prepare($query);
1205 $query = "Delete from biblio where biblionumber=$biblio";
1206 $sth=$dbh->prepare($query);
1217 my $dbh = C4Connect;
1218 my $query = "select * from itemtypes";
1219 my $sth = $dbh->prepare($query);
1220 # || die "Cannot prepare $query" . $dbh->errstr;
1225 # || die "Cannot execute $query\n" . $sth->errstr;
1226 while (my $data = $sth->fetchrow_hashref) {
1227 @results[$count] = $data;
1233 return($count, @results);
1234 } # sub getitemtypes
1238 my ($biblionumber) = @_;
1239 my $dbh = C4Connect;
1240 my $query = "Select * from biblio where biblionumber = $biblionumber";
1241 my $sth = $dbh->prepare($query);
1242 # || die "Cannot prepare $query\n" . $dbh->errstr;
1247 # || die "Cannot execute $query\n" . $sth->errstr;
1248 while (my $data = $sth->fetchrow_hashref) {
1249 $results[$count] = $data;
1255 return($count, @results);
1260 my ($biblioitemnum) = @_;
1261 my $dbh = C4Connect;
1262 my $query = "Select * from biblioitems where
1263 biblioitemnumber = $biblioitemnum";
1264 my $sth = $dbh->prepare($query);
1270 while (my $data = $sth->fetchrow_hashref) {
1271 $results[$count] = $data;
1277 return($count, @results);
1278 } # sub getbiblioitem
1281 sub getbiblioitembybiblionumber {
1282 my ($biblionumber) = @_;
1283 my $dbh = C4Connect;
1284 my $query = "Select * from biblioitems where biblionumber =
1286 my $sth = $dbh->prepare($query);
1292 while (my $data = $sth->fetchrow_hashref) {
1293 $results[$count] = $data;
1299 return($count, @results);
1303 sub getitemsbybiblioitem {
1304 my ($biblioitemnum) = @_;
1305 my $dbh = C4Connect;
1306 my $query = "Select * from items, biblio where
1307 biblio.biblionumber = items.biblionumber and biblioitemnumber
1309 my $sth = $dbh->prepare($query);
1310 # || die "Cannot prepare $query\n" . $dbh->errstr;
1315 # || die "Cannot execute $query\n" . $sth->errstr;
1316 while (my $data = $sth->fetchrow_hashref) {
1317 $results[$count] = $data;
1323 return($count, @results);
1324 } # sub getitemsbybiblioitem
1329 my $dbh = C4Connect;
1335 $isbn = $dbh->quote($isbn);
1336 $query = "Select biblio.* from biblio, biblioitems where
1337 biblio.biblionumber = biblioitems.biblionumber
1339 $sth = $dbh->prepare($query);
1342 while (my $data = $sth->fetchrow_hashref) {
1343 $results[$count] = $data;
1349 return($count, @results);
1354 my ($keywordlist) = @_;
1355 my $dbh = C4Connect;
1356 my $query = "Select distinct biblio.* from biblio, biblioitems where
1357 biblio.biblionumber = biblioitems.biblionumber and (";
1361 my @keywords = split(/ +/, $keywordlist);
1362 my $keyword = shift(@keywords);
1364 $keyword =~ s/%/\\%/g;
1365 $keyword =~ s/_/\\_/;
1366 $keyword = "%" . $keyword . "%";
1367 $keyword = $dbh->quote($keyword);
1368 $query .= " (url like $keyword)";
1370 foreach $keyword (@keywords) {
1371 $keyword =~ s/%/\\%/;
1372 $keyword =~ s/_/\\_/;
1373 $keyword = "%" . $keyword . "%";
1374 $keyword = $dbh->quote($keyword);
1375 $query .= " or (url like $keyword)";
1379 $sth = $dbh->prepare($query);
1382 while (my $data = $sth->fetchrow_hashref) {
1383 $results[$count] = $data;
1389 return($count, @results);
1390 } # sub websitesearch
1395 my $dbh = C4Connect;
1398 $website->{'biblionumber'} = $dbh->quote($website->{'biblionumber'});
1399 $website->{'title'} = $dbh->quote($website->{'title'});
1400 $website->{'description'} = $dbh->quote($website->{'description'});
1401 $website->{'url'} = $dbh->quote($website->{'url'});
1403 $query = "Insert into websites set
1404 biblionumber = $website->{'biblionumber'},
1405 title = $website->{'title'},
1406 description = $website->{'description'},
1407 url = $website->{'url'}";
1417 my $dbh = C4Connect;
1420 $website->{'title'} = $dbh->quote($website->{'title'});
1421 $website->{'description'} = $dbh->quote($website->{'description'});
1422 $website->{'url'} = $dbh->quote($website->{'url'});
1424 $query = "Update websites set
1425 title = $website->{'title'},
1426 description = $website->{'description'},
1427 url = $website->{'url'}
1428 where websitenumber = $website->{'websitenumber'}";
1433 } # sub updatewebsite
1437 my ($websitenumber) = @_;
1438 my $dbh = C4Connect;
1439 my $query = "Delete from websites where websitenumber = $websitenumber";
1444 } # sub deletewebsite
1447 END { } # module clean-up code here (global destructor)