X-Git-Url: http://git.rot13.org/?a=blobdiff_plain;f=C4%2FVirtualShelves.pm;h=254d732413dc3dbeb9051830f650a3151677cbdb;hb=6807d7e3383e015ac787bcd327bfd8b512ea2a78;hp=06bddb1874767897402136232167bdcd374a35f7;hpb=06d8e7d4cf2622505fd062ea356d3f8a050abd37;p=koha.git

diff --git a/C4/VirtualShelves.pm b/C4/VirtualShelves.pm
index 06bddb1874..254d732413 100644
--- a/C4/VirtualShelves.pm
+++ b/C4/VirtualShelves.pm
@@ -1,10 +1,5 @@
-# -*- tab-width: 8 -*-
-# Please use 8-character tabs for this file (indents are every 4 characters)
-
 package C4::VirtualShelves;
 
-# $Id$
-
 # Copyright 2000-2002 Katipo Communications
 #
 # This file is part of Koha.
@@ -18,22 +13,46 @@ package C4::VirtualShelves;
 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
 # A PARTICULAR PURPOSE.  See the GNU General Public License for more details.
 #
-# You should have received a copy of the GNU General Public License along with
-# Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
-# Suite 330, Boston, MA  02111-1307 USA
+# You should have received a copy of the GNU General Public License along
+# with Koha; if not, write to the Free Software Foundation, Inc.,
+# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
 
 use strict;
-require Exporter;
+use warnings;
+
+use Carp;
 use C4::Context;
-use C4::Circulation;
-use vars qw($VERSION @ISA @EXPORT);
+use C4::Debug;
+
+use constant SHELVES_MASTHEAD_MAX => 10; #number under Lists button in masthead
+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 vars qw($VERSION @ISA @EXPORT @EXPORT_OK);
+
+BEGIN {
+    # set the version for version checking
+    $VERSION = 3.07.00.049;
+    require Exporter;
+    @ISA    = qw(Exporter);
+    @EXPORT = qw(
+            &GetShelves &GetShelfContents &GetShelf
+            &AddToShelf &AddShelf
+            &ModShelf
+            &ShelfPossibleAction
+            &DelFromShelf &DelShelf
+            &GetBibliosShelves
+    );
+        @EXPORT_OK = qw(
+            &GetAllShelves &ShelvesMax
+        );
+}
 
-# set the version for version checking
-$VERSION = do { my @v = '$Revision$' =~ /\d+/g; shift(@v) . "." . join( "_", map { sprintf "%03d", $_ } @v ); };
 
 =head1 NAME
 
-C4::VirtualShelves - Functions for manipulating Koha virtual virtualshelves
+C4::VirtualShelves - Functions for manipulating Koha virtual shelves
 
 =head1 SYNOPSIS
 
@@ -41,43 +60,28 @@ C4::VirtualShelves - Functions for manipulating Koha virtual virtualshelves
 
 =head1 DESCRIPTION
 
-This module provides functions for manipulating virtual virtualshelves,
-including creating and deleting virtualshelves, and adding and removing
-items to and from virtualshelves.
+This module provides functions for manipulating virtual shelves,
+including creating and deleting virtual shelves, and adding and removing
+bibs to and from virtual shelves.
 
 =head1 FUNCTIONS
 
-=over 2
-
-=cut
-
-@ISA    = qw(Exporter);
-@EXPORT = qw(
-        &GetShelves &GetShelfContents &GetShelf
-
-        &AddToShelf &AddToShelfFromBiblio &AddShelf
+=head2 GetShelves
 
-        &ModShelf
-        &ShelfPossibleAction
-        &DelFromShelf &DelShelf
-);
-
-my $dbh = C4::Context->dbh;
-
-=item GetShelves
-
-  $shelflist = &GetShelves($owner, $mincategory);
+  ($shelflist, $totshelves) = &GetShelves($category, $row_count, $offset, $owner);
   ($shelfnumber, $shelfhash) = each %{$shelflist};
 
