1 package C4::Acquisitions; #assumes C4/Acquisitions.pm
4 # Copyright 2000-2002 Katipo Communications
6 # This file is part of Koha.
8 # Koha is free software; you can redistribute it and/or modify it under the
9 # terms of the GNU General Public License as published by the Free Software
10 # Foundation; either version 2 of the License, or (at your option) any later
13 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
14 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
15 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
17 # You should have received a copy of the GNU General Public License along with
18 # Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
19 # Suite 330, Boston, MA 02111-1307 USA
25 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
27 # set the version for version checking
31 @EXPORT = qw(&getorders &bookseller &breakdown &basket &newbasket &bookfunds
32 &ordersearch &newbiblio &newbiblioitem &newsubject &newsubtitle &neworder
33 &newordernum &modbiblio &modorder &getsingleorder &invoice &receiveorder
34 &bookfundbreakdown &curconvert &updatesup &insertsup &newitems &modbibitem
35 &getcurrencies &modsubtitle &modsubject &modaddauthor &moditem &countitems
36 &findall &needsmod &delitem &deletebiblioitem &delbiblio &delorder &branches
37 &getallorders &getrecorders &updatecurrencies &getorder &getcurrency &updaterecorder
38 &updatecost &checkitems &modnote &getitemtypes &getbiblio
39 &getbiblioitembybiblionumber
40 &getbiblioitem &getitemsbybiblioitem &isbnsearch
41 &websitesearch &addwebsite &updatewebsite &deletewebsite);
42 %EXPORT_TAGS = ( ); # eg: TAG => [ qw!name1 name2! ],
44 # your exported package globals go here,
45 # as well as any optionally exported functions
47 @EXPORT_OK = qw($Var1 %Hashit);
50 # non-exported package globals go here
51 use vars qw(@more $stuff);
53 # initalize package globals, first exported ones
60 # then the others (which are still accessible as $Some::Module::stuff)
64 # all file-scoped lexicals must be created before
65 # the functions below that use them.
67 # file-private lexicals go here
71 # here's a file-private function as a closure,
72 # callable as &$priv_func; it cannot be prototyped.
77 # make all your functions, whether exported or not;
82 my $query = "Select count(*),authorisedby,entrydate,basketno from aqorders where
83 booksellerid='$supplierid' and (quantity > quantityreceived or
84 quantityreceived is NULL)
85 and (datecancellationprinted is NULL or datecancellationprinted = '0000-00-00')";
86 $query.=" group by basketno order by entrydate desc";
88 my $sth=$dbh->prepare($query);
92 while (my $data=$sth->fetchrow_hashref){
98 return ($i,\@results);
104 my $query="Select count(*) from items where biblionumber=$biblio";
106 my $sth=$dbh->prepare($query);
108 my $data=$sth->fetchrow_hashref;
111 return($data->{'count(*)'});
117 my $query="Select ordernumber
119 where biblionumber=? and biblioitemnumber=?";
120 my $sth=$dbh->prepare($query);
121 $sth->execute($bib,$bi);
122 my $ordnum=$sth->fetchrow_hashref;
124 my $order=getsingleorder($ordnum->{'ordernumber'});
127 return ($order,$ordnum->{'ordernumber'});
133 my $query="Select * from biblio,biblioitems,aqorders,aqorderbreakdown
134 where aqorders.ordernumber=?
135 and biblio.biblionumber=aqorders.biblionumber and
136 biblioitems.biblioitemnumber=aqorders.biblioitemnumber and
137 aqorders.ordernumber=aqorderbreakdown.ordernumber";
138 my $sth=$dbh->prepare($query);
139 $sth->execute($ordnum);
140 my $data=$sth->fetchrow_hashref;
149 my $query="Select * from aqorders,biblio,biblioitems where
150 booksellerinvoicenumber='$invoice'
151 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber=
152 aqorders.biblioitemnumber group by aqorders.ordernumber,aqorders.biblioitemnumber";
155 my $sth=$dbh->prepare($query);
157 while (my $data=$sth->fetchrow_hashref){
167 #gets all orders from a certain supplier, orders them alphabetically
170 my $query="Select * from aqorders,biblio,biblioitems where booksellerid='$supid'
171 and (cancelledby is NULL or cancelledby = '')
172 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber=
173 aqorders.biblioitemnumber
174 group by aqorders.biblioitemnumber
179 my $sth=$dbh->prepare($query);
181 while (my $data=$sth->fetchrow_hashref){
191 #gets all orders from a certain supplier, orders them alphabetically
194 my $query="Select * from aqorders,biblio,biblioitems where booksellerid='$supid'
195 and (cancelledby is NULL or cancelledby = '')
196 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber=
197 aqorders.biblioitemnumber and
198 aqorders.quantityreceived>0
199 and aqorders.datereceived >=now()
200 group by aqorders.biblioitemnumber
205 my $sth=$dbh->prepare($query);
207 while (my $data=$sth->fetchrow_hashref){
217 my ($search,$biblio,$catview)=@_;
219 my $query="Select *,biblio.title from aqorders,biblioitems,biblio
220 where aqorders.biblioitemnumber = biblioitems.biblioitemnumber
221 and biblio.biblionumber=aqorders.biblionumber
222 and ((datecancellationprinted is NULL)
223 or (datecancellationprinted = '0000-00-00'))
225 my @data=split(' ',$search);
227 for (my $i=0;$i<$count;$i++){
228 $query.= "(biblio.title like '$data[$i]%' or biblio.title like '% $data[$i]%') and ";
231 $query.=" ) or biblioitems.isbn='$search'
232 or (aqorders.ordernumber='$search' and aqorders.biblionumber='$biblio')) ";
233 if ($catview ne 'yes'){
234 $query.=" and (quantityreceived < quantity or quantityreceived is NULL)";
236 $query.=" group by aqorders.ordernumber";
237 my $sth=$dbh->prepare($query);
242 while (my $data=$sth->fetchrow_hashref){
243 my $sth2=$dbh->prepare("Select * from biblio where
244 biblionumber='$data->{'biblionumber'}'");
246 my $data2=$sth2->fetchrow_hashref;
248 $data->{'author'}=$data2->{'author'};
249 $data->{'seriestitle'}=$data2->{'seriestitle'};
250 $sth2=$dbh->prepare("Select * from aqorderbreakdown where
251 ordernumber=$data->{'ordernumber'}");
253 $data2=$sth2->fetchrow_hashref;
255 $data->{'branchcode'}=$data2->{'branchcode'};
256 $data->{'bookfundid'}=$data2->{'bookfundid'};
267 my ($searchstring)=@_;
269 my $query="Select * from aqbooksellers where name like '%$searchstring%' or
270 id = '$searchstring'";
271 my $sth=$dbh->prepare($query);
275 while (my $data=$sth->fetchrow_hashref){
287 my $query="Select * from aqorderbreakdown where ordernumber='$id'";
288 my $sth=$dbh->prepare($query);
292 while (my $data=$sth->fetchrow_hashref){
298 return($i,\@results);
302 my ($basketno,$supplier)=@_;
304 my $query="Select *,biblio.title from aqorders,biblio,biblioitems
305 where basketno='$basketno'
306 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber
307 =aqorders.biblioitemnumber
308 and (datecancellationprinted is NULL or datecancellationprinted =
310 if (defined $supplier && $supplier ne ''){
311 $query.=" and aqorders.booksellerid='$supplier'";
313 $query.=" group by aqorders.ordernumber";
314 my $sth=$dbh->prepare($query);
319 while (my $data=$sth->fetchrow_hashref){
330 my $query="Select max(basketno) from aqorders";
331 my $sth=$dbh->prepare($query);
333 my $data=$sth->fetchrow_arrayref;
334 my $basket=$$data[0];
343 my $query="Select * from aqbookfund,aqbudget where aqbookfund.bookfundid
345 and aqbudget.startdate='2001-07-01'
346 group by aqbookfund.bookfundid order by bookfundname";
347 my $sth=$dbh->prepare($query);
351 while (my $data=$sth->fetchrow_hashref){
362 my $query="Select * from branches";
363 my $sth=$dbh->prepare($query);
368 while (my $data = $sth->fetchrow_hashref){
375 return($i, @results);
378 sub bookfundbreakdown {
381 my $query="Select quantity,datereceived,freight,unitprice,listprice,ecost,quantityreceived,subscription
382 from aqorders,aqorderbreakdown where bookfundid='$id' and
383 aqorders.ordernumber=aqorderbreakdown.ordernumber and ((budgetdate >=
384 '2001-07-01' and budgetdate <'2002-07-01') or
385 (datereceived >= '2001-07-01' and datereceived < '2002-07-01'))
386 and (datecancellationprinted is NULL or
387 datecancellationprinted='0000-00-00')";
388 my $sth=$dbh->prepare($query);
392 while (my $data=$sth->fetchrow_hashref){
393 if ($data->{'subscription'} == 1){
394 $spent+=$data->{'quantity'}*$data->{'unitprice'};
396 my $leftover=$data->{'quantity'}-$data->{'quantityreceived'};
397 $comtd+=($data->{'ecost'})*$leftover;
398 $spent+=($data->{'unitprice'})*$data->{'quantityreceived'};
403 return($spent,$comtd);
409 my $dbh = &C4Connect;
410 my $query = "Select max(biblionumber) from biblio";
411 my $sth = $dbh->prepare($query);
413 my $data = $sth->fetchrow_arrayref;
414 my $bibnum = $$data[0] + 1;
417 $biblio->{'title'} = $dbh->quote($biblio->{'title'});
418 $biblio->{'author'} = $dbh->quote($biblio->{'author'});
419 $biblio->{'copyright'} = $dbh->quote($biblio->{'copyright'});
420 $biblio->{'seriestitle'} = $dbh->quote($biblio->{'seriestitle'});
421 $biblio->{'notes'} = $dbh->quote($biblio->{'notes'});
422 $biblio->{'abstract'} = $dbh->quote($biblio->{'abstract'});
423 if ($biblio->{'seriestitle'}) { $series = 1 };
426 $query = "insert into biblio set
427 biblionumber = $bibnum,
428 title = $biblio->{'title'},
429 author = $biblio->{'author'},
430 copyrightdate = $biblio->{'copyright'},
432 seriestitle = $biblio->{'seriestitle'},
433 notes = $biblio->{'notes'},
434 abstract = $biblio->{'abstract'}";
436 $sth = $dbh->prepare($query);
451 $biblio->{'title'} = $dbh->quote($biblio->{'title'});
452 $biblio->{'author'} = $dbh->quote($biblio->{'author'});
453 $biblio->{'abstract'} = $dbh->quote($biblio->{'abstract'});
454 $biblio->{'copyrightdate'} = $dbh->quote($biblio->{'copyrightdate'});
455 $biblio->{'seriestitle'} = $dbh->quote($biblio->{'serirestitle'});
456 $biblio->{'serial'} = $dbh->quote($biblio->{'serial'});
457 $biblio->{'unititle'} = $dbh->quote($biblio->{'unititle'});
458 $biblio->{'notes'} = $dbh->quote($biblio->{'notes'});
460 $query = "Update biblio set
461 title = $biblio->{'title'},
462 author = $biblio->{'author'},
463 abstract = $biblio->{'abstract'},
464 copyrightdate = $biblio->{'copyrightdate'},
465 seriestitle = $biblio->{'seriestitle'},
466 serial = $biblio->{'serial'},
467 unititle = $biblio->{'unititle'},
468 notes = $biblio->{'notes'}
469 where biblionumber = $biblio->{'biblionumber'}";
470 $sth = $dbh->prepare($query);
476 return($biblio->{'biblionumber'});
481 my ($bibnum, $subtitle) = @_;
483 my $query = "update bibliosubtitle set
484 subtitle = '$subtitle'
485 where biblionumber = $bibnum";
486 my $sth = $dbh->prepare($query);
495 my ($bibnum, $author) = @_;
497 my $query = "Delete from additionalauthors where biblionumber = $bibnum";
498 my $sth = $dbh->prepare($query);
504 $query = "Insert into additionalauthors set
506 biblionumber = '$bibnum'";
507 $sth = $dbh->prepare($query);
519 my ($bibnum, $force, @subject) = @_;
521 my $count = @subject;
523 for (my $i = 0; $i < $count; $i++) {
524 $subject[$i] =~ s/^ //g;
525 $subject[$i] =~ s/ $//g;
526 my $query = "select * from catalogueentry
527 where entrytype = 's'
528 and catalogueentry = '$subject[$i]'";
529 my $sth = $dbh->prepare($query);
532 if (my $data = $sth->fetchrow_hashref) {
534 if ($force eq $subject[$i]) {
536 # subject not in aut, chosen to force anway
537 # so insert into cataloguentry so its in auth file
538 $query = "Insert into catalogueentry
539 (entrytype,catalogueentry)
540 values ('s','$subject[$i]')";
541 my $sth2 = $dbh->prepare($query);
548 $error = "$subject[$i]\n does not exist in the subject authority file";
549 $query = "Select * from catalogueentry
550 where entrytype = 's'
551 and (catalogueentry like '$subject[$i] %'
552 or catalogueentry like '% $subject[$i] %'
553 or catalogueentry like '% $subject[$i]')";
554 my $sth2 = $dbh->prepare($query);
557 while (my $data = $sth2->fetchrow_hashref) {
558 $error = $error."<br>$data->{'catalogueentry'}";
567 my $query = "Delete from bibliosubject where biblionumber = $bibnum";
568 my $sth = $dbh->prepare($query);
573 for (my $i = 0; $i < $count; $i++) {
574 $sth = $dbh->prepare("Insert into bibliosubject
575 values ('$subject[$i]', $bibnum)");
588 my ($biblioitem) = @_;
592 $biblioitem->{'itemtype'} = $dbh->quote($biblioitem->{'itemtype'});
593 $biblioitem->{'url'} = $dbh->quote($biblioitem->{'url'});
594 $biblioitem->{'isbn'} = $dbh->quote($biblioitem->{'isbn'});
595 $biblioitem->{'publishercode'} = $dbh->quote($biblioitem->{'publishercode'});
596 $biblioitem->{'publicationyear'} = $dbh->quote($biblioitem->{'publicationyear'});
597 $biblioitem->{'classification'} = $dbh->quote($biblioitem->{'classification'});
598 $biblioitem->{'dewey'} = $dbh->quote($biblioitem->{'dewey'});
599 $biblioitem->{'subclass'} = $dbh->quote($biblioitem->{'subclass'});
600 $biblioitem->{'illus'} = $dbh->quote($biblioitem->{'illus'});
601 $biblioitem->{'pages'} = $dbh->quote($biblioitem->{'pages'});
602 $biblioitem->{'volumeddesc'} = $dbh->quote($biblioitem->{'volumeddesc'});
603 $biblioitem->{'notes'} = $dbh->quote($biblioitem->{'notes'});
604 $biblioitem->{'size'} = $dbh->quote($biblioitem->{'size'});
605 $biblioitem->{'place'} = $dbh->quote($biblioitem->{'place'});
607 $query = "Update biblioitems set
608 itemtype = $biblioitem->{'itemtype'},
609 url = $biblioitem->{'url'},
610 isbn = $biblioitem->{'isbn'},
611 publishercode = $biblioitem->{'publishercode'},
612 publicationyear = $biblioitem->{'publicationyear'},
613 classification = $biblioitem->{'classification'},
614 dewey = $biblioitem->{'dewey'},
615 subclass = $biblioitem->{'subclass'},
616 illus = $biblioitem->{'illus'},
617 pages = $biblioitem->{'pages'},
618 volumeddesc = $biblioitem->{'volumeddesc'},
619 notes = $biblioitem->{'notes'},
620 size = $biblioitem->{'size'},
621 place = $biblioitem->{'place'}
622 where biblioitemnumber = $biblioitem->{'biblioitemnumber'}";
631 my ($bibitemnum,$note)=@_;
633 my $query="update biblioitems set notes='$note' where
634 biblioitemnumber='$bibitemnum'";
635 my $sth=$dbh->prepare($query);
642 my ($biblioitem) = @_;
644 my $query = "Select max(biblioitemnumber) from biblioitems";
645 my $sth = $dbh->prepare($query);
649 $biblioitem->{'volume'} = $dbh->quote($biblioitem->{'volume'});
650 $biblioitem->{'number'} = $dbh->quote($biblioitem->{'number'});
651 $biblioitem->{'classification'} = $dbh->quote($biblioitem->{'classification'});
652 $biblioitem->{'itemtype'} = $dbh->quote($biblioitem->{'itemtype'});
653 $biblioitem->{'url'} = $dbh->quote($biblioitem->{'url'});
654 $biblioitem->{'isbn'} = $dbh->quote($biblioitem->{'isbn'});
655 $biblioitem->{'issn'} = $dbh->quote($biblioitem->{'issn'});
656 $biblioitem->{'dewey'} = $dbh->quote($biblioitem->{'dewey'});
657 $biblioitem->{'subclass'} = $dbh->quote($biblioitem->{'subclass'});
658 $biblioitem->{'publicationyear'} = $dbh->quote($biblioitem->{'publicationyear'});
659 $biblioitem->{'publishercode'} = $dbh->quote($biblioitem->{'publishercode'});
660 $biblioitem->{'volumedate'} = $dbh->quote($biblioitem->{'volumedate'});
661 $biblioitem->{'volumeddesc'} = $dbh->quote($biblioitem->{'volumeddesc'}); $biblioitem->{'illus'} = $dbh->quote($biblioitem->{'illus'});
662 $biblioitem->{'illus'} = $dbh->quote($biblioitem->{'illus'});
663 $biblioitem->{'pages'} = $dbh->quote($biblioitem->{'pages'});
664 $biblioitem->{'notes'} = $dbh->quote($biblioitem->{'notes'});
665 $biblioitem->{'size'} = $dbh->quote($biblioitem->{'size'});
666 $biblioitem->{'place'} = $dbh->quote($biblioitem->{'place'});
667 $biblioitem->{'lccn'} = $dbh->quote($biblioitem->{'lccn'});
668 $biblioitem->{'marc'} = $dbh->quote($biblioitem->{'marc'});
671 $data = $sth->fetchrow_arrayref;
672 $bibitemnum = $$data[0] + 1;
676 $query = "insert into biblioitems set
677 biblioitemnumber = $bibitemnum,
678 biblionumber = $biblioitem->{'biblionumber'},
679 volume = $biblioitem->{'volume'},
680 number = $biblioitem->{'number'},
681 classification = $biblioitem->{'classification'},
682 itemtype = $biblioitem->{'itemtype'},
683 url = $biblioitem->{'url'},
684 isbn = $biblioitem->{'isbn'},
685 issn = $biblioitem->{'issn'},
686 dewey = $biblioitem->{'dewey'},
687 subclass = $biblioitem->{'subclass'},
688 publicationyear = $biblioitem->{'publicationyear'},
689 publishercode = $biblioitem->{'publishercode'},
690 volumedate = $biblioitem->{'volumedate'},
691 volumeddesc = $biblioitem->{'volumeddesc'},
692 illus = $biblioitem->{'illus'},
693 pages = $biblioitem->{'pages'},
694 notes = $biblioitem->{'notes'},
695 size = $biblioitem->{'size'},
696 lccn = $biblioitem->{'lccn'},
697 marc = $biblioitem->{'marc'},
698 place = $biblioitem->{'place'}";
700 $sth = $dbh->prepare($query);
711 my $query="insert into bibliosubject (biblionumber) values
713 my $sth=$dbh->prepare($query);
721 my ($bibnum, $subtitle) = @_;
723 $subtitle = $dbh->quote($subtitle);
724 my $query = "insert into bibliosubtitle set
725 biblionumber = $bibnum,
726 subtitle = $subtitle";
727 my $sth = $dbh->prepare($query);
736 my ($bibnum,$title,$ordnum,$basket,$quantity,$listprice,$supplier,$who,$notes,$bookfund,$bibitemnum,$rrp,$ecost,$gst,$budget,$cost,$sub,$invoice)=@_;
737 if ($budget eq 'now'){
740 $budget="'2001-07-01'";
748 my $query="insert into aqorders (biblionumber,title,basketno,
749 quantity,listprice,booksellerid,entrydate,requisitionedby,authorisedby,notes,
750 biblioitemnumber,rrp,ecost,gst,unitprice,subscription,booksellerinvoicenumber)
753 ($bibnum,'$title',$basket,$quantity,$listprice,'$supplier',now(),
754 '$who','$who','$notes',$bibitemnum,'$rrp','$ecost','$gst','$cost',
756 my $sth=$dbh->prepare($query);
760 $query="select * from aqorders where
761 biblionumber=$bibnum and basketno=$basket and ordernumber >=$ordnum";
762 $sth=$dbh->prepare($query);
764 my $data=$sth->fetchrow_hashref;
766 $ordnum=$data->{'ordernumber'};
767 $query="insert into aqorderbreakdown (ordernumber,bookfundid) values
768 ($ordnum,'$bookfund')";
769 $sth=$dbh->prepare($query);
777 my ($bibnum,$ordnum)=@_;
779 my $query="update aqorders set datecancellationprinted=now()
780 where biblionumber='$bibnum' and
781 ordernumber='$ordnum'";
782 my $sth=$dbh->prepare($query);
786 my $count=itemcount($bibnum);
794 my ($title,$ordnum,$quantity,$listprice,$bibnum,$basketno,$supplier,$who,$notes,$bookfund,$bibitemnum,$rrp,$ecost,$gst,$budget,$cost,$invoice)=@_;
796 my $query="update aqorders set title='$title',
797 quantity='$quantity',listprice='$listprice',basketno='$basketno',
798 rrp='$rrp',ecost='$ecost',unitprice='$cost',
799 booksellerinvoicenumber='$invoice'
801 ordernumber=$ordnum and biblionumber=$bibnum";
802 my $sth=$dbh->prepare($query);
806 $query="update aqorderbreakdown set bookfundid=$bookfund where
807 ordernumber=$ordnum";
808 $sth=$dbh->prepare($query);
817 my $query="Select max(ordernumber) from aqorders";
818 my $sth=$dbh->prepare($query);
820 my $data=$sth->fetchrow_arrayref;
821 my $ordnum=$$data[0];
829 my ($biblio,$ordnum,$quantrec,$user,$cost,$invoiceno,$bibitemno,$freight,$bookfund,$rrp)=@_;
831 my $query="update aqorders set quantityreceived='$quantrec',
832 datereceived=now(),booksellerinvoicenumber='$invoiceno',
833 biblioitemnumber=$bibitemno,unitprice='$cost',freight='$freight',
835 where biblionumber=$biblio and ordernumber=$ordnum
838 my $sth=$dbh->prepare($query);
841 $query="update aqorderbreakdown set bookfundid=$bookfund where
842 ordernumber=$ordnum";
843 $sth=$dbh->prepare($query);
850 my($biblio,$ordnum,$user,$cost,$bookfund,$rrp)=@_;
852 my $query="update aqorders set
853 unitprice='$cost', rrp='$rrp'
854 where biblionumber=$biblio and ordernumber=$ordnum
857 my $sth=$dbh->prepare($query);
860 $query="update aqorderbreakdown set bookfundid=$bookfund where
861 ordernumber=$ordnum";
862 $sth=$dbh->prepare($query);
870 my ($currency,$price)=@_;
872 my $query="Select rate from currency where currency='$currency'";
873 my $sth=$dbh->prepare($query);
875 my $data=$sth->fetchrow_hashref;
878 my $cur=$data->{'rate'};
882 $price=$price / $cur;
888 my $query="Select * from currency";
889 my $sth=$dbh->prepare($query);
893 while (my $data=$sth->fetchrow_hashref){
899 return($i,\@results);
905 my $query="Select * from currency where currency='$cur'";
906 my $sth=$dbh->prepare($query);
909 my $data=$sth->fetchrow_hashref;
915 sub updatecurrencies {
916 my ($currency,$rate)=@_;
918 my $query="update currency set rate=$rate where currency='$currency'";
919 my $sth=$dbh->prepare($query);
928 my $query="Update aqbooksellers set
929 name='$data->{'name'}',address1='$data->{'address1'}',address2='$data->{'address2'}',
930 address3='$data->{'address3'}',address4='$data->{'address4'}',postal='$data->{'postal'}',
931 phone='$data->{'phone'}',fax='$data->{'fax'}',url='$data->{'url'}',
932 contact='$data->{'contact'}',contpos='$data->{'contpos'}',
933 contphone='$data->{'contphone'}', contfax='$data->{'contfax'}', contaltphone=
934 '$data->{'contaltphone'}', contemail='$data->{'contemail'}', contnotes=
935 '$data->{'contnotes'}', active=$data->{'active'},
936 listprice='$data->{'listprice'}', invoiceprice='$data->{'invoiceprice'}',
937 gstreg=$data->{'gstreg'}, listincgst=$data->{'listincgst'},
938 invoiceincgst=$data->{'invoiceincgst'}, specialty='$data->{'specialty'}',
939 discount='$data->{'discount'}',invoicedisc='$data->{'invoicedisc'}',
940 nocalc='$data->{'nocalc'}'
941 where id='$data->{'id'}'";
942 my $sth=$dbh->prepare($query);
952 my $sth=$dbh->prepare("Select max(id) from aqbooksellers");
954 my $data2=$sth->fetchrow_hashref;
956 $data2->{'max(id)'}++;
957 $sth=$dbh->prepare("Insert into aqbooksellers (id) values ($data2->{'max(id)'})");
960 $data->{'id'}=$data2->{'max(id)'};
963 return($data->{'id'});
968 my ($item, @barcodes) = @_;
970 my $query = "Select max(itemnumber) from items";
971 my $sth = $dbh->prepare($query);
977 $data = $sth->fetchrow_hashref;
978 $itemnumber = $data->{'max(itemnumber)'} + 1;
981 $item->{'booksellerid'} = $dbh->quote($item->{'booksellerid'});
982 $item->{'homebranch'} = $dbh->quote($item->{'homebranch'});
983 $item->{'price'} = $dbh->quote($item->{'price'});
984 $item->{'replacementprice'} = $dbh->quote($item->{'replacementprice'});
985 $item->{'itemnotes'} = $dbh->quote($item->{'itemnotes'});
987 foreach my $barcode (@barcodes) {
988 $barcode = uc($barcode);
989 $barcode = $dbh->quote($barcode);
990 $query = "Insert into items set
991 itemnumber = $itemnumber,
992 biblionumber = $item->{'biblionumber'},
993 biblioitemnumber = $item->{'biblioitemnumber'},
995 booksellerid = $item->{'booksellerid'},
996 dateaccessioned = NOW(),
997 homebranch = $item->{'homebranch'},
998 holdingbranch = $item->{'homebranch'},
999 price = $item->{'price'},
1000 replacementprice = $item->{'replacementprice'},
1001 replacementpricedate = NOW(),
1002 itemnotes = $item->{'itemnotes'}";
1004 if ($item->{'loan'}) {
1006 notforloan = $item->{'loan'}";
1009 $sth = $dbh->prepare($query);
1012 $error .= $sth->errstr;
1023 my ($count,@barcodes)=@_;
1026 for (my $i=0;$i<$count;$i++){
1027 $barcodes[$i]=uc $barcodes[$i];
1028 my $query="Select * from items where barcode='$barcodes[$i]'";
1029 my $sth=$dbh->prepare($query);
1031 if (my $data=$sth->fetchrow_hashref){
1032 $error.=" Duplicate Barcode: $barcodes[$i]";
1041 my ($loan,$itemnum,$bibitemnum,$barcode,$notes,$homebranch,$lost,$wthdrawn,$replacement)=@_;
1043 my $query="update items set biblioitemnumber=$bibitemnum,
1044 barcode='$barcode',itemnotes='$notes'
1045 where itemnumber=$itemnum";
1046 if ($barcode eq ''){
1047 $query="update items set biblioitemnumber=$bibitemnum,notforloan=$loan where itemnumber=$itemnum";
1050 $query="update items set biblioitemnumber=$bibitemnum,
1051 barcode='$barcode',itemnotes='$notes',homebranch='$homebranch',
1052 itemlost='$lost',wthdrawn='$wthdrawn' where itemnumber=$itemnum";
1054 if ($replacement ne ''){
1055 $query=~ s/ where/,replacementprice='$replacement' where/;
1058 my $sth=$dbh->prepare($query);
1065 my($price,$rrp,$itemnum)=@_;
1067 my $query="update items set price='$price',replacementprice='$rrp'
1068 where itemnumber=$itemnum";
1069 my $sth=$dbh->prepare($query);
1075 my ($bibitemnum)=@_;
1077 my $query="Select count(*) from items where biblioitemnumber='$bibitemnum'";
1078 my $sth=$dbh->prepare($query);
1080 my $data=$sth->fetchrow_hashref;
1083 return($data->{'count(*)'});
1087 my ($biblionumber)=@_;
1089 my $query="Select * from biblioitems,items,itemtypes where
1090 biblioitems.biblionumber=$biblionumber
1091 and biblioitems.biblioitemnumber=items.biblioitemnumber and
1092 itemtypes.itemtype=biblioitems.itemtype
1093 order by items.biblioitemnumber";
1094 my $sth=$dbh->prepare($query);
1098 while (my $data=$sth->fetchrow_hashref){
1108 my ($bibitemnum,$itemtype)=@_;
1110 my $query="Select * from biblioitems where biblioitemnumber=$bibitemnum
1111 and itemtype='$itemtype'";
1112 my $sth=$dbh->prepare($query);
1115 if (my $data=$sth->fetchrow_hashref){
1126 my $query="select * from items where itemnumber=$itemnum";
1127 my $sth=$dbh->prepare($query);
1129 my @data=$sth->fetchrow_array;
1131 $query="Insert into deleteditems values (";
1132 foreach my $temp (@data){
1133 $query=$query."'$temp',";
1137 $sth=$dbh->prepare($query);
1140 $query = "Delete from items where itemnumber=$itemnum";
1141 $sth=$dbh->prepare($query);
1148 sub deletebiblioitem {
1149 my ($biblioitemnumber) = @_;
1150 my $dbh = C4Connect;
1151 my $query = "Select * from biblioitems
1152 where biblioitemnumber = $biblioitemnumber";
1153 my $sth = $dbh->prepare($query);
1158 if (@results = $sth->fetchrow_array) {
1160 $query = "Insert into deletedbiblioitems values (";
1161 foreach my $value (@results) {
1162 $value = $dbh->quote($value);
1163 $query .= "$value,";
1166 $query =~ s/\,$/\)/;
1169 $query = "Delete from biblioitems
1170 where biblioitemnumber = $biblioitemnumber";
1176 # Now delete all the items attached to the biblioitem
1178 $query = "Select * from items where biblioitemnumber = $biblioitemnumber";
1179 $sth = $dbh->prepare($query);
1183 while (@results = $sth->fetchrow_array) {
1185 $query = "Insert into deleteditems values (";
1186 foreach my $value (@results) {
1187 $value = $dbh->quote($value);
1188 $query .= "$value,";
1191 $query =~ s/\,$/\)/;
1197 $query = "Delete from items where biblioitemnumber = $biblioitemnumber";
1201 } # sub deletebiblioitem
1207 my $query="select * from biblio where biblionumber=$biblio";
1208 my $sth=$dbh->prepare($query);
1210 if (my @data=$sth->fetchrow_array){
1212 $query="Insert into deletedbiblio values (";
1213 foreach my $temp (@data){
1214 $temp=~ s/\'/\\\'/g;
1215 $query=$query."'$temp',";
1219 $sth=$dbh->prepare($query);
1222 $query = "Delete from biblio where biblionumber=$biblio";
1223 $sth=$dbh->prepare($query);
1234 my $dbh = C4Connect;
1235 my $query = "select * from itemtypes";
1236 my $sth = $dbh->prepare($query);
1237 # || die "Cannot prepare $query" . $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);
1251 } # sub getitemtypes
1255 my ($biblionumber) = @_;
1256 my $dbh = C4Connect;
1257 my $query = "Select * from biblio where biblionumber = $biblionumber";
1258 my $sth = $dbh->prepare($query);
1259 # || die "Cannot prepare $query\n" . $dbh->errstr;
1264 # || die "Cannot execute $query\n" . $sth->errstr;
1265 while (my $data = $sth->fetchrow_hashref) {
1266 $results[$count] = $data;
1272 return($count, @results);
1277 my ($biblioitemnum) = @_;
1278 my $dbh = C4Connect;
1279 my $query = "Select * from biblioitems where
1280 biblioitemnumber = $biblioitemnum";
1281 my $sth = $dbh->prepare($query);
1287 while (my $data = $sth->fetchrow_hashref) {
1288 $results[$count] = $data;
1294 return($count, @results);
1295 } # sub getbiblioitem
1298 sub getbiblioitembybiblionumber {
1299 my ($biblionumber) = @_;
1300 my $dbh = C4Connect;
1301 my $query = "Select * from biblioitems where biblionumber =
1303 my $sth = $dbh->prepare($query);
1309 while (my $data = $sth->fetchrow_hashref) {
1310 $results[$count] = $data;
1316 return($count, @results);
1320 sub getitemsbybiblioitem {
1321 my ($biblioitemnum) = @_;
1322 my $dbh = C4Connect;
1323 my $query = "Select * from items, biblio where
1324 biblio.biblionumber = items.biblionumber and biblioitemnumber
1326 my $sth = $dbh->prepare($query);
1327 # || die "Cannot prepare $query\n" . $dbh->errstr;
1332 # || die "Cannot execute $query\n" . $sth->errstr;
1333 while (my $data = $sth->fetchrow_hashref) {
1334 $results[$count] = $data;
1340 return($count, @results);
1341 } # sub getitemsbybiblioitem
1346 my $dbh = C4Connect;
1352 $isbn = $dbh->quote($isbn);
1353 $query = "Select biblio.* from biblio, biblioitems where
1354 biblio.biblionumber = biblioitems.biblionumber
1356 $sth = $dbh->prepare($query);
1359 while (my $data = $sth->fetchrow_hashref) {
1360 $results[$count] = $data;
1366 return($count, @results);
1371 my ($keywordlist) = @_;
1372 my $dbh = C4Connect;
1373 my $query = "Select distinct biblio.* from biblio, biblioitems where
1374 biblio.biblionumber = biblioitems.biblionumber and (";
1378 my @keywords = split(/ +/, $keywordlist);
1379 my $keyword = shift(@keywords);
1381 $keyword =~ s/%/\\%/g;
1382 $keyword =~ s/_/\\_/;
1383 $keyword = "%" . $keyword . "%";
1384 $keyword = $dbh->quote($keyword);
1385 $query .= " (url like $keyword)";
1387 foreach $keyword (@keywords) {
1388 $keyword =~ s/%/\\%/;
1389 $keyword =~ s/_/\\_/;
1390 $keyword = "%" . $keyword . "%";
1391 $keyword = $dbh->quote($keyword);
1392 $query .= " or (url like $keyword)";
1396 $sth = $dbh->prepare($query);
1399 while (my $data = $sth->fetchrow_hashref) {
1400 $results[$count] = $data;
1406 return($count, @results);
1407 } # sub websitesearch
1412 my $dbh = C4Connect;
1415 $website->{'biblionumber'} = $dbh->quote($website->{'biblionumber'});
1416 $website->{'title'} = $dbh->quote($website->{'title'});
1417 $website->{'description'} = $dbh->quote($website->{'description'});
1418 $website->{'url'} = $dbh->quote($website->{'url'});
1420 $query = "Insert into websites set
1421 biblionumber = $website->{'biblionumber'},
1422 title = $website->{'title'},
1423 description = $website->{'description'},
1424 url = $website->{'url'}";
1434 my $dbh = C4Connect;
1437 $website->{'title'} = $dbh->quote($website->{'title'});
1438 $website->{'description'} = $dbh->quote($website->{'description'});
1439 $website->{'url'} = $dbh->quote($website->{'url'});
1441 $query = "Update websites set
1442 title = $website->{'title'},
1443 description = $website->{'description'},
1444 url = $website->{'url'}
1445 where websitenumber = $website->{'websitenumber'}";
1450 } # sub updatewebsite
1454 my ($websitenumber) = @_;
1455 my $dbh = C4Connect;
1456 my $query = "Delete from websites where websitenumber = $websitenumber";
1461 } # sub deletewebsite
1464 END { } # module clean-up code here (global destructor)