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