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
59 use List::MoreUtils qw(any);
61 use DateTime::Format::MySQL;
62 use Data::Dumper; # used as part of logging item record changes, not just for
63 # debugging; so please don't remove this
65 use Koha::AuthorisedValues;
66 use Koha::DateUtils qw(dt_from_string);
69 use Koha::Biblioitems;
72 use Koha::SearchEngine;
73 use Koha::SearchEngine::Indexer;
74 use Koha::SearchEngine::Search;
79 C4::Items - item management functions
83 This module contains an API for manipulating item
84 records in Koha, and is used by cataloguing, circulation,
85 acquisitions, and serials management.
87 # FIXME This POD is not up-to-date
88 A Koha item record is stored in two places: the
89 items table and embedded in a MARC tag in the XML
90 version of the associated bib record in C<biblioitems.marcxml>.
91 This is done to allow the item information to be readily
92 indexed (e.g., by Zebra), but means that each item
93 modification transaction must keep the items table
94 and the MARC XML in sync at all times.
96 The items table will be considered authoritative. In other
97 words, if there is ever a discrepancy between the items
98 table and the MARC XML, the items table should be considered
101 =head1 HISTORICAL NOTE
103 Most of the functions in C<C4::Items> were originally in
104 the C<C4::Biblio> module.
106 =head1 CORE EXPORTED FUNCTIONS
108 The following functions are meant for use by users
115 CartToShelf($itemnumber);
117 Set the current shelving location of the item record
118 to its stored permanent shelving location. This is
119 primarily used to indicate when an item whose current
120 location is a special processing ('PROC') or shelving cart
121 ('CART') location is back in the stacks.
126 my ( $itemnumber ) = @_;
128 unless ( $itemnumber ) {
129 croak "FAILED CartToShelf() - no itemnumber supplied";
132 my $item = Koha::Items->find($itemnumber);
133 if ( $item->location eq 'CART' ) {
134 $item->location($item->permanent_location)->store;
138 =head2 AddItemFromMarc
140 my ($biblionumber, $biblioitemnumber, $itemnumber)
141 = AddItemFromMarc($source_item_marc, $biblionumber[, $params]);
143 Given a MARC::Record object containing an embedded item
144 record and a biblionumber, create a new item record.
146 The final optional parameter, C<$params>, expected to contain
147 'skip_record_index' key, which relayed down to Koha::Item/store,
148 there it prevents calling of index_records,
149 which takes most of the time in batch adds/deletes: index_records
150 to be called later in C<additem.pl> after the whole loop.
153 skip_record_index => 1|0
157 sub AddItemFromMarc {
158 my $source_item_marc = shift;
159 my $biblionumber = shift;
160 my $params = @_ ? shift : {};
162 my $dbh = C4::Context->dbh;
164 # parse item hash from MARC
165 my $frameworkcode = C4::Biblio::GetFrameworkCode($biblionumber);
166 my ( $itemtag, $itemsubfield ) = C4::Biblio::GetMarcFromKohaField( "items.itemnumber" );
168 my $localitemmarc = MARC::Record->new;
169 $localitemmarc->append_fields( $source_item_marc->field($itemtag) );
171 my $item_values = C4::Biblio::TransformMarcToKoha( $localitemmarc, $frameworkcode, 'items' );
172 my $unlinked_item_subfields = _get_unlinked_item_subfields( $localitemmarc, $frameworkcode );
173 $item_values->{more_subfields_xml} = _get_unlinked_subfields_xml($unlinked_item_subfields);
174 $item_values->{biblionumber} = $biblionumber;
175 $item_values->{cn_source} = delete $item_values->{'items.cn_source'}; # Because of C4::Biblio::_disambiguate
176 $item_values->{cn_sort} = delete $item_values->{'items.cn_sort'}; # Because of C4::Biblio::_disambiguate
177 my $item = Koha::Item->new( $item_values )->store({ skip_record_index => $params->{skip_record_index} });
178 return ( $item->biblionumber, $item->biblioitemnumber, $item->itemnumber );
181 =head2 AddItemBatchFromMarc
183 ($itemnumber_ref, $error_ref) = AddItemBatchFromMarc($record,
184 $biblionumber, $biblioitemnumber, $frameworkcode);
186 Efficiently create item records from a MARC biblio record with
187 embedded item fields. This routine is suitable for batch jobs.
189 This API assumes that the bib record has already been
190 saved to the C<biblio> and C<biblioitems> tables. It does
191 not expect that C<biblio_metadata.metadata> is populated, but it
192 will do so via a call to ModBibiloMarc.
194 The goal of this API is to have a similar effect to using AddBiblio
195 and AddItems in succession, but without inefficient repeated
196 parsing of the MARC XML bib record.
198 This function returns an arrayref of new itemsnumbers and an arrayref of item
199 errors encountered during the processing. Each entry in the errors
200 list is a hashref containing the following keys:
206 Sequence number of original item tag in the MARC record.
210 Item barcode, provide to assist in the construction of
211 useful error messages.
215 Code representing the error condition. Can be 'duplicate_barcode',
216 'invalid_homebranch', or 'invalid_holdingbranch'.
218 =item error_information
220 Additional information appropriate to the error condition.
226 sub AddItemBatchFromMarc {
227 my ($record, $biblionumber, $biblioitemnumber, $frameworkcode) = @_;
228 my @itemnumbers = ();
230 my $dbh = C4::Context->dbh;
232 # We modify the record, so lets work on a clone so we don't change the
234 $record = $record->clone();
235 # loop through the item tags and start creating items
236 my @bad_item_fields = ();
237 my ($itemtag, $itemsubfield) = C4::Biblio::GetMarcFromKohaField( "items.itemnumber" );
238 my $item_sequence_num = 0;
239 ITEMFIELD: foreach my $item_field ($record->field($itemtag)) {
240 $item_sequence_num++;
241 # we take the item field and stick it into a new
242 # MARC record -- this is required so far because (FIXME)
243 # TransformMarcToKoha requires a MARC::Record, not a MARC::Field
244 # and there is no TransformMarcFieldToKoha
245 my $temp_item_marc = MARC::Record->new();
246 $temp_item_marc->append_fields($item_field);
248 # add biblionumber and biblioitemnumber
249 my $item = TransformMarcToKoha( $temp_item_marc, $frameworkcode, 'items' );
250 my $unlinked_item_subfields = _get_unlinked_item_subfields($temp_item_marc, $frameworkcode);
251 $item->{'more_subfields_xml'} = _get_unlinked_subfields_xml($unlinked_item_subfields);
252 $item->{'biblionumber'} = $biblionumber;
253 $item->{'biblioitemnumber'} = $biblioitemnumber;
254 $item->{cn_source} = delete $item->{'items.cn_source'}; # Because of C4::Biblio::_disambiguate
255 $item->{cn_sort} = delete $item->{'items.cn_sort'}; # Because of C4::Biblio::_disambiguate
257 # check for duplicate barcode
258 my %item_errors = CheckItemPreSave($item);
260 push @errors, _repack_item_errors($item_sequence_num, $item, \%item_errors);
261 push @bad_item_fields, $item_field;
265 my $item_object = Koha::Item->new($item)->store;
266 push @itemnumbers, $item_object->itemnumber; # FIXME not checking error
268 logaction("CATALOGUING", "ADD", $item_object->itemnumber, "item") if C4::Context->preference("CataloguingLog");
270 my $new_item_marc = _marc_from_item_hash($item_object->unblessed, $frameworkcode, $unlinked_item_subfields);
271 $item_field->replace_with($new_item_marc->field($itemtag));
274 # remove any MARC item fields for rejected items
275 foreach my $item_field (@bad_item_fields) {
276 $record->delete_field($item_field);
279 return (\@itemnumbers, \@errors);
282 =head2 ModItemFromMarc
284 my $item = ModItemFromMarc($item_marc, $biblionumber, $itemnumber[, $params]);
286 The final optional parameter, C<$params>, expected to contain
287 'skip_record_index' key, which relayed down to Koha::Item/store,
288 there it prevents calling of index_records,
289 which takes most of the time in batch adds/deletes: index_records better
290 to be called later in C<additem.pl> after the whole loop.
293 skip_record_index => 1|0
297 sub ModItemFromMarc {
298 my ( $item_marc, $biblionumber, $itemnumber, $params ) = @_;
300 my $frameworkcode = C4::Biblio::GetFrameworkCode($biblionumber);
301 my ( $itemtag, $itemsubfield ) = C4::Biblio::GetMarcFromKohaField( "items.itemnumber" );
303 my $localitemmarc = MARC::Record->new;
304 $localitemmarc->append_fields( $item_marc->field($itemtag) );
305 my $item_object = Koha::Items->find($itemnumber);
306 my $item = TransformMarcToKoha( $localitemmarc, $frameworkcode, 'items' );
308 # Retrieving the values for the fields that are not linked
309 my @mapped_fields = Koha::MarcSubfieldStructures->search(
311 frameworkcode => $frameworkcode,
312 kohafield => { -like => "items.%" }
314 )->get_column('kohafield');
315 for my $c ( $item_object->_result->result_source->columns ) {
316 next if grep { "items.$c" eq $_ } @mapped_fields;
317 $item->{$c} = $item_object->$c;
320 $item->{cn_source} = delete $item->{'items.cn_source'}; # Because of C4::Biblio::_disambiguate
321 delete $item->{'items.cn_sort'}; # Because of C4::Biblio::_disambiguate
322 $item->{itemnumber} = $itemnumber;
323 $item->{biblionumber} = $biblionumber;
325 my $existing_cn_sort = $item_object->cn_sort; # set_or_blank will reset cn_sort to undef as we are not passing it
326 # We rely on Koha::Item->store to modify it if itemcallnumber or cn_source is modified
327 $item_object = $item_object->set_or_blank($item);
328 $item_object->cn_sort($existing_cn_sort); # Resetting to the existing value
330 my $unlinked_item_subfields = _get_unlinked_item_subfields( $localitemmarc, $frameworkcode );
331 $item_object->more_subfields_xml(_get_unlinked_subfields_xml($unlinked_item_subfields));
332 $item_object->store({ skip_record_index => $params->{skip_record_index} });
334 return $item_object->unblessed;
337 =head2 ModItemTransfer
339 ModItemTransfer($itemnumber, $frombranch, $tobranch, $trigger, [$params]);
341 Marks an item as being transferred from one branch to another and records the trigger.
343 The last optional parameter allows for passing skip_record_index through to the items store call.
347 sub ModItemTransfer {
348 my ( $itemnumber, $frombranch, $tobranch, $trigger, $params ) = @_;
350 my $dbh = C4::Context->dbh;
351 my $item = Koha::Items->find( $itemnumber );
353 # Remove the 'shelving cart' location status if it is being used.
354 CartToShelf( $itemnumber ) if $item->location && $item->location eq 'CART' && ( !$item->permanent_location || $item->permanent_location ne 'CART' );
356 $dbh->do("UPDATE branchtransfers SET datearrived = NOW(), comments = ? WHERE itemnumber = ? AND datearrived IS NULL", undef, "Canceled, new transfer from $frombranch to $tobranch created", $itemnumber);
358 #new entry in branchtransfers....
359 my $sth = $dbh->prepare(
360 "INSERT INTO branchtransfers (itemnumber, frombranch, datesent, tobranch, reason)
361 VALUES (?, ?, NOW(), ?, ?)");
362 $sth->execute($itemnumber, $frombranch, $tobranch, $trigger);
364 # FIXME we are fetching the item twice in the 2 next statements!
365 Koha::Items->find($itemnumber)->holdingbranch($frombranch)->store({ log_action => 0, skip_record_index => $params->{skip_record_index} });
366 ModDateLastSeen($itemnumber, undef, { skip_record_index => $params->{skip_record_index} });
370 =head2 ModDateLastSeen
372 ModDateLastSeen( $itemnumber, $leave_item_lost, $params );
374 Mark item as seen. Is called when an item is issued, returned or manually marked during inventory/stocktaking.
375 C<$itemnumber> is the item number
376 C<$leave_item_lost> determines if a lost item will be found or remain lost
378 The last optional parameter allows for passing skip_record_index through to the items store call.
382 sub ModDateLastSeen {
383 my ( $itemnumber, $leave_item_lost, $params ) = @_;
385 my $today = output_pref({ dt => dt_from_string, dateformat => 'iso', dateonly => 1 });
387 my $item = Koha::Items->find($itemnumber);
388 $item->datelastseen($today);
389 $item->itemlost(0) unless $leave_item_lost;
390 $item->store({ log_action => 0, skip_record_index => $params->{skip_record_index} });
393 =head2 CheckItemPreSave
395 my $item_ref = TransformMarcToKoha($marc, 'items');
397 my %errors = CheckItemPreSave($item_ref);
398 if (exists $errors{'duplicate_barcode'}) {
399 print "item has duplicate barcode: ", $errors{'duplicate_barcode'}, "\n";
400 } elsif (exists $errors{'invalid_homebranch'}) {
401 print "item has invalid home branch: ", $errors{'invalid_homebranch'}, "\n";
402 } elsif (exists $errors{'invalid_holdingbranch'}) {
403 print "item has invalid holding branch: ", $errors{'invalid_holdingbranch'}, "\n";
408 Given a hashref containing item fields, determine if it can be
409 inserted or updated in the database. Specifically, checks for
410 database integrity issues, and returns a hash containing any
411 of the following keys, if applicable.
415 =item duplicate_barcode
417 Barcode, if it duplicates one already found in the database.
419 =item invalid_homebranch
421 Home branch, if not defined in branches table.
423 =item invalid_holdingbranch
425 Holding branch, if not defined in branches table.
429 This function does NOT implement any policy-related checks,
430 e.g., whether current operator is allowed to save an
431 item that has a given branch code.
435 sub CheckItemPreSave {
436 my $item_ref = shift;
440 # check for duplicate barcode
441 if (exists $item_ref->{'barcode'} and defined $item_ref->{'barcode'}) {
442 my $existing_item= Koha::Items->find({barcode => $item_ref->{'barcode'}});
443 if ($existing_item) {
444 if (!exists $item_ref->{'itemnumber'} # new item
445 or $item_ref->{'itemnumber'} != $existing_item->itemnumber) { # existing item
446 $errors{'duplicate_barcode'} = $item_ref->{'barcode'};
451 # check for valid home branch
452 if (exists $item_ref->{'homebranch'} and defined $item_ref->{'homebranch'}) {
453 my $home_library = Koha::Libraries->find( $item_ref->{homebranch} );
454 unless (defined $home_library) {
455 $errors{'invalid_homebranch'} = $item_ref->{'homebranch'};
459 # check for valid holding branch
460 if (exists $item_ref->{'holdingbranch'} and defined $item_ref->{'holdingbranch'}) {
461 my $holding_library = Koha::Libraries->find( $item_ref->{holdingbranch} );
462 unless (defined $holding_library) {
463 $errors{'invalid_holdingbranch'} = $item_ref->{'holdingbranch'};
471 =head1 EXPORTED SPECIAL ACCESSOR FUNCTIONS
473 The following functions provide various ways of
474 getting an item record, a set of item records, or
475 lists of authorized values for certain item fields.
479 =head2 GetItemsForInventory
481 ($itemlist, $iTotalRecords) = GetItemsForInventory( {
482 minlocation => $minlocation,
483 maxlocation => $maxlocation,
484 location => $location,
485 ignoreissued => $ignoreissued,
486 datelastseen => $datelastseen,
487 branchcode => $branchcode,
491 statushash => $statushash,
492 itemtypes => \@itemsarray,
495 Retrieve a list of title/authors/barcode/callnumber, for biblio inventory.
497 The sub returns a reference to a list of hashes, each containing
498 itemnumber, author, title, barcode, item callnumber, and date last
499 seen. It is ordered by callnumber then title.
501 The required minlocation & maxlocation parameters are used to specify a range of item callnumbers
502 the datelastseen can be used to specify that you want to see items not seen since a past date only.
503 offset & size can be used to retrieve only a part of the whole listing (defaut behaviour)
504 $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.
506 $iTotalRecords is the number of rows that would have been returned without the $offset, $size limit clause
510 sub GetItemsForInventory {
511 my ( $parameters ) = @_;
512 my $minlocation = $parameters->{'minlocation'} // '';
513 my $maxlocation = $parameters->{'maxlocation'} // '';
514 my $class_source = $parameters->{'class_source'} // C4::Context->preference('DefaultClassificationSource');
515 my $location = $parameters->{'location'} // '';
516 my $itemtype = $parameters->{'itemtype'} // '';
517 my $ignoreissued = $parameters->{'ignoreissued'} // '';
518 my $datelastseen = $parameters->{'datelastseen'} // '';
519 my $branchcode = $parameters->{'branchcode'} // '';
520 my $branch = $parameters->{'branch'} // '';
521 my $offset = $parameters->{'offset'} // '';
522 my $size = $parameters->{'size'} // '';
523 my $statushash = $parameters->{'statushash'} // '';
524 my $ignore_waiting_holds = $parameters->{'ignore_waiting_holds'} // '';
525 my $itemtypes = $parameters->{'itemtypes'} || [];
527 my $dbh = C4::Context->dbh;
528 my ( @bind_params, @where_strings );
530 my $min_cnsort = GetClassSort($class_source,undef,$minlocation);
531 my $max_cnsort = GetClassSort($class_source,undef,$maxlocation);
533 my $select_columns = q{
534 SELECT DISTINCT(items.itemnumber), barcode, itemcallnumber, title, author, biblio.biblionumber, biblio.frameworkcode, datelastseen, homebranch, location, notforloan, damaged, itemlost, withdrawn, stocknumber, items.cn_sort
536 my $select_count = q{SELECT COUNT(DISTINCT(items.itemnumber))};
539 LEFT JOIN biblio ON items.biblionumber = biblio.biblionumber
540 LEFT JOIN biblioitems on items.biblionumber = biblioitems.biblionumber
543 for my $authvfield (keys %$statushash){
544 if ( scalar @{$statushash->{$authvfield}} > 0 ){
545 my $joinedvals = join ',', @{$statushash->{$authvfield}};
546 push @where_strings, "$authvfield in (" . $joinedvals . ")";
552 push @where_strings, 'items.cn_sort >= ?';
553 push @bind_params, $min_cnsort;
557 push @where_strings, 'items.cn_sort <= ?';
558 push @bind_params, $max_cnsort;
562 $datelastseen = output_pref({ str => $datelastseen, dateformat => 'iso', dateonly => 1 });
563 push @where_strings, '(datelastseen < ? OR datelastseen IS NULL)';
564 push @bind_params, $datelastseen;
568 push @where_strings, 'items.location = ?';
569 push @bind_params, $location;
573 if($branch eq "homebranch"){
574 push @where_strings, 'items.homebranch = ?';
576 push @where_strings, 'items.holdingbranch = ?';
578 push @bind_params, $branchcode;
582 push @where_strings, 'biblioitems.itemtype = ?';
583 push @bind_params, $itemtype;
585 if ( $ignoreissued) {
586 $query .= "LEFT JOIN issues ON items.itemnumber = issues.itemnumber ";
587 push @where_strings, 'issues.date_due IS NULL';
590 if ( $ignore_waiting_holds ) {
591 $query .= "LEFT JOIN reserves ON items.itemnumber = reserves.itemnumber ";
592 push( @where_strings, q{(reserves.found != 'W' OR reserves.found IS NULL)} );
596 my $itemtypes_str = join ', ', @$itemtypes;
597 push @where_strings, "( biblioitems.itemtype IN (" . $itemtypes_str . ") OR items.itype IN (" . $itemtypes_str . ") )";
600 if ( @where_strings ) {
602 $query .= join ' AND ', @where_strings;
604 my $count_query = $select_count . $query;
605 $query .= ' ORDER BY items.cn_sort, itemcallnumber, title';
606 $query .= " LIMIT $offset, $size" if ($offset and $size);
607 $query = $select_columns . $query;
608 my $sth = $dbh->prepare($query);
609 $sth->execute( @bind_params );
612 my $tmpresults = $sth->fetchall_arrayref({});
613 $sth = $dbh->prepare( $count_query );
614 $sth->execute( @bind_params );
615 my ($iTotalRecords) = $sth->fetchrow_array();
617 my @avs = Koha::AuthorisedValues->search(
618 { 'marc_subfield_structures.kohafield' => { '>' => '' },
619 'me.authorised_value' => { '>' => '' },
621 { join => { category => 'marc_subfield_structures' },
622 distinct => ['marc_subfield_structures.kohafield, me.category, frameworkcode, me.authorised_value'],
623 '+select' => [ 'marc_subfield_structures.kohafield', 'marc_subfield_structures.frameworkcode', 'me.authorised_value', 'me.lib' ],
624 '+as' => [ 'kohafield', 'frameworkcode', 'authorised_value', 'lib' ],
628 my $avmapping = { map { $_->get_column('kohafield') . ',' . $_->get_column('frameworkcode') . ',' . $_->get_column('authorised_value') => $_->get_column('lib') } @avs };
630 foreach my $row (@$tmpresults) {
633 foreach (keys %$row) {
636 $avmapping->{ "items.$_," . $row->{'frameworkcode'} . "," . ( $row->{$_} // q{} ) }
639 $row->{$_} = $avmapping->{"items.$_,".$row->{'frameworkcode'}.",".$row->{$_}};
645 return (\@results, $iTotalRecords);
650 @results = GetItemsInfo($biblionumber);
652 Returns information about items with the given biblionumber.
654 C<GetItemsInfo> returns a list of references-to-hash. Each element
655 contains a number of keys. Most of them are attributes from the
656 C<biblio>, C<biblioitems>, C<items>, and C<itemtypes> tables in the
657 Koha database. Other keys include:
661 =item C<$data-E<gt>{branchname}>
663 The name (not the code) of the branch to which the book belongs.
665 =item C<$data-E<gt>{datelastseen}>
667 This is simply C<items.datelastseen>, except that while the date is
668 stored in YYYY-MM-DD format in the database, here it is converted to
669 DD/MM/YYYY format. A NULL date is returned as C<//>.
671 =item C<$data-E<gt>{datedue}>
673 =item C<$data-E<gt>{class}>
675 This is the concatenation of C<biblioitems.classification>, the book's
676 Dewey code, and C<biblioitems.subclass>.
678 =item C<$data-E<gt>{ocount}>
680 I think this is the number of copies of the book available.
682 =item C<$data-E<gt>{order}>
684 If this is set, it is set to C<One Order>.
691 my ( $biblionumber ) = @_;
692 my $dbh = C4::Context->dbh;
693 require C4::Languages;
694 my $language = C4::Languages::getlanguage();
700 biblioitems.itemtype,
703 biblioitems.publicationyear,
704 biblioitems.publishercode,
705 biblioitems.volumedate,
706 biblioitems.volumedesc,
709 items.notforloan as itemnotforloan,
710 issues.borrowernumber,
711 issues.date_due as datedue,
712 issues.onsite_checkout,
713 borrowers.cardnumber,
716 borrowers.branchcode as bcode,
718 serial.publisheddate,
719 itemtypes.description,
720 COALESCE( localization.translation, itemtypes.description ) AS translated_description,
721 itemtypes.notforloan as notforloan_per_itemtype,
725 holding.opac_info as holding_branch_opac_info,
726 home.opac_info as home_branch_opac_info,
727 IF(tmp_holdsqueue.itemnumber,1,0) AS has_pending_hold
729 LEFT JOIN branches AS holding ON items.holdingbranch = holding.branchcode
730 LEFT JOIN branches AS home ON items.homebranch=home.branchcode
731 LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber
732 LEFT JOIN biblioitems ON biblioitems.biblioitemnumber = items.biblioitemnumber
733 LEFT JOIN issues USING (itemnumber)
734 LEFT JOIN borrowers USING (borrowernumber)
735 LEFT JOIN serialitems USING (itemnumber)
736 LEFT JOIN serial USING (serialid)
737 LEFT JOIN itemtypes ON itemtypes.itemtype = "
738 . (C4::Context->preference('item-level_itypes') ? 'items.itype' : 'biblioitems.itemtype');
740 LEFT JOIN tmp_holdsqueue USING (itemnumber)
741 LEFT JOIN localization ON itemtypes.itemtype = localization.code
742 AND localization.entity = 'itemtypes'
743 AND localization.lang = ?
746 $query .= " WHERE items.biblionumber = ? ORDER BY home.branchname, items.enumchron, LPAD( items.copynumber, 8, '0' ), items.dateaccessioned DESC" ;
747 my $sth = $dbh->prepare($query);
748 $sth->execute($language, $biblionumber);
753 my $userenv = C4::Context->userenv;
754 my $want_not_same_branch = C4::Context->preference("IndependentBranches") && !C4::Context->IsSuperLibrarian();
755 while ( my $data = $sth->fetchrow_hashref ) {
756 if ( $data->{borrowernumber} && $want_not_same_branch) {
757 $data->{'NOTSAMEBRANCH'} = $data->{'bcode'} ne $userenv->{branch};
760 $serial ||= $data->{'serial'};
763 # get notforloan complete status if applicable
764 $descriptions = Koha::AuthorisedValues->get_description_by_koha_field({frameworkcode => $data->{frameworkcode}, kohafield => 'items.notforloan', authorised_value => $data->{itemnotforloan} });
765 $data->{notforloanvalue} = $descriptions->{lib} // '';
766 $data->{notforloanvalueopac} = $descriptions->{opac_description} // '';
768 # get restricted status and description if applicable
769 $descriptions = Koha::AuthorisedValues->get_description_by_koha_field({frameworkcode => $data->{frameworkcode}, kohafield => 'items.restricted', authorised_value => $data->{restricted} });
770 $data->{restrictedvalue} = $descriptions->{lib} // '';
771 $data->{restrictedvalueopac} = $descriptions->{opac_description} // '';
773 # my stack procedures
774 $descriptions = Koha::AuthorisedValues->get_description_by_koha_field({frameworkcode => $data->{frameworkcode}, kohafield => 'items.stack', authorised_value => $data->{stack} });
775 $data->{stack} = $descriptions->{lib} // '';
777 # Find the last 3 people who borrowed this item.
778 my $sth2 = $dbh->prepare("SELECT * FROM old_issues,borrowers
780 AND old_issues.borrowernumber = borrowers.borrowernumber
781 ORDER BY returndate DESC
783 $sth2->execute($data->{'itemnumber'});
785 while (my $data2 = $sth2->fetchrow_hashref()) {
786 $data->{"timestamp$ii"} = $data2->{'timestamp'} if $data2->{'timestamp'};
787 $data->{"card$ii"} = $data2->{'cardnumber'} if $data2->{'cardnumber'};
788 $data->{"borrower$ii"} = $data2->{'borrowernumber'} if $data2->{'borrowernumber'};
792 $results[$i] = $data;
797 ? sort { ($b->{'publisheddate'} || $b->{'enumchron'}) cmp ($a->{'publisheddate'} || $a->{'enumchron'}) } @results
801 =head2 GetItemsLocationInfo
803 my @itemlocinfo = GetItemsLocationInfo($biblionumber);
805 Returns the branch names, shelving location and itemcallnumber for each item attached to the biblio in question
807 C<GetItemsInfo> returns a list of references-to-hash. Data returned:
811 =item C<$data-E<gt>{homebranch}>
813 Branch Name of the item's homebranch
815 =item C<$data-E<gt>{holdingbranch}>
817 Branch Name of the item's holdingbranch
819 =item C<$data-E<gt>{location}>
821 Item's shelving location code
823 =item C<$data-E<gt>{location_intranet}>
825 The intranet description for the Shelving Location as set in authorised_values 'LOC'
827 =item C<$data-E<gt>{location_opac}>
829 The OPAC description for the Shelving Location as set in authorised_values 'LOC'. Falls back to intranet description if no OPAC
832 =item C<$data-E<gt>{itemcallnumber}>
834 Item's itemcallnumber
836 =item C<$data-E<gt>{cn_sort}>
838 Item's call number normalized for sorting
844 sub GetItemsLocationInfo {
845 my $biblionumber = shift;
848 my $dbh = C4::Context->dbh;
849 my $query = "SELECT a.branchname as homebranch, b.branchname as holdingbranch,
850 location, itemcallnumber, cn_sort
851 FROM items, branches as a, branches as b
852 WHERE homebranch = a.branchcode AND holdingbranch = b.branchcode
854 ORDER BY cn_sort ASC";
855 my $sth = $dbh->prepare($query);
856 $sth->execute($biblionumber);
858 while ( my $data = $sth->fetchrow_hashref ) {
859 my $av = Koha::AuthorisedValues->search({ category => 'LOC', authorised_value => $data->{location} });
860 $av = $av->count ? $av->next : undef;
861 $data->{location_intranet} = $av ? $av->lib : '';
862 $data->{location_opac} = $av ? $av->opac_description : '';
863 push @results, $data;
868 =head2 GetHostItemsInfo
870 $hostiteminfo = GetHostItemsInfo($hostfield);
871 Returns the iteminfo for items linked to records via a host field
875 sub GetHostItemsInfo {
879 if( !C4::Context->preference('EasyAnalyticalRecords') ) {
880 return @returnitemsInfo;
884 if( C4::Context->preference('marcflavour') eq 'MARC21' ||
885 C4::Context->preference('marcflavour') eq 'NORMARC') {
886 @fields = $record->field('773');
887 } elsif( C4::Context->preference('marcflavour') eq 'UNIMARC') {
888 @fields = $record->field('461');
891 foreach my $hostfield ( @fields ) {
892 my $hostbiblionumber = $hostfield->subfield("0");
893 my $linkeditemnumber = $hostfield->subfield("9");
894 my @hostitemInfos = GetItemsInfo($hostbiblionumber);
895 foreach my $hostitemInfo (@hostitemInfos) {
896 if( $hostitemInfo->{itemnumber} eq $linkeditemnumber ) {
897 push @returnitemsInfo, $hostitemInfo;
902 return @returnitemsInfo;
905 =head2 get_hostitemnumbers_of
907 my @itemnumbers_of = get_hostitemnumbers_of($biblionumber);
909 Given a biblionumber, return the list of corresponding itemnumbers that are linked to it via host fields
911 Return a reference on a hash where key is a biblionumber and values are
912 references on array of itemnumbers.
917 sub get_hostitemnumbers_of {
918 my ($biblionumber) = @_;
920 if( !C4::Context->preference('EasyAnalyticalRecords') ) {
924 my $marcrecord = C4::Biblio::GetMarcBiblio({ biblionumber => $biblionumber });
925 return unless $marcrecord;
927 my ( @returnhostitemnumbers, $tag, $biblio_s, $item_s );
929 my $marcflavor = C4::Context->preference('marcflavour');
930 if ( $marcflavor eq 'MARC21' || $marcflavor eq 'NORMARC' ) {
935 elsif ( $marcflavor eq 'UNIMARC' ) {
941 foreach my $hostfield ( $marcrecord->field($tag) ) {
942 my $hostbiblionumber = $hostfield->subfield($biblio_s);
943 next unless $hostbiblionumber; # have tag, don't have $biblio_s subfield
944 my $linkeditemnumber = $hostfield->subfield($item_s);
945 if ( ! $linkeditemnumber ) {
946 warn "ERROR biblionumber $biblionumber has 773^0, but doesn't have 9";
949 my $is_from_biblio = Koha::Items->search({ itemnumber => $linkeditemnumber, biblionumber => $hostbiblionumber });
950 push @returnhostitemnumbers, $linkeditemnumber
954 return @returnhostitemnumbers;
957 =head2 GetHiddenItemnumbers
959 my @itemnumbers_to_hide = GetHiddenItemnumbers({ items => \@items, borcat => $category });
961 Given a list of items it checks which should be hidden from the OPAC given
962 the current configuration. Returns a list of itemnumbers corresponding to
963 those that should be hidden. Optionally takes a borcat parameter for certain borrower types
968 sub GetHiddenItemnumbers {
970 my $items = $params->{items};
971 if (my $exceptions = C4::Context->preference('OpacHiddenItemsExceptions') and $params->{'borcat'}){
972 foreach my $except (split(/\|/, $exceptions)){
973 if ($params->{'borcat'} eq $except){
974 return; # we don't hide anything for this borrower category
980 my $yaml = C4::Context->preference('OpacHiddenItems');
981 return () if (! $yaml =~ /\S/ );
982 $yaml = "$yaml\n\n"; # YAML is anal on ending \n. Surplus does not hurt
985 $hidingrules = YAML::Load($yaml);
988 warn "Unable to parse OpacHiddenItems syspref : $@";
991 my $dbh = C4::Context->dbh;
994 foreach my $item (@$items) {
997 foreach my $field (keys %$hidingrules) {
999 if (exists $item->{$field}) {
1000 $val = $item->{$field};
1003 my $query = "SELECT $field from items where itemnumber = ?";
1004 $val = $dbh->selectrow_array($query, undef, $item->{'itemnumber'});
1006 $val = '' unless defined $val;
1008 # If the results matches the values in the yaml file
1009 if (any { $val eq $_ } @{$hidingrules->{$field}}) {
1011 # We add the itemnumber to the list
1012 push @resultitems, $item->{'itemnumber'};
1014 # If at least one rule matched for an item, no need to test the others
1019 return @resultitems;
1022 =head1 LIMITED USE FUNCTIONS
1024 The following functions, while part of the public API,
1025 are not exported. This is generally because they are
1026 meant to be used by only one script for a specific
1027 purpose, and should not be used in any other context
1028 without careful thought.
1034 my $item_marc = GetMarcItem($biblionumber, $itemnumber);
1036 Returns MARC::Record of the item passed in parameter.
1037 This function is meant for use only in C<cataloguing/additem.pl>,
1038 where it is needed to support that script's MARC-like
1044 my ( $biblionumber, $itemnumber ) = @_;
1046 # GetMarcItem has been revised so that it does the following:
1047 # 1. Gets the item information from the items table.
1048 # 2. Converts it to a MARC field for storage in the bib record.
1050 # The previous behavior was:
1051 # 1. Get the bib record.
1052 # 2. Return the MARC tag corresponding to the item record.
1054 # The difference is that one treats the items row as authoritative,
1055 # while the other treats the MARC representation as authoritative
1056 # under certain circumstances.
1058 my $item = Koha::Items->find($itemnumber) or return;
1060 # Tack on 'items.' prefix to column names so that C4::Biblio::TransformKohaToMarc will work.
1061 # Also, don't emit a subfield if the underlying field is blank.
1063 return Item2Marc($item->unblessed, $biblionumber);
1067 my ($itemrecord,$biblionumber)=@_;
1070 defined($itemrecord->{$_}) && $itemrecord->{$_} ne '' ? ("items.$_" => $itemrecord->{$_}) : ()
1071 } keys %{ $itemrecord }
1073 my $framework = C4::Biblio::GetFrameworkCode( $biblionumber );
1074 my $itemmarc = C4::Biblio::TransformKohaToMarc( $mungeditem, { framework => $framework } );
1075 my ( $itemtag, $itemsubfield ) = C4::Biblio::GetMarcFromKohaField(
1076 "items.itemnumber", $framework,
1079 my $unlinked_item_subfields = _parse_unlinked_item_subfields_from_xml($mungeditem->{'items.more_subfields_xml'});
1080 if (defined $unlinked_item_subfields and $#$unlinked_item_subfields > -1) {
1081 foreach my $field ($itemmarc->field($itemtag)){
1082 $field->add_subfields(@$unlinked_item_subfields);
1088 =head1 PRIVATE FUNCTIONS AND VARIABLES
1090 The following functions are not meant to be called
1091 directly, but are documented in order to explain
1092 the inner workings of C<C4::Items>.
1096 =head2 MoveItemFromBiblio
1098 MoveItemFromBiblio($itenumber, $frombiblio, $tobiblio);
1100 Moves an item from a biblio to another
1102 Returns undef if the move failed or the biblionumber of the destination record otherwise
1106 sub MoveItemFromBiblio {
1107 my ($itemnumber, $frombiblio, $tobiblio) = @_;
1108 my $dbh = C4::Context->dbh;
1109 my ( $tobiblioitem ) = $dbh->selectrow_array(q|
1110 SELECT biblioitemnumber
1112 WHERE biblionumber = ?
1113 |, undef, $tobiblio );
1114 my $return = $dbh->do(q|
1116 SET biblioitemnumber = ?,
1118 WHERE itemnumber = ?
1119 AND biblionumber = ?
1120 |, undef, $tobiblioitem, $tobiblio, $itemnumber, $frombiblio );
1122 my $indexer = Koha::SearchEngine::Indexer->new({ index => $Koha::SearchEngine::BIBLIOS_INDEX });
1123 $indexer->index_records( $tobiblio, "specialUpdate", "biblioserver" );
1124 $indexer->index_records( $frombiblio, "specialUpdate", "biblioserver" );
1125 # Checking if the item we want to move is in an order
1126 require C4::Acquisition;
1127 my $order = C4::Acquisition::GetOrderFromItemnumber($itemnumber);
1129 # Replacing the biblionumber within the order if necessary
1130 $order->{'biblionumber'} = $tobiblio;
1131 C4::Acquisition::ModOrder($order);
1134 # Update reserves, hold_fill_targets, tmp_holdsqueue and linktracker tables
1135 for my $table_name ( qw( reserves hold_fill_targets tmp_holdsqueue linktracker ) ) {
1138 SET biblionumber = ?
1139 WHERE itemnumber = ?
1140 |, undef, $tobiblio, $itemnumber );
1147 =head2 _marc_from_item_hash
1149 my $item_marc = _marc_from_item_hash($item, $frameworkcode[, $unlinked_item_subfields]);
1151 Given an item hash representing a complete item record,
1152 create a C<MARC::Record> object containing an embedded
1153 tag representing that item.
1155 The third, optional parameter C<$unlinked_item_subfields> is
1156 an arrayref of subfields (not mapped to C<items> fields per the
1157 framework) to be added to the MARC representation
1162 sub _marc_from_item_hash {
1164 my $frameworkcode = shift;
1165 my $unlinked_item_subfields;
1167 $unlinked_item_subfields = shift;
1170 # Tack on 'items.' prefix to column names so lookup from MARC frameworks will work
1171 # Also, don't emit a subfield if the underlying field is blank.
1172 my $mungeditem = { map { (defined($item->{$_}) and $item->{$_} ne '') ?
1173 (/^items\./ ? ($_ => $item->{$_}) : ("items.$_" => $item->{$_}))
1174 : () } keys %{ $item } };
1176 my $item_marc = MARC::Record->new();
1177 foreach my $item_field ( keys %{$mungeditem} ) {
1178 my ( $tag, $subfield ) = C4::Biblio::GetMarcFromKohaField( $item_field );
1179 next unless defined $tag and defined $subfield; # skip if not mapped to MARC field
1180 my @values = split(/\s?\|\s?/, $mungeditem->{$item_field}, -1);
1181 foreach my $value (@values){
1182 if ( my $field = $item_marc->field($tag) ) {
1183 $field->add_subfields( $subfield => $value );
1185 my $add_subfields = [];
1186 if (defined $unlinked_item_subfields and ref($unlinked_item_subfields) eq 'ARRAY' and $#$unlinked_item_subfields > -1) {
1187 $add_subfields = $unlinked_item_subfields;
1189 $item_marc->add_fields( $tag, " ", " ", $subfield => $value, @$add_subfields );
1197 =head2 _repack_item_errors
1199 Add an error message hash generated by C<CheckItemPreSave>
1200 to a list of errors.
1204 sub _repack_item_errors {
1205 my $item_sequence_num = shift;
1206 my $item_ref = shift;
1207 my $error_ref = shift;
1209 my @repacked_errors = ();
1211 foreach my $error_code (sort keys %{ $error_ref }) {
1212 my $repacked_error = {};
1213 $repacked_error->{'item_sequence'} = $item_sequence_num;
1214 $repacked_error->{'item_barcode'} = exists($item_ref->{'barcode'}) ? $item_ref->{'barcode'} : '';
1215 $repacked_error->{'error_code'} = $error_code;
1216 $repacked_error->{'error_information'} = $error_ref->{$error_code};
1217 push @repacked_errors, $repacked_error;
1220 return @repacked_errors;
1223 =head2 _get_unlinked_item_subfields
1225 my $unlinked_item_subfields = _get_unlinked_item_subfields($original_item_marc, $frameworkcode);
1229 sub _get_unlinked_item_subfields {
1230 my $original_item_marc = shift;
1231 my $frameworkcode = shift;
1233 my $marcstructure = C4::Biblio::GetMarcStructure(1, $frameworkcode, { unsafe => 1 });
1235 # assume that this record has only one field, and that that
1236 # field contains only the item information
1238 my @fields = $original_item_marc->fields();
1239 if ($#fields > -1) {
1240 my $field = $fields[0];
1241 my $tag = $field->tag();
1242 foreach my $subfield ($field->subfields()) {
1243 if (defined $subfield->[1] and
1244 $subfield->[1] ne '' and
1245 !$marcstructure->{$tag}->{$subfield->[0]}->{'kohafield'}) {
1246 push @$subfields, $subfield->[0] => $subfield->[1];
1253 =head2 _get_unlinked_subfields_xml
1255 my $unlinked_subfields_xml = _get_unlinked_subfields_xml($unlinked_item_subfields);
1259 sub _get_unlinked_subfields_xml {
1260 my $unlinked_item_subfields = shift;
1263 if (defined $unlinked_item_subfields and ref($unlinked_item_subfields) eq 'ARRAY' and $#$unlinked_item_subfields > -1) {
1264 my $marc = MARC::Record->new();
1265 # use of tag 999 is arbitrary, and doesn't need to match the item tag
1266 # used in the framework
1267 $marc->append_fields(MARC::Field->new('999', ' ', ' ', @$unlinked_item_subfields));
1268 $marc->encoding("UTF-8");
1269 $xml = $marc->as_xml("USMARC");
1275 =head2 _parse_unlinked_item_subfields_from_xml
1277 my $unlinked_item_subfields = _parse_unlinked_item_subfields_from_xml($whole_item->{'more_subfields_xml'}):
1281 sub _parse_unlinked_item_subfields_from_xml {
1283 require C4::Charset;
1284 return unless defined $xml and $xml ne "";
1285 my $marc = MARC::Record->new_from_xml(C4::Charset::StripNonXmlChars($xml),'UTF-8');
1286 my $unlinked_subfields = [];
1287 my @fields = $marc->fields();
1288 if ($#fields > -1) {
1289 foreach my $subfield ($fields[0]->subfields()) {
1290 push @$unlinked_subfields, $subfield->[0] => $subfield->[1];
1293 return $unlinked_subfields;
1296 =head2 GetAnalyticsCount
1298 $count= &GetAnalyticsCount($itemnumber)
1300 counts Usage of itemnumber in Analytical bibliorecords.
1304 sub GetAnalyticsCount {
1305 my ($itemnumber) = @_;
1307 ### ZOOM search here
1309 $query= "hi=".$itemnumber;
1310 my $searcher = Koha::SearchEngine::Search->new({index => $Koha::SearchEngine::BIBLIOS_INDEX});
1311 my ($err,$res,$result) = $searcher->simple_search_compat($query,0,10);
1315 sub _SearchItems_build_where_fragment {
1318 my $dbh = C4::Context->dbh;
1321 if (exists($filter->{conjunction})) {
1322 my (@where_strs, @where_args);
1323 foreach my $f (@{ $filter->{filters} }) {
1324 my $fragment = _SearchItems_build_where_fragment($f);
1326 push @where_strs, $fragment->{str};
1327 push @where_args, @{ $fragment->{args} };
1332 $where_str = '(' . join (' ' . $filter->{conjunction} . ' ', @where_strs) . ')';
1335 args => \@where_args,
1339 my @columns = Koha::Database->new()->schema()->resultset('Item')->result_source->columns;
1340 push @columns, Koha::Database->new()->schema()->resultset('Biblio')->result_source->columns;
1341 push @columns, Koha::Database->new()->schema()->resultset('Biblioitem')->result_source->columns;
1342 my @operators = qw(= != > < >= <= like);
1343 push @operators, 'not like';
1344 my $field = $filter->{field} // q{};
1345 if ( (0 < grep { $_ eq $field } @columns) or (substr($field, 0, 5) eq 'marc:') ) {
1346 my $op = $filter->{operator};
1347 my $query = $filter->{query};
1348 my $ifnull = $filter->{ifnull};
1350 if (!$op or (0 == grep { $_ eq $op } @operators)) {
1351 $op = '='; # default operator
1355 if ($field =~ /^marc:(\d{3})(?:\$(\w))?$/) {
1357 my $marcsubfield = $2;
1358 my ($kohafield) = $dbh->selectrow_array(q|
1359 SELECT kohafield FROM marc_subfield_structure
1360 WHERE tagfield=? AND tagsubfield=? AND frameworkcode=''
1361 |, undef, $marcfield, $marcsubfield);
1364 $column = $kohafield;
1366 # MARC field is not linked to a DB field so we need to use
1367 # ExtractValue on marcxml from biblio_metadata or
1368 # items.more_subfields_xml, depending on the MARC field.
1371 my ($itemfield) = C4::Biblio::GetMarcFromKohaField('items.itemnumber');
1372 if ($marcfield eq $itemfield) {
1373 $sqlfield = 'more_subfields_xml';
1374 $xpath = '//record/datafield/subfield[@code="' . $marcsubfield . '"]';
1376 $sqlfield = 'metadata'; # From biblio_metadata
1377 if ($marcfield < 10) {
1378 $xpath = "//record/controlfield[\@tag=\"$marcfield\"]";
1380 $xpath = "//record/datafield[\@tag=\"$marcfield\"]/subfield[\@code=\"$marcsubfield\"]";
1383 $column = "ExtractValue($sqlfield, '$xpath')";
1389 if ( defined $ifnull ) {
1390 $column = "COALESCE($column, ?)";
1393 if (ref $query eq 'ARRAY') {
1396 } elsif ($op eq '!=') {
1400 str => "$column $op (" . join (',', ('?') x @$query) . ")",
1405 str => "$column $op ?",
1410 if ( defined $ifnull ) {
1411 unshift @{ $where_fragment->{args} }, $ifnull;
1416 return $where_fragment;
1421 my ($items, $total) = SearchItems($filter, $params);
1423 Perform a search among items
1425 $filter is a reference to a hash which can be a filter, or a combination of filters.
1427 A filter has the following keys:
1431 =item * field: the name of a SQL column in table items
1433 =item * query: the value to search in this column
1435 =item * operator: comparison operator. Can be one of = != > < >= <= like 'not like'
1439 A combination of filters hash the following keys:
1443 =item * conjunction: 'AND' or 'OR'
1445 =item * filters: array ref of filters
1449 $params is a reference to a hash that can contain the following parameters:
1453 =item * rows: Number of items to return. 0 returns everything (default: 0)
1455 =item * page: Page to return (return items from (page-1)*rows to (page*rows)-1)
1458 =item * sortby: A SQL column name in items table to sort on
1460 =item * sortorder: 'ASC' or 'DESC'
1467 my ($filter, $params) = @_;
1471 return unless ref $filter eq 'HASH';
1472 return unless ref $params eq 'HASH';
1474 # Default parameters
1475 $params->{rows} ||= 0;
1476 $params->{page} ||= 1;
1477 $params->{sortby} ||= 'itemnumber';
1478 $params->{sortorder} ||= 'ASC';
1480 my ($where_str, @where_args);
1481 my $where_fragment = _SearchItems_build_where_fragment($filter);
1482 if ($where_fragment) {
1483 $where_str = $where_fragment->{str};
1484 @where_args = @{ $where_fragment->{args} };
1487 my $dbh = C4::Context->dbh;
1489 SELECT SQL_CALC_FOUND_ROWS items.*
1491 LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber
1492 LEFT JOIN biblioitems ON biblioitems.biblioitemnumber = items.biblioitemnumber
1493 LEFT JOIN biblio_metadata ON biblio_metadata.biblionumber = biblio.biblionumber
1496 if (defined $where_str and $where_str ne '') {
1497 $query .= qq{ AND $where_str };
1500 $query .= q{ AND biblio_metadata.format = 'marcxml' AND biblio_metadata.schema = ? };
1501 push @where_args, C4::Context->preference('marcflavour');
1503 my @columns = Koha::Database->new()->schema()->resultset('Item')->result_source->columns;
1504 push @columns, Koha::Database->new()->schema()->resultset('Biblio')->result_source->columns;
1505 push @columns, Koha::Database->new()->schema()->resultset('Biblioitem')->result_source->columns;
1506 my $sortby = (0 < grep {$params->{sortby} eq $_} @columns)
1507 ? $params->{sortby} : 'itemnumber';
1508 my $sortorder = (uc($params->{sortorder}) eq 'ASC') ? 'ASC' : 'DESC';
1509 $query .= qq{ ORDER BY $sortby $sortorder };
1511 my $rows = $params->{rows};
1514 my $offset = $rows * ($params->{page}-1);
1515 $query .= qq { LIMIT ?, ? };
1516 push @limit_args, $offset, $rows;
1519 my $sth = $dbh->prepare($query);
1520 my $rv = $sth->execute(@where_args, @limit_args);
1522 return unless ($rv);
1523 my ($total_rows) = $dbh->selectrow_array(q{ SELECT FOUND_ROWS() });
1525 return ($sth->fetchall_arrayref({}), $total_rows);
1529 =head1 OTHER FUNCTIONS
1533 ($indicators, $value) = _find_value($tag, $subfield, $record,$encoding);
1535 Find the given $subfield in the given $tag in the given
1536 MARC::Record $record. If the subfield is found, returns
1537 the (indicators, value) pair; otherwise, (undef, undef) is
1541 Such a function is used in addbiblio AND additem and serial-edit and maybe could be used in Authorities.
1542 I suggest we export it from this module.
1547 my ( $tagfield, $insubfield, $record, $encoding ) = @_;
1550 if ( $tagfield < 10 ) {
1551 if ( $record->field($tagfield) ) {
1552 push @result, $record->field($tagfield)->data();
1557 foreach my $field ( $record->field($tagfield) ) {
1558 my @subfields = $field->subfields();
1559 foreach my $subfield (@subfields) {
1560 if ( @$subfield[0] eq $insubfield ) {
1561 push @result, @$subfield[1];
1562 $indicator = $field->indicator(1) . $field->indicator(2);
1567 return ( $indicator, @result );
1571 =head2 PrepareItemrecordDisplay
1573 PrepareItemrecordDisplay($itemrecord,$bibnum,$itemumber,$frameworkcode);
1575 Returns a hash with all the fields for Display a given item data in a template
1577 The $frameworkcode returns the item for the given frameworkcode, ONLY if bibnum is not provided
1581 sub PrepareItemrecordDisplay {
1583 my ( $bibnum, $itemnum, $defaultvalues, $frameworkcode ) = @_;
1585 my $dbh = C4::Context->dbh;
1586 $frameworkcode = C4::Biblio::GetFrameworkCode($bibnum) if $bibnum;
1587 my ( $itemtagfield, $itemtagsubfield ) = C4::Biblio::GetMarcFromKohaField( "items.itemnumber" );
1589 # Note: $tagslib obtained from GetMarcStructure() in 'unsafe' mode is
1590 # a shared data structure. No plugin (including custom ones) should change
1591 # its contents. See also GetMarcStructure.
1592 my $tagslib = GetMarcStructure( 1, $frameworkcode, { unsafe => 1 } );
1594 # return nothing if we don't have found an existing framework.
1595 return q{} unless $tagslib;
1598 $itemrecord = C4::Items::GetMarcItem( $bibnum, $itemnum );
1602 my $branch_limit = C4::Context->userenv ? C4::Context->userenv->{"branch"} : "";
1604 SELECT authorised_value,lib FROM authorised_values
1607 LEFT JOIN authorised_values_branches ON ( id = av_id )
1612 $query .= qq{ AND ( branchcode = ? OR branchcode IS NULL )} if $branch_limit;
1613 $query .= qq{ ORDER BY lib};
1614 my $authorised_values_sth = $dbh->prepare( $query );
1615 foreach my $tag ( sort keys %{$tagslib} ) {
1618 # loop through each subfield
1620 foreach my $subfield ( sort keys %{ $tagslib->{$tag} } ) {
1621 next if IsMarcStructureInternal($tagslib->{$tag}{$subfield});
1622 next unless ( $tagslib->{$tag}->{$subfield}->{'tab'} );
1623 next if ( $tagslib->{$tag}->{$subfield}->{'tab'} ne "10" );
1625 $subfield_data{tag} = $tag;
1626 $subfield_data{subfield} = $subfield;
1627 $subfield_data{countsubfield} = $cntsubf++;
1628 $subfield_data{kohafield} = $tagslib->{$tag}->{$subfield}->{'kohafield'};
1629 $subfield_data{id} = "tag_".$tag."_subfield_".$subfield."_".int(rand(1000000));
1631 # $subfield_data{marc_lib}=$tagslib->{$tag}->{$subfield}->{lib};
1632 $subfield_data{marc_lib} = $tagslib->{$tag}->{$subfield}->{lib};
1633 $subfield_data{mandatory} = $tagslib->{$tag}->{$subfield}->{mandatory};
1634 $subfield_data{repeatable} = $tagslib->{$tag}->{$subfield}->{repeatable};
1635 $subfield_data{hidden} = "display:none"
1636 if ( ( $tagslib->{$tag}->{$subfield}->{hidden} > 4 )
1637 || ( $tagslib->{$tag}->{$subfield}->{hidden} < -4 ) );
1638 my ( $x, $defaultvalue );
1640 ( $x, $defaultvalue ) = _find_value( $tag, $subfield, $itemrecord );
1642 $defaultvalue = $tagslib->{$tag}->{$subfield}->{defaultvalue} unless $defaultvalue;
1643 if ( !defined $defaultvalue ) {
1644 $defaultvalue = q||;
1646 $defaultvalue =~ s/"/"/g;
1647 # get today date & replace <<YYYY>>, <<MM>>, <<DD>> if provided in the default value
1648 my $today_dt = dt_from_string;
1649 my $year = $today_dt->strftime('%Y');
1650 my $shortyear = $today_dt->strftime('%y');
1651 my $month = $today_dt->strftime('%m');
1652 my $day = $today_dt->strftime('%d');
1653 $defaultvalue =~ s/<<YYYY>>/$year/g;
1654 $defaultvalue =~ s/<<YY>>/$shortyear/g;
1655 $defaultvalue =~ s/<<MM>>/$month/g;
1656 $defaultvalue =~ s/<<DD>>/$day/g;
1658 # And <<USER>> with surname (?)
1660 ( C4::Context->userenv
1661 ? C4::Context->userenv->{'surname'}
1662 : "superlibrarian" );
1663 $defaultvalue =~ s/<<USER>>/$username/g;
1666 my $maxlength = $tagslib->{$tag}->{$subfield}->{maxlength};
1668 # search for itemcallnumber if applicable
1669 if ( $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.itemcallnumber'
1670 && C4::Context->preference('itemcallnumber') && $itemrecord) {
1671 foreach my $itemcn_pref (split(/,/,C4::Context->preference('itemcallnumber'))){
1672 my $CNtag = substr( $itemcn_pref, 0, 3 );
1673 next unless my $field = $itemrecord->field($CNtag);
1674 my $CNsubfields = substr( $itemcn_pref, 3 );
1675 $defaultvalue = $field->as_string( $CNsubfields, ' ');
1676 last if $defaultvalue;
1679 if ( $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.itemcallnumber'
1681 && $defaultvalues->{'callnumber'} ) {
1682 if( $itemrecord and $defaultvalues and not $itemrecord->subfield($tag,$subfield) ){
1683 # if the item record exists, only use default value if the item has no callnumber
1684 $defaultvalue = $defaultvalues->{callnumber};
1685 } elsif ( !$itemrecord and $defaultvalues ) {
1686 # if the item record *doesn't* exists, always use the default value
1687 $defaultvalue = $defaultvalues->{callnumber};
1690 if ( ( $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.holdingbranch' || $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.homebranch' )
1692 && $defaultvalues->{'branchcode'} ) {
1693 if ( $itemrecord and $defaultvalues and not $itemrecord->subfield($tag,$subfield) ) {
1694 $defaultvalue = $defaultvalues->{branchcode};
1697 if ( ( $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.location' )
1699 && $defaultvalues->{'location'} ) {
1701 if ( $itemrecord and $defaultvalues and not $itemrecord->subfield($tag,$subfield) ) {
1702 # if the item record exists, only use default value if the item has no locationr
1703 $defaultvalue = $defaultvalues->{location};
1704 } elsif ( !$itemrecord and $defaultvalues ) {
1705 # if the item record *doesn't* exists, always use the default value
1706 $defaultvalue = $defaultvalues->{location};
1709 if ( $tagslib->{$tag}->{$subfield}->{authorised_value} ) {
1710 my @authorised_values;
1713 # builds list, depending on authorised value...
1715 if ( $tagslib->{$tag}->{$subfield}->{'authorised_value'} eq "branches" ) {
1716 if ( ( C4::Context->preference("IndependentBranches") )
1717 && !C4::Context->IsSuperLibrarian() ) {
1718 my $sth = $dbh->prepare( "SELECT branchcode,branchname FROM branches WHERE branchcode = ? ORDER BY branchname" );
1719 $sth->execute( C4::Context->userenv->{branch} );
1720 push @authorised_values, ""
1721 unless ( $tagslib->{$tag}->{$subfield}->{mandatory} );
1722 while ( my ( $branchcode, $branchname ) = $sth->fetchrow_array ) {
1723 push @authorised_values, $branchcode;
1724 $authorised_lib{$branchcode} = $branchname;
1727 my $sth = $dbh->prepare( "SELECT branchcode,branchname FROM branches ORDER BY branchname" );
1729 push @authorised_values, ""
1730 unless ( $tagslib->{$tag}->{$subfield}->{mandatory} );
1731 while ( my ( $branchcode, $branchname ) = $sth->fetchrow_array ) {
1732 push @authorised_values, $branchcode;
1733 $authorised_lib{$branchcode} = $branchname;
1737 $defaultvalue = C4::Context->userenv ? C4::Context->userenv->{branch} : undef;
1738 if ( $defaultvalues and $defaultvalues->{branchcode} ) {
1739 $defaultvalue = $defaultvalues->{branchcode};
1743 } elsif ( $tagslib->{$tag}->{$subfield}->{authorised_value} eq "itemtypes" ) {
1744 my $itemtypes = Koha::ItemTypes->search_with_localization;
1745 push @authorised_values, "";
1746 while ( my $itemtype = $itemtypes->next ) {
1747 push @authorised_values, $itemtype->itemtype;
1748 $authorised_lib{$itemtype->itemtype} = $itemtype->translated_description;
1750 if ($defaultvalues && $defaultvalues->{'itemtype'}) {
1751 $defaultvalue = $defaultvalues->{'itemtype'};
1755 } elsif ( $tagslib->{$tag}->{$subfield}->{authorised_value} eq "cn_source" ) {
1756 push @authorised_values, "";
1758 my $class_sources = GetClassSources();
1759 my $default_source = $defaultvalue || C4::Context->preference("DefaultClassificationSource");
1761 foreach my $class_source (sort keys %$class_sources) {
1762 next unless $class_sources->{$class_source}->{'used'} or
1763 ($class_source eq $default_source);
1764 push @authorised_values, $class_source;
1765 $authorised_lib{$class_source} = $class_sources->{$class_source}->{'description'};
1768 $defaultvalue = $default_source;
1770 #---- "true" authorised value
1772 $authorised_values_sth->execute(
1773 $tagslib->{$tag}->{$subfield}->{authorised_value},
1774 $branch_limit ? $branch_limit : ()
1776 push @authorised_values, "";
1777 while ( my ( $value, $lib ) = $authorised_values_sth->fetchrow_array ) {
1778 push @authorised_values, $value;
1779 $authorised_lib{$value} = $lib;
1782 $subfield_data{marc_value} = {
1784 values => \@authorised_values,
1785 default => $defaultvalue // q{},
1786 labels => \%authorised_lib,
1788 } elsif ( $tagslib->{$tag}->{$subfield}->{value_builder} ) {
1790 require Koha::FrameworkPlugin;
1791 my $plugin = Koha::FrameworkPlugin->new({
1792 name => $tagslib->{$tag}->{$subfield}->{value_builder},
1795 my $pars = { dbh => $dbh, record => undef, tagslib =>$tagslib, id => $subfield_data{id}, tabloop => undef };
1796 $plugin->build( $pars );
1797 if ( $itemrecord and my $field = $itemrecord->field($tag) ) {
1798 $defaultvalue = $field->subfield($subfield) || q{};
1800 if( !$plugin->errstr ) {
1801 #TODO Move html to template; see report 12176/13397
1802 my $tab= $plugin->noclick? '-1': '';
1803 my $class= $plugin->noclick? ' disabled': '';
1804 my $title= $plugin->noclick? 'No popup': 'Tag editor';
1805 $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;
1807 warn $plugin->errstr;
1808 $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
1811 elsif ( $tag eq '' ) { # it's an hidden field
1812 $subfield_data{marc_value} = qq(<input type="hidden" id="$subfield_data{id}" name="field_value" class="input_marceditor" size="50" maxlength="$maxlength" value="$defaultvalue" />);
1814 elsif ( $tagslib->{$tag}->{$subfield}->{'hidden'} ) { # FIXME: shouldn't input type be "hidden" ?
1815 $subfield_data{marc_value} = qq(<input type="text" id="$subfield_data{id}" name="field_value" class="input_marceditor" size="50" maxlength="$maxlength" value="$defaultvalue" />);
1817 elsif ( length($defaultvalue) > 100
1818 or (C4::Context->preference("marcflavour") eq "UNIMARC" and
1819 300 <= $tag && $tag < 400 && $subfield eq 'a' )
1820 or (C4::Context->preference("marcflavour") eq "MARC21" and
1821 500 <= $tag && $tag < 600 )
1823 # oversize field (textarea)
1824 $subfield_data{marc_value} = qq(<textarea id="$subfield_data{id}" name="field_value" class="input_marceditor" size="50" maxlength="$maxlength">$defaultvalue</textarea>\n");
1826 $subfield_data{marc_value} = qq(<input type="text" id="$subfield_data{id}" name="field_value" class="input_marceditor" size="50" maxlength="$maxlength" value="$defaultvalue" />);
1828 push( @loop_data, \%subfield_data );
1833 if ( $itemrecord && $itemrecord->field($itemtagfield) ) {
1834 $itemnumber = $itemrecord->subfield( $itemtagfield, $itemtagsubfield );
1837 'itemtagfield' => $itemtagfield,
1838 'itemtagsubfield' => $itemtagsubfield,
1839 'itemnumber' => $itemnumber,
1840 'iteminformation' => \@loop_data
1844 sub ToggleNewStatus {
1845 my ( $params ) = @_;
1846 my @rules = @{ $params->{rules} };
1847 my $report_only = $params->{report_only};
1849 my $dbh = C4::Context->dbh;
1851 my @item_columns = map { "items.$_" } Koha::Items->columns;
1852 my @biblioitem_columns = map { "biblioitems.$_" } Koha::Biblioitems->columns;
1854 for my $rule ( @rules ) {
1855 my $age = $rule->{age};
1856 my $conditions = $rule->{conditions};
1857 my $substitutions = $rule->{substitutions};
1858 foreach ( @$substitutions ) {
1859 ( $_->{item_field} ) = ( $_->{field} =~ /items\.(.*)/ );
1866 LEFT JOIN biblioitems ON biblioitems.biblionumber = items.biblionumber
1869 for my $condition ( @$conditions ) {
1871 grep { $_ eq $condition->{field} } @item_columns
1872 or grep { $_ eq $condition->{field} } @biblioitem_columns
1874 if ( $condition->{value} =~ /\|/ ) {
1875 my @values = split /\|/, $condition->{value};
1876 $query .= qq| AND $condition->{field} IN (|
1877 . join( ',', ('?') x scalar @values )
1879 push @params, @values;
1881 $query .= qq| AND $condition->{field} = ?|;
1882 push @params, $condition->{value};
1886 if ( defined $age ) {
1887 $query .= q| AND TO_DAYS(NOW()) - TO_DAYS(dateaccessioned) >= ? |;
1890 my $sth = $dbh->prepare($query);
1891 $sth->execute( @params );
1892 while ( my $values = $sth->fetchrow_hashref ) {
1893 my $biblionumber = $values->{biblionumber};
1894 my $itemnumber = $values->{itemnumber};
1895 my $item = Koha::Items->find($itemnumber);
1896 for my $substitution ( @$substitutions ) {
1897 my $field = $substitution->{item_field};
1898 my $value = $substitution->{value};
1899 next unless $substitution->{field};
1900 next if ( defined $values->{ $substitution->{item_field} } and $values->{ $substitution->{item_field} } eq $substitution->{value} );
1901 $item->$field($value);
1902 push @{ $report->{$itemnumber} }, $substitution;
1904 $item->store unless $report_only;