Bug 5549: Formatting duedates in catalogue scripts

[koha.git] / C4 / Suggestions.pm

diff --git a/C4/Suggestions.pm b/C4/Suggestions.pm
index 81ff731..60d0e99 100644 (file)
--- a/C4/Suggestions.pm
+++ b/C4/Suggestions.pm
@@ -1,6 +1,7 @@
 package C4::Suggestions;
 
 # Copyright 2000-2002 Katipo Communications
+# Parts Copyright Biblibre 2011
 #
 # This file is part of Koha.
 #
@@ -24,7 +25,7 @@ use CGI;
 
 use C4::Context;
 use C4::Output;
-use C4::Dates qw(format_date);
+use C4::Dates qw(format_date format_date_in_iso);
 use C4::SQLHelper qw(:all);
 use C4::Debug;
 use C4::Letters;
@@ -39,13 +40,15 @@ our @EXPORT  = qw<
     GetSuggestion
     GetSuggestionByStatus
     GetSuggestionFromBiblionumber
+    GetSuggestionInfoFromBiblionumber
+    GetSuggestionInfo
     ModStatus
     ModSuggestion
     NewSuggestion
     SearchSuggestion
+    DelSuggestionsOlderThan
 >;
 
-
 =head1 NAME
 
 C4::Suggestions - Some useful functions for dealings with aqorders.
