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 =head1 NAME
  54
  55 C4::VirtualShelves - Functions for manipulating Koha virtual shelves
  56
  57 =head1 SYNOPSIS
  58
  59   use C4::VirtualShelves;
  60
  61 =head1 DESCRIPTION
  62
  63 This module provides functions for manipulating virtual shelves,
  64 including creating and deleting virtual shelves, and adding and removing
  65 bibs to and from virtual shelves.
  66
  67 =head1 FUNCTIONS
  68
  69 =head2 GetShelves
  70
  71   ($shelflist, $totshelves) = &GetShelves($category, $row_count, $offset, $owner);
  72   ($shelfnumber, $shelfhash) = each %{$shelflist};
  73
  74 Returns the number of shelves specified by C<$row_count> and C<$offset> as well as the total
  75 number of shelves that meet the C<$owner> and C<$category> criteria.  C<$category>,
  76 C<$row_count>, and C<$offset> are required. C<$owner> must be supplied when C<$category> == 1.
  77 When C<$category> is 2, supply undef as argument for C<$owner>.
  78
  79 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.
  80
  81 C<$shelflist>is a reference-to-hash. The keys are the virtualshelves numbers (C<$shelfnumber>, above),
  82 and the values (C<$shelfhash>, above) are themselves references-to-hash, with the following keys:
  83
  84 =over
  85
  86 =item C<$shelfhash-E<gt>{shelfname}>
  87
  88 A string. The name of the shelf.
  89
  90 =item C<$shelfhash-E<gt>{count}>
  91
  92 The number of virtuals on that virtualshelves.
  93
  94 =back
  95
  96 =cut
  97
  98 sub GetShelves {
  99     my ($category, $row_count, $offset, $owner) = @_;
 100     my @params;
 101     my $total = _shelf_count($owner, $category);
 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         @params= ($owner, $owner, $owner, $offset||0, $row_count);
 116     }
 117     else {
 118         $query.= 'WHERE category=2 ';
 119         @params= ($offset||0, $row_count);
 120     }
 121     $query.= qq{
 122         GROUP BY vs.shelfnumber
 123         ORDER BY vs.shelfname
 124         LIMIT ?, ?};
 125
 126     my $sth2 = $dbh->prepare($query);
 127     $sth2->execute(@params);
 128     my %shelflist;
 129     while( my ($shelfnumber, $shelfname, $owner, $surname, $firstname, $category, $sortfield, $count)= $sth2->fetchrow) {
 130         $shelflist{$shelfnumber}->{'shelfname'} = $shelfname;
 131         $shelflist{$shelfnumber}->{'count'}     = $count;
 132         $shelflist{$shelfnumber}->{'single'}    = $count==1;
 133         $shelflist{$shelfnumber}->{'sortfield'} = $sortfield;
 134         $shelflist{$shelfnumber}->{'category'}  = $category;
 135         $shelflist{$shelfnumber}->{'owner'}     = $owner;
 136         $shelflist{$shelfnumber}->{'surname'}   = $surname;
 137         $shelflist{$shelfnumber}->{'firstname'} = $firstname;
 138     }
 139     return ( \%shelflist, $total );
 140 }
 141
 142 =head2 GetAllShelves
 143
 144     $shelflist = GetAllShelves($category, $owner)
 145
 146 This function returns a reference to an array of hashrefs containing all shelves
 147 sorted by the shelf name.
 148
 149 This function is intended to return a dataset reflecting all the shelves for
 150 the submitted parameters.
 151
 152 =cut
 153
 154 sub GetAllShelves {
 155     my ($category,$owner,$adding_allowed) = @_;
 156     my @params;
 157     my $dbh = C4::Context->dbh;
 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     my $dbh = C4::Context->dbh;
 188
 189     my $bquery = 'SELECT vs.shelfnumber, vs.shelfname FROM virtualshelves vs ';
 190     my $limit= ShelvesMax($purpose);
 191
 192     my $qry1= $bquery."WHERE vs.category=2 ";
 193     $qry1.= "AND (allow_add=1 OR owner=?) " if $adding_allowed;
 194     push @params, $owner||0 if $adding_allowed;
 195     $qry1.= "ORDER BY vs.lastmodified DESC LIMIT $limit";
 196
 197     unless($adding_allowed && (!defined($owner) || $owner<=0)) {
 198         #if adding items, user should be known
 199         $pub= $dbh->selectall_arrayref($qry1,{Slice=>{}},@params);
 200     }
 201
 202     if($owner) {
 203         my $qry2= $bquery. qq{
 204             LEFT JOIN virtualshelfshares sh ON sh.shelfnumber=vs.shelfnumber AND sh.borrowernumber=?
 205             WHERE vs.category=1 AND (vs.owner=? OR sh.borrowernumber=?) };
 206         @params=($owner,$owner,$owner);
 207         $qry2.= "AND (allow_add=1 OR owner=?) " if $adding_allowed;
 208         push @params, $owner if $adding_allowed;
 209         $qry2.= "ORDER BY vs.lastmodified DESC ";
 210         $qry2.= "LIMIT $limit";
 211         $bar= $dbh->selectall_arrayref($qry2,{Slice=>{}},@params);
 212     }
 213
 214     return ( { bartotal => $bar? scalar @$bar: 0, pubtotal => $pub? scalar @$pub: 0}, $pub, $bar);
 215 }
 216
 217 =head2 GetShelf
 218
 219   (shelfnumber,shelfname,owner,category,sortfield,allow_add,allow_delete_own,allow_delete_other) = &GetShelf($shelfnumber);
 220
 221 Returns the above-mentioned fields for passed virtual shelf number.
 222
 223 =cut
 224
 225 sub GetShelf {
 226     my ($shelfnumber) = @_;
 227     my $dbh = C4::Context->dbh;
 228     my $query = qq(
 229         SELECT shelfnumber, shelfname, owner, category, sortfield,
 230             allow_add, allow_delete_own, allow_delete_other
 231         FROM   virtualshelves
 232         WHERE  shelfnumber=?
 233     );
 234     my $sth = $dbh->prepare($query);
 235     $sth->execute($shelfnumber);
 236     return $sth->fetchrow;
 237 }
 238
 239 =head2 GetShelfContents
 240
 241   $biblist = &GetShelfContents($shelfnumber);
 242
 243 Looks up information about the contents of virtual virtualshelves number
 244 C<$shelfnumber>.  Sorted by a field in the biblio table.  copyrightdate
 245 gives a desc sort.
 246
 247 Returns a reference-to-array, whose elements are references-to-hash,
 248 as returned by C<C4::Biblio::GetBiblioFromItemNumber>.
 249
 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.
 254
 255 =cut
 256
 257 sub GetShelfContents {
 258     my ($shelfnumber, $row_count, $offset, $sortfield, $sort_direction ) = @_;
 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;
 263     if(!$sortfield) {
 264         my $sth2 = $dbh->prepare('SELECT sortfield FROM virtualshelves WHERE shelfnumber=?');
 265         $sth2->execute($shelfnumber);
 266         ($sortfield) = $sth2->fetchrow_array;
 267     }
 268     my $query =
 269        " SELECT DISTINCT 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          JOIN biblio      ON      vc.biblionumber =      biblio.biblionumber
 273          LEFT JOIN biblioitems ON  biblio.biblionumber = biblioitems.biblionumber
 274          LEFT JOIN items ON items.biblionumber=vc.biblionumber
 275          LEFT JOIN itemtypes   ON biblioitems.itemtype = itemtypes.itemtype
 276          WHERE  vc.shelfnumber=? ";
 277     my @params = ($shelfnumber);
 278     if($sortfield) {
 279         $query .= " ORDER BY " . $dbh->quote_identifier( $sortfield );
 280         $query .= " DESC " if ( $sort_direction eq 'desc' );
 281     }
 282     if($row_count){
 283        $query .= " LIMIT ?, ? ";
 284        push (@params, ($offset ? $offset : 0));
 285        push (@params, $row_count);
 286     }
 287     my $sth3 = $dbh->prepare($query);
 288     $sth3->execute(@params);
 289     return ($sth3->fetchall_arrayref({}), $total);
 290     # Like the perldoc says,
 291     # returns reference-to-array, where each element is reference-to-hash of the row:
 292     #   like [ $sth->fetchrow_hashref(), $sth->fetchrow_hashref() ... ]
 293     # Suitable for use in TMPL_LOOP.
 294     # See http://search.cpan.org/~timb/DBI-1.601/DBI.pm#fetchall_arrayref
 295     # or newer, for your version of DBI.
 296 }
 297
 298 =head2 AddShelf
 299
 300   $shelfnumber = &AddShelf($hashref, $owner);
 301
 302 Creates a new virtual shelf. Params passed in a hash like ModShelf.
 303
 304 Returns a code to know what's happen.
 305     * -1 : if this virtualshelves already exists.
 306     * $shelfnumber : if success.
 307
 308 =cut
 309
 310 sub AddShelf {
 311     my ($hashref, $owner)= @_;
 312     my $dbh = C4::Context->dbh;
 313
 314     #initialize missing hash values to silence warnings
 315     foreach('shelfname','category', 'sortfield', 'allow_add', 'allow_delete_own', 'allow_delete_other' ) {
 316         $hashref->{$_}= undef unless exists $hashref->{$_};
 317     }
 318
 319     return -1 unless _CheckShelfName($hashref->{shelfname}, $hashref->{category}, $owner, 0);
 320
 321     my $query = qq(INSERT INTO virtualshelves
 322         (shelfname,owner,category,sortfield,allow_add,allow_delete_own,allow_delete_other)
 323         VALUES (?,?,?,?,?,?,?));
 324
 325     my $sth = $dbh->prepare($query);
 326     $sth->execute(
 327         $hashref->{shelfname},
 328         $owner,
 329         $hashref->{category},
 330         $hashref->{sortfield},
 331         $hashref->{allow_add}//0,
 332         $hashref->{allow_delete_own}//1,
 333         $hashref->{allow_delete_other}//0 );
 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 Note that add/delete here refers to adding/deleting entries from the list. Deleting the list itself falls under manage.
 436 new_public and new_private refers to creating a new public or private list.
 437 The distinction between deleting your own entries from the list or entries from
 438 others is made in DelFromShelf.
 439
 440 Returns 1 if the user can do the $action in the $shelfnumber shelf.
 441 Returns 0 otherwise.
 442
 443 =cut
 444
 445 sub ShelfPossibleAction {
 446     my ( $user, $shelfnumber, $action ) = @_;
 447     $action= 'view' unless $action;
 448     $user=0 unless $user;
 449
 450     if($action =~ /^new/) { #no shelfnumber needed
 451         if($action eq 'new_private') {
 452             return $user>0;
 453         }
 454         elsif($action eq 'new_public') {
 455             return $user>0 && C4::Context->preference('OpacAllowPublicListCreation');
 456         }
 457         return 0;
 458     }
 459
 460     return 0 unless defined($shelfnumber);
 461
 462     my $dbh = C4::Context->dbh;
 463     my $query = qq/
 464         SELECT COALESCE(owner,0) AS owner, category, allow_add, allow_delete_own, allow_delete_other, COALESCE(sh.borrowernumber,0) AS borrowernumber
 465         FROM virtualshelves vs
 466         LEFT JOIN virtualshelfshares sh ON sh.shelfnumber=vs.shelfnumber
 467         AND sh.borrowernumber=?
 468         WHERE vs.shelfnumber=?
 469     /;
 470     my $sth = $dbh->prepare($query);
 471     $sth->execute($user, $shelfnumber);
 472     my $shelf= $sth->fetchrow_hashref;
 473
 474     return 0 unless $shelf && ($shelf->{category}==2 || $shelf->{owner}==$user || ($user && $shelf->{borrowernumber}==$user));
 475     if($action eq 'view') {
 476         #already handled in the above condition
 477         return 1;
 478     }
 479     elsif($action eq 'add') {
 480         return 0 if $user<=0; #should be logged in
 481         return 1 if $shelf->{allow_add}==1 || $shelf->{owner}==$user;
 482         #owner may always add
 483     }
 484     elsif($action eq 'delete') {
 485         #this answer is just diplomatic: it says that you may be able to delete
 486         #some items from that shelf
 487         #it does not answer the question about a specific biblio
 488         #DelFromShelf checks the situation per biblio
 489         return 1 if $user>0 && ($shelf->{allow_delete_own}==1 || $shelf->{allow_delete_other}==1);
 490     }
 491     elsif($action eq 'manage') {
 492         return 1 if $user && $shelf->{owner}==$user;
 493     }
 494     return 0;
 495 }
 496
 497 =head2 DelFromShelf
 498
 499     $result= &DelFromShelf( $bibref, $shelfnumber, $user);
 500
 501 Removes biblionumbers in passed arrayref from shelf C<$shelfnumber>.
 502 If the bib wasn't on that virtualshelves to begin with, nothing happens.
 503
 504 Returns 0 if no items have been deleted.
 505
 506 =cut
 507
 508 sub DelFromShelf {
 509     my ($bibref, $shelfnumber, $user) = @_;
 510     my $dbh = C4::Context->dbh;
 511     my $query = qq(SELECT allow_delete_own, allow_delete_other FROM virtualshelves WHERE shelfnumber=?);
 512     my $sth= $dbh->prepare($query);
 513     $sth->execute($shelfnumber);
 514     my ($del_own, $del_oth)= $sth->fetchrow;
 515     my $r; my $t=0;
 516
 517     if($del_own) {
 518         $query = qq(DELETE FROM virtualshelfcontents
 519             WHERE shelfnumber=? AND biblionumber=? AND borrowernumber=?);
 520         $sth= $dbh->prepare($query);
 521         foreach my $biblionumber (@$bibref) {
 522             $sth->execute($shelfnumber, $biblionumber, $user);
 523             $r= $sth->rows; #Expect -1, 0 or 1 (-1 means Don't know; count as 1)
 524             $t+= ($r==-1)? 1: $r;
 525         }
 526     }
 527     if($del_oth) {
 528         #includes a check if borrowernumber is null (deleted patron)
 529         $query = qq/DELETE FROM virtualshelfcontents
 530             WHERE shelfnumber=? AND biblionumber=? AND
 531             (borrowernumber IS NULL OR borrowernumber<>?)/;
 532         $sth= $dbh->prepare($query);
 533         foreach my $biblionumber (@$bibref) {
 534             $sth->execute($shelfnumber, $biblionumber, $user);
 535             $r= $sth->rows;
 536             $t+= ($r==-1)? 1: $r;
 537         }
 538     }
 539     return $t;
 540 }
 541
 542 =head2 DelShelf
 543
 544   $Number = DelShelf($shelfnumber);
 545
 546 This function deletes the shelf number, and all of it's content.
 547 Authorization to do so MUST have been checked before calling, while using
 548 ShelfPossibleAction with manage parameter.
 549
 550 =cut
 551
 552 sub DelShelf {
 553     my ($shelfnumber)= @_;
 554     return unless $shelfnumber && $shelfnumber =~ /^\d+$/;
 555     my $dbh = C4::Context->dbh;
 556     my $sth = $dbh->prepare("DELETE FROM virtualshelves WHERE shelfnumber=?");
 557     return $sth->execute($shelfnumber);
 558 }
 559
 560 =head2 GetBibliosShelves
 561
 562 This finds all the public lists that this bib record is in.
 563
 564 =cut
 565
 566 sub GetBibliosShelves {
 567     my ( $biblionumber )  = @_;
 568     my $dbh = C4::Context->dbh;
 569     my $sth = $dbh->prepare('
 570         SELECT vs.shelfname, vs.shelfnumber
 571         FROM virtualshelves vs
 572         JOIN virtualshelfcontents vc ON (vs.shelfnumber= vc.shelfnumber)
 573         WHERE vs.category=2
 574         AND vc.biblionumber= ?
 575     ');
 576     $sth->execute( $biblionumber );
 577     return $sth->fetchall_arrayref({});
 578 }
 579
 580 =head2 ShelvesMax
 581
 582     $howmany= ShelvesMax($context);
 583
 584 Tells how much shelves are shown in which context.
 585 POPUP refers to addbybiblionumber popup, MGRPAGE is managing page (in opac or
 586 staff), COMBO refers to the Add to-combo of search results. MASTHEAD is the
 587 main Koha toolbar with Lists button.
 588
 589 =cut
 590
 591 sub ShelvesMax {
 592     my $which= shift;
 593     return SHELVES_POPUP_MAX if $which eq 'POPUP';
 594     return SHELVES_MGRPAGE_MAX if $which eq 'MGRPAGE';
 595     return SHELVES_COMBO_MAX if $which eq 'COMBO';
 596     return SHELVES_MASTHEAD_MAX if $which eq 'MASTHEAD';
 597     return SHELVES_MASTHEAD_MAX;
 598 }
 599
 600 =head2 HandleDelBorrower
 601
 602      HandleDelBorrower($borrower);
 603
 604 When a member is deleted (DelMember in Members.pm), you should call me first.
 605 This routine deletes/moves lists and entries for the deleted member/borrower.
 606 You could just delete everything (and lose more than you want), but instead we
 607 now try to save all public/shared stuff and keep others happy.
 608
 609 =cut
 610
 611 sub HandleDelBorrower {
 612     my ($borrower)= @_;
 613     my $query;
 614     my $dbh = C4::Context->dbh;
 615
 616     #Delete shares of this borrower (not lists !)
 617     #Although this would be done later via the FK cascaded delete, we do it now.
 618     #Because it makes the following delete statement on shelves more meaningful.
 619     $query="DELETE FROM virtualshelfshares WHERE borrowernumber=?";
 620     $dbh->do($query,undef,($borrower));
 621
 622     #Delete private lists without owner that now have no shares anymore
 623     $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";
 624     $dbh->do($query);
 625
 626     #Change owner for private lists which have shares
 627     $query="UPDATE virtualshelves LEFT JOIN virtualshelfshares sh USING (shelfnumber) SET owner=NULL where owner=? AND category=1 AND sh.borrowernumber IS NOT NULL";
 628     $dbh->do($query,undef,($borrower));
 629
 630     #Delete unshared private lists
 631     $query="DELETE FROM virtualshelves WHERE owner=? AND category=1";
 632     $dbh->do($query,undef,($borrower));
 633
 634     #Handle public lists owned by borrower
 635     $query="UPDATE virtualshelves SET owner=NULL WHERE owner=? AND category=2";
 636     $dbh->do($query,undef,($borrower));
 637
 638     #Handle entries added by borrower to lists of others
 639     $query="UPDATE virtualshelfcontents SET borrowernumber=NULL WHERE borrowernumber=?";
 640     $dbh->do($query,undef,($borrower));
 641 }
 642
 643 # internal subs
 644
 645 sub _shelf_count {
 646     my ($owner, $category) = @_;
 647     my @params;
 648     # Find out how many shelves total meet the submitted criteria...
 649
 650     my $dbh = C4::Context->dbh;
 651     my $query = "SELECT count(*) FROM virtualshelves vs ";
 652     if($category==1) {
 653         $query.= qq{
 654             LEFT JOIN virtualshelfshares sh ON sh.shelfnumber=vs.shelfnumber
 655             AND sh.borrowernumber=?
 656         WHERE category=1 AND (vs.owner=? OR sh.borrowernumber=?) };
 657         @params= ($owner, $owner, $owner);
 658     }
 659     else {
 660         $query.='WHERE category=2';
 661         @params= ();
 662     }
 663     my $sth = $dbh->prepare($query);
 664     $sth->execute(@params);
 665     my ($total)= $sth->fetchrow;
 666     return $total;
 667 }
 668
 669 sub _biblionumber_sth { #only used in obsolete sub below
 670     my ($shelf) = @_;
 671     my $query = 'select biblionumber from virtualshelfcontents where shelfnumber = ?';
 672     my $dbh = C4::Context->dbh;
 673     my $sth = $dbh->prepare($query)
 674         or die $dbh->errstr;
 675     $sth->execute( $shelf )
 676         or die $sth->errstr;
 677     $sth;
 678 }
 679
 680 sub _CheckShelfName {
 681     my ($name, $cat, $owner, $number)= @_;
 682
 683     my $dbh = C4::Context->dbh;
 684     my @pars;
 685     my $query = qq(
 686         SELECT DISTINCT shelfnumber
 687         FROM   virtualshelves
 688         LEFT JOIN virtualshelfshares sh USING (shelfnumber)
 689         WHERE  shelfname=? AND shelfnumber<>?);
 690     if($cat==1 && defined($owner)) {
 691         $query.= ' AND (sh.borrowernumber=? OR owner=?) AND category=1';
 692         @pars=($name, $number, $owner, $owner);
 693     }
 694     elsif($cat==1 && !defined($owner)) { #owner is null (exceptional)
 695         $query.= ' AND owner IS NULL AND category=1';
 696         @pars=($name, $number);
 697     }
 698     else { #public list
 699         $query.= ' AND category=2';
 700         @pars=($name, $number);
 701     }
 702     my $sth = $dbh->prepare($query);
 703     $sth->execute(@pars);
 704     return $sth->rows>0? 0: 1;
 705 }
 706
 707 1;
 708
 709 __END__
 710
 711 =head1 AUTHOR
 712
 713 Koha Development Team <http://koha-community.org/>
 714
 715 =head1 SEE ALSO
 716
 717 C4::Circulation::Circ2(3)
 718
 719 =cut