C4/Items.pm

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