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
38 use Koha::AuthorisedValues;
39 use Koha::DateUtils qw/dt_from_string/;
42 use Koha::Biblioitems;
45 use Koha::SearchEngine;
46 use Koha::SearchEngine::Search;
49 use vars qw(@ISA @EXPORT);
54 @ISA = qw( Exporter );
72 GetItemsByBiblioitemnumber
76 GetItemnumbersForBiblio
77 get_hostitemnumbers_of
78 GetItemnumberFromBarcode
79 GetBarcodeFromItemnumber
94 PrepareItemrecordDisplay
101 C4::Items - item management functions
105 This module contains an API for manipulating item
106 records in Koha, and is used by cataloguing, circulation,
107 acquisitions, and serials management.
109 # FIXME This POD is not up-to-date
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 $item = Koha::Items->find( $itemnumber );
159 $item = Koha::Items->find( { barcode => $barcode } );
162 return unless ( $item );
164 my $data = $item->unblessed();
165 $data->{itype} = $item->effective_itemtype(); # set the correct itype
168 my $ssth = $dbh->prepare("SELECT serialseq,publisheddate from serialitems left join serial on serialitems.serialid=serial.serialid where serialitems.itemnumber=?");
169 $ssth->execute( $data->{'itemnumber'} );
170 ( $data->{'serialseq'}, $data->{'publisheddate'} ) = $ssth->fetchrow_array();
178 CartToShelf($itemnumber);
180 Set the current shelving location of the item record
181 to its stored permanent shelving location. This is
182 primarily used to indicate when an item whose current
183 location is a special processing ('PROC') or shelving cart
184 ('CART') location is back in the stacks.
189 my ( $itemnumber ) = @_;
191 unless ( $itemnumber ) {
192 croak "FAILED CartToShelf() - no itemnumber supplied";
195 my $item = GetItem($itemnumber);
196 if ( $item->{location} eq 'CART' ) {
197 $item->{location} = $item->{permanent_location};
198 ModItem($item, undef, $itemnumber);
204 ShelfToCart($itemnumber);
206 Set the current shelving location of the item
207 to shelving cart ('CART').
212 my ( $itemnumber ) = @_;
214 unless ( $itemnumber ) {
215 croak "FAILED ShelfToCart() - no itemnumber supplied";
218 my $item = GetItem($itemnumber);
219 $item->{'location'} = 'CART';
220 ModItem($item, undef, $itemnumber);
223 =head2 AddItemFromMarc
225 my ($biblionumber, $biblioitemnumber, $itemnumber)
226 = AddItemFromMarc($source_item_marc, $biblionumber);
228 Given a MARC::Record object containing an embedded item
229 record and a biblionumber, create a new item record.
233 sub AddItemFromMarc {
234 my ( $source_item_marc, $biblionumber ) = @_;
235 my $dbh = C4::Context->dbh;
237 # parse item hash from MARC
238 my $frameworkcode = C4::Biblio::GetFrameworkCode( $biblionumber );
239 my ($itemtag,$itemsubfield)=C4::Biblio::GetMarcFromKohaField("items.itemnumber",$frameworkcode);
241 my $localitemmarc=MARC::Record->new;
242 $localitemmarc->append_fields($source_item_marc->field($itemtag));
243 my $item = &TransformMarcToKoha( $localitemmarc, $frameworkcode ,'items');
244 my $unlinked_item_subfields = _get_unlinked_item_subfields($localitemmarc, $frameworkcode);
245 return AddItem($item, $biblionumber, $dbh, $frameworkcode, $unlinked_item_subfields);
250 my ($biblionumber, $biblioitemnumber, $itemnumber)
251 = AddItem($item, $biblionumber[, $dbh, $frameworkcode, $unlinked_item_subfields]);
253 Given a hash containing item column names as keys,
254 create a new Koha item record.
256 The first two optional parameters (C<$dbh> and C<$frameworkcode>)
257 do not need to be supplied for general use; they exist
258 simply to allow them to be picked up from AddItemFromMarc.
260 The final optional parameter, C<$unlinked_item_subfields>, contains
261 an arrayref containing subfields present in the original MARC
262 representation of the item (e.g., from the item editor) that are
263 not mapped to C<items> columns directly but should instead
264 be stored in C<items.more_subfields_xml> and included in
265 the biblio items tag for display and indexing.
271 my $biblionumber = shift;
273 my $dbh = @_ ? shift : C4::Context->dbh;
274 my $frameworkcode = @_ ? shift : C4::Biblio::GetFrameworkCode($biblionumber);
275 my $unlinked_item_subfields;
277 $unlinked_item_subfields = shift;
280 # needs old biblionumber and biblioitemnumber
281 $item->{'biblionumber'} = $biblionumber;
282 my $sth = $dbh->prepare("SELECT biblioitemnumber FROM biblioitems WHERE biblionumber=?");
283 $sth->execute( $item->{'biblionumber'} );
284 ( $item->{'biblioitemnumber'} ) = $sth->fetchrow;
286 _set_defaults_for_add($item);
287 _set_derived_columns_for_add($item);
288 $item->{'more_subfields_xml'} = _get_unlinked_subfields_xml($unlinked_item_subfields);
290 # FIXME - checks here
291 unless ( $item->{itype} ) { # default to biblioitem.itemtype if no itype
292 my $itype_sth = $dbh->prepare("SELECT itemtype FROM biblioitems WHERE biblionumber = ?");
293 $itype_sth->execute( $item->{'biblionumber'} );
294 ( $item->{'itype'} ) = $itype_sth->fetchrow_array;
297 my ( $itemnumber, $error ) = _koha_new_item( $item, $item->{barcode} );
300 $item->{'itemnumber'} = $itemnumber;
302 ModZebra( $item->{biblionumber}, "specialUpdate", "biblioserver" );
304 logaction( "CATALOGUING", "ADD", $itemnumber, "item" )
305 if C4::Context->preference("CataloguingLog");
307 return ( $item->{biblionumber}, $item->{biblioitemnumber}, $itemnumber );
310 =head2 AddItemBatchFromMarc
312 ($itemnumber_ref, $error_ref) = AddItemBatchFromMarc($record,
313 $biblionumber, $biblioitemnumber, $frameworkcode);
315 Efficiently create item records from a MARC biblio record with
316 embedded item fields. This routine is suitable for batch jobs.
318 This API assumes that the bib record has already been
319 saved to the C<biblio> and C<biblioitems> tables. It does
320 not expect that C<biblio_metadata.metadata> is populated, but it
321 will do so via a call to ModBibiloMarc.
323 The goal of this API is to have a similar effect to using AddBiblio
324 and AddItems in succession, but without inefficient repeated
325 parsing of the MARC XML bib record.
327 This function returns an arrayref of new itemsnumbers and an arrayref of item
328 errors encountered during the processing. Each entry in the errors
329 list is a hashref containing the following keys:
335 Sequence number of original item tag in the MARC record.
339 Item barcode, provide to assist in the construction of
340 useful error messages.
344 Code representing the error condition. Can be 'duplicate_barcode',
345 'invalid_homebranch', or 'invalid_holdingbranch'.
347 =item error_information
349 Additional information appropriate to the error condition.
355 sub AddItemBatchFromMarc {
356 my ($record, $biblionumber, $biblioitemnumber, $frameworkcode) = @_;
358 my @itemnumbers = ();
360 my $dbh = C4::Context->dbh;
362 # We modify the record, so lets work on a clone so we don't change the
364 $record = $record->clone();
365 # loop through the item tags and start creating items
366 my @bad_item_fields = ();
367 my ($itemtag, $itemsubfield) = C4::Biblio::GetMarcFromKohaField("items.itemnumber",'');
368 my $item_sequence_num = 0;
369 ITEMFIELD: foreach my $item_field ($record->field($itemtag)) {
370 $item_sequence_num++;
371 # we take the item field and stick it into a new
372 # MARC record -- this is required so far because (FIXME)
373 # TransformMarcToKoha requires a MARC::Record, not a MARC::Field
374 # and there is no TransformMarcFieldToKoha
375 my $temp_item_marc = MARC::Record->new();
376 $temp_item_marc->append_fields($item_field);
378 # add biblionumber and biblioitemnumber
379 my $item = TransformMarcToKoha( $temp_item_marc, $frameworkcode, 'items' );
380 my $unlinked_item_subfields = _get_unlinked_item_subfields($temp_item_marc, $frameworkcode);
381 $item->{'more_subfields_xml'} = _get_unlinked_subfields_xml($unlinked_item_subfields);
382 $item->{'biblionumber'} = $biblionumber;
383 $item->{'biblioitemnumber'} = $biblioitemnumber;
385 # check for duplicate barcode
386 my %item_errors = CheckItemPreSave($item);
388 push @errors, _repack_item_errors($item_sequence_num, $item, \%item_errors);
389 push @bad_item_fields, $item_field;
393 _set_defaults_for_add($item);
394 _set_derived_columns_for_add($item);
395 my ( $itemnumber, $error ) = _koha_new_item( $item, $item->{barcode} );
396 warn $error if $error;
397 push @itemnumbers, $itemnumber; # FIXME not checking error
398 $item->{'itemnumber'} = $itemnumber;
400 logaction("CATALOGUING", "ADD", $itemnumber, "item") if C4::Context->preference("CataloguingLog");
402 my $new_item_marc = _marc_from_item_hash($item, $frameworkcode, $unlinked_item_subfields);
403 $item_field->replace_with($new_item_marc->field($itemtag));
406 # remove any MARC item fields for rejected items
407 foreach my $item_field (@bad_item_fields) {
408 $record->delete_field($item_field);
411 # update the MARC biblio
412 # $biblionumber = ModBiblioMarc( $record, $biblionumber, $frameworkcode );
414 return (\@itemnumbers, \@errors);
417 =head2 ModItemFromMarc
419 ModItemFromMarc($item_marc, $biblionumber, $itemnumber);
421 This function updates an item record based on a supplied
422 C<MARC::Record> object containing an embedded item field.
423 This API is meant for the use of C<additem.pl>; for
424 other purposes, C<ModItem> should be used.
426 This function uses the hash %default_values_for_mod_from_marc,
427 which contains default values for item fields to
428 apply when modifying an item. This is needed because
429 if an item field's value is cleared, TransformMarcToKoha
430 does not include the column in the
431 hash that's passed to ModItem, which without
432 use of this hash makes it impossible to clear
433 an item field's value. See bug 2466.
435 Note that only columns that can be directly
436 changed from the cataloging and serials
437 item editors are included in this hash.
443 sub _build_default_values_for_mod_marc {
444 my ($frameworkcode) = @_;
446 my $cache = Koha::Caches->get_instance();
447 my $cache_key = "default_value_for_mod_marc-$frameworkcode";
448 my $cached = $cache->get_from_cache($cache_key);
449 return $cached if $cached;
451 my $default_values = {
453 booksellerid => undef,
455 'items.cn_source' => undef,
456 coded_location_qualifier => undef,
460 holdingbranch => undef,
462 itemcallnumber => undef,
465 itemnotes_nonpublic => undef,
468 permanent_location => undef,
472 # paidfor => undef, # commented, see bug 12817
474 replacementprice => undef,
475 replacementpricedate => undef,
478 stocknumber => undef,
482 my %default_values_for_mod_from_marc;
483 while ( my ( $field, $default_value ) = each %$default_values ) {
484 my $kohafield = $field;
485 $kohafield =~ s|^([^\.]+)$|items.$1|;
486 $default_values_for_mod_from_marc{$field} =
488 if C4::Koha::IsKohaFieldLinked(
489 { kohafield => $kohafield, frameworkcode => $frameworkcode } );
492 $cache->set_in_cache($cache_key, \%default_values_for_mod_from_marc);
493 return \%default_values_for_mod_from_marc;
496 sub ModItemFromMarc {
497 my $item_marc = shift;
498 my $biblionumber = shift;
499 my $itemnumber = shift;
501 my $dbh = C4::Context->dbh;
502 my $frameworkcode = C4::Biblio::GetFrameworkCode($biblionumber);
503 my ( $itemtag, $itemsubfield ) = C4::Biblio::GetMarcFromKohaField( "items.itemnumber", $frameworkcode );
505 my $localitemmarc = MARC::Record->new;
506 $localitemmarc->append_fields( $item_marc->field($itemtag) );
507 my $item = &TransformMarcToKoha( $localitemmarc, $frameworkcode, 'items' );
508 my $default_values = _build_default_values_for_mod_marc($frameworkcode);
509 foreach my $item_field ( keys %$default_values ) {
510 $item->{$item_field} = $default_values->{$item_field}
511 unless exists $item->{$item_field};
513 my $unlinked_item_subfields = _get_unlinked_item_subfields( $localitemmarc, $frameworkcode );
515 ModItem($item, $biblionumber, $itemnumber, $dbh, $frameworkcode, $unlinked_item_subfields);
521 ModItem({ column => $newvalue }, $biblionumber, $itemnumber);
523 Change one or more columns in an item record and update
524 the MARC representation of the item.
526 The first argument is a hashref mapping from item column
527 names to the new values. The second and third arguments
528 are the biblionumber and itemnumber, respectively.
530 The fourth, optional parameter, C<$unlinked_item_subfields>, contains
531 an arrayref containing subfields present in the original MARC
532 representation of the item (e.g., from the item editor) that are
533 not mapped to C<items> columns directly but should instead
534 be stored in C<items.more_subfields_xml> and included in
535 the biblio items tag for display and indexing.
537 If one of the changed columns is used to calculate
538 the derived value of a column such as C<items.cn_sort>,
539 this routine will perform the necessary calculation
546 my $biblionumber = shift;
547 my $itemnumber = shift;
549 # if $biblionumber is undefined, get it from the current item
550 unless (defined $biblionumber) {
551 $biblionumber = _get_single_item_column('biblionumber', $itemnumber);
554 my $dbh = @_ ? shift : C4::Context->dbh;
555 my $frameworkcode = @_ ? shift : C4::Biblio::GetFrameworkCode( $biblionumber );
557 my $unlinked_item_subfields;
559 $unlinked_item_subfields = shift;
560 $item->{'more_subfields_xml'} = _get_unlinked_subfields_xml($unlinked_item_subfields);
563 $item->{'itemnumber'} = $itemnumber or return;
565 my @fields = qw( itemlost withdrawn );
567 # Only call GetItem if we need to set an "on" date field
568 if ( $item->{itemlost} || $item->{withdrawn} ) {
569 my $pre_mod_item = GetItem( $item->{'itemnumber'} );
570 for my $field (@fields) {
571 if ( defined( $item->{$field} )
572 and not $pre_mod_item->{$field}
573 and $item->{$field} )
575 $item->{ $field . '_on' } =
576 DateTime::Format::MySQL->format_datetime( dt_from_string() );
581 # If the field is defined but empty, we are removing and,
582 # and thus need to clear out the 'on' field as well
583 for my $field (@fields) {
584 if ( defined( $item->{$field} ) && !$item->{$field} ) {
585 $item->{ $field . '_on' } = undef;
590 _set_derived_columns_for_mod($item);
591 _do_column_fixes_for_mod($item);
594 # attempt to change itemnumber
595 # attempt to change biblionumber (if we want
596 # an API to relink an item to a different bib,
597 # it should be a separate function)
600 _koha_modify_item($item);
602 # request that bib be reindexed so that searching on current
603 # item status is possible
604 ModZebra( $biblionumber, "specialUpdate", "biblioserver" );
606 logaction("CATALOGUING", "MODIFY", $itemnumber, "item ".Dumper($item)) if C4::Context->preference("CataloguingLog");
609 =head2 ModItemTransfer
611 ModItemTransfer($itenumber, $frombranch, $tobranch);
613 Marks an item as being transferred from one branch
618 sub ModItemTransfer {
619 my ( $itemnumber, $frombranch, $tobranch ) = @_;
621 my $dbh = C4::Context->dbh;
623 # Remove the 'shelving cart' location status if it is being used.
624 CartToShelf( $itemnumber ) if ( C4::Context->preference("ReturnToShelvingCart") );
626 $dbh->do("UPDATE branchtransfers SET datearrived = NOW(), comments = ? WHERE itemnumber = ? AND datearrived IS NULL", undef, "Canceled, new transfer from $frombranch to $tobranch created", $itemnumber);
628 #new entry in branchtransfers....
629 my $sth = $dbh->prepare(
630 "INSERT INTO branchtransfers (itemnumber, frombranch, datesent, tobranch)
631 VALUES (?, ?, NOW(), ?)");
632 $sth->execute($itemnumber, $frombranch, $tobranch);
634 ModItem({ holdingbranch => $tobranch }, undef, $itemnumber);
635 ModDateLastSeen($itemnumber);
639 =head2 ModDateLastSeen
641 ModDateLastSeen($itemnum);
643 Mark item as seen. Is called when an item is issued, returned or manually marked during inventory/stocktaking.
644 C<$itemnum> is the item number
648 sub ModDateLastSeen {
649 my ($itemnumber) = @_;
651 my $today = output_pref({ dt => dt_from_string, dateformat => 'iso', dateonly => 1 });
652 ModItem({ itemlost => 0, datelastseen => $today }, undef, $itemnumber);
657 DelItem({ itemnumber => $itemnumber, [ biblionumber => $biblionumber ] } );
659 Exported function (core API) for deleting an item record in Koha.
666 my $itemnumber = $params->{itemnumber};
667 my $biblionumber = $params->{biblionumber};
669 unless ($biblionumber) {
670 my $item = Koha::Items->find( $itemnumber );
671 $biblionumber = $item ? $item->biblio->biblionumber : undef;
674 # If there is no biblionumber for the given itemnumber, there is nothing to delete
675 return 0 unless $biblionumber;
677 # FIXME check the item has no current issues
678 my $deleted = _koha_delete_item( $itemnumber );
680 ModZebra( $biblionumber, "specialUpdate", "biblioserver" );
682 #search item field code
683 logaction("CATALOGUING", "DELETE", $itemnumber, "item") if C4::Context->preference("CataloguingLog");
687 =head2 CheckItemPreSave
689 my $item_ref = TransformMarcToKoha($marc, 'items');
691 my %errors = CheckItemPreSave($item_ref);
692 if (exists $errors{'duplicate_barcode'}) {
693 print "item has duplicate barcode: ", $errors{'duplicate_barcode'}, "\n";
694 } elsif (exists $errors{'invalid_homebranch'}) {
695 print "item has invalid home branch: ", $errors{'invalid_homebranch'}, "\n";
696 } elsif (exists $errors{'invalid_holdingbranch'}) {
697 print "item has invalid holding branch: ", $errors{'invalid_holdingbranch'}, "\n";
702 Given a hashref containing item fields, determine if it can be
703 inserted or updated in the database. Specifically, checks for
704 database integrity issues, and returns a hash containing any
705 of the following keys, if applicable.
709 =item duplicate_barcode
711 Barcode, if it duplicates one already found in the database.
713 =item invalid_homebranch
715 Home branch, if not defined in branches table.
717 =item invalid_holdingbranch
719 Holding branch, if not defined in branches table.
723 This function does NOT implement any policy-related checks,
724 e.g., whether current operator is allowed to save an
725 item that has a given branch code.
729 sub CheckItemPreSave {
730 my $item_ref = shift;
734 # check for duplicate barcode
735 if (exists $item_ref->{'barcode'} and defined $item_ref->{'barcode'}) {
736 my $existing_itemnumber = GetItemnumberFromBarcode($item_ref->{'barcode'});
737 if ($existing_itemnumber) {
738 if (!exists $item_ref->{'itemnumber'} # new item
739 or $item_ref->{'itemnumber'} != $existing_itemnumber) { # existing item
740 $errors{'duplicate_barcode'} = $item_ref->{'barcode'};
745 # check for valid home branch
746 if (exists $item_ref->{'homebranch'} and defined $item_ref->{'homebranch'}) {
747 my $home_library = Koha::Libraries->find( $item_ref->{homebranch} );
748 unless (defined $home_library) {
749 $errors{'invalid_homebranch'} = $item_ref->{'homebranch'};
753 # check for valid holding branch
754 if (exists $item_ref->{'holdingbranch'} and defined $item_ref->{'holdingbranch'}) {
755 my $holding_library = Koha::Libraries->find( $item_ref->{holdingbranch} );
756 unless (defined $holding_library) {
757 $errors{'invalid_holdingbranch'} = $item_ref->{'holdingbranch'};
765 =head1 EXPORTED SPECIAL ACCESSOR FUNCTIONS
767 The following functions provide various ways of
768 getting an item record, a set of item records, or
769 lists of authorized values for certain item fields.
771 Some of the functions in this group are candidates
772 for refactoring -- for example, some of the code
773 in C<GetItemsByBiblioitemnumber> and C<GetItemsInfo>
774 has copy-and-paste work.
778 =head2 GetItemsForInventory
780 ($itemlist, $iTotalRecords) = GetItemsForInventory( {
781 minlocation => $minlocation,
782 maxlocation => $maxlocation,
783 location => $location,
784 itemtype => $itemtype,
785 ignoreissued => $ignoreissued,
786 datelastseen => $datelastseen,
787 branchcode => $branchcode,
791 statushash => $statushash,
794 Retrieve a list of title/authors/barcode/callnumber, for biblio inventory.
796 The sub returns a reference to a list of hashes, each containing
797 itemnumber, author, title, barcode, item callnumber, and date last
798 seen. It is ordered by callnumber then title.
800 The required minlocation & maxlocation parameters are used to specify a range of item callnumbers
801 the datelastseen can be used to specify that you want to see items not seen since a past date only.
802 offset & size can be used to retrieve only a part of the whole listing (defaut behaviour)
803 $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.
805 $iTotalRecords is the number of rows that would have been returned without the $offset, $size limit clause
809 sub GetItemsForInventory {
810 my ( $parameters ) = @_;
811 my $minlocation = $parameters->{'minlocation'} // '';
812 my $maxlocation = $parameters->{'maxlocation'} // '';
813 my $location = $parameters->{'location'} // '';
814 my $itemtype = $parameters->{'itemtype'} // '';
815 my $ignoreissued = $parameters->{'ignoreissued'} // '';
816 my $datelastseen = $parameters->{'datelastseen'} // '';
817 my $branchcode = $parameters->{'branchcode'} // '';
818 my $branch = $parameters->{'branch'} // '';
819 my $offset = $parameters->{'offset'} // '';
820 my $size = $parameters->{'size'} // '';
821 my $statushash = $parameters->{'statushash'} // '';
823 my $dbh = C4::Context->dbh;
824 my ( @bind_params, @where_strings );
826 my $select_columns = q{
827 SELECT items.itemnumber, barcode, itemcallnumber, title, author, biblio.biblionumber, biblio.frameworkcode, datelastseen, homebranch, location, notforloan, damaged, itemlost, withdrawn, stocknumber
829 my $select_count = q{SELECT COUNT(*)};
832 LEFT JOIN biblio ON items.biblionumber = biblio.biblionumber
833 LEFT JOIN biblioitems on items.biblionumber = biblioitems.biblionumber
836 for my $authvfield (keys %$statushash){
837 if ( scalar @{$statushash->{$authvfield}} > 0 ){
838 my $joinedvals = join ',', @{$statushash->{$authvfield}};
839 push @where_strings, "$authvfield in (" . $joinedvals . ")";
845 push @where_strings, 'itemcallnumber >= ?';
846 push @bind_params, $minlocation;
850 push @where_strings, 'itemcallnumber <= ?';
851 push @bind_params, $maxlocation;
855 $datelastseen = output_pref({ str => $datelastseen, dateformat => 'iso', dateonly => 1 });
856 push @where_strings, '(datelastseen < ? OR datelastseen IS NULL)';
857 push @bind_params, $datelastseen;
861 push @where_strings, 'items.location = ?';
862 push @bind_params, $location;
866 if($branch eq "homebranch"){
867 push @where_strings, 'items.homebranch = ?';
869 push @where_strings, 'items.holdingbranch = ?';
871 push @bind_params, $branchcode;
875 push @where_strings, 'biblioitems.itemtype = ?';
876 push @bind_params, $itemtype;
879 if ( $ignoreissued) {
880 $query .= "LEFT JOIN issues ON items.itemnumber = issues.itemnumber ";
881 push @where_strings, 'issues.date_due IS NULL';
884 if ( @where_strings ) {
886 $query .= join ' AND ', @where_strings;
888 $query .= ' ORDER BY items.cn_sort, itemcallnumber, title';
889 my $count_query = $select_count . $query;
890 $query .= " LIMIT $offset, $size" if ($offset and $size);
891 $query = $select_columns . $query;
892 my $sth = $dbh->prepare($query);
893 $sth->execute( @bind_params );
896 my $tmpresults = $sth->fetchall_arrayref({});
897 $sth = $dbh->prepare( $count_query );
898 $sth->execute( @bind_params );
899 my ($iTotalRecords) = $sth->fetchrow_array();
901 my @avs = Koha::AuthorisedValues->search(
902 { 'marc_subfield_structures.kohafield' => { '>' => '' },
903 'me.authorised_value' => { '>' => '' },
905 { join => { category => 'marc_subfield_structures' },
906 distinct => ['marc_subfield_structures.kohafield, me.category, frameworkcode, me.authorised_value'],
907 '+select' => [ 'marc_subfield_structures.kohafield', 'marc_subfield_structures.frameworkcode', 'me.authorised_value', 'me.lib' ],
908 '+as' => [ 'kohafield', 'frameworkcode', 'authorised_value', 'lib' ],
912 my $avmapping = { map { $_->get_column('kohafield') . ',' . $_->get_column('frameworkcode') . ',' . $_->get_column('authorised_value') => $_->get_column('lib') } @avs };
914 foreach my $row (@$tmpresults) {
917 foreach (keys %$row) {
918 if (defined($avmapping->{"items.$_,".$row->{'frameworkcode'}.",".$row->{$_}})) {
919 $row->{$_} = $avmapping->{"items.$_,".$row->{'frameworkcode'}.",".$row->{$_}};
925 return (\@results, $iTotalRecords);
928 =head2 GetItemsByBiblioitemnumber
930 GetItemsByBiblioitemnumber($biblioitemnumber);
932 Returns an arrayref of hashrefs suitable for use in a TMPL_LOOP
933 Called by C<C4::XISBN>
937 sub GetItemsByBiblioitemnumber {
938 my ( $bibitem ) = @_;
939 my $dbh = C4::Context->dbh;
940 my $sth = $dbh->prepare("SELECT * FROM items WHERE items.biblioitemnumber = ?") || die $dbh->errstr;
941 # Get all items attached to a biblioitem
944 $sth->execute($bibitem) || die $sth->errstr;
945 while ( my $data = $sth->fetchrow_hashref ) {
946 # Foreach item, get circulation information
947 my $sth2 = $dbh->prepare( "SELECT * FROM issues,borrowers
949 AND issues.borrowernumber = borrowers.borrowernumber"
951 $sth2->execute( $data->{'itemnumber'} );
952 if ( my $data2 = $sth2->fetchrow_hashref ) {
953 # if item is out, set the due date and who it is out too
954 $data->{'date_due'} = $data2->{'date_due'};
955 $data->{'cardnumber'} = $data2->{'cardnumber'};
956 $data->{'borrowernumber'} = $data2->{'borrowernumber'};
959 # set date_due to blank, so in the template we check itemlost, and withdrawn
960 $data->{'date_due'} = '';
962 # Find the last 3 people who borrowed this item.
963 my $query2 = "SELECT * FROM old_issues, borrowers WHERE itemnumber = ?
964 AND old_issues.borrowernumber = borrowers.borrowernumber
965 ORDER BY returndate desc,timestamp desc LIMIT 3";
966 $sth2 = $dbh->prepare($query2) || die $dbh->errstr;
967 $sth2->execute( $data->{'itemnumber'} ) || die $sth2->errstr;
969 while ( my $data2 = $sth2->fetchrow_hashref ) {
970 $data->{"timestamp$i2"} = $data2->{'timestamp'};
971 $data->{"card$i2"} = $data2->{'cardnumber'};
972 $data->{"borrower$i2"} = $data2->{'borrowernumber'};
975 push(@results,$data);
982 @results = GetItemsInfo($biblionumber);
984 Returns information about items with the given biblionumber.
986 C<GetItemsInfo> returns a list of references-to-hash. Each element
987 contains a number of keys. Most of them are attributes from the
988 C<biblio>, C<biblioitems>, C<items>, and C<itemtypes> tables in the
989 Koha database. Other keys include:
993 =item C<$data-E<gt>{branchname}>
995 The name (not the code) of the branch to which the book belongs.
997 =item C<$data-E<gt>{datelastseen}>
999 This is simply C<items.datelastseen>, except that while the date is
1000 stored in YYYY-MM-DD format in the database, here it is converted to
1001 DD/MM/YYYY format. A NULL date is returned as C<//>.
1003 =item C<$data-E<gt>{datedue}>
1005 =item C<$data-E<gt>{class}>
1007 This is the concatenation of C<biblioitems.classification>, the book's
1008 Dewey code, and C<biblioitems.subclass>.
1010 =item C<$data-E<gt>{ocount}>
1012 I think this is the number of copies of the book available.
1014 =item C<$data-E<gt>{order}>
1016 If this is set, it is set to C<One Order>.
1023 my ( $biblionumber ) = @_;
1024 my $dbh = C4::Context->dbh;
1025 require C4::Languages;
1026 my $language = C4::Languages::getlanguage();
1032 biblioitems.itemtype,
1035 biblioitems.publicationyear,
1036 biblioitems.publishercode,
1037 biblioitems.volumedate,
1038 biblioitems.volumedesc,
1041 items.notforloan as itemnotforloan,
1042 issues.borrowernumber,
1043 issues.date_due as datedue,
1044 issues.onsite_checkout,
1045 borrowers.cardnumber,
1047 borrowers.firstname,
1048 borrowers.branchcode as bcode,
1050 serial.publisheddate,
1051 itemtypes.description,
1052 COALESCE( localization.translation, itemtypes.description ) AS translated_description,
1053 itemtypes.notforloan as notforloan_per_itemtype,
1057 holding.opac_info as holding_branch_opac_info,
1058 home.opac_info as home_branch_opac_info
1062 LEFT JOIN branches AS holding ON items.holdingbranch = holding.branchcode
1063 LEFT JOIN branches AS home ON items.homebranch=home.branchcode
1064 LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber
1065 LEFT JOIN biblioitems ON biblioitems.biblioitemnumber = items.biblioitemnumber
1066 LEFT JOIN issues USING (itemnumber)
1067 LEFT JOIN borrowers USING (borrowernumber)
1068 LEFT JOIN serialitems USING (itemnumber)
1069 LEFT JOIN serial USING (serialid)
1070 LEFT JOIN itemtypes ON itemtypes.itemtype = "
1071 . (C4::Context->preference('item-level_itypes') ? 'items.itype' : 'biblioitems.itemtype');
1073 LEFT JOIN localization ON itemtypes.itemtype = localization.code
1074 AND localization.entity = 'itemtypes'
1075 AND localization.lang = ?
1078 $query .= " WHERE items.biblionumber = ? ORDER BY home.branchname, items.enumchron, LPAD( items.copynumber, 8, '0' ), items.dateaccessioned DESC" ;
1079 my $sth = $dbh->prepare($query);
1080 $sth->execute($language, $biblionumber);
1085 my $userenv = C4::Context->userenv;
1086 my $want_not_same_branch = C4::Context->preference("IndependentBranches") && !C4::Context->IsSuperLibrarian();
1087 while ( my $data = $sth->fetchrow_hashref ) {
1088 if ( $data->{borrowernumber} && $want_not_same_branch) {
1089 $data->{'NOTSAMEBRANCH'} = $data->{'bcode'} ne $userenv->{branch};
1092 $serial ||= $data->{'serial'};
1095 # get notforloan complete status if applicable
1096 $descriptions = Koha::AuthorisedValues->get_description_by_koha_field({frameworkcode => $data->{frameworkcode}, kohafield => 'items.notforloan', authorised_value => $data->{itemnotforloan} });
1097 $data->{notforloanvalue} = $descriptions->{lib} // '';
1098 $data->{notforloanvalueopac} = $descriptions->{opac_description} // '';
1100 # get restricted status and description if applicable
1101 $descriptions = Koha::AuthorisedValues->get_description_by_koha_field({frameworkcode => $data->{frameworkcode}, kohafield => 'items.restricted', authorised_value => $data->{restricted} });
1102 $data->{restricted} = $descriptions->{lib} // '';
1103 $data->{restrictedopac} = $descriptions->{opac_description} // '';
1105 # my stack procedures
1106 $descriptions = Koha::AuthorisedValues->get_description_by_koha_field({frameworkcode => $data->{frameworkcode}, kohafield => 'items.stack', authorised_value => $data->{stack} });
1107 $data->{stack} = $descriptions->{lib} // '';
1109 # Find the last 3 people who borrowed this item.
1110 my $sth2 = $dbh->prepare("SELECT * FROM old_issues,borrowers
1111 WHERE itemnumber = ?
1112 AND old_issues.borrowernumber = borrowers.borrowernumber
1113 ORDER BY returndate DESC
1115 $sth2->execute($data->{'itemnumber'});
1117 while (my $data2 = $sth2->fetchrow_hashref()) {
1118 $data->{"timestamp$ii"} = $data2->{'timestamp'} if $data2->{'timestamp'};
1119 $data->{"card$ii"} = $data2->{'cardnumber'} if $data2->{'cardnumber'};
1120 $data->{"borrower$ii"} = $data2->{'borrowernumber'} if $data2->{'borrowernumber'};
1124 $results[$i] = $data;
1129 ? sort { ($b->{'publisheddate'} || $b->{'enumchron'}) cmp ($a->{'publisheddate'} || $a->{'enumchron'}) } @results
1133 =head2 GetItemsLocationInfo
1135 my @itemlocinfo = GetItemsLocationInfo($biblionumber);
1137 Returns the branch names, shelving location and itemcallnumber for each item attached to the biblio in question
1139 C<GetItemsInfo> returns a list of references-to-hash. Data returned:
1143 =item C<$data-E<gt>{homebranch}>
1145 Branch Name of the item's homebranch
1147 =item C<$data-E<gt>{holdingbranch}>
1149 Branch Name of the item's holdingbranch
1151 =item C<$data-E<gt>{location}>
1153 Item's shelving location code
1155 =item C<$data-E<gt>{location_intranet}>
1157 The intranet description for the Shelving Location as set in authorised_values 'LOC'
1159 =item C<$data-E<gt>{location_opac}>
1161 The OPAC description for the Shelving Location as set in authorised_values 'LOC'. Falls back to intranet description if no OPAC
1164 =item C<$data-E<gt>{itemcallnumber}>
1166 Item's itemcallnumber
1168 =item C<$data-E<gt>{cn_sort}>
1170 Item's call number normalized for sorting
1176 sub GetItemsLocationInfo {
1177 my $biblionumber = shift;
1180 my $dbh = C4::Context->dbh;
1181 my $query = "SELECT a.branchname as homebranch, b.branchname as holdingbranch,
1182 location, itemcallnumber, cn_sort
1183 FROM items, branches as a, branches as b
1184 WHERE homebranch = a.branchcode AND holdingbranch = b.branchcode
1185 AND biblionumber = ?
1186 ORDER BY cn_sort ASC";
1187 my $sth = $dbh->prepare($query);
1188 $sth->execute($biblionumber);
1190 while ( my $data = $sth->fetchrow_hashref ) {
1191 my $av = Koha::AuthorisedValues->search({ category => 'LOC', authorised_value => $data->{location} });
1192 $av = $av->count ? $av->next : undef;
1193 $data->{location_intranet} = $av ? $av->lib : '';
1194 $data->{location_opac} = $av ? $av->opac_description : '';
1195 push @results, $data;
1200 =head2 GetHostItemsInfo
1202 $hostiteminfo = GetHostItemsInfo($hostfield);
1203 Returns the iteminfo for items linked to records via a host field
1207 sub GetHostItemsInfo {
1209 my @returnitemsInfo;
1211 if (C4::Context->preference('marcflavour') eq 'MARC21' ||
1212 C4::Context->preference('marcflavour') eq 'NORMARC'){
1213 foreach my $hostfield ( $record->field('773') ) {
1214 my $hostbiblionumber = $hostfield->subfield("0");
1215 my $linkeditemnumber = $hostfield->subfield("9");
1216 my @hostitemInfos = GetItemsInfo($hostbiblionumber);
1217 foreach my $hostitemInfo (@hostitemInfos){
1218 if ($hostitemInfo->{itemnumber} eq $linkeditemnumber){
1219 push (@returnitemsInfo,$hostitemInfo);
1224 } elsif ( C4::Context->preference('marcflavour') eq 'UNIMARC'){
1225 foreach my $hostfield ( $record->field('461') ) {
1226 my $hostbiblionumber = $hostfield->subfield("0");
1227 my $linkeditemnumber = $hostfield->subfield("9");
1228 my @hostitemInfos = GetItemsInfo($hostbiblionumber);
1229 foreach my $hostitemInfo (@hostitemInfos){
1230 if ($hostitemInfo->{itemnumber} eq $linkeditemnumber){
1231 push (@returnitemsInfo,$hostitemInfo);
1237 return @returnitemsInfo;
1241 =head2 GetLastAcquisitions
1243 my $lastacq = GetLastAcquisitions({'branches' => ('branch1','branch2'),
1244 'itemtypes' => ('BK','BD')}, 10);
1248 sub GetLastAcquisitions {
1249 my ($data,$max) = @_;
1251 my $itemtype = C4::Context->preference('item-level_itypes') ? 'itype' : 'itemtype';
1253 my $number_of_branches = @{$data->{branches}};
1254 my $number_of_itemtypes = @{$data->{itemtypes}};
1257 my @where = ('WHERE 1 ');
1258 $number_of_branches and push @where
1259 , 'AND holdingbranch IN ('
1260 , join(',', ('?') x $number_of_branches )
1264 $number_of_itemtypes and push @where
1265 , "AND $itemtype IN ("
1266 , join(',', ('?') x $number_of_itemtypes )
1270 my $query = "SELECT biblio.biblionumber as biblionumber, title, dateaccessioned
1271 FROM items RIGHT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
1272 RIGHT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber)
1274 GROUP BY biblio.biblionumber
1275 ORDER BY dateaccessioned DESC LIMIT $max";
1277 my $dbh = C4::Context->dbh;
1278 my $sth = $dbh->prepare($query);
1280 $sth->execute((@{$data->{branches}}, @{$data->{itemtypes}}));
1283 while( my $row = $sth->fetchrow_hashref){
1284 push @results, {date => $row->{dateaccessioned}
1285 , biblionumber => $row->{biblionumber}
1286 , title => $row->{title}};
1292 =head2 GetItemnumbersForBiblio
1294 my $itemnumbers = GetItemnumbersForBiblio($biblionumber);
1296 Given a single biblionumber, return an arrayref of all the corresponding itemnumbers
1300 sub GetItemnumbersForBiblio {
1301 my $biblionumber = shift;
1303 my $dbh = C4::Context->dbh;
1304 my $sth = $dbh->prepare("SELECT itemnumber FROM items WHERE biblionumber = ?");
1305 $sth->execute($biblionumber);
1306 while (my $result = $sth->fetchrow_hashref) {
1307 push @items, $result->{'itemnumber'};
1312 =head2 get_hostitemnumbers_of
1314 my @itemnumbers_of = get_hostitemnumbers_of($biblionumber);
1316 Given a biblionumber, return the list of corresponding itemnumbers that are linked to it via host fields
1318 Return a reference on a hash where key is a biblionumber and values are
1319 references on array of itemnumbers.
1324 sub get_hostitemnumbers_of {
1325 my ($biblionumber) = @_;
1326 my $marcrecord = C4::Biblio::GetMarcBiblio({ biblionumber => $biblionumber });
1328 return unless $marcrecord;
1330 my ( @returnhostitemnumbers, $tag, $biblio_s, $item_s );
1332 my $marcflavor = C4::Context->preference('marcflavour');
1333 if ( $marcflavor eq 'MARC21' || $marcflavor eq 'NORMARC' ) {
1338 elsif ( $marcflavor eq 'UNIMARC' ) {
1344 foreach my $hostfield ( $marcrecord->field($tag) ) {
1345 my $hostbiblionumber = $hostfield->subfield($biblio_s);
1346 next unless $hostbiblionumber; # have tag, don't have $biblio_s subfield
1347 my $linkeditemnumber = $hostfield->subfield($item_s);
1348 if ( ! $linkeditemnumber ) {
1349 warn "ERROR biblionumber $biblionumber has 773^0, but doesn't have 9";
1352 my $is_from_biblio = Koha::Items->search({ itemnumber => $linkeditemnumber, biblionumber => $hostbiblionumber });
1353 push @returnhostitemnumbers, $linkeditemnumber
1357 return @returnhostitemnumbers;
1361 =head2 GetItemnumberFromBarcode
1363 $result = GetItemnumberFromBarcode($barcode);
1367 sub GetItemnumberFromBarcode {
1369 my $dbh = C4::Context->dbh;
1372 $dbh->prepare("SELECT itemnumber FROM items WHERE items.barcode=?");
1373 $rq->execute($barcode);
1374 my ($result) = $rq->fetchrow;
1378 =head2 GetBarcodeFromItemnumber
1380 $result = GetBarcodeFromItemnumber($itemnumber);
1384 sub GetBarcodeFromItemnumber {
1385 my ($itemnumber) = @_;
1386 my $dbh = C4::Context->dbh;
1389 $dbh->prepare("SELECT barcode FROM items WHERE items.itemnumber=?");
1390 $rq->execute($itemnumber);
1391 my ($result) = $rq->fetchrow;
1395 =head2 GetHiddenItemnumbers
1397 my @itemnumbers_to_hide = GetHiddenItemnumbers(@items);
1399 Given a list of items it checks which should be hidden from the OPAC given
1400 the current configuration. Returns a list of itemnumbers corresponding to
1401 those that should be hidden.
1405 sub GetHiddenItemnumbers {
1409 my $yaml = C4::Context->preference('OpacHiddenItems');
1410 return () if (! $yaml =~ /\S/ );
1411 $yaml = "$yaml\n\n"; # YAML is anal on ending \n. Surplus does not hurt
1414 $hidingrules = YAML::Load($yaml);
1417 warn "Unable to parse OpacHiddenItems syspref : $@";
1420 my $dbh = C4::Context->dbh;
1423 foreach my $item (@items) {
1425 # We check each rule
1426 foreach my $field (keys %$hidingrules) {
1428 if (exists $item->{$field}) {
1429 $val = $item->{$field};
1432 my $query = "SELECT $field from items where itemnumber = ?";
1433 $val = $dbh->selectrow_array($query, undef, $item->{'itemnumber'});
1435 $val = '' unless defined $val;
1437 # If the results matches the values in the yaml file
1438 if (any { $val eq $_ } @{$hidingrules->{$field}}) {
1440 # We add the itemnumber to the list
1441 push @resultitems, $item->{'itemnumber'};
1443 # If at least one rule matched for an item, no need to test the others
1448 return @resultitems;
1451 =head1 LIMITED USE FUNCTIONS
1453 The following functions, while part of the public API,
1454 are not exported. This is generally because they are
1455 meant to be used by only one script for a specific
1456 purpose, and should not be used in any other context
1457 without careful thought.
1463 my $item_marc = GetMarcItem($biblionumber, $itemnumber);
1465 Returns MARC::Record of the item passed in parameter.
1466 This function is meant for use only in C<cataloguing/additem.pl>,
1467 where it is needed to support that script's MARC-like
1473 my ( $biblionumber, $itemnumber ) = @_;
1475 # GetMarcItem has been revised so that it does the following:
1476 # 1. Gets the item information from the items table.
1477 # 2. Converts it to a MARC field for storage in the bib record.
1479 # The previous behavior was:
1480 # 1. Get the bib record.
1481 # 2. Return the MARC tag corresponding to the item record.
1483 # The difference is that one treats the items row as authoritative,
1484 # while the other treats the MARC representation as authoritative
1485 # under certain circumstances.
1487 my $itemrecord = GetItem($itemnumber);
1489 # Tack on 'items.' prefix to column names so that C4::Biblio::TransformKohaToMarc will work.
1490 # Also, don't emit a subfield if the underlying field is blank.
1493 return Item2Marc($itemrecord,$biblionumber);
1497 my ($itemrecord,$biblionumber)=@_;
1500 defined($itemrecord->{$_}) && $itemrecord->{$_} ne '' ? ("items.$_" => $itemrecord->{$_}) : ()
1501 } keys %{ $itemrecord }
1503 my $itemmarc = C4::Biblio::TransformKohaToMarc($mungeditem);
1504 my ( $itemtag, $itemsubfield ) = C4::Biblio::GetMarcFromKohaField("items.itemnumber",C4::Biblio::GetFrameworkCode($biblionumber)||'');
1506 my $unlinked_item_subfields = _parse_unlinked_item_subfields_from_xml($mungeditem->{'items.more_subfields_xml'});
1507 if (defined $unlinked_item_subfields and $#$unlinked_item_subfields > -1) {
1508 foreach my $field ($itemmarc->field($itemtag)){
1509 $field->add_subfields(@$unlinked_item_subfields);
1515 =head1 PRIVATE FUNCTIONS AND VARIABLES
1517 The following functions are not meant to be called
1518 directly, but are documented in order to explain
1519 the inner workings of C<C4::Items>.
1523 =head2 %derived_columns
1525 This hash keeps track of item columns that
1526 are strictly derived from other columns in
1527 the item record and are not meant to be set
1530 Each key in the hash should be the name of a
1531 column (as named by TransformMarcToKoha). Each
1532 value should be hashref whose keys are the
1533 columns on which the derived column depends. The
1534 hashref should also contain a 'BUILDER' key
1535 that is a reference to a sub that calculates
1540 my %derived_columns = (
1541 'items.cn_sort' => {
1542 'itemcallnumber' => 1,
1543 'items.cn_source' => 1,
1544 'BUILDER' => \&_calc_items_cn_sort,
1548 =head2 _set_derived_columns_for_add
1550 _set_derived_column_for_add($item);
1552 Given an item hash representing a new item to be added,
1553 calculate any derived columns. Currently the only
1554 such column is C<items.cn_sort>.
1558 sub _set_derived_columns_for_add {
1561 foreach my $column (keys %derived_columns) {
1562 my $builder = $derived_columns{$column}->{'BUILDER'};
1563 my $source_values = {};
1564 foreach my $source_column (keys %{ $derived_columns{$column} }) {
1565 next if $source_column eq 'BUILDER';
1566 $source_values->{$source_column} = $item->{$source_column};
1568 $builder->($item, $source_values);
1572 =head2 _set_derived_columns_for_mod
1574 _set_derived_column_for_mod($item);
1576 Given an item hash representing a new item to be modified.
1577 calculate any derived columns. Currently the only
1578 such column is C<items.cn_sort>.
1580 This routine differs from C<_set_derived_columns_for_add>
1581 in that it needs to handle partial item records. In other
1582 words, the caller of C<ModItem> may have supplied only one
1583 or two columns to be changed, so this function needs to
1584 determine whether any of the columns to be changed affect
1585 any of the derived columns. Also, if a derived column
1586 depends on more than one column, but the caller is not
1587 changing all of then, this routine retrieves the unchanged
1588 values from the database in order to ensure a correct
1593 sub _set_derived_columns_for_mod {
1596 foreach my $column (keys %derived_columns) {
1597 my $builder = $derived_columns{$column}->{'BUILDER'};
1598 my $source_values = {};
1599 my %missing_sources = ();
1600 my $must_recalc = 0;
1601 foreach my $source_column (keys %{ $derived_columns{$column} }) {
1602 next if $source_column eq 'BUILDER';
1603 if (exists $item->{$source_column}) {
1605 $source_values->{$source_column} = $item->{$source_column};
1607 $missing_sources{$source_column} = 1;
1611 foreach my $source_column (keys %missing_sources) {
1612 $source_values->{$source_column} = _get_single_item_column($source_column, $item->{'itemnumber'});
1614 $builder->($item, $source_values);
1619 =head2 _do_column_fixes_for_mod
1621 _do_column_fixes_for_mod($item);
1623 Given an item hashref containing one or more
1624 columns to modify, fix up certain values.
1625 Specifically, set to 0 any passed value
1626 of C<notforloan>, C<damaged>, C<itemlost>, or
1627 C<withdrawn> that is either undefined or
1628 contains the empty string.
1632 sub _do_column_fixes_for_mod {
1635 if (exists $item->{'notforloan'} and
1636 (not defined $item->{'notforloan'} or $item->{'notforloan'} eq '')) {
1637 $item->{'notforloan'} = 0;
1639 if (exists $item->{'damaged'} and
1640 (not defined $item->{'damaged'} or $item->{'damaged'} eq '')) {
1641 $item->{'damaged'} = 0;
1643 if (exists $item->{'itemlost'} and
1644 (not defined $item->{'itemlost'} or $item->{'itemlost'} eq '')) {
1645 $item->{'itemlost'} = 0;
1647 if (exists $item->{'withdrawn'} and
1648 (not defined $item->{'withdrawn'} or $item->{'withdrawn'} eq '')) {
1649 $item->{'withdrawn'} = 0;
1651 if (exists $item->{location}
1652 and $item->{location} ne 'CART'
1653 and $item->{location} ne 'PROC'
1654 and not $item->{permanent_location}
1656 $item->{'permanent_location'} = $item->{'location'};
1658 if (exists $item->{'timestamp'}) {
1659 delete $item->{'timestamp'};
1663 =head2 _get_single_item_column
1665 _get_single_item_column($column, $itemnumber);
1667 Retrieves the value of a single column from an C<items>
1668 row specified by C<$itemnumber>.
1672 sub _get_single_item_column {
1674 my $itemnumber = shift;
1676 my $dbh = C4::Context->dbh;
1677 my $sth = $dbh->prepare("SELECT $column FROM items WHERE itemnumber = ?");
1678 $sth->execute($itemnumber);
1679 my ($value) = $sth->fetchrow();
1683 =head2 _calc_items_cn_sort
1685 _calc_items_cn_sort($item, $source_values);
1687 Helper routine to calculate C<items.cn_sort>.
1691 sub _calc_items_cn_sort {
1693 my $source_values = shift;
1695 $item->{'items.cn_sort'} = GetClassSort($source_values->{'items.cn_source'}, $source_values->{'itemcallnumber'}, "");
1698 =head2 _set_defaults_for_add
1700 _set_defaults_for_add($item_hash);
1702 Given an item hash representing an item to be added, set
1703 correct default values for columns whose default value
1704 is not handled by the DBMS. This includes the following
1711 C<items.dateaccessioned>
1733 sub _set_defaults_for_add {
1735 $item->{dateaccessioned} ||= output_pref({ dt => dt_from_string, dateformat => 'iso', dateonly => 1 });
1736 $item->{$_} ||= 0 for (qw( notforloan damaged itemlost withdrawn));
1739 =head2 _koha_new_item
1741 my ($itemnumber,$error) = _koha_new_item( $item, $barcode );
1743 Perform the actual insert into the C<items> table.
1747 sub _koha_new_item {
1748 my ( $item, $barcode ) = @_;
1749 my $dbh=C4::Context->dbh;
1751 $item->{permanent_location} //= $item->{location};
1752 _mod_item_dates( $item );
1754 "INSERT INTO items SET
1756 biblioitemnumber = ?,
1758 dateaccessioned = ?,
1762 replacementprice = ?,
1763 replacementpricedate = ?,
1764 datelastborrowed = ?,
1772 coded_location_qualifier = ?,
1775 itemnotes_nonpublic = ?,
1779 permanent_location = ?,
1791 more_subfields_xml = ?,
1796 my $sth = $dbh->prepare($query);
1797 my $today = output_pref({ dt => dt_from_string, dateformat => 'iso', dateonly => 1 });
1799 $item->{'biblionumber'},
1800 $item->{'biblioitemnumber'},
1802 $item->{'dateaccessioned'},
1803 $item->{'booksellerid'},
1804 $item->{'homebranch'},
1806 $item->{'replacementprice'},
1807 $item->{'replacementpricedate'} || $today,
1808 $item->{datelastborrowed},
1809 $item->{datelastseen} || $today,
1811 $item->{'notforloan'},
1813 $item->{'itemlost'},
1814 $item->{'withdrawn'},
1815 $item->{'itemcallnumber'},
1816 $item->{'coded_location_qualifier'},
1817 $item->{'restricted'},
1818 $item->{'itemnotes'},
1819 $item->{'itemnotes_nonpublic'},
1820 $item->{'holdingbranch'},
1822 $item->{'location'},
1823 $item->{'permanent_location'},
1826 $item->{'renewals'},
1827 $item->{'reserves'},
1828 $item->{'items.cn_source'},
1829 $item->{'items.cn_sort'},
1832 $item->{'materials'},
1834 $item->{'enumchron'},
1835 $item->{'more_subfields_xml'},
1836 $item->{'copynumber'},
1837 $item->{'stocknumber'},
1838 $item->{'new_status'},
1842 if ( defined $sth->errstr ) {
1843 $error.="ERROR in _koha_new_item $query".$sth->errstr;
1846 $itemnumber = $dbh->{'mysql_insertid'};
1849 return ( $itemnumber, $error );
1852 =head2 MoveItemFromBiblio
1854 MoveItemFromBiblio($itenumber, $frombiblio, $tobiblio);
1856 Moves an item from a biblio to another
1858 Returns undef if the move failed or the biblionumber of the destination record otherwise
1862 sub MoveItemFromBiblio {
1863 my ($itemnumber, $frombiblio, $tobiblio) = @_;
1864 my $dbh = C4::Context->dbh;
1865 my ( $tobiblioitem ) = $dbh->selectrow_array(q|
1866 SELECT biblioitemnumber
1868 WHERE biblionumber = ?
1869 |, undef, $tobiblio );
1870 my $return = $dbh->do(q|
1872 SET biblioitemnumber = ?,
1874 WHERE itemnumber = ?
1875 AND biblionumber = ?
1876 |, undef, $tobiblioitem, $tobiblio, $itemnumber, $frombiblio );
1878 ModZebra( $tobiblio, "specialUpdate", "biblioserver" );
1879 ModZebra( $frombiblio, "specialUpdate", "biblioserver" );
1880 # Checking if the item we want to move is in an order
1881 require C4::Acquisition;
1882 my $order = C4::Acquisition::GetOrderFromItemnumber($itemnumber);
1884 # Replacing the biblionumber within the order if necessary
1885 $order->{'biblionumber'} = $tobiblio;
1886 C4::Acquisition::ModOrder($order);
1889 # Update reserves, hold_fill_targets, tmp_holdsqueue and linktracker tables
1890 for my $table_name ( qw( reserves hold_fill_targets tmp_holdsqueue linktracker ) ) {
1893 SET biblionumber = ?
1894 WHERE itemnumber = ?
1895 |, undef, $tobiblio, $itemnumber );
1902 =head2 ItemSafeToDelete
1904 ItemSafeToDelete( $biblionumber, $itemnumber);
1906 Exported function (core API) for checking whether an item record is safe to delete.
1908 returns 1 if the item is safe to delete,
1910 "book_on_loan" if the item is checked out,
1912 "not_same_branch" if the item is blocked by independent branches,
1914 "book_reserved" if the there are holds aganst the item, or
1916 "linked_analytics" if the item has linked analytic records.
1920 sub ItemSafeToDelete {
1921 my ( $biblionumber, $itemnumber ) = @_;
1923 my $dbh = C4::Context->dbh;
1927 my $countanalytics = GetAnalyticsCount($itemnumber);
1929 # check that there is no issue on this item before deletion.
1930 my $sth = $dbh->prepare(
1932 SELECT COUNT(*) FROM issues
1933 WHERE itemnumber = ?
1936 $sth->execute($itemnumber);
1937 my ($onloan) = $sth->fetchrow;
1939 my $item = GetItem($itemnumber);
1942 $status = "book_on_loan";
1944 elsif ( defined C4::Context->userenv
1945 and !C4::Context->IsSuperLibrarian()
1946 and C4::Context->preference("IndependentBranches")
1947 and ( C4::Context->userenv->{branch} ne $item->{'homebranch'} ) )
1949 $status = "not_same_branch";
1952 # check it doesn't have a waiting reserve
1953 $sth = $dbh->prepare(
1955 SELECT COUNT(*) FROM reserves
1956 WHERE (found = 'W' OR found = 'T')
1960 $sth->execute($itemnumber);
1961 my ($reserve) = $sth->fetchrow;
1963 $status = "book_reserved";
1965 elsif ( $countanalytics > 0 ) {
1966 $status = "linked_analytics";
1977 DelItemCheck( $biblionumber, $itemnumber);
1979 Exported function (core API) for deleting an item record in Koha if there no current issue.
1981 DelItemCheck wraps ItemSafeToDelete around DelItem.
1986 my ( $biblionumber, $itemnumber ) = @_;
1987 my $status = ItemSafeToDelete( $biblionumber, $itemnumber );
1989 if ( $status == 1 ) {
1992 biblionumber => $biblionumber,
1993 itemnumber => $itemnumber
2000 =head2 _koha_modify_item
2002 my ($itemnumber,$error) =_koha_modify_item( $item );
2004 Perform the actual update of the C<items> row. Note that this
2005 routine accepts a hashref specifying the columns to update.
2009 sub _koha_modify_item {
2011 my $dbh=C4::Context->dbh;
2014 my $query = "UPDATE items SET ";
2016 _mod_item_dates( $item );
2017 for my $key ( keys %$item ) {
2018 next if ( $key eq 'itemnumber' );
2020 push @bind, $item->{$key};
2023 $query .= " WHERE itemnumber=?";
2024 push @bind, $item->{'itemnumber'};
2025 my $sth = $dbh->prepare($query);
2026 $sth->execute(@bind);
2028 $error.="ERROR in _koha_modify_item $query: ".$sth->errstr;
2031 return ($item->{'itemnumber'},$error);
2034 sub _mod_item_dates { # date formatting for date fields in item hash
2036 return if !$item || ref($item) ne 'HASH';
2039 { $_ =~ /^onloan$|^date|date$|datetime$/ }
2041 # Incl. dateaccessioned,replacementpricedate,datelastborrowed,datelastseen
2042 # NOTE: We do not (yet) have items fields ending with datetime
2043 # Fields with _on$ have been handled already
2045 foreach my $key ( @keys ) {
2046 next if !defined $item->{$key}; # skip undefs
2047 my $dt = eval { dt_from_string( $item->{$key} ) };
2048 # eval: dt_from_string will die on us if we pass illegal dates
2051 if( defined $dt && ref($dt) eq 'DateTime' ) {
2052 if( $key =~ /datetime/ ) {
2053 $newstr = DateTime::Format::MySQL->format_datetime($dt);
2055 $newstr = DateTime::Format::MySQL->format_date($dt);
2058 $item->{$key} = $newstr; # might be undef to clear garbage
2062 =head2 _koha_delete_item
2064 _koha_delete_item( $itemnum );
2066 Internal function to delete an item record from the koha tables
2070 sub _koha_delete_item {
2071 my ( $itemnum ) = @_;
2073 my $dbh = C4::Context->dbh;
2074 # save the deleted item to deleteditems table
2075 my $sth = $dbh->prepare("SELECT * FROM items WHERE itemnumber=?");
2076 $sth->execute($itemnum);
2077 my $data = $sth->fetchrow_hashref();
2079 # There is no item to delete
2080 return 0 unless $data;
2082 my $query = "INSERT INTO deleteditems SET ";
2084 foreach my $key ( keys %$data ) {
2085 next if ( $key eq 'timestamp' ); # timestamp will be set by db
2086 $query .= "$key = ?,";
2087 push( @bind, $data->{$key} );
2090 $sth = $dbh->prepare($query);
2091 $sth->execute(@bind);
2093 # delete from items table
2094 $sth = $dbh->prepare("DELETE FROM items WHERE itemnumber=?");
2095 my $deleted = $sth->execute($itemnum);
2096 return ( $deleted == 1 ) ? 1 : 0;
2099 =head2 _marc_from_item_hash
2101 my $item_marc = _marc_from_item_hash($item, $frameworkcode[, $unlinked_item_subfields]);
2103 Given an item hash representing a complete item record,
2104 create a C<MARC::Record> object containing an embedded
2105 tag representing that item.
2107 The third, optional parameter C<$unlinked_item_subfields> is
2108 an arrayref of subfields (not mapped to C<items> fields per the
2109 framework) to be added to the MARC representation
2114 sub _marc_from_item_hash {
2116 my $frameworkcode = shift;
2117 my $unlinked_item_subfields;
2119 $unlinked_item_subfields = shift;
2122 # Tack on 'items.' prefix to column names so lookup from MARC frameworks will work
2123 # Also, don't emit a subfield if the underlying field is blank.
2124 my $mungeditem = { map { (defined($item->{$_}) and $item->{$_} ne '') ?
2125 (/^items\./ ? ($_ => $item->{$_}) : ("items.$_" => $item->{$_}))
2126 : () } keys %{ $item } };
2128 my $item_marc = MARC::Record->new();
2129 foreach my $item_field ( keys %{$mungeditem} ) {
2130 my ( $tag, $subfield ) = C4::Biblio::GetMarcFromKohaField( $item_field, $frameworkcode );
2131 next unless defined $tag and defined $subfield; # skip if not mapped to MARC field
2132 my @values = split(/\s?\|\s?/, $mungeditem->{$item_field}, -1);
2133 foreach my $value (@values){
2134 if ( my $field = $item_marc->field($tag) ) {
2135 $field->add_subfields( $subfield => $value );
2137 my $add_subfields = [];
2138 if (defined $unlinked_item_subfields and ref($unlinked_item_subfields) eq 'ARRAY' and $#$unlinked_item_subfields > -1) {
2139 $add_subfields = $unlinked_item_subfields;
2141 $item_marc->add_fields( $tag, " ", " ", $subfield => $value, @$add_subfields );
2149 =head2 _repack_item_errors
2151 Add an error message hash generated by C<CheckItemPreSave>
2152 to a list of errors.
2156 sub _repack_item_errors {
2157 my $item_sequence_num = shift;
2158 my $item_ref = shift;
2159 my $error_ref = shift;
2161 my @repacked_errors = ();
2163 foreach my $error_code (sort keys %{ $error_ref }) {
2164 my $repacked_error = {};
2165 $repacked_error->{'item_sequence'} = $item_sequence_num;
2166 $repacked_error->{'item_barcode'} = exists($item_ref->{'barcode'}) ? $item_ref->{'barcode'} : '';
2167 $repacked_error->{'error_code'} = $error_code;
2168 $repacked_error->{'error_information'} = $error_ref->{$error_code};
2169 push @repacked_errors, $repacked_error;
2172 return @repacked_errors;
2175 =head2 _get_unlinked_item_subfields
2177 my $unlinked_item_subfields = _get_unlinked_item_subfields($original_item_marc, $frameworkcode);
2181 sub _get_unlinked_item_subfields {
2182 my $original_item_marc = shift;
2183 my $frameworkcode = shift;
2185 my $marcstructure = GetMarcStructure(1, $frameworkcode, { unsafe => 1 });
2187 # assume that this record has only one field, and that that
2188 # field contains only the item information
2190 my @fields = $original_item_marc->fields();
2191 if ($#fields > -1) {
2192 my $field = $fields[0];
2193 my $tag = $field->tag();
2194 foreach my $subfield ($field->subfields()) {
2195 if (defined $subfield->[1] and
2196 $subfield->[1] ne '' and
2197 !$marcstructure->{$tag}->{$subfield->[0]}->{'kohafield'}) {
2198 push @$subfields, $subfield->[0] => $subfield->[1];
2205 =head2 _get_unlinked_subfields_xml
2207 my $unlinked_subfields_xml = _get_unlinked_subfields_xml($unlinked_item_subfields);
2211 sub _get_unlinked_subfields_xml {
2212 my $unlinked_item_subfields = shift;
2215 if (defined $unlinked_item_subfields and ref($unlinked_item_subfields) eq 'ARRAY' and $#$unlinked_item_subfields > -1) {
2216 my $marc = MARC::Record->new();
2217 # use of tag 999 is arbitrary, and doesn't need to match the item tag
2218 # used in the framework
2219 $marc->append_fields(MARC::Field->new('999', ' ', ' ', @$unlinked_item_subfields));
2220 $marc->encoding("UTF-8");
2221 $xml = $marc->as_xml("USMARC");
2227 =head2 _parse_unlinked_item_subfields_from_xml
2229 my $unlinked_item_subfields = _parse_unlinked_item_subfields_from_xml($whole_item->{'more_subfields_xml'}):
2233 sub _parse_unlinked_item_subfields_from_xml {
2235 require C4::Charset;
2236 return unless defined $xml and $xml ne "";
2237 my $marc = MARC::Record->new_from_xml(C4::Charset::StripNonXmlChars($xml),'UTF-8');
2238 my $unlinked_subfields = [];
2239 my @fields = $marc->fields();
2240 if ($#fields > -1) {
2241 foreach my $subfield ($fields[0]->subfields()) {
2242 push @$unlinked_subfields, $subfield->[0] => $subfield->[1];
2245 return $unlinked_subfields;
2248 =head2 GetAnalyticsCount
2250 $count= &GetAnalyticsCount($itemnumber)
2252 counts Usage of itemnumber in Analytical bibliorecords.
2256 sub GetAnalyticsCount {
2257 my ($itemnumber) = @_;
2259 ### ZOOM search here
2261 $query= "hi=".$itemnumber;
2262 my $searcher = Koha::SearchEngine::Search->new({index => $Koha::SearchEngine::BIBLIOS_INDEX});
2263 my ($err,$res,$result) = $searcher->simple_search_compat($query,0,10);
2267 =head2 SearchItemsByField
2269 my $items = SearchItemsByField($field, $value);
2271 SearchItemsByField will search for items on a specific given field.
2272 For instance you can search all items with a specific stocknumber like this:
2274 my $items = SearchItemsByField('stocknumber', $stocknumber);
2278 sub SearchItemsByField {
2279 my ($field, $value) = @_;
2286 my ($results) = SearchItems($filters);
2290 sub _SearchItems_build_where_fragment {
2293 my $dbh = C4::Context->dbh;
2296 if (exists($filter->{conjunction})) {
2297 my (@where_strs, @where_args);
2298 foreach my $f (@{ $filter->{filters} }) {
2299 my $fragment = _SearchItems_build_where_fragment($f);
2301 push @where_strs, $fragment->{str};
2302 push @where_args, @{ $fragment->{args} };
2307 $where_str = '(' . join (' ' . $filter->{conjunction} . ' ', @where_strs) . ')';
2310 args => \@where_args,
2314 my @columns = Koha::Database->new()->schema()->resultset('Item')->result_source->columns;
2315 push @columns, Koha::Database->new()->schema()->resultset('Biblio')->result_source->columns;
2316 push @columns, Koha::Database->new()->schema()->resultset('Biblioitem')->result_source->columns;
2317 my @operators = qw(= != > < >= <= like);
2318 my $field = $filter->{field};
2319 if ( (0 < grep /^$field$/, @columns) or (substr($field, 0, 5) eq 'marc:') ) {
2320 my $op = $filter->{operator};
2321 my $query = $filter->{query};
2323 if (!$op or (0 == grep /^$op$/, @operators)) {
2324 $op = '='; # default operator
2328 if ($field =~ /^marc:(\d{3})(?:\$(\w))?$/) {
2330 my $marcsubfield = $2;
2331 my ($kohafield) = $dbh->selectrow_array(q|
2332 SELECT kohafield FROM marc_subfield_structure
2333 WHERE tagfield=? AND tagsubfield=? AND frameworkcode=''
2334 |, undef, $marcfield, $marcsubfield);
2337 $column = $kohafield;
2339 # MARC field is not linked to a DB field so we need to use
2340 # ExtractValue on marcxml from biblio_metadata or
2341 # items.more_subfields_xml, depending on the MARC field.
2344 my ($itemfield) = C4::Biblio::GetMarcFromKohaField('items.itemnumber');
2345 if ($marcfield eq $itemfield) {
2346 $sqlfield = 'more_subfields_xml';
2347 $xpath = '//record/datafield/subfield[@code="' . $marcsubfield . '"]';
2349 $sqlfield = 'metadata'; # From biblio_metadata
2350 if ($marcfield < 10) {
2351 $xpath = "//record/controlfield[\@tag=\"$marcfield\"]";
2353 $xpath = "//record/datafield[\@tag=\"$marcfield\"]/subfield[\@code=\"$marcsubfield\"]";
2356 $column = "ExtractValue($sqlfield, '$xpath')";
2362 if (ref $query eq 'ARRAY') {
2365 } elsif ($op eq '!=') {
2369 str => "$column $op (" . join (',', ('?') x @$query) . ")",
2374 str => "$column $op ?",
2381 return $where_fragment;
2386 my ($items, $total) = SearchItems($filter, $params);
2388 Perform a search among items
2390 $filter is a reference to a hash which can be a filter, or a combination of filters.
2392 A filter has the following keys:
2396 =item * field: the name of a SQL column in table items
2398 =item * query: the value to search in this column
2400 =item * operator: comparison operator. Can be one of = != > < >= <= like
2404 A combination of filters hash the following keys:
2408 =item * conjunction: 'AND' or 'OR'
2410 =item * filters: array ref of filters
2414 $params is a reference to a hash that can contain the following parameters:
2418 =item * rows: Number of items to return. 0 returns everything (default: 0)
2420 =item * page: Page to return (return items from (page-1)*rows to (page*rows)-1)
2423 =item * sortby: A SQL column name in items table to sort on
2425 =item * sortorder: 'ASC' or 'DESC'
2432 my ($filter, $params) = @_;
2436 return unless ref $filter eq 'HASH';
2437 return unless ref $params eq 'HASH';
2439 # Default parameters
2440 $params->{rows} ||= 0;
2441 $params->{page} ||= 1;
2442 $params->{sortby} ||= 'itemnumber';
2443 $params->{sortorder} ||= 'ASC';
2445 my ($where_str, @where_args);
2446 my $where_fragment = _SearchItems_build_where_fragment($filter);
2447 if ($where_fragment) {
2448 $where_str = $where_fragment->{str};
2449 @where_args = @{ $where_fragment->{args} };
2452 my $dbh = C4::Context->dbh;
2454 SELECT SQL_CALC_FOUND_ROWS items.*
2456 LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber
2457 LEFT JOIN biblioitems ON biblioitems.biblioitemnumber = items.biblioitemnumber
2458 LEFT JOIN biblio_metadata ON biblio_metadata.biblionumber = biblio.biblionumber
2461 if (defined $where_str and $where_str ne '') {
2462 $query .= qq{ AND $where_str };
2465 $query .= q{ AND biblio_metadata.format = 'marcxml' AND biblio_metadata.marcflavour = ? };
2466 push @where_args, C4::Context->preference('marcflavour');
2468 my @columns = Koha::Database->new()->schema()->resultset('Item')->result_source->columns;
2469 push @columns, Koha::Database->new()->schema()->resultset('Biblio')->result_source->columns;
2470 push @columns, Koha::Database->new()->schema()->resultset('Biblioitem')->result_source->columns;
2471 my $sortby = (0 < grep {$params->{sortby} eq $_} @columns)
2472 ? $params->{sortby} : 'itemnumber';
2473 my $sortorder = (uc($params->{sortorder}) eq 'ASC') ? 'ASC' : 'DESC';
2474 $query .= qq{ ORDER BY $sortby $sortorder };
2476 my $rows = $params->{rows};
2479 my $offset = $rows * ($params->{page}-1);
2480 $query .= qq { LIMIT ?, ? };
2481 push @limit_args, $offset, $rows;
2484 my $sth = $dbh->prepare($query);
2485 my $rv = $sth->execute(@where_args, @limit_args);
2487 return unless ($rv);
2488 my ($total_rows) = $dbh->selectrow_array(q{ SELECT FOUND_ROWS() });
2490 return ($sth->fetchall_arrayref({}), $total_rows);
2494 =head1 OTHER FUNCTIONS
2498 ($indicators, $value) = _find_value($tag, $subfield, $record,$encoding);
2500 Find the given $subfield in the given $tag in the given
2501 MARC::Record $record. If the subfield is found, returns
2502 the (indicators, value) pair; otherwise, (undef, undef) is
2506 Such a function is used in addbiblio AND additem and serial-edit and maybe could be used in Authorities.
2507 I suggest we export it from this module.
2512 my ( $tagfield, $insubfield, $record, $encoding ) = @_;
2515 if ( $tagfield < 10 ) {
2516 if ( $record->field($tagfield) ) {
2517 push @result, $record->field($tagfield)->data();
2522 foreach my $field ( $record->field($tagfield) ) {
2523 my @subfields = $field->subfields();
2524 foreach my $subfield (@subfields) {
2525 if ( @$subfield[0] eq $insubfield ) {
2526 push @result, @$subfield[1];
2527 $indicator = $field->indicator(1) . $field->indicator(2);
2532 return ( $indicator, @result );
2536 =head2 PrepareItemrecordDisplay
2538 PrepareItemrecordDisplay($itemrecord,$bibnum,$itemumber,$frameworkcode);
2540 Returns a hash with all the fields for Display a given item data in a template
2542 The $frameworkcode returns the item for the given frameworkcode, ONLY if bibnum is not provided
2546 sub PrepareItemrecordDisplay {
2548 my ( $bibnum, $itemnum, $defaultvalues, $frameworkcode ) = @_;
2550 my $dbh = C4::Context->dbh;
2551 $frameworkcode = C4::Biblio::GetFrameworkCode($bibnum) if $bibnum;
2552 my ( $itemtagfield, $itemtagsubfield ) = C4::Biblio::GetMarcFromKohaField( "items.itemnumber", $frameworkcode );
2554 # Note: $tagslib obtained from GetMarcStructure() in 'unsafe' mode is
2555 # a shared data structure. No plugin (including custom ones) should change
2556 # its contents. See also GetMarcStructure.
2557 my $tagslib = &GetMarcStructure( 1, $frameworkcode, { unsafe => 1 } );
2559 # return nothing if we don't have found an existing framework.
2560 return q{} unless $tagslib;
2563 $itemrecord = C4::Items::GetMarcItem( $bibnum, $itemnum );
2567 my $branch_limit = C4::Context->userenv ? C4::Context->userenv->{"branch"} : "";
2569 SELECT authorised_value,lib FROM authorised_values
2572 LEFT JOIN authorised_values_branches ON ( id = av_id )
2577 $query .= qq{ AND ( branchcode = ? OR branchcode IS NULL )} if $branch_limit;
2578 $query .= qq{ ORDER BY lib};
2579 my $authorised_values_sth = $dbh->prepare( $query );
2580 foreach my $tag ( sort keys %{$tagslib} ) {
2583 # loop through each subfield
2585 foreach my $subfield ( sort keys %{ $tagslib->{$tag} } ) {
2586 next if IsMarcStructureInternal($tagslib->{$tag}{$subfield});
2587 next unless ( $tagslib->{$tag}->{$subfield}->{'tab'} );
2588 next if ( $tagslib->{$tag}->{$subfield}->{'tab'} ne "10" );
2590 $subfield_data{tag} = $tag;
2591 $subfield_data{subfield} = $subfield;
2592 $subfield_data{countsubfield} = $cntsubf++;
2593 $subfield_data{kohafield} = $tagslib->{$tag}->{$subfield}->{'kohafield'};
2594 $subfield_data{id} = "tag_".$tag."_subfield_".$subfield."_".int(rand(1000000));
2596 # $subfield_data{marc_lib}=$tagslib->{$tag}->{$subfield}->{lib};
2597 $subfield_data{marc_lib} = $tagslib->{$tag}->{$subfield}->{lib};
2598 $subfield_data{mandatory} = $tagslib->{$tag}->{$subfield}->{mandatory};
2599 $subfield_data{repeatable} = $tagslib->{$tag}->{$subfield}->{repeatable};
2600 $subfield_data{hidden} = "display:none"
2601 if ( ( $tagslib->{$tag}->{$subfield}->{hidden} > 4 )
2602 || ( $tagslib->{$tag}->{$subfield}->{hidden} < -4 ) );
2603 my ( $x, $defaultvalue );
2605 ( $x, $defaultvalue ) = _find_value( $tag, $subfield, $itemrecord );
2607 $defaultvalue = $tagslib->{$tag}->{$subfield}->{defaultvalue} unless $defaultvalue;
2608 if ( !defined $defaultvalue ) {
2609 $defaultvalue = q||;
2611 $defaultvalue =~ s/"/"/g;
2614 # search for itemcallnumber if applicable
2615 if ( $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.itemcallnumber'
2616 && C4::Context->preference('itemcallnumber') ) {
2617 my $CNtag = substr( C4::Context->preference('itemcallnumber'), 0, 3 );
2618 my $CNsubfield = substr( C4::Context->preference('itemcallnumber'), 3, 1 );
2619 if ( $itemrecord and my $field = $itemrecord->field($CNtag) ) {
2620 $defaultvalue = $field->subfield($CNsubfield);
2623 if ( $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.itemcallnumber'
2625 && $defaultvalues->{'callnumber'} ) {
2626 if( $itemrecord and $defaultvalues and not $itemrecord->subfield($tag,$subfield) ){
2627 # if the item record exists, only use default value if the item has no callnumber
2628 $defaultvalue = $defaultvalues->{callnumber};
2629 } elsif ( !$itemrecord and $defaultvalues ) {
2630 # if the item record *doesn't* exists, always use the default value
2631 $defaultvalue = $defaultvalues->{callnumber};
2634 if ( ( $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.holdingbranch' || $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.homebranch' )
2636 && $defaultvalues->{'branchcode'} ) {
2637 if ( $itemrecord and $defaultvalues and not $itemrecord->subfield($tag,$subfield) ) {
2638 $defaultvalue = $defaultvalues->{branchcode};
2641 if ( ( $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.location' )
2643 && $defaultvalues->{'location'} ) {
2645 if ( $itemrecord and $defaultvalues and not $itemrecord->subfield($tag,$subfield) ) {
2646 # if the item record exists, only use default value if the item has no locationr
2647 $defaultvalue = $defaultvalues->{location};
2648 } elsif ( !$itemrecord and $defaultvalues ) {
2649 # if the item record *doesn't* exists, always use the default value
2650 $defaultvalue = $defaultvalues->{location};
2653 if ( $tagslib->{$tag}->{$subfield}->{authorised_value} ) {
2654 my @authorised_values;
2657 # builds list, depending on authorised value...
2659 if ( $tagslib->{$tag}->{$subfield}->{'authorised_value'} eq "branches" ) {
2660 if ( ( C4::Context->preference("IndependentBranches") )
2661 && !C4::Context->IsSuperLibrarian() ) {
2662 my $sth = $dbh->prepare( "SELECT branchcode,branchname FROM branches WHERE branchcode = ? ORDER BY branchname" );
2663 $sth->execute( C4::Context->userenv->{branch} );
2664 push @authorised_values, ""
2665 unless ( $tagslib->{$tag}->{$subfield}->{mandatory} );
2666 while ( my ( $branchcode, $branchname ) = $sth->fetchrow_array ) {
2667 push @authorised_values, $branchcode;
2668 $authorised_lib{$branchcode} = $branchname;
2671 my $sth = $dbh->prepare( "SELECT branchcode,branchname FROM branches ORDER BY branchname" );
2673 push @authorised_values, ""
2674 unless ( $tagslib->{$tag}->{$subfield}->{mandatory} );
2675 while ( my ( $branchcode, $branchname ) = $sth->fetchrow_array ) {
2676 push @authorised_values, $branchcode;
2677 $authorised_lib{$branchcode} = $branchname;
2681 $defaultvalue = C4::Context->userenv ? C4::Context->userenv->{branch} : undef;
2682 if ( $defaultvalues and $defaultvalues->{branchcode} ) {
2683 $defaultvalue = $defaultvalues->{branchcode};
2687 } elsif ( $tagslib->{$tag}->{$subfield}->{authorised_value} eq "itemtypes" ) {
2688 my $itemtypes = Koha::ItemTypes->search_with_localization;
2689 push @authorised_values, ""
2690 unless ( $tagslib->{$tag}->{$subfield}->{mandatory} );
2691 while ( my $itemtype = $itemtypes->next ) {
2692 push @authorised_values, $itemtype->itemtype;
2693 $authorised_lib{$itemtype->itemtype} = $itemtype->translated_description;
2695 if ($defaultvalues && $defaultvalues->{'itemtype'}) {
2696 $defaultvalue = $defaultvalues->{'itemtype'};
2700 } elsif ( $tagslib->{$tag}->{$subfield}->{authorised_value} eq "cn_source" ) {
2701 push @authorised_values, "" unless ( $tagslib->{$tag}->{$subfield}->{mandatory} );
2703 my $class_sources = GetClassSources();
2704 my $default_source = C4::Context->preference("DefaultClassificationSource");
2706 foreach my $class_source (sort keys %$class_sources) {
2707 next unless $class_sources->{$class_source}->{'used'} or
2708 ($class_source eq $default_source);
2709 push @authorised_values, $class_source;
2710 $authorised_lib{$class_source} = $class_sources->{$class_source}->{'description'};
2713 $defaultvalue = $default_source;
2715 #---- "true" authorised value
2717 $authorised_values_sth->execute(
2718 $tagslib->{$tag}->{$subfield}->{authorised_value},
2719 $branch_limit ? $branch_limit : ()
2721 push @authorised_values, ""
2722 unless ( $tagslib->{$tag}->{$subfield}->{mandatory} );
2723 while ( my ( $value, $lib ) = $authorised_values_sth->fetchrow_array ) {
2724 push @authorised_values, $value;
2725 $authorised_lib{$value} = $lib;
2728 $subfield_data{marc_value} = {
2730 values => \@authorised_values,
2731 default => "$defaultvalue",
2732 labels => \%authorised_lib,
2734 } elsif ( $tagslib->{$tag}->{$subfield}->{value_builder} ) {
2736 require Koha::FrameworkPlugin;
2737 my $plugin = Koha::FrameworkPlugin->new({
2738 name => $tagslib->{$tag}->{$subfield}->{value_builder},
2741 my $pars = { dbh => $dbh, record => undef, tagslib =>$tagslib, id => $subfield_data{id}, tabloop => undef };
2742 $plugin->build( $pars );
2743 if ( $itemrecord and my $field = $itemrecord->field($tag) ) {
2744 $defaultvalue = $field->subfield($subfield);
2746 if( !$plugin->errstr ) {
2747 #TODO Move html to template; see report 12176/13397
2748 my $tab= $plugin->noclick? '-1': '';
2749 my $class= $plugin->noclick? ' disabled': '';
2750 my $title= $plugin->noclick? 'No popup': 'Tag editor';
2751 $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;
2753 warn $plugin->errstr;
2754 $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
2757 elsif ( $tag eq '' ) { # it's an hidden field
2758 $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" />);
2760 elsif ( $tagslib->{$tag}->{$subfield}->{'hidden'} ) { # FIXME: shouldn't input type be "hidden" ?
2761 $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" />);
2763 elsif ( length($defaultvalue) > 100
2764 or (C4::Context->preference("marcflavour") eq "UNIMARC" and
2765 300 <= $tag && $tag < 400 && $subfield eq 'a' )
2766 or (C4::Context->preference("marcflavour") eq "MARC21" and
2767 500 <= $tag && $tag < 600 )
2769 # oversize field (textarea)
2770 $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");
2772 $subfield_data{marc_value} = "<input type=\"text\" name=\"field_value\" value=\"$defaultvalue\" size=\"50\" maxlength=\"255\" />";
2774 push( @loop_data, \%subfield_data );
2779 if ( $itemrecord && $itemrecord->field($itemtagfield) ) {
2780 $itemnumber = $itemrecord->subfield( $itemtagfield, $itemtagsubfield );
2783 'itemtagfield' => $itemtagfield,
2784 'itemtagsubfield' => $itemtagsubfield,
2785 'itemnumber' => $itemnumber,
2786 'iteminformation' => \@loop_data
2790 sub ToggleNewStatus {
2791 my ( $params ) = @_;
2792 my @rules = @{ $params->{rules} };
2793 my $report_only = $params->{report_only};
2795 my $dbh = C4::Context->dbh;
2797 my @item_columns = map { "items.$_" } Koha::Items->columns;
2798 my @biblioitem_columns = map { "biblioitems.$_" } Koha::Biblioitems->columns;
2800 for my $rule ( @rules ) {
2801 my $age = $rule->{age};
2802 my $conditions = $rule->{conditions};
2803 my $substitutions = $rule->{substitutions};
2807 SELECT items.biblionumber, items.itemnumber
2809 LEFT JOIN biblioitems ON biblioitems.biblionumber = items.biblionumber
2812 for my $condition ( @$conditions ) {
2814 grep {/^$condition->{field}$/} @item_columns
2815 or grep {/^$condition->{field}$/} @biblioitem_columns
2817 if ( $condition->{value} =~ /\|/ ) {
2818 my @values = split /\|/, $condition->{value};
2819 $query .= qq| AND $condition->{field} IN (|
2820 . join( ',', ('?') x scalar @values )
2822 push @params, @values;
2824 $query .= qq| AND $condition->{field} = ?|;
2825 push @params, $condition->{value};
2829 if ( defined $age ) {
2830 $query .= q| AND TO_DAYS(NOW()) - TO_DAYS(dateaccessioned) >= ? |;
2833 my $sth = $dbh->prepare($query);
2834 $sth->execute( @params );
2835 while ( my $values = $sth->fetchrow_hashref ) {
2836 my $biblionumber = $values->{biblionumber};
2837 my $itemnumber = $values->{itemnumber};
2838 my $item = C4::Items::GetItem( $itemnumber );
2839 for my $substitution ( @$substitutions ) {
2840 next unless $substitution->{field};
2841 C4::Items::ModItem( {$substitution->{field} => $substitution->{value}}, $biblionumber, $itemnumber )
2842 unless $report_only;
2843 push @{ $report->{$itemnumber} }, $substitution;