1 package C4::Acquisitions; #asummes 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 &deletebiblioitem &delbiblio &delorder &branches
19 &getallorders &getrecorders &updatecurrencies &getorder &getcurrency &updaterecorder
20 &updatecost &checkitems &modnote &getitemtypes &getbiblio
21 &getbiblioitembybiblionumber
22 &getbiblioitem &getitemsbybiblioitem &isbnsearch
23 &websitesearch &addwebsite &updatewebsite &deletewebsite);
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
42 # then the others (which are still accessible as $Some::Module::stuff)
46 # all file-scoped lexicals must be created before
47 # the functions below that use them.
49 # file-private lexicals go here
53 # here's a file-private function as a closure,
54 # callable as &$priv_func; it cannot be prototyped.
59 # make all your functions, whether exported or not;
64 my $query = "Select count(*),authorisedby,entrydate,basketno from aqorders where
65 booksellerid='$supplierid' and (quantity > quantityreceived or
66 quantityreceived is NULL)
67 and (datecancellationprinted is NULL or datecancellationprinted = '0000-00-00')";
68 $query.=" group by basketno order by entrydate desc";
70 my $sth=$dbh->prepare($query);
74 while (my $data=$sth->fetchrow_hashref){
80 return ($i,\@results);
86 my $query="Select count(*) from items where biblionumber=$biblio";
88 my $sth=$dbh->prepare($query);
90 my $data=$sth->fetchrow_hashref;
93 return($data->{'count(*)'});
99 my $query="Select ordernumber from aqorders where biblionumber=$bib and
100 biblioitemnumber='$bi'";
101 my $sth=$dbh->prepare($query);
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='$ordnum'
116 and biblio.biblionumber=aqorders.biblionumber and
117 biblioitems.biblioitemnumber=aqorders.biblioitemnumber and
118 aqorders.ordernumber=aqorderbreakdown.ordernumber";
119 my $sth=$dbh->prepare($query);
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 (quantityreceived < quantity or quantityreceived is NULL)
154 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber=
155 aqorders.biblioitemnumber
156 group by aqorders.biblioitemnumber
161 my $sth=$dbh->prepare($query);
163 while (my $data=$sth->fetchrow_hashref){
173 #gets all orders from a certain supplier, orders them alphabetically
176 my $query="Select * from aqorders,biblio,biblioitems where booksellerid='$supid'
177 and (cancelledby is NULL or cancelledby = '')
178 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber=
179 aqorders.biblioitemnumber and
180 aqorders.quantityreceived>0
181 and aqorders.datereceived >=now()
182 group by aqorders.biblioitemnumber
187 my $sth=$dbh->prepare($query);
189 while (my $data=$sth->fetchrow_hashref){
199 my ($search,$biblio,$catview) = @_;
201 my $query = "Select *,biblio.title from aqorders,biblioitems,biblio
202 where aqorders.biblioitemnumber = biblioitems.biblioitemnumber
203 and biblio.biblionumber=aqorders.biblionumber
204 and ((datecancellationprinted is NULL)
205 or (datecancellationprinted = '0000-00-00'))
207 my @data = split(' ',$search);
209 for (my $i = 0; $i < $count; $i++) {
210 $query .= "(biblio.title like '$data[$i]%' or biblio.title like '% $data[$i]%') and ";
213 $query.=" ) or biblioitems.isbn='$search'
214 or (aqorders.ordernumber='$search' and aqorders.biblionumber='$biblio')) ";
215 if ($catview ne 'yes'){
216 $query.=" and (quantityreceived < quantity or quantityreceived is NULL)";
218 $query.=" group by aqorders.ordernumber";
219 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 group by aqbookfund.bookfundid order by bookfundname";
327 my $sth=$dbh->prepare($query);
331 while (my $data=$sth->fetchrow_hashref){
342 my $query = "Select * from branches";
343 my $sth = $dbh->prepare($query);
348 while (my $data = $sth->fetchrow_hashref) {
349 $results[$i] = $data;
355 return($i, @results);
358 sub bookfundbreakdown {
361 my $query="Select quantity,datereceived,freight,unitprice,listprice,ecost,quantityreceived,subscription
362 from aqorders,aqorderbreakdown where bookfundid='$id' and
363 aqorders.ordernumber=aqorderbreakdown.ordernumber
364 and (datecancellationprinted is NULL or
365 datecancellationprinted='0000-00-00')";
366 my $sth=$dbh->prepare($query);
370 while (my $data=$sth->fetchrow_hashref){
371 if ($data->{'subscription'} == 1){
372 $spent+=$data->{'quantity'}*$data->{'unitprice'};
374 my $leftover=$data->{'quantity'}-$data->{'quantityreceived'};
375 $comtd+=($data->{'ecost'})*$leftover;
376 $spent+=($data->{'unitprice'})*$data->{'quantityreceived'};
381 return($spent,$comtd);
387 my $dbh = &C4Connect;
388 my $query = "Select max(biblionumber) from biblio";
389 my $sth = $dbh->prepare($query);
391 my $data = $sth->fetchrow_arrayref;
392 my $bibnum = $$data[0] + 1;
395 $biblio->{'title'} = $dbh->quote($biblio->{'title'});
396 $biblio->{'author'} = $dbh->quote($biblio->{'author'});
397 $biblio->{'copyright'} = $dbh->quote($biblio->{'copyright'});
398 $biblio->{'seriestitle'} = $dbh->quote($biblio->{'seriestitle'});
399 $biblio->{'notes'} = $dbh->quote($biblio->{'notes'});
400 $biblio->{'abstract'} = $dbh->quote($biblio->{'abstract'});
401 if ($biblio->{'seriestitle'}) { $series = 1 };
404 $query = "insert into biblio set
405 biblionumber = $bibnum,
406 title = $biblio->{'title'},
407 author = $biblio->{'author'},
408 copyrightdate = $biblio->{'copyright'},
410 seriestitle = $biblio->{'seriestitle'},
411 notes = $biblio->{'notes'},
412 abstract = $biblio->{'abstract'}";
414 $sth = $dbh->prepare($query);
429 $biblio->{'title'} = $dbh->quote($biblio->{'title'});
430 $biblio->{'author'} = $dbh->quote($biblio->{'author'});
431 $biblio->{'abstract'} = $dbh->quote($biblio->{'abstract'});
432 $biblio->{'copyrightdate'} = $dbh->quote($biblio->{'copyrightdate'});
433 $biblio->{'seriestitle'} = $dbh->quote($biblio->{'serirestitle'});
434 $biblio->{'serial'} = $dbh->quote($biblio->{'serial'});
435 $biblio->{'unititle'} = $dbh->quote($biblio->{'unititle'});
436 $biblio->{'notes'} = $dbh->quote($biblio->{'notes'});
438 $query = "Update biblio set
439 title = $biblio->{'title'},
440 author = $biblio->{'author'},
441 abstract = $biblio->{'abstract'},
442 copyrightdate = $biblio->{'copyrightdate'},
443 seriestitle = $biblio->{'seriestitle'},
444 serial = $biblio->{'serial'},
445 unititle = $biblio->{'unititle'},
446 notes = $biblio->{'notes'}
447 where biblionumber = $biblio->{'biblionumber'}";
448 $sth = $dbh->prepare($query);
454 return($biblio->{'biblionumber'});
459 my ($bibnum, $subtitle) = @_;
461 my $query = "update bibliosubtitle set
462 subtitle = '$subtitle'
463 where biblionumber = $bibnum";
464 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) = @_;
499 my $count = @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)");
566 my ($biblioitem) = @_;
570 $biblioitem->{'itemtype'} = $dbh->quote($biblioitem->{'itemtype'});
571 $biblioitem->{'url'} = $dbh->quote($biblioitem->{'url'});
572 $biblioitem->{'isbn'} = $dbh->quote($biblioitem->{'isbn'});
573 $biblioitem->{'publishercode'} = $dbh->quote($biblioitem->{'publishercode'});
574 $biblioitem->{'publicationyear'} = $dbh->quote($biblioitem->{'publicationyear'});
575 $biblioitem->{'classification'} = $dbh->quote($biblioitem->{'classification'});
576 $biblioitem->{'dewey'} = $dbh->quote($biblioitem->{'dewey'});
577 $biblioitem->{'subclass'} = $dbh->quote($biblioitem->{'subclass'});
578 $biblioitem->{'illus'} = $dbh->quote($biblioitem->{'illus'});
579 $biblioitem->{'pages'} = $dbh->quote($biblioitem->{'pages'});
580 $biblioitem->{'volumeddesc'} = $dbh->quote($biblioitem->{'volumeddesc'});
581 $biblioitem->{'notes'} = $dbh->quote($biblioitem->{'notes'});
582 $biblioitem->{'size'} = $dbh->quote($biblioitem->{'size'});
583 $biblioitem->{'place'} = $dbh->quote($biblioitem->{'place'});
585 $query = "Update biblioitems set
586 itemtype = $biblioitem->{'itemtype'},
587 url = $biblioitem->{'url'},
588 isbn = $biblioitem->{'isbn'},
589 publishercode = $biblioitem->{'publishercode'},
590 publicationyear = $biblioitem->{'publicationyear'},
591 classification = $biblioitem->{'classification'},
592 dewey = $biblioitem->{'dewey'},
593 subclass = $biblioitem->{'subclass'},
594 illus = $biblioitem->{'illus'},
595 pages = $biblioitem->{'pages'},
596 volumeddesc = $biblioitem->{'volumeddesc'},
597 notes = $biblioitem->{'notes'},
598 size = $biblioitem->{'size'},
599 place = $biblioitem->{'place'}
600 where biblioitemnumber = $biblioitem->{'biblioitemnumber'}";
609 my ($bibitemnum,$note)=@_;
611 my $query="update biblioitems set notes='$note' where
612 biblioitemnumber='$bibitemnum'";
613 my $sth=$dbh->prepare($query);
620 my ($biblioitem) = @_;
622 my $query = "Select max(biblioitemnumber) from biblioitems";
623 my $sth = $dbh->prepare($query);
627 $biblioitem->{'volume'} = $dbh->quote($biblioitem->{'volume'});
628 $biblioitem->{'number'} = $dbh->quote($biblioitem->{'number'});
629 $biblioitem->{'classification'} = $dbh->quote($biblioitem->{'classification'});
630 $biblioitem->{'itemtype'} = $dbh->quote($biblioitem->{'itemtype'});
631 $biblioitem->{'url'} = $dbh->quote($biblioitem->{'url'});
632 $biblioitem->{'isbn'} = $dbh->quote($biblioitem->{'isbn'});
633 $biblioitem->{'issn'} = $dbh->quote($biblioitem->{'issn'});
634 $biblioitem->{'dewey'} = $dbh->quote($biblioitem->{'dewey'});
635 $biblioitem->{'subclass'} = $dbh->quote($biblioitem->{'subclass'});
636 $biblioitem->{'publicationyear'} = $dbh->quote($biblioitem->{'publicationyear'});
637 $biblioitem->{'publishercode'} = $dbh->quote($biblioitem->{'publishercode'});
638 $biblioitem->{'volumedate'} = $dbh->quote($biblioitem->{'volumedate'});
639 $biblioitem->{'volumeddesc'} = $dbh->quote($biblioitem->{'volumeddesc'}); $biblioitem->{'illus'} = $dbh->quote($biblioitem->{'illus'});
640 $biblioitem->{'illus'} = $dbh->quote($biblioitem->{'illus'});
641 $biblioitem->{'pages'} = $dbh->quote($biblioitem->{'pages'});
642 $biblioitem->{'notes'} = $dbh->quote($biblioitem->{'notes'});
643 $biblioitem->{'size'} = $dbh->quote($biblioitem->{'size'});
644 $biblioitem->{'place'} = $dbh->quote($biblioitem->{'place'});
647 $data = $sth->fetchrow_arrayref;
648 $bibitemnum = $$data[0] + 1;
652 $query = "insert into biblioitems set
653 biblioitemnumber = $bibitemnum,
654 biblionumber = $biblioitem->{'biblionumber'},
655 volume = $biblioitem->{'volume'},
656 number = $biblioitem->{'number'},
657 classification = $biblioitem->{'classification'},
658 itemtype = $biblioitem->{'itemtype'},
659 url = $biblioitem->{'url'},
660 isbn = $biblioitem->{'isbn'},
661 issn = $biblioitem->{'issn'},
662 dewey = $biblioitem->{'dewey'},
663 subclass = $biblioitem->{'subclass'},
664 publicationyear = $biblioitem->{'publicationyear'},
665 publishercode = $biblioitem->{'publishercode'},
666 volumedate = $biblioitem->{'volumedate'},
667 volumeddesc = $biblioitem->{'volumeddesc'},
668 illus = $biblioitem->{'illus'},
669 pages = $biblioitem->{'pages'},
670 notes = $biblioitem->{'notes'},
671 size = $biblioitem->{'size'},
672 place = $biblioitem->{'place'}";
674 $sth = $dbh->prepare($query);
685 my $query="insert into bibliosubject (biblionumber) values
687 my $sth=$dbh->prepare($query);
695 my ($bibnum, $subtitle) = @_;
697 $subtitle = $dbh->quote($subtitle);
698 my $query = "insert into bibliosubtitle set
699 biblionumber = $bibnum,
700 subtitle = $subtitle";
701 my $sth = $dbh->prepare($query);
710 my ($bibnum,$title,$ordnum,$basket,$quantity,$listprice,$supplier,$who,$notes,$bookfund,$bibitemnum,$rrp,$ecost,$gst,$budget,$cost,$sub,$invoice)=@_;
711 if ($budget eq 'now'){
714 $budget="'2001-07-01'";
722 my $query="insert into aqorders (biblionumber,title,basketno,
723 quantity,listprice,booksellerid,entrydate,requisitionedby,authorisedby,notes,
724 biblioitemnumber,rrp,ecost,gst,unitprice,subscription,booksellerinvoicenumber)
727 ($bibnum,'$title',$basket,$quantity,$listprice,'$supplier',now(),
728 '$who','$who','$notes',$bibitemnum,'$rrp','$ecost','$gst','$cost',
730 my $sth=$dbh->prepare($query);
734 $query="select * from aqorders where
735 biblionumber=$bibnum and basketno=$basket and ordernumber >=$ordnum";
736 $sth=$dbh->prepare($query);
738 my $data=$sth->fetchrow_hashref;
740 $ordnum=$data->{'ordernumber'};
741 $query="insert into aqorderbreakdown (ordernumber,bookfundid) values
742 ($ordnum,'$bookfund')";
743 $sth=$dbh->prepare($query);
751 my ($bibnum,$ordnum)=@_;
753 my $query="update aqorders set datecancellationprinted=now()
754 where biblionumber='$bibnum' and
755 ordernumber='$ordnum'";
756 my $sth=$dbh->prepare($query);
760 my $count=itemcount($bibnum);
768 my ($title,$ordnum,$quantity,$listprice,$bibnum,$basketno,$supplier,$who,$notes,$bookfund,$bibitemnum,$rrp,$ecost,$gst,$budget,$cost,$invoice)=@_;
770 my $query="update aqorders set title='$title',
771 quantity='$quantity',listprice='$listprice',basketno='$basketno',
772 rrp='$rrp',ecost='$ecost',unitprice='$cost',
773 booksellerinvoicenumber='$invoice'
775 ordernumber=$ordnum and biblionumber=$bibnum";
776 my $sth=$dbh->prepare($query);
780 $query="update aqorderbreakdown set bookfundid=$bookfund where
781 ordernumber=$ordnum";
782 $sth=$dbh->prepare($query);
791 my $query="Select max(ordernumber) from aqorders";
792 my $sth=$dbh->prepare($query);
794 my $data=$sth->fetchrow_arrayref;
795 my $ordnum=$$data[0];
803 my ($biblio,$ordnum,$quantrec,$user,$cost,$invoiceno,$bibitemno,$freight,$bookfund,$rrp)=@_;
805 my $query="update aqorders set quantityreceived='$quantrec',
806 datereceived=now(),booksellerinvoicenumber='$invoiceno',
807 biblioitemnumber=$bibitemno,unitprice='$cost',freight='$freight',
809 where biblionumber=$biblio and ordernumber=$ordnum
812 my $sth=$dbh->prepare($query);
815 $query="update aqorderbreakdown set bookfundid=$bookfund where
816 ordernumber=$ordnum";
817 $sth=$dbh->prepare($query);
824 my($biblio,$ordnum,$user,$cost,$bookfund,$rrp)=@_;
826 my $query="update aqorders set
827 unitprice='$cost', rrp='$rrp'
828 where biblionumber=$biblio and ordernumber=$ordnum
831 my $sth=$dbh->prepare($query);
834 $query="update aqorderbreakdown set bookfundid=$bookfund where
835 ordernumber=$ordnum";
836 $sth=$dbh->prepare($query);
844 my ($currency,$price)=@_;
846 my $query="Select rate from currency where currency='$currency'";
847 my $sth=$dbh->prepare($query);
849 my $data=$sth->fetchrow_hashref;
852 my $cur=$data->{'rate'};
856 my $price=$price / $cur;
862 my $query="Select * from currency";
863 my $sth=$dbh->prepare($query);
867 while (my $data=$sth->fetchrow_hashref){
873 return($i,\@results);
879 my $query="Select * from currency where currency='$cur'";
880 my $sth=$dbh->prepare($query);
883 my $data=$sth->fetchrow_hashref;
889 sub updatecurrencies {
890 my ($currency,$rate)=@_;
892 my $query="update currency set rate=$rate where currency='$currency'";
893 my $sth=$dbh->prepare($query);
902 my $query="Update aqbooksellers set
903 name='$data->{'name'}',address1='$data->{'address1'}',address2='$data->{'address2'}',
904 address3='$data->{'address3'}',address4='$data->{'address4'}',postal='$data->{'postal'}',
905 phone='$data->{'phone'}',fax='$data->{'fax'}',url='$data->{'url'}',
906 contact='$data->{'contact'}',contpos='$data->{'contpos'}',
907 contphone='$data->{'contphone'}', contfax='$data->{'contfax'}', contaltphone=
908 '$data->{'contaltphone'}', contemail='$data->{'contemail'}', contnotes=
909 '$data->{'contnotes'}', active=$data->{'active'},
910 listprice='$data->{'listprice'}', invoiceprice='$data->{'invoiceprice'}',
911 gstreg=$data->{'gstreg'}, listincgst=$data->{'listincgst'},
912 invoiceincgst=$data->{'invoiceincgst'}, specialty='$data->{'specialty'}',
913 discount='$data->{'discount'}',invoicedisc='$data->{'invoicedisc'}',
914 nocalc='$data->{'nocalc'}'
915 where id='$data->{'id'}'";
916 my $sth=$dbh->prepare($query);
926 my $sth=$dbh->prepare("Select max(id) from aqbooksellers");
928 my $data2=$sth->fetchrow_hashref;
930 $data2->{'max(id)'}++;
931 $sth=$dbh->prepare("Insert into aqbooksellers (id) values ($data2->{'max(id)'})");
934 $data->{'id'}=$data2->{'max(id)'};
937 return($data->{'id'});
942 my ($item, @barcodes) = @_;
944 my $query = "Select max(itemnumber) from items";
945 my $sth = $dbh->prepare($query);
951 $data = $sth->fetchrow_hashref;
952 $itemnumber = $data->{'max(itemnumber)'} + 1;
955 $item->{'booksellerid'} = $dbh->quote($item->{'booksellerid'});
956 $item->{'homebranch'} = $dbh->quote($item->{'homebranch'});
957 $item->{'price'} = $dbh->quote($item->{'price'});
958 $item->{'replacementprice'} = $dbh->quote($item->{'replacementprice'});
959 $item->{'itemnotes'} = $dbh->quote($item->{'itemnotes'});
961 foreach my $barcode (@barcodes) {
962 $barcode = uc($barcode);
963 $barcode = $dbh->quote($barcode);
964 $query = "Insert into items set
965 itemnumber = $itemnumber,
966 biblionumber = $item->{'biblionumber'},
967 biblioitemnumber = $item->{'biblioitemnumber'},
969 booksellerid = $item->{'booksellerid'},
970 dateaccessioned = NOW(),
971 homebranch = $item->{'homebranch'},
972 holdingbranch = $item->{'homebranch'},
973 price = $item->{'price'},
974 replacementprice = $item->{'replacementprice'},
975 replacementpricedate = NOW(),
976 itemnotes = $item->{'itemnotes'}";
978 if ($item->{'loan'}) {
980 notforloan = $item->{'loan'}";
983 $sth = $dbh->prepare($query);
986 $error .= $sth->errstr;
997 my ($count,@barcodes)=@_;
1000 for (my $i=0;$i<$count;$i++){
1001 $barcodes[$i]=uc $barcodes[$i];
1002 my $query="Select * from items where barcode='$barcodes[$i]'";
1003 my $sth=$dbh->prepare($query);
1005 if (my $data=$sth->fetchrow_hashref){
1006 $error.=" Duplicate Barcode: $barcodes[$i]";
1015 my ($loan,$itemnum,$bibitemnum,$barcode,$notes,$homebranch,$lost,$wthdrawn,$replacement)=@_;
1017 my $query="update items set biblioitemnumber=$bibitemnum,
1018 barcode='$barcode',itemnotes='$notes'
1019 where itemnumber=$itemnum";
1020 if ($barcode eq ''){
1021 $query="update items set biblioitemnumber=$bibitemnum,notforloan=$loan where itemnumber=$itemnum";
1024 $query="update items set biblioitemnumber=$bibitemnum,
1025 barcode='$barcode',itemnotes='$notes',homebranch='$homebranch',
1026 itemlost='$lost',wthdrawn='$wthdrawn' where itemnumber=$itemnum";
1028 if ($replacement ne ''){
1029 $query=~ s/ where/,replacementprice='$replacement' where/;
1032 my $sth=$dbh->prepare($query);
1039 my($price,$rrp,$itemnum)=@_;
1041 my $query="update items set price='$price',replacementprice='$rrp'
1042 where itemnumber=$itemnum";
1043 my $sth=$dbh->prepare($query);
1049 my ($bibitemnum)=@_;
1051 my $query="Select count(*) from items where biblioitemnumber='$bibitemnum'";
1052 my $sth=$dbh->prepare($query);
1054 my $data=$sth->fetchrow_hashref;
1057 return($data->{'count(*)'});
1061 my ($biblionumber)=@_;
1063 my $query="Select * from biblioitems,items,itemtypes where
1064 biblioitems.biblionumber=$biblionumber
1065 and biblioitems.biblioitemnumber=items.biblioitemnumber and
1066 itemtypes.itemtype=biblioitems.itemtype
1067 order by items.biblioitemnumber";
1068 my $sth=$dbh->prepare($query);
1072 while (my $data=$sth->fetchrow_hashref){
1082 my ($bibitemnum,$itemtype)=@_;
1084 my $query="Select * from biblioitems where biblioitemnumber=$bibitemnum
1085 and itemtype='$itemtype'";
1086 my $sth=$dbh->prepare($query);
1089 if (my $data=$sth->fetchrow_hashref){
1100 my $query="select * from items where itemnumber=$itemnum";
1101 my $sth=$dbh->prepare($query);
1103 my @data=$sth->fetchrow_array;
1105 $query="Insert into deleteditems values (";
1106 foreach my $temp (@data){
1107 $query=$query."'$temp',";
1111 $sth=$dbh->prepare($query);
1114 $query = "Delete from items where itemnumber=$itemnum";
1115 $sth=$dbh->prepare($query);
1122 sub deletebiblioitem {
1123 my ($biblioitemnumber) = @_;
1124 my $dbh = C4Connect;
1125 my $query = "Select * from biblioitems
1126 where biblioitemnumber = $biblioitemnumber";
1127 my $sth = $dbh->prepare($query);
1132 if (@results = $sth->fetchrow_array) {
1134 $query = "Insert into deletedbiblioitems values (";
1135 foreach my $value (@results) {
1136 $value = $dbh->quote($value);
1137 $query .= "$value,";
1140 $query =~ s/\,$/\)/;
1143 $query = "Delete from biblioitems
1144 where biblioitemnumber = $biblioitemnumber";
1150 # Now delete all the items attached to the biblioitem
1152 $query = "Select * from items where biblioitemnumber = $biblioitemnumber";
1153 $sth = $dbh->prepare($query);
1157 while (@results = $sth->fetchrow_array) {
1159 $query = "Insert into deleteditems values (";
1160 foreach my $value (@results) {
1161 $value = $dbh->quote($value);
1162 $query .= "$value,";
1165 $query =~ s/\,$/\)/;
1171 $query = "Delete from items where biblioitemnumber = $biblioitemnumber";
1175 } # sub deletebiblioitem
1181 my $query="select * from biblio where biblionumber=$biblio";
1182 my $sth=$dbh->prepare($query);
1184 if (my @data=$sth->fetchrow_array){
1186 $query="Insert into deletedbiblio values (";
1187 foreach my $temp (@data){
1188 $temp=~ s/\'/\\\'/g;
1189 $query=$query."'$temp',";
1193 $sth=$dbh->prepare($query);
1196 $query = "Delete from biblio where biblionumber=$biblio";
1197 $sth=$dbh->prepare($query);
1208 my $dbh = C4Connect;
1209 my $query = "select * from itemtypes";
1210 my $sth = $dbh->prepare($query);
1211 # || die "Cannot prepare $query" . $dbh->errstr;
1216 # || die "Cannot execute $query\n" . $sth->errstr;
1217 while (my $data = $sth->fetchrow_hashref) {
1218 @results[$count] = $data;
1224 return($count, @results);
1225 } # sub getitemtypes
1229 my ($biblionumber) = @_;
1230 my $dbh = C4Connect;
1231 my $query = "Select * from biblio where biblionumber = $biblionumber";
1232 my $sth = $dbh->prepare($query);
1233 # || die "Cannot prepare $query\n" . $dbh->errstr;
1238 # || die "Cannot execute $query\n" . $sth->errstr;
1239 while (my $data = $sth->fetchrow_hashref) {
1240 $results[$count] = $data;
1246 return($count, @results);
1251 my ($biblioitemnum) = @_;
1252 my $dbh = C4Connect;
1253 my $query = "Select * from biblioitems where
1254 biblioitemnumber = $biblioitemnum";
1255 my $sth = $dbh->prepare($query);
1261 while (my $data = $sth->fetchrow_hashref) {
1262 $results[$count] = $data;
1268 return($count, @results);
1269 } # sub getbiblioitem
1272 sub getbiblioitembybiblionumber {
1273 my ($biblionumber) = @_;
1274 my $dbh = C4Connect;
1275 my $query = "Select * from biblioitems where biblionumber =
1277 my $sth = $dbh->prepare($query);
1283 while (my $data = $sth->fetchrow_hashref) {
1284 $results[$count] = $data;
1290 return($count, @results);
1294 sub getitemsbybiblioitem {
1295 my ($biblioitemnum) = @_;
1296 my $dbh = C4Connect;
1297 my $query = "Select * from items, biblio where
1298 biblio.biblionumber = items.biblionumber and biblioitemnumber
1300 my $sth = $dbh->prepare($query);
1301 # || die "Cannot prepare $query\n" . $dbh->errstr;
1306 # || die "Cannot execute $query\n" . $sth->errstr;
1307 while (my $data = $sth->fetchrow_hashref) {
1308 $results[$count] = $data;
1314 return($count, @results);
1315 } # sub getitemsbybiblioitem
1320 my $dbh = C4Connect;
1326 $isbn = $dbh->quote($isbn);
1327 $query = "Select biblio.* from biblio, biblioitems where
1328 biblio.biblionumber = biblioitems.biblionumber
1330 $sth = $dbh->prepare($query);
1333 while (my $data = $sth->fetchrow_hashref) {
1334 $results[$count] = $data;
1340 return($count, @results);
1345 my ($keywordlist) = @_;
1346 my $dbh = C4Connect;
1347 my $query = "Select distinct biblio.* from biblio, biblioitems where
1348 biblio.biblionumber = biblioitems.biblionumber and (";
1352 my @keywords = split(/ +/, $keywordlist);
1353 my $keyword = shift(@keywords);
1355 $keyword =~ s/%/\\%/g;
1356 $keyword =~ s/_/\\_/;
1357 $keyword = "%" . $keyword . "%";
1358 $keyword = $dbh->quote($keyword);
1359 $query .= " (url like $keyword)";
1361 foreach $keyword (@keywords) {
1362 $keyword =~ s/%/\\%/;
1363 $keyword =~ s/_/\\_/;
1364 $keyword = "%" . $keyword . "%";
1365 $keyword = $dbh->quote($keyword);
1366 $query .= " or (url like $keyword)";
1370 $sth = $dbh->prepare($query);
1373 while (my $data = $sth->fetchrow_hashref) {
1374 $results[$count] = $data;
1380 return($count, @results);
1381 } # sub websitesearch
1386 my $dbh = C4Connect;
1389 $website->{'biblionumber'} = $dbh->quote($website->{'biblionumber'});
1390 $website->{'title'} = $dbh->quote($website->{'title'});
1391 $website->{'description'} = $dbh->quote($website->{'description'});
1392 $website->{'url'} = $dbh->quote($website->{'url'});
1394 $query = "Insert into websites set
1395 biblionumber = $website->{'biblionumber'},
1396 title = $website->{'title'},
1397 description = $website->{'description'},
1398 url = $website->{'url'}";
1408 my $dbh = C4Connect;
1411 $website->{'title'} = $dbh->quote($website->{'title'});
1412 $website->{'description'} = $dbh->quote($website->{'description'});
1413 $website->{'url'} = $dbh->quote($website->{'url'});
1415 $query = "Update websites set
1416 title = $website->{'title'},
1417 description = $website->{'description'},
1418 url = $website->{'url'}
1419 where websitenumber = $website->{'websitenumber'}";
1424 } # sub updatewebsite
1428 my ($websitenumber) = @_;
1429 my $dbh = C4Connect;
1430 my $query = "Delete from websites where websitenumber = $websitenumber";
1435 } # sub deletewebsite
1438 END { } # module clean-up code here (global destructor)