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.surname AS surnamesuggestedby,
93 U1.firstname AS firstnamesuggestedby,
94 U1.borrowernumber AS borrnumsuggestedby,
95 U2.surname AS surnamemanagedby,
96 U2.firstname AS firstnamemanagedby,
97 U2.borrowernumber AS borrnummanagedby
99 LEFT JOIN borrowers AS U1 ON suggestedby=U1.borrowernumber
100 LEFT JOIN borrowers AS U2 ON managedby=U2.borrowernumber
105 push @sql_params,"%".$author."%";
106 $query .= " and author like ?";
109 push @sql_params,"%".$title."%";
110 $query .= " and suggestions.title like ?";
112 if ($publishercode) {
113 push @sql_params,"%".$publishercode."%";
114 $query .= " and publishercode like ?";
116 if (C4::Context->preference("IndependantBranches") || $branchcode) {
117 my $userenv = C4::Context->userenv;
119 unless ($userenv->{flags} == 1){
120 push @sql_params,$userenv->{branch};
121 $query .= " and (U1.branchcode = ? or U1.branchcode ='')";
125 push @sql_params,$branchcode;
126 $query .= " and (U1.branchcode = ? or U1.branchcode ='')";
130 push @sql_params,$status;
131 $query .= " and status=?";
133 if ($suggestedbyme) {
134 unless ($suggestedbyme eq -1) {
135 push @sql_params,$user;
136 $query .= " and suggestedby=?";
139 $query .= " and managedby is NULL";
141 my $sth=$dbh->prepare($query);
142 $sth->execute(@sql_params);
144 my $even=1; # the even variable is used to set even / odd lines, for highlighting
145 while (my $data=$sth->fetchrow_hashref){
146 $data->{$data->{STATUS}} = 1;
153 # $data->{date} = format_date($data->{date});
154 push(@results,$data);
161 \%sth = &GetSuggestion($suggestionid)
163 this function get the detail of the suggestion $suggestionid (input arg)
166 the result of the SQL query as a hash : $sth->fetchrow_hashref.
171 my ($suggestionid) = @_;
172 my $dbh = C4::Context->dbh;
178 my $sth = $dbh->prepare($query);
179 $sth->execute($suggestionid);
180 return($sth->fetchrow_hashref);
183 =head2 GetSuggestionFromBiblionumber
185 $suggestionid = &GetSuggestionFromBiblionumber($dbh,$biblionumber)
187 Get a suggestion from it's biblionumber.
190 the id of the suggestion which is related to the biblionumber given on input args.
194 sub GetSuggestionFromBiblionumber {
195 my ($dbh,$biblionumber) = @_;
201 my $sth = $dbh->prepare($query);
202 $sth->execute($biblionumber);
203 my ($suggestionid) = $sth->fetchrow;
204 return $suggestionid;
207 =head2 GetSuggestionByStatus
209 $suggestions = &GetSuggestionByStatus($status,[$branchcode])
211 Get a suggestion from it's status
214 all the suggestion with C<$status>
218 sub GetSuggestionByStatus {
220 my $branchcode = shift;
221 my $dbh = C4::Context->dbh;
222 my @sql_params=($status);
223 my $query = qq(SELECT suggestions.*,
224 U1.surname AS surnamesuggestedby,
225 U1.firstname AS firstnamesuggestedby,
226 U1.borrowernumber AS borrnumsuggestedby,
227 U2.surname AS surnamemanagedby,
228 U2.firstname AS firstnamemanagedby,
229 U2.borrowernumber AS borrnummanagedby
231 LEFT JOIN borrowers AS U1 ON suggestedby=U1.borrowernumber
232 LEFT JOIN borrowers AS U2 ON managedby=U2.borrowernumber
234 if (C4::Context->preference("IndependantBranches") || $branchcode) {
235 my $userenv = C4::Context->userenv;
237 unless ($userenv->{flags} == 1){
238 push @sql_params,$userenv->{branch};
239 $query .= " and (U1.branchcode = ? or U1.branchcode ='')";
243 push @sql_params,$branchcode;
244 $query .= " and (U1.branchcode = ? or U1.branchcode ='')";
248 my $sth = $dbh->prepare($query);
249 $sth->execute(@sql_params);
252 $results= $sth->fetchall_arrayref({});
253 # map{$_->{date} = format_date($_->{date})} @$results;
257 =head2 CountSuggestion
259 &CountSuggestion($status)
261 Count the number of suggestions with the status given on input argument.
262 the arg status can be :
266 =item * ASKED : asked by the user, not dealed by the librarian
268 =item * ACCEPTED : accepted by the librarian, but not yet ordered
270 =item * REJECTED : rejected by the librarian (definitive status)
272 =item * ORDERED : ordered by the librarian (acquisition module)
277 the number of suggestion with this status.
281 sub CountSuggestion {
283 my $dbh = C4::Context->dbh;
285 if (C4::Context->preference("IndependantBranches")){
286 my $userenv = C4::Context->userenv;
287 if ($userenv->{flags} == 1){
293 $sth = $dbh->prepare($query);
294 $sth->execute($status);
299 FROM suggestions LEFT JOIN borrowers ON borrowers.borrowernumber=suggestions.suggestedby
301 AND (borrowers.branchcode='' OR borrowers.branchcode =?)
303 $sth = $dbh->prepare($query);
304 $sth->execute($status,$userenv->{branch});
313 $sth = $dbh->prepare($query);
314 $sth->execute($status);
316 my ($result) = $sth->fetchrow;
323 &NewSuggestion($borrowernumber,$title,$author,$publishercode,$note,$copyrightdate,$volumedesc,$publicationyear,$place,$isbn,$biblionumber)
325 Insert a new suggestion on database with value given on input arg.
330 my ($borrowernumber,$title,$author,$publishercode,$note,$copyrightdate,$volumedesc,$publicationyear,$place,$isbn,$biblionumber,$reason) = @_;
331 my $dbh = C4::Context->dbh;
333 INSERT INTO suggestions
334 (status,suggestedby,title,author,publishercode,note,copyrightdate,
335 volumedesc,publicationyear,place,isbn,biblionumber,reason)
336 VALUES ('ASKED',?,?,?,?,?,?,?,?,?,?,?,?)
338 my $sth = $dbh->prepare($query);
339 $sth->execute($borrowernumber,$title,$author,$publishercode,$note,$copyrightdate,$volumedesc,$publicationyear,$place,$isbn,$biblionumber,$reason);
344 &ModStatus($suggestionid,$status,$managedby,$biblionumber)
346 Modify the status (status can be 'ASKED', 'ACCEPTED', 'REJECTED', 'ORDERED')
347 and send a mail to notify the user that did the suggestion.
349 Note that there is no function to modify a suggestion : only the status can be modified, thus the name of the function.
354 my ($suggestionid,$status,$managedby,$biblionumber,$reason) = @_;
355 my $dbh = C4::Context->dbh;
361 SET status=?,managedby=?,biblionumber=?,reason=?
364 $sth = $dbh->prepare($query);
365 $sth->execute($status,$managedby,$biblionumber,$reason,$suggestionid);
369 SET status=?,managedby=?,reason=?
372 $sth = $dbh->prepare($query);
373 $sth->execute($status,$managedby,$reason,$suggestionid);
379 SET status=?,biblionumber=?,reason=?
382 $sth = $dbh->prepare($query);
383 $sth->execute($status,$biblionumber,$reason,$suggestionid);
388 SET status=?,reason=?
391 $sth = $dbh->prepare($query);
392 $sth->execute($status,$reason,$suggestionid);
395 # check mail sending.
397 SELECT suggestions.*,
398 boby.surname AS bysurname,
399 boby.firstname AS byfirstname,
400 boby.email AS byemail,
401 lib.surname AS libsurname,
402 lib.firstname AS libfirstname,
403 lib.email AS libemail
405 LEFT JOIN borrowers AS boby ON boby.borrowernumber=suggestedby
406 LEFT JOIN borrowers AS lib ON lib.borrowernumber=managedby
409 $sth = $dbh->prepare($queryMail);
410 $sth->execute($suggestionid);
411 my $emailinfo = $sth->fetchrow_hashref;
412 my $template = gettemplate("suggestion/mail_suggestion_$status.tmpl", "intranet", CGI->new());
415 byemail => $emailinfo->{byemail},
416 libemail => $emailinfo->{libemail},
417 status => $emailinfo->{status},
418 title => $emailinfo->{title},
419 author =>$emailinfo->{author},
420 libsurname => $emailinfo->{libsurname},
421 libfirstname => $emailinfo->{libfirstname},
422 byfirstname => $emailinfo->{byfirstname},
423 bysurname => $emailinfo->{bysurname},
424 reason => $emailinfo->{reason}
427 To => $emailinfo->{byemail},
428 From => $emailinfo->{libemail},
429 Subject => 'Koha suggestion',
430 Message => "".$template->output,
431 'Content-Type' => 'text/plain; charset="utf8"',
436 =head2 ConnectSuggestionAndBiblio
438 &ConnectSuggestionAndBiblio($suggestionid,$biblionumber)
440 connect a suggestion to an existing biblio
444 sub ConnectSuggestionAndBiblio {
445 my ($suggestionid,$biblionumber) = @_;
446 my $dbh=C4::Context->dbh;
452 my $sth = $dbh->prepare($query);
453 $sth->execute($biblionumber,$suggestionid);
458 &DelSuggestion($borrowernumber,$suggestionid)
460 Delete a suggestion. A borrower can delete a suggestion only if he is its owner.
465 my ($borrowernumber,$suggestionid,$type) = @_;
466 my $dbh = C4::Context->dbh;
467 # check that the suggestion comes from the suggestor
473 my $sth = $dbh->prepare($query);
474 $sth->execute($suggestionid);
475 my ($suggestedby) = $sth->fetchrow;
476 if ($type eq "intranet" || $suggestedby eq $borrowernumber ) {
478 DELETE FROM suggestions
481 $sth = $dbh->prepare($queryDelete);
482 my $suggestiondeleted=$sth->execute($suggestionid);
483 return $suggestiondeleted;
493 Koha Developement team <info@koha.org>