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
37 use Koha::DateUtils qw/dt_from_string/;
41 use Koha::SearchEngine;
42 use Koha::SearchEngine::Search;
44 use vars qw(@ISA @EXPORT);
49 @ISA = qw( Exporter );
72 GetItemsByBiblioitemnumber
76 GetItemnumbersForBiblio
78 get_hostitemnumbers_of
79 GetItemnumberFromBarcode
80 GetBarcodeFromItemnumber
95 PrepareItemrecordDisplay
102 C4::Items - item management functions
106 This module contains an API for manipulating item
107 records in Koha, and is used by cataloguing, circulation,
108 acquisitions, and serials management.
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 my $sth = $dbh->prepare("
159 WHERE itemnumber = ?");
160 $sth->execute($itemnumber);
161 $data = $sth->fetchrow_hashref;
163 my $sth = $dbh->prepare("
167 $sth->execute($barcode);
168 $data = $sth->fetchrow_hashref;
171 return unless ( $data );
174 my $ssth = $dbh->prepare("SELECT serialseq,publisheddate from serialitems left join serial on serialitems.serialid=serial.serialid where serialitems.itemnumber=?");
175 $ssth->execute($data->{'itemnumber'}) ;
176 ($data->{'serialseq'} , $data->{'publisheddate'}) = $ssth->fetchrow_array();
178 #if we don't have an items.itype, use biblioitems.itemtype.
179 # FIXME this should respect the itypes systempreference
180 # if (C4::Context->preference('item-level_itypes')) {
181 if( ! $data->{'itype'} ) {
182 my $sth = $dbh->prepare("SELECT itemtype FROM biblioitems WHERE biblionumber = ?");
183 $sth->execute($data->{'biblionumber'});
184 ($data->{'itype'}) = $sth->fetchrow_array;
191 CartToShelf($itemnumber);
193 Set the current shelving location of the item record
194 to its stored permanent shelving location. This is
195 primarily used to indicate when an item whose current
196 location is a special processing ('PROC') or shelving cart
197 ('CART') location is back in the stacks.
202 my ( $itemnumber ) = @_;
204 unless ( $itemnumber ) {
205 croak "FAILED CartToShelf() - no itemnumber supplied";
208 my $item = GetItem($itemnumber);
209 if ( $item->{location} eq 'CART' ) {
210 $item->{location} = $item->{permanent_location};
211 ModItem($item, undef, $itemnumber);
217 ShelfToCart($itemnumber);
219 Set the current shelving location of the item
220 to shelving cart ('CART').
225 my ( $itemnumber ) = @_;
227 unless ( $itemnumber ) {
228 croak "FAILED ShelfToCart() - no itemnumber supplied";
231 my $item = GetItem($itemnumber);
232 $item->{'location'} = 'CART';
233 ModItem($item, undef, $itemnumber);
236 =head2 AddItemFromMarc
238 my ($biblionumber, $biblioitemnumber, $itemnumber)
239 = AddItemFromMarc($source_item_marc, $biblionumber);
241 Given a MARC::Record object containing an embedded item
242 record and a biblionumber, create a new item record.
246 sub AddItemFromMarc {
247 my ( $source_item_marc, $biblionumber ) = @_;
248 my $dbh = C4::Context->dbh;
250 # parse item hash from MARC
251 my $frameworkcode = GetFrameworkCode( $biblionumber );
252 my ($itemtag,$itemsubfield)=GetMarcFromKohaField("items.itemnumber",$frameworkcode);
254 my $localitemmarc=MARC::Record->new;
255 $localitemmarc->append_fields($source_item_marc->field($itemtag));
256 my $item = &TransformMarcToKoha( $localitemmarc, $frameworkcode ,'items');
257 my $unlinked_item_subfields = _get_unlinked_item_subfields($localitemmarc, $frameworkcode);
258 return AddItem($item, $biblionumber, $dbh, $frameworkcode, $unlinked_item_subfields);
263 my ($biblionumber, $biblioitemnumber, $itemnumber)
264 = AddItem($item, $biblionumber[, $dbh, $frameworkcode, $unlinked_item_subfields]);
266 Given a hash containing item column names as keys,
267 create a new Koha item record.
269 The first two optional parameters (C<$dbh> and C<$frameworkcode>)
270 do not need to be supplied for general use; they exist
271 simply to allow them to be picked up from AddItemFromMarc.
273 The final optional parameter, C<$unlinked_item_subfields>, contains
274 an arrayref containing subfields present in the original MARC
275 representation of the item (e.g., from the item editor) that are
276 not mapped to C<items> columns directly but should instead
277 be stored in C<items.more_subfields_xml> and included in
278 the biblio items tag for display and indexing.
284 my $biblionumber = shift;
286 my $dbh = @_ ? shift : C4::Context->dbh;
287 my $frameworkcode = @_ ? shift : GetFrameworkCode( $biblionumber );
288 my $unlinked_item_subfields;
290 $unlinked_item_subfields = shift
293 # needs old biblionumber and biblioitemnumber
294 $item->{'biblionumber'} = $biblionumber;
295 my $sth = $dbh->prepare("SELECT biblioitemnumber FROM biblioitems WHERE biblionumber=?");
296 $sth->execute( $item->{'biblionumber'} );
297 ($item->{'biblioitemnumber'}) = $sth->fetchrow;
299 _set_defaults_for_add($item);
300 _set_derived_columns_for_add($item);
301 $item->{'more_subfields_xml'} = _get_unlinked_subfields_xml($unlinked_item_subfields);
302 # FIXME - checks here
303 unless ( $item->{itype} ) { # default to biblioitem.itemtype if no itype
304 my $itype_sth = $dbh->prepare("SELECT itemtype FROM biblioitems WHERE biblionumber = ?");
305 $itype_sth->execute( $item->{'biblionumber'} );
306 ( $item->{'itype'} ) = $itype_sth->fetchrow_array;
309 my ( $itemnumber, $error ) = _koha_new_item( $item, $item->{barcode} );
310 $item->{'itemnumber'} = $itemnumber;
312 ModZebra( $item->{biblionumber}, "specialUpdate", "biblioserver" );
314 logaction("CATALOGUING", "ADD", $itemnumber, "item") if C4::Context->preference("CataloguingLog");
316 return ($item->{biblionumber}, $item->{biblioitemnumber}, $itemnumber);
319 =head2 AddItemBatchFromMarc
321 ($itemnumber_ref, $error_ref) = AddItemBatchFromMarc($record,
322 $biblionumber, $biblioitemnumber, $frameworkcode);
324 Efficiently create item records from a MARC biblio record with
325 embedded item fields. This routine is suitable for batch jobs.
327 This API assumes that the bib record has already been
328 saved to the C<biblio> and C<biblioitems> tables. It does
329 not expect that C<biblioitems.marc> and C<biblioitems.marcxml>
330 are populated, but it will do so via a call to ModBibiloMarc.
332 The goal of this API is to have a similar effect to using AddBiblio
333 and AddItems in succession, but without inefficient repeated
334 parsing of the MARC XML bib record.
336 This function returns an arrayref of new itemsnumbers and an arrayref of item
337 errors encountered during the processing. Each entry in the errors
338 list is a hashref containing the following keys:
344 Sequence number of original item tag in the MARC record.
348 Item barcode, provide to assist in the construction of
349 useful error messages.
353 Code representing the error condition. Can be 'duplicate_barcode',
354 'invalid_homebranch', or 'invalid_holdingbranch'.
356 =item error_information
358 Additional information appropriate to the error condition.
364 sub AddItemBatchFromMarc {
365 my ($record, $biblionumber, $biblioitemnumber, $frameworkcode) = @_;
367 my @itemnumbers = ();
369 my $dbh = C4::Context->dbh;
371 # We modify the record, so lets work on a clone so we don't change the
373 $record = $record->clone();
374 # loop through the item tags and start creating items
375 my @bad_item_fields = ();
376 my ($itemtag, $itemsubfield) = &GetMarcFromKohaField("items.itemnumber",'');
377 my $item_sequence_num = 0;
378 ITEMFIELD: foreach my $item_field ($record->field($itemtag)) {
379 $item_sequence_num++;
380 # we take the item field and stick it into a new
381 # MARC record -- this is required so far because (FIXME)
382 # TransformMarcToKoha requires a MARC::Record, not a MARC::Field
383 # and there is no TransformMarcFieldToKoha
384 my $temp_item_marc = MARC::Record->new();
385 $temp_item_marc->append_fields($item_field);
387 # add biblionumber and biblioitemnumber
388 my $item = TransformMarcToKoha( $temp_item_marc, $frameworkcode, 'items' );
389 my $unlinked_item_subfields = _get_unlinked_item_subfields($temp_item_marc, $frameworkcode);
390 $item->{'more_subfields_xml'} = _get_unlinked_subfields_xml($unlinked_item_subfields);
391 $item->{'biblionumber'} = $biblionumber;
392 $item->{'biblioitemnumber'} = $biblioitemnumber;
394 # check for duplicate barcode
395 my %item_errors = CheckItemPreSave($item);
397 push @errors, _repack_item_errors($item_sequence_num, $item, \%item_errors);
398 push @bad_item_fields, $item_field;
402 _set_defaults_for_add($item);
403 _set_derived_columns_for_add($item);
404 my ( $itemnumber, $error ) = _koha_new_item( $item, $item->{barcode} );
405 warn $error if $error;
406 push @itemnumbers, $itemnumber; # FIXME not checking error
407 $item->{'itemnumber'} = $itemnumber;
409 logaction("CATALOGUING", "ADD", $itemnumber, "item") if C4::Context->preference("CataloguingLog");
411 my $new_item_marc = _marc_from_item_hash($item, $frameworkcode, $unlinked_item_subfields);
412 $item_field->replace_with($new_item_marc->field($itemtag));
415 # remove any MARC item fields for rejected items
416 foreach my $item_field (@bad_item_fields) {
417 $record->delete_field($item_field);
420 # update the MARC biblio
421 # $biblionumber = ModBiblioMarc( $record, $biblionumber, $frameworkcode );
423 return (\@itemnumbers, \@errors);
426 =head2 ModItemFromMarc
428 ModItemFromMarc($item_marc, $biblionumber, $itemnumber);
430 This function updates an item record based on a supplied
431 C<MARC::Record> object containing an embedded item field.
432 This API is meant for the use of C<additem.pl>; for
433 other purposes, C<ModItem> should be used.
435 This function uses the hash %default_values_for_mod_from_marc,
436 which contains default values for item fields to
437 apply when modifying an item. This is needed because
438 if an item field's value is cleared, TransformMarcToKoha
439 does not include the column in the
440 hash that's passed to ModItem, which without
441 use of this hash makes it impossible to clear
442 an item field's value. See bug 2466.
444 Note that only columns that can be directly
445 changed from the cataloging and serials
446 item editors are included in this hash.
452 our %default_values_for_mod_from_marc;
454 sub _build_default_values_for_mod_marc {
455 my ($frameworkcode) = @_;
456 return $default_values_for_mod_from_marc{$frameworkcode}
457 if exists $default_values_for_mod_from_marc{$frameworkcode};
458 my $default_values = {
460 booksellerid => undef,
462 'items.cn_source' => undef,
463 coded_location_qualifier => undef,
467 holdingbranch => undef,
469 itemcallnumber => undef,
472 itemnotes_nonpublic => undef,
475 permanent_location => undef,
479 # paidfor => undef, # commented, see bug 12817
481 replacementprice => undef,
482 replacementpricedate => undef,
485 stocknumber => undef,
489 while ( my ( $field, $default_value ) = each %$default_values ) {
490 my $kohafield = $field;
491 $kohafield =~ s|^([^\.]+)$|items.$1|;
492 $default_values_for_mod_from_marc{$frameworkcode}{$field} =
494 if C4::Koha::IsKohaFieldLinked(
495 { kohafield => $kohafield, frameworkcode => $frameworkcode } );
497 return $default_values_for_mod_from_marc{$frameworkcode};
500 sub ModItemFromMarc {
501 my $item_marc = shift;
502 my $biblionumber = shift;
503 my $itemnumber = shift;
505 my $dbh = C4::Context->dbh;
506 my $frameworkcode = GetFrameworkCode($biblionumber);
507 my ( $itemtag, $itemsubfield ) = GetMarcFromKohaField( "items.itemnumber", $frameworkcode );
509 my $localitemmarc = MARC::Record->new;
510 $localitemmarc->append_fields( $item_marc->field($itemtag) );
511 my $item = &TransformMarcToKoha( $localitemmarc, $frameworkcode, 'items' );
512 my $default_values = _build_default_values_for_mod_marc($frameworkcode);
513 foreach my $item_field ( keys %$default_values ) {
514 $item->{$item_field} = $default_values->{$item_field}
515 unless exists $item->{$item_field};
517 my $unlinked_item_subfields = _get_unlinked_item_subfields( $localitemmarc, $frameworkcode );
519 ModItem($item, $biblionumber, $itemnumber, $dbh, $frameworkcode, $unlinked_item_subfields);
525 ModItem({ column => $newvalue }, $biblionumber, $itemnumber);
527 Change one or more columns in an item record and update
528 the MARC representation of the item.
530 The first argument is a hashref mapping from item column
531 names to the new values. The second and third arguments
532 are the biblionumber and itemnumber, respectively.
534 The fourth, optional parameter, C<$unlinked_item_subfields>, contains
535 an arrayref containing subfields present in the original MARC
536 representation of the item (e.g., from the item editor) that are
537 not mapped to C<items> columns directly but should instead
538 be stored in C<items.more_subfields_xml> and included in
539 the biblio items tag for display and indexing.
541 If one of the changed columns is used to calculate
542 the derived value of a column such as C<items.cn_sort>,
543 this routine will perform the necessary calculation
550 my $biblionumber = shift;
551 my $itemnumber = shift;
553 # if $biblionumber is undefined, get it from the current item
554 unless (defined $biblionumber) {
555 $biblionumber = _get_single_item_column('biblionumber', $itemnumber);
558 my $dbh = @_ ? shift : C4::Context->dbh;
559 my $frameworkcode = @_ ? shift : GetFrameworkCode( $biblionumber );
561 my $unlinked_item_subfields;
563 $unlinked_item_subfields = shift;
564 $item->{'more_subfields_xml'} = _get_unlinked_subfields_xml($unlinked_item_subfields);
567 $item->{'itemnumber'} = $itemnumber or return;
569 my @fields = qw( itemlost withdrawn );
571 # Only call GetItem if we need to set an "on" date field
572 if ( $item->{itemlost} || $item->{withdrawn} ) {
573 my $pre_mod_item = GetItem( $item->{'itemnumber'} );
574 for my $field (@fields) {
575 if ( defined( $item->{$field} )
576 and not $pre_mod_item->{$field}
577 and $item->{$field} )
579 $item->{ $field . '_on' } =
580 DateTime::Format::MySQL->format_datetime( dt_from_string() );
585 # If the field is defined but empty, we are removing and,
586 # and thus need to clear out the 'on' field as well
587 for my $field (@fields) {
588 if ( defined( $item->{$field} ) && !$item->{$field} ) {
589 $item->{ $field . '_on' } = undef;
594 _set_derived_columns_for_mod($item);
595 _do_column_fixes_for_mod($item);
598 # attempt to change itemnumber
599 # attempt to change biblionumber (if we want
600 # an API to relink an item to a different bib,
601 # it should be a separate function)
604 _koha_modify_item($item);
606 # request that bib be reindexed so that searching on current
607 # item status is possible
608 ModZebra( $biblionumber, "specialUpdate", "biblioserver" );
610 logaction("CATALOGUING", "MODIFY", $itemnumber, "item ".Dumper($item)) if 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 #new entry in branchtransfers....
631 my $sth = $dbh->prepare(
632 "INSERT INTO branchtransfers (itemnumber, frombranch, datesent, tobranch)
633 VALUES (?, ?, NOW(), ?)");
634 $sth->execute($itemnumber, $frombranch, $tobranch);
636 ModItem({ holdingbranch => $tobranch }, undef, $itemnumber);
637 ModDateLastSeen($itemnumber);
641 =head2 ModDateLastSeen
643 ModDateLastSeen($itemnum);
645 Mark item as seen. Is called when an item is issued, returned or manually marked during inventory/stocktaking.
646 C<$itemnum> is the item number
650 sub ModDateLastSeen {
651 my ($itemnumber) = @_;
653 my $today = output_pref({ dt => dt_from_string, dateformat => 'iso', dateonly => 1 });
654 ModItem({ itemlost => 0, datelastseen => $today }, undef, $itemnumber);
659 DelItem({ itemnumber => $itemnumber, [ biblionumber => $biblionumber ] } );
661 Exported function (core API) for deleting an item record in Koha.
668 my $itemnumber = $params->{itemnumber};
669 my $biblionumber = $params->{biblionumber};
671 unless ($biblionumber) {
672 $biblionumber = C4::Biblio::GetBiblionumberFromItemnumber($itemnumber);
675 # If there is no biblionumber for the given itemnumber, there is nothing to delete
676 return 0 unless $biblionumber;
678 # FIXME check the item has no current issues
679 my $deleted = _koha_delete_item( $itemnumber );
681 # get the MARC record
682 my $record = GetMarcBiblio($biblionumber);
683 ModZebra( $biblionumber, "specialUpdate", "biblioserver" );
685 #search item field code
686 logaction("CATALOGUING", "DELETE", $itemnumber, "item") if C4::Context->preference("CataloguingLog");
690 =head2 CheckItemPreSave
692 my $item_ref = TransformMarcToKoha($marc, 'items');
694 my %errors = CheckItemPreSave($item_ref);
695 if (exists $errors{'duplicate_barcode'}) {
696 print "item has duplicate barcode: ", $errors{'duplicate_barcode'}, "\n";
697 } elsif (exists $errors{'invalid_homebranch'}) {
698 print "item has invalid home branch: ", $errors{'invalid_homebranch'}, "\n";
699 } elsif (exists $errors{'invalid_holdingbranch'}) {
700 print "item has invalid holding branch: ", $errors{'invalid_holdingbranch'}, "\n";
705 Given a hashref containing item fields, determine if it can be
706 inserted or updated in the database. Specifically, checks for
707 database integrity issues, and returns a hash containing any
708 of the following keys, if applicable.
712 =item duplicate_barcode
714 Barcode, if it duplicates one already found in the database.
716 =item invalid_homebranch
718 Home branch, if not defined in branches table.
720 =item invalid_holdingbranch
722 Holding branch, if not defined in branches table.
726 This function does NOT implement any policy-related checks,
727 e.g., whether current operator is allowed to save an
728 item that has a given branch code.
732 sub CheckItemPreSave {
733 my $item_ref = shift;
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 $branch_name = C4::Branch::GetBranchName($item_ref->{'homebranch'});
752 unless (defined $branch_name) {
753 # relies on fact that branches.branchname is a non-NULL column,
754 # so GetBranchName returns undef only if branch does not exist
755 $errors{'invalid_homebranch'} = $item_ref->{'homebranch'};
759 # check for valid holding branch
760 if (exists $item_ref->{'holdingbranch'} and defined $item_ref->{'holdingbranch'}) {
761 my $branch_name = C4::Branch::GetBranchName($item_ref->{'holdingbranch'});
762 unless (defined $branch_name) {
763 # relies on fact that branches.branchname is a non-NULL column,
764 # so GetBranchName returns undef only if branch does not exist
765 $errors{'invalid_holdingbranch'} = $item_ref->{'holdingbranch'};
773 =head1 EXPORTED SPECIAL ACCESSOR FUNCTIONS
775 The following functions provide various ways of
776 getting an item record, a set of item records, or
777 lists of authorized values for certain item fields.
779 Some of the functions in this group are candidates
780 for refactoring -- for example, some of the code
781 in C<GetItemsByBiblioitemnumber> and C<GetItemsInfo>
782 has copy-and-paste work.
788 $itemstatushash = GetItemStatus($fwkcode);
790 Returns a list of valid values for the
791 C<items.notforloan> field.
793 NOTE: does B<not> return an individual item's
796 Can be MARC dependent.
798 But basically could be can be loan or not
799 Create a status selector with the following code
801 =head3 in PERL SCRIPT
803 my $itemstatushash = getitemstatus;
805 foreach my $thisstatus (keys %$itemstatushash) {
806 my %row =(value => $thisstatus,
807 statusname => $itemstatushash->{$thisstatus}->{'statusname'},
809 push @itemstatusloop, \%row;
811 $template->param(statusloop=>\@itemstatusloop);
815 <select name="statusloop" id="statusloop">
816 <option value="">Default</option>
817 [% FOREACH statusloo IN statusloop %]
818 [% IF ( statusloo.selected ) %]
819 <option value="[% statusloo.value %]" selected="selected">[% statusloo.statusname %]</option>
821 <option value="[% statusloo.value %]">[% statusloo.statusname %]</option>
830 # returns a reference to a hash of references to status...
833 my $dbh = C4::Context->dbh;
835 $fwk = '' unless ($fwk);
836 my ( $tag, $subfield ) =
837 GetMarcFromKohaField( "items.notforloan", $fwk );
838 if ( $tag and $subfield ) {
841 "SELECT authorised_value
842 FROM marc_subfield_structure
848 $sth->execute( $tag, $subfield, $fwk );
849 if ( my ($authorisedvaluecat) = $sth->fetchrow ) {
852 "SELECT authorised_value,lib
853 FROM authorised_values
858 $authvalsth->execute($authorisedvaluecat);
859 while ( my ( $authorisedvalue, $lib ) = $authvalsth->fetchrow ) {
860 $itemstatus{$authorisedvalue} = $lib;
874 $itemstatus{"1"} = "Not For Loan";
878 =head2 GetItemLocation
880 $itemlochash = GetItemLocation($fwk);
882 Returns a list of valid values for the
883 C<items.location> field.
885 NOTE: does B<not> return an individual item's
888 where fwk stands for an optional framework code.
889 Create a location selector with the following code
891 =head3 in PERL SCRIPT
893 my $itemlochash = getitemlocation;
895 foreach my $thisloc (keys %$itemlochash) {
896 my $selected = 1 if $thisbranch eq $branch;
897 my %row =(locval => $thisloc,
898 selected => $selected,
899 locname => $itemlochash->{$thisloc},
901 push @itemlocloop, \%row;
903 $template->param(itemlocationloop => \@itemlocloop);
907 <select name="location">
908 <option value="">Default</option>
909 <!-- TMPL_LOOP name="itemlocationloop" -->
910 <option value="<!-- TMPL_VAR name="locval" -->" <!-- TMPL_IF name="selected" -->selected<!-- /TMPL_IF -->><!-- TMPL_VAR name="locname" --></option>
916 sub GetItemLocation {
918 # returns a reference to a hash of references to location...
921 my $dbh = C4::Context->dbh;
923 $fwk = '' unless ($fwk);
924 my ( $tag, $subfield ) =
925 GetMarcFromKohaField( "items.location", $fwk );
926 if ( $tag and $subfield ) {
929 "SELECT authorised_value
930 FROM marc_subfield_structure
935 $sth->execute( $tag, $subfield, $fwk );
936 if ( my ($authorisedvaluecat) = $sth->fetchrow ) {
939 "SELECT authorised_value,lib
940 FROM authorised_values
944 $authvalsth->execute($authorisedvaluecat);
945 while ( my ( $authorisedvalue, $lib ) = $authvalsth->fetchrow ) {
946 $itemlocation{$authorisedvalue} = $lib;
948 return \%itemlocation;
960 $itemlocation{"1"} = "Not For Loan";
961 return \%itemlocation;
966 $items = GetLostItems( $where );
968 This function gets a list of lost items.
974 C<$where> is a hashref. it containts a field of the items table as key
975 and the value to match as value. For example:
977 { barcode => 'abc123',
978 homebranch => 'CPL', }
982 C<$items> is a reference to an array full of hashrefs with columns
983 from the "items" table as keys.
985 =item usage in the perl script:
987 my $where = { barcode => '0001548' };
988 my $items = GetLostItems( $where );
989 $template->param( itemsloop => $items );
996 # Getting input args.
998 my $dbh = C4::Context->dbh;
1001 SELECT title, author, lib, itemlost, authorised_value, barcode, datelastseen, price, replacementprice, homebranch,
1002 itype, itemtype, holdingbranch, location, itemnotes, items.biblionumber as biblionumber, itemcallnumber
1004 LEFT JOIN biblio ON (items.biblionumber = biblio.biblionumber)
1005 LEFT JOIN biblioitems ON (items.biblionumber = biblioitems.biblionumber)
1006 LEFT JOIN authorised_values ON (items.itemlost = authorised_values.authorised_value)
1008 authorised_values.category = 'LOST'
1009 AND itemlost IS NOT NULL
1012 my @query_parameters;
1013 foreach my $key (keys %$where) {
1014 $query .= " AND $key LIKE ?";
1015 push @query_parameters, "%$where->{$key}%";
1018 my $sth = $dbh->prepare($query);
1019 $sth->execute( @query_parameters );
1021 while ( my $row = $sth->fetchrow_hashref ){
1027 =head2 GetItemsForInventory
1029 ($itemlist, $iTotalRecords) = GetItemsForInventory( {
1030 minlocation => $minlocation,
1031 maxlocation => $maxlocation,
1032 location => $location,
1033 itemtype => $itemtype,
1034 ignoreissued => $ignoreissued,
1035 datelastseen => $datelastseen,
1036 branchcode => $branchcode,
1040 statushash => $statushash,
1041 interface => $interface,
1044 Retrieve a list of title/authors/barcode/callnumber, for biblio inventory.
1046 The sub returns a reference to a list of hashes, each containing
1047 itemnumber, author, title, barcode, item callnumber, and date last
1048 seen. It is ordered by callnumber then title.
1050 The required minlocation & maxlocation parameters are used to specify a range of item callnumbers
1051 the datelastseen can be used to specify that you want to see items not seen since a past date only.
1052 offset & size can be used to retrieve only a part of the whole listing (defaut behaviour)
1053 $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.
1055 $iTotalRecords is the number of rows that would have been returned without the $offset, $size limit clause
1059 sub GetItemsForInventory {
1060 my ( $parameters ) = @_;
1061 my $minlocation = $parameters->{'minlocation'} // '';
1062 my $maxlocation = $parameters->{'maxlocation'} // '';
1063 my $location = $parameters->{'location'} // '';
1064 my $itemtype = $parameters->{'itemtype'} // '';
1065 my $ignoreissued = $parameters->{'ignoreissued'} // '';
1066 my $datelastseen = $parameters->{'datelastseen'} // '';
1067 my $branchcode = $parameters->{'branchcode'} // '';
1068 my $branch = $parameters->{'branch'} // '';
1069 my $offset = $parameters->{'offset'} // '';
1070 my $size = $parameters->{'size'} // '';
1071 my $statushash = $parameters->{'statushash'} // '';
1072 my $interface = $parameters->{'interface'} // '';
1074 my $dbh = C4::Context->dbh;
1075 my ( @bind_params, @where_strings );
1077 my $select_columns = q{
1078 SELECT items.itemnumber, barcode, itemcallnumber, title, author, biblio.biblionumber, biblio.frameworkcode, datelastseen, homebranch, location, notforloan, damaged, itemlost, withdrawn, stocknumber
1080 my $select_count = q{SELECT COUNT(*)};
1083 LEFT JOIN biblio ON items.biblionumber = biblio.biblionumber
1084 LEFT JOIN biblioitems on items.biblionumber = biblioitems.biblionumber
1087 for my $authvfield (keys %$statushash){
1088 if ( scalar @{$statushash->{$authvfield}} > 0 ){
1089 my $joinedvals = join ',', @{$statushash->{$authvfield}};
1090 push @where_strings, "$authvfield in (" . $joinedvals . ")";
1096 push @where_strings, 'itemcallnumber >= ?';
1097 push @bind_params, $minlocation;
1101 push @where_strings, 'itemcallnumber <= ?';
1102 push @bind_params, $maxlocation;
1105 if ($datelastseen) {
1106 $datelastseen = output_pref({ str => $datelastseen, dateformat => 'iso', dateonly => 1 });
1107 push @where_strings, '(datelastseen < ? OR datelastseen IS NULL)';
1108 push @bind_params, $datelastseen;
1112 push @where_strings, 'items.location = ?';
1113 push @bind_params, $location;
1116 if ( $branchcode ) {
1117 if($branch eq "homebranch"){
1118 push @where_strings, 'items.homebranch = ?';
1120 push @where_strings, 'items.holdingbranch = ?';
1122 push @bind_params, $branchcode;
1126 push @where_strings, 'biblioitems.itemtype = ?';
1127 push @bind_params, $itemtype;
1130 if ( $ignoreissued) {
1131 $query .= "LEFT JOIN issues ON items.itemnumber = issues.itemnumber ";
1132 push @where_strings, 'issues.date_due IS NULL';
1135 if ( @where_strings ) {
1137 $query .= join ' AND ', @where_strings;
1139 $query .= ' ORDER BY items.cn_sort, itemcallnumber, title';
1140 my $count_query = $select_count . $query;
1141 $query .= " LIMIT $offset, $size" if ($offset and $size);
1142 $query = $select_columns . $query;
1143 my $sth = $dbh->prepare($query);
1144 $sth->execute( @bind_params );
1147 my $tmpresults = $sth->fetchall_arrayref({});
1148 $sth = $dbh->prepare( $count_query );
1149 $sth->execute( @bind_params );
1150 my ($iTotalRecords) = $sth->fetchrow_array();
1152 my $avmapping = C4::Koha::GetKohaAuthorisedValuesMapping( {
1153 interface => $interface
1155 foreach my $row (@$tmpresults) {
1158 foreach (keys %$row) {
1159 if (defined($avmapping->{"items.$_,".$row->{'frameworkcode'}.",".$row->{$_}})) {
1160 $row->{$_} = $avmapping->{"items.$_,".$row->{'frameworkcode'}.",".$row->{$_}};
1163 push @results, $row;
1166 return (\@results, $iTotalRecords);
1169 =head2 GetItemsCount
1171 $count = &GetItemsCount( $biblionumber);
1173 This function return count of item with $biblionumber
1178 my ( $biblionumber ) = @_;
1179 my $dbh = C4::Context->dbh;
1180 my $query = "SELECT count(*)
1182 WHERE biblionumber=?";
1183 my $sth = $dbh->prepare($query);
1184 $sth->execute($biblionumber);
1185 my $count = $sth->fetchrow;
1189 =head2 GetItemInfosOf
1191 GetItemInfosOf(@itemnumbers);
1195 sub GetItemInfosOf {
1196 my @itemnumbers = @_;
1198 my $itemnumber_values = @itemnumbers ? join( ',', @itemnumbers ) : "''";
1203 WHERE itemnumber IN ($itemnumber_values)
1205 return get_infos_of( $query, 'itemnumber' );
1208 =head2 GetItemsByBiblioitemnumber
1210 GetItemsByBiblioitemnumber($biblioitemnumber);
1212 Returns an arrayref of hashrefs suitable for use in a TMPL_LOOP
1213 Called by C<C4::XISBN>
1217 sub GetItemsByBiblioitemnumber {
1218 my ( $bibitem ) = @_;
1219 my $dbh = C4::Context->dbh;
1220 my $sth = $dbh->prepare("SELECT * FROM items WHERE items.biblioitemnumber = ?") || die $dbh->errstr;
1221 # Get all items attached to a biblioitem
1224 $sth->execute($bibitem) || die $sth->errstr;
1225 while ( my $data = $sth->fetchrow_hashref ) {
1226 # Foreach item, get circulation information
1227 my $sth2 = $dbh->prepare( "SELECT * FROM issues,borrowers
1228 WHERE itemnumber = ?
1229 AND issues.borrowernumber = borrowers.borrowernumber"
1231 $sth2->execute( $data->{'itemnumber'} );
1232 if ( my $data2 = $sth2->fetchrow_hashref ) {
1233 # if item is out, set the due date and who it is out too
1234 $data->{'date_due'} = $data2->{'date_due'};
1235 $data->{'cardnumber'} = $data2->{'cardnumber'};
1236 $data->{'borrowernumber'} = $data2->{'borrowernumber'};
1239 # set date_due to blank, so in the template we check itemlost, and withdrawn
1240 $data->{'date_due'} = '';
1242 # Find the last 3 people who borrowed this item.
1243 my $query2 = "SELECT * FROM old_issues, borrowers WHERE itemnumber = ?
1244 AND old_issues.borrowernumber = borrowers.borrowernumber
1245 ORDER BY returndate desc,timestamp desc LIMIT 3";
1246 $sth2 = $dbh->prepare($query2) || die $dbh->errstr;
1247 $sth2->execute( $data->{'itemnumber'} ) || die $sth2->errstr;
1249 while ( my $data2 = $sth2->fetchrow_hashref ) {
1250 $data->{"timestamp$i2"} = $data2->{'timestamp'};
1251 $data->{"card$i2"} = $data2->{'cardnumber'};
1252 $data->{"borrower$i2"} = $data2->{'borrowernumber'};
1255 push(@results,$data);
1262 @results = GetItemsInfo($biblionumber);
1264 Returns information about items with the given biblionumber.
1266 C<GetItemsInfo> returns a list of references-to-hash. Each element
1267 contains a number of keys. Most of them are attributes from the
1268 C<biblio>, C<biblioitems>, C<items>, and C<itemtypes> tables in the
1269 Koha database. Other keys include:
1273 =item C<$data-E<gt>{branchname}>
1275 The name (not the code) of the branch to which the book belongs.
1277 =item C<$data-E<gt>{datelastseen}>
1279 This is simply C<items.datelastseen>, except that while the date is
1280 stored in YYYY-MM-DD format in the database, here it is converted to
1281 DD/MM/YYYY format. A NULL date is returned as C<//>.
1283 =item C<$data-E<gt>{datedue}>
1285 =item C<$data-E<gt>{class}>
1287 This is the concatenation of C<biblioitems.classification>, the book's
1288 Dewey code, and C<biblioitems.subclass>.
1290 =item C<$data-E<gt>{ocount}>
1292 I think this is the number of copies of the book available.
1294 =item C<$data-E<gt>{order}>
1296 If this is set, it is set to C<One Order>.
1303 my ( $biblionumber ) = @_;
1304 my $dbh = C4::Context->dbh;
1305 # note biblioitems.* must be avoided to prevent large marc and marcxml fields from killing performance.
1306 require C4::Languages;
1307 my $language = C4::Languages::getlanguage();
1313 biblioitems.itemtype,
1316 biblioitems.publicationyear,
1317 biblioitems.publishercode,
1318 biblioitems.volumedate,
1319 biblioitems.volumedesc,
1322 items.notforloan as itemnotforloan,
1323 issues.borrowernumber,
1324 issues.date_due as datedue,
1325 issues.onsite_checkout,
1326 borrowers.cardnumber,
1328 borrowers.firstname,
1329 borrowers.branchcode as bcode,
1331 serial.publisheddate,
1332 itemtypes.description,
1333 COALESCE( localization.translation, itemtypes.description ) AS translated_description,
1334 itemtypes.notforloan as notforloan_per_itemtype,
1337 holding.opac_info as holding_branch_opac_info,
1338 home.opac_info as home_branch_opac_info
1342 LEFT JOIN branches AS holding ON items.holdingbranch = holding.branchcode
1343 LEFT JOIN branches AS home ON items.homebranch=home.branchcode
1344 LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber
1345 LEFT JOIN biblioitems ON biblioitems.biblioitemnumber = items.biblioitemnumber
1346 LEFT JOIN issues USING (itemnumber)
1347 LEFT JOIN borrowers USING (borrowernumber)
1348 LEFT JOIN serialitems USING (itemnumber)
1349 LEFT JOIN serial USING (serialid)
1350 LEFT JOIN itemtypes ON itemtypes.itemtype = "
1351 . (C4::Context->preference('item-level_itypes') ? 'items.itype' : 'biblioitems.itemtype');
1353 LEFT JOIN localization ON itemtypes.itemtype = localization.code
1354 AND localization.entity = 'itemtypes'
1355 AND localization.lang = ?
1358 $query .= " WHERE items.biblionumber = ? ORDER BY home.branchname, items.enumchron, LPAD( items.copynumber, 8, '0' ), items.dateaccessioned DESC" ;
1359 my $sth = $dbh->prepare($query);
1360 $sth->execute($language, $biblionumber);
1365 my $userenv = C4::Context->userenv;
1366 my $want_not_same_branch = C4::Context->preference("IndependentBranches") && !C4::Context->IsSuperLibrarian();
1367 while ( my $data = $sth->fetchrow_hashref ) {
1368 if ( $data->{borrowernumber} && $want_not_same_branch) {
1369 $data->{'NOTSAMEBRANCH'} = $data->{'bcode'} ne $userenv->{branch};
1372 $serial ||= $data->{'serial'};
1374 # get notforloan complete status if applicable
1375 if ( my $code = C4::Koha::GetAuthValCode( 'items.notforloan', $data->{frameworkcode} ) ) {
1376 $data->{notforloanvalue} = C4::Koha::GetKohaAuthorisedValueLib( $code, $data->{itemnotforloan} );
1377 $data->{notforloanvalueopac} = C4::Koha::GetKohaAuthorisedValueLib( $code, $data->{itemnotforloan}, 1 );
1380 # get restricted status and description if applicable
1381 if ( my $code = C4::Koha::GetAuthValCode( 'items.restricted', $data->{frameworkcode} ) ) {
1382 $data->{restrictedopac} = C4::Koha::GetKohaAuthorisedValueLib( $code, $data->{restricted}, 1 );
1383 $data->{restricted} = C4::Koha::GetKohaAuthorisedValueLib( $code, $data->{restricted} );
1386 # my stack procedures
1387 if ( my $code = C4::Koha::GetAuthValCode( 'items.stack', $data->{frameworkcode} ) ) {
1388 $data->{stack} = C4::Koha::GetKohaAuthorisedValueLib( $code, $data->{stack} );
1391 # Find the last 3 people who borrowed this item.
1392 my $sth2 = $dbh->prepare("SELECT * FROM old_issues,borrowers
1393 WHERE itemnumber = ?
1394 AND old_issues.borrowernumber = borrowers.borrowernumber
1395 ORDER BY returndate DESC
1397 $sth2->execute($data->{'itemnumber'});
1399 while (my $data2 = $sth2->fetchrow_hashref()) {
1400 $data->{"timestamp$ii"} = $data2->{'timestamp'} if $data2->{'timestamp'};
1401 $data->{"card$ii"} = $data2->{'cardnumber'} if $data2->{'cardnumber'};
1402 $data->{"borrower$ii"} = $data2->{'borrowernumber'} if $data2->{'borrowernumber'};
1406 $results[$i] = $data;
1411 ? sort { ($b->{'publisheddate'} || $b->{'enumchron'}) cmp ($a->{'publisheddate'} || $a->{'enumchron'}) } @results
1415 =head2 GetItemsLocationInfo
1417 my @itemlocinfo = GetItemsLocationInfo($biblionumber);
1419 Returns the branch names, shelving location and itemcallnumber for each item attached to the biblio in question
1421 C<GetItemsInfo> returns a list of references-to-hash. Data returned:
1425 =item C<$data-E<gt>{homebranch}>
1427 Branch Name of the item's homebranch
1429 =item C<$data-E<gt>{holdingbranch}>
1431 Branch Name of the item's holdingbranch
1433 =item C<$data-E<gt>{location}>
1435 Item's shelving location code
1437 =item C<$data-E<gt>{location_intranet}>
1439 The intranet description for the Shelving Location as set in authorised_values 'LOC'
1441 =item C<$data-E<gt>{location_opac}>
1443 The OPAC description for the Shelving Location as set in authorised_values 'LOC'. Falls back to intranet description if no OPAC
1446 =item C<$data-E<gt>{itemcallnumber}>
1448 Item's itemcallnumber
1450 =item C<$data-E<gt>{cn_sort}>
1452 Item's call number normalized for sorting
1458 sub GetItemsLocationInfo {
1459 my $biblionumber = shift;
1462 my $dbh = C4::Context->dbh;
1463 my $query = "SELECT a.branchname as homebranch, b.branchname as holdingbranch,
1464 location, itemcallnumber, cn_sort
1465 FROM items, branches as a, branches as b
1466 WHERE homebranch = a.branchcode AND holdingbranch = b.branchcode
1467 AND biblionumber = ?
1468 ORDER BY cn_sort ASC";
1469 my $sth = $dbh->prepare($query);
1470 $sth->execute($biblionumber);
1472 while ( my $data = $sth->fetchrow_hashref ) {
1473 $data->{location_intranet} = GetKohaAuthorisedValueLib('LOC', $data->{location});
1474 $data->{location_opac}= GetKohaAuthorisedValueLib('LOC', $data->{location}, 1);
1475 push @results, $data;
1480 =head2 GetHostItemsInfo
1482 $hostiteminfo = GetHostItemsInfo($hostfield);
1483 Returns the iteminfo for items linked to records via a host field
1487 sub GetHostItemsInfo {
1489 my @returnitemsInfo;
1491 if (C4::Context->preference('marcflavour') eq 'MARC21' ||
1492 C4::Context->preference('marcflavour') eq 'NORMARC'){
1493 foreach my $hostfield ( $record->field('773') ) {
1494 my $hostbiblionumber = $hostfield->subfield("0");
1495 my $linkeditemnumber = $hostfield->subfield("9");
1496 my @hostitemInfos = GetItemsInfo($hostbiblionumber);
1497 foreach my $hostitemInfo (@hostitemInfos){
1498 if ($hostitemInfo->{itemnumber} eq $linkeditemnumber){
1499 push (@returnitemsInfo,$hostitemInfo);
1504 } elsif ( C4::Context->preference('marcflavour') eq 'UNIMARC'){
1505 foreach my $hostfield ( $record->field('461') ) {
1506 my $hostbiblionumber = $hostfield->subfield("0");
1507 my $linkeditemnumber = $hostfield->subfield("9");
1508 my @hostitemInfos = GetItemsInfo($hostbiblionumber);
1509 foreach my $hostitemInfo (@hostitemInfos){
1510 if ($hostitemInfo->{itemnumber} eq $linkeditemnumber){
1511 push (@returnitemsInfo,$hostitemInfo);
1517 return @returnitemsInfo;
1521 =head2 GetLastAcquisitions
1523 my $lastacq = GetLastAcquisitions({'branches' => ('branch1','branch2'),
1524 'itemtypes' => ('BK','BD')}, 10);
1528 sub GetLastAcquisitions {
1529 my ($data,$max) = @_;
1531 my $itemtype = C4::Context->preference('item-level_itypes') ? 'itype' : 'itemtype';
1533 my $number_of_branches = @{$data->{branches}};
1534 my $number_of_itemtypes = @{$data->{itemtypes}};
1537 my @where = ('WHERE 1 ');
1538 $number_of_branches and push @where
1539 , 'AND holdingbranch IN ('
1540 , join(',', ('?') x $number_of_branches )
1544 $number_of_itemtypes and push @where
1545 , "AND $itemtype IN ("
1546 , join(',', ('?') x $number_of_itemtypes )
1550 my $query = "SELECT biblio.biblionumber as biblionumber, title, dateaccessioned
1551 FROM items RIGHT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
1552 RIGHT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber)
1554 GROUP BY biblio.biblionumber
1555 ORDER BY dateaccessioned DESC LIMIT $max";
1557 my $dbh = C4::Context->dbh;
1558 my $sth = $dbh->prepare($query);
1560 $sth->execute((@{$data->{branches}}, @{$data->{itemtypes}}));
1563 while( my $row = $sth->fetchrow_hashref){
1564 push @results, {date => $row->{dateaccessioned}
1565 , biblionumber => $row->{biblionumber}
1566 , title => $row->{title}};
1572 =head2 GetItemnumbersForBiblio
1574 my $itemnumbers = GetItemnumbersForBiblio($biblionumber);
1576 Given a single biblionumber, return an arrayref of all the corresponding itemnumbers
1580 sub GetItemnumbersForBiblio {
1581 my $biblionumber = shift;
1583 my $dbh = C4::Context->dbh;
1584 my $sth = $dbh->prepare("SELECT itemnumber FROM items WHERE biblionumber = ?");
1585 $sth->execute($biblionumber);
1586 while (my $result = $sth->fetchrow_hashref) {
1587 push @items, $result->{'itemnumber'};
1592 =head2 get_itemnumbers_of
1594 my @itemnumbers_of = get_itemnumbers_of(@biblionumbers);
1596 Given a list of biblionumbers, return the list of corresponding itemnumbers
1597 for each biblionumber.
1599 Return a reference on a hash where keys are biblionumbers and values are
1600 references on array of itemnumbers.
1604 sub get_itemnumbers_of {
1605 my @biblionumbers = @_;
1607 my $dbh = C4::Context->dbh;
1613 WHERE biblionumber IN (?' . ( ',?' x scalar @biblionumbers - 1 ) . ')
1615 my $sth = $dbh->prepare($query);
1616 $sth->execute(@biblionumbers);
1620 while ( my ( $itemnumber, $biblionumber ) = $sth->fetchrow_array ) {
1621 push @{ $itemnumbers_of{$biblionumber} }, $itemnumber;
1624 return \%itemnumbers_of;
1627 =head2 get_hostitemnumbers_of
1629 my @itemnumbers_of = get_hostitemnumbers_of($biblionumber);
1631 Given a biblionumber, return the list of corresponding itemnumbers that are linked to it via host fields
1633 Return a reference on a hash where key is a biblionumber and values are
1634 references on array of itemnumbers.
1639 sub get_hostitemnumbers_of {
1640 my ($biblionumber) = @_;
1641 my $marcrecord = GetMarcBiblio($biblionumber);
1642 my (@returnhostitemnumbers,$tag, $biblio_s, $item_s);
1644 my $marcflavor = C4::Context->preference('marcflavour');
1645 if ($marcflavor eq 'MARC21' || $marcflavor eq 'NORMARC') {
1649 } elsif ($marcflavor eq 'UNIMARC') {
1655 foreach my $hostfield ( $marcrecord->field($tag) ) {
1656 my $hostbiblionumber = $hostfield->subfield($biblio_s);
1657 my $linkeditemnumber = $hostfield->subfield($item_s);
1659 if (my $itemnumbers = get_itemnumbers_of($hostbiblionumber)->{$hostbiblionumber})
1661 @itemnumbers = @$itemnumbers;
1663 foreach my $itemnumber (@itemnumbers){
1664 if ($itemnumber eq $linkeditemnumber){
1665 push (@returnhostitemnumbers,$itemnumber);
1670 return @returnhostitemnumbers;
1674 =head2 GetItemnumberFromBarcode
1676 $result = GetItemnumberFromBarcode($barcode);
1680 sub GetItemnumberFromBarcode {
1682 my $dbh = C4::Context->dbh;
1685 $dbh->prepare("SELECT itemnumber FROM items WHERE items.barcode=?");
1686 $rq->execute($barcode);
1687 my ($result) = $rq->fetchrow;
1691 =head2 GetBarcodeFromItemnumber
1693 $result = GetBarcodeFromItemnumber($itemnumber);
1697 sub GetBarcodeFromItemnumber {
1698 my ($itemnumber) = @_;
1699 my $dbh = C4::Context->dbh;
1702 $dbh->prepare("SELECT barcode FROM items WHERE items.itemnumber=?");
1703 $rq->execute($itemnumber);
1704 my ($result) = $rq->fetchrow;
1708 =head2 GetHiddenItemnumbers
1710 my @itemnumbers_to_hide = GetHiddenItemnumbers(@items);
1712 Given a list of items it checks which should be hidden from the OPAC given
1713 the current configuration. Returns a list of itemnumbers corresponding to
1714 those that should be hidden.
1718 sub GetHiddenItemnumbers {
1722 my $yaml = C4::Context->preference('OpacHiddenItems');
1723 return () if (! $yaml =~ /\S/ );
1724 $yaml = "$yaml\n\n"; # YAML is anal on ending \n. Surplus does not hurt
1727 $hidingrules = YAML::Load($yaml);
1730 warn "Unable to parse OpacHiddenItems syspref : $@";
1733 my $dbh = C4::Context->dbh;
1736 foreach my $item (@items) {
1738 # We check each rule
1739 foreach my $field (keys %$hidingrules) {
1741 if (exists $item->{$field}) {
1742 $val = $item->{$field};
1745 my $query = "SELECT $field from items where itemnumber = ?";
1746 $val = $dbh->selectrow_array($query, undef, $item->{'itemnumber'});
1748 $val = '' unless defined $val;
1750 # If the results matches the values in the yaml file
1751 if (any { $val eq $_ } @{$hidingrules->{$field}}) {
1753 # We add the itemnumber to the list
1754 push @resultitems, $item->{'itemnumber'};
1756 # If at least one rule matched for an item, no need to test the others
1761 return @resultitems;
1764 =head1 LIMITED USE FUNCTIONS
1766 The following functions, while part of the public API,
1767 are not exported. This is generally because they are
1768 meant to be used by only one script for a specific
1769 purpose, and should not be used in any other context
1770 without careful thought.
1776 my $item_marc = GetMarcItem($biblionumber, $itemnumber);
1778 Returns MARC::Record of the item passed in parameter.
1779 This function is meant for use only in C<cataloguing/additem.pl>,
1780 where it is needed to support that script's MARC-like
1786 my ( $biblionumber, $itemnumber ) = @_;
1788 # GetMarcItem has been revised so that it does the following:
1789 # 1. Gets the item information from the items table.
1790 # 2. Converts it to a MARC field for storage in the bib record.
1792 # The previous behavior was:
1793 # 1. Get the bib record.
1794 # 2. Return the MARC tag corresponding to the item record.
1796 # The difference is that one treats the items row as authoritative,
1797 # while the other treats the MARC representation as authoritative
1798 # under certain circumstances.
1800 my $itemrecord = GetItem($itemnumber);
1802 # Tack on 'items.' prefix to column names so that TransformKohaToMarc will work.
1803 # Also, don't emit a subfield if the underlying field is blank.
1806 return Item2Marc($itemrecord,$biblionumber);
1810 my ($itemrecord,$biblionumber)=@_;
1813 defined($itemrecord->{$_}) && $itemrecord->{$_} ne '' ? ("items.$_" => $itemrecord->{$_}) : ()
1814 } keys %{ $itemrecord }
1816 my $itemmarc = TransformKohaToMarc($mungeditem);
1817 my ( $itemtag, $itemsubfield ) = GetMarcFromKohaField("items.itemnumber",GetFrameworkCode($biblionumber)||'');
1819 my $unlinked_item_subfields = _parse_unlinked_item_subfields_from_xml($mungeditem->{'items.more_subfields_xml'});
1820 if (defined $unlinked_item_subfields and $#$unlinked_item_subfields > -1) {
1821 foreach my $field ($itemmarc->field($itemtag)){
1822 $field->add_subfields(@$unlinked_item_subfields);
1828 =head1 PRIVATE FUNCTIONS AND VARIABLES
1830 The following functions are not meant to be called
1831 directly, but are documented in order to explain
1832 the inner workings of C<C4::Items>.
1836 =head2 %derived_columns
1838 This hash keeps track of item columns that
1839 are strictly derived from other columns in
1840 the item record and are not meant to be set
1843 Each key in the hash should be the name of a
1844 column (as named by TransformMarcToKoha). Each
1845 value should be hashref whose keys are the
1846 columns on which the derived column depends. The
1847 hashref should also contain a 'BUILDER' key
1848 that is a reference to a sub that calculates
1853 my %derived_columns = (
1854 'items.cn_sort' => {
1855 'itemcallnumber' => 1,
1856 'items.cn_source' => 1,
1857 'BUILDER' => \&_calc_items_cn_sort,
1861 =head2 _set_derived_columns_for_add
1863 _set_derived_column_for_add($item);
1865 Given an item hash representing a new item to be added,
1866 calculate any derived columns. Currently the only
1867 such column is C<items.cn_sort>.
1871 sub _set_derived_columns_for_add {
1874 foreach my $column (keys %derived_columns) {
1875 my $builder = $derived_columns{$column}->{'BUILDER'};
1876 my $source_values = {};
1877 foreach my $source_column (keys %{ $derived_columns{$column} }) {
1878 next if $source_column eq 'BUILDER';
1879 $source_values->{$source_column} = $item->{$source_column};
1881 $builder->($item, $source_values);
1885 =head2 _set_derived_columns_for_mod
1887 _set_derived_column_for_mod($item);
1889 Given an item hash representing a new item to be modified.
1890 calculate any derived columns. Currently the only
1891 such column is C<items.cn_sort>.
1893 This routine differs from C<_set_derived_columns_for_add>
1894 in that it needs to handle partial item records. In other
1895 words, the caller of C<ModItem> may have supplied only one
1896 or two columns to be changed, so this function needs to
1897 determine whether any of the columns to be changed affect
1898 any of the derived columns. Also, if a derived column
1899 depends on more than one column, but the caller is not
1900 changing all of then, this routine retrieves the unchanged
1901 values from the database in order to ensure a correct
1906 sub _set_derived_columns_for_mod {
1909 foreach my $column (keys %derived_columns) {
1910 my $builder = $derived_columns{$column}->{'BUILDER'};
1911 my $source_values = {};
1912 my %missing_sources = ();
1913 my $must_recalc = 0;
1914 foreach my $source_column (keys %{ $derived_columns{$column} }) {
1915 next if $source_column eq 'BUILDER';
1916 if (exists $item->{$source_column}) {
1918 $source_values->{$source_column} = $item->{$source_column};
1920 $missing_sources{$source_column} = 1;
1924 foreach my $source_column (keys %missing_sources) {
1925 $source_values->{$source_column} = _get_single_item_column($source_column, $item->{'itemnumber'});
1927 $builder->($item, $source_values);
1932 =head2 _do_column_fixes_for_mod
1934 _do_column_fixes_for_mod($item);
1936 Given an item hashref containing one or more
1937 columns to modify, fix up certain values.
1938 Specifically, set to 0 any passed value
1939 of C<notforloan>, C<damaged>, C<itemlost>, or
1940 C<withdrawn> that is either undefined or
1941 contains the empty string.
1945 sub _do_column_fixes_for_mod {
1948 if (exists $item->{'notforloan'} and
1949 (not defined $item->{'notforloan'} or $item->{'notforloan'} eq '')) {
1950 $item->{'notforloan'} = 0;
1952 if (exists $item->{'damaged'} and
1953 (not defined $item->{'damaged'} or $item->{'damaged'} eq '')) {
1954 $item->{'damaged'} = 0;
1956 if (exists $item->{'itemlost'} and
1957 (not defined $item->{'itemlost'} or $item->{'itemlost'} eq '')) {
1958 $item->{'itemlost'} = 0;
1960 if (exists $item->{'withdrawn'} and
1961 (not defined $item->{'withdrawn'} or $item->{'withdrawn'} eq '')) {
1962 $item->{'withdrawn'} = 0;
1964 if (exists $item->{location}
1965 and $item->{location} ne 'CART'
1966 and $item->{location} ne 'PROC'
1967 and not $item->{permanent_location}
1969 $item->{'permanent_location'} = $item->{'location'};
1971 if (exists $item->{'timestamp'}) {
1972 delete $item->{'timestamp'};
1976 =head2 _get_single_item_column
1978 _get_single_item_column($column, $itemnumber);
1980 Retrieves the value of a single column from an C<items>
1981 row specified by C<$itemnumber>.
1985 sub _get_single_item_column {
1987 my $itemnumber = shift;
1989 my $dbh = C4::Context->dbh;
1990 my $sth = $dbh->prepare("SELECT $column FROM items WHERE itemnumber = ?");
1991 $sth->execute($itemnumber);
1992 my ($value) = $sth->fetchrow();
1996 =head2 _calc_items_cn_sort
1998 _calc_items_cn_sort($item, $source_values);
2000 Helper routine to calculate C<items.cn_sort>.
2004 sub _calc_items_cn_sort {
2006 my $source_values = shift;
2008 $item->{'items.cn_sort'} = GetClassSort($source_values->{'items.cn_source'}, $source_values->{'itemcallnumber'}, "");
2011 =head2 _set_defaults_for_add
2013 _set_defaults_for_add($item_hash);
2015 Given an item hash representing an item to be added, set
2016 correct default values for columns whose default value
2017 is not handled by the DBMS. This includes the following
2024 C<items.dateaccessioned>
2046 sub _set_defaults_for_add {
2048 $item->{dateaccessioned} ||= output_pref({ dt => dt_from_string, dateformat => 'iso', dateonly => 1 });
2049 $item->{$_} ||= 0 for (qw( notforloan damaged itemlost withdrawn));
2052 =head2 _koha_new_item
2054 my ($itemnumber,$error) = _koha_new_item( $item, $barcode );
2056 Perform the actual insert into the C<items> table.
2060 sub _koha_new_item {
2061 my ( $item, $barcode ) = @_;
2062 my $dbh=C4::Context->dbh;
2064 $item->{permanent_location} //= $item->{location};
2066 "INSERT INTO items SET
2068 biblioitemnumber = ?,
2070 dateaccessioned = ?,
2074 replacementprice = ?,
2075 replacementpricedate = ?,
2076 datelastborrowed = ?,
2084 coded_location_qualifier = ?,
2087 itemnotes_nonpublic = ?,
2091 permanent_location = ?,
2103 more_subfields_xml = ?,
2108 my $sth = $dbh->prepare($query);
2109 my $today = output_pref({ dt => dt_from_string, dateformat => 'iso', dateonly => 1 });
2111 $item->{'biblionumber'},
2112 $item->{'biblioitemnumber'},
2114 $item->{'dateaccessioned'},
2115 $item->{'booksellerid'},
2116 $item->{'homebranch'},
2118 $item->{'replacementprice'},
2119 $item->{'replacementpricedate'} || $today,
2120 $item->{datelastborrowed},
2121 $item->{datelastseen} || $today,
2123 $item->{'notforloan'},
2125 $item->{'itemlost'},
2126 $item->{'withdrawn'},
2127 $item->{'itemcallnumber'},
2128 $item->{'coded_location_qualifier'},
2129 $item->{'restricted'},
2130 $item->{'itemnotes'},
2131 $item->{'itemnotes_nonpublic'},
2132 $item->{'holdingbranch'},
2134 $item->{'location'},
2135 $item->{'permanent_location'},
2138 $item->{'renewals'},
2139 $item->{'reserves'},
2140 $item->{'items.cn_source'},
2141 $item->{'items.cn_sort'},
2144 $item->{'materials'},
2146 $item->{'enumchron'},
2147 $item->{'more_subfields_xml'},
2148 $item->{'copynumber'},
2149 $item->{'stocknumber'},
2150 $item->{'new_status'},
2154 if ( defined $sth->errstr ) {
2155 $error.="ERROR in _koha_new_item $query".$sth->errstr;
2158 $itemnumber = $dbh->{'mysql_insertid'};
2161 return ( $itemnumber, $error );
2164 =head2 MoveItemFromBiblio
2166 MoveItemFromBiblio($itenumber, $frombiblio, $tobiblio);
2168 Moves an item from a biblio to another
2170 Returns undef if the move failed or the biblionumber of the destination record otherwise
2174 sub MoveItemFromBiblio {
2175 my ($itemnumber, $frombiblio, $tobiblio) = @_;
2176 my $dbh = C4::Context->dbh;
2177 my ( $tobiblioitem ) = $dbh->selectrow_array(q|
2178 SELECT biblioitemnumber
2180 WHERE biblionumber = ?
2181 |, undef, $tobiblio );
2182 my $return = $dbh->do(q|
2184 SET biblioitemnumber = ?,
2186 WHERE itemnumber = ?
2187 AND biblionumber = ?
2188 |, undef, $tobiblioitem, $tobiblio, $itemnumber, $frombiblio );
2190 ModZebra( $tobiblio, "specialUpdate", "biblioserver" );
2191 ModZebra( $frombiblio, "specialUpdate", "biblioserver" );
2192 # Checking if the item we want to move is in an order
2193 require C4::Acquisition;
2194 my $order = C4::Acquisition::GetOrderFromItemnumber($itemnumber);
2196 # Replacing the biblionumber within the order if necessary
2197 $order->{'biblionumber'} = $tobiblio;
2198 C4::Acquisition::ModOrder($order);
2201 # Update reserves, hold_fill_targets, tmp_holdsqueue and linktracker tables
2202 for my $table_name ( qw( reserves hold_fill_targets tmp_holdsqueue linktracker ) ) {
2205 SET biblionumber = ?
2206 WHERE itemnumber = ?
2207 |, undef, $tobiblio, $itemnumber );
2216 DelItemCheck($dbh, $biblionumber, $itemnumber);
2218 Exported function (core API) for deleting an item record in Koha if there no current issue.
2223 my ( $dbh, $biblionumber, $itemnumber ) = @_;
2225 $dbh ||= C4::Context->dbh;
2229 my $countanalytics=GetAnalyticsCount($itemnumber);
2232 # check that there is no issue on this item before deletion.
2233 my $sth = $dbh->prepare(q{
2234 SELECT COUNT(*) FROM issues
2235 WHERE itemnumber = ?
2237 $sth->execute($itemnumber);
2238 my ($onloan) = $sth->fetchrow;
2240 my $item = GetItem($itemnumber);
2243 $error = "book_on_loan"
2245 elsif ( defined C4::Context->userenv
2246 and !C4::Context->IsSuperLibrarian()
2247 and C4::Context->preference("IndependentBranches")
2248 and ( C4::Context->userenv->{branch} ne $item->{'homebranch'} ) )
2250 $error = "not_same_branch";
2253 # check it doesn't have a waiting reserve
2254 $sth = $dbh->prepare(q{
2255 SELECT COUNT(*) FROM reserves
2256 WHERE (found = 'W' OR found = 'T')
2259 $sth->execute($itemnumber);
2260 my ($reserve) = $sth->fetchrow;
2262 $error = "book_reserved";
2263 } elsif ($countanalytics > 0){
2264 $error = "linked_analytics";
2268 biblionumber => $biblionumber,
2269 itemnumber => $itemnumber
2278 =head2 _koha_modify_item
2280 my ($itemnumber,$error) =_koha_modify_item( $item );
2282 Perform the actual update of the C<items> row. Note that this
2283 routine accepts a hashref specifying the columns to update.
2287 sub _koha_modify_item {
2289 my $dbh=C4::Context->dbh;
2292 my $query = "UPDATE items SET ";
2294 for my $key ( keys %$item ) {
2295 next if ( $key eq 'itemnumber' );
2297 push @bind, $item->{$key};
2300 $query .= " WHERE itemnumber=?";
2301 push @bind, $item->{'itemnumber'};
2302 my $sth = $dbh->prepare($query);
2303 $sth->execute(@bind);
2305 $error.="ERROR in _koha_modify_item $query: ".$sth->errstr;
2308 return ($item->{'itemnumber'},$error);
2311 =head2 _koha_delete_item
2313 _koha_delete_item( $itemnum );
2315 Internal function to delete an item record from the koha tables
2319 sub _koha_delete_item {
2320 my ( $itemnum ) = @_;
2322 my $dbh = C4::Context->dbh;
2323 # save the deleted item to deleteditems table
2324 my $sth = $dbh->prepare("SELECT * FROM items WHERE itemnumber=?");
2325 $sth->execute($itemnum);
2326 my $data = $sth->fetchrow_hashref();
2328 # There is no item to delete
2329 return 0 unless $data;
2331 my $query = "INSERT INTO deleteditems SET ";
2333 foreach my $key ( keys %$data ) {
2334 next if ( $key eq 'timestamp' ); # timestamp will be set by db
2335 $query .= "$key = ?,";
2336 push( @bind, $data->{$key} );
2339 $sth = $dbh->prepare($query);
2340 $sth->execute(@bind);
2342 # delete from items table
2343 $sth = $dbh->prepare("DELETE FROM items WHERE itemnumber=?");
2344 my $deleted = $sth->execute($itemnum);
2345 return ( $deleted == 1 ) ? 1 : 0;
2348 =head2 _marc_from_item_hash
2350 my $item_marc = _marc_from_item_hash($item, $frameworkcode[, $unlinked_item_subfields]);
2352 Given an item hash representing a complete item record,
2353 create a C<MARC::Record> object containing an embedded
2354 tag representing that item.
2356 The third, optional parameter C<$unlinked_item_subfields> is
2357 an arrayref of subfields (not mapped to C<items> fields per the
2358 framework) to be added to the MARC representation
2363 sub _marc_from_item_hash {
2365 my $frameworkcode = shift;
2366 my $unlinked_item_subfields;
2368 $unlinked_item_subfields = shift;
2371 # Tack on 'items.' prefix to column names so lookup from MARC frameworks will work
2372 # Also, don't emit a subfield if the underlying field is blank.
2373 my $mungeditem = { map { (defined($item->{$_}) and $item->{$_} ne '') ?
2374 (/^items\./ ? ($_ => $item->{$_}) : ("items.$_" => $item->{$_}))
2375 : () } keys %{ $item } };
2377 my $item_marc = MARC::Record->new();
2378 foreach my $item_field ( keys %{$mungeditem} ) {
2379 my ( $tag, $subfield ) = GetMarcFromKohaField( $item_field, $frameworkcode );
2380 next unless defined $tag and defined $subfield; # skip if not mapped to MARC field
2381 my @values = split(/\s?\|\s?/, $mungeditem->{$item_field}, -1);
2382 foreach my $value (@values){
2383 if ( my $field = $item_marc->field($tag) ) {
2384 $field->add_subfields( $subfield => $value );
2386 my $add_subfields = [];
2387 if (defined $unlinked_item_subfields and ref($unlinked_item_subfields) eq 'ARRAY' and $#$unlinked_item_subfields > -1) {
2388 $add_subfields = $unlinked_item_subfields;
2390 $item_marc->add_fields( $tag, " ", " ", $subfield => $value, @$add_subfields );
2398 =head2 _repack_item_errors
2400 Add an error message hash generated by C<CheckItemPreSave>
2401 to a list of errors.
2405 sub _repack_item_errors {
2406 my $item_sequence_num = shift;
2407 my $item_ref = shift;
2408 my $error_ref = shift;
2410 my @repacked_errors = ();
2412 foreach my $error_code (sort keys %{ $error_ref }) {
2413 my $repacked_error = {};
2414 $repacked_error->{'item_sequence'} = $item_sequence_num;
2415 $repacked_error->{'item_barcode'} = exists($item_ref->{'barcode'}) ? $item_ref->{'barcode'} : '';
2416 $repacked_error->{'error_code'} = $error_code;
2417 $repacked_error->{'error_information'} = $error_ref->{$error_code};
2418 push @repacked_errors, $repacked_error;
2421 return @repacked_errors;
2424 =head2 _get_unlinked_item_subfields
2426 my $unlinked_item_subfields = _get_unlinked_item_subfields($original_item_marc, $frameworkcode);
2430 sub _get_unlinked_item_subfields {
2431 my $original_item_marc = shift;
2432 my $frameworkcode = shift;
2434 my $marcstructure = GetMarcStructure(1, $frameworkcode);
2436 # assume that this record has only one field, and that that
2437 # field contains only the item information
2439 my @fields = $original_item_marc->fields();
2440 if ($#fields > -1) {
2441 my $field = $fields[0];
2442 my $tag = $field->tag();
2443 foreach my $subfield ($field->subfields()) {
2444 if (defined $subfield->[1] and
2445 $subfield->[1] ne '' and
2446 !$marcstructure->{$tag}->{$subfield->[0]}->{'kohafield'}) {
2447 push @$subfields, $subfield->[0] => $subfield->[1];
2454 =head2 _get_unlinked_subfields_xml
2456 my $unlinked_subfields_xml = _get_unlinked_subfields_xml($unlinked_item_subfields);
2460 sub _get_unlinked_subfields_xml {
2461 my $unlinked_item_subfields = shift;
2464 if (defined $unlinked_item_subfields and ref($unlinked_item_subfields) eq 'ARRAY' and $#$unlinked_item_subfields > -1) {
2465 my $marc = MARC::Record->new();
2466 # use of tag 999 is arbitrary, and doesn't need to match the item tag
2467 # used in the framework
2468 $marc->append_fields(MARC::Field->new('999', ' ', ' ', @$unlinked_item_subfields));
2469 $marc->encoding("UTF-8");
2470 $xml = $marc->as_xml("USMARC");
2476 =head2 _parse_unlinked_item_subfields_from_xml
2478 my $unlinked_item_subfields = _parse_unlinked_item_subfields_from_xml($whole_item->{'more_subfields_xml'}):
2482 sub _parse_unlinked_item_subfields_from_xml {
2484 require C4::Charset;
2485 return unless defined $xml and $xml ne "";
2486 my $marc = MARC::Record->new_from_xml(C4::Charset::StripNonXmlChars($xml),'UTF-8');
2487 my $unlinked_subfields = [];
2488 my @fields = $marc->fields();
2489 if ($#fields > -1) {
2490 foreach my $subfield ($fields[0]->subfields()) {
2491 push @$unlinked_subfields, $subfield->[0] => $subfield->[1];
2494 return $unlinked_subfields;
2497 =head2 GetAnalyticsCount
2499 $count= &GetAnalyticsCount($itemnumber)
2501 counts Usage of itemnumber in Analytical bibliorecords.
2505 sub GetAnalyticsCount {
2506 my ($itemnumber) = @_;
2508 ### ZOOM search here
2510 $query= "hi=".$itemnumber;
2511 my $searcher = Koha::SearchEngine::Search->new({index => $Koha::SearchEngine::BIBLIOS_INDEX});
2512 my ($err,$res,$result) = $searcher->simple_search_compat($query,0,10);
2518 $holds = &GetItemHolds($biblionumber, $itemnumber);
2520 This function return the count of holds with $biblionumber and $itemnumber
2525 my ($biblionumber, $itemnumber) = @_;
2527 my $dbh = C4::Context->dbh;
2528 my $query = "SELECT count(*)
2530 WHERE biblionumber=? AND itemnumber=?";
2531 my $sth = $dbh->prepare($query);
2532 $sth->execute($biblionumber, $itemnumber);
2533 $holds = $sth->fetchrow;
2537 =head2 SearchItemsByField
2539 my $items = SearchItemsByField($field, $value);
2541 SearchItemsByField will search for items on a specific given field.
2542 For instance you can search all items with a specific stocknumber like this:
2544 my $items = SearchItemsByField('stocknumber', $stocknumber);
2548 sub SearchItemsByField {
2549 my ($field, $value) = @_;
2556 my ($results) = SearchItems($filters);
2560 sub _SearchItems_build_where_fragment {
2563 my $dbh = C4::Context->dbh;
2566 if (exists($filter->{conjunction})) {
2567 my (@where_strs, @where_args);
2568 foreach my $f (@{ $filter->{filters} }) {
2569 my $fragment = _SearchItems_build_where_fragment($f);
2571 push @where_strs, $fragment->{str};
2572 push @where_args, @{ $fragment->{args} };
2577 $where_str = '(' . join (' ' . $filter->{conjunction} . ' ', @where_strs) . ')';
2580 args => \@where_args,
2584 my @columns = Koha::Database->new()->schema()->resultset('Item')->result_source->columns;
2585 push @columns, Koha::Database->new()->schema()->resultset('Biblio')->result_source->columns;
2586 push @columns, Koha::Database->new()->schema()->resultset('Biblioitem')->result_source->columns;
2587 my @operators = qw(= != > < >= <= like);
2588 my $field = $filter->{field};
2589 if ( (0 < grep /^$field$/, @columns) or (substr($field, 0, 5) eq 'marc:') ) {
2590 my $op = $filter->{operator};
2591 my $query = $filter->{query};
2593 if (!$op or (0 == grep /^$op$/, @operators)) {
2594 $op = '='; # default operator
2598 if ($field =~ /^marc:(\d{3})(?:\$(\w))?$/) {
2600 my $marcsubfield = $2;
2601 my ($kohafield) = $dbh->selectrow_array(q|
2602 SELECT kohafield FROM marc_subfield_structure
2603 WHERE tagfield=? AND tagsubfield=? AND frameworkcode=''
2604 |, undef, $marcfield, $marcsubfield);
2607 $column = $kohafield;
2609 # MARC field is not linked to a DB field so we need to use
2610 # ExtractValue on biblioitems.marcxml or
2611 # items.more_subfields_xml, depending on the MARC field.
2614 my ($itemfield) = GetMarcFromKohaField('items.itemnumber');
2615 if ($marcfield eq $itemfield) {
2616 $sqlfield = 'more_subfields_xml';
2617 $xpath = '//record/datafield/subfield[@code="' . $marcsubfield . '"]';
2619 $sqlfield = 'marcxml';
2620 if ($marcfield < 10) {
2621 $xpath = "//record/controlfield[\@tag=\"$marcfield\"]";
2623 $xpath = "//record/datafield[\@tag=\"$marcfield\"]/subfield[\@code=\"$marcsubfield\"]";
2626 $column = "ExtractValue($sqlfield, '$xpath')";
2632 if (ref $query eq 'ARRAY') {
2635 } elsif ($op eq '!=') {
2639 str => "$column $op (" . join (',', ('?') x @$query) . ")",
2644 str => "$column $op ?",
2651 return $where_fragment;
2656 my ($items, $total) = SearchItems($filter, $params);
2658 Perform a search among items
2660 $filter is a reference to a hash which can be a filter, or a combination of filters.
2662 A filter has the following keys:
2666 =item * field: the name of a SQL column in table items
2668 =item * query: the value to search in this column
2670 =item * operator: comparison operator. Can be one of = != > < >= <= like
2674 A combination of filters hash the following keys:
2678 =item * conjunction: 'AND' or 'OR'
2680 =item * filters: array ref of filters
2684 $params is a reference to a hash that can contain the following parameters:
2688 =item * rows: Number of items to return. 0 returns everything (default: 0)
2690 =item * page: Page to return (return items from (page-1)*rows to (page*rows)-1)
2693 =item * sortby: A SQL column name in items table to sort on
2695 =item * sortorder: 'ASC' or 'DESC'
2702 my ($filter, $params) = @_;
2706 return unless ref $filter eq 'HASH';
2707 return unless ref $params eq 'HASH';
2709 # Default parameters
2710 $params->{rows} ||= 0;
2711 $params->{page} ||= 1;
2712 $params->{sortby} ||= 'itemnumber';
2713 $params->{sortorder} ||= 'ASC';
2715 my ($where_str, @where_args);
2716 my $where_fragment = _SearchItems_build_where_fragment($filter);
2717 if ($where_fragment) {
2718 $where_str = $where_fragment->{str};
2719 @where_args = @{ $where_fragment->{args} };
2722 my $dbh = C4::Context->dbh;
2724 SELECT SQL_CALC_FOUND_ROWS items.*
2726 LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber
2727 LEFT JOIN biblioitems ON biblioitems.biblioitemnumber = items.biblioitemnumber
2729 if (defined $where_str and $where_str ne '') {
2730 $query .= qq{ WHERE $where_str };
2733 my @columns = Koha::Database->new()->schema()->resultset('Item')->result_source->columns;
2734 push @columns, Koha::Database->new()->schema()->resultset('Biblio')->result_source->columns;
2735 push @columns, Koha::Database->new()->schema()->resultset('Biblioitem')->result_source->columns;
2736 my $sortby = (0 < grep {$params->{sortby} eq $_} @columns)
2737 ? $params->{sortby} : 'itemnumber';
2738 my $sortorder = (uc($params->{sortorder}) eq 'ASC') ? 'ASC' : 'DESC';
2739 $query .= qq{ ORDER BY $sortby $sortorder };
2741 my $rows = $params->{rows};
2744 my $offset = $rows * ($params->{page}-1);
2745 $query .= qq { LIMIT ?, ? };
2746 push @limit_args, $offset, $rows;
2749 my $sth = $dbh->prepare($query);
2750 my $rv = $sth->execute(@where_args, @limit_args);
2752 return unless ($rv);
2753 my ($total_rows) = $dbh->selectrow_array(q{ SELECT FOUND_ROWS() });
2755 return ($sth->fetchall_arrayref({}), $total_rows);
2759 =head1 OTHER FUNCTIONS
2763 ($indicators, $value) = _find_value($tag, $subfield, $record,$encoding);
2765 Find the given $subfield in the given $tag in the given
2766 MARC::Record $record. If the subfield is found, returns
2767 the (indicators, value) pair; otherwise, (undef, undef) is
2771 Such a function is used in addbiblio AND additem and serial-edit and maybe could be used in Authorities.
2772 I suggest we export it from this module.
2777 my ( $tagfield, $insubfield, $record, $encoding ) = @_;
2780 if ( $tagfield < 10 ) {
2781 if ( $record->field($tagfield) ) {
2782 push @result, $record->field($tagfield)->data();
2787 foreach my $field ( $record->field($tagfield) ) {
2788 my @subfields = $field->subfields();
2789 foreach my $subfield (@subfields) {
2790 if ( @$subfield[0] eq $insubfield ) {
2791 push @result, @$subfield[1];
2792 $indicator = $field->indicator(1) . $field->indicator(2);
2797 return ( $indicator, @result );
2801 =head2 PrepareItemrecordDisplay
2803 PrepareItemrecordDisplay($itemrecord,$bibnum,$itemumber,$frameworkcode);
2805 Returns a hash with all the fields for Display a given item data in a template
2807 The $frameworkcode returns the item for the given frameworkcode, ONLY if bibnum is not provided
2811 sub PrepareItemrecordDisplay {
2813 my ( $bibnum, $itemnum, $defaultvalues, $frameworkcode ) = @_;
2815 my $dbh = C4::Context->dbh;
2816 $frameworkcode = &GetFrameworkCode($bibnum) if $bibnum;
2817 my ( $itemtagfield, $itemtagsubfield ) = &GetMarcFromKohaField( "items.itemnumber", $frameworkcode );
2818 my $tagslib = &GetMarcStructure( 1, $frameworkcode );
2820 # return nothing if we don't have found an existing framework.
2821 return q{} unless $tagslib;
2824 $itemrecord = C4::Items::GetMarcItem( $bibnum, $itemnum );
2828 my $branch_limit = C4::Context->userenv ? C4::Context->userenv->{"branch"} : "";
2830 SELECT authorised_value,lib FROM authorised_values
2833 LEFT JOIN authorised_values_branches ON ( id = av_id )
2838 $query .= qq{ AND ( branchcode = ? OR branchcode IS NULL )} if $branch_limit;
2839 $query .= qq{ ORDER BY lib};
2840 my $authorised_values_sth = $dbh->prepare( $query );
2841 foreach my $tag ( sort keys %{$tagslib} ) {
2842 my $previous_tag = '';
2845 # loop through each subfield
2847 foreach my $subfield ( sort keys %{ $tagslib->{$tag} } ) {
2848 next if IsMarcStructureInternal($tagslib->{$tag}{$subfield});
2849 next if ( $tagslib->{$tag}->{$subfield}->{'tab'} ne "10" );
2851 $subfield_data{tag} = $tag;
2852 $subfield_data{subfield} = $subfield;
2853 $subfield_data{countsubfield} = $cntsubf++;
2854 $subfield_data{kohafield} = $tagslib->{$tag}->{$subfield}->{'kohafield'};
2855 $subfield_data{id} = "tag_".$tag."_subfield_".$subfield."_".int(rand(1000000));
2857 # $subfield_data{marc_lib}=$tagslib->{$tag}->{$subfield}->{lib};
2858 $subfield_data{marc_lib} = $tagslib->{$tag}->{$subfield}->{lib};
2859 $subfield_data{mandatory} = $tagslib->{$tag}->{$subfield}->{mandatory};
2860 $subfield_data{repeatable} = $tagslib->{$tag}->{$subfield}->{repeatable};
2861 $subfield_data{hidden} = "display:none"
2862 if ( ( $tagslib->{$tag}->{$subfield}->{hidden} > 4 )
2863 || ( $tagslib->{$tag}->{$subfield}->{hidden} < -4 ) );
2864 my ( $x, $defaultvalue );
2866 ( $x, $defaultvalue ) = _find_value( $tag, $subfield, $itemrecord );
2868 $defaultvalue = $tagslib->{$tag}->{$subfield}->{defaultvalue} unless $defaultvalue;
2869 if ( !defined $defaultvalue ) {
2870 $defaultvalue = q||;
2872 $defaultvalue =~ s/"/"/g;
2875 # search for itemcallnumber if applicable
2876 if ( $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.itemcallnumber'
2877 && C4::Context->preference('itemcallnumber') ) {
2878 my $CNtag = substr( C4::Context->preference('itemcallnumber'), 0, 3 );
2879 my $CNsubfield = substr( C4::Context->preference('itemcallnumber'), 3, 1 );
2880 if ( $itemrecord and my $field = $itemrecord->field($CNtag) ) {
2881 $defaultvalue = $field->subfield($CNsubfield);
2884 if ( $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.itemcallnumber'
2886 && $defaultvalues->{'callnumber'} ) {
2887 if( $itemrecord and $defaultvalues and not $itemrecord->field($subfield) ){
2888 # if the item record exists, only use default value if the item has no callnumber
2889 $defaultvalue = $defaultvalues->{callnumber};
2890 } elsif ( !$itemrecord and $defaultvalues ) {
2891 # if the item record *doesn't* exists, always use the default value
2892 $defaultvalue = $defaultvalues->{callnumber};
2895 if ( ( $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.holdingbranch' || $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.homebranch' )
2897 && $defaultvalues->{'branchcode'} ) {
2898 if ( $itemrecord and $defaultvalues and not $itemrecord->field($subfield) ) {
2899 $defaultvalue = $defaultvalues->{branchcode};
2902 if ( ( $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.location' )
2904 && $defaultvalues->{'location'} ) {
2906 if ( $itemrecord and $defaultvalues and not $itemrecord->field($subfield) ) {
2907 # if the item record exists, only use default value if the item has no locationr
2908 $defaultvalue = $defaultvalues->{location};
2909 } elsif ( !$itemrecord and $defaultvalues ) {
2910 # if the item record *doesn't* exists, always use the default value
2911 $defaultvalue = $defaultvalues->{location};
2914 if ( $tagslib->{$tag}->{$subfield}->{authorised_value} ) {
2915 my @authorised_values;
2918 # builds list, depending on authorised value...
2920 if ( $tagslib->{$tag}->{$subfield}->{'authorised_value'} eq "branches" ) {
2921 if ( ( C4::Context->preference("IndependentBranches") )
2922 && !C4::Context->IsSuperLibrarian() ) {
2923 my $sth = $dbh->prepare( "SELECT branchcode,branchname FROM branches WHERE branchcode = ? ORDER BY branchname" );
2924 $sth->execute( C4::Context->userenv->{branch} );
2925 push @authorised_values, ""
2926 unless ( $tagslib->{$tag}->{$subfield}->{mandatory} );
2927 while ( my ( $branchcode, $branchname ) = $sth->fetchrow_array ) {
2928 push @authorised_values, $branchcode;
2929 $authorised_lib{$branchcode} = $branchname;
2932 my $sth = $dbh->prepare( "SELECT branchcode,branchname FROM branches ORDER BY branchname" );
2934 push @authorised_values, ""
2935 unless ( $tagslib->{$tag}->{$subfield}->{mandatory} );
2936 while ( my ( $branchcode, $branchname ) = $sth->fetchrow_array ) {
2937 push @authorised_values, $branchcode;
2938 $authorised_lib{$branchcode} = $branchname;
2942 $defaultvalue = C4::Context->userenv ? C4::Context->userenv->{branch} : undef;
2943 if ( $defaultvalues and $defaultvalues->{branchcode} ) {
2944 $defaultvalue = $defaultvalues->{branchcode};
2948 } elsif ( $tagslib->{$tag}->{$subfield}->{authorised_value} eq "itemtypes" ) {
2949 my $itemtypes = GetItemTypes( style => 'array' );
2950 push @authorised_values, ""
2951 unless ( $tagslib->{$tag}->{$subfield}->{mandatory} );
2952 for my $itemtype ( @$itemtypes ) {
2953 push @authorised_values, $itemtype->{itemtype};
2954 $authorised_lib{$itemtype->{itemtype}} = $itemtype->{translated_description};
2957 } elsif ( $tagslib->{$tag}->{$subfield}->{authorised_value} eq "cn_source" ) {
2958 push @authorised_values, "" unless ( $tagslib->{$tag}->{$subfield}->{mandatory} );
2960 my $class_sources = GetClassSources();
2961 my $default_source = C4::Context->preference("DefaultClassificationSource");
2963 foreach my $class_source (sort keys %$class_sources) {
2964 next unless $class_sources->{$class_source}->{'used'} or
2965 ($class_source eq $default_source);
2966 push @authorised_values, $class_source;
2967 $authorised_lib{$class_source} = $class_sources->{$class_source}->{'description'};
2970 $defaultvalue = $default_source;
2972 #---- "true" authorised value
2974 $authorised_values_sth->execute(
2975 $tagslib->{$tag}->{$subfield}->{authorised_value},
2976 $branch_limit ? $branch_limit : ()
2978 push @authorised_values, ""
2979 unless ( $tagslib->{$tag}->{$subfield}->{mandatory} );
2980 while ( my ( $value, $lib ) = $authorised_values_sth->fetchrow_array ) {
2981 push @authorised_values, $value;
2982 $authorised_lib{$value} = $lib;
2985 $subfield_data{marc_value} = {
2987 values => \@authorised_values,
2988 default => "$defaultvalue",
2989 labels => \%authorised_lib,
2991 } elsif ( $tagslib->{$tag}->{$subfield}->{value_builder} ) {
2993 require Koha::FrameworkPlugin;
2994 my $plugin = Koha::FrameworkPlugin->new({
2995 name => $tagslib->{$tag}->{$subfield}->{value_builder},
2998 my $pars = { dbh => $dbh, record => undef, tagslib =>$tagslib, id => $subfield_data{id}, tabloop => undef };
2999 $plugin->build( $pars );
3000 if( !$plugin->errstr ) {
3001 #TODO Move html to template; see report 12176/13397
3002 my $tab= $plugin->noclick? '-1': '';
3003 my $class= $plugin->noclick? ' disabled': '';
3004 my $title= $plugin->noclick? 'No popup': 'Tag editor';
3005 $subfield_data{marc_value} = qq[<input type="text" id="$subfield_data{id}" name="field_value" class="input_marceditor" size="50" maxlength="255" /><a href="#" id="buttonDot_$subfield_data{id}" tabindex="$tab" class="buttonDot $class" title="$title">...</a>\n].$plugin->javascript;
3007 warn $plugin->errstr;
3008 $subfield_data{marc_value} = qq(<input type="text" id="$subfield_data{id}" name="field_value" class="input_marceditor" size="50" maxlength="255" />); # supply default input form
3011 elsif ( $tag eq '' ) { # it's an hidden field
3012 $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" />);
3014 elsif ( $tagslib->{$tag}->{$subfield}->{'hidden'} ) { # FIXME: shouldn't input type be "hidden" ?
3015 $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" />);
3017 elsif ( length($defaultvalue) > 100
3018 or (C4::Context->preference("marcflavour") eq "UNIMARC" and
3019 300 <= $tag && $tag < 400 && $subfield eq 'a' )
3020 or (C4::Context->preference("marcflavour") eq "MARC21" and
3021 500 <= $tag && $tag < 600 )
3023 # oversize field (textarea)
3024 $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");
3026 $subfield_data{marc_value} = "<input type=\"text\" name=\"field_value\" value=\"$defaultvalue\" size=\"50\" maxlength=\"255\" />";
3028 push( @loop_data, \%subfield_data );
3033 if ( $itemrecord && $itemrecord->field($itemtagfield) ) {
3034 $itemnumber = $itemrecord->subfield( $itemtagfield, $itemtagsubfield );
3037 'itemtagfield' => $itemtagfield,
3038 'itemtagsubfield' => $itemtagsubfield,
3039 'itemnumber' => $itemnumber,
3040 'iteminformation' => \@loop_data
3046 my @columns = C4::Items::columns();
3048 Returns an array of items' table columns on success,
3049 and an empty array on failure.
3054 my $rs = Koha::Database->new->schema->resultset('Item');
3055 return $rs->result_source->columns;
3058 =head2 biblioitems_columns
3060 my @columns = C4::Items::biblioitems_columns();
3062 Returns an array of biblioitems' table columns on success,
3063 and an empty array on failure.
3067 sub biblioitems_columns {
3068 my $rs = Koha::Database->new->schema->resultset('Biblioitem');
3069 return $rs->result_source->columns;
3072 sub ToggleNewStatus {
3073 my ( $params ) = @_;
3074 my @rules = @{ $params->{rules} };
3075 my $report_only = $params->{report_only};
3077 my $dbh = C4::Context->dbh;
3079 my @item_columns = map { "items.$_" } C4::Items::columns;
3080 my @biblioitem_columns = map { "biblioitems.$_" } C4::Items::biblioitems_columns;
3082 for my $rule ( @rules ) {
3083 my $age = $rule->{age};
3084 my $conditions = $rule->{conditions};
3085 my $substitutions = $rule->{substitutions};
3089 SELECT items.biblionumber, items.itemnumber
3091 LEFT JOIN biblioitems ON biblioitems.biblionumber = items.biblionumber
3094 for my $condition ( @$conditions ) {
3096 grep {/^$condition->{field}$/} @item_columns
3097 or grep {/^$condition->{field}$/} @biblioitem_columns
3099 if ( $condition->{value} =~ /\|/ ) {
3100 my @values = split /\|/, $condition->{value};
3101 $query .= qq| AND $condition->{field} IN (|
3102 . join( ',', ('?') x scalar @values )
3104 push @params, @values;
3106 $query .= qq| AND $condition->{field} = ?|;
3107 push @params, $condition->{value};
3111 if ( defined $age ) {
3112 $query .= q| AND TO_DAYS(NOW()) - TO_DAYS(dateaccessioned) >= ? |;
3115 my $sth = $dbh->prepare($query);
3116 $sth->execute( @params );
3117 while ( my $values = $sth->fetchrow_hashref ) {
3118 my $biblionumber = $values->{biblionumber};
3119 my $itemnumber = $values->{itemnumber};
3120 my $item = C4::Items::GetItem( $itemnumber );
3121 for my $substitution ( @$substitutions ) {
3122 next unless $substitution->{field};
3123 C4::Items::ModItem( {$substitution->{field} => $substitution->{value}}, $biblionumber, $itemnumber )
3124 unless $report_only;
3125 push @{ $report->{$itemnumber} }, $substitution;