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
24 use C4::Dates qw(format_date);
26 use vars qw($VERSION @ISA @EXPORT);
29 # set the version for version checking
37 &GetSuggestionByStatus
41 &ConnectSuggestionAndBiblio
42 &GetSuggestionFromBiblionumber
48 C4::Suggestions - Some useful functions for dealings with suggestions.
56 The functions in this module deal with the suggestions in OPAC and in librarian interface
58 A suggestion is done in the OPAC. It has the status "ASKED"
60 When a librarian manages the suggestion, he can set the status to "REJECTED" or "ACCEPTED".
62 When the book is ordered, the suggestion status becomes "ORDERED"
64 When a book is ordered and arrived in the library, the status becomes "AVAILABLE"
66 All suggestions of a borrower can be seen by the borrower itself.
67 Suggestions done by other borrowers can be seen when not "AVAILABLE"
71 =head2 SearchSuggestion
73 (\@array) = &SearchSuggestion($user,$author,$title,$publishercode,$status,$suggestedbyme)
75 searches for a suggestion
78 C<\@array> : the suggestions found. Array of hash.
79 Note the status is stored twice :
81 * as parameter ( for example ASKED => 1, or REJECTED => 1) . This is for template & translation purposes.
85 sub SearchSuggestion {
86 my ($user,$author,$title,$publishercode,$status,$suggestedbyme)=@_;
87 my $dbh = C4::Context->dbh;
90 U1.surname AS surnamesuggestedby,
91 U1.firstname AS firstnamesuggestedby,
92 U2.surname AS surnamemanagedby,
93 U2.firstname AS firstnamemanagedby
95 LEFT JOIN borrowers AS U1 ON suggestedby=U1.borrowernumber
96 LEFT JOIN borrowers AS U2 ON managedby=U2.borrowernumber
101 push @sql_params,"%".$author."%";
102 $query .= " and author like ?";
105 push @sql_params,"%".$title."%";
106 $query .= " and suggestions.title like ?";
108 if ($publishercode) {
109 push @sql_params,"%".$publishercode."%";
110 $query .= " and publishercode like ?";
112 if (C4::Context->preference("IndependantBranches")) {
113 my $userenv = C4::Context->userenv;
115 unless ($userenv->{flags} == 1){
116 push @sql_params,$userenv->{branch};
117 $query .= " and (U1.branchcode = ? or U1.branchcode ='')";
122 push @sql_params,$status;
123 $query .= " and status=?";
125 if ($suggestedbyme) {
126 unless ($suggestedbyme eq -1) {
127 push @sql_params,$user;
128 $query .= " and suggestedby=?";
131 $query .= " and managedby is NULL";
133 my $sth=$dbh->prepare($query);
134 $sth->execute(@sql_params);
136 my $even=1; # the even variable is used to set even / odd lines, for highlighting
137 while (my $data=$sth->fetchrow_hashref){
138 $data->{$data->{STATUS}} = 1;
145 push(@results,$data);
152 \%sth = &GetSuggestion($suggestionid)
154 this function get the detail of the suggestion $suggestionid (input arg)
157 the result of the SQL query as a hash : $sth->fetchrow_hashref.
162 my ($suggestionid) = @_;
163 my $dbh = C4::Context->dbh;
169 my $sth = $dbh->prepare($query);
170 $sth->execute($suggestionid);
171 return($sth->fetchrow_hashref);
174 =head2 GetSuggestionFromBiblionumber
176 $suggestionid = &GetSuggestionFromBiblionumber($dbh,$biblionumber)
178 Get a suggestion from it's biblionumber.
181 the id of the suggestion which is related to the biblionumber given on input args.
185 sub GetSuggestionFromBiblionumber {
186 my ($dbh,$biblionumber) = @_;
192 my $sth = $dbh->prepare($query);
193 $sth->execute($biblionumber);
194 my ($suggestionid) = $sth->fetchrow;
195 return $suggestionid;
198 =head2 GetSuggestionByStatus
200 $suggestions = &GetSuggestionByStatus($status)
202 Get a suggestion from it's status
205 all the suggestion with C<$status>
209 sub GetSuggestionByStatus {
211 my $dbh = C4::Context->dbh;
212 my $query = "SELECT suggestions.*,
213 U1.surname AS surnamesuggestedby,
214 U1.firstname AS firstnamesuggestedby,
215 U2.surname AS surnamemanagedby,
216 U2.firstname AS firstnamemanagedby
218 LEFT JOIN borrowers AS U1 ON suggestedby=U1.borrowernumber
219 LEFT JOIN borrowers AS U2 ON managedby=U2.borrowernumber
222 my $sth = $dbh->prepare($query);
223 $sth->execute($status);
226 while(my $data = $sth->fetchrow_hashref){
227 $data->{date} = format_date($data->{date});
233 =head2 CountSuggestion
235 &CountSuggestion($status)
237 Count the number of suggestions with the status given on input argument.
238 the arg status can be :
242 =item * ASKED : asked by the user, not dealed by the librarian
244 =item * ACCEPTED : accepted by the librarian, but not yet ordered
246 =item * REJECTED : rejected by the librarian (definitive status)
248 =item * ORDERED : ordered by the librarian (acquisition module)
253 the number of suggestion with this status.
257 sub CountSuggestion {
259 my $dbh = C4::Context->dbh;
261 if (C4::Context->preference("IndependantBranches")){
262 my $userenv = C4::Context->userenv;
263 if ($userenv->{flags} == 1){
269 $sth = $dbh->prepare($query);
270 $sth->execute($status);
275 FROM suggestions LEFT JOIN borrowers ON borrowers.borrowernumber=suggestions.suggestedby
277 AND (borrowers.branchcode='' OR borrowers.branchcode =?)
279 $sth = $dbh->prepare($query);
280 $sth->execute($status,$userenv->{branch});
289 $sth = $dbh->prepare($query);
290 $sth->execute($status);
292 my ($result) = $sth->fetchrow;
299 &NewSuggestion($borrowernumber,$title,$author,$publishercode,$note,$copyrightdate,$volumedesc,$publicationyear,$place,$isbn,$biblionumber)
301 Insert a new suggestion on database with value given on input arg.
306 my ($borrowernumber,$title,$author,$publishercode,$note,$copyrightdate,$volumedesc,$publicationyear,$place,$isbn,$biblionumber,$reason) = @_;
307 my $dbh = C4::Context->dbh;
309 INSERT INTO suggestions
310 (status,suggestedby,title,author,publishercode,note,copyrightdate,
311 volumedesc,publicationyear,place,isbn,biblionumber,reason)
312 VALUES ('ASKED',?,?,?,?,?,?,?,?,?,?,?,?)
314 my $sth = $dbh->prepare($query);
315 $sth->execute($borrowernumber,$title,$author,$publishercode,$note,$copyrightdate,$volumedesc,$publicationyear,$place,$isbn,$biblionumber,$reason);
320 &ModStatus($suggestionid,$status,$managedby,$biblionumber)
322 Modify the status (status can be 'ASKED', 'ACCEPTED', 'REJECTED', 'ORDERED')
323 and send a mail to notify the user that did the suggestion.
325 Note that there is no function to modify a suggestion : only the status can be modified, thus the name of the function.
330 my ($suggestionid,$status,$managedby,$biblionumber,$reason) = @_;
331 my $dbh = C4::Context->dbh;
337 SET status=?,managedby=?,biblionumber=?,reason=?
340 $sth = $dbh->prepare($query);
341 $sth->execute($status,$managedby,$biblionumber,$reason,$suggestionid);
345 SET status=?,managedby=?,reason=?
348 $sth = $dbh->prepare($query);
349 $sth->execute($status,$managedby,$reason,$suggestionid);
355 SET status=?,biblionumber=?,reason=?
358 $sth = $dbh->prepare($query);
359 $sth->execute($status,$biblionumber,$reason,$suggestionid);
364 SET status=?,reason=?
367 $sth = $dbh->prepare($query);
368 $sth->execute($status,$reason,$suggestionid);
371 # check mail sending.
373 SELECT suggestions.*,
374 boby.surname AS bysurname,
375 boby.firstname AS byfirstname,
376 boby.email AS byemail,
377 lib.surname AS libsurname,
378 lib.firstname AS libfirstname,
379 lib.email AS libemail
381 LEFT JOIN borrowers AS boby ON boby.borrowernumber=suggestedby
382 LEFT JOIN borrowers AS lib ON lib.borrowernumber=managedby
385 $sth = $dbh->prepare($queryMail);
386 $sth->execute($suggestionid);
387 my $emailinfo = $sth->fetchrow_hashref;
388 my $template = gettemplate("suggestion/mail_suggestion_$status.tmpl","intranet");
391 byemail => $emailinfo->{byemail},
392 libemail => $emailinfo->{libemail},
393 status => $emailinfo->{status},
394 title => $emailinfo->{title},
395 author =>$emailinfo->{author},
396 libsurname => $emailinfo->{libsurname},
397 libfirstname => $emailinfo->{libfirstname},
398 byfirstname => $emailinfo->{byfirstname},
399 bysurname => $emailinfo->{bysurname},
400 reason => $emailinfo->{reason}
403 To => $emailinfo->{byemail},
404 From => $emailinfo->{libemail},
405 Subject => 'Koha suggestion',
406 Message => "".$template->output
411 =head2 ConnectSuggestionAndBiblio
413 &ConnectSuggestionAndBiblio($suggestionid,$biblionumber)
415 connect a suggestion to an existing biblio
419 sub ConnectSuggestionAndBiblio {
420 my ($suggestionid,$biblionumber) = @_;
421 my $dbh=C4::Context->dbh;
427 my $sth = $dbh->prepare($query);
428 $sth->execute($biblionumber,$suggestionid);
433 &DelSuggestion($borrowernumber,$suggestionid)
435 Delete a suggestion. A borrower can delete a suggestion only if he is its owner.
440 my ($borrowernumber,$suggestionid) = @_;
441 my $dbh = C4::Context->dbh;
442 # check that the suggestion comes from the suggestor
448 my $sth = $dbh->prepare($query);
449 $sth->execute($suggestionid);
450 my ($suggestedby) = $sth->fetchrow;
451 if ($suggestedby eq $borrowernumber) {
453 DELETE FROM suggestions
456 $sth = $dbh->prepare($queryDelete);
457 $sth->execute($suggestionid);
467 Koha Developement team <info@koha.org>