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 biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber=
153 aqorders.biblioitemnumber
154 group by aqorders.biblioitemnumber
159 my $sth=$dbh->prepare($query);
161 while (my $data=$sth->fetchrow_hashref){
171 #gets all orders from a certain supplier, orders them alphabetically
174 my $query="Select * from aqorders,biblio,biblioitems where booksellerid='$supid'
175 and (cancelledby is NULL or cancelledby = '')
176 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber=
177 aqorders.biblioitemnumber and
178 aqorders.quantityreceived>0
179 and aqorders.datereceived >=now()
180 group by aqorders.biblioitemnumber
185 my $sth=$dbh->prepare($query);
187 while (my $data=$sth->fetchrow_hashref){
197 my ($search,$biblio,$catview) = @_;
199 my $query = "Select *,biblio.title from aqorders,biblioitems,biblio
200 where aqorders.biblioitemnumber = biblioitems.biblioitemnumber
201 and biblio.biblionumber=aqorders.biblionumber
202 and ((datecancellationprinted is NULL)
203 or (datecancellationprinted = '0000-00-00')
205 my @data = split(' ',$search);
207 for (my $i = 0; $i < $count; $i++) {
208 $query .= "(biblio.title like '$data[$i]%' or biblio.title like '% $data[$i]%') and ";
211 $query.=" ) or biblioitems.isbn='$search'
212 or (aqorders.ordernumber='$search' and aqorders.biblionumber='$biblio')) ";
213 if ($catview ne 'yes'){
214 $query.=" and (quantityreceived < quantity or quantityreceived is NULL)";
216 $query.=" group by aqorders.ordernumber";
217 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);
625 $biblioitem->{'volume'} = $dbh->quote($biblioitem->{'volume'});
626 $biblioitem->{'number'} = $dbh->quote($biblioitem->{'number'});
627 $biblioitem->{'classification'} = $dbh->quote($biblioitem->{'classification'});
628 $biblioitem->{'itemtype'} = $dbh->quote($biblioitem->{'itemtype'});
629 $biblioitem->{'url'} = $dbh->quote($biblioitem->{'url'});
630 $biblioitem->{'isbn'} = $dbh->quote($biblioitem->{'isbn'});
631 $biblioitem->{'issn'} = $dbh->quote($biblioitem->{'issn'});
632 $biblioitem->{'dewey'} = $dbh->quote($biblioitem->{'dewey'});
633 $biblioitem->{'subclass'} = $dbh->quote($biblioitem->{'subclass'});
634 $biblioitem->{'publicationyear'} = $dbh->quote($biblioitem->{'publicationyear'});
635 $biblioitem->{'publishercode'} = $dbh->quote($biblioitem->{'publishercode'});
636 $biblioitem->{'volumedate'} = $dbh->quote($biblioitem->{'volumedate'});
637 $biblioitem->{'volumeddesc'} = $dbh->quote($biblioitem->{'volumeddesc'}); $biblioitem->{'illus'} = $dbh->quote($biblioitem->{'illus'});
638 $biblioitem->{'illus'} = $dbh->quote($biblioitem->{'illus'});
639 $biblioitem->{'pages'} = $dbh->quote($biblioitem->{'pages'});
640 $biblioitem->{'notes'} = $dbh->quote($biblioitem->{'notes'});
641 $biblioitem->{'size'} = $dbh->quote($biblioitem->{'size'});
642 $biblioitem->{'place'} = $dbh->quote($biblioitem->{'place'});
645 $data = $sth->fetchrow_arrayref;
646 $bibitemnum = $$data[0] + 1;
650 $query = "insert into biblioitems set
651 biblioitemnumber = $bibitemnum,
652 biblionumber = $biblioitem->{'biblionumber'},
653 volume = $biblioitem->{'volume'},
654 number = $biblioitem->{'number'},
655 classification = $biblioitem->{'classification'},
656 itemtype = $biblioitem->{'itemtype'},
657 url = $biblioitem->{'url'},
658 isbn = $biblioitem->{'isbn'},
659 issn = $biblioitem->{'issn'},
660 dewey = $biblioitem->{'dewey'},
661 subclass = $biblioitem->{'subclass'},
662 publicationyear = $biblioitem->{'publicationyear'},
663 publishercode = $biblioitem->{'publishercode'},
664 volumedate = $biblioitem->{'volumedate'},
665 volumeddesc = $biblioitem->{'volumeddesc'},
666 illus = $biblioitem->{'illus'},
667 pages = $biblioitem->{'pages'},
668 notes = $biblioitem->{'notes'},
669 size = $biblioitem->{'size'},
670 place = $biblioitem->{'place'}";
672 $sth = $dbh->prepare($query);
683 my $query="insert into bibliosubject (biblionumber) values
685 my $sth=$dbh->prepare($query);
693 my ($bibnum, $subtitle) = @_;
695 $subtitle = $dbh->quote($subtitle);
696 my $query = "insert into bibliosubtitle set
697 biblionumber = $bibnum,
698 subtitle = $subtitle";
699 my $sth = $dbh->prepare($query);
708 my ($bibnum,$title,$ordnum,$basket,$quantity,$listprice,$supplier,$who,$notes,$bookfund,$bibitemnum,$rrp,$ecost,$gst,$budget,$cost,$sub,$invoice)=@_;
709 if ($budget eq 'now'){
712 $budget="'2001-07-01'";
720 my $query="insert into aqorders (biblionumber,title,basketno,
721 quantity,listprice,booksellerid,entrydate,requisitionedby,authorisedby,notes,
722 biblioitemnumber,rrp,ecost,gst,unitprice,subscription,booksellerinvoicenumber)
725 ($bibnum,'$title',$basket,$quantity,$listprice,'$supplier',now(),
726 '$who','$who','$notes',$bibitemnum,'$rrp','$ecost','$gst','$cost',
728 my $sth=$dbh->prepare($query);
732 $query="select * from aqorders where
733 biblionumber=$bibnum and basketno=$basket and ordernumber >=$ordnum";
734 $sth=$dbh->prepare($query);
736 my $data=$sth->fetchrow_hashref;
738 $ordnum=$data->{'ordernumber'};
739 $query="insert into aqorderbreakdown (ordernumber,bookfundid) values
740 ($ordnum,'$bookfund')";
741 $sth=$dbh->prepare($query);
749 my ($bibnum,$ordnum)=@_;
751 my $query="update aqorders set datecancellationprinted=now()
752 where biblionumber='$bibnum' and
753 ordernumber='$ordnum'";
754 my $sth=$dbh->prepare($query);
758 my $count=itemcount($bibnum);
766 my ($title,$ordnum,$quantity,$listprice,$bibnum,$basketno,$supplier,$who,$notes,$bookfund,$bibitemnum,$rrp,$ecost,$gst,$budget,$cost,$invoice)=@_;
768 my $query="update aqorders set title='$title',
769 quantity='$quantity',listprice='$listprice',basketno='$basketno',
770 rrp='$rrp',ecost='$ecost',unitprice='$cost',
771 booksellerinvoicenumber='$invoice'
773 ordernumber=$ordnum and biblionumber=$bibnum";
774 my $sth=$dbh->prepare($query);
778 $query="update aqorderbreakdown set bookfundid=$bookfund where
779 ordernumber=$ordnum";
780 $sth=$dbh->prepare($query);
789 my $query="Select max(ordernumber) from aqorders";
790 my $sth=$dbh->prepare($query);
792 my $data=$sth->fetchrow_arrayref;
793 my $ordnum=$$data[0];
801 my ($biblio,$ordnum,$quantrec,$user,$cost,$invoiceno,$bibitemno,$freight,$bookfund,$rrp)=@_;
803 my $query="update aqorders set quantityreceived='$quantrec',
804 datereceived=now(),booksellerinvoicenumber='$invoiceno',
805 biblioitemnumber=$bibitemno,unitprice='$cost',freight='$freight',
807 where biblionumber=$biblio and ordernumber=$ordnum
810 my $sth=$dbh->prepare($query);
813 $query="update aqorderbreakdown set bookfundid=$bookfund where
814 ordernumber=$ordnum";
815 $sth=$dbh->prepare($query);
822 my($biblio,$ordnum,$user,$cost,$bookfund,$rrp)=@_;
824 my $query="update aqorders set
825 unitprice='$cost', rrp='$rrp'
826 where biblionumber=$biblio and ordernumber=$ordnum
829 my $sth=$dbh->prepare($query);
832 $query="update aqorderbreakdown set bookfundid=$bookfund where
833 ordernumber=$ordnum";
834 $sth=$dbh->prepare($query);
842 my ($currency,$price)=@_;
844 my $query="Select rate from currency where currency='$currency'";
845 my $sth=$dbh->prepare($query);
847 my $data=$sth->fetchrow_hashref;
850 my $cur=$data->{'rate'};
854 my $price=$price / $cur;
860 my $query="Select * from currency";
861 my $sth=$dbh->prepare($query);
865 while (my $data=$sth->fetchrow_hashref){
871 return($i,\@results);
877 my $query="Select * from currency where currency='$cur'";
878 my $sth=$dbh->prepare($query);
881 my $data=$sth->fetchrow_hashref;
887 sub updatecurrencies {
888 my ($currency,$rate)=@_;
890 my $query="update currency set rate=$rate where currency='$currency'";
891 my $sth=$dbh->prepare($query);
900 my $query="Update aqbooksellers set
901 name='$data->{'name'}',address1='$data->{'address1'}',address2='$data->{'address2'}',
902 address3='$data->{'address3'}',address4='$data->{'address4'}',postal='$data->{'postal'}',
903 phone='$data->{'phone'}',fax='$data->{'fax'}',url='$data->{'url'}',
904 contact='$data->{'contact'}',contpos='$data->{'contpos'}',
905 contphone='$data->{'contphone'}', contfax='$data->{'contfax'}', contaltphone=
906 '$data->{'contaltphone'}', contemail='$data->{'contemail'}', contnotes=
907 '$data->{'contnotes'}', active=$data->{'active'},
908 listprice='$data->{'listprice'}', invoiceprice='$data->{'invoiceprice'}',
909 gstreg=$data->{'gstreg'}, listincgst=$data->{'listincgst'},
910 invoiceincgst=$data->{'invoiceincgst'}, specialty='$data->{'specialty'}',
911 discount='$data->{'discount'}',invoicedisc='$data->{'invoicedisc'}',
912 nocalc='$data->{'nocalc'}'
913 where id='$data->{'id'}'";
914 my $sth=$dbh->prepare($query);
924 my $sth=$dbh->prepare("Select max(id) from aqbooksellers");
926 my $data2=$sth->fetchrow_hashref;
928 $data2->{'max(id)'}++;
929 $sth=$dbh->prepare("Insert into aqbooksellers (id) values ($data2->{'max(id)'})");
932 $data->{'id'}=$data2->{'max(id)'};
935 return($data->{'id'});
940 my ($item, @barcodes) = @_;
942 my $query = "Select max(itemnumber) from items";
943 my $sth = $dbh->prepare($query);
949 $data = $sth->fetchrow_hashref;
950 $itemnumber = $data->{'max(itemnumber)'} + 1;
953 $item->{'booksellerid'} = $dbh->quote($item->{'bookselletid'});
954 $item->{'homebranch'} = $dbh->quote($item->{'homebranch'});
955 $item->{'price'} = $dbh->quote($item->{'price'});
956 $item->{'replacementprice'} = $dbh->quote($item->{'replacementprice'});
957 $item->{'itemnotes'} = $dbh->quote($item->{'itemnotes'});
959 foreach my $barcode (@barcodes) {
960 $barcode = uc($barcode);
961 $query = "Insert into items set
962 itemnumber = $itemnumber,
963 biblionumber = $item->{'biblionumber'},
964 biblioitemnumber = $item->{'biblioitemnumber'},
966 booksellerid = $item->{'booksellerid'},
967 dateaccessioned = NOW(),
968 homebranch = $item->{'homebranch'},
969 holdingbranch = $item->{'homebranch'},
970 price = $item->{'price'},
971 replacementprice = $item->{'replacementprice'},
972 replacementpricedate = NOW(),
973 itemnotes = $item->{'itemnotes'}";
975 if ($item->{'loan'}) {
977 notforloan = $item->{'loan'}";
980 $sth = $dbh->prepare($query);
983 $error .= $sth->errstr;
994 my ($count,@barcodes)=@_;
997 for (my $i=0;$i<$count;$i++){
998 $barcodes[$i]=uc $barcodes[$i];
999 my $query="Select * from items where barcode='$barcodes[$i]'";
1000 my $sth=$dbh->prepare($query);
1002 if (my $data=$sth->fetchrow_hashref){
1003 $error.=" Duplicate Barcode: $barcodes[$i]";
1012 my ($loan,$itemnum,$bibitemnum,$barcode,$notes,$homebranch,$lost,$wthdrawn,$replacement)=@_;
1014 my $query="update items set biblioitemnumber=$bibitemnum,
1015 barcode='$barcode',itemnotes='$notes'
1016 where itemnumber=$itemnum";
1017 if ($barcode eq ''){
1018 $query="update items set biblioitemnumber=$bibitemnum,notforloan=$loan where itemnumber=$itemnum";
1021 $query="update items set biblioitemnumber=$bibitemnum,
1022 barcode='$barcode',itemnotes='$notes',homebranch='$homebranch',
1023 itemlost='$lost',wthdrawn='$wthdrawn' where itemnumber=$itemnum";
1025 if ($replacement ne ''){
1026 $query=~ s/ where/,replacementprice='$replacement' where/;
1029 my $sth=$dbh->prepare($query);
1036 my($price,$rrp,$itemnum)=@_;
1038 my $query="update items set price='$price',replacementprice='$rrp'
1039 where itemnumber=$itemnum";
1040 my $sth=$dbh->prepare($query);
1046 my ($bibitemnum)=@_;
1048 my $query="Select count(*) from items where biblioitemnumber='$bibitemnum'";
1049 my $sth=$dbh->prepare($query);
1051 my $data=$sth->fetchrow_hashref;
1054 return($data->{'count(*)'});
1058 my ($biblionumber)=@_;
1060 my $query="Select * from biblioitems,items,itemtypes where
1061 biblioitems.biblionumber=$biblionumber
1062 and biblioitems.biblioitemnumber=items.biblioitemnumber and
1063 itemtypes.itemtype=biblioitems.itemtype
1064 order by items.biblioitemnumber";
1065 my $sth=$dbh->prepare($query);
1069 while (my $data=$sth->fetchrow_hashref){
1079 my ($bibitemnum,$itemtype)=@_;
1081 my $query="Select * from biblioitems where biblioitemnumber=$bibitemnum
1082 and itemtype='$itemtype'";
1083 my $sth=$dbh->prepare($query);
1086 if (my $data=$sth->fetchrow_hashref){
1097 my $query="select * from items where itemnumber=$itemnum";
1098 my $sth=$dbh->prepare($query);
1100 my @data=$sth->fetchrow_array;
1102 $query="Insert into deleteditems values (";
1103 foreach my $temp (@data){
1104 $query=$query."'$temp',";
1108 $sth=$dbh->prepare($query);
1111 $query = "Delete from items where itemnumber=$itemnum";
1112 $sth=$dbh->prepare($query);
1119 sub deletebiblioitem {
1120 my ($biblioitemnumber) = @_;
1121 my $dbh = C4Connect;
1122 my $query = "Select * from biblioitems
1123 where biblioitemnumber = $biblioitemnumber";
1124 my $sth = $dbh->prepare($query);
1129 if (@results = $sth->fetchrow_array) {
1131 $query = "Insert into deletedbiblioitems values (";
1132 foreach my $value (@results) {
1133 $value = $dbh->quote($value);
1134 $query .= "$value,";
1137 $query =~ s/\,$/\)/;
1140 $query = "Delete from biblioitems
1141 where biblioitemnumber = $biblioitemnumber";
1147 # Now delete all the items attached to the biblioitem
1149 $query = "Select * from items where biblioitemnumber = $biblioitemnumber";
1150 $sth = $dbh->prepare($query);
1154 while (@results = $sth->fetchrow_array) {
1156 $query = "Insert into deleteditems values (";
1157 foreach my $value (@results) {
1158 $value = $dbh->quote($value);
1159 $query .= "$value,";
1162 $query =~ s/\,$/\)/;
1168 $query = "Delete from items where biblioitemnumber = $biblioitemnumber";
1172 } # sub deletebiblioitem
1178 my $query="select * from biblio where biblionumber=$biblio";
1179 my $sth=$dbh->prepare($query);
1181 if (my @data=$sth->fetchrow_array){
1183 $query="Insert into deletedbiblio values (";
1184 foreach my $temp (@data){
1185 $temp=~ s/\'/\\\'/g;
1186 $query=$query."'$temp',";
1190 $sth=$dbh->prepare($query);
1193 $query = "Delete from biblio where biblionumber=$biblio";
1194 $sth=$dbh->prepare($query);
1204 my $dbh = C4Connect;
1205 my $query = "select * from itemtypes";
1206 my $sth = $dbh->prepare($query);
1207 # || die "Cannot prepare $query" . $dbh->errstr;
1212 # || die "Cannot execute $query\n" . $sth->errstr;
1213 while (my $data = $sth->fetchrow_hashref) {
1214 @results[$count] = $data;
1220 return($count, @results);
1221 } # sub getitemtypes
1225 my ($biblionumber) = @_;
1226 my $dbh = C4Connect;
1227 my $query = "Select * from biblio where biblionumber = $biblionumber";
1228 my $sth = $dbh->prepare($query);
1229 # || die "Cannot prepare $query\n" . $dbh->errstr;
1234 # || die "Cannot execute $query\n" . $sth->errstr;
1235 while (my $data = $sth->fetchrow_hashref) {
1236 $results[$count] = $data;
1242 return($count, @results);
1247 my ($biblioitemnum) = @_;
1248 my $dbh = C4Connect;
1249 my $query = "Select * from biblioitems where
1250 biblioitemnumber = $biblioitemnum";
1251 my $sth = $dbh->prepare($query);
1257 while (my $data = $sth->fetchrow_hashref) {
1258 $results[$count] = $data;
1264 return($count, @results);
1265 } # sub getbiblioitem
1268 sub getitemsbybiblioitem {
1269 my ($biblioitemnum) = @_;
1270 my $dbh = C4Connect;
1271 my $query = "Select * from items, biblio where
1272 biblio.biblionumber = items.biblionumber and biblioitemnumber
1274 my $sth = $dbh->prepare($query);
1275 # || die "Cannot prepare $query\n" . $dbh->errstr;
1280 # || die "Cannot execute $query\n" . $sth->errstr;
1281 while (my $data = $sth->fetchrow_hashref) {
1282 $results[$count] = $data;
1288 return($count, @results);
1289 } # sub getitemsbybiblioitem
1294 my $dbh = C4Connect;
1300 $isbn = $dbh->quote($isbn);
1301 $query = "Select * from biblioitems where isbn = $isbn";
1302 $sth = $dbh->prepare($query);
1305 while (my $data = $sth->fetchrow_hashref) {
1306 $results[$count] = $data;
1312 return($count, @results);
1317 my ($keywordlist) = @_;
1318 my $dbh = C4Connect;
1319 my $query = "Select * from biblio where";
1323 my @keywords = split(/ +/, $keywordlist);
1324 my $keyword = shift(@keywords);
1326 $keyword =~ s/%/\\%/g;
1327 $keyword =~ s/_/\\_/;
1328 $keyword = "%" . $keyword . "%";
1329 $keyword = $dbh->quote($keyword);
1330 $query .= " (author like $keyword) or
1331 (title like $keyword) or (unititle like $keyword) or
1332 (notes like $keyword) or (seriestitle like $keyword) or
1333 (abstract like $keyword)";
1335 foreach $keyword (@keywords) {
1336 $keyword =~ s/%/\\%/;
1337 $keyword =~ s/_/\\_/;
1338 $keyword = "%" . $keyword . "%";
1339 $keyword = $dbh->quote($keyword);
1340 $query .= " or (author like $keyword) or
1341 (title like $keyword) or (unititle like $keyword) or
1342 (notes like $keyword) or (seriestitle like $keyword) or
1343 (abstract like $keyword)";
1346 $sth = $dbh->prepare($query);
1349 while (my $data = $sth->fetchrow_hashref) {
1350 $results[$count] = $data;
1356 return($count, @results);
1357 } # sub keywordsearch
1361 my ($keywordlist) = @_;
1362 my $dbh = C4Connect;
1363 my $query = "Select distinct biblio.* from biblio, biblioitems where
1364 biblio.biblionumber = biblioitems.biblionumber and (";
1368 my @keywords = split(/ +/, $keywordlist);
1369 my $keyword = shift(@keywords);
1371 $keyword =~ s/%/\\%/g;
1372 $keyword =~ s/_/\\_/;
1373 $keyword = "%" . $keyword . "%";
1374 $keyword = $dbh->quote($keyword);
1375 $query .= " (url like $keyword)";
1377 foreach $keyword (@keywords) {
1378 $keyword =~ s/%/\\%/;
1379 $keyword =~ s/_/\\_/;
1380 $keyword = "%" . $keyword . "%";
1381 $keyword = $dbh->quote($keyword);
1382 $query .= " or (url like $keyword)";
1386 $sth = $dbh->prepare($query);
1389 while (my $data = $sth->fetchrow_hashref) {
1390 $results[$count] = $data;
1396 return($count, @results);
1397 } # sub websitesearch
1402 my $dbh = C4Connect;
1405 $website->{'biblionumber'} = $dbh->quote($website->{'biblionumber'});
1406 $website->{'title'} = $dbh->quote($website->{'title'});
1407 $website->{'description'} = $dbh->quote($website->{'description'});
1408 $website->{'url'} = $dbh->quote($website->{'url'});
1410 $query = "Insert into websites set
1411 biblionumber = $website->{'biblionumber'},
1412 title = $website->{'title'},
1413 description = $website->{'description'},
1414 url = $website->{'url'}";
1424 my $dbh = C4Connect;
1427 $website->{'title'} = $dbh->quote($website->{'title'});
1428 $website->{'description'} = $dbh->quote($website->{'description'});
1429 $website->{'url'} = $dbh->quote($website->{'url'});
1431 $query = "Update websites set
1432 title = $website->{'title'},
1433 description = $website->{'description'},
1434 url = $website->{'url'}
1435 where websitenumber = $website->{'websitenumber'}";
1440 } # sub updatewebsite
1444 my ($websitenumber) = @_;
1445 my $dbh = C4Connect;
1446 my $query = "Delete from websites where websitenumber = $websitenumber";
1451 } # sub deletewebsite
1454 END { } # module clean-up code here (global destructor)