@@ -, +, @@ --------- -- this will add the required default letter. a) set the CommentModeratorsEmail system preference. -- not setting it will mean you won't get any queued letters. a) set the staffClientBaseURL system preference. -- not setting it will mean your email links won't be valid (i.e http:///...). b) modify the COMMENT_CREATED default letter as desired. -- feel free to add other things. a) Log in b) Find something c) Click on the 'Comments' tab -- Holdings/Title notes/Comments (#) is at the bottom d) Click 'Post or edit your comments on this item' e) In the window that pops up, type your comment. f) Click 'Submit and close this window'. g) Click 'Edit' and repeat steps e and f again. a) Open the koha database b) SELECT * FROM message_queue WHERE letter_code='COMMENT_CREATED'; -- There should be at least two. -- They should be able to contain the borrower number, biblio title, and other borrower and biblio related things. -- Comments are at a biblio level, so items, and issues don't make sense as variables for the letter. -- Confirm the URLs are valid by pasting into a browser. --- C4/Letters.pm | 6 +- C4/Review.pm | 67 ++++++++++----- .../data/mysql/en/mandatory/sample_notices.sql | 18 +++- installer/data/mysql/updatedatabase.pl | 62 ++++++++------ t/db_dependent/Review.t | 97 ++++++++++++++++++++++ 5 files changed, 203 insertions(+), 47 deletions(-) create mode 100755 t/db_dependent/Review.t --- a/C4/Letters.pm +++ a/C4/Letters.pm @@ -625,9 +625,12 @@ sub GetPreparedLetter { } } - my $OPACBaseURL = C4::Context->preference('OPACBaseURL'); + my $OPACBaseURL = C4::Context->preference('OPACBaseURL') // ''; $letter->{content} =~ s/<>/$OPACBaseURL/go; + my $staffClientBaseURL = C4::Context->preference('staffClientBaseURL') // ''; + $letter->{content} =~ s/<>/$staffClientBaseURL/go; + if ($want_librarian) { # parsing librarian name my $userenv = C4::Context->userenv; @@ -740,6 +743,7 @@ sub _parseletter_sth { ($table eq 'aqbooksellers') ? "SELECT * FROM $table WHERE id = ?" : ($table eq 'aqorders' ) ? "SELECT * FROM $table WHERE ordernumber = ?" : ($table eq 'opac_news' ) ? "SELECT * FROM $table WHERE idnew = ?" : + ($table eq 'reviews' ) ? "SELECT * FROM $table WHERE reviewid = ?" : ($table eq 'borrower_modifications') ? "SELECT * FROM $table WHERE verification_token = ?" : undef ; unless ($query) { --- a/C4/Review.pm +++ a/C4/Review.pm @@ -4,23 +4,23 @@ package C4::Review; # # 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; +use Modern::Perl; use C4::Context; +require C4::Letters; use vars qw($VERSION @ISA @EXPORT); @@ -29,8 +29,10 @@ BEGIN { $VERSION = 3.07.00.049; require Exporter; @ISA = qw(Exporter); - @EXPORT = qw(getreview savereview updatereview numberofreviews numberofreviewsbybiblionumber - getreviews getallreviews approvereview unapprovereview deletereview); + @EXPORT = qw(getreview savereview updatereview numberofreviews + numberofreviewsbybiblionumber getreviews + getallreviews approvereview unapprovereview + deletereview SendReviewAlert); } =head1 NAME @@ -79,11 +81,20 @@ sub savereview { my $query = "INSERT INTO reviews (borrowernumber,biblionumber, review,approved,datereviewed) VALUES (?,?,?,0,now())"; + my $GetReviewID = q{ +SELECT reviewid FROM reviews WHERE borrowernumber=? AND biblionumber=?; + }; my $sth = $dbh->prepare($query); $sth->execute( $borrowernumber, $biblionumber, $review); + $sth = $dbh->prepare($GetReviewID); + $sth->execute( $borrowernumber, $biblionumber ); + my $reviewid = $sth->fetchrow(); unless ($sth->err()) { - SendReviewAlert( $review, $borrowernumber ); + SendReviewAlert( { + 'reviewid' => $reviewid, + 'borrowernumber' => $borrowernumber, + 'biblionumber' => $biblionumber } ); } } @@ -91,11 +102,20 @@ sub updatereview { my ( $biblionumber, $borrowernumber, $review ) = @_; my $dbh = C4::Context->dbh; my $query = "UPDATE reviews SET review=?,datereviewed=now(),approved=0 WHERE borrowernumber=? and biblionumber=?"; + my $GetReviewID = q{ +SELECT reviewid FROM reviews WHERE borrowernumber=? AND biblionumber=?; + }; my $sth = $dbh->prepare($query); $sth->execute( $review, $borrowernumber, $biblionumber ); + $sth = $dbh->prepare($GetReviewID); + $sth->execute( $borrowernumber, $biblionumber ); + my $reviewid = $sth->fetchrow(); unless ($sth->err()) { - SendReviewAlert( $review, $borrowernumber ); + SendReviewAlert( { + 'reviewid' => $reviewid, + 'borrowernumber' => $borrowernumber, + 'biblionumber' => $biblionumber } ); } } @@ -196,23 +216,30 @@ sub deletereview { =head2 SendReviewAlert - SendReviewAlert( $review, $borrowernumber ); + SendReviewAlert( { 'reviewid' => $reviewid, + 'borrowernumber' => $borrowernumber, + 'biblionumber' => $biblionumber } ); =cut sub SendReviewAlert { - my $review = shift; - my $borrowernumber = shift; + my $params = shift; my $moderatorEmail = C4::Context->preference('CommentModeratorsEmail'); - return unless $moderatorEmail; + my $reviewid = $params->{'reviewid'}; + my $borrowernumber = $params->{'borrowernumber'}; + my $biblionumber = $params->{'biblionumber'}; + my $letter = C4::Letters::GetPreparedLetter ( module => 'members', letter_code => 'COMMENT_CREATED', tables => { + 'reviews' => $reviewid, 'borrowers' => $borrowernumber, + 'biblio' => $biblionumber, + 'biblioitems' => $biblionumber, } ) or return; --- a/installer/data/mysql/en/mandatory/sample_notices.sql +++ a/installer/data/mysql/en/mandatory/sample_notices.sql @@ -119,9 +119,23 @@ If you did not initiate this request, you may safely ignore this one-time messag ); INSERT INTO letter (module, code, branchcode, name, is_html, title, content, message_transport_type) -VALUES ( 'members', 'COMMENT_CREATED', '', 'Comment created notification', '0', 'Comment from <> is waiting for moderation', 'Dear moderator, +VALUES ( 'members', 'COMMENT_CREATED', '', 'Comment created notification', '0', 'Comment from <> is waiting for moderation', +'Dear moderator, -We want to inform you that borrower <> has just created a new comment. +We want to inform you that borrower <> has just created a new comment on <>, which is biblionumber <>. + +--- BEGIN COMMENT --- +<> +--- END COMMENT --- + +To approve: +http://<>/cgi-bin/koha/reviews/reviewswaiting.pl?op=approve&reviewid=<> + +To delete: +http://<>/cgi-bin/koha/reviews/reviewswaiting.pl?op=delete&reviewid=<> + +Or to review all comments: +http://<>/cgi-bin/koha/reviews/reviewswaiting.pl Check it out! --- a/installer/data/mysql/updatedatabase.pl +++ a/installer/data/mysql/updatedatabase.pl @@ -8314,30 +8314,6 @@ if ( CheckVersion($DBversion) ) { SetVersion($DBversion); } -$DBversion = "3.17.00.XXX"; -if ( CheckVersion($DBversion) ) { - $dbh->do(q| -INSERT INTO letter (module, code, branchcode, name, is_html, title, content, message_transport_type) -VALUES ( 'members', 'COMMENT_CREATED', '', 'Comment created notification', '0', 'Comment from <> is waiting for moderation', 'Dear moderator, - -We want to inform you that borrower <> has just created a new comment. - -Check it out! - -Your library.', -'email' -); - |); - $dbh->do(q| - INSERT INTO systempreferences - (variable,value,explanation,options,type) - VALUES - ('CommentModeratorsEmail','','','The email address where to send a notification (template code COMMENT_CREATED) when a Borrower adds/modifies a review/comment for a Biblio. Set to empty to disable sending email notifications.','Textarea') - |); - print "Upgrade to $DBversion done (Bug 1985 - Email notification of new OPAC comments)\n"; - SetVersion($DBversion); -} - $DBversion = "3.15.00.041"; if ( CheckVersion($DBversion) ) { my $name = $dbh->selectcol_arrayref(q| @@ -9817,6 +9793,44 @@ if(CheckVersion($DBversion)) { SetVersion($DBversion); } +$DBversion = "3.19.00.XXX"; +if ( CheckVersion($DBversion) ) { + $dbh->do(q| +INSERT INTO letter (module, code, branchcode, name, is_html, title, content, message_transport_type) +VALUES ( 'members', 'COMMENT_CREATED', '', 'Comment created notification', '0', 'Comment from <> is waiting for moderation', +'Dear moderator, + +We want to inform you that borrower <> has just created a new comment on <>, which is biblionumber <>. + +--- BEGIN COMMENT --- +<> +--- END COMMENT --- + +To approve: +http://<>/cgi-bin/koha/reviews/reviewswaiting.pl?op=approve&reviewid=<> + +To delete: +http://<>/cgi-bin/koha/reviews/reviewswaiting.pl?op=delete&reviewid=<> + +Or to review all comments: +http://<>/cgi-bin/koha/reviews/reviewswaiting.pl + +Check it out! + +Your library.', +'email' +); + |); + $dbh->do(q| + INSERT INTO systempreferences + (variable,value,explanation,options,type) + VALUES + ('CommentModeratorsEmail','','','The email address where to send a notification (template code COMMENT_CREATED) when a Borrower adds/modifies a review/comment for a Biblio. Set to empty to disable sending email notifications.','Textarea') + |); + print "Upgrade to $DBversion done (Bug 1985 - Email notification of new OPAC comments)\n"; + SetVersion($DBversion); +} + =head1 FUNCTIONS =head2 TableExists($table) --- a/t/db_dependent/Review.t +++ a/t/db_dependent/Review.t @@ -0,0 +1,97 @@ +#!/usr/bin/perl + +use Modern::Perl; +use Test::More tests => 14; + +use_ok('C4::Review'); + +my $dbh = C4::Context->dbh; +$dbh->{RaiseError} = 1; +$dbh->{AutoCommit} = 0; + +my $sth = $dbh->prepare("SELECT * FROM borrowers;"); +$sth->execute(); +my $row = $sth->fetchrow_hashref(); +my $borrowernumber; +$borrowernumber = $row->{'borrowernumber'} if $row; +$sth = $dbh->prepare("SELECT * FROM biblio;"); +$sth->execute(); +$row = $sth->fetchrow_hashref(); +my $biblionumber; +$biblionumber = $row->{'biblionumber'} if $row; + +SKIP: { + skip "Missing borrowers or biblios",13 + unless $biblionumber and $borrowernumber; + + # Make sure there are no reviews + $sth = $dbh->prepare("DELETE FROM reviews;"); + $sth->execute(); + + # Clear out the message_queue to make counting easier. + $sth = $dbh->prepare("DELETE FROM message_queue;"); + $sth->execute(); + + # Add a review + savereview($biblionumber,$borrowernumber,'A sample review.'); + + # Retrieve review + my $reviews = getallreviews(0); + my $reviewid = $reviews->[0]->{'reviewid'}; + my $ReviewText = $reviews->[0]->{'review'}; + ok($ReviewText eq 'A sample review.','Saved and retrieved a sample review.'); + + # Update the review + updatereview($biblionumber,$borrowernumber,'A revised review.'); + + # Retrieve it differently. + my $review = getreview($biblionumber,$borrowernumber); + $ReviewText = $review->{'review'}; + ok($ReviewText eq 'A revised review.','Updated and retrieved a revised review.'); + + # Confirm 1 unapproved comment. + my $UnapprovedReviewsCount = numberofreviews(0); + ok($UnapprovedReviewsCount==1,'There is one unapproved comment.'); + + # Approve the comment. + approvereview($reviewid); + $UnapprovedReviewsCount = numberofreviews(0); + ok($UnapprovedReviewsCount==0,'There are no unapproved comments.'); + my $ApprovedReviewsCount = numberofreviews(1); + ok($ApprovedReviewsCount==1,'There is one approved comment.'); + + # Check how many reviews for this biblionumber. + my $ReviewsCount = numberofreviewsbybiblionumber($biblionumber); + ok($ReviewsCount==1,'There is one approved comment for this biblio.'); + + # Unapprove the comment. + unapprovereview($reviewid); + $UnapprovedReviewsCount = numberofreviews(0); + ok($UnapprovedReviewsCount==1,'There is one unapproved comment.'); + $ApprovedReviewsCount = numberofreviews(1); + ok($ApprovedReviewsCount==0,'There are no approved comments.'); + + # Delete the review. + deletereview($reviewid); + $UnapprovedReviewsCount = numberofreviews(0); + ok($UnapprovedReviewsCount==0,'There are no unapproved comments.'); + $ApprovedReviewsCount = numberofreviews(1); + ok($ApprovedReviewsCount==0,'There are no approved comments.'); + + my $ApprovedReviews = getreviews($biblionumber,1); + my $UnapprovedReviews = getreviews($biblionumber,0); + ok(scalar @$ApprovedReviews==0,'No approved comment records.'); + ok(scalar @$UnapprovedReviews==0,'No unapproved comment records.'); + + # Only save, update, and SendReviewAlert should trigger letters. + SendReviewAlert( { 'review' => 'A sample review unused.', + 'biblionumber' => $biblionumber, + 'borrowernumber' => $borrowernumber }); + + $sth = $dbh->prepare("SELECT COUNT(*) FROM message_queue;"); + $sth->execute(); + my $LetterCount = $sth->fetchrow(); + ok($LetterCount==3,'There are three triggered reminders as expected.'); +} + +$dbh->rollback(); --