2 # Please use 8-character tabs for this file (indents are every 4 characters)
4 package C4::VirtualShelves;
7 # Copyright 2000-2002 Katipo Communications
9 # This file is part of Koha.
11 # Koha is free software; you can redistribute it and/or modify it under the
12 # terms of the GNU General Public License as published by the Free Software
13 # Foundation; either version 2 of the License, or (at your option) any later
16 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
17 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
18 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
20 # You should have received a copy of the GNU General Public License along
21 # with Koha; if not, write to the Free Software Foundation, Inc.,
22 # 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
31 use constant SHELVES_MASTHEAD_MAX => 10; #number under Lists button in masthead
32 use constant SHELVES_COMBO_MAX => 10; #add to combo in search
33 use constant SHELVES_MGRPAGE_MAX => 20; #managing page
34 use constant SHELVES_POPUP_MAX => 40; #addbybiblio popup
36 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK);
39 # set the version for version checking
44 &GetShelves &GetShelfContents &GetShelf
48 &DelFromShelf &DelShelf
52 &GetAllShelves &ShelvesMax
57 my $dbh = C4::Context->dbh;
61 C4::VirtualShelves - Functions for manipulating Koha virtual shelves
65 use C4::VirtualShelves;
69 This module provides functions for manipulating virtual shelves,
70 including creating and deleting virtual shelves, and adding and removing
71 bibs to and from virtual shelves.
77 ($shelflist, $totshelves) = &GetShelves($category, $row_count, $offset, $owner);
78 ($shelfnumber, $shelfhash) = each %{$shelflist};
80 Returns the number of shelves specified by C<$row_count> and C<$offset> as well as the total
81 number of shelves that meet the C<$owner> and C<$category> criteria. C<$category>,
82 C<$row_count>, and C<$offset> are required. C<$owner> must be supplied when C<$category> == 1.
83 When C<$category> is 2, supply undef as argument for C<$owner>.
85 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.
87 C<$shelflist>is a reference-to-hash. The keys are the virtualshelves numbers (C<$shelfnumber>, above),
88 and the values (C<$shelfhash>, above) are themselves references-to-hash, with the following keys:
92 =item C<$shelfhash-E<gt>{shelfname}>
94 A string. The name of the shelf.
96 =item C<$shelfhash-E<gt>{count}>
98 The number of virtuals on that virtualshelves.
105 my ($category, $row_count, $offset, $owner) = @_;
107 my $total = _shelf_count($owner, $category);
109 SELECT vs.shelfnumber, vs.shelfname,vs.owner,
110 bo.surname,bo.firstname,vs.category,vs.sortfield,
111 count(vc.biblionumber) as count
112 FROM virtualshelves vs
113 LEFT JOIN borrowers bo ON vs.owner=bo.borrowernumber
114 LEFT JOIN virtualshelfcontents vc USING (shelfnumber) };
117 LEFT JOIN virtualshelfshares sh ON sh.shelfnumber=vs.shelfnumber
118 AND sh.borrowernumber=?
119 WHERE category=1 AND (vs.owner=? OR sh.borrowernumber=?) };
120 @params= ($owner, $owner, $owner, $offset||0, $row_count);
123 $query.= 'WHERE category=2 ';
124 @params= ($offset||0, $row_count);
127 GROUP BY vs.shelfnumber
128 ORDER BY vs.shelfname
131 my $sth2 = $dbh->prepare($query);
132 $sth2->execute(@params);
134 while( my ($shelfnumber, $shelfname, $owner, $surname, $firstname, $category, $sortfield, $count)= $sth2->fetchrow) {
135 $shelflist{$shelfnumber}->{'shelfname'} = $shelfname;
136 $shelflist{$shelfnumber}->{'count'} = $count;
137 $shelflist{$shelfnumber}->{'single'} = $count==1;
138 $shelflist{$shelfnumber}->{'sortfield'} = $sortfield;
139 $shelflist{$shelfnumber}->{'category'} = $category;
140 $shelflist{$shelfnumber}->{'owner'} = $owner;
141 $shelflist{$shelfnumber}->{'surname'} = $surname;
142 $shelflist{$shelfnumber}->{'firstname'} = $firstname;
144 return ( \%shelflist, $total );
149 $shelflist = GetAllShelves($category, $owner)
151 This function returns a reference to an array of hashrefs containing all shelves
152 sorted by the shelf name.
154 This function is intended to return a dataset reflecting all the shelves for
155 the submitted parameters.
160 my ($category,$owner,$adding_allowed) = @_;
162 my $query = 'SELECT vs.* FROM virtualshelves vs ';
165 LEFT JOIN virtualshelfshares sh ON sh.shelfnumber=vs.shelfnumber
166 AND sh.borrowernumber=?
167 WHERE category=1 AND (vs.owner=? OR sh.borrowernumber=?) };
168 @params = ($owner, $owner, $owner);
171 $query.='WHERE category=2 ';
174 $query.='AND (allow_add=1 OR owner=?) ' if $adding_allowed;
175 push @params, $owner if $adding_allowed;
176 $query.= 'ORDER BY shelfname ASC';
177 my $sth = $dbh->prepare( $query );
178 $sth->execute(@params);
179 return $sth->fetchall_arrayref({});
182 =head2 GetSomeShelfNames
184 Returns shelf names and numbers for Add to combo of search results and Lists button of OPAC header.
188 sub GetSomeShelfNames {
189 my ($owner, $purpose, $adding_allowed)= @_;
190 my ($bar, $pub, @params);
192 my $bquery = 'SELECT vs.shelfnumber, vs.shelfname FROM virtualshelves vs ';
193 my $limit= ShelvesMax($purpose);
195 my $qry1= $bquery."WHERE vs.category=2 ";
196 $qry1.= "AND (allow_add=1 OR owner=?) " if $adding_allowed;
197 push @params, $owner||0 if $adding_allowed;
198 $qry1.= "ORDER BY vs.lastmodified DESC LIMIT $limit";
200 unless($adding_allowed && (!defined($owner) || $owner<=0)) {
201 #if adding items, user should be known
202 $pub= $dbh->selectall_arrayref($qry1,{Slice=>{}},@params);
206 my $qry2= $bquery. qq{
207 LEFT JOIN virtualshelfshares sh ON sh.shelfnumber=vs.shelfnumber AND sh.borrowernumber=?
208 WHERE vs.category=1 AND (vs.owner=? OR sh.borrowernumber=?) };
209 @params=($owner,$owner,$owner);
210 $qry2.= "AND (allow_add=1 OR owner=?) " if $adding_allowed;
211 push @params, $owner if $adding_allowed;
212 $qry2.= "ORDER BY vs.lastmodified DESC ";
213 $qry2.= "LIMIT $limit";
214 $bar= $dbh->selectall_arrayref($qry2,{Slice=>{}},@params);
217 return ( { bartotal => $bar? scalar @$bar: 0, pubtotal => $pub? scalar @$pub: 0}, $pub, $bar);
222 (shelfnumber,shelfname,owner,category,sortfield) = &GetShelf($shelfnumber);
224 Looks up information about the contents of virtual virtualshelves number
227 Returns the database's information on 'virtualshelves' table.
232 my ($shelfnumber) = @_;
234 SELECT shelfnumber, shelfname, owner, category, sortfield
238 my $sth = $dbh->prepare($query);
239 $sth->execute($shelfnumber);
240 return $sth->fetchrow;
243 =head2 GetShelfContents
245 $biblist = &GetShelfContents($shelfnumber);
247 Looks up information about the contents of virtual virtualshelves number
248 C<$shelfnumber>. Sorted by a field in the biblio table. copyrightdate
251 Returns a reference-to-array, whose elements are references-to-hash,
252 as returned by C<C4::Biblio::GetBiblioFromItemNumber>.
254 Note: the notforloan status comes from the itemtype, and where it equals 0
255 it does not ensure that related items.notforloan status is likewise 0. The
256 caller has to check any items on their own, possibly with CanBookBeIssued
257 from C4::Circulation.
261 sub GetShelfContents ($;$$$) {
262 my ($shelfnumber, $row_count, $offset, $sortfield) = @_;
263 my $dbh=C4::Context->dbh();
264 my $sth1 = $dbh->prepare("SELECT count(*) FROM virtualshelfcontents WHERE shelfnumber = ?");
265 $sth1->execute($shelfnumber);
266 my $total = $sth1->fetchrow;
268 my $sth2 = $dbh->prepare('SELECT sortfield FROM virtualshelves WHERE shelfnumber=?');
269 $sth2->execute($shelfnumber);
270 ($sortfield) = $sth2->fetchrow_array;
273 " SELECT vc.biblionumber, vc.shelfnumber, vc.dateadded, itemtypes.*,
274 biblio.*, biblioitems.itemtype, biblioitems.publicationyear as year, biblioitems.publishercode, biblioitems.place, biblioitems.size, biblioitems.pages
275 FROM virtualshelfcontents vc
276 LEFT JOIN biblio ON vc.biblionumber = biblio.biblionumber
277 LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber
278 LEFT JOIN itemtypes ON biblioitems.itemtype = itemtypes.itemtype
279 WHERE vc.shelfnumber=? ";
280 my @params = ($shelfnumber);
282 $query .= " ORDER BY " . $sortfield;
283 $query .= " DESC " if ($sortfield eq 'copyrightdate');
286 $query .= " LIMIT ?, ? ";
287 push (@params, ($offset ? $offset : 0));
288 push (@params, $row_count);
290 my $sth3 = $dbh->prepare($query);
291 $sth3->execute(@params);
292 return ($sth3->fetchall_arrayref({}), $total);
293 # Like the perldoc says,
294 # returns reference-to-array, where each element is reference-to-hash of the row:
295 # like [ $sth->fetchrow_hashref(), $sth->fetchrow_hashref() ... ]
296 # Suitable for use in TMPL_LOOP.
297 # See http://search.cpan.org/~timb/DBI-1.601/DBI.pm#fetchall_arrayref
298 # or newer, for your version of DBI.
303 $shelfnumber = &AddShelf($hashref, $owner);
305 Creates a new virtual shelf. Params passed in a hash like ModShelf.
307 Returns a code to know what's happen.
308 * -1 : if this virtualshelves already exists.
309 * $shelfnumber : if success.
314 my ($hashref, $owner)= @_;
316 #initialize missing hash values to silence warnings
317 foreach('shelfname','category', 'sortfield', 'allow_add', 'allow_delete_own', 'allow_delete_other' ) {
318 $hashref->{$_}= exists $hashref->{$_}? $hashref->{$_}||'': '';
321 return -1 unless _CheckShelfName($hashref->{shelfname}, $hashref->{category}, $owner, 0);
323 my $query = qq(INSERT INTO virtualshelves
324 (shelfname,owner,category,sortfield,allow_add,allow_delete_own,allow_delete_other)
325 VALUES (?,?,?,?,?,?,?));
327 my $sth = $dbh->prepare($query);
329 $hashref->{shelfname},
331 $hashref->{category},
332 $hashref->{sortfield},
333 $hashref->{allow_add}||0,
334 $hashref->{allow_delete_own}||1,
335 $hashref->{allow_delete_other}||0 );
336 my $shelfnumber = $dbh->{'mysql_insertid'};
342 &AddToShelf($biblionumber, $shelfnumber, $borrower);
344 Adds bib number C<$biblionumber> to virtual virtualshelves number
345 C<$shelfnumber>, unless that bib is already on that shelf.
350 my ($biblionumber, $shelfnumber, $borrowernumber) = @_;
351 return unless $biblionumber;
354 FROM virtualshelfcontents
355 WHERE shelfnumber=? AND biblionumber=?
357 my $sth = $dbh->prepare($query);
359 $sth->execute( $shelfnumber, $biblionumber );
360 ($sth->rows) and return undef; # already on shelf
362 INSERT INTO virtualshelfcontents
363 (shelfnumber, biblionumber, flags, borrowernumber)
364 VALUES (?, ?, 0, ?));
365 $sth = $dbh->prepare($query);
366 $sth->execute( $shelfnumber, $biblionumber, $borrowernumber);
367 $query = qq(UPDATE virtualshelves
368 SET lastmodified = CURRENT_TIMESTAMP
369 WHERE shelfnumber = ?);
370 $sth = $dbh->prepare($query);
371 $sth->execute( $shelfnumber );
376 my $result= ModShelf($shelfnumber, $hashref)
378 Where $hashref->{column} = param
380 Modify the value into virtualshelves table with values given
381 from hashref, which each key of the hashref should be
382 the name of a column of virtualshelves.
383 Fields like shelfnumber or owner cannot be changed.
385 Returns 1 if the action seemed to be successful.
390 my ($shelfnumber,$hashref) = @_;
392 my $query= "SELECT * FROM virtualshelves WHERE shelfnumber=?";
393 my $sth = $dbh->prepare($query);
394 $sth->execute($shelfnumber);
395 my $oldrecord= $sth->fetchrow_hashref;
396 return 0 unless $oldrecord; #not found?
398 #initialize missing hash values to silence warnings
399 foreach('shelfname','category', 'sortfield', 'allow_add', 'allow_delete_own', 'allow_delete_other' ) {
400 $hashref->{$_}= exists $hashref->{$_}? $hashref->{$_}||'': '';
403 #if name or category changes, the name should be tested
404 if($hashref->{shelfname} || $hashref->{category}) {
405 unless(_CheckShelfName(
406 $hashref->{shelfname}||$oldrecord->{shelfname},
407 $hashref->{category}||$oldrecord->{category},
408 $oldrecord->{owner}, $shelfnumber )) {
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);
462 SELECT IFNULL(owner,0) AS owner, category, allow_add, allow_delete_own, allow_delete_other, IFNULL(sh.borrowernumber,0) AS borrowernumber
463 FROM virtualshelves vs
464 LEFT JOIN virtualshelfshares sh ON sh.shelfnumber=vs.shelfnumber
465 AND sh.borrowernumber=?
466 WHERE vs.shelfnumber=?
468 my $sth = $dbh->prepare($query);
469 $sth->execute($user, $shelfnumber);
470 my $shelf= $sth->fetchrow_hashref;
472 return 0 unless $shelf && ($shelf->{category}==2 || $shelf->{owner}==$user || $shelf->{borrowernumber}==$user);
473 if($action eq 'view') {
474 #already handled in the above condition
477 elsif($action eq 'add') {
478 return 0 if $user<=0; #should be logged in
479 return 1 if $shelf->{allow_add}==1 || $shelf->{owner}==$user;
480 #owner may always add
482 elsif($action eq 'delete') {
483 #this answer is just diplomatic: it says that you may be able to delete
484 #some items from that shelf
485 #it does not answer the question about a specific biblio
486 #DelFromShelf checks the situation per biblio
487 return 1 if $user>0 && ($shelf->{allow_delete_own}==1 || $shelf->{allow_delete_other}==1);
489 elsif($action eq 'manage') {
490 return 1 if $user && $shelf->{owner}==$user;
497 $result= &DelFromShelf( $bibref, $shelfnumber, $user);
499 Removes biblionumbers in passed arrayref from shelf C<$shelfnumber>.
500 If the bib wasn't on that virtualshelves to begin with, nothing happens.
502 Returns 0 if no items have been deleted.
507 my ($bibref, $shelfnumber, $user) = @_;
508 my $query = qq(SELECT allow_delete_own, allow_delete_other FROM virtualshelves WHERE shelfnumber=?);
509 my $sth= $dbh->prepare($query);
510 $sth->execute($shelfnumber);
511 my ($del_own, $del_oth)= $sth->fetchrow;
515 $query = qq(DELETE FROM virtualshelfcontents
516 WHERE shelfnumber=? AND biblionumber=? AND borrowernumber=?);
517 $sth= $dbh->prepare($query);
518 foreach my $biblionumber (@$bibref) {
519 $sth->execute($shelfnumber, $biblionumber, $user);
520 $r= $sth->rows; #Expect -1, 0 or 1 (-1 means Don't know; count as 1)
521 $t+= ($r==-1)? 1: $r;
525 #includes a check if borrowernumber is null (deleted patron)
526 $query = qq/DELETE FROM virtualshelfcontents
527 WHERE shelfnumber=? AND biblionumber=? AND
528 (borrowernumber IS NULL OR borrowernumber<>?)/;
529 $sth= $dbh->prepare($query);
530 foreach my $biblionumber (@$bibref) {
531 $sth->execute($shelfnumber, $biblionumber, $user);
533 $t+= ($r==-1)? 1: $r;
541 $Number = DelShelf($shelfnumber);
543 This function deletes the shelf number, and all of it's content.
544 Authorization to do so MUST have been checked before calling, while using
545 ShelfPossibleAction with manage parameter.
550 my ($shelfnumber)= @_;
551 return unless $shelfnumber && $shelfnumber =~ /^\d+$/;
552 my $sth = $dbh->prepare("DELETE FROM virtualshelves WHERE shelfnumber=?");
553 return $sth->execute($shelfnumber);
556 =head2 GetBibliosShelves
558 This finds all the public lists that this bib record is in.
562 sub GetBibliosShelves {
563 my ( $biblionumber ) = @_;
564 my $dbh = C4::Context->dbh;
565 my $sth = $dbh->prepare('
566 SELECT vs.shelfname, vs.shelfnumber
567 FROM virtualshelves vs
568 JOIN virtualshelfcontents vc ON (vs.shelfnumber= vc.shelfnumber)
570 AND vc.biblionumber= ?
572 $sth->execute( $biblionumber );
573 return $sth->fetchall_arrayref({});
578 $howmany= ShelvesMax($context);
580 Tells how much shelves are shown in which context.
581 POPUP refers to addbybiblionumber popup, MGRPAGE is managing page (in opac or
582 staff), COMBO refers to the Add to-combo of search results. MASTHEAD is the
583 main Koha toolbar with Lists button.
589 return SHELVES_POPUP_MAX if $which eq 'POPUP';
590 return SHELVES_MGRPAGE_MAX if $which eq 'MGRPAGE';
591 return SHELVES_COMBO_MAX if $which eq 'COMBO';
592 return SHELVES_MASTHEAD_MAX if $which eq 'MASTHEAD';
593 return SHELVES_MASTHEAD_MAX;
596 sub HandleDelBorrower {
597 #when a member is deleted (DelMember in Members.pm), you should call me first
598 #this routine deletes/moves lists and entries for the deleted member/borrower
599 #you could just delete everything (and lose more than you want)
600 #instead we now try to save all public/shared stuff and keep others happy
603 my $dbh = C4::Context->dbh;
605 #Delete shares of this borrower (not lists !)
606 $query="DELETE FROM virtualshelfshares WHERE borrowernumber=?";
607 $dbh->do($query,undef,($borrower));
609 #Delete private lists without owner that now have no shares anymore
610 $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";
613 #Change owner for private lists which have shares
614 $query="UPDATE virtualshelves LEFT JOIN virtualshelfshares sh USING (shelfnumber) SET owner=NULL where owner=? AND category=1 AND sh.borrowernumber IS NOT NULL";
615 $dbh->do($query,undef,($borrower));
617 #Delete unshared private lists
618 $query="DELETE FROM virtualshelves WHERE owner=? AND category=1";
619 $dbh->do($query,undef,($borrower));
621 #Handle public lists owned by borrower
622 $query="UPDATE virtualshelves SET owner=NULL WHERE owner=? AND category=2";
623 $dbh->do($query,undef,($borrower));
625 #Handle entries added by borrower to lists of others
626 $query="UPDATE virtualshelfcontents SET borrowernumber=NULL WHERE borrowernumber=?";
627 $dbh->do($query,undef,($borrower));
633 my ($owner, $category) = @_;
635 # Find out how many shelves total meet the submitted criteria...
637 my $query = "SELECT count(*) FROM virtualshelves vs ";
640 LEFT JOIN virtualshelfshares sh ON sh.shelfnumber=vs.shelfnumber
641 AND sh.borrowernumber=?
642 WHERE category=1 AND (vs.owner=? OR sh.borrowernumber=?) };
643 @params= ($owner, $owner, $owner);
646 $query.='WHERE category=2';
649 my $sth = $dbh->prepare($query);
650 $sth->execute(@params);
651 my ($total)= $sth->fetchrow;
655 sub _biblionumber_sth { #only used in obsolete sub below
657 my $query = 'select biblionumber from virtualshelfcontents where shelfnumber = ?';
658 my $dbh = C4::Context->dbh;
659 my $sth = $dbh->prepare($query)
661 $sth->execute( $shelf )
666 sub each_biblionumbers (&$) { #OBSOLETE
667 my ($code,$shelf) = @_;
668 my $ref = _biblionumber_sth($shelf)->fetchall_arrayref;
675 sub _CheckShelfName {
676 my ($name, $cat, $owner, $number)= @_;
679 SELECT DISTINCT shelfnumber
681 LEFT JOIN virtualshelfshares sh USING (shelfnumber)
682 WHERE shelfname=? AND shelfnumber<>?);
684 $query.= ' AND (sh.borrowernumber=? OR owner=?) AND category=1';
687 $query.= ' AND category=2';
689 my $sth = $dbh->prepare($query);
690 $sth->execute($cat==1? ($name, $number, $owner, $owner): ($name, $number));
691 return $sth->rows>0? 0: 1;
700 Koha Development Team <http://koha-community.org/>
704 C4::Circulation::Circ2(3)