X-Git-Url: http://koha-dev.rot13.org:8081/gitweb/?a=blobdiff_plain;f=C4%2FVirtualShelves.pm;h=70dc9657552ea8bbe8b3e5954afcf210c9d53745;hb=5d6c092921919526ade501facb1220f8a108a08f;hp=85dbc9f2bcadb3fbccd2b3f0959d366b8e9967bf;hpb=413a1e1e61b343db47b9b6fdecf3d981d87013ef;p=koha_fer diff --git a/C4/VirtualShelves.pm b/C4/VirtualShelves.pm index 85dbc9f2bc..70dc965755 100644 --- a/C4/VirtualShelves.pm +++ b/C4/VirtualShelves.pm @@ -1,9 +1,5 @@ -# -*- 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. @@ -33,11 +29,13 @@ use constant SHELVES_COMBO_MAX => 10; #add to combo in search 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( @@ -47,6 +45,7 @@ BEGIN { &ShelfPossibleAction &DelFromShelf &DelShelf &GetBibliosShelves + &AddShare &AcceptShare &RemoveShare &IsSharedList ); @EXPORT_OK = qw( &GetAllShelves &ShelvesMax @@ -54,8 +53,6 @@ BEGIN { } -my $dbh = C4::Context->dbh; - =head1 NAME C4::VirtualShelves - Functions for manipulating Koha virtual shelves @@ -105,6 +102,7 @@ sub GetShelves { 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, @@ -159,6 +157,7 @@ the submitted parameters. 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{ @@ -188,6 +187,7 @@ Returns shelf names and numbers for Add to combo of search results and Lists but 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); @@ -198,20 +198,20 @@ sub GetSomeShelfNames { $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); @@ -219,19 +219,18 @@ sub GetSomeShelfNames { =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=? ); @@ -258,8 +257,8 @@ from C4::Circulation. =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); @@ -270,17 +269,18 @@ sub GetShelfContents ($;$$$) { ($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 ?, ? "; @@ -312,10 +312,11 @@ Returns a code to know what's happen. 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); @@ -326,13 +327,14 @@ sub AddShelf { 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; } @@ -349,6 +351,7 @@ C<$shelfnumber>, unless that bib is already on that shelf. sub AddToShelf { my ($biblionumber, $shelfnumber, $borrowernumber) = @_; return unless $biblionumber; + my $dbh = C4::Context->dbh; my $query = qq( SELECT * FROM virtualshelfcontents @@ -357,7 +360,7 @@ sub AddToShelf { 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) @@ -388,6 +391,7 @@ Returns 1 if the action seemed to be successful. sub ModShelf { my ($shelfnumber,$hashref) = @_; + my $dbh = C4::Context->dbh; my $query= "SELECT * FROM virtualshelves WHERE shelfnumber=?"; my $sth = $dbh->prepare($query); @@ -397,16 +401,17 @@ sub ModShelf { #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 } } @@ -414,13 +419,13 @@ sub ModShelf { $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; } @@ -431,6 +436,7 @@ ShelfPossibleAction($loggedinuser, $shelfnumber, $action); 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 @@ -438,6 +444,8 @@ others is made in DelFromShelf. 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 @@ -458,8 +466,9 @@ sub ShelfPossibleAction { 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=? @@ -469,25 +478,47 @@ sub ShelfPossibleAction { $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; } @@ -505,6 +536,7 @@ Returns 0 if no items have been deleted. 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); @@ -515,11 +547,11 @@ sub DelFromShelf { $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) @@ -527,11 +559,11 @@ sub DelFromShelf { 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; } @@ -549,6 +581,7 @@ ShelfPossibleAction with manage parameter. 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); } @@ -585,46 +618,137 @@ main Koha toolbar with Lists button. =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 @@ -634,6 +758,7 @@ sub _shelf_count { 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{ @@ -643,7 +768,7 @@ sub _shelf_count { @params= ($owner, $owner, $owner); } else { - $query.='WHERE category=2'; + $query.='WHERE category=2'; @params= (); } my $sth = $dbh->prepare($query); @@ -652,42 +777,30 @@ sub _shelf_count { 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; }