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
43 &AddToShelf &AddToShelfFromBiblio &AddShelf
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 items 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.category
122 my $sth2 = $dbh->prepare($query);
123 $sth2->execute(@params);
125 while ( my ( $shelfnumber, $shelfname, $owner, $surname,
126 $firstname, $category, $sortfield, $count ) = $sth2->fetchrow ) {
127 $shelflist{$shelfnumber}->{'shelfname'} = $shelfname;
128 $shelflist{$shelfnumber}->{'count'} = $count;
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 (shelfnumber,shelfname,owner,category,sortfield) = &GetShelf($shelfnumber);
219 Looks up information about the contents of virtual virtualshelves number
222 Returns the database's information on 'virtualshelves' table.
227 my ($shelfnumber) = @_;
229 SELECT shelfnumber, shelfname, owner, category, sortfield
233 my $sth = $dbh->prepare($query);
234 $sth->execute($shelfnumber);
235 return $sth->fetchrow;
238 =head2 GetShelfContents
240 $itemlist = &GetShelfContents($shelfnumber);
242 Looks up information about the contents of virtual virtualshelves number
243 C<$shelfnumber>. Sorted by a field in the biblio table. copyrightdate
246 Returns a reference-to-array, whose elements are references-to-hash,
247 as returned by C<C4::Biblio::GetBiblioFromItemNumber>.
249 Note: the notforloan status comes from the itemtype, and where it equals 0
250 it does not ensure that related items.notforloan status is likewise 0. The
251 caller has to check any items on their own, possibly with CanBookBeIssued
252 from C4::Circulation.
256 sub GetShelfContents ($;$$$) {
257 my ($shelfnumber, $row_count, $offset, $sortfield) = @_;
258 my $dbh=C4::Context->dbh();
259 my $sth1 = $dbh->prepare("SELECT count(*) FROM virtualshelfcontents WHERE shelfnumber = ?");
260 $sth1->execute($shelfnumber);
261 my $total = $sth1->fetchrow;
263 my $sth2 = $dbh->prepare('SELECT sortfield FROM virtualshelves WHERE shelfnumber=?');
264 $sth2->execute($shelfnumber);
265 ($sortfield) = $sth2->fetchrow_array;
268 " SELECT vc.biblionumber, vc.shelfnumber, vc.dateadded, itemtypes.*,
269 biblio.*, biblioitems.itemtype, biblioitems.publicationyear
270 FROM virtualshelfcontents vc
271 LEFT JOIN biblio ON vc.biblionumber = biblio.biblionumber
272 LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber
273 LEFT JOIN itemtypes ON biblioitems.itemtype = itemtypes.itemtype
274 WHERE vc.shelfnumber=? ";
275 my @params = ($shelfnumber);
277 $query .= " ORDER BY " . $sortfield;
278 $query .= " DESC " if ($sortfield eq 'copyrightdate');
281 $query .= " LIMIT ?, ? ";
282 push (@params, ($offset ? $offset : 0));
283 push (@params, $row_count);
285 my $sth3 = $dbh->prepare($query);
286 $sth3->execute(@params);
287 return ($sth3->fetchall_arrayref({}), $total);
288 # Like the perldoc says,
289 # returns reference-to-array, where each element is reference-to-hash of the row:
290 # like [ $sth->fetchrow_hashref(), $sth->fetchrow_hashref() ... ]
291 # Suitable for use in TMPL_LOOP.
292 # See http://search.cpan.org/~timb/DBI-1.601/DBI.pm#fetchall_arrayref
293 # or newer, for your version of DBI.
298 $shelfnumber = &AddShelf( $shelfname, $owner, $category);
300 Creates a new virtual virtualshelves with name C<$shelfname>, owner C<$owner> and category
303 Returns a code to know what's happen.
304 * -1 : if this virtualshelves already exist.
305 * $shelfnumber : if success.
310 my ( $shelfname, $owner, $category, $sortfield ) = @_;
314 WHERE shelfname=? AND owner=?
316 my $sth = $dbh->prepare($query);
317 $sth->execute($shelfname,$owner);
318 ( $sth->rows ) and return (-1);
320 INSERT INTO virtualshelves
321 (shelfname,owner,category,sortfield)
324 $sth = $dbh->prepare($query);
325 $sth->execute( $shelfname, $owner, $category, $sortfield );
326 my $shelfnumber = $dbh->{'mysql_insertid'};
327 return ($shelfnumber);
332 &AddToShelf($biblionumber, $shelfnumber);
334 Adds item number C<$biblionumber> to virtual virtualshelves number
335 C<$shelfnumber>, unless that item is already on that shelf.
341 my ( $biblionumber, $shelfnumber ) = @_;
342 return unless $biblionumber;
345 FROM virtualshelfcontents
346 WHERE shelfnumber=? AND biblionumber=?
348 my $sth = $dbh->prepare($query);
350 $sth->execute( $shelfnumber, $biblionumber );
351 ($sth->rows) and return undef; # already on shelf
353 INSERT INTO virtualshelfcontents
354 (shelfnumber, biblionumber, flags)
358 $sth = $dbh->prepare($query);
359 $sth->execute( $shelfnumber, $biblionumber );
360 $query = qq(UPDATE virtualshelves
361 SET lastmodified = CURRENT_TIMESTAMP
362 WHERE shelfnumber = ?);
363 $sth = $dbh->prepare($query);
364 $sth->execute( $shelfnumber );
367 =head2 AddToShelfFromBiblio
369 &AddToShelfFromBiblio($biblionumber, $shelfnumber)
371 this function allow to add a virtual into the shelf number $shelfnumber
376 sub AddToShelfFromBiblio {
377 my ( $biblionumber, $shelfnumber ) = @_;
378 return unless $biblionumber;
381 FROM virtualshelfcontents
382 WHERE shelfnumber=? AND biblionumber=?
384 my $sth = $dbh->prepare($query);
385 $sth->execute( $shelfnumber, $biblionumber );
386 unless ( $sth->rows ) {
388 INSERT INTO virtualshelfcontents
389 (shelfnumber, biblionumber, flags)
393 $sth = $dbh->prepare($query);
394 $sth->execute( $shelfnumber, $biblionumber );
395 $query = qq(UPDATE virtualshelves
396 SET lastmodified = CURRENT_TIMESTAMP
397 WHERE shelfnumber = ?);
398 $sth = $dbh->prepare($query);
399 $sth->execute( $shelfnumber );
405 ModShelf($shelfnumber, $hashref)
407 Where $hashref->{column} = param
409 Modify the value into virtualshelves table with values given
410 from hashref, which each key of the hashref should be
411 the name of a column of virtualshelves.
416 my $shelfnumber = shift;
419 if (exists $shelf->{shelfnumber}) {
420 carp "Should not use ModShelf to change shelfnumber";
423 unless (defined $shelfnumber and $shelfnumber =~ /^\d+$/) {
424 carp "Invalid shelfnumber passed to ModShelf: $shelfnumber";
428 my $query = "UPDATE virtualshelves SET ";
429 my @bind_params = ();
430 my @set_clauses = ();
432 foreach my $column (keys %$shelf) {
433 push @set_clauses, "$column = ?";
434 push @bind_params, $shelf->{$column};
437 if ($#set_clauses == -1) {
438 carp "No columns to update passed to ModShelf";
441 $query .= join(", ", @set_clauses);
443 $query .= " WHERE shelfnumber = ? ";
444 push @bind_params, $shelfnumber;
446 $debug and warn "ModShelf query:\n $query\n",
447 "ModShelf query args: ", join(',', @bind_params), "\n";
448 my $sth = $dbh->prepare($query);
449 $sth->execute( @bind_params );
452 =head2 ShelfPossibleAction
454 ShelfPossibleAction($loggedinuser, $shelfnumber, $action);
456 C<$loggedinuser,$shelfnumber,$action>
458 $action can be "view" or "manage".
460 Returns 1 if the user can do the $action in the $shelfnumber shelf.
465 sub ShelfPossibleAction {
466 my ( $user, $shelfnumber, $action ) = @_;
468 SELECT owner,category
472 my $sth = $dbh->prepare($query);
473 $sth->execute($shelfnumber);
474 my ( $owner, $category ) = $sth->fetchrow;
475 my $borrower = GetMemberDetails($user);
476 return 0 if not defined($user);
477 return 1 if ( $category >= 3); # open list
478 return 1 if (($category >= 2) and
479 defined($action) and $action eq 'view'); # public list, anybody can view
480 return 1 if (($category >= 2) and defined($user) and ($borrower->{authflags}->{superlibrarian} || $user == 0)); # public list, superlibrarian can edit/delete
481 return 1 if (defined($user) and $owner eq $user ); # user owns this list. Check last.
487 &DelFromShelf( $biblionumber, $shelfnumber);
489 Removes item number C<$biblionumber> from virtual virtualshelves number
490 C<$shelfnumber>. If the item wasn't on that virtualshelves to begin with,
497 my ( $biblionumber, $shelfnumber ) = @_;
499 DELETE FROM virtualshelfcontents
500 WHERE shelfnumber=? AND biblionumber=?
502 my $sth = $dbh->prepare($query);
503 $sth->execute( $shelfnumber, $biblionumber );
506 =head2 DelShelf (old version)
508 ($status, $msg) = &DelShelf($shelfnumber);
510 Deletes virtual virtualshelves number C<$shelfnumber>. The virtualshelves must
513 Returns a two-element array, where C<$status> is 0 if the operation
514 was successful, or non-zero otherwise. C<$msg> is "Done" in case of
515 success, or an error message giving the reason for failure.
517 =head2 DelShelf (current version)
519 $Number = DelShelf($shelfnumber);
521 This function deletes the shelf number, and all of it's content.
527 carp "DelShelf called without valid argument (shelfnumber)";
530 my $sth = $dbh->prepare("DELETE FROM virtualshelves WHERE shelfnumber=?");
531 return $sth->execute(shift);
536 This finds all the public lists that this bib record is in.
540 sub GetBibliosShelves {
541 my ( $biblionumber ) = @_;
542 my $dbh = C4::Context->dbh;
543 my $sth = $dbh->prepare('
544 SELECT vs.shelfname, vs.shelfnumber
545 FROM virtualshelves vs
546 JOIN virtualshelfcontents vc ON (vs.shelfnumber= vc.shelfnumber)
547 WHERE vs.category != 1
548 AND vc.biblionumber= ?
550 $sth->execute( $biblionumber );
551 return $sth->fetchall_arrayref({});
554 =head2 RefreshShelvesSummary
556 ($total, $pubshelves, $barshelves) = RefreshShelvesSummary($sessionID, $loggedinuser, $row_count);
558 Updates the current session and userenv with the most recent shelves
560 Returns the total number of shelves stored in the session/userenv along with two references each to an
561 array of hashes, one containing the C<$loggedinuser>'s private shelves and one containing all public/open shelves.
563 This function is used in conjunction with the 'Lists' button in masthead.inc.
567 sub RefreshShelvesSummary ($$$) {
569 my ($sessionID, $loggedinuser, $row_count) = @_;
570 my $session = C4::Auth::get_session($sessionID);
571 my ($total, $totshelves, $barshelves, $pubshelves);
573 ($barshelves, $totshelves) = GetRecentShelves(1, $row_count, $loggedinuser);
574 $total->{'bartotal'} = $totshelves;
575 ($pubshelves, $totshelves) = GetRecentShelves(2, $row_count, undef);
576 $total->{'pubtotal'} = $totshelves;
578 # Update the current session with the latest shelves...
579 $session->param('barshelves', $barshelves->[0]);
580 $session->param('pubshelves', $pubshelves->[0]);
581 $session->param('totshelves', $total);
583 # likewise the userenv...
584 C4::Context->set_shelves_userenv('bar',$barshelves->[0]);
585 C4::Context->set_shelves_userenv('pub',$pubshelves->[0]);
586 C4::Context::set_shelves_userenv('tot',$total);
588 return ($total, $pubshelves, $barshelves);
593 sub _shelf_count ($$) {
595 # Find out how many shelves total meet the submitted criteria...
596 my $query = "SELECT count(*) FROM virtualshelves";
597 $query .= ($params[1] > 1) ? " WHERE category >= ?" : " WHERE owner=? AND category=?";
598 shift @params if $params[1] > 1;
599 my $sth = $dbh->prepare($query);
600 $sth->execute(@params);
601 my $total = $sth->fetchrow;
605 sub _biblionumber_sth {
607 my $query = 'select biblionumber from virtualshelfcontents where shelfnumber = ?';
608 my $dbh = C4::Context->dbh;
609 my $sth = $dbh->prepare($query)
611 $sth->execute( $shelf )
616 sub each_biblionumbers (&$) {
617 my ($code,$shelf) = @_;
618 my $ref = _biblionumber_sth($shelf)->fetchall_arrayref;
631 Koha Development Team <http://koha-community.org/>
635 C4::Circulation::Circ2(3)