1 package C4::Acquisitions; #assumes C4/Acquisitions.pm
7 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
9 # set the version for version checking
13 @EXPORT = qw(&getorders &bookseller &breakdown &basket &newbasket &bookfunds
14 &ordersearch &newbiblio &newbiblioitem &newsubject &newsubtitle &neworder
15 &newordernum &modbiblio &modorder &getsingleorder &invoice &receiveorder
16 &bookfundbreakdown &curconvert &updatesup &insertsup &newitems &modbibitem
17 &getcurrencies &modsubtitle &modsubject &modaddauthor &moditem &countitems
18 &findall &needsmod &delitem &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
101 where biblionumber=? and biblioitemnumber=?";
102 my $sth=$dbh->prepare($query);
103 $sth->execute($bib,$bi);
104 my $ordnum=$sth->fetchrow_hashref;
106 my $order=getsingleorder($ordnum->{'ordernumber'});
109 return ($order,$ordnum->{'ordernumber'});
115 my $query="Select * from biblio,biblioitems,aqorders,aqorderbreakdown
116 where aqorders.ordernumber=?
117 and biblio.biblionumber=aqorders.biblionumber and
118 biblioitems.biblioitemnumber=aqorders.biblioitemnumber and
119 aqorders.ordernumber=aqorderbreakdown.ordernumber";
120 my $sth=$dbh->prepare($query);
121 $sth->execute($ordnum);
122 my $data=$sth->fetchrow_hashref;
131 my $query="Select * from aqorders,biblio,biblioitems where
132 booksellerinvoicenumber='$invoice'
133 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber=
134 aqorders.biblioitemnumber group by aqorders.ordernumber,aqorders.biblioitemnumber";
137 my $sth=$dbh->prepare($query);
139 while (my $data=$sth->fetchrow_hashref){
149 #gets all orders from a certain supplier, orders them alphabetically
152 my $query="Select * from aqorders,biblio,biblioitems where booksellerid='$supid'
153 and (cancelledby is NULL or cancelledby = '')
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);
224 while (my $data=$sth->fetchrow_hashref){
225 my $sth2=$dbh->prepare("Select * from biblio where
226 biblionumber='$data->{'biblionumber'}'");
228 my $data2=$sth2->fetchrow_hashref;
230 $data->{'author'}=$data2->{'author'};
231 $data->{'seriestitle'}=$data2->{'seriestitle'};
232 $sth2=$dbh->prepare("Select * from aqorderbreakdown where
233 ordernumber=$data->{'ordernumber'}");
235 $data2=$sth2->fetchrow_hashref;
237 $data->{'branchcode'}=$data2->{'branchcode'};
238 $data->{'bookfundid'}=$data2->{'bookfundid'};
249 my ($searchstring)=@_;
251 my $query="Select * from aqbooksellers where name like '%$searchstring%' or
252 id = '$searchstring'";
253 my $sth=$dbh->prepare($query);
257 while (my $data=$sth->fetchrow_hashref){
269 my $query="Select * from aqorderbreakdown where ordernumber='$id'";
270 my $sth=$dbh->prepare($query);
274 while (my $data=$sth->fetchrow_hashref){
280 return($i,\@results);
284 my ($basketno,$supplier)=@_;
286 my $query="Select *,biblio.title from aqorders,biblio,biblioitems
287 where basketno='$basketno'
288 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber
289 =aqorders.biblioitemnumber
290 and (datecancellationprinted is NULL or datecancellationprinted =
292 if (defined $supplier && $supplier ne ''){
293 $query.=" and aqorders.booksellerid='$supplier'";
295 $query.=" group by aqorders.ordernumber";
296 my $sth=$dbh->prepare($query);
301 while (my $data=$sth->fetchrow_hashref){
312 my $query="Select max(basketno) from aqorders";
313 my $sth=$dbh->prepare($query);
315 my $data=$sth->fetchrow_arrayref;
316 my $basket=$$data[0];
325 my $query="Select * from aqbookfund,aqbudget where aqbookfund.bookfundid
327 and aqbudget.startdate='2001-07-01'
328 group by aqbookfund.bookfundid order by bookfundname";
329 my $sth=$dbh->prepare($query);
333 while (my $data=$sth->fetchrow_hashref){
344 my $query="Select * from branches";
345 my $sth=$dbh->prepare($query);
350 while (my $data = $sth->fetchrow_hashref){
357 return($i, @results);
360 sub bookfundbreakdown {
363 my $query="Select quantity,datereceived,freight,unitprice,listprice,ecost,quantityreceived,subscription
364 from aqorders,aqorderbreakdown where bookfundid='$id' and
365 aqorders.ordernumber=aqorderbreakdown.ordernumber and ((budgetdate >=
366 '2001-07-01' and budgetdate <'2002-07-01') or
367 (datereceived >= '2001-07-01' and datereceived < '2002-07-01'))
368 and (datecancellationprinted is NULL or
369 datecancellationprinted='0000-00-00')";
370 my $sth=$dbh->prepare($query);
374 while (my $data=$sth->fetchrow_hashref){
375 if ($data->{'subscription'} == 1){
376 $spent+=$data->{'quantity'}*$data->{'unitprice'};
378 my $leftover=$data->{'quantity'}-$data->{'quantityreceived'};
379 $comtd+=($data->{'ecost'})*$leftover;
380 $spent+=($data->{'unitprice'})*$data->{'quantityreceived'};
385 return($spent,$comtd);
391 my $dbh = &C4Connect;
392 my $query = "Select max(biblionumber) from biblio";
393 my $sth = $dbh->prepare($query);
395 my $data = $sth->fetchrow_arrayref;
396 my $bibnum = $$data[0] + 1;
399 $biblio->{'title'} = $dbh->quote($biblio->{'title'});
400 $biblio->{'author'} = $dbh->quote($biblio->{'author'});
401 $biblio->{'copyright'} = $dbh->quote($biblio->{'copyright'});
402 $biblio->{'seriestitle'} = $dbh->quote($biblio->{'seriestitle'});
403 $biblio->{'notes'} = $dbh->quote($biblio->{'notes'});
404 $biblio->{'abstract'} = $dbh->quote($biblio->{'abstract'});
405 if ($biblio->{'seriestitle'}) { $series = 1 };
408 $query = "insert into biblio set
409 biblionumber = $bibnum,
410 title = $biblio->{'title'},
411 author = $biblio->{'author'},
412 copyrightdate = $biblio->{'copyright'},
414 seriestitle = $biblio->{'seriestitle'},
415 notes = $biblio->{'notes'},
416 abstract = $biblio->{'abstract'}";
418 $sth = $dbh->prepare($query);
433 $biblio->{'title'} = $dbh->quote($biblio->{'title'});
434 $biblio->{'author'} = $dbh->quote($biblio->{'author'});
435 $biblio->{'abstract'} = $dbh->quote($biblio->{'abstract'});
436 $biblio->{'copyrightdate'} = $dbh->quote($biblio->{'copyrightdate'});
437 $biblio->{'seriestitle'} = $dbh->quote($biblio->{'serirestitle'});
438 $biblio->{'serial'} = $dbh->quote($biblio->{'serial'});
439 $biblio->{'unititle'} = $dbh->quote($biblio->{'unititle'});
440 $biblio->{'notes'} = $dbh->quote($biblio->{'notes'});
442 $query = "Update biblio set
443 title = $biblio->{'title'},
444 author = $biblio->{'author'},
445 abstract = $biblio->{'abstract'},
446 copyrightdate = $biblio->{'copyrightdate'},
447 seriestitle = $biblio->{'seriestitle'},
448 serial = $biblio->{'serial'},
449 unititle = $biblio->{'unititle'},
450 notes = $biblio->{'notes'}
451 where biblionumber = $biblio->{'biblionumber'}";
452 $sth = $dbh->prepare($query);
458 return($biblio->{'biblionumber'});
463 my ($bibnum, $subtitle) = @_;
465 my $query = "update bibliosubtitle set
466 subtitle = '$subtitle'
467 where biblionumber = $bibnum";
468 my $sth = $dbh->prepare($query);
477 my ($bibnum, $author) = @_;
479 my $query = "Delete from additionalauthors where biblionumber = $bibnum";
480 my $sth = $dbh->prepare($query);
486 $query = "Insert into additionalauthors set
488 biblionumber = '$bibnum'";
489 $sth = $dbh->prepare($query);
501 my ($bibnum, $force, @subject) = @_;
503 my $count = @subject;
505 for (my $i = 0; $i < $count; $i++) {
506 $subject[$i] =~ s/^ //g;
507 $subject[$i] =~ s/ $//g;
508 my $query = "select * from catalogueentry
509 where entrytype = 's'
510 and catalogueentry = '$subject[$i]'";
511 my $sth = $dbh->prepare($query);
514 if (my $data = $sth->fetchrow_hashref) {
516 if ($force eq $subject[$i]) {
518 # subject not in aut, chosen to force anway
519 # so insert into cataloguentry so its in auth file
520 $query = "Insert into catalogueentry
521 (entrytype,catalogueentry)
522 values ('s','$subject[$i]')";
523 my $sth2 = $dbh->prepare($query);
530 $error = "$subject[$i]\n does not exist in the subject authority file";
531 $query = "Select * from catalogueentry
532 where entrytype = 's'
533 and (catalogueentry like '$subject[$i] %'
534 or catalogueentry like '% $subject[$i] %'
535 or catalogueentry like '% $subject[$i]')";
536 my $sth2 = $dbh->prepare($query);
539 while (my $data = $sth2->fetchrow_hashref) {
540 $error = $error."<br>$data->{'catalogueentry'}";
549 my $query = "Delete from bibliosubject where biblionumber = $bibnum";
550 my $sth = $dbh->prepare($query);
555 for (my $i = 0; $i < $count; $i++) {
556 $sth = $dbh->prepare("Insert into bibliosubject
557 values ('$subject[$i]', $bibnum)");
570 my ($biblioitem) = @_;
574 $biblioitem->{'itemtype'} = $dbh->quote($biblioitem->{'itemtype'});
575 $biblioitem->{'url'} = $dbh->quote($biblioitem->{'url'});
576 $biblioitem->{'isbn'} = $dbh->quote($biblioitem->{'isbn'});
577 $biblioitem->{'publishercode'} = $dbh->quote($biblioitem->{'publishercode'});
578 $biblioitem->{'publicationyear'} = $dbh->quote($biblioitem->{'publicationyear'});
579 $biblioitem->{'classification'} = $dbh->quote($biblioitem->{'classification'});
580 $biblioitem->{'dewey'} = $dbh->quote($biblioitem->{'dewey'});
581 $biblioitem->{'subclass'} = $dbh->quote($biblioitem->{'subclass'});
582 $biblioitem->{'illus'} = $dbh->quote($biblioitem->{'illus'});
583 $biblioitem->{'pages'} = $dbh->quote($biblioitem->{'pages'});
584 $biblioitem->{'volumeddesc'} = $dbh->quote($biblioitem->{'volumeddesc'});
585 $biblioitem->{'notes'} = $dbh->quote($biblioitem->{'notes'});
586 $biblioitem->{'size'} = $dbh->quote($biblioitem->{'size'});
587 $biblioitem->{'place'} = $dbh->quote($biblioitem->{'place'});
589 $query = "Update biblioitems set
590 itemtype = $biblioitem->{'itemtype'},
591 url = $biblioitem->{'url'},
592 isbn = $biblioitem->{'isbn'},
593 publishercode = $biblioitem->{'publishercode'},
594 publicationyear = $biblioitem->{'publicationyear'},
595 classification = $biblioitem->{'classification'},
596 dewey = $biblioitem->{'dewey'},
597 subclass = $biblioitem->{'subclass'},
598 illus = $biblioitem->{'illus'},
599 pages = $biblioitem->{'pages'},
600 volumeddesc = $biblioitem->{'volumeddesc'},
601 notes = $biblioitem->{'notes'},
602 size = $biblioitem->{'size'},
603 place = $biblioitem->{'place'}
604 where biblioitemnumber = $biblioitem->{'biblioitemnumber'}";
613 my ($bibitemnum,$note)=@_;
615 my $query="update biblioitems set notes='$note' where
616 biblioitemnumber='$bibitemnum'";
617 my $sth=$dbh->prepare($query);
624 my ($biblioitem) = @_;
626 my $query = "Select max(biblioitemnumber) from biblioitems";
627 my $sth = $dbh->prepare($query);
631 $biblioitem->{'volume'} = $dbh->quote($biblioitem->{'volume'});
632 $biblioitem->{'number'} = $dbh->quote($biblioitem->{'number'});
633 $biblioitem->{'classification'} = $dbh->quote($biblioitem->{'classification'});
634 $biblioitem->{'itemtype'} = $dbh->quote($biblioitem->{'itemtype'});
635 $biblioitem->{'url'} = $dbh->quote($biblioitem->{'url'});
636 $biblioitem->{'isbn'} = $dbh->quote($biblioitem->{'isbn'});
637 $biblioitem->{'issn'} = $dbh->quote($biblioitem->{'issn'});
638 $biblioitem->{'dewey'} = $dbh->quote($biblioitem->{'dewey'});
639 $biblioitem->{'subclass'} = $dbh->quote($biblioitem->{'subclass'});
640 $biblioitem->{'publicationyear'} = $dbh->quote($biblioitem->{'publicationyear'});
641 $biblioitem->{'publishercode'} = $dbh->quote($biblioitem->{'publishercode'});
642 $biblioitem->{'volumedate'} = $dbh->quote($biblioitem->{'volumedate'});
643 $biblioitem->{'volumeddesc'} = $dbh->quote($biblioitem->{'volumeddesc'}); $biblioitem->{'illus'} = $dbh->quote($biblioitem->{'illus'});
644 $biblioitem->{'illus'} = $dbh->quote($biblioitem->{'illus'});
645 $biblioitem->{'pages'} = $dbh->quote($biblioitem->{'pages'});
646 $biblioitem->{'notes'} = $dbh->quote($biblioitem->{'notes'});
647 $biblioitem->{'size'} = $dbh->quote($biblioitem->{'size'});
648 $biblioitem->{'place'} = $dbh->quote($biblioitem->{'place'});
649 $biblioitem->{'lccn'} = $dbh->quote($biblioitem->{'lccn'});
650 $biblioitem->{'marc'} = $dbh->quote($biblioitem->{'marc'});
653 $data = $sth->fetchrow_arrayref;
654 $bibitemnum = $$data[0] + 1;
658 $query = "insert into biblioitems set
659 biblioitemnumber = $bibitemnum,
660 biblionumber = $biblioitem->{'biblionumber'},
661 volume = $biblioitem->{'volume'},
662 number = $biblioitem->{'number'},
663 classification = $biblioitem->{'classification'},
664 itemtype = $biblioitem->{'itemtype'},
665 url = $biblioitem->{'url'},
666 isbn = $biblioitem->{'isbn'},
667 issn = $biblioitem->{'issn'},
668 dewey = $biblioitem->{'dewey'},
669 subclass = $biblioitem->{'subclass'},
670 publicationyear = $biblioitem->{'publicationyear'},
671 publishercode = $biblioitem->{'publishercode'},
672 volumedate = $biblioitem->{'volumedate'},
673 volumeddesc = $biblioitem->{'volumeddesc'},
674 illus = $biblioitem->{'illus'},
675 pages = $biblioitem->{'pages'},
676 notes = $biblioitem->{'notes'},
677 size = $biblioitem->{'size'},
678 lccn = $biblioitem->{'lccn'},
679 marc = $biblioitem->{'marc'},
680 place = $biblioitem->{'place'}";
682 $sth = $dbh->prepare($query);
693 my $query="insert into bibliosubject (biblionumber) values
695 my $sth=$dbh->prepare($query);
703 my ($bibnum, $subtitle) = @_;
705 $subtitle = $dbh->quote($subtitle);
706 my $query = "insert into bibliosubtitle set
707 biblionumber = $bibnum,
708 subtitle = $subtitle";
709 my $sth = $dbh->prepare($query);
718 my ($bibnum,$title,$ordnum,$basket,$quantity,$listprice,$supplier,$who,$notes,$bookfund,$bibitemnum,$rrp,$ecost,$gst,$budget,$cost,$sub,$invoice)=@_;
719 if ($budget eq 'now'){
722 $budget="'2001-07-01'";
730 my $query="insert into aqorders (biblionumber,title,basketno,
731 quantity,listprice,booksellerid,entrydate,requisitionedby,authorisedby,notes,
732 biblioitemnumber,rrp,ecost,gst,unitprice,subscription,booksellerinvoicenumber)
735 ($bibnum,'$title',$basket,$quantity,$listprice,'$supplier',now(),
736 '$who','$who','$notes',$bibitemnum,'$rrp','$ecost','$gst','$cost',
738 my $sth=$dbh->prepare($query);
742 $query="select * from aqorders where
743 biblionumber=$bibnum and basketno=$basket and ordernumber >=$ordnum";
744 $sth=$dbh->prepare($query);
746 my $data=$sth->fetchrow_hashref;
748 $ordnum=$data->{'ordernumber'};
749 $query="insert into aqorderbreakdown (ordernumber,bookfundid) values
750 ($ordnum,'$bookfund')";
751 $sth=$dbh->prepare($query);
759 my ($bibnum,$ordnum)=@_;
761 my $query="update aqorders set datecancellationprinted=now()
762 where biblionumber='$bibnum' and
763 ordernumber='$ordnum'";
764 my $sth=$dbh->prepare($query);
768 my $count=itemcount($bibnum);
776 my ($title,$ordnum,$quantity,$listprice,$bibnum,$basketno,$supplier,$who,$notes,$bookfund,$bibitemnum,$rrp,$ecost,$gst,$budget,$cost,$invoice)=@_;
778 my $query="update aqorders set title='$title',
779 quantity='$quantity',listprice='$listprice',basketno='$basketno',
780 rrp='$rrp',ecost='$ecost',unitprice='$cost',
781 booksellerinvoicenumber='$invoice'
783 ordernumber=$ordnum and biblionumber=$bibnum";
784 my $sth=$dbh->prepare($query);
788 $query="update aqorderbreakdown set bookfundid=$bookfund where
789 ordernumber=$ordnum";
790 $sth=$dbh->prepare($query);
799 my $query="Select max(ordernumber) from aqorders";
800 my $sth=$dbh->prepare($query);
802 my $data=$sth->fetchrow_arrayref;
803 my $ordnum=$$data[0];
811 my ($biblio,$ordnum,$quantrec,$user,$cost,$invoiceno,$bibitemno,$freight,$bookfund,$rrp)=@_;
813 my $query="update aqorders set quantityreceived='$quantrec',
814 datereceived=now(),booksellerinvoicenumber='$invoiceno',
815 biblioitemnumber=$bibitemno,unitprice='$cost',freight='$freight',
817 where biblionumber=$biblio and ordernumber=$ordnum
820 my $sth=$dbh->prepare($query);
823 $query="update aqorderbreakdown set bookfundid=$bookfund where
824 ordernumber=$ordnum";
825 $sth=$dbh->prepare($query);
832 my($biblio,$ordnum,$user,$cost,$bookfund,$rrp)=@_;
834 my $query="update aqorders set
835 unitprice='$cost', rrp='$rrp'
836 where biblionumber=$biblio and ordernumber=$ordnum
839 my $sth=$dbh->prepare($query);
842 $query="update aqorderbreakdown set bookfundid=$bookfund where
843 ordernumber=$ordnum";
844 $sth=$dbh->prepare($query);
852 my ($currency,$price)=@_;
854 my $query="Select rate from currency where currency='$currency'";
855 my $sth=$dbh->prepare($query);
857 my $data=$sth->fetchrow_hashref;
860 my $cur=$data->{'rate'};
864 $price=$price / $cur;
870 my $query="Select * from currency";
871 my $sth=$dbh->prepare($query);
875 while (my $data=$sth->fetchrow_hashref){
881 return($i,\@results);
887 my $query="Select * from currency where currency='$cur'";
888 my $sth=$dbh->prepare($query);
891 my $data=$sth->fetchrow_hashref;
897 sub updatecurrencies {
898 my ($currency,$rate)=@_;
900 my $query="update currency set rate=$rate where currency='$currency'";
901 my $sth=$dbh->prepare($query);
910 my $query="Update aqbooksellers set
911 name='$data->{'name'}',address1='$data->{'address1'}',address2='$data->{'address2'}',
912 address3='$data->{'address3'}',address4='$data->{'address4'}',postal='$data->{'postal'}',
913 phone='$data->{'phone'}',fax='$data->{'fax'}',url='$data->{'url'}',
914 contact='$data->{'contact'}',contpos='$data->{'contpos'}',
915 contphone='$data->{'contphone'}', contfax='$data->{'contfax'}', contaltphone=
916 '$data->{'contaltphone'}', contemail='$data->{'contemail'}', contnotes=
917 '$data->{'contnotes'}', active=$data->{'active'},
918 listprice='$data->{'listprice'}', invoiceprice='$data->{'invoiceprice'}',
919 gstreg=$data->{'gstreg'}, listincgst=$data->{'listincgst'},
920 invoiceincgst=$data->{'invoiceincgst'}, specialty='$data->{'specialty'}',
921 discount='$data->{'discount'}',invoicedisc='$data->{'invoicedisc'}',
922 nocalc='$data->{'nocalc'}'
923 where id='$data->{'id'}'";
924 my $sth=$dbh->prepare($query);
934 my $sth=$dbh->prepare("Select max(id) from aqbooksellers");
936 my $data2=$sth->fetchrow_hashref;
938 $data2->{'max(id)'}++;
939 $sth=$dbh->prepare("Insert into aqbooksellers (id) values ($data2->{'max(id)'})");
942 $data->{'id'}=$data2->{'max(id)'};
945 return($data->{'id'});
950 my ($item, @barcodes) = @_;
952 my $query = "Select max(itemnumber) from items";
953 my $sth = $dbh->prepare($query);
959 $data = $sth->fetchrow_hashref;
960 $itemnumber = $data->{'max(itemnumber)'} + 1;
963 $item->{'booksellerid'} = $dbh->quote($item->{'booksellerid'});
964 $item->{'homebranch'} = $dbh->quote($item->{'homebranch'});
965 $item->{'price'} = $dbh->quote($item->{'price'});
966 $item->{'replacementprice'} = $dbh->quote($item->{'replacementprice'});
967 $item->{'itemnotes'} = $dbh->quote($item->{'itemnotes'});
969 foreach my $barcode (@barcodes) {
970 $barcode = uc($barcode);
971 $barcode = $dbh->quote($barcode);
972 $query = "Insert into items set
973 itemnumber = $itemnumber,
974 biblionumber = $item->{'biblionumber'},
975 biblioitemnumber = $item->{'biblioitemnumber'},
977 booksellerid = $item->{'booksellerid'},
978 dateaccessioned = NOW(),
979 homebranch = $item->{'homebranch'},
980 holdingbranch = $item->{'homebranch'},
981 price = $item->{'price'},
982 replacementprice = $item->{'replacementprice'},
983 replacementpricedate = NOW(),
984 itemnotes = $item->{'itemnotes'}";
986 if ($item->{'loan'}) {
988 notforloan = $item->{'loan'}";
991 $sth = $dbh->prepare($query);
994 $error .= $sth->errstr;
1005 my ($count,@barcodes)=@_;
1008 for (my $i=0;$i<$count;$i++){
1009 $barcodes[$i]=uc $barcodes[$i];
1010 my $query="Select * from items where barcode='$barcodes[$i]'";
1011 my $sth=$dbh->prepare($query);
1013 if (my $data=$sth->fetchrow_hashref){
1014 $error.=" Duplicate Barcode: $barcodes[$i]";
1023 my ($loan,$itemnum,$bibitemnum,$barcode,$notes,$homebranch,$lost,$wthdrawn,$replacement)=@_;
1025 my $query="update items set biblioitemnumber=$bibitemnum,
1026 barcode='$barcode',itemnotes='$notes'
1027 where itemnumber=$itemnum";
1028 if ($barcode eq ''){
1029 $query="update items set biblioitemnumber=$bibitemnum,notforloan=$loan where itemnumber=$itemnum";
1032 $query="update items set biblioitemnumber=$bibitemnum,
1033 barcode='$barcode',itemnotes='$notes',homebranch='$homebranch',
1034 itemlost='$lost',wthdrawn='$wthdrawn' where itemnumber=$itemnum";
1036 if ($replacement ne ''){
1037 $query=~ s/ where/,replacementprice='$replacement' where/;
1040 my $sth=$dbh->prepare($query);
1047 my($price,$rrp,$itemnum)=@_;
1049 my $query="update items set price='$price',replacementprice='$rrp'
1050 where itemnumber=$itemnum";
1051 my $sth=$dbh->prepare($query);
1057 my ($bibitemnum)=@_;
1059 my $query="Select count(*) from items where biblioitemnumber='$bibitemnum'";
1060 my $sth=$dbh->prepare($query);
1062 my $data=$sth->fetchrow_hashref;
1065 return($data->{'count(*)'});
1069 my ($biblionumber)=@_;
1071 my $query="Select * from biblioitems,items,itemtypes where
1072 biblioitems.biblionumber=$biblionumber
1073 and biblioitems.biblioitemnumber=items.biblioitemnumber and
1074 itemtypes.itemtype=biblioitems.itemtype
1075 order by items.biblioitemnumber";
1076 my $sth=$dbh->prepare($query);
1080 while (my $data=$sth->fetchrow_hashref){
1090 my ($bibitemnum,$itemtype)=@_;
1092 my $query="Select * from biblioitems where biblioitemnumber=$bibitemnum
1093 and itemtype='$itemtype'";
1094 my $sth=$dbh->prepare($query);
1097 if (my $data=$sth->fetchrow_hashref){
1108 my $query="select * from items where itemnumber=$itemnum";
1109 my $sth=$dbh->prepare($query);
1111 my @data=$sth->fetchrow_array;
1113 $query="Insert into deleteditems values (";
1114 foreach my $temp (@data){
1115 $query=$query."'$temp',";
1119 $sth=$dbh->prepare($query);
1122 $query = "Delete from items where itemnumber=$itemnum";
1123 $sth=$dbh->prepare($query);
1130 sub deletebiblioitem {
1131 my ($biblioitemnumber) = @_;
1132 my $dbh = C4Connect;
1133 my $query = "Select * from biblioitems
1134 where biblioitemnumber = $biblioitemnumber";
1135 my $sth = $dbh->prepare($query);
1140 if (@results = $sth->fetchrow_array) {
1142 $query = "Insert into deletedbiblioitems values (";
1143 foreach my $value (@results) {
1144 $value = $dbh->quote($value);
1145 $query .= "$value,";
1148 $query =~ s/\,$/\)/;
1151 $query = "Delete from biblioitems
1152 where biblioitemnumber = $biblioitemnumber";
1158 # Now delete all the items attached to the biblioitem
1160 $query = "Select * from items where biblioitemnumber = $biblioitemnumber";
1161 $sth = $dbh->prepare($query);
1165 while (@results = $sth->fetchrow_array) {
1167 $query = "Insert into deleteditems values (";
1168 foreach my $value (@results) {
1169 $value = $dbh->quote($value);
1170 $query .= "$value,";
1173 $query =~ s/\,$/\)/;
1179 $query = "Delete from items where biblioitemnumber = $biblioitemnumber";
1183 } # sub deletebiblioitem
1189 my $query="select * from biblio where biblionumber=$biblio";
1190 my $sth=$dbh->prepare($query);
1192 if (my @data=$sth->fetchrow_array){
1194 $query="Insert into deletedbiblio values (";
1195 foreach my $temp (@data){
1196 $temp=~ s/\'/\\\'/g;
1197 $query=$query."'$temp',";
1201 $sth=$dbh->prepare($query);
1204 $query = "Delete from biblio where biblionumber=$biblio";
1205 $sth=$dbh->prepare($query);
1216 my $dbh = C4Connect;
1217 my $query = "select * from itemtypes";
1218 my $sth = $dbh->prepare($query);
1219 # || die "Cannot prepare $query" . $dbh->errstr;
1224 # || die "Cannot execute $query\n" . $sth->errstr;
1225 while (my $data = $sth->fetchrow_hashref) {
1226 $results[$count] = $data;
1232 return($count, @results);
1233 } # sub getitemtypes
1237 my ($biblionumber) = @_;
1238 my $dbh = C4Connect;
1239 my $query = "Select * from biblio where biblionumber = $biblionumber";
1240 my $sth = $dbh->prepare($query);
1241 # || die "Cannot prepare $query\n" . $dbh->errstr;
1246 # || die "Cannot execute $query\n" . $sth->errstr;
1247 while (my $data = $sth->fetchrow_hashref) {
1248 $results[$count] = $data;
1254 return($count, @results);
1259 my ($biblioitemnum) = @_;
1260 my $dbh = C4Connect;
1261 my $query = "Select * from biblioitems where
1262 biblioitemnumber = $biblioitemnum";
1263 my $sth = $dbh->prepare($query);
1269 while (my $data = $sth->fetchrow_hashref) {
1270 $results[$count] = $data;
1276 return($count, @results);
1277 } # sub getbiblioitem
1280 sub getbiblioitembybiblionumber {
1281 my ($biblionumber) = @_;
1282 my $dbh = C4Connect;
1283 my $query = "Select * from biblioitems where biblionumber =
1285 my $sth = $dbh->prepare($query);
1291 while (my $data = $sth->fetchrow_hashref) {
1292 $results[$count] = $data;
1298 return($count, @results);
1302 sub getitemsbybiblioitem {
1303 my ($biblioitemnum) = @_;
1304 my $dbh = C4Connect;
1305 my $query = "Select * from items, biblio where
1306 biblio.biblionumber = items.biblionumber and biblioitemnumber
1308 my $sth = $dbh->prepare($query);
1309 # || die "Cannot prepare $query\n" . $dbh->errstr;
1314 # || die "Cannot execute $query\n" . $sth->errstr;
1315 while (my $data = $sth->fetchrow_hashref) {
1316 $results[$count] = $data;
1322 return($count, @results);
1323 } # sub getitemsbybiblioitem
1328 my $dbh = C4Connect;
1334 $isbn = $dbh->quote($isbn);
1335 $query = "Select biblio.* from biblio, biblioitems where
1336 biblio.biblionumber = biblioitems.biblionumber
1338 $sth = $dbh->prepare($query);
1341 while (my $data = $sth->fetchrow_hashref) {
1342 $results[$count] = $data;
1348 return($count, @results);
1353 my ($keywordlist) = @_;
1354 my $dbh = C4Connect;
1355 my $query = "Select distinct biblio.* from biblio, biblioitems where
1356 biblio.biblionumber = biblioitems.biblionumber and (";
1360 my @keywords = split(/ +/, $keywordlist);
1361 my $keyword = shift(@keywords);
1363 $keyword =~ s/%/\\%/g;
1364 $keyword =~ s/_/\\_/;
1365 $keyword = "%" . $keyword . "%";
1366 $keyword = $dbh->quote($keyword);
1367 $query .= " (url like $keyword)";
1369 foreach $keyword (@keywords) {
1370 $keyword =~ s/%/\\%/;
1371 $keyword =~ s/_/\\_/;
1372 $keyword = "%" . $keyword . "%";
1373 $keyword = $dbh->quote($keyword);
1374 $query .= " or (url like $keyword)";
1378 $sth = $dbh->prepare($query);
1381 while (my $data = $sth->fetchrow_hashref) {
1382 $results[$count] = $data;
1388 return($count, @results);
1389 } # sub websitesearch
1394 my $dbh = C4Connect;
1397 $website->{'biblionumber'} = $dbh->quote($website->{'biblionumber'});
1398 $website->{'title'} = $dbh->quote($website->{'title'});
1399 $website->{'description'} = $dbh->quote($website->{'description'});
1400 $website->{'url'} = $dbh->quote($website->{'url'});
1402 $query = "Insert into websites set
1403 biblionumber = $website->{'biblionumber'},
1404 title = $website->{'title'},
1405 description = $website->{'description'},
1406 url = $website->{'url'}";
1416 my $dbh = C4Connect;
1419 $website->{'title'} = $dbh->quote($website->{'title'});
1420 $website->{'description'} = $dbh->quote($website->{'description'});
1421 $website->{'url'} = $dbh->quote($website->{'url'});
1423 $query = "Update websites set
1424 title = $website->{'title'},
1425 description = $website->{'description'},
1426 url = $website->{'url'}
1427 where websitenumber = $website->{'websitenumber'}";
1432 } # sub updatewebsite
1436 my ($websitenumber) = @_;
1437 my $dbh = C4Connect;
1438 my $query = "Delete from websites where websitenumber = $websitenumber";
1443 } # sub deletewebsite
1446 END { } # module clean-up code here (global destructor)