# Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
# Suite 330, Boston, MA 02111-1307 USA
-# $Id$
use strict;
-require Exporter;
-use C4::Context;
-use C4::Output;
+use CGI;
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::Context;
+use C4::Output;
+use C4::Dates qw<format_date>;
+use List::MoreUtils;
+use base 'Exporter'; # parent would be better there
+our $VERSION = 3.01;
+our @EXPORT = qw<
+ &ConnectSuggestionAndBiblio
+ &CountSuggestion
+ &DelSuggestion
+ &GetSuggestion
+ &GetSuggestionByStatus
+ &GetSuggestionFromBiblionumber
+ &ModStatus
+ &ModSuggestion
+ &NewSuggestion
+ &SearchSuggestion
+>;
=head1 NAME
=head1 DESCRIPTION
-=over 4
-
The functions in this module deal with the suggestions in OPAC and in librarian interface
A suggestion is done in the OPAC. It has the status "ASKED"
All suggestions of a borrower can be seen by the borrower itself.
Suggestions done by other borrowers can be seen when not "AVAILABLE"
-=back
-
=head1 FUNCTIONS
-=cut
-
-@ISA = qw(Exporter);
-@EXPORT = qw(
- &NewSuggestion
- &SearchSuggestion
- &GetSuggestion
- &DelSuggestion
- &CountSuggestion
- &ModStatus
- &ConnectSuggestionAndBiblio
- &GetSuggestionFromBiblionumber
- );
-
=head2 SearchSuggestion
-=over 4
-
-(\@array) = &SearchSuggestion($user,$author,$title,$publishercode,$status,$suggestedbyme)
+(\@array) = &SearchSuggestion($user,$author,$title,$publishercode,$status,$suggestedbyme,$branchcode)
searches for a suggestion
* in the status field
* as parameter ( for example ASKED => 1, or REJECTED => 1) . This is for template & translation purposes.
-=back
-
=cut
sub SearchSuggestion {
- my ($user,$author,$title,$publishercode,$status,$suggestedbyme)=@_;
+ my ($suggestion,$suggestedbyme)=@_;
my $dbh = C4::Context->dbh;
- my $query = qq|
- SELECT suggestions.*,
- U1.surname AS surnamesuggestedby,
- U1.firstname AS firstnamesuggestedby,
- U2.surname AS surnamemanagedby,
- U2.firstname AS firstnamemanagedby
- FROM suggestions
- LEFT JOIN borrowers AS U1 ON suggestedby=U1.borrowernumber
- LEFT JOIN borrowers AS U2 ON managedby=U2.borrowernumber
- WHERE 1=1 |;
-
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 @query = (
+ q{ SELECT suggestions.*,
+ U1.branchcode AS branchcodesuggestedby,
+ U1.surname AS surnamesuggestedby,
+ U1.firstname AS firstnamesuggestedby,
+ U1.email AS emailsuggestedby,
+ U1.borrowernumber AS borrnumsuggestedby,
+ U2.surname AS surnamemanagedby,
+ U2.firstname AS firstnamemanagedby,
+ 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 borrowers AS U2 ON managedby=U2.borrowernumber
+ WHERE STATUS NOT IN ('CLAIMED')
+ } , map {
+ if ( my $s = $$suggestion{$_} ) {
+ push @sql_params,'%'.$s.'%';
+ " and suggestions.$_ like ? ";
+ } else { () }
+ } qw( title author isbn publishercode collectiontitle )
+ );
+
+ 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 (branchcode = ? or 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 ='')";
- }
- }
+ if ($suggestedbyme) {
+ if (
+ $suggestedbyme != -1
+ and $userenv
+ ) {
+ if ( my $number = $$userenv{number} ) {
+ push @sql_params, $number;
+ push @query, 'and suggestedby=?';
+ delete $$suggestion{suggestedby}
+ }
+ }
+ }
+ foreach my $field (grep {my $fieldname=$_; any {$fieldname eq $_ }qw<
+ STATUS branchcode itemtype suggestedby managedby acceptedby
+ bookfundid biblionumber
+ >} keys %$suggestion
+ ) {
+# next unless $$suggestion{$field};
+ 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) {
- unless ($suggestedbyme eq -1) {
- push @sql_params,$user;
- $query .= " and suggestedby=?";
- }
- } else {
- $query .= " and managedby is NULL";
- }
- my $sth=$dbh->prepare($query);
+
+ 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;
+ while ( my $data=$sth->fetchrow_hashref ){
+ $$data{$$data{STATUS}} = 1;
if ($even) {
$even=0;
- $data->{even}=1;
+ $$data{even}=1;
} else {
- $even=1;
+ $even=1;
}
+
+# $$data{$_} = format_date($$data{$_})
+# foreach qw(suggestioncreatedon suggestionmanagedon suggestionacceptedon date);
push(@results,$data);
}
return (\@results);
=head2 GetSuggestion
-=over 4
-
\%sth = &GetSuggestion($suggestionid)
this function get the detail of the suggestion $suggestionid (input arg)
return :
the result of the SQL query as a hash : $sth->fetchrow_hashref.
-=back
-
=cut
+
sub GetSuggestion {
my ($suggestionid) = @_;
my $dbh = C4::Context->dbh;
- my $query = qq|
+ my $query = q|
SELECT *
FROM suggestions
WHERE suggestionid=?
=head2 GetSuggestionFromBiblionumber
-=over 4
-
$suggestionid = &GetSuggestionFromBiblionumber($dbh,$biblionumber)
Get a suggestion from it's biblionumber.
return :
the id of the suggestion which is related to the biblionumber given on input args.
-=back
-
=cut
+
sub GetSuggestionFromBiblionumber {
my ($dbh,$biblionumber) = @_;
- my $query = qq|
+ my $query = q{
SELECT suggestionid
FROM suggestions
WHERE biblionumber=?
- |;
+ };
my $sth = $dbh->prepare($query);
$sth->execute($biblionumber);
my ($suggestionid) = $sth->fetchrow;
return $suggestionid;
}
+=head2 GetSuggestionByStatus
-=head2 CountSuggestion
+$suggestions = &GetSuggestionByStatus($status,[$branchcode])
+
+Get a suggestion from it's status
+
+return :
+all the suggestion with C<$status>
-=over 4
+=cut
+
+sub GetSuggestionByStatus {
+ my $status = shift;
+ my $branchcode = shift;
+ my $dbh = C4::Context->dbh;
+ my @sql_params=($status);
+ my @query = (q{
+ SELECT suggestions.*,
+ U1.surname AS surnamesuggestedby,
+ U1.firstname AS firstnamesuggestedby,
+ U1.email AS emailsuggestedby,
+ U1.branchcode AS branchcodesuggestedby,
+ U1.borrowernumber AS borrnumsuggestedby,
+ U2.surname AS surnamemanagedby,
+ U2.firstname AS firstnamemanagedby,
+ U2.email AS emailmanagedby,
+ U2.borrowernumber AS borrnummanagedby
+ FROM suggestions
+ LEFT JOIN borrowers AS U1 ON suggestedby=U1.borrowernumber
+ LEFT JOIN borrowers AS U2 ON managedby=U2.borrowernumber
+ 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};
+ push @query, q{ and (suggestions.branchcode = ? or suggestions.branchcode ='')};
+ }
+ }
+ if ($branchcode) {
+ push @sql_params,$branchcode;
+ push @query, q{ and (suggestions.branchcode = ? )};
+ }
+ }
+
+ my $sth = $dbh->prepare("@query");
+ $sth->execute(@sql_params);
+
+ my $results;
+ $results= $sth->fetchall_arrayref({});
+# map{$_->{date} = format_date($_->{date})} @$results;
+ return $results;
+}
+
+=head2 CountSuggestion
&CountSuggestion($status)
Count the number of suggestions with the status given on input argument.
the arg status can be :
-=over
-
-=over
+=over 2
=item * ASKED : asked by the user, not dealed by the librarian
=item * ACCEPTED : accepted by the librarian, but not yet ordered
+=item * CHECKED : checked by the librarian, but not yet ordered
+
=item * REJECTED : rejected by the librarian (definitive status)
=item * ORDERED : ordered by the librarian (acquisition module)
=back
-=back
-
return :
the number of suggestion with this status.
-=back
-
=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){
- my $query = qq |
+ if ($userenv->{flags} % 2 == 1){
+ my $query = q{
SELECT count(*)
FROM suggestions
- WHERE status=?
- |;
+ WHERE STATUS=?
+ };
$sth = $dbh->prepare($query);
$sth->execute($status);
}
else {
- my $query = qq |
+ my $query = q{
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);
$sth->execute($status,$userenv->{branch});
}
}
else {
- my $query = qq |
+ my $query = q{
SELECT count(*)
FROM suggestions
- WHERE status=?
- |;
+ WHERE STATUS=?
+ };
$sth = $dbh->prepare($query);
$sth->execute($status);
}
=head2 NewSuggestion
-=over 4
-
&NewSuggestion($borrowernumber,$title,$author,$publishercode,$note,$copyrightdate,$volumedesc,$publicationyear,$place,$isbn,$biblionumber)
Insert a new suggestion on database with value given on input arg.
-=back
-
=cut
+
sub NewSuggestion {
- my ($borrowernumber,$title,$author,$publishercode,$note,$copyrightdate,$volumedesc,$publicationyear,$place,$isbn,$biblionumber) = @_;
+ my ($suggestion) = @_;
my $dbh = C4::Context->dbh;
+ my @keys; my @values;
- my $query = qq |
- INSERT INTO suggestions
- (status,suggestedby,title,author,publishercode,note,copyrightdate,
- volumedesc,publicationyear,place,isbn,biblionumber)
- VALUES ('ASKED',?,?,?,?,?,?,?,?,?,?,?)
- |;
+ while ( my ($k,$v) = each %$suggestion ) {
+ next unless $v;
+ push @values, $v;
+ push @keys , "$k = ?";
+ }
+
+ my $query = do {
+ local $" = ',';
+ qq{
+ INSERT INTO suggestions
+ SET STATUS='ASKED', @keys
+ };
+ };
+
+ #warn $query;
my $sth = $dbh->prepare($query);
- $sth->execute($borrowernumber,$title,$author,$publishercode,$note,$copyrightdate,$volumedesc,$publicationyear,$place,$isbn,$biblionumber);
+ $sth->execute(@values);
+ return $dbh->{'mysql_insertid'};
}
-=head2 ModStatus
+=head2 ModSuggestion
-=over 4
+&ModSuggestion($suggestion)
-&ModStatus($suggestionid,$status,$managedby,$biblionumber)
-
-Modify the status (status can be 'ASKED', 'ACCEPTED', 'REJECTED', 'ORDERED')
-and send a mail to notify the user that did the suggestion.
+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 : only the status can be modified, thus the name of the function.
-=back
-
=cut
-sub ModStatus {
- my ($suggestionid,$status,$managedby,$biblionumber,$input) = @_;
+
+sub ModSuggestion {
+ my ($suggestion) = @_;
my $dbh = C4::Context->dbh;
my $sth;
- if ($managedby>0) {
- if ($biblionumber) {
- my $query = qq|
+ my $suggestionid=$$suggestion{'suggestionid'};
+ delete $$suggestion{'suggestionid'};
+
+ my @values;
+ my @keys;
+
+ while ( my ($k,$v) = each %$suggestion ) {
+ next if (not $v and $k!~/sort1|note/);
+ #next if any { $_ eq $k } qw( sort1 note);
+ push @values, $v;
+ push @keys, "$k=?";
+ }
+
+ my $query = do { local $"=',';
+ qq{
UPDATE suggestions
- SET status=?,managedby=?,biblionumber=?
+ SET @keys
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);
- }
- }
+ };
+ };
+
+ #warn $query;
+ $sth = $dbh->prepare($query);
+ my $result = $sth->execute(@values, $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;
-if ($emailinfo->{byemail}){
- my $template = gettemplate("suggestion/mail_suggestion_$status.tmpl","intranet",$input);
-
- $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);
-}
+ if ($$suggestion{STATUS}){
+ my $queryMail = q{
+ SELECT suggestions.*,
+ boby.surname AS bysurname,
+ boby.firstname AS byfirstname,
+ boby.email AS byemail,
+ lib.surname AS libsurname,
+ lib.firstname AS libfirstname,
+ lib.email 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_$$suggestion{STATUS}.tmpl"
+ , "intranet"
+ , CGI->new
+ );
+
+ $template->param( %$emailinfo);
+ sendmail(
+ To => $emailinfo->{byemail},
+ From => $emailinfo->{libemail},
+ Subject => 'Koha suggestion',
+ Message => ''.$template->output,
+ 'Content-Type' => 'text/plain; charset="utf8"',
+ );
+ }
+ return $result;
}
=head2 ConnectSuggestionAndBiblio
-=over 4
&ConnectSuggestionAndBiblio($suggestionid,$biblionumber)
connect a suggestion to an existing biblio
-=back
-
=cut
+
sub ConnectSuggestionAndBiblio {
my ($suggestionid,$biblionumber) = @_;
my $dbh=C4::Context->dbh;
- my $query = qq |
+ my $query = q{
UPDATE suggestions
SET biblionumber=?
WHERE suggestionid=?
- |;
+ };
my $sth = $dbh->prepare($query);
- $sth->execute($biblionumber,$suggestionid);
+ return $sth->execute($biblionumber,$suggestionid);
}
=head2 DelSuggestion
-=over 4
-
&DelSuggestion($borrowernumber,$suggestionid)
Delete a suggestion. A borrower can delete a suggestion only if he is its owner.
-=back
-
=cut
sub DelSuggestion {
- my ($borrowernumber,$suggestionid) = @_;
+ my ($borrowernumber,$suggestionid,$type) = @_;
my $dbh = C4::Context->dbh;
# check that the suggestion comes from the suggestor
- my $query = qq |
+ my $query = q{
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|
+ if ($type eq 'intranet' || $suggestedby eq $borrowernumber ) {
+ my $queryDelete = qq{
DELETE FROM suggestions
- WHERE suggestionid=?
- |;
- $sth = $dbh->prepare($queryDelete);
- $sth->execute($suggestionid);
+ WHERE suggestionid=$suggestionid
+ };
+ return $dbh->do($queryDelete);
}
-}
\ No newline at end of file
+}
+
+1;
+__END__
+
+
+=head1 AUTHOR
+
+Koha Developement team <info@koha.org>
+
+=cut
+
projects / koha.git / blobdiff