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
53 my $dbh = C4::Context->dbh;
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, $totshelves) = &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.
92 =item C<$shelfhash-E<gt>{count}>
94 The number of virtuals on that virtualshelves.
101 my ($category, $row_count, $offset, $owner) = @_;
103 my $total = _shelf_count($owner, $category);
105 SELECT vs.shelfnumber, vs.shelfname,vs.owner,
106 bo.surname,bo.firstname,vs.category,vs.sortfield,
107 count(vc.biblionumber) as count
108 FROM virtualshelves vs
109 LEFT JOIN borrowers bo ON vs.owner=bo.borrowernumber
110 LEFT JOIN virtualshelfcontents vc USING (shelfnumber) };
113 LEFT JOIN virtualshelfshares sh ON sh.shelfnumber=vs.shelfnumber
114 AND sh.borrowernumber=?
115 WHERE category=1 AND (vs.owner=? OR sh.borrowernumber=?) };
116 @params= ($owner, $owner, $owner, $offset||0, $row_count);
119 $query.= 'WHERE category=2 ';
120 @params= ($offset||0, $row_count);
123 GROUP BY vs.shelfnumber
124 ORDER BY vs.shelfname
127 my $sth2 = $dbh->prepare($query);
128 $sth2->execute(@params);
130 while( my ($shelfnumber, $shelfname, $owner, $surname, $firstname, $category, $sortfield, $count)= $sth2->fetchrow) {
131 $shelflist{$shelfnumber}->{'shelfname'} = $shelfname;
132 $shelflist{$shelfnumber}->{'count'} = $count;
133 $shelflist{$shelfnumber}->{'single'} = $count==1;
134 $shelflist{$shelfnumber}->{'sortfield'} = $sortfield;
135 $shelflist{$shelfnumber}->{'category'} = $category;
136 $shelflist{$shelfnumber}->{'owner'} = $owner;
137 $shelflist{$shelfnumber}->{'surname'} = $surname;
138 $shelflist{$shelfnumber}->{'firstname'} = $firstname;
140 return ( \%shelflist, $total );
145 $shelflist = GetAllShelves($category, $owner)
147 This function returns a reference to an array of hashrefs containing all shelves
148 sorted by the shelf name.
150 This function is intended to return a dataset reflecting all the shelves for
151 the submitted parameters.
156 my ($category,$owner,$adding_allowed) = @_;
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);
188 my $bquery = 'SELECT vs.shelfnumber, vs.shelfname FROM virtualshelves vs ';
189 my $limit= ShelvesMax($purpose);
191 my $qry1= $bquery."WHERE vs.category=2 ";
192 $qry1.= "AND (allow_add=1 OR owner=?) " if $adding_allowed;
193 push @params, $owner||0 if $adding_allowed;
194 $qry1.= "ORDER BY vs.lastmodified DESC LIMIT $limit";
196 unless($adding_allowed && (!defined($owner) || $owner<=0)) {
197 #if adding items, user should be known
198 $pub= $dbh->selectall_arrayref($qry1,{Slice=>{}},@params);
202 my $qry2= $bquery. qq{
203 LEFT JOIN virtualshelfshares sh ON sh.shelfnumber=vs.shelfnumber AND sh.borrowernumber=?
204 WHERE vs.category=1 AND (vs.owner=? OR sh.borrowernumber=?) };
205 @params=($owner,$owner,$owner);
206 $qry2.= "AND (allow_add=1 OR owner=?) " if $adding_allowed;
207 push @params, $owner if $adding_allowed;
208 $qry2.= "ORDER BY vs.lastmodified DESC ";
209 $qry2.= "LIMIT $limit";
210 $bar= $dbh->selectall_arrayref($qry2,{Slice=>{}},@params);
213 return ( { bartotal => $bar? scalar @$bar: 0, pubtotal => $pub? scalar @$pub: 0}, $pub, $bar);
218 (shelfnumber,shelfname,owner,category,sortfield,allow_add,allow_delete_own,allow_delete_other) = &GetShelf($shelfnumber);
220 Returns the above-mentioned fields for passed virtual shelf number.
225 my ($shelfnumber) = @_;
227 SELECT shelfnumber, shelfname, owner, category, sortfield,
228 allow_add, allow_delete_own, allow_delete_other
232 my $sth = $dbh->prepare($query);
233 $sth->execute($shelfnumber);
234 return $sth->fetchrow;
237 =head2 GetShelfContents
239 $biblist = &GetShelfContents($shelfnumber);
241 Looks up information about the contents of virtual virtualshelves number
242 C<$shelfnumber>. Sorted by a field in the biblio table. copyrightdate
245 Returns a reference-to-array, whose elements are references-to-hash,
246 as returned by C<C4::Biblio::GetBiblioFromItemNumber>.
248 Note: the notforloan status comes from the itemtype, and where it equals 0
249 it does not ensure that related items.notforloan status is likewise 0. The
250 caller has to check any items on their own, possibly with CanBookBeIssued
251 from C4::Circulation.
255 sub GetShelfContents ($;$$$) {
256 my ($shelfnumber, $row_count, $offset, $sortfield) = @_;
257 my $dbh=C4::Context->dbh();
258 my $sth1 = $dbh->prepare("SELECT count(*) FROM virtualshelfcontents WHERE shelfnumber = ?");
259 $sth1->execute($shelfnumber);
260 my $total = $sth1->fetchrow;
262 my $sth2 = $dbh->prepare('SELECT sortfield FROM virtualshelves WHERE shelfnumber=?');
263 $sth2->execute($shelfnumber);
264 ($sortfield) = $sth2->fetchrow_array;
267 " SELECT vc.biblionumber, vc.shelfnumber, vc.dateadded, itemtypes.*,
268 biblio.*, biblioitems.itemtype, biblioitems.publicationyear as year, biblioitems.publishercode, biblioitems.place, biblioitems.size, biblioitems.pages
269 FROM virtualshelfcontents vc
270 LEFT JOIN biblio ON vc.biblionumber = biblio.biblionumber
271 LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber
272 LEFT JOIN itemtypes ON biblioitems.itemtype = itemtypes.itemtype
273 WHERE vc.shelfnumber=? ";
274 my @params = ($shelfnumber);
276 $query .= " ORDER BY " . $sortfield;
277 $query .= " DESC " if ($sortfield eq 'copyrightdate');
280 $query .= " LIMIT ?, ? ";
281 push (@params, ($offset ? $offset : 0));
282 push (@params, $row_count);
284 my $sth3 = $dbh->prepare($query);
285 $sth3->execute(@params);
286 return ($sth3->fetchall_arrayref({}), $total);
287 # Like the perldoc says,
288 # returns reference-to-array, where each element is reference-to-hash of the row:
289 # like [ $sth->fetchrow_hashref(), $sth->fetchrow_hashref() ... ]
290 # Suitable for use in TMPL_LOOP.
291 # See http://search.cpan.org/~timb/DBI-1.601/DBI.pm#fetchall_arrayref
292 # or newer, for your version of DBI.
297 $shelfnumber = &AddShelf($hashref, $owner);
299 Creates a new virtual shelf. Params passed in a hash like ModShelf.
301 Returns a code to know what's happen.
302 * -1 : if this virtualshelves already exists.
303 * $shelfnumber : if success.
308 my ($hashref, $owner)= @_;
310 #initialize missing hash values to silence warnings
311 foreach('shelfname','category', 'sortfield', 'allow_add', 'allow_delete_own', 'allow_delete_other' ) {
312 $hashref->{$_}= undef unless exists $hashref->{$_};
315 return -1 unless _CheckShelfName($hashref->{shelfname}, $hashref->{category}, $owner, 0);
317 my $query = qq(INSERT INTO virtualshelves
318 (shelfname,owner,category,sortfield,allow_add,allow_delete_own,allow_delete_other)
319 VALUES (?,?,?,?,?,?,?));
321 my $sth = $dbh->prepare($query);
323 $hashref->{shelfname},
325 $hashref->{category},
326 $hashref->{sortfield},
327 $hashref->{allow_add}//0,
328 $hashref->{allow_delete_own}//1,
329 $hashref->{allow_delete_other}//0 );
330 my $shelfnumber = $dbh->{'mysql_insertid'};
336 &AddToShelf($biblionumber, $shelfnumber, $borrower);
338 Adds bib number C<$biblionumber> to virtual virtualshelves number
339 C<$shelfnumber>, unless that bib is already on that shelf.
344 my ($biblionumber, $shelfnumber, $borrowernumber) = @_;
345 return unless $biblionumber;
348 FROM virtualshelfcontents
349 WHERE shelfnumber=? AND biblionumber=?
351 my $sth = $dbh->prepare($query);
353 $sth->execute( $shelfnumber, $biblionumber );
354 ($sth->rows) and return undef; # already on shelf
356 INSERT INTO virtualshelfcontents
357 (shelfnumber, biblionumber, flags, borrowernumber)
358 VALUES (?, ?, 0, ?));
359 $sth = $dbh->prepare($query);
360 $sth->execute( $shelfnumber, $biblionumber, $borrowernumber);
361 $query = qq(UPDATE virtualshelves
362 SET lastmodified = CURRENT_TIMESTAMP
363 WHERE shelfnumber = ?);
364 $sth = $dbh->prepare($query);
365 $sth->execute( $shelfnumber );
370 my $result= ModShelf($shelfnumber, $hashref)
372 Where $hashref->{column} = param
374 Modify the value into virtualshelves table with values given
375 from hashref, which each key of the hashref should be
376 the name of a column of virtualshelves.
377 Fields like shelfnumber or owner cannot be changed.
379 Returns 1 if the action seemed to be successful.
384 my ($shelfnumber,$hashref) = @_;
386 my $query= "SELECT * FROM virtualshelves WHERE shelfnumber=?";
387 my $sth = $dbh->prepare($query);
388 $sth->execute($shelfnumber);
389 my $oldrecord= $sth->fetchrow_hashref;
390 return 0 unless $oldrecord; #not found?
392 #initialize missing hash values to silence warnings
393 foreach('shelfname','category', 'sortfield', 'allow_add', 'allow_delete_own', 'allow_delete_other' ) {
394 $hashref->{$_}= undef unless exists $hashref->{$_};
397 #if name or category changes, the name should be tested
398 if($hashref->{shelfname} || $hashref->{category}) {
399 unless(_CheckShelfName(
400 $hashref->{shelfname}//$oldrecord->{shelfname},
401 $hashref->{category}//$oldrecord->{category},
404 return 0; #name check failed
408 #only the following fields from the hash may be changed
409 $query= "UPDATE virtualshelves SET shelfname=?, category=?, sortfield=?, allow_add=?, allow_delete_own=?, allow_delete_other=? WHERE shelfnumber=?";
410 $sth = $dbh->prepare($query);
412 $hashref->{shelfname}//$oldrecord->{shelfname},
413 $hashref->{category}//$oldrecord->{category},
414 $hashref->{sortfield}//$oldrecord->{sortfield},
415 $hashref->{allow_add}//$oldrecord->{allow_add},
416 $hashref->{allow_delete_own}//$oldrecord->{allow_delete_own},
417 $hashref->{allow_delete_other}//$oldrecord->{allow_delete_other},
422 =head2 ShelfPossibleAction
424 ShelfPossibleAction($loggedinuser, $shelfnumber, $action);
426 C<$loggedinuser,$shelfnumber,$action>
428 $action can be "view", "add", "delete", "manage", "new_public", "new_private".
429 Note that add/delete here refers to adding/deleting entries from the list. Deleting the list itself falls under manage.
430 new_public and new_private refers to creating a new public or private list.
431 The distinction between deleting your own entries from the list or entries from
432 others is made in DelFromShelf.
434 Returns 1 if the user can do the $action in the $shelfnumber shelf.
439 sub ShelfPossibleAction {
440 my ( $user, $shelfnumber, $action ) = @_;
441 $action= 'view' unless $action;
442 $user=0 unless $user;
444 if($action =~ /^new/) { #no shelfnumber needed
445 if($action eq 'new_private') {
448 elsif($action eq 'new_public') {
449 return $user>0 && C4::Context->preference('OpacAllowPublicListCreation');
454 return 0 unless defined($shelfnumber);
457 SELECT IFNULL(owner,0) AS owner, category, allow_add, allow_delete_own, allow_delete_other, IFNULL(sh.borrowernumber,0) AS borrowernumber
458 FROM virtualshelves vs
459 LEFT JOIN virtualshelfshares sh ON sh.shelfnumber=vs.shelfnumber
460 AND sh.borrowernumber=?
461 WHERE vs.shelfnumber=?
463 my $sth = $dbh->prepare($query);
464 $sth->execute($user, $shelfnumber);
465 my $shelf= $sth->fetchrow_hashref;
467 return 0 unless $shelf && ($shelf->{category}==2 || $shelf->{owner}==$user || $shelf->{borrowernumber}==$user);
468 if($action eq 'view') {
469 #already handled in the above condition
472 elsif($action eq 'add') {
473 return 0 if $user<=0; #should be logged in
474 return 1 if $shelf->{allow_add}==1 || $shelf->{owner}==$user;
475 #owner may always add
477 elsif($action eq 'delete') {
478 #this answer is just diplomatic: it says that you may be able to delete
479 #some items from that shelf
480 #it does not answer the question about a specific biblio
481 #DelFromShelf checks the situation per biblio
482 return 1 if $user>0 && ($shelf->{allow_delete_own}==1 || $shelf->{allow_delete_other}==1);
484 elsif($action eq 'manage') {
485 return 1 if $user && $shelf->{owner}==$user;
492 $result= &DelFromShelf( $bibref, $shelfnumber, $user);
494 Removes biblionumbers in passed arrayref from shelf C<$shelfnumber>.
495 If the bib wasn't on that virtualshelves to begin with, nothing happens.
497 Returns 0 if no items have been deleted.
502 my ($bibref, $shelfnumber, $user) = @_;
503 my $query = qq(SELECT allow_delete_own, allow_delete_other FROM virtualshelves WHERE shelfnumber=?);
504 my $sth= $dbh->prepare($query);
505 $sth->execute($shelfnumber);
506 my ($del_own, $del_oth)= $sth->fetchrow;
510 $query = qq(DELETE FROM virtualshelfcontents
511 WHERE shelfnumber=? AND biblionumber=? AND borrowernumber=?);
512 $sth= $dbh->prepare($query);
513 foreach my $biblionumber (@$bibref) {
514 $sth->execute($shelfnumber, $biblionumber, $user);
515 $r= $sth->rows; #Expect -1, 0 or 1 (-1 means Don't know; count as 1)
516 $t+= ($r==-1)? 1: $r;
520 #includes a check if borrowernumber is null (deleted patron)
521 $query = qq/DELETE FROM virtualshelfcontents
522 WHERE shelfnumber=? AND biblionumber=? AND
523 (borrowernumber IS NULL OR borrowernumber<>?)/;
524 $sth= $dbh->prepare($query);
525 foreach my $biblionumber (@$bibref) {
526 $sth->execute($shelfnumber, $biblionumber, $user);
528 $t+= ($r==-1)? 1: $r;
536 $Number = DelShelf($shelfnumber);
538 This function deletes the shelf number, and all of it's content.
539 Authorization to do so MUST have been checked before calling, while using
540 ShelfPossibleAction with manage parameter.
545 my ($shelfnumber)= @_;
546 return unless $shelfnumber && $shelfnumber =~ /^\d+$/;
547 my $sth = $dbh->prepare("DELETE FROM virtualshelves WHERE shelfnumber=?");
548 return $sth->execute($shelfnumber);
551 =head2 GetBibliosShelves
553 This finds all the public lists that this bib record is in.
557 sub GetBibliosShelves {
558 my ( $biblionumber ) = @_;
559 my $dbh = C4::Context->dbh;
560 my $sth = $dbh->prepare('
561 SELECT vs.shelfname, vs.shelfnumber
562 FROM virtualshelves vs
563 JOIN virtualshelfcontents vc ON (vs.shelfnumber= vc.shelfnumber)
565 AND vc.biblionumber= ?
567 $sth->execute( $biblionumber );
568 return $sth->fetchall_arrayref({});
573 $howmany= ShelvesMax($context);
575 Tells how much shelves are shown in which context.
576 POPUP refers to addbybiblionumber popup, MGRPAGE is managing page (in opac or
577 staff), COMBO refers to the Add to-combo of search results. MASTHEAD is the
578 main Koha toolbar with Lists button.
584 return SHELVES_POPUP_MAX if $which eq 'POPUP';
585 return SHELVES_MGRPAGE_MAX if $which eq 'MGRPAGE';
586 return SHELVES_COMBO_MAX if $which eq 'COMBO';
587 return SHELVES_MASTHEAD_MAX if $which eq 'MASTHEAD';
588 return SHELVES_MASTHEAD_MAX;
591 sub HandleDelBorrower {
592 #when a member is deleted (DelMember in Members.pm), you should call me first
593 #this routine deletes/moves lists and entries for the deleted member/borrower
594 #you could just delete everything (and lose more than you want)
595 #instead we now try to save all public/shared stuff and keep others happy
598 my $dbh = C4::Context->dbh;
600 #Delete shares of this borrower (not lists !)
601 $query="DELETE FROM virtualshelfshares WHERE borrowernumber=?";
602 $dbh->do($query,undef,($borrower));
604 #Delete private lists without owner that now have no shares anymore
605 $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";
608 #Change owner for private lists which have shares
609 $query="UPDATE virtualshelves LEFT JOIN virtualshelfshares sh USING (shelfnumber) SET owner=NULL where owner=? AND category=1 AND sh.borrowernumber IS NOT NULL";
610 $dbh->do($query,undef,($borrower));
612 #Delete unshared private lists
613 $query="DELETE FROM virtualshelves WHERE owner=? AND category=1";
614 $dbh->do($query,undef,($borrower));
616 #Handle public lists owned by borrower
617 $query="UPDATE virtualshelves SET owner=NULL WHERE owner=? AND category=2";
618 $dbh->do($query,undef,($borrower));
620 #Handle entries added by borrower to lists of others
621 $query="UPDATE virtualshelfcontents SET borrowernumber=NULL WHERE borrowernumber=?";
622 $dbh->do($query,undef,($borrower));
628 my ($owner, $category) = @_;
630 # Find out how many shelves total meet the submitted criteria...
632 my $query = "SELECT count(*) FROM virtualshelves vs ";
635 LEFT JOIN virtualshelfshares sh ON sh.shelfnumber=vs.shelfnumber
636 AND sh.borrowernumber=?
637 WHERE category=1 AND (vs.owner=? OR sh.borrowernumber=?) };
638 @params= ($owner, $owner, $owner);
641 $query.='WHERE category=2';
644 my $sth = $dbh->prepare($query);
645 $sth->execute(@params);
646 my ($total)= $sth->fetchrow;
650 sub _biblionumber_sth { #only used in obsolete sub below
652 my $query = 'select biblionumber from virtualshelfcontents where shelfnumber = ?';
653 my $dbh = C4::Context->dbh;
654 my $sth = $dbh->prepare($query)
656 $sth->execute( $shelf )
661 sub each_biblionumbers (&$) { #OBSOLETE
662 my ($code,$shelf) = @_;
663 my $ref = _biblionumber_sth($shelf)->fetchall_arrayref;
670 sub _CheckShelfName {
671 my ($name, $cat, $owner, $number)= @_;
674 SELECT DISTINCT shelfnumber
676 LEFT JOIN virtualshelfshares sh USING (shelfnumber)
677 WHERE shelfname=? AND shelfnumber<>?);
679 $query.= ' AND (sh.borrowernumber=? OR owner=?) AND category=1';
682 $query.= ' AND category=2';
684 my $sth = $dbh->prepare($query);
685 $sth->execute($cat==1? ($name, $number, $owner, $owner): ($name, $number));
686 return $sth->rows>0? 0: 1;
695 Koha Development Team <http://koha-community.org/>
699 C4::Circulation::Circ2(3)