C4/Items.pm

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