1 package C4::Suggestions;
3 # Copyright 2000-2002 Katipo Communications
4 # Parts Copyright Biblibre 2011
6 # This file is part of Koha.
8 # Koha is free software; you can redistribute it and/or modify it
9 # under the terms of the GNU General Public License as published by
10 # the Free Software Foundation; either version 3 of the License, or
11 # (at your option) any later version.
13 # Koha is distributed in the hope that it will be useful, but
14 # WITHOUT ANY WARRANTY; without even the implied warranty of
15 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
16 # GNU General Public License for more details.
18 # You should have received a copy of the GNU General Public License
19 # along with Koha; if not, see <http://www.gnu.org/licenses>.
23 #use warnings; FIXME - Bug 2505
28 use C4::Dates qw(format_date format_date_in_iso);
31 use Koha::DateUtils qw( dt_from_string );
33 use List::MoreUtils qw(any);
34 use C4::Dates qw(format_date_in_iso);
35 use base qw(Exporter);
37 our $VERSION = 3.07.00.049;
39 ConnectSuggestionAndBiblio
44 GetSuggestionFromBiblionumber
45 GetSuggestionInfoFromBiblionumber
51 DelSuggestionsOlderThan
56 C4::Suggestions - Some useful functions for dealings with aqorders.
64 The functions in this module deal with the aqorders in OPAC and in librarian interface
66 A suggestion is done in the OPAC. It has the status "ASKED"
68 When a librarian manages the suggestion, he can set the status to "REJECTED" or "ACCEPTED".
70 When the book is ordered, the suggestion status becomes "ORDERED"
72 When a book is ordered and arrived in the library, the status becomes "AVAILABLE"
74 All aqorders of a borrower can be seen by the borrower itself.
75 Suggestions done by other borrowers can be seen when not "AVAILABLE"
79 =head2 SearchSuggestion
81 (\@array) = &SearchSuggestion($suggestionhashref_to_search)
83 searches for a suggestion
86 C<\@array> : the aqorders found. Array of hash.
87 Note the status is stored twice :
89 * as parameter ( for example ASKED => 1, or REJECTED => 1) . This is for template & translation purposes.
93 sub SearchSuggestion {
94 my ($suggestion) = @_;
95 my $dbh = C4::Context->dbh;
100 U1.branchcode AS branchcodesuggestedby,
101 B1.branchname AS branchnamesuggestedby,
102 U1.surname AS surnamesuggestedby,
103 U1.firstname AS firstnamesuggestedby,
104 U1.cardnumber AS cardnumbersuggestedby,
105 U1.email AS emailsuggestedby,
106 U1.borrowernumber AS borrnumsuggestedby,
107 U1.categorycode AS categorycodesuggestedby,
108 C1.description AS categorydescriptionsuggestedby,
109 U2.surname AS surnamemanagedby,
110 U2.firstname AS firstnamemanagedby,
111 B2.branchname AS branchnamesuggestedby,
112 U2.email AS emailmanagedby,
113 U2.branchcode AS branchcodemanagedby,
114 U2.borrowernumber AS borrnummanagedby
116 LEFT JOIN borrowers AS U1 ON suggestedby=U1.borrowernumber
117 LEFT JOIN branches AS B1 ON B1.branchcode=U1.branchcode
118 LEFT JOIN categories AS C1 ON C1.categorycode=U1.categorycode
119 LEFT JOIN borrowers AS U2 ON managedby=U2.borrowernumber
120 LEFT JOIN branches AS B2 ON B2.branchcode=U2.branchcode
121 LEFT JOIN categories AS C2 ON C2.categorycode=U2.categorycode
126 # filter on biblio informations
128 qw( title author isbn publishercode copyrightdate collectiontitle ))
130 if ( $suggestion->{$field} ) {
131 push @sql_params, '%' . $suggestion->{$field} . '%';
132 push @query, qq{ AND suggestions.$field LIKE ? };
136 # filter on user branch
137 if ( C4::Context->preference('IndependentBranches') ) {
138 my $userenv = C4::Context->userenv;
140 if ( !C4::Context->IsSuperLibrarian() && !$suggestion->{branchcode} )
142 push @sql_params, $$userenv{branch};
144 AND (suggestions.branchcode=? OR suggestions.branchcode='')
149 if ( defined $suggestion->{branchcode} && $suggestion->{branchcode} ) {
150 unless ( $suggestion->{branchcode} eq '__ANY__' ) {
151 push @sql_params, $suggestion->{branchcode};
152 push @query, qq{ AND suggestions.branchcode=? };
157 # filter on nillable fields
159 qw( STATUS itemtype suggestedby managedby acceptedby budgetid biblionumber )
162 if ( exists $suggestion->{$field}
163 and defined $suggestion->{$field}
164 and $suggestion->{$field} ne '__ANY__'
165 and $suggestion->{$field} ne q||
167 if ( $suggestion->{$field} eq '__NONE__' ) {
168 push @query, qq{ AND (suggestions.$field = '' OR suggestions.$field IS NULL) };
171 push @sql_params, $suggestion->{$field};
172 push @query, qq{ AND suggestions.$field = ? };
177 # filter on date fields
178 my $today = C4::Dates->today('iso');
179 foreach my $field (qw( suggesteddate manageddate accepteddate )) {
180 my $from = $field . "_from";
181 my $to = $field . "_to";
182 if ( $suggestion->{$from} || $suggestion->{$to} ) {
183 push @query, qq{ AND suggestions.$field BETWEEN ? AND ? };
185 format_date_in_iso( $suggestion->{$from} ) || '0000-00-00';
187 format_date_in_iso( $suggestion->{$to} ) || $today;
191 $debug && warn "@query";
192 my $sth = $dbh->prepare("@query");
193 $sth->execute(@sql_params);
196 # add status as field
197 while ( my $data = $sth->fetchrow_hashref ) {
198 $data->{ $data->{STATUS} } = 1;
199 push( @results, $data );
202 return ( \@results );
207 \%sth = &GetSuggestion($suggestionid)
209 this function get the detail of the suggestion $suggestionid (input arg)
212 the result of the SQL query as a hash : $sth->fetchrow_hashref.
217 my ($suggestionid) = @_;
218 my $dbh = C4::Context->dbh;
224 my $sth = $dbh->prepare($query);
225 $sth->execute($suggestionid);
226 return ( $sth->fetchrow_hashref );
229 =head2 GetSuggestionFromBiblionumber
231 $ordernumber = &GetSuggestionFromBiblionumber($biblionumber)
233 Get a suggestion from it's biblionumber.
236 the id of the suggestion which is related to the biblionumber given on input args.
240 sub GetSuggestionFromBiblionumber {
241 my ($biblionumber) = @_;
245 WHERE biblionumber=? LIMIT 1
247 my $dbh = C4::Context->dbh;
248 my $sth = $dbh->prepare($query);
249 $sth->execute($biblionumber);
250 my ($suggestionid) = $sth->fetchrow;
251 return $suggestionid;
254 =head2 GetSuggestionInfoFromBiblionumber
256 Get a suggestion and borrower's informations from it's biblionumber.
259 all informations (suggestion and borrower) of the suggestion which is related to the biblionumber given.
263 sub GetSuggestionInfoFromBiblionumber {
264 my ($biblionumber) = @_;
266 SELECT suggestions.*,
267 U1.surname AS surnamesuggestedby,
268 U1.firstname AS firstnamesuggestedby,
269 U1.borrowernumber AS borrnumsuggestedby
271 LEFT JOIN borrowers AS U1 ON suggestedby=U1.borrowernumber
275 my $dbh = C4::Context->dbh;
276 my $sth = $dbh->prepare($query);
277 $sth->execute($biblionumber);
278 return $sth->fetchrow_hashref;
281 =head2 GetSuggestionInfo
283 Get a suggestion and borrower's informations from it's suggestionid
286 all informations (suggestion and borrower) of the suggestion which is related to the suggestionid given.
290 sub GetSuggestionInfo {
291 my ($suggestionid) = @_;
293 SELECT suggestions.*,
294 U1.surname AS surnamesuggestedby,
295 U1.firstname AS firstnamesuggestedby,
296 U1.borrowernumber AS borrnumsuggestedby
298 LEFT JOIN borrowers AS U1 ON suggestedby=U1.borrowernumber
302 my $dbh = C4::Context->dbh;
303 my $sth = $dbh->prepare($query);
304 $sth->execute($suggestionid);
305 return $sth->fetchrow_hashref;
308 =head2 GetSuggestionByStatus
310 $aqorders = &GetSuggestionByStatus($status,[$branchcode])
312 Get a suggestion from it's status
315 all the suggestion with C<$status>
319 sub GetSuggestionByStatus {
321 my $branchcode = shift;
322 my $dbh = C4::Context->dbh;
323 my @sql_params = ($status);
325 SELECT suggestions.*,
326 U1.surname AS surnamesuggestedby,
327 U1.firstname AS firstnamesuggestedby,
328 U1.branchcode AS branchcodesuggestedby,
329 B1.branchname AS branchnamesuggestedby,
330 U1.borrowernumber AS borrnumsuggestedby,
331 U1.categorycode AS categorycodesuggestedby,
332 C1.description AS categorydescriptionsuggestedby,
333 U2.surname AS surnamemanagedby,
334 U2.firstname AS firstnamemanagedby,
335 U2.borrowernumber AS borrnummanagedby
337 LEFT JOIN borrowers AS U1 ON suggestedby=U1.borrowernumber
338 LEFT JOIN borrowers AS U2 ON managedby=U2.borrowernumber
339 LEFT JOIN categories AS C1 ON C1.categorycode=U1.categorycode
340 LEFT JOIN branches AS B1 on B1.branchcode=U1.branchcode
345 if ( C4::Context->preference("IndependentBranches") || $branchcode ) {
346 my $userenv = C4::Context->userenv;
348 unless ( C4::Context->IsSuperLibrarian() ) {
349 push @sql_params, $userenv->{branch};
350 $query .= q{ AND (U1.branchcode = ? OR U1.branchcode ='') };
354 push @sql_params, $branchcode;
355 $query .= q{ AND (U1.branchcode = ? OR U1.branchcode ='') };
359 my $sth = $dbh->prepare($query);
360 $sth->execute(@sql_params);
362 $results = $sth->fetchall_arrayref( {} );
366 =head2 CountSuggestion
368 &CountSuggestion($status)
370 Count the number of aqorders with the status given on input argument.
371 the arg status can be :
375 =item * ASKED : asked by the user, not dealed by the librarian
377 =item * ACCEPTED : accepted by the librarian, but not yet ordered
379 =item * REJECTED : rejected by the librarian (definitive status)
381 =item * ORDERED : ordered by the librarian (acquisition module)
386 the number of suggestion with this status.
390 sub CountSuggestion {
392 my $dbh = C4::Context->dbh;
394 my $userenv = C4::Context->userenv;
395 if ( C4::Context->preference("IndependentBranches")
396 && !C4::Context->IsSuperLibrarian() )
401 LEFT JOIN borrowers ON borrowers.borrowernumber=suggestions.suggestedby
403 AND (borrowers.branchcode='' OR borrowers.branchcode=?)
405 $sth = $dbh->prepare($query);
406 $sth->execute( $status, $userenv->{branch} );
414 $sth = $dbh->prepare($query);
415 $sth->execute($status);
417 my ($result) = $sth->fetchrow;
424 &NewSuggestion($suggestion);
426 Insert a new suggestion on database with value given on input arg.
431 my ($suggestion) = @_;
443 # Set the fields to NULL if not given.
444 $suggestion->{$field} ||= undef;
447 $suggestion->{STATUS} = "ASKED" unless $suggestion->{STATUS};
449 $suggestion->{suggesteddate} = dt_from_string unless $suggestion->{suggesteddate};
451 my $rs = Koha::Database->new->schema->resultset('Suggestion');
452 return $rs->create($suggestion)->id;
457 &ModSuggestion($suggestion)
459 Modify the suggestion according to the hash passed by ref.
460 The hash HAS to contain suggestionid
461 Data not defined is not updated unless it is a note or sort1
462 Send a mail to notify the user that did the suggestion.
464 Note that there is no function to modify a suggestion.
469 my ($suggestion) = @_;
470 return unless( $suggestion and defined($suggestion->{suggestionid}) );
482 # Set the fields to NULL if not given.
483 $suggestion->{$field} = undef
484 if exists $suggestion->{$field}
485 and ($suggestion->{$field} eq '0'
486 or $suggestion->{$field} eq '' );
489 my $rs = Koha::Database->new->schema->resultset('Suggestion')->find($suggestion->{suggestionid});
490 my $status_update_table = 1;
492 $rs->update($suggestion);
494 $status_update_table = 0 if( $@ );
496 if ( $suggestion->{STATUS} ) {
498 # fetch the entire updated suggestion so that we can populate the letter
499 my $full_suggestion = GetSuggestion( $suggestion->{suggestionid} );
501 my $letter = C4::Letters::GetPreparedLetter(
502 module => 'suggestions',
503 letter_code => $full_suggestion->{STATUS},
504 branchcode => $full_suggestion->{branchcode},
506 'branches' => $full_suggestion->{branchcode},
507 'borrowers' => $full_suggestion->{suggestedby},
508 'suggestions' => $full_suggestion,
509 'biblio' => $full_suggestion->{biblionumber},
514 C4::Letters::EnqueueLetter(
517 borrowernumber => $full_suggestion->{suggestedby},
518 suggestionid => $full_suggestion->{suggestionid},
519 LibraryName => C4::Context->preference("LibraryName"),
520 message_transport_type => 'email',
522 ) or warn "can't enqueue letter $letter";
525 return $status_update_table;
528 =head2 ConnectSuggestionAndBiblio
530 &ConnectSuggestionAndBiblio($ordernumber,$biblionumber)
532 connect a suggestion to an existing biblio
536 sub ConnectSuggestionAndBiblio {
537 my ( $suggestionid, $biblionumber ) = @_;
538 my $dbh = C4::Context->dbh;
544 my $sth = $dbh->prepare($query);
545 $sth->execute( $biblionumber, $suggestionid );
550 &DelSuggestion($borrowernumber,$ordernumber)
552 Delete a suggestion. A borrower can delete a suggestion only if he is its owner.
557 my ( $borrowernumber, $suggestionid, $type ) = @_;
558 my $dbh = C4::Context->dbh;
560 # check that the suggestion comes from the suggestor
566 my $sth = $dbh->prepare($query);
567 $sth->execute($suggestionid);
568 my ($suggestedby) = $sth->fetchrow;
569 if ( $type eq 'intranet' || $suggestedby eq $borrowernumber ) {
571 DELETE FROM suggestions
574 $sth = $dbh->prepare($queryDelete);
575 my $suggestiondeleted = $sth->execute($suggestionid);
576 return $suggestiondeleted;
580 =head2 DelSuggestionsOlderThan
581 &DelSuggestionsOlderThan($days)
583 Delete all suggestions older than TODAY-$days , that have be accepted or rejected.
587 sub DelSuggestionsOlderThan {
590 my $dbh = C4::Context->dbh;
591 my $sth = $dbh->prepare(
593 DELETE FROM suggestions
594 WHERE STATUS<>'ASKED'
595 AND date < ADDDATE(NOW(), ?)
598 $sth->execute("-$days");
607 Koha Development Team <http://koha-community.org/>