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 under the
8 # terms of the GNU General Public License as published by the Free Software
9 # Foundation; either version 2 of the License, or (at your option) any later
12 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
13 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
14 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
16 # You should have received a copy of the GNU General Public License along
17 # with Koha; if not, write to the Free Software Foundation, Inc.,
18 # 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
27 use constant SHELVES_MASTHEAD_MAX => 10; #number under Lists button in masthead
28 use constant SHELVES_COMBO_MAX => 10; #add to combo in search
29 use constant SHELVES_MGRPAGE_MAX => 20; #managing page
30 use constant SHELVES_POPUP_MAX => 40; #addbybiblio popup
32 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK);
35 # set the version for version checking
36 $VERSION = 3.07.00.049;
40 &GetShelves &GetShelfContents &GetShelf
44 &DelFromShelf &DelShelf
48 &GetAllShelves &ShelvesMax
55 C4::VirtualShelves - Functions for manipulating Koha virtual shelves
59 use C4::VirtualShelves;
63 This module provides functions for manipulating virtual shelves,
64 including creating and deleting virtual shelves, and adding and removing
65 bibs to and from virtual shelves.
71 ($shelflist, $totshelves) = &GetShelves($category, $row_count, $offset, $owner);
72 ($shelfnumber, $shelfhash) = each %{$shelflist};
74 Returns the number of shelves specified by C<$row_count> and C<$offset> as well as the total
75 number of shelves that meet the C<$owner> and C<$category> criteria. C<$category>,
76 C<$row_count>, and C<$offset> are required. C<$owner> must be supplied when C<$category> == 1.
77 When C<$category> is 2, supply undef as argument for C<$owner>.
79 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.
81 C<$shelflist>is a reference-to-hash. The keys are the virtualshelves numbers (C<$shelfnumber>, above),
82 and the values (C<$shelfhash>, above) are themselves references-to-hash, with the following keys:
86 =item C<$shelfhash-E<gt>{shelfname}>
88 A string. The name of the shelf.
90 =item C<$shelfhash-E<gt>{count}>
92 The number of virtuals on that virtualshelves.
99 my ($category, $row_count, $offset, $owner) = @_;
101 my $total = _shelf_count($owner, $category);
102 my $dbh = C4::Context->dbh;
104 SELECT vs.shelfnumber, vs.shelfname,vs.owner,
105 bo.surname,bo.firstname,vs.category,vs.sortfield,
106 count(vc.biblionumber) as count
107 FROM virtualshelves vs
108 LEFT JOIN borrowers bo ON vs.owner=bo.borrowernumber
109 LEFT JOIN virtualshelfcontents vc USING (shelfnumber) };
112 LEFT JOIN virtualshelfshares sh ON sh.shelfnumber=vs.shelfnumber
113 AND sh.borrowernumber=?
114 WHERE category=1 AND (vs.owner=? OR sh.borrowernumber=?) };
115 @params= ($owner, $owner, $owner, $offset||0, $row_count);
118 $query.= 'WHERE category=2 ';
119 @params= ($offset||0, $row_count);
122 GROUP BY vs.shelfnumber
123 ORDER BY vs.shelfname
126 my $sth2 = $dbh->prepare($query);
127 $sth2->execute(@params);
129 while( my ($shelfnumber, $shelfname, $owner, $surname, $firstname, $category, $sortfield, $count)= $sth2->fetchrow) {
130 $shelflist{$shelfnumber}->{'shelfname'} = $shelfname;
131 $shelflist{$shelfnumber}->{'count'} = $count;
132 $shelflist{$shelfnumber}->{'single'} = $count==1;
133 $shelflist{$shelfnumber}->{'sortfield'} = $sortfield;
134 $shelflist{$shelfnumber}->{'category'} = $category;
135 $shelflist{$shelfnumber}->{'owner'} = $owner;
136 $shelflist{$shelfnumber}->{'surname'} = $surname;
137 $shelflist{$shelfnumber}->{'firstname'} = $firstname;
139 return ( \%shelflist, $total );
144 $shelflist = GetAllShelves($category, $owner)
146 This function returns a reference to an array of hashrefs containing all shelves
147 sorted by the shelf name.
149 This function is intended to return a dataset reflecting all the shelves for
150 the submitted parameters.
155 my ($category,$owner,$adding_allowed) = @_;
157 my $dbh = C4::Context->dbh;
158 my $query = 'SELECT vs.* FROM virtualshelves vs ';
161 LEFT JOIN virtualshelfshares sh ON sh.shelfnumber=vs.shelfnumber
162 AND sh.borrowernumber=?
163 WHERE category=1 AND (vs.owner=? OR sh.borrowernumber=?) };
164 @params = ($owner, $owner, $owner);
167 $query.='WHERE category=2 ';
170 $query.='AND (allow_add=1 OR owner=?) ' if $adding_allowed;
171 push @params, $owner if $adding_allowed;
172 $query.= 'ORDER BY shelfname ASC';
173 my $sth = $dbh->prepare( $query );
174 $sth->execute(@params);
175 return $sth->fetchall_arrayref({});
178 =head2 GetSomeShelfNames
180 Returns shelf names and numbers for Add to combo of search results and Lists button of OPAC header.
184 sub GetSomeShelfNames {
185 my ($owner, $purpose, $adding_allowed)= @_;
186 my ($bar, $pub, @params);
187 my $dbh = C4::Context->dbh;
189 my $bquery = 'SELECT vs.shelfnumber, vs.shelfname FROM virtualshelves vs ';
190 my $limit= ShelvesMax($purpose);
192 my $qry1= $bquery."WHERE vs.category=2 ";
193 $qry1.= "AND (allow_add=1 OR owner=?) " if $adding_allowed;
194 push @params, $owner||0 if $adding_allowed;
195 $qry1.= "ORDER BY vs.lastmodified DESC LIMIT $limit";
197 unless($adding_allowed && (!defined($owner) || $owner<=0)) {
198 #if adding items, user should be known
199 $pub= $dbh->selectall_arrayref($qry1,{Slice=>{}},@params);
203 my $qry2= $bquery. qq{
204 LEFT JOIN virtualshelfshares sh ON sh.shelfnumber=vs.shelfnumber AND sh.borrowernumber=?
205 WHERE vs.category=1 AND (vs.owner=? OR sh.borrowernumber=?) };
206 @params=($owner,$owner,$owner);
207 $qry2.= "AND (allow_add=1 OR owner=?) " if $adding_allowed;
208 push @params, $owner if $adding_allowed;
209 $qry2.= "ORDER BY vs.lastmodified DESC ";
210 $qry2.= "LIMIT $limit";
211 $bar= $dbh->selectall_arrayref($qry2,{Slice=>{}},@params);
214 return ( { bartotal => $bar? scalar @$bar: 0, pubtotal => $pub? scalar @$pub: 0}, $pub, $bar);
219 (shelfnumber,shelfname,owner,category,sortfield,allow_add,allow_delete_own,allow_delete_other) = &GetShelf($shelfnumber);
221 Returns the above-mentioned fields for passed virtual shelf number.
226 my ($shelfnumber) = @_;
227 my $dbh = C4::Context->dbh;
229 SELECT shelfnumber, shelfname, owner, category, sortfield,
230 allow_add, allow_delete_own, allow_delete_other
234 my $sth = $dbh->prepare($query);
235 $sth->execute($shelfnumber);
236 return $sth->fetchrow;
239 =head2 GetShelfContents
241 $biblist = &GetShelfContents($shelfnumber);
243 Looks up information about the contents of virtual virtualshelves number
244 C<$shelfnumber>. Sorted by a field in the biblio table. copyrightdate
247 Returns a reference-to-array, whose elements are references-to-hash,
248 as returned by C<C4::Biblio::GetBiblioFromItemNumber>.
250 Note: the notforloan status comes from the itemtype, and where it equals 0
251 it does not ensure that related items.notforloan status is likewise 0. The
252 caller has to check any items on their own, possibly with CanBookBeIssued
253 from C4::Circulation.
257 sub GetShelfContents {
258 my ($shelfnumber, $row_count, $offset, $sortfield) = @_;
259 my $dbh=C4::Context->dbh();
260 my $sth1 = $dbh->prepare("SELECT count(*) FROM virtualshelfcontents WHERE shelfnumber = ?");
261 $sth1->execute($shelfnumber);
262 my $total = $sth1->fetchrow;
264 my $sth2 = $dbh->prepare('SELECT sortfield FROM virtualshelves WHERE shelfnumber=?');
265 $sth2->execute($shelfnumber);
266 ($sortfield) = $sth2->fetchrow_array;
269 " SELECT vc.biblionumber, vc.shelfnumber, vc.dateadded, itemtypes.*,
270 biblio.*, biblioitems.itemtype, biblioitems.publicationyear as year, biblioitems.publishercode, biblioitems.place, biblioitems.size, biblioitems.pages
271 FROM virtualshelfcontents vc
272 JOIN biblio ON vc.biblionumber = biblio.biblionumber
273 LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber
274 LEFT JOIN itemtypes ON biblioitems.itemtype = itemtypes.itemtype
275 WHERE vc.shelfnumber=? ";
276 my @params = ($shelfnumber);
278 $query .= " ORDER BY " . $sortfield;
279 $query .= " DESC " if ($sortfield eq 'copyrightdate');
282 $query .= " LIMIT ?, ? ";
283 push (@params, ($offset ? $offset : 0));
284 push (@params, $row_count);
286 my $sth3 = $dbh->prepare($query);
287 $sth3->execute(@params);
288 return ($sth3->fetchall_arrayref({}), $total);
289 # Like the perldoc says,
290 # returns reference-to-array, where each element is reference-to-hash of the row:
291 # like [ $sth->fetchrow_hashref(), $sth->fetchrow_hashref() ... ]
292 # Suitable for use in TMPL_LOOP.
293 # See http://search.cpan.org/~timb/DBI-1.601/DBI.pm#fetchall_arrayref
294 # or newer, for your version of DBI.
299 $shelfnumber = &AddShelf($hashref, $owner);
301 Creates a new virtual shelf. Params passed in a hash like ModShelf.
303 Returns a code to know what's happen.
304 * -1 : if this virtualshelves already exists.
305 * $shelfnumber : if success.
310 my ($hashref, $owner)= @_;
311 my $dbh = C4::Context->dbh;
313 #initialize missing hash values to silence warnings
314 foreach('shelfname','category', 'sortfield', 'allow_add', 'allow_delete_own', 'allow_delete_other' ) {
315 $hashref->{$_}= undef unless exists $hashref->{$_};
318 return -1 unless _CheckShelfName($hashref->{shelfname}, $hashref->{category}, $owner, 0);
320 my $query = qq(INSERT INTO virtualshelves
321 (shelfname,owner,category,sortfield,allow_add,allow_delete_own,allow_delete_other)
322 VALUES (?,?,?,?,?,?,?));
324 my $sth = $dbh->prepare($query);
326 $hashref->{shelfname},
328 $hashref->{category},
329 $hashref->{sortfield},
330 $hashref->{allow_add}//0,
331 $hashref->{allow_delete_own}//1,
332 $hashref->{allow_delete_other}//0 );
333 my $shelfnumber = $dbh->{'mysql_insertid'};
339 &AddToShelf($biblionumber, $shelfnumber, $borrower);
341 Adds bib number C<$biblionumber> to virtual virtualshelves number
342 C<$shelfnumber>, unless that bib is already on that shelf.
347 my ($biblionumber, $shelfnumber, $borrowernumber) = @_;
348 return unless $biblionumber;
349 my $dbh = C4::Context->dbh;
352 FROM virtualshelfcontents
353 WHERE shelfnumber=? AND biblionumber=?
355 my $sth = $dbh->prepare($query);
357 $sth->execute( $shelfnumber, $biblionumber );
358 ($sth->rows) and return; # already on shelf
360 INSERT INTO virtualshelfcontents
361 (shelfnumber, biblionumber, flags, borrowernumber)
362 VALUES (?, ?, 0, ?));
363 $sth = $dbh->prepare($query);
364 $sth->execute( $shelfnumber, $biblionumber, $borrowernumber);
365 $query = qq(UPDATE virtualshelves
366 SET lastmodified = CURRENT_TIMESTAMP
367 WHERE shelfnumber = ?);
368 $sth = $dbh->prepare($query);
369 $sth->execute( $shelfnumber );
374 my $result= ModShelf($shelfnumber, $hashref)
376 Where $hashref->{column} = param
378 Modify the value into virtualshelves table with values given
379 from hashref, which each key of the hashref should be
380 the name of a column of virtualshelves.
381 Fields like shelfnumber or owner cannot be changed.
383 Returns 1 if the action seemed to be successful.
388 my ($shelfnumber,$hashref) = @_;
389 my $dbh = C4::Context->dbh;
391 my $query= "SELECT * FROM virtualshelves WHERE shelfnumber=?";
392 my $sth = $dbh->prepare($query);
393 $sth->execute($shelfnumber);
394 my $oldrecord= $sth->fetchrow_hashref;
395 return 0 unless $oldrecord; #not found?
397 #initialize missing hash values to silence warnings
398 foreach('shelfname','category', 'sortfield', 'allow_add', 'allow_delete_own', 'allow_delete_other' ) {
399 $hashref->{$_}= undef unless exists $hashref->{$_};
402 #if name or category changes, the name should be tested
403 if($hashref->{shelfname} || $hashref->{category}) {
404 unless(_CheckShelfName(
405 $hashref->{shelfname}//$oldrecord->{shelfname},
406 $hashref->{category}//$oldrecord->{category},
409 return 0; #name check failed
413 #only the following fields from the hash may be changed
414 $query= "UPDATE virtualshelves SET shelfname=?, category=?, sortfield=?, allow_add=?, allow_delete_own=?, allow_delete_other=? WHERE shelfnumber=?";
415 $sth = $dbh->prepare($query);
417 $hashref->{shelfname}//$oldrecord->{shelfname},
418 $hashref->{category}//$oldrecord->{category},
419 $hashref->{sortfield}//$oldrecord->{sortfield},
420 $hashref->{allow_add}//$oldrecord->{allow_add},
421 $hashref->{allow_delete_own}//$oldrecord->{allow_delete_own},
422 $hashref->{allow_delete_other}//$oldrecord->{allow_delete_other},
427 =head2 ShelfPossibleAction
429 ShelfPossibleAction($loggedinuser, $shelfnumber, $action);
431 C<$loggedinuser,$shelfnumber,$action>
433 $action can be "view", "add", "delete", "manage", "new_public", "new_private".
434 Note that add/delete here refers to adding/deleting entries from the list. Deleting the list itself falls under manage.
435 new_public and new_private refers to creating a new public or private list.
436 The distinction between deleting your own entries from the list or entries from
437 others is made in DelFromShelf.
439 Returns 1 if the user can do the $action in the $shelfnumber shelf.
444 sub ShelfPossibleAction {
445 my ( $user, $shelfnumber, $action ) = @_;
446 $action= 'view' unless $action;
447 $user=0 unless $user;
449 if($action =~ /^new/) { #no shelfnumber needed
450 if($action eq 'new_private') {
453 elsif($action eq 'new_public') {
454 return $user>0 && C4::Context->preference('OpacAllowPublicListCreation');
459 return 0 unless defined($shelfnumber);
461 my $dbh = C4::Context->dbh;
463 SELECT IFNULL(owner,0) AS owner, category, allow_add, allow_delete_own, allow_delete_other, IFNULL(sh.borrowernumber,0) AS borrowernumber
464 FROM virtualshelves vs
465 LEFT JOIN virtualshelfshares sh ON sh.shelfnumber=vs.shelfnumber
466 AND sh.borrowernumber=?
467 WHERE vs.shelfnumber=?
469 my $sth = $dbh->prepare($query);
470 $sth->execute($user, $shelfnumber);
471 my $shelf= $sth->fetchrow_hashref;
473 return 0 unless $shelf && ($shelf->{category}==2 || $shelf->{owner}==$user || ($user && $shelf->{borrowernumber}==$user));
474 if($action eq 'view') {
475 #already handled in the above condition
478 elsif($action eq 'add') {
479 return 0 if $user<=0; #should be logged in
480 return 1 if $shelf->{allow_add}==1 || $shelf->{owner}==$user;
481 #owner may always add
483 elsif($action eq 'delete') {
484 #this answer is just diplomatic: it says that you may be able to delete
485 #some items from that shelf
486 #it does not answer the question about a specific biblio
487 #DelFromShelf checks the situation per biblio
488 return 1 if $user>0 && ($shelf->{allow_delete_own}==1 || $shelf->{allow_delete_other}==1);
490 elsif($action eq 'manage') {
491 return 1 if $user && $shelf->{owner}==$user;
498 $result= &DelFromShelf( $bibref, $shelfnumber, $user);
500 Removes biblionumbers in passed arrayref from shelf C<$shelfnumber>.
501 If the bib wasn't on that virtualshelves to begin with, nothing happens.
503 Returns 0 if no items have been deleted.
508 my ($bibref, $shelfnumber, $user) = @_;
509 my $dbh = C4::Context->dbh;
510 my $query = qq(SELECT allow_delete_own, allow_delete_other FROM virtualshelves WHERE shelfnumber=?);
511 my $sth= $dbh->prepare($query);
512 $sth->execute($shelfnumber);
513 my ($del_own, $del_oth)= $sth->fetchrow;
517 $query = qq(DELETE FROM virtualshelfcontents
518 WHERE shelfnumber=? AND biblionumber=? AND borrowernumber=?);
519 $sth= $dbh->prepare($query);
520 foreach my $biblionumber (@$bibref) {
521 $sth->execute($shelfnumber, $biblionumber, $user);
522 $r= $sth->rows; #Expect -1, 0 or 1 (-1 means Don't know; count as 1)
523 $t+= ($r==-1)? 1: $r;
527 #includes a check if borrowernumber is null (deleted patron)
528 $query = qq/DELETE FROM virtualshelfcontents
529 WHERE shelfnumber=? AND biblionumber=? AND
530 (borrowernumber IS NULL OR borrowernumber<>?)/;
531 $sth= $dbh->prepare($query);
532 foreach my $biblionumber (@$bibref) {
533 $sth->execute($shelfnumber, $biblionumber, $user);
535 $t+= ($r==-1)? 1: $r;
543 $Number = DelShelf($shelfnumber);
545 This function deletes the shelf number, and all of it's content.
546 Authorization to do so MUST have been checked before calling, while using
547 ShelfPossibleAction with manage parameter.
552 my ($shelfnumber)= @_;
553 return unless $shelfnumber && $shelfnumber =~ /^\d+$/;
554 my $dbh = C4::Context->dbh;
555 my $sth = $dbh->prepare("DELETE FROM virtualshelves WHERE shelfnumber=?");
556 return $sth->execute($shelfnumber);
559 =head2 GetBibliosShelves
561 This finds all the public lists that this bib record is in.
565 sub GetBibliosShelves {
566 my ( $biblionumber ) = @_;
567 my $dbh = C4::Context->dbh;
568 my $sth = $dbh->prepare('
569 SELECT vs.shelfname, vs.shelfnumber
570 FROM virtualshelves vs
571 JOIN virtualshelfcontents vc ON (vs.shelfnumber= vc.shelfnumber)
573 AND vc.biblionumber= ?
575 $sth->execute( $biblionumber );
576 return $sth->fetchall_arrayref({});
581 $howmany= ShelvesMax($context);
583 Tells how much shelves are shown in which context.
584 POPUP refers to addbybiblionumber popup, MGRPAGE is managing page (in opac or
585 staff), COMBO refers to the Add to-combo of search results. MASTHEAD is the
586 main Koha toolbar with Lists button.
592 return SHELVES_POPUP_MAX if $which eq 'POPUP';
593 return SHELVES_MGRPAGE_MAX if $which eq 'MGRPAGE';
594 return SHELVES_COMBO_MAX if $which eq 'COMBO';
595 return SHELVES_MASTHEAD_MAX if $which eq 'MASTHEAD';
596 return SHELVES_MASTHEAD_MAX;
599 sub HandleDelBorrower {
600 #when a member is deleted (DelMember in Members.pm), you should call me first
601 #this routine deletes/moves lists and entries for the deleted member/borrower
602 #you could just delete everything (and lose more than you want)
603 #instead we now try to save all public/shared stuff and keep others happy
606 my $dbh = C4::Context->dbh;
608 #Delete shares of this borrower (not lists !)
609 $query="DELETE FROM virtualshelfshares WHERE borrowernumber=?";
610 $dbh->do($query,undef,($borrower));
612 #Delete private lists without owner that now have no shares anymore
613 $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";
616 #Change owner for private lists which have shares
617 $query="UPDATE virtualshelves LEFT JOIN virtualshelfshares sh USING (shelfnumber) SET owner=NULL where owner=? AND category=1 AND sh.borrowernumber IS NOT NULL";
618 $dbh->do($query,undef,($borrower));
620 #Delete unshared private lists
621 $query="DELETE FROM virtualshelves WHERE owner=? AND category=1";
622 $dbh->do($query,undef,($borrower));
624 #Handle public lists owned by borrower
625 $query="UPDATE virtualshelves SET owner=NULL WHERE owner=? AND category=2";
626 $dbh->do($query,undef,($borrower));
628 #Handle entries added by borrower to lists of others
629 $query="UPDATE virtualshelfcontents SET borrowernumber=NULL WHERE borrowernumber=?";
630 $dbh->do($query,undef,($borrower));
636 my ($owner, $category) = @_;
638 # Find out how many shelves total meet the submitted criteria...
640 my $dbh = C4::Context->dbh;
641 my $query = "SELECT count(*) FROM virtualshelves vs ";
644 LEFT JOIN virtualshelfshares sh ON sh.shelfnumber=vs.shelfnumber
645 AND sh.borrowernumber=?
646 WHERE category=1 AND (vs.owner=? OR sh.borrowernumber=?) };
647 @params= ($owner, $owner, $owner);
650 $query.='WHERE category=2';
653 my $sth = $dbh->prepare($query);
654 $sth->execute(@params);
655 my ($total)= $sth->fetchrow;
659 sub _biblionumber_sth { #only used in obsolete sub below
661 my $query = 'select biblionumber from virtualshelfcontents where shelfnumber = ?';
662 my $dbh = C4::Context->dbh;
663 my $sth = $dbh->prepare($query)
665 $sth->execute( $shelf )
670 sub _CheckShelfName {
671 my ($name, $cat, $owner, $number)= @_;
673 my $dbh = C4::Context->dbh;
675 SELECT DISTINCT shelfnumber
677 LEFT JOIN virtualshelfshares sh USING (shelfnumber)
678 WHERE shelfname=? AND shelfnumber<>?);
680 $query.= ' AND (sh.borrowernumber=? OR owner=?) AND category=1';
683 $query.= ' AND category=2';
685 my $sth = $dbh->prepare($query);
686 $sth->execute($cat==1? ($name, $number, $owner, $owner): ($name, $number));
687 return $sth->rows>0? 0: 1;
696 Koha Development Team <http://koha-community.org/>
700 C4::Circulation::Circ2(3)