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/;
33 use vars qw($VERSION @ISA @EXPORT);
37 @ISA = qw( Exporter );
56 GetItemsByBiblioitemnumber
63 C4::Items - item management functions
67 This module contains an API for manipulating item
68 records in Koha, and is used by cataloguing, circulation,
69 acquisitions, and serials management.
71 A Koha item record is stored in two places: the
72 items table and embedded in a MARC tag in the XML
73 version of the associated bib record in C<biblioitems.marcxml>.
74 This is done to allow the item information to be readily
75 indexed (e.g., by Zebra), but means that each item
76 modification transaction must keep the items table
77 and the MARC XML in sync at all times.
79 Consequently, all code that creates, modifies, or deletes
80 item records B<must> use an appropriate function from
81 C<C4::Items>. If no existing function is suitable, it is
82 better to add one to C<C4::Items> than to use add
83 one-off SQL statements to add or modify items.
85 The items table will be considered authoritative. In other
86 words, if there is ever a discrepancy between the items
87 table and the MARC XML, the items table should be considered
90 =head1 HISTORICAL NOTE
92 Most of the functions in C<C4::Items> were originally in
93 the C<C4::Biblio> module.
95 =head1 CORE EXPORTED FUNCTIONS
97 The following functions are meant for use by users
106 $item = GetItem($itemnumber,$barcode);
110 Return item information, for a given itemnumber or barcode.
111 The return value is a hashref mapping item column
117 my ($itemnumber,$barcode) = @_;
118 my $dbh = C4::Context->dbh;
120 my $sth = $dbh->prepare("
122 WHERE itemnumber = ?");
123 $sth->execute($itemnumber);
124 my $data = $sth->fetchrow_hashref;
127 my $sth = $dbh->prepare("
131 $sth->execute($barcode);
132 my $data = $sth->fetchrow_hashref;
137 =head2 AddItemFromMarc
141 my ($biblionumber, $biblioitemnumber, $itemnumber)
142 = AddItemFromMarc($source_item_marc, $biblionumber);
146 Given a MARC::Record object containing an embedded item
147 record and a biblionumber, create a new item record.
151 sub AddItemFromMarc {
152 my ( $source_item_marc, $biblionumber ) = @_;
153 my $dbh = C4::Context->dbh;
155 # parse item hash from MARC
156 my $frameworkcode = GetFrameworkCode( $biblionumber );
157 my $item = &TransformMarcToKoha( $dbh, $source_item_marc, $frameworkcode );
159 return AddItem($item, $biblionumber, $dbh, $frameworkcode);
166 my ($biblionumber, $biblioitemnumber, $itemnumber)
167 = AddItem($item, $biblionumber[, $dbh, $frameworkcode]);
171 Given a hash containing item column names as keys,
172 create a new Koha item record.
174 The two optional parameters (C<$dbh> and C<$frameworkcode>)
175 do not need to be supplied for general use; they exist
176 simply to allow them to be picked up from AddItemFromMarc.
182 my $biblionumber = shift;
184 my $dbh = @_ ? shift : C4::Context->dbh;
185 my $frameworkcode = @_ ? shift : GetFrameworkCode( $biblionumber );
187 # needs old biblionumber and biblioitemnumber
188 $item->{'biblionumber'} = $biblionumber;
189 my $sth = $dbh->prepare("SELECT biblioitemnumber FROM biblioitems WHERE biblionumber=?");
190 $sth->execute( $item->{'biblionumber'} );
191 ($item->{'biblioitemnumber'}) = $sth->fetchrow;
193 _set_defaults_for_add($item);
194 _set_derived_columns_for_add($item);
195 # FIXME - checks here
196 my ( $itemnumber, $error ) = _koha_new_item( $dbh, $item, $item->{barcode} );
197 $item->{'itemnumber'} = $itemnumber;
199 # create MARC tag representing item and add to bib
200 my $new_item_marc = _marc_from_item_hash($item, $frameworkcode);
201 _add_item_field_to_biblio($new_item_marc, $item->{'biblionumber'}, $frameworkcode );
203 logaction(C4::Context->userenv->{'number'},"CATALOGUING","ADD",$itemnumber,"item")
204 if C4::Context->preference("CataloguingLog");
206 return ($item->{biblionumber}, $item->{biblioitemnumber}, $itemnumber);
209 =head2 ModItemFromMarc
213 ModItemFromMarc($item_marc, $biblionumber, $itemnumber);
217 This function updates an item record based on a supplied
218 C<MARC::Record> object containing an embedded item field.
219 This API is meant for the use of C<additem.pl>; for
220 other purposes, C<ModItem> should be used.
224 sub ModItemFromMarc {
225 my $item_marc = shift;
226 my $biblionumber = shift;
227 my $itemnumber = shift;
229 my $dbh = C4::Context->dbh;
230 my $frameworkcode = GetFrameworkCode( $biblionumber );
231 my $item = &TransformMarcToKoha( $dbh, $item_marc, $frameworkcode );
233 return ModItem($item, $biblionumber, $itemnumber, $dbh, $frameworkcode);
240 ModItem({ column => $newvalue }, $biblionumber, $itemnumber);
244 Change one or more columns in an item record and update
245 the MARC representation of the item.
247 The first argument is a hashref mapping from item column
248 names to the new values. The second and third arguments
249 are the biblionumber and itemnumber, respectively.
251 If one of the changed columns is used to calculate
252 the derived value of a column such as C<items.cn_sort>,
253 this routine will perform the necessary calculation
260 my $biblionumber = shift;
261 my $itemnumber = shift;
263 # if $biblionumber is undefined, get it from the current item
264 unless (defined $biblionumber) {
265 $biblionumber = _get_single_item_column('biblionumber', $itemnumber);
268 my $dbh = @_ ? shift : C4::Context->dbh;
269 my $frameworkcode = @_ ? shift : GetFrameworkCode( $biblionumber );
271 $item->{'itemnumber'} = $itemnumber;
272 _set_derived_columns_for_mod($item);
273 _do_column_fixes_for_mod($item);
276 # attempt to change itemnumber
277 # attempt to change biblionumber (if we want
278 # an API to relink an item to a different bib,
279 # it should be a separate function)
282 _koha_modify_item($dbh, $item);
284 # update biblio MARC XML
285 my $whole_item = GetItem($itemnumber);
286 my $new_item_marc = _marc_from_item_hash($whole_item, $frameworkcode);
287 _replace_item_field_in_biblio($new_item_marc, $biblionumber, $itemnumber, $frameworkcode);
289 logaction(C4::Context->userenv->{'number'},"CATALOGUING","MODIFY",$itemnumber,$new_item_marc->as_formatted)
290 if C4::Context->preference("CataloguingLog");
293 =head2 ModItemTransfer
297 ModItemTransfer($itenumber, $frombranch, $tobranch);
301 Marks an item as being transferred from one branch
306 sub ModItemTransfer {
307 my ( $itemnumber, $frombranch, $tobranch ) = @_;
309 my $dbh = C4::Context->dbh;
311 #new entry in branchtransfers....
312 my $sth = $dbh->prepare(
313 "INSERT INTO branchtransfers (itemnumber, frombranch, datesent, tobranch)
314 VALUES (?, ?, NOW(), ?)");
315 $sth->execute($itemnumber, $frombranch, $tobranch);
317 ModItem({ holdingbranch => $tobranch }, undef, $itemnumber);
318 ModDateLastSeen($itemnumber);
322 =head2 ModDateLastSeen
326 ModDateLastSeen($itemnum);
330 Mark item as seen. Is called when an item is issued, returned or manually marked during inventory/stocktaking.
331 C<$itemnum> is the item number
335 sub ModDateLastSeen {
336 my ($itemnumber) = @_;
338 my $today = C4::Dates->new();
339 ModItem({ itemlost => 0, datelastseen => $today->output("iso") }, undef, $itemnumber);
346 DelItem($biblionumber, $itemnumber);
350 Exported function (core API) for deleting an item record in Koha.
355 my ( $dbh, $biblionumber, $itemnumber ) = @_;
357 # FIXME check the item has no current issues
359 _koha_delete_item( $dbh, $itemnumber );
361 # get the MARC record
362 my $record = GetMarcBiblio($biblionumber);
363 my $frameworkcode = GetFrameworkCode($biblionumber);
366 my $copy2deleted = $dbh->prepare("UPDATE deleteditems SET marc=? WHERE itemnumber=?");
367 $copy2deleted->execute( $record->as_usmarc(), $itemnumber );
369 #search item field code
370 my ( $itemtag, $itemsubfield ) = GetMarcFromKohaField("items.itemnumber",$frameworkcode);
371 my @fields = $record->field($itemtag);
373 # delete the item specified
374 foreach my $field (@fields) {
375 if ( $field->subfield($itemsubfield) eq $itemnumber ) {
376 $record->delete_field($field);
379 &ModBiblioMarc( $record, $biblionumber, $frameworkcode );
380 &logaction(C4::Context->userenv->{'number'},"CATALOGUING","DELETE",$itemnumber,"item")
381 if C4::Context->preference("CataloguingLog");
384 =head1 EXPORTED SPECIAL ACCESSOR FUNCTIONS
386 The following functions provide various ways of
387 getting an item record, a set of item records, or
388 lists of authorized values for certain item fields.
390 Some of the functions in this group are candidates
391 for refactoring -- for example, some of the code
392 in C<GetItemsByBiblioitemnumber> and C<GetItemsInfo>
393 has copy-and-paste work.
401 $itemstatushash = GetItemStatus($fwkcode);
405 Returns a list of valid values for the
406 C<items.notforloan> field.
408 NOTE: does B<not> return an individual item's
411 Can be MARC dependant.
413 But basically could be can be loan or not
414 Create a status selector with the following code
416 =head3 in PERL SCRIPT
420 my $itemstatushash = getitemstatus;
422 foreach my $thisstatus (keys %$itemstatushash) {
423 my %row =(value => $thisstatus,
424 statusname => $itemstatushash->{$thisstatus}->{'statusname'},
426 push @itemstatusloop, \%row;
428 $template->param(statusloop=>\@itemstatusloop);
436 <select name="statusloop">
437 <option value="">Default</option>
438 <!-- TMPL_LOOP name="statusloop" -->
439 <option value="<!-- TMPL_VAR name="value" -->" <!-- TMPL_IF name="selected" -->selected<!-- /TMPL_IF -->><!-- TMPL_VAR name="statusname" --></option>
449 # returns a reference to a hash of references to status...
452 my $dbh = C4::Context->dbh;
454 $fwk = '' unless ($fwk);
455 my ( $tag, $subfield ) =
456 GetMarcFromKohaField( "items.notforloan", $fwk );
457 if ( $tag and $subfield ) {
460 "SELECT authorised_value
461 FROM marc_subfield_structure
467 $sth->execute( $tag, $subfield, $fwk );
468 if ( my ($authorisedvaluecat) = $sth->fetchrow ) {
471 "SELECT authorised_value,lib
472 FROM authorised_values
477 $authvalsth->execute($authorisedvaluecat);
478 while ( my ( $authorisedvalue, $lib ) = $authvalsth->fetchrow ) {
479 $itemstatus{$authorisedvalue} = $lib;
495 $itemstatus{"1"} = "Not For Loan";
499 =head2 GetItemLocation
503 $itemlochash = GetItemLocation($fwk);
507 Returns a list of valid values for the
508 C<items.location> field.
510 NOTE: does B<not> return an individual item's
513 where fwk stands for an optional framework code.
514 Create a location selector with the following code
516 =head3 in PERL SCRIPT
520 my $itemlochash = getitemlocation;
522 foreach my $thisloc (keys %$itemlochash) {
523 my $selected = 1 if $thisbranch eq $branch;
524 my %row =(locval => $thisloc,
525 selected => $selected,
526 locname => $itemlochash->{$thisloc},
528 push @itemlocloop, \%row;
530 $template->param(itemlocationloop => \@itemlocloop);
538 <select name="location">
539 <option value="">Default</option>
540 <!-- TMPL_LOOP name="itemlocationloop" -->
541 <option value="<!-- TMPL_VAR name="locval" -->" <!-- TMPL_IF name="selected" -->selected<!-- /TMPL_IF -->><!-- TMPL_VAR name="locname" --></option>
549 sub GetItemLocation {
551 # returns a reference to a hash of references to location...
554 my $dbh = C4::Context->dbh;
556 $fwk = '' unless ($fwk);
557 my ( $tag, $subfield ) =
558 GetMarcFromKohaField( "items.location", $fwk );
559 if ( $tag and $subfield ) {
562 "SELECT authorised_value
563 FROM marc_subfield_structure
568 $sth->execute( $tag, $subfield, $fwk );
569 if ( my ($authorisedvaluecat) = $sth->fetchrow ) {
572 "SELECT authorised_value,lib
573 FROM authorised_values
577 $authvalsth->execute($authorisedvaluecat);
578 while ( my ( $authorisedvalue, $lib ) = $authvalsth->fetchrow ) {
579 $itemlocation{$authorisedvalue} = $lib;
582 return \%itemlocation;
595 $itemlocation{"1"} = "Not For Loan";
596 return \%itemlocation;
603 $items = GetLostItems($where,$orderby);
607 This function get the items lost into C<$items>.
612 C<$where> is a hashref. it containts a field of the items table as key
613 and the value to match as value.
614 C<$orderby> is a field of the items table.
617 C<$items> is a reference to an array full of hasref which keys are items' table column.
619 =item usage in the perl script:
622 $where{barcode} = 0001548;
623 my $items = GetLostItems( \%where, "homebranch" );
624 $template->param(itemsloop => $items);
631 # Getting input args.
634 my $dbh = C4::Context->dbh;
639 WHERE itemlost IS NOT NULL
642 foreach my $key (keys %$where) {
643 $query .= " AND " . $key . " LIKE '%" . $where->{$key} . "%'";
645 $query .= " ORDER BY ".$orderby if defined $orderby;
647 my $sth = $dbh->prepare($query);
650 while ( my $row = $sth->fetchrow_hashref ){
656 =head2 GetItemsForInventory
660 $itemlist = GetItemsForInventory($minlocation,$maxlocation,$datelastseen,$offset,$size)
664 Retrieve a list of title/authors/barcode/callnumber, for biblio inventory.
666 The sub returns a list of hashes, containing itemnumber, author, title, barcode & item callnumber.
667 It is ordered by callnumber,title.
669 The minlocation & maxlocation parameters are used to specify a range of item callnumbers
670 the datelastseen can be used to specify that you want to see items not seen since a past date only.
671 offset & size can be used to retrieve only a part of the whole listing (defaut behaviour)
675 sub GetItemsForInventory {
676 my ( $minlocation, $maxlocation,$location, $datelastseen, $branch, $offset, $size ) = @_;
677 my $dbh = C4::Context->dbh;
680 $datelastseen=format_date_in_iso($datelastseen);
682 "SELECT itemnumber,barcode,itemcallnumber,title,author,biblio.biblionumber,datelastseen
684 LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber
685 WHERE itemcallnumber>= ?
686 AND itemcallnumber <=?
687 AND (datelastseen< ? OR datelastseen IS NULL)";
688 $query.= " AND items.location=".$dbh->quote($location) if $location;
689 $query.= " AND items.homebranch=".$dbh->quote($branch) if $branch;
690 $query .= " ORDER BY itemcallnumber,title";
691 $sth = $dbh->prepare($query);
692 $sth->execute( $minlocation, $maxlocation, $datelastseen );
696 SELECT itemnumber,barcode,itemcallnumber,biblio.biblionumber,title,author,datelastseen
698 LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber
699 WHERE itemcallnumber>= ?
700 AND itemcallnumber <=?";
701 $query.= " AND items.location=".$dbh->quote($location) if $location;
702 $query.= " AND items.homebranch=".$dbh->quote($branch) if $branch;
703 $query .= " ORDER BY itemcallnumber,title";
704 $sth = $dbh->prepare($query);
705 $sth->execute( $minlocation, $maxlocation );
708 while ( my $row = $sth->fetchrow_hashref ) {
709 $offset-- if ($offset);
710 $row->{datelastseen}=format_date($row->{datelastseen});
711 if ( ( !$offset ) && $size ) {
722 $count = &GetItemsCount( $biblionumber);
726 This function return count of item with $biblionumber
731 my ( $biblionumber ) = @_;
732 my $dbh = C4::Context->dbh;
733 my $query = "SELECT count(*)
735 WHERE biblionumber=?";
736 my $sth = $dbh->prepare($query);
737 $sth->execute($biblionumber);
738 my $count = $sth->fetchrow;
743 =head2 GetItemInfosOf
747 GetItemInfosOf(@itemnumbers);
754 my @itemnumbers = @_;
759 WHERE itemnumber IN (' . join( ',', @itemnumbers ) . ')
761 return get_infos_of( $query, 'itemnumber' );
764 =head2 GetItemsByBiblioitemnumber
768 GetItemsByBiblioitemnumber($biblioitemnumber);
772 Returns an arrayref of hashrefs suitable for use in a TMPL_LOOP
773 Called by C<C4::XISBN>
777 sub GetItemsByBiblioitemnumber {
778 my ( $bibitem ) = @_;
779 my $dbh = C4::Context->dbh;
780 my $sth = $dbh->prepare("SELECT * FROM items WHERE items.biblioitemnumber = ?") || die $dbh->errstr;
781 # Get all items attached to a biblioitem
784 $sth->execute($bibitem) || die $sth->errstr;
785 while ( my $data = $sth->fetchrow_hashref ) {
786 # Foreach item, get circulation information
787 my $sth2 = $dbh->prepare( "SELECT * FROM issues,borrowers
789 AND returndate is NULL
790 AND issues.borrowernumber = borrowers.borrowernumber"
792 $sth2->execute( $data->{'itemnumber'} );
793 if ( my $data2 = $sth2->fetchrow_hashref ) {
794 # if item is out, set the due date and who it is out too
795 $data->{'date_due'} = $data2->{'date_due'};
796 $data->{'cardnumber'} = $data2->{'cardnumber'};
797 $data->{'borrowernumber'} = $data2->{'borrowernumber'};
800 # set date_due to blank, so in the template we check itemlost, and wthdrawn
801 $data->{'date_due'} = '';
804 # Find the last 3 people who borrowed this item.
805 my $query2 = "SELECT * FROM issues, borrowers WHERE itemnumber = ?
806 AND issues.borrowernumber = borrowers.borrowernumber
807 AND returndate is not NULL
808 ORDER BY returndate desc,timestamp desc LIMIT 3";
809 $sth2 = $dbh->prepare($query2) || die $dbh->errstr;
810 $sth2->execute( $data->{'itemnumber'} ) || die $sth2->errstr;
812 while ( my $data2 = $sth2->fetchrow_hashref ) {
813 $data->{"timestamp$i2"} = $data2->{'timestamp'};
814 $data->{"card$i2"} = $data2->{'cardnumber'};
815 $data->{"borrower$i2"} = $data2->{'borrowernumber'};
819 push(@results,$data);
829 @results = GetItemsInfo($biblionumber, $type);
833 Returns information about books with the given biblionumber.
835 C<$type> may be either C<intra> or anything else. If it is not set to
836 C<intra>, then the search will exclude lost, very overdue, and
839 C<GetItemsInfo> returns a list of references-to-hash. Each element
840 contains a number of keys. Most of them are table items from the
841 C<biblio>, C<biblioitems>, C<items>, and C<itemtypes> tables in the
842 Koha database. Other keys include:
846 =item C<$data-E<gt>{branchname}>
848 The name (not the code) of the branch to which the book belongs.
850 =item C<$data-E<gt>{datelastseen}>
852 This is simply C<items.datelastseen>, except that while the date is
853 stored in YYYY-MM-DD format in the database, here it is converted to
854 DD/MM/YYYY format. A NULL date is returned as C<//>.
856 =item C<$data-E<gt>{datedue}>
858 =item C<$data-E<gt>{class}>
860 This is the concatenation of C<biblioitems.classification>, the book's
861 Dewey code, and C<biblioitems.subclass>.
863 =item C<$data-E<gt>{ocount}>
865 I think this is the number of copies of the book available.
867 =item C<$data-E<gt>{order}>
869 If this is set, it is set to C<One Order>.
876 my ( $biblionumber, $type ) = @_;
877 my $dbh = C4::Context->dbh;
878 my $query = "SELECT *,items.notforloan as itemnotforloan
880 LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber
881 LEFT JOIN biblioitems ON biblioitems.biblioitemnumber = items.biblioitemnumber";
882 $query .= (C4::Context->preference('item-level_itypes')) ?
883 " LEFT JOIN itemtypes on items.itype = itemtypes.itemtype "
884 : " LEFT JOIN itemtypes on biblioitems.itemtype = itemtypes.itemtype ";
885 $query .= "WHERE items.biblionumber = ? ORDER BY items.dateaccessioned desc" ;
886 my $sth = $dbh->prepare($query);
887 $sth->execute($biblionumber);
890 my ( $date_due, $count_reserves );
892 my $isth = $dbh->prepare(
893 "SELECT issues.*,borrowers.cardnumber,borrowers.surname,borrowers.firstname,borrowers.branchcode as bcode
894 FROM issues LEFT JOIN borrowers ON issues.borrowernumber=borrowers.borrowernumber
896 AND returndate IS NULL"
898 while ( my $data = $sth->fetchrow_hashref ) {
900 $isth->execute( $data->{'itemnumber'} );
901 if ( my $idata = $isth->fetchrow_hashref ) {
902 $data->{borrowernumber} = $idata->{borrowernumber};
903 $data->{cardnumber} = $idata->{cardnumber};
904 $data->{surname} = $idata->{surname};
905 $data->{firstname} = $idata->{firstname};
906 $datedue = $idata->{'date_due'};
907 if (C4::Context->preference("IndependantBranches")){
908 my $userenv = C4::Context->userenv;
909 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
910 $data->{'NOTSAMEBRANCH'} = 1 if ($idata->{'bcode'} ne $userenv->{branch});
914 if ( $datedue eq '' ) {
915 my ( $restype, $reserves ) =
916 C4::Reserves::CheckReserves( $data->{'itemnumber'} );
918 $count_reserves = $restype;
923 #get branch information.....
924 my $bsth = $dbh->prepare(
925 "SELECT * FROM branches WHERE branchcode = ?
928 $bsth->execute( $data->{'holdingbranch'} );
929 if ( my $bdata = $bsth->fetchrow_hashref ) {
930 $data->{'branchname'} = $bdata->{'branchname'};
932 $data->{'datedue'} = $datedue;
933 $data->{'count_reserves'} = $count_reserves;
935 # get notforloan complete status if applicable
936 my $sthnflstatus = $dbh->prepare(
937 'SELECT authorised_value
938 FROM marc_subfield_structure
939 WHERE kohafield="items.notforloan"
943 $sthnflstatus->execute;
944 my ($authorised_valuecode) = $sthnflstatus->fetchrow;
945 if ($authorised_valuecode) {
946 $sthnflstatus = $dbh->prepare(
947 "SELECT lib FROM authorised_values
949 AND authorised_value=?"
951 $sthnflstatus->execute( $authorised_valuecode,
952 $data->{itemnotforloan} );
953 my ($lib) = $sthnflstatus->fetchrow;
954 $data->{notforloan} = $lib;
957 # my stack procedures
958 my $stackstatus = $dbh->prepare(
959 'SELECT authorised_value
960 FROM marc_subfield_structure
961 WHERE kohafield="items.stack"
964 $stackstatus->execute;
966 ($authorised_valuecode) = $stackstatus->fetchrow;
967 if ($authorised_valuecode) {
968 $stackstatus = $dbh->prepare(
970 FROM authorised_values
972 AND authorised_value=?
975 $stackstatus->execute( $authorised_valuecode, $data->{stack} );
976 my ($lib) = $stackstatus->fetchrow;
977 $data->{stack} = $lib;
979 # Find the last 3 people who borrowed this item.
980 my $sth2 = $dbh->prepare("SELECT * FROM issues,borrowers
982 AND issues.borrowernumber = borrowers.borrowernumber
983 AND returndate IS NOT NULL LIMIT 3");
984 $sth2->execute($data->{'itemnumber'});
986 while (my $data2 = $sth2->fetchrow_hashref()) {
987 $data->{"timestamp$ii"} = $data2->{'timestamp'} if $data2->{'timestamp'};
988 $data->{"card$ii"} = $data2->{'cardnumber'} if $data2->{'cardnumber'};
989 $data->{"borrower$ii"} = $data2->{'borrowernumber'} if $data2->{'borrowernumber'};
993 $results[$i] = $data;
1001 =head2 get_itemnumbers_of
1005 my @itemnumbers_of = get_itemnumbers_of(@biblionumbers);
1009 Given a list of biblionumbers, return the list of corresponding itemnumbers
1010 for each biblionumber.
1012 Return a reference on a hash where keys are biblionumbers and values are
1013 references on array of itemnumbers.
1017 sub get_itemnumbers_of {
1018 my @biblionumbers = @_;
1020 my $dbh = C4::Context->dbh;
1026 WHERE biblionumber IN (?' . ( ',?' x scalar @biblionumbers - 1 ) . ')
1028 my $sth = $dbh->prepare($query);
1029 $sth->execute(@biblionumbers);
1033 while ( my ( $itemnumber, $biblionumber ) = $sth->fetchrow_array ) {
1034 push @{ $itemnumbers_of{$biblionumber} }, $itemnumber;
1037 return \%itemnumbers_of;
1040 =head1 LIMITED USE FUNCTIONS
1042 The following functions, while part of the public API,
1043 are not exported. This is generally because they are
1044 meant to be used by only one script for a specific
1045 purpose, and should not be used in any other context
1046 without careful thought.
1054 my $item_marc = GetMarcItem($biblionumber, $itemnumber);
1058 Returns MARC::Record of the item passed in parameter.
1059 This function is meant for use only in C<cataloguing/additem.pl>,
1060 where it is needed to support that script's MARC-like
1066 my ( $biblionumber, $itemnumber ) = @_;
1068 # GetMarcItem has been revised so that it does the following:
1069 # 1. Gets the item information from the items table.
1070 # 2. Converts it to a MARC field for storage in the bib record.
1072 # The previous behavior was:
1073 # 1. Get the bib record.
1074 # 2. Return the MARC tag corresponding to the item record.
1076 # The difference is that one treats the items row as authoritative,
1077 # while the other treats the MARC representation as authoritative
1078 # under certain circumstances.
1080 my $itemrecord = GetItem($itemnumber);
1082 # Tack on 'items.' prefix to column names so that TransformKohaToMarc will work.
1083 # Also, don't emit a subfield if the underlying field is blank.
1084 my $mungeditem = { map { $itemrecord->{$_} ne '' ? ("items.$_" => $itemrecord->{$_}) : () } keys %{ $itemrecord } };
1086 my $itemmarc = TransformKohaToMarc($mungeditem);
1091 =head1 PRIVATE FUNCTIONS AND VARIABLES
1093 The following functions are not meant to be called
1094 directly, but are documented in order to explain
1095 the inner workings of C<C4::Items>.
1099 =head2 %derived_columns
1101 This hash keeps track of item columns that
1102 are strictly derived from other columns in
1103 the item record and are not meant to be set
1106 Each key in the hash should be the name of a
1107 column (as named by TransformMarcToKoha). Each
1108 value should be hashref whose keys are the
1109 columns on which the derived column depends. The
1110 hashref should also contain a 'BUILDER' key
1111 that is a reference to a sub that calculates
1116 my %derived_columns = (
1117 'items.cn_sort' => {
1118 'itemcallnumber' => 1,
1119 'items.cn_source' => 1,
1120 'BUILDER' => \&_calc_items_cn_sort,
1124 =head2 _set_derived_columns_for_add
1128 _set_derived_column_for_add($item);
1132 Given an item hash representing a new item to be added,
1133 calculate any derived columns. Currently the only
1134 such column is C<items.cn_sort>.
1138 sub _set_derived_columns_for_add {
1141 foreach my $column (keys %derived_columns) {
1142 my $builder = $derived_columns{$column}->{'BUILDER'};
1143 my $source_values = {};
1144 foreach my $source_column (keys %{ $derived_columns{$column} }) {
1145 next if $source_column eq 'BUILDER';
1146 $source_values->{$source_column} = $item->{$source_column};
1148 $builder->($item, $source_values);
1152 =head2 _set_derived_columns_for_mod
1156 _set_derived_column_for_mod($item);
1160 Given an item hash representing a new item to be modified.
1161 calculate any derived columns. Currently the only
1162 such column is C<items.cn_sort>.
1164 This routine differs from C<_set_derived_columns_for_add>
1165 in that it needs to handle partial item records. In other
1166 words, the caller of C<ModItem> may have supplied only one
1167 or two columns to be changed, so this function needs to
1168 determine whether any of the columns to be changed affect
1169 any of the derived columns. Also, if a derived column
1170 depends on more than one column, but the caller is not
1171 changing all of then, this routine retrieves the unchanged
1172 values from the database in order to ensure a correct
1177 sub _set_derived_columns_for_mod {
1180 foreach my $column (keys %derived_columns) {
1181 my $builder = $derived_columns{$column}->{'BUILDER'};
1182 my $source_values = {};
1183 my %missing_sources = ();
1184 my $must_recalc = 0;
1185 foreach my $source_column (keys %{ $derived_columns{$column} }) {
1186 next if $source_column eq 'BUILDER';
1187 if (exists $item->{$source_column}) {
1189 $source_values->{$source_column} = $item->{$source_column};
1191 $missing_sources{$source_column} = 1;
1195 foreach my $source_column (keys %missing_sources) {
1196 $source_values->{$source_column} = _get_single_item_column($source_column, $item->{'itemnumber'});
1198 $builder->($item, $source_values);
1203 =head2 _do_column_fixes_for_mod
1207 _do_column_fixes_for_mod($item);
1211 Given an item hashref containing one or more
1212 columns to modify, fix up certain values.
1213 Specifically, set to 0 any passed value
1214 of C<notforloan>, C<damaged>, C<itemlost>, or
1215 C<wthdrawn> that is either undefined or
1216 contains the empty string.
1220 sub _do_column_fixes_for_mod {
1223 if (exists $item->{'notforloan'} and
1224 (not defined $item->{'notforloan'} or $item->{'notforloan'} eq '')) {
1225 $item->{'notforloan'} = 0;
1227 if (exists $item->{'damaged'} and
1228 (not defined $item->{'damaged'} or $item->{'damaged'} eq '')) {
1229 $item->{'damaged'} = 0;
1231 if (exists $item->{'itemlost'} and
1232 (not defined $item->{'itemlost'} or $item->{'itemlost'} eq '')) {
1233 $item->{'itemlost'} = 0;
1235 if (exists $item->{'wthdrawn'} and
1236 (not defined $item->{'wthdrawn'} or $item->{'wthdrawn'} eq '')) {
1237 $item->{'wthdrawn'} = 0;
1241 =head2 _get_single_item_column
1245 _get_single_item_column($column, $itemnumber);
1249 Retrieves the value of a single column from an C<items>
1250 row specified by C<$itemnumber>.
1254 sub _get_single_item_column {
1256 my $itemnumber = shift;
1258 my $dbh = C4::Context->dbh;
1259 my $sth = $dbh->prepare("SELECT $column FROM items WHERE itemnumber = ?");
1260 $sth->execute($itemnumber);
1261 my ($value) = $sth->fetchrow();
1265 =head2 _calc_items_cn_sort
1269 _calc_items_cn_sort($item, $source_values);
1273 Helper routine to calculate C<items.cn_sort>.
1277 sub _calc_items_cn_sort {
1279 my $source_values = shift;
1281 $item->{'items.cn_sort'} = GetClassSort($source_values->{'items.cn_source'}, $source_values->{'itemcallnumber'}, "");
1284 =head2 _set_defaults_for_add
1288 _set_defaults_for_add($item_hash);
1292 Given an item hash representing an item to be added, set
1293 correct default values for columns whose default value
1294 is not handled by the DBMS. This includes the following
1301 C<items.dateaccessioned>
1323 sub _set_defaults_for_add {
1326 # if dateaccessioned is provided, use it. Otherwise, set to NOW()
1327 if (!(exists $item->{'dateaccessioned'}) ||
1328 ($item->{'dateaccessioned'} eq '')) {
1329 # FIXME add check for invalid date
1330 my $today = C4::Dates->new();
1331 $item->{'dateaccessioned'} = $today->output("iso"); #TODO: check time issues
1334 # various item status fields cannot be null
1335 $item->{'notforloan'} = 0 unless exists $item->{'notforloan'} and defined $item->{'notforloan'};
1336 $item->{'damaged'} = 0 unless exists $item->{'damaged'} and defined $item->{'damaged'};
1337 $item->{'itemlost'} = 0 unless exists $item->{'itemlost'} and defined $item->{'itemlost'};
1338 $item->{'wthdrawn'} = 0 unless exists $item->{'wthdrawn'} and defined $item->{'wthdrawn'};
1341 =head2 _koha_new_item
1345 my ($itemnumber,$error) = _koha_new_item( $dbh, $item, $barcode );
1349 Perform the actual insert into the C<items> table.
1353 sub _koha_new_item {
1354 my ( $dbh, $item, $barcode ) = @_;
1358 "INSERT INTO items SET
1360 biblioitemnumber = ?,
1362 dateaccessioned = ?,
1366 replacementprice = ?,
1367 replacementpricedate = NOW(),
1368 datelastborrowed = ?,
1369 datelastseen = NOW(),
1392 my $sth = $dbh->prepare($query);
1394 $item->{'biblionumber'},
1395 $item->{'biblioitemnumber'},
1397 $item->{'dateaccessioned'},
1398 $item->{'booksellerid'},
1399 $item->{'homebranch'},
1401 $item->{'replacementprice'},
1402 $item->{datelastborrowed},
1404 $item->{'notforloan'},
1406 $item->{'itemlost'},
1407 $item->{'wthdrawn'},
1408 $item->{'itemcallnumber'},
1409 $item->{'restricted'},
1410 $item->{'itemnotes'},
1411 $item->{'holdingbranch'},
1413 $item->{'location'},
1416 $item->{'renewals'},
1417 $item->{'reserves'},
1418 $item->{'items.cn_source'},
1419 $item->{'items.cn_sort'},
1422 $item->{'materials'},
1425 my $itemnumber = $dbh->{'mysql_insertid'};
1426 if ( defined $sth->errstr ) {
1427 $error.="ERROR in _koha_new_item $query".$sth->errstr;
1430 return ( $itemnumber, $error );
1433 =head2 _koha_modify_item
1437 my ($itemnumber,$error) =_koha_modify_item( $dbh, $item, $op );
1441 Perform the actual update of the C<items> row. Note that this
1442 routine accepts a hashref specifying the columns to update.
1446 sub _koha_modify_item {
1447 my ( $dbh, $item ) = @_;
1450 my $query = "UPDATE items SET ";
1452 for my $key ( keys %$item ) {
1454 push @bind, $item->{$key};
1457 $query .= " WHERE itemnumber=?";
1458 push @bind, $item->{'itemnumber'};
1459 my $sth = $dbh->prepare($query);
1460 $sth->execute(@bind);
1461 if ( $dbh->errstr ) {
1462 $error.="ERROR in _koha_modify_item $query".$dbh->errstr;
1466 return ($item->{'itemnumber'},$error);
1469 =head2 _koha_delete_item
1473 _koha_delete_item( $dbh, $itemnum );
1477 Internal function to delete an item record from the koha tables
1481 sub _koha_delete_item {
1482 my ( $dbh, $itemnum ) = @_;
1484 # save the deleted item to deleteditems table
1485 my $sth = $dbh->prepare("SELECT * FROM items WHERE itemnumber=?");
1486 $sth->execute($itemnum);
1487 my $data = $sth->fetchrow_hashref();
1489 my $query = "INSERT INTO deleteditems SET ";
1491 foreach my $key ( keys %$data ) {
1492 $query .= "$key = ?,";
1493 push( @bind, $data->{$key} );
1496 $sth = $dbh->prepare($query);
1497 $sth->execute(@bind);
1500 # delete from items table
1501 $sth = $dbh->prepare("DELETE FROM items WHERE itemnumber=?");
1502 $sth->execute($itemnum);
1507 =head2 _marc_from_item_hash
1511 my $item_marc = _marc_from_item_hash($item, $frameworkcode);
1515 Given an item hash representing a complete item record,
1516 create a C<MARC::Record> object containing an embedded
1517 tag representing that item.
1521 sub _marc_from_item_hash {
1523 my $frameworkcode = shift;
1525 # Tack on 'items.' prefix to column names so lookup from MARC frameworks will work
1526 # Also, don't emit a subfield if the underlying field is blank.
1527 my $mungeditem = { map { (defined($item->{$_}) and $item->{$_} ne '') ?
1528 (/^items\./ ? ($_ => $item->{$_}) : ("items.$_" => $item->{$_}))
1529 : () } keys %{ $item } };
1531 my $item_marc = MARC::Record->new();
1532 foreach my $item_field (keys %{ $mungeditem }) {
1533 my ($tag, $subfield) = GetMarcFromKohaField($item_field, $frameworkcode);
1534 next unless defined $tag and defined $subfield; # skip if not mapped to MARC field
1535 if (my $field = $item_marc->field($tag)) {
1536 $field->add_subfields($subfield => $mungeditem->{$item_field});
1538 $item_marc->add_fields( $tag, " ", " ", $subfield => $mungeditem->{$item_field});
1545 =head2 _add_item_field_to_biblio
1549 _add_item_field_to_biblio($item_marc, $biblionumber, $frameworkcode);
1553 Adds the fields from a MARC record containing the
1554 representation of a Koha item record to the MARC
1555 biblio record. The input C<$item_marc> record
1556 is expect to contain just one field, the embedded
1557 item information field.
1561 sub _add_item_field_to_biblio {
1562 my ($item_marc, $biblionumber, $frameworkcode) = @_;
1564 my $biblio_marc = GetMarcBiblio($biblionumber);
1566 foreach my $field ($item_marc->fields()) {
1567 $biblio_marc->append_fields($field);
1570 ModBiblioMarc($biblio_marc, $biblionumber, $frameworkcode);
1573 =head2 _replace_item_field_in_biblio
1577 &_replace_item_field_in_biblio($item_marc, $biblionumber, $itemnumber, $frameworkcode)
1581 Given a MARC::Record C<$item_marc> containing one tag with the MARC
1582 representation of the item, examine the biblio MARC
1583 for the corresponding tag for that item and
1584 replace it with the tag from C<$item_marc>.
1588 sub _replace_item_field_in_biblio {
1589 my ($ItemRecord, $biblionumber, $itemnumber, $frameworkcode) = @_;
1590 my $dbh = C4::Context->dbh;
1592 # get complete MARC record & replace the item field by the new one
1593 my $completeRecord = GetMarcBiblio($biblionumber);
1594 my ($itemtag,$itemsubfield) = GetMarcFromKohaField("items.itemnumber",$frameworkcode);
1595 my $itemField = $ItemRecord->field($itemtag);
1596 my @items = $completeRecord->field($itemtag);
1599 if ($_->subfield($itemsubfield) eq $itemnumber) {
1600 $_->replace_with($itemField);
1606 # If we haven't found the matching field,
1607 # just add it. However, this means that
1608 # there is likely a bug.
1609 $completeRecord->append_fields($itemField);
1613 ModBiblioMarc($completeRecord, $biblionumber, $frameworkcode);