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....
18 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
20 # set the version for version checking
27 &getorders &getallorders &getrecorders
28 &getorder &neworder &delorder
30 &modorder &getsingleorder &invoice &receiveorder
31 &updaterecorder &newordernum
33 &bookfunds &bookfundbreakdown &updatecost
34 &curconvert &getcurrencies &updatecurrencies &getcurrency
36 &findall &needsmod &branches &updatesup &insertsup
37 &bookseller &breakdown &checkitems
38 &websitesearch &addwebsite &updatewebsite &deletewebsite
40 %EXPORT_TAGS = ( ); # eg: TAG => [ qw!name1 name2! ],
42 # your exported package globals go here,
43 # as well as any optionally exported functions
45 @EXPORT_OK = qw($Var1 %Hashit);
48 # non-exported package globals go here
49 use vars qw(@more $stuff);
51 # initalize package globals, first exported ones
57 # then the others (which are still accessible as $Some::Module::stuff)
61 # all file-scoped lexicals must be created before
62 # the functions below that use them.
64 # file-private lexicals go here
68 # here's a file-private function as a closure,
69 # callable as &$priv_func; it cannot be prototyped.
74 # make all your functions, whether exported or not;
85 my ($basketno,$supplier)=@_;
87 my $query="Select *,biblio.title from aqorders,biblio,biblioitems
88 where basketno='$basketno'
89 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber
90 =aqorders.biblioitemnumber
91 and (datecancellationprinted is NULL or datecancellationprinted =
94 $query.=" and aqorders.booksellerid='$supplier'";
96 $query.=" group by aqorders.ordernumber";
97 my $sth=$dbh->prepare($query);
102 while (my $data=$sth->fetchrow_hashref){
113 my $query="Select max(basketno) from aqorders";
114 my $sth=$dbh->prepare($query);
116 my $data=$sth->fetchrow_arrayref;
117 my $basket=$$data[0];
124 my ($bibnum,$title,$ordnum,$basket,$quantity,$listprice,$supplier,$who,$notes,$bookfund,$bibitemnum,$rrp,$ecost,$gst,$budget,$cost,$sub,$invoice)=@_;
125 if ($budget eq 'now'){
128 $budget="'2001-07-01'";
136 my $query="insert into aqorders (biblionumber,title,basketno,
137 quantity,listprice,booksellerid,entrydate,requisitionedby,authorisedby,notes,
138 biblioitemnumber,rrp,ecost,gst,unitprice,subscription,booksellerinvoicenumber)
141 ($bibnum,'$title',$basket,$quantity,$listprice,'$supplier',now(),
142 '$who','$who','$notes',$bibitemnum,'$rrp','$ecost','$gst','$cost',
144 my $sth=$dbh->prepare($query);
148 $query="select * from aqorders where
149 biblionumber=$bibnum and basketno=$basket and ordernumber >=$ordnum";
150 $sth=$dbh->prepare($query);
152 my $data=$sth->fetchrow_hashref;
154 $ordnum=$data->{'ordernumber'};
155 $query="insert into aqorderbreakdown (ordernumber,bookfundid) values
156 ($ordnum,'$bookfund')";
157 $sth=$dbh->prepare($query);
165 my ($bibnum,$ordnum)=@_;
167 my $query="update aqorders set datecancellationprinted=now()
168 where biblionumber='$bibnum' and
169 ordernumber='$ordnum'";
170 my $sth=$dbh->prepare($query);
174 my $count=itemcount($bibnum);
182 my ($title,$ordnum,$quantity,$listprice,$bibnum,$basketno,$supplier,$who,$notes,$bookfund,$bibitemnum,$rrp,$ecost,$gst,$budget,$cost,$invoice)=@_;
184 my $query="update aqorders set title='$title',
185 quantity='$quantity',listprice='$listprice',basketno='$basketno',
186 rrp='$rrp',ecost='$ecost',unitprice='$cost',
187 booksellerinvoicenumber='$invoice'
189 ordernumber=$ordnum and biblionumber=$bibnum";
190 my $sth=$dbh->prepare($query);
194 $query="update aqorderbreakdown set bookfundid=$bookfund where
195 ordernumber=$ordnum";
196 $sth=$dbh->prepare($query);
205 my $query="Select max(ordernumber) from aqorders";
206 my $sth=$dbh->prepare($query);
208 my $data=$sth->fetchrow_arrayref;
209 my $ordnum=$$data[0];
217 my ($biblio,$ordnum,$quantrec,$user,$cost,$invoiceno,$bibitemno,$freight,$bookfund,$rrp)=@_;
219 my $query="update aqorders set quantityreceived='$quantrec',
220 datereceived=now(),booksellerinvoicenumber='$invoiceno',
221 biblioitemnumber=$bibitemno,unitprice='$cost',freight='$freight',
223 where biblionumber=$biblio and ordernumber=$ordnum
226 my $sth=$dbh->prepare($query);
229 $query="update aqorderbreakdown set bookfundid=$bookfund where
230 ordernumber=$ordnum";
231 $sth=$dbh->prepare($query);
238 my($biblio,$ordnum,$user,$cost,$bookfund,$rrp)=@_;
240 my $query="update aqorders set
241 unitprice='$cost', rrp='$rrp'
242 where biblionumber=$biblio and ordernumber=$ordnum
245 my $sth=$dbh->prepare($query);
248 $query="update aqorderbreakdown set bookfundid=$bookfund where
249 ordernumber=$ordnum";
250 $sth=$dbh->prepare($query);
266 my $query = "Select count(*),authorisedby,entrydate,basketno from aqorders where
267 booksellerid='$supplierid' and (quantity > quantityreceived or
268 quantityreceived is NULL)
269 and (datecancellationprinted is NULL or datecancellationprinted = '0000-00-00')";
270 $query.=" group by basketno order by entrydate desc";
272 my $sth=$dbh->prepare($query);
276 while (my $data=$sth->fetchrow_hashref){
282 return ($i,\@results);
288 my $query="Select ordernumber from aqorders where biblionumber=$bib and
289 biblioitemnumber='$bi'";
290 my $sth=$dbh->prepare($query);
292 my $ordnum=$sth->fetchrow_hashref;
294 my $order=getsingleorder($ordnum->{'ordernumber'});
297 return ($order,$ordnum->{'ordernumber'});
303 my $query="Select * from biblio,biblioitems,aqorders,aqorderbreakdown
304 where aqorders.ordernumber='$ordnum'
305 and biblio.biblionumber=aqorders.biblionumber and
306 biblioitems.biblioitemnumber=aqorders.biblioitemnumber and
307 aqorders.ordernumber=aqorderbreakdown.ordernumber";
308 my $sth=$dbh->prepare($query);
310 my $data=$sth->fetchrow_hashref;
317 #gets all orders from a certain supplier, orders them alphabetically
320 my $query="Select * from aqorders,biblio,biblioitems where booksellerid='$supid'
321 and (cancelledby is NULL or cancelledby = '')
322 and (quantityreceived < quantity or quantityreceived is NULL)
323 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber=
324 aqorders.biblioitemnumber
325 group by aqorders.biblioitemnumber
330 my $sth=$dbh->prepare($query);
332 while (my $data=$sth->fetchrow_hashref){
342 #gets all orders from a certain supplier, orders them alphabetically
345 my $query="Select * from aqorders,biblio,biblioitems where booksellerid='$supid'
346 and (cancelledby is NULL or cancelledby = '')
347 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber=
348 aqorders.biblioitemnumber and
349 aqorders.quantityreceived>0
350 and aqorders.datereceived >=now()
351 group by aqorders.biblioitemnumber
356 my $sth=$dbh->prepare($query);
358 while (my $data=$sth->fetchrow_hashref){
368 my ($search,$biblio,$catview) = @_;
370 my $query = "Select *,biblio.title from aqorders,biblioitems,biblio
371 where aqorders.biblioitemnumber = biblioitems.biblioitemnumber
372 and biblio.biblionumber=aqorders.biblionumber
373 and ((datecancellationprinted is NULL)
374 or (datecancellationprinted = '0000-00-00'))
376 my @data = split(' ',$search);
378 for (my $i = 0; $i < $count; $i++) {
379 $query .= "(biblio.title like '$data[$i]%' or biblio.title like '% $data[$i]%') and ";
382 $query.=" ) or biblioitems.isbn='$search'
383 or (aqorders.ordernumber='$search' and aqorders.biblionumber='$biblio')) ";
384 if ($catview ne 'yes'){
385 $query.=" and (quantityreceived < quantity or quantityreceived is NULL)";
387 $query.=" group by aqorders.ordernumber";
388 my $sth=$dbh->prepare($query);
392 while (my $data=$sth->fetchrow_hashref){
393 my $sth2=$dbh->prepare("Select * from biblio where
394 biblionumber='$data->{'biblionumber'}'");
396 my $data2=$sth2->fetchrow_hashref;
398 $data->{'author'}=$data2->{'author'};
399 $data->{'seriestitle'}=$data2->{'seriestitle'};
400 $sth2=$dbh->prepare("Select * from aqorderbreakdown where
401 ordernumber=$data->{'ordernumber'}");
403 $data2=$sth2->fetchrow_hashref;
405 $data->{'branchcode'}=$data2->{'branchcode'};
406 $data->{'bookfundid'}=$data2->{'bookfundid'};
423 my $query="Select * from aqorders,biblio,biblioitems where
424 booksellerinvoicenumber='$invoice'
425 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber=
426 aqorders.biblioitemnumber group by aqorders.ordernumber,aqorders.biblioitemnumber";
429 my $sth=$dbh->prepare($query);
431 while (my $data=$sth->fetchrow_hashref){
442 my $query="Select * from aqbookfund,aqbudget where aqbookfund.bookfundid
444 group by aqbookfund.bookfundid order by bookfundname";
445 my $sth=$dbh->prepare($query);
449 while (my $data=$sth->fetchrow_hashref){
458 sub bookfundbreakdown {
461 my $query="Select quantity,datereceived,freight,unitprice,listprice,ecost,quantityreceived,subscription
462 from aqorders,aqorderbreakdown where bookfundid='$id' and
463 aqorders.ordernumber=aqorderbreakdown.ordernumber
464 and (datecancellationprinted is NULL or
465 datecancellationprinted='0000-00-00')";
466 my $sth=$dbh->prepare($query);
470 while (my $data=$sth->fetchrow_hashref){
471 if ($data->{'subscription'} == 1){
472 $spent+=$data->{'quantity'}*$data->{'unitprice'};
474 my $leftover=$data->{'quantity'}-$data->{'quantityreceived'};
475 $comtd+=($data->{'ecost'})*$leftover;
476 $spent+=($data->{'unitprice'})*$data->{'quantityreceived'};
481 return($spent,$comtd);
485 my ($currency,$price)=@_;
487 my $query="Select rate from currency where currency='$currency'";
488 my $sth=$dbh->prepare($query);
490 my $data=$sth->fetchrow_hashref;
493 my $cur=$data->{'rate'};
497 my $price=$price / $cur;
503 my $query="Select * from currency";
504 my $sth=$dbh->prepare($query);
508 while (my $data=$sth->fetchrow_hashref){
514 return($i,\@results);
520 my $query="Select * from currency where currency='$cur'";
521 my $sth=$dbh->prepare($query);
524 my $data=$sth->fetchrow_hashref;
530 sub updatecurrencies {
531 my ($currency,$rate)=@_;
533 my $query="update currency set rate=$rate where currency='$currency'";
534 my $sth=$dbh->prepare($query);
541 my($price,$rrp,$itemnum)=@_;
543 my $query="update items set price='$price',replacementprice='$rrp'
544 where itemnumber=$itemnum";
545 my $sth=$dbh->prepare($query);
558 my ($searchstring)=@_;
560 my $query="Select * from aqbooksellers where name like '%$searchstring%' or
561 id = '$searchstring'";
562 my $sth=$dbh->prepare($query);
566 while (my $data=$sth->fetchrow_hashref){
577 my $query="Select * from aqorderbreakdown where ordernumber='$id'";
578 my $sth=$dbh->prepare($query);
582 while (my $data=$sth->fetchrow_hashref){
588 return($i,\@results);
593 my $query = "Select * from branches";
594 my $sth = $dbh->prepare($query);
599 while (my $data = $sth->fetchrow_hashref) {
600 $results[$i] = $data;
606 return($i, @results);
610 my ($biblionumber)=@_;
612 my $query="Select * from biblioitems,items,itemtypes where
613 biblioitems.biblionumber=$biblionumber
614 and biblioitems.biblioitemnumber=items.biblioitemnumber and
615 itemtypes.itemtype=biblioitems.itemtype
616 order by items.biblioitemnumber";
617 my $sth=$dbh->prepare($query);
621 while (my $data=$sth->fetchrow_hashref){
631 my ($bibitemnum,$itemtype)=@_;
633 my $query="Select * from biblioitems where biblioitemnumber=$bibitemnum
634 and itemtype='$itemtype'";
635 my $sth=$dbh->prepare($query);
638 if (my $data=$sth->fetchrow_hashref){
649 my $query="Update aqbooksellers set
650 name='$data->{'name'}',address1='$data->{'address1'}',address2='$data->{'address2'}',
651 address3='$data->{'address3'}',address4='$data->{'address4'}',postal='$data->{'postal'}',
652 phone='$data->{'phone'}',fax='$data->{'fax'}',url='$data->{'url'}',
653 contact='$data->{'contact'}',contpos='$data->{'contpos'}',
654 contphone='$data->{'contphone'}', contfax='$data->{'contfax'}', contaltphone=
655 '$data->{'contaltphone'}', contemail='$data->{'contemail'}', contnotes=
656 '$data->{'contnotes'}', active=$data->{'active'},
657 listprice='$data->{'listprice'}', invoiceprice='$data->{'invoiceprice'}',
658 gstreg=$data->{'gstreg'}, listincgst=$data->{'listincgst'},
659 invoiceincgst=$data->{'invoiceincgst'}, specialty='$data->{'specialty'}',
660 discount='$data->{'discount'}',invoicedisc='$data->{'invoicedisc'}',
661 nocalc='$data->{'nocalc'}'
662 where id='$data->{'id'}'";
663 my $sth=$dbh->prepare($query);
673 my $sth=$dbh->prepare("Select max(id) from aqbooksellers");
675 my $data2=$sth->fetchrow_hashref;
677 $data2->{'max(id)'}++;
678 $sth=$dbh->prepare("Insert into aqbooksellers (id) values ($data2->{'max(id)'})");
681 $data->{'id'}=$data2->{'max(id)'};
684 return($data->{'id'});
688 my ($keywordlist) = @_;
690 my $query = "Select distinct biblio.* from biblio, biblioitems where
691 biblio.biblionumber = biblioitems.biblionumber and (";
695 my @keywords = split(/ +/, $keywordlist);
696 my $keyword = shift(@keywords);
698 $keyword =~ s/%/\\%/g;
699 $keyword =~ s/_/\\_/;
700 $keyword = "%" . $keyword . "%";
701 $keyword = $dbh->quote($keyword);
702 $query .= " (url like $keyword)";
704 foreach $keyword (@keywords) {
705 $keyword =~ s/%/\\%/;
706 $keyword =~ s/_/\\_/;
707 $keyword = "%" . $keyword . "%";
708 $keyword = $dbh->quote($keyword);
709 $query .= " or (url like $keyword)";
713 $sth = $dbh->prepare($query);
716 while (my $data = $sth->fetchrow_hashref) {
717 $results[$count] = $data;
723 return($count, @results);
724 } # sub websitesearch
732 $website->{'biblionumber'} = $dbh->quote($website->{'biblionumber'});
733 $website->{'title'} = $dbh->quote($website->{'title'});
734 $website->{'description'} = $dbh->quote($website->{'description'});
735 $website->{'url'} = $dbh->quote($website->{'url'});
737 $query = "Insert into websites set
738 biblionumber = $website->{'biblionumber'},
739 title = $website->{'title'},
740 description = $website->{'description'},
741 url = $website->{'url'}";
754 $website->{'title'} = $dbh->quote($website->{'title'});
755 $website->{'description'} = $dbh->quote($website->{'description'});
756 $website->{'url'} = $dbh->quote($website->{'url'});
758 $query = "Update websites set
759 title = $website->{'title'},
760 description = $website->{'description'},
761 url = $website->{'url'}
762 where websitenumber = $website->{'websitenumber'}";
767 } # sub updatewebsite
771 my ($websitenumber) = @_;
773 my $query = "Delete from websites where websitenumber = $websitenumber";
778 } # sub deletewebsite
780 END { } # module clean-up code here (global destructor)