-Looks up the virtual virtualshelves, and returns a summary. C<$shelflist>
-is a reference-to-hash. The keys are the virtualshelves numbers
-(C<$shelfnumber>, above), and the values (C<$shelfhash>, above) are
-themselves references-to-hash, with the following keys:
+Returns the number of shelves specified by C<$row_count> and C<$offset> as well as the total
+number of shelves that meet the C<$owner> and C<$category> criteria.  C<$category>,
+C<$row_count>, and C<$offset> are required. C<$owner> must be supplied when C<$category> == 1.
+When C<$category> is 2, supply undef as argument for C<$owner>.
 
-C<mincategory> : 2 if the list is for "look". 3 if the list is for "Select virtualshelves for adding a virtual".
-virtualshelves of the owner are always selected, whatever the category
+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.
 
-=over 4
+C<$shelflist>is a reference-to-hash. The keys are the virtualshelves numbers (C<$shelfnumber>, above),
+and the values (C<$shelfhash>, above) are themselves references-to-hash, with the following keys:
+
+=over
 
 =item C<$shelfhash-E<gt>{shelfname}>
 
@@ -91,60 +95,139 @@ The number of virtuals on that virtualshelves.
 
 =cut
 
-#'
-# FIXME - Wouldn't it be more intuitive to return a list, rather than
-# a reference-to-hash? The shelf number can be just another key in the
-# hash.
-
 sub GetShelves {
-    my ( $owner, $mincategory ) = @_;
+    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,
+        count(vc.biblionumber) as count
+        FROM virtualshelves vs
+        LEFT JOIN borrowers bo ON vs.owner=bo.borrowernumber
+        LEFT JOIN virtualshelfcontents vc USING (shelfnumber) };
+    if($category==1) {
+        $query.= qq{
+            LEFT JOIN virtualshelfshares sh ON sh.shelfnumber=vs.shelfnumber
+            AND sh.borrowernumber=?
+        WHERE category=1 AND (vs.owner=? OR sh.borrowernumber=?) };
+        @params= ($owner, $owner, $owner, $offset||0, $row_count);
+    }
+    else {
+        $query.= 'WHERE category=2 ';
+        @params= ($offset||0, $row_count);
+    }
+    $query.= qq{
+        GROUP BY vs.shelfnumber
+        ORDER BY vs.shelfname
+        LIMIT ?, ?};
 
-    my $query = qq(
-        SELECT virtualshelves.shelfnumber, virtualshelves.shelfname,owner,surname,firstname,virtualshelves.category,
-               count(virtualshelfcontents.biblionumber) as count
-        FROM   virtualshelves
-            LEFT JOIN   virtualshelfcontents ON virtualshelves.shelfnumber = virtualshelfcontents.shelfnumber
-            LEFT JOIN   borrowers ON virtualshelves.owner = borrowers.borrowernumber
-        WHERE  owner=? OR category>=?
-        GROUP BY virtualshelves.shelfnumber
-        ORDER BY virtualshelves.category, virtualshelves.shelfname, borrowers.firstname, borrowers.surname
-    );
-    my $sth = $dbh->prepare($query);
-    $sth->execute( $owner, $mincategory );
+    my $sth2 = $dbh->prepare($query);
+    $sth2->execute(@params);
     my %shelflist;
-    while (
-        my (
-            $shelfnumber, $shelfname, $owner, $surname,
-            $firstname,   $category,  $count
-        )
-        = $sth->fetchrow
-      )
-    {
+    while( my ($shelfnumber, $shelfname, $owner, $surname, $firstname, $category, $sortfield, $count)= $sth2->fetchrow) {
         $shelflist{$shelfnumber}->{'shelfname'} = $shelfname;
         $shelflist{$shelfnumber}->{'count'}     = $count;
+        $shelflist{$shelfnumber}->{'single'}    = $count==1;
+        $shelflist{$shelfnumber}->{'sortfield'} = $sortfield;
         $shelflist{$shelfnumber}->{'category'}  = $category;
         $shelflist{$shelfnumber}->{'owner'}     = $owner;
-        $shelflist{$shelfnumber}->{'surname'}     = $surname;
-        $shelflist{$shelfnumber}->{'firstname'}   = $firstname;
+        $shelflist{$shelfnumber}->{'surname'}   = $surname;
+        $shelflist{$shelfnumber}->{'firstname'} = $firstname;
     }
-    return ( \%shelflist );
+    return ( \%shelflist, $total );
 }
 
