C4/Items.pm

   1 package C4::Items;
   2
   3 # Copyright 2007 LibLime, Inc.
   4 # Parts Copyright Biblibre 2010
   5 #
   6 # This file is part of Koha.
   7 #
   8 # Koha is free software; you can redistribute it and/or modify it
   9 # under the terms of the GNU General Public License as published by
  10 # the Free Software Foundation; either version 3 of the License, or
  11 # (at your option) any later version.
  12 #
  13 # Koha is distributed in the hope that it will be useful, but
  14 # WITHOUT ANY WARRANTY; without even the implied warranty of
  15 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
  16 # GNU General Public License for more details.
  17 #
  18 # You should have received a copy of the GNU General Public License
  19 # along with Koha; if not, see <http://www.gnu.org/licenses>.
  20
  21 use Modern::Perl;
  22
  23 use vars qw(@ISA @EXPORT);
  24 BEGIN {
  25     require Exporter;
  26     @ISA = qw(Exporter);
  27
  28     @EXPORT = qw(
  29         AddItemFromMarc
  30         AddItemBatchFromMarc
  31         ModItemFromMarc
  32         Item2Marc
  33         ModDateLastSeen
  34         ModItemTransfer
  35         CheckItemPreSave
  36         GetItemsForInventory
  37         GetItemsInfo
  38         GetItemsLocationInfo
  39         GetHostItemsInfo
  40         get_hostitemnumbers_of
  41         GetHiddenItemnumbers
  42         MoveItemFromBiblio
  43         CartToShelf
  44         GetAnalyticsCount
  45         SearchItems
  46         PrepareItemrecordDisplay
  47     );
  48 }
  49
  50 use Carp;
  51 use Try::Tiny;
  52 use C4::Context;
  53 use C4::Koha;
  54 use C4::Biblio;
  55 use Koha::DateUtils;
  56 use MARC::Record;
  57 use C4::ClassSource;
  58 use C4::Log;
  59 use List::MoreUtils qw(any);
  60 use YAML qw(Load);
  61 use DateTime::Format::MySQL;
  62 use Data::Dumper; # used as part of logging item record changes, not just for
  63                   # debugging; so please don't remove this
  64
  65 use Koha::AuthorisedValues;
  66 use Koha::DateUtils qw(dt_from_string);
  67 use Koha::Database;
  68
  69 use Koha::Biblioitems;
  70 use Koha::Items;
  71 use Koha::ItemTypes;
  72 use Koha::SearchEngine;
  73 use Koha::SearchEngine::Search;
  74 use Koha::Libraries;
  75
  76 =head1 NAME
  77
  78 C4::Items - item management functions
  79
  80 =head1 DESCRIPTION
  81
  82 This module contains an API for manipulating item
  83 records in Koha, and is used by cataloguing, circulation,
  84 acquisitions, and serials management.
  85
  86 # FIXME This POD is not up-to-date
  87 A Koha item record is stored in two places: the
  88 items table and embedded in a MARC tag in the XML
  89 version of the associated bib record in C<biblioitems.marcxml>.
  90 This is done to allow the item information to be readily
  91 indexed (e.g., by Zebra), but means that each item
  92 modification transaction must keep the items table
  93 and the MARC XML in sync at all times.
  94
  95 The items table will be considered authoritative.  In other
  96 words, if there is ever a discrepancy between the items
  97 table and the MARC XML, the items table should be considered
  98 accurate.
  99
 100 =head1 HISTORICAL NOTE
 101
 102 Most of the functions in C<C4::Items> were originally in
 103 the C<C4::Biblio> module.
 104
 105 =head1 CORE EXPORTED FUNCTIONS
 106
 107 The following functions are meant for use by users
 108 of C<C4::Items>
 109
 110 =cut
 111
 112 =head2 CartToShelf
 113
 114   CartToShelf($itemnumber);
 115
 116 Set the current shelving location of the item record
 117 to its stored permanent shelving location.  This is
 118 primarily used to indicate when an item whose current
 119 location is a special processing ('PROC') or shelving cart
 120 ('CART') location is back in the stacks.
 121
 122 =cut
 123
 124 sub CartToShelf {
 125     my ( $itemnumber ) = @_;
 126
 127     unless ( $itemnumber ) {
 128         croak "FAILED CartToShelf() - no itemnumber supplied";
 129     }
 130
 131     my $item = Koha::Items->find($itemnumber);
 132     if ( $item->location eq 'CART' ) {
 133         $item->location($item->permanent_location)->store;
 134     }
 135 }
 136
 137 =head2 AddItemFromMarc
 138
 139   my ($biblionumber, $biblioitemnumber, $itemnumber)
 140       = AddItemFromMarc($source_item_marc, $biblionumber[, $params]);
 141
 142 Given a MARC::Record object containing an embedded item
 143 record and a biblionumber, create a new item record.
 144
 145 The final optional parameter, C<$params>, expected to contain
 146 'skip_modzebra_update' key, which relayed down to Koha::Item/store,
 147 there it prevents calling of ModZebra (and Elasticsearch update),
 148 which takes most of the time in batch adds/deletes: ModZebra better
 149 to be called later in C<additem.pl> after the whole loop.
 150
 151 $params:
 152     skip_modzebra_update => 1|0
 153
 154 =cut
 155
 156 sub AddItemFromMarc {
 157     my $source_item_marc = shift;
 158     my $biblionumber     = shift;
 159     my $params           = @_ ? shift : {};
 160
 161     my $dbh = C4::Context->dbh;
 162
 163     # parse item hash from MARC
 164     my $frameworkcode = C4::Biblio::GetFrameworkCode($biblionumber);
 165     my ( $itemtag, $itemsubfield ) = C4::Biblio::GetMarcFromKohaField( "items.itemnumber" );
 166
 167     my $localitemmarc = MARC::Record->new;
 168     $localitemmarc->append_fields( $source_item_marc->field($itemtag) );
 169
 170     my $item_values = C4::Biblio::TransformMarcToKoha( $localitemmarc, $frameworkcode, 'items' );
 171     my $unlinked_item_subfields = _get_unlinked_item_subfields( $localitemmarc, $frameworkcode );
 172     $item_values->{more_subfields_xml} = _get_unlinked_subfields_xml($unlinked_item_subfields);
 173     $item_values->{biblionumber} = $biblionumber;
 174     $item_values->{cn_source} = delete $item_values->{'items.cn_source'}; # Because of C4::Biblio::_disambiguate
 175     $item_values->{cn_sort}   = delete $item_values->{'items.cn_sort'};   # Because of C4::Biblio::_disambiguate
 176     my $item = Koha::Item->new( $item_values )->store({ skip_modzebra_update => $params->{skip_modzebra_update} });
 177     return ( $item->biblionumber, $item->biblioitemnumber, $item->itemnumber );
 178 }
 179
 180 =head2 AddItemBatchFromMarc
 181
 182   ($itemnumber_ref, $error_ref) = AddItemBatchFromMarc($record,
 183              $biblionumber, $biblioitemnumber, $frameworkcode);
 184
 185 Efficiently create item records from a MARC biblio record with
 186 embedded item fields.  This routine is suitable for batch jobs.
 187
 188 This API assumes that the bib record has already been
 189 saved to the C<biblio> and C<biblioitems> tables.  It does
 190 not expect that C<biblio_metadata.metadata> is populated, but it
 191 will do so via a call to ModBibiloMarc.
 192
 193 The goal of this API is to have a similar effect to using AddBiblio
 194 and AddItems in succession, but without inefficient repeated
 195 parsing of the MARC XML bib record.
 196
 197 This function returns an arrayref of new itemsnumbers and an arrayref of item
 198 errors encountered during the processing.  Each entry in the errors
 199 list is a hashref containing the following keys:
 200
 201 =over
 202
 203 =item item_sequence
 204
 205 Sequence number of original item tag in the MARC record.
 206
 207 =item item_barcode
 208
 209 Item barcode, provide to assist in the construction of
 210 useful error messages.
 211
 212 =item error_code
 213
 214 Code representing the error condition.  Can be 'duplicate_barcode',
 215 'invalid_homebranch', or 'invalid_holdingbranch'.
 216
 217 =item error_information
 218
 219 Additional information appropriate to the error condition.
 220
 221 =back
 222
 223 =cut
 224
 225 sub AddItemBatchFromMarc {
 226     my ($record, $biblionumber, $biblioitemnumber, $frameworkcode) = @_;
 227     my $error;
 228     my @itemnumbers = ();
 229     my @errors = ();
 230     my $dbh = C4::Context->dbh;
 231
 232     # We modify the record, so lets work on a clone so we don't change the
 233     # original.
 234     $record = $record->clone();
 235     # loop through the item tags and start creating items
 236     my @bad_item_fields = ();
 237     my ($itemtag, $itemsubfield) = C4::Biblio::GetMarcFromKohaField( "items.itemnumber" );
 238     my $item_sequence_num = 0;
 239     ITEMFIELD: foreach my $item_field ($record->field($itemtag)) {
 240         $item_sequence_num++;
 241         # we take the item field and stick it into a new
 242         # MARC record -- this is required so far because (FIXME)
 243         # TransformMarcToKoha requires a MARC::Record, not a MARC::Field
 244         # and there is no TransformMarcFieldToKoha
 245         my $temp_item_marc = MARC::Record->new();
 246         $temp_item_marc->append_fields($item_field);
 247
 248         # add biblionumber and biblioitemnumber
 249         my $item = TransformMarcToKoha( $temp_item_marc, $frameworkcode, 'items' );
 250         my $unlinked_item_subfields = _get_unlinked_item_subfields($temp_item_marc, $frameworkcode);
 251         $item->{'more_subfields_xml'} = _get_unlinked_subfields_xml($unlinked_item_subfields);
 252         $item->{'biblionumber'} = $biblionumber;
 253         $item->{'biblioitemnumber'} = $biblioitemnumber;
 254         $item->{cn_source} = delete $item->{'items.cn_source'}; # Because of C4::Biblio::_disambiguate
 255         $item->{cn_sort}   = delete $item->{'items.cn_sort'};   # Because of C4::Biblio::_disambiguate
 256
 257         # check for duplicate barcode
 258         my %item_errors = CheckItemPreSave($item);
 259         if (%item_errors) {
 260             push @errors, _repack_item_errors($item_sequence_num, $item, \%item_errors);
 261             push @bad_item_fields, $item_field;
 262             next ITEMFIELD;
 263         }
 264
 265         my $item_object = Koha::Item->new($item)->store;
 266         push @itemnumbers, $item_object->itemnumber; # FIXME not checking error
 267
 268         logaction("CATALOGUING", "ADD", $item_object->itemnumber, "item") if C4::Context->preference("CataloguingLog");
 269
 270         my $new_item_marc = _marc_from_item_hash($item_object->unblessed, $frameworkcode, $unlinked_item_subfields);
 271         $item_field->replace_with($new_item_marc->field($itemtag));
 272     }
 273
 274     # remove any MARC item fields for rejected items
 275     foreach my $item_field (@bad_item_fields) {
 276         $record->delete_field($item_field);
 277     }
 278
 279     # update the MARC biblio
 280  #   $biblionumber = ModBiblioMarc( $record, $biblionumber, $frameworkcode );
 281
 282     return (\@itemnumbers, \@errors);
 283 }
 284
 285 sub ModItemFromMarc {
 286     my $item_marc = shift;
 287     my $biblionumber = shift;
 288     my $itemnumber = shift;
 289
 290     my $frameworkcode = C4::Biblio::GetFrameworkCode($biblionumber);
 291     my ( $itemtag, $itemsubfield ) = C4::Biblio::GetMarcFromKohaField( "items.itemnumber" );
 292
 293     my $localitemmarc = MARC::Record->new;
 294     $localitemmarc->append_fields( $item_marc->field($itemtag) );
 295     my $item_object = Koha::Items->find($itemnumber);
 296     my $item = TransformMarcToKoha( $localitemmarc, $frameworkcode, 'items' );
 297     $item->{cn_source} = delete $item->{'items.cn_source'}; # Because of C4::Biblio::_disambiguate
 298     $item->{cn_sort}   = delete $item->{'items.cn_sort'};   # Because of C4::Biblio::_disambiguate
 299     $item->{itemnumber} = $itemnumber;
 300     $item->{biblionumber} = $biblionumber;
 301     $item_object = $item_object->set_or_blank($item);
 302     my $unlinked_item_subfields = _get_unlinked_item_subfields( $localitemmarc, $frameworkcode );
 303     $item_object->more_subfields_xml(_get_unlinked_subfields_xml($unlinked_item_subfields))->store;
 304     $item_object->store;
 305
 306     return $item_object->unblessed;
 307 }
 308
 309 =head2 ModItemTransfer
 310
 311   ModItemTransfer($itemnumber, $frombranch, $tobranch, $trigger);
 312
 313 Marks an item as being transferred from one branch to another and records the trigger.
 314
 315 =cut
 316
 317 sub ModItemTransfer {
 318     my ( $itemnumber, $frombranch, $tobranch, $trigger ) = @_;
 319
 320     my $dbh = C4::Context->dbh;
 321     my $item = Koha::Items->find( $itemnumber );
 322
 323     # Remove the 'shelving cart' location status if it is being used.
 324     CartToShelf( $itemnumber ) if $item->location && $item->location eq 'CART' && ( !$item->permanent_location || $item->permanent_location ne 'CART' );
 325
 326     $dbh->do("UPDATE branchtransfers SET datearrived = NOW(), comments = ? WHERE itemnumber = ? AND datearrived IS NULL", undef, "Canceled, new transfer from $frombranch to $tobranch created", $itemnumber);
 327
 328     #new entry in branchtransfers....
 329     my $sth = $dbh->prepare(
 330         "INSERT INTO branchtransfers (itemnumber, frombranch, datesent, tobranch, reason)
 331         VALUES (?, ?, NOW(), ?, ?)");
 332     $sth->execute($itemnumber, $frombranch, $tobranch, $trigger);
 333
 334     # FIXME we are fetching the item twice in the 2 next statements!
 335     Koha::Items->find($itemnumber)->holdingbranch($frombranch)->store({ log_action => 0 });
 336     ModDateLastSeen($itemnumber);
 337     return;
 338 }
 339
 340 =head2 ModDateLastSeen
 341
 342 ModDateLastSeen( $itemnumber, $leave_item_lost );
 343
 344 Mark item as seen. Is called when an item is issued, returned or manually marked during inventory/stocktaking.
 345 C<$itemnumber> is the item number
 346 C<$leave_item_lost> determines if a lost item will be found or remain lost
 347
 348 =cut
 349
 350 sub ModDateLastSeen {
 351     my ( $itemnumber, $leave_item_lost ) = @_;
 352
 353     my $today = output_pref({ dt => dt_from_string, dateformat => 'iso', dateonly => 1 });
 354
 355     my $item = Koha::Items->find($itemnumber);
 356     $item->datelastseen($today);
 357     $item->itemlost(0) unless $leave_item_lost;
 358     $item->store({ log_action => 0 });
 359 }
 360
 361 =head2 CheckItemPreSave
 362
 363     my $item_ref = TransformMarcToKoha($marc, 'items');
 364     # do stuff
 365     my %errors = CheckItemPreSave($item_ref);
 366     if (exists $errors{'duplicate_barcode'}) {
 367         print "item has duplicate barcode: ", $errors{'duplicate_barcode'}, "\n";
 368     } elsif (exists $errors{'invalid_homebranch'}) {
 369         print "item has invalid home branch: ", $errors{'invalid_homebranch'}, "\n";
 370     } elsif (exists $errors{'invalid_holdingbranch'}) {
 371         print "item has invalid holding branch: ", $errors{'invalid_holdingbranch'}, "\n";
 372     } else {
 373         print "item is OK";
 374     }
 375
 376 Given a hashref containing item fields, determine if it can be
 377 inserted or updated in the database.  Specifically, checks for
 378 database integrity issues, and returns a hash containing any
 379 of the following keys, if applicable.
 380
 381 =over 2
 382
 383 =item duplicate_barcode
 384
 385 Barcode, if it duplicates one already found in the database.
 386
 387 =item invalid_homebranch
 388
 389 Home branch, if not defined in branches table.
 390
 391 =item invalid_holdingbranch
 392
 393 Holding branch, if not defined in branches table.
 394
 395 =back
 396
 397 This function does NOT implement any policy-related checks,
 398 e.g., whether current operator is allowed to save an
 399 item that has a given branch code.
 400
 401 =cut
 402
 403 sub CheckItemPreSave {
 404     my $item_ref = shift;
 405
 406     my %errors = ();
 407
 408     # check for duplicate barcode
 409     if (exists $item_ref->{'barcode'} and defined $item_ref->{'barcode'}) {
 410         my $existing_item= Koha::Items->find({barcode => $item_ref->{'barcode'}});
 411         if ($existing_item) {
 412             if (!exists $item_ref->{'itemnumber'}                       # new item
 413                 or $item_ref->{'itemnumber'} != $existing_item->itemnumber) { # existing item
 414                 $errors{'duplicate_barcode'} = $item_ref->{'barcode'};
 415             }
 416         }
 417     }
 418
 419     # check for valid home branch
 420     if (exists $item_ref->{'homebranch'} and defined $item_ref->{'homebranch'}) {
 421         my $home_library = Koha::Libraries->find( $item_ref->{homebranch} );
 422         unless (defined $home_library) {
 423             $errors{'invalid_homebranch'} = $item_ref->{'homebranch'};
 424         }
 425     }
 426
 427     # check for valid holding branch
 428     if (exists $item_ref->{'holdingbranch'} and defined $item_ref->{'holdingbranch'}) {
 429         my $holding_library = Koha::Libraries->find( $item_ref->{holdingbranch} );
 430         unless (defined $holding_library) {
 431             $errors{'invalid_holdingbranch'} = $item_ref->{'holdingbranch'};
 432         }
 433     }
 434
 435     return %errors;
 436
 437 }
 438
 439 =head1 EXPORTED SPECIAL ACCESSOR FUNCTIONS
 440
 441 The following functions provide various ways of
 442 getting an item record, a set of item records, or
 443 lists of authorized values for certain item fields.
 444
 445 =cut
 446
 447 =head2 GetItemsForInventory
 448
 449 ($itemlist, $iTotalRecords) = GetItemsForInventory( {
 450   minlocation  => $minlocation,
 451   maxlocation  => $maxlocation,
 452   location     => $location,
 453   itemtype     => $itemtype,
 454   ignoreissued => $ignoreissued,
 455   datelastseen => $datelastseen,
 456   branchcode   => $branchcode,
 457   branch       => $branch,
 458   offset       => $offset,
 459   size         => $size,
 460   statushash   => $statushash,
 461 } );
 462
 463 Retrieve a list of title/authors/barcode/callnumber, for biblio inventory.
 464
 465 The sub returns a reference to a list of hashes, each containing
 466 itemnumber, author, title, barcode, item callnumber, and date last
 467 seen. It is ordered by callnumber then title.
 468
 469 The required minlocation & maxlocation parameters are used to specify a range of item callnumbers
 470 the datelastseen can be used to specify that you want to see items not seen since a past date only.
 471 offset & size can be used to retrieve only a part of the whole listing (defaut behaviour)
 472 $statushash requires a hashref that has the authorized values fieldname (intems.notforloan, etc...) as keys, and an arrayref of statuscodes we are searching for as values.
 473
 474 $iTotalRecords is the number of rows that would have been returned without the $offset, $size limit clause
 475
 476 =cut
 477
 478 sub GetItemsForInventory {
 479     my ( $parameters ) = @_;
 480     my $minlocation  = $parameters->{'minlocation'}  // '';
 481     my $maxlocation  = $parameters->{'maxlocation'}  // '';
 482     my $class_source = $parameters->{'class_source'}  // C4::Context->preference('DefaultClassificationSource');
 483     my $location     = $parameters->{'location'}     // '';
 484     my $itemtype     = $parameters->{'itemtype'}     // '';
 485     my $ignoreissued = $parameters->{'ignoreissued'} // '';
 486     my $datelastseen = $parameters->{'datelastseen'} // '';
 487     my $branchcode   = $parameters->{'branchcode'}   // '';
 488     my $branch       = $parameters->{'branch'}       // '';
 489     my $offset       = $parameters->{'offset'}       // '';
 490     my $size         = $parameters->{'size'}         // '';
 491     my $statushash   = $parameters->{'statushash'}   // '';
 492     my $ignore_waiting_holds = $parameters->{'ignore_waiting_holds'} // '';
 493
 494     my $dbh = C4::Context->dbh;
 495     my ( @bind_params, @where_strings );
 496
 497     my $min_cnsort = GetClassSort($class_source,undef,$minlocation);
 498     my $max_cnsort = GetClassSort($class_source,undef,$maxlocation);
 499
 500     my $select_columns = q{
 501         SELECT DISTINCT(items.itemnumber), barcode, itemcallnumber, title, author, biblio.biblionumber, biblio.frameworkcode, datelastseen, homebranch, location, notforloan, damaged, itemlost, withdrawn, stocknumber, items.cn_sort
 502     };
 503     my $select_count = q{SELECT COUNT(DISTINCT(items.itemnumber))};
 504     my $query = q{
 505         FROM items
 506         LEFT JOIN biblio ON items.biblionumber = biblio.biblionumber
 507         LEFT JOIN biblioitems on items.biblionumber = biblioitems.biblionumber
 508     };
 509     if ($statushash){
 510         for my $authvfield (keys %$statushash){
 511             if ( scalar @{$statushash->{$authvfield}} > 0 ){
 512                 my $joinedvals = join ',', @{$statushash->{$authvfield}};
 513                 push @where_strings, "$authvfield in (" . $joinedvals . ")";
 514             }
 515         }
 516     }
 517
 518     if ($minlocation) {
 519         push @where_strings, 'items.cn_sort >= ?';
 520         push @bind_params, $min_cnsort;
 521     }
 522
 523     if ($maxlocation) {
 524         push @where_strings, 'items.cn_sort <= ?';
 525         push @bind_params, $max_cnsort;
 526     }
 527
 528     if ($datelastseen) {
 529         $datelastseen = output_pref({ str => $datelastseen, dateformat => 'iso', dateonly => 1 });
 530         push @where_strings, '(datelastseen < ? OR datelastseen IS NULL)';
 531         push @bind_params, $datelastseen;
 532     }
 533
 534     if ( $location ) {
 535         push @where_strings, 'items.location = ?';
 536         push @bind_params, $location;
 537     }
 538
 539     if ( $branchcode ) {
 540         if($branch eq "homebranch"){
 541         push @where_strings, 'items.homebranch = ?';
 542         }else{
 543             push @where_strings, 'items.holdingbranch = ?';
 544         }
 545         push @bind_params, $branchcode;
 546     }
 547
 548     if ( $itemtype ) {
 549         push @where_strings, 'biblioitems.itemtype = ?';
 550         push @bind_params, $itemtype;
 551     }
 552
 553     if ( $ignoreissued) {
 554         $query .= "LEFT JOIN issues ON items.itemnumber = issues.itemnumber ";
 555         push @where_strings, 'issues.date_due IS NULL';
 556     }
 557
 558     if ( $ignore_waiting_holds ) {
 559         $query .= "LEFT JOIN reserves ON items.itemnumber = reserves.itemnumber ";
 560         push( @where_strings, q{(reserves.found != 'W' OR reserves.found IS NULL)} );
 561     }
 562
 563     if ( @where_strings ) {
 564         $query .= 'WHERE ';
 565         $query .= join ' AND ', @where_strings;
 566     }
 567     my $count_query = $select_count . $query;
 568     $query .= ' ORDER BY items.cn_sort, itemcallnumber, title';
 569     $query .= " LIMIT $offset, $size" if ($offset and $size);
 570     $query = $select_columns . $query;
 571     my $sth = $dbh->prepare($query);
 572     $sth->execute( @bind_params );
 573
 574     my @results = ();
 575     my $tmpresults = $sth->fetchall_arrayref({});
 576     $sth = $dbh->prepare( $count_query );
 577     $sth->execute( @bind_params );
 578     my ($iTotalRecords) = $sth->fetchrow_array();
 579
 580     my @avs = Koha::AuthorisedValues->search(
 581         {   'marc_subfield_structures.kohafield' => { '>' => '' },
 582             'me.authorised_value'                => { '>' => '' },
 583         },
 584         {   join     => { category => 'marc_subfield_structures' },
 585             distinct => ['marc_subfield_structures.kohafield, me.category, frameworkcode, me.authorised_value'],
 586             '+select' => [ 'marc_subfield_structures.kohafield', 'marc_subfield_structures.frameworkcode', 'me.authorised_value', 'me.lib' ],
 587             '+as'     => [ 'kohafield',                          'frameworkcode',                          'authorised_value',    'lib' ],
 588         }
 589     );
 590
 591     my $avmapping = { map { $_->get_column('kohafield') . ',' . $_->get_column('frameworkcode') . ',' . $_->get_column('authorised_value') => $_->get_column('lib') } @avs };
 592
 593     foreach my $row (@$tmpresults) {
 594
 595         # Auth values
 596         foreach (keys %$row) {
 597             if (
 598                 defined(
 599                     $avmapping->{ "items.$_," . $row->{'frameworkcode'} . "," . ( $row->{$_} // q{} ) }
 600                 )
 601             ) {
 602                 $row->{$_} = $avmapping->{"items.$_,".$row->{'frameworkcode'}.",".$row->{$_}};
 603             }
 604         }
 605         push @results, $row;
 606     }
 607
 608     return (\@results, $iTotalRecords);
 609 }
 610
 611 =head2 GetItemsInfo
 612
 613   @results = GetItemsInfo($biblionumber);
 614
 615 Returns information about items with the given biblionumber.
 616
 617 C<GetItemsInfo> returns a list of references-to-hash. Each element
 618 contains a number of keys. Most of them are attributes from the
 619 C<biblio>, C<biblioitems>, C<items>, and C<itemtypes> tables in the
 620 Koha database. Other keys include:
 621
 622 =over 2
 623
 624 =item C<$data-E<gt>{branchname}>
 625
 626 The name (not the code) of the branch to which the book belongs.
 627
 628 =item C<$data-E<gt>{datelastseen}>
 629
 630 This is simply C<items.datelastseen>, except that while the date is
 631 stored in YYYY-MM-DD format in the database, here it is converted to
 632 DD/MM/YYYY format. A NULL date is returned as C<//>.
 633
 634 =item C<$data-E<gt>{datedue}>
 635
 636 =item C<$data-E<gt>{class}>
 637
 638 This is the concatenation of C<biblioitems.classification>, the book's
 639 Dewey code, and C<biblioitems.subclass>.
 640
 641 =item C<$data-E<gt>{ocount}>
 642
 643 I think this is the number of copies of the book available.
 644
 645 =item C<$data-E<gt>{order}>
 646
 647 If this is set, it is set to C<One Order>.
 648
 649 =back
 650
 651 =cut
 652
 653 sub GetItemsInfo {
 654     my ( $biblionumber ) = @_;
 655     my $dbh   = C4::Context->dbh;
 656     require C4::Languages;
 657     my $language = C4::Languages::getlanguage();
 658     my $query = "
 659     SELECT items.*,
 660            biblio.*,
 661            biblioitems.volume,
 662            biblioitems.number,
 663            biblioitems.itemtype,
 664            biblioitems.isbn,
 665            biblioitems.issn,
 666            biblioitems.publicationyear,
 667            biblioitems.publishercode,
 668            biblioitems.volumedate,
 669            biblioitems.volumedesc,
 670            biblioitems.lccn,
 671            biblioitems.url,
 672            items.notforloan as itemnotforloan,
 673            issues.borrowernumber,
 674            issues.date_due as datedue,
 675            issues.onsite_checkout,
 676            borrowers.cardnumber,
 677            borrowers.surname,
 678            borrowers.firstname,
 679            borrowers.branchcode as bcode,
 680            serial.serialseq,
 681            serial.publisheddate,
 682            itemtypes.description,
 683            COALESCE( localization.translation, itemtypes.description ) AS translated_description,
 684            itemtypes.notforloan as notforloan_per_itemtype,
 685            holding.branchurl,
 686            holding.branchcode,
 687            holding.branchname,
 688            holding.opac_info as holding_branch_opac_info,
 689            home.opac_info as home_branch_opac_info,
 690            IF(tmp_holdsqueue.itemnumber,1,0) AS has_pending_hold
 691      FROM items
 692      LEFT JOIN branches AS holding ON items.holdingbranch = holding.branchcode
 693      LEFT JOIN branches AS home ON items.homebranch=home.branchcode
 694      LEFT JOIN biblio      ON      biblio.biblionumber     = items.biblionumber
 695      LEFT JOIN biblioitems ON biblioitems.biblioitemnumber = items.biblioitemnumber
 696      LEFT JOIN issues USING (itemnumber)
 697      LEFT JOIN borrowers USING (borrowernumber)
 698      LEFT JOIN serialitems USING (itemnumber)
 699      LEFT JOIN serial USING (serialid)
 700      LEFT JOIN itemtypes   ON   itemtypes.itemtype         = "
 701      . (C4::Context->preference('item-level_itypes') ? 'items.itype' : 'biblioitems.itemtype');
 702     $query .= q|
 703     LEFT JOIN tmp_holdsqueue USING (itemnumber)
 704     LEFT JOIN localization ON itemtypes.itemtype = localization.code
 705         AND localization.entity = 'itemtypes'
 706         AND localization.lang = ?
 707     |;
 708
 709     $query .= " WHERE items.biblionumber = ? ORDER BY home.branchname, items.enumchron, LPAD( items.copynumber, 8, '0' ), items.dateaccessioned DESC" ;
 710     my $sth = $dbh->prepare($query);
 711     $sth->execute($language, $biblionumber);
 712     my $i = 0;
 713     my @results;
 714     my $serial;
 715
 716     my $userenv = C4::Context->userenv;
 717     my $want_not_same_branch = C4::Context->preference("IndependentBranches") && !C4::Context->IsSuperLibrarian();
 718     while ( my $data = $sth->fetchrow_hashref ) {
 719         if ( $data->{borrowernumber} && $want_not_same_branch) {
 720             $data->{'NOTSAMEBRANCH'} = $data->{'bcode'} ne $userenv->{branch};
 721         }
 722
 723         $serial ||= $data->{'serial'};
 724
 725         my $descriptions;
 726         # get notforloan complete status if applicable
 727         $descriptions = Koha::AuthorisedValues->get_description_by_koha_field({frameworkcode => $data->{frameworkcode}, kohafield => 'items.notforloan', authorised_value => $data->{itemnotforloan} });
 728         $data->{notforloanvalue}     = $descriptions->{lib} // '';
 729         $data->{notforloanvalueopac} = $descriptions->{opac_description} // '';
 730
 731         # get restricted status and description if applicable
 732         $descriptions = Koha::AuthorisedValues->get_description_by_koha_field({frameworkcode => $data->{frameworkcode}, kohafield => 'items.restricted', authorised_value => $data->{restricted} });
 733         $data->{restrictedvalue}     = $descriptions->{lib} // '';
 734         $data->{restrictedvalueopac} = $descriptions->{opac_description} // '';
 735
 736         # my stack procedures
 737         $descriptions = Koha::AuthorisedValues->get_description_by_koha_field({frameworkcode => $data->{frameworkcode}, kohafield => 'items.stack', authorised_value => $data->{stack} });
 738         $data->{stack}          = $descriptions->{lib} // '';
 739
 740         # Find the last 3 people who borrowed this item.
 741         my $sth2 = $dbh->prepare("SELECT * FROM old_issues,borrowers
 742                                     WHERE itemnumber = ?
 743                                     AND old_issues.borrowernumber = borrowers.borrowernumber
 744                                     ORDER BY returndate DESC
 745                                     LIMIT 3");
 746         $sth2->execute($data->{'itemnumber'});
 747         my $ii = 0;
 748         while (my $data2 = $sth2->fetchrow_hashref()) {
 749             $data->{"timestamp$ii"} = $data2->{'timestamp'} if $data2->{'timestamp'};
 750             $data->{"card$ii"}      = $data2->{'cardnumber'} if $data2->{'cardnumber'};
 751             $data->{"borrower$ii"}  = $data2->{'borrowernumber'} if $data2->{'borrowernumber'};
 752             $ii++;
 753         }
 754
 755         $results[$i] = $data;
 756         $i++;
 757     }
 758
 759     return $serial
 760         ? sort { ($b->{'publisheddate'} || $b->{'enumchron'}) cmp ($a->{'publisheddate'} || $a->{'enumchron'}) } @results
 761         : @results;
 762 }
 763
 764 =head2 GetItemsLocationInfo
 765
 766   my @itemlocinfo = GetItemsLocationInfo($biblionumber);
 767
 768 Returns the branch names, shelving location and itemcallnumber for each item attached to the biblio in question
 769
 770 C<GetItemsInfo> returns a list of references-to-hash. Data returned:
 771
 772 =over 2
 773
 774 =item C<$data-E<gt>{homebranch}>
 775
 776 Branch Name of the item's homebranch
 777
 778 =item C<$data-E<gt>{holdingbranch}>
 779
 780 Branch Name of the item's holdingbranch
 781
 782 =item C<$data-E<gt>{location}>
 783
 784 Item's shelving location code
 785
 786 =item C<$data-E<gt>{location_intranet}>
 787
 788 The intranet description for the Shelving Location as set in authorised_values 'LOC'
 789
 790 =item C<$data-E<gt>{location_opac}>
 791
 792 The OPAC description for the Shelving Location as set in authorised_values 'LOC'.  Falls back to intranet description if no OPAC
 793 description is set.
 794
 795 =item C<$data-E<gt>{itemcallnumber}>
 796
 797 Item's itemcallnumber
 798
 799 =item C<$data-E<gt>{cn_sort}>
 800
 801 Item's call number normalized for sorting
 802
 803 =back
 804
 805 =cut
 806
 807 sub GetItemsLocationInfo {
 808         my $biblionumber = shift;
 809         my @results;
 810
 811         my $dbh = C4::Context->dbh;
 812         my $query = "SELECT a.branchname as homebranch, b.branchname as holdingbranch,
 813                             location, itemcallnumber, cn_sort
 814                      FROM items, branches as a, branches as b
 815                      WHERE homebranch = a.branchcode AND holdingbranch = b.branchcode
 816                      AND biblionumber = ?
 817                      ORDER BY cn_sort ASC";
 818         my $sth = $dbh->prepare($query);
 819         $sth->execute($biblionumber);
 820
 821         while ( my $data = $sth->fetchrow_hashref ) {
 822              my $av = Koha::AuthorisedValues->search({ category => 'LOC', authorised_value => $data->{location} });
 823              $av = $av->count ? $av->next : undef;
 824              $data->{location_intranet} = $av ? $av->lib : '';
 825              $data->{location_opac}     = $av ? $av->opac_description : '';
 826              push @results, $data;
 827         }
 828         return @results;
 829 }
 830
 831 =head2 GetHostItemsInfo
 832
 833     $hostiteminfo = GetHostItemsInfo($hostfield);
 834     Returns the iteminfo for items linked to records via a host field
 835
 836 =cut
 837
 838 sub GetHostItemsInfo {
 839     my ($record) = @_;
 840     my @returnitemsInfo;
 841
 842     if( !C4::Context->preference('EasyAnalyticalRecords') ) {
 843         return @returnitemsInfo;
 844     }
 845
 846     my @fields;
 847     if( C4::Context->preference('marcflavour') eq 'MARC21' ||
 848       C4::Context->preference('marcflavour') eq 'NORMARC') {
 849         @fields = $record->field('773');
 850     } elsif( C4::Context->preference('marcflavour') eq 'UNIMARC') {
 851         @fields = $record->field('461');
 852     }
 853
 854     foreach my $hostfield ( @fields ) {
 855         my $hostbiblionumber = $hostfield->subfield("0");
 856         my $linkeditemnumber = $hostfield->subfield("9");
 857         my @hostitemInfos = GetItemsInfo($hostbiblionumber);
 858         foreach my $hostitemInfo (@hostitemInfos) {
 859             if( $hostitemInfo->{itemnumber} eq $linkeditemnumber ) {
 860                 push @returnitemsInfo, $hostitemInfo;
 861                 last;
 862             }
 863         }
 864     }
 865     return @returnitemsInfo;
 866 }
 867
 868 =head2 get_hostitemnumbers_of
 869
 870   my @itemnumbers_of = get_hostitemnumbers_of($biblionumber);
 871
 872 Given a biblionumber, return the list of corresponding itemnumbers that are linked to it via host fields
 873
 874 Return a reference on a hash where key is a biblionumber and values are
 875 references on array of itemnumbers.
 876
 877 =cut
 878
 879
 880 sub get_hostitemnumbers_of {
 881     my ($biblionumber) = @_;
 882
 883     if( !C4::Context->preference('EasyAnalyticalRecords') ) {
 884         return ();
 885     }
 886
 887     my $marcrecord = C4::Biblio::GetMarcBiblio({ biblionumber => $biblionumber });
 888     return unless $marcrecord;
 889
 890     my ( @returnhostitemnumbers, $tag, $biblio_s, $item_s );
 891
 892     my $marcflavor = C4::Context->preference('marcflavour');
 893     if ( $marcflavor eq 'MARC21' || $marcflavor eq 'NORMARC' ) {
 894         $tag      = '773';
 895         $biblio_s = '0';
 896         $item_s   = '9';
 897     }
 898     elsif ( $marcflavor eq 'UNIMARC' ) {
 899         $tag      = '461';
 900         $biblio_s = '0';
 901         $item_s   = '9';
 902     }
 903
 904     foreach my $hostfield ( $marcrecord->field($tag) ) {
 905         my $hostbiblionumber = $hostfield->subfield($biblio_s);
 906         next unless $hostbiblionumber; # have tag, don't have $biblio_s subfield
 907         my $linkeditemnumber = $hostfield->subfield($item_s);
 908         if ( ! $linkeditemnumber ) {
 909             warn "ERROR biblionumber $biblionumber has 773^0, but doesn't have 9";
 910             next;
 911         }
 912         my $is_from_biblio = Koha::Items->search({ itemnumber => $linkeditemnumber, biblionumber => $hostbiblionumber });
 913         push @returnhostitemnumbers, $linkeditemnumber
 914           if $is_from_biblio;
 915     }
 916
 917     return @returnhostitemnumbers;
 918 }
 919
 920 =head2 GetHiddenItemnumbers
 921
 922     my @itemnumbers_to_hide = GetHiddenItemnumbers({ items => \@items, borcat => $category });
 923
 924 Given a list of items it checks which should be hidden from the OPAC given
 925 the current configuration. Returns a list of itemnumbers corresponding to
 926 those that should be hidden. Optionally takes a borcat parameter for certain borrower types
 927 to be excluded
 928
 929 =cut
 930
 931 sub GetHiddenItemnumbers {
 932     my $params = shift;
 933     my $items = $params->{items};
 934     if (my $exceptions = C4::Context->preference('OpacHiddenItemsExceptions') and $params->{'borcat'}){
 935         foreach my $except (split(/\|/, $exceptions)){
 936             if ($params->{'borcat'} eq $except){
 937                 return; # we don't hide anything for this borrower category
 938             }
 939         }
 940     }
 941     my @resultitems;
 942
 943     my $yaml = C4::Context->preference('OpacHiddenItems');
 944     return () if (! $yaml =~ /\S/ );
 945     $yaml = "$yaml\n\n"; # YAML is anal on ending \n. Surplus does not hurt
 946     my $hidingrules;
 947     eval {
 948         $hidingrules = YAML::Load($yaml);
 949     };
 950     if ($@) {
 951         warn "Unable to parse OpacHiddenItems syspref : $@";
 952         return ();
 953     }
 954     my $dbh = C4::Context->dbh;
 955
 956     # For each item
 957     foreach my $item (@$items) {
 958
 959         # We check each rule
 960         foreach my $field (keys %$hidingrules) {
 961             my $val;
 962             if (exists $item->{$field}) {
 963                 $val = $item->{$field};
 964             }
 965             else {
 966                 my $query = "SELECT $field from items where itemnumber = ?";
 967                 $val = $dbh->selectrow_array($query, undef, $item->{'itemnumber'});
 968             }
 969             $val = '' unless defined $val;
 970
 971             # If the results matches the values in the yaml file
 972             if (any { $val eq $_ } @{$hidingrules->{$field}}) {
 973
 974                 # We add the itemnumber to the list
 975                 push @resultitems, $item->{'itemnumber'};
 976
 977                 # If at least one rule matched for an item, no need to test the others
 978                 last;
 979             }
 980         }
 981     }
 982     return @resultitems;
 983 }
 984
 985 =head1 LIMITED USE FUNCTIONS
 986
 987 The following functions, while part of the public API,
 988 are not exported.  This is generally because they are
 989 meant to be used by only one script for a specific
 990 purpose, and should not be used in any other context
 991 without careful thought.
 992
 993 =cut
 994
 995 =head2 GetMarcItem
 996
 997   my $item_marc = GetMarcItem($biblionumber, $itemnumber);
 998
 999 Returns MARC::Record of the item passed in parameter.
1000 This function is meant for use only in C<cataloguing/additem.pl>,
1001 where it is needed to support that script's MARC-like
1002 editor.
1003
1004 =cut
1005
1006 sub GetMarcItem {
1007     my ( $biblionumber, $itemnumber ) = @_;
1008
1009     # GetMarcItem has been revised so that it does the following:
1010     #  1. Gets the item information from the items table.
1011     #  2. Converts it to a MARC field for storage in the bib record.
1012     #
1013     # The previous behavior was:
1014     #  1. Get the bib record.
1015     #  2. Return the MARC tag corresponding to the item record.
1016     #
1017     # The difference is that one treats the items row as authoritative,
1018     # while the other treats the MARC representation as authoritative
1019     # under certain circumstances.
1020
1021     my $item = Koha::Items->find($itemnumber) or return;
1022
1023     # Tack on 'items.' prefix to column names so that C4::Biblio::TransformKohaToMarc will work.
1024     # Also, don't emit a subfield if the underlying field is blank.
1025
1026     return Item2Marc($item->unblessed, $biblionumber);
1027
1028 }
1029 sub Item2Marc {
1030         my ($itemrecord,$biblionumber)=@_;
1031     my $mungeditem = {
1032         map {
1033             defined($itemrecord->{$_}) && $itemrecord->{$_} ne '' ? ("items.$_" => $itemrecord->{$_}) : ()
1034         } keys %{ $itemrecord }
1035     };
1036     my $framework = C4::Biblio::GetFrameworkCode( $biblionumber );
1037     my $itemmarc = C4::Biblio::TransformKohaToMarc( $mungeditem, { framework => $framework } );
1038     my ( $itemtag, $itemsubfield ) = C4::Biblio::GetMarcFromKohaField(
1039         "items.itemnumber", $framework,
1040     );
1041
1042     my $unlinked_item_subfields = _parse_unlinked_item_subfields_from_xml($mungeditem->{'items.more_subfields_xml'});
1043     if (defined $unlinked_item_subfields and $#$unlinked_item_subfields > -1) {
1044                 foreach my $field ($itemmarc->field($itemtag)){
1045             $field->add_subfields(@$unlinked_item_subfields);
1046         }
1047     }
1048         return $itemmarc;
1049 }
1050
1051 =head1 PRIVATE FUNCTIONS AND VARIABLES
1052
1053 The following functions are not meant to be called
1054 directly, but are documented in order to explain
1055 the inner workings of C<C4::Items>.
1056
1057 =cut
1058
1059 =head2 MoveItemFromBiblio
1060
1061   MoveItemFromBiblio($itenumber, $frombiblio, $tobiblio);
1062
1063 Moves an item from a biblio to another
1064
1065 Returns undef if the move failed or the biblionumber of the destination record otherwise
1066
1067 =cut
1068
1069 sub MoveItemFromBiblio {
1070     my ($itemnumber, $frombiblio, $tobiblio) = @_;
1071     my $dbh = C4::Context->dbh;
1072     my ( $tobiblioitem ) = $dbh->selectrow_array(q|
1073         SELECT biblioitemnumber
1074         FROM biblioitems
1075         WHERE biblionumber = ?
1076     |, undef, $tobiblio );
1077     my $return = $dbh->do(q|
1078         UPDATE items
1079         SET biblioitemnumber = ?,
1080             biblionumber = ?
1081         WHERE itemnumber = ?
1082             AND biblionumber = ?
1083     |, undef, $tobiblioitem, $tobiblio, $itemnumber, $frombiblio );
1084     if ($return == 1) {
1085         ModZebra( $tobiblio, "specialUpdate", "biblioserver" );
1086         ModZebra( $frombiblio, "specialUpdate", "biblioserver" );
1087             # Checking if the item we want to move is in an order
1088         require C4::Acquisition;
1089         my $order = C4::Acquisition::GetOrderFromItemnumber($itemnumber);
1090             if ($order) {
1091                     # Replacing the biblionumber within the order if necessary
1092                     $order->{'biblionumber'} = $tobiblio;
1093                 C4::Acquisition::ModOrder($order);
1094             }
1095
1096         # Update reserves, hold_fill_targets, tmp_holdsqueue and linktracker tables
1097         for my $table_name ( qw( reserves hold_fill_targets tmp_holdsqueue linktracker ) ) {
1098             $dbh->do( qq|
1099                 UPDATE $table_name
1100                 SET biblionumber = ?
1101                 WHERE itemnumber = ?
1102             |, undef, $tobiblio, $itemnumber );
1103         }
1104         return $tobiblio;
1105         }
1106     return;
1107 }
1108
1109 =head2 _marc_from_item_hash
1110
1111   my $item_marc = _marc_from_item_hash($item, $frameworkcode[, $unlinked_item_subfields]);
1112
1113 Given an item hash representing a complete item record,
1114 create a C<MARC::Record> object containing an embedded
1115 tag representing that item.
1116
1117 The third, optional parameter C<$unlinked_item_subfields> is
1118 an arrayref of subfields (not mapped to C<items> fields per the
1119 framework) to be added to the MARC representation
1120 of the item.
1121
1122 =cut
1123
1124 sub _marc_from_item_hash {
1125     my $item = shift;
1126     my $frameworkcode = shift;
1127     my $unlinked_item_subfields;
1128     if (@_) {
1129         $unlinked_item_subfields = shift;
1130     }
1131
1132     # Tack on 'items.' prefix to column names so lookup from MARC frameworks will work
1133     # Also, don't emit a subfield if the underlying field is blank.
1134     my $mungeditem = { map {  (defined($item->{$_}) and $item->{$_} ne '') ?
1135                                 (/^items\./ ? ($_ => $item->{$_}) : ("items.$_" => $item->{$_}))
1136                                 : ()  } keys %{ $item } };
1137
1138     my $item_marc = MARC::Record->new();
1139     foreach my $item_field ( keys %{$mungeditem} ) {
1140         my ( $tag, $subfield ) = C4::Biblio::GetMarcFromKohaField( $item_field );
1141         next unless defined $tag and defined $subfield;    # skip if not mapped to MARC field
1142         my @values = split(/\s?\|\s?/, $mungeditem->{$item_field}, -1);
1143         foreach my $value (@values){
1144             if ( my $field = $item_marc->field($tag) ) {
1145                     $field->add_subfields( $subfield => $value );
1146             } else {
1147                 my $add_subfields = [];
1148                 if (defined $unlinked_item_subfields and ref($unlinked_item_subfields) eq 'ARRAY' and $#$unlinked_item_subfields > -1) {
1149                     $add_subfields = $unlinked_item_subfields;
1150             }
1151             $item_marc->add_fields( $tag, " ", " ", $subfield => $value, @$add_subfields );
1152             }
1153         }
1154     }
1155
1156     return $item_marc;
1157 }
1158
1159 =head2 _repack_item_errors
1160
1161 Add an error message hash generated by C<CheckItemPreSave>
1162 to a list of errors.
1163
1164 =cut
1165
1166 sub _repack_item_errors {
1167     my $item_sequence_num = shift;
1168     my $item_ref = shift;
1169     my $error_ref = shift;
1170
1171     my @repacked_errors = ();
1172
1173     foreach my $error_code (sort keys %{ $error_ref }) {
1174         my $repacked_error = {};
1175         $repacked_error->{'item_sequence'} = $item_sequence_num;
1176         $repacked_error->{'item_barcode'} = exists($item_ref->{'barcode'}) ? $item_ref->{'barcode'} : '';
1177         $repacked_error->{'error_code'} = $error_code;
1178         $repacked_error->{'error_information'} = $error_ref->{$error_code};
1179         push @repacked_errors, $repacked_error;
1180     }
1181
1182     return @repacked_errors;
1183 }
1184
1185 =head2 _get_unlinked_item_subfields
1186
1187   my $unlinked_item_subfields = _get_unlinked_item_subfields($original_item_marc, $frameworkcode);
1188
1189 =cut
1190
1191 sub _get_unlinked_item_subfields {
1192     my $original_item_marc = shift;
1193     my $frameworkcode = shift;
1194
1195     my $marcstructure = C4::Biblio::GetMarcStructure(1, $frameworkcode, { unsafe => 1 });
1196
1197     # assume that this record has only one field, and that that
1198     # field contains only the item information
1199     my $subfields = [];
1200     my @fields = $original_item_marc->fields();
1201     if ($#fields > -1) {
1202         my $field = $fields[0];
1203             my $tag = $field->tag();
1204         foreach my $subfield ($field->subfields()) {
1205             if (defined $subfield->[1] and
1206                 $subfield->[1] ne '' and
1207                 !$marcstructure->{$tag}->{$subfield->[0]}->{'kohafield'}) {
1208                 push @$subfields, $subfield->[0] => $subfield->[1];
1209             }
1210         }
1211     }
1212     return $subfields;
1213 }
1214
1215 =head2 _get_unlinked_subfields_xml
1216
1217   my $unlinked_subfields_xml = _get_unlinked_subfields_xml($unlinked_item_subfields);
1218
1219 =cut
1220
1221 sub _get_unlinked_subfields_xml {
1222     my $unlinked_item_subfields = shift;
1223
1224     my $xml;
1225     if (defined $unlinked_item_subfields and ref($unlinked_item_subfields) eq 'ARRAY' and $#$unlinked_item_subfields > -1) {
1226         my $marc = MARC::Record->new();
1227         # use of tag 999 is arbitrary, and doesn't need to match the item tag
1228         # used in the framework
1229         $marc->append_fields(MARC::Field->new('999', ' ', ' ', @$unlinked_item_subfields));
1230         $marc->encoding("UTF-8");
1231         $xml = $marc->as_xml("USMARC");
1232     }
1233
1234     return $xml;
1235 }
1236
1237 =head2 _parse_unlinked_item_subfields_from_xml
1238
1239   my $unlinked_item_subfields = _parse_unlinked_item_subfields_from_xml($whole_item->{'more_subfields_xml'}):
1240
1241 =cut
1242
1243 sub  _parse_unlinked_item_subfields_from_xml {
1244     my $xml = shift;
1245     require C4::Charset;
1246     return unless defined $xml and $xml ne "";
1247     my $marc = MARC::Record->new_from_xml(C4::Charset::StripNonXmlChars($xml),'UTF-8');
1248     my $unlinked_subfields = [];
1249     my @fields = $marc->fields();
1250     if ($#fields > -1) {
1251         foreach my $subfield ($fields[0]->subfields()) {
1252             push @$unlinked_subfields, $subfield->[0] => $subfield->[1];
1253         }
1254     }
1255     return $unlinked_subfields;
1256 }
1257
1258 =head2 GetAnalyticsCount
1259
1260   $count= &GetAnalyticsCount($itemnumber)
1261
1262 counts Usage of itemnumber in Analytical bibliorecords.
1263
1264 =cut
1265
1266 sub GetAnalyticsCount {
1267     my ($itemnumber) = @_;
1268
1269     ### ZOOM search here
1270     my $query;
1271     $query= "hi=".$itemnumber;
1272     my $searcher = Koha::SearchEngine::Search->new({index => $Koha::SearchEngine::BIBLIOS_INDEX});
1273     my ($err,$res,$result) = $searcher->simple_search_compat($query,0,10);
1274     return ($result);
1275 }
1276
1277 sub _SearchItems_build_where_fragment {
1278     my ($filter) = @_;
1279
1280     my $dbh = C4::Context->dbh;
1281
1282     my $where_fragment;
1283     if (exists($filter->{conjunction})) {
1284         my (@where_strs, @where_args);
1285         foreach my $f (@{ $filter->{filters} }) {
1286             my $fragment = _SearchItems_build_where_fragment($f);
1287             if ($fragment) {
1288                 push @where_strs, $fragment->{str};
1289                 push @where_args, @{ $fragment->{args} };
1290             }
1291         }
1292         my $where_str = '';
1293         if (@where_strs) {
1294             $where_str = '(' . join (' ' . $filter->{conjunction} . ' ', @where_strs) . ')';
1295             $where_fragment = {
1296                 str => $where_str,
1297                 args => \@where_args,
1298             };
1299         }
1300     } else {
1301         my @columns = Koha::Database->new()->schema()->resultset('Item')->result_source->columns;
1302         push @columns, Koha::Database->new()->schema()->resultset('Biblio')->result_source->columns;
1303         push @columns, Koha::Database->new()->schema()->resultset('Biblioitem')->result_source->columns;
1304         my @operators = qw(= != > < >= <= like);
1305         my $field = $filter->{field} // q{};
1306         if ( (0 < grep { $_ eq $field } @columns) or (substr($field, 0, 5) eq 'marc:') ) {
1307             my $op = $filter->{operator};
1308             my $query = $filter->{query};
1309
1310             if (!$op or (0 == grep { $_ eq $op } @operators)) {
1311                 $op = '='; # default operator
1312             }
1313
1314             my $column;
1315             if ($field =~ /^marc:(\d{3})(?:\$(\w))?$/) {
1316                 my $marcfield = $1;
1317                 my $marcsubfield = $2;
1318                 my ($kohafield) = $dbh->selectrow_array(q|
1319                     SELECT kohafield FROM marc_subfield_structure
1320                     WHERE tagfield=? AND tagsubfield=? AND frameworkcode=''
1321                 |, undef, $marcfield, $marcsubfield);
1322
1323                 if ($kohafield) {
1324                     $column = $kohafield;
1325                 } else {
1326                     # MARC field is not linked to a DB field so we need to use
1327                     # ExtractValue on marcxml from biblio_metadata or
1328                     # items.more_subfields_xml, depending on the MARC field.
1329                     my $xpath;
1330                     my $sqlfield;
1331                     my ($itemfield) = C4::Biblio::GetMarcFromKohaField('items.itemnumber');
1332                     if ($marcfield eq $itemfield) {
1333                         $sqlfield = 'more_subfields_xml';
1334                         $xpath = '//record/datafield/subfield[@code="' . $marcsubfield . '"]';
1335                     } else {
1336                         $sqlfield = 'metadata'; # From biblio_metadata
1337                         if ($marcfield < 10) {
1338                             $xpath = "//record/controlfield[\@tag=\"$marcfield\"]";
1339                         } else {
1340                             $xpath = "//record/datafield[\@tag=\"$marcfield\"]/subfield[\@code=\"$marcsubfield\"]";
1341                         }
1342                     }
1343                     $column = "ExtractValue($sqlfield, '$xpath')";
1344                 }
1345             } elsif ($field eq 'issues') {
1346                 # Consider NULL as 0 for issues count
1347                 $column = 'COALESCE(issues,0)';
1348             } else {
1349                 $column = $field;
1350             }
1351
1352             if (ref $query eq 'ARRAY') {
1353                 if ($op eq '=') {
1354                     $op = 'IN';
1355                 } elsif ($op eq '!=') {
1356                     $op = 'NOT IN';
1357                 }
1358                 $where_fragment = {
1359                     str => "$column $op (" . join (',', ('?') x @$query) . ")",
1360                     args => $query,
1361                 };
1362             } else {
1363                 $where_fragment = {
1364                     str => "$column $op ?",
1365                     args => [ $query ],
1366                 };
1367             }
1368         }
1369     }
1370
1371     return $where_fragment;
1372 }
1373
1374 =head2 SearchItems
1375
1376     my ($items, $total) = SearchItems($filter, $params);
1377
1378 Perform a search among items
1379
1380 $filter is a reference to a hash which can be a filter, or a combination of filters.
1381
1382 A filter has the following keys:
1383
1384 =over 2
1385
1386 =item * field: the name of a SQL column in table items
1387
1388 =item * query: the value to search in this column
1389
1390 =item * operator: comparison operator. Can be one of = != > < >= <= like
1391
1392 =back
1393
1394 A combination of filters hash the following keys:
1395
1396 =over 2
1397
1398 =item * conjunction: 'AND' or 'OR'
1399
1400 =item * filters: array ref of filters
1401
1402 =back
1403
1404 $params is a reference to a hash that can contain the following parameters:
1405
1406 =over 2
1407
1408 =item * rows: Number of items to return. 0 returns everything (default: 0)
1409
1410 =item * page: Page to return (return items from (page-1)*rows to (page*rows)-1)
1411                (default: 1)
1412
1413 =item * sortby: A SQL column name in items table to sort on
1414
1415 =item * sortorder: 'ASC' or 'DESC'
1416
1417 =back
1418
1419 =cut
1420
1421 sub SearchItems {
1422     my ($filter, $params) = @_;
1423
1424     $filter //= {};
1425     $params //= {};
1426     return unless ref $filter eq 'HASH';
1427     return unless ref $params eq 'HASH';
1428
1429     # Default parameters
1430     $params->{rows} ||= 0;
1431     $params->{page} ||= 1;
1432     $params->{sortby} ||= 'itemnumber';
1433     $params->{sortorder} ||= 'ASC';
1434
1435     my ($where_str, @where_args);
1436     my $where_fragment = _SearchItems_build_where_fragment($filter);
1437     if ($where_fragment) {
1438         $where_str = $where_fragment->{str};
1439         @where_args = @{ $where_fragment->{args} };
1440     }
1441
1442     my $dbh = C4::Context->dbh;
1443     my $query = q{
1444         SELECT SQL_CALC_FOUND_ROWS items.*
1445         FROM items
1446           LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber
1447           LEFT JOIN biblioitems ON biblioitems.biblioitemnumber = items.biblioitemnumber
1448           LEFT JOIN biblio_metadata ON biblio_metadata.biblionumber = biblio.biblionumber
1449           WHERE 1
1450     };
1451     if (defined $where_str and $where_str ne '') {
1452         $query .= qq{ AND $where_str };
1453     }
1454
1455     $query .= q{ AND biblio_metadata.format = 'marcxml' AND biblio_metadata.schema = ? };
1456     push @where_args, C4::Context->preference('marcflavour');
1457
1458     my @columns = Koha::Database->new()->schema()->resultset('Item')->result_source->columns;
1459     push @columns, Koha::Database->new()->schema()->resultset('Biblio')->result_source->columns;
1460     push @columns, Koha::Database->new()->schema()->resultset('Biblioitem')->result_source->columns;
1461     my $sortby = (0 < grep {$params->{sortby} eq $_} @columns)
1462         ? $params->{sortby} : 'itemnumber';
1463     my $sortorder = (uc($params->{sortorder}) eq 'ASC') ? 'ASC' : 'DESC';
1464     $query .= qq{ ORDER BY $sortby $sortorder };
1465
1466     my $rows = $params->{rows};
1467     my @limit_args;
1468     if ($rows > 0) {
1469         my $offset = $rows * ($params->{page}-1);
1470         $query .= qq { LIMIT ?, ? };
1471         push @limit_args, $offset, $rows;
1472     }
1473
1474     my $sth = $dbh->prepare($query);
1475     my $rv = $sth->execute(@where_args, @limit_args);
1476
1477     return unless ($rv);
1478     my ($total_rows) = $dbh->selectrow_array(q{ SELECT FOUND_ROWS() });
1479
1480     return ($sth->fetchall_arrayref({}), $total_rows);
1481 }
1482
1483
1484 =head1  OTHER FUNCTIONS
1485
1486 =head2 _find_value
1487
1488   ($indicators, $value) = _find_value($tag, $subfield, $record,$encoding);
1489
1490 Find the given $subfield in the given $tag in the given
1491 MARC::Record $record.  If the subfield is found, returns
1492 the (indicators, value) pair; otherwise, (undef, undef) is
1493 returned.
1494
1495 PROPOSITION :
1496 Such a function is used in addbiblio AND additem and serial-edit and maybe could be used in Authorities.
1497 I suggest we export it from this module.
1498
1499 =cut
1500
1501 sub _find_value {
1502     my ( $tagfield, $insubfield, $record, $encoding ) = @_;
1503     my @result;
1504     my $indicator;
1505     if ( $tagfield < 10 ) {
1506         if ( $record->field($tagfield) ) {
1507             push @result, $record->field($tagfield)->data();
1508         } else {
1509             push @result, "";
1510         }
1511     } else {
1512         foreach my $field ( $record->field($tagfield) ) {
1513             my @subfields = $field->subfields();
1514             foreach my $subfield (@subfields) {
1515                 if ( @$subfield[0] eq $insubfield ) {
1516                     push @result, @$subfield[1];
1517                     $indicator = $field->indicator(1) . $field->indicator(2);
1518                 }
1519             }
1520         }
1521     }
1522     return ( $indicator, @result );
1523 }
1524
1525
1526 =head2 PrepareItemrecordDisplay
1527
1528   PrepareItemrecordDisplay($itemrecord,$bibnum,$itemumber,$frameworkcode);
1529
1530 Returns a hash with all the fields for Display a given item data in a template
1531
1532 The $frameworkcode returns the item for the given frameworkcode, ONLY if bibnum is not provided
1533
1534 =cut
1535
1536 sub PrepareItemrecordDisplay {
1537
1538     my ( $bibnum, $itemnum, $defaultvalues, $frameworkcode ) = @_;
1539
1540     my $dbh = C4::Context->dbh;
1541     $frameworkcode = C4::Biblio::GetFrameworkCode($bibnum) if $bibnum;
1542     my ( $itemtagfield, $itemtagsubfield ) = C4::Biblio::GetMarcFromKohaField( "items.itemnumber" );
1543
1544     # Note: $tagslib obtained from GetMarcStructure() in 'unsafe' mode is
1545     # a shared data structure. No plugin (including custom ones) should change
1546     # its contents. See also GetMarcStructure.
1547     my $tagslib = GetMarcStructure( 1, $frameworkcode, { unsafe => 1 } );
1548
1549     # return nothing if we don't have found an existing framework.
1550     return q{} unless $tagslib;
1551     my $itemrecord;
1552     if ($itemnum) {
1553         $itemrecord = C4::Items::GetMarcItem( $bibnum, $itemnum );
1554     }
1555     my @loop_data;
1556
1557     my $branch_limit = C4::Context->userenv ? C4::Context->userenv->{"branch"} : "";
1558     my $query = qq{
1559         SELECT authorised_value,lib FROM authorised_values
1560     };
1561     $query .= qq{
1562         LEFT JOIN authorised_values_branches ON ( id = av_id )
1563     } if $branch_limit;
1564     $query .= qq{
1565         WHERE category = ?
1566     };
1567     $query .= qq{ AND ( branchcode = ? OR branchcode IS NULL )} if $branch_limit;
1568     $query .= qq{ ORDER BY lib};
1569     my $authorised_values_sth = $dbh->prepare( $query );
1570     foreach my $tag ( sort keys %{$tagslib} ) {
1571         if ( $tag ne '' ) {
1572
1573             # loop through each subfield
1574             my $cntsubf;
1575             foreach my $subfield ( sort keys %{ $tagslib->{$tag} } ) {
1576                 next if IsMarcStructureInternal($tagslib->{$tag}{$subfield});
1577                 next unless ( $tagslib->{$tag}->{$subfield}->{'tab'} );
1578                 next if ( $tagslib->{$tag}->{$subfield}->{'tab'} ne "10" );
1579                 my %subfield_data;
1580                 $subfield_data{tag}           = $tag;
1581                 $subfield_data{subfield}      = $subfield;
1582                 $subfield_data{countsubfield} = $cntsubf++;
1583                 $subfield_data{kohafield}     = $tagslib->{$tag}->{$subfield}->{'kohafield'};
1584                 $subfield_data{id}            = "tag_".$tag."_subfield_".$subfield."_".int(rand(1000000));
1585
1586                 #        $subfield_data{marc_lib}=$tagslib->{$tag}->{$subfield}->{lib};
1587                 $subfield_data{marc_lib}   = $tagslib->{$tag}->{$subfield}->{lib};
1588                 $subfield_data{mandatory}  = $tagslib->{$tag}->{$subfield}->{mandatory};
1589                 $subfield_data{repeatable} = $tagslib->{$tag}->{$subfield}->{repeatable};
1590                 $subfield_data{hidden}     = "display:none"
1591                   if ( ( $tagslib->{$tag}->{$subfield}->{hidden} > 4 )
1592                     || ( $tagslib->{$tag}->{$subfield}->{hidden} < -4 ) );
1593                 my ( $x, $defaultvalue );
1594                 if ($itemrecord) {
1595                     ( $x, $defaultvalue ) = _find_value( $tag, $subfield, $itemrecord );
1596                 }
1597                 $defaultvalue = $tagslib->{$tag}->{$subfield}->{defaultvalue} unless $defaultvalue;
1598                 if ( !defined $defaultvalue ) {
1599                     $defaultvalue = q||;
1600                 } else {
1601                     $defaultvalue =~ s/"/&quot;/g;
1602                 }
1603
1604                 my $maxlength = $tagslib->{$tag}->{$subfield}->{maxlength};
1605
1606                 # search for itemcallnumber if applicable
1607                 if ( $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.itemcallnumber'
1608                     && C4::Context->preference('itemcallnumber') && $itemrecord) {
1609                     foreach my $itemcn_pref (split(/,/,C4::Context->preference('itemcallnumber'))){
1610                         my $CNtag      = substr( $itemcn_pref, 0, 3 );
1611                         next unless my $field = $itemrecord->field($CNtag);
1612                         my $CNsubfields = substr( $itemcn_pref, 3 );
1613                         $defaultvalue = $field->as_string( $CNsubfields, ' ');
1614                         last if $defaultvalue;
1615                     }
1616                 }
1617                 if (   $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.itemcallnumber'
1618                     && $defaultvalues
1619                     && $defaultvalues->{'callnumber'} ) {
1620                     if( $itemrecord and $defaultvalues and not $itemrecord->subfield($tag,$subfield) ){
1621                         # if the item record exists, only use default value if the item has no callnumber
1622                         $defaultvalue = $defaultvalues->{callnumber};
1623                     } elsif ( !$itemrecord and $defaultvalues ) {
1624                         # if the item record *doesn't* exists, always use the default value
1625                         $defaultvalue = $defaultvalues->{callnumber};
1626                     }
1627                 }
1628                 if (   ( $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.holdingbranch' || $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.homebranch' )
1629                     && $defaultvalues
1630                     && $defaultvalues->{'branchcode'} ) {
1631                     if ( $itemrecord and $defaultvalues and not $itemrecord->subfield($tag,$subfield) ) {
1632                         $defaultvalue = $defaultvalues->{branchcode};
1633                     }
1634                 }
1635                 if (   ( $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.location' )
1636                     && $defaultvalues
1637                     && $defaultvalues->{'location'} ) {
1638
1639                     if ( $itemrecord and $defaultvalues and not $itemrecord->subfield($tag,$subfield) ) {
1640                         # if the item record exists, only use default value if the item has no locationr
1641                         $defaultvalue = $defaultvalues->{location};
1642                     } elsif ( !$itemrecord and $defaultvalues ) {
1643                         # if the item record *doesn't* exists, always use the default value
1644                         $defaultvalue = $defaultvalues->{location};
1645                     }
1646                 }
1647                 if ( $tagslib->{$tag}->{$subfield}->{authorised_value} ) {
1648                     my @authorised_values;
1649                     my %authorised_lib;
1650
1651                     # builds list, depending on authorised value...
1652                     #---- branch
1653                     if ( $tagslib->{$tag}->{$subfield}->{'authorised_value'} eq "branches" ) {
1654                         if (   ( C4::Context->preference("IndependentBranches") )
1655                             && !C4::Context->IsSuperLibrarian() ) {
1656                             my $sth = $dbh->prepare( "SELECT branchcode,branchname FROM branches WHERE branchcode = ? ORDER BY branchname" );
1657                             $sth->execute( C4::Context->userenv->{branch} );
1658                             push @authorised_values, ""
1659                               unless ( $tagslib->{$tag}->{$subfield}->{mandatory} );
1660                             while ( my ( $branchcode, $branchname ) = $sth->fetchrow_array ) {
1661                                 push @authorised_values, $branchcode;
1662                                 $authorised_lib{$branchcode} = $branchname;
1663                             }
1664                         } else {
1665                             my $sth = $dbh->prepare( "SELECT branchcode,branchname FROM branches ORDER BY branchname" );
1666                             $sth->execute;
1667                             push @authorised_values, ""
1668                               unless ( $tagslib->{$tag}->{$subfield}->{mandatory} );
1669                             while ( my ( $branchcode, $branchname ) = $sth->fetchrow_array ) {
1670                                 push @authorised_values, $branchcode;
1671                                 $authorised_lib{$branchcode} = $branchname;
1672                             }
1673                         }
1674
1675                         $defaultvalue = C4::Context->userenv ? C4::Context->userenv->{branch} : undef;
1676                         if ( $defaultvalues and $defaultvalues->{branchcode} ) {
1677                             $defaultvalue = $defaultvalues->{branchcode};
1678                         }
1679
1680                         #----- itemtypes
1681                     } elsif ( $tagslib->{$tag}->{$subfield}->{authorised_value} eq "itemtypes" ) {
1682                         my $itemtypes = Koha::ItemTypes->search_with_localization;
1683                         push @authorised_values, ""
1684                           unless ( $tagslib->{$tag}->{$subfield}->{mandatory} );
1685                         while ( my $itemtype = $itemtypes->next ) {
1686                             push @authorised_values, $itemtype->itemtype;
1687                             $authorised_lib{$itemtype->itemtype} = $itemtype->translated_description;
1688                         }
1689                         if ($defaultvalues && $defaultvalues->{'itemtype'}) {
1690                             $defaultvalue = $defaultvalues->{'itemtype'};
1691                         }
1692
1693                         #---- class_sources
1694                     } elsif ( $tagslib->{$tag}->{$subfield}->{authorised_value} eq "cn_source" ) {
1695                         push @authorised_values, "" unless ( $tagslib->{$tag}->{$subfield}->{mandatory} );
1696
1697                         my $class_sources = GetClassSources();
1698                         my $default_source = $defaultvalue || C4::Context->preference("DefaultClassificationSource");
1699
1700                         foreach my $class_source (sort keys %$class_sources) {
1701                             next unless $class_sources->{$class_source}->{'used'} or
1702                                         ($class_source eq $default_source);
1703                             push @authorised_values, $class_source;
1704                             $authorised_lib{$class_source} = $class_sources->{$class_source}->{'description'};
1705                         }
1706
1707                         $defaultvalue = $default_source;
1708
1709                         #---- "true" authorised value
1710                     } else {
1711                         $authorised_values_sth->execute(
1712                             $tagslib->{$tag}->{$subfield}->{authorised_value},
1713                             $branch_limit ? $branch_limit : ()
1714                         );
1715                         push @authorised_values, ""
1716                           unless ( $tagslib->{$tag}->{$subfield}->{mandatory} );
1717                         while ( my ( $value, $lib ) = $authorised_values_sth->fetchrow_array ) {
1718                             push @authorised_values, $value;
1719                             $authorised_lib{$value} = $lib;
1720                         }
1721                     }
1722                     $subfield_data{marc_value} = {
1723                         type    => 'select',
1724                         values  => \@authorised_values,
1725                         default => $defaultvalue // q{},
1726                         labels  => \%authorised_lib,
1727                     };
1728                 } elsif ( $tagslib->{$tag}->{$subfield}->{value_builder} ) {
1729                 # it is a plugin
1730                     require Koha::FrameworkPlugin;
1731                     my $plugin = Koha::FrameworkPlugin->new({
1732                         name => $tagslib->{$tag}->{$subfield}->{value_builder},
1733                         item_style => 1,
1734                     });
1735                     my $pars = { dbh => $dbh, record => undef, tagslib =>$tagslib, id => $subfield_data{id}, tabloop => undef };
1736                     $plugin->build( $pars );
1737                     if ( $itemrecord and my $field = $itemrecord->field($tag) ) {
1738                         $defaultvalue = $field->subfield($subfield) || q{};
1739                     }
1740                     if( !$plugin->errstr ) {
1741                         #TODO Move html to template; see report 12176/13397
1742                         my $tab= $plugin->noclick? '-1': '';
1743                         my $class= $plugin->noclick? ' disabled': '';
1744                         my $title= $plugin->noclick? 'No popup': 'Tag editor';
1745                         $subfield_data{marc_value} = qq[<input type="text" id="$subfield_data{id}" name="field_value" class="input_marceditor" size="50" maxlength="$maxlength" value="$defaultvalue" /><a href="#" id="buttonDot_$subfield_data{id}" class="buttonDot $class" title="$title">...</a>\n].$plugin->javascript;
1746                     } else {
1747                         warn $plugin->errstr;
1748                         $subfield_data{marc_value} = qq(<input type="text" id="$subfield_data{id}" name="field_value" class="input_marceditor" size="50" maxlength="$maxlength" value="$defaultvalue" />); # supply default input form
1749                     }
1750                 }
1751                 elsif ( $tag eq '' ) {       # it's an hidden field
1752                     $subfield_data{marc_value} = qq(<input type="hidden" id="$subfield_data{id}" name="field_value" class="input_marceditor" size="50" maxlength="$maxlength" value="$defaultvalue" />);
1753                 }
1754                 elsif ( $tagslib->{$tag}->{$subfield}->{'hidden'} ) {   # FIXME: shouldn't input type be "hidden" ?
1755                     $subfield_data{marc_value} = qq(<input type="text" id="$subfield_data{id}" name="field_value" class="input_marceditor" size="50" maxlength="$maxlength" value="$defaultvalue" />);
1756                 }
1757                 elsif ( length($defaultvalue) > 100
1758                             or (C4::Context->preference("marcflavour") eq "UNIMARC" and
1759                                   300 <= $tag && $tag < 400 && $subfield eq 'a' )
1760                             or (C4::Context->preference("marcflavour") eq "MARC21"  and
1761                                   500 <= $tag && $tag < 600                     )
1762                           ) {
1763                     # oversize field (textarea)
1764                     $subfield_data{marc_value} = qq(<textarea id="$subfield_data{id}" name="field_value" class="input_marceditor" size="50" maxlength="$maxlength">$defaultvalue</textarea>\n");
1765                 } else {
1766                     $subfield_data{marc_value} = qq(<input type="text" id="$subfield_data{id}" name="field_value" class="input_marceditor" size="50" maxlength="$maxlength" value="$defaultvalue" />);
1767                 }
1768                 push( @loop_data, \%subfield_data );
1769             }
1770         }
1771     }
1772     my $itemnumber;
1773     if ( $itemrecord && $itemrecord->field($itemtagfield) ) {
1774         $itemnumber = $itemrecord->subfield( $itemtagfield, $itemtagsubfield );
1775     }
1776     return {
1777         'itemtagfield'    => $itemtagfield,
1778         'itemtagsubfield' => $itemtagsubfield,
1779         'itemnumber'      => $itemnumber,
1780         'iteminformation' => \@loop_data
1781     };
1782 }
1783
1784 sub ToggleNewStatus {
1785     my ( $params ) = @_;
1786     my @rules = @{ $params->{rules} };
1787     my $report_only = $params->{report_only};
1788
1789     my $dbh = C4::Context->dbh;
1790     my @errors;
1791     my @item_columns = map { "items.$_" } Koha::Items->columns;
1792     my @biblioitem_columns = map { "biblioitems.$_" } Koha::Biblioitems->columns;
1793     my $report;
1794     for my $rule ( @rules ) {
1795         my $age = $rule->{age};
1796         my $conditions = $rule->{conditions};
1797         my $substitutions = $rule->{substitutions};
1798         foreach ( @$substitutions ) {
1799             ( $_->{item_field} ) = ( $_->{field} =~ /items\.(.*)/ );
1800         }
1801         my @params;
1802
1803         my $query = q|
1804             SELECT items.*
1805             FROM items
1806             LEFT JOIN biblioitems ON biblioitems.biblionumber = items.biblionumber
1807             WHERE 1
1808         |;
1809         for my $condition ( @$conditions ) {
1810             if (
1811                  grep { $_ eq $condition->{field} } @item_columns
1812               or grep { $_ eq $condition->{field} } @biblioitem_columns
1813             ) {
1814                 if ( $condition->{value} =~ /\|/ ) {
1815                     my @values = split /\|/, $condition->{value};
1816                     $query .= qq| AND $condition->{field} IN (|
1817                         . join( ',', ('?') x scalar @values )
1818                         . q|)|;
1819                     push @params, @values;
1820                 } else {
1821                     $query .= qq| AND $condition->{field} = ?|;
1822                     push @params, $condition->{value};
1823                 }
1824             }
1825         }
1826         if ( defined $age ) {
1827             $query .= q| AND TO_DAYS(NOW()) - TO_DAYS(dateaccessioned) >= ? |;
1828             push @params, $age;
1829         }
1830         my $sth = $dbh->prepare($query);
1831         $sth->execute( @params );
1832         while ( my $values = $sth->fetchrow_hashref ) {
1833             my $biblionumber = $values->{biblionumber};
1834             my $itemnumber = $values->{itemnumber};
1835             my $item = Koha::Items->find($itemnumber);
1836             for my $substitution ( @$substitutions ) {
1837                 my $field = $substitution->{item_field};
1838                 my $value = $substitution->{value};
1839                 next unless $substitution->{field};
1840                 next if ( defined $values->{ $substitution->{item_field} } and $values->{ $substitution->{item_field} } eq $substitution->{value} );
1841                 $item->$field($value);
1842                 push @{ $report->{$itemnumber} }, $substitution;
1843             }
1844             $item->store unless $report_only;
1845         }
1846     }
1847
1848     return $report;
1849 }
1850
1851 1;