2 # Please use 8-character tabs for this file (indents are every 4 characters)
4 package C4::VirtualShelves;
7 # Copyright 2000-2002 Katipo Communications
9 # This file is part of Koha.
11 # Koha is free software; you can redistribute it and/or modify it under the
12 # terms of the GNU General Public License as published by the Free Software
13 # Foundation; either version 2 of the License, or (at your option) any later
16 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
17 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
18 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
20 # You should have received a copy of the GNU General Public License along with
21 # Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
22 # Suite 330, Boston, MA 02111-1307 USA
34 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK);
37 # set the version for version checking
42 &GetShelves &GetShelfContents &GetShelf
43 &AddToShelf &AddToShelfFromBiblio &AddShelf
46 &DelFromShelf &DelShelf
49 &GetShelvesSummary &GetRecentShelves
50 &RefreshShelvesSummary &SetShelvesLimit
55 my $dbh = C4::Context->dbh;
59 C4::VirtualShelves - Functions for manipulating Koha virtual virtualshelves
63 use C4::VirtualShelves;
67 This module provides functions for manipulating virtual virtualshelves,
68 including creating and deleting virtualshelves, and adding and removing
69 items to and from virtualshelves.
77 ($shelflist, $totshelves) = &GetShelves($mincategory, $row_count, $offset, $owner);
78 ($shelfnumber, $shelfhash) = each %{$shelflist};
80 Returns the number of shelves specified by C<$row_count> and C<$offset> as well as the total
81 number of shelves that meet the C<$owner> and C<$mincategory> criteria. C<$mincategory>,
82 C<$row_count>, and C<$offset> are required. C<$owner> must be supplied when C<$mincategory> == 1.
83 When C<$mincategory> is 2 or 3, supply undef as argument for C<$owner>.
84 C<$shelflist>is a reference-to-hash. The keys are the virtualshelves numbers (C<$shelfnumber>, above),
85 and the values (C<$shelfhash>, above) are themselves references-to-hash, with the following keys:
89 =item C<$shelfhash-E<gt>{shelfname}>
91 A string. The name of the shelf.
93 =item C<$shelfhash-E<gt>{count}>
95 The number of virtuals on that virtualshelves.
101 sub GetShelves ($$$$) {
102 my ($mincategory, $row_count, $offset, $owner) = @_;
103 my @params = ($owner, $mincategory, ($offset ? $offset : 0), $row_count);
104 my @params1 = ($owner, $mincategory);
105 if ($mincategory > 1) {
109 my $total = _shelf_count($owner, $mincategory);
110 # grab only the shelves meeting the row_count/offset spec...
112 SELECT virtualshelves.shelfnumber, virtualshelves.shelfname,owner,surname,firstname,virtualshelves.category,virtualshelves.sortfield,
113 count(virtualshelfcontents.biblionumber) as count
115 LEFT JOIN virtualshelfcontents ON virtualshelves.shelfnumber = virtualshelfcontents.shelfnumber
116 LEFT JOIN borrowers ON virtualshelves.owner = borrowers.borrowernumber );
117 $query .= ($mincategory == 1) ? "WHERE owner=? AND category=?" : "WHERE category>=?";
119 GROUP BY virtualshelves.shelfnumber
120 ORDER BY virtualshelves.category
123 my $sth2 = $dbh->prepare($query);
124 $sth2->execute(@params);
126 while ( my ( $shelfnumber, $shelfname, $owner, $surname,
127 $firstname, $category, $sortfield, $count ) = $sth2->fetchrow ) {
128 $shelflist{$shelfnumber}->{'shelfname'} = $shelfname;
129 $shelflist{$shelfnumber}->{'count'} = $count;
130 $shelflist{$shelfnumber}->{'sortfield'} = $sortfield;
131 $shelflist{$shelfnumber}->{'category'} = $category;
132 $shelflist{$shelfnumber}->{'owner'} = $owner;
133 $shelflist{$shelfnumber}->{'surname'} = $surname;
134 $shelflist{$shelfnumber}->{'firstname'} = $firstname;
136 return ( \%shelflist, $total );
139 =item GetShelvesSummary
141 ($shelves, $total) = GetShelvesSummary($mincategory, $row_count, $offset, $owner)
143 Returns the number of shelves specified by C<$row_count> and C<$offset> as well as the total
144 number of shelves that meet the C<$owner> and/or C<$mincategory> criteria. C<$mincategory>,
145 C<$row_count>, and C<$offset> are required. C<$owner> must be supplied when C<$mincategory> == 1.
146 When C<$mincategory> is 2 or 3, supply undef as argument for C<$owner>.
150 sub GetShelvesSummary ($$$$) {
151 my ($mincategory, $row_count, $offset, $owner) = @_;
152 my @params = ($owner, $mincategory, ($offset ? $offset : 0), $row_count);
153 my @params1 = ($owner, $mincategory);
154 if ($mincategory > 1) {
158 my $total = _shelf_count($owner, $mincategory);
159 # grab only the shelves meeting the row_count/offset spec...
162 virtualshelves.shelfnumber,
163 virtualshelves.shelfname,
165 CONCAT(firstname, ' ', surname) AS name,
166 virtualshelves.category,
167 count(virtualshelfcontents.biblionumber) AS count
169 LEFT JOIN virtualshelfcontents ON virtualshelves.shelfnumber = virtualshelfcontents.shelfnumber
170 LEFT JOIN borrowers ON virtualshelves.owner = borrowers.borrowernumber );
171 $query .= ($mincategory == 1) ? "WHERE owner=? AND category=?" : "WHERE category>=?";
173 GROUP BY virtualshelves.shelfnumber
174 ORDER BY virtualshelves.category
177 my $sth2 = $dbh->prepare($query);
178 $sth2->execute(@params);
179 my $shelves = $sth2->fetchall_arrayref({});
180 return ($shelves, $total);
182 # Probably NOT the final implementation since it is still bulky (repeated hash keys).
183 # might like an array of rows of delimited values:
184 # 1|2||0|blacklist|112
185 # 2|6|Josh Ferraro|51|en_fuego|106
188 =item GetRecentShelves
190 ($shelflist) = GetRecentShelves(1, $limit, $owner)
192 This function returns a references to an array of hashrefs containing specified shelves sorted
193 by the date the shelf was last modified in descending order limited to the number of records
194 specified by C<$row_count>. If calling with C<$mincategory> other than 1, use undef as C<$owner>.
196 This function is intended to return a dataset reflecting the most recently active shelves for
197 the submitted parameters.
201 sub GetRecentShelves ($$$) {
202 my ($mincategory, $row_count, $owner) = @_;
204 my $total = _shelf_count($owner, $mincategory);
205 my @params = ($owner, $mincategory, 0, $row_count); #FIXME: offset is hardcoded here, but could be passed in for enhancements
206 shift @params if !$owner;
207 my $query = "SELECT * FROM virtualshelves";
208 $query .= ($owner ? " WHERE owner = ? AND category = ?" : " WHERE category >= ? ");
209 $query .= " ORDER BY lastmodified DESC LIMIT ?, ?";
210 my $sth = $dbh->prepare($query);
211 $sth->execute(@params);
212 @shelflist = $sth->fetchall_arrayref({});
213 return ( \@shelflist, $total );
218 (shelfnumber,shelfname,owner,category,sortfield) = &GetShelf($shelfnumber);
220 Looks up information about the contents of virtual virtualshelves number
223 Returns the database's information on 'virtualshelves' table.
228 my ($shelfnumber) = @_;
230 SELECT shelfnumber, shelfname, owner, category, sortfield
234 my $sth = $dbh->prepare($query);
235 $sth->execute($shelfnumber);
236 return $sth->fetchrow;
239 =item GetShelfContents
241 $itemlist = &GetShelfContents($shelfnumber);
243 Looks up information about the contents of virtual virtualshelves number
244 C<$shelfnumber>. Sorted by a field in the biblio table. copyrightdate
247 Returns a reference-to-array, whose elements are references-to-hash,
248 as returned by C<C4::Biblio::GetBiblioFromItemNumber>.
250 Note: the notforloan status comes from the itemtype, and where it equals 0
251 it does not ensure that related items.notforloan status is likewise 0. The
252 caller has to check any items on their own, possibly with CanBookBeIssued
253 from C4::Circulation.
257 sub GetShelfContents ($;$$$) {
258 my ($shelfnumber, $row_count, $offset, $sortfield) = @_;
259 my $dbh=C4::Context->dbh();
260 my $sth1 = $dbh->prepare("SELECT count(*) FROM virtualshelfcontents WHERE shelfnumber = ?");
261 $sth1->execute($shelfnumber);
262 my $total = $sth1->fetchrow;
264 my $sth2 = $dbh->prepare('SELECT sortfield FROM virtualshelves WHERE shelfnumber=?');
265 $sth2->execute($shelfnumber);
266 ($sortfield) = $sth2->fetchrow_array;
269 " SELECT vc.biblionumber, vc.shelfnumber, vc.dateadded, itemtypes.*,
270 biblio.*, biblioitems.itemtype, biblioitems.publicationyear
271 FROM virtualshelfcontents vc
272 LEFT JOIN biblio ON vc.biblionumber = biblio.biblionumber
273 LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber
274 LEFT JOIN itemtypes ON biblioitems.itemtype = itemtypes.itemtype
275 WHERE vc.shelfnumber=? ";
276 my @params = ($shelfnumber);
278 $query .= " ORDER BY ? ";
279 $query .= " DESC " if ($sortfield eq 'copyrightdate');
280 push (@params, $sortfield);
283 $query .= " LIMIT ?, ? ";
284 push (@params, ($offset ? $offset : 0));
285 push (@params, $row_count);
287 my $sth3 = $dbh->prepare($query);
288 $sth3->execute(@params);
289 return ($sth3->fetchall_arrayref({}), $total);
290 # Like the perldoc says,
291 # returns reference-to-array, where each element is reference-to-hash of the row:
292 # like [ $sth->fetchrow_hashref(), $sth->fetchrow_hashref() ... ]
293 # Suitable for use in TMPL_LOOP.
294 # See http://search.cpan.org/~timb/DBI-1.601/DBI.pm#fetchall_arrayref
295 # or newer, for your version of DBI.
300 $shelfnumber = &AddShelf( $shelfname, $owner, $category);
302 Creates a new virtual virtualshelves with name C<$shelfname>, owner C<$owner> and category
305 Returns a code to know what's happen.
306 * -1 : if this virtualshelves already exist.
307 * $shelfnumber : if success.
312 my ( $shelfname, $owner, $category, $sortfield ) = @_;
316 WHERE shelfname=? AND owner=?
318 my $sth = $dbh->prepare($query);
319 $sth->execute($shelfname,$owner);
320 ( $sth->rows ) and return (-1);
322 INSERT INTO virtualshelves
323 (shelfname,owner,category,sortfield)
326 $sth = $dbh->prepare($query);
327 $sth->execute( $shelfname, $owner, $category, $sortfield );
328 my $shelfnumber = $dbh->{'mysql_insertid'};
329 return ($shelfnumber);
334 &AddToShelf($biblionumber, $shelfnumber);
336 Adds item number C<$biblionumber> to virtual virtualshelves number
337 C<$shelfnumber>, unless that item is already on that shelf.
343 my ( $biblionumber, $shelfnumber ) = @_;
344 return unless $biblionumber;
347 FROM virtualshelfcontents
348 WHERE shelfnumber=? AND biblionumber=?
350 my $sth = $dbh->prepare($query);
352 $sth->execute( $shelfnumber, $biblionumber );
353 ($sth->rows) and return undef; # already on shelf
355 INSERT INTO virtualshelfcontents
356 (shelfnumber, biblionumber, flags)
360 $sth = $dbh->prepare($query);
361 $sth->execute( $shelfnumber, $biblionumber );
362 $query = qq(UPDATE virtualshelves
363 SET lastmodified = CURRENT_TIMESTAMP
364 WHERE shelfnumber = ?);
365 $sth = $dbh->prepare($query);
366 $sth->execute( $shelfnumber );
369 =item AddToShelfFromBiblio
371 &AddToShelfFromBiblio($biblionumber, $shelfnumber)
373 this function allow to add a virtual into the shelf number $shelfnumber
378 sub AddToShelfFromBiblio {
379 my ( $biblionumber, $shelfnumber ) = @_;
380 return unless $biblionumber;
383 FROM virtualshelfcontents
384 WHERE shelfnumber=? AND biblionumber=?
386 my $sth = $dbh->prepare($query);
387 $sth->execute( $shelfnumber, $biblionumber );
388 unless ( $sth->rows ) {
390 INSERT INTO virtualshelfcontents
391 (shelfnumber, biblionumber, flags)
395 $sth = $dbh->prepare($query);
396 $sth->execute( $shelfnumber, $biblionumber );
397 $query = qq(UPDATE virtualshelves
398 SET lastmodified = CURRENT_TIMESTAMP
399 WHERE shelfnumber = ?);
400 $sth = $dbh->prepare($query);
401 $sth->execute( $shelfnumber );
407 ModShelf($shelfnumber, $hashref)
409 Where $hashref->{column} = param
411 Modify the value into virtualshelves table with values given
412 from hashref, which each key of the hashref should be
413 the name of a column of virtualshelves.
418 my $shelfnumber = shift;
421 if (exists $shelf->{shelfnumber}) {
422 carp "Should not use ModShelf to change shelfnumber";
425 unless (defined $shelfnumber and $shelfnumber =~ /^\d+$/) {
426 carp "Invalid shelfnumber passed to ModShelf: $shelfnumber";
430 my $query = "UPDATE virtualshelves SET ";
431 my @bind_params = ();
432 my @set_clauses = ();
434 foreach my $column (keys %$shelf) {
435 push @set_clauses, "$column = ?";
436 push @bind_params, $shelf->{$column};
439 if ($#set_clauses == -1) {
440 carp "No columns to update passed to ModShelf";
443 $query .= join(", ", @set_clauses);
445 $query .= " WHERE shelfnumber = ? ";
446 push @bind_params, $shelfnumber;
448 $debug and warn "ModShelf query:\n $query\n",
449 "ModShelf query args: ", join(',', @bind_params), "\n";
450 my $sth = $dbh->prepare($query);
451 $sth->execute( @bind_params );
454 =item ShelfPossibleAction
456 ShelfPossibleAction($loggedinuser, $shelfnumber, $action);
458 C<$loggedinuser,$shelfnumber,$action>
460 $action can be "view" or "manage".
462 Returns 1 if the user can do the $action in the $shelfnumber shelf.
467 sub ShelfPossibleAction {
468 my ( $user, $shelfnumber, $action ) = @_;
470 SELECT owner,category
474 my $sth = $dbh->prepare($query);
475 $sth->execute($shelfnumber);
476 my ( $owner, $category ) = $sth->fetchrow;
477 my $borrower = GetMemberDetails($user);
478 return 1 if ( $category >= 3); # open list
479 return 1 if (($category >= 2) and
480 defined($action) and $action eq 'view'); # public list, anybody can view
481 return 1 if (($category >= 2) and defined($user) and ($borrower->{authflags}->{superlibrarian} || $user == 0)); # public list, superlibrarian can edit/delete
482 return 1 if (defined($user) and $owner eq $user ); # user owns this list. Check last.
488 &DelFromShelf( $biblionumber, $shelfnumber);
490 Removes item number C<$biblionumber> from virtual virtualshelves number
491 C<$shelfnumber>. If the item wasn't on that virtualshelves to begin with,
498 my ( $biblionumber, $shelfnumber ) = @_;
500 DELETE FROM virtualshelfcontents
501 WHERE shelfnumber=? AND biblionumber=?
503 my $sth = $dbh->prepare($query);
504 $sth->execute( $shelfnumber, $biblionumber );
507 =item DelShelf (old version)
509 ($status, $msg) = &DelShelf($shelfnumber);
511 Deletes virtual virtualshelves number C<$shelfnumber>. The virtualshelves must
514 Returns a two-element array, where C<$status> is 0 if the operation
515 was successful, or non-zero otherwise. C<$msg> is "Done" in case of
516 success, or an error message giving the reason for failure.
518 =item DelShelf (current version)
520 $Number = DelShelf($shelfnumber);
522 This function deletes the shelf number, and all of it's content.
528 carp "DelShelf called without valid argument (shelfnumber)";
531 my $sth = $dbh->prepare("DELETE FROM virtualshelves WHERE shelfnumber=?");
532 return $sth->execute(shift);
535 =item RefreshShelvesSummary
537 ($total, $pubshelves, $barshelves) = RefreshShelvesSummary($sessionID, $loggedinuser, $row_count);
539 Updates the current session and userenv with the most recent shelves
541 Returns the total number of shelves stored in the session/userenv along with two references each to an
542 array of hashes, one containing the C<$loggedinuser>'s private shelves and one containing all public/open shelves.
544 This function is used in conjunction with the 'Lists' button in masthead.inc.
548 sub RefreshShelvesSummary ($$$) {
550 my ($sessionID, $loggedinuser, $row_count) = @_;
551 my $session = C4::Auth::get_session($sessionID);
552 my ($total, $totshelves, $barshelves, $pubshelves);
554 ($barshelves, $totshelves) = GetRecentShelves(1, $row_count, $loggedinuser);
555 $total->{'bartotal'} = $totshelves;
556 ($pubshelves, $totshelves) = GetRecentShelves(2, $row_count, undef);
557 $total->{'pubtotal'} = $totshelves;
559 # Update the current session with the latest shelves...
560 $session->param('barshelves', $barshelves->[0]);
561 $session->param('pubshelves', $pubshelves->[0]);
562 $session->param('totshelves', $total);
564 # likewise the userenv...
565 C4::Context->set_shelves_userenv('bar',$barshelves->[0]);
566 C4::Context->set_shelves_userenv('pub',$pubshelves->[0]);
567 C4::Context::set_shelves_userenv('tot',$total);
569 return ($total, $pubshelves, $barshelves);
574 sub _shelf_count ($$) {
576 # Find out how many shelves total meet the submitted criteria...
577 my $query = "SELECT count(*) FROM virtualshelves";
578 $query .= ($params[1] > 1) ? " WHERE category >= ?" : " WHERE owner=? AND category=?";
579 shift @params if $params[1] > 1;
580 my $sth = $dbh->prepare($query);
581 $sth->execute(@params);
582 my $total = $sth->fetchrow;
594 Koha Developement team <info@koha.org>
598 C4::Circulation::Circ2(3)