-=item GetShef
+=head2 GetAllShelves
 
-  (shelfnumber,shelfname,owner,category) = &GetShelf($shelfnumber);
+    $shelflist = GetAllShelves($category, $owner)
 
-Looks up information about the contents of virtual virtualshelves number
-C<$shelfnumber>
+This function returns a reference to an array of hashrefs containing all shelves
+sorted by the shelf name.
+
+This function is intended to return a dataset reflecting all the shelves for
+the submitted parameters.
+
+=cut
+
+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{
+            LEFT JOIN virtualshelfshares sh ON sh.shelfnumber=vs.shelfnumber
+            AND sh.borrowernumber=?
+        WHERE category=1 AND (vs.owner=? OR sh.borrowernumber=?) };
+        @params = ($owner, $owner, $owner);
+    }
+    else {
+    $query.='WHERE category=2 ';
+        @params = ();
+    }
+    $query.='AND (allow_add=1 OR owner=?) ' if $adding_allowed;
+    push @params, $owner if $adding_allowed;
+    $query.= 'ORDER BY shelfname ASC';
+    my $sth = $dbh->prepare( $query );
+    $sth->execute(@params);
+    return $sth->fetchall_arrayref({});
+}
+
+=head2 GetSomeShelfNames
+
+Returns shelf names and numbers for Add to combo of search results and Lists button of OPAC header.
+
+=cut
+
+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);
+
+    my $qry1= $bquery."WHERE vs.category=2 ";
+    $qry1.= "AND (allow_add=1 OR owner=?) " if $adding_allowed;
+    push @params, $owner||0 if $adding_allowed;
+    $qry1.= "ORDER BY vs.lastmodified DESC LIMIT $limit";
 
-Returns the database's information on 'virtualshelves' table.
+    unless($adding_allowed && (!defined($owner) || $owner<=0)) {
+        #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);
+    }
+
+    return ( { bartotal => $bar? scalar @$bar: 0, pubtotal => $pub? scalar @$pub: 0}, $pub, $bar);
+}
+
+=head2 GetShelf
+
+  (shelfnumber,shelfname,owner,category,sortfield,allow_add,allow_delete_own,allow_delete_other) = &GetShelf($shelfnumber);
+
+Returns the above-mentioned fields for passed virtual shelf number.
 
 =cut
 
 sub GetShelf {
     my ($shelfnumber) = @_;
+    my $dbh = C4::Context->dbh;
     my $query = qq(
-        SELECT shelfnumber,shelfname,owner,category
+        SELECT shelfnumber, shelfname, owner, category, sortfield,
+            allow_add, allow_delete_own, allow_delete_other
         FROM   virtualshelves
         WHERE  shelfnumber=?
     );
@@ -153,95 +236,118 @@ sub GetShelf {
     return $sth->fetchrow;
 }
 
-=item GetShelfContents
+=head2 GetShelfContents
 
-  $itemlist = &GetShelfContents($shelfnumber);
+  $biblist = &GetShelfContents($shelfnumber);
 
 Looks up information about the contents of virtual virtualshelves number
-C<$shelfnumber>.
+C<$shelfnumber>.  Sorted by a field in the biblio table.  copyrightdate 
+gives a desc sort.
 
 Returns a reference-to-array, whose elements are references-to-hash,
 as returned by C<C4::Biblio::GetBiblioFromItemNumber>.
 
+Note: the notforloan status comes from the itemtype, and where it equals 0
+it does not ensure that related items.notforloan status is likewise 0. The
+caller has to check any items on their own, possibly with CanBookBeIssued
+from C4::Circulation.
+
 =cut
 
-#'
 sub GetShelfContents {
-    my ( $shelfnumber ) = @_;
-    my @itemlist;
+    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);
+    my $total = $sth1->fetchrow;
+    if(!$sortfield) {
+        my $sth2 = $dbh->prepare('SELECT sortfield FROM virtualshelves WHERE shelfnumber=?');
+        $sth2->execute($shelfnumber);
+        ($sortfield) = $sth2->fetchrow_array;
+    }
     my $query =
-       " SELECT biblionumber
-         FROM   virtualshelfcontents
-         WHERE  shelfnumber=?
-         ORDER BY biblionumber
-       ";
-    my $sth = $dbh->prepare($query);
-    $sth->execute($shelfnumber);
-    my $sth2 = $dbh->prepare("
-        SELECT biblio.*,biblioitems.* FROM biblio
-            LEFT JOIN biblioitems on biblio.biblionumber=biblioitems.biblionumber
-        WHERE biblio.biblionumber=?"
-    );
-    while ( my ($biblionumber) = $sth->fetchrow ) {
-        $sth2->execute($biblionumber);
-        my $item = $sth2->fetchrow_hashref;
-        $item->{'biblionumber'}=$biblionumber;
-        push( @itemlist, $item );
+       " 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
+         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 " . $dbh->quote_identifier( $sortfield );
+        $query .= " DESC " if ( $sort_direction eq 'desc' );
     }
-    return ( \@itemlist );
+    if($row_count){
+       $query .= " LIMIT ?, ? ";
+       push (@params, ($offset ? $offset : 0));
+       push (@params, $row_count);
+    }
+    my $sth3 = $dbh->prepare($query);
+    $sth3->execute(@params);
+    return ($sth3->fetchall_arrayref({}), $total);
+    # Like the perldoc says,
+    # returns reference-to-array, where each element is reference-to-hash of the row:
+    #   like [ $sth->fetchrow_hashref(), $sth->fetchrow_hashref() ... ]
+    # Suitable for use in TMPL_LOOP.
+    # See http://search.cpan.org/~timb/DBI-1.601/DBI.pm#fetchall_arrayref
+    # or newer, for your version of DBI.
 }
 
