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
24 use vars qw(@ISA @EXPORT);
42 GetItemsByBiblioitemnumber
46 GetItemnumbersForBiblio
47 get_hostitemnumbers_of
48 GetItemnumberFromBarcode
49 GetBarcodeFromItemnumber
60 PrepareItemrecordDisplay
72 use List::MoreUtils qw(any);
74 use DateTime::Format::MySQL;
75 use Data::Dumper; # used as part of logging item record changes, not just for
76 # debugging; so please don't remove this
78 use Koha::AuthorisedValues;
79 use Koha::DateUtils qw(dt_from_string);
82 use Koha::Biblioitems;
85 use Koha::SearchEngine;
86 use Koha::SearchEngine::Search;
91 C4::Items - item management functions
95 This module contains an API for manipulating item
96 records in Koha, and is used by cataloguing, circulation,
97 acquisitions, and serials management.
99 # FIXME This POD is not up-to-date
100 A Koha item record is stored in two places: the
101 items table and embedded in a MARC tag in the XML
102 version of the associated bib record in C<biblioitems.marcxml>.
103 This is done to allow the item information to be readily
104 indexed (e.g., by Zebra), but means that each item
105 modification transaction must keep the items table
106 and the MARC XML in sync at all times.
108 Consequently, all code that creates, modifies, or deletes
109 item records B<must> use an appropriate function from
110 C<C4::Items>. If no existing function is suitable, it is
111 better to add one to C<C4::Items> than to use add
112 one-off SQL statements to add or modify items.
114 The items table will be considered authoritative. In other
115 words, if there is ever a discrepancy between the items
116 table and the MARC XML, the items table should be considered
119 =head1 HISTORICAL NOTE
121 Most of the functions in C<C4::Items> were originally in
122 the C<C4::Biblio> module.
124 =head1 CORE EXPORTED FUNCTIONS
126 The following functions are meant for use by users
133 $item = GetItem($itemnumber,$barcode,$serial);
135 Return item information, for a given itemnumber or barcode.
136 The return value is a hashref mapping item column
137 names to values. If C<$serial> is true, include serial publication data.
142 my ($itemnumber,$barcode, $serial) = @_;
143 my $dbh = C4::Context->dbh;
147 $item = Koha::Items->find( $itemnumber );
149 $item = Koha::Items->find( { barcode => $barcode } );
152 return unless ( $item );
154 my $data = $item->unblessed();
155 $data->{itype} = $item->effective_itemtype(); # set the correct itype
158 my $ssth = $dbh->prepare("SELECT serialseq,publisheddate from serialitems left join serial on serialitems.serialid=serial.serialid where serialitems.itemnumber=?");
159 $ssth->execute( $data->{'itemnumber'} );
160 ( $data->{'serialseq'}, $data->{'publisheddate'} ) = $ssth->fetchrow_array();
168 CartToShelf($itemnumber);
170 Set the current shelving location of the item record
171 to its stored permanent shelving location. This is
172 primarily used to indicate when an item whose current
173 location is a special processing ('PROC') or shelving cart
174 ('CART') location is back in the stacks.
179 my ( $itemnumber ) = @_;
181 unless ( $itemnumber ) {
182 croak "FAILED CartToShelf() - no itemnumber supplied";
185 my $item = GetItem($itemnumber);
186 if ( $item->{location} eq 'CART' ) {
187 $item->{location} = $item->{permanent_location};
188 ModItem($item, undef, $itemnumber);
194 ShelfToCart($itemnumber);
196 Set the current shelving location of the item
197 to shelving cart ('CART').
202 my ( $itemnumber ) = @_;
204 unless ( $itemnumber ) {
205 croak "FAILED ShelfToCart() - no itemnumber supplied";
208 my $item = GetItem($itemnumber);
209 $item->{'location'} = 'CART';
210 ModItem($item, undef, $itemnumber);
213 =head2 AddItemFromMarc
215 my ($biblionumber, $biblioitemnumber, $itemnumber)
216 = AddItemFromMarc($source_item_marc, $biblionumber);
218 Given a MARC::Record object containing an embedded item
219 record and a biblionumber, create a new item record.
223 sub AddItemFromMarc {
224 my ( $source_item_marc, $biblionumber ) = @_;
225 my $dbh = C4::Context->dbh;
227 # parse item hash from MARC
228 my $frameworkcode = C4::Biblio::GetFrameworkCode( $biblionumber );
229 my ($itemtag,$itemsubfield)=C4::Biblio::GetMarcFromKohaField("items.itemnumber",$frameworkcode);
231 my $localitemmarc=MARC::Record->new;
232 $localitemmarc->append_fields($source_item_marc->field($itemtag));
233 my $item = TransformMarcToKoha( $localitemmarc, $frameworkcode ,'items');
234 my $unlinked_item_subfields = _get_unlinked_item_subfields($localitemmarc, $frameworkcode);
235 return AddItem($item, $biblionumber, $dbh, $frameworkcode, $unlinked_item_subfields);
240 my ($biblionumber, $biblioitemnumber, $itemnumber)
241 = AddItem($item, $biblionumber[, $dbh, $frameworkcode, $unlinked_item_subfields]);
243 Given a hash containing item column names as keys,
244 create a new Koha item record.
246 The first two optional parameters (C<$dbh> and C<$frameworkcode>)
247 do not need to be supplied for general use; they exist
248 simply to allow them to be picked up from AddItemFromMarc.
250 The final optional parameter, C<$unlinked_item_subfields>, contains
251 an arrayref containing subfields present in the original MARC
252 representation of the item (e.g., from the item editor) that are
253 not mapped to C<items> columns directly but should instead
254 be stored in C<items.more_subfields_xml> and included in
255 the biblio items tag for display and indexing.
261 my $biblionumber = shift;
263 my $dbh = @_ ? shift : C4::Context->dbh;
264 my $frameworkcode = @_ ? shift : C4::Biblio::GetFrameworkCode($biblionumber);
265 my $unlinked_item_subfields;
267 $unlinked_item_subfields = shift;
270 # needs old biblionumber and biblioitemnumber
271 $item->{'biblionumber'} = $biblionumber;
272 my $sth = $dbh->prepare("SELECT biblioitemnumber FROM biblioitems WHERE biblionumber=?");
273 $sth->execute( $item->{'biblionumber'} );
274 ( $item->{'biblioitemnumber'} ) = $sth->fetchrow;
276 _set_defaults_for_add($item);
277 _set_derived_columns_for_add($item);
278 $item->{'more_subfields_xml'} = _get_unlinked_subfields_xml($unlinked_item_subfields);
280 # FIXME - checks here
281 unless ( $item->{itype} ) { # default to biblioitem.itemtype if no itype
282 my $itype_sth = $dbh->prepare("SELECT itemtype FROM biblioitems WHERE biblionumber = ?");
283 $itype_sth->execute( $item->{'biblionumber'} );
284 ( $item->{'itype'} ) = $itype_sth->fetchrow_array;
287 my ( $itemnumber, $error ) = _koha_new_item( $item, $item->{barcode} );
290 $item->{'itemnumber'} = $itemnumber;
292 ModZebra( $item->{biblionumber}, "specialUpdate", "biblioserver" );
294 logaction( "CATALOGUING", "ADD", $itemnumber, "item" )
295 if C4::Context->preference("CataloguingLog");
297 return ( $item->{biblionumber}, $item->{biblioitemnumber}, $itemnumber );
300 =head2 AddItemBatchFromMarc
302 ($itemnumber_ref, $error_ref) = AddItemBatchFromMarc($record,
303 $biblionumber, $biblioitemnumber, $frameworkcode);
305 Efficiently create item records from a MARC biblio record with
306 embedded item fields. This routine is suitable for batch jobs.
308 This API assumes that the bib record has already been
309 saved to the C<biblio> and C<biblioitems> tables. It does
310 not expect that C<biblio_metadata.metadata> is populated, but it
311 will do so via a call to ModBibiloMarc.
313 The goal of this API is to have a similar effect to using AddBiblio
314 and AddItems in succession, but without inefficient repeated
315 parsing of the MARC XML bib record.
317 This function returns an arrayref of new itemsnumbers and an arrayref of item
318 errors encountered during the processing. Each entry in the errors
319 list is a hashref containing the following keys:
325 Sequence number of original item tag in the MARC record.
329 Item barcode, provide to assist in the construction of
330 useful error messages.
334 Code representing the error condition. Can be 'duplicate_barcode',
335 'invalid_homebranch', or 'invalid_holdingbranch'.
337 =item error_information
339 Additional information appropriate to the error condition.
345 sub AddItemBatchFromMarc {
346 my ($record, $biblionumber, $biblioitemnumber, $frameworkcode) = @_;
348 my @itemnumbers = ();
350 my $dbh = C4::Context->dbh;
352 # We modify the record, so lets work on a clone so we don't change the
354 $record = $record->clone();
355 # loop through the item tags and start creating items
356 my @bad_item_fields = ();
357 my ($itemtag, $itemsubfield) = C4::Biblio::GetMarcFromKohaField("items.itemnumber",'');
358 my $item_sequence_num = 0;
359 ITEMFIELD: foreach my $item_field ($record->field($itemtag)) {
360 $item_sequence_num++;
361 # we take the item field and stick it into a new
362 # MARC record -- this is required so far because (FIXME)
363 # TransformMarcToKoha requires a MARC::Record, not a MARC::Field
364 # and there is no TransformMarcFieldToKoha
365 my $temp_item_marc = MARC::Record->new();
366 $temp_item_marc->append_fields($item_field);
368 # add biblionumber and biblioitemnumber
369 my $item = TransformMarcToKoha( $temp_item_marc, $frameworkcode, 'items' );
370 my $unlinked_item_subfields = _get_unlinked_item_subfields($temp_item_marc, $frameworkcode);
371 $item->{'more_subfields_xml'} = _get_unlinked_subfields_xml($unlinked_item_subfields);
372 $item->{'biblionumber'} = $biblionumber;
373 $item->{'biblioitemnumber'} = $biblioitemnumber;
375 # check for duplicate barcode
376 my %item_errors = CheckItemPreSave($item);
378 push @errors, _repack_item_errors($item_sequence_num, $item, \%item_errors);
379 push @bad_item_fields, $item_field;
383 _set_defaults_for_add($item);
384 _set_derived_columns_for_add($item);
385 my ( $itemnumber, $error ) = _koha_new_item( $item, $item->{barcode} );
386 warn $error if $error;
387 push @itemnumbers, $itemnumber; # FIXME not checking error
388 $item->{'itemnumber'} = $itemnumber;
390 logaction("CATALOGUING", "ADD", $itemnumber, "item") if C4::Context->preference("CataloguingLog");
392 my $new_item_marc = _marc_from_item_hash($item, $frameworkcode, $unlinked_item_subfields);
393 $item_field->replace_with($new_item_marc->field($itemtag));
396 # remove any MARC item fields for rejected items
397 foreach my $item_field (@bad_item_fields) {
398 $record->delete_field($item_field);
401 # update the MARC biblio
402 # $biblionumber = ModBiblioMarc( $record, $biblionumber, $frameworkcode );
404 return (\@itemnumbers, \@errors);
407 =head2 ModItemFromMarc
409 ModItemFromMarc($item_marc, $biblionumber, $itemnumber);
411 This function updates an item record based on a supplied
412 C<MARC::Record> object containing an embedded item field.
413 This API is meant for the use of C<additem.pl>; for
414 other purposes, C<ModItem> should be used.
416 This function uses the hash %default_values_for_mod_from_marc,
417 which contains default values for item fields to
418 apply when modifying an item. This is needed because
419 if an item field's value is cleared, TransformMarcToKoha
420 does not include the column in the
421 hash that's passed to ModItem, which without
422 use of this hash makes it impossible to clear
423 an item field's value. See bug 2466.
425 Note that only columns that can be directly
426 changed from the cataloging and serials
427 item editors are included in this hash.
433 sub _build_default_values_for_mod_marc {
434 # Has no framework parameter anymore, since Default is authoritative
435 # for Koha to MARC mappings.
437 my $cache = Koha::Caches->get_instance();
438 my $cache_key = "default_value_for_mod_marc-";
439 my $cached = $cache->get_from_cache($cache_key);
440 return $cached if $cached;
442 my $default_values = {
444 booksellerid => undef,
446 'items.cn_source' => undef,
447 coded_location_qualifier => undef,
451 holdingbranch => undef,
453 itemcallnumber => undef,
456 itemnotes_nonpublic => undef,
459 permanent_location => undef,
463 # paidfor => undef, # commented, see bug 12817
465 replacementprice => undef,
466 replacementpricedate => undef,
469 stocknumber => undef,
473 my %default_values_for_mod_from_marc;
474 while ( my ( $field, $default_value ) = each %$default_values ) {
475 my $kohafield = $field;
476 $kohafield =~ s|^([^\.]+)$|items.$1|;
477 $default_values_for_mod_from_marc{$field} = $default_value
478 if C4::Biblio::GetMarcFromKohaField( $kohafield );
481 $cache->set_in_cache($cache_key, \%default_values_for_mod_from_marc);
482 return \%default_values_for_mod_from_marc;
485 sub ModItemFromMarc {
486 my $item_marc = shift;
487 my $biblionumber = shift;
488 my $itemnumber = shift;
490 my $frameworkcode = C4::Biblio::GetFrameworkCode($biblionumber);
491 my ( $itemtag, $itemsubfield ) = C4::Biblio::GetMarcFromKohaField( "items.itemnumber", $frameworkcode );
493 my $localitemmarc = MARC::Record->new;
494 $localitemmarc->append_fields( $item_marc->field($itemtag) );
495 my $item = TransformMarcToKoha( $localitemmarc, $frameworkcode, 'items' );
496 my $default_values = _build_default_values_for_mod_marc();
497 foreach my $item_field ( keys %$default_values ) {
498 $item->{$item_field} = $default_values->{$item_field}
499 unless exists $item->{$item_field};
501 my $unlinked_item_subfields = _get_unlinked_item_subfields( $localitemmarc, $frameworkcode );
503 ModItem( $item, $biblionumber, $itemnumber, { unlinked_item_subfields => $unlinked_item_subfields } );
510 { column => $newvalue },
514 [ unlinked_item_subfields => $unlinked_item_subfields, ]
519 Change one or more columns in an item record and update
520 the MARC representation of the item.
522 The first argument is a hashref mapping from item column
523 names to the new values. The second and third arguments
524 are the biblionumber and itemnumber, respectively.
525 The fourth, optional parameter (additional_params) may contain the keys
526 unlinked_item_subfields and log_action.
528 C<$unlinked_item_subfields> contains an arrayref containing
529 subfields present in the original MARC
530 representation of the item (e.g., from the item editor) that are
531 not mapped to C<items> columns directly but should instead
532 be stored in C<items.more_subfields_xml> and included in
533 the biblio items tag for display and indexing.
535 If one of the changed columns is used to calculate
536 the derived value of a column such as C<items.cn_sort>,
537 this routine will perform the necessary calculation
540 If log_action is set to false, the action will not be logged.
541 If log_action is true or undefined, the action will be logged.
546 my ( $item, $biblionumber, $itemnumber, $additional_params ) = @_;
547 my $log_action = $additional_params->{log_action} // 1;
548 my $unlinked_item_subfields = $additional_params->{unlinked_item_subfields};
550 return unless %$item;
551 $item->{'itemnumber'} = $itemnumber or return;
553 # if $biblionumber is undefined, get it from the current item
554 unless (defined $biblionumber) {
555 $biblionumber = _get_single_item_column('biblionumber', $itemnumber);
558 if ($unlinked_item_subfields) {
559 $item->{'more_subfields_xml'} = _get_unlinked_subfields_xml($unlinked_item_subfields);
562 my @fields = qw( itemlost withdrawn damaged );
564 # Only call GetItem if we need to set an "on" date field
565 if ( $item->{itemlost} || $item->{withdrawn} || $item->{damaged} ) {
566 my $pre_mod_item = GetItem( $item->{'itemnumber'} );
567 for my $field (@fields) {
568 if ( defined( $item->{$field} )
569 and not $pre_mod_item->{$field}
570 and $item->{$field} )
572 $item->{ $field . '_on' } =
573 DateTime::Format::MySQL->format_datetime( dt_from_string() );
578 # If the field is defined but empty, we are removing and,
579 # and thus need to clear out the 'on' field as well
580 for my $field (@fields) {
581 if ( defined( $item->{$field} ) && !$item->{$field} ) {
582 $item->{ $field . '_on' } = undef;
587 _set_derived_columns_for_mod($item);
588 _do_column_fixes_for_mod($item);
591 # attempt to change itemnumber
592 # attempt to change biblionumber (if we want
593 # an API to relink an item to a different bib,
594 # it should be a separate function)
597 _koha_modify_item($item);
599 # request that bib be reindexed so that searching on current
600 # item status is possible
601 ModZebra( $biblionumber, "specialUpdate", "biblioserver" );
603 logaction( "CATALOGUING", "MODIFY", $itemnumber, "item " . Dumper($item) )
604 if $log_action && C4::Context->preference("CataloguingLog");
607 =head2 ModItemTransfer
609 ModItemTransfer($itenumber, $frombranch, $tobranch);
611 Marks an item as being transferred from one branch
616 sub ModItemTransfer {
617 my ( $itemnumber, $frombranch, $tobranch ) = @_;
619 my $dbh = C4::Context->dbh;
621 # Remove the 'shelving cart' location status if it is being used.
622 CartToShelf( $itemnumber ) if ( C4::Context->preference("ReturnToShelvingCart") );
624 $dbh->do("UPDATE branchtransfers SET datearrived = NOW(), comments = ? WHERE itemnumber = ? AND datearrived IS NULL", undef, "Canceled, new transfer from $frombranch to $tobranch created", $itemnumber);
626 #new entry in branchtransfers....
627 my $sth = $dbh->prepare(
628 "INSERT INTO branchtransfers (itemnumber, frombranch, datesent, tobranch)
629 VALUES (?, ?, NOW(), ?)");
630 $sth->execute($itemnumber, $frombranch, $tobranch);
632 ModItem({ holdingbranch => $tobranch }, undef, $itemnumber);
633 ModDateLastSeen($itemnumber);
637 =head2 ModDateLastSeen
639 ModDateLastSeen( $itemnumber, $leave_item_lost );
641 Mark item as seen. Is called when an item is issued, returned or manually marked during inventory/stocktaking.
642 C<$itemnumber> is the item number
643 C<$leave_item_lost> determines if a lost item will be found or remain lost
647 sub ModDateLastSeen {
648 my ( $itemnumber, $leave_item_lost ) = @_;
650 my $today = output_pref({ dt => dt_from_string, dateformat => 'iso', dateonly => 1 });
653 $params->{datelastseen} = $today;
654 $params->{itemlost} = 0 unless $leave_item_lost;
656 ModItem( $params, undef, $itemnumber, { log_action => 0 } );
661 DelItem({ itemnumber => $itemnumber, [ biblionumber => $biblionumber ] } );
663 Exported function (core API) for deleting an item record in Koha.
670 my $itemnumber = $params->{itemnumber};
671 my $biblionumber = $params->{biblionumber};
673 unless ($biblionumber) {
674 my $item = Koha::Items->find( $itemnumber );
675 $biblionumber = $item ? $item->biblio->biblionumber : undef;
678 # If there is no biblionumber for the given itemnumber, there is nothing to delete
679 return 0 unless $biblionumber;
681 # FIXME check the item has no current issues
682 my $deleted = _koha_delete_item( $itemnumber );
684 ModZebra( $biblionumber, "specialUpdate", "biblioserver" );
686 #search item field code
687 logaction("CATALOGUING", "DELETE", $itemnumber, "item") if C4::Context->preference("CataloguingLog");
691 =head2 CheckItemPreSave
693 my $item_ref = TransformMarcToKoha($marc, 'items');
695 my %errors = CheckItemPreSave($item_ref);
696 if (exists $errors{'duplicate_barcode'}) {
697 print "item has duplicate barcode: ", $errors{'duplicate_barcode'}, "\n";
698 } elsif (exists $errors{'invalid_homebranch'}) {
699 print "item has invalid home branch: ", $errors{'invalid_homebranch'}, "\n";
700 } elsif (exists $errors{'invalid_holdingbranch'}) {
701 print "item has invalid holding branch: ", $errors{'invalid_holdingbranch'}, "\n";
706 Given a hashref containing item fields, determine if it can be
707 inserted or updated in the database. Specifically, checks for
708 database integrity issues, and returns a hash containing any
709 of the following keys, if applicable.
713 =item duplicate_barcode
715 Barcode, if it duplicates one already found in the database.
717 =item invalid_homebranch
719 Home branch, if not defined in branches table.
721 =item invalid_holdingbranch
723 Holding branch, if not defined in branches table.
727 This function does NOT implement any policy-related checks,
728 e.g., whether current operator is allowed to save an
729 item that has a given branch code.
733 sub CheckItemPreSave {
734 my $item_ref = shift;
738 # check for duplicate barcode
739 if (exists $item_ref->{'barcode'} and defined $item_ref->{'barcode'}) {
740 my $existing_itemnumber = GetItemnumberFromBarcode($item_ref->{'barcode'});
741 if ($existing_itemnumber) {
742 if (!exists $item_ref->{'itemnumber'} # new item
743 or $item_ref->{'itemnumber'} != $existing_itemnumber) { # existing item
744 $errors{'duplicate_barcode'} = $item_ref->{'barcode'};
749 # check for valid home branch
750 if (exists $item_ref->{'homebranch'} and defined $item_ref->{'homebranch'}) {
751 my $home_library = Koha::Libraries->find( $item_ref->{homebranch} );
752 unless (defined $home_library) {
753 $errors{'invalid_homebranch'} = $item_ref->{'homebranch'};
757 # check for valid holding branch
758 if (exists $item_ref->{'holdingbranch'} and defined $item_ref->{'holdingbranch'}) {
759 my $holding_library = Koha::Libraries->find( $item_ref->{holdingbranch} );
760 unless (defined $holding_library) {
761 $errors{'invalid_holdingbranch'} = $item_ref->{'holdingbranch'};
769 =head1 EXPORTED SPECIAL ACCESSOR FUNCTIONS
771 The following functions provide various ways of
772 getting an item record, a set of item records, or
773 lists of authorized values for certain item fields.
775 Some of the functions in this group are candidates
776 for refactoring -- for example, some of the code
777 in C<GetItemsByBiblioitemnumber> and C<GetItemsInfo>
778 has copy-and-paste work.
782 =head2 GetItemsForInventory
784 ($itemlist, $iTotalRecords) = GetItemsForInventory( {
785 minlocation => $minlocation,
786 maxlocation => $maxlocation,
787 location => $location,
788 itemtype => $itemtype,
789 ignoreissued => $ignoreissued,
790 datelastseen => $datelastseen,
791 branchcode => $branchcode,
795 statushash => $statushash,
798 Retrieve a list of title/authors/barcode/callnumber, for biblio inventory.
800 The sub returns a reference to a list of hashes, each containing
801 itemnumber, author, title, barcode, item callnumber, and date last
802 seen. It is ordered by callnumber then title.
804 The required minlocation & maxlocation parameters are used to specify a range of item callnumbers
805 the datelastseen can be used to specify that you want to see items not seen since a past date only.
806 offset & size can be used to retrieve only a part of the whole listing (defaut behaviour)
807 $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.
809 $iTotalRecords is the number of rows that would have been returned without the $offset, $size limit clause
813 sub GetItemsForInventory {
814 my ( $parameters ) = @_;
815 my $minlocation = $parameters->{'minlocation'} // '';
816 my $maxlocation = $parameters->{'maxlocation'} // '';
817 my $location = $parameters->{'location'} // '';
818 my $itemtype = $parameters->{'itemtype'} // '';
819 my $ignoreissued = $parameters->{'ignoreissued'} // '';
820 my $datelastseen = $parameters->{'datelastseen'} // '';
821 my $branchcode = $parameters->{'branchcode'} // '';
822 my $branch = $parameters->{'branch'} // '';
823 my $offset = $parameters->{'offset'} // '';
824 my $size = $parameters->{'size'} // '';
825 my $statushash = $parameters->{'statushash'} // '';
827 my $dbh = C4::Context->dbh;
828 my ( @bind_params, @where_strings );
830 my $select_columns = q{
831 SELECT items.itemnumber, barcode, itemcallnumber, title, author, biblio.biblionumber, biblio.frameworkcode, datelastseen, homebranch, location, notforloan, damaged, itemlost, withdrawn, stocknumber
833 my $select_count = q{SELECT COUNT(*)};
836 LEFT JOIN biblio ON items.biblionumber = biblio.biblionumber
837 LEFT JOIN biblioitems on items.biblionumber = biblioitems.biblionumber
840 for my $authvfield (keys %$statushash){
841 if ( scalar @{$statushash->{$authvfield}} > 0 ){
842 my $joinedvals = join ',', @{$statushash->{$authvfield}};
843 push @where_strings, "$authvfield in (" . $joinedvals . ")";
849 push @where_strings, 'itemcallnumber >= ?';
850 push @bind_params, $minlocation;
854 push @where_strings, 'itemcallnumber <= ?';
855 push @bind_params, $maxlocation;
859 $datelastseen = output_pref({ str => $datelastseen, dateformat => 'iso', dateonly => 1 });
860 push @where_strings, '(datelastseen < ? OR datelastseen IS NULL)';
861 push @bind_params, $datelastseen;
865 push @where_strings, 'items.location = ?';
866 push @bind_params, $location;
870 if($branch eq "homebranch"){
871 push @where_strings, 'items.homebranch = ?';
873 push @where_strings, 'items.holdingbranch = ?';
875 push @bind_params, $branchcode;
879 push @where_strings, 'biblioitems.itemtype = ?';
880 push @bind_params, $itemtype;
883 if ( $ignoreissued) {
884 $query .= "LEFT JOIN issues ON items.itemnumber = issues.itemnumber ";
885 push @where_strings, 'issues.date_due IS NULL';
888 if ( @where_strings ) {
890 $query .= join ' AND ', @where_strings;
892 my $count_query = $select_count . $query;
893 $query .= ' ORDER BY items.cn_sort, itemcallnumber, title';
894 $query .= " LIMIT $offset, $size" if ($offset and $size);
895 $query = $select_columns . $query;
896 my $sth = $dbh->prepare($query);
897 $sth->execute( @bind_params );
900 my $tmpresults = $sth->fetchall_arrayref({});
901 $sth = $dbh->prepare( $count_query );
902 $sth->execute( @bind_params );
903 my ($iTotalRecords) = $sth->fetchrow_array();
905 my @avs = Koha::AuthorisedValues->search(
906 { 'marc_subfield_structures.kohafield' => { '>' => '' },
907 'me.authorised_value' => { '>' => '' },
909 { join => { category => 'marc_subfield_structures' },
910 distinct => ['marc_subfield_structures.kohafield, me.category, frameworkcode, me.authorised_value'],
911 '+select' => [ 'marc_subfield_structures.kohafield', 'marc_subfield_structures.frameworkcode', 'me.authorised_value', 'me.lib' ],
912 '+as' => [ 'kohafield', 'frameworkcode', 'authorised_value', 'lib' ],
916 my $avmapping = { map { $_->get_column('kohafield') . ',' . $_->get_column('frameworkcode') . ',' . $_->get_column('authorised_value') => $_->get_column('lib') } @avs };
918 foreach my $row (@$tmpresults) {
921 foreach (keys %$row) {
922 if (defined($avmapping->{"items.$_,".$row->{'frameworkcode'}.",".$row->{$_}})) {
923 $row->{$_} = $avmapping->{"items.$_,".$row->{'frameworkcode'}.",".$row->{$_}};
929 return (\@results, $iTotalRecords);
932 =head2 GetItemsByBiblioitemnumber
934 GetItemsByBiblioitemnumber($biblioitemnumber);
936 Returns an arrayref of hashrefs suitable for use in a TMPL_LOOP
937 Called by C<C4::XISBN>
941 sub GetItemsByBiblioitemnumber {
942 my ( $bibitem ) = @_;
943 warn "C4::Items::GetItemsByBiblioitemnumber will be deprecated as of 18.11. - See Bug 21202 for details\n";
944 my $dbh = C4::Context->dbh;
945 my $sth = $dbh->prepare("SELECT * FROM items WHERE items.biblioitemnumber = ?") || die $dbh->errstr;
946 # Get all items attached to a biblioitem
949 $sth->execute($bibitem) || die $sth->errstr;
950 while ( my $data = $sth->fetchrow_hashref ) {
951 # Foreach item, get circulation information
952 my $sth2 = $dbh->prepare( "SELECT * FROM issues,borrowers
954 AND issues.borrowernumber = borrowers.borrowernumber"
956 $sth2->execute( $data->{'itemnumber'} );
957 if ( my $data2 = $sth2->fetchrow_hashref ) {
958 # if item is out, set the due date and who it is out too
959 $data->{'date_due'} = $data2->{'date_due'};
960 $data->{'cardnumber'} = $data2->{'cardnumber'};
961 $data->{'borrowernumber'} = $data2->{'borrowernumber'};
964 # set date_due to blank, so in the template we check itemlost, and withdrawn
965 $data->{'date_due'} = '';
967 # Find the last 3 people who borrowed this item.
968 my $query2 = "SELECT * FROM old_issues, borrowers WHERE itemnumber = ?
969 AND old_issues.borrowernumber = borrowers.borrowernumber
970 ORDER BY returndate desc,timestamp desc LIMIT 3";
971 $sth2 = $dbh->prepare($query2) || die $dbh->errstr;
972 $sth2->execute( $data->{'itemnumber'} ) || die $sth2->errstr;
974 while ( my $data2 = $sth2->fetchrow_hashref ) {
975 $data->{"timestamp$i2"} = $data2->{'timestamp'};
976 $data->{"card$i2"} = $data2->{'cardnumber'};
977 $data->{"borrower$i2"} = $data2->{'borrowernumber'};
980 push(@results,$data);
987 @results = GetItemsInfo($biblionumber);
989 Returns information about items with the given biblionumber.
991 C<GetItemsInfo> returns a list of references-to-hash. Each element
992 contains a number of keys. Most of them are attributes from the
993 C<biblio>, C<biblioitems>, C<items>, and C<itemtypes> tables in the
994 Koha database. Other keys include:
998 =item C<$data-E<gt>{branchname}>
1000 The name (not the code) of the branch to which the book belongs.
1002 =item C<$data-E<gt>{datelastseen}>
1004 This is simply C<items.datelastseen>, except that while the date is
1005 stored in YYYY-MM-DD format in the database, here it is converted to
1006 DD/MM/YYYY format. A NULL date is returned as C<//>.
1008 =item C<$data-E<gt>{datedue}>
1010 =item C<$data-E<gt>{class}>
1012 This is the concatenation of C<biblioitems.classification>, the book's
1013 Dewey code, and C<biblioitems.subclass>.
1015 =item C<$data-E<gt>{ocount}>
1017 I think this is the number of copies of the book available.
1019 =item C<$data-E<gt>{order}>
1021 If this is set, it is set to C<One Order>.
1028 my ( $biblionumber ) = @_;
1029 my $dbh = C4::Context->dbh;
1030 require C4::Languages;
1031 my $language = C4::Languages::getlanguage();
1037 biblioitems.itemtype,
1040 biblioitems.publicationyear,
1041 biblioitems.publishercode,
1042 biblioitems.volumedate,
1043 biblioitems.volumedesc,
1046 items.notforloan as itemnotforloan,
1047 issues.borrowernumber,
1048 issues.date_due as datedue,
1049 issues.onsite_checkout,
1050 borrowers.cardnumber,
1052 borrowers.firstname,
1053 borrowers.branchcode as bcode,
1055 serial.publisheddate,
1056 itemtypes.description,
1057 COALESCE( localization.translation, itemtypes.description ) AS translated_description,
1058 itemtypes.notforloan as notforloan_per_itemtype,
1062 holding.opac_info as holding_branch_opac_info,
1063 home.opac_info as home_branch_opac_info
1067 LEFT JOIN branches AS holding ON items.holdingbranch = holding.branchcode
1068 LEFT JOIN branches AS home ON items.homebranch=home.branchcode
1069 LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber
1070 LEFT JOIN biblioitems ON biblioitems.biblioitemnumber = items.biblioitemnumber
1071 LEFT JOIN issues USING (itemnumber)
1072 LEFT JOIN borrowers USING (borrowernumber)
1073 LEFT JOIN serialitems USING (itemnumber)
1074 LEFT JOIN serial USING (serialid)
1075 LEFT JOIN itemtypes ON itemtypes.itemtype = "
1076 . (C4::Context->preference('item-level_itypes') ? 'items.itype' : 'biblioitems.itemtype');
1078 LEFT JOIN localization ON itemtypes.itemtype = localization.code
1079 AND localization.entity = 'itemtypes'
1080 AND localization.lang = ?
1083 $query .= " WHERE items.biblionumber = ? ORDER BY home.branchname, items.enumchron, LPAD( items.copynumber, 8, '0' ), items.dateaccessioned DESC" ;
1084 my $sth = $dbh->prepare($query);
1085 $sth->execute($language, $biblionumber);
1090 my $userenv = C4::Context->userenv;
1091 my $want_not_same_branch = C4::Context->preference("IndependentBranches") && !C4::Context->IsSuperLibrarian();
1092 while ( my $data = $sth->fetchrow_hashref ) {
1093 if ( $data->{borrowernumber} && $want_not_same_branch) {
1094 $data->{'NOTSAMEBRANCH'} = $data->{'bcode'} ne $userenv->{branch};
1097 $serial ||= $data->{'serial'};
1100 # get notforloan complete status if applicable
1101 $descriptions = Koha::AuthorisedValues->get_description_by_koha_field({frameworkcode => $data->{frameworkcode}, kohafield => 'items.notforloan', authorised_value => $data->{itemnotforloan} });
1102 $data->{notforloanvalue} = $descriptions->{lib} // '';
1103 $data->{notforloanvalueopac} = $descriptions->{opac_description} // '';
1105 # get restricted status and description if applicable
1106 $descriptions = Koha::AuthorisedValues->get_description_by_koha_field({frameworkcode => $data->{frameworkcode}, kohafield => 'items.restricted', authorised_value => $data->{restricted} });
1107 $data->{restricted} = $descriptions->{lib} // '';
1108 $data->{restrictedopac} = $descriptions->{opac_description} // '';
1110 # my stack procedures
1111 $descriptions = Koha::AuthorisedValues->get_description_by_koha_field({frameworkcode => $data->{frameworkcode}, kohafield => 'items.stack', authorised_value => $data->{stack} });
1112 $data->{stack} = $descriptions->{lib} // '';
1114 # Find the last 3 people who borrowed this item.
1115 my $sth2 = $dbh->prepare("SELECT * FROM old_issues,borrowers
1116 WHERE itemnumber = ?
1117 AND old_issues.borrowernumber = borrowers.borrowernumber
1118 ORDER BY returndate DESC
1120 $sth2->execute($data->{'itemnumber'});
1122 while (my $data2 = $sth2->fetchrow_hashref()) {
1123 $data->{"timestamp$ii"} = $data2->{'timestamp'} if $data2->{'timestamp'};
1124 $data->{"card$ii"} = $data2->{'cardnumber'} if $data2->{'cardnumber'};
1125 $data->{"borrower$ii"} = $data2->{'borrowernumber'} if $data2->{'borrowernumber'};
1129 $results[$i] = $data;
1134 ? sort { ($b->{'publisheddate'} || $b->{'enumchron'}) cmp ($a->{'publisheddate'} || $a->{'enumchron'}) } @results
1138 =head2 GetItemsLocationInfo
1140 my @itemlocinfo = GetItemsLocationInfo($biblionumber);
1142 Returns the branch names, shelving location and itemcallnumber for each item attached to the biblio in question
1144 C<GetItemsInfo> returns a list of references-to-hash. Data returned:
1148 =item C<$data-E<gt>{homebranch}>
1150 Branch Name of the item's homebranch
1152 =item C<$data-E<gt>{holdingbranch}>
1154 Branch Name of the item's holdingbranch
1156 =item C<$data-E<gt>{location}>
1158 Item's shelving location code
1160 =item C<$data-E<gt>{location_intranet}>
1162 The intranet description for the Shelving Location as set in authorised_values 'LOC'
1164 =item C<$data-E<gt>{location_opac}>
1166 The OPAC description for the Shelving Location as set in authorised_values 'LOC'. Falls back to intranet description if no OPAC
1169 =item C<$data-E<gt>{itemcallnumber}>
1171 Item's itemcallnumber
1173 =item C<$data-E<gt>{cn_sort}>
1175 Item's call number normalized for sorting
1181 sub GetItemsLocationInfo {
1182 my $biblionumber = shift;
1185 my $dbh = C4::Context->dbh;
1186 my $query = "SELECT a.branchname as homebranch, b.branchname as holdingbranch,
1187 location, itemcallnumber, cn_sort
1188 FROM items, branches as a, branches as b
1189 WHERE homebranch = a.branchcode AND holdingbranch = b.branchcode
1190 AND biblionumber = ?
1191 ORDER BY cn_sort ASC";
1192 my $sth = $dbh->prepare($query);
1193 $sth->execute($biblionumber);
1195 while ( my $data = $sth->fetchrow_hashref ) {
1196 my $av = Koha::AuthorisedValues->search({ category => 'LOC', authorised_value => $data->{location} });
1197 $av = $av->count ? $av->next : undef;
1198 $data->{location_intranet} = $av ? $av->lib : '';
1199 $data->{location_opac} = $av ? $av->opac_description : '';
1200 push @results, $data;
1205 =head2 GetHostItemsInfo
1207 $hostiteminfo = GetHostItemsInfo($hostfield);
1208 Returns the iteminfo for items linked to records via a host field
1212 sub GetHostItemsInfo {
1214 my @returnitemsInfo;
1216 if( !C4::Context->preference('EasyAnalyticalRecords') ) {
1217 return @returnitemsInfo;
1221 if( C4::Context->preference('marcflavour') eq 'MARC21' ||
1222 C4::Context->preference('marcflavour') eq 'NORMARC') {
1223 @fields = $record->field('773');
1224 } elsif( C4::Context->preference('marcflavour') eq 'UNIMARC') {
1225 @fields = $record->field('461');
1228 foreach my $hostfield ( @fields ) {
1229 my $hostbiblionumber = $hostfield->subfield("0");
1230 my $linkeditemnumber = $hostfield->subfield("9");
1231 my @hostitemInfos = GetItemsInfo($hostbiblionumber);
1232 foreach my $hostitemInfo (@hostitemInfos) {
1233 if( $hostitemInfo->{itemnumber} eq $linkeditemnumber ) {
1234 push @returnitemsInfo, $hostitemInfo;
1239 return @returnitemsInfo;
1242 =head2 GetLastAcquisitions
1244 my $lastacq = GetLastAcquisitions({'branches' => ('branch1','branch2'),
1245 'itemtypes' => ('BK','BD')}, 10);
1249 sub GetLastAcquisitions {
1250 my ($data,$max) = @_;
1252 my $itemtype = C4::Context->preference('item-level_itypes') ? 'itype' : 'itemtype';
1254 my $number_of_branches = @{$data->{branches}};
1255 my $number_of_itemtypes = @{$data->{itemtypes}};
1258 my @where = ('WHERE 1 ');
1259 $number_of_branches and push @where
1260 , 'AND holdingbranch IN ('
1261 , join(',', ('?') x $number_of_branches )
1265 $number_of_itemtypes and push @where
1266 , "AND $itemtype IN ("
1267 , join(',', ('?') x $number_of_itemtypes )
1271 my $query = "SELECT biblio.biblionumber as biblionumber, title, dateaccessioned
1272 FROM items RIGHT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
1273 RIGHT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber)
1275 GROUP BY biblio.biblionumber
1276 ORDER BY dateaccessioned DESC LIMIT $max";
1278 my $dbh = C4::Context->dbh;
1279 my $sth = $dbh->prepare($query);
1281 $sth->execute((@{$data->{branches}}, @{$data->{itemtypes}}));
1284 while( my $row = $sth->fetchrow_hashref){
1285 push @results, {date => $row->{dateaccessioned}
1286 , biblionumber => $row->{biblionumber}
1287 , title => $row->{title}};
1293 =head2 GetItemnumbersForBiblio
1295 my $itemnumbers = GetItemnumbersForBiblio($biblionumber);
1297 Given a single biblionumber, return an arrayref of all the corresponding itemnumbers
1301 sub GetItemnumbersForBiblio {
1302 my $biblionumber = shift;
1304 my $dbh = C4::Context->dbh;
1305 my $sth = $dbh->prepare("SELECT itemnumber FROM items WHERE biblionumber = ?");
1306 $sth->execute($biblionumber);
1307 while (my $result = $sth->fetchrow_hashref) {
1308 push @items, $result->{'itemnumber'};
1313 =head2 get_hostitemnumbers_of
1315 my @itemnumbers_of = get_hostitemnumbers_of($biblionumber);
1317 Given a biblionumber, return the list of corresponding itemnumbers that are linked to it via host fields
1319 Return a reference on a hash where key is a biblionumber and values are
1320 references on array of itemnumbers.
1325 sub get_hostitemnumbers_of {
1326 my ($biblionumber) = @_;
1327 my $marcrecord = C4::Biblio::GetMarcBiblio({ biblionumber => $biblionumber });
1329 return unless $marcrecord;
1331 my ( @returnhostitemnumbers, $tag, $biblio_s, $item_s );
1333 my $marcflavor = C4::Context->preference('marcflavour');
1334 if ( $marcflavor eq 'MARC21' || $marcflavor eq 'NORMARC' ) {
1339 elsif ( $marcflavor eq 'UNIMARC' ) {
1345 foreach my $hostfield ( $marcrecord->field($tag) ) {
1346 my $hostbiblionumber = $hostfield->subfield($biblio_s);
1347 next unless $hostbiblionumber; # have tag, don't have $biblio_s subfield
1348 my $linkeditemnumber = $hostfield->subfield($item_s);
1349 if ( ! $linkeditemnumber ) {
1350 warn "ERROR biblionumber $biblionumber has 773^0, but doesn't have 9";
1353 my $is_from_biblio = Koha::Items->search({ itemnumber => $linkeditemnumber, biblionumber => $hostbiblionumber });
1354 push @returnhostitemnumbers, $linkeditemnumber
1358 return @returnhostitemnumbers;
1362 =head2 GetItemnumberFromBarcode
1364 $result = GetItemnumberFromBarcode($barcode);
1368 sub GetItemnumberFromBarcode {
1370 my $dbh = C4::Context->dbh;
1373 $dbh->prepare("SELECT itemnumber FROM items WHERE items.barcode=?");
1374 $rq->execute($barcode);
1375 my ($result) = $rq->fetchrow;
1379 =head2 GetBarcodeFromItemnumber
1381 $result = GetBarcodeFromItemnumber($itemnumber);
1385 sub GetBarcodeFromItemnumber {
1386 my ($itemnumber) = @_;
1387 my $dbh = C4::Context->dbh;
1390 $dbh->prepare("SELECT barcode FROM items WHERE items.itemnumber=?");
1391 $rq->execute($itemnumber);
1392 my ($result) = $rq->fetchrow;
1396 =head2 GetHiddenItemnumbers
1398 my @itemnumbers_to_hide = GetHiddenItemnumbers(@items);
1400 Given a list of items it checks which should be hidden from the OPAC given
1401 the current configuration. Returns a list of itemnumbers corresponding to
1402 those that should be hidden.
1406 sub GetHiddenItemnumbers {
1410 my $yaml = C4::Context->preference('OpacHiddenItems');
1411 return () if (! $yaml =~ /\S/ );
1412 $yaml = "$yaml\n\n"; # YAML is anal on ending \n. Surplus does not hurt
1415 $hidingrules = YAML::Load($yaml);
1418 warn "Unable to parse OpacHiddenItems syspref : $@";
1421 my $dbh = C4::Context->dbh;
1424 foreach my $item (@items) {
1426 # We check each rule
1427 foreach my $field (keys %$hidingrules) {
1429 if (exists $item->{$field}) {
1430 $val = $item->{$field};
1433 my $query = "SELECT $field from items where itemnumber = ?";
1434 $val = $dbh->selectrow_array($query, undef, $item->{'itemnumber'});
1436 $val = '' unless defined $val;
1438 # If the results matches the values in the yaml file
1439 if (any { $val eq $_ } @{$hidingrules->{$field}}) {
1441 # We add the itemnumber to the list
1442 push @resultitems, $item->{'itemnumber'};
1444 # If at least one rule matched for an item, no need to test the others
1449 return @resultitems;
1452 =head1 LIMITED USE FUNCTIONS
1454 The following functions, while part of the public API,
1455 are not exported. This is generally because they are
1456 meant to be used by only one script for a specific
1457 purpose, and should not be used in any other context
1458 without careful thought.
1464 my $item_marc = GetMarcItem($biblionumber, $itemnumber);
1466 Returns MARC::Record of the item passed in parameter.
1467 This function is meant for use only in C<cataloguing/additem.pl>,
1468 where it is needed to support that script's MARC-like
1474 my ( $biblionumber, $itemnumber ) = @_;
1476 # GetMarcItem has been revised so that it does the following:
1477 # 1. Gets the item information from the items table.
1478 # 2. Converts it to a MARC field for storage in the bib record.
1480 # The previous behavior was:
1481 # 1. Get the bib record.
1482 # 2. Return the MARC tag corresponding to the item record.
1484 # The difference is that one treats the items row as authoritative,
1485 # while the other treats the MARC representation as authoritative
1486 # under certain circumstances.
1488 my $itemrecord = GetItem($itemnumber);
1490 # Tack on 'items.' prefix to column names so that C4::Biblio::TransformKohaToMarc will work.
1491 # Also, don't emit a subfield if the underlying field is blank.
1494 return Item2Marc($itemrecord,$biblionumber);
1498 my ($itemrecord,$biblionumber)=@_;
1501 defined($itemrecord->{$_}) && $itemrecord->{$_} ne '' ? ("items.$_" => $itemrecord->{$_}) : ()
1502 } keys %{ $itemrecord }
1504 my $framework = C4::Biblio::GetFrameworkCode( $biblionumber );
1505 my $itemmarc = C4::Biblio::TransformKohaToMarc( $mungeditem ); # Bug 21774: no_split parameter removed to allow cloned subfields
1506 my ( $itemtag, $itemsubfield ) = C4::Biblio::GetMarcFromKohaField(
1507 "items.itemnumber", $framework,
1510 my $unlinked_item_subfields = _parse_unlinked_item_subfields_from_xml($mungeditem->{'items.more_subfields_xml'});
1511 if (defined $unlinked_item_subfields and $#$unlinked_item_subfields > -1) {
1512 foreach my $field ($itemmarc->field($itemtag)){
1513 $field->add_subfields(@$unlinked_item_subfields);
1519 =head1 PRIVATE FUNCTIONS AND VARIABLES
1521 The following functions are not meant to be called
1522 directly, but are documented in order to explain
1523 the inner workings of C<C4::Items>.
1527 =head2 %derived_columns
1529 This hash keeps track of item columns that
1530 are strictly derived from other columns in
1531 the item record and are not meant to be set
1534 Each key in the hash should be the name of a
1535 column (as named by TransformMarcToKoha). Each
1536 value should be hashref whose keys are the
1537 columns on which the derived column depends. The
1538 hashref should also contain a 'BUILDER' key
1539 that is a reference to a sub that calculates
1544 my %derived_columns = (
1545 'items.cn_sort' => {
1546 'itemcallnumber' => 1,
1547 'items.cn_source' => 1,
1548 'BUILDER' => \&_calc_items_cn_sort,
1552 =head2 _set_derived_columns_for_add
1554 _set_derived_column_for_add($item);
1556 Given an item hash representing a new item to be added,
1557 calculate any derived columns. Currently the only
1558 such column is C<items.cn_sort>.
1562 sub _set_derived_columns_for_add {
1565 foreach my $column (keys %derived_columns) {
1566 my $builder = $derived_columns{$column}->{'BUILDER'};
1567 my $source_values = {};
1568 foreach my $source_column (keys %{ $derived_columns{$column} }) {
1569 next if $source_column eq 'BUILDER';
1570 $source_values->{$source_column} = $item->{$source_column};
1572 $builder->($item, $source_values);
1576 =head2 _set_derived_columns_for_mod
1578 _set_derived_column_for_mod($item);
1580 Given an item hash representing a new item to be modified.
1581 calculate any derived columns. Currently the only
1582 such column is C<items.cn_sort>.
1584 This routine differs from C<_set_derived_columns_for_add>
1585 in that it needs to handle partial item records. In other
1586 words, the caller of C<ModItem> may have supplied only one
1587 or two columns to be changed, so this function needs to
1588 determine whether any of the columns to be changed affect
1589 any of the derived columns. Also, if a derived column
1590 depends on more than one column, but the caller is not
1591 changing all of then, this routine retrieves the unchanged
1592 values from the database in order to ensure a correct
1597 sub _set_derived_columns_for_mod {
1600 foreach my $column (keys %derived_columns) {
1601 my $builder = $derived_columns{$column}->{'BUILDER'};
1602 my $source_values = {};
1603 my %missing_sources = ();
1604 my $must_recalc = 0;
1605 foreach my $source_column (keys %{ $derived_columns{$column} }) {
1606 next if $source_column eq 'BUILDER';
1607 if (exists $item->{$source_column}) {
1609 $source_values->{$source_column} = $item->{$source_column};
1611 $missing_sources{$source_column} = 1;
1615 foreach my $source_column (keys %missing_sources) {
1616 $source_values->{$source_column} = _get_single_item_column($source_column, $item->{'itemnumber'});
1618 $builder->($item, $source_values);
1623 =head2 _do_column_fixes_for_mod
1625 _do_column_fixes_for_mod($item);
1627 Given an item hashref containing one or more
1628 columns to modify, fix up certain values.
1629 Specifically, set to 0 any passed value
1630 of C<notforloan>, C<damaged>, C<itemlost>, or
1631 C<withdrawn> that is either undefined or
1632 contains the empty string.
1636 sub _do_column_fixes_for_mod {
1639 if (exists $item->{'notforloan'} and
1640 (not defined $item->{'notforloan'} or $item->{'notforloan'} eq '')) {
1641 $item->{'notforloan'} = 0;
1643 if (exists $item->{'damaged'} and
1644 (not defined $item->{'damaged'} or $item->{'damaged'} eq '')) {
1645 $item->{'damaged'} = 0;
1647 if (exists $item->{'itemlost'} and
1648 (not defined $item->{'itemlost'} or $item->{'itemlost'} eq '')) {
1649 $item->{'itemlost'} = 0;
1651 if (exists $item->{'withdrawn'} and
1652 (not defined $item->{'withdrawn'} or $item->{'withdrawn'} eq '')) {
1653 $item->{'withdrawn'} = 0;
1655 if (exists $item->{location}
1656 and $item->{location} ne 'CART'
1657 and $item->{location} ne 'PROC'
1658 and not $item->{permanent_location}
1660 $item->{'permanent_location'} = $item->{'location'};
1662 if (exists $item->{'timestamp'}) {
1663 delete $item->{'timestamp'};
1667 =head2 _get_single_item_column
1669 _get_single_item_column($column, $itemnumber);
1671 Retrieves the value of a single column from an C<items>
1672 row specified by C<$itemnumber>.
1676 sub _get_single_item_column {
1678 my $itemnumber = shift;
1680 my $dbh = C4::Context->dbh;
1681 my $sth = $dbh->prepare("SELECT $column FROM items WHERE itemnumber = ?");
1682 $sth->execute($itemnumber);
1683 my ($value) = $sth->fetchrow();
1687 =head2 _calc_items_cn_sort
1689 _calc_items_cn_sort($item, $source_values);
1691 Helper routine to calculate C<items.cn_sort>.
1695 sub _calc_items_cn_sort {
1697 my $source_values = shift;
1699 $item->{'items.cn_sort'} = GetClassSort($source_values->{'items.cn_source'}, $source_values->{'itemcallnumber'}, "");
1702 =head2 _set_defaults_for_add
1704 _set_defaults_for_add($item_hash);
1706 Given an item hash representing an item to be added, set
1707 correct default values for columns whose default value
1708 is not handled by the DBMS. This includes the following
1715 C<items.dateaccessioned>
1737 sub _set_defaults_for_add {
1739 $item->{dateaccessioned} ||= output_pref({ dt => dt_from_string, dateformat => 'iso', dateonly => 1 });
1740 $item->{$_} ||= 0 for (qw( notforloan damaged itemlost withdrawn));
1743 =head2 _koha_new_item
1745 my ($itemnumber,$error) = _koha_new_item( $item, $barcode );
1747 Perform the actual insert into the C<items> table.
1751 sub _koha_new_item {
1752 my ( $item, $barcode ) = @_;
1753 my $dbh=C4::Context->dbh;
1755 $item->{permanent_location} //= $item->{location};
1756 _mod_item_dates( $item );
1758 "INSERT INTO items SET
1760 biblioitemnumber = ?,
1762 dateaccessioned = ?,
1766 replacementprice = ?,
1767 replacementpricedate = ?,
1768 datelastborrowed = ?,
1776 coded_location_qualifier = ?,
1779 itemnotes_nonpublic = ?,
1783 permanent_location = ?,
1795 more_subfields_xml = ?,
1800 my $sth = $dbh->prepare($query);
1801 my $today = output_pref({ dt => dt_from_string, dateformat => 'iso', dateonly => 1 });
1803 $item->{'biblionumber'},
1804 $item->{'biblioitemnumber'},
1806 $item->{'dateaccessioned'},
1807 $item->{'booksellerid'},
1808 $item->{'homebranch'},
1810 $item->{'replacementprice'},
1811 $item->{'replacementpricedate'} || $today,
1812 $item->{datelastborrowed},
1813 $item->{datelastseen} || $today,
1815 $item->{'notforloan'},
1817 $item->{'itemlost'},
1818 $item->{'withdrawn'},
1819 $item->{'itemcallnumber'},
1820 $item->{'coded_location_qualifier'},
1821 $item->{'restricted'},
1822 $item->{'itemnotes'},
1823 $item->{'itemnotes_nonpublic'},
1824 $item->{'holdingbranch'},
1826 $item->{'location'},
1827 $item->{'permanent_location'},
1830 $item->{'renewals'},
1831 $item->{'reserves'},
1832 $item->{'items.cn_source'},
1833 $item->{'items.cn_sort'},
1836 $item->{'materials'},
1838 $item->{'enumchron'},
1839 $item->{'more_subfields_xml'},
1840 $item->{'copynumber'},
1841 $item->{'stocknumber'},
1842 $item->{'new_status'},
1846 if ( defined $sth->errstr ) {
1847 $error.="ERROR in _koha_new_item $query".$sth->errstr;
1850 $itemnumber = $dbh->{'mysql_insertid'};
1853 return ( $itemnumber, $error );
1856 =head2 MoveItemFromBiblio
1858 MoveItemFromBiblio($itenumber, $frombiblio, $tobiblio);
1860 Moves an item from a biblio to another
1862 Returns undef if the move failed or the biblionumber of the destination record otherwise
1866 sub MoveItemFromBiblio {
1867 my ($itemnumber, $frombiblio, $tobiblio) = @_;
1868 my $dbh = C4::Context->dbh;
1869 my ( $tobiblioitem ) = $dbh->selectrow_array(q|
1870 SELECT biblioitemnumber
1872 WHERE biblionumber = ?
1873 |, undef, $tobiblio );
1874 my $return = $dbh->do(q|
1876 SET biblioitemnumber = ?,
1878 WHERE itemnumber = ?
1879 AND biblionumber = ?
1880 |, undef, $tobiblioitem, $tobiblio, $itemnumber, $frombiblio );
1882 ModZebra( $tobiblio, "specialUpdate", "biblioserver" );
1883 ModZebra( $frombiblio, "specialUpdate", "biblioserver" );
1884 # Checking if the item we want to move is in an order
1885 require C4::Acquisition;
1886 my $order = C4::Acquisition::GetOrderFromItemnumber($itemnumber);
1888 # Replacing the biblionumber within the order if necessary
1889 $order->{'biblionumber'} = $tobiblio;
1890 C4::Acquisition::ModOrder($order);
1893 # Update reserves, hold_fill_targets, tmp_holdsqueue and linktracker tables
1894 for my $table_name ( qw( reserves hold_fill_targets tmp_holdsqueue linktracker ) ) {
1897 SET biblionumber = ?
1898 WHERE itemnumber = ?
1899 |, undef, $tobiblio, $itemnumber );
1906 =head2 ItemSafeToDelete
1908 ItemSafeToDelete( $biblionumber, $itemnumber);
1910 Exported function (core API) for checking whether an item record is safe to delete.
1912 returns 1 if the item is safe to delete,
1914 "book_on_loan" if the item is checked out,
1916 "not_same_branch" if the item is blocked by independent branches,
1918 "book_reserved" if the there are holds aganst the item, or
1920 "linked_analytics" if the item has linked analytic records.
1924 sub ItemSafeToDelete {
1925 my ( $biblionumber, $itemnumber ) = @_;
1927 my $dbh = C4::Context->dbh;
1931 my $countanalytics = GetAnalyticsCount($itemnumber);
1933 # check that there is no issue on this item before deletion.
1934 my $sth = $dbh->prepare(
1936 SELECT COUNT(*) FROM issues
1937 WHERE itemnumber = ?
1940 $sth->execute($itemnumber);
1941 my ($onloan) = $sth->fetchrow;
1943 my $item = GetItem($itemnumber);
1946 $status = "book_on_loan";
1948 elsif ( defined C4::Context->userenv
1949 and !C4::Context->IsSuperLibrarian()
1950 and C4::Context->preference("IndependentBranches")
1951 and ( C4::Context->userenv->{branch} ne $item->{'homebranch'} ) )
1953 $status = "not_same_branch";
1956 # check it doesn't have a waiting reserve
1957 $sth = $dbh->prepare(
1959 SELECT COUNT(*) FROM reserves
1960 WHERE (found = 'W' OR found = 'T')
1964 $sth->execute($itemnumber);
1965 my ($reserve) = $sth->fetchrow;
1967 $status = "book_reserved";
1969 elsif ( $countanalytics > 0 ) {
1970 $status = "linked_analytics";
1981 DelItemCheck( $biblionumber, $itemnumber);
1983 Exported function (core API) for deleting an item record in Koha if there no current issue.
1985 DelItemCheck wraps ItemSafeToDelete around DelItem.
1990 my ( $biblionumber, $itemnumber ) = @_;
1991 my $status = ItemSafeToDelete( $biblionumber, $itemnumber );
1993 if ( $status == 1 ) {
1996 biblionumber => $biblionumber,
1997 itemnumber => $itemnumber
2004 =head2 _koha_modify_item
2006 my ($itemnumber,$error) =_koha_modify_item( $item );
2008 Perform the actual update of the C<items> row. Note that this
2009 routine accepts a hashref specifying the columns to update.
2013 sub _koha_modify_item {
2015 my $dbh=C4::Context->dbh;
2018 my $query = "UPDATE items SET ";
2020 _mod_item_dates( $item );
2021 for my $key ( keys %$item ) {
2022 next if ( $key eq 'itemnumber' );
2024 push @bind, $item->{$key};
2027 $query .= " WHERE itemnumber=?";
2028 push @bind, $item->{'itemnumber'};
2029 my $sth = $dbh->prepare($query);
2030 $sth->execute(@bind);
2032 $error.="ERROR in _koha_modify_item $query: ".$sth->errstr;
2035 return ($item->{'itemnumber'},$error);
2038 sub _mod_item_dates { # date formatting for date fields in item hash
2040 return if !$item || ref($item) ne 'HASH';
2043 { $_ =~ /^onloan$|^date|date$|datetime$/ }
2045 # Incl. dateaccessioned,replacementpricedate,datelastborrowed,datelastseen
2046 # NOTE: We do not (yet) have items fields ending with datetime
2047 # Fields with _on$ have been handled already
2049 foreach my $key ( @keys ) {
2050 next if !defined $item->{$key}; # skip undefs
2051 my $dt = eval { dt_from_string( $item->{$key} ) };
2052 # eval: dt_from_string will die on us if we pass illegal dates
2055 if( defined $dt && ref($dt) eq 'DateTime' ) {
2056 if( $key =~ /datetime/ ) {
2057 $newstr = DateTime::Format::MySQL->format_datetime($dt);
2059 $newstr = DateTime::Format::MySQL->format_date($dt);
2062 $item->{$key} = $newstr; # might be undef to clear garbage
2066 =head2 _koha_delete_item
2068 _koha_delete_item( $itemnum );
2070 Internal function to delete an item record from the koha tables
2074 sub _koha_delete_item {
2075 my ( $itemnum ) = @_;
2077 my $dbh = C4::Context->dbh;
2078 # save the deleted item to deleteditems table
2079 my $sth = $dbh->prepare("SELECT * FROM items WHERE itemnumber=?");
2080 $sth->execute($itemnum);
2081 my $data = $sth->fetchrow_hashref();
2083 # There is no item to delete
2084 return 0 unless $data;
2086 my $query = "INSERT INTO deleteditems SET ";
2088 foreach my $key ( keys %$data ) {
2089 next if ( $key eq 'timestamp' ); # timestamp will be set by db
2090 $query .= "$key = ?,";
2091 push( @bind, $data->{$key} );
2094 $sth = $dbh->prepare($query);
2095 $sth->execute(@bind);
2097 # delete from items table
2098 $sth = $dbh->prepare("DELETE FROM items WHERE itemnumber=?");
2099 my $deleted = $sth->execute($itemnum);
2100 return ( $deleted == 1 ) ? 1 : 0;
2103 =head2 _marc_from_item_hash
2105 my $item_marc = _marc_from_item_hash($item, $frameworkcode[, $unlinked_item_subfields]);
2107 Given an item hash representing a complete item record,
2108 create a C<MARC::Record> object containing an embedded
2109 tag representing that item.
2111 The third, optional parameter C<$unlinked_item_subfields> is
2112 an arrayref of subfields (not mapped to C<items> fields per the
2113 framework) to be added to the MARC representation
2118 sub _marc_from_item_hash {
2120 my $frameworkcode = shift;
2121 my $unlinked_item_subfields;
2123 $unlinked_item_subfields = shift;
2126 # Tack on 'items.' prefix to column names so lookup from MARC frameworks will work
2127 # Also, don't emit a subfield if the underlying field is blank.
2128 my $mungeditem = { map { (defined($item->{$_}) and $item->{$_} ne '') ?
2129 (/^items\./ ? ($_ => $item->{$_}) : ("items.$_" => $item->{$_}))
2130 : () } keys %{ $item } };
2132 my $item_marc = MARC::Record->new();
2133 foreach my $item_field ( keys %{$mungeditem} ) {
2134 my ( $tag, $subfield ) = C4::Biblio::GetMarcFromKohaField( $item_field, $frameworkcode );
2135 next unless defined $tag and defined $subfield; # skip if not mapped to MARC field
2136 my @values = split(/\s?\|\s?/, $mungeditem->{$item_field}, -1);
2137 foreach my $value (@values){
2138 if ( my $field = $item_marc->field($tag) ) {
2139 $field->add_subfields( $subfield => $value );
2141 my $add_subfields = [];
2142 if (defined $unlinked_item_subfields and ref($unlinked_item_subfields) eq 'ARRAY' and $#$unlinked_item_subfields > -1) {
2143 $add_subfields = $unlinked_item_subfields;
2145 $item_marc->add_fields( $tag, " ", " ", $subfield => $value, @$add_subfields );
2153 =head2 _repack_item_errors
2155 Add an error message hash generated by C<CheckItemPreSave>
2156 to a list of errors.
2160 sub _repack_item_errors {
2161 my $item_sequence_num = shift;
2162 my $item_ref = shift;
2163 my $error_ref = shift;
2165 my @repacked_errors = ();
2167 foreach my $error_code (sort keys %{ $error_ref }) {
2168 my $repacked_error = {};
2169 $repacked_error->{'item_sequence'} = $item_sequence_num;
2170 $repacked_error->{'item_barcode'} = exists($item_ref->{'barcode'}) ? $item_ref->{'barcode'} : '';
2171 $repacked_error->{'error_code'} = $error_code;
2172 $repacked_error->{'error_information'} = $error_ref->{$error_code};
2173 push @repacked_errors, $repacked_error;
2176 return @repacked_errors;
2179 =head2 _get_unlinked_item_subfields
2181 my $unlinked_item_subfields = _get_unlinked_item_subfields($original_item_marc, $frameworkcode);
2185 sub _get_unlinked_item_subfields {
2186 my $original_item_marc = shift;
2187 my $frameworkcode = shift;
2189 my $marcstructure = GetMarcStructure(1, $frameworkcode, { unsafe => 1 });
2191 # assume that this record has only one field, and that that
2192 # field contains only the item information
2194 my @fields = $original_item_marc->fields();
2195 if ($#fields > -1) {
2196 my $field = $fields[0];
2197 my $tag = $field->tag();
2198 foreach my $subfield ($field->subfields()) {
2199 if (defined $subfield->[1] and
2200 $subfield->[1] ne '' and
2201 !$marcstructure->{$tag}->{$subfield->[0]}->{'kohafield'}) {
2202 push @$subfields, $subfield->[0] => $subfield->[1];
2209 =head2 _get_unlinked_subfields_xml
2211 my $unlinked_subfields_xml = _get_unlinked_subfields_xml($unlinked_item_subfields);
2215 sub _get_unlinked_subfields_xml {
2216 my $unlinked_item_subfields = shift;
2219 if (defined $unlinked_item_subfields and ref($unlinked_item_subfields) eq 'ARRAY' and $#$unlinked_item_subfields > -1) {
2220 my $marc = MARC::Record->new();
2221 # use of tag 999 is arbitrary, and doesn't need to match the item tag
2222 # used in the framework
2223 $marc->append_fields(MARC::Field->new('999', ' ', ' ', @$unlinked_item_subfields));
2224 $marc->encoding("UTF-8");
2225 $xml = $marc->as_xml("USMARC");
2231 =head2 _parse_unlinked_item_subfields_from_xml
2233 my $unlinked_item_subfields = _parse_unlinked_item_subfields_from_xml($whole_item->{'more_subfields_xml'}):
2237 sub _parse_unlinked_item_subfields_from_xml {
2239 require C4::Charset;
2240 return unless defined $xml and $xml ne "";
2241 my $marc = MARC::Record->new_from_xml(C4::Charset::StripNonXmlChars($xml),'UTF-8');
2242 my $unlinked_subfields = [];
2243 my @fields = $marc->fields();
2244 if ($#fields > -1) {
2245 foreach my $subfield ($fields[0]->subfields()) {
2246 push @$unlinked_subfields, $subfield->[0] => $subfield->[1];
2249 return $unlinked_subfields;
2252 =head2 GetAnalyticsCount
2254 $count= &GetAnalyticsCount($itemnumber)
2256 counts Usage of itemnumber in Analytical bibliorecords.
2260 sub GetAnalyticsCount {
2261 my ($itemnumber) = @_;
2263 ### ZOOM search here
2265 $query= "hi=".$itemnumber;
2266 my $searcher = Koha::SearchEngine::Search->new({index => $Koha::SearchEngine::BIBLIOS_INDEX});
2267 my ($err,$res,$result) = $searcher->simple_search_compat($query,0,10);
2271 =head2 SearchItemsByField
2273 my $items = SearchItemsByField($field, $value);
2275 SearchItemsByField will search for items on a specific given field.
2276 For instance you can search all items with a specific stocknumber like this:
2278 my $items = SearchItemsByField('stocknumber', $stocknumber);
2282 sub SearchItemsByField {
2283 my ($field, $value) = @_;
2290 my ($results) = SearchItems($filters);
2294 sub _SearchItems_build_where_fragment {
2297 my $dbh = C4::Context->dbh;
2300 if (exists($filter->{conjunction})) {
2301 my (@where_strs, @where_args);
2302 foreach my $f (@{ $filter->{filters} }) {
2303 my $fragment = _SearchItems_build_where_fragment($f);
2305 push @where_strs, $fragment->{str};
2306 push @where_args, @{ $fragment->{args} };
2311 $where_str = '(' . join (' ' . $filter->{conjunction} . ' ', @where_strs) . ')';
2314 args => \@where_args,
2318 my @columns = Koha::Database->new()->schema()->resultset('Item')->result_source->columns;
2319 push @columns, Koha::Database->new()->schema()->resultset('Biblio')->result_source->columns;
2320 push @columns, Koha::Database->new()->schema()->resultset('Biblioitem')->result_source->columns;
2321 my @operators = qw(= != > < >= <= like);
2322 my $field = $filter->{field};
2323 if ( (0 < grep /^$field$/, @columns) or (substr($field, 0, 5) eq 'marc:') ) {
2324 my $op = $filter->{operator};
2325 my $query = $filter->{query};
2327 if (!$op or (0 == grep /^$op$/, @operators)) {
2328 $op = '='; # default operator
2332 if ($field =~ /^marc:(\d{3})(?:\$(\w))?$/) {
2334 my $marcsubfield = $2;
2335 my ($kohafield) = $dbh->selectrow_array(q|
2336 SELECT kohafield FROM marc_subfield_structure
2337 WHERE tagfield=? AND tagsubfield=? AND frameworkcode=''
2338 |, undef, $marcfield, $marcsubfield);
2341 $column = $kohafield;
2343 # MARC field is not linked to a DB field so we need to use
2344 # ExtractValue on marcxml from biblio_metadata or
2345 # items.more_subfields_xml, depending on the MARC field.
2348 my ($itemfield) = C4::Biblio::GetMarcFromKohaField('items.itemnumber');
2349 if ($marcfield eq $itemfield) {
2350 $sqlfield = 'more_subfields_xml';
2351 $xpath = '//record/datafield/subfield[@code="' . $marcsubfield . '"]';
2353 $sqlfield = 'metadata'; # From biblio_metadata
2354 if ($marcfield < 10) {
2355 $xpath = "//record/controlfield[\@tag=\"$marcfield\"]";
2357 $xpath = "//record/datafield[\@tag=\"$marcfield\"]/subfield[\@code=\"$marcsubfield\"]";
2360 $column = "ExtractValue($sqlfield, '$xpath')";
2366 if (ref $query eq 'ARRAY') {
2369 } elsif ($op eq '!=') {
2373 str => "$column $op (" . join (',', ('?') x @$query) . ")",
2378 str => "$column $op ?",
2385 return $where_fragment;
2390 my ($items, $total) = SearchItems($filter, $params);
2392 Perform a search among items
2394 $filter is a reference to a hash which can be a filter, or a combination of filters.
2396 A filter has the following keys:
2400 =item * field: the name of a SQL column in table items
2402 =item * query: the value to search in this column
2404 =item * operator: comparison operator. Can be one of = != > < >= <= like
2408 A combination of filters hash the following keys:
2412 =item * conjunction: 'AND' or 'OR'
2414 =item * filters: array ref of filters
2418 $params is a reference to a hash that can contain the following parameters:
2422 =item * rows: Number of items to return. 0 returns everything (default: 0)
2424 =item * page: Page to return (return items from (page-1)*rows to (page*rows)-1)
2427 =item * sortby: A SQL column name in items table to sort on
2429 =item * sortorder: 'ASC' or 'DESC'
2436 my ($filter, $params) = @_;
2440 return unless ref $filter eq 'HASH';
2441 return unless ref $params eq 'HASH';
2443 # Default parameters
2444 $params->{rows} ||= 0;
2445 $params->{page} ||= 1;
2446 $params->{sortby} ||= 'itemnumber';
2447 $params->{sortorder} ||= 'ASC';
2449 my ($where_str, @where_args);
2450 my $where_fragment = _SearchItems_build_where_fragment($filter);
2451 if ($where_fragment) {
2452 $where_str = $where_fragment->{str};
2453 @where_args = @{ $where_fragment->{args} };
2456 my $dbh = C4::Context->dbh;
2458 SELECT SQL_CALC_FOUND_ROWS items.*
2460 LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber
2461 LEFT JOIN biblioitems ON biblioitems.biblioitemnumber = items.biblioitemnumber
2462 LEFT JOIN biblio_metadata ON biblio_metadata.biblionumber = biblio.biblionumber
2465 if (defined $where_str and $where_str ne '') {
2466 $query .= qq{ AND $where_str };
2469 $query .= q{ AND biblio_metadata.format = 'marcxml' AND biblio_metadata.marcflavour = ? };
2470 push @where_args, C4::Context->preference('marcflavour');
2472 my @columns = Koha::Database->new()->schema()->resultset('Item')->result_source->columns;
2473 push @columns, Koha::Database->new()->schema()->resultset('Biblio')->result_source->columns;
2474 push @columns, Koha::Database->new()->schema()->resultset('Biblioitem')->result_source->columns;
2475 my $sortby = (0 < grep {$params->{sortby} eq $_} @columns)
2476 ? $params->{sortby} : 'itemnumber';
2477 my $sortorder = (uc($params->{sortorder}) eq 'ASC') ? 'ASC' : 'DESC';
2478 $query .= qq{ ORDER BY $sortby $sortorder };
2480 my $rows = $params->{rows};
2483 my $offset = $rows * ($params->{page}-1);
2484 $query .= qq { LIMIT ?, ? };
2485 push @limit_args, $offset, $rows;
2488 my $sth = $dbh->prepare($query);
2489 my $rv = $sth->execute(@where_args, @limit_args);
2491 return unless ($rv);
2492 my ($total_rows) = $dbh->selectrow_array(q{ SELECT FOUND_ROWS() });
2494 return ($sth->fetchall_arrayref({}), $total_rows);
2498 =head1 OTHER FUNCTIONS
2502 ($indicators, $value) = _find_value($tag, $subfield, $record,$encoding);
2504 Find the given $subfield in the given $tag in the given
2505 MARC::Record $record. If the subfield is found, returns
2506 the (indicators, value) pair; otherwise, (undef, undef) is
2510 Such a function is used in addbiblio AND additem and serial-edit and maybe could be used in Authorities.
2511 I suggest we export it from this module.
2516 my ( $tagfield, $insubfield, $record, $encoding ) = @_;
2519 if ( $tagfield < 10 ) {
2520 if ( $record->field($tagfield) ) {
2521 push @result, $record->field($tagfield)->data();
2526 foreach my $field ( $record->field($tagfield) ) {
2527 my @subfields = $field->subfields();
2528 foreach my $subfield (@subfields) {
2529 if ( @$subfield[0] eq $insubfield ) {
2530 push @result, @$subfield[1];
2531 $indicator = $field->indicator(1) . $field->indicator(2);
2536 return ( $indicator, @result );
2540 =head2 PrepareItemrecordDisplay
2542 PrepareItemrecordDisplay($itemrecord,$bibnum,$itemumber,$frameworkcode);
2544 Returns a hash with all the fields for Display a given item data in a template
2546 The $frameworkcode returns the item for the given frameworkcode, ONLY if bibnum is not provided
2550 sub PrepareItemrecordDisplay {
2552 my ( $bibnum, $itemnum, $defaultvalues, $frameworkcode ) = @_;
2554 my $dbh = C4::Context->dbh;
2555 $frameworkcode = C4::Biblio::GetFrameworkCode($bibnum) if $bibnum;
2556 my ( $itemtagfield, $itemtagsubfield ) = C4::Biblio::GetMarcFromKohaField( "items.itemnumber", $frameworkcode );
2558 # Note: $tagslib obtained from GetMarcStructure() in 'unsafe' mode is
2559 # a shared data structure. No plugin (including custom ones) should change
2560 # its contents. See also GetMarcStructure.
2561 my $tagslib = GetMarcStructure( 1, $frameworkcode, { unsafe => 1 } );
2563 # return nothing if we don't have found an existing framework.
2564 return q{} unless $tagslib;
2567 $itemrecord = C4::Items::GetMarcItem( $bibnum, $itemnum );
2571 my $branch_limit = C4::Context->userenv ? C4::Context->userenv->{"branch"} : "";
2573 SELECT authorised_value,lib FROM authorised_values
2576 LEFT JOIN authorised_values_branches ON ( id = av_id )
2581 $query .= qq{ AND ( branchcode = ? OR branchcode IS NULL )} if $branch_limit;
2582 $query .= qq{ ORDER BY lib};
2583 my $authorised_values_sth = $dbh->prepare( $query );
2584 foreach my $tag ( sort keys %{$tagslib} ) {
2587 # loop through each subfield
2589 foreach my $subfield ( sort keys %{ $tagslib->{$tag} } ) {
2590 next if IsMarcStructureInternal($tagslib->{$tag}{$subfield});
2591 next unless ( $tagslib->{$tag}->{$subfield}->{'tab'} );
2592 next if ( $tagslib->{$tag}->{$subfield}->{'tab'} ne "10" );
2594 $subfield_data{tag} = $tag;
2595 $subfield_data{subfield} = $subfield;
2596 $subfield_data{countsubfield} = $cntsubf++;
2597 $subfield_data{kohafield} = $tagslib->{$tag}->{$subfield}->{'kohafield'};
2598 $subfield_data{id} = "tag_".$tag."_subfield_".$subfield."_".int(rand(1000000));
2600 # $subfield_data{marc_lib}=$tagslib->{$tag}->{$subfield}->{lib};
2601 $subfield_data{marc_lib} = $tagslib->{$tag}->{$subfield}->{lib};
2602 $subfield_data{mandatory} = $tagslib->{$tag}->{$subfield}->{mandatory};
2603 $subfield_data{repeatable} = $tagslib->{$tag}->{$subfield}->{repeatable};
2604 $subfield_data{hidden} = "display:none"
2605 if ( ( $tagslib->{$tag}->{$subfield}->{hidden} > 4 )
2606 || ( $tagslib->{$tag}->{$subfield}->{hidden} < -4 ) );
2607 my ( $x, $defaultvalue );
2609 ( $x, $defaultvalue ) = _find_value( $tag, $subfield, $itemrecord );
2611 $defaultvalue = $tagslib->{$tag}->{$subfield}->{defaultvalue} unless $defaultvalue;
2612 if ( !defined $defaultvalue ) {
2613 $defaultvalue = q||;
2615 $defaultvalue =~ s/"/"/g;
2618 # search for itemcallnumber if applicable
2619 if ( $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.itemcallnumber'
2620 && C4::Context->preference('itemcallnumber') ) {
2621 my $CNtag = substr( C4::Context->preference('itemcallnumber'), 0, 3 );
2622 my $CNsubfield = substr( C4::Context->preference('itemcallnumber'), 3, 1 );
2623 if ( $itemrecord and my $field = $itemrecord->field($CNtag) ) {
2624 $defaultvalue = $field->subfield($CNsubfield);
2627 if ( $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.itemcallnumber'
2629 && $defaultvalues->{'callnumber'} ) {
2630 if( $itemrecord and $defaultvalues and not $itemrecord->subfield($tag,$subfield) ){
2631 # if the item record exists, only use default value if the item has no callnumber
2632 $defaultvalue = $defaultvalues->{callnumber};
2633 } elsif ( !$itemrecord and $defaultvalues ) {
2634 # if the item record *doesn't* exists, always use the default value
2635 $defaultvalue = $defaultvalues->{callnumber};
2638 if ( ( $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.holdingbranch' || $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.homebranch' )
2640 && $defaultvalues->{'branchcode'} ) {
2641 if ( $itemrecord and $defaultvalues and not $itemrecord->subfield($tag,$subfield) ) {
2642 $defaultvalue = $defaultvalues->{branchcode};
2645 if ( ( $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.location' )
2647 && $defaultvalues->{'location'} ) {
2649 if ( $itemrecord and $defaultvalues and not $itemrecord->subfield($tag,$subfield) ) {
2650 # if the item record exists, only use default value if the item has no locationr
2651 $defaultvalue = $defaultvalues->{location};
2652 } elsif ( !$itemrecord and $defaultvalues ) {
2653 # if the item record *doesn't* exists, always use the default value
2654 $defaultvalue = $defaultvalues->{location};
2657 if ( $tagslib->{$tag}->{$subfield}->{authorised_value} ) {
2658 my @authorised_values;
2661 # builds list, depending on authorised value...
2663 if ( $tagslib->{$tag}->{$subfield}->{'authorised_value'} eq "branches" ) {
2664 if ( ( C4::Context->preference("IndependentBranches") )
2665 && !C4::Context->IsSuperLibrarian() ) {
2666 my $sth = $dbh->prepare( "SELECT branchcode,branchname FROM branches WHERE branchcode = ? ORDER BY branchname" );
2667 $sth->execute( C4::Context->userenv->{branch} );
2668 push @authorised_values, ""
2669 unless ( $tagslib->{$tag}->{$subfield}->{mandatory} );
2670 while ( my ( $branchcode, $branchname ) = $sth->fetchrow_array ) {
2671 push @authorised_values, $branchcode;
2672 $authorised_lib{$branchcode} = $branchname;
2675 my $sth = $dbh->prepare( "SELECT branchcode,branchname FROM branches ORDER BY branchname" );
2677 push @authorised_values, ""
2678 unless ( $tagslib->{$tag}->{$subfield}->{mandatory} );
2679 while ( my ( $branchcode, $branchname ) = $sth->fetchrow_array ) {
2680 push @authorised_values, $branchcode;
2681 $authorised_lib{$branchcode} = $branchname;
2685 $defaultvalue = C4::Context->userenv ? C4::Context->userenv->{branch} : undef;
2686 if ( $defaultvalues and $defaultvalues->{branchcode} ) {
2687 $defaultvalue = $defaultvalues->{branchcode};
2691 } elsif ( $tagslib->{$tag}->{$subfield}->{authorised_value} eq "itemtypes" ) {
2692 my $itemtypes = Koha::ItemTypes->search_with_localization;
2693 push @authorised_values, ""
2694 unless ( $tagslib->{$tag}->{$subfield}->{mandatory} );
2695 while ( my $itemtype = $itemtypes->next ) {
2696 push @authorised_values, $itemtype->itemtype;
2697 $authorised_lib{$itemtype->itemtype} = $itemtype->translated_description;
2699 if ($defaultvalues && $defaultvalues->{'itemtype'}) {
2700 $defaultvalue = $defaultvalues->{'itemtype'};
2704 } elsif ( $tagslib->{$tag}->{$subfield}->{authorised_value} eq "cn_source" ) {
2705 push @authorised_values, "" unless ( $tagslib->{$tag}->{$subfield}->{mandatory} );
2707 my $class_sources = GetClassSources();
2708 my $default_source = C4::Context->preference("DefaultClassificationSource");
2710 foreach my $class_source (sort keys %$class_sources) {
2711 next unless $class_sources->{$class_source}->{'used'} or
2712 ($class_source eq $default_source);
2713 push @authorised_values, $class_source;
2714 $authorised_lib{$class_source} = $class_sources->{$class_source}->{'description'};
2717 $defaultvalue = $default_source;
2719 #---- "true" authorised value
2721 $authorised_values_sth->execute(
2722 $tagslib->{$tag}->{$subfield}->{authorised_value},
2723 $branch_limit ? $branch_limit : ()
2725 push @authorised_values, ""
2726 unless ( $tagslib->{$tag}->{$subfield}->{mandatory} );
2727 while ( my ( $value, $lib ) = $authorised_values_sth->fetchrow_array ) {
2728 push @authorised_values, $value;
2729 $authorised_lib{$value} = $lib;
2732 $subfield_data{marc_value} = {
2734 values => \@authorised_values,
2735 default => "$defaultvalue",
2736 labels => \%authorised_lib,
2738 } elsif ( $tagslib->{$tag}->{$subfield}->{value_builder} ) {
2740 require Koha::FrameworkPlugin;
2741 my $plugin = Koha::FrameworkPlugin->new({
2742 name => $tagslib->{$tag}->{$subfield}->{value_builder},
2745 my $pars = { dbh => $dbh, record => undef, tagslib =>$tagslib, id => $subfield_data{id}, tabloop => undef };
2746 $plugin->build( $pars );
2747 if ( $itemrecord and my $field = $itemrecord->field($tag) ) {
2748 $defaultvalue = $field->subfield($subfield);
2750 if( !$plugin->errstr ) {
2751 #TODO Move html to template; see report 12176/13397
2752 my $tab= $plugin->noclick? '-1': '';
2753 my $class= $plugin->noclick? ' disabled': '';
2754 my $title= $plugin->noclick? 'No popup': 'Tag editor';
2755 $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;
2757 warn $plugin->errstr;
2758 $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
2761 elsif ( $tag eq '' ) { # it's an hidden field
2762 $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" />);
2764 elsif ( $tagslib->{$tag}->{$subfield}->{'hidden'} ) { # FIXME: shouldn't input type be "hidden" ?
2765 $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" />);
2767 elsif ( length($defaultvalue) > 100
2768 or (C4::Context->preference("marcflavour") eq "UNIMARC" and
2769 300 <= $tag && $tag < 400 && $subfield eq 'a' )
2770 or (C4::Context->preference("marcflavour") eq "MARC21" and
2771 500 <= $tag && $tag < 600 )
2773 # oversize field (textarea)
2774 $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");
2776 $subfield_data{marc_value} = "<input type=\"text\" name=\"field_value\" value=\"$defaultvalue\" size=\"50\" maxlength=\"255\" />";
2778 push( @loop_data, \%subfield_data );
2783 if ( $itemrecord && $itemrecord->field($itemtagfield) ) {
2784 $itemnumber = $itemrecord->subfield( $itemtagfield, $itemtagsubfield );
2787 'itemtagfield' => $itemtagfield,
2788 'itemtagsubfield' => $itemtagsubfield,
2789 'itemnumber' => $itemnumber,
2790 'iteminformation' => \@loop_data
2794 sub ToggleNewStatus {
2795 my ( $params ) = @_;
2796 my @rules = @{ $params->{rules} };
2797 my $report_only = $params->{report_only};
2799 my $dbh = C4::Context->dbh;
2801 my @item_columns = map { "items.$_" } Koha::Items->columns;
2802 my @biblioitem_columns = map { "biblioitems.$_" } Koha::Biblioitems->columns;
2804 for my $rule ( @rules ) {
2805 my $age = $rule->{age};
2806 my $conditions = $rule->{conditions};
2807 my $substitutions = $rule->{substitutions};
2811 SELECT items.biblionumber, items.itemnumber
2813 LEFT JOIN biblioitems ON biblioitems.biblionumber = items.biblionumber
2816 for my $condition ( @$conditions ) {
2818 grep {/^$condition->{field}$/} @item_columns
2819 or grep {/^$condition->{field}$/} @biblioitem_columns
2821 if ( $condition->{value} =~ /\|/ ) {
2822 my @values = split /\|/, $condition->{value};
2823 $query .= qq| AND $condition->{field} IN (|
2824 . join( ',', ('?') x scalar @values )
2826 push @params, @values;
2828 $query .= qq| AND $condition->{field} = ?|;
2829 push @params, $condition->{value};
2833 if ( defined $age ) {
2834 $query .= q| AND TO_DAYS(NOW()) - TO_DAYS(dateaccessioned) >= ? |;
2837 my $sth = $dbh->prepare($query);
2838 $sth->execute( @params );
2839 while ( my $values = $sth->fetchrow_hashref ) {
2840 my $biblionumber = $values->{biblionumber};
2841 my $itemnumber = $values->{itemnumber};
2842 my $item = C4::Items::GetItem( $itemnumber );
2843 for my $substitution ( @$substitutions ) {
2844 next unless $substitution->{field};
2845 C4::Items::ModItem( {$substitution->{field} => $substitution->{value}}, $biblionumber, $itemnumber )
2846 unless $report_only;
2847 push @{ $report->{$itemnumber} }, $substitution;