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