1 package C4::Catalogue; #asummes C4/Acquisitions.pm
3 # Continue working on updateItem!!!!!!
5 # updateItem is looking not bad. Need to add addSubfield and deleteSubfield
8 # Trying to track down $dbh's that aren't disconnected....
13 # Copyright 2000-2002 Katipo Communications
15 # This file is part of Koha.
17 # Koha is free software; you can redistribute it and/or modify it under the
18 # terms of the GNU General Public License as published by the Free Software
19 # Foundation; either version 2 of the License, or (at your option) any later
22 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
23 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
24 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
26 # You should have received a copy of the GNU General Public License along with
27 # Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
28 # Suite 330, Boston, MA 02111-1307 USA
36 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
38 # set the version for version checking
45 &getorders &getallorders &getrecorders
46 &getorder &neworder &delorder
48 &modorder &getsingleorder &invoice &receiveorder
49 &updaterecorder &newordernum
51 &bookfunds &bookfundbreakdown &updatecost
52 &curconvert &getcurrencies &updatecurrencies &getcurrency
54 &findall &needsmod &branches &updatesup &insertsup
55 &bookseller &breakdown &checkitems
56 &websitesearch &addwebsite &updatewebsite &deletewebsite
58 %EXPORT_TAGS = ( ); # eg: TAG => [ qw!name1 name2! ],
60 # your exported package globals go here,
61 # as well as any optionally exported functions
63 @EXPORT_OK = qw($Var1 %Hashit);
66 # non-exported package globals go here
67 use vars qw(@more $stuff);
69 # initalize package globals, first exported ones
75 # then the others (which are still accessible as $Some::Module::stuff)
79 # all file-scoped lexicals must be created before
80 # the functions below that use them.
82 # file-private lexicals go here
86 # here's a file-private function as a closure,
87 # callable as &$priv_func; it cannot be prototyped.
92 # make all your functions, whether exported or not;
103 my ($basketno,$supplier)=@_;
105 my $query="Select *,biblio.title from aqorders,biblio,biblioitems
106 where basketno='$basketno'
107 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber
108 =aqorders.biblioitemnumber
109 and (datecancellationprinted is NULL or datecancellationprinted =
111 if ($supplier ne ''){
112 $query.=" and aqorders.booksellerid='$supplier'";
114 $query.=" group by aqorders.ordernumber";
115 my $sth=$dbh->prepare($query);
120 while (my $data=$sth->fetchrow_hashref){
131 my $query="Select max(basketno) from aqorders";
132 my $sth=$dbh->prepare($query);
134 my $data=$sth->fetchrow_arrayref;
135 my $basket=$$data[0];
142 my ($bibnum,$title,$ordnum,$basket,$quantity,$listprice,$supplier,$who,$notes,$bookfund,$bibitemnum,$rrp,$ecost,$gst,$budget,$cost,$sub,$invoice)=@_;
143 if ($budget eq 'now'){
146 $budget="'2001-07-01'";
154 my $query="insert into aqorders (biblionumber,title,basketno,
155 quantity,listprice,booksellerid,entrydate,requisitionedby,authorisedby,notes,
156 biblioitemnumber,rrp,ecost,gst,unitprice,subscription,booksellerinvoicenumber)
159 ($bibnum,'$title',$basket,$quantity,$listprice,'$supplier',now(),
160 '$who','$who','$notes',$bibitemnum,'$rrp','$ecost','$gst','$cost',
162 my $sth=$dbh->prepare($query);
166 $query="select * from aqorders where
167 biblionumber=$bibnum and basketno=$basket and ordernumber >=$ordnum";
168 $sth=$dbh->prepare($query);
170 my $data=$sth->fetchrow_hashref;
172 $ordnum=$data->{'ordernumber'};
173 $query="insert into aqorderbreakdown (ordernumber,bookfundid) values
174 ($ordnum,'$bookfund')";
175 $sth=$dbh->prepare($query);
183 my ($bibnum,$ordnum)=@_;
185 my $query="update aqorders set datecancellationprinted=now()
186 where biblionumber='$bibnum' and
187 ordernumber='$ordnum'";
188 my $sth=$dbh->prepare($query);
192 my $count=itemcount($bibnum);
200 my ($title,$ordnum,$quantity,$listprice,$bibnum,$basketno,$supplier,$who,$notes,$bookfund,$bibitemnum,$rrp,$ecost,$gst,$budget,$cost,$invoice)=@_;
202 my $query="update aqorders set title='$title',
203 quantity='$quantity',listprice='$listprice',basketno='$basketno',
204 rrp='$rrp',ecost='$ecost',unitprice='$cost',
205 booksellerinvoicenumber='$invoice'
207 ordernumber=$ordnum and biblionumber=$bibnum";
208 my $sth=$dbh->prepare($query);
212 $query="update aqorderbreakdown set bookfundid=$bookfund where
213 ordernumber=$ordnum";
214 $sth=$dbh->prepare($query);
223 my $query="Select max(ordernumber) from aqorders";
224 my $sth=$dbh->prepare($query);
226 my $data=$sth->fetchrow_arrayref;
227 my $ordnum=$$data[0];
235 my ($biblio,$ordnum,$quantrec,$user,$cost,$invoiceno,$bibitemno,$freight,$bookfund,$rrp)=@_;
237 my $query="update aqorders set quantityreceived='$quantrec',
238 datereceived=now(),booksellerinvoicenumber='$invoiceno',
239 biblioitemnumber=$bibitemno,unitprice='$cost',freight='$freight',
241 where biblionumber=$biblio and ordernumber=$ordnum
244 my $sth=$dbh->prepare($query);
247 $query="update aqorderbreakdown set bookfundid=$bookfund where
248 ordernumber=$ordnum";
249 $sth=$dbh->prepare($query);
256 my($biblio,$ordnum,$user,$cost,$bookfund,$rrp)=@_;
258 my $query="update aqorders set
259 unitprice='$cost', rrp='$rrp'
260 where biblionumber=$biblio and ordernumber=$ordnum
263 my $sth=$dbh->prepare($query);
266 $query="update aqorderbreakdown set bookfundid=$bookfund where
267 ordernumber=$ordnum";
268 $sth=$dbh->prepare($query);
284 my $query = "Select count(*),authorisedby,entrydate,basketno from aqorders where
285 booksellerid='$supplierid' and (quantity > quantityreceived or
286 quantityreceived is NULL)
287 and (datecancellationprinted is NULL or datecancellationprinted = '0000-00-00')";
288 $query.=" group by basketno order by entrydate desc";
290 my $sth=$dbh->prepare($query);
294 while (my $data=$sth->fetchrow_hashref){
300 return ($i,\@results);
306 my $query="Select ordernumber from aqorders where biblionumber=$bib and
307 biblioitemnumber='$bi'";
308 my $sth=$dbh->prepare($query);
310 my $ordnum=$sth->fetchrow_hashref;
312 my $order=getsingleorder($ordnum->{'ordernumber'});
315 return ($order,$ordnum->{'ordernumber'});
321 my $query="Select * from biblio,biblioitems,aqorders,aqorderbreakdown
322 where aqorders.ordernumber='$ordnum'
323 and biblio.biblionumber=aqorders.biblionumber and
324 biblioitems.biblioitemnumber=aqorders.biblioitemnumber and
325 aqorders.ordernumber=aqorderbreakdown.ordernumber";
326 my $sth=$dbh->prepare($query);
328 my $data=$sth->fetchrow_hashref;
335 #gets all orders from a certain supplier, orders them alphabetically
338 my $query="Select * from aqorders,biblio,biblioitems where booksellerid='$supid'
339 and (cancelledby is NULL or cancelledby = '')
340 and (quantityreceived < quantity or quantityreceived is NULL)
341 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber=
342 aqorders.biblioitemnumber
343 group by aqorders.biblioitemnumber
348 my $sth=$dbh->prepare($query);
350 while (my $data=$sth->fetchrow_hashref){
360 #gets all orders from a certain supplier, orders them alphabetically
363 my $query="Select * from aqorders,biblio,biblioitems where booksellerid='$supid'
364 and (cancelledby is NULL or cancelledby = '')
365 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber=
366 aqorders.biblioitemnumber and
367 aqorders.quantityreceived>0
368 and aqorders.datereceived >=now()
369 group by aqorders.biblioitemnumber
374 my $sth=$dbh->prepare($query);
376 while (my $data=$sth->fetchrow_hashref){
386 my ($search,$biblio,$catview) = @_;
388 my $query = "Select *,biblio.title from aqorders,biblioitems,biblio
389 where aqorders.biblioitemnumber = biblioitems.biblioitemnumber
390 and biblio.biblionumber=aqorders.biblionumber
391 and ((datecancellationprinted is NULL)
392 or (datecancellationprinted = '0000-00-00'))
394 my @data = split(' ',$search);
396 for (my $i = 0; $i < $count; $i++) {
397 $query .= "(biblio.title like '$data[$i]%' or biblio.title like '% $data[$i]%') and ";
400 $query.=" ) or biblioitems.isbn='$search'
401 or (aqorders.ordernumber='$search' and aqorders.biblionumber='$biblio')) ";
402 if ($catview ne 'yes'){
403 $query.=" and (quantityreceived < quantity or quantityreceived is NULL)";
405 $query.=" group by aqorders.ordernumber";
406 my $sth=$dbh->prepare($query);
410 while (my $data=$sth->fetchrow_hashref){
411 my $sth2=$dbh->prepare("Select * from biblio where
412 biblionumber='$data->{'biblionumber'}'");
414 my $data2=$sth2->fetchrow_hashref;
416 $data->{'author'}=$data2->{'author'};
417 $data->{'seriestitle'}=$data2->{'seriestitle'};
418 $sth2=$dbh->prepare("Select * from aqorderbreakdown where
419 ordernumber=$data->{'ordernumber'}");
421 $data2=$sth2->fetchrow_hashref;
423 $data->{'branchcode'}=$data2->{'branchcode'};
424 $data->{'bookfundid'}=$data2->{'bookfundid'};
441 my $query="Select * from aqorders,biblio,biblioitems where
442 booksellerinvoicenumber='$invoice'
443 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber=
444 aqorders.biblioitemnumber group by aqorders.ordernumber,aqorders.biblioitemnumber";
447 my $sth=$dbh->prepare($query);
449 while (my $data=$sth->fetchrow_hashref){
460 my $query="Select * from aqbookfund,aqbudget where aqbookfund.bookfundid
462 group by aqbookfund.bookfundid order by bookfundname";
463 my $sth=$dbh->prepare($query);
467 while (my $data=$sth->fetchrow_hashref){
476 sub bookfundbreakdown {
479 my $query="Select quantity,datereceived,freight,unitprice,listprice,ecost,quantityreceived,subscription
480 from aqorders,aqorderbreakdown where bookfundid='$id' and
481 aqorders.ordernumber=aqorderbreakdown.ordernumber
482 and (datecancellationprinted is NULL or
483 datecancellationprinted='0000-00-00')";
484 my $sth=$dbh->prepare($query);
488 while (my $data=$sth->fetchrow_hashref){
489 if ($data->{'subscription'} == 1){
490 $spent+=$data->{'quantity'}*$data->{'unitprice'};
492 my $leftover=$data->{'quantity'}-$data->{'quantityreceived'};
493 $comtd+=($data->{'ecost'})*$leftover;
494 $spent+=($data->{'unitprice'})*$data->{'quantityreceived'};
499 return($spent,$comtd);
503 my ($currency,$price)=@_;
505 my $query="Select rate from currency where currency='$currency'";
506 my $sth=$dbh->prepare($query);
508 my $data=$sth->fetchrow_hashref;
511 my $cur=$data->{'rate'};
515 my $price=$price / $cur;
521 my $query="Select * from currency";
522 my $sth=$dbh->prepare($query);
526 while (my $data=$sth->fetchrow_hashref){
532 return($i,\@results);
538 my $query="Select * from currency where currency='$cur'";
539 my $sth=$dbh->prepare($query);
542 my $data=$sth->fetchrow_hashref;
548 sub updatecurrencies {
549 my ($currency,$rate)=@_;
551 my $query="update currency set rate=$rate where currency='$currency'";
552 my $sth=$dbh->prepare($query);
559 my($price,$rrp,$itemnum)=@_;
561 my $query="update items set price='$price',replacementprice='$rrp'
562 where itemnumber=$itemnum";
563 my $sth=$dbh->prepare($query);
576 my ($searchstring)=@_;
578 my $query="Select * from aqbooksellers where name like '%$searchstring%' or
579 id = '$searchstring'";
580 my $sth=$dbh->prepare($query);
584 while (my $data=$sth->fetchrow_hashref){
595 my $query="Select * from aqorderbreakdown where ordernumber='$id'";
596 my $sth=$dbh->prepare($query);
600 while (my $data=$sth->fetchrow_hashref){
606 return($i,\@results);
611 my $query = "Select * from branches";
612 my $sth = $dbh->prepare($query);
617 while (my $data = $sth->fetchrow_hashref) {
618 $results[$i] = $data;
624 return($i, @results);
628 my ($biblionumber)=@_;
630 my $query="Select * from biblioitems,items,itemtypes where
631 biblioitems.biblionumber=$biblionumber
632 and biblioitems.biblioitemnumber=items.biblioitemnumber and
633 itemtypes.itemtype=biblioitems.itemtype
634 order by items.biblioitemnumber";
635 my $sth=$dbh->prepare($query);
639 while (my $data=$sth->fetchrow_hashref){
649 my ($bibitemnum,$itemtype)=@_;
651 my $query="Select * from biblioitems where biblioitemnumber=$bibitemnum
652 and itemtype='$itemtype'";
653 my $sth=$dbh->prepare($query);
656 if (my $data=$sth->fetchrow_hashref){
667 my $query="Update aqbooksellers set
668 name='$data->{'name'}',address1='$data->{'address1'}',address2='$data->{'address2'}',
669 address3='$data->{'address3'}',address4='$data->{'address4'}',postal='$data->{'postal'}',
670 phone='$data->{'phone'}',fax='$data->{'fax'}',url='$data->{'url'}',
671 contact='$data->{'contact'}',contpos='$data->{'contpos'}',
672 contphone='$data->{'contphone'}', contfax='$data->{'contfax'}', contaltphone=
673 '$data->{'contaltphone'}', contemail='$data->{'contemail'}', contnotes=
674 '$data->{'contnotes'}', active=$data->{'active'},
675 listprice='$data->{'listprice'}', invoiceprice='$data->{'invoiceprice'}',
676 gstreg=$data->{'gstreg'}, listincgst=$data->{'listincgst'},
677 invoiceincgst=$data->{'invoiceincgst'}, specialty='$data->{'specialty'}',
678 discount='$data->{'discount'}',invoicedisc='$data->{'invoicedisc'}',
679 nocalc='$data->{'nocalc'}'
680 where id='$data->{'id'}'";
681 my $sth=$dbh->prepare($query);
691 my $sth=$dbh->prepare("Select max(id) from aqbooksellers");
693 my $data2=$sth->fetchrow_hashref;
695 $data2->{'max(id)'}++;
696 $sth=$dbh->prepare("Insert into aqbooksellers (id) values ($data2->{'max(id)'})");
699 $data->{'id'}=$data2->{'max(id)'};
702 return($data->{'id'});
706 my ($keywordlist) = @_;
708 my $query = "Select distinct biblio.* from biblio, biblioitems where
709 biblio.biblionumber = biblioitems.biblionumber and (";
713 my @keywords = split(/ +/, $keywordlist);
714 my $keyword = shift(@keywords);
716 $keyword =~ s/%/\\%/g;
717 $keyword =~ s/_/\\_/;
718 $keyword = "%" . $keyword . "%";
719 $keyword = $dbh->quote($keyword);
720 $query .= " (url like $keyword)";
722 foreach $keyword (@keywords) {
723 $keyword =~ s/%/\\%/;
724 $keyword =~ s/_/\\_/;
725 $keyword = "%" . $keyword . "%";
726 $keyword = $dbh->quote($keyword);
727 $query .= " or (url like $keyword)";
731 $sth = $dbh->prepare($query);
734 while (my $data = $sth->fetchrow_hashref) {
735 $results[$count] = $data;
741 return($count, @results);
742 } # sub websitesearch
750 $website->{'biblionumber'} = $dbh->quote($website->{'biblionumber'});
751 $website->{'title'} = $dbh->quote($website->{'title'});
752 $website->{'description'} = $dbh->quote($website->{'description'});
753 $website->{'url'} = $dbh->quote($website->{'url'});
755 $query = "Insert into websites set
756 biblionumber = $website->{'biblionumber'},
757 title = $website->{'title'},
758 description = $website->{'description'},
759 url = $website->{'url'}";
772 $website->{'title'} = $dbh->quote($website->{'title'});
773 $website->{'description'} = $dbh->quote($website->{'description'});
774 $website->{'url'} = $dbh->quote($website->{'url'});
776 $query = "Update websites set
777 title = $website->{'title'},
778 description = $website->{'description'},
779 url = $website->{'url'}
780 where websitenumber = $website->{'websitenumber'}";
785 } # sub updatewebsite
789 my ($websitenumber) = @_;
791 my $query = "Delete from websites where websitenumber = $websitenumber";
796 } # sub deletewebsite
798 END { } # module clean-up code here (global destructor)