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/;
32 use vars qw($VERSION @ISA @EXPORT);
38 @ISA = qw( Exporter );
60 GetItemsByBiblioitemnumber
63 GetItemnumberFromBarcode
69 C4::Items - item management functions
73 This module contains an API for manipulating item
74 records in Koha, and is used by cataloguing, circulation,
75 acquisitions, and serials management.
77 A Koha item record is stored in two places: the
78 items table and embedded in a MARC tag in the XML
79 version of the associated bib record in C<biblioitems.marcxml>.
80 This is done to allow the item information to be readily
81 indexed (e.g., by Zebra), but means that each item
82 modification transaction must keep the items table
83 and the MARC XML in sync at all times.
85 Consequently, all code that creates, modifies, or deletes
86 item records B<must> use an appropriate function from
87 C<C4::Items>. If no existing function is suitable, it is
88 better to add one to C<C4::Items> than to use add
89 one-off SQL statements to add or modify items.
91 The items table will be considered authoritative. In other
92 words, if there is ever a discrepancy between the items
93 table and the MARC XML, the items table should be considered
96 =head1 HISTORICAL NOTE
98 Most of the functions in C<C4::Items> were originally in
99 the C<C4::Biblio> module.
101 =head1 CORE EXPORTED FUNCTIONS
103 The following functions are meant for use by users
112 $item = GetItem($itemnumber,$barcode);
116 Return item information, for a given itemnumber or barcode.
117 The return value is a hashref mapping item column
123 my ($itemnumber,$barcode) = @_;
124 my $dbh = C4::Context->dbh;
126 my $sth = $dbh->prepare("
128 WHERE itemnumber = ?");
129 $sth->execute($itemnumber);
130 my $data = $sth->fetchrow_hashref;
133 my $sth = $dbh->prepare("
137 $sth->execute($barcode);
138 my $data = $sth->fetchrow_hashref;
143 =head2 AddItemFromMarc
147 my ($biblionumber, $biblioitemnumber, $itemnumber)
148 = AddItemFromMarc($source_item_marc, $biblionumber);
152 Given a MARC::Record object containing an embedded item
153 record and a biblionumber, create a new item record.
157 sub AddItemFromMarc {
158 my ( $source_item_marc, $biblionumber ) = @_;
159 my $dbh = C4::Context->dbh;
161 # parse item hash from MARC
162 my $frameworkcode = GetFrameworkCode( $biblionumber );
163 my $item = &TransformMarcToKoha( $dbh, $source_item_marc, $frameworkcode );
165 return AddItem($item, $biblionumber, $dbh, $frameworkcode);
172 my ($biblionumber, $biblioitemnumber, $itemnumber)
173 = AddItem($item, $biblionumber[, $dbh, $frameworkcode]);
177 Given a hash containing item column names as keys,
178 create a new Koha item record.
180 The two optional parameters (C<$dbh> and C<$frameworkcode>)
181 do not need to be supplied for general use; they exist
182 simply to allow them to be picked up from AddItemFromMarc.
188 my $biblionumber = shift;
190 my $dbh = @_ ? shift : C4::Context->dbh;
191 my $frameworkcode = @_ ? shift : GetFrameworkCode( $biblionumber );
193 # needs old biblionumber and biblioitemnumber
194 $item->{'biblionumber'} = $biblionumber;
195 my $sth = $dbh->prepare("SELECT biblioitemnumber FROM biblioitems WHERE biblionumber=?");
196 $sth->execute( $item->{'biblionumber'} );
197 ($item->{'biblioitemnumber'}) = $sth->fetchrow;
199 _set_defaults_for_add($item);
200 _set_derived_columns_for_add($item);
201 # FIXME - checks here
202 my ( $itemnumber, $error ) = _koha_new_item( $dbh, $item, $item->{barcode} );
203 $item->{'itemnumber'} = $itemnumber;
205 # create MARC tag representing item and add to bib
206 my $new_item_marc = _marc_from_item_hash($item, $frameworkcode);
207 _add_item_field_to_biblio($new_item_marc, $item->{'biblionumber'}, $frameworkcode );
209 logaction(C4::Context->userenv->{'number'},"CATALOGUING","ADD",$itemnumber,"item")
210 if C4::Context->preference("CataloguingLog");
212 return ($item->{biblionumber}, $item->{biblioitemnumber}, $itemnumber);
215 =head2 AddItemBatchFromMarc
219 ($itemnumber_ref, $error_ref) = AddItemBatchFromMarc($record, $biblionumber, $biblioitemnumber, $frameworkcode);
223 Efficiently create item records from a MARC biblio record with
224 embedded item fields. This routine is suitable for batch jobs.
226 This API assumes that the bib record has already been
227 saved to the C<biblio> and C<biblioitems> tables. It does
228 not expect that C<biblioitems.marc> and C<biblioitems.marcxml>
229 are populated, but it will do so via a call to ModBibiloMarc.
231 The goal of this API is to have a similar effect to using AddBiblio
232 and AddItems in succession, but without inefficient repeated
233 parsing of the MARC XML bib record.
235 This function returns an arrayref of new itemsnumbers and an arrayref of item
236 errors encountered during the processing. Each entry in the errors
237 list is a hashref containing the following keys:
243 Sequence number of original item tag in the MARC record.
247 Item barcode, provide to assist in the construction of
248 useful error messages.
250 =item error_condition
252 Code representing the error condition. Can be 'duplicate_barcode',
253 'invalid_homebranch', or 'invalid_holdingbranch'.
255 =item error_information
257 Additional information appropriate to the error condition.
263 sub AddItemBatchFromMarc {
264 my ($record, $biblionumber, $biblioitemnumber, $frameworkcode) = @_;
266 my @itemnumbers = ();
268 my $dbh = C4::Context->dbh;
270 # loop through the item tags and start creating items
271 my @bad_item_fields = ();
272 my ($itemtag, $itemsubfield) = &GetMarcFromKohaField("items.itemnumber",'');
273 my $item_sequence_num = 0;
274 ITEMFIELD: foreach my $item_field ($record->field($itemtag)) {
275 $item_sequence_num++;
276 # we take the item field and stick it into a new
277 # MARC record -- this is required so far because (FIXME)
278 # TransformMarcToKoha requires a MARC::Record, not a MARC::Field
279 # and there is no TransformMarcFieldToKoha
280 my $temp_item_marc = MARC::Record->new();
281 $temp_item_marc->append_fields($item_field);
283 # add biblionumber and biblioitemnumber
284 my $item = TransformMarcToKoha( $dbh, $temp_item_marc, $frameworkcode, 'items' );
285 $item->{'biblionumber'} = $biblionumber;
286 $item->{'biblioitemnumber'} = $biblioitemnumber;
288 # check for duplicate barcode
289 my %item_errors = CheckItemPreSave($item);
291 push @errors, _repack_item_errors($item_sequence_num, $item, \%item_errors);
292 push @bad_item_fields, $item_field;
296 _set_defaults_for_add($item);
297 _set_derived_columns_for_add($item);
298 my ( $itemnumber, $error ) = _koha_new_item( $dbh, $item, $item->{barcode} );
299 warn $error if $error;
300 push @itemnumbers, $itemnumber; # FIXME not checking error
301 $item->{'itemnumber'} = $itemnumber;
303 &logaction(C4::Context->userenv->{'number'},"CATALOGUING","ADD",$itemnumber,"item")
304 if C4::Context->preference("CataloguingLog");
306 my $new_item_marc = _marc_from_item_hash($item, $frameworkcode);
307 $item_field->replace_with($new_item_marc->field($itemtag));
310 # remove any MARC item fields for rejected items
311 foreach my $item_field (@bad_item_fields) {
312 $record->delete_field($item_field);
315 # update the MARC biblio
316 $biblionumber = ModBiblioMarc( $record, $biblionumber, $frameworkcode );
318 return (\@itemnumbers, \@errors);
321 =head2 ModItemFromMarc
325 ModItemFromMarc($item_marc, $biblionumber, $itemnumber);
329 This function updates an item record based on a supplied
330 C<MARC::Record> object containing an embedded item field.
331 This API is meant for the use of C<additem.pl>; for
332 other purposes, C<ModItem> should be used.
336 sub ModItemFromMarc {
337 my $item_marc = shift;
338 my $biblionumber = shift;
339 my $itemnumber = shift;
341 my $dbh = C4::Context->dbh;
342 my $frameworkcode = GetFrameworkCode( $biblionumber );
343 my $item = &TransformMarcToKoha( $dbh, $item_marc, $frameworkcode );
345 return ModItem($item, $biblionumber, $itemnumber, $dbh, $frameworkcode);
352 ModItem({ column => $newvalue }, $biblionumber, $itemnumber);
356 Change one or more columns in an item record and update
357 the MARC representation of the item.
359 The first argument is a hashref mapping from item column
360 names to the new values. The second and third arguments
361 are the biblionumber and itemnumber, respectively.
363 If one of the changed columns is used to calculate
364 the derived value of a column such as C<items.cn_sort>,
365 this routine will perform the necessary calculation
372 my $biblionumber = shift;
373 my $itemnumber = shift;
375 # if $biblionumber is undefined, get it from the current item
376 unless (defined $biblionumber) {
377 $biblionumber = _get_single_item_column('biblionumber', $itemnumber);
380 my $dbh = @_ ? shift : C4::Context->dbh;
381 my $frameworkcode = @_ ? shift : GetFrameworkCode( $biblionumber );
383 $item->{'itemnumber'} = $itemnumber or return undef;
384 _set_derived_columns_for_mod($item);
385 _do_column_fixes_for_mod($item);
388 # attempt to change itemnumber
389 # attempt to change biblionumber (if we want
390 # an API to relink an item to a different bib,
391 # it should be a separate function)
394 _koha_modify_item($dbh, $item);
396 # update biblio MARC XML
397 my $whole_item = GetItem($itemnumber) or die "FAILED GetItem($itemnumber)";
398 my $new_item_marc = _marc_from_item_hash($whole_item, $frameworkcode) or die "FAILED _marc_from_item_hash($whole_item, $frameworkcode)";
399 _replace_item_field_in_biblio($new_item_marc, $biblionumber, $itemnumber, $frameworkcode);
400 (C4::Context->userenv eq '0') and die "userenv is '0', not hashref"; # logaction line would crash anyway
401 ($new_item_marc eq '0') and die "$new_item_marc is '0', not hashref"; # logaction line would crash anyway
402 logaction(C4::Context->userenv->{'number'},"CATALOGUING","MODIFY",$itemnumber,$new_item_marc->as_formatted)
403 if C4::Context->preference("CataloguingLog");
406 =head2 ModItemTransfer
410 ModItemTransfer($itenumber, $frombranch, $tobranch);
414 Marks an item as being transferred from one branch
419 sub ModItemTransfer {
420 my ( $itemnumber, $frombranch, $tobranch ) = @_;
422 my $dbh = C4::Context->dbh;
424 #new entry in branchtransfers....
425 my $sth = $dbh->prepare(
426 "INSERT INTO branchtransfers (itemnumber, frombranch, datesent, tobranch)
427 VALUES (?, ?, NOW(), ?)");
428 $sth->execute($itemnumber, $frombranch, $tobranch);
430 ModItem({ holdingbranch => $tobranch }, undef, $itemnumber);
431 ModDateLastSeen($itemnumber);
435 =head2 ModDateLastSeen
439 ModDateLastSeen($itemnum);
443 Mark item as seen. Is called when an item is issued, returned or manually marked during inventory/stocktaking.
444 C<$itemnum> is the item number
448 sub ModDateLastSeen {
449 my ($itemnumber) = @_;
451 my $today = C4::Dates->new();
452 ModItem({ itemlost => 0, datelastseen => $today->output("iso") }, undef, $itemnumber);
459 DelItem($biblionumber, $itemnumber);
463 Exported function (core API) for deleting an item record in Koha.
468 my ( $dbh, $biblionumber, $itemnumber ) = @_;
470 # FIXME check the item has no current issues
472 _koha_delete_item( $dbh, $itemnumber );
474 # get the MARC record
475 my $record = GetMarcBiblio($biblionumber);
476 my $frameworkcode = GetFrameworkCode($biblionumber);
479 my $copy2deleted = $dbh->prepare("UPDATE deleteditems SET marc=? WHERE itemnumber=?");
480 $copy2deleted->execute( $record->as_usmarc(), $itemnumber );
482 #search item field code
483 my ( $itemtag, $itemsubfield ) = GetMarcFromKohaField("items.itemnumber",$frameworkcode);
484 my @fields = $record->field($itemtag);
486 # delete the item specified
487 foreach my $field (@fields) {
488 if ( $field->subfield($itemsubfield) eq $itemnumber ) {
489 $record->delete_field($field);
492 &ModBiblioMarc( $record, $biblionumber, $frameworkcode );
493 &logaction(C4::Context->userenv->{'number'},"CATALOGUING","DELETE",$itemnumber,"item")
494 if C4::Context->preference("CataloguingLog");
497 =head2 CheckItemPreSave
501 my $item_ref = TransformMarcToKoha($marc, 'items');
503 my %errors = CheckItemPreSave($item_ref);
504 if (exists $errors{'duplicate_barcode'}) {
505 print "item has duplicate barcode: ", $errors{'duplicate_barcode'}, "\n";
506 } elsif (exists $errors{'invalid_homebranch'}) {
507 print "item has invalid home branch: ", $errors{'invalid_homebranch'}, "\n";
508 } elsif (exists $errors{'invalid_holdingbranch'}) {
509 print "item has invalid holding branch: ", $errors{'invalid_holdingbranch'}, "\n";
516 Given a hashref containing item fields, determine if it can be
517 inserted or updated in the database. Specifically, checks for
518 database integrity issues, and returns a hash containing any
519 of the following keys, if applicable.
523 =item duplicate_barcode
525 Barcode, if it duplicates one already found in the database.
527 =item invalid_homebranch
529 Home branch, if not defined in branches table.
531 =item invalid_holdingbranch
533 Holding branch, if not defined in branches table.
537 This function does NOT implement any policy-related checks,
538 e.g., whether current operator is allowed to save an
539 item that has a given branch code.
543 sub CheckItemPreSave {
544 my $item_ref = shift;
548 # check for duplicate barcode
549 if (exists $item_ref->{'barcode'} and defined $item_ref->{'barcode'}) {
550 my $existing_itemnumber = GetItemnumberFromBarcode($item_ref->{'barcode'});
551 if ($existing_itemnumber) {
552 if (!exists $item_ref->{'itemnumber'} # new item
553 or $item_ref->{'itemnumber'} != $existing_itemnumber) { # existing item
554 $errors{'duplicate_barcode'} = $item_ref->{'barcode'};
559 # check for valid home branch
560 if (exists $item_ref->{'homebranch'} and defined $item_ref->{'homebranch'}) {
561 my $branch_name = GetBranchName($item_ref->{'homebranch'});
562 unless (defined $branch_name) {
563 # relies on fact that branches.branchname is a non-NULL column,
564 # so GetBranchName returns undef only if branch does not exist
565 $errors{'invalid_homebranch'} = $item_ref->{'homebranch'};
569 # check for valid holding branch
570 if (exists $item_ref->{'holdingbranch'} and defined $item_ref->{'holdingbranch'}) {
571 my $branch_name = GetBranchName($item_ref->{'holdingbranch'});
572 unless (defined $branch_name) {
573 # relies on fact that branches.branchname is a non-NULL column,
574 # so GetBranchName returns undef only if branch does not exist
575 $errors{'invalid_holdingbranch'} = $item_ref->{'holdingbranch'};
583 =head1 EXPORTED SPECIAL ACCESSOR FUNCTIONS
585 The following functions provide various ways of
586 getting an item record, a set of item records, or
587 lists of authorized values for certain item fields.
589 Some of the functions in this group are candidates
590 for refactoring -- for example, some of the code
591 in C<GetItemsByBiblioitemnumber> and C<GetItemsInfo>
592 has copy-and-paste work.
600 $itemstatushash = GetItemStatus($fwkcode);
604 Returns a list of valid values for the
605 C<items.notforloan> field.
607 NOTE: does B<not> return an individual item's
610 Can be MARC dependant.
612 But basically could be can be loan or not
613 Create a status selector with the following code
615 =head3 in PERL SCRIPT
619 my $itemstatushash = getitemstatus;
621 foreach my $thisstatus (keys %$itemstatushash) {
622 my %row =(value => $thisstatus,
623 statusname => $itemstatushash->{$thisstatus}->{'statusname'},
625 push @itemstatusloop, \%row;
627 $template->param(statusloop=>\@itemstatusloop);
635 <select name="statusloop">
636 <option value="">Default</option>
637 <!-- TMPL_LOOP name="statusloop" -->
638 <option value="<!-- TMPL_VAR name="value" -->" <!-- TMPL_IF name="selected" -->selected<!-- /TMPL_IF -->><!-- TMPL_VAR name="statusname" --></option>
648 # returns a reference to a hash of references to status...
651 my $dbh = C4::Context->dbh;
653 $fwk = '' unless ($fwk);
654 my ( $tag, $subfield ) =
655 GetMarcFromKohaField( "items.notforloan", $fwk );
656 if ( $tag and $subfield ) {
659 "SELECT authorised_value
660 FROM marc_subfield_structure
666 $sth->execute( $tag, $subfield, $fwk );
667 if ( my ($authorisedvaluecat) = $sth->fetchrow ) {
670 "SELECT authorised_value,lib
671 FROM authorised_values
676 $authvalsth->execute($authorisedvaluecat);
677 while ( my ( $authorisedvalue, $lib ) = $authvalsth->fetchrow ) {
678 $itemstatus{$authorisedvalue} = $lib;
694 $itemstatus{"1"} = "Not For Loan";
698 =head2 GetItemLocation
702 $itemlochash = GetItemLocation($fwk);
706 Returns a list of valid values for the
707 C<items.location> field.
709 NOTE: does B<not> return an individual item's
712 where fwk stands for an optional framework code.
713 Create a location selector with the following code
715 =head3 in PERL SCRIPT
719 my $itemlochash = getitemlocation;
721 foreach my $thisloc (keys %$itemlochash) {
722 my $selected = 1 if $thisbranch eq $branch;
723 my %row =(locval => $thisloc,
724 selected => $selected,
725 locname => $itemlochash->{$thisloc},
727 push @itemlocloop, \%row;
729 $template->param(itemlocationloop => \@itemlocloop);
737 <select name="location">
738 <option value="">Default</option>
739 <!-- TMPL_LOOP name="itemlocationloop" -->
740 <option value="<!-- TMPL_VAR name="locval" -->" <!-- TMPL_IF name="selected" -->selected<!-- /TMPL_IF -->><!-- TMPL_VAR name="locname" --></option>
748 sub GetItemLocation {
750 # returns a reference to a hash of references to location...
753 my $dbh = C4::Context->dbh;
755 $fwk = '' unless ($fwk);
756 my ( $tag, $subfield ) =
757 GetMarcFromKohaField( "items.location", $fwk );
758 if ( $tag and $subfield ) {
761 "SELECT authorised_value
762 FROM marc_subfield_structure
767 $sth->execute( $tag, $subfield, $fwk );
768 if ( my ($authorisedvaluecat) = $sth->fetchrow ) {
771 "SELECT authorised_value,lib
772 FROM authorised_values
776 $authvalsth->execute($authorisedvaluecat);
777 while ( my ( $authorisedvalue, $lib ) = $authvalsth->fetchrow ) {
778 $itemlocation{$authorisedvalue} = $lib;
781 return \%itemlocation;
794 $itemlocation{"1"} = "Not For Loan";
795 return \%itemlocation;
802 $items = GetLostItems($where,$orderby);
806 This function get the items lost into C<$items>.
811 C<$where> is a hashref. it containts a field of the items table as key
812 and the value to match as value.
813 C<$orderby> is a field of the items table.
816 C<$items> is a reference to an array full of hasref which keys are items' table column.
818 =item usage in the perl script:
821 $where{barcode} = 0001548;
822 my $items = GetLostItems( \%where, "homebranch" );
823 $template->param(itemsloop => $items);
830 # Getting input args.
833 my $dbh = C4::Context->dbh;
838 WHERE itemlost IS NOT NULL
841 foreach my $key (keys %$where) {
842 $query .= " AND " . $key . " LIKE '%" . $where->{$key} . "%'";
844 $query .= " ORDER BY ".$orderby if defined $orderby;
846 my $sth = $dbh->prepare($query);
849 while ( my $row = $sth->fetchrow_hashref ){
855 =head2 GetItemsForInventory
859 $itemlist = GetItemsForInventory($minlocation,$maxlocation,$datelastseen,$offset,$size)
863 Retrieve a list of title/authors/barcode/callnumber, for biblio inventory.
865 The sub returns a list of hashes, containing itemnumber, author, title, barcode & item callnumber.
866 It is ordered by callnumber,title.
868 The minlocation & maxlocation parameters are used to specify a range of item callnumbers
869 the datelastseen can be used to specify that you want to see items not seen since a past date only.
870 offset & size can be used to retrieve only a part of the whole listing (defaut behaviour)
874 sub GetItemsForInventory {
875 my ( $minlocation, $maxlocation,$location, $datelastseen, $branch, $offset, $size ) = @_;
876 my $dbh = C4::Context->dbh;
879 $datelastseen=format_date_in_iso($datelastseen);
881 "SELECT itemnumber,barcode,itemcallnumber,title,author,biblio.biblionumber,datelastseen
883 LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber
884 WHERE itemcallnumber>= ?
885 AND itemcallnumber <=?
886 AND (datelastseen< ? OR datelastseen IS NULL)";
887 $query.= " AND items.location=".$dbh->quote($location) if $location;
888 $query.= " AND items.homebranch=".$dbh->quote($branch) if $branch;
889 $query .= " ORDER BY itemcallnumber,title";
890 $sth = $dbh->prepare($query);
891 $sth->execute( $minlocation, $maxlocation, $datelastseen );
895 SELECT itemnumber,barcode,itemcallnumber,biblio.biblionumber,title,author,datelastseen
897 LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber
898 WHERE itemcallnumber>= ?
899 AND itemcallnumber <=?";
900 $query.= " AND items.location=".$dbh->quote($location) if $location;
901 $query.= " AND items.homebranch=".$dbh->quote($branch) if $branch;
902 $query .= " ORDER BY itemcallnumber,title";
903 $sth = $dbh->prepare($query);
904 $sth->execute( $minlocation, $maxlocation );
907 while ( my $row = $sth->fetchrow_hashref ) {
908 $offset-- if ($offset);
909 $row->{datelastseen}=format_date($row->{datelastseen});
910 if ( ( !$offset ) && $size ) {
921 $count = &GetItemsCount( $biblionumber);
925 This function return count of item with $biblionumber
930 my ( $biblionumber ) = @_;
931 my $dbh = C4::Context->dbh;
932 my $query = "SELECT count(*)
934 WHERE biblionumber=?";
935 my $sth = $dbh->prepare($query);
936 $sth->execute($biblionumber);
937 my $count = $sth->fetchrow;
942 =head2 GetItemInfosOf
946 GetItemInfosOf(@itemnumbers);
953 my @itemnumbers = @_;
958 WHERE itemnumber IN (' . join( ',', @itemnumbers ) . ')
960 return get_infos_of( $query, 'itemnumber' );
963 =head2 GetItemsByBiblioitemnumber
967 GetItemsByBiblioitemnumber($biblioitemnumber);
971 Returns an arrayref of hashrefs suitable for use in a TMPL_LOOP
972 Called by C<C4::XISBN>
976 sub GetItemsByBiblioitemnumber {
977 my ( $bibitem ) = @_;
978 my $dbh = C4::Context->dbh;
979 my $sth = $dbh->prepare("SELECT * FROM items WHERE items.biblioitemnumber = ?") || die $dbh->errstr;
980 # Get all items attached to a biblioitem
983 $sth->execute($bibitem) || die $sth->errstr;
984 while ( my $data = $sth->fetchrow_hashref ) {
985 # Foreach item, get circulation information
986 my $sth2 = $dbh->prepare( "SELECT * FROM issues,borrowers
988 AND returndate is NULL
989 AND issues.borrowernumber = borrowers.borrowernumber"
991 $sth2->execute( $data->{'itemnumber'} );
992 if ( my $data2 = $sth2->fetchrow_hashref ) {
993 # if item is out, set the due date and who it is out too
994 $data->{'date_due'} = $data2->{'date_due'};
995 $data->{'cardnumber'} = $data2->{'cardnumber'};
996 $data->{'borrowernumber'} = $data2->{'borrowernumber'};
999 # set date_due to blank, so in the template we check itemlost, and wthdrawn
1000 $data->{'date_due'} = '';
1003 # Find the last 3 people who borrowed this item.
1004 my $query2 = "SELECT * FROM issues, borrowers WHERE itemnumber = ?
1005 AND issues.borrowernumber = borrowers.borrowernumber
1006 AND returndate is not NULL
1007 ORDER BY returndate desc,timestamp desc LIMIT 3";
1008 $sth2 = $dbh->prepare($query2) || die $dbh->errstr;
1009 $sth2->execute( $data->{'itemnumber'} ) || die $sth2->errstr;
1011 while ( my $data2 = $sth2->fetchrow_hashref ) {
1012 $data->{"timestamp$i2"} = $data2->{'timestamp'};
1013 $data->{"card$i2"} = $data2->{'cardnumber'};
1014 $data->{"borrower$i2"} = $data2->{'borrowernumber'};
1018 push(@results,$data);
1028 @results = GetItemsInfo($biblionumber, $type);
1032 Returns information about books with the given biblionumber.
1034 C<$type> may be either C<intra> or anything else. If it is not set to
1035 C<intra>, then the search will exclude lost, very overdue, and
1038 C<GetItemsInfo> returns a list of references-to-hash. Each element
1039 contains a number of keys. Most of them are table items from the
1040 C<biblio>, C<biblioitems>, C<items>, and C<itemtypes> tables in the
1041 Koha database. Other keys include:
1045 =item C<$data-E<gt>{branchname}>
1047 The name (not the code) of the branch to which the book belongs.
1049 =item C<$data-E<gt>{datelastseen}>
1051 This is simply C<items.datelastseen>, except that while the date is
1052 stored in YYYY-MM-DD format in the database, here it is converted to
1053 DD/MM/YYYY format. A NULL date is returned as C<//>.
1055 =item C<$data-E<gt>{datedue}>
1057 =item C<$data-E<gt>{class}>
1059 This is the concatenation of C<biblioitems.classification>, the book's
1060 Dewey code, and C<biblioitems.subclass>.
1062 =item C<$data-E<gt>{ocount}>
1064 I think this is the number of copies of the book available.
1066 =item C<$data-E<gt>{order}>
1068 If this is set, it is set to C<One Order>.
1075 my ( $biblionumber, $type ) = @_;
1076 my $dbh = C4::Context->dbh;
1077 my $query = "SELECT *,items.notforloan as itemnotforloan
1079 LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber
1080 LEFT JOIN biblioitems ON biblioitems.biblioitemnumber = items.biblioitemnumber";
1081 $query .= (C4::Context->preference('item-level_itypes')) ?
1082 " LEFT JOIN itemtypes on items.itype = itemtypes.itemtype "
1083 : " LEFT JOIN itemtypes on biblioitems.itemtype = itemtypes.itemtype ";
1084 $query .= "WHERE items.biblionumber = ? ORDER BY items.dateaccessioned desc" ;
1085 my $sth = $dbh->prepare($query);
1086 $sth->execute($biblionumber);
1089 my ( $date_due, $count_reserves );
1091 my $isth = $dbh->prepare(
1092 "SELECT issues.*,borrowers.cardnumber,borrowers.surname,borrowers.firstname,borrowers.branchcode as bcode
1093 FROM issues LEFT JOIN borrowers ON issues.borrowernumber=borrowers.borrowernumber
1094 WHERE itemnumber = ?
1095 AND returndate IS NULL"
1097 while ( my $data = $sth->fetchrow_hashref ) {
1099 $isth->execute( $data->{'itemnumber'} );
1100 if ( my $idata = $isth->fetchrow_hashref ) {
1101 $data->{borrowernumber} = $idata->{borrowernumber};
1102 $data->{cardnumber} = $idata->{cardnumber};
1103 $data->{surname} = $idata->{surname};
1104 $data->{firstname} = $idata->{firstname};
1105 $datedue = $idata->{'date_due'};
1106 if (C4::Context->preference("IndependantBranches")){
1107 my $userenv = C4::Context->userenv;
1108 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
1109 $data->{'NOTSAMEBRANCH'} = 1 if ($idata->{'bcode'} ne $userenv->{branch});
1113 if ( $datedue eq '' ) {
1114 my ( $restype, $reserves ) =
1115 C4::Reserves::CheckReserves( $data->{'itemnumber'} );
1117 $count_reserves = $restype;
1122 #get branch information.....
1123 my $bsth = $dbh->prepare(
1124 "SELECT * FROM branches WHERE branchcode = ?
1127 $bsth->execute( $data->{'holdingbranch'} );
1128 if ( my $bdata = $bsth->fetchrow_hashref ) {
1129 $data->{'branchname'} = $bdata->{'branchname'};
1131 $data->{'datedue'} = $datedue;
1132 $data->{'count_reserves'} = $count_reserves;
1134 # get notforloan complete status if applicable
1135 my $sthnflstatus = $dbh->prepare(
1136 'SELECT authorised_value
1137 FROM marc_subfield_structure
1138 WHERE kohafield="items.notforloan"
1142 $sthnflstatus->execute;
1143 my ($authorised_valuecode) = $sthnflstatus->fetchrow;
1144 if ($authorised_valuecode) {
1145 $sthnflstatus = $dbh->prepare(
1146 "SELECT lib FROM authorised_values
1148 AND authorised_value=?"
1150 $sthnflstatus->execute( $authorised_valuecode,
1151 $data->{itemnotforloan} );
1152 my ($lib) = $sthnflstatus->fetchrow;
1153 $data->{notforloan} = $lib;
1156 # my stack procedures
1157 my $stackstatus = $dbh->prepare(
1158 'SELECT authorised_value
1159 FROM marc_subfield_structure
1160 WHERE kohafield="items.stack"
1163 $stackstatus->execute;
1165 ($authorised_valuecode) = $stackstatus->fetchrow;
1166 if ($authorised_valuecode) {
1167 $stackstatus = $dbh->prepare(
1169 FROM authorised_values
1171 AND authorised_value=?
1174 $stackstatus->execute( $authorised_valuecode, $data->{stack} );
1175 my ($lib) = $stackstatus->fetchrow;
1176 $data->{stack} = $lib;
1178 # Find the last 3 people who borrowed this item.
1179 my $sth2 = $dbh->prepare("SELECT * FROM issues,borrowers
1180 WHERE itemnumber = ?
1181 AND issues.borrowernumber = borrowers.borrowernumber
1182 AND returndate IS NOT NULL LIMIT 3");
1183 $sth2->execute($data->{'itemnumber'});
1185 while (my $data2 = $sth2->fetchrow_hashref()) {
1186 $data->{"timestamp$ii"} = $data2->{'timestamp'} if $data2->{'timestamp'};
1187 $data->{"card$ii"} = $data2->{'cardnumber'} if $data2->{'cardnumber'};
1188 $data->{"borrower$ii"} = $data2->{'borrowernumber'} if $data2->{'borrowernumber'};
1192 $results[$i] = $data;
1200 =head2 get_itemnumbers_of
1204 my @itemnumbers_of = get_itemnumbers_of(@biblionumbers);
1208 Given a list of biblionumbers, return the list of corresponding itemnumbers
1209 for each biblionumber.
1211 Return a reference on a hash where keys are biblionumbers and values are
1212 references on array of itemnumbers.
1216 sub get_itemnumbers_of {
1217 my @biblionumbers = @_;
1219 my $dbh = C4::Context->dbh;
1225 WHERE biblionumber IN (?' . ( ',?' x scalar @biblionumbers - 1 ) . ')
1227 my $sth = $dbh->prepare($query);
1228 $sth->execute(@biblionumbers);
1232 while ( my ( $itemnumber, $biblionumber ) = $sth->fetchrow_array ) {
1233 push @{ $itemnumbers_of{$biblionumber} }, $itemnumber;
1236 return \%itemnumbers_of;
1239 =head2 GetItemnumberFromBarcode
1243 $result = GetItemnumberFromBarcode($barcode);
1249 sub GetItemnumberFromBarcode {
1251 my $dbh = C4::Context->dbh;
1254 $dbh->prepare("SELECT itemnumber FROM items WHERE items.barcode=?");
1255 $rq->execute($barcode);
1256 my ($result) = $rq->fetchrow;
1260 =head1 LIMITED USE FUNCTIONS
1262 The following functions, while part of the public API,
1263 are not exported. This is generally because they are
1264 meant to be used by only one script for a specific
1265 purpose, and should not be used in any other context
1266 without careful thought.
1274 my $item_marc = GetMarcItem($biblionumber, $itemnumber);
1278 Returns MARC::Record of the item passed in parameter.
1279 This function is meant for use only in C<cataloguing/additem.pl>,
1280 where it is needed to support that script's MARC-like
1286 my ( $biblionumber, $itemnumber ) = @_;
1288 # GetMarcItem has been revised so that it does the following:
1289 # 1. Gets the item information from the items table.
1290 # 2. Converts it to a MARC field for storage in the bib record.
1292 # The previous behavior was:
1293 # 1. Get the bib record.
1294 # 2. Return the MARC tag corresponding to the item record.
1296 # The difference is that one treats the items row as authoritative,
1297 # while the other treats the MARC representation as authoritative
1298 # under certain circumstances.
1300 my $itemrecord = GetItem($itemnumber);
1302 # Tack on 'items.' prefix to column names so that TransformKohaToMarc will work.
1303 # Also, don't emit a subfield if the underlying field is blank.
1304 my $mungeditem = { map { $itemrecord->{$_} ne '' ? ("items.$_" => $itemrecord->{$_}) : () } keys %{ $itemrecord } };
1306 my $itemmarc = TransformKohaToMarc($mungeditem);
1311 =head1 PRIVATE FUNCTIONS AND VARIABLES
1313 The following functions are not meant to be called
1314 directly, but are documented in order to explain
1315 the inner workings of C<C4::Items>.
1319 =head2 %derived_columns
1321 This hash keeps track of item columns that
1322 are strictly derived from other columns in
1323 the item record and are not meant to be set
1326 Each key in the hash should be the name of a
1327 column (as named by TransformMarcToKoha). Each
1328 value should be hashref whose keys are the
1329 columns on which the derived column depends. The
1330 hashref should also contain a 'BUILDER' key
1331 that is a reference to a sub that calculates
1336 my %derived_columns = (
1337 'items.cn_sort' => {
1338 'itemcallnumber' => 1,
1339 'items.cn_source' => 1,
1340 'BUILDER' => \&_calc_items_cn_sort,
1344 =head2 _set_derived_columns_for_add
1348 _set_derived_column_for_add($item);
1352 Given an item hash representing a new item to be added,
1353 calculate any derived columns. Currently the only
1354 such column is C<items.cn_sort>.
1358 sub _set_derived_columns_for_add {
1361 foreach my $column (keys %derived_columns) {
1362 my $builder = $derived_columns{$column}->{'BUILDER'};
1363 my $source_values = {};
1364 foreach my $source_column (keys %{ $derived_columns{$column} }) {
1365 next if $source_column eq 'BUILDER';
1366 $source_values->{$source_column} = $item->{$source_column};
1368 $builder->($item, $source_values);
1372 =head2 _set_derived_columns_for_mod
1376 _set_derived_column_for_mod($item);
1380 Given an item hash representing a new item to be modified.
1381 calculate any derived columns. Currently the only
1382 such column is C<items.cn_sort>.
1384 This routine differs from C<_set_derived_columns_for_add>
1385 in that it needs to handle partial item records. In other
1386 words, the caller of C<ModItem> may have supplied only one
1387 or two columns to be changed, so this function needs to
1388 determine whether any of the columns to be changed affect
1389 any of the derived columns. Also, if a derived column
1390 depends on more than one column, but the caller is not
1391 changing all of then, this routine retrieves the unchanged
1392 values from the database in order to ensure a correct
1397 sub _set_derived_columns_for_mod {
1400 foreach my $column (keys %derived_columns) {
1401 my $builder = $derived_columns{$column}->{'BUILDER'};
1402 my $source_values = {};
1403 my %missing_sources = ();
1404 my $must_recalc = 0;
1405 foreach my $source_column (keys %{ $derived_columns{$column} }) {
1406 next if $source_column eq 'BUILDER';
1407 if (exists $item->{$source_column}) {
1409 $source_values->{$source_column} = $item->{$source_column};
1411 $missing_sources{$source_column} = 1;
1415 foreach my $source_column (keys %missing_sources) {
1416 $source_values->{$source_column} = _get_single_item_column($source_column, $item->{'itemnumber'});
1418 $builder->($item, $source_values);
1423 =head2 _do_column_fixes_for_mod
1427 _do_column_fixes_for_mod($item);
1431 Given an item hashref containing one or more
1432 columns to modify, fix up certain values.
1433 Specifically, set to 0 any passed value
1434 of C<notforloan>, C<damaged>, C<itemlost>, or
1435 C<wthdrawn> that is either undefined or
1436 contains the empty string.
1440 sub _do_column_fixes_for_mod {
1443 if (exists $item->{'notforloan'} and
1444 (not defined $item->{'notforloan'} or $item->{'notforloan'} eq '')) {
1445 $item->{'notforloan'} = 0;
1447 if (exists $item->{'damaged'} and
1448 (not defined $item->{'damaged'} or $item->{'damaged'} eq '')) {
1449 $item->{'damaged'} = 0;
1451 if (exists $item->{'itemlost'} and
1452 (not defined $item->{'itemlost'} or $item->{'itemlost'} eq '')) {
1453 $item->{'itemlost'} = 0;
1455 if (exists $item->{'wthdrawn'} and
1456 (not defined $item->{'wthdrawn'} or $item->{'wthdrawn'} eq '')) {
1457 $item->{'wthdrawn'} = 0;
1461 =head2 _get_single_item_column
1465 _get_single_item_column($column, $itemnumber);
1469 Retrieves the value of a single column from an C<items>
1470 row specified by C<$itemnumber>.
1474 sub _get_single_item_column {
1476 my $itemnumber = shift;
1478 my $dbh = C4::Context->dbh;
1479 my $sth = $dbh->prepare("SELECT $column FROM items WHERE itemnumber = ?");
1480 $sth->execute($itemnumber);
1481 my ($value) = $sth->fetchrow();
1485 =head2 _calc_items_cn_sort
1489 _calc_items_cn_sort($item, $source_values);
1493 Helper routine to calculate C<items.cn_sort>.
1497 sub _calc_items_cn_sort {
1499 my $source_values = shift;
1501 $item->{'items.cn_sort'} = GetClassSort($source_values->{'items.cn_source'}, $source_values->{'itemcallnumber'}, "");
1504 =head2 _set_defaults_for_add
1508 _set_defaults_for_add($item_hash);
1512 Given an item hash representing an item to be added, set
1513 correct default values for columns whose default value
1514 is not handled by the DBMS. This includes the following
1521 C<items.dateaccessioned>
1543 sub _set_defaults_for_add {
1546 # if dateaccessioned is provided, use it. Otherwise, set to NOW()
1547 if (!(exists $item->{'dateaccessioned'}) ||
1548 ($item->{'dateaccessioned'} eq '')) {
1549 # FIXME add check for invalid date
1550 my $today = C4::Dates->new();
1551 $item->{'dateaccessioned'} = $today->output("iso"); #TODO: check time issues
1554 # various item status fields cannot be null
1555 $item->{'notforloan'} = 0 unless exists $item->{'notforloan'} and defined $item->{'notforloan'} and $item->{'notforloan'} ne '';
1556 $item->{'damaged'} = 0 unless exists $item->{'damaged'} and defined $item->{'damaged'} and $item->{'damaged'} ne '';
1557 $item->{'itemlost'} = 0 unless exists $item->{'itemlost'} and defined $item->{'itemlost'} and $item->{'itemlost'} ne '';
1558 $item->{'wthdrawn'} = 0 unless exists $item->{'wthdrawn'} and defined $item->{'wthdrawn'} and $item->{'wthdrawn'} ne '';
1561 =head2 _koha_new_item
1565 my ($itemnumber,$error) = _koha_new_item( $dbh, $item, $barcode );
1569 Perform the actual insert into the C<items> table.
1573 sub _koha_new_item {
1574 my ( $dbh, $item, $barcode ) = @_;
1578 "INSERT INTO items SET
1580 biblioitemnumber = ?,
1582 dateaccessioned = ?,
1586 replacementprice = ?,
1587 replacementpricedate = NOW(),
1588 datelastborrowed = ?,
1589 datelastseen = NOW(),
1612 my $sth = $dbh->prepare($query);
1614 $item->{'biblionumber'},
1615 $item->{'biblioitemnumber'},
1617 $item->{'dateaccessioned'},
1618 $item->{'booksellerid'},
1619 $item->{'homebranch'},
1621 $item->{'replacementprice'},
1622 $item->{datelastborrowed},
1624 $item->{'notforloan'},
1626 $item->{'itemlost'},
1627 $item->{'wthdrawn'},
1628 $item->{'itemcallnumber'},
1629 $item->{'restricted'},
1630 $item->{'itemnotes'},
1631 $item->{'holdingbranch'},
1633 $item->{'location'},
1636 $item->{'renewals'},
1637 $item->{'reserves'},
1638 $item->{'items.cn_source'},
1639 $item->{'items.cn_sort'},
1642 $item->{'materials'},
1645 my $itemnumber = $dbh->{'mysql_insertid'};
1646 if ( defined $sth->errstr ) {
1647 $error.="ERROR in _koha_new_item $query".$sth->errstr;
1650 return ( $itemnumber, $error );
1653 =head2 _koha_modify_item
1657 my ($itemnumber,$error) =_koha_modify_item( $dbh, $item, $op );
1661 Perform the actual update of the C<items> row. Note that this
1662 routine accepts a hashref specifying the columns to update.
1666 sub _koha_modify_item {
1667 my ( $dbh, $item ) = @_;
1670 my $query = "UPDATE items SET ";
1672 for my $key ( keys %$item ) {
1674 push @bind, $item->{$key};
1677 $query .= " WHERE itemnumber=?";
1678 push @bind, $item->{'itemnumber'};
1679 my $sth = $dbh->prepare($query);
1680 $sth->execute(@bind);
1681 if ( $dbh->errstr ) {
1682 $error.="ERROR in _koha_modify_item $query".$dbh->errstr;
1686 return ($item->{'itemnumber'},$error);
1689 =head2 _koha_delete_item
1693 _koha_delete_item( $dbh, $itemnum );
1697 Internal function to delete an item record from the koha tables
1701 sub _koha_delete_item {
1702 my ( $dbh, $itemnum ) = @_;
1704 # save the deleted item to deleteditems table
1705 my $sth = $dbh->prepare("SELECT * FROM items WHERE itemnumber=?");
1706 $sth->execute($itemnum);
1707 my $data = $sth->fetchrow_hashref();
1709 my $query = "INSERT INTO deleteditems SET ";
1711 foreach my $key ( keys %$data ) {
1712 $query .= "$key = ?,";
1713 push( @bind, $data->{$key} );
1716 $sth = $dbh->prepare($query);
1717 $sth->execute(@bind);
1720 # delete from items table
1721 $sth = $dbh->prepare("DELETE FROM items WHERE itemnumber=?");
1722 $sth->execute($itemnum);
1727 =head2 _marc_from_item_hash
1731 my $item_marc = _marc_from_item_hash($item, $frameworkcode);
1735 Given an item hash representing a complete item record,
1736 create a C<MARC::Record> object containing an embedded
1737 tag representing that item.
1741 sub _marc_from_item_hash {
1743 my $frameworkcode = shift;
1745 # Tack on 'items.' prefix to column names so lookup from MARC frameworks will work
1746 # Also, don't emit a subfield if the underlying field is blank.
1747 my $mungeditem = { map { (defined($item->{$_}) and $item->{$_} ne '') ?
1748 (/^items\./ ? ($_ => $item->{$_}) : ("items.$_" => $item->{$_}))
1749 : () } keys %{ $item } };
1751 my $item_marc = MARC::Record->new();
1752 foreach my $item_field (keys %{ $mungeditem }) {
1753 my ($tag, $subfield) = GetMarcFromKohaField($item_field, $frameworkcode);
1754 next unless defined $tag and defined $subfield; # skip if not mapped to MARC field
1755 if (my $field = $item_marc->field($tag)) {
1756 $field->add_subfields($subfield => $mungeditem->{$item_field});
1758 $item_marc->add_fields( $tag, " ", " ", $subfield => $mungeditem->{$item_field});
1765 =head2 _add_item_field_to_biblio
1769 _add_item_field_to_biblio($item_marc, $biblionumber, $frameworkcode);
1773 Adds the fields from a MARC record containing the
1774 representation of a Koha item record to the MARC
1775 biblio record. The input C<$item_marc> record
1776 is expect to contain just one field, the embedded
1777 item information field.
1781 sub _add_item_field_to_biblio {
1782 my ($item_marc, $biblionumber, $frameworkcode) = @_;
1784 my $biblio_marc = GetMarcBiblio($biblionumber);
1786 foreach my $field ($item_marc->fields()) {
1787 $biblio_marc->append_fields($field);
1790 ModBiblioMarc($biblio_marc, $biblionumber, $frameworkcode);
1793 =head2 _replace_item_field_in_biblio
1797 &_replace_item_field_in_biblio($item_marc, $biblionumber, $itemnumber, $frameworkcode)
1801 Given a MARC::Record C<$item_marc> containing one tag with the MARC
1802 representation of the item, examine the biblio MARC
1803 for the corresponding tag for that item and
1804 replace it with the tag from C<$item_marc>.
1808 sub _replace_item_field_in_biblio {
1809 my ($ItemRecord, $biblionumber, $itemnumber, $frameworkcode) = @_;
1810 my $dbh = C4::Context->dbh;
1812 # get complete MARC record & replace the item field by the new one
1813 my $completeRecord = GetMarcBiblio($biblionumber);
1814 my ($itemtag,$itemsubfield) = GetMarcFromKohaField("items.itemnumber",$frameworkcode);
1815 my $itemField = $ItemRecord->field($itemtag);
1816 my @items = $completeRecord->field($itemtag);
1819 if ($_->subfield($itemsubfield) eq $itemnumber) {
1820 $_->replace_with($itemField);
1826 # If we haven't found the matching field,
1827 # just add it. However, this means that
1828 # there is likely a bug.
1829 $completeRecord->append_fields($itemField);
1833 ModBiblioMarc($completeRecord, $biblionumber, $frameworkcode);
1836 =head2 _repack_item_errors
1838 Add an error message hash generated by C<CheckItemPreSave>
1839 to a list of errors.
1843 sub _repack_item_errors {
1844 my $item_sequence_num = shift;
1845 my $item_ref = shift;
1846 my $error_ref = shift;
1848 my @repacked_errors = ();
1850 foreach my $error_code (sort keys %{ $error_ref }) {
1851 my $repacked_error = {};
1852 $repacked_error->{'item_sequence'} = $item_sequence_num;
1853 $repacked_error->{'item_barcode'} = exists($item_ref->{'barcode'}) ? $item_ref->{'barcode'} : '';
1854 $repacked_error->{'error_code'} = $error_code;
1855 $repacked_error->{'error_information'} = $error_ref->{$error_code};
1856 push @repacked_errors, $repacked_error;
1859 return @repacked_errors;