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
30 $VERSION = do { my @v = '$Revision$' =~ /\d+/g;
31 shift(@v) . "." . join("_", map {sprintf "%03d", $_ } @v); };
35 C4::Suggestions - Some useful functions for dealings with suggestions.
45 The functions in this module deal with the suggestions in OPAC and in librarian interface
47 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 "ACCEPTED".
51 When the book is ordered, the suggestion status becomes "ORDERED"
53 When a book is ordered and arrived in the library, the status becomes "AVAILABLE"
55 All suggestions of a borrower can be seen by the borrower itself.
56 Suggestions done by other borrowers can be seen when not "AVAILABLE"
72 &ConnectSuggestionAndBiblio
73 &GetSuggestionFromBiblionumber
76 =head2 SearchSuggestion
80 (\@array) = &SearchSuggestion($user,$author,$title,$publishercode,$status,$suggestedbyme)
82 searches for a suggestion
85 C<\@array> : the suggestions found. Array of hash.
86 Note the status is stored twice :
88 * as parameter ( for example ASKED => 1, or REJECTED => 1) . This is for template & translation purposes.
94 sub SearchSuggestion {
95 my ($user,$author,$title,$publishercode,$status,$suggestedbyme)=@_;
96 my $dbh = C4::Context->dbh;
99 U1.surname AS surnamesuggestedby,
100 U1.firstname AS firstnamesuggestedby,
101 U2.surname AS surnamemanagedby,
102 U2.firstname AS firstnamemanagedby
104 LEFT JOIN borrowers AS U1 ON suggestedby=U1.borrowernumber
105 LEFT JOIN borrowers AS U2 ON managedby=U2.borrowernumber
110 push @sql_params,"%".$author."%";
111 $query .= " and author like ?";
114 push @sql_params,"%".$title."%";
115 $query .= " and suggestions.title like ?";
117 if ($publishercode) {
118 push @sql_params,"%".$publishercode."%";
119 $query .= " and publishercode like ?";
122 push @sql_params,$status;
123 $query .= " and status=?";
126 if (C4::Context->preference("IndependantBranches")) {
127 my $userenv = C4::Context->userenv;
129 unless ($userenv->{flags} == 1){
130 push @sql_params,$userenv->{branch};
131 $query .= " and (U1.branchcode = ? or U1.branchcode ='')";
135 if ($suggestedbyme) {
136 unless ($suggestedbyme eq -1) {
137 push @sql_params,$user;
138 $query .= " and suggestedby=?";
141 $query .= " and managedby is NULL";
143 my $sth=$dbh->prepare($query);
144 $sth->execute(@sql_params);
146 my $even=1; # the even variable is used to set even / odd lines, for highlighting
147 while (my $data=$sth->fetchrow_hashref){
148 $data->{$data->{STATUS}} = 1;
155 push(@results,$data);
164 \%sth = &GetSuggestion($suggestionid)
166 this function get the detail of the suggestion $suggestionid (input arg)
169 the result of the SQL query as a hash : $sth->fetchrow_hashref.
175 my ($suggestionid) = @_;
176 my $dbh = C4::Context->dbh;
182 my $sth = $dbh->prepare($query);
183 $sth->execute($suggestionid);
184 return($sth->fetchrow_hashref);
187 =head2 GetSuggestionFromBiblionumber
191 $suggestionid = &GetSuggestionFromBiblionumber($dbh,$biblionumber)
193 Get a suggestion from it's biblionumber.
196 the id of the suggestion which is related to the biblionumber given on input args.
201 sub GetSuggestionFromBiblionumber {
202 my ($dbh,$biblionumber) = @_;
208 my $sth = $dbh->prepare($query);
209 $sth->execute($biblionumber);
210 my ($suggestionid) = $sth->fetchrow;
211 return $suggestionid;
215 =head2 CountSuggestion
219 &CountSuggestion($status)
221 Count the number of suggestions with the status given on input argument.
222 the arg status can be :
228 =item * ASKED : asked by the user, not dealed by the librarian
230 =item * ACCEPTED : accepted by the librarian, but not yet ordered
232 =item * REJECTED : rejected by the librarian (definitive status)
234 =item * ORDERED : ordered by the librarian (acquisition module)
241 the number of suggestion with this status.
246 sub CountSuggestion {
248 my $dbh = C4::Context->dbh;
250 if (C4::Context->preference("IndependantBranches")){
251 my $userenv = C4::Context->userenv;
252 if ($userenv->{flags} == 1){
258 $sth = $dbh->prepare($query);
259 $sth->execute($status);
264 FROM suggestions,borrowers
266 AND borrowers.borrowernumber=suggestions.suggestedby
267 AND (borrowers.branchcode='' OR borrowers.branchcode =?)
269 $sth = $dbh->prepare($query);
270 $sth->execute($status,$userenv->{branch});
279 $sth = $dbh->prepare($query);
280 $sth->execute($status);
282 my ($result) = $sth->fetchrow;
291 &NewSuggestion($borrowernumber,$title,$author,$publishercode,$note,$copyrightdate,$volumedesc,$publicationyear,$place,$isbn,$biblionumber)
293 Insert a new suggestion on database with value given on input arg.
299 my ($borrowernumber,$title,$author,$publishercode,$note,$copyrightdate,$volumedesc,$publicationyear,$place,$isbn,$biblionumber) = @_;
300 my $dbh = C4::Context->dbh;
302 INSERT INTO suggestions
303 (status,suggestedby,title,author,publishercode,note,copyrightdate,
304 volumedesc,publicationyear,place,isbn,biblionumber)
305 VALUES ('ASKED',?,?,?,?,?,?,?,?,?,?,?)
307 my $sth = $dbh->prepare($query);
308 $sth->execute($borrowernumber,$title,$author,$publishercode,$note,$copyrightdate,$volumedesc,$publicationyear,$place,$isbn,$biblionumber);
315 &ModStatus($suggestionid,$status,$managedby,$biblionumber)
317 Modify the status (status can be 'ASKED', 'ACCEPTED', 'REJECTED', 'ORDERED')
318 and send a mail to notify the user that did the suggestion.
320 Note that there is no function to modify a suggestion : only the status can be modified, thus the name of the function.
326 my ($suggestionid,$status,$managedby,$biblionumber) = @_;
327 my $dbh = C4::Context->dbh;
333 SET status=?,managedby=?,biblionumber=?
336 $sth = $dbh->prepare($query);
337 $sth->execute($status,$managedby,$biblionumber,$suggestionid);
341 SET status=?,managedby=?
344 $sth = $dbh->prepare($query);
345 $sth->execute($status,$managedby,$suggestionid);
351 SET status=?,biblionumber=?
354 $sth = $dbh->prepare($query);
355 $sth->execute($status,$biblionumber,$suggestionid);
363 $sth = $dbh->prepare($query);
364 $sth->execute($status,$suggestionid);
367 # check mail sending.
369 SELECT suggestions.*,
370 boby.surname AS bysurname,
371 boby.firstname AS byfirstname,
372 boby.emailaddress AS byemail,
373 lib.surname AS libsurname,
374 lib.firstname AS libfirstname,
375 lib.emailaddress AS libemail
377 LEFT JOIN borrowers AS boby ON boby.borrowernumber=suggestedby
378 LEFT JOIN borrowers AS lib ON lib.borrowernumber=managedby
381 $sth = $dbh->prepare($queryMail);
382 $sth->execute($suggestionid);
383 my $emailinfo = $sth->fetchrow_hashref;
384 if ($emailinfo->{byemail}){
385 my $template = gettemplate("suggestion/mail_suggestion_$status.tmpl","intranet");
388 byemail => $emailinfo->{byemail},
389 libemail => $emailinfo->{libemail},
390 status => $emailinfo->{status},
391 title => $emailinfo->{title},
392 author =>$emailinfo->{author},
393 libsurname => $emailinfo->{libsurname},
394 libfirstname => $emailinfo->{libfirstname},
395 byfirstname => $emailinfo->{byfirstname},
396 bysurname => $emailinfo->{bysurname},
399 To => $emailinfo->{byemail},
400 From => $emailinfo->{libemail},
401 Subject => 'Koha suggestion',
402 Message => "".$template->output
408 =head2 ConnectSuggestionAndBiblio
411 &ConnectSuggestionAndBiblio($suggestionid,$biblionumber)
413 connect a suggestion to an existing biblio
418 sub ConnectSuggestionAndBiblio {
419 my ($suggestionid,$biblionumber) = @_;
420 my $dbh=C4::Context->dbh;
426 my $sth = $dbh->prepare($query);
427 $sth->execute($biblionumber,$suggestionid);
434 &DelSuggestion($borrowernumber,$suggestionid)
436 Delete a suggestion. A borrower can delete a suggestion only if he is its owner.
443 my ($borrowernumber,$suggestionid) = @_;
444 my $dbh = C4::Context->dbh;
445 # check that the suggestion comes from the suggestor
451 my $sth = $dbh->prepare($query);
452 $sth->execute($suggestionid);
453 my ($suggestedby) = $sth->fetchrow;
454 if ($suggestedby eq $borrowernumber) {
455 my $queryDelete = qq|
456 DELETE FROM suggestions
459 $sth = $dbh->prepare($queryDelete);
460 $sth->execute($suggestionid);