C4/VirtualShelves.pm

   1 package C4::VirtualShelves;
   2
   3 # Copyright 2000-2002 Katipo Communications
   4 #
   5 # This file is part of Koha.
   6 #
   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.
  11 #
  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.
  16 #
  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>.
  19
  20 use strict;
  21 use warnings;
  22
  23 use Carp;
  24 use C4::Context;
  25 use C4::Debug;
  26 use C4::Members;
  27
  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
  32
  33 use constant SHARE_INVITATION_EXPIRY_DAYS => 14; #two weeks to accept
  34
  35 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK);
  36
  37 BEGIN {
  38     # set the version for version checking
  39     $VERSION = 3.07.00.049;
  40     require Exporter;
  41     @ISA    = qw(Exporter);
  42     @EXPORT = qw(
  43             &GetShelves &GetShelfContents &GetShelf
  44             &AddToShelf &AddShelf
  45             &ModShelf
  46             &ShelfPossibleAction
  47             &DelFromShelf &DelShelf
  48             &GetBibliosShelves
  49             &AddShare &AcceptShare &RemoveShare &IsSharedList
  50     );
  51         @EXPORT_OK = qw(
  52             &GetAllShelves &ShelvesMax
  53         );
  54 }
  55
  56
  57 =head1 NAME
  58
  59 C4::VirtualShelves - Functions for manipulating Koha virtual shelves
  60
  61 =head1 SYNOPSIS
  62
  63   use C4::VirtualShelves;
  64
  65 =head1 DESCRIPTION
  66
  67 This module provides functions for manipulating virtual shelves,
  68 including creating and deleting virtual shelves, and adding and removing
  69 bibs to and from virtual shelves.
  70
  71 =head1 FUNCTIONS
  72
  73 =head2 GetShelves
  74
  75   $shelflist = &GetShelves($category, $row_count, $offset, $owner);
  76   ($shelfnumber, $shelfhash) = each %{$shelflist};
  77
  78 Returns the number of shelves specified by C<$row_count> and C<$offset> as well as the total
  79 number of shelves that meet the C<$owner> and C<$category> criteria.  C<$category>,
  80 C<$row_count>, and C<$offset> are required. C<$owner> must be supplied when C<$category> == 1.
  81 When C<$category> is 2, supply undef as argument for C<$owner>.
  82
  83 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.
  84
  85 C<$shelflist>is a reference-to-hash. The keys are the virtualshelves numbers (C<$shelfnumber>, above),
  86 and the values (C<$shelfhash>, above) are themselves references-to-hash, with the following keys:
  87
  88 =over
  89
  90 =item C<$shelfhash-E<gt>{shelfname}>
  91
  92 A string. The name of the shelf.
  93
  94 =back
  95
  96 =cut
  97
  98 sub GetShelves {
  99     my ($category, $row_count, $offset, $owner) = @_;
 100     $offset ||= 0;
 101     my @params = ( $offset, $row_count );
 102     my $dbh = C4::Context->dbh;
 103     my $query = qq{
 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) };
 110     if($category==1) {
 111         $query.= qq{
 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         unshift @params, ($owner) x 3;
 116     }
 117     else {
 118         $query.= 'WHERE category=2 ';
 119     }
 120     $query.= qq{
 121         GROUP BY vs.shelfnumber
 122         ORDER BY vs.shelfname
 123         LIMIT ?, ?};
 124
 125     my $sth2 = $dbh->prepare($query);
 126     $sth2->execute(@params);
 127     my %shelflist;
 128     while( my ($shelfnumber, $shelfname, $owner, $surname, $firstname, $category, $sortfield, $count)= $sth2->fetchrow) {
 129         $shelflist{$shelfnumber}->{'shelfname'} = $shelfname;
 130         $shelflist{$shelfnumber}->{'count'}     = $count;
 131         $shelflist{$shelfnumber}->{'single'}    = $count==1;
 132         $shelflist{$shelfnumber}->{'sortfield'} = $sortfield;
 133         $shelflist{$shelfnumber}->{'category'}  = $category;
 134         $shelflist{$shelfnumber}->{'owner'}     = $owner;
 135         $shelflist{$shelfnumber}->{'surname'}   = $surname;
 136         $shelflist{$shelfnumber}->{'firstname'} = $firstname;
 137     }
 138     return \%shelflist;
 139 }
 140
 141 =head2 GetAllShelves
 142
 143     $shelflist = GetAllShelves($category, $owner)
 144
 145 This function returns a reference to an array of hashrefs containing all shelves
 146 sorted by the shelf name.
 147
 148 This function is intended to return a dataset reflecting all the shelves for
 149 the submitted parameters.
 150
 151 =cut
 152
 153 sub GetAllShelves {
 154     my ($category,$owner,$adding_allowed) = @_;
 155     my @params;
 156     my $dbh = C4::Context->dbh;
 157     my $query = 'SELECT vs.* FROM virtualshelves vs ';
 158     if($category==1) {
 159         $query.= qq{
 160             LEFT JOIN virtualshelfshares sh ON sh.shelfnumber=vs.shelfnumber
 161             AND sh.borrowernumber=?
 162         WHERE category=1 AND (vs.owner=? OR sh.borrowernumber=?) };
 163         @params = ($owner, $owner, $owner);
 164     }
 165     else {
 166     $query.='WHERE category=2 ';
 167         @params = ();
 168     }
 169     $query.='AND (allow_add=1 OR owner=?) ' if $adding_allowed;
 170     push @params, $owner if $adding_allowed;
 171     $query.= 'ORDER BY shelfname ASC';
 172     my $sth = $dbh->prepare( $query );
 173     $sth->execute(@params);
 174     return $sth->fetchall_arrayref({});
 175 }
 176
 177 =head2 GetSomeShelfNames
 178
 179 Returns shelf names and numbers for Add to combo of search results and Lists button of OPAC header.
 180
 181 =cut
 182
 183 sub GetSomeShelfNames {
 184     my ($owner, $purpose, $adding_allowed)= @_;
 185     my ($bar, $pub, @params);
 186     my $dbh = C4::Context->dbh;
 187
 188     my $bquery = 'SELECT vs.shelfnumber, vs.shelfname FROM virtualshelves vs ';
 189     my $limit= ShelvesMax($purpose);
 190
 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";
 195
 196     unless($adding_allowed && (!defined($owner) || $owner<=0)) {
 197         #if adding items, user should be known
 198         $pub= $dbh->selectall_arrayref($qry1,{Slice=>{}},@params);
 199     }
 200
 201     if($owner) {
 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);
 211     }
 212
 213     return ( { bartotal => $bar? scalar @$bar: 0, pubtotal => $pub? scalar @$pub: 0}, $pub, $bar);
 214 }
 215
 216 =head2 GetShelf
 217
 218   (shelfnumber,shelfname,owner,category,sortfield,allow_add,allow_delete_own,allow_delete_other) = &GetShelf($shelfnumber);
 219
 220 Returns the above-mentioned fields for passed virtual shelf number.
 221
 222 =cut
 223
 224 sub GetShelf {
 225     my ($shelfnumber) = @_;
 226     my $dbh = C4::Context->dbh;
 227     my $query = qq(
 228         SELECT shelfnumber, shelfname, owner, category, sortfield,
 229             allow_add, allow_delete_own, allow_delete_other
 230         FROM   virtualshelves
 231         WHERE  shelfnumber=?
 232     );
 233     my $sth = $dbh->prepare($query);
 234     $sth->execute($shelfnumber);
 235     return $sth->fetchrow;
 236 }
 237
 238 =head2 GetShelfContents
 239
 240   $biblist = &GetShelfContents($shelfnumber);
 241
 242 Looks up information about the contents of virtual virtualshelves number
 243 C<$shelfnumber>.  Sorted by a field in the biblio table.  copyrightdate
 244 gives a desc sort.
 245
 246 Returns a reference-to-array, whose elements are references-to-hash,
 247 as returned by C<C4::Biblio::GetBiblioFromItemNumber>.
 248
 249 Note: the notforloan status comes from the itemtype, and where it equals 0
 250 it does not ensure that related items.notforloan status is likewise 0. The
 251 caller has to check any items on their own, possibly with CanBookBeIssued
 252 from C4::Circulation.
 253
 254 =cut
 255
 256 sub GetShelfContents {
 257     my ($shelfnumber, $row_count, $offset, $sortfield, $sort_direction ) = @_;
 258     my $dbh=C4::Context->dbh();
 259     my $sth1 = $dbh->prepare("SELECT count(*) FROM virtualshelfcontents WHERE shelfnumber = ?");
 260     $sth1->execute($shelfnumber);
 261     my $total = $sth1->fetchrow;
 262     if(!$sortfield) {
 263         my $sth2 = $dbh->prepare('SELECT sortfield FROM virtualshelves WHERE shelfnumber=?');
 264         $sth2->execute($shelfnumber);
 265         ($sortfield) = $sth2->fetchrow_array;
 266     }
 267     my $query =
 268        " SELECT DISTINCT vc.biblionumber, vc.shelfnumber, vc.dateadded, itemtypes.*,
 269             biblio.*, biblioitems.itemtype, biblioitems.publicationyear as year, biblioitems.publishercode, biblioitems.place, biblioitems.size, biblioitems.pages
 270          FROM   virtualshelfcontents vc
 271          JOIN biblio      ON      vc.biblionumber =      biblio.biblionumber
 272          LEFT JOIN biblioitems ON  biblio.biblionumber = biblioitems.biblionumber
 273          LEFT JOIN items ON items.biblionumber=vc.biblionumber
 274          LEFT JOIN itemtypes   ON biblioitems.itemtype = itemtypes.itemtype
 275          WHERE  vc.shelfnumber=? ";
 276     my @params = ($shelfnumber);
 277     if($sortfield) {
 278         $query .= " ORDER BY " . $dbh->quote_identifier( $sortfield );
 279         $query .= " DESC " if ( $sort_direction eq 'desc' );
 280     }
 281     if($row_count){
 282        $query .= " LIMIT ?, ? ";
 283        push (@params, ($offset ? $offset : 0));
 284        push (@params, $row_count);
 285     }
 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.
 295 }
 296
 297 =head2 AddShelf
 298
 299   $shelfnumber = &AddShelf($hashref, $owner);
 300
 301 Creates a new virtual shelf. Params passed in a hash like ModShelf.
 302
 303 Returns a code to know what's happen.
 304     * -1 : if this virtualshelves already exists.
 305     * $shelfnumber : if success.
 306
 307 =cut
 308
 309 sub AddShelf {
 310     my ($hashref, $owner)= @_;
 311     my $dbh = C4::Context->dbh;
 312
 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->{$_};
 316     }
 317
 318     return -1 unless _CheckShelfName($hashref->{shelfname}, $hashref->{category}, $owner, 0);
 319
 320     my $query = qq(INSERT INTO virtualshelves
 321         (shelfname,owner,category,sortfield,allow_add,allow_delete_own,allow_delete_other)
 322         VALUES (?,?,?,?,?,?,?));
 323
 324     my $sth = $dbh->prepare($query);
 325     $sth->execute(
 326         $hashref->{shelfname},
 327         $owner,
 328         $hashref->{category},
 329         $hashref->{sortfield},
 330         $hashref->{allow_add}//0,
 331         $hashref->{allow_delete_own}//1,
 332         $hashref->{allow_delete_other}//0 );
 333     return if $sth->err;
 334     my $shelfnumber = $dbh->{'mysql_insertid'};
 335     return $shelfnumber;
 336 }
 337
 338 =head2 AddToShelf
 339
 340   &AddToShelf($biblionumber, $shelfnumber, $borrower);
 341
 342 Adds bib number C<$biblionumber> to virtual virtualshelves number
 343 C<$shelfnumber>, unless that bib is already on that shelf.
 344
 345 =cut
 346
 347 sub AddToShelf {
 348     my ($biblionumber, $shelfnumber, $borrowernumber) = @_;
 349     return unless $biblionumber;
 350     my $dbh = C4::Context->dbh;
 351     my $query = qq(
 352         SELECT *
 353         FROM   virtualshelfcontents
 354         WHERE  shelfnumber=? AND biblionumber=?
 355     );
 356     my $sth = $dbh->prepare($query);
 357
 358     $sth->execute( $shelfnumber, $biblionumber );
 359     ($sth->rows) and return; # already on shelf
 360     $query = qq(
 361         INSERT INTO virtualshelfcontents
 362             (shelfnumber, biblionumber, flags, borrowernumber)
 363         VALUES (?, ?, 0, ?));
 364     $sth = $dbh->prepare($query);
 365     $sth->execute( $shelfnumber, $biblionumber, $borrowernumber);
 366     $query = qq(UPDATE virtualshelves
 367                 SET lastmodified = CURRENT_TIMESTAMP
 368                 WHERE shelfnumber = ?);
 369     $sth = $dbh->prepare($query);
 370     $sth->execute( $shelfnumber );
 371 }
 372
 373 =head2 ModShelf
 374
 375 my $result= ModShelf($shelfnumber, $hashref)
 376
 377 Where $hashref->{column} = param
 378
 379 Modify the value into virtualshelves table with values given
 380 from hashref, which each key of the hashref should be
 381 the name of a column of virtualshelves.
 382 Fields like shelfnumber or owner cannot be changed.
 383
 384 Returns 1 if the action seemed to be successful.
 385
 386 =cut
 387
 388 sub ModShelf {
 389     my ($shelfnumber,$hashref) = @_;
 390     my $dbh = C4::Context->dbh;
 391
 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?
 397
 398     #initialize missing hash values to silence warnings
 399     foreach('shelfname','category', 'sortfield', 'allow_add', 'allow_delete_own', 'allow_delete_other' ) {
 400         $hashref->{$_}= undef unless exists $hashref->{$_};
 401     }
 402
 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},
 409             $shelfnumber )) {
 410                 return 0; #name check failed
 411         }
 412     }
 413
 414     #only the following fields from the hash may be changed
 415     $query= "UPDATE virtualshelves SET shelfname=?, category=?, sortfield=?, allow_add=?, allow_delete_own=?, allow_delete_other=? WHERE shelfnumber=?";
 416     $sth = $dbh->prepare($query);
 417     $sth->execute(
 418         $hashref->{shelfname}//$oldrecord->{shelfname},
 419         $hashref->{category}//$oldrecord->{category},
 420         $hashref->{sortfield}//$oldrecord->{sortfield},
 421         $hashref->{allow_add}//$oldrecord->{allow_add},
 422         $hashref->{allow_delete_own}//$oldrecord->{allow_delete_own},
 423         $hashref->{allow_delete_other}//$oldrecord->{allow_delete_other},
 424         $shelfnumber );
 425     return $@? 0: 1;
 426 }
 427
 428 =head2 ShelfPossibleAction
 429
 430 ShelfPossibleAction($loggedinuser, $shelfnumber, $action);
 431
 432 C<$loggedinuser,$shelfnumber,$action>
 433
 434 $action can be "view", "add", "delete", "manage", "new_public", "new_private".
 435 New additional actions are: invite, acceptshare.
 436 Note that add/delete here refers to adding/deleting entries from the list. Deleting the list itself falls under manage.
 437 new_public and new_private refers to creating a new public or private list.
 438 The distinction between deleting your own entries from the list or entries from
 439 others is made in DelFromShelf.
 440
 441 Returns 1 if the user can do the $action in the $shelfnumber shelf.
 442 Returns 0 otherwise.
 443 For the actions invite and acceptshare a second errorcode is returned if the
 444 result is false. See opac-shareshelf.pl
 445
 446 =cut
 447
 448 sub ShelfPossibleAction {
 449     my ( $user, $shelfnumber, $action ) = @_;
 450     $action= 'view' unless $action;
 451     $user=0 unless $user;
 452
 453     if($action =~ /^new/) { #no shelfnumber needed
 454         if($action eq 'new_private') {
 455             return $user>0;
 456         }
 457         elsif($action eq 'new_public') {
 458             return $user>0 && C4::Context->preference('OpacAllowPublicListCreation');
 459         }
 460         return 0;
 461     }
 462
 463     return 0 unless defined($shelfnumber);
 464
 465     if ( $user > 0 and $action eq 'delete_shelf' ) {
 466         my $borrower = C4::Members::GetMember( borrowernumber => $user );
 467         require C4::Auth;
 468         return 1
 469             if C4::Auth::haspermission( $borrower->{userid}, { shelves => 'delete_public_lists' } );
 470     }
 471
 472     my $dbh = C4::Context->dbh;
 473     my $query = qq/
 474         SELECT COALESCE(owner,0) AS owner, category, allow_add, allow_delete_own, allow_delete_other, COALESCE(sh.borrowernumber,0) AS borrowernumber
 475         FROM virtualshelves vs
 476         LEFT JOIN virtualshelfshares sh ON sh.shelfnumber=vs.shelfnumber
 477         AND sh.borrowernumber=?
 478         WHERE vs.shelfnumber=?
 479     /;
 480     my $sth = $dbh->prepare($query);
 481     $sth->execute($user, $shelfnumber);
 482     my $shelf= $sth->fetchrow_hashref;
 483
 484     return 0 unless $shelf && ($shelf->{category}==2 || $shelf->{owner}==$user || ($user && $shelf->{borrowernumber}==$user));
 485     if($action eq 'view') {
 486         #already handled in the above condition
 487         return 1;
 488     }
 489     elsif($action eq 'add') {
 490         return 0 if $user<=0; #should be logged in
 491         return 1 if $shelf->{allow_add}==1 || $shelf->{owner}==$user;
 492         #owner may always add
 493     }
 494     elsif($action eq 'delete') {
 495         #this answer is just diplomatic: it says that you may be able to delete
 496         #some items from that shelf
 497         #it does not answer the question about a specific biblio
 498         #DelFromShelf checks the situation per biblio
 499         return 1 if $user>0 && ($shelf->{allow_delete_own}==1 || $shelf->{allow_delete_other}==1);
 500     }
 501     elsif($action eq 'invite') {
 502         #for sharing you must be the owner and the list must be private
 503         if( $shelf->{category}==1 ) {
 504             return 1 if $shelf->{owner}==$user;
 505             return (0, 4); # code 4: should be owner
 506         }
 507         else {
 508             return (0, 5); # code 5: should be private list
 509         }
 510     }
 511     elsif($action eq 'acceptshare') {
 512         #the key for accepting is checked later in AcceptShare
 513         #you must not be the owner, list must be private
 514         if( $shelf->{category}==1 ) {
 515             return (0, 8) if $shelf->{owner}==$user;
 516                 #code 8: should not be owner
 517             return 1;
 518         }
 519         else {
 520             return (0, 5); # code 5: should be private list
 521         }
 522     }
 523     elsif($action eq 'manage' or $action eq 'delete_shelf') {
 524         return 1 if $user && $shelf->{owner}==$user;
 525     }
 526     return 0;
 527 }
 528
 529 =head2 DelFromShelf
 530
 531     $result= &DelFromShelf( $bibref, $shelfnumber, $user);
 532
 533 Removes biblionumbers in passed arrayref from shelf C<$shelfnumber>.
 534 If the bib wasn't on that virtualshelves to begin with, nothing happens.
 535
 536 Returns 0 if no items have been deleted.
 537
 538 =cut
 539
 540 sub DelFromShelf {
 541     my ($bibref, $shelfnumber, $user) = @_;
 542     my $dbh = C4::Context->dbh;
 543     my $query = qq(SELECT allow_delete_own, allow_delete_other FROM virtualshelves WHERE shelfnumber=?);
 544     my $sth= $dbh->prepare($query);
 545     $sth->execute($shelfnumber);
 546     my ($del_own, $del_oth)= $sth->fetchrow;
 547     my $r; my $t=0;
 548
 549     if($del_own) {
 550         $query = qq(DELETE FROM virtualshelfcontents
 551             WHERE shelfnumber=? AND biblionumber=? AND borrowernumber=?);
 552         $sth= $dbh->prepare($query);
 553         foreach my $biblionumber (@$bibref) {
 554             $sth->execute($shelfnumber, $biblionumber, $user);
 555             $r= $sth->rows; #Expect -1, 0 or 1 (-1 means Don't know; count as 1)
 556             $t+= ($r==-1)? 1: $r;
 557         }
 558     }
 559     if($del_oth) {
 560         #includes a check if borrowernumber is null (deleted patron)
 561         $query = qq/DELETE FROM virtualshelfcontents
 562             WHERE shelfnumber=? AND biblionumber=? AND
 563             (borrowernumber IS NULL OR borrowernumber<>?)/;
 564         $sth= $dbh->prepare($query);
 565         foreach my $biblionumber (@$bibref) {
 566             $sth->execute($shelfnumber, $biblionumber, $user);
 567             $r= $sth->rows;
 568             $t+= ($r==-1)? 1: $r;
 569         }
 570     }
 571     return $t;
 572 }
 573
 574 =head2 DelShelf
 575
 576   $Number = DelShelf($shelfnumber);
 577
 578 This function deletes the shelf number, and all of it's content.
 579 Authorization to do so MUST have been checked before calling, while using
 580 ShelfPossibleAction with manage parameter.
 581
 582 =cut
 583
 584 sub DelShelf {
 585     my ($shelfnumber)= @_;
 586     return unless $shelfnumber && $shelfnumber =~ /^\d+$/;
 587     my $dbh = C4::Context->dbh;
 588     my $sth = $dbh->prepare("DELETE FROM virtualshelves WHERE shelfnumber=?");
 589     return $sth->execute($shelfnumber);
 590 }
 591
 592 =head2 GetBibliosShelves
 593
 594 This finds all the public lists that this bib record is in.
 595
 596 =cut
 597
 598 sub GetBibliosShelves {
 599     my ( $biblionumber )  = @_;
 600     my $dbh = C4::Context->dbh;
 601     my $sth = $dbh->prepare('
 602         SELECT vs.shelfname, vs.shelfnumber
 603         FROM virtualshelves vs
 604         JOIN virtualshelfcontents vc ON (vs.shelfnumber= vc.shelfnumber)
 605         WHERE vs.category=2
 606         AND vc.biblionumber= ?
 607     ');
 608     $sth->execute( $biblionumber );
 609     return $sth->fetchall_arrayref({});
 610 }
 611
 612 =head2 ShelvesMax
 613
 614     $howmany= ShelvesMax($context);
 615
 616 Tells how much shelves are shown in which context.
 617 POPUP refers to addbybiblionumber popup, MGRPAGE is managing page (in opac or
 618 staff), COMBO refers to the Add to-combo of search results. MASTHEAD is the
 619 main Koha toolbar with Lists button.
 620
 621 =cut
 622
 623 sub ShelvesMax {
 624     my $which= shift;
 625     return SHELVES_POPUP_MAX if $which eq 'POPUP';
 626     return SHELVES_MGRPAGE_MAX if $which eq 'MGRPAGE';
 627     return SHELVES_COMBO_MAX if $which eq 'COMBO';
 628     return SHELVES_MASTHEAD_MAX if $which eq 'MASTHEAD';
 629     return SHELVES_MASTHEAD_MAX;
 630 }
 631
 632 =head2 HandleDelBorrower
 633
 634      HandleDelBorrower($borrower);
 635
 636 When a member is deleted (DelMember in Members.pm), you should call me first.
 637 This routine deletes/moves lists and entries for the deleted member/borrower.
 638 Lists owned by the borrower are deleted, but entries from the borrower to
 639 other lists are kept.
 640
 641 =cut
 642
 643 sub HandleDelBorrower {
 644     my ($borrower)= @_;
 645     my $query;
 646     my $dbh = C4::Context->dbh;
 647
 648     #Delete all lists and all shares of this borrower
 649     #Consistent with the approach Koha uses on deleting individual lists
 650     #Note that entries in virtualshelfcontents added by this borrower to
 651     #lists of others will be handled by a table constraint: the borrower
 652     #is set to NULL in those entries.
 653     $query="DELETE FROM virtualshelves WHERE owner=?";
 654     $dbh->do($query,undef,($borrower));
 655
 656     #NOTE:
 657     #We could handle the above deletes via a constraint too.
 658     #But a new BZ report 11889 has been opened to discuss another approach.
 659     #Instead of deleting we could also disown lists (based on a pref).
 660     #In that way we could save shared and public lists.
 661     #The current table constraints support that idea now.
 662     #This pref should then govern the results of other routines such as
 663     #DelShelf too.
 664 }
 665
 666 =head2 AddShare
 667
 668      AddShare($shelfnumber, $key);
 669
 670 Adds a share request to the virtualshelves table.
 671 Authorization must have been checked, and a key must be supplied. See script
 672 opac-shareshelf.pl for an example.
 673 This request is not yet confirmed. So it has no borrowernumber, it does have an
 674 expiry date.
 675
 676 =cut
 677
 678 sub AddShare {
 679     my ($shelfnumber, $key)= @_;
 680     return if !$shelfnumber || !$key;
 681
 682     my $dbh = C4::Context->dbh;
 683     my $sql = "INSERT INTO virtualshelfshares (shelfnumber, invitekey, sharedate) VALUES (?, ?, NOW())";
 684     $dbh->do($sql, undef, ($shelfnumber, $key));
 685     return !$dbh->err;
 686 }
 687
 688 =head2 AcceptShare
 689
 690      my $result= AcceptShare($shelfnumber, $key, $borrowernumber);
 691
 692 Checks acceptation of a share request.
 693 Key must be found for this shelf. Invitation must not have expired.
 694 Returns true when accepted, false otherwise.
 695
 696 =cut
 697
 698 sub AcceptShare {
 699     my ($shelfnumber, $key, $borrowernumber)= @_;
 700     return if !$shelfnumber || !$key || !$borrowernumber;
 701
 702     my $sql;
 703     my $dbh = C4::Context->dbh;
 704     $sql="
 705 UPDATE virtualshelfshares
 706 SET invitekey=NULL, sharedate=NOW(), borrowernumber=?
 707 WHERE shelfnumber=? AND invitekey=? AND (sharedate + INTERVAL ? DAY) >NOW()
 708     ";
 709     my $i= $dbh->do($sql, undef, ($borrowernumber, $shelfnumber, $key,  SHARE_INVITATION_EXPIRY_DAYS));
 710     return if !defined($i) || !$i || $i eq '0E0'; #not found
 711     return 1;
 712 }
 713
 714 =head2 IsSharedList
 715
 716      my $bool= IsSharedList( $shelfnumber );
 717
 718 IsSharedList checks if a (private) list has shares.
 719 Note that such a check would not be useful for public lists. A public list has
 720 no shares, but is visible for anyone by nature..
 721 Used to determine the list type in the display of Your lists (all private).
 722 Returns boolean value.
 723
 724 =cut
 725
 726 sub IsSharedList {
 727     my ($shelfnumber) = @_;
 728     my $dbh = C4::Context->dbh;
 729     my $sql="SELECT id FROM virtualshelfshares WHERE shelfnumber=? AND borrowernumber IS NOT NULL";
 730     my $sth = $dbh->prepare($sql);
 731     $sth->execute($shelfnumber);
 732     my ($rv)= $sth->fetchrow_array;
 733     return defined($rv);
 734 }
 735
 736 =head2 RemoveShare
 737
 738      RemoveShare( $user, $shelfnumber );
 739
 740 RemoveShare removes a share for specific shelf and borrower.
 741 Returns true if a record could be deleted.
 742
 743 =cut
 744
 745 sub RemoveShare {
 746     my ($user, $shelfnumber)= @_;
 747     my $dbh = C4::Context->dbh;
 748     my $sql="
 749 DELETE FROM virtualshelfshares
 750 WHERE borrowernumber=? AND shelfnumber=?
 751     ";
 752     my $n= $dbh->do($sql,undef,($user, $shelfnumber));
 753     return if !defined($n) || !$n || $n eq '0E0'; #nothing removed
 754     return 1;
 755 }
 756
 757
 758 sub GetShelfCount {
 759     my ($owner, $category) = @_;
 760     my @params;
 761     # Find out how many shelves total meet the submitted criteria...
 762
 763     my $dbh = C4::Context->dbh;
 764     my $query = "SELECT count(*) FROM virtualshelves vs ";
 765     if($category==1) {
 766         $query.= qq{
 767             LEFT JOIN virtualshelfshares sh ON sh.shelfnumber=vs.shelfnumber
 768             AND sh.borrowernumber=?
 769         WHERE category=1 AND (vs.owner=? OR sh.borrowernumber=?) };
 770         @params= ($owner, $owner, $owner);
 771     }
 772     else {
 773         $query.='WHERE category=2';
 774         @params= ();
 775     }
 776     my $sth = $dbh->prepare($query);
 777     $sth->execute(@params);
 778     my ($total)= $sth->fetchrow;
 779     return $total;
 780 }
 781
 782 # internal subs
 783 sub _CheckShelfName {
 784     my ($name, $cat, $owner, $number)= @_;
 785
 786     my $dbh = C4::Context->dbh;
 787     my @pars;
 788     my $query = qq(
 789         SELECT DISTINCT shelfnumber
 790         FROM   virtualshelves
 791         LEFT JOIN virtualshelfshares sh USING (shelfnumber)
 792         WHERE  shelfname=? AND shelfnumber<>?);
 793     if($cat==1 && defined($owner)) {
 794         $query.= ' AND (sh.borrowernumber=? OR owner=?) AND category=1';
 795         @pars=($name, $number, $owner, $owner);
 796     }
 797     elsif($cat==1 && !defined($owner)) { #owner is null (exceptional)
 798         $query.= ' AND owner IS NULL AND category=1';
 799         @pars=($name, $number);
 800     }
 801     else { #public list
 802         $query.= ' AND category=2';
 803         @pars=($name, $number);
 804     }
 805     my $sth = $dbh->prepare($query);
 806     $sth->execute(@pars);
 807     return $sth->rows>0? 0: 1;
 808 }
 809
 810 1;
 811
 812 __END__
 813
 814 =head1 AUTHOR
 815
 816 Koha Development Team <http://koha-community.org/>
 817
 818 =head1 SEE ALSO
 819
 820 C4::Circulation::Circ2(3)
 821
 822 =cut