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 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
  10 # version.
  11 #
  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.
  15 #
  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.
  19
  20 use strict;
  21 use warnings;
  22
  23 use Carp;
  24 use C4::Context;
  25 use C4::Debug;
  26
  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
  31
  32 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK);
  33
  34 BEGIN {
  35     # set the version for version checking
  36     $VERSION = 3.07.00.049;
  37     require Exporter;
  38     @ISA    = qw(Exporter);
  39     @EXPORT = qw(
  40             &GetShelves &GetShelfContents &GetShelf
  41             &AddToShelf &AddShelf
  42             &ModShelf
  43             &ShelfPossibleAction
  44             &DelFromShelf &DelShelf
  45             &GetBibliosShelves
  46     );
  47         @EXPORT_OK = qw(
  48             &GetAllShelves &ShelvesMax
  49         );
  50 }
  51
  52
  53 my $dbh = C4::Context->dbh;
  54
  55 =head1 NAME
  56
  57 C4::VirtualShelves - Functions for manipulating Koha virtual shelves
  58
  59 =head1 SYNOPSIS
  60
  61   use C4::VirtualShelves;
  62
  63 =head1 DESCRIPTION
  64
  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.
  68
  69 =head1 FUNCTIONS
  70
  71 =head2 GetShelves
  72
  73   ($shelflist, $totshelves) = &GetShelves($category, $row_count, $offset, $owner);
  74   ($shelfnumber, $shelfhash) = each %{$shelflist};
  75
  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>.
  80
  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.
  82
  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:
  85
  86 =over
  87
  88 =item C<$shelfhash-E<gt>{shelfname}>
  89
  90 A string. The name of the shelf.
  91
  92 =item C<$shelfhash-E<gt>{count}>
  93
  94 The number of virtuals on that virtualshelves.
  95
  96 =back
  97
  98 =cut
  99
 100 sub GetShelves {
 101     my ($category, $row_count, $offset, $owner) = @_;
 102     my @params;
 103     my $total = _shelf_count($owner, $category);
 104     my $query = qq{
 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) };
 111     if($category==1) {
 112         $query.= qq{
 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);
 117     }
 118     else {
 119         $query.= 'WHERE category=2 ';
 120         @params= ($offset||0, $row_count);
 121     }
 122     $query.= qq{
 123         GROUP BY vs.shelfnumber
 124         ORDER BY vs.shelfname
 125         LIMIT ?, ?};
 126
 127     my $sth2 = $dbh->prepare($query);
 128     $sth2->execute(@params);
 129     my %shelflist;
 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;
 139     }
 140     return ( \%shelflist, $total );
 141 }
 142
 143 =head2 GetAllShelves
 144
 145     $shelflist = GetAllShelves($category, $owner)
 146
 147 This function returns a reference to an array of hashrefs containing all shelves
 148 sorted by the shelf name.
 149
 150 This function is intended to return a dataset reflecting all the shelves for
 151 the submitted parameters.
 152
 153 =cut
 154
 155 sub GetAllShelves {
 156     my ($category,$owner,$adding_allowed) = @_;
 157     my @params;
 158     my $query = 'SELECT vs.* FROM virtualshelves vs ';
 159     if($category==1) {
 160         $query.= qq{
 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);
 165     }
 166     else {
 167     $query.='WHERE category=2 ';
 168         @params = ();
 169     }
 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({});
 176 }
 177
 178 =head2 GetSomeShelfNames
 179
 180 Returns shelf names and numbers for Add to combo of search results and Lists button of OPAC header.
 181
 182 =cut
 183
 184 sub GetSomeShelfNames {
 185     my ($owner, $purpose, $adding_allowed)= @_;
 186     my ($bar, $pub, @params);
 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 $query = qq(
 227         SELECT shelfnumber, shelfname, owner, category, sortfield,
 228             allow_add, allow_delete_own, allow_delete_other
 229         FROM   virtualshelves
 230         WHERE  shelfnumber=?
 231     );
 232     my $sth = $dbh->prepare($query);
 233     $sth->execute($shelfnumber);
 234     return $sth->fetchrow;
 235 }
 236
 237 =head2 GetShelfContents
 238
 239   $biblist = &GetShelfContents($shelfnumber);
 240
 241 Looks up information about the contents of virtual virtualshelves number
 242 C<$shelfnumber>.  Sorted by a field in the biblio table.  copyrightdate
 243 gives a desc sort.
 244
 245 Returns a reference-to-array, whose elements are references-to-hash,
 246 as returned by C<C4::Biblio::GetBiblioFromItemNumber>.
 247
 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.
 252
 253 =cut
 254
 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;
 261     if(!$sortfield) {
 262         my $sth2 = $dbh->prepare('SELECT sortfield FROM virtualshelves WHERE shelfnumber=?');
 263         $sth2->execute($shelfnumber);
 264         ($sortfield) = $sth2->fetchrow_array;
 265     }
 266     my $query =
 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);
 275     if($sortfield) {
 276         $query .= " ORDER BY " . $sortfield;
 277         $query .= " DESC " if ($sortfield eq 'copyrightdate');
 278     }
 279     if($row_count){
 280        $query .= " LIMIT ?, ? ";
 281        push (@params, ($offset ? $offset : 0));
 282        push (@params, $row_count);
 283     }
 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.
 293 }
 294
 295 =head2 AddShelf
 296
 297   $shelfnumber = &AddShelf($hashref, $owner);
 298
 299 Creates a new virtual shelf. Params passed in a hash like ModShelf.
 300
 301 Returns a code to know what's happen.
 302     * -1 : if this virtualshelves already exists.
 303     * $shelfnumber : if success.
 304
 305 =cut
 306
 307 sub AddShelf {
 308     my ($hashref, $owner)= @_;
 309
 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->{$_};
 313     }
 314
 315     return -1 unless _CheckShelfName($hashref->{shelfname}, $hashref->{category}, $owner, 0);
 316
 317     my $query = qq(INSERT INTO virtualshelves
 318         (shelfname,owner,category,sortfield,allow_add,allow_delete_own,allow_delete_other)
 319         VALUES (?,?,?,?,?,?,?));
 320
 321     my $sth = $dbh->prepare($query);
 322     $sth->execute(
 323         $hashref->{shelfname},
 324         $owner,
 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'};
 331     return $shelfnumber;
 332 }
 333
 334 =head2 AddToShelf
 335
 336   &AddToShelf($biblionumber, $shelfnumber, $borrower);
 337
 338 Adds bib number C<$biblionumber> to virtual virtualshelves number
 339 C<$shelfnumber>, unless that bib is already on that shelf.
 340
 341 =cut
 342
 343 sub AddToShelf {
 344     my ($biblionumber, $shelfnumber, $borrowernumber) = @_;
 345     return unless $biblionumber;
 346     my $query = qq(
 347         SELECT *
 348         FROM   virtualshelfcontents
 349         WHERE  shelfnumber=? AND biblionumber=?
 350     );
 351     my $sth = $dbh->prepare($query);
 352
 353     $sth->execute( $shelfnumber, $biblionumber );
 354     ($sth->rows) and return; # already on shelf
 355     $query = qq(
 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 );
 366 }
 367
 368 =head2 ModShelf
 369
 370 my $result= ModShelf($shelfnumber, $hashref)
 371
 372 Where $hashref->{column} = param
 373
 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.
 378
 379 Returns 1 if the action seemed to be successful.
 380
 381 =cut
 382
 383 sub ModShelf {
 384     my ($shelfnumber,$hashref) = @_;
 385
 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?
 391
 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->{$_};
 395     }
 396
 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},
 402             $oldrecord->{owner},
 403             $shelfnumber )) {
 404                 return 0; #name check failed
 405         }
 406     }
 407
 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);
 411     $sth->execute(
 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},
 418         $shelfnumber );
 419     return $@? 0: 1;
 420 }
 421
 422 =head2 ShelfPossibleAction
 423
 424 ShelfPossibleAction($loggedinuser, $shelfnumber, $action);
 425
 426 C<$loggedinuser,$shelfnumber,$action>
 427
 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.
 433
 434 Returns 1 if the user can do the $action in the $shelfnumber shelf.
 435 Returns 0 otherwise.
 436
 437 =cut
 438
 439 sub ShelfPossibleAction {
 440     my ( $user, $shelfnumber, $action ) = @_;
 441     $action= 'view' unless $action;
 442     $user=0 unless $user;
 443
 444     if($action =~ /^new/) { #no shelfnumber needed
 445         if($action eq 'new_private') {
 446             return $user>0;
 447         }
 448         elsif($action eq 'new_public') {
 449             return $user>0 && C4::Context->preference('OpacAllowPublicListCreation');
 450         }
 451         return 0;
 452     }
 453
 454     return 0 unless defined($shelfnumber);
 455
 456     my $query = qq/
 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=?
 462     /;
 463     my $sth = $dbh->prepare($query);
 464     $sth->execute($user, $shelfnumber);
 465     my $shelf= $sth->fetchrow_hashref;
 466
 467     return 0 unless $shelf && ($shelf->{category}==2 || $shelf->{owner}==$user || ($user && $shelf->{borrowernumber}==$user));
 468     if($action eq 'view') {
 469         #already handled in the above condition
 470         return 1;
 471     }
 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
 476     }
 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);
 483     }
 484     elsif($action eq 'manage') {
 485         return 1 if $user && $shelf->{owner}==$user;
 486     }
 487     return 0;
 488 }
 489
 490 =head2 DelFromShelf
 491
 492     $result= &DelFromShelf( $bibref, $shelfnumber, $user);
 493
 494 Removes biblionumbers in passed arrayref from shelf C<$shelfnumber>.
 495 If the bib wasn't on that virtualshelves to begin with, nothing happens.
 496
 497 Returns 0 if no items have been deleted.
 498
 499 =cut
 500
 501 sub DelFromShelf {
 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;
 507     my $r; my $t=0;
 508
 509     if($del_own) {
 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;
 517         }
 518     }
 519     if($del_oth) {
 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);
 527             $r= $sth->rows;
 528             $t+= ($r==-1)? 1: $r;
 529         }
 530     }
 531     return $t;
 532 }
 533
 534 =head2 DelShelf
 535
 536   $Number = DelShelf($shelfnumber);
 537
 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.
 541
 542 =cut
 543
 544 sub DelShelf {
 545     my ($shelfnumber)= @_;
 546     return unless $shelfnumber && $shelfnumber =~ /^\d+$/;
 547     my $sth = $dbh->prepare("DELETE FROM virtualshelves WHERE shelfnumber=?");
 548     return $sth->execute($shelfnumber);
 549 }
 550
 551 =head2 GetBibliosShelves
 552
 553 This finds all the public lists that this bib record is in.
 554
 555 =cut
 556
 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)
 564         WHERE vs.category=2
 565         AND vc.biblionumber= ?
 566     ');
 567     $sth->execute( $biblionumber );
 568     return $sth->fetchall_arrayref({});
 569 }
 570
 571 =head2 ShelvesMax
 572
 573     $howmany= ShelvesMax($context);
 574
 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.
 579
 580 =cut
 581
 582 sub ShelvesMax {
 583     my $which= shift;
 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;
 589 }
 590
 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
 596     my ($borrower)= @_;
 597     my $query;
 598     my $dbh = C4::Context->dbh;
 599
 600     #Delete shares of this borrower (not lists !)
 601     $query="DELETE FROM virtualshelfshares WHERE borrowernumber=?";
 602     $dbh->do($query,undef,($borrower));
 603
 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";
 606     $dbh->do($query);
 607
 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));
 611
 612     #Delete unshared private lists
 613     $query="DELETE FROM virtualshelves WHERE owner=? AND category=1";
 614     $dbh->do($query,undef,($borrower));
 615
 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));
 619
 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));
 623 }
 624
 625 # internal subs
 626
 627 sub _shelf_count {
 628     my ($owner, $category) = @_;
 629     my @params;
 630     # Find out how many shelves total meet the submitted criteria...
 631
 632     my $query = "SELECT count(*) FROM virtualshelves vs ";
 633     if($category==1) {
 634         $query.= qq{
 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);
 639     }
 640     else {
 641         $query.='WHERE category=2';
 642         @params= ();
 643     }
 644     my $sth = $dbh->prepare($query);
 645     $sth->execute(@params);
 646     my ($total)= $sth->fetchrow;
 647     return $total;
 648 }
 649
 650 sub _biblionumber_sth { #only used in obsolete sub below
 651     my ($shelf) = @_;
 652     my $query = 'select biblionumber from virtualshelfcontents where shelfnumber = ?';
 653     my $dbh = C4::Context->dbh;
 654     my $sth = $dbh->prepare($query)
 655         or die $dbh->errstr;
 656     $sth->execute( $shelf )
 657         or die $sth->errstr;
 658     $sth;
 659 }
 660
 661 sub _CheckShelfName {
 662     my ($name, $cat, $owner, $number)= @_;
 663
 664     my $query = qq(
 665         SELECT DISTINCT shelfnumber
 666         FROM   virtualshelves
 667         LEFT JOIN virtualshelfshares sh USING (shelfnumber)
 668         WHERE  shelfname=? AND shelfnumber<>?);
 669     if($cat==1) {
 670         $query.= ' AND (sh.borrowernumber=? OR owner=?) AND category=1';
 671     }
 672     else {
 673         $query.= ' AND category=2';
 674     }
 675     my $sth = $dbh->prepare($query);
 676     $sth->execute($cat==1? ($name, $number, $owner, $owner): ($name, $number));
 677     return $sth->rows>0? 0: 1;
 678 }
 679
 680 1;
 681
 682 __END__
 683
 684 =head1 AUTHOR
 685
 686 Koha Development Team <http://koha-community.org/>
 687
 688 =head1 SEE ALSO
 689
 690 C4::Circulation::Circ2(3)
 691
 692 =cut