C4/Suggestions.pm

   1 package C4::Suggestions;
   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 with
  17 # Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
  18 # Suite 330, Boston, MA  02111-1307 USA
  19
  20
  21 use strict;
  22 use CGI;
  23 use Mail::Sendmail;
  24
  25 use C4::Context;
  26 use C4::Output;
  27 use C4::Dates qw(format_date);
  28 use vars qw($VERSION @ISA @EXPORT);
  29
  30 BEGIN {
  31         # set the version for version checking
  32         $VERSION = 3.01;
  33         require Exporter;
  34         @ISA = qw(Exporter);
  35         @EXPORT = qw(
  36                 &NewSuggestion
  37                 &SearchSuggestion
  38                 &GetSuggestion
  39                 &GetSuggestionByStatus
  40                 &DelSuggestion
  41                 &CountSuggestion
  42                 &ModStatus
  43                 &ConnectSuggestionAndBiblio
  44                 &GetSuggestionFromBiblionumber
  45         );
  46 }
  47
  48 =head1 NAME
  49
  50 C4::Suggestions - Some useful functions for dealings with suggestions.
  51
  52 =head1 SYNOPSIS
  53
  54 use C4::Suggestions;
  55
  56 =head1 DESCRIPTION
  57
  58 The functions in this module deal with the suggestions in OPAC and in librarian interface
  59
  60 A suggestion is done in the OPAC. It has the status "ASKED"
  61
  62 When a librarian manages the suggestion, he can set the status to "REJECTED" or "ACCEPTED".
  63
  64 When the book is ordered, the suggestion status becomes "ORDERED"
  65
  66 When a book is ordered and arrived in the library, the status becomes "AVAILABLE"
  67
  68 All suggestions of a borrower can be seen by the borrower itself.
  69 Suggestions done by other borrowers can be seen when not "AVAILABLE"
  70
  71 =head1 FUNCTIONS
  72
  73 =head2 SearchSuggestion
  74
  75 (\@array) = &SearchSuggestion($user,$author,$title,$publishercode,$status,$suggestedbyme,$branchcode)
  76
  77 searches for a suggestion
  78
  79 return :
  80 C<\@array> : the suggestions found. Array of hash.
  81 Note the status is stored twice :
  82 * in the status field
  83 * as parameter ( for example ASKED => 1, or REJECTED => 1) . This is for template & translation purposes.
  84
  85 =cut
  86
  87 sub SearchSuggestion  {
  88     my ($user,$author,$title,$publishercode,$status,$suggestedbyme,$branchcode)=@_;
  89     my $dbh = C4::Context->dbh;
  90     my $query = "
  91     SELECT suggestions.*,
  92         U1.surname   AS surnamesuggestedby,
  93         U1.firstname AS firstnamesuggestedby,
  94         U1.borrowernumber AS borrnumsuggestedby,
  95         U2.surname   AS surnamemanagedby,
  96         U2.firstname AS firstnamemanagedby,
  97         U2.borrowernumber AS borrnummanagedby
  98     FROM suggestions
  99     LEFT JOIN borrowers AS U1 ON suggestedby=U1.borrowernumber
 100     LEFT JOIN borrowers AS U2 ON managedby=U2.borrowernumber
 101     WHERE 1=1 ";
 102
 103     my @sql_params;
 104     if ($author) {
 105        push @sql_params,"%".$author."%";
 106        $query .= " and author like ?";
 107     }
 108     if ($title) {
 109         push @sql_params,"%".$title."%";
 110         $query .= " and suggestions.title like ?";
 111     }
 112     if ($publishercode) {
 113         push @sql_params,"%".$publishercode."%";
 114         $query .= " and publishercode like ?";
 115     }
 116     if (C4::Context->preference("IndependantBranches") || $branchcode) {
 117         my $userenv = C4::Context->userenv;
 118         if ($userenv) {
 119             unless ($userenv->{flags} == 1){
 120                 push @sql_params,$userenv->{branch};
 121                 $query .= " and (U1.branchcode = ? or U1.branchcode ='')";
 122             }
 123         }
 124         if ($branchcode) {
 125             push @sql_params,$branchcode;
 126             $query .= " and (U1.branchcode = ? or U1.branchcode ='')";
 127         }
 128     }
 129     if ($status) {
 130         push @sql_params,$status;
 131         $query .= " and status=?";
 132     }
 133     if ($suggestedbyme) {
 134         unless ($suggestedbyme eq -1) {
 135             push @sql_params,$user;
 136             $query .= " and suggestedby=?";
 137         }
 138     } else {
 139         $query .= " and managedby is NULL";
 140     }
 141     my $sth=$dbh->prepare($query);
 142     $sth->execute(@sql_params);
 143     my @results;
 144     my $even=1; # the even variable is used to set even / odd lines, for highlighting
 145     while (my $data=$sth->fetchrow_hashref){
 146         $data->{$data->{STATUS}} = 1;
 147         if ($even) {
 148             $even=0;
 149             $data->{even}=1;
 150         } else {
 151             $even=1;
 152         }
 153 #         $data->{date} = format_date($data->{date});
 154         push(@results,$data);
 155     }
 156     return (\@results);
 157 }
 158
 159 =head2 GetSuggestion
 160
 161 \%sth = &GetSuggestion($suggestionid)
 162
 163 this function get the detail of the suggestion $suggestionid (input arg)
 164
 165 return :
 166     the result of the SQL query as a hash : $sth->fetchrow_hashref.
 167
 168 =cut
 169
 170 sub GetSuggestion {
 171     my ($suggestionid) = @_;
 172     my $dbh = C4::Context->dbh;
 173     my $query = "
 174         SELECT *
 175         FROM   suggestions
 176         WHERE  suggestionid=?
 177     ";
 178     my $sth = $dbh->prepare($query);
 179     $sth->execute($suggestionid);
 180     return($sth->fetchrow_hashref);
 181 }
 182
 183 =head2 GetSuggestionFromBiblionumber
 184
 185 $suggestionid = &GetSuggestionFromBiblionumber($dbh,$biblionumber)
 186
 187 Get a suggestion from it's biblionumber.
 188
 189 return :
 190 the id of the suggestion which is related to the biblionumber given on input args.
 191
 192 =cut
 193
 194 sub GetSuggestionFromBiblionumber {
 195     my ($dbh,$biblionumber) = @_;
 196     my $query = qq|
 197         SELECT suggestionid
 198         FROM   suggestions
 199         WHERE  biblionumber=?
 200     |;
 201     my $sth = $dbh->prepare($query);
 202     $sth->execute($biblionumber);
 203     my ($suggestionid) = $sth->fetchrow;
 204     return $suggestionid;
 205 }
 206
 207 =head2 GetSuggestionByStatus
 208
 209 $suggestions = &GetSuggestionByStatus($status,[$branchcode])
 210
 211 Get a suggestion from it's status
 212
 213 return :
 214 all the suggestion with C<$status>
 215
 216 =cut
 217
 218 sub GetSuggestionByStatus {
 219     my $status = shift;
 220     my $branchcode = shift;
 221     my $dbh = C4::Context->dbh;
 222     my @sql_params=($status);
 223     my $query = qq(SELECT suggestions.*,
 224                         U1.surname   AS surnamesuggestedby,
 225                         U1.firstname AS firstnamesuggestedby,
 226                                                 U1.borrowernumber AS borrnumsuggestedby,
 227                         U2.surname   AS surnamemanagedby,
 228                         U2.firstname AS firstnamemanagedby,
 229                                                 U2.borrowernumber AS borrnummanagedby
 230                         FROM suggestions
 231                         LEFT JOIN borrowers AS U1 ON suggestedby=U1.borrowernumber
 232                         LEFT JOIN borrowers AS U2 ON managedby=U2.borrowernumber
 233                         WHERE status = ?);
 234     if (C4::Context->preference("IndependantBranches") || $branchcode) {
 235         my $userenv = C4::Context->userenv;
 236         if ($userenv) {
 237             unless ($userenv->{flags} == 1){
 238                 push @sql_params,$userenv->{branch};
 239                 $query .= " and (U1.branchcode = ? or U1.branchcode ='')";
 240             }
 241         }
 242         if ($branchcode) {
 243             push @sql_params,$branchcode;
 244             $query .= " and (U1.branchcode = ? or U1.branchcode ='')";
 245         }
 246     }
 247
 248     my $sth = $dbh->prepare($query);
 249     $sth->execute(@sql_params);
 250
 251     my $results;
 252     $results=  $sth->fetchall_arrayref({});
 253 #     map{$_->{date} = format_date($_->{date})} @$results;
 254     return $results;
 255 }
 256
 257 =head2 CountSuggestion
 258
 259 &CountSuggestion($status)
 260
 261 Count the number of suggestions with the status given on input argument.
 262 the arg status can be :
 263
 264 =over 2
 265
 266 =item * ASKED : asked by the user, not dealed by the librarian
 267
 268 =item * ACCEPTED : accepted by the librarian, but not yet ordered
 269
 270 =item * REJECTED : rejected by the librarian (definitive status)
 271
 272 =item * ORDERED : ordered by the librarian (acquisition module)
 273
 274 =back
 275
 276 return :
 277 the number of suggestion with this status.
 278
 279 =cut
 280
 281 sub CountSuggestion {
 282     my ($status) = @_;
 283     my $dbh = C4::Context->dbh;
 284     my $sth;
 285     if (C4::Context->preference("IndependantBranches")){
 286         my $userenv = C4::Context->userenv;
 287         if ($userenv->{flags} == 1){
 288             my $query = qq |
 289                 SELECT count(*)
 290                 FROM   suggestions
 291                 WHERE  status=?
 292             |;
 293             $sth = $dbh->prepare($query);
 294             $sth->execute($status);
 295         }
 296         else {
 297             my $query = qq |
 298                 SELECT count(*)
 299                 FROM suggestions LEFT JOIN borrowers ON borrowers.borrowernumber=suggestions.suggestedby
 300                 WHERE status=?
 301                 AND (borrowers.branchcode='' OR borrowers.branchcode =?)
 302             |;
 303             $sth = $dbh->prepare($query);
 304             $sth->execute($status,$userenv->{branch});
 305         }
 306     }
 307     else {
 308         my $query = qq |
 309             SELECT count(*)
 310             FROM suggestions
 311             WHERE status=?
 312         |;
 313          $sth = $dbh->prepare($query);
 314         $sth->execute($status);
 315     }
 316     my ($result) = $sth->fetchrow;
 317     return $result;
 318 }
 319
 320 =head2 NewSuggestion
 321
 322
 323 &NewSuggestion($borrowernumber,$title,$author,$publishercode,$note,$copyrightdate,$volumedesc,$publicationyear,$place,$isbn,$biblionumber)
 324
 325 Insert a new suggestion on database with value given on input arg.
 326
 327 =cut
 328
 329 sub NewSuggestion {
 330     my ($borrowernumber,$title,$author,$publishercode,$note,$copyrightdate,$volumedesc,$publicationyear,$place,$isbn,$biblionumber,$reason) = @_;
 331     my $dbh = C4::Context->dbh;
 332     my $query = qq |
 333         INSERT INTO suggestions
 334             (status,suggestedby,title,author,publishercode,note,copyrightdate,
 335             volumedesc,publicationyear,place,isbn,biblionumber,reason)
 336         VALUES ('ASKED',?,?,?,?,?,?,?,?,?,?,?,?)
 337     |;
 338     my $sth = $dbh->prepare($query);
 339     $sth->execute($borrowernumber,$title,$author,$publishercode,$note,$copyrightdate,$volumedesc,$publicationyear,$place,$isbn,$biblionumber,$reason);
 340 }
 341
 342 =head2 ModStatus
 343
 344 &ModStatus($suggestionid,$status,$managedby,$biblionumber)
 345
 346 Modify the status (status can be 'ASKED', 'ACCEPTED', 'REJECTED', 'ORDERED')
 347 and send a mail to notify the user that did the suggestion.
 348
 349 Note that there is no function to modify a suggestion : only the status can be modified, thus the name of the function.
 350
 351 =cut
 352
 353 sub ModStatus {
 354     my ($suggestionid,$status,$managedby,$biblionumber,$reason) = @_;
 355     my $dbh = C4::Context->dbh;
 356     my $sth;
 357     if ($managedby>0) {
 358         if ($biblionumber) {
 359         my $query = qq|
 360             UPDATE suggestions
 361             SET    status=?,managedby=?,biblionumber=?,reason=?
 362             WHERE  suggestionid=?
 363         |;
 364         $sth = $dbh->prepare($query);
 365         $sth->execute($status,$managedby,$biblionumber,$reason,$suggestionid);
 366         } else {
 367             my $query = qq|
 368                 UPDATE suggestions
 369                 SET    status=?,managedby=?,reason=?
 370                 WHERE  suggestionid=?
 371             |;
 372             $sth = $dbh->prepare($query);
 373             $sth->execute($status,$managedby,$reason,$suggestionid);
 374         }
 375    } else {
 376         if ($biblionumber) {
 377             my $query = qq|
 378                 UPDATE suggestions
 379                 SET    status=?,biblionumber=?,reason=?
 380                 WHERE  suggestionid=?
 381             |;
 382             $sth = $dbh->prepare($query);
 383             $sth->execute($status,$biblionumber,$reason,$suggestionid);
 384         }
 385         else {
 386             my $query = qq|
 387                 UPDATE suggestions
 388                 SET    status=?,reason=?
 389                 WHERE  suggestionid=?
 390             |;
 391             $sth = $dbh->prepare($query);
 392             $sth->execute($status,$reason,$suggestionid);
 393         }
 394     }
 395     # check mail sending.
 396     my $queryMail = "
 397         SELECT suggestions.*,
 398             boby.surname AS bysurname,
 399             boby.firstname AS byfirstname,
 400             boby.email AS byemail,
 401             lib.surname AS libsurname,
 402             lib.firstname AS libfirstname,
 403             lib.email AS libemail
 404         FROM suggestions
 405             LEFT JOIN borrowers AS boby ON boby.borrowernumber=suggestedby
 406             LEFT JOIN borrowers AS lib ON lib.borrowernumber=managedby
 407         WHERE suggestionid=?
 408     ";
 409     $sth = $dbh->prepare($queryMail);
 410     $sth->execute($suggestionid);
 411     my $emailinfo = $sth->fetchrow_hashref;
 412     my $template = gettemplate("suggestion/mail_suggestion_$status.tmpl", "intranet", CGI->new());
 413
 414     $template->param(
 415         byemail => $emailinfo->{byemail},
 416         libemail => $emailinfo->{libemail},
 417         status => $emailinfo->{status},
 418         title => $emailinfo->{title},
 419         author =>$emailinfo->{author},
 420         libsurname => $emailinfo->{libsurname},
 421         libfirstname => $emailinfo->{libfirstname},
 422         byfirstname => $emailinfo->{byfirstname},
 423         bysurname => $emailinfo->{bysurname},
 424         reason => $emailinfo->{reason}
 425     );
 426     my %mail = (
 427         To => $emailinfo->{byemail},
 428         From => $emailinfo->{libemail},
 429         Subject => 'Koha suggestion',
 430         Message => "".$template->output,
 431         'Content-Type' => 'text/plain; charset="utf8"',
 432     );
 433     sendmail(%mail);
 434 }
 435
 436 =head2 ConnectSuggestionAndBiblio
 437
 438 &ConnectSuggestionAndBiblio($suggestionid,$biblionumber)
 439
 440 connect a suggestion to an existing biblio
 441
 442 =cut
 443
 444 sub ConnectSuggestionAndBiblio {
 445     my ($suggestionid,$biblionumber) = @_;
 446     my $dbh=C4::Context->dbh;
 447     my $query = "
 448         UPDATE suggestions
 449         SET    biblionumber=?
 450         WHERE  suggestionid=?
 451     ";
 452     my $sth = $dbh->prepare($query);
 453     $sth->execute($biblionumber,$suggestionid);
 454 }
 455
 456 =head2 DelSuggestion
 457
 458 &DelSuggestion($borrowernumber,$suggestionid)
 459
 460 Delete a suggestion. A borrower can delete a suggestion only if he is its owner.
 461
 462 =cut
 463
 464 sub DelSuggestion {
 465     my ($borrowernumber,$suggestionid,$type) = @_;
 466     my $dbh = C4::Context->dbh;
 467     # check that the suggestion comes from the suggestor
 468     my $query = "
 469         SELECT suggestedby
 470         FROM   suggestions
 471         WHERE  suggestionid=?
 472     ";
 473     my $sth = $dbh->prepare($query);
 474     $sth->execute($suggestionid);
 475     my ($suggestedby) = $sth->fetchrow;
 476     if ($type eq "intranet" || $suggestedby eq $borrowernumber ) {
 477         my $queryDelete = "
 478             DELETE FROM suggestions
 479             WHERE suggestionid=?
 480         ";
 481         $sth = $dbh->prepare($queryDelete);
 482         my $suggestiondeleted=$sth->execute($suggestionid);
 483         return $suggestiondeleted;
 484     }
 485 }
 486
 487 1;
 488 __END__
 489
 490
 491 =head1 AUTHOR
 492
 493 Koha Developement team <info@koha.org>
 494
 495 =cut
 496