C4/Items.pm

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