1 package C4::VirtualShelves;
3 # Copyright 2000-2002 Katipo Communications
5 # This file is part of Koha.
7 # Koha is free software; you can redistribute it and/or modify it
8 # under the terms of the GNU General Public License as published by
9 # the Free Software Foundation; either version 3 of the License, or
10 # (at your option) any later version.
12 # Koha is distributed in the hope that it will be useful, but
13 # WITHOUT ANY WARRANTY; without even the implied warranty of
14 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15 # GNU General Public License for more details.
17 # You should have received a copy of the GNU General Public License
18 # along with Koha; if not, see <http://www.gnu.org/licenses>.
28 use constant SHELVES_MASTHEAD_MAX => 10; #number under Lists button in masthead
29 use constant SHELVES_COMBO_MAX => 10; #add to combo in search
30 use constant SHELVES_MGRPAGE_MAX => 20; #managing page
31 use constant SHELVES_POPUP_MAX => 40; #addbybiblio popup
33 use constant SHARE_INVITATION_EXPIRY_DAYS => 14; #two weeks to accept
35 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK);
38 # set the version for version checking
39 $VERSION = 3.07.00.049;
43 &GetShelves &GetShelfContents
49 &AddShare &AcceptShare &RemoveShare &IsSharedList
52 &GetAllShelves &ShelvesMax
59 C4::VirtualShelves - Functions for manipulating Koha virtual shelves
63 use C4::VirtualShelves;
67 This module provides functions for manipulating virtual shelves,
68 including creating and deleting virtual shelves, and adding and removing
69 bibs to and from virtual shelves.
75 $shelflist = &GetShelves($category, $row_count, $offset, $owner);
76 ($shelfnumber, $shelfhash) = each %{$shelflist};
78 Returns the number of shelves specified by C<$row_count> and C<$offset> as well as the total
79 number of shelves that meet the C<$owner> and C<$category> criteria. C<$category>,
80 C<$row_count>, and C<$offset> are required. C<$owner> must be supplied when C<$category> == 1.
81 When C<$category> is 2, supply undef as argument for C<$owner>.
83 This function is used by shelfpage in VirtualShelves/Page.pm when listing all shelves for lists management in opac or staff client. Order is by shelfname.
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.
99 my ($category, $row_count, $offset, $owner) = @_;
101 my @params = ( $offset, $row_count );
102 my $dbh = C4::Context->dbh;
104 SELECT vs.shelfnumber, vs.shelfname,vs.owner,
105 bo.surname,bo.firstname,vs.category,vs.sortfield,
106 count(vc.biblionumber) as count
107 FROM virtualshelves vs
108 LEFT JOIN borrowers bo ON vs.owner=bo.borrowernumber
109 LEFT JOIN virtualshelfcontents vc USING (shelfnumber) };
112 LEFT JOIN virtualshelfshares sh ON sh.shelfnumber=vs.shelfnumber
113 AND sh.borrowernumber=?
114 WHERE category=1 AND (vs.owner=? OR sh.borrowernumber=?) };
115 unshift @params, ($owner) x 3;
118 $query.= 'WHERE category=2 ';
121 GROUP BY vs.shelfnumber
122 ORDER BY vs.shelfname
125 my $sth2 = $dbh->prepare($query);
126 $sth2->execute(@params);
128 while( my ($shelfnumber, $shelfname, $owner, $surname, $firstname, $category, $sortfield, $count)= $sth2->fetchrow) {
129 $shelflist{$shelfnumber}->{'shelfname'} = $shelfname;
130 $shelflist{$shelfnumber}->{'count'} = $count;
131 $shelflist{$shelfnumber}->{'single'} = $count==1;
132 $shelflist{$shelfnumber}->{'sortfield'} = $sortfield;
133 $shelflist{$shelfnumber}->{'category'} = $category;
134 $shelflist{$shelfnumber}->{'owner'} = $owner;
135 $shelflist{$shelfnumber}->{'surname'} = $surname;
136 $shelflist{$shelfnumber}->{'firstname'} = $firstname;
143 $shelflist = GetAllShelves($category, $owner)
145 This function returns a reference to an array of hashrefs containing all shelves
146 sorted by the shelf name.
148 This function is intended to return a dataset reflecting all the shelves for
149 the submitted parameters.
154 my ($category,$owner,$adding_allowed) = @_;
156 my $dbh = C4::Context->dbh;
157 my $query = 'SELECT vs.* FROM virtualshelves vs ';
160 LEFT JOIN virtualshelfshares sh ON sh.shelfnumber=vs.shelfnumber
161 AND sh.borrowernumber=?
162 WHERE category=1 AND (vs.owner=? OR sh.borrowernumber=?) };
163 @params = ($owner, $owner, $owner);
166 $query.='WHERE category=2 ';
169 $query.='AND (allow_add=1 OR owner=?) ' if $adding_allowed;
170 push @params, $owner if $adding_allowed;
171 $query.= 'ORDER BY shelfname ASC';
172 my $sth = $dbh->prepare( $query );
173 $sth->execute(@params);
174 return $sth->fetchall_arrayref({});
177 =head2 GetSomeShelfNames
179 Returns shelf names and numbers for Add to combo of search results and Lists button of OPAC header.
183 sub GetSomeShelfNames {
184 my ($owner, $purpose, $adding_allowed)= @_;
185 my ($bar, $pub, @params);
186 my $dbh = C4::Context->dbh;
188 my $bquery = 'SELECT vs.shelfnumber, vs.shelfname FROM virtualshelves vs ';
189 my $limit= ShelvesMax($purpose);
191 my $qry1= $bquery."WHERE vs.category=2 ";
192 $qry1.= "AND (allow_add=1 OR owner=?) " if $adding_allowed;
193 push @params, $owner||0 if $adding_allowed;
194 $qry1.= "ORDER BY vs.lastmodified DESC LIMIT $limit";
196 unless($adding_allowed && (!defined($owner) || $owner<=0)) {
197 #if adding items, user should be known
198 $pub= $dbh->selectall_arrayref($qry1,{Slice=>{}},@params);
202 my $qry2= $bquery. qq{
203 LEFT JOIN virtualshelfshares sh ON sh.shelfnumber=vs.shelfnumber AND sh.borrowernumber=?
204 WHERE vs.category=1 AND (vs.owner=? OR sh.borrowernumber=?) };
205 @params=($owner,$owner,$owner);
206 $qry2.= "AND (allow_add=1 OR owner=?) " if $adding_allowed;
207 push @params, $owner if $adding_allowed;
208 $qry2.= "ORDER BY vs.lastmodified DESC ";
209 $qry2.= "LIMIT $limit";
210 $bar= $dbh->selectall_arrayref($qry2,{Slice=>{}},@params);
213 return ( { bartotal => $bar? scalar @$bar: 0, pubtotal => $pub? scalar @$pub: 0}, $pub, $bar);
216 =head2 GetShelfContents
218 $biblist = &GetShelfContents($shelfnumber);
220 Looks up information about the contents of virtual virtualshelves number
221 C<$shelfnumber>. Sorted by a field in the biblio table. copyrightdate
224 Returns a reference-to-array, whose elements are references-to-hash,
225 as returned by C<C4::Biblio::GetBiblioFromItemNumber>.
227 Note: the notforloan status comes from the itemtype, and where it equals 0
228 it does not ensure that related items.notforloan status is likewise 0. The
229 caller has to check any items on their own, possibly with CanBookBeIssued
230 from C4::Circulation.
234 sub GetShelfContents {
235 my ($shelfnumber, $row_count, $offset, $sortfield, $sort_direction ) = @_;
236 my $dbh=C4::Context->dbh();
237 my $sth1 = $dbh->prepare("SELECT count(*) FROM virtualshelfcontents WHERE shelfnumber = ?");
238 $sth1->execute($shelfnumber);
239 my $total = $sth1->fetchrow;
241 my $sth2 = $dbh->prepare('SELECT sortfield FROM virtualshelves WHERE shelfnumber=?');
242 $sth2->execute($shelfnumber);
243 ($sortfield) = $sth2->fetchrow_array;
246 " SELECT DISTINCT vc.biblionumber, vc.shelfnumber, vc.dateadded, itemtypes.*,
247 biblio.*, biblioitems.itemtype, biblioitems.publicationyear as year, biblioitems.publishercode, biblioitems.place, biblioitems.size, biblioitems.pages
248 FROM virtualshelfcontents vc
249 JOIN biblio ON vc.biblionumber = biblio.biblionumber
250 LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber
251 LEFT JOIN items ON items.biblionumber=vc.biblionumber
252 LEFT JOIN itemtypes ON biblioitems.itemtype = itemtypes.itemtype
253 WHERE vc.shelfnumber=? ";
254 my @params = ($shelfnumber);
256 $query .= " ORDER BY " . $dbh->quote_identifier( $sortfield );
257 $query .= " DESC " if ( $sort_direction eq 'desc' );
260 $query .= " LIMIT ?, ? ";
261 push (@params, ($offset ? $offset : 0));
262 push (@params, $row_count);
264 my $sth3 = $dbh->prepare($query);
265 $sth3->execute(@params);
266 return ($sth3->fetchall_arrayref({}), $total);
267 # Like the perldoc says,
268 # returns reference-to-array, where each element is reference-to-hash of the row:
269 # like [ $sth->fetchrow_hashref(), $sth->fetchrow_hashref() ... ]
270 # Suitable for use in TMPL_LOOP.
271 # See http://search.cpan.org/~timb/DBI-1.601/DBI.pm#fetchall_arrayref
272 # or newer, for your version of DBI.
277 &AddToShelf($biblionumber, $shelfnumber, $borrower);
279 Adds bib number C<$biblionumber> to virtual virtualshelves number
280 C<$shelfnumber>, unless that bib is already on that shelf.
285 my ($biblionumber, $shelfnumber, $borrowernumber) = @_;
286 return unless $biblionumber;
287 my $dbh = C4::Context->dbh;
290 FROM virtualshelfcontents
291 WHERE shelfnumber=? AND biblionumber=?
293 my $sth = $dbh->prepare($query);
295 $sth->execute( $shelfnumber, $biblionumber );
296 ($sth->rows) and return; # already on shelf
298 INSERT INTO virtualshelfcontents
299 (shelfnumber, biblionumber, flags, borrowernumber)
300 VALUES (?, ?, 0, ?));
301 $sth = $dbh->prepare($query);
302 $sth->execute( $shelfnumber, $biblionumber, $borrowernumber);
303 $query = qq(UPDATE virtualshelves
304 SET lastmodified = CURRENT_TIMESTAMP
305 WHERE shelfnumber = ?);
306 $sth = $dbh->prepare($query);
307 $sth->execute( $shelfnumber );
312 my $result= ModShelf($shelfnumber, $hashref)
314 Where $hashref->{column} = param
316 Modify the value into virtualshelves table with values given
317 from hashref, which each key of the hashref should be
318 the name of a column of virtualshelves.
319 Fields like shelfnumber or owner cannot be changed.
321 Returns 1 if the action seemed to be successful.
326 my ($shelfnumber,$hashref) = @_;
327 my $dbh = C4::Context->dbh;
329 my $query= "SELECT * FROM virtualshelves WHERE shelfnumber=?";
330 my $sth = $dbh->prepare($query);
331 $sth->execute($shelfnumber);
332 my $oldrecord= $sth->fetchrow_hashref;
333 return 0 unless $oldrecord; #not found?
335 #initialize missing hash values to silence warnings
336 foreach('shelfname','category', 'sortfield', 'allow_add', 'allow_delete_own', 'allow_delete_other' ) {
337 $hashref->{$_}= undef unless exists $hashref->{$_};
340 #if name or category changes, the name should be tested
341 if($hashref->{shelfname} || $hashref->{category}) {
342 unless(_CheckShelfName(
343 $hashref->{shelfname}//$oldrecord->{shelfname},
344 $hashref->{category}//$oldrecord->{category},
347 return 0; #name check failed
351 #only the following fields from the hash may be changed
352 $query= "UPDATE virtualshelves SET shelfname=?, category=?, sortfield=?, allow_add=?, allow_delete_own=?, allow_delete_other=? WHERE shelfnumber=?";
353 $sth = $dbh->prepare($query);
355 $hashref->{shelfname}//$oldrecord->{shelfname},
356 $hashref->{category}//$oldrecord->{category},
357 $hashref->{sortfield}//$oldrecord->{sortfield},
358 $hashref->{allow_add}//$oldrecord->{allow_add},
359 $hashref->{allow_delete_own}//$oldrecord->{allow_delete_own},
360 $hashref->{allow_delete_other}//$oldrecord->{allow_delete_other},
365 =head2 ShelfPossibleAction
367 ShelfPossibleAction($loggedinuser, $shelfnumber, $action);
369 C<$loggedinuser,$shelfnumber,$action>
371 $action can be "view", "add", "delete", "manage", "new_public", "new_private".
372 New additional actions are: invite, acceptshare.
373 Note that add/delete here refers to adding/deleting entries from the list. Deleting the list itself falls under manage.
374 new_public and new_private refers to creating a new public or private list.
375 The distinction between deleting your own entries from the list or entries from
376 others is made in DelFromShelf.
378 Returns 1 if the user can do the $action in the $shelfnumber shelf.
380 For the actions invite and acceptshare a second errorcode is returned if the
381 result is false. See opac-shareshelf.pl
385 sub ShelfPossibleAction {
386 my ( $user, $shelfnumber, $action ) = @_;
387 $action= 'view' unless $action;
388 $user=0 unless $user;
390 if($action =~ /^new/) { #no shelfnumber needed
391 if($action eq 'new_private') {
394 elsif($action eq 'new_public') {
395 return $user>0 && C4::Context->preference('OpacAllowPublicListCreation');
400 return 0 unless defined($shelfnumber);
402 if ( $user > 0 and $action eq 'delete_shelf' ) {
403 my $borrower = C4::Members::GetMember( borrowernumber => $user );
406 if C4::Auth::haspermission( $borrower->{userid}, { lists => 'delete_public_lists' } );
409 my $dbh = C4::Context->dbh;
411 SELECT COALESCE(owner,0) AS owner, category, allow_add, allow_delete_own, allow_delete_other, COALESCE(sh.borrowernumber,0) AS borrowernumber
412 FROM virtualshelves vs
413 LEFT JOIN virtualshelfshares sh ON sh.shelfnumber=vs.shelfnumber
414 AND sh.borrowernumber=?
415 WHERE vs.shelfnumber=?
417 my $sth = $dbh->prepare($query);
418 $sth->execute($user, $shelfnumber);
419 my $shelf= $sth->fetchrow_hashref;
421 return 0 unless $shelf && ($shelf->{category}==2 || $shelf->{owner}==$user || ($user && $shelf->{borrowernumber}==$user));
422 if($action eq 'view') {
423 #already handled in the above condition
426 elsif($action eq 'add') {
427 return 0 if $user<=0; #should be logged in
428 return 1 if $shelf->{allow_add}==1 || $shelf->{owner}==$user;
429 #owner may always add
431 elsif($action eq 'delete') {
432 #this answer is just diplomatic: it says that you may be able to delete
433 #some items from that shelf
434 #it does not answer the question about a specific biblio
435 #DelFromShelf checks the situation per biblio
436 return 1 if $user>0 && ($shelf->{allow_delete_own}==1 || $shelf->{allow_delete_other}==1);
438 elsif($action eq 'invite') {
439 #for sharing you must be the owner and the list must be private
440 if( $shelf->{category}==1 ) {
441 return 1 if $shelf->{owner}==$user;
442 return (0, 4); # code 4: should be owner
445 return (0, 5); # code 5: should be private list
448 elsif($action eq 'acceptshare') {
449 #the key for accepting is checked later in AcceptShare
450 #you must not be the owner, list must be private
451 if( $shelf->{category}==1 ) {
452 return (0, 8) if $shelf->{owner}==$user;
453 #code 8: should not be owner
457 return (0, 5); # code 5: should be private list
460 elsif($action eq 'manage' or $action eq 'delete_shelf') {
461 return 1 if $user && $shelf->{owner}==$user;
468 $result= &DelFromShelf( $bibref, $shelfnumber, $user);
470 Removes biblionumbers in passed arrayref from shelf C<$shelfnumber>.
471 If the bib wasn't on that virtualshelves to begin with, nothing happens.
473 Returns 0 if no items have been deleted.
478 my ($bibref, $shelfnumber, $user) = @_;
479 my $dbh = C4::Context->dbh;
480 my $query = qq(SELECT allow_delete_own, allow_delete_other FROM virtualshelves WHERE shelfnumber=?);
481 my $sth= $dbh->prepare($query);
482 $sth->execute($shelfnumber);
483 my ($del_own, $del_oth)= $sth->fetchrow;
487 $query = qq(DELETE FROM virtualshelfcontents
488 WHERE shelfnumber=? AND biblionumber=? AND borrowernumber=?);
489 $sth= $dbh->prepare($query);
490 foreach my $biblionumber (@$bibref) {
491 $sth->execute($shelfnumber, $biblionumber, $user);
492 $r= $sth->rows; #Expect -1, 0 or 1 (-1 means Don't know; count as 1)
493 $t+= ($r==-1)? 1: $r;
497 #includes a check if borrowernumber is null (deleted patron)
498 $query = qq/DELETE FROM virtualshelfcontents
499 WHERE shelfnumber=? AND biblionumber=? AND
500 (borrowernumber IS NULL OR borrowernumber<>?)/;
501 $sth= $dbh->prepare($query);
502 foreach my $biblionumber (@$bibref) {
503 $sth->execute($shelfnumber, $biblionumber, $user);
505 $t+= ($r==-1)? 1: $r;
511 =head2 GetBibliosShelves
513 This finds all the public lists that this bib record is in.
517 sub GetBibliosShelves {
518 my ( $biblionumber ) = @_;
519 my $dbh = C4::Context->dbh;
520 my $sth = $dbh->prepare('
521 SELECT vs.shelfname, vs.shelfnumber
522 FROM virtualshelves vs
523 JOIN virtualshelfcontents vc ON (vs.shelfnumber= vc.shelfnumber)
525 AND vc.biblionumber= ?
527 $sth->execute( $biblionumber );
528 return $sth->fetchall_arrayref({});
533 $howmany= ShelvesMax($context);
535 Tells how much shelves are shown in which context.
536 POPUP refers to addbybiblionumber popup, MGRPAGE is managing page (in opac or
537 staff), COMBO refers to the Add to-combo of search results. MASTHEAD is the
538 main Koha toolbar with Lists button.
544 return SHELVES_POPUP_MAX if $which eq 'POPUP';
545 return SHELVES_MGRPAGE_MAX if $which eq 'MGRPAGE';
546 return SHELVES_COMBO_MAX if $which eq 'COMBO';
547 return SHELVES_MASTHEAD_MAX if $which eq 'MASTHEAD';
548 return SHELVES_MASTHEAD_MAX;
551 =head2 HandleDelBorrower
553 HandleDelBorrower($borrower);
555 When a member is deleted (DelMember in Members.pm), you should call me first.
556 This routine deletes/moves lists and entries for the deleted member/borrower.
557 Lists owned by the borrower are deleted, but entries from the borrower to
558 other lists are kept.
562 sub HandleDelBorrower {
565 my $dbh = C4::Context->dbh;
567 #Delete all lists and all shares of this borrower
568 #Consistent with the approach Koha uses on deleting individual lists
569 #Note that entries in virtualshelfcontents added by this borrower to
570 #lists of others will be handled by a table constraint: the borrower
571 #is set to NULL in those entries.
572 $query="DELETE FROM virtualshelves WHERE owner=?";
573 $dbh->do($query,undef,($borrower));
576 #We could handle the above deletes via a constraint too.
577 #But a new BZ report 11889 has been opened to discuss another approach.
578 #Instead of deleting we could also disown lists (based on a pref).
579 #In that way we could save shared and public lists.
580 #The current table constraints support that idea now.
581 #This pref should then govern the results of other routines/methods such as
582 #Koha::Virtualshelf->new->delete too.
587 AddShare($shelfnumber, $key);
589 Adds a share request to the virtualshelves table.
590 Authorization must have been checked, and a key must be supplied. See script
591 opac-shareshelf.pl for an example.
592 This request is not yet confirmed. So it has no borrowernumber, it does have an
598 my ($shelfnumber, $key)= @_;
599 return if !$shelfnumber || !$key;
601 my $dbh = C4::Context->dbh;
602 my $sql = "INSERT INTO virtualshelfshares (shelfnumber, invitekey, sharedate) VALUES (?, ?, NOW())";
603 $dbh->do($sql, undef, ($shelfnumber, $key));
609 my $result= AcceptShare($shelfnumber, $key, $borrowernumber);
611 Checks acceptation of a share request.
612 Key must be found for this shelf. Invitation must not have expired.
613 Returns true when accepted, false otherwise.
618 my ($shelfnumber, $key, $borrowernumber)= @_;
619 return if !$shelfnumber || !$key || !$borrowernumber;
622 my $dbh = C4::Context->dbh;
624 UPDATE virtualshelfshares
625 SET invitekey=NULL, sharedate=NOW(), borrowernumber=?
626 WHERE shelfnumber=? AND invitekey=? AND (sharedate + INTERVAL ? DAY) >NOW()
628 my $i= $dbh->do($sql, undef, ($borrowernumber, $shelfnumber, $key, SHARE_INVITATION_EXPIRY_DAYS));
629 return if !defined($i) || !$i || $i eq '0E0'; #not found
635 my $bool= IsSharedList( $shelfnumber );
637 IsSharedList checks if a (private) list has shares.
638 Note that such a check would not be useful for public lists. A public list has
639 no shares, but is visible for anyone by nature..
640 Used to determine the list type in the display of Your lists (all private).
641 Returns boolean value.
646 my ($shelfnumber) = @_;
647 my $dbh = C4::Context->dbh;
648 my $sql="SELECT id FROM virtualshelfshares WHERE shelfnumber=? AND borrowernumber IS NOT NULL";
649 my $sth = $dbh->prepare($sql);
650 $sth->execute($shelfnumber);
651 my ($rv)= $sth->fetchrow_array;
657 RemoveShare( $user, $shelfnumber );
659 RemoveShare removes a share for specific shelf and borrower.
660 Returns true if a record could be deleted.
665 my ($user, $shelfnumber)= @_;
666 my $dbh = C4::Context->dbh;
668 DELETE FROM virtualshelfshares
669 WHERE borrowernumber=? AND shelfnumber=?
671 my $n= $dbh->do($sql,undef,($user, $shelfnumber));
672 return if !defined($n) || !$n || $n eq '0E0'; #nothing removed
678 my ($owner, $category) = @_;
680 # Find out how many shelves total meet the submitted criteria...
682 my $dbh = C4::Context->dbh;
683 my $query = "SELECT count(*) FROM virtualshelves vs ";
686 LEFT JOIN virtualshelfshares sh ON sh.shelfnumber=vs.shelfnumber
687 AND sh.borrowernumber=?
688 WHERE category=1 AND (vs.owner=? OR sh.borrowernumber=?) };
689 @params= ($owner, $owner, $owner);
692 $query.='WHERE category=2';
695 my $sth = $dbh->prepare($query);
696 $sth->execute(@params);
697 my ($total)= $sth->fetchrow;
707 Koha Development Team <http://koha-community.org/>
711 C4::Circulation::Circ2(3)