C4/Suggestions.pm

   1 package C4::Suggestions;
   2
   3 # $Id$
   4
   5 # Copyright 2000-2002 Katipo Communications
   6 #
   7 # This file is part of Koha.
   8 #
   9 # Koha is free software; you can redistribute it and/or modify it under the
  10 # terms of the GNU General Public License as published by the Free Software
  11 # Foundation; either version 2 of the License, or (at your option) any later
  12 # version.
  13 #
  14 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
  15 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
  16 # A PARTICULAR PURPOSE.  See the GNU General Public License for more details.
  17 #
  18 # You should have received a copy of the GNU General Public License along with
  19 # Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
  20 # Suite 330, Boston, MA  02111-1307 USA
  21
  22 use strict;
  23 require Exporter;
  24 use DBI;
  25 use C4::Context;
  26 use C4::Output;
  27 use Mail::Sendmail;
  28 # use C4::Interface::CGI::Output;
  29 use vars qw($VERSION @ISA @EXPORT);
  30
  31 # set the version for version checking
  32 $VERSION = 0.01;
  33
  34 =head1 NAME
  35
  36 C4::Accounts - Functions for dealing with Koha authorities
  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(   &newsuggestion
  62                                 &searchsuggestion
  63                                 &getsuggestion
  64                                 &delsuggestion
  65                                 &countsuggestion
  66                                 &changestatus
  67                                 &connectSuggestionAndBiblio
  68                                 &findsuggestion_from_biblionumber
  69                         );
  70
  71 =item SearchSuggestion
  72
  73   (\@array) = &SearchSuggestion($user)
  74
  75   searches for a suggestion
  76
  77 C<$user> is the user code (used as suggestor filter)
  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 sub searchsuggestion  {
  87         my ($user,$author,$title,$publishercode,$status,$suggestedbyme)=@_;
  88         my $dbh = C4::Context->dbh;
  89         my $query="Select suggestions.*,
  90                                                 U1.surname as surnamesuggestedby,U1.firstname as firstnamesuggestedby,
  91                                                 U2.surname as surnamemanagedby,U2.firstname as firstnamemanagedby
  92                                                 from suggestions
  93                                                 left join borrowers as U1 on suggestedby=U1.borrowernumber
  94                                                 left join borrowers as U2  on managedby=U2.borrowernumber
  95                                                 where 1=1";
  96         my @sql_params;
  97         if ($author) {
  98                 push @sql_params,"%".$author."%";
  99                 $query .= " and author like ?";
 100         }
 101         if ($title) {
 102                 push @sql_params,"%".$title."%";
 103                 $query .= " and suggestions.title like ?";
 104         }
 105         if ($publishercode) {
 106                 push @sql_params,"%".$publishercode."%";
 107                 $query .= " and publishercode like ?";
 108         }
 109         if ($status) {
 110                 push @sql_params,$status;
 111                 $query .= " and status=?";
 112         }
 113
 114         if (C4::Context->preference("IndependantBranches")) {
 115                 my $userenv = C4::Context->userenv;
 116                 if ($userenv) {
 117                   unless ($userenv->{flags} == 1){
 118                         push @sql_params,$userenv->{branch};
 119                         $query .= " and (U1.branchcode = ? or U1.branchcode ='')";
 120                   }
 121                 }
 122         }
 123         if ($suggestedbyme) {
 124                 if ($suggestedbyme eq -1) {
 125                 } else {
 126                         push @sql_params,$user;
 127                         $query .= " and suggestedby=?";
 128                 }
 129         } else {
 130                 $query .= " and managedby is NULL";
 131         }
 132         my $sth=$dbh->prepare($query);
 133         $sth->execute(@sql_params);
 134         my @results;
 135         my $even=1; # the even variable is used to set even / odd lines, for highlighting
 136         while (my $data=$sth->fetchrow_hashref){
 137                         $data->{$data->{STATUS}} = 1;
 138                         if ($even) {
 139                                 $even=0;
 140                                 $data->{even}=1;
 141                         } else {
 142                                 $even=1;
 143                         }
 144                         push(@results,$data);
 145         }
 146         return (\@results);
 147 }
 148
 149 sub newsuggestion {
 150         my ($borrowernumber,$title,$author,$publishercode,$note,$copyrightdate,$volumedesc,$publicationyear,$place,$isbn,$biblionumber) = @_;
 151         my $dbh = C4::Context->dbh;
 152         my $sth = $dbh->prepare("insert into suggestions (status,suggestedby,title,author,publishercode,note,copyrightdate,volumedesc,publicationyear,place,isbn,biblionumber) values ('ASKED',?,?,?,?,?,?,?,?,?,?,?)");
 153         $sth->execute($borrowernumber,$title,$author,$publishercode,$note,$copyrightdate,$volumedesc,$publicationyear,$place,$isbn,$biblionumber);
 154 }
 155
 156 sub getsuggestion {
 157         my ($suggestionid) = @_;
 158         my $dbh = C4::Context->dbh;
 159         my $sth = $dbh->prepare("select * from suggestions where suggestionid=?");
 160         $sth->execute($suggestionid);
 161         return($sth->fetchrow_hashref);
 162 }
 163
 164 sub delsuggestion {
 165         my ($borrowernumber,$suggestionid) = @_;
 166         my $dbh = C4::Context->dbh;
 167         # check that the suggestion comes from the suggestor
 168         my $sth = $dbh->prepare("select suggestedby from suggestions where suggestionid=?");
 169         $sth->execute($suggestionid);
 170         my ($suggestedby) = $sth->fetchrow;
 171         if ($suggestedby eq $borrowernumber) {
 172                 $sth = $dbh->prepare("delete from suggestions where suggestionid=?");
 173                 $sth->execute($suggestionid);
 174         }
 175 }
 176
 177 sub countsuggestion {
 178         my ($status) = @_;
 179         my $dbh = C4::Context->dbh;
 180         my $sth;
 181         if (C4::Context->preference("IndependantBranches")){
 182                 my $userenv = C4::Context->userenv;
 183                 if ($userenv->{flags} == 1){
 184                         $sth = $dbh->prepare("select count(*) from suggestions where status=?");
 185                         $sth->execute($status);
 186                 } else {
 187                         $sth = $dbh->prepare("select count(*) from suggestions,borrowers where status=? and borrowers.borrowernumber=suggestions.suggestedby and (borrowers.branchcode='' or borrowers.branchcode =?)");
 188                         $sth->execute($status,$userenv->{branch});
 189                 }
 190         } else {
 191                 $sth = $dbh->prepare("select count(*) from suggestions where status=?");
 192                 $sth->execute($status);
 193         }
 194         my ($result) = $sth->fetchrow;
 195         return $result;
 196 }
 197
 198 sub changestatus {
 199         my ($suggestionid,$status,$managedby,$biblionumber) = @_;
 200         my $dbh = C4::Context->dbh;
 201         my $sth;
 202         if ($managedby>0) {
 203                 if ($biblionumber) {
 204                         $sth = $dbh->prepare("update suggestions set status=?,managedby=?,biblionumber=? where suggestionid=?");
 205                         $sth->execute($status,$managedby,$biblionumber,$suggestionid);
 206                 } else {
 207                         $sth = $dbh->prepare("update suggestions set status=?,managedby=? where suggestionid=?");
 208                         $sth->execute($status,$managedby,$suggestionid);
 209                 }
 210         } else {
 211                 if ($biblionumber) {
 212                         $sth = $dbh->prepare("update suggestions set status=?,biblionumber=? where suggestionid=?");
 213                         $sth->execute($status,$biblionumber,$suggestionid);
 214                 } else {
 215                         $sth = $dbh->prepare("update suggestions set status=? where suggestionid=?");
 216                         $sth->execute($status,$suggestionid);
 217                 }
 218
 219         }
 220         # check mail sending.
 221         $sth = $dbh->prepare("select suggestions.*,
 222                                                         boby.surname as bysurname, boby.firstname as byfirstname, boby.emailaddress as byemail,
 223                                                         lib.surname as libsurname,lib.firstname as libfirstname,lib.emailaddress as libemail
 224                                                 from suggestions left join borrowers as boby on boby.borrowernumber=suggestedby left join borrowers as lib on lib.borrowernumber=managedby where suggestionid=?");
 225         $sth->execute($suggestionid);
 226         my $emailinfo = $sth->fetchrow_hashref;
 227         my $template = gettemplate("suggestion/mail_suggestion_$status.tmpl","intranet");
 228 #                                query =>'',
 229 #                            authnotrequired => 1,
 230 #                        });
 231         $template->param(byemail => $emailinfo->{byemail},
 232                                         libemail => $emailinfo->{libemail},
 233                                         status => $emailinfo->{status},
 234                                         title => $emailinfo->{title},
 235                                         author =>$emailinfo->{author},
 236                                         libsurname => $emailinfo->{libsurname},
 237                                         libfirstname => $emailinfo->{libfirstname},
 238                                         byfirstname => $emailinfo->{byfirstname},
 239                                         bysurname => $emailinfo->{bysurname},
 240                                         );
 241         my %mail = ( To => $emailinfo->{byemail},
 242                                  From => $emailinfo->{libemail},
 243                                  Subject => 'Koha suggestion',
 244                                  Message => "".$template->output
 245                                  );
 246 sendmail(%mail);
 247 #       warn "sending email to $emailinfo->{byemail} from $emailinfo->{libemail} to notice new status $emailinfo->{status} for $emailinfo->{title} / $emailinfo->{author}";
 248 }
 249
 250 sub findsuggestion_from_biblionumber {
 251         my ($dbh,$biblionumber) = @_;
 252         my $sth = $dbh->prepare("select suggestionid from suggestions where biblionumber=?");
 253         $sth->execute($biblionumber);
 254         my ($suggestionid) = $sth->fetchrow;
 255         return $suggestionid;
 256 }
 257
 258 # connect a suggestion to an existing biblio
 259 sub connectSuggestionAndBiblio {
 260         my ($suggestionid,$biblionumber) = @_;
 261         my $dbh=C4::Context->dbh;
 262         my $sth = $dbh->prepare("update suggestions set biblionumber=? where suggestionid=?");
 263         $sth->execute($biblionumber,$suggestionid);
 264 }
 265 =back
 266
 267 =head1 SEE ALSO
 268
 269 =cut