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 $log_action = shift // 1;
552 # if $biblionumber is undefined, get it from the current item
553 unless (defined $biblionumber) {
554 $biblionumber = _get_single_item_column('biblionumber', $itemnumber);
557 my $dbh = @_ ? shift : C4::Context->dbh;
558 my $frameworkcode = @_ ? shift : C4::Biblio::GetFrameworkCode( $biblionumber );
560 my $unlinked_item_subfields;
562 $unlinked_item_subfields = shift;
563 $item->{'more_subfields_xml'} = _get_unlinked_subfields_xml($unlinked_item_subfields);
566 $item->{'itemnumber'} = $itemnumber or return;
568 my @fields = qw( itemlost withdrawn damaged );
570 # Only call GetItem if we need to set an "on" date field
571 if ( $item->{itemlost} || $item->{withdrawn} || $item->{damaged} ) {
572 my $pre_mod_item = GetItem( $item->{'itemnumber'} );
573 for my $field (@fields) {
574 if ( defined( $item->{$field} )
575 and not $pre_mod_item->{$field}
576 and $item->{$field} )
578 $item->{ $field . '_on' } =
579 DateTime::Format::MySQL->format_datetime( dt_from_string() );
584 # If the field is defined but empty, we are removing and,
585 # and thus need to clear out the 'on' field as well
586 for my $field (@fields) {
587 if ( defined( $item->{$field} ) && !$item->{$field} ) {
588 $item->{ $field . '_on' } = undef;
593 _set_derived_columns_for_mod($item);
594 _do_column_fixes_for_mod($item);
597 # attempt to change itemnumber
598 # attempt to change biblionumber (if we want
599 # an API to relink an item to a different bib,
600 # it should be a separate function)
603 _koha_modify_item($item);
605 # request that bib be reindexed so that searching on current
606 # item status is possible
607 ModZebra( $biblionumber, "specialUpdate", "biblioserver" );
609 logaction( "CATALOGUING", "MODIFY", $itemnumber, "item " . Dumper($item) )
610 if $log_action && C4::Context->preference("CataloguingLog");
613 =head2 ModItemTransfer
615 ModItemTransfer($itenumber, $frombranch, $tobranch);
617 Marks an item as being transferred from one branch
622 sub ModItemTransfer {
623 my ( $itemnumber, $frombranch, $tobranch ) = @_;
625 my $dbh = C4::Context->dbh;
627 # Remove the 'shelving cart' location status if it is being used.
628 CartToShelf( $itemnumber ) if ( C4::Context->preference("ReturnToShelvingCart") );
630 $dbh->do("UPDATE branchtransfers SET datearrived = NOW(), comments = ? WHERE itemnumber = ? AND datearrived IS NULL", undef, "Canceled, new transfer from $frombranch to $tobranch created", $itemnumber);
632 #new entry in branchtransfers....
633 my $sth = $dbh->prepare(
634 "INSERT INTO branchtransfers (itemnumber, frombranch, datesent, tobranch)
635 VALUES (?, ?, NOW(), ?)");
636 $sth->execute($itemnumber, $frombranch, $tobranch);
638 ModItem({ holdingbranch => $tobranch }, undef, $itemnumber);
639 ModDateLastSeen($itemnumber);
643 =head2 ModDateLastSeen
645 ModDateLastSeen($itemnum);
647 Mark item as seen. Is called when an item is issued, returned or manually marked during inventory/stocktaking.
648 C<$itemnum> is the item number
652 sub ModDateLastSeen {
653 my ($itemnumber) = @_;
655 my $today = output_pref({ dt => dt_from_string, dateformat => 'iso', dateonly => 1 });
656 ModItem( { itemlost => 0, datelastseen => $today }, undef, $itemnumber, 0 );
661 DelItem({ itemnumber => $itemnumber, [ biblionumber => $biblionumber ] } );
663 Exported function (core API) for deleting an item record in Koha.
670 my $itemnumber = $params->{itemnumber};
671 my $biblionumber = $params->{biblionumber};
673 unless ($biblionumber) {
674 my $item = Koha::Items->find( $itemnumber );
675 $biblionumber = $item ? $item->biblio->biblionumber : undef;
678 # If there is no biblionumber for the given itemnumber, there is nothing to delete
679 return 0 unless $biblionumber;
681 # FIXME check the item has no current issues
682 my $deleted = _koha_delete_item( $itemnumber );
684 ModZebra( $biblionumber, "specialUpdate", "biblioserver" );
686 #search item field code
687 logaction("CATALOGUING", "DELETE", $itemnumber, "item") if C4::Context->preference("CataloguingLog");
691 =head2 CheckItemPreSave
693 my $item_ref = TransformMarcToKoha($marc, 'items');
695 my %errors = CheckItemPreSave($item_ref);
696 if (exists $errors{'duplicate_barcode'}) {
697 print "item has duplicate barcode: ", $errors{'duplicate_barcode'}, "\n";
698 } elsif (exists $errors{'invalid_homebranch'}) {
699 print "item has invalid home branch: ", $errors{'invalid_homebranch'}, "\n";
700 } elsif (exists $errors{'invalid_holdingbranch'}) {
701 print "item has invalid holding branch: ", $errors{'invalid_holdingbranch'}, "\n";
706 Given a hashref containing item fields, determine if it can be
707 inserted or updated in the database. Specifically, checks for
708 database integrity issues, and returns a hash containing any
709 of the following keys, if applicable.
713 =item duplicate_barcode
715 Barcode, if it duplicates one already found in the database.
717 =item invalid_homebranch
719 Home branch, if not defined in branches table.
721 =item invalid_holdingbranch
723 Holding branch, if not defined in branches table.
727 This function does NOT implement any policy-related checks,
728 e.g., whether current operator is allowed to save an
729 item that has a given branch code.
733 sub CheckItemPreSave {
734 my $item_ref = shift;
738 # check for duplicate barcode
739 if (exists $item_ref->{'barcode'} and defined $item_ref->{'barcode'}) {
740 my $existing_itemnumber = GetItemnumberFromBarcode($item_ref->{'barcode'});
741 if ($existing_itemnumber) {
742 if (!exists $item_ref->{'itemnumber'} # new item
743 or $item_ref->{'itemnumber'} != $existing_itemnumber) { # existing item
744 $errors{'duplicate_barcode'} = $item_ref->{'barcode'};
749 # check for valid home branch
750 if (exists $item_ref->{'homebranch'} and defined $item_ref->{'homebranch'}) {
751 my $home_library = Koha::Libraries->find( $item_ref->{homebranch} );
752 unless (defined $home_library) {
753 $errors{'invalid_homebranch'} = $item_ref->{'homebranch'};
757 # check for valid holding branch
758 if (exists $item_ref->{'holdingbranch'} and defined $item_ref->{'holdingbranch'}) {
759 my $holding_library = Koha::Libraries->find( $item_ref->{holdingbranch} );
760 unless (defined $holding_library) {
761 $errors{'invalid_holdingbranch'} = $item_ref->{'holdingbranch'};
769 =head1 EXPORTED SPECIAL ACCESSOR FUNCTIONS
771 The following functions provide various ways of
772 getting an item record, a set of item records, or
773 lists of authorized values for certain item fields.
775 Some of the functions in this group are candidates
776 for refactoring -- for example, some of the code
777 in C<GetItemsByBiblioitemnumber> and C<GetItemsInfo>
778 has copy-and-paste work.
782 =head2 GetItemsForInventory
784 ($itemlist, $iTotalRecords) = GetItemsForInventory( {
785 minlocation => $minlocation,
786 maxlocation => $maxlocation,
787 location => $location,
788 itemtype => $itemtype,
789 ignoreissued => $ignoreissued,
790 datelastseen => $datelastseen,
791 branchcode => $branchcode,
795 statushash => $statushash,
798 Retrieve a list of title/authors/barcode/callnumber, for biblio inventory.
800 The sub returns a reference to a list of hashes, each containing
801 itemnumber, author, title, barcode, item callnumber, and date last
802 seen. It is ordered by callnumber then title.
804 The required minlocation & maxlocation parameters are used to specify a range of item callnumbers
805 the datelastseen can be used to specify that you want to see items not seen since a past date only.
806 offset & size can be used to retrieve only a part of the whole listing (defaut behaviour)
807 $statushash requires a hashref that has the authorized values fieldname (intems.notforloan, etc...) as keys, and an arrayref of statuscodes we are searching for as values.
809 $iTotalRecords is the number of rows that would have been returned without the $offset, $size limit clause
813 sub GetItemsForInventory {
814 my ( $parameters ) = @_;
815 my $minlocation = $parameters->{'minlocation'} // '';
816 my $maxlocation = $parameters->{'maxlocation'} // '';
817 my $location = $parameters->{'location'} // '';
818 my $itemtype = $parameters->{'itemtype'} // '';
819 my $ignoreissued = $parameters->{'ignoreissued'} // '';
820 my $datelastseen = $parameters->{'datelastseen'} // '';
821 my $branchcode = $parameters->{'branchcode'} // '';
822 my $branch = $parameters->{'branch'} // '';
823 my $offset = $parameters->{'offset'} // '';
824 my $size = $parameters->{'size'} // '';
825 my $statushash = $parameters->{'statushash'} // '';
827 my $dbh = C4::Context->dbh;
828 my ( @bind_params, @where_strings );
830 my $select_columns = q{
831 SELECT items.itemnumber, barcode, itemcallnumber, title, author, biblio.biblionumber, biblio.frameworkcode, datelastseen, homebranch, location, notforloan, damaged, itemlost, withdrawn, stocknumber
833 my $select_count = q{SELECT COUNT(*)};
836 LEFT JOIN biblio ON items.biblionumber = biblio.biblionumber
837 LEFT JOIN biblioitems on items.biblionumber = biblioitems.biblionumber
840 for my $authvfield (keys %$statushash){
841 if ( scalar @{$statushash->{$authvfield}} > 0 ){
842 my $joinedvals = join ',', @{$statushash->{$authvfield}};
843 push @where_strings, "$authvfield in (" . $joinedvals . ")";
849 push @where_strings, 'itemcallnumber >= ?';
850 push @bind_params, $minlocation;
854 push @where_strings, 'itemcallnumber <= ?';
855 push @bind_params, $maxlocation;
859 $datelastseen = output_pref({ str => $datelastseen, dateformat => 'iso', dateonly => 1 });
860 push @where_strings, '(datelastseen < ? OR datelastseen IS NULL)';
861 push @bind_params, $datelastseen;
865 push @where_strings, 'items.location = ?';
866 push @bind_params, $location;
870 if($branch eq "homebranch"){
871 push @where_strings, 'items.homebranch = ?';
873 push @where_strings, 'items.holdingbranch = ?';
875 push @bind_params, $branchcode;
879 push @where_strings, 'biblioitems.itemtype = ?';
880 push @bind_params, $itemtype;
883 if ( $ignoreissued) {
884 $query .= "LEFT JOIN issues ON items.itemnumber = issues.itemnumber ";
885 push @where_strings, 'issues.date_due IS NULL';
888 if ( @where_strings ) {
890 $query .= join ' AND ', @where_strings;
892 my $count_query = $select_count . $query;
893 $query .= ' ORDER BY items.cn_sort, itemcallnumber, title';
894 $query .= " LIMIT $offset, $size" if ($offset and $size);
895 $query = $select_columns . $query;
896 my $sth = $dbh->prepare($query);
897 $sth->execute( @bind_params );
900 my $tmpresults = $sth->fetchall_arrayref({});
901 $sth = $dbh->prepare( $count_query );
902 $sth->execute( @bind_params );
903 my ($iTotalRecords) = $sth->fetchrow_array();
905 my @avs = Koha::AuthorisedValues->search(
906 { 'marc_subfield_structures.kohafield' => { '>' => '' },
907 'me.authorised_value' => { '>' => '' },
909 { join => { category => 'marc_subfield_structures' },
910 distinct => ['marc_subfield_structures.kohafield, me.category, frameworkcode, me.authorised_value'],
911 '+select' => [ 'marc_subfield_structures.kohafield', 'marc_subfield_structures.frameworkcode', 'me.authorised_value', 'me.lib' ],
912 '+as' => [ 'kohafield', 'frameworkcode', 'authorised_value', 'lib' ],
916 my $avmapping = { map { $_->get_column('kohafield') . ',' . $_->get_column('frameworkcode') . ',' . $_->get_column('authorised_value') => $_->get_column('lib') } @avs };
918 foreach my $row (@$tmpresults) {
921 foreach (keys %$row) {
922 if (defined($avmapping->{"items.$_,".$row->{'frameworkcode'}.",".$row->{$_}})) {
923 $row->{$_} = $avmapping->{"items.$_,".$row->{'frameworkcode'}.",".$row->{$_}};
929 return (\@results, $iTotalRecords);
932 =head2 GetItemsByBiblioitemnumber
934 GetItemsByBiblioitemnumber($biblioitemnumber);
936 Returns an arrayref of hashrefs suitable for use in a TMPL_LOOP
937 Called by C<C4::XISBN>
941 sub GetItemsByBiblioitemnumber {
942 my ( $bibitem ) = @_;
943 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->{restricted} = $descriptions->{lib} // '';
1107 $data->{restrictedopac} = $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('marcflavour') eq 'MARC21' ||
1216 C4::Context->preference('marcflavour') eq 'NORMARC'){
1217 foreach my $hostfield ( $record->field('773') ) {
1218 my $hostbiblionumber = $hostfield->subfield("0");
1219 my $linkeditemnumber = $hostfield->subfield("9");
1220 my @hostitemInfos = GetItemsInfo($hostbiblionumber);
1221 foreach my $hostitemInfo (@hostitemInfos){
1222 if ($hostitemInfo->{itemnumber} eq $linkeditemnumber){
1223 push (@returnitemsInfo,$hostitemInfo);
1228 } elsif ( C4::Context->preference('marcflavour') eq 'UNIMARC'){
1229 foreach my $hostfield ( $record->field('461') ) {
1230 my $hostbiblionumber = $hostfield->subfield("0");
1231 my $linkeditemnumber = $hostfield->subfield("9");
1232 my @hostitemInfos = GetItemsInfo($hostbiblionumber);
1233 foreach my $hostitemInfo (@hostitemInfos){
1234 if ($hostitemInfo->{itemnumber} eq $linkeditemnumber){
1235 push (@returnitemsInfo,$hostitemInfo);
1241 return @returnitemsInfo;
1245 =head2 GetLastAcquisitions
1247 my $lastacq = GetLastAcquisitions({'branches' => ('branch1','branch2'),
1248 'itemtypes' => ('BK','BD')}, 10);
1252 sub GetLastAcquisitions {
1253 my ($data,$max) = @_;
1255 my $itemtype = C4::Context->preference('item-level_itypes') ? 'itype' : 'itemtype';
1257 my $number_of_branches = @{$data->{branches}};
1258 my $number_of_itemtypes = @{$data->{itemtypes}};
1261 my @where = ('WHERE 1 ');
1262 $number_of_branches and push @where
1263 , 'AND holdingbranch IN ('
1264 , join(',', ('?') x $number_of_branches )
1268 $number_of_itemtypes and push @where
1269 , "AND $itemtype IN ("
1270 , join(',', ('?') x $number_of_itemtypes )
1274 my $query = "SELECT biblio.biblionumber as biblionumber, title, dateaccessioned
1275 FROM items RIGHT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
1276 RIGHT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber)
1278 GROUP BY biblio.biblionumber
1279 ORDER BY dateaccessioned DESC LIMIT $max";
1281 my $dbh = C4::Context->dbh;
1282 my $sth = $dbh->prepare($query);
1284 $sth->execute((@{$data->{branches}}, @{$data->{itemtypes}}));
1287 while( my $row = $sth->fetchrow_hashref){
1288 push @results, {date => $row->{dateaccessioned}
1289 , biblionumber => $row->{biblionumber}
1290 , title => $row->{title}};
1296 =head2 GetItemnumbersForBiblio
1298 my $itemnumbers = GetItemnumbersForBiblio($biblionumber);
1300 Given a single biblionumber, return an arrayref of all the corresponding itemnumbers
1304 sub GetItemnumbersForBiblio {
1305 my $biblionumber = shift;
1307 my $dbh = C4::Context->dbh;
1308 my $sth = $dbh->prepare("SELECT itemnumber FROM items WHERE biblionumber = ?");
1309 $sth->execute($biblionumber);
1310 while (my $result = $sth->fetchrow_hashref) {
1311 push @items, $result->{'itemnumber'};
1316 =head2 get_hostitemnumbers_of
1318 my @itemnumbers_of = get_hostitemnumbers_of($biblionumber);
1320 Given a biblionumber, return the list of corresponding itemnumbers that are linked to it via host fields
1322 Return a reference on a hash where key is a biblionumber and values are
1323 references on array of itemnumbers.
1328 sub get_hostitemnumbers_of {
1329 my ($biblionumber) = @_;
1330 my $marcrecord = C4::Biblio::GetMarcBiblio({ biblionumber => $biblionumber });
1332 return unless $marcrecord;
1334 my ( @returnhostitemnumbers, $tag, $biblio_s, $item_s );
1336 my $marcflavor = C4::Context->preference('marcflavour');
1337 if ( $marcflavor eq 'MARC21' || $marcflavor eq 'NORMARC' ) {
1342 elsif ( $marcflavor eq 'UNIMARC' ) {
1348 foreach my $hostfield ( $marcrecord->field($tag) ) {
1349 my $hostbiblionumber = $hostfield->subfield($biblio_s);
1350 next unless $hostbiblionumber; # have tag, don't have $biblio_s subfield
1351 my $linkeditemnumber = $hostfield->subfield($item_s);
1352 if ( ! $linkeditemnumber ) {
1353 warn "ERROR biblionumber $biblionumber has 773^0, but doesn't have 9";
1356 my $is_from_biblio = Koha::Items->search({ itemnumber => $linkeditemnumber, biblionumber => $hostbiblionumber });
1357 push @returnhostitemnumbers, $linkeditemnumber
1361 return @returnhostitemnumbers;
1365 =head2 GetItemnumberFromBarcode
1367 $result = GetItemnumberFromBarcode($barcode);
1371 sub GetItemnumberFromBarcode {
1373 my $dbh = C4::Context->dbh;
1376 $dbh->prepare("SELECT itemnumber FROM items WHERE items.barcode=?");
1377 $rq->execute($barcode);
1378 my ($result) = $rq->fetchrow;
1382 =head2 GetBarcodeFromItemnumber
1384 $result = GetBarcodeFromItemnumber($itemnumber);
1388 sub GetBarcodeFromItemnumber {
1389 my ($itemnumber) = @_;
1390 my $dbh = C4::Context->dbh;
1393 $dbh->prepare("SELECT barcode FROM items WHERE items.itemnumber=?");
1394 $rq->execute($itemnumber);
1395 my ($result) = $rq->fetchrow;
1399 =head2 GetHiddenItemnumbers
1401 my @itemnumbers_to_hide = GetHiddenItemnumbers(@items);
1403 Given a list of items it checks which should be hidden from the OPAC given
1404 the current configuration. Returns a list of itemnumbers corresponding to
1405 those that should be hidden.
1409 sub GetHiddenItemnumbers {
1413 my $yaml = C4::Context->preference('OpacHiddenItems');
1414 return () if (! $yaml =~ /\S/ );
1415 $yaml = "$yaml\n\n"; # YAML is anal on ending \n. Surplus does not hurt
1418 $hidingrules = YAML::Load($yaml);
1421 warn "Unable to parse OpacHiddenItems syspref : $@";
1424 my $dbh = C4::Context->dbh;
1427 foreach my $item (@items) {
1429 # We check each rule
1430 foreach my $field (keys %$hidingrules) {
1432 if (exists $item->{$field}) {
1433 $val = $item->{$field};
1436 my $query = "SELECT $field from items where itemnumber = ?";
1437 $val = $dbh->selectrow_array($query, undef, $item->{'itemnumber'});
1439 $val = '' unless defined $val;
1441 # If the results matches the values in the yaml file
1442 if (any { $val eq $_ } @{$hidingrules->{$field}}) {
1444 # We add the itemnumber to the list
1445 push @resultitems, $item->{'itemnumber'};
1447 # If at least one rule matched for an item, no need to test the others
1452 return @resultitems;
1455 =head1 LIMITED USE FUNCTIONS
1457 The following functions, while part of the public API,
1458 are not exported. This is generally because they are
1459 meant to be used by only one script for a specific
1460 purpose, and should not be used in any other context
1461 without careful thought.
1467 my $item_marc = GetMarcItem($biblionumber, $itemnumber);
1469 Returns MARC::Record of the item passed in parameter.
1470 This function is meant for use only in C<cataloguing/additem.pl>,
1471 where it is needed to support that script's MARC-like
1477 my ( $biblionumber, $itemnumber ) = @_;
1479 # GetMarcItem has been revised so that it does the following:
1480 # 1. Gets the item information from the items table.
1481 # 2. Converts it to a MARC field for storage in the bib record.
1483 # The previous behavior was:
1484 # 1. Get the bib record.
1485 # 2. Return the MARC tag corresponding to the item record.
1487 # The difference is that one treats the items row as authoritative,
1488 # while the other treats the MARC representation as authoritative
1489 # under certain circumstances.
1491 my $itemrecord = GetItem($itemnumber);
1493 # Tack on 'items.' prefix to column names so that C4::Biblio::TransformKohaToMarc will work.
1494 # Also, don't emit a subfield if the underlying field is blank.
1497 return Item2Marc($itemrecord,$biblionumber);
1501 my ($itemrecord,$biblionumber)=@_;
1504 defined($itemrecord->{$_}) && $itemrecord->{$_} ne '' ? ("items.$_" => $itemrecord->{$_}) : ()
1505 } keys %{ $itemrecord }
1507 my $framework = C4::Biblio::GetFrameworkCode( $biblionumber );
1508 my $itemmarc = C4::Biblio::TransformKohaToMarc(
1509 $mungeditem, { no_split => 1},
1511 my ( $itemtag, $itemsubfield ) = C4::Biblio::GetMarcFromKohaField(
1512 "items.itemnumber", $framework,
1515 my $unlinked_item_subfields = _parse_unlinked_item_subfields_from_xml($mungeditem->{'items.more_subfields_xml'});
1516 if (defined $unlinked_item_subfields and $#$unlinked_item_subfields > -1) {
1517 foreach my $field ($itemmarc->field($itemtag)){
1518 $field->add_subfields(@$unlinked_item_subfields);
1524 =head1 PRIVATE FUNCTIONS AND VARIABLES
1526 The following functions are not meant to be called
1527 directly, but are documented in order to explain
1528 the inner workings of C<C4::Items>.
1532 =head2 %derived_columns
1534 This hash keeps track of item columns that
1535 are strictly derived from other columns in
1536 the item record and are not meant to be set
1539 Each key in the hash should be the name of a
1540 column (as named by TransformMarcToKoha). Each
1541 value should be hashref whose keys are the
1542 columns on which the derived column depends. The
1543 hashref should also contain a 'BUILDER' key
1544 that is a reference to a sub that calculates
1549 my %derived_columns = (
1550 'items.cn_sort' => {
1551 'itemcallnumber' => 1,
1552 'items.cn_source' => 1,
1553 'BUILDER' => \&_calc_items_cn_sort,
1557 =head2 _set_derived_columns_for_add
1559 _set_derived_column_for_add($item);
1561 Given an item hash representing a new item to be added,
1562 calculate any derived columns. Currently the only
1563 such column is C<items.cn_sort>.
1567 sub _set_derived_columns_for_add {
1570 foreach my $column (keys %derived_columns) {
1571 my $builder = $derived_columns{$column}->{'BUILDER'};
1572 my $source_values = {};
1573 foreach my $source_column (keys %{ $derived_columns{$column} }) {
1574 next if $source_column eq 'BUILDER';
1575 $source_values->{$source_column} = $item->{$source_column};
1577 $builder->($item, $source_values);
1581 =head2 _set_derived_columns_for_mod
1583 _set_derived_column_for_mod($item);
1585 Given an item hash representing a new item to be modified.
1586 calculate any derived columns. Currently the only
1587 such column is C<items.cn_sort>.
1589 This routine differs from C<_set_derived_columns_for_add>
1590 in that it needs to handle partial item records. In other
1591 words, the caller of C<ModItem> may have supplied only one
1592 or two columns to be changed, so this function needs to
1593 determine whether any of the columns to be changed affect
1594 any of the derived columns. Also, if a derived column
1595 depends on more than one column, but the caller is not
1596 changing all of then, this routine retrieves the unchanged
1597 values from the database in order to ensure a correct
1602 sub _set_derived_columns_for_mod {
1605 foreach my $column (keys %derived_columns) {
1606 my $builder = $derived_columns{$column}->{'BUILDER'};
1607 my $source_values = {};
1608 my %missing_sources = ();
1609 my $must_recalc = 0;
1610 foreach my $source_column (keys %{ $derived_columns{$column} }) {
1611 next if $source_column eq 'BUILDER';
1612 if (exists $item->{$source_column}) {
1614 $source_values->{$source_column} = $item->{$source_column};
1616 $missing_sources{$source_column} = 1;
1620 foreach my $source_column (keys %missing_sources) {
1621 $source_values->{$source_column} = _get_single_item_column($source_column, $item->{'itemnumber'});
1623 $builder->($item, $source_values);
1628 =head2 _do_column_fixes_for_mod
1630 _do_column_fixes_for_mod($item);
1632 Given an item hashref containing one or more
1633 columns to modify, fix up certain values.
1634 Specifically, set to 0 any passed value
1635 of C<notforloan>, C<damaged>, C<itemlost>, or
1636 C<withdrawn> that is either undefined or
1637 contains the empty string.
1641 sub _do_column_fixes_for_mod {
1644 if (exists $item->{'notforloan'} and
1645 (not defined $item->{'notforloan'} or $item->{'notforloan'} eq '')) {
1646 $item->{'notforloan'} = 0;
1648 if (exists $item->{'damaged'} and
1649 (not defined $item->{'damaged'} or $item->{'damaged'} eq '')) {
1650 $item->{'damaged'} = 0;
1652 if (exists $item->{'itemlost'} and
1653 (not defined $item->{'itemlost'} or $item->{'itemlost'} eq '')) {
1654 $item->{'itemlost'} = 0;
1656 if (exists $item->{'withdrawn'} and
1657 (not defined $item->{'withdrawn'} or $item->{'withdrawn'} eq '')) {
1658 $item->{'withdrawn'} = 0;
1660 if (exists $item->{location}
1661 and $item->{location} ne 'CART'
1662 and $item->{location} ne 'PROC'
1663 and not $item->{permanent_location}
1665 $item->{'permanent_location'} = $item->{'location'};
1667 if (exists $item->{'timestamp'}) {
1668 delete $item->{'timestamp'};
1672 =head2 _get_single_item_column
1674 _get_single_item_column($column, $itemnumber);
1676 Retrieves the value of a single column from an C<items>
1677 row specified by C<$itemnumber>.
1681 sub _get_single_item_column {
1683 my $itemnumber = shift;
1685 my $dbh = C4::Context->dbh;
1686 my $sth = $dbh->prepare("SELECT $column FROM items WHERE itemnumber = ?");
1687 $sth->execute($itemnumber);
1688 my ($value) = $sth->fetchrow();
1692 =head2 _calc_items_cn_sort
1694 _calc_items_cn_sort($item, $source_values);
1696 Helper routine to calculate C<items.cn_sort>.
1700 sub _calc_items_cn_sort {
1702 my $source_values = shift;
1704 $item->{'items.cn_sort'} = GetClassSort($source_values->{'items.cn_source'}, $source_values->{'itemcallnumber'}, "");
1707 =head2 _set_defaults_for_add
1709 _set_defaults_for_add($item_hash);
1711 Given an item hash representing an item to be added, set
1712 correct default values for columns whose default value
1713 is not handled by the DBMS. This includes the following
1720 C<items.dateaccessioned>
1742 sub _set_defaults_for_add {
1744 $item->{dateaccessioned} ||= output_pref({ dt => dt_from_string, dateformat => 'iso', dateonly => 1 });
1745 $item->{$_} ||= 0 for (qw( notforloan damaged itemlost withdrawn));
1748 =head2 _koha_new_item
1750 my ($itemnumber,$error) = _koha_new_item( $item, $barcode );
1752 Perform the actual insert into the C<items> table.
1756 sub _koha_new_item {
1757 my ( $item, $barcode ) = @_;
1758 my $dbh=C4::Context->dbh;
1760 $item->{permanent_location} //= $item->{location};
1761 _mod_item_dates( $item );
1763 "INSERT INTO items SET
1765 biblioitemnumber = ?,
1767 dateaccessioned = ?,
1771 replacementprice = ?,
1772 replacementpricedate = ?,
1773 datelastborrowed = ?,
1781 coded_location_qualifier = ?,
1784 itemnotes_nonpublic = ?,
1788 permanent_location = ?,
1800 more_subfields_xml = ?,
1805 my $sth = $dbh->prepare($query);
1806 my $today = output_pref({ dt => dt_from_string, dateformat => 'iso', dateonly => 1 });
1808 $item->{'biblionumber'},
1809 $item->{'biblioitemnumber'},
1811 $item->{'dateaccessioned'},
1812 $item->{'booksellerid'},
1813 $item->{'homebranch'},
1815 $item->{'replacementprice'},
1816 $item->{'replacementpricedate'} || $today,
1817 $item->{datelastborrowed},
1818 $item->{datelastseen} || $today,
1820 $item->{'notforloan'},
1822 $item->{'itemlost'},
1823 $item->{'withdrawn'},
1824 $item->{'itemcallnumber'},
1825 $item->{'coded_location_qualifier'},
1826 $item->{'restricted'},
1827 $item->{'itemnotes'},
1828 $item->{'itemnotes_nonpublic'},
1829 $item->{'holdingbranch'},
1831 $item->{'location'},
1832 $item->{'permanent_location'},
1835 $item->{'renewals'},
1836 $item->{'reserves'},
1837 $item->{'items.cn_source'},
1838 $item->{'items.cn_sort'},
1841 $item->{'materials'},
1843 $item->{'enumchron'},
1844 $item->{'more_subfields_xml'},
1845 $item->{'copynumber'},
1846 $item->{'stocknumber'},
1847 $item->{'new_status'},
1851 if ( defined $sth->errstr ) {
1852 $error.="ERROR in _koha_new_item $query".$sth->errstr;
1855 $itemnumber = $dbh->{'mysql_insertid'};
1858 return ( $itemnumber, $error );
1861 =head2 MoveItemFromBiblio
1863 MoveItemFromBiblio($itenumber, $frombiblio, $tobiblio);
1865 Moves an item from a biblio to another
1867 Returns undef if the move failed or the biblionumber of the destination record otherwise
1871 sub MoveItemFromBiblio {
1872 my ($itemnumber, $frombiblio, $tobiblio) = @_;
1873 my $dbh = C4::Context->dbh;
1874 my ( $tobiblioitem ) = $dbh->selectrow_array(q|
1875 SELECT biblioitemnumber
1877 WHERE biblionumber = ?
1878 |, undef, $tobiblio );
1879 my $return = $dbh->do(q|
1881 SET biblioitemnumber = ?,
1883 WHERE itemnumber = ?
1884 AND biblionumber = ?
1885 |, undef, $tobiblioitem, $tobiblio, $itemnumber, $frombiblio );
1887 ModZebra( $tobiblio, "specialUpdate", "biblioserver" );
1888 ModZebra( $frombiblio, "specialUpdate", "biblioserver" );
1889 # Checking if the item we want to move is in an order
1890 require C4::Acquisition;
1891 my $order = C4::Acquisition::GetOrderFromItemnumber($itemnumber);
1893 # Replacing the biblionumber within the order if necessary
1894 $order->{'biblionumber'} = $tobiblio;
1895 C4::Acquisition::ModOrder($order);
1898 # Update reserves, hold_fill_targets, tmp_holdsqueue and linktracker tables
1899 for my $table_name ( qw( reserves hold_fill_targets tmp_holdsqueue linktracker ) ) {
1902 SET biblionumber = ?
1903 WHERE itemnumber = ?
1904 |, undef, $tobiblio, $itemnumber );
1911 =head2 ItemSafeToDelete
1913 ItemSafeToDelete( $biblionumber, $itemnumber);
1915 Exported function (core API) for checking whether an item record is safe to delete.
1917 returns 1 if the item is safe to delete,
1919 "book_on_loan" if the item is checked out,
1921 "not_same_branch" if the item is blocked by independent branches,
1923 "book_reserved" if the there are holds aganst the item, or
1925 "linked_analytics" if the item has linked analytic records.
1929 sub ItemSafeToDelete {
1930 my ( $biblionumber, $itemnumber ) = @_;
1932 my $dbh = C4::Context->dbh;
1936 my $countanalytics = GetAnalyticsCount($itemnumber);
1938 # check that there is no issue on this item before deletion.
1939 my $sth = $dbh->prepare(
1941 SELECT COUNT(*) FROM issues
1942 WHERE itemnumber = ?
1945 $sth->execute($itemnumber);
1946 my ($onloan) = $sth->fetchrow;
1948 my $item = GetItem($itemnumber);
1951 $status = "book_on_loan";
1953 elsif ( defined C4::Context->userenv
1954 and !C4::Context->IsSuperLibrarian()
1955 and C4::Context->preference("IndependentBranches")
1956 and ( C4::Context->userenv->{branch} ne $item->{'homebranch'} ) )
1958 $status = "not_same_branch";
1961 # check it doesn't have a waiting reserve
1962 $sth = $dbh->prepare(
1964 SELECT COUNT(*) FROM reserves
1965 WHERE (found = 'W' OR found = 'T')
1969 $sth->execute($itemnumber);
1970 my ($reserve) = $sth->fetchrow;
1972 $status = "book_reserved";
1974 elsif ( $countanalytics > 0 ) {
1975 $status = "linked_analytics";
1986 DelItemCheck( $biblionumber, $itemnumber);
1988 Exported function (core API) for deleting an item record in Koha if there no current issue.
1990 DelItemCheck wraps ItemSafeToDelete around DelItem.
1995 my ( $biblionumber, $itemnumber ) = @_;
1996 my $status = ItemSafeToDelete( $biblionumber, $itemnumber );
1998 if ( $status == 1 ) {
2001 biblionumber => $biblionumber,
2002 itemnumber => $itemnumber
2009 =head2 _koha_modify_item
2011 my ($itemnumber,$error) =_koha_modify_item( $item );
2013 Perform the actual update of the C<items> row. Note that this
2014 routine accepts a hashref specifying the columns to update.
2018 sub _koha_modify_item {
2020 my $dbh=C4::Context->dbh;
2023 my $query = "UPDATE items SET ";
2025 _mod_item_dates( $item );
2026 for my $key ( keys %$item ) {
2027 next if ( $key eq 'itemnumber' );
2029 push @bind, $item->{$key};
2032 $query .= " WHERE itemnumber=?";
2033 push @bind, $item->{'itemnumber'};
2034 my $sth = $dbh->prepare($query);
2035 $sth->execute(@bind);
2037 $error.="ERROR in _koha_modify_item $query: ".$sth->errstr;
2040 return ($item->{'itemnumber'},$error);
2043 sub _mod_item_dates { # date formatting for date fields in item hash
2045 return if !$item || ref($item) ne 'HASH';
2048 { $_ =~ /^onloan$|^date|date$|datetime$/ }
2050 # Incl. dateaccessioned,replacementpricedate,datelastborrowed,datelastseen
2051 # NOTE: We do not (yet) have items fields ending with datetime
2052 # Fields with _on$ have been handled already
2054 foreach my $key ( @keys ) {
2055 next if !defined $item->{$key}; # skip undefs
2056 my $dt = eval { dt_from_string( $item->{$key} ) };
2057 # eval: dt_from_string will die on us if we pass illegal dates
2060 if( defined $dt && ref($dt) eq 'DateTime' ) {
2061 if( $key =~ /datetime/ ) {
2062 $newstr = DateTime::Format::MySQL->format_datetime($dt);
2064 $newstr = DateTime::Format::MySQL->format_date($dt);
2067 $item->{$key} = $newstr; # might be undef to clear garbage
2071 =head2 _koha_delete_item
2073 _koha_delete_item( $itemnum );
2075 Internal function to delete an item record from the koha tables
2079 sub _koha_delete_item {
2080 my ( $itemnum ) = @_;
2082 my $dbh = C4::Context->dbh;
2083 # save the deleted item to deleteditems table
2084 my $sth = $dbh->prepare("SELECT * FROM items WHERE itemnumber=?");
2085 $sth->execute($itemnum);
2086 my $data = $sth->fetchrow_hashref();
2088 # There is no item to delete
2089 return 0 unless $data;
2091 my $query = "INSERT INTO deleteditems SET ";
2093 foreach my $key ( keys %$data ) {
2094 next if ( $key eq 'timestamp' ); # timestamp will be set by db
2095 $query .= "$key = ?,";
2096 push( @bind, $data->{$key} );
2099 $sth = $dbh->prepare($query);
2100 $sth->execute(@bind);
2102 # delete from items table
2103 $sth = $dbh->prepare("DELETE FROM items WHERE itemnumber=?");
2104 my $deleted = $sth->execute($itemnum);
2105 return ( $deleted == 1 ) ? 1 : 0;
2108 =head2 _marc_from_item_hash
2110 my $item_marc = _marc_from_item_hash($item, $frameworkcode[, $unlinked_item_subfields]);
2112 Given an item hash representing a complete item record,
2113 create a C<MARC::Record> object containing an embedded
2114 tag representing that item.
2116 The third, optional parameter C<$unlinked_item_subfields> is
2117 an arrayref of subfields (not mapped to C<items> fields per the
2118 framework) to be added to the MARC representation
2123 sub _marc_from_item_hash {
2125 my $frameworkcode = shift;
2126 my $unlinked_item_subfields;
2128 $unlinked_item_subfields = shift;
2131 # Tack on 'items.' prefix to column names so lookup from MARC frameworks will work
2132 # Also, don't emit a subfield if the underlying field is blank.
2133 my $mungeditem = { map { (defined($item->{$_}) and $item->{$_} ne '') ?
2134 (/^items\./ ? ($_ => $item->{$_}) : ("items.$_" => $item->{$_}))
2135 : () } keys %{ $item } };
2137 my $item_marc = MARC::Record->new();
2138 foreach my $item_field ( keys %{$mungeditem} ) {
2139 my ( $tag, $subfield ) = C4::Biblio::GetMarcFromKohaField( $item_field, $frameworkcode );
2140 next unless defined $tag and defined $subfield; # skip if not mapped to MARC field
2141 my @values = split(/\s?\|\s?/, $mungeditem->{$item_field}, -1);
2142 foreach my $value (@values){
2143 if ( my $field = $item_marc->field($tag) ) {
2144 $field->add_subfields( $subfield => $value );
2146 my $add_subfields = [];
2147 if (defined $unlinked_item_subfields and ref($unlinked_item_subfields) eq 'ARRAY' and $#$unlinked_item_subfields > -1) {
2148 $add_subfields = $unlinked_item_subfields;
2150 $item_marc->add_fields( $tag, " ", " ", $subfield => $value, @$add_subfields );
2158 =head2 _repack_item_errors
2160 Add an error message hash generated by C<CheckItemPreSave>
2161 to a list of errors.
2165 sub _repack_item_errors {
2166 my $item_sequence_num = shift;
2167 my $item_ref = shift;
2168 my $error_ref = shift;
2170 my @repacked_errors = ();
2172 foreach my $error_code (sort keys %{ $error_ref }) {
2173 my $repacked_error = {};
2174 $repacked_error->{'item_sequence'} = $item_sequence_num;
2175 $repacked_error->{'item_barcode'} = exists($item_ref->{'barcode'}) ? $item_ref->{'barcode'} : '';
2176 $repacked_error->{'error_code'} = $error_code;
2177 $repacked_error->{'error_information'} = $error_ref->{$error_code};
2178 push @repacked_errors, $repacked_error;
2181 return @repacked_errors;
2184 =head2 _get_unlinked_item_subfields
2186 my $unlinked_item_subfields = _get_unlinked_item_subfields($original_item_marc, $frameworkcode);
2190 sub _get_unlinked_item_subfields {
2191 my $original_item_marc = shift;
2192 my $frameworkcode = shift;
2194 my $marcstructure = GetMarcStructure(1, $frameworkcode, { unsafe => 1 });
2196 # assume that this record has only one field, and that that
2197 # field contains only the item information
2199 my @fields = $original_item_marc->fields();
2200 if ($#fields > -1) {
2201 my $field = $fields[0];
2202 my $tag = $field->tag();
2203 foreach my $subfield ($field->subfields()) {
2204 if (defined $subfield->[1] and
2205 $subfield->[1] ne '' and
2206 !$marcstructure->{$tag}->{$subfield->[0]}->{'kohafield'}) {
2207 push @$subfields, $subfield->[0] => $subfield->[1];
2214 =head2 _get_unlinked_subfields_xml
2216 my $unlinked_subfields_xml = _get_unlinked_subfields_xml($unlinked_item_subfields);
2220 sub _get_unlinked_subfields_xml {
2221 my $unlinked_item_subfields = shift;
2224 if (defined $unlinked_item_subfields and ref($unlinked_item_subfields) eq 'ARRAY' and $#$unlinked_item_subfields > -1) {
2225 my $marc = MARC::Record->new();
2226 # use of tag 999 is arbitrary, and doesn't need to match the item tag
2227 # used in the framework
2228 $marc->append_fields(MARC::Field->new('999', ' ', ' ', @$unlinked_item_subfields));
2229 $marc->encoding("UTF-8");
2230 $xml = $marc->as_xml("USMARC");
2236 =head2 _parse_unlinked_item_subfields_from_xml
2238 my $unlinked_item_subfields = _parse_unlinked_item_subfields_from_xml($whole_item->{'more_subfields_xml'}):
2242 sub _parse_unlinked_item_subfields_from_xml {
2244 require C4::Charset;
2245 return unless defined $xml and $xml ne "";
2246 my $marc = MARC::Record->new_from_xml(C4::Charset::StripNonXmlChars($xml),'UTF-8');
2247 my $unlinked_subfields = [];
2248 my @fields = $marc->fields();
2249 if ($#fields > -1) {
2250 foreach my $subfield ($fields[0]->subfields()) {
2251 push @$unlinked_subfields, $subfield->[0] => $subfield->[1];
2254 return $unlinked_subfields;
2257 =head2 GetAnalyticsCount
2259 $count= &GetAnalyticsCount($itemnumber)
2261 counts Usage of itemnumber in Analytical bibliorecords.
2265 sub GetAnalyticsCount {
2266 my ($itemnumber) = @_;
2268 ### ZOOM search here
2270 $query= "hi=".$itemnumber;
2271 my $searcher = Koha::SearchEngine::Search->new({index => $Koha::SearchEngine::BIBLIOS_INDEX});
2272 my ($err,$res,$result) = $searcher->simple_search_compat($query,0,10);
2276 =head2 SearchItemsByField
2278 my $items = SearchItemsByField($field, $value);
2280 SearchItemsByField will search for items on a specific given field.
2281 For instance you can search all items with a specific stocknumber like this:
2283 my $items = SearchItemsByField('stocknumber', $stocknumber);
2287 sub SearchItemsByField {
2288 my ($field, $value) = @_;
2295 my ($results) = SearchItems($filters);
2299 sub _SearchItems_build_where_fragment {
2302 my $dbh = C4::Context->dbh;
2305 if (exists($filter->{conjunction})) {
2306 my (@where_strs, @where_args);
2307 foreach my $f (@{ $filter->{filters} }) {
2308 my $fragment = _SearchItems_build_where_fragment($f);
2310 push @where_strs, $fragment->{str};
2311 push @where_args, @{ $fragment->{args} };
2316 $where_str = '(' . join (' ' . $filter->{conjunction} . ' ', @where_strs) . ')';
2319 args => \@where_args,
2323 my @columns = Koha::Database->new()->schema()->resultset('Item')->result_source->columns;
2324 push @columns, Koha::Database->new()->schema()->resultset('Biblio')->result_source->columns;
2325 push @columns, Koha::Database->new()->schema()->resultset('Biblioitem')->result_source->columns;
2326 my @operators = qw(= != > < >= <= like);
2327 my $field = $filter->{field};
2328 if ( (0 < grep /^$field$/, @columns) or (substr($field, 0, 5) eq 'marc:') ) {
2329 my $op = $filter->{operator};
2330 my $query = $filter->{query};
2332 if (!$op or (0 == grep /^$op$/, @operators)) {
2333 $op = '='; # default operator
2337 if ($field =~ /^marc:(\d{3})(?:\$(\w))?$/) {
2339 my $marcsubfield = $2;
2340 my ($kohafield) = $dbh->selectrow_array(q|
2341 SELECT kohafield FROM marc_subfield_structure
2342 WHERE tagfield=? AND tagsubfield=? AND frameworkcode=''
2343 |, undef, $marcfield, $marcsubfield);
2346 $column = $kohafield;
2348 # MARC field is not linked to a DB field so we need to use
2349 # ExtractValue on marcxml from biblio_metadata or
2350 # items.more_subfields_xml, depending on the MARC field.
2353 my ($itemfield) = C4::Biblio::GetMarcFromKohaField('items.itemnumber');
2354 if ($marcfield eq $itemfield) {
2355 $sqlfield = 'more_subfields_xml';
2356 $xpath = '//record/datafield/subfield[@code="' . $marcsubfield . '"]';
2358 $sqlfield = 'metadata'; # From biblio_metadata
2359 if ($marcfield < 10) {
2360 $xpath = "//record/controlfield[\@tag=\"$marcfield\"]";
2362 $xpath = "//record/datafield[\@tag=\"$marcfield\"]/subfield[\@code=\"$marcsubfield\"]";
2365 $column = "ExtractValue($sqlfield, '$xpath')";
2371 if (ref $query eq 'ARRAY') {
2374 } elsif ($op eq '!=') {
2378 str => "$column $op (" . join (',', ('?') x @$query) . ")",
2383 str => "$column $op ?",
2390 return $where_fragment;
2395 my ($items, $total) = SearchItems($filter, $params);
2397 Perform a search among items
2399 $filter is a reference to a hash which can be a filter, or a combination of filters.
2401 A filter has the following keys:
2405 =item * field: the name of a SQL column in table items
2407 =item * query: the value to search in this column
2409 =item * operator: comparison operator. Can be one of = != > < >= <= like
2413 A combination of filters hash the following keys:
2417 =item * conjunction: 'AND' or 'OR'
2419 =item * filters: array ref of filters
2423 $params is a reference to a hash that can contain the following parameters:
2427 =item * rows: Number of items to return. 0 returns everything (default: 0)
2429 =item * page: Page to return (return items from (page-1)*rows to (page*rows)-1)
2432 =item * sortby: A SQL column name in items table to sort on
2434 =item * sortorder: 'ASC' or 'DESC'
2441 my ($filter, $params) = @_;
2445 return unless ref $filter eq 'HASH';
2446 return unless ref $params eq 'HASH';
2448 # Default parameters
2449 $params->{rows} ||= 0;
2450 $params->{page} ||= 1;
2451 $params->{sortby} ||= 'itemnumber';
2452 $params->{sortorder} ||= 'ASC';
2454 my ($where_str, @where_args);
2455 my $where_fragment = _SearchItems_build_where_fragment($filter);
2456 if ($where_fragment) {
2457 $where_str = $where_fragment->{str};
2458 @where_args = @{ $where_fragment->{args} };
2461 my $dbh = C4::Context->dbh;
2463 SELECT SQL_CALC_FOUND_ROWS items.*
2465 LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber
2466 LEFT JOIN biblioitems ON biblioitems.biblioitemnumber = items.biblioitemnumber
2467 LEFT JOIN biblio_metadata ON biblio_metadata.biblionumber = biblio.biblionumber
2470 if (defined $where_str and $where_str ne '') {
2471 $query .= qq{ AND $where_str };
2474 $query .= q{ AND biblio_metadata.format = 'marcxml' AND biblio_metadata.marcflavour = ? };
2475 push @where_args, C4::Context->preference('marcflavour');
2477 my @columns = Koha::Database->new()->schema()->resultset('Item')->result_source->columns;
2478 push @columns, Koha::Database->new()->schema()->resultset('Biblio')->result_source->columns;
2479 push @columns, Koha::Database->new()->schema()->resultset('Biblioitem')->result_source->columns;
2480 my $sortby = (0 < grep {$params->{sortby} eq $_} @columns)
2481 ? $params->{sortby} : 'itemnumber';
2482 my $sortorder = (uc($params->{sortorder}) eq 'ASC') ? 'ASC' : 'DESC';
2483 $query .= qq{ ORDER BY $sortby $sortorder };
2485 my $rows = $params->{rows};
2488 my $offset = $rows * ($params->{page}-1);
2489 $query .= qq { LIMIT ?, ? };
2490 push @limit_args, $offset, $rows;
2493 my $sth = $dbh->prepare($query);
2494 my $rv = $sth->execute(@where_args, @limit_args);
2496 return unless ($rv);
2497 my ($total_rows) = $dbh->selectrow_array(q{ SELECT FOUND_ROWS() });
2499 return ($sth->fetchall_arrayref({}), $total_rows);
2503 =head1 OTHER FUNCTIONS
2507 ($indicators, $value) = _find_value($tag, $subfield, $record,$encoding);
2509 Find the given $subfield in the given $tag in the given
2510 MARC::Record $record. If the subfield is found, returns
2511 the (indicators, value) pair; otherwise, (undef, undef) is
2515 Such a function is used in addbiblio AND additem and serial-edit and maybe could be used in Authorities.
2516 I suggest we export it from this module.
2521 my ( $tagfield, $insubfield, $record, $encoding ) = @_;
2524 if ( $tagfield < 10 ) {
2525 if ( $record->field($tagfield) ) {
2526 push @result, $record->field($tagfield)->data();
2531 foreach my $field ( $record->field($tagfield) ) {
2532 my @subfields = $field->subfields();
2533 foreach my $subfield (@subfields) {
2534 if ( @$subfield[0] eq $insubfield ) {
2535 push @result, @$subfield[1];
2536 $indicator = $field->indicator(1) . $field->indicator(2);
2541 return ( $indicator, @result );
2545 =head2 PrepareItemrecordDisplay
2547 PrepareItemrecordDisplay($itemrecord,$bibnum,$itemumber,$frameworkcode);
2549 Returns a hash with all the fields for Display a given item data in a template
2551 The $frameworkcode returns the item for the given frameworkcode, ONLY if bibnum is not provided
2555 sub PrepareItemrecordDisplay {
2557 my ( $bibnum, $itemnum, $defaultvalues, $frameworkcode ) = @_;
2559 my $dbh = C4::Context->dbh;
2560 $frameworkcode = C4::Biblio::GetFrameworkCode($bibnum) if $bibnum;
2561 my ( $itemtagfield, $itemtagsubfield ) = C4::Biblio::GetMarcFromKohaField( "items.itemnumber", $frameworkcode );
2563 # Note: $tagslib obtained from GetMarcStructure() in 'unsafe' mode is
2564 # a shared data structure. No plugin (including custom ones) should change
2565 # its contents. See also GetMarcStructure.
2566 my $tagslib = &GetMarcStructure( 1, $frameworkcode, { unsafe => 1 } );
2568 # return nothing if we don't have found an existing framework.
2569 return q{} unless $tagslib;
2572 $itemrecord = C4::Items::GetMarcItem( $bibnum, $itemnum );
2576 my $branch_limit = C4::Context->userenv ? C4::Context->userenv->{"branch"} : "";
2578 SELECT authorised_value,lib FROM authorised_values
2581 LEFT JOIN authorised_values_branches ON ( id = av_id )
2586 $query .= qq{ AND ( branchcode = ? OR branchcode IS NULL )} if $branch_limit;
2587 $query .= qq{ ORDER BY lib};
2588 my $authorised_values_sth = $dbh->prepare( $query );
2589 foreach my $tag ( sort keys %{$tagslib} ) {
2592 # loop through each subfield
2594 foreach my $subfield ( sort keys %{ $tagslib->{$tag} } ) {
2595 next if IsMarcStructureInternal($tagslib->{$tag}{$subfield});
2596 next unless ( $tagslib->{$tag}->{$subfield}->{'tab'} );
2597 next if ( $tagslib->{$tag}->{$subfield}->{'tab'} ne "10" );
2599 $subfield_data{tag} = $tag;
2600 $subfield_data{subfield} = $subfield;
2601 $subfield_data{countsubfield} = $cntsubf++;
2602 $subfield_data{kohafield} = $tagslib->{$tag}->{$subfield}->{'kohafield'};
2603 $subfield_data{id} = "tag_".$tag."_subfield_".$subfield."_".int(rand(1000000));
2605 # $subfield_data{marc_lib}=$tagslib->{$tag}->{$subfield}->{lib};
2606 $subfield_data{marc_lib} = $tagslib->{$tag}->{$subfield}->{lib};
2607 $subfield_data{mandatory} = $tagslib->{$tag}->{$subfield}->{mandatory};
2608 $subfield_data{repeatable} = $tagslib->{$tag}->{$subfield}->{repeatable};
2609 $subfield_data{hidden} = "display:none"
2610 if ( ( $tagslib->{$tag}->{$subfield}->{hidden} > 4 )
2611 || ( $tagslib->{$tag}->{$subfield}->{hidden} < -4 ) );
2612 my ( $x, $defaultvalue );
2614 ( $x, $defaultvalue ) = _find_value( $tag, $subfield, $itemrecord );
2616 $defaultvalue = $tagslib->{$tag}->{$subfield}->{defaultvalue} unless $defaultvalue;
2617 if ( !defined $defaultvalue ) {
2618 $defaultvalue = q||;
2620 $defaultvalue =~ s/"/"/g;
2623 # search for itemcallnumber if applicable
2624 if ( $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.itemcallnumber'
2625 && C4::Context->preference('itemcallnumber') ) {
2626 my $CNtag = substr( C4::Context->preference('itemcallnumber'), 0, 3 );
2627 my $CNsubfield = substr( C4::Context->preference('itemcallnumber'), 3, 1 );
2628 if ( $itemrecord and my $field = $itemrecord->field($CNtag) ) {
2629 $defaultvalue = $field->subfield($CNsubfield);
2632 if ( $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.itemcallnumber'
2634 && $defaultvalues->{'callnumber'} ) {
2635 if( $itemrecord and $defaultvalues and not $itemrecord->subfield($tag,$subfield) ){
2636 # if the item record exists, only use default value if the item has no callnumber
2637 $defaultvalue = $defaultvalues->{callnumber};
2638 } elsif ( !$itemrecord and $defaultvalues ) {
2639 # if the item record *doesn't* exists, always use the default value
2640 $defaultvalue = $defaultvalues->{callnumber};
2643 if ( ( $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.holdingbranch' || $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.homebranch' )
2645 && $defaultvalues->{'branchcode'} ) {
2646 if ( $itemrecord and $defaultvalues and not $itemrecord->subfield($tag,$subfield) ) {
2647 $defaultvalue = $defaultvalues->{branchcode};
2650 if ( ( $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.location' )
2652 && $defaultvalues->{'location'} ) {
2654 if ( $itemrecord and $defaultvalues and not $itemrecord->subfield($tag,$subfield) ) {
2655 # if the item record exists, only use default value if the item has no locationr
2656 $defaultvalue = $defaultvalues->{location};
2657 } elsif ( !$itemrecord and $defaultvalues ) {
2658 # if the item record *doesn't* exists, always use the default value
2659 $defaultvalue = $defaultvalues->{location};
2662 if ( $tagslib->{$tag}->{$subfield}->{authorised_value} ) {
2663 my @authorised_values;
2666 # builds list, depending on authorised value...
2668 if ( $tagslib->{$tag}->{$subfield}->{'authorised_value'} eq "branches" ) {
2669 if ( ( C4::Context->preference("IndependentBranches") )
2670 && !C4::Context->IsSuperLibrarian() ) {
2671 my $sth = $dbh->prepare( "SELECT branchcode,branchname FROM branches WHERE branchcode = ? ORDER BY branchname" );
2672 $sth->execute( C4::Context->userenv->{branch} );
2673 push @authorised_values, ""
2674 unless ( $tagslib->{$tag}->{$subfield}->{mandatory} );
2675 while ( my ( $branchcode, $branchname ) = $sth->fetchrow_array ) {
2676 push @authorised_values, $branchcode;
2677 $authorised_lib{$branchcode} = $branchname;
2680 my $sth = $dbh->prepare( "SELECT branchcode,branchname FROM branches ORDER BY branchname" );
2682 push @authorised_values, ""
2683 unless ( $tagslib->{$tag}->{$subfield}->{mandatory} );
2684 while ( my ( $branchcode, $branchname ) = $sth->fetchrow_array ) {
2685 push @authorised_values, $branchcode;
2686 $authorised_lib{$branchcode} = $branchname;
2690 $defaultvalue = C4::Context->userenv ? C4::Context->userenv->{branch} : undef;
2691 if ( $defaultvalues and $defaultvalues->{branchcode} ) {
2692 $defaultvalue = $defaultvalues->{branchcode};
2696 } elsif ( $tagslib->{$tag}->{$subfield}->{authorised_value} eq "itemtypes" ) {
2697 my $itemtypes = Koha::ItemTypes->search_with_localization;
2698 push @authorised_values, ""
2699 unless ( $tagslib->{$tag}->{$subfield}->{mandatory} );
2700 while ( my $itemtype = $itemtypes->next ) {
2701 push @authorised_values, $itemtype->itemtype;
2702 $authorised_lib{$itemtype->itemtype} = $itemtype->translated_description;
2704 if ($defaultvalues && $defaultvalues->{'itemtype'}) {
2705 $defaultvalue = $defaultvalues->{'itemtype'};
2709 } elsif ( $tagslib->{$tag}->{$subfield}->{authorised_value} eq "cn_source" ) {
2710 push @authorised_values, "" unless ( $tagslib->{$tag}->{$subfield}->{mandatory} );
2712 my $class_sources = GetClassSources();
2713 my $default_source = C4::Context->preference("DefaultClassificationSource");
2715 foreach my $class_source (sort keys %$class_sources) {
2716 next unless $class_sources->{$class_source}->{'used'} or
2717 ($class_source eq $default_source);
2718 push @authorised_values, $class_source;
2719 $authorised_lib{$class_source} = $class_sources->{$class_source}->{'description'};
2722 $defaultvalue = $default_source;
2724 #---- "true" authorised value
2726 $authorised_values_sth->execute(
2727 $tagslib->{$tag}->{$subfield}->{authorised_value},
2728 $branch_limit ? $branch_limit : ()
2730 push @authorised_values, ""
2731 unless ( $tagslib->{$tag}->{$subfield}->{mandatory} );
2732 while ( my ( $value, $lib ) = $authorised_values_sth->fetchrow_array ) {
2733 push @authorised_values, $value;
2734 $authorised_lib{$value} = $lib;
2737 $subfield_data{marc_value} = {
2739 values => \@authorised_values,
2740 default => "$defaultvalue",
2741 labels => \%authorised_lib,
2743 } elsif ( $tagslib->{$tag}->{$subfield}->{value_builder} ) {
2745 require Koha::FrameworkPlugin;
2746 my $plugin = Koha::FrameworkPlugin->new({
2747 name => $tagslib->{$tag}->{$subfield}->{value_builder},
2750 my $pars = { dbh => $dbh, record => undef, tagslib =>$tagslib, id => $subfield_data{id}, tabloop => undef };
2751 $plugin->build( $pars );
2752 if ( $itemrecord and my $field = $itemrecord->field($tag) ) {
2753 $defaultvalue = $field->subfield($subfield);
2755 if( !$plugin->errstr ) {
2756 #TODO Move html to template; see report 12176/13397
2757 my $tab= $plugin->noclick? '-1': '';
2758 my $class= $plugin->noclick? ' disabled': '';
2759 my $title= $plugin->noclick? 'No popup': 'Tag editor';
2760 $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;
2762 warn $plugin->errstr;
2763 $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
2766 elsif ( $tag eq '' ) { # it's an hidden field
2767 $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" />);
2769 elsif ( $tagslib->{$tag}->{$subfield}->{'hidden'} ) { # FIXME: shouldn't input type be "hidden" ?
2770 $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" />);
2772 elsif ( length($defaultvalue) > 100
2773 or (C4::Context->preference("marcflavour") eq "UNIMARC" and
2774 300 <= $tag && $tag < 400 && $subfield eq 'a' )
2775 or (C4::Context->preference("marcflavour") eq "MARC21" and
2776 500 <= $tag && $tag < 600 )
2778 # oversize field (textarea)
2779 $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");
2781 $subfield_data{marc_value} = "<input type=\"text\" name=\"field_value\" value=\"$defaultvalue\" size=\"50\" maxlength=\"255\" />";
2783 push( @loop_data, \%subfield_data );
2788 if ( $itemrecord && $itemrecord->field($itemtagfield) ) {
2789 $itemnumber = $itemrecord->subfield( $itemtagfield, $itemtagsubfield );
2792 'itemtagfield' => $itemtagfield,
2793 'itemtagsubfield' => $itemtagsubfield,
2794 'itemnumber' => $itemnumber,
2795 'iteminformation' => \@loop_data
2799 sub ToggleNewStatus {
2800 my ( $params ) = @_;
2801 my @rules = @{ $params->{rules} };
2802 my $report_only = $params->{report_only};
2804 my $dbh = C4::Context->dbh;
2806 my @item_columns = map { "items.$_" } Koha::Items->columns;
2807 my @biblioitem_columns = map { "biblioitems.$_" } Koha::Biblioitems->columns;
2809 for my $rule ( @rules ) {
2810 my $age = $rule->{age};
2811 my $conditions = $rule->{conditions};
2812 my $substitutions = $rule->{substitutions};
2816 SELECT items.biblionumber, items.itemnumber
2818 LEFT JOIN biblioitems ON biblioitems.biblionumber = items.biblionumber
2821 for my $condition ( @$conditions ) {
2823 grep {/^$condition->{field}$/} @item_columns
2824 or grep {/^$condition->{field}$/} @biblioitem_columns
2826 if ( $condition->{value} =~ /\|/ ) {
2827 my @values = split /\|/, $condition->{value};
2828 $query .= qq| AND $condition->{field} IN (|
2829 . join( ',', ('?') x scalar @values )
2831 push @params, @values;
2833 $query .= qq| AND $condition->{field} = ?|;
2834 push @params, $condition->{value};
2838 if ( defined $age ) {
2839 $query .= q| AND TO_DAYS(NOW()) - TO_DAYS(dateaccessioned) >= ? |;
2842 my $sth = $dbh->prepare($query);
2843 $sth->execute( @params );
2844 while ( my $values = $sth->fetchrow_hashref ) {
2845 my $biblionumber = $values->{biblionumber};
2846 my $itemnumber = $values->{itemnumber};
2847 my $item = C4::Items::GetItem( $itemnumber );
2848 for my $substitution ( @$substitutions ) {
2849 next unless $substitution->{field};
2850 C4::Items::ModItem( {$substitution->{field} => $substitution->{value}}, $biblionumber, $itemnumber )
2851 unless $report_only;
2852 push @{ $report->{$itemnumber} }, $substitution;