-=item AddShelf
+=head2 AddShelf
 
-  $shelfnumber = &AddShelf( $shelfname, $owner, $category);
+  $shelfnumber = &AddShelf($hashref, $owner);
 
-Creates a new virtual virtualshelves with name C<$shelfname>, owner C<$owner> and category
-C<$category>.
+Creates a new virtual shelf. Params passed in a hash like ModShelf.
 
 Returns a code to know what's happen.
-    * -1 : if this virtualshelves already exist.
+    * -1 : if this virtualshelves already exists.
     * $shelfnumber : if success.
 
 =cut
 
 sub AddShelf {
-    my ( $shelfname, $owner, $category ) = @_;
-    my $query = qq(
-        SELECT *
-        FROM   virtualshelves
-        WHERE  shelfname=? AND owner=?
-    );
-    my $sth = $dbh->prepare($query);
-    $sth->execute($shelfname,$owner);
-    if ( $sth->rows ) {
-        return (-1);
-    }
-    else {
-        my $query = qq(
-            INSERT INTO virtualshelves
-                (shelfname,owner,category)
-            VALUES (?,?,?)
-        );
-        $sth = $dbh->prepare($query);
-        $sth->execute( $shelfname, $owner, $category );
-        my $shelfnumber = $dbh->{'mysql_insertid'};
-        return ($shelfnumber);
+    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->{$_}= undef unless exists $hashref->{$_};
     }
+
+    return -1 unless _CheckShelfName($hashref->{shelfname}, $hashref->{category}, $owner, 0);
+
+    my $query = qq(INSERT INTO virtualshelves
+        (shelfname,owner,category,sortfield,allow_add,allow_delete_own,allow_delete_other)
+        VALUES (?,?,?,?,?,?,?));
+
+    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 );
+    my $shelfnumber = $dbh->{'mysql_insertid'};
+    return $shelfnumber;
 }
 
-=item AddToShelf
+=head2 AddToShelf
 
-  &AddToShelf($biblionumber, $shelfnumber);
+  &AddToShelf($biblionumber, $shelfnumber, $borrower);
 
-Adds item number C<$biblionumber> to virtual virtualshelves number
-C<$shelfnumber>, unless that item is already on that shelf.
+Adds bib number C<$biblionumber> to virtual virtualshelves number
+C<$shelfnumber>, unless that bib is already on that shelf.
 
 =cut
 
