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.
46 The functions in this module deal with the suggestions in OPAC and in librarian interface
48 A suggestion is done in the OPAC. It has the status "ASKED"
50 When a librarian manages the suggestion, he can set the status to "REJECTED" or "ACCEPTED".
52 When the book is ordered, the suggestion status becomes "ORDERED"
54 When a book is ordered and arrived in the library, the status becomes "AVAILABLE"
56 All suggestions of a borrower can be seen by the borrower itself.
57 Suggestions done by other borrowers can be seen when not "AVAILABLE"
73 &ConnectSuggestionAndBiblio
74 &GetSuggestionFromBiblionumber
77 =head2 SearchSuggestion
81 (\@array) = &SearchSuggestion($user,$author,$title,$publishercode,$status,$suggestedbyme)
83 searches for a suggestion
86 C<\@array> : the suggestions found. Array of hash.
87 Note the status is stored twice :
89 * as parameter ( for example ASKED => 1, or REJECTED => 1) . This is for template & translation purposes.
95 sub SearchSuggestion {
96 my ($user,$author,$title,$publishercode,$status,$suggestedbyme)=@_;
97 my $dbh = C4::Context->dbh;
100 U1.surname AS surnamesuggestedby,
101 U1.firstname AS firstnamesuggestedby,
102 U2.surname AS surnamemanagedby,
103 U2.firstname AS firstnamemanagedby
105 LEFT JOIN borrowers AS U1 ON suggestedby=U1.borrowernumber
106 LEFT JOIN borrowers AS U2 ON managedby=U2.borrowernumber
111 push @sql_params,"%".$author."%";
112 $query .= " and author like ?";
115 push @sql_params,"%".$title."%";
116 $query .= " and suggestions.title like ?";
118 if ($publishercode) {
119 push @sql_params,"%".$publishercode."%";
120 $query .= " and publishercode like ?";
123 push @sql_params,$status;
124 $query .= " and status=?";
127 if (C4::Context->preference("IndependantBranches")) {
128 my $userenv = C4::Context->userenv;
130 unless ($userenv->{flags} == 1){
131 push @sql_params,$userenv->{branch};
132 $query .= " and (U1.branchcode = ? or U1.branchcode ='')";
136 if ($suggestedbyme) {
137 unless ($suggestedbyme eq -1) {
138 push @sql_params,$user;
139 $query .= " and suggestedby=?";
142 $query .= " and managedby is NULL";
144 my $sth=$dbh->prepare($query);
145 $sth->execute(@sql_params);
147 my $even=1; # the even variable is used to set even / odd lines, for highlighting
148 while (my $data=$sth->fetchrow_hashref){
149 $data->{$data->{STATUS}} = 1;
156 push(@results,$data);
165 \%sth = &GetSuggestion($suggestionid)
167 this function get the detail of the suggestion $suggestionid (input arg)
170 the result of the SQL query as a hash : $sth->fetchrow_hashref.
176 my ($suggestionid) = @_;
177 my $dbh = C4::Context->dbh;
183 my $sth = $dbh->prepare($query);
184 $sth->execute($suggestionid);
185 return($sth->fetchrow_hashref);
188 =head2 GetSuggestionFromBiblionumber
192 $suggestionid = &GetSuggestionFromBiblionumber($dbh,$biblionumber)
194 Get a suggestion from it's biblionumber.
197 the id of the suggestion which is related to the biblionumber given on input args.
202 sub GetSuggestionFromBiblionumber {
203 my ($dbh,$biblionumber) = @_;
209 my $sth = $dbh->prepare($query);
210 $sth->execute($biblionumber);
211 my ($suggestionid) = $sth->fetchrow;
212 return $suggestionid;
216 =head2 CountSuggestion
220 &CountSuggestion($status)
222 Count the number of suggestions with the status given on input argument.
223 the arg status can be :
229 =item * ASKED : asked by the user, not dealed by the librarian
231 =item * ACCEPTED : accepted by the librarian, but not yet ordered
233 =item * REJECTED : rejected by the librarian (definitive status)
235 =item * ORDERED : ordered by the librarian (acquisition module)
242 the number of suggestion with this status.
247 sub CountSuggestion {
249 my $dbh = C4::Context->dbh;
251 if (C4::Context->preference("IndependantBranches")){
252 my $userenv = C4::Context->userenv;
253 if ($userenv->{flags} == 1){
259 $sth = $dbh->prepare($query);
260 $sth->execute($status);
265 FROM suggestions,borrowers
267 AND borrowers.borrowernumber=suggestions.suggestedby
268 AND (borrowers.branchcode='' OR borrowers.branchcode =?)
270 $sth = $dbh->prepare($query);
271 $sth->execute($status,$userenv->{branch});
280 $sth = $dbh->prepare($query);
281 $sth->execute($status);
283 my ($result) = $sth->fetchrow;
292 &NewSuggestion($borrowernumber,$title,$author,$publishercode,$note,$copyrightdate,$volumedesc,$publicationyear,$place,$isbn,$biblionumber)
294 Insert a new suggestion on database with value given on input arg.
300 my ($borrowernumber,$title,$author,$publishercode,$note,$copyrightdate,$volumedesc,$publicationyear,$place,$isbn,$biblionumber) = @_;
301 my $dbh = C4::Context->dbh;
303 INSERT INTO suggestions
304 (status,suggestedby,title,author,publishercode,note,copyrightdate,
305 volumedesc,publicationyear,place,isbn,biblionumber)
306 VALUES ('ASKED',?,?,?,?,?,?,?,?,?,?,?)
308 my $sth = $dbh->prepare($query);
309 $sth->execute($borrowernumber,$title,$author,$publishercode,$note,$copyrightdate,$volumedesc,$publicationyear,$place,$isbn,$biblionumber);
316 &ModStatus($suggestionid,$status,$managedby,$biblionumber)
318 Modify the status (status can be 'ASKED', 'ACCEPTED', 'REJECTED', 'ORDERED')
319 and send a mail to notify the user that did the suggestion.
321 Note that there is no function to modify a suggestion : only the status can be modified, thus the name of the function.
327 my ($suggestionid,$status,$managedby,$biblionumber) = @_;
328 my $dbh = C4::Context->dbh;
334 SET status=?,managedby=?,biblionumber=?
337 $sth = $dbh->prepare($query);
338 $sth->execute($status,$managedby,$biblionumber,$suggestionid);
342 SET status=?,managedby=?
345 $sth = $dbh->prepare($query);
346 $sth->execute($status,$managedby,$suggestionid);
352 SET status=?,biblionumber=?
355 $sth = $dbh->prepare($query);
356 $sth->execute($status,$biblionumber,$suggestionid);
364 $sth = $dbh->prepare($query);
365 $sth->execute($status,$suggestionid);
368 # check mail sending.
370 SELECT suggestions.*,
371 boby.surname AS bysurname,
372 boby.firstname AS byfirstname,
373 boby.emailaddress AS byemail,
374 lib.surname AS libsurname,
375 lib.firstname AS libfirstname,
376 lib.emailaddress AS libemail
378 LEFT JOIN borrowers AS boby ON boby.borrowernumber=suggestedby
379 LEFT JOIN borrowers AS lib ON lib.borrowernumber=managedby
382 $sth = $dbh->prepare($queryMail);
383 $sth->execute($suggestionid);
384 my $emailinfo = $sth->fetchrow_hashref;
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
407 =head2 ConnectSuggestionAndBiblio
410 &ConnectSuggestionAndBiblio($suggestionid,$biblionumber)
412 connect a suggestion to an existing biblio
417 sub ConnectSuggestionAndBiblio {
418 my ($suggestionid,$biblionumber) = @_;
419 my $dbh=C4::Context->dbh;
425 my $sth = $dbh->prepare($query);
426 $sth->execute($biblionumber,$suggestionid);
433 &DelSuggestion($borrowernumber,$suggestionid)
435 Delete a suggestion. A borrower can delete a suggestion only if he is its owner.
442 my ($borrowernumber,$suggestionid) = @_;
443 my $dbh = C4::Context->dbh;
444 # check that the suggestion comes from the suggestor
450 my $sth = $dbh->prepare($query);
451 $sth->execute($suggestionid);
452 my ($suggestedby) = $sth->fetchrow;
453 if ($suggestedby eq $borrowernumber) {
454 my $queryDelete = qq|
455 DELETE FROM suggestions
458 $sth = $dbh->prepare($queryDelete);
459 $sth->execute($suggestionid);