3 # Copyright 2007 LibLime, Inc.
4 # Parts Copyright Biblibre 2010
6 # This file is part of Koha.
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.
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.
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>.
22 #use warnings; FIXME - Bug 2505
32 use List::MoreUtils qw/any/;
34 use DateTime::Format::MySQL;
35 use Data::Dumper; # used as part of logging item record changes, not just for
36 # debugging; so please don't remove this
37 use Koha::DateUtils qw/dt_from_string/;
41 use Koha::SearchEngine;
42 use Koha::SearchEngine::Search;
44 use vars qw(@ISA @EXPORT);
49 @ISA = qw( Exporter );
72 GetItemsByBiblioitemnumber
76 GetItemnumbersForBiblio
78 get_hostitemnumbers_of
79 GetItemnumberFromBarcode
80 GetBarcodeFromItemnumber
95 PrepareItemrecordDisplay
102 C4::Items - item management functions
106 This module contains an API for manipulating item
107 records in Koha, and is used by cataloguing, circulation,
108 acquisitions, and serials management.
110 A Koha item record is stored in two places: the
111 items table and embedded in a MARC tag in the XML
112 version of the associated bib record in C<biblioitems.marcxml>.
113 This is done to allow the item information to be readily
114 indexed (e.g., by Zebra), but means that each item
115 modification transaction must keep the items table
116 and the MARC XML in sync at all times.
118 Consequently, all code that creates, modifies, or deletes
119 item records B<must> use an appropriate function from
120 C<C4::Items>. If no existing function is suitable, it is
121 better to add one to C<C4::Items> than to use add
122 one-off SQL statements to add or modify items.
124 The items table will be considered authoritative. In other
125 words, if there is ever a discrepancy between the items
126 table and the MARC XML, the items table should be considered
129 =head1 HISTORICAL NOTE
131 Most of the functions in C<C4::Items> were originally in
132 the C<C4::Biblio> module.
134 =head1 CORE EXPORTED FUNCTIONS
136 The following functions are meant for use by users
143 $item = GetItem($itemnumber,$barcode,$serial);
145 Return item information, for a given itemnumber or barcode.
146 The return value is a hashref mapping item column
147 names to values. If C<$serial> is true, include serial publication data.
152 my ($itemnumber,$barcode, $serial) = @_;
153 my $dbh = C4::Context->dbh;
157 my $sth = $dbh->prepare("
159 WHERE itemnumber = ?");
160 $sth->execute($itemnumber);
161 $data = $sth->fetchrow_hashref;
163 my $sth = $dbh->prepare("
167 $sth->execute($barcode);
168 $data = $sth->fetchrow_hashref;
171 return unless ( $data );
174 my $ssth = $dbh->prepare("SELECT serialseq,publisheddate from serialitems left join serial on serialitems.serialid=serial.serialid where serialitems.itemnumber=?");
175 $ssth->execute($data->{'itemnumber'}) ;
176 ($data->{'serialseq'} , $data->{'publisheddate'}) = $ssth->fetchrow_array();
178 #if we don't have an items.itype, use biblioitems.itemtype.
179 # FIXME this should respect the itypes systempreference
180 # if (C4::Context->preference('item-level_itypes')) {
181 if( ! $data->{'itype'} ) {
182 my $sth = $dbh->prepare("SELECT itemtype FROM biblioitems WHERE biblionumber = ?");
183 $sth->execute($data->{'biblionumber'});
184 ($data->{'itype'}) = $sth->fetchrow_array;
191 CartToShelf($itemnumber);
193 Set the current shelving location of the item record
194 to its stored permanent shelving location. This is
195 primarily used to indicate when an item whose current
196 location is a special processing ('PROC') or shelving cart
197 ('CART') location is back in the stacks.
202 my ( $itemnumber ) = @_;
204 unless ( $itemnumber ) {
205 croak "FAILED CartToShelf() - no itemnumber supplied";
208 my $item = GetItem($itemnumber);
209 if ( $item->{location} eq 'CART' ) {
210 $item->{location} = $item->{permanent_location};
211 ModItem($item, undef, $itemnumber);
217 ShelfToCart($itemnumber);
219 Set the current shelving location of the item
220 to shelving cart ('CART').
225 my ( $itemnumber ) = @_;
227 unless ( $itemnumber ) {
228 croak "FAILED ShelfToCart() - no itemnumber supplied";
231 my $item = GetItem($itemnumber);
232 $item->{'location'} = 'CART';
233 ModItem($item, undef, $itemnumber);
236 =head2 AddItemFromMarc
238 my ($biblionumber, $biblioitemnumber, $itemnumber)
239 = AddItemFromMarc($source_item_marc, $biblionumber);
241 Given a MARC::Record object containing an embedded item
242 record and a biblionumber, create a new item record.
246 sub AddItemFromMarc {
247 my ( $source_item_marc, $biblionumber ) = @_;
248 my $dbh = C4::Context->dbh;
250 # parse item hash from MARC
251 my $frameworkcode = GetFrameworkCode( $biblionumber );
252 my ($itemtag,$itemsubfield)=GetMarcFromKohaField("items.itemnumber",$frameworkcode);
254 my $localitemmarc=MARC::Record->new;
255 $localitemmarc->append_fields($source_item_marc->field($itemtag));
256 my $item = &TransformMarcToKoha( $localitemmarc, $frameworkcode ,'items');
257 my $unlinked_item_subfields = _get_unlinked_item_subfields($localitemmarc, $frameworkcode);
258 return AddItem($item, $biblionumber, $dbh, $frameworkcode, $unlinked_item_subfields);
263 my ($biblionumber, $biblioitemnumber, $itemnumber)
264 = AddItem($item, $biblionumber[, $dbh, $frameworkcode, $unlinked_item_subfields]);
266 Given a hash containing item column names as keys,
267 create a new Koha item record.
269 The first two optional parameters (C<$dbh> and C<$frameworkcode>)
270 do not need to be supplied for general use; they exist
271 simply to allow them to be picked up from AddItemFromMarc.
273 The final optional parameter, C<$unlinked_item_subfields>, contains
274 an arrayref containing subfields present in the original MARC
275 representation of the item (e.g., from the item editor) that are
276 not mapped to C<items> columns directly but should instead
277 be stored in C<items.more_subfields_xml> and included in
278 the biblio items tag for display and indexing.
284 my $biblionumber = shift;
286 my $dbh = @_ ? shift : C4::Context->dbh;
287 my $frameworkcode = @_ ? shift : GetFrameworkCode($biblionumber);
288 my $unlinked_item_subfields;
290 $unlinked_item_subfields = shift;
293 # needs old biblionumber and biblioitemnumber
294 $item->{'biblionumber'} = $biblionumber;
295 my $sth = $dbh->prepare("SELECT biblioitemnumber FROM biblioitems WHERE biblionumber=?");
296 $sth->execute( $item->{'biblionumber'} );
297 ( $item->{'biblioitemnumber'} ) = $sth->fetchrow;
299 _set_defaults_for_add($item);
300 _set_derived_columns_for_add($item);
301 $item->{'more_subfields_xml'} = _get_unlinked_subfields_xml($unlinked_item_subfields);
303 # FIXME - checks here
304 unless ( $item->{itype} ) { # default to biblioitem.itemtype if no itype
305 my $itype_sth = $dbh->prepare("SELECT itemtype FROM biblioitems WHERE biblionumber = ?");
306 $itype_sth->execute( $item->{'biblionumber'} );
307 ( $item->{'itype'} ) = $itype_sth->fetchrow_array;
310 my ( $itemnumber, $error ) = _koha_new_item( $item, $item->{barcode} );
313 $item->{'itemnumber'} = $itemnumber;
315 ModZebra( $item->{biblionumber}, "specialUpdate", "biblioserver" );
317 logaction( "CATALOGUING", "ADD", $itemnumber, "item" )
318 if C4::Context->preference("CataloguingLog");
320 return ( $item->{biblionumber}, $item->{biblioitemnumber}, $itemnumber );
323 =head2 AddItemBatchFromMarc
325 ($itemnumber_ref, $error_ref) = AddItemBatchFromMarc($record,
326 $biblionumber, $biblioitemnumber, $frameworkcode);
328 Efficiently create item records from a MARC biblio record with
329 embedded item fields. This routine is suitable for batch jobs.
331 This API assumes that the bib record has already been
332 saved to the C<biblio> and C<biblioitems> tables. It does
333 not expect that C<biblioitems.marc> and C<biblioitems.marcxml>
334 are populated, but it will do so via a call to ModBibiloMarc.
336 The goal of this API is to have a similar effect to using AddBiblio
337 and AddItems in succession, but without inefficient repeated
338 parsing of the MARC XML bib record.
340 This function returns an arrayref of new itemsnumbers and an arrayref of item
341 errors encountered during the processing. Each entry in the errors
342 list is a hashref containing the following keys:
348 Sequence number of original item tag in the MARC record.
352 Item barcode, provide to assist in the construction of
353 useful error messages.
357 Code representing the error condition. Can be 'duplicate_barcode',
358 'invalid_homebranch', or 'invalid_holdingbranch'.
360 =item error_information
362 Additional information appropriate to the error condition.
368 sub AddItemBatchFromMarc {
369 my ($record, $biblionumber, $biblioitemnumber, $frameworkcode) = @_;
371 my @itemnumbers = ();
373 my $dbh = C4::Context->dbh;
375 # We modify the record, so lets work on a clone so we don't change the
377 $record = $record->clone();
378 # loop through the item tags and start creating items
379 my @bad_item_fields = ();
380 my ($itemtag, $itemsubfield) = &GetMarcFromKohaField("items.itemnumber",'');
381 my $item_sequence_num = 0;
382 ITEMFIELD: foreach my $item_field ($record->field($itemtag)) {
383 $item_sequence_num++;
384 # we take the item field and stick it into a new
385 # MARC record -- this is required so far because (FIXME)
386 # TransformMarcToKoha requires a MARC::Record, not a MARC::Field
387 # and there is no TransformMarcFieldToKoha
388 my $temp_item_marc = MARC::Record->new();
389 $temp_item_marc->append_fields($item_field);
391 # add biblionumber and biblioitemnumber
392 my $item = TransformMarcToKoha( $temp_item_marc, $frameworkcode, 'items' );
393 my $unlinked_item_subfields = _get_unlinked_item_subfields($temp_item_marc, $frameworkcode);
394 $item->{'more_subfields_xml'} = _get_unlinked_subfields_xml($unlinked_item_subfields);
395 $item->{'biblionumber'} = $biblionumber;
396 $item->{'biblioitemnumber'} = $biblioitemnumber;
398 # check for duplicate barcode
399 my %item_errors = CheckItemPreSave($item);
401 push @errors, _repack_item_errors($item_sequence_num, $item, \%item_errors);
402 push @bad_item_fields, $item_field;
406 _set_defaults_for_add($item);
407 _set_derived_columns_for_add($item);
408 my ( $itemnumber, $error ) = _koha_new_item( $item, $item->{barcode} );
409 warn $error if $error;
410 push @itemnumbers, $itemnumber; # FIXME not checking error
411 $item->{'itemnumber'} = $itemnumber;
413 logaction("CATALOGUING", "ADD", $itemnumber, "item") if C4::Context->preference("CataloguingLog");
415 my $new_item_marc = _marc_from_item_hash($item, $frameworkcode, $unlinked_item_subfields);
416 $item_field->replace_with($new_item_marc->field($itemtag));
419 # remove any MARC item fields for rejected items
420 foreach my $item_field (@bad_item_fields) {
421 $record->delete_field($item_field);
424 # update the MARC biblio
425 # $biblionumber = ModBiblioMarc( $record, $biblionumber, $frameworkcode );
427 return (\@itemnumbers, \@errors);
430 =head2 ModItemFromMarc
432 ModItemFromMarc($item_marc, $biblionumber, $itemnumber);
434 This function updates an item record based on a supplied
435 C<MARC::Record> object containing an embedded item field.
436 This API is meant for the use of C<additem.pl>; for
437 other purposes, C<ModItem> should be used.
439 This function uses the hash %default_values_for_mod_from_marc,
440 which contains default values for item fields to
441 apply when modifying an item. This is needed because
442 if an item field's value is cleared, TransformMarcToKoha
443 does not include the column in the
444 hash that's passed to ModItem, which without
445 use of this hash makes it impossible to clear
446 an item field's value. See bug 2466.
448 Note that only columns that can be directly
449 changed from the cataloging and serials
450 item editors are included in this hash.
456 sub _build_default_values_for_mod_marc {
457 my ($frameworkcode) = @_;
459 my $cache = Koha::Cache->get_instance();
460 my $cache_key = "default_value_for_mod_marc-$frameworkcode";
461 my $cached = $cache->get_from_cache($cache_key);
462 return $cached if $cached;
464 my $default_values = {
466 booksellerid => undef,
468 'items.cn_source' => undef,
469 coded_location_qualifier => undef,
473 holdingbranch => undef,
475 itemcallnumber => undef,
478 itemnotes_nonpublic => undef,
481 permanent_location => undef,
485 # paidfor => undef, # commented, see bug 12817
487 replacementprice => undef,
488 replacementpricedate => undef,
491 stocknumber => undef,
495 my %default_values_for_mod_from_marc;
496 while ( my ( $field, $default_value ) = each %$default_values ) {
497 my $kohafield = $field;
498 $kohafield =~ s|^([^\.]+)$|items.$1|;
499 $default_values_for_mod_from_marc{$field} =
501 if C4::Koha::IsKohaFieldLinked(
502 { kohafield => $kohafield, frameworkcode => $frameworkcode } );
505 $cache->set_in_cache($cache_key, \%default_values_for_mod_from_marc);
506 return \%default_values_for_mod_from_marc;
509 sub ModItemFromMarc {
510 my $item_marc = shift;
511 my $biblionumber = shift;
512 my $itemnumber = shift;
514 my $dbh = C4::Context->dbh;
515 my $frameworkcode = GetFrameworkCode($biblionumber);
516 my ( $itemtag, $itemsubfield ) = GetMarcFromKohaField( "items.itemnumber", $frameworkcode );
518 my $localitemmarc = MARC::Record->new;
519 $localitemmarc->append_fields( $item_marc->field($itemtag) );
520 my $item = &TransformMarcToKoha( $localitemmarc, $frameworkcode, 'items' );
521 my $default_values = _build_default_values_for_mod_marc($frameworkcode);
522 foreach my $item_field ( keys %$default_values ) {
523 $item->{$item_field} = $default_values->{$item_field}
524 unless exists $item->{$item_field};
526 my $unlinked_item_subfields = _get_unlinked_item_subfields( $localitemmarc, $frameworkcode );
528 ModItem($item, $biblionumber, $itemnumber, $dbh, $frameworkcode, $unlinked_item_subfields);
534 ModItem({ column => $newvalue }, $biblionumber, $itemnumber);
536 Change one or more columns in an item record and update
537 the MARC representation of the item.
539 The first argument is a hashref mapping from item column
540 names to the new values. The second and third arguments
541 are the biblionumber and itemnumber, respectively.
543 The fourth, optional parameter, C<$unlinked_item_subfields>, contains
544 an arrayref containing subfields present in the original MARC
545 representation of the item (e.g., from the item editor) that are
546 not mapped to C<items> columns directly but should instead
547 be stored in C<items.more_subfields_xml> and included in
548 the biblio items tag for display and indexing.
550 If one of the changed columns is used to calculate
551 the derived value of a column such as C<items.cn_sort>,
552 this routine will perform the necessary calculation
559 my $biblionumber = shift;
560 my $itemnumber = shift;
562 # if $biblionumber is undefined, get it from the current item
563 unless (defined $biblionumber) {
564 $biblionumber = _get_single_item_column('biblionumber', $itemnumber);
567 my $dbh = @_ ? shift : C4::Context->dbh;
568 my $frameworkcode = @_ ? shift : GetFrameworkCode( $biblionumber );
570 my $unlinked_item_subfields;
572 $unlinked_item_subfields = shift;
573 $item->{'more_subfields_xml'} = _get_unlinked_subfields_xml($unlinked_item_subfields);
576 $item->{'itemnumber'} = $itemnumber or return;
578 my @fields = qw( itemlost withdrawn );
580 # Only call GetItem if we need to set an "on" date field
581 if ( $item->{itemlost} || $item->{withdrawn} ) {
582 my $pre_mod_item = GetItem( $item->{'itemnumber'} );
583 for my $field (@fields) {
584 if ( defined( $item->{$field} )
585 and not $pre_mod_item->{$field}
586 and $item->{$field} )
588 $item->{ $field . '_on' } =
589 DateTime::Format::MySQL->format_datetime( dt_from_string() );
594 # If the field is defined but empty, we are removing and,
595 # and thus need to clear out the 'on' field as well
596 for my $field (@fields) {
597 if ( defined( $item->{$field} ) && !$item->{$field} ) {
598 $item->{ $field . '_on' } = undef;
603 _set_derived_columns_for_mod($item);
604 _do_column_fixes_for_mod($item);
607 # attempt to change itemnumber
608 # attempt to change biblionumber (if we want
609 # an API to relink an item to a different bib,
610 # it should be a separate function)
613 _koha_modify_item($item);
615 # request that bib be reindexed so that searching on current
616 # item status is possible
617 ModZebra( $biblionumber, "specialUpdate", "biblioserver" );
619 logaction("CATALOGUING", "MODIFY", $itemnumber, "item ".Dumper($item)) if C4::Context->preference("CataloguingLog");
622 =head2 ModItemTransfer
624 ModItemTransfer($itenumber, $frombranch, $tobranch);
626 Marks an item as being transferred from one branch
631 sub ModItemTransfer {
632 my ( $itemnumber, $frombranch, $tobranch ) = @_;
634 my $dbh = C4::Context->dbh;
636 # Remove the 'shelving cart' location status if it is being used.
637 CartToShelf( $itemnumber ) if ( C4::Context->preference("ReturnToShelvingCart") );
639 #new entry in branchtransfers....
640 my $sth = $dbh->prepare(
641 "INSERT INTO branchtransfers (itemnumber, frombranch, datesent, tobranch)
642 VALUES (?, ?, NOW(), ?)");
643 $sth->execute($itemnumber, $frombranch, $tobranch);
645 ModItem({ holdingbranch => $tobranch }, undef, $itemnumber);
646 ModDateLastSeen($itemnumber);
650 =head2 ModDateLastSeen
652 ModDateLastSeen($itemnum);
654 Mark item as seen. Is called when an item is issued, returned or manually marked during inventory/stocktaking.
655 C<$itemnum> is the item number
659 sub ModDateLastSeen {
660 my ($itemnumber) = @_;
662 my $today = output_pref({ dt => dt_from_string, dateformat => 'iso', dateonly => 1 });
663 ModItem({ itemlost => 0, datelastseen => $today }, undef, $itemnumber);
668 DelItem({ itemnumber => $itemnumber, [ biblionumber => $biblionumber ] } );
670 Exported function (core API) for deleting an item record in Koha.
677 my $itemnumber = $params->{itemnumber};
678 my $biblionumber = $params->{biblionumber};
680 unless ($biblionumber) {
681 $biblionumber = C4::Biblio::GetBiblionumberFromItemnumber($itemnumber);
684 # If there is no biblionumber for the given itemnumber, there is nothing to delete
685 return 0 unless $biblionumber;
687 # FIXME check the item has no current issues
688 my $deleted = _koha_delete_item( $itemnumber );
690 # get the MARC record
691 my $record = GetMarcBiblio($biblionumber);
692 ModZebra( $biblionumber, "specialUpdate", "biblioserver" );
694 #search item field code
695 logaction("CATALOGUING", "DELETE", $itemnumber, "item") if C4::Context->preference("CataloguingLog");
699 =head2 CheckItemPreSave
701 my $item_ref = TransformMarcToKoha($marc, 'items');
703 my %errors = CheckItemPreSave($item_ref);
704 if (exists $errors{'duplicate_barcode'}) {
705 print "item has duplicate barcode: ", $errors{'duplicate_barcode'}, "\n";
706 } elsif (exists $errors{'invalid_homebranch'}) {
707 print "item has invalid home branch: ", $errors{'invalid_homebranch'}, "\n";
708 } elsif (exists $errors{'invalid_holdingbranch'}) {
709 print "item has invalid holding branch: ", $errors{'invalid_holdingbranch'}, "\n";
714 Given a hashref containing item fields, determine if it can be
715 inserted or updated in the database. Specifically, checks for
716 database integrity issues, and returns a hash containing any
717 of the following keys, if applicable.
721 =item duplicate_barcode
723 Barcode, if it duplicates one already found in the database.
725 =item invalid_homebranch
727 Home branch, if not defined in branches table.
729 =item invalid_holdingbranch
731 Holding branch, if not defined in branches table.
735 This function does NOT implement any policy-related checks,
736 e.g., whether current operator is allowed to save an
737 item that has a given branch code.
741 sub CheckItemPreSave {
742 my $item_ref = shift;
747 # check for duplicate barcode
748 if (exists $item_ref->{'barcode'} and defined $item_ref->{'barcode'}) {
749 my $existing_itemnumber = GetItemnumberFromBarcode($item_ref->{'barcode'});
750 if ($existing_itemnumber) {
751 if (!exists $item_ref->{'itemnumber'} # new item
752 or $item_ref->{'itemnumber'} != $existing_itemnumber) { # existing item
753 $errors{'duplicate_barcode'} = $item_ref->{'barcode'};
758 # check for valid home branch
759 if (exists $item_ref->{'homebranch'} and defined $item_ref->{'homebranch'}) {
760 my $branch_name = C4::Branch::GetBranchName($item_ref->{'homebranch'});
761 unless (defined $branch_name) {
762 # relies on fact that branches.branchname is a non-NULL column,
763 # so GetBranchName returns undef only if branch does not exist
764 $errors{'invalid_homebranch'} = $item_ref->{'homebranch'};
768 # check for valid holding branch
769 if (exists $item_ref->{'holdingbranch'} and defined $item_ref->{'holdingbranch'}) {
770 my $branch_name = C4::Branch::GetBranchName($item_ref->{'holdingbranch'});
771 unless (defined $branch_name) {
772 # relies on fact that branches.branchname is a non-NULL column,
773 # so GetBranchName returns undef only if branch does not exist
774 $errors{'invalid_holdingbranch'} = $item_ref->{'holdingbranch'};
782 =head1 EXPORTED SPECIAL ACCESSOR FUNCTIONS
784 The following functions provide various ways of
785 getting an item record, a set of item records, or
786 lists of authorized values for certain item fields.
788 Some of the functions in this group are candidates
789 for refactoring -- for example, some of the code
790 in C<GetItemsByBiblioitemnumber> and C<GetItemsInfo>
791 has copy-and-paste work.
797 $itemstatushash = GetItemStatus($fwkcode);
799 Returns a list of valid values for the
800 C<items.notforloan> field.
802 NOTE: does B<not> return an individual item's
805 Can be MARC dependent.
807 But basically could be can be loan or not
808 Create a status selector with the following code
810 =head3 in PERL SCRIPT
812 my $itemstatushash = getitemstatus;
814 foreach my $thisstatus (keys %$itemstatushash) {
815 my %row =(value => $thisstatus,
816 statusname => $itemstatushash->{$thisstatus}->{'statusname'},
818 push @itemstatusloop, \%row;
820 $template->param(statusloop=>\@itemstatusloop);
824 <select name="statusloop" id="statusloop">
825 <option value="">Default</option>
826 [% FOREACH statusloo IN statusloop %]
827 [% IF ( statusloo.selected ) %]
828 <option value="[% statusloo.value %]" selected="selected">[% statusloo.statusname %]</option>
830 <option value="[% statusloo.value %]">[% statusloo.statusname %]</option>
839 # returns a reference to a hash of references to status...
842 my $dbh = C4::Context->dbh;
844 $fwk = '' unless ($fwk);
845 my ( $tag, $subfield ) =
846 GetMarcFromKohaField( "items.notforloan", $fwk );
847 if ( $tag and $subfield ) {
850 "SELECT authorised_value
851 FROM marc_subfield_structure
857 $sth->execute( $tag, $subfield, $fwk );
858 if ( my ($authorisedvaluecat) = $sth->fetchrow ) {
861 "SELECT authorised_value,lib
862 FROM authorised_values
867 $authvalsth->execute($authorisedvaluecat);
868 while ( my ( $authorisedvalue, $lib ) = $authvalsth->fetchrow ) {
869 $itemstatus{$authorisedvalue} = $lib;
882 $itemstatus{"1"} = "Not For Loan";
886 =head2 GetItemLocation
888 $itemlochash = GetItemLocation($fwk);
890 Returns a list of valid values for the
891 C<items.location> field.
893 NOTE: does B<not> return an individual item's
896 where fwk stands for an optional framework code.
897 Create a location selector with the following code
899 =head3 in PERL SCRIPT
901 my $itemlochash = getitemlocation;
903 foreach my $thisloc (keys %$itemlochash) {
904 my $selected = 1 if $thisbranch eq $branch;
905 my %row =(locval => $thisloc,
906 selected => $selected,
907 locname => $itemlochash->{$thisloc},
909 push @itemlocloop, \%row;
911 $template->param(itemlocationloop => \@itemlocloop);
915 <select name="location">
916 <option value="">Default</option>
917 <!-- TMPL_LOOP name="itemlocationloop" -->
918 <option value="<!-- TMPL_VAR name="locval" -->" <!-- TMPL_IF name="selected" -->selected<!-- /TMPL_IF -->><!-- TMPL_VAR name="locname" --></option>
924 sub GetItemLocation {
926 # returns a reference to a hash of references to location...
929 my $dbh = C4::Context->dbh;
931 $fwk = '' unless ($fwk);
932 my ( $tag, $subfield ) =
933 GetMarcFromKohaField( "items.location", $fwk );
934 if ( $tag and $subfield ) {
937 "SELECT authorised_value
938 FROM marc_subfield_structure
943 $sth->execute( $tag, $subfield, $fwk );
944 if ( my ($authorisedvaluecat) = $sth->fetchrow ) {
947 "SELECT authorised_value,lib
948 FROM authorised_values
952 $authvalsth->execute($authorisedvaluecat);
953 while ( my ( $authorisedvalue, $lib ) = $authvalsth->fetchrow ) {
954 $itemlocation{$authorisedvalue} = $lib;
956 return \%itemlocation;
967 $itemlocation{"1"} = "Not For Loan";
968 return \%itemlocation;
973 $items = GetLostItems( $where );
975 This function gets a list of lost items.
981 C<$where> is a hashref. it containts a field of the items table as key
982 and the value to match as value. For example:
984 { barcode => 'abc123',
985 homebranch => 'CPL', }
989 C<$items> is a reference to an array full of hashrefs with columns
990 from the "items" table as keys.
992 =item usage in the perl script:
994 my $where = { barcode => '0001548' };
995 my $items = GetLostItems( $where );
996 $template->param( itemsloop => $items );
1003 # Getting input args.
1005 my $dbh = C4::Context->dbh;
1008 SELECT title, author, lib, itemlost, authorised_value, barcode, datelastseen, price, replacementprice, homebranch,
1009 itype, itemtype, holdingbranch, location, itemnotes, items.biblionumber as biblionumber, itemcallnumber
1011 LEFT JOIN biblio ON (items.biblionumber = biblio.biblionumber)
1012 LEFT JOIN biblioitems ON (items.biblionumber = biblioitems.biblionumber)
1013 LEFT JOIN authorised_values ON (items.itemlost = authorised_values.authorised_value)
1015 authorised_values.category = 'LOST'
1016 AND itemlost IS NOT NULL
1019 my @query_parameters;
1020 foreach my $key (keys %$where) {
1021 $query .= " AND $key LIKE ?";
1022 push @query_parameters, "%$where->{$key}%";
1025 my $sth = $dbh->prepare($query);
1026 $sth->execute( @query_parameters );
1028 while ( my $row = $sth->fetchrow_hashref ){
1034 =head2 GetItemsForInventory
1036 ($itemlist, $iTotalRecords) = GetItemsForInventory( {
1037 minlocation => $minlocation,
1038 maxlocation => $maxlocation,
1039 location => $location,
1040 itemtype => $itemtype,
1041 ignoreissued => $ignoreissued,
1042 datelastseen => $datelastseen,
1043 branchcode => $branchcode,
1047 statushash => $statushash,
1048 interface => $interface,
1051 Retrieve a list of title/authors/barcode/callnumber, for biblio inventory.
1053 The sub returns a reference to a list of hashes, each containing
1054 itemnumber, author, title, barcode, item callnumber, and date last
1055 seen. It is ordered by callnumber then title.
1057 The required minlocation & maxlocation parameters are used to specify a range of item callnumbers
1058 the datelastseen can be used to specify that you want to see items not seen since a past date only.
1059 offset & size can be used to retrieve only a part of the whole listing (defaut behaviour)
1060 $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.
1062 $iTotalRecords is the number of rows that would have been returned without the $offset, $size limit clause
1066 sub GetItemsForInventory {
1067 my ( $parameters ) = @_;
1068 my $minlocation = $parameters->{'minlocation'} // '';
1069 my $maxlocation = $parameters->{'maxlocation'} // '';
1070 my $location = $parameters->{'location'} // '';
1071 my $itemtype = $parameters->{'itemtype'} // '';
1072 my $ignoreissued = $parameters->{'ignoreissued'} // '';
1073 my $datelastseen = $parameters->{'datelastseen'} // '';
1074 my $branchcode = $parameters->{'branchcode'} // '';
1075 my $branch = $parameters->{'branch'} // '';
1076 my $offset = $parameters->{'offset'} // '';
1077 my $size = $parameters->{'size'} // '';
1078 my $statushash = $parameters->{'statushash'} // '';
1079 my $interface = $parameters->{'interface'} // '';
1081 my $dbh = C4::Context->dbh;
1082 my ( @bind_params, @where_strings );
1084 my $select_columns = q{
1085 SELECT items.itemnumber, barcode, itemcallnumber, title, author, biblio.biblionumber, biblio.frameworkcode, datelastseen, homebranch, location, notforloan, damaged, itemlost, withdrawn, stocknumber
1087 my $select_count = q{SELECT COUNT(*)};
1090 LEFT JOIN biblio ON items.biblionumber = biblio.biblionumber
1091 LEFT JOIN biblioitems on items.biblionumber = biblioitems.biblionumber
1094 for my $authvfield (keys %$statushash){
1095 if ( scalar @{$statushash->{$authvfield}} > 0 ){
1096 my $joinedvals = join ',', @{$statushash->{$authvfield}};
1097 push @where_strings, "$authvfield in (" . $joinedvals . ")";
1103 push @where_strings, 'itemcallnumber >= ?';
1104 push @bind_params, $minlocation;
1108 push @where_strings, 'itemcallnumber <= ?';
1109 push @bind_params, $maxlocation;
1112 if ($datelastseen) {
1113 $datelastseen = output_pref({ str => $datelastseen, dateformat => 'iso', dateonly => 1 });
1114 push @where_strings, '(datelastseen < ? OR datelastseen IS NULL)';
1115 push @bind_params, $datelastseen;
1119 push @where_strings, 'items.location = ?';
1120 push @bind_params, $location;
1123 if ( $branchcode ) {
1124 if($branch eq "homebranch"){
1125 push @where_strings, 'items.homebranch = ?';
1127 push @where_strings, 'items.holdingbranch = ?';
1129 push @bind_params, $branchcode;
1133 push @where_strings, 'biblioitems.itemtype = ?';
1134 push @bind_params, $itemtype;
1137 if ( $ignoreissued) {
1138 $query .= "LEFT JOIN issues ON items.itemnumber = issues.itemnumber ";
1139 push @where_strings, 'issues.date_due IS NULL';
1142 if ( @where_strings ) {
1144 $query .= join ' AND ', @where_strings;
1146 $query .= ' ORDER BY items.cn_sort, itemcallnumber, title';
1147 my $count_query = $select_count . $query;
1148 $query .= " LIMIT $offset, $size" if ($offset and $size);
1149 $query = $select_columns . $query;
1150 my $sth = $dbh->prepare($query);
1151 $sth->execute( @bind_params );
1154 my $tmpresults = $sth->fetchall_arrayref({});
1155 $sth = $dbh->prepare( $count_query );
1156 $sth->execute( @bind_params );
1157 my ($iTotalRecords) = $sth->fetchrow_array();
1159 my $avmapping = C4::Koha::GetKohaAuthorisedValuesMapping( {
1160 interface => $interface
1162 foreach my $row (@$tmpresults) {
1165 foreach (keys %$row) {
1166 if (defined($avmapping->{"items.$_,".$row->{'frameworkcode'}.",".$row->{$_}})) {
1167 $row->{$_} = $avmapping->{"items.$_,".$row->{'frameworkcode'}.",".$row->{$_}};
1170 push @results, $row;
1173 return (\@results, $iTotalRecords);
1176 =head2 GetItemsCount
1178 $count = &GetItemsCount( $biblionumber);
1180 This function return count of item with $biblionumber
1185 my ( $biblionumber ) = @_;
1186 my $dbh = C4::Context->dbh;
1187 my $query = "SELECT count(*)
1189 WHERE biblionumber=?";
1190 my $sth = $dbh->prepare($query);
1191 $sth->execute($biblionumber);
1192 my $count = $sth->fetchrow;
1196 =head2 GetItemInfosOf
1198 GetItemInfosOf(@itemnumbers);
1202 sub GetItemInfosOf {
1203 my @itemnumbers = @_;
1205 my $itemnumber_values = @itemnumbers ? join( ',', @itemnumbers ) : "''";
1210 WHERE itemnumber IN ($itemnumber_values)
1212 return get_infos_of( $query, 'itemnumber' );
1215 =head2 GetItemsByBiblioitemnumber
1217 GetItemsByBiblioitemnumber($biblioitemnumber);
1219 Returns an arrayref of hashrefs suitable for use in a TMPL_LOOP
1220 Called by C<C4::XISBN>
1224 sub GetItemsByBiblioitemnumber {
1225 my ( $bibitem ) = @_;
1226 my $dbh = C4::Context->dbh;
1227 my $sth = $dbh->prepare("SELECT * FROM items WHERE items.biblioitemnumber = ?") || die $dbh->errstr;
1228 # Get all items attached to a biblioitem
1231 $sth->execute($bibitem) || die $sth->errstr;
1232 while ( my $data = $sth->fetchrow_hashref ) {
1233 # Foreach item, get circulation information
1234 my $sth2 = $dbh->prepare( "SELECT * FROM issues,borrowers
1235 WHERE itemnumber = ?
1236 AND issues.borrowernumber = borrowers.borrowernumber"
1238 $sth2->execute( $data->{'itemnumber'} );
1239 if ( my $data2 = $sth2->fetchrow_hashref ) {
1240 # if item is out, set the due date and who it is out too
1241 $data->{'date_due'} = $data2->{'date_due'};
1242 $data->{'cardnumber'} = $data2->{'cardnumber'};
1243 $data->{'borrowernumber'} = $data2->{'borrowernumber'};
1246 # set date_due to blank, so in the template we check itemlost, and withdrawn
1247 $data->{'date_due'} = '';
1249 # Find the last 3 people who borrowed this item.
1250 my $query2 = "SELECT * FROM old_issues, borrowers WHERE itemnumber = ?
1251 AND old_issues.borrowernumber = borrowers.borrowernumber
1252 ORDER BY returndate desc,timestamp desc LIMIT 3";
1253 $sth2 = $dbh->prepare($query2) || die $dbh->errstr;
1254 $sth2->execute( $data->{'itemnumber'} ) || die $sth2->errstr;
1256 while ( my $data2 = $sth2->fetchrow_hashref ) {
1257 $data->{"timestamp$i2"} = $data2->{'timestamp'};
1258 $data->{"card$i2"} = $data2->{'cardnumber'};
1259 $data->{"borrower$i2"} = $data2->{'borrowernumber'};
1262 push(@results,$data);
1269 @results = GetItemsInfo($biblionumber);
1271 Returns information about items with the given biblionumber.
1273 C<GetItemsInfo> returns a list of references-to-hash. Each element
1274 contains a number of keys. Most of them are attributes from the
1275 C<biblio>, C<biblioitems>, C<items>, and C<itemtypes> tables in the
1276 Koha database. Other keys include:
1280 =item C<$data-E<gt>{branchname}>
1282 The name (not the code) of the branch to which the book belongs.
1284 =item C<$data-E<gt>{datelastseen}>
1286 This is simply C<items.datelastseen>, except that while the date is
1287 stored in YYYY-MM-DD format in the database, here it is converted to
1288 DD/MM/YYYY format. A NULL date is returned as C<//>.
1290 =item C<$data-E<gt>{datedue}>
1292 =item C<$data-E<gt>{class}>
1294 This is the concatenation of C<biblioitems.classification>, the book's
1295 Dewey code, and C<biblioitems.subclass>.
1297 =item C<$data-E<gt>{ocount}>
1299 I think this is the number of copies of the book available.
1301 =item C<$data-E<gt>{order}>
1303 If this is set, it is set to C<One Order>.
1310 my ( $biblionumber ) = @_;
1311 my $dbh = C4::Context->dbh;
1312 # note biblioitems.* must be avoided to prevent large marc and marcxml fields from killing performance.
1313 require C4::Languages;
1314 my $language = C4::Languages::getlanguage();
1320 biblioitems.itemtype,
1323 biblioitems.publicationyear,
1324 biblioitems.publishercode,
1325 biblioitems.volumedate,
1326 biblioitems.volumedesc,
1329 items.notforloan as itemnotforloan,
1330 issues.borrowernumber,
1331 issues.date_due as datedue,
1332 issues.onsite_checkout,
1333 borrowers.cardnumber,
1335 borrowers.firstname,
1336 borrowers.branchcode as bcode,
1338 serial.publisheddate,
1339 itemtypes.description,
1340 COALESCE( localization.translation, itemtypes.description ) AS translated_description,
1341 itemtypes.notforloan as notforloan_per_itemtype,
1344 holding.opac_info as holding_branch_opac_info,
1345 home.opac_info as home_branch_opac_info
1349 LEFT JOIN branches AS holding ON items.holdingbranch = holding.branchcode
1350 LEFT JOIN branches AS home ON items.homebranch=home.branchcode
1351 LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber
1352 LEFT JOIN biblioitems ON biblioitems.biblioitemnumber = items.biblioitemnumber
1353 LEFT JOIN issues USING (itemnumber)
1354 LEFT JOIN borrowers USING (borrowernumber)
1355 LEFT JOIN serialitems USING (itemnumber)
1356 LEFT JOIN serial USING (serialid)
1357 LEFT JOIN itemtypes ON itemtypes.itemtype = "
1358 . (C4::Context->preference('item-level_itypes') ? 'items.itype' : 'biblioitems.itemtype');
1360 LEFT JOIN localization ON itemtypes.itemtype = localization.code
1361 AND localization.entity = 'itemtypes'
1362 AND localization.lang = ?
1365 $query .= " WHERE items.biblionumber = ? ORDER BY home.branchname, items.enumchron, LPAD( items.copynumber, 8, '0' ), items.dateaccessioned DESC" ;
1366 my $sth = $dbh->prepare($query);
1367 $sth->execute($language, $biblionumber);
1372 my $userenv = C4::Context->userenv;
1373 my $want_not_same_branch = C4::Context->preference("IndependentBranches") && !C4::Context->IsSuperLibrarian();
1374 while ( my $data = $sth->fetchrow_hashref ) {
1375 if ( $data->{borrowernumber} && $want_not_same_branch) {
1376 $data->{'NOTSAMEBRANCH'} = $data->{'bcode'} ne $userenv->{branch};
1379 $serial ||= $data->{'serial'};
1381 # get notforloan complete status if applicable
1382 if ( my $code = C4::Koha::GetAuthValCode( 'items.notforloan', $data->{frameworkcode} ) ) {
1383 $data->{notforloanvalue} = C4::Koha::GetKohaAuthorisedValueLib( $code, $data->{itemnotforloan} );
1384 $data->{notforloanvalueopac} = C4::Koha::GetKohaAuthorisedValueLib( $code, $data->{itemnotforloan}, 1 );
1387 # get restricted status and description if applicable
1388 if ( my $code = C4::Koha::GetAuthValCode( 'items.restricted', $data->{frameworkcode} ) ) {
1389 $data->{restrictedopac} = C4::Koha::GetKohaAuthorisedValueLib( $code, $data->{restricted}, 1 );
1390 $data->{restricted} = C4::Koha::GetKohaAuthorisedValueLib( $code, $data->{restricted} );
1393 # my stack procedures
1394 if ( my $code = C4::Koha::GetAuthValCode( 'items.stack', $data->{frameworkcode} ) ) {
1395 $data->{stack} = C4::Koha::GetKohaAuthorisedValueLib( $code, $data->{stack} );
1398 # Find the last 3 people who borrowed this item.
1399 my $sth2 = $dbh->prepare("SELECT * FROM old_issues,borrowers
1400 WHERE itemnumber = ?
1401 AND old_issues.borrowernumber = borrowers.borrowernumber
1402 ORDER BY returndate DESC
1404 $sth2->execute($data->{'itemnumber'});
1406 while (my $data2 = $sth2->fetchrow_hashref()) {
1407 $data->{"timestamp$ii"} = $data2->{'timestamp'} if $data2->{'timestamp'};
1408 $data->{"card$ii"} = $data2->{'cardnumber'} if $data2->{'cardnumber'};
1409 $data->{"borrower$ii"} = $data2->{'borrowernumber'} if $data2->{'borrowernumber'};
1413 $results[$i] = $data;
1418 ? sort { ($b->{'publisheddate'} || $b->{'enumchron'}) cmp ($a->{'publisheddate'} || $a->{'enumchron'}) } @results
1422 =head2 GetItemsLocationInfo
1424 my @itemlocinfo = GetItemsLocationInfo($biblionumber);
1426 Returns the branch names, shelving location and itemcallnumber for each item attached to the biblio in question
1428 C<GetItemsInfo> returns a list of references-to-hash. Data returned:
1432 =item C<$data-E<gt>{homebranch}>
1434 Branch Name of the item's homebranch
1436 =item C<$data-E<gt>{holdingbranch}>
1438 Branch Name of the item's holdingbranch
1440 =item C<$data-E<gt>{location}>
1442 Item's shelving location code
1444 =item C<$data-E<gt>{location_intranet}>
1446 The intranet description for the Shelving Location as set in authorised_values 'LOC'
1448 =item C<$data-E<gt>{location_opac}>
1450 The OPAC description for the Shelving Location as set in authorised_values 'LOC'. Falls back to intranet description if no OPAC
1453 =item C<$data-E<gt>{itemcallnumber}>
1455 Item's itemcallnumber
1457 =item C<$data-E<gt>{cn_sort}>
1459 Item's call number normalized for sorting
1465 sub GetItemsLocationInfo {
1466 my $biblionumber = shift;
1469 my $dbh = C4::Context->dbh;
1470 my $query = "SELECT a.branchname as homebranch, b.branchname as holdingbranch,
1471 location, itemcallnumber, cn_sort
1472 FROM items, branches as a, branches as b
1473 WHERE homebranch = a.branchcode AND holdingbranch = b.branchcode
1474 AND biblionumber = ?
1475 ORDER BY cn_sort ASC";
1476 my $sth = $dbh->prepare($query);
1477 $sth->execute($biblionumber);
1479 while ( my $data = $sth->fetchrow_hashref ) {
1480 $data->{location_intranet} = GetKohaAuthorisedValueLib('LOC', $data->{location});
1481 $data->{location_opac}= GetKohaAuthorisedValueLib('LOC', $data->{location}, 1);
1482 push @results, $data;
1487 =head2 GetHostItemsInfo
1489 $hostiteminfo = GetHostItemsInfo($hostfield);
1490 Returns the iteminfo for items linked to records via a host field
1494 sub GetHostItemsInfo {
1496 my @returnitemsInfo;
1498 if (C4::Context->preference('marcflavour') eq 'MARC21' ||
1499 C4::Context->preference('marcflavour') eq 'NORMARC'){
1500 foreach my $hostfield ( $record->field('773') ) {
1501 my $hostbiblionumber = $hostfield->subfield("0");
1502 my $linkeditemnumber = $hostfield->subfield("9");
1503 my @hostitemInfos = GetItemsInfo($hostbiblionumber);
1504 foreach my $hostitemInfo (@hostitemInfos){
1505 if ($hostitemInfo->{itemnumber} eq $linkeditemnumber){
1506 push (@returnitemsInfo,$hostitemInfo);
1511 } elsif ( C4::Context->preference('marcflavour') eq 'UNIMARC'){
1512 foreach my $hostfield ( $record->field('461') ) {
1513 my $hostbiblionumber = $hostfield->subfield("0");
1514 my $linkeditemnumber = $hostfield->subfield("9");
1515 my @hostitemInfos = GetItemsInfo($hostbiblionumber);
1516 foreach my $hostitemInfo (@hostitemInfos){
1517 if ($hostitemInfo->{itemnumber} eq $linkeditemnumber){
1518 push (@returnitemsInfo,$hostitemInfo);
1524 return @returnitemsInfo;
1528 =head2 GetLastAcquisitions
1530 my $lastacq = GetLastAcquisitions({'branches' => ('branch1','branch2'),
1531 'itemtypes' => ('BK','BD')}, 10);
1535 sub GetLastAcquisitions {
1536 my ($data,$max) = @_;
1538 my $itemtype = C4::Context->preference('item-level_itypes') ? 'itype' : 'itemtype';
1540 my $number_of_branches = @{$data->{branches}};
1541 my $number_of_itemtypes = @{$data->{itemtypes}};
1544 my @where = ('WHERE 1 ');
1545 $number_of_branches and push @where
1546 , 'AND holdingbranch IN ('
1547 , join(',', ('?') x $number_of_branches )
1551 $number_of_itemtypes and push @where
1552 , "AND $itemtype IN ("
1553 , join(',', ('?') x $number_of_itemtypes )
1557 my $query = "SELECT biblio.biblionumber as biblionumber, title, dateaccessioned
1558 FROM items RIGHT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
1559 RIGHT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber)
1561 GROUP BY biblio.biblionumber
1562 ORDER BY dateaccessioned DESC LIMIT $max";
1564 my $dbh = C4::Context->dbh;
1565 my $sth = $dbh->prepare($query);
1567 $sth->execute((@{$data->{branches}}, @{$data->{itemtypes}}));
1570 while( my $row = $sth->fetchrow_hashref){
1571 push @results, {date => $row->{dateaccessioned}
1572 , biblionumber => $row->{biblionumber}
1573 , title => $row->{title}};
1579 =head2 GetItemnumbersForBiblio
1581 my $itemnumbers = GetItemnumbersForBiblio($biblionumber);
1583 Given a single biblionumber, return an arrayref of all the corresponding itemnumbers
1587 sub GetItemnumbersForBiblio {
1588 my $biblionumber = shift;
1590 my $dbh = C4::Context->dbh;
1591 my $sth = $dbh->prepare("SELECT itemnumber FROM items WHERE biblionumber = ?");
1592 $sth->execute($biblionumber);
1593 while (my $result = $sth->fetchrow_hashref) {
1594 push @items, $result->{'itemnumber'};
1599 =head2 get_itemnumbers_of
1601 my @itemnumbers_of = get_itemnumbers_of(@biblionumbers);
1603 Given a list of biblionumbers, return the list of corresponding itemnumbers
1604 for each biblionumber.
1606 Return a reference on a hash where keys are biblionumbers and values are
1607 references on array of itemnumbers.
1611 sub get_itemnumbers_of {
1612 my @biblionumbers = @_;
1614 my $dbh = C4::Context->dbh;
1620 WHERE biblionumber IN (?' . ( ',?' x scalar @biblionumbers - 1 ) . ')
1622 my $sth = $dbh->prepare($query);
1623 $sth->execute(@biblionumbers);
1627 while ( my ( $itemnumber, $biblionumber ) = $sth->fetchrow_array ) {
1628 push @{ $itemnumbers_of{$biblionumber} }, $itemnumber;
1631 return \%itemnumbers_of;
1634 =head2 get_hostitemnumbers_of
1636 my @itemnumbers_of = get_hostitemnumbers_of($biblionumber);
1638 Given a biblionumber, return the list of corresponding itemnumbers that are linked to it via host fields
1640 Return a reference on a hash where key is a biblionumber and values are
1641 references on array of itemnumbers.
1646 sub get_hostitemnumbers_of {
1647 my ($biblionumber) = @_;
1648 my $marcrecord = GetMarcBiblio($biblionumber);
1649 my (@returnhostitemnumbers,$tag, $biblio_s, $item_s);
1651 my $marcflavor = C4::Context->preference('marcflavour');
1652 if ($marcflavor eq 'MARC21' || $marcflavor eq 'NORMARC') {
1656 } elsif ($marcflavor eq 'UNIMARC') {
1662 foreach my $hostfield ( $marcrecord->field($tag) ) {
1663 my $hostbiblionumber = $hostfield->subfield($biblio_s);
1664 my $linkeditemnumber = $hostfield->subfield($item_s);
1666 if (my $itemnumbers = get_itemnumbers_of($hostbiblionumber)->{$hostbiblionumber})
1668 @itemnumbers = @$itemnumbers;
1670 foreach my $itemnumber (@itemnumbers){
1671 if ($itemnumber eq $linkeditemnumber){
1672 push (@returnhostitemnumbers,$itemnumber);
1677 return @returnhostitemnumbers;
1681 =head2 GetItemnumberFromBarcode
1683 $result = GetItemnumberFromBarcode($barcode);
1687 sub GetItemnumberFromBarcode {
1689 my $dbh = C4::Context->dbh;
1692 $dbh->prepare("SELECT itemnumber FROM items WHERE items.barcode=?");
1693 $rq->execute($barcode);
1694 my ($result) = $rq->fetchrow;
1698 =head2 GetBarcodeFromItemnumber
1700 $result = GetBarcodeFromItemnumber($itemnumber);
1704 sub GetBarcodeFromItemnumber {
1705 my ($itemnumber) = @_;
1706 my $dbh = C4::Context->dbh;
1709 $dbh->prepare("SELECT barcode FROM items WHERE items.itemnumber=?");
1710 $rq->execute($itemnumber);
1711 my ($result) = $rq->fetchrow;
1715 =head2 GetHiddenItemnumbers
1717 my @itemnumbers_to_hide = GetHiddenItemnumbers(@items);
1719 Given a list of items it checks which should be hidden from the OPAC given
1720 the current configuration. Returns a list of itemnumbers corresponding to
1721 those that should be hidden.
1725 sub GetHiddenItemnumbers {
1729 my $yaml = C4::Context->preference('OpacHiddenItems');
1730 return () if (! $yaml =~ /\S/ );
1731 $yaml = "$yaml\n\n"; # YAML is anal on ending \n. Surplus does not hurt
1734 $hidingrules = YAML::Load($yaml);
1737 warn "Unable to parse OpacHiddenItems syspref : $@";
1740 my $dbh = C4::Context->dbh;
1743 foreach my $item (@items) {
1745 # We check each rule
1746 foreach my $field (keys %$hidingrules) {
1748 if (exists $item->{$field}) {
1749 $val = $item->{$field};
1752 my $query = "SELECT $field from items where itemnumber = ?";
1753 $val = $dbh->selectrow_array($query, undef, $item->{'itemnumber'});
1755 $val = '' unless defined $val;
1757 # If the results matches the values in the yaml file
1758 if (any { $val eq $_ } @{$hidingrules->{$field}}) {
1760 # We add the itemnumber to the list
1761 push @resultitems, $item->{'itemnumber'};
1763 # If at least one rule matched for an item, no need to test the others
1768 return @resultitems;
1771 =head1 LIMITED USE FUNCTIONS
1773 The following functions, while part of the public API,
1774 are not exported. This is generally because they are
1775 meant to be used by only one script for a specific
1776 purpose, and should not be used in any other context
1777 without careful thought.
1783 my $item_marc = GetMarcItem($biblionumber, $itemnumber);
1785 Returns MARC::Record of the item passed in parameter.
1786 This function is meant for use only in C<cataloguing/additem.pl>,
1787 where it is needed to support that script's MARC-like
1793 my ( $biblionumber, $itemnumber ) = @_;
1795 # GetMarcItem has been revised so that it does the following:
1796 # 1. Gets the item information from the items table.
1797 # 2. Converts it to a MARC field for storage in the bib record.
1799 # The previous behavior was:
1800 # 1. Get the bib record.
1801 # 2. Return the MARC tag corresponding to the item record.
1803 # The difference is that one treats the items row as authoritative,
1804 # while the other treats the MARC representation as authoritative
1805 # under certain circumstances.
1807 my $itemrecord = GetItem($itemnumber);
1809 # Tack on 'items.' prefix to column names so that TransformKohaToMarc will work.
1810 # Also, don't emit a subfield if the underlying field is blank.
1813 return Item2Marc($itemrecord,$biblionumber);
1817 my ($itemrecord,$biblionumber)=@_;
1820 defined($itemrecord->{$_}) && $itemrecord->{$_} ne '' ? ("items.$_" => $itemrecord->{$_}) : ()
1821 } keys %{ $itemrecord }
1823 my $itemmarc = TransformKohaToMarc($mungeditem);
1824 my ( $itemtag, $itemsubfield ) = GetMarcFromKohaField("items.itemnumber",GetFrameworkCode($biblionumber)||'');
1826 my $unlinked_item_subfields = _parse_unlinked_item_subfields_from_xml($mungeditem->{'items.more_subfields_xml'});
1827 if (defined $unlinked_item_subfields and $#$unlinked_item_subfields > -1) {
1828 foreach my $field ($itemmarc->field($itemtag)){
1829 $field->add_subfields(@$unlinked_item_subfields);
1835 =head1 PRIVATE FUNCTIONS AND VARIABLES
1837 The following functions are not meant to be called
1838 directly, but are documented in order to explain
1839 the inner workings of C<C4::Items>.
1843 =head2 %derived_columns
1845 This hash keeps track of item columns that
1846 are strictly derived from other columns in
1847 the item record and are not meant to be set
1850 Each key in the hash should be the name of a
1851 column (as named by TransformMarcToKoha). Each
1852 value should be hashref whose keys are the
1853 columns on which the derived column depends. The
1854 hashref should also contain a 'BUILDER' key
1855 that is a reference to a sub that calculates
1860 my %derived_columns = (
1861 'items.cn_sort' => {
1862 'itemcallnumber' => 1,
1863 'items.cn_source' => 1,
1864 'BUILDER' => \&_calc_items_cn_sort,
1868 =head2 _set_derived_columns_for_add
1870 _set_derived_column_for_add($item);
1872 Given an item hash representing a new item to be added,
1873 calculate any derived columns. Currently the only
1874 such column is C<items.cn_sort>.
1878 sub _set_derived_columns_for_add {
1881 foreach my $column (keys %derived_columns) {
1882 my $builder = $derived_columns{$column}->{'BUILDER'};
1883 my $source_values = {};
1884 foreach my $source_column (keys %{ $derived_columns{$column} }) {
1885 next if $source_column eq 'BUILDER';
1886 $source_values->{$source_column} = $item->{$source_column};
1888 $builder->($item, $source_values);
1892 =head2 _set_derived_columns_for_mod
1894 _set_derived_column_for_mod($item);
1896 Given an item hash representing a new item to be modified.
1897 calculate any derived columns. Currently the only
1898 such column is C<items.cn_sort>.
1900 This routine differs from C<_set_derived_columns_for_add>
1901 in that it needs to handle partial item records. In other
1902 words, the caller of C<ModItem> may have supplied only one
1903 or two columns to be changed, so this function needs to
1904 determine whether any of the columns to be changed affect
1905 any of the derived columns. Also, if a derived column
1906 depends on more than one column, but the caller is not
1907 changing all of then, this routine retrieves the unchanged
1908 values from the database in order to ensure a correct
1913 sub _set_derived_columns_for_mod {
1916 foreach my $column (keys %derived_columns) {
1917 my $builder = $derived_columns{$column}->{'BUILDER'};
1918 my $source_values = {};
1919 my %missing_sources = ();
1920 my $must_recalc = 0;
1921 foreach my $source_column (keys %{ $derived_columns{$column} }) {
1922 next if $source_column eq 'BUILDER';
1923 if (exists $item->{$source_column}) {
1925 $source_values->{$source_column} = $item->{$source_column};
1927 $missing_sources{$source_column} = 1;
1931 foreach my $source_column (keys %missing_sources) {
1932 $source_values->{$source_column} = _get_single_item_column($source_column, $item->{'itemnumber'});
1934 $builder->($item, $source_values);
1939 =head2 _do_column_fixes_for_mod
1941 _do_column_fixes_for_mod($item);
1943 Given an item hashref containing one or more
1944 columns to modify, fix up certain values.
1945 Specifically, set to 0 any passed value
1946 of C<notforloan>, C<damaged>, C<itemlost>, or
1947 C<withdrawn> that is either undefined or
1948 contains the empty string.
1952 sub _do_column_fixes_for_mod {
1955 if (exists $item->{'notforloan'} and
1956 (not defined $item->{'notforloan'} or $item->{'notforloan'} eq '')) {
1957 $item->{'notforloan'} = 0;
1959 if (exists $item->{'damaged'} and
1960 (not defined $item->{'damaged'} or $item->{'damaged'} eq '')) {
1961 $item->{'damaged'} = 0;
1963 if (exists $item->{'itemlost'} and
1964 (not defined $item->{'itemlost'} or $item->{'itemlost'} eq '')) {
1965 $item->{'itemlost'} = 0;
1967 if (exists $item->{'withdrawn'} and
1968 (not defined $item->{'withdrawn'} or $item->{'withdrawn'} eq '')) {
1969 $item->{'withdrawn'} = 0;
1971 if (exists $item->{location}
1972 and $item->{location} ne 'CART'
1973 and $item->{location} ne 'PROC'
1974 and not $item->{permanent_location}
1976 $item->{'permanent_location'} = $item->{'location'};
1978 if (exists $item->{'timestamp'}) {
1979 delete $item->{'timestamp'};
1983 =head2 _get_single_item_column
1985 _get_single_item_column($column, $itemnumber);
1987 Retrieves the value of a single column from an C<items>
1988 row specified by C<$itemnumber>.
1992 sub _get_single_item_column {
1994 my $itemnumber = shift;
1996 my $dbh = C4::Context->dbh;
1997 my $sth = $dbh->prepare("SELECT $column FROM items WHERE itemnumber = ?");
1998 $sth->execute($itemnumber);
1999 my ($value) = $sth->fetchrow();
2003 =head2 _calc_items_cn_sort
2005 _calc_items_cn_sort($item, $source_values);
2007 Helper routine to calculate C<items.cn_sort>.
2011 sub _calc_items_cn_sort {
2013 my $source_values = shift;
2015 $item->{'items.cn_sort'} = GetClassSort($source_values->{'items.cn_source'}, $source_values->{'itemcallnumber'}, "");
2018 =head2 _set_defaults_for_add
2020 _set_defaults_for_add($item_hash);
2022 Given an item hash representing an item to be added, set
2023 correct default values for columns whose default value
2024 is not handled by the DBMS. This includes the following
2031 C<items.dateaccessioned>
2053 sub _set_defaults_for_add {
2055 $item->{dateaccessioned} ||= output_pref({ dt => dt_from_string, dateformat => 'iso', dateonly => 1 });
2056 $item->{$_} ||= 0 for (qw( notforloan damaged itemlost withdrawn));
2059 =head2 _koha_new_item
2061 my ($itemnumber,$error) = _koha_new_item( $item, $barcode );
2063 Perform the actual insert into the C<items> table.
2067 sub _koha_new_item {
2068 my ( $item, $barcode ) = @_;
2069 my $dbh=C4::Context->dbh;
2071 $item->{permanent_location} //= $item->{location};
2072 _mod_item_dates( $item );
2074 "INSERT INTO items SET
2076 biblioitemnumber = ?,
2078 dateaccessioned = ?,
2082 replacementprice = ?,
2083 replacementpricedate = ?,
2084 datelastborrowed = ?,
2092 coded_location_qualifier = ?,
2095 itemnotes_nonpublic = ?,
2099 permanent_location = ?,
2111 more_subfields_xml = ?,
2116 my $sth = $dbh->prepare($query);
2117 my $today = output_pref({ dt => dt_from_string, dateformat => 'iso', dateonly => 1 });
2119 $item->{'biblionumber'},
2120 $item->{'biblioitemnumber'},
2122 $item->{'dateaccessioned'},
2123 $item->{'booksellerid'},
2124 $item->{'homebranch'},
2126 $item->{'replacementprice'},
2127 $item->{'replacementpricedate'} || $today,
2128 $item->{datelastborrowed},
2129 $item->{datelastseen} || $today,
2131 $item->{'notforloan'},
2133 $item->{'itemlost'},
2134 $item->{'withdrawn'},
2135 $item->{'itemcallnumber'},
2136 $item->{'coded_location_qualifier'},
2137 $item->{'restricted'},
2138 $item->{'itemnotes'},
2139 $item->{'itemnotes_nonpublic'},
2140 $item->{'holdingbranch'},
2142 $item->{'location'},
2143 $item->{'permanent_location'},
2146 $item->{'renewals'},
2147 $item->{'reserves'},
2148 $item->{'items.cn_source'},
2149 $item->{'items.cn_sort'},
2152 $item->{'materials'},
2154 $item->{'enumchron'},
2155 $item->{'more_subfields_xml'},
2156 $item->{'copynumber'},
2157 $item->{'stocknumber'},
2158 $item->{'new_status'},
2162 if ( defined $sth->errstr ) {
2163 $error.="ERROR in _koha_new_item $query".$sth->errstr;
2166 $itemnumber = $dbh->{'mysql_insertid'};
2169 return ( $itemnumber, $error );
2172 =head2 MoveItemFromBiblio
2174 MoveItemFromBiblio($itenumber, $frombiblio, $tobiblio);
2176 Moves an item from a biblio to another
2178 Returns undef if the move failed or the biblionumber of the destination record otherwise
2182 sub MoveItemFromBiblio {
2183 my ($itemnumber, $frombiblio, $tobiblio) = @_;
2184 my $dbh = C4::Context->dbh;
2185 my ( $tobiblioitem ) = $dbh->selectrow_array(q|
2186 SELECT biblioitemnumber
2188 WHERE biblionumber = ?
2189 |, undef, $tobiblio );
2190 my $return = $dbh->do(q|
2192 SET biblioitemnumber = ?,
2194 WHERE itemnumber = ?
2195 AND biblionumber = ?
2196 |, undef, $tobiblioitem, $tobiblio, $itemnumber, $frombiblio );
2198 ModZebra( $tobiblio, "specialUpdate", "biblioserver" );
2199 ModZebra( $frombiblio, "specialUpdate", "biblioserver" );
2200 # Checking if the item we want to move is in an order
2201 require C4::Acquisition;
2202 my $order = C4::Acquisition::GetOrderFromItemnumber($itemnumber);
2204 # Replacing the biblionumber within the order if necessary
2205 $order->{'biblionumber'} = $tobiblio;
2206 C4::Acquisition::ModOrder($order);
2209 # Update reserves, hold_fill_targets, tmp_holdsqueue and linktracker tables
2210 for my $table_name ( qw( reserves hold_fill_targets tmp_holdsqueue linktracker ) ) {
2213 SET biblionumber = ?
2214 WHERE itemnumber = ?
2215 |, undef, $tobiblio, $itemnumber );
2224 DelItemCheck($dbh, $biblionumber, $itemnumber);
2226 Exported function (core API) for deleting an item record in Koha if there no current issue.
2231 my ( $dbh, $biblionumber, $itemnumber ) = @_;
2233 $dbh ||= C4::Context->dbh;
2237 my $countanalytics=GetAnalyticsCount($itemnumber);
2240 # check that there is no issue on this item before deletion.
2241 my $sth = $dbh->prepare(q{
2242 SELECT COUNT(*) FROM issues
2243 WHERE itemnumber = ?
2245 $sth->execute($itemnumber);
2246 my ($onloan) = $sth->fetchrow;
2248 my $item = GetItem($itemnumber);
2251 $error = "book_on_loan"
2253 elsif ( defined C4::Context->userenv
2254 and !C4::Context->IsSuperLibrarian()
2255 and C4::Context->preference("IndependentBranches")
2256 and ( C4::Context->userenv->{branch} ne $item->{'homebranch'} ) )
2258 $error = "not_same_branch";
2261 # check it doesn't have a waiting reserve
2262 $sth = $dbh->prepare(q{
2263 SELECT COUNT(*) FROM reserves
2264 WHERE (found = 'W' OR found = 'T')
2267 $sth->execute($itemnumber);
2268 my ($reserve) = $sth->fetchrow;
2270 $error = "book_reserved";
2271 } elsif ($countanalytics > 0){
2272 $error = "linked_analytics";
2276 biblionumber => $biblionumber,
2277 itemnumber => $itemnumber
2286 =head2 _koha_modify_item
2288 my ($itemnumber,$error) =_koha_modify_item( $item );
2290 Perform the actual update of the C<items> row. Note that this
2291 routine accepts a hashref specifying the columns to update.
2295 sub _koha_modify_item {
2297 my $dbh=C4::Context->dbh;
2300 my $query = "UPDATE items SET ";
2302 _mod_item_dates( $item );
2303 for my $key ( keys %$item ) {
2304 next if ( $key eq 'itemnumber' );
2306 push @bind, $item->{$key};
2309 $query .= " WHERE itemnumber=?";
2310 push @bind, $item->{'itemnumber'};
2311 my $sth = $dbh->prepare($query);
2312 $sth->execute(@bind);
2314 $error.="ERROR in _koha_modify_item $query: ".$sth->errstr;
2317 return ($item->{'itemnumber'},$error);
2320 sub _mod_item_dates { # date formatting for date fields in item hash
2322 return if !$item || ref($item) ne 'HASH';
2325 { $_ =~ /^onloan$|^date|date$|datetime$/ }
2327 # Incl. dateaccessioned,replacementpricedate,datelastborrowed,datelastseen
2328 # NOTE: We do not (yet) have items fields ending with datetime
2329 # Fields with _on$ have been handled already
2331 foreach my $key ( @keys ) {
2332 next if !defined $item->{$key}; # skip undefs
2333 my $dt = eval { dt_from_string( $item->{$key} ) };
2334 # eval: dt_from_string will die on us if we pass illegal dates
2337 if( defined $dt && ref($dt) eq 'DateTime' ) {
2338 if( $key =~ /datetime/ ) {
2339 $newstr = DateTime::Format::MySQL->format_datetime($dt);
2341 $newstr = DateTime::Format::MySQL->format_date($dt);
2344 $item->{$key} = $newstr; # might be undef to clear garbage
2348 =head2 _koha_delete_item
2350 _koha_delete_item( $itemnum );
2352 Internal function to delete an item record from the koha tables
2356 sub _koha_delete_item {
2357 my ( $itemnum ) = @_;
2359 my $dbh = C4::Context->dbh;
2360 # save the deleted item to deleteditems table
2361 my $sth = $dbh->prepare("SELECT * FROM items WHERE itemnumber=?");
2362 $sth->execute($itemnum);
2363 my $data = $sth->fetchrow_hashref();
2365 # There is no item to delete
2366 return 0 unless $data;
2368 my $query = "INSERT INTO deleteditems SET ";
2370 foreach my $key ( keys %$data ) {
2371 next if ( $key eq 'timestamp' ); # timestamp will be set by db
2372 $query .= "$key = ?,";
2373 push( @bind, $data->{$key} );
2376 $sth = $dbh->prepare($query);
2377 $sth->execute(@bind);
2379 # delete from items table
2380 $sth = $dbh->prepare("DELETE FROM items WHERE itemnumber=?");
2381 my $deleted = $sth->execute($itemnum);
2382 return ( $deleted == 1 ) ? 1 : 0;
2385 =head2 _marc_from_item_hash
2387 my $item_marc = _marc_from_item_hash($item, $frameworkcode[, $unlinked_item_subfields]);
2389 Given an item hash representing a complete item record,
2390 create a C<MARC::Record> object containing an embedded
2391 tag representing that item.
2393 The third, optional parameter C<$unlinked_item_subfields> is
2394 an arrayref of subfields (not mapped to C<items> fields per the
2395 framework) to be added to the MARC representation
2400 sub _marc_from_item_hash {
2402 my $frameworkcode = shift;
2403 my $unlinked_item_subfields;
2405 $unlinked_item_subfields = shift;
2408 # Tack on 'items.' prefix to column names so lookup from MARC frameworks will work
2409 # Also, don't emit a subfield if the underlying field is blank.
2410 my $mungeditem = { map { (defined($item->{$_}) and $item->{$_} ne '') ?
2411 (/^items\./ ? ($_ => $item->{$_}) : ("items.$_" => $item->{$_}))
2412 : () } keys %{ $item } };
2414 my $item_marc = MARC::Record->new();
2415 foreach my $item_field ( keys %{$mungeditem} ) {
2416 my ( $tag, $subfield ) = GetMarcFromKohaField( $item_field, $frameworkcode );
2417 next unless defined $tag and defined $subfield; # skip if not mapped to MARC field
2418 my @values = split(/\s?\|\s?/, $mungeditem->{$item_field}, -1);
2419 foreach my $value (@values){
2420 if ( my $field = $item_marc->field($tag) ) {
2421 $field->add_subfields( $subfield => $value );
2423 my $add_subfields = [];
2424 if (defined $unlinked_item_subfields and ref($unlinked_item_subfields) eq 'ARRAY' and $#$unlinked_item_subfields > -1) {
2425 $add_subfields = $unlinked_item_subfields;
2427 $item_marc->add_fields( $tag, " ", " ", $subfield => $value, @$add_subfields );
2435 =head2 _repack_item_errors
2437 Add an error message hash generated by C<CheckItemPreSave>
2438 to a list of errors.
2442 sub _repack_item_errors {
2443 my $item_sequence_num = shift;
2444 my $item_ref = shift;
2445 my $error_ref = shift;
2447 my @repacked_errors = ();
2449 foreach my $error_code (sort keys %{ $error_ref }) {
2450 my $repacked_error = {};
2451 $repacked_error->{'item_sequence'} = $item_sequence_num;
2452 $repacked_error->{'item_barcode'} = exists($item_ref->{'barcode'}) ? $item_ref->{'barcode'} : '';
2453 $repacked_error->{'error_code'} = $error_code;
2454 $repacked_error->{'error_information'} = $error_ref->{$error_code};
2455 push @repacked_errors, $repacked_error;
2458 return @repacked_errors;
2461 =head2 _get_unlinked_item_subfields
2463 my $unlinked_item_subfields = _get_unlinked_item_subfields($original_item_marc, $frameworkcode);
2467 sub _get_unlinked_item_subfields {
2468 my $original_item_marc = shift;
2469 my $frameworkcode = shift;
2471 my $marcstructure = GetMarcStructure(1, $frameworkcode);
2473 # assume that this record has only one field, and that that
2474 # field contains only the item information
2476 my @fields = $original_item_marc->fields();
2477 if ($#fields > -1) {
2478 my $field = $fields[0];
2479 my $tag = $field->tag();
2480 foreach my $subfield ($field->subfields()) {
2481 if (defined $subfield->[1] and
2482 $subfield->[1] ne '' and
2483 !$marcstructure->{$tag}->{$subfield->[0]}->{'kohafield'}) {
2484 push @$subfields, $subfield->[0] => $subfield->[1];
2491 =head2 _get_unlinked_subfields_xml
2493 my $unlinked_subfields_xml = _get_unlinked_subfields_xml($unlinked_item_subfields);
2497 sub _get_unlinked_subfields_xml {
2498 my $unlinked_item_subfields = shift;
2501 if (defined $unlinked_item_subfields and ref($unlinked_item_subfields) eq 'ARRAY' and $#$unlinked_item_subfields > -1) {
2502 my $marc = MARC::Record->new();
2503 # use of tag 999 is arbitrary, and doesn't need to match the item tag
2504 # used in the framework
2505 $marc->append_fields(MARC::Field->new('999', ' ', ' ', @$unlinked_item_subfields));
2506 $marc->encoding("UTF-8");
2507 $xml = $marc->as_xml("USMARC");
2513 =head2 _parse_unlinked_item_subfields_from_xml
2515 my $unlinked_item_subfields = _parse_unlinked_item_subfields_from_xml($whole_item->{'more_subfields_xml'}):
2519 sub _parse_unlinked_item_subfields_from_xml {
2521 require C4::Charset;
2522 return unless defined $xml and $xml ne "";
2523 my $marc = MARC::Record->new_from_xml(C4::Charset::StripNonXmlChars($xml),'UTF-8');
2524 my $unlinked_subfields = [];
2525 my @fields = $marc->fields();
2526 if ($#fields > -1) {
2527 foreach my $subfield ($fields[0]->subfields()) {
2528 push @$unlinked_subfields, $subfield->[0] => $subfield->[1];
2531 return $unlinked_subfields;
2534 =head2 GetAnalyticsCount
2536 $count= &GetAnalyticsCount($itemnumber)
2538 counts Usage of itemnumber in Analytical bibliorecords.
2542 sub GetAnalyticsCount {
2543 my ($itemnumber) = @_;
2545 ### ZOOM search here
2547 $query= "hi=".$itemnumber;
2548 my $searcher = Koha::SearchEngine::Search->new({index => $Koha::SearchEngine::BIBLIOS_INDEX});
2549 my ($err,$res,$result) = $searcher->simple_search_compat($query,0,10);
2555 $holds = &GetItemHolds($biblionumber, $itemnumber);
2557 This function return the count of holds with $biblionumber and $itemnumber
2562 my ($biblionumber, $itemnumber) = @_;
2564 my $dbh = C4::Context->dbh;
2565 my $query = "SELECT count(*)
2567 WHERE biblionumber=? AND itemnumber=?";
2568 my $sth = $dbh->prepare($query);
2569 $sth->execute($biblionumber, $itemnumber);
2570 $holds = $sth->fetchrow;
2574 =head2 SearchItemsByField
2576 my $items = SearchItemsByField($field, $value);
2578 SearchItemsByField will search for items on a specific given field.
2579 For instance you can search all items with a specific stocknumber like this:
2581 my $items = SearchItemsByField('stocknumber', $stocknumber);
2585 sub SearchItemsByField {
2586 my ($field, $value) = @_;
2593 my ($results) = SearchItems($filters);
2597 sub _SearchItems_build_where_fragment {
2600 my $dbh = C4::Context->dbh;
2603 if (exists($filter->{conjunction})) {
2604 my (@where_strs, @where_args);
2605 foreach my $f (@{ $filter->{filters} }) {
2606 my $fragment = _SearchItems_build_where_fragment($f);
2608 push @where_strs, $fragment->{str};
2609 push @where_args, @{ $fragment->{args} };
2614 $where_str = '(' . join (' ' . $filter->{conjunction} . ' ', @where_strs) . ')';
2617 args => \@where_args,
2621 my @columns = Koha::Database->new()->schema()->resultset('Item')->result_source->columns;
2622 push @columns, Koha::Database->new()->schema()->resultset('Biblio')->result_source->columns;
2623 push @columns, Koha::Database->new()->schema()->resultset('Biblioitem')->result_source->columns;
2624 my @operators = qw(= != > < >= <= like);
2625 my $field = $filter->{field};
2626 if ( (0 < grep /^$field$/, @columns) or (substr($field, 0, 5) eq 'marc:') ) {
2627 my $op = $filter->{operator};
2628 my $query = $filter->{query};
2630 if (!$op or (0 == grep /^$op$/, @operators)) {
2631 $op = '='; # default operator
2635 if ($field =~ /^marc:(\d{3})(?:\$(\w))?$/) {
2637 my $marcsubfield = $2;
2638 my ($kohafield) = $dbh->selectrow_array(q|
2639 SELECT kohafield FROM marc_subfield_structure
2640 WHERE tagfield=? AND tagsubfield=? AND frameworkcode=''
2641 |, undef, $marcfield, $marcsubfield);
2644 $column = $kohafield;
2646 # MARC field is not linked to a DB field so we need to use
2647 # ExtractValue on biblioitems.marcxml or
2648 # items.more_subfields_xml, depending on the MARC field.
2651 my ($itemfield) = GetMarcFromKohaField('items.itemnumber');
2652 if ($marcfield eq $itemfield) {
2653 $sqlfield = 'more_subfields_xml';
2654 $xpath = '//record/datafield/subfield[@code="' . $marcsubfield . '"]';
2656 $sqlfield = 'marcxml';
2657 if ($marcfield < 10) {
2658 $xpath = "//record/controlfield[\@tag=\"$marcfield\"]";
2660 $xpath = "//record/datafield[\@tag=\"$marcfield\"]/subfield[\@code=\"$marcsubfield\"]";
2663 $column = "ExtractValue($sqlfield, '$xpath')";
2669 if (ref $query eq 'ARRAY') {
2672 } elsif ($op eq '!=') {
2676 str => "$column $op (" . join (',', ('?') x @$query) . ")",
2681 str => "$column $op ?",
2688 return $where_fragment;
2693 my ($items, $total) = SearchItems($filter, $params);
2695 Perform a search among items
2697 $filter is a reference to a hash which can be a filter, or a combination of filters.
2699 A filter has the following keys:
2703 =item * field: the name of a SQL column in table items
2705 =item * query: the value to search in this column
2707 =item * operator: comparison operator. Can be one of = != > < >= <= like
2711 A combination of filters hash the following keys:
2715 =item * conjunction: 'AND' or 'OR'
2717 =item * filters: array ref of filters
2721 $params is a reference to a hash that can contain the following parameters:
2725 =item * rows: Number of items to return. 0 returns everything (default: 0)
2727 =item * page: Page to return (return items from (page-1)*rows to (page*rows)-1)
2730 =item * sortby: A SQL column name in items table to sort on
2732 =item * sortorder: 'ASC' or 'DESC'
2739 my ($filter, $params) = @_;
2743 return unless ref $filter eq 'HASH';
2744 return unless ref $params eq 'HASH';
2746 # Default parameters
2747 $params->{rows} ||= 0;
2748 $params->{page} ||= 1;
2749 $params->{sortby} ||= 'itemnumber';
2750 $params->{sortorder} ||= 'ASC';
2752 my ($where_str, @where_args);
2753 my $where_fragment = _SearchItems_build_where_fragment($filter);
2754 if ($where_fragment) {
2755 $where_str = $where_fragment->{str};
2756 @where_args = @{ $where_fragment->{args} };
2759 my $dbh = C4::Context->dbh;
2761 SELECT SQL_CALC_FOUND_ROWS items.*
2763 LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber
2764 LEFT JOIN biblioitems ON biblioitems.biblioitemnumber = items.biblioitemnumber
2766 if (defined $where_str and $where_str ne '') {
2767 $query .= qq{ WHERE $where_str };
2770 my @columns = Koha::Database->new()->schema()->resultset('Item')->result_source->columns;
2771 push @columns, Koha::Database->new()->schema()->resultset('Biblio')->result_source->columns;
2772 push @columns, Koha::Database->new()->schema()->resultset('Biblioitem')->result_source->columns;
2773 my $sortby = (0 < grep {$params->{sortby} eq $_} @columns)
2774 ? $params->{sortby} : 'itemnumber';
2775 my $sortorder = (uc($params->{sortorder}) eq 'ASC') ? 'ASC' : 'DESC';
2776 $query .= qq{ ORDER BY $sortby $sortorder };
2778 my $rows = $params->{rows};
2781 my $offset = $rows * ($params->{page}-1);
2782 $query .= qq { LIMIT ?, ? };
2783 push @limit_args, $offset, $rows;
2786 my $sth = $dbh->prepare($query);
2787 my $rv = $sth->execute(@where_args, @limit_args);
2789 return unless ($rv);
2790 my ($total_rows) = $dbh->selectrow_array(q{ SELECT FOUND_ROWS() });
2792 return ($sth->fetchall_arrayref({}), $total_rows);
2796 =head1 OTHER FUNCTIONS
2800 ($indicators, $value) = _find_value($tag, $subfield, $record,$encoding);
2802 Find the given $subfield in the given $tag in the given
2803 MARC::Record $record. If the subfield is found, returns
2804 the (indicators, value) pair; otherwise, (undef, undef) is
2808 Such a function is used in addbiblio AND additem and serial-edit and maybe could be used in Authorities.
2809 I suggest we export it from this module.
2814 my ( $tagfield, $insubfield, $record, $encoding ) = @_;
2817 if ( $tagfield < 10 ) {
2818 if ( $record->field($tagfield) ) {
2819 push @result, $record->field($tagfield)->data();
2824 foreach my $field ( $record->field($tagfield) ) {
2825 my @subfields = $field->subfields();
2826 foreach my $subfield (@subfields) {
2827 if ( @$subfield[0] eq $insubfield ) {
2828 push @result, @$subfield[1];
2829 $indicator = $field->indicator(1) . $field->indicator(2);
2834 return ( $indicator, @result );
2838 =head2 PrepareItemrecordDisplay
2840 PrepareItemrecordDisplay($itemrecord,$bibnum,$itemumber,$frameworkcode);
2842 Returns a hash with all the fields for Display a given item data in a template
2844 The $frameworkcode returns the item for the given frameworkcode, ONLY if bibnum is not provided
2848 sub PrepareItemrecordDisplay {
2850 my ( $bibnum, $itemnum, $defaultvalues, $frameworkcode ) = @_;
2852 my $dbh = C4::Context->dbh;
2853 $frameworkcode = &GetFrameworkCode($bibnum) if $bibnum;
2854 my ( $itemtagfield, $itemtagsubfield ) = &GetMarcFromKohaField( "items.itemnumber", $frameworkcode );
2855 my $tagslib = &GetMarcStructure( 1, $frameworkcode );
2857 # return nothing if we don't have found an existing framework.
2858 return q{} unless $tagslib;
2861 $itemrecord = C4::Items::GetMarcItem( $bibnum, $itemnum );
2865 my $branch_limit = C4::Context->userenv ? C4::Context->userenv->{"branch"} : "";
2867 SELECT authorised_value,lib FROM authorised_values
2870 LEFT JOIN authorised_values_branches ON ( id = av_id )
2875 $query .= qq{ AND ( branchcode = ? OR branchcode IS NULL )} if $branch_limit;
2876 $query .= qq{ ORDER BY lib};
2877 my $authorised_values_sth = $dbh->prepare( $query );
2878 foreach my $tag ( sort keys %{$tagslib} ) {
2879 my $previous_tag = '';
2882 # loop through each subfield
2884 foreach my $subfield ( sort keys %{ $tagslib->{$tag} } ) {
2885 next if IsMarcStructureInternal($tagslib->{$tag}{$subfield});
2886 next if ( $tagslib->{$tag}->{$subfield}->{'tab'} ne "10" );
2888 $subfield_data{tag} = $tag;
2889 $subfield_data{subfield} = $subfield;
2890 $subfield_data{countsubfield} = $cntsubf++;
2891 $subfield_data{kohafield} = $tagslib->{$tag}->{$subfield}->{'kohafield'};
2892 $subfield_data{id} = "tag_".$tag."_subfield_".$subfield."_".int(rand(1000000));
2894 # $subfield_data{marc_lib}=$tagslib->{$tag}->{$subfield}->{lib};
2895 $subfield_data{marc_lib} = $tagslib->{$tag}->{$subfield}->{lib};
2896 $subfield_data{mandatory} = $tagslib->{$tag}->{$subfield}->{mandatory};
2897 $subfield_data{repeatable} = $tagslib->{$tag}->{$subfield}->{repeatable};
2898 $subfield_data{hidden} = "display:none"
2899 if ( ( $tagslib->{$tag}->{$subfield}->{hidden} > 4 )
2900 || ( $tagslib->{$tag}->{$subfield}->{hidden} < -4 ) );
2901 my ( $x, $defaultvalue );
2903 ( $x, $defaultvalue ) = _find_value( $tag, $subfield, $itemrecord );
2905 $defaultvalue = $tagslib->{$tag}->{$subfield}->{defaultvalue} unless $defaultvalue;
2906 if ( !defined $defaultvalue ) {
2907 $defaultvalue = q||;
2909 $defaultvalue =~ s/"/"/g;
2912 # search for itemcallnumber if applicable
2913 if ( $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.itemcallnumber'
2914 && C4::Context->preference('itemcallnumber') ) {
2915 my $CNtag = substr( C4::Context->preference('itemcallnumber'), 0, 3 );
2916 my $CNsubfield = substr( C4::Context->preference('itemcallnumber'), 3, 1 );
2917 if ( $itemrecord and my $field = $itemrecord->field($CNtag) ) {
2918 $defaultvalue = $field->subfield($CNsubfield);
2921 if ( $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.itemcallnumber'
2923 && $defaultvalues->{'callnumber'} ) {
2924 if( $itemrecord and $defaultvalues and not $itemrecord->subfield($tag,$subfield) ){
2925 # if the item record exists, only use default value if the item has no callnumber
2926 $defaultvalue = $defaultvalues->{callnumber};
2927 } elsif ( !$itemrecord and $defaultvalues ) {
2928 # if the item record *doesn't* exists, always use the default value
2929 $defaultvalue = $defaultvalues->{callnumber};
2932 if ( ( $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.holdingbranch' || $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.homebranch' )
2934 && $defaultvalues->{'branchcode'} ) {
2935 if ( $itemrecord and $defaultvalues and not $itemrecord->subfield($tag,$subfield) ) {
2936 $defaultvalue = $defaultvalues->{branchcode};
2939 if ( ( $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.location' )
2941 && $defaultvalues->{'location'} ) {
2943 if ( $itemrecord and $defaultvalues and not $itemrecord->subfield($tag,$subfield) ) {
2944 # if the item record exists, only use default value if the item has no locationr
2945 $defaultvalue = $defaultvalues->{location};
2946 } elsif ( !$itemrecord and $defaultvalues ) {
2947 # if the item record *doesn't* exists, always use the default value
2948 $defaultvalue = $defaultvalues->{location};
2951 if ( $tagslib->{$tag}->{$subfield}->{authorised_value} ) {
2952 my @authorised_values;
2955 # builds list, depending on authorised value...
2957 if ( $tagslib->{$tag}->{$subfield}->{'authorised_value'} eq "branches" ) {
2958 if ( ( C4::Context->preference("IndependentBranches") )
2959 && !C4::Context->IsSuperLibrarian() ) {
2960 my $sth = $dbh->prepare( "SELECT branchcode,branchname FROM branches WHERE branchcode = ? ORDER BY branchname" );
2961 $sth->execute( C4::Context->userenv->{branch} );
2962 push @authorised_values, ""
2963 unless ( $tagslib->{$tag}->{$subfield}->{mandatory} );
2964 while ( my ( $branchcode, $branchname ) = $sth->fetchrow_array ) {
2965 push @authorised_values, $branchcode;
2966 $authorised_lib{$branchcode} = $branchname;
2969 my $sth = $dbh->prepare( "SELECT branchcode,branchname FROM branches ORDER BY branchname" );
2971 push @authorised_values, ""
2972 unless ( $tagslib->{$tag}->{$subfield}->{mandatory} );
2973 while ( my ( $branchcode, $branchname ) = $sth->fetchrow_array ) {
2974 push @authorised_values, $branchcode;
2975 $authorised_lib{$branchcode} = $branchname;
2979 $defaultvalue = C4::Context->userenv ? C4::Context->userenv->{branch} : undef;
2980 if ( $defaultvalues and $defaultvalues->{branchcode} ) {
2981 $defaultvalue = $defaultvalues->{branchcode};
2985 } elsif ( $tagslib->{$tag}->{$subfield}->{authorised_value} eq "itemtypes" ) {
2986 my $itemtypes = GetItemTypes( style => 'array' );
2987 push @authorised_values, ""
2988 unless ( $tagslib->{$tag}->{$subfield}->{mandatory} );
2989 for my $itemtype ( @$itemtypes ) {
2990 push @authorised_values, $itemtype->{itemtype};
2991 $authorised_lib{$itemtype->{itemtype}} = $itemtype->{translated_description};
2994 } elsif ( $tagslib->{$tag}->{$subfield}->{authorised_value} eq "cn_source" ) {
2995 push @authorised_values, "" unless ( $tagslib->{$tag}->{$subfield}->{mandatory} );
2997 my $class_sources = GetClassSources();
2998 my $default_source = C4::Context->preference("DefaultClassificationSource");
3000 foreach my $class_source (sort keys %$class_sources) {
3001 next unless $class_sources->{$class_source}->{'used'} or
3002 ($class_source eq $default_source);
3003 push @authorised_values, $class_source;
3004 $authorised_lib{$class_source} = $class_sources->{$class_source}->{'description'};
3007 $defaultvalue = $default_source;
3009 #---- "true" authorised value
3011 $authorised_values_sth->execute(
3012 $tagslib->{$tag}->{$subfield}->{authorised_value},
3013 $branch_limit ? $branch_limit : ()
3015 push @authorised_values, ""
3016 unless ( $tagslib->{$tag}->{$subfield}->{mandatory} );
3017 while ( my ( $value, $lib ) = $authorised_values_sth->fetchrow_array ) {
3018 push @authorised_values, $value;
3019 $authorised_lib{$value} = $lib;
3022 $subfield_data{marc_value} = {
3024 values => \@authorised_values,
3025 default => "$defaultvalue",
3026 labels => \%authorised_lib,
3028 } elsif ( $tagslib->{$tag}->{$subfield}->{value_builder} ) {
3030 require Koha::FrameworkPlugin;
3031 my $plugin = Koha::FrameworkPlugin->new({
3032 name => $tagslib->{$tag}->{$subfield}->{value_builder},
3035 my $pars = { dbh => $dbh, record => undef, tagslib =>$tagslib, id => $subfield_data{id}, tabloop => undef };
3036 $plugin->build( $pars );
3037 if ( $itemrecord and my $field = $itemrecord->field($tag) ) {
3038 $defaultvalue = $field->subfield($subfield);
3040 if( !$plugin->errstr ) {
3041 #TODO Move html to template; see report 12176/13397
3042 my $tab= $plugin->noclick? '-1': '';
3043 my $class= $plugin->noclick? ' disabled': '';
3044 my $title= $plugin->noclick? 'No popup': 'Tag editor';
3045 $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;
3047 warn $plugin->errstr;
3048 $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
3051 elsif ( $tag eq '' ) { # it's an hidden field
3052 $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" />);
3054 elsif ( $tagslib->{$tag}->{$subfield}->{'hidden'} ) { # FIXME: shouldn't input type be "hidden" ?
3055 $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" />);
3057 elsif ( length($defaultvalue) > 100
3058 or (C4::Context->preference("marcflavour") eq "UNIMARC" and
3059 300 <= $tag && $tag < 400 && $subfield eq 'a' )
3060 or (C4::Context->preference("marcflavour") eq "MARC21" and
3061 500 <= $tag && $tag < 600 )
3063 # oversize field (textarea)
3064 $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");
3066 $subfield_data{marc_value} = "<input type=\"text\" name=\"field_value\" value=\"$defaultvalue\" size=\"50\" maxlength=\"255\" />";
3068 push( @loop_data, \%subfield_data );
3073 if ( $itemrecord && $itemrecord->field($itemtagfield) ) {
3074 $itemnumber = $itemrecord->subfield( $itemtagfield, $itemtagsubfield );
3077 'itemtagfield' => $itemtagfield,
3078 'itemtagsubfield' => $itemtagsubfield,
3079 'itemnumber' => $itemnumber,
3080 'iteminformation' => \@loop_data
3086 my @columns = C4::Items::columns();
3088 Returns an array of items' table columns on success,
3089 and an empty array on failure.
3094 my $rs = Koha::Database->new->schema->resultset('Item');
3095 return $rs->result_source->columns;
3098 =head2 biblioitems_columns
3100 my @columns = C4::Items::biblioitems_columns();
3102 Returns an array of biblioitems' table columns on success,
3103 and an empty array on failure.
3107 sub biblioitems_columns {
3108 my $rs = Koha::Database->new->schema->resultset('Biblioitem');
3109 return $rs->result_source->columns;
3112 sub ToggleNewStatus {
3113 my ( $params ) = @_;
3114 my @rules = @{ $params->{rules} };
3115 my $report_only = $params->{report_only};
3117 my $dbh = C4::Context->dbh;
3119 my @item_columns = map { "items.$_" } C4::Items::columns;
3120 my @biblioitem_columns = map { "biblioitems.$_" } C4::Items::biblioitems_columns;
3122 for my $rule ( @rules ) {
3123 my $age = $rule->{age};
3124 my $conditions = $rule->{conditions};
3125 my $substitutions = $rule->{substitutions};
3129 SELECT items.biblionumber, items.itemnumber
3131 LEFT JOIN biblioitems ON biblioitems.biblionumber = items.biblionumber
3134 for my $condition ( @$conditions ) {
3136 grep {/^$condition->{field}$/} @item_columns
3137 or grep {/^$condition->{field}$/} @biblioitem_columns
3139 if ( $condition->{value} =~ /\|/ ) {
3140 my @values = split /\|/, $condition->{value};
3141 $query .= qq| AND $condition->{field} IN (|
3142 . join( ',', ('?') x scalar @values )
3144 push @params, @values;
3146 $query .= qq| AND $condition->{field} = ?|;
3147 push @params, $condition->{value};
3151 if ( defined $age ) {
3152 $query .= q| AND TO_DAYS(NOW()) - TO_DAYS(dateaccessioned) >= ? |;
3155 my $sth = $dbh->prepare($query);
3156 $sth->execute( @params );
3157 while ( my $values = $sth->fetchrow_hashref ) {
3158 my $biblionumber = $values->{biblionumber};
3159 my $itemnumber = $values->{itemnumber};
3160 my $item = C4::Items::GetItem( $itemnumber );
3161 for my $substitution ( @$substitutions ) {
3162 next unless $substitution->{field};
3163 C4::Items::ModItem( {$substitution->{field} => $substitution->{value}}, $biblionumber, $itemnumber )
3164 unless $report_only;
3165 push @{ $report->{$itemnumber} }, $substitution;