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