C4/ImportBatch.pm

   1 package C4::ImportBatch;
   2
   3 # Copyright (C) 2007 LibLime
   4 #
   5 # This file is part of Koha.
   6 #
   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
  10 # version.
  11 #
  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.
  15 #
  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.
  19
  20 use strict;
  21 use warnings;
  22
  23 use C4::Context;
  24 use C4::Koha;
  25 use C4::Biblio;
  26 use C4::Items;
  27 use C4::Charset;
  28
  29 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
  30
  31 BEGIN {
  32         # set the version for version checking
  33         $VERSION = 3.01;
  34         require Exporter;
  35         @ISA    = qw(Exporter);
  36         @EXPORT = qw(
  37     GetZ3950BatchId
  38     GetImportRecordMarc
  39     AddImportBatch
  40     GetImportBatch
  41     AddBiblioToBatch
  42     ModBiblioInBatch
  43
  44     BatchStageMarcRecords
  45     BatchFindBibDuplicates
  46     BatchCommitBibRecords
  47     BatchRevertBibRecords
  48     CleanBatch
  49
  50     GetAllImportBatches
  51     GetImportBatchRangeDesc
  52     GetNumberOfNonZ3950ImportBatches
  53     GetImportBibliosRange
  54         GetItemNumbersFromImportBatch
  55
  56     GetImportBatchStatus
  57     SetImportBatchStatus
  58     GetImportBatchOverlayAction
  59     SetImportBatchOverlayAction
  60     GetImportBatchNoMatchAction
  61     SetImportBatchNoMatchAction
  62     GetImportBatchItemAction
  63     SetImportBatchItemAction
  64     GetImportBatchMatcher
  65     SetImportBatchMatcher
  66     GetImportRecordOverlayStatus
  67     SetImportRecordOverlayStatus
  68     GetImportRecordStatus
  69     SetImportRecordStatus
  70     GetImportRecordMatches
  71     SetImportRecordMatches
  72         );
  73 }
  74
  75 =head1 NAME
  76
  77 C4::ImportBatch - manage batches of imported MARC records
  78
  79 =head1 SYNOPSIS
  80
  81 use C4::ImportBatch;
  82
  83 =head1 FUNCTIONS
  84
  85 =head2 GetZ3950BatchId
  86
  87   my $batchid = GetZ3950BatchId($z3950server);
  88
  89 Retrieves the ID of the import batch for the Z39.50
  90 reservoir for the given target.  If necessary,
  91 creates the import batch.
  92
  93 =cut
  94
  95 sub GetZ3950BatchId {
  96     my ($z3950server) = @_;
  97
  98     my $dbh = C4::Context->dbh;
  99     my $sth = $dbh->prepare("SELECT import_batch_id FROM import_batches
 100                              WHERE  batch_type = 'z3950'
 101                              AND    file_name = ?");
 102     $sth->execute($z3950server);
 103     my $rowref = $sth->fetchrow_arrayref();
 104     $sth->finish();
 105     if (defined $rowref) {
 106         return $rowref->[0];
 107     } else {
 108         my $batch_id = AddImportBatch('create_new', 'staged', 'z3950', $z3950server, '');
 109         return $batch_id;
 110     }
 111
 112 }
 113
 114 =head2 GetImportRecordMarc
 115
 116   my ($marcblob, $encoding) = GetImportRecordMarc($import_record_id);
 117
 118 =cut
 119
 120 sub GetImportRecordMarc {
 121     my ($import_record_id) = @_;
 122
 123     my $dbh = C4::Context->dbh;
 124     my $sth = $dbh->prepare("SELECT marc, encoding FROM import_records WHERE import_record_id = ?");
 125     $sth->execute($import_record_id);
 126     my ($marc, $encoding) = $sth->fetchrow();
 127     $sth->finish();
 128     return $marc, $encoding;
 129
 130 }
 131
 132 =head2 AddImportBatch
 133
 134   my $batch_id = AddImportBatch($overlay_action, $import_status, $type,
 135                                 $file_name, $comments);
 136
 137 =cut
 138
 139 sub AddImportBatch {
 140     my ($overlay_action, $import_status, $type, $file_name, $comments) = @_;
 141
 142     my $dbh = C4::Context->dbh;
 143     my $sth = $dbh->prepare("INSERT INTO import_batches (overlay_action, import_status, batch_type,
 144                                                          file_name, comments)
 145                                     VALUES (?, ?, ?, ?, ?)");
 146     $sth->execute($overlay_action, $import_status, $type, $file_name, $comments);
 147     my $batch_id = $dbh->{'mysql_insertid'};
 148     $sth->finish();
 149
 150     return $batch_id;
 151
 152 }
 153
 154 =head2 GetImportBatch
 155
 156   my $row = GetImportBatch($batch_id);
 157
 158 Retrieve a hashref of an import_batches row.
 159
 160 =cut
 161
 162 sub GetImportBatch {
 163     my ($batch_id) = @_;
 164
 165     my $dbh = C4::Context->dbh;
 166     my $sth = $dbh->prepare_cached("SELECT * FROM import_batches WHERE import_batch_id = ?");
 167     $sth->bind_param(1, $batch_id);
 168     $sth->execute();
 169     my $result = $sth->fetchrow_hashref;
 170     $sth->finish();
 171     return $result;
 172
 173 }
 174
 175 =head2 AddBiblioToBatch
 176
 177   my $import_record_id = AddBiblioToBatch($batch_id, $record_sequence,
 178                 $marc_record, $encoding, $z3950random, $update_counts);
 179
 180 =cut
 181
 182 sub AddBiblioToBatch {
 183     my $batch_id = shift;
 184     my $record_sequence = shift;
 185     my $marc_record = shift;
 186     my $encoding = shift;
 187     my $z3950random = shift;
 188     my $update_counts = @_ ? shift : 1;
 189
 190     my $import_record_id = _create_import_record($batch_id, $record_sequence, $marc_record, 'biblio', $encoding, $z3950random);
 191     _add_biblio_fields($import_record_id, $marc_record);
 192     _update_batch_record_counts($batch_id) if $update_counts;
 193     return $import_record_id;
 194 }
 195
 196 =head2 ModBiblioInBatch
 197
 198   ModBiblioInBatch($import_record_id, $marc_record);
 199
 200 =cut
 201
 202 sub ModBiblioInBatch {
 203     my ($import_record_id, $marc_record) = @_;
 204
 205     _update_import_record_marc($import_record_id, $marc_record);
 206     _update_biblio_fields($import_record_id, $marc_record);
 207
 208 }
 209
 210 =head2 BatchStageMarcRecords
 211
 212   ($batch_id, $num_records, $num_items, @invalid_records) =
 213     BatchStageMarcRecords($marc_flavor, $marc_records, $file_name,
 214                           $comments, $branch_code, $parse_items,
 215                           $leave_as_staging,
 216                           $progress_interval, $progress_callback);
 217
 218 =cut
 219
 220 sub  BatchStageMarcRecords {
 221     my $marc_flavor = shift;
 222     my $marc_records = shift;
 223     my $file_name = shift;
 224     my $comments = shift;
 225     my $branch_code = shift;
 226     my $parse_items = shift;
 227     my $leave_as_staging = shift;
 228
 229     # optional callback to monitor status
 230     # of job
 231     my $progress_interval = 0;
 232     my $progress_callback = undef;
 233     if ($#_ == 1) {
 234         $progress_interval = shift;
 235         $progress_callback = shift;
 236         $progress_interval = 0 unless $progress_interval =~ /^\d+$/ and $progress_interval > 0;
 237         $progress_interval = 0 unless 'CODE' eq ref $progress_callback;
 238     }
 239
 240     my $batch_id = AddImportBatch('create_new', 'staging', 'batch', $file_name, $comments);
 241     if ($parse_items) {
 242         SetImportBatchItemAction($batch_id, 'always_add');
 243     } else {
 244         SetImportBatchItemAction($batch_id, 'ignore');
 245     }
 246
 247     my @invalid_records = ();
 248     my $num_valid = 0;
 249     my $num_items = 0;
 250     # FIXME - for now, we're dealing only with bibs
 251     my $rec_num = 0;
 252     foreach my $marc_blob (split(/\x1D/, $marc_records)) {
 253         $marc_blob =~ s/^\s+//g;
 254         $marc_blob =~ s/\s+$//g;
 255         next unless $marc_blob;
 256         $rec_num++;
 257         if ($progress_interval and (0 == ($rec_num % $progress_interval))) {
 258             &$progress_callback($rec_num);
 259         }
 260         my ($marc_record, $charset_guessed, $char_errors) =
 261             MarcToUTF8Record($marc_blob, C4::Context->preference("marcflavour"));
 262         my $import_record_id;
 263         if (scalar($marc_record->fields()) == 0) {
 264             push @invalid_records, $marc_blob;
 265         } else {
 266             $num_valid++;
 267             $import_record_id = AddBiblioToBatch($batch_id, $rec_num, $marc_record, $marc_flavor, int(rand(99999)), 0);
 268             if ($parse_items) {
 269                 my @import_items_ids = AddItemsToImportBiblio($batch_id, $import_record_id, $marc_record, 0);
 270                 $num_items += scalar(@import_items_ids);
 271             }
 272         }
 273     }
 274     unless ($leave_as_staging) {
 275         SetImportBatchStatus($batch_id, 'staged');
 276     }
 277     # FIXME branch_code, number of bibs, number of items
 278     _update_batch_record_counts($batch_id);
 279     return ($batch_id, $num_valid, $num_items, @invalid_records);
 280 }
 281
 282 =head2 AddItemsToImportBiblio
 283
 284   my @import_items_ids = AddItemsToImportBiblio($batch_id,
 285                 $import_record_id, $marc_record, $update_counts);
 286
 287 =cut
 288
 289 sub AddItemsToImportBiblio {
 290     my $batch_id = shift;
 291     my $import_record_id = shift;
 292     my $marc_record = shift;
 293     my $update_counts = @_ ? shift : 0;
 294
 295     my @import_items_ids = ();
 296
 297     my $dbh = C4::Context->dbh;
 298     my ($item_tag,$item_subfield) = &GetMarcFromKohaField("items.itemnumber",'');
 299     foreach my $item_field ($marc_record->field($item_tag)) {
 300         my $item_marc = MARC::Record->new();
 301         $item_marc->leader("00000    a              "); # must set Leader/09 to 'a'
 302         $item_marc->append_fields($item_field);
 303         $marc_record->delete_field($item_field);
 304         my $sth = $dbh->prepare_cached("INSERT INTO import_items (import_record_id, status, marcxml)
 305                                         VALUES (?, ?, ?)");
 306         $sth->bind_param(1, $import_record_id);
 307         $sth->bind_param(2, 'staged');
 308         $sth->bind_param(3, $item_marc->as_xml());
 309         $sth->execute();
 310         push @import_items_ids, $dbh->{'mysql_insertid'};
 311         $sth->finish();
 312     }
 313
 314     if ($#import_items_ids > -1) {
 315         _update_batch_record_counts($batch_id) if $update_counts;
 316         _update_import_record_marc($import_record_id, $marc_record);
 317     }
 318     return @import_items_ids;
 319 }
 320
 321 =head2 BatchFindBibDuplicates
 322
 323   my $num_with_matches = BatchFindBibDuplicates($batch_id, $matcher,
 324              $max_matches, $progress_interval, $progress_callback);
 325
 326 Goes through the records loaded in the batch and attempts to
 327 find duplicates for each one.  Sets the matching status
 328 of each record to "no_match" or "auto_match" as appropriate.
 329
 330 The $max_matches parameter is optional; if it is not supplied,
 331 it defaults to 10.
 332
 333 The $progress_interval and $progress_callback parameters are
 334 optional; if both are supplied, the sub referred to by
 335 $progress_callback will be invoked every $progress_interval
 336 records using the number of records processed as the
 337 singular argument.
 338
 339 =cut
 340
 341 sub BatchFindBibDuplicates {
 342     my $batch_id = shift;
 343     my $matcher = shift;
 344     my $max_matches = @_ ? shift : 10;
 345
 346     # optional callback to monitor status
 347     # of job
 348     my $progress_interval = 0;
 349     my $progress_callback = undef;
 350     if ($#_ == 1) {
 351         $progress_interval = shift;
 352         $progress_callback = shift;
 353         $progress_interval = 0 unless $progress_interval =~ /^\d+$/ and $progress_interval > 0;
 354         $progress_interval = 0 unless 'CODE' eq ref $progress_callback;
 355     }
 356
 357     my $dbh = C4::Context->dbh;
 358
 359     my $sth = $dbh->prepare("SELECT import_record_id, marc
 360                              FROM import_records
 361                              JOIN import_biblios USING (import_record_id)
 362                              WHERE import_batch_id = ?");
 363     $sth->execute($batch_id);
 364     my $num_with_matches = 0;
 365     my $rec_num = 0;
 366     while (my $rowref = $sth->fetchrow_hashref) {
 367         $rec_num++;
 368         if ($progress_interval and (0 == ($rec_num % $progress_interval))) {
 369             &$progress_callback($rec_num);
 370         }
 371         my $marc_record = MARC::Record->new_from_usmarc($rowref->{'marc'});
 372         my @matches = ();
 373         if (defined $matcher) {
 374             @matches = $matcher->get_matches($marc_record, $max_matches);
 375         }
 376         if (scalar(@matches) > 0) {
 377             $num_with_matches++;
 378             SetImportRecordMatches($rowref->{'import_record_id'}, @matches);
 379             SetImportRecordOverlayStatus($rowref->{'import_record_id'}, 'auto_match');
 380         } else {
 381             SetImportRecordMatches($rowref->{'import_record_id'}, ());
 382             SetImportRecordOverlayStatus($rowref->{'import_record_id'}, 'no_match');
 383         }
 384     }
 385     $sth->finish();
 386     return $num_with_matches;
 387 }
 388
 389 =head2 BatchCommitBibRecords
 390
 391   my ($num_added, $num_updated, $num_items_added, $num_items_errored,
 392       $num_ignored) = BatchCommitBibRecords($batch_id,
 393                       $progress_interval, $progress_callback);
 394
 395 =cut
 396
 397 sub BatchCommitBibRecords {
 398     my $batch_id = shift;
 399
 400     # optional callback to monitor status
 401     # of job
 402     my $progress_interval = 0;
 403     my $progress_callback = undef;
 404     if ($#_ == 1) {
 405         $progress_interval = shift;
 406         $progress_callback = shift;
 407         $progress_interval = 0 unless $progress_interval =~ /^\d+$/ and $progress_interval > 0;
 408         $progress_interval = 0 unless 'CODE' eq ref $progress_callback;
 409     }
 410
 411     my $num_added = 0;
 412     my $num_updated = 0;
 413     my $num_items_added = 0;
 414     my $num_items_errored = 0;
 415     my $num_ignored = 0;
 416     # commit (i.e., save, all records in the batch)
 417     # FIXME biblio only at the moment
 418     SetImportBatchStatus('importing');
 419     my $overlay_action = GetImportBatchOverlayAction($batch_id);
 420     my $nomatch_action = GetImportBatchNoMatchAction($batch_id);
 421     my $item_action = GetImportBatchItemAction($batch_id);
 422     my $dbh = C4::Context->dbh;
 423     my $sth = $dbh->prepare("SELECT import_record_id, status, overlay_status, marc, encoding
 424                              FROM import_records
 425                              JOIN import_biblios USING (import_record_id)
 426                              WHERE import_batch_id = ?");
 427     $sth->execute($batch_id);
 428     my $rec_num = 0;
 429     while (my $rowref = $sth->fetchrow_hashref) {
 430         $rec_num++;
 431         if ($progress_interval and (0 == ($rec_num % $progress_interval))) {
 432             &$progress_callback($rec_num);
 433         }
 434         if ($rowref->{'status'} eq 'error' or $rowref->{'status'} eq 'imported') {
 435             $num_ignored++;
 436             next;
 437         }
 438
 439         my $marc_record = MARC::Record->new_from_usmarc($rowref->{'marc'});
 440
 441         # remove any item tags - rely on BatchCommitItems
 442         my ($item_tag,$item_subfield) = &GetMarcFromKohaField("items.itemnumber",'');
 443         foreach my $item_field ($marc_record->field($item_tag)) {
 444             $marc_record->delete_field($item_field);
 445         }
 446
 447         # decide what what to do with the bib and item records
 448         my ($bib_result, $item_result, $bib_match) =
 449             _get_commit_action($overlay_action, $nomatch_action, $item_action,
 450                                $rowref->{'overlay_status'}, $rowref->{'import_record_id'});
 451
 452         if ($bib_result eq 'create_new') {
 453             $num_added++;
 454             my ($biblionumber, $biblioitemnumber) = AddBiblio($marc_record, '');
 455             my $sth = $dbh->prepare_cached("UPDATE import_biblios SET matched_biblionumber = ? WHERE import_record_id = ?");
 456             $sth->execute($biblionumber, $rowref->{'import_record_id'});
 457             $sth->finish();
 458             if ($item_result eq 'create_new') {
 459                 my ($bib_items_added, $bib_items_errored) = BatchCommitItems($rowref->{'import_record_id'}, $biblionumber);
 460                 $num_items_added += $bib_items_added;
 461                 $num_items_errored += $bib_items_errored;
 462             }
 463             SetImportRecordStatus($rowref->{'import_record_id'}, 'imported');
 464         } elsif ($bib_result eq 'replace') {
 465             $num_updated++;
 466             my $biblionumber = $bib_match;
 467             my ($count, $oldbiblio) = GetBiblio($biblionumber);
 468             my $oldxml = GetXmlBiblio($biblionumber);
 469
 470             # remove item fields so that they don't get
 471             # added again if record is reverted
 472             my $old_marc = MARC::Record->new_from_xml(StripNonXmlChars($oldxml), 'UTF-8', $rowref->{'encoding'});
 473             foreach my $item_field ($old_marc->field($item_tag)) {
 474                 $old_marc->delete_field($item_field);
 475             }
 476
 477             ModBiblio($marc_record, $biblionumber, $oldbiblio->{'frameworkcode'});
 478             my $sth = $dbh->prepare_cached("UPDATE import_records SET marcxml_old = ? WHERE import_record_id = ?");
 479             $sth->execute($old_marc->as_xml(), $rowref->{'import_record_id'});
 480             $sth->finish();
 481             my $sth2 = $dbh->prepare_cached("UPDATE import_biblios SET matched_biblionumber = ? WHERE import_record_id = ?");
 482             $sth2->execute($biblionumber, $rowref->{'import_record_id'});
 483             $sth2->finish();
 484             if ($item_result eq 'create_new') {
 485                 my ($bib_items_added, $bib_items_errored) = BatchCommitItems($rowref->{'import_record_id'}, $biblionumber);
 486                 $num_items_added += $bib_items_added;
 487                 $num_items_errored += $bib_items_errored;
 488             }
 489             SetImportRecordOverlayStatus($rowref->{'import_record_id'}, 'match_applied');
 490             SetImportRecordStatus($rowref->{'import_record_id'}, 'imported');
 491         } elsif ($bib_result eq 'ignore') {
 492             $num_ignored++;
 493             my $biblionumber = $bib_match;
 494             if (defined $biblionumber and $item_result eq 'create_new') {
 495                 my ($bib_items_added, $bib_items_errored) = BatchCommitItems($rowref->{'import_record_id'}, $biblionumber);
 496                 $num_items_added += $bib_items_added;
 497                 $num_items_errored += $bib_items_errored;
 498                 # still need to record the matched biblionumber so that the
 499                 # items can be reverted
 500                 my $sth2 = $dbh->prepare_cached("UPDATE import_biblios SET matched_biblionumber = ? WHERE import_record_id = ?");
 501                 $sth2->execute($biblionumber, $rowref->{'import_record_id'});
 502                 SetImportRecordOverlayStatus($rowref->{'import_record_id'}, 'match_applied');
 503             }
 504             SetImportRecordStatus($rowref->{'import_record_id'}, 'ignored');
 505         }
 506     }
 507     $sth->finish();
 508     SetImportBatchStatus($batch_id, 'imported');
 509     return ($num_added, $num_updated, $num_items_added, $num_items_errored, $num_ignored);
 510 }
 511
 512 =head2 BatchCommitItems
 513
 514   ($num_items_added, $num_items_errored) =
 515          BatchCommitItems($import_record_id, $biblionumber);
 516
 517 =cut
 518
 519 sub BatchCommitItems {
 520     my ($import_record_id, $biblionumber) = @_;
 521
 522     my $dbh = C4::Context->dbh;
 523
 524     my $num_items_added = 0;
 525     my $num_items_errored = 0;
 526     my $sth = $dbh->prepare("SELECT import_items_id, import_items.marcxml, encoding
 527                              FROM import_items
 528                              JOIN import_records USING (import_record_id)
 529                              WHERE import_record_id = ?
 530                              ORDER BY import_items_id");
 531     $sth->bind_param(1, $import_record_id);
 532     $sth->execute();
 533     while (my $row = $sth->fetchrow_hashref()) {
 534         my $item_marc = MARC::Record->new_from_xml(StripNonXmlChars($row->{'marcxml'}), 'UTF-8', $row->{'encoding'});
 535         # FIXME - duplicate barcode check needs to become part of AddItemFromMarc()
 536         my $item = TransformMarcToKoha($dbh, $item_marc);
 537         my $duplicate_barcode = exists($item->{'barcode'}) && GetItemnumberFromBarcode($item->{'barcode'});
 538         if ($duplicate_barcode) {
 539             my $updsth = $dbh->prepare("UPDATE import_items SET status = ?, import_error = ? WHERE import_items_id = ?");
 540             $updsth->bind_param(1, 'error');
 541             $updsth->bind_param(2, 'duplicate item barcode');
 542             $updsth->bind_param(3, $row->{'import_items_id'});
 543             $updsth->execute();
 544             $num_items_errored++;
 545         } else {
 546             my ($item_biblionumber, $biblioitemnumber, $itemnumber) = AddItemFromMarc($item_marc, $biblionumber);
 547             my $updsth = $dbh->prepare("UPDATE import_items SET status = ?, itemnumber = ? WHERE import_items_id = ?");
 548             $updsth->bind_param(1, 'imported');
 549             $updsth->bind_param(2, $itemnumber);
 550             $updsth->bind_param(3, $row->{'import_items_id'});
 551             $updsth->execute();
 552             $updsth->finish();
 553             $num_items_added++;
 554         }
 555     }
 556     $sth->finish();
 557     return ($num_items_added, $num_items_errored);
 558 }
 559
 560 =head2 BatchRevertBibRecords
 561
 562   my ($num_deleted, $num_errors, $num_reverted, $num_items_deleted,
 563       $num_ignored) = BatchRevertBibRecords($batch_id);
 564
 565 =cut
 566
 567 sub BatchRevertBibRecords {
 568     my $batch_id = shift;
 569
 570     my $num_deleted = 0;
 571     my $num_errors = 0;
 572     my $num_reverted = 0;
 573     my $num_items_deleted = 0;
 574     my $num_ignored = 0;
 575     # commit (i.e., save, all records in the batch)
 576     # FIXME biblio only at the moment
 577     SetImportBatchStatus('reverting');
 578     my $overlay_action = GetImportBatchOverlayAction($batch_id);
 579     my $nomatch_action = GetImportBatchNoMatchAction($batch_id);
 580     my $dbh = C4::Context->dbh;
 581     my $sth = $dbh->prepare("SELECT import_record_id, status, overlay_status, marcxml_old, encoding, matched_biblionumber
 582                              FROM import_records
 583                              JOIN import_biblios USING (import_record_id)
 584                              WHERE import_batch_id = ?");
 585     $sth->execute($batch_id);
 586     while (my $rowref = $sth->fetchrow_hashref) {
 587         if ($rowref->{'status'} eq 'error' or $rowref->{'status'} eq 'reverted') {
 588             $num_ignored++;
 589             next;
 590         }
 591
 592         my $bib_result = _get_revert_action($overlay_action, $rowref->{'overlay_status'}, $rowref->{'status'});
 593
 594         if ($bib_result eq 'delete') {
 595             $num_items_deleted += BatchRevertItems($rowref->{'import_record_id'}, $rowref->{'matched_biblionumber'});
 596             my $error = DelBiblio($rowref->{'matched_biblionumber'});
 597             if (defined $error) {
 598                 $num_errors++;
 599             } else {
 600                 $num_deleted++;
 601                 SetImportRecordStatus($rowref->{'import_record_id'}, 'reverted');
 602             }
 603         } elsif ($bib_result eq 'restore') {
 604             $num_reverted++;
 605             my $old_record = MARC::Record->new_from_xml(StripNonXmlChars($rowref->{'marcxml_old'}), 'UTF-8', $rowref->{'encoding'});
 606             my $biblionumber = $rowref->{'matched_biblionumber'};
 607             my ($count, $oldbiblio) = GetBiblio($biblionumber);
 608             $num_items_deleted += BatchRevertItems($rowref->{'import_record_id'}, $rowref->{'matched_biblionumber'});
 609             ModBiblio($old_record, $biblionumber, $oldbiblio->{'frameworkcode'});
 610             SetImportRecordStatus($rowref->{'import_record_id'}, 'reverted');
 611         } elsif ($bib_result eq 'ignore') {
 612             $num_items_deleted += BatchRevertItems($rowref->{'import_record_id'}, $rowref->{'matched_biblionumber'});
 613             SetImportRecordStatus($rowref->{'import_record_id'}, 'reverted');
 614         }
 615         my $sth2 = $dbh->prepare_cached("UPDATE import_biblios SET matched_biblionumber = NULL WHERE import_record_id = ?");
 616         $sth2->execute($rowref->{'import_record_id'});
 617     }
 618
 619     $sth->finish();
 620     SetImportBatchStatus($batch_id, 'reverted');
 621     return ($num_deleted, $num_errors, $num_reverted, $num_items_deleted, $num_ignored);
 622 }
 623
 624 =head2 BatchRevertItems
 625
 626   my $num_items_deleted = BatchRevertItems($import_record_id, $biblionumber);
 627
 628 =cut
 629
 630 sub BatchRevertItems {
 631     my ($import_record_id, $biblionumber) = @_;
 632
 633     my $dbh = C4::Context->dbh;
 634     my $num_items_deleted = 0;
 635
 636     my $sth = $dbh->prepare_cached("SELECT import_items_id, itemnumber
 637                                    FROM import_items
 638                                    JOIN items USING (itemnumber)
 639                                    WHERE import_record_id = ?");
 640     $sth->bind_param(1, $import_record_id);
 641     $sth->execute();
 642     while (my $row = $sth->fetchrow_hashref()) {
 643         DelItem($dbh, $biblionumber, $row->{'itemnumber'});
 644         my $updsth = $dbh->prepare("UPDATE import_items SET status = ? WHERE import_items_id = ?");
 645         $updsth->bind_param(1, 'reverted');
 646         $updsth->bind_param(2, $row->{'import_items_id'});
 647         $updsth->execute();
 648         $updsth->finish();
 649         $num_items_deleted++;
 650     }
 651     $sth->finish();
 652     return $num_items_deleted;
 653 }
 654
 655 =head2 CleanBatch
 656
 657   CleanBatch($batch_id)
 658
 659 Deletes all staged records from the import batch
 660 and sets the status of the batch to 'cleaned'.  Note
 661 that deleting a stage record does *not* affect
 662 any record that has been committed to the database.
 663
 664 =cut
 665
 666 sub CleanBatch {
 667     my $batch_id = shift;
 668     return unless defined $batch_id;
 669
 670     C4::Context->dbh->do('DELETE FROM import_records WHERE import_batch_id = ?', {}, $batch_id);
 671     SetImportBatchStatus($batch_id, 'cleaned');
 672 }
 673
 674 =head2 GetAllImportBatches
 675
 676   my $results = GetAllImportBatches();
 677
 678 Returns a references to an array of hash references corresponding
 679 to all import_batches rows (of batch_type 'batch'), sorted in
 680 ascending order by import_batch_id.
 681
 682 =cut
 683
 684 sub  GetAllImportBatches {
 685     my $dbh = C4::Context->dbh;
 686     my $sth = $dbh->prepare_cached("SELECT * FROM import_batches
 687                                     WHERE batch_type = 'batch'
 688                                     ORDER BY import_batch_id ASC");
 689
 690     my $results = [];
 691     $sth->execute();
 692     while (my $row = $sth->fetchrow_hashref) {
 693         push @$results, $row;
 694     }
 695     $sth->finish();
 696     return $results;
 697 }
 698
 699 =head2 GetImportBatchRangeDesc
 700
 701   my $results = GetImportBatchRangeDesc($offset, $results_per_group);
 702
 703 Returns a reference to an array of hash references corresponding to
 704 import_batches rows (sorted in descending order by import_batch_id)
 705 start at the given offset.
 706
 707 =cut
 708
 709 sub GetImportBatchRangeDesc {
 710     my ($offset, $results_per_group) = @_;
 711
 712     my $dbh = C4::Context->dbh;
 713     my $query = "SELECT * FROM import_batches
 714                                     WHERE batch_type = 'batch'
 715                                     ORDER BY import_batch_id DESC";
 716     my @params;
 717     if ($results_per_group){
 718         $query .= " LIMIT ?";
 719         push(@params, $results_per_group);
 720     }
 721     if ($offset){
 722         $query .= " OFFSET ?";
 723         push(@params, $offset);
 724     }
 725     my $sth = $dbh->prepare_cached($query);
 726     $sth->execute(@params);
 727     my $results = $sth->fetchall_arrayref({});
 728     $sth->finish();
 729     return $results;
 730 }
 731
 732 =head2 GetItemNumbersFromImportBatch
 733
 734   my @itemsnos = GetItemNumbersFromImportBatch($batch_id);
 735
 736 =cut
 737
 738 sub GetItemNumbersFromImportBatch {
 739         my ($batch_id) = @_;
 740         my $dbh = C4::Context->dbh;
 741         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=?");
 742         $sth->execute($batch_id);
 743         my @items ;
 744         while ( my ($itm) = $sth->fetchrow_array ) {
 745                 push @items, $itm;
 746         }
 747         return @items;
 748 }
 749
 750 =head2 GetNumberOfImportBatches
 751
 752   my $count = GetNumberOfImportBatches();
 753
 754 =cut
 755
 756 sub GetNumberOfNonZ3950ImportBatches {
 757     my $dbh = C4::Context->dbh;
 758     my $sth = $dbh->prepare("SELECT COUNT(*) FROM import_batches WHERE batch_type='batch'");
 759     $sth->execute();
 760     my ($count) = $sth->fetchrow_array();
 761     $sth->finish();
 762     return $count;
 763 }
 764
 765 =head2 GetImportBibliosRange
 766
 767   my $results = GetImportBibliosRange($batch_id, $offset, $results_per_group);
 768
 769 Returns a reference to an array of hash references corresponding to
 770 import_biblios/import_records rows for a given batch
 771 starting at the given offset.
 772
 773 =cut
 774
 775 sub GetImportBibliosRange {
 776     my ($batch_id, $offset, $results_per_group, $status) = @_;
 777
 778     my $dbh = C4::Context->dbh;
 779     my $query = "SELECT title, author, isbn, issn, import_record_id, record_sequence,
 780                                            status, overlay_status, matched_biblionumber
 781                                     FROM   import_records
 782                                     JOIN   import_biblios USING (import_record_id)
 783                                     WHERE  import_batch_id = ?";
 784     my @params;
 785     push(@params, $batch_id);
 786     if ($status) {
 787         $query .= " AND status=?";
 788         push(@params,$status);
 789     }
 790     $query.=" ORDER BY import_record_id";
 791
 792     if($results_per_group){
 793         $query .= " LIMIT ?";
 794         push(@params, $results_per_group);
 795     }
 796     if($offset){
 797         $query .= " OFFSET ?";
 798         push(@params, $offset);
 799     }
 800     my $sth = $dbh->prepare_cached($query);
 801     $sth->execute(@params);
 802     my $results = $sth->fetchall_arrayref({});
 803     $sth->finish();
 804     return $results;
 805
 806 }
 807
 808 =head2 GetBestRecordMatch
 809
 810   my $record_id = GetBestRecordMatch($import_record_id);
 811
 812 =cut
 813
 814 sub GetBestRecordMatch {
 815     my ($import_record_id) = @_;
 816
 817     my $dbh = C4::Context->dbh;
 818     my $sth = $dbh->prepare("SELECT candidate_match_id
 819                              FROM   import_record_matches
 820                              WHERE  import_record_id = ?
 821                              ORDER BY score DESC, candidate_match_id DESC");
 822     $sth->execute($import_record_id);
 823     my ($record_id) = $sth->fetchrow_array();
 824     $sth->finish();
 825     return $record_id;
 826 }
 827
 828 =head2 GetImportBatchStatus
 829
 830   my $status = GetImportBatchStatus($batch_id);
 831
 832 =cut
 833
 834 sub GetImportBatchStatus {
 835     my ($batch_id) = @_;
 836
 837     my $dbh = C4::Context->dbh;
 838     my $sth = $dbh->prepare("SELECT import_status FROM import_batches WHERE import_batch_id = ?");
 839     $sth->execute($batch_id);
 840     my ($status) = $sth->fetchrow_array();
 841     $sth->finish();
 842     return $status;
 843
 844 }
 845
 846 =head2 SetImportBatchStatus
 847
 848   SetImportBatchStatus($batch_id, $new_status);
 849
 850 =cut
 851
 852 sub SetImportBatchStatus {
 853     my ($batch_id, $new_status) = @_;
 854
 855     my $dbh = C4::Context->dbh;
 856     my $sth = $dbh->prepare("UPDATE import_batches SET import_status = ? WHERE import_batch_id = ?");
 857     $sth->execute($new_status, $batch_id);
 858     $sth->finish();
 859
 860 }
 861
 862 =head2 GetImportBatchOverlayAction
 863
 864   my $overlay_action = GetImportBatchOverlayAction($batch_id);
 865
 866 =cut
 867
 868 sub GetImportBatchOverlayAction {
 869     my ($batch_id) = @_;
 870
 871     my $dbh = C4::Context->dbh;
 872     my $sth = $dbh->prepare("SELECT overlay_action FROM import_batches WHERE import_batch_id = ?");
 873     $sth->execute($batch_id);
 874     my ($overlay_action) = $sth->fetchrow_array();
 875     $sth->finish();
 876     return $overlay_action;
 877
 878 }
 879
 880
 881 =head2 SetImportBatchOverlayAction
 882
 883   SetImportBatchOverlayAction($batch_id, $new_overlay_action);
 884
 885 =cut
 886
 887 sub SetImportBatchOverlayAction {
 888     my ($batch_id, $new_overlay_action) = @_;
 889
 890     my $dbh = C4::Context->dbh;
 891     my $sth = $dbh->prepare("UPDATE import_batches SET overlay_action = ? WHERE import_batch_id = ?");
 892     $sth->execute($new_overlay_action, $batch_id);
 893     $sth->finish();
 894
 895 }
 896
 897 =head2 GetImportBatchNoMatchAction
 898
 899   my $nomatch_action = GetImportBatchNoMatchAction($batch_id);
 900
 901 =cut
 902
 903 sub GetImportBatchNoMatchAction {
 904     my ($batch_id) = @_;
 905
 906     my $dbh = C4::Context->dbh;
 907     my $sth = $dbh->prepare("SELECT nomatch_action FROM import_batches WHERE import_batch_id = ?");
 908     $sth->execute($batch_id);
 909     my ($nomatch_action) = $sth->fetchrow_array();
 910     $sth->finish();
 911     return $nomatch_action;
 912
 913 }
 914
 915
 916 =head2 SetImportBatchNoMatchAction
 917
 918   SetImportBatchNoMatchAction($batch_id, $new_nomatch_action);
 919
 920 =cut
 921
 922 sub SetImportBatchNoMatchAction {
 923     my ($batch_id, $new_nomatch_action) = @_;
 924
 925     my $dbh = C4::Context->dbh;
 926     my $sth = $dbh->prepare("UPDATE import_batches SET nomatch_action = ? WHERE import_batch_id = ?");
 927     $sth->execute($new_nomatch_action, $batch_id);
 928     $sth->finish();
 929
 930 }
 931
 932 =head2 GetImportBatchItemAction
 933
 934   my $item_action = GetImportBatchItemAction($batch_id);
 935
 936 =cut
 937
 938 sub GetImportBatchItemAction {
 939     my ($batch_id) = @_;
 940
 941     my $dbh = C4::Context->dbh;
 942     my $sth = $dbh->prepare("SELECT item_action FROM import_batches WHERE import_batch_id = ?");
 943     $sth->execute($batch_id);
 944     my ($item_action) = $sth->fetchrow_array();
 945     $sth->finish();
 946     return $item_action;
 947
 948 }
 949
 950
 951 =head2 SetImportBatchItemAction
 952
 953   SetImportBatchItemAction($batch_id, $new_item_action);
 954
 955 =cut
 956
 957 sub SetImportBatchItemAction {
 958     my ($batch_id, $new_item_action) = @_;
 959
 960     my $dbh = C4::Context->dbh;
 961     my $sth = $dbh->prepare("UPDATE import_batches SET item_action = ? WHERE import_batch_id = ?");
 962     $sth->execute($new_item_action, $batch_id);
 963     $sth->finish();
 964
 965 }
 966
 967 =head2 GetImportBatchMatcher
 968
 969   my $matcher_id = GetImportBatchMatcher($batch_id);
 970
 971 =cut
 972
 973 sub GetImportBatchMatcher {
 974     my ($batch_id) = @_;
 975
 976     my $dbh = C4::Context->dbh;
 977     my $sth = $dbh->prepare("SELECT matcher_id FROM import_batches WHERE import_batch_id = ?");
 978     $sth->execute($batch_id);
 979     my ($matcher_id) = $sth->fetchrow_array();
 980     $sth->finish();
 981     return $matcher_id;
 982
 983 }
 984
 985
 986 =head2 SetImportBatchMatcher
 987
 988   SetImportBatchMatcher($batch_id, $new_matcher_id);
 989
 990 =cut
 991
 992 sub SetImportBatchMatcher {
 993     my ($batch_id, $new_matcher_id) = @_;
 994
 995     my $dbh = C4::Context->dbh;
 996     my $sth = $dbh->prepare("UPDATE import_batches SET matcher_id = ? WHERE import_batch_id = ?");
 997     $sth->execute($new_matcher_id, $batch_id);
 998     $sth->finish();
 999
1000 }
1001
1002 =head2 GetImportRecordOverlayStatus
1003
1004   my $overlay_status = GetImportRecordOverlayStatus($import_record_id);
1005
1006 =cut
1007
1008 sub GetImportRecordOverlayStatus {
1009     my ($import_record_id) = @_;
1010
1011     my $dbh = C4::Context->dbh;
1012     my $sth = $dbh->prepare("SELECT overlay_status FROM import_records WHERE import_record_id = ?");
1013     $sth->execute($import_record_id);
1014     my ($overlay_status) = $sth->fetchrow_array();
1015     $sth->finish();
1016     return $overlay_status;
1017
1018 }
1019
1020
1021 =head2 SetImportRecordOverlayStatus
1022
1023   SetImportRecordOverlayStatus($import_record_id, $new_overlay_status);
1024
1025 =cut
1026
1027 sub SetImportRecordOverlayStatus {
1028     my ($import_record_id, $new_overlay_status) = @_;
1029
1030     my $dbh = C4::Context->dbh;
1031     my $sth = $dbh->prepare("UPDATE import_records SET overlay_status = ? WHERE import_record_id = ?");
1032     $sth->execute($new_overlay_status, $import_record_id);
1033     $sth->finish();
1034
1035 }
1036
1037 =head2 GetImportRecordStatus
1038
1039   my $overlay_status = GetImportRecordStatus($import_record_id);
1040
1041 =cut
1042
1043 sub GetImportRecordStatus {
1044     my ($import_record_id) = @_;
1045
1046     my $dbh = C4::Context->dbh;
1047     my $sth = $dbh->prepare("SELECT status FROM import_records WHERE import_record_id = ?");
1048     $sth->execute($import_record_id);
1049     my ($overlay_status) = $sth->fetchrow_array();
1050     $sth->finish();
1051     return $overlay_status;
1052
1053 }
1054
1055
1056 =head2 SetImportRecordStatus
1057
1058   SetImportRecordStatus($import_record_id, $new_overlay_status);
1059
1060 =cut
1061
1062 sub SetImportRecordStatus {
1063     my ($import_record_id, $new_overlay_status) = @_;
1064
1065     my $dbh = C4::Context->dbh;
1066     my $sth = $dbh->prepare("UPDATE import_records SET status = ? WHERE import_record_id = ?");
1067     $sth->execute($new_overlay_status, $import_record_id);
1068     $sth->finish();
1069
1070 }
1071
1072 =head2 GetImportRecordMatches
1073
1074   my $results = GetImportRecordMatches($import_record_id, $best_only);
1075
1076 =cut
1077
1078 sub GetImportRecordMatches {
1079     my $import_record_id = shift;
1080     my $best_only = @_ ? shift : 0;
1081
1082     my $dbh = C4::Context->dbh;
1083     # FIXME currently biblio only
1084     my $sth = $dbh->prepare_cached("SELECT title, author, biblionumber, score
1085                                     FROM import_records
1086                                     JOIN import_record_matches USING (import_record_id)
1087                                     JOIN biblio ON (biblionumber = candidate_match_id)
1088                                     WHERE import_record_id = ?
1089                                     ORDER BY score DESC, biblionumber DESC");
1090     $sth->bind_param(1, $import_record_id);
1091     my $results = [];
1092     $sth->execute();
1093     while (my $row = $sth->fetchrow_hashref) {
1094         push @$results, $row;
1095         last if $best_only;
1096     }
1097     $sth->finish();
1098
1099     return $results;
1100
1101 }
1102
1103
1104 =head2 SetImportRecordMatches
1105
1106   SetImportRecordMatches($import_record_id, @matches);
1107
1108 =cut
1109
1110 sub SetImportRecordMatches {
1111     my $import_record_id = shift;
1112     my @matches = @_;
1113
1114     my $dbh = C4::Context->dbh;
1115     my $delsth = $dbh->prepare("DELETE FROM import_record_matches WHERE import_record_id = ?");
1116     $delsth->execute($import_record_id);
1117     $delsth->finish();
1118
1119     my $sth = $dbh->prepare("INSERT INTO import_record_matches (import_record_id, candidate_match_id, score)
1120                                     VALUES (?, ?, ?)");
1121     foreach my $match (@matches) {
1122         $sth->execute($import_record_id, $match->{'record_id'}, $match->{'score'});
1123     }
1124 }
1125
1126
1127 # internal functions
1128
1129 sub _create_import_record {
1130     my ($batch_id, $record_sequence, $marc_record, $record_type, $encoding, $z3950random) = @_;
1131
1132     my $dbh = C4::Context->dbh;
1133     my $sth = $dbh->prepare("INSERT INTO import_records (import_batch_id, record_sequence, marc, marcxml,
1134                                                          record_type, encoding, z3950random)
1135                                     VALUES (?, ?, ?, ?, ?, ?, ?)");
1136     $sth->execute($batch_id, $record_sequence, $marc_record->as_usmarc(), $marc_record->as_xml(),
1137                   $record_type, $encoding, $z3950random);
1138     my $import_record_id = $dbh->{'mysql_insertid'};
1139     $sth->finish();
1140     return $import_record_id;
1141 }
1142
1143 sub _update_import_record_marc {
1144     my ($import_record_id, $marc_record) = @_;
1145
1146     my $dbh = C4::Context->dbh;
1147     my $sth = $dbh->prepare("UPDATE import_records SET marc = ?, marcxml = ?
1148                              WHERE  import_record_id = ?");
1149     $sth->execute($marc_record->as_usmarc(), $marc_record->as_xml(), $import_record_id);
1150     $sth->finish();
1151 }
1152
1153 sub _add_biblio_fields {
1154     my ($import_record_id, $marc_record) = @_;
1155
1156     my ($title, $author, $isbn, $issn) = _parse_biblio_fields($marc_record);
1157     my $dbh = C4::Context->dbh;
1158     # FIXME no controlnumber, originalsource
1159     $isbn = C4::Koha::_isbn_cleanup($isbn); # FIXME C4::Koha::_isbn_cleanup should be made public
1160     my $sth = $dbh->prepare("INSERT INTO import_biblios (import_record_id, title, author, isbn, issn) VALUES (?, ?, ?, ?, ?)");
1161     $sth->execute($import_record_id, $title, $author, $isbn, $issn);
1162     $sth->finish();
1163
1164 }
1165
1166 sub _update_biblio_fields {
1167     my ($import_record_id, $marc_record) = @_;
1168
1169     my ($title, $author, $isbn, $issn) = _parse_biblio_fields($marc_record);
1170     my $dbh = C4::Context->dbh;
1171     # FIXME no controlnumber, originalsource
1172     # FIXME 2 - should regularize normalization of ISBN wherever it is done
1173     $isbn =~ s/\(.*$//;
1174     $isbn =~ tr/ -_//;
1175     $isbn = uc $isbn;
1176     my $sth = $dbh->prepare("UPDATE import_biblios SET title = ?, author = ?, isbn = ?, issn = ?
1177                              WHERE  import_record_id = ?");
1178     $sth->execute($title, $author, $isbn, $issn, $import_record_id);
1179     $sth->finish();
1180 }
1181
1182 sub _parse_biblio_fields {
1183     my ($marc_record) = @_;
1184
1185     my $dbh = C4::Context->dbh;
1186     my $bibliofields = TransformMarcToKoha($dbh, $marc_record, '');
1187     return ($bibliofields->{'title'}, $bibliofields->{'author'}, $bibliofields->{'isbn'}, $bibliofields->{'issn'});
1188
1189 }
1190
1191 sub _update_batch_record_counts {
1192     my ($batch_id) = @_;
1193
1194     my $dbh = C4::Context->dbh;
1195     my $sth = $dbh->prepare_cached("UPDATE import_batches SET num_biblios = (
1196                                     SELECT COUNT(*)
1197                                     FROM import_records
1198                                     WHERE import_batch_id = import_batches.import_batch_id
1199                                     AND record_type = 'biblio')
1200                                     WHERE import_batch_id = ?");
1201     $sth->bind_param(1, $batch_id);
1202     $sth->execute();
1203     $sth->finish();
1204     $sth = $dbh->prepare_cached("UPDATE import_batches SET num_items = (
1205                                     SELECT COUNT(*)
1206                                     FROM import_records
1207                                     JOIN import_items USING (import_record_id)
1208                                     WHERE import_batch_id = import_batches.import_batch_id
1209                                     AND record_type = 'biblio')
1210                                     WHERE import_batch_id = ?");
1211     $sth->bind_param(1, $batch_id);
1212     $sth->execute();
1213     $sth->finish();
1214
1215 }
1216
1217 sub _get_commit_action {
1218     my ($overlay_action, $nomatch_action, $item_action, $overlay_status, $import_record_id) = @_;
1219
1220     my ($bib_result, $bib_match, $item_result);
1221
1222     if ($overlay_status ne 'no_match') {
1223         $bib_match = GetBestRecordMatch($import_record_id);
1224         if ($overlay_action eq 'replace') {
1225             $bib_result  = defined($bib_match) ? 'replace' : 'create_new';
1226         } elsif ($overlay_action eq 'create_new') {
1227             $bib_result  = 'create_new';
1228         } elsif ($overlay_action eq 'ignore') {
1229             $bib_result  = 'ignore';
1230         }
1231         $item_result = ($item_action eq 'always_add' or $item_action eq 'add_only_for_matches') ? 'create_new' : 'ignore';
1232     } else {
1233         $bib_result = $nomatch_action;
1234         $item_result = ($item_action eq 'always_add' or $item_action eq 'add_only_for_new')     ? 'create_new' : 'ignore';
1235     }
1236
1237     return ($bib_result, $item_result, $bib_match);
1238 }
1239
1240 sub _get_revert_action {
1241     my ($overlay_action, $overlay_status, $status) = @_;
1242
1243     my $bib_result;
1244
1245     if ($status eq 'ignored') {
1246         $bib_result = 'ignore';
1247     } else {
1248         if ($overlay_action eq 'create_new') {
1249             $bib_result = 'delete';
1250         } else {
1251             $bib_result = ($overlay_status eq 'match_applied') ? 'restore' : 'delete';
1252         }
1253     }
1254     return $bib_result;
1255 }
1256
1257 1;
1258 __END__
1259
1260 =head1 AUTHOR
1261
1262 Koha Development Team <http://koha-community.org/>
1263
1264 Galen Charlton <galen.charlton@liblime.com>
1265
1266 =cut