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
27 use vars qw($VERSION @ISA @EXPORT);
29 # set the version for version checking
34 C4::Suggestions - Some useful functions for dealings with suggestions.
42 The functions in this module deal with the suggestions in OPAC and in librarian interface
44 A suggestion is done in the OPAC. It has the status "ASKED"
46 When a librarian manages the suggestion, he can set the status to "REJECTED" or "ACCEPTED".
48 When the book is ordered, the suggestion status becomes "ORDERED"
50 When a book is ordered and arrived in the library, the status becomes "AVAILABLE"
52 All suggestions of a borrower can be seen by the borrower itself.
53 Suggestions done by other borrowers can be seen when not "AVAILABLE"
64 &GetSuggestionByStatus
68 &ConnectSuggestionAndBiblio
69 &GetSuggestionFromBiblionumber
72 =head2 SearchSuggestion
74 (\@array) = &SearchSuggestion($user,$author,$title,$publishercode,$status,$suggestedbyme)
76 searches for a suggestion
79 C<\@array> : the suggestions found. Array of hash.
80 Note the status is stored twice :
82 * as parameter ( for example ASKED => 1, or REJECTED => 1) . This is for template & translation purposes.
86 sub SearchSuggestion {
87 my ($user,$author,$title,$publishercode,$status,$suggestedbyme)=@_;
88 my $dbh = C4::Context->dbh;
91 U1.surname AS surnamesuggestedby,
92 U1.firstname AS firstnamesuggestedby,
93 U2.surname AS surnamemanagedby,
94 U2.firstname AS firstnamemanagedby
96 LEFT JOIN borrowers AS U1 ON suggestedby=U1.borrowernumber
97 LEFT JOIN borrowers AS U2 ON managedby=U2.borrowernumber
102 push @sql_params,"%".$author."%";
103 $query .= " and author like ?";
106 push @sql_params,"%".$title."%";
107 $query .= " and suggestions.title like ?";
109 if ($publishercode) {
110 push @sql_params,"%".$publishercode."%";
111 $query .= " and publishercode like ?";
113 if (C4::Context->preference("IndependantBranches")) {
114 my $userenv = C4::Context->userenv;
116 unless ($userenv->{flags} == 1){
117 push @sql_params,$userenv->{branch};
118 $query .= " and (U1.branchcode = ? or U1.branchcode ='')";
123 push @sql_params,$status;
124 $query .= " and status=?";
126 if ($suggestedbyme) {
127 unless ($suggestedbyme eq -1) {
128 push @sql_params,$user;
129 $query .= " and suggestedby=?";
132 $query .= " and managedby is NULL";
134 my $sth=$dbh->prepare($query);
135 $sth->execute(@sql_params);
137 my $even=1; # the even variable is used to set even / odd lines, for highlighting
138 while (my $data=$sth->fetchrow_hashref){
139 $data->{$data->{STATUS}} = 1;
146 push(@results,$data);
153 \%sth = &GetSuggestion($suggestionid)
155 this function get the detail of the suggestion $suggestionid (input arg)
158 the result of the SQL query as a hash : $sth->fetchrow_hashref.
163 my ($suggestionid) = @_;
164 my $dbh = C4::Context->dbh;
170 my $sth = $dbh->prepare($query);
171 $sth->execute($suggestionid);
172 return($sth->fetchrow_hashref);
175 =head2 GetSuggestionFromBiblionumber
177 $suggestionid = &GetSuggestionFromBiblionumber($dbh,$biblionumber)
179 Get a suggestion from it's biblionumber.
182 the id of the suggestion which is related to the biblionumber given on input args.
186 sub GetSuggestionFromBiblionumber {
187 my ($dbh,$biblionumber) = @_;
193 my $sth = $dbh->prepare($query);
194 $sth->execute($biblionumber);
195 my ($suggestionid) = $sth->fetchrow;
196 return $suggestionid;
199 =head2 GetSuggestionByStatus
201 $suggestions = &GetSuggestionByStatus($status)
203 Get a suggestion from it's status
206 all the suggestion with C<$status>
210 sub GetSuggestionByStatus {
212 my $dbh = C4::Context->dbh;
213 my $query = "SELECT suggestions.*,
214 U1.surname AS surnamesuggestedby,
215 U1.firstname AS firstnamesuggestedby,
216 U2.surname AS surnamemanagedby,
217 U2.firstname AS firstnamemanagedby
219 LEFT JOIN borrowers AS U1 ON suggestedby=U1.borrowernumber
220 LEFT JOIN borrowers AS U2 ON managedby=U2.borrowernumber
223 my $sth = $dbh->prepare($query);
224 $sth->execute($status);
227 while(my $data = $sth->fetchrow_hashref){
228 $data->{date} = format_date($data->{date});
234 =head2 CountSuggestion
236 &CountSuggestion($status)
238 Count the number of suggestions with the status given on input argument.
239 the arg status can be :
243 =item * ASKED : asked by the user, not dealed by the librarian
245 =item * ACCEPTED : accepted by the librarian, but not yet ordered
247 =item * REJECTED : rejected by the librarian (definitive status)
249 =item * ORDERED : ordered by the librarian (acquisition module)
254 the number of suggestion with this status.
258 sub CountSuggestion {
260 my $dbh = C4::Context->dbh;
262 if (C4::Context->preference("IndependantBranches")){
263 my $userenv = C4::Context->userenv;
264 if ($userenv->{flags} == 1){
270 $sth = $dbh->prepare($query);
271 $sth->execute($status);
276 FROM suggestions LEFT JOIN borrowers ON borrowers.borrowernumber=suggestions.suggestedby
278 AND (borrowers.branchcode='' OR borrowers.branchcode =?)
280 $sth = $dbh->prepare($query);
281 $sth->execute($status,$userenv->{branch});
290 $sth = $dbh->prepare($query);
291 $sth->execute($status);
293 my ($result) = $sth->fetchrow;
300 &NewSuggestion($borrowernumber,$title,$author,$publishercode,$note,$copyrightdate,$volumedesc,$publicationyear,$place,$isbn,$biblionumber)
302 Insert a new suggestion on database with value given on input arg.
307 my ($borrowernumber,$title,$author,$publishercode,$note,$copyrightdate,$volumedesc,$publicationyear,$place,$isbn,$biblionumber,$reason) = @_;
308 my $dbh = C4::Context->dbh;
310 INSERT INTO suggestions
311 (status,suggestedby,title,author,publishercode,note,copyrightdate,
312 volumedesc,publicationyear,place,isbn,biblionumber,reason)
313 VALUES ('ASKED',?,?,?,?,?,?,?,?,?,?,?,?)
315 my $sth = $dbh->prepare($query);
316 $sth->execute($borrowernumber,$title,$author,$publishercode,$note,$copyrightdate,$volumedesc,$publicationyear,$place,$isbn,$biblionumber,$reason);
321 &ModStatus($suggestionid,$status,$managedby,$biblionumber)
323 Modify the status (status can be 'ASKED', 'ACCEPTED', 'REJECTED', 'ORDERED')
324 and send a mail to notify the user that did the suggestion.
326 Note that there is no function to modify a suggestion : only the status can be modified, thus the name of the function.
331 my ($suggestionid,$status,$managedby,$biblionumber,$reason) = @_;
332 my $dbh = C4::Context->dbh;
338 SET status=?,managedby=?,biblionumber=?,reason=?
341 $sth = $dbh->prepare($query);
342 $sth->execute($status,$managedby,$biblionumber,$reason,$suggestionid);
346 SET status=?,managedby=?,reason=?
349 $sth = $dbh->prepare($query);
350 $sth->execute($status,$managedby,$reason,$suggestionid);
356 SET status=?,biblionumber=?,reason=?
359 $sth = $dbh->prepare($query);
360 $sth->execute($status,$biblionumber,$reason,$suggestionid);
365 SET status=?,reason=?
368 $sth = $dbh->prepare($query);
369 $sth->execute($status,$reason,$suggestionid);
372 # check mail sending.
374 SELECT suggestions.*,
375 boby.surname AS bysurname,
376 boby.firstname AS byfirstname,
377 boby.email AS byemail,
378 lib.surname AS libsurname,
379 lib.firstname AS libfirstname,
380 lib.email AS libemail
382 LEFT JOIN borrowers AS boby ON boby.borrowernumber=suggestedby
383 LEFT JOIN borrowers AS lib ON lib.borrowernumber=managedby
386 $sth = $dbh->prepare($queryMail);
387 $sth->execute($suggestionid);
388 my $emailinfo = $sth->fetchrow_hashref;
389 my $template = gettemplate("suggestion/mail_suggestion_$status.tmpl","intranet");
392 byemail => $emailinfo->{byemail},
393 libemail => $emailinfo->{libemail},
394 status => $emailinfo->{status},
395 title => $emailinfo->{title},
396 author =>$emailinfo->{author},
397 libsurname => $emailinfo->{libsurname},
398 libfirstname => $emailinfo->{libfirstname},
399 byfirstname => $emailinfo->{byfirstname},
400 bysurname => $emailinfo->{bysurname},
401 reason => $emailinfo->{reason}
404 To => $emailinfo->{byemail},
405 From => $emailinfo->{libemail},
406 Subject => 'Koha suggestion',
407 Message => "".$template->output
412 =head2 ConnectSuggestionAndBiblio
414 &ConnectSuggestionAndBiblio($suggestionid,$biblionumber)
416 connect a suggestion to an existing biblio
420 sub ConnectSuggestionAndBiblio {
421 my ($suggestionid,$biblionumber) = @_;
422 my $dbh=C4::Context->dbh;
428 my $sth = $dbh->prepare($query);
429 $sth->execute($biblionumber,$suggestionid);
434 &DelSuggestion($borrowernumber,$suggestionid)
436 Delete a suggestion. A borrower can delete a suggestion only if he is its owner.
441 my ($borrowernumber,$suggestionid) = @_;
442 my $dbh = C4::Context->dbh;
443 # check that the suggestion comes from the suggestor
449 my $sth = $dbh->prepare($query);
450 $sth->execute($suggestionid);
451 my ($suggestedby) = $sth->fetchrow;
452 if ($suggestedby eq $borrowernumber) {
454 DELETE FROM suggestions
457 $sth = $dbh->prepare($queryDelete);
458 $sth->execute($suggestionid);
468 Koha Developement team <info@koha.org>