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
26 use C4::Dates qw(format_date);
27 use C4::SQLHelper qw(:all);
30 use List::MoreUtils qw(any);
31 use vars qw($VERSION @ISA @EXPORT);
34 # set the version for version checking
42 &GetSuggestionByStatus
46 &ConnectSuggestionAndBiblio
47 &GetSuggestionFromBiblionumber
53 C4::Suggestions - Some useful functions for dealings with suggestions.
61 The functions in this module deal with the suggestions in OPAC and in librarian interface
63 A suggestion is done in the OPAC. It has the status "ASKED"
65 When a librarian manages the suggestion, he can set the status to "REJECTED" or "ACCEPTED".
67 When the book is ordered, the suggestion status becomes "ORDERED"
69 When a book is ordered and arrived in the library, the status becomes "AVAILABLE"
71 All suggestions of a borrower can be seen by the borrower itself.
72 Suggestions done by other borrowers can be seen when not "AVAILABLE"
76 =head2 SearchSuggestion
78 (\@array) = &SearchSuggestion($suggestionhashref_to_search)
80 searches for a suggestion
83 C<\@array> : the suggestions found. Array of hash.
84 Note the status is stored twice :
86 * as parameter ( for example ASKED => 1, or REJECTED => 1) . This is for template & translation purposes.
90 sub SearchSuggestion {
92 my $dbh = C4::Context->dbh;
96 U1.branchcode AS branchcodesuggestedby,
97 B1.branchname AS branchnamesuggestedby,
98 U1.surname AS surnamesuggestedby,
99 U1.firstname AS firstnamesuggestedby,
100 U1.borrowernumber AS borrnumsuggestedby,
101 U1.categorycode AS categorycodesuggestedby,
102 C1.description AS categorydescriptionsuggestedby,
103 U2.branchcode AS branchcodemanagedby,
104 B2.branchname AS branchnamemanagedby,
105 U2.surname AS surnamemanagedby,
106 U2.firstname AS firstnamemanagedby,
107 U2.borrowernumber AS borrnummanagedby
109 LEFT JOIN borrowers AS U1 ON suggestedby=U1.borrowernumber
110 LEFT JOIN borrowers AS U2 ON managedby=U2.borrowernumber
111 LEFT JOIN categories AS C1 ON C1.categorycode = U1.categorycode
112 LEFT JOIN branches AS B1 ON B1.branchcode = U1.branchcode
113 LEFT JOIN branches AS B2 ON B2.branchcode = U2.branchcode
114 WHERE STATUS NOT IN ('CLAIMED')
116 if ( my $s = $$suggestion{$_} ) {
117 push @sql_params,'%'.$s.'%';
118 " and suggestions.$_ like ? ";
120 } qw( title author isbn publishercode collectiontitle )
123 my $userenv = C4::Context->userenv;
124 if (C4::Context->preference('IndependantBranches')) {
126 if (($userenv->{flags} % 2) != 1 && !$$suggestion{branchcode}){
127 push @sql_params,$$userenv{branch};
128 push @query,q{ and (branchcode = ? or branchcode ='')};
133 foreach my $field (grep { my $fieldname=$_;
134 any {$fieldname eq $_ } qw<
135 STATUS branchcode itemtype suggestedby managedby acceptedby
136 bookfundid biblionumber
139 if ($$suggestion{$field}){
140 push @sql_params,$$suggestion{$field};
141 push @query, " and suggestions.$field=?";
144 push @query, " and (suggestions.$field='' OR suggestions.$field IS NULL)";
148 $debug && warn "@query";
149 my $sth=$dbh->prepare("@query");
150 $sth->execute(@sql_params);
151 return ($sth->fetchall_arrayref({}));
156 \%sth = &GetSuggestion($suggestionid)
158 this function get the detail of the suggestion $suggestionid (input arg)
161 the result of the SQL query as a hash : $sth->fetchrow_hashref.
166 my ($suggestionid) = @_;
167 my $dbh = C4::Context->dbh;
173 my $sth = $dbh->prepare($query);
174 $sth->execute($suggestionid);
175 return($sth->fetchrow_hashref);
178 =head2 GetSuggestionFromBiblionumber
180 $suggestionid = &GetSuggestionFromBiblionumber($dbh,$biblionumber)
182 Get a suggestion from it's biblionumber.
185 the id of the suggestion which is related to the biblionumber given on input args.
189 sub GetSuggestionFromBiblionumber {
190 my ($dbh,$biblionumber) = @_;
196 my $sth = $dbh->prepare($query);
197 $sth->execute($biblionumber);
198 my ($suggestionid) = $sth->fetchrow;
199 return $suggestionid;
202 =head2 GetSuggestionByStatus
204 $suggestions = &GetSuggestionByStatus($status,[$branchcode])
206 Get a suggestion from it's status
209 all the suggestion with C<$status>
213 sub GetSuggestionByStatus {
215 my $branchcode = shift;
216 my $dbh = C4::Context->dbh;
217 my @sql_params=($status);
218 my $query = qq(SELECT suggestions.*,
219 U1.surname AS surnamesuggestedby,
220 U1.firstname AS firstnamesuggestedby,
221 U1.branchcode AS branchcodesuggestedby,
222 B1.branchname AS branchnamesuggestedby,
223 U1.borrowernumber AS borrnumsuggestedby,
224 U1.categorycode AS categorycodesuggestedby,
225 C1.description AS categorydescriptionsuggestedby,
226 U2.surname AS surnamemanagedby,
227 U2.firstname AS firstnamemanagedby,
228 U2.borrowernumber AS borrnummanagedby
230 LEFT JOIN borrowers AS U1 ON suggestedby=U1.borrowernumber
231 LEFT JOIN borrowers AS U2 ON managedby=U2.borrowernumber
232 LEFT JOIN categories AS C1 ON C1.categorycode=U1.categorycode
233 LEFT JOIN branches AS B1 on B1.branchcode = U1.branchcode
235 if (C4::Context->preference("IndependantBranches") || $branchcode) {
236 my $userenv = C4::Context->userenv;
238 unless ($userenv->{flags} % 2 == 1){
239 push @sql_params,$userenv->{branch};
240 $query .= " and (U1.branchcode = ? or U1.branchcode ='')";
244 push @sql_params,$branchcode;
245 $query .= " and (U1.branchcode = ? or U1.branchcode ='')";
249 my $sth = $dbh->prepare($query);
250 $sth->execute(@sql_params);
253 $results= $sth->fetchall_arrayref({});
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} % 2 == 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($suggestion)
325 Insert a new suggestion on database with value given on input arg.
330 my ($suggestion) = @_;
331 $suggestion->{STATUS}="ASKED" unless $suggestion->{STATUS};
332 return InsertInTable("suggestions",$suggestion);
337 &ModSuggestion($suggestion)
339 Modify the suggestion according to the hash passed by ref.
340 The hash HAS to contain suggestionid
341 Data not defined is not updated unless it is a note or sort1
342 Send a mail to notify the user that did the suggestion.
344 Note that there is no function to modify a suggestion.
350 my $status_update_table=UpdateInTable("suggestions", $suggestion);
351 # check mail sending.
352 if ($$suggestion{STATUS}){
353 my $letter=C4::Letters::getletter('suggestions',$$suggestion{STATUS});
355 my $enqueued = C4::Letters::EnqueueLetter({
357 borrowernumber=>$$suggestion{suggestedby},
358 suggestionid=>$$suggestion{suggestionid},
359 msg_transport_type=>'email'
361 if (!$enqueued){warn "can't enqueue letter $letter";}
364 return $status_update_table;
367 =head2 ConnectSuggestionAndBiblio
369 &ConnectSuggestionAndBiblio($suggestionid,$biblionumber)
371 connect a suggestion to an existing biblio
375 sub ConnectSuggestionAndBiblio {
376 my ($suggestionid,$biblionumber) = @_;
377 my $dbh=C4::Context->dbh;
383 my $sth = $dbh->prepare($query);
384 $sth->execute($biblionumber,$suggestionid);
389 &DelSuggestion($borrowernumber,$suggestionid)
391 Delete a suggestion. A borrower can delete a suggestion only if he is its owner.
396 my ($borrowernumber,$suggestionid,$type) = @_;
397 my $dbh = C4::Context->dbh;
398 # check that the suggestion comes from the suggestor
404 my $sth = $dbh->prepare($query);
405 $sth->execute($suggestionid);
406 my ($suggestedby) = $sth->fetchrow;
407 if ($type eq "intranet" || $suggestedby eq $borrowernumber ) {
409 DELETE FROM suggestions
412 $sth = $dbh->prepare($queryDelete);
413 my $suggestiondeleted=$sth->execute($suggestionid);
414 return $suggestiondeleted;
424 Koha Developement team <info@koha.org>