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 &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 from aqorders where biblionumber=$bib and
99 biblioitemnumber='$bi'";
100 my $sth=$dbh->prepare($query);
102 my $ordnum=$sth->fetchrow_hashref;
104 my $order=getsingleorder($ordnum->{'ordernumber'});
107 return ($order,$ordnum->{'ordernumber'});
113 my $query="Select * from biblio,biblioitems,aqorders,aqorderbreakdown
114 where aqorders.ordernumber='$ordnum'
115 and biblio.biblionumber=aqorders.biblionumber and
116 biblioitems.biblioitemnumber=aqorders.biblioitemnumber and
117 aqorders.ordernumber=aqorderbreakdown.ordernumber";
118 my $sth=$dbh->prepare($query);
120 my $data=$sth->fetchrow_hashref;
129 my $query="Select * from aqorders,biblio,biblioitems where
130 booksellerinvoicenumber='$invoice'
131 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber=
132 aqorders.biblioitemnumber group by aqorders.ordernumber,aqorders.biblioitemnumber";
135 my $sth=$dbh->prepare($query);
137 while (my $data=$sth->fetchrow_hashref){
147 #gets all orders from a certain supplier, orders them alphabetically
150 my $query="Select * from aqorders,biblio,biblioitems where booksellerid='$supid'
151 and (cancelledby is NULL or cancelledby = '')
152 and (quantityreceived < quantity or quantityreceived is NULL)
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);
222 while (my $data=$sth->fetchrow_hashref){
223 my $sth2=$dbh->prepare("Select * from biblio where
224 biblionumber='$data->{'biblionumber'}'");
226 my $data2=$sth2->fetchrow_hashref;
228 $data->{'author'}=$data2->{'author'};
229 $data->{'seriestitle'}=$data2->{'seriestitle'};
230 $sth2=$dbh->prepare("Select * from aqorderbreakdown where
231 ordernumber=$data->{'ordernumber'}");
233 $data2=$sth2->fetchrow_hashref;
235 $data->{'branchcode'}=$data2->{'branchcode'};
236 $data->{'bookfundid'}=$data2->{'bookfundid'};
247 my ($searchstring)=@_;
249 my $query="Select * from aqbooksellers where name like '%$searchstring%' or
250 id = '$searchstring'";
251 my $sth=$dbh->prepare($query);
255 while (my $data=$sth->fetchrow_hashref){
267 my $query="Select * from aqorderbreakdown where ordernumber='$id'";
268 my $sth=$dbh->prepare($query);
272 while (my $data=$sth->fetchrow_hashref){
278 return($i,\@results);
282 my ($basketno,$supplier)=@_;
284 my $query="Select *,biblio.title from aqorders,biblio,biblioitems
285 where basketno='$basketno'
286 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber
287 =aqorders.biblioitemnumber
288 and (datecancellationprinted is NULL or datecancellationprinted =
290 if ($supplier ne ''){
291 $query.=" and aqorders.booksellerid='$supplier'";
293 $query.=" group by aqorders.ordernumber";
294 my $sth=$dbh->prepare($query);
299 while (my $data=$sth->fetchrow_hashref){
310 my $query="Select max(basketno) from aqorders";
311 my $sth=$dbh->prepare($query);
313 my $data=$sth->fetchrow_arrayref;
314 my $basket=$$data[0];
323 my $query="Select * from aqbookfund,aqbudget where aqbookfund.bookfundid
325 group by aqbookfund.bookfundid order by bookfundname";
326 my $sth=$dbh->prepare($query);
330 while (my $data=$sth->fetchrow_hashref){
341 my $query = "Select * from branches";
342 my $sth = $dbh->prepare($query);
347 while (my $data = $sth->fetchrow_hashref) {
348 $results[$i] = $data;
354 return($i, @results);
357 sub bookfundbreakdown {
360 my $query="Select quantity,datereceived,freight,unitprice,listprice,ecost,quantityreceived,subscription
361 from aqorders,aqorderbreakdown where bookfundid='$id' and
362 aqorders.ordernumber=aqorderbreakdown.ordernumber
363 and (datecancellationprinted is NULL or
364 datecancellationprinted='0000-00-00')";
365 my $sth=$dbh->prepare($query);
369 while (my $data=$sth->fetchrow_hashref){
370 if ($data->{'subscription'} == 1){
371 $spent+=$data->{'quantity'}*$data->{'unitprice'};
373 my $leftover=$data->{'quantity'}-$data->{'quantityreceived'};
374 $comtd+=($data->{'ecost'})*$leftover;
375 $spent+=($data->{'unitprice'})*$data->{'quantityreceived'};
380 return($spent,$comtd);
386 my $dbh = &C4Connect;
387 my $query = "Select max(biblionumber) from biblio";
388 my $sth = $dbh->prepare($query);
390 my $data = $sth->fetchrow_arrayref;
391 my $bibnum = $$data[0] + 1;
394 $biblio->{'title'} = $dbh->quote($biblio->{'title'});
395 $biblio->{'author'} = $dbh->quote($biblio->{'author'});
396 $biblio->{'copyright'} = $dbh->quote($biblio->{'copyright'});
397 $biblio->{'seriestitle'} = $dbh->quote($biblio->{'seriestitle'});
398 $biblio->{'notes'} = $dbh->quote($biblio->{'notes'});
399 $biblio->{'abstract'} = $dbh->quote($biblio->{'abstract'});
400 if ($biblio->{'seriestitle'}) { $series = 1 };
403 $query = "insert into biblio set
404 biblionumber = $bibnum,
405 title = $biblio->{'title'},
406 author = $biblio->{'author'},
407 copyrightdate = $biblio->{'copyright'},
409 seriestitle = $biblio->{'seriestitle'},
410 notes = $biblio->{'notes'},
411 abstract = $biblio->{'abstract'}";
413 $sth = $dbh->prepare($query);
428 $biblio->{'title'} = $dbh->quote($biblio->{'title'});
429 $biblio->{'author'} = $dbh->quote($biblio->{'author'});
430 $biblio->{'abstract'} = $dbh->quote($biblio->{'abstract'});
431 $biblio->{'copyrightdate'} = $dbh->quote($biblio->{'copyrightdate'});
432 $biblio->{'seriestitle'} = $dbh->quote($biblio->{'serirestitle'});
433 $biblio->{'serial'} = $dbh->quote($biblio->{'serial'});
434 $biblio->{'unititle'} = $dbh->quote($biblio->{'unititle'});
435 $biblio->{'notes'} = $dbh->quote($biblio->{'notes'});
437 $query = "Update biblio set
438 title = $biblio->{'title'},
439 author = $biblio->{'author'},
440 abstract = $biblio->{'abstract'},
441 copyrightdate = $biblio->{'copyrightdate'},
442 seriestitle = $biblio->{'seriestitle'},
443 serial = $biblio->{'serial'},
444 unititle = $biblio->{'unititle'},
445 notes = $biblio->{'notes'}
446 where biblionumber = $biblio->{'biblionumber'}";
447 $sth = $dbh->prepare($query);
453 return($biblio->{'biblionumber'});
458 my ($bibnum, $subtitle) = @_;
460 my $query = "update bibliosubtitle set
461 subtitle = '$subtitle'
462 where biblionumber = $bibnum";
463 my $sth = $dbh->prepare($query);
472 my ($bibnum, $author) = @_;
474 my $query = "Delete from additionalauthors where biblionumber = $bibnum";
475 my $sth = $dbh->prepare($query);
481 $query = "Insert into additionalauthors set
483 biblionumber = '$bibnum'";
484 $sth = $dbh->prepare($query);
496 my ($bibnum, $force, @subject) = @_;
498 my $count = @subject;
500 for (my $i = 0; $i < $count; $i++) {
501 $subject[$i] =~ s/^ //g;
502 $subject[$i] =~ s/ $//g;
503 my $query = "select * from catalogueentry
504 where entrytype = 's'
505 and catalogueentry = '$subject[$i]'";
506 my $sth = $dbh->prepare($query);
509 if (my $data = $sth->fetchrow_hashref) {
511 if ($force eq $subject[$i]) {
513 # subject not in aut, chosen to force anway
514 # so insert into cataloguentry so its in auth file
515 $query = "Insert into catalogueentry
516 (entrytype,catalogueentry)
517 values ('s','$subject[$i]')";
518 my $sth2 = $dbh->prepare($query);
525 $error = "$subject[$i]\n does not exist in the subject authority file";
526 $query = "Select * from catalogueentry
527 where entrytype = 's'
528 and (catalogueentry like '$subject[$i] %'
529 or catalogueentry like '% $subject[$i] %'
530 or catalogueentry like '% $subject[$i]')";
531 my $sth2 = $dbh->prepare($query);
534 while (my $data = $sth2->fetchrow_hashref) {
535 $error = $error."<br>$data->{'catalogueentry'}";
544 my $query = "Delete from bibliosubject where biblionumber = $bibnum";
545 my $sth = $dbh->prepare($query);
550 for (my $i = 0; $i < $count; $i++) {
551 $sth = $dbh->prepare("Insert into bibliosubject
552 values ('$subject[$i]', $bibnum)");
565 my ($biblioitem) = @_;
569 $biblioitem->{'itemtype'} = $dbh->quote($biblioitem->{'itemtype'});
570 $biblioitem->{'url'} = $dbh->quote($biblioitem->{'url'});
571 $biblioitem->{'isbn'} = $dbh->quote($biblioitem->{'isbn'});
572 $biblioitem->{'publishercode'} = $dbh->quote($biblioitem->{'publishercode'});
573 $biblioitem->{'publicationyear'} = $dbh->quote($biblioitem->{'publicationyear'});
574 $biblioitem->{'classification'} = $dbh->quote($biblioitem->{'classification'});
575 $biblioitem->{'dewey'} = $dbh->quote($biblioitem->{'dewey'});
576 $biblioitem->{'subclass'} = $dbh->quote($biblioitem->{'subclass'});
577 $biblioitem->{'illus'} = $dbh->quote($biblioitem->{'illus'});
578 $biblioitem->{'pages'} = $dbh->quote($biblioitem->{'pages'});
579 $biblioitem->{'volumeddesc'} = $dbh->quote($biblioitem->{'volumeddesc'});
580 $biblioitem->{'notes'} = $dbh->quote($biblioitem->{'notes'});
581 $biblioitem->{'size'} = $dbh->quote($biblioitem->{'size'});
582 $biblioitem->{'place'} = $dbh->quote($biblioitem->{'place'});
584 $query = "Update biblioitems set
585 itemtype = $biblioitem->{'itemtype'},
586 url = $biblioitem->{'url'},
587 isbn = $biblioitem->{'isbn'},
588 publishercode = $biblioitem->{'publishercode'},
589 publicationyear = $biblioitem->{'publicationyear'},
590 classification = $biblioitem->{'classification'},
591 dewey = $biblioitem->{'dewey'},
592 subclass = $biblioitem->{'subclass'},
593 illus = $biblioitem->{'illus'},
594 pages = $biblioitem->{'pages'},
595 volumeddesc = $biblioitem->{'volumeddesc'},
596 notes = $biblioitem->{'notes'},
597 size = $biblioitem->{'size'},
598 place = $biblioitem->{'place'}
599 where biblioitemnumber = $biblioitem->{'biblioitemnumber'}";
608 my ($bibitemnum,$note)=@_;
610 my $query="update biblioitems set notes='$note' where
611 biblioitemnumber='$bibitemnum'";
612 my $sth=$dbh->prepare($query);
619 my ($biblioitem) = @_;
621 my $query = "Select max(biblioitemnumber) from biblioitems";
622 my $sth = $dbh->prepare($query);
626 $biblioitem->{'volume'} = $dbh->quote($biblioitem->{'volume'});
627 $biblioitem->{'number'} = $dbh->quote($biblioitem->{'number'});
628 $biblioitem->{'classification'} = $dbh->quote($biblioitem->{'classification'});
629 $biblioitem->{'itemtype'} = $dbh->quote($biblioitem->{'itemtype'});
630 $biblioitem->{'url'} = $dbh->quote($biblioitem->{'url'});
631 $biblioitem->{'isbn'} = $dbh->quote($biblioitem->{'isbn'});
632 $biblioitem->{'issn'} = $dbh->quote($biblioitem->{'issn'});
633 $biblioitem->{'dewey'} = $dbh->quote($biblioitem->{'dewey'});
634 $biblioitem->{'subclass'} = $dbh->quote($biblioitem->{'subclass'});
635 $biblioitem->{'publicationyear'} = $dbh->quote($biblioitem->{'publicationyear'});
636 $biblioitem->{'publishercode'} = $dbh->quote($biblioitem->{'publishercode'});
637 $biblioitem->{'volumedate'} = $dbh->quote($biblioitem->{'volumedate'});
638 $biblioitem->{'volumeddesc'} = $dbh->quote($biblioitem->{'volumeddesc'}); $biblioitem->{'illus'} = $dbh->quote($biblioitem->{'illus'});
639 $biblioitem->{'illus'} = $dbh->quote($biblioitem->{'illus'});
640 $biblioitem->{'pages'} = $dbh->quote($biblioitem->{'pages'});
641 $biblioitem->{'notes'} = $dbh->quote($biblioitem->{'notes'});
642 $biblioitem->{'size'} = $dbh->quote($biblioitem->{'size'});
643 $biblioitem->{'place'} = $dbh->quote($biblioitem->{'place'});
646 $data = $sth->fetchrow_arrayref;
647 $bibitemnum = $$data[0] + 1;
651 $query = "insert into biblioitems set
652 biblioitemnumber = $bibitemnum,
653 biblionumber = $biblioitem->{'biblionumber'},
654 volume = $biblioitem->{'volume'},
655 number = $biblioitem->{'number'},
656 classification = $biblioitem->{'classification'},
657 itemtype = $biblioitem->{'itemtype'},
658 url = $biblioitem->{'url'},
659 isbn = $biblioitem->{'isbn'},
660 issn = $biblioitem->{'issn'},
661 dewey = $biblioitem->{'dewey'},
662 subclass = $biblioitem->{'subclass'},
663 publicationyear = $biblioitem->{'publicationyear'},
664 publishercode = $biblioitem->{'publishercode'},
665 volumedate = $biblioitem->{'volumedate'},
666 volumeddesc = $biblioitem->{'volumeddesc'},
667 illus = $biblioitem->{'illus'},
668 pages = $biblioitem->{'pages'},
669 notes = $biblioitem->{'notes'},
670 size = $biblioitem->{'size'},
671 place = $biblioitem->{'place'}";
673 $sth = $dbh->prepare($query);
684 my $query="insert into bibliosubject (biblionumber) values
686 my $sth=$dbh->prepare($query);
694 my ($bibnum, $subtitle) = @_;
696 $subtitle = $dbh->quote($subtitle);
697 my $query = "insert into bibliosubtitle set
698 biblionumber = $bibnum,
699 subtitle = $subtitle";
700 my $sth = $dbh->prepare($query);
709 my ($bibnum,$title,$ordnum,$basket,$quantity,$listprice,$supplier,$who,$notes,$bookfund,$bibitemnum,$rrp,$ecost,$gst,$budget,$cost,$sub,$invoice)=@_;
710 if ($budget eq 'now'){
713 $budget="'2001-07-01'";
721 my $query="insert into aqorders (biblionumber,title,basketno,
722 quantity,listprice,booksellerid,entrydate,requisitionedby,authorisedby,notes,
723 biblioitemnumber,rrp,ecost,gst,unitprice,subscription,booksellerinvoicenumber)
726 ($bibnum,'$title',$basket,$quantity,$listprice,'$supplier',now(),
727 '$who','$who','$notes',$bibitemnum,'$rrp','$ecost','$gst','$cost',
729 my $sth=$dbh->prepare($query);
733 $query="select * from aqorders where
734 biblionumber=$bibnum and basketno=$basket and ordernumber >=$ordnum";
735 $sth=$dbh->prepare($query);
737 my $data=$sth->fetchrow_hashref;
739 $ordnum=$data->{'ordernumber'};
740 $query="insert into aqorderbreakdown (ordernumber,bookfundid) values
741 ($ordnum,'$bookfund')";
742 $sth=$dbh->prepare($query);
750 my ($bibnum,$ordnum)=@_;
752 my $query="update aqorders set datecancellationprinted=now()
753 where biblionumber='$bibnum' and
754 ordernumber='$ordnum'";
755 my $sth=$dbh->prepare($query);
759 my $count=itemcount($bibnum);
767 my ($title,$ordnum,$quantity,$listprice,$bibnum,$basketno,$supplier,$who,$notes,$bookfund,$bibitemnum,$rrp,$ecost,$gst,$budget,$cost,$invoice)=@_;
769 my $query="update aqorders set title='$title',
770 quantity='$quantity',listprice='$listprice',basketno='$basketno',
771 rrp='$rrp',ecost='$ecost',unitprice='$cost',
772 booksellerinvoicenumber='$invoice'
774 ordernumber=$ordnum and biblionumber=$bibnum";
775 my $sth=$dbh->prepare($query);
779 $query="update aqorderbreakdown set bookfundid=$bookfund where
780 ordernumber=$ordnum";
781 $sth=$dbh->prepare($query);
790 my $query="Select max(ordernumber) from aqorders";
791 my $sth=$dbh->prepare($query);
793 my $data=$sth->fetchrow_arrayref;
794 my $ordnum=$$data[0];
802 my ($biblio,$ordnum,$quantrec,$user,$cost,$invoiceno,$bibitemno,$freight,$bookfund,$rrp)=@_;
804 my $query="update aqorders set quantityreceived='$quantrec',
805 datereceived=now(),booksellerinvoicenumber='$invoiceno',
806 biblioitemnumber=$bibitemno,unitprice='$cost',freight='$freight',
808 where biblionumber=$biblio and ordernumber=$ordnum
811 my $sth=$dbh->prepare($query);
814 $query="update aqorderbreakdown set bookfundid=$bookfund where
815 ordernumber=$ordnum";
816 $sth=$dbh->prepare($query);
823 my($biblio,$ordnum,$user,$cost,$bookfund,$rrp)=@_;
825 my $query="update aqorders set
826 unitprice='$cost', rrp='$rrp'
827 where biblionumber=$biblio and ordernumber=$ordnum
830 my $sth=$dbh->prepare($query);
833 $query="update aqorderbreakdown set bookfundid=$bookfund where
834 ordernumber=$ordnum";
835 $sth=$dbh->prepare($query);
843 my ($currency,$price)=@_;
845 my $query="Select rate from currency where currency='$currency'";
846 my $sth=$dbh->prepare($query);
848 my $data=$sth->fetchrow_hashref;
851 my $cur=$data->{'rate'};
855 my $price=$price / $cur;
861 my $query="Select * from currency";
862 my $sth=$dbh->prepare($query);
866 while (my $data=$sth->fetchrow_hashref){
872 return($i,\@results);
878 my $query="Select * from currency where currency='$cur'";
879 my $sth=$dbh->prepare($query);
882 my $data=$sth->fetchrow_hashref;
888 sub updatecurrencies {
889 my ($currency,$rate)=@_;
891 my $query="update currency set rate=$rate where currency='$currency'";
892 my $sth=$dbh->prepare($query);
901 my $query="Update aqbooksellers set
902 name='$data->{'name'}',address1='$data->{'address1'}',address2='$data->{'address2'}',
903 address3='$data->{'address3'}',address4='$data->{'address4'}',postal='$data->{'postal'}',
904 phone='$data->{'phone'}',fax='$data->{'fax'}',url='$data->{'url'}',
905 contact='$data->{'contact'}',contpos='$data->{'contpos'}',
906 contphone='$data->{'contphone'}', contfax='$data->{'contfax'}', contaltphone=
907 '$data->{'contaltphone'}', contemail='$data->{'contemail'}', contnotes=
908 '$data->{'contnotes'}', active=$data->{'active'},
909 listprice='$data->{'listprice'}', invoiceprice='$data->{'invoiceprice'}',
910 gstreg=$data->{'gstreg'}, listincgst=$data->{'listincgst'},
911 invoiceincgst=$data->{'invoiceincgst'}, specialty='$data->{'specialty'}',
912 discount='$data->{'discount'}',invoicedisc='$data->{'invoicedisc'}',
913 nocalc='$data->{'nocalc'}'
914 where id='$data->{'id'}'";
915 my $sth=$dbh->prepare($query);
925 my $sth=$dbh->prepare("Select max(id) from aqbooksellers");
927 my $data2=$sth->fetchrow_hashref;
929 $data2->{'max(id)'}++;
930 $sth=$dbh->prepare("Insert into aqbooksellers (id) values ($data2->{'max(id)'})");
933 $data->{'id'}=$data2->{'max(id)'};
936 return($data->{'id'});
941 my ($item, @barcodes) = @_;
943 my $query = "Select max(itemnumber) from items";
944 my $sth = $dbh->prepare($query);
950 $data = $sth->fetchrow_hashref;
951 $itemnumber = $data->{'max(itemnumber)'} + 1;
954 $item->{'booksellerid'} = $dbh->quote($item->{'bookselletid'});
955 $item->{'homebranch'} = $dbh->quote($item->{'homebranch'});
956 $item->{'price'} = $dbh->quote($item->{'price'});
957 $item->{'replacementprice'} = $dbh->quote($item->{'replacementprice'});
958 $item->{'itemnotes'} = $dbh->quote($item->{'itemnotes'});
960 foreach my $barcode (@barcodes) {
961 $barcode = uc($barcode);
962 $query = "Insert into items set
963 itemnumber = $itemnumber,
964 biblionumber = $item->{'biblionumber'},
965 biblioitemnumber = $item->{'biblioitemnumber'},
967 booksellerid = $item->{'booksellerid'},
968 dateaccessioned = NOW(),
969 homebranch = $item->{'homebranch'},
970 holdingbranch = $item->{'homebranch'},
971 price = $item->{'price'},
972 replacementprice = $item->{'replacementprice'},
973 replacementpricedate = NOW(),
974 itemnotes = $item->{'itemnotes'}";
976 if ($item->{'loan'}) {
978 notforloan = $item->{'loan'}";
981 $sth = $dbh->prepare($query);
984 $error .= $sth->errstr;
995 my ($count,@barcodes)=@_;
998 for (my $i=0;$i<$count;$i++){
999 $barcodes[$i]=uc $barcodes[$i];
1000 my $query="Select * from items where barcode='$barcodes[$i]'";
1001 my $sth=$dbh->prepare($query);
1003 if (my $data=$sth->fetchrow_hashref){
1004 $error.=" Duplicate Barcode: $barcodes[$i]";
1013 my ($loan,$itemnum,$bibitemnum,$barcode,$notes,$homebranch,$lost,$wthdrawn,$replacement)=@_;
1015 my $query="update items set biblioitemnumber=$bibitemnum,
1016 barcode='$barcode',itemnotes='$notes'
1017 where itemnumber=$itemnum";
1018 if ($barcode eq ''){
1019 $query="update items set biblioitemnumber=$bibitemnum,notforloan=$loan where itemnumber=$itemnum";
1022 $query="update items set biblioitemnumber=$bibitemnum,
1023 barcode='$barcode',itemnotes='$notes',homebranch='$homebranch',
1024 itemlost='$lost',wthdrawn='$wthdrawn' where itemnumber=$itemnum";
1026 if ($replacement ne ''){
1027 $query=~ s/ where/,replacementprice='$replacement' where/;
1030 my $sth=$dbh->prepare($query);
1037 my($price,$rrp,$itemnum)=@_;
1039 my $query="update items set price='$price',replacementprice='$rrp'
1040 where itemnumber=$itemnum";
1041 my $sth=$dbh->prepare($query);
1047 my ($bibitemnum)=@_;
1049 my $query="Select count(*) from items where biblioitemnumber='$bibitemnum'";
1050 my $sth=$dbh->prepare($query);
1052 my $data=$sth->fetchrow_hashref;
1055 return($data->{'count(*)'});
1059 my ($biblionumber)=@_;
1061 my $query="Select * from biblioitems,items,itemtypes where
1062 biblioitems.biblionumber=$biblionumber
1063 and biblioitems.biblioitemnumber=items.biblioitemnumber and
1064 itemtypes.itemtype=biblioitems.itemtype
1065 order by items.biblioitemnumber";
1066 my $sth=$dbh->prepare($query);
1070 while (my $data=$sth->fetchrow_hashref){
1080 my ($bibitemnum,$itemtype)=@_;
1082 my $query="Select * from biblioitems where biblioitemnumber=$bibitemnum
1083 and itemtype='$itemtype'";
1084 my $sth=$dbh->prepare($query);
1087 if (my $data=$sth->fetchrow_hashref){
1098 my $query="select * from items where itemnumber=$itemnum";
1099 my $sth=$dbh->prepare($query);
1101 my @data=$sth->fetchrow_array;
1103 $query="Insert into deleteditems values (";
1104 foreach my $temp (@data){
1105 $query=$query."'$temp',";
1109 $sth=$dbh->prepare($query);
1112 $query = "Delete from items where itemnumber=$itemnum";
1113 $sth=$dbh->prepare($query);
1120 sub deletebiblioitem {
1121 my ($biblioitemnumber) = @_;
1122 my $dbh = C4Connect;
1123 my $query = "Select * from biblioitems
1124 where biblioitemnumber = $biblioitemnumber";
1125 my $sth = $dbh->prepare($query);
1130 if (@results = $sth->fetchrow_array) {
1132 $query = "Insert into deletedbiblioitems values (";
1133 foreach my $value (@results) {
1134 $value = $dbh->quote($value);
1135 $query .= "$value,";
1138 $query =~ s/\,$/\)/;
1141 $query = "Delete from biblioitems
1142 where biblioitemnumber = $biblioitemnumber";
1148 # Now delete all the items attached to the biblioitem
1150 $query = "Select * from items where biblioitemnumber = $biblioitemnumber";
1151 $sth = $dbh->prepare($query);
1155 while (@results = $sth->fetchrow_array) {
1157 $query = "Insert into deleteditems values (";
1158 foreach my $value (@results) {
1159 $value = $dbh->quote($value);
1160 $query .= "$value,";
1163 $query =~ s/\,$/\)/;
1169 $query = "Delete from items where biblioitemnumber = $biblioitemnumber";
1173 } # sub deletebiblioitem
1179 my $query="select * from biblio where biblionumber=$biblio";
1180 my $sth=$dbh->prepare($query);
1182 if (my @data=$sth->fetchrow_array){
1184 $query="Insert into deletedbiblio values (";
1185 foreach my $temp (@data){
1186 $temp=~ s/\'/\\\'/g;
1187 $query=$query."'$temp',";
1191 $sth=$dbh->prepare($query);
1194 $query = "Delete from biblio where biblionumber=$biblio";
1195 $sth=$dbh->prepare($query);
1205 my $dbh = C4Connect;
1206 my $query = "select * from itemtypes";
1207 my $sth = $dbh->prepare($query);
1208 # || die "Cannot prepare $query" . $dbh->errstr;
1213 # || die "Cannot execute $query\n" . $sth->errstr;
1214 while (my $data = $sth->fetchrow_hashref) {
1215 @results[$count] = $data;
1221 return($count, @results);
1222 } # sub getitemtypes
1226 my ($biblionumber) = @_;
1227 my $dbh = C4Connect;
1228 my $query = "Select * from biblio where biblionumber = $biblionumber";
1229 my $sth = $dbh->prepare($query);
1230 # || die "Cannot prepare $query\n" . $dbh->errstr;
1235 # || die "Cannot execute $query\n" . $sth->errstr;
1236 while (my $data = $sth->fetchrow_hashref) {
1237 $results[$count] = $data;
1243 return($count, @results);
1248 my ($biblioitemnum) = @_;
1249 my $dbh = C4Connect;
1250 my $query = "Select * from biblioitems where
1251 biblioitemnumber = $biblioitemnum";
1252 my $sth = $dbh->prepare($query);
1258 while (my $data = $sth->fetchrow_hashref) {
1259 $results[$count] = $data;
1265 return($count, @results);
1266 } # sub getbiblioitem
1269 sub getitemsbybiblioitem {
1270 my ($biblioitemnum) = @_;
1271 my $dbh = C4Connect;
1272 my $query = "Select * from items, biblio where
1273 biblio.biblionumber = items.biblionumber and biblioitemnumber
1275 my $sth = $dbh->prepare($query);
1276 # || die "Cannot prepare $query\n" . $dbh->errstr;
1281 # || die "Cannot execute $query\n" . $sth->errstr;
1282 while (my $data = $sth->fetchrow_hashref) {
1283 $results[$count] = $data;
1289 return($count, @results);
1290 } # sub getitemsbybiblioitem
1295 my $dbh = C4Connect;
1301 $isbn = $dbh->quote($isbn);
1302 $query = "Select * from biblioitems where isbn = $isbn";
1303 $sth = $dbh->prepare($query);
1306 while (my $data = $sth->fetchrow_hashref) {
1307 $results[$count] = $data;
1313 return($count, @results);
1318 my ($keywordlist) = @_;
1319 my $dbh = C4Connect;
1320 my $query = "Select * from biblio where";
1324 my @keywords = split(/ +/, $keywordlist);
1325 my $keyword = shift(@keywords);
1327 $keyword =~ s/%/\\%/g;
1328 $keyword =~ s/_/\\_/;
1329 $keyword = "%" . $keyword . "%";
1330 $keyword = $dbh->quote($keyword);
1331 $query .= " (author like $keyword) or
1332 (title like $keyword) or (unititle like $keyword) or
1333 (notes like $keyword) or (seriestitle like $keyword) or
1334 (abstract like $keyword)";
1336 foreach $keyword (@keywords) {
1337 $keyword =~ s/%/\\%/;
1338 $keyword =~ s/_/\\_/;
1339 $keyword = "%" . $keyword . "%";
1340 $keyword = $dbh->quote($keyword);
1341 $query .= " or (author like $keyword) or
1342 (title like $keyword) or (unititle like $keyword) or
1343 (notes like $keyword) or (seriestitle like $keyword) or
1344 (abstract like $keyword)";
1347 $sth = $dbh->prepare($query);
1350 while (my $data = $sth->fetchrow_hashref) {
1351 $results[$count] = $data;
1357 return($count, @results);
1358 } # sub keywordsearch
1362 my ($keywordlist) = @_;
1363 my $dbh = C4Connect;
1364 my $query = "Select distinct biblio.* from biblio, biblioitems where
1365 biblio.biblionumber = biblioitems.biblionumber and (";
1369 my @keywords = split(/ +/, $keywordlist);
1370 my $keyword = shift(@keywords);
1372 $keyword =~ s/%/\\%/g;
1373 $keyword =~ s/_/\\_/;
1374 $keyword = "%" . $keyword . "%";
1375 $keyword = $dbh->quote($keyword);
1376 $query .= " (url like $keyword)";
1378 foreach $keyword (@keywords) {
1379 $keyword =~ s/%/\\%/;
1380 $keyword =~ s/_/\\_/;
1381 $keyword = "%" . $keyword . "%";
1382 $keyword = $dbh->quote($keyword);
1383 $query .= " or (url like $keyword)";
1387 $sth = $dbh->prepare($query);
1390 while (my $data = $sth->fetchrow_hashref) {
1391 $results[$count] = $data;
1397 return($count, @results);
1398 } # sub websitesearch
1403 my $dbh = C4Connect;
1406 $website->{'biblionumber'} = $dbh->quote($website->{'biblionumber'});
1407 $website->{'title'} = $dbh->quote($website->{'title'});
1408 $website->{'description'} = $dbh->quote($website->{'description'});
1409 $website->{'url'} = $dbh->quote($website->{'url'});
1411 $query = "Insert into websites set
1412 biblionumber = $website->{'biblionumber'},
1413 title = $website->{'title'},
1414 description = $website->{'description'},
1415 url = $website->{'url'}";
1425 my $dbh = C4Connect;
1428 $website->{'title'} = $dbh->quote($website->{'title'});
1429 $website->{'description'} = $dbh->quote($website->{'description'});
1430 $website->{'url'} = $dbh->quote($website->{'url'});
1432 $query = "Update websites set
1433 title = $website->{'title'},
1434 description = $website->{'description'},
1435 url = $website->{'url'}
1436 where websitenumber = $website->{'websitenumber'}";
1441 } # sub updatewebsite
1445 my ($websitenumber) = @_;
1446 my $dbh = C4Connect;
1447 my $query = "Delete from websites where websitenumber = $websitenumber";
1452 } # sub deletewebsite
1455 END { } # module clean-up code here (global destructor)