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
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) = &GetShelf($shelfnumber);
220 Looks up information about the contents of virtual virtualshelves number
223 Returns the database's information on 'virtualshelves' table.
228 my ($shelfnumber) = @_;
230 SELECT shelfnumber, shelfname, owner, category, sortfield
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 LEFT 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)= @_;
312 #initialize missing hash values to silence warnings
313 foreach('shelfname','category', 'sortfield', 'allow_add', 'allow_delete_own', 'allow_delete_other' ) {
314 $hashref->{$_}= exists $hashref->{$_}? $hashref->{$_}||'': '';
317 return -1 unless _CheckShelfName($hashref->{shelfname}, $hashref->{category}, $owner, 0);
319 my $query = qq(INSERT INTO virtualshelves
320 (shelfname,owner,category,sortfield,allow_add,allow_delete_own,allow_delete_other)
321 VALUES (?,?,?,?,?,?,?));
323 my $sth = $dbh->prepare($query);
325 $hashref->{shelfname},
327 $hashref->{category},
328 $hashref->{sortfield},
329 $hashref->{allow_add}||0,
330 $hashref->{allow_delete_own}||1,
331 $hashref->{allow_delete_other}||0 );
332 my $shelfnumber = $dbh->{'mysql_insertid'};
338 &AddToShelf($biblionumber, $shelfnumber, $borrower);
340 Adds bib number C<$biblionumber> to virtual virtualshelves number
341 C<$shelfnumber>, unless that bib is already on that shelf.
346 my ($biblionumber, $shelfnumber, $borrowernumber) = @_;
347 return unless $biblionumber;
350 FROM virtualshelfcontents
351 WHERE shelfnumber=? AND biblionumber=?
353 my $sth = $dbh->prepare($query);
355 $sth->execute( $shelfnumber, $biblionumber );
356 ($sth->rows) and return undef; # already on shelf
358 INSERT INTO virtualshelfcontents
359 (shelfnumber, biblionumber, flags, borrowernumber)
360 VALUES (?, ?, 0, ?));
361 $sth = $dbh->prepare($query);
362 $sth->execute( $shelfnumber, $biblionumber, $borrowernumber);
363 $query = qq(UPDATE virtualshelves
364 SET lastmodified = CURRENT_TIMESTAMP
365 WHERE shelfnumber = ?);
366 $sth = $dbh->prepare($query);
367 $sth->execute( $shelfnumber );
372 my $result= ModShelf($shelfnumber, $hashref)
374 Where $hashref->{column} = param
376 Modify the value into virtualshelves table with values given
377 from hashref, which each key of the hashref should be
378 the name of a column of virtualshelves.
379 Fields like shelfnumber or owner cannot be changed.
381 Returns 1 if the action seemed to be successful.
386 my ($shelfnumber,$hashref) = @_;
388 my $query= "SELECT * FROM virtualshelves WHERE shelfnumber=?";
389 my $sth = $dbh->prepare($query);
390 $sth->execute($shelfnumber);
391 my $oldrecord= $sth->fetchrow_hashref;
392 return 0 unless $oldrecord; #not found?
394 #initialize missing hash values to silence warnings
395 foreach('shelfname','category', 'sortfield', 'allow_add', 'allow_delete_own', 'allow_delete_other' ) {
396 $hashref->{$_}= exists $hashref->{$_}? $hashref->{$_}||'': '';
399 #if name or category changes, the name should be tested
400 if($hashref->{shelfname} || $hashref->{category}) {
401 unless(_CheckShelfName(
402 $hashref->{shelfname}||$oldrecord->{shelfname},
403 $hashref->{category}||$oldrecord->{category},
404 $oldrecord->{owner}, $shelfnumber )) {
405 return 0; #name check failed
409 #only the following fields from the hash may be changed
410 $query= "UPDATE virtualshelves SET shelfname=?, category=?, sortfield=?, allow_add=?, allow_delete_own=?, allow_delete_other=? WHERE shelfnumber=?";
411 $sth = $dbh->prepare($query);
413 $hashref->{shelfname}||$oldrecord->{shelfname},
414 $hashref->{category}||$oldrecord->{category},
415 $hashref->{sortfield}||$oldrecord->{sortfield},
416 $hashref->{allow_add}||$oldrecord->{allow_add},
417 $hashref->{allow_delete_own}||$oldrecord->{allow_delete_own},
418 $hashref->{allow_delete_other}||$oldrecord->{allow_delete_other},
423 =head2 ShelfPossibleAction
425 ShelfPossibleAction($loggedinuser, $shelfnumber, $action);
427 C<$loggedinuser,$shelfnumber,$action>
429 $action can be "view", "add", "delete", "manage", "new_public", "new_private".
430 Note that add/delete here refers to adding/deleting entries from the list. Deleting the list itself falls under manage.
431 new_public and new_private refers to creating a new public or private list.
432 The distinction between deleting your own entries from the list or entries from
433 others is made in DelFromShelf.
435 Returns 1 if the user can do the $action in the $shelfnumber shelf.
440 sub ShelfPossibleAction {
441 my ( $user, $shelfnumber, $action ) = @_;
442 $action= 'view' unless $action;
443 $user=0 unless $user;
445 if($action =~ /^new/) { #no shelfnumber needed
446 if($action eq 'new_private') {
449 elsif($action eq 'new_public') {
450 return $user>0 && C4::Context->preference('OpacAllowPublicListCreation');
455 return 0 unless defined($shelfnumber);
458 SELECT IFNULL(owner,0) AS owner, category, allow_add, allow_delete_own, allow_delete_other, IFNULL(sh.borrowernumber,0) AS borrowernumber
459 FROM virtualshelves vs
460 LEFT JOIN virtualshelfshares sh ON sh.shelfnumber=vs.shelfnumber
461 AND sh.borrowernumber=?
462 WHERE vs.shelfnumber=?
464 my $sth = $dbh->prepare($query);
465 $sth->execute($user, $shelfnumber);
466 my $shelf= $sth->fetchrow_hashref;
468 return 0 unless $shelf && ($shelf->{category}==2 || $shelf->{owner}==$user || $shelf->{borrowernumber}==$user);
469 if($action eq 'view') {
470 #already handled in the above condition
473 elsif($action eq 'add') {
474 return 0 if $user<=0; #should be logged in
475 return 1 if $shelf->{allow_add}==1 || $shelf->{owner}==$user;
476 #owner may always add
478 elsif($action eq 'delete') {
479 #this answer is just diplomatic: it says that you may be able to delete
480 #some items from that shelf
481 #it does not answer the question about a specific biblio
482 #DelFromShelf checks the situation per biblio
483 return 1 if $user>0 && ($shelf->{allow_delete_own}==1 || $shelf->{allow_delete_other}==1);
485 elsif($action eq 'manage') {
486 return 1 if $user && $shelf->{owner}==$user;
493 $result= &DelFromShelf( $bibref, $shelfnumber, $user);
495 Removes biblionumbers in passed arrayref from shelf C<$shelfnumber>.
496 If the bib wasn't on that virtualshelves to begin with, nothing happens.
498 Returns 0 if no items have been deleted.
503 my ($bibref, $shelfnumber, $user) = @_;
504 my $query = qq(SELECT allow_delete_own, allow_delete_other FROM virtualshelves WHERE shelfnumber=?);
505 my $sth= $dbh->prepare($query);
506 $sth->execute($shelfnumber);
507 my ($del_own, $del_oth)= $sth->fetchrow;
511 $query = qq(DELETE FROM virtualshelfcontents
512 WHERE shelfnumber=? AND biblionumber=? AND borrowernumber=?);
513 $sth= $dbh->prepare($query);
514 foreach my $biblionumber (@$bibref) {
515 $sth->execute($shelfnumber, $biblionumber, $user);
516 $r= $sth->rows; #Expect -1, 0 or 1 (-1 means Don't know; count as 1)
517 $t+= ($r==-1)? 1: $r;
521 #includes a check if borrowernumber is null (deleted patron)
522 $query = qq/DELETE FROM virtualshelfcontents
523 WHERE shelfnumber=? AND biblionumber=? AND
524 (borrowernumber IS NULL OR borrowernumber<>?)/;
525 $sth= $dbh->prepare($query);
526 foreach my $biblionumber (@$bibref) {
527 $sth->execute($shelfnumber, $biblionumber, $user);
529 $t+= ($r==-1)? 1: $r;
537 $Number = DelShelf($shelfnumber);
539 This function deletes the shelf number, and all of it's content.
540 Authorization to do so MUST have been checked before calling, while using
541 ShelfPossibleAction with manage parameter.
546 my ($shelfnumber)= @_;
547 return unless $shelfnumber && $shelfnumber =~ /^\d+$/;
548 my $sth = $dbh->prepare("DELETE FROM virtualshelves WHERE shelfnumber=?");
549 return $sth->execute($shelfnumber);
552 =head2 GetBibliosShelves
554 This finds all the public lists that this bib record is in.
558 sub GetBibliosShelves {
559 my ( $biblionumber ) = @_;
560 my $dbh = C4::Context->dbh;
561 my $sth = $dbh->prepare('
562 SELECT vs.shelfname, vs.shelfnumber
563 FROM virtualshelves vs
564 JOIN virtualshelfcontents vc ON (vs.shelfnumber= vc.shelfnumber)
566 AND vc.biblionumber= ?
568 $sth->execute( $biblionumber );
569 return $sth->fetchall_arrayref({});
574 $howmany= ShelvesMax($context);
576 Tells how much shelves are shown in which context.
577 POPUP refers to addbybiblionumber popup, MGRPAGE is managing page (in opac or
578 staff), COMBO refers to the Add to-combo of search results. MASTHEAD is the
579 main Koha toolbar with Lists button.
585 return SHELVES_POPUP_MAX if $which eq 'POPUP';
586 return SHELVES_MGRPAGE_MAX if $which eq 'MGRPAGE';
587 return SHELVES_COMBO_MAX if $which eq 'COMBO';
588 return SHELVES_MASTHEAD_MAX if $which eq 'MASTHEAD';
589 return SHELVES_MASTHEAD_MAX;
592 sub HandleDelBorrower {
593 #when a member is deleted (DelMember in Members.pm), you should call me first
594 #this routine deletes/moves lists and entries for the deleted member/borrower
595 #you could just delete everything (and lose more than you want)
596 #instead we now try to save all public/shared stuff and keep others happy
599 my $dbh = C4::Context->dbh;
601 #Delete shares of this borrower (not lists !)
602 $query="DELETE FROM virtualshelfshares WHERE borrowernumber=?";
603 $dbh->do($query,undef,($borrower));
605 #Delete private lists without owner that now have no shares anymore
606 $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";
609 #Change owner for private lists which have shares
610 $query="UPDATE virtualshelves LEFT JOIN virtualshelfshares sh USING (shelfnumber) SET owner=NULL where owner=? AND category=1 AND sh.borrowernumber IS NOT NULL";
611 $dbh->do($query,undef,($borrower));
613 #Delete unshared private lists
614 $query="DELETE FROM virtualshelves WHERE owner=? AND category=1";
615 $dbh->do($query,undef,($borrower));
617 #Handle public lists owned by borrower
618 $query="UPDATE virtualshelves SET owner=NULL WHERE owner=? AND category=2";
619 $dbh->do($query,undef,($borrower));
621 #Handle entries added by borrower to lists of others
622 $query="UPDATE virtualshelfcontents SET borrowernumber=NULL WHERE borrowernumber=?";
623 $dbh->do($query,undef,($borrower));
629 my ($owner, $category) = @_;
631 # Find out how many shelves total meet the submitted criteria...
633 my $query = "SELECT count(*) FROM virtualshelves vs ";
636 LEFT JOIN virtualshelfshares sh ON sh.shelfnumber=vs.shelfnumber
637 AND sh.borrowernumber=?
638 WHERE category=1 AND (vs.owner=? OR sh.borrowernumber=?) };
639 @params= ($owner, $owner, $owner);
642 $query.='WHERE category=2';
645 my $sth = $dbh->prepare($query);
646 $sth->execute(@params);
647 my ($total)= $sth->fetchrow;
651 sub _biblionumber_sth { #only used in obsolete sub below
653 my $query = 'select biblionumber from virtualshelfcontents where shelfnumber = ?';
654 my $dbh = C4::Context->dbh;
655 my $sth = $dbh->prepare($query)
657 $sth->execute( $shelf )
662 sub each_biblionumbers (&$) { #OBSOLETE
663 my ($code,$shelf) = @_;
664 my $ref = _biblionumber_sth($shelf)->fetchall_arrayref;
671 sub _CheckShelfName {
672 my ($name, $cat, $owner, $number)= @_;
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)