C4/Items.pm

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