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
66 &GetSuggestionByStatus
67 &GetSuggestionFromBiblionumber
76 C4::Suggestions - Some useful functions for dealings with aqorders.
84 The functions in this module deal with the aqorders in OPAC and in librarian interface
86 A suggestion is done in the OPAC. It has the status "ASKED"
88 When a librarian manages the suggestion, he can set the status to "REJECTED" or "ACCEPTED".
90 When the book is ordered, the suggestion status becomes "ORDERED"
92 When a book is ordered and arrived in the library, the status becomes "AVAILABLE"
94 All aqorders of a borrower can be seen by the borrower itself.
95 Suggestions done by other borrowers can be seen when not "AVAILABLE"
99 =head2 SearchSuggestion
101 (\@array) = &SearchSuggestion($suggestionhashref_to_search)
103 searches for a suggestion
106 C<\@array> : the aqorders found. Array of hash.
107 Note the status is stored twice :
108 * in the status field
109 * as parameter ( for example ASKED => 1, or REJECTED => 1) . This is for template & translation purposes.
113 sub SearchSuggestion {
115 my $dbh = C4::Context->dbh;
118 q{ SELECT suggestions.*,
119 U1.branchcode AS branchcodesuggestedby,
120 B1.branchname AS branchnamesuggestedby,
121 U1.surname AS surnamesuggestedby,
122 U1.firstname AS firstnamesuggestedby,
123 U1.email AS emailsuggestedby,
124 U1.borrowernumber AS borrnumsuggestedby,
125 U1.categorycode AS categorycodesuggestedby,
126 C1.description AS categorydescriptionsuggestedby,
127 U2.surname AS surnamemanagedby,
128 U2.firstname AS firstnamemanagedby,
129 B2.branchname AS branchnamesuggestedby,
130 U2.email AS emailmanagedby,
131 U2.branchcode AS branchcodemanagedby,
132 U2.borrowernumber AS borrnummanagedby
134 LEFT JOIN borrowers AS U1 ON suggestedby=U1.borrowernumber
135 LEFT JOIN branches AS B1 ON B1.branchcode=U1.branchcode
136 LEFT JOIN categories AS C1 ON C1.categorycode = U1.categorycode
137 LEFT JOIN borrowers AS U2 ON managedby=U2.borrowernumber
138 LEFT JOIN branches AS B2 ON B2.branchcode=U2.branchcode
139 LEFT JOIN categories AS C2 ON C2.categorycode = U2.categorycode
140 WHERE STATUS NOT IN ('CLAIMED')
142 if ( my $s = $$suggestion{$_} ) {
143 push @sql_params,'%'.$s.'%';
144 " and suggestions.$_ like ? ";
146 } qw( title author isbn publishercode collectiontitle )
149 my $userenv = C4::Context->userenv;
150 if (C4::Context->preference('IndependantBranches')) {
152 if (($userenv->{flags} % 2) != 1 && !$$suggestion{branchcode}){
153 push @sql_params,$$userenv{branch};
154 push @query,q{ and (branchcode = ? or branchcode ='')};
159 foreach my $field (grep { my $fieldname=$_;
160 any {$fieldname eq $_ } qw<
161 STATUS branchcode itemtype suggestedby managedby acceptedby
162 bookfundid biblionumber
165 if ($$suggestion{$field}){
166 push @sql_params,$$suggestion{$field};
167 push @query, " and suggestions.$field=?";
170 push @query, " and (suggestions.$field='' OR suggestions.$field IS NULL)";
174 $debug && warn "@query";
175 my $sth=$dbh->prepare("@query");
176 $sth->execute(@sql_params);
178 while ( my $data=$sth->fetchrow_hashref ){
179 $$data{$$data{STATUS}} = 1;
180 push(@results,$data);
187 \%sth = &GetSuggestion($ordernumber)
189 this function get the detail of the suggestion $ordernumber (input arg)
192 the result of the SQL query as a hash : $sth->fetchrow_hashref.
197 my ($ordernumber) = @_;
198 my $dbh = C4::Context->dbh;
204 my $sth = $dbh->prepare($query);
205 $sth->execute($ordernumber);
206 return($sth->fetchrow_hashref);
209 =head2 GetSuggestionFromBiblionumber
211 $ordernumber = &GetSuggestionFromBiblionumber($biblionumber)
213 Get a suggestion from it's biblionumber.
216 the id of the suggestion which is related to the biblionumber given on input args.
220 sub GetSuggestionFromBiblionumber {
221 my ($biblionumber) = @_;
227 my $dbh=C4::Context->dbh;
228 my $sth = $dbh->prepare($query);
229 $sth->execute($biblionumber);
230 my ($ordernumber) = $sth->fetchrow;
234 =head2 GetSuggestionByStatus
236 $aqorders = &GetSuggestionByStatus($status,[$branchcode])
238 Get a suggestion from it's status
241 all the suggestion with C<$status>
245 sub GetSuggestionByStatus {
247 my $branchcode = shift;
248 my $dbh = C4::Context->dbh;
249 my @sql_params=($status);
250 my $query = qq(SELECT suggestions.*,
251 U1.surname AS surnamesuggestedby,
252 U1.firstname AS firstnamesuggestedby,
253 U1.branchcode AS branchcodesuggestedby,
254 B1.branchname AS branchnamesuggestedby,
255 U1.borrowernumber AS borrnumsuggestedby,
256 U1.categorycode AS categorycodesuggestedby,
257 C1.description AS categorydescriptionsuggestedby,
258 U2.surname AS surnamemanagedby,
259 U2.firstname AS firstnamemanagedby,
260 U2.borrowernumber AS borrnummanagedby
262 LEFT JOIN borrowers AS U1 ON suggestedby=U1.borrowernumber
263 LEFT JOIN borrowers AS U2 ON managedby=U2.borrowernumber
264 LEFT JOIN categories AS C1 ON C1.categorycode=U1.categorycode
265 LEFT JOIN branches AS B1 on B1.branchcode = U1.branchcode
267 if (C4::Context->preference("IndependantBranches") || $branchcode) {
268 my $userenv = C4::Context->userenv;
270 unless ($userenv->{flags} % 2 == 1){
271 push @sql_params,$userenv->{branch};
272 $query .= " and (U1.branchcode = ? or U1.branchcode ='')";
276 push @sql_params,$branchcode;
277 $query .= " and (U1.branchcode = ? or U1.branchcode ='')";
281 my $sth = $dbh->prepare($query);
282 $sth->execute(@sql_params);
285 $results= $sth->fetchall_arrayref({});
289 =head2 CountSuggestion
291 &CountSuggestion($status)
293 Count the number of aqorders with the status given on input argument.
294 the arg status can be :
298 =item * ASKED : asked by the user, not dealed by the librarian
300 =item * ACCEPTED : accepted by the librarian, but not yet ordered
302 =item * REJECTED : rejected by the librarian (definitive status)
304 =item * ORDERED : ordered by the librarian (acquisition module)
309 the number of suggestion with this status.
313 sub CountSuggestion {
315 my $dbh = C4::Context->dbh;
317 if (C4::Context->preference("IndependantBranches")){
318 my $userenv = C4::Context->userenv;
319 if ($userenv->{flags} % 2 == 1){
325 $sth = $dbh->prepare($query);
326 $sth->execute($status);
331 FROM suggestions LEFT JOIN borrowers ON borrowers.borrowernumber=suggestions.suggestedby
333 AND (borrowers.branchcode='' OR borrowers.branchcode =?)
335 $sth = $dbh->prepare($query);
336 $sth->execute($status,$userenv->{branch});
345 $sth = $dbh->prepare($query);
346 $sth->execute($status);
348 my ($result) = $sth->fetchrow;
355 &NewSuggestion($suggestion);
357 Insert a new suggestion on database with value given on input arg.
362 my ($suggestion) = @_;
363 $suggestion->{STATUS}="ASKED" unless $suggestion->{STATUS};
364 return InsertInTable("suggestions",$suggestion);
369 &ModSuggestion($suggestion)
371 Modify the suggestion according to the hash passed by ref.
372 The hash HAS to contain suggestionid
373 Data not defined is not updated unless it is a note or sort1
374 Send a mail to notify the user that did the suggestion.
376 Note that there is no function to modify a suggestion.
382 my $status_update_table=UpdateInTable("suggestions", $suggestion);
383 # check mail sending.
384 if ($$suggestion{STATUS}){
385 my $letter=C4::Letters::getletter('suggestions',$$suggestion{STATUS});
387 my $enqueued = C4::Letters::EnqueueLetter({
389 borrowernumber=>$$suggestion{suggestedby},
390 suggestionid=>$$suggestion{suggestionid},
391 msg_transport_type=>'email'
393 if (!$enqueued){warn "can't enqueue letter $letter";}
396 return $status_update_table;
399 =head2 ConnectSuggestionAndBiblio
401 &ConnectSuggestionAndBiblio($ordernumber,$biblionumber)
403 connect a suggestion to an existing biblio
407 sub ConnectSuggestionAndBiblio {
408 my ($suggestionid,$biblionumber) = @_;
409 my $dbh=C4::Context->dbh;
415 my $sth = $dbh->prepare($query);
416 $sth->execute($biblionumber,$suggestionid);
421 &DelSuggestion($borrowernumber,$ordernumber)
423 Delete a suggestion. A borrower can delete a suggestion only if he is its owner.
428 my ($borrowernumber,$suggestionid,$type) = @_;
429 my $dbh = C4::Context->dbh;
430 # check that the suggestion comes from the suggestor
436 my $sth = $dbh->prepare($query);
437 $sth->execute($suggestionid);
438 my ($suggestedby) = $sth->fetchrow;
439 if ($type eq "intranet" || $suggestedby eq $borrowernumber ) {
441 DELETE FROM suggestions
444 $sth = $dbh->prepare($queryDelete);
445 my $suggestiondeleted=$sth->execute($suggestionid);
446 return $suggestiondeleted;
456 Koha Developement team <info@koha.org>