package C4::Suggestions;
# Copyright 2000-2002 Katipo Communications
+# Parts Copyright Biblibre 2011
#
# This file is part of Koha.
#
# 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., 59 Temple Place,
-# Suite 330, Boston, MA 02111-1307 USA
+# 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.
-# $Id$
use strict;
-require Exporter;
-use DBI;
+#use warnings; FIXME - Bug 2505
+use CGI;
+
use C4::Context;
use C4::Output;
-use Mail::Sendmail;
-use vars qw($VERSION @ISA @EXPORT);
-
-# set the version for version checking
-$VERSION = do { my @v = '$Revision$' =~ /\d+/g;
- shift(@v) . "." . join("_", map {sprintf "%03d", $_ } @v); };
+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<any>;
+use C4::Dates qw(format_date_in_iso);
+use base qw(Exporter);
+our $VERSION = 3.01;
+our @EXPORT = qw<
+ ConnectSuggestionAndBiblio
+ CountSuggestion
+ DelSuggestion
+ GetSuggestion
+ GetSuggestionByStatus
+ GetSuggestionFromBiblionumber
+ ModStatus
+ ModSuggestion
+ NewSuggestion
+ SearchSuggestion
+ DelSuggestionsOlderThan
+>;
=head1 NAME
-C4::Suggestions - Some useful functions for dealings with suggestions.
+C4::Suggestions - Some useful functions for dealings with aqorders.
=head1 SYNOPSIS
=head1 DESCRIPTION
-The functions in this module deal with the suggestions :
-* in OPAC
-* in librarian interface
+The functions in this module deal with the aqorders in OPAC and in librarian interface
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 "ORDERED".
-When a book is ordered and arrived in the library, the status becomes "AVAILABLE"
-All suggestions of a borrower by the borrower itself.
-Suggestions done by other can be seen when not "AVAILABLE"
-=head1 FUNCTIONS
+When a librarian manages the suggestion, he can set the status to "REJECTED" or "ACCEPTED".
-=over 2
+When the book is ordered, the suggestion status becomes "ORDERED"
-=cut
+When a book is ordered and arrived in the library, the status becomes "AVAILABLE"
-@ISA = qw(Exporter);
-@EXPORT = qw(
- &NewSuggestion
- &SearchSuggestion
- &GetSuggestion
- &DelSuggestion
- &CountSuggestion
- &ModStatus
- &ConnectSuggestionAndBiblio
- &GetSuggestionFromBiblionumber
- );
+All aqorders of a borrower can be seen by the borrower itself.
+Suggestions done by other borrowers can be seen when not "AVAILABLE"
-=item SearchSuggestion
+=head1 FUNCTIONS
-(\@array) = &SearchSuggestion($user)
+=head2 SearchSuggestion
-searches for a suggestion
+(\@array) = &SearchSuggestion($suggestionhashref_to_search)
-C<$user> is the user code (used as suggestor filter)
+searches for a suggestion
return :
-C<\@array> : the suggestions found. Array of hash.
+C<\@array> : the aqorders found. Array of hash.
Note the status is stored twice :
* in the status field
* as parameter ( for example ASKED => 1, or REJECTED => 1) . This is for template & translation purposes.
=cut
+
sub SearchSuggestion {
- my ($user,$author,$title,$publishercode,$status,$suggestedbyme)=@_;
+ my ($suggestion)=@_;
my $dbh = C4::Context->dbh;
- my $query = qq|
- SELECT suggestions.*,
+ 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
+ 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
- WHERE 1=1 |;
+ 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 )
+ );
- my @sql_params;
- if ($author) {
- push @sql_params,"%".$author."%";
- $query .= " and author like ?";
- }
- if ($title) {
- push @sql_params,"%".$title."%";
- $query .= " and suggestions.title like ?";
- }
- if ($publishercode) {
- push @sql_params,"%".$publishercode."%";
- $query .= " and publishercode like ?";
- }
- if ($status) {
- push @sql_params,$status;
- $query .= " and status=?";
+ 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 ='')};
+ }
+ }
}
- if (C4::Context->preference("IndependantBranches")) {
- my $userenv = C4::Context->userenv;
- if ($userenv) {
- unless ($userenv->{flags} == 1){
- push @sql_params,$userenv->{branch};
- $query .= " and (U1.branchcode = ? or U1.branchcode ='')";
- }
+ 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)";
}
}
- if ($suggestedbyme) {
- if ($suggestedbyme eq -1) { # FIXME ! what's this strange code ?
- } else {
- push @sql_params,$user;
- $query .= " and suggestedby=?";
- }
- } else {
- $query .= " and managedby is NULL";
+
+ 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) . "'";
+ }
}
- my $sth=$dbh->prepare($query);
+
+ $debug && warn "@query";
+ my $sth=$dbh->prepare("@query");
$sth->execute(@sql_params);
my @results;
- my $even=1; # the even variable is used to set even / odd lines, for highlighting
- while (my $data=$sth->fetchrow_hashref){
- $data->{$data->{STATUS}} = 1;
- if ($even) {
- $even=0;
- $data->{even}=1;
- } else {
- $even=1;
- }
+ while ( my $data=$sth->fetchrow_hashref ){
+ $$data{$$data{STATUS}} = 1;
push(@results,$data);
}
return (\@results);
}
-=item NewSuggestion
+=head2 GetSuggestion
-&NewSuggestion($borrowernumber,$title,$author,$publishercode,$note,$copyrightdate,$volumedesc,$publicationyear,$place,$isbn,$biblionumber)
+\%sth = &GetSuggestion($ordernumber)
-Insert a new suggestion on database with value given on input arg.
+this function get the detail of the suggestion $ordernumber (input arg)
+
+return :
+ the result of the SQL query as a hash : $sth->fetchrow_hashref.
=cut
-sub NewSuggestion {
- my ($borrowernumber,$title,$author,$publishercode,$note,$copyrightdate,$volumedesc,$publicationyear,$place,$isbn,$biblionumber) = @_;
+
+sub GetSuggestion {
+ my ($ordernumber) = @_;
my $dbh = C4::Context->dbh;
- my $query = qq |
- INSERT INTO suggestions
- (status,suggestedby,title,author,publishercode,note,copyrightdate,
- volumedesc,publicationyear,place,isbn,biblionumber)
- VALUES ('ASKED',?,?,?,?,?,?,?,?,?,?,?)
- |;
+ my $query = "
+ SELECT *
+ FROM suggestions
+ WHERE suggestionid=?
+ ";
my $sth = $dbh->prepare($query);
- $sth->execute($borrowernumber,$title,$author,$publishercode,$note,$copyrightdate,$volumedesc,$publicationyear,$place,$isbn,$biblionumber);
+ $sth->execute($ordernumber);
+ return($sth->fetchrow_hashref);
}
-=item GetSuggestion
+=head2 GetSuggestionFromBiblionumber
-\%sth = &GetSuggestion($suggestionid)
+$ordernumber = &GetSuggestionFromBiblionumber($biblionumber)
-this function get a suggestion from $suggestionid given on input arg.
+Get a suggestion from it's biblionumber.
return :
- the result of the SQL query as a hash : $sth->fetchrow_hashref.
+the id of the suggestion which is related to the biblionumber given on input args.
+
=cut
-sub GetSuggestion {
- my ($suggestionid) = @_;
- my $dbh = C4::Context->dbh;
- my $query = qq|
- SELECT *
+
+sub GetSuggestionFromBiblionumber {
+ my ($biblionumber) = @_;
+ my $query = q{
+ SELECT suggestionid
FROM suggestions
- WHERE suggestionid=?
- |;
+ WHERE biblionumber=?
+ };
+ my $dbh=C4::Context->dbh;
my $sth = $dbh->prepare($query);
- $sth->execute($suggestionid);
- return($sth->fetchrow_hashref);
+ $sth->execute($biblionumber);
+ my ($ordernumber) = $sth->fetchrow;
+ return $ordernumber;
}
-=item DelSuggestion
+=head2 GetSuggestionByStatus
-&DelSuggestion($borrowernumber,$suggestionid)
+$aqorders = &GetSuggestionByStatus($status,[$branchcode])
-Delete a suggestion. A borrower can delete a suggestion only if he is its owner.
+Get a suggestion from it's status
+
+return :
+all the suggestion with C<$status>
=cut
-sub DelSuggestion {
- my ($borrowernumber,$suggestionid) = @_;
+
+sub GetSuggestionByStatus {
+ my $status = shift;
+ my $branchcode = shift;
my $dbh = C4::Context->dbh;
- # check that the suggestion comes from the suggestor
- my $query = qq |
- SELECT suggestedby
- FROM suggestions
- WHERE suggestionid=?
- |;
- my $sth = $dbh->prepare($query);
- $sth->execute($suggestionid);
- my ($suggestedby) = $sth->fetchrow;
- if ($suggestedby eq $borrowernumber) {
- my $queryDelete = qq|
- DELETE FROM suggestions
- WHERE suggestionid=?
- |;
- $sth = $dbh->prepare($queryDelete);
- $sth->execute($suggestionid);
+ 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 $userenv = C4::Context->userenv;
+ if ($userenv) {
+ unless ($userenv->{flags} % 2 == 1){
+ push @sql_params,$userenv->{branch};
+ $query .= " and (U1.branchcode = ? or U1.branchcode ='')";
+ }
+ }
+ if ($branchcode) {
+ push @sql_params,$branchcode;
+ $query .= " and (U1.branchcode = ? or U1.branchcode ='')";
+ }
}
+
+ my $sth = $dbh->prepare($query);
+ $sth->execute(@sql_params);
+
+ my $results;
+ $results= $sth->fetchall_arrayref({});
+ return $results;
}
-=item CountSuggestion
+
+=head2 CountSuggestion
&CountSuggestion($status)
-Count the number of suggestions with the status given on input argument.
+Count the number of aqorders with the status given on input argument.
+the arg status can be :
+
+=over 2
+
+=item * ASKED : asked by the user, not dealed by the librarian
+
+=item * ACCEPTED : accepted by the librarian, but not yet ordered
+
+=item * REJECTED : rejected by the librarian (definitive status)
+
+=item * ORDERED : ordered by the librarian (acquisition module)
+
+=back
return :
the number of suggestion with this status.
=cut
+
sub CountSuggestion {
my ($status) = @_;
my $dbh = C4::Context->dbh;
my $sth;
if (C4::Context->preference("IndependantBranches")){
my $userenv = C4::Context->userenv;
- if ($userenv->{flags} == 1){
+ if ($userenv->{flags} % 2 == 1){
my $query = qq |
SELECT count(*)
FROM suggestions
- WHERE status=?
+ WHERE STATUS=?
|;
$sth = $dbh->prepare($query);
$sth->execute($status);
else {
my $query = qq |
SELECT count(*)
- FROM suggestions,borrowers
- WHERE status=?
- AND borrowers.borrowernumber=suggestions.suggestedby
+ FROM suggestions LEFT JOIN borrowers ON borrowers.borrowernumber=suggestions.suggestedby
+ WHERE STATUS=?
AND (borrowers.branchcode='' OR borrowers.branchcode =?)
|;
$sth = $dbh->prepare($query);
my $query = qq |
SELECT count(*)
FROM suggestions
- WHERE status=?
+ WHERE STATUS=?
|;
- $sth = $dbh->prepare($query);
+ $sth = $dbh->prepare($query);
$sth->execute($status);
}
my ($result) = $sth->fetchrow;
return $result;
}
-=item ModStatus
+=head2 NewSuggestion
+
-&ModStatus($suggestionid,$status,$managedby,$biblionumber)
+&NewSuggestion($suggestion);
+
+Insert a new suggestion on database with value given on input arg.
-Modify the status (status can be 'ASKED', 'ACCEPTED', 'REJECTED'...)
-and send a mail to notify the librarian.
=cut
-sub ModStatus {
- my ($suggestionid,$status,$managedby,$biblionumber) = @_;
- my $dbh = C4::Context->dbh;
- my $sth;
- if ($managedby>0) {
- if ($biblionumber) {
- my $query = qq|
- UPDATE suggestions
- SET status=?,managedby=?,biblionumber=?
- WHERE suggestionid=?
- |;
- $sth = $dbh->prepare($query);
- $sth->execute($status,$managedby,$biblionumber,$suggestionid);
- } else {
- my $query = qq|
- UPDATE suggestions
- SET status=?,managedby=?
- WHERE suggestionid=?
- |;
- $sth = $dbh->prepare($query);
- $sth->execute($status,$managedby,$suggestionid);
- }
- } else {
- if ($biblionumber) {
- my $query = qq|
- UPDATE suggestions
- SET status=?,biblionumber=?
- WHERE suggestionid=?
- |;
- $sth = $dbh->prepare($query);
- $sth->execute($status,$biblionumber,$suggestionid);
- }
- else {
- my $query = qq|
- UPDATE suggestions
- SET status=?
- WHERE suggestionid=?
- |;
- $sth = $dbh->prepare($query);
- $sth->execute($status,$suggestionid);
- }
- }
- # check mail sending.
- my $queryMail = qq|
- SELECT suggestions.*,
- boby.surname AS bysurname,
- boby.firstname AS byfirstname,
- boby.emailaddress AS byemail,
- lib.surname AS libsurname,
- lib.firstname AS libfirstname,
- lib.emailaddress AS libemail
- FROM suggestions
- LEFT JOIN borrowers AS boby ON boby.borrowernumber=suggestedby
- LEFT JOIN borrowers AS lib ON lib.borrowernumber=managedby
- WHERE suggestionid=?
- |;
- $sth = $dbh->prepare($queryMail);
- $sth->execute($suggestionid);
- my $emailinfo = $sth->fetchrow_hashref;
- my $template = gettemplate("suggestion/mail_suggestion_$status.tmpl","intranet");
-
- $template->param(
- byemail => $emailinfo->{byemail},
- libemail => $emailinfo->{libemail},
- status => $emailinfo->{status},
- title => $emailinfo->{title},
- author =>$emailinfo->{author},
- libsurname => $emailinfo->{libsurname},
- libfirstname => $emailinfo->{libfirstname},
- byfirstname => $emailinfo->{byfirstname},
- bysurname => $emailinfo->{bysurname},
- );
- my %mail = (
- To => $emailinfo->{byemail},
- From => $emailinfo->{libemail},
- Subject => 'Koha suggestion',
- Message => "".$template->output
- );
- sendmail(%mail);
+
+sub NewSuggestion {
+ my ($suggestion) = @_;
+ $suggestion->{STATUS}="ASKED" unless $suggestion->{STATUS};
+ return InsertInTable("suggestions",$suggestion);
}
-=item GetSuggestionFromBiblionumber
-$suggestionid = &GetSuggestionFromBiblionumber($dbh,$biblionumber)
+=head2 ModSuggestion
-Get a suggestion from the biblionumber.
+&ModSuggestion($suggestion)
-return :
- the id of the suggestion which has the biblionumber given on input args.
+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
+Send a mail to notify the user that did the suggestion.
+
+Note that there is no function to modify a suggestion.
=cut
-sub GetSuggestionFromBiblionumber {
- my ($dbh,$biblionumber) = @_;
- my $query = qq|
- SELECT suggestionid
- FROM suggestions
- WHERE biblionumber=?
- |;
- my $sth = $dbh->prepare($query);
- $sth->execute($biblionumber);
- my ($suggestionid) = $sth->fetchrow;
- return $suggestionid;
+
+sub ModSuggestion {
+ my ($suggestion)=@_;
+ my $status_update_table=UpdateInTable("suggestions", $suggestion);
+
+ 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({
+ 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";}
+ }
+ }
+ return $status_update_table;
}
-=item ConnectSuggestionAndBiblio
-&ConnectSuggestionAndBiblio($suggestionid,$biblionumber)
+=head2 ConnectSuggestionAndBiblio
+
+&ConnectSuggestionAndBiblio($ordernumber,$biblionumber)
- connect a suggestion to an existing biblio
+connect a suggestion to an existing biblio
=cut
+
sub ConnectSuggestionAndBiblio {
my ($suggestionid,$biblionumber) = @_;
my $dbh=C4::Context->dbh;
- my $query = qq |
+ my $query = "
UPDATE suggestions
SET biblionumber=?
WHERE suggestionid=?
- |;
+ ";
my $sth = $dbh->prepare($query);
$sth->execute($biblionumber,$suggestionid);
}
-=back
-=head1 SEE ALSO
+=head2 DelSuggestion
+
+&DelSuggestion($borrowernumber,$ordernumber)
+
+Delete a suggestion. A borrower can delete a suggestion only if he is its owner.
=cut
+
+sub DelSuggestion {
+ my ($borrowernumber,$suggestionid,$type) = @_;
+ my $dbh = C4::Context->dbh;
+ # check that the suggestion comes from the suggestor
+ my $query = "
+ 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 = "
+ DELETE FROM suggestions
+ WHERE suggestionid=?
+ ";
+ $sth = $dbh->prepare($queryDelete);
+ my $suggestiondeleted=$sth->execute($suggestionid);
+ return $suggestiondeleted;
+ }
+}
+
+=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__
+
+
+=head1 AUTHOR
+
+Koha Development Team <http://koha-community.org/>
+
+=cut
+