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 # $Id$
  21
  22 use strict;
  23 require Exporter;
  24 use DBI;
  25 use C4::Context;
  26 use C4::Output;
  27 use Mail::Sendmail;
  28 use vars qw($VERSION @ISA @EXPORT);
  29
  30 # set the version for version checking
  31 $VERSION = do { my @v = '$Revision$' =~ /\d+/g;
  32   shift(@v) . "." . join("_", map {sprintf "%03d", $_ } @v); };
  33
  34 =head1 NAME
  35
  36 C4::Suggestions - Some useful functions for dealings with suggestions.
  37
  38 =head1 SYNOPSIS
  39
  40 use C4::Suggestions;
  41
  42 =head1 DESCRIPTION
  43
  44 The functions in this module deal with the suggestions :
  45 * in OPAC
  46 * in librarian interface
  47
  48 A suggestion is done in the OPAC. It has the status "ASKED"
  49 When a librarian manages the suggestion, he can set the status to "REJECTED" or "ORDERED".
  50 When a book is ordered and arrived in the library, the status becomes "AVAILABLE"
  51 All suggestions of a borrower by the borrower itself.
  52 Suggestions done by other can be seen when not "AVAILABLE"
  53
  54 =head1 FUNCTIONS
  55
  56 =over 2
  57
  58 =cut
  59
  60 @ISA = qw(Exporter);
  61 @EXPORT = qw(
  62     &NewSuggestion
  63     &SearchSuggestion
  64     &GetSuggestion
  65     &DelSuggestion
  66     &CountSuggestion
  67     &ModStatus
  68     &ConnectSuggestionAndBiblio
  69     &GetSuggestionFromBiblionumber
  70  );
  71
  72 =item SearchSuggestion
  73
  74 (\@array) = &SearchSuggestion($user)
  75
  76 searches for a suggestion
  77
  78 C<$user> is the user code (used as suggestor filter)
  79
  80 return :
  81 C<\@array> : the suggestions found. Array of hash.
  82 Note the status is stored twice :
  83 * in the status field
  84 * as parameter ( for example ASKED => 1, or REJECTED => 1) . This is for template & translation purposes.
  85
  86 =cut
  87 sub SearchSuggestion  {
  88     my ($user,$author,$title,$publishercode,$status,$suggestedbyme)=@_;
  89     my $dbh = C4::Context->dbh;
  90     my $query = qq|
  91     SELECT suggestions.*,
  92         U1.surname   AS surnamesuggestedby,
  93         U1.firstname AS firstnamesuggestedby,
  94         U2.surname   AS surnamemanagedby,
  95         U2.firstname AS firstnamemanagedby
  96     FROM suggestions
  97     LEFT JOIN borrowers AS U1 ON suggestedby=U1.borrowernumber
  98     LEFT JOIN borrowers AS U2 ON managedby=U2.borrowernumber
  99     WHERE 1=1 |;
 100
 101     my @sql_params;
 102     if ($author) {
 103        push @sql_params,"%".$author."%";
 104        $query .= " and author like ?";
 105     }
 106     if ($title) {
 107         push @sql_params,"%".$title."%";
 108         $query .= " and suggestions.title like ?";
 109     }
 110     if ($publishercode) {
 111         push @sql_params,"%".$publishercode."%";
 112         $query .= " and publishercode like ?";
 113     }
 114     if ($status) {
 115         push @sql_params,$status;
 116         $query .= " and status=?";
 117     }
 118
 119     if (C4::Context->preference("IndependantBranches")) {
 120         my $userenv = C4::Context->userenv;
 121         if ($userenv) {
 122             unless ($userenv->{flags} == 1){
 123                 push @sql_params,$userenv->{branch};
 124                 $query .= " and (U1.branchcode = ? or U1.branchcode ='')";
 125             }
 126         }
 127     }
 128     if ($suggestedbyme) {
 129         if ($suggestedbyme eq -1) {        # FIXME ! what's this strange code ?
 130         } else {
 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 =item NewSuggestion
 155
 156 &NewSuggestion($borrowernumber,$title,$author,$publishercode,$note,$copyrightdate,$volumedesc,$publicationyear,$place,$isbn,$biblionumber)
 157
 158 Insert a new suggestion on database with value given on input arg.
 159
 160 =cut
 161 sub NewSuggestion {
 162     my ($borrowernumber,$title,$author,$publishercode,$note,$copyrightdate,$volumedesc,$publicationyear,$place,$isbn,$biblionumber) = @_;
 163     my $dbh = C4::Context->dbh;
 164     my $query = qq |
 165         INSERT INTO suggestions
 166             (status,suggestedby,title,author,publishercode,note,copyrightdate,
 167             volumedesc,publicationyear,place,isbn,biblionumber)
 168         VALUES ('ASKED',?,?,?,?,?,?,?,?,?,?,?)
 169     |;
 170     my $sth = $dbh->prepare($query);
 171     $sth->execute($borrowernumber,$title,$author,$publishercode,$note,$copyrightdate,$volumedesc,$publicationyear,$place,$isbn,$biblionumber);
 172 }
 173
 174 =item GetSuggestion
 175
 176 \%sth = &GetSuggestion($suggestionid)
 177
 178 this function get a suggestion from $suggestionid given on input arg.
 179
 180 return :
 181     the result of the SQL query as a hash : $sth->fetchrow_hashref.
 182 =cut
 183 sub GetSuggestion {
 184     my ($suggestionid) = @_;
 185     my $dbh = C4::Context->dbh;
 186     my $query = qq|
 187         SELECT *
 188         FROM   suggestions
 189         WHERE  suggestionid=?
 190     |;
 191     my $sth = $dbh->prepare($query);
 192     $sth->execute($suggestionid);
 193     return($sth->fetchrow_hashref);
 194 }
 195
 196 =item DelSuggestion
 197
 198 &DelSuggestion($borrowernumber,$suggestionid)
 199
 200 Delete a suggestion. A borrower can delete a suggestion only if he is its owner.
 201
 202 =cut
 203 sub DelSuggestion {
 204     my ($borrowernumber,$suggestionid) = @_;
 205     my $dbh = C4::Context->dbh;
 206     # check that the suggestion comes from the suggestor
 207     my $query = qq |
 208         SELECT suggestedby
 209         FROM   suggestions
 210         WHERE  suggestionid=?
 211     |;
 212     my $sth = $dbh->prepare($query);
 213     $sth->execute($suggestionid);
 214     my ($suggestedby) = $sth->fetchrow;
 215     if ($suggestedby eq $borrowernumber) {
 216         my $queryDelete = qq|
 217             DELETE FROM suggestions
 218             WHERE suggestionid=?
 219         |;
 220         $sth = $dbh->prepare($queryDelete);
 221         $sth->execute($suggestionid);
 222     }
 223 }
 224 =item CountSuggestion
 225
 226 &CountSuggestion($status)
 227
 228 Count the number of suggestions with the status given on input argument.
 229
 230 return :
 231 the number of suggestion with this status.
 232
 233 =cut
 234 sub CountSuggestion {
 235     my ($status) = @_;
 236     my $dbh = C4::Context->dbh;
 237     my $sth;
 238     if (C4::Context->preference("IndependantBranches")){
 239         my $userenv = C4::Context->userenv;
 240         if ($userenv->{flags} == 1){
 241             my $query = qq |
 242                 SELECT count(*)
 243                 FROM   suggestions
 244                 WHERE  status=?
 245             |;
 246             $sth = $dbh->prepare($query);
 247             $sth->execute($status);
 248         }
 249         else {
 250             my $query = qq |
 251                 SELECT count(*)
 252                 FROM suggestions,borrowers
 253                 WHERE status=?
 254                 AND borrowers.borrowernumber=suggestions.suggestedby
 255                 AND (borrowers.branchcode='' OR borrowers.branchcode =?)
 256             |;
 257             $sth = $dbh->prepare($query);
 258             $sth->execute($status,$userenv->{branch});
 259         }
 260     }
 261     else {
 262         my $query = qq |
 263             SELECT count(*)
 264             FROM suggestions
 265             WHERE status=?
 266         |;
 267          $sth = $dbh->prepare($query);
 268         $sth->execute($status);
 269     }
 270     my ($result) = $sth->fetchrow;
 271     return $result;
 272 }
 273
 274 =item ModStatus
 275
 276 &ModStatus($suggestionid,$status,$managedby,$biblionumber)
 277
 278 Modify the status (status can be 'ASKED', 'ACCEPTED', 'REJECTED'...)
 279 and send a mail to notify the librarian.
 280 =cut
 281 sub ModStatus {
 282     my ($suggestionid,$status,$managedby,$biblionumber) = @_;
 283     my $dbh = C4::Context->dbh;
 284     my $sth;
 285     if ($managedby>0) {
 286         if ($biblionumber) {
 287         my $query = qq|
 288             UPDATE suggestions
 289             SET    status=?,managedby=?,biblionumber=?
 290             WHERE  suggestionid=?
 291         |;
 292         $sth = $dbh->prepare($query);
 293         $sth->execute($status,$managedby,$biblionumber,$suggestionid);
 294         } else {
 295             my $query = qq|
 296                 UPDATE suggestions
 297                 SET    status=?,managedby=?
 298                 WHERE  suggestionid=?
 299             |;
 300             $sth = $dbh->prepare($query);
 301             $sth->execute($status,$managedby,$suggestionid);
 302         }
 303    } else {
 304         if ($biblionumber) {
 305             my $query = qq|
 306                 UPDATE suggestions
 307                 SET    status=?,biblionumber=?
 308                 WHERE  suggestionid=?
 309             |;
 310             $sth = $dbh->prepare($query);
 311             $sth->execute($status,$biblionumber,$suggestionid);
 312         }
 313         else {
 314             my $query = qq|
 315                 UPDATE suggestions
 316                 SET    status=?
 317                 WHERE  suggestionid=?
 318             |;
 319             $sth = $dbh->prepare($query);
 320             $sth->execute($status,$suggestionid);
 321         }
 322     }
 323     # check mail sending.
 324     my $queryMail = qq|
 325         SELECT suggestions.*,
 326             boby.surname AS bysurname,
 327             boby.firstname AS byfirstname,
 328             boby.emailaddress AS byemail,
 329             lib.surname AS libsurname,
 330             lib.firstname AS libfirstname,
 331             lib.emailaddress AS libemail
 332         FROM suggestions
 333             LEFT JOIN borrowers AS boby ON boby.borrowernumber=suggestedby
 334             LEFT JOIN borrowers AS lib ON lib.borrowernumber=managedby
 335         WHERE suggestionid=?
 336     |;
 337     $sth = $dbh->prepare($queryMail);
 338     $sth->execute($suggestionid);
 339     my $emailinfo = $sth->fetchrow_hashref;
 340     my $template = gettemplate("suggestion/mail_suggestion_$status.tmpl","intranet");
 341
 342     $template->param(
 343         byemail => $emailinfo->{byemail},
 344         libemail => $emailinfo->{libemail},
 345         status => $emailinfo->{status},
 346         title => $emailinfo->{title},
 347         author =>$emailinfo->{author},
 348         libsurname => $emailinfo->{libsurname},
 349         libfirstname => $emailinfo->{libfirstname},
 350         byfirstname => $emailinfo->{byfirstname},
 351         bysurname => $emailinfo->{bysurname},
 352     );
 353     my %mail = (
 354         To => $emailinfo->{byemail},
 355         From => $emailinfo->{libemail},
 356         Subject => 'Koha suggestion',
 357         Message => "".$template->output
 358     );
 359     sendmail(%mail);
 360 }
 361 =item GetSuggestionFromBiblionumber
 362
 363 $suggestionid = &GetSuggestionFromBiblionumber($dbh,$biblionumber)
 364
 365 Get a suggestion from the biblionumber.
 366
 367 return :
 368  the id of the suggestion which has the biblionumber given on input args.
 369
 370 =cut
 371 sub GetSuggestionFromBiblionumber {
 372     my ($dbh,$biblionumber) = @_;
 373     my $query = qq|
 374         SELECT suggestionid
 375         FROM   suggestions
 376         WHERE  biblionumber=?
 377     |;
 378     my $sth = $dbh->prepare($query);
 379     $sth->execute($biblionumber);
 380     my ($suggestionid) = $sth->fetchrow;
 381     return $suggestionid;
 382 }
 383 =item ConnectSuggestionAndBiblio
 384
 385 &ConnectSuggestionAndBiblio($suggestionid,$biblionumber)
 386
 387  connect a suggestion to an existing biblio
 388
 389 =cut
 390 sub ConnectSuggestionAndBiblio {
 391     my ($suggestionid,$biblionumber) = @_;
 392     my $dbh=C4::Context->dbh;
 393     my $query = qq |
 394         UPDATE suggestions
 395         SET    biblionumber=?
 396         WHERE  suggestionid=?
 397     |;
 398     my $sth = $dbh->prepare($query);
 399     $sth->execute($biblionumber,$suggestionid);
 400 }
 401 =back
 402
 403 =head1 SEE ALSO
 404
 405 =cut