1 package C4::ImportBatch;
3 # Copyright (C) 2007 LibLime, 2012 C & P Bibliography Services
5 # This file is part of Koha.
7 # Koha is free software; you can redistribute it and/or modify it under the
8 # terms of the GNU General Public License as published by the Free Software
9 # Foundation; either version 2 of the License, or (at your option) any later
12 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
13 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
14 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
16 # You should have received a copy of the GNU General Public License along
17 # with Koha; if not, write to the Free Software Foundation, Inc.,
18 # 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
28 use C4::AuthoritiesMarc;
30 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
33 # set the version for version checking
34 $VERSION = 3.07.00.049;
41 GetImportRecordMarcXML
46 AddItemsToImportBiblio
57 GetStagedWebserviceBatches
58 GetImportBatchRangeDesc
59 GetNumberOfNonZ3950ImportBatches
61 GetItemNumbersFromImportBatch
65 GetImportBatchOverlayAction
66 SetImportBatchOverlayAction
67 GetImportBatchNoMatchAction
68 SetImportBatchNoMatchAction
69 GetImportBatchItemAction
70 SetImportBatchItemAction
73 GetImportRecordOverlayStatus
74 SetImportRecordOverlayStatus
77 GetImportRecordMatches
78 SetImportRecordMatches
84 C4::ImportBatch - manage batches of imported MARC records
92 =head2 GetZ3950BatchId
94 my $batchid = GetZ3950BatchId($z3950server);
96 Retrieves the ID of the import batch for the Z39.50
97 reservoir for the given target. If necessary,
98 creates the import batch.
102 sub GetZ3950BatchId {
103 my ($z3950server) = @_;
105 my $dbh = C4::Context->dbh;
106 my $sth = $dbh->prepare("SELECT import_batch_id FROM import_batches
107 WHERE batch_type = 'z3950'
109 $sth->execute($z3950server);
110 my $rowref = $sth->fetchrow_arrayref();
112 if (defined $rowref) {
115 my $batch_id = AddImportBatch( {
116 overlay_action => 'create_new',
117 import_status => 'staged',
118 batch_type => 'z3950',
119 file_name => $z3950server,
126 =head2 GetWebserviceBatchId
128 my $batchid = GetWebserviceBatchId();
130 Retrieves the ID of the import batch for webservice.
131 If necessary, creates the import batch.
135 my $WEBSERVICE_BASE_QRY = <<EOQ;
136 SELECT import_batch_id FROM import_batches
137 WHERE batch_type = 'webservice'
138 AND import_status = 'staged'
140 sub GetWebserviceBatchId {
143 my $dbh = C4::Context->dbh;
144 my $sql = $WEBSERVICE_BASE_QRY;
146 foreach my $field (qw(matcher_id overlay_action nomatch_action item_action)) {
147 if (my $val = $params->{$field}) {
148 $sql .= " AND $field = ?";
152 my $id = $dbh->selectrow_array($sql, undef, @args);
155 $params->{batch_type} = 'webservice';
156 $params->{import_status} = 'staged';
157 return AddImportBatch($params);
160 =head2 GetImportRecordMarc
162 my ($marcblob, $encoding) = GetImportRecordMarc($import_record_id);
166 sub GetImportRecordMarc {
167 my ($import_record_id) = @_;
169 my $dbh = C4::Context->dbh;
170 my $sth = $dbh->prepare("SELECT marc, encoding FROM import_records WHERE import_record_id = ?");
171 $sth->execute($import_record_id);
172 my ($marc, $encoding) = $sth->fetchrow();
174 return $marc, $encoding;
178 =head2 GetImportRecordMarcXML
180 my $marcxml = GetImportRecordMarcXML($import_record_id);
184 sub GetImportRecordMarcXML {
185 my ($import_record_id) = @_;
187 my $dbh = C4::Context->dbh;
188 my $sth = $dbh->prepare("SELECT marcxml FROM import_records WHERE import_record_id = ?");
189 $sth->execute($import_record_id);
190 my ($marcxml) = $sth->fetchrow();
196 =head2 AddImportBatch
198 my $batch_id = AddImportBatch($params_hash);
206 foreach (qw( matcher_id template_id branchcode
207 overlay_action nomatch_action item_action
208 import_status batch_type file_name comments record_type )) {
209 if (exists $params->{$_}) {
211 push @vals, $params->{$_};
214 my $dbh = C4::Context->dbh;
215 $dbh->do("INSERT INTO import_batches (".join( ',', @fields).")
216 VALUES (".join( ',', map '?', @fields).")",
219 return $dbh->{'mysql_insertid'};
222 =head2 GetImportBatch
224 my $row = GetImportBatch($batch_id);
226 Retrieve a hashref of an import_batches row.
233 my $dbh = C4::Context->dbh;
234 my $sth = $dbh->prepare_cached("SELECT * FROM import_batches WHERE import_batch_id = ?");
235 $sth->bind_param(1, $batch_id);
237 my $result = $sth->fetchrow_hashref;
243 =head2 AddBiblioToBatch
245 my $import_record_id = AddBiblioToBatch($batch_id, $record_sequence,
246 $marc_record, $encoding, $z3950random, $update_counts);
250 sub AddBiblioToBatch {
251 my $batch_id = shift;
252 my $record_sequence = shift;
253 my $marc_record = shift;
254 my $encoding = shift;
255 my $z3950random = shift;
256 my $update_counts = @_ ? shift : 1;
258 my $import_record_id = _create_import_record($batch_id, $record_sequence, $marc_record, 'biblio', $encoding, $z3950random);
259 _add_biblio_fields($import_record_id, $marc_record);
260 _update_batch_record_counts($batch_id) if $update_counts;
261 return $import_record_id;
264 =head2 ModBiblioInBatch
266 ModBiblioInBatch($import_record_id, $marc_record);
270 sub ModBiblioInBatch {
271 my ($import_record_id, $marc_record) = @_;
273 _update_import_record_marc($import_record_id, $marc_record);
274 _update_biblio_fields($import_record_id, $marc_record);
278 =head2 AddAuthToBatch
280 my $import_record_id = AddAuthToBatch($batch_id, $record_sequence,
281 $marc_record, $encoding, $z3950random, $update_counts);
286 my $batch_id = shift;
287 my $record_sequence = shift;
288 my $marc_record = shift;
289 my $encoding = shift;
290 my $z3950random = shift;
291 my $update_counts = @_ ? shift : 1;
293 my $import_record_id = _create_import_record($batch_id, $record_sequence, $marc_record, 'auth', $encoding, $z3950random);
294 _add_auth_fields($import_record_id, $marc_record);
295 _update_batch_record_counts($batch_id) if $update_counts;
296 return $import_record_id;
299 =head2 ModAuthInBatch
301 ModAuthInBatch($import_record_id, $marc_record);
306 my ($import_record_id, $marc_record) = @_;
308 _update_import_record_marc($import_record_id, $marc_record);
312 =head2 BatchStageMarcRecords
314 ($batch_id, $num_records, $num_items, @invalid_records) =
315 BatchStageMarcRecords($record_type, $encoding, $marc_records, $file_name,
316 $comments, $branch_code, $parse_items,
318 $progress_interval, $progress_callback);
322 sub BatchStageMarcRecords {
323 my $record_type = shift;
324 my $encoding = shift;
325 my $marc_records = shift;
326 my $file_name = shift;
327 my $comments = shift;
328 my $branch_code = shift;
329 my $parse_items = shift;
330 my $leave_as_staging = shift;
332 # optional callback to monitor status
334 my $progress_interval = 0;
335 my $progress_callback = undef;
337 $progress_interval = shift;
338 $progress_callback = shift;
339 $progress_interval = 0 unless $progress_interval =~ /^\d+$/ and $progress_interval > 0;
340 $progress_interval = 0 unless 'CODE' eq ref $progress_callback;
343 my $batch_id = AddImportBatch( {
344 overlay_action => 'create_new',
345 import_status => 'staging',
346 batch_type => 'batch',
347 file_name => $file_name,
348 comments => $comments,
349 record_type => $record_type,
352 SetImportBatchItemAction($batch_id, 'always_add');
354 SetImportBatchItemAction($batch_id, 'ignore');
357 my @invalid_records = ();
360 # FIXME - for now, we're dealing only with bibs
362 foreach my $marc_blob (split(/\x1D/, $marc_records)) {
363 $marc_blob =~ s/^\s+//g;
364 $marc_blob =~ s/\s+$//g;
365 next unless $marc_blob;
367 if ($progress_interval and (0 == ($rec_num % $progress_interval))) {
368 &$progress_callback($rec_num);
370 my ($marc_record, $charset_guessed, $char_errors) =
371 MarcToUTF8Record($marc_blob, C4::Context->preference("marcflavour"), $encoding);
373 $encoding = $charset_guessed unless $encoding;
375 my $import_record_id;
376 if (scalar($marc_record->fields()) == 0) {
377 push @invalid_records, $marc_blob;
380 if ($record_type eq 'biblio') {
381 $import_record_id = AddBiblioToBatch($batch_id, $rec_num, $marc_record, $encoding, int(rand(99999)), 0);
383 my @import_items_ids = AddItemsToImportBiblio($batch_id, $import_record_id, $marc_record, 0);
384 $num_items += scalar(@import_items_ids);
386 } elsif ($record_type eq 'auth') {
387 $import_record_id = AddAuthToBatch($batch_id, $rec_num, $marc_record, $encoding, int(rand(99999)), 0);
391 unless ($leave_as_staging) {
392 SetImportBatchStatus($batch_id, 'staged');
394 # FIXME branch_code, number of bibs, number of items
395 _update_batch_record_counts($batch_id);
396 return ($batch_id, $num_valid, $num_items, @invalid_records);
399 =head2 AddItemsToImportBiblio
401 my @import_items_ids = AddItemsToImportBiblio($batch_id,
402 $import_record_id, $marc_record, $update_counts);
406 sub AddItemsToImportBiblio {
407 my $batch_id = shift;
408 my $import_record_id = shift;
409 my $marc_record = shift;
410 my $update_counts = @_ ? shift : 0;
412 my @import_items_ids = ();
414 my $dbh = C4::Context->dbh;
415 my ($item_tag,$item_subfield) = &GetMarcFromKohaField("items.itemnumber",'');
416 foreach my $item_field ($marc_record->field($item_tag)) {
417 my $item_marc = MARC::Record->new();
418 $item_marc->leader("00000 a "); # must set Leader/09 to 'a'
419 $item_marc->append_fields($item_field);
420 $marc_record->delete_field($item_field);
421 my $sth = $dbh->prepare_cached("INSERT INTO import_items (import_record_id, status, marcxml)
423 $sth->bind_param(1, $import_record_id);
424 $sth->bind_param(2, 'staged');
425 $sth->bind_param(3, $item_marc->as_xml());
427 push @import_items_ids, $dbh->{'mysql_insertid'};
431 if ($#import_items_ids > -1) {
432 _update_batch_record_counts($batch_id) if $update_counts;
433 _update_import_record_marc($import_record_id, $marc_record);
435 return @import_items_ids;
438 =head2 BatchFindDuplicates
440 my $num_with_matches = BatchFindDuplicates($batch_id, $matcher,
441 $max_matches, $progress_interval, $progress_callback);
443 Goes through the records loaded in the batch and attempts to
444 find duplicates for each one. Sets the matching status
445 of each record to "no_match" or "auto_match" as appropriate.
447 The $max_matches parameter is optional; if it is not supplied,
450 The $progress_interval and $progress_callback parameters are
451 optional; if both are supplied, the sub referred to by
452 $progress_callback will be invoked every $progress_interval
453 records using the number of records processed as the
458 sub BatchFindDuplicates {
459 my $batch_id = shift;
461 my $max_matches = @_ ? shift : 10;
463 # optional callback to monitor status
465 my $progress_interval = 0;
466 my $progress_callback = undef;
468 $progress_interval = shift;
469 $progress_callback = shift;
470 $progress_interval = 0 unless $progress_interval =~ /^\d+$/ and $progress_interval > 0;
471 $progress_interval = 0 unless 'CODE' eq ref $progress_callback;
474 my $dbh = C4::Context->dbh;
476 my $sth = $dbh->prepare("SELECT import_record_id, record_type, marc
478 WHERE import_batch_id = ?");
479 $sth->execute($batch_id);
480 my $num_with_matches = 0;
482 while (my $rowref = $sth->fetchrow_hashref) {
484 if ($progress_interval and (0 == ($rec_num % $progress_interval))) {
485 &$progress_callback($rec_num);
487 my $marc_record = MARC::Record->new_from_usmarc($rowref->{'marc'});
489 if (defined $matcher) {
490 @matches = $matcher->get_matches($marc_record, $max_matches);
492 if (scalar(@matches) > 0) {
494 SetImportRecordMatches($rowref->{'import_record_id'}, @matches);
495 SetImportRecordOverlayStatus($rowref->{'import_record_id'}, 'auto_match');
497 SetImportRecordMatches($rowref->{'import_record_id'}, ());
498 SetImportRecordOverlayStatus($rowref->{'import_record_id'}, 'no_match');
502 return $num_with_matches;
505 =head2 BatchCommitRecords
507 my ($num_added, $num_updated, $num_items_added, $num_items_errored, $num_ignored) =
508 BatchCommitRecords($batch_id, $framework,
509 $progress_interval, $progress_callback);
513 sub BatchCommitRecords {
514 my $batch_id = shift;
515 my $framework = shift;
517 # optional callback to monitor status
519 my $progress_interval = 0;
520 my $progress_callback = undef;
522 $progress_interval = shift;
523 $progress_callback = shift;
524 $progress_interval = 0 unless $progress_interval =~ /^\d+$/ and $progress_interval > 0;
525 $progress_interval = 0 unless 'CODE' eq ref $progress_callback;
531 my $num_items_added = 0;
532 my $num_items_errored = 0;
534 # commit (i.e., save, all records in the batch)
535 SetImportBatchStatus('importing');
536 my $overlay_action = GetImportBatchOverlayAction($batch_id);
537 my $nomatch_action = GetImportBatchNoMatchAction($batch_id);
538 my $item_action = GetImportBatchItemAction($batch_id);
541 my $dbh = C4::Context->dbh;
542 my $sth = $dbh->prepare("SELECT import_records.import_record_id, record_type, status, overlay_status, marc, encoding
544 LEFT JOIN import_auths ON (import_records.import_record_id=import_auths.import_record_id)
545 LEFT JOIN import_biblios ON (import_records.import_record_id=import_biblios.import_record_id)
546 WHERE import_batch_id = ?");
547 $sth->execute($batch_id);
549 while (my $rowref = $sth->fetchrow_hashref) {
550 $record_type = $rowref->{'record_type'};
552 if ($progress_interval and (0 == ($rec_num % $progress_interval))) {
553 &$progress_callback($rec_num);
555 if ($rowref->{'status'} eq 'error' or $rowref->{'status'} eq 'imported') {
560 my $marc_record = MARC::Record->new_from_usmarc($rowref->{'marc'});
562 if ($record_type eq 'biblio') {
563 # remove any item tags - rely on BatchCommitItems
564 ($item_tag,$item_subfield) = &GetMarcFromKohaField("items.itemnumber",'');
565 foreach my $item_field ($marc_record->field($item_tag)) {
566 $marc_record->delete_field($item_field);
570 my ($record_result, $item_result, $record_match) =
571 _get_commit_action($overlay_action, $nomatch_action, $item_action,
572 $rowref->{'overlay_status'}, $rowref->{'import_record_id'}, $record_type);
576 if ($record_result eq 'create_new') {
578 if ($record_type eq 'biblio') {
579 my $biblioitemnumber;
580 ($recordid, $biblioitemnumber) = AddBiblio($marc_record, $framework);
581 $query = "UPDATE import_biblios SET matched_biblionumber = ? WHERE import_record_id = ?";
582 if ($item_result eq 'create_new') {
583 my ($bib_items_added, $bib_items_errored) = BatchCommitItems($rowref->{'import_record_id'}, $recordid);
584 $num_items_added += $bib_items_added;
585 $num_items_errored += $bib_items_errored;
588 $recordid = AddAuthority($marc_record, undef, GuessAuthTypeCode($marc_record));
589 $query = "UPDATE import_auths SET matched_authid = ? WHERE import_record_id = ?";
591 my $sth = $dbh->prepare_cached($query);
592 $sth->execute($recordid, $rowref->{'import_record_id'});
594 SetImportRecordStatus($rowref->{'import_record_id'}, 'imported');
595 } elsif ($record_result eq 'replace') {
597 $recordid = $record_match;
599 if ($record_type eq 'biblio') {
600 my ($count, $oldbiblio) = GetBiblio($recordid);
601 $oldxml = GetXmlBiblio($recordid);
603 # remove item fields so that they don't get
604 # added again if record is reverted
605 my $old_marc = MARC::Record->new_from_xml(StripNonXmlChars($oldxml), 'UTF-8', $rowref->{'encoding'});
606 foreach my $item_field ($old_marc->field($item_tag)) {
607 $old_marc->delete_field($item_field);
609 $oldxml = $old_marc->as_xml();
611 ModBiblio($marc_record, $recordid, $oldbiblio->{'frameworkcode'});
612 $query = "UPDATE import_biblios SET matched_biblionumber = ? WHERE import_record_id = ?";
614 if ($item_result eq 'create_new') {
615 my ($bib_items_added, $bib_items_errored) = BatchCommitItems($rowref->{'import_record_id'}, $recordid);
616 $num_items_added += $bib_items_added;
617 $num_items_errored += $bib_items_errored;
620 $oldxml = GetAuthorityXML($recordid);
622 ModAuthority($recordid, $marc_record, GuessAuthTypeCode($marc_record));
623 $query = "UPDATE import_auths SET matched_authid = ? WHERE import_record_id = ?";
625 my $sth = $dbh->prepare_cached("UPDATE import_records SET marcxml_old = ? WHERE import_record_id = ?");
626 $sth->execute($oldxml, $rowref->{'import_record_id'});
628 my $sth2 = $dbh->prepare_cached($query);
629 $sth2->execute($recordid, $rowref->{'import_record_id'});
631 SetImportRecordOverlayStatus($rowref->{'import_record_id'}, 'match_applied');
632 SetImportRecordStatus($rowref->{'import_record_id'}, 'imported');
633 } elsif ($record_result eq 'ignore') {
635 if ($record_type eq 'biblio' and defined $recordid and $item_result eq 'create_new') {
636 my ($bib_items_added, $bib_items_errored) = BatchCommitItems($rowref->{'import_record_id'}, $recordid);
637 $num_items_added += $bib_items_added;
638 $num_items_errored += $bib_items_errored;
639 # still need to record the matched biblionumber so that the
640 # items can be reverted
641 my $sth2 = $dbh->prepare_cached("UPDATE import_biblios SET matched_biblionumber = ? WHERE import_record_id = ?");
642 $sth2->execute($recordid, $rowref->{'import_record_id'});
643 SetImportRecordOverlayStatus($rowref->{'import_record_id'}, 'match_applied');
645 SetImportRecordStatus($rowref->{'import_record_id'}, 'ignored');
649 SetImportBatchStatus($batch_id, 'imported');
650 return ($num_added, $num_updated, $num_items_added, $num_items_errored, $num_ignored);
653 =head2 BatchCommitItems
655 ($num_items_added, $num_items_errored) =
656 BatchCommitItems($import_record_id, $biblionumber);
660 sub BatchCommitItems {
661 my ($import_record_id, $biblionumber) = @_;
663 my $dbh = C4::Context->dbh;
665 my $num_items_added = 0;
666 my $num_items_errored = 0;
667 my $sth = $dbh->prepare("SELECT import_items_id, import_items.marcxml, encoding
669 JOIN import_records USING (import_record_id)
670 WHERE import_record_id = ?
671 ORDER BY import_items_id");
672 $sth->bind_param(1, $import_record_id);
674 while (my $row = $sth->fetchrow_hashref()) {
675 my $item_marc = MARC::Record->new_from_xml(StripNonXmlChars($row->{'marcxml'}), 'UTF-8', $row->{'encoding'});
676 # FIXME - duplicate barcode check needs to become part of AddItemFromMarc()
677 my $item = TransformMarcToKoha($dbh, $item_marc);
678 my $duplicate_barcode = exists($item->{'barcode'}) && GetItemnumberFromBarcode($item->{'barcode'});
679 if ($duplicate_barcode) {
680 my $updsth = $dbh->prepare("UPDATE import_items SET status = ?, import_error = ? WHERE import_items_id = ?");
681 $updsth->bind_param(1, 'error');
682 $updsth->bind_param(2, 'duplicate item barcode');
683 $updsth->bind_param(3, $row->{'import_items_id'});
685 $num_items_errored++;
687 my ($item_biblionumber, $biblioitemnumber, $itemnumber) = AddItemFromMarc($item_marc, $biblionumber);
688 my $updsth = $dbh->prepare("UPDATE import_items SET status = ?, itemnumber = ? WHERE import_items_id = ?");
689 $updsth->bind_param(1, 'imported');
690 $updsth->bind_param(2, $itemnumber);
691 $updsth->bind_param(3, $row->{'import_items_id'});
698 return ($num_items_added, $num_items_errored);
701 =head2 BatchRevertRecords
703 my ($num_deleted, $num_errors, $num_reverted, $num_items_deleted,
704 $num_ignored) = BatchRevertRecords($batch_id);
708 sub BatchRevertRecords {
709 my $batch_id = shift;
714 my $num_reverted = 0;
716 my $num_items_deleted = 0;
717 # commit (i.e., save, all records in the batch)
718 SetImportBatchStatus('reverting');
719 my $overlay_action = GetImportBatchOverlayAction($batch_id);
720 my $nomatch_action = GetImportBatchNoMatchAction($batch_id);
721 my $dbh = C4::Context->dbh;
722 my $sth = $dbh->prepare("SELECT import_records.import_record_id, record_type, status, overlay_status, marcxml_old, encoding, matched_biblionumber, matched_authid
724 LEFT JOIN import_auths ON (import_records.import_record_id=import_auths.import_record_id)
725 LEFT JOIN import_biblios ON (import_records.import_record_id=import_biblios.import_record_id)
726 WHERE import_batch_id = ?");
727 $sth->execute($batch_id);
728 while (my $rowref = $sth->fetchrow_hashref) {
729 $record_type = $rowref->{'record_type'};
730 if ($rowref->{'status'} eq 'error' or $rowref->{'status'} eq 'reverted') {
735 my $record_result = _get_revert_action($overlay_action, $rowref->{'overlay_status'}, $rowref->{'status'});
737 if ($record_result eq 'delete') {
739 if ($record_type eq 'biblio') {
740 $num_items_deleted += BatchRevertItems($rowref->{'import_record_id'}, $rowref->{'matched_biblionumber'});
741 $error = DelBiblio($rowref->{'matched_biblionumber'});
743 my $deletedauthid = DelAuthority($rowref->{'matched_authid'});
745 if (defined $error) {
749 SetImportRecordStatus($rowref->{'import_record_id'}, 'reverted');
751 } elsif ($record_result eq 'restore') {
753 my $old_record = MARC::Record->new_from_xml(StripNonXmlChars($rowref->{'marcxml_old'}), 'UTF-8', $rowref->{'encoding'});
754 if ($record_type eq 'biblio') {
755 my $biblionumber = $rowref->{'matched_biblionumber'};
756 my ($count, $oldbiblio) = GetBiblio($biblionumber);
757 $num_items_deleted += BatchRevertItems($rowref->{'import_record_id'}, $rowref->{'matched_biblionumber'});
758 ModBiblio($old_record, $biblionumber, $oldbiblio->{'frameworkcode'});
760 my $authid = $rowref->{'matched_authid'};
761 ModAuthority($authid, $old_record, GuessAuthTypeCode($old_record));
763 SetImportRecordStatus($rowref->{'import_record_id'}, 'reverted');
764 } elsif ($record_result eq 'ignore') {
765 if ($record_type eq 'biblio') {
766 $num_items_deleted += BatchRevertItems($rowref->{'import_record_id'}, $rowref->{'matched_biblionumber'});
768 SetImportRecordStatus($rowref->{'import_record_id'}, 'reverted');
771 if ($record_type eq 'biblio') {
772 # remove matched_biblionumber only if there is no 'imported' item left
773 $query = "UPDATE import_biblios SET matched_biblionumber = NULL WHERE import_record_id = ?";
774 $query = "UPDATE import_biblios SET matched_biblionumber = NULL WHERE import_record_id = ? AND NOT EXISTS (SELECT * FROM import_items WHERE import_items.import_record_id=import_biblios.import_record_id and status='imported')";
776 $query = "UPDATE import_auths SET matched_authid = NULL WHERE import_record_id = ?";
778 my $sth2 = $dbh->prepare_cached($query);
779 $sth2->execute($rowref->{'import_record_id'});
783 SetImportBatchStatus($batch_id, 'reverted');
784 return ($num_deleted, $num_errors, $num_reverted, $num_items_deleted, $num_ignored);
787 =head2 BatchRevertItems
789 my $num_items_deleted = BatchRevertItems($import_record_id, $biblionumber);
793 sub BatchRevertItems {
794 my ($import_record_id, $biblionumber) = @_;
796 my $dbh = C4::Context->dbh;
797 my $num_items_deleted = 0;
799 my $sth = $dbh->prepare_cached("SELECT import_items_id, itemnumber
801 JOIN items USING (itemnumber)
802 WHERE import_record_id = ?");
803 $sth->bind_param(1, $import_record_id);
805 while (my $row = $sth->fetchrow_hashref()) {
806 my $error = DelItemCheck($dbh, $biblionumber, $row->{'itemnumber'});
808 my $updsth = $dbh->prepare("UPDATE import_items SET status = ? WHERE import_items_id = ?");
809 $updsth->bind_param(1, 'reverted');
810 $updsth->bind_param(2, $row->{'import_items_id'});
813 $num_items_deleted++;
820 return $num_items_deleted;
825 CleanBatch($batch_id)
827 Deletes all staged records from the import batch
828 and sets the status of the batch to 'cleaned'. Note
829 that deleting a stage record does *not* affect
830 any record that has been committed to the database.
835 my $batch_id = shift;
836 return unless defined $batch_id;
838 C4::Context->dbh->do('DELETE FROM import_records WHERE import_batch_id = ?', {}, $batch_id);
839 SetImportBatchStatus($batch_id, 'cleaned');
842 =head2 GetAllImportBatches
844 my $results = GetAllImportBatches();
846 Returns a references to an array of hash references corresponding
847 to all import_batches rows (of batch_type 'batch'), sorted in
848 ascending order by import_batch_id.
852 sub GetAllImportBatches {
853 my $dbh = C4::Context->dbh;
854 my $sth = $dbh->prepare_cached("SELECT * FROM import_batches
855 WHERE batch_type IN ('batch', 'webservice')
856 ORDER BY import_batch_id ASC");
860 while (my $row = $sth->fetchrow_hashref) {
861 push @$results, $row;
867 =head2 GetStagedWebserviceBatches
869 my $batch_ids = GetStagedWebserviceBatches();
871 Returns a references to an array of batch id's
872 of batch_type 'webservice' that are not imported
876 my $PENDING_WEBSERVICE_BATCHES_QRY = <<EOQ;
877 SELECT import_batch_id FROM import_batches
878 WHERE batch_type = 'webservice'
879 AND import_status = 'staged'
881 sub GetStagedWebserviceBatches {
882 my $dbh = C4::Context->dbh;
883 return $dbh->selectcol_arrayref($PENDING_WEBSERVICE_BATCHES_QRY);
886 =head2 GetImportBatchRangeDesc
888 my $results = GetImportBatchRangeDesc($offset, $results_per_group);
890 Returns a reference to an array of hash references corresponding to
891 import_batches rows (sorted in descending order by import_batch_id)
892 start at the given offset.
896 sub GetImportBatchRangeDesc {
897 my ($offset, $results_per_group) = @_;
899 my $dbh = C4::Context->dbh;
900 my $query = "SELECT * FROM import_batches
901 WHERE batch_type IN ('batch', 'webservice')
902 ORDER BY import_batch_id DESC";
904 if ($results_per_group){
905 $query .= " LIMIT ?";
906 push(@params, $results_per_group);
909 $query .= " OFFSET ?";
910 push(@params, $offset);
912 my $sth = $dbh->prepare_cached($query);
913 $sth->execute(@params);
914 my $results = $sth->fetchall_arrayref({});
919 =head2 GetItemNumbersFromImportBatch
921 my @itemsnos = GetItemNumbersFromImportBatch($batch_id);
925 sub GetItemNumbersFromImportBatch {
927 my $dbh = C4::Context->dbh;
928 my $sth = $dbh->prepare("SELECT itemnumber FROM import_batches,import_records,import_items WHERE import_batches.import_batch_id=import_records.import_batch_id AND import_records.import_record_id=import_items.import_record_id AND import_batches.import_batch_id=?");
929 $sth->execute($batch_id);
931 while ( my ($itm) = $sth->fetchrow_array ) {
937 =head2 GetNumberOfImportBatches
939 my $count = GetNumberOfImportBatches();
943 sub GetNumberOfNonZ3950ImportBatches {
944 my $dbh = C4::Context->dbh;
945 my $sth = $dbh->prepare("SELECT COUNT(*) FROM import_batches WHERE batch_type != 'z3950'");
947 my ($count) = $sth->fetchrow_array();
952 =head2 GetImportRecordsRange
954 my $results = GetImportRecordsRange($batch_id, $offset, $results_per_group);
956 Returns a reference to an array of hash references corresponding to
957 import_biblios/import_auths/import_records rows for a given batch
958 starting at the given offset.
962 sub GetImportRecordsRange {
963 my ($batch_id, $offset, $results_per_group, $status) = @_;
965 my $dbh = C4::Context->dbh;
966 my $query = "SELECT title, author, isbn, issn, authorized_heading, import_records.import_record_id,
967 record_sequence, status, overlay_status,
968 matched_biblionumber, matched_authid, record_type
970 LEFT JOIN import_auths ON (import_records.import_record_id=import_auths.import_record_id)
971 LEFT JOIN import_biblios ON (import_records.import_record_id=import_biblios.import_record_id)
972 WHERE import_batch_id = ?";
974 push(@params, $batch_id);
976 $query .= " AND status=?";
977 push(@params,$status);
979 $query.=" ORDER BY import_record_id";
981 if($results_per_group){
982 $query .= " LIMIT ?";
983 push(@params, $results_per_group);
986 $query .= " OFFSET ?";
987 push(@params, $offset);
989 my $sth = $dbh->prepare_cached($query);
990 $sth->execute(@params);
991 my $results = $sth->fetchall_arrayref({});
997 =head2 GetBestRecordMatch
999 my $record_id = GetBestRecordMatch($import_record_id);
1003 sub GetBestRecordMatch {
1004 my ($import_record_id) = @_;
1006 my $dbh = C4::Context->dbh;
1007 my $sth = $dbh->prepare("SELECT candidate_match_id
1008 FROM import_record_matches
1009 WHERE import_record_id = ?
1010 ORDER BY score DESC, candidate_match_id DESC");
1011 $sth->execute($import_record_id);
1012 my ($record_id) = $sth->fetchrow_array();
1017 =head2 GetImportBatchStatus
1019 my $status = GetImportBatchStatus($batch_id);
1023 sub GetImportBatchStatus {
1024 my ($batch_id) = @_;
1026 my $dbh = C4::Context->dbh;
1027 my $sth = $dbh->prepare("SELECT import_status FROM import_batches WHERE import_batch_id = ?");
1028 $sth->execute($batch_id);
1029 my ($status) = $sth->fetchrow_array();
1035 =head2 SetImportBatchStatus
1037 SetImportBatchStatus($batch_id, $new_status);
1041 sub SetImportBatchStatus {
1042 my ($batch_id, $new_status) = @_;
1044 my $dbh = C4::Context->dbh;
1045 my $sth = $dbh->prepare("UPDATE import_batches SET import_status = ? WHERE import_batch_id = ?");
1046 $sth->execute($new_status, $batch_id);
1051 =head2 GetImportBatchOverlayAction
1053 my $overlay_action = GetImportBatchOverlayAction($batch_id);
1057 sub GetImportBatchOverlayAction {
1058 my ($batch_id) = @_;
1060 my $dbh = C4::Context->dbh;
1061 my $sth = $dbh->prepare("SELECT overlay_action FROM import_batches WHERE import_batch_id = ?");
1062 $sth->execute($batch_id);
1063 my ($overlay_action) = $sth->fetchrow_array();
1065 return $overlay_action;
1070 =head2 SetImportBatchOverlayAction
1072 SetImportBatchOverlayAction($batch_id, $new_overlay_action);
1076 sub SetImportBatchOverlayAction {
1077 my ($batch_id, $new_overlay_action) = @_;
1079 my $dbh = C4::Context->dbh;
1080 my $sth = $dbh->prepare("UPDATE import_batches SET overlay_action = ? WHERE import_batch_id = ?");
1081 $sth->execute($new_overlay_action, $batch_id);
1086 =head2 GetImportBatchNoMatchAction
1088 my $nomatch_action = GetImportBatchNoMatchAction($batch_id);
1092 sub GetImportBatchNoMatchAction {
1093 my ($batch_id) = @_;
1095 my $dbh = C4::Context->dbh;
1096 my $sth = $dbh->prepare("SELECT nomatch_action FROM import_batches WHERE import_batch_id = ?");
1097 $sth->execute($batch_id);
1098 my ($nomatch_action) = $sth->fetchrow_array();
1100 return $nomatch_action;
1105 =head2 SetImportBatchNoMatchAction
1107 SetImportBatchNoMatchAction($batch_id, $new_nomatch_action);
1111 sub SetImportBatchNoMatchAction {
1112 my ($batch_id, $new_nomatch_action) = @_;
1114 my $dbh = C4::Context->dbh;
1115 my $sth = $dbh->prepare("UPDATE import_batches SET nomatch_action = ? WHERE import_batch_id = ?");
1116 $sth->execute($new_nomatch_action, $batch_id);
1121 =head2 GetImportBatchItemAction
1123 my $item_action = GetImportBatchItemAction($batch_id);
1127 sub GetImportBatchItemAction {
1128 my ($batch_id) = @_;
1130 my $dbh = C4::Context->dbh;
1131 my $sth = $dbh->prepare("SELECT item_action FROM import_batches WHERE import_batch_id = ?");
1132 $sth->execute($batch_id);
1133 my ($item_action) = $sth->fetchrow_array();
1135 return $item_action;
1140 =head2 SetImportBatchItemAction
1142 SetImportBatchItemAction($batch_id, $new_item_action);
1146 sub SetImportBatchItemAction {
1147 my ($batch_id, $new_item_action) = @_;
1149 my $dbh = C4::Context->dbh;
1150 my $sth = $dbh->prepare("UPDATE import_batches SET item_action = ? WHERE import_batch_id = ?");
1151 $sth->execute($new_item_action, $batch_id);
1156 =head2 GetImportBatchMatcher
1158 my $matcher_id = GetImportBatchMatcher($batch_id);
1162 sub GetImportBatchMatcher {
1163 my ($batch_id) = @_;
1165 my $dbh = C4::Context->dbh;
1166 my $sth = $dbh->prepare("SELECT matcher_id FROM import_batches WHERE import_batch_id = ?");
1167 $sth->execute($batch_id);
1168 my ($matcher_id) = $sth->fetchrow_array();
1175 =head2 SetImportBatchMatcher
1177 SetImportBatchMatcher($batch_id, $new_matcher_id);
1181 sub SetImportBatchMatcher {
1182 my ($batch_id, $new_matcher_id) = @_;
1184 my $dbh = C4::Context->dbh;
1185 my $sth = $dbh->prepare("UPDATE import_batches SET matcher_id = ? WHERE import_batch_id = ?");
1186 $sth->execute($new_matcher_id, $batch_id);
1191 =head2 GetImportRecordOverlayStatus
1193 my $overlay_status = GetImportRecordOverlayStatus($import_record_id);
1197 sub GetImportRecordOverlayStatus {
1198 my ($import_record_id) = @_;
1200 my $dbh = C4::Context->dbh;
1201 my $sth = $dbh->prepare("SELECT overlay_status FROM import_records WHERE import_record_id = ?");
1202 $sth->execute($import_record_id);
1203 my ($overlay_status) = $sth->fetchrow_array();
1205 return $overlay_status;
1210 =head2 SetImportRecordOverlayStatus
1212 SetImportRecordOverlayStatus($import_record_id, $new_overlay_status);
1216 sub SetImportRecordOverlayStatus {
1217 my ($import_record_id, $new_overlay_status) = @_;
1219 my $dbh = C4::Context->dbh;
1220 my $sth = $dbh->prepare("UPDATE import_records SET overlay_status = ? WHERE import_record_id = ?");
1221 $sth->execute($new_overlay_status, $import_record_id);
1226 =head2 GetImportRecordStatus
1228 my $overlay_status = GetImportRecordStatus($import_record_id);
1232 sub GetImportRecordStatus {
1233 my ($import_record_id) = @_;
1235 my $dbh = C4::Context->dbh;
1236 my $sth = $dbh->prepare("SELECT status FROM import_records WHERE import_record_id = ?");
1237 $sth->execute($import_record_id);
1238 my ($overlay_status) = $sth->fetchrow_array();
1240 return $overlay_status;
1245 =head2 SetImportRecordStatus
1247 SetImportRecordStatus($import_record_id, $new_overlay_status);
1251 sub SetImportRecordStatus {
1252 my ($import_record_id, $new_overlay_status) = @_;
1254 my $dbh = C4::Context->dbh;
1255 my $sth = $dbh->prepare("UPDATE import_records SET status = ? WHERE import_record_id = ?");
1256 $sth->execute($new_overlay_status, $import_record_id);
1261 =head2 GetImportRecordMatches
1263 my $results = GetImportRecordMatches($import_record_id, $best_only);
1267 sub GetImportRecordMatches {
1268 my $import_record_id = shift;
1269 my $best_only = @_ ? shift : 0;
1271 my $dbh = C4::Context->dbh;
1272 # FIXME currently biblio only
1273 my $sth = $dbh->prepare_cached("SELECT title, author, biblionumber,
1274 candidate_match_id, score, record_type
1276 JOIN import_record_matches USING (import_record_id)
1277 LEFT JOIN biblio ON (biblionumber = candidate_match_id)
1278 WHERE import_record_id = ?
1279 ORDER BY score DESC, biblionumber DESC");
1280 $sth->bind_param(1, $import_record_id);
1283 while (my $row = $sth->fetchrow_hashref) {
1284 if ($row->{'record_type'} eq 'auth') {
1285 $row->{'authorized_heading'} = GetAuthorizedHeading( { authid => $row->{'candidate_match_id'} } );
1287 next if ($row->{'record_type'} eq 'biblio' && not $row->{'biblionumber'});
1288 push @$results, $row;
1298 =head2 SetImportRecordMatches
1300 SetImportRecordMatches($import_record_id, @matches);
1304 sub SetImportRecordMatches {
1305 my $import_record_id = shift;
1308 my $dbh = C4::Context->dbh;
1309 my $delsth = $dbh->prepare("DELETE FROM import_record_matches WHERE import_record_id = ?");
1310 $delsth->execute($import_record_id);
1313 my $sth = $dbh->prepare("INSERT INTO import_record_matches (import_record_id, candidate_match_id, score)
1315 foreach my $match (@matches) {
1316 $sth->execute($import_record_id, $match->{'record_id'}, $match->{'score'});
1321 # internal functions
1323 sub _create_import_record {
1324 my ($batch_id, $record_sequence, $marc_record, $record_type, $encoding, $z3950random) = @_;
1326 my $dbh = C4::Context->dbh;
1327 my $sth = $dbh->prepare("INSERT INTO import_records (import_batch_id, record_sequence, marc, marcxml,
1328 record_type, encoding, z3950random)
1329 VALUES (?, ?, ?, ?, ?, ?, ?)");
1330 $sth->execute($batch_id, $record_sequence, $marc_record->as_usmarc(), $marc_record->as_xml(),
1331 $record_type, $encoding, $z3950random);
1332 my $import_record_id = $dbh->{'mysql_insertid'};
1334 return $import_record_id;
1337 sub _update_import_record_marc {
1338 my ($import_record_id, $marc_record) = @_;
1340 my $dbh = C4::Context->dbh;
1341 my $sth = $dbh->prepare("UPDATE import_records SET marc = ?, marcxml = ?
1342 WHERE import_record_id = ?");
1343 $sth->execute($marc_record->as_usmarc(), $marc_record->as_xml(C4::Context->preference('marcflavour')), $import_record_id);
1347 sub _add_auth_fields {
1348 my ($import_record_id, $marc_record) = @_;
1351 if ($marc_record->field('001')) {
1352 $controlnumber = $marc_record->field('001')->data();
1354 my $authorized_heading = GetAuthorizedHeading({ record => $marc_record });
1355 my $dbh = C4::Context->dbh;
1356 my $sth = $dbh->prepare("INSERT INTO import_auths (import_record_id, control_number, authorized_heading) VALUES (?, ?, ?)");
1357 $sth->execute($import_record_id, $controlnumber, $authorized_heading);
1361 sub _add_biblio_fields {
1362 my ($import_record_id, $marc_record) = @_;
1364 my ($title, $author, $isbn, $issn) = _parse_biblio_fields($marc_record);
1365 my $dbh = C4::Context->dbh;
1366 # FIXME no controlnumber, originalsource
1367 $isbn = C4::Koha::_isbn_cleanup($isbn); # FIXME C4::Koha::_isbn_cleanup should be made public
1368 my $sth = $dbh->prepare("INSERT INTO import_biblios (import_record_id, title, author, isbn, issn) VALUES (?, ?, ?, ?, ?)");
1369 $sth->execute($import_record_id, $title, $author, $isbn, $issn);
1374 sub _update_biblio_fields {
1375 my ($import_record_id, $marc_record) = @_;
1377 my ($title, $author, $isbn, $issn) = _parse_biblio_fields($marc_record);
1378 my $dbh = C4::Context->dbh;
1379 # FIXME no controlnumber, originalsource
1380 # FIXME 2 - should regularize normalization of ISBN wherever it is done
1384 my $sth = $dbh->prepare("UPDATE import_biblios SET title = ?, author = ?, isbn = ?, issn = ?
1385 WHERE import_record_id = ?");
1386 $sth->execute($title, $author, $isbn, $issn, $import_record_id);
1390 sub _parse_biblio_fields {
1391 my ($marc_record) = @_;
1393 my $dbh = C4::Context->dbh;
1394 my $bibliofields = TransformMarcToKoha($dbh, $marc_record, '');
1395 return ($bibliofields->{'title'}, $bibliofields->{'author'}, $bibliofields->{'isbn'}, $bibliofields->{'issn'});
1399 sub _update_batch_record_counts {
1400 my ($batch_id) = @_;
1402 my $dbh = C4::Context->dbh;
1403 my $sth = $dbh->prepare_cached("UPDATE import_batches SET
1407 WHERE import_batch_id = import_batches.import_batch_id),
1411 JOIN import_items USING (import_record_id)
1412 WHERE import_batch_id = import_batches.import_batch_id
1413 AND record_type = 'biblio')
1414 WHERE import_batch_id = ?");
1415 $sth->bind_param(1, $batch_id);
1420 sub _get_commit_action {
1421 my ($overlay_action, $nomatch_action, $item_action, $overlay_status, $import_record_id, $record_type) = @_;
1423 if ($record_type eq 'biblio') {
1424 my ($bib_result, $bib_match, $item_result);
1426 if ($overlay_status ne 'no_match') {
1427 $bib_match = GetBestRecordMatch($import_record_id);
1428 if ($overlay_action eq 'replace') {
1429 $bib_result = defined($bib_match) ? 'replace' : 'create_new';
1430 } elsif ($overlay_action eq 'create_new') {
1431 $bib_result = 'create_new';
1432 } elsif ($overlay_action eq 'ignore') {
1433 $bib_result = 'ignore';
1435 $item_result = ($item_action eq 'always_add' or $item_action eq 'add_only_for_matches') ? 'create_new' : 'ignore';
1437 $bib_result = $nomatch_action;
1438 $item_result = ($item_action eq 'always_add' or $item_action eq 'add_only_for_new') ? 'create_new' : 'ignore';
1440 return ($bib_result, $item_result, $bib_match);
1441 } else { # must be auths
1442 my ($auth_result, $auth_match);
1444 if ($overlay_status ne 'no_match') {
1445 $auth_match = GetBestRecordMatch($import_record_id);
1446 if ($overlay_action eq 'replace') {
1447 $auth_result = defined($auth_match) ? 'replace' : 'create_new';
1448 } elsif ($overlay_action eq 'create_new') {
1449 $auth_result = 'create_new';
1450 } elsif ($overlay_action eq 'ignore') {
1451 $auth_result = 'ignore';
1454 $auth_result = $nomatch_action;
1457 return ($auth_result, undef, $auth_match);
1462 sub _get_revert_action {
1463 my ($overlay_action, $overlay_status, $status) = @_;
1467 if ($status eq 'ignored') {
1468 $bib_result = 'ignore';
1470 if ($overlay_action eq 'create_new') {
1471 $bib_result = 'delete';
1473 $bib_result = ($overlay_status eq 'match_applied') ? 'restore' : 'delete';
1484 Koha Development Team <http://koha-community.org/>
1486 Galen Charlton <galen.charlton@liblime.com>