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