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 );
74 GetItemsByBiblioitemnumber
78 GetItemnumbersForBiblio
79 get_hostitemnumbers_of
80 GetItemnumberFromBarcode
81 GetBarcodeFromItemnumber
96 PrepareItemrecordDisplay
103 C4::Items - item management functions
107 This module contains an API for manipulating item
108 records in Koha, and is used by cataloguing, circulation,
109 acquisitions, and serials management.
111 # FIXME This POD is not up-to-date
112 A Koha item record is stored in two places: the
113 items table and embedded in a MARC tag in the XML
114 version of the associated bib record in C<biblioitems.marcxml>.
115 This is done to allow the item information to be readily
116 indexed (e.g., by Zebra), but means that each item
117 modification transaction must keep the items table
118 and the MARC XML in sync at all times.
120 Consequently, all code that creates, modifies, or deletes
121 item records B<must> use an appropriate function from
122 C<C4::Items>. If no existing function is suitable, it is
123 better to add one to C<C4::Items> than to use add
124 one-off SQL statements to add or modify items.
126 The items table will be considered authoritative. In other
127 words, if there is ever a discrepancy between the items
128 table and the MARC XML, the items table should be considered
131 =head1 HISTORICAL NOTE
133 Most of the functions in C<C4::Items> were originally in
134 the C<C4::Biblio> module.
136 =head1 CORE EXPORTED FUNCTIONS
138 The following functions are meant for use by users
145 $item = GetItem($itemnumber,$barcode,$serial);
147 Return item information, for a given itemnumber or barcode.
148 The return value is a hashref mapping item column
149 names to values. If C<$serial> is true, include serial publication data.
154 my ($itemnumber,$barcode, $serial) = @_;
155 my $dbh = C4::Context->dbh;
159 $item = Koha::Items->find( $itemnumber );
161 $item = Koha::Items->find( { barcode => $barcode } );
164 return unless ( $item );
166 my $data = $item->unblessed();
167 $data->{itype} = $item->effective_itemtype(); # set the correct itype
170 my $ssth = $dbh->prepare("SELECT serialseq,publisheddate from serialitems left join serial on serialitems.serialid=serial.serialid where serialitems.itemnumber=?");
171 $ssth->execute( $data->{'itemnumber'} );
172 ( $data->{'serialseq'}, $data->{'publisheddate'} ) = $ssth->fetchrow_array();
180 CartToShelf($itemnumber);
182 Set the current shelving location of the item record
183 to its stored permanent shelving location. This is
184 primarily used to indicate when an item whose current
185 location is a special processing ('PROC') or shelving cart
186 ('CART') location is back in the stacks.
191 my ( $itemnumber ) = @_;
193 unless ( $itemnumber ) {
194 croak "FAILED CartToShelf() - no itemnumber supplied";
197 my $item = GetItem($itemnumber);
198 if ( $item->{location} eq 'CART' ) {
199 $item->{location} = $item->{permanent_location};
200 ModItem($item, undef, $itemnumber);
206 ShelfToCart($itemnumber);
208 Set the current shelving location of the item
209 to shelving cart ('CART').
214 my ( $itemnumber ) = @_;
216 unless ( $itemnumber ) {
217 croak "FAILED ShelfToCart() - no itemnumber supplied";
220 my $item = GetItem($itemnumber);
221 $item->{'location'} = 'CART';
222 ModItem($item, undef, $itemnumber);
225 =head2 AddItemFromMarc
227 my ($biblionumber, $biblioitemnumber, $itemnumber)
228 = AddItemFromMarc($source_item_marc, $biblionumber);
230 Given a MARC::Record object containing an embedded item
231 record and a biblionumber, create a new item record.
235 sub AddItemFromMarc {
236 my ( $source_item_marc, $biblionumber ) = @_;
237 my $dbh = C4::Context->dbh;
239 # parse item hash from MARC
240 my $frameworkcode = GetFrameworkCode( $biblionumber );
241 my ($itemtag,$itemsubfield)=GetMarcFromKohaField("items.itemnumber",$frameworkcode);
243 my $localitemmarc=MARC::Record->new;
244 $localitemmarc->append_fields($source_item_marc->field($itemtag));
245 my $item = &TransformMarcToKoha( $localitemmarc, $frameworkcode ,'items');
246 my $unlinked_item_subfields = _get_unlinked_item_subfields($localitemmarc, $frameworkcode);
247 return AddItem($item, $biblionumber, $dbh, $frameworkcode, $unlinked_item_subfields);
252 my ($biblionumber, $biblioitemnumber, $itemnumber)
253 = AddItem($item, $biblionumber[, $dbh, $frameworkcode, $unlinked_item_subfields]);
255 Given a hash containing item column names as keys,
256 create a new Koha item record.
258 The first two optional parameters (C<$dbh> and C<$frameworkcode>)
259 do not need to be supplied for general use; they exist
260 simply to allow them to be picked up from AddItemFromMarc.
262 The final optional parameter, C<$unlinked_item_subfields>, contains
263 an arrayref containing subfields present in the original MARC
264 representation of the item (e.g., from the item editor) that are
265 not mapped to C<items> columns directly but should instead
266 be stored in C<items.more_subfields_xml> and included in
267 the biblio items tag for display and indexing.
273 my $biblionumber = shift;
275 my $dbh = @_ ? shift : C4::Context->dbh;
276 my $frameworkcode = @_ ? shift : GetFrameworkCode($biblionumber);
277 my $unlinked_item_subfields;
279 $unlinked_item_subfields = shift;
282 # needs old biblionumber and biblioitemnumber
283 $item->{'biblionumber'} = $biblionumber;
284 my $sth = $dbh->prepare("SELECT biblioitemnumber FROM biblioitems WHERE biblionumber=?");
285 $sth->execute( $item->{'biblionumber'} );
286 ( $item->{'biblioitemnumber'} ) = $sth->fetchrow;
288 _set_defaults_for_add($item);
289 _set_derived_columns_for_add($item);
290 $item->{'more_subfields_xml'} = _get_unlinked_subfields_xml($unlinked_item_subfields);
292 # FIXME - checks here
293 unless ( $item->{itype} ) { # default to biblioitem.itemtype if no itype
294 my $itype_sth = $dbh->prepare("SELECT itemtype FROM biblioitems WHERE biblionumber = ?");
295 $itype_sth->execute( $item->{'biblionumber'} );
296 ( $item->{'itype'} ) = $itype_sth->fetchrow_array;
299 my ( $itemnumber, $error ) = _koha_new_item( $item, $item->{barcode} );
302 $item->{'itemnumber'} = $itemnumber;
304 ModZebra( $item->{biblionumber}, "specialUpdate", "biblioserver" );
306 logaction( "CATALOGUING", "ADD", $itemnumber, "item" )
307 if C4::Context->preference("CataloguingLog");
309 return ( $item->{biblionumber}, $item->{biblioitemnumber}, $itemnumber );
312 =head2 AddItemBatchFromMarc
314 ($itemnumber_ref, $error_ref) = AddItemBatchFromMarc($record,
315 $biblionumber, $biblioitemnumber, $frameworkcode);
317 Efficiently create item records from a MARC biblio record with
318 embedded item fields. This routine is suitable for batch jobs.
320 This API assumes that the bib record has already been
321 saved to the C<biblio> and C<biblioitems> tables. It does
322 not expect that C<biblio_metadata.metadata> is populated, but it
323 will do so via a call to ModBibiloMarc.
325 The goal of this API is to have a similar effect to using AddBiblio
326 and AddItems in succession, but without inefficient repeated
327 parsing of the MARC XML bib record.
329 This function returns an arrayref of new itemsnumbers and an arrayref of item
330 errors encountered during the processing. Each entry in the errors
331 list is a hashref containing the following keys:
337 Sequence number of original item tag in the MARC record.
341 Item barcode, provide to assist in the construction of
342 useful error messages.
346 Code representing the error condition. Can be 'duplicate_barcode',
347 'invalid_homebranch', or 'invalid_holdingbranch'.
349 =item error_information
351 Additional information appropriate to the error condition.
357 sub AddItemBatchFromMarc {
358 my ($record, $biblionumber, $biblioitemnumber, $frameworkcode) = @_;
360 my @itemnumbers = ();
362 my $dbh = C4::Context->dbh;
364 # We modify the record, so lets work on a clone so we don't change the
366 $record = $record->clone();
367 # loop through the item tags and start creating items
368 my @bad_item_fields = ();
369 my ($itemtag, $itemsubfield) = &GetMarcFromKohaField("items.itemnumber",'');
370 my $item_sequence_num = 0;
371 ITEMFIELD: foreach my $item_field ($record->field($itemtag)) {
372 $item_sequence_num++;
373 # we take the item field and stick it into a new
374 # MARC record -- this is required so far because (FIXME)
375 # TransformMarcToKoha requires a MARC::Record, not a MARC::Field
376 # and there is no TransformMarcFieldToKoha
377 my $temp_item_marc = MARC::Record->new();
378 $temp_item_marc->append_fields($item_field);
380 # add biblionumber and biblioitemnumber
381 my $item = TransformMarcToKoha( $temp_item_marc, $frameworkcode, 'items' );
382 my $unlinked_item_subfields = _get_unlinked_item_subfields($temp_item_marc, $frameworkcode);
383 $item->{'more_subfields_xml'} = _get_unlinked_subfields_xml($unlinked_item_subfields);
384 $item->{'biblionumber'} = $biblionumber;
385 $item->{'biblioitemnumber'} = $biblioitemnumber;
387 # check for duplicate barcode
388 my %item_errors = CheckItemPreSave($item);
390 push @errors, _repack_item_errors($item_sequence_num, $item, \%item_errors);
391 push @bad_item_fields, $item_field;
395 _set_defaults_for_add($item);
396 _set_derived_columns_for_add($item);
397 my ( $itemnumber, $error ) = _koha_new_item( $item, $item->{barcode} );
398 warn $error if $error;
399 push @itemnumbers, $itemnumber; # FIXME not checking error
400 $item->{'itemnumber'} = $itemnumber;
402 logaction("CATALOGUING", "ADD", $itemnumber, "item") if C4::Context->preference("CataloguingLog");
404 my $new_item_marc = _marc_from_item_hash($item, $frameworkcode, $unlinked_item_subfields);
405 $item_field->replace_with($new_item_marc->field($itemtag));
408 # remove any MARC item fields for rejected items
409 foreach my $item_field (@bad_item_fields) {
410 $record->delete_field($item_field);
413 # update the MARC biblio
414 # $biblionumber = ModBiblioMarc( $record, $biblionumber, $frameworkcode );
416 return (\@itemnumbers, \@errors);
419 =head2 ModItemFromMarc
421 ModItemFromMarc($item_marc, $biblionumber, $itemnumber);
423 This function updates an item record based on a supplied
424 C<MARC::Record> object containing an embedded item field.
425 This API is meant for the use of C<additem.pl>; for
426 other purposes, C<ModItem> should be used.
428 This function uses the hash %default_values_for_mod_from_marc,
429 which contains default values for item fields to
430 apply when modifying an item. This is needed because
431 if an item field's value is cleared, TransformMarcToKoha
432 does not include the column in the
433 hash that's passed to ModItem, which without
434 use of this hash makes it impossible to clear
435 an item field's value. See bug 2466.
437 Note that only columns that can be directly
438 changed from the cataloging and serials
439 item editors are included in this hash.
445 sub _build_default_values_for_mod_marc {
446 my ($frameworkcode) = @_;
448 my $cache = Koha::Caches->get_instance();
449 my $cache_key = "default_value_for_mod_marc-$frameworkcode";
450 my $cached = $cache->get_from_cache($cache_key);
451 return $cached if $cached;
453 my $default_values = {
455 booksellerid => undef,
457 'items.cn_source' => undef,
458 coded_location_qualifier => undef,
462 holdingbranch => undef,
464 itemcallnumber => undef,
467 itemnotes_nonpublic => undef,
470 permanent_location => undef,
474 # paidfor => undef, # commented, see bug 12817
476 replacementprice => undef,
477 replacementpricedate => undef,
480 stocknumber => undef,
484 my %default_values_for_mod_from_marc;
485 while ( my ( $field, $default_value ) = each %$default_values ) {
486 my $kohafield = $field;
487 $kohafield =~ s|^([^\.]+)$|items.$1|;
488 $default_values_for_mod_from_marc{$field} =
490 if C4::Koha::IsKohaFieldLinked(
491 { kohafield => $kohafield, frameworkcode => $frameworkcode } );
494 $cache->set_in_cache($cache_key, \%default_values_for_mod_from_marc);
495 return \%default_values_for_mod_from_marc;
498 sub ModItemFromMarc {
499 my $item_marc = shift;
500 my $biblionumber = shift;
501 my $itemnumber = shift;
503 my $dbh = C4::Context->dbh;
504 my $frameworkcode = GetFrameworkCode($biblionumber);
505 my ( $itemtag, $itemsubfield ) = GetMarcFromKohaField( "items.itemnumber", $frameworkcode );
507 my $localitemmarc = MARC::Record->new;
508 $localitemmarc->append_fields( $item_marc->field($itemtag) );
509 my $item = &TransformMarcToKoha( $localitemmarc, $frameworkcode, 'items' );
510 my $default_values = _build_default_values_for_mod_marc($frameworkcode);
511 foreach my $item_field ( keys %$default_values ) {
512 $item->{$item_field} = $default_values->{$item_field}
513 unless exists $item->{$item_field};
515 my $unlinked_item_subfields = _get_unlinked_item_subfields( $localitemmarc, $frameworkcode );
517 ModItem($item, $biblionumber, $itemnumber, $dbh, $frameworkcode, $unlinked_item_subfields);
523 ModItem({ column => $newvalue }, $biblionumber, $itemnumber);
525 Change one or more columns in an item record and update
526 the MARC representation of the item.
528 The first argument is a hashref mapping from item column
529 names to the new values. The second and third arguments
530 are the biblionumber and itemnumber, respectively.
532 The fourth, optional parameter, C<$unlinked_item_subfields>, contains
533 an arrayref containing subfields present in the original MARC
534 representation of the item (e.g., from the item editor) that are
535 not mapped to C<items> columns directly but should instead
536 be stored in C<items.more_subfields_xml> and included in
537 the biblio items tag for display and indexing.
539 If one of the changed columns is used to calculate
540 the derived value of a column such as C<items.cn_sort>,
541 this routine will perform the necessary calculation
548 my $biblionumber = shift;
549 my $itemnumber = shift;
551 # if $biblionumber is undefined, get it from the current item
552 unless (defined $biblionumber) {
553 $biblionumber = _get_single_item_column('biblionumber', $itemnumber);
556 my $dbh = @_ ? shift : C4::Context->dbh;
557 my $frameworkcode = @_ ? shift : GetFrameworkCode( $biblionumber );
559 my $unlinked_item_subfields;
561 $unlinked_item_subfields = shift;
562 $item->{'more_subfields_xml'} = _get_unlinked_subfields_xml($unlinked_item_subfields);
565 $item->{'itemnumber'} = $itemnumber or return;
567 my @fields = qw( itemlost withdrawn );
569 # Only call GetItem if we need to set an "on" date field
570 if ( $item->{itemlost} || $item->{withdrawn} ) {
571 my $pre_mod_item = GetItem( $item->{'itemnumber'} );
572 for my $field (@fields) {
573 if ( defined( $item->{$field} )
574 and not $pre_mod_item->{$field}
575 and $item->{$field} )
577 $item->{ $field . '_on' } =
578 DateTime::Format::MySQL->format_datetime( dt_from_string() );
583 # If the field is defined but empty, we are removing and,
584 # and thus need to clear out the 'on' field as well
585 for my $field (@fields) {
586 if ( defined( $item->{$field} ) && !$item->{$field} ) {
587 $item->{ $field . '_on' } = undef;
592 _set_derived_columns_for_mod($item);
593 _do_column_fixes_for_mod($item);
596 # attempt to change itemnumber
597 # attempt to change biblionumber (if we want
598 # an API to relink an item to a different bib,
599 # it should be a separate function)
602 _koha_modify_item($item);
604 # request that bib be reindexed so that searching on current
605 # item status is possible
606 ModZebra( $biblionumber, "specialUpdate", "biblioserver" );
608 logaction("CATALOGUING", "MODIFY", $itemnumber, "item ".Dumper($item)) if C4::Context->preference("CataloguingLog");
611 =head2 ModItemTransfer
613 ModItemTransfer($itenumber, $frombranch, $tobranch);
615 Marks an item as being transferred from one branch
620 sub ModItemTransfer {
621 my ( $itemnumber, $frombranch, $tobranch ) = @_;
623 my $dbh = C4::Context->dbh;
625 # Remove the 'shelving cart' location status if it is being used.
626 CartToShelf( $itemnumber ) if ( C4::Context->preference("ReturnToShelvingCart") );
628 #new entry in branchtransfers....
629 my $sth = $dbh->prepare(
630 "INSERT INTO branchtransfers (itemnumber, frombranch, datesent, tobranch)
631 VALUES (?, ?, NOW(), ?)");
632 $sth->execute($itemnumber, $frombranch, $tobranch);
634 ModItem({ holdingbranch => $tobranch }, undef, $itemnumber);
635 ModDateLastSeen($itemnumber);
639 =head2 ModDateLastSeen
641 ModDateLastSeen($itemnum);
643 Mark item as seen. Is called when an item is issued, returned or manually marked during inventory/stocktaking.
644 C<$itemnum> is the item number
648 sub ModDateLastSeen {
649 my ($itemnumber) = @_;
651 my $today = output_pref({ dt => dt_from_string, dateformat => 'iso', dateonly => 1 });
652 ModItem({ itemlost => 0, datelastseen => $today }, undef, $itemnumber);
657 DelItem({ itemnumber => $itemnumber, [ biblionumber => $biblionumber ] } );
659 Exported function (core API) for deleting an item record in Koha.
666 my $itemnumber = $params->{itemnumber};
667 my $biblionumber = $params->{biblionumber};
669 unless ($biblionumber) {
670 $biblionumber = C4::Biblio::GetBiblionumberFromItemnumber($itemnumber);
673 # If there is no biblionumber for the given itemnumber, there is nothing to delete
674 return 0 unless $biblionumber;
676 # FIXME check the item has no current issues
677 my $deleted = _koha_delete_item( $itemnumber );
679 ModZebra( $biblionumber, "specialUpdate", "biblioserver" );
681 #search item field code
682 logaction("CATALOGUING", "DELETE", $itemnumber, "item") if C4::Context->preference("CataloguingLog");
686 =head2 CheckItemPreSave
688 my $item_ref = TransformMarcToKoha($marc, 'items');
690 my %errors = CheckItemPreSave($item_ref);
691 if (exists $errors{'duplicate_barcode'}) {
692 print "item has duplicate barcode: ", $errors{'duplicate_barcode'}, "\n";
693 } elsif (exists $errors{'invalid_homebranch'}) {
694 print "item has invalid home branch: ", $errors{'invalid_homebranch'}, "\n";
695 } elsif (exists $errors{'invalid_holdingbranch'}) {
696 print "item has invalid holding branch: ", $errors{'invalid_holdingbranch'}, "\n";
701 Given a hashref containing item fields, determine if it can be
702 inserted or updated in the database. Specifically, checks for
703 database integrity issues, and returns a hash containing any
704 of the following keys, if applicable.
708 =item duplicate_barcode
710 Barcode, if it duplicates one already found in the database.
712 =item invalid_homebranch
714 Home branch, if not defined in branches table.
716 =item invalid_holdingbranch
718 Holding branch, if not defined in branches table.
722 This function does NOT implement any policy-related checks,
723 e.g., whether current operator is allowed to save an
724 item that has a given branch code.
728 sub CheckItemPreSave {
729 my $item_ref = shift;
733 # check for duplicate barcode
734 if (exists $item_ref->{'barcode'} and defined $item_ref->{'barcode'}) {
735 my $existing_itemnumber = GetItemnumberFromBarcode($item_ref->{'barcode'});
736 if ($existing_itemnumber) {
737 if (!exists $item_ref->{'itemnumber'} # new item
738 or $item_ref->{'itemnumber'} != $existing_itemnumber) { # existing item
739 $errors{'duplicate_barcode'} = $item_ref->{'barcode'};
744 # check for valid home branch
745 if (exists $item_ref->{'homebranch'} and defined $item_ref->{'homebranch'}) {
746 my $home_library = Koha::Libraries->find( $item_ref->{homebranch} );
747 unless (defined $home_library) {
748 $errors{'invalid_homebranch'} = $item_ref->{'homebranch'};
752 # check for valid holding branch
753 if (exists $item_ref->{'holdingbranch'} and defined $item_ref->{'holdingbranch'}) {
754 my $holding_library = Koha::Libraries->find( $item_ref->{holdingbranch} );
755 unless (defined $holding_library) {
756 $errors{'invalid_holdingbranch'} = $item_ref->{'holdingbranch'};
764 =head1 EXPORTED SPECIAL ACCESSOR FUNCTIONS
766 The following functions provide various ways of
767 getting an item record, a set of item records, or
768 lists of authorized values for certain item fields.
770 Some of the functions in this group are candidates
771 for refactoring -- for example, some of the code
772 in C<GetItemsByBiblioitemnumber> and C<GetItemsInfo>
773 has copy-and-paste work.
779 $items = GetLostItems( $where );
781 This function gets a list of lost items.
787 C<$where> is a hashref. it containts a field of the items table as key
788 and the value to match as value. For example:
790 { barcode => 'abc123',
791 homebranch => 'CPL', }
795 C<$items> is a reference to an array full of hashrefs with columns
796 from the "items" table as keys.
798 =item usage in the perl script:
800 my $where = { barcode => '0001548' };
801 my $items = GetLostItems( $where );
802 $template->param( itemsloop => $items );
809 # Getting input args.
811 my $dbh = C4::Context->dbh;
814 SELECT title, author, lib, itemlost, authorised_value, barcode, datelastseen, price, replacementprice, homebranch,
815 itype, itemtype, holdingbranch, location, itemnotes, items.biblionumber as biblionumber, itemcallnumber
817 LEFT JOIN biblio ON (items.biblionumber = biblio.biblionumber)
818 LEFT JOIN biblioitems ON (items.biblionumber = biblioitems.biblionumber)
819 LEFT JOIN authorised_values ON (items.itemlost = authorised_values.authorised_value)
821 authorised_values.category = 'LOST'
822 AND itemlost IS NOT NULL
825 my @query_parameters;
826 foreach my $key (keys %$where) {
827 $query .= " AND $key LIKE ?";
828 push @query_parameters, "%$where->{$key}%";
831 my $sth = $dbh->prepare($query);
832 $sth->execute( @query_parameters );
834 while ( my $row = $sth->fetchrow_hashref ){
840 =head2 GetItemsForInventory
842 ($itemlist, $iTotalRecords) = GetItemsForInventory( {
843 minlocation => $minlocation,
844 maxlocation => $maxlocation,
845 location => $location,
846 itemtype => $itemtype,
847 ignoreissued => $ignoreissued,
848 datelastseen => $datelastseen,
849 branchcode => $branchcode,
853 statushash => $statushash,
856 Retrieve a list of title/authors/barcode/callnumber, for biblio inventory.
858 The sub returns a reference to a list of hashes, each containing
859 itemnumber, author, title, barcode, item callnumber, and date last
860 seen. It is ordered by callnumber then title.
862 The required minlocation & maxlocation parameters are used to specify a range of item callnumbers
863 the datelastseen can be used to specify that you want to see items not seen since a past date only.
864 offset & size can be used to retrieve only a part of the whole listing (defaut behaviour)
865 $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.
867 $iTotalRecords is the number of rows that would have been returned without the $offset, $size limit clause
871 sub GetItemsForInventory {
872 my ( $parameters ) = @_;
873 my $minlocation = $parameters->{'minlocation'} // '';
874 my $maxlocation = $parameters->{'maxlocation'} // '';
875 my $location = $parameters->{'location'} // '';
876 my $itemtype = $parameters->{'itemtype'} // '';
877 my $ignoreissued = $parameters->{'ignoreissued'} // '';
878 my $datelastseen = $parameters->{'datelastseen'} // '';
879 my $branchcode = $parameters->{'branchcode'} // '';
880 my $branch = $parameters->{'branch'} // '';
881 my $offset = $parameters->{'offset'} // '';
882 my $size = $parameters->{'size'} // '';
883 my $statushash = $parameters->{'statushash'} // '';
885 my $dbh = C4::Context->dbh;
886 my ( @bind_params, @where_strings );
888 my $select_columns = q{
889 SELECT items.itemnumber, barcode, itemcallnumber, title, author, biblio.biblionumber, biblio.frameworkcode, datelastseen, homebranch, location, notforloan, damaged, itemlost, withdrawn, stocknumber
891 my $select_count = q{SELECT COUNT(*)};
894 LEFT JOIN biblio ON items.biblionumber = biblio.biblionumber
895 LEFT JOIN biblioitems on items.biblionumber = biblioitems.biblionumber
898 for my $authvfield (keys %$statushash){
899 if ( scalar @{$statushash->{$authvfield}} > 0 ){
900 my $joinedvals = join ',', @{$statushash->{$authvfield}};
901 push @where_strings, "$authvfield in (" . $joinedvals . ")";
907 push @where_strings, 'itemcallnumber >= ?';
908 push @bind_params, $minlocation;
912 push @where_strings, 'itemcallnumber <= ?';
913 push @bind_params, $maxlocation;
917 $datelastseen = output_pref({ str => $datelastseen, dateformat => 'iso', dateonly => 1 });
918 push @where_strings, '(datelastseen < ? OR datelastseen IS NULL)';
919 push @bind_params, $datelastseen;
923 push @where_strings, 'items.location = ?';
924 push @bind_params, $location;
928 if($branch eq "homebranch"){
929 push @where_strings, 'items.homebranch = ?';
931 push @where_strings, 'items.holdingbranch = ?';
933 push @bind_params, $branchcode;
937 push @where_strings, 'biblioitems.itemtype = ?';
938 push @bind_params, $itemtype;
941 if ( $ignoreissued) {
942 $query .= "LEFT JOIN issues ON items.itemnumber = issues.itemnumber ";
943 push @where_strings, 'issues.date_due IS NULL';
946 if ( @where_strings ) {
948 $query .= join ' AND ', @where_strings;
950 $query .= ' ORDER BY items.cn_sort, itemcallnumber, title';
951 my $count_query = $select_count . $query;
952 $query .= " LIMIT $offset, $size" if ($offset and $size);
953 $query = $select_columns . $query;
954 my $sth = $dbh->prepare($query);
955 $sth->execute( @bind_params );
958 my $tmpresults = $sth->fetchall_arrayref({});
959 $sth = $dbh->prepare( $count_query );
960 $sth->execute( @bind_params );
961 my ($iTotalRecords) = $sth->fetchrow_array();
963 my @avs = Koha::AuthorisedValues->search(
964 { 'marc_subfield_structures.kohafield' => { '>' => '' },
965 'me.authorised_value' => { '>' => '' },
967 { join => { category => 'marc_subfield_structures' },
968 distinct => ['marc_subfield_structures.kohafield, me.category, frameworkcode, me.authorised_value'],
969 '+select' => [ 'marc_subfield_structures.kohafield', 'marc_subfield_structures.frameworkcode', 'me.authorised_value', 'me.lib' ],
970 '+as' => [ 'kohafield', 'frameworkcode', 'authorised_value', 'lib' ],
974 my $avmapping = { map { $_->get_column('kohafield') . ',' . $_->get_column('frameworkcode') . ',' . $_->get_column('authorised_value') => $_->get_column('lib') } @avs };
976 foreach my $row (@$tmpresults) {
979 foreach (keys %$row) {
980 if (defined($avmapping->{"items.$_,".$row->{'frameworkcode'}.",".$row->{$_}})) {
981 $row->{$_} = $avmapping->{"items.$_,".$row->{'frameworkcode'}.",".$row->{$_}};
987 return (\@results, $iTotalRecords);
990 =head2 GetItemInfosOf
992 GetItemInfosOf(@itemnumbers);
997 my @itemnumbers = @_;
999 my $itemnumber_values = @itemnumbers ? join( ',', @itemnumbers ) : "''";
1001 my $dbh = C4::Context->dbh;
1005 WHERE itemnumber IN ($itemnumber_values)
1007 return $dbh->selectall_hashref($query, 'itemnumber');
1010 =head2 GetItemsByBiblioitemnumber
1012 GetItemsByBiblioitemnumber($biblioitemnumber);
1014 Returns an arrayref of hashrefs suitable for use in a TMPL_LOOP
1015 Called by C<C4::XISBN>
1019 sub GetItemsByBiblioitemnumber {
1020 my ( $bibitem ) = @_;
1021 my $dbh = C4::Context->dbh;
1022 my $sth = $dbh->prepare("SELECT * FROM items WHERE items.biblioitemnumber = ?") || die $dbh->errstr;
1023 # Get all items attached to a biblioitem
1026 $sth->execute($bibitem) || die $sth->errstr;
1027 while ( my $data = $sth->fetchrow_hashref ) {
1028 # Foreach item, get circulation information
1029 my $sth2 = $dbh->prepare( "SELECT * FROM issues,borrowers
1030 WHERE itemnumber = ?
1031 AND issues.borrowernumber = borrowers.borrowernumber"
1033 $sth2->execute( $data->{'itemnumber'} );
1034 if ( my $data2 = $sth2->fetchrow_hashref ) {
1035 # if item is out, set the due date and who it is out too
1036 $data->{'date_due'} = $data2->{'date_due'};
1037 $data->{'cardnumber'} = $data2->{'cardnumber'};
1038 $data->{'borrowernumber'} = $data2->{'borrowernumber'};
1041 # set date_due to blank, so in the template we check itemlost, and withdrawn
1042 $data->{'date_due'} = '';
1044 # Find the last 3 people who borrowed this item.
1045 my $query2 = "SELECT * FROM old_issues, borrowers WHERE itemnumber = ?
1046 AND old_issues.borrowernumber = borrowers.borrowernumber
1047 ORDER BY returndate desc,timestamp desc LIMIT 3";
1048 $sth2 = $dbh->prepare($query2) || die $dbh->errstr;
1049 $sth2->execute( $data->{'itemnumber'} ) || die $sth2->errstr;
1051 while ( my $data2 = $sth2->fetchrow_hashref ) {
1052 $data->{"timestamp$i2"} = $data2->{'timestamp'};
1053 $data->{"card$i2"} = $data2->{'cardnumber'};
1054 $data->{"borrower$i2"} = $data2->{'borrowernumber'};
1057 push(@results,$data);
1064 @results = GetItemsInfo($biblionumber);
1066 Returns information about items with the given biblionumber.
1068 C<GetItemsInfo> returns a list of references-to-hash. Each element
1069 contains a number of keys. Most of them are attributes from the
1070 C<biblio>, C<biblioitems>, C<items>, and C<itemtypes> tables in the
1071 Koha database. Other keys include:
1075 =item C<$data-E<gt>{branchname}>
1077 The name (not the code) of the branch to which the book belongs.
1079 =item C<$data-E<gt>{datelastseen}>
1081 This is simply C<items.datelastseen>, except that while the date is
1082 stored in YYYY-MM-DD format in the database, here it is converted to
1083 DD/MM/YYYY format. A NULL date is returned as C<//>.
1085 =item C<$data-E<gt>{datedue}>
1087 =item C<$data-E<gt>{class}>
1089 This is the concatenation of C<biblioitems.classification>, the book's
1090 Dewey code, and C<biblioitems.subclass>.
1092 =item C<$data-E<gt>{ocount}>
1094 I think this is the number of copies of the book available.
1096 =item C<$data-E<gt>{order}>
1098 If this is set, it is set to C<One Order>.
1105 my ( $biblionumber ) = @_;
1106 my $dbh = C4::Context->dbh;
1107 require C4::Languages;
1108 my $language = C4::Languages::getlanguage();
1114 biblioitems.itemtype,
1117 biblioitems.publicationyear,
1118 biblioitems.publishercode,
1119 biblioitems.volumedate,
1120 biblioitems.volumedesc,
1123 items.notforloan as itemnotforloan,
1124 issues.borrowernumber,
1125 issues.date_due as datedue,
1126 issues.onsite_checkout,
1127 borrowers.cardnumber,
1129 borrowers.firstname,
1130 borrowers.branchcode as bcode,
1132 serial.publisheddate,
1133 itemtypes.description,
1134 COALESCE( localization.translation, itemtypes.description ) AS translated_description,
1135 itemtypes.notforloan as notforloan_per_itemtype,
1139 holding.opac_info as holding_branch_opac_info,
1140 home.opac_info as home_branch_opac_info
1144 LEFT JOIN branches AS holding ON items.holdingbranch = holding.branchcode
1145 LEFT JOIN branches AS home ON items.homebranch=home.branchcode
1146 LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber
1147 LEFT JOIN biblioitems ON biblioitems.biblioitemnumber = items.biblioitemnumber
1148 LEFT JOIN issues USING (itemnumber)
1149 LEFT JOIN borrowers USING (borrowernumber)
1150 LEFT JOIN serialitems USING (itemnumber)
1151 LEFT JOIN serial USING (serialid)
1152 LEFT JOIN itemtypes ON itemtypes.itemtype = "
1153 . (C4::Context->preference('item-level_itypes') ? 'items.itype' : 'biblioitems.itemtype');
1155 LEFT JOIN localization ON itemtypes.itemtype = localization.code
1156 AND localization.entity = 'itemtypes'
1157 AND localization.lang = ?
1160 $query .= " WHERE items.biblionumber = ? ORDER BY home.branchname, items.enumchron, LPAD( items.copynumber, 8, '0' ), items.dateaccessioned DESC" ;
1161 my $sth = $dbh->prepare($query);
1162 $sth->execute($language, $biblionumber);
1167 my $userenv = C4::Context->userenv;
1168 my $want_not_same_branch = C4::Context->preference("IndependentBranches") && !C4::Context->IsSuperLibrarian();
1169 while ( my $data = $sth->fetchrow_hashref ) {
1170 if ( $data->{borrowernumber} && $want_not_same_branch) {
1171 $data->{'NOTSAMEBRANCH'} = $data->{'bcode'} ne $userenv->{branch};
1174 $serial ||= $data->{'serial'};
1177 # get notforloan complete status if applicable
1178 $descriptions = Koha::AuthorisedValues->get_description_by_koha_field({frameworkcode => $data->{frameworkcode}, kohafield => 'items.notforloan', authorised_value => $data->{itemnotforloan} });
1179 $data->{notforloanvalue} = $descriptions->{lib} // '';
1180 $data->{notforloanvalueopac} = $descriptions->{opac_description} // '';
1182 # get restricted status and description if applicable
1183 $descriptions = Koha::AuthorisedValues->get_description_by_koha_field({frameworkcode => $data->{frameworkcode}, kohafield => 'items.restricted', authorised_value => $data->{restricted} });
1184 $data->{restricted} = $descriptions->{lib} // '';
1185 $data->{restrictedopac} = $descriptions->{opac_description} // '';
1187 # my stack procedures
1188 $descriptions = Koha::AuthorisedValues->get_description_by_koha_field({frameworkcode => $data->{frameworkcode}, kohafield => 'items.stack', authorised_value => $data->{stack} });
1189 $data->{stack} = $descriptions->{lib} // '';
1191 # Find the last 3 people who borrowed this item.
1192 my $sth2 = $dbh->prepare("SELECT * FROM old_issues,borrowers
1193 WHERE itemnumber = ?
1194 AND old_issues.borrowernumber = borrowers.borrowernumber
1195 ORDER BY returndate DESC
1197 $sth2->execute($data->{'itemnumber'});
1199 while (my $data2 = $sth2->fetchrow_hashref()) {
1200 $data->{"timestamp$ii"} = $data2->{'timestamp'} if $data2->{'timestamp'};
1201 $data->{"card$ii"} = $data2->{'cardnumber'} if $data2->{'cardnumber'};
1202 $data->{"borrower$ii"} = $data2->{'borrowernumber'} if $data2->{'borrowernumber'};
1206 $results[$i] = $data;
1211 ? sort { ($b->{'publisheddate'} || $b->{'enumchron'}) cmp ($a->{'publisheddate'} || $a->{'enumchron'}) } @results
1215 =head2 GetItemsLocationInfo
1217 my @itemlocinfo = GetItemsLocationInfo($biblionumber);
1219 Returns the branch names, shelving location and itemcallnumber for each item attached to the biblio in question
1221 C<GetItemsInfo> returns a list of references-to-hash. Data returned:
1225 =item C<$data-E<gt>{homebranch}>
1227 Branch Name of the item's homebranch
1229 =item C<$data-E<gt>{holdingbranch}>
1231 Branch Name of the item's holdingbranch
1233 =item C<$data-E<gt>{location}>
1235 Item's shelving location code
1237 =item C<$data-E<gt>{location_intranet}>
1239 The intranet description for the Shelving Location as set in authorised_values 'LOC'
1241 =item C<$data-E<gt>{location_opac}>
1243 The OPAC description for the Shelving Location as set in authorised_values 'LOC'. Falls back to intranet description if no OPAC
1246 =item C<$data-E<gt>{itemcallnumber}>
1248 Item's itemcallnumber
1250 =item C<$data-E<gt>{cn_sort}>
1252 Item's call number normalized for sorting
1258 sub GetItemsLocationInfo {
1259 my $biblionumber = shift;
1262 my $dbh = C4::Context->dbh;
1263 my $query = "SELECT a.branchname as homebranch, b.branchname as holdingbranch,
1264 location, itemcallnumber, cn_sort
1265 FROM items, branches as a, branches as b
1266 WHERE homebranch = a.branchcode AND holdingbranch = b.branchcode
1267 AND biblionumber = ?
1268 ORDER BY cn_sort ASC";
1269 my $sth = $dbh->prepare($query);
1270 $sth->execute($biblionumber);
1272 while ( my $data = $sth->fetchrow_hashref ) {
1273 my $av = Koha::AuthorisedValues->search({ category => 'LOC', authorised_value => $data->{location} });
1274 $av = $av->count ? $av->next : undef;
1275 $data->{location_intranet} = $av ? $av->lib : '';
1276 $data->{location_opac} = $av ? $av->opac_description : '';
1277 push @results, $data;
1282 =head2 GetHostItemsInfo
1284 $hostiteminfo = GetHostItemsInfo($hostfield);
1285 Returns the iteminfo for items linked to records via a host field
1289 sub GetHostItemsInfo {
1291 my @returnitemsInfo;
1293 if (C4::Context->preference('marcflavour') eq 'MARC21' ||
1294 C4::Context->preference('marcflavour') eq 'NORMARC'){
1295 foreach my $hostfield ( $record->field('773') ) {
1296 my $hostbiblionumber = $hostfield->subfield("0");
1297 my $linkeditemnumber = $hostfield->subfield("9");
1298 my @hostitemInfos = GetItemsInfo($hostbiblionumber);
1299 foreach my $hostitemInfo (@hostitemInfos){
1300 if ($hostitemInfo->{itemnumber} eq $linkeditemnumber){
1301 push (@returnitemsInfo,$hostitemInfo);
1306 } elsif ( C4::Context->preference('marcflavour') eq 'UNIMARC'){
1307 foreach my $hostfield ( $record->field('461') ) {
1308 my $hostbiblionumber = $hostfield->subfield("0");
1309 my $linkeditemnumber = $hostfield->subfield("9");
1310 my @hostitemInfos = GetItemsInfo($hostbiblionumber);
1311 foreach my $hostitemInfo (@hostitemInfos){
1312 if ($hostitemInfo->{itemnumber} eq $linkeditemnumber){
1313 push (@returnitemsInfo,$hostitemInfo);
1319 return @returnitemsInfo;
1323 =head2 GetLastAcquisitions
1325 my $lastacq = GetLastAcquisitions({'branches' => ('branch1','branch2'),
1326 'itemtypes' => ('BK','BD')}, 10);
1330 sub GetLastAcquisitions {
1331 my ($data,$max) = @_;
1333 my $itemtype = C4::Context->preference('item-level_itypes') ? 'itype' : 'itemtype';
1335 my $number_of_branches = @{$data->{branches}};
1336 my $number_of_itemtypes = @{$data->{itemtypes}};
1339 my @where = ('WHERE 1 ');
1340 $number_of_branches and push @where
1341 , 'AND holdingbranch IN ('
1342 , join(',', ('?') x $number_of_branches )
1346 $number_of_itemtypes and push @where
1347 , "AND $itemtype IN ("
1348 , join(',', ('?') x $number_of_itemtypes )
1352 my $query = "SELECT biblio.biblionumber as biblionumber, title, dateaccessioned
1353 FROM items RIGHT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
1354 RIGHT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber)
1356 GROUP BY biblio.biblionumber
1357 ORDER BY dateaccessioned DESC LIMIT $max";
1359 my $dbh = C4::Context->dbh;
1360 my $sth = $dbh->prepare($query);
1362 $sth->execute((@{$data->{branches}}, @{$data->{itemtypes}}));
1365 while( my $row = $sth->fetchrow_hashref){
1366 push @results, {date => $row->{dateaccessioned}
1367 , biblionumber => $row->{biblionumber}
1368 , title => $row->{title}};
1374 =head2 GetItemnumbersForBiblio
1376 my $itemnumbers = GetItemnumbersForBiblio($biblionumber);
1378 Given a single biblionumber, return an arrayref of all the corresponding itemnumbers
1382 sub GetItemnumbersForBiblio {
1383 my $biblionumber = shift;
1385 my $dbh = C4::Context->dbh;
1386 my $sth = $dbh->prepare("SELECT itemnumber FROM items WHERE biblionumber = ?");
1387 $sth->execute($biblionumber);
1388 while (my $result = $sth->fetchrow_hashref) {
1389 push @items, $result->{'itemnumber'};
1394 =head2 get_hostitemnumbers_of
1396 my @itemnumbers_of = get_hostitemnumbers_of($biblionumber);
1398 Given a biblionumber, return the list of corresponding itemnumbers that are linked to it via host fields
1400 Return a reference on a hash where key is a biblionumber and values are
1401 references on array of itemnumbers.
1406 sub get_hostitemnumbers_of {
1407 my ($biblionumber) = @_;
1408 my $marcrecord = GetMarcBiblio($biblionumber);
1410 return unless $marcrecord;
1412 my ( @returnhostitemnumbers, $tag, $biblio_s, $item_s );
1414 my $marcflavor = C4::Context->preference('marcflavour');
1415 if ( $marcflavor eq 'MARC21' || $marcflavor eq 'NORMARC' ) {
1420 elsif ( $marcflavor eq 'UNIMARC' ) {
1426 foreach my $hostfield ( $marcrecord->field($tag) ) {
1427 my $hostbiblionumber = $hostfield->subfield($biblio_s);
1428 my $linkeditemnumber = $hostfield->subfield($item_s);
1429 my $is_from_biblio = Koha::Items->search({ itemnumber => $linkeditemnumber, biblionumber => $hostbiblionumber });
1430 push @returnhostitemnumbers, $linkeditemnumber
1434 return @returnhostitemnumbers;
1438 =head2 GetItemnumberFromBarcode
1440 $result = GetItemnumberFromBarcode($barcode);
1444 sub GetItemnumberFromBarcode {
1446 my $dbh = C4::Context->dbh;
1449 $dbh->prepare("SELECT itemnumber FROM items WHERE items.barcode=?");
1450 $rq->execute($barcode);
1451 my ($result) = $rq->fetchrow;
1455 =head2 GetBarcodeFromItemnumber
1457 $result = GetBarcodeFromItemnumber($itemnumber);
1461 sub GetBarcodeFromItemnumber {
1462 my ($itemnumber) = @_;
1463 my $dbh = C4::Context->dbh;
1466 $dbh->prepare("SELECT barcode FROM items WHERE items.itemnumber=?");
1467 $rq->execute($itemnumber);
1468 my ($result) = $rq->fetchrow;
1472 =head2 GetHiddenItemnumbers
1474 my @itemnumbers_to_hide = GetHiddenItemnumbers(@items);
1476 Given a list of items it checks which should be hidden from the OPAC given
1477 the current configuration. Returns a list of itemnumbers corresponding to
1478 those that should be hidden.
1482 sub GetHiddenItemnumbers {
1486 my $yaml = C4::Context->preference('OpacHiddenItems');
1487 return () if (! $yaml =~ /\S/ );
1488 $yaml = "$yaml\n\n"; # YAML is anal on ending \n. Surplus does not hurt
1491 $hidingrules = YAML::Load($yaml);
1494 warn "Unable to parse OpacHiddenItems syspref : $@";
1497 my $dbh = C4::Context->dbh;
1500 foreach my $item (@items) {
1502 # We check each rule
1503 foreach my $field (keys %$hidingrules) {
1505 if (exists $item->{$field}) {
1506 $val = $item->{$field};
1509 my $query = "SELECT $field from items where itemnumber = ?";
1510 $val = $dbh->selectrow_array($query, undef, $item->{'itemnumber'});
1512 $val = '' unless defined $val;
1514 # If the results matches the values in the yaml file
1515 if (any { $val eq $_ } @{$hidingrules->{$field}}) {
1517 # We add the itemnumber to the list
1518 push @resultitems, $item->{'itemnumber'};
1520 # If at least one rule matched for an item, no need to test the others
1525 return @resultitems;
1528 =head1 LIMITED USE FUNCTIONS
1530 The following functions, while part of the public API,
1531 are not exported. This is generally because they are
1532 meant to be used by only one script for a specific
1533 purpose, and should not be used in any other context
1534 without careful thought.
1540 my $item_marc = GetMarcItem($biblionumber, $itemnumber);
1542 Returns MARC::Record of the item passed in parameter.
1543 This function is meant for use only in C<cataloguing/additem.pl>,
1544 where it is needed to support that script's MARC-like
1550 my ( $biblionumber, $itemnumber ) = @_;
1552 # GetMarcItem has been revised so that it does the following:
1553 # 1. Gets the item information from the items table.
1554 # 2. Converts it to a MARC field for storage in the bib record.
1556 # The previous behavior was:
1557 # 1. Get the bib record.
1558 # 2. Return the MARC tag corresponding to the item record.
1560 # The difference is that one treats the items row as authoritative,
1561 # while the other treats the MARC representation as authoritative
1562 # under certain circumstances.
1564 my $itemrecord = GetItem($itemnumber);
1566 # Tack on 'items.' prefix to column names so that TransformKohaToMarc will work.
1567 # Also, don't emit a subfield if the underlying field is blank.
1570 return Item2Marc($itemrecord,$biblionumber);
1574 my ($itemrecord,$biblionumber)=@_;
1577 defined($itemrecord->{$_}) && $itemrecord->{$_} ne '' ? ("items.$_" => $itemrecord->{$_}) : ()
1578 } keys %{ $itemrecord }
1580 my $itemmarc = TransformKohaToMarc($mungeditem);
1581 my ( $itemtag, $itemsubfield ) = GetMarcFromKohaField("items.itemnumber",GetFrameworkCode($biblionumber)||'');
1583 my $unlinked_item_subfields = _parse_unlinked_item_subfields_from_xml($mungeditem->{'items.more_subfields_xml'});
1584 if (defined $unlinked_item_subfields and $#$unlinked_item_subfields > -1) {
1585 foreach my $field ($itemmarc->field($itemtag)){
1586 $field->add_subfields(@$unlinked_item_subfields);
1592 =head1 PRIVATE FUNCTIONS AND VARIABLES
1594 The following functions are not meant to be called
1595 directly, but are documented in order to explain
1596 the inner workings of C<C4::Items>.
1600 =head2 %derived_columns
1602 This hash keeps track of item columns that
1603 are strictly derived from other columns in
1604 the item record and are not meant to be set
1607 Each key in the hash should be the name of a
1608 column (as named by TransformMarcToKoha). Each
1609 value should be hashref whose keys are the
1610 columns on which the derived column depends. The
1611 hashref should also contain a 'BUILDER' key
1612 that is a reference to a sub that calculates
1617 my %derived_columns = (
1618 'items.cn_sort' => {
1619 'itemcallnumber' => 1,
1620 'items.cn_source' => 1,
1621 'BUILDER' => \&_calc_items_cn_sort,
1625 =head2 _set_derived_columns_for_add
1627 _set_derived_column_for_add($item);
1629 Given an item hash representing a new item to be added,
1630 calculate any derived columns. Currently the only
1631 such column is C<items.cn_sort>.
1635 sub _set_derived_columns_for_add {
1638 foreach my $column (keys %derived_columns) {
1639 my $builder = $derived_columns{$column}->{'BUILDER'};
1640 my $source_values = {};
1641 foreach my $source_column (keys %{ $derived_columns{$column} }) {
1642 next if $source_column eq 'BUILDER';
1643 $source_values->{$source_column} = $item->{$source_column};
1645 $builder->($item, $source_values);
1649 =head2 _set_derived_columns_for_mod
1651 _set_derived_column_for_mod($item);
1653 Given an item hash representing a new item to be modified.
1654 calculate any derived columns. Currently the only
1655 such column is C<items.cn_sort>.
1657 This routine differs from C<_set_derived_columns_for_add>
1658 in that it needs to handle partial item records. In other
1659 words, the caller of C<ModItem> may have supplied only one
1660 or two columns to be changed, so this function needs to
1661 determine whether any of the columns to be changed affect
1662 any of the derived columns. Also, if a derived column
1663 depends on more than one column, but the caller is not
1664 changing all of then, this routine retrieves the unchanged
1665 values from the database in order to ensure a correct
1670 sub _set_derived_columns_for_mod {
1673 foreach my $column (keys %derived_columns) {
1674 my $builder = $derived_columns{$column}->{'BUILDER'};
1675 my $source_values = {};
1676 my %missing_sources = ();
1677 my $must_recalc = 0;
1678 foreach my $source_column (keys %{ $derived_columns{$column} }) {
1679 next if $source_column eq 'BUILDER';
1680 if (exists $item->{$source_column}) {
1682 $source_values->{$source_column} = $item->{$source_column};
1684 $missing_sources{$source_column} = 1;
1688 foreach my $source_column (keys %missing_sources) {
1689 $source_values->{$source_column} = _get_single_item_column($source_column, $item->{'itemnumber'});
1691 $builder->($item, $source_values);
1696 =head2 _do_column_fixes_for_mod
1698 _do_column_fixes_for_mod($item);
1700 Given an item hashref containing one or more
1701 columns to modify, fix up certain values.
1702 Specifically, set to 0 any passed value
1703 of C<notforloan>, C<damaged>, C<itemlost>, or
1704 C<withdrawn> that is either undefined or
1705 contains the empty string.
1709 sub _do_column_fixes_for_mod {
1712 if (exists $item->{'notforloan'} and
1713 (not defined $item->{'notforloan'} or $item->{'notforloan'} eq '')) {
1714 $item->{'notforloan'} = 0;
1716 if (exists $item->{'damaged'} and
1717 (not defined $item->{'damaged'} or $item->{'damaged'} eq '')) {
1718 $item->{'damaged'} = 0;
1720 if (exists $item->{'itemlost'} and
1721 (not defined $item->{'itemlost'} or $item->{'itemlost'} eq '')) {
1722 $item->{'itemlost'} = 0;
1724 if (exists $item->{'withdrawn'} and
1725 (not defined $item->{'withdrawn'} or $item->{'withdrawn'} eq '')) {
1726 $item->{'withdrawn'} = 0;
1728 if (exists $item->{location}
1729 and $item->{location} ne 'CART'
1730 and $item->{location} ne 'PROC'
1731 and not $item->{permanent_location}
1733 $item->{'permanent_location'} = $item->{'location'};
1735 if (exists $item->{'timestamp'}) {
1736 delete $item->{'timestamp'};
1740 =head2 _get_single_item_column
1742 _get_single_item_column($column, $itemnumber);
1744 Retrieves the value of a single column from an C<items>
1745 row specified by C<$itemnumber>.
1749 sub _get_single_item_column {
1751 my $itemnumber = shift;
1753 my $dbh = C4::Context->dbh;
1754 my $sth = $dbh->prepare("SELECT $column FROM items WHERE itemnumber = ?");
1755 $sth->execute($itemnumber);
1756 my ($value) = $sth->fetchrow();
1760 =head2 _calc_items_cn_sort
1762 _calc_items_cn_sort($item, $source_values);
1764 Helper routine to calculate C<items.cn_sort>.
1768 sub _calc_items_cn_sort {
1770 my $source_values = shift;
1772 $item->{'items.cn_sort'} = GetClassSort($source_values->{'items.cn_source'}, $source_values->{'itemcallnumber'}, "");
1775 =head2 _set_defaults_for_add
1777 _set_defaults_for_add($item_hash);
1779 Given an item hash representing an item to be added, set
1780 correct default values for columns whose default value
1781 is not handled by the DBMS. This includes the following
1788 C<items.dateaccessioned>
1810 sub _set_defaults_for_add {
1812 $item->{dateaccessioned} ||= output_pref({ dt => dt_from_string, dateformat => 'iso', dateonly => 1 });
1813 $item->{$_} ||= 0 for (qw( notforloan damaged itemlost withdrawn));
1816 =head2 _koha_new_item
1818 my ($itemnumber,$error) = _koha_new_item( $item, $barcode );
1820 Perform the actual insert into the C<items> table.
1824 sub _koha_new_item {
1825 my ( $item, $barcode ) = @_;
1826 my $dbh=C4::Context->dbh;
1828 $item->{permanent_location} //= $item->{location};
1829 _mod_item_dates( $item );
1831 "INSERT INTO items SET
1833 biblioitemnumber = ?,
1835 dateaccessioned = ?,
1839 replacementprice = ?,
1840 replacementpricedate = ?,
1841 datelastborrowed = ?,
1849 coded_location_qualifier = ?,
1852 itemnotes_nonpublic = ?,
1856 permanent_location = ?,
1868 more_subfields_xml = ?,
1873 my $sth = $dbh->prepare($query);
1874 my $today = output_pref({ dt => dt_from_string, dateformat => 'iso', dateonly => 1 });
1876 $item->{'biblionumber'},
1877 $item->{'biblioitemnumber'},
1879 $item->{'dateaccessioned'},
1880 $item->{'booksellerid'},
1881 $item->{'homebranch'},
1883 $item->{'replacementprice'},
1884 $item->{'replacementpricedate'} || $today,
1885 $item->{datelastborrowed},
1886 $item->{datelastseen} || $today,
1888 $item->{'notforloan'},
1890 $item->{'itemlost'},
1891 $item->{'withdrawn'},
1892 $item->{'itemcallnumber'},
1893 $item->{'coded_location_qualifier'},
1894 $item->{'restricted'},
1895 $item->{'itemnotes'},
1896 $item->{'itemnotes_nonpublic'},
1897 $item->{'holdingbranch'},
1899 $item->{'location'},
1900 $item->{'permanent_location'},
1903 $item->{'renewals'},
1904 $item->{'reserves'},
1905 $item->{'items.cn_source'},
1906 $item->{'items.cn_sort'},
1909 $item->{'materials'},
1911 $item->{'enumchron'},
1912 $item->{'more_subfields_xml'},
1913 $item->{'copynumber'},
1914 $item->{'stocknumber'},
1915 $item->{'new_status'},
1919 if ( defined $sth->errstr ) {
1920 $error.="ERROR in _koha_new_item $query".$sth->errstr;
1923 $itemnumber = $dbh->{'mysql_insertid'};
1926 return ( $itemnumber, $error );
1929 =head2 MoveItemFromBiblio
1931 MoveItemFromBiblio($itenumber, $frombiblio, $tobiblio);
1933 Moves an item from a biblio to another
1935 Returns undef if the move failed or the biblionumber of the destination record otherwise
1939 sub MoveItemFromBiblio {
1940 my ($itemnumber, $frombiblio, $tobiblio) = @_;
1941 my $dbh = C4::Context->dbh;
1942 my ( $tobiblioitem ) = $dbh->selectrow_array(q|
1943 SELECT biblioitemnumber
1945 WHERE biblionumber = ?
1946 |, undef, $tobiblio );
1947 my $return = $dbh->do(q|
1949 SET biblioitemnumber = ?,
1951 WHERE itemnumber = ?
1952 AND biblionumber = ?
1953 |, undef, $tobiblioitem, $tobiblio, $itemnumber, $frombiblio );
1955 ModZebra( $tobiblio, "specialUpdate", "biblioserver" );
1956 ModZebra( $frombiblio, "specialUpdate", "biblioserver" );
1957 # Checking if the item we want to move is in an order
1958 require C4::Acquisition;
1959 my $order = C4::Acquisition::GetOrderFromItemnumber($itemnumber);
1961 # Replacing the biblionumber within the order if necessary
1962 $order->{'biblionumber'} = $tobiblio;
1963 C4::Acquisition::ModOrder($order);
1966 # Update reserves, hold_fill_targets, tmp_holdsqueue and linktracker tables
1967 for my $table_name ( qw( reserves hold_fill_targets tmp_holdsqueue linktracker ) ) {
1970 SET biblionumber = ?
1971 WHERE itemnumber = ?
1972 |, undef, $tobiblio, $itemnumber );
1979 =head2 ItemSafeToDelete
1981 ItemSafeToDelete( $biblionumber, $itemnumber);
1983 Exported function (core API) for checking whether an item record is safe to delete.
1985 returns 1 if the item is safe to delete,
1987 "book_on_loan" if the item is checked out,
1989 "not_same_branch" if the item is blocked by independent branches,
1991 "book_reserved" if the there are holds aganst the item, or
1993 "linked_analytics" if the item has linked analytic records.
1997 sub ItemSafeToDelete {
1998 my ( $biblionumber, $itemnumber ) = @_;
2000 my $dbh = C4::Context->dbh;
2004 my $countanalytics = GetAnalyticsCount($itemnumber);
2006 # check that there is no issue on this item before deletion.
2007 my $sth = $dbh->prepare(
2009 SELECT COUNT(*) FROM issues
2010 WHERE itemnumber = ?
2013 $sth->execute($itemnumber);
2014 my ($onloan) = $sth->fetchrow;
2016 my $item = GetItem($itemnumber);
2019 $status = "book_on_loan";
2021 elsif ( defined C4::Context->userenv
2022 and !C4::Context->IsSuperLibrarian()
2023 and C4::Context->preference("IndependentBranches")
2024 and ( C4::Context->userenv->{branch} ne $item->{'homebranch'} ) )
2026 $status = "not_same_branch";
2029 # check it doesn't have a waiting reserve
2030 $sth = $dbh->prepare(
2032 SELECT COUNT(*) FROM reserves
2033 WHERE (found = 'W' OR found = 'T')
2037 $sth->execute($itemnumber);
2038 my ($reserve) = $sth->fetchrow;
2040 $status = "book_reserved";
2042 elsif ( $countanalytics > 0 ) {
2043 $status = "linked_analytics";
2054 DelItemCheck( $biblionumber, $itemnumber);
2056 Exported function (core API) for deleting an item record in Koha if there no current issue.
2058 DelItemCheck wraps ItemSafeToDelete around DelItem.
2063 my ( $biblionumber, $itemnumber ) = @_;
2064 my $status = ItemSafeToDelete( $biblionumber, $itemnumber );
2066 if ( $status == 1 ) {
2069 biblionumber => $biblionumber,
2070 itemnumber => $itemnumber
2077 =head2 _koha_modify_item
2079 my ($itemnumber,$error) =_koha_modify_item( $item );
2081 Perform the actual update of the C<items> row. Note that this
2082 routine accepts a hashref specifying the columns to update.
2086 sub _koha_modify_item {
2088 my $dbh=C4::Context->dbh;
2091 my $query = "UPDATE items SET ";
2093 _mod_item_dates( $item );
2094 for my $key ( keys %$item ) {
2095 next if ( $key eq 'itemnumber' );
2097 push @bind, $item->{$key};
2100 $query .= " WHERE itemnumber=?";
2101 push @bind, $item->{'itemnumber'};
2102 my $sth = $dbh->prepare($query);
2103 $sth->execute(@bind);
2105 $error.="ERROR in _koha_modify_item $query: ".$sth->errstr;
2108 return ($item->{'itemnumber'},$error);
2111 sub _mod_item_dates { # date formatting for date fields in item hash
2113 return if !$item || ref($item) ne 'HASH';
2116 { $_ =~ /^onloan$|^date|date$|datetime$/ }
2118 # Incl. dateaccessioned,replacementpricedate,datelastborrowed,datelastseen
2119 # NOTE: We do not (yet) have items fields ending with datetime
2120 # Fields with _on$ have been handled already
2122 foreach my $key ( @keys ) {
2123 next if !defined $item->{$key}; # skip undefs
2124 my $dt = eval { dt_from_string( $item->{$key} ) };
2125 # eval: dt_from_string will die on us if we pass illegal dates
2128 if( defined $dt && ref($dt) eq 'DateTime' ) {
2129 if( $key =~ /datetime/ ) {
2130 $newstr = DateTime::Format::MySQL->format_datetime($dt);
2132 $newstr = DateTime::Format::MySQL->format_date($dt);
2135 $item->{$key} = $newstr; # might be undef to clear garbage
2139 =head2 _koha_delete_item
2141 _koha_delete_item( $itemnum );
2143 Internal function to delete an item record from the koha tables
2147 sub _koha_delete_item {
2148 my ( $itemnum ) = @_;
2150 my $dbh = C4::Context->dbh;
2151 # save the deleted item to deleteditems table
2152 my $sth = $dbh->prepare("SELECT * FROM items WHERE itemnumber=?");
2153 $sth->execute($itemnum);
2154 my $data = $sth->fetchrow_hashref();
2156 # There is no item to delete
2157 return 0 unless $data;
2159 my $query = "INSERT INTO deleteditems SET ";
2161 foreach my $key ( keys %$data ) {
2162 next if ( $key eq 'timestamp' ); # timestamp will be set by db
2163 $query .= "$key = ?,";
2164 push( @bind, $data->{$key} );
2167 $sth = $dbh->prepare($query);
2168 $sth->execute(@bind);
2170 # delete from items table
2171 $sth = $dbh->prepare("DELETE FROM items WHERE itemnumber=?");
2172 my $deleted = $sth->execute($itemnum);
2173 return ( $deleted == 1 ) ? 1 : 0;
2176 =head2 _marc_from_item_hash
2178 my $item_marc = _marc_from_item_hash($item, $frameworkcode[, $unlinked_item_subfields]);
2180 Given an item hash representing a complete item record,
2181 create a C<MARC::Record> object containing an embedded
2182 tag representing that item.
2184 The third, optional parameter C<$unlinked_item_subfields> is
2185 an arrayref of subfields (not mapped to C<items> fields per the
2186 framework) to be added to the MARC representation
2191 sub _marc_from_item_hash {
2193 my $frameworkcode = shift;
2194 my $unlinked_item_subfields;
2196 $unlinked_item_subfields = shift;
2199 # Tack on 'items.' prefix to column names so lookup from MARC frameworks will work
2200 # Also, don't emit a subfield if the underlying field is blank.
2201 my $mungeditem = { map { (defined($item->{$_}) and $item->{$_} ne '') ?
2202 (/^items\./ ? ($_ => $item->{$_}) : ("items.$_" => $item->{$_}))
2203 : () } keys %{ $item } };
2205 my $item_marc = MARC::Record->new();
2206 foreach my $item_field ( keys %{$mungeditem} ) {
2207 my ( $tag, $subfield ) = GetMarcFromKohaField( $item_field, $frameworkcode );
2208 next unless defined $tag and defined $subfield; # skip if not mapped to MARC field
2209 my @values = split(/\s?\|\s?/, $mungeditem->{$item_field}, -1);
2210 foreach my $value (@values){
2211 if ( my $field = $item_marc->field($tag) ) {
2212 $field->add_subfields( $subfield => $value );
2214 my $add_subfields = [];
2215 if (defined $unlinked_item_subfields and ref($unlinked_item_subfields) eq 'ARRAY' and $#$unlinked_item_subfields > -1) {
2216 $add_subfields = $unlinked_item_subfields;
2218 $item_marc->add_fields( $tag, " ", " ", $subfield => $value, @$add_subfields );
2226 =head2 _repack_item_errors
2228 Add an error message hash generated by C<CheckItemPreSave>
2229 to a list of errors.
2233 sub _repack_item_errors {
2234 my $item_sequence_num = shift;
2235 my $item_ref = shift;
2236 my $error_ref = shift;
2238 my @repacked_errors = ();
2240 foreach my $error_code (sort keys %{ $error_ref }) {
2241 my $repacked_error = {};
2242 $repacked_error->{'item_sequence'} = $item_sequence_num;
2243 $repacked_error->{'item_barcode'} = exists($item_ref->{'barcode'}) ? $item_ref->{'barcode'} : '';
2244 $repacked_error->{'error_code'} = $error_code;
2245 $repacked_error->{'error_information'} = $error_ref->{$error_code};
2246 push @repacked_errors, $repacked_error;
2249 return @repacked_errors;
2252 =head2 _get_unlinked_item_subfields
2254 my $unlinked_item_subfields = _get_unlinked_item_subfields($original_item_marc, $frameworkcode);
2258 sub _get_unlinked_item_subfields {
2259 my $original_item_marc = shift;
2260 my $frameworkcode = shift;
2262 my $marcstructure = GetMarcStructure(1, $frameworkcode, { unsafe => 1 });
2264 # assume that this record has only one field, and that that
2265 # field contains only the item information
2267 my @fields = $original_item_marc->fields();
2268 if ($#fields > -1) {
2269 my $field = $fields[0];
2270 my $tag = $field->tag();
2271 foreach my $subfield ($field->subfields()) {
2272 if (defined $subfield->[1] and
2273 $subfield->[1] ne '' and
2274 !$marcstructure->{$tag}->{$subfield->[0]}->{'kohafield'}) {
2275 push @$subfields, $subfield->[0] => $subfield->[1];
2282 =head2 _get_unlinked_subfields_xml
2284 my $unlinked_subfields_xml = _get_unlinked_subfields_xml($unlinked_item_subfields);
2288 sub _get_unlinked_subfields_xml {
2289 my $unlinked_item_subfields = shift;
2292 if (defined $unlinked_item_subfields and ref($unlinked_item_subfields) eq 'ARRAY' and $#$unlinked_item_subfields > -1) {
2293 my $marc = MARC::Record->new();
2294 # use of tag 999 is arbitrary, and doesn't need to match the item tag
2295 # used in the framework
2296 $marc->append_fields(MARC::Field->new('999', ' ', ' ', @$unlinked_item_subfields));
2297 $marc->encoding("UTF-8");
2298 $xml = $marc->as_xml("USMARC");
2304 =head2 _parse_unlinked_item_subfields_from_xml
2306 my $unlinked_item_subfields = _parse_unlinked_item_subfields_from_xml($whole_item->{'more_subfields_xml'}):
2310 sub _parse_unlinked_item_subfields_from_xml {
2312 require C4::Charset;
2313 return unless defined $xml and $xml ne "";
2314 my $marc = MARC::Record->new_from_xml(C4::Charset::StripNonXmlChars($xml),'UTF-8');
2315 my $unlinked_subfields = [];
2316 my @fields = $marc->fields();
2317 if ($#fields > -1) {
2318 foreach my $subfield ($fields[0]->subfields()) {
2319 push @$unlinked_subfields, $subfield->[0] => $subfield->[1];
2322 return $unlinked_subfields;
2325 =head2 GetAnalyticsCount
2327 $count= &GetAnalyticsCount($itemnumber)
2329 counts Usage of itemnumber in Analytical bibliorecords.
2333 sub GetAnalyticsCount {
2334 my ($itemnumber) = @_;
2336 ### ZOOM search here
2338 $query= "hi=".$itemnumber;
2339 my $searcher = Koha::SearchEngine::Search->new({index => $Koha::SearchEngine::BIBLIOS_INDEX});
2340 my ($err,$res,$result) = $searcher->simple_search_compat($query,0,10);
2344 =head2 SearchItemsByField
2346 my $items = SearchItemsByField($field, $value);
2348 SearchItemsByField will search for items on a specific given field.
2349 For instance you can search all items with a specific stocknumber like this:
2351 my $items = SearchItemsByField('stocknumber', $stocknumber);
2355 sub SearchItemsByField {
2356 my ($field, $value) = @_;
2363 my ($results) = SearchItems($filters);
2367 sub _SearchItems_build_where_fragment {
2370 my $dbh = C4::Context->dbh;
2373 if (exists($filter->{conjunction})) {
2374 my (@where_strs, @where_args);
2375 foreach my $f (@{ $filter->{filters} }) {
2376 my $fragment = _SearchItems_build_where_fragment($f);
2378 push @where_strs, $fragment->{str};
2379 push @where_args, @{ $fragment->{args} };
2384 $where_str = '(' . join (' ' . $filter->{conjunction} . ' ', @where_strs) . ')';
2387 args => \@where_args,
2391 my @columns = Koha::Database->new()->schema()->resultset('Item')->result_source->columns;
2392 push @columns, Koha::Database->new()->schema()->resultset('Biblio')->result_source->columns;
2393 push @columns, Koha::Database->new()->schema()->resultset('Biblioitem')->result_source->columns;
2394 my @operators = qw(= != > < >= <= like);
2395 my $field = $filter->{field};
2396 if ( (0 < grep /^$field$/, @columns) or (substr($field, 0, 5) eq 'marc:') ) {
2397 my $op = $filter->{operator};
2398 my $query = $filter->{query};
2400 if (!$op or (0 == grep /^$op$/, @operators)) {
2401 $op = '='; # default operator
2405 if ($field =~ /^marc:(\d{3})(?:\$(\w))?$/) {
2407 my $marcsubfield = $2;
2408 my ($kohafield) = $dbh->selectrow_array(q|
2409 SELECT kohafield FROM marc_subfield_structure
2410 WHERE tagfield=? AND tagsubfield=? AND frameworkcode=''
2411 |, undef, $marcfield, $marcsubfield);
2414 $column = $kohafield;
2416 # MARC field is not linked to a DB field so we need to use
2417 # ExtractValue on marcxml from biblio_metadata or
2418 # items.more_subfields_xml, depending on the MARC field.
2421 my ($itemfield) = GetMarcFromKohaField('items.itemnumber');
2422 if ($marcfield eq $itemfield) {
2423 $sqlfield = 'more_subfields_xml';
2424 $xpath = '//record/datafield/subfield[@code="' . $marcsubfield . '"]';
2426 $sqlfield = 'metadata'; # From biblio_metadata
2427 if ($marcfield < 10) {
2428 $xpath = "//record/controlfield[\@tag=\"$marcfield\"]";
2430 $xpath = "//record/datafield[\@tag=\"$marcfield\"]/subfield[\@code=\"$marcsubfield\"]";
2433 $column = "ExtractValue($sqlfield, '$xpath')";
2439 if (ref $query eq 'ARRAY') {
2442 } elsif ($op eq '!=') {
2446 str => "$column $op (" . join (',', ('?') x @$query) . ")",
2451 str => "$column $op ?",
2458 return $where_fragment;
2463 my ($items, $total) = SearchItems($filter, $params);
2465 Perform a search among items
2467 $filter is a reference to a hash which can be a filter, or a combination of filters.
2469 A filter has the following keys:
2473 =item * field: the name of a SQL column in table items
2475 =item * query: the value to search in this column
2477 =item * operator: comparison operator. Can be one of = != > < >= <= like
2481 A combination of filters hash the following keys:
2485 =item * conjunction: 'AND' or 'OR'
2487 =item * filters: array ref of filters
2491 $params is a reference to a hash that can contain the following parameters:
2495 =item * rows: Number of items to return. 0 returns everything (default: 0)
2497 =item * page: Page to return (return items from (page-1)*rows to (page*rows)-1)
2500 =item * sortby: A SQL column name in items table to sort on
2502 =item * sortorder: 'ASC' or 'DESC'
2509 my ($filter, $params) = @_;
2513 return unless ref $filter eq 'HASH';
2514 return unless ref $params eq 'HASH';
2516 # Default parameters
2517 $params->{rows} ||= 0;
2518 $params->{page} ||= 1;
2519 $params->{sortby} ||= 'itemnumber';
2520 $params->{sortorder} ||= 'ASC';
2522 my ($where_str, @where_args);
2523 my $where_fragment = _SearchItems_build_where_fragment($filter);
2524 if ($where_fragment) {
2525 $where_str = $where_fragment->{str};
2526 @where_args = @{ $where_fragment->{args} };
2529 my $dbh = C4::Context->dbh;
2531 SELECT SQL_CALC_FOUND_ROWS items.*
2533 LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber
2534 LEFT JOIN biblioitems ON biblioitems.biblioitemnumber = items.biblioitemnumber
2535 LEFT JOIN biblio_metadata ON biblio_metadata.biblionumber = biblio.biblionumber
2538 if (defined $where_str and $where_str ne '') {
2539 $query .= qq{ AND $where_str };
2542 $query .= q{ AND biblio_metadata.format = 'marcxml' AND biblio_metadata.marcflavour = ? };
2543 push @where_args, C4::Context->preference('marcflavour');
2545 my @columns = Koha::Database->new()->schema()->resultset('Item')->result_source->columns;
2546 push @columns, Koha::Database->new()->schema()->resultset('Biblio')->result_source->columns;
2547 push @columns, Koha::Database->new()->schema()->resultset('Biblioitem')->result_source->columns;
2548 my $sortby = (0 < grep {$params->{sortby} eq $_} @columns)
2549 ? $params->{sortby} : 'itemnumber';
2550 my $sortorder = (uc($params->{sortorder}) eq 'ASC') ? 'ASC' : 'DESC';
2551 $query .= qq{ ORDER BY $sortby $sortorder };
2553 my $rows = $params->{rows};
2556 my $offset = $rows * ($params->{page}-1);
2557 $query .= qq { LIMIT ?, ? };
2558 push @limit_args, $offset, $rows;
2561 my $sth = $dbh->prepare($query);
2562 my $rv = $sth->execute(@where_args, @limit_args);
2564 return unless ($rv);
2565 my ($total_rows) = $dbh->selectrow_array(q{ SELECT FOUND_ROWS() });
2567 return ($sth->fetchall_arrayref({}), $total_rows);
2571 =head1 OTHER FUNCTIONS
2575 ($indicators, $value) = _find_value($tag, $subfield, $record,$encoding);
2577 Find the given $subfield in the given $tag in the given
2578 MARC::Record $record. If the subfield is found, returns
2579 the (indicators, value) pair; otherwise, (undef, undef) is
2583 Such a function is used in addbiblio AND additem and serial-edit and maybe could be used in Authorities.
2584 I suggest we export it from this module.
2589 my ( $tagfield, $insubfield, $record, $encoding ) = @_;
2592 if ( $tagfield < 10 ) {
2593 if ( $record->field($tagfield) ) {
2594 push @result, $record->field($tagfield)->data();
2599 foreach my $field ( $record->field($tagfield) ) {
2600 my @subfields = $field->subfields();
2601 foreach my $subfield (@subfields) {
2602 if ( @$subfield[0] eq $insubfield ) {
2603 push @result, @$subfield[1];
2604 $indicator = $field->indicator(1) . $field->indicator(2);
2609 return ( $indicator, @result );
2613 =head2 PrepareItemrecordDisplay
2615 PrepareItemrecordDisplay($itemrecord,$bibnum,$itemumber,$frameworkcode);
2617 Returns a hash with all the fields for Display a given item data in a template
2619 The $frameworkcode returns the item for the given frameworkcode, ONLY if bibnum is not provided
2623 sub PrepareItemrecordDisplay {
2625 my ( $bibnum, $itemnum, $defaultvalues, $frameworkcode ) = @_;
2627 my $dbh = C4::Context->dbh;
2628 $frameworkcode = &GetFrameworkCode($bibnum) if $bibnum;
2629 my ( $itemtagfield, $itemtagsubfield ) = &GetMarcFromKohaField( "items.itemnumber", $frameworkcode );
2631 # Note: $tagslib obtained from GetMarcStructure() in 'unsafe' mode is
2632 # a shared data structure. No plugin (including custom ones) should change
2633 # its contents. See also GetMarcStructure.
2634 my $tagslib = &GetMarcStructure( 1, $frameworkcode, { unsafe => 1 } );
2636 # return nothing if we don't have found an existing framework.
2637 return q{} unless $tagslib;
2640 $itemrecord = C4::Items::GetMarcItem( $bibnum, $itemnum );
2644 my $branch_limit = C4::Context->userenv ? C4::Context->userenv->{"branch"} : "";
2646 SELECT authorised_value,lib FROM authorised_values
2649 LEFT JOIN authorised_values_branches ON ( id = av_id )
2654 $query .= qq{ AND ( branchcode = ? OR branchcode IS NULL )} if $branch_limit;
2655 $query .= qq{ ORDER BY lib};
2656 my $authorised_values_sth = $dbh->prepare( $query );
2657 foreach my $tag ( sort keys %{$tagslib} ) {
2660 # loop through each subfield
2662 foreach my $subfield ( sort keys %{ $tagslib->{$tag} } ) {
2663 next if IsMarcStructureInternal($tagslib->{$tag}{$subfield});
2664 next unless ( $tagslib->{$tag}->{$subfield}->{'tab'} );
2665 next if ( $tagslib->{$tag}->{$subfield}->{'tab'} ne "10" );
2667 $subfield_data{tag} = $tag;
2668 $subfield_data{subfield} = $subfield;
2669 $subfield_data{countsubfield} = $cntsubf++;
2670 $subfield_data{kohafield} = $tagslib->{$tag}->{$subfield}->{'kohafield'};
2671 $subfield_data{id} = "tag_".$tag."_subfield_".$subfield."_".int(rand(1000000));
2673 # $subfield_data{marc_lib}=$tagslib->{$tag}->{$subfield}->{lib};
2674 $subfield_data{marc_lib} = $tagslib->{$tag}->{$subfield}->{lib};
2675 $subfield_data{mandatory} = $tagslib->{$tag}->{$subfield}->{mandatory};
2676 $subfield_data{repeatable} = $tagslib->{$tag}->{$subfield}->{repeatable};
2677 $subfield_data{hidden} = "display:none"
2678 if ( ( $tagslib->{$tag}->{$subfield}->{hidden} > 4 )
2679 || ( $tagslib->{$tag}->{$subfield}->{hidden} < -4 ) );
2680 my ( $x, $defaultvalue );
2682 ( $x, $defaultvalue ) = _find_value( $tag, $subfield, $itemrecord );
2684 $defaultvalue = $tagslib->{$tag}->{$subfield}->{defaultvalue} unless $defaultvalue;
2685 if ( !defined $defaultvalue ) {
2686 $defaultvalue = q||;
2688 $defaultvalue =~ s/"/"/g;
2691 # search for itemcallnumber if applicable
2692 if ( $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.itemcallnumber'
2693 && C4::Context->preference('itemcallnumber') ) {
2694 my $CNtag = substr( C4::Context->preference('itemcallnumber'), 0, 3 );
2695 my $CNsubfield = substr( C4::Context->preference('itemcallnumber'), 3, 1 );
2696 if ( $itemrecord and my $field = $itemrecord->field($CNtag) ) {
2697 $defaultvalue = $field->subfield($CNsubfield);
2700 if ( $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.itemcallnumber'
2702 && $defaultvalues->{'callnumber'} ) {
2703 if( $itemrecord and $defaultvalues and not $itemrecord->subfield($tag,$subfield) ){
2704 # if the item record exists, only use default value if the item has no callnumber
2705 $defaultvalue = $defaultvalues->{callnumber};
2706 } elsif ( !$itemrecord and $defaultvalues ) {
2707 # if the item record *doesn't* exists, always use the default value
2708 $defaultvalue = $defaultvalues->{callnumber};
2711 if ( ( $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.holdingbranch' || $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.homebranch' )
2713 && $defaultvalues->{'branchcode'} ) {
2714 if ( $itemrecord and $defaultvalues and not $itemrecord->subfield($tag,$subfield) ) {
2715 $defaultvalue = $defaultvalues->{branchcode};
2718 if ( ( $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.location' )
2720 && $defaultvalues->{'location'} ) {
2722 if ( $itemrecord and $defaultvalues and not $itemrecord->subfield($tag,$subfield) ) {
2723 # if the item record exists, only use default value if the item has no locationr
2724 $defaultvalue = $defaultvalues->{location};
2725 } elsif ( !$itemrecord and $defaultvalues ) {
2726 # if the item record *doesn't* exists, always use the default value
2727 $defaultvalue = $defaultvalues->{location};
2730 if ( $tagslib->{$tag}->{$subfield}->{authorised_value} ) {
2731 my @authorised_values;
2734 # builds list, depending on authorised value...
2736 if ( $tagslib->{$tag}->{$subfield}->{'authorised_value'} eq "branches" ) {
2737 if ( ( C4::Context->preference("IndependentBranches") )
2738 && !C4::Context->IsSuperLibrarian() ) {
2739 my $sth = $dbh->prepare( "SELECT branchcode,branchname FROM branches WHERE branchcode = ? ORDER BY branchname" );
2740 $sth->execute( C4::Context->userenv->{branch} );
2741 push @authorised_values, ""
2742 unless ( $tagslib->{$tag}->{$subfield}->{mandatory} );
2743 while ( my ( $branchcode, $branchname ) = $sth->fetchrow_array ) {
2744 push @authorised_values, $branchcode;
2745 $authorised_lib{$branchcode} = $branchname;
2748 my $sth = $dbh->prepare( "SELECT branchcode,branchname FROM branches ORDER BY branchname" );
2750 push @authorised_values, ""
2751 unless ( $tagslib->{$tag}->{$subfield}->{mandatory} );
2752 while ( my ( $branchcode, $branchname ) = $sth->fetchrow_array ) {
2753 push @authorised_values, $branchcode;
2754 $authorised_lib{$branchcode} = $branchname;
2758 $defaultvalue = C4::Context->userenv ? C4::Context->userenv->{branch} : undef;
2759 if ( $defaultvalues and $defaultvalues->{branchcode} ) {
2760 $defaultvalue = $defaultvalues->{branchcode};
2764 } elsif ( $tagslib->{$tag}->{$subfield}->{authorised_value} eq "itemtypes" ) {
2765 my $itemtypes = Koha::ItemTypes->search_with_localization;
2766 push @authorised_values, ""
2767 unless ( $tagslib->{$tag}->{$subfield}->{mandatory} );
2768 while ( my $itemtype = $itemtypes->next ) {
2769 push @authorised_values, $itemtype->itemtype;
2770 $authorised_lib{$itemtype->itemtype} = $itemtype->translated_description;
2772 if ($defaultvalues && $defaultvalues->{'itemtype'}) {
2773 $defaultvalue = $defaultvalues->{'itemtype'};
2777 } elsif ( $tagslib->{$tag}->{$subfield}->{authorised_value} eq "cn_source" ) {
2778 push @authorised_values, "" unless ( $tagslib->{$tag}->{$subfield}->{mandatory} );
2780 my $class_sources = GetClassSources();
2781 my $default_source = C4::Context->preference("DefaultClassificationSource");
2783 foreach my $class_source (sort keys %$class_sources) {
2784 next unless $class_sources->{$class_source}->{'used'} or
2785 ($class_source eq $default_source);
2786 push @authorised_values, $class_source;
2787 $authorised_lib{$class_source} = $class_sources->{$class_source}->{'description'};
2790 $defaultvalue = $default_source;
2792 #---- "true" authorised value
2794 $authorised_values_sth->execute(
2795 $tagslib->{$tag}->{$subfield}->{authorised_value},
2796 $branch_limit ? $branch_limit : ()
2798 push @authorised_values, ""
2799 unless ( $tagslib->{$tag}->{$subfield}->{mandatory} );
2800 while ( my ( $value, $lib ) = $authorised_values_sth->fetchrow_array ) {
2801 push @authorised_values, $value;
2802 $authorised_lib{$value} = $lib;
2805 $subfield_data{marc_value} = {
2807 values => \@authorised_values,
2808 default => "$defaultvalue",
2809 labels => \%authorised_lib,
2811 } elsif ( $tagslib->{$tag}->{$subfield}->{value_builder} ) {
2813 require Koha::FrameworkPlugin;
2814 my $plugin = Koha::FrameworkPlugin->new({
2815 name => $tagslib->{$tag}->{$subfield}->{value_builder},
2818 my $pars = { dbh => $dbh, record => undef, tagslib =>$tagslib, id => $subfield_data{id}, tabloop => undef };
2819 $plugin->build( $pars );
2820 if ( $itemrecord and my $field = $itemrecord->field($tag) ) {
2821 $defaultvalue = $field->subfield($subfield);
2823 if( !$plugin->errstr ) {
2824 #TODO Move html to template; see report 12176/13397
2825 my $tab= $plugin->noclick? '-1': '';
2826 my $class= $plugin->noclick? ' disabled': '';
2827 my $title= $plugin->noclick? 'No popup': 'Tag editor';
2828 $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;
2830 warn $plugin->errstr;
2831 $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
2834 elsif ( $tag eq '' ) { # it's an hidden field
2835 $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" />);
2837 elsif ( $tagslib->{$tag}->{$subfield}->{'hidden'} ) { # FIXME: shouldn't input type be "hidden" ?
2838 $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" />);
2840 elsif ( length($defaultvalue) > 100
2841 or (C4::Context->preference("marcflavour") eq "UNIMARC" and
2842 300 <= $tag && $tag < 400 && $subfield eq 'a' )
2843 or (C4::Context->preference("marcflavour") eq "MARC21" and
2844 500 <= $tag && $tag < 600 )
2846 # oversize field (textarea)
2847 $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");
2849 $subfield_data{marc_value} = "<input type=\"text\" name=\"field_value\" value=\"$defaultvalue\" size=\"50\" maxlength=\"255\" />";
2851 push( @loop_data, \%subfield_data );
2856 if ( $itemrecord && $itemrecord->field($itemtagfield) ) {
2857 $itemnumber = $itemrecord->subfield( $itemtagfield, $itemtagsubfield );
2860 'itemtagfield' => $itemtagfield,
2861 'itemtagsubfield' => $itemtagsubfield,
2862 'itemnumber' => $itemnumber,
2863 'iteminformation' => \@loop_data
2867 sub ToggleNewStatus {
2868 my ( $params ) = @_;
2869 my @rules = @{ $params->{rules} };
2870 my $report_only = $params->{report_only};
2872 my $dbh = C4::Context->dbh;
2874 my @item_columns = map { "items.$_" } Koha::Items->columns;
2875 my @biblioitem_columns = map { "biblioitems.$_" } Koha::Biblioitems->columns;
2877 for my $rule ( @rules ) {
2878 my $age = $rule->{age};
2879 my $conditions = $rule->{conditions};
2880 my $substitutions = $rule->{substitutions};
2884 SELECT items.biblionumber, items.itemnumber
2886 LEFT JOIN biblioitems ON biblioitems.biblionumber = items.biblionumber
2889 for my $condition ( @$conditions ) {
2891 grep {/^$condition->{field}$/} @item_columns
2892 or grep {/^$condition->{field}$/} @biblioitem_columns
2894 if ( $condition->{value} =~ /\|/ ) {
2895 my @values = split /\|/, $condition->{value};
2896 $query .= qq| AND $condition->{field} IN (|
2897 . join( ',', ('?') x scalar @values )
2899 push @params, @values;
2901 $query .= qq| AND $condition->{field} = ?|;
2902 push @params, $condition->{value};
2906 if ( defined $age ) {
2907 $query .= q| AND TO_DAYS(NOW()) - TO_DAYS(dateaccessioned) >= ? |;
2910 my $sth = $dbh->prepare($query);
2911 $sth->execute( @params );
2912 while ( my $values = $sth->fetchrow_hashref ) {
2913 my $biblionumber = $values->{biblionumber};
2914 my $itemnumber = $values->{itemnumber};
2915 my $item = C4::Items::GetItem( $itemnumber );
2916 for my $substitution ( @$substitutions ) {
2917 next unless $substitution->{field};
2918 C4::Items::ModItem( {$substitution->{field} => $substitution->{value}}, $biblionumber, $itemnumber )
2919 unless $report_only;
2920 push @{ $report->{$itemnumber} }, $substitution;