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 U2.surname AS surnamemanagedby,
97 U2.firstname AS firstnamemanagedby,
98 U2.borrowernumber AS borrnummanagedby
100 LEFT JOIN borrowers AS U1 ON suggestedby=U1.borrowernumber
101 LEFT JOIN borrowers AS U2 ON managedby=U2.borrowernumber
106 push @sql_params,"%".$author."%";
107 $query .= " and author like ?";
110 push @sql_params,"%".$title."%";
111 $query .= " and suggestions.title like ?";
113 if ($publishercode) {
114 push @sql_params,"%".$publishercode."%";
115 $query .= " and publishercode like ?";
117 if (C4::Context->preference("IndependantBranches") || $branchcode) {
118 my $userenv = C4::Context->userenv;
120 unless ($userenv->{flags} == 1){
121 push @sql_params,$userenv->{branch};
122 $query .= " and (U1.branchcode = ? or U1.branchcode ='')";
126 push @sql_params,$branchcode;
127 $query .= " and (U1.branchcode = ? or U1.branchcode ='')";
131 push @sql_params,$status;
132 $query .= " and status=?";
134 if ($suggestedbyme) {
135 unless ($suggestedbyme eq -1) {
136 push @sql_params,$user;
137 $query .= " and suggestedby=?";
140 $query .= " and managedby is NULL";
142 my $sth=$dbh->prepare($query);
143 $sth->execute(@sql_params);
145 my $even=1; # the even variable is used to set even / odd lines, for highlighting
146 while (my $data=$sth->fetchrow_hashref){
147 $data->{$data->{STATUS}} = 1;
154 # $data->{date} = format_date($data->{date});
155 push(@results,$data);
162 \%sth = &GetSuggestion($suggestionid)
164 this function get the detail of the suggestion $suggestionid (input arg)
167 the result of the SQL query as a hash : $sth->fetchrow_hashref.
172 my ($suggestionid) = @_;
173 my $dbh = C4::Context->dbh;
179 my $sth = $dbh->prepare($query);
180 $sth->execute($suggestionid);
181 return($sth->fetchrow_hashref);
184 =head2 GetSuggestionFromBiblionumber
186 $suggestionid = &GetSuggestionFromBiblionumber($dbh,$biblionumber)
188 Get a suggestion from it's biblionumber.
191 the id of the suggestion which is related to the biblionumber given on input args.
195 sub GetSuggestionFromBiblionumber {
196 my ($dbh,$biblionumber) = @_;
202 my $sth = $dbh->prepare($query);
203 $sth->execute($biblionumber);
204 my ($suggestionid) = $sth->fetchrow;
205 return $suggestionid;
208 =head2 GetSuggestionByStatus
210 $suggestions = &GetSuggestionByStatus($status,[$branchcode])
212 Get a suggestion from it's status
215 all the suggestion with C<$status>
219 sub GetSuggestionByStatus {
221 my $branchcode = shift;
222 my $dbh = C4::Context->dbh;
223 my @sql_params=($status);
224 my $query = qq(SELECT suggestions.*,
225 U1.surname AS surnamesuggestedby,
226 U1.firstname AS firstnamesuggestedby,
227 U1.branchcode AS branchcodesuggestedby,
228 U1.borrowernumber AS borrnumsuggestedby,
229 U2.surname AS surnamemanagedby,
230 U2.firstname AS firstnamemanagedby,
231 U2.borrowernumber AS borrnummanagedby
233 LEFT JOIN borrowers AS U1 ON suggestedby=U1.borrowernumber
234 LEFT JOIN borrowers AS U2 ON managedby=U2.borrowernumber
236 if (C4::Context->preference("IndependantBranches") || $branchcode) {
237 my $userenv = C4::Context->userenv;
239 unless ($userenv->{flags} == 1){
240 push @sql_params,$userenv->{branch};
241 $query .= " and (U1.branchcode = ? or U1.branchcode ='')";
245 push @sql_params,$branchcode;
246 $query .= " and (U1.branchcode = ? or U1.branchcode ='')";
250 my $sth = $dbh->prepare($query);
251 $sth->execute(@sql_params);
254 $results= $sth->fetchall_arrayref({});
255 # map{$_->{date} = format_date($_->{date})} @$results;
259 =head2 CountSuggestion
261 &CountSuggestion($status)
263 Count the number of suggestions with the status given on input argument.
264 the arg status can be :
268 =item * ASKED : asked by the user, not dealed by the librarian
270 =item * ACCEPTED : accepted by the librarian, but not yet ordered
272 =item * REJECTED : rejected by the librarian (definitive status)
274 =item * ORDERED : ordered by the librarian (acquisition module)
279 the number of suggestion with this status.
283 sub CountSuggestion {
285 my $dbh = C4::Context->dbh;
287 if (C4::Context->preference("IndependantBranches")){
288 my $userenv = C4::Context->userenv;
289 if ($userenv->{flags} == 1){
295 $sth = $dbh->prepare($query);
296 $sth->execute($status);
301 FROM suggestions LEFT JOIN borrowers ON borrowers.borrowernumber=suggestions.suggestedby
303 AND (borrowers.branchcode='' OR borrowers.branchcode =?)
305 $sth = $dbh->prepare($query);
306 $sth->execute($status,$userenv->{branch});
315 $sth = $dbh->prepare($query);
316 $sth->execute($status);
318 my ($result) = $sth->fetchrow;
325 &NewSuggestion($borrowernumber,$title,$author,$publishercode,$note,$copyrightdate,$volumedesc,$publicationyear,$place,$isbn,$biblionumber)
327 Insert a new suggestion on database with value given on input arg.
332 my ($borrowernumber,$title,$author,$publishercode,$note,$copyrightdate,$volumedesc,$publicationyear,$place,$isbn,$biblionumber,$reason) = @_;
333 my $dbh = C4::Context->dbh;
335 INSERT INTO suggestions
336 (status,suggestedby,title,author,publishercode,note,copyrightdate,
337 volumedesc,publicationyear,place,isbn,biblionumber,reason)
338 VALUES ('ASKED',?,?,?,?,?,?,?,?,?,?,?,?)
340 my $sth = $dbh->prepare($query);
341 $sth->execute($borrowernumber,$title,$author,$publishercode,$note,$copyrightdate,$volumedesc,$publicationyear,$place,$isbn,$biblionumber,$reason);
346 &ModStatus($suggestionid,$status,$managedby,$biblionumber)
348 Modify the status (status can be 'ASKED', 'ACCEPTED', 'REJECTED', 'ORDERED')
349 and send a mail to notify the user that did the suggestion.
351 Note that there is no function to modify a suggestion : only the status can be modified, thus the name of the function.
356 my ($suggestionid,$status,$managedby,$biblionumber,$reason) = @_;
357 my $dbh = C4::Context->dbh;
363 SET status=?,managedby=?,biblionumber=?,reason=?
366 $sth = $dbh->prepare($query);
367 $sth->execute($status,$managedby,$biblionumber,$reason,$suggestionid);
371 SET status=?,managedby=?,reason=?
374 $sth = $dbh->prepare($query);
375 $sth->execute($status,$managedby,$reason,$suggestionid);
381 SET status=?,biblionumber=?,reason=?
384 $sth = $dbh->prepare($query);
385 $sth->execute($status,$biblionumber,$reason,$suggestionid);
390 SET status=?,reason=?
393 $sth = $dbh->prepare($query);
394 $sth->execute($status,$reason,$suggestionid);
397 # check mail sending.
399 SELECT suggestions.*,
400 boby.surname AS bysurname,
401 boby.firstname AS byfirstname,
402 boby.email AS byemail,
403 lib.surname AS libsurname,
404 lib.firstname AS libfirstname,
405 lib.email AS libemail
407 LEFT JOIN borrowers AS boby ON boby.borrowernumber=suggestedby
408 LEFT JOIN borrowers AS lib ON lib.borrowernumber=managedby
411 $sth = $dbh->prepare($queryMail);
412 $sth->execute($suggestionid);
413 my $emailinfo = $sth->fetchrow_hashref;
414 my $template = gettemplate("suggestion/mail_suggestion_$status.tmpl", "intranet", CGI->new());
417 byemail => $emailinfo->{byemail},
418 libemail => $emailinfo->{libemail},
419 status => $emailinfo->{status},
420 title => $emailinfo->{title},
421 author =>$emailinfo->{author},
422 libsurname => $emailinfo->{libsurname},
423 libfirstname => $emailinfo->{libfirstname},
424 byfirstname => $emailinfo->{byfirstname},
425 bysurname => $emailinfo->{bysurname},
426 reason => $emailinfo->{reason}
429 To => $emailinfo->{byemail},
430 From => $emailinfo->{libemail},
431 Subject => 'Koha suggestion',
432 Message => "".$template->output,
433 'Content-Type' => 'text/plain; charset="utf8"',
438 =head2 ConnectSuggestionAndBiblio
440 &ConnectSuggestionAndBiblio($suggestionid,$biblionumber)
442 connect a suggestion to an existing biblio
446 sub ConnectSuggestionAndBiblio {
447 my ($suggestionid,$biblionumber) = @_;
448 my $dbh=C4::Context->dbh;
454 my $sth = $dbh->prepare($query);
455 $sth->execute($biblionumber,$suggestionid);
460 &DelSuggestion($borrowernumber,$suggestionid)
462 Delete a suggestion. A borrower can delete a suggestion only if he is its owner.
467 my ($borrowernumber,$suggestionid,$type) = @_;
468 my $dbh = C4::Context->dbh;
469 # check that the suggestion comes from the suggestor
475 my $sth = $dbh->prepare($query);
476 $sth->execute($suggestionid);
477 my ($suggestedby) = $sth->fetchrow;
478 if ($type eq "intranet" || $suggestedby eq $borrowernumber ) {
480 DELETE FROM suggestions
483 $sth = $dbh->prepare($queryDelete);
484 my $suggestiondeleted=$sth->execute($suggestionid);
485 return $suggestiondeleted;
495 Koha Developement team <info@koha.org>