4 # Revision 1.9 2002/09/20 12:57:46 tipaul
5 # long is the road to 1.4.0
6 # * MARCadditem and MARCmoditem now wroks
7 # * various bugfixes in MARC management
8 # !!! 1.3.0 should be released very soon now. Be careful !!!
10 # Revision 1.8 2002/09/10 13:53:52 tipaul
11 # MARC API continued...
13 # * multiple item management : MARCadditem and MARCmoditem have been added. They suppose that ALL the MARC field linked to koha-item are in the same MARC tag (on the same line of MARC file)
15 # Note : it should not be hard for marcimport and marcexport to re-link fields from internal tag/subfield to "legal" tag/subfield.
17 # Revision 1.7 2002/08/14 18:12:51 tonnesen
18 # Added copyright statement to all .pl and .pm files
20 # Revision 1.6 2002/07/25 13:40:31 tipaul
21 # pod documenting the API.
23 # Revision 1.5 2002/07/24 16:11:37 tipaul
25 # Database.pm and Output.pm are almost not modified (var test...)
27 # Biblio.pm is almost completly rewritten.
29 # WHAT DOES IT ??? ==> END of Hitchcock suspens
31 # 1st, it does... nothing...
32 # Every old API should be there. So if MARC-stuff is not done, the behaviour is EXACTLY the same (if there is no added bug, of course). So, if you use normal acquisition, you won't find anything new neither on screen or old-DB tables ...
34 # All old-API functions have been cloned. for example, the "newbiblio" sub, now has become :
35 # * a "newbiblio" sub, with the same parameters. It just call a sub named OLDnewbiblio
36 # * a "OLDnewbiblio" sub, which is a copy/paste of the previous newbiblio sub. Then, when you want to add the MARC-DB stuff, you can modify the newbiblio sub without modifying the OLDnewbiblio one. If we correct a bug in 1.2 in newbiblio, we can do the same in main branch by correcting OLDnewbiblio.
37 # * The MARC stuff is usually done through a sub named MARCxxx where xxx is the same as OLDxxx. For example, newbiblio calls MARCnewbiblio. the MARCxxx subs use a MARC::Record as parameter.
38 # The last thing to solve was to manage biblios through real MARC import : they must populate the old-db, but must populate the MARC-DB too, without loosing information (if we go from MARC::Record to old-data then back to MARC::Record, we loose A LOT OF ROWS). To do this, there are subs beginning by "ALLxxx" : they manage datas with MARC::Record datas. they call OLDxxx sub too (to populate old-DB), but MARCxxx subs too, with a complete MARC::Record ;-)
40 # In Biblio.pm, there are some subs that permits to build a old-style record from a MARC::Record, and the opposite. There is also a sub finding a MARC-bibid from a old-biblionumber and the opposite too.
41 # Note we have decided with steve that a old-biblio <=> a MARC-Biblio.
45 # move from 1.2 to 1.4 version :
46 # 1.2 and previous version uses a specific API to manage biblios. This API uses old-DB style parameters.
47 # In the 1.4 version, we want to do 2 differents things :
48 # - keep populating the old-DB, that has a LOT less datas than MARC
49 # - populate the MARC-DB
50 # To populate the DBs we have 2 differents sources :
51 # - the standard acquisition system (through book sellers), that does'nt use MARC data
52 # - the MARC acquisition system, that uses MARC data.
54 # thus, we have 2 differents cases :
55 # - with the standard acquisition system, we have non MARC data and want to populate old-DB and MARC-DB, knowing it's an incomplete MARC-record
56 # - with the MARC acquisition system, we have MARC datas, and want to loose nothing in MARC-DB. So, we can't store datas in old-DB, then copy in MARC-DB.
57 # we MUST have an API for true MARC data, that populate MARC-DB then old-DB
59 # That's why we need 4 subs :
60 # all subs beginning by MARC manage only MARC tables. They manage MARC-DB with MARC::Record parameters
61 # all subs beginning by OLD manage only OLD-DB tables. They manage old-DB with old-DB parameters
62 # all subs beginning by ALL manage both OLD-DB and MARC tables. They use MARC::Record as parameters. it's the API that MUST be used in MARC acquisition system
63 # all subs beginning by seomething else are the old-style API. They use old-DB as parameter, then call internally the OLD and MARC subs.
65 # only ALL and old-style API should be used in koha. MARC and OLD is used internally only
67 # Thus, we assume a nice translation to future versions : if we want in a 1.6 release completly forget old-DB, we can do it easily.
68 # in 1.4 version, the translations will be nicer, as we have NOTHING to do in code. Everything has to be done in Biblio.pm ;-)
72 # Copyright 2000-2002 Katipo Communications
74 # This file is part of Koha.
76 # Koha is free software; you can redistribute it and/or modify it under the
77 # terms of the GNU General Public License as published by the Free Software
78 # Foundation; either version 2 of the License, or (at your option) any later
81 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
82 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
83 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
85 # You should have received a copy of the GNU General Public License along with
86 # Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
87 # Suite 330, Boston, MA 02111-1307 USA
94 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
96 # set the version for version checking
101 # don't forget MARCxxx subs are here only for testing purposes. Should not be used
102 # as the old-style API and the ALL one are the only public functions.
105 &updateBiblio &updateBiblioItem &updateItem
106 &itemcount &newbiblio &newbiblioitem
107 &modnote &newsubject &newsubtitle
108 &modbiblio &checkitems
109 &newitems &modbibitem
110 &modsubtitle &modsubject &modaddauthor &moditem &countitems
111 &delitem &deletebiblioitem &delbiblio
112 &getitemtypes &getbiblio
113 &getbiblioitembybiblionumber
114 &getbiblioitem &getitemsbybiblioitem &isbnsearch
116 &newcompletebiblioitem
118 &ALLnewbiblio &ALLnewitem
121 &MARCaddbiblio &MARCadditem
122 &MARCmodsubfield &MARCaddsubfield
123 &MARCmodbiblio &MARCmoditem
125 &MARCkoha2marcBiblio &MARCmarc2koha &MARCkoha2marcItem
126 &MARCgetbiblio &MARCgetitem
127 &MARCaddword &MARCdelword
129 %EXPORT_TAGS = ( ); # eg: TAG => [ qw!name1 name2! ],
131 # your exported package globals go here,
132 # as well as any optionally exported functions
134 @EXPORT_OK = qw($Var1 %Hashit);
137 # non-exported package globals go here
138 use vars qw(@more $stuff);
140 # initalize package globals, first exported ones
146 # then the others (which are still accessible as $Some::Module::stuff)
150 # all file-scoped lexicals must be created before
151 # the functions below that use them.
153 # file-private lexicals go here
155 my %secret_hash = ();
157 # here's a file-private function as a closure,
158 # callable as &$priv_func; it cannot be prototyped.
159 my $priv_func = sub {
163 # make all your functions, whether exported or not;
167 # MARC MARC MARC MARC MARC MARC MARC MARC MARC MARC MARC MARC MARC MARC MARC MARC MARC MARC MARC
170 # all the following subs takes a MARC::Record as parameter and manage
171 # the MARC-DB. They are called by the 1.0/1.2 xxx subs, and by the
172 # ALLxxx subs (xxx deals with old-DB parameters, the ALLxxx deals with MARC-DB parameter)
176 all subs requires/use $dbh as 1st parameter.
177 NOTE : all those subs are private and must be used only inside Biblio.pm (called by a old API sub, or the ALLsub)
181 =head2 @tagslib = &MARCgettagslib($dbh,1|0);
182 last param is 1 for liblibrarian and 0 for libopac
183 returns a hash with tag/subfield meaning
185 =head2 ($tagfield,$tagsubfield) = &MARCfindmarc_from_kohafield($dbh,$kohafield);
186 finds MARC tag and subfield for a given kohafield
187 kohafield is "table.field" where table= biblio|biblioitems|items, and field a field of the previous table
189 =head2 $biblionumber = &MARCfind_oldbiblionumber_from_MARCbibid($dbh,$MARCbibi);
190 finds a old-db biblio number for a given MARCbibid number
192 =head2 $bibid = &MARCfind_MARCbibid_from_oldbiblionumber($dbh,$oldbiblionumber);
193 finds a MARC bibid from a old-db biblionumber
195 =head2 &MARCaddbiblio($dbh,$MARC::Record,$biblionumber);
196 creates a biblio (in the MARC tables only). $biblionumber is the old-db biblionumber of the biblio
198 =head2 &MARCaddsubfield($dbh,$bibid,$tagid,$indicator,$tagorder,$subfieldcode,$subfieldorder,$subfieldvalue);
199 adds a subfield in a biblio (in the MARC tables only).
201 =head2 $MARCRecord = &MARCgetbiblio($dbh,$bibid);
202 Returns a MARC::Record for the biblio $bibid.
204 =head2 &MARCmodbiblio($dbh,$bibid,$delete,$record);
205 MARCmodbiblio changes a biblio for a biblio,MARC::Record passed as parameter
206 if $delete == 1, every field/subfield not found is deleted in the biblio
207 otherwise, only data passed to MARCmodbiblio is managed.
208 thus, you can change only a small part of a biblio (like an item, or a subtitle, or a additionalauthor...)
210 =head2 ($subfieldid,$subfieldvalue) = &MARCmodsubfield($dbh,$subfieldid,$subfieldvalue);
211 MARCmodsubfield changes the value of a given subfield
213 =head2 $subfieldid = &MARCfindsubfield($dbh,$bibid,$tag,$subfieldcode,$subfieldorder,$subfieldvalue);
214 MARCfindsubfield returns a subfield number given a bibid/tag/subfieldvalue values.
215 Returns -1 if more than 1 answer
217 =head2 $subfieldid = &MARCfindsubfieldid($dbh,$bibid,$tag,$tagorder,$subfield,$subfieldorder);
218 MARCfindsubfieldid find a subfieldid for a bibid/tag/tagorder/subfield/subfieldorder
220 =head2 &MARCdelsubfield($dbh,$bibid,$tag,$tagorder,$subfield,$subfieldorder);
221 MARCdelsubfield delete a subfield for a bibid/tag/tagorder/subfield/subfieldorder
223 =head2 &MARCdelbiblio($dbh,$bibid);
224 MARCdelbiblio delete biblio $bibid
226 =head2 $MARCRecord = &MARCkoha2marcBiblio($dbh,$biblionumber,biblioitemnumber);
227 MARCkoha2marcBiblio is a wrapper between old-DB and MARC-DB. It returns a MARC::Record builded with old-DB biblio/biblioitem
229 =head2 $MARCRecord = &MARCkoha2marcItem($dbh,$biblionumber,itemnumber);
230 MARCkoha2marcItem is a wrapper between old-DB and MARC-DB. It returns a MARC::Record builded with old-DB item
232 =head2 $MARCRecord = &MARCkoha2marcSubtitle($dbh,$biblionumber,$subtitle);
233 MARCkoha2marcSubtitle is a wrapper between old-DB and MARC-DB. It returns a MARC::Record builded with old-DB subtitle
235 =head2 &MARCkoha2marcOnefield => used by MARCkoha2marc and should not be useful elsewhere
237 =head2 $olddb = &MARCmarc2koha($dbh,$MARCRecord);
238 builds a hash with old-db datas from a MARC::Record
240 =head2 &MARCmarc2kohaOnefield => used by MARCmarc2koha and should not be useful elsewhere
242 =head2 MARCaddword => used to manage MARC_word table and should not be useful elsewhere
244 =head2 MARCdelword => used to manage MARC_word table and should not be useful elsewhere
248 Paul POULAIN paul.poulain@free.fr
253 my ($dbh,$forlibrarian)= @_;
255 if ($forlibrarian eq 1) {
256 $sth=$dbh->prepare("select tagfield,tagsubfield,liblibrarian as lib from marc_subfield_structure");
258 $sth=$dbh->prepare("select tagfield,tagsubfield,libopac as lib from marc_subfield_structure");
265 while ( ($tag,$subfield,$lib) = $sth->fetchrow) {
266 $res->{$tag}->{$subfield}=$lib;
271 sub MARCfind_marc_from_kohafield {
272 my ($dbh,$kohafield) = @_;
273 my $sth=$dbh->prepare("select tagfield,tagsubfield from marc_subfield_structure where kohafield=?");
274 $sth->execute($kohafield);
275 my ($tagfield,$tagsubfield) = $sth->fetchrow;
276 return ($tagfield,$tagsubfield);
279 sub MARCfind_oldbiblionumber_from_MARCbibid {
280 my ($dbh,$MARCbibid) = @_;
281 my $sth=$dbh->prepare("select biblionumber from marc_biblio where bibid=?");
282 $sth->execute($MARCbibid);
283 my ($biblionumber) = $sth->fetchrow;
284 return $biblionumber;
287 sub MARCfind_MARCbibid_from_oldbiblionumber {
288 my ($dbh,$oldbiblionumber) = @_;
289 my $sth=$dbh->prepare("select bibid from marc_biblio where biblionumber=?");
290 $sth->execute($oldbiblionumber);
291 my ($bibid) = $sth->fetchrow;
296 # pass the MARC::Record to this function, and it will create the records in the marc tables
297 my ($dbh,$record,$biblionumber) = @_;
298 my @fields=$record->fields();
300 # adding main table, and retrieving bibid
301 $dbh->do("lock tables marc_biblio WRITE,marc_subfield_table WRITE, marc_word WRITE, marc_blob_subfield WRITE, stopwords READ");
302 my $sth=$dbh->prepare("insert into marc_biblio (datecreated,biblionumber) values (now(),?)");
303 $sth->execute($biblionumber);
304 $sth=$dbh->prepare("select max(bibid) from marc_biblio");
306 ($bibid)=$sth->fetchrow;
309 # now, add subfields...
310 foreach my $field (@fields) {
311 my @subfields=$field->subfields();
313 foreach my $subfieldcount (0..$#subfields) {
314 &MARCaddsubfield($dbh,$bibid,
316 $field->indicator(1).$field->indicator(2),
318 $subfields[$subfieldcount][0],
320 $subfields[$subfieldcount][1]
324 $dbh->do("unlock tables");
329 # pass the MARC::Record to this function, and it will create the records in the marc tables
330 my ($dbh,$record,$biblionumber) = @_;
331 # search for MARC biblionumber
332 $dbh->do("lock tables marc_biblio WRITE,marc_subfield_table WRITE, marc_word WRITE, marc_blob_subfield WRITE, stopwords READ");
333 my $bibid = &MARCfind_MARCbibid_from_oldbiblionumber($dbh,$biblionumber);
334 my @fields=$record->fields();
335 my $sth = $dbh->prepare("select max(tagorder) from marc_subfield_table where bibid=?");
336 $sth->execute($bibid);
337 my ($fieldcount) = $sth->fetchrow;
338 # now, add subfields...
339 foreach my $field (@fields) {
340 my @subfields=$field->subfields();
342 foreach my $subfieldcount (0..$#subfields) {
343 &MARCaddsubfield($dbh,$bibid,
345 $field->indicator(1).$field->indicator(2),
347 $subfields[$subfieldcount][0],
349 $subfields[$subfieldcount][1]
353 $dbh->do("unlock tables");
357 sub MARCaddsubfield {
358 # Add a new subfield to a tag into the DB.
359 my ($dbh,$bibid,$tagid,$indicator,$tagorder,$subfieldcode,$subfieldorder,$subfieldvalue) = @_;
360 # if not value, end of job, we do nothing
361 if (not($subfieldvalue)) {
364 if (not($subfieldcode)) {
367 if (length($subfieldvalue)>255) {
368 # $dbh->do("lock tables marc_blob_subfield WRITE, marc_subfield_table WRITE");
369 my $sth=$dbh->prepare("insert into marc_blob_subfield (subfieldvalue) values (?)");
370 $sth->execute($subfieldvalue);
371 $sth=$dbh->prepare("select max(blobidlink)from marc_blob_subfield");
373 my ($res)=$sth->fetchrow;
374 $sth=$dbh->prepare("insert into marc_subfield_table (bibid,tag,tagorder,subfieldcode,subfieldorder,valuebloblink) values (?,?,?,?,?,?)");
376 $sth->execute($bibid,'0'.$tagid,$tagorder,$subfieldcode,$subfieldorder,$res);
378 $sth->execute($bibid,$tagid,$tagorder,$subfieldcode,$subfieldorder,$res);
381 print STDERR "ERROR ==> insert into marc_subfield_table (bibid,tag,tagorder,subfieldcode,subfieldorder,subfieldvalue) values ($bibid,$tagid,$tagorder,$subfieldcode,$subfieldorder,$subfieldvalue)\n";
383 # $dbh->do("unlock tables");
385 my $sth=$dbh->prepare("insert into marc_subfield_table (bibid,tag,tagorder,subfieldcode,subfieldorder,subfieldvalue) values (?,?,?,?,?,?)");
386 $sth->execute($bibid,$tagid,$tagorder,$subfieldcode,$subfieldorder,$subfieldvalue);
388 print STDERR "ERROR ==> insert into marc_subfield_table (bibid,tag,tagorder,subfieldcode,subfieldorder,subfieldvalue) values ($bibid,$tagid,$tagorder,$subfieldcode,$subfieldorder,$subfieldvalue)\n";
391 &MARCaddword($dbh,$bibid,$tagid,$tagorder,$subfieldcode,$subfieldorder,$subfieldvalue);
396 # Returns MARC::Record of the biblio passed in parameter.
398 my $record = MARC::Record->new();
399 #---- TODO : the leader is missing
400 my $sth=$dbh->prepare("select bibid,subfieldid,tag,tagorder,tag_indicator,subfieldcode,subfieldorder,subfieldvalue,valuebloblink
401 from marc_subfield_table
402 where bibid=? order by tagorder,subfieldorder
404 my $sth2=$dbh->prepare("select subfieldvalue from marc_blob_subfield where blobidlink=?");
405 $sth->execute($bibid);
406 while (my $row=$sth->fetchrow_hashref) {
407 if ($row->{'valuebloblink'}) { #---- search blob if there is one
408 $sth2->execute($row->{'valuebloblink'});
409 my $row2=$sth2->fetchrow_hashref;
411 $row->{'subfieldvalue'}=$row2->{'subfieldvalue'};
413 if ($record->field($row->{'tag'})) {
415 #--- this test must stay as this, because of strange behaviour of mySQL/Perl DBI with char var containing a number...
416 #--- sometimes, eliminates 0 at beginning, sometimes no ;-\\\
417 if (length($row->{'tag'}) <3) {
418 $row->{'tag'} = "0".$row->{'tag'};
420 $field =$record->field($row->{'tag'});
422 my $x = $field->add_subfields($row->{'subfieldcode'},$row->{'subfieldvalue'});
423 $record->delete_field($field);
424 $record->add_fields($field);
427 if (length($row->{'tag'}) < 3) {
428 $row->{'tag'} = "0".$row->{'tag'};
430 my $temp = MARC::Field->new($row->{'tag'}," "," ", $row->{'subfieldcode'} => $row->{'subfieldvalue'});
431 $record->add_fields($temp);
438 # Returns MARC::Record of the biblio passed in parameter.
439 my ($dbh,$bibid,$itemnumber)=@_;
440 warn "MARCgetitem : $bibid, $itemnumber\n";
441 my $record = MARC::Record->new();
442 # search MARC tagorder
443 my $sth2 = $dbh->prepare("select tagorder from marc_subfield_table,marc_subfield_structure where marc_subfield_table.tag=marc_subfield_structure.tagfield and marc_subfield_table.subfieldcode=marc_subfield_structure.tagsubfield and bibid=? and kohafield='items.itemnumber' and subfieldvalue=?");
444 $sth2->execute($bibid,$itemnumber);
445 my ($tagorder) = $sth2->fetchrow_array();
446 #---- TODO : the leader is missing
447 my $sth=$dbh->prepare("select bibid,subfieldid,tag,tagorder,tag_indicator,subfieldcode,subfieldorder,subfieldvalue,valuebloblink
448 from marc_subfield_table
449 where bibid=? and tagorder=? order by subfieldorder
451 my $sth2=$dbh->prepare("select subfieldvalue from marc_blob_subfield where blobidlink=?");
452 $sth->execute($bibid,$tagorder);
453 while (my $row=$sth->fetchrow_hashref) {
454 if ($row->{'valuebloblink'}) { #---- search blob if there is one
455 $sth2->execute($row->{'valuebloblink'});
456 my $row2=$sth2->fetchrow_hashref;
458 $row->{'subfieldvalue'}=$row2->{'subfieldvalue'};
460 if ($record->field($row->{'tag'})) {
462 #--- this test must stay as this, because of strange behaviour of mySQL/Perl DBI with char var containing a number...
463 #--- sometimes, eliminates 0 at beginning, sometimes no ;-\\\
464 if (length($row->{'tag'}) <3) {
465 $row->{'tag'} = "0".$row->{'tag'};
467 $field =$record->field($row->{'tag'});
469 my $x = $field->add_subfields($row->{'subfieldcode'},$row->{'subfieldvalue'});
470 $record->delete_field($field);
471 $record->add_fields($field);
474 if (length($row->{'tag'}) < 3) {
475 $row->{'tag'} = "0".$row->{'tag'};
477 my $temp = MARC::Field->new($row->{'tag'}," "," ", $row->{'subfieldcode'} => $row->{'subfieldvalue'});
478 $record->add_fields($temp);
486 my ($dbh,$record,$bibid,$itemnumber,$delete)=@_;
487 my $oldrecord=&MARCgetbiblio($dbh,$bibid);
488 # if nothing to change, don't waste time...
489 if ($oldrecord eq $record) {
492 # otherwise, skip through each subfield...
493 my @fields = $record->fields();
495 foreach my $field (@fields) {
496 my $oldfield = $oldrecord->field($field->tag());
497 my @subfields=$field->subfields();
500 foreach my $subfield (@subfields) {
502 if ($oldfield eq 0 or (! $oldfield->subfield(@$subfield[0])) ) {
503 # just adding datas...
504 &MARCaddsubfield($dbh,$bibid,$field->tag(),$field->indicator(1).$field->indicator(2),
505 1,@$subfield[0],$subfieldorder,@$subfield[1]);
507 # modify he subfield if it's a different string
508 if ($oldfield->subfield(@$subfield[0]) ne @$subfield[1] ) {
509 my $subfieldid=&MARCfindsubfieldid($dbh,$bibid,$field->tag(),$tagorder,@$subfield[0],$subfieldorder);
510 &MARCmodsubfield($dbh,$subfieldid,@$subfield[1]);
518 my ($dbh,$record,$bibid,$itemnumber,$delete)=@_;
519 my $oldrecord=&MARCgetitem($dbh,$bibid,$itemnumber);
520 # if nothing to change, don't waste time...
521 if ($oldrecord eq $record) {
524 # otherwise, skip through each subfield...
525 my @fields = $record->fields();
526 # search old MARC item
527 my $sth2 = $dbh->prepare("select tagorder from marc_subfield_table,marc_subfield_structure where marc_subfield_table.tag=marc_subfield_structure.tagfield and marc_subfield_table.subfieldcode=marc_subfield_structure.tagsubfield and bibid=? and kohafield='items.itemnumber' and subfieldvalue=?");
528 $sth2->execute($bibid,$itemnumber);
529 my ($tagorder) = $sth2->fetchrow_array();
530 foreach my $field (@fields) {
531 my $oldfield = $oldrecord->field($field->tag());
532 my @subfields=$field->subfields();
534 foreach my $subfield (@subfields) {
536 if ($oldfield eq 0 or (! $oldfield->subfield(@$subfield[0])) ) {
537 # just adding datas...
538 warn "ADD = $bibid,".$field->tag().",".$field->indicator(1).".".$field->indicator(2).", $tagorder,".@$subfield[0].",$subfieldorder,@$subfield[1])\n";
539 &MARCaddsubfield($dbh,$bibid,$field->tag(),$field->indicator(1).$field->indicator(2),
540 $tagorder,@$subfield[0],$subfieldorder,@$subfield[1]);
542 # modify he subfield if it's a different string
543 warn "MODIFY = $bibid,".$field->tag().",".$field->indicator(1).".".$field->indicator(2).", $tagorder,".@$subfield[0].",$subfieldorder,@$subfield[1])\n";
544 if ($oldfield->subfield(@$subfield[0]) ne @$subfield[1] ) {
545 my $subfieldid=&MARCfindsubfieldid($dbh,$bibid,$field->tag(),$tagorder,@$subfield[0],$subfieldorder);
546 warn "MODIFY2 = $bibid, $subfieldid, ".@$subfield[1]."\n";
547 &MARCmodsubfield($dbh,$subfieldid,@$subfield[1]);
556 sub MARCmodsubfield {
557 # Subroutine changes a subfield value given a subfieldid.
558 my ($dbh, $subfieldid, $subfieldvalue )=@_;
559 $dbh->do("lock tables marc_blob_subfield WRITE,marc_subfield_table WRITE");
560 my $sth1=$dbh->prepare("select valuebloblink from marc_subfield_table where subfieldid=?");
561 $sth1->execute($subfieldid);
562 my ($oldvaluebloblink)=$sth1->fetchrow;
565 # if too long, use a bloblink
566 if (length($subfieldvalue)>255 ) {
567 # if already a bloblink, update it, otherwise, insert a new one.
568 if ($oldvaluebloblink) {
569 $sth=$dbh->prepare("update marc_blob_subfield set subfieldvalue=? where blobidlink=?");
570 $sth->execute($subfieldvalue,$oldvaluebloblink);
572 $sth=$dbh->prepare("insert into marc_blob_subfield (subfieldvalue) values (?)");
573 $sth->execute($subfieldvalue);
574 $sth=$dbh->prepare("select max(blobidlink) from marc_blob_subfield");
576 my ($res)=$sth->fetchrow;
577 $sth=$dbh->prepare("update marc_subfield_table set subfieldvalue=null, valuebloblink=$res where subfieldid=?");
578 $sth->execute($subfieldid);
581 # note this can leave orphan bloblink. Not a big problem, but we should build somewhere a orphan deleting script...
582 $sth=$dbh->prepare("update marc_subfield_table set subfieldvalue=?,valuebloblink=null where subfieldid=?");
583 $sth->execute($subfieldvalue, $subfieldid);
585 $dbh->do("unlock tables");
587 $sth=$dbh->prepare("select bibid,tag,tagorder,subfieldcode,subfieldid,subfieldorder from marc_subfield_table where subfieldid=?");
588 $sth->execute($subfieldid);
589 my ($bibid,$tagid,$tagorder,$subfieldcode,$x,$subfieldorder) = $sth->fetchrow;
591 &MARCdelword($dbh,$bibid,$tagid,$tagorder,$subfieldcode,$subfieldorder);
592 &MARCaddword($dbh,$bibid,$tagid,$tagorder,$subfieldcode,$subfieldorder,$subfieldvalue);
593 return($subfieldid, $subfieldvalue);
596 sub MARCfindsubfield {
597 my ($dbh,$bibid,$tag,$subfieldcode,$subfieldorder,$subfieldvalue) = @_;
601 my $query="select subfieldid from marc_subfield_table where bibid=? and tag=? and subfieldcode=?";
602 if ($subfieldvalue) {
603 $query .= " and subfieldvalue=".$dbh->quote($subfieldvalue);
605 if ($subfieldorder<1) {
608 $query .= " and subfieldorder=$subfieldorder";
610 my $sti=$dbh->prepare($query);
611 $sti->execute($bibid,$tag, $subfieldcode);
612 while (($subfieldid) = $sti->fetchrow) {
614 $lastsubfieldid=$subfieldid;
616 if ($resultcounter>1) {
617 # Error condition. Values given did not resolve into a unique record. Don't know what to edit
618 # should rarely occur (only if we use subfieldvalue with a value that exists twice, which is strange)
621 return $lastsubfieldid;
625 sub MARCfindsubfieldid {
626 my ($dbh,$bibid,$tag,$tagorder,$subfield,$subfieldorder) = @_;
627 my $sth=$dbh->prepare("select subfieldid from marc_subfield_table
628 where bibid=? and tag=? and tagorder=?
629 and subfieldcode=? and subfieldorder=?");
630 $sth->execute($bibid,$tag,$tagorder,$subfield,$subfieldorder);
631 my ($res) = $sth->fetchrow;
635 sub MARCdelsubfield {
636 # delete a subfield for $bibid / tag / tagorder / subfield / subfieldorder
637 my ($dbh,$bibid,$tag,$tagorder,$subfield,$subfieldorder) = @_;
638 $dbh->do("delete from marc_subfield_table where bibid='$bibid' and
639 tag='$tag' and tagorder='$tagorder'
640 and subfieldcode='$subfield' and subfieldorder='$subfieldorder
645 # delete a biblio for a $bibid
646 my ($dbh,$bibid) = @_;
647 $dbh->do("delete from marc_subfield_table where bibid='$bibid'");
648 $dbh->do("delete from marc_biblio where bibid='$bibid'");
651 sub MARCkoha2marcBiblio {
652 # this function builds partial MARC::Record from the old koha-DB fields
653 my ($dbh,$biblionumber,$biblioitemnumber) = @_;
654 my $sth=$dbh->prepare("select tagfield,tagsubfield from marc_subfield_structure where kohafield=?");
655 my $record = MARC::Record->new();
656 #--- if bibid, then retrieve old-style koha data
657 if ($biblionumber>0) {
658 my $sth2=$dbh->prepare("select biblionumber,author,title,unititle,notes,abstract,serial,seriestitle,copyrightdate,timestamp
659 from biblio where biblionumber=?");
660 $sth2->execute($biblionumber);
661 my $row=$sth2->fetchrow_hashref;
663 foreach $code (keys %$row) {
665 &MARCkoha2marcOnefield($sth,$record,"biblio.".$code,$row->{$code});
669 #--- if biblioitem, then retrieve old-style koha data
670 if ($biblioitemnumber>0) {
671 my $sth2=$dbh->prepare(" SELECT biblioitemnumber,biblionumber,volume,number,classification,
672 itemtype,url,isbn,issn,dewey,subclass,publicationyear,publishercode,
673 volumedate,volumeddesc,timestamp,illus,pages,notes,size,place
675 WHERE biblionumber=? and biblioitemnumber=?
677 $sth2->execute($biblionumber,$biblioitemnumber);
678 my $row=$sth2->fetchrow_hashref;
680 foreach $code (keys %$row) {
682 &MARCkoha2marcOnefield($sth,$record,"biblioitems.".$code,$row->{$code});
687 # TODO : retrieve notes, additionalauthors
690 sub MARCkoha2marcItem {
691 # this function builds partial MARC::Record from the old koha-DB fields
692 my ($dbh,$biblionumber,$itemnumber) = @_;
693 # my $dbh=&C4Connect;
694 my $sth=$dbh->prepare("select tagfield,tagsubfield from marc_subfield_structure where kohafield=?");
695 my $record = MARC::Record->new();
696 #--- if item, then retrieve old-style koha data
698 # print STDERR "prepare $biblionumber,$itemnumber\n";
699 my $sth2=$dbh->prepare("SELECT itemnumber,biblionumber,multivolumepart,biblioitemnumber,barcode,dateaccessioned,
700 booksellerid,homebranch,price,replacementprice,replacementpricedate,datelastborrowed,
701 datelastseen,multivolume,stack,notforloan,itemlost,wthdrawn,bulk,issues,renewals,
702 reserves,restricted,binding,itemnotes,holdingbranch,interim,timestamp
704 WHERE itemnumber=?");
705 $sth2->execute($itemnumber);
706 my $row=$sth2->fetchrow_hashref;
708 foreach $code (keys %$row) {
710 &MARCkoha2marcOnefield($sth,$record,"items.".$code,$row->{$code});
715 # TODO : retrieve notes, additionalauthors
718 sub MARCkoha2marcSubtitle {
719 # this function builds partial MARC::Record from the old koha-DB fields
720 my ($dbh,$bibnum,$subtitle) = @_;
721 my $sth=$dbh->prepare("select tagfield,tagsubfield from marc_subfield_structure where kohafield=?");
722 my $record = MARC::Record->new();
723 &MARCkoha2marcOnefield($sth,$record,"bibliosubtitle.subtitle",$subtitle);
727 sub MARCkoha2marcOnefield {
728 my ($sth,$record,$kohafieldname,$value)=@_;
731 $sth->execute($kohafieldname);
732 if (($tagfield,$tagsubfield)=$sth->fetchrow) {
733 if ($record->field($tagfield)) {
734 my $tag =$record->field($tagfield);
736 $tag->add_subfields($tagsubfield,$value);
737 $record->delete_field($tag);
738 $record->add_fields($tag);
741 $record->add_fields($tagfield," "," ",$tagsubfield => $value);
748 my ($dbh,$record) = @_;
749 my $sth=$dbh->prepare("select tagfield,tagsubfield from marc_subfield_structure where kohafield=?");
751 my $sth2=$dbh->prepare("SHOW COLUMNS from biblio");
754 # print STDERR $record->as_formatted;
755 while (($field)=$sth2->fetchrow) {
756 $result=&MARCmarc2kohaOneField($sth,"biblio",$field,$record,$result);
758 my $sth2=$dbh->prepare("SHOW COLUMNS from biblioitems");
761 while (($field)=$sth2->fetchrow) {
762 $result=&MARCmarc2kohaOneField($sth,"biblioitems",$field,$record,$result);
764 my $sth2=$dbh->prepare("SHOW COLUMNS from items");
767 while (($field)=$sth2->fetchrow) {
768 $result = &MARCmarc2kohaOneField($sth,"items",$field,$record,$result);
770 # additional authors : specific
771 $result = &MARCmarc2kohaOneField($sth,"additionalauthors","additionalauthors",$record,$result);
775 sub MARCmarc2kohaOneField {
776 # to check : if a field has a repeatable subfield that is used in old-db, only the 1st will be retrieved...
777 my ($sth,$kohatable,$kohafield,$record,$result)= @_;
781 $sth->execute($kohatable.".".$kohafield);
782 ($tagfield,$subfield) = $sth->fetchrow;
783 foreach my $field ($record->field($tagfield)) {
784 if ($field->subfield($subfield)) {
785 if ($result->{$kohafield}) {
786 $result->{$kohafield} .= " | ".$field->subfield($subfield);
788 $result->{$kohafield}=$field->subfield($subfield);
796 # split a subfield string and adds it into the word table.
798 my ($dbh,$bibid,$tag,$tagorder,$subfieldid,$subfieldorder,$sentence) =@_;
799 $sentence =~ s/(\.|\?|\:|\!|\'|,|\-)/ /g;
800 my @words = split / /,$sentence;
801 # build stopword list
802 my $sth2 =$dbh->prepare("select word from stopwords");
806 while(($stopword) = $sth2->fetchrow_array) {
807 $stopwords->{$stopword} = $stopword;
809 my $sth=$dbh->prepare("insert into marc_word (bibid, tag, tagorder, subfieldid, subfieldorder, word, sndx_word)
810 values (?,?,?,?,?,?,soundex(?))");
811 foreach my $word (@words) {
812 # we record only words longer than 2 car and not in stopwords hash
813 if (length($word)>1 and !($stopwords->{uc($word)})) {
814 $sth->execute($bibid,$tag,$tagorder,$subfieldid,$subfieldorder,$word,$word);
816 print STDERR "ERROR ==> insert into marc_word (bibid, tag, tagorder, subfieldid, subfieldorder, word, sndx_word) values ($bibid,$tag,$tagorder,$subfieldid,$subfieldorder,$word,soundex($word))\n";
823 # delete words. this sub deletes all the words from a sentence. a subfield modif is done by a delete then a add
824 my ($dbh,$bibid,$tag,$tagorder,$subfield,$subfieldorder) = @_;
825 my $sth=$dbh->prepare("delete from marc_word where bibid=? and tag=? and tagorder=? and subfieldid=? and subfieldorder=?");
826 $sth->execute($bibid,$tag,$tagorder,$subfield,$subfieldorder);
831 # ALL ALL ALL ALL ALL ALL ALL ALL ALL ALL ALL ALL ALL ALL ALL ALL ALL ALL
834 # all the following subs are useful to manage MARC-DB with complete MARC records.
835 # it's used with marcimport, and marc management tools
840 all subs requires/use $dbh as 1st parameter.
841 those subs are used by the MARC-compliant version of koha : marc import, or marc management.
845 =head2 (oldbibnum,$oldbibitemnum) = ALLnewbibilio($dbh,$MARCRecord,$oldbiblio,$oldbiblioitem);
846 creates a new biblio from a MARC::Record. The 3rd and 4th parameter are hashes and may be ignored. If only 2 params are passed to the sub, the old-db hashes
847 are builded from the MARC::Record. If they are passed, they are used.
849 =head2 ALLnewitem($dbh,$olditem);
850 adds an item in the db. $olditem is a old-db hash.
854 Paul POULAIN paul.poulain@free.fr
859 my ($dbh, $record, $oldbiblio, $oldbiblioitem) = @_;
860 # note $oldbiblio and $oldbiblioitem are not mandatory.
861 # if not present, they will be builded from $record with MARCmarc2koha function
862 if (($oldbiblio) and not($oldbiblioitem)) {
863 print STDERR "ALLnewbiblio : missing parameter\n";
864 print "ALLnewbiblio : missing parameter : contact koha development team\n";
870 $oldbibnum = OLDnewbiblio($dbh,$oldbiblio);
871 $oldbiblioitem->{'biblionumber'} = $oldbibnum;
872 $oldbibitemnum = OLDnewbiblioitem($dbh,$oldbiblioitem);
874 my $olddata = MARCmarc2koha($dbh,$record);
875 $oldbibnum = OLDnewbiblio($dbh,$olddata);
876 $oldbibitemnum = OLDnewbiblioitem($dbh,$olddata);
878 # we must add bibnum and bibitemnum in MARC::Record...
879 # we build the new field with biblionumber and biblioitemnumber
880 # we drop the original field
881 # we add the new builded field.
882 # NOTE : Works only if the field is ONLY for biblionumber and biblioitemnumber
883 # (steve and paul : thinks 090 is a good choice)
884 my $sth=$dbh->prepare("select tagfield,tagsubfield from marc_subfield_structure where kohafield=?");
885 $sth->execute("biblio.biblionumber");
886 (my $tagfield1, my $tagsubfield1) = $sth->fetchrow;
887 $sth->execute("biblioitems.biblioitemnumber");
888 (my $tagfield2, my $tagsubfield2) = $sth->fetchrow;
889 print STDERR "tag1 : $tagfield1 / $tagsubfield1\n tag2 : $tagfield2 / $tagsubfield2\n";
890 if ($tagsubfield1 != $tagsubfield2) {
891 print STDERR "Error in ALLnewbiblio : biblio.biblionumber and biblioitems.biblioitemnumber MUST have the same field number";
892 print "Error in ALLnewbiblio : biblio.biblionumber and biblioitems.biblioitemnumber MUST have the same field number";
895 my $newfield = MARC::Field->new( $tagfield1,'','',
896 "$tagsubfield1" => $oldbibnum,
897 "$tagsubfield2" => $oldbibitemnum);
898 # drop old field and create new one...
899 my $old_field = $record->field($tagfield1);
900 $record->delete_field($old_field);
901 $record->add_fields($newfield);
902 my $bibid = MARCaddbiblio($dbh,$record,$oldbibnum);
903 return ( $oldbibnum,$oldbibitemnum );
907 my ($dbh, $item) = @_;
910 ($itemnumber,$error) = &OLDnewitems($dbh,$item,$item->{'barcode'});
911 # search MARC biblionumber
912 my $bibid=&MARCfind_MARCbibid_from_oldbiblionumber($dbh,$item->{'biblionumber'});
914 my $sth = $dbh->prepare("select max(tagorder) from marc_subfield_table where bibid=?");
915 $sth->execute($bibid);
916 my ($tagorder) = $sth->fetchrow;
919 # for each field, find MARC tag and subfield, and call the proper MARC sub
920 foreach my $itemkey (keys %$item) {
923 print STDERR "=============> $itemkey : ".$item->{$itemkey}."\n";
924 if ($itemkey eq "biblionumber" || $itemkey eq "biblioitemnumber") {
925 ($tagfield,$tagsubfield) = MARCfind_marc_from_kohafield($dbh,"biblio.".$itemkey);
927 ($tagfield,$tagsubfield) = MARCfind_marc_from_kohafield($dbh,"items.".$itemkey);
929 if ($tagfield && $item->{$itemkey} ne 'NULL') {
931 &MARCaddsubfield($dbh,
947 # OLD OLD OLD OLD OLD OLD OLD OLD OLD OLD OLD OLD OLD OLD OLD OLD OLD
953 all subs requires/use $dbh as 1st parameter.
954 those subs are used by the MARC-compliant version of koha : marc import, or marc management.
956 They all are the exact copy of 1.0/1.2 version of the sub
957 without the OLD. The OLDxxx is called by the original xxx sub.
958 the 1.4 xxx sub also builds MARC::Record an calls the MARCxxx
960 WARNING : there is 1 difference between initialxxx and OLDxxx :
961 the db header $dbh is always passed as parameter
962 to avoid over-DB connexion
966 =head2 $biblionumber = OLDnewbiblio($dbh,$biblio);
967 adds a record in biblio table. Datas are in the hash $biblio.
969 =head2 $biblionumber = OLDmodbiblio($dbh,$biblio);
970 modify a record in biblio table. Datas are in the hash $biblio.
972 =head2 OLDmodsubtitle($dbh,$bibnum,$subtitle);
973 modify subtitles in bibliosubtitle table.
975 =head2 OLDmodaddauthor($dbh,$bibnum,$author);
976 adds or modify additional authors
977 NOTE : Strange sub : seems to delete MANY and add only ONE author... maybe buggy ?
979 =head2 $errors = OLDmodsubject($dbh,$bibnum, $force, @subject);
982 =head2 OLDmodbibitem($dbh, $biblioitem);
985 =head2 OLDmodnote($dbh,$bibitemnum,$note
986 modify a note for a biblioitem
988 =head2 OLDnewbiblioitem($dbh,$biblioitem);
989 adds a biblioitem ($biblioitem is a hash with the values)
991 =head2 OLDnewsubject($dbh,$bibnum);
993 =head2 OLDnewsubtitle($dbh,$bibnum,$subtitle);
994 create a new subtitle
996 =head2 ($itemnumber,$errors)= OLDnewitems($dbh,$item,$barcode);
997 create a item. $item is a hash and $barcode the barcode.
999 =head2 OLDmoditem($dbh,$item);
1002 =head2 OLDdelitem($dbh,$itemnum);
1005 =head2 OLDdeletebiblioitem($dbh,$biblioitemnumber);
1006 deletes a biblioitem
1007 NOTE : not standard sub name. Should be OLDdelbiblioitem()
1009 =head2 OLDdelbiblio($dbh,$biblio);
1014 Paul POULAIN paul.poulain@free.fr
1019 my ($dbh,$biblio) = @_;
1020 # my $dbh = &C4Connect;
1021 my $query = "Select max(biblionumber) from biblio";
1022 my $sth = $dbh->prepare($query);
1024 my $data = $sth->fetchrow_arrayref;
1025 my $bibnum = $$data[0] + 1;
1028 $biblio->{'title'} = $dbh->quote($biblio->{'title'});
1029 $biblio->{'author'} = $dbh->quote($biblio->{'author'});
1030 $biblio->{'copyright'} = $dbh->quote($biblio->{'copyright'});
1031 $biblio->{'seriestitle'} = $dbh->quote($biblio->{'seriestitle'});
1032 $biblio->{'notes'} = $dbh->quote($biblio->{'notes'});
1033 $biblio->{'abstract'} = $dbh->quote($biblio->{'abstract'});
1034 if ($biblio->{'seriestitle'}) { $series = 1 };
1037 $query = "insert into biblio set
1038 biblionumber = $bibnum,
1039 title = $biblio->{'title'},
1040 author = $biblio->{'author'},
1041 copyrightdate = $biblio->{'copyright'},
1043 seriestitle = $biblio->{'seriestitle'},
1044 notes = $biblio->{'notes'},
1045 abstract = $biblio->{'abstract'}";
1047 $sth = $dbh->prepare($query);
1056 my ($dbh,$biblio) = @_;
1057 # my $dbh = C4Connect;
1061 $biblio->{'title'} = $dbh->quote($biblio->{'title'});
1062 $biblio->{'author'} = $dbh->quote($biblio->{'author'});
1063 $biblio->{'abstract'} = $dbh->quote($biblio->{'abstract'});
1064 $biblio->{'copyrightdate'} = $dbh->quote($biblio->{'copyrightdate'});
1065 $biblio->{'seriestitle'} = $dbh->quote($biblio->{'serirestitle'});
1066 $biblio->{'serial'} = $dbh->quote($biblio->{'serial'});
1067 $biblio->{'unititle'} = $dbh->quote($biblio->{'unititle'});
1068 $biblio->{'notes'} = $dbh->quote($biblio->{'notes'});
1070 $query = "Update biblio set
1071 title = $biblio->{'title'},
1072 author = $biblio->{'author'},
1073 abstract = $biblio->{'abstract'},
1074 copyrightdate = $biblio->{'copyrightdate'},
1075 seriestitle = $biblio->{'seriestitle'},
1076 serial = $biblio->{'serial'},
1077 unititle = $biblio->{'unititle'},
1078 notes = $biblio->{'notes'}
1079 where biblionumber = $biblio->{'biblionumber'}";
1080 $sth = $dbh->prepare($query);
1086 return($biblio->{'biblionumber'});
1089 sub OLDmodsubtitle {
1090 my ($dbh,$bibnum, $subtitle) = @_;
1091 # my $dbh = C4Connect;
1092 my $query = "update bibliosubtitle set
1093 subtitle = '$subtitle'
1094 where biblionumber = $bibnum";
1095 my $sth = $dbh->prepare($query);
1103 sub OLDmodaddauthor {
1104 my ($dbh,$bibnum, $author) = @_;
1105 # my $dbh = C4Connect;
1106 my $query = "Delete from additionalauthors where biblionumber = $bibnum";
1107 my $sth = $dbh->prepare($query);
1112 if ($author ne '') {
1113 $query = "Insert into additionalauthors set
1115 biblionumber = '$bibnum'";
1116 $sth = $dbh->prepare($query);
1124 } # sub modaddauthor
1128 my ($dbh,$bibnum, $force, @subject) = @_;
1129 # my $dbh = C4Connect;
1130 my $count = @subject;
1132 for (my $i = 0; $i < $count; $i++) {
1133 $subject[$i] =~ s/^ //g;
1134 $subject[$i] =~ s/ $//g;
1135 my $query = "select * from catalogueentry
1136 where entrytype = 's'
1137 and catalogueentry = '$subject[$i]'";
1138 my $sth = $dbh->prepare($query);
1141 if (my $data = $sth->fetchrow_hashref) {
1143 if ($force eq $subject[$i]) {
1144 # subject not in aut, chosen to force anway
1145 # so insert into cataloguentry so its in auth file
1146 $query = "Insert into catalogueentry
1147 (entrytype,catalogueentry)
1148 values ('s','$subject[$i]')";
1149 my $sth2 = $dbh->prepare($query);
1154 $error = "$subject[$i]\n does not exist in the subject authority file";
1155 $query = "Select * from catalogueentry
1156 where entrytype = 's'
1157 and (catalogueentry like '$subject[$i] %'
1158 or catalogueentry like '% $subject[$i] %'
1159 or catalogueentry like '% $subject[$i]')";
1160 my $sth2 = $dbh->prepare($query);
1163 while (my $data = $sth2->fetchrow_hashref) {
1164 $error = $error."<br>$data->{'catalogueentry'}";
1172 my $query = "Delete from bibliosubject where biblionumber = $bibnum";
1173 my $sth = $dbh->prepare($query);
1176 for (my $i = 0; $i < $count; $i++) {
1177 $sth = $dbh->prepare("Insert into bibliosubject
1178 values ('$subject[$i]', $bibnum)");
1190 my ($dbh,$biblioitem) = @_;
1191 # my $dbh = C4Connect;
1194 $biblioitem->{'itemtype'} = $dbh->quote($biblioitem->{'itemtype'});
1195 $biblioitem->{'url'} = $dbh->quote($biblioitem->{'url'});
1196 $biblioitem->{'isbn'} = $dbh->quote($biblioitem->{'isbn'});
1197 $biblioitem->{'publishercode'} = $dbh->quote($biblioitem->{'publishercode'});
1198 $biblioitem->{'publicationyear'} = $dbh->quote($biblioitem->{'publicationyear'});
1199 $biblioitem->{'classification'} = $dbh->quote($biblioitem->{'classification'});
1200 $biblioitem->{'dewey'} = $dbh->quote($biblioitem->{'dewey'});
1201 $biblioitem->{'subclass'} = $dbh->quote($biblioitem->{'subclass'});
1202 $biblioitem->{'illus'} = $dbh->quote($biblioitem->{'illus'});
1203 $biblioitem->{'pages'} = $dbh->quote($biblioitem->{'pages'});
1204 $biblioitem->{'volumeddesc'} = $dbh->quote($biblioitem->{'volumeddesc'});
1205 $biblioitem->{'notes'} = $dbh->quote($biblioitem->{'notes'});
1206 $biblioitem->{'size'} = $dbh->quote($biblioitem->{'size'});
1207 $biblioitem->{'place'} = $dbh->quote($biblioitem->{'place'});
1209 $query = "Update biblioitems set
1210 itemtype = $biblioitem->{'itemtype'},
1211 url = $biblioitem->{'url'},
1212 isbn = $biblioitem->{'isbn'},
1213 publishercode = $biblioitem->{'publishercode'},
1214 publicationyear = $biblioitem->{'publicationyear'},
1215 classification = $biblioitem->{'classification'},
1216 dewey = $biblioitem->{'dewey'},
1217 subclass = $biblioitem->{'subclass'},
1218 illus = $biblioitem->{'illus'},
1219 pages = $biblioitem->{'pages'},
1220 volumeddesc = $biblioitem->{'volumeddesc'},
1221 notes = $biblioitem->{'notes'},
1222 size = $biblioitem->{'size'},
1223 place = $biblioitem->{'place'}
1224 where biblioitemnumber = $biblioitem->{'biblioitemnumber'}";
1232 my ($dbh,$bibitemnum,$note)=@_;
1233 # my $dbh=C4Connect;
1234 my $query="update biblioitems set notes='$note' where
1235 biblioitemnumber='$bibitemnum'";
1236 my $sth=$dbh->prepare($query);
1242 sub OLDnewbiblioitem {
1243 my ($dbh,$biblioitem) = @_;
1244 # my $dbh = C4Connect;
1245 my $query = "Select max(biblioitemnumber) from biblioitems";
1246 my $sth = $dbh->prepare($query);
1250 $biblioitem->{'volume'} = $dbh->quote($biblioitem->{'volume'});
1251 $biblioitem->{'number'} = $dbh->quote($biblioitem->{'number'});
1252 $biblioitem->{'classification'} = $dbh->quote($biblioitem->{'classification'});
1253 $biblioitem->{'itemtype'} = $dbh->quote($biblioitem->{'itemtype'});
1254 $biblioitem->{'url'} = $dbh->quote($biblioitem->{'url'});
1255 $biblioitem->{'isbn'} = $dbh->quote($biblioitem->{'isbn'});
1256 $biblioitem->{'issn'} = $dbh->quote($biblioitem->{'issn'});
1257 $biblioitem->{'dewey'} = $dbh->quote($biblioitem->{'dewey'});
1258 $biblioitem->{'subclass'} = $dbh->quote($biblioitem->{'subclass'});
1259 $biblioitem->{'publicationyear'} = $dbh->quote($biblioitem->{'publicationyear'});
1260 $biblioitem->{'publishercode'} = $dbh->quote($biblioitem->{'publishercode'});
1261 $biblioitem->{'volumedate'} = $dbh->quote($biblioitem->{'volumedate'});
1262 $biblioitem->{'volumeddesc'} = $dbh->quote($biblioitem->{'volumeddesc'}); $biblioitem->{'illus'} = $dbh->quote($biblioitem->{'illus'});
1263 $biblioitem->{'illus'} = $dbh->quote($biblioitem->{'illus'});
1264 $biblioitem->{'pages'} = $dbh->quote($biblioitem->{'pages'});
1265 $biblioitem->{'notes'} = $dbh->quote($biblioitem->{'notes'});
1266 $biblioitem->{'size'} = $dbh->quote($biblioitem->{'size'});
1267 $biblioitem->{'place'} = $dbh->quote($biblioitem->{'place'});
1268 $biblioitem->{'lccn'} = $dbh->quote($biblioitem->{'lccn'});
1269 $biblioitem->{'marc'} = $dbh->quote($biblioitem->{'marc'});
1272 $data = $sth->fetchrow_arrayref;
1273 $bibitemnum = $$data[0] + 1;
1277 $query = "insert into biblioitems set
1278 biblioitemnumber = $bibitemnum,
1279 biblionumber = $biblioitem->{'biblionumber'},
1280 volume = $biblioitem->{'volume'},
1281 number = $biblioitem->{'number'},
1282 classification = $biblioitem->{'classification'},
1283 itemtype = $biblioitem->{'itemtype'},
1284 url = $biblioitem->{'url'},
1285 isbn = $biblioitem->{'isbn'},
1286 issn = $biblioitem->{'issn'},
1287 dewey = $biblioitem->{'dewey'},
1288 subclass = $biblioitem->{'subclass'},
1289 publicationyear = $biblioitem->{'publicationyear'},
1290 publishercode = $biblioitem->{'publishercode'},
1291 volumedate = $biblioitem->{'volumedate'},
1292 volumeddesc = $biblioitem->{'volumeddesc'},
1293 illus = $biblioitem->{'illus'},
1294 pages = $biblioitem->{'pages'},
1295 notes = $biblioitem->{'notes'},
1296 size = $biblioitem->{'size'},
1297 lccn = $biblioitem->{'lccn'},
1298 marc = $biblioitem->{'marc'},
1299 place = $biblioitem->{'place'}";
1301 $sth = $dbh->prepare($query);
1305 return($bibitemnum);
1309 my ($dbh,$bibnum)=@_;
1310 # my $dbh=C4Connect;
1311 my $query="insert into bibliosubject (biblionumber) values
1313 my $sth=$dbh->prepare($query);
1320 sub OLDnewsubtitle {
1321 my ($dbh,$bibnum, $subtitle) = @_;
1322 # my $dbh = C4Connect;
1323 $subtitle = $dbh->quote($subtitle);
1324 my $query = "insert into bibliosubtitle set
1325 biblionumber = $bibnum,
1326 subtitle = $subtitle";
1327 my $sth = $dbh->prepare($query);
1337 my ($dbh,$item, $barcode) = @_;
1338 # my $dbh = C4Connect;
1339 my $query = "Select max(itemnumber) from items";
1340 my $sth = $dbh->prepare($query);
1346 $data = $sth->fetchrow_hashref;
1347 $itemnumber = $data->{'max(itemnumber)'} + 1;
1350 $item->{'booksellerid'} = $dbh->quote($item->{'booksellerid'});
1351 $item->{'homebranch'} = $dbh->quote($item->{'homebranch'});
1352 $item->{'price'} = $dbh->quote($item->{'price'});
1353 $item->{'replacementprice'} = $dbh->quote($item->{'replacementprice'});
1354 $item->{'itemnotes'} = $dbh->quote($item->{'itemnotes'});
1356 # foreach my $barcode (@barcodes) {
1357 # $barcode = uc($barcode);
1358 $barcode = $dbh->quote($barcode);
1359 $query = "Insert into items set
1360 itemnumber = $itemnumber,
1361 biblionumber = $item->{'biblionumber'},
1362 biblioitemnumber = $item->{'biblioitemnumber'},
1364 booksellerid = $item->{'booksellerid'},
1365 dateaccessioned = NOW(),
1366 homebranch = $item->{'homebranch'},
1367 holdingbranch = $item->{'homebranch'},
1368 price = $item->{'price'},
1369 replacementprice = $item->{'replacementprice'},
1370 replacementpricedate = NOW(),
1371 itemnotes = $item->{'itemnotes'}";
1372 if ($item->{'loan'}) {
1373 $query .= ",notforloan = $item->{'loan'}";
1376 $sth = $dbh->prepare($query);
1378 if (defined $sth->errstr) {
1379 $error .= $sth->errstr;
1384 return($itemnumber,$error);
1388 my ($dbh,$item) = @_;
1389 # my ($dbh,$loan,$itemnum,$bibitemnum,$barcode,$notes,$homebranch,$lost,$wthdrawn,$replacement)=@_;
1390 # my $dbh=C4Connect;
1391 my $query="update items set biblioitemnumber=$item->{'bibitemnum'},
1392 barcode='$item->{'barcode'}',itemnotes='$item->{'notes'}'
1393 where itemnumber=$item->{'itemnum'}";
1394 if ($item->{'barcode'} eq ''){
1395 $query="update items set biblioitemnumber=$item->{'bibitemnum'},notforloan=$item->{'loan'} where itemnumber=$item->{'itemnum'}";
1397 if ($item->{'lost'} ne ''){
1398 $query="update items set biblioitemnumber=$item->{'bibitemnum'},
1399 barcode='$item->{'barcode'}',
1400 itemnotes='$item->{'notes'}',
1401 homebranch='$item->{'homebranch'}',
1402 itemlost='$item->{'lost'}',
1403 wthdrawn='$item->{'wthdrawn'}'
1404 where itemnumber=$item->{'itemnum'}";
1406 if ($item->{'replacement'} ne ''){
1407 $query=~ s/ where/,replacementprice='$item->{'replacement'}' where/;
1410 my $sth=$dbh->prepare($query);
1417 my ($dbh,$itemnum)=@_;
1418 # my $dbh=C4Connect;
1419 my $query="select * from items where itemnumber=$itemnum";
1420 my $sth=$dbh->prepare($query);
1422 my @data=$sth->fetchrow_array;
1424 $query="Insert into deleteditems values (";
1425 foreach my $temp (@data){
1426 $query=$query."'$temp',";
1430 $sth=$dbh->prepare($query);
1433 $query = "Delete from items where itemnumber=$itemnum";
1434 $sth=$dbh->prepare($query);
1440 sub OLDdeletebiblioitem {
1441 my ($dbh,$biblioitemnumber) = @_;
1442 # my $dbh = C4Connect;
1443 my $query = "Select * from biblioitems
1444 where biblioitemnumber = $biblioitemnumber";
1445 my $sth = $dbh->prepare($query);
1450 if (@results = $sth->fetchrow_array) {
1451 $query = "Insert into deletedbiblioitems values (";
1452 foreach my $value (@results) {
1453 $value = $dbh->quote($value);
1454 $query .= "$value,";
1457 $query =~ s/\,$/\)/;
1460 $query = "Delete from biblioitems
1461 where biblioitemnumber = $biblioitemnumber";
1465 # Now delete all the items attached to the biblioitem
1466 $query = "Select * from items where biblioitemnumber = $biblioitemnumber";
1467 $sth = $dbh->prepare($query);
1469 while (@results = $sth->fetchrow_array) {
1470 $query = "Insert into deleteditems values (";
1471 foreach my $value (@results) {
1472 $value = $dbh->quote($value);
1473 $query .= "$value,";
1475 $query =~ s/\,$/\)/;
1479 $query = "Delete from items where biblioitemnumber = $biblioitemnumber";
1482 } # sub deletebiblioitem
1485 my ($dbh,$biblio)=@_;
1486 # my $dbh=C4Connect;
1487 my $query="select * from biblio where biblionumber=$biblio";
1488 my $sth=$dbh->prepare($query);
1490 if (my @data=$sth->fetchrow_array){
1492 $query="Insert into deletedbiblio values (";
1493 foreach my $temp (@data){
1494 $temp=~ s/\'/\\\'/g;
1495 $query=$query."'$temp',";
1499 $sth=$dbh->prepare($query);
1502 $query = "Delete from biblio where biblionumber=$biblio";
1503 $sth=$dbh->prepare($query);
1520 my $query="Select count(*) from items where biblionumber=$biblio";
1522 my $sth=$dbh->prepare($query);
1524 my $data=$sth->fetchrow_hashref;
1527 return($data->{'count(*)'});
1533 my $query="Select ordernumber
1535 where biblionumber=? and biblioitemnumber=?";
1536 my $sth=$dbh->prepare($query);
1537 $sth->execute($bib,$bi);
1538 my $ordnum=$sth->fetchrow_hashref;
1540 my $order=getsingleorder($ordnum->{'ordernumber'});
1543 return ($order,$ordnum->{'ordernumber'});
1546 sub getsingleorder {
1549 my $query="Select * from biblio,biblioitems,aqorders,aqorderbreakdown
1550 where aqorders.ordernumber=?
1551 and biblio.biblionumber=aqorders.biblionumber and
1552 biblioitems.biblioitemnumber=aqorders.biblioitemnumber and
1553 aqorders.ordernumber=aqorderbreakdown.ordernumber";
1554 my $sth=$dbh->prepare($query);
1555 $sth->execute($ordnum);
1556 my $data=$sth->fetchrow_hashref;
1564 my $dbh = &C4Connect;
1565 my $bibnum=OLDnewbiblio($dbh,$biblio);
1573 my $dbh = C4Connect;
1574 my $biblionumber=OLDmodbiblio($dbh,$biblio);
1576 return($biblionumber);
1580 my ($bibnum, $subtitle) = @_;
1581 my $dbh = C4Connect;
1582 &OLDmodsubtitle($dbh,$bibnum,$subtitle);
1588 my ($bibnum, $author) = @_;
1589 my $dbh = C4Connect;
1590 &OLDmodaddauthor($dbh,$bibnum,$author);
1592 } # sub modaddauthor
1596 my ($bibnum, $force, @subject) = @_;
1597 my $dbh = C4Connect;
1598 my $error= &OLDmodsubject($dbh,$bibnum,$force, @subject);
1603 my ($biblioitem) = @_;
1604 my $dbh = C4Connect;
1605 &OLDmodbibitem($dbh,$biblioitem);
1606 my $MARCbibitem = MARCkoha2marcBiblio($dbh,$biblioitem);
1607 &MARCmodbiblio($dbh,$biblioitem->{biblionumber},0,$MARCbibitem);
1612 my ($bibitemnum,$note)=@_;
1614 &OLDmodnote($dbh,$bibitemnum,$note);
1619 my ($biblioitem) = @_;
1620 my $dbh = C4Connect;
1621 my $bibitemnum = &OLDnewbiblioitem($dbh,$biblioitem);
1622 # print STDERR "bibitemnum : $bibitemnum\n";
1623 my $MARCbiblio= MARCkoha2marcBiblio($dbh,$biblioitem->{biblionumber},$bibitemnum);
1624 # print STDERR $MARCbiblio->as_formatted();
1625 &MARCaddbiblio($dbh,$MARCbiblio,$biblioitem->{biblionumber});
1626 return($bibitemnum);
1632 &OLDnewsubject($dbh,$bibnum);
1637 my ($bibnum, $subtitle) = @_;
1638 my $dbh = C4Connect;
1639 &OLDnewsubtitle($dbh,$bibnum,$subtitle);
1644 my ($item, @barcodes) = @_;
1645 my $dbh = C4Connect;
1649 foreach my $barcode (@barcodes) {
1650 ($itemnumber,$error)=&OLDnewitems($dbh,$item,uc($barcode));
1652 # print STDERR "biblionumber : $item->{biblionumber} / MARCbibid : $MARCbibid / itemnumber : $itemnumber\n";
1653 my $MARCitem = &MARCkoha2marcItem($dbh,$item->{biblionumber},$itemnumber);
1654 # print STDERR "MARCitem ".$MARCitem->as_formatted()."\n";
1655 &MARCadditem($dbh,$MARCitem,$item->{biblionumber});
1656 # print STDERR "MARCmodbiblio called\n";
1664 # my ($loan,$itemnum,$bibitemnum,$barcode,$notes,$homebranch,$lost,$wthdrawn,$replacement)=@_;
1666 &OLDmoditem($dbh,$item);
1667 warn "biblionumber : $item->{'biblionumber'} / $item->{'itemnum'}\n";
1668 my $MARCitem = &MARCkoha2marcItem($dbh,$item->{'biblionumber'},$item->{'itemnum'});
1669 warn "before MARCmoditem : $item->{biblionumber}, $item->{'itemnum'}\n";
1670 warn $MARCitem->as_formatted();
1671 # print STDERR "MARCitem ".$MARCitem->as_formatted()."\n";
1672 my $bibid = &MARCfind_MARCbibid_from_oldbiblionumber($dbh,$item->{biblionumber});
1673 &MARCmoditem($dbh,$MARCitem,$bibid,$item->{itemnum},0);
1678 my ($count,@barcodes)=@_;
1681 for (my $i=0;$i<$count;$i++){
1682 $barcodes[$i]=uc $barcodes[$i];
1683 my $query="Select * from items where barcode='$barcodes[$i]'";
1684 my $sth=$dbh->prepare($query);
1686 if (my $data=$sth->fetchrow_hashref){
1687 $error.=" Duplicate Barcode: $barcodes[$i]";
1696 my ($bibitemnum)=@_;
1698 my $query="Select count(*) from items where biblioitemnumber='$bibitemnum'";
1699 my $sth=$dbh->prepare($query);
1701 my $data=$sth->fetchrow_hashref;
1704 return($data->{'count(*)'});
1710 &OLDdelitem($dbh,$itemnum);
1714 sub deletebiblioitem {
1715 my ($biblioitemnumber) = @_;
1716 my $dbh = C4Connect;
1717 &OLDdeletebiblioitem($dbh,$biblioitemnumber);
1719 } # sub deletebiblioitem
1725 &OLDdelbiblio($dbh,$biblio);
1730 my $dbh = C4Connect;
1731 my $query = "select * from itemtypes";
1732 my $sth = $dbh->prepare($query);
1733 # || die "Cannot prepare $query" . $dbh->errstr;
1738 # || die "Cannot execute $query\n" . $sth->errstr;
1739 while (my $data = $sth->fetchrow_hashref) {
1740 $results[$count] = $data;
1746 return($count, @results);
1747 } # sub getitemtypes
1750 my ($biblionumber) = @_;
1751 my $dbh = C4Connect;
1752 my $query = "Select * from biblio where biblionumber = $biblionumber";
1753 my $sth = $dbh->prepare($query);
1754 # || die "Cannot prepare $query\n" . $dbh->errstr;
1759 # || die "Cannot execute $query\n" . $sth->errstr;
1760 while (my $data = $sth->fetchrow_hashref) {
1761 $results[$count] = $data;
1767 return($count, @results);
1771 my ($biblioitemnum) = @_;
1772 my $dbh = C4Connect;
1773 my $query = "Select * from biblioitems where
1774 biblioitemnumber = $biblioitemnum";
1775 my $sth = $dbh->prepare($query);
1781 while (my $data = $sth->fetchrow_hashref) {
1782 $results[$count] = $data;
1788 return($count, @results);
1789 } # sub getbiblioitem
1791 sub getbiblioitembybiblionumber {
1792 my ($biblionumber) = @_;
1793 my $dbh = C4Connect;
1794 my $query = "Select * from biblioitems where biblionumber =
1796 my $sth = $dbh->prepare($query);
1802 while (my $data = $sth->fetchrow_hashref) {
1803 $results[$count] = $data;
1809 return($count, @results);
1812 sub getitemsbybiblioitem {
1813 my ($biblioitemnum) = @_;
1814 my $dbh = C4Connect;
1815 my $query = "Select * from items, biblio where
1816 biblio.biblionumber = items.biblionumber and biblioitemnumber
1818 my $sth = $dbh->prepare($query);
1819 # || die "Cannot prepare $query\n" . $dbh->errstr;
1824 # || die "Cannot execute $query\n" . $sth->errstr;
1825 while (my $data = $sth->fetchrow_hashref) {
1826 $results[$count] = $data;
1832 return($count, @results);
1833 } # sub getitemsbybiblioitem
1837 my $dbh = C4Connect;
1843 $isbn = $dbh->quote($isbn);
1844 $query = "Select biblio.* from biblio, biblioitems where
1845 biblio.biblionumber = biblioitems.biblionumber
1847 $sth = $dbh->prepare($query);
1850 while (my $data = $sth->fetchrow_hashref) {
1851 $results[$count] = $data;
1857 return($count, @results);
1861 # At the moment this is just a straight copy of the subject code. Needs heavy
1862 # modification to work for additional authors, obviously.
1863 # Check for additional author changes
1865 # my $newadditionalauthor='';
1866 # my $additionalauthors;
1867 # foreach $newadditionalauthor (@{$biblio->{'additionalauthor'}}) {
1868 # $additionalauthors->{$newadditionalauthor}=1;
1869 # if ($origadditionalauthors->{$newadditionalauthor}) {
1870 # $additionalauthors->{$newadditionalauthor}=2;
1872 # my $q_newadditionalauthor=$dbh->quote($newadditionalauthor);
1873 # my $sth=$dbh->prepare("insert into biblioadditionalauthors (additionalauthor,biblionumber) values ($q_newadditionalauthor, $biblionumber)");
1875 # logchange('kohadb', 'add', 'biblio', 'additionalauthor', $newadditionalauthor);
1877 # $subfields->{1}->{'Subfield_Mark'}='a';
1878 # $subfields->{1}->{'Subfield_Value'}=$newadditionalauthor;
1881 # foreach $Record_ID (@marcrecords) {
1882 # addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
1883 # logchange('marc', 'add', $Record_ID, '650', 'a', $newadditionalauthor);
1887 # my $origadditionalauthor;
1888 # foreach $origadditionalauthor (keys %$origadditionalauthors) {
1889 # if ($additionalauthors->{$origadditionalauthor} == 1) {
1890 # my $q_origadditionalauthor=$dbh->quote($origadditionalauthor);
1891 # logchange('kohadb', 'delete', 'biblio', '$biblionumber', 'additionalauthor', $origadditionalauthor);
1892 # my $sth=$dbh->prepare("delete from biblioadditionalauthors where biblionumber=$biblionumber and additionalauthor=$q_origadditionalauthor");
1902 # Subroutine to log changes to databases
1903 # Eventually, this subroutine will be used to create a log of all changes made,
1904 # with the possibility of "undo"ing some changes
1906 if ($database eq 'kohadb') {
1912 print STDERR "KOHA: $type $section $item $original $new\n";
1913 } elsif ($database eq 'marc') {
1915 my $Record_ID=shift;
1918 my $subfield_ID=shift;
1921 print STDERR "MARC: $type $Record_ID $tag $mark $subfield_ID $original $new\n";
1925 #------------------------------------------------
1928 #---------------------------------------
1929 # Find a biblio entry, or create a new one if it doesn't exist.
1930 # If a "subtitle" entry is in hash, add it to subtitle table
1931 sub getoraddbiblio {
1935 $biblio, # hash ref to fields
1946 requireDBI($dbh,"getoraddbiblio");
1948 print "<PRE>Looking for biblio </PRE>\n" if $debug;
1949 $sth=$dbh->prepare("select biblionumber
1951 where title=? and author=?
1952 and copyrightdate=? and seriestitle=?");
1954 $biblio->{title}, $biblio->{author},
1955 $biblio->{copyright}, $biblio->{seriestitle} );
1957 ($biblionumber) = $sth->fetchrow;
1958 print "<PRE>Biblio exists with number $biblionumber</PRE>\n" if $debug;
1960 # Doesn't exist. Add new one.
1961 print "<PRE>Adding biblio</PRE>\n" if $debug;
1962 ($biblionumber,$error)=&newbiblio($biblio);
1963 if ( $biblionumber ) {
1964 print "<PRE>Added with biblio number=$biblionumber</PRE>\n" if $debug;
1965 if ( $biblio->{subtitle} ) {
1966 &newsubtitle($biblionumber,$biblio->{subtitle} );
1969 print "<PRE>Couldn't add biblio: $error</PRE>\n" if $debug;
1973 return $biblionumber,$error;
1975 } # sub getoraddbiblio
1979 # UNUSEFUL SUBs. Could be deleted, kept only until beta test
1980 # maybe useful for some MARC tricks steve used.
1983 sub OLD_MAYBE_DELETED_newBiblioItem {
1984 my ($env, $biblioitem) = @_;
1986 my $biblionumber=$biblioitem->{'biblionumber'};
1987 my $biblioitemnumber=$biblioitem->{'biblioitemnumber'};
1988 my $volume=$biblioitem->{'volume'};
1989 my $q_volume=$dbh->quote($volume);
1990 my $number=$biblioitem->{'number'};
1991 my $q_number=$dbh->quote($number);
1992 my $classification=$biblioitem->{'classification'};
1993 my $q_classification=$dbh->quote($classification);
1994 my $itemtype=$biblioitem->{'itemtype'};
1995 my $q_itemtype=$dbh->quote($itemtype);
1996 my $isbn=$biblioitem->{'isbn'};
1997 my $q_isbn=$dbh->quote($isbn);
1998 my $issn=$biblioitem->{'issn'};
1999 my $q_issn=$dbh->quote($issn);
2000 my $dewey=$biblioitem->{'dewey'};
2002 ($dewey == 0) && ($dewey='');
2003 my $subclass=$biblioitem->{'subclass'};
2004 my $q_subclass=$dbh->quote($subclass);
2005 my $publicationyear=$biblioitem->{'publicationyear'};
2006 my $publishercode=$biblioitem->{'publishercode'};
2007 my $q_publishercode=$dbh->quote($publishercode);
2008 my $volumedate=$biblioitem->{'volumedate'};
2009 my $q_volumedate=$dbh->quote($volumedate);
2010 my $illus=$biblioitem->{'illus'};
2011 my $q_illus=$dbh->quote($illus);
2012 my $pages=$biblioitem->{'pages'};
2013 my $q_pages=$dbh->quote($pages);
2014 my $notes=$biblioitem->{'notes'};
2015 my $q_notes=$dbh->quote($notes);
2016 my $size=$biblioitem->{'size'};
2017 my $q_size=$dbh->quote($size);
2018 my $place=$biblioitem->{'place'};
2019 my $q_place=$dbh->quote($place);
2020 my $lccn=$biblioitem->{'lccn'};
2021 my $q_lccn=$dbh->quote($lccn);
2024 # Unless the $env->{'marconly'} flag is set, update the biblioitems table with
2027 unless ($env->{'marconly'}) {
2028 #my $sth=$dbh->prepare("lock tables biblioitems write");
2030 my $sth=$dbh->prepare("select max(biblioitemnumber) from biblioitems");
2032 my ($biblioitemnumber) =$sth->fetchrow;
2033 $biblioitemnumber++;
2034 $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)");
2036 #my $sth=$dbh->prepare("unlock tables");
2041 # Should we check if there is already a biblioitem/amrc with the
2042 # same isbn/lccn/issn?
2044 my $sth=$dbh->prepare("select title,unititle,seriestitle,copyrightdate,notes,author from biblio where biblionumber=$biblionumber");
2046 my ($title, $unititle,$seriestitle,$copyrightdate,$biblionotes,$author) = $sth->fetchrow;
2047 $sth=$dbh->prepare("select subtitle from bibliosubtitle where biblionumber=$biblionumber");
2049 my ($subtitle) = $sth->fetchrow;
2050 $sth=$dbh->prepare("select author from additionalauthors where biblionumber=$biblionumber");
2052 my @additionalauthors;
2053 while (my ($additionalauthor) = $sth->fetchrow) {
2054 push (@additionalauthors, $additionalauthor);
2056 $sth=$dbh->prepare("select subject from bibliosubject where biblionumber=$biblionumber");
2059 while (my ($subject) = $sth->fetchrow) {
2060 push (@subjects, $subject);
2065 $sth=$dbh->prepare("insert into Resource_Table (Record_ID) values (0)");
2067 my $Resource_ID=$dbh->{'mysql_insertid'};
2068 my $Record_ID=$Resource_ID;
2069 $sth=$dbh->prepare("update Resource_Table set Record_ID=$Record_ID where Resource_ID=$Resource_ID");
2075 $subfields->{1}->{'Subfield_Mark'}='a';
2076 $subfields->{1}->{'Subfield_Value'}=$title;
2078 $subfields->{2}->{'Subfield_Mark'}='b';
2079 $subfields->{2}->{'Subfield_Value'}=$subtitle;
2082 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
2088 $subfields->{1}->{'Subfield_Mark'}='a';
2089 $subfields->{1}->{'Subfield_Value'}=$author;
2091 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
2096 $subfields->{1}->{'Subfield_Mark'}='a';
2097 $subfields->{1}->{'Subfield_Value'}=$seriestitle;
2099 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
2104 $subfields->{1}->{'Subfield_Mark'}='a';
2105 $subfields->{1}->{'Subfield_Value'}=$biblionotes;
2106 $subfields->{2}->{'Subfield_Mark'}='3';
2107 $subfields->{2}->{'Subfield_Value'}='biblio';
2109 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
2111 # Additional Authors
2112 foreach (@additionalauthors) {
2114 (next) unless ($author);
2116 $subfields->{1}->{'Subfield_Mark'}='a';
2117 $subfields->{1}->{'Subfield_Value'}=$author;
2118 $subfields->{2}->{'Subfield_Mark'}='e';
2119 $subfields->{2}->{'Subfield_Value'}='author';
2121 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
2125 (next) unless ($illus);
2127 $subfields->{1}->{'Subfield_Mark'}='a';
2128 $subfields->{1}->{'Subfield_Value'}=$illus;
2129 $subfields->{2}->{'Subfield_Mark'}='e';
2130 $subfields->{2}->{'Subfield_Value'}='illustrator';
2132 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
2135 foreach (@subjects) {
2137 (next) unless ($subject);
2139 $subfields->{1}->{'Subfield_Mark'}='a';
2140 $subfields->{1}->{'Subfield_Value'}=$subject;
2142 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
2149 $subfields->{1}->{'Subfield_Mark'}='a';
2150 $subfields->{1}->{'Subfield_Value'}=$isbn;
2152 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
2157 $subfields->{1}->{'Subfield_Mark'}='a';
2158 $subfields->{1}->{'Subfield_Value'}=$lccn;
2160 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
2165 $subfields->{1}->{'Subfield_Mark'}='a';
2166 $subfields->{1}->{'Subfield_Value'}=$issn;
2168 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
2173 $subfields->{1}->{'Subfield_Mark'}='a';
2174 $subfields->{1}->{'Subfield_Value'}=$dewey;
2176 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
2178 # DEWEY subclass and itemtype
2181 $subfields->{1}->{'Subfield_Mark'}='a';
2182 $subfields->{1}->{'Subfield_Value'}=$itemtype;
2183 $subfields->{2}->{'Subfield_Mark'}='b';
2184 $subfields->{2}->{'Subfield_Value'}=$subclass;
2185 $subfields->{3}->{'Subfield_Mark'}='c';
2186 $subfields->{3}->{'Subfield_Value'}=$biblionumber;
2187 $subfields->{4}->{'Subfield_Mark'}='d';
2188 $subfields->{4}->{'Subfield_Value'}=$biblioitemnumber;
2190 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
2195 $subfields->{1}->{'Subfield_Mark'}='a';
2196 $subfields->{1}->{'Subfield_Value'}=$place;
2197 $subfields->{2}->{'Subfield_Mark'}='b';
2198 $subfields->{2}->{'Subfield_Value'}=$publishercode;
2199 $subfields->{3}->{'Subfield_Mark'}='c';
2200 $subfields->{3}->{'Subfield_Value'}=$publicationyear;
2201 if ($copyrightdate) {
2202 $subfields->{4}->{'Subfield_Mark'}='c';
2203 $subfields->{4}->{'Subfield_Value'}="c$copyrightdate";
2206 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
2209 if ($pages || $size) {
2211 $subfields->{1}->{'Subfield_Mark'}='a';
2212 $subfields->{1}->{'Subfield_Value'}=$pages;
2213 $subfields->{2}->{'Subfield_Mark'}='c';
2214 $subfields->{2}->{'Subfield_Value'}=$size;
2216 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
2219 if ($volume || $number) {
2221 $subfields->{1}->{'Subfield_Mark'}='v';
2222 $subfields->{1}->{'Subfield_Value'}=$volume;
2223 $subfields->{2}->{'Subfield_Mark'}='n';
2224 $subfields->{2}->{'Subfield_Value'}=$number;
2226 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
2231 $subfields->{1}->{'Subfield_Mark'}='a';
2232 $subfields->{1}->{'Subfield_Value'}=$notes;
2233 $subfields->{2}->{'Subfield_Mark'}='3';
2234 $subfields->{2}->{'Subfield_Value'}='biblioitem';
2236 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
2240 return ($env, $Record_ID);
2243 sub OLD_MAYBE_DELETED_newItem {
2244 my ($env, $Record_ID, $item) = @_;
2246 my $barcode=$item->{'barcode'};
2247 my $q_barcode=$dbh->quote($barcode);
2248 my $biblionumber=$item->{'biblionumber'};
2249 my $biblioitemnumber=$item->{'biblioitemnumber'};
2250 my $dateaccessioned=$item->{'dateaccessioned'};
2251 my $booksellerid=$item->{'booksellerid'};
2252 my $q_booksellerid=$dbh->quote($booksellerid);
2253 my $homebranch=$item->{'homebranch'};
2254 my $q_homebranch=$dbh->quote($homebranch);
2255 my $holdingbranch=$item->{'holdingbranch'};
2256 my $price=$item->{'price'};
2257 my $replacementprice=$item->{'replacementprice'};
2258 my $replacementpricedate=$item->{'replacementpricedate'};
2259 my $q_replacementpricedate=$dbh->quote($replacementpricedate);
2260 my $notforloan=$item->{'notforloan'};
2261 my $itemlost=$item->{'itemlost'};
2262 my $wthdrawn=$item->{'wthdrawn'};
2263 my $restricted=$item->{'restricted'};
2264 my $itemnotes=$item->{'itemnotes'};
2265 my $q_itemnotes=$dbh->quote($itemnotes);
2266 my $itemtype=$item->{'itemtype'};
2267 my $subclass=$item->{'subclass'};
2271 unless ($env->{'marconly'}) {
2272 my $sth=$dbh->prepare("select max(itemnumber) from items");
2274 my ($itemnumber) =$sth->fetchrow;
2276 $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)");
2283 $subfields->{1}->{'Subfield_Mark'}='p';
2284 $subfields->{1}->{'Subfield_Value'}=$barcode;
2285 $subfields->{2}->{'Subfield_Mark'}='d';
2286 $subfields->{2}->{'Subfield_Value'}=$dateaccessioned;
2287 $subfields->{3}->{'Subfield_Mark'}='e';
2288 $subfields->{3}->{'Subfield_Value'}=$booksellerid;
2289 $subfields->{4}->{'Subfield_Mark'}='b';
2290 $subfields->{4}->{'Subfield_Value'}=$homebranch;
2291 $subfields->{5}->{'Subfield_Mark'}='l';
2292 $subfields->{5}->{'Subfield_Value'}=$holdingbranch;
2293 $subfields->{6}->{'Subfield_Mark'}='c';
2294 $subfields->{6}->{'Subfield_Value'}=$price;
2295 $subfields->{7}->{'Subfield_Mark'}='c';
2296 $subfields->{7}->{'Subfield_Value'}=$replacementprice;
2297 $subfields->{8}->{'Subfield_Mark'}='d';
2298 $subfields->{8}->{'Subfield_Value'}=$replacementpricedate;
2300 $subfields->{9}->{'Subfield_Mark'}='h';
2301 $subfields->{9}->{'Subfield_Value'}='Not for loan';
2304 $subfields->{10}->{'Subfield_Mark'}='j';
2305 $subfields->{10}->{'Subfield_Value'}='Item lost';
2308 $subfields->{11}->{'Subfield_Mark'}='j';
2309 $subfields->{11}->{'Subfield_Value'}='Item withdrawn';
2312 $subfields->{12}->{'Subfield_Mark'}='z';
2313 $subfields->{12}->{'Subfield_Value'}=$itemnotes;
2317 $env->{'linkage'}=1;
2318 ($env, $Tag_ID) = addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
2319 $env->{'linkage'}=0;
2320 $env->{'linkid'}=$Tag_ID;
2323 $subfields2->{1}->{'Subfield_Mark'}='a';
2324 $subfields2->{1}->{'Subfield_Value'}='Coast Mountains School District';
2325 $subfields2->{1}->{'Subfield_Mark'}='b';
2326 $subfields2->{1}->{'Subfield_Value'}=$homebranch;
2327 $subfields2->{1}->{'Subfield_Mark'}='c';
2328 $subfields2->{1}->{'Subfield_Value'}=$itemtype;
2329 $subfields2->{2}->{'Subfield_Mark'}='m';
2330 $subfields2->{2}->{'Subfield_Value'}=$subclass;
2331 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields2);
2332 $env->{'linkid'}='';
2335 sub OLD_MAYBE_DELETED_updateBiblio {
2336 # Update the biblio with biblionumber $biblio->{'biblionumber'}
2337 # I guess this routine should search through all marc records for a record that
2338 # has the same biblionumber stored in it, and modify the MARC record as well as
2341 # Also, this subroutine should search through the $biblio object and compare it
2342 # to the existing record and _LOG ALL CHANGES MADE_ in some way. I'd like for
2343 # this logging feature to be usable to undo changes easily.
2345 my ($env, $biblio) = @_;
2347 my $biblionumber=$biblio->{'biblionumber'};
2349 my $sth=$dbh->prepare("select * from biblio where biblionumber=$biblionumber");
2351 my $origbiblio=$sth->fetchrow_hashref;
2352 $sth=$dbh->prepare("select subtitle from bibliosubtitle where biblionumber=$biblionumber");
2354 my ($subtitle)=$sth->fetchrow;
2355 $origbiblio->{'subtitle'}=$subtitle;
2356 $sth=$dbh->prepare("select author from additionalauthors where biblionumber=$biblionumber");
2358 my $origadditionalauthors;
2359 while (my ($author) = $sth->fetchrow) {
2360 push (@{$origbiblio->{'additionalauthors'}}, $author);
2361 $origadditionalauthors->{$author}=1;
2363 $sth=$dbh->prepare("select subject from bibliosubject where biblionumber=$biblionumber");
2366 while (my ($subject) = $sth->fetchrow) {
2367 push (@{$origbiblio->{'subjects'}}, $subject);
2368 $origsubjects->{$subject}=1;
2372 # Obtain a list of MARC Record_ID's that are tied to this biblio
2373 $sth=$dbh->prepare("select bibid from marc_subfield_table where tag='090' and subfieldvalue=$biblionumber and subfieldcode='c'");
2376 while (my ($bibid) = $sth->fetchrow) {
2377 push(@marcrecords, $bibid);
2381 if ($biblio->{'author'} ne $origbiblio->{'author'}) {
2382 my $q_author=$dbh->quote($biblio->{'author'});
2383 logchange('kohadb', 'change', 'biblio', 'author', $origbiblio->{'author'}, $biblio->{'author'});
2384 my $sti=$dbh->prepare("update biblio set author=$q_author where biblionumber=$biblio->{'biblionumber'}");
2386 foreach $bibid (@marcrecords) {
2387 logchange('marc', 'change', $bibid, '100', 'a', $origbiblio->{'author'}, $biblio->{'author'});
2388 changeSubfield($bibid, '100', 'a', $origbiblio->{'author'}, $biblio->{'author'});
2391 if ($biblio->{'title'} ne $origbiblio->{'title'}) {
2392 my $q_title=$dbh->quote($biblio->{'title'});
2393 logchange('kohadb', 'change', 'biblio', 'title', $origbiblio->{'title'}, $biblio->{'title'});
2394 my $sti=$dbh->prepare("update biblio set title=$q_title where biblionumber=$biblio->{'biblionumber'}");
2396 foreach $Record_ID (@marcrecords) {
2397 logchange('marc', 'change', $Record_ID, '245', 'a', $origbiblio->{'title'}, $biblio->{'title'});
2398 changeSubfield($Record_ID, '245', 'a', $origbiblio->{'title'}, $biblio->{'title'});
2401 if ($biblio->{'subtitle'} ne $origbiblio->{'subtitle'}) {
2402 my $q_subtitle=$dbh->quote($biblio->{'subtitle'});
2403 logchange('kohadb', 'change', 'biblio', 'subtitle', $origbiblio->{'subtitle'}, $biblio->{'subtitle'});
2404 my $sti=$dbh->prepare("update bibliosubtitle set subtitle=$q_subtitle where biblionumber=$biblio->{'biblionumber'}");
2406 foreach $Record_ID (@marcrecords) {
2407 logchange('marc', 'change', $Record_ID, '245', 'b', $origbiblio->{'subtitle'}, $biblio->{'subtitle'});
2408 changeSubfield($Record_ID, '245', 'b', $origbiblio->{'subtitle'}, $biblio->{'subtitle'});
2411 if ($biblio->{'unititle'} ne $origbiblio->{'unititle'}) {
2412 my $q_unititle=$dbh->quote($biblio->{'unititle'});
2413 logchange('kohadb', 'change', 'biblio', 'unititle', $origbiblio->{'unititle'}, $biblio->{'unititle'});
2414 my $sti=$dbh->prepare("update biblio set unititle=$q_unititle where biblionumber=$biblio->{'biblionumber'}");
2417 if ($biblio->{'notes'} ne $origbiblio->{'notes'}) {
2418 my $q_notes=$dbh->quote($biblio->{'notes'});
2419 logchange('kohadb', 'change', 'biblio', 'notes', $origbiblio->{'notes'}, $biblio->{'notes'});
2420 my $sti=$dbh->prepare("update biblio set notes=$q_notes where biblionumber=$biblio->{'biblionumber'}");
2422 foreach $Record_ID (@marcrecords) {
2423 logchange('marc', 'change', $Record_ID, '500', 'a', $origbiblio->{'notes'}, $biblio->{'notes'});
2424 changeSubfield($Record_ID, '500', 'a', $origbiblio->{'notes'}, $biblio->{'notes'});
2427 if ($biblio->{'serial'} ne $origbiblio->{'serial'}) {
2428 my $q_serial=$dbh->quote($biblio->{'serial'});
2429 logchange('kohadb', 'change', 'biblio', 'serial', $origbiblio->{'serial'}, $biblio->{'serial'});
2430 my $sti=$dbh->prepare("update biblio set serial=$q_serial where biblionumber=$biblio->{'biblionumber'}");
2433 if ($biblio->{'seriestitle'} ne $origbiblio->{'seriestitle'}) {
2434 my $q_seriestitle=$dbh->quote($biblio->{'seriestitle'});
2435 logchange('kohadb', 'change', 'biblio', 'seriestitle', $origbiblio->{'seriestitle'}, $biblio->{'seriestitle'});
2436 my $sti=$dbh->prepare("update biblio set seriestitle=$q_seriestitle where biblionumber=$biblio->{'biblionumber'}");
2438 foreach $Record_ID (@marcrecords) {
2439 logchange('marc', 'change', $Record_ID, '440', 'a', $origbiblio->{'seriestitle'}, $biblio->{'seriestitle'});
2440 changeSubfield($Record_ID, '440', 'a', $origbiblio->{'seriestitle'}, $biblio->{'seriestitle'});
2443 if ($biblio->{'copyrightdate'} ne $origbiblio->{'copyrightdate'}) {
2444 my $q_copyrightdate=$dbh->quote($biblio->{'copyrightdate'});
2445 logchange('kohadb', 'change', 'biblio', 'copyrightdate', $origbiblio->{'copyrightdate'}, $biblio->{'copyrightdate'});
2446 my $sti=$dbh->prepare("update biblio set copyrightdate=$q_copyrightdate where biblionumber=$biblio->{'biblionumber'}");
2448 foreach $Record_ID (@marcrecords) {
2449 logchange('marc', 'change', $Record_ID, '260', 'c', "c$origbiblio->{'notes'}", "c$biblio->{'notes'}");
2450 changeSubfield($Record_ID, '260', 'c', "c$origbiblio->{'notes'}", "c$biblio->{'notes'}");
2454 # Check for subject heading changes
2458 foreach $newsubject (@{$biblio->{'subject'}}) {
2459 $subjects->{$newsubject}=1;
2460 if ($origsubjects->{$newsubject}) {
2461 $subjects->{$newsubject}=2;
2463 my $q_newsubject=$dbh->quote($newsubject);
2464 my $sth=$dbh->prepare("insert into bibliosubject (subject,biblionumber) values ($q_newsubject, $biblionumber)");
2466 logchange('kohadb', 'add', 'biblio', 'subject', $newsubject);
2468 $subfields->{1}->{'Subfield_Mark'}='a';
2469 $subfields->{1}->{'Subfield_Value'}=$newsubject;
2472 foreach $Record_ID (@marcrecords) {
2473 addTag($env, $Record_ID, $tag, ' ', ' ', $subfields);
2474 logchange('marc', 'add', $Record_ID, '650', 'a', $newsubject);
2479 foreach $origsubject (keys %$origsubjects) {
2480 if ($subjects->{$origsubject} == 1) {
2481 my $q_origsubject=$dbh->quote($origsubject);
2482 logchange('kohadb', 'delete', 'biblio', '$biblionumber', 'subject', $origsubject);
2483 my $sth=$dbh->prepare("delete from bibliosubject where biblionumber=$biblionumber and subject=$q_origsubject");
2489 sub OLD_MAYBE_DELETED_updateBiblioItem {
2490 # Update the biblioitem with biblioitemnumber $biblioitem->{'biblioitemnumber'}
2492 # This routine should also check to see which fields are actually being
2493 # modified, and log all changes.
2495 my ($env, $biblioitem) = @_;
2498 my $biblioitemnumber=$biblioitem->{'biblioitemnumber'};
2499 my $sth=$dbh->prepare("select * from biblioitems where biblioitemnumber=$biblioitemnumber");
2500 # obi = original biblioitem
2501 my $obi=$sth->fetchrow_hashref;
2502 $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");
2504 my ($Record_ID) = $sth->fetchrow;
2505 if ($biblioitem->{'biblionumber'} ne $obi->{'biblionumber'}) {
2506 logchange('kohadb', 'change', 'biblioitems', 'biblionumber', $obi->{'biblionumber'}, $biblioitem->{'biblionumber'});
2507 my $sth=$dbh->prepare("update biblioitems set biblionumber=$biblioitem->{'biblionumber'} where biblioitemnumber=$biblioitemnumber");
2508 logchange('marc', 'change', $Record_ID, '090', 'c', $obi->{'biblionumber'}, $biblioitem->{'biblionumber'});
2509 changeSubfield($Record_ID, '090', 'c', $obi->{'biblionumber'}, $biblioitem->{'biblionumber'});
2511 if ($biblioitem->{'volume'} ne $obi->{'volume'}) {
2512 logchange('kohadb', 'change', 'biblioitems', 'volume', $obi->{'volume'}, $biblioitem->{'volume'});
2513 my $q_volume=$dbh->quote($biblioitem->{'volume'});
2514 my $sth=$dbh->prepare("update biblioitems set volume=$q_volume where biblioitemnumber=$biblioitemnumber");
2515 logchange('marc', 'change', $Record_ID, '440', 'v', $obi->{'volume'}, $biblioitem->{'volume'});
2516 changeSubfield($Record_ID, '440', 'v', $obi->{'volume'}, $biblioitem->{'volume'});
2518 if ($biblioitem->{'number'} ne $obi->{'number'}) {
2519 logchange('kohadb', 'change', 'biblioitems', 'number', $obi->{'number'}, $biblioitem->{'number'});
2520 my $q_number=$dbh->quote($biblioitem->{'number'});
2521 my $sth=$dbh->prepare("update biblioitems set number=$q_number where biblioitemnumber=$biblioitemnumber");
2522 logchange('marc', 'change', $Record_ID, '440', 'v', $obi->{'number'}, $biblioitem->{'number'});
2523 changeSubfield($Record_ID, '440', 'v', $obi->{'number'}, $biblioitem->{'number'});
2525 if ($biblioitem->{'itemtype'} ne $obi->{'itemtype'}) {
2526 logchange('kohadb', 'change', 'biblioitems', 'itemtype', $obi->{'itemtype'}, $biblioitem->{'itemtype'});
2527 my $q_itemtype=$dbh->quote($biblioitem->{'itemtype'});
2528 my $sth=$dbh->prepare("update biblioitems set itemtype=$q_itemtype where biblioitemnumber=$biblioitemnumber");
2529 logchange('marc', 'change', $Record_ID, '090', 'a', $obi->{'itemtype'}, $biblioitem->{'itemtype'});
2530 changeSubfield($Record_ID, '090', 'a', $obi->{'itemtype'}, $biblioitem->{'itemtype'});
2532 if ($biblioitem->{'isbn'} ne $obi->{'isbn'}) {
2533 logchange('kohadb', 'change', 'biblioitems', 'isbn', $obi->{'isbn'}, $biblioitem->{'isbn'});
2534 my $q_isbn=$dbh->quote($biblioitem->{'isbn'});
2535 my $sth=$dbh->prepare("update biblioitems set isbn=$q_isbn where biblioitemnumber=$biblioitemnumber");
2536 logchange('marc', 'change', $Record_ID, '020', 'a', $obi->{'isbn'}, $biblioitem->{'isbn'});
2537 changeSubfield($Record_ID, '020', 'a', $obi->{'isbn'}, $biblioitem->{'isbn'});
2539 if ($biblioitem->{'issn'} ne $obi->{'issn'}) {
2540 logchange('kohadb', 'change', 'biblioitems', 'issn', $obi->{'issn'}, $biblioitem->{'issn'});
2541 my $q_issn=$dbh->quote($biblioitem->{'issn'});
2542 my $sth=$dbh->prepare("update biblioitems set issn=$q_issn where biblioitemnumber=$biblioitemnumber");
2543 logchange('marc', 'change', $Record_ID, '022', 'a', $obi->{'issn'}, $biblioitem->{'issn'});
2544 changeSubfield($Record_ID, '022', 'a', $obi->{'issn'}, $biblioitem->{'issn'});
2546 if ($biblioitem->{'dewey'} ne $obi->{'dewey'}) {
2547 logchange('kohadb', 'change', 'biblioitems', 'dewey', $obi->{'dewey'}, $biblioitem->{'dewey'});
2548 my $sth=$dbh->prepare("update biblioitems set dewey=$biblioitem->{'dewey'} where biblioitemnumber=$biblioitemnumber");
2549 logchange('marc', 'change', $Record_ID, '082', 'a', $obi->{'dewey'}, $biblioitem->{'dewey'});
2550 changeSubfield($Record_ID, '082', 'a', $obi->{'dewey'}, $biblioitem->{'dewey'});
2552 if ($biblioitem->{'subclass'} ne $obi->{'subclass'}) {
2553 logchange('kohadb', 'change', 'biblioitems', 'subclass', $obi->{'subclass'}, $biblioitem->{'subclass'});
2554 my $q_subclass=$dbh->quote($biblioitem->{'subclass'});
2555 my $sth=$dbh->prepare("update biblioitems set subclass=$q_subclass where biblioitemnumber=$biblioitemnumber");
2556 logchange('marc', 'change', $Record_ID, '090', 'b', $obi->{'subclass'}, $biblioitem->{'subclass'});
2557 changeSubfield($Record_ID, '090', 'b', $obi->{'subclass'}, $biblioitem->{'subclass'});
2559 if ($biblioitem->{'place'} ne $obi->{'place'}) {
2560 logchange('kohadb', 'change', 'biblioitems', 'place', $obi->{'place'}, $biblioitem->{'place'});
2561 my $q_place=$dbh->quote($biblioitem->{'place'});
2562 my $sth=$dbh->prepare("update biblioitems set place=$q_place where biblioitemnumber=$biblioitemnumber");
2563 logchange('marc', 'change', $Record_ID, '260', 'a', $obi->{'place'}, $biblioitem->{'place'});
2564 changeSubfield($Record_ID, '260', 'a', $obi->{'place'}, $biblioitem->{'place'});
2566 if ($biblioitem->{'publishercode'} ne $obi->{'publishercode'}) {
2567 logchange('kohadb', 'change', 'biblioitems', 'publishercode', $obi->{'publishercode'}, $biblioitem->{'publishercode'});
2568 my $q_publishercode=$dbh->quote($biblioitem->{'publishercode'});
2569 my $sth=$dbh->prepare("update biblioitems set publishercode=$q_publishercode where biblioitemnumber=$biblioitemnumber");
2570 logchange('marc', 'change', $Record_ID, '260', 'b', $obi->{'publishercode'}, $biblioitem->{'publishercode'});
2571 changeSubfield($Record_ID, '260', 'b', $obi->{'publishercode'}, $biblioitem->{'publishercode'});
2573 if ($biblioitem->{'publicationyear'} ne $obi->{'publicationyear'}) {
2574 logchange('kohadb', 'change', 'biblioitems', 'publicationyear', $obi->{'publicationyear'}, $biblioitem->{'publicationyear'});
2575 my $q_publicationyear=$dbh->quote($biblioitem->{'publicationyear'});
2576 my $sth=$dbh->prepare("update biblioitems set publicationyear=$q_publicationyear where biblioitemnumber=$biblioitemnumber");
2577 logchange('marc', 'change', $Record_ID, '260', 'c', $obi->{'publicationyear'}, $biblioitem->{'publicationyear'});
2578 changeSubfield($Record_ID, '260', 'c', $obi->{'publicationyear'}, $biblioitem->{'publicationyear'});
2580 if ($biblioitem->{'illus'} ne $obi->{'illus'}) {
2581 logchange('kohadb', 'change', 'biblioitems', 'illus', $obi->{'illus'}, $biblioitem->{'illus'});
2582 my $q_illus=$dbh->quote($biblioitem->{'illus'});
2583 my $sth=$dbh->prepare("update biblioitems set illus=$q_illus where biblioitemnumber=$biblioitemnumber");
2584 logchange('marc', 'change', $Record_ID, '700', 'a', $obi->{'illus'}, $biblioitem->{'illus'});
2585 changeSubfield($Record_ID, '700', 'a', $obi->{'illus'}, $biblioitem->{'illus'});
2587 if ($biblioitem->{'pages'} ne $obi->{'pages'}) {
2588 logchange('kohadb', 'change', 'biblioitems', 'pages', $obi->{'pages'}, $biblioitem->{'pages'});
2589 my $q_pages=$dbh->quote($biblioitem->{'pages'});
2590 my $sth=$dbh->prepare("update biblioitems set pages=$q_pages where biblioitemnumber=$biblioitemnumber");
2591 logchange('marc', 'change', $Record_ID, '300', 'a', $obi->{'pages'}, $biblioitem->{'pages'});
2592 changeSubfield($Record_ID, '300', 'a', $obi->{'pages'}, $biblioitem->{'pages'});
2594 if ($biblioitem->{'size'} ne $obi->{'size'}) {
2595 logchange('kohadb', 'change', 'biblioitems', 'size', $obi->{'size'}, $biblioitem->{'size'});
2596 my $q_size=$dbh->quote($biblioitem->{'size'});
2597 my $sth=$dbh->prepare("update biblioitems set size=$q_size where biblioitemnumber=$biblioitemnumber");
2598 logchange('marc', 'change', $Record_ID, '300', 'c', $obi->{'size'}, $biblioitem->{'size'});
2599 changeSubfield($Record_ID, '300', 'c', $obi->{'size'}, $biblioitem->{'size'});
2601 if ($biblioitem->{'notes'} ne $obi->{'notes'}) {
2602 logchange('kohadb', 'change', 'biblioitems', 'notes', $obi->{'notes'}, $biblioitem->{'notes'});
2603 my $q_notes=$dbh->quote($biblioitem->{'notes'});
2604 my $sth=$dbh->prepare("update biblioitems set notes=$q_notes where biblioitemnumber=$biblioitemnumber");
2605 logchange('marc', 'change', $Record_ID, '500', 'a', $obi->{'notes'}, $biblioitem->{'notes'});
2606 changeSubfield($Record_ID, '500', 'a', $obi->{'notes'}, $biblioitem->{'notes'});
2608 if ($biblioitem->{'lccn'} ne $obi->{'lccn'}) {
2609 logchange('kohadb', 'change', 'biblioitems', 'lccn', $obi->{'lccn'}, $biblioitem->{'lccn'});
2610 my $q_lccn=$dbh->quote($biblioitem->{'lccn'});
2611 my $sth=$dbh->prepare("update biblioitems set lccn=$q_lccn where biblioitemnumber=$biblioitemnumber");
2612 logchange('marc', 'change', $Record_ID, '010', 'a', $obi->{'lccn'}, $biblioitem->{'lccn'});
2613 changeSubfield($Record_ID, '010', 'a', $obi->{'lccn'}, $biblioitem->{'lccn'});
2620 sub OLD_MAYBE_DELETED_updateItem {
2621 # Update the item with itemnumber $item->{'itemnumber'}
2622 # This routine should also modify the corresponding MARC record data. (852 and
2623 # 876 tags with 876p tag the same as $item->{'barcode'}
2625 # This routine should also check to see which fields are actually being
2626 # modified, and log all changes.
2628 my ($env, $item) = @_;
2630 my $itemnumber=$item->{'itemnumber'};
2631 my $biblionumber=$item->{'biblionumber'};
2632 my $biblioitemnumber=$item->{'biblioitemnumber'};
2633 my $barcode=$item->{'barcode'};
2634 my $dateaccessioned=$item->{'dateaccessioned'};
2635 my $booksellerid=$item->{'booksellerid'};
2636 my $homebranch=$item->{'homebranch'};
2637 my $price=$item->{'price'};
2638 my $replacementprice=$item->{'replacementprice'};
2639 my $replacementpricedate=$item->{'replacementpricedate'};
2640 my $multivolume=$item->{'multivolume'};
2641 my $stack=$item->{'stack'};
2642 my $notforloan=$item->{'notforloan'};
2643 my $itemlost=$item->{'itemlost'};
2644 my $wthdrawn=$item->{'wthdrawn'};
2645 my $bulk=$item->{'bulk'};
2646 my $restricted=$item->{'restricted'};
2647 my $binding=$item->{'binding'};
2648 my $itemnotes=$item->{'itemnotes'};
2649 my $holdingbranch=$item->{'holdingbranch'};
2650 my $interim=$item->{'interim'};
2651 my $sth=$dbh->prepare("select * from items where itemnumber=$itemnumber");
2653 my $olditem=$sth->fetchrow_hashref;
2654 my $q_barcode=$dbh->quote($olditem->{'barcode'});
2655 $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");
2657 my ($Subfield876_ID, $Record_ID) = $sth->fetchrow;
2658 $sth=$dbh->prepare("select Subfield_Value from 8XX_Subfield_Table where Subfield_Mark=8 and Subfield_ID=$Subfield876_ID");
2660 my ($link) = $sth->fetchrow;
2661 $sth=$dbh->prepare("select Subfield_ID from 8XX_Subfield_Table where Subfield_Mark=8 and Subfield_Value=$link and !(Subfield_ID=$Subfield876_ID)");
2663 my ($Subfield852_ID) = $sth->fetchrow;
2665 if ($item->{'barcode'} ne $olditem->{'barcode'}) {
2666 logchange('kohadb', 'change', 'items', 'barcode', $olditem->{'barcode'}, $item->{'barcode'});
2667 my $q_barcode=$dbh->quote($item->{'barcode'});
2668 my $sth=$dbh->prepare("update items set barcode=$q_barcode where itemnumber=$itemnumber");
2670 my ($Subfield_ID, $Subfield_Key) = changeSubfield($Record_ID, '876', 'p', $olditem->{'barcode'}, $item->{'barcode'}, $Subfield876_ID);
2671 logchange('marc', 'change', $Record_ID, '876', 'p', $Subfield_Key, $olditem->{'barcode'}, $item->{'barcode'});
2673 if ($item->{'booksellerid'} ne $olditem->{'booksellerid'}) {
2674 logchange('kohadb', 'change', 'items', 'booksellerid', $olditem->{'booksellerid'}, $item->{'booksellerid'});
2675 my $q_booksellerid=$dbh->quote($item->{'booksellerid'});
2676 my $sth=$dbh->prepare("update items set booksellerid=$q_booksellerid where itemnumber=$itemnumber");
2678 my ($Subfield_ID, $Subfield_Key) = changeSubfield($Record_ID, '876', 'e', $olditem->{'booksellerid'}, $item->{'booksellerid'}, $Subfield876_ID);
2679 logchange('marc', 'change', $Record_ID, '876', 'e', $Subfield_Key, $olditem->{'booksellerid'}, $item->{'booksellerid'});
2681 if ($item->{'dateaccessioned'} ne $olditem->{'dateaccessioned'}) {
2682 logchange('kohadb', 'change', 'items', 'dateaccessioned', $olditem->{'dateaccessioned'}, $item->{'dateaccessioned'});
2683 my $q_dateaccessioned=$dbh->quote($item->{'dateaccessioned'});
2684 my $sth=$dbh->prepare("update items set dateaccessioned=$q_dateaccessioned where itemnumber=$itemnumber");
2686 my ($Subfield_ID, $Subfield_Key) = changeSubfield($Record_ID, '876', 'd', $olditem->{'dateaccessioned'}, $item->{'dateaccessioned'}, $Subfield876_ID);
2687 logchange('marc', 'change', $Record_ID, '876', 'd', $Subfield_Key, $olditem->{'dateaccessioned'}, $item->{'dateaccessioned'});
2689 if ($item->{'homebranch'} ne $olditem->{'homebranch'}) {
2690 logchange('kohadb', 'change', 'items', 'homebranch', $olditem->{'homebranch'}, $item->{'homebranch'});
2691 my $q_homebranch=$dbh->quote($item->{'homebranch'});
2692 my $sth=$dbh->prepare("update items set homebranch=$q_homebranch where itemnumber=$itemnumber");
2694 my ($Subfield_ID, $Subfield_Key) = changeSubfield($Record_ID, '876', 'b', $olditem->{'homebranch'}, $item->{'homebranch'}, $Subfield876_ID);
2695 logchange('marc', 'change', $Record_ID, '876', 'b', $Subfield_Key, $olditem->{'homebranch'}, $item->{'homebranch'});
2697 if ($item->{'holdingbranch'} ne $olditem->{'holdingbranch'}) {
2698 logchange('kohadb', 'change', 'items', 'holdingbranch', $olditem->{'holdingbranch'}, $item->{'holdingbranch'});
2699 my $q_holdingbranch=$dbh->quote($item->{'holdingbranch'});
2700 my $sth=$dbh->prepare("update items set holdingbranch=$q_holdingbranch where itemnumber=$itemnumber");
2702 my ($Subfield_ID, $Subfield_Key) = changeSubfield($Record_ID, '876', 'l', $olditem->{'holdingbranch'}, $item->{'holdingbranch'}, $Subfield876_ID);
2703 logchange('marc', 'change', $Record_ID, '876', 'l', $Subfield_Key, $olditem->{'holdingbranch'}, $item->{'holdingbranch'});
2705 if ($item->{'price'} ne $olditem->{'price'}) {
2706 logchange('kohadb', 'change', 'items', 'price', $olditem->{'price'}, $item->{'price'});
2707 my $q_price=$dbh->quote($item->{'price'});
2708 my $sth=$dbh->prepare("update items set price=$q_price where itemnumber=$itemnumber");
2710 my ($Subfield_ID, $Subfield_Key) = changeSubfield($Record_ID, '876', 'c', $olditem->{'price'}, $item->{'price'}, $Subfield876_ID);
2711 logchange('marc', 'change', $Record_ID, '876', 'c', $Subfield_Key, $olditem->{'price'}, $item->{'price'});
2713 if ($item->{'itemnotes'} ne $olditem->{'itemnotes'}) {
2714 logchange('kohadb', 'change', 'items', 'itemnotes', $olditem->{'itemnotes'}, $item->{'itemnotes'});
2715 my $q_itemnotes=$dbh->quote($item->{'itemnotes'});
2716 my $sth=$dbh->prepare("update items set itemnotes=$q_itemnotes where itemnumber=$itemnumber");
2718 my ($Subfield_ID, $Subfield_Key) = changeSubfield($Record_ID, '876', 'c', $olditem->{'itemnotes'}, $item->{'itemnotes'}, $Subfield876_ID);
2719 logchange('marc', 'change', $Record_ID, '876', 'c', $Subfield_Key, $olditem->{'itemnotes'}, $item->{'itemnotes'});
2721 if ($item->{'notforloan'} ne $olditem->{'notforloan'}) {
2722 logchange('kohadb', 'change', 'items', 'notforloan', $olditem->{'notforloan'}, $item->{'notforloan'});
2723 my $sth=$dbh->prepare("update items set notforloan=$notforloan where itemnumber=$itemnumber");
2725 if ($item->{'notforloan'}) {
2726 my ($Subfield_ID, $Subfield_Key) = addSubfield($Record_ID, '876', 'h', 'Not for loan', $Subfield876_ID);
2727 logchange('marc', 'add', $Record_ID, '876', 'h', $Subfield_Key, 'Not for loan');
2729 my ($Subfield_ID, $Subfield_Key) = deleteSubfield($Record_ID, '876', 'h', 'Not for loan', $Subfield876_ID);
2730 logchange('marc', 'delete', $Record_ID, '876', 'h', $Subfield_Key, 'Not for loan');
2733 if ($item->{'itemlost'} ne $olditem->{'itemlost'}) {
2734 logchange('kohadb', 'change', 'items', 'itemlost', $olditem->{'itemlost'}, $item->{'itemlost'});
2735 my $sth=$dbh->prepare("update items set itemlost=$itemlost where itemnumber=$itemnumber");
2737 if ($item->{'itemlost'}) {
2738 my ($Subfield_ID, $Subfield_Key) = addSubfield($Record_ID, '876', 'h', 'Item lost', $Subfield876_ID);
2739 logchange('marc', 'add', $Record_ID, '876', 'h', $Subfield_Key, 'Item lost');
2741 my ($Subfield_ID, $Subfield_Key) = deleteSubfield($Record_ID, '876', 'h', 'Item lost', $Subfield876_ID);
2742 logchange('marc', 'delete', $Record_ID, '876', 'h', $Subfield_Key, 'Item lost');
2745 if ($item->{'wthdrawn'} ne $olditem->{'wthdrawn'}) {
2746 logchange('kohadb', 'change', 'items', 'wthdrawn', $olditem->{'wthdrawn'}, $item->{'wthdrawn'});
2747 my $sth=$dbh->prepare("update items set wthdrawn=$wthdrawn where itemnumber=$itemnumber");
2749 if ($item->{'wthdrawn'}) {
2750 my ($Subfield_ID, $Subfield_Key) = addSubfield($Record_ID, '876', 'h', 'Withdrawn', $Subfield876_ID);
2751 logchange('marc', 'add', $Record_ID, '876', 'h', $Subfield_Key, 'Withdrawn');
2753 my ($Subfield_ID, $Subfield_Key) = deleteSubfield($Record_ID, '876', 'h', 'Withdrawn', $Subfield876_ID);
2754 logchange('marc', 'delete', $Record_ID, '876', 'h', $Subfield_Key, 'Withdrawn');
2757 if ($item->{'restricted'} ne $olditem->{'restricted'}) {
2758 logchange('kohadb', 'change', 'items', 'restricted', $olditem->{'restricted'}, $item->{'restricted'});
2759 my $sth=$dbh->prepare("update items set restricted=$restricted where itemnumber=$itemnumber");
2761 if ($item->{'restricted'}) {
2762 my ($Subfield_ID, $Subfield_Key) = addSubfield($Record_ID, '876', 'h', 'Restricted', $Subfield876_ID);
2763 logchange('marc', 'add', $Record_ID, '876', 'h', $Subfield_Key, 'Restricted');
2765 my ($Subfield_ID, $Subfield_Key) = deleteSubfield($Record_ID, '876', 'h', 'Restricted', $Subfield876_ID);
2766 logchange('marc', 'delete', $Record_ID, '876', 'h', $Subfield_Key, 'Restricted');
2773 # Add a biblioitem and related data to Koha database
2774 sub OLD_MAY_BE_DELETED_newcompletebiblioitem {
2779 $biblio, # hash ref to biblio record
2780 $biblioitem, # hash ref to biblioitem record
2781 $subjects, # list ref of subjects
2782 $addlauthors, # list ref of additional authors
2785 my ( $biblionumber, $biblioitemnumber, $error); # return values
2790 my $additionalauthor;
2793 requireDBI($dbh,"newcompletebiblioitem");
2795 print "<PRE>Trying to add biblio item Title=$biblio->{title} " .
2796 "ISBN=$biblioitem->{isbn} </PRE>\n" if $debug;
2798 # Make sure master biblio entry exists
2799 ($biblionumber,$error)=getoraddbiblio($dbh, $biblio);
2803 $biblioitem->{biblionumber}=$biblionumber;
2806 $biblioitemnumber=newbiblioitem($biblioitem);
2809 $sth=$dbh->prepare("insert into bibliosubject
2810 (biblionumber,subject)
2812 foreach $subjectheading (@{$subjects} ) {
2813 $sth->execute($biblionumber, $subjectheading)
2814 or $error.=$sth->errstr ;
2818 # Add additional authors
2819 $sth=$dbh->prepare("insert into additionalauthors
2820 (biblionumber,author)
2822 foreach $additionalauthor (@{$addlauthors} ) {
2823 $sth->execute($biblionumber, $additionalauthor)
2824 or $error.=$sth->errstr ;
2828 # couldn't get biblio
2830 $biblioitemnumber='';
2832 } # if no biblio error
2834 return ( $biblionumber, $biblioitemnumber, $error);
2836 } # sub newcompletebiblioitem
2840 # END OF UNUSEFUL SUBs
2844 END { } # module clean-up code here (global destructor)