-# -*- tab-width: 8 -*-
-# Please use 8-character tabs for this file (indents are every 4 characters)
-
package C4::VirtualShelves;
-
# Copyright 2000-2002 Katipo Communications
#
# This file is part of Koha.
use constant SHELVES_MGRPAGE_MAX => 20; #managing page
use constant SHELVES_POPUP_MAX => 40; #addbybiblio popup
+use constant SHARE_INVITATION_EXPIRY_DAYS => 14; #two weeks to accept
+
use vars qw($VERSION @ISA @EXPORT @EXPORT_OK);
BEGIN {
# set the version for version checking
- $VERSION = 3.02;
+ $VERSION = 3.07.00.049;
require Exporter;
@ISA = qw(Exporter);
@EXPORT = qw(
&ShelfPossibleAction
&DelFromShelf &DelShelf
&GetBibliosShelves
+ &AddShare &AcceptShare &RemoveShare &IsSharedList
);
@EXPORT_OK = qw(
&GetAllShelves &ShelvesMax
}
-my $dbh = C4::Context->dbh;
-
=head1 NAME
C4::VirtualShelves - Functions for manipulating Koha virtual shelves
my ($category, $row_count, $offset, $owner) = @_;
my @params;
my $total = _shelf_count($owner, $category);
+ my $dbh = C4::Context->dbh;
my $query = qq{
SELECT vs.shelfnumber, vs.shelfname,vs.owner,
bo.surname,bo.firstname,vs.category,vs.sortfield,
sub GetAllShelves {
my ($category,$owner,$adding_allowed) = @_;
my @params;
+ my $dbh = C4::Context->dbh;
my $query = 'SELECT vs.* FROM virtualshelves vs ';
if($category==1) {
$query.= qq{
sub GetSomeShelfNames {
my ($owner, $purpose, $adding_allowed)= @_;
my ($bar, $pub, @params);
+ my $dbh = C4::Context->dbh;
my $bquery = 'SELECT vs.shelfnumber, vs.shelfname FROM virtualshelves vs ';
my $limit= ShelvesMax($purpose);
$qry1.= "ORDER BY vs.lastmodified DESC LIMIT $limit";
unless($adding_allowed && (!defined($owner) || $owner<=0)) {
- #if adding items, user should be known
- $pub= $dbh->selectall_arrayref($qry1,{Slice=>{}},@params);
+ #if adding items, user should be known
+ $pub= $dbh->selectall_arrayref($qry1,{Slice=>{}},@params);
}
if($owner) {
- my $qry2= $bquery. qq{
- LEFT JOIN virtualshelfshares sh ON sh.shelfnumber=vs.shelfnumber AND sh.borrowernumber=?
- WHERE vs.category=1 AND (vs.owner=? OR sh.borrowernumber=?) };
- @params=($owner,$owner,$owner);
- $qry2.= "AND (allow_add=1 OR owner=?) " if $adding_allowed;
- push @params, $owner if $adding_allowed;
- $qry2.= "ORDER BY vs.lastmodified DESC ";
- $qry2.= "LIMIT $limit";
- $bar= $dbh->selectall_arrayref($qry2,{Slice=>{}},@params);
+ my $qry2= $bquery. qq{
+ LEFT JOIN virtualshelfshares sh ON sh.shelfnumber=vs.shelfnumber AND sh.borrowernumber=?
+ WHERE vs.category=1 AND (vs.owner=? OR sh.borrowernumber=?) };
+ @params=($owner,$owner,$owner);
+ $qry2.= "AND (allow_add=1 OR owner=?) " if $adding_allowed;
+ push @params, $owner if $adding_allowed;
+ $qry2.= "ORDER BY vs.lastmodified DESC ";
+ $qry2.= "LIMIT $limit";
+ $bar= $dbh->selectall_arrayref($qry2,{Slice=>{}},@params);
}
return ( { bartotal => $bar? scalar @$bar: 0, pubtotal => $pub? scalar @$pub: 0}, $pub, $bar);
=head2 GetShelf
- (shelfnumber,shelfname,owner,category,sortfield) = &GetShelf($shelfnumber);
+ (shelfnumber,shelfname,owner,category,sortfield,allow_add,allow_delete_own,allow_delete_other) = &GetShelf($shelfnumber);
-Looks up information about the contents of virtual virtualshelves number
-C<$shelfnumber>
-
-Returns the database's information on 'virtualshelves' table.
+Returns the above-mentioned fields for passed virtual shelf number.
=cut
-sub GetShelf ($) {
+sub GetShelf {
my ($shelfnumber) = @_;
+ my $dbh = C4::Context->dbh;
my $query = qq(
- SELECT shelfnumber, shelfname, owner, category, sortfield
+ SELECT shelfnumber, shelfname, owner, category, sortfield,
+ allow_add, allow_delete_own, allow_delete_other
FROM virtualshelves
WHERE shelfnumber=?
);
=cut
-sub GetShelfContents ($;$$$) {
- my ($shelfnumber, $row_count, $offset, $sortfield) = @_;
+sub GetShelfContents {
+ my ($shelfnumber, $row_count, $offset, $sortfield, $sort_direction ) = @_;
my $dbh=C4::Context->dbh();
my $sth1 = $dbh->prepare("SELECT count(*) FROM virtualshelfcontents WHERE shelfnumber = ?");
$sth1->execute($shelfnumber);
($sortfield) = $sth2->fetchrow_array;
}
my $query =
- " SELECT vc.biblionumber, vc.shelfnumber, vc.dateadded, itemtypes.*,
+ " SELECT DISTINCT vc.biblionumber, vc.shelfnumber, vc.dateadded, itemtypes.*,
biblio.*, biblioitems.itemtype, biblioitems.publicationyear as year, biblioitems.publishercode, biblioitems.place, biblioitems.size, biblioitems.pages
FROM virtualshelfcontents vc
- LEFT JOIN biblio ON vc.biblionumber = biblio.biblionumber
+ JOIN biblio ON vc.biblionumber = biblio.biblionumber
LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber
+ LEFT JOIN items ON items.biblionumber=vc.biblionumber
LEFT JOIN itemtypes ON biblioitems.itemtype = itemtypes.itemtype
WHERE vc.shelfnumber=? ";
my @params = ($shelfnumber);
if($sortfield) {
- $query .= " ORDER BY " . $sortfield;
- $query .= " DESC " if ($sortfield eq 'copyrightdate');
+ $query .= " ORDER BY " . $dbh->quote_identifier( $sortfield );
+ $query .= " DESC " if ( $sort_direction eq 'desc' );
}
if($row_count){
$query .= " LIMIT ?, ? ";
sub AddShelf {
my ($hashref, $owner)= @_;
+ my $dbh = C4::Context->dbh;
#initialize missing hash values to silence warnings
foreach('shelfname','category', 'sortfield', 'allow_add', 'allow_delete_own', 'allow_delete_other' ) {
- $hashref->{$_}= exists $hashref->{$_}? $hashref->{$_}||'': '';
+ $hashref->{$_}= undef unless exists $hashref->{$_};
}
return -1 unless _CheckShelfName($hashref->{shelfname}, $hashref->{category}, $owner, 0);
my $sth = $dbh->prepare($query);
$sth->execute(
- $hashref->{shelfname},
- $owner,
- $hashref->{category},
- $hashref->{sortfield},
- $hashref->{allow_add}||0,
- $hashref->{allow_delete_own}||1,
- $hashref->{allow_delete_other}||0 );
+ $hashref->{shelfname},
+ $owner,
+ $hashref->{category},
+ $hashref->{sortfield},
+ $hashref->{allow_add}//0,
+ $hashref->{allow_delete_own}//1,
+ $hashref->{allow_delete_other}//0 );
+ return if $sth->err;
my $shelfnumber = $dbh->{'mysql_insertid'};
return $shelfnumber;
}
sub AddToShelf {
my ($biblionumber, $shelfnumber, $borrowernumber) = @_;
return unless $biblionumber;
+ my $dbh = C4::Context->dbh;
my $query = qq(
SELECT *
FROM virtualshelfcontents
my $sth = $dbh->prepare($query);
$sth->execute( $shelfnumber, $biblionumber );
- ($sth->rows) and return undef; # already on shelf
+ ($sth->rows) and return; # already on shelf
$query = qq(
INSERT INTO virtualshelfcontents
(shelfnumber, biblionumber, flags, borrowernumber)
sub ModShelf {
my ($shelfnumber,$hashref) = @_;
+ my $dbh = C4::Context->dbh;
my $query= "SELECT * FROM virtualshelves WHERE shelfnumber=?";
my $sth = $dbh->prepare($query);
#initialize missing hash values to silence warnings
foreach('shelfname','category', 'sortfield', 'allow_add', 'allow_delete_own', 'allow_delete_other' ) {
- $hashref->{$_}= exists $hashref->{$_}? $hashref->{$_}||'': '';
+ $hashref->{$_}= undef unless exists $hashref->{$_};
}
#if name or category changes, the name should be tested
if($hashref->{shelfname} || $hashref->{category}) {
unless(_CheckShelfName(
- $hashref->{shelfname}||$oldrecord->{shelfname},
- $hashref->{category}||$oldrecord->{category},
- $oldrecord->{owner}, $shelfnumber )) {
- return 0; #name check failed
+ $hashref->{shelfname}//$oldrecord->{shelfname},
+ $hashref->{category}//$oldrecord->{category},
+ $oldrecord->{owner},
+ $shelfnumber )) {
+ return 0; #name check failed
}
}
$query= "UPDATE virtualshelves SET shelfname=?, category=?, sortfield=?, allow_add=?, allow_delete_own=?, allow_delete_other=? WHERE shelfnumber=?";
$sth = $dbh->prepare($query);
$sth->execute(
- $hashref->{shelfname}||$oldrecord->{shelfname},
- $hashref->{category}||$oldrecord->{category},
- $hashref->{sortfield}||$oldrecord->{sortfield},
- $hashref->{allow_add}||$oldrecord->{allow_add},
- $hashref->{allow_delete_own}||$oldrecord->{allow_delete_own},
- $hashref->{allow_delete_other}||$oldrecord->{allow_delete_other},
- $shelfnumber );
+ $hashref->{shelfname}//$oldrecord->{shelfname},
+ $hashref->{category}//$oldrecord->{category},
+ $hashref->{sortfield}//$oldrecord->{sortfield},
+ $hashref->{allow_add}//$oldrecord->{allow_add},
+ $hashref->{allow_delete_own}//$oldrecord->{allow_delete_own},
+ $hashref->{allow_delete_other}//$oldrecord->{allow_delete_other},
+ $shelfnumber );
return $@? 0: 1;
}
C<$loggedinuser,$shelfnumber,$action>
$action can be "view", "add", "delete", "manage", "new_public", "new_private".
+New additional actions are: invite, acceptshare.
Note that add/delete here refers to adding/deleting entries from the list. Deleting the list itself falls under manage.
new_public and new_private refers to creating a new public or private list.
The distinction between deleting your own entries from the list or entries from
Returns 1 if the user can do the $action in the $shelfnumber shelf.
Returns 0 otherwise.
+For the actions invite and acceptshare a second errorcode is returned if the
+result is false. See opac-shareshelf.pl
=cut
return 0 unless defined($shelfnumber);
+ my $dbh = C4::Context->dbh;
my $query = qq/
- SELECT IFNULL(owner,0) AS owner, category, allow_add, allow_delete_own, allow_delete_other, IFNULL(sh.borrowernumber,0) AS borrowernumber
+ SELECT COALESCE(owner,0) AS owner, category, allow_add, allow_delete_own, allow_delete_other, COALESCE(sh.borrowernumber,0) AS borrowernumber
FROM virtualshelves vs
LEFT JOIN virtualshelfshares sh ON sh.shelfnumber=vs.shelfnumber
AND sh.borrowernumber=?
$sth->execute($user, $shelfnumber);
my $shelf= $sth->fetchrow_hashref;
- return 0 unless $shelf && ($shelf->{category}==2 || $shelf->{owner}==$user || $shelf->{borrowernumber}==$user);
+ return 0 unless $shelf && ($shelf->{category}==2 || $shelf->{owner}==$user || ($user && $shelf->{borrowernumber}==$user));
if($action eq 'view') {
- #already handled in the above condition
- return 1;
+ #already handled in the above condition
+ return 1;
}
elsif($action eq 'add') {
- return 0 if $user<=0; #should be logged in
- return 1 if $shelf->{allow_add}==1 || $shelf->{owner}==$user;
- #owner may always add
+ return 0 if $user<=0; #should be logged in
+ return 1 if $shelf->{allow_add}==1 || $shelf->{owner}==$user;
+ #owner may always add
}
elsif($action eq 'delete') {
#this answer is just diplomatic: it says that you may be able to delete
#some items from that shelf
#it does not answer the question about a specific biblio
#DelFromShelf checks the situation per biblio
- return 1 if $user>0 && ($shelf->{allow_delete_own}==1 || $shelf->{allow_delete_other}==1);
+ return 1 if $user>0 && ($shelf->{allow_delete_own}==1 || $shelf->{allow_delete_other}==1);
+ }
+ elsif($action eq 'invite') {
+ #for sharing you must be the owner and the list must be private
+ if( $shelf->{category}==1 ) {
+ return 1 if $shelf->{owner}==$user;
+ return (0, 4); # code 4: should be owner
+ }
+ else {
+ return (0, 5); # code 5: should be private list
+ }
+ }
+ elsif($action eq 'acceptshare') {
+ #the key for accepting is checked later in AcceptShare
+ #you must not be the owner, list must be private
+ if( $shelf->{category}==1 ) {
+ return (0, 8) if $shelf->{owner}==$user;
+ #code 8: should not be owner
+ return 1;
+ }
+ else {
+ return (0, 5); # code 5: should be private list
+ }
}
elsif($action eq 'manage') {
- return 1 if $user && $shelf->{owner}==$user;
+ return 1 if $user && $shelf->{owner}==$user;
}
return 0;
}
sub DelFromShelf {
my ($bibref, $shelfnumber, $user) = @_;
+ my $dbh = C4::Context->dbh;
my $query = qq(SELECT allow_delete_own, allow_delete_other FROM virtualshelves WHERE shelfnumber=?);
my $sth= $dbh->prepare($query);
$sth->execute($shelfnumber);
$query = qq(DELETE FROM virtualshelfcontents
WHERE shelfnumber=? AND biblionumber=? AND borrowernumber=?);
$sth= $dbh->prepare($query);
- foreach my $biblionumber (@$bibref) {
+ foreach my $biblionumber (@$bibref) {
$sth->execute($shelfnumber, $biblionumber, $user);
- $r= $sth->rows; #Expect -1, 0 or 1 (-1 means Don't know; count as 1)
- $t+= ($r==-1)? 1: $r;
- }
+ $r= $sth->rows; #Expect -1, 0 or 1 (-1 means Don't know; count as 1)
+ $t+= ($r==-1)? 1: $r;
+ }
}
if($del_oth) {
#includes a check if borrowernumber is null (deleted patron)
WHERE shelfnumber=? AND biblionumber=? AND
(borrowernumber IS NULL OR borrowernumber<>?)/;
$sth= $dbh->prepare($query);
- foreach my $biblionumber (@$bibref) {
+ foreach my $biblionumber (@$bibref) {
$sth->execute($shelfnumber, $biblionumber, $user);
- $r= $sth->rows;
- $t+= ($r==-1)? 1: $r;
- }
+ $r= $sth->rows;
+ $t+= ($r==-1)? 1: $r;
+ }
}
return $t;
}
sub DelShelf {
my ($shelfnumber)= @_;
return unless $shelfnumber && $shelfnumber =~ /^\d+$/;
+ my $dbh = C4::Context->dbh;
my $sth = $dbh->prepare("DELETE FROM virtualshelves WHERE shelfnumber=?");
return $sth->execute($shelfnumber);
}
=cut
sub ShelvesMax {
- my $which= shift;
- return SHELVES_POPUP_MAX if $which eq 'POPUP';
- return SHELVES_MGRPAGE_MAX if $which eq 'MGRPAGE';
- return SHELVES_COMBO_MAX if $which eq 'COMBO';
- return SHELVES_MASTHEAD_MAX if $which eq 'MASTHEAD';
- return SHELVES_MASTHEAD_MAX;
+ my $which= shift;
+ return SHELVES_POPUP_MAX if $which eq 'POPUP';
+ return SHELVES_MGRPAGE_MAX if $which eq 'MGRPAGE';
+ return SHELVES_COMBO_MAX if $which eq 'COMBO';
+ return SHELVES_MASTHEAD_MAX if $which eq 'MASTHEAD';
+ return SHELVES_MASTHEAD_MAX;
}
+=head2 HandleDelBorrower
+
+ HandleDelBorrower($borrower);
+
+When a member is deleted (DelMember in Members.pm), you should call me first.
+This routine deletes/moves lists and entries for the deleted member/borrower.
+Lists owned by the borrower are deleted, but entries from the borrower to
+other lists are kept.
+
+=cut
+
sub HandleDelBorrower {
-#when a member is deleted (DelMember in Members.pm), you should call me first
-#this routine deletes/moves lists and entries for the deleted member/borrower
-#you could just delete everything (and lose more than you want)
-#instead we now try to save all public/shared stuff and keep others happy
my ($borrower)= @_;
my $query;
my $dbh = C4::Context->dbh;
- #Delete shares of this borrower (not lists !)
- $query="DELETE FROM virtualshelfshares WHERE borrowernumber=?";
+ #Delete all lists and all shares of this borrower
+ #Consistent with the approach Koha uses on deleting individual lists
+ #Note that entries in virtualshelfcontents added by this borrower to
+ #lists of others will be handled by a table constraint: the borrower
+ #is set to NULL in those entries.
+ $query="DELETE FROM virtualshelves WHERE owner=?";
$dbh->do($query,undef,($borrower));
- #Delete private lists without owner that now have no shares anymore
- $query="DELETE vs.* FROM virtualshelves vs LEFT JOIN virtualshelfshares sh USING (shelfnumber) WHERE category=1 AND vs.owner IS NULL AND sh.shelfnumber IS NULL";
- $dbh->do($query);
+ #NOTE:
+ #We could handle the above deletes via a constraint too.
+ #But a new BZ report 11889 has been opened to discuss another approach.
+ #Instead of deleting we could also disown lists (based on a pref).
+ #In that way we could save shared and public lists.
+ #The current table constraints support that idea now.
+ #This pref should then govern the results of other routines such as
+ #DelShelf too.
+}
- #Change owner for private lists which have shares
- $query="UPDATE virtualshelves LEFT JOIN virtualshelfshares sh USING (shelfnumber) SET owner=NULL where owner=? AND category=1 AND sh.borrowernumber IS NOT NULL";
- $dbh->do($query,undef,($borrower));
+=head2 AddShare
- #Delete unshared private lists
- $query="DELETE FROM virtualshelves WHERE owner=? AND category=1";
- $dbh->do($query,undef,($borrower));
+ AddShare($shelfnumber, $key);
- #Handle public lists owned by borrower
- $query="UPDATE virtualshelves SET owner=NULL WHERE owner=? AND category=2";
- $dbh->do($query,undef,($borrower));
+Adds a share request to the virtualshelves table.
+Authorization must have been checked, and a key must be supplied. See script
+opac-shareshelf.pl for an example.
+This request is not yet confirmed. So it has no borrowernumber, it does have an
+expiry date.
- #Handle entries added by borrower to lists of others
- $query="UPDATE virtualshelfcontents SET borrowernumber=NULL WHERE borrowernumber=?";
- $dbh->do($query,undef,($borrower));
+=cut
+
+sub AddShare {
+ my ($shelfnumber, $key)= @_;
+ return if !$shelfnumber || !$key;
+
+ my $dbh = C4::Context->dbh;
+ my $sql = "INSERT INTO virtualshelfshares (shelfnumber, invitekey, sharedate) VALUES (?, ?, NOW())";
+ $dbh->do($sql, undef, ($shelfnumber, $key));
+ return !$dbh->err;
+}
+
+=head2 AcceptShare
+
+ my $result= AcceptShare($shelfnumber, $key, $borrowernumber);
+
+Checks acceptation of a share request.
+Key must be found for this shelf. Invitation must not have expired.
+Returns true when accepted, false otherwise.
+
+=cut
+
+sub AcceptShare {
+ my ($shelfnumber, $key, $borrowernumber)= @_;
+ return if !$shelfnumber || !$key || !$borrowernumber;
+
+ my $sql;
+ my $dbh = C4::Context->dbh;
+ $sql="
+UPDATE virtualshelfshares
+SET invitekey=NULL, sharedate=NOW(), borrowernumber=?
+WHERE shelfnumber=? AND invitekey=? AND (sharedate + INTERVAL ? DAY) >NOW()
+ ";
+ my $i= $dbh->do($sql, undef, ($borrowernumber, $shelfnumber, $key, SHARE_INVITATION_EXPIRY_DAYS));
+ return if !defined($i) || !$i || $i eq '0E0'; #not found
+ return 1;
+}
+
+=head2 IsSharedList
+
+ my $bool= IsSharedList( $shelfnumber );
+
+IsSharedList checks if a (private) list has shares.
+Note that such a check would not be useful for public lists. A public list has
+no shares, but is visible for anyone by nature..
+Used to determine the list type in the display of Your lists (all private).
+Returns boolean value.
+
+=cut
+
+sub IsSharedList {
+ my ($shelfnumber) = @_;
+ my $dbh = C4::Context->dbh;
+ my $sql="SELECT id FROM virtualshelfshares WHERE shelfnumber=? AND borrowernumber IS NOT NULL";
+ my $sth = $dbh->prepare($sql);
+ $sth->execute($shelfnumber);
+ my ($rv)= $sth->fetchrow_array;
+ return defined($rv);
+}
+
+=head2 RemoveShare
+
+ RemoveShare( $user, $shelfnumber );
+
+RemoveShare removes a share for specific shelf and borrower.
+Returns true if a record could be deleted.
+
+=cut
+
+sub RemoveShare {
+ my ($user, $shelfnumber)= @_;
+ my $dbh = C4::Context->dbh;
+ my $sql="
+DELETE FROM virtualshelfshares
+WHERE borrowernumber=? AND shelfnumber=?
+ ";
+ my $n= $dbh->do($sql,undef,($user, $shelfnumber));
+ return if !defined($n) || !$n || $n eq '0E0'; #nothing removed
+ return 1;
}
# internal subs
my @params;
# Find out how many shelves total meet the submitted criteria...
+ my $dbh = C4::Context->dbh;
my $query = "SELECT count(*) FROM virtualshelves vs ";
if($category==1) {
$query.= qq{
@params= ($owner, $owner, $owner);
}
else {
- $query.='WHERE category=2';
+ $query.='WHERE category=2';
@params= ();
}
my $sth = $dbh->prepare($query);
return $total;
}
-sub _biblionumber_sth { #only used in obsolete sub below
- my ($shelf) = @_;
- my $query = 'select biblionumber from virtualshelfcontents where shelfnumber = ?';
- my $dbh = C4::Context->dbh;
- my $sth = $dbh->prepare($query)
- or die $dbh->errstr;
- $sth->execute( $shelf )
- or die $sth->errstr;
- $sth;
-}
-
-sub each_biblionumbers (&$) { #OBSOLETE
- my ($code,$shelf) = @_;
- my $ref = _biblionumber_sth($shelf)->fetchall_arrayref;
- map {
- $_=$$_[0];
- $code->();
- } @$ref;
-}
-
sub _CheckShelfName {
my ($name, $cat, $owner, $number)= @_;
+ my $dbh = C4::Context->dbh;
+ my @pars;
my $query = qq(
SELECT DISTINCT shelfnumber
FROM virtualshelves
- LEFT JOIN virtualshelfshares sh USING (shelfnumber)
+ LEFT JOIN virtualshelfshares sh USING (shelfnumber)
WHERE shelfname=? AND shelfnumber<>?);
- if($cat==1) {
+ if($cat==1 && defined($owner)) {
$query.= ' AND (sh.borrowernumber=? OR owner=?) AND category=1';
+ @pars=($name, $number, $owner, $owner);
}
- else {
+ elsif($cat==1 && !defined($owner)) { #owner is null (exceptional)
+ $query.= ' AND owner IS NULL AND category=1';
+ @pars=($name, $number);
+ }
+ else { #public list
$query.= ' AND category=2';
+ @pars=($name, $number);
}
my $sth = $dbh->prepare($query);
- $sth->execute($cat==1? ($name, $number, $owner, $owner): ($name, $number));
+ $sth->execute(@pars);
return $sth->rows>0? 0: 1;
}
projects / koha_fer / blobdiff