C4/Items.pm

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