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