X-Git-Url: http://git.rot13.org/?a=blobdiff_plain;f=C4%2FVirtualShelves.pm;h=38fa0e82a84a03715c799f2055a55f05c038c740;hb=9c5e40e4923179bf0b2b630d3e09797dc4c1fdb0;hp=85dbc9f2bcadb3fbccd2b3f0959d366b8e9967bf;hpb=0c40ff9f9814295fe27f52047bfbe4ef7b47e78b;p=koha.git diff --git a/C4/VirtualShelves.pm b/C4/VirtualShelves.pm index 85dbc9f2bc..38fa0e82a8 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. @@ -37,7 +33,7 @@ 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( @@ -54,8 +50,6 @@ BEGIN { } -my $dbh = C4::Context->dbh; - =head1 NAME C4::VirtualShelves - Functions for manipulating Koha virtual shelves @@ -105,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, @@ -159,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{ @@ -188,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); @@ -198,20 +195,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 +216,18 @@ sub GetSomeShelfNames { =head2 GetShelf - (shelfnumber,shelfname,owner,category,sortfield) = &GetShelf($shelfnumber); - -Looks up information about the contents of virtual virtualshelves number -C<$shelfnumber> + (shelfnumber,shelfname,owner,category,sortfield,allow_add,allow_delete_own,allow_delete_other) = &GetShelf($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 +254,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 +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 ?, ? "; @@ -312,10 +309,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 +324,13 @@ 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 ); my $shelfnumber = $dbh->{'mysql_insertid'}; return $shelfnumber; } @@ -349,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 @@ -357,7 +356,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 +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); @@ -397,16 +397,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 +415,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; } @@ -458,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=? @@ -469,25 +471,25 @@ 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 'manage') { - return 1 if $user && $shelf->{owner}==$user; + return 1 if $user && $shelf->{owner}==$user; } return 0; } @@ -505,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); @@ -515,11 +518,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 +530,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 +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); } @@ -585,24 +589,33 @@ 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. +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)); @@ -634,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{ @@ -643,7 +657,7 @@ sub _shelf_count { @params= ($owner, $owner, $owner); } else { - $query.='WHERE category=2'; + $query.='WHERE category=2'; @params= (); } my $sth = $dbh->prepare($query); @@ -657,37 +671,36 @@ sub _biblionumber_sth { #only used in obsolete sub below my $query = 'select biblionumber from virtualshelfcontents where shelfnumber = ?'; my $dbh = C4::Context->dbh; my $sth = $dbh->prepare($query) - or die $dbh->errstr; + or die $dbh->errstr; $sth->execute( $shelf ) - or die $sth->errstr; + 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; }