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