C4/Items.pm

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