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 base 'Exporter'; # parent would be better there
34 &ConnectSuggestionAndBiblio
38 &GetSuggestionByStatus
39 &GetSuggestionFromBiblionumber
45 use C4::Dates qw(format_date_in_iso);
46 use vars qw($VERSION @ISA @EXPORT);
49 # set the version for version checking
57 &GetSuggestionByStatus
61 &ConnectSuggestionAndBiblio
62 &GetSuggestionFromBiblionumber
63 &ConnectSuggestionAndBiblio
67 &GetSuggestionByStatus
68 &GetSuggestionFromBiblionumber
77 C4::Suggestions - Some useful functions for dealings with aqorders.
85 The functions in this module deal with the aqorders in OPAC and in librarian interface
87 A suggestion is done in the OPAC. It has the status "ASKED"
89 When a librarian manages the suggestion, he can set the status to "REJECTED" or "ACCEPTED".
91 When the book is ordered, the suggestion status becomes "ORDERED"
93 When a book is ordered and arrived in the library, the status becomes "AVAILABLE"
95 All aqorders of a borrower can be seen by the borrower itself.
96 Suggestions done by other borrowers can be seen when not "AVAILABLE"
100 =head2 SearchSuggestion
102 (\@array) = &SearchSuggestion($suggestionhashref_to_search)
104 searches for a suggestion
107 C<\@array> : the aqorders found. Array of hash.
108 Note the status is stored twice :
109 * in the status field
110 * as parameter ( for example ASKED => 1, or REJECTED => 1) . This is for template & translation purposes.
114 sub SearchSuggestion {
116 my $dbh = C4::Context->dbh;
119 SELECT suggestions.*,
120 U1.branchcode AS branchcodesuggestedby,
121 B1.branchname AS branchnamesuggestedby,
122 U1.surname AS surnamesuggestedby,
123 U1.firstname AS firstnamesuggestedby,
124 U1.borrowernumber AS borrnumsuggestedby,
125 U1.categorycode AS categorycodesuggestedby,
126 C1.description AS categorydescriptionsuggestedby,
127 U2.branchcode AS branchcodemanagedby,
128 B2.branchname AS branchnamemanagedby,
129 U2.surname AS surnamemanagedby,
130 U2.firstname AS firstnamemanagedby,
131 U2.borrowernumber AS borrnummanagedby
133 LEFT JOIN borrowers AS U1 ON suggestedby=U1.borrowernumber
134 LEFT JOIN borrowers AS U2 ON managedby=U2.borrowernumber
135 LEFT JOIN categories AS C1 ON C1.categorycode = U1.categorycode
136 LEFT JOIN branches AS B1 ON B1.branchcode = U1.branchcode
137 LEFT JOIN branches AS B2 ON B2.branchcode = U2.branchcode
138 WHERE STATUS NOT IN ('CLAIMED')
140 if ( my $s = $$suggestion{$_} ) {
141 push @sql_params,'%'.$s.'%';
142 " and suggestions.$_ like ? ";
144 } qw( title author isbn publishercode collectiontitle )
147 my $userenv = C4::Context->userenv;
148 if (C4::Context->preference('IndependantBranches')) {
150 if (($userenv->{flags} % 2) != 1 && !$$suggestion{branchcode}){
151 push @sql_params,$$userenv{branch};
152 push @query,q{ and (branchcode = ? or branchcode ='')};
157 foreach my $field (grep { my $fieldname=$_;
158 any {$fieldname eq $_ } qw<
159 STATUS branchcode itemtype suggestedby managedby acceptedby
160 bookfundid biblionumber
163 if ($$suggestion{$field}){
164 push @sql_params,$$suggestion{$field};
165 push @query, " and suggestions.$field=?";
168 push @query, " and (suggestions.$field='' OR suggestions.$field IS NULL)";
172 $debug && warn "@query";
173 my $sth=$dbh->prepare("@query");
174 $sth->execute(@sql_params);
175 return ($sth->fetchall_arrayref({}));
180 \%sth = &GetSuggestion($ordernumber)
182 this function get the detail of the suggestion $ordernumber (input arg)
185 the result of the SQL query as a hash : $sth->fetchrow_hashref.
190 my ($ordernumber) = @_;
191 my $dbh = C4::Context->dbh;
197 my $sth = $dbh->prepare($query);
198 $sth->execute($ordernumber);
199 return($sth->fetchrow_hashref);
202 =head2 GetSuggestionFromBiblionumber
204 $ordernumber = &GetSuggestionFromBiblionumber($biblionumber)
206 Get a suggestion from it's biblionumber.
209 the id of the suggestion which is related to the biblionumber given on input args.
213 sub GetSuggestionFromBiblionumber {
214 my ($biblionumber) = @_;
220 my $dbh=C4::Context->dbh;
221 my $sth = $dbh->prepare($query);
222 $sth->execute($biblionumber);
223 my ($ordernumber) = $sth->fetchrow;
227 =head2 GetSuggestionByStatus
229 $aqorders = &GetSuggestionByStatus($status,[$branchcode])
231 Get a suggestion from it's status
234 all the suggestion with C<$status>
238 sub GetSuggestionByStatus {
240 my $branchcode = shift;
241 my $dbh = C4::Context->dbh;
242 my @sql_params=($status);
243 my $query = qq(SELECT suggestions.*,
244 U1.surname AS surnamesuggestedby,
245 U1.firstname AS firstnamesuggestedby,
246 U1.branchcode AS branchcodesuggestedby,
247 B1.branchname AS branchnamesuggestedby,
248 U1.borrowernumber AS borrnumsuggestedby,
249 U1.categorycode AS categorycodesuggestedby,
250 C1.description AS categorydescriptionsuggestedby,
251 U2.surname AS surnamemanagedby,
252 U2.firstname AS firstnamemanagedby,
253 U2.borrowernumber AS borrnummanagedby
255 LEFT JOIN borrowers AS U1 ON suggestedby=U1.borrowernumber
256 LEFT JOIN borrowers AS U2 ON managedby=U2.borrowernumber
257 LEFT JOIN categories AS C1 ON C1.categorycode=U1.categorycode
258 LEFT JOIN branches AS B1 on B1.branchcode = U1.branchcode
260 if (C4::Context->preference("IndependantBranches") || $branchcode) {
261 my $userenv = C4::Context->userenv;
263 unless ($userenv->{flags} % 2 == 1){
264 push @sql_params,$userenv->{branch};
265 $query .= " and (U1.branchcode = ? or U1.branchcode ='')";
269 push @sql_params,$branchcode;
270 $query .= " and (U1.branchcode = ? or U1.branchcode ='')";
274 my $sth = $dbh->prepare($query);
275 $sth->execute(@sql_params);
278 $results= $sth->fetchall_arrayref({});
282 =head2 CountSuggestion
284 &CountSuggestion($status)
286 Count the number of aqorders with the status given on input argument.
287 the arg status can be :
291 =item * ASKED : asked by the user, not dealed by the librarian
293 =item * ACCEPTED : accepted by the librarian, but not yet ordered
295 =item * REJECTED : rejected by the librarian (definitive status)
297 =item * ORDERED : ordered by the librarian (acquisition module)
302 the number of suggestion with this status.
306 sub CountSuggestion {
308 my $dbh = C4::Context->dbh;
310 if (C4::Context->preference("IndependantBranches")){
311 my $userenv = C4::Context->userenv;
312 if ($userenv->{flags} % 2 == 1){
318 $sth = $dbh->prepare($query);
319 $sth->execute($status);
324 FROM suggestions LEFT JOIN borrowers ON borrowers.borrowernumber=suggestions.suggestedby
326 AND (borrowers.branchcode='' OR borrowers.branchcode =?)
328 $sth = $dbh->prepare($query);
329 $sth->execute($status,$userenv->{branch});
338 $sth = $dbh->prepare($query);
339 $sth->execute($status);
341 my ($result) = $sth->fetchrow;
348 &NewSuggestion($suggestion);
350 Insert a new suggestion on database with value given on input arg.
355 my ($suggestion) = @_;
356 $suggestion->{STATUS}="ASKED" unless $suggestion->{STATUS};
357 return InsertInTable("suggestions",$suggestion);
362 &ModSuggestion($suggestion)
364 Modify the suggestion according to the hash passed by ref.
365 The hash HAS to contain suggestionid
366 Data not defined is not updated unless it is a note or sort1
367 Send a mail to notify the user that did the suggestion.
369 Note that there is no function to modify a suggestion.
375 my $status_update_table=UpdateInTable("suggestions", $suggestion);
376 # check mail sending.
377 if ($$suggestion{STATUS}){
378 my $letter=C4::Letters::getletter('suggestions',$$suggestion{STATUS});
380 my $enqueued = C4::Letters::EnqueueLetter({
382 borrowernumber=>$$suggestion{suggestedby},
383 suggestionid=>$$suggestion{suggestionid},
384 msg_transport_type=>'email'
386 if (!$enqueued){warn "can't enqueue letter $letter";}
389 return $status_update_table;
392 =head2 ConnectSuggestionAndBiblio
394 &ConnectSuggestionAndBiblio($ordernumber,$biblionumber)
396 connect a suggestion to an existing biblio
400 sub ConnectSuggestionAndBiblio {
401 my ($ordernumber,$biblionumber) = @_;
402 my $dbh=C4::Context->dbh;
408 my $sth = $dbh->prepare($query);
409 $sth->execute($biblionumber,$suggestionid);
414 &DelSuggestion($borrowernumber,$ordernumber)
416 Delete a suggestion. A borrower can delete a suggestion only if he is its owner.
421 my ($borrowernumber,$ordernumber,$type) = @_;
422 my $dbh = C4::Context->dbh;
423 # check that the suggestion comes from the suggestor
429 my $sth = $dbh->prepare($query);
430 $sth->execute($ordernumber);
431 my ($suggestedby) = $sth->fetchrow;
432 if ($type eq "intranet" || $suggestedby eq $borrowernumber ) {
434 DELETE FROM suggestions
437 $sth = $dbh->prepare($queryDelete);
438 my $suggestiondeleted=$sth->execute($suggestionid);
439 return $suggestiondeleted;
449 Koha Developement team <info@koha.org>