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
21 # with Koha; if not, write to the Free Software Foundation, Inc.,
22 # 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
34 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK);
37 # set the version for version checking
42 &GetShelves &GetShelfContents &GetShelf
46 &DelFromShelf &DelShelf
50 &GetShelvesSummary &GetRecentShelves
51 &RefreshShelvesSummary &SetShelvesLimit
56 my $dbh = C4::Context->dbh;
60 C4::VirtualShelves - Functions for manipulating Koha virtual virtualshelves
64 use C4::VirtualShelves;
68 This module provides functions for manipulating virtual virtualshelves,
69 including creating and deleting virtualshelves, and adding and removing
70 bibs to and from virtualshelves.
76 ($shelflist, $totshelves) = &GetShelves($mincategory, $row_count, $offset, $owner);
77 ($shelfnumber, $shelfhash) = each %{$shelflist};
79 Returns the number of shelves specified by C<$row_count> and C<$offset> as well as the total
80 number of shelves that meet the C<$owner> and C<$mincategory> criteria. C<$mincategory>,
81 C<$row_count>, and C<$offset> are required. C<$owner> must be supplied when C<$mincategory> == 1.
82 When C<$mincategory> is 2 or 3, supply undef as argument for C<$owner>.
83 C<$shelflist>is a reference-to-hash. The keys are the virtualshelves numbers (C<$shelfnumber>, above),
84 and the values (C<$shelfhash>, above) are themselves references-to-hash, with the following keys:
88 =item C<$shelfhash-E<gt>{shelfname}>
90 A string. The name of the shelf.
92 =item C<$shelfhash-E<gt>{count}>
94 The number of virtuals on that virtualshelves.
100 sub GetShelves ($$$$) {
101 my ($mincategory, $row_count, $offset, $owner) = @_;
102 my @params = ($owner, $mincategory, ($offset ? $offset : 0), $row_count);
103 my @params1 = ($owner, $mincategory);
104 if ($mincategory > 1) {
108 my $total = _shelf_count($owner, $mincategory);
109 # grab only the shelves meeting the row_count/offset spec...
111 SELECT virtualshelves.shelfnumber, virtualshelves.shelfname,owner,surname,firstname,virtualshelves.category,virtualshelves.sortfield,
112 count(virtualshelfcontents.biblionumber) as count
114 LEFT JOIN virtualshelfcontents ON virtualshelves.shelfnumber = virtualshelfcontents.shelfnumber
115 LEFT JOIN borrowers ON virtualshelves.owner = borrowers.borrowernumber );
116 $query .= ($mincategory == 1) ? "WHERE owner=? AND category=?" : "WHERE category>=?";
118 GROUP BY virtualshelves.shelfnumber
119 ORDER BY virtualshelves.shelfname
121 my $sth2 = $dbh->prepare($query);
122 $sth2->execute(@params);
124 while ( my ( $shelfnumber, $shelfname, $owner, $surname,
125 $firstname, $category, $sortfield, $count ) = $sth2->fetchrow ) {
126 $shelflist{$shelfnumber}->{'shelfname'} = $shelfname;
127 $shelflist{$shelfnumber}->{'count'} = $count;
128 $shelflist{$shelfnumber}->{'sortfield'} = $sortfield;
129 $shelflist{$shelfnumber}->{'category'} = $category;
130 $shelflist{$shelfnumber}->{'owner'} = $owner;
131 $shelflist{$shelfnumber}->{'surname'} = $surname;
132 $shelflist{$shelfnumber}->{'firstname'} = $firstname;
134 return ( \%shelflist, $total );
137 =head2 GetShelvesSummary
139 ($shelves, $total) = GetShelvesSummary($mincategory, $row_count, $offset, $owner)
141 Returns the number of shelves specified by C<$row_count> and C<$offset> as well as the total
142 number of shelves that meet the C<$owner> and/or C<$mincategory> criteria. C<$mincategory>,
143 C<$row_count>, and C<$offset> are required. C<$owner> must be supplied when C<$mincategory> == 1.
144 When C<$mincategory> is 2 or 3, supply undef as argument for C<$owner>.
148 sub GetShelvesSummary ($$$$) {
149 my ($mincategory, $row_count, $offset, $owner) = @_;
150 my @params = ($owner, $mincategory, ($offset ? $offset : 0), $row_count);
151 my @params1 = ($owner, $mincategory);
152 if ($mincategory > 1) {
156 my $total = _shelf_count($owner, $mincategory);
157 # grab only the shelves meeting the row_count/offset spec...
160 virtualshelves.shelfnumber,
161 virtualshelves.shelfname,
163 CONCAT(firstname, ' ', surname) AS name,
164 virtualshelves.category,
165 count(virtualshelfcontents.biblionumber) AS count
167 LEFT JOIN virtualshelfcontents ON virtualshelves.shelfnumber = virtualshelfcontents.shelfnumber
168 LEFT JOIN borrowers ON virtualshelves.owner = borrowers.borrowernumber );
169 $query .= ($mincategory == 1) ? "WHERE owner=? AND category=?" : "WHERE category>=?";
171 GROUP BY virtualshelves.shelfnumber
172 ORDER BY virtualshelves.category
175 my $sth2 = $dbh->prepare($query);
176 $sth2->execute(@params);
177 my $shelves = $sth2->fetchall_arrayref({});
178 return ($shelves, $total);
180 # Probably NOT the final implementation since it is still bulky (repeated hash keys).
181 # might like an array of rows of delimited values:
182 # 1|2||0|blacklist|112
183 # 2|6|Josh Ferraro|51|en_fuego|106
186 =head2 GetRecentShelves
188 ($shelflist) = GetRecentShelves(1, $limit, $owner)
190 This function returns a references to an array of hashrefs containing specified shelves sorted
191 by the date the shelf was last modified in descending order limited to the number of records
192 specified by C<$row_count>. If calling with C<$mincategory> other than 1, use undef as C<$owner>.
194 This function is intended to return a dataset reflecting the most recently active shelves for
195 the submitted parameters.
199 sub GetRecentShelves ($$$) {
200 my ($mincategory, $row_count, $owner) = @_;
202 my $total = _shelf_count($owner, $mincategory);
203 my @params = ($owner, $mincategory, 0, $row_count); #FIXME: offset is hardcoded here, but could be passed in for enhancements
204 shift @params if (not defined $owner);
205 my $query = "SELECT * FROM virtualshelves";
206 $query .= ((defined $owner) ? " WHERE owner = ? AND category = ?" : " WHERE category >= ? ");
207 $query .= " ORDER BY lastmodified DESC LIMIT ?, ?";
208 my $sth = $dbh->prepare($query);
209 $sth->execute(@params);
210 @shelflist = $sth->fetchall_arrayref({});
211 return ( \@shelflist, $total );
216 (shelfnumber,shelfname,owner,category,sortfield) = &GetShelf($shelfnumber);
218 Looks up information about the contents of virtual virtualshelves number
221 Returns the database's information on 'virtualshelves' table.
226 my ($shelfnumber) = @_;
228 SELECT shelfnumber, shelfname, owner, category, sortfield
232 my $sth = $dbh->prepare($query);
233 $sth->execute($shelfnumber);
234 return $sth->fetchrow;
237 =head2 GetShelfContents
239 $biblist = &GetShelfContents($shelfnumber);
241 Looks up information about the contents of virtual virtualshelves number
242 C<$shelfnumber>. Sorted by a field in the biblio table. copyrightdate
245 Returns a reference-to-array, whose elements are references-to-hash,
246 as returned by C<C4::Biblio::GetBiblioFromItemNumber>.
248 Note: the notforloan status comes from the itemtype, and where it equals 0
249 it does not ensure that related items.notforloan status is likewise 0. The
250 caller has to check any items on their own, possibly with CanBookBeIssued
251 from C4::Circulation.
255 sub GetShelfContents ($;$$$) {
256 my ($shelfnumber, $row_count, $offset, $sortfield) = @_;
257 my $dbh=C4::Context->dbh();
258 my $sth1 = $dbh->prepare("SELECT count(*) FROM virtualshelfcontents WHERE shelfnumber = ?");
259 $sth1->execute($shelfnumber);
260 my $total = $sth1->fetchrow;
262 my $sth2 = $dbh->prepare('SELECT sortfield FROM virtualshelves WHERE shelfnumber=?');
263 $sth2->execute($shelfnumber);
264 ($sortfield) = $sth2->fetchrow_array;
267 " SELECT vc.biblionumber, vc.shelfnumber, vc.dateadded, itemtypes.*,
268 biblio.*, biblioitems.itemtype, biblioitems.publicationyear
269 FROM virtualshelfcontents vc
270 LEFT JOIN biblio ON vc.biblionumber = biblio.biblionumber
271 LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber
272 LEFT JOIN itemtypes ON biblioitems.itemtype = itemtypes.itemtype
273 WHERE vc.shelfnumber=? ";
274 my @params = ($shelfnumber);
276 $query .= " ORDER BY " . $sortfield;
277 $query .= " DESC " if ($sortfield eq 'copyrightdate');
280 $query .= " LIMIT ?, ? ";
281 push (@params, ($offset ? $offset : 0));
282 push (@params, $row_count);
284 my $sth3 = $dbh->prepare($query);
285 $sth3->execute(@params);
286 return ($sth3->fetchall_arrayref({}), $total);
287 # Like the perldoc says,
288 # returns reference-to-array, where each element is reference-to-hash of the row:
289 # like [ $sth->fetchrow_hashref(), $sth->fetchrow_hashref() ... ]
290 # Suitable for use in TMPL_LOOP.
291 # See http://search.cpan.org/~timb/DBI-1.601/DBI.pm#fetchall_arrayref
292 # or newer, for your version of DBI.
297 $shelfnumber = &AddShelf( $shelfname, $owner, $category);
299 Creates a new virtual virtualshelves with name C<$shelfname>, owner C<$owner> and category
302 Returns a code to know what's happen.
303 * -1 : if this virtualshelves already exist.
304 * $shelfnumber : if success.
309 my ( $shelfname, $owner, $category, $sortfield ) = @_;
313 WHERE shelfname=? AND owner=?
315 my $sth = $dbh->prepare($query);
316 $sth->execute($shelfname,$owner);
317 ( $sth->rows ) and return (-1);
319 INSERT INTO virtualshelves
320 (shelfname,owner,category,sortfield)
323 $sth = $dbh->prepare($query);
324 $sth->execute( $shelfname, $owner, $category, $sortfield );
325 my $shelfnumber = $dbh->{'mysql_insertid'};
326 return ($shelfnumber);
331 &AddToShelf($biblionumber, $shelfnumber);
333 Adds bib number C<$biblionumber> to virtual virtualshelves number
334 C<$shelfnumber>, unless that bib is already on that shelf.
340 my ( $biblionumber, $shelfnumber ) = @_;
341 return unless $biblionumber;
344 FROM virtualshelfcontents
345 WHERE shelfnumber=? AND biblionumber=?
347 my $sth = $dbh->prepare($query);
349 $sth->execute( $shelfnumber, $biblionumber );
350 ($sth->rows) and return undef; # already on shelf
352 INSERT INTO virtualshelfcontents
353 (shelfnumber, biblionumber, flags)
357 $sth = $dbh->prepare($query);
358 $sth->execute( $shelfnumber, $biblionumber );
359 $query = qq(UPDATE virtualshelves
360 SET lastmodified = CURRENT_TIMESTAMP
361 WHERE shelfnumber = ?);
362 $sth = $dbh->prepare($query);
363 $sth->execute( $shelfnumber );
368 ModShelf($shelfnumber, $hashref)
370 Where $hashref->{column} = param
372 Modify the value into virtualshelves table with values given
373 from hashref, which each key of the hashref should be
374 the name of a column of virtualshelves.
379 my $shelfnumber = shift;
382 if (exists $shelf->{shelfnumber}) {
383 carp "Should not use ModShelf to change shelfnumber";
386 unless (defined $shelfnumber and $shelfnumber =~ /^\d+$/) {
387 carp "Invalid shelfnumber passed to ModShelf: $shelfnumber";
391 my $query = "UPDATE virtualshelves SET ";
392 my @bind_params = ();
393 my @set_clauses = ();
395 foreach my $column (keys %$shelf) {
396 push @set_clauses, "$column = ?";
397 push @bind_params, $shelf->{$column};
400 if ($#set_clauses == -1) {
401 carp "No columns to update passed to ModShelf";
404 $query .= join(", ", @set_clauses);
406 $query .= " WHERE shelfnumber = ? ";
407 push @bind_params, $shelfnumber;
409 $debug and warn "ModShelf query:\n $query\n",
410 "ModShelf query args: ", join(',', @bind_params), "\n";
411 my $sth = $dbh->prepare($query);
412 $sth->execute( @bind_params );
415 =head2 ShelfPossibleAction
417 ShelfPossibleAction($loggedinuser, $shelfnumber, $action);
419 C<$loggedinuser,$shelfnumber,$action>
421 $action can be "view" or "manage".
423 Returns 1 if the user can do the $action in the $shelfnumber shelf.
428 sub ShelfPossibleAction {
429 my ( $user, $shelfnumber, $action ) = @_;
431 SELECT owner,category
435 my $sth = $dbh->prepare($query);
436 $sth->execute($shelfnumber);
437 my ( $owner, $category ) = $sth->fetchrow;
438 my $borrower = GetMemberDetails($user);
439 return 0 if not defined($user);
440 return 1 if ( $category >= 3); # open list
441 return 1 if (($category >= 2) and
442 defined($action) and $action eq 'view'); # public list, anybody can view
443 return 1 if (($category >= 2) and defined($user) and ($borrower->{authflags}->{superlibrarian} || $user == 0)); # public list, superlibrarian can edit/delete
444 return 1 if (defined($user) and $owner eq $user ); # user owns this list. Check last.
450 &DelFromShelf( $biblionumber, $shelfnumber);
452 Removes bib number C<$biblionumber> from virtual virtualshelves number
453 C<$shelfnumber>. If the bib wasn't on that virtualshelves to begin with,
460 my ( $biblionumber, $shelfnumber ) = @_;
462 DELETE FROM virtualshelfcontents
463 WHERE shelfnumber=? AND biblionumber=?
465 my $sth = $dbh->prepare($query);
466 $sth->execute( $shelfnumber, $biblionumber );
469 =head2 DelShelf (old version)
471 ($status, $msg) = &DelShelf($shelfnumber);
473 Deletes virtual virtualshelves number C<$shelfnumber>. The virtualshelves must
476 Returns a two-element array, where C<$status> is 0 if the operation
477 was successful, or non-zero otherwise. C<$msg> is "Done" in case of
478 success, or an error message giving the reason for failure.
480 =head2 DelShelf (current version)
482 $Number = DelShelf($shelfnumber);
484 This function deletes the shelf number, and all of it's content.
490 carp "DelShelf called without valid argument (shelfnumber)";
493 my $sth = $dbh->prepare("DELETE FROM virtualshelves WHERE shelfnumber=?");
494 return $sth->execute(shift);
499 This finds all the public lists that this bib record is in.
503 sub GetBibliosShelves {
504 my ( $biblionumber ) = @_;
505 my $dbh = C4::Context->dbh;
506 my $sth = $dbh->prepare('
507 SELECT vs.shelfname, vs.shelfnumber
508 FROM virtualshelves vs
509 JOIN virtualshelfcontents vc ON (vs.shelfnumber= vc.shelfnumber)
510 WHERE vs.category != 1
511 AND vc.biblionumber= ?
513 $sth->execute( $biblionumber );
514 return $sth->fetchall_arrayref({});
517 =head2 RefreshShelvesSummary
519 ($total, $pubshelves, $barshelves) = RefreshShelvesSummary($sessionID, $loggedinuser, $row_count);
521 Updates the current session and userenv with the most recent shelves
523 Returns the total number of shelves stored in the session/userenv along with two references each to an
524 array of hashes, one containing the C<$loggedinuser>'s private shelves and one containing all public/open shelves.
526 This function is used in conjunction with the 'Lists' button in masthead.inc.
530 sub RefreshShelvesSummary ($$$) {
532 my ($sessionID, $loggedinuser, $row_count) = @_;
533 my $session = C4::Auth::get_session($sessionID);
534 my ($total, $totshelves, $barshelves, $pubshelves);
536 ($barshelves, $totshelves) = GetRecentShelves(1, $row_count, $loggedinuser);
537 $total->{'bartotal'} = $totshelves;
538 ($pubshelves, $totshelves) = GetRecentShelves(2, $row_count, undef);
539 $total->{'pubtotal'} = $totshelves;
541 # Update the current session with the latest shelves...
542 $session->param('barshelves', $barshelves->[0]);
543 $session->param('pubshelves', $pubshelves->[0]);
544 $session->param('totshelves', $total);
546 # likewise the userenv...
547 C4::Context->set_shelves_userenv('bar',$barshelves->[0]);
548 C4::Context->set_shelves_userenv('pub',$pubshelves->[0]);
549 C4::Context::set_shelves_userenv('tot',$total);
551 return ($total, $pubshelves, $barshelves);
556 sub _shelf_count ($$) {
558 # Find out how many shelves total meet the submitted criteria...
559 my $query = "SELECT count(*) FROM virtualshelves";
560 $query .= ($params[1] > 1) ? " WHERE category >= ?" : " WHERE owner=? AND category=?";
561 shift @params if $params[1] > 1;
562 my $sth = $dbh->prepare($query);
563 $sth->execute(@params);
564 my $total = $sth->fetchrow;
568 sub _biblionumber_sth {
570 my $query = 'select biblionumber from virtualshelfcontents where shelfnumber = ?';
571 my $dbh = C4::Context->dbh;
572 my $sth = $dbh->prepare($query)
574 $sth->execute( $shelf )
579 sub each_biblionumbers (&$) {
580 my ($code,$shelf) = @_;
581 my $ref = _biblionumber_sth($shelf)->fetchall_arrayref;
594 Koha Development Team <http://koha-community.org/>
598 C4::Circulation::Circ2(3)