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