-#'
 sub AddToShelf {
-    my ( $biblionumber, $shelfnumber ) = @_;
+    my ($biblionumber, $shelfnumber, $borrowernumber) = @_;
     return unless $biblionumber;
+    my $dbh = C4::Context->dbh;
     my $query = qq(
         SELECT *
         FROM   virtualshelfcontents
@@ -250,90 +356,86 @@ sub AddToShelf {
     my $sth = $dbh->prepare($query);
 
     $sth->execute( $shelfnumber, $biblionumber );
-    unless ( $sth->rows ) {
-        # already on shelf
-        my $query = qq(
-            INSERT INTO virtualshelfcontents
-                (shelfnumber, biblionumber, flags)
-            VALUES
-                (?, ?, 0)
-        );
-        $sth = $dbh->prepare($query);
-        $sth->execute( $shelfnumber, $biblionumber );
-    }
+    ($sth->rows) and return; # already on shelf
+    $query = qq(
+        INSERT INTO virtualshelfcontents
+            (shelfnumber, biblionumber, flags, borrowernumber)
+        VALUES (?, ?, 0, ?));
+    $sth = $dbh->prepare($query);
+    $sth->execute( $shelfnumber, $biblionumber, $borrowernumber);
+    $query = qq(UPDATE virtualshelves
+                SET lastmodified = CURRENT_TIMESTAMP
+                WHERE shelfnumber = ?);
+    $sth = $dbh->prepare($query);
+    $sth->execute( $shelfnumber );
 }
 
-=item AddToShelfFromBiblio
- 
-    &AddToShelfFromBiblio($biblionumber, $shelfnumber)
+=head2 ModShelf
 
-    this function allow to add a virtual into the shelf number $shelfnumber
-    from biblionumber.
+my $result= ModShelf($shelfnumber, $hashref)
 
-=cut
+Where $hashref->{column} = param
 
-sub AddToShelfFromBiblio {
-    my ( $biblionumber, $shelfnumber ) = @_;
-    return unless $biblionumber;
-    my $query = qq(
-        SELECT *
-        FROM   virtualshelfcontents
-        WHERE  shelfnumber=? AND biblionumber=?
-    );
-    my $sth = $dbh->prepare($query);
-    $sth->execute( $shelfnumber, $biblionumber );
-    unless ( $sth->rows ) {
-        my $query =qq(
-            INSERT INTO virtualshelfcontents
-                (shelfnumber, biblionumber, flags)
-            VALUES
-                (?, ?, 0)
-        );
-        $sth = $dbh->prepare($query);
-        $sth->execute( $shelfnumber, $biblionumber );
-    }
-}
-
-=item ModShelf
-
-ModShelf($shelfnumber, $shelfname, $owner, $category )
+Modify the value into virtualshelves table with values given 
+from hashref, which each key of the hashref should be
+the name of a column of virtualshelves.
+Fields like shelfnumber or owner cannot be changed.
 
-Modify the value into virtualshelves table with values given on input arg.
+Returns 1 if the action seemed to be successful.
 
 =cut
 
 sub ModShelf {
-    my ( $shelfnumber, $shelfname, $owner, $category ) = @_;
-    my $query = qq(
-        UPDATE virtualshelves
-        SET    shelfname=?,owner=?,category=?
-        WHERE  shelfnumber=?
-    );
-    my $sth = $dbh->prepare($query);
-    $sth->execute( $shelfname, $owner, $category, $shelfnumber );
-}
+    my ($shelfnumber,$hashref) = @_;
+    my $dbh = C4::Context->dbh;
 
-=item DelShelf
-
-  ($status) = &DelShelf($shelfnumber);
-
-Deletes virtual virtualshelves number C<$shelfnumber>. The virtualshelves must
-be empty.
+    my $query= "SELECT * FROM virtualshelves WHERE shelfnumber=?";
+    my $sth = $dbh->prepare($query);
+    $sth->execute($shelfnumber);
+    my $oldrecord= $sth->fetchrow_hashref;
+    return 0 unless $oldrecord; #not found?
 
-Returns a two-element array, where C<$status> is 0 if the operation
-was successful, or non-zero otherwise. C<$msg> is "Done" in case of
-success, or an error message giving the reason for failure.
+    #initialize missing hash values to silence warnings
+    foreach('shelfname','category', 'sortfield', 'allow_add', 'allow_delete_own', 'allow_delete_other' ) {
+        $hashref->{$_}= undef unless exists $hashref->{$_};
+    }
 
-=cut
+    #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
+        }
+    }
 