@@ -112,7 +115,7 @@ sub SearchSuggestion  {
     LEFT JOIN borrowers AS U2 ON managedby=U2.borrowernumber
     LEFT JOIN branches AS B2 ON B2.branchcode=U2.branchcode
     LEFT JOIN categories AS C2 ON C2.categorycode = U2.categorycode
-    WHERE STATUS NOT IN ('CLAIMED')
+    WHERE 1=1
     } , map {
         if ( my $s = $suggestion->{$_} ) {
         push @sql_params,'%'.$s.'%'; 
@@ -126,7 +129,7 @@ sub SearchSuggestion  {
             if ($userenv) {
                 if (($userenv->{flags} % 2) != 1 && !$suggestion->{branchcode}){
                 push @sql_params,$$userenv{branch};
-                push @query,q{ and (branchcode = ? or branchcode ='')};
+                push @query,q{ and (suggestions.branchcode = ? or suggestions.branchcode ='')};
                 }
             }
     }
@@ -146,6 +149,17 @@ sub SearchSuggestion  {
         }
     }
 
+    my $today = C4::Dates->today('iso');
+
+    foreach ( qw( suggesteddate manageddate accepteddate ) ) {
+        my $from = $_ . "_from";
+        my $to = $_ . "_to";
+        if ($$suggestion{$from} || $$suggestion{$to}) {
+            push @query, " AND suggestions.suggesteddate BETWEEN '" 
+                . (format_date_in_iso($$suggestion{$from}) || 0000-00-00) . "' AND '" . (format_date_in_iso($$suggestion{$to}) || $today) . "'";
+        } 
+    }
+
     $debug && warn "@query";
     my $sth=$dbh->prepare("@query");
     $sth->execute(@sql_params);
@@ -197,13 +211,65 @@ sub GetSuggestionFromBiblionumber {
     my $query = q{
         SELECT suggestionid
         FROM   suggestions
-        WHERE  biblionumber=?
+        WHERE  biblionumber=? LIMIT 1
     };
     my $dbh=C4::Context->dbh;
     my $sth = $dbh->prepare($query);
     $sth->execute($biblionumber);
-    my ($ordernumber) = $sth->fetchrow;
-    return $ordernumber;
+    my ($suggestionid) = $sth->fetchrow;
+    return $suggestionid;
+}
+
+=head2 GetSuggestionInfoFromBiblionumber
+
+Get a suggestion and borrower's informations from it's biblionumber.
+
+return :
+all informations (suggestion and borrower) of the suggestion which is related to the biblionumber given.
+
+=cut
+
+sub GetSuggestionInfoFromBiblionumber {
+    my ($biblionumber) = @_;
+    my $query = qq{
+        SELECT suggestions.*,
+        U1.surname   AS surnamesuggestedby,
+        U1.firstname AS firstnamesuggestedby,
+        U1.borrowernumber AS borrnumsuggestedby
+        FROM suggestions
+        LEFT JOIN borrowers AS U1 ON suggestedby=U1.borrowernumber
+        WHERE biblionumber = ? LIMIT 1
+    };
+    my $dbh = C4::Context->dbh;
+    my $sth = $dbh->prepare($query);
+    $sth->execute($biblionumber);
+    return $sth->fetchrow_hashref;
+}
+
+=head2 GetSuggestionInfo
+
+Get a suggestion and borrower's informations from it's suggestionid
+
+return :
+all informations (suggestion and borrower) of the suggestion which is related to the suggestionid given.
+
+=cut
+
+sub GetSuggestionInfo {
+    my ($suggestionid) = @_;
+    my $query = qq{
+        SELECT suggestions.*,
+        U1.surname   AS surnamesuggestedby,
+        U1.firstname AS firstnamesuggestedby,
+        U1.borrowernumber AS borrnumsuggestedby
+        FROM suggestions
+        LEFT JOIN borrowers AS U1 ON suggestedby=U1.borrowernumber
+        WHERE suggestionid = ? LIMIT 1
+    };
+    my $dbh = C4::Context->dbh;
+    my $sth = $dbh->prepare($query);
+    $sth->execute($suggestionid);
+    return $sth->fetchrow_hashref;
 }
 
 =head2 GetSuggestionByStatus
@@ -359,20 +425,24 @@ sub ModSuggestion {
     if ($suggestion->{STATUS}) {
         # fetch the entire updated suggestion so that we can populate the letter
         my $full_suggestion = GetSuggestion($suggestion->{suggestionid});
-        my $letter = C4::Letters::getletter('suggestions', $full_suggestion->{STATUS});
-        if ($letter) {
-            C4::Letters::parseletter($letter, 'branches',    $full_suggestion->{branchcode});
-            C4::Letters::parseletter($letter, 'borrowers',   $full_suggestion->{suggestedby});
-            C4::Letters::parseletter($letter, 'suggestions', $full_suggestion->{suggestionid});
-            C4::Letters::parseletter($letter, 'biblio',      $full_suggestion->{biblionumber});
-            my $enqueued = C4::Letters::EnqueueLetter({
+        if ( my $letter =  C4::Letters::GetPreparedLetter (
+            module => 'suggestions',
+            letter_code => $full_suggestion->{STATUS},
+            branchcode => $full_suggestion->{branchcode},
+            tables => {
+                'branches'    => $full_suggestion->{branchcode},
+                'borrowers'   => $full_suggestion->{suggestedby},
+                'suggestions' => $full_suggestion,
+                'biblio'      => $full_suggestion->{biblionumber},
+            },
+        ) ) {
+            C4::Letters::EnqueueLetter({
                 letter                  => $letter,
                 borrowernumber          => $full_suggestion->{suggestedby},
                 suggestionid            => $full_suggestion->{suggestionid},
                 LibraryName             => C4::Context->preference("LibraryName"),
                 message_transport_type  => 'email',
-            });
-            if (!$enqueued){warn "can't enqueue letter $letter";}
+            }) or warn "can't enqueue letter $letter";
         }
     }
     return $status_update_table;
@@ -429,6 +499,23 @@ sub DelSuggestion {
     }
 }
 
+=head2 DelSuggestionsOlderThan
+    &DelSuggestionsOlderThan($days)
+    
+    Delete all suggestions older than TODAY-$days , that have be accepted or rejected.
+    
+=cut
+sub DelSuggestionsOlderThan {
+    my ($days) = @_;
+    return if not $days;
+    my $dbh = C4::Context->dbh;
+    
+    my $sth = $dbh->prepare("
+        DELETE FROM suggestions WHERE STATUS <> 'ASKED' AND date < ADDDATE(NOW(), ?);
+    ");
+    $sth->execute("-$days");
+}
+
 1;
 __END__