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 in OPAC and in librarian interface
46 A suggestion is done in the OPAC. It has the status "ASKED"
48 When a librarian manages the suggestion, he can set the status to "REJECTED" or "ACCEPTED".
50 When the book is ordered, the suggestion status becomes "ORDERED"
52 When a book is ordered and arrived in the library, the status becomes "AVAILABLE"
54 All suggestions of a borrower can be seen by the borrower itself.
55 Suggestions done by other borrowers can be seen when not "AVAILABLE"
66 &GetSuggestionByStatus
70 &ConnectSuggestionAndBiblio
71 &GetSuggestionFromBiblionumber
74 =head2 SearchSuggestion
76 (\@array) = &SearchSuggestion($user,$author,$title,$publishercode,$status,$suggestedbyme)
78 searches for a suggestion
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.
88 sub SearchSuggestion {
89 my ($user,$author,$title,$publishercode,$status,$suggestedbyme)=@_;
90 my $dbh = C4::Context->dbh;
93 U1.surname AS surnamesuggestedby,
94 U1.firstname AS firstnamesuggestedby,
95 U2.surname AS surnamemanagedby,
96 U2.firstname AS firstnamemanagedby
98 LEFT JOIN borrowers AS U1 ON suggestedby=U1.borrowernumber
99 LEFT JOIN borrowers AS U2 ON managedby=U2.borrowernumber
104 push @sql_params,"%".$author."%";
105 $query .= " and author like ?";
108 push @sql_params,"%".$title."%";
109 $query .= " and suggestions.title like ?";
111 if ($publishercode) {
112 push @sql_params,"%".$publishercode."%";
113 $query .= " and publishercode like ?";
115 if (C4::Context->preference("IndependantBranches")) {
116 my $userenv = C4::Context->userenv;
118 unless ($userenv->{flags} == 1){
119 push @sql_params,$userenv->{branch};
120 $query .= " and (U1.branchcode = ? or U1.branchcode ='')";
125 push @sql_params,$status;
126 $query .= " and status=?";
128 if ($suggestedbyme) {
129 unless ($suggestedbyme eq -1) {
130 push @sql_params,$user;
131 $query .= " and suggestedby=?";
134 $query .= " and managedby is NULL";
136 my $sth=$dbh->prepare($query);
137 $sth->execute(@sql_params);
139 my $even=1; # the even variable is used to set even / odd lines, for highlighting
140 while (my $data=$sth->fetchrow_hashref){
141 $data->{$data->{STATUS}} = 1;
148 push(@results,$data);
155 \%sth = &GetSuggestion($suggestionid)
157 this function get the detail of the suggestion $suggestionid (input arg)
160 the result of the SQL query as a hash : $sth->fetchrow_hashref.
165 my ($suggestionid) = @_;
166 my $dbh = C4::Context->dbh;
172 my $sth = $dbh->prepare($query);
173 $sth->execute($suggestionid);
174 return($sth->fetchrow_hashref);
177 =head2 GetSuggestionFromBiblionumber
179 $suggestionid = &GetSuggestionFromBiblionumber($dbh,$biblionumber)
181 Get a suggestion from it's biblionumber.
184 the id of the suggestion which is related to the biblionumber given on input args.
188 sub GetSuggestionFromBiblionumber {
189 my ($dbh,$biblionumber) = @_;
195 my $sth = $dbh->prepare($query);
196 $sth->execute($biblionumber);
197 my ($suggestionid) = $sth->fetchrow;
198 return $suggestionid;
201 =head2 GetSuggestionByStatus
203 $suggestions = &GetSuggestionByStatus($status)
205 Get a suggestion from it's status
208 all the suggestion with C<$status>
212 sub GetSuggestionByStatus {
214 my $dbh = C4::Context->dbh;
215 my $query = "SELECT suggestions.*,
216 U1.surname AS surnamesuggestedby,
217 U1.firstname AS firstnamesuggestedby,
218 U2.surname AS surnamemanagedby,
219 U2.firstname AS firstnamemanagedby
221 LEFT JOIN borrowers AS U1 ON suggestedby=U1.borrowernumber
222 LEFT JOIN borrowers AS U2 ON managedby=U2.borrowernumber
225 my $sth = $dbh->prepare($query);
226 $sth->execute($status);
229 while(my $data = $sth->fetchrow_hashref){
230 $data->{date} = format_date($data->{date});
236 =head2 CountSuggestion
238 &CountSuggestion($status)
240 Count the number of suggestions with the status given on input argument.
241 the arg status can be :
245 =item * ASKED : asked by the user, not dealed by the librarian
247 =item * ACCEPTED : accepted by the librarian, but not yet ordered
249 =item * REJECTED : rejected by the librarian (definitive status)
251 =item * ORDERED : ordered by the librarian (acquisition module)
256 the number of suggestion with this status.
260 sub CountSuggestion {
262 my $dbh = C4::Context->dbh;
264 if (C4::Context->preference("IndependantBranches")){
265 my $userenv = C4::Context->userenv;
266 if ($userenv->{flags} == 1){
272 $sth = $dbh->prepare($query);
273 $sth->execute($status);
278 FROM suggestions,borrowers
280 AND borrowers.borrowernumber=suggestions.suggestedby
281 AND (borrowers.branchcode='' OR borrowers.branchcode =?)
283 $sth = $dbh->prepare($query);
284 $sth->execute($status,$userenv->{branch});
293 $sth = $dbh->prepare($query);
294 $sth->execute($status);
296 my ($result) = $sth->fetchrow;
303 &NewSuggestion($borrowernumber,$title,$author,$publishercode,$note,$copyrightdate,$volumedesc,$publicationyear,$place,$isbn,$biblionumber)
305 Insert a new suggestion on database with value given on input arg.
310 my ($borrowernumber,$title,$author,$publishercode,$note,$copyrightdate,$volumedesc,$publicationyear,$place,$isbn,$biblionumber,$reason) = @_;
311 my $dbh = C4::Context->dbh;
313 INSERT INTO suggestions
314 (status,suggestedby,title,author,publishercode,note,copyrightdate,
315 volumedesc,publicationyear,place,isbn,biblionumber,reason)
316 VALUES ('ASKED',?,?,?,?,?,?,?,?,?,?,?,?)
318 my $sth = $dbh->prepare($query);
319 $sth->execute($borrowernumber,$title,$author,$publishercode,$note,$copyrightdate,$volumedesc,$publicationyear,$place,$isbn,$biblionumber,$reason);
324 &ModStatus($suggestionid,$status,$managedby,$biblionumber)
326 Modify the status (status can be 'ASKED', 'ACCEPTED', 'REJECTED', 'ORDERED')
327 and send a mail to notify the user that did the suggestion.
329 Note that there is no function to modify a suggestion : only the status can be modified, thus the name of the function.
334 my ($suggestionid,$status,$managedby,$biblionumber,$reason) = @_;
335 my $dbh = C4::Context->dbh;
341 SET status=?,managedby=?,biblionumber=?,reason=?
344 $sth = $dbh->prepare($query);
345 $sth->execute($status,$managedby,$biblionumber,$reason,$suggestionid);
349 SET status=?,managedby=?,reason=?
352 $sth = $dbh->prepare($query);
353 $sth->execute($status,$managedby,$reason,$suggestionid);
359 SET status=?,biblionumber=?,reason=?
362 $sth = $dbh->prepare($query);
363 $sth->execute($status,$biblionumber,$reason,$suggestionid);
368 SET status=?,reason=?
371 $sth = $dbh->prepare($query);
372 $sth->execute($status,$reason,$suggestionid);
375 # check mail sending.
377 SELECT suggestions.*,
378 boby.surname AS bysurname,
379 boby.firstname AS byfirstname,
380 boby.email AS byemail,
381 lib.surname AS libsurname,
382 lib.firstname AS libfirstname,
383 lib.email AS libemail
385 LEFT JOIN borrowers AS boby ON boby.borrowernumber=suggestedby
386 LEFT JOIN borrowers AS lib ON lib.borrowernumber=managedby
389 $sth = $dbh->prepare($queryMail);
390 $sth->execute($suggestionid);
391 my $emailinfo = $sth->fetchrow_hashref;
392 my $template = gettemplate("suggestion/mail_suggestion_$status.tmpl","intranet");
395 byemail => $emailinfo->{byemail},
396 libemail => $emailinfo->{libemail},
397 status => $emailinfo->{status},
398 title => $emailinfo->{title},
399 author =>$emailinfo->{author},
400 libsurname => $emailinfo->{libsurname},
401 libfirstname => $emailinfo->{libfirstname},
402 byfirstname => $emailinfo->{byfirstname},
403 bysurname => $emailinfo->{bysurname},
404 reason => $emailinfo->{reason}
407 To => $emailinfo->{byemail},
408 From => $emailinfo->{libemail},
409 Subject => 'Koha suggestion',
410 Message => "".$template->output
415 =head2 ConnectSuggestionAndBiblio
417 &ConnectSuggestionAndBiblio($suggestionid,$biblionumber)
419 connect a suggestion to an existing biblio
423 sub ConnectSuggestionAndBiblio {
424 my ($suggestionid,$biblionumber) = @_;
425 my $dbh=C4::Context->dbh;
431 my $sth = $dbh->prepare($query);
432 $sth->execute($biblionumber,$suggestionid);
437 &DelSuggestion($borrowernumber,$suggestionid)
439 Delete a suggestion. A borrower can delete a suggestion only if he is its owner.
444 my ($borrowernumber,$suggestionid) = @_;
445 my $dbh = C4::Context->dbh;
446 # check that the suggestion comes from the suggestor
452 my $sth = $dbh->prepare($query);
453 $sth->execute($suggestionid);
454 my ($suggestedby) = $sth->fetchrow;
455 if ($suggestedby eq $borrowernumber) {
457 DELETE FROM suggestions
460 $sth = $dbh->prepare($queryDelete);
461 $sth->execute($suggestionid);
471 Koha Developement team <info@koha.org>