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 reference 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.
234 my ($category,$owner) = @_;
235 my $query = 'SELECT * FROM virtualshelves WHERE category = ? AND owner = ? ORDER BY shelfname ASC';
236 my $sth = $dbh->prepare( $query );
237 $sth->execute( $category, $owner );
238 return $sth->fetchall_arrayref({});
243 (shelfnumber,shelfname,owner,category,sortfield) = &GetShelf($shelfnumber);
245 Looks up information about the contents of virtual virtualshelves number
248 Returns the database's information on 'virtualshelves' table.
253 my ($shelfnumber) = @_;
255 SELECT shelfnumber, shelfname, owner, category, sortfield
259 my $sth = $dbh->prepare($query);
260 $sth->execute($shelfnumber);
261 return $sth->fetchrow;
264 =head2 GetShelfContents
266 $biblist = &GetShelfContents($shelfnumber);
268 Looks up information about the contents of virtual virtualshelves number
269 C<$shelfnumber>. Sorted by a field in the biblio table. copyrightdate
272 Returns a reference-to-array, whose elements are references-to-hash,
273 as returned by C<C4::Biblio::GetBiblioFromItemNumber>.
275 Note: the notforloan status comes from the itemtype, and where it equals 0
276 it does not ensure that related items.notforloan status is likewise 0. The
277 caller has to check any items on their own, possibly with CanBookBeIssued
278 from C4::Circulation.
282 sub GetShelfContents ($;$$$) {
283 my ($shelfnumber, $row_count, $offset, $sortfield) = @_;
284 my $dbh=C4::Context->dbh();
285 my $sth1 = $dbh->prepare("SELECT count(*) FROM virtualshelfcontents WHERE shelfnumber = ?");
286 $sth1->execute($shelfnumber);
287 my $total = $sth1->fetchrow;
289 my $sth2 = $dbh->prepare('SELECT sortfield FROM virtualshelves WHERE shelfnumber=?');
290 $sth2->execute($shelfnumber);
291 ($sortfield) = $sth2->fetchrow_array;
294 " SELECT vc.biblionumber, vc.shelfnumber, vc.dateadded, itemtypes.*,
295 biblio.*, biblioitems.itemtype, biblioitems.publicationyear, biblioitems.publishercode, biblioitems.place, biblioitems.size, biblioitems.pages
296 FROM virtualshelfcontents vc
297 LEFT JOIN biblio ON vc.biblionumber = biblio.biblionumber
298 LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber
299 LEFT JOIN itemtypes ON biblioitems.itemtype = itemtypes.itemtype
300 WHERE vc.shelfnumber=? ";
301 my @params = ($shelfnumber);
303 $query .= " ORDER BY " . $sortfield;
304 $query .= " DESC " if ($sortfield eq 'copyrightdate');
307 $query .= " LIMIT ?, ? ";
308 push (@params, ($offset ? $offset : 0));
309 push (@params, $row_count);
311 my $sth3 = $dbh->prepare($query);
312 $sth3->execute(@params);
313 return ($sth3->fetchall_arrayref({}), $total);
314 # Like the perldoc says,
315 # returns reference-to-array, where each element is reference-to-hash of the row:
316 # like [ $sth->fetchrow_hashref(), $sth->fetchrow_hashref() ... ]
317 # Suitable for use in TMPL_LOOP.
318 # See http://search.cpan.org/~timb/DBI-1.601/DBI.pm#fetchall_arrayref
319 # or newer, for your version of DBI.
324 $shelfnumber = &AddShelf( $shelfname, $owner, $category);
326 Creates a new virtual virtualshelves with name C<$shelfname>, owner C<$owner> and category
329 Returns a code to know what's happen.
330 * -1 : if this virtualshelves already exist.
331 * $shelfnumber : if success.
336 my ( $shelfname, $owner, $category, $sortfield ) = @_;
340 WHERE shelfname=? AND owner=?
342 my $sth = $dbh->prepare($query);
343 $sth->execute($shelfname,$owner);
344 ( $sth->rows ) and return (-1);
346 INSERT INTO virtualshelves
347 (shelfname,owner,category,sortfield)
350 $sth = $dbh->prepare($query);
351 $sth->execute( $shelfname, $owner, $category, $sortfield );
352 my $shelfnumber = $dbh->{'mysql_insertid'};
353 return ($shelfnumber);
358 &AddToShelf($biblionumber, $shelfnumber);
360 Adds bib number C<$biblionumber> to virtual virtualshelves number
361 C<$shelfnumber>, unless that bib is already on that shelf.
367 my ( $biblionumber, $shelfnumber ) = @_;
368 return unless $biblionumber;
371 FROM virtualshelfcontents
372 WHERE shelfnumber=? AND biblionumber=?
374 my $sth = $dbh->prepare($query);
376 $sth->execute( $shelfnumber, $biblionumber );
377 ($sth->rows) and return undef; # already on shelf
379 INSERT INTO virtualshelfcontents
380 (shelfnumber, biblionumber, flags)
384 $sth = $dbh->prepare($query);
385 $sth->execute( $shelfnumber, $biblionumber );
386 $query = qq(UPDATE virtualshelves
387 SET lastmodified = CURRENT_TIMESTAMP
388 WHERE shelfnumber = ?);
389 $sth = $dbh->prepare($query);
390 $sth->execute( $shelfnumber );
395 ModShelf($shelfnumber, $hashref)
397 Where $hashref->{column} = param
399 Modify the value into virtualshelves table with values given
400 from hashref, which each key of the hashref should be
401 the name of a column of virtualshelves.
406 my $shelfnumber = shift;
409 if (exists $shelf->{shelfnumber}) {
410 carp "Should not use ModShelf to change shelfnumber";
413 unless (defined $shelfnumber and $shelfnumber =~ /^\d+$/) {
414 carp "Invalid shelfnumber passed to ModShelf: $shelfnumber";
418 my $query = "UPDATE virtualshelves SET ";
419 my @bind_params = ();
420 my @set_clauses = ();
422 foreach my $column (keys %$shelf) {
423 push @set_clauses, "$column = ?";
424 push @bind_params, $shelf->{$column};
427 if ($#set_clauses == -1) {
428 carp "No columns to update passed to ModShelf";
431 $query .= join(", ", @set_clauses);
433 $query .= " WHERE shelfnumber = ? ";
434 push @bind_params, $shelfnumber;
436 $debug and warn "ModShelf query:\n $query\n",
437 "ModShelf query args: ", join(',', @bind_params), "\n";
438 my $sth = $dbh->prepare($query);
439 $sth->execute( @bind_params );
442 =head2 ShelfPossibleAction
444 ShelfPossibleAction($loggedinuser, $shelfnumber, $action);
446 C<$loggedinuser,$shelfnumber,$action>
448 $action can be "view" or "manage".
450 Returns 1 if the user can do the $action in the $shelfnumber shelf.
455 sub ShelfPossibleAction {
456 my ( $user, $shelfnumber, $action ) = @_;
458 SELECT owner,category
462 my $sth = $dbh->prepare($query);
463 $sth->execute($shelfnumber);
464 my ( $owner, $category ) = $sth->fetchrow;
465 my $borrower = GetMemberDetails($user);
466 return 0 if not defined($user);
467 return 1 if ( $category >= 3); # open list
468 return 1 if (($category >= 2) and
469 defined($action) and $action eq 'view'); # public list, anybody can view
470 return 1 if (($category >= 2) and defined($user) and ($borrower->{authflags}->{superlibrarian} || $user == 0)); # public list, superlibrarian can edit/delete
471 return 1 if (defined($user) and $owner eq $user ); # user owns this list. Check last.
477 &DelFromShelf( $biblionumber, $shelfnumber);
479 Removes bib number C<$biblionumber> from virtual virtualshelves number
480 C<$shelfnumber>. If the bib wasn't on that virtualshelves to begin with,
487 my ( $biblionumber, $shelfnumber ) = @_;
489 DELETE FROM virtualshelfcontents
490 WHERE shelfnumber=? AND biblionumber=?
492 my $sth = $dbh->prepare($query);
493 $sth->execute( $shelfnumber, $biblionumber );
496 =head2 DelShelf (old version)
498 ($status, $msg) = &DelShelf($shelfnumber);
500 Deletes virtual virtualshelves number C<$shelfnumber>. The virtualshelves must
503 Returns a two-element array, where C<$status> is 0 if the operation
504 was successful, or non-zero otherwise. C<$msg> is "Done" in case of
505 success, or an error message giving the reason for failure.
507 =head2 DelShelf (current version)
509 $Number = DelShelf($shelfnumber);
511 This function deletes the shelf number, and all of it's content.
517 carp "DelShelf called without valid argument (shelfnumber)";
520 my $sth = $dbh->prepare("DELETE FROM virtualshelves WHERE shelfnumber=?");
521 return $sth->execute(shift);
526 This finds all the public lists that this bib record is in.
530 sub GetBibliosShelves {
531 my ( $biblionumber ) = @_;
532 my $dbh = C4::Context->dbh;
533 my $sth = $dbh->prepare('
534 SELECT vs.shelfname, vs.shelfnumber
535 FROM virtualshelves vs
536 JOIN virtualshelfcontents vc ON (vs.shelfnumber= vc.shelfnumber)
537 WHERE vs.category != 1
538 AND vc.biblionumber= ?
540 $sth->execute( $biblionumber );
541 return $sth->fetchall_arrayref({});
544 =head2 RefreshShelvesSummary
546 ($total, $pubshelves, $barshelves) = RefreshShelvesSummary($sessionID, $loggedinuser, $row_count);
548 Updates the current session and userenv with the most recent shelves
550 Returns the total number of shelves stored in the session/userenv along with two references each to an
551 array of hashes, one containing the C<$loggedinuser>'s private shelves and one containing all public/open shelves.
553 This function is used in conjunction with the 'Lists' button in masthead.inc.
557 sub RefreshShelvesSummary ($$$) {
559 my ($sessionID, $loggedinuser, $row_count) = @_;
560 my $session = C4::Auth::get_session($sessionID);
561 my ($total, $totshelves, $barshelves, $pubshelves);
563 ($barshelves, $totshelves) = GetRecentShelves(1, $row_count, $loggedinuser);
564 $total->{'bartotal'} = $totshelves;
565 ($pubshelves, $totshelves) = GetRecentShelves(2, $row_count, undef);
566 $total->{'pubtotal'} = $totshelves;
568 # Update the current session with the latest shelves...
569 $session->param('barshelves', $barshelves);
570 $session->param('pubshelves', $pubshelves);
571 $session->param('totshelves', $total);
573 # likewise the userenv...
574 C4::Context->set_shelves_userenv('bar',$barshelves);
575 C4::Context->set_shelves_userenv('pub',$pubshelves);
576 C4::Context::set_shelves_userenv('tot',$total);
578 return ($total, $pubshelves, $barshelves);
583 sub _shelf_count ($$) {
585 # Find out how many shelves total meet the submitted criteria...
586 my $query = "SELECT count(*) FROM virtualshelves";
587 $query .= ($params[1] > 1) ? " WHERE category >= ?" : " WHERE owner=? AND category=?";
588 shift @params if $params[1] > 1;
589 my $sth = $dbh->prepare($query);
590 $sth->execute(@params);
591 my $total = $sth->fetchrow;
595 sub _biblionumber_sth {
597 my $query = 'select biblionumber from virtualshelfcontents where shelfnumber = ?';
598 my $dbh = C4::Context->dbh;
599 my $sth = $dbh->prepare($query)
601 $sth->execute( $shelf )
606 sub each_biblionumbers (&$) {
607 my ($code,$shelf) = @_;
608 my $ref = _biblionumber_sth($shelf)->fetchall_arrayref;
621 Koha Development Team <http://koha-community.org/>
625 C4::Circulation::Circ2(3)