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 q{ SELECT suggestions.*,
120 U1.branchcode AS branchcodesuggestedby,
121 B1.branchname AS branchnamesuggestedby,
122 U1.surname AS surnamesuggestedby,
123 U1.firstname AS firstnamesuggestedby,
124 U1.email AS emailsuggestedby,
125 U1.borrowernumber AS borrnumsuggestedby,
126 U1.categorycode AS categorycodesuggestedby,
127 C1.description AS categorydescriptionsuggestedby,
128 U2.surname AS surnamemanagedby,
129 U2.firstname AS firstnamemanagedby,
130 B2.branchname AS branchnamesuggestedby,
131 U2.email AS emailmanagedby,
132 U2.branchcode AS branchcodemanagedby,
133 U2.borrowernumber AS borrnummanagedby
135 LEFT JOIN borrowers AS U1 ON suggestedby=U1.borrowernumber
136 LEFT JOIN branches AS B1 ON B1.branchcode=U1.branchcode
137 LEFT JOIN categories AS C1 ON C1.categorycode = U1.categorycode
138 LEFT JOIN borrowers AS U2 ON managedby=U2.borrowernumber
139 LEFT JOIN branches AS B2 ON B2.branchcode=U2.branchcode
140 LEFT JOIN categories AS C2 ON C2.categorycode = U2.categorycode
141 WHERE STATUS NOT IN ('CLAIMED')
143 if ( my $s = $$suggestion{$_} ) {
144 push @sql_params,'%'.$s.'%';
145 " and suggestions.$_ like ? ";
147 } qw( title author isbn publishercode collectiontitle )
150 my $userenv = C4::Context->userenv;
151 if (C4::Context->preference('IndependantBranches')) {
153 if (($userenv->{flags} % 2) != 1 && !$$suggestion{branchcode}){
154 push @sql_params,$$userenv{branch};
155 push @query,q{ and (branchcode = ? or branchcode ='')};
160 foreach my $field (grep { my $fieldname=$_;
161 any {$fieldname eq $_ } qw<
162 STATUS branchcode itemtype suggestedby managedby acceptedby
163 bookfundid biblionumber
166 if ($$suggestion{$field}){
167 push @sql_params,$$suggestion{$field};
168 push @query, " and suggestions.$field=?";
171 push @query, " and (suggestions.$field='' OR suggestions.$field IS NULL)";
175 $debug && warn "@query";
176 my $sth=$dbh->prepare("@query");
177 $sth->execute(@sql_params);
179 while ( my $data=$sth->fetchrow_hashref ){
180 $$data{$$data{STATUS}} = 1;
181 push(@results,$data);
188 \%sth = &GetSuggestion($ordernumber)
190 this function get the detail of the suggestion $ordernumber (input arg)
193 the result of the SQL query as a hash : $sth->fetchrow_hashref.
198 my ($ordernumber) = @_;
199 my $dbh = C4::Context->dbh;
205 my $sth = $dbh->prepare($query);
206 $sth->execute($ordernumber);
207 return($sth->fetchrow_hashref);
210 =head2 GetSuggestionFromBiblionumber
212 $ordernumber = &GetSuggestionFromBiblionumber($biblionumber)
214 Get a suggestion from it's biblionumber.
217 the id of the suggestion which is related to the biblionumber given on input args.
221 sub GetSuggestionFromBiblionumber {
222 my ($biblionumber) = @_;
228 my $dbh=C4::Context->dbh;
229 my $sth = $dbh->prepare($query);
230 $sth->execute($biblionumber);
231 my ($ordernumber) = $sth->fetchrow;
235 =head2 GetSuggestionByStatus
237 $aqorders = &GetSuggestionByStatus($status,[$branchcode])
239 Get a suggestion from it's status
242 all the suggestion with C<$status>
246 sub GetSuggestionByStatus {
248 my $branchcode = shift;
249 my $dbh = C4::Context->dbh;
250 my @sql_params=($status);
251 my $query = qq(SELECT suggestions.*,
252 U1.surname AS surnamesuggestedby,
253 U1.firstname AS firstnamesuggestedby,
254 U1.branchcode AS branchcodesuggestedby,
255 B1.branchname AS branchnamesuggestedby,
256 U1.borrowernumber AS borrnumsuggestedby,
257 U1.categorycode AS categorycodesuggestedby,
258 C1.description AS categorydescriptionsuggestedby,
259 U2.surname AS surnamemanagedby,
260 U2.firstname AS firstnamemanagedby,
261 U2.borrowernumber AS borrnummanagedby
263 LEFT JOIN borrowers AS U1 ON suggestedby=U1.borrowernumber
264 LEFT JOIN borrowers AS U2 ON managedby=U2.borrowernumber
265 LEFT JOIN categories AS C1 ON C1.categorycode=U1.categorycode
266 LEFT JOIN branches AS B1 on B1.branchcode = U1.branchcode
268 if (C4::Context->preference("IndependantBranches") || $branchcode) {
269 my $userenv = C4::Context->userenv;
271 unless ($userenv->{flags} % 2 == 1){
272 push @sql_params,$userenv->{branch};
273 $query .= " and (U1.branchcode = ? or U1.branchcode ='')";
277 push @sql_params,$branchcode;
278 $query .= " and (U1.branchcode = ? or U1.branchcode ='')";
282 my $sth = $dbh->prepare($query);
283 $sth->execute(@sql_params);
286 $results= $sth->fetchall_arrayref({});
290 =head2 CountSuggestion
292 &CountSuggestion($status)
294 Count the number of aqorders with the status given on input argument.
295 the arg status can be :
299 =item * ASKED : asked by the user, not dealed by the librarian
301 =item * ACCEPTED : accepted by the librarian, but not yet ordered
303 =item * REJECTED : rejected by the librarian (definitive status)
305 =item * ORDERED : ordered by the librarian (acquisition module)
310 the number of suggestion with this status.
314 sub CountSuggestion {
316 my $dbh = C4::Context->dbh;
318 if (C4::Context->preference("IndependantBranches")){
319 my $userenv = C4::Context->userenv;
320 if ($userenv->{flags} % 2 == 1){
326 $sth = $dbh->prepare($query);
327 $sth->execute($status);
332 FROM suggestions LEFT JOIN borrowers ON borrowers.borrowernumber=suggestions.suggestedby
334 AND (borrowers.branchcode='' OR borrowers.branchcode =?)
336 $sth = $dbh->prepare($query);
337 $sth->execute($status,$userenv->{branch});
346 $sth = $dbh->prepare($query);
347 $sth->execute($status);
349 my ($result) = $sth->fetchrow;
356 &NewSuggestion($suggestion);
358 Insert a new suggestion on database with value given on input arg.
363 my ($suggestion) = @_;
364 $suggestion->{STATUS}="ASKED" unless $suggestion->{STATUS};
365 return InsertInTable("suggestions",$suggestion);
370 &ModSuggestion($suggestion)
372 Modify the suggestion according to the hash passed by ref.
373 The hash HAS to contain suggestionid
374 Data not defined is not updated unless it is a note or sort1
375 Send a mail to notify the user that did the suggestion.
377 Note that there is no function to modify a suggestion.
383 my $status_update_table=UpdateInTable("suggestions", $suggestion);
384 # check mail sending.
385 if ($$suggestion{STATUS}){
386 my $letter=C4::Letters::getletter('suggestions',$$suggestion{STATUS});
388 my $enqueued = C4::Letters::EnqueueLetter({
390 borrowernumber=>$$suggestion{suggestedby},
391 suggestionid=>$$suggestion{suggestionid},
392 msg_transport_type=>'email'
394 if (!$enqueued){warn "can't enqueue letter $letter";}
397 return $status_update_table;
400 =head2 ConnectSuggestionAndBiblio
402 &ConnectSuggestionAndBiblio($ordernumber,$biblionumber)
404 connect a suggestion to an existing biblio
408 sub ConnectSuggestionAndBiblio {
409 my ($suggestionid,$biblionumber) = @_;
410 my $dbh=C4::Context->dbh;
416 my $sth = $dbh->prepare($query);
417 $sth->execute($biblionumber,$suggestionid);
422 &DelSuggestion($borrowernumber,$ordernumber)
424 Delete a suggestion. A borrower can delete a suggestion only if he is its owner.
429 my ($borrowernumber,$suggestionid,$type) = @_;
430 my $dbh = C4::Context->dbh;
431 # check that the suggestion comes from the suggestor
437 my $sth = $dbh->prepare($query);
438 $sth->execute($suggestionid);
439 my ($suggestedby) = $sth->fetchrow;
440 if ($type eq "intranet" || $suggestedby eq $borrowernumber ) {
442 DELETE FROM suggestions
445 $sth = $dbh->prepare($queryDelete);
446 my $suggestiondeleted=$sth->execute($suggestionid);
447 return $suggestiondeleted;
457 Koha Developement team <info@koha.org>