1 package C4::VirtualShelves;
3 # Copyright 2000-2002 Katipo Communications
5 # This file is part of Koha.
7 # Koha is free software; you can redistribute it and/or modify it
8 # under the terms of the GNU General Public License as published by
9 # the Free Software Foundation; either version 3 of the License, or
10 # (at your option) any later version.
12 # Koha is distributed in the hope that it will be useful, but
13 # WITHOUT ANY WARRANTY; without even the implied warranty of
14 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15 # GNU General Public License for more details.
17 # You should have received a copy of the GNU General Public License
18 # along with Koha; if not, see <http://www.gnu.org/licenses>.
28 use constant SHELVES_MASTHEAD_MAX => 10; #number under Lists button in masthead
29 use constant SHELVES_COMBO_MAX => 10; #add to combo in search
30 use constant SHELVES_MGRPAGE_MAX => 20; #managing page
31 use constant SHELVES_POPUP_MAX => 40; #addbybiblio popup
33 use constant SHARE_INVITATION_EXPIRY_DAYS => 14; #two weeks to accept
35 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK);
38 # set the version for version checking
39 $VERSION = 3.07.00.049;
43 &GetShelves &GetShelfContents
57 C4::VirtualShelves - Functions for manipulating Koha virtual shelves
61 use C4::VirtualShelves;
65 This module provides functions for manipulating virtual shelves,
66 including creating and deleting virtual shelves, and adding and removing
67 bibs to and from virtual shelves.
73 $shelflist = &GetShelves($category, $row_count, $offset, $owner);
74 ($shelfnumber, $shelfhash) = each %{$shelflist};
76 Returns the number of shelves specified by C<$row_count> and C<$offset> as well as the total
77 number of shelves that meet the C<$owner> and C<$category> criteria. C<$category>,
78 C<$row_count>, and C<$offset> are required. C<$owner> must be supplied when C<$category> == 1.
79 When C<$category> is 2, supply undef as argument for C<$owner>.
81 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.
83 C<$shelflist>is a reference-to-hash. The keys are the virtualshelves numbers (C<$shelfnumber>, above),
84 and the values (C<$shelfhash>, above) are themselves references-to-hash, with the following keys:
88 =item C<$shelfhash-E<gt>{shelfname}>
90 A string. The name of the shelf.
97 my ($category, $row_count, $offset, $owner) = @_;
99 my @params = ( $offset, $row_count );
100 my $dbh = C4::Context->dbh;
102 SELECT vs.shelfnumber, vs.shelfname,vs.owner,
103 bo.surname,bo.firstname,vs.category,vs.sortfield,
104 count(vc.biblionumber) as count
105 FROM virtualshelves vs
106 LEFT JOIN borrowers bo ON vs.owner=bo.borrowernumber
107 LEFT JOIN virtualshelfcontents vc USING (shelfnumber) };
110 LEFT JOIN virtualshelfshares sh ON sh.shelfnumber=vs.shelfnumber
111 AND sh.borrowernumber=?
112 WHERE category=1 AND (vs.owner=? OR sh.borrowernumber=?) };
113 unshift @params, ($owner) x 3;
116 $query.= 'WHERE category=2 ';
119 GROUP BY vs.shelfnumber
120 ORDER BY vs.shelfname
123 my $sth2 = $dbh->prepare($query);
124 $sth2->execute(@params);
126 while( my ($shelfnumber, $shelfname, $owner, $surname, $firstname, $category, $sortfield, $count)= $sth2->fetchrow) {
127 $shelflist{$shelfnumber}->{'shelfname'} = $shelfname;
128 $shelflist{$shelfnumber}->{'count'} = $count;
129 $shelflist{$shelfnumber}->{'single'} = $count==1;
130 $shelflist{$shelfnumber}->{'sortfield'} = $sortfield;
131 $shelflist{$shelfnumber}->{'category'} = $category;
132 $shelflist{$shelfnumber}->{'owner'} = $owner;
133 $shelflist{$shelfnumber}->{'surname'} = $surname;
134 $shelflist{$shelfnumber}->{'firstname'} = $firstname;
139 =head2 GetSomeShelfNames
141 Returns shelf names and numbers for Add to combo of search results and Lists button of OPAC header.
145 sub GetSomeShelfNames {
146 my ($owner, $purpose, $adding_allowed)= @_;
147 my ($bar, $pub, @params);
148 my $dbh = C4::Context->dbh;
150 my $bquery = 'SELECT vs.shelfnumber, vs.shelfname FROM virtualshelves vs ';
151 my $limit= ShelvesMax($purpose);
153 my $qry1= $bquery."WHERE vs.category=2 ";
154 $qry1.= "AND (allow_add=1 OR owner=?) " if $adding_allowed;
155 push @params, $owner||0 if $adding_allowed;
156 $qry1.= "ORDER BY vs.lastmodified DESC LIMIT $limit";
158 unless($adding_allowed && (!defined($owner) || $owner<=0)) {
159 #if adding items, user should be known
160 $pub= $dbh->selectall_arrayref($qry1,{Slice=>{}},@params);
164 my $qry2= $bquery. qq{
165 LEFT JOIN virtualshelfshares sh ON sh.shelfnumber=vs.shelfnumber AND sh.borrowernumber=?
166 WHERE vs.category=1 AND (vs.owner=? OR sh.borrowernumber=?) };
167 @params=($owner,$owner,$owner);
168 $qry2.= "AND (allow_add=1 OR owner=?) " if $adding_allowed;
169 push @params, $owner if $adding_allowed;
170 $qry2.= "ORDER BY vs.lastmodified DESC ";
171 $qry2.= "LIMIT $limit";
172 $bar= $dbh->selectall_arrayref($qry2,{Slice=>{}},@params);
175 return ( { bartotal => $bar? scalar @$bar: 0, pubtotal => $pub? scalar @$pub: 0}, $pub, $bar);
178 =head2 GetShelfContents
180 $biblist = &GetShelfContents($shelfnumber);
182 Looks up information about the contents of virtual virtualshelves number
183 C<$shelfnumber>. Sorted by a field in the biblio table. copyrightdate
186 Returns a reference-to-array, whose elements are references-to-hash,
187 as returned by C<C4::Biblio::GetBiblioFromItemNumber>.
189 Note: the notforloan status comes from the itemtype, and where it equals 0
190 it does not ensure that related items.notforloan status is likewise 0. The
191 caller has to check any items on their own, possibly with CanBookBeIssued
192 from C4::Circulation.
196 sub GetShelfContents {
197 my ($shelfnumber, $row_count, $offset, $sortfield, $sort_direction ) = @_;
198 my $dbh=C4::Context->dbh();
199 my $sth1 = $dbh->prepare("SELECT count(*) FROM virtualshelfcontents WHERE shelfnumber = ?");
200 $sth1->execute($shelfnumber);
201 my $total = $sth1->fetchrow;
203 my $sth2 = $dbh->prepare('SELECT sortfield FROM virtualshelves WHERE shelfnumber=?');
204 $sth2->execute($shelfnumber);
205 ($sortfield) = $sth2->fetchrow_array;
208 " SELECT DISTINCT vc.biblionumber, vc.shelfnumber, vc.dateadded, itemtypes.*,
209 biblio.*, biblioitems.itemtype, biblioitems.publicationyear as year, biblioitems.publishercode, biblioitems.place, biblioitems.size, biblioitems.pages
210 FROM virtualshelfcontents vc
211 JOIN biblio ON vc.biblionumber = biblio.biblionumber
212 LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber
213 LEFT JOIN items ON items.biblionumber=vc.biblionumber
214 LEFT JOIN itemtypes ON biblioitems.itemtype = itemtypes.itemtype
215 WHERE vc.shelfnumber=? ";
216 my @params = ($shelfnumber);
218 $query .= " ORDER BY " . $dbh->quote_identifier( $sortfield );
219 $query .= " DESC " if ( $sort_direction eq 'desc' );
222 $query .= " LIMIT ?, ? ";
223 push (@params, ($offset ? $offset : 0));
224 push (@params, $row_count);
226 my $sth3 = $dbh->prepare($query);
227 $sth3->execute(@params);
228 return ($sth3->fetchall_arrayref({}), $total);
229 # Like the perldoc says,
230 # returns reference-to-array, where each element is reference-to-hash of the row:
231 # like [ $sth->fetchrow_hashref(), $sth->fetchrow_hashref() ... ]
232 # Suitable for use in TMPL_LOOP.
233 # See http://search.cpan.org/~timb/DBI-1.601/DBI.pm#fetchall_arrayref
234 # or newer, for your version of DBI.
239 &AddToShelf($biblionumber, $shelfnumber, $borrower);
241 Adds bib number C<$biblionumber> to virtual virtualshelves number
242 C<$shelfnumber>, unless that bib is already on that shelf.
247 my ($biblionumber, $shelfnumber, $borrowernumber) = @_;
248 return unless $biblionumber;
249 my $dbh = C4::Context->dbh;
252 FROM virtualshelfcontents
253 WHERE shelfnumber=? AND biblionumber=?
255 my $sth = $dbh->prepare($query);
257 $sth->execute( $shelfnumber, $biblionumber );
258 ($sth->rows) and return; # already on shelf
260 INSERT INTO virtualshelfcontents
261 (shelfnumber, biblionumber, flags, borrowernumber)
262 VALUES (?, ?, 0, ?));
263 $sth = $dbh->prepare($query);
264 $sth->execute( $shelfnumber, $biblionumber, $borrowernumber);
265 $query = qq(UPDATE virtualshelves
266 SET lastmodified = CURRENT_TIMESTAMP
267 WHERE shelfnumber = ?);
268 $sth = $dbh->prepare($query);
269 $sth->execute( $shelfnumber );
272 =head2 ShelfPossibleAction
274 ShelfPossibleAction($loggedinuser, $shelfnumber, $action);
276 C<$loggedinuser,$shelfnumber,$action>
278 $action can be "view", "add", "delete", "manage", "new_public", "new_private".
279 New additional actions are: invite, acceptshare.
280 Note that add/delete here refers to adding/deleting entries from the list. Deleting the list itself falls under manage.
281 new_public and new_private refers to creating a new public or private list.
282 The distinction between deleting your own entries from the list or entries from
283 others is made in DelFromShelf.
285 Returns 1 if the user can do the $action in the $shelfnumber shelf.
287 For the actions invite and acceptshare a second errorcode is returned if the
288 result is false. See opac-shareshelf.pl
292 sub ShelfPossibleAction {
293 my ( $user, $shelfnumber, $action ) = @_;
294 $action= 'view' unless $action;
295 $user=0 unless $user;
297 if($action =~ /^new/) { #no shelfnumber needed
298 if($action eq 'new_private') {
301 elsif($action eq 'new_public') {
302 return $user>0 && C4::Context->preference('OpacAllowPublicListCreation');
307 return 0 unless defined($shelfnumber);
309 if ( $user > 0 and $action eq 'delete_shelf' ) {
310 my $borrower = C4::Members::GetMember( borrowernumber => $user );
313 if C4::Auth::haspermission( $borrower->{userid}, { lists => 'delete_public_lists' } );
316 my $dbh = C4::Context->dbh;
318 SELECT COALESCE(owner,0) AS owner, category, allow_add, allow_delete_own, allow_delete_other, COALESCE(sh.borrowernumber,0) AS borrowernumber
319 FROM virtualshelves vs
320 LEFT JOIN virtualshelfshares sh ON sh.shelfnumber=vs.shelfnumber
321 AND sh.borrowernumber=?
322 WHERE vs.shelfnumber=?
324 my $sth = $dbh->prepare($query);
325 $sth->execute($user, $shelfnumber);
326 my $shelf= $sth->fetchrow_hashref;
328 return 0 unless $shelf && ($shelf->{category}==2 || $shelf->{owner}==$user || ($user && $shelf->{borrowernumber}==$user));
329 if($action eq 'view') {
330 #already handled in the above condition
333 elsif($action eq 'add') {
334 return 0 if $user<=0; #should be logged in
335 return 1 if $shelf->{allow_add}==1 || $shelf->{owner}==$user;
336 #owner may always add
338 elsif($action eq 'delete') {
339 #this answer is just diplomatic: it says that you may be able to delete
340 #some items from that shelf
341 #it does not answer the question about a specific biblio
342 #DelFromShelf checks the situation per biblio
343 return 1 if $user>0 && ($shelf->{allow_delete_own}==1 || $shelf->{allow_delete_other}==1);
345 elsif($action eq 'invite') {
346 #for sharing you must be the owner and the list must be private
347 if( $shelf->{category}==1 ) {
348 return 1 if $shelf->{owner}==$user;
349 return (0, 4); # code 4: should be owner
352 return (0, 5); # code 5: should be private list
355 elsif($action eq 'acceptshare') {
356 #the key for accepting is checked later in Koha::Virtualshelf->share
357 #you must not be the owner, list must be private
358 if( $shelf->{category}==1 ) {
359 return (0, 8) if $shelf->{owner}==$user;
360 #code 8: should not be owner
364 return (0, 5); # code 5: should be private list
367 elsif($action eq 'manage' or $action eq 'delete_shelf') {
368 return 1 if $user && $shelf->{owner}==$user;
375 $result= &DelFromShelf( $bibref, $shelfnumber, $user);
377 Removes biblionumbers in passed arrayref from shelf C<$shelfnumber>.
378 If the bib wasn't on that virtualshelves to begin with, nothing happens.
380 Returns 0 if no items have been deleted.
385 my ($bibref, $shelfnumber, $user) = @_;
386 my $dbh = C4::Context->dbh;
387 my $query = qq(SELECT allow_delete_own, allow_delete_other FROM virtualshelves WHERE shelfnumber=?);
388 my $sth= $dbh->prepare($query);
389 $sth->execute($shelfnumber);
390 my ($del_own, $del_oth)= $sth->fetchrow;
394 $query = qq(DELETE FROM virtualshelfcontents
395 WHERE shelfnumber=? AND biblionumber=? AND borrowernumber=?);
396 $sth= $dbh->prepare($query);
397 foreach my $biblionumber (@$bibref) {
398 $sth->execute($shelfnumber, $biblionumber, $user);
399 $r= $sth->rows; #Expect -1, 0 or 1 (-1 means Don't know; count as 1)
400 $t+= ($r==-1)? 1: $r;
404 #includes a check if borrowernumber is null (deleted patron)
405 $query = qq/DELETE FROM virtualshelfcontents
406 WHERE shelfnumber=? AND biblionumber=? AND
407 (borrowernumber IS NULL OR borrowernumber<>?)/;
408 $sth= $dbh->prepare($query);
409 foreach my $biblionumber (@$bibref) {
410 $sth->execute($shelfnumber, $biblionumber, $user);
412 $t+= ($r==-1)? 1: $r;
418 =head2 GetBibliosShelves
420 This finds all the public lists that this bib record is in.
424 sub GetBibliosShelves {
425 my ( $biblionumber ) = @_;
426 my $dbh = C4::Context->dbh;
427 my $sth = $dbh->prepare('
428 SELECT vs.shelfname, vs.shelfnumber
429 FROM virtualshelves vs
430 JOIN virtualshelfcontents vc ON (vs.shelfnumber= vc.shelfnumber)
432 AND vc.biblionumber= ?
434 $sth->execute( $biblionumber );
435 return $sth->fetchall_arrayref({});
440 $howmany= ShelvesMax($context);
442 Tells how much shelves are shown in which context.
443 POPUP refers to addbybiblionumber popup, MGRPAGE is managing page (in opac or
444 staff), COMBO refers to the Add to-combo of search results. MASTHEAD is the
445 main Koha toolbar with Lists button.
451 return SHELVES_POPUP_MAX if $which eq 'POPUP';
452 return SHELVES_MGRPAGE_MAX if $which eq 'MGRPAGE';
453 return SHELVES_COMBO_MAX if $which eq 'COMBO';
454 return SHELVES_MASTHEAD_MAX if $which eq 'MASTHEAD';
455 return SHELVES_MASTHEAD_MAX;
458 =head2 HandleDelBorrower
460 HandleDelBorrower($borrower);
462 When a member is deleted (DelMember in Members.pm), you should call me first.
463 This routine deletes/moves lists and entries for the deleted member/borrower.
464 Lists owned by the borrower are deleted, but entries from the borrower to
465 other lists are kept.
469 sub HandleDelBorrower {
472 my $dbh = C4::Context->dbh;
474 #Delete all lists and all shares of this borrower
475 #Consistent with the approach Koha uses on deleting individual lists
476 #Note that entries in virtualshelfcontents added by this borrower to
477 #lists of others will be handled by a table constraint: the borrower
478 #is set to NULL in those entries.
479 $query="DELETE FROM virtualshelves WHERE owner=?";
480 $dbh->do($query,undef,($borrower));
483 #We could handle the above deletes via a constraint too.
484 #But a new BZ report 11889 has been opened to discuss another approach.
485 #Instead of deleting we could also disown lists (based on a pref).
486 #In that way we could save shared and public lists.
487 #The current table constraints support that idea now.
488 #This pref should then govern the results of other routines/methods such as
489 #Koha::Virtualshelf->new->delete too.
493 my ($owner, $category) = @_;
495 # Find out how many shelves total meet the submitted criteria...
497 my $dbh = C4::Context->dbh;
498 my $query = "SELECT count(*) FROM virtualshelves vs ";
501 LEFT JOIN virtualshelfshares sh ON sh.shelfnumber=vs.shelfnumber
502 AND sh.borrowernumber=?
503 WHERE category=1 AND (vs.owner=? OR sh.borrowernumber=?) };
504 @params= ($owner, $owner, $owner);
507 $query.='WHERE category=2';
510 my $sth = $dbh->prepare($query);
511 $sth->execute(@params);
512 my ($total)= $sth->fetchrow;
522 Koha Development Team <http://koha-community.org/>
526 C4::Circulation::Circ2(3)