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      FROM items
1303      LEFT JOIN branches AS holding ON items.holdingbranch = holding.branchcode
1304      LEFT JOIN branches AS home ON items.homebranch=home.branchcode
1305      LEFT JOIN biblio      ON      biblio.biblionumber     = items.biblionumber
1306      LEFT JOIN biblioitems ON biblioitems.biblioitemnumber = items.biblioitemnumber
1307      LEFT JOIN itemtypes   ON   itemtypes.itemtype         = "
1308      . (C4::Context->preference('item-level_itypes') ? 'items.itype' : 'biblioitems.itemtype');
1309     $query .= " WHERE items.biblionumber = ? ORDER BY home.branchname, items.enumchron, LPAD( items.copynumber, 8, '0' ), items.dateaccessioned DESC" ;
1310     my $sth = $dbh->prepare($query);
1311     $sth->execute($biblionumber);
1312     my $i = 0;
1313     my @results;
1314     my $serial;
1315
1316     my $isth    = $dbh->prepare(
1317         "SELECT issues.*,borrowers.cardnumber,borrowers.surname,borrowers.firstname,borrowers.branchcode as bcode
1318         FROM   issues LEFT JOIN borrowers ON issues.borrowernumber=borrowers.borrowernumber
1319         WHERE  itemnumber = ?"
1320        );
1321         my $ssth = $dbh->prepare("SELECT serialseq,publisheddate from serialitems left join serial on serialitems.serialid=serial.serialid where serialitems.itemnumber=? ");
1322         while ( my $data = $sth->fetchrow_hashref ) {
1323         my $datedue = '';
1324         $isth->execute( $data->{'itemnumber'} );
1325         if ( my $idata = $isth->fetchrow_hashref ) {
1326             $data->{borrowernumber} = $idata->{borrowernumber};
1327             $data->{cardnumber}     = $idata->{cardnumber};
1328             $data->{surname}     = $idata->{surname};
1329             $data->{firstname}     = $idata->{firstname};
1330             $data->{lastreneweddate} = $idata->{lastreneweddate};
1331             $datedue                = $idata->{'date_due'};
1332         if (C4::Context->preference("IndependentBranches")){
1333         my $userenv = C4::Context->userenv;
1334         unless ( C4::Context->IsSuperLibrarian() ) {
1335             $data->{'NOTSAMEBRANCH'} = 1 if ($idata->{'bcode'} ne $userenv->{branch});
1336         }
1337         }
1338         }
1339                 if ( $data->{'serial'}) {
1340                         $ssth->execute($data->{'itemnumber'}) ;
1341                         ($data->{'serialseq'} , $data->{'publisheddate'}) = $ssth->fetchrow_array();
1342                         $serial = 1;
1343         }
1344         #get branch information.....
1345         my $bsth = $dbh->prepare(
1346             "SELECT * FROM branches WHERE branchcode = ?
1347         "
1348         );
1349         $bsth->execute( $data->{'holdingbranch'} );
1350         if ( my $bdata = $bsth->fetchrow_hashref ) {
1351             $data->{'branchname'} = $bdata->{'branchname'};
1352         }
1353         $data->{'datedue'}        = $datedue;
1354
1355         # get notforloan complete status if applicable
1356         if ( my $code = C4::Koha::GetAuthValCode( 'items.notforloan', $data->{frameworkcode} ) ) {
1357             $data->{notforloanvalue}     = C4::Koha::GetKohaAuthorisedValueLib( $code, $data->{itemnotforloan} );
1358             $data->{notforloanvalueopac} = C4::Koha::GetKohaAuthorisedValueLib( $code, $data->{itemnotforloan}, 1 );
1359         }
1360
1361         # get restricted status and description if applicable
1362         if ( my $code = C4::Koha::GetAuthValCode( 'items.restricted', $data->{frameworkcode} ) ) {
1363             $data->{restrictedopac} = C4::Koha::GetKohaAuthorisedValueLib( $code, $data->{restricted}, 1 );
1364             $data->{restricted}     = C4::Koha::GetKohaAuthorisedValueLib( $code, $data->{restricted} );
1365         }
1366
1367         # my stack procedures
1368         if ( my $code = C4::Koha::GetAuthValCode( 'items.stack', $data->{frameworkcode} ) ) {
1369             $data->{stack}          = C4::Koha::GetKohaAuthorisedValueLib( $code, $data->{stack} );
1370         }
1371         # Find the last 3 people who borrowed this item.
1372         my $sth2 = $dbh->prepare("SELECT * FROM old_issues,borrowers
1373                                     WHERE itemnumber = ?
1374                                     AND old_issues.borrowernumber = borrowers.borrowernumber
1375                                     ORDER BY returndate DESC
1376                                     LIMIT 3");
1377         $sth2->execute($data->{'itemnumber'});
1378         my $ii = 0;
1379         while (my $data2 = $sth2->fetchrow_hashref()) {
1380             $data->{"timestamp$ii"} = $data2->{'timestamp'} if $data2->{'timestamp'};
1381             $data->{"card$ii"}      = $data2->{'cardnumber'} if $data2->{'cardnumber'};
1382             $data->{"borrower$ii"}  = $data2->{'borrowernumber'} if $data2->{'borrowernumber'};
1383             $ii++;
1384         }
1385
1386         $results[$i] = $data;
1387         $i++;
1388     }
1389         if($serial) {
1390                 return( sort { ($b->{'publisheddate'} || $b->{'enumchron'}) cmp ($a->{'publisheddate'} || $a->{'enumchron'}) } @results );
1391         } else {
1392         return (@results);
1393         }
1394 }
1395
1396 =head2 GetItemsLocationInfo
1397
1398   my @itemlocinfo = GetItemsLocationInfo($biblionumber);
1399
1400 Returns the branch names, shelving location and itemcallnumber for each item attached to the biblio in question
1401
1402 C<GetItemsInfo> returns a list of references-to-hash. Data returned:
1403
1404 =over 2
1405
1406 =item C<$data-E<gt>{homebranch}>
1407
1408 Branch Name of the item's homebranch
1409
1410 =item C<$data-E<gt>{holdingbranch}>
1411
1412 Branch Name of the item's holdingbranch
1413
1414 =item C<$data-E<gt>{location}>
1415
1416 Item's shelving location code
1417
1418 =item C<$data-E<gt>{location_intranet}>
1419
1420 The intranet description for the Shelving Location as set in authorised_values 'LOC'
1421
1422 =item C<$data-E<gt>{location_opac}>
1423
1424 The OPAC description for the Shelving Location as set in authorised_values 'LOC'.  Falls back to intranet description if no OPAC
1425 description is set.
1426
1427 =item C<$data-E<gt>{itemcallnumber}>
1428
1429 Item's itemcallnumber
1430
1431 =item C<$data-E<gt>{cn_sort}>
1432
1433 Item's call number normalized for sorting
1434
1435 =back
1436
1437 =cut
1438
1439 sub GetItemsLocationInfo {
1440         my $biblionumber = shift;
1441         my @results;
1442
1443         my $dbh = C4::Context->dbh;
1444         my $query = "SELECT a.branchname as homebranch, b.branchname as holdingbranch,
1445                             location, itemcallnumber, cn_sort
1446                      FROM items, branches as a, branches as b
1447                      WHERE homebranch = a.branchcode AND holdingbranch = b.branchcode
1448                      AND biblionumber = ?
1449                      ORDER BY cn_sort ASC";
1450         my $sth = $dbh->prepare($query);
1451         $sth->execute($biblionumber);
1452
1453         while ( my $data = $sth->fetchrow_hashref ) {
1454              $data->{location_intranet} = GetKohaAuthorisedValueLib('LOC', $data->{location});
1455              $data->{location_opac}= GetKohaAuthorisedValueLib('LOC', $data->{location}, 1);
1456              push @results, $data;
1457         }
1458         return @results;
1459 }
1460
1461 =head2 GetHostItemsInfo
1462
1463         $hostiteminfo = GetHostItemsInfo($hostfield);
1464         Returns the iteminfo for items linked to records via a host field
1465
1466 =cut
1467
1468 sub GetHostItemsInfo {
1469         my ($record) = @_;
1470         my @returnitemsInfo;
1471
1472         if (C4::Context->preference('marcflavour') eq 'MARC21' ||
1473         C4::Context->preference('marcflavour') eq 'NORMARC'){
1474             foreach my $hostfield ( $record->field('773') ) {
1475                 my $hostbiblionumber = $hostfield->subfield("0");
1476                 my $linkeditemnumber = $hostfield->subfield("9");
1477                 my @hostitemInfos = GetItemsInfo($hostbiblionumber);
1478                 foreach my $hostitemInfo (@hostitemInfos){
1479                         if ($hostitemInfo->{itemnumber} eq $linkeditemnumber){
1480                                 push (@returnitemsInfo,$hostitemInfo);
1481                                 last;
1482                         }
1483                 }
1484             }
1485         } elsif ( C4::Context->preference('marcflavour') eq 'UNIMARC'){
1486             foreach my $hostfield ( $record->field('461') ) {
1487                 my $hostbiblionumber = $hostfield->subfield("0");
1488                 my $linkeditemnumber = $hostfield->subfield("9");
1489                 my @hostitemInfos = GetItemsInfo($hostbiblionumber);
1490                 foreach my $hostitemInfo (@hostitemInfos){
1491                         if ($hostitemInfo->{itemnumber} eq $linkeditemnumber){
1492                                 push (@returnitemsInfo,$hostitemInfo);
1493                                 last;
1494                         }
1495                 }
1496             }
1497         }
1498         return @returnitemsInfo;
1499 }
1500
1501
1502 =head2 GetLastAcquisitions
1503
1504   my $lastacq = GetLastAcquisitions({'branches' => ('branch1','branch2'),
1505                                     'itemtypes' => ('BK','BD')}, 10);
1506
1507 =cut
1508
1509 sub  GetLastAcquisitions {
1510         my ($data,$max) = @_;
1511
1512         my $itemtype = C4::Context->preference('item-level_itypes') ? 'itype' : 'itemtype';
1513
1514         my $number_of_branches = @{$data->{branches}};
1515         my $number_of_itemtypes   = @{$data->{itemtypes}};
1516
1517
1518         my @where = ('WHERE 1 ');
1519         $number_of_branches and push @where
1520            , 'AND holdingbranch IN ('
1521            , join(',', ('?') x $number_of_branches )
1522            , ')'
1523          ;
1524
1525         $number_of_itemtypes and push @where
1526            , "AND $itemtype IN ("
1527            , join(',', ('?') x $number_of_itemtypes )
1528            , ')'
1529          ;
1530
1531         my $query = "SELECT biblio.biblionumber as biblionumber, title, dateaccessioned
1532                                  FROM items RIGHT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
1533                                     RIGHT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber)
1534                                     @where
1535                                     GROUP BY biblio.biblionumber
1536                                     ORDER BY dateaccessioned DESC LIMIT $max";
1537
1538         my $dbh = C4::Context->dbh;
1539         my $sth = $dbh->prepare($query);
1540
1541     $sth->execute((@{$data->{branches}}, @{$data->{itemtypes}}));
1542
1543         my @results;
1544         while( my $row = $sth->fetchrow_hashref){
1545                 push @results, {date => $row->{dateaccessioned}
1546                                                 , biblionumber => $row->{biblionumber}
1547                                                 , title => $row->{title}};
1548         }
1549
1550         return @results;
1551 }
1552
1553 =head2 GetItemnumbersForBiblio
1554
1555   my $itemnumbers = GetItemnumbersForBiblio($biblionumber);
1556
1557 Given a single biblionumber, return an arrayref of all the corresponding itemnumbers
1558
1559 =cut
1560
1561 sub GetItemnumbersForBiblio {
1562     my $biblionumber = shift;
1563     my @items;
1564     my $dbh = C4::Context->dbh;
1565     my $sth = $dbh->prepare("SELECT itemnumber FROM items WHERE biblionumber = ?");
1566     $sth->execute($biblionumber);
1567     while (my $result = $sth->fetchrow_hashref) {
1568         push @items, $result->{'itemnumber'};
1569     }
1570     return \@items;
1571 }
1572
1573 =head2 get_itemnumbers_of
1574
1575   my @itemnumbers_of = get_itemnumbers_of(@biblionumbers);
1576
1577 Given a list of biblionumbers, return the list of corresponding itemnumbers
1578 for each biblionumber.
1579
1580 Return a reference on a hash where keys are biblionumbers and values are
1581 references on array of itemnumbers.
1582
1583 =cut
1584
1585 sub get_itemnumbers_of {
1586     my @biblionumbers = @_;
1587
1588     my $dbh = C4::Context->dbh;
1589
1590     my $query = '
1591         SELECT itemnumber,
1592             biblionumber
1593         FROM items
1594         WHERE biblionumber IN (?' . ( ',?' x scalar @biblionumbers - 1 ) . ')
1595     ';
1596     my $sth = $dbh->prepare($query);
1597     $sth->execute(@biblionumbers);
1598
1599     my %itemnumbers_of;
1600
1601     while ( my ( $itemnumber, $biblionumber ) = $sth->fetchrow_array ) {
1602         push @{ $itemnumbers_of{$biblionumber} }, $itemnumber;
1603     }
1604
1605     return \%itemnumbers_of;
1606 }
1607
1608 =head2 get_hostitemnumbers_of
1609
1610   my @itemnumbers_of = get_hostitemnumbers_of($biblionumber);
1611
1612 Given a biblionumber, return the list of corresponding itemnumbers that are linked to it via host fields
1613
1614 Return a reference on a hash where key is a biblionumber and values are
1615 references on array of itemnumbers.
1616
1617 =cut
1618
1619
1620 sub get_hostitemnumbers_of {
1621         my ($biblionumber) = @_;
1622         my $marcrecord = GetMarcBiblio($biblionumber);
1623         my (@returnhostitemnumbers,$tag, $biblio_s, $item_s);
1624
1625         my $marcflavor = C4::Context->preference('marcflavour');
1626         if ($marcflavor eq 'MARC21' || $marcflavor eq 'NORMARC') {
1627         $tag='773';
1628         $biblio_s='0';
1629         $item_s='9';
1630     } elsif ($marcflavor eq 'UNIMARC') {
1631         $tag='461';
1632         $biblio_s='0';
1633         $item_s='9';
1634     }
1635
1636     foreach my $hostfield ( $marcrecord->field($tag) ) {
1637         my $hostbiblionumber = $hostfield->subfield($biblio_s);
1638         my $linkeditemnumber = $hostfield->subfield($item_s);
1639         my @itemnumbers;
1640         if (my $itemnumbers = get_itemnumbers_of($hostbiblionumber)->{$hostbiblionumber})
1641         {
1642             @itemnumbers = @$itemnumbers;
1643         }
1644         foreach my $itemnumber (@itemnumbers){
1645             if ($itemnumber eq $linkeditemnumber){
1646                 push (@returnhostitemnumbers,$itemnumber);
1647                 last;
1648             }
1649         }
1650     }
1651     return @returnhostitemnumbers;
1652 }
1653
1654
1655 =head2 GetItemnumberFromBarcode
1656
1657   $result = GetItemnumberFromBarcode($barcode);
1658
1659 =cut
1660
1661 sub GetItemnumberFromBarcode {
1662     my ($barcode) = @_;
1663     my $dbh = C4::Context->dbh;
1664
1665     my $rq =
1666       $dbh->prepare("SELECT itemnumber FROM items WHERE items.barcode=?");
1667     $rq->execute($barcode);
1668     my ($result) = $rq->fetchrow;
1669     return ($result);
1670 }
1671
1672 =head2 GetBarcodeFromItemnumber
1673
1674   $result = GetBarcodeFromItemnumber($itemnumber);
1675
1676 =cut
1677
1678 sub GetBarcodeFromItemnumber {
1679     my ($itemnumber) = @_;
1680     my $dbh = C4::Context->dbh;
1681
1682     my $rq =
1683       $dbh->prepare("SELECT barcode FROM items WHERE items.itemnumber=?");
1684     $rq->execute($itemnumber);
1685     my ($result) = $rq->fetchrow;
1686     return ($result);
1687 }
1688
1689 =head2 GetHiddenItemnumbers
1690
1691     my @itemnumbers_to_hide = GetHiddenItemnumbers(@items);
1692
1693 Given a list of items it checks which should be hidden from the OPAC given
1694 the current configuration. Returns a list of itemnumbers corresponding to
1695 those that should be hidden.
1696
1697 =cut
1698
1699 sub GetHiddenItemnumbers {
1700     my (@items) = @_;
1701     my @resultitems;
1702
1703     my $yaml = C4::Context->preference('OpacHiddenItems');
1704     return () if (! $yaml =~ /\S/ );
1705     $yaml = "$yaml\n\n"; # YAML is anal on ending \n. Surplus does not hurt
1706     my $hidingrules;
1707     eval {
1708         $hidingrules = YAML::Load($yaml);
1709     };
1710     if ($@) {
1711         warn "Unable to parse OpacHiddenItems syspref : $@";
1712         return ();
1713     }
1714     my $dbh = C4::Context->dbh;
1715
1716     # For each item
1717     foreach my $item (@items) {
1718
1719         # We check each rule
1720         foreach my $field (keys %$hidingrules) {
1721             my $val;
1722             if (exists $item->{$field}) {
1723                 $val = $item->{$field};
1724             }
1725             else {
1726                 my $query = "SELECT $field from items where itemnumber = ?";
1727                 $val = $dbh->selectrow_array($query, undef, $item->{'itemnumber'});
1728             }
1729             $val = '' unless defined $val;
1730
1731             # If the results matches the values in the yaml file
1732             if (any { $val eq $_ } @{$hidingrules->{$field}}) {
1733
1734                 # We add the itemnumber to the list
1735                 push @resultitems, $item->{'itemnumber'};
1736
1737                 # If at least one rule matched for an item, no need to test the others
1738                 last;
1739             }
1740         }
1741     }
1742     return @resultitems;
1743 }
1744
1745 =head3 get_item_authorised_values
1746
1747 find the types and values for all authorised values assigned to this item.
1748
1749 parameters: itemnumber
1750
1751 returns: a hashref malling the authorised value to the value set for this itemnumber
1752
1753     $authorised_values = {
1754              'CCODE'      => undef,
1755              'DAMAGED'    => '0',
1756              'LOC'        => '3',
1757              'LOST'       => '0'
1758              'NOT_LOAN'   => '0',
1759              'RESTRICTED' => undef,
1760              'STACK'      => undef,
1761              'WITHDRAWN'  => '0',
1762              'branches'   => 'CPL',
1763              'cn_source'  => undef,
1764              'itemtypes'  => 'SER',
1765            };
1766
1767 Notes: see C4::Biblio::get_biblio_authorised_values for a similar method at the biblio level.
1768
1769 =cut
1770
1771 sub get_item_authorised_values {
1772     my $itemnumber = shift;
1773
1774     # assume that these entries in the authorised_value table are item level.
1775     my $query = q(SELECT distinct authorised_value, kohafield
1776                     FROM marc_subfield_structure
1777                     WHERE kohafield like 'item%'
1778                       AND authorised_value != '' );
1779
1780     my $itemlevel_authorised_values = C4::Context->dbh->selectall_hashref( $query, 'authorised_value' );
1781     my $iteminfo = GetItem( $itemnumber );
1782     # warn( Data::Dumper->Dump( [ $itemlevel_authorised_values ], [ 'itemlevel_authorised_values' ] ) );
1783     my $return;
1784     foreach my $this_authorised_value ( keys %$itemlevel_authorised_values ) {
1785         my $field = $itemlevel_authorised_values->{ $this_authorised_value }->{'kohafield'};
1786         $field =~ s/^items\.//;
1787         if ( exists $iteminfo->{ $field } ) {
1788             $return->{ $this_authorised_value } = $iteminfo->{ $field };
1789         }
1790     }
1791     # warn( Data::Dumper->Dump( [ $return ], [ 'return' ] ) );
1792     return $return;
1793 }
1794
1795 =head3 get_authorised_value_images
1796
1797 find a list of icons that are appropriate for display based on the
1798 authorised values for a biblio.
1799
1800 parameters: listref of authorised values, such as comes from
1801 get_item_authorised_values or
1802 from C4::Biblio::get_biblio_authorised_values
1803
1804 returns: listref of hashrefs for each image. Each hashref looks like this:
1805
1806       { imageurl => '/intranet-tmpl/prog/img/itemtypeimg/npl/WEB.gif',
1807         label    => '',
1808         category => '',
1809         value    => '', }
1810
1811 Notes: Currently, I put on the full path to the images on the staff
1812 side. This should either be configurable or not done at all. Since I
1813 have to deal with 'intranet' or 'opac' in
1814 get_biblio_authorised_values, perhaps I should be passing it in.
1815
1816 =cut
1817
1818 sub get_authorised_value_images {
1819     my $authorised_values = shift;
1820
1821     my @imagelist;
1822
1823     my $authorised_value_list = GetAuthorisedValues();
1824     # warn ( Data::Dumper->Dump( [ $authorised_value_list ], [ 'authorised_value_list' ] ) );
1825     foreach my $this_authorised_value ( @$authorised_value_list ) {
1826         if ( exists $authorised_values->{ $this_authorised_value->{'category'} }
1827              && $authorised_values->{ $this_authorised_value->{'category'} } eq $this_authorised_value->{'authorised_value'} ) {
1828             # warn ( Data::Dumper->Dump( [ $this_authorised_value ], [ 'this_authorised_value' ] ) );
1829             if ( defined $this_authorised_value->{'imageurl'} ) {
1830                 push @imagelist, { imageurl => C4::Koha::getitemtypeimagelocation( 'intranet', $this_authorised_value->{'imageurl'} ),
1831                                    label    => $this_authorised_value->{'lib'},
1832                                    category => $this_authorised_value->{'category'},
1833                                    value    => $this_authorised_value->{'authorised_value'}, };
1834             }
1835         }
1836     }
1837
1838     # warn ( Data::Dumper->Dump( [ \@imagelist ], [ 'imagelist' ] ) );
1839     return \@imagelist;
1840
1841 }
1842
1843 =head1 LIMITED USE FUNCTIONS
1844
1845 The following functions, while part of the public API,
1846 are not exported.  This is generally because they are
1847 meant to be used by only one script for a specific
1848 purpose, and should not be used in any other context
1849 without careful thought.
1850
1851 =cut
1852
1853 =head2 GetMarcItem
1854
1855   my $item_marc = GetMarcItem($biblionumber, $itemnumber);
1856
1857 Returns MARC::Record of the item passed in parameter.
1858 This function is meant for use only in C<cataloguing/additem.pl>,
1859 where it is needed to support that script's MARC-like
1860 editor.
1861
1862 =cut
1863
1864 sub GetMarcItem {
1865     my ( $biblionumber, $itemnumber ) = @_;
1866
1867     # GetMarcItem has been revised so that it does the following:
1868     #  1. Gets the item information from the items table.
1869     #  2. Converts it to a MARC field for storage in the bib record.
1870     #
1871     # The previous behavior was:
1872     #  1. Get the bib record.
1873     #  2. Return the MARC tag corresponding to the item record.
1874     #
1875     # The difference is that one treats the items row as authoritative,
1876     # while the other treats the MARC representation as authoritative
1877     # under certain circumstances.
1878
1879     my $itemrecord = GetItem($itemnumber);
1880
1881     # Tack on 'items.' prefix to column names so that TransformKohaToMarc will work.
1882     # Also, don't emit a subfield if the underlying field is blank.
1883
1884
1885     return Item2Marc($itemrecord,$biblionumber);
1886
1887 }
1888 sub Item2Marc {
1889         my ($itemrecord,$biblionumber)=@_;
1890     my $mungeditem = {
1891         map {
1892             defined($itemrecord->{$_}) && $itemrecord->{$_} ne '' ? ("items.$_" => $itemrecord->{$_}) : ()
1893         } keys %{ $itemrecord }
1894     };
1895     my $itemmarc = TransformKohaToMarc($mungeditem);
1896     my ( $itemtag, $itemsubfield ) = GetMarcFromKohaField("items.itemnumber",GetFrameworkCode($biblionumber)||'');
1897
1898     my $unlinked_item_subfields = _parse_unlinked_item_subfields_from_xml($mungeditem->{'items.more_subfields_xml'});
1899     if (defined $unlinked_item_subfields and $#$unlinked_item_subfields > -1) {
1900                 foreach my $field ($itemmarc->field($itemtag)){
1901             $field->add_subfields(@$unlinked_item_subfields);
1902         }
1903     }
1904         return $itemmarc;
1905 }
1906
1907 =head1 PRIVATE FUNCTIONS AND VARIABLES
1908
1909 The following functions are not meant to be called
1910 directly, but are documented in order to explain
1911 the inner workings of C<C4::Items>.
1912
1913 =cut
1914
1915 =head2 %derived_columns
1916
1917 This hash keeps track of item columns that
1918 are strictly derived from other columns in
1919 the item record and are not meant to be set
1920 independently.
1921
1922 Each key in the hash should be the name of a
1923 column (as named by TransformMarcToKoha).  Each
1924 value should be hashref whose keys are the
1925 columns on which the derived column depends.  The
1926 hashref should also contain a 'BUILDER' key
1927 that is a reference to a sub that calculates
1928 the derived value.
1929
1930 =cut
1931
1932 my %derived_columns = (
1933     'items.cn_sort' => {
1934         'itemcallnumber' => 1,
1935         'items.cn_source' => 1,
1936         'BUILDER' => \&_calc_items_cn_sort,
1937     }
1938 );
1939
1940 =head2 _set_derived_columns_for_add
1941
1942   _set_derived_column_for_add($item);
1943
1944 Given an item hash representing a new item to be added,
1945 calculate any derived columns.  Currently the only
1946 such column is C<items.cn_sort>.
1947
1948 =cut
1949
1950 sub _set_derived_columns_for_add {
1951     my $item = shift;
1952
1953     foreach my $column (keys %derived_columns) {
1954         my $builder = $derived_columns{$column}->{'BUILDER'};
1955         my $source_values = {};
1956         foreach my $source_column (keys %{ $derived_columns{$column} }) {
1957             next if $source_column eq 'BUILDER';
1958             $source_values->{$source_column} = $item->{$source_column};
1959         }
1960         $builder->($item, $source_values);
1961     }
1962 }
1963
1964 =head2 _set_derived_columns_for_mod
1965
1966   _set_derived_column_for_mod($item);
1967
1968 Given an item hash representing a new item to be modified.
1969 calculate any derived columns.  Currently the only
1970 such column is C<items.cn_sort>.
1971
1972 This routine differs from C<_set_derived_columns_for_add>
1973 in that it needs to handle partial item records.  In other
1974 words, the caller of C<ModItem> may have supplied only one
1975 or two columns to be changed, so this function needs to
1976 determine whether any of the columns to be changed affect
1977 any of the derived columns.  Also, if a derived column
1978 depends on more than one column, but the caller is not
1979 changing all of then, this routine retrieves the unchanged
1980 values from the database in order to ensure a correct
1981 calculation.
1982
1983 =cut
1984
1985 sub _set_derived_columns_for_mod {
1986     my $item = shift;
1987
1988     foreach my $column (keys %derived_columns) {
1989         my $builder = $derived_columns{$column}->{'BUILDER'};
1990         my $source_values = {};
1991         my %missing_sources = ();
1992         my $must_recalc = 0;
1993         foreach my $source_column (keys %{ $derived_columns{$column} }) {
1994             next if $source_column eq 'BUILDER';
1995             if (exists $item->{$source_column}) {
1996                 $must_recalc = 1;
1997                 $source_values->{$source_column} = $item->{$source_column};
1998             } else {
1999                 $missing_sources{$source_column} = 1;
2000             }
2001         }
2002         if ($must_recalc) {
2003             foreach my $source_column (keys %missing_sources) {
2004                 $source_values->{$source_column} = _get_single_item_column($source_column, $item->{'itemnumber'});
2005             }
2006             $builder->($item, $source_values);
2007         }
2008     }
2009 }
2010
2011 =head2 _do_column_fixes_for_mod
2012
2013   _do_column_fixes_for_mod($item);
2014
2015 Given an item hashref containing one or more
2016 columns to modify, fix up certain values.
2017 Specifically, set to 0 any passed value
2018 of C<notforloan>, C<damaged>, C<itemlost>, or
2019 C<withdrawn> that is either undefined or
2020 contains the empty string.
2021
2022 =cut
2023
2024 sub _do_column_fixes_for_mod {
2025     my $item = shift;
2026
2027     if (exists $item->{'notforloan'} and
2028         (not defined $item->{'notforloan'} or $item->{'notforloan'} eq '')) {
2029         $item->{'notforloan'} = 0;
2030     }
2031     if (exists $item->{'damaged'} and
2032         (not defined $item->{'damaged'} or $item->{'damaged'} eq '')) {
2033         $item->{'damaged'} = 0;
2034     }
2035     if (exists $item->{'itemlost'} and
2036         (not defined $item->{'itemlost'} or $item->{'itemlost'} eq '')) {
2037         $item->{'itemlost'} = 0;
2038     }
2039     if (exists $item->{'withdrawn'} and
2040         (not defined $item->{'withdrawn'} or $item->{'withdrawn'} eq '')) {
2041         $item->{'withdrawn'} = 0;
2042     }
2043     if (exists $item->{'location'} && !$item->{'permanent_location'}) {
2044         $item->{'permanent_location'} = $item->{'location'};
2045     }
2046     if (exists $item->{'timestamp'}) {
2047         delete $item->{'timestamp'};
2048     }
2049 }
2050
2051 =head2 _get_single_item_column
2052
2053   _get_single_item_column($column, $itemnumber);
2054
2055 Retrieves the value of a single column from an C<items>
2056 row specified by C<$itemnumber>.
2057
2058 =cut
2059
2060 sub _get_single_item_column {
2061     my $column = shift;
2062     my $itemnumber = shift;
2063
2064     my $dbh = C4::Context->dbh;
2065     my $sth = $dbh->prepare("SELECT $column FROM items WHERE itemnumber = ?");
2066     $sth->execute($itemnumber);
2067     my ($value) = $sth->fetchrow();
2068     return $value;
2069 }
2070
2071 =head2 _calc_items_cn_sort
2072
2073   _calc_items_cn_sort($item, $source_values);
2074
2075 Helper routine to calculate C<items.cn_sort>.
2076
2077 =cut
2078
2079 sub _calc_items_cn_sort {
2080     my $item = shift;
2081     my $source_values = shift;
2082
2083     $item->{'items.cn_sort'} = GetClassSort($source_values->{'items.cn_source'}, $source_values->{'itemcallnumber'}, "");
2084 }
2085
2086 =head2 _set_defaults_for_add
2087
2088   _set_defaults_for_add($item_hash);
2089
2090 Given an item hash representing an item to be added, set
2091 correct default values for columns whose default value
2092 is not handled by the DBMS.  This includes the following
2093 columns:
2094
2095 =over 2
2096
2097 =item *
2098
2099 C<items.dateaccessioned>
2100
2101 =item *
2102
2103 C<items.notforloan>
2104
2105 =item *
2106
2107 C<items.damaged>
2108
2109 =item *
2110
2111 C<items.itemlost>
2112
2113 =item *
2114
2115 C<items.withdrawn>
2116
2117 =back
2118
2119 =cut
2120
2121 sub _set_defaults_for_add {
2122     my $item = shift;
2123     $item->{dateaccessioned} ||= C4::Dates->new->output('iso');
2124     $item->{$_} ||= 0 for (qw( notforloan damaged itemlost withdrawn));
2125 }
2126
2127 =head2 _koha_new_item
2128
2129   my ($itemnumber,$error) = _koha_new_item( $item, $barcode );
2130
2131 Perform the actual insert into the C<items> table.
2132
2133 =cut
2134
2135 sub _koha_new_item {
2136     my ( $item, $barcode ) = @_;
2137     my $dbh=C4::Context->dbh;
2138     my $error;
2139     my $query =
2140            "INSERT INTO items SET
2141             biblionumber        = ?,
2142             biblioitemnumber    = ?,
2143             barcode             = ?,
2144             dateaccessioned     = ?,
2145             booksellerid        = ?,
2146             homebranch          = ?,
2147             price               = ?,
2148             replacementprice    = ?,
2149             replacementpricedate = ?,
2150             datelastborrowed    = ?,
2151             datelastseen        = ?,
2152             stack               = ?,
2153             notforloan          = ?,
2154             damaged             = ?,
2155             itemlost            = ?,
2156             withdrawn            = ?,
2157             itemcallnumber      = ?,
2158             coded_location_qualifier = ?,
2159             restricted          = ?,
2160             itemnotes           = ?,
2161             holdingbranch       = ?,
2162             paidfor             = ?,
2163             location            = ?,
2164             permanent_location            = ?,
2165             onloan              = ?,
2166             issues              = ?,
2167             renewals            = ?,
2168             reserves            = ?,
2169             cn_source           = ?,
2170             cn_sort             = ?,
2171             ccode               = ?,
2172             itype               = ?,
2173             materials           = ?,
2174             uri = ?,
2175             enumchron           = ?,
2176             more_subfields_xml  = ?,
2177             copynumber          = ?,
2178             stocknumber         = ?
2179           ";
2180     my $sth = $dbh->prepare($query);
2181     my $today = C4::Dates->today('iso');
2182    $sth->execute(
2183             $item->{'biblionumber'},
2184             $item->{'biblioitemnumber'},
2185             $barcode,
2186             $item->{'dateaccessioned'},
2187             $item->{'booksellerid'},
2188             $item->{'homebranch'},
2189             $item->{'price'},
2190             $item->{'replacementprice'},
2191             $item->{'replacementpricedate'} || $today,
2192             $item->{datelastborrowed},
2193             $item->{datelastseen} || $today,
2194             $item->{stack},
2195             $item->{'notforloan'},
2196             $item->{'damaged'},
2197             $item->{'itemlost'},
2198             $item->{'withdrawn'},
2199             $item->{'itemcallnumber'},
2200             $item->{'coded_location_qualifier'},
2201             $item->{'restricted'},
2202             $item->{'itemnotes'},
2203             $item->{'holdingbranch'},
2204             $item->{'paidfor'},
2205             $item->{'location'},
2206             $item->{'permanent_location'},
2207             $item->{'onloan'},
2208             $item->{'issues'},
2209             $item->{'renewals'},
2210             $item->{'reserves'},
2211             $item->{'items.cn_source'},
2212             $item->{'items.cn_sort'},
2213             $item->{'ccode'},
2214             $item->{'itype'},
2215             $item->{'materials'},
2216             $item->{'uri'},
2217             $item->{'enumchron'},
2218             $item->{'more_subfields_xml'},
2219             $item->{'copynumber'},
2220             $item->{'stocknumber'},
2221     );
2222
2223     my $itemnumber;
2224     if ( defined $sth->errstr ) {
2225         $error.="ERROR in _koha_new_item $query".$sth->errstr;
2226     }
2227     else {
2228         $itemnumber = $dbh->{'mysql_insertid'};
2229     }
2230
2231     return ( $itemnumber, $error );
2232 }
2233
2234 =head2 MoveItemFromBiblio
2235
2236   MoveItemFromBiblio($itenumber, $frombiblio, $tobiblio);
2237
2238 Moves an item from a biblio to another
2239
2240 Returns undef if the move failed or the biblionumber of the destination record otherwise
2241
2242 =cut
2243
2244 sub MoveItemFromBiblio {
2245     my ($itemnumber, $frombiblio, $tobiblio) = @_;
2246     my $dbh = C4::Context->dbh;
2247     my $sth = $dbh->prepare("SELECT biblioitemnumber FROM biblioitems WHERE biblionumber = ?");
2248     $sth->execute( $tobiblio );
2249     my ( $tobiblioitem ) = $sth->fetchrow();
2250     $sth = $dbh->prepare("UPDATE items SET biblioitemnumber = ?, biblionumber = ? WHERE itemnumber = ? AND biblionumber = ?");
2251     my $return = $sth->execute($tobiblioitem, $tobiblio, $itemnumber, $frombiblio);
2252     if ($return == 1) {
2253         ModZebra( $tobiblio, "specialUpdate", "biblioserver" );
2254         ModZebra( $frombiblio, "specialUpdate", "biblioserver" );
2255             # Checking if the item we want to move is in an order
2256         require C4::Acquisition;
2257         my $order = C4::Acquisition::GetOrderFromItemnumber($itemnumber);
2258             if ($order) {
2259                     # Replacing the biblionumber within the order if necessary
2260                     $order->{'biblionumber'} = $tobiblio;
2261                 C4::Acquisition::ModOrder($order);
2262             }
2263         return $tobiblio;
2264         }
2265     return;
2266 }
2267
2268 =head2 DelItemCheck
2269
2270    DelItemCheck($dbh, $biblionumber, $itemnumber);
2271
2272 Exported function (core API) for deleting an item record in Koha if there no current issue.
2273
2274 =cut
2275
2276 sub DelItemCheck {
2277     my ( $dbh, $biblionumber, $itemnumber ) = @_;
2278     my $error;
2279
2280         my $countanalytics=GetAnalyticsCount($itemnumber);
2281
2282
2283     # check that there is no issue on this item before deletion.
2284     my $sth = $dbh->prepare(q{
2285         SELECT COUNT(*) FROM issues
2286         WHERE itemnumber = ?
2287     });
2288     $sth->execute($itemnumber);
2289     my ($onloan) = $sth->fetchrow;
2290
2291     my $item = GetItem($itemnumber);
2292
2293     if ($onloan){
2294         $error = "book_on_loan"
2295     }
2296     elsif ( !C4::Context->IsSuperLibrarian()
2297         and C4::Context->preference("IndependentBranches")
2298         and ( C4::Context->userenv->{branch} ne $item->{'homebranch'} ) )
2299     {
2300         $error = "not_same_branch";
2301     }
2302         else{
2303         # check it doesnt have a waiting reserve
2304         $sth = $dbh->prepare(q{
2305             SELECT COUNT(*) FROM reserves
2306             WHERE (found = 'W' OR found = 'T')
2307             AND itemnumber = ?
2308         });
2309         $sth->execute($itemnumber);
2310         my ($reserve) = $sth->fetchrow;
2311         if ($reserve){
2312             $error = "book_reserved";
2313         } elsif ($countanalytics > 0){
2314                 $error = "linked_analytics";
2315         } else {
2316             DelItem(
2317                 {
2318                     biblionumber => $biblionumber,
2319                     itemnumber   => $itemnumber
2320                 }
2321             );
2322             return 1;
2323         }
2324     }
2325     return $error;
2326 }
2327
2328 =head2 _koha_modify_item
2329
2330   my ($itemnumber,$error) =_koha_modify_item( $item );
2331
2332 Perform the actual update of the C<items> row.  Note that this
2333 routine accepts a hashref specifying the columns to update.
2334
2335 =cut
2336
2337 sub _koha_modify_item {
2338     my ( $item ) = @_;
2339     my $dbh=C4::Context->dbh;
2340     my $error;
2341
2342     my $query = "UPDATE items SET ";
2343     my @bind;
2344     for my $key ( keys %$item ) {
2345         next if ( $key eq 'itemnumber' );
2346         $query.="$key=?,";
2347         push @bind, $item->{$key};
2348     }
2349     $query =~ s/,$//;
2350     $query .= " WHERE itemnumber=?";
2351     push @bind, $item->{'itemnumber'};
2352     my $sth = $dbh->prepare($query);
2353     $sth->execute(@bind);
2354     if ( $sth->err ) {
2355         $error.="ERROR in _koha_modify_item $query: ".$sth->errstr;
2356         warn $error;
2357     }
2358     return ($item->{'itemnumber'},$error);
2359 }
2360
2361 =head2 _koha_delete_item
2362
2363   _koha_delete_item( $itemnum );
2364
2365 Internal function to delete an item record from the koha tables
2366
2367 =cut
2368
2369 sub _koha_delete_item {
2370     my ( $itemnum ) = @_;
2371
2372     my $dbh = C4::Context->dbh;
2373     # save the deleted item to deleteditems table
2374     my $sth = $dbh->prepare("SELECT * FROM items WHERE itemnumber=?");
2375     $sth->execute($itemnum);
2376     my $data = $sth->fetchrow_hashref();
2377
2378     # There is no item to delete
2379     return 0 unless $data;
2380
2381     my $query = "INSERT INTO deleteditems SET ";
2382     my @bind  = ();
2383     foreach my $key ( keys %$data ) {
2384         next if ( $key eq 'timestamp' ); # timestamp will be set by db
2385         $query .= "$key = ?,";
2386         push( @bind, $data->{$key} );
2387     }
2388     $query =~ s/\,$//;
2389     $sth = $dbh->prepare($query);
2390     $sth->execute(@bind);
2391
2392     # delete from items table
2393     $sth = $dbh->prepare("DELETE FROM items WHERE itemnumber=?");
2394     my $deleted = $sth->execute($itemnum);
2395     return ( $deleted == 1 ) ? 1 : 0;
2396 }
2397
2398 =head2 _marc_from_item_hash
2399
2400   my $item_marc = _marc_from_item_hash($item, $frameworkcode[, $unlinked_item_subfields]);
2401
2402 Given an item hash representing a complete item record,
2403 create a C<MARC::Record> object containing an embedded
2404 tag representing that item.
2405
2406 The third, optional parameter C<$unlinked_item_subfields> is
2407 an arrayref of subfields (not mapped to C<items> fields per the
2408 framework) to be added to the MARC representation
2409 of the item.
2410
2411 =cut
2412
2413 sub _marc_from_item_hash {
2414     my $item = shift;
2415     my $frameworkcode = shift;
2416     my $unlinked_item_subfields;
2417     if (@_) {
2418         $unlinked_item_subfields = shift;
2419     }
2420
2421     # Tack on 'items.' prefix to column names so lookup from MARC frameworks will work
2422     # Also, don't emit a subfield if the underlying field is blank.
2423     my $mungeditem = { map {  (defined($item->{$_}) and $item->{$_} ne '') ?
2424                                 (/^items\./ ? ($_ => $item->{$_}) : ("items.$_" => $item->{$_}))
2425                                 : ()  } keys %{ $item } };
2426
2427     my $item_marc = MARC::Record->new();
2428     foreach my $item_field ( keys %{$mungeditem} ) {
2429         my ( $tag, $subfield ) = GetMarcFromKohaField( $item_field, $frameworkcode );
2430         next unless defined $tag and defined $subfield;    # skip if not mapped to MARC field
2431         my @values = split(/\s?\|\s?/, $mungeditem->{$item_field}, -1);
2432         foreach my $value (@values){
2433             if ( my $field = $item_marc->field($tag) ) {
2434                     $field->add_subfields( $subfield => $value );
2435             } else {
2436                 my $add_subfields = [];
2437                 if (defined $unlinked_item_subfields and ref($unlinked_item_subfields) eq 'ARRAY' and $#$unlinked_item_subfields > -1) {
2438                     $add_subfields = $unlinked_item_subfields;
2439             }
2440             $item_marc->add_fields( $tag, " ", " ", $subfield => $value, @$add_subfields );
2441             }
2442         }
2443     }
2444
2445     return $item_marc;
2446 }
2447
2448 =head2 _repack_item_errors
2449
2450 Add an error message hash generated by C<CheckItemPreSave>
2451 to a list of errors.
2452
2453 =cut
2454
2455 sub _repack_item_errors {
2456     my $item_sequence_num = shift;
2457     my $item_ref = shift;
2458     my $error_ref = shift;
2459
2460     my @repacked_errors = ();
2461
2462     foreach my $error_code (sort keys %{ $error_ref }) {
2463         my $repacked_error = {};
2464         $repacked_error->{'item_sequence'} = $item_sequence_num;
2465         $repacked_error->{'item_barcode'} = exists($item_ref->{'barcode'}) ? $item_ref->{'barcode'} : '';
2466         $repacked_error->{'error_code'} = $error_code;
2467         $repacked_error->{'error_information'} = $error_ref->{$error_code};
2468         push @repacked_errors, $repacked_error;
2469     }
2470
2471     return @repacked_errors;
2472 }
2473
2474 =head2 _get_unlinked_item_subfields
2475
2476   my $unlinked_item_subfields = _get_unlinked_item_subfields($original_item_marc, $frameworkcode);
2477
2478 =cut
2479
2480 sub _get_unlinked_item_subfields {
2481     my $original_item_marc = shift;
2482     my $frameworkcode = shift;
2483
2484     my $marcstructure = GetMarcStructure(1, $frameworkcode);
2485
2486     # assume that this record has only one field, and that that
2487     # field contains only the item information
2488     my $subfields = [];
2489     my @fields = $original_item_marc->fields();
2490     if ($#fields > -1) {
2491         my $field = $fields[0];
2492             my $tag = $field->tag();
2493         foreach my $subfield ($field->subfields()) {
2494             if (defined $subfield->[1] and
2495                 $subfield->[1] ne '' and
2496                 !$marcstructure->{$tag}->{$subfield->[0]}->{'kohafield'}) {
2497                 push @$subfields, $subfield->[0] => $subfield->[1];
2498             }
2499         }
2500     }
2501     return $subfields;
2502 }
2503
2504 =head2 _get_unlinked_subfields_xml
2505
2506   my $unlinked_subfields_xml = _get_unlinked_subfields_xml($unlinked_item_subfields);
2507
2508 =cut
2509
2510 sub _get_unlinked_subfields_xml {
2511     my $unlinked_item_subfields = shift;
2512
2513     my $xml;
2514     if (defined $unlinked_item_subfields and ref($unlinked_item_subfields) eq 'ARRAY' and $#$unlinked_item_subfields > -1) {
2515         my $marc = MARC::Record->new();
2516         # use of tag 999 is arbitrary, and doesn't need to match the item tag
2517         # used in the framework
2518         $marc->append_fields(MARC::Field->new('999', ' ', ' ', @$unlinked_item_subfields));
2519         $marc->encoding("UTF-8");
2520         $xml = $marc->as_xml("USMARC");
2521     }
2522
2523     return $xml;
2524 }
2525
2526 =head2 _parse_unlinked_item_subfields_from_xml
2527
2528   my $unlinked_item_subfields = _parse_unlinked_item_subfields_from_xml($whole_item->{'more_subfields_xml'}):
2529
2530 =cut
2531
2532 sub  _parse_unlinked_item_subfields_from_xml {
2533     my $xml = shift;
2534     require C4::Charset;
2535     return unless defined $xml and $xml ne "";
2536     my $marc = MARC::Record->new_from_xml(C4::Charset::StripNonXmlChars($xml),'UTF-8');
2537     my $unlinked_subfields = [];
2538     my @fields = $marc->fields();
2539     if ($#fields > -1) {
2540         foreach my $subfield ($fields[0]->subfields()) {
2541             push @$unlinked_subfields, $subfield->[0] => $subfield->[1];
2542         }
2543     }
2544     return $unlinked_subfields;
2545 }
2546
2547 =head2 GetAnalyticsCount
2548
2549   $count= &GetAnalyticsCount($itemnumber)
2550
2551 counts Usage of itemnumber in Analytical bibliorecords.
2552
2553 =cut
2554
2555 sub GetAnalyticsCount {
2556     my ($itemnumber) = @_;
2557     require C4::Search;
2558
2559     ### ZOOM search here
2560     my $query;
2561     $query= "hi=".$itemnumber;
2562             my ($err,$res,$result) = C4::Search::SimpleSearch($query,0,10);
2563     return ($result);
2564 }
2565
2566 =head2 GetItemHolds
2567
2568 =over 4
2569 $holds = &GetItemHolds($biblionumber, $itemnumber);
2570
2571 =back
2572
2573 This function return the count of holds with $biblionumber and $itemnumber
2574
2575 =cut
2576
2577 sub GetItemHolds {
2578     my ($biblionumber, $itemnumber) = @_;
2579     my $holds;
2580     my $dbh            = C4::Context->dbh;
2581     my $query          = "SELECT count(*)
2582         FROM  reserves
2583         WHERE biblionumber=? AND itemnumber=?";
2584     my $sth = $dbh->prepare($query);
2585     $sth->execute($biblionumber, $itemnumber);
2586     $holds = $sth->fetchrow;
2587     return $holds;
2588 }
2589
2590 # Return the list of the column names of items table
2591 sub _get_items_columns {
2592     my $dbh = C4::Context->dbh;
2593     my $sth = $dbh->column_info(undef, undef, 'items', '%');
2594     $sth->execute;
2595     my $results = $sth->fetchall_hashref('COLUMN_NAME');
2596     return keys %$results;
2597 }
2598
2599 =head2 SearchItems
2600
2601     my $items = SearchItems($field, $value);
2602
2603 SearchItems will search for items on a specific given field.
2604 For instance you can search all items with a specific stocknumber like this:
2605
2606     my $items = SearchItems('stocknumber', $stocknumber);
2607
2608 =cut
2609
2610 sub SearchItems {
2611     my ($field, $value) = @_;
2612
2613     my $dbh = C4::Context->dbh;
2614     my @columns = _get_items_columns;
2615     my $results = [];
2616     if(0 < grep /^$field$/, @columns) {
2617         my $query = "SELECT $field FROM items WHERE $field = ?";
2618         my $sth = $dbh->prepare( $query );
2619         $sth->execute( $value );
2620         $results = $sth->fetchall_arrayref({});
2621     }
2622     return $results;
2623 }
2624
2625
2626 =head1  OTHER FUNCTIONS
2627
2628 =head2 _find_value
2629
2630   ($indicators, $value) = _find_value($tag, $subfield, $record,$encoding);
2631
2632 Find the given $subfield in the given $tag in the given
2633 MARC::Record $record.  If the subfield is found, returns
2634 the (indicators, value) pair; otherwise, (undef, undef) is
2635 returned.
2636
2637 PROPOSITION :
2638 Such a function is used in addbiblio AND additem and serial-edit and maybe could be used in Authorities.
2639 I suggest we export it from this module.
2640
2641 =cut
2642
2643 sub _find_value {
2644     my ( $tagfield, $insubfield, $record, $encoding ) = @_;
2645     my @result;
2646     my $indicator;
2647     if ( $tagfield < 10 ) {
2648         if ( $record->field($tagfield) ) {
2649             push @result, $record->field($tagfield)->data();
2650         } else {
2651             push @result, "";
2652         }
2653     } else {
2654         foreach my $field ( $record->field($tagfield) ) {
2655             my @subfields = $field->subfields();
2656             foreach my $subfield (@subfields) {
2657                 if ( @$subfield[0] eq $insubfield ) {
2658                     push @result, @$subfield[1];
2659                     $indicator = $field->indicator(1) . $field->indicator(2);
2660                 }
2661             }
2662         }
2663     }
2664     return ( $indicator, @result );
2665 }
2666
2667
2668 =head2 PrepareItemrecordDisplay
2669
2670   PrepareItemrecordDisplay($itemrecord,$bibnum,$itemumber,$frameworkcode);
2671
2672 Returns a hash with all the fields for Display a given item data in a template
2673
2674 The $frameworkcode returns the item for the given frameworkcode, ONLY if bibnum is not provided
2675
2676 =cut
2677
2678 sub PrepareItemrecordDisplay {
2679
2680     my ( $bibnum, $itemnum, $defaultvalues, $frameworkcode ) = @_;
2681
2682     my $dbh = C4::Context->dbh;
2683     $frameworkcode = &GetFrameworkCode($bibnum) if $bibnum;
2684     my ( $itemtagfield, $itemtagsubfield ) = &GetMarcFromKohaField( "items.itemnumber", $frameworkcode );
2685     my $tagslib = &GetMarcStructure( 1, $frameworkcode );
2686
2687     # return nothing if we don't have found an existing framework.
2688     return q{} unless $tagslib;
2689     my $itemrecord;
2690     if ($itemnum) {
2691         $itemrecord = C4::Items::GetMarcItem( $bibnum, $itemnum );
2692     }
2693     my @loop_data;
2694
2695     my $branch_limit = C4::Context->userenv ? C4::Context->userenv->{"branch"} : "";
2696     my $query = qq{
2697         SELECT authorised_value,lib FROM authorised_values
2698     };
2699     $query .= qq{
2700         LEFT JOIN authorised_values_branches ON ( id = av_id )
2701     } if $branch_limit;
2702     $query .= qq{
2703         WHERE category = ?
2704     };
2705     $query .= qq{ AND ( branchcode = ? OR branchcode IS NULL )} if $branch_limit;
2706     $query .= qq{ ORDER BY lib};
2707     my $authorised_values_sth = $dbh->prepare( $query );
2708     foreach my $tag ( sort keys %{$tagslib} ) {
2709         my $previous_tag = '';
2710         if ( $tag ne '' ) {
2711
2712             # loop through each subfield
2713             my $cntsubf;
2714             foreach my $subfield ( sort keys %{ $tagslib->{$tag} } ) {
2715                 next if ( subfield_is_koha_internal_p($subfield) );
2716                 next if ( $tagslib->{$tag}->{$subfield}->{'tab'} ne "10" );
2717                 my %subfield_data;
2718                 $subfield_data{tag}           = $tag;
2719                 $subfield_data{subfield}      = $subfield;
2720                 $subfield_data{countsubfield} = $cntsubf++;
2721                 $subfield_data{kohafield}     = $tagslib->{$tag}->{$subfield}->{'kohafield'};
2722                 $subfield_data{id}            = "tag_".$tag."_subfield_".$subfield."_".int(rand(1000000));
2723
2724                 #        $subfield_data{marc_lib}=$tagslib->{$tag}->{$subfield}->{lib};
2725                 $subfield_data{marc_lib}   = $tagslib->{$tag}->{$subfield}->{lib};
2726                 $subfield_data{mandatory}  = $tagslib->{$tag}->{$subfield}->{mandatory};
2727                 $subfield_data{repeatable} = $tagslib->{$tag}->{$subfield}->{repeatable};
2728                 $subfield_data{hidden}     = "display:none"
2729                   if ( ( $tagslib->{$tag}->{$subfield}->{hidden} > 4 )
2730                     || ( $tagslib->{$tag}->{$subfield}->{hidden} < -4 ) );
2731                 my ( $x, $defaultvalue );
2732                 if ($itemrecord) {
2733                     ( $x, $defaultvalue ) = _find_value( $tag, $subfield, $itemrecord );
2734                 }
2735                 $defaultvalue = $tagslib->{$tag}->{$subfield}->{defaultvalue} unless $defaultvalue;
2736                 if ( !defined $defaultvalue ) {
2737                     $defaultvalue = q||;
2738                 } else {
2739                     $defaultvalue =~ s/"/&quot;/g;
2740                 }
2741
2742                 # search for itemcallnumber if applicable
2743                 if ( $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.itemcallnumber'
2744                     && C4::Context->preference('itemcallnumber') ) {
2745                     my $CNtag      = substr( C4::Context->preference('itemcallnumber'), 0, 3 );
2746                     my $CNsubfield = substr( C4::Context->preference('itemcallnumber'), 3, 1 );
2747                     if ( $itemrecord and my $field = $itemrecord->field($CNtag) ) {
2748                         $defaultvalue = $field->subfield($CNsubfield);
2749                     }
2750                 }
2751                 if (   $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.itemcallnumber'
2752                     && $defaultvalues
2753                     && $defaultvalues->{'callnumber'} ) {
2754                     if( $itemrecord and $defaultvalues and not $itemrecord->field($subfield) ){
2755                         # if the item record exists, only use default value if the item has no callnumber
2756                         $defaultvalue = $defaultvalues->{callnumber};
2757                     } elsif ( !$itemrecord and $defaultvalues ) {
2758                         # if the item record *doesn't* exists, always use the default value
2759                         $defaultvalue = $defaultvalues->{callnumber};
2760                     }
2761                 }
2762                 if (   ( $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.holdingbranch' || $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.homebranch' )
2763                     && $defaultvalues
2764                     && $defaultvalues->{'branchcode'} ) {
2765                     if ( $itemrecord and $defaultvalues and not $itemrecord->field($subfield) ) {
2766                         $defaultvalue = $defaultvalues->{branchcode};
2767                     }
2768                 }
2769                 if (   ( $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.location' )
2770                     && $defaultvalues
2771                     && $defaultvalues->{'location'} ) {
2772
2773                     if ( $itemrecord and $defaultvalues and not $itemrecord->field($subfield) ) {
2774                         # if the item record exists, only use default value if the item has no locationr
2775                         $defaultvalue = $defaultvalues->{location};
2776                     } elsif ( !$itemrecord and $defaultvalues ) {
2777                         # if the item record *doesn't* exists, always use the default value
2778                         $defaultvalue = $defaultvalues->{location};
2779                     }
2780                 }
2781                 if ( $tagslib->{$tag}->{$subfield}->{authorised_value} ) {
2782                     my @authorised_values;
2783                     my %authorised_lib;
2784
2785                     # builds list, depending on authorised value...
2786                     #---- branch
2787                     if ( $tagslib->{$tag}->{$subfield}->{'authorised_value'} eq "branches" ) {
2788                         if (   ( C4::Context->preference("IndependentBranches") )
2789                             && !C4::Context->IsSuperLibrarian() ) {
2790                             my $sth = $dbh->prepare( "SELECT branchcode,branchname FROM branches WHERE branchcode = ? ORDER BY branchname" );
2791                             $sth->execute( C4::Context->userenv->{branch} );
2792                             push @authorised_values, ""
2793                               unless ( $tagslib->{$tag}->{$subfield}->{mandatory} );
2794                             while ( my ( $branchcode, $branchname ) = $sth->fetchrow_array ) {
2795                                 push @authorised_values, $branchcode;
2796                                 $authorised_lib{$branchcode} = $branchname;
2797                             }
2798                         } else {
2799                             my $sth = $dbh->prepare( "SELECT branchcode,branchname FROM branches ORDER BY branchname" );
2800                             $sth->execute;
2801                             push @authorised_values, ""
2802                               unless ( $tagslib->{$tag}->{$subfield}->{mandatory} );
2803                             while ( my ( $branchcode, $branchname ) = $sth->fetchrow_array ) {
2804                                 push @authorised_values, $branchcode;
2805                                 $authorised_lib{$branchcode} = $branchname;
2806                             }
2807                         }
2808
2809                         $defaultvalue = C4::Context->userenv ? C4::Context->userenv->{branch} : undef;
2810                         if ( $defaultvalues and $defaultvalues->{branchcode} ) {
2811                             $defaultvalue = $defaultvalues->{branchcode};
2812                         }
2813
2814                         #----- itemtypes
2815                     } elsif ( $tagslib->{$tag}->{$subfield}->{authorised_value} eq "itemtypes" ) {
2816                         my $sth = $dbh->prepare( "SELECT itemtype,description FROM itemtypes ORDER BY description" );
2817                         $sth->execute;
2818                         push @authorised_values, ""
2819                           unless ( $tagslib->{$tag}->{$subfield}->{mandatory} );
2820                         while ( my ( $itemtype, $description ) = $sth->fetchrow_array ) {
2821                             push @authorised_values, $itemtype;
2822                             $authorised_lib{$itemtype} = $description;
2823                         }
2824                         #---- class_sources
2825                     } elsif ( $tagslib->{$tag}->{$subfield}->{authorised_value} eq "cn_source" ) {
2826                         push @authorised_values, "" unless ( $tagslib->{$tag}->{$subfield}->{mandatory} );
2827
2828                         my $class_sources = GetClassSources();
2829                         my $default_source = C4::Context->preference("DefaultClassificationSource");
2830
2831                         foreach my $class_source (sort keys %$class_sources) {
2832                             next unless $class_sources->{$class_source}->{'used'} or
2833                                         ($class_source eq $default_source);
2834                             push @authorised_values, $class_source;
2835                             $authorised_lib{$class_source} = $class_sources->{$class_source}->{'description'};
2836                         }
2837
2838                         $defaultvalue = $default_source;
2839
2840                         #---- "true" authorised value
2841                     } else {
2842                         $authorised_values_sth->execute(
2843                             $tagslib->{$tag}->{$subfield}->{authorised_value},
2844                             $branch_limit ? $branch_limit : ()
2845                         );
2846                         push @authorised_values, ""
2847                           unless ( $tagslib->{$tag}->{$subfield}->{mandatory} );
2848                         while ( my ( $value, $lib ) = $authorised_values_sth->fetchrow_array ) {
2849                             push @authorised_values, $value;
2850                             $authorised_lib{$value} = $lib;
2851                         }
2852                     }
2853                     $subfield_data{marc_value} = {
2854                         type    => 'select',
2855                         values  => \@authorised_values,
2856                         default => "$defaultvalue",
2857                         labels  => \%authorised_lib,
2858                     };
2859                 } elsif ( $tagslib->{$tag}->{$subfield}->{value_builder} ) {
2860                         # opening plugin
2861                         my $plugin = C4::Context->intranetdir . "/cataloguing/value_builder/" . $tagslib->{$tag}->{$subfield}->{'value_builder'};
2862                         if (do $plugin) {
2863                             my $extended_param = plugin_parameters( $dbh, undef, $tagslib, $subfield_data{id}, undef );
2864                             my ( $function_name, $javascript ) = plugin_javascript( $dbh, undef, $tagslib, $subfield_data{id}, undef );
2865                             $subfield_data{random}     = int(rand(1000000));    # why do we need 2 different randoms?
2866                             $subfield_data{marc_value} = qq[<input type="text" tabindex="1" id="$subfield_data{id}" name="field_value" class="input_marceditor" size="50" maxlength="255"
2867                                 onfocus="Focus$function_name($subfield_data{random}, '$subfield_data{id}');"
2868                                  onblur=" Blur$function_name($subfield_data{random}, '$subfield_data{id}');" />
2869                                 <a href="#" class="buttonDot" onclick="Clic$function_name('$subfield_data{id}'); return false;" title="Tag Editor">...</a>
2870                                 $javascript];
2871                         } else {
2872                             warn "Plugin Failed: $plugin";
2873                             $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
2874                         }
2875                 }
2876                 elsif ( $tag eq '' ) {       # it's an hidden field
2877                     $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" />);
2878                 }
2879                 elsif ( $tagslib->{$tag}->{$subfield}->{'hidden'} ) {   # FIXME: shouldn't input type be "hidden" ?
2880                     $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" />);
2881                 }
2882                 elsif ( length($defaultvalue) > 100
2883                             or (C4::Context->preference("marcflavour") eq "UNIMARC" and
2884                                   300 <= $tag && $tag < 400 && $subfield eq 'a' )
2885                             or (C4::Context->preference("marcflavour") eq "MARC21"  and
2886                                   500 <= $tag && $tag < 600                     )
2887                           ) {
2888                     # oversize field (textarea)
2889                     $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");
2890                 } else {
2891                     $subfield_data{marc_value} = "<input type=\"text\" name=\"field_value\" value=\"$defaultvalue\" size=\"50\" maxlength=\"255\" />";
2892                 }
2893                 push( @loop_data, \%subfield_data );
2894             }
2895         }
2896     }
2897     my $itemnumber;
2898     if ( $itemrecord && $itemrecord->field($itemtagfield) ) {
2899         $itemnumber = $itemrecord->subfield( $itemtagfield, $itemtagsubfield );
2900     }
2901     return {
2902         'itemtagfield'    => $itemtagfield,
2903         'itemtagsubfield' => $itemtagsubfield,
2904         'itemnumber'      => $itemnumber,
2905         'iteminformation' => \@loop_data
2906     };
2907 }
2908
2909 1;