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