X-Git-Url: http://git.rot13.org/?a=blobdiff_plain;f=C4%2FSuggestions.pm;h=e2dd3e7f9191d4b8a39f2a1c089f22eadeeb2b85;hb=55d18aeca6e8bff1a1484fafe261dc4d2c5f21c8;hp=60d0e99e40481f3c6d1aaef67d7e5ddbce935b04;hpb=0ef6b0d5c618c2bd4d0ec942e870a6f989632ca0;p=koha.git diff --git a/C4/Suggestions.pm b/C4/Suggestions.pm index 60d0e99e40..e2dd3e7f91 100644 --- a/C4/Suggestions.pm +++ b/C4/Suggestions.pm @@ -5,49 +5,49 @@ package C4::Suggestions; # # This file is part of Koha. # -# Koha is free software; you can redistribute it and/or modify it under the -# terms of the GNU General Public License as published by the Free Software -# Foundation; either version 2 of the License, or (at your option) any later -# version. +# Koha is free software; you can redistribute it and/or modify it +# under the terms of the GNU General Public License as published by +# the Free Software Foundation; either version 3 of the License, or +# (at your option) any later version. # -# Koha is distributed in the hope that it will be useful, but WITHOUT ANY -# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR -# A PARTICULAR PURPOSE. See the GNU General Public License for more details. +# Koha is distributed in the hope that it will be useful, but +# WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU General Public License for more details. # -# You should have received a copy of the GNU General Public License along -# with Koha; if not, write to the Free Software Foundation, Inc., -# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. - +# You should have received a copy of the GNU General Public License +# along with Koha; if not, see . use strict; + #use warnings; FIXME - Bug 2505 -use CGI; +use CGI qw ( -utf8 ); use C4::Context; use C4::Output; -use C4::Dates qw(format_date format_date_in_iso); -use C4::SQLHelper qw(:all); use C4::Debug; use C4::Letters; -use List::MoreUtils qw; -use C4::Dates qw(format_date_in_iso); +use Koha::DateUtils; + +use List::MoreUtils qw(any); use base qw(Exporter); -our $VERSION = 3.01; -our @EXPORT = qw< - ConnectSuggestionAndBiblio - CountSuggestion - DelSuggestion - GetSuggestion - GetSuggestionByStatus - GetSuggestionFromBiblionumber - GetSuggestionInfoFromBiblionumber - GetSuggestionInfo - ModStatus - ModSuggestion - NewSuggestion - SearchSuggestion - DelSuggestionsOlderThan ->; + +our @EXPORT = qw( + ConnectSuggestionAndBiblio + CountSuggestion + DelSuggestion + GetSuggestion + GetSuggestionByStatus + GetSuggestionFromBiblionumber + GetSuggestionInfoFromBiblionumber + GetSuggestionInfo + ModStatus + ModSuggestion + NewSuggestion + SearchSuggestion + DelSuggestionsOlderThan + GetUnprocessedSuggestions +); =head1 NAME @@ -63,7 +63,7 @@ The functions in this module deal with the aqorders in OPAC and in librarian int A suggestion is done in the OPAC. It has the status "ASKED" -When a librarian manages the suggestion, he can set the status to "REJECTED" or "ACCEPTED". +When a librarian manages the suggestion, they can set the status to "REJECTED" or "ACCEPTED". When the book is ordered, the suggestion status becomes "ORDERED" @@ -88,94 +88,130 @@ Note the status is stored twice : =cut -sub SearchSuggestion { - my ($suggestion)=@_; +sub SearchSuggestion { + my ($suggestion) = @_; my $dbh = C4::Context->dbh; my @sql_params; my @query = ( - q{ SELECT suggestions.*, - U1.branchcode AS branchcodesuggestedby, - B1.branchname AS branchnamesuggestedby, - U1.surname AS surnamesuggestedby, - U1.firstname AS firstnamesuggestedby, - U1.email AS emailsuggestedby, - U1.borrowernumber AS borrnumsuggestedby, - U1.categorycode AS categorycodesuggestedby, - C1.description AS categorydescriptionsuggestedby, - U2.surname AS surnamemanagedby, - U2.firstname AS firstnamemanagedby, - B2.branchname AS branchnamesuggestedby, - U2.email AS emailmanagedby, - U2.branchcode AS branchcodemanagedby, - U2.borrowernumber AS borrnummanagedby - FROM suggestions - LEFT JOIN borrowers AS U1 ON suggestedby=U1.borrowernumber - LEFT JOIN branches AS B1 ON B1.branchcode=U1.branchcode - LEFT JOIN categories AS C1 ON C1.categorycode = U1.categorycode - 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 1=1 - } , map { - if ( my $s = $suggestion->{$_} ) { - push @sql_params,'%'.$s.'%'; - " and suggestions.$_ like ? "; - } else { () } - } qw( title author isbn publishercode collectiontitle ) + q{ + SELECT suggestions.*, + U1.branchcode AS branchcodesuggestedby, + B1.branchname AS branchnamesuggestedby, + U1.surname AS surnamesuggestedby, + U1.firstname AS firstnamesuggestedby, + U1.cardnumber AS cardnumbersuggestedby, + U1.email AS emailsuggestedby, + U1.borrowernumber AS borrnumsuggestedby, + U1.categorycode AS categorycodesuggestedby, + C1.description AS categorydescriptionsuggestedby, + U2.surname AS surnamemanagedby, + U2.firstname AS firstnamemanagedby, + B2.branchname AS branchnamesuggestedby, + U2.email AS emailmanagedby, + U2.branchcode AS branchcodemanagedby, + U2.borrowernumber AS borrnummanagedby + FROM suggestions + LEFT JOIN borrowers AS U1 ON suggestedby=U1.borrowernumber + LEFT JOIN branches AS B1 ON B1.branchcode=U1.branchcode + LEFT JOIN categories AS C1 ON C1.categorycode=U1.categorycode + 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 1=1 + } ); - my $userenv = C4::Context->userenv; - if (C4::Context->preference('IndependantBranches')) { - if ($userenv) { - if (($userenv->{flags} % 2) != 1 && !$suggestion->{branchcode}){ - push @sql_params,$$userenv{branch}; - push @query,q{ and (suggestions.branchcode = ? or suggestions.branchcode ='')}; - } - } + # filter on biblio informations + foreach my $field ( + qw( title author isbn publishercode copyrightdate collectiontitle )) + { + if ( $suggestion->{$field} ) { + push @sql_params, '%' . $suggestion->{$field} . '%'; + push @query, qq{ AND suggestions.$field LIKE ? }; + } } - foreach my $field (grep { my $fieldname=$_; - any {$fieldname eq $_ } qw< - STATUS branchcode itemtype suggestedby managedby acceptedby - bookfundid biblionumber - >} keys %$suggestion - ) { - if ($$suggestion{$field}){ - push @sql_params,$suggestion->{$field}; - push @query, " and suggestions.$field=?"; - } - else { - push @query, " and (suggestions.$field='' OR suggestions.$field IS NULL)"; + # filter on user branch + if ( C4::Context->preference('IndependentBranches') ) { + my $userenv = C4::Context->userenv; + if ($userenv) { + if ( !C4::Context->IsSuperLibrarian() && !$suggestion->{branchcode} ) + { + push @sql_params, $$userenv{branch}; + push @query, q{ + AND (suggestions.branchcode=? OR suggestions.branchcode='') + }; + } + } + } else { + if ( defined $suggestion->{branchcode} && $suggestion->{branchcode} ) { + unless ( $suggestion->{branchcode} eq '__ANY__' ) { + push @sql_params, $suggestion->{branchcode}; + push @query, qq{ AND suggestions.branchcode=? }; + } } } - my $today = C4::Dates->today('iso'); + # filter on nillable fields + foreach my $field ( + qw( STATUS itemtype suggestedby managedby acceptedby budgetid biblionumber ) + ) + { + if ( exists $suggestion->{$field} + and defined $suggestion->{$field} + and $suggestion->{$field} ne '__ANY__' + and ( + $suggestion->{$field} ne q|| + or $field eq 'STATUS' + ) + ) { + if ( $suggestion->{$field} eq '__NONE__' ) { + push @query, qq{ AND (suggestions.$field = '' OR suggestions.$field IS NULL) }; + } + else { + push @sql_params, $suggestion->{$field}; + push @query, qq{ AND suggestions.$field = ? }; + } + } + } - 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) . "'"; - } + # filter on date fields + foreach my $field (qw( suggesteddate manageddate accepteddate )) { + my $from = $field . "_from"; + my $to = $field . "_to"; + my $from_dt; + $from_dt = eval { dt_from_string( $suggestion->{$from} ) } if ( $suggestion->{$from} ); + my $from_sql = '0000-00-00'; + $from_sql = output_pref({ dt => $from_dt, dateformat => 'iso', dateonly => 1 }) + if ($from_dt); + $debug && warn "SQL for start date ($field): $from_sql"; + if ( $suggestion->{$from} || $suggestion->{$to} ) { + push @query, qq{ AND suggestions.$field BETWEEN ? AND ? }; + push @sql_params, $from_sql; + push @sql_params, + output_pref({ dt => dt_from_string( $suggestion->{$to} ), dateformat => 'iso', dateonly => 1 }) || output_pref({ dt => dt_from_string, dateformat => 'iso', dateonly => 1 }); + } } $debug && warn "@query"; - my $sth=$dbh->prepare("@query"); + my $sth = $dbh->prepare("@query"); $sth->execute(@sql_params); my @results; - while ( my $data=$sth->fetchrow_hashref ){ - $$data{$$data{STATUS}} = 1; - push(@results,$data); + + # add status as field + while ( my $data = $sth->fetchrow_hashref ) { + $data->{ $data->{STATUS} } = 1; + push( @results, $data ); } - return (\@results); + + return ( \@results ); } =head2 GetSuggestion -\%sth = &GetSuggestion($ordernumber) +\%sth = &GetSuggestion($suggestionid) -this function get the detail of the suggestion $ordernumber (input arg) +this function get the detail of the suggestion $suggestionid (input arg) return : the result of the SQL query as a hash : $sth->fetchrow_hashref. @@ -183,16 +219,16 @@ return : =cut sub GetSuggestion { - my ($ordernumber) = @_; - my $dbh = C4::Context->dbh; - my $query = " + my ($suggestionid) = @_; + my $dbh = C4::Context->dbh; + my $query = q{ SELECT * FROM suggestions WHERE suggestionid=? - "; + }; my $sth = $dbh->prepare($query); - $sth->execute($ordernumber); - return($sth->fetchrow_hashref); + $sth->execute($suggestionid); + return ( $sth->fetchrow_hashref ); } =head2 GetSuggestionFromBiblionumber @@ -213,7 +249,7 @@ sub GetSuggestionFromBiblionumber { FROM suggestions WHERE biblionumber=? LIMIT 1 }; - my $dbh=C4::Context->dbh; + my $dbh = C4::Context->dbh; my $sth = $dbh->prepare($query); $sth->execute($biblionumber); my ($suggestionid) = $sth->fetchrow; @@ -231,14 +267,15 @@ all informations (suggestion and borrower) of the suggestion which is related to sub GetSuggestionInfoFromBiblionumber { my ($biblionumber) = @_; - my $query = qq{ + my $query = q{ SELECT suggestions.*, - U1.surname AS surnamesuggestedby, - U1.firstname AS firstnamesuggestedby, - U1.borrowernumber AS borrnumsuggestedby + 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 + LEFT JOIN borrowers AS U1 ON suggestedby=U1.borrowernumber + WHERE biblionumber=? + LIMIT 1 }; my $dbh = C4::Context->dbh; my $sth = $dbh->prepare($query); @@ -257,14 +294,15 @@ all informations (suggestion and borrower) of the suggestion which is related to sub GetSuggestionInfo { my ($suggestionid) = @_; - my $query = qq{ + my $query = q{ SELECT suggestions.*, - U1.surname AS surnamesuggestedby, - U1.firstname AS firstnamesuggestedby, - U1.borrowernumber AS borrnumsuggestedby + 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 + LEFT JOIN borrowers AS U1 ON suggestedby=U1.borrowernumber + WHERE suggestionid=? + LIMIT 1 }; my $dbh = C4::Context->dbh; my $sth = $dbh->prepare($query); @@ -284,46 +322,49 @@ all the suggestion with C<$status> =cut sub GetSuggestionByStatus { - my $status = shift; + my $status = shift; my $branchcode = shift; - my $dbh = C4::Context->dbh; - my @sql_params=($status); - my $query = qq(SELECT suggestions.*, - U1.surname AS surnamesuggestedby, - U1.firstname AS firstnamesuggestedby, - U1.branchcode AS branchcodesuggestedby, - B1.branchname AS branchnamesuggestedby, - U1.borrowernumber AS borrnumsuggestedby, - U1.categorycode AS categorycodesuggestedby, - C1.description AS categorydescriptionsuggestedby, - U2.surname AS surnamemanagedby, - U2.firstname AS firstnamemanagedby, - U2.borrowernumber AS borrnummanagedby - FROM suggestions - LEFT JOIN borrowers AS U1 ON suggestedby=U1.borrowernumber - LEFT JOIN borrowers AS U2 ON managedby=U2.borrowernumber - LEFT JOIN categories AS C1 ON C1.categorycode=U1.categorycode - LEFT JOIN branches AS B1 on B1.branchcode = U1.branchcode - WHERE status = ?); - if (C4::Context->preference("IndependantBranches") || $branchcode) { + my $dbh = C4::Context->dbh; + my @sql_params = ($status); + my $query = q{ + SELECT suggestions.*, + U1.surname AS surnamesuggestedby, + U1.firstname AS firstnamesuggestedby, + U1.branchcode AS branchcodesuggestedby, + B1.branchname AS branchnamesuggestedby, + U1.borrowernumber AS borrnumsuggestedby, + U1.categorycode AS categorycodesuggestedby, + C1.description AS categorydescriptionsuggestedby, + U2.surname AS surnamemanagedby, + U2.firstname AS firstnamemanagedby, + U2.borrowernumber AS borrnummanagedby + FROM suggestions + LEFT JOIN borrowers AS U1 ON suggestedby=U1.borrowernumber + LEFT JOIN borrowers AS U2 ON managedby=U2.borrowernumber + LEFT JOIN categories AS C1 ON C1.categorycode=U1.categorycode + LEFT JOIN branches AS B1 on B1.branchcode=U1.branchcode + WHERE status = ? + }; + + # filter on branch + if ( C4::Context->preference("IndependentBranches") || $branchcode ) { my $userenv = C4::Context->userenv; if ($userenv) { - unless ($userenv->{flags} % 2 == 1){ - push @sql_params,$userenv->{branch}; - $query .= " and (U1.branchcode = ? or U1.branchcode ='')"; + unless ( C4::Context->IsSuperLibrarian() ) { + push @sql_params, $userenv->{branch}; + $query .= q{ AND (U1.branchcode = ? OR U1.branchcode ='') }; } } if ($branchcode) { - push @sql_params,$branchcode; - $query .= " and (U1.branchcode = ? or U1.branchcode ='')"; + push @sql_params, $branchcode; + $query .= q{ AND (U1.branchcode = ? OR U1.branchcode ='') }; } } - + my $sth = $dbh->prepare($query); $sth->execute(@sql_params); - my $results; - $results= $sth->fetchall_arrayref({}); + $results = $sth->fetchall_arrayref( {} ); return $results; } @@ -355,34 +396,26 @@ sub CountSuggestion { my ($status) = @_; my $dbh = C4::Context->dbh; my $sth; - if (C4::Context->preference("IndependantBranches")){ - my $userenv = C4::Context->userenv; - if ($userenv->{flags} % 2 == 1){ - my $query = qq | - SELECT count(*) - FROM suggestions - WHERE STATUS=? - |; - $sth = $dbh->prepare($query); - $sth->execute($status); - } - else { - my $query = qq | - SELECT count(*) - FROM suggestions LEFT JOIN borrowers ON borrowers.borrowernumber=suggestions.suggestedby - WHERE STATUS=? - AND (borrowers.branchcode='' OR borrowers.branchcode =?) - |; - $sth = $dbh->prepare($query); - $sth->execute($status,$userenv->{branch}); - } + my $userenv = C4::Context->userenv; + if ( C4::Context->preference("IndependentBranches") + && !C4::Context->IsSuperLibrarian() ) + { + my $query = q{ + SELECT count(*) + FROM suggestions + LEFT JOIN borrowers ON borrowers.borrowernumber=suggestions.suggestedby + WHERE STATUS=? + AND (borrowers.branchcode='' OR borrowers.branchcode=?) + }; + $sth = $dbh->prepare($query); + $sth->execute( $status, $userenv->{branch} ); } else { - my $query = qq | + my $query = q{ SELECT count(*) FROM suggestions WHERE STATUS=? - |; + }; $sth = $dbh->prepare($query); $sth->execute($status); } @@ -401,8 +434,27 @@ Insert a new suggestion on database with value given on input arg. sub NewSuggestion { my ($suggestion) = @_; - $suggestion->{STATUS}="ASKED" unless $suggestion->{STATUS}; - return InsertInTable("suggestions",$suggestion); + + for my $field ( qw( + suggestedby + managedby + manageddate + acceptedby + accepteddate + rejectedby + rejecteddate + budgetid + ) ) { + # Set the fields to NULL if not given. + $suggestion->{$field} ||= undef; + } + + $suggestion->{STATUS} = "ASKED" unless $suggestion->{STATUS}; + + $suggestion->{suggesteddate} = dt_from_string unless $suggestion->{suggesteddate}; + + my $rs = Koha::Database->new->schema->resultset('Suggestion'); + return $rs->create($suggestion)->id; } =head2 ModSuggestion @@ -411,38 +463,73 @@ sub NewSuggestion { Modify the suggestion according to the hash passed by ref. The hash HAS to contain suggestionid -Data not defined is not updated unless it is a note or sort1 +Data not defined is not updated unless it is a note or sort1 Send a mail to notify the user that did the suggestion. -Note that there is no function to modify a suggestion. +Note that there is no function to modify a suggestion. =cut sub ModSuggestion { - my ($suggestion)=@_; - my $status_update_table=UpdateInTable("suggestions", $suggestion); + my ($suggestion) = @_; + return unless( $suggestion and defined($suggestion->{suggestionid}) ); + + for my $field ( qw( + suggestedby + managedby + manageddate + acceptedby + accepteddate + rejectedby + rejecteddate + budgetid + ) ) { + # Set the fields to NULL if not given. + $suggestion->{$field} = undef + if exists $suggestion->{$field} + and ($suggestion->{$field} eq '0' + or $suggestion->{$field} eq '' ); + } + + my $rs = Koha::Database->new->schema->resultset('Suggestion')->find($suggestion->{suggestionid}); + my $status_update_table = 1; + eval { + $rs->update($suggestion); + }; + $status_update_table = 0 if( $@ ); + + if ( $suggestion->{STATUS} ) { - if ($suggestion->{STATUS}) { # fetch the entire updated suggestion so that we can populate the letter - my $full_suggestion = GetSuggestion($suggestion->{suggestionid}); - 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', - }) or warn "can't enqueue letter $letter"; + my $full_suggestion = GetSuggestion( $suggestion->{suggestionid} ); + my $patron = Koha::Patrons->find( $full_suggestion->{suggestedby} ); + + my $transport = (C4::Context->preference("FallbackToSMSIfNoEmail")) && ($patron->smsalertnumber) && (!$patron->email) ? 'sms' : 'email'; + + if ( + my $letter = C4::Letters::GetPreparedLetter( + module => 'suggestions', + letter_code => $full_suggestion->{STATUS}, + branchcode => $full_suggestion->{branchcode}, + lang => $patron->lang, + 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 => $transport, + } + ) or warn "can't enqueue letter $letter"; } } return $status_update_table; @@ -457,65 +544,88 @@ connect a suggestion to an existing biblio =cut sub ConnectSuggestionAndBiblio { - my ($suggestionid,$biblionumber) = @_; - my $dbh=C4::Context->dbh; - my $query = " + my ( $suggestionid, $biblionumber ) = @_; + my $dbh = C4::Context->dbh; + my $query = q{ UPDATE suggestions SET biblionumber=? WHERE suggestionid=? - "; + }; my $sth = $dbh->prepare($query); - $sth->execute($biblionumber,$suggestionid); + $sth->execute( $biblionumber, $suggestionid ); } =head2 DelSuggestion &DelSuggestion($borrowernumber,$ordernumber) -Delete a suggestion. A borrower can delete a suggestion only if he is its owner. +Delete a suggestion. A borrower can delete a suggestion only if they are its owner. =cut sub DelSuggestion { - my ($borrowernumber,$suggestionid,$type) = @_; + my ( $borrowernumber, $suggestionid, $type ) = @_; my $dbh = C4::Context->dbh; + # check that the suggestion comes from the suggestor - my $query = " + my $query = q{ SELECT suggestedby FROM suggestions WHERE suggestionid=? - "; + }; my $sth = $dbh->prepare($query); $sth->execute($suggestionid); my ($suggestedby) = $sth->fetchrow; - if ($type eq "intranet" || $suggestedby eq $borrowernumber ) { - my $queryDelete = " + if ( $type eq 'intranet' || $suggestedby eq $borrowernumber ) { + my $queryDelete = q{ DELETE FROM suggestions WHERE suggestionid=? - "; + }; $sth = $dbh->prepare($queryDelete); - my $suggestiondeleted=$sth->execute($suggestionid); - return $suggestiondeleted; + my $suggestiondeleted = $sth->execute($suggestionid); + return $suggestiondeleted; } } =head2 DelSuggestionsOlderThan &DelSuggestionsOlderThan($days) - + Delete all suggestions older than TODAY-$days , that have be accepted or rejected. - + We do now allow a negative number. If you want to delete all suggestions, just use Koha::Suggestions->delete or so. + =cut + sub DelSuggestionsOlderThan { my ($days) = @_; - return if not $days; + return unless $days && $days > 0; my $dbh = C4::Context->dbh; - - my $sth = $dbh->prepare(" - DELETE FROM suggestions WHERE STATUS <> 'ASKED' AND date < ADDDATE(NOW(), ?); - "); + my $sth = $dbh->prepare( + q{ + DELETE FROM suggestions + WHERE STATUS<>'ASKED' + AND date < ADDDATE(NOW(), ?) + } + ); $sth->execute("-$days"); } +sub GetUnprocessedSuggestions { + my ( $number_of_days_since_the_last_modification ) = @_; + + $number_of_days_since_the_last_modification ||= 0; + + my $dbh = C4::Context->dbh; + + my $s = $dbh->selectall_arrayref(q| + SELECT * + FROM suggestions + WHERE STATUS = 'ASKED' + AND budgetid IS NOT NULL + AND CAST(NOW() AS DATE) - INTERVAL ? DAY = CAST(suggesteddate AS DATE) + |, { Slice => {} }, $number_of_days_since_the_last_modification ); + return $s; +} + 1; __END__