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
27 use C4::Dates qw/format_date format_date_in_iso/;
34 use vars qw($VERSION @ISA @EXPORT);
38 @ISA = qw( Exporter );
60 GetItemsByBiblioitemnumber
63 GetItemnumberFromBarcode
68 C4::Items - item management functions
72 This module contains an API for manipulating item
73 records in Koha, and is used by cataloguing, circulation,
74 acquisitions, and serials management.
76 A Koha item record is stored in two places: the
77 items table and embedded in a MARC tag in the XML
78 version of the associated bib record in C<biblioitems.marcxml>.
79 This is done to allow the item information to be readily
80 indexed (e.g., by Zebra), but means that each item
81 modification transaction must keep the items table
82 and the MARC XML in sync at all times.
84 Consequently, all code that creates, modifies, or deletes
85 item records B<must> use an appropriate function from
86 C<C4::Items>. If no existing function is suitable, it is
87 better to add one to C<C4::Items> than to use add
88 one-off SQL statements to add or modify items.
90 The items table will be considered authoritative. In other
91 words, if there is ever a discrepancy between the items
92 table and the MARC XML, the items table should be considered
95 =head1 HISTORICAL NOTE
97 Most of the functions in C<C4::Items> were originally in
98 the C<C4::Biblio> module.
100 =head1 CORE EXPORTED FUNCTIONS
102 The following functions are meant for use by users
111 $item = GetItem($itemnumber,$barcode);
115 Return item information, for a given itemnumber or barcode.
116 The return value is a hashref mapping item column
122 my ($itemnumber,$barcode) = @_;
123 my $dbh = C4::Context->dbh;
125 my $sth = $dbh->prepare("
127 WHERE itemnumber = ?");
128 $sth->execute($itemnumber);
129 my $data = $sth->fetchrow_hashref;
132 my $sth = $dbh->prepare("
136 $sth->execute($barcode);
137 my $data = $sth->fetchrow_hashref;
142 =head2 AddItemFromMarc
146 my ($biblionumber, $biblioitemnumber, $itemnumber)
147 = AddItemFromMarc($source_item_marc, $biblionumber);
151 Given a MARC::Record object containing an embedded item
152 record and a biblionumber, create a new item record.
156 sub AddItemFromMarc {
157 my ( $source_item_marc, $biblionumber ) = @_;
158 my $dbh = C4::Context->dbh;
160 # parse item hash from MARC
161 my $frameworkcode = GetFrameworkCode( $biblionumber );
162 my $item = &TransformMarcToKoha( $dbh, $source_item_marc, $frameworkcode );
164 return AddItem($item, $biblionumber, $dbh, $frameworkcode);
171 my ($biblionumber, $biblioitemnumber, $itemnumber)
172 = AddItem($item, $biblionumber[, $dbh, $frameworkcode]);
176 Given a hash containing item column names as keys,
177 create a new Koha item record.
179 The two optional parameters (C<$dbh> and C<$frameworkcode>)
180 do not need to be supplied for general use; they exist
181 simply to allow them to be picked up from AddItemFromMarc.
187 my $biblionumber = shift;
189 my $dbh = @_ ? shift : C4::Context->dbh;
190 my $frameworkcode = @_ ? shift : GetFrameworkCode( $biblionumber );
192 # needs old biblionumber and biblioitemnumber
193 $item->{'biblionumber'} = $biblionumber;
194 my $sth = $dbh->prepare("SELECT biblioitemnumber FROM biblioitems WHERE biblionumber=?");
195 $sth->execute( $item->{'biblionumber'} );
196 ($item->{'biblioitemnumber'}) = $sth->fetchrow;
198 _set_defaults_for_add($item);
199 _set_derived_columns_for_add($item);
200 # FIXME - checks here
201 my ( $itemnumber, $error ) = _koha_new_item( $dbh, $item, $item->{barcode} );
202 $item->{'itemnumber'} = $itemnumber;
204 # create MARC tag representing item and add to bib
205 my $new_item_marc = _marc_from_item_hash($item, $frameworkcode);
206 _add_item_field_to_biblio($new_item_marc, $item->{'biblionumber'}, $frameworkcode );
208 logaction(C4::Context->userenv->{'number'},"CATALOGUING","ADD",$itemnumber,"item")
209 if C4::Context->preference("CataloguingLog");
211 return ($item->{biblionumber}, $item->{biblioitemnumber}, $itemnumber);
214 =head2 AddItemBatchFromMarc
218 ($itemnumber_ref, $error_ref) = AddItemBatchFromMarc($record, $biblionumber, $biblioitemnumber, $frameworkcode);
222 Efficiently create item records from a MARC biblio record with
223 embedded item fields. This routine is suitable for batch jobs.
225 This API assumes that the bib record has already been
226 saved to the C<biblio> and C<biblioitems> tables. It does
227 not expect that C<biblioitems.marc> and C<biblioitems.marcxml>
228 are populated, but it will do so via a call to ModBibiloMarc.
230 The goal of this API is to have a similar effect to using AddBiblio
231 and AddItems in succession, but without inefficient repeated
232 parsing of the MARC XML bib record.
234 This function returns an arrayref of new itemsnumbers and an arrayref of item
235 errors encountered during the processing. Each entry in the errors
236 list is a hashref containing the following keys:
242 Sequence number of original item tag in the MARC record.
246 Item barcode, provide to assist in the construction of
247 useful error messages.
249 =item error_condition
251 Code representing the error condition. Can be 'duplicate_barcode',
252 'invalid_homebranch', or 'invalid_holdingbranch'.
254 =item error_information
256 Additional information appropriate to the error condition.
262 sub AddItemBatchFromMarc {
263 my ($record, $biblionumber, $biblioitemnumber, $frameworkcode) = @_;
265 my @itemnumbers = ();
267 my $dbh = C4::Context->dbh;
269 # loop through the item tags and start creating items
270 my @bad_item_fields = ();
271 my ($itemtag, $itemsubfield) = &GetMarcFromKohaField("items.itemnumber",'');
272 my $item_sequence_num = 0;
273 ITEMFIELD: foreach my $item_field ($record->field($itemtag)) {
274 $item_sequence_num++;
275 # we take the item field and stick it into a new
276 # MARC record -- this is required so far because (FIXME)
277 # TransformMarcToKoha requires a MARC::Record, not a MARC::Field
278 # and there is no TransformMarcFieldToKoha
279 my $temp_item_marc = MARC::Record->new();
280 $temp_item_marc->append_fields($item_field);
282 # add biblionumber and biblioitemnumber
283 my $item = TransformMarcToKoha( $dbh, $temp_item_marc, $frameworkcode, 'items' );
284 $item->{'biblionumber'} = $biblionumber;
285 $item->{'biblioitemnumber'} = $biblioitemnumber;
287 # check for duplicate barcode
288 my %item_errors = CheckItemPreSave($item);
290 push @errors, _repack_item_errors($item_sequence_num, $item, \%item_errors);
291 push @bad_item_fields, $item_field;
295 _set_defaults_for_add($item);
296 _set_derived_columns_for_add($item);
297 my ( $itemnumber, $error ) = _koha_new_item( $dbh, $item, $item->{barcode} );
298 warn $error if $error;
299 push @itemnumbers, $itemnumber; # FIXME not checking error
300 $item->{'itemnumber'} = $itemnumber;
302 &logaction(C4::Context->userenv->{'number'},"CATALOGUING","ADD",$itemnumber,"item")
303 if C4::Context->preference("CataloguingLog");
305 my $new_item_marc = _marc_from_item_hash($item, $frameworkcode);
306 $item_field->replace_with($new_item_marc->field($itemtag));
309 # remove any MARC item fields for rejected items
310 foreach my $item_field (@bad_item_fields) {
311 $record->delete_field($item_field);
314 # update the MARC biblio
315 $biblionumber = ModBiblioMarc( $record, $biblionumber, $frameworkcode );
317 return (\@itemnumbers, \@errors);
320 =head2 ModItemFromMarc
324 ModItemFromMarc($item_marc, $biblionumber, $itemnumber);
328 This function updates an item record based on a supplied
329 C<MARC::Record> object containing an embedded item field.
330 This API is meant for the use of C<additem.pl>; for
331 other purposes, C<ModItem> should be used.
335 sub ModItemFromMarc {
336 my $item_marc = shift;
337 my $biblionumber = shift;
338 my $itemnumber = shift;
340 my $dbh = C4::Context->dbh;
341 my $frameworkcode = GetFrameworkCode( $biblionumber );
342 my $item = &TransformMarcToKoha( $dbh, $item_marc, $frameworkcode );
344 return ModItem($item, $biblionumber, $itemnumber, $dbh, $frameworkcode);
351 ModItem({ column => $newvalue }, $biblionumber, $itemnumber);
355 Change one or more columns in an item record and update
356 the MARC representation of the item.
358 The first argument is a hashref mapping from item column
359 names to the new values. The second and third arguments
360 are the biblionumber and itemnumber, respectively.
362 If one of the changed columns is used to calculate
363 the derived value of a column such as C<items.cn_sort>,
364 this routine will perform the necessary calculation
371 my $biblionumber = shift;
372 my $itemnumber = shift;
374 # if $biblionumber is undefined, get it from the current item
375 unless (defined $biblionumber) {
376 $biblionumber = _get_single_item_column('biblionumber', $itemnumber);
379 my $dbh = @_ ? shift : C4::Context->dbh;
380 my $frameworkcode = @_ ? shift : GetFrameworkCode( $biblionumber );
382 $item->{'itemnumber'} = $itemnumber;
383 _set_derived_columns_for_mod($item);
384 _do_column_fixes_for_mod($item);
387 # attempt to change itemnumber
388 # attempt to change biblionumber (if we want
389 # an API to relink an item to a different bib,
390 # it should be a separate function)
393 _koha_modify_item($dbh, $item);
395 # update biblio MARC XML
396 my $whole_item = GetItem($itemnumber);
397 my $new_item_marc = _marc_from_item_hash($whole_item, $frameworkcode);
398 _replace_item_field_in_biblio($new_item_marc, $biblionumber, $itemnumber, $frameworkcode);
400 logaction(C4::Context->userenv->{'number'},"CATALOGUING","MODIFY",$itemnumber,$new_item_marc->as_formatted)
401 if C4::Context->preference("CataloguingLog");
404 =head2 ModItemTransfer
408 ModItemTransfer($itenumber, $frombranch, $tobranch);
412 Marks an item as being transferred from one branch
417 sub ModItemTransfer {
418 my ( $itemnumber, $frombranch, $tobranch ) = @_;
420 my $dbh = C4::Context->dbh;
422 #new entry in branchtransfers....
423 my $sth = $dbh->prepare(
424 "INSERT INTO branchtransfers (itemnumber, frombranch, datesent, tobranch)
425 VALUES (?, ?, NOW(), ?)");
426 $sth->execute($itemnumber, $frombranch, $tobranch);
428 ModItem({ holdingbranch => $tobranch }, undef, $itemnumber);
429 ModDateLastSeen($itemnumber);
433 =head2 ModDateLastSeen
437 ModDateLastSeen($itemnum);
441 Mark item as seen. Is called when an item is issued, returned or manually marked during inventory/stocktaking.
442 C<$itemnum> is the item number
446 sub ModDateLastSeen {
447 my ($itemnumber) = @_;
449 my $today = C4::Dates->new();
450 ModItem({ itemlost => 0, datelastseen => $today->output("iso") }, undef, $itemnumber);
457 DelItem($biblionumber, $itemnumber);
461 Exported function (core API) for deleting an item record in Koha.
466 my ( $dbh, $biblionumber, $itemnumber ) = @_;
468 # FIXME check the item has no current issues
470 _koha_delete_item( $dbh, $itemnumber );
472 # get the MARC record
473 my $record = GetMarcBiblio($biblionumber);
474 my $frameworkcode = GetFrameworkCode($biblionumber);
477 my $copy2deleted = $dbh->prepare("UPDATE deleteditems SET marc=? WHERE itemnumber=?");
478 $copy2deleted->execute( $record->as_usmarc(), $itemnumber );
480 #search item field code
481 my ( $itemtag, $itemsubfield ) = GetMarcFromKohaField("items.itemnumber",$frameworkcode);
482 my @fields = $record->field($itemtag);
484 # delete the item specified
485 foreach my $field (@fields) {
486 if ( $field->subfield($itemsubfield) eq $itemnumber ) {
487 $record->delete_field($field);
490 &ModBiblioMarc( $record, $biblionumber, $frameworkcode );
491 &logaction(C4::Context->userenv->{'number'},"CATALOGUING","DELETE",$itemnumber,"item")
492 if C4::Context->preference("CataloguingLog");
495 =head2 CheckItemPreSave
499 my $item_ref = TransformMarcToKoha($marc, 'items');
501 my %errors = CheckItemPreSave($item_ref);
502 if (exists $errors{'duplicate_barcode'}) {
503 print "item has duplicate barcode: ", $errors{'duplicate_barcode'}, "\n";
504 } elsif (exists $errors{'invalid_homebranch'}) {
505 print "item has invalid home branch: ", $errors{'invalid_homebranch'}, "\n";
506 } elsif (exists $errors{'invalid_holdingbranch'}) {
507 print "item has invalid holding branch: ", $errors{'invalid_holdingbranch'}, "\n";
514 Given a hashref containing item fields, determine if it can be
515 inserted or updated in the database. Specifically, checks for
516 database integrity issues, and returns a hash containing any
517 of the following keys, if applicable.
521 =item duplicate_barcode
523 Barcode, if it duplicates one already found in the database.
525 =item invalid_homebranch
527 Home branch, if not defined in branches table.
529 =item invalid_holdingbranch
531 Holding branch, if not defined in branches table.
535 This function does NOT implement any policy-related checks,
536 e.g., whether current operator is allowed to save an
537 item that has a given branch code.
541 sub CheckItemPreSave {
542 my $item_ref = shift;
546 # check for duplicate barcode
547 if (exists $item_ref->{'barcode'} and defined $item_ref->{'barcode'}) {
548 my $existing_itemnumber = GetItemnumberFromBarcode($item_ref->{'barcode'});
549 if ($existing_itemnumber) {
550 if (!exists $item_ref->{'itemnumber'} # new item
551 or $item_ref->{'itemnumber'} != $existing_itemnumber) { # existing item
552 $errors{'duplicate_barcode'} = $item_ref->{'barcode'};
557 # check for valid home branch
558 if (exists $item_ref->{'homebranch'} and defined $item_ref->{'homebranch'}) {
559 my $branch_name = GetBranchName($item_ref->{'homebranch'});
560 unless (defined $branch_name) {
561 # relies on fact that branches.branchname is a non-NULL column,
562 # so GetBranchName returns undef only if branch does not exist
563 $errors{'invalid_homebranch'} = $item_ref->{'homebranch'};
567 # check for valid holding branch
568 if (exists $item_ref->{'holdingbranch'} and defined $item_ref->{'holdingbranch'}) {
569 my $branch_name = GetBranchName($item_ref->{'holdingbranch'});
570 unless (defined $branch_name) {
571 # relies on fact that branches.branchname is a non-NULL column,
572 # so GetBranchName returns undef only if branch does not exist
573 $errors{'invalid_holdingbranch'} = $item_ref->{'holdingbranch'};
581 =head1 EXPORTED SPECIAL ACCESSOR FUNCTIONS
583 The following functions provide various ways of
584 getting an item record, a set of item records, or
585 lists of authorized values for certain item fields.
587 Some of the functions in this group are candidates
588 for refactoring -- for example, some of the code
589 in C<GetItemsByBiblioitemnumber> and C<GetItemsInfo>
590 has copy-and-paste work.
598 $itemstatushash = GetItemStatus($fwkcode);
602 Returns a list of valid values for the
603 C<items.notforloan> field.
605 NOTE: does B<not> return an individual item's
608 Can be MARC dependant.
610 But basically could be can be loan or not
611 Create a status selector with the following code
613 =head3 in PERL SCRIPT
617 my $itemstatushash = getitemstatus;
619 foreach my $thisstatus (keys %$itemstatushash) {
620 my %row =(value => $thisstatus,
621 statusname => $itemstatushash->{$thisstatus}->{'statusname'},
623 push @itemstatusloop, \%row;
625 $template->param(statusloop=>\@itemstatusloop);
633 <select name="statusloop">
634 <option value="">Default</option>
635 <!-- TMPL_LOOP name="statusloop" -->
636 <option value="<!-- TMPL_VAR name="value" -->" <!-- TMPL_IF name="selected" -->selected<!-- /TMPL_IF -->><!-- TMPL_VAR name="statusname" --></option>
646 # returns a reference to a hash of references to status...
649 my $dbh = C4::Context->dbh;
651 $fwk = '' unless ($fwk);
652 my ( $tag, $subfield ) =
653 GetMarcFromKohaField( "items.notforloan", $fwk );
654 if ( $tag and $subfield ) {
657 "SELECT authorised_value
658 FROM marc_subfield_structure
664 $sth->execute( $tag, $subfield, $fwk );
665 if ( my ($authorisedvaluecat) = $sth->fetchrow ) {
668 "SELECT authorised_value,lib
669 FROM authorised_values
674 $authvalsth->execute($authorisedvaluecat);
675 while ( my ( $authorisedvalue, $lib ) = $authvalsth->fetchrow ) {
676 $itemstatus{$authorisedvalue} = $lib;
692 $itemstatus{"1"} = "Not For Loan";
696 =head2 GetItemLocation
700 $itemlochash = GetItemLocation($fwk);
704 Returns a list of valid values for the
705 C<items.location> field.
707 NOTE: does B<not> return an individual item's
710 where fwk stands for an optional framework code.
711 Create a location selector with the following code
713 =head3 in PERL SCRIPT
717 my $itemlochash = getitemlocation;
719 foreach my $thisloc (keys %$itemlochash) {
720 my $selected = 1 if $thisbranch eq $branch;
721 my %row =(locval => $thisloc,
722 selected => $selected,
723 locname => $itemlochash->{$thisloc},
725 push @itemlocloop, \%row;
727 $template->param(itemlocationloop => \@itemlocloop);
735 <select name="location">
736 <option value="">Default</option>
737 <!-- TMPL_LOOP name="itemlocationloop" -->
738 <option value="<!-- TMPL_VAR name="locval" -->" <!-- TMPL_IF name="selected" -->selected<!-- /TMPL_IF -->><!-- TMPL_VAR name="locname" --></option>
746 sub GetItemLocation {
748 # returns a reference to a hash of references to location...
751 my $dbh = C4::Context->dbh;
753 $fwk = '' unless ($fwk);
754 my ( $tag, $subfield ) =
755 GetMarcFromKohaField( "items.location", $fwk );
756 if ( $tag and $subfield ) {
759 "SELECT authorised_value
760 FROM marc_subfield_structure
765 $sth->execute( $tag, $subfield, $fwk );
766 if ( my ($authorisedvaluecat) = $sth->fetchrow ) {
769 "SELECT authorised_value,lib
770 FROM authorised_values
774 $authvalsth->execute($authorisedvaluecat);
775 while ( my ( $authorisedvalue, $lib ) = $authvalsth->fetchrow ) {
776 $itemlocation{$authorisedvalue} = $lib;
779 return \%itemlocation;
792 $itemlocation{"1"} = "Not For Loan";
793 return \%itemlocation;
800 $items = GetLostItems($where,$orderby);
804 This function get the items lost into C<$items>.
809 C<$where> is a hashref. it containts a field of the items table as key
810 and the value to match as value.
811 C<$orderby> is a field of the items table.
814 C<$items> is a reference to an array full of hasref which keys are items' table column.
816 =item usage in the perl script:
819 $where{barcode} = 0001548;
820 my $items = GetLostItems( \%where, "homebranch" );
821 $template->param(itemsloop => $items);
828 # Getting input args.
831 my $dbh = C4::Context->dbh;
836 WHERE itemlost IS NOT NULL
839 foreach my $key (keys %$where) {
840 $query .= " AND " . $key . " LIKE '%" . $where->{$key} . "%'";
842 $query .= " ORDER BY ".$orderby if defined $orderby;
844 my $sth = $dbh->prepare($query);
847 while ( my $row = $sth->fetchrow_hashref ){
853 =head2 GetItemsForInventory
857 $itemlist = GetItemsForInventory($minlocation,$maxlocation,$datelastseen,$offset,$size)
861 Retrieve a list of title/authors/barcode/callnumber, for biblio inventory.
863 The sub returns a list of hashes, containing itemnumber, author, title, barcode & item callnumber.
864 It is ordered by callnumber,title.
866 The minlocation & maxlocation parameters are used to specify a range of item callnumbers
867 the datelastseen can be used to specify that you want to see items not seen since a past date only.
868 offset & size can be used to retrieve only a part of the whole listing (defaut behaviour)
872 sub GetItemsForInventory {
873 my ( $minlocation, $maxlocation,$location, $datelastseen, $branch, $offset, $size ) = @_;
874 my $dbh = C4::Context->dbh;
877 $datelastseen=format_date_in_iso($datelastseen);
879 "SELECT itemnumber,barcode,itemcallnumber,title,author,biblio.biblionumber,datelastseen
881 LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber
882 WHERE itemcallnumber>= ?
883 AND itemcallnumber <=?
884 AND (datelastseen< ? OR datelastseen IS NULL)";
885 $query.= " AND items.location=".$dbh->quote($location) if $location;
886 $query.= " AND items.homebranch=".$dbh->quote($branch) if $branch;
887 $query .= " ORDER BY itemcallnumber,title";
888 $sth = $dbh->prepare($query);
889 $sth->execute( $minlocation, $maxlocation, $datelastseen );
893 SELECT itemnumber,barcode,itemcallnumber,biblio.biblionumber,title,author,datelastseen
895 LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber
896 WHERE itemcallnumber>= ?
897 AND itemcallnumber <=?";
898 $query.= " AND items.location=".$dbh->quote($location) if $location;
899 $query.= " AND items.homebranch=".$dbh->quote($branch) if $branch;
900 $query .= " ORDER BY itemcallnumber,title";
901 $sth = $dbh->prepare($query);
902 $sth->execute( $minlocation, $maxlocation );
905 while ( my $row = $sth->fetchrow_hashref ) {
906 $offset-- if ($offset);
907 $row->{datelastseen}=format_date($row->{datelastseen});
908 if ( ( !$offset ) && $size ) {
919 $count = &GetItemsCount( $biblionumber);
923 This function return count of item with $biblionumber
928 my ( $biblionumber ) = @_;
929 my $dbh = C4::Context->dbh;
930 my $query = "SELECT count(*)
932 WHERE biblionumber=?";
933 my $sth = $dbh->prepare($query);
934 $sth->execute($biblionumber);
935 my $count = $sth->fetchrow;
940 =head2 GetItemInfosOf
944 GetItemInfosOf(@itemnumbers);
951 my @itemnumbers = @_;
956 WHERE itemnumber IN (' . join( ',', @itemnumbers ) . ')
958 return get_infos_of( $query, 'itemnumber' );
961 =head2 GetItemsByBiblioitemnumber
965 GetItemsByBiblioitemnumber($biblioitemnumber);
969 Returns an arrayref of hashrefs suitable for use in a TMPL_LOOP
970 Called by C<C4::XISBN>
974 sub GetItemsByBiblioitemnumber {
975 my ( $bibitem ) = @_;
976 my $dbh = C4::Context->dbh;
977 my $sth = $dbh->prepare("SELECT * FROM items WHERE items.biblioitemnumber = ?") || die $dbh->errstr;
978 # Get all items attached to a biblioitem
981 $sth->execute($bibitem) || die $sth->errstr;
982 while ( my $data = $sth->fetchrow_hashref ) {
983 # Foreach item, get circulation information
984 my $sth2 = $dbh->prepare( "SELECT * FROM issues,borrowers
986 AND returndate is NULL
987 AND issues.borrowernumber = borrowers.borrowernumber"
989 $sth2->execute( $data->{'itemnumber'} );
990 if ( my $data2 = $sth2->fetchrow_hashref ) {
991 # if item is out, set the due date and who it is out too
992 $data->{'date_due'} = $data2->{'date_due'};
993 $data->{'cardnumber'} = $data2->{'cardnumber'};
994 $data->{'borrowernumber'} = $data2->{'borrowernumber'};
997 # set date_due to blank, so in the template we check itemlost, and wthdrawn
998 $data->{'date_due'} = '';
1001 # Find the last 3 people who borrowed this item.
1002 my $query2 = "SELECT * FROM issues, borrowers WHERE itemnumber = ?
1003 AND issues.borrowernumber = borrowers.borrowernumber
1004 AND returndate is not NULL
1005 ORDER BY returndate desc,timestamp desc LIMIT 3";
1006 $sth2 = $dbh->prepare($query2) || die $dbh->errstr;
1007 $sth2->execute( $data->{'itemnumber'} ) || die $sth2->errstr;
1009 while ( my $data2 = $sth2->fetchrow_hashref ) {
1010 $data->{"timestamp$i2"} = $data2->{'timestamp'};
1011 $data->{"card$i2"} = $data2->{'cardnumber'};
1012 $data->{"borrower$i2"} = $data2->{'borrowernumber'};
1016 push(@results,$data);
1026 @results = GetItemsInfo($biblionumber, $type);
1030 Returns information about books with the given biblionumber.
1032 C<$type> may be either C<intra> or anything else. If it is not set to
1033 C<intra>, then the search will exclude lost, very overdue, and
1036 C<GetItemsInfo> returns a list of references-to-hash. Each element
1037 contains a number of keys. Most of them are table items from the
1038 C<biblio>, C<biblioitems>, C<items>, and C<itemtypes> tables in the
1039 Koha database. Other keys include:
1043 =item C<$data-E<gt>{branchname}>
1045 The name (not the code) of the branch to which the book belongs.
1047 =item C<$data-E<gt>{datelastseen}>
1049 This is simply C<items.datelastseen>, except that while the date is
1050 stored in YYYY-MM-DD format in the database, here it is converted to
1051 DD/MM/YYYY format. A NULL date is returned as C<//>.
1053 =item C<$data-E<gt>{datedue}>
1055 =item C<$data-E<gt>{class}>
1057 This is the concatenation of C<biblioitems.classification>, the book's
1058 Dewey code, and C<biblioitems.subclass>.
1060 =item C<$data-E<gt>{ocount}>
1062 I think this is the number of copies of the book available.
1064 =item C<$data-E<gt>{order}>
1066 If this is set, it is set to C<One Order>.
1073 my ( $biblionumber, $type ) = @_;
1074 my $dbh = C4::Context->dbh;
1075 my $query = "SELECT *,items.notforloan as itemnotforloan
1077 LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber
1078 LEFT JOIN biblioitems ON biblioitems.biblioitemnumber = items.biblioitemnumber";
1079 $query .= (C4::Context->preference('item-level_itypes')) ?
1080 " LEFT JOIN itemtypes on items.itype = itemtypes.itemtype "
1081 : " LEFT JOIN itemtypes on biblioitems.itemtype = itemtypes.itemtype ";
1082 $query .= "WHERE items.biblionumber = ? ORDER BY items.dateaccessioned desc" ;
1083 my $sth = $dbh->prepare($query);
1084 $sth->execute($biblionumber);
1087 my ( $date_due, $count_reserves );
1089 my $isth = $dbh->prepare(
1090 "SELECT issues.*,borrowers.cardnumber,borrowers.surname,borrowers.firstname,borrowers.branchcode as bcode
1091 FROM issues LEFT JOIN borrowers ON issues.borrowernumber=borrowers.borrowernumber
1092 WHERE itemnumber = ?
1093 AND returndate IS NULL"
1095 while ( my $data = $sth->fetchrow_hashref ) {
1097 $isth->execute( $data->{'itemnumber'} );
1098 if ( my $idata = $isth->fetchrow_hashref ) {
1099 $data->{borrowernumber} = $idata->{borrowernumber};
1100 $data->{cardnumber} = $idata->{cardnumber};
1101 $data->{surname} = $idata->{surname};
1102 $data->{firstname} = $idata->{firstname};
1103 $datedue = $idata->{'date_due'};
1104 if (C4::Context->preference("IndependantBranches")){
1105 my $userenv = C4::Context->userenv;
1106 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
1107 $data->{'NOTSAMEBRANCH'} = 1 if ($idata->{'bcode'} ne $userenv->{branch});
1111 if ( $datedue eq '' ) {
1112 my ( $restype, $reserves ) =
1113 C4::Reserves::CheckReserves( $data->{'itemnumber'} );
1115 $count_reserves = $restype;
1120 #get branch information.....
1121 my $bsth = $dbh->prepare(
1122 "SELECT * FROM branches WHERE branchcode = ?
1125 $bsth->execute( $data->{'holdingbranch'} );
1126 if ( my $bdata = $bsth->fetchrow_hashref ) {
1127 $data->{'branchname'} = $bdata->{'branchname'};
1129 $data->{'datedue'} = $datedue;
1130 $data->{'count_reserves'} = $count_reserves;
1132 # get notforloan complete status if applicable
1133 my $sthnflstatus = $dbh->prepare(
1134 'SELECT authorised_value
1135 FROM marc_subfield_structure
1136 WHERE kohafield="items.notforloan"
1140 $sthnflstatus->execute;
1141 my ($authorised_valuecode) = $sthnflstatus->fetchrow;
1142 if ($authorised_valuecode) {
1143 $sthnflstatus = $dbh->prepare(
1144 "SELECT lib FROM authorised_values
1146 AND authorised_value=?"
1148 $sthnflstatus->execute( $authorised_valuecode,
1149 $data->{itemnotforloan} );
1150 my ($lib) = $sthnflstatus->fetchrow;
1151 $data->{notforloan} = $lib;
1154 # my stack procedures
1155 my $stackstatus = $dbh->prepare(
1156 'SELECT authorised_value
1157 FROM marc_subfield_structure
1158 WHERE kohafield="items.stack"
1161 $stackstatus->execute;
1163 ($authorised_valuecode) = $stackstatus->fetchrow;
1164 if ($authorised_valuecode) {
1165 $stackstatus = $dbh->prepare(
1167 FROM authorised_values
1169 AND authorised_value=?
1172 $stackstatus->execute( $authorised_valuecode, $data->{stack} );
1173 my ($lib) = $stackstatus->fetchrow;
1174 $data->{stack} = $lib;
1176 # Find the last 3 people who borrowed this item.
1177 my $sth2 = $dbh->prepare("SELECT * FROM issues,borrowers
1178 WHERE itemnumber = ?
1179 AND issues.borrowernumber = borrowers.borrowernumber
1180 AND returndate IS NOT NULL LIMIT 3");
1181 $sth2->execute($data->{'itemnumber'});
1183 while (my $data2 = $sth2->fetchrow_hashref()) {
1184 $data->{"timestamp$ii"} = $data2->{'timestamp'} if $data2->{'timestamp'};
1185 $data->{"card$ii"} = $data2->{'cardnumber'} if $data2->{'cardnumber'};
1186 $data->{"borrower$ii"} = $data2->{'borrowernumber'} if $data2->{'borrowernumber'};
1190 $results[$i] = $data;
1198 =head2 get_itemnumbers_of
1202 my @itemnumbers_of = get_itemnumbers_of(@biblionumbers);
1206 Given a list of biblionumbers, return the list of corresponding itemnumbers
1207 for each biblionumber.
1209 Return a reference on a hash where keys are biblionumbers and values are
1210 references on array of itemnumbers.
1214 sub get_itemnumbers_of {
1215 my @biblionumbers = @_;
1217 my $dbh = C4::Context->dbh;
1223 WHERE biblionumber IN (?' . ( ',?' x scalar @biblionumbers - 1 ) . ')
1225 my $sth = $dbh->prepare($query);
1226 $sth->execute(@biblionumbers);
1230 while ( my ( $itemnumber, $biblionumber ) = $sth->fetchrow_array ) {
1231 push @{ $itemnumbers_of{$biblionumber} }, $itemnumber;
1234 return \%itemnumbers_of;
1237 =head2 GetItemnumberFromBarcode
1241 $result = GetItemnumberFromBarcode($barcode);
1247 sub GetItemnumberFromBarcode {
1249 my $dbh = C4::Context->dbh;
1252 $dbh->prepare("SELECT itemnumber FROM items WHERE items.barcode=?");
1253 $rq->execute($barcode);
1254 my ($result) = $rq->fetchrow;
1258 =head1 LIMITED USE FUNCTIONS
1260 The following functions, while part of the public API,
1261 are not exported. This is generally because they are
1262 meant to be used by only one script for a specific
1263 purpose, and should not be used in any other context
1264 without careful thought.
1272 my $item_marc = GetMarcItem($biblionumber, $itemnumber);
1276 Returns MARC::Record of the item passed in parameter.
1277 This function is meant for use only in C<cataloguing/additem.pl>,
1278 where it is needed to support that script's MARC-like
1284 my ( $biblionumber, $itemnumber ) = @_;
1286 # GetMarcItem has been revised so that it does the following:
1287 # 1. Gets the item information from the items table.
1288 # 2. Converts it to a MARC field for storage in the bib record.
1290 # The previous behavior was:
1291 # 1. Get the bib record.
1292 # 2. Return the MARC tag corresponding to the item record.
1294 # The difference is that one treats the items row as authoritative,
1295 # while the other treats the MARC representation as authoritative
1296 # under certain circumstances.
1298 my $itemrecord = GetItem($itemnumber);
1300 # Tack on 'items.' prefix to column names so that TransformKohaToMarc will work.
1301 # Also, don't emit a subfield if the underlying field is blank.
1302 my $mungeditem = { map { $itemrecord->{$_} ne '' ? ("items.$_" => $itemrecord->{$_}) : () } keys %{ $itemrecord } };
1304 my $itemmarc = TransformKohaToMarc($mungeditem);
1309 =head1 PRIVATE FUNCTIONS AND VARIABLES
1311 The following functions are not meant to be called
1312 directly, but are documented in order to explain
1313 the inner workings of C<C4::Items>.
1317 =head2 %derived_columns
1319 This hash keeps track of item columns that
1320 are strictly derived from other columns in
1321 the item record and are not meant to be set
1324 Each key in the hash should be the name of a
1325 column (as named by TransformMarcToKoha). Each
1326 value should be hashref whose keys are the
1327 columns on which the derived column depends. The
1328 hashref should also contain a 'BUILDER' key
1329 that is a reference to a sub that calculates
1334 my %derived_columns = (
1335 'items.cn_sort' => {
1336 'itemcallnumber' => 1,
1337 'items.cn_source' => 1,
1338 'BUILDER' => \&_calc_items_cn_sort,
1342 =head2 _set_derived_columns_for_add
1346 _set_derived_column_for_add($item);
1350 Given an item hash representing a new item to be added,
1351 calculate any derived columns. Currently the only
1352 such column is C<items.cn_sort>.
1356 sub _set_derived_columns_for_add {
1359 foreach my $column (keys %derived_columns) {
1360 my $builder = $derived_columns{$column}->{'BUILDER'};
1361 my $source_values = {};
1362 foreach my $source_column (keys %{ $derived_columns{$column} }) {
1363 next if $source_column eq 'BUILDER';
1364 $source_values->{$source_column} = $item->{$source_column};
1366 $builder->($item, $source_values);
1370 =head2 _set_derived_columns_for_mod
1374 _set_derived_column_for_mod($item);
1378 Given an item hash representing a new item to be modified.
1379 calculate any derived columns. Currently the only
1380 such column is C<items.cn_sort>.
1382 This routine differs from C<_set_derived_columns_for_add>
1383 in that it needs to handle partial item records. In other
1384 words, the caller of C<ModItem> may have supplied only one
1385 or two columns to be changed, so this function needs to
1386 determine whether any of the columns to be changed affect
1387 any of the derived columns. Also, if a derived column
1388 depends on more than one column, but the caller is not
1389 changing all of then, this routine retrieves the unchanged
1390 values from the database in order to ensure a correct
1395 sub _set_derived_columns_for_mod {
1398 foreach my $column (keys %derived_columns) {
1399 my $builder = $derived_columns{$column}->{'BUILDER'};
1400 my $source_values = {};
1401 my %missing_sources = ();
1402 my $must_recalc = 0;
1403 foreach my $source_column (keys %{ $derived_columns{$column} }) {
1404 next if $source_column eq 'BUILDER';
1405 if (exists $item->{$source_column}) {
1407 $source_values->{$source_column} = $item->{$source_column};
1409 $missing_sources{$source_column} = 1;
1413 foreach my $source_column (keys %missing_sources) {
1414 $source_values->{$source_column} = _get_single_item_column($source_column, $item->{'itemnumber'});
1416 $builder->($item, $source_values);
1421 =head2 _do_column_fixes_for_mod
1425 _do_column_fixes_for_mod($item);
1429 Given an item hashref containing one or more
1430 columns to modify, fix up certain values.
1431 Specifically, set to 0 any passed value
1432 of C<notforloan>, C<damaged>, C<itemlost>, or
1433 C<wthdrawn> that is either undefined or
1434 contains the empty string.
1438 sub _do_column_fixes_for_mod {
1441 if (exists $item->{'notforloan'} and
1442 (not defined $item->{'notforloan'} or $item->{'notforloan'} eq '')) {
1443 $item->{'notforloan'} = 0;
1445 if (exists $item->{'damaged'} and
1446 (not defined $item->{'damaged'} or $item->{'damaged'} eq '')) {
1447 $item->{'damaged'} = 0;
1449 if (exists $item->{'itemlost'} and
1450 (not defined $item->{'itemlost'} or $item->{'itemlost'} eq '')) {
1451 $item->{'itemlost'} = 0;
1453 if (exists $item->{'wthdrawn'} and
1454 (not defined $item->{'wthdrawn'} or $item->{'wthdrawn'} eq '')) {
1455 $item->{'wthdrawn'} = 0;
1459 =head2 _get_single_item_column
1463 _get_single_item_column($column, $itemnumber);
1467 Retrieves the value of a single column from an C<items>
1468 row specified by C<$itemnumber>.
1472 sub _get_single_item_column {
1474 my $itemnumber = shift;
1476 my $dbh = C4::Context->dbh;
1477 my $sth = $dbh->prepare("SELECT $column FROM items WHERE itemnumber = ?");
1478 $sth->execute($itemnumber);
1479 my ($value) = $sth->fetchrow();
1483 =head2 _calc_items_cn_sort
1487 _calc_items_cn_sort($item, $source_values);
1491 Helper routine to calculate C<items.cn_sort>.
1495 sub _calc_items_cn_sort {
1497 my $source_values = shift;
1499 $item->{'items.cn_sort'} = GetClassSort($source_values->{'items.cn_source'}, $source_values->{'itemcallnumber'}, "");
1502 =head2 _set_defaults_for_add
1506 _set_defaults_for_add($item_hash);
1510 Given an item hash representing an item to be added, set
1511 correct default values for columns whose default value
1512 is not handled by the DBMS. This includes the following
1519 C<items.dateaccessioned>
1541 sub _set_defaults_for_add {
1544 # if dateaccessioned is provided, use it. Otherwise, set to NOW()
1545 if (!(exists $item->{'dateaccessioned'}) ||
1546 ($item->{'dateaccessioned'} eq '')) {
1547 # FIXME add check for invalid date
1548 my $today = C4::Dates->new();
1549 $item->{'dateaccessioned'} = $today->output("iso"); #TODO: check time issues
1552 # various item status fields cannot be null
1553 $item->{'notforloan'} = 0 unless exists $item->{'notforloan'} and defined $item->{'notforloan'} and $item->{'notforloan'} ne '';
1554 $item->{'damaged'} = 0 unless exists $item->{'damaged'} and defined $item->{'damaged'} and $item->{'damaged'} ne '';
1555 $item->{'itemlost'} = 0 unless exists $item->{'itemlost'} and defined $item->{'itemlost'} and $item->{'itemlost'} ne '';
1556 $item->{'wthdrawn'} = 0 unless exists $item->{'wthdrawn'} and defined $item->{'wthdrawn'} and $item->{'wthdrawn'} ne '';
1559 =head2 _koha_new_item
1563 my ($itemnumber,$error) = _koha_new_item( $dbh, $item, $barcode );
1567 Perform the actual insert into the C<items> table.
1571 sub _koha_new_item {
1572 my ( $dbh, $item, $barcode ) = @_;
1576 "INSERT INTO items SET
1578 biblioitemnumber = ?,
1580 dateaccessioned = ?,
1584 replacementprice = ?,
1585 replacementpricedate = NOW(),
1586 datelastborrowed = ?,
1587 datelastseen = NOW(),
1610 my $sth = $dbh->prepare($query);
1612 $item->{'biblionumber'},
1613 $item->{'biblioitemnumber'},
1615 $item->{'dateaccessioned'},
1616 $item->{'booksellerid'},
1617 $item->{'homebranch'},
1619 $item->{'replacementprice'},
1620 $item->{datelastborrowed},
1622 $item->{'notforloan'},
1624 $item->{'itemlost'},
1625 $item->{'wthdrawn'},
1626 $item->{'itemcallnumber'},
1627 $item->{'restricted'},
1628 $item->{'itemnotes'},
1629 $item->{'holdingbranch'},
1631 $item->{'location'},
1634 $item->{'renewals'},
1635 $item->{'reserves'},
1636 $item->{'items.cn_source'},
1637 $item->{'items.cn_sort'},
1640 $item->{'materials'},
1643 my $itemnumber = $dbh->{'mysql_insertid'};
1644 if ( defined $sth->errstr ) {
1645 $error.="ERROR in _koha_new_item $query".$sth->errstr;
1648 return ( $itemnumber, $error );
1651 =head2 _koha_modify_item
1655 my ($itemnumber,$error) =_koha_modify_item( $dbh, $item, $op );
1659 Perform the actual update of the C<items> row. Note that this
1660 routine accepts a hashref specifying the columns to update.
1664 sub _koha_modify_item {
1665 my ( $dbh, $item ) = @_;
1668 my $query = "UPDATE items SET ";
1670 for my $key ( keys %$item ) {
1672 push @bind, $item->{$key};
1675 $query .= " WHERE itemnumber=?";
1676 push @bind, $item->{'itemnumber'};
1677 my $sth = $dbh->prepare($query);
1678 $sth->execute(@bind);
1679 if ( $dbh->errstr ) {
1680 $error.="ERROR in _koha_modify_item $query".$dbh->errstr;
1684 return ($item->{'itemnumber'},$error);
1687 =head2 _koha_delete_item
1691 _koha_delete_item( $dbh, $itemnum );
1695 Internal function to delete an item record from the koha tables
1699 sub _koha_delete_item {
1700 my ( $dbh, $itemnum ) = @_;
1702 # save the deleted item to deleteditems table
1703 my $sth = $dbh->prepare("SELECT * FROM items WHERE itemnumber=?");
1704 $sth->execute($itemnum);
1705 my $data = $sth->fetchrow_hashref();
1707 my $query = "INSERT INTO deleteditems SET ";
1709 foreach my $key ( keys %$data ) {
1710 $query .= "$key = ?,";
1711 push( @bind, $data->{$key} );
1714 $sth = $dbh->prepare($query);
1715 $sth->execute(@bind);
1718 # delete from items table
1719 $sth = $dbh->prepare("DELETE FROM items WHERE itemnumber=?");
1720 $sth->execute($itemnum);
1725 =head2 _marc_from_item_hash
1729 my $item_marc = _marc_from_item_hash($item, $frameworkcode);
1733 Given an item hash representing a complete item record,
1734 create a C<MARC::Record> object containing an embedded
1735 tag representing that item.
1739 sub _marc_from_item_hash {
1741 my $frameworkcode = shift;
1743 # Tack on 'items.' prefix to column names so lookup from MARC frameworks will work
1744 # Also, don't emit a subfield if the underlying field is blank.
1745 my $mungeditem = { map { (defined($item->{$_}) and $item->{$_} ne '') ?
1746 (/^items\./ ? ($_ => $item->{$_}) : ("items.$_" => $item->{$_}))
1747 : () } keys %{ $item } };
1749 my $item_marc = MARC::Record->new();
1750 foreach my $item_field (keys %{ $mungeditem }) {
1751 my ($tag, $subfield) = GetMarcFromKohaField($item_field, $frameworkcode);
1752 next unless defined $tag and defined $subfield; # skip if not mapped to MARC field
1753 if (my $field = $item_marc->field($tag)) {
1754 $field->add_subfields($subfield => $mungeditem->{$item_field});
1756 $item_marc->add_fields( $tag, " ", " ", $subfield => $mungeditem->{$item_field});
1763 =head2 _add_item_field_to_biblio
1767 _add_item_field_to_biblio($item_marc, $biblionumber, $frameworkcode);
1771 Adds the fields from a MARC record containing the
1772 representation of a Koha item record to the MARC
1773 biblio record. The input C<$item_marc> record
1774 is expect to contain just one field, the embedded
1775 item information field.
1779 sub _add_item_field_to_biblio {
1780 my ($item_marc, $biblionumber, $frameworkcode) = @_;
1782 my $biblio_marc = GetMarcBiblio($biblionumber);
1784 foreach my $field ($item_marc->fields()) {
1785 $biblio_marc->append_fields($field);
1788 ModBiblioMarc($biblio_marc, $biblionumber, $frameworkcode);
1791 =head2 _replace_item_field_in_biblio
1795 &_replace_item_field_in_biblio($item_marc, $biblionumber, $itemnumber, $frameworkcode)
1799 Given a MARC::Record C<$item_marc> containing one tag with the MARC
1800 representation of the item, examine the biblio MARC
1801 for the corresponding tag for that item and
1802 replace it with the tag from C<$item_marc>.
1806 sub _replace_item_field_in_biblio {
1807 my ($ItemRecord, $biblionumber, $itemnumber, $frameworkcode) = @_;
1808 my $dbh = C4::Context->dbh;
1810 # get complete MARC record & replace the item field by the new one
1811 my $completeRecord = GetMarcBiblio($biblionumber);
1812 my ($itemtag,$itemsubfield) = GetMarcFromKohaField("items.itemnumber",$frameworkcode);
1813 my $itemField = $ItemRecord->field($itemtag);
1814 my @items = $completeRecord->field($itemtag);
1817 if ($_->subfield($itemsubfield) eq $itemnumber) {
1818 $_->replace_with($itemField);
1824 # If we haven't found the matching field,
1825 # just add it. However, this means that
1826 # there is likely a bug.
1827 $completeRecord->append_fields($itemField);
1831 ModBiblioMarc($completeRecord, $biblionumber, $frameworkcode);
1834 =head2 _repack_item_errors
1836 Add an error message hash generated by C<CheckItemPreSave>
1837 to a list of errors.
1841 sub _repack_item_errors {
1842 my $item_sequence_num = shift;
1843 my $item_ref = shift;
1844 my $error_ref = shift;
1846 my @repacked_errors = ();
1848 foreach my $error_code (sort keys %{ $error_ref }) {
1849 my $repacked_error = {};
1850 $repacked_error->{'item_sequence'} = $item_sequence_num;
1851 $repacked_error->{'item_barcode'} = exists($item_ref->{'barcode'}) ? $item_ref->{'barcode'} : '';
1852 $repacked_error->{'error_code'} = $error_code;
1853 $repacked_error->{'error_information'} = $error_ref->{$error_code};
1854 push @repacked_errors, $repacked_error;
1857 return @repacked_errors;