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 C4::Dates qw(format_date);
28 use vars qw($VERSION @ISA @EXPORT);
31 # set the version for version checking
39 &GetSuggestionByStatus
43 &ConnectSuggestionAndBiblio
44 &GetSuggestionFromBiblionumber
50 C4::Suggestions - Some useful functions for dealings with suggestions.
58 The functions in this module deal with the suggestions in OPAC and in librarian interface
60 A suggestion is done in the OPAC. It has the status "ASKED"
62 When a librarian manages the suggestion, he can set the status to "REJECTED" or "ACCEPTED".
64 When the book is ordered, the suggestion status becomes "ORDERED"
66 When a book is ordered and arrived in the library, the status becomes "AVAILABLE"
68 All suggestions of a borrower can be seen by the borrower itself.
69 Suggestions done by other borrowers can be seen when not "AVAILABLE"
73 =head2 SearchSuggestion
75 (\@array) = &SearchSuggestion($user,$author,$title,$publishercode,$status,$suggestedbyme,$branchcode)
77 searches for a suggestion
80 C<\@array> : the suggestions found. Array of hash.
81 Note the status is stored twice :
83 * as parameter ( for example ASKED => 1, or REJECTED => 1) . This is for template & translation purposes.
87 sub SearchSuggestion {
88 my ($user,$author,$title,$publishercode,$status,$suggestedbyme,$branchcode)=@_;
89 my $dbh = C4::Context->dbh;
92 U1.branchcode AS branchcodesuggestedby,
93 U1.surname AS surnamesuggestedby,
94 U1.firstname AS firstnamesuggestedby,
95 U1.borrowernumber AS borrnumsuggestedby,
96 U1.categorycode AS categorycodesuggestedby,
97 U2.branchcode AS branchcodemanagedby,
98 U2.surname AS surnamemanagedby,
99 U2.firstname AS firstnamemanagedby,
100 U2.borrowernumber AS borrnummanagedby
102 LEFT JOIN borrowers AS U1 ON suggestedby=U1.borrowernumber
103 LEFT JOIN borrowers AS U2 ON managedby=U2.borrowernumber
108 push @sql_params,"%".$author."%";
109 $query .= " and author like ?";
112 push @sql_params,"%".$title."%";
113 $query .= " and suggestions.title like ?";
115 if ($publishercode) {
116 push @sql_params,"%".$publishercode."%";
117 $query .= " and publishercode like ?";
119 if (C4::Context->preference("IndependantBranches") || $branchcode) {
120 my $userenv = C4::Context->userenv;
122 unless ($userenv->{flags} % 2 == 1){
123 push @sql_params,$userenv->{branch};
124 $query .= " and (U1.branchcode = ? or U1.branchcode ='')";
128 push @sql_params,$branchcode;
129 $query .= " and (U1.branchcode = ? or U1.branchcode ='')";
133 push @sql_params,$status;
134 $query .= " and status=?";
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 # $data->{date} = format_date($data->{date});
157 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.
174 my ($suggestionid) = @_;
175 my $dbh = C4::Context->dbh;
181 my $sth = $dbh->prepare($query);
182 $sth->execute($suggestionid);
183 return($sth->fetchrow_hashref);
186 =head2 GetSuggestionFromBiblionumber
188 $suggestionid = &GetSuggestionFromBiblionumber($dbh,$biblionumber)
190 Get a suggestion from it's biblionumber.
193 the id of the suggestion which is related to the biblionumber given on input args.
197 sub GetSuggestionFromBiblionumber {
198 my ($dbh,$biblionumber) = @_;
204 my $sth = $dbh->prepare($query);
205 $sth->execute($biblionumber);
206 my ($suggestionid) = $sth->fetchrow;
207 return $suggestionid;
210 =head2 GetSuggestionByStatus
212 $suggestions = &GetSuggestionByStatus($status,[$branchcode])
214 Get a suggestion from it's status
217 all the suggestion with C<$status>
221 sub GetSuggestionByStatus {
223 my $branchcode = shift;
224 my $dbh = C4::Context->dbh;
225 my @sql_params=($status);
226 my $query = qq(SELECT suggestions.*,
227 U1.surname AS surnamesuggestedby,
228 U1.firstname AS firstnamesuggestedby,
229 U1.branchcode AS branchcodesuggestedby,
230 U1.borrowernumber AS borrnumsuggestedby,
231 U1.categorycode AS categorycodesuggestedby,
232 U2.surname AS surnamemanagedby,
233 U2.firstname AS firstnamemanagedby,
234 U2.borrowernumber AS borrnummanagedby
236 LEFT JOIN borrowers AS U1 ON suggestedby=U1.borrowernumber
237 LEFT JOIN borrowers AS U2 ON managedby=U2.borrowernumber
239 if (C4::Context->preference("IndependantBranches") || $branchcode) {
240 my $userenv = C4::Context->userenv;
242 unless ($userenv->{flags} % 2 == 1){
243 push @sql_params,$userenv->{branch};
244 $query .= " and (U1.branchcode = ? or U1.branchcode ='')";
248 push @sql_params,$branchcode;
249 $query .= " and (U1.branchcode = ? or U1.branchcode ='')";
253 my $sth = $dbh->prepare($query);
254 $sth->execute(@sql_params);
257 $results= $sth->fetchall_arrayref({});
258 # map{$_->{date} = format_date($_->{date})} @$results;
262 =head2 CountSuggestion
264 &CountSuggestion($status)
266 Count the number of suggestions with the status given on input argument.
267 the arg status can be :
271 =item * ASKED : asked by the user, not dealed by the librarian
273 =item * ACCEPTED : accepted by the librarian, but not yet ordered
275 =item * REJECTED : rejected by the librarian (definitive status)
277 =item * ORDERED : ordered by the librarian (acquisition module)
282 the number of suggestion with this status.
286 sub CountSuggestion {
288 my $dbh = C4::Context->dbh;
290 if (C4::Context->preference("IndependantBranches")){
291 my $userenv = C4::Context->userenv;
292 if ($userenv->{flags} % 2 == 1){
298 $sth = $dbh->prepare($query);
299 $sth->execute($status);
304 FROM suggestions LEFT JOIN borrowers ON borrowers.borrowernumber=suggestions.suggestedby
306 AND (borrowers.branchcode='' OR borrowers.branchcode =?)
308 $sth = $dbh->prepare($query);
309 $sth->execute($status,$userenv->{branch});
318 $sth = $dbh->prepare($query);
319 $sth->execute($status);
321 my ($result) = $sth->fetchrow;
328 &NewSuggestion($borrowernumber,$title,$author,$publishercode,$note,$copyrightdate,$volumedesc,$publicationyear,$place,$isbn,$biblionumber)
330 Insert a new suggestion on database with value given on input arg.
335 my ($borrowernumber,$title,$author,$publishercode,$note,$copyrightdate,$volumedesc,$publicationyear,$place,$isbn,$biblionumber,$reason) = @_;
336 my $dbh = C4::Context->dbh;
338 INSERT INTO suggestions
339 (status,suggestedby,title,author,publishercode,note,copyrightdate,
340 volumedesc,publicationyear,place,isbn,biblionumber,reason)
341 VALUES ('ASKED',?,?,?,?,?,?,?,?,?,?,?,?)
343 my $sth = $dbh->prepare($query);
344 $sth->execute($borrowernumber,$title,$author,$publishercode,$note,$copyrightdate,$volumedesc,$publicationyear,$place,$isbn,$biblionumber,$reason);
349 &ModStatus($suggestionid,$status,$managedby,$biblionumber)
351 Modify the status (status can be 'ASKED', 'ACCEPTED', 'REJECTED', 'ORDERED')
352 and send a mail to notify the user that did the suggestion.
354 Note that there is no function to modify a suggestion : only the status can be modified, thus the name of the function.
359 my ($suggestionid,$status,$managedby,$biblionumber,$reason) = @_;
360 my $dbh = C4::Context->dbh;
366 SET status=?,managedby=?,biblionumber=?,reason=?
369 $sth = $dbh->prepare($query);
370 $sth->execute($status,$managedby,$biblionumber,$reason,$suggestionid);
374 SET status=?,managedby=?,reason=?
377 $sth = $dbh->prepare($query);
378 $sth->execute($status,$managedby,$reason,$suggestionid);
384 SET status=?,biblionumber=?,reason=?
387 $sth = $dbh->prepare($query);
388 $sth->execute($status,$biblionumber,$reason,$suggestionid);
393 SET status=?,reason=?
396 $sth = $dbh->prepare($query);
397 $sth->execute($status,$reason,$suggestionid);
400 # check mail sending.
402 SELECT suggestions.*,
403 boby.surname AS bysurname,
404 boby.firstname AS byfirstname,
405 boby.email AS byemail,
406 lib.surname AS libsurname,
407 lib.firstname AS libfirstname,
408 lib.email AS libemail
410 LEFT JOIN borrowers AS boby ON boby.borrowernumber=suggestedby
411 LEFT JOIN borrowers AS lib ON lib.borrowernumber=managedby
414 $sth = $dbh->prepare($queryMail);
415 $sth->execute($suggestionid);
416 my $emailinfo = $sth->fetchrow_hashref;
417 my $template = gettemplate("suggestion/mail_suggestion_$status.tmpl", "intranet", CGI->new());
420 byemail => $emailinfo->{byemail},
421 libemail => $emailinfo->{libemail},
422 status => $emailinfo->{status},
423 title => $emailinfo->{title},
424 author =>$emailinfo->{author},
425 libsurname => $emailinfo->{libsurname},
426 libfirstname => $emailinfo->{libfirstname},
427 byfirstname => $emailinfo->{byfirstname},
428 bysurname => $emailinfo->{bysurname},
429 reason => $emailinfo->{reason}
432 To => $emailinfo->{byemail},
433 From => $emailinfo->{libemail},
434 Subject => 'Koha suggestion',
435 Message => "".$template->output,
436 'Content-Type' => 'text/plain; charset="utf8"',
441 =head2 ConnectSuggestionAndBiblio
443 &ConnectSuggestionAndBiblio($suggestionid,$biblionumber)
445 connect a suggestion to an existing biblio
449 sub ConnectSuggestionAndBiblio {
450 my ($suggestionid,$biblionumber) = @_;
451 my $dbh=C4::Context->dbh;
457 my $sth = $dbh->prepare($query);
458 $sth->execute($biblionumber,$suggestionid);
463 &DelSuggestion($borrowernumber,$suggestionid)
465 Delete a suggestion. A borrower can delete a suggestion only if he is its owner.
470 my ($borrowernumber,$suggestionid,$type) = @_;
471 my $dbh = C4::Context->dbh;
472 # check that the suggestion comes from the suggestor
478 my $sth = $dbh->prepare($query);
479 $sth->execute($suggestionid);
480 my ($suggestedby) = $sth->fetchrow;
481 if ($type eq "intranet" || $suggestedby eq $borrowernumber ) {
483 DELETE FROM suggestions
486 $sth = $dbh->prepare($queryDelete);
487 my $suggestiondeleted=$sth->execute($suggestionid);
488 return $suggestiondeleted;
498 Koha Developement team <info@koha.org>