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