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....
17 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
19 # set the version for version checking
23 @EXPORT = qw(&newBiblio &newBiblioItem &newItem &updateBiblio &updateBiblioItem
25 &getorders &bookseller &breakdown &basket &newbasket &bookfunds
26 &ordersearch &newbiblio &newbiblioitem &newsubject &newsubtitle &neworder
27 &newordernum &modbiblio &modorder &getsingleorder &invoice &receiveorder
28 &bookfundbreakdown &curconvert &updatesup &insertsup &newitems &modbibitem
29 &getcurrencies &modsubtitle &modsubject &modaddauthor &moditem &countitems
30 &findall &needsmod &delitem &deletebiblioitem &delbiblio &delorder &branches
31 &getallorders &getrecorders &updatecurrencies &getorder &getcurrency &updaterecorder
32 &updatecost &checkitems &modnote &getitemtypes &getbiblio
33 &getbiblioitembybiblionumber
34 &getbiblioitem &getitemsbybiblioitem &isbnsearch
35 &websitesearch &addwebsite &updatewebsite &deletewebsite
37 &MARCchangeSubfield &MARCaddSubfield &MARCfindSubfield
38 &MARCaddMarcBiblio &MARCnextsubfieldid &MARCkoha2marc
40 %EXPORT_TAGS = ( ); # eg: TAG => [ qw!name1 name2! ],
42 # your exported package globals go here,
43 # as well as any optionally exported functions
45 @EXPORT_OK = qw($Var1 %Hashit);
48 # non-exported package globals go here
49 use vars qw(@more $stuff);
51 # initalize package globals, first exported ones
57 # then the others (which are still accessible as $Some::Module::stuff)
61 # all file-scoped lexicals must be created before
62 # the functions below that use them.
64 # file-private lexicals go here
68 # here's a file-private function as a closure,
69 # callable as &$priv_func; it cannot be prototyped.
74 # make all your functions, whether exported or not;
79 # This subroutine makes no modifications to the MARC tables. MARC records are
80 # only created when new biblioitems are added.
81 my ($env, $biblio) = @_;
83 my $subject=$biblio->{'subject'};
84 my $additionalauthors=$biblio->{'additionalauthors'};
86 # Why am I doing this? This is a potential race condition. At the very least,
87 # this needs code to ensure that two inserts didn't use the same
90 # Get next biblio number
91 my $sth=$dbh->prepare("select max(biblionumber) from biblio");
93 my ($biblionumber) = $sth->fetchrow;
96 $sth=$dbh->prepare("insert into biblio
97 (biblionumber,title,author,
98 unititle,copyrightdate,
99 serial,seriestitle,notes)
100 values (?, ?, ?, ?, ?, ?, ?, ?)");
101 $sth->execute($biblionumber, $biblio->{'title'}, $biblio->{'author'},
102 $biblio->{'unititle'}, $biblio->{'copyrightdate'},
103 $biblio->{'serial'}, $biblio->{'seriestitle'}, $biblio->{'notes'} );
104 $sth=$dbh->prepare("insert into bibliosubtitle
105 (biblionumber,subtitle)
107 $sth->execute($biblionumber, $biblio->{'subtitle'} );
109 my $sth=$dbh->prepare("insert into bibliosubject
110 (biblionumber,subject)
112 foreach $_ (@$subject) {
113 $sth->execute($biblionumber,$_);
115 my $sth=$dbh->prepare("insert into additionalauthors
116 (biblionumber,author)
118 foreach $_ (@$additionalauthors) {
119 $sth->execute($biblionumber, $_ );
124 sub MARCchangeSubfield {
125 # Subroutine changes a subfield value given a subfieldid.
126 my ( $subfieldid, $subfieldvalue )=@_;
128 # my $dbh=&C4Connect;
129 $dbh->do("lock tables marc_blob_subfield WRITE,marc_subfield_table WRITE");
130 my $sth1=$dbh->prepare("select valuebloblink from marc_subfield_table where subfieldid=?");
131 $sth1->execute($subfieldid);
132 my ($oldvaluebloblink)=$sth1->fetchrow;
135 # if too long, use a bloblink
136 if (length($subfieldvalue)>255 ) {
137 # if already a bloblink, update it, otherwise, insert a new one.
138 if ($oldvaluebloblink) {
139 $sth=$dbh->prepare("update marc_blob_subfield set subfieldvalue=? where blobidlink=?");
140 $sth->execute($subfieldvalue,$oldvaluebloblink);
142 $sth=$dbh->prepare("insert into marc_blob_subfield (subfieldvalue) values (?)");
143 $sth->execute($subfieldvalue);
144 $sth=$dbh->prepare("select max(blobidlink) from marc_blob_subfield");
146 my ($res)=$sth->fetchrow;
147 $sth=$dbh->prepare("update marc_subfield_table set subfieldvalue=null, valuebloblink=$res where subfieldid=?");
148 $sth->execute($subfieldid);
151 # note this can leave orphan bloblink. Not a big problem, but we should build somewhere a orphan deleting script...
152 $sth=$dbh->prepare("update marc_subfield_table set subfieldvalue=?,valuebloblink=null where subfieldid=?");
153 $sth->execute($subfieldvalue, $subfieldid);
155 $dbh->do("unlock tables");
158 return($subfieldid, $subfieldvalue);
161 sub MARCfindSubfield {
162 # returns a subfields number given a bibid/tad/subfield values
163 my ($bibid,$tag,$subfieldcode,$subfieldvalue,$subfieldorder) = @_;
167 # my $dbh=&C4Connect;
168 my $query="select subfieldid from marc_subfield_table where bibid=? and tag=? and subfieldcode=?";
169 if ($subfieldvalue) {
170 $query .= " and subfieldvalue=".$dbh->quote($subfieldvalue);
172 if ($subfieldorder<1) {
175 $query .= " and subfieldorder=$subfieldorder";
177 my $sti=$dbh->prepare($query);
178 $sti->execute($bibid,$tag, $subfieldcode);
179 while (($subfieldid) = $sti->fetchrow) {
181 $lastsubfieldid=$subfieldid;
183 if ($resultcounter>1) {
184 # Error condition. Values given did not resolve into a unique record. Don't know what to edit
185 # should rarely occur (only if we use subfieldvalue with a value that exists twice, which is strange)
188 return $lastsubfieldid;
192 sub MARCaddSubfield {
193 # Add a new subfield to a tag into the DB.
198 my $subfieldcode=shift;
199 my $subfieldorder=shift;
200 my $subfieldvalue=shift;
202 # my $dbh=&C4Connect;
203 unless ($subfieldorder) {
204 my $sth=$dbh->prepare("select max(subfieldorder) from marc_subfield_table where tag=$tagid");
207 ($subfieldorder) = $sth->fetchrow;
213 if (length($subfieldvalue)>255) {
214 $dbh->do("lock tables marc_blob_subfield WRITE, marc_subfield_table WRITE");
215 my $sth=$dbh->prepare("insert into marc_blob_subfield (subfieldvalue) values (?)");
216 $sth->execute($subfieldvalue);
217 $sth=$dbh->prepare("select max(blobidlink)from marc_blob_subfield");
219 my ($res)=$sth->fetchrow;
220 my $sth=$dbh->prepare("insert into marc_subfield_table (bibid,tag,tagorder,subfieldcode,subfieldorder,valuebloblink) values (?,?,?,?,?,?)");
221 $sth->execute($bibid,$tagid,$tagorder,$subfieldcode,$subfieldorder,$res);
222 $dbh->do("unlock tables");
224 my $sth=$dbh->prepare("insert into marc_subfield_table (bibid,tag,tagorder,subfieldcode,subfieldorder,subfieldvalue) values (?,?,?,?,?,?)");
225 $sth->execute($bibid,$tagid,$tagorder,$subfieldcode,$subfieldorder,$subfieldvalue);
229 sub MARCaddMarcBiblio {
230 # pass the MARC::Record to this function, and it will create the records in the marc tables
232 my @fields=$record->fields();
235 # adding main table, and retrieving bibid
236 $dbh->do("lock tables marc_biblio WRITE");
237 my $sth=$dbh->prepare("insert into marc_biblio (datecreated) values (now())");
239 $sth=$dbh->prepare("select max(bibid) from marc_biblio");
241 ($bibid)=$sth->fetchrow;
242 # print "BIBID :::".$marcstructure->{bibid}."\n";
244 $dbh->do("unlock tables");
246 # now, add subfields...
247 foreach my $field (@fields) {
248 my @subfields=$field->subfields();
250 foreach my $subfieldcount (0..$#subfields) {
251 print $field->tag().":".$field->indicator(1).$field->indicator(2).":".$subfields[$subfieldcount][0].":".$subfields[$subfieldcount][1]."\n";
252 &MARCaddSubfield($bibid,
254 $field->indicator(1).$field->indicator(2),
256 $subfields[$subfieldcount][0],
258 $subfields[$subfieldcount][1]
265 # this function builds MARC::Record from the old koha-DB fields
266 my ($biblionumber,$author,$title,$unititle,$notes,$abstract,
267 $serial,$seriestitle,$copyrightdate,$biblioitemnumber,$volume,$number,
268 $classification,$itemtype,$isbn,$issn,
269 $dewey,$subclass,$publicationyear,$publishercode,
270 $volumedate,$illus,$pages,$notes,
271 $size,$place,$lccn) = @_;
273 my $record = MARC::Record->new();
274 # my $dbh=&C4Connect;
275 my $sth=$dbh->prepare("select tagfield,tagsubfield from marc_subfield_structure where kohafield=?");
276 &MARCkoha2marcOnefield($sth,$record,"biblio.author",$author);
277 &MARCkoha2marcOnefield($sth,$record,"biblio.title",$title);
278 &MARCkoha2marcOnefield($sth,$record,"biblio.unititle",$unititle);
279 &MARCkoha2marcOnefield($sth,$record,"biblio.notes",$notes);
280 &MARCkoha2marcOnefield($sth,$record,"biblio.abstract",$abstract);
281 &MARCkoha2marcOnefield($sth,$record,"biblio.serial",$serial);
282 &MARCkoha2marcOnefield($sth,$record,"biblio.seriestitle",$seriestitle);
283 &MARCkoha2marcOnefield($sth,$record,"biblio.copyrightdate",$copyrightdate);
284 &MARCkoha2marcOnefield($sth,$record,"biblioitems.biblionumber",$biblionumber);
285 &MARCkoha2marcOnefield($sth,$record,"biblioitems.biblioitemnumber",$biblioitemnumber);
286 &MARCkoha2marcOnefield($sth,$record,"biblioitems.volume",$volume);
287 &MARCkoha2marcOnefield($sth,$record,"biblioitems.number",$number);
288 &MARCkoha2marcOnefield($sth,$record,"biblioitems.classification",$classification);
289 &MARCkoha2marcOnefield($sth,$record,"biblioitems.itemtype",$itemtype);
290 &MARCkoha2marcOnefield($sth,$record,"biblioitems.isbn",$isbn);
291 &MARCkoha2marcOnefield($sth,$record,"biblioitems.issn",$issn);
292 &MARCkoha2marcOnefield($sth,$record,"biblioitems.dewey",$dewey);
293 &MARCkoha2marcOnefield($sth,$record,"biblioitems.subclass",$subclass);
294 &MARCkoha2marcOnefield($sth,$record,"biblioitems.publicationyear",$publicationyear);
295 &MARCkoha2marcOnefield($sth,$record,"biblioitems.publishercode",$publishercode);
296 &MARCkoha2marcOnefield($sth,$record,"biblioitems.volumedate",$volumedate);
297 &MARCkoha2marcOnefield($sth,$record,"biblioitems.illus",$illus);
298 &MARCkoha2marcOnefield($sth,$record,"biblioitems.pages",$pages);
299 &MARCkoha2marcOnefield($sth,$record,"biblioitems.notes",$notes);
300 &MARCkoha2marcOnefield($sth,$record,"biblioitems.size",$size);
301 &MARCkoha2marcOnefield($sth,$record,"biblioitems.place",$place);
302 &MARCkoha2marcOnefield($sth,$record,"biblioitems.lccn",$lccn);
303 print "RECORD : ".$record->as_formatted()."\n";
307 sub MARCkoha2marcOnefield {
308 my ($sth,$record,$kohafieldname,$value)=@_;
311 $sth->execute($kohafieldname);
312 if (($tagfield,$tagsubfield)=$sth->fetchrow) {
313 if ($record->field($tagfield)) {
314 my $tag =$record->field($tagfield);
316 $tag->add_subfields($tagsubfield,$value);
317 $record->delete_field($tag);
318 $record->add_fields($tag);
321 $record->add_fields($tagfield," "," ",$tagsubfield => $value);
328 # Update the biblio with biblionumber $biblio->{'biblionumber'}
329 # I guess this routine should search through all marc records for a record that
330 # has the same biblionumber stored in it, and modify the MARC record as well as
333 # Also, this subroutine should search through the $biblio object and compare it
334 # to the existing record and _LOG ALL CHANGES MADE_ in some way. I'd like for
335 # this logging feature to be usable to undo changes easily.
337 my ($env, $biblio) = @_;
339 my $biblionumber=$biblio->{'biblionumber'};
341 my $sth=$dbh->prepare("select * from biblio where biblionumber=$biblionumber");
343 my $origbiblio=$sth->fetchrow_hashref;
344 $sth=$dbh->prepare("select subtitle from bibliosubtitle where biblionumber=$biblionumber");
346 my ($subtitle)=$sth->fetchrow;
347 $origbiblio->{'subtitle'}=$subtitle;
348 $sth=$dbh->prepare("select author from additionalauthors where biblionumber=$biblionumber");
350 my $origadditionalauthors;
351 while (my ($author) = $sth->fetchrow) {
352 push (@{$origbiblio->{'additionalauthors'}}, $author);
353 $origadditionalauthors->{$author}=1;
355 $sth=$dbh->prepare("select subject from bibliosubject where biblionumber=$biblionumber");
358 while (my ($subject) = $sth->fetchrow) {
359 push (@{$origbiblio->{'subjects'}}, $subject);
360 $origsubjects->{$subject}=1;
364 # Obtain a list of MARC Record_ID's that are tied to this biblio
365 $sth=$dbh->prepare("select bibid from marc_subfield_table where tag='090' and subfieldvalue=$biblionumber and subfieldcode='c'");
368 while (my ($bibid) = $sth->fetchrow) {
369 push(@marcrecords, $bibid);
375 if ($biblio->{'author'} ne $origbiblio->{'author'}) {
376 my $q_author=$dbh->quote($biblio->{'author'});
377 logchange('kohadb', 'change', 'biblio', 'author', $origbiblio->{'author'}, $biblio->{'author'});
378 my $sti=$dbh->prepare("update biblio set author=$q_author where biblionumber=$biblio->{'biblionumber'}");
380 foreach $bibid (@marcrecords) {
381 logchange('marc', 'change', $bibid, '100', 'a', $origbiblio->{'author'}, $biblio->{'author'});
382 changeSubfield($bibid, '100', 'a', $origbiblio->{'author'}, $biblio->{'author'});
385 if ($biblio->{'title'} ne $origbiblio->{'title'}) {
386 my $q_title=$dbh->quote($biblio->{'title'});
387 logchange('kohadb', 'change', 'biblio', 'title', $origbiblio->{'title'}, $biblio->{'title'});
388 my $sti=$dbh->prepare("update biblio set title=$q_title where biblionumber=$biblio->{'biblionumber'}");
390 foreach $Record_ID (@marcrecords) {
391 logchange('marc', 'change', $Record_ID, '245', 'a', $origbiblio->{'title'}, $biblio->{'title'});
392 changeSubfield($Record_ID, '245', 'a', $origbiblio->{'title'}, $biblio->{'title'});
395 if ($biblio->{'subtitle'} ne $origbiblio->{'subtitle'}) {
396 my $q_subtitle=$dbh->quote($biblio->{'subtitle'});
397 logchange('kohadb', 'change', 'biblio', 'subtitle', $origbiblio->{'subtitle'}, $biblio->{'subtitle'});
398 my $sti=$dbh->prepare("update bibliosubtitle set subtitle=$q_subtitle where biblionumber=$biblio->{'biblionumber'}");
400 foreach $Record_ID (@marcrecords) {
401 logchange('marc', 'change', $Record_ID, '245', 'b', $origbiblio->{'subtitle'}, $biblio->{'subtitle'});
402 changeSubfield($Record_ID, '245', 'b', $origbiblio->{'subtitle'}, $biblio->{'subtitle'});
405 if ($biblio->{'unititle'} ne $origbiblio->{'unititle'}) {
406 my $q_unititle=$dbh->quote($biblio->{'unititle'});
407 logchange('kohadb', 'change', 'biblio', 'unititle', $origbiblio->{'unititle'}, $biblio->{'unititle'});
408 my $sti=$dbh->prepare("update biblio set unititle=$q_unititle where biblionumber=$biblio->{'biblionumber'}");
411 if ($biblio->{'notes'} ne $origbiblio->{'notes'}) {
412 my $q_notes=$dbh->quote($biblio->{'notes'});
413 logchange('kohadb', 'change', 'biblio', 'notes', $origbiblio->{'notes'}, $biblio->{'notes'});
414 my $sti=$dbh->prepare("update biblio set notes=$q_notes where biblionumber=$biblio->{'biblionumber'}");
416 foreach $Record_ID (@marcrecords) {
417 logchange('marc', 'change', $Record_ID, '500', 'a', $origbiblio->{'notes'}, $biblio->{'notes'});
418 changeSubfield($Record_ID, '500', 'a', $origbiblio->{'notes'}, $biblio->{'notes'});
421 if ($biblio->{'serial'} ne $origbiblio->{'serial'}) {
422 my $q_serial=$dbh->quote($biblio->{'serial'});
423 logchange('kohadb', 'change', 'biblio', 'serial', $origbiblio->{'serial'}, $biblio->{'serial'});
424 my $sti=$dbh->prepare("update biblio set serial=$q_serial where biblionumber=$biblio->{'biblionumber'}");
427 if ($biblio->{'seriestitle'} ne $origbiblio->{'seriestitle'}) {
428 my $q_seriestitle=$dbh->quote($biblio->{'seriestitle'});
429 logchange('kohadb', 'change', 'biblio', 'seriestitle', $origbiblio->{'seriestitle'}, $biblio->{'seriestitle'});
430 my $sti=$dbh->prepare("update biblio set seriestitle=$q_seriestitle where biblionumber=$biblio->{'biblionumber'}");
432 foreach $Record_ID (@marcrecords) {
433 logchange('marc', 'change', $Record_ID, '440', 'a', $origbiblio->{'seriestitle'}, $biblio->{'seriestitle'});
434 changeSubfield($Record_ID, '440', 'a', $origbiblio->{'seriestitle'}, $biblio->{'seriestitle'});
437 if ($biblio->{'copyrightdate'} ne $origbiblio->{'copyrightdate'}) {
438 my $q_copyrightdate=$dbh->quote($biblio->{'copyrightdate'});
439 logchange('kohadb', 'change', 'biblio', 'copyrightdate', $origbiblio->{'copyrightdate'}, $biblio->{'copyrightdate'});
440 my $sti=$dbh->prepare("update biblio set copyrightdate=$q_copyrightdate where biblionumber=$biblio->{'biblionumber'}");
442 foreach $Record_ID (@marcrecords) {
443 logchange('marc', 'change', $Record_ID, '260', 'c', "c$origbiblio->{'notes'}", "c$biblio->{'notes'}");
444 changeSubfield($Record_ID, '260', 'c', "c$origbiblio->{'notes'}", "c$biblio->{'notes'}");
448 # Check for subject heading changes
452 foreach $newsubject (@{$biblio->{'subject'}}) {
453 $subjects->{$newsubject}=1;
454 if ($origsubjects->{$newsubject}) {
455 $subjects->{$newsubject}=2;
457 my $q_newsubject=$dbh->quote($newsubject);
458 my $sth=$dbh->prepare("insert into bibliosubject (subject,biblionumber) values ($q_newsubject, $biblionumber)");
460 logchange('kohadb', 'add', 'biblio', 'subject', $newsubject);
462 $subfields->{1}->{'Subfield_Mark'}='a';
463 $subfields->{1}->{'Subfield_Value'}=$newsubject;
466 foreach $Record_ID (@marcrecords) {
467 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
468 logchange('marc', 'add', $Record_ID, '650', 'a', $newsubject);
473 foreach $origsubject (keys %$origsubjects) {
474 if ($subjects->{$origsubject} == 1) {
475 my $q_origsubject=$dbh->quote($origsubject);
476 logchange('kohadb', 'delete', 'biblio', '$biblionumber', 'subject', $origsubject);
477 my $sth=$dbh->prepare("delete from bibliosubject where biblionumber=$biblionumber and subject=$q_origsubject");
483 # At the moment this is just a straight copy of the subject code. Needs heavy
484 # modification to work for additional authors, obviously.
485 # Check for additional author changes
487 my $newadditionalauthor='';
488 my $additionalauthors;
489 foreach $newadditionalauthor (@{$biblio->{'additionalauthor'}}) {
490 $additionalauthors->{$newadditionalauthor}=1;
491 if ($origadditionalauthors->{$newadditionalauthor}) {
492 $additionalauthors->{$newadditionalauthor}=2;
494 my $q_newadditionalauthor=$dbh->quote($newadditionalauthor);
495 my $sth=$dbh->prepare("insert into biblioadditionalauthors (additionalauthor,biblionumber) values ($q_newadditionalauthor, $biblionumber)");
497 logchange('kohadb', 'add', 'biblio', 'additionalauthor', $newadditionalauthor);
499 $subfields->{1}->{'Subfield_Mark'}='a';
500 $subfields->{1}->{'Subfield_Value'}=$newadditionalauthor;
503 foreach $Record_ID (@marcrecords) {
504 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
505 logchange('marc', 'add', $Record_ID, '650', 'a', $newadditionalauthor);
509 my $origadditionalauthor;
510 foreach $origadditionalauthor (keys %$origadditionalauthors) {
511 if ($additionalauthors->{$origadditionalauthor} == 1) {
512 my $q_origadditionalauthor=$dbh->quote($origadditionalauthor);
513 logchange('kohadb', 'delete', 'biblio', '$biblionumber', 'additionalauthor', $origadditionalauthor);
514 my $sth=$dbh->prepare("delete from biblioadditionalauthors where biblionumber=$biblionumber and additionalauthor=$q_origadditionalauthor");
524 # Subroutine to log changes to databases
525 # Eventually, this subroutine will be used to create a log of all changes made,
526 # with the possibility of "undo"ing some changes
528 if ($database eq 'kohadb') {
534 print STDERR "KOHA: $type $section $item $original $new\n";
535 } elsif ($database eq 'marc') {
540 my $subfield_ID=shift;
543 print STDERR "MARC: $type $Record_ID $tag $mark $subfield_ID $original $new\n";
548 my ($env, $biblioitem) = @_;
550 my $biblionumber=$biblioitem->{'biblionumber'};
551 my $biblioitemnumber=$biblioitem->{'biblioitemnumber'};
552 my $volume=$biblioitem->{'volume'};
553 my $q_volume=$dbh->quote($volume);
554 my $number=$biblioitem->{'number'};
555 my $q_number=$dbh->quote($number);
556 my $classification=$biblioitem->{'classification'};
557 my $q_classification=$dbh->quote($classification);
558 my $itemtype=$biblioitem->{'itemtype'};
559 my $q_itemtype=$dbh->quote($itemtype);
560 my $isbn=$biblioitem->{'isbn'};
561 my $q_isbn=$dbh->quote($isbn);
562 my $issn=$biblioitem->{'issn'};
563 my $q_issn=$dbh->quote($issn);
564 my $dewey=$biblioitem->{'dewey'};
566 ($dewey == 0) && ($dewey='');
567 my $subclass=$biblioitem->{'subclass'};
568 my $q_subclass=$dbh->quote($subclass);
569 my $publicationyear=$biblioitem->{'publicationyear'};
570 my $publishercode=$biblioitem->{'publishercode'};
571 my $q_publishercode=$dbh->quote($publishercode);
572 my $volumedate=$biblioitem->{'volumedate'};
573 my $q_volumedate=$dbh->quote($volumedate);
574 my $illus=$biblioitem->{'illus'};
575 my $q_illus=$dbh->quote($illus);
576 my $pages=$biblioitem->{'pages'};
577 my $q_pages=$dbh->quote($pages);
578 my $notes=$biblioitem->{'notes'};
579 my $q_notes=$dbh->quote($notes);
580 my $size=$biblioitem->{'size'};
581 my $q_size=$dbh->quote($size);
582 my $place=$biblioitem->{'place'};
583 my $q_place=$dbh->quote($place);
584 my $lccn=$biblioitem->{'lccn'};
585 my $q_lccn=$dbh->quote($lccn);
588 # Unless the $env->{'marconly'} flag is set, update the biblioitems table with
591 unless ($env->{'marconly'}) {
592 #my $sth=$dbh->prepare("lock tables biblioitems write");
594 my $sth=$dbh->prepare("select max(biblioitemnumber) from biblioitems");
596 my ($biblioitemnumber) =$sth->fetchrow;
598 $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)");
600 #my $sth=$dbh->prepare("unlock tables");
605 # Should we check if there is already a biblioitem/marc with the
606 # same isbn/lccn/issn?
608 my $sth=$dbh->prepare("select title,unititle,seriestitle,copyrightdate,notes,author from biblio where biblionumber=$biblionumber");
610 my ($title, $unititle,$seriestitle,$copyrightdate,$biblionotes,$author) = $sth->fetchrow;
611 $sth=$dbh->prepare("select subtitle from bibliosubtitle where biblionumber=$biblionumber");
613 my ($subtitle) = $sth->fetchrow;
614 $sth=$dbh->prepare("select author from additionalauthors where biblionumber=$biblionumber");
616 my @additionalauthors;
617 while (my ($additionalauthor) = $sth->fetchrow) {
618 push (@additionalauthors, $additionalauthor);
620 $sth=$dbh->prepare("select subject from bibliosubject where biblionumber=$biblionumber");
623 while (my ($subject) = $sth->fetchrow) {
624 push (@subjects, $subject);
629 $sth=$dbh->prepare("insert into Resource_Table (Record_ID) values (0)");
631 my $Resource_ID=$dbh->{'mysql_insertid'};
632 my $Record_ID=$Resource_ID;
633 $sth=$dbh->prepare("update Resource_Table set Record_ID=$Record_ID where Resource_ID=$Resource_ID");
639 $subfields->{1}->{'Subfield_Mark'}='a';
640 $subfields->{1}->{'Subfield_Value'}=$title;
642 $subfields->{2}->{'Subfield_Mark'}='b';
643 $subfields->{2}->{'Subfield_Value'}=$subtitle;
646 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
652 $subfields->{1}->{'Subfield_Mark'}='a';
653 $subfields->{1}->{'Subfield_Value'}=$author;
655 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
660 $subfields->{1}->{'Subfield_Mark'}='a';
661 $subfields->{1}->{'Subfield_Value'}=$seriestitle;
663 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
668 $subfields->{1}->{'Subfield_Mark'}='a';
669 $subfields->{1}->{'Subfield_Value'}=$biblionotes;
670 $subfields->{2}->{'Subfield_Mark'}='3';
671 $subfields->{2}->{'Subfield_Value'}='biblio';
673 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
676 foreach (@additionalauthors) {
678 (next) unless ($author);
680 $subfields->{1}->{'Subfield_Mark'}='a';
681 $subfields->{1}->{'Subfield_Value'}=$author;
682 $subfields->{2}->{'Subfield_Mark'}='e';
683 $subfields->{2}->{'Subfield_Value'}='author';
685 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
689 (next) unless ($illus);
691 $subfields->{1}->{'Subfield_Mark'}='a';
692 $subfields->{1}->{'Subfield_Value'}=$illus;
693 $subfields->{2}->{'Subfield_Mark'}='e';
694 $subfields->{2}->{'Subfield_Value'}='illustrator';
696 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
699 foreach (@subjects) {
701 (next) unless ($subject);
703 $subfields->{1}->{'Subfield_Mark'}='a';
704 $subfields->{1}->{'Subfield_Value'}=$subject;
706 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
713 $subfields->{1}->{'Subfield_Mark'}='a';
714 $subfields->{1}->{'Subfield_Value'}=$isbn;
716 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
721 $subfields->{1}->{'Subfield_Mark'}='a';
722 $subfields->{1}->{'Subfield_Value'}=$lccn;
724 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
729 $subfields->{1}->{'Subfield_Mark'}='a';
730 $subfields->{1}->{'Subfield_Value'}=$issn;
732 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
737 $subfields->{1}->{'Subfield_Mark'}='a';
738 $subfields->{1}->{'Subfield_Value'}=$dewey;
740 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
742 # DEWEY subclass and itemtype
745 $subfields->{1}->{'Subfield_Mark'}='a';
746 $subfields->{1}->{'Subfield_Value'}=$itemtype;
747 $subfields->{2}->{'Subfield_Mark'}='b';
748 $subfields->{2}->{'Subfield_Value'}=$subclass;
749 $subfields->{3}->{'Subfield_Mark'}='c';
750 $subfields->{3}->{'Subfield_Value'}=$biblionumber;
751 $subfields->{4}->{'Subfield_Mark'}='d';
752 $subfields->{4}->{'Subfield_Value'}=$biblioitemnumber;
754 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
759 $subfields->{1}->{'Subfield_Mark'}='a';
760 $subfields->{1}->{'Subfield_Value'}=$place;
761 $subfields->{2}->{'Subfield_Mark'}='b';
762 $subfields->{2}->{'Subfield_Value'}=$publishercode;
763 $subfields->{3}->{'Subfield_Mark'}='c';
764 $subfields->{3}->{'Subfield_Value'}=$publicationyear;
765 if ($copyrightdate) {
766 $subfields->{4}->{'Subfield_Mark'}='c';
767 $subfields->{4}->{'Subfield_Value'}="c$copyrightdate";
770 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
773 if ($pages || $size) {
775 $subfields->{1}->{'Subfield_Mark'}='a';
776 $subfields->{1}->{'Subfield_Value'}=$pages;
777 $subfields->{2}->{'Subfield_Mark'}='c';
778 $subfields->{2}->{'Subfield_Value'}=$size;
780 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
783 if ($volume || $number) {
785 $subfields->{1}->{'Subfield_Mark'}='v';
786 $subfields->{1}->{'Subfield_Value'}=$volume;
787 $subfields->{2}->{'Subfield_Mark'}='n';
788 $subfields->{2}->{'Subfield_Value'}=$number;
790 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
795 $subfields->{1}->{'Subfield_Mark'}='a';
796 $subfields->{1}->{'Subfield_Value'}=$notes;
797 $subfields->{2}->{'Subfield_Mark'}='3';
798 $subfields->{2}->{'Subfield_Value'}='biblioitem';
800 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
804 return ($env, $Record_ID);
807 sub updateBiblioItem {
808 # Update the biblioitem with biblioitemnumber $biblioitem->{'biblioitemnumber'}
810 # This routine should also check to see which fields are actually being
811 # modified, and log all changes.
813 my ($env, $biblioitem) = @_;
816 my $biblioitemnumber=$biblioitem->{'biblioitemnumber'};
817 my $sth=$dbh->prepare("select * from biblioitems where biblioitemnumber=$biblioitemnumber");
818 # obi = original biblioitem
819 my $obi=$sth->fetchrow_hashref;
820 $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");
822 my ($Record_ID) = $sth->fetchrow;
823 if ($biblioitem->{'biblionumber'} ne $obi->{'biblionumber'}) {
824 logchange('kohadb', 'change', 'biblioitems', 'biblionumber', $obi->{'biblionumber'}, $biblioitem->{'biblionumber'});
825 my $sth=$dbh->prepare("update biblioitems set biblionumber=$biblioitem->{'biblionumber'} where biblioitemnumber=$biblioitemnumber");
826 logchange('marc', 'change', $Record_ID, '090', 'c', $obi->{'biblionumber'}, $biblioitem->{'biblionumber'});
827 changeSubfield($Record_ID, '090', 'c', $obi->{'biblionumber'}, $biblioitem->{'biblionumber'});
829 if ($biblioitem->{'volume'} ne $obi->{'volume'}) {
830 logchange('kohadb', 'change', 'biblioitems', 'volume', $obi->{'volume'}, $biblioitem->{'volume'});
831 my $q_volume=$dbh->quote($biblioitem->{'volume'});
832 my $sth=$dbh->prepare("update biblioitems set volume=$q_volume where biblioitemnumber=$biblioitemnumber");
833 logchange('marc', 'change', $Record_ID, '440', 'v', $obi->{'volume'}, $biblioitem->{'volume'});
834 changeSubfield($Record_ID, '440', 'v', $obi->{'volume'}, $biblioitem->{'volume'});
836 if ($biblioitem->{'number'} ne $obi->{'number'}) {
837 logchange('kohadb', 'change', 'biblioitems', 'number', $obi->{'number'}, $biblioitem->{'number'});
838 my $q_number=$dbh->quote($biblioitem->{'number'});
839 my $sth=$dbh->prepare("update biblioitems set number=$q_number where biblioitemnumber=$biblioitemnumber");
840 logchange('marc', 'change', $Record_ID, '440', 'v', $obi->{'number'}, $biblioitem->{'number'});
841 changeSubfield($Record_ID, '440', 'v', $obi->{'number'}, $biblioitem->{'number'});
843 if ($biblioitem->{'itemtype'} ne $obi->{'itemtype'}) {
844 logchange('kohadb', 'change', 'biblioitems', 'itemtype', $obi->{'itemtype'}, $biblioitem->{'itemtype'});
845 my $q_itemtype=$dbh->quote($biblioitem->{'itemtype'});
846 my $sth=$dbh->prepare("update biblioitems set itemtype=$q_itemtype where biblioitemnumber=$biblioitemnumber");
847 logchange('marc', 'change', $Record_ID, '090', 'a', $obi->{'itemtype'}, $biblioitem->{'itemtype'});
848 changeSubfield($Record_ID, '090', 'a', $obi->{'itemtype'}, $biblioitem->{'itemtype'});
850 if ($biblioitem->{'isbn'} ne $obi->{'isbn'}) {
851 logchange('kohadb', 'change', 'biblioitems', 'isbn', $obi->{'isbn'}, $biblioitem->{'isbn'});
852 my $q_isbn=$dbh->quote($biblioitem->{'isbn'});
853 my $sth=$dbh->prepare("update biblioitems set isbn=$q_isbn where biblioitemnumber=$biblioitemnumber");
854 logchange('marc', 'change', $Record_ID, '020', 'a', $obi->{'isbn'}, $biblioitem->{'isbn'});
855 changeSubfield($Record_ID, '020', 'a', $obi->{'isbn'}, $biblioitem->{'isbn'});
857 if ($biblioitem->{'issn'} ne $obi->{'issn'}) {
858 logchange('kohadb', 'change', 'biblioitems', 'issn', $obi->{'issn'}, $biblioitem->{'issn'});
859 my $q_issn=$dbh->quote($biblioitem->{'issn'});
860 my $sth=$dbh->prepare("update biblioitems set issn=$q_issn where biblioitemnumber=$biblioitemnumber");
861 logchange('marc', 'change', $Record_ID, '022', 'a', $obi->{'issn'}, $biblioitem->{'issn'});
862 changeSubfield($Record_ID, '022', 'a', $obi->{'issn'}, $biblioitem->{'issn'});
864 if ($biblioitem->{'dewey'} ne $obi->{'dewey'}) {
865 logchange('kohadb', 'change', 'biblioitems', 'dewey', $obi->{'dewey'}, $biblioitem->{'dewey'});
866 my $sth=$dbh->prepare("update biblioitems set dewey=$biblioitem->{'dewey'} where biblioitemnumber=$biblioitemnumber");
867 logchange('marc', 'change', $Record_ID, '082', 'a', $obi->{'dewey'}, $biblioitem->{'dewey'});
868 changeSubfield($Record_ID, '082', 'a', $obi->{'dewey'}, $biblioitem->{'dewey'});
870 if ($biblioitem->{'subclass'} ne $obi->{'subclass'}) {
871 logchange('kohadb', 'change', 'biblioitems', 'subclass', $obi->{'subclass'}, $biblioitem->{'subclass'});
872 my $q_subclass=$dbh->quote($biblioitem->{'subclass'});
873 my $sth=$dbh->prepare("update biblioitems set subclass=$q_subclass where biblioitemnumber=$biblioitemnumber");
874 logchange('marc', 'change', $Record_ID, '090', 'b', $obi->{'subclass'}, $biblioitem->{'subclass'});
875 changeSubfield($Record_ID, '090', 'b', $obi->{'subclass'}, $biblioitem->{'subclass'});
877 if ($biblioitem->{'place'} ne $obi->{'place'}) {
878 logchange('kohadb', 'change', 'biblioitems', 'place', $obi->{'place'}, $biblioitem->{'place'});
879 my $q_place=$dbh->quote($biblioitem->{'place'});
880 my $sth=$dbh->prepare("update biblioitems set place=$q_place where biblioitemnumber=$biblioitemnumber");
881 logchange('marc', 'change', $Record_ID, '260', 'a', $obi->{'place'}, $biblioitem->{'place'});
882 changeSubfield($Record_ID, '260', 'a', $obi->{'place'}, $biblioitem->{'place'});
884 if ($biblioitem->{'publishercode'} ne $obi->{'publishercode'}) {
885 logchange('kohadb', 'change', 'biblioitems', 'publishercode', $obi->{'publishercode'}, $biblioitem->{'publishercode'});
886 my $q_publishercode=$dbh->quote($biblioitem->{'publishercode'});
887 my $sth=$dbh->prepare("update biblioitems set publishercode=$q_publishercode where biblioitemnumber=$biblioitemnumber");
888 logchange('marc', 'change', $Record_ID, '260', 'b', $obi->{'publishercode'}, $biblioitem->{'publishercode'});
889 changeSubfield($Record_ID, '260', 'b', $obi->{'publishercode'}, $biblioitem->{'publishercode'});
891 if ($biblioitem->{'publicationyear'} ne $obi->{'publicationyear'}) {
892 logchange('kohadb', 'change', 'biblioitems', 'publicationyear', $obi->{'publicationyear'}, $biblioitem->{'publicationyear'});
893 my $q_publicationyear=$dbh->quote($biblioitem->{'publicationyear'});
894 my $sth=$dbh->prepare("update biblioitems set publicationyear=$q_publicationyear where biblioitemnumber=$biblioitemnumber");
895 logchange('marc', 'change', $Record_ID, '260', 'c', $obi->{'publicationyear'}, $biblioitem->{'publicationyear'});
896 changeSubfield($Record_ID, '260', 'c', $obi->{'publicationyear'}, $biblioitem->{'publicationyear'});
898 if ($biblioitem->{'illus'} ne $obi->{'illus'}) {
899 logchange('kohadb', 'change', 'biblioitems', 'illus', $obi->{'illus'}, $biblioitem->{'illus'});
900 my $q_illus=$dbh->quote($biblioitem->{'illus'});
901 my $sth=$dbh->prepare("update biblioitems set illus=$q_illus where biblioitemnumber=$biblioitemnumber");
902 logchange('marc', 'change', $Record_ID, '700', 'a', $obi->{'illus'}, $biblioitem->{'illus'});
903 changeSubfield($Record_ID, '700', 'a', $obi->{'illus'}, $biblioitem->{'illus'});
905 if ($biblioitem->{'pages'} ne $obi->{'pages'}) {
906 logchange('kohadb', 'change', 'biblioitems', 'pages', $obi->{'pages'}, $biblioitem->{'pages'});
907 my $q_pages=$dbh->quote($biblioitem->{'pages'});
908 my $sth=$dbh->prepare("update biblioitems set pages=$q_pages where biblioitemnumber=$biblioitemnumber");
909 logchange('marc', 'change', $Record_ID, '300', 'a', $obi->{'pages'}, $biblioitem->{'pages'});
910 changeSubfield($Record_ID, '300', 'a', $obi->{'pages'}, $biblioitem->{'pages'});
912 if ($biblioitem->{'size'} ne $obi->{'size'}) {
913 logchange('kohadb', 'change', 'biblioitems', 'size', $obi->{'size'}, $biblioitem->{'size'});
914 my $q_size=$dbh->quote($biblioitem->{'size'});
915 my $sth=$dbh->prepare("update biblioitems set size=$q_size where biblioitemnumber=$biblioitemnumber");
916 logchange('marc', 'change', $Record_ID, '300', 'c', $obi->{'size'}, $biblioitem->{'size'});
917 changeSubfield($Record_ID, '300', 'c', $obi->{'size'}, $biblioitem->{'size'});
919 if ($biblioitem->{'notes'} ne $obi->{'notes'}) {
920 logchange('kohadb', 'change', 'biblioitems', 'notes', $obi->{'notes'}, $biblioitem->{'notes'});
921 my $q_notes=$dbh->quote($biblioitem->{'notes'});
922 my $sth=$dbh->prepare("update biblioitems set notes=$q_notes where biblioitemnumber=$biblioitemnumber");
923 logchange('marc', 'change', $Record_ID, '500', 'a', $obi->{'notes'}, $biblioitem->{'notes'});
924 changeSubfield($Record_ID, '500', 'a', $obi->{'notes'}, $biblioitem->{'notes'});
926 if ($biblioitem->{'lccn'} ne $obi->{'lccn'}) {
927 logchange('kohadb', 'change', 'biblioitems', 'lccn', $obi->{'lccn'}, $biblioitem->{'lccn'});
928 my $q_lccn=$dbh->quote($biblioitem->{'lccn'});
929 my $sth=$dbh->prepare("update biblioitems set lccn=$q_lccn where biblioitemnumber=$biblioitemnumber");
930 logchange('marc', 'change', $Record_ID, '010', 'a', $obi->{'lccn'}, $biblioitem->{'lccn'});
931 changeSubfield($Record_ID, '010', 'a', $obi->{'lccn'}, $biblioitem->{'lccn'});
940 my ($env, $Record_ID, $item) = @_;
942 my $barcode=$item->{'barcode'};
943 my $q_barcode=$dbh->quote($barcode);
944 my $biblionumber=$item->{'biblionumber'};
945 my $biblioitemnumber=$item->{'biblioitemnumber'};
946 my $dateaccessioned=$item->{'dateaccessioned'};
947 my $booksellerid=$item->{'booksellerid'};
948 my $q_booksellerid=$dbh->quote($booksellerid);
949 my $homebranch=$item->{'homebranch'};
950 my $q_homebranch=$dbh->quote($homebranch);
951 my $holdingbranch=$item->{'holdingbranch'};
952 my $price=$item->{'price'};
953 my $replacementprice=$item->{'replacementprice'};
954 my $replacementpricedate=$item->{'replacementpricedate'};
955 my $q_replacementpricedate=$dbh->quote($replacementpricedate);
956 my $notforloan=$item->{'notforloan'};
957 my $itemlost=$item->{'itemlost'};
958 my $wthdrawn=$item->{'wthdrawn'};
959 my $restricted=$item->{'restricted'};
960 my $itemnotes=$item->{'itemnotes'};
961 my $q_itemnotes=$dbh->quote($itemnotes);
962 my $itemtype=$item->{'itemtype'};
963 my $subclass=$item->{'subclass'};
967 unless ($env->{'marconly'}) {
968 my $sth=$dbh->prepare("select max(itemnumber) from items");
970 my ($itemnumber) =$sth->fetchrow;
972 $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)");
979 $subfields->{1}->{'Subfield_Mark'}='p';
980 $subfields->{1}->{'Subfield_Value'}=$barcode;
981 $subfields->{2}->{'Subfield_Mark'}='d';
982 $subfields->{2}->{'Subfield_Value'}=$dateaccessioned;
983 $subfields->{3}->{'Subfield_Mark'}='e';
984 $subfields->{3}->{'Subfield_Value'}=$booksellerid;
985 $subfields->{4}->{'Subfield_Mark'}='b';
986 $subfields->{4}->{'Subfield_Value'}=$homebranch;
987 $subfields->{5}->{'Subfield_Mark'}='l';
988 $subfields->{5}->{'Subfield_Value'}=$holdingbranch;
989 $subfields->{6}->{'Subfield_Mark'}='c';
990 $subfields->{6}->{'Subfield_Value'}=$price;
991 $subfields->{7}->{'Subfield_Mark'}='c';
992 $subfields->{7}->{'Subfield_Value'}=$replacementprice;
993 $subfields->{8}->{'Subfield_Mark'}='d';
994 $subfields->{8}->{'Subfield_Value'}=$replacementpricedate;
996 $subfields->{9}->{'Subfield_Mark'}='h';
997 $subfields->{9}->{'Subfield_Value'}='Not for loan';
1000 $subfields->{10}->{'Subfield_Mark'}='j';
1001 $subfields->{10}->{'Subfield_Value'}='Item lost';
1004 $subfields->{11}->{'Subfield_Mark'}='j';
1005 $subfields->{11}->{'Subfield_Value'}='Item withdrawn';
1008 $subfields->{12}->{'Subfield_Mark'}='z';
1009 $subfields->{12}->{'Subfield_Value'}=$itemnotes;
1013 $env->{'linkage'}=1;
1014 ($env, $Tag_ID) = addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
1015 $env->{'linkage'}=0;
1016 $env->{'linkid'}=$Tag_ID;
1019 $subfields2->{1}->{'Subfield_Mark'}='a';
1020 $subfields2->{1}->{'Subfield_Value'}='Coast Mountains School District';
1021 $subfields2->{1}->{'Subfield_Mark'}='b';
1022 $subfields2->{1}->{'Subfield_Value'}=$homebranch;
1023 $subfields2->{1}->{'Subfield_Mark'}='c';
1024 $subfields2->{1}->{'Subfield_Value'}=$itemtype;
1025 $subfields2->{2}->{'Subfield_Mark'}='m';
1026 $subfields2->{2}->{'Subfield_Value'}=$subclass;
1027 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields2);
1028 $env->{'linkid'}='';
1032 # Update the item with itemnumber $item->{'itemnumber'}
1033 # This routine should also modify the corresponding MARC record data. (852 and
1034 # 876 tags with 876p tag the same as $item->{'barcode'}
1036 # This routine should also check to see which fields are actually being
1037 # modified, and log all changes.
1039 my ($env, $item) = @_;
1041 my $itemnumber=$item->{'itemnumber'};
1042 my $biblionumber=$item->{'biblionumber'};
1043 my $biblioitemnumber=$item->{'biblioitemnumber'};
1044 my $barcode=$item->{'barcode'};
1045 my $dateaccessioned=$item->{'dateaccessioned'};
1046 my $booksellerid=$item->{'booksellerid'};
1047 my $homebranch=$item->{'homebranch'};
1048 my $price=$item->{'price'};
1049 my $replacementprice=$item->{'replacementprice'};
1050 my $replacementpricedate=$item->{'replacementpricedate'};
1051 my $multivolume=$item->{'multivolume'};
1052 my $stack=$item->{'stack'};
1053 my $notforloan=$item->{'notforloan'};
1054 my $itemlost=$item->{'itemlost'};
1055 my $wthdrawn=$item->{'wthdrawn'};
1056 my $bulk=$item->{'bulk'};
1057 my $restricted=$item->{'restricted'};
1058 my $binding=$item->{'binding'};
1059 my $itemnotes=$item->{'itemnotes'};
1060 my $holdingbranch=$item->{'holdingbranch'};
1061 my $interim=$item->{'interim'};
1062 my $sth=$dbh->prepare("select * from items where itemnumber=$itemnumber");
1064 my $olditem=$sth->fetchrow_hashref;
1065 my $q_barcode=$dbh->quote($olditem->{'barcode'});
1066 $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");
1068 my ($Subfield876_ID, $Record_ID) = $sth->fetchrow;
1069 $sth=$dbh->prepare("select Subfield_Value from 8XX_Subfield_Table where Subfield_Mark=8 and Subfield_ID=$Subfield876_ID");
1071 my ($link) = $sth->fetchrow;
1072 $sth=$dbh->prepare("select Subfield_ID from 8XX_Subfield_Table where Subfield_Mark=8 and Subfield_Value=$link and !(Subfield_ID=$Subfield876_ID)");
1074 my ($Subfield852_ID) = $sth->fetchrow;
1076 if ($item->{'barcode'} ne $olditem->{'barcode'}) {
1077 logchange('kohadb', 'change', 'items', 'barcode', $olditem->{'barcode'}, $item->{'barcode'});
1078 my $q_barcode=$dbh->quote($item->{'barcode'});
1079 my $sth=$dbh->prepare("update items set barcode=$q_barcode where itemnumber=$itemnumber");
1081 my ($Subfield_ID, $Subfield_Key) = changeSubfield($Record_ID, '876', 'p', $olditem->{'barcode'}, $item->{'barcode'}, $Subfield876_ID);
1082 logchange('marc', 'change', $Record_ID, '876', 'p', $Subfield_Key, $olditem->{'barcode'}, $item->{'barcode'});
1084 if ($item->{'booksellerid'} ne $olditem->{'booksellerid'}) {
1085 logchange('kohadb', 'change', 'items', 'booksellerid', $olditem->{'booksellerid'}, $item->{'booksellerid'});
1086 my $q_booksellerid=$dbh->quote($item->{'booksellerid'});
1087 my $sth=$dbh->prepare("update items set booksellerid=$q_booksellerid where itemnumber=$itemnumber");
1089 my ($Subfield_ID, $Subfield_Key) = changeSubfield($Record_ID, '876', 'e', $olditem->{'booksellerid'}, $item->{'booksellerid'}, $Subfield876_ID);
1090 logchange('marc', 'change', $Record_ID, '876', 'e', $Subfield_Key, $olditem->{'booksellerid'}, $item->{'booksellerid'});
1092 if ($item->{'dateaccessioned'} ne $olditem->{'dateaccessioned'}) {
1093 logchange('kohadb', 'change', 'items', 'dateaccessioned', $olditem->{'dateaccessioned'}, $item->{'dateaccessioned'});
1094 my $q_dateaccessioned=$dbh->quote($item->{'dateaccessioned'});
1095 my $sth=$dbh->prepare("update items set dateaccessioned=$q_dateaccessioned where itemnumber=$itemnumber");
1097 my ($Subfield_ID, $Subfield_Key) = changeSubfield($Record_ID, '876', 'd', $olditem->{'dateaccessioned'}, $item->{'dateaccessioned'}, $Subfield876_ID);
1098 logchange('marc', 'change', $Record_ID, '876', 'd', $Subfield_Key, $olditem->{'dateaccessioned'}, $item->{'dateaccessioned'});
1100 if ($item->{'homebranch'} ne $olditem->{'homebranch'}) {
1101 logchange('kohadb', 'change', 'items', 'homebranch', $olditem->{'homebranch'}, $item->{'homebranch'});
1102 my $q_homebranch=$dbh->quote($item->{'homebranch'});
1103 my $sth=$dbh->prepare("update items set homebranch=$q_homebranch where itemnumber=$itemnumber");
1105 my ($Subfield_ID, $Subfield_Key) = changeSubfield($Record_ID, '876', 'b', $olditem->{'homebranch'}, $item->{'homebranch'}, $Subfield876_ID);
1106 logchange('marc', 'change', $Record_ID, '876', 'b', $Subfield_Key, $olditem->{'homebranch'}, $item->{'homebranch'});
1108 if ($item->{'holdingbranch'} ne $olditem->{'holdingbranch'}) {
1109 logchange('kohadb', 'change', 'items', 'holdingbranch', $olditem->{'holdingbranch'}, $item->{'holdingbranch'});
1110 my $q_holdingbranch=$dbh->quote($item->{'holdingbranch'});
1111 my $sth=$dbh->prepare("update items set holdingbranch=$q_holdingbranch where itemnumber=$itemnumber");
1113 my ($Subfield_ID, $Subfield_Key) = changeSubfield($Record_ID, '876', 'l', $olditem->{'holdingbranch'}, $item->{'holdingbranch'}, $Subfield876_ID);
1114 logchange('marc', 'change', $Record_ID, '876', 'l', $Subfield_Key, $olditem->{'holdingbranch'}, $item->{'holdingbranch'});
1116 if ($item->{'price'} ne $olditem->{'price'}) {
1117 logchange('kohadb', 'change', 'items', 'price', $olditem->{'price'}, $item->{'price'});
1118 my $q_price=$dbh->quote($item->{'price'});
1119 my $sth=$dbh->prepare("update items set price=$q_price where itemnumber=$itemnumber");
1121 my ($Subfield_ID, $Subfield_Key) = changeSubfield($Record_ID, '876', 'c', $olditem->{'price'}, $item->{'price'}, $Subfield876_ID);
1122 logchange('marc', 'change', $Record_ID, '876', 'c', $Subfield_Key, $olditem->{'price'}, $item->{'price'});
1124 if ($item->{'itemnotes'} ne $olditem->{'itemnotes'}) {
1125 logchange('kohadb', 'change', 'items', 'itemnotes', $olditem->{'itemnotes'}, $item->{'itemnotes'});
1126 my $q_itemnotes=$dbh->quote($item->{'itemnotes'});
1127 my $sth=$dbh->prepare("update items set itemnotes=$q_itemnotes where itemnumber=$itemnumber");
1129 my ($Subfield_ID, $Subfield_Key) = changeSubfield($Record_ID, '876', 'c', $olditem->{'itemnotes'}, $item->{'itemnotes'}, $Subfield876_ID);
1130 logchange('marc', 'change', $Record_ID, '876', 'c', $Subfield_Key, $olditem->{'itemnotes'}, $item->{'itemnotes'});
1132 if ($item->{'notforloan'} ne $olditem->{'notforloan'}) {
1133 logchange('kohadb', 'change', 'items', 'notforloan', $olditem->{'notforloan'}, $item->{'notforloan'});
1134 my $sth=$dbh->prepare("update items set notforloan=$notforloan where itemnumber=$itemnumber");
1136 if ($item->{'notforloan'}) {
1137 my ($Subfield_ID, $Subfield_Key) = addSubfield($Record_ID, '876', 'h', 'Not for loan', $Subfield876_ID);
1138 logchange('marc', 'add', $Record_ID, '876', 'h', $Subfield_Key, 'Not for loan');
1140 my ($Subfield_ID, $Subfield_Key) = deleteSubfield($Record_ID, '876', 'h', 'Not for loan', $Subfield876_ID);
1141 logchange('marc', 'delete', $Record_ID, '876', 'h', $Subfield_Key, 'Not for loan');
1144 if ($item->{'itemlost'} ne $olditem->{'itemlost'}) {
1145 logchange('kohadb', 'change', 'items', 'itemlost', $olditem->{'itemlost'}, $item->{'itemlost'});
1146 my $sth=$dbh->prepare("update items set itemlost=$itemlost where itemnumber=$itemnumber");
1148 if ($item->{'itemlost'}) {
1149 my ($Subfield_ID, $Subfield_Key) = addSubfield($Record_ID, '876', 'h', 'Item lost', $Subfield876_ID);
1150 logchange('marc', 'add', $Record_ID, '876', 'h', $Subfield_Key, 'Item lost');
1152 my ($Subfield_ID, $Subfield_Key) = deleteSubfield($Record_ID, '876', 'h', 'Item lost', $Subfield876_ID);
1153 logchange('marc', 'delete', $Record_ID, '876', 'h', $Subfield_Key, 'Item lost');
1156 if ($item->{'wthdrawn'} ne $olditem->{'wthdrawn'}) {
1157 logchange('kohadb', 'change', 'items', 'wthdrawn', $olditem->{'wthdrawn'}, $item->{'wthdrawn'});
1158 my $sth=$dbh->prepare("update items set wthdrawn=$wthdrawn where itemnumber=$itemnumber");
1160 if ($item->{'wthdrawn'}) {
1161 my ($Subfield_ID, $Subfield_Key) = addSubfield($Record_ID, '876', 'h', 'Withdrawn', $Subfield876_ID);
1162 logchange('marc', 'add', $Record_ID, '876', 'h', $Subfield_Key, 'Withdrawn');
1164 my ($Subfield_ID, $Subfield_Key) = deleteSubfield($Record_ID, '876', 'h', 'Withdrawn', $Subfield876_ID);
1165 logchange('marc', 'delete', $Record_ID, '876', 'h', $Subfield_Key, 'Withdrawn');
1168 if ($item->{'restricted'} ne $olditem->{'restricted'}) {
1169 logchange('kohadb', 'change', 'items', 'restricted', $olditem->{'restricted'}, $item->{'restricted'});
1170 my $sth=$dbh->prepare("update items set restricted=$restricted where itemnumber=$itemnumber");
1172 if ($item->{'restricted'}) {
1173 my ($Subfield_ID, $Subfield_Key) = addSubfield($Record_ID, '876', 'h', 'Restricted', $Subfield876_ID);
1174 logchange('marc', 'add', $Record_ID, '876', 'h', $Subfield_Key, 'Restricted');
1176 my ($Subfield_ID, $Subfield_Key) = deleteSubfield($Record_ID, '876', 'h', 'Restricted', $Subfield876_ID);
1177 logchange('marc', 'delete', $Record_ID, '876', 'h', $Subfield_Key, 'Restricted');
1186 my ($supplierid)=@_;
1188 my $query = "Select count(*),authorisedby,entrydate,basketno from aqorders where
1189 booksellerid='$supplierid' and (quantity > quantityreceived or
1190 quantityreceived is NULL)
1191 and (datecancellationprinted is NULL or datecancellationprinted = '0000-00-00')";
1192 $query.=" group by basketno order by entrydate desc";
1194 my $sth=$dbh->prepare($query);
1198 while (my $data=$sth->fetchrow_hashref){
1204 return ($i,\@results);
1210 my $query="Select count(*) from items where biblionumber=$biblio";
1212 my $sth=$dbh->prepare($query);
1214 my $data=$sth->fetchrow_hashref;
1217 return($data->{'count(*)'});
1223 my $query="Select ordernumber from aqorders where biblionumber=$bib and
1224 biblioitemnumber='$bi'";
1225 my $sth=$dbh->prepare($query);
1227 my $ordnum=$sth->fetchrow_hashref;
1229 my $order=getsingleorder($ordnum->{'ordernumber'});
1232 return ($order,$ordnum->{'ordernumber'});
1235 sub getsingleorder {
1238 my $query="Select * from biblio,biblioitems,aqorders,aqorderbreakdown
1239 where aqorders.ordernumber='$ordnum'
1240 and biblio.biblionumber=aqorders.biblionumber and
1241 biblioitems.biblioitemnumber=aqorders.biblioitemnumber and
1242 aqorders.ordernumber=aqorderbreakdown.ordernumber";
1243 my $sth=$dbh->prepare($query);
1245 my $data=$sth->fetchrow_hashref;
1254 my $query="Select * from aqorders,biblio,biblioitems where
1255 booksellerinvoicenumber='$invoice'
1256 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber=
1257 aqorders.biblioitemnumber group by aqorders.ordernumber,aqorders.biblioitemnumber";
1260 my $sth=$dbh->prepare($query);
1262 while (my $data=$sth->fetchrow_hashref){
1268 return($i,@results);
1272 #gets all orders from a certain supplier, orders them alphabetically
1275 my $query="Select * from aqorders,biblio,biblioitems where booksellerid='$supid'
1276 and (cancelledby is NULL or cancelledby = '')
1277 and (quantityreceived < quantity or quantityreceived is NULL)
1278 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber=
1279 aqorders.biblioitemnumber
1280 group by aqorders.biblioitemnumber
1285 my $sth=$dbh->prepare($query);
1287 while (my $data=$sth->fetchrow_hashref){
1293 return($i,@results);
1297 #gets all orders from a certain supplier, orders them alphabetically
1300 my $query="Select * from aqorders,biblio,biblioitems where booksellerid='$supid'
1301 and (cancelledby is NULL or cancelledby = '')
1302 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber=
1303 aqorders.biblioitemnumber and
1304 aqorders.quantityreceived>0
1305 and aqorders.datereceived >=now()
1306 group by aqorders.biblioitemnumber
1311 my $sth=$dbh->prepare($query);
1313 while (my $data=$sth->fetchrow_hashref){
1319 return($i,@results);
1323 my ($search,$biblio,$catview) = @_;
1324 my $dbh = C4Connect;
1325 my $query = "Select *,biblio.title from aqorders,biblioitems,biblio
1326 where aqorders.biblioitemnumber = biblioitems.biblioitemnumber
1327 and biblio.biblionumber=aqorders.biblionumber
1328 and ((datecancellationprinted is NULL)
1329 or (datecancellationprinted = '0000-00-00'))
1331 my @data = split(' ',$search);
1333 for (my $i = 0; $i < $count; $i++) {
1334 $query .= "(biblio.title like '$data[$i]%' or biblio.title like '% $data[$i]%') and ";
1336 $query=~ s/ and $//;
1337 $query.=" ) or biblioitems.isbn='$search'
1338 or (aqorders.ordernumber='$search' and aqorders.biblionumber='$biblio')) ";
1339 if ($catview ne 'yes'){
1340 $query.=" and (quantityreceived < quantity or quantityreceived is NULL)";
1342 $query.=" group by aqorders.ordernumber";
1343 my $sth=$dbh->prepare($query);
1347 while (my $data=$sth->fetchrow_hashref){
1348 my $sth2=$dbh->prepare("Select * from biblio where
1349 biblionumber='$data->{'biblionumber'}'");
1351 my $data2=$sth2->fetchrow_hashref;
1353 $data->{'author'}=$data2->{'author'};
1354 $data->{'seriestitle'}=$data2->{'seriestitle'};
1355 $sth2=$dbh->prepare("Select * from aqorderbreakdown where
1356 ordernumber=$data->{'ordernumber'}");
1358 $data2=$sth2->fetchrow_hashref;
1360 $data->{'branchcode'}=$data2->{'branchcode'};
1361 $data->{'bookfundid'}=$data2->{'bookfundid'};
1367 return($i,@results);
1372 my ($searchstring)=@_;
1374 my $query="Select * from aqbooksellers where name like '%$searchstring%' or
1375 id = '$searchstring'";
1376 my $sth=$dbh->prepare($query);
1380 while (my $data=$sth->fetchrow_hashref){
1386 return($i,@results);
1392 my $query="Select * from aqorderbreakdown where ordernumber='$id'";
1393 my $sth=$dbh->prepare($query);
1397 while (my $data=$sth->fetchrow_hashref){
1403 return($i,\@results);
1407 my ($basketno,$supplier)=@_;
1409 my $query="Select *,biblio.title from aqorders,biblio,biblioitems
1410 where basketno='$basketno'
1411 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber
1412 =aqorders.biblioitemnumber
1413 and (datecancellationprinted is NULL or datecancellationprinted =
1415 if ($supplier ne ''){
1416 $query.=" and aqorders.booksellerid='$supplier'";
1418 $query.=" group by aqorders.ordernumber";
1419 my $sth=$dbh->prepare($query);
1424 while (my $data=$sth->fetchrow_hashref){
1430 return($i,@results);
1435 my $query="Select max(basketno) from aqorders";
1436 my $sth=$dbh->prepare($query);
1438 my $data=$sth->fetchrow_arrayref;
1439 my $basket=$$data[0];
1448 my $query="Select * from aqbookfund,aqbudget where aqbookfund.bookfundid
1449 =aqbudget.bookfundid
1450 group by aqbookfund.bookfundid order by bookfundname";
1451 my $sth=$dbh->prepare($query);
1455 while (my $data=$sth->fetchrow_hashref){
1461 return($i,@results);
1465 my $dbh = C4Connect;
1466 my $query = "Select * from branches";
1467 my $sth = $dbh->prepare($query);
1472 while (my $data = $sth->fetchrow_hashref) {
1473 $results[$i] = $data;
1479 return($i, @results);
1482 sub bookfundbreakdown {
1485 my $query="Select quantity,datereceived,freight,unitprice,listprice,ecost,quantityreceived,subscription
1486 from aqorders,aqorderbreakdown where bookfundid='$id' and
1487 aqorders.ordernumber=aqorderbreakdown.ordernumber
1488 and (datecancellationprinted is NULL or
1489 datecancellationprinted='0000-00-00')";
1490 my $sth=$dbh->prepare($query);
1494 while (my $data=$sth->fetchrow_hashref){
1495 if ($data->{'subscription'} == 1){
1496 $spent+=$data->{'quantity'}*$data->{'unitprice'};
1498 my $leftover=$data->{'quantity'}-$data->{'quantityreceived'};
1499 $comtd+=($data->{'ecost'})*$leftover;
1500 $spent+=($data->{'unitprice'})*$data->{'quantityreceived'};
1505 return($spent,$comtd);
1511 my $dbh = &C4Connect;
1512 my $query = "Select max(biblionumber) from biblio";
1513 my $sth = $dbh->prepare($query);
1515 my $data = $sth->fetchrow_arrayref;
1516 my $bibnum = $$data[0] + 1;
1519 $biblio->{'title'} = $dbh->quote($biblio->{'title'});
1520 $biblio->{'author'} = $dbh->quote($biblio->{'author'});
1521 $biblio->{'copyright'} = $dbh->quote($biblio->{'copyright'});
1522 $biblio->{'seriestitle'} = $dbh->quote($biblio->{'seriestitle'});
1523 $biblio->{'notes'} = $dbh->quote($biblio->{'notes'});
1524 $biblio->{'abstract'} = $dbh->quote($biblio->{'abstract'});
1525 if ($biblio->{'seriestitle'}) { $series = 1 };
1528 $query = "insert into biblio set
1529 biblionumber = $bibnum,
1530 title = $biblio->{'title'},
1531 author = $biblio->{'author'},
1532 copyrightdate = $biblio->{'copyright'},
1534 seriestitle = $biblio->{'seriestitle'},
1535 notes = $biblio->{'notes'},
1536 abstract = $biblio->{'abstract'}";
1538 $sth = $dbh->prepare($query);
1549 my $dbh = C4Connect;
1553 $biblio->{'title'} = $dbh->quote($biblio->{'title'});
1554 $biblio->{'author'} = $dbh->quote($biblio->{'author'});
1555 $biblio->{'abstract'} = $dbh->quote($biblio->{'abstract'});
1556 $biblio->{'copyrightdate'} = $dbh->quote($biblio->{'copyrightdate'});
1557 $biblio->{'seriestitle'} = $dbh->quote($biblio->{'serirestitle'});
1558 $biblio->{'serial'} = $dbh->quote($biblio->{'serial'});
1559 $biblio->{'unititle'} = $dbh->quote($biblio->{'unititle'});
1560 $biblio->{'notes'} = $dbh->quote($biblio->{'notes'});
1562 $query = "Update biblio set
1563 title = $biblio->{'title'},
1564 author = $biblio->{'author'},
1565 abstract = $biblio->{'abstract'},
1566 copyrightdate = $biblio->{'copyrightdate'},
1567 seriestitle = $biblio->{'seriestitle'},
1568 serial = $biblio->{'serial'},
1569 unititle = $biblio->{'unititle'},
1570 notes = $biblio->{'notes'}
1571 where biblionumber = $biblio->{'biblionumber'}";
1572 $sth = $dbh->prepare($query);
1578 return($biblio->{'biblionumber'});
1583 my ($bibnum, $subtitle) = @_;
1584 my $dbh = C4Connect;
1585 my $query = "update bibliosubtitle set
1586 subtitle = '$subtitle'
1587 where biblionumber = $bibnum";
1588 my $sth = $dbh->prepare($query);
1597 my ($bibnum, $author) = @_;
1598 my $dbh = C4Connect;
1599 my $query = "Delete from additionalauthors where biblionumber = $bibnum";
1600 my $sth = $dbh->prepare($query);
1605 if ($author ne '') {
1606 $query = "Insert into additionalauthors set
1608 biblionumber = '$bibnum'";
1609 $sth = $dbh->prepare($query);
1617 } # sub modaddauthor
1621 my ($bibnum, $force, @subject) = @_;
1622 my $dbh = C4Connect;
1623 my $count = @subject;
1625 for (my $i = 0; $i < $count; $i++) {
1626 $subject[$i] =~ s/^ //g;
1627 $subject[$i] =~ s/ $//g;
1628 my $query = "select * from catalogueentry
1629 where entrytype = 's'
1630 and catalogueentry = '$subject[$i]'";
1631 my $sth = $dbh->prepare($query);
1634 if (my $data = $sth->fetchrow_hashref) {
1636 if ($force eq $subject[$i]) {
1638 # subject not in aut, chosen to force anway
1639 # so insert into cataloguentry so its in auth file
1640 $query = "Insert into catalogueentry
1641 (entrytype,catalogueentry)
1642 values ('s','$subject[$i]')";
1643 my $sth2 = $dbh->prepare($query);
1650 $error = "$subject[$i]\n does not exist in the subject authority file";
1651 $query = "Select * from catalogueentry
1652 where entrytype = 's'
1653 and (catalogueentry like '$subject[$i] %'
1654 or catalogueentry like '% $subject[$i] %'
1655 or catalogueentry like '% $subject[$i]')";
1656 my $sth2 = $dbh->prepare($query);
1659 while (my $data = $sth2->fetchrow_hashref) {
1660 $error = $error."<br>$data->{'catalogueentry'}";
1669 my $query = "Delete from bibliosubject where biblionumber = $bibnum";
1670 my $sth = $dbh->prepare($query);
1675 for (my $i = 0; $i < $count; $i++) {
1676 $sth = $dbh->prepare("Insert into bibliosubject
1677 values ('$subject[$i]', $bibnum)");
1690 my ($biblioitem) = @_;
1691 my $dbh = C4Connect;
1694 $biblioitem->{'itemtype'} = $dbh->quote($biblioitem->{'itemtype'});
1695 $biblioitem->{'url'} = $dbh->quote($biblioitem->{'url'});
1696 $biblioitem->{'isbn'} = $dbh->quote($biblioitem->{'isbn'});
1697 $biblioitem->{'publishercode'} = $dbh->quote($biblioitem->{'publishercode'});
1698 $biblioitem->{'publicationyear'} = $dbh->quote($biblioitem->{'publicationyear'});
1699 $biblioitem->{'classification'} = $dbh->quote($biblioitem->{'classification'});
1700 $biblioitem->{'dewey'} = $dbh->quote($biblioitem->{'dewey'});
1701 $biblioitem->{'subclass'} = $dbh->quote($biblioitem->{'subclass'});
1702 $biblioitem->{'illus'} = $dbh->quote($biblioitem->{'illus'});
1703 $biblioitem->{'pages'} = $dbh->quote($biblioitem->{'pages'});
1704 $biblioitem->{'volumeddesc'} = $dbh->quote($biblioitem->{'volumeddesc'});
1705 $biblioitem->{'notes'} = $dbh->quote($biblioitem->{'notes'});
1706 $biblioitem->{'size'} = $dbh->quote($biblioitem->{'size'});
1707 $biblioitem->{'place'} = $dbh->quote($biblioitem->{'place'});
1709 $query = "Update biblioitems set
1710 itemtype = $biblioitem->{'itemtype'},
1711 url = $biblioitem->{'url'},
1712 isbn = $biblioitem->{'isbn'},
1713 publishercode = $biblioitem->{'publishercode'},
1714 publicationyear = $biblioitem->{'publicationyear'},
1715 classification = $biblioitem->{'classification'},
1716 dewey = $biblioitem->{'dewey'},
1717 subclass = $biblioitem->{'subclass'},
1718 illus = $biblioitem->{'illus'},
1719 pages = $biblioitem->{'pages'},
1720 volumeddesc = $biblioitem->{'volumeddesc'},
1721 notes = $biblioitem->{'notes'},
1722 size = $biblioitem->{'size'},
1723 place = $biblioitem->{'place'}
1724 where biblioitemnumber = $biblioitem->{'biblioitemnumber'}";
1733 my ($bibitemnum,$note)=@_;
1735 my $query="update biblioitems set notes='$note' where
1736 biblioitemnumber='$bibitemnum'";
1737 my $sth=$dbh->prepare($query);
1744 my ($biblioitem) = @_;
1745 my $dbh = C4Connect;
1746 my $query = "Select max(biblioitemnumber) from biblioitems";
1747 my $sth = $dbh->prepare($query);
1751 $biblioitem->{'volume'} = $dbh->quote($biblioitem->{'volume'});
1752 $biblioitem->{'number'} = $dbh->quote($biblioitem->{'number'});
1753 $biblioitem->{'classification'} = $dbh->quote($biblioitem->{'classification'});
1754 $biblioitem->{'itemtype'} = $dbh->quote($biblioitem->{'itemtype'});
1755 $biblioitem->{'url'} = $dbh->quote($biblioitem->{'url'});
1756 $biblioitem->{'isbn'} = $dbh->quote($biblioitem->{'isbn'});
1757 $biblioitem->{'issn'} = $dbh->quote($biblioitem->{'issn'});
1758 $biblioitem->{'dewey'} = $dbh->quote($biblioitem->{'dewey'});
1759 $biblioitem->{'subclass'} = $dbh->quote($biblioitem->{'subclass'});
1760 $biblioitem->{'publicationyear'} = $dbh->quote($biblioitem->{'publicationyear'});
1761 $biblioitem->{'publishercode'} = $dbh->quote($biblioitem->{'publishercode'});
1762 $biblioitem->{'volumedate'} = $dbh->quote($biblioitem->{'volumedate'});
1763 $biblioitem->{'volumeddesc'} = $dbh->quote($biblioitem->{'volumeddesc'}); $biblioitem->{'illus'} = $dbh->quote($biblioitem->{'illus'});
1764 $biblioitem->{'illus'} = $dbh->quote($biblioitem->{'illus'});
1765 $biblioitem->{'pages'} = $dbh->quote($biblioitem->{'pages'});
1766 $biblioitem->{'notes'} = $dbh->quote($biblioitem->{'notes'});
1767 $biblioitem->{'size'} = $dbh->quote($biblioitem->{'size'});
1768 $biblioitem->{'place'} = $dbh->quote($biblioitem->{'place'});
1769 $biblioitem->{'lccn'} = $dbh->quote($biblioitem->{'lccn'});
1770 $biblioitem->{'marc'} = $dbh->quote($biblioitem->{'marc'});
1773 $data = $sth->fetchrow_arrayref;
1774 $bibitemnum = $$data[0] + 1;
1778 $query = "insert into biblioitems set
1779 biblioitemnumber = $bibitemnum,
1780 biblionumber = $biblioitem->{'biblionumber'},
1781 volume = $biblioitem->{'volume'},
1782 number = $biblioitem->{'number'},
1783 classification = $biblioitem->{'classification'},
1784 itemtype = $biblioitem->{'itemtype'},
1785 url = $biblioitem->{'url'},
1786 isbn = $biblioitem->{'isbn'},
1787 issn = $biblioitem->{'issn'},
1788 dewey = $biblioitem->{'dewey'},
1789 subclass = $biblioitem->{'subclass'},
1790 publicationyear = $biblioitem->{'publicationyear'},
1791 publishercode = $biblioitem->{'publishercode'},
1792 volumedate = $biblioitem->{'volumedate'},
1793 volumeddesc = $biblioitem->{'volumeddesc'},
1794 illus = $biblioitem->{'illus'},
1795 pages = $biblioitem->{'pages'},
1796 notes = $biblioitem->{'notes'},
1797 size = $biblioitem->{'size'},
1798 lccn = $biblioitem->{'lccn'},
1799 marc = $biblioitem->{'marc'},
1800 place = $biblioitem->{'place'}";
1802 $sth = $dbh->prepare($query);
1807 return($bibitemnum);
1813 my $query="insert into bibliosubject (biblionumber) values
1815 my $sth=$dbh->prepare($query);
1823 my ($bibnum, $subtitle) = @_;
1824 my $dbh = C4Connect;
1825 $subtitle = $dbh->quote($subtitle);
1826 my $query = "insert into bibliosubtitle set
1827 biblionumber = $bibnum,
1828 subtitle = $subtitle";
1829 my $sth = $dbh->prepare($query);
1838 my ($bibnum,$title,$ordnum,$basket,$quantity,$listprice,$supplier,$who,$notes,$bookfund,$bibitemnum,$rrp,$ecost,$gst,$budget,$cost,$sub,$invoice)=@_;
1839 if ($budget eq 'now'){
1842 $budget="'2001-07-01'";
1850 my $query="insert into aqorders (biblionumber,title,basketno,
1851 quantity,listprice,booksellerid,entrydate,requisitionedby,authorisedby,notes,
1852 biblioitemnumber,rrp,ecost,gst,unitprice,subscription,booksellerinvoicenumber)
1855 ($bibnum,'$title',$basket,$quantity,$listprice,'$supplier',now(),
1856 '$who','$who','$notes',$bibitemnum,'$rrp','$ecost','$gst','$cost',
1857 '$sub','$invoice')";
1858 my $sth=$dbh->prepare($query);
1862 $query="select * from aqorders where
1863 biblionumber=$bibnum and basketno=$basket and ordernumber >=$ordnum";
1864 $sth=$dbh->prepare($query);
1866 my $data=$sth->fetchrow_hashref;
1868 $ordnum=$data->{'ordernumber'};
1869 $query="insert into aqorderbreakdown (ordernumber,bookfundid) values
1870 ($ordnum,'$bookfund')";
1871 $sth=$dbh->prepare($query);
1879 my ($bibnum,$ordnum)=@_;
1881 my $query="update aqorders set datecancellationprinted=now()
1882 where biblionumber='$bibnum' and
1883 ordernumber='$ordnum'";
1884 my $sth=$dbh->prepare($query);
1888 my $count=itemcount($bibnum);
1896 my ($title,$ordnum,$quantity,$listprice,$bibnum,$basketno,$supplier,$who,$notes,$bookfund,$bibitemnum,$rrp,$ecost,$gst,$budget,$cost,$invoice)=@_;
1898 my $query="update aqorders set title='$title',
1899 quantity='$quantity',listprice='$listprice',basketno='$basketno',
1900 rrp='$rrp',ecost='$ecost',unitprice='$cost',
1901 booksellerinvoicenumber='$invoice'
1903 ordernumber=$ordnum and biblionumber=$bibnum";
1904 my $sth=$dbh->prepare($query);
1908 $query="update aqorderbreakdown set bookfundid=$bookfund where
1909 ordernumber=$ordnum";
1910 $sth=$dbh->prepare($query);
1919 my $query="Select max(ordernumber) from aqorders";
1920 my $sth=$dbh->prepare($query);
1922 my $data=$sth->fetchrow_arrayref;
1923 my $ordnum=$$data[0];
1931 my ($biblio,$ordnum,$quantrec,$user,$cost,$invoiceno,$bibitemno,$freight,$bookfund,$rrp)=@_;
1933 my $query="update aqorders set quantityreceived='$quantrec',
1934 datereceived=now(),booksellerinvoicenumber='$invoiceno',
1935 biblioitemnumber=$bibitemno,unitprice='$cost',freight='$freight',
1937 where biblionumber=$biblio and ordernumber=$ordnum
1940 my $sth=$dbh->prepare($query);
1943 $query="update aqorderbreakdown set bookfundid=$bookfund where
1944 ordernumber=$ordnum";
1945 $sth=$dbh->prepare($query);
1952 my($biblio,$ordnum,$user,$cost,$bookfund,$rrp)=@_;
1954 my $query="update aqorders set
1955 unitprice='$cost', rrp='$rrp'
1956 where biblionumber=$biblio and ordernumber=$ordnum
1959 my $sth=$dbh->prepare($query);
1962 $query="update aqorderbreakdown set bookfundid=$bookfund where
1963 ordernumber=$ordnum";
1964 $sth=$dbh->prepare($query);
1972 my ($currency,$price)=@_;
1974 my $query="Select rate from currency where currency='$currency'";
1975 my $sth=$dbh->prepare($query);
1977 my $data=$sth->fetchrow_hashref;
1980 my $cur=$data->{'rate'};
1984 my $price=$price / $cur;
1990 my $query="Select * from currency";
1991 my $sth=$dbh->prepare($query);
1995 while (my $data=$sth->fetchrow_hashref){
2001 return($i,\@results);
2007 my $query="Select * from currency where currency='$cur'";
2008 my $sth=$dbh->prepare($query);
2011 my $data=$sth->fetchrow_hashref;
2017 sub updatecurrencies {
2018 my ($currency,$rate)=@_;
2020 my $query="update currency set rate=$rate where currency='$currency'";
2021 my $sth=$dbh->prepare($query);
2030 my $query="Update aqbooksellers set
2031 name='$data->{'name'}',address1='$data->{'address1'}',address2='$data->{'address2'}',
2032 address3='$data->{'address3'}',address4='$data->{'address4'}',postal='$data->{'postal'}',
2033 phone='$data->{'phone'}',fax='$data->{'fax'}',url='$data->{'url'}',
2034 contact='$data->{'contact'}',contpos='$data->{'contpos'}',
2035 contphone='$data->{'contphone'}', contfax='$data->{'contfax'}', contaltphone=
2036 '$data->{'contaltphone'}', contemail='$data->{'contemail'}', contnotes=
2037 '$data->{'contnotes'}', active=$data->{'active'},
2038 listprice='$data->{'listprice'}', invoiceprice='$data->{'invoiceprice'}',
2039 gstreg=$data->{'gstreg'}, listincgst=$data->{'listincgst'},
2040 invoiceincgst=$data->{'invoiceincgst'}, specialty='$data->{'specialty'}',
2041 discount='$data->{'discount'}',invoicedisc='$data->{'invoicedisc'}',
2042 nocalc='$data->{'nocalc'}'
2043 where id='$data->{'id'}'";
2044 my $sth=$dbh->prepare($query);
2054 my $sth=$dbh->prepare("Select max(id) from aqbooksellers");
2056 my $data2=$sth->fetchrow_hashref;
2058 $data2->{'max(id)'}++;
2059 $sth=$dbh->prepare("Insert into aqbooksellers (id) values ($data2->{'max(id)'})");
2062 $data->{'id'}=$data2->{'max(id)'};
2065 return($data->{'id'});
2070 my ($item, @barcodes) = @_;
2071 my $dbh = C4Connect;
2072 my $query = "Select max(itemnumber) from items";
2073 my $sth = $dbh->prepare($query);
2079 $data = $sth->fetchrow_hashref;
2080 $itemnumber = $data->{'max(itemnumber)'} + 1;
2083 $item->{'booksellerid'} = $dbh->quote($item->{'booksellerid'});
2084 $item->{'homebranch'} = $dbh->quote($item->{'homebranch'});
2085 $item->{'price'} = $dbh->quote($item->{'price'});
2086 $item->{'replacementprice'} = $dbh->quote($item->{'replacementprice'});
2087 $item->{'itemnotes'} = $dbh->quote($item->{'itemnotes'});
2089 foreach my $barcode (@barcodes) {
2090 $barcode = uc($barcode);
2091 $barcode = $dbh->quote($barcode);
2092 $query = "Insert into items set
2093 itemnumber = $itemnumber,
2094 biblionumber = $item->{'biblionumber'},
2095 biblioitemnumber = $item->{'biblioitemnumber'},
2097 booksellerid = $item->{'booksellerid'},
2098 dateaccessioned = NOW(),
2099 homebranch = $item->{'homebranch'},
2100 holdingbranch = $item->{'homebranch'},
2101 price = $item->{'price'},
2102 replacementprice = $item->{'replacementprice'},
2103 replacementpricedate = NOW(),
2104 itemnotes = $item->{'itemnotes'}";
2106 if ($item->{'loan'}) {
2108 notforloan = $item->{'loan'}";
2111 $sth = $dbh->prepare($query);
2114 $error .= $sth->errstr;
2125 my ($count,@barcodes)=@_;
2128 for (my $i=0;$i<$count;$i++){
2129 $barcodes[$i]=uc $barcodes[$i];
2130 my $query="Select * from items where barcode='$barcodes[$i]'";
2131 my $sth=$dbh->prepare($query);
2133 if (my $data=$sth->fetchrow_hashref){
2134 $error.=" Duplicate Barcode: $barcodes[$i]";
2143 my ($loan,$itemnum,$bibitemnum,$barcode,$notes,$homebranch,$lost,$wthdrawn,$replacement)=@_;
2145 my $query="update items set biblioitemnumber=$bibitemnum,
2146 barcode='$barcode',itemnotes='$notes'
2147 where itemnumber=$itemnum";
2148 if ($barcode eq ''){
2149 $query="update items set biblioitemnumber=$bibitemnum,notforloan=$loan where itemnumber=$itemnum";
2152 $query="update items set biblioitemnumber=$bibitemnum,
2153 barcode='$barcode',itemnotes='$notes',homebranch='$homebranch',
2154 itemlost='$lost',wthdrawn='$wthdrawn' where itemnumber=$itemnum";
2156 if ($replacement ne ''){
2157 $query=~ s/ where/,replacementprice='$replacement' where/;
2160 my $sth=$dbh->prepare($query);
2167 my($price,$rrp,$itemnum)=@_;
2169 my $query="update items set price='$price',replacementprice='$rrp'
2170 where itemnumber=$itemnum";
2171 my $sth=$dbh->prepare($query);
2177 my ($bibitemnum)=@_;
2179 my $query="Select count(*) from items where biblioitemnumber='$bibitemnum'";
2180 my $sth=$dbh->prepare($query);
2182 my $data=$sth->fetchrow_hashref;
2185 return($data->{'count(*)'});
2189 my ($biblionumber)=@_;
2191 my $query="Select * from biblioitems,items,itemtypes where
2192 biblioitems.biblionumber=$biblionumber
2193 and biblioitems.biblioitemnumber=items.biblioitemnumber and
2194 itemtypes.itemtype=biblioitems.itemtype
2195 order by items.biblioitemnumber";
2196 my $sth=$dbh->prepare($query);
2200 while (my $data=$sth->fetchrow_hashref){
2210 my ($bibitemnum,$itemtype)=@_;
2212 my $query="Select * from biblioitems where biblioitemnumber=$bibitemnum
2213 and itemtype='$itemtype'";
2214 my $sth=$dbh->prepare($query);
2217 if (my $data=$sth->fetchrow_hashref){
2228 my $query="select * from items where itemnumber=$itemnum";
2229 my $sth=$dbh->prepare($query);
2231 my @data=$sth->fetchrow_array;
2233 $query="Insert into deleteditems values (";
2234 foreach my $temp (@data){
2235 $query=$query."'$temp',";
2239 $sth=$dbh->prepare($query);
2242 $query = "Delete from items where itemnumber=$itemnum";
2243 $sth=$dbh->prepare($query);
2248 sub deletebiblioitem {
2249 my ($biblioitemnumber) = @_;
2250 my $dbh = C4Connect;
2251 my $query = "Select * from biblioitems
2252 where biblioitemnumber = $biblioitemnumber";
2253 my $sth = $dbh->prepare($query);
2258 if (@results = $sth->fetchrow_array) {
2260 $query = "Insert into deletedbiblioitems values (";
2261 foreach my $value (@results) {
2262 $value = $dbh->quote($value);
2263 $query .= "$value,";
2266 $query =~ s/\,$/\)/;
2269 $query = "Delete from biblioitems
2270 where biblioitemnumber = $biblioitemnumber";
2276 # Now delete all the items attached to the biblioitem
2278 $query = "Select * from items where biblioitemnumber = $biblioitemnumber";
2279 $sth = $dbh->prepare($query);
2283 while (@results = $sth->fetchrow_array) {
2285 $query = "Insert into deleteditems values (";
2286 foreach my $value (@results) {
2287 $value = $dbh->quote($value);
2288 $query .= "$value,";
2291 $query =~ s/\,$/\)/;
2297 $query = "Delete from items where biblioitemnumber = $biblioitemnumber";
2301 } # sub deletebiblioitem
2307 my $query="select * from biblio where biblionumber=$biblio";
2308 my $sth=$dbh->prepare($query);
2310 if (my @data=$sth->fetchrow_array){
2312 $query="Insert into deletedbiblio values (";
2313 foreach my $temp (@data){
2314 $temp=~ s/\'/\\\'/g;
2315 $query=$query."'$temp',";
2319 $sth=$dbh->prepare($query);
2322 $query = "Delete from biblio where biblionumber=$biblio";
2323 $sth=$dbh->prepare($query);
2334 my $dbh = C4Connect;
2335 my $query = "select * from itemtypes";
2336 my $sth = $dbh->prepare($query);
2337 # || die "Cannot prepare $query" . $dbh->errstr;
2342 # || die "Cannot execute $query\n" . $sth->errstr;
2343 while (my $data = $sth->fetchrow_hashref) {
2344 @results[$count] = $data;
2350 return($count, @results);
2351 } # sub getitemtypes
2355 my ($biblionumber) = @_;
2356 my $dbh = C4Connect;
2357 my $query = "Select * from biblio where biblionumber = $biblionumber";
2358 my $sth = $dbh->prepare($query);
2359 # || die "Cannot prepare $query\n" . $dbh->errstr;
2364 # || die "Cannot execute $query\n" . $sth->errstr;
2365 while (my $data = $sth->fetchrow_hashref) {
2366 $results[$count] = $data;
2372 return($count, @results);
2377 my ($biblioitemnum) = @_;
2378 my $dbh = C4Connect;
2379 my $query = "Select * from biblioitems where
2380 biblioitemnumber = $biblioitemnum";
2381 my $sth = $dbh->prepare($query);
2387 while (my $data = $sth->fetchrow_hashref) {
2388 $results[$count] = $data;
2394 return($count, @results);
2395 } # sub getbiblioitem
2398 sub getbiblioitembybiblionumber {
2399 my ($biblionumber) = @_;
2400 my $dbh = C4Connect;
2401 my $query = "Select * from biblioitems where biblionumber =
2403 my $sth = $dbh->prepare($query);
2409 while (my $data = $sth->fetchrow_hashref) {
2410 $results[$count] = $data;
2416 return($count, @results);
2420 sub getitemsbybiblioitem {
2421 my ($biblioitemnum) = @_;
2422 my $dbh = C4Connect;
2423 my $query = "Select * from items, biblio where
2424 biblio.biblionumber = items.biblionumber and biblioitemnumber
2426 my $sth = $dbh->prepare($query);
2427 # || die "Cannot prepare $query\n" . $dbh->errstr;
2432 # || die "Cannot execute $query\n" . $sth->errstr;
2433 while (my $data = $sth->fetchrow_hashref) {
2434 $results[$count] = $data;
2440 return($count, @results);
2441 } # sub getitemsbybiblioitem
2446 my $dbh = C4Connect;
2452 $isbn = $dbh->quote($isbn);
2453 $query = "Select biblio.* from biblio, biblioitems where
2454 biblio.biblionumber = biblioitems.biblionumber
2456 $sth = $dbh->prepare($query);
2459 while (my $data = $sth->fetchrow_hashref) {
2460 $results[$count] = $data;
2466 return($count, @results);
2471 my ($keywordlist) = @_;
2472 my $dbh = C4Connect;
2473 my $query = "Select distinct biblio.* from biblio, biblioitems where
2474 biblio.biblionumber = biblioitems.biblionumber and (";
2478 my @keywords = split(/ +/, $keywordlist);
2479 my $keyword = shift(@keywords);
2481 $keyword =~ s/%/\\%/g;
2482 $keyword =~ s/_/\\_/;
2483 $keyword = "%" . $keyword . "%";
2484 $keyword = $dbh->quote($keyword);
2485 $query .= " (url like $keyword)";
2487 foreach $keyword (@keywords) {
2488 $keyword =~ s/%/\\%/;
2489 $keyword =~ s/_/\\_/;
2490 $keyword = "%" . $keyword . "%";
2491 $keyword = $dbh->quote($keyword);
2492 $query .= " or (url like $keyword)";
2496 $sth = $dbh->prepare($query);
2499 while (my $data = $sth->fetchrow_hashref) {
2500 $results[$count] = $data;
2506 return($count, @results);
2507 } # sub websitesearch
2512 my $dbh = C4Connect;
2515 $website->{'biblionumber'} = $dbh->quote($website->{'biblionumber'});
2516 $website->{'title'} = $dbh->quote($website->{'title'});
2517 $website->{'description'} = $dbh->quote($website->{'description'});
2518 $website->{'url'} = $dbh->quote($website->{'url'});
2520 $query = "Insert into websites set
2521 biblionumber = $website->{'biblionumber'},
2522 title = $website->{'title'},
2523 description = $website->{'description'},
2524 url = $website->{'url'}";
2534 my $dbh = C4Connect;
2537 $website->{'title'} = $dbh->quote($website->{'title'});
2538 $website->{'description'} = $dbh->quote($website->{'description'});
2539 $website->{'url'} = $dbh->quote($website->{'url'});
2541 $query = "Update websites set
2542 title = $website->{'title'},
2543 description = $website->{'description'},
2544 url = $website->{'url'}
2545 where websitenumber = $website->{'websitenumber'}";
2550 } # sub updatewebsite
2554 my ($websitenumber) = @_;
2555 my $dbh = C4Connect;
2556 my $query = "Delete from websites where websitenumber = $websitenumber";
2561 } # sub deletewebsite
2563 END { } # module clean-up code here (global destructor)