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 with
21 # Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
22 # Suite 330, Boston, MA 02111-1307 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.
78 ($shelflist, $totshelves) = &GetShelves($mincategory, $row_count, $offset, $owner);
79 ($shelfnumber, $shelfhash) = each %{$shelflist};
81 Returns the number of shelves specified by C<$row_count> and C<$offset> as well as the total
82 number of shelves that meet the C<$owner> and C<$mincategory> criteria. C<$mincategory>,
83 C<$row_count>, and C<$offset> are required. C<$owner> must be supplied when C<$mincategory> == 1.
84 When C<$mincategory> is 2 or 3, supply undef as argument for C<$owner>.
85 C<$shelflist>is a reference-to-hash. The keys are the virtualshelves numbers (C<$shelfnumber>, above),
86 and the values (C<$shelfhash>, above) are themselves references-to-hash, with the following keys:
90 =item C<$shelfhash-E<gt>{shelfname}>
92 A string. The name of the shelf.
94 =item C<$shelfhash-E<gt>{count}>
96 The number of virtuals on that virtualshelves.
102 sub GetShelves ($$$$) {
103 my ($mincategory, $row_count, $offset, $owner) = @_;
104 my @params = ($owner, $mincategory, ($offset ? $offset : 0), $row_count);
105 my @params1 = ($owner, $mincategory);
106 if ($mincategory > 1) {
110 my $total = _shelf_count($owner, $mincategory);
111 # grab only the shelves meeting the row_count/offset spec...
113 SELECT virtualshelves.shelfnumber, virtualshelves.shelfname,owner,surname,firstname,virtualshelves.category,virtualshelves.sortfield,
114 count(virtualshelfcontents.biblionumber) as count
116 LEFT JOIN virtualshelfcontents ON virtualshelves.shelfnumber = virtualshelfcontents.shelfnumber
117 LEFT JOIN borrowers ON virtualshelves.owner = borrowers.borrowernumber );
118 $query .= ($mincategory == 1) ? "WHERE owner=? AND category=?" : "WHERE category>=?";
120 GROUP BY virtualshelves.shelfnumber
121 ORDER BY virtualshelves.category
124 my $sth2 = $dbh->prepare($query);
125 $sth2->execute(@params);
127 while ( my ( $shelfnumber, $shelfname, $owner, $surname,
128 $firstname, $category, $sortfield, $count ) = $sth2->fetchrow ) {
129 $shelflist{$shelfnumber}->{'shelfname'} = $shelfname;
130 $shelflist{$shelfnumber}->{'count'} = $count;
131 $shelflist{$shelfnumber}->{'sortfield'} = $sortfield;
132 $shelflist{$shelfnumber}->{'category'} = $category;
133 $shelflist{$shelfnumber}->{'owner'} = $owner;
134 $shelflist{$shelfnumber}->{'surname'} = $surname;
135 $shelflist{$shelfnumber}->{'firstname'} = $firstname;
137 return ( \%shelflist, $total );
140 =item GetShelvesSummary
142 ($shelves, $total) = GetShelvesSummary($mincategory, $row_count, $offset, $owner)
144 Returns the number of shelves specified by C<$row_count> and C<$offset> as well as the total
145 number of shelves that meet the C<$owner> and/or C<$mincategory> criteria. C<$mincategory>,
146 C<$row_count>, and C<$offset> are required. C<$owner> must be supplied when C<$mincategory> == 1.
147 When C<$mincategory> is 2 or 3, supply undef as argument for C<$owner>.
151 sub GetShelvesSummary ($$$$) {
152 my ($mincategory, $row_count, $offset, $owner) = @_;
153 my @params = ($owner, $mincategory, ($offset ? $offset : 0), $row_count);
154 my @params1 = ($owner, $mincategory);
155 if ($mincategory > 1) {
159 my $total = _shelf_count($owner, $mincategory);
160 # grab only the shelves meeting the row_count/offset spec...
163 virtualshelves.shelfnumber,
164 virtualshelves.shelfname,
166 CONCAT(firstname, ' ', surname) AS name,
167 virtualshelves.category,
168 count(virtualshelfcontents.biblionumber) AS count
170 LEFT JOIN virtualshelfcontents ON virtualshelves.shelfnumber = virtualshelfcontents.shelfnumber
171 LEFT JOIN borrowers ON virtualshelves.owner = borrowers.borrowernumber );
172 $query .= ($mincategory == 1) ? "WHERE owner=? AND category=?" : "WHERE category>=?";
174 GROUP BY virtualshelves.shelfnumber
175 ORDER BY virtualshelves.category
178 my $sth2 = $dbh->prepare($query);
179 $sth2->execute(@params);
180 my $shelves = $sth2->fetchall_arrayref({});
181 return ($shelves, $total);
183 # Probably NOT the final implementation since it is still bulky (repeated hash keys).
184 # might like an array of rows of delimited values:
185 # 1|2||0|blacklist|112
186 # 2|6|Josh Ferraro|51|en_fuego|106
189 =item GetRecentShelves
191 ($shelflist) = GetRecentShelves(1, $limit, $owner)
193 This function returns a references to an array of hashrefs containing specified shelves sorted
194 by the date the shelf was last modified in descending order limited to the number of records
195 specified by C<$row_count>. If calling with C<$mincategory> other than 1, use undef as C<$owner>.
197 This function is intended to return a dataset reflecting the most recently active shelves for
198 the submitted parameters.
202 sub GetRecentShelves ($$$) {
203 my ($mincategory, $row_count, $owner) = @_;
205 my $total = _shelf_count($owner, $mincategory);
206 my @params = ($owner, $mincategory, 0, $row_count); #FIXME: offset is hardcoded here, but could be passed in for enhancements
207 shift @params if (not defined $owner);
208 my $query = "SELECT * FROM virtualshelves";
209 $query .= ((defined $owner) ? " WHERE owner = ? AND category = ?" : " WHERE category >= ? ");
210 $query .= " ORDER BY lastmodified DESC LIMIT ?, ?";
211 my $sth = $dbh->prepare($query);
212 $sth->execute(@params);
213 @shelflist = $sth->fetchall_arrayref({});
214 return ( \@shelflist, $total );
219 (shelfnumber,shelfname,owner,category,sortfield) = &GetShelf($shelfnumber);
221 Looks up information about the contents of virtual virtualshelves number
224 Returns the database's information on 'virtualshelves' table.
229 my ($shelfnumber) = @_;
231 SELECT shelfnumber, shelfname, owner, category, sortfield
235 my $sth = $dbh->prepare($query);
236 $sth->execute($shelfnumber);
237 return $sth->fetchrow;
240 =item GetShelfContents
242 $itemlist = &GetShelfContents($shelfnumber);
244 Looks up information about the contents of virtual virtualshelves number
245 C<$shelfnumber>. Sorted by a field in the biblio table. copyrightdate
248 Returns a reference-to-array, whose elements are references-to-hash,
249 as returned by C<C4::Biblio::GetBiblioFromItemNumber>.
251 Note: the notforloan status comes from the itemtype, and where it equals 0
252 it does not ensure that related items.notforloan status is likewise 0. The
253 caller has to check any items on their own, possibly with CanBookBeIssued
254 from C4::Circulation.
258 sub GetShelfContents ($;$$$) {
259 my ($shelfnumber, $row_count, $offset, $sortfield) = @_;
260 my $dbh=C4::Context->dbh();
261 my $sth1 = $dbh->prepare("SELECT count(*) FROM virtualshelfcontents WHERE shelfnumber = ?");
262 $sth1->execute($shelfnumber);
263 my $total = $sth1->fetchrow;
265 my $sth2 = $dbh->prepare('SELECT sortfield FROM virtualshelves WHERE shelfnumber=?');
266 $sth2->execute($shelfnumber);
267 ($sortfield) = $sth2->fetchrow_array;
270 " SELECT vc.biblionumber, vc.shelfnumber, vc.dateadded, itemtypes.*,
271 biblio.*, biblioitems.itemtype, biblioitems.publicationyear
272 FROM virtualshelfcontents vc
273 LEFT JOIN biblio ON vc.biblionumber = biblio.biblionumber
274 LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber
275 LEFT JOIN itemtypes ON biblioitems.itemtype = itemtypes.itemtype
276 WHERE vc.shelfnumber=? ";
277 my @params = ($shelfnumber);
279 $query .= " ORDER BY ? ";
280 $query .= " DESC " if ($sortfield eq 'copyrightdate');
281 push (@params, $sortfield);
284 $query .= " LIMIT ?, ? ";
285 push (@params, ($offset ? $offset : 0));
286 push (@params, $row_count);
288 my $sth3 = $dbh->prepare($query);
289 $sth3->execute(@params);
290 return ($sth3->fetchall_arrayref({}), $total);
291 # Like the perldoc says,
292 # returns reference-to-array, where each element is reference-to-hash of the row:
293 # like [ $sth->fetchrow_hashref(), $sth->fetchrow_hashref() ... ]
294 # Suitable for use in TMPL_LOOP.
295 # See http://search.cpan.org/~timb/DBI-1.601/DBI.pm#fetchall_arrayref
296 # or newer, for your version of DBI.
301 $shelfnumber = &AddShelf( $shelfname, $owner, $category);
303 Creates a new virtual virtualshelves with name C<$shelfname>, owner C<$owner> and category
306 Returns a code to know what's happen.
307 * -1 : if this virtualshelves already exist.
308 * $shelfnumber : if success.
313 my ( $shelfname, $owner, $category, $sortfield ) = @_;
317 WHERE shelfname=? AND owner=?
319 my $sth = $dbh->prepare($query);
320 $sth->execute($shelfname,$owner);
321 ( $sth->rows ) and return (-1);
323 INSERT INTO virtualshelves
324 (shelfname,owner,category,sortfield)
327 $sth = $dbh->prepare($query);
328 $sth->execute( $shelfname, $owner, $category, $sortfield );
329 my $shelfnumber = $dbh->{'mysql_insertid'};
330 return ($shelfnumber);
335 &AddToShelf($biblionumber, $shelfnumber);
337 Adds item number C<$biblionumber> to virtual virtualshelves number
338 C<$shelfnumber>, unless that item is already on that shelf.
344 my ( $biblionumber, $shelfnumber ) = @_;
345 return unless $biblionumber;
348 FROM virtualshelfcontents
349 WHERE shelfnumber=? AND biblionumber=?
351 my $sth = $dbh->prepare($query);
353 $sth->execute( $shelfnumber, $biblionumber );
354 ($sth->rows) and return undef; # already on shelf
356 INSERT INTO virtualshelfcontents
357 (shelfnumber, biblionumber, flags)
361 $sth = $dbh->prepare($query);
362 $sth->execute( $shelfnumber, $biblionumber );
363 $query = qq(UPDATE virtualshelves
364 SET lastmodified = CURRENT_TIMESTAMP
365 WHERE shelfnumber = ?);
366 $sth = $dbh->prepare($query);
367 $sth->execute( $shelfnumber );
370 =item AddToShelfFromBiblio
372 &AddToShelfFromBiblio($biblionumber, $shelfnumber)
374 this function allow to add a virtual into the shelf number $shelfnumber
379 sub AddToShelfFromBiblio {
380 my ( $biblionumber, $shelfnumber ) = @_;
381 return unless $biblionumber;
384 FROM virtualshelfcontents
385 WHERE shelfnumber=? AND biblionumber=?
387 my $sth = $dbh->prepare($query);
388 $sth->execute( $shelfnumber, $biblionumber );
389 unless ( $sth->rows ) {
391 INSERT INTO virtualshelfcontents
392 (shelfnumber, biblionumber, flags)
396 $sth = $dbh->prepare($query);
397 $sth->execute( $shelfnumber, $biblionumber );
398 $query = qq(UPDATE virtualshelves
399 SET lastmodified = CURRENT_TIMESTAMP
400 WHERE shelfnumber = ?);
401 $sth = $dbh->prepare($query);
402 $sth->execute( $shelfnumber );
408 ModShelf($shelfnumber, $hashref)
410 Where $hashref->{column} = param
412 Modify the value into virtualshelves table with values given
413 from hashref, which each key of the hashref should be
414 the name of a column of virtualshelves.
419 my $shelfnumber = shift;
422 if (exists $shelf->{shelfnumber}) {
423 carp "Should not use ModShelf to change shelfnumber";
426 unless (defined $shelfnumber and $shelfnumber =~ /^\d+$/) {
427 carp "Invalid shelfnumber passed to ModShelf: $shelfnumber";
431 my $query = "UPDATE virtualshelves SET ";
432 my @bind_params = ();
433 my @set_clauses = ();
435 foreach my $column (keys %$shelf) {
436 push @set_clauses, "$column = ?";
437 push @bind_params, $shelf->{$column};
440 if ($#set_clauses == -1) {
441 carp "No columns to update passed to ModShelf";
444 $query .= join(", ", @set_clauses);
446 $query .= " WHERE shelfnumber = ? ";
447 push @bind_params, $shelfnumber;
449 $debug and warn "ModShelf query:\n $query\n",
450 "ModShelf query args: ", join(',', @bind_params), "\n";
451 my $sth = $dbh->prepare($query);
452 $sth->execute( @bind_params );
455 =item ShelfPossibleAction
457 ShelfPossibleAction($loggedinuser, $shelfnumber, $action);
459 C<$loggedinuser,$shelfnumber,$action>
461 $action can be "view" or "manage".
463 Returns 1 if the user can do the $action in the $shelfnumber shelf.
468 sub ShelfPossibleAction {
469 my ( $user, $shelfnumber, $action ) = @_;
471 SELECT owner,category
475 my $sth = $dbh->prepare($query);
476 $sth->execute($shelfnumber);
477 my ( $owner, $category ) = $sth->fetchrow;
478 my $borrower = GetMemberDetails($user);
479 return 0 if not defined($user);
480 return 1 if ( $category >= 3); # open list
481 return 1 if (($category >= 2) and
482 defined($action) and $action eq 'view'); # public list, anybody can view
483 return 1 if (($category >= 2) and defined($user) and ($borrower->{authflags}->{superlibrarian} || $user == 0)); # public list, superlibrarian can edit/delete
484 return 1 if (defined($user) and $owner eq $user ); # user owns this list. Check last.
490 &DelFromShelf( $biblionumber, $shelfnumber);
492 Removes item number C<$biblionumber> from virtual virtualshelves number
493 C<$shelfnumber>. If the item wasn't on that virtualshelves to begin with,
500 my ( $biblionumber, $shelfnumber ) = @_;
502 DELETE FROM virtualshelfcontents
503 WHERE shelfnumber=? AND biblionumber=?
505 my $sth = $dbh->prepare($query);
506 $sth->execute( $shelfnumber, $biblionumber );
509 =item DelShelf (old version)
511 ($status, $msg) = &DelShelf($shelfnumber);
513 Deletes virtual virtualshelves number C<$shelfnumber>. The virtualshelves must
516 Returns a two-element array, where C<$status> is 0 if the operation
517 was successful, or non-zero otherwise. C<$msg> is "Done" in case of
518 success, or an error message giving the reason for failure.
520 =item DelShelf (current version)
522 $Number = DelShelf($shelfnumber);
524 This function deletes the shelf number, and all of it's content.
530 carp "DelShelf called without valid argument (shelfnumber)";
533 my $sth = $dbh->prepare("DELETE FROM virtualshelves WHERE shelfnumber=?");
534 return $sth->execute(shift);
539 This finds all the public lists that this bib record is in.
543 sub GetBibliosShelves {
544 my ( $biblionumber ) = @_;
545 my $dbh = C4::Context->dbh;
546 my $sth = $dbh->prepare('
547 SELECT vs.shelfname, vs.shelfnumber
548 FROM virtualshelves vs
549 JOIN virtualshelfcontents vc ON (vs.shelfnumber= vc.shelfnumber)
550 WHERE vs.category != 1
551 AND vc.biblionumber= ?
553 $sth->execute( $biblionumber );
554 return $sth->fetchall_arrayref({});
557 =item RefreshShelvesSummary
559 ($total, $pubshelves, $barshelves) = RefreshShelvesSummary($sessionID, $loggedinuser, $row_count);
561 Updates the current session and userenv with the most recent shelves
563 Returns the total number of shelves stored in the session/userenv along with two references each to an
564 array of hashes, one containing the C<$loggedinuser>'s private shelves and one containing all public/open shelves.
566 This function is used in conjunction with the 'Lists' button in masthead.inc.
570 sub RefreshShelvesSummary ($$$) {
572 my ($sessionID, $loggedinuser, $row_count) = @_;
573 my $session = C4::Auth::get_session($sessionID);
574 my ($total, $totshelves, $barshelves, $pubshelves);
576 ($barshelves, $totshelves) = GetRecentShelves(1, $row_count, $loggedinuser);
577 $total->{'bartotal'} = $totshelves;
578 ($pubshelves, $totshelves) = GetRecentShelves(2, $row_count, undef);
579 $total->{'pubtotal'} = $totshelves;
581 # Update the current session with the latest shelves...
582 $session->param('barshelves', $barshelves->[0]);
583 $session->param('pubshelves', $pubshelves->[0]);
584 $session->param('totshelves', $total);
586 # likewise the userenv...
587 C4::Context->set_shelves_userenv('bar',$barshelves->[0]);
588 C4::Context->set_shelves_userenv('pub',$pubshelves->[0]);
589 C4::Context::set_shelves_userenv('tot',$total);
591 return ($total, $pubshelves, $barshelves);
596 sub _shelf_count ($$) {
598 # Find out how many shelves total meet the submitted criteria...
599 my $query = "SELECT count(*) FROM virtualshelves";
600 $query .= ($params[1] > 1) ? " WHERE category >= ?" : " WHERE owner=? AND category=?";
601 shift @params if $params[1] > 1;
602 my $sth = $dbh->prepare($query);
603 $sth->execute(@params);
604 my $total = $sth->fetchrow;
608 sub _biblionumber_sth {
610 my $query = 'select biblionumber from virtualshelfcontents where shelfnumber = ?';
611 my $dbh = C4::Context->dbh;
612 my $sth = $dbh->prepare($query)
614 $sth->execute( $shelf )
619 sub each_biblionumbers (&$) {
620 my ($code,$shelf) = @_;
621 my $ref = _biblionumber_sth($shelf)->fetchall_arrayref;
636 Koha Developement team <info@koha.org>
640 C4::Circulation::Circ2(3)