1 package C4::Suggestions;
3 # Copyright 2000-2002 Katipo Communications
5 # This file is part of Koha.
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
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.
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
28 use vars qw($VERSION @ISA @EXPORT);
30 # set the version for version checking
31 $VERSION = do { my @v = '$Revision$' =~ /\d+/g;
32 shift(@v) . "." . join("_", map {sprintf "%03d", $_ } @v); };
36 C4::Suggestions - Some useful functions for dealings with suggestions.
44 The functions in this module deal with the suggestions :
46 * in librarian interface
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"
68 &ConnectSuggestionAndBiblio
69 &GetSuggestionFromBiblionumber
72 =item SearchSuggestion
74 (\@array) = &SearchSuggestion($user)
76 searches for a suggestion
78 C<$user> is the user code (used as suggestor filter)
81 C<\@array> : the suggestions found. Array of hash.
82 Note the status is stored twice :
84 * as parameter ( for example ASKED => 1, or REJECTED => 1) . This is for template & translation purposes.
87 sub SearchSuggestion {
88 my ($user,$author,$title,$publishercode,$status,$suggestedbyme)=@_;
89 my $dbh = C4::Context->dbh;
92 U1.surname AS surnamesuggestedby,
93 U1.firstname AS firstnamesuggestedby,
94 U2.surname AS surnamemanagedby,
95 U2.firstname AS firstnamemanagedby
97 LEFT JOIN borrowers AS U1 ON suggestedby=U1.borrowernumber
98 LEFT JOIN borrowers AS U2 ON managedby=U2.borrowernumber
103 push @sql_params,"%".$author."%";
104 $query .= " and author like ?";
107 push @sql_params,"%".$title."%";
108 $query .= " and suggestions.title like ?";
110 if ($publishercode) {
111 push @sql_params,"%".$publishercode."%";
112 $query .= " and publishercode like ?";
115 push @sql_params,$status;
116 $query .= " and status=?";
119 if (C4::Context->preference("IndependantBranches")) {
120 my $userenv = C4::Context->userenv;
122 unless ($userenv->{flags} == 1){
123 push @sql_params,$userenv->{branch};
124 $query .= " and (U1.branchcode = ? or U1.branchcode ='')";
128 if ($suggestedbyme) {
129 if ($suggestedbyme eq -1) { # FIXME ! what's this strange code ?
131 push @sql_params,$user;
132 $query .= " and suggestedby=?";
135 $query .= " and managedby is NULL";
137 my $sth=$dbh->prepare($query);
138 $sth->execute(@sql_params);
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;
149 push(@results,$data);
156 &NewSuggestion($borrowernumber,$title,$author,$publishercode,$note,$copyrightdate,$volumedesc,$publicationyear,$place,$isbn,$biblionumber)
158 Insert a new suggestion on database with value given on input arg.
162 my ($borrowernumber,$title,$author,$publishercode,$note,$copyrightdate,$volumedesc,$publicationyear,$place,$isbn,$biblionumber) = @_;
163 my $dbh = C4::Context->dbh;
165 INSERT INTO suggestions
166 (status,suggestedby,title,author,publishercode,note,copyrightdate,
167 volumedesc,publicationyear,place,isbn,biblionumber)
168 VALUES ('ASKED',?,?,?,?,?,?,?,?,?,?,?)
170 my $sth = $dbh->prepare($query);
171 $sth->execute($borrowernumber,$title,$author,$publishercode,$note,$copyrightdate,$volumedesc,$publicationyear,$place,$isbn,$biblionumber);
176 \%sth = &GetSuggestion($suggestionid)
178 this function get a suggestion from $suggestionid given on input arg.
181 the result of the SQL query as a hash : $sth->fetchrow_hashref.
184 my ($suggestionid) = @_;
185 my $dbh = C4::Context->dbh;
191 my $sth = $dbh->prepare($query);
192 $sth->execute($suggestionid);
193 return($sth->fetchrow_hashref);
198 &DelSuggestion($borrowernumber,$suggestionid)
200 Delete a suggestion. A borrower can delete a suggestion only if he is its owner.
204 my ($borrowernumber,$suggestionid) = @_;
205 my $dbh = C4::Context->dbh;
206 # check that the suggestion comes from the suggestor
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
220 $sth = $dbh->prepare($queryDelete);
221 $sth->execute($suggestionid);
224 =item CountSuggestion
226 &CountSuggestion($status)
228 Count the number of suggestions with the status given on input argument.
231 the number of suggestion with this status.
234 sub CountSuggestion {
236 my $dbh = C4::Context->dbh;
238 if (C4::Context->preference("IndependantBranches")){
239 my $userenv = C4::Context->userenv;
240 if ($userenv->{flags} == 1){
246 $sth = $dbh->prepare($query);
247 $sth->execute($status);
252 FROM suggestions,borrowers
254 AND borrowers.borrowernumber=suggestions.suggestedby
255 AND (borrowers.branchcode='' OR borrowers.branchcode =?)
257 $sth = $dbh->prepare($query);
258 $sth->execute($status,$userenv->{branch});
267 $sth = $dbh->prepare($query);
268 $sth->execute($status);
270 my ($result) = $sth->fetchrow;
276 &ModStatus($suggestionid,$status,$managedby,$biblionumber)
278 Modify the status (status can be 'ASKED', 'ACCEPTED', 'REJECTED'...)
279 and send a mail to notify the librarian.
282 my ($suggestionid,$status,$managedby,$biblionumber) = @_;
283 my $dbh = C4::Context->dbh;
289 SET status=?,managedby=?,biblionumber=?
292 $sth = $dbh->prepare($query);
293 $sth->execute($status,$managedby,$biblionumber,$suggestionid);
297 SET status=?,managedby=?
300 $sth = $dbh->prepare($query);
301 $sth->execute($status,$managedby,$suggestionid);
307 SET status=?,biblionumber=?
310 $sth = $dbh->prepare($query);
311 $sth->execute($status,$biblionumber,$suggestionid);
319 $sth = $dbh->prepare($query);
320 $sth->execute($status,$suggestionid);
323 # check mail sending.
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
333 LEFT JOIN borrowers AS boby ON boby.borrowernumber=suggestedby
334 LEFT JOIN borrowers AS lib ON lib.borrowernumber=managedby
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");
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},
354 To => $emailinfo->{byemail},
355 From => $emailinfo->{libemail},
356 Subject => 'Koha suggestion',
357 Message => "".$template->output
361 =item GetSuggestionFromBiblionumber
363 $suggestionid = &GetSuggestionFromBiblionumber($dbh,$biblionumber)
365 Get a suggestion from the biblionumber.
368 the id of the suggestion which has the biblionumber given on input args.
371 sub GetSuggestionFromBiblionumber {
372 my ($dbh,$biblionumber) = @_;
378 my $sth = $dbh->prepare($query);
379 $sth->execute($biblionumber);
380 my ($suggestionid) = $sth->fetchrow;
381 return $suggestionid;
383 =item ConnectSuggestionAndBiblio
385 &ConnectSuggestionAndBiblio($suggestionid,$biblionumber)
387 connect a suggestion to an existing biblio
390 sub ConnectSuggestionAndBiblio {
391 my ($suggestionid,$biblionumber) = @_;
392 my $dbh=C4::Context->dbh;
398 my $sth = $dbh->prepare($query);
399 $sth->execute($biblionumber,$suggestionid);