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 &GetAllShelves
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 if($count eq 1){ $shelflist{$shelfnumber}->{'single'} = 1; }
129 $shelflist{$shelfnumber}->{'sortfield'} = $sortfield;
130 $shelflist{$shelfnumber}->{'category'} = $category;
131 $shelflist{$shelfnumber}->{'owner'} = $owner;
132 $shelflist{$shelfnumber}->{'surname'} = $surname;
133 $shelflist{$shelfnumber}->{'firstname'} = $firstname;
135 return ( \%shelflist, $total );
138 =head2 GetShelvesSummary
140 ($shelves, $total) = GetShelvesSummary($mincategory, $row_count, $offset, $owner)
142 Returns the number of shelves specified by C<$row_count> and C<$offset> as well as the total
143 number of shelves that meet the C<$owner> and/or C<$mincategory> criteria. C<$mincategory>,
144 C<$row_count>, and C<$offset> are required. C<$owner> must be supplied when C<$mincategory> == 1.
145 When C<$mincategory> is 2 or 3, supply undef as argument for C<$owner>.
149 sub GetShelvesSummary ($$$$) {
150 my ($mincategory, $row_count, $offset, $owner) = @_;
151 my @params = ($owner, $mincategory, ($offset ? $offset : 0), $row_count);
152 my @params1 = ($owner, $mincategory);
153 if ($mincategory > 1) {
157 my $total = _shelf_count($owner, $mincategory);
158 # grab only the shelves meeting the row_count/offset spec...
161 virtualshelves.shelfnumber,
162 virtualshelves.shelfname,
164 CONCAT(firstname, ' ', surname) AS name,
165 virtualshelves.category,
166 count(virtualshelfcontents.biblionumber) AS count
168 LEFT JOIN virtualshelfcontents ON virtualshelves.shelfnumber = virtualshelfcontents.shelfnumber
169 LEFT JOIN borrowers ON virtualshelves.owner = borrowers.borrowernumber );
170 $query .= ($mincategory == 1) ? "WHERE owner=? AND category=?" : "WHERE category>=?";
172 GROUP BY virtualshelves.shelfnumber
173 ORDER BY virtualshelves.category
176 my $sth2 = $dbh->prepare($query);
177 $sth2->execute(@params);
178 my $shelves = $sth2->fetchall_arrayref({});
179 return ($shelves, $total);
181 # Probably NOT the final implementation since it is still bulky (repeated hash keys).
182 # might like an array of rows of delimited values:
183 # 1|2||0|blacklist|112
184 # 2|6|Josh Ferraro|51|en_fuego|106
187 =head2 GetRecentShelves
189 ($shelflist, $total) = GetRecentShelves(1, $limit, $owner)
191 This function returns a reference to an array of hashrefs containing specified shelves sorted
192 by the date the shelf was last modified in descending order limited to the number of records
193 specified by C<$row_count>. If calling with C<$mincategory> other than 1, use undef as C<$owner>.
195 This function is intended to return a dataset reflecting the most recently active shelves for
196 the submitted parameters.
200 sub GetRecentShelves {
201 my ($mincategory, $row_count, $owner) = @_;
202 my $total = _shelf_count($owner, $mincategory);
205 if (defined $owner) {
206 @params = ($owner, $mincategory, $row_count);
207 $selection = ' WHERE owner = ? AND category = ?';
209 @params = ( $mincategory, $row_count);
210 $selection = ' WHERE category >= ? ';
212 my $query = 'SELECT * FROM virtualshelves';
213 $query .= $selection;
214 $query .= ' ORDER BY lastmodified DESC LIMIT ?';
215 my $sth = $dbh->prepare($query);
216 $sth->execute(@params);
217 my $shelflist = $sth->fetchall_arrayref({});
218 return ( $shelflist, $total );
223 ($shelflist) = GetAllShelves($owner)
225 This function returns a references to an array of hashrefs containing all shelves sorted
228 This function is intended to return a dataset reflecting all the shelves for
229 the submitted parameters.
233 sub GetAllShelves ($$) {
234 my ($category,$owner) = @_;
236 my @params = ($category,$owner);
237 my $query = "SELECT * FROM virtualshelves WHERE category = ? AND owner = ? ORDER BY shelfname ASC";
238 my $sth = $dbh->prepare($query);
239 $sth->execute(@params);
240 @shelflist = $sth->fetchall_arrayref({});
241 return ( \@shelflist );
246 (shelfnumber,shelfname,owner,category,sortfield) = &GetShelf($shelfnumber);
248 Looks up information about the contents of virtual virtualshelves number
251 Returns the database's information on 'virtualshelves' table.
256 my ($shelfnumber) = @_;
258 SELECT shelfnumber, shelfname, owner, category, sortfield
262 my $sth = $dbh->prepare($query);
263 $sth->execute($shelfnumber);
264 return $sth->fetchrow;
267 =head2 GetShelfContents
269 $biblist = &GetShelfContents($shelfnumber);
271 Looks up information about the contents of virtual virtualshelves number
272 C<$shelfnumber>. Sorted by a field in the biblio table. copyrightdate
275 Returns a reference-to-array, whose elements are references-to-hash,
276 as returned by C<C4::Biblio::GetBiblioFromItemNumber>.
278 Note: the notforloan status comes from the itemtype, and where it equals 0
279 it does not ensure that related items.notforloan status is likewise 0. The
280 caller has to check any items on their own, possibly with CanBookBeIssued
281 from C4::Circulation.
285 sub GetShelfContents ($;$$$) {
286 my ($shelfnumber, $row_count, $offset, $sortfield) = @_;
287 my $dbh=C4::Context->dbh();
288 my $sth1 = $dbh->prepare("SELECT count(*) FROM virtualshelfcontents WHERE shelfnumber = ?");
289 $sth1->execute($shelfnumber);
290 my $total = $sth1->fetchrow;
292 my $sth2 = $dbh->prepare('SELECT sortfield FROM virtualshelves WHERE shelfnumber=?');
293 $sth2->execute($shelfnumber);
294 ($sortfield) = $sth2->fetchrow_array;
297 " SELECT vc.biblionumber, vc.shelfnumber, vc.dateadded, itemtypes.*,
298 biblio.*, biblioitems.itemtype, biblioitems.publicationyear, biblioitems.publishercode, biblioitems.place, biblioitems.size, biblioitems.pages
299 FROM virtualshelfcontents vc
300 LEFT JOIN biblio ON vc.biblionumber = biblio.biblionumber
301 LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber
302 LEFT JOIN itemtypes ON biblioitems.itemtype = itemtypes.itemtype
303 WHERE vc.shelfnumber=? ";
304 my @params = ($shelfnumber);
306 $query .= " ORDER BY " . $sortfield;
307 $query .= " DESC " if ($sortfield eq 'copyrightdate');
310 $query .= " LIMIT ?, ? ";
311 push (@params, ($offset ? $offset : 0));
312 push (@params, $row_count);
314 my $sth3 = $dbh->prepare($query);
315 $sth3->execute(@params);
316 return ($sth3->fetchall_arrayref({}), $total);
317 # Like the perldoc says,
318 # returns reference-to-array, where each element is reference-to-hash of the row:
319 # like [ $sth->fetchrow_hashref(), $sth->fetchrow_hashref() ... ]
320 # Suitable for use in TMPL_LOOP.
321 # See http://search.cpan.org/~timb/DBI-1.601/DBI.pm#fetchall_arrayref
322 # or newer, for your version of DBI.
327 $shelfnumber = &AddShelf( $shelfname, $owner, $category);
329 Creates a new virtual virtualshelves with name C<$shelfname>, owner C<$owner> and category
332 Returns a code to know what's happen.
333 * -1 : if this virtualshelves already exist.
334 * $shelfnumber : if success.
339 my ( $shelfname, $owner, $category, $sortfield ) = @_;
343 WHERE shelfname=? AND owner=?
345 my $sth = $dbh->prepare($query);
346 $sth->execute($shelfname,$owner);
347 ( $sth->rows ) and return (-1);
349 INSERT INTO virtualshelves
350 (shelfname,owner,category,sortfield)
353 $sth = $dbh->prepare($query);
354 $sth->execute( $shelfname, $owner, $category, $sortfield );
355 my $shelfnumber = $dbh->{'mysql_insertid'};
356 return ($shelfnumber);
361 &AddToShelf($biblionumber, $shelfnumber);
363 Adds bib number C<$biblionumber> to virtual virtualshelves number
364 C<$shelfnumber>, unless that bib is already on that shelf.
370 my ( $biblionumber, $shelfnumber ) = @_;
371 return unless $biblionumber;
374 FROM virtualshelfcontents
375 WHERE shelfnumber=? AND biblionumber=?
377 my $sth = $dbh->prepare($query);
379 $sth->execute( $shelfnumber, $biblionumber );
380 ($sth->rows) and return undef; # already on shelf
382 INSERT INTO virtualshelfcontents
383 (shelfnumber, biblionumber, flags)
387 $sth = $dbh->prepare($query);
388 $sth->execute( $shelfnumber, $biblionumber );
389 $query = qq(UPDATE virtualshelves
390 SET lastmodified = CURRENT_TIMESTAMP
391 WHERE shelfnumber = ?);
392 $sth = $dbh->prepare($query);
393 $sth->execute( $shelfnumber );
398 ModShelf($shelfnumber, $hashref)
400 Where $hashref->{column} = param
402 Modify the value into virtualshelves table with values given
403 from hashref, which each key of the hashref should be
404 the name of a column of virtualshelves.
409 my $shelfnumber = shift;
412 if (exists $shelf->{shelfnumber}) {
413 carp "Should not use ModShelf to change shelfnumber";
416 unless (defined $shelfnumber and $shelfnumber =~ /^\d+$/) {
417 carp "Invalid shelfnumber passed to ModShelf: $shelfnumber";
421 my $query = "UPDATE virtualshelves SET ";
422 my @bind_params = ();
423 my @set_clauses = ();
425 foreach my $column (keys %$shelf) {
426 push @set_clauses, "$column = ?";
427 push @bind_params, $shelf->{$column};
430 if ($#set_clauses == -1) {
431 carp "No columns to update passed to ModShelf";
434 $query .= join(", ", @set_clauses);
436 $query .= " WHERE shelfnumber = ? ";
437 push @bind_params, $shelfnumber;
439 $debug and warn "ModShelf query:\n $query\n",
440 "ModShelf query args: ", join(',', @bind_params), "\n";
441 my $sth = $dbh->prepare($query);
442 $sth->execute( @bind_params );
445 =head2 ShelfPossibleAction
447 ShelfPossibleAction($loggedinuser, $shelfnumber, $action);
449 C<$loggedinuser,$shelfnumber,$action>
451 $action can be "view" or "manage".
453 Returns 1 if the user can do the $action in the $shelfnumber shelf.
458 sub ShelfPossibleAction {
459 my ( $user, $shelfnumber, $action ) = @_;
461 SELECT owner,category
465 my $sth = $dbh->prepare($query);
466 $sth->execute($shelfnumber);
467 my ( $owner, $category ) = $sth->fetchrow;
468 my $borrower = GetMemberDetails($user);
469 return 0 if not defined($user);
470 return 1 if ( $category >= 3); # open list
471 return 1 if (($category >= 2) and
472 defined($action) and $action eq 'view'); # public list, anybody can view
473 return 1 if (($category >= 2) and defined($user) and ($borrower->{authflags}->{superlibrarian} || $user == 0)); # public list, superlibrarian can edit/delete
474 return 1 if (defined($user) and $owner eq $user ); # user owns this list. Check last.
480 &DelFromShelf( $biblionumber, $shelfnumber);
482 Removes bib number C<$biblionumber> from virtual virtualshelves number
483 C<$shelfnumber>. If the bib wasn't on that virtualshelves to begin with,
490 my ( $biblionumber, $shelfnumber ) = @_;
492 DELETE FROM virtualshelfcontents
493 WHERE shelfnumber=? AND biblionumber=?
495 my $sth = $dbh->prepare($query);
496 $sth->execute( $shelfnumber, $biblionumber );
499 =head2 DelShelf (old version)
501 ($status, $msg) = &DelShelf($shelfnumber);
503 Deletes virtual virtualshelves number C<$shelfnumber>. The virtualshelves must
506 Returns a two-element array, where C<$status> is 0 if the operation
507 was successful, or non-zero otherwise. C<$msg> is "Done" in case of
508 success, or an error message giving the reason for failure.
510 =head2 DelShelf (current version)
512 $Number = DelShelf($shelfnumber);
514 This function deletes the shelf number, and all of it's content.
520 carp "DelShelf called without valid argument (shelfnumber)";
523 my $sth = $dbh->prepare("DELETE FROM virtualshelves WHERE shelfnumber=?");
524 return $sth->execute(shift);
529 This finds all the public lists that this bib record is in.
533 sub GetBibliosShelves {
534 my ( $biblionumber ) = @_;
535 my $dbh = C4::Context->dbh;
536 my $sth = $dbh->prepare('
537 SELECT vs.shelfname, vs.shelfnumber
538 FROM virtualshelves vs
539 JOIN virtualshelfcontents vc ON (vs.shelfnumber= vc.shelfnumber)
540 WHERE vs.category != 1
541 AND vc.biblionumber= ?
543 $sth->execute( $biblionumber );
544 return $sth->fetchall_arrayref({});
547 =head2 RefreshShelvesSummary
549 ($total, $pubshelves, $barshelves) = RefreshShelvesSummary($sessionID, $loggedinuser, $row_count);
551 Updates the current session and userenv with the most recent shelves
553 Returns the total number of shelves stored in the session/userenv along with two references each to an
554 array of hashes, one containing the C<$loggedinuser>'s private shelves and one containing all public/open shelves.
556 This function is used in conjunction with the 'Lists' button in masthead.inc.
560 sub RefreshShelvesSummary ($$$) {
562 my ($sessionID, $loggedinuser, $row_count) = @_;
563 my $session = C4::Auth::get_session($sessionID);
564 my ($total, $totshelves, $barshelves, $pubshelves);
566 ($barshelves, $totshelves) = GetRecentShelves(1, $row_count, $loggedinuser);
567 $total->{'bartotal'} = $totshelves;
568 ($pubshelves, $totshelves) = GetRecentShelves(2, $row_count, undef);
569 $total->{'pubtotal'} = $totshelves;
571 # Update the current session with the latest shelves...
572 $session->param('barshelves', $barshelves);
573 $session->param('pubshelves', $pubshelves);
574 $session->param('totshelves', $total);
576 # likewise the userenv...
577 C4::Context->set_shelves_userenv('bar',$barshelves);
578 C4::Context->set_shelves_userenv('pub',$pubshelves);
579 C4::Context::set_shelves_userenv('tot',$total);
581 return ($total, $pubshelves, $barshelves);
586 sub _shelf_count ($$) {
588 # Find out how many shelves total meet the submitted criteria...
589 my $query = "SELECT count(*) FROM virtualshelves";
590 $query .= ($params[1] > 1) ? " WHERE category >= ?" : " WHERE owner=? AND category=?";
591 shift @params if $params[1] > 1;
592 my $sth = $dbh->prepare($query);
593 $sth->execute(@params);
594 my $total = $sth->fetchrow;
598 sub _biblionumber_sth {
600 my $query = 'select biblionumber from virtualshelfcontents where shelfnumber = ?';
601 my $dbh = C4::Context->dbh;
602 my $sth = $dbh->prepare($query)
604 $sth->execute( $shelf )
609 sub each_biblionumbers (&$) {
610 my ($code,$shelf) = @_;
611 my $ref = _biblionumber_sth($shelf)->fetchall_arrayref;
624 Koha Development Team <http://koha-community.org/>
628 C4::Circulation::Circ2(3)