+    #only the following fields from the hash may be changed
+    $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 );
+    return $@? 0: 1;
+}
 
-=item ShelfPossibleAction
+=head2 ShelfPossibleAction
 
 ShelfPossibleAction($loggedinuser, $shelfnumber, $action);
 
 C<$loggedinuser,$shelfnumber,$action>
 
-$action can be "view" or "manage".
+$action can be "view", "add", "delete", "manage", "new_public", "new_private".
+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
+others is made in DelFromShelf.
 
 Returns 1 if the user can do the $action in the $shelfnumber shelf.
 Returns 0 otherwise.
@@ -342,67 +444,257 @@ Returns 0 otherwise.
 
 sub ShelfPossibleAction {
     my ( $user, $shelfnumber, $action ) = @_;
-    my $query = qq(
-        SELECT owner,category
-        FROM   virtualshelves
-        WHERE  shelfnumber=?
-    );
+    $action= 'view' unless $action;
+    $user=0 unless $user;
+
+    if($action =~ /^new/) { #no shelfnumber needed
+        if($action eq 'new_private') {
+            return $user>0;
+        }
+        elsif($action eq 'new_public') {
+            return $user>0 && C4::Context->preference('OpacAllowPublicListCreation');
+        }
+        return 0;
+    }
+
+    return 0 unless defined($shelfnumber);
+
+    my $dbh = C4::Context->dbh;
+    my $query = qq/
+        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=?
+        WHERE vs.shelfnumber=?
+    /;
     my $sth = $dbh->prepare($query);
-    $sth->execute($shelfnumber);
-    my ( $owner, $category ) = $sth->fetchrow;
-    return 1 if (($category >= 3 or $owner eq $user) && $action eq 'manage' );
-    return 1 if (($category >= 2 or $owner eq $user) && $action eq 'view' );
+    $sth->execute($user, $shelfnumber);
+    my $shelf= $sth->fetchrow_hashref;
+
+    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;
+    }
+    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
+    }
+    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);
+    }
+    elsif($action eq 'manage') {
+        return 1 if $user && $shelf->{owner}==$user;
+    }
     return 0;
 }
 
-=item DelFromShelf
+=head2 DelFromShelf
 
-  &DelFromShelf( $biblionumber, $shelfnumber);
+    $result= &DelFromShelf( $bibref, $shelfnumber, $user);
 
-Removes item number C<$biblionumber> from virtual virtualshelves number
-C<$shelfnumber>. If the item wasn't on that virtualshelves to begin with,
-nothing happens.
+Removes biblionumbers in passed arrayref from shelf C<$shelfnumber>.
+If the bib wasn't on that virtualshelves to begin with, nothing happens.
+
+Returns 0 if no items have been deleted.
 
 =cut
 
-#'
 sub DelFromShelf {
-    my ( $biblionumber, $shelfnumber ) = @_;
-    my $query = qq(
-        DELETE FROM virtualshelfcontents
-        WHERE  shelfnumber=? AND biblionumber=?
-    );
-    my $sth = $dbh->prepare($query);
-    $sth->execute( $shelfnumber, $biblionumber );
+    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);
+    my ($del_own, $del_oth)= $sth->fetchrow;
+    my $r; my $t=0;
+
+    if($del_own) {
+        $query = qq(DELETE FROM virtualshelfcontents
+            WHERE shelfnumber=? AND biblionumber=? AND borrowernumber=?);
+        $sth= $dbh->prepare($query);
+        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;
+        }
+    }
+    if($del_oth) {
+        #includes a check if borrowernumber is null (deleted patron)
+        $query = qq/DELETE FROM virtualshelfcontents
+            WHERE shelfnumber=? AND biblionumber=? AND
+            (borrowernumber IS NULL OR borrowernumber<>?)/;
+        $sth= $dbh->prepare($query);
+        foreach my $biblionumber (@$bibref) {
+            $sth->execute($shelfnumber, $biblionumber, $user);
+            $r= $sth->rows;
+            $t+= ($r==-1)? 1: $r;
+        }
+    }
+    return $t;
 }
 
 =head2 DelShelf
 
   $Number = DelShelf($shelfnumber);
 
