Bug 24963: Terminology: Use "auto-renewal" instead of alternatives
[koha.git] / C4 / Items.pm
bloba162ac5dec163bd3de6cec3cade76d033744eacb
1 package C4::Items;
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>.
21 use Modern::Perl;
23 use vars qw(@ISA @EXPORT);
24 BEGIN {
25 require Exporter;
26 @ISA = qw(Exporter);
28 @EXPORT = qw(
29 AddItemFromMarc
30 AddItemBatchFromMarc
31 ModItemFromMarc
32 Item2Marc
33 ModDateLastSeen
34 ModItemTransfer
35 CheckItemPreSave
36 GetItemsForInventory
37 GetItemsInfo
38 GetItemsLocationInfo
39 GetHostItemsInfo
40 get_hostitemnumbers_of
41 GetHiddenItemnumbers
42 MoveItemFromBiblio
43 CartToShelf
44 GetAnalyticsCount
45 SearchItems
46 PrepareItemrecordDisplay
50 use Carp;
51 use Try::Tiny;
52 use C4::Context;
53 use C4::Koha;
54 use C4::Biblio;
55 use Koha::DateUtils;
56 use MARC::Record;
57 use C4::ClassSource;
58 use C4::Log;
59 use List::MoreUtils qw(any);
60 use YAML qw(Load);
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);
67 use Koha::Database;
69 use Koha::Biblioitems;
70 use Koha::Items;
71 use Koha::ItemTypes;
72 use Koha::SearchEngine;
73 use Koha::SearchEngine::Search;
74 use Koha::Libraries;
76 =head1 NAME
78 C4::Items - item management functions
80 =head1 DESCRIPTION
82 This module contains an API for manipulating item
83 records in Koha, and is used by cataloguing, circulation,
84 acquisitions, and serials management.
86 # FIXME This POD is not up-to-date
87 A Koha item record is stored in two places: the
88 items table and embedded in a MARC tag in the XML
89 version of the associated bib record in C<biblioitems.marcxml>.
90 This is done to allow the item information to be readily
91 indexed (e.g., by Zebra), but means that each item
92 modification transaction must keep the items table
93 and the MARC XML in sync at all times.
95 The items table will be considered authoritative. In other
96 words, if there is ever a discrepancy between the items
97 table and the MARC XML, the items table should be considered
98 accurate.
100 =head1 HISTORICAL NOTE
102 Most of the functions in C<C4::Items> were originally in
103 the C<C4::Biblio> module.
105 =head1 CORE EXPORTED FUNCTIONS
107 The following functions are meant for use by users
108 of C<C4::Items>
110 =cut
112 =head2 CartToShelf
114 CartToShelf($itemnumber);
116 Set the current shelving location of the item record
117 to its stored permanent shelving location. This is
118 primarily used to indicate when an item whose current
119 location is a special processing ('PROC') or shelving cart
120 ('CART') location is back in the stacks.
122 =cut
124 sub CartToShelf {
125 my ( $itemnumber ) = @_;
127 unless ( $itemnumber ) {
128 croak "FAILED CartToShelf() - no itemnumber supplied";
131 my $item = Koha::Items->find($itemnumber);
132 if ( $item->location eq 'CART' ) {
133 $item->location($item->permanent_location)->store;
137 =head2 AddItemFromMarc
139 my ($biblionumber, $biblioitemnumber, $itemnumber)
140 = AddItemFromMarc($source_item_marc, $biblionumber[, $params]);
142 Given a MARC::Record object containing an embedded item
143 record and a biblionumber, create a new item record.
145 The final optional parameter, C<$params>, expected to contain
146 'skip_modzebra_update' key, which relayed down to Koha::Item/store,
147 there it prevents calling of ModZebra (and Elasticsearch update),
148 which takes most of the time in batch adds/deletes: ModZebra better
149 to be called later in C<additem.pl> after the whole loop.
151 $params:
152 skip_modzebra_update => 1|0
154 =cut
156 sub AddItemFromMarc {
157 my $source_item_marc = shift;
158 my $biblionumber = shift;
159 my $params = @_ ? shift : {};
161 my $dbh = C4::Context->dbh;
163 # parse item hash from MARC
164 my $frameworkcode = C4::Biblio::GetFrameworkCode($biblionumber);
165 my ( $itemtag, $itemsubfield ) = C4::Biblio::GetMarcFromKohaField( "items.itemnumber" );
167 my $localitemmarc = MARC::Record->new;
168 $localitemmarc->append_fields( $source_item_marc->field($itemtag) );
170 my $item_values = C4::Biblio::TransformMarcToKoha( $localitemmarc, $frameworkcode, 'items' );
171 my $unlinked_item_subfields = _get_unlinked_item_subfields( $localitemmarc, $frameworkcode );
172 $item_values->{more_subfields_xml} = _get_unlinked_subfields_xml($unlinked_item_subfields);
173 $item_values->{biblionumber} = $biblionumber;
174 $item_values->{cn_source} = delete $item_values->{'items.cn_source'}; # Because of C4::Biblio::_disambiguate
175 $item_values->{cn_sort} = delete $item_values->{'items.cn_sort'}; # Because of C4::Biblio::_disambiguate
176 my $item = Koha::Item->new( $item_values )->store({ skip_modzebra_update => $params->{skip_modzebra_update} });
177 return ( $item->biblionumber, $item->biblioitemnumber, $item->itemnumber );
180 =head2 AddItemBatchFromMarc
182 ($itemnumber_ref, $error_ref) = AddItemBatchFromMarc($record,
183 $biblionumber, $biblioitemnumber, $frameworkcode);
185 Efficiently create item records from a MARC biblio record with
186 embedded item fields. This routine is suitable for batch jobs.
188 This API assumes that the bib record has already been
189 saved to the C<biblio> and C<biblioitems> tables. It does
190 not expect that C<biblio_metadata.metadata> is populated, but it
191 will do so via a call to ModBibiloMarc.
193 The goal of this API is to have a similar effect to using AddBiblio
194 and AddItems in succession, but without inefficient repeated
195 parsing of the MARC XML bib record.
197 This function returns an arrayref of new itemsnumbers and an arrayref of item
198 errors encountered during the processing. Each entry in the errors
199 list is a hashref containing the following keys:
201 =over
203 =item item_sequence
205 Sequence number of original item tag in the MARC record.
207 =item item_barcode
209 Item barcode, provide to assist in the construction of
210 useful error messages.
212 =item error_code
214 Code representing the error condition. Can be 'duplicate_barcode',
215 'invalid_homebranch', or 'invalid_holdingbranch'.
217 =item error_information
219 Additional information appropriate to the error condition.
221 =back
223 =cut
225 sub AddItemBatchFromMarc {
226 my ($record, $biblionumber, $biblioitemnumber, $frameworkcode) = @_;
227 my $error;
228 my @itemnumbers = ();
229 my @errors = ();
230 my $dbh = C4::Context->dbh;
232 # We modify the record, so lets work on a clone so we don't change the
233 # original.
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);
259 if (%item_errors) {
260 push @errors, _repack_item_errors($item_sequence_num, $item, \%item_errors);
261 push @bad_item_fields, $item_field;
262 next ITEMFIELD;
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 # update the MARC biblio
280 # $biblionumber = ModBiblioMarc( $record, $biblionumber, $frameworkcode );
282 return (\@itemnumbers, \@errors);
285 sub ModItemFromMarc {
286 my $item_marc = shift;
287 my $biblionumber = shift;
288 my $itemnumber = shift;
290 my $frameworkcode = C4::Biblio::GetFrameworkCode($biblionumber);
291 my ( $itemtag, $itemsubfield ) = C4::Biblio::GetMarcFromKohaField( "items.itemnumber" );
293 my $localitemmarc = MARC::Record->new;
294 $localitemmarc->append_fields( $item_marc->field($itemtag) );
295 my $item_object = Koha::Items->find($itemnumber);
296 my $item = TransformMarcToKoha( $localitemmarc, $frameworkcode, 'items' );
297 $item->{cn_source} = delete $item->{'items.cn_source'}; # Because of C4::Biblio::_disambiguate
298 $item->{cn_sort} = delete $item->{'items.cn_sort'}; # Because of C4::Biblio::_disambiguate
299 $item->{itemnumber} = $itemnumber;
300 $item->{biblionumber} = $biblionumber;
301 $item_object = $item_object->set_or_blank($item);
302 my $unlinked_item_subfields = _get_unlinked_item_subfields( $localitemmarc, $frameworkcode );
303 $item_object->more_subfields_xml(_get_unlinked_subfields_xml($unlinked_item_subfields));
304 $item_object->store;
306 return $item_object->unblessed;
309 =head2 ModItemTransfer
311 ModItemTransfer($itemnumber, $frombranch, $tobranch, $trigger);
313 Marks an item as being transferred from one branch to another and records the trigger.
315 =cut
317 sub ModItemTransfer {
318 my ( $itemnumber, $frombranch, $tobranch, $trigger ) = @_;
320 my $dbh = C4::Context->dbh;
321 my $item = Koha::Items->find( $itemnumber );
323 # Remove the 'shelving cart' location status if it is being used.
324 CartToShelf( $itemnumber ) if $item->location && $item->location eq 'CART' && ( !$item->permanent_location || $item->permanent_location ne 'CART' );
326 $dbh->do("UPDATE branchtransfers SET datearrived = NOW(), comments = ? WHERE itemnumber = ? AND datearrived IS NULL", undef, "Canceled, new transfer from $frombranch to $tobranch created", $itemnumber);
328 #new entry in branchtransfers....
329 my $sth = $dbh->prepare(
330 "INSERT INTO branchtransfers (itemnumber, frombranch, datesent, tobranch, reason)
331 VALUES (?, ?, NOW(), ?, ?)");
332 $sth->execute($itemnumber, $frombranch, $tobranch, $trigger);
334 # FIXME we are fetching the item twice in the 2 next statements!
335 Koha::Items->find($itemnumber)->holdingbranch($frombranch)->store({ log_action => 0 });
336 ModDateLastSeen($itemnumber);
337 return;
340 =head2 ModDateLastSeen
342 ModDateLastSeen( $itemnumber, $leave_item_lost );
344 Mark item as seen. Is called when an item is issued, returned or manually marked during inventory/stocktaking.
345 C<$itemnumber> is the item number
346 C<$leave_item_lost> determines if a lost item will be found or remain lost
348 =cut
350 sub ModDateLastSeen {
351 my ( $itemnumber, $leave_item_lost ) = @_;
353 my $today = output_pref({ dt => dt_from_string, dateformat => 'iso', dateonly => 1 });
355 my $item = Koha::Items->find($itemnumber);
356 $item->datelastseen($today);
357 $item->itemlost(0) unless $leave_item_lost;
358 $item->store({ log_action => 0 });
361 =head2 CheckItemPreSave
363 my $item_ref = TransformMarcToKoha($marc, 'items');
364 # do stuff
365 my %errors = CheckItemPreSave($item_ref);
366 if (exists $errors{'duplicate_barcode'}) {
367 print "item has duplicate barcode: ", $errors{'duplicate_barcode'}, "\n";
368 } elsif (exists $errors{'invalid_homebranch'}) {
369 print "item has invalid home branch: ", $errors{'invalid_homebranch'}, "\n";
370 } elsif (exists $errors{'invalid_holdingbranch'}) {
371 print "item has invalid holding branch: ", $errors{'invalid_holdingbranch'}, "\n";
372 } else {
373 print "item is OK";
376 Given a hashref containing item fields, determine if it can be
377 inserted or updated in the database. Specifically, checks for
378 database integrity issues, and returns a hash containing any
379 of the following keys, if applicable.
381 =over 2
383 =item duplicate_barcode
385 Barcode, if it duplicates one already found in the database.
387 =item invalid_homebranch
389 Home branch, if not defined in branches table.
391 =item invalid_holdingbranch
393 Holding branch, if not defined in branches table.
395 =back
397 This function does NOT implement any policy-related checks,
398 e.g., whether current operator is allowed to save an
399 item that has a given branch code.
401 =cut
403 sub CheckItemPreSave {
404 my $item_ref = shift;
406 my %errors = ();
408 # check for duplicate barcode
409 if (exists $item_ref->{'barcode'} and defined $item_ref->{'barcode'}) {
410 my $existing_item= Koha::Items->find({barcode => $item_ref->{'barcode'}});
411 if ($existing_item) {
412 if (!exists $item_ref->{'itemnumber'} # new item
413 or $item_ref->{'itemnumber'} != $existing_item->itemnumber) { # existing item
414 $errors{'duplicate_barcode'} = $item_ref->{'barcode'};
419 # check for valid home branch
420 if (exists $item_ref->{'homebranch'} and defined $item_ref->{'homebranch'}) {
421 my $home_library = Koha::Libraries->find( $item_ref->{homebranch} );
422 unless (defined $home_library) {
423 $errors{'invalid_homebranch'} = $item_ref->{'homebranch'};
427 # check for valid holding branch
428 if (exists $item_ref->{'holdingbranch'} and defined $item_ref->{'holdingbranch'}) {
429 my $holding_library = Koha::Libraries->find( $item_ref->{holdingbranch} );
430 unless (defined $holding_library) {
431 $errors{'invalid_holdingbranch'} = $item_ref->{'holdingbranch'};
435 return %errors;
439 =head1 EXPORTED SPECIAL ACCESSOR FUNCTIONS
441 The following functions provide various ways of
442 getting an item record, a set of item records, or
443 lists of authorized values for certain item fields.
445 =cut
447 =head2 GetItemsForInventory
449 ($itemlist, $iTotalRecords) = GetItemsForInventory( {
450 minlocation => $minlocation,
451 maxlocation => $maxlocation,
452 location => $location,
453 itemtype => $itemtype,
454 ignoreissued => $ignoreissued,
455 datelastseen => $datelastseen,
456 branchcode => $branchcode,
457 branch => $branch,
458 offset => $offset,
459 size => $size,
460 statushash => $statushash,
461 } );
463 Retrieve a list of title/authors/barcode/callnumber, for biblio inventory.
465 The sub returns a reference to a list of hashes, each containing
466 itemnumber, author, title, barcode, item callnumber, and date last
467 seen. It is ordered by callnumber then title.
469 The required minlocation & maxlocation parameters are used to specify a range of item callnumbers
470 the datelastseen can be used to specify that you want to see items not seen since a past date only.
471 offset & size can be used to retrieve only a part of the whole listing (defaut behaviour)
472 $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.
474 $iTotalRecords is the number of rows that would have been returned without the $offset, $size limit clause
476 =cut
478 sub GetItemsForInventory {
479 my ( $parameters ) = @_;
480 my $minlocation = $parameters->{'minlocation'} // '';
481 my $maxlocation = $parameters->{'maxlocation'} // '';
482 my $class_source = $parameters->{'class_source'} // C4::Context->preference('DefaultClassificationSource');
483 my $location = $parameters->{'location'} // '';
484 my $itemtype = $parameters->{'itemtype'} // '';
485 my $ignoreissued = $parameters->{'ignoreissued'} // '';
486 my $datelastseen = $parameters->{'datelastseen'} // '';
487 my $branchcode = $parameters->{'branchcode'} // '';
488 my $branch = $parameters->{'branch'} // '';
489 my $offset = $parameters->{'offset'} // '';
490 my $size = $parameters->{'size'} // '';
491 my $statushash = $parameters->{'statushash'} // '';
492 my $ignore_waiting_holds = $parameters->{'ignore_waiting_holds'} // '';
494 my $dbh = C4::Context->dbh;
495 my ( @bind_params, @where_strings );
497 my $min_cnsort = GetClassSort($class_source,undef,$minlocation);
498 my $max_cnsort = GetClassSort($class_source,undef,$maxlocation);
500 my $select_columns = q{
501 SELECT DISTINCT(items.itemnumber), barcode, itemcallnumber, title, author, biblio.biblionumber, biblio.frameworkcode, datelastseen, homebranch, location, notforloan, damaged, itemlost, withdrawn, stocknumber, items.cn_sort
503 my $select_count = q{SELECT COUNT(DISTINCT(items.itemnumber))};
504 my $query = q{
505 FROM items
506 LEFT JOIN biblio ON items.biblionumber = biblio.biblionumber
507 LEFT JOIN biblioitems on items.biblionumber = biblioitems.biblionumber
509 if ($statushash){
510 for my $authvfield (keys %$statushash){
511 if ( scalar @{$statushash->{$authvfield}} > 0 ){
512 my $joinedvals = join ',', @{$statushash->{$authvfield}};
513 push @where_strings, "$authvfield in (" . $joinedvals . ")";
518 if ($minlocation) {
519 push @where_strings, 'items.cn_sort >= ?';
520 push @bind_params, $min_cnsort;
523 if ($maxlocation) {
524 push @where_strings, 'items.cn_sort <= ?';
525 push @bind_params, $max_cnsort;
528 if ($datelastseen) {
529 $datelastseen = output_pref({ str => $datelastseen, dateformat => 'iso', dateonly => 1 });
530 push @where_strings, '(datelastseen < ? OR datelastseen IS NULL)';
531 push @bind_params, $datelastseen;
534 if ( $location ) {
535 push @where_strings, 'items.location = ?';
536 push @bind_params, $location;
539 if ( $branchcode ) {
540 if($branch eq "homebranch"){
541 push @where_strings, 'items.homebranch = ?';
542 }else{
543 push @where_strings, 'items.holdingbranch = ?';
545 push @bind_params, $branchcode;
548 if ( $itemtype ) {
549 push @where_strings, 'biblioitems.itemtype = ?';
550 push @bind_params, $itemtype;
553 if ( $ignoreissued) {
554 $query .= "LEFT JOIN issues ON items.itemnumber = issues.itemnumber ";
555 push @where_strings, 'issues.date_due IS NULL';
558 if ( $ignore_waiting_holds ) {
559 $query .= "LEFT JOIN reserves ON items.itemnumber = reserves.itemnumber ";
560 push( @where_strings, q{(reserves.found != 'W' OR reserves.found IS NULL)} );
563 if ( @where_strings ) {
564 $query .= 'WHERE ';
565 $query .= join ' AND ', @where_strings;
567 my $count_query = $select_count . $query;
568 $query .= ' ORDER BY items.cn_sort, itemcallnumber, title';
569 $query .= " LIMIT $offset, $size" if ($offset and $size);
570 $query = $select_columns . $query;
571 my $sth = $dbh->prepare($query);
572 $sth->execute( @bind_params );
574 my @results = ();
575 my $tmpresults = $sth->fetchall_arrayref({});
576 $sth = $dbh->prepare( $count_query );
577 $sth->execute( @bind_params );
578 my ($iTotalRecords) = $sth->fetchrow_array();
580 my @avs = Koha::AuthorisedValues->search(
581 { 'marc_subfield_structures.kohafield' => { '>' => '' },
582 'me.authorised_value' => { '>' => '' },
584 { join => { category => 'marc_subfield_structures' },
585 distinct => ['marc_subfield_structures.kohafield, me.category, frameworkcode, me.authorised_value'],
586 '+select' => [ 'marc_subfield_structures.kohafield', 'marc_subfield_structures.frameworkcode', 'me.authorised_value', 'me.lib' ],
587 '+as' => [ 'kohafield', 'frameworkcode', 'authorised_value', 'lib' ],
591 my $avmapping = { map { $_->get_column('kohafield') . ',' . $_->get_column('frameworkcode') . ',' . $_->get_column('authorised_value') => $_->get_column('lib') } @avs };
593 foreach my $row (@$tmpresults) {
595 # Auth values
596 foreach (keys %$row) {
597 if (
598 defined(
599 $avmapping->{ "items.$_," . $row->{'frameworkcode'} . "," . ( $row->{$_} // q{} ) }
602 $row->{$_} = $avmapping->{"items.$_,".$row->{'frameworkcode'}.",".$row->{$_}};
605 push @results, $row;
608 return (\@results, $iTotalRecords);
611 =head2 GetItemsInfo
613 @results = GetItemsInfo($biblionumber);
615 Returns information about items with the given biblionumber.
617 C<GetItemsInfo> returns a list of references-to-hash. Each element
618 contains a number of keys. Most of them are attributes from the
619 C<biblio>, C<biblioitems>, C<items>, and C<itemtypes> tables in the
620 Koha database. Other keys include:
622 =over 2
624 =item C<$data-E<gt>{branchname}>
626 The name (not the code) of the branch to which the book belongs.
628 =item C<$data-E<gt>{datelastseen}>
630 This is simply C<items.datelastseen>, except that while the date is
631 stored in YYYY-MM-DD format in the database, here it is converted to
632 DD/MM/YYYY format. A NULL date is returned as C<//>.
634 =item C<$data-E<gt>{datedue}>
636 =item C<$data-E<gt>{class}>
638 This is the concatenation of C<biblioitems.classification>, the book's
639 Dewey code, and C<biblioitems.subclass>.
641 =item C<$data-E<gt>{ocount}>
643 I think this is the number of copies of the book available.
645 =item C<$data-E<gt>{order}>
647 If this is set, it is set to C<One Order>.
649 =back
651 =cut
653 sub GetItemsInfo {
654 my ( $biblionumber ) = @_;
655 my $dbh = C4::Context->dbh;
656 require C4::Languages;
657 my $language = C4::Languages::getlanguage();
658 my $query = "
659 SELECT items.*,
660 biblio.*,
661 biblioitems.volume,
662 biblioitems.number,
663 biblioitems.itemtype,
664 biblioitems.isbn,
665 biblioitems.issn,
666 biblioitems.publicationyear,
667 biblioitems.publishercode,
668 biblioitems.volumedate,
669 biblioitems.volumedesc,
670 biblioitems.lccn,
671 biblioitems.url,
672 items.notforloan as itemnotforloan,
673 issues.borrowernumber,
674 issues.date_due as datedue,
675 issues.onsite_checkout,
676 borrowers.cardnumber,
677 borrowers.surname,
678 borrowers.firstname,
679 borrowers.branchcode as bcode,
680 serial.serialseq,
681 serial.publisheddate,
682 itemtypes.description,
683 COALESCE( localization.translation, itemtypes.description ) AS translated_description,
684 itemtypes.notforloan as notforloan_per_itemtype,
685 holding.branchurl,
686 holding.branchcode,
687 holding.branchname,
688 holding.opac_info as holding_branch_opac_info,
689 home.opac_info as home_branch_opac_info,
690 IF(tmp_holdsqueue.itemnumber,1,0) AS has_pending_hold
691 FROM items
692 LEFT JOIN branches AS holding ON items.holdingbranch = holding.branchcode
693 LEFT JOIN branches AS home ON items.homebranch=home.branchcode
694 LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber
695 LEFT JOIN biblioitems ON biblioitems.biblioitemnumber = items.biblioitemnumber
696 LEFT JOIN issues USING (itemnumber)
697 LEFT JOIN borrowers USING (borrowernumber)
698 LEFT JOIN serialitems USING (itemnumber)
699 LEFT JOIN serial USING (serialid)
700 LEFT JOIN itemtypes ON itemtypes.itemtype = "
701 . (C4::Context->preference('item-level_itypes') ? 'items.itype' : 'biblioitems.itemtype');
702 $query .= q|
703 LEFT JOIN tmp_holdsqueue USING (itemnumber)
704 LEFT JOIN localization ON itemtypes.itemtype = localization.code
705 AND localization.entity = 'itemtypes'
706 AND localization.lang = ?
709 $query .= " WHERE items.biblionumber = ? ORDER BY home.branchname, items.enumchron, LPAD( items.copynumber, 8, '0' ), items.dateaccessioned DESC" ;
710 my $sth = $dbh->prepare($query);
711 $sth->execute($language, $biblionumber);
712 my $i = 0;
713 my @results;
714 my $serial;
716 my $userenv = C4::Context->userenv;
717 my $want_not_same_branch = C4::Context->preference("IndependentBranches") && !C4::Context->IsSuperLibrarian();
718 while ( my $data = $sth->fetchrow_hashref ) {
719 if ( $data->{borrowernumber} && $want_not_same_branch) {
720 $data->{'NOTSAMEBRANCH'} = $data->{'bcode'} ne $userenv->{branch};
723 $serial ||= $data->{'serial'};
725 my $descriptions;
726 # get notforloan complete status if applicable
727 $descriptions = Koha::AuthorisedValues->get_description_by_koha_field({frameworkcode => $data->{frameworkcode}, kohafield => 'items.notforloan', authorised_value => $data->{itemnotforloan} });
728 $data->{notforloanvalue} = $descriptions->{lib} // '';
729 $data->{notforloanvalueopac} = $descriptions->{opac_description} // '';
731 # get restricted status and description if applicable
732 $descriptions = Koha::AuthorisedValues->get_description_by_koha_field({frameworkcode => $data->{frameworkcode}, kohafield => 'items.restricted', authorised_value => $data->{restricted} });
733 $data->{restrictedvalue} = $descriptions->{lib} // '';
734 $data->{restrictedvalueopac} = $descriptions->{opac_description} // '';
736 # my stack procedures
737 $descriptions = Koha::AuthorisedValues->get_description_by_koha_field({frameworkcode => $data->{frameworkcode}, kohafield => 'items.stack', authorised_value => $data->{stack} });
738 $data->{stack} = $descriptions->{lib} // '';
740 # Find the last 3 people who borrowed this item.
741 my $sth2 = $dbh->prepare("SELECT * FROM old_issues,borrowers
742 WHERE itemnumber = ?
743 AND old_issues.borrowernumber = borrowers.borrowernumber
744 ORDER BY returndate DESC
745 LIMIT 3");
746 $sth2->execute($data->{'itemnumber'});
747 my $ii = 0;
748 while (my $data2 = $sth2->fetchrow_hashref()) {
749 $data->{"timestamp$ii"} = $data2->{'timestamp'} if $data2->{'timestamp'};
750 $data->{"card$ii"} = $data2->{'cardnumber'} if $data2->{'cardnumber'};
751 $data->{"borrower$ii"} = $data2->{'borrowernumber'} if $data2->{'borrowernumber'};
752 $ii++;
755 $results[$i] = $data;
756 $i++;
759 return $serial
760 ? sort { ($b->{'publisheddate'} || $b->{'enumchron'}) cmp ($a->{'publisheddate'} || $a->{'enumchron'}) } @results
761 : @results;
764 =head2 GetItemsLocationInfo
766 my @itemlocinfo = GetItemsLocationInfo($biblionumber);
768 Returns the branch names, shelving location and itemcallnumber for each item attached to the biblio in question
770 C<GetItemsInfo> returns a list of references-to-hash. Data returned:
772 =over 2
774 =item C<$data-E<gt>{homebranch}>
776 Branch Name of the item's homebranch
778 =item C<$data-E<gt>{holdingbranch}>
780 Branch Name of the item's holdingbranch
782 =item C<$data-E<gt>{location}>
784 Item's shelving location code
786 =item C<$data-E<gt>{location_intranet}>
788 The intranet description for the Shelving Location as set in authorised_values 'LOC'
790 =item C<$data-E<gt>{location_opac}>
792 The OPAC description for the Shelving Location as set in authorised_values 'LOC'. Falls back to intranet description if no OPAC
793 description is set.
795 =item C<$data-E<gt>{itemcallnumber}>
797 Item's itemcallnumber
799 =item C<$data-E<gt>{cn_sort}>
801 Item's call number normalized for sorting
803 =back
805 =cut
807 sub GetItemsLocationInfo {
808 my $biblionumber = shift;
809 my @results;
811 my $dbh = C4::Context->dbh;
812 my $query = "SELECT a.branchname as homebranch, b.branchname as holdingbranch,
813 location, itemcallnumber, cn_sort
814 FROM items, branches as a, branches as b
815 WHERE homebranch = a.branchcode AND holdingbranch = b.branchcode
816 AND biblionumber = ?
817 ORDER BY cn_sort ASC";
818 my $sth = $dbh->prepare($query);
819 $sth->execute($biblionumber);
821 while ( my $data = $sth->fetchrow_hashref ) {
822 my $av = Koha::AuthorisedValues->search({ category => 'LOC', authorised_value => $data->{location} });
823 $av = $av->count ? $av->next : undef;
824 $data->{location_intranet} = $av ? $av->lib : '';
825 $data->{location_opac} = $av ? $av->opac_description : '';
826 push @results, $data;
828 return @results;
831 =head2 GetHostItemsInfo
833 $hostiteminfo = GetHostItemsInfo($hostfield);
834 Returns the iteminfo for items linked to records via a host field
836 =cut
838 sub GetHostItemsInfo {
839 my ($record) = @_;
840 my @returnitemsInfo;
842 if( !C4::Context->preference('EasyAnalyticalRecords') ) {
843 return @returnitemsInfo;
846 my @fields;
847 if( C4::Context->preference('marcflavour') eq 'MARC21' ||
848 C4::Context->preference('marcflavour') eq 'NORMARC') {
849 @fields = $record->field('773');
850 } elsif( C4::Context->preference('marcflavour') eq 'UNIMARC') {
851 @fields = $record->field('461');
854 foreach my $hostfield ( @fields ) {
855 my $hostbiblionumber = $hostfield->subfield("0");
856 my $linkeditemnumber = $hostfield->subfield("9");
857 my @hostitemInfos = GetItemsInfo($hostbiblionumber);
858 foreach my $hostitemInfo (@hostitemInfos) {
859 if( $hostitemInfo->{itemnumber} eq $linkeditemnumber ) {
860 push @returnitemsInfo, $hostitemInfo;
861 last;
865 return @returnitemsInfo;
868 =head2 get_hostitemnumbers_of
870 my @itemnumbers_of = get_hostitemnumbers_of($biblionumber);
872 Given a biblionumber, return the list of corresponding itemnumbers that are linked to it via host fields
874 Return a reference on a hash where key is a biblionumber and values are
875 references on array of itemnumbers.
877 =cut
880 sub get_hostitemnumbers_of {
881 my ($biblionumber) = @_;
883 if( !C4::Context->preference('EasyAnalyticalRecords') ) {
884 return ();
887 my $marcrecord = C4::Biblio::GetMarcBiblio({ biblionumber => $biblionumber });
888 return unless $marcrecord;
890 my ( @returnhostitemnumbers, $tag, $biblio_s, $item_s );
892 my $marcflavor = C4::Context->preference('marcflavour');
893 if ( $marcflavor eq 'MARC21' || $marcflavor eq 'NORMARC' ) {
894 $tag = '773';
895 $biblio_s = '0';
896 $item_s = '9';
898 elsif ( $marcflavor eq 'UNIMARC' ) {
899 $tag = '461';
900 $biblio_s = '0';
901 $item_s = '9';
904 foreach my $hostfield ( $marcrecord->field($tag) ) {
905 my $hostbiblionumber = $hostfield->subfield($biblio_s);
906 next unless $hostbiblionumber; # have tag, don't have $biblio_s subfield
907 my $linkeditemnumber = $hostfield->subfield($item_s);
908 if ( ! $linkeditemnumber ) {
909 warn "ERROR biblionumber $biblionumber has 773^0, but doesn't have 9";
910 next;
912 my $is_from_biblio = Koha::Items->search({ itemnumber => $linkeditemnumber, biblionumber => $hostbiblionumber });
913 push @returnhostitemnumbers, $linkeditemnumber
914 if $is_from_biblio;
917 return @returnhostitemnumbers;
920 =head2 GetHiddenItemnumbers
922 my @itemnumbers_to_hide = GetHiddenItemnumbers({ items => \@items, borcat => $category });
924 Given a list of items it checks which should be hidden from the OPAC given
925 the current configuration. Returns a list of itemnumbers corresponding to
926 those that should be hidden. Optionally takes a borcat parameter for certain borrower types
927 to be excluded
929 =cut
931 sub GetHiddenItemnumbers {
932 my $params = shift;
933 my $items = $params->{items};
934 if (my $exceptions = C4::Context->preference('OpacHiddenItemsExceptions') and $params->{'borcat'}){
935 foreach my $except (split(/\|/, $exceptions)){
936 if ($params->{'borcat'} eq $except){
937 return; # we don't hide anything for this borrower category
941 my @resultitems;
943 my $yaml = C4::Context->preference('OpacHiddenItems');
944 return () if (! $yaml =~ /\S/ );
945 $yaml = "$yaml\n\n"; # YAML is anal on ending \n. Surplus does not hurt
946 my $hidingrules;
947 eval {
948 $hidingrules = YAML::Load($yaml);
950 if ($@) {
951 warn "Unable to parse OpacHiddenItems syspref : $@";
952 return ();
954 my $dbh = C4::Context->dbh;
956 # For each item
957 foreach my $item (@$items) {
959 # We check each rule
960 foreach my $field (keys %$hidingrules) {
961 my $val;
962 if (exists $item->{$field}) {
963 $val = $item->{$field};
965 else {
966 my $query = "SELECT $field from items where itemnumber = ?";
967 $val = $dbh->selectrow_array($query, undef, $item->{'itemnumber'});
969 $val = '' unless defined $val;
971 # If the results matches the values in the yaml file
972 if (any { $val eq $_ } @{$hidingrules->{$field}}) {
974 # We add the itemnumber to the list
975 push @resultitems, $item->{'itemnumber'};
977 # If at least one rule matched for an item, no need to test the others
978 last;
982 return @resultitems;
985 =head1 LIMITED USE FUNCTIONS
987 The following functions, while part of the public API,
988 are not exported. This is generally because they are
989 meant to be used by only one script for a specific
990 purpose, and should not be used in any other context
991 without careful thought.
993 =cut
995 =head2 GetMarcItem
997 my $item_marc = GetMarcItem($biblionumber, $itemnumber);
999 Returns MARC::Record of the item passed in parameter.
1000 This function is meant for use only in C<cataloguing/additem.pl>,
1001 where it is needed to support that script's MARC-like
1002 editor.
1004 =cut
1006 sub GetMarcItem {
1007 my ( $biblionumber, $itemnumber ) = @_;
1009 # GetMarcItem has been revised so that it does the following:
1010 # 1. Gets the item information from the items table.
1011 # 2. Converts it to a MARC field for storage in the bib record.
1013 # The previous behavior was:
1014 # 1. Get the bib record.
1015 # 2. Return the MARC tag corresponding to the item record.
1017 # The difference is that one treats the items row as authoritative,
1018 # while the other treats the MARC representation as authoritative
1019 # under certain circumstances.
1021 my $item = Koha::Items->find($itemnumber) or return;
1023 # Tack on 'items.' prefix to column names so that C4::Biblio::TransformKohaToMarc will work.
1024 # Also, don't emit a subfield if the underlying field is blank.
1026 return Item2Marc($item->unblessed, $biblionumber);
1029 sub Item2Marc {
1030 my ($itemrecord,$biblionumber)=@_;
1031 my $mungeditem = {
1032 map {
1033 defined($itemrecord->{$_}) && $itemrecord->{$_} ne '' ? ("items.$_" => $itemrecord->{$_}) : ()
1034 } keys %{ $itemrecord }
1036 my $framework = C4::Biblio::GetFrameworkCode( $biblionumber );
1037 my $itemmarc = C4::Biblio::TransformKohaToMarc( $mungeditem, { framework => $framework } );
1038 my ( $itemtag, $itemsubfield ) = C4::Biblio::GetMarcFromKohaField(
1039 "items.itemnumber", $framework,
1042 my $unlinked_item_subfields = _parse_unlinked_item_subfields_from_xml($mungeditem->{'items.more_subfields_xml'});
1043 if (defined $unlinked_item_subfields and $#$unlinked_item_subfields > -1) {
1044 foreach my $field ($itemmarc->field($itemtag)){
1045 $field->add_subfields(@$unlinked_item_subfields);
1048 return $itemmarc;
1051 =head1 PRIVATE FUNCTIONS AND VARIABLES
1053 The following functions are not meant to be called
1054 directly, but are documented in order to explain
1055 the inner workings of C<C4::Items>.
1057 =cut
1059 =head2 MoveItemFromBiblio
1061 MoveItemFromBiblio($itenumber, $frombiblio, $tobiblio);
1063 Moves an item from a biblio to another
1065 Returns undef if the move failed or the biblionumber of the destination record otherwise
1067 =cut
1069 sub MoveItemFromBiblio {
1070 my ($itemnumber, $frombiblio, $tobiblio) = @_;
1071 my $dbh = C4::Context->dbh;
1072 my ( $tobiblioitem ) = $dbh->selectrow_array(q|
1073 SELECT biblioitemnumber
1074 FROM biblioitems
1075 WHERE biblionumber = ?
1076 |, undef, $tobiblio );
1077 my $return = $dbh->do(q|
1078 UPDATE items
1079 SET biblioitemnumber = ?,
1080 biblionumber = ?
1081 WHERE itemnumber = ?
1082 AND biblionumber = ?
1083 |, undef, $tobiblioitem, $tobiblio, $itemnumber, $frombiblio );
1084 if ($return == 1) {
1085 ModZebra( $tobiblio, "specialUpdate", "biblioserver" );
1086 ModZebra( $frombiblio, "specialUpdate", "biblioserver" );
1087 # Checking if the item we want to move is in an order
1088 require C4::Acquisition;
1089 my $order = C4::Acquisition::GetOrderFromItemnumber($itemnumber);
1090 if ($order) {
1091 # Replacing the biblionumber within the order if necessary
1092 $order->{'biblionumber'} = $tobiblio;
1093 C4::Acquisition::ModOrder($order);
1096 # Update reserves, hold_fill_targets, tmp_holdsqueue and linktracker tables
1097 for my $table_name ( qw( reserves hold_fill_targets tmp_holdsqueue linktracker ) ) {
1098 $dbh->do( qq|
1099 UPDATE $table_name
1100 SET biblionumber = ?
1101 WHERE itemnumber = ?
1102 |, undef, $tobiblio, $itemnumber );
1104 return $tobiblio;
1106 return;
1109 =head2 _marc_from_item_hash
1111 my $item_marc = _marc_from_item_hash($item, $frameworkcode[, $unlinked_item_subfields]);
1113 Given an item hash representing a complete item record,
1114 create a C<MARC::Record> object containing an embedded
1115 tag representing that item.
1117 The third, optional parameter C<$unlinked_item_subfields> is
1118 an arrayref of subfields (not mapped to C<items> fields per the
1119 framework) to be added to the MARC representation
1120 of the item.
1122 =cut
1124 sub _marc_from_item_hash {
1125 my $item = shift;
1126 my $frameworkcode = shift;
1127 my $unlinked_item_subfields;
1128 if (@_) {
1129 $unlinked_item_subfields = shift;
1132 # Tack on 'items.' prefix to column names so lookup from MARC frameworks will work
1133 # Also, don't emit a subfield if the underlying field is blank.
1134 my $mungeditem = { map { (defined($item->{$_}) and $item->{$_} ne '') ?
1135 (/^items\./ ? ($_ => $item->{$_}) : ("items.$_" => $item->{$_}))
1136 : () } keys %{ $item } };
1138 my $item_marc = MARC::Record->new();
1139 foreach my $item_field ( keys %{$mungeditem} ) {
1140 my ( $tag, $subfield ) = C4::Biblio::GetMarcFromKohaField( $item_field );
1141 next unless defined $tag and defined $subfield; # skip if not mapped to MARC field
1142 my @values = split(/\s?\|\s?/, $mungeditem->{$item_field}, -1);
1143 foreach my $value (@values){
1144 if ( my $field = $item_marc->field($tag) ) {
1145 $field->add_subfields( $subfield => $value );
1146 } else {
1147 my $add_subfields = [];
1148 if (defined $unlinked_item_subfields and ref($unlinked_item_subfields) eq 'ARRAY' and $#$unlinked_item_subfields > -1) {
1149 $add_subfields = $unlinked_item_subfields;
1151 $item_marc->add_fields( $tag, " ", " ", $subfield => $value, @$add_subfields );
1156 return $item_marc;
1159 =head2 _repack_item_errors
1161 Add an error message hash generated by C<CheckItemPreSave>
1162 to a list of errors.
1164 =cut
1166 sub _repack_item_errors {
1167 my $item_sequence_num = shift;
1168 my $item_ref = shift;
1169 my $error_ref = shift;
1171 my @repacked_errors = ();
1173 foreach my $error_code (sort keys %{ $error_ref }) {
1174 my $repacked_error = {};
1175 $repacked_error->{'item_sequence'} = $item_sequence_num;
1176 $repacked_error->{'item_barcode'} = exists($item_ref->{'barcode'}) ? $item_ref->{'barcode'} : '';
1177 $repacked_error->{'error_code'} = $error_code;
1178 $repacked_error->{'error_information'} = $error_ref->{$error_code};
1179 push @repacked_errors, $repacked_error;
1182 return @repacked_errors;
1185 =head2 _get_unlinked_item_subfields
1187 my $unlinked_item_subfields = _get_unlinked_item_subfields($original_item_marc, $frameworkcode);
1189 =cut
1191 sub _get_unlinked_item_subfields {
1192 my $original_item_marc = shift;
1193 my $frameworkcode = shift;
1195 my $marcstructure = C4::Biblio::GetMarcStructure(1, $frameworkcode, { unsafe => 1 });
1197 # assume that this record has only one field, and that that
1198 # field contains only the item information
1199 my $subfields = [];
1200 my @fields = $original_item_marc->fields();
1201 if ($#fields > -1) {
1202 my $field = $fields[0];
1203 my $tag = $field->tag();
1204 foreach my $subfield ($field->subfields()) {
1205 if (defined $subfield->[1] and
1206 $subfield->[1] ne '' and
1207 !$marcstructure->{$tag}->{$subfield->[0]}->{'kohafield'}) {
1208 push @$subfields, $subfield->[0] => $subfield->[1];
1212 return $subfields;
1215 =head2 _get_unlinked_subfields_xml
1217 my $unlinked_subfields_xml = _get_unlinked_subfields_xml($unlinked_item_subfields);
1219 =cut
1221 sub _get_unlinked_subfields_xml {
1222 my $unlinked_item_subfields = shift;
1224 my $xml;
1225 if (defined $unlinked_item_subfields and ref($unlinked_item_subfields) eq 'ARRAY' and $#$unlinked_item_subfields > -1) {
1226 my $marc = MARC::Record->new();
1227 # use of tag 999 is arbitrary, and doesn't need to match the item tag
1228 # used in the framework
1229 $marc->append_fields(MARC::Field->new('999', ' ', ' ', @$unlinked_item_subfields));
1230 $marc->encoding("UTF-8");
1231 $xml = $marc->as_xml("USMARC");
1234 return $xml;
1237 =head2 _parse_unlinked_item_subfields_from_xml
1239 my $unlinked_item_subfields = _parse_unlinked_item_subfields_from_xml($whole_item->{'more_subfields_xml'}):
1241 =cut
1243 sub _parse_unlinked_item_subfields_from_xml {
1244 my $xml = shift;
1245 require C4::Charset;
1246 return unless defined $xml and $xml ne "";
1247 my $marc = MARC::Record->new_from_xml(C4::Charset::StripNonXmlChars($xml),'UTF-8');
1248 my $unlinked_subfields = [];
1249 my @fields = $marc->fields();
1250 if ($#fields > -1) {
1251 foreach my $subfield ($fields[0]->subfields()) {
1252 push @$unlinked_subfields, $subfield->[0] => $subfield->[1];
1255 return $unlinked_subfields;
1258 =head2 GetAnalyticsCount
1260 $count= &GetAnalyticsCount($itemnumber)
1262 counts Usage of itemnumber in Analytical bibliorecords.
1264 =cut
1266 sub GetAnalyticsCount {
1267 my ($itemnumber) = @_;
1269 ### ZOOM search here
1270 my $query;
1271 $query= "hi=".$itemnumber;
1272 my $searcher = Koha::SearchEngine::Search->new({index => $Koha::SearchEngine::BIBLIOS_INDEX});
1273 my ($err,$res,$result) = $searcher->simple_search_compat($query,0,10);
1274 return ($result);
1277 sub _SearchItems_build_where_fragment {
1278 my ($filter) = @_;
1280 my $dbh = C4::Context->dbh;
1282 my $where_fragment;
1283 if (exists($filter->{conjunction})) {
1284 my (@where_strs, @where_args);
1285 foreach my $f (@{ $filter->{filters} }) {
1286 my $fragment = _SearchItems_build_where_fragment($f);
1287 if ($fragment) {
1288 push @where_strs, $fragment->{str};
1289 push @where_args, @{ $fragment->{args} };
1292 my $where_str = '';
1293 if (@where_strs) {
1294 $where_str = '(' . join (' ' . $filter->{conjunction} . ' ', @where_strs) . ')';
1295 $where_fragment = {
1296 str => $where_str,
1297 args => \@where_args,
1300 } else {
1301 my @columns = Koha::Database->new()->schema()->resultset('Item')->result_source->columns;
1302 push @columns, Koha::Database->new()->schema()->resultset('Biblio')->result_source->columns;
1303 push @columns, Koha::Database->new()->schema()->resultset('Biblioitem')->result_source->columns;
1304 my @operators = qw(= != > < >= <= like);
1305 my $field = $filter->{field} // q{};
1306 if ( (0 < grep { $_ eq $field } @columns) or (substr($field, 0, 5) eq 'marc:') ) {
1307 my $op = $filter->{operator};
1308 my $query = $filter->{query};
1310 if (!$op or (0 == grep { $_ eq $op } @operators)) {
1311 $op = '='; # default operator
1314 my $column;
1315 if ($field =~ /^marc:(\d{3})(?:\$(\w))?$/) {
1316 my $marcfield = $1;
1317 my $marcsubfield = $2;
1318 my ($kohafield) = $dbh->selectrow_array(q|
1319 SELECT kohafield FROM marc_subfield_structure
1320 WHERE tagfield=? AND tagsubfield=? AND frameworkcode=''
1321 |, undef, $marcfield, $marcsubfield);
1323 if ($kohafield) {
1324 $column = $kohafield;
1325 } else {
1326 # MARC field is not linked to a DB field so we need to use
1327 # ExtractValue on marcxml from biblio_metadata or
1328 # items.more_subfields_xml, depending on the MARC field.
1329 my $xpath;
1330 my $sqlfield;
1331 my ($itemfield) = C4::Biblio::GetMarcFromKohaField('items.itemnumber');
1332 if ($marcfield eq $itemfield) {
1333 $sqlfield = 'more_subfields_xml';
1334 $xpath = '//record/datafield/subfield[@code="' . $marcsubfield . '"]';
1335 } else {
1336 $sqlfield = 'metadata'; # From biblio_metadata
1337 if ($marcfield < 10) {
1338 $xpath = "//record/controlfield[\@tag=\"$marcfield\"]";
1339 } else {
1340 $xpath = "//record/datafield[\@tag=\"$marcfield\"]/subfield[\@code=\"$marcsubfield\"]";
1343 $column = "ExtractValue($sqlfield, '$xpath')";
1345 } else {
1346 $column = $field;
1349 if (ref $query eq 'ARRAY') {
1350 if ($op eq '=') {
1351 $op = 'IN';
1352 } elsif ($op eq '!=') {
1353 $op = 'NOT IN';
1355 $where_fragment = {
1356 str => "$column $op (" . join (',', ('?') x @$query) . ")",
1357 args => $query,
1359 } else {
1360 $where_fragment = {
1361 str => "$column $op ?",
1362 args => [ $query ],
1368 return $where_fragment;
1371 =head2 SearchItems
1373 my ($items, $total) = SearchItems($filter, $params);
1375 Perform a search among items
1377 $filter is a reference to a hash which can be a filter, or a combination of filters.
1379 A filter has the following keys:
1381 =over 2
1383 =item * field: the name of a SQL column in table items
1385 =item * query: the value to search in this column
1387 =item * operator: comparison operator. Can be one of = != > < >= <= like
1389 =back
1391 A combination of filters hash the following keys:
1393 =over 2
1395 =item * conjunction: 'AND' or 'OR'
1397 =item * filters: array ref of filters
1399 =back
1401 $params is a reference to a hash that can contain the following parameters:
1403 =over 2
1405 =item * rows: Number of items to return. 0 returns everything (default: 0)
1407 =item * page: Page to return (return items from (page-1)*rows to (page*rows)-1)
1408 (default: 1)
1410 =item * sortby: A SQL column name in items table to sort on
1412 =item * sortorder: 'ASC' or 'DESC'
1414 =back
1416 =cut
1418 sub SearchItems {
1419 my ($filter, $params) = @_;
1421 $filter //= {};
1422 $params //= {};
1423 return unless ref $filter eq 'HASH';
1424 return unless ref $params eq 'HASH';
1426 # Default parameters
1427 $params->{rows} ||= 0;
1428 $params->{page} ||= 1;
1429 $params->{sortby} ||= 'itemnumber';
1430 $params->{sortorder} ||= 'ASC';
1432 my ($where_str, @where_args);
1433 my $where_fragment = _SearchItems_build_where_fragment($filter);
1434 if ($where_fragment) {
1435 $where_str = $where_fragment->{str};
1436 @where_args = @{ $where_fragment->{args} };
1439 my $dbh = C4::Context->dbh;
1440 my $query = q{
1441 SELECT SQL_CALC_FOUND_ROWS items.*
1442 FROM items
1443 LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber
1444 LEFT JOIN biblioitems ON biblioitems.biblioitemnumber = items.biblioitemnumber
1445 LEFT JOIN biblio_metadata ON biblio_metadata.biblionumber = biblio.biblionumber
1446 WHERE 1
1448 if (defined $where_str and $where_str ne '') {
1449 $query .= qq{ AND $where_str };
1452 $query .= q{ AND biblio_metadata.format = 'marcxml' AND biblio_metadata.schema = ? };
1453 push @where_args, C4::Context->preference('marcflavour');
1455 my @columns = Koha::Database->new()->schema()->resultset('Item')->result_source->columns;
1456 push @columns, Koha::Database->new()->schema()->resultset('Biblio')->result_source->columns;
1457 push @columns, Koha::Database->new()->schema()->resultset('Biblioitem')->result_source->columns;
1458 my $sortby = (0 < grep {$params->{sortby} eq $_} @columns)
1459 ? $params->{sortby} : 'itemnumber';
1460 my $sortorder = (uc($params->{sortorder}) eq 'ASC') ? 'ASC' : 'DESC';
1461 $query .= qq{ ORDER BY $sortby $sortorder };
1463 my $rows = $params->{rows};
1464 my @limit_args;
1465 if ($rows > 0) {
1466 my $offset = $rows * ($params->{page}-1);
1467 $query .= qq { LIMIT ?, ? };
1468 push @limit_args, $offset, $rows;
1471 my $sth = $dbh->prepare($query);
1472 my $rv = $sth->execute(@where_args, @limit_args);
1474 return unless ($rv);
1475 my ($total_rows) = $dbh->selectrow_array(q{ SELECT FOUND_ROWS() });
1477 return ($sth->fetchall_arrayref({}), $total_rows);
1481 =head1 OTHER FUNCTIONS
1483 =head2 _find_value
1485 ($indicators, $value) = _find_value($tag, $subfield, $record,$encoding);
1487 Find the given $subfield in the given $tag in the given
1488 MARC::Record $record. If the subfield is found, returns
1489 the (indicators, value) pair; otherwise, (undef, undef) is
1490 returned.
1492 PROPOSITION :
1493 Such a function is used in addbiblio AND additem and serial-edit and maybe could be used in Authorities.
1494 I suggest we export it from this module.
1496 =cut
1498 sub _find_value {
1499 my ( $tagfield, $insubfield, $record, $encoding ) = @_;
1500 my @result;
1501 my $indicator;
1502 if ( $tagfield < 10 ) {
1503 if ( $record->field($tagfield) ) {
1504 push @result, $record->field($tagfield)->data();
1505 } else {
1506 push @result, "";
1508 } else {
1509 foreach my $field ( $record->field($tagfield) ) {
1510 my @subfields = $field->subfields();
1511 foreach my $subfield (@subfields) {
1512 if ( @$subfield[0] eq $insubfield ) {
1513 push @result, @$subfield[1];
1514 $indicator = $field->indicator(1) . $field->indicator(2);
1519 return ( $indicator, @result );
1523 =head2 PrepareItemrecordDisplay
1525 PrepareItemrecordDisplay($itemrecord,$bibnum,$itemumber,$frameworkcode);
1527 Returns a hash with all the fields for Display a given item data in a template
1529 The $frameworkcode returns the item for the given frameworkcode, ONLY if bibnum is not provided
1531 =cut
1533 sub PrepareItemrecordDisplay {
1535 my ( $bibnum, $itemnum, $defaultvalues, $frameworkcode ) = @_;
1537 my $dbh = C4::Context->dbh;
1538 $frameworkcode = C4::Biblio::GetFrameworkCode($bibnum) if $bibnum;
1539 my ( $itemtagfield, $itemtagsubfield ) = C4::Biblio::GetMarcFromKohaField( "items.itemnumber" );
1541 # Note: $tagslib obtained from GetMarcStructure() in 'unsafe' mode is
1542 # a shared data structure. No plugin (including custom ones) should change
1543 # its contents. See also GetMarcStructure.
1544 my $tagslib = GetMarcStructure( 1, $frameworkcode, { unsafe => 1 } );
1546 # return nothing if we don't have found an existing framework.
1547 return q{} unless $tagslib;
1548 my $itemrecord;
1549 if ($itemnum) {
1550 $itemrecord = C4::Items::GetMarcItem( $bibnum, $itemnum );
1552 my @loop_data;
1554 my $branch_limit = C4::Context->userenv ? C4::Context->userenv->{"branch"} : "";
1555 my $query = qq{
1556 SELECT authorised_value,lib FROM authorised_values
1558 $query .= qq{
1559 LEFT JOIN authorised_values_branches ON ( id = av_id )
1560 } if $branch_limit;
1561 $query .= qq{
1562 WHERE category = ?
1564 $query .= qq{ AND ( branchcode = ? OR branchcode IS NULL )} if $branch_limit;
1565 $query .= qq{ ORDER BY lib};
1566 my $authorised_values_sth = $dbh->prepare( $query );
1567 foreach my $tag ( sort keys %{$tagslib} ) {
1568 if ( $tag ne '' ) {
1570 # loop through each subfield
1571 my $cntsubf;
1572 foreach my $subfield ( sort keys %{ $tagslib->{$tag} } ) {
1573 next if IsMarcStructureInternal($tagslib->{$tag}{$subfield});
1574 next unless ( $tagslib->{$tag}->{$subfield}->{'tab'} );
1575 next if ( $tagslib->{$tag}->{$subfield}->{'tab'} ne "10" );
1576 my %subfield_data;
1577 $subfield_data{tag} = $tag;
1578 $subfield_data{subfield} = $subfield;
1579 $subfield_data{countsubfield} = $cntsubf++;
1580 $subfield_data{kohafield} = $tagslib->{$tag}->{$subfield}->{'kohafield'};
1581 $subfield_data{id} = "tag_".$tag."_subfield_".$subfield."_".int(rand(1000000));
1583 # $subfield_data{marc_lib}=$tagslib->{$tag}->{$subfield}->{lib};
1584 $subfield_data{marc_lib} = $tagslib->{$tag}->{$subfield}->{lib};
1585 $subfield_data{mandatory} = $tagslib->{$tag}->{$subfield}->{mandatory};
1586 $subfield_data{repeatable} = $tagslib->{$tag}->{$subfield}->{repeatable};
1587 $subfield_data{hidden} = "display:none"
1588 if ( ( $tagslib->{$tag}->{$subfield}->{hidden} > 4 )
1589 || ( $tagslib->{$tag}->{$subfield}->{hidden} < -4 ) );
1590 my ( $x, $defaultvalue );
1591 if ($itemrecord) {
1592 ( $x, $defaultvalue ) = _find_value( $tag, $subfield, $itemrecord );
1594 $defaultvalue = $tagslib->{$tag}->{$subfield}->{defaultvalue} unless $defaultvalue;
1595 if ( !defined $defaultvalue ) {
1596 $defaultvalue = q||;
1597 } else {
1598 $defaultvalue =~ s/"/&quot;/g;
1601 my $maxlength = $tagslib->{$tag}->{$subfield}->{maxlength};
1603 # search for itemcallnumber if applicable
1604 if ( $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.itemcallnumber'
1605 && C4::Context->preference('itemcallnumber') && $itemrecord) {
1606 foreach my $itemcn_pref (split(/,/,C4::Context->preference('itemcallnumber'))){
1607 my $CNtag = substr( $itemcn_pref, 0, 3 );
1608 next unless my $field = $itemrecord->field($CNtag);
1609 my $CNsubfields = substr( $itemcn_pref, 3 );
1610 $defaultvalue = $field->as_string( $CNsubfields, ' ');
1611 last if $defaultvalue;
1614 if ( $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.itemcallnumber'
1615 && $defaultvalues
1616 && $defaultvalues->{'callnumber'} ) {
1617 if( $itemrecord and $defaultvalues and not $itemrecord->subfield($tag,$subfield) ){
1618 # if the item record exists, only use default value if the item has no callnumber
1619 $defaultvalue = $defaultvalues->{callnumber};
1620 } elsif ( !$itemrecord and $defaultvalues ) {
1621 # if the item record *doesn't* exists, always use the default value
1622 $defaultvalue = $defaultvalues->{callnumber};
1625 if ( ( $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.holdingbranch' || $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.homebranch' )
1626 && $defaultvalues
1627 && $defaultvalues->{'branchcode'} ) {
1628 if ( $itemrecord and $defaultvalues and not $itemrecord->subfield($tag,$subfield) ) {
1629 $defaultvalue = $defaultvalues->{branchcode};
1632 if ( ( $tagslib->{$tag}->{$subfield}->{kohafield} eq 'items.location' )
1633 && $defaultvalues
1634 && $defaultvalues->{'location'} ) {
1636 if ( $itemrecord and $defaultvalues and not $itemrecord->subfield($tag,$subfield) ) {
1637 # if the item record exists, only use default value if the item has no locationr
1638 $defaultvalue = $defaultvalues->{location};
1639 } elsif ( !$itemrecord and $defaultvalues ) {
1640 # if the item record *doesn't* exists, always use the default value
1641 $defaultvalue = $defaultvalues->{location};
1644 if ( $tagslib->{$tag}->{$subfield}->{authorised_value} ) {
1645 my @authorised_values;
1646 my %authorised_lib;
1648 # builds list, depending on authorised value...
1649 #---- branch
1650 if ( $tagslib->{$tag}->{$subfield}->{'authorised_value'} eq "branches" ) {
1651 if ( ( C4::Context->preference("IndependentBranches") )
1652 && !C4::Context->IsSuperLibrarian() ) {
1653 my $sth = $dbh->prepare( "SELECT branchcode,branchname FROM branches WHERE branchcode = ? ORDER BY branchname" );
1654 $sth->execute( C4::Context->userenv->{branch} );
1655 push @authorised_values, ""
1656 unless ( $tagslib->{$tag}->{$subfield}->{mandatory} );
1657 while ( my ( $branchcode, $branchname ) = $sth->fetchrow_array ) {
1658 push @authorised_values, $branchcode;
1659 $authorised_lib{$branchcode} = $branchname;
1661 } else {
1662 my $sth = $dbh->prepare( "SELECT branchcode,branchname FROM branches ORDER BY branchname" );
1663 $sth->execute;
1664 push @authorised_values, ""
1665 unless ( $tagslib->{$tag}->{$subfield}->{mandatory} );
1666 while ( my ( $branchcode, $branchname ) = $sth->fetchrow_array ) {
1667 push @authorised_values, $branchcode;
1668 $authorised_lib{$branchcode} = $branchname;
1672 $defaultvalue = C4::Context->userenv ? C4::Context->userenv->{branch} : undef;
1673 if ( $defaultvalues and $defaultvalues->{branchcode} ) {
1674 $defaultvalue = $defaultvalues->{branchcode};
1677 #----- itemtypes
1678 } elsif ( $tagslib->{$tag}->{$subfield}->{authorised_value} eq "itemtypes" ) {
1679 my $itemtypes = Koha::ItemTypes->search_with_localization;
1680 push @authorised_values, "";
1681 while ( my $itemtype = $itemtypes->next ) {
1682 push @authorised_values, $itemtype->itemtype;
1683 $authorised_lib{$itemtype->itemtype} = $itemtype->translated_description;
1685 if ($defaultvalues && $defaultvalues->{'itemtype'}) {
1686 $defaultvalue = $defaultvalues->{'itemtype'};
1689 #---- class_sources
1690 } elsif ( $tagslib->{$tag}->{$subfield}->{authorised_value} eq "cn_source" ) {
1691 push @authorised_values, "";
1693 my $class_sources = GetClassSources();
1694 my $default_source = $defaultvalue || C4::Context->preference("DefaultClassificationSource");
1696 foreach my $class_source (sort keys %$class_sources) {
1697 next unless $class_sources->{$class_source}->{'used'} or
1698 ($class_source eq $default_source);
1699 push @authorised_values, $class_source;
1700 $authorised_lib{$class_source} = $class_sources->{$class_source}->{'description'};
1703 $defaultvalue = $default_source;
1705 #---- "true" authorised value
1706 } else {
1707 $authorised_values_sth->execute(
1708 $tagslib->{$tag}->{$subfield}->{authorised_value},
1709 $branch_limit ? $branch_limit : ()
1711 push @authorised_values, "";
1712 while ( my ( $value, $lib ) = $authorised_values_sth->fetchrow_array ) {
1713 push @authorised_values, $value;
1714 $authorised_lib{$value} = $lib;
1717 $subfield_data{marc_value} = {
1718 type => 'select',
1719 values => \@authorised_values,
1720 default => $defaultvalue // q{},
1721 labels => \%authorised_lib,
1723 } elsif ( $tagslib->{$tag}->{$subfield}->{value_builder} ) {
1724 # it is a plugin
1725 require Koha::FrameworkPlugin;
1726 my $plugin = Koha::FrameworkPlugin->new({
1727 name => $tagslib->{$tag}->{$subfield}->{value_builder},
1728 item_style => 1,
1730 my $pars = { dbh => $dbh, record => undef, tagslib =>$tagslib, id => $subfield_data{id}, tabloop => undef };
1731 $plugin->build( $pars );
1732 if ( $itemrecord and my $field = $itemrecord->field($tag) ) {
1733 $defaultvalue = $field->subfield($subfield) || q{};
1735 if( !$plugin->errstr ) {
1736 #TODO Move html to template; see report 12176/13397
1737 my $tab= $plugin->noclick? '-1': '';
1738 my $class= $plugin->noclick? ' disabled': '';
1739 my $title= $plugin->noclick? 'No popup': 'Tag editor';
1740 $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;
1741 } else {
1742 warn $plugin->errstr;
1743 $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
1746 elsif ( $tag eq '' ) { # it's an hidden field
1747 $subfield_data{marc_value} = qq(<input type="hidden" id="$subfield_data{id}" name="field_value" class="input_marceditor" size="50" maxlength="$maxlength" value="$defaultvalue" />);
1749 elsif ( $tagslib->{$tag}->{$subfield}->{'hidden'} ) { # FIXME: shouldn't input type be "hidden" ?
1750 $subfield_data{marc_value} = qq(<input type="text" id="$subfield_data{id}" name="field_value" class="input_marceditor" size="50" maxlength="$maxlength" value="$defaultvalue" />);
1752 elsif ( length($defaultvalue) > 100
1753 or (C4::Context->preference("marcflavour") eq "UNIMARC" and
1754 300 <= $tag && $tag < 400 && $subfield eq 'a' )
1755 or (C4::Context->preference("marcflavour") eq "MARC21" and
1756 500 <= $tag && $tag < 600 )
1758 # oversize field (textarea)
1759 $subfield_data{marc_value} = qq(<textarea id="$subfield_data{id}" name="field_value" class="input_marceditor" size="50" maxlength="$maxlength">$defaultvalue</textarea>\n");
1760 } else {
1761 $subfield_data{marc_value} = qq(<input type="text" id="$subfield_data{id}" name="field_value" class="input_marceditor" size="50" maxlength="$maxlength" value="$defaultvalue" />);
1763 push( @loop_data, \%subfield_data );
1767 my $itemnumber;
1768 if ( $itemrecord && $itemrecord->field($itemtagfield) ) {
1769 $itemnumber = $itemrecord->subfield( $itemtagfield, $itemtagsubfield );
1771 return {
1772 'itemtagfield' => $itemtagfield,
1773 'itemtagsubfield' => $itemtagsubfield,
1774 'itemnumber' => $itemnumber,
1775 'iteminformation' => \@loop_data
1779 sub ToggleNewStatus {
1780 my ( $params ) = @_;
1781 my @rules = @{ $params->{rules} };
1782 my $report_only = $params->{report_only};
1784 my $dbh = C4::Context->dbh;
1785 my @errors;
1786 my @item_columns = map { "items.$_" } Koha::Items->columns;
1787 my @biblioitem_columns = map { "biblioitems.$_" } Koha::Biblioitems->columns;
1788 my $report;
1789 for my $rule ( @rules ) {
1790 my $age = $rule->{age};
1791 my $conditions = $rule->{conditions};
1792 my $substitutions = $rule->{substitutions};
1793 foreach ( @$substitutions ) {
1794 ( $_->{item_field} ) = ( $_->{field} =~ /items\.(.*)/ );
1796 my @params;
1798 my $query = q|
1799 SELECT items.*
1800 FROM items
1801 LEFT JOIN biblioitems ON biblioitems.biblionumber = items.biblionumber
1802 WHERE 1
1804 for my $condition ( @$conditions ) {
1805 if (
1806 grep { $_ eq $condition->{field} } @item_columns
1807 or grep { $_ eq $condition->{field} } @biblioitem_columns
1809 if ( $condition->{value} =~ /\|/ ) {
1810 my @values = split /\|/, $condition->{value};
1811 $query .= qq| AND $condition->{field} IN (|
1812 . join( ',', ('?') x scalar @values )
1813 . q|)|;
1814 push @params, @values;
1815 } else {
1816 $query .= qq| AND $condition->{field} = ?|;
1817 push @params, $condition->{value};
1821 if ( defined $age ) {
1822 $query .= q| AND TO_DAYS(NOW()) - TO_DAYS(dateaccessioned) >= ? |;
1823 push @params, $age;
1825 my $sth = $dbh->prepare($query);
1826 $sth->execute( @params );
1827 while ( my $values = $sth->fetchrow_hashref ) {
1828 my $biblionumber = $values->{biblionumber};
1829 my $itemnumber = $values->{itemnumber};
1830 my $item = Koha::Items->find($itemnumber);
1831 for my $substitution ( @$substitutions ) {
1832 my $field = $substitution->{item_field};
1833 my $value = $substitution->{value};
1834 next unless $substitution->{field};
1835 next if ( defined $values->{ $substitution->{item_field} } and $values->{ $substitution->{item_field} } eq $substitution->{value} );
1836 $item->$field($value);
1837 push @{ $report->{$itemnumber} }, $substitution;
1839 $item->store unless $report_only;
1843 return $report;