2 # Please use 8-character tabs for this file (indents are every 4 characters)
4 package C4::BookShelves;
8 # Copyright 2000-2002 Katipo Communications
10 # This file is part of Koha.
12 # Koha is free software; you can redistribute it and/or modify it under the
13 # terms of the GNU General Public License as published by the Free Software
14 # Foundation; either version 2 of the License, or (at your option) any later
17 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
18 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
19 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
21 # You should have received a copy of the GNU General Public License along with
22 # Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
23 # Suite 330, Boston, MA 02111-1307 USA
28 use C4::Circulation::Circ2;
33 use vars qw($VERSION @ISA @EXPORT);
35 # set the version for version checking
40 C4::BookShelves - Functions for manipulating Koha virtual bookshelves
48 This module provides functions for manipulating virtual bookshelves,
49 including creating and deleting bookshelves, and adding and removing
50 items to and from bookshelves.
59 @EXPORT = qw(&GetShelfList &GetShelfContents &AddToShelf &AddToShelfFromBiblio
60 &RemoveFromShelf &AddShelf &RemoveShelf
63 &GetShelfListExt &AddShelfExt &EditShelfExt &RemoveShelfExt
64 &GetShelfInfo &GetShelfContentsExt &RemoveFromShelfExt
65 &GetShelfListOfExt &AddToShelfExt
67 &AddRequestToShelf &CountShelfRequest &GetShelfRequests
68 &RejectShelfRequest &CatalogueShelfRequest &GetShelfRequestOwner
73 $dbh = C4::Context->dbh;
75 =item ShelfPossibleAction
79 =item C<$loggedinuser,$shelfnumber,$action>
81 $action can be "view" or "manage".
83 Returns 1 if the user can do the $action in the $shelfnumber shelf.
89 sub ShelfPossibleAction {
90 my ($loggedinuser,$shelfnumber,$action)= @_;
91 my $sth = $dbh->prepare("select owner,category from bookshelf where shelfnumber=?");
92 $sth->execute($shelfnumber);
93 my ($owner,$category) = $sth->fetchrow;
94 return 1 if (($category>=3 or $owner eq $loggedinuser) && $action eq 'manage');
95 return 1 if (($category>= 2 or $owner eq $loggedinuser) && $action eq 'view');
101 $shelflist = &GetShelfList();
102 ($shelfnumber, $shelfhash) = each %{$shelflist};
104 Looks up the virtual bookshelves, and returns a summary. C<$shelflist>
105 is a reference-to-hash. The keys are the bookshelf numbers
106 (C<$shelfnumber>, above), and the values (C<$shelfhash>, above) are
107 themselves references-to-hash, with the following keys:
111 =item C<$shelfhash-E<gt>{shelfname}>
113 A string. The name of the shelf.
115 =item C<$shelfhash-E<gt>{count}>
117 The number of books on that bookshelf.
123 # FIXME - Wouldn't it be more intuitive to return a list, rather than
124 # a reference-to-hash? The shelf number can be just another key in the
127 my ($owner,$mincategory) = @_;
128 # mincategory : 2 if the list is for "look". 3 if the list is for "Select bookshelf for adding a book".
129 # bookshelves of the owner are always selected, whatever the category
130 my $sth=$dbh->prepare("SELECT bookshelf.shelfnumber, bookshelf.shelfname,owner,surname,firstname, category,
131 count(shelfcontents.itemnumber) as count
133 LEFT JOIN shelfcontents
134 ON bookshelf.shelfnumber = shelfcontents.shelfnumber
135 left join borrowers on bookshelf.owner = borrowers.borrowernumber
137 where owner=? or category>=?
138 GROUP BY bookshelf.shelfnumber order by shelfname");
139 $sth->execute($owner,$mincategory);
141 while (my ($shelfnumber, $shelfname,$owner,$surname,$firstname,$category,$count) = $sth->fetchrow) {
142 $shelflist{$shelfnumber}->{'shelfname'}=$shelfname;
143 $shelflist{$shelfnumber}->{'count'}=$count;
144 $shelflist{$shelfnumber}->{'owner'}=$owner;
145 $shelflist{$shelfnumber}->{'surname'} = $surname;
146 $shelflist{$shelfnumber}->{'firstname'} = $firstname;
147 $shelflist{$shelfnumber}->{'category'} = $category;
155 =item GetShelfContents
157 $itemlist = &GetShelfContents($env, $shelfnumber);
159 Looks up information about the contents of virtual bookshelf number
162 Returns a reference-to-array, whose elements are references-to-hash,
163 as returned by C<&getiteminformation>.
165 I don't know what C<$env> is.
169 sub GetShelfContents {
170 my ($env, $shelfnumber) = @_;
172 my $sth=$dbh->prepare("select itemnumber from shelfcontents where shelfnumber=? order by itemnumber");
173 $sth->execute($shelfnumber);
174 while (my ($itemnumber) = $sth->fetchrow) {
175 my ($item) = getiteminformation($env, $itemnumber, 0);
176 push (@itemlist, $item);
183 &AddToShelf($env, $itemnumber, $shelfnumber);
185 Adds item number C<$itemnumber> to virtual bookshelf number
186 C<$shelfnumber>, unless that item is already on that shelf.
193 my ($env, $itemnumber, $shelfnumber) = @_;
194 return unless $itemnumber;
195 my $sth=$dbh->prepare("select * from shelfcontents where shelfnumber=? and itemnumber=?");
197 $sth->execute($shelfnumber, $itemnumber);
201 $sth=$dbh->prepare("insert into shelfcontents (shelfnumber, itemnumber, flags) values (?, ?, 0)");
202 $sth->execute($shelfnumber, $itemnumber);
205 sub AddToShelfFromBiblio {
206 my ($env, $biblionumber, $shelfnumber) = @_;
207 return unless $biblionumber;
208 my $sth = $dbh->prepare("select itemnumber from items where biblionumber=?");
209 $sth->execute($biblionumber);
210 my ($itemnumber) = $sth->fetchrow;
211 $sth=$dbh->prepare("select * from shelfcontents where shelfnumber=? and itemnumber=?");
212 $sth->execute($shelfnumber, $itemnumber);
216 $sth=$dbh->prepare("insert into shelfcontents (shelfnumber, itemnumber, flags,biblionumber) values (?, ?, 0,?)");
217 $sth->execute($shelfnumber, $itemnumber,$biblionumber);
221 =item RemoveFromShelf
223 &RemoveFromShelf($env, $itemnumber, $shelfnumber);
225 Removes item number C<$itemnumber> from virtual bookshelf number
226 C<$shelfnumber>. If the item wasn't on that bookshelf to begin with,
233 sub RemoveFromShelf {
234 my ($env, $itemnumber, $shelfnumber) = @_;
235 my $sth=$dbh->prepare("delete from shelfcontents where shelfnumber=? and itemnumber=?");
236 $sth->execute($shelfnumber,$itemnumber);
241 ($status, $msg) = &AddShelf($env, $shelfname);
243 Creates a new virtual bookshelf with name C<$shelfname>.
245 Returns a two-element array, where C<$status> is 0 if the operation
246 was successful, or non-zero otherwise. C<$msg> is "Done" in case of
247 success, or an error message giving the reason for failure.
253 # FIXME - Perhaps this could/should return the number of the new bookshelf
256 my ($env, $shelfname,$owner,$category) = @_;
257 my $sth=$dbh->prepare("select * from bookshelf where shelfname=?");
258 $sth->execute($shelfname);
260 return(1, "Shelf \"$shelfname\" already exists");
262 $sth=$dbh->prepare("insert into bookshelf (shelfname,owner,category) values (?,?,?)");
263 $sth->execute($shelfname,$owner,$category);
270 ($status, $msg) = &RemoveShelf($env, $shelfnumber);
272 Deletes virtual bookshelf number C<$shelfnumber>. The bookshelf must
275 Returns a two-element array, where C<$status> is 0 if the operation
276 was successful, or non-zero otherwise. C<$msg> is "Done" in case of
277 success, or an error message giving the reason for failure.
284 my ($env, $shelfnumber) = @_;
285 my $sth=$dbh->prepare("select count(*) from shelfcontents where shelfnumber=?");
286 $sth->execute($shelfnumber);
287 my ($count)=$sth->fetchrow;
289 return (1, "Shelf has $count items on it. Please remove all items before deleting this shelf.");
291 $sth=$dbh->prepare("delete from bookshelf where shelfnumber=?");
292 $sth->execute($shelfnumber);
297 sub GetShelfListOfExt {
301 $sth = $dbh->prepare("SELECT * FROM bookshelf WHERE (owner = ?) or category>=2 ORDER BY shelfname");
302 $sth->execute($owner);
304 $sth = $dbh->prepare("SELECT * FROM bookshelf where category<2 ORDER BY shelfname");
308 my $sth2 = $dbh->prepare("SELECT count(biblionumber) as bibliocount FROM shelfcontents WHERE (shelfnumber = ?)");
311 while (my $row = $sth->fetchrow_hashref) {
312 $sth2->execute($row->{'shelfnumber'});
313 $row->{'bibliocount'} = $sth2->fetchrow;
314 if ($row->{'category'} == 1) {
315 $row->{'private'} = 1;
317 $row->{'public'} = 1;
324 sub GetShelfListExt {
325 my ($owner,$mincategory,$id_intitution, $intra) = @_;
327 my $sth1 = $dbh->prepare("SELECT * FROM careers WHERE id_institution = ?");
328 $sth1->execute($id_intitution);
331 my $total_shelves = 0;
332 while (my $row1 = $sth1->fetchrow_hashref) {
337 $sth2=$dbh->prepare("SELECT
338 bookshelf.shelfnumber, bookshelf.shelfname,owner,surname,firstname, category,
339 count(shelfcontents.biblionumber) as count
342 LEFT JOIN shelfcontents ON bookshelf.shelfnumber = shelfcontents.shelfnumber
343 LEFT JOIN borrowers ON bookshelf.owner = borrowers.borrowernumber
344 LEFT JOIN bookshelves_careers ON bookshelves_careers.shelfnumber = bookshelf.shelfnumber
347 GROUP BY bookshelf.shelfnumber
348 ORDER BY shelfname");
349 $sth2->execute($row1->{'id_career'});
352 $sth2=$dbh->prepare("SELECT
353 bookshelf.shelfnumber, bookshelf.shelfname,owner,surname,firstname, category,
354 count(shelfcontents.biblionumber) as count
357 LEFT JOIN shelfcontents ON bookshelf.shelfnumber = shelfcontents.shelfnumber
358 LEFT JOIN borrowers ON bookshelf.owner = borrowers.borrowernumber
359 LEFT JOIN bookshelves_careers ON bookshelves_careers.shelfnumber = bookshelf.shelfnumber
361 (owner = ? OR category >= ?) AND (id_career = ?)
362 GROUP BY bookshelf.shelfnumber
363 ORDER BY shelfname");
364 $sth2->execute($owner,$mincategory,$row1->{'id_career'});
367 $row1->{'shelfcount'} = 0;
368 while (my $row2 = $sth2->fetchrow_hashref) {
369 if ($owner == $row2->{'owner'}) {
370 $row2->{'canmanage'} = 1;
372 if ($row2->{'category'} == 1) {
373 $row2->{'private'} = 1;
375 $row2->{'public'} = 1;
377 $row1->{'shelfcount'}++;
379 push @shelves, $row2;
381 $row1->{'shelvesloop'} = \@shelves;
382 push @results, $row1;
385 return($total_shelves, \@results);
389 my ($shelfname,$owner,$category,$careers) = @_;
390 my $sth = $dbh->prepare("SELECT * FROM bookshelf WHERE shelfname = ?");
391 $sth->execute($shelfname);
395 $sth = $dbh->prepare("INSERT INTO bookshelf (shelfname,owner,category) VALUES (?,?,?)");
396 $sth->execute($shelfname,$owner,$category);
397 my $shelfnumber = $dbh->{'mysql_insertid'};
399 foreach my $row (@{$careers}) {
400 $sth = $dbh->prepare("INSERT INTO bookshelves_careers VALUES (?,?)");
401 $sth->execute($shelfnumber, $row);
408 my ($shelfnumber,$shelfname,$category,$careers) = @_;
409 my $sth = $dbh->prepare("SELECT * FROM bookshelf WHERE shelfname = ? AND NOT shelfnumber = ? ");
410 $sth->execute($shelfname, $shelfnumber);
414 $sth = $dbh->prepare("UPDATE bookshelf SET shelfname = ?, category = ? WHERE shelfnumber = ?");
415 $sth->execute($shelfname,$category,$shelfnumber);
417 $sth = $dbh->prepare("DELETE FROM bookshelves_careers WHERE shelfnumber = ?");
418 $sth->execute($shelfnumber);
420 foreach my $row (@{$careers}) {
421 $sth = $dbh->prepare("INSERT INTO bookshelves_careers VALUES (?,?)");
422 $sth->execute($shelfnumber, $row);
430 my ($shelfnumber) = @_;
431 my $sth = $dbh->prepare("DELETE FROM bookshelves_careers WHERE shelfnumber = ?");
432 $sth->execute($shelfnumber);
433 my $sth = $dbh->prepare("DELETE FROM shelfcontents WHERE shelfnumber = ?");
434 $sth->execute($shelfnumber);
435 $sth = $dbh->prepare("DELETE FROM bookshelf WHERE shelfnumber = ?");
436 $sth->execute($shelfnumber);
441 my ($shelfnumber, $owner) = @_;
442 my $sth = $dbh->prepare("SELECT * FROM bookshelf WHERE shelfnumber = ?");
443 $sth->execute($shelfnumber);
444 my $result = $sth->fetchrow_hashref;
446 if ($result->{'owner'} == $owner) {
447 $result->{'canmanage'} = 1;
450 my $sth = $dbh->prepare("SELECT id_career FROM bookshelves_careers WHERE shelfnumber = ?");
451 $sth->execute($shelfnumber);
453 while (my $row = $sth->fetchrow) {
456 $result->{'careers'} = \@careers;
460 sub GetShelfContentsExt {
461 my ($shelfnumber) = @_;
462 my $sth = $dbh->prepare("SELECT biblionumber FROM shelfcontents WHERE shelfnumber = ? ORDER BY biblionumber");
463 $sth->execute($shelfnumber);
466 while (my ($biblionumber) = $sth->fetchrow) {
467 my $biblio=ZEBRA_readyXML_noheader($dbh,$biblionumber);
468 push @biblios,$biblio;
470 my (@results)=parsefields($dbh,"opac",@biblios);
475 sub RemoveFromShelfExt {
476 my ($biblionumber, $shelfnumber) = @_;
477 my $sth = $dbh->prepare("DELETE FROM shelfcontents WHERE shelfnumber = ? AND biblionumber = ?");
478 $sth->execute($shelfnumber,$biblionumber);
482 my ($biblionumber, $shelfnumber) = @_;
483 my $sth = $dbh->prepare("SELECT * FROM shelfcontents WHERE shelfnumber = ? AND biblionumber = ?");
484 $sth->execute($shelfnumber, $biblionumber);
488 $sth = $dbh->prepare("INSERT INTO shelfcontents (shelfnumber, biblionumber) VALUES (?, ?)");
489 $sth->execute($shelfnumber, $biblionumber);
493 sub AddRequestToShelf {
494 my ($shelfnumber, $requestType, $requestName, $comments) = @_;
495 my $sth = $dbh->prepare("INSERT INTO shelf_requests (shelfnumber, request_name, request_type, status, request_date, comments) VALUES (?,?,?,?, CURRENT_DATE(),?)");
496 $sth->execute($shelfnumber, $requestName, $requestType, "PENDING", $comments);
497 return $dbh->{'mysql_insertid'};
500 sub CountShelfRequest {
501 my ($shelfnumber, $status) = @_;
504 $sth = $dbh->prepare("SELECT count(idRequest) FROM shelf_requests WHERE shelfnumber = ? AND status = ?");
505 $sth->execute($shelfnumber, $status);
507 $sth = $dbh->prepare("SELECT count(idRequest) FROM shelf_requests WHERE status = ?");
508 $sth->execute($status);
510 my ($count) = $sth->fetchrow_array;
514 sub GetShelfRequests {
515 my ($shelfnumber, $status, $type) = @_;
517 my $query = "SELECT * FROM shelf_requests SR INNER JOIN bookshelf BS ON SR.shelfnumber = BS.shelfnumber WHERE status = ?";
518 push @params, $status;
520 $query.= " AND shelfnumber = ?";
521 push @params, $shelfnumber;
524 $query.= " AND request_type = ?";
527 $query.= " ORDER BY SR.shelfnumber, SR.request_date";
528 my $sth = $dbh->prepare($query);
529 $sth->execute(@params);
533 while (my $row = $sth->fetchrow_hashref) {
534 my $borrdata = borrdata('',$row->{'owner'});
535 $row->{'surname'} = $borrdata->{'surname'};
536 $row->{'firstname'} = $borrdata->{'firstname'};
537 $row->{'cardnumber'} = $borrdata->{'cardnumber'};
538 $row->{'request_date'} = format_date($row->{'request_date'});
539 $row->{$row->{'request_type'}} = 1;
540 $row->{$row->{'status'}} = 1;
541 $row->{'color'} = $color = not $color;
547 sub RejectShelfRequest {
548 my ($idRequest) = @_;
549 #get the type and name request
550 my $sth = $dbh->prepare("SELECT request_type, request_name FROM shelf_requests WHERE idRequest = ?");
551 $sth->execute($idRequest);
552 my ($request_type, $request_name) = $sth->fetchrow_array;
553 #if the request is a file, then unlink the file
554 if ($request_type eq 'file') {
555 unlink($ENV{'DOCUMENT_ROOT'}."/uploaded-files/shelf-files/$idRequest-$request_name");
557 #change tha request status to REJECTED
558 $sth = $dbh->prepare("UPDATE shelf_requests SET status = ? WHERE idRequest = ?");
559 $sth->execute("REJECTED", $idRequest);
563 sub GetShelfRequestOwner {
564 my ($idRequest) = @_;
565 my $sth = $dbh->prepare("SELECT owner FROM shelf_requests R INNER JOIN bookshelf S ON R.shelfnumber = S.shelfnumber WHERE idRequest = ?");
566 $sth->execute($idRequest);
567 my ($owner) = $sth->fetchrow_array;
568 my $bordata = &borrdata(undef, $owner);
569 #print "Content-type: text/plain \n\n --- $owner ----- $bordata->{'emailaddress'}" ;
573 sub GetShelfRequest {
574 my ($idRequest) = @_;
575 my $sth = $dbh->prepare("SELECT * FROM shelf_requests R INNER JOIN bookshelf S ON R.shelfnumber = S.shelfnumber WHERE idRequest = ?");
576 $sth->execute($idRequest);
577 my $request_data = $sth->fetchrow_hashref;
578 return $request_data;
581 sub CatalogueShelfRequest {
582 my ($idRequest, $shelfnumber, $biblionumber) = @_;
583 #find the last request status
584 my $sth = $dbh->prepare("SELECT status, biblionumber FROM shelf_requests WHERE idRequest = ?");
585 $sth->execute($idRequest);
586 my ($prev_status, $prev_biblionumber) = $sth->fetchrow_array;
587 #if the status was not seted, inserts an entry in shelfcontents
588 if ($prev_status ne "CATALOGUED") {
589 $sth = $dbh->prepare("INSERT INTO shelfcontents (shelfnumber, biblionumber) VALUES (?,?)");
590 $sth->execute($shelfnumber, $biblionumber);
591 #if the request was previously catalogued, delete the entry in shelfcontens
592 } elsif ($prev_status ne "REJECTED") {
593 $sth = $dbh->prepare("DELETE FROM shelfcontents WHERE shelfnumber = ? AND biblionumber = ?");
594 $sth->execute($shelfnumber, $prev_biblionumber);
596 #change the status to catalogued
597 $sth = $dbh->prepare("UPDATE shelf_requests SET status = ?, biblionumber = ? WHERE idRequest = ?");
598 $sth->execute("CATALOGUED", $biblionumber, $idRequest);
602 END { } # module clean-up code here (global destructor)
608 # Revision 1.17 2006/09/01 22:16:00 tgarip1957
610 # Event & Net::Z3950 dependency removed
611 # HTML::Template::Pro dependency added
613 # Revision 1.13 2004/03/11 16:06:20 tipaul
614 # *** empty log message ***
616 # Revision 1.11.2.2 2004/02/19 10:15:41 tipaul
617 # new feature : adding book to bookshelf from biblio detail screen.
619 # Revision 1.11.2.1 2004/02/06 14:16:55 tipaul
620 # fixing bugs in bookshelves management.
622 # Revision 1.11 2003/12/15 10:57:08 slef
623 # DBI call fix for bug 662
625 # Revision 1.10 2003/02/05 10:05:02 acli
626 # Converted a few SQL statements to use ? to fix a few strange SQL errors
627 # Noted correct tab size
629 # Revision 1.9 2002/10/13 08:29:18 arensb
630 # Deleted unused variables.
631 # Removed trailing whitespace.
633 # Revision 1.8 2002/10/10 04:32:44 arensb
634 # Simplified references.
636 # Revision 1.7 2002/10/05 09:50:10 arensb
637 # Merged with arensb-context branch: use C4::Context->dbh instead of
638 # &C4Connect, and generally prefer C4::Context over C4::Database.
640 # Revision 1.6.2.1 2002/10/04 02:24:43 arensb
641 # Use C4::Connect instead of C4::Database, C4::Connect->dbh instead
644 # Revision 1.6 2002/09/23 13:50:30 arensb
645 # Fixed missing bit in POD.
647 # Revision 1.5 2002/09/22 17:29:17 arensb
649 # Added some FIXME comments.
650 # Removed useless trailing whitespace.
652 # Revision 1.4 2002/08/14 18:12:51 tonnesen
653 # Added copyright statement to all .pl and .pm files
655 # Revision 1.3 2002/07/02 17:48:06 tonnesen
656 # Merged in updates from rel-1-2
658 # Revision 1.2.2.1 2002/06/26 20:46:48 tonnesen
659 # Inserting some changes I made locally a while ago.
669 Koha Developement team <info@koha.org>
673 C4::Circulation::Circ2(3)