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) = GetRecentShelves(1, $limit, $owner)
191 This function returns a references 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) = @_;
203 my $total = _shelf_count($owner, $mincategory);
204 my @params = ($owner, $mincategory, 0, $row_count); #FIXME: offset is hardcoded here, but could be passed in for enhancements
205 shift @params if (not defined $owner);
206 my $query = "SELECT * FROM virtualshelves";
207 $query .= ((defined $owner) ? " WHERE owner = ? AND category = ?" : " WHERE category >= ? ");
208 $query .= " ORDER BY lastmodified DESC LIMIT ?, ?";
209 my $sth = $dbh->prepare($query);
210 $sth->execute(@params);
211 @shelflist = $sth->fetchall_arrayref({});
212 return ( \@shelflist, $total );
217 ($shelflist) = GetAllShelves($owner)
219 This function returns a references to an array of hashrefs containing all shelves sorted
222 This function is intended to return a dataset reflecting all the shelves for
223 the submitted parameters.
227 sub GetAllShelves ($$) {
228 my ($category,$owner) = @_;
230 my @params = ($category,$owner);
231 my $query = "SELECT * FROM virtualshelves WHERE category = ? AND owner = ? ORDER BY shelfname ASC";
232 my $sth = $dbh->prepare($query);
233 $sth->execute(@params);
234 @shelflist = $sth->fetchall_arrayref({});
235 return ( \@shelflist );
240 (shelfnumber,shelfname,owner,category,sortfield) = &GetShelf($shelfnumber);
242 Looks up information about the contents of virtual virtualshelves number
245 Returns the database's information on 'virtualshelves' table.
250 my ($shelfnumber) = @_;
252 SELECT shelfnumber, shelfname, owner, category, sortfield
256 my $sth = $dbh->prepare($query);
257 $sth->execute($shelfnumber);
258 return $sth->fetchrow;
261 =head2 GetShelfContents
263 $biblist = &GetShelfContents($shelfnumber);
265 Looks up information about the contents of virtual virtualshelves number
266 C<$shelfnumber>. Sorted by a field in the biblio table. copyrightdate
269 Returns a reference-to-array, whose elements are references-to-hash,
270 as returned by C<C4::Biblio::GetBiblioFromItemNumber>.
272 Note: the notforloan status comes from the itemtype, and where it equals 0
273 it does not ensure that related items.notforloan status is likewise 0. The
274 caller has to check any items on their own, possibly with CanBookBeIssued
275 from C4::Circulation.
279 sub GetShelfContents ($;$$$) {
280 my ($shelfnumber, $row_count, $offset, $sortfield) = @_;
281 my $dbh=C4::Context->dbh();
282 my $sth1 = $dbh->prepare("SELECT count(*) FROM virtualshelfcontents WHERE shelfnumber = ?");
283 $sth1->execute($shelfnumber);
284 my $total = $sth1->fetchrow;
286 my $sth2 = $dbh->prepare('SELECT sortfield FROM virtualshelves WHERE shelfnumber=?');
287 $sth2->execute($shelfnumber);
288 ($sortfield) = $sth2->fetchrow_array;
291 " SELECT vc.biblionumber, vc.shelfnumber, vc.dateadded, itemtypes.*,
292 biblio.*, biblioitems.itemtype, biblioitems.publicationyear, biblioitems.publishercode, biblioitems.place, biblioitems.size, biblioitems.pages
293 FROM virtualshelfcontents vc
294 LEFT JOIN biblio ON vc.biblionumber = biblio.biblionumber
295 LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber
296 LEFT JOIN itemtypes ON biblioitems.itemtype = itemtypes.itemtype
297 WHERE vc.shelfnumber=? ";
298 my @params = ($shelfnumber);
300 $query .= " ORDER BY " . $sortfield;
301 $query .= " DESC " if ($sortfield eq 'copyrightdate');
304 $query .= " LIMIT ?, ? ";
305 push (@params, ($offset ? $offset : 0));
306 push (@params, $row_count);
308 my $sth3 = $dbh->prepare($query);
309 $sth3->execute(@params);
310 return ($sth3->fetchall_arrayref({}), $total);
311 # Like the perldoc says,
312 # returns reference-to-array, where each element is reference-to-hash of the row:
313 # like [ $sth->fetchrow_hashref(), $sth->fetchrow_hashref() ... ]
314 # Suitable for use in TMPL_LOOP.
315 # See http://search.cpan.org/~timb/DBI-1.601/DBI.pm#fetchall_arrayref
316 # or newer, for your version of DBI.
321 $shelfnumber = &AddShelf( $shelfname, $owner, $category);
323 Creates a new virtual virtualshelves with name C<$shelfname>, owner C<$owner> and category
326 Returns a code to know what's happen.
327 * -1 : if this virtualshelves already exist.
328 * $shelfnumber : if success.
333 my ( $shelfname, $owner, $category, $sortfield ) = @_;
337 WHERE shelfname=? AND owner=?
339 my $sth = $dbh->prepare($query);
340 $sth->execute($shelfname,$owner);
341 ( $sth->rows ) and return (-1);
343 INSERT INTO virtualshelves
344 (shelfname,owner,category,sortfield)
347 $sth = $dbh->prepare($query);
348 $sth->execute( $shelfname, $owner, $category, $sortfield );
349 my $shelfnumber = $dbh->{'mysql_insertid'};
350 return ($shelfnumber);
355 &AddToShelf($biblionumber, $shelfnumber);
357 Adds bib number C<$biblionumber> to virtual virtualshelves number
358 C<$shelfnumber>, unless that bib is already on that shelf.
364 my ( $biblionumber, $shelfnumber ) = @_;
365 return unless $biblionumber;
368 FROM virtualshelfcontents
369 WHERE shelfnumber=? AND biblionumber=?
371 my $sth = $dbh->prepare($query);
373 $sth->execute( $shelfnumber, $biblionumber );
374 ($sth->rows) and return undef; # already on shelf
376 INSERT INTO virtualshelfcontents
377 (shelfnumber, biblionumber, flags)
381 $sth = $dbh->prepare($query);
382 $sth->execute( $shelfnumber, $biblionumber );
383 $query = qq(UPDATE virtualshelves
384 SET lastmodified = CURRENT_TIMESTAMP
385 WHERE shelfnumber = ?);
386 $sth = $dbh->prepare($query);
387 $sth->execute( $shelfnumber );
392 ModShelf($shelfnumber, $hashref)
394 Where $hashref->{column} = param
396 Modify the value into virtualshelves table with values given
397 from hashref, which each key of the hashref should be
398 the name of a column of virtualshelves.
403 my $shelfnumber = shift;
406 if (exists $shelf->{shelfnumber}) {
407 carp "Should not use ModShelf to change shelfnumber";
410 unless (defined $shelfnumber and $shelfnumber =~ /^\d+$/) {
411 carp "Invalid shelfnumber passed to ModShelf: $shelfnumber";
415 my $query = "UPDATE virtualshelves SET ";
416 my @bind_params = ();
417 my @set_clauses = ();
419 foreach my $column (keys %$shelf) {
420 push @set_clauses, "$column = ?";
421 push @bind_params, $shelf->{$column};
424 if ($#set_clauses == -1) {
425 carp "No columns to update passed to ModShelf";
428 $query .= join(", ", @set_clauses);
430 $query .= " WHERE shelfnumber = ? ";
431 push @bind_params, $shelfnumber;
433 $debug and warn "ModShelf query:\n $query\n",
434 "ModShelf query args: ", join(',', @bind_params), "\n";
435 my $sth = $dbh->prepare($query);
436 $sth->execute( @bind_params );
439 =head2 ShelfPossibleAction
441 ShelfPossibleAction($loggedinuser, $shelfnumber, $action);
443 C<$loggedinuser,$shelfnumber,$action>
445 $action can be "view" or "manage".
447 Returns 1 if the user can do the $action in the $shelfnumber shelf.
452 sub ShelfPossibleAction {
453 my ( $user, $shelfnumber, $action ) = @_;
455 SELECT owner,category
459 my $sth = $dbh->prepare($query);
460 $sth->execute($shelfnumber);
461 my ( $owner, $category ) = $sth->fetchrow;
462 my $borrower = GetMemberDetails($user);
463 return 0 if not defined($user);
464 return 1 if ( $category >= 3); # open list
465 return 1 if (($category >= 2) and
466 defined($action) and $action eq 'view'); # public list, anybody can view
467 return 1 if (($category >= 2) and defined($user) and ($borrower->{authflags}->{superlibrarian} || $user == 0)); # public list, superlibrarian can edit/delete
468 return 1 if (defined($user) and $owner eq $user ); # user owns this list. Check last.
474 &DelFromShelf( $biblionumber, $shelfnumber);
476 Removes bib number C<$biblionumber> from virtual virtualshelves number
477 C<$shelfnumber>. If the bib wasn't on that virtualshelves to begin with,
484 my ( $biblionumber, $shelfnumber ) = @_;
486 DELETE FROM virtualshelfcontents
487 WHERE shelfnumber=? AND biblionumber=?
489 my $sth = $dbh->prepare($query);
490 $sth->execute( $shelfnumber, $biblionumber );
493 =head2 DelShelf (old version)
495 ($status, $msg) = &DelShelf($shelfnumber);
497 Deletes virtual virtualshelves number C<$shelfnumber>. The virtualshelves must
500 Returns a two-element array, where C<$status> is 0 if the operation
501 was successful, or non-zero otherwise. C<$msg> is "Done" in case of
502 success, or an error message giving the reason for failure.
504 =head2 DelShelf (current version)
506 $Number = DelShelf($shelfnumber);
508 This function deletes the shelf number, and all of it's content.
514 carp "DelShelf called without valid argument (shelfnumber)";
517 my $sth = $dbh->prepare("DELETE FROM virtualshelves WHERE shelfnumber=?");
518 return $sth->execute(shift);
523 This finds all the public lists that this bib record is in.
527 sub GetBibliosShelves {
528 my ( $biblionumber ) = @_;
529 my $dbh = C4::Context->dbh;
530 my $sth = $dbh->prepare('
531 SELECT vs.shelfname, vs.shelfnumber
532 FROM virtualshelves vs
533 JOIN virtualshelfcontents vc ON (vs.shelfnumber= vc.shelfnumber)
534 WHERE vs.category != 1
535 AND vc.biblionumber= ?
537 $sth->execute( $biblionumber );
538 return $sth->fetchall_arrayref({});
541 =head2 RefreshShelvesSummary
543 ($total, $pubshelves, $barshelves) = RefreshShelvesSummary($sessionID, $loggedinuser, $row_count);
545 Updates the current session and userenv with the most recent shelves
547 Returns the total number of shelves stored in the session/userenv along with two references each to an
548 array of hashes, one containing the C<$loggedinuser>'s private shelves and one containing all public/open shelves.
550 This function is used in conjunction with the 'Lists' button in masthead.inc.
554 sub RefreshShelvesSummary ($$$) {
556 my ($sessionID, $loggedinuser, $row_count) = @_;
557 my $session = C4::Auth::get_session($sessionID);
558 my ($total, $totshelves, $barshelves, $pubshelves);
560 ($barshelves, $totshelves) = GetRecentShelves(1, $row_count, $loggedinuser);
561 $total->{'bartotal'} = $totshelves;
562 ($pubshelves, $totshelves) = GetRecentShelves(2, $row_count, undef);
563 $total->{'pubtotal'} = $totshelves;
565 # Update the current session with the latest shelves...
566 $session->param('barshelves', $barshelves->[0]);
567 $session->param('pubshelves', $pubshelves->[0]);
568 $session->param('totshelves', $total);
570 # likewise the userenv...
571 C4::Context->set_shelves_userenv('bar',$barshelves->[0]);
572 C4::Context->set_shelves_userenv('pub',$pubshelves->[0]);
573 C4::Context::set_shelves_userenv('tot',$total);
575 return ($total, $pubshelves, $barshelves);
580 sub _shelf_count ($$) {
582 # Find out how many shelves total meet the submitted criteria...
583 my $query = "SELECT count(*) FROM virtualshelves";
584 $query .= ($params[1] > 1) ? " WHERE category >= ?" : " WHERE owner=? AND category=?";
585 shift @params if $params[1] > 1;
586 my $sth = $dbh->prepare($query);
587 $sth->execute(@params);
588 my $total = $sth->fetchrow;
592 sub _biblionumber_sth {
594 my $query = 'select biblionumber from virtualshelfcontents where shelfnumber = ?';
595 my $dbh = C4::Context->dbh;
596 my $sth = $dbh->prepare($query)
598 $sth->execute( $shelf )
603 sub each_biblionumbers (&$) {
604 my ($code,$shelf) = @_;
605 my $ref = _biblionumber_sth($shelf)->fetchall_arrayref;
618 Koha Development Team <http://koha-community.org/>
622 C4::Circulation::Circ2(3)