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