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
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) = @_;
231 # adding main table, and retrieving bibid
232 $dbh->do("lock tables marc_biblio WRITE");
233 my $sth=$dbh->prepare("insert into marc_biblio (datecreated,origincode) values (now(),?)");
234 $sth->execute($marcstructure->{origincode});
235 $sth=$dbh->prepare("select max(bibid) from marc_biblio");
237 ($marcstructure->{bibid})=$sth->fetchrow;
238 print "BIBID :::".$marcstructure->{bibid}."\n";
240 $dbh->do("unlock tables");
241 # now, add subfields...
242 foreach $tags ($marcstructure->{tags}) {
243 foreach $i (keys %{$tags}) {
244 foreach $j (keys %{$tags->{$i}->{subfields}}) {
245 &addSubfield($marcstructure->{bibid},
247 $tags->{$i}->{tagorder},
248 $tags->{$i}->{subfields}->{$j}->{mark},
249 $tags->{$i}->{subfields}->{$j}->{subfieldorder},
250 $tags->{$i}->{subfields}->{$j}->{value}
252 print $tags->{$i}->{tag}."//".$tags->{$i}->{subfields}->{$j}->{value}."\n";
259 # Update the biblio with biblionumber $biblio->{'biblionumber'}
260 # I guess this routine should search through all marc records for a record that
261 # has the same biblionumber stored in it, and modify the MARC record as well as
264 # Also, this subroutine should search through the $biblio object and compare it
265 # to the existing record and _LOG ALL CHANGES MADE_ in some way. I'd like for
266 # this logging feature to be usable to undo changes easily.
268 my ($env, $biblio) = @_;
270 my $biblionumber=$biblio->{'biblionumber'};
272 my $sth=$dbh->prepare("select * from biblio where biblionumber=$biblionumber");
274 my $origbiblio=$sth->fetchrow_hashref;
275 $sth=$dbh->prepare("select subtitle from bibliosubtitle where biblionumber=$biblionumber");
277 my ($subtitle)=$sth->fetchrow;
278 $origbiblio->{'subtitle'}=$subtitle;
279 $sth=$dbh->prepare("select author from additionalauthors where biblionumber=$biblionumber");
281 my $origadditionalauthors;
282 while (my ($author) = $sth->fetchrow) {
283 push (@{$origbiblio->{'additionalauthors'}}, $author);
284 $origadditionalauthors->{$author}=1;
286 $sth=$dbh->prepare("select subject from bibliosubject where biblionumber=$biblionumber");
289 while (my ($subject) = $sth->fetchrow) {
290 push (@{$origbiblio->{'subjects'}}, $subject);
291 $origsubjects->{$subject}=1;
295 # Obtain a list of MARC Record_ID's that are tied to this biblio
296 $sth=$dbh->prepare("select bibid from marc_subfield_table where tag='090' and subfieldvalue=$biblionumber and subfieldcode='c'");
299 while (my ($bibid) = $sth->fetchrow) {
300 push(@marcrecords, $bibid);
306 if ($biblio->{'author'} ne $origbiblio->{'author'}) {
307 my $q_author=$dbh->quote($biblio->{'author'});
308 logchange('kohadb', 'change', 'biblio', 'author', $origbiblio->{'author'}, $biblio->{'author'});
309 my $sti=$dbh->prepare("update biblio set author=$q_author where biblionumber=$biblio->{'biblionumber'}");
311 foreach $bibid (@marcrecords) {
312 logchange('marc', 'change', $bibid, '100', 'a', $origbiblio->{'author'}, $biblio->{'author'});
313 changeSubfield($bibid, '100', 'a', $origbiblio->{'author'}, $biblio->{'author'});
316 if ($biblio->{'title'} ne $origbiblio->{'title'}) {
317 my $q_title=$dbh->quote($biblio->{'title'});
318 logchange('kohadb', 'change', 'biblio', 'title', $origbiblio->{'title'}, $biblio->{'title'});
319 my $sti=$dbh->prepare("update biblio set title=$q_title where biblionumber=$biblio->{'biblionumber'}");
321 foreach $Record_ID (@marcrecords) {
322 logchange('marc', 'change', $Record_ID, '245', 'a', $origbiblio->{'title'}, $biblio->{'title'});
323 changeSubfield($Record_ID, '245', 'a', $origbiblio->{'title'}, $biblio->{'title'});
326 if ($biblio->{'subtitle'} ne $origbiblio->{'subtitle'}) {
327 my $q_subtitle=$dbh->quote($biblio->{'subtitle'});
328 logchange('kohadb', 'change', 'biblio', 'subtitle', $origbiblio->{'subtitle'}, $biblio->{'subtitle'});
329 my $sti=$dbh->prepare("update bibliosubtitle set subtitle=$q_subtitle where biblionumber=$biblio->{'biblionumber'}");
331 foreach $Record_ID (@marcrecords) {
332 logchange('marc', 'change', $Record_ID, '245', 'b', $origbiblio->{'subtitle'}, $biblio->{'subtitle'});
333 changeSubfield($Record_ID, '245', 'b', $origbiblio->{'subtitle'}, $biblio->{'subtitle'});
336 if ($biblio->{'unititle'} ne $origbiblio->{'unititle'}) {
337 my $q_unititle=$dbh->quote($biblio->{'unititle'});
338 logchange('kohadb', 'change', 'biblio', 'unititle', $origbiblio->{'unititle'}, $biblio->{'unititle'});
339 my $sti=$dbh->prepare("update biblio set unititle=$q_unititle where biblionumber=$biblio->{'biblionumber'}");
342 if ($biblio->{'notes'} ne $origbiblio->{'notes'}) {
343 my $q_notes=$dbh->quote($biblio->{'notes'});
344 logchange('kohadb', 'change', 'biblio', 'notes', $origbiblio->{'notes'}, $biblio->{'notes'});
345 my $sti=$dbh->prepare("update biblio set notes=$q_notes where biblionumber=$biblio->{'biblionumber'}");
347 foreach $Record_ID (@marcrecords) {
348 logchange('marc', 'change', $Record_ID, '500', 'a', $origbiblio->{'notes'}, $biblio->{'notes'});
349 changeSubfield($Record_ID, '500', 'a', $origbiblio->{'notes'}, $biblio->{'notes'});
352 if ($biblio->{'serial'} ne $origbiblio->{'serial'}) {
353 my $q_serial=$dbh->quote($biblio->{'serial'});
354 logchange('kohadb', 'change', 'biblio', 'serial', $origbiblio->{'serial'}, $biblio->{'serial'});
355 my $sti=$dbh->prepare("update biblio set serial=$q_serial where biblionumber=$biblio->{'biblionumber'}");
358 if ($biblio->{'seriestitle'} ne $origbiblio->{'seriestitle'}) {
359 my $q_seriestitle=$dbh->quote($biblio->{'seriestitle'});
360 logchange('kohadb', 'change', 'biblio', 'seriestitle', $origbiblio->{'seriestitle'}, $biblio->{'seriestitle'});
361 my $sti=$dbh->prepare("update biblio set seriestitle=$q_seriestitle where biblionumber=$biblio->{'biblionumber'}");
363 foreach $Record_ID (@marcrecords) {
364 logchange('marc', 'change', $Record_ID, '440', 'a', $origbiblio->{'seriestitle'}, $biblio->{'seriestitle'});
365 changeSubfield($Record_ID, '440', 'a', $origbiblio->{'seriestitle'}, $biblio->{'seriestitle'});
368 if ($biblio->{'copyrightdate'} ne $origbiblio->{'copyrightdate'}) {
369 my $q_copyrightdate=$dbh->quote($biblio->{'copyrightdate'});
370 logchange('kohadb', 'change', 'biblio', 'copyrightdate', $origbiblio->{'copyrightdate'}, $biblio->{'copyrightdate'});
371 my $sti=$dbh->prepare("update biblio set copyrightdate=$q_copyrightdate where biblionumber=$biblio->{'biblionumber'}");
373 foreach $Record_ID (@marcrecords) {
374 logchange('marc', 'change', $Record_ID, '260', 'c', "c$origbiblio->{'notes'}", "c$biblio->{'notes'}");
375 changeSubfield($Record_ID, '260', 'c', "c$origbiblio->{'notes'}", "c$biblio->{'notes'}");
379 # Check for subject heading changes
383 foreach $newsubject (@{$biblio->{'subject'}}) {
384 $subjects->{$newsubject}=1;
385 if ($origsubjects->{$newsubject}) {
386 $subjects->{$newsubject}=2;
388 my $q_newsubject=$dbh->quote($newsubject);
389 my $sth=$dbh->prepare("insert into bibliosubject (subject,biblionumber) values ($q_newsubject, $biblionumber)");
391 logchange('kohadb', 'add', 'biblio', 'subject', $newsubject);
393 $subfields->{1}->{'Subfield_Mark'}='a';
394 $subfields->{1}->{'Subfield_Value'}=$newsubject;
397 foreach $Record_ID (@marcrecords) {
398 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
399 logchange('marc', 'add', $Record_ID, '650', 'a', $newsubject);
404 foreach $origsubject (keys %$origsubjects) {
405 if ($subjects->{$origsubject} == 1) {
406 my $q_origsubject=$dbh->quote($origsubject);
407 logchange('kohadb', 'delete', 'biblio', '$biblionumber', 'subject', $origsubject);
408 my $sth=$dbh->prepare("delete from bibliosubject where biblionumber=$biblionumber and subject=$q_origsubject");
414 # At the moment this is just a straight copy of the subject code. Needs heavy
415 # modification to work for additional authors, obviously.
416 # Check for additional author changes
418 my $newadditionalauthor='';
419 my $additionalauthors;
420 foreach $newadditionalauthor (@{$biblio->{'additionalauthor'}}) {
421 $additionalauthors->{$newadditionalauthor}=1;
422 if ($origadditionalauthors->{$newadditionalauthor}) {
423 $additionalauthors->{$newadditionalauthor}=2;
425 my $q_newadditionalauthor=$dbh->quote($newadditionalauthor);
426 my $sth=$dbh->prepare("insert into biblioadditionalauthors (additionalauthor,biblionumber) values ($q_newadditionalauthor, $biblionumber)");
428 logchange('kohadb', 'add', 'biblio', 'additionalauthor', $newadditionalauthor);
430 $subfields->{1}->{'Subfield_Mark'}='a';
431 $subfields->{1}->{'Subfield_Value'}=$newadditionalauthor;
434 foreach $Record_ID (@marcrecords) {
435 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
436 logchange('marc', 'add', $Record_ID, '650', 'a', $newadditionalauthor);
440 my $origadditionalauthor;
441 foreach $origadditionalauthor (keys %$origadditionalauthors) {
442 if ($additionalauthors->{$origadditionalauthor} == 1) {
443 my $q_origadditionalauthor=$dbh->quote($origadditionalauthor);
444 logchange('kohadb', 'delete', 'biblio', '$biblionumber', 'additionalauthor', $origadditionalauthor);
445 my $sth=$dbh->prepare("delete from biblioadditionalauthors where biblionumber=$biblionumber and additionalauthor=$q_origadditionalauthor");
455 # Subroutine to log changes to databases
456 # Eventually, this subroutine will be used to create a log of all changes made,
457 # with the possibility of "undo"ing some changes
459 if ($database eq 'kohadb') {
465 print STDERR "KOHA: $type $section $item $original $new\n";
466 } elsif ($database eq 'marc') {
471 my $subfield_ID=shift;
474 print STDERR "MARC: $type $Record_ID $tag $mark $subfield_ID $original $new\n";
479 # Subroutine to add a tag to an existing MARC Record. If a new linkage id is
480 # desired, set $env->{'linkage'} to 1. If an existing linkage id should be
481 # set, set $env->{'linkid'} to the link number.
482 my ($env, $Record_ID, $tag, $Indicator1, $Indicator2, $subfields) = @_;
484 ($Indicator1) || ($Indicator1=' ');
485 ($Indicator2) || ($Indicator2=' ');
486 my $firstdigit=substr($tag,0,1);
488 foreach (sort keys %$subfields) {
489 my $Subfield_Mark=$subfields->{$_}->{'Subfield_Mark'};
490 my $Subfield_Value=$subfields->{$_}->{'Subfield_Value'};
491 my $q_Subfield_Value=$dbh->quote($Subfield_Value);
493 my $sth=$dbh->prepare("insert into $firstdigit\XX_Subfield_Table (Subfield_ID, Subfield_Mark, Subfield_Value) values ($Subfield_ID, '$Subfield_Mark', $q_Subfield_Value)");
496 my $sth=$dbh->prepare("insert into $firstdigit\XX_Subfield_Table (Subfield_Mark, Subfield_Value) values ('$Subfield_Mark', $q_Subfield_Value)");
498 my $Subfield_Key=$dbh->{'mysql_insertid'};
499 $Subfield_ID=$Subfield_Key;
500 $sth=$dbh->prepare("update $firstdigit\XX_Subfield_Table set Subfield_ID=$Subfield_ID where Subfield_Key=$Subfield_Key");
504 if (my $linkid=$env->{'linkid'}) {
506 my $sth=$dbh->prepare("insert into $firstdigit\XX_Subfield_Table (Subfield_ID, Subfield_Mark, Subfield_Value) values ($Subfield_ID, '8', '$linkid')");
509 my $sth=$dbh->prepare("insert into $firstdigit\XX_Tag_Table (Indicator1, Indicator2, Tag, Subfield_ID) values ('$Indicator1', '$Indicator2', '$tag', $Subfield_ID)");
511 my $Tag_Key=$dbh->{'mysql_insertid'};
513 $sth=$dbh->prepare("update $firstdigit\XX_Tag_Table set Tag_ID=$Tag_ID where Tag_Key=$Tag_Key");
515 $sth=$dbh->prepare("insert into Bib_Table (Record_ID, Tag_$firstdigit\XX_ID) values ($Record_ID, $Tag_ID)");
517 if ($env->{'linkage'}) {
518 my $sth=$dbh->prepare("insert into $firstdigit\XX_Subfield_Table (Subfield_ID, Subfield_Mark, Subfield_Value) values ($Subfield_ID, '8', '$Tag_ID')");
524 return ($env, $Tag_ID);
528 my ($env, $biblioitem) = @_;
530 my $biblionumber=$biblioitem->{'biblionumber'};
531 my $biblioitemnumber=$biblioitem->{'biblioitemnumber'};
532 my $volume=$biblioitem->{'volume'};
533 my $q_volume=$dbh->quote($volume);
534 my $number=$biblioitem->{'number'};
535 my $q_number=$dbh->quote($number);
536 my $classification=$biblioitem->{'classification'};
537 my $q_classification=$dbh->quote($classification);
538 my $itemtype=$biblioitem->{'itemtype'};
539 my $q_itemtype=$dbh->quote($itemtype);
540 my $isbn=$biblioitem->{'isbn'};
541 my $q_isbn=$dbh->quote($isbn);
542 my $issn=$biblioitem->{'issn'};
543 my $q_issn=$dbh->quote($issn);
544 my $dewey=$biblioitem->{'dewey'};
546 ($dewey == 0) && ($dewey='');
547 my $subclass=$biblioitem->{'subclass'};
548 my $q_subclass=$dbh->quote($subclass);
549 my $publicationyear=$biblioitem->{'publicationyear'};
550 my $publishercode=$biblioitem->{'publishercode'};
551 my $q_publishercode=$dbh->quote($publishercode);
552 my $volumedate=$biblioitem->{'volumedate'};
553 my $q_volumedate=$dbh->quote($volumedate);
554 my $illus=$biblioitem->{'illus'};
555 my $q_illus=$dbh->quote($illus);
556 my $pages=$biblioitem->{'pages'};
557 my $q_pages=$dbh->quote($pages);
558 my $notes=$biblioitem->{'notes'};
559 my $q_notes=$dbh->quote($notes);
560 my $size=$biblioitem->{'size'};
561 my $q_size=$dbh->quote($size);
562 my $place=$biblioitem->{'place'};
563 my $q_place=$dbh->quote($place);
564 my $lccn=$biblioitem->{'lccn'};
565 my $q_lccn=$dbh->quote($lccn);
568 # Unless the $env->{'marconly'} flag is set, update the biblioitems table with
571 unless ($env->{'marconly'}) {
572 #my $sth=$dbh->prepare("lock tables biblioitems write");
574 my $sth=$dbh->prepare("select max(biblioitemnumber) from biblioitems");
576 my ($biblioitemnumber) =$sth->fetchrow;
578 $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)");
580 #my $sth=$dbh->prepare("unlock tables");
585 # Should we check if there is already a biblioitem/marc with the
586 # same isbn/lccn/issn?
588 my $sth=$dbh->prepare("select title,unititle,seriestitle,copyrightdate,notes,author from biblio where biblionumber=$biblionumber");
590 my ($title, $unititle,$seriestitle,$copyrightdate,$biblionotes,$author) = $sth->fetchrow;
591 $sth=$dbh->prepare("select subtitle from bibliosubtitle where biblionumber=$biblionumber");
593 my ($subtitle) = $sth->fetchrow;
594 $sth=$dbh->prepare("select author from additionalauthors where biblionumber=$biblionumber");
596 my @additionalauthors;
597 while (my ($additionalauthor) = $sth->fetchrow) {
598 push (@additionalauthors, $additionalauthor);
600 $sth=$dbh->prepare("select subject from bibliosubject where biblionumber=$biblionumber");
603 while (my ($subject) = $sth->fetchrow) {
604 push (@subjects, $subject);
609 $sth=$dbh->prepare("insert into Resource_Table (Record_ID) values (0)");
611 my $Resource_ID=$dbh->{'mysql_insertid'};
612 my $Record_ID=$Resource_ID;
613 $sth=$dbh->prepare("update Resource_Table set Record_ID=$Record_ID where Resource_ID=$Resource_ID");
619 $subfields->{1}->{'Subfield_Mark'}='a';
620 $subfields->{1}->{'Subfield_Value'}=$title;
622 $subfields->{2}->{'Subfield_Mark'}='b';
623 $subfields->{2}->{'Subfield_Value'}=$subtitle;
626 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
632 $subfields->{1}->{'Subfield_Mark'}='a';
633 $subfields->{1}->{'Subfield_Value'}=$author;
635 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
640 $subfields->{1}->{'Subfield_Mark'}='a';
641 $subfields->{1}->{'Subfield_Value'}=$seriestitle;
643 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
648 $subfields->{1}->{'Subfield_Mark'}='a';
649 $subfields->{1}->{'Subfield_Value'}=$biblionotes;
650 $subfields->{2}->{'Subfield_Mark'}='3';
651 $subfields->{2}->{'Subfield_Value'}='biblio';
653 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
656 foreach (@additionalauthors) {
658 (next) unless ($author);
660 $subfields->{1}->{'Subfield_Mark'}='a';
661 $subfields->{1}->{'Subfield_Value'}=$author;
662 $subfields->{2}->{'Subfield_Mark'}='e';
663 $subfields->{2}->{'Subfield_Value'}='author';
665 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
669 (next) unless ($illus);
671 $subfields->{1}->{'Subfield_Mark'}='a';
672 $subfields->{1}->{'Subfield_Value'}=$illus;
673 $subfields->{2}->{'Subfield_Mark'}='e';
674 $subfields->{2}->{'Subfield_Value'}='illustrator';
676 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
679 foreach (@subjects) {
681 (next) unless ($subject);
683 $subfields->{1}->{'Subfield_Mark'}='a';
684 $subfields->{1}->{'Subfield_Value'}=$subject;
686 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
693 $subfields->{1}->{'Subfield_Mark'}='a';
694 $subfields->{1}->{'Subfield_Value'}=$isbn;
696 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
701 $subfields->{1}->{'Subfield_Mark'}='a';
702 $subfields->{1}->{'Subfield_Value'}=$lccn;
704 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
709 $subfields->{1}->{'Subfield_Mark'}='a';
710 $subfields->{1}->{'Subfield_Value'}=$issn;
712 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
717 $subfields->{1}->{'Subfield_Mark'}='a';
718 $subfields->{1}->{'Subfield_Value'}=$dewey;
720 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
722 # DEWEY subclass and itemtype
725 $subfields->{1}->{'Subfield_Mark'}='a';
726 $subfields->{1}->{'Subfield_Value'}=$itemtype;
727 $subfields->{2}->{'Subfield_Mark'}='b';
728 $subfields->{2}->{'Subfield_Value'}=$subclass;
729 $subfields->{3}->{'Subfield_Mark'}='c';
730 $subfields->{3}->{'Subfield_Value'}=$biblionumber;
731 $subfields->{4}->{'Subfield_Mark'}='d';
732 $subfields->{4}->{'Subfield_Value'}=$biblioitemnumber;
734 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
739 $subfields->{1}->{'Subfield_Mark'}='a';
740 $subfields->{1}->{'Subfield_Value'}=$place;
741 $subfields->{2}->{'Subfield_Mark'}='b';
742 $subfields->{2}->{'Subfield_Value'}=$publishercode;
743 $subfields->{3}->{'Subfield_Mark'}='c';
744 $subfields->{3}->{'Subfield_Value'}=$publicationyear;
745 if ($copyrightdate) {
746 $subfields->{4}->{'Subfield_Mark'}='c';
747 $subfields->{4}->{'Subfield_Value'}="c$copyrightdate";
750 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
753 if ($pages || $size) {
755 $subfields->{1}->{'Subfield_Mark'}='a';
756 $subfields->{1}->{'Subfield_Value'}=$pages;
757 $subfields->{2}->{'Subfield_Mark'}='c';
758 $subfields->{2}->{'Subfield_Value'}=$size;
760 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
763 if ($volume || $number) {
765 $subfields->{1}->{'Subfield_Mark'}='v';
766 $subfields->{1}->{'Subfield_Value'}=$volume;
767 $subfields->{2}->{'Subfield_Mark'}='n';
768 $subfields->{2}->{'Subfield_Value'}=$number;
770 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
775 $subfields->{1}->{'Subfield_Mark'}='a';
776 $subfields->{1}->{'Subfield_Value'}=$notes;
777 $subfields->{2}->{'Subfield_Mark'}='3';
778 $subfields->{2}->{'Subfield_Value'}='biblioitem';
780 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
784 return ($env, $Record_ID);
787 sub updateBiblioItem {
788 # Update the biblioitem with biblioitemnumber $biblioitem->{'biblioitemnumber'}
790 # This routine should also check to see which fields are actually being
791 # modified, and log all changes.
793 my ($env, $biblioitem) = @_;
796 my $biblioitemnumber=$biblioitem->{'biblioitemnumber'};
797 my $sth=$dbh->prepare("select * from biblioitems where biblioitemnumber=$biblioitemnumber");
798 # obi = original biblioitem
799 my $obi=$sth->fetchrow_hashref;
800 $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");
802 my ($Record_ID) = $sth->fetchrow;
803 if ($biblioitem->{'biblionumber'} ne $obi->{'biblionumber'}) {
804 logchange('kohadb', 'change', 'biblioitems', 'biblionumber', $obi->{'biblionumber'}, $biblioitem->{'biblionumber'});
805 my $sth=$dbh->prepare("update biblioitems set biblionumber=$biblioitem->{'biblionumber'} where biblioitemnumber=$biblioitemnumber");
806 logchange('marc', 'change', $Record_ID, '090', 'c', $obi->{'biblionumber'}, $biblioitem->{'biblionumber'});
807 changeSubfield($Record_ID, '090', 'c', $obi->{'biblionumber'}, $biblioitem->{'biblionumber'});
809 if ($biblioitem->{'volume'} ne $obi->{'volume'}) {
810 logchange('kohadb', 'change', 'biblioitems', 'volume', $obi->{'volume'}, $biblioitem->{'volume'});
811 my $q_volume=$dbh->quote($biblioitem->{'volume'});
812 my $sth=$dbh->prepare("update biblioitems set volume=$q_volume where biblioitemnumber=$biblioitemnumber");
813 logchange('marc', 'change', $Record_ID, '440', 'v', $obi->{'volume'}, $biblioitem->{'volume'});
814 changeSubfield($Record_ID, '440', 'v', $obi->{'volume'}, $biblioitem->{'volume'});
816 if ($biblioitem->{'number'} ne $obi->{'number'}) {
817 logchange('kohadb', 'change', 'biblioitems', 'number', $obi->{'number'}, $biblioitem->{'number'});
818 my $q_number=$dbh->quote($biblioitem->{'number'});
819 my $sth=$dbh->prepare("update biblioitems set number=$q_number where biblioitemnumber=$biblioitemnumber");
820 logchange('marc', 'change', $Record_ID, '440', 'v', $obi->{'number'}, $biblioitem->{'number'});
821 changeSubfield($Record_ID, '440', 'v', $obi->{'number'}, $biblioitem->{'number'});
823 if ($biblioitem->{'itemtype'} ne $obi->{'itemtype'}) {
824 logchange('kohadb', 'change', 'biblioitems', 'itemtype', $obi->{'itemtype'}, $biblioitem->{'itemtype'});
825 my $q_itemtype=$dbh->quote($biblioitem->{'itemtype'});
826 my $sth=$dbh->prepare("update biblioitems set itemtype=$q_itemtype where biblioitemnumber=$biblioitemnumber");
827 logchange('marc', 'change', $Record_ID, '090', 'a', $obi->{'itemtype'}, $biblioitem->{'itemtype'});
828 changeSubfield($Record_ID, '090', 'a', $obi->{'itemtype'}, $biblioitem->{'itemtype'});
830 if ($biblioitem->{'isbn'} ne $obi->{'isbn'}) {
831 logchange('kohadb', 'change', 'biblioitems', 'isbn', $obi->{'isbn'}, $biblioitem->{'isbn'});
832 my $q_isbn=$dbh->quote($biblioitem->{'isbn'});
833 my $sth=$dbh->prepare("update biblioitems set isbn=$q_isbn where biblioitemnumber=$biblioitemnumber");
834 logchange('marc', 'change', $Record_ID, '020', 'a', $obi->{'isbn'}, $biblioitem->{'isbn'});
835 changeSubfield($Record_ID, '020', 'a', $obi->{'isbn'}, $biblioitem->{'isbn'});
837 if ($biblioitem->{'issn'} ne $obi->{'issn'}) {
838 logchange('kohadb', 'change', 'biblioitems', 'issn', $obi->{'issn'}, $biblioitem->{'issn'});
839 my $q_issn=$dbh->quote($biblioitem->{'issn'});
840 my $sth=$dbh->prepare("update biblioitems set issn=$q_issn where biblioitemnumber=$biblioitemnumber");
841 logchange('marc', 'change', $Record_ID, '022', 'a', $obi->{'issn'}, $biblioitem->{'issn'});
842 changeSubfield($Record_ID, '022', 'a', $obi->{'issn'}, $biblioitem->{'issn'});
844 if ($biblioitem->{'dewey'} ne $obi->{'dewey'}) {
845 logchange('kohadb', 'change', 'biblioitems', 'dewey', $obi->{'dewey'}, $biblioitem->{'dewey'});
846 my $sth=$dbh->prepare("update biblioitems set dewey=$biblioitem->{'dewey'} where biblioitemnumber=$biblioitemnumber");
847 logchange('marc', 'change', $Record_ID, '082', 'a', $obi->{'dewey'}, $biblioitem->{'dewey'});
848 changeSubfield($Record_ID, '082', 'a', $obi->{'dewey'}, $biblioitem->{'dewey'});
850 if ($biblioitem->{'subclass'} ne $obi->{'subclass'}) {
851 logchange('kohadb', 'change', 'biblioitems', 'subclass', $obi->{'subclass'}, $biblioitem->{'subclass'});
852 my $q_subclass=$dbh->quote($biblioitem->{'subclass'});
853 my $sth=$dbh->prepare("update biblioitems set subclass=$q_subclass where biblioitemnumber=$biblioitemnumber");
854 logchange('marc', 'change', $Record_ID, '090', 'b', $obi->{'subclass'}, $biblioitem->{'subclass'});
855 changeSubfield($Record_ID, '090', 'b', $obi->{'subclass'}, $biblioitem->{'subclass'});
857 if ($biblioitem->{'place'} ne $obi->{'place'}) {
858 logchange('kohadb', 'change', 'biblioitems', 'place', $obi->{'place'}, $biblioitem->{'place'});
859 my $q_place=$dbh->quote($biblioitem->{'place'});
860 my $sth=$dbh->prepare("update biblioitems set place=$q_place where biblioitemnumber=$biblioitemnumber");
861 logchange('marc', 'change', $Record_ID, '260', 'a', $obi->{'place'}, $biblioitem->{'place'});
862 changeSubfield($Record_ID, '260', 'a', $obi->{'place'}, $biblioitem->{'place'});
864 if ($biblioitem->{'publishercode'} ne $obi->{'publishercode'}) {
865 logchange('kohadb', 'change', 'biblioitems', 'publishercode', $obi->{'publishercode'}, $biblioitem->{'publishercode'});
866 my $q_publishercode=$dbh->quote($biblioitem->{'publishercode'});
867 my $sth=$dbh->prepare("update biblioitems set publishercode=$q_publishercode where biblioitemnumber=$biblioitemnumber");
868 logchange('marc', 'change', $Record_ID, '260', 'b', $obi->{'publishercode'}, $biblioitem->{'publishercode'});
869 changeSubfield($Record_ID, '260', 'b', $obi->{'publishercode'}, $biblioitem->{'publishercode'});
871 if ($biblioitem->{'publicationyear'} ne $obi->{'publicationyear'}) {
872 logchange('kohadb', 'change', 'biblioitems', 'publicationyear', $obi->{'publicationyear'}, $biblioitem->{'publicationyear'});
873 my $q_publicationyear=$dbh->quote($biblioitem->{'publicationyear'});
874 my $sth=$dbh->prepare("update biblioitems set publicationyear=$q_publicationyear where biblioitemnumber=$biblioitemnumber");
875 logchange('marc', 'change', $Record_ID, '260', 'c', $obi->{'publicationyear'}, $biblioitem->{'publicationyear'});
876 changeSubfield($Record_ID, '260', 'c', $obi->{'publicationyear'}, $biblioitem->{'publicationyear'});
878 if ($biblioitem->{'illus'} ne $obi->{'illus'}) {
879 logchange('kohadb', 'change', 'biblioitems', 'illus', $obi->{'illus'}, $biblioitem->{'illus'});
880 my $q_illus=$dbh->quote($biblioitem->{'illus'});
881 my $sth=$dbh->prepare("update biblioitems set illus=$q_illus where biblioitemnumber=$biblioitemnumber");
882 logchange('marc', 'change', $Record_ID, '700', 'a', $obi->{'illus'}, $biblioitem->{'illus'});
883 changeSubfield($Record_ID, '700', 'a', $obi->{'illus'}, $biblioitem->{'illus'});
885 if ($biblioitem->{'pages'} ne $obi->{'pages'}) {
886 logchange('kohadb', 'change', 'biblioitems', 'pages', $obi->{'pages'}, $biblioitem->{'pages'});
887 my $q_pages=$dbh->quote($biblioitem->{'pages'});
888 my $sth=$dbh->prepare("update biblioitems set pages=$q_pages where biblioitemnumber=$biblioitemnumber");
889 logchange('marc', 'change', $Record_ID, '300', 'a', $obi->{'pages'}, $biblioitem->{'pages'});
890 changeSubfield($Record_ID, '300', 'a', $obi->{'pages'}, $biblioitem->{'pages'});
892 if ($biblioitem->{'size'} ne $obi->{'size'}) {
893 logchange('kohadb', 'change', 'biblioitems', 'size', $obi->{'size'}, $biblioitem->{'size'});
894 my $q_size=$dbh->quote($biblioitem->{'size'});
895 my $sth=$dbh->prepare("update biblioitems set size=$q_size where biblioitemnumber=$biblioitemnumber");
896 logchange('marc', 'change', $Record_ID, '300', 'c', $obi->{'size'}, $biblioitem->{'size'});
897 changeSubfield($Record_ID, '300', 'c', $obi->{'size'}, $biblioitem->{'size'});
899 if ($biblioitem->{'notes'} ne $obi->{'notes'}) {
900 logchange('kohadb', 'change', 'biblioitems', 'notes', $obi->{'notes'}, $biblioitem->{'notes'});
901 my $q_notes=$dbh->quote($biblioitem->{'notes'});
902 my $sth=$dbh->prepare("update biblioitems set notes=$q_notes where biblioitemnumber=$biblioitemnumber");
903 logchange('marc', 'change', $Record_ID, '500', 'a', $obi->{'notes'}, $biblioitem->{'notes'});
904 changeSubfield($Record_ID, '500', 'a', $obi->{'notes'}, $biblioitem->{'notes'});
906 if ($biblioitem->{'lccn'} ne $obi->{'lccn'}) {
907 logchange('kohadb', 'change', 'biblioitems', 'lccn', $obi->{'lccn'}, $biblioitem->{'lccn'});
908 my $q_lccn=$dbh->quote($biblioitem->{'lccn'});
909 my $sth=$dbh->prepare("update biblioitems set lccn=$q_lccn where biblioitemnumber=$biblioitemnumber");
910 logchange('marc', 'change', $Record_ID, '010', 'a', $obi->{'lccn'}, $biblioitem->{'lccn'});
911 changeSubfield($Record_ID, '010', 'a', $obi->{'lccn'}, $biblioitem->{'lccn'});
920 my ($env, $Record_ID, $item) = @_;
922 my $barcode=$item->{'barcode'};
923 my $q_barcode=$dbh->quote($barcode);
924 my $biblionumber=$item->{'biblionumber'};
925 my $biblioitemnumber=$item->{'biblioitemnumber'};
926 my $dateaccessioned=$item->{'dateaccessioned'};
927 my $booksellerid=$item->{'booksellerid'};
928 my $q_booksellerid=$dbh->quote($booksellerid);
929 my $homebranch=$item->{'homebranch'};
930 my $q_homebranch=$dbh->quote($homebranch);
931 my $holdingbranch=$item->{'holdingbranch'};
932 my $price=$item->{'price'};
933 my $replacementprice=$item->{'replacementprice'};
934 my $replacementpricedate=$item->{'replacementpricedate'};
935 my $q_replacementpricedate=$dbh->quote($replacementpricedate);
936 my $notforloan=$item->{'notforloan'};
937 my $itemlost=$item->{'itemlost'};
938 my $wthdrawn=$item->{'wthdrawn'};
939 my $restricted=$item->{'restricted'};
940 my $itemnotes=$item->{'itemnotes'};
941 my $q_itemnotes=$dbh->quote($itemnotes);
942 my $itemtype=$item->{'itemtype'};
943 my $subclass=$item->{'subclass'};
947 unless ($env->{'marconly'}) {
948 my $sth=$dbh->prepare("select max(itemnumber) from items");
950 my ($itemnumber) =$sth->fetchrow;
952 $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)");
959 $subfields->{1}->{'Subfield_Mark'}='p';
960 $subfields->{1}->{'Subfield_Value'}=$barcode;
961 $subfields->{2}->{'Subfield_Mark'}='d';
962 $subfields->{2}->{'Subfield_Value'}=$dateaccessioned;
963 $subfields->{3}->{'Subfield_Mark'}='e';
964 $subfields->{3}->{'Subfield_Value'}=$booksellerid;
965 $subfields->{4}->{'Subfield_Mark'}='b';
966 $subfields->{4}->{'Subfield_Value'}=$homebranch;
967 $subfields->{5}->{'Subfield_Mark'}='l';
968 $subfields->{5}->{'Subfield_Value'}=$holdingbranch;
969 $subfields->{6}->{'Subfield_Mark'}='c';
970 $subfields->{6}->{'Subfield_Value'}=$price;
971 $subfields->{7}->{'Subfield_Mark'}='c';
972 $subfields->{7}->{'Subfield_Value'}=$replacementprice;
973 $subfields->{8}->{'Subfield_Mark'}='d';
974 $subfields->{8}->{'Subfield_Value'}=$replacementpricedate;
976 $subfields->{9}->{'Subfield_Mark'}='h';
977 $subfields->{9}->{'Subfield_Value'}='Not for loan';
980 $subfields->{10}->{'Subfield_Mark'}='j';
981 $subfields->{10}->{'Subfield_Value'}='Item lost';
984 $subfields->{11}->{'Subfield_Mark'}='j';
985 $subfields->{11}->{'Subfield_Value'}='Item withdrawn';
988 $subfields->{12}->{'Subfield_Mark'}='z';
989 $subfields->{12}->{'Subfield_Value'}=$itemnotes;
994 ($env, $Tag_ID) = addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
996 $env->{'linkid'}=$Tag_ID;
999 $subfields2->{1}->{'Subfield_Mark'}='a';
1000 $subfields2->{1}->{'Subfield_Value'}='Coast Mountains School District';
1001 $subfields2->{1}->{'Subfield_Mark'}='b';
1002 $subfields2->{1}->{'Subfield_Value'}=$homebranch;
1003 $subfields2->{1}->{'Subfield_Mark'}='c';
1004 $subfields2->{1}->{'Subfield_Value'}=$itemtype;
1005 $subfields2->{2}->{'Subfield_Mark'}='m';
1006 $subfields2->{2}->{'Subfield_Value'}=$subclass;
1007 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields2);
1008 $env->{'linkid'}='';
1012 # Update the item with itemnumber $item->{'itemnumber'}
1013 # This routine should also modify the corresponding MARC record data. (852 and
1014 # 876 tags with 876p tag the same as $item->{'barcode'}
1016 # This routine should also check to see which fields are actually being
1017 # modified, and log all changes.
1019 my ($env, $item) = @_;
1021 my $itemnumber=$item->{'itemnumber'};
1022 my $biblionumber=$item->{'biblionumber'};
1023 my $biblioitemnumber=$item->{'biblioitemnumber'};
1024 my $barcode=$item->{'barcode'};
1025 my $dateaccessioned=$item->{'dateaccessioned'};
1026 my $booksellerid=$item->{'booksellerid'};
1027 my $homebranch=$item->{'homebranch'};
1028 my $price=$item->{'price'};
1029 my $replacementprice=$item->{'replacementprice'};
1030 my $replacementpricedate=$item->{'replacementpricedate'};
1031 my $multivolume=$item->{'multivolume'};
1032 my $stack=$item->{'stack'};
1033 my $notforloan=$item->{'notforloan'};
1034 my $itemlost=$item->{'itemlost'};
1035 my $wthdrawn=$item->{'wthdrawn'};
1036 my $bulk=$item->{'bulk'};
1037 my $restricted=$item->{'restricted'};
1038 my $binding=$item->{'binding'};
1039 my $itemnotes=$item->{'itemnotes'};
1040 my $holdingbranch=$item->{'holdingbranch'};
1041 my $interim=$item->{'interim'};
1042 my $sth=$dbh->prepare("select * from items where itemnumber=$itemnumber");
1044 my $olditem=$sth->fetchrow_hashref;
1045 my $q_barcode=$dbh->quote($olditem->{'barcode'});
1046 $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");
1048 my ($Subfield876_ID, $Record_ID) = $sth->fetchrow;
1049 $sth=$dbh->prepare("select Subfield_Value from 8XX_Subfield_Table where Subfield_Mark=8 and Subfield_ID=$Subfield876_ID");
1051 my ($link) = $sth->fetchrow;
1052 $sth=$dbh->prepare("select Subfield_ID from 8XX_Subfield_Table where Subfield_Mark=8 and Subfield_Value=$link and !(Subfield_ID=$Subfield876_ID)");
1054 my ($Subfield852_ID) = $sth->fetchrow;
1056 if ($item->{'barcode'} ne $olditem->{'barcode'}) {
1057 logchange('kohadb', 'change', 'items', 'barcode', $olditem->{'barcode'}, $item->{'barcode'});
1058 my $q_barcode=$dbh->quote($item->{'barcode'});
1059 my $sth=$dbh->prepare("update items set barcode=$q_barcode where itemnumber=$itemnumber");
1061 my ($Subfield_ID, $Subfield_Key) = changeSubfield($Record_ID, '876', 'p', $olditem->{'barcode'}, $item->{'barcode'}, $Subfield876_ID);
1062 logchange('marc', 'change', $Record_ID, '876', 'p', $Subfield_Key, $olditem->{'barcode'}, $item->{'barcode'});
1064 if ($item->{'booksellerid'} ne $olditem->{'booksellerid'}) {
1065 logchange('kohadb', 'change', 'items', 'booksellerid', $olditem->{'booksellerid'}, $item->{'booksellerid'});
1066 my $q_booksellerid=$dbh->quote($item->{'booksellerid'});
1067 my $sth=$dbh->prepare("update items set booksellerid=$q_booksellerid where itemnumber=$itemnumber");
1069 my ($Subfield_ID, $Subfield_Key) = changeSubfield($Record_ID, '876', 'e', $olditem->{'booksellerid'}, $item->{'booksellerid'}, $Subfield876_ID);
1070 logchange('marc', 'change', $Record_ID, '876', 'e', $Subfield_Key, $olditem->{'booksellerid'}, $item->{'booksellerid'});
1072 if ($item->{'dateaccessioned'} ne $olditem->{'dateaccessioned'}) {
1073 logchange('kohadb', 'change', 'items', 'dateaccessioned', $olditem->{'dateaccessioned'}, $item->{'dateaccessioned'});
1074 my $q_dateaccessioned=$dbh->quote($item->{'dateaccessioned'});
1075 my $sth=$dbh->prepare("update items set dateaccessioned=$q_dateaccessioned where itemnumber=$itemnumber");
1077 my ($Subfield_ID, $Subfield_Key) = changeSubfield($Record_ID, '876', 'd', $olditem->{'dateaccessioned'}, $item->{'dateaccessioned'}, $Subfield876_ID);
1078 logchange('marc', 'change', $Record_ID, '876', 'd', $Subfield_Key, $olditem->{'dateaccessioned'}, $item->{'dateaccessioned'});
1080 if ($item->{'homebranch'} ne $olditem->{'homebranch'}) {
1081 logchange('kohadb', 'change', 'items', 'homebranch', $olditem->{'homebranch'}, $item->{'homebranch'});
1082 my $q_homebranch=$dbh->quote($item->{'homebranch'});
1083 my $sth=$dbh->prepare("update items set homebranch=$q_homebranch where itemnumber=$itemnumber");
1085 my ($Subfield_ID, $Subfield_Key) = changeSubfield($Record_ID, '876', 'b', $olditem->{'homebranch'}, $item->{'homebranch'}, $Subfield876_ID);
1086 logchange('marc', 'change', $Record_ID, '876', 'b', $Subfield_Key, $olditem->{'homebranch'}, $item->{'homebranch'});
1088 if ($item->{'holdingbranch'} ne $olditem->{'holdingbranch'}) {
1089 logchange('kohadb', 'change', 'items', 'holdingbranch', $olditem->{'holdingbranch'}, $item->{'holdingbranch'});
1090 my $q_holdingbranch=$dbh->quote($item->{'holdingbranch'});
1091 my $sth=$dbh->prepare("update items set holdingbranch=$q_holdingbranch where itemnumber=$itemnumber");
1093 my ($Subfield_ID, $Subfield_Key) = changeSubfield($Record_ID, '876', 'l', $olditem->{'holdingbranch'}, $item->{'holdingbranch'}, $Subfield876_ID);
1094 logchange('marc', 'change', $Record_ID, '876', 'l', $Subfield_Key, $olditem->{'holdingbranch'}, $item->{'holdingbranch'});
1096 if ($item->{'price'} ne $olditem->{'price'}) {
1097 logchange('kohadb', 'change', 'items', 'price', $olditem->{'price'}, $item->{'price'});
1098 my $q_price=$dbh->quote($item->{'price'});
1099 my $sth=$dbh->prepare("update items set price=$q_price where itemnumber=$itemnumber");
1101 my ($Subfield_ID, $Subfield_Key) = changeSubfield($Record_ID, '876', 'c', $olditem->{'price'}, $item->{'price'}, $Subfield876_ID);
1102 logchange('marc', 'change', $Record_ID, '876', 'c', $Subfield_Key, $olditem->{'price'}, $item->{'price'});
1104 if ($item->{'itemnotes'} ne $olditem->{'itemnotes'}) {
1105 logchange('kohadb', 'change', 'items', 'itemnotes', $olditem->{'itemnotes'}, $item->{'itemnotes'});
1106 my $q_itemnotes=$dbh->quote($item->{'itemnotes'});
1107 my $sth=$dbh->prepare("update items set itemnotes=$q_itemnotes where itemnumber=$itemnumber");
1109 my ($Subfield_ID, $Subfield_Key) = changeSubfield($Record_ID, '876', 'c', $olditem->{'itemnotes'}, $item->{'itemnotes'}, $Subfield876_ID);
1110 logchange('marc', 'change', $Record_ID, '876', 'c', $Subfield_Key, $olditem->{'itemnotes'}, $item->{'itemnotes'});
1112 if ($item->{'notforloan'} ne $olditem->{'notforloan'}) {
1113 logchange('kohadb', 'change', 'items', 'notforloan', $olditem->{'notforloan'}, $item->{'notforloan'});
1114 my $sth=$dbh->prepare("update items set notforloan=$notforloan where itemnumber=$itemnumber");
1116 if ($item->{'notforloan'}) {
1117 my ($Subfield_ID, $Subfield_Key) = addSubfield($Record_ID, '876', 'h', 'Not for loan', $Subfield876_ID);
1118 logchange('marc', 'add', $Record_ID, '876', 'h', $Subfield_Key, 'Not for loan');
1120 my ($Subfield_ID, $Subfield_Key) = deleteSubfield($Record_ID, '876', 'h', 'Not for loan', $Subfield876_ID);
1121 logchange('marc', 'delete', $Record_ID, '876', 'h', $Subfield_Key, 'Not for loan');
1124 if ($item->{'itemlost'} ne $olditem->{'itemlost'}) {
1125 logchange('kohadb', 'change', 'items', 'itemlost', $olditem->{'itemlost'}, $item->{'itemlost'});
1126 my $sth=$dbh->prepare("update items set itemlost=$itemlost where itemnumber=$itemnumber");
1128 if ($item->{'itemlost'}) {
1129 my ($Subfield_ID, $Subfield_Key) = addSubfield($Record_ID, '876', 'h', 'Item lost', $Subfield876_ID);
1130 logchange('marc', 'add', $Record_ID, '876', 'h', $Subfield_Key, 'Item lost');
1132 my ($Subfield_ID, $Subfield_Key) = deleteSubfield($Record_ID, '876', 'h', 'Item lost', $Subfield876_ID);
1133 logchange('marc', 'delete', $Record_ID, '876', 'h', $Subfield_Key, 'Item lost');
1136 if ($item->{'wthdrawn'} ne $olditem->{'wthdrawn'}) {
1137 logchange('kohadb', 'change', 'items', 'wthdrawn', $olditem->{'wthdrawn'}, $item->{'wthdrawn'});
1138 my $sth=$dbh->prepare("update items set wthdrawn=$wthdrawn where itemnumber=$itemnumber");
1140 if ($item->{'wthdrawn'}) {
1141 my ($Subfield_ID, $Subfield_Key) = addSubfield($Record_ID, '876', 'h', 'Withdrawn', $Subfield876_ID);
1142 logchange('marc', 'add', $Record_ID, '876', 'h', $Subfield_Key, 'Withdrawn');
1144 my ($Subfield_ID, $Subfield_Key) = deleteSubfield($Record_ID, '876', 'h', 'Withdrawn', $Subfield876_ID);
1145 logchange('marc', 'delete', $Record_ID, '876', 'h', $Subfield_Key, 'Withdrawn');
1148 if ($item->{'restricted'} ne $olditem->{'restricted'}) {
1149 logchange('kohadb', 'change', 'items', 'restricted', $olditem->{'restricted'}, $item->{'restricted'});
1150 my $sth=$dbh->prepare("update items set restricted=$restricted where itemnumber=$itemnumber");
1152 if ($item->{'restricted'}) {
1153 my ($Subfield_ID, $Subfield_Key) = addSubfield($Record_ID, '876', 'h', 'Restricted', $Subfield876_ID);
1154 logchange('marc', 'add', $Record_ID, '876', 'h', $Subfield_Key, 'Restricted');
1156 my ($Subfield_ID, $Subfield_Key) = deleteSubfield($Record_ID, '876', 'h', 'Restricted', $Subfield876_ID);
1157 logchange('marc', 'delete', $Record_ID, '876', 'h', $Subfield_Key, 'Restricted');
1166 my ($supplierid)=@_;
1168 my $query = "Select count(*),authorisedby,entrydate,basketno from aqorders where
1169 booksellerid='$supplierid' and (quantity > quantityreceived or
1170 quantityreceived is NULL)
1171 and (datecancellationprinted is NULL or datecancellationprinted = '0000-00-00')";
1172 $query.=" group by basketno order by entrydate desc";
1174 my $sth=$dbh->prepare($query);
1178 while (my $data=$sth->fetchrow_hashref){
1184 return ($i,\@results);
1190 my $query="Select count(*) from items where biblionumber=$biblio";
1192 my $sth=$dbh->prepare($query);
1194 my $data=$sth->fetchrow_hashref;
1197 return($data->{'count(*)'});
1203 my $query="Select ordernumber from aqorders where biblionumber=$bib and
1204 biblioitemnumber='$bi'";
1205 my $sth=$dbh->prepare($query);
1207 my $ordnum=$sth->fetchrow_hashref;
1209 my $order=getsingleorder($ordnum->{'ordernumber'});
1212 return ($order,$ordnum->{'ordernumber'});
1215 sub getsingleorder {
1218 my $query="Select * from biblio,biblioitems,aqorders,aqorderbreakdown
1219 where aqorders.ordernumber='$ordnum'
1220 and biblio.biblionumber=aqorders.biblionumber and
1221 biblioitems.biblioitemnumber=aqorders.biblioitemnumber and
1222 aqorders.ordernumber=aqorderbreakdown.ordernumber";
1223 my $sth=$dbh->prepare($query);
1225 my $data=$sth->fetchrow_hashref;
1234 my $query="Select * from aqorders,biblio,biblioitems where
1235 booksellerinvoicenumber='$invoice'
1236 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber=
1237 aqorders.biblioitemnumber group by aqorders.ordernumber,aqorders.biblioitemnumber";
1240 my $sth=$dbh->prepare($query);
1242 while (my $data=$sth->fetchrow_hashref){
1248 return($i,@results);
1252 #gets all orders from a certain supplier, orders them alphabetically
1255 my $query="Select * from aqorders,biblio,biblioitems where booksellerid='$supid'
1256 and (cancelledby is NULL or cancelledby = '')
1257 and (quantityreceived < quantity or quantityreceived is NULL)
1258 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber=
1259 aqorders.biblioitemnumber
1260 group by aqorders.biblioitemnumber
1265 my $sth=$dbh->prepare($query);
1267 while (my $data=$sth->fetchrow_hashref){
1273 return($i,@results);
1277 #gets all orders from a certain supplier, orders them alphabetically
1280 my $query="Select * from aqorders,biblio,biblioitems where booksellerid='$supid'
1281 and (cancelledby is NULL or cancelledby = '')
1282 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber=
1283 aqorders.biblioitemnumber and
1284 aqorders.quantityreceived>0
1285 and aqorders.datereceived >=now()
1286 group by aqorders.biblioitemnumber
1291 my $sth=$dbh->prepare($query);
1293 while (my $data=$sth->fetchrow_hashref){
1299 return($i,@results);
1303 my ($search,$biblio,$catview) = @_;
1304 my $dbh = C4Connect;
1305 my $query = "Select *,biblio.title from aqorders,biblioitems,biblio
1306 where aqorders.biblioitemnumber = biblioitems.biblioitemnumber
1307 and biblio.biblionumber=aqorders.biblionumber
1308 and ((datecancellationprinted is NULL)
1309 or (datecancellationprinted = '0000-00-00'))
1311 my @data = split(' ',$search);
1313 for (my $i = 0; $i < $count; $i++) {
1314 $query .= "(biblio.title like '$data[$i]%' or biblio.title like '% $data[$i]%') and ";
1316 $query=~ s/ and $//;
1317 $query.=" ) or biblioitems.isbn='$search'
1318 or (aqorders.ordernumber='$search' and aqorders.biblionumber='$biblio')) ";
1319 if ($catview ne 'yes'){
1320 $query.=" and (quantityreceived < quantity or quantityreceived is NULL)";
1322 $query.=" group by aqorders.ordernumber";
1323 my $sth=$dbh->prepare($query);
1327 while (my $data=$sth->fetchrow_hashref){
1328 my $sth2=$dbh->prepare("Select * from biblio where
1329 biblionumber='$data->{'biblionumber'}'");
1331 my $data2=$sth2->fetchrow_hashref;
1333 $data->{'author'}=$data2->{'author'};
1334 $data->{'seriestitle'}=$data2->{'seriestitle'};
1335 $sth2=$dbh->prepare("Select * from aqorderbreakdown where
1336 ordernumber=$data->{'ordernumber'}");
1338 $data2=$sth2->fetchrow_hashref;
1340 $data->{'branchcode'}=$data2->{'branchcode'};
1341 $data->{'bookfundid'}=$data2->{'bookfundid'};
1347 return($i,@results);
1352 my ($searchstring)=@_;
1354 my $query="Select * from aqbooksellers where name like '%$searchstring%' or
1355 id = '$searchstring'";
1356 my $sth=$dbh->prepare($query);
1360 while (my $data=$sth->fetchrow_hashref){
1366 return($i,@results);
1372 my $query="Select * from aqorderbreakdown where ordernumber='$id'";
1373 my $sth=$dbh->prepare($query);
1377 while (my $data=$sth->fetchrow_hashref){
1383 return($i,\@results);
1387 my ($basketno,$supplier)=@_;
1389 my $query="Select *,biblio.title from aqorders,biblio,biblioitems
1390 where basketno='$basketno'
1391 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber
1392 =aqorders.biblioitemnumber
1393 and (datecancellationprinted is NULL or datecancellationprinted =
1395 if ($supplier ne ''){
1396 $query.=" and aqorders.booksellerid='$supplier'";
1398 $query.=" group by aqorders.ordernumber";
1399 my $sth=$dbh->prepare($query);
1404 while (my $data=$sth->fetchrow_hashref){
1410 return($i,@results);
1415 my $query="Select max(basketno) from aqorders";
1416 my $sth=$dbh->prepare($query);
1418 my $data=$sth->fetchrow_arrayref;
1419 my $basket=$$data[0];
1428 my $query="Select * from aqbookfund,aqbudget where aqbookfund.bookfundid
1429 =aqbudget.bookfundid
1430 group by aqbookfund.bookfundid order by bookfundname";
1431 my $sth=$dbh->prepare($query);
1435 while (my $data=$sth->fetchrow_hashref){
1441 return($i,@results);
1445 my $dbh = C4Connect;
1446 my $query = "Select * from branches";
1447 my $sth = $dbh->prepare($query);
1452 while (my $data = $sth->fetchrow_hashref) {
1453 $results[$i] = $data;
1459 return($i, @results);
1462 sub bookfundbreakdown {
1465 my $query="Select quantity,datereceived,freight,unitprice,listprice,ecost,quantityreceived,subscription
1466 from aqorders,aqorderbreakdown where bookfundid='$id' and
1467 aqorders.ordernumber=aqorderbreakdown.ordernumber
1468 and (datecancellationprinted is NULL or
1469 datecancellationprinted='0000-00-00')";
1470 my $sth=$dbh->prepare($query);
1474 while (my $data=$sth->fetchrow_hashref){
1475 if ($data->{'subscription'} == 1){
1476 $spent+=$data->{'quantity'}*$data->{'unitprice'};
1478 my $leftover=$data->{'quantity'}-$data->{'quantityreceived'};
1479 $comtd+=($data->{'ecost'})*$leftover;
1480 $spent+=($data->{'unitprice'})*$data->{'quantityreceived'};
1485 return($spent,$comtd);
1491 my $dbh = &C4Connect;
1492 my $query = "Select max(biblionumber) from biblio";
1493 my $sth = $dbh->prepare($query);
1495 my $data = $sth->fetchrow_arrayref;
1496 my $bibnum = $$data[0] + 1;
1499 $biblio->{'title'} = $dbh->quote($biblio->{'title'});
1500 $biblio->{'author'} = $dbh->quote($biblio->{'author'});
1501 $biblio->{'copyright'} = $dbh->quote($biblio->{'copyright'});
1502 $biblio->{'seriestitle'} = $dbh->quote($biblio->{'seriestitle'});
1503 $biblio->{'notes'} = $dbh->quote($biblio->{'notes'});
1504 $biblio->{'abstract'} = $dbh->quote($biblio->{'abstract'});
1505 if ($biblio->{'seriestitle'}) { $series = 1 };
1508 $query = "insert into biblio set
1509 biblionumber = $bibnum,
1510 title = $biblio->{'title'},
1511 author = $biblio->{'author'},
1512 copyrightdate = $biblio->{'copyright'},
1514 seriestitle = $biblio->{'seriestitle'},
1515 notes = $biblio->{'notes'},
1516 abstract = $biblio->{'abstract'}";
1518 $sth = $dbh->prepare($query);
1529 my $dbh = C4Connect;
1533 $biblio->{'title'} = $dbh->quote($biblio->{'title'});
1534 $biblio->{'author'} = $dbh->quote($biblio->{'author'});
1535 $biblio->{'abstract'} = $dbh->quote($biblio->{'abstract'});
1536 $biblio->{'copyrightdate'} = $dbh->quote($biblio->{'copyrightdate'});
1537 $biblio->{'seriestitle'} = $dbh->quote($biblio->{'serirestitle'});
1538 $biblio->{'serial'} = $dbh->quote($biblio->{'serial'});
1539 $biblio->{'unititle'} = $dbh->quote($biblio->{'unititle'});
1540 $biblio->{'notes'} = $dbh->quote($biblio->{'notes'});
1542 $query = "Update biblio set
1543 title = $biblio->{'title'},
1544 author = $biblio->{'author'},
1545 abstract = $biblio->{'abstract'},
1546 copyrightdate = $biblio->{'copyrightdate'},
1547 seriestitle = $biblio->{'seriestitle'},
1548 serial = $biblio->{'serial'},
1549 unititle = $biblio->{'unititle'},
1550 notes = $biblio->{'notes'}
1551 where biblionumber = $biblio->{'biblionumber'}";
1552 $sth = $dbh->prepare($query);
1558 return($biblio->{'biblionumber'});
1563 my ($bibnum, $subtitle) = @_;
1564 my $dbh = C4Connect;
1565 my $query = "update bibliosubtitle set
1566 subtitle = '$subtitle'
1567 where biblionumber = $bibnum";
1568 my $sth = $dbh->prepare($query);
1577 my ($bibnum, $author) = @_;
1578 my $dbh = C4Connect;
1579 my $query = "Delete from additionalauthors where biblionumber = $bibnum";
1580 my $sth = $dbh->prepare($query);
1585 if ($author ne '') {
1586 $query = "Insert into additionalauthors set
1588 biblionumber = '$bibnum'";
1589 $sth = $dbh->prepare($query);
1597 } # sub modaddauthor
1601 my ($bibnum, $force, @subject) = @_;
1602 my $dbh = C4Connect;
1603 my $count = @subject;
1605 for (my $i = 0; $i < $count; $i++) {
1606 $subject[$i] =~ s/^ //g;
1607 $subject[$i] =~ s/ $//g;
1608 my $query = "select * from catalogueentry
1609 where entrytype = 's'
1610 and catalogueentry = '$subject[$i]'";
1611 my $sth = $dbh->prepare($query);
1614 if (my $data = $sth->fetchrow_hashref) {
1616 if ($force eq $subject[$i]) {
1618 # subject not in aut, chosen to force anway
1619 # so insert into cataloguentry so its in auth file
1620 $query = "Insert into catalogueentry
1621 (entrytype,catalogueentry)
1622 values ('s','$subject[$i]')";
1623 my $sth2 = $dbh->prepare($query);
1630 $error = "$subject[$i]\n does not exist in the subject authority file";
1631 $query = "Select * from catalogueentry
1632 where entrytype = 's'
1633 and (catalogueentry like '$subject[$i] %'
1634 or catalogueentry like '% $subject[$i] %'
1635 or catalogueentry like '% $subject[$i]')";
1636 my $sth2 = $dbh->prepare($query);
1639 while (my $data = $sth2->fetchrow_hashref) {
1640 $error = $error."<br>$data->{'catalogueentry'}";
1649 my $query = "Delete from bibliosubject where biblionumber = $bibnum";
1650 my $sth = $dbh->prepare($query);
1655 for (my $i = 0; $i < $count; $i++) {
1656 $sth = $dbh->prepare("Insert into bibliosubject
1657 values ('$subject[$i]', $bibnum)");
1670 my ($biblioitem) = @_;
1671 my $dbh = C4Connect;
1674 $biblioitem->{'itemtype'} = $dbh->quote($biblioitem->{'itemtype'});
1675 $biblioitem->{'url'} = $dbh->quote($biblioitem->{'url'});
1676 $biblioitem->{'isbn'} = $dbh->quote($biblioitem->{'isbn'});
1677 $biblioitem->{'publishercode'} = $dbh->quote($biblioitem->{'publishercode'});
1678 $biblioitem->{'publicationyear'} = $dbh->quote($biblioitem->{'publicationyear'});
1679 $biblioitem->{'classification'} = $dbh->quote($biblioitem->{'classification'});
1680 $biblioitem->{'dewey'} = $dbh->quote($biblioitem->{'dewey'});
1681 $biblioitem->{'subclass'} = $dbh->quote($biblioitem->{'subclass'});
1682 $biblioitem->{'illus'} = $dbh->quote($biblioitem->{'illus'});
1683 $biblioitem->{'pages'} = $dbh->quote($biblioitem->{'pages'});
1684 $biblioitem->{'volumeddesc'} = $dbh->quote($biblioitem->{'volumeddesc'});
1685 $biblioitem->{'notes'} = $dbh->quote($biblioitem->{'notes'});
1686 $biblioitem->{'size'} = $dbh->quote($biblioitem->{'size'});
1687 $biblioitem->{'place'} = $dbh->quote($biblioitem->{'place'});
1689 $query = "Update biblioitems set
1690 itemtype = $biblioitem->{'itemtype'},
1691 url = $biblioitem->{'url'},
1692 isbn = $biblioitem->{'isbn'},
1693 publishercode = $biblioitem->{'publishercode'},
1694 publicationyear = $biblioitem->{'publicationyear'},
1695 classification = $biblioitem->{'classification'},
1696 dewey = $biblioitem->{'dewey'},
1697 subclass = $biblioitem->{'subclass'},
1698 illus = $biblioitem->{'illus'},
1699 pages = $biblioitem->{'pages'},
1700 volumeddesc = $biblioitem->{'volumeddesc'},
1701 notes = $biblioitem->{'notes'},
1702 size = $biblioitem->{'size'},
1703 place = $biblioitem->{'place'}
1704 where biblioitemnumber = $biblioitem->{'biblioitemnumber'}";
1713 my ($bibitemnum,$note)=@_;
1715 my $query="update biblioitems set notes='$note' where
1716 biblioitemnumber='$bibitemnum'";
1717 my $sth=$dbh->prepare($query);
1724 my ($biblioitem) = @_;
1725 my $dbh = C4Connect;
1726 my $query = "Select max(biblioitemnumber) from biblioitems";
1727 my $sth = $dbh->prepare($query);
1731 $biblioitem->{'volume'} = $dbh->quote($biblioitem->{'volume'});
1732 $biblioitem->{'number'} = $dbh->quote($biblioitem->{'number'});
1733 $biblioitem->{'classification'} = $dbh->quote($biblioitem->{'classification'});
1734 $biblioitem->{'itemtype'} = $dbh->quote($biblioitem->{'itemtype'});
1735 $biblioitem->{'url'} = $dbh->quote($biblioitem->{'url'});
1736 $biblioitem->{'isbn'} = $dbh->quote($biblioitem->{'isbn'});
1737 $biblioitem->{'issn'} = $dbh->quote($biblioitem->{'issn'});
1738 $biblioitem->{'dewey'} = $dbh->quote($biblioitem->{'dewey'});
1739 $biblioitem->{'subclass'} = $dbh->quote($biblioitem->{'subclass'});
1740 $biblioitem->{'publicationyear'} = $dbh->quote($biblioitem->{'publicationyear'});
1741 $biblioitem->{'publishercode'} = $dbh->quote($biblioitem->{'publishercode'});
1742 $biblioitem->{'volumedate'} = $dbh->quote($biblioitem->{'volumedate'});
1743 $biblioitem->{'volumeddesc'} = $dbh->quote($biblioitem->{'volumeddesc'}); $biblioitem->{'illus'} = $dbh->quote($biblioitem->{'illus'});
1744 $biblioitem->{'illus'} = $dbh->quote($biblioitem->{'illus'});
1745 $biblioitem->{'pages'} = $dbh->quote($biblioitem->{'pages'});
1746 $biblioitem->{'notes'} = $dbh->quote($biblioitem->{'notes'});
1747 $biblioitem->{'size'} = $dbh->quote($biblioitem->{'size'});
1748 $biblioitem->{'place'} = $dbh->quote($biblioitem->{'place'});
1749 $biblioitem->{'lccn'} = $dbh->quote($biblioitem->{'lccn'});
1750 $biblioitem->{'marc'} = $dbh->quote($biblioitem->{'marc'});
1753 $data = $sth->fetchrow_arrayref;
1754 $bibitemnum = $$data[0] + 1;
1758 $query = "insert into biblioitems set
1759 biblioitemnumber = $bibitemnum,
1760 biblionumber = $biblioitem->{'biblionumber'},
1761 volume = $biblioitem->{'volume'},
1762 number = $biblioitem->{'number'},
1763 classification = $biblioitem->{'classification'},
1764 itemtype = $biblioitem->{'itemtype'},
1765 url = $biblioitem->{'url'},
1766 isbn = $biblioitem->{'isbn'},
1767 issn = $biblioitem->{'issn'},
1768 dewey = $biblioitem->{'dewey'},
1769 subclass = $biblioitem->{'subclass'},
1770 publicationyear = $biblioitem->{'publicationyear'},
1771 publishercode = $biblioitem->{'publishercode'},
1772 volumedate = $biblioitem->{'volumedate'},
1773 volumeddesc = $biblioitem->{'volumeddesc'},
1774 illus = $biblioitem->{'illus'},
1775 pages = $biblioitem->{'pages'},
1776 notes = $biblioitem->{'notes'},
1777 size = $biblioitem->{'size'},
1778 lccn = $biblioitem->{'lccn'},
1779 marc = $biblioitem->{'marc'},
1780 place = $biblioitem->{'place'}";
1782 $sth = $dbh->prepare($query);
1787 return($bibitemnum);
1793 my $query="insert into bibliosubject (biblionumber) values
1795 my $sth=$dbh->prepare($query);
1803 my ($bibnum, $subtitle) = @_;
1804 my $dbh = C4Connect;
1805 $subtitle = $dbh->quote($subtitle);
1806 my $query = "insert into bibliosubtitle set
1807 biblionumber = $bibnum,
1808 subtitle = $subtitle";
1809 my $sth = $dbh->prepare($query);
1818 my ($bibnum,$title,$ordnum,$basket,$quantity,$listprice,$supplier,$who,$notes,$bookfund,$bibitemnum,$rrp,$ecost,$gst,$budget,$cost,$sub,$invoice)=@_;
1819 if ($budget eq 'now'){
1822 $budget="'2001-07-01'";
1830 my $query="insert into aqorders (biblionumber,title,basketno,
1831 quantity,listprice,booksellerid,entrydate,requisitionedby,authorisedby,notes,
1832 biblioitemnumber,rrp,ecost,gst,unitprice,subscription,booksellerinvoicenumber)
1835 ($bibnum,'$title',$basket,$quantity,$listprice,'$supplier',now(),
1836 '$who','$who','$notes',$bibitemnum,'$rrp','$ecost','$gst','$cost',
1837 '$sub','$invoice')";
1838 my $sth=$dbh->prepare($query);
1842 $query="select * from aqorders where
1843 biblionumber=$bibnum and basketno=$basket and ordernumber >=$ordnum";
1844 $sth=$dbh->prepare($query);
1846 my $data=$sth->fetchrow_hashref;
1848 $ordnum=$data->{'ordernumber'};
1849 $query="insert into aqorderbreakdown (ordernumber,bookfundid) values
1850 ($ordnum,'$bookfund')";
1851 $sth=$dbh->prepare($query);
1859 my ($bibnum,$ordnum)=@_;
1861 my $query="update aqorders set datecancellationprinted=now()
1862 where biblionumber='$bibnum' and
1863 ordernumber='$ordnum'";
1864 my $sth=$dbh->prepare($query);
1868 my $count=itemcount($bibnum);
1876 my ($title,$ordnum,$quantity,$listprice,$bibnum,$basketno,$supplier,$who,$notes,$bookfund,$bibitemnum,$rrp,$ecost,$gst,$budget,$cost,$invoice)=@_;
1878 my $query="update aqorders set title='$title',
1879 quantity='$quantity',listprice='$listprice',basketno='$basketno',
1880 rrp='$rrp',ecost='$ecost',unitprice='$cost',
1881 booksellerinvoicenumber='$invoice'
1883 ordernumber=$ordnum and biblionumber=$bibnum";
1884 my $sth=$dbh->prepare($query);
1888 $query="update aqorderbreakdown set bookfundid=$bookfund where
1889 ordernumber=$ordnum";
1890 $sth=$dbh->prepare($query);
1899 my $query="Select max(ordernumber) from aqorders";
1900 my $sth=$dbh->prepare($query);
1902 my $data=$sth->fetchrow_arrayref;
1903 my $ordnum=$$data[0];
1911 my ($biblio,$ordnum,$quantrec,$user,$cost,$invoiceno,$bibitemno,$freight,$bookfund,$rrp)=@_;
1913 my $query="update aqorders set quantityreceived='$quantrec',
1914 datereceived=now(),booksellerinvoicenumber='$invoiceno',
1915 biblioitemnumber=$bibitemno,unitprice='$cost',freight='$freight',
1917 where biblionumber=$biblio and ordernumber=$ordnum
1920 my $sth=$dbh->prepare($query);
1923 $query="update aqorderbreakdown set bookfundid=$bookfund where
1924 ordernumber=$ordnum";
1925 $sth=$dbh->prepare($query);
1932 my($biblio,$ordnum,$user,$cost,$bookfund,$rrp)=@_;
1934 my $query="update aqorders set
1935 unitprice='$cost', rrp='$rrp'
1936 where biblionumber=$biblio and ordernumber=$ordnum
1939 my $sth=$dbh->prepare($query);
1942 $query="update aqorderbreakdown set bookfundid=$bookfund where
1943 ordernumber=$ordnum";
1944 $sth=$dbh->prepare($query);
1952 my ($currency,$price)=@_;
1954 my $query="Select rate from currency where currency='$currency'";
1955 my $sth=$dbh->prepare($query);
1957 my $data=$sth->fetchrow_hashref;
1960 my $cur=$data->{'rate'};
1964 my $price=$price / $cur;
1970 my $query="Select * from currency";
1971 my $sth=$dbh->prepare($query);
1975 while (my $data=$sth->fetchrow_hashref){
1981 return($i,\@results);
1987 my $query="Select * from currency where currency='$cur'";
1988 my $sth=$dbh->prepare($query);
1991 my $data=$sth->fetchrow_hashref;
1997 sub updatecurrencies {
1998 my ($currency,$rate)=@_;
2000 my $query="update currency set rate=$rate where currency='$currency'";
2001 my $sth=$dbh->prepare($query);
2010 my $query="Update aqbooksellers set
2011 name='$data->{'name'}',address1='$data->{'address1'}',address2='$data->{'address2'}',
2012 address3='$data->{'address3'}',address4='$data->{'address4'}',postal='$data->{'postal'}',
2013 phone='$data->{'phone'}',fax='$data->{'fax'}',url='$data->{'url'}',
2014 contact='$data->{'contact'}',contpos='$data->{'contpos'}',
2015 contphone='$data->{'contphone'}', contfax='$data->{'contfax'}', contaltphone=
2016 '$data->{'contaltphone'}', contemail='$data->{'contemail'}', contnotes=
2017 '$data->{'contnotes'}', active=$data->{'active'},
2018 listprice='$data->{'listprice'}', invoiceprice='$data->{'invoiceprice'}',
2019 gstreg=$data->{'gstreg'}, listincgst=$data->{'listincgst'},
2020 invoiceincgst=$data->{'invoiceincgst'}, specialty='$data->{'specialty'}',
2021 discount='$data->{'discount'}',invoicedisc='$data->{'invoicedisc'}',
2022 nocalc='$data->{'nocalc'}'
2023 where id='$data->{'id'}'";
2024 my $sth=$dbh->prepare($query);
2034 my $sth=$dbh->prepare("Select max(id) from aqbooksellers");
2036 my $data2=$sth->fetchrow_hashref;
2038 $data2->{'max(id)'}++;
2039 $sth=$dbh->prepare("Insert into aqbooksellers (id) values ($data2->{'max(id)'})");
2042 $data->{'id'}=$data2->{'max(id)'};
2045 return($data->{'id'});
2050 my ($item, @barcodes) = @_;
2051 my $dbh = C4Connect;
2052 my $query = "Select max(itemnumber) from items";
2053 my $sth = $dbh->prepare($query);
2059 $data = $sth->fetchrow_hashref;
2060 $itemnumber = $data->{'max(itemnumber)'} + 1;
2063 $item->{'booksellerid'} = $dbh->quote($item->{'booksellerid'});
2064 $item->{'homebranch'} = $dbh->quote($item->{'homebranch'});
2065 $item->{'price'} = $dbh->quote($item->{'price'});
2066 $item->{'replacementprice'} = $dbh->quote($item->{'replacementprice'});
2067 $item->{'itemnotes'} = $dbh->quote($item->{'itemnotes'});
2069 foreach my $barcode (@barcodes) {
2070 $barcode = uc($barcode);
2071 $barcode = $dbh->quote($barcode);
2072 $query = "Insert into items set
2073 itemnumber = $itemnumber,
2074 biblionumber = $item->{'biblionumber'},
2075 biblioitemnumber = $item->{'biblioitemnumber'},
2077 booksellerid = $item->{'booksellerid'},
2078 dateaccessioned = NOW(),
2079 homebranch = $item->{'homebranch'},
2080 holdingbranch = $item->{'homebranch'},
2081 price = $item->{'price'},
2082 replacementprice = $item->{'replacementprice'},
2083 replacementpricedate = NOW(),
2084 itemnotes = $item->{'itemnotes'}";
2086 if ($item->{'loan'}) {
2088 notforloan = $item->{'loan'}";
2091 $sth = $dbh->prepare($query);
2094 $error .= $sth->errstr;
2105 my ($count,@barcodes)=@_;
2108 for (my $i=0;$i<$count;$i++){
2109 $barcodes[$i]=uc $barcodes[$i];
2110 my $query="Select * from items where barcode='$barcodes[$i]'";
2111 my $sth=$dbh->prepare($query);
2113 if (my $data=$sth->fetchrow_hashref){
2114 $error.=" Duplicate Barcode: $barcodes[$i]";
2123 my ($loan,$itemnum,$bibitemnum,$barcode,$notes,$homebranch,$lost,$wthdrawn,$replacement)=@_;
2125 my $query="update items set biblioitemnumber=$bibitemnum,
2126 barcode='$barcode',itemnotes='$notes'
2127 where itemnumber=$itemnum";
2128 if ($barcode eq ''){
2129 $query="update items set biblioitemnumber=$bibitemnum,notforloan=$loan where itemnumber=$itemnum";
2132 $query="update items set biblioitemnumber=$bibitemnum,
2133 barcode='$barcode',itemnotes='$notes',homebranch='$homebranch',
2134 itemlost='$lost',wthdrawn='$wthdrawn' where itemnumber=$itemnum";
2136 if ($replacement ne ''){
2137 $query=~ s/ where/,replacementprice='$replacement' where/;
2140 my $sth=$dbh->prepare($query);
2147 my($price,$rrp,$itemnum)=@_;
2149 my $query="update items set price='$price',replacementprice='$rrp'
2150 where itemnumber=$itemnum";
2151 my $sth=$dbh->prepare($query);
2157 my ($bibitemnum)=@_;
2159 my $query="Select count(*) from items where biblioitemnumber='$bibitemnum'";
2160 my $sth=$dbh->prepare($query);
2162 my $data=$sth->fetchrow_hashref;
2165 return($data->{'count(*)'});
2169 my ($biblionumber)=@_;
2171 my $query="Select * from biblioitems,items,itemtypes where
2172 biblioitems.biblionumber=$biblionumber
2173 and biblioitems.biblioitemnumber=items.biblioitemnumber and
2174 itemtypes.itemtype=biblioitems.itemtype
2175 order by items.biblioitemnumber";
2176 my $sth=$dbh->prepare($query);
2180 while (my $data=$sth->fetchrow_hashref){
2190 my ($bibitemnum,$itemtype)=@_;
2192 my $query="Select * from biblioitems where biblioitemnumber=$bibitemnum
2193 and itemtype='$itemtype'";
2194 my $sth=$dbh->prepare($query);
2197 if (my $data=$sth->fetchrow_hashref){
2208 my $query="select * from items where itemnumber=$itemnum";
2209 my $sth=$dbh->prepare($query);
2211 my @data=$sth->fetchrow_array;
2213 $query="Insert into deleteditems values (";
2214 foreach my $temp (@data){
2215 $query=$query."'$temp',";
2219 $sth=$dbh->prepare($query);
2222 $query = "Delete from items where itemnumber=$itemnum";
2223 $sth=$dbh->prepare($query);
2228 sub deletebiblioitem {
2229 my ($biblioitemnumber) = @_;
2230 my $dbh = C4Connect;
2231 my $query = "Select * from biblioitems
2232 where biblioitemnumber = $biblioitemnumber";
2233 my $sth = $dbh->prepare($query);
2238 if (@results = $sth->fetchrow_array) {
2240 $query = "Insert into deletedbiblioitems values (";
2241 foreach my $value (@results) {
2242 $value = $dbh->quote($value);
2243 $query .= "$value,";
2246 $query =~ s/\,$/\)/;
2249 $query = "Delete from biblioitems
2250 where biblioitemnumber = $biblioitemnumber";
2256 # Now delete all the items attached to the biblioitem
2258 $query = "Select * from items where biblioitemnumber = $biblioitemnumber";
2259 $sth = $dbh->prepare($query);
2263 while (@results = $sth->fetchrow_array) {
2265 $query = "Insert into deleteditems values (";
2266 foreach my $value (@results) {
2267 $value = $dbh->quote($value);
2268 $query .= "$value,";
2271 $query =~ s/\,$/\)/;
2277 $query = "Delete from items where biblioitemnumber = $biblioitemnumber";
2281 } # sub deletebiblioitem
2287 my $query="select * from biblio where biblionumber=$biblio";
2288 my $sth=$dbh->prepare($query);
2290 if (my @data=$sth->fetchrow_array){
2292 $query="Insert into deletedbiblio values (";
2293 foreach my $temp (@data){
2294 $temp=~ s/\'/\\\'/g;
2295 $query=$query."'$temp',";
2299 $sth=$dbh->prepare($query);
2302 $query = "Delete from biblio where biblionumber=$biblio";
2303 $sth=$dbh->prepare($query);
2314 my $dbh = C4Connect;
2315 my $query = "select * from itemtypes";
2316 my $sth = $dbh->prepare($query);
2317 # || die "Cannot prepare $query" . $dbh->errstr;
2322 # || die "Cannot execute $query\n" . $sth->errstr;
2323 while (my $data = $sth->fetchrow_hashref) {
2324 @results[$count] = $data;
2330 return($count, @results);
2331 } # sub getitemtypes
2335 my ($biblionumber) = @_;
2336 my $dbh = C4Connect;
2337 my $query = "Select * from biblio where biblionumber = $biblionumber";
2338 my $sth = $dbh->prepare($query);
2339 # || die "Cannot prepare $query\n" . $dbh->errstr;
2344 # || die "Cannot execute $query\n" . $sth->errstr;
2345 while (my $data = $sth->fetchrow_hashref) {
2346 $results[$count] = $data;
2352 return($count, @results);
2357 my ($biblioitemnum) = @_;
2358 my $dbh = C4Connect;
2359 my $query = "Select * from biblioitems where
2360 biblioitemnumber = $biblioitemnum";
2361 my $sth = $dbh->prepare($query);
2367 while (my $data = $sth->fetchrow_hashref) {
2368 $results[$count] = $data;
2374 return($count, @results);
2375 } # sub getbiblioitem
2378 sub getbiblioitembybiblionumber {
2379 my ($biblionumber) = @_;
2380 my $dbh = C4Connect;
2381 my $query = "Select * from biblioitems where biblionumber =
2383 my $sth = $dbh->prepare($query);
2389 while (my $data = $sth->fetchrow_hashref) {
2390 $results[$count] = $data;
2396 return($count, @results);
2400 sub getitemsbybiblioitem {
2401 my ($biblioitemnum) = @_;
2402 my $dbh = C4Connect;
2403 my $query = "Select * from items, biblio where
2404 biblio.biblionumber = items.biblionumber and biblioitemnumber
2406 my $sth = $dbh->prepare($query);
2407 # || die "Cannot prepare $query\n" . $dbh->errstr;
2412 # || die "Cannot execute $query\n" . $sth->errstr;
2413 while (my $data = $sth->fetchrow_hashref) {
2414 $results[$count] = $data;
2420 return($count, @results);
2421 } # sub getitemsbybiblioitem
2426 my $dbh = C4Connect;
2432 $isbn = $dbh->quote($isbn);
2433 $query = "Select biblio.* from biblio, biblioitems where
2434 biblio.biblionumber = biblioitems.biblionumber
2436 $sth = $dbh->prepare($query);
2439 while (my $data = $sth->fetchrow_hashref) {
2440 $results[$count] = $data;
2446 return($count, @results);
2451 my ($keywordlist) = @_;
2452 my $dbh = C4Connect;
2453 my $query = "Select distinct biblio.* from biblio, biblioitems where
2454 biblio.biblionumber = biblioitems.biblionumber and (";
2458 my @keywords = split(/ +/, $keywordlist);
2459 my $keyword = shift(@keywords);
2461 $keyword =~ s/%/\\%/g;
2462 $keyword =~ s/_/\\_/;
2463 $keyword = "%" . $keyword . "%";
2464 $keyword = $dbh->quote($keyword);
2465 $query .= " (url like $keyword)";
2467 foreach $keyword (@keywords) {
2468 $keyword =~ s/%/\\%/;
2469 $keyword =~ s/_/\\_/;
2470 $keyword = "%" . $keyword . "%";
2471 $keyword = $dbh->quote($keyword);
2472 $query .= " or (url like $keyword)";
2476 $sth = $dbh->prepare($query);
2479 while (my $data = $sth->fetchrow_hashref) {
2480 $results[$count] = $data;
2486 return($count, @results);
2487 } # sub websitesearch
2492 my $dbh = C4Connect;
2495 $website->{'biblionumber'} = $dbh->quote($website->{'biblionumber'});
2496 $website->{'title'} = $dbh->quote($website->{'title'});
2497 $website->{'description'} = $dbh->quote($website->{'description'});
2498 $website->{'url'} = $dbh->quote($website->{'url'});
2500 $query = "Insert into websites set
2501 biblionumber = $website->{'biblionumber'},
2502 title = $website->{'title'},
2503 description = $website->{'description'},
2504 url = $website->{'url'}";
2514 my $dbh = C4Connect;
2517 $website->{'title'} = $dbh->quote($website->{'title'});
2518 $website->{'description'} = $dbh->quote($website->{'description'});
2519 $website->{'url'} = $dbh->quote($website->{'url'});
2521 $query = "Update websites set
2522 title = $website->{'title'},
2523 description = $website->{'description'},
2524 url = $website->{'url'}
2525 where websitenumber = $website->{'websitenumber'}";
2530 } # sub updatewebsite
2534 my ($websitenumber) = @_;
2535 my $dbh = C4Connect;
2536 my $query = "Delete from websites where websitenumber = $websitenumber";
2541 } # sub deletewebsite
2543 END { } # module clean-up code here (global destructor)