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'});
645 $biblioitem->{'lccn'} = $dbh->quote($biblioitem->{'lccn'});
646 $biblioitem->{'marc'} = $dbh->quote($biblioitem->{'marc'});
649 $data = $sth->fetchrow_arrayref;
650 $bibitemnum = $$data[0] + 1;
654 $query = "insert into biblioitems set
655 biblioitemnumber = $bibitemnum,
656 biblionumber = $biblioitem->{'biblionumber'},
657 volume = $biblioitem->{'volume'},
658 number = $biblioitem->{'number'},
659 classification = $biblioitem->{'classification'},
660 itemtype = $biblioitem->{'itemtype'},
661 url = $biblioitem->{'url'},
662 isbn = $biblioitem->{'isbn'},
663 issn = $biblioitem->{'issn'},
664 dewey = $biblioitem->{'dewey'},
665 subclass = $biblioitem->{'subclass'},
666 publicationyear = $biblioitem->{'publicationyear'},
667 publishercode = $biblioitem->{'publishercode'},
668 volumedate = $biblioitem->{'volumedate'},
669 volumeddesc = $biblioitem->{'volumeddesc'},
670 illus = $biblioitem->{'illus'},
671 pages = $biblioitem->{'pages'},
672 notes = $biblioitem->{'notes'},
673 size = $biblioitem->{'size'},
674 lccn = $biblioitem->{'lccn'},
675 marc = $biblioitem->{'marc'},
676 place = $biblioitem->{'place'}";
678 $sth = $dbh->prepare($query);
689 my $query="insert into bibliosubject (biblionumber) values
691 my $sth=$dbh->prepare($query);
699 my ($bibnum, $subtitle) = @_;
701 $subtitle = $dbh->quote($subtitle);
702 my $query = "insert into bibliosubtitle set
703 biblionumber = $bibnum,
704 subtitle = $subtitle";
705 my $sth = $dbh->prepare($query);
714 my ($bibnum,$title,$ordnum,$basket,$quantity,$listprice,$supplier,$who,$notes,$bookfund,$bibitemnum,$rrp,$ecost,$gst,$budget,$cost,$sub,$invoice)=@_;
715 if ($budget eq 'now'){
718 $budget="'2001-07-01'";
726 my $query="insert into aqorders (biblionumber,title,basketno,
727 quantity,listprice,booksellerid,entrydate,requisitionedby,authorisedby,notes,
728 biblioitemnumber,rrp,ecost,gst,unitprice,subscription,booksellerinvoicenumber)
731 ($bibnum,'$title',$basket,$quantity,$listprice,'$supplier',now(),
732 '$who','$who','$notes',$bibitemnum,'$rrp','$ecost','$gst','$cost',
734 my $sth=$dbh->prepare($query);
738 $query="select * from aqorders where
739 biblionumber=$bibnum and basketno=$basket and ordernumber >=$ordnum";
740 $sth=$dbh->prepare($query);
742 my $data=$sth->fetchrow_hashref;
744 $ordnum=$data->{'ordernumber'};
745 $query="insert into aqorderbreakdown (ordernumber,bookfundid) values
746 ($ordnum,'$bookfund')";
747 $sth=$dbh->prepare($query);
755 my ($bibnum,$ordnum)=@_;
757 my $query="update aqorders set datecancellationprinted=now()
758 where biblionumber='$bibnum' and
759 ordernumber='$ordnum'";
760 my $sth=$dbh->prepare($query);
764 my $count=itemcount($bibnum);
772 my ($title,$ordnum,$quantity,$listprice,$bibnum,$basketno,$supplier,$who,$notes,$bookfund,$bibitemnum,$rrp,$ecost,$gst,$budget,$cost,$invoice)=@_;
774 my $query="update aqorders set title='$title',
775 quantity='$quantity',listprice='$listprice',basketno='$basketno',
776 rrp='$rrp',ecost='$ecost',unitprice='$cost',
777 booksellerinvoicenumber='$invoice'
779 ordernumber=$ordnum and biblionumber=$bibnum";
780 my $sth=$dbh->prepare($query);
784 $query="update aqorderbreakdown set bookfundid=$bookfund where
785 ordernumber=$ordnum";
786 $sth=$dbh->prepare($query);
795 my $query="Select max(ordernumber) from aqorders";
796 my $sth=$dbh->prepare($query);
798 my $data=$sth->fetchrow_arrayref;
799 my $ordnum=$$data[0];
807 my ($biblio,$ordnum,$quantrec,$user,$cost,$invoiceno,$bibitemno,$freight,$bookfund,$rrp)=@_;
809 my $query="update aqorders set quantityreceived='$quantrec',
810 datereceived=now(),booksellerinvoicenumber='$invoiceno',
811 biblioitemnumber=$bibitemno,unitprice='$cost',freight='$freight',
813 where biblionumber=$biblio and ordernumber=$ordnum
816 my $sth=$dbh->prepare($query);
819 $query="update aqorderbreakdown set bookfundid=$bookfund where
820 ordernumber=$ordnum";
821 $sth=$dbh->prepare($query);
828 my($biblio,$ordnum,$user,$cost,$bookfund,$rrp)=@_;
830 my $query="update aqorders set
831 unitprice='$cost', rrp='$rrp'
832 where biblionumber=$biblio and ordernumber=$ordnum
835 my $sth=$dbh->prepare($query);
838 $query="update aqorderbreakdown set bookfundid=$bookfund where
839 ordernumber=$ordnum";
840 $sth=$dbh->prepare($query);
848 my ($currency,$price)=@_;
850 my $query="Select rate from currency where currency='$currency'";
851 my $sth=$dbh->prepare($query);
853 my $data=$sth->fetchrow_hashref;
856 my $cur=$data->{'rate'};
860 my $price=$price / $cur;
866 my $query="Select * from currency";
867 my $sth=$dbh->prepare($query);
871 while (my $data=$sth->fetchrow_hashref){
877 return($i,\@results);
883 my $query="Select * from currency where currency='$cur'";
884 my $sth=$dbh->prepare($query);
887 my $data=$sth->fetchrow_hashref;
893 sub updatecurrencies {
894 my ($currency,$rate)=@_;
896 my $query="update currency set rate=$rate where currency='$currency'";
897 my $sth=$dbh->prepare($query);
906 my $query="Update aqbooksellers set
907 name='$data->{'name'}',address1='$data->{'address1'}',address2='$data->{'address2'}',
908 address3='$data->{'address3'}',address4='$data->{'address4'}',postal='$data->{'postal'}',
909 phone='$data->{'phone'}',fax='$data->{'fax'}',url='$data->{'url'}',
910 contact='$data->{'contact'}',contpos='$data->{'contpos'}',
911 contphone='$data->{'contphone'}', contfax='$data->{'contfax'}', contaltphone=
912 '$data->{'contaltphone'}', contemail='$data->{'contemail'}', contnotes=
913 '$data->{'contnotes'}', active=$data->{'active'},
914 listprice='$data->{'listprice'}', invoiceprice='$data->{'invoiceprice'}',
915 gstreg=$data->{'gstreg'}, listincgst=$data->{'listincgst'},
916 invoiceincgst=$data->{'invoiceincgst'}, specialty='$data->{'specialty'}',
917 discount='$data->{'discount'}',invoicedisc='$data->{'invoicedisc'}',
918 nocalc='$data->{'nocalc'}'
919 where id='$data->{'id'}'";
920 my $sth=$dbh->prepare($query);
930 my $sth=$dbh->prepare("Select max(id) from aqbooksellers");
932 my $data2=$sth->fetchrow_hashref;
934 $data2->{'max(id)'}++;
935 $sth=$dbh->prepare("Insert into aqbooksellers (id) values ($data2->{'max(id)'})");
938 $data->{'id'}=$data2->{'max(id)'};
941 return($data->{'id'});
946 my ($item, @barcodes) = @_;
948 my $query = "Select max(itemnumber) from items";
949 my $sth = $dbh->prepare($query);
955 $data = $sth->fetchrow_hashref;
956 $itemnumber = $data->{'max(itemnumber)'} + 1;
959 $item->{'booksellerid'} = $dbh->quote($item->{'booksellerid'});
960 $item->{'homebranch'} = $dbh->quote($item->{'homebranch'});
961 $item->{'price'} = $dbh->quote($item->{'price'});
962 $item->{'replacementprice'} = $dbh->quote($item->{'replacementprice'});
963 $item->{'itemnotes'} = $dbh->quote($item->{'itemnotes'});
965 foreach my $barcode (@barcodes) {
966 $barcode = uc($barcode);
967 $barcode = $dbh->quote($barcode);
968 $query = "Insert into items set
969 itemnumber = $itemnumber,
970 biblionumber = $item->{'biblionumber'},
971 biblioitemnumber = $item->{'biblioitemnumber'},
973 booksellerid = $item->{'booksellerid'},
974 dateaccessioned = NOW(),
975 homebranch = $item->{'homebranch'},
976 holdingbranch = $item->{'homebranch'},
977 price = $item->{'price'},
978 replacementprice = $item->{'replacementprice'},
979 replacementpricedate = NOW(),
980 itemnotes = $item->{'itemnotes'}";
982 if ($item->{'loan'}) {
984 notforloan = $item->{'loan'}";
987 $sth = $dbh->prepare($query);
990 $error .= $sth->errstr;
1001 my ($count,@barcodes)=@_;
1004 for (my $i=0;$i<$count;$i++){
1005 $barcodes[$i]=uc $barcodes[$i];
1006 my $query="Select * from items where barcode='$barcodes[$i]'";
1007 my $sth=$dbh->prepare($query);
1009 if (my $data=$sth->fetchrow_hashref){
1010 $error.=" Duplicate Barcode: $barcodes[$i]";
1019 my ($loan,$itemnum,$bibitemnum,$barcode,$notes,$homebranch,$lost,$wthdrawn,$replacement)=@_;
1021 my $query="update items set biblioitemnumber=$bibitemnum,
1022 barcode='$barcode',itemnotes='$notes'
1023 where itemnumber=$itemnum";
1024 if ($barcode eq ''){
1025 $query="update items set biblioitemnumber=$bibitemnum,notforloan=$loan where itemnumber=$itemnum";
1028 $query="update items set biblioitemnumber=$bibitemnum,
1029 barcode='$barcode',itemnotes='$notes',homebranch='$homebranch',
1030 itemlost='$lost',wthdrawn='$wthdrawn' where itemnumber=$itemnum";
1032 if ($replacement ne ''){
1033 $query=~ s/ where/,replacementprice='$replacement' where/;
1036 my $sth=$dbh->prepare($query);
1043 my($price,$rrp,$itemnum)=@_;
1045 my $query="update items set price='$price',replacementprice='$rrp'
1046 where itemnumber=$itemnum";
1047 my $sth=$dbh->prepare($query);
1053 my ($bibitemnum)=@_;
1055 my $query="Select count(*) from items where biblioitemnumber='$bibitemnum'";
1056 my $sth=$dbh->prepare($query);
1058 my $data=$sth->fetchrow_hashref;
1061 return($data->{'count(*)'});
1065 my ($biblionumber)=@_;
1067 my $query="Select * from biblioitems,items,itemtypes where
1068 biblioitems.biblionumber=$biblionumber
1069 and biblioitems.biblioitemnumber=items.biblioitemnumber and
1070 itemtypes.itemtype=biblioitems.itemtype
1071 order by items.biblioitemnumber";
1072 my $sth=$dbh->prepare($query);
1076 while (my $data=$sth->fetchrow_hashref){
1086 my ($bibitemnum,$itemtype)=@_;
1088 my $query="Select * from biblioitems where biblioitemnumber=$bibitemnum
1089 and itemtype='$itemtype'";
1090 my $sth=$dbh->prepare($query);
1093 if (my $data=$sth->fetchrow_hashref){
1104 my $query="select * from items where itemnumber=$itemnum";
1105 my $sth=$dbh->prepare($query);
1107 my @data=$sth->fetchrow_array;
1109 $query="Insert into deleteditems values (";
1110 foreach my $temp (@data){
1111 $query=$query."'$temp',";
1115 $sth=$dbh->prepare($query);
1118 $query = "Delete from items where itemnumber=$itemnum";
1119 $sth=$dbh->prepare($query);
1126 sub deletebiblioitem {
1127 my ($biblioitemnumber) = @_;
1128 my $dbh = C4Connect;
1129 my $query = "Select * from biblioitems
1130 where biblioitemnumber = $biblioitemnumber";
1131 my $sth = $dbh->prepare($query);
1136 if (@results = $sth->fetchrow_array) {
1138 $query = "Insert into deletedbiblioitems values (";
1139 foreach my $value (@results) {
1140 $value = $dbh->quote($value);
1141 $query .= "$value,";
1144 $query =~ s/\,$/\)/;
1147 $query = "Delete from biblioitems
1148 where biblioitemnumber = $biblioitemnumber";
1154 # Now delete all the items attached to the biblioitem
1156 $query = "Select * from items where biblioitemnumber = $biblioitemnumber";
1157 $sth = $dbh->prepare($query);
1161 while (@results = $sth->fetchrow_array) {
1163 $query = "Insert into deleteditems values (";
1164 foreach my $value (@results) {
1165 $value = $dbh->quote($value);
1166 $query .= "$value,";
1169 $query =~ s/\,$/\)/;
1175 $query = "Delete from items where biblioitemnumber = $biblioitemnumber";
1179 } # sub deletebiblioitem
1185 my $query="select * from biblio where biblionumber=$biblio";
1186 my $sth=$dbh->prepare($query);
1188 if (my @data=$sth->fetchrow_array){
1190 $query="Insert into deletedbiblio values (";
1191 foreach my $temp (@data){
1192 $temp=~ s/\'/\\\'/g;
1193 $query=$query."'$temp',";
1197 $sth=$dbh->prepare($query);
1200 $query = "Delete from biblio where biblionumber=$biblio";
1201 $sth=$dbh->prepare($query);
1212 my $dbh = C4Connect;
1213 my $query = "select * from itemtypes";
1214 my $sth = $dbh->prepare($query);
1215 # || die "Cannot prepare $query" . $dbh->errstr;
1220 # || die "Cannot execute $query\n" . $sth->errstr;
1221 while (my $data = $sth->fetchrow_hashref) {
1222 @results[$count] = $data;
1228 return($count, @results);
1229 } # sub getitemtypes
1233 my ($biblionumber) = @_;
1234 my $dbh = C4Connect;
1235 my $query = "Select * from biblio where biblionumber = $biblionumber";
1236 my $sth = $dbh->prepare($query);
1237 # || die "Cannot prepare $query\n" . $dbh->errstr;
1242 # || die "Cannot execute $query\n" . $sth->errstr;
1243 while (my $data = $sth->fetchrow_hashref) {
1244 $results[$count] = $data;
1250 return($count, @results);
1255 my ($biblioitemnum) = @_;
1256 my $dbh = C4Connect;
1257 my $query = "Select * from biblioitems where
1258 biblioitemnumber = $biblioitemnum";
1259 my $sth = $dbh->prepare($query);
1265 while (my $data = $sth->fetchrow_hashref) {
1266 $results[$count] = $data;
1272 return($count, @results);
1273 } # sub getbiblioitem
1276 sub getbiblioitembybiblionumber {
1277 my ($biblionumber) = @_;
1278 my $dbh = C4Connect;
1279 my $query = "Select * from biblioitems where biblionumber =
1281 my $sth = $dbh->prepare($query);
1287 while (my $data = $sth->fetchrow_hashref) {
1288 $results[$count] = $data;
1294 return($count, @results);
1298 sub getitemsbybiblioitem {
1299 my ($biblioitemnum) = @_;
1300 my $dbh = C4Connect;
1301 my $query = "Select * from items, biblio where
1302 biblio.biblionumber = items.biblionumber and biblioitemnumber
1304 my $sth = $dbh->prepare($query);
1305 # || die "Cannot prepare $query\n" . $dbh->errstr;
1310 # || die "Cannot execute $query\n" . $sth->errstr;
1311 while (my $data = $sth->fetchrow_hashref) {
1312 $results[$count] = $data;
1318 return($count, @results);
1319 } # sub getitemsbybiblioitem
1324 my $dbh = C4Connect;
1330 $isbn = $dbh->quote($isbn);
1331 $query = "Select biblio.* from biblio, biblioitems where
1332 biblio.biblionumber = biblioitems.biblionumber
1334 $sth = $dbh->prepare($query);
1337 while (my $data = $sth->fetchrow_hashref) {
1338 $results[$count] = $data;
1344 return($count, @results);
1349 my ($keywordlist) = @_;
1350 my $dbh = C4Connect;
1351 my $query = "Select distinct biblio.* from biblio, biblioitems where
1352 biblio.biblionumber = biblioitems.biblionumber and (";
1356 my @keywords = split(/ +/, $keywordlist);
1357 my $keyword = shift(@keywords);
1359 $keyword =~ s/%/\\%/g;
1360 $keyword =~ s/_/\\_/;
1361 $keyword = "%" . $keyword . "%";
1362 $keyword = $dbh->quote($keyword);
1363 $query .= " (url like $keyword)";
1365 foreach $keyword (@keywords) {
1366 $keyword =~ s/%/\\%/;
1367 $keyword =~ s/_/\\_/;
1368 $keyword = "%" . $keyword . "%";
1369 $keyword = $dbh->quote($keyword);
1370 $query .= " or (url like $keyword)";
1374 $sth = $dbh->prepare($query);
1377 while (my $data = $sth->fetchrow_hashref) {
1378 $results[$count] = $data;
1384 return($count, @results);
1385 } # sub websitesearch
1390 my $dbh = C4Connect;
1393 $website->{'biblionumber'} = $dbh->quote($website->{'biblionumber'});
1394 $website->{'title'} = $dbh->quote($website->{'title'});
1395 $website->{'description'} = $dbh->quote($website->{'description'});
1396 $website->{'url'} = $dbh->quote($website->{'url'});
1398 $query = "Insert into websites set
1399 biblionumber = $website->{'biblionumber'},
1400 title = $website->{'title'},
1401 description = $website->{'description'},
1402 url = $website->{'url'}";
1412 my $dbh = C4Connect;
1415 $website->{'title'} = $dbh->quote($website->{'title'});
1416 $website->{'description'} = $dbh->quote($website->{'description'});
1417 $website->{'url'} = $dbh->quote($website->{'url'});
1419 $query = "Update websites set
1420 title = $website->{'title'},
1421 description = $website->{'description'},
1422 url = $website->{'url'}
1423 where websitenumber = $website->{'websitenumber'}";
1428 } # sub updatewebsite
1432 my ($websitenumber) = @_;
1433 my $dbh = C4Connect;
1434 my $query = "Delete from websites where websitenumber = $websitenumber";
1439 } # sub deletewebsite
1442 END { } # module clean-up code here (global destructor)