C4/Items.pm

   1 package C4::Items;
   2
   3 # Copyright 2007 LibLime, Inc.
   4 #
   5 # This file is part of Koha.
   6 #
   7 # Koha is free software; you can redistribute it and/or modify it under the
   8 # terms of the GNU General Public License as published by the Free Software
   9 # Foundation; either version 2 of the License, or (at your option) any later
  10 # version.
  11 #
  12 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
  13 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
  14 # A PARTICULAR PURPOSE.  See the GNU General Public License for more details.
  15 #
  16 # You should have received a copy of the GNU General Public License along
  17 # with Koha; if not, write to the Free Software Foundation, Inc.,
  18 # 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
  19
  20 use strict;
  21 #use warnings; FIXME - Bug 2505
  22
  23 use Carp;
  24 use C4::Context;
  25 use C4::Koha;
  26 use C4::Biblio;
  27 use C4::Dates qw/format_date format_date_in_iso/;
  28 use MARC::Record;
  29 use C4::ClassSource;
  30 use C4::Log;
  31 use C4::Branch;
  32 require C4::Reserves;
  33 use C4::Charset;
  34 use C4::Acquisition;
  35
  36 use vars qw($VERSION @ISA @EXPORT);
  37
  38 BEGIN {
  39     $VERSION = 3.01;
  40
  41         require Exporter;
  42     @ISA = qw( Exporter );
  43
  44     # function exports
  45     @EXPORT = qw(
  46         GetItem
  47         AddItemFromMarc
  48         AddItem
  49         AddItemBatchFromMarc
  50         ModItemFromMarc
  51                 Item2Marc
  52         ModItem
  53         ModDateLastSeen
  54         ModItemTransfer
  55         DelItem
  56
  57         CheckItemPreSave
  58
  59         GetItemStatus
  60         GetItemLocation
  61         GetLostItems
  62         GetItemsForInventory
  63         GetItemsCount
  64         GetItemInfosOf
  65         GetItemsByBiblioitemnumber
  66         GetItemsInfo
  67         get_itemnumbers_of
  68         GetItemnumberFromBarcode
  69         GetBarcodeFromItemnumber
  70
  71                 DelItemCheck
  72                 MoveItemFromBiblio
  73                 GetLatestAcquisitions
  74         CartToShelf
  75     );
  76 }
  77
  78 =head1 NAME
  79
  80 C4::Items - item management functions
  81
  82 =head1 DESCRIPTION
  83
  84 This module contains an API for manipulating item
  85 records in Koha, and is used by cataloguing, circulation,
  86 acquisitions, and serials management.
  87
  88 A Koha item record is stored in two places: the
  89 items table and embedded in a MARC tag in the XML
  90 version of the associated bib record in C<biblioitems.marcxml>.
  91 This is done to allow the item information to be readily
  92 indexed (e.g., by Zebra), but means that each item
  93 modification transaction must keep the items table
  94 and the MARC XML in sync at all times.
  95
  96 Consequently, all code that creates, modifies, or deletes
  97 item records B<must> use an appropriate function from
  98 C<C4::Items>.  If no existing function is suitable, it is
  99 better to add one to C<C4::Items> than to use add
 100 one-off SQL statements to add or modify items.
 101
 102 The items table will be considered authoritative.  In other
 103 words, if there is ever a discrepancy between the items
 104 table and the MARC XML, the items table should be considered
 105 accurate.
 106
 107 =head1 HISTORICAL NOTE
 108
 109 Most of the functions in C<C4::Items> were originally in
 110 the C<C4::Biblio> module.
 111
 112 =head1 CORE EXPORTED FUNCTIONS
 113
 114 The following functions are meant for use by users
 115 of C<C4::Items>
 116
 117 =cut
 118
 119 =head2 GetItem
 120
 121 =over 4
 122
 123 $item = GetItem($itemnumber,$barcode,$serial);
 124
 125 =back
 126
 127 Return item information, for a given itemnumber or barcode.
 128 The return value is a hashref mapping item column
 129 names to values.  If C<$serial> is true, include serial publication data.
 130
 131 =cut
 132
 133 sub GetItem {
 134     my ($itemnumber,$barcode, $serial) = @_;
 135     my $dbh = C4::Context->dbh;
 136         my $data;
 137     if ($itemnumber) {
 138         my $sth = $dbh->prepare("
 139             SELECT * FROM items
 140             WHERE itemnumber = ?");
 141         $sth->execute($itemnumber);
 142         $data = $sth->fetchrow_hashref;
 143     } else {
 144         my $sth = $dbh->prepare("
 145             SELECT * FROM items
 146             WHERE barcode = ?"
 147             );
 148         $sth->execute($barcode);
 149         $data = $sth->fetchrow_hashref;
 150     }
 151     if ( $serial) {
 152     my $ssth = $dbh->prepare("SELECT serialseq,publisheddate from serialitems left join serial on serialitems.serialid=serial.serialid where serialitems.itemnumber=?");
 153         $ssth->execute($data->{'itemnumber'}) ;
 154         ($data->{'serialseq'} , $data->{'publisheddate'}) = $ssth->fetchrow_array();
 155                 warn $data->{'serialseq'} , $data->{'publisheddate'};
 156     }
 157         #if we don't have an items.itype, use biblioitems.itemtype.
 158         if( ! $data->{'itype'} ) {
 159                 my $sth = $dbh->prepare("SELECT itemtype FROM biblioitems  WHERE biblionumber = ?");
 160                 $sth->execute($data->{'biblionumber'});
 161                 ($data->{'itype'}) = $sth->fetchrow_array;
 162         }
 163     return $data;
 164 }    # sub GetItem
 165
 166 =head2 CartToShelf
 167
 168 =over 4
 169
 170 CartToShelf($itemnumber);
 171
 172 =back
 173
 174 Set the current shelving location of the item record
 175 to its stored permanent shelving location.  This is
 176 primarily used to indicate when an item whose current
 177 location is a special processing ('PROC') or shelving cart
 178 ('CART') location is back in the stacks.
 179
 180 =cut
 181
 182 sub CartToShelf {
 183     my ( $itemnumber ) = @_;
 184
 185     unless ( $itemnumber ) {
 186         croak "FAILED CartToShelf() - no itemnumber supplied";
 187     }
 188
 189     my $item = GetItem($itemnumber);
 190     $item->{location} = $item->{permanent_location};
 191     ModItem($item, undef, $itemnumber);
 192 }
 193
 194 =head2 AddItemFromMarc
 195
 196 =over 4
 197
 198 my ($biblionumber, $biblioitemnumber, $itemnumber)
 199     = AddItemFromMarc($source_item_marc, $biblionumber);
 200
 201 =back
 202
 203 Given a MARC::Record object containing an embedded item
 204 record and a biblionumber, create a new item record.
 205
 206 =cut
 207
 208 sub AddItemFromMarc {
 209     my ( $source_item_marc, $biblionumber ) = @_;
 210     my $dbh = C4::Context->dbh;
 211
 212     # parse item hash from MARC
 213     my $frameworkcode = GetFrameworkCode( $biblionumber );
 214         my ($itemtag,$itemsubfield)=GetMarcFromKohaField("items.itemnumber",$frameworkcode);
 215
 216         my $localitemmarc=MARC::Record->new;
 217         $localitemmarc->append_fields($source_item_marc->field($itemtag));
 218     my $item = &TransformMarcToKoha( $dbh, $localitemmarc, $frameworkcode ,'items');
 219     my $unlinked_item_subfields = _get_unlinked_item_subfields($localitemmarc, $frameworkcode);
 220     return AddItem($item, $biblionumber, $dbh, $frameworkcode, $unlinked_item_subfields);
 221 }
 222
 223 =head2 AddItem
 224
 225 =over 4
 226
 227 my ($biblionumber, $biblioitemnumber, $itemnumber)
 228     = AddItem($item, $biblionumber[, $dbh, $frameworkcode, $unlinked_item_subfields]);
 229
 230 =back
 231
 232 Given a hash containing item column names as keys,
 233 create a new Koha item record.
 234
 235 The first two optional parameters (C<$dbh> and C<$frameworkcode>)
 236 do not need to be supplied for general use; they exist
 237 simply to allow them to be picked up from AddItemFromMarc.
 238
 239 The final optional parameter, C<$unlinked_item_subfields>, contains
 240 an arrayref containing subfields present in the original MARC
 241 representation of the item (e.g., from the item editor) that are
 242 not mapped to C<items> columns directly but should instead
 243 be stored in C<items.more_subfields_xml> and included in
 244 the biblio items tag for display and indexing.
 245
 246 =cut
 247
 248 sub AddItem {
 249     my $item = shift;
 250     my $biblionumber = shift;
 251
 252     my $dbh           = @_ ? shift : C4::Context->dbh;
 253     my $frameworkcode = @_ ? shift : GetFrameworkCode( $biblionumber );
 254     my $unlinked_item_subfields;
 255     if (@_) {
 256         $unlinked_item_subfields = shift
 257     };
 258
 259     # needs old biblionumber and biblioitemnumber
 260     $item->{'biblionumber'} = $biblionumber;
 261     my $sth = $dbh->prepare("SELECT biblioitemnumber FROM biblioitems WHERE biblionumber=?");
 262     $sth->execute( $item->{'biblionumber'} );
 263     ($item->{'biblioitemnumber'}) = $sth->fetchrow;
 264
 265     _set_defaults_for_add($item);
 266     _set_derived_columns_for_add($item);
 267     $item->{'more_subfields_xml'} = _get_unlinked_subfields_xml($unlinked_item_subfields);
 268     # FIXME - checks here
 269     unless ( $item->{itype} ) {  # default to biblioitem.itemtype if no itype
 270         my $itype_sth = $dbh->prepare("SELECT itemtype FROM biblioitems WHERE biblionumber = ?");
 271         $itype_sth->execute( $item->{'biblionumber'} );
 272         ( $item->{'itype'} ) = $itype_sth->fetchrow_array;
 273     }
 274
 275         my ( $itemnumber, $error ) = _koha_new_item( $item, $item->{barcode} );
 276     $item->{'itemnumber'} = $itemnumber;
 277
 278     # create MARC tag representing item and add to bib
 279     my $new_item_marc = _marc_from_item_hash($item, $frameworkcode, $unlinked_item_subfields);
 280     _add_item_field_to_biblio($new_item_marc, $item->{'biblionumber'}, $frameworkcode );
 281
 282     logaction("CATALOGUING", "ADD", $itemnumber, "item") if C4::Context->preference("CataloguingLog");
 283
 284     return ($item->{biblionumber}, $item->{biblioitemnumber}, $itemnumber);
 285 }
 286
 287 =head2 AddItemBatchFromMarc
 288
 289 =over 4
 290
 291 ($itemnumber_ref, $error_ref) = AddItemBatchFromMarc($record, $biblionumber, $biblioitemnumber, $frameworkcode);
 292
 293 =back
 294
 295 Efficiently create item records from a MARC biblio record with
 296 embedded item fields.  This routine is suitable for batch jobs.
 297
 298 This API assumes that the bib record has already been
 299 saved to the C<biblio> and C<biblioitems> tables.  It does
 300 not expect that C<biblioitems.marc> and C<biblioitems.marcxml>
 301 are populated, but it will do so via a call to ModBibiloMarc.
 302
 303 The goal of this API is to have a similar effect to using AddBiblio
 304 and AddItems in succession, but without inefficient repeated
 305 parsing of the MARC XML bib record.
 306
 307 This function returns an arrayref of new itemsnumbers and an arrayref of item
 308 errors encountered during the processing.  Each entry in the errors
 309 list is a hashref containing the following keys:
 310
 311 =over 2
 312
 313 =item item_sequence
 314
 315 Sequence number of original item tag in the MARC record.
 316
 317 =item item_barcode
 318
 319 Item barcode, provide to assist in the construction of
 320 useful error messages.
 321
 322 =item error_condition
 323
 324 Code representing the error condition.  Can be 'duplicate_barcode',
 325 'invalid_homebranch', or 'invalid_holdingbranch'.
 326
 327 =item error_information
 328
 329 Additional information appropriate to the error condition.
 330
 331 =back
 332
 333 =cut
 334
 335 sub AddItemBatchFromMarc {
 336     my ($record, $biblionumber, $biblioitemnumber, $frameworkcode) = @_;
 337     my $error;
 338     my @itemnumbers = ();
 339     my @errors = ();
 340     my $dbh = C4::Context->dbh;
 341
 342     # loop through the item tags and start creating items
 343     my @bad_item_fields = ();
 344     my ($itemtag, $itemsubfield) = &GetMarcFromKohaField("items.itemnumber",'');
 345     my $item_sequence_num = 0;
 346     ITEMFIELD: foreach my $item_field ($record->field($itemtag)) {
 347         $item_sequence_num++;
 348         # we take the item field and stick it into a new
 349         # MARC record -- this is required so far because (FIXME)
 350         # TransformMarcToKoha requires a MARC::Record, not a MARC::Field
 351         # and there is no TransformMarcFieldToKoha
 352         my $temp_item_marc = MARC::Record->new();
 353         $temp_item_marc->append_fields($item_field);
 354
 355         # add biblionumber and biblioitemnumber
 356         my $item = TransformMarcToKoha( $dbh, $temp_item_marc, $frameworkcode, 'items' );
 357         my $unlinked_item_subfields = _get_unlinked_item_subfields($temp_item_marc, $frameworkcode);
 358         $item->{'more_subfields_xml'} = _get_unlinked_subfields_xml($unlinked_item_subfields);
 359         $item->{'biblionumber'} = $biblionumber;
 360         $item->{'biblioitemnumber'} = $biblioitemnumber;
 361
 362         # check for duplicate barcode
 363         my %item_errors = CheckItemPreSave($item);
 364         if (%item_errors) {
 365             push @errors, _repack_item_errors($item_sequence_num, $item, \%item_errors);
 366             push @bad_item_fields, $item_field;
 367             next ITEMFIELD;
 368         }
 369
 370         _set_defaults_for_add($item);
 371         _set_derived_columns_for_add($item);
 372         my ( $itemnumber, $error ) = _koha_new_item( $item, $item->{barcode} );
 373         warn $error if $error;
 374         push @itemnumbers, $itemnumber; # FIXME not checking error
 375         $item->{'itemnumber'} = $itemnumber;
 376
 377         logaction("CATALOGUING", "ADD", $itemnumber, "item") if C4::Context->preference("CataloguingLog");
 378
 379         my $new_item_marc = _marc_from_item_hash($item, $frameworkcode, $unlinked_item_subfields);
 380         $item_field->replace_with($new_item_marc->field($itemtag));
 381     }
 382
 383     # remove any MARC item fields for rejected items
 384     foreach my $item_field (@bad_item_fields) {
 385         $record->delete_field($item_field);
 386     }
 387
 388     # update the MARC biblio
 389     $biblionumber = ModBiblioMarc( $record, $biblionumber, $frameworkcode );
 390
 391     return (\@itemnumbers, \@errors);
 392 }
 393
 394 =head2 ModItemFromMarc
 395
 396 =over 4
 397
 398 ModItemFromMarc($item_marc, $biblionumber, $itemnumber);
 399
 400 =back
 401
 402 This function updates an item record based on a supplied
 403 C<MARC::Record> object containing an embedded item field.
 404 This API is meant for the use of C<additem.pl>; for
 405 other purposes, C<ModItem> should be used.
 406
 407 This function uses the hash %default_values_for_mod_from_marc,
 408 which contains default values for item fields to
 409 apply when modifying an item.  This is needed beccause
 410 if an item field's value is cleared, TransformMarcToKoha
 411 does not include the column in the
 412 hash that's passed to ModItem, which without
 413 use of this hash makes it impossible to clear
 414 an item field's value.  See bug 2466.
 415
 416 Note that only columns that can be directly
 417 changed from the cataloging and serials
 418 item editors are included in this hash.
 419
 420 =cut
 421
 422 my %default_values_for_mod_from_marc = (
 423     barcode              => undef,
 424     booksellerid         => undef,
 425     ccode                => undef,
 426     'items.cn_source'    => undef,
 427     copynumber           => undef,
 428     damaged              => 0,
 429     dateaccessioned      => undef,
 430     enumchron            => undef,
 431     holdingbranch        => undef,
 432     homebranch           => undef,
 433     itemcallnumber       => undef,
 434     itemlost             => 0,
 435     itemnotes            => undef,
 436     itype                => undef,
 437     location             => undef,
 438     materials            => undef,
 439     notforloan           => 0,
 440     paidfor              => undef,
 441     price                => undef,
 442     replacementprice     => undef,
 443     replacementpricedate => undef,
 444     restricted           => undef,
 445     stack                => undef,
 446     stocknumber          => undef,
 447     uri                  => undef,
 448     wthdrawn             => 0,
 449 );
 450
 451 sub ModItemFromMarc {
 452     my $item_marc = shift;
 453     my $biblionumber = shift;
 454     my $itemnumber = shift;
 455
 456     my $dbh = C4::Context->dbh;
 457     my $frameworkcode = GetFrameworkCode( $biblionumber );
 458         my ($itemtag,$itemsubfield)=GetMarcFromKohaField("items.itemnumber",$frameworkcode);
 459
 460         my $localitemmarc=MARC::Record->new;
 461         $localitemmarc->append_fields($item_marc->field($itemtag));
 462     my $item = &TransformMarcToKoha( $dbh, $localitemmarc, $frameworkcode, 'items');
 463     foreach my $item_field (keys %default_values_for_mod_from_marc) {
 464         $item->{$item_field} = $default_values_for_mod_from_marc{$item_field} unless exists $item->{$item_field};
 465     }
 466     my $unlinked_item_subfields = _get_unlinked_item_subfields($localitemmarc, $frameworkcode);
 467
 468     return ModItem($item, $biblionumber, $itemnumber, $dbh, $frameworkcode, $unlinked_item_subfields);
 469 }
 470
 471 =head2 ModItem
 472
 473 =over 4
 474
 475 ModItem({ column => $newvalue }, $biblionumber, $itemnumber[, $original_item_marc]);
 476
 477 =back
 478
 479 Change one or more columns in an item record and update
 480 the MARC representation of the item.
 481
 482 The first argument is a hashref mapping from item column
 483 names to the new values.  The second and third arguments
 484 are the biblionumber and itemnumber, respectively.
 485
 486 The fourth, optional parameter, C<$unlinked_item_subfields>, contains
 487 an arrayref containing subfields present in the original MARC
 488 representation of the item (e.g., from the item editor) that are
 489 not mapped to C<items> columns directly but should instead
 490 be stored in C<items.more_subfields_xml> and included in
 491 the biblio items tag for display and indexing.
 492
 493 If one of the changed columns is used to calculate
 494 the derived value of a column such as C<items.cn_sort>,
 495 this routine will perform the necessary calculation
 496 and set the value.
 497
 498 =cut
 499
 500 sub ModItem {
 501     my $item = shift;
 502     my $biblionumber = shift;
 503     my $itemnumber = shift;
 504
 505     # if $biblionumber is undefined, get it from the current item
 506     unless (defined $biblionumber) {
 507         $biblionumber = _get_single_item_column('biblionumber', $itemnumber);
 508     }
 509
 510     my $dbh           = @_ ? shift : C4::Context->dbh;
 511     my $frameworkcode = @_ ? shift : GetFrameworkCode( $biblionumber );
 512
 513     my $unlinked_item_subfields;
 514     if (@_) {
 515         $unlinked_item_subfields = shift;
 516         $item->{'more_subfields_xml'} = _get_unlinked_subfields_xml($unlinked_item_subfields);
 517     };
 518
 519     $item->{'itemnumber'} = $itemnumber or return undef;
 520     _set_derived_columns_for_mod($item);
 521     _do_column_fixes_for_mod($item);
 522     # FIXME add checks
 523     # duplicate barcode
 524     # attempt to change itemnumber
 525     # attempt to change biblionumber (if we want
 526     # an API to relink an item to a different bib,
 527     # it should be a separate function)
 528
 529     # update items table
 530     _koha_modify_item($item);
 531
 532     # update biblio MARC XML
 533     my $whole_item = GetItem($itemnumber) or die "FAILED GetItem($itemnumber)";
 534
 535     unless (defined $unlinked_item_subfields) {
 536         $unlinked_item_subfields = _parse_unlinked_item_subfields_from_xml($whole_item->{'more_subfields_xml'});
 537     }
 538     my $new_item_marc = _marc_from_item_hash($whole_item, $frameworkcode, $unlinked_item_subfields)
 539         or die "FAILED _marc_from_item_hash($whole_item, $frameworkcode)";
 540
 541     _replace_item_field_in_biblio($new_item_marc, $biblionumber, $itemnumber, $frameworkcode);
 542         ($new_item_marc       eq '0') and die "$new_item_marc is '0', not hashref";  # logaction line would crash anyway
 543     logaction("CATALOGUING", "MODIFY", $itemnumber, $new_item_marc->as_formatted) if C4::Context->preference("CataloguingLog");
 544 }
 545
 546 =head2 ModItemTransfer
 547
 548 =over 4
 549
 550 ModItemTransfer($itenumber, $frombranch, $tobranch);
 551
 552 =back
 553
 554 Marks an item as being transferred from one branch
 555 to another.
 556
 557 =cut
 558
 559 sub ModItemTransfer {
 560     my ( $itemnumber, $frombranch, $tobranch ) = @_;
 561
 562     my $dbh = C4::Context->dbh;
 563
 564     #new entry in branchtransfers....
 565     my $sth = $dbh->prepare(
 566         "INSERT INTO branchtransfers (itemnumber, frombranch, datesent, tobranch)
 567         VALUES (?, ?, NOW(), ?)");
 568     $sth->execute($itemnumber, $frombranch, $tobranch);
 569
 570     ModItem({ holdingbranch => $tobranch }, undef, $itemnumber);
 571     ModDateLastSeen($itemnumber);
 572     return;
 573 }
 574
 575 =head2 ModDateLastSeen
 576
 577 =over 4
 578
 579 ModDateLastSeen($itemnum);
 580
 581 =back
 582
 583 Mark item as seen. Is called when an item is issued, returned or manually marked during inventory/stocktaking.
 584 C<$itemnum> is the item number
 585
 586 =cut
 587
 588 sub ModDateLastSeen {
 589     my ($itemnumber) = @_;
 590
 591     my $today = C4::Dates->new();
 592     ModItem({ itemlost => 0, datelastseen => $today->output("iso") }, undef, $itemnumber);
 593 }
 594
 595 =head2 DelItem
 596
 597 =over 4
 598
 599 DelItem($dbh, $biblionumber, $itemnumber);
 600
 601 =back
 602
 603 Exported function (core API) for deleting an item record in Koha.
 604
 605 =cut
 606
 607 sub DelItem {
 608     my ( $dbh, $biblionumber, $itemnumber ) = @_;
 609
 610     # FIXME check the item has no current issues
 611
 612     _koha_delete_item( $dbh, $itemnumber );
 613
 614     # get the MARC record
 615     my $record = GetMarcBiblio($biblionumber);
 616     my $frameworkcode = GetFrameworkCode($biblionumber);
 617
 618     # backup the record
 619     my $copy2deleted = $dbh->prepare("UPDATE deleteditems SET marc=? WHERE itemnumber=?");
 620     $copy2deleted->execute( $record->as_usmarc(), $itemnumber );
 621
 622     #search item field code
 623     my ( $itemtag, $itemsubfield ) = GetMarcFromKohaField("items.itemnumber",$frameworkcode);
 624     my @fields = $record->field($itemtag);
 625
 626     # delete the item specified
 627     foreach my $field (@fields) {
 628         if ( $field->subfield($itemsubfield) eq $itemnumber ) {
 629             $record->delete_field($field);
 630         }
 631     }
 632     &ModBiblioMarc( $record, $biblionumber, $frameworkcode );
 633     logaction("CATALOGUING", "DELETE", $itemnumber, "item") if C4::Context->preference("CataloguingLog");
 634 }
 635
 636 =head2 CheckItemPreSave
 637
 638 =over 4
 639
 640     my $item_ref = TransformMarcToKoha($marc, 'items');
 641     # do stuff
 642     my %errors = CheckItemPreSave($item_ref);
 643     if (exists $errors{'duplicate_barcode'}) {
 644         print "item has duplicate barcode: ", $errors{'duplicate_barcode'}, "\n";
 645     } elsif (exists $errors{'invalid_homebranch'}) {
 646         print "item has invalid home branch: ", $errors{'invalid_homebranch'}, "\n";
 647     } elsif (exists $errors{'invalid_holdingbranch'}) {
 648         print "item has invalid holding branch: ", $errors{'invalid_holdingbranch'}, "\n";
 649     } else {
 650         print "item is OK";
 651     }
 652
 653 =back
 654
 655 Given a hashref containing item fields, determine if it can be
 656 inserted or updated in the database.  Specifically, checks for
 657 database integrity issues, and returns a hash containing any
 658 of the following keys, if applicable.
 659
 660 =over 2
 661
 662 =item duplicate_barcode
 663
 664 Barcode, if it duplicates one already found in the database.
 665
 666 =item invalid_homebranch
 667
 668 Home branch, if not defined in branches table.
 669
 670 =item invalid_holdingbranch
 671
 672 Holding branch, if not defined in branches table.
 673
 674 =back
 675
 676 This function does NOT implement any policy-related checks,
 677 e.g., whether current operator is allowed to save an
 678 item that has a given branch code.
 679
 680 =cut
 681
 682 sub CheckItemPreSave {
 683     my $item_ref = shift;
 684
 685     my %errors = ();
 686
 687     # check for duplicate barcode
 688     if (exists $item_ref->{'barcode'} and defined $item_ref->{'barcode'}) {
 689         my $existing_itemnumber = GetItemnumberFromBarcode($item_ref->{'barcode'});
 690         if ($existing_itemnumber) {
 691             if (!exists $item_ref->{'itemnumber'}                       # new item
 692                 or $item_ref->{'itemnumber'} != $existing_itemnumber) { # existing item
 693                 $errors{'duplicate_barcode'} = $item_ref->{'barcode'};
 694             }
 695         }
 696     }
 697
 698     # check for valid home branch
 699     if (exists $item_ref->{'homebranch'} and defined $item_ref->{'homebranch'}) {
 700         my $branch_name = GetBranchName($item_ref->{'homebranch'});
 701         unless (defined $branch_name) {
 702             # relies on fact that branches.branchname is a non-NULL column,
 703             # so GetBranchName returns undef only if branch does not exist
 704             $errors{'invalid_homebranch'} = $item_ref->{'homebranch'};
 705         }
 706     }
 707
 708     # check for valid holding branch
 709     if (exists $item_ref->{'holdingbranch'} and defined $item_ref->{'holdingbranch'}) {
 710         my $branch_name = GetBranchName($item_ref->{'holdingbranch'});
 711         unless (defined $branch_name) {
 712             # relies on fact that branches.branchname is a non-NULL column,
 713             # so GetBranchName returns undef only if branch does not exist
 714             $errors{'invalid_holdingbranch'} = $item_ref->{'holdingbranch'};
 715         }
 716     }
 717
 718     return %errors;
 719
 720 }
 721
 722 =head1 EXPORTED SPECIAL ACCESSOR FUNCTIONS
 723
 724 The following functions provide various ways of
 725 getting an item record, a set of item records, or
 726 lists of authorized values for certain item fields.
 727
 728 Some of the functions in this group are candidates
 729 for refactoring -- for example, some of the code
 730 in C<GetItemsByBiblioitemnumber> and C<GetItemsInfo>
 731 has copy-and-paste work.
 732
 733 =cut
 734
 735 =head2 GetItemStatus
 736
 737 =over 4
 738
 739 $itemstatushash = GetItemStatus($fwkcode);
 740
 741 =back
 742
 743 Returns a list of valid values for the
 744 C<items.notforloan> field.
 745
 746 NOTE: does B<not> return an individual item's
 747 status.
 748
 749 Can be MARC dependant.
 750 fwkcode is optional.
 751 But basically could be can be loan or not
 752 Create a status selector with the following code
 753
 754 =head3 in PERL SCRIPT
 755
 756 =over 4
 757
 758 my $itemstatushash = getitemstatus;
 759 my @itemstatusloop;
 760 foreach my $thisstatus (keys %$itemstatushash) {
 761     my %row =(value => $thisstatus,
 762                 statusname => $itemstatushash->{$thisstatus}->{'statusname'},
 763             );
 764     push @itemstatusloop, \%row;
 765 }
 766 $template->param(statusloop=>\@itemstatusloop);
 767
 768 =back
 769
 770 =head3 in TEMPLATE
 771
 772 =over 4
 773
 774 <select name="statusloop">
 775     <option value="">Default</option>
 776 <!-- TMPL_LOOP name="statusloop" -->
 777     <option value="<!-- TMPL_VAR name="value" -->" <!-- TMPL_IF name="selected" -->selected<!-- /TMPL_IF -->><!-- TMPL_VAR name="statusname" --></option>
 778 <!-- /TMPL_LOOP -->
 779 </select>
 780
 781 =back
 782
 783 =cut
 784
 785 sub GetItemStatus {
 786
 787     # returns a reference to a hash of references to status...
 788     my ($fwk) = @_;
 789     my %itemstatus;
 790     my $dbh = C4::Context->dbh;
 791     my $sth;
 792     $fwk = '' unless ($fwk);
 793     my ( $tag, $subfield ) =
 794       GetMarcFromKohaField( "items.notforloan", $fwk );
 795     if ( $tag and $subfield ) {
 796         my $sth =
 797           $dbh->prepare(
 798             "SELECT authorised_value
 799             FROM marc_subfield_structure
 800             WHERE tagfield=?
 801                 AND tagsubfield=?
 802                 AND frameworkcode=?
 803             "
 804           );
 805         $sth->execute( $tag, $subfield, $fwk );
 806         if ( my ($authorisedvaluecat) = $sth->fetchrow ) {
 807             my $authvalsth =
 808               $dbh->prepare(
 809                 "SELECT authorised_value,lib
 810                 FROM authorised_values
 811                 WHERE category=?
 812                 ORDER BY lib
 813                 "
 814               );
 815             $authvalsth->execute($authorisedvaluecat);
 816             while ( my ( $authorisedvalue, $lib ) = $authvalsth->fetchrow ) {
 817                 $itemstatus{$authorisedvalue} = $lib;
 818             }
 819             return \%itemstatus;
 820             exit 1;
 821         }
 822         else {
 823
 824             #No authvalue list
 825             # build default
 826         }
 827     }
 828
 829     #No authvalue list
 830     #build default
 831     $itemstatus{"1"} = "Not For Loan";
 832     return \%itemstatus;
 833 }
 834
 835 =head2 GetItemLocation
 836
 837 =over 4
 838
 839 $itemlochash = GetItemLocation($fwk);
 840
 841 =back
 842
 843 Returns a list of valid values for the
 844 C<items.location> field.
 845
 846 NOTE: does B<not> return an individual item's
 847 location.
 848
 849 where fwk stands for an optional framework code.
 850 Create a location selector with the following code
 851
 852 =head3 in PERL SCRIPT
 853
 854 =over 4
 855
 856 my $itemlochash = getitemlocation;
 857 my @itemlocloop;
 858 foreach my $thisloc (keys %$itemlochash) {
 859     my $selected = 1 if $thisbranch eq $branch;
 860     my %row =(locval => $thisloc,
 861                 selected => $selected,
 862                 locname => $itemlochash->{$thisloc},
 863             );
 864     push @itemlocloop, \%row;
 865 }
 866 $template->param(itemlocationloop => \@itemlocloop);
 867
 868 =back
 869
 870 =head3 in TEMPLATE
 871
 872 =over 4
 873
 874 <select name="location">
 875     <option value="">Default</option>
 876 <!-- TMPL_LOOP name="itemlocationloop" -->
 877     <option value="<!-- TMPL_VAR name="locval" -->" <!-- TMPL_IF name="selected" -->selected<!-- /TMPL_IF -->><!-- TMPL_VAR name="locname" --></option>
 878 <!-- /TMPL_LOOP -->
 879 </select>
 880
 881 =back
 882
 883 =cut
 884
 885 sub GetItemLocation {
 886
 887     # returns a reference to a hash of references to location...
 888     my ($fwk) = @_;
 889     my %itemlocation;
 890     my $dbh = C4::Context->dbh;
 891     my $sth;
 892     $fwk = '' unless ($fwk);
 893     my ( $tag, $subfield ) =
 894       GetMarcFromKohaField( "items.location", $fwk );
 895     if ( $tag and $subfield ) {
 896         my $sth =
 897           $dbh->prepare(
 898             "SELECT authorised_value
 899             FROM marc_subfield_structure
 900             WHERE tagfield=?
 901                 AND tagsubfield=?
 902                 AND frameworkcode=?"
 903           );
 904         $sth->execute( $tag, $subfield, $fwk );
 905         if ( my ($authorisedvaluecat) = $sth->fetchrow ) {
 906             my $authvalsth =
 907               $dbh->prepare(
 908                 "SELECT authorised_value,lib
 909                 FROM authorised_values
 910                 WHERE category=?
 911                 ORDER BY lib"
 912               );
 913             $authvalsth->execute($authorisedvaluecat);
 914             while ( my ( $authorisedvalue, $lib ) = $authvalsth->fetchrow ) {
 915                 $itemlocation{$authorisedvalue} = $lib;
 916             }
 917             return \%itemlocation;
 918             exit 1;
 919         }
 920         else {
 921
 922             #No authvalue list
 923             # build default
 924         }
 925     }
 926
 927     #No authvalue list
 928     #build default
 929     $itemlocation{"1"} = "Not For Loan";
 930     return \%itemlocation;
 931 }
 932
 933 =head2 GetLostItems
 934
 935 =over 4
 936
 937 $items = GetLostItems( $where, $orderby );
 938
 939 =back
 940
 941 This function gets a list of lost items.
 942
 943 =over 2
 944
 945 =item input:
 946
 947 C<$where> is a hashref. it containts a field of the items table as key
 948 and the value to match as value. For example:
 949
 950 { barcode    => 'abc123',
 951   homebranch => 'CPL',    }
 952
 953 C<$orderby> is a field of the items table by which the resultset
 954 should be orderd.
 955
 956 =item return:
 957
 958 C<$items> is a reference to an array full of hashrefs with columns
 959 from the "items" table as keys.
 960
 961 =item usage in the perl script:
 962
 963 my $where = { barcode => '0001548' };
 964 my $items = GetLostItems( $where, "homebranch" );
 965 $template->param( itemsloop => $items );
 966
 967 =back
 968
 969 =cut
 970
 971 sub GetLostItems {
 972     # Getting input args.
 973     my $where   = shift;
 974     my $orderby = shift;
 975     my $dbh     = C4::Context->dbh;
 976
 977     my $query   = "
 978         SELECT *
 979         FROM   items
 980             LEFT JOIN biblio ON (items.biblionumber = biblio.biblionumber)
 981             LEFT JOIN biblioitems ON (items.biblionumber = biblioitems.biblionumber)
 982             LEFT JOIN authorised_values ON (items.itemlost = authorised_values.authorised_value)
 983         WHERE
 984                 authorised_values.category = 'LOST'
 985                 AND itemlost IS NOT NULL
 986                 AND itemlost <> 0
 987     ";
 988     my @query_parameters;
 989     foreach my $key (keys %$where) {
 990         $query .= " AND $key LIKE ?";
 991         push @query_parameters, "%$where->{$key}%";
 992     }
 993     my @ordervalues = qw/title author homebranch itype barcode price replacementprice lib datelastseen location/;
 994
 995     if ( defined $orderby && grep($orderby, @ordervalues)) {
 996         $query .= ' ORDER BY '.$orderby;
 997     }
 998
 999     my $sth = $dbh->prepare($query);
1000     $sth->execute( @query_parameters );
1001     my $items = [];
1002     while ( my $row = $sth->fetchrow_hashref ){
1003         push @$items, $row;
1004     }
1005     return $items;
1006 }
1007
1008 =head2 GetItemsForInventory
1009
1010 =over 4
1011
1012 $itemlist = GetItemsForInventory($minlocation, $maxlocation, $location, $itemtype $datelastseen, $branch, $offset, $size, $statushash);
1013
1014 =back
1015
1016 Retrieve a list of title/authors/barcode/callnumber, for biblio inventory.
1017
1018 The sub returns a reference to a list of hashes, each containing
1019 itemnumber, author, title, barcode, item callnumber, and date last
1020 seen. It is ordered by callnumber then title.
1021
1022 The required minlocation & maxlocation parameters are used to specify a range of item callnumbers
1023 the datelastseen can be used to specify that you want to see items not seen since a past date only.
1024 offset & size can be used to retrieve only a part of the whole listing (defaut behaviour)
1025 $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.
1026
1027 =cut
1028
1029 sub GetItemsForInventory {
1030     my ( $minlocation, $maxlocation,$location, $itemtype, $ignoreissued, $datelastseen, $branch, $offset, $size, $statushash ) = @_;
1031     my $dbh = C4::Context->dbh;
1032     my ( @bind_params, @where_strings );
1033
1034     my $query = <<'END_SQL';
1035 SELECT items.itemnumber, barcode, itemcallnumber, title, author, biblio.biblionumber, datelastseen
1036 FROM items
1037   LEFT JOIN biblio ON items.biblionumber = biblio.biblionumber
1038   LEFT JOIN biblioitems on items.biblionumber = biblioitems.biblionumber
1039 END_SQL
1040     if ($statushash){
1041         for my $authvfield (keys %$statushash){
1042             if ( scalar @{$statushash->{$authvfield}} > 0 ){
1043                 my $joinedvals = join ',', @{$statushash->{$authvfield}};
1044                 push @where_strings, "$authvfield in (" . $joinedvals . ")";
1045             }
1046         }
1047     }
1048
1049     if ($minlocation) {
1050         push @where_strings, 'itemcallnumber >= ?';
1051         push @bind_params, $minlocation;
1052     }
1053
1054     if ($maxlocation) {
1055         push @where_strings, 'itemcallnumber <= ?';
1056         push @bind_params, $maxlocation;
1057     }
1058
1059     if ($datelastseen) {
1060         $datelastseen = format_date_in_iso($datelastseen);
1061         push @where_strings, '(datelastseen < ? OR datelastseen IS NULL)';
1062         push @bind_params, $datelastseen;
1063     }
1064
1065     if ( $location ) {
1066         push @where_strings, 'items.location = ?';
1067         push @bind_params, $location;
1068     }
1069
1070     if ( $branch ) {
1071         push @where_strings, 'items.homebranch = ?';
1072         push @bind_params, $branch;
1073     }
1074
1075     if ( $itemtype ) {
1076         push @where_strings, 'biblioitems.itemtype = ?';
1077         push @bind_params, $itemtype;
1078     }
1079
1080     if ( $ignoreissued) {
1081         $query .= "LEFT JOIN issues ON items.itemnumber = issues.itemnumber ";
1082         push @where_strings, 'issues.date_due IS NULL';
1083     }
1084
1085     if ( @where_strings ) {
1086         $query .= 'WHERE ';
1087         $query .= join ' AND ', @where_strings;
1088     }
1089     $query .= ' ORDER BY items.cn_sort, itemcallnumber, title';
1090     my $sth = $dbh->prepare($query);
1091     $sth->execute( @bind_params );
1092
1093     my @results;
1094     $size--;
1095     while ( my $row = $sth->fetchrow_hashref ) {
1096         $offset-- if ($offset);
1097         $row->{datelastseen}=format_date($row->{datelastseen});
1098         if ( ( !$offset ) && $size ) {
1099             push @results, $row;
1100             $size--;
1101         }
1102     }
1103     return \@results;
1104 }
1105
1106 =head2 GetItemsCount
1107
1108 =over 4
1109 $count = &GetItemsCount( $biblionumber);
1110
1111 =back
1112
1113 This function return count of item with $biblionumber
1114
1115 =cut
1116
1117 sub GetItemsCount {
1118     my ( $biblionumber ) = @_;
1119     my $dbh = C4::Context->dbh;
1120     my $query = "SELECT count(*)
1121           FROM  items
1122           WHERE biblionumber=?";
1123     my $sth = $dbh->prepare($query);
1124     $sth->execute($biblionumber);
1125     my $count = $sth->fetchrow;
1126     return ($count);
1127 }
1128
1129 =head2 GetItemInfosOf
1130
1131 =over 4
1132
1133 GetItemInfosOf(@itemnumbers);
1134
1135 =back
1136
1137 =cut
1138
1139 sub GetItemInfosOf {
1140     my @itemnumbers = @_;
1141
1142     my $query = '
1143         SELECT *
1144         FROM items
1145         WHERE itemnumber IN (' . join( ',', @itemnumbers ) . ')
1146     ';
1147     return get_infos_of( $query, 'itemnumber' );
1148 }
1149
1150 =head2 GetItemsByBiblioitemnumber
1151
1152 =over 4
1153
1154 GetItemsByBiblioitemnumber($biblioitemnumber);
1155
1156 =back
1157
1158 Returns an arrayref of hashrefs suitable for use in a TMPL_LOOP
1159 Called by C<C4::XISBN>
1160
1161 =cut
1162
1163 sub GetItemsByBiblioitemnumber {
1164     my ( $bibitem ) = @_;
1165     my $dbh = C4::Context->dbh;
1166     my $sth = $dbh->prepare("SELECT * FROM items WHERE items.biblioitemnumber = ?") || die $dbh->errstr;
1167     # Get all items attached to a biblioitem
1168     my $i = 0;
1169     my @results;
1170     $sth->execute($bibitem) || die $sth->errstr;
1171     while ( my $data = $sth->fetchrow_hashref ) {
1172         # Foreach item, get circulation information
1173         my $sth2 = $dbh->prepare( "SELECT * FROM issues,borrowers
1174                                    WHERE itemnumber = ?
1175                                    AND issues.borrowernumber = borrowers.borrowernumber"
1176         );
1177         $sth2->execute( $data->{'itemnumber'} );
1178         if ( my $data2 = $sth2->fetchrow_hashref ) {
1179             # if item is out, set the due date and who it is out too
1180             $data->{'date_due'}   = $data2->{'date_due'};
1181             $data->{'cardnumber'} = $data2->{'cardnumber'};
1182             $data->{'borrowernumber'}   = $data2->{'borrowernumber'};
1183         }
1184         else {
1185             # set date_due to blank, so in the template we check itemlost, and wthdrawn
1186             $data->{'date_due'} = '';
1187         }    # else
1188         # Find the last 3 people who borrowed this item.
1189         my $query2 = "SELECT * FROM old_issues, borrowers WHERE itemnumber = ?
1190                       AND old_issues.borrowernumber = borrowers.borrowernumber
1191                       ORDER BY returndate desc,timestamp desc LIMIT 3";
1192         $sth2 = $dbh->prepare($query2) || die $dbh->errstr;
1193         $sth2->execute( $data->{'itemnumber'} ) || die $sth2->errstr;
1194         my $i2 = 0;
1195         while ( my $data2 = $sth2->fetchrow_hashref ) {
1196             $data->{"timestamp$i2"} = $data2->{'timestamp'};
1197             $data->{"card$i2"}      = $data2->{'cardnumber'};
1198             $data->{"borrower$i2"}  = $data2->{'borrowernumber'};
1199             $i2++;
1200         }
1201         push(@results,$data);
1202     }
1203     return (\@results);
1204 }
1205
1206 =head2 GetItemsInfo
1207
1208 =over 4
1209
1210 @results = GetItemsInfo($biblionumber, $type);
1211
1212 =back
1213
1214 Returns information about books with the given biblionumber.
1215
1216 C<$type> may be either C<intra> or anything else. If it is not set to
1217 C<intra>, then the search will exclude lost, very overdue, and
1218 withdrawn items.
1219
1220 C<GetItemsInfo> returns a list of references-to-hash. Each element
1221 contains a number of keys. Most of them are table items from the
1222 C<biblio>, C<biblioitems>, C<items>, and C<itemtypes> tables in the
1223 Koha database. Other keys include:
1224
1225 =over 2
1226
1227 =item C<$data-E<gt>{branchname}>
1228
1229 The name (not the code) of the branch to which the book belongs.
1230
1231 =item C<$data-E<gt>{datelastseen}>
1232
1233 This is simply C<items.datelastseen>, except that while the date is
1234 stored in YYYY-MM-DD format in the database, here it is converted to
1235 DD/MM/YYYY format. A NULL date is returned as C<//>.
1236
1237 =item C<$data-E<gt>{datedue}>
1238
1239 =item C<$data-E<gt>{class}>
1240
1241 This is the concatenation of C<biblioitems.classification>, the book's
1242 Dewey code, and C<biblioitems.subclass>.
1243
1244 =item C<$data-E<gt>{ocount}>
1245
1246 I think this is the number of copies of the book available.
1247
1248 =item C<$data-E<gt>{order}>
1249
1250 If this is set, it is set to C<One Order>.
1251
1252 =back
1253
1254 =cut
1255
1256 sub GetItemsInfo {
1257     my ( $biblionumber, $type ) = @_;
1258     my $dbh   = C4::Context->dbh;
1259     # note biblioitems.* must be avoided to prevent large marc and marcxml fields from killing performance.
1260     my $query = "
1261     SELECT items.*,
1262            biblio.*,
1263            biblioitems.volume,
1264            biblioitems.number,
1265            biblioitems.itemtype,
1266            biblioitems.isbn,
1267            biblioitems.issn,
1268            biblioitems.publicationyear,
1269            biblioitems.publishercode,
1270            biblioitems.volumedate,
1271            biblioitems.volumedesc,
1272            biblioitems.lccn,
1273            biblioitems.url,
1274            items.notforloan as itemnotforloan,
1275            itemtypes.description,
1276            itemtypes.notforloan as notforloan_per_itemtype,
1277            branchurl
1278      FROM items
1279      LEFT JOIN branches ON items.homebranch = branches.branchcode
1280      LEFT JOIN biblio      ON      biblio.biblionumber     = items.biblionumber
1281      LEFT JOIN biblioitems ON biblioitems.biblioitemnumber = items.biblioitemnumber
1282      LEFT JOIN itemtypes   ON   itemtypes.itemtype         = "
1283      . (C4::Context->preference('item-level_itypes') ? 'items.itype' : 'biblioitems.itemtype');
1284     $query .= " WHERE items.biblionumber = ? ORDER BY branches.branchname,items.dateaccessioned desc" ;
1285     my $sth = $dbh->prepare($query);
1286     $sth->execute($biblionumber);
1287     my $i = 0;
1288     my @results;
1289     my $serial;
1290
1291     my $isth    = $dbh->prepare(
1292         "SELECT issues.*,borrowers.cardnumber,borrowers.surname,borrowers.firstname,borrowers.branchcode as bcode
1293         FROM   issues LEFT JOIN borrowers ON issues.borrowernumber=borrowers.borrowernumber
1294         WHERE  itemnumber = ?"
1295        );
1296         my $ssth = $dbh->prepare("SELECT serialseq,publisheddate from serialitems left join serial on serialitems.serialid=serial.serialid where serialitems.itemnumber=? ");
1297         while ( my $data = $sth->fetchrow_hashref ) {
1298         my $datedue = '';
1299         my $count_reserves;
1300         $isth->execute( $data->{'itemnumber'} );
1301         if ( my $idata = $isth->fetchrow_hashref ) {
1302             $data->{borrowernumber} = $idata->{borrowernumber};
1303             $data->{cardnumber}     = $idata->{cardnumber};
1304             $data->{surname}     = $idata->{surname};
1305             $data->{firstname}     = $idata->{firstname};
1306             $datedue                = $idata->{'date_due'};
1307         if (C4::Context->preference("IndependantBranches")){
1308         my $userenv = C4::Context->userenv;
1309         if ( ($userenv) && ( $userenv->{flags} % 2 != 1 ) ) {
1310             $data->{'NOTSAMEBRANCH'} = 1 if ($idata->{'bcode'} ne $userenv->{branch});
1311         }
1312         }
1313         }
1314                 if ( $data->{'serial'}) {
1315                         $ssth->execute($data->{'itemnumber'}) ;
1316                         ($data->{'serialseq'} , $data->{'publisheddate'}) = $ssth->fetchrow_array();
1317                         $serial = 1;
1318         }
1319                 if ( $datedue eq '' ) {
1320             my ( $restype, $reserves ) =
1321               C4::Reserves::CheckReserves( $data->{'itemnumber'} );
1322 # Previous conditional check with if ($restype) is not needed because a true
1323 # result for one item will result in subsequent items defaulting to this true
1324 # value.
1325             $count_reserves = $restype;
1326         }
1327         #get branch information.....
1328         my $bsth = $dbh->prepare(
1329             "SELECT * FROM branches WHERE branchcode = ?
1330         "
1331         );
1332         $bsth->execute( $data->{'holdingbranch'} );
1333         if ( my $bdata = $bsth->fetchrow_hashref ) {
1334             $data->{'branchname'} = $bdata->{'branchname'};
1335         }
1336         $data->{'datedue'}        = $datedue;
1337         $data->{'count_reserves'} = $count_reserves;
1338
1339         # get notforloan complete status if applicable
1340         my $sthnflstatus = $dbh->prepare(
1341             'SELECT authorised_value
1342             FROM   marc_subfield_structure
1343             WHERE  kohafield="items.notforloan"
1344         '
1345         );
1346
1347         $sthnflstatus->execute;
1348         my ($authorised_valuecode) = $sthnflstatus->fetchrow;
1349         if ($authorised_valuecode) {
1350             $sthnflstatus = $dbh->prepare(
1351                 "SELECT lib FROM authorised_values
1352                  WHERE  category=?
1353                  AND authorised_value=?"
1354             );
1355             $sthnflstatus->execute( $authorised_valuecode,
1356                 $data->{itemnotforloan} );
1357             my ($lib) = $sthnflstatus->fetchrow;
1358             $data->{notforloanvalue} = $lib;
1359         }
1360
1361         # my stack procedures
1362         my $stackstatus = $dbh->prepare(
1363             'SELECT authorised_value
1364              FROM   marc_subfield_structure
1365              WHERE  kohafield="items.stack"
1366         '
1367         );
1368         $stackstatus->execute;
1369
1370         ($authorised_valuecode) = $stackstatus->fetchrow;
1371         if ($authorised_valuecode) {
1372             $stackstatus = $dbh->prepare(
1373                 "SELECT lib
1374                  FROM   authorised_values
1375                  WHERE  category=?
1376                  AND    authorised_value=?
1377             "
1378             );
1379             $stackstatus->execute( $authorised_valuecode, $data->{stack} );
1380             my ($lib) = $stackstatus->fetchrow;
1381             $data->{stack} = $lib;
1382         }
1383         # Find the last 3 people who borrowed this item.
1384         my $sth2 = $dbh->prepare("SELECT * FROM old_issues,borrowers
1385                                     WHERE itemnumber = ?
1386                                     AND old_issues.borrowernumber = borrowers.borrowernumber
1387                                     ORDER BY returndate DESC
1388                                     LIMIT 3");
1389         $sth2->execute($data->{'itemnumber'});
1390         my $ii = 0;
1391         while (my $data2 = $sth2->fetchrow_hashref()) {
1392             $data->{"timestamp$ii"} = $data2->{'timestamp'} if $data2->{'timestamp'};
1393             $data->{"card$ii"}      = $data2->{'cardnumber'} if $data2->{'cardnumber'};
1394             $data->{"borrower$ii"}  = $data2->{'borrowernumber'} if $data2->{'borrowernumber'};
1395             $ii++;
1396         }
1397
1398         $results[$i] = $data;
1399         $i++;
1400     }
1401         if($serial) {
1402                 return( sort { ($b->{'publisheddate'} || $b->{'enumchron'}) cmp ($a->{'publisheddate'} || $a->{'enumchron'}) } @results );
1403         } else {
1404         return (@results);
1405         }
1406 }
1407
1408 =head2 GetLastAcquisitions
1409
1410 =over 4
1411
1412 my $lastacq = GetLastAcquisitions({'branches' => ('branch1','branch2'), 'itemtypes' => ('BK','BD')}, 10);
1413
1414 =back
1415
1416 =cut
1417
1418 sub  GetLastAcquisitions {
1419         my ($data,$max) = @_;
1420
1421         my $itemtype = C4::Context->preference('item-level_itypes') ? 'itype' : 'itemtype';
1422
1423         my $number_of_branches = @{$data->{branches}};
1424         my $number_of_itemtypes   = @{$data->{itemtypes}};
1425
1426
1427         my @where = ('WHERE 1 ');
1428         $number_of_branches and push @where
1429            , 'AND holdingbranch IN ('
1430            , join(',', ('?') x $number_of_branches )
1431            , ')'
1432          ;
1433
1434         $number_of_itemtypes and push @where
1435            , "AND $itemtype IN ("
1436            , join(',', ('?') x $number_of_itemtypes )
1437            , ')'
1438          ;
1439
1440         my $query = "SELECT biblio.biblionumber as biblionumber, title, dateaccessioned
1441                                  FROM items RIGHT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
1442                                     RIGHT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber)
1443                                     @where
1444                                     GROUP BY biblio.biblionumber
1445                                     ORDER BY dateaccessioned DESC LIMIT $max";
1446
1447         my $dbh = C4::Context->dbh;
1448         my $sth = $dbh->prepare($query);
1449
1450     $sth->execute((@{$data->{branches}}, @{$data->{itemtypes}}));
1451
1452         my @results;
1453         while( my $row = $sth->fetchrow_hashref){
1454                 push @results, {date => $row->{dateaccessioned}
1455                                                 , biblionumber => $row->{biblionumber}
1456                                                 , title => $row->{title}};
1457         }
1458
1459         return @results;
1460 }
1461
1462 =head2 get_itemnumbers_of
1463
1464 =over 4
1465
1466 my @itemnumbers_of = get_itemnumbers_of(@biblionumbers);
1467
1468 =back
1469
1470 Given a list of biblionumbers, return the list of corresponding itemnumbers
1471 for each biblionumber.
1472
1473 Return a reference on a hash where keys are biblionumbers and values are
1474 references on array of itemnumbers.
1475
1476 =cut
1477
1478 sub get_itemnumbers_of {
1479     my @biblionumbers = @_;
1480
1481     my $dbh = C4::Context->dbh;
1482
1483     my $query = '
1484         SELECT itemnumber,
1485             biblionumber
1486         FROM items
1487         WHERE biblionumber IN (?' . ( ',?' x scalar @biblionumbers - 1 ) . ')
1488     ';
1489     my $sth = $dbh->prepare($query);
1490     $sth->execute(@biblionumbers);
1491
1492     my %itemnumbers_of;
1493
1494     while ( my ( $itemnumber, $biblionumber ) = $sth->fetchrow_array ) {
1495         push @{ $itemnumbers_of{$biblionumber} }, $itemnumber;
1496     }
1497
1498     return \%itemnumbers_of;
1499 }
1500
1501 =head2 GetItemnumberFromBarcode
1502
1503 =over 4
1504
1505 $result = GetItemnumberFromBarcode($barcode);
1506
1507 =back
1508
1509 =cut
1510
1511 sub GetItemnumberFromBarcode {
1512     my ($barcode) = @_;
1513     my $dbh = C4::Context->dbh;
1514
1515     my $rq =
1516       $dbh->prepare("SELECT itemnumber FROM items WHERE items.barcode=?");
1517     $rq->execute($barcode);
1518     my ($result) = $rq->fetchrow;
1519     return ($result);
1520 }
1521
1522 =head2 GetBarcodeFromItemnumber
1523
1524 =over 4
1525
1526 $result = GetBarcodeFromItemnumber($itemnumber);
1527
1528 =back
1529
1530 =cut
1531
1532 sub GetBarcodeFromItemnumber {
1533     my ($itemnumber) = @_;
1534     my $dbh = C4::Context->dbh;
1535
1536     my $rq =
1537       $dbh->prepare("SELECT barcode FROM items WHERE items.itemnumber=?");
1538     $rq->execute($itemnumber);
1539     my ($result) = $rq->fetchrow;
1540     return ($result);
1541 }
1542
1543 =head3 get_item_authorised_values
1544
1545   find the types and values for all authorised values assigned to this item.
1546
1547   parameters:
1548     itemnumber
1549
1550   returns: a hashref malling the authorised value to the value set for this itemnumber
1551
1552     $authorised_values = {
1553              'CCODE'      => undef,
1554              'DAMAGED'    => '0',
1555              'LOC'        => '3',
1556              'LOST'       => '0'
1557              'NOT_LOAN'   => '0',
1558              'RESTRICTED' => undef,
1559              'STACK'      => undef,
1560              'WITHDRAWN'  => '0',
1561              'branches'   => 'CPL',
1562              'cn_source'  => undef,
1563              'itemtypes'  => 'SER',
1564            };
1565
1566    Notes: see C4::Biblio::get_biblio_authorised_values for a similar method at the biblio level.
1567
1568 =cut
1569
1570 sub get_item_authorised_values {
1571     my $itemnumber = shift;
1572
1573     # assume that these entries in the authorised_value table are item level.
1574     my $query = q(SELECT distinct authorised_value, kohafield
1575                     FROM marc_subfield_structure
1576                     WHERE kohafield like 'item%'
1577                       AND authorised_value != '' );
1578
1579     my $itemlevel_authorised_values = C4::Context->dbh->selectall_hashref( $query, 'authorised_value' );
1580     my $iteminfo = GetItem( $itemnumber );
1581     # warn( Data::Dumper->Dump( [ $itemlevel_authorised_values ], [ 'itemlevel_authorised_values' ] ) );
1582     my $return;
1583     foreach my $this_authorised_value ( keys %$itemlevel_authorised_values ) {
1584         my $field = $itemlevel_authorised_values->{ $this_authorised_value }->{'kohafield'};
1585         $field =~ s/^items\.//;
1586         if ( exists $iteminfo->{ $field } ) {
1587             $return->{ $this_authorised_value } = $iteminfo->{ $field };
1588         }
1589     }
1590     # warn( Data::Dumper->Dump( [ $return ], [ 'return' ] ) );
1591     return $return;
1592 }
1593
1594 =head3 get_authorised_value_images
1595
1596   find a list of icons that are appropriate for display based on the
1597   authorised values for a biblio.
1598
1599   parameters: listref of authorised values, such as comes from
1600     get_item_authorised_values or
1601     from C4::Biblio::get_biblio_authorised_values
1602
1603   returns: listref of hashrefs for each image. Each hashref looks like
1604     this:
1605
1606       { imageurl => '/intranet-tmpl/prog/img/itemtypeimg/npl/WEB.gif',
1607         label    => '',
1608         category => '',
1609         value    => '', }
1610
1611   Notes: Currently, I put on the full path to the images on the staff
1612   side. This should either be configurable or not done at all. Since I
1613   have to deal with 'intranet' or 'opac' in
1614   get_biblio_authorised_values, perhaps I should be passing it in.
1615
1616 =cut
1617
1618 sub get_authorised_value_images {
1619     my $authorised_values = shift;
1620
1621     my @imagelist;
1622
1623     my $authorised_value_list = GetAuthorisedValues();
1624     # warn ( Data::Dumper->Dump( [ $authorised_value_list ], [ 'authorised_value_list' ] ) );
1625     foreach my $this_authorised_value ( @$authorised_value_list ) {
1626         if ( exists $authorised_values->{ $this_authorised_value->{'category'} }
1627              && $authorised_values->{ $this_authorised_value->{'category'} } eq $this_authorised_value->{'authorised_value'} ) {
1628             # warn ( Data::Dumper->Dump( [ $this_authorised_value ], [ 'this_authorised_value' ] ) );
1629             if ( defined $this_authorised_value->{'imageurl'} ) {
1630                 push @imagelist, { imageurl => C4::Koha::getitemtypeimagelocation( 'intranet', $this_authorised_value->{'imageurl'} ),
1631                                    label    => $this_authorised_value->{'lib'},
1632                                    category => $this_authorised_value->{'category'},
1633                                    value    => $this_authorised_value->{'authorised_value'}, };
1634             }
1635         }
1636     }
1637
1638     # warn ( Data::Dumper->Dump( [ \@imagelist ], [ 'imagelist' ] ) );
1639     return \@imagelist;
1640
1641 }
1642
1643 =head1 LIMITED USE FUNCTIONS
1644
1645 The following functions, while part of the public API,
1646 are not exported.  This is generally because they are
1647 meant to be used by only one script for a specific
1648 purpose, and should not be used in any other context
1649 without careful thought.
1650
1651 =cut
1652
1653 =head2 GetMarcItem
1654
1655 =over 4
1656
1657 my $item_marc = GetMarcItem($biblionumber, $itemnumber);
1658
1659 =back
1660
1661 Returns MARC::Record of the item passed in parameter.
1662 This function is meant for use only in C<cataloguing/additem.pl>,
1663 where it is needed to support that script's MARC-like
1664 editor.
1665
1666 =cut
1667
1668 sub GetMarcItem {
1669     my ( $biblionumber, $itemnumber ) = @_;
1670
1671     # GetMarcItem has been revised so that it does the following:
1672     #  1. Gets the item information from the items table.
1673     #  2. Converts it to a MARC field for storage in the bib record.
1674     #
1675     # The previous behavior was:
1676     #  1. Get the bib record.
1677     #  2. Return the MARC tag corresponding to the item record.
1678     #
1679     # The difference is that one treats the items row as authoritative,
1680     # while the other treats the MARC representation as authoritative
1681     # under certain circumstances.
1682
1683     my $itemrecord = GetItem($itemnumber);
1684
1685     # Tack on 'items.' prefix to column names so that TransformKohaToMarc will work.
1686     # Also, don't emit a subfield if the underlying field is blank.
1687
1688
1689     return Item2Marc($itemrecord,$biblionumber);
1690
1691 }
1692 sub Item2Marc {
1693         my ($itemrecord,$biblionumber)=@_;
1694     my $mungeditem = {
1695         map {
1696             defined($itemrecord->{$_}) && $itemrecord->{$_} ne '' ? ("items.$_" => $itemrecord->{$_}) : ()
1697         } keys %{ $itemrecord }
1698     };
1699     my $itemmarc = TransformKohaToMarc($mungeditem);
1700     my ( $itemtag, $itemsubfield ) = GetMarcFromKohaField("items.itemnumber",GetFrameworkCode($biblionumber)||'');
1701
1702     my $unlinked_item_subfields = _parse_unlinked_item_subfields_from_xml($mungeditem->{'items.more_subfields_xml'});
1703     if (defined $unlinked_item_subfields and $#$unlinked_item_subfields > -1) {
1704                 foreach my $field ($itemmarc->field($itemtag)){
1705             $field->add_subfields(@$unlinked_item_subfields);
1706         }
1707     }
1708         return $itemmarc;
1709 }
1710
1711 =head1 PRIVATE FUNCTIONS AND VARIABLES
1712
1713 The following functions are not meant to be called
1714 directly, but are documented in order to explain
1715 the inner workings of C<C4::Items>.
1716
1717 =cut
1718
1719 =head2 %derived_columns
1720
1721 This hash keeps track of item columns that
1722 are strictly derived from other columns in
1723 the item record and are not meant to be set
1724 independently.
1725
1726 Each key in the hash should be the name of a
1727 column (as named by TransformMarcToKoha).  Each
1728 value should be hashref whose keys are the
1729 columns on which the derived column depends.  The
1730 hashref should also contain a 'BUILDER' key
1731 that is a reference to a sub that calculates
1732 the derived value.
1733
1734 =cut
1735
1736 my %derived_columns = (
1737     'items.cn_sort' => {
1738         'itemcallnumber' => 1,
1739         'items.cn_source' => 1,
1740         'BUILDER' => \&_calc_items_cn_sort,
1741     }
1742 );
1743
1744 =head2 _set_derived_columns_for_add
1745
1746 =over 4
1747
1748 _set_derived_column_for_add($item);
1749
1750 =back
1751
1752 Given an item hash representing a new item to be added,
1753 calculate any derived columns.  Currently the only
1754 such column is C<items.cn_sort>.
1755
1756 =cut
1757
1758 sub _set_derived_columns_for_add {
1759     my $item = shift;
1760
1761     foreach my $column (keys %derived_columns) {
1762         my $builder = $derived_columns{$column}->{'BUILDER'};
1763         my $source_values = {};
1764         foreach my $source_column (keys %{ $derived_columns{$column} }) {
1765             next if $source_column eq 'BUILDER';
1766             $source_values->{$source_column} = $item->{$source_column};
1767         }
1768         $builder->($item, $source_values);
1769     }
1770 }
1771
1772 =head2 _set_derived_columns_for_mod
1773
1774 =over 4
1775
1776 _set_derived_column_for_mod($item);
1777
1778 =back
1779
1780 Given an item hash representing a new item to be modified.
1781 calculate any derived columns.  Currently the only
1782 such column is C<items.cn_sort>.
1783
1784 This routine differs from C<_set_derived_columns_for_add>
1785 in that it needs to handle partial item records.  In other
1786 words, the caller of C<ModItem> may have supplied only one
1787 or two columns to be changed, so this function needs to
1788 determine whether any of the columns to be changed affect
1789 any of the derived columns.  Also, if a derived column
1790 depends on more than one column, but the caller is not
1791 changing all of then, this routine retrieves the unchanged
1792 values from the database in order to ensure a correct
1793 calculation.
1794
1795 =cut
1796
1797 sub _set_derived_columns_for_mod {
1798     my $item = shift;
1799
1800     foreach my $column (keys %derived_columns) {
1801         my $builder = $derived_columns{$column}->{'BUILDER'};
1802         my $source_values = {};
1803         my %missing_sources = ();
1804         my $must_recalc = 0;
1805         foreach my $source_column (keys %{ $derived_columns{$column} }) {
1806             next if $source_column eq 'BUILDER';
1807             if (exists $item->{$source_column}) {
1808                 $must_recalc = 1;
1809                 $source_values->{$source_column} = $item->{$source_column};
1810             } else {
1811                 $missing_sources{$source_column} = 1;
1812             }
1813         }
1814         if ($must_recalc) {
1815             foreach my $source_column (keys %missing_sources) {
1816                 $source_values->{$source_column} = _get_single_item_column($source_column, $item->{'itemnumber'});
1817             }
1818             $builder->($item, $source_values);
1819         }
1820     }
1821 }
1822
1823 =head2 _do_column_fixes_for_mod
1824
1825 =over 4
1826
1827 _do_column_fixes_for_mod($item);
1828
1829 =back
1830
1831 Given an item hashref containing one or more
1832 columns to modify, fix up certain values.
1833 Specifically, set to 0 any passed value
1834 of C<notforloan>, C<damaged>, C<itemlost>, or
1835 C<wthdrawn> that is either undefined or
1836 contains the empty string.
1837
1838 =cut
1839
1840 sub _do_column_fixes_for_mod {
1841     my $item = shift;
1842
1843     if (exists $item->{'notforloan'} and
1844         (not defined $item->{'notforloan'} or $item->{'notforloan'} eq '')) {
1845         $item->{'notforloan'} = 0;
1846     }
1847     if (exists $item->{'damaged'} and
1848         (not defined $item->{'damaged'} or $item->{'damaged'} eq '')) {
1849         $item->{'damaged'} = 0;
1850     }
1851     if (exists $item->{'itemlost'} and
1852         (not defined $item->{'itemlost'} or $item->{'itemlost'} eq '')) {
1853         $item->{'itemlost'} = 0;
1854     }
1855     if (exists $item->{'wthdrawn'} and
1856         (not defined $item->{'wthdrawn'} or $item->{'wthdrawn'} eq '')) {
1857         $item->{'wthdrawn'} = 0;
1858     }
1859     if (exists $item->{'location'} && !exists $item->{'permanent_location'}) {
1860         $item->{'permanent_location'} = $item->{'location'};
1861     }
1862 }
1863
1864 =head2 _get_single_item_column
1865
1866 =over 4
1867
1868 _get_single_item_column($column, $itemnumber);
1869
1870 =back
1871
1872 Retrieves the value of a single column from an C<items>
1873 row specified by C<$itemnumber>.
1874
1875 =cut
1876
1877 sub _get_single_item_column {
1878     my $column = shift;
1879     my $itemnumber = shift;
1880
1881     my $dbh = C4::Context->dbh;
1882     my $sth = $dbh->prepare("SELECT $column FROM items WHERE itemnumber = ?");
1883     $sth->execute($itemnumber);
1884     my ($value) = $sth->fetchrow();
1885     return $value;
1886 }
1887
1888 =head2 _calc_items_cn_sort
1889
1890 =over 4
1891
1892 _calc_items_cn_sort($item, $source_values);
1893
1894 =back
1895
1896 Helper routine to calculate C<items.cn_sort>.
1897
1898 =cut
1899
1900 sub _calc_items_cn_sort {
1901     my $item = shift;
1902     my $source_values = shift;
1903
1904     $item->{'items.cn_sort'} = GetClassSort($source_values->{'items.cn_source'}, $source_values->{'itemcallnumber'}, "");
1905 }
1906
1907 =head2 _set_defaults_for_add
1908
1909 =over 4
1910
1911 _set_defaults_for_add($item_hash);
1912
1913 =back
1914
1915 Given an item hash representing an item to be added, set
1916 correct default values for columns whose default value
1917 is not handled by the DBMS.  This includes the following
1918 columns:
1919
1920 =over 2
1921
1922 =item *
1923
1924 C<items.dateaccessioned>
1925
1926 =item *
1927
1928 C<items.notforloan>
1929
1930 =item *
1931
1932 C<items.damaged>
1933
1934 =item *
1935
1936 C<items.itemlost>
1937
1938 =item *
1939
1940 C<items.wthdrawn>
1941
1942 =back
1943
1944 =cut
1945
1946 sub _set_defaults_for_add {
1947     my $item = shift;
1948     $item->{dateaccessioned} ||= C4::Dates->new->output('iso');
1949     $item->{$_} ||= 0 for (qw( notforloan damaged itemlost wthdrawn));
1950 }
1951
1952 =head2 _koha_new_item
1953
1954 =over 4
1955
1956 my ($itemnumber,$error) = _koha_new_item( $item, $barcode );
1957
1958 =back
1959
1960 Perform the actual insert into the C<items> table.
1961
1962 =cut
1963
1964 sub _koha_new_item {
1965     my ( $item, $barcode ) = @_;
1966     my $dbh=C4::Context->dbh;
1967     my $error;
1968     my $query =
1969            "INSERT INTO items SET
1970             biblionumber        = ?,
1971             biblioitemnumber    = ?,
1972             barcode             = ?,
1973             dateaccessioned     = ?,
1974             booksellerid        = ?,
1975             homebranch          = ?,
1976             price               = ?,
1977             replacementprice    = ?,
1978             replacementpricedate = NOW(),
1979             datelastborrowed    = ?,
1980             datelastseen        = NOW(),
1981             stack               = ?,
1982             notforloan          = ?,
1983             damaged             = ?,
1984             itemlost            = ?,
1985             wthdrawn            = ?,
1986             itemcallnumber      = ?,
1987             restricted          = ?,
1988             itemnotes           = ?,
1989             holdingbranch       = ?,
1990             paidfor             = ?,
1991             location            = ?,
1992             onloan              = ?,
1993             issues              = ?,
1994             renewals            = ?,
1995             reserves            = ?,
1996             cn_source           = ?,
1997             cn_sort             = ?,
1998             ccode               = ?,
1999             itype               = ?,
2000             materials           = ?,
2001             uri = ?,
2002             enumchron           = ?,
2003             more_subfields_xml  = ?,
2004             copynumber          = ?
2005           ";
2006     my $sth = $dbh->prepare($query);
2007    $sth->execute(
2008             $item->{'biblionumber'},
2009             $item->{'biblioitemnumber'},
2010             $barcode,
2011             $item->{'dateaccessioned'},
2012             $item->{'booksellerid'},
2013             $item->{'homebranch'},
2014             $item->{'price'},
2015             $item->{'replacementprice'},
2016             $item->{datelastborrowed},
2017             $item->{stack},
2018             $item->{'notforloan'},
2019             $item->{'damaged'},
2020             $item->{'itemlost'},
2021             $item->{'wthdrawn'},
2022             $item->{'itemcallnumber'},
2023             $item->{'restricted'},
2024             $item->{'itemnotes'},
2025             $item->{'holdingbranch'},
2026             $item->{'paidfor'},
2027             $item->{'location'},
2028             $item->{'onloan'},
2029             $item->{'issues'},
2030             $item->{'renewals'},
2031             $item->{'reserves'},
2032             $item->{'items.cn_source'},
2033             $item->{'items.cn_sort'},
2034             $item->{'ccode'},
2035             $item->{'itype'},
2036             $item->{'materials'},
2037             $item->{'uri'},
2038             $item->{'enumchron'},
2039             $item->{'more_subfields_xml'},
2040             $item->{'copynumber'},
2041     );
2042     my $itemnumber = $dbh->{'mysql_insertid'};
2043     if ( defined $sth->errstr ) {
2044         $error.="ERROR in _koha_new_item $query".$sth->errstr;
2045     }
2046     return ( $itemnumber, $error );
2047 }
2048
2049 =head2 MoveItemFromBiblio
2050
2051 =over 4
2052
2053 MoveItemFromBiblio($itenumber, $frombiblio, $tobiblio);
2054
2055 =back
2056
2057 Moves an item from a biblio to another
2058
2059 Returns undef if the move failed or the biblionumber of the destination record otherwise
2060 =cut
2061 sub MoveItemFromBiblio {
2062     my ($itemnumber, $frombiblio, $tobiblio) = @_;
2063     my $dbh = C4::Context->dbh;
2064     my $sth = $dbh->prepare("SELECT biblioitemnumber FROM biblioitems WHERE biblionumber = ?");
2065     $sth->execute( $tobiblio );
2066     my ( $tobiblioitem ) = $sth->fetchrow();
2067     $sth = $dbh->prepare("UPDATE items SET biblioitemnumber = ?, biblionumber = ? WHERE itemnumber = ? AND biblionumber = ?");
2068     my $return = $sth->execute($tobiblioitem, $tobiblio, $itemnumber, $frombiblio);
2069     if ($return == 1) {
2070
2071         # Getting framework
2072         my $frameworkcode = GetFrameworkCode($frombiblio);
2073
2074         # Getting marc field for itemnumber
2075         my ($itemtag, $itemsubfield) = GetMarcFromKohaField('items.itemnumber', $frameworkcode);
2076
2077         # Getting the record we want to move the item from
2078         my $record = GetMarcBiblio($frombiblio);
2079
2080         # The item we want to move
2081         my $item;
2082
2083         # For each item
2084         foreach my $fielditem ($record->field($itemtag)){
2085                 # If it is the item we want to move
2086                 if ($fielditem->subfield($itemsubfield) == $itemnumber) {
2087                     # We save it
2088                     $item = $fielditem;
2089                     # Then delete it from the record
2090                     $record->delete_field($fielditem)
2091                 }
2092         }
2093
2094         # If we found an item (should always true, except in case of database-marcxml inconsistency)
2095         if ($item) {
2096
2097             # Checking if the item we want to move is in an order
2098             my $order = GetOrderFromItemnumber($itemnumber);
2099             if ($order) {
2100                 # Replacing the biblionumber within the order if necessary
2101                 $order->{'biblionumber'} = $tobiblio;
2102                 ModOrder($order);
2103             }
2104
2105             # Saving the modification
2106             ModBiblioMarc($record, $frombiblio, $frameworkcode);
2107
2108             # Getting the record we want to move the item to
2109             $record = GetMarcBiblio($tobiblio);
2110
2111             # Inserting the previously saved item
2112             $record->insert_fields_ordered($item);
2113
2114             # Saving the modification
2115             ModBiblioMarc($record, $tobiblio, $frameworkcode);
2116
2117         } else {
2118             return undef;
2119         }
2120     } else {
2121         return undef;
2122     }
2123 }
2124
2125 =head2 DelItemCheck
2126
2127 =over 4
2128
2129 DelItemCheck($dbh, $biblionumber, $itemnumber);
2130
2131 =back
2132
2133 Exported function (core API) for deleting an item record in Koha if there no current issue.
2134
2135 =cut
2136
2137 sub DelItemCheck {
2138     my ( $dbh, $biblionumber, $itemnumber ) = @_;
2139     my $error;
2140
2141     # check that there is no issue on this item before deletion.
2142     my $sth=$dbh->prepare("select * from issues i where i.itemnumber=?");
2143     $sth->execute($itemnumber);
2144
2145     my $onloan=$sth->fetchrow;
2146
2147     if ($onloan){
2148         $error = "book_on_loan"
2149     }else{
2150         # check it doesnt have a waiting reserve
2151         $sth=$dbh->prepare("SELECT * FROM reserves WHERE found = 'W' AND itemnumber = ?");
2152         $sth->execute($itemnumber);
2153         my $reserve=$sth->fetchrow;
2154         if ($reserve){
2155             $error = "book_reserved";
2156         }else{
2157             DelItem($dbh, $biblionumber, $itemnumber);
2158             return 1;
2159         }
2160     }
2161     return $error;
2162 }
2163
2164 =head2 _koha_modify_item
2165
2166 =over 4
2167
2168 my ($itemnumber,$error) =_koha_modify_item( $item );
2169
2170 =back
2171
2172 Perform the actual update of the C<items> row.  Note that this
2173 routine accepts a hashref specifying the columns to update.
2174
2175 =cut
2176
2177 sub _koha_modify_item {
2178     my ( $item ) = @_;
2179     my $dbh=C4::Context->dbh;
2180     my $error;
2181
2182     my $query = "UPDATE items SET ";
2183     my @bind;
2184     for my $key ( keys %$item ) {
2185         $query.="$key=?,";
2186         push @bind, $item->{$key};
2187     }
2188     $query =~ s/,$//;
2189     $query .= " WHERE itemnumber=?";
2190     push @bind, $item->{'itemnumber'};
2191     my $sth = C4::Context->dbh->prepare($query);
2192     $sth->execute(@bind);
2193     if ( C4::Context->dbh->errstr ) {
2194         $error.="ERROR in _koha_modify_item $query".$dbh->errstr;
2195         warn $error;
2196     }
2197     return ($item->{'itemnumber'},$error);
2198 }
2199
2200 =head2 _koha_delete_item
2201
2202 =over 4
2203
2204 _koha_delete_item( $dbh, $itemnum );
2205
2206 =back
2207
2208 Internal function to delete an item record from the koha tables
2209
2210 =cut
2211
2212 sub _koha_delete_item {
2213     my ( $dbh, $itemnum ) = @_;
2214
2215     # save the deleted item to deleteditems table
2216     my $sth = $dbh->prepare("SELECT * FROM items WHERE itemnumber=?");
2217     $sth->execute($itemnum);
2218     my $data = $sth->fetchrow_hashref();
2219     my $query = "INSERT INTO deleteditems SET ";
2220     my @bind  = ();
2221     foreach my $key ( keys %$data ) {
2222         $query .= "$key = ?,";
2223         push( @bind, $data->{$key} );
2224     }
2225     $query =~ s/\,$//;
2226     $sth = $dbh->prepare($query);
2227     $sth->execute(@bind);
2228
2229     # delete from items table
2230     $sth = $dbh->prepare("DELETE FROM items WHERE itemnumber=?");
2231     $sth->execute($itemnum);
2232     return undef;
2233 }
2234
2235 =head2 _marc_from_item_hash
2236
2237 =over 4
2238
2239 my $item_marc = _marc_from_item_hash($item, $frameworkcode[, $unlinked_item_subfields]);
2240
2241 =back
2242
2243 Given an item hash representing a complete item record,
2244 create a C<MARC::Record> object containing an embedded
2245 tag representing that item.
2246
2247 The third, optional parameter C<$unlinked_item_subfields> is
2248 an arrayref of subfields (not mapped to C<items> fields per the
2249 framework) to be added to the MARC representation
2250 of the item.
2251
2252 =cut
2253
2254 sub _marc_from_item_hash {
2255     my $item = shift;
2256     my $frameworkcode = shift;
2257     my $unlinked_item_subfields;
2258     if (@_) {
2259         $unlinked_item_subfields = shift;
2260     }
2261
2262     # Tack on 'items.' prefix to column names so lookup from MARC frameworks will work
2263     # Also, don't emit a subfield if the underlying field is blank.
2264     my $mungeditem = { map {  (defined($item->{$_}) and $item->{$_} ne '') ?
2265                                 (/^items\./ ? ($_ => $item->{$_}) : ("items.$_" => $item->{$_}))
2266                                 : ()  } keys %{ $item } };
2267
2268     my $item_marc = MARC::Record->new();
2269     foreach my $item_field (keys %{ $mungeditem }) {
2270         my ($tag, $subfield) = GetMarcFromKohaField($item_field, $frameworkcode);
2271         next unless defined $tag and defined $subfield; # skip if not mapped to MARC field
2272         if (my $field = $item_marc->field($tag)) {
2273             $field->add_subfields($subfield => $mungeditem->{$item_field});
2274         } else {
2275             my $add_subfields = [];
2276             if (defined $unlinked_item_subfields and ref($unlinked_item_subfields) eq 'ARRAY' and $#$unlinked_item_subfields > -1) {
2277                 $add_subfields = $unlinked_item_subfields;
2278             }
2279             $item_marc->add_fields( $tag, " ", " ", $subfield =>  $mungeditem->{$item_field}, @$add_subfields);
2280         }
2281     }
2282
2283     return $item_marc;
2284 }
2285
2286 =head2 _add_item_field_to_biblio
2287
2288 =over 4
2289
2290 _add_item_field_to_biblio($item_marc, $biblionumber, $frameworkcode);
2291
2292 =back
2293
2294 Adds the fields from a MARC record containing the
2295 representation of a Koha item record to the MARC
2296 biblio record.  The input C<$item_marc> record
2297 is expect to contain just one field, the embedded
2298 item information field.
2299
2300 =cut
2301
2302 sub _add_item_field_to_biblio {
2303     my ($item_marc, $biblionumber, $frameworkcode) = @_;
2304
2305     my $biblio_marc = GetMarcBiblio($biblionumber);
2306     foreach my $field ($item_marc->fields()) {
2307         $biblio_marc->append_fields($field);
2308     }
2309
2310     ModBiblioMarc($biblio_marc, $biblionumber, $frameworkcode);
2311 }
2312
2313 =head2 _replace_item_field_in_biblio
2314
2315 =over
2316
2317 &_replace_item_field_in_biblio($item_marc, $biblionumber, $itemnumber, $frameworkcode)
2318
2319 =back
2320
2321 Given a MARC::Record C<$item_marc> containing one tag with the MARC
2322 representation of the item, examine the biblio MARC
2323 for the corresponding tag for that item and
2324 replace it with the tag from C<$item_marc>.
2325
2326 =cut
2327
2328 sub _replace_item_field_in_biblio {
2329     my ($ItemRecord, $biblionumber, $itemnumber, $frameworkcode) = @_;
2330     my $dbh = C4::Context->dbh;
2331
2332     # get complete MARC record & replace the item field by the new one
2333     my $completeRecord = GetMarcBiblio($biblionumber);
2334     my ($itemtag,$itemsubfield) = GetMarcFromKohaField("items.itemnumber",$frameworkcode);
2335     my $itemField = $ItemRecord->field($itemtag);
2336     my @items = $completeRecord->field($itemtag);
2337     my $found = 0;
2338     foreach (@items) {
2339         if ($_->subfield($itemsubfield) eq $itemnumber) {
2340             $_->replace_with($itemField);
2341             $found = 1;
2342         }
2343     }
2344
2345     unless ($found) {
2346         # If we haven't found the matching field,
2347         # just add it.  However, this means that
2348         # there is likely a bug.
2349         $completeRecord->append_fields($itemField);
2350     }
2351
2352     # save the record
2353     ModBiblioMarc($completeRecord, $biblionumber, $frameworkcode);
2354 }
2355
2356 =head2 _repack_item_errors
2357
2358 Add an error message hash generated by C<CheckItemPreSave>
2359 to a list of errors.
2360
2361 =cut
2362
2363 sub _repack_item_errors {
2364     my $item_sequence_num = shift;
2365     my $item_ref = shift;
2366     my $error_ref = shift;
2367
2368     my @repacked_errors = ();
2369
2370     foreach my $error_code (sort keys %{ $error_ref }) {
2371         my $repacked_error = {};
2372         $repacked_error->{'item_sequence'} = $item_sequence_num;
2373         $repacked_error->{'item_barcode'} = exists($item_ref->{'barcode'}) ? $item_ref->{'barcode'} : '';
2374         $repacked_error->{'error_code'} = $error_code;
2375         $repacked_error->{'error_information'} = $error_ref->{$error_code};
2376         push @repacked_errors, $repacked_error;
2377     }
2378
2379     return @repacked_errors;
2380 }
2381
2382 =head2 _get_unlinked_item_subfields
2383
2384 =over 4
2385
2386 my $unlinked_item_subfields = _get_unlinked_item_subfields($original_item_marc, $frameworkcode);
2387
2388 =back
2389
2390 =cut
2391
2392 sub _get_unlinked_item_subfields {
2393     my $original_item_marc = shift;
2394     my $frameworkcode = shift;
2395
2396     my $marcstructure = GetMarcStructure(1, $frameworkcode);
2397
2398     # assume that this record has only one field, and that that
2399     # field contains only the item information
2400     my $subfields = [];
2401     my @fields = $original_item_marc->fields();
2402     if ($#fields > -1) {
2403         my $field = $fields[0];
2404             my $tag = $field->tag();
2405         foreach my $subfield ($field->subfields()) {
2406             if (defined $subfield->[1] and
2407                 $subfield->[1] ne '' and
2408                 !$marcstructure->{$tag}->{$subfield->[0]}->{'kohafield'}) {
2409                 push @$subfields, $subfield->[0] => $subfield->[1];
2410             }
2411         }
2412     }
2413     return $subfields;
2414 }
2415
2416 =head2 _get_unlinked_subfields_xml
2417
2418 =over 4
2419
2420 my $unlinked_subfields_xml = _get_unlinked_subfields_xml($unlinked_item_subfields);
2421
2422 =back
2423
2424 =cut
2425
2426 sub _get_unlinked_subfields_xml {
2427     my $unlinked_item_subfields = shift;
2428
2429     my $xml;
2430     if (defined $unlinked_item_subfields and ref($unlinked_item_subfields) eq 'ARRAY' and $#$unlinked_item_subfields > -1) {
2431         my $marc = MARC::Record->new();
2432         # use of tag 999 is arbitrary, and doesn't need to match the item tag
2433         # used in the framework
2434         $marc->append_fields(MARC::Field->new('999', ' ', ' ', @$unlinked_item_subfields));
2435         $marc->encoding("UTF-8");
2436         $xml = $marc->as_xml("USMARC");
2437     }
2438
2439     return $xml;
2440 }
2441
2442 =head2 _parse_unlinked_item_subfields_from_xml
2443
2444 =over 4
2445
2446 my $unlinked_item_subfields = _parse_unlinked_item_subfields_from_xml($whole_item->{'more_subfields_xml'}):
2447
2448 =back
2449
2450 =cut
2451
2452 sub  _parse_unlinked_item_subfields_from_xml {
2453     my $xml = shift;
2454
2455     return unless defined $xml and $xml ne "";
2456     my $marc = MARC::Record->new_from_xml(StripNonXmlChars($xml),'UTF-8');
2457     my $unlinked_subfields = [];
2458     my @fields = $marc->fields();
2459     if ($#fields > -1) {
2460         foreach my $subfield ($fields[0]->subfields()) {
2461             push @$unlinked_subfields, $subfield->[0] => $subfield->[1];
2462         }
2463     }
2464     return $unlinked_subfields;
2465 }
2466
2467 1;