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 );
59 GetItemsByBiblioitemnumber
62 GetItemnumberFromBarcode
67 C4::Items - item management functions
71 This module contains an API for manipulating item
72 records in Koha, and is used by cataloguing, circulation,
73 acquisitions, and serials management.
75 A Koha item record is stored in two places: the
76 items table and embedded in a MARC tag in the XML
77 version of the associated bib record in C<biblioitems.marcxml>.
78 This is done to allow the item information to be readily
79 indexed (e.g., by Zebra), but means that each item
80 modification transaction must keep the items table
81 and the MARC XML in sync at all times.
83 Consequently, all code that creates, modifies, or deletes
84 item records B<must> use an appropriate function from
85 C<C4::Items>. If no existing function is suitable, it is
86 better to add one to C<C4::Items> than to use add
87 one-off SQL statements to add or modify items.
89 The items table will be considered authoritative. In other
90 words, if there is ever a discrepancy between the items
91 table and the MARC XML, the items table should be considered
94 =head1 HISTORICAL NOTE
96 Most of the functions in C<C4::Items> were originally in
97 the C<C4::Biblio> module.
99 =head1 CORE EXPORTED FUNCTIONS
101 The following functions are meant for use by users
110 $item = GetItem($itemnumber,$barcode);
114 Return item information, for a given itemnumber or barcode.
115 The return value is a hashref mapping item column
121 my ($itemnumber,$barcode) = @_;
122 my $dbh = C4::Context->dbh;
124 my $sth = $dbh->prepare("
126 WHERE itemnumber = ?");
127 $sth->execute($itemnumber);
128 my $data = $sth->fetchrow_hashref;
131 my $sth = $dbh->prepare("
135 $sth->execute($barcode);
136 my $data = $sth->fetchrow_hashref;
141 =head2 AddItemFromMarc
145 my ($biblionumber, $biblioitemnumber, $itemnumber)
146 = AddItemFromMarc($source_item_marc, $biblionumber);
150 Given a MARC::Record object containing an embedded item
151 record and a biblionumber, create a new item record.
155 sub AddItemFromMarc {
156 my ( $source_item_marc, $biblionumber ) = @_;
157 my $dbh = C4::Context->dbh;
159 # parse item hash from MARC
160 my $frameworkcode = GetFrameworkCode( $biblionumber );
161 my $item = &TransformMarcToKoha( $dbh, $source_item_marc, $frameworkcode );
163 return AddItem($item, $biblionumber, $dbh, $frameworkcode);
170 my ($biblionumber, $biblioitemnumber, $itemnumber)
171 = AddItem($item, $biblionumber[, $dbh, $frameworkcode]);
175 Given a hash containing item column names as keys,
176 create a new Koha item record.
178 The two optional parameters (C<$dbh> and C<$frameworkcode>)
179 do not need to be supplied for general use; they exist
180 simply to allow them to be picked up from AddItemFromMarc.
186 my $biblionumber = shift;
188 my $dbh = @_ ? shift : C4::Context->dbh;
189 my $frameworkcode = @_ ? shift : GetFrameworkCode( $biblionumber );
191 # needs old biblionumber and biblioitemnumber
192 $item->{'biblionumber'} = $biblionumber;
193 my $sth = $dbh->prepare("SELECT biblioitemnumber FROM biblioitems WHERE biblionumber=?");
194 $sth->execute( $item->{'biblionumber'} );
195 ($item->{'biblioitemnumber'}) = $sth->fetchrow;
197 _set_defaults_for_add($item);
198 _set_derived_columns_for_add($item);
199 # FIXME - checks here
200 my ( $itemnumber, $error ) = _koha_new_item( $dbh, $item, $item->{barcode} );
201 $item->{'itemnumber'} = $itemnumber;
203 # create MARC tag representing item and add to bib
204 my $new_item_marc = _marc_from_item_hash($item, $frameworkcode);
205 _add_item_field_to_biblio($new_item_marc, $item->{'biblionumber'}, $frameworkcode );
207 logaction(C4::Context->userenv->{'number'},"CATALOGUING","ADD",$itemnumber,"item")
208 if C4::Context->preference("CataloguingLog");
210 return ($item->{biblionumber}, $item->{biblioitemnumber}, $itemnumber);
213 =head2 ModItemFromMarc
217 ModItemFromMarc($item_marc, $biblionumber, $itemnumber);
221 This function updates an item record based on a supplied
222 C<MARC::Record> object containing an embedded item field.
223 This API is meant for the use of C<additem.pl>; for
224 other purposes, C<ModItem> should be used.
228 sub ModItemFromMarc {
229 my $item_marc = shift;
230 my $biblionumber = shift;
231 my $itemnumber = shift;
233 my $dbh = C4::Context->dbh;
234 my $frameworkcode = GetFrameworkCode( $biblionumber );
235 my $item = &TransformMarcToKoha( $dbh, $item_marc, $frameworkcode );
237 return ModItem($item, $biblionumber, $itemnumber, $dbh, $frameworkcode);
244 ModItem({ column => $newvalue }, $biblionumber, $itemnumber);
248 Change one or more columns in an item record and update
249 the MARC representation of the item.
251 The first argument is a hashref mapping from item column
252 names to the new values. The second and third arguments
253 are the biblionumber and itemnumber, respectively.
255 If one of the changed columns is used to calculate
256 the derived value of a column such as C<items.cn_sort>,
257 this routine will perform the necessary calculation
264 my $biblionumber = shift;
265 my $itemnumber = shift;
267 # if $biblionumber is undefined, get it from the current item
268 unless (defined $biblionumber) {
269 $biblionumber = _get_single_item_column('biblionumber', $itemnumber);
272 my $dbh = @_ ? shift : C4::Context->dbh;
273 my $frameworkcode = @_ ? shift : GetFrameworkCode( $biblionumber );
275 $item->{'itemnumber'} = $itemnumber;
276 _set_derived_columns_for_mod($item);
277 _do_column_fixes_for_mod($item);
280 # attempt to change itemnumber
281 # attempt to change biblionumber (if we want
282 # an API to relink an item to a different bib,
283 # it should be a separate function)
286 _koha_modify_item($dbh, $item);
288 # update biblio MARC XML
289 my $whole_item = GetItem($itemnumber);
290 my $new_item_marc = _marc_from_item_hash($whole_item, $frameworkcode);
291 _replace_item_field_in_biblio($new_item_marc, $biblionumber, $itemnumber, $frameworkcode);
293 logaction(C4::Context->userenv->{'number'},"CATALOGUING","MODIFY",$itemnumber,$new_item_marc->as_formatted)
294 if C4::Context->preference("CataloguingLog");
297 =head2 ModItemTransfer
301 ModItemTransfer($itenumber, $frombranch, $tobranch);
305 Marks an item as being transferred from one branch
310 sub ModItemTransfer {
311 my ( $itemnumber, $frombranch, $tobranch ) = @_;
313 my $dbh = C4::Context->dbh;
315 #new entry in branchtransfers....
316 my $sth = $dbh->prepare(
317 "INSERT INTO branchtransfers (itemnumber, frombranch, datesent, tobranch)
318 VALUES (?, ?, NOW(), ?)");
319 $sth->execute($itemnumber, $frombranch, $tobranch);
321 ModItem({ holdingbranch => $tobranch }, undef, $itemnumber);
322 ModDateLastSeen($itemnumber);
326 =head2 ModDateLastSeen
330 ModDateLastSeen($itemnum);
334 Mark item as seen. Is called when an item is issued, returned or manually marked during inventory/stocktaking.
335 C<$itemnum> is the item number
339 sub ModDateLastSeen {
340 my ($itemnumber) = @_;
342 my $today = C4::Dates->new();
343 ModItem({ itemlost => 0, datelastseen => $today->output("iso") }, undef, $itemnumber);
350 DelItem($biblionumber, $itemnumber);
354 Exported function (core API) for deleting an item record in Koha.
359 my ( $dbh, $biblionumber, $itemnumber ) = @_;
361 # FIXME check the item has no current issues
363 _koha_delete_item( $dbh, $itemnumber );
365 # get the MARC record
366 my $record = GetMarcBiblio($biblionumber);
367 my $frameworkcode = GetFrameworkCode($biblionumber);
370 my $copy2deleted = $dbh->prepare("UPDATE deleteditems SET marc=? WHERE itemnumber=?");
371 $copy2deleted->execute( $record->as_usmarc(), $itemnumber );
373 #search item field code
374 my ( $itemtag, $itemsubfield ) = GetMarcFromKohaField("items.itemnumber",$frameworkcode);
375 my @fields = $record->field($itemtag);
377 # delete the item specified
378 foreach my $field (@fields) {
379 if ( $field->subfield($itemsubfield) eq $itemnumber ) {
380 $record->delete_field($field);
383 &ModBiblioMarc( $record, $biblionumber, $frameworkcode );
384 &logaction(C4::Context->userenv->{'number'},"CATALOGUING","DELETE",$itemnumber,"item")
385 if C4::Context->preference("CataloguingLog");
388 =head2 CheckItemPreSave
392 my $item_ref = TransformMarcToKoha($marc, 'items');
394 my %errors = CheckItemPreSave($item_ref);
395 if (exists $errors{'duplicate_barcode'}) {
396 print "item has duplicate barcode: ", $errors{'duplicate_barcode'}, "\n";
397 } elsif (exists $errors{'invalid_homebranch'}) {
398 print "item has invalid home branch: ", $errors{'invalid_homebranch'}, "\n";
399 } elsif (exists $errors{'invalid_holdingbranch'}) {
400 print "item has invalid holding branch: ", $errors{'invalid_holdingbranch'}, "\n";
407 Given a hashref containing item fields, determine if it can be
408 inserted or updated in the database. Specifically, checks for
409 database integrity issues, and returns a hash containing any
410 of the following keys, if applicable.
414 =item duplicate_barcode
416 Barcode, if it duplicates one already found in the database.
418 =item invalid_homebranch
420 Home branch, if not defined in branches table.
422 =item invalid_holdingbranch
424 Holding branch, if not defined in branches table.
428 This function does NOT implement any policy-related checks,
429 e.g., whether current operator is allowed to save an
430 item that has a given branch code.
434 sub CheckItemPreSave {
435 my $item_ref = shift;
439 # check for duplicate barcode
440 if (exists $item_ref->{'barcode'} and defined $item_ref->{'barcode'}) {
441 my $existing_itemnumber = GetItemnumberFromBarcode($item_ref->{'barcode'});
442 if ($existing_itemnumber) {
443 if (!exists $item_ref->{'itemnumber'} # new item
444 or $item_ref->{'itemnumber'} != $existing_itemnumber) { # existing item
445 $errors{'duplicate_barcode'} = $item_ref->{'barcode'};
450 # check for valid home branch
451 if (exists $item_ref->{'homebranch'} and defined $item_ref->{'homebranch'}) {
452 my $branch_name = GetBranchName($item_ref->{'homebranch'});
453 unless (defined $branch_name) {
454 # relies on fact that branches.branchname is a non-NULL column,
455 # so GetBranchName returns undef only if branch does not exist
456 $errors{'invalid_homebranch'} = $item_ref->{'homebranch'};
460 # check for valid holding branch
461 if (exists $item_ref->{'holdingbranch'} and defined $item_ref->{'holdingbranch'}) {
462 my $branch_name = GetBranchName($item_ref->{'holdingbranch'});
463 unless (defined $branch_name) {
464 # relies on fact that branches.branchname is a non-NULL column,
465 # so GetBranchName returns undef only if branch does not exist
466 $errors{'invalid_holdingbranch'} = $item_ref->{'holdingbranch'};
474 =head1 EXPORTED SPECIAL ACCESSOR FUNCTIONS
476 The following functions provide various ways of
477 getting an item record, a set of item records, or
478 lists of authorized values for certain item fields.
480 Some of the functions in this group are candidates
481 for refactoring -- for example, some of the code
482 in C<GetItemsByBiblioitemnumber> and C<GetItemsInfo>
483 has copy-and-paste work.
491 $itemstatushash = GetItemStatus($fwkcode);
495 Returns a list of valid values for the
496 C<items.notforloan> field.
498 NOTE: does B<not> return an individual item's
501 Can be MARC dependant.
503 But basically could be can be loan or not
504 Create a status selector with the following code
506 =head3 in PERL SCRIPT
510 my $itemstatushash = getitemstatus;
512 foreach my $thisstatus (keys %$itemstatushash) {
513 my %row =(value => $thisstatus,
514 statusname => $itemstatushash->{$thisstatus}->{'statusname'},
516 push @itemstatusloop, \%row;
518 $template->param(statusloop=>\@itemstatusloop);
526 <select name="statusloop">
527 <option value="">Default</option>
528 <!-- TMPL_LOOP name="statusloop" -->
529 <option value="<!-- TMPL_VAR name="value" -->" <!-- TMPL_IF name="selected" -->selected<!-- /TMPL_IF -->><!-- TMPL_VAR name="statusname" --></option>
539 # returns a reference to a hash of references to status...
542 my $dbh = C4::Context->dbh;
544 $fwk = '' unless ($fwk);
545 my ( $tag, $subfield ) =
546 GetMarcFromKohaField( "items.notforloan", $fwk );
547 if ( $tag and $subfield ) {
550 "SELECT authorised_value
551 FROM marc_subfield_structure
557 $sth->execute( $tag, $subfield, $fwk );
558 if ( my ($authorisedvaluecat) = $sth->fetchrow ) {
561 "SELECT authorised_value,lib
562 FROM authorised_values
567 $authvalsth->execute($authorisedvaluecat);
568 while ( my ( $authorisedvalue, $lib ) = $authvalsth->fetchrow ) {
569 $itemstatus{$authorisedvalue} = $lib;
585 $itemstatus{"1"} = "Not For Loan";
589 =head2 GetItemLocation
593 $itemlochash = GetItemLocation($fwk);
597 Returns a list of valid values for the
598 C<items.location> field.
600 NOTE: does B<not> return an individual item's
603 where fwk stands for an optional framework code.
604 Create a location selector with the following code
606 =head3 in PERL SCRIPT
610 my $itemlochash = getitemlocation;
612 foreach my $thisloc (keys %$itemlochash) {
613 my $selected = 1 if $thisbranch eq $branch;
614 my %row =(locval => $thisloc,
615 selected => $selected,
616 locname => $itemlochash->{$thisloc},
618 push @itemlocloop, \%row;
620 $template->param(itemlocationloop => \@itemlocloop);
628 <select name="location">
629 <option value="">Default</option>
630 <!-- TMPL_LOOP name="itemlocationloop" -->
631 <option value="<!-- TMPL_VAR name="locval" -->" <!-- TMPL_IF name="selected" -->selected<!-- /TMPL_IF -->><!-- TMPL_VAR name="locname" --></option>
639 sub GetItemLocation {
641 # returns a reference to a hash of references to location...
644 my $dbh = C4::Context->dbh;
646 $fwk = '' unless ($fwk);
647 my ( $tag, $subfield ) =
648 GetMarcFromKohaField( "items.location", $fwk );
649 if ( $tag and $subfield ) {
652 "SELECT authorised_value
653 FROM marc_subfield_structure
658 $sth->execute( $tag, $subfield, $fwk );
659 if ( my ($authorisedvaluecat) = $sth->fetchrow ) {
662 "SELECT authorised_value,lib
663 FROM authorised_values
667 $authvalsth->execute($authorisedvaluecat);
668 while ( my ( $authorisedvalue, $lib ) = $authvalsth->fetchrow ) {
669 $itemlocation{$authorisedvalue} = $lib;
672 return \%itemlocation;
685 $itemlocation{"1"} = "Not For Loan";
686 return \%itemlocation;
693 $items = GetLostItems($where,$orderby);
697 This function get the items lost into C<$items>.
702 C<$where> is a hashref. it containts a field of the items table as key
703 and the value to match as value.
704 C<$orderby> is a field of the items table.
707 C<$items> is a reference to an array full of hasref which keys are items' table column.
709 =item usage in the perl script:
712 $where{barcode} = 0001548;
713 my $items = GetLostItems( \%where, "homebranch" );
714 $template->param(itemsloop => $items);
721 # Getting input args.
724 my $dbh = C4::Context->dbh;
729 WHERE itemlost IS NOT NULL
732 foreach my $key (keys %$where) {
733 $query .= " AND " . $key . " LIKE '%" . $where->{$key} . "%'";
735 $query .= " ORDER BY ".$orderby if defined $orderby;
737 my $sth = $dbh->prepare($query);
740 while ( my $row = $sth->fetchrow_hashref ){
746 =head2 GetItemsForInventory
750 $itemlist = GetItemsForInventory($minlocation,$maxlocation,$datelastseen,$offset,$size)
754 Retrieve a list of title/authors/barcode/callnumber, for biblio inventory.
756 The sub returns a list of hashes, containing itemnumber, author, title, barcode & item callnumber.
757 It is ordered by callnumber,title.
759 The minlocation & maxlocation parameters are used to specify a range of item callnumbers
760 the datelastseen can be used to specify that you want to see items not seen since a past date only.
761 offset & size can be used to retrieve only a part of the whole listing (defaut behaviour)
765 sub GetItemsForInventory {
766 my ( $minlocation, $maxlocation,$location, $datelastseen, $branch, $offset, $size ) = @_;
767 my $dbh = C4::Context->dbh;
770 $datelastseen=format_date_in_iso($datelastseen);
772 "SELECT itemnumber,barcode,itemcallnumber,title,author,biblio.biblionumber,datelastseen
774 LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber
775 WHERE itemcallnumber>= ?
776 AND itemcallnumber <=?
777 AND (datelastseen< ? OR datelastseen IS NULL)";
778 $query.= " AND items.location=".$dbh->quote($location) if $location;
779 $query.= " AND items.homebranch=".$dbh->quote($branch) if $branch;
780 $query .= " ORDER BY itemcallnumber,title";
781 $sth = $dbh->prepare($query);
782 $sth->execute( $minlocation, $maxlocation, $datelastseen );
786 SELECT itemnumber,barcode,itemcallnumber,biblio.biblionumber,title,author,datelastseen
788 LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber
789 WHERE itemcallnumber>= ?
790 AND itemcallnumber <=?";
791 $query.= " AND items.location=".$dbh->quote($location) if $location;
792 $query.= " AND items.homebranch=".$dbh->quote($branch) if $branch;
793 $query .= " ORDER BY itemcallnumber,title";
794 $sth = $dbh->prepare($query);
795 $sth->execute( $minlocation, $maxlocation );
798 while ( my $row = $sth->fetchrow_hashref ) {
799 $offset-- if ($offset);
800 $row->{datelastseen}=format_date($row->{datelastseen});
801 if ( ( !$offset ) && $size ) {
812 $count = &GetItemsCount( $biblionumber);
816 This function return count of item with $biblionumber
821 my ( $biblionumber ) = @_;
822 my $dbh = C4::Context->dbh;
823 my $query = "SELECT count(*)
825 WHERE biblionumber=?";
826 my $sth = $dbh->prepare($query);
827 $sth->execute($biblionumber);
828 my $count = $sth->fetchrow;
833 =head2 GetItemInfosOf
837 GetItemInfosOf(@itemnumbers);
844 my @itemnumbers = @_;
849 WHERE itemnumber IN (' . join( ',', @itemnumbers ) . ')
851 return get_infos_of( $query, 'itemnumber' );
854 =head2 GetItemsByBiblioitemnumber
858 GetItemsByBiblioitemnumber($biblioitemnumber);
862 Returns an arrayref of hashrefs suitable for use in a TMPL_LOOP
863 Called by C<C4::XISBN>
867 sub GetItemsByBiblioitemnumber {
868 my ( $bibitem ) = @_;
869 my $dbh = C4::Context->dbh;
870 my $sth = $dbh->prepare("SELECT * FROM items WHERE items.biblioitemnumber = ?") || die $dbh->errstr;
871 # Get all items attached to a biblioitem
874 $sth->execute($bibitem) || die $sth->errstr;
875 while ( my $data = $sth->fetchrow_hashref ) {
876 # Foreach item, get circulation information
877 my $sth2 = $dbh->prepare( "SELECT * FROM issues,borrowers
879 AND returndate is NULL
880 AND issues.borrowernumber = borrowers.borrowernumber"
882 $sth2->execute( $data->{'itemnumber'} );
883 if ( my $data2 = $sth2->fetchrow_hashref ) {
884 # if item is out, set the due date and who it is out too
885 $data->{'date_due'} = $data2->{'date_due'};
886 $data->{'cardnumber'} = $data2->{'cardnumber'};
887 $data->{'borrowernumber'} = $data2->{'borrowernumber'};
890 # set date_due to blank, so in the template we check itemlost, and wthdrawn
891 $data->{'date_due'} = '';
894 # Find the last 3 people who borrowed this item.
895 my $query2 = "SELECT * FROM issues, borrowers WHERE itemnumber = ?
896 AND issues.borrowernumber = borrowers.borrowernumber
897 AND returndate is not NULL
898 ORDER BY returndate desc,timestamp desc LIMIT 3";
899 $sth2 = $dbh->prepare($query2) || die $dbh->errstr;
900 $sth2->execute( $data->{'itemnumber'} ) || die $sth2->errstr;
902 while ( my $data2 = $sth2->fetchrow_hashref ) {
903 $data->{"timestamp$i2"} = $data2->{'timestamp'};
904 $data->{"card$i2"} = $data2->{'cardnumber'};
905 $data->{"borrower$i2"} = $data2->{'borrowernumber'};
909 push(@results,$data);
919 @results = GetItemsInfo($biblionumber, $type);
923 Returns information about books with the given biblionumber.
925 C<$type> may be either C<intra> or anything else. If it is not set to
926 C<intra>, then the search will exclude lost, very overdue, and
929 C<GetItemsInfo> returns a list of references-to-hash. Each element
930 contains a number of keys. Most of them are table items from the
931 C<biblio>, C<biblioitems>, C<items>, and C<itemtypes> tables in the
932 Koha database. Other keys include:
936 =item C<$data-E<gt>{branchname}>
938 The name (not the code) of the branch to which the book belongs.
940 =item C<$data-E<gt>{datelastseen}>
942 This is simply C<items.datelastseen>, except that while the date is
943 stored in YYYY-MM-DD format in the database, here it is converted to
944 DD/MM/YYYY format. A NULL date is returned as C<//>.
946 =item C<$data-E<gt>{datedue}>
948 =item C<$data-E<gt>{class}>
950 This is the concatenation of C<biblioitems.classification>, the book's
951 Dewey code, and C<biblioitems.subclass>.
953 =item C<$data-E<gt>{ocount}>
955 I think this is the number of copies of the book available.
957 =item C<$data-E<gt>{order}>
959 If this is set, it is set to C<One Order>.
966 my ( $biblionumber, $type ) = @_;
967 my $dbh = C4::Context->dbh;
968 my $query = "SELECT *,items.notforloan as itemnotforloan
970 LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber
971 LEFT JOIN biblioitems ON biblioitems.biblioitemnumber = items.biblioitemnumber";
972 $query .= (C4::Context->preference('item-level_itypes')) ?
973 " LEFT JOIN itemtypes on items.itype = itemtypes.itemtype "
974 : " LEFT JOIN itemtypes on biblioitems.itemtype = itemtypes.itemtype ";
975 $query .= "WHERE items.biblionumber = ? ORDER BY items.dateaccessioned desc" ;
976 my $sth = $dbh->prepare($query);
977 $sth->execute($biblionumber);
980 my ( $date_due, $count_reserves );
982 my $isth = $dbh->prepare(
983 "SELECT issues.*,borrowers.cardnumber,borrowers.surname,borrowers.firstname,borrowers.branchcode as bcode
984 FROM issues LEFT JOIN borrowers ON issues.borrowernumber=borrowers.borrowernumber
986 AND returndate IS NULL"
988 while ( my $data = $sth->fetchrow_hashref ) {
990 $isth->execute( $data->{'itemnumber'} );
991 if ( my $idata = $isth->fetchrow_hashref ) {
992 $data->{borrowernumber} = $idata->{borrowernumber};
993 $data->{cardnumber} = $idata->{cardnumber};
994 $data->{surname} = $idata->{surname};
995 $data->{firstname} = $idata->{firstname};
996 $datedue = $idata->{'date_due'};
997 if (C4::Context->preference("IndependantBranches")){
998 my $userenv = C4::Context->userenv;
999 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
1000 $data->{'NOTSAMEBRANCH'} = 1 if ($idata->{'bcode'} ne $userenv->{branch});
1004 if ( $datedue eq '' ) {
1005 my ( $restype, $reserves ) =
1006 C4::Reserves::CheckReserves( $data->{'itemnumber'} );
1008 $count_reserves = $restype;
1013 #get branch information.....
1014 my $bsth = $dbh->prepare(
1015 "SELECT * FROM branches WHERE branchcode = ?
1018 $bsth->execute( $data->{'holdingbranch'} );
1019 if ( my $bdata = $bsth->fetchrow_hashref ) {
1020 $data->{'branchname'} = $bdata->{'branchname'};
1022 $data->{'datedue'} = $datedue;
1023 $data->{'count_reserves'} = $count_reserves;
1025 # get notforloan complete status if applicable
1026 my $sthnflstatus = $dbh->prepare(
1027 'SELECT authorised_value
1028 FROM marc_subfield_structure
1029 WHERE kohafield="items.notforloan"
1033 $sthnflstatus->execute;
1034 my ($authorised_valuecode) = $sthnflstatus->fetchrow;
1035 if ($authorised_valuecode) {
1036 $sthnflstatus = $dbh->prepare(
1037 "SELECT lib FROM authorised_values
1039 AND authorised_value=?"
1041 $sthnflstatus->execute( $authorised_valuecode,
1042 $data->{itemnotforloan} );
1043 my ($lib) = $sthnflstatus->fetchrow;
1044 $data->{notforloan} = $lib;
1047 # my stack procedures
1048 my $stackstatus = $dbh->prepare(
1049 'SELECT authorised_value
1050 FROM marc_subfield_structure
1051 WHERE kohafield="items.stack"
1054 $stackstatus->execute;
1056 ($authorised_valuecode) = $stackstatus->fetchrow;
1057 if ($authorised_valuecode) {
1058 $stackstatus = $dbh->prepare(
1060 FROM authorised_values
1062 AND authorised_value=?
1065 $stackstatus->execute( $authorised_valuecode, $data->{stack} );
1066 my ($lib) = $stackstatus->fetchrow;
1067 $data->{stack} = $lib;
1069 # Find the last 3 people who borrowed this item.
1070 my $sth2 = $dbh->prepare("SELECT * FROM issues,borrowers
1071 WHERE itemnumber = ?
1072 AND issues.borrowernumber = borrowers.borrowernumber
1073 AND returndate IS NOT NULL LIMIT 3");
1074 $sth2->execute($data->{'itemnumber'});
1076 while (my $data2 = $sth2->fetchrow_hashref()) {
1077 $data->{"timestamp$ii"} = $data2->{'timestamp'} if $data2->{'timestamp'};
1078 $data->{"card$ii"} = $data2->{'cardnumber'} if $data2->{'cardnumber'};
1079 $data->{"borrower$ii"} = $data2->{'borrowernumber'} if $data2->{'borrowernumber'};
1083 $results[$i] = $data;
1091 =head2 get_itemnumbers_of
1095 my @itemnumbers_of = get_itemnumbers_of(@biblionumbers);
1099 Given a list of biblionumbers, return the list of corresponding itemnumbers
1100 for each biblionumber.
1102 Return a reference on a hash where keys are biblionumbers and values are
1103 references on array of itemnumbers.
1107 sub get_itemnumbers_of {
1108 my @biblionumbers = @_;
1110 my $dbh = C4::Context->dbh;
1116 WHERE biblionumber IN (?' . ( ',?' x scalar @biblionumbers - 1 ) . ')
1118 my $sth = $dbh->prepare($query);
1119 $sth->execute(@biblionumbers);
1123 while ( my ( $itemnumber, $biblionumber ) = $sth->fetchrow_array ) {
1124 push @{ $itemnumbers_of{$biblionumber} }, $itemnumber;
1127 return \%itemnumbers_of;
1130 =head2 GetItemnumberFromBarcode
1134 $result = GetItemnumberFromBarcode($barcode);
1140 sub GetItemnumberFromBarcode {
1142 my $dbh = C4::Context->dbh;
1145 $dbh->prepare("SELECT itemnumber FROM items WHERE items.barcode=?");
1146 $rq->execute($barcode);
1147 my ($result) = $rq->fetchrow;
1151 =head1 LIMITED USE FUNCTIONS
1153 The following functions, while part of the public API,
1154 are not exported. This is generally because they are
1155 meant to be used by only one script for a specific
1156 purpose, and should not be used in any other context
1157 without careful thought.
1165 my $item_marc = GetMarcItem($biblionumber, $itemnumber);
1169 Returns MARC::Record of the item passed in parameter.
1170 This function is meant for use only in C<cataloguing/additem.pl>,
1171 where it is needed to support that script's MARC-like
1177 my ( $biblionumber, $itemnumber ) = @_;
1179 # GetMarcItem has been revised so that it does the following:
1180 # 1. Gets the item information from the items table.
1181 # 2. Converts it to a MARC field for storage in the bib record.
1183 # The previous behavior was:
1184 # 1. Get the bib record.
1185 # 2. Return the MARC tag corresponding to the item record.
1187 # The difference is that one treats the items row as authoritative,
1188 # while the other treats the MARC representation as authoritative
1189 # under certain circumstances.
1191 my $itemrecord = GetItem($itemnumber);
1193 # Tack on 'items.' prefix to column names so that TransformKohaToMarc will work.
1194 # Also, don't emit a subfield if the underlying field is blank.
1195 my $mungeditem = { map { $itemrecord->{$_} ne '' ? ("items.$_" => $itemrecord->{$_}) : () } keys %{ $itemrecord } };
1197 my $itemmarc = TransformKohaToMarc($mungeditem);
1202 =head1 PRIVATE FUNCTIONS AND VARIABLES
1204 The following functions are not meant to be called
1205 directly, but are documented in order to explain
1206 the inner workings of C<C4::Items>.
1210 =head2 %derived_columns
1212 This hash keeps track of item columns that
1213 are strictly derived from other columns in
1214 the item record and are not meant to be set
1217 Each key in the hash should be the name of a
1218 column (as named by TransformMarcToKoha). Each
1219 value should be hashref whose keys are the
1220 columns on which the derived column depends. The
1221 hashref should also contain a 'BUILDER' key
1222 that is a reference to a sub that calculates
1227 my %derived_columns = (
1228 'items.cn_sort' => {
1229 'itemcallnumber' => 1,
1230 'items.cn_source' => 1,
1231 'BUILDER' => \&_calc_items_cn_sort,
1235 =head2 _set_derived_columns_for_add
1239 _set_derived_column_for_add($item);
1243 Given an item hash representing a new item to be added,
1244 calculate any derived columns. Currently the only
1245 such column is C<items.cn_sort>.
1249 sub _set_derived_columns_for_add {
1252 foreach my $column (keys %derived_columns) {
1253 my $builder = $derived_columns{$column}->{'BUILDER'};
1254 my $source_values = {};
1255 foreach my $source_column (keys %{ $derived_columns{$column} }) {
1256 next if $source_column eq 'BUILDER';
1257 $source_values->{$source_column} = $item->{$source_column};
1259 $builder->($item, $source_values);
1263 =head2 _set_derived_columns_for_mod
1267 _set_derived_column_for_mod($item);
1271 Given an item hash representing a new item to be modified.
1272 calculate any derived columns. Currently the only
1273 such column is C<items.cn_sort>.
1275 This routine differs from C<_set_derived_columns_for_add>
1276 in that it needs to handle partial item records. In other
1277 words, the caller of C<ModItem> may have supplied only one
1278 or two columns to be changed, so this function needs to
1279 determine whether any of the columns to be changed affect
1280 any of the derived columns. Also, if a derived column
1281 depends on more than one column, but the caller is not
1282 changing all of then, this routine retrieves the unchanged
1283 values from the database in order to ensure a correct
1288 sub _set_derived_columns_for_mod {
1291 foreach my $column (keys %derived_columns) {
1292 my $builder = $derived_columns{$column}->{'BUILDER'};
1293 my $source_values = {};
1294 my %missing_sources = ();
1295 my $must_recalc = 0;
1296 foreach my $source_column (keys %{ $derived_columns{$column} }) {
1297 next if $source_column eq 'BUILDER';
1298 if (exists $item->{$source_column}) {
1300 $source_values->{$source_column} = $item->{$source_column};
1302 $missing_sources{$source_column} = 1;
1306 foreach my $source_column (keys %missing_sources) {
1307 $source_values->{$source_column} = _get_single_item_column($source_column, $item->{'itemnumber'});
1309 $builder->($item, $source_values);
1314 =head2 _do_column_fixes_for_mod
1318 _do_column_fixes_for_mod($item);
1322 Given an item hashref containing one or more
1323 columns to modify, fix up certain values.
1324 Specifically, set to 0 any passed value
1325 of C<notforloan>, C<damaged>, C<itemlost>, or
1326 C<wthdrawn> that is either undefined or
1327 contains the empty string.
1331 sub _do_column_fixes_for_mod {
1334 if (exists $item->{'notforloan'} and
1335 (not defined $item->{'notforloan'} or $item->{'notforloan'} eq '')) {
1336 $item->{'notforloan'} = 0;
1338 if (exists $item->{'damaged'} and
1339 (not defined $item->{'damaged'} or $item->{'damaged'} eq '')) {
1340 $item->{'damaged'} = 0;
1342 if (exists $item->{'itemlost'} and
1343 (not defined $item->{'itemlost'} or $item->{'itemlost'} eq '')) {
1344 $item->{'itemlost'} = 0;
1346 if (exists $item->{'wthdrawn'} and
1347 (not defined $item->{'wthdrawn'} or $item->{'wthdrawn'} eq '')) {
1348 $item->{'wthdrawn'} = 0;
1352 =head2 _get_single_item_column
1356 _get_single_item_column($column, $itemnumber);
1360 Retrieves the value of a single column from an C<items>
1361 row specified by C<$itemnumber>.
1365 sub _get_single_item_column {
1367 my $itemnumber = shift;
1369 my $dbh = C4::Context->dbh;
1370 my $sth = $dbh->prepare("SELECT $column FROM items WHERE itemnumber = ?");
1371 $sth->execute($itemnumber);
1372 my ($value) = $sth->fetchrow();
1376 =head2 _calc_items_cn_sort
1380 _calc_items_cn_sort($item, $source_values);
1384 Helper routine to calculate C<items.cn_sort>.
1388 sub _calc_items_cn_sort {
1390 my $source_values = shift;
1392 $item->{'items.cn_sort'} = GetClassSort($source_values->{'items.cn_source'}, $source_values->{'itemcallnumber'}, "");
1395 =head2 _set_defaults_for_add
1399 _set_defaults_for_add($item_hash);
1403 Given an item hash representing an item to be added, set
1404 correct default values for columns whose default value
1405 is not handled by the DBMS. This includes the following
1412 C<items.dateaccessioned>
1434 sub _set_defaults_for_add {
1437 # if dateaccessioned is provided, use it. Otherwise, set to NOW()
1438 if (!(exists $item->{'dateaccessioned'}) ||
1439 ($item->{'dateaccessioned'} eq '')) {
1440 # FIXME add check for invalid date
1441 my $today = C4::Dates->new();
1442 $item->{'dateaccessioned'} = $today->output("iso"); #TODO: check time issues
1445 # various item status fields cannot be null
1446 $item->{'notforloan'} = 0 unless exists $item->{'notforloan'} and defined $item->{'notforloan'};
1447 $item->{'damaged'} = 0 unless exists $item->{'damaged'} and defined $item->{'damaged'};
1448 $item->{'itemlost'} = 0 unless exists $item->{'itemlost'} and defined $item->{'itemlost'};
1449 $item->{'wthdrawn'} = 0 unless exists $item->{'wthdrawn'} and defined $item->{'wthdrawn'};
1452 =head2 _koha_new_item
1456 my ($itemnumber,$error) = _koha_new_item( $dbh, $item, $barcode );
1460 Perform the actual insert into the C<items> table.
1464 sub _koha_new_item {
1465 my ( $dbh, $item, $barcode ) = @_;
1469 "INSERT INTO items SET
1471 biblioitemnumber = ?,
1473 dateaccessioned = ?,
1477 replacementprice = ?,
1478 replacementpricedate = NOW(),
1479 datelastborrowed = ?,
1480 datelastseen = NOW(),
1503 my $sth = $dbh->prepare($query);
1505 $item->{'biblionumber'},
1506 $item->{'biblioitemnumber'},
1508 $item->{'dateaccessioned'},
1509 $item->{'booksellerid'},
1510 $item->{'homebranch'},
1512 $item->{'replacementprice'},
1513 $item->{datelastborrowed},
1515 $item->{'notforloan'},
1517 $item->{'itemlost'},
1518 $item->{'wthdrawn'},
1519 $item->{'itemcallnumber'},
1520 $item->{'restricted'},
1521 $item->{'itemnotes'},
1522 $item->{'holdingbranch'},
1524 $item->{'location'},
1527 $item->{'renewals'},
1528 $item->{'reserves'},
1529 $item->{'items.cn_source'},
1530 $item->{'items.cn_sort'},
1533 $item->{'materials'},
1536 my $itemnumber = $dbh->{'mysql_insertid'};
1537 if ( defined $sth->errstr ) {
1538 $error.="ERROR in _koha_new_item $query".$sth->errstr;
1541 return ( $itemnumber, $error );
1544 =head2 _koha_modify_item
1548 my ($itemnumber,$error) =_koha_modify_item( $dbh, $item, $op );
1552 Perform the actual update of the C<items> row. Note that this
1553 routine accepts a hashref specifying the columns to update.
1557 sub _koha_modify_item {
1558 my ( $dbh, $item ) = @_;
1561 my $query = "UPDATE items SET ";
1563 for my $key ( keys %$item ) {
1565 push @bind, $item->{$key};
1568 $query .= " WHERE itemnumber=?";
1569 push @bind, $item->{'itemnumber'};
1570 my $sth = $dbh->prepare($query);
1571 $sth->execute(@bind);
1572 if ( $dbh->errstr ) {
1573 $error.="ERROR in _koha_modify_item $query".$dbh->errstr;
1577 return ($item->{'itemnumber'},$error);
1580 =head2 _koha_delete_item
1584 _koha_delete_item( $dbh, $itemnum );
1588 Internal function to delete an item record from the koha tables
1592 sub _koha_delete_item {
1593 my ( $dbh, $itemnum ) = @_;
1595 # save the deleted item to deleteditems table
1596 my $sth = $dbh->prepare("SELECT * FROM items WHERE itemnumber=?");
1597 $sth->execute($itemnum);
1598 my $data = $sth->fetchrow_hashref();
1600 my $query = "INSERT INTO deleteditems SET ";
1602 foreach my $key ( keys %$data ) {
1603 $query .= "$key = ?,";
1604 push( @bind, $data->{$key} );
1607 $sth = $dbh->prepare($query);
1608 $sth->execute(@bind);
1611 # delete from items table
1612 $sth = $dbh->prepare("DELETE FROM items WHERE itemnumber=?");
1613 $sth->execute($itemnum);
1618 =head2 _marc_from_item_hash
1622 my $item_marc = _marc_from_item_hash($item, $frameworkcode);
1626 Given an item hash representing a complete item record,
1627 create a C<MARC::Record> object containing an embedded
1628 tag representing that item.
1632 sub _marc_from_item_hash {
1634 my $frameworkcode = shift;
1636 # Tack on 'items.' prefix to column names so lookup from MARC frameworks will work
1637 # Also, don't emit a subfield if the underlying field is blank.
1638 my $mungeditem = { map { (defined($item->{$_}) and $item->{$_} ne '') ?
1639 (/^items\./ ? ($_ => $item->{$_}) : ("items.$_" => $item->{$_}))
1640 : () } keys %{ $item } };
1642 my $item_marc = MARC::Record->new();
1643 foreach my $item_field (keys %{ $mungeditem }) {
1644 my ($tag, $subfield) = GetMarcFromKohaField($item_field, $frameworkcode);
1645 next unless defined $tag and defined $subfield; # skip if not mapped to MARC field
1646 if (my $field = $item_marc->field($tag)) {
1647 $field->add_subfields($subfield => $mungeditem->{$item_field});
1649 $item_marc->add_fields( $tag, " ", " ", $subfield => $mungeditem->{$item_field});
1656 =head2 _add_item_field_to_biblio
1660 _add_item_field_to_biblio($item_marc, $biblionumber, $frameworkcode);
1664 Adds the fields from a MARC record containing the
1665 representation of a Koha item record to the MARC
1666 biblio record. The input C<$item_marc> record
1667 is expect to contain just one field, the embedded
1668 item information field.
1672 sub _add_item_field_to_biblio {
1673 my ($item_marc, $biblionumber, $frameworkcode) = @_;
1675 my $biblio_marc = GetMarcBiblio($biblionumber);
1677 foreach my $field ($item_marc->fields()) {
1678 $biblio_marc->append_fields($field);
1681 ModBiblioMarc($biblio_marc, $biblionumber, $frameworkcode);
1684 =head2 _replace_item_field_in_biblio
1688 &_replace_item_field_in_biblio($item_marc, $biblionumber, $itemnumber, $frameworkcode)
1692 Given a MARC::Record C<$item_marc> containing one tag with the MARC
1693 representation of the item, examine the biblio MARC
1694 for the corresponding tag for that item and
1695 replace it with the tag from C<$item_marc>.
1699 sub _replace_item_field_in_biblio {
1700 my ($ItemRecord, $biblionumber, $itemnumber, $frameworkcode) = @_;
1701 my $dbh = C4::Context->dbh;
1703 # get complete MARC record & replace the item field by the new one
1704 my $completeRecord = GetMarcBiblio($biblionumber);
1705 my ($itemtag,$itemsubfield) = GetMarcFromKohaField("items.itemnumber",$frameworkcode);
1706 my $itemField = $ItemRecord->field($itemtag);
1707 my @items = $completeRecord->field($itemtag);
1710 if ($_->subfield($itemsubfield) eq $itemnumber) {
1711 $_->replace_with($itemField);
1717 # If we haven't found the matching field,
1718 # just add it. However, this means that
1719 # there is likely a bug.
1720 $completeRecord->append_fields($itemField);
1724 ModBiblioMarc($completeRecord, $biblionumber, $frameworkcode);