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 base 'Exporter'; # parent would be better there
31 &ConnectSuggestionAndBiblio
35 &GetSuggestionByStatus
36 &GetSuggestionFromBiblionumber
45 C4::Suggestions - Some useful functions for dealings with suggestions.
53 The functions in this module deal with the suggestions in OPAC and in librarian interface
55 A suggestion is done in the OPAC. It has the status "ASKED"
57 When a librarian manages the suggestion, he can set the status to "REJECTED" or "ACCEPTED".
59 When the book is ordered, the suggestion status becomes "ORDERED"
61 When a book is ordered and arrived in the library, the status becomes "AVAILABLE"
63 All suggestions of a borrower can be seen by the borrower itself.
64 Suggestions done by other borrowers can be seen when not "AVAILABLE"
68 =head2 SearchSuggestion
70 (\@array) = &SearchSuggestion($user,$author,$title,$publishercode,$status,$suggestedbyme,$branchcode)
72 searches for a suggestion
75 C<\@array> : the suggestions found. Array of hash.
76 Note the status is stored twice :
78 * as parameter ( for example ASKED => 1, or REJECTED => 1) . This is for template & translation purposes.
82 sub SearchSuggestion {
83 my ($suggestion,$suggestedbyme)=@_;
84 my $dbh = C4::Context->dbh;
87 q{ SELECT suggestions.*,
88 U1.branchcode AS branchcodesuggestedby,
89 U1.surname AS surnamesuggestedby,
90 U1.firstname AS firstnamesuggestedby,
91 U1.email AS emailsuggestedby,
92 U1.borrowernumber AS borrnumsuggestedby,
93 U2.surname AS surnamemanagedby,
94 U2.firstname AS firstnamemanagedby,
95 U2.email AS emailmanagedby,
96 U2.branchcode AS branchcodemanagedby,
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
101 WHERE STATUS NOT IN ('CLAIMED')
103 if ( my $s = $$suggestion{$_} ) {
104 push @sql_params,'%'.$s.'%';
105 " and suggestions.$_ like ? ";
107 } qw( title author isbn publishercode collectiontitle )
110 my $userenv = C4::Context->userenv;
111 if (C4::Context->preference('IndependantBranches')) {
113 if (($userenv->{flags} % 2) != 1 && !$$suggestion{branchcode}){
114 push @sql_params,$$userenv{branch};
115 push @query,q{ and (branchcode = ? or branchcode ='')};
120 if ($suggestedbyme) {
125 if ( my $number = $$userenv{number} ) {
126 push @sql_params, $number;
127 push @query, 'and suggestedby=?';
128 delete $$suggestion{suggestedby}
132 foreach my $field (grep {qw<
133 STATUS branchcode itemtype suggestedby managedby acceptedby
134 bookfundid biblionumber
137 # next unless $$suggestion{$field};
138 if ($$suggestion{$field}){
139 push @sql_params,$$suggestion{$field};
140 push @query, " and suggestions.$field=?";
143 push @query, " and (suggestions.$field='' OR suggestions.$field IS NULL)";
148 my $sth=$dbh->prepare("@query");
149 $sth->execute(@sql_params);
151 my $even=1; # the even variable is used to set even / odd lines, for highlighting
152 while ( my $data=$sth->fetchrow_hashref ){
153 $$data{$$data{STATUS}} = 1;
161 # $$data{$_} = format_date($$data{$_})
162 # foreach qw(suggestioncreatedon suggestionmanagedon suggestionacceptedon date);
163 push(@results,$data);
170 \%sth = &GetSuggestion($suggestionid)
172 this function get the detail of the suggestion $suggestionid (input arg)
175 the result of the SQL query as a hash : $sth->fetchrow_hashref.
180 my ($suggestionid) = @_;
181 my $dbh = C4::Context->dbh;
187 my $sth = $dbh->prepare($query);
188 $sth->execute($suggestionid);
189 return($sth->fetchrow_hashref);
192 =head2 GetSuggestionFromBiblionumber
194 $suggestionid = &GetSuggestionFromBiblionumber($dbh,$biblionumber)
196 Get a suggestion from it's biblionumber.
199 the id of the suggestion which is related to the biblionumber given on input args.
203 sub GetSuggestionFromBiblionumber {
204 my ($dbh,$biblionumber) = @_;
210 my $sth = $dbh->prepare($query);
211 $sth->execute($biblionumber);
212 my ($suggestionid) = $sth->fetchrow;
213 return $suggestionid;
216 =head2 GetSuggestionByStatus
218 $suggestions = &GetSuggestionByStatus($status,[$branchcode])
220 Get a suggestion from it's status
223 all the suggestion with C<$status>
227 sub GetSuggestionByStatus {
229 my $branchcode = shift;
230 my $dbh = C4::Context->dbh;
231 my @sql_params=($status);
233 SELECT suggestions.*,
234 U1.surname AS surnamesuggestedby,
235 U1.firstname AS firstnamesuggestedby,
236 U1.email AS emailsuggestedby,
237 U1.branchcode AS branchcodesuggestedby,
238 U1.borrowernumber AS borrnumsuggestedby,
239 U2.surname AS surnamemanagedby,
240 U2.firstname AS firstnamemanagedby,
241 U2.email AS emailmanagedby,
242 U2.borrowernumber AS borrnummanagedby
244 LEFT JOIN borrowers AS U1 ON suggestedby=U1.borrowernumber
245 LEFT JOIN borrowers AS U2 ON managedby=U2.borrowernumber
249 if (C4::Context->preference("IndependantBranches") || $branchcode) {
250 my $userenv = C4::Context->userenv;
252 unless ($userenv->{flags} % 2 == 1){
253 push @sql_params,$userenv->{branch};
254 push @query, q{ and (suggestions.branchcode = ? or suggestions.branchcode ='')};
258 push @sql_params,$branchcode;
259 push @query, q{ and (suggestions.branchcode = ? )};
263 my $sth = $dbh->prepare("@query");
264 $sth->execute(@sql_params);
267 $results= $sth->fetchall_arrayref({});
268 # map{$_->{date} = format_date($_->{date})} @$results;
272 =head2 CountSuggestion
274 &CountSuggestion($status)
276 Count the number of suggestions with the status given on input argument.
277 the arg status can be :
281 =item * ASKED : asked by the user, not dealed by the librarian
283 =item * ACCEPTED : accepted by the librarian, but not yet ordered
285 =item * CHECKED : checked by the librarian, but not yet ordered
287 =item * REJECTED : rejected by the librarian (definitive status)
289 =item * ORDERED : ordered by the librarian (acquisition module)
294 the number of suggestion with this status.
298 sub CountSuggestion {
300 my $dbh = C4::Context->dbh;
302 if (C4::Context->preference("IndependantBranches")){
303 my $userenv = C4::Context->userenv;
304 if ($userenv->{flags} % 2 == 1){
310 $sth = $dbh->prepare($query);
311 $sth->execute($status);
316 FROM suggestions LEFT JOIN borrowers ON borrowers.borrowernumber=suggestions.suggestedby
318 AND (borrowers.branchcode='' OR borrowers.branchcode =?)
320 $sth = $dbh->prepare($query);
321 $sth->execute($status,$userenv->{branch});
330 $sth = $dbh->prepare($query);
331 $sth->execute($status);
333 my ($result) = $sth->fetchrow;
340 &NewSuggestion($borrowernumber,$title,$author,$publishercode,$note,$copyrightdate,$volumedesc,$publicationyear,$place,$isbn,$biblionumber)
342 Insert a new suggestion on database with value given on input arg.
347 my ($suggestion) = @_;
348 my $dbh = C4::Context->dbh;
349 my @keys; my @values;
351 while ( my ($k,$v) = each %$suggestion ) {
354 push @keys , "$k = ?";
360 INSERT INTO suggestions
361 SET STATUS='ASKED', @keys
366 my $sth = $dbh->prepare($query);
367 $sth->execute(@values);
368 return $dbh->{'mysql_insertid'};
373 &ModSuggestion($suggestion)
375 Modify the suggestion according to the hash passed by ref.
376 The hash HAS to contain suggestionid
377 Data not defined is not updated unless it is a note or sort1
378 Send a mail to notify the user that did the suggestion.
380 Note that there is no function to modify a suggestion : only the status can be modified, thus the name of the function.
385 my ($suggestion) = @_;
386 my $dbh = C4::Context->dbh;
388 my $suggestionid=$$suggestion{'suggestionid'};
389 delete $$suggestion{'suggestionid'};
394 while ( my ($k,$v) = each %$suggestion ) {
395 next if (not $v and $k!~/sort1|note/);
396 #next if any { $_ eq $k } qw( sort1 note);
401 my $query = do { local $"=',';
410 $sth = $dbh->prepare($query);
411 my $result = $sth->execute(@values, $suggestionid);
412 # check mail sending.
413 if ($$suggestion{STATUS}){
415 SELECT suggestions.*,
416 boby.surname AS bysurname,
417 boby.firstname AS byfirstname,
418 boby.email AS byemail,
419 lib.surname AS libsurname,
420 lib.firstname AS libfirstname,
421 lib.email AS libemail
423 LEFT JOIN borrowers AS boby ON boby.borrowernumber=suggestedby
424 LEFT JOIN borrowers AS lib ON lib.borrowernumber=managedby
427 $sth = $dbh->prepare($queryMail);
428 $sth->execute($suggestionid);
429 my $emailinfo = $sth->fetchrow_hashref;
430 my $template = gettemplate(
431 "suggestion/mail_suggestion_$$suggestion{STATUS}.tmpl"
436 $template->param( %$emailinfo);
438 To => $emailinfo->{byemail},
439 From => $emailinfo->{libemail},
440 Subject => 'Koha suggestion',
441 Message => ''.$template->output,
442 'Content-Type' => 'text/plain; charset="utf8"',
448 =head2 ConnectSuggestionAndBiblio
450 &ConnectSuggestionAndBiblio($suggestionid,$biblionumber)
452 connect a suggestion to an existing biblio
456 sub ConnectSuggestionAndBiblio {
457 my ($suggestionid,$biblionumber) = @_;
458 my $dbh=C4::Context->dbh;
464 my $sth = $dbh->prepare($query);
465 return $sth->execute($biblionumber,$suggestionid);
470 &DelSuggestion($borrowernumber,$suggestionid)
472 Delete a suggestion. A borrower can delete a suggestion only if he is its owner.
477 my ($borrowernumber,$suggestionid,$type) = @_;
478 my $dbh = C4::Context->dbh;
479 # check that the suggestion comes from the suggestor
485 my $sth = $dbh->prepare($query);
486 $sth->execute($suggestionid);
487 my ($suggestedby) = $sth->fetchrow;
488 if ($type eq 'intranet' || $suggestedby eq $borrowernumber ) {
489 my $queryDelete = qq{
490 DELETE FROM suggestions
491 WHERE suggestionid=$suggestionid
493 return $dbh->do($queryDelete);
503 Koha Developement team <info@koha.org>