C4/Items.pm

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