X-Git-Url: http://git.rot13.org/?a=blobdiff_plain;f=C4%2FVirtualShelves.pm;h=38fa0e82a84a03715c799f2055a55f05c038c740;hb=9c5e40e4923179bf0b2b630d3e09797dc4c1fdb0;hp=8c0b15e275c0cd41b52ff37444127bb07c9de37c;hpb=4f44847c11add59ab7d0c55aeffac1fa2835cc1f;p=koha.git

diff --git a/C4/VirtualShelves.pm b/C4/VirtualShelves.pm
index 8c0b15e275..38fa0e82a8 100644
--- a/C4/VirtualShelves.pm
+++ b/C4/VirtualShelves.pm
@@ -50,8 +50,6 @@ BEGIN {
 }
 
 
-my $dbh = C4::Context->dbh;
-
 =head1 NAME
 
 C4::VirtualShelves - Functions for manipulating Koha virtual shelves
@@ -101,6 +99,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,
@@ -155,6 +154,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{
@@ -184,6 +184,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);
@@ -223,6 +224,7 @@ Returns the above-mentioned fields for passed virtual shelf number.
 
 sub GetShelf {
     my ($shelfnumber) = @_;
+    my $dbh = C4::Context->dbh;
     my $query = qq(
         SELECT shelfnumber, shelfname, owner, category, sortfield,
             allow_add, allow_delete_own, allow_delete_other
@@ -253,7 +255,7 @@ from C4::Circulation.
 =cut
 
 sub GetShelfContents {
-    my ($shelfnumber, $row_count, $offset, $sortfield) = @_;
+    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);
@@ -264,17 +266,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 ?, ? ";
@@ -306,6 +309,7 @@ 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' ) {
@@ -343,6 +347,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
@@ -382,6 +387,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);
@@ -453,8 +459,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=?
@@ -500,6 +507,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);
@@ -544,6 +552,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);
 }
@@ -588,16 +597,25 @@ sub ShelvesMax {
     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.
+You could just delete everything (and lose more than you want), but instead we
+now try to save all public/shared stuff and keep others happy.
+
+=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 !)
+    #Although this would be done later via the FK cascaded delete, we do it now.
+    #Because it makes the following delete statement on shelves more meaningful.
     $query="DELETE FROM virtualshelfshares WHERE borrowernumber=?";
     $dbh->do($query,undef,($borrower));
 
@@ -629,6 +647,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{
@@ -661,19 +680,27 @@ sub _biblionumber_sth { #only used in obsolete sub below
 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)
         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;
 }