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.
521 The first argument is a hashref mapping from item column
522 names to the new values. The second and third arguments
523 are the biblionumber and itemnumber, respectively.
524 The fourth, optional parameter (additional_params) may contain the keys
525 unlinked_item_subfields and log_action.
527 C<$unlinked_item_subfields> contains an arrayref containing
528 subfields present in the original MARC
529 representation of the item (e.g., from the item editor) that are
530 not mapped to C<items> columns directly but should instead
531 be stored in C<items.more_subfields_xml> and included in
532 the biblio items tag for display and indexing.
534 If one of the changed columns is used to calculate
535 the derived value of a column such as C<items.cn_sort>,
536 this routine will perform the necessary calculation
539 If log_action is set to false, the action will not be logged.
540 If log_action is true or undefined, the action will be logged.
545 my ( $item, $biblionumber, $itemnumber, $additional_params ) = @_;
546 my $log_action = $additional_params->{log_action} // 1;
547 my $unlinked_item_subfields = $additional_params->{unlinked_item_subfields};
549 return unless %$item;
550 $item->{'itemnumber'} = $itemnumber or return;
552 # if $biblionumber is undefined, get it from the current item
553 unless (defined $biblionumber) {
554 $biblionumber = _get_single_item_column('biblionumber', $itemnumber);
557 if ($unlinked_item_subfields) {
558 $item->{'more_subfields_xml'} = _get_unlinked_subfields_xml($unlinked_item_subfields);
561 my @fields = qw( itemlost withdrawn damaged );
563 # Only call GetItem if we need to set an "on" date field
564 if ( $item->{itemlost} || $item->{withdrawn} || $item->{damaged} ) {
565 my $pre_mod_item = GetItem( $item->{'itemnumber'} );
566 for my $field (@fields) {
567 if ( defined( $item->{$field} )
568 and not $pre_mod_item->{$field}
569 and $item->{$field} )
571 $item->{ $field . '_on' } =
572 DateTime::Format::MySQL->format_datetime( dt_from_string() );
577 # If the field is defined but empty, we are removing and,
578 # and thus need to clear out the 'on' field as well
579 for my $field (@fields) {
580 if ( defined( $item->{$field} ) && !$item->{$field} ) {
581 $item->{ $field . '_on' } = undef;
586 _set_derived_columns_for_mod($item);
587 _do_column_fixes_for_mod($item);
590 # attempt to change itemnumber
591 # attempt to change biblionumber (if we want
592 # an API to relink an item to a different bib,
593 # it should be a separate function)
596 _koha_modify_item($item);
598 # request that bib be reindexed so that searching on current
599 # item status is possible
600 ModZebra( $biblionumber, "specialUpdate", "biblioserver" );
602 logaction( "CATALOGUING", "MODIFY", $itemnumber, "item " . Dumper($item) )
603 if $log_action && C4::Context->preference("CataloguingLog");
606 =head2 ModItemTransfer
608 ModItemTransfer($itenumber, $frombranch, $tobranch);
610 Marks an item as being transferred from one branch
615 sub ModItemTransfer {
616 my ( $itemnumber, $frombranch, $tobranch ) = @_;
618 my $dbh = C4::Context->dbh;
620 # Remove the 'shelving cart' location status if it is being used.
621 CartToShelf( $itemnumber ) if ( C4::Context->preference("ReturnToShelvingCart") );
623 $dbh->do("UPDATE branchtransfers SET datearrived = NOW(), comments = ? WHERE itemnumber = ? AND datearrived IS NULL", undef, "Canceled, new transfer from $frombranch to $tobranch created", $itemnumber);
625 #new entry in branchtransfers....
626 my $sth = $dbh->prepare(
627 "INSERT INTO branchtransfers (itemnumber, frombranch, datesent, tobranch)
628 VALUES (?, ?, NOW(), ?)");
629 $sth->execute($itemnumber, $frombranch, $tobranch);
631 ModItem({ holdingbranch => $tobranch }, undef, $itemnumber, { log_action => 0 });
632 ModDateLastSeen($itemnumber);
636 =head2 ModDateLastSeen
638 ModDateLastSeen( $itemnumber, $leave_item_lost );
640 Mark item as seen. Is called when an item is issued, returned or manually marked during inventory/stocktaking.
641 C<$itemnumber> is the item number
642 C<$leave_item_lost> determines if a lost item will be found or remain lost
646 sub ModDateLastSeen {
647 my ( $itemnumber, $leave_item_lost ) = @_;
649 my $today = output_pref({ dt => dt_from_string, dateformat => 'iso', dateonly => 1 });
652 $params->{datelastseen} = $today;
653 $params->{itemlost} = 0 unless $leave_item_lost;
655 ModItem( $params, undef, $itemnumber, { log_action => 0 } );
660 DelItem({ itemnumber => $itemnumber, [ biblionumber => $biblionumber ] } );
662 Exported function (core API) for deleting an item record in Koha.
669 my $itemnumber = $params->{itemnumber};
670 my $biblionumber = $params->{biblionumber};
672 unless ($biblionumber) {
673 my $item = Koha::Items->find( $itemnumber );
674 $biblionumber = $item ? $item->biblio->biblionumber : undef;
677 # If there is no biblionumber for the given itemnumber, there is nothing to delete
678 return 0 unless $biblionumber;
680 # FIXME check the item has no current issues
681 my $deleted = _koha_delete_item( $itemnumber );
683 ModZebra( $biblionumber, "specialUpdate", "biblioserver" );
685 #search item field code
686 logaction("CATALOGUING", "DELETE", $itemnumber, "item") if C4::Context->preference("CataloguingLog");
690 =head2 CheckItemPreSave
692 my $item_ref = TransformMarcToKoha($marc, 'items');
694 my %errors = CheckItemPreSave($item_ref);
695 if (exists $errors{'duplicate_barcode'}) {
696 print "item has duplicate barcode: ", $errors{'duplicate_barcode'}, "\n";
697 } elsif (exists $errors{'invalid_homebranch'}) {
698 print "item has invalid home branch: ", $errors{'invalid_homebranch'}, "\n";
699 } elsif (exists $errors{'invalid_holdingbranch'}) {
700 print "item has invalid holding branch: ", $errors{'invalid_holdingbranch'}, "\n";
705 Given a hashref containing item fields, determine if it can be
706 inserted or updated in the database. Specifically, checks for
707 database integrity issues, and returns a hash containing any
708 of the following keys, if applicable.
712 =item duplicate_barcode
714 Barcode, if it duplicates one already found in the database.
716 =item invalid_homebranch
718 Home branch, if not defined in branches table.
720 =item invalid_holdingbranch
722 Holding branch, if not defined in branches table.
726 This function does NOT implement any policy-related checks,
727 e.g., whether current operator is allowed to save an
728 item that has a given branch code.
732 sub CheckItemPreSave {
733 my $item_ref = shift;
737 # check for duplicate barcode
738 if (exists $item_ref->{'barcode'} and defined $item_ref->{'barcode'}) {
739 my $existing_itemnumber = GetItemnumberFromBarcode($item_ref->{'barcode'});
740 if ($existing_itemnumber) {
741 if (!exists $item_ref->{'itemnumber'} # new item
742 or $item_ref->{'itemnumber'} != $existing_itemnumber) { # existing item
743 $errors{'duplicate_barcode'} = $item_ref->{'barcode'};
748 # check for valid home branch
749 if (exists $item_ref->{'homebranch'} and defined $item_ref->{'homebranch'}) {
750 my $home_library = Koha::Libraries->find( $item_ref->{homebranch} );
751 unless (defined $home_library) {
752 $errors{'invalid_homebranch'} = $item_ref->{'homebranch'};
756 # check for valid holding branch
757 if (exists $item_ref->{'holdingbranch'} and defined $item_ref->{'holdingbranch'}) {
758 my $holding_library = Koha::Libraries->find( $item_ref->{holdingbranch} );
759 unless (defined $holding_library) {
760 $errors{'invalid_holdingbranch'} = $item_ref->{'holdingbranch'};
768 =head1 EXPORTED SPECIAL ACCESSOR FUNCTIONS
770 The following functions provide various ways of
771 getting an item record, a set of item records, or
772 lists of authorized values for certain item fields.
774 Some of the functions in this group are candidates
775 for refactoring -- for example, some of the code
776 in C<GetItemsByBiblioitemnumber> and C<GetItemsInfo>
777 has copy-and-paste work.
781 =head2 GetItemsForInventory
783 ($itemlist, $iTotalRecords) = GetItemsForInventory( {
784 minlocation => $minlocation,
785 maxlocation => $maxlocation,
786 location => $location,
787 itemtype => $itemtype,
788 ignoreissued => $ignoreissued,
789 datelastseen => $datelastseen,
790 branchcode => $branchcode,
794 statushash => $statushash,
797 Retrieve a list of title/authors/barcode/callnumber, for biblio inventory.
799 The sub returns a reference to a list of hashes, each containing
800 itemnumber, author, title, barcode, item callnumber, and date last
801 seen. It is ordered by callnumber then title.
803 The required minlocation & maxlocation parameters are used to specify a range of item callnumbers
804 the datelastseen can be used to specify that you want to see items not seen since a past date only.
805 offset & size can be used to retrieve only a part of the whole listing (defaut behaviour)
806 $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.
808 $iTotalRecords is the number of rows that would have been returned without the $offset, $size limit clause
812 sub GetItemsForInventory {
813 my ( $parameters ) = @_;
814 my $minlocation = $parameters->{'minlocation'} // '';
815 my $maxlocation = $parameters->{'maxlocation'} // '';
816 my $location = $parameters->{'location'} // '';
817 my $itemtype = $parameters->{'itemtype'} // '';
818 my $ignoreissued = $parameters->{'ignoreissued'} // '';
819 my $datelastseen = $parameters->{'datelastseen'} // '';
820 my $branchcode = $parameters->{'branchcode'} // '';
821 my $branch = $parameters->{'branch'} // '';
822 my $offset = $parameters->{'offset'} // '';
823 my $size = $parameters->{'size'} // '';
824 my $statushash = $parameters->{'statushash'} // '';
826 my $dbh = C4::Context->dbh;
827 my ( @bind_params, @where_strings );
829 my $select_columns = q{
830 SELECT items.itemnumber, barcode, itemcallnumber, title, author, biblio.biblionumber, biblio.frameworkcode, datelastseen, homebranch, location, notforloan, damaged, itemlost, withdrawn, stocknumber
832 my $select_count = q{SELECT COUNT(*)};
835 LEFT JOIN biblio ON items.biblionumber = biblio.biblionumber
836 LEFT JOIN biblioitems on items.biblionumber = biblioitems.biblionumber
839 for my $authvfield (keys %$statushash){
840 if ( scalar @{$statushash->{$authvfield}} > 0 ){
841 my $joinedvals = join ',', @{$statushash->{$authvfield}};
842 push @where_strings, "$authvfield in (" . $joinedvals . ")";
848 push @where_strings, 'itemcallnumber >= ?';
849 push @bind_params, $minlocation;
853 push @where_strings, 'itemcallnumber <= ?';
854 push @bind_params, $maxlocation;
858 $datelastseen = output_pref({ str => $datelastseen, dateformat => 'iso', dateonly => 1 });
859 push @where_strings, '(datelastseen < ? OR datelastseen IS NULL)';
860 push @bind_params, $datelastseen;
864 push @where_strings, 'items.location = ?';
865 push @bind_params, $location;
869 if($branch eq "homebranch"){
870 push @where_strings, 'items.homebranch = ?';
872 push @where_strings, 'items.holdingbranch = ?';
874 push @bind_params, $branchcode;
878 push @where_strings, 'biblioitems.itemtype = ?';
879 push @bind_params, $itemtype;
882 if ( $ignoreissued) {
883 $query .= "LEFT JOIN issues ON items.itemnumber = issues.itemnumber ";
884 push @where_strings, 'issues.date_due IS NULL';
887 if ( @where_strings ) {
889 $query .= join ' AND ', @where_strings;
891 my $count_query = $select_count . $query;
892 $query .= ' ORDER BY items.cn_sort, itemcallnumber, title';
893 $query .= " LIMIT $offset, $size" if ($offset and $size);
894 $query = $select_columns . $query;
895 my $sth = $dbh->prepare($query);
896 $sth->execute( @bind_params );
899 my $tmpresults = $sth->fetchall_arrayref({});
900 $sth = $dbh->prepare( $count_query );
901 $sth->execute( @bind_params );
902 my ($iTotalRecords) = $sth->fetchrow_array();
904 my @avs = Koha::AuthorisedValues->search(
905 { 'marc_subfield_structures.kohafield' => { '>' => '' },
906 'me.authorised_value' => { '>' => '' },
908 { join => { category => 'marc_subfield_structures' },
909 distinct => ['marc_subfield_structures.kohafield, me.category, frameworkcode, me.authorised_value'],
910 '+select' => [ 'marc_subfield_structures.kohafield', 'marc_subfield_structures.frameworkcode', 'me.authorised_value', 'me.lib' ],
911 '+as' => [ 'kohafield', 'frameworkcode', 'authorised_value', 'lib' ],
915 my $avmapping = { map { $_->get_column('kohafield') . ',' . $_->get_column('frameworkcode') . ',' . $_->get_column('authorised_value') => $_->get_column('lib') } @avs };
917 foreach my $row (@$tmpresults) {
920 foreach (keys %$row) {
921 if (defined($avmapping->{"items.$_,".$row->{'frameworkcode'}.",".$row->{$_}})) {
922 $row->{$_} = $avmapping->{"items.$_,".$row->{'frameworkcode'}.",".$row->{$_}};
928 return (\@results, $iTotalRecords);
931 =head2 GetItemsByBiblioitemnumber
933 GetItemsByBiblioitemnumber($biblioitemnumber);
935 Returns an arrayref of hashrefs suitable for use in a TMPL_LOOP
936 Called by C<C4::XISBN>
940 sub GetItemsByBiblioitemnumber {
941 my ( $bibitem ) = @_;
942 warn "C4::Items::GetItemsByBiblioitemnumber will be deprecated as of 18.11. - See Bug 21202 for details\n";
943 my $dbh = C4::Context->dbh;
944 my $sth = $dbh->prepare("SELECT * FROM items WHERE items.biblioitemnumber = ?") || die $dbh->errstr;
945 # Get all items attached to a biblioitem
948 $sth->execute($bibitem) || die $sth->errstr;
949 while ( my $data = $sth->fetchrow_hashref ) {
950 # Foreach item, get circulation information
951 my $sth2 = $dbh->prepare( "SELECT * FROM issues,borrowers
953 AND issues.borrowernumber = borrowers.borrowernumber"
955 $sth2->execute( $data->{'itemnumber'} );
956 if ( my $data2 = $sth2->fetchrow_hashref ) {
957 # if item is out, set the due date and who it is out too
958 $data->{'date_due'} = $data2->{'date_due'};
959 $data->{'cardnumber'} = $data2->{'cardnumber'};
960 $data->{'borrowernumber'} = $data2->{'borrowernumber'};
963 # set date_due to blank, so in the template we check itemlost, and withdrawn
964 $data->{'date_due'} = '';
966 # Find the last 3 people who borrowed this item.
967 my $query2 = "SELECT * FROM old_issues, borrowers WHERE itemnumber = ?
968 AND old_issues.borrowernumber = borrowers.borrowernumber
969 ORDER BY returndate desc,timestamp desc LIMIT 3";
970 $sth2 = $dbh->prepare($query2) || die $dbh->errstr;
971 $sth2->execute( $data->{'itemnumber'} ) || die $sth2->errstr;
973 while ( my $data2 = $sth2->fetchrow_hashref ) {
974 $data->{"timestamp$i2"} = $data2->{'timestamp'};
975 $data->{"card$i2"} = $data2->{'cardnumber'};
976 $data->{"borrower$i2"} = $data2->{'borrowernumber'};
979 push(@results,$data);
986 @results = GetItemsInfo($biblionumber);
988 Returns information about items with the given biblionumber.
990 C<GetItemsInfo> returns a list of references-to-hash. Each element
991 contains a number of keys. Most of them are attributes from the
992 C<biblio>, C<biblioitems>, C<items>, and C<itemtypes> tables in the
993 Koha database. Other keys include:
997 =item C<$data-E<gt>{branchname}>
999 The name (not the code) of the branch to which the book belongs.
1001 =item C<$data-E<gt>{datelastseen}>
1003 This is simply C<items.datelastseen>, except that while the date is
1004 stored in YYYY-MM-DD format in the database, here it is converted to
1005 DD/MM/YYYY format. A NULL date is returned as C<//>.
1007 =item C<$data-E<gt>{datedue}>
1009 =item C<$data-E<gt>{class}>
1011 This is the concatenation of C<biblioitems.classification>, the book's
1012 Dewey code, and C<biblioitems.subclass>.
1014 =item C<$data-E<gt>{ocount}>
1016 I think this is the number of copies of the book available.
1018 =item C<$data-E<gt>{order}>
1020 If this is set, it is set to C<One Order>.
1027 my ( $biblionumber ) = @_;
1028 my $dbh = C4::Context->dbh;
1029 require C4::Languages;
1030 my $language = C4::Languages::getlanguage();
1036 biblioitems.itemtype,
1039 biblioitems.publicationyear,
1040 biblioitems.publishercode,
1041 biblioitems.volumedate,
1042 biblioitems.volumedesc,
1045 items.notforloan as itemnotforloan,
1046 issues.borrowernumber,
1047 issues.date_due as datedue,
1048 issues.onsite_checkout,
1049 borrowers.cardnumber,
1051 borrowers.firstname,
1052 borrowers.branchcode as bcode,
1054 serial.publisheddate,
1055 itemtypes.description,
1056 COALESCE( localization.translation, itemtypes.description ) AS translated_description,
1057 itemtypes.notforloan as notforloan_per_itemtype,
1061 holding.opac_info as holding_branch_opac_info,
1062 home.opac_info as home_branch_opac_info
1066 LEFT JOIN branches AS holding ON items.holdingbranch = holding.branchcode
1067 LEFT JOIN branches AS home ON items.homebranch=home.branchcode
1068 LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber
1069 LEFT JOIN biblioitems ON biblioitems.biblioitemnumber = items.biblioitemnumber
1070 LEFT JOIN issues USING (itemnumber)
1071 LEFT JOIN borrowers USING (borrowernumber)
1072 LEFT JOIN serialitems USING (itemnumber)
1073 LEFT JOIN serial USING (serialid)
1074 LEFT JOIN itemtypes ON itemtypes.itemtype = "
1075 . (C4::Context->preference('item-level_itypes') ? 'items.itype' : 'biblioitems.itemtype');
1077 LEFT JOIN localization ON itemtypes.itemtype = localization.code
1078 AND localization.entity = 'itemtypes'
1079 AND localization.lang = ?
1082 $query .= " WHERE items.biblionumber = ? ORDER BY home.branchname, items.enumchron, LPAD( items.copynumber, 8, '0' ), items.dateaccessioned DESC" ;
1083 my $sth = $dbh->prepare($query);
1084 $sth->execute($language, $biblionumber);
1089 my $userenv = C4::Context->userenv;
1090 my $want_not_same_branch = C4::Context->preference("IndependentBranches") && !C4::Context->IsSuperLibrarian();
1091 while ( my $data = $sth->fetchrow_hashref ) {
1092 if ( $data->{borrowernumber} && $want_not_same_branch) {
1093 $data->{'NOTSAMEBRANCH'} = $data->{'bcode'} ne $userenv->{branch};
1096 $serial ||= $data->{'serial'};
1099 # get notforloan complete status if applicable
1100 $descriptions = Koha::AuthorisedValues->get_description_by_koha_field({frameworkcode => $data->{frameworkcode}, kohafield => 'items.notforloan', authorised_value => $data->{itemnotforloan} });
1101 $data->{notforloanvalue} = $descriptions->{lib} // '';
1102 $data->{notforloanvalueopac} = $descriptions->{opac_description} // '';
1104 # get restricted status and description if applicable
1105 $descriptions = Koha::AuthorisedValues->get_description_by_koha_field({frameworkcode => $data->{frameworkcode}, kohafield => 'items.restricted', authorised_value => $data->{restricted} });
1106 $data->{restrictedvalue} = $descriptions->{lib} // '';
1107 $data->{restrictedvalueopac} = $descriptions->{opac_description} // '';
1109 # my stack procedures
1110 $descriptions = Koha::AuthorisedValues->get_description_by_koha_field({frameworkcode => $data->{frameworkcode}, kohafield => 'items.stack', authorised_value => $data->{stack} });
1111 $data->{stack} = $descriptions->{lib} // '';
1113 # Find the last 3 people who borrowed this item.
1114 my $sth2 = $dbh->prepare("SELECT * FROM old_issues,borrowers
1115 WHERE itemnumber = ?
1116 AND old_issues.borrowernumber = borrowers.borrowernumber
1117 ORDER BY returndate DESC
1119 $sth2->execute($data->{'itemnumber'});
1121 while (my $data2 = $sth2->fetchrow_hashref()) {
1122 $data->{"timestamp$ii"} = $data2->{'timestamp'} if $data2->{'timestamp'};
1123 $data->{"card$ii"} = $data2->{'cardnumber'} if $data2->{'cardnumber'};
1124 $data->{"borrower$ii"} = $data2->{'borrowernumber'} if $data2->{'borrowernumber'};
1128 $results[$i] = $data;
1133 ? sort { ($b->{'publisheddate'} || $b->{'enumchron'}) cmp ($a->{'publisheddate'} || $a->{'enumchron'}) } @results
1137 =head2 GetItemsLocationInfo
1139 my @itemlocinfo = GetItemsLocationInfo($biblionumber);
1141 Returns the branch names, shelving location and itemcallnumber for each item attached to the biblio in question
1143 C<GetItemsInfo> returns a list of references-to-hash. Data returned:
1147 =item C<$data-E<gt>{homebranch}>
1149 Branch Name of the item's homebranch
1151 =item C<$data-E<gt>{holdingbranch}>
1153 Branch Name of the item's holdingbranch
1155 =item C<$data-E<gt>{location}>
1157 Item's shelving location code
1159 =item C<$data-E<gt>{location_intranet}>
1161 The intranet description for the Shelving Location as set in authorised_values 'LOC'
1163 =item C<$data-E<gt>{location_opac}>
1165 The OPAC description for the Shelving Location as set in authorised_values 'LOC'. Falls back to intranet description if no OPAC
1168 =item C<$data-E<gt>{itemcallnumber}>
1170 Item's itemcallnumber
1172 =item C<$data-E<gt>{cn_sort}>
1174 Item's call number normalized for sorting
1180 sub GetItemsLocationInfo {
1181 my $biblionumber = shift;
1184 my $dbh = C4::Context->dbh;
1185 my $query = "SELECT a.branchname as homebranch, b.branchname as holdingbranch,
1186 location, itemcallnumber, cn_sort
1187 FROM items, branches as a, branches as b
1188 WHERE homebranch = a.branchcode AND holdingbranch = b.branchcode
1189 AND biblionumber = ?
1190 ORDER BY cn_sort ASC";
1191 my $sth = $dbh->prepare($query);
1192 $sth->execute($biblionumber);
1194 while ( my $data = $sth->fetchrow_hashref ) {
1195 my $av = Koha::AuthorisedValues->search({ category => 'LOC', authorised_value => $data->{location} });
1196 $av = $av->count ? $av->next : undef;
1197 $data->{location_intranet} = $av ? $av->lib : '';
1198 $data->{location_opac} = $av ? $av->opac_description : '';
1199 push @results, $data;
1204 =head2 GetHostItemsInfo
1206 $hostiteminfo = GetHostItemsInfo($hostfield);
1207 Returns the iteminfo for items linked to records via a host field
1211 sub GetHostItemsInfo {
1213 my @returnitemsInfo;
1215 if( !C4::Context->preference('EasyAnalyticalRecords') ) {
1216 return @returnitemsInfo;
1220 if( C4::Context->preference('marcflavour') eq 'MARC21' ||
1221 C4::Context->preference('marcflavour') eq 'NORMARC') {
1222 @fields = $record->field('773');
1223 } elsif( C4::Context->preference('marcflavour') eq 'UNIMARC') {
1224 @fields = $record->field('461');
1227 foreach my $hostfield ( @fields ) {
1228 my $hostbiblionumber = $hostfield->subfield("0");
1229 my $linkeditemnumber = $hostfield->subfield("9");
1230 my @hostitemInfos = GetItemsInfo($hostbiblionumber);
1231 foreach my $hostitemInfo (@hostitemInfos) {
1232 if( $hostitemInfo->{itemnumber} eq $linkeditemnumber ) {
1233 push @returnitemsInfo, $hostitemInfo;
1238 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 $framework = C4::Biblio::GetFrameworkCode( $biblionumber );
1504 my $itemmarc = C4::Biblio::TransformKohaToMarc( $mungeditem ); # Bug 21774: no_split parameter removed to allow cloned subfields
1505 my ( $itemtag, $itemsubfield ) = C4::Biblio::GetMarcFromKohaField(
1506 "items.itemnumber", $framework,
1509 my $unlinked_item_subfields = _parse_unlinked_item_subfields_from_xml($mungeditem->{'items.more_subfields_xml'});
1510 if (defined $unlinked_item_subfields and $#$unlinked_item_subfields > -1) {
1511 foreach my $field ($itemmarc->field($itemtag)){
1512 $field->add_subfields(@$unlinked_item_subfields);
1518 =head1 PRIVATE FUNCTIONS AND VARIABLES
1520 The following functions are not meant to be called
1521 directly, but are documented in order to explain
1522 the inner workings of C<C4::Items>.
1526 =head2 %derived_columns
1528 This hash keeps track of item columns that
1529 are strictly derived from other columns in
1530 the item record and are not meant to be set
1533 Each key in the hash should be the name of a
1534 column (as named by TransformMarcToKoha). Each
1535 value should be hashref whose keys are the
1536 columns on which the derived column depends. The
1537 hashref should also contain a 'BUILDER' key
1538 that is a reference to a sub that calculates
1543 my %derived_columns = (
1544 'items.cn_sort' => {
1545 'itemcallnumber' => 1,
1546 'items.cn_source' => 1,
1547 'BUILDER' => \&_calc_items_cn_sort,
1551 =head2 _set_derived_columns_for_add
1553 _set_derived_column_for_add($item);
1555 Given an item hash representing a new item to be added,
1556 calculate any derived columns. Currently the only
1557 such column is C<items.cn_sort>.
1561 sub _set_derived_columns_for_add {
1564 foreach my $column (keys %derived_columns) {
1565 my $builder = $derived_columns{$column}->{'BUILDER'};
1566 my $source_values = {};
1567 foreach my $source_column (keys %{ $derived_columns{$column} }) {
1568 next if $source_column eq 'BUILDER';
1569 $source_values->{$source_column} = $item->{$source_column};
1571 $builder->($item, $source_values);
1575 =head2 _set_derived_columns_for_mod
1577 _set_derived_column_for_mod($item);
1579 Given an item hash representing a new item to be modified.
1580 calculate any derived columns. Currently the only
1581 such column is C<items.cn_sort>.
1583 This routine differs from C<_set_derived_columns_for_add>
1584 in that it needs to handle partial item records. In other
1585 words, the caller of C<ModItem> may have supplied only one
1586 or two columns to be changed, so this function needs to
1587 determine whether any of the columns to be changed affect
1588 any of the derived columns. Also, if a derived column
1589 depends on more than one column, but the caller is not
1590 changing all of then, this routine retrieves the unchanged
1591 values from the database in order to ensure a correct
1596 sub _set_derived_columns_for_mod {
1599 foreach my $column (keys %derived_columns) {
1600 my $builder = $derived_columns{$column}->{'BUILDER'};
1601 my $source_values = {};
1602 my %missing_sources = ();
1603 my $must_recalc = 0;
1604 foreach my $source_column (keys %{ $derived_columns{$column} }) {
1605 next if $source_column eq 'BUILDER';
1606 if (exists $item->{$source_column}) {
1608 $source_values->{$source_column} = $item->{$source_column};
1610 $missing_sources{$source_column} = 1;
1614 foreach my $source_column (keys %missing_sources) {
1615 $source_values->{$source_column} = _get_single_item_column($source_column, $item->{'itemnumber'});
1617 $builder->($item, $source_values);
1622 =head2 _do_column_fixes_for_mod
1624 _do_column_fixes_for_mod($item);
1626 Given an item hashref containing one or more
1627 columns to modify, fix up certain values.
1628 Specifically, set to 0 any passed value
1629 of C<notforloan>, C<damaged>, C<itemlost>, or
1630 C<withdrawn> that is either undefined or
1631 contains the empty string.
1635 sub _do_column_fixes_for_mod {
1638 if (exists $item->{'notforloan'} and
1639 (not defined $item->{'notforloan'} or $item->{'notforloan'} eq '')) {
1640 $item->{'notforloan'} = 0;
1642 if (exists $item->{'damaged'} and
1643 (not defined $item->{'damaged'} or $item->{'damaged'} eq '')) {
1644 $item->{'damaged'} = 0;
1646 if (exists $item->{'itemlost'} and
1647 (not defined $item->{'itemlost'} or $item->{'itemlost'} eq '')) {
1648 $item->{'itemlost'} = 0;
1650 if (exists $item->{'withdrawn'} and
1651 (not defined $item->{'withdrawn'} or $item->{'withdrawn'} eq '')) {
1652 $item->{'withdrawn'} = 0;
1654 if (exists $item->{location}
1655 and $item->{location} ne 'CART'
1656 and $item->{location} ne 'PROC'
1657 and not $item->{permanent_location}
1659 $item->{'permanent_location'} = $item->{'location'};
1661 if (exists $item->{'timestamp'}) {
1662 delete $item->{'timestamp'};
1666 =head2 _get_single_item_column
1668 _get_single_item_column($column, $itemnumber);
1670 Retrieves the value of a single column from an C<items>
1671 row specified by C<$itemnumber>.
1675 sub _get_single_item_column {
1677 my $itemnumber = shift;
1679 my $dbh = C4::Context->dbh;
1680 my $sth = $dbh->prepare("SELECT $column FROM items WHERE itemnumber = ?");
1681 $sth->execute($itemnumber);
1682 my ($value) = $sth->fetchrow();
1686 =head2 _calc_items_cn_sort
1688 _calc_items_cn_sort($item, $source_values);
1690 Helper routine to calculate C<items.cn_sort>.
1694 sub _calc_items_cn_sort {
1696 my $source_values = shift;
1698 $item->{'items.cn_sort'} = GetClassSort($source_values->{'items.cn_source'}, $source_values->{'itemcallnumber'}, "");
1701 =head2 _set_defaults_for_add
1703 _set_defaults_for_add($item_hash);
1705 Given an item hash representing an item to be added, set
1706 correct default values for columns whose default value
1707 is not handled by the DBMS. This includes the following
1714 C<items.dateaccessioned>
1736 sub _set_defaults_for_add {
1738 $item->{dateaccessioned} ||= output_pref({ dt => dt_from_string, dateformat => 'iso', dateonly => 1 });
1739 $item->{$_} ||= 0 for (qw( notforloan damaged itemlost withdrawn));
1742 =head2 _koha_new_item
1744 my ($itemnumber,$error) = _koha_new_item( $item, $barcode );
1746 Perform the actual insert into the C<items> table.
1750 sub _koha_new_item {
1751 my ( $item, $barcode ) = @_;
1752 my $dbh=C4::Context->dbh;
1754 $item->{permanent_location} //= $item->{location};
1755 _mod_item_dates( $item );
1757 "INSERT INTO items SET
1759 biblioitemnumber = ?,
1761 dateaccessioned = ?,
1765 replacementprice = ?,
1766 replacementpricedate = ?,
1767 datelastborrowed = ?,
1775 coded_location_qualifier = ?,
1778 itemnotes_nonpublic = ?,
1782 permanent_location = ?,
1794 more_subfields_xml = ?,
1799 my $sth = $dbh->prepare($query);
1800 my $today = output_pref({ dt => dt_from_string, dateformat => 'iso', dateonly => 1 });
1802 $item->{'biblionumber'},
1803 $item->{'biblioitemnumber'},
1805 $item->{'dateaccessioned'},
1806 $item->{'booksellerid'},
1807 $item->{'homebranch'},
1809 $item->{'replacementprice'},
1810 $item->{'replacementpricedate'} || $today,
1811 $item->{datelastborrowed},
1812 $item->{datelastseen} || $today,
1814 $item->{'notforloan'},
1816 $item->{'itemlost'},
1817 $item->{'withdrawn'},
1818 $item->{'itemcallnumber'},
1819 $item->{'coded_location_qualifier'},
1820 $item->{'restricted'},
1821 $item->{'itemnotes'},
1822 $item->{'itemnotes_nonpublic'},
1823 $item->{'holdingbranch'},
1825 $item->{'location'},
1826 $item->{'permanent_location'},
1829 $item->{'renewals'},
1830 $item->{'reserves'},
1831 $item->{'items.cn_source'},
1832 $item->{'items.cn_sort'},
1835 $item->{'materials'},
1837 $item->{'enumchron'},
1838 $item->{'more_subfields_xml'},
1839 $item->{'copynumber'},
1840 $item->{'stocknumber'},
1841 $item->{'new_status'},
1845 if ( defined $sth->errstr ) {
1846 $error.="ERROR in _koha_new_item $query".$sth->errstr;
1849 $itemnumber = $dbh->{'mysql_insertid'};
1852 return ( $itemnumber, $error );
1855 =head2 MoveItemFromBiblio
1857 MoveItemFromBiblio($itenumber, $frombiblio, $tobiblio);
1859 Moves an item from a biblio to another
1861 Returns undef if the move failed or the biblionumber of the destination record otherwise
1865 sub MoveItemFromBiblio {
1866 my ($itemnumber, $frombiblio, $tobiblio) = @_;
1867 my $dbh = C4::Context->dbh;
1868 my ( $tobiblioitem ) = $dbh->selectrow_array(q|
1869 SELECT biblioitemnumber
1871 WHERE biblionumber = ?
1872 |, undef, $tobiblio );
1873 my $return = $dbh->do(q|
1875 SET biblioitemnumber = ?,
1877 WHERE itemnumber = ?
1878 AND biblionumber = ?
1879 |, undef, $tobiblioitem, $tobiblio, $itemnumber, $frombiblio );
1881 ModZebra( $tobiblio, "specialUpdate", "biblioserver" );
1882 ModZebra( $frombiblio, "specialUpdate", "biblioserver" );
1883 # Checking if the item we want to move is in an order
1884 require C4::Acquisition;
1885 my $order = C4::Acquisition::GetOrderFromItemnumber($itemnumber);
1887 # Replacing the biblionumber within the order if necessary
1888 $order->{'biblionumber'} = $tobiblio;
1889 C4::Acquisition::ModOrder($order);
1892 # Update reserves, hold_fill_targets, tmp_holdsqueue and linktracker tables
1893 for my $table_name ( qw( reserves hold_fill_targets tmp_holdsqueue linktracker ) ) {
1896 SET biblionumber = ?
1897 WHERE itemnumber = ?
1898 |, undef, $tobiblio, $itemnumber );
1905 =head2 ItemSafeToDelete
1907 ItemSafeToDelete( $biblionumber, $itemnumber);
1909 Exported function (core API) for checking whether an item record is safe to delete.
1911 returns 1 if the item is safe to delete,
1913 "book_on_loan" if the item is checked out,
1915 "not_same_branch" if the item is blocked by independent branches,
1917 "book_reserved" if the there are holds aganst the item, or
1919 "linked_analytics" if the item has linked analytic records.
1923 sub ItemSafeToDelete {
1924 my ( $biblionumber, $itemnumber ) = @_;
1926 my $dbh = C4::Context->dbh;
1930 my $countanalytics = GetAnalyticsCount($itemnumber);
1932 # check that there is no issue on this item before deletion.
1933 my $sth = $dbh->prepare(
1935 SELECT COUNT(*) FROM issues
1936 WHERE itemnumber = ?
1939 $sth->execute($itemnumber);
1940 my ($onloan) = $sth->fetchrow;
1942 my $item = GetItem($itemnumber);
1945 $status = "book_on_loan";
1947 elsif ( defined C4::Context->userenv
1948 and !C4::Context->IsSuperLibrarian()
1949 and C4::Context->preference("IndependentBranches")
1950 and ( C4::Context->userenv->{branch} ne $item->{'homebranch'} ) )
1952 $status = "not_same_branch";
1955 # check it doesn't have a waiting reserve
1956 $sth = $dbh->prepare(
1958 SELECT COUNT(*) FROM reserves
1959 WHERE (found = 'W' OR found = 'T')
1963 $sth->execute($itemnumber);
1964 my ($reserve) = $sth->fetchrow;
1966 $status = "book_reserved";
1968 elsif ( $countanalytics > 0 ) {
1969 $status = "linked_analytics";
1980 DelItemCheck( $biblionumber, $itemnumber);
1982 Exported function (core API) for deleting an item record in Koha if there no current issue.
1984 DelItemCheck wraps ItemSafeToDelete around DelItem.
1989 my ( $biblionumber, $itemnumber ) = @_;
1990 my $status = ItemSafeToDelete( $biblionumber, $itemnumber );
1992 if ( $status == 1 ) {
1995 biblionumber => $biblionumber,
1996 itemnumber => $itemnumber
2003 =head2 _koha_modify_item
2005 my ($itemnumber,$error) =_koha_modify_item( $item );
2007 Perform the actual update of the C<items> row. Note that this
2008 routine accepts a hashref specifying the columns to update.
2012 sub _koha_modify_item {
2014 my $dbh=C4::Context->dbh;
2017 my $query = "UPDATE items SET ";
2019 _mod_item_dates( $item );
2020 for my $key ( keys %$item ) {
2021 next if ( $key eq 'itemnumber' );
2023 push @bind, $item->{$key};
2026 $query .= " WHERE itemnumber=?";
2027 push @bind, $item->{'itemnumber'};
2028 my $sth = $dbh->prepare($query);
2029 $sth->execute(@bind);
2031 $error.="ERROR in _koha_modify_item $query: ".$sth->errstr;
2034 return ($item->{'itemnumber'},$error);
2037 sub _mod_item_dates { # date formatting for date fields in item hash
2039 return if !$item || ref($item) ne 'HASH';
2042 { $_ =~ /^onloan$|^date|date$|datetime$/ }
2044 # Incl. dateaccessioned,replacementpricedate,datelastborrowed,datelastseen
2045 # NOTE: We do not (yet) have items fields ending with datetime
2046 # Fields with _on$ have been handled already
2048 foreach my $key ( @keys ) {
2049 next if !defined $item->{$key}; # skip undefs
2050 my $dt = eval { dt_from_string( $item->{$key} ) };
2051 # eval: dt_from_string will die on us if we pass illegal dates
2054 if( defined $dt && ref($dt) eq 'DateTime' ) {
2055 if( $key =~ /datetime/ ) {
2056 $newstr = DateTime::Format::MySQL->format_datetime($dt);
2058 $newstr = DateTime::Format::MySQL->format_date($dt);
2061 $item->{$key} = $newstr; # might be undef to clear garbage
2065 =head2 _koha_delete_item
2067 _koha_delete_item( $itemnum );
2069 Internal function to delete an item record from the koha tables
2073 sub _koha_delete_item {
2074 my ( $itemnum ) = @_;
2076 my $dbh = C4::Context->dbh;
2077 # save the deleted item to deleteditems table
2078 my $sth = $dbh->prepare("SELECT * FROM items WHERE itemnumber=?");
2079 $sth->execute($itemnum);
2080 my $data = $sth->fetchrow_hashref();
2082 # There is no item to delete
2083 return 0 unless $data;
2085 my $query = "INSERT INTO deleteditems SET ";
2087 foreach my $key ( keys %$data ) {
2088 next if ( $key eq 'timestamp' ); # timestamp will be set by db
2089 $query .= "$key = ?,";
2090 push( @bind, $data->{$key} );
2093 $sth = $dbh->prepare($query);
2094 $sth->execute(@bind);
2096 # delete from items table
2097 $sth = $dbh->prepare("DELETE FROM items WHERE itemnumber=?");
2098 my $deleted = $sth->execute($itemnum);
2099 return ( $deleted == 1 ) ? 1 : 0;
2102 =head2 _marc_from_item_hash
2104 my $item_marc = _marc_from_item_hash($item, $frameworkcode[, $unlinked_item_subfields]);
2106 Given an item hash representing a complete item record,
2107 create a C<MARC::Record> object containing an embedded
2108 tag representing that item.
2110 The third, optional parameter C<$unlinked_item_subfields> is
2111 an arrayref of subfields (not mapped to C<items> fields per the
2112 framework) to be added to the MARC representation
2117 sub _marc_from_item_hash {
2119 my $frameworkcode = shift;
2120 my $unlinked_item_subfields;
2122 $unlinked_item_subfields = shift;
2125 # Tack on 'items.' prefix to column names so lookup from MARC frameworks will work
2126 # Also, don't emit a subfield if the underlying field is blank.
2127 my $mungeditem = { map { (defined($item->{$_}) and $item->{$_} ne '') ?
2128 (/^items\./ ? ($_ => $item->{$_}) : ("items.$_" => $item->{$_}))
2129 : () } keys %{ $item } };
2131 my $item_marc = MARC::Record->new();
2132 foreach my $item_field ( keys %{$mungeditem} ) {
2133 my ( $tag, $subfield ) = C4::Biblio::GetMarcFromKohaField( $item_field, $frameworkcode );
2134 next unless defined $tag and defined $subfield; # skip if not mapped to MARC field
2135 my @values = split(/\s?\|\s?/, $mungeditem->{$item_field}, -1);
2136 foreach my $value (@values){
2137 if ( my $field = $item_marc->field($tag) ) {
2138 $field->add_subfields( $subfield => $value );
2140 my $add_subfields = [];
2141 if (defined $unlinked_item_subfields and ref($unlinked_item_subfields) eq 'ARRAY' and $#$unlinked_item_subfields > -1) {
2142 $add_subfields = $unlinked_item_subfields;
2144 $item_marc->add_fields( $tag, " ", " ", $subfield => $value, @$add_subfields );
2152 =head2 _repack_item_errors
2154 Add an error message hash generated by C<CheckItemPreSave>
2155 to a list of errors.
2159 sub _repack_item_errors {
2160 my $item_sequence_num = shift;
2161 my $item_ref = shift;
2162 my $error_ref = shift;
2164 my @repacked_errors = ();
2166 foreach my $error_code (sort keys %{ $error_ref }) {
2167 my $repacked_error = {};
2168 $repacked_error->{'item_sequence'} = $item_sequence_num;
2169 $repacked_error->{'item_barcode'} = exists($item_ref->{'barcode'}) ? $item_ref->{'barcode'} : '';
2170 $repacked_error->{'error_code'} = $error_code;
2171 $repacked_error->{'error_information'} = $error_ref->{$error_code};
2172 push @repacked_errors, $repacked_error;
2175 return @repacked_errors;
2178 =head2 _get_unlinked_item_subfields
2180 my $unlinked_item_subfields = _get_unlinked_item_subfields($original_item_marc, $frameworkcode);
2184 sub _get_unlinked_item_subfields {
2185 my $original_item_marc = shift;
2186 my $frameworkcode = shift;
2188 my $marcstructure = GetMarcStructure(1, $frameworkcode, { unsafe => 1 });
2190 # assume that this record has only one field, and that that
2191 # field contains only the item information
2193 my @fields = $original_item_marc->fields();
2194 if ($#fields > -1) {
2195 my $field = $fields[0];
2196 my $tag = $field->tag();
2197 foreach my $subfield ($field->subfields()) {
2198 if (defined $subfield->[1] and
2199 $subfield->[1] ne '' and
2200 !$marcstructure->{$tag}->{$subfield->[0]}->{'kohafield'}) {
2201 push @$subfields, $subfield->[0] => $subfield->[1];
2208 =head2 _get_unlinked_subfields_xml
2210 my $unlinked_subfields_xml = _get_unlinked_subfields_xml($unlinked_item_subfields);
2214 sub _get_unlinked_subfields_xml {
2215 my $unlinked_item_subfields = shift;
2218 if (defined $unlinked_item_subfields and ref($unlinked_item_subfields) eq 'ARRAY' and $#$unlinked_item_subfields > -1) {
2219 my $marc = MARC::Record->new();
2220 # use of tag 999 is arbitrary, and doesn't need to match the item tag
2221 # used in the framework
2222 $marc->append_fields(MARC::Field->new('999', ' ', ' ', @$unlinked_item_subfields));
2223 $marc->encoding("UTF-8");
2224 $xml = $marc->as_xml("USMARC");
2230 =head2 _parse_unlinked_item_subfields_from_xml
2232 my $unlinked_item_subfields = _parse_unlinked_item_subfields_from_xml($whole_item->{'more_subfields_xml'}):
2236 sub _parse_unlinked_item_subfields_from_xml {
2238 require C4::Charset;
2239 return unless defined $xml and $xml ne "";
2240 my $marc = MARC::Record->new_from_xml(C4::Charset::StripNonXmlChars($xml),'UTF-8');
2241 my $unlinked_subfields = [];
2242 my @fields = $marc->fields();
2243 if ($#fields > -1) {
2244 foreach my $subfield ($fields[0]->subfields()) {
2245 push @$unlinked_subfields, $subfield->[0] => $subfield->[1];
2248 return $unlinked_subfields;
2251 =head2 GetAnalyticsCount
2253 $count= &GetAnalyticsCount($itemnumber)
2255 counts Usage of itemnumber in Analytical bibliorecords.
2259 sub GetAnalyticsCount {
2260 my ($itemnumber) = @_;
2262 ### ZOOM search here
2264 $query= "hi=".$itemnumber;
2265 my $searcher = Koha::SearchEngine::Search->new({index => $Koha::SearchEngine::BIBLIOS_INDEX});
2266 my ($err,$res,$result) = $searcher->simple_search_compat($query,0,10);
2270 =head2 SearchItemsByField
2272 my $items = SearchItemsByField($field, $value);
2274 SearchItemsByField will search for items on a specific given field.
2275 For instance you can search all items with a specific stocknumber like this:
2277 my $items = SearchItemsByField('stocknumber', $stocknumber);
2281 sub SearchItemsByField {
2282 my ($field, $value) = @_;
2289 my ($results) = SearchItems($filters);
2293 sub _SearchItems_build_where_fragment {
2296 my $dbh = C4::Context->dbh;
2299 if (exists($filter->{conjunction})) {
2300 my (@where_strs, @where_args);
2301 foreach my $f (@{ $filter->{filters} }) {
2302 my $fragment = _SearchItems_build_where_fragment($f);
2304 push @where_strs, $fragment->{str};
2305 push @where_args, @{ $fragment->{args} };
2310 $where_str = '(' . join (' ' . $filter->{conjunction} . ' ', @where_strs) . ')';
2313 args => \@where_args,
2317 my @columns = Koha::Database->new()->schema()->resultset('Item')->result_source->columns;
2318 push @columns, Koha::Database->new()->schema()->resultset('Biblio')->result_source->columns;
2319 push @columns, Koha::Database->new()->schema()->resultset('Biblioitem')->result_source->columns;
2320 my @operators = qw(= != > < >= <= like);
2321 my $field = $filter->{field};
2322 if ( (0 < grep /^$field$/, @columns) or (substr($field, 0, 5) eq 'marc:') ) {
2323 my $op = $filter->{operator};
2324 my $query = $filter->{query};
2326 if (!$op or (0 == grep /^$op$/, @operators)) {
2327 $op = '='; # default operator
2331 if ($field =~ /^marc:(\d{3})(?:\$(\w))?$/) {
2333 my $marcsubfield = $2;
2334 my ($kohafield) = $dbh->selectrow_array(q|
2335 SELECT kohafield FROM marc_subfield_structure
2336 WHERE tagfield=? AND tagsubfield=? AND frameworkcode=''
2337 |, undef, $marcfield, $marcsubfield);
2340 $column = $kohafield;
2342 # MARC field is not linked to a DB field so we need to use
2343 # ExtractValue on marcxml from biblio_metadata or
2344 # items.more_subfields_xml, depending on the MARC field.
2347 my ($itemfield) = C4::Biblio::GetMarcFromKohaField('items.itemnumber');
2348 if ($marcfield eq $itemfield) {
2349 $sqlfield = 'more_subfields_xml';
2350 $xpath = '//record/datafield/subfield[@code="' . $marcsubfield . '"]';
2352 $sqlfield = 'metadata'; # From biblio_metadata
2353 if ($marcfield < 10) {
2354 $xpath = "//record/controlfield[\@tag=\"$marcfield\"]";
2356 $xpath = "//record/datafield[\@tag=\"$marcfield\"]/subfield[\@code=\"$marcsubfield\"]";
2359 $column = "ExtractValue($sqlfield, '$xpath')";
2365 if (ref $query eq 'ARRAY') {
2368 } elsif ($op eq '!=') {
2372 str => "$column $op (" . join (',', ('?') x @$query) . ")",
2377 str => "$column $op ?",
2384 return $where_fragment;
2389 my ($items, $total) = SearchItems($filter, $params);
2391 Perform a search among items
2393 $filter is a reference to a hash which can be a filter, or a combination of filters.
2395 A filter has the following keys:
2399 =item * field: the name of a SQL column in table items
2401 =item * query: the value to search in this column
2403 =item * operator: comparison operator. Can be one of = != > < >= <= like
2407 A combination of filters hash the following keys:
2411 =item * conjunction: 'AND' or 'OR'
2413 =item * filters: array ref of filters
2417 $params is a reference to a hash that can contain the following parameters:
2421 =item * rows: Number of items to return. 0 returns everything (default: 0)
2423 =item * page: Page to return (return items from (page-1)*rows to (page*rows)-1)
2426 =item * sortby: A SQL column name in items table to sort on
2428 =item * sortorder: 'ASC' or 'DESC'
2435 my ($filter, $params) = @_;
2439 return unless ref $filter eq 'HASH';
2440 return unless ref $params eq 'HASH';
2442 # Default parameters
2443 $params->{rows} ||= 0;
2444 $params->{page} ||= 1;
2445 $params->{sortby} ||= 'itemnumber';
2446 $params->{sortorder} ||= 'ASC';
2448 my ($where_str, @where_args);
2449 my $where_fragment = _SearchItems_build_where_fragment($filter);
2450 if ($where_fragment) {
2451 $where_str = $where_fragment->{str};
2452 @where_args = @{ $where_fragment->{args} };
2455 my $dbh = C4::Context->dbh;
2457 SELECT SQL_CALC_FOUND_ROWS items.*
2459 LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber
2460 LEFT JOIN biblioitems ON biblioitems.biblioitemnumber = items.biblioitemnumber
2461 LEFT JOIN biblio_metadata ON biblio_metadata.biblionumber = biblio.biblionumber
2464 if (defined $where_str and $where_str ne '') {
2465 $query .= qq{ AND $where_str };
2468 $query .= q{ AND biblio_metadata.format = 'marcxml' AND biblio_metadata.marcflavour = ? };
2469 push @where_args, C4::Context->preference('marcflavour');
2471 my @columns = Koha::Database->new()->schema()->resultset('Item')->result_source->columns;
2472 push @columns, Koha::Database->new()->schema()->resultset('Biblio')->result_source->columns;
2473 push @columns, Koha::Database->new()->schema()->resultset('Biblioitem')->result_source->columns;
2474 my $sortby = (0 < grep {$params->{sortby} eq $_} @columns)
2475 ? $params->{sortby} : 'itemnumber';
2476 my $sortorder = (uc($params->{sortorder}) eq 'ASC') ? 'ASC' : 'DESC';
2477 $query .= qq{ ORDER BY $sortby $sortorder };
2479 my $rows = $params->{rows};
2482 my $offset = $rows * ($params->{page}-1);
2483 $query .= qq { LIMIT ?, ? };
2484 push @limit_args, $offset, $rows;
2487 my $sth = $dbh->prepare($query);
2488 my $rv = $sth->execute(@where_args, @limit_args);
2490 return unless ($rv);
2491 my ($total_rows) = $dbh->selectrow_array(q{ SELECT FOUND_ROWS() });
2493 return ($sth->fetchall_arrayref({}), $total_rows);
2497 =head1 OTHER FUNCTIONS
2501 ($indicators, $value) = _find_value($tag, $subfield, $record,$encoding);
2503 Find the given $subfield in the given $tag in the given
2504 MARC::Record $record. If the subfield is found, returns
2505 the (indicators, value) pair; otherwise, (undef, undef) is
2509 Such a function is used in addbiblio AND additem and serial-edit and maybe could be used in Authorities.
2510 I suggest we export it from this module.
2515 my ( $tagfield, $insubfield, $record, $encoding ) = @_;
2518 if ( $tagfield < 10 ) {
2519 if ( $record->field($tagfield) ) {
2520 push @result, $record->field($tagfield)->data();
2525 foreach my $field ( $record->field($tagfield) ) {
2526 my @subfields = $field->subfields();
2527 foreach my $subfield (@subfields) {
2528 if ( @$subfield[0] eq $insubfield ) {
2529 push @result, @$subfield[1];
2530 $indicator = $field->indicator(1) . $field->indicator(2);
2535 return ( $indicator, @result );
2539 =head2 PrepareItemrecordDisplay
2541 PrepareItemrecordDisplay($itemrecord,$bibnum,$itemumber,$frameworkcode);
2543 Returns a hash with all the fields for Display a given item data in a template
2545 The $frameworkcode returns the item for the given frameworkcode, ONLY if bibnum is not provided
2549 sub PrepareItemrecordDisplay {
2551 my ( $bibnum, $itemnum, $defaultvalues, $frameworkcode ) = @_;
2553 my $dbh = C4::Context->dbh;
2554 $frameworkcode = C4::Biblio::GetFrameworkCode($bibnum) if $bibnum;
2555 my ( $itemtagfield, $itemtagsubfield ) = C4::Biblio::GetMarcFromKohaField( "items.itemnumber", $frameworkcode );
2557 # Note: $tagslib obtained from GetMarcStructure() in 'unsafe' mode is
2558 # a shared data structure. No plugin (including custom ones) should change
2559 # its contents. See also GetMarcStructure.
2560 my $tagslib = GetMarcStructure( 1, $frameworkcode, { unsafe => 1 } );
2562 # return nothing if we don't have found an existing framework.
2563 return q{} unless $tagslib;
2566 $itemrecord = C4::Items::GetMarcItem( $bibnum, $itemnum );
2570 my $branch_limit = C4::Context->userenv ? C4::Context->userenv->{"branch"} : "";
2572 SELECT authorised_value,lib FROM authorised_values
2575 LEFT JOIN authorised_values_branches ON ( id = av_id )
2580 $query .= qq{ AND ( branchcode = ? OR branchcode IS NULL )} if $branch_limit;
2581 $query .= qq{ ORDER BY lib};
2582 my $authorised_values_sth = $dbh->prepare( $query );
2583 foreach my $tag ( sort keys %{$tagslib} ) {
2586 # loop through each subfield
2588 foreach my $subfield ( sort keys %{ $tagslib->{$tag} } ) {
2589 next if IsMarcStructureInternal($tagslib->{$tag}{$subfield});
2590 next unless ( $tagslib->{$tag}->{$subfield}->{'tab'} );
2591 next if ( $tagslib->{$tag}->{$subfield}->{'tab'} ne "10" );
2593 $subfield_data{tag} = $tag;
2594 $subfield_data{subfield} = $subfield;
2595 $subfield_data{countsubfield} = $cntsubf++;
2596 $subfield_data{kohafield} = $tagslib->{$tag}->{$subfield}->{'kohafield'};
2597 $subfield_data{id} = "tag_".$tag."_subfield_".$subfield."_".int(rand(1000000));
2599 # $subfield_data{marc_lib}=$tagslib->{$tag}->{$subfield}->{lib};
2600 $subfield_data{marc_lib} = $tagslib->{$tag}->{$subfield}->{lib};
2601 $subfield_data{mandatory} = $tagslib->{$tag}->{$subfield}->{mandatory};
2602 $subfield_data{repeatable} = $tagslib->{$tag}->{$subfield}->{repeatable};
2603 $subfield_data{hidden} = "display:none"
2604 if ( ( $tagslib->{$tag}->{$subfield}->{hidden} > 4 )
2605 || ( $tagslib->{$tag}->{$subfield}->{hidden} < -4 ) );
2606 my ( $x, $defaultvalue );
2608 ( $x, $defaultvalue ) = _find_value( $tag, $subfield, $itemrecord );
2610 $defaultvalue = $tagslib->{$tag}->{$subfield}->{defaultvalue} unless $defaultvalue;
2611 if ( !defined $defaultvalue ) {
2612 $defaultvalue = q||;
2614 $defaultvalue =~ s/"/"/g;
2617 my $maxlength = $tagslib->{$tag}->{$subfield}->{maxlength};
2619 # search for itemcallnumber if applicable
2620 if ( $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.itemcallnumber'
2621 && C4::Context->preference('itemcallnumber') ) {
2622 my $CNtag = substr( C4::Context->preference('itemcallnumber'), 0, 3 );
2623 my $CNsubfield = substr( C4::Context->preference('itemcallnumber'), 3, 1 );
2624 if ( $itemrecord and my $field = $itemrecord->field($CNtag) ) {
2625 $defaultvalue = $field->subfield($CNsubfield);
2628 if ( $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.itemcallnumber'
2630 && $defaultvalues->{'callnumber'} ) {
2631 if( $itemrecord and $defaultvalues and not $itemrecord->subfield($tag,$subfield) ){
2632 # if the item record exists, only use default value if the item has no callnumber
2633 $defaultvalue = $defaultvalues->{callnumber};
2634 } elsif ( !$itemrecord and $defaultvalues ) {
2635 # if the item record *doesn't* exists, always use the default value
2636 $defaultvalue = $defaultvalues->{callnumber};
2639 if ( ( $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.holdingbranch' || $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.homebranch' )
2641 && $defaultvalues->{'branchcode'} ) {
2642 if ( $itemrecord and $defaultvalues and not $itemrecord->subfield($tag,$subfield) ) {
2643 $defaultvalue = $defaultvalues->{branchcode};
2646 if ( ( $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.location' )
2648 && $defaultvalues->{'location'} ) {
2650 if ( $itemrecord and $defaultvalues and not $itemrecord->subfield($tag,$subfield) ) {
2651 # if the item record exists, only use default value if the item has no locationr
2652 $defaultvalue = $defaultvalues->{location};
2653 } elsif ( !$itemrecord and $defaultvalues ) {
2654 # if the item record *doesn't* exists, always use the default value
2655 $defaultvalue = $defaultvalues->{location};
2658 if ( $tagslib->{$tag}->{$subfield}->{authorised_value} ) {
2659 my @authorised_values;
2662 # builds list, depending on authorised value...
2664 if ( $tagslib->{$tag}->{$subfield}->{'authorised_value'} eq "branches" ) {
2665 if ( ( C4::Context->preference("IndependentBranches") )
2666 && !C4::Context->IsSuperLibrarian() ) {
2667 my $sth = $dbh->prepare( "SELECT branchcode,branchname FROM branches WHERE branchcode = ? ORDER BY branchname" );
2668 $sth->execute( C4::Context->userenv->{branch} );
2669 push @authorised_values, ""
2670 unless ( $tagslib->{$tag}->{$subfield}->{mandatory} );
2671 while ( my ( $branchcode, $branchname ) = $sth->fetchrow_array ) {
2672 push @authorised_values, $branchcode;
2673 $authorised_lib{$branchcode} = $branchname;
2676 my $sth = $dbh->prepare( "SELECT branchcode,branchname FROM branches ORDER BY branchname" );
2678 push @authorised_values, ""
2679 unless ( $tagslib->{$tag}->{$subfield}->{mandatory} );
2680 while ( my ( $branchcode, $branchname ) = $sth->fetchrow_array ) {
2681 push @authorised_values, $branchcode;
2682 $authorised_lib{$branchcode} = $branchname;
2686 $defaultvalue = C4::Context->userenv ? C4::Context->userenv->{branch} : undef;
2687 if ( $defaultvalues and $defaultvalues->{branchcode} ) {
2688 $defaultvalue = $defaultvalues->{branchcode};
2692 } elsif ( $tagslib->{$tag}->{$subfield}->{authorised_value} eq "itemtypes" ) {
2693 my $itemtypes = Koha::ItemTypes->search_with_localization;
2694 push @authorised_values, ""
2695 unless ( $tagslib->{$tag}->{$subfield}->{mandatory} );
2696 while ( my $itemtype = $itemtypes->next ) {
2697 push @authorised_values, $itemtype->itemtype;
2698 $authorised_lib{$itemtype->itemtype} = $itemtype->translated_description;
2700 if ($defaultvalues && $defaultvalues->{'itemtype'}) {
2701 $defaultvalue = $defaultvalues->{'itemtype'};
2705 } elsif ( $tagslib->{$tag}->{$subfield}->{authorised_value} eq "cn_source" ) {
2706 push @authorised_values, "" unless ( $tagslib->{$tag}->{$subfield}->{mandatory} );
2708 my $class_sources = GetClassSources();
2709 my $default_source = C4::Context->preference("DefaultClassificationSource");
2711 foreach my $class_source (sort keys %$class_sources) {
2712 next unless $class_sources->{$class_source}->{'used'} or
2713 ($class_source eq $default_source);
2714 push @authorised_values, $class_source;
2715 $authorised_lib{$class_source} = $class_sources->{$class_source}->{'description'};
2718 $defaultvalue = $default_source;
2720 #---- "true" authorised value
2722 $authorised_values_sth->execute(
2723 $tagslib->{$tag}->{$subfield}->{authorised_value},
2724 $branch_limit ? $branch_limit : ()
2726 push @authorised_values, ""
2727 unless ( $tagslib->{$tag}->{$subfield}->{mandatory} );
2728 while ( my ( $value, $lib ) = $authorised_values_sth->fetchrow_array ) {
2729 push @authorised_values, $value;
2730 $authorised_lib{$value} = $lib;
2733 $subfield_data{marc_value} = {
2735 values => \@authorised_values,
2736 default => "$defaultvalue",
2737 labels => \%authorised_lib,
2739 } elsif ( $tagslib->{$tag}->{$subfield}->{value_builder} ) {
2741 require Koha::FrameworkPlugin;
2742 my $plugin = Koha::FrameworkPlugin->new({
2743 name => $tagslib->{$tag}->{$subfield}->{value_builder},
2746 my $pars = { dbh => $dbh, record => undef, tagslib =>$tagslib, id => $subfield_data{id}, tabloop => undef };
2747 $plugin->build( $pars );
2748 if ( $itemrecord and my $field = $itemrecord->field($tag) ) {
2749 $defaultvalue = $field->subfield($subfield);
2751 if( !$plugin->errstr ) {
2752 #TODO Move html to template; see report 12176/13397
2753 my $tab= $plugin->noclick? '-1': '';
2754 my $class= $plugin->noclick? ' disabled': '';
2755 my $title= $plugin->noclick? 'No popup': 'Tag editor';
2756 $subfield_data{marc_value} = qq[<input type="text" id="$subfield_data{id}" name="field_value" class="input_marceditor" size="50" maxlength="$maxlength" value="$defaultvalue" /><a href="#" id="buttonDot_$subfield_data{id}" tabindex="$tab" class="buttonDot $class" title="$title">...</a>\n].$plugin->javascript;
2758 warn $plugin->errstr;
2759 $subfield_data{marc_value} = qq(<input type="text" id="$subfield_data{id}" name="field_value" class="input_marceditor" size="50" maxlength="$maxlength" value="$defaultvalue" />); # supply default input form
2762 elsif ( $tag eq '' ) { # it's an hidden field
2763 $subfield_data{marc_value} = qq(<input type="hidden" tabindex="1" id="$subfield_data{id}" name="field_value" class="input_marceditor" size="50" maxlength="$maxlength" value="$defaultvalue" />);
2765 elsif ( $tagslib->{$tag}->{$subfield}->{'hidden'} ) { # FIXME: shouldn't input type be "hidden" ?
2766 $subfield_data{marc_value} = qq(<input type="text" tabindex="1" id="$subfield_data{id}" name="field_value" class="input_marceditor" size="50" maxlength="$maxlength" value="$defaultvalue" />);
2768 elsif ( length($defaultvalue) > 100
2769 or (C4::Context->preference("marcflavour") eq "UNIMARC" and
2770 300 <= $tag && $tag < 400 && $subfield eq 'a' )
2771 or (C4::Context->preference("marcflavour") eq "MARC21" and
2772 500 <= $tag && $tag < 600 )
2774 # oversize field (textarea)
2775 $subfield_data{marc_value} = qq(<textarea tabindex="1" id="$subfield_data{id}" name="field_value" class="input_marceditor" size="50" maxlength="$maxlength">$defaultvalue</textarea>\n");
2777 $subfield_data{marc_value} = "<input type=\"text\" name=\"field_value\" value=\"$defaultvalue\" size=\"50\" maxlength=\"$maxlength\" />";
2779 push( @loop_data, \%subfield_data );
2784 if ( $itemrecord && $itemrecord->field($itemtagfield) ) {
2785 $itemnumber = $itemrecord->subfield( $itemtagfield, $itemtagsubfield );
2788 'itemtagfield' => $itemtagfield,
2789 'itemtagsubfield' => $itemtagsubfield,
2790 'itemnumber' => $itemnumber,
2791 'iteminformation' => \@loop_data
2795 sub ToggleNewStatus {
2796 my ( $params ) = @_;
2797 my @rules = @{ $params->{rules} };
2798 my $report_only = $params->{report_only};
2800 my $dbh = C4::Context->dbh;
2802 my @item_columns = map { "items.$_" } Koha::Items->columns;
2803 my @biblioitem_columns = map { "biblioitems.$_" } Koha::Biblioitems->columns;
2805 for my $rule ( @rules ) {
2806 my $age = $rule->{age};
2807 my $conditions = $rule->{conditions};
2808 my $substitutions = $rule->{substitutions};
2812 SELECT items.biblionumber, items.itemnumber
2814 LEFT JOIN biblioitems ON biblioitems.biblionumber = items.biblionumber
2817 for my $condition ( @$conditions ) {
2819 grep {/^$condition->{field}$/} @item_columns
2820 or grep {/^$condition->{field}$/} @biblioitem_columns
2822 if ( $condition->{value} =~ /\|/ ) {
2823 my @values = split /\|/, $condition->{value};
2824 $query .= qq| AND $condition->{field} IN (|
2825 . join( ',', ('?') x scalar @values )
2827 push @params, @values;
2829 $query .= qq| AND $condition->{field} = ?|;
2830 push @params, $condition->{value};
2834 if ( defined $age ) {
2835 $query .= q| AND TO_DAYS(NOW()) - TO_DAYS(dateaccessioned) >= ? |;
2838 my $sth = $dbh->prepare($query);
2839 $sth->execute( @params );
2840 while ( my $values = $sth->fetchrow_hashref ) {
2841 my $biblionumber = $values->{biblionumber};
2842 my $itemnumber = $values->{itemnumber};
2843 my $item = C4::Items::GetItem( $itemnumber );
2844 for my $substitution ( @$substitutions ) {
2845 next unless $substitution->{field};
2846 C4::Items::ModItem( {$substitution->{field} => $substitution->{value}}, $biblionumber, $itemnumber )
2847 unless $report_only;
2848 push @{ $report->{$itemnumber} }, $substitution;