C4/Items.pm

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