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>.
23 use vars qw(@ISA @EXPORT);
40 get_hostitemnumbers_of
46 PrepareItemrecordDisplay
60 use List::MoreUtils qw(any);
62 use DateTime::Format::MySQL;
63 use Data::Dumper; # used as part of logging item record changes, not just for
64 # debugging; so please don't remove this
66 use Koha::AuthorisedValues;
67 use Koha::DateUtils qw(dt_from_string);
70 use Koha::Biblioitems;
73 use Koha::SearchEngine;
74 use Koha::SearchEngine::Indexer;
75 use Koha::SearchEngine::Search;
80 C4::Items - item management functions
84 This module contains an API for manipulating item
85 records in Koha, and is used by cataloguing, circulation,
86 acquisitions, and serials management.
88 # FIXME This POD is not up-to-date
89 A Koha item record is stored in two places: the
90 items table and embedded in a MARC tag in the XML
91 version of the associated bib record in C<biblioitems.marcxml>.
92 This is done to allow the item information to be readily
93 indexed (e.g., by Zebra), but means that each item
94 modification transaction must keep the items table
95 and the MARC XML in sync at all times.
97 The items table will be considered authoritative. In other
98 words, if there is ever a discrepancy between the items
99 table and the MARC XML, the items table should be considered
102 =head1 HISTORICAL NOTE
104 Most of the functions in C<C4::Items> were originally in
105 the C<C4::Biblio> module.
107 =head1 CORE EXPORTED FUNCTIONS
109 The following functions are meant for use by users
116 CartToShelf($itemnumber);
118 Set the current shelving location of the item record
119 to its stored permanent shelving location. This is
120 primarily used to indicate when an item whose current
121 location is a special processing ('PROC') or shelving cart
122 ('CART') location is back in the stacks.
127 my ( $itemnumber ) = @_;
129 unless ( $itemnumber ) {
130 croak "FAILED CartToShelf() - no itemnumber supplied";
133 my $item = Koha::Items->find($itemnumber);
134 if ( $item->location eq 'CART' ) {
135 $item->location($item->permanent_location)->store;
139 =head2 AddItemFromMarc
141 my ($biblionumber, $biblioitemnumber, $itemnumber)
142 = AddItemFromMarc($source_item_marc, $biblionumber[, $params]);
144 Given a MARC::Record object containing an embedded item
145 record and a biblionumber, create a new item record.
147 The final optional parameter, C<$params>, expected to contain
148 'skip_record_index' key, which relayed down to Koha::Item/store,
149 there it prevents calling of index_records,
150 which takes most of the time in batch adds/deletes: index_records
151 to be called later in C<additem.pl> after the whole loop.
154 skip_record_index => 1|0
158 sub AddItemFromMarc {
159 my $source_item_marc = shift;
160 my $biblionumber = shift;
161 my $params = @_ ? shift : {};
163 my $dbh = C4::Context->dbh;
165 # parse item hash from MARC
166 my $frameworkcode = C4::Biblio::GetFrameworkCode($biblionumber);
167 my ( $itemtag, $itemsubfield ) = C4::Biblio::GetMarcFromKohaField( "items.itemnumber" );
169 my $localitemmarc = MARC::Record->new;
170 $localitemmarc->append_fields( $source_item_marc->field($itemtag) );
172 my $item_values = C4::Biblio::TransformMarcToKoha( $localitemmarc, $frameworkcode, 'items' );
173 my $unlinked_item_subfields = _get_unlinked_item_subfields( $localitemmarc, $frameworkcode );
174 $item_values->{more_subfields_xml} = _get_unlinked_subfields_xml($unlinked_item_subfields);
175 $item_values->{biblionumber} = $biblionumber;
176 $item_values->{cn_source} = delete $item_values->{'items.cn_source'}; # Because of C4::Biblio::_disambiguate
177 $item_values->{cn_sort} = delete $item_values->{'items.cn_sort'}; # Because of C4::Biblio::_disambiguate
178 my $item = Koha::Item->new( $item_values )->store({ skip_record_index => $params->{skip_record_index} });
179 return ( $item->biblionumber, $item->biblioitemnumber, $item->itemnumber );
182 =head2 AddItemBatchFromMarc
184 ($itemnumber_ref, $error_ref) = AddItemBatchFromMarc($record,
185 $biblionumber, $biblioitemnumber, $frameworkcode);
187 Efficiently create item records from a MARC biblio record with
188 embedded item fields. This routine is suitable for batch jobs.
190 This API assumes that the bib record has already been
191 saved to the C<biblio> and C<biblioitems> tables. It does
192 not expect that C<biblio_metadata.metadata> is populated, but it
193 will do so via a call to ModBibiloMarc.
195 The goal of this API is to have a similar effect to using AddBiblio
196 and AddItems in succession, but without inefficient repeated
197 parsing of the MARC XML bib record.
199 This function returns an arrayref of new itemsnumbers and an arrayref of item
200 errors encountered during the processing. Each entry in the errors
201 list is a hashref containing the following keys:
207 Sequence number of original item tag in the MARC record.
211 Item barcode, provide to assist in the construction of
212 useful error messages.
216 Code representing the error condition. Can be 'duplicate_barcode',
217 'invalid_homebranch', or 'invalid_holdingbranch'.
219 =item error_information
221 Additional information appropriate to the error condition.
227 sub AddItemBatchFromMarc {
228 my ($record, $biblionumber, $biblioitemnumber, $frameworkcode) = @_;
229 my @itemnumbers = ();
231 my $dbh = C4::Context->dbh;
233 # We modify the record, so lets work on a clone so we don't change the
235 $record = $record->clone();
236 # loop through the item tags and start creating items
237 my @bad_item_fields = ();
238 my ($itemtag, $itemsubfield) = C4::Biblio::GetMarcFromKohaField( "items.itemnumber" );
239 my $item_sequence_num = 0;
240 ITEMFIELD: foreach my $item_field ($record->field($itemtag)) {
241 $item_sequence_num++;
242 # we take the item field and stick it into a new
243 # MARC record -- this is required so far because (FIXME)
244 # TransformMarcToKoha requires a MARC::Record, not a MARC::Field
245 # and there is no TransformMarcFieldToKoha
246 my $temp_item_marc = MARC::Record->new();
247 $temp_item_marc->append_fields($item_field);
249 # add biblionumber and biblioitemnumber
250 my $item = TransformMarcToKoha( $temp_item_marc, $frameworkcode, 'items' );
251 my $unlinked_item_subfields = _get_unlinked_item_subfields($temp_item_marc, $frameworkcode);
252 $item->{'more_subfields_xml'} = _get_unlinked_subfields_xml($unlinked_item_subfields);
253 $item->{'biblionumber'} = $biblionumber;
254 $item->{'biblioitemnumber'} = $biblioitemnumber;
255 $item->{cn_source} = delete $item->{'items.cn_source'}; # Because of C4::Biblio::_disambiguate
256 $item->{cn_sort} = delete $item->{'items.cn_sort'}; # Because of C4::Biblio::_disambiguate
258 # check for duplicate barcode
259 my %item_errors = CheckItemPreSave($item);
261 push @errors, _repack_item_errors($item_sequence_num, $item, \%item_errors);
262 push @bad_item_fields, $item_field;
266 my $item_object = Koha::Item->new($item)->store;
267 push @itemnumbers, $item_object->itemnumber; # FIXME not checking error
269 logaction("CATALOGUING", "ADD", $item_object->itemnumber, "item") if C4::Context->preference("CataloguingLog");
271 my $new_item_marc = _marc_from_item_hash($item_object->unblessed, $frameworkcode, $unlinked_item_subfields);
272 $item_field->replace_with($new_item_marc->field($itemtag));
275 # remove any MARC item fields for rejected items
276 foreach my $item_field (@bad_item_fields) {
277 $record->delete_field($item_field);
280 return (\@itemnumbers, \@errors);
283 =head2 ModItemFromMarc
285 my $item = ModItemFromMarc($item_marc, $biblionumber, $itemnumber[, $params]);
287 The final optional parameter, C<$params>, expected to contain
288 'skip_record_index' key, which relayed down to Koha::Item/store,
289 there it prevents calling of index_records,
290 which takes most of the time in batch adds/deletes: index_records better
291 to be called later in C<additem.pl> after the whole loop.
294 skip_record_index => 1|0
298 sub ModItemFromMarc {
299 my ( $item_marc, $biblionumber, $itemnumber, $params ) = @_;
301 my $frameworkcode = C4::Biblio::GetFrameworkCode($biblionumber);
302 my ( $itemtag, $itemsubfield ) = C4::Biblio::GetMarcFromKohaField( "items.itemnumber" );
304 my $localitemmarc = MARC::Record->new;
305 $localitemmarc->append_fields( $item_marc->field($itemtag) );
306 my $item_object = Koha::Items->find($itemnumber);
307 my $item = TransformMarcToKoha( $localitemmarc, $frameworkcode, 'items' );
309 # Retrieving the values for the fields that are not linked
310 my @mapped_fields = Koha::MarcSubfieldStructures->search(
312 frameworkcode => $frameworkcode,
313 kohafield => { -like => "items.%" }
315 )->get_column('kohafield');
316 for my $c ( $item_object->_result->result_source->columns ) {
317 next if grep { "items.$c" eq $_ } @mapped_fields;
318 $item->{$c} = $item_object->$c;
321 $item->{cn_source} = delete $item->{'items.cn_source'}; # Because of C4::Biblio::_disambiguate
322 delete $item->{'items.cn_sort'}; # Because of C4::Biblio::_disambiguate
323 $item->{itemnumber} = $itemnumber;
324 $item->{biblionumber} = $biblionumber;
326 my $existing_cn_sort = $item_object->cn_sort; # set_or_blank will reset cn_sort to undef as we are not passing it
327 # We rely on Koha::Item->store to modify it if itemcallnumber or cn_source is modified
328 $item_object = $item_object->set_or_blank($item);
329 $item_object->cn_sort($existing_cn_sort); # Resetting to the existing value
331 my $unlinked_item_subfields = _get_unlinked_item_subfields( $localitemmarc, $frameworkcode );
332 $item_object->more_subfields_xml(_get_unlinked_subfields_xml($unlinked_item_subfields));
333 $item_object->store({ skip_record_index => $params->{skip_record_index} });
335 return $item_object->unblessed;
338 =head2 ModItemTransfer
340 ModItemTransfer($itemnumber, $frombranch, $tobranch, $trigger, [$params]);
342 Marks an item as being transferred from one branch to another and records the trigger.
344 The last optional parameter allows for passing skip_record_index through to the items store call.
348 sub ModItemTransfer {
349 my ( $itemnumber, $frombranch, $tobranch, $trigger, $params ) = @_;
351 my $dbh = C4::Context->dbh;
352 my $item = Koha::Items->find( $itemnumber );
354 # Remove the 'shelving cart' location status if it is being used.
355 CartToShelf( $itemnumber ) if $item->location && $item->location eq 'CART' && ( !$item->permanent_location || $item->permanent_location ne 'CART' );
357 $dbh->do("UPDATE branchtransfers SET datearrived = NOW(), comments = ? WHERE itemnumber = ? AND datearrived IS NULL", undef, "Canceled, new transfer from $frombranch to $tobranch created", $itemnumber);
359 #new entry in branchtransfers....
360 my $sth = $dbh->prepare(
361 "INSERT INTO branchtransfers (itemnumber, frombranch, datesent, tobranch, reason)
362 VALUES (?, ?, NOW(), ?, ?)");
363 $sth->execute($itemnumber, $frombranch, $tobranch, $trigger);
365 # FIXME we are fetching the item twice in the 2 next statements!
366 Koha::Items->find($itemnumber)->holdingbranch($frombranch)->store({ log_action => 0, skip_record_index => $params->{skip_record_index} });
367 ModDateLastSeen($itemnumber, undef, { skip_record_index => $params->{skip_record_index} });
371 =head2 ModDateLastSeen
373 ModDateLastSeen( $itemnumber, $leave_item_lost, $params );
375 Mark item as seen. Is called when an item is issued, returned or manually marked during inventory/stocktaking.
376 C<$itemnumber> is the item number
377 C<$leave_item_lost> determines if a lost item will be found or remain lost
379 The last optional parameter allows for passing skip_record_index through to the items store call.
383 sub ModDateLastSeen {
384 my ( $itemnumber, $leave_item_lost, $params ) = @_;
386 my $today = output_pref({ dt => dt_from_string, dateformat => 'iso', dateonly => 1 });
388 my $item = Koha::Items->find($itemnumber);
389 $item->datelastseen($today);
390 $item->itemlost(0) unless $leave_item_lost;
391 $item->store({ log_action => 0, skip_record_index => $params->{skip_record_index} });
394 =head2 CheckItemPreSave
396 my $item_ref = TransformMarcToKoha($marc, 'items');
398 my %errors = CheckItemPreSave($item_ref);
399 if (exists $errors{'duplicate_barcode'}) {
400 print "item has duplicate barcode: ", $errors{'duplicate_barcode'}, "\n";
401 } elsif (exists $errors{'invalid_homebranch'}) {
402 print "item has invalid home branch: ", $errors{'invalid_homebranch'}, "\n";
403 } elsif (exists $errors{'invalid_holdingbranch'}) {
404 print "item has invalid holding branch: ", $errors{'invalid_holdingbranch'}, "\n";
409 Given a hashref containing item fields, determine if it can be
410 inserted or updated in the database. Specifically, checks for
411 database integrity issues, and returns a hash containing any
412 of the following keys, if applicable.
416 =item duplicate_barcode
418 Barcode, if it duplicates one already found in the database.
420 =item invalid_homebranch
422 Home branch, if not defined in branches table.
424 =item invalid_holdingbranch
426 Holding branch, if not defined in branches table.
430 This function does NOT implement any policy-related checks,
431 e.g., whether current operator is allowed to save an
432 item that has a given branch code.
436 sub CheckItemPreSave {
437 my $item_ref = shift;
441 # check for duplicate barcode
442 if (exists $item_ref->{'barcode'} and defined $item_ref->{'barcode'}) {
443 my $existing_item= Koha::Items->find({barcode => $item_ref->{'barcode'}});
444 if ($existing_item) {
445 if (!exists $item_ref->{'itemnumber'} # new item
446 or $item_ref->{'itemnumber'} != $existing_item->itemnumber) { # existing item
447 $errors{'duplicate_barcode'} = $item_ref->{'barcode'};
452 # check for valid home branch
453 if (exists $item_ref->{'homebranch'} and defined $item_ref->{'homebranch'}) {
454 my $home_library = Koha::Libraries->find( $item_ref->{homebranch} );
455 unless (defined $home_library) {
456 $errors{'invalid_homebranch'} = $item_ref->{'homebranch'};
460 # check for valid holding branch
461 if (exists $item_ref->{'holdingbranch'} and defined $item_ref->{'holdingbranch'}) {
462 my $holding_library = Koha::Libraries->find( $item_ref->{holdingbranch} );
463 unless (defined $holding_library) {
464 $errors{'invalid_holdingbranch'} = $item_ref->{'holdingbranch'};
472 =head1 EXPORTED SPECIAL ACCESSOR FUNCTIONS
474 The following functions provide various ways of
475 getting an item record, a set of item records, or
476 lists of authorized values for certain item fields.
480 =head2 GetItemsForInventory
482 ($itemlist, $iTotalRecords) = GetItemsForInventory( {
483 minlocation => $minlocation,
484 maxlocation => $maxlocation,
485 location => $location,
486 ignoreissued => $ignoreissued,
487 datelastseen => $datelastseen,
488 branchcode => $branchcode,
492 statushash => $statushash,
493 itemtypes => \@itemsarray,
496 Retrieve a list of title/authors/barcode/callnumber, for biblio inventory.
498 The sub returns a reference to a list of hashes, each containing
499 itemnumber, author, title, barcode, item callnumber, and date last
500 seen. It is ordered by callnumber then title.
502 The required minlocation & maxlocation parameters are used to specify a range of item callnumbers
503 the datelastseen can be used to specify that you want to see items not seen since a past date only.
504 offset & size can be used to retrieve only a part of the whole listing (defaut behaviour)
505 $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.
507 $iTotalRecords is the number of rows that would have been returned without the $offset, $size limit clause
511 sub GetItemsForInventory {
512 my ( $parameters ) = @_;
513 my $minlocation = $parameters->{'minlocation'} // '';
514 my $maxlocation = $parameters->{'maxlocation'} // '';
515 my $class_source = $parameters->{'class_source'} // C4::Context->preference('DefaultClassificationSource');
516 my $location = $parameters->{'location'} // '';
517 my $itemtype = $parameters->{'itemtype'} // '';
518 my $ignoreissued = $parameters->{'ignoreissued'} // '';
519 my $datelastseen = $parameters->{'datelastseen'} // '';
520 my $branchcode = $parameters->{'branchcode'} // '';
521 my $branch = $parameters->{'branch'} // '';
522 my $offset = $parameters->{'offset'} // '';
523 my $size = $parameters->{'size'} // '';
524 my $statushash = $parameters->{'statushash'} // '';
525 my $ignore_waiting_holds = $parameters->{'ignore_waiting_holds'} // '';
526 my $itemtypes = $parameters->{'itemtypes'} || [];
528 my $dbh = C4::Context->dbh;
529 my ( @bind_params, @where_strings );
531 my $min_cnsort = GetClassSort($class_source,undef,$minlocation);
532 my $max_cnsort = GetClassSort($class_source,undef,$maxlocation);
534 my $select_columns = q{
535 SELECT DISTINCT(items.itemnumber), barcode, itemcallnumber, title, author, biblio.biblionumber, biblio.frameworkcode, datelastseen, homebranch, location, notforloan, damaged, itemlost, withdrawn, stocknumber, items.cn_sort
537 my $select_count = q{SELECT COUNT(DISTINCT(items.itemnumber))};
540 LEFT JOIN biblio ON items.biblionumber = biblio.biblionumber
541 LEFT JOIN biblioitems on items.biblionumber = biblioitems.biblionumber
544 for my $authvfield (keys %$statushash){
545 if ( scalar @{$statushash->{$authvfield}} > 0 ){
546 my $joinedvals = join ',', @{$statushash->{$authvfield}};
547 push @where_strings, "$authvfield in (" . $joinedvals . ")";
553 push @where_strings, 'items.cn_sort >= ?';
554 push @bind_params, $min_cnsort;
558 push @where_strings, 'items.cn_sort <= ?';
559 push @bind_params, $max_cnsort;
563 $datelastseen = output_pref({ str => $datelastseen, dateformat => 'iso', dateonly => 1 });
564 push @where_strings, '(datelastseen < ? OR datelastseen IS NULL)';
565 push @bind_params, $datelastseen;
569 push @where_strings, 'items.location = ?';
570 push @bind_params, $location;
574 if($branch eq "homebranch"){
575 push @where_strings, 'items.homebranch = ?';
577 push @where_strings, 'items.holdingbranch = ?';
579 push @bind_params, $branchcode;
583 push @where_strings, 'biblioitems.itemtype = ?';
584 push @bind_params, $itemtype;
586 if ( $ignoreissued) {
587 $query .= "LEFT JOIN issues ON items.itemnumber = issues.itemnumber ";
588 push @where_strings, 'issues.date_due IS NULL';
591 if ( $ignore_waiting_holds ) {
592 $query .= "LEFT JOIN reserves ON items.itemnumber = reserves.itemnumber ";
593 push( @where_strings, q{(reserves.found != 'W' OR reserves.found IS NULL)} );
597 my $itemtypes_str = join ', ', @$itemtypes;
598 push @where_strings, "( biblioitems.itemtype IN (" . $itemtypes_str . ") OR items.itype IN (" . $itemtypes_str . ") )";
601 if ( @where_strings ) {
603 $query .= join ' AND ', @where_strings;
605 my $count_query = $select_count . $query;
606 $query .= ' ORDER BY items.cn_sort, itemcallnumber, title';
607 $query .= " LIMIT $offset, $size" if ($offset and $size);
608 $query = $select_columns . $query;
609 my $sth = $dbh->prepare($query);
610 $sth->execute( @bind_params );
613 my $tmpresults = $sth->fetchall_arrayref({});
614 $sth = $dbh->prepare( $count_query );
615 $sth->execute( @bind_params );
616 my ($iTotalRecords) = $sth->fetchrow_array();
618 my @avs = Koha::AuthorisedValues->search(
619 { 'marc_subfield_structures.kohafield' => { '>' => '' },
620 'me.authorised_value' => { '>' => '' },
622 { join => { category => 'marc_subfield_structures' },
623 distinct => ['marc_subfield_structures.kohafield, me.category, frameworkcode, me.authorised_value'],
624 '+select' => [ 'marc_subfield_structures.kohafield', 'marc_subfield_structures.frameworkcode', 'me.authorised_value', 'me.lib' ],
625 '+as' => [ 'kohafield', 'frameworkcode', 'authorised_value', 'lib' ],
629 my $avmapping = { map { $_->get_column('kohafield') . ',' . $_->get_column('frameworkcode') . ',' . $_->get_column('authorised_value') => $_->get_column('lib') } @avs };
631 foreach my $row (@$tmpresults) {
634 foreach (keys %$row) {
637 $avmapping->{ "items.$_," . $row->{'frameworkcode'} . "," . ( $row->{$_} // q{} ) }
640 $row->{$_} = $avmapping->{"items.$_,".$row->{'frameworkcode'}.",".$row->{$_}};
646 return (\@results, $iTotalRecords);
651 @results = GetItemsInfo($biblionumber);
653 Returns information about items with the given biblionumber.
655 C<GetItemsInfo> returns a list of references-to-hash. Each element
656 contains a number of keys. Most of them are attributes from the
657 C<biblio>, C<biblioitems>, C<items>, and C<itemtypes> tables in the
658 Koha database. Other keys include:
662 =item C<$data-E<gt>{branchname}>
664 The name (not the code) of the branch to which the book belongs.
666 =item C<$data-E<gt>{datelastseen}>
668 This is simply C<items.datelastseen>, except that while the date is
669 stored in YYYY-MM-DD format in the database, here it is converted to
670 DD/MM/YYYY format. A NULL date is returned as C<//>.
672 =item C<$data-E<gt>{datedue}>
674 =item C<$data-E<gt>{class}>
676 This is the concatenation of C<biblioitems.classification>, the book's
677 Dewey code, and C<biblioitems.subclass>.
679 =item C<$data-E<gt>{ocount}>
681 I think this is the number of copies of the book available.
683 =item C<$data-E<gt>{order}>
685 If this is set, it is set to C<One Order>.
692 my ( $biblionumber ) = @_;
693 my $dbh = C4::Context->dbh;
694 require C4::Languages;
695 my $language = C4::Languages::getlanguage();
701 biblioitems.itemtype,
704 biblioitems.publicationyear,
705 biblioitems.publishercode,
706 biblioitems.volumedate,
707 biblioitems.volumedesc,
710 items.notforloan as itemnotforloan,
711 issues.borrowernumber,
712 issues.date_due as datedue,
713 issues.onsite_checkout,
714 borrowers.cardnumber,
717 borrowers.branchcode as bcode,
719 serial.publisheddate,
720 itemtypes.description,
721 COALESCE( localization.translation, itemtypes.description ) AS translated_description,
722 itemtypes.notforloan as notforloan_per_itemtype,
726 holding.opac_info as holding_branch_opac_info,
727 home.opac_info as home_branch_opac_info,
728 IF(tmp_holdsqueue.itemnumber,1,0) AS has_pending_hold
730 LEFT JOIN branches AS holding ON items.holdingbranch = holding.branchcode
731 LEFT JOIN branches AS home ON items.homebranch=home.branchcode
732 LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber
733 LEFT JOIN biblioitems ON biblioitems.biblioitemnumber = items.biblioitemnumber
734 LEFT JOIN issues USING (itemnumber)
735 LEFT JOIN borrowers USING (borrowernumber)
736 LEFT JOIN serialitems USING (itemnumber)
737 LEFT JOIN serial USING (serialid)
738 LEFT JOIN itemtypes ON itemtypes.itemtype = "
739 . (C4::Context->preference('item-level_itypes') ? 'items.itype' : 'biblioitems.itemtype');
741 LEFT JOIN tmp_holdsqueue USING (itemnumber)
742 LEFT JOIN localization ON itemtypes.itemtype = localization.code
743 AND localization.entity = 'itemtypes'
744 AND localization.lang = ?
747 $query .= " WHERE items.biblionumber = ? ORDER BY home.branchname, items.enumchron, LPAD( items.copynumber, 8, '0' ), items.dateaccessioned DESC" ;
748 my $sth = $dbh->prepare($query);
749 $sth->execute($language, $biblionumber);
754 my $userenv = C4::Context->userenv;
755 my $want_not_same_branch = C4::Context->preference("IndependentBranches") && !C4::Context->IsSuperLibrarian();
756 while ( my $data = $sth->fetchrow_hashref ) {
757 if ( $data->{borrowernumber} && $want_not_same_branch) {
758 $data->{'NOTSAMEBRANCH'} = $data->{'bcode'} ne $userenv->{branch};
761 $serial ||= $data->{'serial'};
764 # get notforloan complete status if applicable
765 $descriptions = Koha::AuthorisedValues->get_description_by_koha_field({frameworkcode => $data->{frameworkcode}, kohafield => 'items.notforloan', authorised_value => $data->{itemnotforloan} });
766 $data->{notforloanvalue} = $descriptions->{lib} // '';
767 $data->{notforloanvalueopac} = $descriptions->{opac_description} // '';
769 # get restricted status and description if applicable
770 $descriptions = Koha::AuthorisedValues->get_description_by_koha_field({frameworkcode => $data->{frameworkcode}, kohafield => 'items.restricted', authorised_value => $data->{restricted} });
771 $data->{restrictedvalue} = $descriptions->{lib} // '';
772 $data->{restrictedvalueopac} = $descriptions->{opac_description} // '';
774 # my stack procedures
775 $descriptions = Koha::AuthorisedValues->get_description_by_koha_field({frameworkcode => $data->{frameworkcode}, kohafield => 'items.stack', authorised_value => $data->{stack} });
776 $data->{stack} = $descriptions->{lib} // '';
778 # Find the last 3 people who borrowed this item.
779 my $sth2 = $dbh->prepare("SELECT * FROM old_issues,borrowers
781 AND old_issues.borrowernumber = borrowers.borrowernumber
782 ORDER BY returndate DESC
784 $sth2->execute($data->{'itemnumber'});
786 while (my $data2 = $sth2->fetchrow_hashref()) {
787 $data->{"timestamp$ii"} = $data2->{'timestamp'} if $data2->{'timestamp'};
788 $data->{"card$ii"} = $data2->{'cardnumber'} if $data2->{'cardnumber'};
789 $data->{"borrower$ii"} = $data2->{'borrowernumber'} if $data2->{'borrowernumber'};
793 $results[$i] = $data;
798 ? sort { ($b->{'publisheddate'} || $b->{'enumchron'}) cmp ($a->{'publisheddate'} || $a->{'enumchron'}) } @results
802 =head2 GetItemsLocationInfo
804 my @itemlocinfo = GetItemsLocationInfo($biblionumber);
806 Returns the branch names, shelving location and itemcallnumber for each item attached to the biblio in question
808 C<GetItemsInfo> returns a list of references-to-hash. Data returned:
812 =item C<$data-E<gt>{homebranch}>
814 Branch Name of the item's homebranch
816 =item C<$data-E<gt>{holdingbranch}>
818 Branch Name of the item's holdingbranch
820 =item C<$data-E<gt>{location}>
822 Item's shelving location code
824 =item C<$data-E<gt>{location_intranet}>
826 The intranet description for the Shelving Location as set in authorised_values 'LOC'
828 =item C<$data-E<gt>{location_opac}>
830 The OPAC description for the Shelving Location as set in authorised_values 'LOC'. Falls back to intranet description if no OPAC
833 =item C<$data-E<gt>{itemcallnumber}>
835 Item's itemcallnumber
837 =item C<$data-E<gt>{cn_sort}>
839 Item's call number normalized for sorting
845 sub GetItemsLocationInfo {
846 my $biblionumber = shift;
849 my $dbh = C4::Context->dbh;
850 my $query = "SELECT a.branchname as homebranch, b.branchname as holdingbranch,
851 location, itemcallnumber, cn_sort
852 FROM items, branches as a, branches as b
853 WHERE homebranch = a.branchcode AND holdingbranch = b.branchcode
855 ORDER BY cn_sort ASC";
856 my $sth = $dbh->prepare($query);
857 $sth->execute($biblionumber);
859 while ( my $data = $sth->fetchrow_hashref ) {
860 my $av = Koha::AuthorisedValues->search({ category => 'LOC', authorised_value => $data->{location} });
861 $av = $av->count ? $av->next : undef;
862 $data->{location_intranet} = $av ? $av->lib : '';
863 $data->{location_opac} = $av ? $av->opac_description : '';
864 push @results, $data;
869 =head2 GetHostItemsInfo
871 $hostiteminfo = GetHostItemsInfo($hostfield);
872 Returns the iteminfo for items linked to records via a host field
876 sub GetHostItemsInfo {
880 if( !C4::Context->preference('EasyAnalyticalRecords') ) {
881 return @returnitemsInfo;
885 if( C4::Context->preference('marcflavour') eq 'MARC21' ||
886 C4::Context->preference('marcflavour') eq 'NORMARC') {
887 @fields = $record->field('773');
888 } elsif( C4::Context->preference('marcflavour') eq 'UNIMARC') {
889 @fields = $record->field('461');
892 foreach my $hostfield ( @fields ) {
893 my $hostbiblionumber = $hostfield->subfield("0");
894 my $linkeditemnumber = $hostfield->subfield("9");
895 my @hostitemInfos = GetItemsInfo($hostbiblionumber);
896 foreach my $hostitemInfo (@hostitemInfos) {
897 if( $hostitemInfo->{itemnumber} eq $linkeditemnumber ) {
898 push @returnitemsInfo, $hostitemInfo;
903 return @returnitemsInfo;
906 =head2 get_hostitemnumbers_of
908 my @itemnumbers_of = get_hostitemnumbers_of($biblionumber);
910 Given a biblionumber, return the list of corresponding itemnumbers that are linked to it via host fields
912 Return a reference on a hash where key is a biblionumber and values are
913 references on array of itemnumbers.
918 sub get_hostitemnumbers_of {
919 my ($biblionumber) = @_;
921 if( !C4::Context->preference('EasyAnalyticalRecords') ) {
925 my $marcrecord = C4::Biblio::GetMarcBiblio({ biblionumber => $biblionumber });
926 return unless $marcrecord;
928 my ( @returnhostitemnumbers, $tag, $biblio_s, $item_s );
930 my $marcflavor = C4::Context->preference('marcflavour');
931 if ( $marcflavor eq 'MARC21' || $marcflavor eq 'NORMARC' ) {
936 elsif ( $marcflavor eq 'UNIMARC' ) {
942 foreach my $hostfield ( $marcrecord->field($tag) ) {
943 my $hostbiblionumber = $hostfield->subfield($biblio_s);
944 next unless $hostbiblionumber; # have tag, don't have $biblio_s subfield
945 my $linkeditemnumber = $hostfield->subfield($item_s);
946 if ( ! $linkeditemnumber ) {
947 warn "ERROR biblionumber $biblionumber has 773^0, but doesn't have 9";
950 my $is_from_biblio = Koha::Items->search({ itemnumber => $linkeditemnumber, biblionumber => $hostbiblionumber });
951 push @returnhostitemnumbers, $linkeditemnumber
955 return @returnhostitemnumbers;
958 =head2 GetHiddenItemnumbers
960 my @itemnumbers_to_hide = GetHiddenItemnumbers({ items => \@items, borcat => $category });
962 Given a list of items it checks which should be hidden from the OPAC given
963 the current configuration. Returns a list of itemnumbers corresponding to
964 those that should be hidden. Optionally takes a borcat parameter for certain borrower types
969 sub GetHiddenItemnumbers {
971 my $items = $params->{items};
972 if (my $exceptions = C4::Context->preference('OpacHiddenItemsExceptions') and $params->{'borcat'}){
973 foreach my $except (split(/\|/, $exceptions)){
974 if ($params->{'borcat'} eq $except){
975 return; # we don't hide anything for this borrower category
981 my $yaml = C4::Context->preference('OpacHiddenItems');
982 return () if (! $yaml =~ /\S/ );
983 $yaml = "$yaml\n\n"; # YAML is anal on ending \n. Surplus does not hurt
986 $hidingrules = YAML::XS::Load(Encode::encode_utf8($yaml));
989 warn "Unable to parse OpacHiddenItems syspref : $@";
992 my $dbh = C4::Context->dbh;
995 foreach my $item (@$items) {
998 foreach my $field (keys %$hidingrules) {
1000 if (exists $item->{$field}) {
1001 $val = $item->{$field};
1004 my $query = "SELECT $field from items where itemnumber = ?";
1005 $val = $dbh->selectrow_array($query, undef, $item->{'itemnumber'});
1007 $val = '' unless defined $val;
1009 # If the results matches the values in the yaml file
1010 if (any { $val eq $_ } @{$hidingrules->{$field}}) {
1012 # We add the itemnumber to the list
1013 push @resultitems, $item->{'itemnumber'};
1015 # If at least one rule matched for an item, no need to test the others
1020 return @resultitems;
1023 =head1 LIMITED USE FUNCTIONS
1025 The following functions, while part of the public API,
1026 are not exported. This is generally because they are
1027 meant to be used by only one script for a specific
1028 purpose, and should not be used in any other context
1029 without careful thought.
1035 my $item_marc = GetMarcItem($biblionumber, $itemnumber);
1037 Returns MARC::Record of the item passed in parameter.
1038 This function is meant for use only in C<cataloguing/additem.pl>,
1039 where it is needed to support that script's MARC-like
1045 my ( $biblionumber, $itemnumber ) = @_;
1047 # GetMarcItem has been revised so that it does the following:
1048 # 1. Gets the item information from the items table.
1049 # 2. Converts it to a MARC field for storage in the bib record.
1051 # The previous behavior was:
1052 # 1. Get the bib record.
1053 # 2. Return the MARC tag corresponding to the item record.
1055 # The difference is that one treats the items row as authoritative,
1056 # while the other treats the MARC representation as authoritative
1057 # under certain circumstances.
1059 my $item = Koha::Items->find($itemnumber) or return;
1061 # Tack on 'items.' prefix to column names so that C4::Biblio::TransformKohaToMarc will work.
1062 # Also, don't emit a subfield if the underlying field is blank.
1064 return Item2Marc($item->unblessed, $biblionumber);
1068 my ($itemrecord,$biblionumber)=@_;
1071 defined($itemrecord->{$_}) && $itemrecord->{$_} ne '' ? ("items.$_" => $itemrecord->{$_}) : ()
1072 } keys %{ $itemrecord }
1074 my $framework = C4::Biblio::GetFrameworkCode( $biblionumber );
1075 my $itemmarc = C4::Biblio::TransformKohaToMarc( $mungeditem, { framework => $framework } );
1076 my ( $itemtag, $itemsubfield ) = C4::Biblio::GetMarcFromKohaField(
1077 "items.itemnumber", $framework,
1080 my $unlinked_item_subfields = _parse_unlinked_item_subfields_from_xml($mungeditem->{'items.more_subfields_xml'});
1081 if (defined $unlinked_item_subfields and $#$unlinked_item_subfields > -1) {
1082 foreach my $field ($itemmarc->field($itemtag)){
1083 $field->add_subfields(@$unlinked_item_subfields);
1089 =head1 PRIVATE FUNCTIONS AND VARIABLES
1091 The following functions are not meant to be called
1092 directly, but are documented in order to explain
1093 the inner workings of C<C4::Items>.
1097 =head2 MoveItemFromBiblio
1099 MoveItemFromBiblio($itenumber, $frombiblio, $tobiblio);
1101 Moves an item from a biblio to another
1103 Returns undef if the move failed or the biblionumber of the destination record otherwise
1107 sub MoveItemFromBiblio {
1108 my ($itemnumber, $frombiblio, $tobiblio) = @_;
1109 my $dbh = C4::Context->dbh;
1110 my ( $tobiblioitem ) = $dbh->selectrow_array(q|
1111 SELECT biblioitemnumber
1113 WHERE biblionumber = ?
1114 |, undef, $tobiblio );
1115 my $return = $dbh->do(q|
1117 SET biblioitemnumber = ?,
1119 WHERE itemnumber = ?
1120 AND biblionumber = ?
1121 |, undef, $tobiblioitem, $tobiblio, $itemnumber, $frombiblio );
1123 my $indexer = Koha::SearchEngine::Indexer->new({ index => $Koha::SearchEngine::BIBLIOS_INDEX });
1124 $indexer->index_records( $tobiblio, "specialUpdate", "biblioserver" );
1125 $indexer->index_records( $frombiblio, "specialUpdate", "biblioserver" );
1126 # Checking if the item we want to move is in an order
1127 require C4::Acquisition;
1128 my $order = C4::Acquisition::GetOrderFromItemnumber($itemnumber);
1130 # Replacing the biblionumber within the order if necessary
1131 $order->{'biblionumber'} = $tobiblio;
1132 C4::Acquisition::ModOrder($order);
1135 # Update reserves, hold_fill_targets, tmp_holdsqueue and linktracker tables
1136 for my $table_name ( qw( reserves hold_fill_targets tmp_holdsqueue linktracker ) ) {
1139 SET biblionumber = ?
1140 WHERE itemnumber = ?
1141 |, undef, $tobiblio, $itemnumber );
1148 =head2 _marc_from_item_hash
1150 my $item_marc = _marc_from_item_hash($item, $frameworkcode[, $unlinked_item_subfields]);
1152 Given an item hash representing a complete item record,
1153 create a C<MARC::Record> object containing an embedded
1154 tag representing that item.
1156 The third, optional parameter C<$unlinked_item_subfields> is
1157 an arrayref of subfields (not mapped to C<items> fields per the
1158 framework) to be added to the MARC representation
1163 sub _marc_from_item_hash {
1165 my $frameworkcode = shift;
1166 my $unlinked_item_subfields;
1168 $unlinked_item_subfields = shift;
1171 # Tack on 'items.' prefix to column names so lookup from MARC frameworks will work
1172 # Also, don't emit a subfield if the underlying field is blank.
1173 my $mungeditem = { map { (defined($item->{$_}) and $item->{$_} ne '') ?
1174 (/^items\./ ? ($_ => $item->{$_}) : ("items.$_" => $item->{$_}))
1175 : () } keys %{ $item } };
1177 my $item_marc = MARC::Record->new();
1178 foreach my $item_field ( keys %{$mungeditem} ) {
1179 my ( $tag, $subfield ) = C4::Biblio::GetMarcFromKohaField( $item_field );
1180 next unless defined $tag and defined $subfield; # skip if not mapped to MARC field
1181 my @values = split(/\s?\|\s?/, $mungeditem->{$item_field}, -1);
1182 foreach my $value (@values){
1183 if ( my $field = $item_marc->field($tag) ) {
1184 $field->add_subfields( $subfield => $value );
1186 my $add_subfields = [];
1187 if (defined $unlinked_item_subfields and ref($unlinked_item_subfields) eq 'ARRAY' and $#$unlinked_item_subfields > -1) {
1188 $add_subfields = $unlinked_item_subfields;
1190 $item_marc->add_fields( $tag, " ", " ", $subfield => $value, @$add_subfields );
1198 =head2 _repack_item_errors
1200 Add an error message hash generated by C<CheckItemPreSave>
1201 to a list of errors.
1205 sub _repack_item_errors {
1206 my $item_sequence_num = shift;
1207 my $item_ref = shift;
1208 my $error_ref = shift;
1210 my @repacked_errors = ();
1212 foreach my $error_code (sort keys %{ $error_ref }) {
1213 my $repacked_error = {};
1214 $repacked_error->{'item_sequence'} = $item_sequence_num;
1215 $repacked_error->{'item_barcode'} = exists($item_ref->{'barcode'}) ? $item_ref->{'barcode'} : '';
1216 $repacked_error->{'error_code'} = $error_code;
1217 $repacked_error->{'error_information'} = $error_ref->{$error_code};
1218 push @repacked_errors, $repacked_error;
1221 return @repacked_errors;
1224 =head2 _get_unlinked_item_subfields
1226 my $unlinked_item_subfields = _get_unlinked_item_subfields($original_item_marc, $frameworkcode);
1230 sub _get_unlinked_item_subfields {
1231 my $original_item_marc = shift;
1232 my $frameworkcode = shift;
1234 my $marcstructure = C4::Biblio::GetMarcStructure(1, $frameworkcode, { unsafe => 1 });
1236 # assume that this record has only one field, and that that
1237 # field contains only the item information
1239 my @fields = $original_item_marc->fields();
1240 if ($#fields > -1) {
1241 my $field = $fields[0];
1242 my $tag = $field->tag();
1243 foreach my $subfield ($field->subfields()) {
1244 if (defined $subfield->[1] and
1245 $subfield->[1] ne '' and
1246 !$marcstructure->{$tag}->{$subfield->[0]}->{'kohafield'}) {
1247 push @$subfields, $subfield->[0] => $subfield->[1];
1254 =head2 _get_unlinked_subfields_xml
1256 my $unlinked_subfields_xml = _get_unlinked_subfields_xml($unlinked_item_subfields);
1260 sub _get_unlinked_subfields_xml {
1261 my $unlinked_item_subfields = shift;
1264 if (defined $unlinked_item_subfields and ref($unlinked_item_subfields) eq 'ARRAY' and $#$unlinked_item_subfields > -1) {
1265 my $marc = MARC::Record->new();
1266 # use of tag 999 is arbitrary, and doesn't need to match the item tag
1267 # used in the framework
1268 $marc->append_fields(MARC::Field->new('999', ' ', ' ', @$unlinked_item_subfields));
1269 $marc->encoding("UTF-8");
1270 $xml = $marc->as_xml("USMARC");
1276 =head2 _parse_unlinked_item_subfields_from_xml
1278 my $unlinked_item_subfields = _parse_unlinked_item_subfields_from_xml($whole_item->{'more_subfields_xml'}):
1282 sub _parse_unlinked_item_subfields_from_xml {
1284 require C4::Charset;
1285 return unless defined $xml and $xml ne "";
1286 my $marc = MARC::Record->new_from_xml(C4::Charset::StripNonXmlChars($xml),'UTF-8');
1287 my $unlinked_subfields = [];
1288 my @fields = $marc->fields();
1289 if ($#fields > -1) {
1290 foreach my $subfield ($fields[0]->subfields()) {
1291 push @$unlinked_subfields, $subfield->[0] => $subfield->[1];
1294 return $unlinked_subfields;
1297 =head2 GetAnalyticsCount
1299 $count= &GetAnalyticsCount($itemnumber)
1301 counts Usage of itemnumber in Analytical bibliorecords.
1305 sub GetAnalyticsCount {
1306 my ($itemnumber) = @_;
1308 ### ZOOM search here
1310 $query= "hi=".$itemnumber;
1311 my $searcher = Koha::SearchEngine::Search->new({index => $Koha::SearchEngine::BIBLIOS_INDEX});
1312 my ($err,$res,$result) = $searcher->simple_search_compat($query,0,10);
1316 sub _SearchItems_build_where_fragment {
1319 my $dbh = C4::Context->dbh;
1322 if (exists($filter->{conjunction})) {
1323 my (@where_strs, @where_args);
1324 foreach my $f (@{ $filter->{filters} }) {
1325 my $fragment = _SearchItems_build_where_fragment($f);
1327 push @where_strs, $fragment->{str};
1328 push @where_args, @{ $fragment->{args} };
1333 $where_str = '(' . join (' ' . $filter->{conjunction} . ' ', @where_strs) . ')';
1336 args => \@where_args,
1340 my @columns = Koha::Database->new()->schema()->resultset('Item')->result_source->columns;
1341 push @columns, Koha::Database->new()->schema()->resultset('Biblio')->result_source->columns;
1342 push @columns, Koha::Database->new()->schema()->resultset('Biblioitem')->result_source->columns;
1343 my @operators = qw(= != > < >= <= like);
1344 push @operators, 'not like';
1345 my $field = $filter->{field} // q{};
1346 if ( (0 < grep { $_ eq $field } @columns) or (substr($field, 0, 5) eq 'marc:') ) {
1347 my $op = $filter->{operator};
1348 my $query = $filter->{query};
1349 my $ifnull = $filter->{ifnull};
1351 if (!$op or (0 == grep { $_ eq $op } @operators)) {
1352 $op = '='; # default operator
1356 if ($field =~ /^marc:(\d{3})(?:\$(\w))?$/) {
1358 my $marcsubfield = $2;
1359 my ($kohafield) = $dbh->selectrow_array(q|
1360 SELECT kohafield FROM marc_subfield_structure
1361 WHERE tagfield=? AND tagsubfield=? AND frameworkcode=''
1362 |, undef, $marcfield, $marcsubfield);
1365 $column = $kohafield;
1367 # MARC field is not linked to a DB field so we need to use
1368 # ExtractValue on marcxml from biblio_metadata or
1369 # items.more_subfields_xml, depending on the MARC field.
1372 my ($itemfield) = C4::Biblio::GetMarcFromKohaField('items.itemnumber');
1373 if ($marcfield eq $itemfield) {
1374 $sqlfield = 'more_subfields_xml';
1375 $xpath = '//record/datafield/subfield[@code="' . $marcsubfield . '"]';
1377 $sqlfield = 'metadata'; # From biblio_metadata
1378 if ($marcfield < 10) {
1379 $xpath = "//record/controlfield[\@tag=\"$marcfield\"]";
1381 $xpath = "//record/datafield[\@tag=\"$marcfield\"]/subfield[\@code=\"$marcsubfield\"]";
1384 $column = "ExtractValue($sqlfield, '$xpath')";
1390 if ( defined $ifnull ) {
1391 $column = "COALESCE($column, ?)";
1394 if (ref $query eq 'ARRAY') {
1397 } elsif ($op eq '!=') {
1401 str => "$column $op (" . join (',', ('?') x @$query) . ")",
1406 str => "$column $op ?",
1411 if ( defined $ifnull ) {
1412 unshift @{ $where_fragment->{args} }, $ifnull;
1417 return $where_fragment;
1422 my ($items, $total) = SearchItems($filter, $params);
1424 Perform a search among items
1426 $filter is a reference to a hash which can be a filter, or a combination of filters.
1428 A filter has the following keys:
1432 =item * field: the name of a SQL column in table items
1434 =item * query: the value to search in this column
1436 =item * operator: comparison operator. Can be one of = != > < >= <= like 'not like'
1440 A combination of filters hash the following keys:
1444 =item * conjunction: 'AND' or 'OR'
1446 =item * filters: array ref of filters
1450 $params is a reference to a hash that can contain the following parameters:
1454 =item * rows: Number of items to return. 0 returns everything (default: 0)
1456 =item * page: Page to return (return items from (page-1)*rows to (page*rows)-1)
1459 =item * sortby: A SQL column name in items table to sort on
1461 =item * sortorder: 'ASC' or 'DESC'
1468 my ($filter, $params) = @_;
1472 return unless ref $filter eq 'HASH';
1473 return unless ref $params eq 'HASH';
1475 # Default parameters
1476 $params->{rows} ||= 0;
1477 $params->{page} ||= 1;
1478 $params->{sortby} ||= 'itemnumber';
1479 $params->{sortorder} ||= 'ASC';
1481 my ($where_str, @where_args);
1482 my $where_fragment = _SearchItems_build_where_fragment($filter);
1483 if ($where_fragment) {
1484 $where_str = $where_fragment->{str};
1485 @where_args = @{ $where_fragment->{args} };
1488 my $dbh = C4::Context->dbh;
1490 SELECT SQL_CALC_FOUND_ROWS items.*
1492 LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber
1493 LEFT JOIN biblioitems ON biblioitems.biblioitemnumber = items.biblioitemnumber
1494 LEFT JOIN biblio_metadata ON biblio_metadata.biblionumber = biblio.biblionumber
1497 if (defined $where_str and $where_str ne '') {
1498 $query .= qq{ AND $where_str };
1501 $query .= q{ AND biblio_metadata.format = 'marcxml' AND biblio_metadata.schema = ? };
1502 push @where_args, C4::Context->preference('marcflavour');
1504 my @columns = Koha::Database->new()->schema()->resultset('Item')->result_source->columns;
1505 push @columns, Koha::Database->new()->schema()->resultset('Biblio')->result_source->columns;
1506 push @columns, Koha::Database->new()->schema()->resultset('Biblioitem')->result_source->columns;
1507 my $sortby = (0 < grep {$params->{sortby} eq $_} @columns)
1508 ? $params->{sortby} : 'itemnumber';
1509 my $sortorder = (uc($params->{sortorder}) eq 'ASC') ? 'ASC' : 'DESC';
1510 $query .= qq{ ORDER BY $sortby $sortorder };
1512 my $rows = $params->{rows};
1515 my $offset = $rows * ($params->{page}-1);
1516 $query .= qq { LIMIT ?, ? };
1517 push @limit_args, $offset, $rows;
1520 my $sth = $dbh->prepare($query);
1521 my $rv = $sth->execute(@where_args, @limit_args);
1523 return unless ($rv);
1524 my ($total_rows) = $dbh->selectrow_array(q{ SELECT FOUND_ROWS() });
1526 return ($sth->fetchall_arrayref({}), $total_rows);
1530 =head1 OTHER FUNCTIONS
1534 ($indicators, $value) = _find_value($tag, $subfield, $record,$encoding);
1536 Find the given $subfield in the given $tag in the given
1537 MARC::Record $record. If the subfield is found, returns
1538 the (indicators, value) pair; otherwise, (undef, undef) is
1542 Such a function is used in addbiblio AND additem and serial-edit and maybe could be used in Authorities.
1543 I suggest we export it from this module.
1548 my ( $tagfield, $insubfield, $record, $encoding ) = @_;
1551 if ( $tagfield < 10 ) {
1552 if ( $record->field($tagfield) ) {
1553 push @result, $record->field($tagfield)->data();
1558 foreach my $field ( $record->field($tagfield) ) {
1559 my @subfields = $field->subfields();
1560 foreach my $subfield (@subfields) {
1561 if ( @$subfield[0] eq $insubfield ) {
1562 push @result, @$subfield[1];
1563 $indicator = $field->indicator(1) . $field->indicator(2);
1568 return ( $indicator, @result );
1572 =head2 PrepareItemrecordDisplay
1574 PrepareItemrecordDisplay($itemrecord,$bibnum,$itemumber,$frameworkcode);
1576 Returns a hash with all the fields for Display a given item data in a template
1578 The $frameworkcode returns the item for the given frameworkcode, ONLY if bibnum is not provided
1582 sub PrepareItemrecordDisplay {
1584 my ( $bibnum, $itemnum, $defaultvalues, $frameworkcode ) = @_;
1586 my $dbh = C4::Context->dbh;
1587 $frameworkcode = C4::Biblio::GetFrameworkCode($bibnum) if $bibnum;
1588 my ( $itemtagfield, $itemtagsubfield ) = C4::Biblio::GetMarcFromKohaField( "items.itemnumber" );
1590 # Note: $tagslib obtained from GetMarcStructure() in 'unsafe' mode is
1591 # a shared data structure. No plugin (including custom ones) should change
1592 # its contents. See also GetMarcStructure.
1593 my $tagslib = GetMarcStructure( 1, $frameworkcode, { unsafe => 1 } );
1595 # return nothing if we don't have found an existing framework.
1596 return q{} unless $tagslib;
1599 $itemrecord = C4::Items::GetMarcItem( $bibnum, $itemnum );
1603 my $branch_limit = C4::Context->userenv ? C4::Context->userenv->{"branch"} : "";
1605 SELECT authorised_value,lib FROM authorised_values
1608 LEFT JOIN authorised_values_branches ON ( id = av_id )
1613 $query .= qq{ AND ( branchcode = ? OR branchcode IS NULL )} if $branch_limit;
1614 $query .= qq{ ORDER BY lib};
1615 my $authorised_values_sth = $dbh->prepare( $query );
1616 foreach my $tag ( sort keys %{$tagslib} ) {
1619 # loop through each subfield
1621 foreach my $subfield ( sort keys %{ $tagslib->{$tag} } ) {
1622 next if IsMarcStructureInternal($tagslib->{$tag}{$subfield});
1623 next unless ( $tagslib->{$tag}->{$subfield}->{'tab'} );
1624 next if ( $tagslib->{$tag}->{$subfield}->{'tab'} ne "10" );
1626 $subfield_data{tag} = $tag;
1627 $subfield_data{subfield} = $subfield;
1628 $subfield_data{countsubfield} = $cntsubf++;
1629 $subfield_data{kohafield} = $tagslib->{$tag}->{$subfield}->{'kohafield'};
1630 $subfield_data{id} = "tag_".$tag."_subfield_".$subfield."_".int(rand(1000000));
1632 # $subfield_data{marc_lib}=$tagslib->{$tag}->{$subfield}->{lib};
1633 $subfield_data{marc_lib} = $tagslib->{$tag}->{$subfield}->{lib};
1634 $subfield_data{mandatory} = $tagslib->{$tag}->{$subfield}->{mandatory};
1635 $subfield_data{repeatable} = $tagslib->{$tag}->{$subfield}->{repeatable};
1636 $subfield_data{hidden} = "display:none"
1637 if ( ( $tagslib->{$tag}->{$subfield}->{hidden} > 4 )
1638 || ( $tagslib->{$tag}->{$subfield}->{hidden} < -4 ) );
1639 my ( $x, $defaultvalue );
1641 ( $x, $defaultvalue ) = _find_value( $tag, $subfield, $itemrecord );
1643 $defaultvalue = $tagslib->{$tag}->{$subfield}->{defaultvalue} unless $defaultvalue;
1644 if ( !defined $defaultvalue ) {
1645 $defaultvalue = q||;
1647 $defaultvalue =~ s/"/"/g;
1648 # get today date & replace <<YYYY>>, <<MM>>, <<DD>> if provided in the default value
1649 my $today_dt = dt_from_string;
1650 my $year = $today_dt->strftime('%Y');
1651 my $shortyear = $today_dt->strftime('%y');
1652 my $month = $today_dt->strftime('%m');
1653 my $day = $today_dt->strftime('%d');
1654 $defaultvalue =~ s/<<YYYY>>/$year/g;
1655 $defaultvalue =~ s/<<YY>>/$shortyear/g;
1656 $defaultvalue =~ s/<<MM>>/$month/g;
1657 $defaultvalue =~ s/<<DD>>/$day/g;
1659 # And <<USER>> with surname (?)
1661 ( C4::Context->userenv
1662 ? C4::Context->userenv->{'surname'}
1663 : "superlibrarian" );
1664 $defaultvalue =~ s/<<USER>>/$username/g;
1667 my $maxlength = $tagslib->{$tag}->{$subfield}->{maxlength};
1669 # search for itemcallnumber if applicable
1670 if ( $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.itemcallnumber'
1671 && C4::Context->preference('itemcallnumber') && $itemrecord) {
1672 foreach my $itemcn_pref (split(/,/,C4::Context->preference('itemcallnumber'))){
1673 my $CNtag = substr( $itemcn_pref, 0, 3 );
1674 next unless my $field = $itemrecord->field($CNtag);
1675 my $CNsubfields = substr( $itemcn_pref, 3 );
1676 $defaultvalue = $field->as_string( $CNsubfields, ' ');
1677 last if $defaultvalue;
1680 if ( $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.itemcallnumber'
1682 && $defaultvalues->{'callnumber'} ) {
1683 if( $itemrecord and $defaultvalues and not $itemrecord->subfield($tag,$subfield) ){
1684 # if the item record exists, only use default value if the item has no callnumber
1685 $defaultvalue = $defaultvalues->{callnumber};
1686 } elsif ( !$itemrecord and $defaultvalues ) {
1687 # if the item record *doesn't* exists, always use the default value
1688 $defaultvalue = $defaultvalues->{callnumber};
1691 if ( ( $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.holdingbranch' || $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.homebranch' )
1693 && $defaultvalues->{'branchcode'} ) {
1694 if ( $itemrecord and $defaultvalues and not $itemrecord->subfield($tag,$subfield) ) {
1695 $defaultvalue = $defaultvalues->{branchcode};
1698 if ( ( $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.location' )
1700 && $defaultvalues->{'location'} ) {
1702 if ( $itemrecord and $defaultvalues and not $itemrecord->subfield($tag,$subfield) ) {
1703 # if the item record exists, only use default value if the item has no locationr
1704 $defaultvalue = $defaultvalues->{location};
1705 } elsif ( !$itemrecord and $defaultvalues ) {
1706 # if the item record *doesn't* exists, always use the default value
1707 $defaultvalue = $defaultvalues->{location};
1710 if ( $tagslib->{$tag}->{$subfield}->{authorised_value} ) {
1711 my @authorised_values;
1714 # builds list, depending on authorised value...
1716 if ( $tagslib->{$tag}->{$subfield}->{'authorised_value'} eq "branches" ) {
1717 if ( ( C4::Context->preference("IndependentBranches") )
1718 && !C4::Context->IsSuperLibrarian() ) {
1719 my $sth = $dbh->prepare( "SELECT branchcode,branchname FROM branches WHERE branchcode = ? ORDER BY branchname" );
1720 $sth->execute( C4::Context->userenv->{branch} );
1721 push @authorised_values, ""
1722 unless ( $tagslib->{$tag}->{$subfield}->{mandatory} );
1723 while ( my ( $branchcode, $branchname ) = $sth->fetchrow_array ) {
1724 push @authorised_values, $branchcode;
1725 $authorised_lib{$branchcode} = $branchname;
1728 my $sth = $dbh->prepare( "SELECT branchcode,branchname FROM branches ORDER BY branchname" );
1730 push @authorised_values, ""
1731 unless ( $tagslib->{$tag}->{$subfield}->{mandatory} );
1732 while ( my ( $branchcode, $branchname ) = $sth->fetchrow_array ) {
1733 push @authorised_values, $branchcode;
1734 $authorised_lib{$branchcode} = $branchname;
1738 $defaultvalue = C4::Context->userenv ? C4::Context->userenv->{branch} : undef;
1739 if ( $defaultvalues and $defaultvalues->{branchcode} ) {
1740 $defaultvalue = $defaultvalues->{branchcode};
1744 } elsif ( $tagslib->{$tag}->{$subfield}->{authorised_value} eq "itemtypes" ) {
1745 my $itemtypes = Koha::ItemTypes->search_with_localization;
1746 push @authorised_values, "";
1747 while ( my $itemtype = $itemtypes->next ) {
1748 push @authorised_values, $itemtype->itemtype;
1749 $authorised_lib{$itemtype->itemtype} = $itemtype->translated_description;
1751 if ($defaultvalues && $defaultvalues->{'itemtype'}) {
1752 $defaultvalue = $defaultvalues->{'itemtype'};
1756 } elsif ( $tagslib->{$tag}->{$subfield}->{authorised_value} eq "cn_source" ) {
1757 push @authorised_values, "";
1759 my $class_sources = GetClassSources();
1760 my $default_source = $defaultvalue || C4::Context->preference("DefaultClassificationSource");
1762 foreach my $class_source (sort keys %$class_sources) {
1763 next unless $class_sources->{$class_source}->{'used'} or
1764 ($class_source eq $default_source);
1765 push @authorised_values, $class_source;
1766 $authorised_lib{$class_source} = $class_sources->{$class_source}->{'description'};
1769 $defaultvalue = $default_source;
1771 #---- "true" authorised value
1773 $authorised_values_sth->execute(
1774 $tagslib->{$tag}->{$subfield}->{authorised_value},
1775 $branch_limit ? $branch_limit : ()
1777 push @authorised_values, "";
1778 while ( my ( $value, $lib ) = $authorised_values_sth->fetchrow_array ) {
1779 push @authorised_values, $value;
1780 $authorised_lib{$value} = $lib;
1783 $subfield_data{marc_value} = {
1785 values => \@authorised_values,
1786 default => $defaultvalue // q{},
1787 labels => \%authorised_lib,
1789 } elsif ( $tagslib->{$tag}->{$subfield}->{value_builder} ) {
1791 require Koha::FrameworkPlugin;
1792 my $plugin = Koha::FrameworkPlugin->new({
1793 name => $tagslib->{$tag}->{$subfield}->{value_builder},
1796 my $pars = { dbh => $dbh, record => undef, tagslib =>$tagslib, id => $subfield_data{id}, tabloop => undef };
1797 $plugin->build( $pars );
1798 if ( $itemrecord and my $field = $itemrecord->field($tag) ) {
1799 $defaultvalue = $field->subfield($subfield) || q{};
1801 if( !$plugin->errstr ) {
1802 #TODO Move html to template; see report 12176/13397
1803 my $tab= $plugin->noclick? '-1': '';
1804 my $class= $plugin->noclick? ' disabled': '';
1805 my $title= $plugin->noclick? 'No popup': 'Tag editor';
1806 $subfield_data{marc_value} = qq[<input type="text" id="$subfield_data{id}" name="field_value" class="input_marceditor" size="50" maxlength="$maxlength" value="$defaultvalue" /><a href="#" id="buttonDot_$subfield_data{id}" class="buttonDot $class" title="$title">...</a>\n].$plugin->javascript;
1808 warn $plugin->errstr;
1809 $subfield_data{marc_value} = qq(<input type="text" id="$subfield_data{id}" name="field_value" class="input_marceditor" size="50" maxlength="$maxlength" value="$defaultvalue" />); # supply default input form
1812 elsif ( $tag eq '' ) { # it's an hidden field
1813 $subfield_data{marc_value} = qq(<input type="hidden" id="$subfield_data{id}" name="field_value" class="input_marceditor" size="50" maxlength="$maxlength" value="$defaultvalue" />);
1815 elsif ( $tagslib->{$tag}->{$subfield}->{'hidden'} ) { # FIXME: shouldn't input type be "hidden" ?
1816 $subfield_data{marc_value} = qq(<input type="text" id="$subfield_data{id}" name="field_value" class="input_marceditor" size="50" maxlength="$maxlength" value="$defaultvalue" />);
1818 elsif ( length($defaultvalue) > 100
1819 or (C4::Context->preference("marcflavour") eq "UNIMARC" and
1820 300 <= $tag && $tag < 400 && $subfield eq 'a' )
1821 or (C4::Context->preference("marcflavour") eq "MARC21" and
1822 500 <= $tag && $tag < 600 )
1824 # oversize field (textarea)
1825 $subfield_data{marc_value} = qq(<textarea id="$subfield_data{id}" name="field_value" class="input_marceditor" size="50" maxlength="$maxlength">$defaultvalue</textarea>\n");
1827 $subfield_data{marc_value} = qq(<input type="text" id="$subfield_data{id}" name="field_value" class="input_marceditor" size="50" maxlength="$maxlength" value="$defaultvalue" />);
1829 push( @loop_data, \%subfield_data );
1834 if ( $itemrecord && $itemrecord->field($itemtagfield) ) {
1835 $itemnumber = $itemrecord->subfield( $itemtagfield, $itemtagsubfield );
1838 'itemtagfield' => $itemtagfield,
1839 'itemtagsubfield' => $itemtagsubfield,
1840 'itemnumber' => $itemnumber,
1841 'iteminformation' => \@loop_data
1845 sub ToggleNewStatus {
1846 my ( $params ) = @_;
1847 my @rules = @{ $params->{rules} };
1848 my $report_only = $params->{report_only};
1850 my $dbh = C4::Context->dbh;
1852 my @item_columns = map { "items.$_" } Koha::Items->columns;
1853 my @biblioitem_columns = map { "biblioitems.$_" } Koha::Biblioitems->columns;
1855 for my $rule ( @rules ) {
1856 my $age = $rule->{age};
1857 my $conditions = $rule->{conditions};
1858 my $substitutions = $rule->{substitutions};
1859 foreach ( @$substitutions ) {
1860 ( $_->{item_field} ) = ( $_->{field} =~ /items\.(.*)/ );
1867 LEFT JOIN biblioitems ON biblioitems.biblionumber = items.biblionumber
1870 for my $condition ( @$conditions ) {
1872 grep { $_ eq $condition->{field} } @item_columns
1873 or grep { $_ eq $condition->{field} } @biblioitem_columns
1875 if ( $condition->{value} =~ /\|/ ) {
1876 my @values = split /\|/, $condition->{value};
1877 $query .= qq| AND $condition->{field} IN (|
1878 . join( ',', ('?') x scalar @values )
1880 push @params, @values;
1882 $query .= qq| AND $condition->{field} = ?|;
1883 push @params, $condition->{value};
1887 if ( defined $age ) {
1888 $query .= q| AND TO_DAYS(NOW()) - TO_DAYS(dateaccessioned) >= ? |;
1891 my $sth = $dbh->prepare($query);
1892 $sth->execute( @params );
1893 while ( my $values = $sth->fetchrow_hashref ) {
1894 my $biblionumber = $values->{biblionumber};
1895 my $itemnumber = $values->{itemnumber};
1896 my $item = Koha::Items->find($itemnumber);
1897 for my $substitution ( @$substitutions ) {
1898 my $field = $substitution->{item_field};
1899 my $value = $substitution->{value};
1900 next unless $substitution->{field};
1901 next if ( defined $values->{ $substitution->{item_field} } and $values->{ $substitution->{item_field} } eq $substitution->{value} );
1902 $item->$field($value);
1903 push @{ $report->{$itemnumber} }, $substitution;
1905 $item->store unless $report_only;