3 # Copyright 2007 LibLime, Inc.
5 # This file is part of Koha.
7 # Koha is free software; you can redistribute it and/or modify it under the
8 # terms of the GNU General Public License as published by the Free Software
9 # Foundation; either version 2 of the License, or (at your option) any later
12 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
13 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
14 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
16 # You should have received a copy of the GNU General Public License along with
17 # Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
18 # Suite 330, Boston, MA 02111-1307 USA
25 use C4::Dates qw/format_date format_date_in_iso/;
33 use vars qw($VERSION @ISA @EXPORT);
39 @ISA = qw( Exporter );
62 GetItemsByBiblioitemnumber
65 GetItemnumberFromBarcode
72 C4::Items - item management functions
76 This module contains an API for manipulating item
77 records in Koha, and is used by cataloguing, circulation,
78 acquisitions, and serials management.
80 A Koha item record is stored in two places: the
81 items table and embedded in a MARC tag in the XML
82 version of the associated bib record in C<biblioitems.marcxml>.
83 This is done to allow the item information to be readily
84 indexed (e.g., by Zebra), but means that each item
85 modification transaction must keep the items table
86 and the MARC XML in sync at all times.
88 Consequently, all code that creates, modifies, or deletes
89 item records B<must> use an appropriate function from
90 C<C4::Items>. If no existing function is suitable, it is
91 better to add one to C<C4::Items> than to use add
92 one-off SQL statements to add or modify items.
94 The items table will be considered authoritative. In other
95 words, if there is ever a discrepancy between the items
96 table and the MARC XML, the items table should be considered
99 =head1 HISTORICAL NOTE
101 Most of the functions in C<C4::Items> were originally in
102 the C<C4::Biblio> module.
104 =head1 CORE EXPORTED FUNCTIONS
106 The following functions are meant for use by users
115 $item = GetItem($itemnumber,$barcode,$serial);
119 Return item information, for a given itemnumber or barcode.
120 The return value is a hashref mapping item column
121 names to values. If C<$serial> is true, include serial publication data.
126 my ($itemnumber,$barcode, $serial) = @_;
127 my $dbh = C4::Context->dbh;
130 my $sth = $dbh->prepare("
132 WHERE itemnumber = ?");
133 $sth->execute($itemnumber);
134 $data = $sth->fetchrow_hashref;
136 my $sth = $dbh->prepare("
140 $sth->execute($barcode);
141 $data = $sth->fetchrow_hashref;
144 my $ssth = $dbh->prepare("SELECT serialseq,publisheddate from serialitems left join serial on serialitems.serialid=serial.serialid where serialitems.itemnumber=?");
145 $ssth->execute($data->{'itemnumber'}) ;
146 ($data->{'serialseq'} , $data->{'publisheddate'}) = $ssth->fetchrow_array();
147 warn $data->{'serialseq'} , $data->{'publisheddate'};
149 #if we don't have an items.itype, use biblioitems.itemtype.
150 if( ! $data->{'itype'} ) {
151 my $sth = $dbh->prepare("SELECT itemtype FROM biblioitems WHERE biblionumber = ?");
152 $sth->execute($data->{'biblionumber'});
153 ($data->{'itype'}) = $sth->fetchrow_array;
158 =head2 AddItemFromMarc
162 my ($biblionumber, $biblioitemnumber, $itemnumber)
163 = AddItemFromMarc($source_item_marc, $biblionumber);
167 Given a MARC::Record object containing an embedded item
168 record and a biblionumber, create a new item record.
172 sub AddItemFromMarc {
173 my ( $source_item_marc, $biblionumber ) = @_;
174 my $dbh = C4::Context->dbh;
176 # parse item hash from MARC
177 my $frameworkcode = GetFrameworkCode( $biblionumber );
178 my $item = &TransformMarcToKoha( $dbh, $source_item_marc, $frameworkcode );
179 my $unlinked_item_subfields = _get_unlinked_item_subfields($source_item_marc, $frameworkcode);
180 return AddItem($item, $biblionumber, $dbh, $frameworkcode, $unlinked_item_subfields);
187 my ($biblionumber, $biblioitemnumber, $itemnumber)
188 = AddItem($item, $biblionumber[, $dbh, $frameworkcode, $unlinked_item_subfields]);
192 Given a hash containing item column names as keys,
193 create a new Koha item record.
195 The first two optional parameters (C<$dbh> and C<$frameworkcode>)
196 do not need to be supplied for general use; they exist
197 simply to allow them to be picked up from AddItemFromMarc.
199 The final optional parameter, C<$unlinked_item_subfields>, contains
200 an arrayref containing subfields present in the original MARC
201 representation of the item (e.g., from the item editor) that are
202 not mapped to C<items> columns directly but should instead
203 be stored in C<items.more_subfields_xml> and included in
204 the biblio items tag for display and indexing.
210 my $biblionumber = shift;
212 my $dbh = @_ ? shift : C4::Context->dbh;
213 my $frameworkcode = @_ ? shift : GetFrameworkCode( $biblionumber );
214 my $unlinked_item_subfields;
216 $unlinked_item_subfields = shift
219 # needs old biblionumber and biblioitemnumber
220 $item->{'biblionumber'} = $biblionumber;
221 my $sth = $dbh->prepare("SELECT biblioitemnumber FROM biblioitems WHERE biblionumber=?");
222 $sth->execute( $item->{'biblionumber'} );
223 ($item->{'biblioitemnumber'}) = $sth->fetchrow;
225 _set_defaults_for_add($item);
226 _set_derived_columns_for_add($item);
227 $item->{'more_subfields_xml'} = _get_unlinked_subfields_xml($unlinked_item_subfields);
228 # FIXME - checks here
229 unless ( $item->{itype} ) { # default to biblioitem.itemtype if no itype
230 my $itype_sth = $dbh->prepare("SELECT itemtype FROM biblioitems WHERE biblionumber = ?");
231 $itype_sth->execute( $item->{'biblionumber'} );
232 ( $item->{'itype'} ) = $itype_sth->fetchrow_array;
235 my ( $itemnumber, $error ) = _koha_new_item( $item, $item->{barcode} );
236 $item->{'itemnumber'} = $itemnumber;
238 # create MARC tag representing item and add to bib
239 my $new_item_marc = _marc_from_item_hash($item, $frameworkcode, $unlinked_item_subfields);
240 _add_item_field_to_biblio($new_item_marc, $item->{'biblionumber'}, $frameworkcode );
242 logaction("CATALOGUING", "ADD", $itemnumber, "item") if C4::Context->preference("CataloguingLog");
244 return ($item->{biblionumber}, $item->{biblioitemnumber}, $itemnumber);
247 =head2 AddItemBatchFromMarc
251 ($itemnumber_ref, $error_ref) = AddItemBatchFromMarc($record, $biblionumber, $biblioitemnumber, $frameworkcode);
255 Efficiently create item records from a MARC biblio record with
256 embedded item fields. This routine is suitable for batch jobs.
258 This API assumes that the bib record has already been
259 saved to the C<biblio> and C<biblioitems> tables. It does
260 not expect that C<biblioitems.marc> and C<biblioitems.marcxml>
261 are populated, but it will do so via a call to ModBibiloMarc.
263 The goal of this API is to have a similar effect to using AddBiblio
264 and AddItems in succession, but without inefficient repeated
265 parsing of the MARC XML bib record.
267 This function returns an arrayref of new itemsnumbers and an arrayref of item
268 errors encountered during the processing. Each entry in the errors
269 list is a hashref containing the following keys:
275 Sequence number of original item tag in the MARC record.
279 Item barcode, provide to assist in the construction of
280 useful error messages.
282 =item error_condition
284 Code representing the error condition. Can be 'duplicate_barcode',
285 'invalid_homebranch', or 'invalid_holdingbranch'.
287 =item error_information
289 Additional information appropriate to the error condition.
295 sub AddItemBatchFromMarc {
296 my ($record, $biblionumber, $biblioitemnumber, $frameworkcode) = @_;
298 my @itemnumbers = ();
300 my $dbh = C4::Context->dbh;
302 # loop through the item tags and start creating items
303 my @bad_item_fields = ();
304 my ($itemtag, $itemsubfield) = &GetMarcFromKohaField("items.itemnumber",'');
305 my $item_sequence_num = 0;
306 ITEMFIELD: foreach my $item_field ($record->field($itemtag)) {
307 $item_sequence_num++;
308 # we take the item field and stick it into a new
309 # MARC record -- this is required so far because (FIXME)
310 # TransformMarcToKoha requires a MARC::Record, not a MARC::Field
311 # and there is no TransformMarcFieldToKoha
312 my $temp_item_marc = MARC::Record->new();
313 $temp_item_marc->append_fields($item_field);
315 # add biblionumber and biblioitemnumber
316 my $item = TransformMarcToKoha( $dbh, $temp_item_marc, $frameworkcode, 'items' );
317 my $unlinked_item_subfields = _get_unlinked_item_subfields($temp_item_marc, $frameworkcode);
318 $item->{'more_subfields_xml'} = _get_unlinked_subfields_xml($unlinked_item_subfields);
319 $item->{'biblionumber'} = $biblionumber;
320 $item->{'biblioitemnumber'} = $biblioitemnumber;
322 # check for duplicate barcode
323 my %item_errors = CheckItemPreSave($item);
325 push @errors, _repack_item_errors($item_sequence_num, $item, \%item_errors);
326 push @bad_item_fields, $item_field;
330 _set_defaults_for_add($item);
331 _set_derived_columns_for_add($item);
332 my ( $itemnumber, $error ) = _koha_new_item( $item, $item->{barcode} );
333 warn $error if $error;
334 push @itemnumbers, $itemnumber; # FIXME not checking error
335 $item->{'itemnumber'} = $itemnumber;
337 logaction("CATALOGUING", "ADD", $itemnumber, "item") if C4::Context->preference("CataloguingLog");
339 my $new_item_marc = _marc_from_item_hash($item, $frameworkcode, $unlinked_item_subfields);
340 $item_field->replace_with($new_item_marc->field($itemtag));
343 # remove any MARC item fields for rejected items
344 foreach my $item_field (@bad_item_fields) {
345 $record->delete_field($item_field);
348 # update the MARC biblio
349 $biblionumber = ModBiblioMarc( $record, $biblionumber, $frameworkcode );
351 return (\@itemnumbers, \@errors);
354 =head2 ModItemFromMarc
358 ModItemFromMarc($item_marc, $biblionumber, $itemnumber);
362 This function updates an item record based on a supplied
363 C<MARC::Record> object containing an embedded item field.
364 This API is meant for the use of C<additem.pl>; for
365 other purposes, C<ModItem> should be used.
367 This function uses the hash %default_values_for_mod_from_marc,
368 which contains default values for item fields to
369 apply when modifying an item. This is needed beccause
370 if an item field's value is cleared, TransformMarcToKoha
371 does not include the column in the
372 hash that's passed to ModItem, which without
373 use of this hash makes it impossible to clear
374 an item field's value. See bug 2466.
376 Note that only columns that can be directly
377 changed from the cataloging and serials
378 item editors are included in this hash.
382 my %default_values_for_mod_from_marc = (
384 booksellerid => undef,
386 'items.cn_source' => undef,
389 dateaccessioned => undef,
391 holdingbranch => undef,
393 itemcallnumber => undef,
402 replacementprice => undef,
403 replacementpricedate => undef,
410 sub ModItemFromMarc {
411 my $item_marc = shift;
412 my $biblionumber = shift;
413 my $itemnumber = shift;
415 my $dbh = C4::Context->dbh;
416 my $frameworkcode = GetFrameworkCode( $biblionumber );
417 my $item = &TransformMarcToKoha( $dbh, $item_marc, $frameworkcode );
418 foreach my $item_field (keys %default_values_for_mod_from_marc) {
419 $item->{$item_field} = $default_values_for_mod_from_marc{$item_field} unless exists $item->{$item_field};
421 my $unlinked_item_subfields = _get_unlinked_item_subfields($item_marc, $frameworkcode);
423 return ModItem($item, $biblionumber, $itemnumber, $dbh, $frameworkcode, $unlinked_item_subfields);
430 ModItem({ column => $newvalue }, $biblionumber, $itemnumber[, $original_item_marc]);
434 Change one or more columns in an item record and update
435 the MARC representation of the item.
437 The first argument is a hashref mapping from item column
438 names to the new values. The second and third arguments
439 are the biblionumber and itemnumber, respectively.
441 The fourth, optional parameter, C<$unlinked_item_subfields>, contains
442 an arrayref containing subfields present in the original MARC
443 representation of the item (e.g., from the item editor) that are
444 not mapped to C<items> columns directly but should instead
445 be stored in C<items.more_subfields_xml> and included in
446 the biblio items tag for display and indexing.
448 If one of the changed columns is used to calculate
449 the derived value of a column such as C<items.cn_sort>,
450 this routine will perform the necessary calculation
457 my $biblionumber = shift;
458 my $itemnumber = shift;
460 # if $biblionumber is undefined, get it from the current item
461 unless (defined $biblionumber) {
462 $biblionumber = _get_single_item_column('biblionumber', $itemnumber);
465 my $dbh = @_ ? shift : C4::Context->dbh;
466 my $frameworkcode = @_ ? shift : GetFrameworkCode( $biblionumber );
468 my $unlinked_item_subfields;
470 $unlinked_item_subfields = shift;
471 $item->{'more_subfields_xml'} = _get_unlinked_subfields_xml($unlinked_item_subfields);
474 $item->{'itemnumber'} = $itemnumber or return undef;
475 _set_derived_columns_for_mod($item);
476 _do_column_fixes_for_mod($item);
479 # attempt to change itemnumber
480 # attempt to change biblionumber (if we want
481 # an API to relink an item to a different bib,
482 # it should be a separate function)
485 _koha_modify_item($item);
487 # update biblio MARC XML
488 my $whole_item = GetItem($itemnumber) or die "FAILED GetItem($itemnumber)";
490 unless (defined $unlinked_item_subfields) {
491 $unlinked_item_subfields = _parse_unlinked_item_subfields_from_xml($whole_item->{'more_subfields_xml'});
493 my $new_item_marc = _marc_from_item_hash($whole_item, $frameworkcode, $unlinked_item_subfields)
494 or die "FAILED _marc_from_item_hash($whole_item, $frameworkcode)";
496 _replace_item_field_in_biblio($new_item_marc, $biblionumber, $itemnumber, $frameworkcode);
497 ($new_item_marc eq '0') and die "$new_item_marc is '0', not hashref"; # logaction line would crash anyway
498 logaction("CATALOGUING", "MODIFY", $itemnumber, $new_item_marc->as_formatted) if C4::Context->preference("CataloguingLog");
501 =head2 ModItemTransfer
505 ModItemTransfer($itenumber, $frombranch, $tobranch);
509 Marks an item as being transferred from one branch
514 sub ModItemTransfer {
515 my ( $itemnumber, $frombranch, $tobranch ) = @_;
517 my $dbh = C4::Context->dbh;
519 #new entry in branchtransfers....
520 my $sth = $dbh->prepare(
521 "INSERT INTO branchtransfers (itemnumber, frombranch, datesent, tobranch)
522 VALUES (?, ?, NOW(), ?)");
523 $sth->execute($itemnumber, $frombranch, $tobranch);
525 ModItem({ holdingbranch => $tobranch }, undef, $itemnumber);
526 ModDateLastSeen($itemnumber);
530 =head2 ModDateLastSeen
534 ModDateLastSeen($itemnum);
538 Mark item as seen. Is called when an item is issued, returned or manually marked during inventory/stocktaking.
539 C<$itemnum> is the item number
543 sub ModDateLastSeen {
544 my ($itemnumber) = @_;
546 my $today = C4::Dates->new();
547 ModItem({ itemlost => 0, datelastseen => $today->output("iso") }, undef, $itemnumber);
554 DelItem($biblionumber, $itemnumber);
558 Exported function (core API) for deleting an item record in Koha.
563 my ( $dbh, $biblionumber, $itemnumber ) = @_;
565 # FIXME check the item has no current issues
567 _koha_delete_item( $dbh, $itemnumber );
569 # get the MARC record
570 my $record = GetMarcBiblio($biblionumber);
571 my $frameworkcode = GetFrameworkCode($biblionumber);
574 my $copy2deleted = $dbh->prepare("UPDATE deleteditems SET marc=? WHERE itemnumber=?");
575 $copy2deleted->execute( $record->as_usmarc(), $itemnumber );
577 #search item field code
578 my ( $itemtag, $itemsubfield ) = GetMarcFromKohaField("items.itemnumber",$frameworkcode);
579 my @fields = $record->field($itemtag);
581 # delete the item specified
582 foreach my $field (@fields) {
583 if ( $field->subfield($itemsubfield) eq $itemnumber ) {
584 $record->delete_field($field);
587 &ModBiblioMarc( $record, $biblionumber, $frameworkcode );
588 logaction("CATALOGUING", "DELETE", $itemnumber, "item") if C4::Context->preference("CataloguingLog");
595 DelItemCheck($dbh, $biblionumber, $itemnumber);
599 Exported function (core API) for deleting an item record in Koha if there no current issue.
604 my ( $dbh, $biblionumber, $itemnumber ) = @_;
607 # check that there is no issue on this item before deletion.
608 my $sth=$dbh->prepare("select * from issues i where i.itemnumber=?");
609 $sth->execute($itemnumber);
611 my $onloan=$sth->fetchrow;
615 $error = "book_on_loan"
617 # check it doesnt have a waiting reserve
618 $sth=$dbh->prepare("SELECT * FROM reserves WHERE found = 'W' AND itemnumber = ?");
619 $sth->execute($itemnumber);
620 my $reserve=$sth->fetchrow;
623 $error = "book_reserved";
625 DelItem($dbh, $biblionumber, $itemnumber);
632 =head2 CheckItemPreSave
636 my $item_ref = TransformMarcToKoha($marc, 'items');
638 my %errors = CheckItemPreSave($item_ref);
639 if (exists $errors{'duplicate_barcode'}) {
640 print "item has duplicate barcode: ", $errors{'duplicate_barcode'}, "\n";
641 } elsif (exists $errors{'invalid_homebranch'}) {
642 print "item has invalid home branch: ", $errors{'invalid_homebranch'}, "\n";
643 } elsif (exists $errors{'invalid_holdingbranch'}) {
644 print "item has invalid holding branch: ", $errors{'invalid_holdingbranch'}, "\n";
651 Given a hashref containing item fields, determine if it can be
652 inserted or updated in the database. Specifically, checks for
653 database integrity issues, and returns a hash containing any
654 of the following keys, if applicable.
658 =item duplicate_barcode
660 Barcode, if it duplicates one already found in the database.
662 =item invalid_homebranch
664 Home branch, if not defined in branches table.
666 =item invalid_holdingbranch
668 Holding branch, if not defined in branches table.
672 This function does NOT implement any policy-related checks,
673 e.g., whether current operator is allowed to save an
674 item that has a given branch code.
678 sub CheckItemPreSave {
679 my $item_ref = shift;
683 # check for duplicate barcode
684 if (exists $item_ref->{'barcode'} and defined $item_ref->{'barcode'}) {
685 my $existing_itemnumber = GetItemnumberFromBarcode($item_ref->{'barcode'});
686 if ($existing_itemnumber) {
687 if (!exists $item_ref->{'itemnumber'} # new item
688 or $item_ref->{'itemnumber'} != $existing_itemnumber) { # existing item
689 $errors{'duplicate_barcode'} = $item_ref->{'barcode'};
694 # check for valid home branch
695 if (exists $item_ref->{'homebranch'} and defined $item_ref->{'homebranch'}) {
696 my $branch_name = GetBranchName($item_ref->{'homebranch'});
697 unless (defined $branch_name) {
698 # relies on fact that branches.branchname is a non-NULL column,
699 # so GetBranchName returns undef only if branch does not exist
700 $errors{'invalid_homebranch'} = $item_ref->{'homebranch'};
704 # check for valid holding branch
705 if (exists $item_ref->{'holdingbranch'} and defined $item_ref->{'holdingbranch'}) {
706 my $branch_name = GetBranchName($item_ref->{'holdingbranch'});
707 unless (defined $branch_name) {
708 # relies on fact that branches.branchname is a non-NULL column,
709 # so GetBranchName returns undef only if branch does not exist
710 $errors{'invalid_holdingbranch'} = $item_ref->{'holdingbranch'};
718 =head1 EXPORTED SPECIAL ACCESSOR FUNCTIONS
720 The following functions provide various ways of
721 getting an item record, a set of item records, or
722 lists of authorized values for certain item fields.
724 Some of the functions in this group are candidates
725 for refactoring -- for example, some of the code
726 in C<GetItemsByBiblioitemnumber> and C<GetItemsInfo>
727 has copy-and-paste work.
735 $itemstatushash = GetItemStatus($fwkcode);
739 Returns a list of valid values for the
740 C<items.notforloan> field.
742 NOTE: does B<not> return an individual item's
745 Can be MARC dependant.
747 But basically could be can be loan or not
748 Create a status selector with the following code
750 =head3 in PERL SCRIPT
754 my $itemstatushash = getitemstatus;
756 foreach my $thisstatus (keys %$itemstatushash) {
757 my %row =(value => $thisstatus,
758 statusname => $itemstatushash->{$thisstatus}->{'statusname'},
760 push @itemstatusloop, \%row;
762 $template->param(statusloop=>\@itemstatusloop);
770 <select name="statusloop">
771 <option value="">Default</option>
772 <!-- TMPL_LOOP name="statusloop" -->
773 <option value="<!-- TMPL_VAR name="value" -->" <!-- TMPL_IF name="selected" -->selected<!-- /TMPL_IF -->><!-- TMPL_VAR name="statusname" --></option>
783 # returns a reference to a hash of references to status...
786 my $dbh = C4::Context->dbh;
788 $fwk = '' unless ($fwk);
789 my ( $tag, $subfield ) =
790 GetMarcFromKohaField( "items.notforloan", $fwk );
791 if ( $tag and $subfield ) {
794 "SELECT authorised_value
795 FROM marc_subfield_structure
801 $sth->execute( $tag, $subfield, $fwk );
802 if ( my ($authorisedvaluecat) = $sth->fetchrow ) {
805 "SELECT authorised_value,lib
806 FROM authorised_values
811 $authvalsth->execute($authorisedvaluecat);
812 while ( my ( $authorisedvalue, $lib ) = $authvalsth->fetchrow ) {
813 $itemstatus{$authorisedvalue} = $lib;
829 $itemstatus{"1"} = "Not For Loan";
833 =head2 GetItemLocation
837 $itemlochash = GetItemLocation($fwk);
841 Returns a list of valid values for the
842 C<items.location> field.
844 NOTE: does B<not> return an individual item's
847 where fwk stands for an optional framework code.
848 Create a location selector with the following code
850 =head3 in PERL SCRIPT
854 my $itemlochash = getitemlocation;
856 foreach my $thisloc (keys %$itemlochash) {
857 my $selected = 1 if $thisbranch eq $branch;
858 my %row =(locval => $thisloc,
859 selected => $selected,
860 locname => $itemlochash->{$thisloc},
862 push @itemlocloop, \%row;
864 $template->param(itemlocationloop => \@itemlocloop);
872 <select name="location">
873 <option value="">Default</option>
874 <!-- TMPL_LOOP name="itemlocationloop" -->
875 <option value="<!-- TMPL_VAR name="locval" -->" <!-- TMPL_IF name="selected" -->selected<!-- /TMPL_IF -->><!-- TMPL_VAR name="locname" --></option>
883 sub GetItemLocation {
885 # returns a reference to a hash of references to location...
888 my $dbh = C4::Context->dbh;
890 $fwk = '' unless ($fwk);
891 my ( $tag, $subfield ) =
892 GetMarcFromKohaField( "items.location", $fwk );
893 if ( $tag and $subfield ) {
896 "SELECT authorised_value
897 FROM marc_subfield_structure
902 $sth->execute( $tag, $subfield, $fwk );
903 if ( my ($authorisedvaluecat) = $sth->fetchrow ) {
906 "SELECT authorised_value,lib
907 FROM authorised_values
911 $authvalsth->execute($authorisedvaluecat);
912 while ( my ( $authorisedvalue, $lib ) = $authvalsth->fetchrow ) {
913 $itemlocation{$authorisedvalue} = $lib;
916 return \%itemlocation;
929 $itemlocation{"1"} = "Not For Loan";
930 return \%itemlocation;
937 $items = GetLostItems( $where, $orderby );
941 This function gets a list of lost items.
947 C<$where> is a hashref. it containts a field of the items table as key
948 and the value to match as value. For example:
950 { barcode => 'abc123',
951 homebranch => 'CPL', }
953 C<$orderby> is a field of the items table by which the resultset
958 C<$items> is a reference to an array full of hashrefs with columns
959 from the "items" table as keys.
961 =item usage in the perl script:
963 my $where = { barcode => '0001548' };
964 my $items = GetLostItems( $where, "homebranch" );
965 $template->param( itemsloop => $items );
972 # Getting input args.
975 my $dbh = C4::Context->dbh;
980 LEFT JOIN biblio ON (items.biblionumber = biblio.biblionumber)
981 LEFT JOIN biblioitems ON (items.biblionumber = biblioitems.biblionumber)
982 LEFT JOIN authorised_values ON (items.itemlost = authorised_values.authorised_value)
984 authorised_values.category = 'LOST'
985 AND itemlost IS NOT NULL
988 my @query_parameters;
989 foreach my $key (keys %$where) {
990 $query .= " AND $key LIKE ?";
991 push @query_parameters, "%$where->{$key}%";
993 my @ordervalues = qw/title author homebranch itype barcode price replacementprice lib datelastseen location/;
995 if ( defined $orderby && grep($orderby, @ordervalues)) {
996 $query .= ' ORDER BY '.$orderby;
999 my $sth = $dbh->prepare($query);
1000 $sth->execute( @query_parameters );
1002 while ( my $row = $sth->fetchrow_hashref ){
1008 =head2 GetItemsForInventory
1012 $itemlist = GetItemsForInventory($minlocation, $maxlocation, $location, $itemtype $datelastseen, $branch, $offset, $size, $statushash);
1016 Retrieve a list of title/authors/barcode/callnumber, for biblio inventory.
1018 The sub returns a reference to a list of hashes, each containing
1019 itemnumber, author, title, barcode, item callnumber, and date last
1020 seen. It is ordered by callnumber then title.
1022 The required minlocation & maxlocation parameters are used to specify a range of item callnumbers
1023 the datelastseen can be used to specify that you want to see items not seen since a past date only.
1024 offset & size can be used to retrieve only a part of the whole listing (defaut behaviour)
1026 $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.
1030 sub GetItemsForInventory {
1031 my ( $minlocation, $maxlocation,$location, $itemtype, $ignoreissued, $datelastseen, $branch, $offset, $size, $statushash ) = @_;
1032 my $dbh = C4::Context->dbh;
1033 my ( @bind_params, @where_strings );
1035 my $query = <<'END_SQL';
1036 SELECT items.itemnumber, barcode, itemcallnumber, title, author, biblio.biblionumber, datelastseen
1038 LEFT JOIN biblio ON items.biblionumber = biblio.biblionumber
1039 LEFT JOIN biblioitems on items.biblionumber = biblioitems.biblionumber
1043 push @where_strings, 'itemcallnumber >= ?';
1044 push @bind_params, $minlocation;
1048 push @where_strings, 'itemcallnumber <= ?';
1049 push @bind_params, $maxlocation;
1052 if ($datelastseen) {
1053 $datelastseen = format_date_in_iso($datelastseen);
1054 push @where_strings, '(datelastseen < ? OR datelastseen IS NULL)';
1055 push @bind_params, $datelastseen;
1059 push @where_strings, 'items.location = ?';
1060 push @bind_params, $location;
1064 push @where_strings, 'items.homebranch = ?';
1065 push @bind_params, $branch;
1067 if ( $ignoreissued) {
1068 $query .= "LEFT JOIN issues ON items.itemnumber = issues.itemnumber ";
1069 push @where_strings, 'issues.date_due IS NULL';
1072 for my $authvfield (keys %$statushash){
1073 if ( scalar @{$statushash->{$authvfield}} > 0 ){
1074 my $joinedvals = join ',', @{$statushash->{$authvfield}};
1075 push @where_strings, "$authvfield in (" . $joinedvals . ")";
1080 push @where_strings, 'biblioitems.itemtype = ?';
1081 push @bind_params, $itemtype;
1083 if ( @where_strings ) {
1085 $query .= join ' AND ', @where_strings;
1087 $query .= ' ORDER BY itemcallnumber, title';
1088 my $sth = $dbh->prepare($query);
1089 $sth->execute( @bind_params );
1093 while ( my $row = $sth->fetchrow_hashref ) {
1094 $offset-- if ($offset);
1095 $row->{datelastseen}=format_date($row->{datelastseen});
1096 if ( ( !$offset ) && $size ) {
1097 push @results, $row;
1104 =head2 GetItemsCount
1107 $count = &GetItemsCount( $biblionumber);
1111 This function return count of item with $biblionumber
1116 my ( $biblionumber ) = @_;
1117 my $dbh = C4::Context->dbh;
1118 my $query = "SELECT count(*)
1120 WHERE biblionumber=?";
1121 my $sth = $dbh->prepare($query);
1122 $sth->execute($biblionumber);
1123 my $count = $sth->fetchrow;
1128 =head2 GetItemInfosOf
1132 GetItemInfosOf(@itemnumbers);
1138 sub GetItemInfosOf {
1139 my @itemnumbers = @_;
1144 WHERE itemnumber IN (' . join( ',', @itemnumbers ) . ')
1146 return get_infos_of( $query, 'itemnumber' );
1149 =head2 GetItemsByBiblioitemnumber
1153 GetItemsByBiblioitemnumber($biblioitemnumber);
1157 Returns an arrayref of hashrefs suitable for use in a TMPL_LOOP
1158 Called by C<C4::XISBN>
1162 sub GetItemsByBiblioitemnumber {
1163 my ( $bibitem ) = @_;
1164 my $dbh = C4::Context->dbh;
1165 my $sth = $dbh->prepare("SELECT * FROM items WHERE items.biblioitemnumber = ?") || die $dbh->errstr;
1166 # Get all items attached to a biblioitem
1169 $sth->execute($bibitem) || die $sth->errstr;
1170 while ( my $data = $sth->fetchrow_hashref ) {
1171 # Foreach item, get circulation information
1172 my $sth2 = $dbh->prepare( "SELECT * FROM issues,borrowers
1173 WHERE itemnumber = ?
1174 AND issues.borrowernumber = borrowers.borrowernumber"
1176 $sth2->execute( $data->{'itemnumber'} );
1177 if ( my $data2 = $sth2->fetchrow_hashref ) {
1178 # if item is out, set the due date and who it is out too
1179 $data->{'date_due'} = $data2->{'date_due'};
1180 $data->{'cardnumber'} = $data2->{'cardnumber'};
1181 $data->{'borrowernumber'} = $data2->{'borrowernumber'};
1184 # set date_due to blank, so in the template we check itemlost, and wthdrawn
1185 $data->{'date_due'} = '';
1188 # Find the last 3 people who borrowed this item.
1189 my $query2 = "SELECT * FROM old_issues, borrowers WHERE itemnumber = ?
1190 AND old_issues.borrowernumber = borrowers.borrowernumber
1191 ORDER BY returndate desc,timestamp desc LIMIT 3";
1192 $sth2 = $dbh->prepare($query2) || die $dbh->errstr;
1193 $sth2->execute( $data->{'itemnumber'} ) || die $sth2->errstr;
1195 while ( my $data2 = $sth2->fetchrow_hashref ) {
1196 $data->{"timestamp$i2"} = $data2->{'timestamp'};
1197 $data->{"card$i2"} = $data2->{'cardnumber'};
1198 $data->{"borrower$i2"} = $data2->{'borrowernumber'};
1202 push(@results,$data);
1212 @results = GetItemsInfo($biblionumber, $type);
1216 Returns information about books with the given biblionumber.
1218 C<$type> may be either C<intra> or anything else. If it is not set to
1219 C<intra>, then the search will exclude lost, very overdue, and
1222 C<GetItemsInfo> returns a list of references-to-hash. Each element
1223 contains a number of keys. Most of them are table items from the
1224 C<biblio>, C<biblioitems>, C<items>, and C<itemtypes> tables in the
1225 Koha database. Other keys include:
1229 =item C<$data-E<gt>{branchname}>
1231 The name (not the code) of the branch to which the book belongs.
1233 =item C<$data-E<gt>{datelastseen}>
1235 This is simply C<items.datelastseen>, except that while the date is
1236 stored in YYYY-MM-DD format in the database, here it is converted to
1237 DD/MM/YYYY format. A NULL date is returned as C<//>.
1239 =item C<$data-E<gt>{datedue}>
1241 =item C<$data-E<gt>{class}>
1243 This is the concatenation of C<biblioitems.classification>, the book's
1244 Dewey code, and C<biblioitems.subclass>.
1246 =item C<$data-E<gt>{ocount}>
1248 I think this is the number of copies of the book available.
1250 =item C<$data-E<gt>{order}>
1252 If this is set, it is set to C<One Order>.
1259 my ( $biblionumber, $type ) = @_;
1260 my $dbh = C4::Context->dbh;
1261 # note biblioitems.* must be avoided to prevent large marc and marcxml fields from killing performance.
1267 biblioitems.itemtype,
1270 biblioitems.publicationyear,
1271 biblioitems.publishercode,
1272 biblioitems.volumedate,
1273 biblioitems.volumedesc,
1276 items.notforloan as itemnotforloan,
1277 itemtypes.description
1279 LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber
1280 LEFT JOIN biblioitems ON biblioitems.biblioitemnumber = items.biblioitemnumber
1281 LEFT JOIN itemtypes ON itemtypes.itemtype = "
1282 . (C4::Context->preference('item-level_itypes') ? 'items.itype' : 'biblioitems.itemtype');
1283 $query .= " WHERE items.biblionumber = ? ORDER BY items.dateaccessioned desc" ;
1284 my $sth = $dbh->prepare($query);
1285 $sth->execute($biblionumber);
1290 my $isth = $dbh->prepare(
1291 "SELECT issues.*,borrowers.cardnumber,borrowers.surname,borrowers.firstname,borrowers.branchcode as bcode
1292 FROM issues LEFT JOIN borrowers ON issues.borrowernumber=borrowers.borrowernumber
1293 WHERE itemnumber = ?"
1295 my $ssth = $dbh->prepare("SELECT serialseq,publisheddate from serialitems left join serial on serialitems.serialid=serial.serialid where serialitems.itemnumber=? ");
1296 while ( my $data = $sth->fetchrow_hashref ) {
1299 $isth->execute( $data->{'itemnumber'} );
1300 if ( my $idata = $isth->fetchrow_hashref ) {
1302 if (C4::Context->preference("IndependantBranches")){
1303 my $userenv = C4::Context->userenv;
1304 if ( ($userenv) && ( $userenv->{flags} % 2 != 1 ) ) {
1305 $data->{'NOTSAMEBRANCH'} = 1 if ($idata->{'bcode'} ne $userenv->{branch});
1309 if ( $data->{'serial'}) {
1310 $ssth->execute($data->{'itemnumber'}) ;
1311 ($data->{'serialseq'} , $data->{'publisheddate'}) = $ssth->fetchrow_array();
1314 if ( $datedue eq '' ) {
1315 my ( $restype, $reserves ) =
1316 C4::Reserves::CheckReserves( $data->{'itemnumber'} );
1317 # Previous conditional check with if ($restype) is not needed because a true
1318 # result for one item will result in subsequent items defaulting to this true
1320 $count_reserves = $restype;
1324 #get branch information.....
1325 my $bsth = $dbh->prepare(
1326 "SELECT * FROM branches WHERE branchcode = ?
1329 $bsth->execute( $data->{'holdingbranch'} );
1330 if ( my $bdata = $bsth->fetchrow_hashref ) {
1331 $data->{'branchname'} = $bdata->{'branchname'};
1333 $data->{'datedue'} = $datedue;
1334 $data->{'count_reserves'} = $count_reserves;
1336 # get notforloan complete status if applicable
1337 my $sthnflstatus = $dbh->prepare(
1338 'SELECT authorised_value
1339 FROM marc_subfield_structure
1340 WHERE kohafield="items.notforloan"
1344 $sthnflstatus->execute;
1345 my ($authorised_valuecode) = $sthnflstatus->fetchrow;
1346 if ($authorised_valuecode) {
1347 $sthnflstatus = $dbh->prepare(
1348 "SELECT lib FROM authorised_values
1350 AND authorised_value=?"
1352 $sthnflstatus->execute( $authorised_valuecode,
1353 $data->{itemnotforloan} );
1354 my ($lib) = $sthnflstatus->fetchrow;
1355 $data->{notforloanvalue} = $lib;
1357 $data->{itypenotforloan} = $data->{notforloan} if (C4::Context->preference('item-level_itypes'));
1359 # my stack procedures
1360 my $stackstatus = $dbh->prepare(
1361 'SELECT authorised_value
1362 FROM marc_subfield_structure
1363 WHERE kohafield="items.stack"
1366 $stackstatus->execute;
1368 ($authorised_valuecode) = $stackstatus->fetchrow;
1369 if ($authorised_valuecode) {
1370 $stackstatus = $dbh->prepare(
1372 FROM authorised_values
1374 AND authorised_value=?
1377 $stackstatus->execute( $authorised_valuecode, $data->{stack} );
1378 my ($lib) = $stackstatus->fetchrow;
1379 $data->{stack} = $lib;
1381 # Find the last 3 people who borrowed this item.
1382 my $sth2 = $dbh->prepare("SELECT * FROM old_issues,borrowers
1383 WHERE itemnumber = ?
1384 AND old_issues.borrowernumber = borrowers.borrowernumber
1385 ORDER BY returndate DESC
1387 $sth2->execute($data->{'itemnumber'});
1389 while (my $data2 = $sth2->fetchrow_hashref()) {
1390 $data->{"timestamp$ii"} = $data2->{'timestamp'} if $data2->{'timestamp'};
1391 $data->{"card$ii"} = $data2->{'cardnumber'} if $data2->{'cardnumber'};
1392 $data->{"borrower$ii"} = $data2->{'borrowernumber'} if $data2->{'borrowernumber'};
1396 $results[$i] = $data;
1400 return( sort { ($b->{'publisheddate'} || $b->{'enumchron'}) cmp ($a->{'publisheddate'} || $a->{'enumchron'}) } @results );
1406 =head2 get_itemnumbers_of
1410 my @itemnumbers_of = get_itemnumbers_of(@biblionumbers);
1414 Given a list of biblionumbers, return the list of corresponding itemnumbers
1415 for each biblionumber.
1417 Return a reference on a hash where keys are biblionumbers and values are
1418 references on array of itemnumbers.
1422 sub get_itemnumbers_of {
1423 my @biblionumbers = @_;
1425 my $dbh = C4::Context->dbh;
1431 WHERE biblionumber IN (?' . ( ',?' x scalar @biblionumbers - 1 ) . ')
1433 my $sth = $dbh->prepare($query);
1434 $sth->execute(@biblionumbers);
1438 while ( my ( $itemnumber, $biblionumber ) = $sth->fetchrow_array ) {
1439 push @{ $itemnumbers_of{$biblionumber} }, $itemnumber;
1442 return \%itemnumbers_of;
1445 =head2 GetItemnumberFromBarcode
1449 $result = GetItemnumberFromBarcode($barcode);
1455 sub GetItemnumberFromBarcode {
1457 my $dbh = C4::Context->dbh;
1460 $dbh->prepare("SELECT itemnumber FROM items WHERE items.barcode=?");
1461 $rq->execute($barcode);
1462 my ($result) = $rq->fetchrow;
1466 =head3 get_item_authorised_values
1468 find the types and values for all authorised values assigned to this item.
1473 returns: a hashref malling the authorised value to the value set for this itemnumber
1475 $authorised_values = {
1481 'RESTRICTED' => undef,
1484 'branches' => 'CPL',
1485 'cn_source' => undef,
1486 'itemtypes' => 'SER',
1489 Notes: see C4::Biblio::get_biblio_authorised_values for a similar method at the biblio level.
1493 sub get_item_authorised_values {
1494 my $itemnumber = shift;
1496 # assume that these entries in the authorised_value table are item level.
1497 my $query = q(SELECT distinct authorised_value, kohafield
1498 FROM marc_subfield_structure
1499 WHERE kohafield like 'item%'
1500 AND authorised_value != '' );
1502 my $itemlevel_authorised_values = C4::Context->dbh->selectall_hashref( $query, 'authorised_value' );
1503 my $iteminfo = GetItem( $itemnumber );
1504 # warn( Data::Dumper->Dump( [ $itemlevel_authorised_values ], [ 'itemlevel_authorised_values' ] ) );
1506 foreach my $this_authorised_value ( keys %$itemlevel_authorised_values ) {
1507 my $field = $itemlevel_authorised_values->{ $this_authorised_value }->{'kohafield'};
1508 $field =~ s/^items\.//;
1509 if ( exists $iteminfo->{ $field } ) {
1510 $return->{ $this_authorised_value } = $iteminfo->{ $field };
1513 # warn( Data::Dumper->Dump( [ $return ], [ 'return' ] ) );
1517 =head3 get_authorised_value_images
1519 find a list of icons that are appropriate for display based on the
1520 authorised values for a biblio.
1522 parameters: listref of authorised values, such as comes from
1523 get_item_ahtorised_values or
1524 from C4::Biblio::get_biblio_authorised_values
1526 returns: listref of hashrefs for each image. Each hashref looks like
1529 { imageurl => '/intranet-tmpl/prog/img/itemtypeimg/npl/WEB.gif',
1534 Notes: Currently, I put on the full path to the images on the staff
1535 side. This should either be configurable or not done at all. Since I
1536 have to deal with 'intranet' or 'opac' in
1537 get_biblio_authorised_values, perhaps I should be passing it in.
1541 sub get_authorised_value_images {
1542 my $authorised_values = shift;
1546 my $authorised_value_list = GetAuthorisedValues();
1547 # warn ( Data::Dumper->Dump( [ $authorised_value_list ], [ 'authorised_value_list' ] ) );
1548 foreach my $this_authorised_value ( @$authorised_value_list ) {
1549 if ( exists $authorised_values->{ $this_authorised_value->{'category'} }
1550 && $authorised_values->{ $this_authorised_value->{'category'} } eq $this_authorised_value->{'authorised_value'} ) {
1551 # warn ( Data::Dumper->Dump( [ $this_authorised_value ], [ 'this_authorised_value' ] ) );
1552 if ( defined $this_authorised_value->{'imageurl'} ) {
1553 push @imagelist, { imageurl => C4::Koha::getitemtypeimagelocation( 'intranet', $this_authorised_value->{'imageurl'} ),
1554 label => $this_authorised_value->{'lib'},
1555 category => $this_authorised_value->{'category'},
1556 value => $this_authorised_value->{'authorised_value'}, };
1561 # warn ( Data::Dumper->Dump( [ \@imagelist ], [ 'imagelist' ] ) );
1566 =head1 LIMITED USE FUNCTIONS
1568 The following functions, while part of the public API,
1569 are not exported. This is generally because they are
1570 meant to be used by only one script for a specific
1571 purpose, and should not be used in any other context
1572 without careful thought.
1580 my $item_marc = GetMarcItem($biblionumber, $itemnumber);
1584 Returns MARC::Record of the item passed in parameter.
1585 This function is meant for use only in C<cataloguing/additem.pl>,
1586 where it is needed to support that script's MARC-like
1592 my ( $biblionumber, $itemnumber ) = @_;
1594 # GetMarcItem has been revised so that it does the following:
1595 # 1. Gets the item information from the items table.
1596 # 2. Converts it to a MARC field for storage in the bib record.
1598 # The previous behavior was:
1599 # 1. Get the bib record.
1600 # 2. Return the MARC tag corresponding to the item record.
1602 # The difference is that one treats the items row as authoritative,
1603 # while the other treats the MARC representation as authoritative
1604 # under certain circumstances.
1606 my $itemrecord = GetItem($itemnumber);
1608 # Tack on 'items.' prefix to column names so that TransformKohaToMarc will work.
1609 # Also, don't emit a subfield if the underlying field is blank.
1612 defined($itemrecord->{$_}) && $itemrecord->{$_} ne '' ? ("items.$_" => $itemrecord->{$_}) : ()
1613 } keys %{ $itemrecord }
1615 my $itemmarc = TransformKohaToMarc($mungeditem);
1617 my $unlinked_item_subfields = _parse_unlinked_item_subfields_from_xml($mungeditem->{'items.more_subfields_xml'});
1618 if (defined $unlinked_item_subfields and $#$unlinked_item_subfields > -1) {
1619 my @fields = $itemmarc->fields();
1620 if ($#fields > -1) {
1621 $fields[0]->add_subfields(@$unlinked_item_subfields);
1629 =head1 PRIVATE FUNCTIONS AND VARIABLES
1631 The following functions are not meant to be called
1632 directly, but are documented in order to explain
1633 the inner workings of C<C4::Items>.
1637 =head2 %derived_columns
1639 This hash keeps track of item columns that
1640 are strictly derived from other columns in
1641 the item record and are not meant to be set
1644 Each key in the hash should be the name of a
1645 column (as named by TransformMarcToKoha). Each
1646 value should be hashref whose keys are the
1647 columns on which the derived column depends. The
1648 hashref should also contain a 'BUILDER' key
1649 that is a reference to a sub that calculates
1654 my %derived_columns = (
1655 'items.cn_sort' => {
1656 'itemcallnumber' => 1,
1657 'items.cn_source' => 1,
1658 'BUILDER' => \&_calc_items_cn_sort,
1662 =head2 _set_derived_columns_for_add
1666 _set_derived_column_for_add($item);
1670 Given an item hash representing a new item to be added,
1671 calculate any derived columns. Currently the only
1672 such column is C<items.cn_sort>.
1676 sub _set_derived_columns_for_add {
1679 foreach my $column (keys %derived_columns) {
1680 my $builder = $derived_columns{$column}->{'BUILDER'};
1681 my $source_values = {};
1682 foreach my $source_column (keys %{ $derived_columns{$column} }) {
1683 next if $source_column eq 'BUILDER';
1684 $source_values->{$source_column} = $item->{$source_column};
1686 $builder->($item, $source_values);
1690 =head2 _set_derived_columns_for_mod
1694 _set_derived_column_for_mod($item);
1698 Given an item hash representing a new item to be modified.
1699 calculate any derived columns. Currently the only
1700 such column is C<items.cn_sort>.
1702 This routine differs from C<_set_derived_columns_for_add>
1703 in that it needs to handle partial item records. In other
1704 words, the caller of C<ModItem> may have supplied only one
1705 or two columns to be changed, so this function needs to
1706 determine whether any of the columns to be changed affect
1707 any of the derived columns. Also, if a derived column
1708 depends on more than one column, but the caller is not
1709 changing all of then, this routine retrieves the unchanged
1710 values from the database in order to ensure a correct
1715 sub _set_derived_columns_for_mod {
1718 foreach my $column (keys %derived_columns) {
1719 my $builder = $derived_columns{$column}->{'BUILDER'};
1720 my $source_values = {};
1721 my %missing_sources = ();
1722 my $must_recalc = 0;
1723 foreach my $source_column (keys %{ $derived_columns{$column} }) {
1724 next if $source_column eq 'BUILDER';
1725 if (exists $item->{$source_column}) {
1727 $source_values->{$source_column} = $item->{$source_column};
1729 $missing_sources{$source_column} = 1;
1733 foreach my $source_column (keys %missing_sources) {
1734 $source_values->{$source_column} = _get_single_item_column($source_column, $item->{'itemnumber'});
1736 $builder->($item, $source_values);
1741 =head2 _do_column_fixes_for_mod
1745 _do_column_fixes_for_mod($item);
1749 Given an item hashref containing one or more
1750 columns to modify, fix up certain values.
1751 Specifically, set to 0 any passed value
1752 of C<notforloan>, C<damaged>, C<itemlost>, or
1753 C<wthdrawn> that is either undefined or
1754 contains the empty string.
1758 sub _do_column_fixes_for_mod {
1761 if (exists $item->{'notforloan'} and
1762 (not defined $item->{'notforloan'} or $item->{'notforloan'} eq '')) {
1763 $item->{'notforloan'} = 0;
1765 if (exists $item->{'damaged'} and
1766 (not defined $item->{'damaged'} or $item->{'damaged'} eq '')) {
1767 $item->{'damaged'} = 0;
1769 if (exists $item->{'itemlost'} and
1770 (not defined $item->{'itemlost'} or $item->{'itemlost'} eq '')) {
1771 $item->{'itemlost'} = 0;
1773 if (exists $item->{'wthdrawn'} and
1774 (not defined $item->{'wthdrawn'} or $item->{'wthdrawn'} eq '')) {
1775 $item->{'wthdrawn'} = 0;
1779 =head2 _get_single_item_column
1783 _get_single_item_column($column, $itemnumber);
1787 Retrieves the value of a single column from an C<items>
1788 row specified by C<$itemnumber>.
1792 sub _get_single_item_column {
1794 my $itemnumber = shift;
1796 my $dbh = C4::Context->dbh;
1797 my $sth = $dbh->prepare("SELECT $column FROM items WHERE itemnumber = ?");
1798 $sth->execute($itemnumber);
1799 my ($value) = $sth->fetchrow();
1803 =head2 _calc_items_cn_sort
1807 _calc_items_cn_sort($item, $source_values);
1811 Helper routine to calculate C<items.cn_sort>.
1815 sub _calc_items_cn_sort {
1817 my $source_values = shift;
1819 $item->{'items.cn_sort'} = GetClassSort($source_values->{'items.cn_source'}, $source_values->{'itemcallnumber'}, "");
1822 =head2 _set_defaults_for_add
1826 _set_defaults_for_add($item_hash);
1830 Given an item hash representing an item to be added, set
1831 correct default values for columns whose default value
1832 is not handled by the DBMS. This includes the following
1839 C<items.dateaccessioned>
1861 sub _set_defaults_for_add {
1863 $item->{dateaccessioned} ||= C4::Dates->new->output('iso');
1864 $item->{$_} ||= 0 for (qw( notforloan damaged itemlost wthdrawn));
1867 =head2 _koha_new_item
1871 my ($itemnumber,$error) = _koha_new_item( $item, $barcode );
1875 Perform the actual insert into the C<items> table.
1879 sub _koha_new_item {
1880 my ( $item, $barcode ) = @_;
1881 my $dbh=C4::Context->dbh;
1884 "INSERT INTO items SET
1886 biblioitemnumber = ?,
1888 dateaccessioned = ?,
1892 replacementprice = ?,
1893 replacementpricedate = NOW(),
1894 datelastborrowed = ?,
1895 datelastseen = NOW(),
1918 more_subfields_xml = ?,
1921 my $sth = $dbh->prepare($query);
1923 $item->{'biblionumber'},
1924 $item->{'biblioitemnumber'},
1926 $item->{'dateaccessioned'},
1927 $item->{'booksellerid'},
1928 $item->{'homebranch'},
1930 $item->{'replacementprice'},
1931 $item->{datelastborrowed},
1933 $item->{'notforloan'},
1935 $item->{'itemlost'},
1936 $item->{'wthdrawn'},
1937 $item->{'itemcallnumber'},
1938 $item->{'restricted'},
1939 $item->{'itemnotes'},
1940 $item->{'holdingbranch'},
1942 $item->{'location'},
1945 $item->{'renewals'},
1946 $item->{'reserves'},
1947 $item->{'items.cn_source'},
1948 $item->{'items.cn_sort'},
1951 $item->{'materials'},
1953 $item->{'enumchron'},
1954 $item->{'more_subfields_xml'},
1955 $item->{'copynumber'},
1957 my $itemnumber = $dbh->{'mysql_insertid'};
1958 if ( defined $sth->errstr ) {
1959 $error.="ERROR in _koha_new_item $query".$sth->errstr;
1962 return ( $itemnumber, $error );
1965 =head2 _koha_modify_item
1969 my ($itemnumber,$error) =_koha_modify_item( $item );
1973 Perform the actual update of the C<items> row. Note that this
1974 routine accepts a hashref specifying the columns to update.
1978 sub _koha_modify_item {
1980 my $dbh=C4::Context->dbh;
1983 my $query = "UPDATE items SET ";
1985 for my $key ( keys %$item ) {
1987 push @bind, $item->{$key};
1990 $query .= " WHERE itemnumber=?";
1991 push @bind, $item->{'itemnumber'};
1992 my $sth = C4::Context->dbh->prepare($query);
1993 $sth->execute(@bind);
1994 if ( C4::Context->dbh->errstr ) {
1995 $error.="ERROR in _koha_modify_item $query".$dbh->errstr;
1999 return ($item->{'itemnumber'},$error);
2002 =head2 _koha_delete_item
2006 _koha_delete_item( $dbh, $itemnum );
2010 Internal function to delete an item record from the koha tables
2014 sub _koha_delete_item {
2015 my ( $dbh, $itemnum ) = @_;
2017 # save the deleted item to deleteditems table
2018 my $sth = $dbh->prepare("SELECT * FROM items WHERE itemnumber=?");
2019 $sth->execute($itemnum);
2020 my $data = $sth->fetchrow_hashref();
2022 my $query = "INSERT INTO deleteditems SET ";
2024 foreach my $key ( keys %$data ) {
2025 $query .= "$key = ?,";
2026 push( @bind, $data->{$key} );
2029 $sth = $dbh->prepare($query);
2030 $sth->execute(@bind);
2033 # delete from items table
2034 $sth = $dbh->prepare("DELETE FROM items WHERE itemnumber=?");
2035 $sth->execute($itemnum);
2040 =head2 _marc_from_item_hash
2044 my $item_marc = _marc_from_item_hash($item, $frameworkcode[, $unlinked_item_subfields]);
2048 Given an item hash representing a complete item record,
2049 create a C<MARC::Record> object containing an embedded
2050 tag representing that item.
2052 The third, optional parameter C<$unlinked_item_subfields> is
2053 an arrayref of subfields (not mapped to C<items> fields per the
2054 framework) to be added to the MARC representation
2059 sub _marc_from_item_hash {
2061 my $frameworkcode = shift;
2062 my $unlinked_item_subfields;
2064 $unlinked_item_subfields = shift;
2067 # Tack on 'items.' prefix to column names so lookup from MARC frameworks will work
2068 # Also, don't emit a subfield if the underlying field is blank.
2069 my $mungeditem = { map { (defined($item->{$_}) and $item->{$_} ne '') ?
2070 (/^items\./ ? ($_ => $item->{$_}) : ("items.$_" => $item->{$_}))
2071 : () } keys %{ $item } };
2073 my $item_marc = MARC::Record->new();
2074 foreach my $item_field (keys %{ $mungeditem }) {
2075 my ($tag, $subfield) = GetMarcFromKohaField($item_field, $frameworkcode);
2076 next unless defined $tag and defined $subfield; # skip if not mapped to MARC field
2077 if (my $field = $item_marc->field($tag)) {
2078 $field->add_subfields($subfield => $mungeditem->{$item_field});
2080 my $add_subfields = [];
2081 if (defined $unlinked_item_subfields and ref($unlinked_item_subfields) eq 'ARRAY' and $#$unlinked_item_subfields > -1) {
2082 $add_subfields = $unlinked_item_subfields;
2084 $item_marc->add_fields( $tag, " ", " ", $subfield => $mungeditem->{$item_field}, @$add_subfields);
2091 =head2 _add_item_field_to_biblio
2095 _add_item_field_to_biblio($item_marc, $biblionumber, $frameworkcode);
2099 Adds the fields from a MARC record containing the
2100 representation of a Koha item record to the MARC
2101 biblio record. The input C<$item_marc> record
2102 is expect to contain just one field, the embedded
2103 item information field.
2107 sub _add_item_field_to_biblio {
2108 my ($item_marc, $biblionumber, $frameworkcode) = @_;
2110 my $biblio_marc = GetMarcBiblio($biblionumber);
2111 foreach my $field ($item_marc->fields()) {
2112 $biblio_marc->append_fields($field);
2115 ModBiblioMarc($biblio_marc, $biblionumber, $frameworkcode);
2118 =head2 _replace_item_field_in_biblio
2122 &_replace_item_field_in_biblio($item_marc, $biblionumber, $itemnumber, $frameworkcode)
2126 Given a MARC::Record C<$item_marc> containing one tag with the MARC
2127 representation of the item, examine the biblio MARC
2128 for the corresponding tag for that item and
2129 replace it with the tag from C<$item_marc>.
2133 sub _replace_item_field_in_biblio {
2134 my ($ItemRecord, $biblionumber, $itemnumber, $frameworkcode) = @_;
2135 my $dbh = C4::Context->dbh;
2137 # get complete MARC record & replace the item field by the new one
2138 my $completeRecord = GetMarcBiblio($biblionumber);
2139 my ($itemtag,$itemsubfield) = GetMarcFromKohaField("items.itemnumber",$frameworkcode);
2140 my $itemField = $ItemRecord->field($itemtag);
2141 my @items = $completeRecord->field($itemtag);
2144 if ($_->subfield($itemsubfield) eq $itemnumber) {
2145 $_->replace_with($itemField);
2151 # If we haven't found the matching field,
2152 # just add it. However, this means that
2153 # there is likely a bug.
2154 $completeRecord->append_fields($itemField);
2158 ModBiblioMarc($completeRecord, $biblionumber, $frameworkcode);
2161 =head2 _repack_item_errors
2163 Add an error message hash generated by C<CheckItemPreSave>
2164 to a list of errors.
2168 sub _repack_item_errors {
2169 my $item_sequence_num = shift;
2170 my $item_ref = shift;
2171 my $error_ref = shift;
2173 my @repacked_errors = ();
2175 foreach my $error_code (sort keys %{ $error_ref }) {
2176 my $repacked_error = {};
2177 $repacked_error->{'item_sequence'} = $item_sequence_num;
2178 $repacked_error->{'item_barcode'} = exists($item_ref->{'barcode'}) ? $item_ref->{'barcode'} : '';
2179 $repacked_error->{'error_code'} = $error_code;
2180 $repacked_error->{'error_information'} = $error_ref->{$error_code};
2181 push @repacked_errors, $repacked_error;
2184 return @repacked_errors;
2187 =head2 _get_unlinked_item_subfields
2191 my $unlinked_item_subfields = _get_unlinked_item_subfields($original_item_marc, $frameworkcode);
2197 sub _get_unlinked_item_subfields {
2198 my $original_item_marc = shift;
2199 my $frameworkcode = shift;
2201 my $marcstructure = GetMarcStructure(1, $frameworkcode);
2203 # assume that this record has only one field, and that that
2204 # field contains only the item information
2206 my @fields = $original_item_marc->fields();
2207 if ($#fields > -1) {
2208 my $field = $fields[0];
2209 my $tag = $field->tag();
2210 foreach my $subfield ($field->subfields()) {
2211 if (defined $subfield->[1] and
2212 $subfield->[1] ne '' and
2213 !$marcstructure->{$tag}->{$subfield->[0]}->{'kohafield'}) {
2214 push @$subfields, $subfield->[0] => $subfield->[1];
2221 =head2 _get_unlinked_subfields_xml
2225 my $unlinked_subfields_xml = _get_unlinked_subfields_xml($unlinked_item_subfields);
2231 sub _get_unlinked_subfields_xml {
2232 my $unlinked_item_subfields = shift;
2235 if (defined $unlinked_item_subfields and ref($unlinked_item_subfields) eq 'ARRAY' and $#$unlinked_item_subfields > -1) {
2236 my $marc = MARC::Record->new();
2237 # use of tag 999 is arbitrary, and doesn't need to match the item tag
2238 # used in the framework
2239 $marc->append_fields(MARC::Field->new('999', ' ', ' ', @$unlinked_item_subfields));
2240 $marc->encoding("UTF-8");
2241 $xml = $marc->as_xml("USMARC");
2247 =head2 _parse_unlinked_item_subfields_from_xml
2251 my $unlinked_item_subfields = _parse_unlinked_item_subfields_from_xml($whole_item->{'more_subfields_xml'}):
2257 sub _parse_unlinked_item_subfields_from_xml {
2260 return unless defined $xml and $xml ne "";
2261 my $marc = MARC::Record->new_from_xml(StripNonXmlChars($xml),'UTF-8');
2262 my $unlinked_subfields = [];
2263 my @fields = $marc->fields();
2264 if ($#fields > -1) {
2265 foreach my $subfield ($fields[0]->subfields()) {
2266 push @$unlinked_subfields, $subfield->[0] => $subfield->[1];
2269 return $unlinked_subfields;
2272 =head2 GetLastAcquisitions
2276 my $lastacq = GetLastAcquisitions({'branches' => ('branch1','branch2'), 'itemtypes' => ('BK','BD')}, 10);
2282 sub GetLastAcquisitions {
2283 my ($data,$max) = @_;
2285 my $itemtype = C4::Context->preference('item-level_itypes') ? 'itype' : 'itemtype';
2287 my $number_of_branches = @{$data->{branches}};
2288 my $number_of_itemtypes = @{$data->{itemtypes}};
2291 my @where = ('WHERE 1 ');
2292 $number_of_branches and push @where
2293 , 'AND holdingbranch IN ('
2294 , join(',', ('?') x $number_of_branches )
2298 $number_of_itemtypes and push @where
2299 , "AND $itemtype IN ("
2300 , join(',', ('?') x $number_of_itemtypes )
2304 my $query = "SELECT biblio.biblionumber as biblionumber, title, dateaccessioned
2305 FROM items RIGHT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
2306 RIGHT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber)
2308 GROUP BY biblio.biblionumber
2309 ORDER BY dateaccessioned DESC LIMIT $max";
2311 my $dbh = C4::Context->dbh;
2312 my $sth = $dbh->prepare($query);
2314 $sth->execute((@{$data->{branches}}, @{$data->{itemtypes}}));
2317 while( my $row = $sth->fetchrow_hashref){
2318 push @results, {date => $row->{dateaccessioned}
2319 , biblionumber => $row->{biblionumber}
2320 , title => $row->{title}};