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);
207 $selection = ' WHERE owner = ? AND category = ?';
209 @params = ( $mincategory);
210 $selection = ' WHERE category >= ? ';
212 my $query = 'SELECT * FROM virtualshelves';
213 $query .= $selection;
214 $query .= ' ORDER BY lastmodified DESC';
216 $query .= ' LIMIT ?';
217 push @params,$row_count;
219 my $sth = $dbh->prepare($query);
220 $sth->execute(@params);
221 my $shelflist = $sth->fetchall_arrayref({});
222 return ( $shelflist, $total );
227 $shelflist = GetAllShelves($owner)
229 This function returns a reference to an array of hashrefs containing all shelves sorted
232 This function is intended to return a dataset reflecting all the shelves for
233 the submitted parameters.
238 my ($category,$owner) = @_;
239 my $query = 'SELECT * FROM virtualshelves WHERE category = ? AND owner = ? ORDER BY shelfname ASC';
240 my $sth = $dbh->prepare( $query );
241 $sth->execute( $category, $owner );
242 return $sth->fetchall_arrayref({});
247 (shelfnumber,shelfname,owner,category,sortfield) = &GetShelf($shelfnumber);
249 Looks up information about the contents of virtual virtualshelves number
252 Returns the database's information on 'virtualshelves' table.
257 my ($shelfnumber) = @_;
259 SELECT shelfnumber, shelfname, owner, category, sortfield
263 my $sth = $dbh->prepare($query);
264 $sth->execute($shelfnumber);
265 return $sth->fetchrow;
268 =head2 GetShelfContents
270 $biblist = &GetShelfContents($shelfnumber);
272 Looks up information about the contents of virtual virtualshelves number
273 C<$shelfnumber>. Sorted by a field in the biblio table. copyrightdate
276 Returns a reference-to-array, whose elements are references-to-hash,
277 as returned by C<C4::Biblio::GetBiblioFromItemNumber>.
279 Note: the notforloan status comes from the itemtype, and where it equals 0
280 it does not ensure that related items.notforloan status is likewise 0. The
281 caller has to check any items on their own, possibly with CanBookBeIssued
282 from C4::Circulation.
286 sub GetShelfContents ($;$$$) {
287 my ($shelfnumber, $row_count, $offset, $sortfield) = @_;
288 my $dbh=C4::Context->dbh();
289 my $sth1 = $dbh->prepare("SELECT count(*) FROM virtualshelfcontents WHERE shelfnumber = ?");
290 $sth1->execute($shelfnumber);
291 my $total = $sth1->fetchrow;
293 my $sth2 = $dbh->prepare('SELECT sortfield FROM virtualshelves WHERE shelfnumber=?');
294 $sth2->execute($shelfnumber);
295 ($sortfield) = $sth2->fetchrow_array;
298 " SELECT vc.biblionumber, vc.shelfnumber, vc.dateadded, itemtypes.*,
299 biblio.*, biblioitems.itemtype, biblioitems.publicationyear as year, biblioitems.publishercode, biblioitems.place, biblioitems.size, biblioitems.pages
300 FROM virtualshelfcontents vc
301 LEFT JOIN biblio ON vc.biblionumber = biblio.biblionumber
302 LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber
303 LEFT JOIN itemtypes ON biblioitems.itemtype = itemtypes.itemtype
304 WHERE vc.shelfnumber=? ";
305 my @params = ($shelfnumber);
307 $query .= " ORDER BY " . $sortfield;
308 $query .= " DESC " if ($sortfield eq 'copyrightdate');
311 $query .= " LIMIT ?, ? ";
312 push (@params, ($offset ? $offset : 0));
313 push (@params, $row_count);
315 my $sth3 = $dbh->prepare($query);
316 $sth3->execute(@params);
317 return ($sth3->fetchall_arrayref({}), $total);
318 # Like the perldoc says,
319 # returns reference-to-array, where each element is reference-to-hash of the row:
320 # like [ $sth->fetchrow_hashref(), $sth->fetchrow_hashref() ... ]
321 # Suitable for use in TMPL_LOOP.
322 # See http://search.cpan.org/~timb/DBI-1.601/DBI.pm#fetchall_arrayref
323 # or newer, for your version of DBI.
328 $shelfnumber = &AddShelf( $shelfname, $owner, $category);
330 Creates a new virtual virtualshelves with name C<$shelfname>, owner C<$owner> and category
333 Returns a code to know what's happen.
334 * -1 : if this virtualshelves already exist.
335 * $shelfnumber : if success.
340 my ( $shelfname, $owner, $category, $sortfield ) = @_;
344 WHERE shelfname=? AND owner=?
346 my $sth = $dbh->prepare($query);
347 $sth->execute($shelfname,$owner);
348 ( $sth->rows ) and return (-1);
350 INSERT INTO virtualshelves
351 (shelfname,owner,category,sortfield)
354 $sth = $dbh->prepare($query);
355 $sth->execute( $shelfname, $owner, $category, $sortfield );
356 my $shelfnumber = $dbh->{'mysql_insertid'};
357 return ($shelfnumber);
362 &AddToShelf($biblionumber, $shelfnumber);
364 Adds bib number C<$biblionumber> to virtual virtualshelves number
365 C<$shelfnumber>, unless that bib is already on that shelf.
371 my ( $biblionumber, $shelfnumber ) = @_;
372 return unless $biblionumber;
375 FROM virtualshelfcontents
376 WHERE shelfnumber=? AND biblionumber=?
378 my $sth = $dbh->prepare($query);
380 $sth->execute( $shelfnumber, $biblionumber );
381 ($sth->rows) and return undef; # already on shelf
383 INSERT INTO virtualshelfcontents
384 (shelfnumber, biblionumber, flags)
388 $sth = $dbh->prepare($query);
389 $sth->execute( $shelfnumber, $biblionumber );
390 $query = qq(UPDATE virtualshelves
391 SET lastmodified = CURRENT_TIMESTAMP
392 WHERE shelfnumber = ?);
393 $sth = $dbh->prepare($query);
394 $sth->execute( $shelfnumber );
399 ModShelf($shelfnumber, $hashref)
401 Where $hashref->{column} = param
403 Modify the value into virtualshelves table with values given
404 from hashref, which each key of the hashref should be
405 the name of a column of virtualshelves.
410 my $shelfnumber = shift;
413 if (exists $shelf->{shelfnumber}) {
414 carp "Should not use ModShelf to change shelfnumber";
417 unless (defined $shelfnumber and $shelfnumber =~ /^\d+$/) {
418 carp "Invalid shelfnumber passed to ModShelf: $shelfnumber";
422 my $query = "UPDATE virtualshelves SET ";
423 my @bind_params = ();
424 my @set_clauses = ();
426 foreach my $column (keys %$shelf) {
427 push @set_clauses, "$column = ?";
428 push @bind_params, $shelf->{$column};
431 if ($#set_clauses == -1) {
432 carp "No columns to update passed to ModShelf";
435 $query .= join(", ", @set_clauses);
437 $query .= " WHERE shelfnumber = ? ";
438 push @bind_params, $shelfnumber;
440 $debug and warn "ModShelf query:\n $query\n",
441 "ModShelf query args: ", join(',', @bind_params), "\n";
442 my $sth = $dbh->prepare($query);
443 $sth->execute( @bind_params );
446 =head2 ShelfPossibleAction
448 ShelfPossibleAction($loggedinuser, $shelfnumber, $action);
450 C<$loggedinuser,$shelfnumber,$action>
452 $action can be "view" or "manage".
454 Returns 1 if the user can do the $action in the $shelfnumber shelf.
459 sub ShelfPossibleAction {
460 my ( $user, $shelfnumber, $action ) = @_;
462 SELECT owner,category
466 my $sth = $dbh->prepare($query);
467 $sth->execute($shelfnumber);
468 my ( $owner, $category ) = $sth->fetchrow;
469 my $borrower = GetMemberDetails($user);
470 return 0 if not defined($user);
471 return 1 if ( $category >= 3); # open list
472 return 1 if (($category >= 2) and
473 defined($action) and $action eq 'view'); # public list, anybody can view
474 return 1 if (($category >= 2) and defined($user) and ($borrower->{authflags}->{superlibrarian} || $user == 0)); # public list, superlibrarian can edit/delete
475 return 1 if (defined($user) and $owner eq $user ); # user owns this list. Check last.
481 &DelFromShelf( $biblionumber, $shelfnumber);
483 Removes bib number C<$biblionumber> from virtual virtualshelves number
484 C<$shelfnumber>. If the bib wasn't on that virtualshelves to begin with,
491 my ( $biblionumber, $shelfnumber ) = @_;
493 DELETE FROM virtualshelfcontents
494 WHERE shelfnumber=? AND biblionumber=?
496 my $sth = $dbh->prepare($query);
497 $sth->execute( $shelfnumber, $biblionumber );
500 =head2 DelShelf (old version)
502 ($status, $msg) = &DelShelf($shelfnumber);
504 Deletes virtual virtualshelves number C<$shelfnumber>. The virtualshelves must
507 Returns a two-element array, where C<$status> is 0 if the operation
508 was successful, or non-zero otherwise. C<$msg> is "Done" in case of
509 success, or an error message giving the reason for failure.
511 =head2 DelShelf (current version)
513 $Number = DelShelf($shelfnumber);
515 This function deletes the shelf number, and all of it's content.
521 carp "DelShelf called without valid argument (shelfnumber)";
524 my $sth = $dbh->prepare("DELETE FROM virtualshelves WHERE shelfnumber=?");
525 return $sth->execute(shift);
530 This finds all the public lists that this bib record is in.
534 sub GetBibliosShelves {
535 my ( $biblionumber ) = @_;
536 my $dbh = C4::Context->dbh;
537 my $sth = $dbh->prepare('
538 SELECT vs.shelfname, vs.shelfnumber
539 FROM virtualshelves vs
540 JOIN virtualshelfcontents vc ON (vs.shelfnumber= vc.shelfnumber)
541 WHERE vs.category != 1
542 AND vc.biblionumber= ?
544 $sth->execute( $biblionumber );
545 return $sth->fetchall_arrayref({});
548 =head2 RefreshShelvesSummary
550 ($total, $pubshelves, $barshelves) = RefreshShelvesSummary($sessionID, $loggedinuser, $row_count);
552 Updates the current session and userenv with the most recent shelves
554 Returns the total number of shelves stored in the session/userenv along with two references each to an
555 array of hashes, one containing the C<$loggedinuser>'s private shelves and one containing all public/open shelves.
557 This function is used in conjunction with the 'Lists' button in masthead.inc.
561 sub RefreshShelvesSummary ($$$) {
563 my ($sessionID, $loggedinuser, $row_count) = @_;
564 my $session = C4::Auth::get_session($sessionID);
565 my ($total, $totshelves, $barshelves, $pubshelves);
567 ($barshelves, $totshelves) = GetRecentShelves(1, $row_count, $loggedinuser);
568 $total->{'bartotal'} = $totshelves;
569 ($pubshelves, $totshelves) = GetRecentShelves(2, $row_count, undef);
570 $total->{'pubtotal'} = $totshelves;
572 # Update the current session with the latest shelves...
573 $session->param('barshelves', $barshelves);
574 $session->param('pubshelves', $pubshelves);
575 $session->param('totshelves', $total);
577 # likewise the userenv...
578 C4::Context->set_shelves_userenv('bar',$barshelves);
579 C4::Context->set_shelves_userenv('pub',$pubshelves);
580 C4::Context::set_shelves_userenv('tot',$total);
582 return ($total, $pubshelves, $barshelves);
587 sub _shelf_count ($$) {
589 # Find out how many shelves total meet the submitted criteria...
590 my $query = "SELECT count(*) FROM virtualshelves";
591 $query .= ($params[1] > 1) ? " WHERE category >= ?" : " WHERE owner=? AND category=?";
592 shift @params if $params[1] > 1;
593 my $sth = $dbh->prepare($query);
594 $sth->execute(@params);
595 my $total = $sth->fetchrow;
599 sub _biblionumber_sth {
601 my $query = 'select biblionumber from virtualshelfcontents where shelfnumber = ?';
602 my $dbh = C4::Context->dbh;
603 my $sth = $dbh->prepare($query)
605 $sth->execute( $shelf )
610 sub each_biblionumbers (&$) {
611 my ($code,$shelf) = @_;
612 my $ref = _biblionumber_sth($shelf)->fetchall_arrayref;
625 Koha Development Team <http://koha-community.org/>
629 C4::Circulation::Circ2(3)