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 # Has no framework parameter anymore, since Default is authoritative
445 # for Koha to MARC mappings.
447 my $cache = Koha::Caches->get_instance();
448 my $cache_key = "default_value_for_mod_marc-";
449 my $cached = $cache->get_from_cache($cache_key);
450 return $cached if $cached;
452 my $default_values = {
454 booksellerid => undef,
456 'items.cn_source' => undef,
457 coded_location_qualifier => undef,
461 holdingbranch => undef,
463 itemcallnumber => undef,
466 itemnotes_nonpublic => undef,
469 permanent_location => undef,
473 # paidfor => undef, # commented, see bug 12817
475 replacementprice => undef,
476 replacementpricedate => undef,
479 stocknumber => undef,
483 my %default_values_for_mod_from_marc;
484 while ( my ( $field, $default_value ) = each %$default_values ) {
485 my $kohafield = $field;
486 $kohafield =~ s|^([^\.]+)$|items.$1|;
487 $default_values_for_mod_from_marc{$field} = $default_value
488 if C4::Biblio::GetMarcFromKohaField( $kohafield );
491 $cache->set_in_cache($cache_key, \%default_values_for_mod_from_marc);
492 return \%default_values_for_mod_from_marc;
495 sub ModItemFromMarc {
496 my $item_marc = shift;
497 my $biblionumber = shift;
498 my $itemnumber = shift;
500 my $dbh = C4::Context->dbh;
501 my $frameworkcode = C4::Biblio::GetFrameworkCode($biblionumber);
502 my ( $itemtag, $itemsubfield ) = C4::Biblio::GetMarcFromKohaField( "items.itemnumber", $frameworkcode );
504 my $localitemmarc = MARC::Record->new;
505 $localitemmarc->append_fields( $item_marc->field($itemtag) );
506 my $item = &TransformMarcToKoha( $localitemmarc, $frameworkcode, 'items' );
507 my $default_values = _build_default_values_for_mod_marc();
508 foreach my $item_field ( keys %$default_values ) {
509 $item->{$item_field} = $default_values->{$item_field}
510 unless exists $item->{$item_field};
512 my $unlinked_item_subfields = _get_unlinked_item_subfields( $localitemmarc, $frameworkcode );
514 ModItem($item, $biblionumber, $itemnumber, $dbh, $frameworkcode, $unlinked_item_subfields);
520 ModItem({ column => $newvalue }, $biblionumber, $itemnumber, $log_action );
522 Change one or more columns in an item record and update
523 the MARC representation of the item.
525 The first argument is a hashref mapping from item column
526 names to the new values. The second and third arguments
527 are the biblionumber and itemnumber, respectively.
529 The fourth, optional parameter, C<$unlinked_item_subfields>, contains
530 an arrayref containing subfields present in the original MARC
531 representation of the item (e.g., from the item editor) that are
532 not mapped to C<items> columns directly but should instead
533 be stored in C<items.more_subfields_xml> and included in
534 the biblio items tag for display and indexing.
536 If one of the changed columns is used to calculate
537 the derived value of a column such as C<items.cn_sort>,
538 this routine will perform the necessary calculation
541 If log_action is set to false, the action will not be logged.
542 If log_action is true or undefined, the action will be logged.
548 my $biblionumber = shift;
549 my $itemnumber = shift;
550 my $additional_params = shift;
552 my $log_action = $additional_params->{log_action} // 1;
554 # if $biblionumber is undefined, get it from the current item
555 unless (defined $biblionumber) {
556 $biblionumber = _get_single_item_column('biblionumber', $itemnumber);
559 my $dbh = @_ ? shift : C4::Context->dbh;
560 my $frameworkcode = @_ ? shift : C4::Biblio::GetFrameworkCode( $biblionumber );
562 my $unlinked_item_subfields;
564 $unlinked_item_subfields = shift;
565 $item->{'more_subfields_xml'} = _get_unlinked_subfields_xml($unlinked_item_subfields);
568 $item->{'itemnumber'} = $itemnumber or return;
570 my @fields = qw( itemlost withdrawn damaged );
572 # Only call GetItem if we need to set an "on" date field
573 if ( $item->{itemlost} || $item->{withdrawn} || $item->{damaged} ) {
574 my $pre_mod_item = GetItem( $item->{'itemnumber'} );
575 for my $field (@fields) {
576 if ( defined( $item->{$field} )
577 and not $pre_mod_item->{$field}
578 and $item->{$field} )
580 $item->{ $field . '_on' } =
581 DateTime::Format::MySQL->format_datetime( dt_from_string() );
586 # If the field is defined but empty, we are removing and,
587 # and thus need to clear out the 'on' field as well
588 for my $field (@fields) {
589 if ( defined( $item->{$field} ) && !$item->{$field} ) {
590 $item->{ $field . '_on' } = undef;
595 _set_derived_columns_for_mod($item);
596 _do_column_fixes_for_mod($item);
599 # attempt to change itemnumber
600 # attempt to change biblionumber (if we want
601 # an API to relink an item to a different bib,
602 # it should be a separate function)
605 _koha_modify_item($item);
607 # request that bib be reindexed so that searching on current
608 # item status is possible
609 ModZebra( $biblionumber, "specialUpdate", "biblioserver" );
611 logaction( "CATALOGUING", "MODIFY", $itemnumber, "item " . Dumper($item) )
612 if $log_action && C4::Context->preference("CataloguingLog");
615 =head2 ModItemTransfer
617 ModItemTransfer($itenumber, $frombranch, $tobranch);
619 Marks an item as being transferred from one branch
624 sub ModItemTransfer {
625 my ( $itemnumber, $frombranch, $tobranch ) = @_;
627 my $dbh = C4::Context->dbh;
629 # Remove the 'shelving cart' location status if it is being used.
630 CartToShelf( $itemnumber ) if ( C4::Context->preference("ReturnToShelvingCart") );
632 $dbh->do("UPDATE branchtransfers SET datearrived = NOW(), comments = ? WHERE itemnumber = ? AND datearrived IS NULL", undef, "Canceled, new transfer from $frombranch to $tobranch created", $itemnumber);
634 #new entry in branchtransfers....
635 my $sth = $dbh->prepare(
636 "INSERT INTO branchtransfers (itemnumber, frombranch, datesent, tobranch)
637 VALUES (?, ?, NOW(), ?)");
638 $sth->execute($itemnumber, $frombranch, $tobranch);
640 ModItem({ holdingbranch => $tobranch }, undef, $itemnumber);
641 ModDateLastSeen($itemnumber);
645 =head2 ModDateLastSeen
647 ModDateLastSeen($itemnum);
649 Mark item as seen. Is called when an item is issued, returned or manually marked during inventory/stocktaking.
650 C<$itemnum> is the item number
654 sub ModDateLastSeen {
655 my ($itemnumber) = @_;
657 my $today = output_pref({ dt => dt_from_string, dateformat => 'iso', dateonly => 1 });
658 ModItem( { itemlost => 0, datelastseen => $today }, undef, $itemnumber, { log_action => 0 } );
663 DelItem({ itemnumber => $itemnumber, [ biblionumber => $biblionumber ] } );
665 Exported function (core API) for deleting an item record in Koha.
672 my $itemnumber = $params->{itemnumber};
673 my $biblionumber = $params->{biblionumber};
675 unless ($biblionumber) {
676 my $item = Koha::Items->find( $itemnumber );
677 $biblionumber = $item ? $item->biblio->biblionumber : undef;
680 # If there is no biblionumber for the given itemnumber, there is nothing to delete
681 return 0 unless $biblionumber;
683 # FIXME check the item has no current issues
684 my $deleted = _koha_delete_item( $itemnumber );
686 ModZebra( $biblionumber, "specialUpdate", "biblioserver" );
688 #search item field code
689 logaction("CATALOGUING", "DELETE", $itemnumber, "item") if C4::Context->preference("CataloguingLog");
693 =head2 CheckItemPreSave
695 my $item_ref = TransformMarcToKoha($marc, 'items');
697 my %errors = CheckItemPreSave($item_ref);
698 if (exists $errors{'duplicate_barcode'}) {
699 print "item has duplicate barcode: ", $errors{'duplicate_barcode'}, "\n";
700 } elsif (exists $errors{'invalid_homebranch'}) {
701 print "item has invalid home branch: ", $errors{'invalid_homebranch'}, "\n";
702 } elsif (exists $errors{'invalid_holdingbranch'}) {
703 print "item has invalid holding branch: ", $errors{'invalid_holdingbranch'}, "\n";
708 Given a hashref containing item fields, determine if it can be
709 inserted or updated in the database. Specifically, checks for
710 database integrity issues, and returns a hash containing any
711 of the following keys, if applicable.
715 =item duplicate_barcode
717 Barcode, if it duplicates one already found in the database.
719 =item invalid_homebranch
721 Home branch, if not defined in branches table.
723 =item invalid_holdingbranch
725 Holding branch, if not defined in branches table.
729 This function does NOT implement any policy-related checks,
730 e.g., whether current operator is allowed to save an
731 item that has a given branch code.
735 sub CheckItemPreSave {
736 my $item_ref = shift;
740 # check for duplicate barcode
741 if (exists $item_ref->{'barcode'} and defined $item_ref->{'barcode'}) {
742 my $existing_itemnumber = GetItemnumberFromBarcode($item_ref->{'barcode'});
743 if ($existing_itemnumber) {
744 if (!exists $item_ref->{'itemnumber'} # new item
745 or $item_ref->{'itemnumber'} != $existing_itemnumber) { # existing item
746 $errors{'duplicate_barcode'} = $item_ref->{'barcode'};
751 # check for valid home branch
752 if (exists $item_ref->{'homebranch'} and defined $item_ref->{'homebranch'}) {
753 my $home_library = Koha::Libraries->find( $item_ref->{homebranch} );
754 unless (defined $home_library) {
755 $errors{'invalid_homebranch'} = $item_ref->{'homebranch'};
759 # check for valid holding branch
760 if (exists $item_ref->{'holdingbranch'} and defined $item_ref->{'holdingbranch'}) {
761 my $holding_library = Koha::Libraries->find( $item_ref->{holdingbranch} );
762 unless (defined $holding_library) {
763 $errors{'invalid_holdingbranch'} = $item_ref->{'holdingbranch'};
771 =head1 EXPORTED SPECIAL ACCESSOR FUNCTIONS
773 The following functions provide various ways of
774 getting an item record, a set of item records, or
775 lists of authorized values for certain item fields.
777 Some of the functions in this group are candidates
778 for refactoring -- for example, some of the code
779 in C<GetItemsByBiblioitemnumber> and C<GetItemsInfo>
780 has copy-and-paste work.
784 =head2 GetItemsForInventory
786 ($itemlist, $iTotalRecords) = GetItemsForInventory( {
787 minlocation => $minlocation,
788 maxlocation => $maxlocation,
789 location => $location,
790 itemtype => $itemtype,
791 ignoreissued => $ignoreissued,
792 datelastseen => $datelastseen,
793 branchcode => $branchcode,
797 statushash => $statushash,
800 Retrieve a list of title/authors/barcode/callnumber, for biblio inventory.
802 The sub returns a reference to a list of hashes, each containing
803 itemnumber, author, title, barcode, item callnumber, and date last
804 seen. It is ordered by callnumber then title.
806 The required minlocation & maxlocation parameters are used to specify a range of item callnumbers
807 the datelastseen can be used to specify that you want to see items not seen since a past date only.
808 offset & size can be used to retrieve only a part of the whole listing (defaut behaviour)
809 $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.
811 $iTotalRecords is the number of rows that would have been returned without the $offset, $size limit clause
815 sub GetItemsForInventory {
816 my ( $parameters ) = @_;
817 my $minlocation = $parameters->{'minlocation'} // '';
818 my $maxlocation = $parameters->{'maxlocation'} // '';
819 my $location = $parameters->{'location'} // '';
820 my $itemtype = $parameters->{'itemtype'} // '';
821 my $ignoreissued = $parameters->{'ignoreissued'} // '';
822 my $datelastseen = $parameters->{'datelastseen'} // '';
823 my $branchcode = $parameters->{'branchcode'} // '';
824 my $branch = $parameters->{'branch'} // '';
825 my $offset = $parameters->{'offset'} // '';
826 my $size = $parameters->{'size'} // '';
827 my $statushash = $parameters->{'statushash'} // '';
829 my $dbh = C4::Context->dbh;
830 my ( @bind_params, @where_strings );
832 my $select_columns = q{
833 SELECT items.itemnumber, barcode, itemcallnumber, title, author, biblio.biblionumber, biblio.frameworkcode, datelastseen, homebranch, location, notforloan, damaged, itemlost, withdrawn, stocknumber
835 my $select_count = q{SELECT COUNT(*)};
838 LEFT JOIN biblio ON items.biblionumber = biblio.biblionumber
839 LEFT JOIN biblioitems on items.biblionumber = biblioitems.biblionumber
842 for my $authvfield (keys %$statushash){
843 if ( scalar @{$statushash->{$authvfield}} > 0 ){
844 my $joinedvals = join ',', @{$statushash->{$authvfield}};
845 push @where_strings, "$authvfield in (" . $joinedvals . ")";
851 push @where_strings, 'itemcallnumber >= ?';
852 push @bind_params, $minlocation;
856 push @where_strings, 'itemcallnumber <= ?';
857 push @bind_params, $maxlocation;
861 $datelastseen = output_pref({ str => $datelastseen, dateformat => 'iso', dateonly => 1 });
862 push @where_strings, '(datelastseen < ? OR datelastseen IS NULL)';
863 push @bind_params, $datelastseen;
867 push @where_strings, 'items.location = ?';
868 push @bind_params, $location;
872 if($branch eq "homebranch"){
873 push @where_strings, 'items.homebranch = ?';
875 push @where_strings, 'items.holdingbranch = ?';
877 push @bind_params, $branchcode;
881 push @where_strings, 'biblioitems.itemtype = ?';
882 push @bind_params, $itemtype;
885 if ( $ignoreissued) {
886 $query .= "LEFT JOIN issues ON items.itemnumber = issues.itemnumber ";
887 push @where_strings, 'issues.date_due IS NULL';
890 if ( @where_strings ) {
892 $query .= join ' AND ', @where_strings;
894 my $count_query = $select_count . $query;
895 $query .= ' ORDER BY items.cn_sort, itemcallnumber, title';
896 $query .= " LIMIT $offset, $size" if ($offset and $size);
897 $query = $select_columns . $query;
898 my $sth = $dbh->prepare($query);
899 $sth->execute( @bind_params );
902 my $tmpresults = $sth->fetchall_arrayref({});
903 $sth = $dbh->prepare( $count_query );
904 $sth->execute( @bind_params );
905 my ($iTotalRecords) = $sth->fetchrow_array();
907 my @avs = Koha::AuthorisedValues->search(
908 { 'marc_subfield_structures.kohafield' => { '>' => '' },
909 'me.authorised_value' => { '>' => '' },
911 { join => { category => 'marc_subfield_structures' },
912 distinct => ['marc_subfield_structures.kohafield, me.category, frameworkcode, me.authorised_value'],
913 '+select' => [ 'marc_subfield_structures.kohafield', 'marc_subfield_structures.frameworkcode', 'me.authorised_value', 'me.lib' ],
914 '+as' => [ 'kohafield', 'frameworkcode', 'authorised_value', 'lib' ],
918 my $avmapping = { map { $_->get_column('kohafield') . ',' . $_->get_column('frameworkcode') . ',' . $_->get_column('authorised_value') => $_->get_column('lib') } @avs };
920 foreach my $row (@$tmpresults) {
923 foreach (keys %$row) {
924 if (defined($avmapping->{"items.$_,".$row->{'frameworkcode'}.",".$row->{$_}})) {
925 $row->{$_} = $avmapping->{"items.$_,".$row->{'frameworkcode'}.",".$row->{$_}};
931 return (\@results, $iTotalRecords);
934 =head2 GetItemsByBiblioitemnumber
936 GetItemsByBiblioitemnumber($biblioitemnumber);
938 Returns an arrayref of hashrefs suitable for use in a TMPL_LOOP
939 Called by C<C4::XISBN>
943 sub GetItemsByBiblioitemnumber {
944 my ( $bibitem ) = @_;
945 my $dbh = C4::Context->dbh;
946 my $sth = $dbh->prepare("SELECT * FROM items WHERE items.biblioitemnumber = ?") || die $dbh->errstr;
947 # Get all items attached to a biblioitem
950 $sth->execute($bibitem) || die $sth->errstr;
951 while ( my $data = $sth->fetchrow_hashref ) {
952 # Foreach item, get circulation information
953 my $sth2 = $dbh->prepare( "SELECT * FROM issues,borrowers
955 AND issues.borrowernumber = borrowers.borrowernumber"
957 $sth2->execute( $data->{'itemnumber'} );
958 if ( my $data2 = $sth2->fetchrow_hashref ) {
959 # if item is out, set the due date and who it is out too
960 $data->{'date_due'} = $data2->{'date_due'};
961 $data->{'cardnumber'} = $data2->{'cardnumber'};
962 $data->{'borrowernumber'} = $data2->{'borrowernumber'};
965 # set date_due to blank, so in the template we check itemlost, and withdrawn
966 $data->{'date_due'} = '';
968 # Find the last 3 people who borrowed this item.
969 my $query2 = "SELECT * FROM old_issues, borrowers WHERE itemnumber = ?
970 AND old_issues.borrowernumber = borrowers.borrowernumber
971 ORDER BY returndate desc,timestamp desc LIMIT 3";
972 $sth2 = $dbh->prepare($query2) || die $dbh->errstr;
973 $sth2->execute( $data->{'itemnumber'} ) || die $sth2->errstr;
975 while ( my $data2 = $sth2->fetchrow_hashref ) {
976 $data->{"timestamp$i2"} = $data2->{'timestamp'};
977 $data->{"card$i2"} = $data2->{'cardnumber'};
978 $data->{"borrower$i2"} = $data2->{'borrowernumber'};
981 push(@results,$data);
988 @results = GetItemsInfo($biblionumber);
990 Returns information about items with the given biblionumber.
992 C<GetItemsInfo> returns a list of references-to-hash. Each element
993 contains a number of keys. Most of them are attributes from the
994 C<biblio>, C<biblioitems>, C<items>, and C<itemtypes> tables in the
995 Koha database. Other keys include:
999 =item C<$data-E<gt>{branchname}>
1001 The name (not the code) of the branch to which the book belongs.
1003 =item C<$data-E<gt>{datelastseen}>
1005 This is simply C<items.datelastseen>, except that while the date is
1006 stored in YYYY-MM-DD format in the database, here it is converted to
1007 DD/MM/YYYY format. A NULL date is returned as C<//>.
1009 =item C<$data-E<gt>{datedue}>
1011 =item C<$data-E<gt>{class}>
1013 This is the concatenation of C<biblioitems.classification>, the book's
1014 Dewey code, and C<biblioitems.subclass>.
1016 =item C<$data-E<gt>{ocount}>
1018 I think this is the number of copies of the book available.
1020 =item C<$data-E<gt>{order}>
1022 If this is set, it is set to C<One Order>.
1029 my ( $biblionumber ) = @_;
1030 my $dbh = C4::Context->dbh;
1031 require C4::Languages;
1032 my $language = C4::Languages::getlanguage();
1038 biblioitems.itemtype,
1041 biblioitems.publicationyear,
1042 biblioitems.publishercode,
1043 biblioitems.volumedate,
1044 biblioitems.volumedesc,
1047 items.notforloan as itemnotforloan,
1048 issues.borrowernumber,
1049 issues.date_due as datedue,
1050 issues.onsite_checkout,
1051 borrowers.cardnumber,
1053 borrowers.firstname,
1054 borrowers.branchcode as bcode,
1056 serial.publisheddate,
1057 itemtypes.description,
1058 COALESCE( localization.translation, itemtypes.description ) AS translated_description,
1059 itemtypes.notforloan as notforloan_per_itemtype,
1063 holding.opac_info as holding_branch_opac_info,
1064 home.opac_info as home_branch_opac_info
1068 LEFT JOIN branches AS holding ON items.holdingbranch = holding.branchcode
1069 LEFT JOIN branches AS home ON items.homebranch=home.branchcode
1070 LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber
1071 LEFT JOIN biblioitems ON biblioitems.biblioitemnumber = items.biblioitemnumber
1072 LEFT JOIN issues USING (itemnumber)
1073 LEFT JOIN borrowers USING (borrowernumber)
1074 LEFT JOIN serialitems USING (itemnumber)
1075 LEFT JOIN serial USING (serialid)
1076 LEFT JOIN itemtypes ON itemtypes.itemtype = "
1077 . (C4::Context->preference('item-level_itypes') ? 'items.itype' : 'biblioitems.itemtype');
1079 LEFT JOIN localization ON itemtypes.itemtype = localization.code
1080 AND localization.entity = 'itemtypes'
1081 AND localization.lang = ?
1084 $query .= " WHERE items.biblionumber = ? ORDER BY home.branchname, items.enumchron, LPAD( items.copynumber, 8, '0' ), items.dateaccessioned DESC" ;
1085 my $sth = $dbh->prepare($query);
1086 $sth->execute($language, $biblionumber);
1091 my $userenv = C4::Context->userenv;
1092 my $want_not_same_branch = C4::Context->preference("IndependentBranches") && !C4::Context->IsSuperLibrarian();
1093 while ( my $data = $sth->fetchrow_hashref ) {
1094 if ( $data->{borrowernumber} && $want_not_same_branch) {
1095 $data->{'NOTSAMEBRANCH'} = $data->{'bcode'} ne $userenv->{branch};
1098 $serial ||= $data->{'serial'};
1101 # get notforloan complete status if applicable
1102 $descriptions = Koha::AuthorisedValues->get_description_by_koha_field({frameworkcode => $data->{frameworkcode}, kohafield => 'items.notforloan', authorised_value => $data->{itemnotforloan} });
1103 $data->{notforloanvalue} = $descriptions->{lib} // '';
1104 $data->{notforloanvalueopac} = $descriptions->{opac_description} // '';
1106 # get restricted status and description if applicable
1107 $descriptions = Koha::AuthorisedValues->get_description_by_koha_field({frameworkcode => $data->{frameworkcode}, kohafield => 'items.restricted', authorised_value => $data->{restricted} });
1108 $data->{restricted} = $descriptions->{lib} // '';
1109 $data->{restrictedopac} = $descriptions->{opac_description} // '';
1111 # my stack procedures
1112 $descriptions = Koha::AuthorisedValues->get_description_by_koha_field({frameworkcode => $data->{frameworkcode}, kohafield => 'items.stack', authorised_value => $data->{stack} });
1113 $data->{stack} = $descriptions->{lib} // '';
1115 # Find the last 3 people who borrowed this item.
1116 my $sth2 = $dbh->prepare("SELECT * FROM old_issues,borrowers
1117 WHERE itemnumber = ?
1118 AND old_issues.borrowernumber = borrowers.borrowernumber
1119 ORDER BY returndate DESC
1121 $sth2->execute($data->{'itemnumber'});
1123 while (my $data2 = $sth2->fetchrow_hashref()) {
1124 $data->{"timestamp$ii"} = $data2->{'timestamp'} if $data2->{'timestamp'};
1125 $data->{"card$ii"} = $data2->{'cardnumber'} if $data2->{'cardnumber'};
1126 $data->{"borrower$ii"} = $data2->{'borrowernumber'} if $data2->{'borrowernumber'};
1130 $results[$i] = $data;
1135 ? sort { ($b->{'publisheddate'} || $b->{'enumchron'}) cmp ($a->{'publisheddate'} || $a->{'enumchron'}) } @results
1139 =head2 GetItemsLocationInfo
1141 my @itemlocinfo = GetItemsLocationInfo($biblionumber);
1143 Returns the branch names, shelving location and itemcallnumber for each item attached to the biblio in question
1145 C<GetItemsInfo> returns a list of references-to-hash. Data returned:
1149 =item C<$data-E<gt>{homebranch}>
1151 Branch Name of the item's homebranch
1153 =item C<$data-E<gt>{holdingbranch}>
1155 Branch Name of the item's holdingbranch
1157 =item C<$data-E<gt>{location}>
1159 Item's shelving location code
1161 =item C<$data-E<gt>{location_intranet}>
1163 The intranet description for the Shelving Location as set in authorised_values 'LOC'
1165 =item C<$data-E<gt>{location_opac}>
1167 The OPAC description for the Shelving Location as set in authorised_values 'LOC'. Falls back to intranet description if no OPAC
1170 =item C<$data-E<gt>{itemcallnumber}>
1172 Item's itemcallnumber
1174 =item C<$data-E<gt>{cn_sort}>
1176 Item's call number normalized for sorting
1182 sub GetItemsLocationInfo {
1183 my $biblionumber = shift;
1186 my $dbh = C4::Context->dbh;
1187 my $query = "SELECT a.branchname as homebranch, b.branchname as holdingbranch,
1188 location, itemcallnumber, cn_sort
1189 FROM items, branches as a, branches as b
1190 WHERE homebranch = a.branchcode AND holdingbranch = b.branchcode
1191 AND biblionumber = ?
1192 ORDER BY cn_sort ASC";
1193 my $sth = $dbh->prepare($query);
1194 $sth->execute($biblionumber);
1196 while ( my $data = $sth->fetchrow_hashref ) {
1197 my $av = Koha::AuthorisedValues->search({ category => 'LOC', authorised_value => $data->{location} });
1198 $av = $av->count ? $av->next : undef;
1199 $data->{location_intranet} = $av ? $av->lib : '';
1200 $data->{location_opac} = $av ? $av->opac_description : '';
1201 push @results, $data;
1206 =head2 GetHostItemsInfo
1208 $hostiteminfo = GetHostItemsInfo($hostfield);
1209 Returns the iteminfo for items linked to records via a host field
1213 sub GetHostItemsInfo {
1215 my @returnitemsInfo;
1217 if (C4::Context->preference('marcflavour') eq 'MARC21' ||
1218 C4::Context->preference('marcflavour') eq 'NORMARC'){
1219 foreach my $hostfield ( $record->field('773') ) {
1220 my $hostbiblionumber = $hostfield->subfield("0");
1221 my $linkeditemnumber = $hostfield->subfield("9");
1222 my @hostitemInfos = GetItemsInfo($hostbiblionumber);
1223 foreach my $hostitemInfo (@hostitemInfos){
1224 if ($hostitemInfo->{itemnumber} eq $linkeditemnumber){
1225 push (@returnitemsInfo,$hostitemInfo);
1230 } elsif ( C4::Context->preference('marcflavour') eq 'UNIMARC'){
1231 foreach my $hostfield ( $record->field('461') ) {
1232 my $hostbiblionumber = $hostfield->subfield("0");
1233 my $linkeditemnumber = $hostfield->subfield("9");
1234 my @hostitemInfos = GetItemsInfo($hostbiblionumber);
1235 foreach my $hostitemInfo (@hostitemInfos){
1236 if ($hostitemInfo->{itemnumber} eq $linkeditemnumber){
1237 push (@returnitemsInfo,$hostitemInfo);
1243 return @returnitemsInfo;
1247 =head2 GetLastAcquisitions
1249 my $lastacq = GetLastAcquisitions({'branches' => ('branch1','branch2'),
1250 'itemtypes' => ('BK','BD')}, 10);
1254 sub GetLastAcquisitions {
1255 my ($data,$max) = @_;
1257 my $itemtype = C4::Context->preference('item-level_itypes') ? 'itype' : 'itemtype';
1259 my $number_of_branches = @{$data->{branches}};
1260 my $number_of_itemtypes = @{$data->{itemtypes}};
1263 my @where = ('WHERE 1 ');
1264 $number_of_branches and push @where
1265 , 'AND holdingbranch IN ('
1266 , join(',', ('?') x $number_of_branches )
1270 $number_of_itemtypes and push @where
1271 , "AND $itemtype IN ("
1272 , join(',', ('?') x $number_of_itemtypes )
1276 my $query = "SELECT biblio.biblionumber as biblionumber, title, dateaccessioned
1277 FROM items RIGHT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
1278 RIGHT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber)
1280 GROUP BY biblio.biblionumber
1281 ORDER BY dateaccessioned DESC LIMIT $max";
1283 my $dbh = C4::Context->dbh;
1284 my $sth = $dbh->prepare($query);
1286 $sth->execute((@{$data->{branches}}, @{$data->{itemtypes}}));
1289 while( my $row = $sth->fetchrow_hashref){
1290 push @results, {date => $row->{dateaccessioned}
1291 , biblionumber => $row->{biblionumber}
1292 , title => $row->{title}};
1298 =head2 GetItemnumbersForBiblio
1300 my $itemnumbers = GetItemnumbersForBiblio($biblionumber);
1302 Given a single biblionumber, return an arrayref of all the corresponding itemnumbers
1306 sub GetItemnumbersForBiblio {
1307 my $biblionumber = shift;
1309 my $dbh = C4::Context->dbh;
1310 my $sth = $dbh->prepare("SELECT itemnumber FROM items WHERE biblionumber = ?");
1311 $sth->execute($biblionumber);
1312 while (my $result = $sth->fetchrow_hashref) {
1313 push @items, $result->{'itemnumber'};
1318 =head2 get_hostitemnumbers_of
1320 my @itemnumbers_of = get_hostitemnumbers_of($biblionumber);
1322 Given a biblionumber, return the list of corresponding itemnumbers that are linked to it via host fields
1324 Return a reference on a hash where key is a biblionumber and values are
1325 references on array of itemnumbers.
1330 sub get_hostitemnumbers_of {
1331 my ($biblionumber) = @_;
1332 my $marcrecord = C4::Biblio::GetMarcBiblio({ biblionumber => $biblionumber });
1334 return unless $marcrecord;
1336 my ( @returnhostitemnumbers, $tag, $biblio_s, $item_s );
1338 my $marcflavor = C4::Context->preference('marcflavour');
1339 if ( $marcflavor eq 'MARC21' || $marcflavor eq 'NORMARC' ) {
1344 elsif ( $marcflavor eq 'UNIMARC' ) {
1350 foreach my $hostfield ( $marcrecord->field($tag) ) {
1351 my $hostbiblionumber = $hostfield->subfield($biblio_s);
1352 next unless $hostbiblionumber; # have tag, don't have $biblio_s subfield
1353 my $linkeditemnumber = $hostfield->subfield($item_s);
1354 if ( ! $linkeditemnumber ) {
1355 warn "ERROR biblionumber $biblionumber has 773^0, but doesn't have 9";
1358 my $is_from_biblio = Koha::Items->search({ itemnumber => $linkeditemnumber, biblionumber => $hostbiblionumber });
1359 push @returnhostitemnumbers, $linkeditemnumber
1363 return @returnhostitemnumbers;
1367 =head2 GetItemnumberFromBarcode
1369 $result = GetItemnumberFromBarcode($barcode);
1373 sub GetItemnumberFromBarcode {
1375 my $dbh = C4::Context->dbh;
1378 $dbh->prepare("SELECT itemnumber FROM items WHERE items.barcode=?");
1379 $rq->execute($barcode);
1380 my ($result) = $rq->fetchrow;
1384 =head2 GetBarcodeFromItemnumber
1386 $result = GetBarcodeFromItemnumber($itemnumber);
1390 sub GetBarcodeFromItemnumber {
1391 my ($itemnumber) = @_;
1392 my $dbh = C4::Context->dbh;
1395 $dbh->prepare("SELECT barcode FROM items WHERE items.itemnumber=?");
1396 $rq->execute($itemnumber);
1397 my ($result) = $rq->fetchrow;
1401 =head2 GetHiddenItemnumbers
1403 my @itemnumbers_to_hide = GetHiddenItemnumbers(@items);
1405 Given a list of items it checks which should be hidden from the OPAC given
1406 the current configuration. Returns a list of itemnumbers corresponding to
1407 those that should be hidden.
1411 sub GetHiddenItemnumbers {
1415 my $yaml = C4::Context->preference('OpacHiddenItems');
1416 return () if (! $yaml =~ /\S/ );
1417 $yaml = "$yaml\n\n"; # YAML is anal on ending \n. Surplus does not hurt
1420 $hidingrules = YAML::Load($yaml);
1423 warn "Unable to parse OpacHiddenItems syspref : $@";
1426 my $dbh = C4::Context->dbh;
1429 foreach my $item (@items) {
1431 # We check each rule
1432 foreach my $field (keys %$hidingrules) {
1434 if (exists $item->{$field}) {
1435 $val = $item->{$field};
1438 my $query = "SELECT $field from items where itemnumber = ?";
1439 $val = $dbh->selectrow_array($query, undef, $item->{'itemnumber'});
1441 $val = '' unless defined $val;
1443 # If the results matches the values in the yaml file
1444 if (any { $val eq $_ } @{$hidingrules->{$field}}) {
1446 # We add the itemnumber to the list
1447 push @resultitems, $item->{'itemnumber'};
1449 # If at least one rule matched for an item, no need to test the others
1454 return @resultitems;
1457 =head1 LIMITED USE FUNCTIONS
1459 The following functions, while part of the public API,
1460 are not exported. This is generally because they are
1461 meant to be used by only one script for a specific
1462 purpose, and should not be used in any other context
1463 without careful thought.
1469 my $item_marc = GetMarcItem($biblionumber, $itemnumber);
1471 Returns MARC::Record of the item passed in parameter.
1472 This function is meant for use only in C<cataloguing/additem.pl>,
1473 where it is needed to support that script's MARC-like
1479 my ( $biblionumber, $itemnumber ) = @_;
1481 # GetMarcItem has been revised so that it does the following:
1482 # 1. Gets the item information from the items table.
1483 # 2. Converts it to a MARC field for storage in the bib record.
1485 # The previous behavior was:
1486 # 1. Get the bib record.
1487 # 2. Return the MARC tag corresponding to the item record.
1489 # The difference is that one treats the items row as authoritative,
1490 # while the other treats the MARC representation as authoritative
1491 # under certain circumstances.
1493 my $itemrecord = GetItem($itemnumber);
1495 # Tack on 'items.' prefix to column names so that C4::Biblio::TransformKohaToMarc will work.
1496 # Also, don't emit a subfield if the underlying field is blank.
1499 return Item2Marc($itemrecord,$biblionumber);
1503 my ($itemrecord,$biblionumber)=@_;
1506 defined($itemrecord->{$_}) && $itemrecord->{$_} ne '' ? ("items.$_" => $itemrecord->{$_}) : ()
1507 } keys %{ $itemrecord }
1509 my $framework = C4::Biblio::GetFrameworkCode( $biblionumber );
1510 my $itemmarc = C4::Biblio::TransformKohaToMarc(
1511 $mungeditem, { no_split => 1},
1513 my ( $itemtag, $itemsubfield ) = C4::Biblio::GetMarcFromKohaField(
1514 "items.itemnumber", $framework,
1517 my $unlinked_item_subfields = _parse_unlinked_item_subfields_from_xml($mungeditem->{'items.more_subfields_xml'});
1518 if (defined $unlinked_item_subfields and $#$unlinked_item_subfields > -1) {
1519 foreach my $field ($itemmarc->field($itemtag)){
1520 $field->add_subfields(@$unlinked_item_subfields);
1526 =head1 PRIVATE FUNCTIONS AND VARIABLES
1528 The following functions are not meant to be called
1529 directly, but are documented in order to explain
1530 the inner workings of C<C4::Items>.
1534 =head2 %derived_columns
1536 This hash keeps track of item columns that
1537 are strictly derived from other columns in
1538 the item record and are not meant to be set
1541 Each key in the hash should be the name of a
1542 column (as named by TransformMarcToKoha). Each
1543 value should be hashref whose keys are the
1544 columns on which the derived column depends. The
1545 hashref should also contain a 'BUILDER' key
1546 that is a reference to a sub that calculates
1551 my %derived_columns = (
1552 'items.cn_sort' => {
1553 'itemcallnumber' => 1,
1554 'items.cn_source' => 1,
1555 'BUILDER' => \&_calc_items_cn_sort,
1559 =head2 _set_derived_columns_for_add
1561 _set_derived_column_for_add($item);
1563 Given an item hash representing a new item to be added,
1564 calculate any derived columns. Currently the only
1565 such column is C<items.cn_sort>.
1569 sub _set_derived_columns_for_add {
1572 foreach my $column (keys %derived_columns) {
1573 my $builder = $derived_columns{$column}->{'BUILDER'};
1574 my $source_values = {};
1575 foreach my $source_column (keys %{ $derived_columns{$column} }) {
1576 next if $source_column eq 'BUILDER';
1577 $source_values->{$source_column} = $item->{$source_column};
1579 $builder->($item, $source_values);
1583 =head2 _set_derived_columns_for_mod
1585 _set_derived_column_for_mod($item);
1587 Given an item hash representing a new item to be modified.
1588 calculate any derived columns. Currently the only
1589 such column is C<items.cn_sort>.
1591 This routine differs from C<_set_derived_columns_for_add>
1592 in that it needs to handle partial item records. In other
1593 words, the caller of C<ModItem> may have supplied only one
1594 or two columns to be changed, so this function needs to
1595 determine whether any of the columns to be changed affect
1596 any of the derived columns. Also, if a derived column
1597 depends on more than one column, but the caller is not
1598 changing all of then, this routine retrieves the unchanged
1599 values from the database in order to ensure a correct
1604 sub _set_derived_columns_for_mod {
1607 foreach my $column (keys %derived_columns) {
1608 my $builder = $derived_columns{$column}->{'BUILDER'};
1609 my $source_values = {};
1610 my %missing_sources = ();
1611 my $must_recalc = 0;
1612 foreach my $source_column (keys %{ $derived_columns{$column} }) {
1613 next if $source_column eq 'BUILDER';
1614 if (exists $item->{$source_column}) {
1616 $source_values->{$source_column} = $item->{$source_column};
1618 $missing_sources{$source_column} = 1;
1622 foreach my $source_column (keys %missing_sources) {
1623 $source_values->{$source_column} = _get_single_item_column($source_column, $item->{'itemnumber'});
1625 $builder->($item, $source_values);
1630 =head2 _do_column_fixes_for_mod
1632 _do_column_fixes_for_mod($item);
1634 Given an item hashref containing one or more
1635 columns to modify, fix up certain values.
1636 Specifically, set to 0 any passed value
1637 of C<notforloan>, C<damaged>, C<itemlost>, or
1638 C<withdrawn> that is either undefined or
1639 contains the empty string.
1643 sub _do_column_fixes_for_mod {
1646 if (exists $item->{'notforloan'} and
1647 (not defined $item->{'notforloan'} or $item->{'notforloan'} eq '')) {
1648 $item->{'notforloan'} = 0;
1650 if (exists $item->{'damaged'} and
1651 (not defined $item->{'damaged'} or $item->{'damaged'} eq '')) {
1652 $item->{'damaged'} = 0;
1654 if (exists $item->{'itemlost'} and
1655 (not defined $item->{'itemlost'} or $item->{'itemlost'} eq '')) {
1656 $item->{'itemlost'} = 0;
1658 if (exists $item->{'withdrawn'} and
1659 (not defined $item->{'withdrawn'} or $item->{'withdrawn'} eq '')) {
1660 $item->{'withdrawn'} = 0;
1662 if (exists $item->{location}
1663 and $item->{location} ne 'CART'
1664 and $item->{location} ne 'PROC'
1665 and not $item->{permanent_location}
1667 $item->{'permanent_location'} = $item->{'location'};
1669 if (exists $item->{'timestamp'}) {
1670 delete $item->{'timestamp'};
1674 =head2 _get_single_item_column
1676 _get_single_item_column($column, $itemnumber);
1678 Retrieves the value of a single column from an C<items>
1679 row specified by C<$itemnumber>.
1683 sub _get_single_item_column {
1685 my $itemnumber = shift;
1687 my $dbh = C4::Context->dbh;
1688 my $sth = $dbh->prepare("SELECT $column FROM items WHERE itemnumber = ?");
1689 $sth->execute($itemnumber);
1690 my ($value) = $sth->fetchrow();
1694 =head2 _calc_items_cn_sort
1696 _calc_items_cn_sort($item, $source_values);
1698 Helper routine to calculate C<items.cn_sort>.
1702 sub _calc_items_cn_sort {
1704 my $source_values = shift;
1706 $item->{'items.cn_sort'} = GetClassSort($source_values->{'items.cn_source'}, $source_values->{'itemcallnumber'}, "");
1709 =head2 _set_defaults_for_add
1711 _set_defaults_for_add($item_hash);
1713 Given an item hash representing an item to be added, set
1714 correct default values for columns whose default value
1715 is not handled by the DBMS. This includes the following
1722 C<items.dateaccessioned>
1744 sub _set_defaults_for_add {
1746 $item->{dateaccessioned} ||= output_pref({ dt => dt_from_string, dateformat => 'iso', dateonly => 1 });
1747 $item->{$_} ||= 0 for (qw( notforloan damaged itemlost withdrawn));
1750 =head2 _koha_new_item
1752 my ($itemnumber,$error) = _koha_new_item( $item, $barcode );
1754 Perform the actual insert into the C<items> table.
1758 sub _koha_new_item {
1759 my ( $item, $barcode ) = @_;
1760 my $dbh=C4::Context->dbh;
1762 $item->{permanent_location} //= $item->{location};
1763 _mod_item_dates( $item );
1765 "INSERT INTO items SET
1767 biblioitemnumber = ?,
1769 dateaccessioned = ?,
1773 replacementprice = ?,
1774 replacementpricedate = ?,
1775 datelastborrowed = ?,
1783 coded_location_qualifier = ?,
1786 itemnotes_nonpublic = ?,
1790 permanent_location = ?,
1802 more_subfields_xml = ?,
1807 my $sth = $dbh->prepare($query);
1808 my $today = output_pref({ dt => dt_from_string, dateformat => 'iso', dateonly => 1 });
1810 $item->{'biblionumber'},
1811 $item->{'biblioitemnumber'},
1813 $item->{'dateaccessioned'},
1814 $item->{'booksellerid'},
1815 $item->{'homebranch'},
1817 $item->{'replacementprice'},
1818 $item->{'replacementpricedate'} || $today,
1819 $item->{datelastborrowed},
1820 $item->{datelastseen} || $today,
1822 $item->{'notforloan'},
1824 $item->{'itemlost'},
1825 $item->{'withdrawn'},
1826 $item->{'itemcallnumber'},
1827 $item->{'coded_location_qualifier'},
1828 $item->{'restricted'},
1829 $item->{'itemnotes'},
1830 $item->{'itemnotes_nonpublic'},
1831 $item->{'holdingbranch'},
1833 $item->{'location'},
1834 $item->{'permanent_location'},
1837 $item->{'renewals'},
1838 $item->{'reserves'},
1839 $item->{'items.cn_source'},
1840 $item->{'items.cn_sort'},
1843 $item->{'materials'},
1845 $item->{'enumchron'},
1846 $item->{'more_subfields_xml'},
1847 $item->{'copynumber'},
1848 $item->{'stocknumber'},
1849 $item->{'new_status'},
1853 if ( defined $sth->errstr ) {
1854 $error.="ERROR in _koha_new_item $query".$sth->errstr;
1857 $itemnumber = $dbh->{'mysql_insertid'};
1860 return ( $itemnumber, $error );
1863 =head2 MoveItemFromBiblio
1865 MoveItemFromBiblio($itenumber, $frombiblio, $tobiblio);
1867 Moves an item from a biblio to another
1869 Returns undef if the move failed or the biblionumber of the destination record otherwise
1873 sub MoveItemFromBiblio {
1874 my ($itemnumber, $frombiblio, $tobiblio) = @_;
1875 my $dbh = C4::Context->dbh;
1876 my ( $tobiblioitem ) = $dbh->selectrow_array(q|
1877 SELECT biblioitemnumber
1879 WHERE biblionumber = ?
1880 |, undef, $tobiblio );
1881 my $return = $dbh->do(q|
1883 SET biblioitemnumber = ?,
1885 WHERE itemnumber = ?
1886 AND biblionumber = ?
1887 |, undef, $tobiblioitem, $tobiblio, $itemnumber, $frombiblio );
1889 ModZebra( $tobiblio, "specialUpdate", "biblioserver" );
1890 ModZebra( $frombiblio, "specialUpdate", "biblioserver" );
1891 # Checking if the item we want to move is in an order
1892 require C4::Acquisition;
1893 my $order = C4::Acquisition::GetOrderFromItemnumber($itemnumber);
1895 # Replacing the biblionumber within the order if necessary
1896 $order->{'biblionumber'} = $tobiblio;
1897 C4::Acquisition::ModOrder($order);
1900 # Update reserves, hold_fill_targets, tmp_holdsqueue and linktracker tables
1901 for my $table_name ( qw( reserves hold_fill_targets tmp_holdsqueue linktracker ) ) {
1904 SET biblionumber = ?
1905 WHERE itemnumber = ?
1906 |, undef, $tobiblio, $itemnumber );
1913 =head2 ItemSafeToDelete
1915 ItemSafeToDelete( $biblionumber, $itemnumber);
1917 Exported function (core API) for checking whether an item record is safe to delete.
1919 returns 1 if the item is safe to delete,
1921 "book_on_loan" if the item is checked out,
1923 "not_same_branch" if the item is blocked by independent branches,
1925 "book_reserved" if the there are holds aganst the item, or
1927 "linked_analytics" if the item has linked analytic records.
1931 sub ItemSafeToDelete {
1932 my ( $biblionumber, $itemnumber ) = @_;
1934 my $dbh = C4::Context->dbh;
1938 my $countanalytics = GetAnalyticsCount($itemnumber);
1940 # check that there is no issue on this item before deletion.
1941 my $sth = $dbh->prepare(
1943 SELECT COUNT(*) FROM issues
1944 WHERE itemnumber = ?
1947 $sth->execute($itemnumber);
1948 my ($onloan) = $sth->fetchrow;
1950 my $item = GetItem($itemnumber);
1953 $status = "book_on_loan";
1955 elsif ( defined C4::Context->userenv
1956 and !C4::Context->IsSuperLibrarian()
1957 and C4::Context->preference("IndependentBranches")
1958 and ( C4::Context->userenv->{branch} ne $item->{'homebranch'} ) )
1960 $status = "not_same_branch";
1963 # check it doesn't have a waiting reserve
1964 $sth = $dbh->prepare(
1966 SELECT COUNT(*) FROM reserves
1967 WHERE (found = 'W' OR found = 'T')
1971 $sth->execute($itemnumber);
1972 my ($reserve) = $sth->fetchrow;
1974 $status = "book_reserved";
1976 elsif ( $countanalytics > 0 ) {
1977 $status = "linked_analytics";
1988 DelItemCheck( $biblionumber, $itemnumber);
1990 Exported function (core API) for deleting an item record in Koha if there no current issue.
1992 DelItemCheck wraps ItemSafeToDelete around DelItem.
1997 my ( $biblionumber, $itemnumber ) = @_;
1998 my $status = ItemSafeToDelete( $biblionumber, $itemnumber );
2000 if ( $status == 1 ) {
2003 biblionumber => $biblionumber,
2004 itemnumber => $itemnumber
2011 =head2 _koha_modify_item
2013 my ($itemnumber,$error) =_koha_modify_item( $item );
2015 Perform the actual update of the C<items> row. Note that this
2016 routine accepts a hashref specifying the columns to update.
2020 sub _koha_modify_item {
2022 my $dbh=C4::Context->dbh;
2025 my $query = "UPDATE items SET ";
2027 _mod_item_dates( $item );
2028 for my $key ( keys %$item ) {
2029 next if ( $key eq 'itemnumber' );
2031 push @bind, $item->{$key};
2034 $query .= " WHERE itemnumber=?";
2035 push @bind, $item->{'itemnumber'};
2036 my $sth = $dbh->prepare($query);
2037 $sth->execute(@bind);
2039 $error.="ERROR in _koha_modify_item $query: ".$sth->errstr;
2042 return ($item->{'itemnumber'},$error);
2045 sub _mod_item_dates { # date formatting for date fields in item hash
2047 return if !$item || ref($item) ne 'HASH';
2050 { $_ =~ /^onloan$|^date|date$|datetime$/ }
2052 # Incl. dateaccessioned,replacementpricedate,datelastborrowed,datelastseen
2053 # NOTE: We do not (yet) have items fields ending with datetime
2054 # Fields with _on$ have been handled already
2056 foreach my $key ( @keys ) {
2057 next if !defined $item->{$key}; # skip undefs
2058 my $dt = eval { dt_from_string( $item->{$key} ) };
2059 # eval: dt_from_string will die on us if we pass illegal dates
2062 if( defined $dt && ref($dt) eq 'DateTime' ) {
2063 if( $key =~ /datetime/ ) {
2064 $newstr = DateTime::Format::MySQL->format_datetime($dt);
2066 $newstr = DateTime::Format::MySQL->format_date($dt);
2069 $item->{$key} = $newstr; # might be undef to clear garbage
2073 =head2 _koha_delete_item
2075 _koha_delete_item( $itemnum );
2077 Internal function to delete an item record from the koha tables
2081 sub _koha_delete_item {
2082 my ( $itemnum ) = @_;
2084 my $dbh = C4::Context->dbh;
2085 # save the deleted item to deleteditems table
2086 my $sth = $dbh->prepare("SELECT * FROM items WHERE itemnumber=?");
2087 $sth->execute($itemnum);
2088 my $data = $sth->fetchrow_hashref();
2090 # There is no item to delete
2091 return 0 unless $data;
2093 my $query = "INSERT INTO deleteditems SET ";
2095 foreach my $key ( keys %$data ) {
2096 next if ( $key eq 'timestamp' ); # timestamp will be set by db
2097 $query .= "$key = ?,";
2098 push( @bind, $data->{$key} );
2101 $sth = $dbh->prepare($query);
2102 $sth->execute(@bind);
2104 # delete from items table
2105 $sth = $dbh->prepare("DELETE FROM items WHERE itemnumber=?");
2106 my $deleted = $sth->execute($itemnum);
2107 return ( $deleted == 1 ) ? 1 : 0;
2110 =head2 _marc_from_item_hash
2112 my $item_marc = _marc_from_item_hash($item, $frameworkcode[, $unlinked_item_subfields]);
2114 Given an item hash representing a complete item record,
2115 create a C<MARC::Record> object containing an embedded
2116 tag representing that item.
2118 The third, optional parameter C<$unlinked_item_subfields> is
2119 an arrayref of subfields (not mapped to C<items> fields per the
2120 framework) to be added to the MARC representation
2125 sub _marc_from_item_hash {
2127 my $frameworkcode = shift;
2128 my $unlinked_item_subfields;
2130 $unlinked_item_subfields = shift;
2133 # Tack on 'items.' prefix to column names so lookup from MARC frameworks will work
2134 # Also, don't emit a subfield if the underlying field is blank.
2135 my $mungeditem = { map { (defined($item->{$_}) and $item->{$_} ne '') ?
2136 (/^items\./ ? ($_ => $item->{$_}) : ("items.$_" => $item->{$_}))
2137 : () } keys %{ $item } };
2139 my $item_marc = MARC::Record->new();
2140 foreach my $item_field ( keys %{$mungeditem} ) {
2141 my ( $tag, $subfield ) = C4::Biblio::GetMarcFromKohaField( $item_field, $frameworkcode );
2142 next unless defined $tag and defined $subfield; # skip if not mapped to MARC field
2143 my @values = split(/\s?\|\s?/, $mungeditem->{$item_field}, -1);
2144 foreach my $value (@values){
2145 if ( my $field = $item_marc->field($tag) ) {
2146 $field->add_subfields( $subfield => $value );
2148 my $add_subfields = [];
2149 if (defined $unlinked_item_subfields and ref($unlinked_item_subfields) eq 'ARRAY' and $#$unlinked_item_subfields > -1) {
2150 $add_subfields = $unlinked_item_subfields;
2152 $item_marc->add_fields( $tag, " ", " ", $subfield => $value, @$add_subfields );
2160 =head2 _repack_item_errors
2162 Add an error message hash generated by C<CheckItemPreSave>
2163 to a list of errors.
2167 sub _repack_item_errors {
2168 my $item_sequence_num = shift;
2169 my $item_ref = shift;
2170 my $error_ref = shift;
2172 my @repacked_errors = ();
2174 foreach my $error_code (sort keys %{ $error_ref }) {
2175 my $repacked_error = {};
2176 $repacked_error->{'item_sequence'} = $item_sequence_num;
2177 $repacked_error->{'item_barcode'} = exists($item_ref->{'barcode'}) ? $item_ref->{'barcode'} : '';
2178 $repacked_error->{'error_code'} = $error_code;
2179 $repacked_error->{'error_information'} = $error_ref->{$error_code};
2180 push @repacked_errors, $repacked_error;
2183 return @repacked_errors;
2186 =head2 _get_unlinked_item_subfields
2188 my $unlinked_item_subfields = _get_unlinked_item_subfields($original_item_marc, $frameworkcode);
2192 sub _get_unlinked_item_subfields {
2193 my $original_item_marc = shift;
2194 my $frameworkcode = shift;
2196 my $marcstructure = GetMarcStructure(1, $frameworkcode, { unsafe => 1 });
2198 # assume that this record has only one field, and that that
2199 # field contains only the item information
2201 my @fields = $original_item_marc->fields();
2202 if ($#fields > -1) {
2203 my $field = $fields[0];
2204 my $tag = $field->tag();
2205 foreach my $subfield ($field->subfields()) {
2206 if (defined $subfield->[1] and
2207 $subfield->[1] ne '' and
2208 !$marcstructure->{$tag}->{$subfield->[0]}->{'kohafield'}) {
2209 push @$subfields, $subfield->[0] => $subfield->[1];
2216 =head2 _get_unlinked_subfields_xml
2218 my $unlinked_subfields_xml = _get_unlinked_subfields_xml($unlinked_item_subfields);
2222 sub _get_unlinked_subfields_xml {
2223 my $unlinked_item_subfields = shift;
2226 if (defined $unlinked_item_subfields and ref($unlinked_item_subfields) eq 'ARRAY' and $#$unlinked_item_subfields > -1) {
2227 my $marc = MARC::Record->new();
2228 # use of tag 999 is arbitrary, and doesn't need to match the item tag
2229 # used in the framework
2230 $marc->append_fields(MARC::Field->new('999', ' ', ' ', @$unlinked_item_subfields));
2231 $marc->encoding("UTF-8");
2232 $xml = $marc->as_xml("USMARC");
2238 =head2 _parse_unlinked_item_subfields_from_xml
2240 my $unlinked_item_subfields = _parse_unlinked_item_subfields_from_xml($whole_item->{'more_subfields_xml'}):
2244 sub _parse_unlinked_item_subfields_from_xml {
2246 require C4::Charset;
2247 return unless defined $xml and $xml ne "";
2248 my $marc = MARC::Record->new_from_xml(C4::Charset::StripNonXmlChars($xml),'UTF-8');
2249 my $unlinked_subfields = [];
2250 my @fields = $marc->fields();
2251 if ($#fields > -1) {
2252 foreach my $subfield ($fields[0]->subfields()) {
2253 push @$unlinked_subfields, $subfield->[0] => $subfield->[1];
2256 return $unlinked_subfields;
2259 =head2 GetAnalyticsCount
2261 $count= &GetAnalyticsCount($itemnumber)
2263 counts Usage of itemnumber in Analytical bibliorecords.
2267 sub GetAnalyticsCount {
2268 my ($itemnumber) = @_;
2270 ### ZOOM search here
2272 $query= "hi=".$itemnumber;
2273 my $searcher = Koha::SearchEngine::Search->new({index => $Koha::SearchEngine::BIBLIOS_INDEX});
2274 my ($err,$res,$result) = $searcher->simple_search_compat($query,0,10);
2278 =head2 SearchItemsByField
2280 my $items = SearchItemsByField($field, $value);
2282 SearchItemsByField will search for items on a specific given field.
2283 For instance you can search all items with a specific stocknumber like this:
2285 my $items = SearchItemsByField('stocknumber', $stocknumber);
2289 sub SearchItemsByField {
2290 my ($field, $value) = @_;
2297 my ($results) = SearchItems($filters);
2301 sub _SearchItems_build_where_fragment {
2304 my $dbh = C4::Context->dbh;
2307 if (exists($filter->{conjunction})) {
2308 my (@where_strs, @where_args);
2309 foreach my $f (@{ $filter->{filters} }) {
2310 my $fragment = _SearchItems_build_where_fragment($f);
2312 push @where_strs, $fragment->{str};
2313 push @where_args, @{ $fragment->{args} };
2318 $where_str = '(' . join (' ' . $filter->{conjunction} . ' ', @where_strs) . ')';
2321 args => \@where_args,
2325 my @columns = Koha::Database->new()->schema()->resultset('Item')->result_source->columns;
2326 push @columns, Koha::Database->new()->schema()->resultset('Biblio')->result_source->columns;
2327 push @columns, Koha::Database->new()->schema()->resultset('Biblioitem')->result_source->columns;
2328 my @operators = qw(= != > < >= <= like);
2329 my $field = $filter->{field};
2330 if ( (0 < grep /^$field$/, @columns) or (substr($field, 0, 5) eq 'marc:') ) {
2331 my $op = $filter->{operator};
2332 my $query = $filter->{query};
2334 if (!$op or (0 == grep /^$op$/, @operators)) {
2335 $op = '='; # default operator
2339 if ($field =~ /^marc:(\d{3})(?:\$(\w))?$/) {
2341 my $marcsubfield = $2;
2342 my ($kohafield) = $dbh->selectrow_array(q|
2343 SELECT kohafield FROM marc_subfield_structure
2344 WHERE tagfield=? AND tagsubfield=? AND frameworkcode=''
2345 |, undef, $marcfield, $marcsubfield);
2348 $column = $kohafield;
2350 # MARC field is not linked to a DB field so we need to use
2351 # ExtractValue on marcxml from biblio_metadata or
2352 # items.more_subfields_xml, depending on the MARC field.
2355 my ($itemfield) = C4::Biblio::GetMarcFromKohaField('items.itemnumber');
2356 if ($marcfield eq $itemfield) {
2357 $sqlfield = 'more_subfields_xml';
2358 $xpath = '//record/datafield/subfield[@code="' . $marcsubfield . '"]';
2360 $sqlfield = 'metadata'; # From biblio_metadata
2361 if ($marcfield < 10) {
2362 $xpath = "//record/controlfield[\@tag=\"$marcfield\"]";
2364 $xpath = "//record/datafield[\@tag=\"$marcfield\"]/subfield[\@code=\"$marcsubfield\"]";
2367 $column = "ExtractValue($sqlfield, '$xpath')";
2373 if (ref $query eq 'ARRAY') {
2376 } elsif ($op eq '!=') {
2380 str => "$column $op (" . join (',', ('?') x @$query) . ")",
2385 str => "$column $op ?",
2392 return $where_fragment;
2397 my ($items, $total) = SearchItems($filter, $params);
2399 Perform a search among items
2401 $filter is a reference to a hash which can be a filter, or a combination of filters.
2403 A filter has the following keys:
2407 =item * field: the name of a SQL column in table items
2409 =item * query: the value to search in this column
2411 =item * operator: comparison operator. Can be one of = != > < >= <= like
2415 A combination of filters hash the following keys:
2419 =item * conjunction: 'AND' or 'OR'
2421 =item * filters: array ref of filters
2425 $params is a reference to a hash that can contain the following parameters:
2429 =item * rows: Number of items to return. 0 returns everything (default: 0)
2431 =item * page: Page to return (return items from (page-1)*rows to (page*rows)-1)
2434 =item * sortby: A SQL column name in items table to sort on
2436 =item * sortorder: 'ASC' or 'DESC'
2443 my ($filter, $params) = @_;
2447 return unless ref $filter eq 'HASH';
2448 return unless ref $params eq 'HASH';
2450 # Default parameters
2451 $params->{rows} ||= 0;
2452 $params->{page} ||= 1;
2453 $params->{sortby} ||= 'itemnumber';
2454 $params->{sortorder} ||= 'ASC';
2456 my ($where_str, @where_args);
2457 my $where_fragment = _SearchItems_build_where_fragment($filter);
2458 if ($where_fragment) {
2459 $where_str = $where_fragment->{str};
2460 @where_args = @{ $where_fragment->{args} };
2463 my $dbh = C4::Context->dbh;
2465 SELECT SQL_CALC_FOUND_ROWS items.*
2467 LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber
2468 LEFT JOIN biblioitems ON biblioitems.biblioitemnumber = items.biblioitemnumber
2469 LEFT JOIN biblio_metadata ON biblio_metadata.biblionumber = biblio.biblionumber
2472 if (defined $where_str and $where_str ne '') {
2473 $query .= qq{ AND $where_str };
2476 $query .= q{ AND biblio_metadata.format = 'marcxml' AND biblio_metadata.marcflavour = ? };
2477 push @where_args, C4::Context->preference('marcflavour');
2479 my @columns = Koha::Database->new()->schema()->resultset('Item')->result_source->columns;
2480 push @columns, Koha::Database->new()->schema()->resultset('Biblio')->result_source->columns;
2481 push @columns, Koha::Database->new()->schema()->resultset('Biblioitem')->result_source->columns;
2482 my $sortby = (0 < grep {$params->{sortby} eq $_} @columns)
2483 ? $params->{sortby} : 'itemnumber';
2484 my $sortorder = (uc($params->{sortorder}) eq 'ASC') ? 'ASC' : 'DESC';
2485 $query .= qq{ ORDER BY $sortby $sortorder };
2487 my $rows = $params->{rows};
2490 my $offset = $rows * ($params->{page}-1);
2491 $query .= qq { LIMIT ?, ? };
2492 push @limit_args, $offset, $rows;
2495 my $sth = $dbh->prepare($query);
2496 my $rv = $sth->execute(@where_args, @limit_args);
2498 return unless ($rv);
2499 my ($total_rows) = $dbh->selectrow_array(q{ SELECT FOUND_ROWS() });
2501 return ($sth->fetchall_arrayref({}), $total_rows);
2505 =head1 OTHER FUNCTIONS
2509 ($indicators, $value) = _find_value($tag, $subfield, $record,$encoding);
2511 Find the given $subfield in the given $tag in the given
2512 MARC::Record $record. If the subfield is found, returns
2513 the (indicators, value) pair; otherwise, (undef, undef) is
2517 Such a function is used in addbiblio AND additem and serial-edit and maybe could be used in Authorities.
2518 I suggest we export it from this module.
2523 my ( $tagfield, $insubfield, $record, $encoding ) = @_;
2526 if ( $tagfield < 10 ) {
2527 if ( $record->field($tagfield) ) {
2528 push @result, $record->field($tagfield)->data();
2533 foreach my $field ( $record->field($tagfield) ) {
2534 my @subfields = $field->subfields();
2535 foreach my $subfield (@subfields) {
2536 if ( @$subfield[0] eq $insubfield ) {
2537 push @result, @$subfield[1];
2538 $indicator = $field->indicator(1) . $field->indicator(2);
2543 return ( $indicator, @result );
2547 =head2 PrepareItemrecordDisplay
2549 PrepareItemrecordDisplay($itemrecord,$bibnum,$itemumber,$frameworkcode);
2551 Returns a hash with all the fields for Display a given item data in a template
2553 The $frameworkcode returns the item for the given frameworkcode, ONLY if bibnum is not provided
2557 sub PrepareItemrecordDisplay {
2559 my ( $bibnum, $itemnum, $defaultvalues, $frameworkcode ) = @_;
2561 my $dbh = C4::Context->dbh;
2562 $frameworkcode = C4::Biblio::GetFrameworkCode($bibnum) if $bibnum;
2563 my ( $itemtagfield, $itemtagsubfield ) = C4::Biblio::GetMarcFromKohaField( "items.itemnumber", $frameworkcode );
2565 # Note: $tagslib obtained from GetMarcStructure() in 'unsafe' mode is
2566 # a shared data structure. No plugin (including custom ones) should change
2567 # its contents. See also GetMarcStructure.
2568 my $tagslib = &GetMarcStructure( 1, $frameworkcode, { unsafe => 1 } );
2570 # return nothing if we don't have found an existing framework.
2571 return q{} unless $tagslib;
2574 $itemrecord = C4::Items::GetMarcItem( $bibnum, $itemnum );
2578 my $branch_limit = C4::Context->userenv ? C4::Context->userenv->{"branch"} : "";
2580 SELECT authorised_value,lib FROM authorised_values
2583 LEFT JOIN authorised_values_branches ON ( id = av_id )
2588 $query .= qq{ AND ( branchcode = ? OR branchcode IS NULL )} if $branch_limit;
2589 $query .= qq{ ORDER BY lib};
2590 my $authorised_values_sth = $dbh->prepare( $query );
2591 foreach my $tag ( sort keys %{$tagslib} ) {
2594 # loop through each subfield
2596 foreach my $subfield ( sort keys %{ $tagslib->{$tag} } ) {
2597 next if IsMarcStructureInternal($tagslib->{$tag}{$subfield});
2598 next unless ( $tagslib->{$tag}->{$subfield}->{'tab'} );
2599 next if ( $tagslib->{$tag}->{$subfield}->{'tab'} ne "10" );
2601 $subfield_data{tag} = $tag;
2602 $subfield_data{subfield} = $subfield;
2603 $subfield_data{countsubfield} = $cntsubf++;
2604 $subfield_data{kohafield} = $tagslib->{$tag}->{$subfield}->{'kohafield'};
2605 $subfield_data{id} = "tag_".$tag."_subfield_".$subfield."_".int(rand(1000000));
2607 # $subfield_data{marc_lib}=$tagslib->{$tag}->{$subfield}->{lib};
2608 $subfield_data{marc_lib} = $tagslib->{$tag}->{$subfield}->{lib};
2609 $subfield_data{mandatory} = $tagslib->{$tag}->{$subfield}->{mandatory};
2610 $subfield_data{repeatable} = $tagslib->{$tag}->{$subfield}->{repeatable};
2611 $subfield_data{hidden} = "display:none"
2612 if ( ( $tagslib->{$tag}->{$subfield}->{hidden} > 4 )
2613 || ( $tagslib->{$tag}->{$subfield}->{hidden} < -4 ) );
2614 my ( $x, $defaultvalue );
2616 ( $x, $defaultvalue ) = _find_value( $tag, $subfield, $itemrecord );
2618 $defaultvalue = $tagslib->{$tag}->{$subfield}->{defaultvalue} unless $defaultvalue;
2619 if ( !defined $defaultvalue ) {
2620 $defaultvalue = q||;
2622 $defaultvalue =~ s/"/"/g;
2625 # search for itemcallnumber if applicable
2626 if ( $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.itemcallnumber'
2627 && C4::Context->preference('itemcallnumber') ) {
2628 my $CNtag = substr( C4::Context->preference('itemcallnumber'), 0, 3 );
2629 my $CNsubfield = substr( C4::Context->preference('itemcallnumber'), 3, 1 );
2630 if ( $itemrecord and my $field = $itemrecord->field($CNtag) ) {
2631 $defaultvalue = $field->subfield($CNsubfield);
2634 if ( $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.itemcallnumber'
2636 && $defaultvalues->{'callnumber'} ) {
2637 if( $itemrecord and $defaultvalues and not $itemrecord->subfield($tag,$subfield) ){
2638 # if the item record exists, only use default value if the item has no callnumber
2639 $defaultvalue = $defaultvalues->{callnumber};
2640 } elsif ( !$itemrecord and $defaultvalues ) {
2641 # if the item record *doesn't* exists, always use the default value
2642 $defaultvalue = $defaultvalues->{callnumber};
2645 if ( ( $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.holdingbranch' || $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.homebranch' )
2647 && $defaultvalues->{'branchcode'} ) {
2648 if ( $itemrecord and $defaultvalues and not $itemrecord->subfield($tag,$subfield) ) {
2649 $defaultvalue = $defaultvalues->{branchcode};
2652 if ( ( $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.location' )
2654 && $defaultvalues->{'location'} ) {
2656 if ( $itemrecord and $defaultvalues and not $itemrecord->subfield($tag,$subfield) ) {
2657 # if the item record exists, only use default value if the item has no locationr
2658 $defaultvalue = $defaultvalues->{location};
2659 } elsif ( !$itemrecord and $defaultvalues ) {
2660 # if the item record *doesn't* exists, always use the default value
2661 $defaultvalue = $defaultvalues->{location};
2664 if ( $tagslib->{$tag}->{$subfield}->{authorised_value} ) {
2665 my @authorised_values;
2668 # builds list, depending on authorised value...
2670 if ( $tagslib->{$tag}->{$subfield}->{'authorised_value'} eq "branches" ) {
2671 if ( ( C4::Context->preference("IndependentBranches") )
2672 && !C4::Context->IsSuperLibrarian() ) {
2673 my $sth = $dbh->prepare( "SELECT branchcode,branchname FROM branches WHERE branchcode = ? ORDER BY branchname" );
2674 $sth->execute( C4::Context->userenv->{branch} );
2675 push @authorised_values, ""
2676 unless ( $tagslib->{$tag}->{$subfield}->{mandatory} );
2677 while ( my ( $branchcode, $branchname ) = $sth->fetchrow_array ) {
2678 push @authorised_values, $branchcode;
2679 $authorised_lib{$branchcode} = $branchname;
2682 my $sth = $dbh->prepare( "SELECT branchcode,branchname FROM branches ORDER BY branchname" );
2684 push @authorised_values, ""
2685 unless ( $tagslib->{$tag}->{$subfield}->{mandatory} );
2686 while ( my ( $branchcode, $branchname ) = $sth->fetchrow_array ) {
2687 push @authorised_values, $branchcode;
2688 $authorised_lib{$branchcode} = $branchname;
2692 $defaultvalue = C4::Context->userenv ? C4::Context->userenv->{branch} : undef;
2693 if ( $defaultvalues and $defaultvalues->{branchcode} ) {
2694 $defaultvalue = $defaultvalues->{branchcode};
2698 } elsif ( $tagslib->{$tag}->{$subfield}->{authorised_value} eq "itemtypes" ) {
2699 my $itemtypes = Koha::ItemTypes->search_with_localization;
2700 push @authorised_values, ""
2701 unless ( $tagslib->{$tag}->{$subfield}->{mandatory} );
2702 while ( my $itemtype = $itemtypes->next ) {
2703 push @authorised_values, $itemtype->itemtype;
2704 $authorised_lib{$itemtype->itemtype} = $itemtype->translated_description;
2706 if ($defaultvalues && $defaultvalues->{'itemtype'}) {
2707 $defaultvalue = $defaultvalues->{'itemtype'};
2711 } elsif ( $tagslib->{$tag}->{$subfield}->{authorised_value} eq "cn_source" ) {
2712 push @authorised_values, "" unless ( $tagslib->{$tag}->{$subfield}->{mandatory} );
2714 my $class_sources = GetClassSources();
2715 my $default_source = C4::Context->preference("DefaultClassificationSource");
2717 foreach my $class_source (sort keys %$class_sources) {
2718 next unless $class_sources->{$class_source}->{'used'} or
2719 ($class_source eq $default_source);
2720 push @authorised_values, $class_source;
2721 $authorised_lib{$class_source} = $class_sources->{$class_source}->{'description'};
2724 $defaultvalue = $default_source;
2726 #---- "true" authorised value
2728 $authorised_values_sth->execute(
2729 $tagslib->{$tag}->{$subfield}->{authorised_value},
2730 $branch_limit ? $branch_limit : ()
2732 push @authorised_values, ""
2733 unless ( $tagslib->{$tag}->{$subfield}->{mandatory} );
2734 while ( my ( $value, $lib ) = $authorised_values_sth->fetchrow_array ) {
2735 push @authorised_values, $value;
2736 $authorised_lib{$value} = $lib;
2739 $subfield_data{marc_value} = {
2741 values => \@authorised_values,
2742 default => "$defaultvalue",
2743 labels => \%authorised_lib,
2745 } elsif ( $tagslib->{$tag}->{$subfield}->{value_builder} ) {
2747 require Koha::FrameworkPlugin;
2748 my $plugin = Koha::FrameworkPlugin->new({
2749 name => $tagslib->{$tag}->{$subfield}->{value_builder},
2752 my $pars = { dbh => $dbh, record => undef, tagslib =>$tagslib, id => $subfield_data{id}, tabloop => undef };
2753 $plugin->build( $pars );
2754 if ( $itemrecord and my $field = $itemrecord->field($tag) ) {
2755 $defaultvalue = $field->subfield($subfield);
2757 if( !$plugin->errstr ) {
2758 #TODO Move html to template; see report 12176/13397
2759 my $tab= $plugin->noclick? '-1': '';
2760 my $class= $plugin->noclick? ' disabled': '';
2761 my $title= $plugin->noclick? 'No popup': 'Tag editor';
2762 $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;
2764 warn $plugin->errstr;
2765 $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
2768 elsif ( $tag eq '' ) { # it's an hidden field
2769 $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" />);
2771 elsif ( $tagslib->{$tag}->{$subfield}->{'hidden'} ) { # FIXME: shouldn't input type be "hidden" ?
2772 $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" />);
2774 elsif ( length($defaultvalue) > 100
2775 or (C4::Context->preference("marcflavour") eq "UNIMARC" and
2776 300 <= $tag && $tag < 400 && $subfield eq 'a' )
2777 or (C4::Context->preference("marcflavour") eq "MARC21" and
2778 500 <= $tag && $tag < 600 )
2780 # oversize field (textarea)
2781 $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");
2783 $subfield_data{marc_value} = "<input type=\"text\" name=\"field_value\" value=\"$defaultvalue\" size=\"50\" maxlength=\"255\" />";
2785 push( @loop_data, \%subfield_data );
2790 if ( $itemrecord && $itemrecord->field($itemtagfield) ) {
2791 $itemnumber = $itemrecord->subfield( $itemtagfield, $itemtagsubfield );
2794 'itemtagfield' => $itemtagfield,
2795 'itemtagsubfield' => $itemtagsubfield,
2796 'itemnumber' => $itemnumber,
2797 'iteminformation' => \@loop_data
2801 sub ToggleNewStatus {
2802 my ( $params ) = @_;
2803 my @rules = @{ $params->{rules} };
2804 my $report_only = $params->{report_only};
2806 my $dbh = C4::Context->dbh;
2808 my @item_columns = map { "items.$_" } Koha::Items->columns;
2809 my @biblioitem_columns = map { "biblioitems.$_" } Koha::Biblioitems->columns;
2811 for my $rule ( @rules ) {
2812 my $age = $rule->{age};
2813 my $conditions = $rule->{conditions};
2814 my $substitutions = $rule->{substitutions};
2818 SELECT items.biblionumber, items.itemnumber
2820 LEFT JOIN biblioitems ON biblioitems.biblionumber = items.biblionumber
2823 for my $condition ( @$conditions ) {
2825 grep {/^$condition->{field}$/} @item_columns
2826 or grep {/^$condition->{field}$/} @biblioitem_columns
2828 if ( $condition->{value} =~ /\|/ ) {
2829 my @values = split /\|/, $condition->{value};
2830 $query .= qq| AND $condition->{field} IN (|
2831 . join( ',', ('?') x scalar @values )
2833 push @params, @values;
2835 $query .= qq| AND $condition->{field} = ?|;
2836 push @params, $condition->{value};
2840 if ( defined $age ) {
2841 $query .= q| AND TO_DAYS(NOW()) - TO_DAYS(dateaccessioned) >= ? |;
2844 my $sth = $dbh->prepare($query);
2845 $sth->execute( @params );
2846 while ( my $values = $sth->fetchrow_hashref ) {
2847 my $biblionumber = $values->{biblionumber};
2848 my $itemnumber = $values->{itemnumber};
2849 my $item = C4::Items::GetItem( $itemnumber );
2850 for my $substitution ( @$substitutions ) {
2851 next unless $substitution->{field};
2852 C4::Items::ModItem( {$substitution->{field} => $substitution->{value}}, $biblionumber, $itemnumber )
2853 unless $report_only;
2854 push @{ $report->{$itemnumber} }, $substitution;