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