View | Details | Raw Unified | Return to bug 1985
Collapse All | Expand All

(-)a/C4/Letters.pm (-1 / +5 lines)
Lines 638-646 sub GetPreparedLetter { Link Here
638
       }
638
       }
639
    }
639
    }
640
640
641
    my $OPACBaseURL = C4::Context->preference('OPACBaseURL');
641
    my $OPACBaseURL = C4::Context->preference('OPACBaseURL') // '';
642
    $letter->{content} =~ s/<<OPACBaseURL>>/$OPACBaseURL/go;
642
    $letter->{content} =~ s/<<OPACBaseURL>>/$OPACBaseURL/go;
643
643
644
    my $staffClientBaseURL = C4::Context->preference('staffClientBaseURL') // '';
645
    $letter->{content} =~ s/<<staffClientBaseURL>>/$staffClientBaseURL/go;
646
644
    if ($want_librarian) {
647
    if ($want_librarian) {
645
        # parsing librarian name
648
        # parsing librarian name
646
        my $userenv = C4::Context->userenv;
649
        my $userenv = C4::Context->userenv;
Lines 753-758 sub _parseletter_sth { Link Here
753
    ($table eq 'aqbooksellers') ? "SELECT * FROM $table WHERE             id = ?"                                  :
756
    ($table eq 'aqbooksellers') ? "SELECT * FROM $table WHERE             id = ?"                                  :
754
    ($table eq 'aqorders'     ) ? "SELECT * FROM $table WHERE    ordernumber = ?"                                  :
757
    ($table eq 'aqorders'     ) ? "SELECT * FROM $table WHERE    ordernumber = ?"                                  :
755
    ($table eq 'opac_news'    ) ? "SELECT * FROM $table WHERE          idnew = ?"                                  :
758
    ($table eq 'opac_news'    ) ? "SELECT * FROM $table WHERE          idnew = ?"                                  :
759
    ($table eq 'reviews'      ) ? "SELECT * FROM $table WHERE       reviewid = ?"                                  :
756
    ($table eq 'borrower_modifications') ? "SELECT * FROM $table WHERE verification_token = ?" :
760
    ($table eq 'borrower_modifications') ? "SELECT * FROM $table WHERE verification_token = ?" :
757
    undef ;
761
    undef ;
758
    unless ($query) {
762
    unless ($query) {
(-)a/C4/Review.pm (-10 / +37 lines)
Lines 17-26 package C4::Review; Link Here
17
# You should have received a copy of the GNU General Public License
17
# You should have received a copy of the GNU General Public License
18
# along with Koha; if not, see <http://www.gnu.org/licenses>.
18
# along with Koha; if not, see <http://www.gnu.org/licenses>.
19
19
20
use strict;
20
use Modern::Perl;
21
use warnings;
22
21
23
use C4::Context;
22
use C4::Context;
23
require C4::Letters;
24
24
25
use vars qw($VERSION @ISA @EXPORT);
25
use vars qw($VERSION @ISA @EXPORT);
26
26
Lines 29-36 BEGIN { Link Here
29
    $VERSION = 3.07.00.049;
29
    $VERSION = 3.07.00.049;
30
	require Exporter;
30
	require Exporter;
31
	@ISA    = qw(Exporter);
31
	@ISA    = qw(Exporter);
32
	@EXPORT = qw(getreview savereview updatereview numberofreviews numberofreviewsbybiblionumber
32
    @EXPORT = qw(getreview savereview updatereview numberofreviews
33
		getreviews getallreviews approvereview unapprovereview deletereview);
33
                 numberofreviewsbybiblionumber getreviews
34
                 getallreviews approvereview unapprovereview
35
                 deletereview SendReviewAlert);
34
}
36
}
35
37
36
=head1 NAME
38
=head1 NAME
Lines 79-89 sub savereview { Link Here
79
    my $query = "INSERT INTO reviews (borrowernumber,biblionumber,
81
    my $query = "INSERT INTO reviews (borrowernumber,biblionumber,
80
	review,approved,datereviewed) VALUES 
82
	review,approved,datereviewed) VALUES 
81
  (?,?,?,0,now())";
83
  (?,?,?,0,now())";
84
    my $GetReviewID = q{
85
SELECT reviewid FROM reviews WHERE borrowernumber=? AND biblionumber=?;
86
                      };
82
    my $sth = $dbh->prepare($query);
87
    my $sth = $dbh->prepare($query);
83
    $sth->execute( $borrowernumber, $biblionumber, $review);
88
    $sth->execute( $borrowernumber, $biblionumber, $review);
89
    $sth = $dbh->prepare($GetReviewID);
90
    $sth->execute( $borrowernumber, $biblionumber );
91
    my $reviewid = $sth->fetchrow();
84
92
85
    unless ($sth->err()) {
93
    unless ($sth->err()) {
86
        SendReviewAlert( $review, $borrowernumber );
94
        SendReviewAlert( {
95
                           'reviewid'       => $reviewid,
96
                           'borrowernumber' => $borrowernumber,
97
                           'biblionumber'   => $biblionumber } );
87
    }
98
    }
88
}
99
}
89
100
Lines 91-101 sub updatereview { Link Here
91
    my ( $biblionumber, $borrowernumber, $review ) = @_;
102
    my ( $biblionumber, $borrowernumber, $review ) = @_;
92
    my $dbh   = C4::Context->dbh;
103
    my $dbh   = C4::Context->dbh;
93
    my $query = "UPDATE reviews SET review=?,datereviewed=now(),approved=0  WHERE borrowernumber=? and biblionumber=?";
104
    my $query = "UPDATE reviews SET review=?,datereviewed=now(),approved=0  WHERE borrowernumber=? and biblionumber=?";
105
    my $GetReviewID = q{
106
SELECT reviewid FROM reviews WHERE borrowernumber=? AND biblionumber=?;
107
                      };
94
    my $sth = $dbh->prepare($query);
108
    my $sth = $dbh->prepare($query);
95
    $sth->execute( $review, $borrowernumber, $biblionumber );
109
    $sth->execute( $review, $borrowernumber, $biblionumber );
110
    $sth = $dbh->prepare($GetReviewID);
111
    $sth->execute( $borrowernumber, $biblionumber );
112
    my $reviewid = $sth->fetchrow();
96
113
97
    unless ($sth->err()) {
114
    unless ($sth->err()) {
98
        SendReviewAlert( $review, $borrowernumber );
115
        SendReviewAlert( {
116
                           'reviewid'       => $reviewid,
117
                           'borrowernumber' => $borrowernumber,
118
                           'biblionumber'   => $biblionumber } );
99
    }
119
    }
100
}
120
}
101
121
Lines 196-218 sub deletereview { Link Here
196
216
197
=head2 SendReviewAlert
217
=head2 SendReviewAlert
198
218
199
  SendReviewAlert( $review, $borrowernumber );
219
  SendReviewAlert( { 'reviewid'       => $reviewid,
220
                     'borrowernumber' => $borrowernumber,
221
                     'biblionumber'   => $biblionumber } );
200
222
201
=cut
223
=cut
202
224
203
sub SendReviewAlert {
225
sub SendReviewAlert {
204
    my $review = shift;
226
    my $params = shift;
205
    my $borrowernumber = shift;
206
227
207
    my $moderatorEmail = C4::Context->preference('CommentModeratorsEmail');
228
    my $moderatorEmail = C4::Context->preference('CommentModeratorsEmail');
208
209
    return unless $moderatorEmail;
229
    return unless $moderatorEmail;
210
230
231
    my $reviewid       = $params->{'reviewid'};
232
    my $borrowernumber = $params->{'borrowernumber'};
233
    my $biblionumber   = $params->{'biblionumber'};
234
211
    my $letter =  C4::Letters::GetPreparedLetter (
235
    my $letter =  C4::Letters::GetPreparedLetter (
212
        module => 'members',
236
        module => 'members',
213
        letter_code => 'COMMENT_CREATED',
237
        letter_code => 'COMMENT_CREATED',
214
        tables => {
238
        tables => {
239
            'reviews'     => $reviewid,
215
            'borrowers'   => $borrowernumber,
240
            'borrowers'   => $borrowernumber,
241
            'biblio'      => $biblionumber,
242
            'biblioitems' => $biblionumber,
216
        }
243
        }
217
    ) or return;
244
    ) or return;
218
245
(-)a/installer/data/mysql/en/mandatory/sample_notices.sql (-2 / +16 lines)
Lines 121-129 If you did not initiate this request, you may safely ignore this one-time messag Link Here
121
);
121
);
122
122
123
INSERT INTO  letter (module, code, branchcode, name, is_html, title, content, message_transport_type)
123
INSERT INTO  letter (module, code, branchcode, name, is_html, title, content, message_transport_type)
124
VALUES ( 'members', 'COMMENT_CREATED', '', 'Comment created notification', '0', 'Comment from <<borrowers.cardnumber>> is waiting for moderation', 'Dear moderator,
124
VALUES ( 'members', 'COMMENT_CREATED', '', 'Comment created notification', '0', 'Comment from <<borrowers.cardnumber>> is waiting for moderation',
125
'Dear moderator,
125
126
126
We want to inform you that borrower <<borrowers.cardnumber>> has just created a new comment.
127
We want to inform you that borrower <<borrowers.cardnumber>> has just created a new comment on <<biblio.title>>, which is biblionumber <<biblio.biblionumber>>.
128
129
--- BEGIN COMMENT ---
130
<<reviews.review>>
131
--- END COMMENT ---
132
133
To approve:
134
http://<<staffClientBaseURL>>/cgi-bin/koha/reviews/reviewswaiting.pl?op=approve&reviewid=<<reviews.reviewid>>
135
136
To delete:
137
http://<<staffClientBaseURL>>/cgi-bin/koha/reviews/reviewswaiting.pl?op=delete&reviewid=<<reviews.reviewid>>
138
139
Or to review all comments:
140
http://<<staffClientBaseURL>>/cgi-bin/koha/reviews/reviewswaiting.pl
127
141
128
Check it out!
142
Check it out!
129
143
(-)a/installer/data/mysql/updatedatabase.pl (-24 / +38 lines)
Lines 8359-8388 if ( CheckVersion($DBversion) ) { Link Here
8359
    SetVersion($DBversion);
8359
    SetVersion($DBversion);
8360
}
8360
}
8361
8361
8362
$DBversion = "3.17.00.XXX";
8363
if ( CheckVersion($DBversion) ) {
8364
    $dbh->do(q|
8365
INSERT INTO  letter (module, code, branchcode, name, is_html, title, content, message_transport_type)
8366
VALUES ( 'members', 'COMMENT_CREATED', '', 'Comment created notification', '0', 'Comment from <<borrowers.cardnumber>> is waiting for moderation', 'Dear moderator,
8367
8368
We want to inform you that borrower <<borrowers.cardnumber>> has just created a new comment.
8369
8370
Check it out!
8371
8372
Your library.',
8373
'email'
8374
);
8375
    |);
8376
    $dbh->do(q|
8377
        INSERT INTO systempreferences
8378
            (variable,value,explanation,options,type)
8379
        VALUES
8380
        ('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')
8381
    |);
8382
    print "Upgrade to $DBversion done (Bug 1985 - Email notification of new OPAC comments)\n";
8383
    SetVersion($DBversion);
8384
}
8385
8386
$DBversion = "3.15.00.041";
8362
$DBversion = "3.15.00.041";
8387
if ( CheckVersion($DBversion) ) {
8363
if ( CheckVersion($DBversion) ) {
8388
    my $name = $dbh->selectcol_arrayref(q|
8364
    my $name = $dbh->selectcol_arrayref(q|
Lines 10414-10419 if ( CheckVersion($DBversion) ) { Link Here
10414
    SetVersion ($DBversion);
10390
    SetVersion ($DBversion);
10415
}
10391
}
10416
10392
10393
$DBversion = "3.19.00.XXX";
10394
if ( CheckVersion($DBversion) ) {
10395
    $dbh->do(q|
10396
INSERT INTO  letter (module, code, branchcode, name, is_html, title, content, message_transport_type)
10397
VALUES ( 'members', 'COMMENT_CREATED', '', 'Comment created notification', '0', 'Comment from <<borrowers.cardnumber>> is waiting for moderation',
10398
'Dear moderator,
10399
10400
We want to inform you that borrower <<borrowers.cardnumber>> has just created a new comment on <<biblio.title>>, which is biblionumber <<biblio.biblionumber>>.
10401
10402
--- BEGIN COMMENT ---
10403
<<reviews.review>>
10404
--- END COMMENT ---
10405
10406
To approve:
10407
http://<<staffClientBaseURL>>/cgi-bin/koha/reviews/reviewswaiting.pl?op=approve&reviewid=<<reviews.reviewid>>
10408
10409
To delete:
10410
http://<<staffClientBaseURL>>/cgi-bin/koha/reviews/reviewswaiting.pl?op=delete&reviewid=<<reviews.reviewid>>
10411
10412
Or to review all comments:
10413
http://<<staffClientBaseURL>>/cgi-bin/koha/reviews/reviewswaiting.pl
10414
10415
Check it out!
10416
10417
Your library.',
10418
'email'
10419
);
10420
    |);
10421
    $dbh->do(q|
10422
        INSERT INTO systempreferences
10423
            (variable,value,explanation,options,type)
10424
        VALUES
10425
        ('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')
10426
    |);
10427
    print "Upgrade to $DBversion done (Bug 1985 - Email notification of new OPAC comments)\n";
10428
    SetVersion($DBversion);
10429
}
10430
10417
# DEVELOPER PROCESS, search for anything to execute in the db_update directory
10431
# DEVELOPER PROCESS, search for anything to execute in the db_update directory
10418
# SEE bug 13068
10432
# SEE bug 13068
10419
# if there is anything in the atomicupdate, read and execute it.
10433
# if there is anything in the atomicupdate, read and execute it.
(-)a/t/db_dependent/Review.t (-1 / +97 lines)
Line 0 Link Here
0
- 
1
#!/usr/bin/perl
2
3
use Modern::Perl;
4
use Test::More tests => 14;
5
6
use_ok('C4::Review');
7
8
my $dbh = C4::Context->dbh;
9
$dbh->{RaiseError} = 1;
10
$dbh->{AutoCommit} = 0;
11
12
my $sth = $dbh->prepare("SELECT * FROM borrowers;");
13
$sth->execute();
14
my $row = $sth->fetchrow_hashref();
15
my $borrowernumber;
16
$borrowernumber = $row->{'borrowernumber'} if $row;
17
$sth = $dbh->prepare("SELECT * FROM biblio;");
18
$sth->execute();
19
$row = $sth->fetchrow_hashref();
20
my $biblionumber;
21
$biblionumber = $row->{'biblionumber'} if $row;
22
23
SKIP: {
24
    skip "Missing borrowers or biblios",13
25
      unless $biblionumber and $borrowernumber;
26
27
    # Make sure there are no reviews
28
    $sth = $dbh->prepare("DELETE FROM reviews;");
29
    $sth->execute();
30
31
    # Clear out the message_queue to make counting easier.
32
    $sth = $dbh->prepare("DELETE FROM message_queue;");
33
    $sth->execute();
34
35
    # Add a review
36
    savereview($biblionumber,$borrowernumber,'A sample review.');
37
38
    # Retrieve review
39
    my $reviews = getallreviews(0);
40
    my $reviewid = $reviews->[0]->{'reviewid'};
41
    my $ReviewText = $reviews->[0]->{'review'};
42
    ok($ReviewText eq 'A sample review.','Saved and retrieved a sample review.');
43
44
    # Update the review
45
    updatereview($biblionumber,$borrowernumber,'A revised review.');
46
47
    # Retrieve it differently.
48
    my $review = getreview($biblionumber,$borrowernumber);
49
    $ReviewText = $review->{'review'};
50
    ok($ReviewText eq 'A revised review.','Updated and retrieved a revised review.');
51
52
    # Confirm 1 unapproved comment.
53
    my $UnapprovedReviewsCount = numberofreviews(0);
54
    ok($UnapprovedReviewsCount==1,'There is one unapproved comment.');
55
56
    # Approve the comment.
57
    approvereview($reviewid);
58
    $UnapprovedReviewsCount = numberofreviews(0);
59
    ok($UnapprovedReviewsCount==0,'There are no unapproved comments.');
60
    my $ApprovedReviewsCount = numberofreviews(1);
61
    ok($ApprovedReviewsCount==1,'There is one approved comment.');
62
63
    # Check how many reviews for this biblionumber.
64
    my $ReviewsCount = numberofreviewsbybiblionumber($biblionumber);
65
    ok($ReviewsCount==1,'There is one approved comment for this biblio.');
66
67
    # Unapprove the comment.
68
    unapprovereview($reviewid);
69
    $UnapprovedReviewsCount = numberofreviews(0);
70
    ok($UnapprovedReviewsCount==1,'There is one unapproved comment.');
71
    $ApprovedReviewsCount = numberofreviews(1);
72
    ok($ApprovedReviewsCount==0,'There are no approved comments.');
73
74
    # Delete the review.
75
    deletereview($reviewid);
76
    $UnapprovedReviewsCount = numberofreviews(0);
77
    ok($UnapprovedReviewsCount==0,'There are no unapproved comments.');
78
    $ApprovedReviewsCount = numberofreviews(1);
79
    ok($ApprovedReviewsCount==0,'There are no approved comments.');
80
81
    my $ApprovedReviews   = getreviews($biblionumber,1);
82
    my $UnapprovedReviews = getreviews($biblionumber,0);
83
    ok(scalar @$ApprovedReviews==0,'No approved comment records.');
84
    ok(scalar @$UnapprovedReviews==0,'No unapproved comment records.');
85
86
    # Only save, update, and SendReviewAlert should trigger letters.
87
    SendReviewAlert( { 'review' => 'A sample review unused.',
88
                       'biblionumber' => $biblionumber,
89
                       'borrowernumber' => $borrowernumber });
90
91
    $sth = $dbh->prepare("SELECT COUNT(*) FROM message_queue;");
92
    $sth->execute();
93
    my $LetterCount = $sth->fetchrow();
94
    ok($LetterCount==3,'There are three triggered reminders as expected.');
95
}
96
97
$dbh->rollback();

Return to bug 1985