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