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 strict;
  22 #use warnings; FIXME - Bug 2505
  23
  24 use Carp;
  25 use C4::Context;
  26 use C4::Koha;
  27 use C4::Biblio;
  28 use Koha::DateUtils;
  29 use MARC::Record;
  30 use C4::ClassSource;
  31 use C4::Log;
  32 use List::MoreUtils qw/any/;
  33 use YAML qw/Load/;
  34 use DateTime::Format::MySQL;
  35 use Data::Dumper; # used as part of logging item record changes, not just for
  36                   # debugging; so please don't remove this
  37
  38 use Koha::AuthorisedValues;
  39 use Koha::DateUtils qw/dt_from_string/;
  40 use Koha::Database;
  41
  42 use Koha::Biblioitems;
  43 use Koha::Items;
  44 use Koha::ItemTypes;
  45 use Koha::SearchEngine;
  46 use Koha::SearchEngine::Search;
  47 use Koha::Libraries;
  48
  49 use vars qw(@ISA @EXPORT);
  50
  51 BEGIN {
  52
  53         require Exporter;
  54     @ISA = qw( Exporter );
  55
  56     # function exports
  57     @EXPORT = qw(
  58         GetItem
  59         AddItemFromMarc
  60         AddItem
  61         AddItemBatchFromMarc
  62         ModItemFromMarc
  63     Item2Marc
  64         ModItem
  65         ModDateLastSeen
  66         ModItemTransfer
  67         DelItem
  68
  69         CheckItemPreSave
  70
  71         GetItemsForInventory
  72         GetItemsByBiblioitemnumber
  73         GetItemsInfo
  74         GetItemsLocationInfo
  75         GetHostItemsInfo
  76         GetItemnumbersForBiblio
  77         get_hostitemnumbers_of
  78         GetItemnumberFromBarcode
  79         GetBarcodeFromItemnumber
  80         GetHiddenItemnumbers
  81         ItemSafeToDelete
  82         DelItemCheck
  83     MoveItemFromBiblio
  84     GetLatestAcquisitions
  85
  86         CartToShelf
  87         ShelfToCart
  88
  89         GetAnalyticsCount
  90
  91         SearchItemsByField
  92         SearchItems
  93
  94         PrepareItemrecordDisplay
  95
  96     );
  97 }
  98
  99 =head1 NAME
 100
 101 C4::Items - item management functions
 102
 103 =head1 DESCRIPTION
 104
 105 This module contains an API for manipulating item
 106 records in Koha, and is used by cataloguing, circulation,
 107 acquisitions, and serials management.
 108
 109 # FIXME This POD is not up-to-date
 110 A Koha item record is stored in two places: the
 111 items table and embedded in a MARC tag in the XML
 112 version of the associated bib record in C<biblioitems.marcxml>.
 113 This is done to allow the item information to be readily
 114 indexed (e.g., by Zebra), but means that each item
 115 modification transaction must keep the items table
 116 and the MARC XML in sync at all times.
 117
 118 Consequently, all code that creates, modifies, or deletes
 119 item records B<must> use an appropriate function from
 120 C<C4::Items>.  If no existing function is suitable, it is
 121 better to add one to C<C4::Items> than to use add
 122 one-off SQL statements to add or modify items.
 123
 124 The items table will be considered authoritative.  In other
 125 words, if there is ever a discrepancy between the items
 126 table and the MARC XML, the items table should be considered
 127 accurate.
 128
 129 =head1 HISTORICAL NOTE
 130
 131 Most of the functions in C<C4::Items> were originally in
 132 the C<C4::Biblio> module.
 133
 134 =head1 CORE EXPORTED FUNCTIONS
 135
 136 The following functions are meant for use by users
 137 of C<C4::Items>
 138
 139 =cut
 140
 141 =head2 GetItem
 142
 143   $item = GetItem($itemnumber,$barcode,$serial);
 144
 145 Return item information, for a given itemnumber or barcode.
 146 The return value is a hashref mapping item column
 147 names to values.  If C<$serial> is true, include serial publication data.
 148
 149 =cut
 150
 151 sub GetItem {
 152     my ($itemnumber,$barcode, $serial) = @_;
 153     my $dbh = C4::Context->dbh;
 154
 155     my $item;
 156     if ($itemnumber) {
 157         $item = Koha::Items->find( $itemnumber );
 158     } else {
 159         $item = Koha::Items->find( { barcode => $barcode } );
 160     }
 161
 162     return unless ( $item );
 163
 164     my $data = $item->unblessed();
 165     $data->{itype} = $item->effective_itemtype(); # set the correct itype
 166
 167     if ($serial) {
 168         my $ssth = $dbh->prepare("SELECT serialseq,publisheddate from serialitems left join serial on serialitems.serialid=serial.serialid where serialitems.itemnumber=?");
 169         $ssth->execute( $data->{'itemnumber'} );
 170         ( $data->{'serialseq'}, $data->{'publisheddate'} ) = $ssth->fetchrow_array();
 171     }
 172
 173     return $data;
 174 }    # sub GetItem
 175
 176 =head2 CartToShelf
 177
 178   CartToShelf($itemnumber);
 179
 180 Set the current shelving location of the item record
 181 to its stored permanent shelving location.  This is
 182 primarily used to indicate when an item whose current
 183 location is a special processing ('PROC') or shelving cart
 184 ('CART') location is back in the stacks.
 185
 186 =cut
 187
 188 sub CartToShelf {
 189     my ( $itemnumber ) = @_;
 190
 191     unless ( $itemnumber ) {
 192         croak "FAILED CartToShelf() - no itemnumber supplied";
 193     }
 194
 195     my $item = GetItem($itemnumber);
 196     if ( $item->{location} eq 'CART' ) {
 197         $item->{location} = $item->{permanent_location};
 198         ModItem($item, undef, $itemnumber);
 199     }
 200 }
 201
 202 =head2 ShelfToCart
 203
 204   ShelfToCart($itemnumber);
 205
 206 Set the current shelving location of the item
 207 to shelving cart ('CART').
 208
 209 =cut
 210
 211 sub ShelfToCart {
 212     my ( $itemnumber ) = @_;
 213
 214     unless ( $itemnumber ) {
 215         croak "FAILED ShelfToCart() - no itemnumber supplied";
 216     }
 217
 218     my $item = GetItem($itemnumber);
 219     $item->{'location'} = 'CART';
 220     ModItem($item, undef, $itemnumber);
 221 }
 222
 223 =head2 AddItemFromMarc
 224
 225   my ($biblionumber, $biblioitemnumber, $itemnumber)
 226       = AddItemFromMarc($source_item_marc, $biblionumber);
 227
 228 Given a MARC::Record object containing an embedded item
 229 record and a biblionumber, create a new item record.
 230
 231 =cut
 232
 233 sub AddItemFromMarc {
 234     my ( $source_item_marc, $biblionumber ) = @_;
 235     my $dbh = C4::Context->dbh;
 236
 237     # parse item hash from MARC
 238     my $frameworkcode = C4::Biblio::GetFrameworkCode( $biblionumber );
 239     my ($itemtag,$itemsubfield)=C4::Biblio::GetMarcFromKohaField("items.itemnumber",$frameworkcode);
 240
 241         my $localitemmarc=MARC::Record->new;
 242         $localitemmarc->append_fields($source_item_marc->field($itemtag));
 243     my $item = &TransformMarcToKoha( $localitemmarc, $frameworkcode ,'items');
 244     my $unlinked_item_subfields = _get_unlinked_item_subfields($localitemmarc, $frameworkcode);
 245     return AddItem($item, $biblionumber, $dbh, $frameworkcode, $unlinked_item_subfields);
 246 }
 247
 248 =head2 AddItem
 249
 250   my ($biblionumber, $biblioitemnumber, $itemnumber)
 251       = AddItem($item, $biblionumber[, $dbh, $frameworkcode, $unlinked_item_subfields]);
 252
 253 Given a hash containing item column names as keys,
 254 create a new Koha item record.
 255
 256 The first two optional parameters (C<$dbh> and C<$frameworkcode>)
 257 do not need to be supplied for general use; they exist
 258 simply to allow them to be picked up from AddItemFromMarc.
 259
 260 The final optional parameter, C<$unlinked_item_subfields>, contains
 261 an arrayref containing subfields present in the original MARC
 262 representation of the item (e.g., from the item editor) that are
 263 not mapped to C<items> columns directly but should instead
 264 be stored in C<items.more_subfields_xml> and included in
 265 the biblio items tag for display and indexing.
 266
 267 =cut
 268
 269 sub AddItem {
 270     my $item         = shift;
 271     my $biblionumber = shift;
 272
 273     my $dbh           = @_ ? shift : C4::Context->dbh;
 274     my $frameworkcode = @_ ? shift : C4::Biblio::GetFrameworkCode($biblionumber);
 275     my $unlinked_item_subfields;
 276     if (@_) {
 277         $unlinked_item_subfields = shift;
 278     }
 279
 280     # needs old biblionumber and biblioitemnumber
 281     $item->{'biblionumber'} = $biblionumber;
 282     my $sth = $dbh->prepare("SELECT biblioitemnumber FROM biblioitems WHERE biblionumber=?");
 283     $sth->execute( $item->{'biblionumber'} );
 284     ( $item->{'biblioitemnumber'} ) = $sth->fetchrow;
 285
 286     _set_defaults_for_add($item);
 287     _set_derived_columns_for_add($item);
 288     $item->{'more_subfields_xml'} = _get_unlinked_subfields_xml($unlinked_item_subfields);
 289
 290     # FIXME - checks here
 291     unless ( $item->{itype} ) {    # default to biblioitem.itemtype if no itype
 292         my $itype_sth = $dbh->prepare("SELECT itemtype FROM biblioitems WHERE biblionumber = ?");
 293         $itype_sth->execute( $item->{'biblionumber'} );
 294         ( $item->{'itype'} ) = $itype_sth->fetchrow_array;
 295     }
 296
 297     my ( $itemnumber, $error ) = _koha_new_item( $item, $item->{barcode} );
 298     return if $error;
 299
 300     $item->{'itemnumber'} = $itemnumber;
 301
 302     ModZebra( $item->{biblionumber}, "specialUpdate", "biblioserver" );
 303
 304     logaction( "CATALOGUING", "ADD", $itemnumber, "item" )
 305       if C4::Context->preference("CataloguingLog");
 306
 307     return ( $item->{biblionumber}, $item->{biblioitemnumber}, $itemnumber );
 308 }
 309
 310 =head2 AddItemBatchFromMarc
 311
 312   ($itemnumber_ref, $error_ref) = AddItemBatchFromMarc($record,
 313              $biblionumber, $biblioitemnumber, $frameworkcode);
 314
 315 Efficiently create item records from a MARC biblio record with
 316 embedded item fields.  This routine is suitable for batch jobs.
 317
 318 This API assumes that the bib record has already been
 319 saved to the C<biblio> and C<biblioitems> tables.  It does
 320 not expect that C<biblio_metadata.metadata> is populated, but it
 321 will do so via a call to ModBibiloMarc.
 322
 323 The goal of this API is to have a similar effect to using AddBiblio
 324 and AddItems in succession, but without inefficient repeated
 325 parsing of the MARC XML bib record.
 326
 327 This function returns an arrayref of new itemsnumbers and an arrayref of item
 328 errors encountered during the processing.  Each entry in the errors
 329 list is a hashref containing the following keys:
 330
 331 =over
 332
 333 =item item_sequence
 334
 335 Sequence number of original item tag in the MARC record.
 336
 337 =item item_barcode
 338
 339 Item barcode, provide to assist in the construction of
 340 useful error messages.
 341
 342 =item error_code
 343
 344 Code representing the error condition.  Can be 'duplicate_barcode',
 345 'invalid_homebranch', or 'invalid_holdingbranch'.
 346
 347 =item error_information
 348
 349 Additional information appropriate to the error condition.
 350
 351 =back
 352
 353 =cut
 354
 355 sub AddItemBatchFromMarc {
 356     my ($record, $biblionumber, $biblioitemnumber, $frameworkcode) = @_;
 357     my $error;
 358     my @itemnumbers = ();
 359     my @errors = ();
 360     my $dbh = C4::Context->dbh;
 361
 362     # We modify the record, so lets work on a clone so we don't change the
 363     # original.
 364     $record = $record->clone();
 365     # loop through the item tags and start creating items
 366     my @bad_item_fields = ();
 367     my ($itemtag, $itemsubfield) = C4::Biblio::GetMarcFromKohaField("items.itemnumber",'');
 368     my $item_sequence_num = 0;
 369     ITEMFIELD: foreach my $item_field ($record->field($itemtag)) {
 370         $item_sequence_num++;
 371         # we take the item field and stick it into a new
 372         # MARC record -- this is required so far because (FIXME)
 373         # TransformMarcToKoha requires a MARC::Record, not a MARC::Field
 374         # and there is no TransformMarcFieldToKoha
 375         my $temp_item_marc = MARC::Record->new();
 376         $temp_item_marc->append_fields($item_field);
 377
 378         # add biblionumber and biblioitemnumber
 379         my $item = TransformMarcToKoha( $temp_item_marc, $frameworkcode, 'items' );
 380         my $unlinked_item_subfields = _get_unlinked_item_subfields($temp_item_marc, $frameworkcode);
 381         $item->{'more_subfields_xml'} = _get_unlinked_subfields_xml($unlinked_item_subfields);
 382         $item->{'biblionumber'} = $biblionumber;
 383         $item->{'biblioitemnumber'} = $biblioitemnumber;
 384
 385         # check for duplicate barcode
 386         my %item_errors = CheckItemPreSave($item);
 387         if (%item_errors) {
 388             push @errors, _repack_item_errors($item_sequence_num, $item, \%item_errors);
 389             push @bad_item_fields, $item_field;
 390             next ITEMFIELD;
 391         }
 392
 393         _set_defaults_for_add($item);
 394         _set_derived_columns_for_add($item);
 395         my ( $itemnumber, $error ) = _koha_new_item( $item, $item->{barcode} );
 396         warn $error if $error;
 397         push @itemnumbers, $itemnumber; # FIXME not checking error
 398         $item->{'itemnumber'} = $itemnumber;
 399
 400         logaction("CATALOGUING", "ADD", $itemnumber, "item") if C4::Context->preference("CataloguingLog");
 401
 402         my $new_item_marc = _marc_from_item_hash($item, $frameworkcode, $unlinked_item_subfields);
 403         $item_field->replace_with($new_item_marc->field($itemtag));
 404     }
 405
 406     # remove any MARC item fields for rejected items
 407     foreach my $item_field (@bad_item_fields) {
 408         $record->delete_field($item_field);
 409     }
 410
 411     # update the MARC biblio
 412  #   $biblionumber = ModBiblioMarc( $record, $biblionumber, $frameworkcode );
 413
 414     return (\@itemnumbers, \@errors);
 415 }
 416
 417 =head2 ModItemFromMarc
 418
 419   ModItemFromMarc($item_marc, $biblionumber, $itemnumber);
 420
 421 This function updates an item record based on a supplied
 422 C<MARC::Record> object containing an embedded item field.
 423 This API is meant for the use of C<additem.pl>; for
 424 other purposes, C<ModItem> should be used.
 425
 426 This function uses the hash %default_values_for_mod_from_marc,
 427 which contains default values for item fields to
 428 apply when modifying an item.  This is needed because
 429 if an item field's value is cleared, TransformMarcToKoha
 430 does not include the column in the
 431 hash that's passed to ModItem, which without
 432 use of this hash makes it impossible to clear
 433 an item field's value.  See bug 2466.
 434
 435 Note that only columns that can be directly
 436 changed from the cataloging and serials
 437 item editors are included in this hash.
 438
 439 Returns item record
 440
 441 =cut
 442
 443 sub _build_default_values_for_mod_marc {
 444     # Has no framework parameter anymore, since Default is authoritative
 445     # for Koha to MARC mappings.
 446
 447     my $cache     = Koha::Caches->get_instance();
 448     my $cache_key = "default_value_for_mod_marc-";
 449     my $cached    = $cache->get_from_cache($cache_key);
 450     return $cached if $cached;
 451
 452     my $default_values = {
 453         barcode                  => undef,
 454         booksellerid             => undef,
 455         ccode                    => undef,
 456         'items.cn_source'        => undef,
 457         coded_location_qualifier => undef,
 458         copynumber               => undef,
 459         damaged                  => 0,
 460         enumchron                => undef,
 461         holdingbranch            => undef,
 462         homebranch               => undef,
 463         itemcallnumber           => undef,
 464         itemlost                 => 0,
 465         itemnotes                => undef,
 466         itemnotes_nonpublic      => undef,
 467         itype                    => undef,
 468         location                 => undef,
 469         permanent_location       => undef,
 470         materials                => undef,
 471         new_status               => undef,
 472         notforloan               => 0,
 473         # paidfor => undef, # commented, see bug 12817
 474         price                    => undef,
 475         replacementprice         => undef,
 476         replacementpricedate     => undef,
 477         restricted               => undef,
 478         stack                    => undef,
 479         stocknumber              => undef,
 480         uri                      => undef,
 481         withdrawn                => 0,
 482     };
 483     my %default_values_for_mod_from_marc;
 484     while ( my ( $field, $default_value ) = each %$default_values ) {
 485         my $kohafield = $field;
 486         $kohafield =~ s|^([^\.]+)$|items.$1|;
 487         $default_values_for_mod_from_marc{$field} = $default_value
 488             if C4::Biblio::GetMarcFromKohaField( $kohafield );
 489     }
 490
 491     $cache->set_in_cache($cache_key, \%default_values_for_mod_from_marc);
 492     return \%default_values_for_mod_from_marc;
 493 }
 494
 495 sub ModItemFromMarc {
 496     my $item_marc = shift;
 497     my $biblionumber = shift;
 498     my $itemnumber = shift;
 499
 500     my $frameworkcode = C4::Biblio::GetFrameworkCode($biblionumber);
 501     my ( $itemtag, $itemsubfield ) = C4::Biblio::GetMarcFromKohaField( "items.itemnumber", $frameworkcode );
 502
 503     my $localitemmarc = MARC::Record->new;
 504     $localitemmarc->append_fields( $item_marc->field($itemtag) );
 505     my $item = &TransformMarcToKoha( $localitemmarc, $frameworkcode, 'items' );
 506     my $default_values = _build_default_values_for_mod_marc();
 507     foreach my $item_field ( keys %$default_values ) {
 508         $item->{$item_field} = $default_values->{$item_field}
 509           unless exists $item->{$item_field};
 510     }
 511     my $unlinked_item_subfields = _get_unlinked_item_subfields( $localitemmarc, $frameworkcode );
 512
 513     ModItem( $item, $biblionumber, $itemnumber, { unlinked_item_subfields => $unlinked_item_subfields } );
 514     return $item;
 515 }
 516
 517 =head2 ModItem
 518
 519 ModItem(
 520     { column => $newvalue },
 521     $biblionumber,
 522     $itemnumber,
 523     {
 524         [ unlinked_item_subfields => $unlinked_item_subfields, ]
 525         [ log_action => 1, ]
 526     }
 527 );
 528
 529 Change one or more columns in an item record and update
 530 the MARC representation of the item.
 531
 532 The first argument is a hashref mapping from item column
 533 names to the new values.  The second and third arguments
 534 are the biblionumber and itemnumber, respectively.
 535 The fourth, optional parameter (additional_params) may contain the keys
 536 unlinked_item_subfields and log_action.
 537
 538 C<$unlinked_item_subfields> contains an arrayref containing
 539 subfields present in the original MARC
 540 representation of the item (e.g., from the item editor) that are
 541 not mapped to C<items> columns directly but should instead
 542 be stored in C<items.more_subfields_xml> and included in
 543 the biblio items tag for display and indexing.
 544
 545 If one of the changed columns is used to calculate
 546 the derived value of a column such as C<items.cn_sort>,
 547 this routine will perform the necessary calculation
 548 and set the value.
 549
 550 If log_action is set to false, the action will not be logged.
 551 If log_action is true or undefined, the action will be logged.
 552
 553 =cut
 554
 555 sub ModItem {
 556     my ( $item, $biblionumber, $itemnumber, $additional_params ) = @_;
 557     my $log_action = $additional_params->{log_action} // 1;
 558     my $unlinked_item_subfields = $additional_params->{unlinked_item_subfields};
 559
 560     # if $biblionumber is undefined, get it from the current item
 561     unless (defined $biblionumber) {
 562         $biblionumber = _get_single_item_column('biblionumber', $itemnumber);
 563     }
 564
 565     if ($unlinked_item_subfields) {
 566         $item->{'more_subfields_xml'} = _get_unlinked_subfields_xml($unlinked_item_subfields);
 567     };
 568
 569     $item->{'itemnumber'} = $itemnumber or return;
 570
 571     my @fields = qw( itemlost withdrawn damaged );
 572
 573     # Only call GetItem if we need to set an "on" date field
 574     if ( $item->{itemlost} || $item->{withdrawn} || $item->{damaged} ) {
 575         my $pre_mod_item = GetItem( $item->{'itemnumber'} );
 576         for my $field (@fields) {
 577             if (    defined( $item->{$field} )
 578                 and not $pre_mod_item->{$field}
 579                 and $item->{$field} )
 580             {
 581                 $item->{ $field . '_on' } =
 582                   DateTime::Format::MySQL->format_datetime( dt_from_string() );
 583             }
 584         }
 585     }
 586
 587     # If the field is defined but empty, we are removing and,
 588     # and thus need to clear out the 'on' field as well
 589     for my $field (@fields) {
 590         if ( defined( $item->{$field} ) && !$item->{$field} ) {
 591             $item->{ $field . '_on' } = undef;
 592         }
 593     }
 594
 595
 596     _set_derived_columns_for_mod($item);
 597     _do_column_fixes_for_mod($item);
 598     # FIXME add checks
 599     # duplicate barcode
 600     # attempt to change itemnumber
 601     # attempt to change biblionumber (if we want
 602     # an API to relink an item to a different bib,
 603     # it should be a separate function)
 604
 605     # update items table
 606     _koha_modify_item($item);
 607
 608     # request that bib be reindexed so that searching on current
 609     # item status is possible
 610     ModZebra( $biblionumber, "specialUpdate", "biblioserver" );
 611
 612     logaction( "CATALOGUING", "MODIFY", $itemnumber, "item " . Dumper($item) )
 613       if $log_action && C4::Context->preference("CataloguingLog");
 614 }
 615
 616 =head2 ModItemTransfer
 617
 618   ModItemTransfer($itenumber, $frombranch, $tobranch);
 619
 620 Marks an item as being transferred from one branch
 621 to another.
 622
 623 =cut
 624
 625 sub ModItemTransfer {
 626     my ( $itemnumber, $frombranch, $tobranch ) = @_;
 627
 628     my $dbh = C4::Context->dbh;
 629
 630     # Remove the 'shelving cart' location status if it is being used.
 631     CartToShelf( $itemnumber ) if ( C4::Context->preference("ReturnToShelvingCart") );
 632
 633     $dbh->do("UPDATE branchtransfers SET datearrived = NOW(), comments = ? WHERE itemnumber = ? AND datearrived IS NULL", undef, "Canceled, new transfer from $frombranch to $tobranch created", $itemnumber);
 634
 635     #new entry in branchtransfers....
 636     my $sth = $dbh->prepare(
 637         "INSERT INTO branchtransfers (itemnumber, frombranch, datesent, tobranch)
 638         VALUES (?, ?, NOW(), ?)");
 639     $sth->execute($itemnumber, $frombranch, $tobranch);
 640
 641     ModItem({ holdingbranch => $tobranch }, undef, $itemnumber);
 642     ModDateLastSeen($itemnumber);
 643     return;
 644 }
 645
 646 =head2 ModDateLastSeen
 647
 648   ModDateLastSeen($itemnum);
 649
 650 Mark item as seen. Is called when an item is issued, returned or manually marked during inventory/stocktaking.
 651 C<$itemnum> is the item number
 652
 653 =cut
 654
 655 sub ModDateLastSeen {
 656     my ($itemnumber) = @_;
 657
 658     my $today = output_pref({ dt => dt_from_string, dateformat => 'iso', dateonly => 1 });
 659     ModItem( { itemlost => 0, datelastseen => $today }, undef, $itemnumber, { log_action => 0 } );
 660 }
 661
 662 =head2 DelItem
 663
 664   DelItem({ itemnumber => $itemnumber, [ biblionumber => $biblionumber ] } );
 665
 666 Exported function (core API) for deleting an item record in Koha.
 667
 668 =cut
 669
 670 sub DelItem {
 671     my ( $params ) = @_;
 672
 673     my $itemnumber   = $params->{itemnumber};
 674     my $biblionumber = $params->{biblionumber};
 675
 676     unless ($biblionumber) {
 677         my $item = Koha::Items->find( $itemnumber );
 678         $biblionumber = $item ? $item->biblio->biblionumber : undef;
 679     }
 680
 681     # If there is no biblionumber for the given itemnumber, there is nothing to delete
 682     return 0 unless $biblionumber;
 683
 684     # FIXME check the item has no current issues
 685     my $deleted = _koha_delete_item( $itemnumber );
 686
 687     ModZebra( $biblionumber, "specialUpdate", "biblioserver" );
 688
 689     #search item field code
 690     logaction("CATALOGUING", "DELETE", $itemnumber, "item") if C4::Context->preference("CataloguingLog");
 691     return $deleted;
 692 }
 693
 694 =head2 CheckItemPreSave
 695
 696     my $item_ref = TransformMarcToKoha($marc, 'items');
 697     # do stuff
 698     my %errors = CheckItemPreSave($item_ref);
 699     if (exists $errors{'duplicate_barcode'}) {
 700         print "item has duplicate barcode: ", $errors{'duplicate_barcode'}, "\n";
 701     } elsif (exists $errors{'invalid_homebranch'}) {
 702         print "item has invalid home branch: ", $errors{'invalid_homebranch'}, "\n";
 703     } elsif (exists $errors{'invalid_holdingbranch'}) {
 704         print "item has invalid holding branch: ", $errors{'invalid_holdingbranch'}, "\n";
 705     } else {
 706         print "item is OK";
 707     }
 708
 709 Given a hashref containing item fields, determine if it can be
 710 inserted or updated in the database.  Specifically, checks for
 711 database integrity issues, and returns a hash containing any
 712 of the following keys, if applicable.
 713
 714 =over 2
 715
 716 =item duplicate_barcode
 717
 718 Barcode, if it duplicates one already found in the database.
 719
 720 =item invalid_homebranch
 721
 722 Home branch, if not defined in branches table.
 723
 724 =item invalid_holdingbranch
 725
 726 Holding branch, if not defined in branches table.
 727
 728 =back
 729
 730 This function does NOT implement any policy-related checks,
 731 e.g., whether current operator is allowed to save an
 732 item that has a given branch code.
 733
 734 =cut
 735
 736 sub CheckItemPreSave {
 737     my $item_ref = shift;
 738
 739     my %errors = ();
 740
 741     # check for duplicate barcode
 742     if (exists $item_ref->{'barcode'} and defined $item_ref->{'barcode'}) {
 743         my $existing_itemnumber = GetItemnumberFromBarcode($item_ref->{'barcode'});
 744         if ($existing_itemnumber) {
 745             if (!exists $item_ref->{'itemnumber'}                       # new item
 746                 or $item_ref->{'itemnumber'} != $existing_itemnumber) { # existing item
 747                 $errors{'duplicate_barcode'} = $item_ref->{'barcode'};
 748             }
 749         }
 750     }
 751
 752     # check for valid home branch
 753     if (exists $item_ref->{'homebranch'} and defined $item_ref->{'homebranch'}) {
 754         my $home_library = Koha::Libraries->find( $item_ref->{homebranch} );
 755         unless (defined $home_library) {
 756             $errors{'invalid_homebranch'} = $item_ref->{'homebranch'};
 757         }
 758     }
 759
 760     # check for valid holding branch
 761     if (exists $item_ref->{'holdingbranch'} and defined $item_ref->{'holdingbranch'}) {
 762         my $holding_library = Koha::Libraries->find( $item_ref->{holdingbranch} );
 763         unless (defined $holding_library) {
 764             $errors{'invalid_holdingbranch'} = $item_ref->{'holdingbranch'};
 765         }
 766     }
 767
 768     return %errors;
 769
 770 }
 771
 772 =head1 EXPORTED SPECIAL ACCESSOR FUNCTIONS
 773
 774 The following functions provide various ways of
 775 getting an item record, a set of item records, or
 776 lists of authorized values for certain item fields.
 777
 778 Some of the functions in this group are candidates
 779 for refactoring -- for example, some of the code
 780 in C<GetItemsByBiblioitemnumber> and C<GetItemsInfo>
 781 has copy-and-paste work.
 782
 783 =cut
 784
 785 =head2 GetItemsForInventory
 786
 787 ($itemlist, $iTotalRecords) = GetItemsForInventory( {
 788   minlocation  => $minlocation,
 789   maxlocation  => $maxlocation,
 790   location     => $location,
 791   itemtype     => $itemtype,
 792   ignoreissued => $ignoreissued,
 793   datelastseen => $datelastseen,
 794   branchcode   => $branchcode,
 795   branch       => $branch,
 796   offset       => $offset,
 797   size         => $size,
 798   statushash   => $statushash,
 799 } );
 800
 801 Retrieve a list of title/authors/barcode/callnumber, for biblio inventory.
 802
 803 The sub returns a reference to a list of hashes, each containing
 804 itemnumber, author, title, barcode, item callnumber, and date last
 805 seen. It is ordered by callnumber then title.
 806
 807 The required minlocation & maxlocation parameters are used to specify a range of item callnumbers
 808 the datelastseen can be used to specify that you want to see items not seen since a past date only.
 809 offset & size can be used to retrieve only a part of the whole listing (defaut behaviour)
 810 $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.
 811
 812 $iTotalRecords is the number of rows that would have been returned without the $offset, $size limit clause
 813
 814 =cut
 815
 816 sub GetItemsForInventory {
 817     my ( $parameters ) = @_;
 818     my $minlocation  = $parameters->{'minlocation'}  // '';
 819     my $maxlocation  = $parameters->{'maxlocation'}  // '';
 820     my $location     = $parameters->{'location'}     // '';
 821     my $itemtype     = $parameters->{'itemtype'}     // '';
 822     my $ignoreissued = $parameters->{'ignoreissued'} // '';
 823     my $datelastseen = $parameters->{'datelastseen'} // '';
 824     my $branchcode   = $parameters->{'branchcode'}   // '';
 825     my $branch       = $parameters->{'branch'}       // '';
 826     my $offset       = $parameters->{'offset'}       // '';
 827     my $size         = $parameters->{'size'}         // '';
 828     my $statushash   = $parameters->{'statushash'}   // '';
 829
 830     my $dbh = C4::Context->dbh;
 831     my ( @bind_params, @where_strings );
 832
 833     my $select_columns = q{
 834         SELECT items.itemnumber, barcode, itemcallnumber, title, author, biblio.biblionumber, biblio.frameworkcode, datelastseen, homebranch, location, notforloan, damaged, itemlost, withdrawn, stocknumber
 835     };
 836     my $select_count = q{SELECT COUNT(*)};
 837     my $query = q{
 838         FROM items
 839         LEFT JOIN biblio ON items.biblionumber = biblio.biblionumber
 840         LEFT JOIN biblioitems on items.biblionumber = biblioitems.biblionumber
 841     };
 842     if ($statushash){
 843         for my $authvfield (keys %$statushash){
 844             if ( scalar @{$statushash->{$authvfield}} > 0 ){
 845                 my $joinedvals = join ',', @{$statushash->{$authvfield}};
 846                 push @where_strings, "$authvfield in (" . $joinedvals . ")";
 847             }
 848         }
 849     }
 850
 851     if ($minlocation) {
 852         push @where_strings, 'itemcallnumber >= ?';
 853         push @bind_params, $minlocation;
 854     }
 855
 856     if ($maxlocation) {
 857         push @where_strings, 'itemcallnumber <= ?';
 858         push @bind_params, $maxlocation;
 859     }
 860
 861     if ($datelastseen) {
 862         $datelastseen = output_pref({ str => $datelastseen, dateformat => 'iso', dateonly => 1 });
 863         push @where_strings, '(datelastseen < ? OR datelastseen IS NULL)';
 864         push @bind_params, $datelastseen;
 865     }
 866
 867     if ( $location ) {
 868         push @where_strings, 'items.location = ?';
 869         push @bind_params, $location;
 870     }
 871
 872     if ( $branchcode ) {
 873         if($branch eq "homebranch"){
 874         push @where_strings, 'items.homebranch = ?';
 875         }else{
 876             push @where_strings, 'items.holdingbranch = ?';
 877         }
 878         push @bind_params, $branchcode;
 879     }
 880
 881     if ( $itemtype ) {
 882         push @where_strings, 'biblioitems.itemtype = ?';
 883         push @bind_params, $itemtype;
 884     }
 885
 886     if ( $ignoreissued) {
 887         $query .= "LEFT JOIN issues ON items.itemnumber = issues.itemnumber ";
 888         push @where_strings, 'issues.date_due IS NULL';
 889     }
 890
 891     if ( @where_strings ) {
 892         $query .= 'WHERE ';
 893         $query .= join ' AND ', @where_strings;
 894     }
 895     my $count_query = $select_count . $query;
 896     $query .= ' ORDER BY items.cn_sort, itemcallnumber, title';
 897     $query .= " LIMIT $offset, $size" if ($offset and $size);
 898     $query = $select_columns . $query;
 899     my $sth = $dbh->prepare($query);
 900     $sth->execute( @bind_params );
 901
 902     my @results = ();
 903     my $tmpresults = $sth->fetchall_arrayref({});
 904     $sth = $dbh->prepare( $count_query );
 905     $sth->execute( @bind_params );
 906     my ($iTotalRecords) = $sth->fetchrow_array();
 907
 908     my @avs = Koha::AuthorisedValues->search(
 909         {   'marc_subfield_structures.kohafield' => { '>' => '' },
 910             'me.authorised_value'                => { '>' => '' },
 911         },
 912         {   join     => { category => 'marc_subfield_structures' },
 913             distinct => ['marc_subfield_structures.kohafield, me.category, frameworkcode, me.authorised_value'],
 914             '+select' => [ 'marc_subfield_structures.kohafield', 'marc_subfield_structures.frameworkcode', 'me.authorised_value', 'me.lib' ],
 915             '+as'     => [ 'kohafield',                          'frameworkcode',                          'authorised_value',    'lib' ],
 916         }
 917     );
 918
 919     my $avmapping = { map { $_->get_column('kohafield') . ',' . $_->get_column('frameworkcode') . ',' . $_->get_column('authorised_value') => $_->get_column('lib') } @avs };
 920
 921     foreach my $row (@$tmpresults) {
 922
 923         # Auth values
 924         foreach (keys %$row) {
 925             if (defined($avmapping->{"items.$_,".$row->{'frameworkcode'}.",".$row->{$_}})) {
 926                 $row->{$_} = $avmapping->{"items.$_,".$row->{'frameworkcode'}.",".$row->{$_}};
 927             }
 928         }
 929         push @results, $row;
 930     }
 931
 932     return (\@results, $iTotalRecords);
 933 }
 934
 935 =head2 GetItemsByBiblioitemnumber
 936
 937   GetItemsByBiblioitemnumber($biblioitemnumber);
 938
 939 Returns an arrayref of hashrefs suitable for use in a TMPL_LOOP
 940 Called by C<C4::XISBN>
 941
 942 =cut
 943
 944 sub GetItemsByBiblioitemnumber {
 945     my ( $bibitem ) = @_;
 946     my $dbh = C4::Context->dbh;
 947     my $sth = $dbh->prepare("SELECT * FROM items WHERE items.biblioitemnumber = ?") || die $dbh->errstr;
 948     # Get all items attached to a biblioitem
 949     my $i = 0;
 950     my @results;
 951     $sth->execute($bibitem) || die $sth->errstr;
 952     while ( my $data = $sth->fetchrow_hashref ) {
 953         # Foreach item, get circulation information
 954         my $sth2 = $dbh->prepare( "SELECT * FROM issues,borrowers
 955                                    WHERE itemnumber = ?
 956                                    AND issues.borrowernumber = borrowers.borrowernumber"
 957         );
 958         $sth2->execute( $data->{'itemnumber'} );
 959         if ( my $data2 = $sth2->fetchrow_hashref ) {
 960             # if item is out, set the due date and who it is out too
 961             $data->{'date_due'}   = $data2->{'date_due'};
 962             $data->{'cardnumber'} = $data2->{'cardnumber'};
 963             $data->{'borrowernumber'}   = $data2->{'borrowernumber'};
 964         }
 965         else {
 966             # set date_due to blank, so in the template we check itemlost, and withdrawn
 967             $data->{'date_due'} = '';
 968         }    # else
 969         # Find the last 3 people who borrowed this item.
 970         my $query2 = "SELECT * FROM old_issues, borrowers WHERE itemnumber = ?
 971                       AND old_issues.borrowernumber = borrowers.borrowernumber
 972                       ORDER BY returndate desc,timestamp desc LIMIT 3";
 973         $sth2 = $dbh->prepare($query2) || die $dbh->errstr;
 974         $sth2->execute( $data->{'itemnumber'} ) || die $sth2->errstr;
 975         my $i2 = 0;
 976         while ( my $data2 = $sth2->fetchrow_hashref ) {
 977             $data->{"timestamp$i2"} = $data2->{'timestamp'};
 978             $data->{"card$i2"}      = $data2->{'cardnumber'};
 979             $data->{"borrower$i2"}  = $data2->{'borrowernumber'};
 980             $i2++;
 981         }
 982         push(@results,$data);
 983     }
 984     return (\@results);
 985 }
 986
 987 =head2 GetItemsInfo
 988
 989   @results = GetItemsInfo($biblionumber);
 990
 991 Returns information about items with the given biblionumber.
 992
 993 C<GetItemsInfo> returns a list of references-to-hash. Each element
 994 contains a number of keys. Most of them are attributes from the
 995 C<biblio>, C<biblioitems>, C<items>, and C<itemtypes> tables in the
 996 Koha database. Other keys include:
 997
 998 =over 2
 999
1000 =item C<$data-E<gt>{branchname}>
1001
1002 The name (not the code) of the branch to which the book belongs.
1003
1004 =item C<$data-E<gt>{datelastseen}>
1005
1006 This is simply C<items.datelastseen>, except that while the date is
1007 stored in YYYY-MM-DD format in the database, here it is converted to
1008 DD/MM/YYYY format. A NULL date is returned as C<//>.
1009
1010 =item C<$data-E<gt>{datedue}>
1011
1012 =item C<$data-E<gt>{class}>
1013
1014 This is the concatenation of C<biblioitems.classification>, the book's
1015 Dewey code, and C<biblioitems.subclass>.
1016
1017 =item C<$data-E<gt>{ocount}>
1018
1019 I think this is the number of copies of the book available.
1020
1021 =item C<$data-E<gt>{order}>
1022
1023 If this is set, it is set to C<One Order>.
1024
1025 =back
1026
1027 =cut
1028
1029 sub GetItemsInfo {
1030     my ( $biblionumber ) = @_;
1031     my $dbh   = C4::Context->dbh;
1032     require C4::Languages;
1033     my $language = C4::Languages::getlanguage();
1034     my $query = "
1035     SELECT items.*,
1036            biblio.*,
1037            biblioitems.volume,
1038            biblioitems.number,
1039            biblioitems.itemtype,
1040            biblioitems.isbn,
1041            biblioitems.issn,
1042            biblioitems.publicationyear,
1043            biblioitems.publishercode,
1044            biblioitems.volumedate,
1045            biblioitems.volumedesc,
1046            biblioitems.lccn,
1047            biblioitems.url,
1048            items.notforloan as itemnotforloan,
1049            issues.borrowernumber,
1050            issues.date_due as datedue,
1051            issues.onsite_checkout,
1052            borrowers.cardnumber,
1053            borrowers.surname,
1054            borrowers.firstname,
1055            borrowers.branchcode as bcode,
1056            serial.serialseq,
1057            serial.publisheddate,
1058            itemtypes.description,
1059            COALESCE( localization.translation, itemtypes.description ) AS translated_description,
1060            itemtypes.notforloan as notforloan_per_itemtype,
1061            holding.branchurl,
1062            holding.branchcode,
1063            holding.branchname,
1064            holding.opac_info as holding_branch_opac_info,
1065            home.opac_info as home_branch_opac_info
1066     ";
1067     $query .= "
1068      FROM items
1069      LEFT JOIN branches AS holding ON items.holdingbranch = holding.branchcode
1070      LEFT JOIN branches AS home ON items.homebranch=home.branchcode
1071      LEFT JOIN biblio      ON      biblio.biblionumber     = items.biblionumber
1072      LEFT JOIN biblioitems ON biblioitems.biblioitemnumber = items.biblioitemnumber
1073      LEFT JOIN issues USING (itemnumber)
1074      LEFT JOIN borrowers USING (borrowernumber)
1075      LEFT JOIN serialitems USING (itemnumber)
1076      LEFT JOIN serial USING (serialid)
1077      LEFT JOIN itemtypes   ON   itemtypes.itemtype         = "
1078      . (C4::Context->preference('item-level_itypes') ? 'items.itype' : 'biblioitems.itemtype');
1079     $query .= q|
1080     LEFT JOIN localization ON itemtypes.itemtype = localization.code
1081         AND localization.entity = 'itemtypes'
1082         AND localization.lang = ?
1083     |;
1084
1085     $query .= " WHERE items.biblionumber = ? ORDER BY home.branchname, items.enumchron, LPAD( items.copynumber, 8, '0' ), items.dateaccessioned DESC" ;
1086     my $sth = $dbh->prepare($query);
1087     $sth->execute($language, $biblionumber);
1088     my $i = 0;
1089     my @results;
1090     my $serial;
1091
1092     my $userenv = C4::Context->userenv;
1093     my $want_not_same_branch = C4::Context->preference("IndependentBranches") && !C4::Context->IsSuperLibrarian();
1094     while ( my $data = $sth->fetchrow_hashref ) {
1095         if ( $data->{borrowernumber} && $want_not_same_branch) {
1096             $data->{'NOTSAMEBRANCH'} = $data->{'bcode'} ne $userenv->{branch};
1097         }
1098
1099         $serial ||= $data->{'serial'};
1100
1101         my $descriptions;
1102         # get notforloan complete status if applicable
1103         $descriptions = Koha::AuthorisedValues->get_description_by_koha_field({frameworkcode => $data->{frameworkcode}, kohafield => 'items.notforloan', authorised_value => $data->{itemnotforloan} });
1104         $data->{notforloanvalue}     = $descriptions->{lib} // '';
1105         $data->{notforloanvalueopac} = $descriptions->{opac_description} // '';
1106
1107         # get restricted status and description if applicable
1108         $descriptions = Koha::AuthorisedValues->get_description_by_koha_field({frameworkcode => $data->{frameworkcode}, kohafield => 'items.restricted', authorised_value => $data->{restricted} });
1109         $data->{restricted}     = $descriptions->{lib} // '';
1110         $data->{restrictedopac} = $descriptions->{opac_description} // '';
1111
1112         # my stack procedures
1113         $descriptions = Koha::AuthorisedValues->get_description_by_koha_field({frameworkcode => $data->{frameworkcode}, kohafield => 'items.stack', authorised_value => $data->{stack} });
1114         $data->{stack}          = $descriptions->{lib} // '';
1115
1116         # Find the last 3 people who borrowed this item.
1117         my $sth2 = $dbh->prepare("SELECT * FROM old_issues,borrowers
1118                                     WHERE itemnumber = ?
1119                                     AND old_issues.borrowernumber = borrowers.borrowernumber
1120                                     ORDER BY returndate DESC
1121                                     LIMIT 3");
1122         $sth2->execute($data->{'itemnumber'});
1123         my $ii = 0;
1124         while (my $data2 = $sth2->fetchrow_hashref()) {
1125             $data->{"timestamp$ii"} = $data2->{'timestamp'} if $data2->{'timestamp'};
1126             $data->{"card$ii"}      = $data2->{'cardnumber'} if $data2->{'cardnumber'};
1127             $data->{"borrower$ii"}  = $data2->{'borrowernumber'} if $data2->{'borrowernumber'};
1128             $ii++;
1129         }
1130
1131         $results[$i] = $data;
1132         $i++;
1133     }
1134
1135     return $serial
1136         ? sort { ($b->{'publisheddate'} || $b->{'enumchron'}) cmp ($a->{'publisheddate'} || $a->{'enumchron'}) } @results
1137         : @results;
1138 }
1139
1140 =head2 GetItemsLocationInfo
1141
1142   my @itemlocinfo = GetItemsLocationInfo($biblionumber);
1143
1144 Returns the branch names, shelving location and itemcallnumber for each item attached to the biblio in question
1145
1146 C<GetItemsInfo> returns a list of references-to-hash. Data returned:
1147
1148 =over 2
1149
1150 =item C<$data-E<gt>{homebranch}>
1151
1152 Branch Name of the item's homebranch
1153
1154 =item C<$data-E<gt>{holdingbranch}>
1155
1156 Branch Name of the item's holdingbranch
1157
1158 =item C<$data-E<gt>{location}>
1159
1160 Item's shelving location code
1161
1162 =item C<$data-E<gt>{location_intranet}>
1163
1164 The intranet description for the Shelving Location as set in authorised_values 'LOC'
1165
1166 =item C<$data-E<gt>{location_opac}>
1167
1168 The OPAC description for the Shelving Location as set in authorised_values 'LOC'.  Falls back to intranet description if no OPAC
1169 description is set.
1170
1171 =item C<$data-E<gt>{itemcallnumber}>
1172
1173 Item's itemcallnumber
1174
1175 =item C<$data-E<gt>{cn_sort}>
1176
1177 Item's call number normalized for sorting
1178
1179 =back
1180
1181 =cut
1182
1183 sub GetItemsLocationInfo {
1184         my $biblionumber = shift;
1185         my @results;
1186
1187         my $dbh = C4::Context->dbh;
1188         my $query = "SELECT a.branchname as homebranch, b.branchname as holdingbranch,
1189                             location, itemcallnumber, cn_sort
1190                      FROM items, branches as a, branches as b
1191                      WHERE homebranch = a.branchcode AND holdingbranch = b.branchcode
1192                      AND biblionumber = ?
1193                      ORDER BY cn_sort ASC";
1194         my $sth = $dbh->prepare($query);
1195         $sth->execute($biblionumber);
1196
1197         while ( my $data = $sth->fetchrow_hashref ) {
1198              my $av = Koha::AuthorisedValues->search({ category => 'LOC', authorised_value => $data->{location} });
1199              $av = $av->count ? $av->next : undef;
1200              $data->{location_intranet} = $av ? $av->lib : '';
1201              $data->{location_opac}     = $av ? $av->opac_description : '';
1202              push @results, $data;
1203         }
1204         return @results;
1205 }
1206
1207 =head2 GetHostItemsInfo
1208
1209         $hostiteminfo = GetHostItemsInfo($hostfield);
1210         Returns the iteminfo for items linked to records via a host field
1211
1212 =cut
1213
1214 sub GetHostItemsInfo {
1215         my ($record) = @_;
1216         my @returnitemsInfo;
1217
1218         if (C4::Context->preference('marcflavour') eq 'MARC21' ||
1219         C4::Context->preference('marcflavour') eq 'NORMARC'){
1220             foreach my $hostfield ( $record->field('773') ) {
1221                 my $hostbiblionumber = $hostfield->subfield("0");
1222                 my $linkeditemnumber = $hostfield->subfield("9");
1223                 my @hostitemInfos = GetItemsInfo($hostbiblionumber);
1224                 foreach my $hostitemInfo (@hostitemInfos){
1225                         if ($hostitemInfo->{itemnumber} eq $linkeditemnumber){
1226                                 push (@returnitemsInfo,$hostitemInfo);
1227                                 last;
1228                         }
1229                 }
1230             }
1231         } elsif ( C4::Context->preference('marcflavour') eq 'UNIMARC'){
1232             foreach my $hostfield ( $record->field('461') ) {
1233                 my $hostbiblionumber = $hostfield->subfield("0");
1234                 my $linkeditemnumber = $hostfield->subfield("9");
1235                 my @hostitemInfos = GetItemsInfo($hostbiblionumber);
1236                 foreach my $hostitemInfo (@hostitemInfos){
1237                         if ($hostitemInfo->{itemnumber} eq $linkeditemnumber){
1238                                 push (@returnitemsInfo,$hostitemInfo);
1239                                 last;
1240                         }
1241                 }
1242             }
1243         }
1244         return @returnitemsInfo;
1245 }
1246
1247
1248 =head2 GetLastAcquisitions
1249
1250   my $lastacq = GetLastAcquisitions({'branches' => ('branch1','branch2'),
1251                                     'itemtypes' => ('BK','BD')}, 10);
1252
1253 =cut
1254
1255 sub  GetLastAcquisitions {
1256         my ($data,$max) = @_;
1257
1258         my $itemtype = C4::Context->preference('item-level_itypes') ? 'itype' : 'itemtype';
1259
1260         my $number_of_branches = @{$data->{branches}};
1261         my $number_of_itemtypes   = @{$data->{itemtypes}};
1262
1263
1264         my @where = ('WHERE 1 ');
1265         $number_of_branches and push @where
1266            , 'AND holdingbranch IN ('
1267            , join(',', ('?') x $number_of_branches )
1268            , ')'
1269          ;
1270
1271         $number_of_itemtypes and push @where
1272            , "AND $itemtype IN ("
1273            , join(',', ('?') x $number_of_itemtypes )
1274            , ')'
1275          ;
1276
1277         my $query = "SELECT biblio.biblionumber as biblionumber, title, dateaccessioned
1278                                  FROM items RIGHT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
1279                                     RIGHT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber)
1280                                     @where
1281                                     GROUP BY biblio.biblionumber
1282                                     ORDER BY dateaccessioned DESC LIMIT $max";
1283
1284         my $dbh = C4::Context->dbh;
1285         my $sth = $dbh->prepare($query);
1286
1287     $sth->execute((@{$data->{branches}}, @{$data->{itemtypes}}));
1288
1289         my @results;
1290         while( my $row = $sth->fetchrow_hashref){
1291                 push @results, {date => $row->{dateaccessioned}
1292                                                 , biblionumber => $row->{biblionumber}
1293                                                 , title => $row->{title}};
1294         }
1295
1296         return @results;
1297 }
1298
1299 =head2 GetItemnumbersForBiblio
1300
1301   my $itemnumbers = GetItemnumbersForBiblio($biblionumber);
1302
1303 Given a single biblionumber, return an arrayref of all the corresponding itemnumbers
1304
1305 =cut
1306
1307 sub GetItemnumbersForBiblio {
1308     my $biblionumber = shift;
1309     my @items;
1310     my $dbh = C4::Context->dbh;
1311     my $sth = $dbh->prepare("SELECT itemnumber FROM items WHERE biblionumber = ?");
1312     $sth->execute($biblionumber);
1313     while (my $result = $sth->fetchrow_hashref) {
1314         push @items, $result->{'itemnumber'};
1315     }
1316     return \@items;
1317 }
1318
1319 =head2 get_hostitemnumbers_of
1320
1321   my @itemnumbers_of = get_hostitemnumbers_of($biblionumber);
1322
1323 Given a biblionumber, return the list of corresponding itemnumbers that are linked to it via host fields
1324
1325 Return a reference on a hash where key is a biblionumber and values are
1326 references on array of itemnumbers.
1327
1328 =cut
1329
1330
1331 sub get_hostitemnumbers_of {
1332     my ($biblionumber) = @_;
1333     my $marcrecord = C4::Biblio::GetMarcBiblio({ biblionumber => $biblionumber });
1334
1335     return unless $marcrecord;
1336
1337     my ( @returnhostitemnumbers, $tag, $biblio_s, $item_s );
1338
1339     my $marcflavor = C4::Context->preference('marcflavour');
1340     if ( $marcflavor eq 'MARC21' || $marcflavor eq 'NORMARC' ) {
1341         $tag      = '773';
1342         $biblio_s = '0';
1343         $item_s   = '9';
1344     }
1345     elsif ( $marcflavor eq 'UNIMARC' ) {
1346         $tag      = '461';
1347         $biblio_s = '0';
1348         $item_s   = '9';
1349     }
1350
1351     foreach my $hostfield ( $marcrecord->field($tag) ) {
1352         my $hostbiblionumber = $hostfield->subfield($biblio_s);
1353         next unless $hostbiblionumber; # have tag, don't have $biblio_s subfield
1354         my $linkeditemnumber = $hostfield->subfield($item_s);
1355         if ( ! $linkeditemnumber ) {
1356             warn "ERROR biblionumber $biblionumber has 773^0, but doesn't have 9";
1357             next;
1358         }
1359         my $is_from_biblio = Koha::Items->search({ itemnumber => $linkeditemnumber, biblionumber => $hostbiblionumber });
1360         push @returnhostitemnumbers, $linkeditemnumber
1361           if $is_from_biblio;
1362     }
1363
1364     return @returnhostitemnumbers;
1365 }
1366
1367
1368 =head2 GetItemnumberFromBarcode
1369
1370   $result = GetItemnumberFromBarcode($barcode);
1371
1372 =cut
1373
1374 sub GetItemnumberFromBarcode {
1375     my ($barcode) = @_;
1376     my $dbh = C4::Context->dbh;
1377
1378     my $rq =
1379       $dbh->prepare("SELECT itemnumber FROM items WHERE items.barcode=?");
1380     $rq->execute($barcode);
1381     my ($result) = $rq->fetchrow;
1382     return ($result);
1383 }
1384
1385 =head2 GetBarcodeFromItemnumber
1386
1387   $result = GetBarcodeFromItemnumber($itemnumber);
1388
1389 =cut
1390
1391 sub GetBarcodeFromItemnumber {
1392     my ($itemnumber) = @_;
1393     my $dbh = C4::Context->dbh;
1394
1395     my $rq =
1396       $dbh->prepare("SELECT barcode FROM items WHERE items.itemnumber=?");
1397     $rq->execute($itemnumber);
1398     my ($result) = $rq->fetchrow;
1399     return ($result);
1400 }
1401
1402 =head2 GetHiddenItemnumbers
1403
1404     my @itemnumbers_to_hide = GetHiddenItemnumbers(@items);
1405
1406 Given a list of items it checks which should be hidden from the OPAC given
1407 the current configuration. Returns a list of itemnumbers corresponding to
1408 those that should be hidden.
1409
1410 =cut
1411
1412 sub GetHiddenItemnumbers {
1413     my (@items) = @_;
1414     my @resultitems;
1415
1416     my $yaml = C4::Context->preference('OpacHiddenItems');
1417     return () if (! $yaml =~ /\S/ );
1418     $yaml = "$yaml\n\n"; # YAML is anal on ending \n. Surplus does not hurt
1419     my $hidingrules;
1420     eval {
1421         $hidingrules = YAML::Load($yaml);
1422     };
1423     if ($@) {
1424         warn "Unable to parse OpacHiddenItems syspref : $@";
1425         return ();
1426     }
1427     my $dbh = C4::Context->dbh;
1428
1429     # For each item
1430     foreach my $item (@items) {
1431
1432         # We check each rule
1433         foreach my $field (keys %$hidingrules) {
1434             my $val;
1435             if (exists $item->{$field}) {
1436                 $val = $item->{$field};
1437             }
1438             else {
1439                 my $query = "SELECT $field from items where itemnumber = ?";
1440                 $val = $dbh->selectrow_array($query, undef, $item->{'itemnumber'});
1441             }
1442             $val = '' unless defined $val;
1443
1444             # If the results matches the values in the yaml file
1445             if (any { $val eq $_ } @{$hidingrules->{$field}}) {
1446
1447                 # We add the itemnumber to the list
1448                 push @resultitems, $item->{'itemnumber'};
1449
1450                 # If at least one rule matched for an item, no need to test the others
1451                 last;
1452             }
1453         }
1454     }
1455     return @resultitems;
1456 }
1457
1458 =head1 LIMITED USE FUNCTIONS
1459
1460 The following functions, while part of the public API,
1461 are not exported.  This is generally because they are
1462 meant to be used by only one script for a specific
1463 purpose, and should not be used in any other context
1464 without careful thought.
1465
1466 =cut
1467
1468 =head2 GetMarcItem
1469
1470   my $item_marc = GetMarcItem($biblionumber, $itemnumber);
1471
1472 Returns MARC::Record of the item passed in parameter.
1473 This function is meant for use only in C<cataloguing/additem.pl>,
1474 where it is needed to support that script's MARC-like
1475 editor.
1476
1477 =cut
1478
1479 sub GetMarcItem {
1480     my ( $biblionumber, $itemnumber ) = @_;
1481
1482     # GetMarcItem has been revised so that it does the following:
1483     #  1. Gets the item information from the items table.
1484     #  2. Converts it to a MARC field for storage in the bib record.
1485     #
1486     # The previous behavior was:
1487     #  1. Get the bib record.
1488     #  2. Return the MARC tag corresponding to the item record.
1489     #
1490     # The difference is that one treats the items row as authoritative,
1491     # while the other treats the MARC representation as authoritative
1492     # under certain circumstances.
1493
1494     my $itemrecord = GetItem($itemnumber);
1495
1496     # Tack on 'items.' prefix to column names so that C4::Biblio::TransformKohaToMarc will work.
1497     # Also, don't emit a subfield if the underlying field is blank.
1498
1499
1500     return Item2Marc($itemrecord,$biblionumber);
1501
1502 }
1503 sub Item2Marc {
1504         my ($itemrecord,$biblionumber)=@_;
1505     my $mungeditem = {
1506         map {
1507             defined($itemrecord->{$_}) && $itemrecord->{$_} ne '' ? ("items.$_" => $itemrecord->{$_}) : ()
1508         } keys %{ $itemrecord }
1509     };
1510     my $framework = C4::Biblio::GetFrameworkCode( $biblionumber );
1511     my $itemmarc = C4::Biblio::TransformKohaToMarc(
1512         $mungeditem, { no_split => 1},
1513     );
1514     my ( $itemtag, $itemsubfield ) = C4::Biblio::GetMarcFromKohaField(
1515         "items.itemnumber", $framework,
1516     );
1517
1518     my $unlinked_item_subfields = _parse_unlinked_item_subfields_from_xml($mungeditem->{'items.more_subfields_xml'});
1519     if (defined $unlinked_item_subfields and $#$unlinked_item_subfields > -1) {
1520                 foreach my $field ($itemmarc->field($itemtag)){
1521             $field->add_subfields(@$unlinked_item_subfields);
1522         }
1523     }
1524         return $itemmarc;
1525 }
1526
1527 =head1 PRIVATE FUNCTIONS AND VARIABLES
1528
1529 The following functions are not meant to be called
1530 directly, but are documented in order to explain
1531 the inner workings of C<C4::Items>.
1532
1533 =cut
1534
1535 =head2 %derived_columns
1536
1537 This hash keeps track of item columns that
1538 are strictly derived from other columns in
1539 the item record and are not meant to be set
1540 independently.
1541
1542 Each key in the hash should be the name of a
1543 column (as named by TransformMarcToKoha).  Each
1544 value should be hashref whose keys are the
1545 columns on which the derived column depends.  The
1546 hashref should also contain a 'BUILDER' key
1547 that is a reference to a sub that calculates
1548 the derived value.
1549
1550 =cut
1551
1552 my %derived_columns = (
1553     'items.cn_sort' => {
1554         'itemcallnumber' => 1,
1555         'items.cn_source' => 1,
1556         'BUILDER' => \&_calc_items_cn_sort,
1557     }
1558 );
1559
1560 =head2 _set_derived_columns_for_add
1561
1562   _set_derived_column_for_add($item);
1563
1564 Given an item hash representing a new item to be added,
1565 calculate any derived columns.  Currently the only
1566 such column is C<items.cn_sort>.
1567
1568 =cut
1569
1570 sub _set_derived_columns_for_add {
1571     my $item = shift;
1572
1573     foreach my $column (keys %derived_columns) {
1574         my $builder = $derived_columns{$column}->{'BUILDER'};
1575         my $source_values = {};
1576         foreach my $source_column (keys %{ $derived_columns{$column} }) {
1577             next if $source_column eq 'BUILDER';
1578             $source_values->{$source_column} = $item->{$source_column};
1579         }
1580         $builder->($item, $source_values);
1581     }
1582 }
1583
1584 =head2 _set_derived_columns_for_mod
1585
1586   _set_derived_column_for_mod($item);
1587
1588 Given an item hash representing a new item to be modified.
1589 calculate any derived columns.  Currently the only
1590 such column is C<items.cn_sort>.
1591
1592 This routine differs from C<_set_derived_columns_for_add>
1593 in that it needs to handle partial item records.  In other
1594 words, the caller of C<ModItem> may have supplied only one
1595 or two columns to be changed, so this function needs to
1596 determine whether any of the columns to be changed affect
1597 any of the derived columns.  Also, if a derived column
1598 depends on more than one column, but the caller is not
1599 changing all of then, this routine retrieves the unchanged
1600 values from the database in order to ensure a correct
1601 calculation.
1602
1603 =cut
1604
1605 sub _set_derived_columns_for_mod {
1606     my $item = shift;
1607
1608     foreach my $column (keys %derived_columns) {
1609         my $builder = $derived_columns{$column}->{'BUILDER'};
1610         my $source_values = {};
1611         my %missing_sources = ();
1612         my $must_recalc = 0;
1613         foreach my $source_column (keys %{ $derived_columns{$column} }) {
1614             next if $source_column eq 'BUILDER';
1615             if (exists $item->{$source_column}) {
1616                 $must_recalc = 1;
1617                 $source_values->{$source_column} = $item->{$source_column};
1618             } else {
1619                 $missing_sources{$source_column} = 1;
1620             }
1621         }
1622         if ($must_recalc) {
1623             foreach my $source_column (keys %missing_sources) {
1624                 $source_values->{$source_column} = _get_single_item_column($source_column, $item->{'itemnumber'});
1625             }
1626             $builder->($item, $source_values);
1627         }
1628     }
1629 }
1630
1631 =head2 _do_column_fixes_for_mod
1632
1633   _do_column_fixes_for_mod($item);
1634
1635 Given an item hashref containing one or more
1636 columns to modify, fix up certain values.
1637 Specifically, set to 0 any passed value
1638 of C<notforloan>, C<damaged>, C<itemlost>, or
1639 C<withdrawn> that is either undefined or
1640 contains the empty string.
1641
1642 =cut
1643
1644 sub _do_column_fixes_for_mod {
1645     my $item = shift;
1646
1647     if (exists $item->{'notforloan'} and
1648         (not defined $item->{'notforloan'} or $item->{'notforloan'} eq '')) {
1649         $item->{'notforloan'} = 0;
1650     }
1651     if (exists $item->{'damaged'} and
1652         (not defined $item->{'damaged'} or $item->{'damaged'} eq '')) {
1653         $item->{'damaged'} = 0;
1654     }
1655     if (exists $item->{'itemlost'} and
1656         (not defined $item->{'itemlost'} or $item->{'itemlost'} eq '')) {
1657         $item->{'itemlost'} = 0;
1658     }
1659     if (exists $item->{'withdrawn'} and
1660         (not defined $item->{'withdrawn'} or $item->{'withdrawn'} eq '')) {
1661         $item->{'withdrawn'} = 0;
1662     }
1663     if (exists $item->{location}
1664         and $item->{location} ne 'CART'
1665         and $item->{location} ne 'PROC'
1666         and not $item->{permanent_location}
1667     ) {
1668         $item->{'permanent_location'} = $item->{'location'};
1669     }
1670     if (exists $item->{'timestamp'}) {
1671         delete $item->{'timestamp'};
1672     }
1673 }
1674
1675 =head2 _get_single_item_column
1676
1677   _get_single_item_column($column, $itemnumber);
1678
1679 Retrieves the value of a single column from an C<items>
1680 row specified by C<$itemnumber>.
1681
1682 =cut
1683
1684 sub _get_single_item_column {
1685     my $column = shift;
1686     my $itemnumber = shift;
1687
1688     my $dbh = C4::Context->dbh;
1689     my $sth = $dbh->prepare("SELECT $column FROM items WHERE itemnumber = ?");
1690     $sth->execute($itemnumber);
1691     my ($value) = $sth->fetchrow();
1692     return $value;
1693 }
1694
1695 =head2 _calc_items_cn_sort
1696
1697   _calc_items_cn_sort($item, $source_values);
1698
1699 Helper routine to calculate C<items.cn_sort>.
1700
1701 =cut
1702
1703 sub _calc_items_cn_sort {
1704     my $item = shift;
1705     my $source_values = shift;
1706
1707     $item->{'items.cn_sort'} = GetClassSort($source_values->{'items.cn_source'}, $source_values->{'itemcallnumber'}, "");
1708 }
1709
1710 =head2 _set_defaults_for_add
1711
1712   _set_defaults_for_add($item_hash);
1713
1714 Given an item hash representing an item to be added, set
1715 correct default values for columns whose default value
1716 is not handled by the DBMS.  This includes the following
1717 columns:
1718
1719 =over 2
1720
1721 =item *
1722
1723 C<items.dateaccessioned>
1724
1725 =item *
1726
1727 C<items.notforloan>
1728
1729 =item *
1730
1731 C<items.damaged>
1732
1733 =item *
1734
1735 C<items.itemlost>
1736
1737 =item *
1738
1739 C<items.withdrawn>
1740
1741 =back
1742
1743 =cut
1744
1745 sub _set_defaults_for_add {
1746     my $item = shift;
1747     $item->{dateaccessioned} ||= output_pref({ dt => dt_from_string, dateformat => 'iso', dateonly => 1 });
1748     $item->{$_} ||= 0 for (qw( notforloan damaged itemlost withdrawn));
1749 }
1750
1751 =head2 _koha_new_item
1752
1753   my ($itemnumber,$error) = _koha_new_item( $item, $barcode );
1754
1755 Perform the actual insert into the C<items> table.
1756
1757 =cut
1758
1759 sub _koha_new_item {
1760     my ( $item, $barcode ) = @_;
1761     my $dbh=C4::Context->dbh;
1762     my $error;
1763     $item->{permanent_location} //= $item->{location};
1764     _mod_item_dates( $item );
1765     my $query =
1766            "INSERT INTO items SET
1767             biblionumber        = ?,
1768             biblioitemnumber    = ?,
1769             barcode             = ?,
1770             dateaccessioned     = ?,
1771             booksellerid        = ?,
1772             homebranch          = ?,
1773             price               = ?,
1774             replacementprice    = ?,
1775             replacementpricedate = ?,
1776             datelastborrowed    = ?,
1777             datelastseen        = ?,
1778             stack               = ?,
1779             notforloan          = ?,
1780             damaged             = ?,
1781             itemlost            = ?,
1782             withdrawn           = ?,
1783             itemcallnumber      = ?,
1784             coded_location_qualifier = ?,
1785             restricted          = ?,
1786             itemnotes           = ?,
1787             itemnotes_nonpublic = ?,
1788             holdingbranch       = ?,
1789             paidfor             = ?,
1790             location            = ?,
1791             permanent_location  = ?,
1792             onloan              = ?,
1793             issues              = ?,
1794             renewals            = ?,
1795             reserves            = ?,
1796             cn_source           = ?,
1797             cn_sort             = ?,
1798             ccode               = ?,
1799             itype               = ?,
1800             materials           = ?,
1801             uri                 = ?,
1802             enumchron           = ?,
1803             more_subfields_xml  = ?,
1804             copynumber          = ?,
1805             stocknumber         = ?,
1806             new_status          = ?
1807           ";
1808     my $sth = $dbh->prepare($query);
1809     my $today = output_pref({ dt => dt_from_string, dateformat => 'iso', dateonly => 1 });
1810    $sth->execute(
1811             $item->{'biblionumber'},
1812             $item->{'biblioitemnumber'},
1813             $barcode,
1814             $item->{'dateaccessioned'},
1815             $item->{'booksellerid'},
1816             $item->{'homebranch'},
1817             $item->{'price'},
1818             $item->{'replacementprice'},
1819             $item->{'replacementpricedate'} || $today,
1820             $item->{datelastborrowed},
1821             $item->{datelastseen} || $today,
1822             $item->{stack},
1823             $item->{'notforloan'},
1824             $item->{'damaged'},
1825             $item->{'itemlost'},
1826             $item->{'withdrawn'},
1827             $item->{'itemcallnumber'},
1828             $item->{'coded_location_qualifier'},
1829             $item->{'restricted'},
1830             $item->{'itemnotes'},
1831             $item->{'itemnotes_nonpublic'},
1832             $item->{'holdingbranch'},
1833             $item->{'paidfor'},
1834             $item->{'location'},
1835             $item->{'permanent_location'},
1836             $item->{'onloan'},
1837             $item->{'issues'},
1838             $item->{'renewals'},
1839             $item->{'reserves'},
1840             $item->{'items.cn_source'},
1841             $item->{'items.cn_sort'},
1842             $item->{'ccode'},
1843             $item->{'itype'},
1844             $item->{'materials'},
1845             $item->{'uri'},
1846             $item->{'enumchron'},
1847             $item->{'more_subfields_xml'},
1848             $item->{'copynumber'},
1849             $item->{'stocknumber'},
1850             $item->{'new_status'},
1851     );
1852
1853     my $itemnumber;
1854     if ( defined $sth->errstr ) {
1855         $error.="ERROR in _koha_new_item $query".$sth->errstr;
1856     }
1857     else {
1858         $itemnumber = $dbh->{'mysql_insertid'};
1859     }
1860
1861     return ( $itemnumber, $error );
1862 }
1863
1864 =head2 MoveItemFromBiblio
1865
1866   MoveItemFromBiblio($itenumber, $frombiblio, $tobiblio);
1867
1868 Moves an item from a biblio to another
1869
1870 Returns undef if the move failed or the biblionumber of the destination record otherwise
1871
1872 =cut
1873
1874 sub MoveItemFromBiblio {
1875     my ($itemnumber, $frombiblio, $tobiblio) = @_;
1876     my $dbh = C4::Context->dbh;
1877     my ( $tobiblioitem ) = $dbh->selectrow_array(q|
1878         SELECT biblioitemnumber
1879         FROM biblioitems
1880         WHERE biblionumber = ?
1881     |, undef, $tobiblio );
1882     my $return = $dbh->do(q|
1883         UPDATE items
1884         SET biblioitemnumber = ?,
1885             biblionumber = ?
1886         WHERE itemnumber = ?
1887             AND biblionumber = ?
1888     |, undef, $tobiblioitem, $tobiblio, $itemnumber, $frombiblio );
1889     if ($return == 1) {
1890         ModZebra( $tobiblio, "specialUpdate", "biblioserver" );
1891         ModZebra( $frombiblio, "specialUpdate", "biblioserver" );
1892             # Checking if the item we want to move is in an order
1893         require C4::Acquisition;
1894         my $order = C4::Acquisition::GetOrderFromItemnumber($itemnumber);
1895             if ($order) {
1896                     # Replacing the biblionumber within the order if necessary
1897                     $order->{'biblionumber'} = $tobiblio;
1898                 C4::Acquisition::ModOrder($order);
1899             }
1900
1901         # Update reserves, hold_fill_targets, tmp_holdsqueue and linktracker tables
1902         for my $table_name ( qw( reserves hold_fill_targets tmp_holdsqueue linktracker ) ) {
1903             $dbh->do( qq|
1904                 UPDATE $table_name
1905                 SET biblionumber = ?
1906                 WHERE itemnumber = ?
1907             |, undef, $tobiblio, $itemnumber );
1908         }
1909         return $tobiblio;
1910         }
1911     return;
1912 }
1913
1914 =head2 ItemSafeToDelete
1915
1916    ItemSafeToDelete( $biblionumber, $itemnumber);
1917
1918 Exported function (core API) for checking whether an item record is safe to delete.
1919
1920 returns 1 if the item is safe to delete,
1921
1922 "book_on_loan" if the item is checked out,
1923
1924 "not_same_branch" if the item is blocked by independent branches,
1925
1926 "book_reserved" if the there are holds aganst the item, or
1927
1928 "linked_analytics" if the item has linked analytic records.
1929
1930 =cut
1931
1932 sub ItemSafeToDelete {
1933     my ( $biblionumber, $itemnumber ) = @_;
1934     my $status;
1935     my $dbh = C4::Context->dbh;
1936
1937     my $error;
1938
1939     my $countanalytics = GetAnalyticsCount($itemnumber);
1940
1941     # check that there is no issue on this item before deletion.
1942     my $sth = $dbh->prepare(
1943         q{
1944         SELECT COUNT(*) FROM issues
1945         WHERE itemnumber = ?
1946     }
1947     );
1948     $sth->execute($itemnumber);
1949     my ($onloan) = $sth->fetchrow;
1950
1951     my $item = GetItem($itemnumber);
1952
1953     if ($onloan) {
1954         $status = "book_on_loan";
1955     }
1956     elsif ( defined C4::Context->userenv
1957         and !C4::Context->IsSuperLibrarian()
1958         and C4::Context->preference("IndependentBranches")
1959         and ( C4::Context->userenv->{branch} ne $item->{'homebranch'} ) )
1960     {
1961         $status = "not_same_branch";
1962     }
1963     else {
1964         # check it doesn't have a waiting reserve
1965         $sth = $dbh->prepare(
1966             q{
1967             SELECT COUNT(*) FROM reserves
1968             WHERE (found = 'W' OR found = 'T')
1969             AND itemnumber = ?
1970         }
1971         );
1972         $sth->execute($itemnumber);
1973         my ($reserve) = $sth->fetchrow;
1974         if ($reserve) {
1975             $status = "book_reserved";
1976         }
1977         elsif ( $countanalytics > 0 ) {
1978             $status = "linked_analytics";
1979         }
1980         else {
1981             $status = 1;
1982         }
1983     }
1984     return $status;
1985 }
1986
1987 =head2 DelItemCheck
1988
1989    DelItemCheck( $biblionumber, $itemnumber);
1990
1991 Exported function (core API) for deleting an item record in Koha if there no current issue.
1992
1993 DelItemCheck wraps ItemSafeToDelete around DelItem.
1994
1995 =cut
1996
1997 sub DelItemCheck {
1998     my ( $biblionumber, $itemnumber ) = @_;
1999     my $status = ItemSafeToDelete( $biblionumber, $itemnumber );
2000
2001     if ( $status == 1 ) {
2002         DelItem(
2003             {
2004                 biblionumber => $biblionumber,
2005                 itemnumber   => $itemnumber
2006             }
2007         );
2008     }
2009     return $status;
2010 }
2011
2012 =head2 _koha_modify_item
2013
2014   my ($itemnumber,$error) =_koha_modify_item( $item );
2015
2016 Perform the actual update of the C<items> row.  Note that this
2017 routine accepts a hashref specifying the columns to update.
2018
2019 =cut
2020
2021 sub _koha_modify_item {
2022     my ( $item ) = @_;
2023     my $dbh=C4::Context->dbh;
2024     my $error;
2025
2026     my $query = "UPDATE items SET ";
2027     my @bind;
2028     _mod_item_dates( $item );
2029     for my $key ( keys %$item ) {
2030         next if ( $key eq 'itemnumber' );
2031         $query.="$key=?,";
2032         push @bind, $item->{$key};
2033     }
2034     $query =~ s/,$//;
2035     $query .= " WHERE itemnumber=?";
2036     push @bind, $item->{'itemnumber'};
2037     my $sth = $dbh->prepare($query);
2038     $sth->execute(@bind);
2039     if ( $sth->err ) {
2040         $error.="ERROR in _koha_modify_item $query: ".$sth->errstr;
2041         warn $error;
2042     }
2043     return ($item->{'itemnumber'},$error);
2044 }
2045
2046 sub _mod_item_dates { # date formatting for date fields in item hash
2047     my ( $item ) = @_;
2048     return if !$item || ref($item) ne 'HASH';
2049
2050     my @keys = grep
2051         { $_ =~ /^onloan$|^date|date$|datetime$/ }
2052         keys %$item;
2053     # Incl. dateaccessioned,replacementpricedate,datelastborrowed,datelastseen
2054     # NOTE: We do not (yet) have items fields ending with datetime
2055     # Fields with _on$ have been handled already
2056
2057     foreach my $key ( @keys ) {
2058         next if !defined $item->{$key}; # skip undefs
2059         my $dt = eval { dt_from_string( $item->{$key} ) };
2060             # eval: dt_from_string will die on us if we pass illegal dates
2061
2062         my $newstr;
2063         if( defined $dt  && ref($dt) eq 'DateTime' ) {
2064             if( $key =~ /datetime/ ) {
2065                 $newstr = DateTime::Format::MySQL->format_datetime($dt);
2066             } else {
2067                 $newstr = DateTime::Format::MySQL->format_date($dt);
2068             }
2069         }
2070         $item->{$key} = $newstr; # might be undef to clear garbage
2071     }
2072 }
2073
2074 =head2 _koha_delete_item
2075
2076   _koha_delete_item( $itemnum );
2077
2078 Internal function to delete an item record from the koha tables
2079
2080 =cut
2081
2082 sub _koha_delete_item {
2083     my ( $itemnum ) = @_;
2084
2085     my $dbh = C4::Context->dbh;
2086     # save the deleted item to deleteditems table
2087     my $sth = $dbh->prepare("SELECT * FROM items WHERE itemnumber=?");
2088     $sth->execute($itemnum);
2089     my $data = $sth->fetchrow_hashref();
2090
2091     # There is no item to delete
2092     return 0 unless $data;
2093
2094     my $query = "INSERT INTO deleteditems SET ";
2095     my @bind  = ();
2096     foreach my $key ( keys %$data ) {
2097         next if ( $key eq 'timestamp' ); # timestamp will be set by db
2098         $query .= "$key = ?,";
2099         push( @bind, $data->{$key} );
2100     }
2101     $query =~ s/\,$//;
2102     $sth = $dbh->prepare($query);
2103     $sth->execute(@bind);
2104
2105     # delete from items table
2106     $sth = $dbh->prepare("DELETE FROM items WHERE itemnumber=?");
2107     my $deleted = $sth->execute($itemnum);
2108     return ( $deleted == 1 ) ? 1 : 0;
2109 }
2110
2111 =head2 _marc_from_item_hash
2112
2113   my $item_marc = _marc_from_item_hash($item, $frameworkcode[, $unlinked_item_subfields]);
2114
2115 Given an item hash representing a complete item record,
2116 create a C<MARC::Record> object containing an embedded
2117 tag representing that item.
2118
2119 The third, optional parameter C<$unlinked_item_subfields> is
2120 an arrayref of subfields (not mapped to C<items> fields per the
2121 framework) to be added to the MARC representation
2122 of the item.
2123
2124 =cut
2125
2126 sub _marc_from_item_hash {
2127     my $item = shift;
2128     my $frameworkcode = shift;
2129     my $unlinked_item_subfields;
2130     if (@_) {
2131         $unlinked_item_subfields = shift;
2132     }
2133
2134     # Tack on 'items.' prefix to column names so lookup from MARC frameworks will work
2135     # Also, don't emit a subfield if the underlying field is blank.
2136     my $mungeditem = { map {  (defined($item->{$_}) and $item->{$_} ne '') ?
2137                                 (/^items\./ ? ($_ => $item->{$_}) : ("items.$_" => $item->{$_}))
2138                                 : ()  } keys %{ $item } };
2139
2140     my $item_marc = MARC::Record->new();
2141     foreach my $item_field ( keys %{$mungeditem} ) {
2142         my ( $tag, $subfield ) = C4::Biblio::GetMarcFromKohaField( $item_field, $frameworkcode );
2143         next unless defined $tag and defined $subfield;    # skip if not mapped to MARC field
2144         my @values = split(/\s?\|\s?/, $mungeditem->{$item_field}, -1);
2145         foreach my $value (@values){
2146             if ( my $field = $item_marc->field($tag) ) {
2147                     $field->add_subfields( $subfield => $value );
2148             } else {
2149                 my $add_subfields = [];
2150                 if (defined $unlinked_item_subfields and ref($unlinked_item_subfields) eq 'ARRAY' and $#$unlinked_item_subfields > -1) {
2151                     $add_subfields = $unlinked_item_subfields;
2152             }
2153             $item_marc->add_fields( $tag, " ", " ", $subfield => $value, @$add_subfields );
2154             }
2155         }
2156     }
2157
2158     return $item_marc;
2159 }
2160
2161 =head2 _repack_item_errors
2162
2163 Add an error message hash generated by C<CheckItemPreSave>
2164 to a list of errors.
2165
2166 =cut
2167
2168 sub _repack_item_errors {
2169     my $item_sequence_num = shift;
2170     my $item_ref = shift;
2171     my $error_ref = shift;
2172
2173     my @repacked_errors = ();
2174
2175     foreach my $error_code (sort keys %{ $error_ref }) {
2176         my $repacked_error = {};
2177         $repacked_error->{'item_sequence'} = $item_sequence_num;
2178         $repacked_error->{'item_barcode'} = exists($item_ref->{'barcode'}) ? $item_ref->{'barcode'} : '';
2179         $repacked_error->{'error_code'} = $error_code;
2180         $repacked_error->{'error_information'} = $error_ref->{$error_code};
2181         push @repacked_errors, $repacked_error;
2182     }
2183
2184     return @repacked_errors;
2185 }
2186
2187 =head2 _get_unlinked_item_subfields
2188
2189   my $unlinked_item_subfields = _get_unlinked_item_subfields($original_item_marc, $frameworkcode);
2190
2191 =cut
2192
2193 sub _get_unlinked_item_subfields {
2194     my $original_item_marc = shift;
2195     my $frameworkcode = shift;
2196
2197     my $marcstructure = GetMarcStructure(1, $frameworkcode, { unsafe => 1 });
2198
2199     # assume that this record has only one field, and that that
2200     # field contains only the item information
2201     my $subfields = [];
2202     my @fields = $original_item_marc->fields();
2203     if ($#fields > -1) {
2204         my $field = $fields[0];
2205             my $tag = $field->tag();
2206         foreach my $subfield ($field->subfields()) {
2207             if (defined $subfield->[1] and
2208                 $subfield->[1] ne '' and
2209                 !$marcstructure->{$tag}->{$subfield->[0]}->{'kohafield'}) {
2210                 push @$subfields, $subfield->[0] => $subfield->[1];
2211             }
2212         }
2213     }
2214     return $subfields;
2215 }
2216
2217 =head2 _get_unlinked_subfields_xml
2218
2219   my $unlinked_subfields_xml = _get_unlinked_subfields_xml($unlinked_item_subfields);
2220
2221 =cut
2222
2223 sub _get_unlinked_subfields_xml {
2224     my $unlinked_item_subfields = shift;
2225
2226     my $xml;
2227     if (defined $unlinked_item_subfields and ref($unlinked_item_subfields) eq 'ARRAY' and $#$unlinked_item_subfields > -1) {
2228         my $marc = MARC::Record->new();
2229         # use of tag 999 is arbitrary, and doesn't need to match the item tag
2230         # used in the framework
2231         $marc->append_fields(MARC::Field->new('999', ' ', ' ', @$unlinked_item_subfields));
2232         $marc->encoding("UTF-8");
2233         $xml = $marc->as_xml("USMARC");
2234     }
2235
2236     return $xml;
2237 }
2238
2239 =head2 _parse_unlinked_item_subfields_from_xml
2240
2241   my $unlinked_item_subfields = _parse_unlinked_item_subfields_from_xml($whole_item->{'more_subfields_xml'}):
2242
2243 =cut
2244
2245 sub  _parse_unlinked_item_subfields_from_xml {
2246     my $xml = shift;
2247     require C4::Charset;
2248     return unless defined $xml and $xml ne "";
2249     my $marc = MARC::Record->new_from_xml(C4::Charset::StripNonXmlChars($xml),'UTF-8');
2250     my $unlinked_subfields = [];
2251     my @fields = $marc->fields();
2252     if ($#fields > -1) {
2253         foreach my $subfield ($fields[0]->subfields()) {
2254             push @$unlinked_subfields, $subfield->[0] => $subfield->[1];
2255         }
2256     }
2257     return $unlinked_subfields;
2258 }
2259
2260 =head2 GetAnalyticsCount
2261
2262   $count= &GetAnalyticsCount($itemnumber)
2263
2264 counts Usage of itemnumber in Analytical bibliorecords.
2265
2266 =cut
2267
2268 sub GetAnalyticsCount {
2269     my ($itemnumber) = @_;
2270
2271     ### ZOOM search here
2272     my $query;
2273     $query= "hi=".$itemnumber;
2274     my $searcher = Koha::SearchEngine::Search->new({index => $Koha::SearchEngine::BIBLIOS_INDEX});
2275     my ($err,$res,$result) = $searcher->simple_search_compat($query,0,10);
2276     return ($result);
2277 }
2278
2279 =head2 SearchItemsByField
2280
2281     my $items = SearchItemsByField($field, $value);
2282
2283 SearchItemsByField will search for items on a specific given field.
2284 For instance you can search all items with a specific stocknumber like this:
2285
2286     my $items = SearchItemsByField('stocknumber', $stocknumber);
2287
2288 =cut
2289
2290 sub SearchItemsByField {
2291     my ($field, $value) = @_;
2292
2293     my $filters = {
2294         field => $field,
2295         query => $value,
2296     };
2297
2298     my ($results) = SearchItems($filters);
2299     return $results;
2300 }
2301
2302 sub _SearchItems_build_where_fragment {
2303     my ($filter) = @_;
2304
2305     my $dbh = C4::Context->dbh;
2306
2307     my $where_fragment;
2308     if (exists($filter->{conjunction})) {
2309         my (@where_strs, @where_args);
2310         foreach my $f (@{ $filter->{filters} }) {
2311             my $fragment = _SearchItems_build_where_fragment($f);
2312             if ($fragment) {
2313                 push @where_strs, $fragment->{str};
2314                 push @where_args, @{ $fragment->{args} };
2315             }
2316         }
2317         my $where_str = '';
2318         if (@where_strs) {
2319             $where_str = '(' . join (' ' . $filter->{conjunction} . ' ', @where_strs) . ')';
2320             $where_fragment = {
2321                 str => $where_str,
2322                 args => \@where_args,
2323             };
2324         }
2325     } else {
2326         my @columns = Koha::Database->new()->schema()->resultset('Item')->result_source->columns;
2327         push @columns, Koha::Database->new()->schema()->resultset('Biblio')->result_source->columns;
2328         push @columns, Koha::Database->new()->schema()->resultset('Biblioitem')->result_source->columns;
2329         my @operators = qw(= != > < >= <= like);
2330         my $field = $filter->{field};
2331         if ( (0 < grep /^$field$/, @columns) or (substr($field, 0, 5) eq 'marc:') ) {
2332             my $op = $filter->{operator};
2333             my $query = $filter->{query};
2334
2335             if (!$op or (0 == grep /^$op$/, @operators)) {
2336                 $op = '='; # default operator
2337             }
2338
2339             my $column;
2340             if ($field =~ /^marc:(\d{3})(?:\$(\w))?$/) {
2341                 my $marcfield = $1;
2342                 my $marcsubfield = $2;
2343                 my ($kohafield) = $dbh->selectrow_array(q|
2344                     SELECT kohafield FROM marc_subfield_structure
2345                     WHERE tagfield=? AND tagsubfield=? AND frameworkcode=''
2346                 |, undef, $marcfield, $marcsubfield);
2347
2348                 if ($kohafield) {
2349                     $column = $kohafield;
2350                 } else {
2351                     # MARC field is not linked to a DB field so we need to use
2352                     # ExtractValue on marcxml from biblio_metadata or
2353                     # items.more_subfields_xml, depending on the MARC field.
2354                     my $xpath;
2355                     my $sqlfield;
2356                     my ($itemfield) = C4::Biblio::GetMarcFromKohaField('items.itemnumber');
2357                     if ($marcfield eq $itemfield) {
2358                         $sqlfield = 'more_subfields_xml';
2359                         $xpath = '//record/datafield/subfield[@code="' . $marcsubfield . '"]';
2360                     } else {
2361                         $sqlfield = 'metadata'; # From biblio_metadata
2362                         if ($marcfield < 10) {
2363                             $xpath = "//record/controlfield[\@tag=\"$marcfield\"]";
2364                         } else {
2365                             $xpath = "//record/datafield[\@tag=\"$marcfield\"]/subfield[\@code=\"$marcsubfield\"]";
2366                         }
2367                     }
2368                     $column = "ExtractValue($sqlfield, '$xpath')";
2369                 }
2370             } else {
2371                 $column = $field;
2372             }
2373
2374             if (ref $query eq 'ARRAY') {
2375                 if ($op eq '=') {
2376                     $op = 'IN';
2377                 } elsif ($op eq '!=') {
2378                     $op = 'NOT IN';
2379                 }
2380                 $where_fragment = {
2381                     str => "$column $op (" . join (',', ('?') x @$query) . ")",
2382                     args => $query,
2383                 };
2384             } else {
2385                 $where_fragment = {
2386                     str => "$column $op ?",
2387                     args => [ $query ],
2388                 };
2389             }
2390         }
2391     }
2392
2393     return $where_fragment;
2394 }
2395
2396 =head2 SearchItems
2397
2398     my ($items, $total) = SearchItems($filter, $params);
2399
2400 Perform a search among items
2401
2402 $filter is a reference to a hash which can be a filter, or a combination of filters.
2403
2404 A filter has the following keys:
2405
2406 =over 2
2407
2408 =item * field: the name of a SQL column in table items
2409
2410 =item * query: the value to search in this column
2411
2412 =item * operator: comparison operator. Can be one of = != > < >= <= like
2413
2414 =back
2415
2416 A combination of filters hash the following keys:
2417
2418 =over 2
2419
2420 =item * conjunction: 'AND' or 'OR'
2421
2422 =item * filters: array ref of filters
2423
2424 =back
2425
2426 $params is a reference to a hash that can contain the following parameters:
2427
2428 =over 2
2429
2430 =item * rows: Number of items to return. 0 returns everything (default: 0)
2431
2432 =item * page: Page to return (return items from (page-1)*rows to (page*rows)-1)
2433                (default: 1)
2434
2435 =item * sortby: A SQL column name in items table to sort on
2436
2437 =item * sortorder: 'ASC' or 'DESC'
2438
2439 =back
2440
2441 =cut
2442
2443 sub SearchItems {
2444     my ($filter, $params) = @_;
2445
2446     $filter //= {};
2447     $params //= {};
2448     return unless ref $filter eq 'HASH';
2449     return unless ref $params eq 'HASH';
2450
2451     # Default parameters
2452     $params->{rows} ||= 0;
2453     $params->{page} ||= 1;
2454     $params->{sortby} ||= 'itemnumber';
2455     $params->{sortorder} ||= 'ASC';
2456
2457     my ($where_str, @where_args);
2458     my $where_fragment = _SearchItems_build_where_fragment($filter);
2459     if ($where_fragment) {
2460         $where_str = $where_fragment->{str};
2461         @where_args = @{ $where_fragment->{args} };
2462     }
2463
2464     my $dbh = C4::Context->dbh;
2465     my $query = q{
2466         SELECT SQL_CALC_FOUND_ROWS items.*
2467         FROM items
2468           LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber
2469           LEFT JOIN biblioitems ON biblioitems.biblioitemnumber = items.biblioitemnumber
2470           LEFT JOIN biblio_metadata ON biblio_metadata.biblionumber = biblio.biblionumber
2471           WHERE 1
2472     };
2473     if (defined $where_str and $where_str ne '') {
2474         $query .= qq{ AND $where_str };
2475     }
2476
2477     $query .= q{ AND biblio_metadata.format = 'marcxml' AND biblio_metadata.marcflavour = ? };
2478     push @where_args, C4::Context->preference('marcflavour');
2479
2480     my @columns = Koha::Database->new()->schema()->resultset('Item')->result_source->columns;
2481     push @columns, Koha::Database->new()->schema()->resultset('Biblio')->result_source->columns;
2482     push @columns, Koha::Database->new()->schema()->resultset('Biblioitem')->result_source->columns;
2483     my $sortby = (0 < grep {$params->{sortby} eq $_} @columns)
2484         ? $params->{sortby} : 'itemnumber';
2485     my $sortorder = (uc($params->{sortorder}) eq 'ASC') ? 'ASC' : 'DESC';
2486     $query .= qq{ ORDER BY $sortby $sortorder };
2487
2488     my $rows = $params->{rows};
2489     my @limit_args;
2490     if ($rows > 0) {
2491         my $offset = $rows * ($params->{page}-1);
2492         $query .= qq { LIMIT ?, ? };
2493         push @limit_args, $offset, $rows;
2494     }
2495
2496     my $sth = $dbh->prepare($query);
2497     my $rv = $sth->execute(@where_args, @limit_args);
2498
2499     return unless ($rv);
2500     my ($total_rows) = $dbh->selectrow_array(q{ SELECT FOUND_ROWS() });
2501
2502     return ($sth->fetchall_arrayref({}), $total_rows);
2503 }
2504
2505
2506 =head1  OTHER FUNCTIONS
2507
2508 =head2 _find_value
2509
2510   ($indicators, $value) = _find_value($tag, $subfield, $record,$encoding);
2511
2512 Find the given $subfield in the given $tag in the given
2513 MARC::Record $record.  If the subfield is found, returns
2514 the (indicators, value) pair; otherwise, (undef, undef) is
2515 returned.
2516
2517 PROPOSITION :
2518 Such a function is used in addbiblio AND additem and serial-edit and maybe could be used in Authorities.
2519 I suggest we export it from this module.
2520
2521 =cut
2522
2523 sub _find_value {
2524     my ( $tagfield, $insubfield, $record, $encoding ) = @_;
2525     my @result;
2526     my $indicator;
2527     if ( $tagfield < 10 ) {
2528         if ( $record->field($tagfield) ) {
2529             push @result, $record->field($tagfield)->data();
2530         } else {
2531             push @result, "";
2532         }
2533     } else {
2534         foreach my $field ( $record->field($tagfield) ) {
2535             my @subfields = $field->subfields();
2536             foreach my $subfield (@subfields) {
2537                 if ( @$subfield[0] eq $insubfield ) {
2538                     push @result, @$subfield[1];
2539                     $indicator = $field->indicator(1) . $field->indicator(2);
2540                 }
2541             }
2542         }
2543     }
2544     return ( $indicator, @result );
2545 }
2546
2547
2548 =head2 PrepareItemrecordDisplay
2549
2550   PrepareItemrecordDisplay($itemrecord,$bibnum,$itemumber,$frameworkcode);
2551
2552 Returns a hash with all the fields for Display a given item data in a template
2553
2554 The $frameworkcode returns the item for the given frameworkcode, ONLY if bibnum is not provided
2555
2556 =cut
2557
2558 sub PrepareItemrecordDisplay {
2559
2560     my ( $bibnum, $itemnum, $defaultvalues, $frameworkcode ) = @_;
2561
2562     my $dbh = C4::Context->dbh;
2563     $frameworkcode = C4::Biblio::GetFrameworkCode($bibnum) if $bibnum;
2564     my ( $itemtagfield, $itemtagsubfield ) = C4::Biblio::GetMarcFromKohaField( "items.itemnumber", $frameworkcode );
2565
2566     # Note: $tagslib obtained from GetMarcStructure() in 'unsafe' mode is
2567     # a shared data structure. No plugin (including custom ones) should change
2568     # its contents. See also GetMarcStructure.
2569     my $tagslib = &GetMarcStructure( 1, $frameworkcode, { unsafe => 1 } );
2570
2571     # return nothing if we don't have found an existing framework.
2572     return q{} unless $tagslib;
2573     my $itemrecord;
2574     if ($itemnum) {
2575         $itemrecord = C4::Items::GetMarcItem( $bibnum, $itemnum );
2576     }
2577     my @loop_data;
2578
2579     my $branch_limit = C4::Context->userenv ? C4::Context->userenv->{"branch"} : "";
2580     my $query = qq{
2581         SELECT authorised_value,lib FROM authorised_values
2582     };
2583     $query .= qq{
2584         LEFT JOIN authorised_values_branches ON ( id = av_id )
2585     } if $branch_limit;
2586     $query .= qq{
2587         WHERE category = ?
2588     };
2589     $query .= qq{ AND ( branchcode = ? OR branchcode IS NULL )} if $branch_limit;
2590     $query .= qq{ ORDER BY lib};
2591     my $authorised_values_sth = $dbh->prepare( $query );
2592     foreach my $tag ( sort keys %{$tagslib} ) {
2593         if ( $tag ne '' ) {
2594
2595             # loop through each subfield
2596             my $cntsubf;
2597             foreach my $subfield ( sort keys %{ $tagslib->{$tag} } ) {
2598                 next if IsMarcStructureInternal($tagslib->{$tag}{$subfield});
2599                 next unless ( $tagslib->{$tag}->{$subfield}->{'tab'} );
2600                 next if ( $tagslib->{$tag}->{$subfield}->{'tab'} ne "10" );
2601                 my %subfield_data;
2602                 $subfield_data{tag}           = $tag;
2603                 $subfield_data{subfield}      = $subfield;
2604                 $subfield_data{countsubfield} = $cntsubf++;
2605                 $subfield_data{kohafield}     = $tagslib->{$tag}->{$subfield}->{'kohafield'};
2606                 $subfield_data{id}            = "tag_".$tag."_subfield_".$subfield."_".int(rand(1000000));
2607
2608                 #        $subfield_data{marc_lib}=$tagslib->{$tag}->{$subfield}->{lib};
2609                 $subfield_data{marc_lib}   = $tagslib->{$tag}->{$subfield}->{lib};
2610                 $subfield_data{mandatory}  = $tagslib->{$tag}->{$subfield}->{mandatory};
2611                 $subfield_data{repeatable} = $tagslib->{$tag}->{$subfield}->{repeatable};
2612                 $subfield_data{hidden}     = "display:none"
2613                   if ( ( $tagslib->{$tag}->{$subfield}->{hidden} > 4 )
2614                     || ( $tagslib->{$tag}->{$subfield}->{hidden} < -4 ) );
2615                 my ( $x, $defaultvalue );
2616                 if ($itemrecord) {
2617                     ( $x, $defaultvalue ) = _find_value( $tag, $subfield, $itemrecord );
2618                 }
2619                 $defaultvalue = $tagslib->{$tag}->{$subfield}->{defaultvalue} unless $defaultvalue;
2620                 if ( !defined $defaultvalue ) {
2621                     $defaultvalue = q||;
2622                 } else {
2623                     $defaultvalue =~ s/"/&quot;/g;
2624                 }
2625
2626                 # search for itemcallnumber if applicable
2627                 if ( $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.itemcallnumber'
2628                     && C4::Context->preference('itemcallnumber') ) {
2629                     my $CNtag      = substr( C4::Context->preference('itemcallnumber'), 0, 3 );
2630                     my $CNsubfield = substr( C4::Context->preference('itemcallnumber'), 3, 1 );
2631                     if ( $itemrecord and my $field = $itemrecord->field($CNtag) ) {
2632                         $defaultvalue = $field->subfield($CNsubfield);
2633                     }
2634                 }
2635                 if (   $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.itemcallnumber'
2636                     && $defaultvalues
2637                     && $defaultvalues->{'callnumber'} ) {
2638                     if( $itemrecord and $defaultvalues and not $itemrecord->subfield($tag,$subfield) ){
2639                         # if the item record exists, only use default value if the item has no callnumber
2640                         $defaultvalue = $defaultvalues->{callnumber};
2641                     } elsif ( !$itemrecord and $defaultvalues ) {
2642                         # if the item record *doesn't* exists, always use the default value
2643                         $defaultvalue = $defaultvalues->{callnumber};
2644                     }
2645                 }
2646                 if (   ( $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.holdingbranch' || $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.homebranch' )
2647                     && $defaultvalues
2648                     && $defaultvalues->{'branchcode'} ) {
2649                     if ( $itemrecord and $defaultvalues and not $itemrecord->subfield($tag,$subfield) ) {
2650                         $defaultvalue = $defaultvalues->{branchcode};
2651                     }
2652                 }
2653                 if (   ( $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.location' )
2654                     && $defaultvalues
2655                     && $defaultvalues->{'location'} ) {
2656
2657                     if ( $itemrecord and $defaultvalues and not $itemrecord->subfield($tag,$subfield) ) {
2658                         # if the item record exists, only use default value if the item has no locationr
2659                         $defaultvalue = $defaultvalues->{location};
2660                     } elsif ( !$itemrecord and $defaultvalues ) {
2661                         # if the item record *doesn't* exists, always use the default value
2662                         $defaultvalue = $defaultvalues->{location};
2663                     }
2664                 }
2665                 if ( $tagslib->{$tag}->{$subfield}->{authorised_value} ) {
2666                     my @authorised_values;
2667                     my %authorised_lib;
2668
2669                     # builds list, depending on authorised value...
2670                     #---- branch
2671                     if ( $tagslib->{$tag}->{$subfield}->{'authorised_value'} eq "branches" ) {
2672                         if (   ( C4::Context->preference("IndependentBranches") )
2673                             && !C4::Context->IsSuperLibrarian() ) {
2674                             my $sth = $dbh->prepare( "SELECT branchcode,branchname FROM branches WHERE branchcode = ? ORDER BY branchname" );
2675                             $sth->execute( C4::Context->userenv->{branch} );
2676                             push @authorised_values, ""
2677                               unless ( $tagslib->{$tag}->{$subfield}->{mandatory} );
2678                             while ( my ( $branchcode, $branchname ) = $sth->fetchrow_array ) {
2679                                 push @authorised_values, $branchcode;
2680                                 $authorised_lib{$branchcode} = $branchname;
2681                             }
2682                         } else {
2683                             my $sth = $dbh->prepare( "SELECT branchcode,branchname FROM branches ORDER BY branchname" );
2684                             $sth->execute;
2685                             push @authorised_values, ""
2686                               unless ( $tagslib->{$tag}->{$subfield}->{mandatory} );
2687                             while ( my ( $branchcode, $branchname ) = $sth->fetchrow_array ) {
2688                                 push @authorised_values, $branchcode;
2689                                 $authorised_lib{$branchcode} = $branchname;
2690                             }
2691                         }
2692
2693                         $defaultvalue = C4::Context->userenv ? C4::Context->userenv->{branch} : undef;
2694                         if ( $defaultvalues and $defaultvalues->{branchcode} ) {
2695                             $defaultvalue = $defaultvalues->{branchcode};
2696                         }
2697
2698                         #----- itemtypes
2699                     } elsif ( $tagslib->{$tag}->{$subfield}->{authorised_value} eq "itemtypes" ) {
2700                         my $itemtypes = Koha::ItemTypes->search_with_localization;
2701                         push @authorised_values, ""
2702                           unless ( $tagslib->{$tag}->{$subfield}->{mandatory} );
2703                         while ( my $itemtype = $itemtypes->next ) {
2704                             push @authorised_values, $itemtype->itemtype;
2705                             $authorised_lib{$itemtype->itemtype} = $itemtype->translated_description;
2706                         }
2707                         if ($defaultvalues && $defaultvalues->{'itemtype'}) {
2708                             $defaultvalue = $defaultvalues->{'itemtype'};
2709                         }
2710
2711                         #---- class_sources
2712                     } elsif ( $tagslib->{$tag}->{$subfield}->{authorised_value} eq "cn_source" ) {
2713                         push @authorised_values, "" unless ( $tagslib->{$tag}->{$subfield}->{mandatory} );
2714
2715                         my $class_sources = GetClassSources();
2716                         my $default_source = C4::Context->preference("DefaultClassificationSource");
2717
2718                         foreach my $class_source (sort keys %$class_sources) {
2719                             next unless $class_sources->{$class_source}->{'used'} or
2720                                         ($class_source eq $default_source);
2721                             push @authorised_values, $class_source;
2722                             $authorised_lib{$class_source} = $class_sources->{$class_source}->{'description'};
2723                         }
2724
2725                         $defaultvalue = $default_source;
2726
2727                         #---- "true" authorised value
2728                     } else {
2729                         $authorised_values_sth->execute(
2730                             $tagslib->{$tag}->{$subfield}->{authorised_value},
2731                             $branch_limit ? $branch_limit : ()
2732                         );
2733                         push @authorised_values, ""
2734                           unless ( $tagslib->{$tag}->{$subfield}->{mandatory} );
2735                         while ( my ( $value, $lib ) = $authorised_values_sth->fetchrow_array ) {
2736                             push @authorised_values, $value;
2737                             $authorised_lib{$value} = $lib;
2738                         }
2739                     }
2740                     $subfield_data{marc_value} = {
2741                         type    => 'select',
2742                         values  => \@authorised_values,
2743                         default => "$defaultvalue",
2744                         labels  => \%authorised_lib,
2745                     };
2746                 } elsif ( $tagslib->{$tag}->{$subfield}->{value_builder} ) {
2747                 # it is a plugin
2748                     require Koha::FrameworkPlugin;
2749                     my $plugin = Koha::FrameworkPlugin->new({
2750                         name => $tagslib->{$tag}->{$subfield}->{value_builder},
2751                         item_style => 1,
2752                     });
2753                     my $pars = { dbh => $dbh, record => undef, tagslib =>$tagslib, id => $subfield_data{id}, tabloop => undef };
2754                     $plugin->build( $pars );
2755                     if ( $itemrecord and my $field = $itemrecord->field($tag) ) {
2756                         $defaultvalue = $field->subfield($subfield);
2757                     }
2758                     if( !$plugin->errstr ) {
2759                         #TODO Move html to template; see report 12176/13397
2760                         my $tab= $plugin->noclick? '-1': '';
2761                         my $class= $plugin->noclick? ' disabled': '';
2762                         my $title= $plugin->noclick? 'No popup': 'Tag editor';
2763                         $subfield_data{marc_value} = qq[<input type="text" id="$subfield_data{id}" name="field_value" class="input_marceditor" size="50" maxlength="255" value="$defaultvalue" /><a href="#" id="buttonDot_$subfield_data{id}" tabindex="$tab" class="buttonDot $class" title="$title">...</a>\n].$plugin->javascript;
2764                     } else {
2765                         warn $plugin->errstr;
2766                         $subfield_data{marc_value} = qq(<input type="text" id="$subfield_data{id}" name="field_value" class="input_marceditor" size="50" maxlength="255" value="$defaultvalue" />); # supply default input form
2767                     }
2768                 }
2769                 elsif ( $tag eq '' ) {       # it's an hidden field
2770                     $subfield_data{marc_value} = qq(<input type="hidden" tabindex="1" id="$subfield_data{id}" name="field_value" class="input_marceditor" size="50" maxlength="255" value="$defaultvalue" />);
2771                 }
2772                 elsif ( $tagslib->{$tag}->{$subfield}->{'hidden'} ) {   # FIXME: shouldn't input type be "hidden" ?
2773                     $subfield_data{marc_value} = qq(<input type="text" tabindex="1" id="$subfield_data{id}" name="field_value" class="input_marceditor" size="50" maxlength="255" value="$defaultvalue" />);
2774                 }
2775                 elsif ( length($defaultvalue) > 100
2776                             or (C4::Context->preference("marcflavour") eq "UNIMARC" and
2777                                   300 <= $tag && $tag < 400 && $subfield eq 'a' )
2778                             or (C4::Context->preference("marcflavour") eq "MARC21"  and
2779                                   500 <= $tag && $tag < 600                     )
2780                           ) {
2781                     # oversize field (textarea)
2782                     $subfield_data{marc_value} = qq(<textarea tabindex="1" id="$subfield_data{id}" name="field_value" class="input_marceditor" size="50" maxlength="255">$defaultvalue</textarea>\n");
2783                 } else {
2784                     $subfield_data{marc_value} = "<input type=\"text\" name=\"field_value\" value=\"$defaultvalue\" size=\"50\" maxlength=\"255\" />";
2785                 }
2786                 push( @loop_data, \%subfield_data );
2787             }
2788         }
2789     }
2790     my $itemnumber;
2791     if ( $itemrecord && $itemrecord->field($itemtagfield) ) {
2792         $itemnumber = $itemrecord->subfield( $itemtagfield, $itemtagsubfield );
2793     }
2794     return {
2795         'itemtagfield'    => $itemtagfield,
2796         'itemtagsubfield' => $itemtagsubfield,
2797         'itemnumber'      => $itemnumber,
2798         'iteminformation' => \@loop_data
2799     };
2800 }
2801
2802 sub ToggleNewStatus {
2803     my ( $params ) = @_;
2804     my @rules = @{ $params->{rules} };
2805     my $report_only = $params->{report_only};
2806
2807     my $dbh = C4::Context->dbh;
2808     my @errors;
2809     my @item_columns = map { "items.$_" } Koha::Items->columns;
2810     my @biblioitem_columns = map { "biblioitems.$_" } Koha::Biblioitems->columns;
2811     my $report;
2812     for my $rule ( @rules ) {
2813         my $age = $rule->{age};
2814         my $conditions = $rule->{conditions};
2815         my $substitutions = $rule->{substitutions};
2816         my @params;
2817
2818         my $query = q|
2819             SELECT items.biblionumber, items.itemnumber
2820             FROM items
2821             LEFT JOIN biblioitems ON biblioitems.biblionumber = items.biblionumber
2822             WHERE 1
2823         |;
2824         for my $condition ( @$conditions ) {
2825             if (
2826                  grep {/^$condition->{field}$/} @item_columns
2827               or grep {/^$condition->{field}$/} @biblioitem_columns
2828             ) {
2829                 if ( $condition->{value} =~ /\|/ ) {
2830                     my @values = split /\|/, $condition->{value};
2831                     $query .= qq| AND $condition->{field} IN (|
2832                         . join( ',', ('?') x scalar @values )
2833                         . q|)|;
2834                     push @params, @values;
2835                 } else {
2836                     $query .= qq| AND $condition->{field} = ?|;
2837                     push @params, $condition->{value};
2838                 }
2839             }
2840         }
2841         if ( defined $age ) {
2842             $query .= q| AND TO_DAYS(NOW()) - TO_DAYS(dateaccessioned) >= ? |;
2843             push @params, $age;
2844         }
2845         my $sth = $dbh->prepare($query);
2846         $sth->execute( @params );
2847         while ( my $values = $sth->fetchrow_hashref ) {
2848             my $biblionumber = $values->{biblionumber};
2849             my $itemnumber = $values->{itemnumber};
2850             my $item = C4::Items::GetItem( $itemnumber );
2851             for my $substitution ( @$substitutions ) {
2852                 next unless $substitution->{field};
2853                 C4::Items::ModItem( {$substitution->{field} => $substitution->{value}}, $biblionumber, $itemnumber )
2854                     unless $report_only;
2855                 push @{ $report->{$itemnumber} }, $substitution;
2856             }
2857         }
2858     }
2859
2860     return $report;
2861 }
2862
2863
2864 1;