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,borrowers
 279                 WHERE status=?
 280                 AND borrowers.borrowernumber=suggestions.suggestedby
 281                 AND (borrowers.branchcode='' OR borrowers.branchcode =?)
 282             |;
 283             $sth = $dbh->prepare($query);
 284             $sth->execute($status,$userenv->{branch});
 285         }
 286     }
 287     else {
 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     my ($result) = $sth->fetchrow;
 297     return $result;
 298 }
 299
 300 =head2 NewSuggestion
 301
 302
 303 &NewSuggestion($borrowernumber,$title,$author,$publishercode,$note,$copyrightdate,$volumedesc,$publicationyear,$place,$isbn,$biblionumber)
 304
 305 Insert a new suggestion on database with value given on input arg.
 306
 307 =cut
 308
 309 sub NewSuggestion {
 310     my ($borrowernumber,$title,$author,$publishercode,$note,$copyrightdate,$volumedesc,$publicationyear,$place,$isbn,$biblionumber,$reason) = @_;
 311     my $dbh = C4::Context->dbh;
 312     my $query = qq |
 313         INSERT INTO suggestions
 314             (status,suggestedby,title,author,publishercode,note,copyrightdate,
 315             volumedesc,publicationyear,place,isbn,biblionumber,reason)
 316         VALUES ('ASKED',?,?,?,?,?,?,?,?,?,?,?,?)
 317     |;
 318     my $sth = $dbh->prepare($query);
 319     $sth->execute($borrowernumber,$title,$author,$publishercode,$note,$copyrightdate,$volumedesc,$publicationyear,$place,$isbn,$biblionumber,$reason);
 320 }
 321
 322 =head2 ModStatus
 323
 324 &ModStatus($suggestionid,$status,$managedby,$biblionumber)
 325
 326 Modify the status (status can be 'ASKED', 'ACCEPTED', 'REJECTED', 'ORDERED')
 327 and send a mail to notify the user that did the suggestion.
 328
 329 Note that there is no function to modify a suggestion : only the status can be modified, thus the name of the function.
 330
 331 =cut
 332
 333 sub ModStatus {
 334     my ($suggestionid,$status,$managedby,$biblionumber,$reason) = @_;
 335     my $dbh = C4::Context->dbh;
 336     my $sth;
 337     if ($managedby>0) {
 338         if ($biblionumber) {
 339         my $query = qq|
 340             UPDATE suggestions
 341             SET    status=?,managedby=?,biblionumber=?,reason=?
 342             WHERE  suggestionid=?
 343         |;
 344         $sth = $dbh->prepare($query);
 345         $sth->execute($status,$managedby,$biblionumber,$reason,$suggestionid);
 346         } else {
 347             my $query = qq|
 348                 UPDATE suggestions
 349                 SET    status=?,managedby=?,reason=?
 350                 WHERE  suggestionid=?
 351             |;
 352             $sth = $dbh->prepare($query);
 353             $sth->execute($status,$managedby,$reason,$suggestionid);
 354         }
 355    } else {
 356         if ($biblionumber) {
 357             my $query = qq|
 358                 UPDATE suggestions
 359                 SET    status=?,biblionumber=?,reason=?
 360                 WHERE  suggestionid=?
 361             |;
 362             $sth = $dbh->prepare($query);
 363             $sth->execute($status,$biblionumber,$reason,$suggestionid);
 364         }
 365         else {
 366             my $query = qq|
 367                 UPDATE suggestions
 368                 SET    status=?,reason=?
 369                 WHERE  suggestionid=?
 370             |;
 371             $sth = $dbh->prepare($query);
 372             $sth->execute($status,$reason,$suggestionid);
 373         }
 374     }
 375     # check mail sending.
 376     my $queryMail = "
 377         SELECT suggestions.*,
 378             boby.surname AS bysurname,
 379             boby.firstname AS byfirstname,
 380             boby.email AS byemail,
 381             lib.surname AS libsurname,
 382             lib.firstname AS libfirstname,
 383             lib.email AS libemail
 384         FROM suggestions
 385             LEFT JOIN borrowers AS boby ON boby.borrowernumber=suggestedby
 386             LEFT JOIN borrowers AS lib ON lib.borrowernumber=managedby
 387         WHERE suggestionid=?
 388     ";
 389     $sth = $dbh->prepare($queryMail);
 390     $sth->execute($suggestionid);
 391     my $emailinfo = $sth->fetchrow_hashref;
 392     my $template = gettemplate("suggestion/mail_suggestion_$status.tmpl","intranet");
 393
 394     $template->param(
 395         byemail => $emailinfo->{byemail},
 396         libemail => $emailinfo->{libemail},
 397         status => $emailinfo->{status},
 398         title => $emailinfo->{title},
 399         author =>$emailinfo->{author},
 400         libsurname => $emailinfo->{libsurname},
 401         libfirstname => $emailinfo->{libfirstname},
 402         byfirstname => $emailinfo->{byfirstname},
 403         bysurname => $emailinfo->{bysurname},
 404         reason => $emailinfo->{reason}
 405     );
 406     my %mail = (
 407         To => $emailinfo->{byemail},
 408         From => $emailinfo->{libemail},
 409         Subject => 'Koha suggestion',
 410         Message => "".$template->output
 411     );
 412     sendmail(%mail);
 413 }
 414
 415 =head2 ConnectSuggestionAndBiblio
 416
 417 &ConnectSuggestionAndBiblio($suggestionid,$biblionumber)
 418
 419 connect a suggestion to an existing biblio
 420
 421 =cut
 422
 423 sub ConnectSuggestionAndBiblio {
 424     my ($suggestionid,$biblionumber) = @_;
 425     my $dbh=C4::Context->dbh;
 426     my $query = "
 427         UPDATE suggestions
 428         SET    biblionumber=?
 429         WHERE  suggestionid=?
 430     ";
 431     my $sth = $dbh->prepare($query);
 432     $sth->execute($biblionumber,$suggestionid);
 433 }
 434
 435 =head2 DelSuggestion
 436
 437 &DelSuggestion($borrowernumber,$suggestionid)
 438
 439 Delete a suggestion. A borrower can delete a suggestion only if he is its owner.
 440
 441 =cut
 442
 443 sub DelSuggestion {
 444     my ($borrowernumber,$suggestionid) = @_;
 445     my $dbh = C4::Context->dbh;
 446     # check that the suggestion comes from the suggestor
 447     my $query = "
 448         SELECT suggestedby
 449         FROM   suggestions
 450         WHERE  suggestionid=?
 451     ";
 452     my $sth = $dbh->prepare($query);
 453     $sth->execute($suggestionid);
 454     my ($suggestedby) = $sth->fetchrow;
 455     if ($suggestedby eq $borrowernumber) {
 456         my $queryDelete = "
 457             DELETE FROM suggestions
 458             WHERE suggestionid=?
 459         ";
 460         $sth = $dbh->prepare($queryDelete);
 461         $sth->execute($suggestionid);
 462     }
 463 }
 464
 465 1;
 466 __END__
 467
 468
 469 =head1 AUTHOR
 470
 471 Koha Developement team <info@koha.org>
 472
 473 =cut
 474