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