fixing help feature with new template structure
[koha.git] / C4 / Suggestions.pm
1 package C4::Suggestions;
2
3 # Copyright 2000-2002 Katipo Communications
4 #
5 # This file is part of Koha.
6 #
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
10 # version.
11 #
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.
15 #
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
19
20 # $Id$
21
22 use strict;
23 require Exporter;
24 use C4::Context;
25 use C4::Output;
26 use C4::Date;
27 use Mail::Sendmail;
28 use vars qw($VERSION @ISA @EXPORT);
29
30 # set the version for version checking
31 $VERSION = do { my @v = '$Revision$' =~ /\d+/g;
32   shift(@v) . "." . join("_", map {sprintf "%03d", $_ } @v); };
33
34 =head1 NAME
35
36 C4::Suggestions - Some useful functions for dealings with suggestions.
37
38 =head1 SYNOPSIS
39
40 use C4::Suggestions;
41
42 =head1 DESCRIPTION
43
44 The functions in this module deal with the suggestions in OPAC and in librarian interface
45
46 A suggestion is done in the OPAC. It has the status "ASKED"
47
48 When a librarian manages the suggestion, he can set the status to "REJECTED" or "ACCEPTED".
49
50 When the book is ordered, the suggestion status becomes "ORDERED"
51
52 When a book is ordered and arrived in the library, the status becomes "AVAILABLE"
53
54 All suggestions of a borrower can be seen by the borrower itself.
55 Suggestions done by other borrowers can be seen when not "AVAILABLE"
56
57 =head1 FUNCTIONS
58
59 =cut
60
61 @ISA = qw(Exporter);
62 @EXPORT = qw(
63     &NewSuggestion
64     &SearchSuggestion
65     &GetSuggestion
66     &GetSuggestionByStatus
67     &DelSuggestion
68     &CountSuggestion
69     &ModStatus
70     &ConnectSuggestionAndBiblio
71     &GetSuggestionFromBiblionumber
72  );
73
74 =head2 SearchSuggestion
75
76 (\@array) = &SearchSuggestion($user,$author,$title,$publishercode,$status,$suggestedbyme)
77
78 searches for a suggestion
79
80 return :
81 C<\@array> : the suggestions found. Array of hash.
82 Note the status is stored twice :
83 * in the status field
84 * as parameter ( for example ASKED => 1, or REJECTED => 1) . This is for template & translation purposes.
85
86 =cut
87
88 sub SearchSuggestion  {
89     my ($user,$author,$title,$publishercode,$status,$suggestedbyme)=@_;
90     my $dbh = C4::Context->dbh;
91     my $query = "
92     SELECT suggestions.*,
93         U1.surname   AS surnamesuggestedby,
94         U1.firstname AS firstnamesuggestedby,
95         U2.surname   AS surnamemanagedby,
96         U2.firstname AS firstnamemanagedby
97     FROM suggestions
98     LEFT JOIN borrowers AS U1 ON suggestedby=U1.borrowernumber
99     LEFT JOIN borrowers AS U2 ON managedby=U2.borrowernumber
100     WHERE 1=1 ";
101
102     my @sql_params;
103     if ($author) {
104        push @sql_params,"%".$author."%";
105        $query .= " and author like ?";
106     }
107     if ($title) {
108         push @sql_params,"%".$title."%";
109         $query .= " and suggestions.title like ?";
110     }
111     if ($publishercode) {
112         push @sql_params,"%".$publishercode."%";
113         $query .= " and publishercode like ?";
114     }
115     if (C4::Context->preference("IndependantBranches")) {
116         my $userenv = C4::Context->userenv;
117         if ($userenv) {
118             unless ($userenv->{flags} == 1){
119                 push @sql_params,$userenv->{branch};
120                 $query .= " and (U1.branchcode = ? or U1.branchcode ='')";
121             }
122         }
123     }
124     if ($status) {
125         push @sql_params,$status;
126         $query .= " and status=?";
127     }
128     if ($suggestedbyme) {
129         unless ($suggestedbyme eq -1) {
130             push @sql_params,$user;
131             $query .= " and suggestedby=?";
132         }
133     } else {
134         $query .= " and managedby is NULL";
135     }
136     my $sth=$dbh->prepare($query);
137     $sth->execute(@sql_params);
138     my @results;
139     my $even=1; # the even variable is used to set even / odd lines, for highlighting
140     while (my $data=$sth->fetchrow_hashref){
141         $data->{$data->{STATUS}} = 1;
142         if ($even) {
143             $even=0;
144             $data->{even}=1;
145         } else {
146             $even=1;
147         }
148         push(@results,$data);
149     }
150     return (\@results);
151 }
152
153 =head2 GetSuggestion
154
155 \%sth = &GetSuggestion($suggestionid)
156
157 this function get the detail of the suggestion $suggestionid (input arg)
158
159 return :
160     the result of the SQL query as a hash : $sth->fetchrow_hashref.
161
162 =cut
163
164 sub GetSuggestion {
165     my ($suggestionid) = @_;
166     my $dbh = C4::Context->dbh;
167     my $query = "
168         SELECT *
169         FROM   suggestions
170         WHERE  suggestionid=?
171     ";
172     my $sth = $dbh->prepare($query);
173     $sth->execute($suggestionid);
174     return($sth->fetchrow_hashref);
175 }
176
177 =head2 GetSuggestionFromBiblionumber
178
179 $suggestionid = &GetSuggestionFromBiblionumber($dbh,$biblionumber)
180
181 Get a suggestion from it's biblionumber.
182
183 return :
184 the id of the suggestion which is related to the biblionumber given on input args.
185
186 =cut
187
188 sub GetSuggestionFromBiblionumber {
189     my ($dbh,$biblionumber) = @_;
190     my $query = qq|
191         SELECT suggestionid
192         FROM   suggestions
193         WHERE  biblionumber=?
194     |;
195     my $sth = $dbh->prepare($query);
196     $sth->execute($biblionumber);
197     my ($suggestionid) = $sth->fetchrow;
198     return $suggestionid;
199 }
200
201 =head2 GetSuggestionByStatus
202
203 $suggestions = &GetSuggestionByStatus($status)
204
205 Get a suggestion from it's status
206
207 return :
208 all the suggestion with C<$status>
209
210 =cut
211
212 sub GetSuggestionByStatus {
213     my $status = shift;
214     my $dbh = C4::Context->dbh;
215     my $query = "SELECT suggestions.*,
216                         U1.surname   AS surnamesuggestedby,
217                         U1.firstname AS firstnamesuggestedby,
218                         U2.surname   AS surnamemanagedby,
219                         U2.firstname AS firstnamemanagedby
220                         FROM suggestions
221                         LEFT JOIN borrowers AS U1 ON suggestedby=U1.borrowernumber
222                         LEFT JOIN borrowers AS U2 ON managedby=U2.borrowernumber
223                         WHERE status = ?
224                         ";
225     my $sth = $dbh->prepare($query);
226     $sth->execute($status);
227     
228     my @results;
229     while(my $data = $sth->fetchrow_hashref){
230         $data->{date} = format_date($data->{date});
231         push @results,$data;
232     }
233     return \@results;
234 }
235
236 =head2 CountSuggestion
237
238 &CountSuggestion($status)
239
240 Count the number of suggestions with the status given on input argument.
241 the arg status can be :
242
243 =over 2
244
245 =item * ASKED : asked by the user, not dealed by the librarian
246
247 =item * ACCEPTED : accepted by the librarian, but not yet ordered
248
249 =item * REJECTED : rejected by the librarian (definitive status)
250
251 =item * ORDERED : ordered by the librarian (acquisition module)
252
253 =back
254
255 return :
256 the number of suggestion with this status.
257
258 =cut
259
260 sub CountSuggestion {
261     my ($status) = @_;
262     my $dbh = C4::Context->dbh;
263     my $sth;
264     if (C4::Context->preference("IndependantBranches")){
265         my $userenv = C4::Context->userenv;
266         if ($userenv->{flags} == 1){
267             my $query = qq |
268                 SELECT count(*)
269                 FROM   suggestions
270                 WHERE  status=?
271             |;
272             $sth = $dbh->prepare($query);
273             $sth->execute($status);
274         }
275         else {
276             my $query = qq |
277                 SELECT count(*)
278                 FROM suggestions,borrowers
279                 WHERE status=?
280                 AND borrowers.borrowernumber=suggestions.suggestedby
281                 AND (borrowers.branchcode='' OR borrowers.branchcode =?)
282             |;
283             $sth = $dbh->prepare($query);
284             $sth->execute($status,$userenv->{branch});
285         }
286     }
287     else {
288         my $query = qq |
289             SELECT count(*)
290             FROM suggestions
291             WHERE status=?
292         |;
293          $sth = $dbh->prepare($query);
294         $sth->execute($status);
295     }
296     my ($result) = $sth->fetchrow;
297     return $result;
298 }
299
300 =head2 NewSuggestion
301
302
303 &NewSuggestion($borrowernumber,$title,$author,$publishercode,$note,$copyrightdate,$volumedesc,$publicationyear,$place,$isbn,$biblionumber)
304
305 Insert a new suggestion on database with value given on input arg.
306
307 =cut
308
309 sub NewSuggestion {
310     my ($borrowernumber,$title,$author,$publishercode,$note,$copyrightdate,$volumedesc,$publicationyear,$place,$isbn,$biblionumber,$reason) = @_;
311     my $dbh = C4::Context->dbh;
312     my $query = qq |
313         INSERT INTO suggestions
314             (status,suggestedby,title,author,publishercode,note,copyrightdate,
315             volumedesc,publicationyear,place,isbn,biblionumber,reason)
316         VALUES ('ASKED',?,?,?,?,?,?,?,?,?,?,?,?)
317     |;
318     my $sth = $dbh->prepare($query);
319     $sth->execute($borrowernumber,$title,$author,$publishercode,$note,$copyrightdate,$volumedesc,$publicationyear,$place,$isbn,$biblionumber,$reason);
320 }
321
322 =head2 ModStatus
323
324 &ModStatus($suggestionid,$status,$managedby,$biblionumber)
325
326 Modify the status (status can be 'ASKED', 'ACCEPTED', 'REJECTED', 'ORDERED')
327 and send a mail to notify the user that did the suggestion.
328
329 Note that there is no function to modify a suggestion : only the status can be modified, thus the name of the function.
330
331 =cut
332
333 sub ModStatus {
334     my ($suggestionid,$status,$managedby,$biblionumber,$reason) = @_;
335     my $dbh = C4::Context->dbh;
336     my $sth;
337     if ($managedby>0) {
338         if ($biblionumber) {
339         my $query = qq|
340             UPDATE suggestions
341             SET    status=?,managedby=?,biblionumber=?,reason=?
342             WHERE  suggestionid=?
343         |;
344         $sth = $dbh->prepare($query);
345         $sth->execute($status,$managedby,$biblionumber,$reason,$suggestionid);
346         } else {
347             my $query = qq|
348                 UPDATE suggestions
349                 SET    status=?,managedby=?,reason=?
350                 WHERE  suggestionid=?
351             |;
352             $sth = $dbh->prepare($query);
353             $sth->execute($status,$managedby,$reason,$suggestionid);
354         }
355    } else {
356         if ($biblionumber) {
357             my $query = qq|
358                 UPDATE suggestions
359                 SET    status=?,biblionumber=?,reason=?
360                 WHERE  suggestionid=?
361             |;
362             $sth = $dbh->prepare($query);
363             $sth->execute($status,$biblionumber,$reason,$suggestionid);
364         }
365         else {
366             my $query = qq|
367                 UPDATE suggestions
368                 SET    status=?,reason=?
369                 WHERE  suggestionid=?
370             |;
371             $sth = $dbh->prepare($query);
372             $sth->execute($status,$reason,$suggestionid);
373         }
374     }
375     # check mail sending.
376     my $queryMail = "
377         SELECT suggestions.*,
378             boby.surname AS bysurname,
379             boby.firstname AS byfirstname,
380             boby.email AS byemail,
381             lib.surname AS libsurname,
382             lib.firstname AS libfirstname,
383             lib.email AS libemail
384         FROM suggestions
385             LEFT JOIN borrowers AS boby ON boby.borrowernumber=suggestedby
386             LEFT JOIN borrowers AS lib ON lib.borrowernumber=managedby
387         WHERE suggestionid=?
388     ";
389     $sth = $dbh->prepare($queryMail);
390     $sth->execute($suggestionid);
391     my $emailinfo = $sth->fetchrow_hashref;
392     my $template = gettemplate("suggestion/mail_suggestion_$status.tmpl","intranet");
393
394     $template->param(
395         byemail => $emailinfo->{byemail},
396         libemail => $emailinfo->{libemail},
397         status => $emailinfo->{status},
398         title => $emailinfo->{title},
399         author =>$emailinfo->{author},
400         libsurname => $emailinfo->{libsurname},
401         libfirstname => $emailinfo->{libfirstname},
402         byfirstname => $emailinfo->{byfirstname},
403         bysurname => $emailinfo->{bysurname},
404         reason => $emailinfo->{reason}
405     );
406     my %mail = (
407         To => $emailinfo->{byemail},
408         From => $emailinfo->{libemail},
409         Subject => 'Koha suggestion',
410         Message => "".$template->output
411     );
412     sendmail(%mail);
413 }
414
415 =head2 ConnectSuggestionAndBiblio
416
417 &ConnectSuggestionAndBiblio($suggestionid,$biblionumber)
418
419 connect a suggestion to an existing biblio
420
421 =cut
422
423 sub ConnectSuggestionAndBiblio {
424     my ($suggestionid,$biblionumber) = @_;
425     my $dbh=C4::Context->dbh;
426     my $query = "
427         UPDATE suggestions
428         SET    biblionumber=?
429         WHERE  suggestionid=?
430     ";
431     my $sth = $dbh->prepare($query);
432     $sth->execute($biblionumber,$suggestionid);
433 }
434
435 =head2 DelSuggestion
436
437 &DelSuggestion($borrowernumber,$suggestionid)
438
439 Delete a suggestion. A borrower can delete a suggestion only if he is its owner.
440
441 =cut
442
443 sub DelSuggestion {
444     my ($borrowernumber,$suggestionid) = @_;
445     my $dbh = C4::Context->dbh;
446     # check that the suggestion comes from the suggestor
447     my $query = "
448         SELECT suggestedby
449         FROM   suggestions
450         WHERE  suggestionid=?
451     ";
452     my $sth = $dbh->prepare($query);
453     $sth->execute($suggestionid);
454     my ($suggestedby) = $sth->fetchrow;
455     if ($suggestedby eq $borrowernumber) {
456         my $queryDelete = "
457             DELETE FROM suggestions
458             WHERE suggestionid=?
459         ";
460         $sth = $dbh->prepare($queryDelete);
461         $sth->execute($suggestionid);
462     }
463 }
464
465 1;
466 __END__
467
468
469 =head1 AUTHOR
470
471 Koha Developement team <info@koha.org>
472
473 =cut
474