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....
16 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
18 # set the version for version checking
22 @EXPORT = qw(&newBiblio &newBiblioItem &newItem &updateBiblio &updateBiblioItem
23 &updateItem &changeSubfield &addSubfield &findSubfield
24 &addMarcBiblio &nextsubfieldid
26 &getorders &bookseller &breakdown &basket &newbasket &bookfunds
27 &ordersearch &newbiblio &newbiblioitem &newsubject &newsubtitle &neworder
28 &newordernum &modbiblio &modorder &getsingleorder &invoice &receiveorder
29 &bookfundbreakdown &curconvert &updatesup &insertsup &newitems &modbibitem
30 &getcurrencies &modsubtitle &modsubject &modaddauthor &moditem &countitems
31 &findall &needsmod &delitem &deletebiblioitem &delbiblio &delorder &branches
32 &getallorders &getrecorders &updatecurrencies &getorder &getcurrency &updaterecorder
33 &updatecost &checkitems &modnote &getitemtypes &getbiblio
34 &getbiblioitembybiblionumber
35 &getbiblioitem &getitemsbybiblioitem &isbnsearch
36 &websitesearch &addwebsite &updatewebsite &deletewebsite);
37 %EXPORT_TAGS = ( ); # eg: TAG => [ qw!name1 name2! ],
39 # your exported package globals go here,
40 # as well as any optionally exported functions
42 @EXPORT_OK = qw($Var1 %Hashit);
45 # non-exported package globals go here
46 use vars qw(@more $stuff);
48 # initalize package globals, first exported ones
54 # then the others (which are still accessible as $Some::Module::stuff)
58 # all file-scoped lexicals must be created before
59 # the functions below that use them.
61 # file-private lexicals go here
65 # here's a file-private function as a closure,
66 # callable as &$priv_func; it cannot be prototyped.
71 # make all your functions, whether exported or not;
76 # This subroutine makes no modifications to the MARC tables. MARC records are
77 # only created when new biblioitems are added.
78 my ($env, $biblio) = @_;
80 my $subject=$biblio->{'subject'};
81 my $additionalauthors=$biblio->{'additionalauthors'};
83 # Why am I doing this? This is a potential race condition. At the very least,
84 # this needs code to ensure that two inserts didn't use the same
87 # Get next biblio number
88 my $sth=$dbh->prepare("select max(biblionumber) from biblio");
90 my ($biblionumber) = $sth->fetchrow;
93 $sth=$dbh->prepare("insert into biblio
94 (biblionumber,title,author,
95 unititle,copyrightdate,
96 serial,seriestitle,notes)
97 values (?, ?, ?, ?, ?, ?, ?, ?)");
98 $sth->execute($biblionumber, $biblio->{'title'}, $biblio->{'author'},
99 $biblio->{'unititle'}, $biblio->{'copyrightdate'},
100 $biblio->{'serial'}, $biblio->{'seriestitle'}, $biblio->{'notes'} );
101 $sth=$dbh->prepare("insert into bibliosubtitle
102 (biblionumber,subtitle)
104 $sth->execute($biblionumber, $biblio->{'subtitle'} );
106 my $sth=$dbh->prepare("insert into bibliosubject
107 (biblionumber,subject)
109 foreach $_ (@$subject) {
110 $sth->execute($biblionumber,$_);
112 my $sth=$dbh->prepare("insert into additionalauthors
113 (biblionumber,author)
115 foreach $_ (@$additionalauthors) {
116 $sth->execute($biblionumber, $_ );
122 # Subroutine changes a subfield value given a subfieldid.
123 my ( $subfieldid, $subfieldvalue )=@_;
126 $dbh->do("lock tables marc_blob_subfield WRITE,marc_subfield_table WRITE");
127 my $sth1=$dbh->prepare("select valuebloblink from marc_subfield_table where subfieldid=?");
128 $sth1->execute($subfieldid);
129 my ($oldvaluebloblink)=$sth1->fetchrow;
132 # if too long, use a bloblink
133 if (length($subfieldvalue)>255 ) {
134 # if already a bloblink, update it, otherwise, insert a new one.
135 if ($oldvaluebloblink) {
136 $sth=$dbh->prepare("update marc_blob_subfield set subfieldvalue=? where blobidlink=?");
137 $sth->execute($subfieldvalue,$oldvaluebloblink);
139 $sth=$dbh->prepare("insert into marc_blob_subfield (subfieldvalue) values (?)");
140 $sth->execute($subfieldvalue);
141 $sth=$dbh->prepare("select max(blobidlink) from marc_blob_subfield");
143 my ($res)=$sth->fetchrow;
144 $sth=$dbh->prepare("update marc_subfield_table set subfieldvalue=null, valuebloblink=$res where subfieldid=?");
145 $sth->execute($subfieldid);
148 # note this can leave orphan bloblink. Not a big problem, but we should build somewhere a orphan deleting script...
149 $sth=$dbh->prepare("update marc_subfield_table set subfieldvalue=?,valuebloblink=null where subfieldid=?");
150 $sth->execute($subfieldvalue, $subfieldid);
152 $dbh->do("unlock tables");
155 return($subfieldid, $subfieldvalue);
159 my ($bibid,$tag,$subfieldcode,$subfieldvalue,$subfieldorder) = @_;
164 my $query="select subfieldid from marc_subfield_table where bibid=? and tag=? and subfieldcode=?";
165 if ($subfieldvalue) {
166 $query .= " and subfieldvalue=".$dbh->quote($subfieldvalue);
168 if ($subfieldorder<1) {
171 $query .= " and subfieldorder=$subfieldorder";
173 my $sti=$dbh->prepare($query);
174 $sti->execute($bibid,$tag, $subfieldcode);
175 while (($subfieldid) = $sti->fetchrow) {
177 $lastsubfieldid=$subfieldid;
179 if ($resultcounter>1) {
180 # Error condition. Values given did not resolve into a unique record. Don't know what to edit
181 # should rarely occur (only if we use subfieldvalue with a value that exists twice, which is strange)
184 return $lastsubfieldid;
189 # Add a new subfield to a tag.
193 my $subfieldcode=shift;
194 my $subfieldorder=shift;
195 my $subfieldvalue=shift;
198 unless ($subfieldorder) {
199 my $sth=$dbh->prepare("select max(subfieldorder) from marc_subfield_table where tagid=$tagid");
202 ($subfieldorder) = $sth->fetchrow;
208 if (length($subfieldvalue)>255) {
209 $dbh->do("lock tables marc_blob_subfield WRITE, marc_subfield_table WRITE");
210 my $sth=$dbh->prepare("insert into marc_blob_subfield (subfieldvalue) values (?)");
211 $sth->execute($subfieldvalue);
212 $sth=$dbh->prepare("select max(blobidlink)from marc_blob_subfield");
214 my ($res)=$sth->fetchrow;
215 my $sth=$dbh->prepare("insert into marc_subfield_table (bibid,tagid,tagorder,subfieldcode,subfieldorder,valuebloblink) values (?,?,?,?,?,?)");
216 $sth->execute($bibid,$tagid,$tagorder,$subfieldcode,$subfieldorder,$res);
217 $dbh->do("unlock tables");
219 my $sth=$dbh->prepare("insert into marc_subfield_table (bibid,tag,tagorder,subfieldcode,subfieldorder,subfieldvalue) values (?,?,?,?,?,?)");
220 $sth->execute($bibid,$tagid,$tagorder,$subfieldcode,$subfieldorder,$subfieldvalue);
225 # pass the marcperlstructure to this function, and it will create the records in the marc tables
226 my ($marcstructure) = @_;
232 # adding main table, and retrieving bibid
233 $dbh->do("lock tables marc_biblio WRITE");
234 my $sth=$dbh->prepare("insert into marc_biblio (datecreated,origincode) values (now(),?)");
235 $sth->execute($marcstructure->{origincode});
236 $sth=$dbh->prepare("select max(bibid) from marc_biblio");
238 ($marcstructure->{bibid})=$sth->fetchrow;
239 print "BIBID :::".$marcstructure->{bibid}."\n";
241 $dbh->do("unlock tables");
242 # now, add subfields...
243 foreach $tag (keys %{$marcstructure->{tags}}) {
244 foreach $tagorder (keys %{$marcstructure->{tags}->{$tag}}) {
245 foreach $subfield (keys %{$marcstructure->{tags}->{$tag}->{$tagorder}->{subfields}}) {
246 foreach $subfieldorder (keys %{$marcstructure->{tags}->{$tag}->{$tagorder}->{subfields}->{$subfield}}) {
247 &addSubfield($marcstructure->{bibid},
252 $marcstructure->{tags}->{$tag}->{$tagorder}->{subfields}->{$subfield}->{$subfieldorder}
254 # print "$tag / $tagorder / $subfield / $subfieldorder / ".$marcstructure->{tags}->{$tag}->{$tagorder}->{subfields}->{$subfield}->{$subfieldorder}."\n";
261 sub buildPerlmarcstructure {
262 # this function builds perlmarcstructure from the old koha-DB fields
263 my ($biblionumber,$author,$title,$unititle,$notes,$abstract,
264 $serial,$seriestitle,$copyrightdate,$biblioitemnumber,$volume,$number,
265 $classification,$itemtype,$isbn,$issn,
266 $dewey,$subclass,$publicationyear,$publishercode,
267 $volumedate,$illus,$pages,$notes,
268 $size,$place,$lccn) = @_;
272 my $perlmarcstructure={};
275 my $sth=$dbh->prepare("select tagfield,tagsubfield from marc_subfield_structure where kohafield=?");
276 $sth->execute("biblionumber");
277 if (($tagfield,$tagsubfield)=$sth->fetchrow) {
278 $i=nextsubfieldid($perlmarcstructure->{tags}->{$tagfield}->{1}->{subfields}->{$tagsubfield});
279 $perlmarcstructure->{tags}->{$i}->{tag}=$tagfield;
280 $perlmarcstructure->{tags}->{$i}->{tagorder}=1;
281 $perlmarcstructure->{tags}->{$i}->{indicator}='##';
286 my $subfieldhash=shift;
287 my $maxsubfieldnumber=0;
290 foreach $subfield (keys %$subfieldhash) {
291 foreach $number (keys %{$subfieldhash->{$subfield}}) {
292 ($number>$maxsubfieldnumber) && ($maxsubfieldnumber=$number);
295 return $maxsubfieldnumber+1;
299 # Update the biblio with biblionumber $biblio->{'biblionumber'}
300 # I guess this routine should search through all marc records for a record that
301 # has the same biblionumber stored in it, and modify the MARC record as well as
304 # Also, this subroutine should search through the $biblio object and compare it
305 # to the existing record and _LOG ALL CHANGES MADE_ in some way. I'd like for
306 # this logging feature to be usable to undo changes easily.
308 my ($env, $biblio) = @_;
310 my $biblionumber=$biblio->{'biblionumber'};
312 my $sth=$dbh->prepare("select * from biblio where biblionumber=$biblionumber");
314 my $origbiblio=$sth->fetchrow_hashref;
315 $sth=$dbh->prepare("select subtitle from bibliosubtitle where biblionumber=$biblionumber");
317 my ($subtitle)=$sth->fetchrow;
318 $origbiblio->{'subtitle'}=$subtitle;
319 $sth=$dbh->prepare("select author from additionalauthors where biblionumber=$biblionumber");
321 my $origadditionalauthors;
322 while (my ($author) = $sth->fetchrow) {
323 push (@{$origbiblio->{'additionalauthors'}}, $author);
324 $origadditionalauthors->{$author}=1;
326 $sth=$dbh->prepare("select subject from bibliosubject where biblionumber=$biblionumber");
329 while (my ($subject) = $sth->fetchrow) {
330 push (@{$origbiblio->{'subjects'}}, $subject);
331 $origsubjects->{$subject}=1;
335 # Obtain a list of MARC Record_ID's that are tied to this biblio
336 $sth=$dbh->prepare("select bibid from marc_subfield_table where tag='090' and subfieldvalue=$biblionumber and subfieldcode='c'");
339 while (my ($bibid) = $sth->fetchrow) {
340 push(@marcrecords, $bibid);
346 if ($biblio->{'author'} ne $origbiblio->{'author'}) {
347 my $q_author=$dbh->quote($biblio->{'author'});
348 logchange('kohadb', 'change', 'biblio', 'author', $origbiblio->{'author'}, $biblio->{'author'});
349 my $sti=$dbh->prepare("update biblio set author=$q_author where biblionumber=$biblio->{'biblionumber'}");
351 foreach $bibid (@marcrecords) {
352 logchange('marc', 'change', $bibid, '100', 'a', $origbiblio->{'author'}, $biblio->{'author'});
353 changeSubfield($bibid, '100', 'a', $origbiblio->{'author'}, $biblio->{'author'});
356 if ($biblio->{'title'} ne $origbiblio->{'title'}) {
357 my $q_title=$dbh->quote($biblio->{'title'});
358 logchange('kohadb', 'change', 'biblio', 'title', $origbiblio->{'title'}, $biblio->{'title'});
359 my $sti=$dbh->prepare("update biblio set title=$q_title where biblionumber=$biblio->{'biblionumber'}");
361 foreach $Record_ID (@marcrecords) {
362 logchange('marc', 'change', $Record_ID, '245', 'a', $origbiblio->{'title'}, $biblio->{'title'});
363 changeSubfield($Record_ID, '245', 'a', $origbiblio->{'title'}, $biblio->{'title'});
366 if ($biblio->{'subtitle'} ne $origbiblio->{'subtitle'}) {
367 my $q_subtitle=$dbh->quote($biblio->{'subtitle'});
368 logchange('kohadb', 'change', 'biblio', 'subtitle', $origbiblio->{'subtitle'}, $biblio->{'subtitle'});
369 my $sti=$dbh->prepare("update bibliosubtitle set subtitle=$q_subtitle where biblionumber=$biblio->{'biblionumber'}");
371 foreach $Record_ID (@marcrecords) {
372 logchange('marc', 'change', $Record_ID, '245', 'b', $origbiblio->{'subtitle'}, $biblio->{'subtitle'});
373 changeSubfield($Record_ID, '245', 'b', $origbiblio->{'subtitle'}, $biblio->{'subtitle'});
376 if ($biblio->{'unititle'} ne $origbiblio->{'unititle'}) {
377 my $q_unititle=$dbh->quote($biblio->{'unititle'});
378 logchange('kohadb', 'change', 'biblio', 'unititle', $origbiblio->{'unititle'}, $biblio->{'unititle'});
379 my $sti=$dbh->prepare("update biblio set unititle=$q_unititle where biblionumber=$biblio->{'biblionumber'}");
382 if ($biblio->{'notes'} ne $origbiblio->{'notes'}) {
383 my $q_notes=$dbh->quote($biblio->{'notes'});
384 logchange('kohadb', 'change', 'biblio', 'notes', $origbiblio->{'notes'}, $biblio->{'notes'});
385 my $sti=$dbh->prepare("update biblio set notes=$q_notes where biblionumber=$biblio->{'biblionumber'}");
387 foreach $Record_ID (@marcrecords) {
388 logchange('marc', 'change', $Record_ID, '500', 'a', $origbiblio->{'notes'}, $biblio->{'notes'});
389 changeSubfield($Record_ID, '500', 'a', $origbiblio->{'notes'}, $biblio->{'notes'});
392 if ($biblio->{'serial'} ne $origbiblio->{'serial'}) {
393 my $q_serial=$dbh->quote($biblio->{'serial'});
394 logchange('kohadb', 'change', 'biblio', 'serial', $origbiblio->{'serial'}, $biblio->{'serial'});
395 my $sti=$dbh->prepare("update biblio set serial=$q_serial where biblionumber=$biblio->{'biblionumber'}");
398 if ($biblio->{'seriestitle'} ne $origbiblio->{'seriestitle'}) {
399 my $q_seriestitle=$dbh->quote($biblio->{'seriestitle'});
400 logchange('kohadb', 'change', 'biblio', 'seriestitle', $origbiblio->{'seriestitle'}, $biblio->{'seriestitle'});
401 my $sti=$dbh->prepare("update biblio set seriestitle=$q_seriestitle where biblionumber=$biblio->{'biblionumber'}");
403 foreach $Record_ID (@marcrecords) {
404 logchange('marc', 'change', $Record_ID, '440', 'a', $origbiblio->{'seriestitle'}, $biblio->{'seriestitle'});
405 changeSubfield($Record_ID, '440', 'a', $origbiblio->{'seriestitle'}, $biblio->{'seriestitle'});
408 if ($biblio->{'copyrightdate'} ne $origbiblio->{'copyrightdate'}) {
409 my $q_copyrightdate=$dbh->quote($biblio->{'copyrightdate'});
410 logchange('kohadb', 'change', 'biblio', 'copyrightdate', $origbiblio->{'copyrightdate'}, $biblio->{'copyrightdate'});
411 my $sti=$dbh->prepare("update biblio set copyrightdate=$q_copyrightdate where biblionumber=$biblio->{'biblionumber'}");
413 foreach $Record_ID (@marcrecords) {
414 logchange('marc', 'change', $Record_ID, '260', 'c', "c$origbiblio->{'notes'}", "c$biblio->{'notes'}");
415 changeSubfield($Record_ID, '260', 'c', "c$origbiblio->{'notes'}", "c$biblio->{'notes'}");
419 # Check for subject heading changes
423 foreach $newsubject (@{$biblio->{'subject'}}) {
424 $subjects->{$newsubject}=1;
425 if ($origsubjects->{$newsubject}) {
426 $subjects->{$newsubject}=2;
428 my $q_newsubject=$dbh->quote($newsubject);
429 my $sth=$dbh->prepare("insert into bibliosubject (subject,biblionumber) values ($q_newsubject, $biblionumber)");
431 logchange('kohadb', 'add', 'biblio', 'subject', $newsubject);
433 $subfields->{1}->{'Subfield_Mark'}='a';
434 $subfields->{1}->{'Subfield_Value'}=$newsubject;
437 foreach $Record_ID (@marcrecords) {
438 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
439 logchange('marc', 'add', $Record_ID, '650', 'a', $newsubject);
444 foreach $origsubject (keys %$origsubjects) {
445 if ($subjects->{$origsubject} == 1) {
446 my $q_origsubject=$dbh->quote($origsubject);
447 logchange('kohadb', 'delete', 'biblio', '$biblionumber', 'subject', $origsubject);
448 my $sth=$dbh->prepare("delete from bibliosubject where biblionumber=$biblionumber and subject=$q_origsubject");
454 # At the moment this is just a straight copy of the subject code. Needs heavy
455 # modification to work for additional authors, obviously.
456 # Check for additional author changes
458 my $newadditionalauthor='';
459 my $additionalauthors;
460 foreach $newadditionalauthor (@{$biblio->{'additionalauthor'}}) {
461 $additionalauthors->{$newadditionalauthor}=1;
462 if ($origadditionalauthors->{$newadditionalauthor}) {
463 $additionalauthors->{$newadditionalauthor}=2;
465 my $q_newadditionalauthor=$dbh->quote($newadditionalauthor);
466 my $sth=$dbh->prepare("insert into biblioadditionalauthors (additionalauthor,biblionumber) values ($q_newadditionalauthor, $biblionumber)");
468 logchange('kohadb', 'add', 'biblio', 'additionalauthor', $newadditionalauthor);
470 $subfields->{1}->{'Subfield_Mark'}='a';
471 $subfields->{1}->{'Subfield_Value'}=$newadditionalauthor;
474 foreach $Record_ID (@marcrecords) {
475 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
476 logchange('marc', 'add', $Record_ID, '650', 'a', $newadditionalauthor);
480 my $origadditionalauthor;
481 foreach $origadditionalauthor (keys %$origadditionalauthors) {
482 if ($additionalauthors->{$origadditionalauthor} == 1) {
483 my $q_origadditionalauthor=$dbh->quote($origadditionalauthor);
484 logchange('kohadb', 'delete', 'biblio', '$biblionumber', 'additionalauthor', $origadditionalauthor);
485 my $sth=$dbh->prepare("delete from biblioadditionalauthors where biblionumber=$biblionumber and additionalauthor=$q_origadditionalauthor");
495 # Subroutine to log changes to databases
496 # Eventually, this subroutine will be used to create a log of all changes made,
497 # with the possibility of "undo"ing some changes
499 if ($database eq 'kohadb') {
505 print STDERR "KOHA: $type $section $item $original $new\n";
506 } elsif ($database eq 'marc') {
511 my $subfield_ID=shift;
514 print STDERR "MARC: $type $Record_ID $tag $mark $subfield_ID $original $new\n";
519 # Subroutine to add a tag to an existing MARC Record. If a new linkage id is
520 # desired, set $env->{'linkage'} to 1. If an existing linkage id should be
521 # set, set $env->{'linkid'} to the link number.
522 my ($env, $Record_ID, $tag, $Indicator1, $Indicator2, $subfields) = @_;
524 ($Indicator1) || ($Indicator1=' ');
525 ($Indicator2) || ($Indicator2=' ');
526 my $firstdigit=substr($tag,0,1);
528 foreach (sort keys %$subfields) {
529 my $Subfield_Mark=$subfields->{$_}->{'Subfield_Mark'};
530 my $Subfield_Value=$subfields->{$_}->{'Subfield_Value'};
531 my $q_Subfield_Value=$dbh->quote($Subfield_Value);
533 my $sth=$dbh->prepare("insert into $firstdigit\XX_Subfield_Table (Subfield_ID, Subfield_Mark, Subfield_Value) values ($Subfield_ID, '$Subfield_Mark', $q_Subfield_Value)");
536 my $sth=$dbh->prepare("insert into $firstdigit\XX_Subfield_Table (Subfield_Mark, Subfield_Value) values ('$Subfield_Mark', $q_Subfield_Value)");
538 my $Subfield_Key=$dbh->{'mysql_insertid'};
539 $Subfield_ID=$Subfield_Key;
540 $sth=$dbh->prepare("update $firstdigit\XX_Subfield_Table set Subfield_ID=$Subfield_ID where Subfield_Key=$Subfield_Key");
544 if (my $linkid=$env->{'linkid'}) {
546 my $sth=$dbh->prepare("insert into $firstdigit\XX_Subfield_Table (Subfield_ID, Subfield_Mark, Subfield_Value) values ($Subfield_ID, '8', '$linkid')");
549 my $sth=$dbh->prepare("insert into $firstdigit\XX_Tag_Table (Indicator1, Indicator2, Tag, Subfield_ID) values ('$Indicator1', '$Indicator2', '$tag', $Subfield_ID)");
551 my $Tag_Key=$dbh->{'mysql_insertid'};
553 $sth=$dbh->prepare("update $firstdigit\XX_Tag_Table set Tag_ID=$Tag_ID where Tag_Key=$Tag_Key");
555 $sth=$dbh->prepare("insert into Bib_Table (Record_ID, Tag_$firstdigit\XX_ID) values ($Record_ID, $Tag_ID)");
557 if ($env->{'linkage'}) {
558 my $sth=$dbh->prepare("insert into $firstdigit\XX_Subfield_Table (Subfield_ID, Subfield_Mark, Subfield_Value) values ($Subfield_ID, '8', '$Tag_ID')");
564 return ($env, $Tag_ID);
568 my ($env, $biblioitem) = @_;
570 my $biblionumber=$biblioitem->{'biblionumber'};
571 my $biblioitemnumber=$biblioitem->{'biblioitemnumber'};
572 my $volume=$biblioitem->{'volume'};
573 my $q_volume=$dbh->quote($volume);
574 my $number=$biblioitem->{'number'};
575 my $q_number=$dbh->quote($number);
576 my $classification=$biblioitem->{'classification'};
577 my $q_classification=$dbh->quote($classification);
578 my $itemtype=$biblioitem->{'itemtype'};
579 my $q_itemtype=$dbh->quote($itemtype);
580 my $isbn=$biblioitem->{'isbn'};
581 my $q_isbn=$dbh->quote($isbn);
582 my $issn=$biblioitem->{'issn'};
583 my $q_issn=$dbh->quote($issn);
584 my $dewey=$biblioitem->{'dewey'};
586 ($dewey == 0) && ($dewey='');
587 my $subclass=$biblioitem->{'subclass'};
588 my $q_subclass=$dbh->quote($subclass);
589 my $publicationyear=$biblioitem->{'publicationyear'};
590 my $publishercode=$biblioitem->{'publishercode'};
591 my $q_publishercode=$dbh->quote($publishercode);
592 my $volumedate=$biblioitem->{'volumedate'};
593 my $q_volumedate=$dbh->quote($volumedate);
594 my $illus=$biblioitem->{'illus'};
595 my $q_illus=$dbh->quote($illus);
596 my $pages=$biblioitem->{'pages'};
597 my $q_pages=$dbh->quote($pages);
598 my $notes=$biblioitem->{'notes'};
599 my $q_notes=$dbh->quote($notes);
600 my $size=$biblioitem->{'size'};
601 my $q_size=$dbh->quote($size);
602 my $place=$biblioitem->{'place'};
603 my $q_place=$dbh->quote($place);
604 my $lccn=$biblioitem->{'lccn'};
605 my $q_lccn=$dbh->quote($lccn);
608 # Unless the $env->{'marconly'} flag is set, update the biblioitems table with
611 unless ($env->{'marconly'}) {
612 #my $sth=$dbh->prepare("lock tables biblioitems write");
614 my $sth=$dbh->prepare("select max(biblioitemnumber) from biblioitems");
616 my ($biblioitemnumber) =$sth->fetchrow;
618 $sth=$dbh->prepare("insert into biblioitems (biblionumber,biblioitemnumber,volume,number,classification,itemtype,isbn,issn,dewey,subclass,publicationyear,publishercode,volumedate,illus,pages,notes,size,place,lccn) values ($biblionumber, $biblioitemnumber, $q_volume, $q_number, $q_classification, $q_itemtype, $q_isbn, $q_issn, $dewey, $q_subclass, $publicationyear, $q_publishercode, $q_volumedate, $q_illus, $q_pages,$q_notes, $q_size, $q_place, $q_lccn)");
620 #my $sth=$dbh->prepare("unlock tables");
625 # Should we check if there is already a biblioitem/marc with the
626 # same isbn/lccn/issn?
628 my $sth=$dbh->prepare("select title,unititle,seriestitle,copyrightdate,notes,author from biblio where biblionumber=$biblionumber");
630 my ($title, $unititle,$seriestitle,$copyrightdate,$biblionotes,$author) = $sth->fetchrow;
631 $sth=$dbh->prepare("select subtitle from bibliosubtitle where biblionumber=$biblionumber");
633 my ($subtitle) = $sth->fetchrow;
634 $sth=$dbh->prepare("select author from additionalauthors where biblionumber=$biblionumber");
636 my @additionalauthors;
637 while (my ($additionalauthor) = $sth->fetchrow) {
638 push (@additionalauthors, $additionalauthor);
640 $sth=$dbh->prepare("select subject from bibliosubject where biblionumber=$biblionumber");
643 while (my ($subject) = $sth->fetchrow) {
644 push (@subjects, $subject);
649 $sth=$dbh->prepare("insert into Resource_Table (Record_ID) values (0)");
651 my $Resource_ID=$dbh->{'mysql_insertid'};
652 my $Record_ID=$Resource_ID;
653 $sth=$dbh->prepare("update Resource_Table set Record_ID=$Record_ID where Resource_ID=$Resource_ID");
659 $subfields->{1}->{'Subfield_Mark'}='a';
660 $subfields->{1}->{'Subfield_Value'}=$title;
662 $subfields->{2}->{'Subfield_Mark'}='b';
663 $subfields->{2}->{'Subfield_Value'}=$subtitle;
666 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
672 $subfields->{1}->{'Subfield_Mark'}='a';
673 $subfields->{1}->{'Subfield_Value'}=$author;
675 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
680 $subfields->{1}->{'Subfield_Mark'}='a';
681 $subfields->{1}->{'Subfield_Value'}=$seriestitle;
683 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
688 $subfields->{1}->{'Subfield_Mark'}='a';
689 $subfields->{1}->{'Subfield_Value'}=$biblionotes;
690 $subfields->{2}->{'Subfield_Mark'}='3';
691 $subfields->{2}->{'Subfield_Value'}='biblio';
693 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
696 foreach (@additionalauthors) {
698 (next) unless ($author);
700 $subfields->{1}->{'Subfield_Mark'}='a';
701 $subfields->{1}->{'Subfield_Value'}=$author;
702 $subfields->{2}->{'Subfield_Mark'}='e';
703 $subfields->{2}->{'Subfield_Value'}='author';
705 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
709 (next) unless ($illus);
711 $subfields->{1}->{'Subfield_Mark'}='a';
712 $subfields->{1}->{'Subfield_Value'}=$illus;
713 $subfields->{2}->{'Subfield_Mark'}='e';
714 $subfields->{2}->{'Subfield_Value'}='illustrator';
716 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
719 foreach (@subjects) {
721 (next) unless ($subject);
723 $subfields->{1}->{'Subfield_Mark'}='a';
724 $subfields->{1}->{'Subfield_Value'}=$subject;
726 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
733 $subfields->{1}->{'Subfield_Mark'}='a';
734 $subfields->{1}->{'Subfield_Value'}=$isbn;
736 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
741 $subfields->{1}->{'Subfield_Mark'}='a';
742 $subfields->{1}->{'Subfield_Value'}=$lccn;
744 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
749 $subfields->{1}->{'Subfield_Mark'}='a';
750 $subfields->{1}->{'Subfield_Value'}=$issn;
752 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
757 $subfields->{1}->{'Subfield_Mark'}='a';
758 $subfields->{1}->{'Subfield_Value'}=$dewey;
760 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
762 # DEWEY subclass and itemtype
765 $subfields->{1}->{'Subfield_Mark'}='a';
766 $subfields->{1}->{'Subfield_Value'}=$itemtype;
767 $subfields->{2}->{'Subfield_Mark'}='b';
768 $subfields->{2}->{'Subfield_Value'}=$subclass;
769 $subfields->{3}->{'Subfield_Mark'}='c';
770 $subfields->{3}->{'Subfield_Value'}=$biblionumber;
771 $subfields->{4}->{'Subfield_Mark'}='d';
772 $subfields->{4}->{'Subfield_Value'}=$biblioitemnumber;
774 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
779 $subfields->{1}->{'Subfield_Mark'}='a';
780 $subfields->{1}->{'Subfield_Value'}=$place;
781 $subfields->{2}->{'Subfield_Mark'}='b';
782 $subfields->{2}->{'Subfield_Value'}=$publishercode;
783 $subfields->{3}->{'Subfield_Mark'}='c';
784 $subfields->{3}->{'Subfield_Value'}=$publicationyear;
785 if ($copyrightdate) {
786 $subfields->{4}->{'Subfield_Mark'}='c';
787 $subfields->{4}->{'Subfield_Value'}="c$copyrightdate";
790 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
793 if ($pages || $size) {
795 $subfields->{1}->{'Subfield_Mark'}='a';
796 $subfields->{1}->{'Subfield_Value'}=$pages;
797 $subfields->{2}->{'Subfield_Mark'}='c';
798 $subfields->{2}->{'Subfield_Value'}=$size;
800 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
803 if ($volume || $number) {
805 $subfields->{1}->{'Subfield_Mark'}='v';
806 $subfields->{1}->{'Subfield_Value'}=$volume;
807 $subfields->{2}->{'Subfield_Mark'}='n';
808 $subfields->{2}->{'Subfield_Value'}=$number;
810 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
815 $subfields->{1}->{'Subfield_Mark'}='a';
816 $subfields->{1}->{'Subfield_Value'}=$notes;
817 $subfields->{2}->{'Subfield_Mark'}='3';
818 $subfields->{2}->{'Subfield_Value'}='biblioitem';
820 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
824 return ($env, $Record_ID);
827 sub updateBiblioItem {
828 # Update the biblioitem with biblioitemnumber $biblioitem->{'biblioitemnumber'}
830 # This routine should also check to see which fields are actually being
831 # modified, and log all changes.
833 my ($env, $biblioitem) = @_;
836 my $biblioitemnumber=$biblioitem->{'biblioitemnumber'};
837 my $sth=$dbh->prepare("select * from biblioitems where biblioitemnumber=$biblioitemnumber");
838 # obi = original biblioitem
839 my $obi=$sth->fetchrow_hashref;
840 $sth=$dbh->prepare("select B.Record_ID from Bib_Table B, 0XX_Tag_Table T, 0XX_Subfield_Table S where B.Tag_0XX_ID=T.Tag_ID and T.Subfield_ID=S.Subfield_ID and T.Tag='090' and S.Subfield_Mark='c' and S.Subfield_Value=$biblioitemnumber");
842 my ($Record_ID) = $sth->fetchrow;
843 if ($biblioitem->{'biblionumber'} ne $obi->{'biblionumber'}) {
844 logchange('kohadb', 'change', 'biblioitems', 'biblionumber', $obi->{'biblionumber'}, $biblioitem->{'biblionumber'});
845 my $sth=$dbh->prepare("update biblioitems set biblionumber=$biblioitem->{'biblionumber'} where biblioitemnumber=$biblioitemnumber");
846 logchange('marc', 'change', $Record_ID, '090', 'c', $obi->{'biblionumber'}, $biblioitem->{'biblionumber'});
847 changeSubfield($Record_ID, '090', 'c', $obi->{'biblionumber'}, $biblioitem->{'biblionumber'});
849 if ($biblioitem->{'volume'} ne $obi->{'volume'}) {
850 logchange('kohadb', 'change', 'biblioitems', 'volume', $obi->{'volume'}, $biblioitem->{'volume'});
851 my $q_volume=$dbh->quote($biblioitem->{'volume'});
852 my $sth=$dbh->prepare("update biblioitems set volume=$q_volume where biblioitemnumber=$biblioitemnumber");
853 logchange('marc', 'change', $Record_ID, '440', 'v', $obi->{'volume'}, $biblioitem->{'volume'});
854 changeSubfield($Record_ID, '440', 'v', $obi->{'volume'}, $biblioitem->{'volume'});
856 if ($biblioitem->{'number'} ne $obi->{'number'}) {
857 logchange('kohadb', 'change', 'biblioitems', 'number', $obi->{'number'}, $biblioitem->{'number'});
858 my $q_number=$dbh->quote($biblioitem->{'number'});
859 my $sth=$dbh->prepare("update biblioitems set number=$q_number where biblioitemnumber=$biblioitemnumber");
860 logchange('marc', 'change', $Record_ID, '440', 'v', $obi->{'number'}, $biblioitem->{'number'});
861 changeSubfield($Record_ID, '440', 'v', $obi->{'number'}, $biblioitem->{'number'});
863 if ($biblioitem->{'itemtype'} ne $obi->{'itemtype'}) {
864 logchange('kohadb', 'change', 'biblioitems', 'itemtype', $obi->{'itemtype'}, $biblioitem->{'itemtype'});
865 my $q_itemtype=$dbh->quote($biblioitem->{'itemtype'});
866 my $sth=$dbh->prepare("update biblioitems set itemtype=$q_itemtype where biblioitemnumber=$biblioitemnumber");
867 logchange('marc', 'change', $Record_ID, '090', 'a', $obi->{'itemtype'}, $biblioitem->{'itemtype'});
868 changeSubfield($Record_ID, '090', 'a', $obi->{'itemtype'}, $biblioitem->{'itemtype'});
870 if ($biblioitem->{'isbn'} ne $obi->{'isbn'}) {
871 logchange('kohadb', 'change', 'biblioitems', 'isbn', $obi->{'isbn'}, $biblioitem->{'isbn'});
872 my $q_isbn=$dbh->quote($biblioitem->{'isbn'});
873 my $sth=$dbh->prepare("update biblioitems set isbn=$q_isbn where biblioitemnumber=$biblioitemnumber");
874 logchange('marc', 'change', $Record_ID, '020', 'a', $obi->{'isbn'}, $biblioitem->{'isbn'});
875 changeSubfield($Record_ID, '020', 'a', $obi->{'isbn'}, $biblioitem->{'isbn'});
877 if ($biblioitem->{'issn'} ne $obi->{'issn'}) {
878 logchange('kohadb', 'change', 'biblioitems', 'issn', $obi->{'issn'}, $biblioitem->{'issn'});
879 my $q_issn=$dbh->quote($biblioitem->{'issn'});
880 my $sth=$dbh->prepare("update biblioitems set issn=$q_issn where biblioitemnumber=$biblioitemnumber");
881 logchange('marc', 'change', $Record_ID, '022', 'a', $obi->{'issn'}, $biblioitem->{'issn'});
882 changeSubfield($Record_ID, '022', 'a', $obi->{'issn'}, $biblioitem->{'issn'});
884 if ($biblioitem->{'dewey'} ne $obi->{'dewey'}) {
885 logchange('kohadb', 'change', 'biblioitems', 'dewey', $obi->{'dewey'}, $biblioitem->{'dewey'});
886 my $sth=$dbh->prepare("update biblioitems set dewey=$biblioitem->{'dewey'} where biblioitemnumber=$biblioitemnumber");
887 logchange('marc', 'change', $Record_ID, '082', 'a', $obi->{'dewey'}, $biblioitem->{'dewey'});
888 changeSubfield($Record_ID, '082', 'a', $obi->{'dewey'}, $biblioitem->{'dewey'});
890 if ($biblioitem->{'subclass'} ne $obi->{'subclass'}) {
891 logchange('kohadb', 'change', 'biblioitems', 'subclass', $obi->{'subclass'}, $biblioitem->{'subclass'});
892 my $q_subclass=$dbh->quote($biblioitem->{'subclass'});
893 my $sth=$dbh->prepare("update biblioitems set subclass=$q_subclass where biblioitemnumber=$biblioitemnumber");
894 logchange('marc', 'change', $Record_ID, '090', 'b', $obi->{'subclass'}, $biblioitem->{'subclass'});
895 changeSubfield($Record_ID, '090', 'b', $obi->{'subclass'}, $biblioitem->{'subclass'});
897 if ($biblioitem->{'place'} ne $obi->{'place'}) {
898 logchange('kohadb', 'change', 'biblioitems', 'place', $obi->{'place'}, $biblioitem->{'place'});
899 my $q_place=$dbh->quote($biblioitem->{'place'});
900 my $sth=$dbh->prepare("update biblioitems set place=$q_place where biblioitemnumber=$biblioitemnumber");
901 logchange('marc', 'change', $Record_ID, '260', 'a', $obi->{'place'}, $biblioitem->{'place'});
902 changeSubfield($Record_ID, '260', 'a', $obi->{'place'}, $biblioitem->{'place'});
904 if ($biblioitem->{'publishercode'} ne $obi->{'publishercode'}) {
905 logchange('kohadb', 'change', 'biblioitems', 'publishercode', $obi->{'publishercode'}, $biblioitem->{'publishercode'});
906 my $q_publishercode=$dbh->quote($biblioitem->{'publishercode'});
907 my $sth=$dbh->prepare("update biblioitems set publishercode=$q_publishercode where biblioitemnumber=$biblioitemnumber");
908 logchange('marc', 'change', $Record_ID, '260', 'b', $obi->{'publishercode'}, $biblioitem->{'publishercode'});
909 changeSubfield($Record_ID, '260', 'b', $obi->{'publishercode'}, $biblioitem->{'publishercode'});
911 if ($biblioitem->{'publicationyear'} ne $obi->{'publicationyear'}) {
912 logchange('kohadb', 'change', 'biblioitems', 'publicationyear', $obi->{'publicationyear'}, $biblioitem->{'publicationyear'});
913 my $q_publicationyear=$dbh->quote($biblioitem->{'publicationyear'});
914 my $sth=$dbh->prepare("update biblioitems set publicationyear=$q_publicationyear where biblioitemnumber=$biblioitemnumber");
915 logchange('marc', 'change', $Record_ID, '260', 'c', $obi->{'publicationyear'}, $biblioitem->{'publicationyear'});
916 changeSubfield($Record_ID, '260', 'c', $obi->{'publicationyear'}, $biblioitem->{'publicationyear'});
918 if ($biblioitem->{'illus'} ne $obi->{'illus'}) {
919 logchange('kohadb', 'change', 'biblioitems', 'illus', $obi->{'illus'}, $biblioitem->{'illus'});
920 my $q_illus=$dbh->quote($biblioitem->{'illus'});
921 my $sth=$dbh->prepare("update biblioitems set illus=$q_illus where biblioitemnumber=$biblioitemnumber");
922 logchange('marc', 'change', $Record_ID, '700', 'a', $obi->{'illus'}, $biblioitem->{'illus'});
923 changeSubfield($Record_ID, '700', 'a', $obi->{'illus'}, $biblioitem->{'illus'});
925 if ($biblioitem->{'pages'} ne $obi->{'pages'}) {
926 logchange('kohadb', 'change', 'biblioitems', 'pages', $obi->{'pages'}, $biblioitem->{'pages'});
927 my $q_pages=$dbh->quote($biblioitem->{'pages'});
928 my $sth=$dbh->prepare("update biblioitems set pages=$q_pages where biblioitemnumber=$biblioitemnumber");
929 logchange('marc', 'change', $Record_ID, '300', 'a', $obi->{'pages'}, $biblioitem->{'pages'});
930 changeSubfield($Record_ID, '300', 'a', $obi->{'pages'}, $biblioitem->{'pages'});
932 if ($biblioitem->{'size'} ne $obi->{'size'}) {
933 logchange('kohadb', 'change', 'biblioitems', 'size', $obi->{'size'}, $biblioitem->{'size'});
934 my $q_size=$dbh->quote($biblioitem->{'size'});
935 my $sth=$dbh->prepare("update biblioitems set size=$q_size where biblioitemnumber=$biblioitemnumber");
936 logchange('marc', 'change', $Record_ID, '300', 'c', $obi->{'size'}, $biblioitem->{'size'});
937 changeSubfield($Record_ID, '300', 'c', $obi->{'size'}, $biblioitem->{'size'});
939 if ($biblioitem->{'notes'} ne $obi->{'notes'}) {
940 logchange('kohadb', 'change', 'biblioitems', 'notes', $obi->{'notes'}, $biblioitem->{'notes'});
941 my $q_notes=$dbh->quote($biblioitem->{'notes'});
942 my $sth=$dbh->prepare("update biblioitems set notes=$q_notes where biblioitemnumber=$biblioitemnumber");
943 logchange('marc', 'change', $Record_ID, '500', 'a', $obi->{'notes'}, $biblioitem->{'notes'});
944 changeSubfield($Record_ID, '500', 'a', $obi->{'notes'}, $biblioitem->{'notes'});
946 if ($biblioitem->{'lccn'} ne $obi->{'lccn'}) {
947 logchange('kohadb', 'change', 'biblioitems', 'lccn', $obi->{'lccn'}, $biblioitem->{'lccn'});
948 my $q_lccn=$dbh->quote($biblioitem->{'lccn'});
949 my $sth=$dbh->prepare("update biblioitems set lccn=$q_lccn where biblioitemnumber=$biblioitemnumber");
950 logchange('marc', 'change', $Record_ID, '010', 'a', $obi->{'lccn'}, $biblioitem->{'lccn'});
951 changeSubfield($Record_ID, '010', 'a', $obi->{'lccn'}, $biblioitem->{'lccn'});
960 my ($env, $Record_ID, $item) = @_;
962 my $barcode=$item->{'barcode'};
963 my $q_barcode=$dbh->quote($barcode);
964 my $biblionumber=$item->{'biblionumber'};
965 my $biblioitemnumber=$item->{'biblioitemnumber'};
966 my $dateaccessioned=$item->{'dateaccessioned'};
967 my $booksellerid=$item->{'booksellerid'};
968 my $q_booksellerid=$dbh->quote($booksellerid);
969 my $homebranch=$item->{'homebranch'};
970 my $q_homebranch=$dbh->quote($homebranch);
971 my $holdingbranch=$item->{'holdingbranch'};
972 my $price=$item->{'price'};
973 my $replacementprice=$item->{'replacementprice'};
974 my $replacementpricedate=$item->{'replacementpricedate'};
975 my $q_replacementpricedate=$dbh->quote($replacementpricedate);
976 my $notforloan=$item->{'notforloan'};
977 my $itemlost=$item->{'itemlost'};
978 my $wthdrawn=$item->{'wthdrawn'};
979 my $restricted=$item->{'restricted'};
980 my $itemnotes=$item->{'itemnotes'};
981 my $q_itemnotes=$dbh->quote($itemnotes);
982 my $itemtype=$item->{'itemtype'};
983 my $subclass=$item->{'subclass'};
987 unless ($env->{'marconly'}) {
988 my $sth=$dbh->prepare("select max(itemnumber) from items");
990 my ($itemnumber) =$sth->fetchrow;
992 $sth=$dbh->prepare("insert into items (itemnumber,biblionumber,biblioitemnumber,barcode,dateaccessioned,booksellerid,homebranch,price,replacementprice,replacementpricedate,notforloan,itemlost,wthdrawn,restricted,itemnotes) values ($itemnumber,$biblionumber,$biblioitemnumber,$q_barcode,$dateaccessioned,$q_booksellerid,$q_homebranch,$price,$q_replacementpricedate,$notforloan,$itemlost,$wthdrawn,$restricted,$q_itemnotes)");
999 $subfields->{1}->{'Subfield_Mark'}='p';
1000 $subfields->{1}->{'Subfield_Value'}=$barcode;
1001 $subfields->{2}->{'Subfield_Mark'}='d';
1002 $subfields->{2}->{'Subfield_Value'}=$dateaccessioned;
1003 $subfields->{3}->{'Subfield_Mark'}='e';
1004 $subfields->{3}->{'Subfield_Value'}=$booksellerid;
1005 $subfields->{4}->{'Subfield_Mark'}='b';
1006 $subfields->{4}->{'Subfield_Value'}=$homebranch;
1007 $subfields->{5}->{'Subfield_Mark'}='l';
1008 $subfields->{5}->{'Subfield_Value'}=$holdingbranch;
1009 $subfields->{6}->{'Subfield_Mark'}='c';
1010 $subfields->{6}->{'Subfield_Value'}=$price;
1011 $subfields->{7}->{'Subfield_Mark'}='c';
1012 $subfields->{7}->{'Subfield_Value'}=$replacementprice;
1013 $subfields->{8}->{'Subfield_Mark'}='d';
1014 $subfields->{8}->{'Subfield_Value'}=$replacementpricedate;
1016 $subfields->{9}->{'Subfield_Mark'}='h';
1017 $subfields->{9}->{'Subfield_Value'}='Not for loan';
1020 $subfields->{10}->{'Subfield_Mark'}='j';
1021 $subfields->{10}->{'Subfield_Value'}='Item lost';
1024 $subfields->{11}->{'Subfield_Mark'}='j';
1025 $subfields->{11}->{'Subfield_Value'}='Item withdrawn';
1028 $subfields->{12}->{'Subfield_Mark'}='z';
1029 $subfields->{12}->{'Subfield_Value'}=$itemnotes;
1033 $env->{'linkage'}=1;
1034 ($env, $Tag_ID) = addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
1035 $env->{'linkage'}=0;
1036 $env->{'linkid'}=$Tag_ID;
1039 $subfields2->{1}->{'Subfield_Mark'}='a';
1040 $subfields2->{1}->{'Subfield_Value'}='Coast Mountains School District';
1041 $subfields2->{1}->{'Subfield_Mark'}='b';
1042 $subfields2->{1}->{'Subfield_Value'}=$homebranch;
1043 $subfields2->{1}->{'Subfield_Mark'}='c';
1044 $subfields2->{1}->{'Subfield_Value'}=$itemtype;
1045 $subfields2->{2}->{'Subfield_Mark'}='m';
1046 $subfields2->{2}->{'Subfield_Value'}=$subclass;
1047 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields2);
1048 $env->{'linkid'}='';
1052 # Update the item with itemnumber $item->{'itemnumber'}
1053 # This routine should also modify the corresponding MARC record data. (852 and
1054 # 876 tags with 876p tag the same as $item->{'barcode'}
1056 # This routine should also check to see which fields are actually being
1057 # modified, and log all changes.
1059 my ($env, $item) = @_;
1061 my $itemnumber=$item->{'itemnumber'};
1062 my $biblionumber=$item->{'biblionumber'};
1063 my $biblioitemnumber=$item->{'biblioitemnumber'};
1064 my $barcode=$item->{'barcode'};
1065 my $dateaccessioned=$item->{'dateaccessioned'};
1066 my $booksellerid=$item->{'booksellerid'};
1067 my $homebranch=$item->{'homebranch'};
1068 my $price=$item->{'price'};
1069 my $replacementprice=$item->{'replacementprice'};
1070 my $replacementpricedate=$item->{'replacementpricedate'};
1071 my $multivolume=$item->{'multivolume'};
1072 my $stack=$item->{'stack'};
1073 my $notforloan=$item->{'notforloan'};
1074 my $itemlost=$item->{'itemlost'};
1075 my $wthdrawn=$item->{'wthdrawn'};
1076 my $bulk=$item->{'bulk'};
1077 my $restricted=$item->{'restricted'};
1078 my $binding=$item->{'binding'};
1079 my $itemnotes=$item->{'itemnotes'};
1080 my $holdingbranch=$item->{'holdingbranch'};
1081 my $interim=$item->{'interim'};
1082 my $sth=$dbh->prepare("select * from items where itemnumber=$itemnumber");
1084 my $olditem=$sth->fetchrow_hashref;
1085 my $q_barcode=$dbh->quote($olditem->{'barcode'});
1086 $sth=$dbh->prepare("select S.Subfield_ID, B.Record_ID from 8XX_Subfield_Table S, 8XX_Tag_Table T, Bib_Table B where B.Tag_8XX_ID=T.Tag_ID and T.Subfield_ID=S.Subfield_ID and Subfield_Mark='p' and Subfield_Value=$q_barcode");
1088 my ($Subfield876_ID, $Record_ID) = $sth->fetchrow;
1089 $sth=$dbh->prepare("select Subfield_Value from 8XX_Subfield_Table where Subfield_Mark=8 and Subfield_ID=$Subfield876_ID");
1091 my ($link) = $sth->fetchrow;
1092 $sth=$dbh->prepare("select Subfield_ID from 8XX_Subfield_Table where Subfield_Mark=8 and Subfield_Value=$link and !(Subfield_ID=$Subfield876_ID)");
1094 my ($Subfield852_ID) = $sth->fetchrow;
1096 if ($item->{'barcode'} ne $olditem->{'barcode'}) {
1097 logchange('kohadb', 'change', 'items', 'barcode', $olditem->{'barcode'}, $item->{'barcode'});
1098 my $q_barcode=$dbh->quote($item->{'barcode'});
1099 my $sth=$dbh->prepare("update items set barcode=$q_barcode where itemnumber=$itemnumber");
1101 my ($Subfield_ID, $Subfield_Key) = changeSubfield($Record_ID, '876', 'p', $olditem->{'barcode'}, $item->{'barcode'}, $Subfield876_ID);
1102 logchange('marc', 'change', $Record_ID, '876', 'p', $Subfield_Key, $olditem->{'barcode'}, $item->{'barcode'});
1104 if ($item->{'booksellerid'} ne $olditem->{'booksellerid'}) {
1105 logchange('kohadb', 'change', 'items', 'booksellerid', $olditem->{'booksellerid'}, $item->{'booksellerid'});
1106 my $q_booksellerid=$dbh->quote($item->{'booksellerid'});
1107 my $sth=$dbh->prepare("update items set booksellerid=$q_booksellerid where itemnumber=$itemnumber");
1109 my ($Subfield_ID, $Subfield_Key) = changeSubfield($Record_ID, '876', 'e', $olditem->{'booksellerid'}, $item->{'booksellerid'}, $Subfield876_ID);
1110 logchange('marc', 'change', $Record_ID, '876', 'e', $Subfield_Key, $olditem->{'booksellerid'}, $item->{'booksellerid'});
1112 if ($item->{'dateaccessioned'} ne $olditem->{'dateaccessioned'}) {
1113 logchange('kohadb', 'change', 'items', 'dateaccessioned', $olditem->{'dateaccessioned'}, $item->{'dateaccessioned'});
1114 my $q_dateaccessioned=$dbh->quote($item->{'dateaccessioned'});
1115 my $sth=$dbh->prepare("update items set dateaccessioned=$q_dateaccessioned where itemnumber=$itemnumber");
1117 my ($Subfield_ID, $Subfield_Key) = changeSubfield($Record_ID, '876', 'd', $olditem->{'dateaccessioned'}, $item->{'dateaccessioned'}, $Subfield876_ID);
1118 logchange('marc', 'change', $Record_ID, '876', 'd', $Subfield_Key, $olditem->{'dateaccessioned'}, $item->{'dateaccessioned'});
1120 if ($item->{'homebranch'} ne $olditem->{'homebranch'}) {
1121 logchange('kohadb', 'change', 'items', 'homebranch', $olditem->{'homebranch'}, $item->{'homebranch'});
1122 my $q_homebranch=$dbh->quote($item->{'homebranch'});
1123 my $sth=$dbh->prepare("update items set homebranch=$q_homebranch where itemnumber=$itemnumber");
1125 my ($Subfield_ID, $Subfield_Key) = changeSubfield($Record_ID, '876', 'b', $olditem->{'homebranch'}, $item->{'homebranch'}, $Subfield876_ID);
1126 logchange('marc', 'change', $Record_ID, '876', 'b', $Subfield_Key, $olditem->{'homebranch'}, $item->{'homebranch'});
1128 if ($item->{'holdingbranch'} ne $olditem->{'holdingbranch'}) {
1129 logchange('kohadb', 'change', 'items', 'holdingbranch', $olditem->{'holdingbranch'}, $item->{'holdingbranch'});
1130 my $q_holdingbranch=$dbh->quote($item->{'holdingbranch'});
1131 my $sth=$dbh->prepare("update items set holdingbranch=$q_holdingbranch where itemnumber=$itemnumber");
1133 my ($Subfield_ID, $Subfield_Key) = changeSubfield($Record_ID, '876', 'l', $olditem->{'holdingbranch'}, $item->{'holdingbranch'}, $Subfield876_ID);
1134 logchange('marc', 'change', $Record_ID, '876', 'l', $Subfield_Key, $olditem->{'holdingbranch'}, $item->{'holdingbranch'});
1136 if ($item->{'price'} ne $olditem->{'price'}) {
1137 logchange('kohadb', 'change', 'items', 'price', $olditem->{'price'}, $item->{'price'});
1138 my $q_price=$dbh->quote($item->{'price'});
1139 my $sth=$dbh->prepare("update items set price=$q_price where itemnumber=$itemnumber");
1141 my ($Subfield_ID, $Subfield_Key) = changeSubfield($Record_ID, '876', 'c', $olditem->{'price'}, $item->{'price'}, $Subfield876_ID);
1142 logchange('marc', 'change', $Record_ID, '876', 'c', $Subfield_Key, $olditem->{'price'}, $item->{'price'});
1144 if ($item->{'itemnotes'} ne $olditem->{'itemnotes'}) {
1145 logchange('kohadb', 'change', 'items', 'itemnotes', $olditem->{'itemnotes'}, $item->{'itemnotes'});
1146 my $q_itemnotes=$dbh->quote($item->{'itemnotes'});
1147 my $sth=$dbh->prepare("update items set itemnotes=$q_itemnotes where itemnumber=$itemnumber");
1149 my ($Subfield_ID, $Subfield_Key) = changeSubfield($Record_ID, '876', 'c', $olditem->{'itemnotes'}, $item->{'itemnotes'}, $Subfield876_ID);
1150 logchange('marc', 'change', $Record_ID, '876', 'c', $Subfield_Key, $olditem->{'itemnotes'}, $item->{'itemnotes'});
1152 if ($item->{'notforloan'} ne $olditem->{'notforloan'}) {
1153 logchange('kohadb', 'change', 'items', 'notforloan', $olditem->{'notforloan'}, $item->{'notforloan'});
1154 my $sth=$dbh->prepare("update items set notforloan=$notforloan where itemnumber=$itemnumber");
1156 if ($item->{'notforloan'}) {
1157 my ($Subfield_ID, $Subfield_Key) = addSubfield($Record_ID, '876', 'h', 'Not for loan', $Subfield876_ID);
1158 logchange('marc', 'add', $Record_ID, '876', 'h', $Subfield_Key, 'Not for loan');
1160 my ($Subfield_ID, $Subfield_Key) = deleteSubfield($Record_ID, '876', 'h', 'Not for loan', $Subfield876_ID);
1161 logchange('marc', 'delete', $Record_ID, '876', 'h', $Subfield_Key, 'Not for loan');
1164 if ($item->{'itemlost'} ne $olditem->{'itemlost'}) {
1165 logchange('kohadb', 'change', 'items', 'itemlost', $olditem->{'itemlost'}, $item->{'itemlost'});
1166 my $sth=$dbh->prepare("update items set itemlost=$itemlost where itemnumber=$itemnumber");
1168 if ($item->{'itemlost'}) {
1169 my ($Subfield_ID, $Subfield_Key) = addSubfield($Record_ID, '876', 'h', 'Item lost', $Subfield876_ID);
1170 logchange('marc', 'add', $Record_ID, '876', 'h', $Subfield_Key, 'Item lost');
1172 my ($Subfield_ID, $Subfield_Key) = deleteSubfield($Record_ID, '876', 'h', 'Item lost', $Subfield876_ID);
1173 logchange('marc', 'delete', $Record_ID, '876', 'h', $Subfield_Key, 'Item lost');
1176 if ($item->{'wthdrawn'} ne $olditem->{'wthdrawn'}) {
1177 logchange('kohadb', 'change', 'items', 'wthdrawn', $olditem->{'wthdrawn'}, $item->{'wthdrawn'});
1178 my $sth=$dbh->prepare("update items set wthdrawn=$wthdrawn where itemnumber=$itemnumber");
1180 if ($item->{'wthdrawn'}) {
1181 my ($Subfield_ID, $Subfield_Key) = addSubfield($Record_ID, '876', 'h', 'Withdrawn', $Subfield876_ID);
1182 logchange('marc', 'add', $Record_ID, '876', 'h', $Subfield_Key, 'Withdrawn');
1184 my ($Subfield_ID, $Subfield_Key) = deleteSubfield($Record_ID, '876', 'h', 'Withdrawn', $Subfield876_ID);
1185 logchange('marc', 'delete', $Record_ID, '876', 'h', $Subfield_Key, 'Withdrawn');
1188 if ($item->{'restricted'} ne $olditem->{'restricted'}) {
1189 logchange('kohadb', 'change', 'items', 'restricted', $olditem->{'restricted'}, $item->{'restricted'});
1190 my $sth=$dbh->prepare("update items set restricted=$restricted where itemnumber=$itemnumber");
1192 if ($item->{'restricted'}) {
1193 my ($Subfield_ID, $Subfield_Key) = addSubfield($Record_ID, '876', 'h', 'Restricted', $Subfield876_ID);
1194 logchange('marc', 'add', $Record_ID, '876', 'h', $Subfield_Key, 'Restricted');
1196 my ($Subfield_ID, $Subfield_Key) = deleteSubfield($Record_ID, '876', 'h', 'Restricted', $Subfield876_ID);
1197 logchange('marc', 'delete', $Record_ID, '876', 'h', $Subfield_Key, 'Restricted');
1206 my ($supplierid)=@_;
1208 my $query = "Select count(*),authorisedby,entrydate,basketno from aqorders where
1209 booksellerid='$supplierid' and (quantity > quantityreceived or
1210 quantityreceived is NULL)
1211 and (datecancellationprinted is NULL or datecancellationprinted = '0000-00-00')";
1212 $query.=" group by basketno order by entrydate desc";
1214 my $sth=$dbh->prepare($query);
1218 while (my $data=$sth->fetchrow_hashref){
1224 return ($i,\@results);
1230 my $query="Select count(*) from items where biblionumber=$biblio";
1232 my $sth=$dbh->prepare($query);
1234 my $data=$sth->fetchrow_hashref;
1237 return($data->{'count(*)'});
1243 my $query="Select ordernumber from aqorders where biblionumber=$bib and
1244 biblioitemnumber='$bi'";
1245 my $sth=$dbh->prepare($query);
1247 my $ordnum=$sth->fetchrow_hashref;
1249 my $order=getsingleorder($ordnum->{'ordernumber'});
1252 return ($order,$ordnum->{'ordernumber'});
1255 sub getsingleorder {
1258 my $query="Select * from biblio,biblioitems,aqorders,aqorderbreakdown
1259 where aqorders.ordernumber='$ordnum'
1260 and biblio.biblionumber=aqorders.biblionumber and
1261 biblioitems.biblioitemnumber=aqorders.biblioitemnumber and
1262 aqorders.ordernumber=aqorderbreakdown.ordernumber";
1263 my $sth=$dbh->prepare($query);
1265 my $data=$sth->fetchrow_hashref;
1274 my $query="Select * from aqorders,biblio,biblioitems where
1275 booksellerinvoicenumber='$invoice'
1276 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber=
1277 aqorders.biblioitemnumber group by aqorders.ordernumber,aqorders.biblioitemnumber";
1280 my $sth=$dbh->prepare($query);
1282 while (my $data=$sth->fetchrow_hashref){
1288 return($i,@results);
1292 #gets all orders from a certain supplier, orders them alphabetically
1295 my $query="Select * from aqorders,biblio,biblioitems where booksellerid='$supid'
1296 and (cancelledby is NULL or cancelledby = '')
1297 and (quantityreceived < quantity or quantityreceived is NULL)
1298 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber=
1299 aqorders.biblioitemnumber
1300 group by aqorders.biblioitemnumber
1305 my $sth=$dbh->prepare($query);
1307 while (my $data=$sth->fetchrow_hashref){
1313 return($i,@results);
1317 #gets all orders from a certain supplier, orders them alphabetically
1320 my $query="Select * from aqorders,biblio,biblioitems where booksellerid='$supid'
1321 and (cancelledby is NULL or cancelledby = '')
1322 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber=
1323 aqorders.biblioitemnumber and
1324 aqorders.quantityreceived>0
1325 and aqorders.datereceived >=now()
1326 group by aqorders.biblioitemnumber
1331 my $sth=$dbh->prepare($query);
1333 while (my $data=$sth->fetchrow_hashref){
1339 return($i,@results);
1343 my ($search,$biblio,$catview) = @_;
1344 my $dbh = C4Connect;
1345 my $query = "Select *,biblio.title from aqorders,biblioitems,biblio
1346 where aqorders.biblioitemnumber = biblioitems.biblioitemnumber
1347 and biblio.biblionumber=aqorders.biblionumber
1348 and ((datecancellationprinted is NULL)
1349 or (datecancellationprinted = '0000-00-00'))
1351 my @data = split(' ',$search);
1353 for (my $i = 0; $i < $count; $i++) {
1354 $query .= "(biblio.title like '$data[$i]%' or biblio.title like '% $data[$i]%') and ";
1356 $query=~ s/ and $//;
1357 $query.=" ) or biblioitems.isbn='$search'
1358 or (aqorders.ordernumber='$search' and aqorders.biblionumber='$biblio')) ";
1359 if ($catview ne 'yes'){
1360 $query.=" and (quantityreceived < quantity or quantityreceived is NULL)";
1362 $query.=" group by aqorders.ordernumber";
1363 my $sth=$dbh->prepare($query);
1367 while (my $data=$sth->fetchrow_hashref){
1368 my $sth2=$dbh->prepare("Select * from biblio where
1369 biblionumber='$data->{'biblionumber'}'");
1371 my $data2=$sth2->fetchrow_hashref;
1373 $data->{'author'}=$data2->{'author'};
1374 $data->{'seriestitle'}=$data2->{'seriestitle'};
1375 $sth2=$dbh->prepare("Select * from aqorderbreakdown where
1376 ordernumber=$data->{'ordernumber'}");
1378 $data2=$sth2->fetchrow_hashref;
1380 $data->{'branchcode'}=$data2->{'branchcode'};
1381 $data->{'bookfundid'}=$data2->{'bookfundid'};
1387 return($i,@results);
1392 my ($searchstring)=@_;
1394 my $query="Select * from aqbooksellers where name like '%$searchstring%' or
1395 id = '$searchstring'";
1396 my $sth=$dbh->prepare($query);
1400 while (my $data=$sth->fetchrow_hashref){
1406 return($i,@results);
1412 my $query="Select * from aqorderbreakdown where ordernumber='$id'";
1413 my $sth=$dbh->prepare($query);
1417 while (my $data=$sth->fetchrow_hashref){
1423 return($i,\@results);
1427 my ($basketno,$supplier)=@_;
1429 my $query="Select *,biblio.title from aqorders,biblio,biblioitems
1430 where basketno='$basketno'
1431 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber
1432 =aqorders.biblioitemnumber
1433 and (datecancellationprinted is NULL or datecancellationprinted =
1435 if ($supplier ne ''){
1436 $query.=" and aqorders.booksellerid='$supplier'";
1438 $query.=" group by aqorders.ordernumber";
1439 my $sth=$dbh->prepare($query);
1444 while (my $data=$sth->fetchrow_hashref){
1450 return($i,@results);
1455 my $query="Select max(basketno) from aqorders";
1456 my $sth=$dbh->prepare($query);
1458 my $data=$sth->fetchrow_arrayref;
1459 my $basket=$$data[0];
1468 my $query="Select * from aqbookfund,aqbudget where aqbookfund.bookfundid
1469 =aqbudget.bookfundid
1470 group by aqbookfund.bookfundid order by bookfundname";
1471 my $sth=$dbh->prepare($query);
1475 while (my $data=$sth->fetchrow_hashref){
1481 return($i,@results);
1485 my $dbh = C4Connect;
1486 my $query = "Select * from branches";
1487 my $sth = $dbh->prepare($query);
1492 while (my $data = $sth->fetchrow_hashref) {
1493 $results[$i] = $data;
1499 return($i, @results);
1502 sub bookfundbreakdown {
1505 my $query="Select quantity,datereceived,freight,unitprice,listprice,ecost,quantityreceived,subscription
1506 from aqorders,aqorderbreakdown where bookfundid='$id' and
1507 aqorders.ordernumber=aqorderbreakdown.ordernumber
1508 and (datecancellationprinted is NULL or
1509 datecancellationprinted='0000-00-00')";
1510 my $sth=$dbh->prepare($query);
1514 while (my $data=$sth->fetchrow_hashref){
1515 if ($data->{'subscription'} == 1){
1516 $spent+=$data->{'quantity'}*$data->{'unitprice'};
1518 my $leftover=$data->{'quantity'}-$data->{'quantityreceived'};
1519 $comtd+=($data->{'ecost'})*$leftover;
1520 $spent+=($data->{'unitprice'})*$data->{'quantityreceived'};
1525 return($spent,$comtd);
1531 my $dbh = &C4Connect;
1532 my $query = "Select max(biblionumber) from biblio";
1533 my $sth = $dbh->prepare($query);
1535 my $data = $sth->fetchrow_arrayref;
1536 my $bibnum = $$data[0] + 1;
1539 $biblio->{'title'} = $dbh->quote($biblio->{'title'});
1540 $biblio->{'author'} = $dbh->quote($biblio->{'author'});
1541 $biblio->{'copyright'} = $dbh->quote($biblio->{'copyright'});
1542 $biblio->{'seriestitle'} = $dbh->quote($biblio->{'seriestitle'});
1543 $biblio->{'notes'} = $dbh->quote($biblio->{'notes'});
1544 $biblio->{'abstract'} = $dbh->quote($biblio->{'abstract'});
1545 if ($biblio->{'seriestitle'}) { $series = 1 };
1548 $query = "insert into biblio set
1549 biblionumber = $bibnum,
1550 title = $biblio->{'title'},
1551 author = $biblio->{'author'},
1552 copyrightdate = $biblio->{'copyright'},
1554 seriestitle = $biblio->{'seriestitle'},
1555 notes = $biblio->{'notes'},
1556 abstract = $biblio->{'abstract'}";
1558 $sth = $dbh->prepare($query);
1569 my $dbh = C4Connect;
1573 $biblio->{'title'} = $dbh->quote($biblio->{'title'});
1574 $biblio->{'author'} = $dbh->quote($biblio->{'author'});
1575 $biblio->{'abstract'} = $dbh->quote($biblio->{'abstract'});
1576 $biblio->{'copyrightdate'} = $dbh->quote($biblio->{'copyrightdate'});
1577 $biblio->{'seriestitle'} = $dbh->quote($biblio->{'serirestitle'});
1578 $biblio->{'serial'} = $dbh->quote($biblio->{'serial'});
1579 $biblio->{'unititle'} = $dbh->quote($biblio->{'unititle'});
1580 $biblio->{'notes'} = $dbh->quote($biblio->{'notes'});
1582 $query = "Update biblio set
1583 title = $biblio->{'title'},
1584 author = $biblio->{'author'},
1585 abstract = $biblio->{'abstract'},
1586 copyrightdate = $biblio->{'copyrightdate'},
1587 seriestitle = $biblio->{'seriestitle'},
1588 serial = $biblio->{'serial'},
1589 unititle = $biblio->{'unititle'},
1590 notes = $biblio->{'notes'}
1591 where biblionumber = $biblio->{'biblionumber'}";
1592 $sth = $dbh->prepare($query);
1598 return($biblio->{'biblionumber'});
1603 my ($bibnum, $subtitle) = @_;
1604 my $dbh = C4Connect;
1605 my $query = "update bibliosubtitle set
1606 subtitle = '$subtitle'
1607 where biblionumber = $bibnum";
1608 my $sth = $dbh->prepare($query);
1617 my ($bibnum, $author) = @_;
1618 my $dbh = C4Connect;
1619 my $query = "Delete from additionalauthors where biblionumber = $bibnum";
1620 my $sth = $dbh->prepare($query);
1625 if ($author ne '') {
1626 $query = "Insert into additionalauthors set
1628 biblionumber = '$bibnum'";
1629 $sth = $dbh->prepare($query);
1637 } # sub modaddauthor
1641 my ($bibnum, $force, @subject) = @_;
1642 my $dbh = C4Connect;
1643 my $count = @subject;
1645 for (my $i = 0; $i < $count; $i++) {
1646 $subject[$i] =~ s/^ //g;
1647 $subject[$i] =~ s/ $//g;
1648 my $query = "select * from catalogueentry
1649 where entrytype = 's'
1650 and catalogueentry = '$subject[$i]'";
1651 my $sth = $dbh->prepare($query);
1654 if (my $data = $sth->fetchrow_hashref) {
1656 if ($force eq $subject[$i]) {
1658 # subject not in aut, chosen to force anway
1659 # so insert into cataloguentry so its in auth file
1660 $query = "Insert into catalogueentry
1661 (entrytype,catalogueentry)
1662 values ('s','$subject[$i]')";
1663 my $sth2 = $dbh->prepare($query);
1670 $error = "$subject[$i]\n does not exist in the subject authority file";
1671 $query = "Select * from catalogueentry
1672 where entrytype = 's'
1673 and (catalogueentry like '$subject[$i] %'
1674 or catalogueentry like '% $subject[$i] %'
1675 or catalogueentry like '% $subject[$i]')";
1676 my $sth2 = $dbh->prepare($query);
1679 while (my $data = $sth2->fetchrow_hashref) {
1680 $error = $error."<br>$data->{'catalogueentry'}";
1689 my $query = "Delete from bibliosubject where biblionumber = $bibnum";
1690 my $sth = $dbh->prepare($query);
1695 for (my $i = 0; $i < $count; $i++) {
1696 $sth = $dbh->prepare("Insert into bibliosubject
1697 values ('$subject[$i]', $bibnum)");
1710 my ($biblioitem) = @_;
1711 my $dbh = C4Connect;
1714 $biblioitem->{'itemtype'} = $dbh->quote($biblioitem->{'itemtype'});
1715 $biblioitem->{'url'} = $dbh->quote($biblioitem->{'url'});
1716 $biblioitem->{'isbn'} = $dbh->quote($biblioitem->{'isbn'});
1717 $biblioitem->{'publishercode'} = $dbh->quote($biblioitem->{'publishercode'});
1718 $biblioitem->{'publicationyear'} = $dbh->quote($biblioitem->{'publicationyear'});
1719 $biblioitem->{'classification'} = $dbh->quote($biblioitem->{'classification'});
1720 $biblioitem->{'dewey'} = $dbh->quote($biblioitem->{'dewey'});
1721 $biblioitem->{'subclass'} = $dbh->quote($biblioitem->{'subclass'});
1722 $biblioitem->{'illus'} = $dbh->quote($biblioitem->{'illus'});
1723 $biblioitem->{'pages'} = $dbh->quote($biblioitem->{'pages'});
1724 $biblioitem->{'volumeddesc'} = $dbh->quote($biblioitem->{'volumeddesc'});
1725 $biblioitem->{'notes'} = $dbh->quote($biblioitem->{'notes'});
1726 $biblioitem->{'size'} = $dbh->quote($biblioitem->{'size'});
1727 $biblioitem->{'place'} = $dbh->quote($biblioitem->{'place'});
1729 $query = "Update biblioitems set
1730 itemtype = $biblioitem->{'itemtype'},
1731 url = $biblioitem->{'url'},
1732 isbn = $biblioitem->{'isbn'},
1733 publishercode = $biblioitem->{'publishercode'},
1734 publicationyear = $biblioitem->{'publicationyear'},
1735 classification = $biblioitem->{'classification'},
1736 dewey = $biblioitem->{'dewey'},
1737 subclass = $biblioitem->{'subclass'},
1738 illus = $biblioitem->{'illus'},
1739 pages = $biblioitem->{'pages'},
1740 volumeddesc = $biblioitem->{'volumeddesc'},
1741 notes = $biblioitem->{'notes'},
1742 size = $biblioitem->{'size'},
1743 place = $biblioitem->{'place'}
1744 where biblioitemnumber = $biblioitem->{'biblioitemnumber'}";
1753 my ($bibitemnum,$note)=@_;
1755 my $query="update biblioitems set notes='$note' where
1756 biblioitemnumber='$bibitemnum'";
1757 my $sth=$dbh->prepare($query);
1764 my ($biblioitem) = @_;
1765 my $dbh = C4Connect;
1766 my $query = "Select max(biblioitemnumber) from biblioitems";
1767 my $sth = $dbh->prepare($query);
1771 $biblioitem->{'volume'} = $dbh->quote($biblioitem->{'volume'});
1772 $biblioitem->{'number'} = $dbh->quote($biblioitem->{'number'});
1773 $biblioitem->{'classification'} = $dbh->quote($biblioitem->{'classification'});
1774 $biblioitem->{'itemtype'} = $dbh->quote($biblioitem->{'itemtype'});
1775 $biblioitem->{'url'} = $dbh->quote($biblioitem->{'url'});
1776 $biblioitem->{'isbn'} = $dbh->quote($biblioitem->{'isbn'});
1777 $biblioitem->{'issn'} = $dbh->quote($biblioitem->{'issn'});
1778 $biblioitem->{'dewey'} = $dbh->quote($biblioitem->{'dewey'});
1779 $biblioitem->{'subclass'} = $dbh->quote($biblioitem->{'subclass'});
1780 $biblioitem->{'publicationyear'} = $dbh->quote($biblioitem->{'publicationyear'});
1781 $biblioitem->{'publishercode'} = $dbh->quote($biblioitem->{'publishercode'});
1782 $biblioitem->{'volumedate'} = $dbh->quote($biblioitem->{'volumedate'});
1783 $biblioitem->{'volumeddesc'} = $dbh->quote($biblioitem->{'volumeddesc'}); $biblioitem->{'illus'} = $dbh->quote($biblioitem->{'illus'});
1784 $biblioitem->{'illus'} = $dbh->quote($biblioitem->{'illus'});
1785 $biblioitem->{'pages'} = $dbh->quote($biblioitem->{'pages'});
1786 $biblioitem->{'notes'} = $dbh->quote($biblioitem->{'notes'});
1787 $biblioitem->{'size'} = $dbh->quote($biblioitem->{'size'});
1788 $biblioitem->{'place'} = $dbh->quote($biblioitem->{'place'});
1789 $biblioitem->{'lccn'} = $dbh->quote($biblioitem->{'lccn'});
1790 $biblioitem->{'marc'} = $dbh->quote($biblioitem->{'marc'});
1793 $data = $sth->fetchrow_arrayref;
1794 $bibitemnum = $$data[0] + 1;
1798 $query = "insert into biblioitems set
1799 biblioitemnumber = $bibitemnum,
1800 biblionumber = $biblioitem->{'biblionumber'},
1801 volume = $biblioitem->{'volume'},
1802 number = $biblioitem->{'number'},
1803 classification = $biblioitem->{'classification'},
1804 itemtype = $biblioitem->{'itemtype'},
1805 url = $biblioitem->{'url'},
1806 isbn = $biblioitem->{'isbn'},
1807 issn = $biblioitem->{'issn'},
1808 dewey = $biblioitem->{'dewey'},
1809 subclass = $biblioitem->{'subclass'},
1810 publicationyear = $biblioitem->{'publicationyear'},
1811 publishercode = $biblioitem->{'publishercode'},
1812 volumedate = $biblioitem->{'volumedate'},
1813 volumeddesc = $biblioitem->{'volumeddesc'},
1814 illus = $biblioitem->{'illus'},
1815 pages = $biblioitem->{'pages'},
1816 notes = $biblioitem->{'notes'},
1817 size = $biblioitem->{'size'},
1818 lccn = $biblioitem->{'lccn'},
1819 marc = $biblioitem->{'marc'},
1820 place = $biblioitem->{'place'}";
1822 $sth = $dbh->prepare($query);
1827 return($bibitemnum);
1833 my $query="insert into bibliosubject (biblionumber) values
1835 my $sth=$dbh->prepare($query);
1843 my ($bibnum, $subtitle) = @_;
1844 my $dbh = C4Connect;
1845 $subtitle = $dbh->quote($subtitle);
1846 my $query = "insert into bibliosubtitle set
1847 biblionumber = $bibnum,
1848 subtitle = $subtitle";
1849 my $sth = $dbh->prepare($query);
1858 my ($bibnum,$title,$ordnum,$basket,$quantity,$listprice,$supplier,$who,$notes,$bookfund,$bibitemnum,$rrp,$ecost,$gst,$budget,$cost,$sub,$invoice)=@_;
1859 if ($budget eq 'now'){
1862 $budget="'2001-07-01'";
1870 my $query="insert into aqorders (biblionumber,title,basketno,
1871 quantity,listprice,booksellerid,entrydate,requisitionedby,authorisedby,notes,
1872 biblioitemnumber,rrp,ecost,gst,unitprice,subscription,booksellerinvoicenumber)
1875 ($bibnum,'$title',$basket,$quantity,$listprice,'$supplier',now(),
1876 '$who','$who','$notes',$bibitemnum,'$rrp','$ecost','$gst','$cost',
1877 '$sub','$invoice')";
1878 my $sth=$dbh->prepare($query);
1882 $query="select * from aqorders where
1883 biblionumber=$bibnum and basketno=$basket and ordernumber >=$ordnum";
1884 $sth=$dbh->prepare($query);
1886 my $data=$sth->fetchrow_hashref;
1888 $ordnum=$data->{'ordernumber'};
1889 $query="insert into aqorderbreakdown (ordernumber,bookfundid) values
1890 ($ordnum,'$bookfund')";
1891 $sth=$dbh->prepare($query);
1899 my ($bibnum,$ordnum)=@_;
1901 my $query="update aqorders set datecancellationprinted=now()
1902 where biblionumber='$bibnum' and
1903 ordernumber='$ordnum'";
1904 my $sth=$dbh->prepare($query);
1908 my $count=itemcount($bibnum);
1916 my ($title,$ordnum,$quantity,$listprice,$bibnum,$basketno,$supplier,$who,$notes,$bookfund,$bibitemnum,$rrp,$ecost,$gst,$budget,$cost,$invoice)=@_;
1918 my $query="update aqorders set title='$title',
1919 quantity='$quantity',listprice='$listprice',basketno='$basketno',
1920 rrp='$rrp',ecost='$ecost',unitprice='$cost',
1921 booksellerinvoicenumber='$invoice'
1923 ordernumber=$ordnum and biblionumber=$bibnum";
1924 my $sth=$dbh->prepare($query);
1928 $query="update aqorderbreakdown set bookfundid=$bookfund where
1929 ordernumber=$ordnum";
1930 $sth=$dbh->prepare($query);
1939 my $query="Select max(ordernumber) from aqorders";
1940 my $sth=$dbh->prepare($query);
1942 my $data=$sth->fetchrow_arrayref;
1943 my $ordnum=$$data[0];
1951 my ($biblio,$ordnum,$quantrec,$user,$cost,$invoiceno,$bibitemno,$freight,$bookfund,$rrp)=@_;
1953 my $query="update aqorders set quantityreceived='$quantrec',
1954 datereceived=now(),booksellerinvoicenumber='$invoiceno',
1955 biblioitemnumber=$bibitemno,unitprice='$cost',freight='$freight',
1957 where biblionumber=$biblio and ordernumber=$ordnum
1960 my $sth=$dbh->prepare($query);
1963 $query="update aqorderbreakdown set bookfundid=$bookfund where
1964 ordernumber=$ordnum";
1965 $sth=$dbh->prepare($query);
1972 my($biblio,$ordnum,$user,$cost,$bookfund,$rrp)=@_;
1974 my $query="update aqorders set
1975 unitprice='$cost', rrp='$rrp'
1976 where biblionumber=$biblio and ordernumber=$ordnum
1979 my $sth=$dbh->prepare($query);
1982 $query="update aqorderbreakdown set bookfundid=$bookfund where
1983 ordernumber=$ordnum";
1984 $sth=$dbh->prepare($query);
1992 my ($currency,$price)=@_;
1994 my $query="Select rate from currency where currency='$currency'";
1995 my $sth=$dbh->prepare($query);
1997 my $data=$sth->fetchrow_hashref;
2000 my $cur=$data->{'rate'};
2004 my $price=$price / $cur;
2010 my $query="Select * from currency";
2011 my $sth=$dbh->prepare($query);
2015 while (my $data=$sth->fetchrow_hashref){
2021 return($i,\@results);
2027 my $query="Select * from currency where currency='$cur'";
2028 my $sth=$dbh->prepare($query);
2031 my $data=$sth->fetchrow_hashref;
2037 sub updatecurrencies {
2038 my ($currency,$rate)=@_;
2040 my $query="update currency set rate=$rate where currency='$currency'";
2041 my $sth=$dbh->prepare($query);
2050 my $query="Update aqbooksellers set
2051 name='$data->{'name'}',address1='$data->{'address1'}',address2='$data->{'address2'}',
2052 address3='$data->{'address3'}',address4='$data->{'address4'}',postal='$data->{'postal'}',
2053 phone='$data->{'phone'}',fax='$data->{'fax'}',url='$data->{'url'}',
2054 contact='$data->{'contact'}',contpos='$data->{'contpos'}',
2055 contphone='$data->{'contphone'}', contfax='$data->{'contfax'}', contaltphone=
2056 '$data->{'contaltphone'}', contemail='$data->{'contemail'}', contnotes=
2057 '$data->{'contnotes'}', active=$data->{'active'},
2058 listprice='$data->{'listprice'}', invoiceprice='$data->{'invoiceprice'}',
2059 gstreg=$data->{'gstreg'}, listincgst=$data->{'listincgst'},
2060 invoiceincgst=$data->{'invoiceincgst'}, specialty='$data->{'specialty'}',
2061 discount='$data->{'discount'}',invoicedisc='$data->{'invoicedisc'}',
2062 nocalc='$data->{'nocalc'}'
2063 where id='$data->{'id'}'";
2064 my $sth=$dbh->prepare($query);
2074 my $sth=$dbh->prepare("Select max(id) from aqbooksellers");
2076 my $data2=$sth->fetchrow_hashref;
2078 $data2->{'max(id)'}++;
2079 $sth=$dbh->prepare("Insert into aqbooksellers (id) values ($data2->{'max(id)'})");
2082 $data->{'id'}=$data2->{'max(id)'};
2085 return($data->{'id'});
2090 my ($item, @barcodes) = @_;
2091 my $dbh = C4Connect;
2092 my $query = "Select max(itemnumber) from items";
2093 my $sth = $dbh->prepare($query);
2099 $data = $sth->fetchrow_hashref;
2100 $itemnumber = $data->{'max(itemnumber)'} + 1;
2103 $item->{'booksellerid'} = $dbh->quote($item->{'booksellerid'});
2104 $item->{'homebranch'} = $dbh->quote($item->{'homebranch'});
2105 $item->{'price'} = $dbh->quote($item->{'price'});
2106 $item->{'replacementprice'} = $dbh->quote($item->{'replacementprice'});
2107 $item->{'itemnotes'} = $dbh->quote($item->{'itemnotes'});
2109 foreach my $barcode (@barcodes) {
2110 $barcode = uc($barcode);
2111 $barcode = $dbh->quote($barcode);
2112 $query = "Insert into items set
2113 itemnumber = $itemnumber,
2114 biblionumber = $item->{'biblionumber'},
2115 biblioitemnumber = $item->{'biblioitemnumber'},
2117 booksellerid = $item->{'booksellerid'},
2118 dateaccessioned = NOW(),
2119 homebranch = $item->{'homebranch'},
2120 holdingbranch = $item->{'homebranch'},
2121 price = $item->{'price'},
2122 replacementprice = $item->{'replacementprice'},
2123 replacementpricedate = NOW(),
2124 itemnotes = $item->{'itemnotes'}";
2126 if ($item->{'loan'}) {
2128 notforloan = $item->{'loan'}";
2131 $sth = $dbh->prepare($query);
2134 $error .= $sth->errstr;
2145 my ($count,@barcodes)=@_;
2148 for (my $i=0;$i<$count;$i++){
2149 $barcodes[$i]=uc $barcodes[$i];
2150 my $query="Select * from items where barcode='$barcodes[$i]'";
2151 my $sth=$dbh->prepare($query);
2153 if (my $data=$sth->fetchrow_hashref){
2154 $error.=" Duplicate Barcode: $barcodes[$i]";
2163 my ($loan,$itemnum,$bibitemnum,$barcode,$notes,$homebranch,$lost,$wthdrawn,$replacement)=@_;
2165 my $query="update items set biblioitemnumber=$bibitemnum,
2166 barcode='$barcode',itemnotes='$notes'
2167 where itemnumber=$itemnum";
2168 if ($barcode eq ''){
2169 $query="update items set biblioitemnumber=$bibitemnum,notforloan=$loan where itemnumber=$itemnum";
2172 $query="update items set biblioitemnumber=$bibitemnum,
2173 barcode='$barcode',itemnotes='$notes',homebranch='$homebranch',
2174 itemlost='$lost',wthdrawn='$wthdrawn' where itemnumber=$itemnum";
2176 if ($replacement ne ''){
2177 $query=~ s/ where/,replacementprice='$replacement' where/;
2180 my $sth=$dbh->prepare($query);
2187 my($price,$rrp,$itemnum)=@_;
2189 my $query="update items set price='$price',replacementprice='$rrp'
2190 where itemnumber=$itemnum";
2191 my $sth=$dbh->prepare($query);
2197 my ($bibitemnum)=@_;
2199 my $query="Select count(*) from items where biblioitemnumber='$bibitemnum'";
2200 my $sth=$dbh->prepare($query);
2202 my $data=$sth->fetchrow_hashref;
2205 return($data->{'count(*)'});
2209 my ($biblionumber)=@_;
2211 my $query="Select * from biblioitems,items,itemtypes where
2212 biblioitems.biblionumber=$biblionumber
2213 and biblioitems.biblioitemnumber=items.biblioitemnumber and
2214 itemtypes.itemtype=biblioitems.itemtype
2215 order by items.biblioitemnumber";
2216 my $sth=$dbh->prepare($query);
2220 while (my $data=$sth->fetchrow_hashref){
2230 my ($bibitemnum,$itemtype)=@_;
2232 my $query="Select * from biblioitems where biblioitemnumber=$bibitemnum
2233 and itemtype='$itemtype'";
2234 my $sth=$dbh->prepare($query);
2237 if (my $data=$sth->fetchrow_hashref){
2248 my $query="select * from items where itemnumber=$itemnum";
2249 my $sth=$dbh->prepare($query);
2251 my @data=$sth->fetchrow_array;
2253 $query="Insert into deleteditems values (";
2254 foreach my $temp (@data){
2255 $query=$query."'$temp',";
2259 $sth=$dbh->prepare($query);
2262 $query = "Delete from items where itemnumber=$itemnum";
2263 $sth=$dbh->prepare($query);
2268 sub deletebiblioitem {
2269 my ($biblioitemnumber) = @_;
2270 my $dbh = C4Connect;
2271 my $query = "Select * from biblioitems
2272 where biblioitemnumber = $biblioitemnumber";
2273 my $sth = $dbh->prepare($query);
2278 if (@results = $sth->fetchrow_array) {
2280 $query = "Insert into deletedbiblioitems values (";
2281 foreach my $value (@results) {
2282 $value = $dbh->quote($value);
2283 $query .= "$value,";
2286 $query =~ s/\,$/\)/;
2289 $query = "Delete from biblioitems
2290 where biblioitemnumber = $biblioitemnumber";
2296 # Now delete all the items attached to the biblioitem
2298 $query = "Select * from items where biblioitemnumber = $biblioitemnumber";
2299 $sth = $dbh->prepare($query);
2303 while (@results = $sth->fetchrow_array) {
2305 $query = "Insert into deleteditems values (";
2306 foreach my $value (@results) {
2307 $value = $dbh->quote($value);
2308 $query .= "$value,";
2311 $query =~ s/\,$/\)/;
2317 $query = "Delete from items where biblioitemnumber = $biblioitemnumber";
2321 } # sub deletebiblioitem
2327 my $query="select * from biblio where biblionumber=$biblio";
2328 my $sth=$dbh->prepare($query);
2330 if (my @data=$sth->fetchrow_array){
2332 $query="Insert into deletedbiblio values (";
2333 foreach my $temp (@data){
2334 $temp=~ s/\'/\\\'/g;
2335 $query=$query."'$temp',";
2339 $sth=$dbh->prepare($query);
2342 $query = "Delete from biblio where biblionumber=$biblio";
2343 $sth=$dbh->prepare($query);
2354 my $dbh = C4Connect;
2355 my $query = "select * from itemtypes";
2356 my $sth = $dbh->prepare($query);
2357 # || die "Cannot prepare $query" . $dbh->errstr;
2362 # || die "Cannot execute $query\n" . $sth->errstr;
2363 while (my $data = $sth->fetchrow_hashref) {
2364 @results[$count] = $data;
2370 return($count, @results);
2371 } # sub getitemtypes
2375 my ($biblionumber) = @_;
2376 my $dbh = C4Connect;
2377 my $query = "Select * from biblio where biblionumber = $biblionumber";
2378 my $sth = $dbh->prepare($query);
2379 # || die "Cannot prepare $query\n" . $dbh->errstr;
2384 # || die "Cannot execute $query\n" . $sth->errstr;
2385 while (my $data = $sth->fetchrow_hashref) {
2386 $results[$count] = $data;
2392 return($count, @results);
2397 my ($biblioitemnum) = @_;
2398 my $dbh = C4Connect;
2399 my $query = "Select * from biblioitems where
2400 biblioitemnumber = $biblioitemnum";
2401 my $sth = $dbh->prepare($query);
2407 while (my $data = $sth->fetchrow_hashref) {
2408 $results[$count] = $data;
2414 return($count, @results);
2415 } # sub getbiblioitem
2418 sub getbiblioitembybiblionumber {
2419 my ($biblionumber) = @_;
2420 my $dbh = C4Connect;
2421 my $query = "Select * from biblioitems where biblionumber =
2423 my $sth = $dbh->prepare($query);
2429 while (my $data = $sth->fetchrow_hashref) {
2430 $results[$count] = $data;
2436 return($count, @results);
2440 sub getitemsbybiblioitem {
2441 my ($biblioitemnum) = @_;
2442 my $dbh = C4Connect;
2443 my $query = "Select * from items, biblio where
2444 biblio.biblionumber = items.biblionumber and biblioitemnumber
2446 my $sth = $dbh->prepare($query);
2447 # || die "Cannot prepare $query\n" . $dbh->errstr;
2452 # || die "Cannot execute $query\n" . $sth->errstr;
2453 while (my $data = $sth->fetchrow_hashref) {
2454 $results[$count] = $data;
2460 return($count, @results);
2461 } # sub getitemsbybiblioitem
2466 my $dbh = C4Connect;
2472 $isbn = $dbh->quote($isbn);
2473 $query = "Select biblio.* from biblio, biblioitems where
2474 biblio.biblionumber = biblioitems.biblionumber
2476 $sth = $dbh->prepare($query);
2479 while (my $data = $sth->fetchrow_hashref) {
2480 $results[$count] = $data;
2486 return($count, @results);
2491 my ($keywordlist) = @_;
2492 my $dbh = C4Connect;
2493 my $query = "Select distinct biblio.* from biblio, biblioitems where
2494 biblio.biblionumber = biblioitems.biblionumber and (";
2498 my @keywords = split(/ +/, $keywordlist);
2499 my $keyword = shift(@keywords);
2501 $keyword =~ s/%/\\%/g;
2502 $keyword =~ s/_/\\_/;
2503 $keyword = "%" . $keyword . "%";
2504 $keyword = $dbh->quote($keyword);
2505 $query .= " (url like $keyword)";
2507 foreach $keyword (@keywords) {
2508 $keyword =~ s/%/\\%/;
2509 $keyword =~ s/_/\\_/;
2510 $keyword = "%" . $keyword . "%";
2511 $keyword = $dbh->quote($keyword);
2512 $query .= " or (url like $keyword)";
2516 $sth = $dbh->prepare($query);
2519 while (my $data = $sth->fetchrow_hashref) {
2520 $results[$count] = $data;
2526 return($count, @results);
2527 } # sub websitesearch
2532 my $dbh = C4Connect;
2535 $website->{'biblionumber'} = $dbh->quote($website->{'biblionumber'});
2536 $website->{'title'} = $dbh->quote($website->{'title'});
2537 $website->{'description'} = $dbh->quote($website->{'description'});
2538 $website->{'url'} = $dbh->quote($website->{'url'});
2540 $query = "Insert into websites set
2541 biblionumber = $website->{'biblionumber'},
2542 title = $website->{'title'},
2543 description = $website->{'description'},
2544 url = $website->{'url'}";
2554 my $dbh = C4Connect;
2557 $website->{'title'} = $dbh->quote($website->{'title'});
2558 $website->{'description'} = $dbh->quote($website->{'description'});
2559 $website->{'url'} = $dbh->quote($website->{'url'});
2561 $query = "Update websites set
2562 title = $website->{'title'},
2563 description = $website->{'description'},
2564 url = $website->{'url'}
2565 where websitenumber = $website->{'websitenumber'}";
2570 } # sub updatewebsite
2574 my ($websitenumber) = @_;
2575 my $dbh = C4Connect;
2576 my $query = "Delete from websites where websitenumber = $websitenumber";
2581 } # sub deletewebsite
2583 END { } # module clean-up code here (global destructor)