1 package C4::ImportBatch;
3 # Copyright (C) 2007 LibLime
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.
29 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
32 # set the version for version checking
33 $VERSION = 3.07.00.049;
40 GetImportRecordMarcXML
44 AddItemsToImportBiblio
48 BatchFindBibDuplicates
54 GetStagedWebserviceBatches
55 GetImportBatchRangeDesc
56 GetNumberOfNonZ3950ImportBatches
58 GetItemNumbersFromImportBatch
62 GetImportBatchOverlayAction
63 SetImportBatchOverlayAction
64 GetImportBatchNoMatchAction
65 SetImportBatchNoMatchAction
66 GetImportBatchItemAction
67 SetImportBatchItemAction
70 GetImportRecordOverlayStatus
71 SetImportRecordOverlayStatus
74 GetImportRecordMatches
75 SetImportRecordMatches
81 C4::ImportBatch - manage batches of imported MARC records
89 =head2 GetZ3950BatchId
91 my $batchid = GetZ3950BatchId($z3950server);
93 Retrieves the ID of the import batch for the Z39.50
94 reservoir for the given target. If necessary,
95 creates the import batch.
100 my ($z3950server) = @_;
102 my $dbh = C4::Context->dbh;
103 my $sth = $dbh->prepare("SELECT import_batch_id FROM import_batches
104 WHERE batch_type = 'z3950'
106 $sth->execute($z3950server);
107 my $rowref = $sth->fetchrow_arrayref();
109 if (defined $rowref) {
112 my $batch_id = AddImportBatch( {
113 overlay_action => 'create_new',
114 import_status => 'staged',
115 batch_type => 'z3950',
116 file_name => $z3950server,
123 =head2 GetWebserviceBatchId
125 my $batchid = GetWebserviceBatchId();
127 Retrieves the ID of the import batch for webservice.
128 If necessary, creates the import batch.
132 my $WEBSERVICE_BASE_QRY = <<EOQ;
133 SELECT import_batch_id FROM import_batches
134 WHERE batch_type = 'webservice'
135 AND import_status = 'staged'
137 sub GetWebserviceBatchId {
140 my $dbh = C4::Context->dbh;
141 my $sql = $WEBSERVICE_BASE_QRY;
143 foreach my $field (qw(matcher_id overlay_action nomatch_action item_action)) {
144 if (my $val = $params->{$field}) {
145 $sql .= " AND $field = ?";
149 my $id = $dbh->selectrow_array($sql, undef, @args);
152 $params->{batch_type} = 'webservice';
153 $params->{import_status} = 'staged';
154 return AddImportBatch($params);
157 =head2 GetImportRecordMarc
159 my ($marcblob, $encoding) = GetImportRecordMarc($import_record_id);
163 sub GetImportRecordMarc {
164 my ($import_record_id) = @_;
166 my $dbh = C4::Context->dbh;
167 my $sth = $dbh->prepare("SELECT marc, encoding FROM import_records WHERE import_record_id = ?");
168 $sth->execute($import_record_id);
169 my ($marc, $encoding) = $sth->fetchrow();
171 return $marc, $encoding;
175 =head2 GetImportRecordMarcXML
177 my $marcxml = GetImportRecordMarcXML($import_record_id);
181 sub GetImportRecordMarcXML {
182 my ($import_record_id) = @_;
184 my $dbh = C4::Context->dbh;
185 my $sth = $dbh->prepare("SELECT marcxml FROM import_records WHERE import_record_id = ?");
186 $sth->execute($import_record_id);
187 my ($marcxml) = $sth->fetchrow();
193 =head2 AddImportBatch
195 my $batch_id = AddImportBatch($params_hash);
203 foreach (qw( matcher_id template_id branchcode
204 overlay_action nomatch_action item_action
205 import_status batch_type file_name comments )) {
206 if (exists $params->{$_}) {
208 push @vals, $params->{$_};
211 my $dbh = C4::Context->dbh;
212 $dbh->do("INSERT INTO import_batches (".join( ',', @fields).")
213 VALUES (".join( ',', map '?', @fields).")",
216 return $dbh->{'mysql_insertid'};
219 =head2 GetImportBatch
221 my $row = GetImportBatch($batch_id);
223 Retrieve a hashref of an import_batches row.
230 my $dbh = C4::Context->dbh;
231 my $sth = $dbh->prepare_cached("SELECT * FROM import_batches WHERE import_batch_id = ?");
232 $sth->bind_param(1, $batch_id);
234 my $result = $sth->fetchrow_hashref;
240 =head2 AddBiblioToBatch
242 my $import_record_id = AddBiblioToBatch($batch_id, $record_sequence,
243 $marc_record, $encoding, $z3950random, $update_counts);
247 sub AddBiblioToBatch {
248 my $batch_id = shift;
249 my $record_sequence = shift;
250 my $marc_record = shift;
251 my $encoding = shift;
252 my $z3950random = shift;
253 my $update_counts = @_ ? shift : 1;
255 my $import_record_id = _create_import_record($batch_id, $record_sequence, $marc_record, 'biblio', $encoding, $z3950random);
256 _add_biblio_fields($import_record_id, $marc_record);
257 _update_batch_record_counts($batch_id) if $update_counts;
258 return $import_record_id;
261 =head2 ModBiblioInBatch
263 ModBiblioInBatch($import_record_id, $marc_record);
267 sub ModBiblioInBatch {
268 my ($import_record_id, $marc_record) = @_;
270 _update_import_record_marc($import_record_id, $marc_record);
271 _update_biblio_fields($import_record_id, $marc_record);
275 =head2 BatchStageMarcRecords
277 ($batch_id, $num_records, $num_items, @invalid_records) =
278 BatchStageMarcRecords($encoding, $marc_records, $file_name,
279 $comments, $branch_code, $parse_items,
281 $progress_interval, $progress_callback);
285 sub BatchStageMarcRecords {
286 my $encoding = shift;
287 my $marc_records = shift;
288 my $file_name = shift;
289 my $comments = shift;
290 my $branch_code = shift;
291 my $parse_items = shift;
292 my $leave_as_staging = shift;
294 # optional callback to monitor status
296 my $progress_interval = 0;
297 my $progress_callback = undef;
299 $progress_interval = shift;
300 $progress_callback = shift;
301 $progress_interval = 0 unless $progress_interval =~ /^\d+$/ and $progress_interval > 0;
302 $progress_interval = 0 unless 'CODE' eq ref $progress_callback;
305 my $batch_id = AddImportBatch( {
306 overlay_action => 'create_new',
307 import_status => 'staging',
308 batch_type => 'batch',
309 file_name => $file_name,
310 comments => $comments,
313 SetImportBatchItemAction($batch_id, 'always_add');
315 SetImportBatchItemAction($batch_id, 'ignore');
318 my @invalid_records = ();
321 # FIXME - for now, we're dealing only with bibs
323 foreach my $marc_blob (split(/\x1D/, $marc_records)) {
324 $marc_blob =~ s/^\s+//g;
325 $marc_blob =~ s/\s+$//g;
326 next unless $marc_blob;
328 if ($progress_interval and (0 == ($rec_num % $progress_interval))) {
329 &$progress_callback($rec_num);
331 my ($marc_record, $charset_guessed, $char_errors) =
332 MarcToUTF8Record($marc_blob, C4::Context->preference("marcflavour"), $encoding);
334 $encoding = $charset_guessed unless $encoding;
336 my $import_record_id;
337 if (scalar($marc_record->fields()) == 0) {
338 push @invalid_records, $marc_blob;
341 $import_record_id = AddBiblioToBatch($batch_id, $rec_num, $marc_record, $encoding, int(rand(99999)), 0);
343 my @import_items_ids = AddItemsToImportBiblio($batch_id, $import_record_id, $marc_record, 0);
344 $num_items += scalar(@import_items_ids);
348 unless ($leave_as_staging) {
349 SetImportBatchStatus($batch_id, 'staged');
351 # FIXME branch_code, number of bibs, number of items
352 _update_batch_record_counts($batch_id);
353 return ($batch_id, $num_valid, $num_items, @invalid_records);
356 =head2 AddItemsToImportBiblio
358 my @import_items_ids = AddItemsToImportBiblio($batch_id,
359 $import_record_id, $marc_record, $update_counts);
363 sub AddItemsToImportBiblio {
364 my $batch_id = shift;
365 my $import_record_id = shift;
366 my $marc_record = shift;
367 my $update_counts = @_ ? shift : 0;
369 my @import_items_ids = ();
371 my $dbh = C4::Context->dbh;
372 my ($item_tag,$item_subfield) = &GetMarcFromKohaField("items.itemnumber",'');
373 foreach my $item_field ($marc_record->field($item_tag)) {
374 my $item_marc = MARC::Record->new();
375 $item_marc->leader("00000 a "); # must set Leader/09 to 'a'
376 $item_marc->append_fields($item_field);
377 $marc_record->delete_field($item_field);
378 my $sth = $dbh->prepare_cached("INSERT INTO import_items (import_record_id, status, marcxml)
380 $sth->bind_param(1, $import_record_id);
381 $sth->bind_param(2, 'staged');
382 $sth->bind_param(3, $item_marc->as_xml());
384 push @import_items_ids, $dbh->{'mysql_insertid'};
388 if ($#import_items_ids > -1) {
389 _update_batch_record_counts($batch_id) if $update_counts;
390 _update_import_record_marc($import_record_id, $marc_record);
392 return @import_items_ids;
395 =head2 BatchFindBibDuplicates
397 my $num_with_matches = BatchFindBibDuplicates($batch_id, $matcher,
398 $max_matches, $progress_interval, $progress_callback);
400 Goes through the records loaded in the batch and attempts to
401 find duplicates for each one. Sets the matching status
402 of each record to "no_match" or "auto_match" as appropriate.
404 The $max_matches parameter is optional; if it is not supplied,
407 The $progress_interval and $progress_callback parameters are
408 optional; if both are supplied, the sub referred to by
409 $progress_callback will be invoked every $progress_interval
410 records using the number of records processed as the
415 sub BatchFindBibDuplicates {
416 my $batch_id = shift;
418 my $max_matches = @_ ? shift : 10;
420 # optional callback to monitor status
422 my $progress_interval = 0;
423 my $progress_callback = undef;
425 $progress_interval = shift;
426 $progress_callback = shift;
427 $progress_interval = 0 unless $progress_interval =~ /^\d+$/ and $progress_interval > 0;
428 $progress_interval = 0 unless 'CODE' eq ref $progress_callback;
431 my $dbh = C4::Context->dbh;
433 my $sth = $dbh->prepare("SELECT import_record_id, marc
435 JOIN import_biblios USING (import_record_id)
436 WHERE import_batch_id = ?");
437 $sth->execute($batch_id);
438 my $num_with_matches = 0;
440 while (my $rowref = $sth->fetchrow_hashref) {
442 if ($progress_interval and (0 == ($rec_num % $progress_interval))) {
443 &$progress_callback($rec_num);
445 my $marc_record = MARC::Record->new_from_usmarc($rowref->{'marc'});
447 if (defined $matcher) {
448 @matches = $matcher->get_matches($marc_record, $max_matches);
450 if (scalar(@matches) > 0) {
452 SetImportRecordMatches($rowref->{'import_record_id'}, @matches);
453 SetImportRecordOverlayStatus($rowref->{'import_record_id'}, 'auto_match');
455 SetImportRecordMatches($rowref->{'import_record_id'}, ());
456 SetImportRecordOverlayStatus($rowref->{'import_record_id'}, 'no_match');
460 return $num_with_matches;
463 =head2 BatchCommitBibRecords
465 my ($num_added, $num_updated, $num_items_added, $num_items_errored,
466 $num_ignored) = BatchCommitBibRecords($batch_id, $framework,
467 $progress_interval, $progress_callback);
471 sub BatchCommitBibRecords {
472 my $batch_id = shift;
473 my $framework = shift;
475 # optional callback to monitor status
477 my $progress_interval = 0;
478 my $progress_callback = undef;
480 $progress_interval = shift;
481 $progress_callback = shift;
482 $progress_interval = 0 unless $progress_interval =~ /^\d+$/ and $progress_interval > 0;
483 $progress_interval = 0 unless 'CODE' eq ref $progress_callback;
488 my $num_items_added = 0;
489 my $num_items_errored = 0;
491 # commit (i.e., save, all records in the batch)
492 # FIXME biblio only at the moment
493 SetImportBatchStatus('importing');
494 my $overlay_action = GetImportBatchOverlayAction($batch_id);
495 my $nomatch_action = GetImportBatchNoMatchAction($batch_id);
496 my $item_action = GetImportBatchItemAction($batch_id);
497 my $dbh = C4::Context->dbh;
498 my $sth = $dbh->prepare("SELECT import_record_id, status, overlay_status, marc, encoding
500 JOIN import_biblios USING (import_record_id)
501 WHERE import_batch_id = ?");
502 $sth->execute($batch_id);
504 while (my $rowref = $sth->fetchrow_hashref) {
506 if ($progress_interval and (0 == ($rec_num % $progress_interval))) {
507 &$progress_callback($rec_num);
509 if ($rowref->{'status'} eq 'error' or $rowref->{'status'} eq 'imported') {
514 my $marc_record = MARC::Record->new_from_usmarc($rowref->{'marc'});
516 # remove any item tags - rely on BatchCommitItems
517 my ($item_tag,$item_subfield) = &GetMarcFromKohaField("items.itemnumber",'');
518 foreach my $item_field ($marc_record->field($item_tag)) {
519 $marc_record->delete_field($item_field);
522 # decide what what to do with the bib and item records
523 my ($bib_result, $item_result, $bib_match) =
524 _get_commit_action($overlay_action, $nomatch_action, $item_action,
525 $rowref->{'overlay_status'}, $rowref->{'import_record_id'});
527 if ($bib_result eq 'create_new') {
529 my ($biblionumber, $biblioitemnumber) = AddBiblio($marc_record, $framework);
530 my $sth = $dbh->prepare_cached("UPDATE import_biblios SET matched_biblionumber = ? WHERE import_record_id = ?");
531 $sth->execute($biblionumber, $rowref->{'import_record_id'});
533 if ($item_result eq 'create_new') {
534 my ($bib_items_added, $bib_items_errored) = BatchCommitItems($rowref->{'import_record_id'}, $biblionumber);
535 $num_items_added += $bib_items_added;
536 $num_items_errored += $bib_items_errored;
538 SetImportRecordStatus($rowref->{'import_record_id'}, 'imported');
539 } elsif ($bib_result eq 'replace') {
541 my $biblionumber = $bib_match;
542 my ($count, $oldbiblio) = GetBiblio($biblionumber);
543 my $oldxml = GetXmlBiblio($biblionumber);
545 # remove item fields so that they don't get
546 # added again if record is reverted
547 my $old_marc = MARC::Record->new_from_xml(StripNonXmlChars($oldxml), 'UTF-8', $rowref->{'encoding'});
548 foreach my $item_field ($old_marc->field($item_tag)) {
549 $old_marc->delete_field($item_field);
552 ModBiblio($marc_record, $biblionumber, $oldbiblio->{'frameworkcode'});
553 my $sth = $dbh->prepare_cached("UPDATE import_records SET marcxml_old = ? WHERE import_record_id = ?");
554 $sth->execute($old_marc->as_xml(), $rowref->{'import_record_id'});
556 my $sth2 = $dbh->prepare_cached("UPDATE import_biblios SET matched_biblionumber = ? WHERE import_record_id = ?");
557 $sth2->execute($biblionumber, $rowref->{'import_record_id'});
559 if ($item_result eq 'create_new') {
560 my ($bib_items_added, $bib_items_errored) = BatchCommitItems($rowref->{'import_record_id'}, $biblionumber);
561 $num_items_added += $bib_items_added;
562 $num_items_errored += $bib_items_errored;
564 SetImportRecordOverlayStatus($rowref->{'import_record_id'}, 'match_applied');
565 SetImportRecordStatus($rowref->{'import_record_id'}, 'imported');
566 } elsif ($bib_result eq 'ignore') {
568 my $biblionumber = $bib_match;
569 if (defined $biblionumber and $item_result eq 'create_new') {
570 my ($bib_items_added, $bib_items_errored) = BatchCommitItems($rowref->{'import_record_id'}, $biblionumber);
571 $num_items_added += $bib_items_added;
572 $num_items_errored += $bib_items_errored;
573 # still need to record the matched biblionumber so that the
574 # items can be reverted
575 my $sth2 = $dbh->prepare_cached("UPDATE import_biblios SET matched_biblionumber = ? WHERE import_record_id = ?");
576 $sth2->execute($biblionumber, $rowref->{'import_record_id'});
577 SetImportRecordOverlayStatus($rowref->{'import_record_id'}, 'match_applied');
579 SetImportRecordStatus($rowref->{'import_record_id'}, 'ignored');
583 SetImportBatchStatus($batch_id, 'imported');
584 return ($num_added, $num_updated, $num_items_added, $num_items_errored, $num_ignored);
587 =head2 BatchCommitItems
589 ($num_items_added, $num_items_errored) =
590 BatchCommitItems($import_record_id, $biblionumber);
594 sub BatchCommitItems {
595 my ($import_record_id, $biblionumber) = @_;
597 my $dbh = C4::Context->dbh;
599 my $num_items_added = 0;
600 my $num_items_errored = 0;
601 my $sth = $dbh->prepare("SELECT import_items_id, import_items.marcxml, encoding
603 JOIN import_records USING (import_record_id)
604 WHERE import_record_id = ?
605 ORDER BY import_items_id");
606 $sth->bind_param(1, $import_record_id);
608 while (my $row = $sth->fetchrow_hashref()) {
609 my $item_marc = MARC::Record->new_from_xml(StripNonXmlChars($row->{'marcxml'}), 'UTF-8', $row->{'encoding'});
610 # FIXME - duplicate barcode check needs to become part of AddItemFromMarc()
611 my $item = TransformMarcToKoha($dbh, $item_marc);
612 my $duplicate_barcode = exists($item->{'barcode'}) && GetItemnumberFromBarcode($item->{'barcode'});
613 if ($duplicate_barcode) {
614 my $updsth = $dbh->prepare("UPDATE import_items SET status = ?, import_error = ? WHERE import_items_id = ?");
615 $updsth->bind_param(1, 'error');
616 $updsth->bind_param(2, 'duplicate item barcode');
617 $updsth->bind_param(3, $row->{'import_items_id'});
619 $num_items_errored++;
621 my ($item_biblionumber, $biblioitemnumber, $itemnumber) = AddItemFromMarc($item_marc, $biblionumber);
622 my $updsth = $dbh->prepare("UPDATE import_items SET status = ?, itemnumber = ? WHERE import_items_id = ?");
623 $updsth->bind_param(1, 'imported');
624 $updsth->bind_param(2, $itemnumber);
625 $updsth->bind_param(3, $row->{'import_items_id'});
632 return ($num_items_added, $num_items_errored);
635 =head2 BatchRevertBibRecords
637 my ($num_deleted, $num_errors, $num_reverted, $num_items_deleted,
638 $num_ignored) = BatchRevertBibRecords($batch_id);
642 sub BatchRevertBibRecords {
643 my $batch_id = shift;
647 my $num_reverted = 0;
648 my $num_items_deleted = 0;
650 # commit (i.e., save, all records in the batch)
651 # FIXME biblio only at the moment
652 SetImportBatchStatus('reverting');
653 my $overlay_action = GetImportBatchOverlayAction($batch_id);
654 my $nomatch_action = GetImportBatchNoMatchAction($batch_id);
655 my $dbh = C4::Context->dbh;
656 my $sth = $dbh->prepare("SELECT import_record_id, status, overlay_status, marcxml_old, encoding, matched_biblionumber
658 JOIN import_biblios USING (import_record_id)
659 WHERE import_batch_id = ?");
660 $sth->execute($batch_id);
661 while (my $rowref = $sth->fetchrow_hashref) {
662 if ($rowref->{'status'} eq 'error' or $rowref->{'status'} eq 'reverted') {
667 my $bib_result = _get_revert_action($overlay_action, $rowref->{'overlay_status'}, $rowref->{'status'});
669 if ($bib_result eq 'delete') {
670 $num_items_deleted += BatchRevertItems($rowref->{'import_record_id'}, $rowref->{'matched_biblionumber'});
671 my $error = DelBiblio($rowref->{'matched_biblionumber'});
672 if (defined $error) {
676 SetImportRecordStatus($rowref->{'import_record_id'}, 'reverted');
678 } elsif ($bib_result eq 'restore') {
680 my $old_record = MARC::Record->new_from_xml(StripNonXmlChars($rowref->{'marcxml_old'}), 'UTF-8', $rowref->{'encoding'});
681 my $biblionumber = $rowref->{'matched_biblionumber'};
682 my ($count, $oldbiblio) = GetBiblio($biblionumber);
683 $num_items_deleted += BatchRevertItems($rowref->{'import_record_id'}, $rowref->{'matched_biblionumber'});
684 ModBiblio($old_record, $biblionumber, $oldbiblio->{'frameworkcode'});
685 SetImportRecordStatus($rowref->{'import_record_id'}, 'reverted');
686 } elsif ($bib_result eq 'ignore') {
687 $num_items_deleted += BatchRevertItems($rowref->{'import_record_id'}, $rowref->{'matched_biblionumber'});
688 SetImportRecordStatus($rowref->{'import_record_id'}, 'reverted');
690 my $sth2 = $dbh->prepare_cached("UPDATE import_biblios LEFT JOIN import_items USING (import_record_id) SET matched_biblionumber = NULL WHERE import_record_id = ? AND status != 'imported'" );
691 $sth2->execute($rowref->{'import_record_id'});
695 SetImportBatchStatus($batch_id, 'reverted');
696 return ($num_deleted, $num_errors, $num_reverted, $num_items_deleted, $num_ignored);
699 =head2 BatchRevertItems
701 my $num_items_deleted = BatchRevertItems($import_record_id, $biblionumber);
705 sub BatchRevertItems {
706 my ($import_record_id, $biblionumber) = @_;
708 my $dbh = C4::Context->dbh;
709 my $num_items_deleted = 0;
711 my $sth = $dbh->prepare_cached("SELECT import_items_id, itemnumber
713 JOIN items USING (itemnumber)
714 WHERE import_record_id = ?");
715 $sth->bind_param(1, $import_record_id);
717 while (my $row = $sth->fetchrow_hashref()) {
718 my $error = DelItemCheck($dbh, $biblionumber, $row->{'itemnumber'});
720 my $updsth = $dbh->prepare("UPDATE import_items SET status = ? WHERE import_items_id = ?");
721 $updsth->bind_param(1, 'reverted');
722 $updsth->bind_param(2, $row->{'import_items_id'});
725 $num_items_deleted++;
732 return $num_items_deleted;
737 CleanBatch($batch_id)
739 Deletes all staged records from the import batch
740 and sets the status of the batch to 'cleaned'. Note
741 that deleting a stage record does *not* affect
742 any record that has been committed to the database.
747 my $batch_id = shift;
748 return unless defined $batch_id;
750 C4::Context->dbh->do('DELETE FROM import_records WHERE import_batch_id = ?', {}, $batch_id);
751 SetImportBatchStatus($batch_id, 'cleaned');
754 =head2 GetAllImportBatches
756 my $results = GetAllImportBatches();
758 Returns a references to an array of hash references corresponding
759 to all import_batches rows (of batch_type 'batch'), sorted in
760 ascending order by import_batch_id.
764 sub GetAllImportBatches {
765 my $dbh = C4::Context->dbh;
766 my $sth = $dbh->prepare_cached("SELECT * FROM import_batches
767 WHERE batch_type IN ('batch', 'webservice')
768 ORDER BY import_batch_id ASC");
772 while (my $row = $sth->fetchrow_hashref) {
773 push @$results, $row;
779 =head2 GetStagedWebserviceBatches
781 my $batch_ids = GetStagedWebserviceBatches();
783 Returns a references to an array of batch id's
784 of batch_type 'webservice' that are not imported
788 my $PENDING_WEBSERVICE_BATCHES_QRY = <<EOQ;
789 SELECT import_batch_id FROM import_batches
790 WHERE batch_type = 'webservice'
791 AND import_status = 'staged'
793 sub GetStagedWebserviceBatches {
794 my $dbh = C4::Context->dbh;
795 return $dbh->selectcol_arrayref($PENDING_WEBSERVICE_BATCHES_QRY);
798 =head2 GetImportBatchRangeDesc
800 my $results = GetImportBatchRangeDesc($offset, $results_per_group);
802 Returns a reference to an array of hash references corresponding to
803 import_batches rows (sorted in descending order by import_batch_id)
804 start at the given offset.
808 sub GetImportBatchRangeDesc {
809 my ($offset, $results_per_group) = @_;
811 my $dbh = C4::Context->dbh;
812 my $query = "SELECT * FROM import_batches
813 WHERE batch_type IN ('batch', 'webservice')
814 ORDER BY import_batch_id DESC";
816 if ($results_per_group){
817 $query .= " LIMIT ?";
818 push(@params, $results_per_group);
821 $query .= " OFFSET ?";
822 push(@params, $offset);
824 my $sth = $dbh->prepare_cached($query);
825 $sth->execute(@params);
826 my $results = $sth->fetchall_arrayref({});
831 =head2 GetItemNumbersFromImportBatch
833 my @itemsnos = GetItemNumbersFromImportBatch($batch_id);
837 sub GetItemNumbersFromImportBatch {
839 my $dbh = C4::Context->dbh;
840 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=?");
841 $sth->execute($batch_id);
843 while ( my ($itm) = $sth->fetchrow_array ) {
849 =head2 GetNumberOfImportBatches
851 my $count = GetNumberOfImportBatches();
855 sub GetNumberOfNonZ3950ImportBatches {
856 my $dbh = C4::Context->dbh;
857 my $sth = $dbh->prepare("SELECT COUNT(*) FROM import_batches WHERE batch_type != 'z3950'");
859 my ($count) = $sth->fetchrow_array();
864 =head2 GetImportBibliosRange
866 my $results = GetImportBibliosRange($batch_id, $offset, $results_per_group);
868 Returns a reference to an array of hash references corresponding to
869 import_biblios/import_records rows for a given batch
870 starting at the given offset.
874 sub GetImportBibliosRange {
875 my ($batch_id, $offset, $results_per_group, $status) = @_;
877 my $dbh = C4::Context->dbh;
878 my $query = "SELECT title, author, isbn, issn, import_record_id, record_sequence,
879 status, overlay_status, matched_biblionumber
881 JOIN import_biblios USING (import_record_id)
882 WHERE import_batch_id = ?";
884 push(@params, $batch_id);
886 $query .= " AND status=?";
887 push(@params,$status);
889 $query.=" ORDER BY import_record_id";
891 if($results_per_group){
892 $query .= " LIMIT ?";
893 push(@params, $results_per_group);
896 $query .= " OFFSET ?";
897 push(@params, $offset);
899 my $sth = $dbh->prepare_cached($query);
900 $sth->execute(@params);
901 my $results = $sth->fetchall_arrayref({});
907 =head2 GetBestRecordMatch
909 my $record_id = GetBestRecordMatch($import_record_id);
913 sub GetBestRecordMatch {
914 my ($import_record_id) = @_;
916 my $dbh = C4::Context->dbh;
917 my $sth = $dbh->prepare("SELECT candidate_match_id
918 FROM import_record_matches
919 WHERE import_record_id = ?
920 ORDER BY score DESC, candidate_match_id DESC");
921 $sth->execute($import_record_id);
922 my ($record_id) = $sth->fetchrow_array();
927 =head2 GetImportBatchStatus
929 my $status = GetImportBatchStatus($batch_id);
933 sub GetImportBatchStatus {
936 my $dbh = C4::Context->dbh;
937 my $sth = $dbh->prepare("SELECT import_status FROM import_batches WHERE import_batch_id = ?");
938 $sth->execute($batch_id);
939 my ($status) = $sth->fetchrow_array();
945 =head2 SetImportBatchStatus
947 SetImportBatchStatus($batch_id, $new_status);
951 sub SetImportBatchStatus {
952 my ($batch_id, $new_status) = @_;
954 my $dbh = C4::Context->dbh;
955 my $sth = $dbh->prepare("UPDATE import_batches SET import_status = ? WHERE import_batch_id = ?");
956 $sth->execute($new_status, $batch_id);
961 =head2 GetImportBatchOverlayAction
963 my $overlay_action = GetImportBatchOverlayAction($batch_id);
967 sub GetImportBatchOverlayAction {
970 my $dbh = C4::Context->dbh;
971 my $sth = $dbh->prepare("SELECT overlay_action FROM import_batches WHERE import_batch_id = ?");
972 $sth->execute($batch_id);
973 my ($overlay_action) = $sth->fetchrow_array();
975 return $overlay_action;
980 =head2 SetImportBatchOverlayAction
982 SetImportBatchOverlayAction($batch_id, $new_overlay_action);
986 sub SetImportBatchOverlayAction {
987 my ($batch_id, $new_overlay_action) = @_;
989 my $dbh = C4::Context->dbh;
990 my $sth = $dbh->prepare("UPDATE import_batches SET overlay_action = ? WHERE import_batch_id = ?");
991 $sth->execute($new_overlay_action, $batch_id);
996 =head2 GetImportBatchNoMatchAction
998 my $nomatch_action = GetImportBatchNoMatchAction($batch_id);
1002 sub GetImportBatchNoMatchAction {
1003 my ($batch_id) = @_;
1005 my $dbh = C4::Context->dbh;
1006 my $sth = $dbh->prepare("SELECT nomatch_action FROM import_batches WHERE import_batch_id = ?");
1007 $sth->execute($batch_id);
1008 my ($nomatch_action) = $sth->fetchrow_array();
1010 return $nomatch_action;
1015 =head2 SetImportBatchNoMatchAction
1017 SetImportBatchNoMatchAction($batch_id, $new_nomatch_action);
1021 sub SetImportBatchNoMatchAction {
1022 my ($batch_id, $new_nomatch_action) = @_;
1024 my $dbh = C4::Context->dbh;
1025 my $sth = $dbh->prepare("UPDATE import_batches SET nomatch_action = ? WHERE import_batch_id = ?");
1026 $sth->execute($new_nomatch_action, $batch_id);
1031 =head2 GetImportBatchItemAction
1033 my $item_action = GetImportBatchItemAction($batch_id);
1037 sub GetImportBatchItemAction {
1038 my ($batch_id) = @_;
1040 my $dbh = C4::Context->dbh;
1041 my $sth = $dbh->prepare("SELECT item_action FROM import_batches WHERE import_batch_id = ?");
1042 $sth->execute($batch_id);
1043 my ($item_action) = $sth->fetchrow_array();
1045 return $item_action;
1050 =head2 SetImportBatchItemAction
1052 SetImportBatchItemAction($batch_id, $new_item_action);
1056 sub SetImportBatchItemAction {
1057 my ($batch_id, $new_item_action) = @_;
1059 my $dbh = C4::Context->dbh;
1060 my $sth = $dbh->prepare("UPDATE import_batches SET item_action = ? WHERE import_batch_id = ?");
1061 $sth->execute($new_item_action, $batch_id);
1066 =head2 GetImportBatchMatcher
1068 my $matcher_id = GetImportBatchMatcher($batch_id);
1072 sub GetImportBatchMatcher {
1073 my ($batch_id) = @_;
1075 my $dbh = C4::Context->dbh;
1076 my $sth = $dbh->prepare("SELECT matcher_id FROM import_batches WHERE import_batch_id = ?");
1077 $sth->execute($batch_id);
1078 my ($matcher_id) = $sth->fetchrow_array();
1085 =head2 SetImportBatchMatcher
1087 SetImportBatchMatcher($batch_id, $new_matcher_id);
1091 sub SetImportBatchMatcher {
1092 my ($batch_id, $new_matcher_id) = @_;
1094 my $dbh = C4::Context->dbh;
1095 my $sth = $dbh->prepare("UPDATE import_batches SET matcher_id = ? WHERE import_batch_id = ?");
1096 $sth->execute($new_matcher_id, $batch_id);
1101 =head2 GetImportRecordOverlayStatus
1103 my $overlay_status = GetImportRecordOverlayStatus($import_record_id);
1107 sub GetImportRecordOverlayStatus {
1108 my ($import_record_id) = @_;
1110 my $dbh = C4::Context->dbh;
1111 my $sth = $dbh->prepare("SELECT overlay_status FROM import_records WHERE import_record_id = ?");
1112 $sth->execute($import_record_id);
1113 my ($overlay_status) = $sth->fetchrow_array();
1115 return $overlay_status;
1120 =head2 SetImportRecordOverlayStatus
1122 SetImportRecordOverlayStatus($import_record_id, $new_overlay_status);
1126 sub SetImportRecordOverlayStatus {
1127 my ($import_record_id, $new_overlay_status) = @_;
1129 my $dbh = C4::Context->dbh;
1130 my $sth = $dbh->prepare("UPDATE import_records SET overlay_status = ? WHERE import_record_id = ?");
1131 $sth->execute($new_overlay_status, $import_record_id);
1136 =head2 GetImportRecordStatus
1138 my $overlay_status = GetImportRecordStatus($import_record_id);
1142 sub GetImportRecordStatus {
1143 my ($import_record_id) = @_;
1145 my $dbh = C4::Context->dbh;
1146 my $sth = $dbh->prepare("SELECT status FROM import_records WHERE import_record_id = ?");
1147 $sth->execute($import_record_id);
1148 my ($overlay_status) = $sth->fetchrow_array();
1150 return $overlay_status;
1155 =head2 SetImportRecordStatus
1157 SetImportRecordStatus($import_record_id, $new_overlay_status);
1161 sub SetImportRecordStatus {
1162 my ($import_record_id, $new_overlay_status) = @_;
1164 my $dbh = C4::Context->dbh;
1165 my $sth = $dbh->prepare("UPDATE import_records SET status = ? WHERE import_record_id = ?");
1166 $sth->execute($new_overlay_status, $import_record_id);
1171 =head2 GetImportRecordMatches
1173 my $results = GetImportRecordMatches($import_record_id, $best_only);
1177 sub GetImportRecordMatches {
1178 my $import_record_id = shift;
1179 my $best_only = @_ ? shift : 0;
1181 my $dbh = C4::Context->dbh;
1182 # FIXME currently biblio only
1183 my $sth = $dbh->prepare_cached("SELECT title, author, biblionumber, score
1185 JOIN import_record_matches USING (import_record_id)
1186 JOIN biblio ON (biblionumber = candidate_match_id)
1187 WHERE import_record_id = ?
1188 ORDER BY score DESC, biblionumber DESC");
1189 $sth->bind_param(1, $import_record_id);
1192 while (my $row = $sth->fetchrow_hashref) {
1193 push @$results, $row;
1203 =head2 SetImportRecordMatches
1205 SetImportRecordMatches($import_record_id, @matches);
1209 sub SetImportRecordMatches {
1210 my $import_record_id = shift;
1213 my $dbh = C4::Context->dbh;
1214 my $delsth = $dbh->prepare("DELETE FROM import_record_matches WHERE import_record_id = ?");
1215 $delsth->execute($import_record_id);
1218 my $sth = $dbh->prepare("INSERT INTO import_record_matches (import_record_id, candidate_match_id, score)
1220 foreach my $match (@matches) {
1221 $sth->execute($import_record_id, $match->{'record_id'}, $match->{'score'});
1226 # internal functions
1228 sub _create_import_record {
1229 my ($batch_id, $record_sequence, $marc_record, $record_type, $encoding, $z3950random) = @_;
1231 my $dbh = C4::Context->dbh;
1232 my $sth = $dbh->prepare("INSERT INTO import_records (import_batch_id, record_sequence, marc, marcxml,
1233 record_type, encoding, z3950random)
1234 VALUES (?, ?, ?, ?, ?, ?, ?)");
1235 $sth->execute($batch_id, $record_sequence, $marc_record->as_usmarc(), $marc_record->as_xml(),
1236 $record_type, $encoding, $z3950random);
1237 my $import_record_id = $dbh->{'mysql_insertid'};
1239 return $import_record_id;
1242 sub _update_import_record_marc {
1243 my ($import_record_id, $marc_record) = @_;
1245 my $dbh = C4::Context->dbh;
1246 my $sth = $dbh->prepare("UPDATE import_records SET marc = ?, marcxml = ?
1247 WHERE import_record_id = ?");
1248 $sth->execute($marc_record->as_usmarc(), $marc_record->as_xml(C4::Context->preference('marcflavour')), $import_record_id);
1252 sub _add_biblio_fields {
1253 my ($import_record_id, $marc_record) = @_;
1255 my ($title, $author, $isbn, $issn) = _parse_biblio_fields($marc_record);
1256 my $dbh = C4::Context->dbh;
1257 # FIXME no controlnumber, originalsource
1258 $isbn = C4::Koha::_isbn_cleanup($isbn); # FIXME C4::Koha::_isbn_cleanup should be made public
1259 my $sth = $dbh->prepare("INSERT INTO import_biblios (import_record_id, title, author, isbn, issn) VALUES (?, ?, ?, ?, ?)");
1260 $sth->execute($import_record_id, $title, $author, $isbn, $issn);
1265 sub _update_biblio_fields {
1266 my ($import_record_id, $marc_record) = @_;
1268 my ($title, $author, $isbn, $issn) = _parse_biblio_fields($marc_record);
1269 my $dbh = C4::Context->dbh;
1270 # FIXME no controlnumber, originalsource
1271 # FIXME 2 - should regularize normalization of ISBN wherever it is done
1275 my $sth = $dbh->prepare("UPDATE import_biblios SET title = ?, author = ?, isbn = ?, issn = ?
1276 WHERE import_record_id = ?");
1277 $sth->execute($title, $author, $isbn, $issn, $import_record_id);
1281 sub _parse_biblio_fields {
1282 my ($marc_record) = @_;
1284 my $dbh = C4::Context->dbh;
1285 my $bibliofields = TransformMarcToKoha($dbh, $marc_record, '');
1286 return ($bibliofields->{'title'}, $bibliofields->{'author'}, $bibliofields->{'isbn'}, $bibliofields->{'issn'});
1290 sub _update_batch_record_counts {
1291 my ($batch_id) = @_;
1293 my $dbh = C4::Context->dbh;
1294 my $sth = $dbh->prepare_cached("UPDATE import_batches SET
1298 WHERE import_batch_id = import_batches.import_batch_id
1299 AND record_type = 'biblio'),
1303 JOIN import_items USING (import_record_id)
1304 WHERE import_batch_id = import_batches.import_batch_id
1305 AND record_type = 'biblio')
1306 WHERE import_batch_id = ?");
1307 $sth->bind_param(1, $batch_id);
1312 sub _get_commit_action {
1313 my ($overlay_action, $nomatch_action, $item_action, $overlay_status, $import_record_id) = @_;
1315 my ($bib_result, $bib_match, $item_result);
1317 if ($overlay_status ne 'no_match') {
1318 $bib_match = GetBestRecordMatch($import_record_id);
1319 if ($overlay_action eq 'replace') {
1320 $bib_result = defined($bib_match) ? 'replace' : 'create_new';
1321 } elsif ($overlay_action eq 'create_new') {
1322 $bib_result = 'create_new';
1323 } elsif ($overlay_action eq 'ignore') {
1324 $bib_result = 'ignore';
1326 $item_result = ($item_action eq 'always_add' or $item_action eq 'add_only_for_matches') ? 'create_new' : 'ignore';
1328 $bib_result = $nomatch_action;
1329 $item_result = ($item_action eq 'always_add' or $item_action eq 'add_only_for_new') ? 'create_new' : 'ignore';
1332 return ($bib_result, $item_result, $bib_match);
1335 sub _get_revert_action {
1336 my ($overlay_action, $overlay_status, $status) = @_;
1340 if ($status eq 'ignored') {
1341 $bib_result = 'ignore';
1343 if ($overlay_action eq 'create_new') {
1344 $bib_result = 'delete';
1346 $bib_result = ($overlay_status eq 'match_applied') ? 'restore' : 'delete';
1357 Koha Development Team <http://koha-community.org/>
1359 Galen Charlton <galen.charlton@liblime.com>