-    this function delete the shelf number, and all of it's content
+This function deletes the shelf number, and all of it's content.
+Authorization to do so MUST have been checked before calling, while using
+ShelfPossibleAction with manage parameter.
 
 =cut
 
-#'
 sub DelShelf {
-    my ( $shelfnumber ) = @_;
-        my $sth = $dbh->prepare("DELETE FROM virtualshelves WHERE shelfnumber=?");
-        $sth->execute($shelfnumber);
-        return 0;
+    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);
+}
+
+=head2 GetBibliosShelves
+
+This finds all the public lists that this bib record is in.
+
+=cut
+
+sub GetBibliosShelves {
+    my ( $biblionumber )  = @_;
+    my $dbh = C4::Context->dbh;
+    my $sth = $dbh->prepare('
+        SELECT vs.shelfname, vs.shelfnumber 
+        FROM virtualshelves vs 
+        JOIN virtualshelfcontents vc ON (vs.shelfnumber= vc.shelfnumber) 
+        WHERE vs.category=2
+        AND vc.biblionumber= ?
+    ');
+    $sth->execute( $biblionumber );
+    return $sth->fetchall_arrayref({});
 }
 
-END { }    # module clean-up code here (global destructor)
+=head2 ShelvesMax
+
+    $howmany= ShelvesMax($context);
+
+Tells how much shelves are shown in which context.
+POPUP refers to addbybiblionumber popup, MGRPAGE is managing page (in opac or
+staff), COMBO refers to the Add to-combo of search results. MASTHEAD is the
+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;
+}
+
+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=?";
+    $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);
+
+    #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));
+
+    #Delete unshared private lists
+    $query="DELETE FROM virtualshelves WHERE owner=? AND category=1";
+    $dbh->do($query,undef,($borrower));
+
+    #Handle public lists owned by borrower
+    $query="UPDATE virtualshelves SET owner=NULL WHERE owner=? AND category=2";
+    $dbh->do($query,undef,($borrower));
+
+    #Handle entries added by borrower to lists of others
+    $query="UPDATE virtualshelfcontents SET borrowernumber=NULL WHERE borrowernumber=?";
+    $dbh->do($query,undef,($borrower));
+}
+
+# internal subs
+
+sub _shelf_count {
+    my ($owner, $category) = @_;
+    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{
+            LEFT JOIN virtualshelfshares sh ON sh.shelfnumber=vs.shelfnumber
+            AND sh.borrowernumber=?
+        WHERE category=1 AND (vs.owner=? OR sh.borrowernumber=?) };
+        @params= ($owner, $owner, $owner);
+    }
+    else {
+        $query.='WHERE category=2';
+        @params= ();
+    }
+    my $sth = $dbh->prepare($query);
+    $sth->execute(@params);
+    my ($total)= $sth->fetchrow;
+    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 _CheckShelfName {
+    my ($name, $cat, $owner, $number)= @_;
+
+    my $dbh = C4::Context->dbh;
+    my $query = qq(
+        SELECT DISTINCT shelfnumber
+        FROM   virtualshelves
+        LEFT JOIN virtualshelfshares sh USING (shelfnumber)
+        WHERE  shelfname=? AND shelfnumber<>?);
+    if($cat==1) {
+        $query.= ' AND (sh.borrowernumber=? OR owner=?) AND category=1';
+    }
+    else {
+        $query.= ' AND category=2';
+    }
+    my $sth = $dbh->prepare($query);
+    $sth->execute($cat==1? ($name, $number, $owner, $owner): ($name, $number));
+    return $sth->rows>0? 0: 1;
+}
 
 1;
 
 __END__
 
-=back
-
 =head1 AUTHOR
 
-Koha Developement team <info@koha.org>
+Koha Development Team <http://koha-community.org/>
 
 =head1 SEE ALSO