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.categorycode AS categorycodesuggestedby,
  97         U2.branchcode AS branchcodemanagedby,
  98         U2.surname   AS surnamemanagedby,
  99         U2.firstname AS firstnamemanagedby,
 100         U2.borrowernumber AS borrnummanagedby
 101     FROM suggestions
 102     LEFT JOIN borrowers AS U1 ON suggestedby=U1.borrowernumber
 103     LEFT JOIN borrowers AS U2 ON managedby=U2.borrowernumber
 104     WHERE 1=1 ";
 105
 106     my @sql_params;
 107     if ($author) {
 108        push @sql_params,"%".$author."%";
 109        $query .= " and author like ?";
 110     }
 111     if ($title) {
 112         push @sql_params,"%".$title."%";
 113         $query .= " and suggestions.title like ?";
 114     }
 115     if ($publishercode) {
 116         push @sql_params,"%".$publishercode."%";
 117         $query .= " and publishercode like ?";
 118     }
 119     if (C4::Context->preference("IndependantBranches") || $branchcode) {
 120         my $userenv = C4::Context->userenv;
 121         if ($userenv) {
 122             unless ($userenv->{flags} % 2 == 1){
 123                 push @sql_params,$userenv->{branch};
 124                 $query .= " and (U1.branchcode = ? or U1.branchcode ='')";
 125             }
 126         }
 127         if ($branchcode) {
 128             push @sql_params,$branchcode;
 129             $query .= " and (U1.branchcode = ? or U1.branchcode ='')";
 130         }
 131     }
 132     if ($status) {
 133         push @sql_params,$status;
 134         $query .= " and status=?";
 135     }
 136     if ($suggestedbyme) {
 137         unless ($suggestedbyme eq -1) {
 138             push @sql_params,$user;
 139             $query .= " and suggestedby=?";
 140         }
 141     } else {
 142         $query .= " and managedby is NULL";
 143     }
 144     my $sth=$dbh->prepare($query);
 145     $sth->execute(@sql_params);
 146     my @results;
 147     my $even=1; # the even variable is used to set even / odd lines, for highlighting
 148     while (my $data=$sth->fetchrow_hashref){
 149         $data->{$data->{STATUS}} = 1;
 150         if ($even) {
 151             $even=0;
 152             $data->{even}=1;
 153         } else {
 154             $even=1;
 155         }
 156 #         $data->{date} = format_date($data->{date});
 157         push(@results,$data);
 158     }
 159     return (\@results);
 160 }
 161
 162 =head2 GetSuggestion
 163
 164 \%sth = &GetSuggestion($suggestionid)
 165
 166 this function get the detail of the suggestion $suggestionid (input arg)
 167
 168 return :
 169     the result of the SQL query as a hash : $sth->fetchrow_hashref.
 170
 171 =cut
 172
 173 sub GetSuggestion {
 174     my ($suggestionid) = @_;
 175     my $dbh = C4::Context->dbh;
 176     my $query = "
 177         SELECT *
 178         FROM   suggestions
 179         WHERE  suggestionid=?
 180     ";
 181     my $sth = $dbh->prepare($query);
 182     $sth->execute($suggestionid);
 183     return($sth->fetchrow_hashref);
 184 }
 185
 186 =head2 GetSuggestionFromBiblionumber
 187
 188 $suggestionid = &GetSuggestionFromBiblionumber($dbh,$biblionumber)
 189
 190 Get a suggestion from it's biblionumber.
 191
 192 return :
 193 the id of the suggestion which is related to the biblionumber given on input args.
 194
 195 =cut
 196
 197 sub GetSuggestionFromBiblionumber {
 198     my ($dbh,$biblionumber) = @_;
 199     my $query = qq|
 200         SELECT suggestionid
 201         FROM   suggestions
 202         WHERE  biblionumber=?
 203     |;
 204     my $sth = $dbh->prepare($query);
 205     $sth->execute($biblionumber);
 206     my ($suggestionid) = $sth->fetchrow;
 207     return $suggestionid;
 208 }
 209
 210 =head2 GetSuggestionByStatus
 211
 212 $suggestions = &GetSuggestionByStatus($status,[$branchcode])
 213
 214 Get a suggestion from it's status
 215
 216 return :
 217 all the suggestion with C<$status>
 218
 219 =cut
 220
 221 sub GetSuggestionByStatus {
 222     my $status = shift;
 223     my $branchcode = shift;
 224     my $dbh = C4::Context->dbh;
 225     my @sql_params=($status);
 226     my $query = qq(SELECT suggestions.*,
 227                         U1.surname   AS surnamesuggestedby,
 228                         U1.firstname AS firstnamesuggestedby,
 229             U1.branchcode AS branchcodesuggestedby,
 230                         U1.borrowernumber AS borrnumsuggestedby,
 231                         U1.categorycode AS categorycodesuggestedby,
 232                         U2.surname   AS surnamemanagedby,
 233                         U2.firstname AS firstnamemanagedby,
 234                                                 U2.borrowernumber AS borrnummanagedby
 235                         FROM suggestions
 236                         LEFT JOIN borrowers AS U1 ON suggestedby=U1.borrowernumber
 237                         LEFT JOIN borrowers AS U2 ON managedby=U2.borrowernumber
 238                         WHERE status = ?);
 239     if (C4::Context->preference("IndependantBranches") || $branchcode) {
 240         my $userenv = C4::Context->userenv;
 241         if ($userenv) {
 242             unless ($userenv->{flags} % 2 == 1){
 243                 push @sql_params,$userenv->{branch};
 244                 $query .= " and (U1.branchcode = ? or U1.branchcode ='')";
 245             }
 246         }
 247         if ($branchcode) {
 248             push @sql_params,$branchcode;
 249             $query .= " and (U1.branchcode = ? or U1.branchcode ='')";
 250         }
 251     }
 252
 253     my $sth = $dbh->prepare($query);
 254     $sth->execute(@sql_params);
 255
 256     my $results;
 257     $results=  $sth->fetchall_arrayref({});
 258 #     map{$_->{date} = format_date($_->{date})} @$results;
 259     return $results;
 260 }
 261
 262 =head2 CountSuggestion
 263
 264 &CountSuggestion($status)
 265
 266 Count the number of suggestions with the status given on input argument.
 267 the arg status can be :
 268
 269 =over 2
 270
 271 =item * ASKED : asked by the user, not dealed by the librarian
 272
 273 =item * ACCEPTED : accepted by the librarian, but not yet ordered
 274
 275 =item * REJECTED : rejected by the librarian (definitive status)
 276
 277 =item * ORDERED : ordered by the librarian (acquisition module)
 278
 279 =back
 280
 281 return :
 282 the number of suggestion with this status.
 283
 284 =cut
 285
 286 sub CountSuggestion {
 287     my ($status) = @_;
 288     my $dbh = C4::Context->dbh;
 289     my $sth;
 290     if (C4::Context->preference("IndependantBranches")){
 291         my $userenv = C4::Context->userenv;
 292         if ($userenv->{flags} % 2 == 1){
 293             my $query = qq |
 294                 SELECT count(*)
 295                 FROM   suggestions
 296                 WHERE  status=?
 297             |;
 298             $sth = $dbh->prepare($query);
 299             $sth->execute($status);
 300         }
 301         else {
 302             my $query = qq |
 303                 SELECT count(*)
 304                 FROM suggestions LEFT JOIN borrowers ON borrowers.borrowernumber=suggestions.suggestedby
 305                 WHERE status=?
 306                 AND (borrowers.branchcode='' OR borrowers.branchcode =?)
 307             |;
 308             $sth = $dbh->prepare($query);
 309             $sth->execute($status,$userenv->{branch});
 310         }
 311     }
 312     else {
 313         my $query = qq |
 314             SELECT count(*)
 315             FROM suggestions
 316             WHERE status=?
 317         |;
 318          $sth = $dbh->prepare($query);
 319         $sth->execute($status);
 320     }
 321     my ($result) = $sth->fetchrow;
 322     return $result;
 323 }
 324
 325 =head2 NewSuggestion
 326
 327
 328 &NewSuggestion($borrowernumber,$title,$author,$publishercode,$note,$copyrightdate,$volumedesc,$publicationyear,$place,$isbn,$biblionumber)
 329
 330 Insert a new suggestion on database with value given on input arg.
 331
 332 =cut
 333
 334 sub NewSuggestion {
 335     my ($borrowernumber,$title,$author,$publishercode,$note,$copyrightdate,$volumedesc,$publicationyear,$place,$isbn,$biblionumber,$reason) = @_;
 336     my $dbh = C4::Context->dbh;
 337     my $query = qq |
 338         INSERT INTO suggestions
 339             (status,suggestedby,title,author,publishercode,note,copyrightdate,
 340             volumedesc,publicationyear,place,isbn,biblionumber,reason)
 341         VALUES ('ASKED',?,?,?,?,?,?,?,?,?,?,?,?)
 342     |;
 343     my $sth = $dbh->prepare($query);
 344     $sth->execute($borrowernumber,$title,$author,$publishercode,$note,$copyrightdate,$volumedesc,$publicationyear,$place,$isbn,$biblionumber,$reason);
 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