Attachment #37615 for bug #13969

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




use vars qw($VERSION @ISA @EXPORT);

BEGIN {
  # set the version for version checking
    $VERSION = 3.07.00.049;
  require Exporter;
  @ISA    = qw(Exporter);
  @EXPORT = qw(getreview savereview updatereview numberofreviews numberofreviewsbybiblionumber
    getreviews getallreviews approvereview unapprovereview deletereview);
}

=head1 NAME

  updatereview($biblionumber,$borrowernumber,$review);
  my $count=numberofreviews($status);
  my $count=numberofreviewsbybiblionumber($biblionumber);
  my $reviews=getreviews($biblionumber, $status);
  my $reviews=getallreviews($status, [$offset], [$row_count]);

=head1 DESCRIPTION


    return $sth->fetchrow_hashref();
}

=head2 savereview

  savereview($biblionumber,$borrowernumber, $review);

Save a review in the 'reviews' database 

=cut

sub savereview {
    my ( $biblionumber, $borrowernumber, $review ) = @_;
    my $dbh   = C4::Context->dbh;
    my $query = "INSERT INTO reviews (borrowernumber,biblionumber,
  review,approved,datereviewed) VALUES 
  (?,?,?,0,now())";
    my $sth = $dbh->prepare($query);
    $sth->execute( $borrowernumber, $biblionumber, $review);
}

=head2 updatereview

  updateview($biblionumber,$borrowernumber, $review);

Update the review description in the 'reviews' database 

=cut

sub updatereview {
    my ( $biblionumber, $borrowernumber, $review ) = @_;
    my $dbh   = C4::Context->dbh;

    $sth->execute( $review, $borrowernumber, $biblionumber );
}

=head2 numberofreviews

  my $count=numberofreviews( [$status] );

Return the number of reviews where in the 'reviews' database : 'approved' = $status  
(By default $status = 1)

=cut

sub numberofreviews {
    my ($param) = @_;
    my $status = (defined($param) ? $param : 1);

  return $sth->fetchrow;
}

=head2 numberofreviewsbybiblionumber

  my $count=numberofreviewsbybiblionumber($biblionumber);

Return the number of reviews approved for a given biblionumber

=cut

sub numberofreviewsbybiblionumber {
    my ($biblionumber) = @_;
    my $dbh            = C4::Context->dbh;

      "SELECT count(*) FROM reviews WHERE biblionumber=? and approved=?";
    my $sth = $dbh->prepare($query);
    $sth->execute( $biblionumber, 1 );
  return $sth->fetchrow;
}

=head2 getreviews

  my $reviews=getreviews($biblionumber, $status);

Return all reviews where in the 'reviews' database : 
'biblionumber' = $biblionumber and 'approved' = $status

=cut

sub getreviews {
    my ( $biblionumber, $approved ) = @_;
    my $dbh   = C4::Context->dbh;
    my $query =
      "SELECT * FROM reviews WHERE biblionumber=? and approved=? order by datereviewed desc";
    my $sth = $dbh->prepare($query);
    $sth->execute( $biblionumber, $approved );
  return $sth->fetchall_arrayref({});
}

=head2 getallreviews

  my $reviews=getallreviews($status, [$offset], [$row_count]);

Return all reviews where in the 'reviews' database : 'approved' = $status

If offset and row_count are fiven, it's return all reviews between the 
$offset position and the ($offset + $row_count) position. 
(By default : $offset = 0 and $row_count = 20)

=cut

sub getallreviews {
    my ($status, $offset, $row_count) = @_;
    my @params = ($status,($offset ? $offset : 0),($row_count ? $row_count : 20));
    my $dbh      = C4::Context->dbh;
    my $query    =
      "SELECT * FROM reviews WHERE approved=? order by datereviewed desc LIMIT ?, ?";
    my $sth = $dbh->prepare($query);
    $sth->execute(@params);
  return $sth->fetchall_arrayref({});
}

=head2 approvereview




#!/usr/bin/perl

# Copyright 2015 BibLibre
#
# 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 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.
#
# You should have received a copy of the GNU General Public License along
# with Koha; if not, see <http://www.gnu.org/licenses>.

use Modern::Perl;
use Test::More tests => 117;
use Data::Dumper;
use Time::Piece; 

BEGIN {
    use_ok ('C4::Biblio');
    use_ok ('C4::Review');
    use_ok ('Koha::Borrower');
    use_ok ('MARC::Record');
}

can_ok(
    'C4::Review', qw(
        getreview
        savereview
        updatereview
        numberofreviews
        numberofreviewsbybiblionumber
        getreviews
        getallreviews
        approvereview
        unapprovereview
        deletereview )
);


my $dbh = C4::Context->dbh;
$dbh->{AutoCommit} = 0;
$dbh->{RaiseError} = 1;
$dbh->do('DELETE FROM reviews');
$dbh->do('DELETE FROM borrowers');

# ---------- Some borrowers for testing -------------------
my $categorycode = Koha::Database->new()->schema()->resultset('Category')->first()->categorycode();
my $branchcode   = Koha::Database->new()->schema()->resultset('Branch')  ->first()->branchcode();

my $b1 = Koha::Borrower->new(
    {
        surname      => 'Borrower 1',
        branchcode   => $branchcode,
        categorycode => $categorycode
    }
);
$b1->store();

my $b2 = Koha::Borrower->new(
    {
        surname      => 'Borrower 2',
        branchcode   => $branchcode,
        categorycode => $categorycode
    }
);
$b2->store();

my $b3 = Koha::Borrower->new(
    {
        surname      => 'Borrower 3',
        branchcode   => $branchcode,
        categorycode => $categorycode
    }
);
$b3->store();


# ---------- Some biblios for testing -------------------
my ( $biblionumber1 ) = AddBiblio( MARC::Record->new, '' );
my ( $biblionumber2 ) = AddBiblio( MARC::Record->new, '' );
my ( $biblionumber3 ) = AddBiblio( MARC::Record->new, '' );


# ---------- Some reviews for testing -------------------
my $rev1 = 'Review 1';
my $rev2 = 'Review 2';
my $rev3 = 'Review 3';


# ---------- Testing savereview ---------------------------
my $date = Time::Piece::localtime->strftime('%F %T');

savereview($biblionumber1,$b1->borrowernumber,$rev1);

my $query = '
  SELECT count(*) 
  FROM reviews
';
my $sth = $dbh->prepare($query);
$sth->execute;
my $count = $sth->fetchrow_array;
is ($count, 1, 'There is 1 review');

$query = '
  SELECT reviewid, borrowernumber, biblionumber, review, approved, datereviewed
  FROM reviews
';
$sth = $dbh->prepare($query);
$sth->execute;
my ($reviewid, $borrowernumber, $biblionumber, $review, $approved, $datereviewed) = $sth->fetchrow_array;
is ($borrowernumber, $b1->borrowernumber, 'borrowernumber field is good'  );
is ($biblionumber,   $biblionumber1,      'biblionumber field is good'    );
is ($review,         $rev1,               'review field is good'          );
is ($approved,       0,                   'approved field is 0 by default');
is ($datereviewed,   $date,               'datereviewed field is good'    );

# We add some others reviews
savereview($biblionumber1,$b2->borrowernumber,$rev2);
savereview($biblionumber3,$b2->borrowernumber,$rev3);


# ---------- Testing getreview ----------------------------
my $review1 = getreview($biblionumber1,$b1->borrowernumber);
my $review2 = getreview($biblionumber1,$b2->borrowernumber);
my $review3 = getreview($biblionumber3,$b2->borrowernumber);

$query = '
  SELECT count(*) 
  FROM reviews
';
$sth = $dbh->prepare($query);
$sth->execute;
$count = $sth->fetchrow_array;
is ($count, 3, 'There are 3 reviews');

isa_ok ($review1, 'HASH', '$review1 is defined as a hash');
is ($review1->{borrowernumber}, $b1->borrowernumber, 'borrowernumber field is good'  );
is ($review1->{biblionumber},   $biblionumber1,      'biblionumber field is good'    );
is ($review1->{review},         $rev1,               'review field is good'          );
is ($review1->{approved},       0,                   'approved field is 0 by default');
cmp_ok ($review1->{datereviewed}, 'ge' ,$date , 'datereviewed field is good');

isa_ok ($review2, 'HASH', '$review2 is defined as a hash');
is ($review2->{borrowernumber}, $b2->borrowernumber, 'borrowernumber field is good'  );
is ($review2->{biblionumber},   $biblionumber1,      'biblionumber field is good'    );
is ($review2->{review},         $rev2,               'review field is good'          );
is ($review2->{approved},       0,                   'approved field is 0 by default');
cmp_ok ($review2->{datereviewed}, 'ge' ,$date , 'datereviewed field is good');

isa_ok ($review3, 'HASH', '$review3 is defined as a hash');
is ($review3->{borrowernumber}, $b2->borrowernumber, 'borrowernumber field is good'  );
is ($review3->{biblionumber},   $biblionumber3,      'biblionumber field is good'    );
is ($review3->{review},         $rev3,               'review field is good'          );
is ($review3->{approved},       0,                   'approved field is 0 by default');
cmp_ok ($review3->{datereviewed}, 'ge' ,$date , 'datereviewed field is good');


# ---------- Testing getreviews ---------------------------
my $status = 0;
my $reviews = getreviews($biblionumber1, $status);

$query = '
  SELECT count(*) 
  FROM reviews
  WHERE biblionumber = ?
    AND approved = ?
';
$sth = $dbh->prepare($query);
$sth->execute($biblionumber1, $status);
$count = $sth->fetchrow_array;
is ($count, 2, 'There are 2 reviews corresponding');

isa_ok ($reviews, 'ARRAY', '$reviews is defined as an array');

isa_ok ($reviews->[0], 'HASH', '$reviews->[0] is defined as a hash');
is ($reviews->[0]->{reviewid},       $review1->{reviewid},       'reviewid field is good'        );
is ($reviews->[0]->{borrowernumber}, $review1->{borrowernumber}, 'borrowernumber field is good'  );
is ($reviews->[0]->{biblionumber},   $review1->{biblionumber},   'biblionumber field is good'    );
is ($reviews->[0]->{review},         $review1->{review},         'review field is good'          );
is ($reviews->[0]->{approved},       $review1->{approved},       'approved field is 0 by default');
cmp_ok ($reviews->[0]->{datereviewed}, 'ge', $date, 'datereviewed field is good');

isa_ok ($reviews->[1], 'HASH', '$reviews->[1] is defined as a hash');
is ($reviews->[1]->{reviewid},       $review2->{reviewid},       'reviewid field is good'        );
is ($reviews->[1]->{borrowernumber}, $review2->{borrowernumber}, 'borrowernumber field is good'  );
is ($reviews->[1]->{biblionumber},   $review2->{biblionumber},   'biblionumber field is good'    );
is ($reviews->[1]->{review},         $review2->{review},         'review field is good'          );
is ($reviews->[1]->{approved},       $review2->{approved},       'approved field is 0 by default');
cmp_ok ($reviews->[1]->{datereviewed}, 'ge', $date, 'datereviewed field is good');

$status = 1;
$reviews = getreviews($biblionumber1, $status);
isa_ok ($reviews, 'ARRAY', '$reviews is defined as an array');
is_deeply($reviews, [], '$reviews is empty, there is no approved review' );


# ---------- Testing getallreviews ------------------------
$status = 1;
$reviews = getallreviews($status);
isa_ok ($reviews, 'ARRAY', '$reviews is defined as an array');
is_deeply($reviews, [], '$reviews is empty, there is no approved review' );


$status = 0;
$reviews = getallreviews($status);

$query = '
  SELECT count(*) 
  FROM reviews
  WHERE approved = ?
';
$sth = $dbh->prepare($query);
$sth->execute($status);
$count = $sth->fetchrow_array;
is ($count, 3, 'There are 3 reviews corresponding');

my $count2 = numberofreviews($status);
is ($count2, $count, 'number of reviews returned is good');


isa_ok ($reviews, 'ARRAY', '$reviews is defined as an array');

isa_ok ($reviews->[0], 'HASH', '$reviews->[0] is defined as a hash');
is ($reviews->[0]->{reviewid},       $review1->{reviewid},       'reviewid field is good'        );
is ($reviews->[0]->{borrowernumber}, $review1->{borrowernumber}, 'borrowernumber field is good'  );
is ($reviews->[0]->{biblionumber},   $review1->{biblionumber},   'biblionumber field is good'    );
is ($reviews->[0]->{review},         $review1->{review},         'review field is good'          );
is ($reviews->[0]->{approved},       $review1->{approved},       'approved field is 0 by default');
cmp_ok ($reviews->[0]->{datereviewed}, 'ge' ,$date , 'datereviewed field is good');

isa_ok ($reviews->[1], 'HASH', '$reviews->[1] is defined as a hash');
is ($reviews->[1]->{reviewid},       $review2->{reviewid},       'reviewid field is good'        );
is ($reviews->[1]->{borrowernumber}, $review2->{borrowernumber}, 'borrowernumber field is good'  );
is ($reviews->[1]->{biblionumber},   $review2->{biblionumber},   'biblionumber field is good'    );
is ($reviews->[1]->{review},         $review2->{review},         'review field is good'          );
is ($reviews->[1]->{approved},       $review2->{approved},       'approved field is 0 by default');
cmp_ok ($reviews->[1]->{datereviewed}, 'ge' ,$date , 'datereviewed field is good');

isa_ok ($reviews->[2], 'HASH', '$reviews->[2] is defined as a hash');
is ($reviews->[2]->{reviewid},       $review3->{reviewid},       'reviewid field is good'        );
is ($reviews->[2]->{borrowernumber}, $review3->{borrowernumber}, 'borrowernumber field is good'  );
is ($reviews->[2]->{biblionumber},   $review3->{biblionumber},   'biblionumber field is good'    );
is ($reviews->[2]->{review},         $review3->{review},         'review field is good'          );
is ($reviews->[2]->{approved},       $review3->{approved},       'approved field is 0 by default');
cmp_ok ($reviews->[2]->{datereviewed}, 'ge' ,$date , 'datereviewed field is good');

my $offset = 1;
my $row_count = 1;
$reviews = getallreviews($status, $offset);
is (@$reviews, 2, 'There are only 2 Reviews here');
is_deeply ($reviews->[0], $review2, 'We have Review2...');
is_deeply ($reviews->[1], $review3, '...and Review3');

$reviews = getallreviews($status, $offset, $row_count);
is (@$reviews, 1, 'There is only 1 Review here');
is_deeply ($reviews->[0], $review2, 'We have only Review2');


# ---------- Testing numberofreviews ----------------------
$status = 0;
$count = numberofreviews($status);
is($count, 3, 'There are 3 reviews where approved = 0');

$status = 1;
$count = numberofreviews($status);
is($count, 0, 'There is no review where approved = 0');

$count = numberofreviews();
is($count, 0, 'There is no review where approved = 0 (Default)');


# ---------- Testing approvereview ------------------------
is($review1->{approved}, 0, 'review1 is not approved');
approvereview($review1->{reviewid});
$review1 = getreview($biblionumber1,$b1->borrowernumber);
is($review1->{approved}, 1, 'review1 is approved');

is($review2->{approved}, 0, 'review2 is not approved');
approvereview($review2->{reviewid});
$review2 = getreview($biblionumber1,$b2->borrowernumber);
is($review2->{approved}, 1, 'review2 is approved');

is($review3->{approved}, 0, 'review3 is not approved');
approvereview($review3->{reviewid});
$review3 = getreview($biblionumber3,$b2->borrowernumber);
is($review3->{approved}, 1, 'review3 is approved');

$status = 1;
$reviews = getallreviews($status);

$count = numberofreviews($status);
is ($count, 3, '3 reviews are approved');

$status = 0;
$count = numberofreviews($status);
is ($count, 0, 'No review are not approved');


# ---------- Testing unapprovereview ----------------------
is($review1->{approved}, 1, 'review1 is approved');
unapprovereview($review1->{reviewid});
$review1 = getreview($biblionumber1,$b1->borrowernumber);
is($review1->{approved}, 0, 'review1 is not approved');

is($review2->{approved}, 1, 'review2 is approved');
unapprovereview($review2->{reviewid});
$review2 = getreview($biblionumber1,$b2->borrowernumber);
is($review2->{approved}, 0, 'review2 is not approved');

is($review3->{approved}, 1, 'review3 is approved');
unapprovereview($review3->{reviewid});
$review3 = getreview($biblionumber3,$b2->borrowernumber);
is($review3->{approved}, 0, 'review3 is not approved');

$status = 0;
$reviews = getallreviews($status);

$count = numberofreviews($status);
is ($count, 3, '3 reviews are not approved');

$status = 1;
$count = numberofreviews($status);
is ($count, 0, 'No review are approved');


# ---------- Testing numberofreviewsbybiblionumber --------
approvereview($review1->{reviewid});
approvereview($review2->{reviewid});
approvereview($review3->{reviewid});

$biblionumber = $biblionumber1;
$count = numberofreviewsbybiblionumber($biblionumber);
is($count, 2, 'There are 2 reviews for biblionumber1 and approved = 1');

$biblionumber = $biblionumber2;
$count = numberofreviewsbybiblionumber($biblionumber);
is($count, 0, 'There is no review for biblionumber2 and  approved = 1');

$biblionumber = $biblionumber3;
$count = numberofreviewsbybiblionumber($biblionumber);
is($count, 1, 'There 1 review for biblionumber3 and approved = 1');

unapprovereview($review1->{reviewid});
unapprovereview($review3->{reviewid});

$biblionumber = $biblionumber1;
$count = numberofreviewsbybiblionumber($biblionumber);
is($count, 1, 'There is 1 review for biblionumber1 and approved = 1');

$biblionumber = $biblionumber2;
$count = numberofreviewsbybiblionumber($biblionumber);
is($count, 0, 'There is no review for biblionumber2 and  approved = 1');

$biblionumber = $biblionumber3;
$count = numberofreviewsbybiblionumber($biblionumber);
is($count, 0, 'There is no review for biblionumber3 and approved = 1');


# ---------- Testing updatereview -------------------------
my $rev1b = 'Review 1 bis';
my $rev2b = 'Review 2 bis';
my $rev3b = 'Review 3 bis';

is ($review1->{review}, $rev1, 'review field is "Review 1"');
updatereview($biblionumber1,$b1->borrowernumber,$rev1b);
$review1 = getreview($biblionumber1, $b1->borrowernumber);
is ($review1->{review}, $rev1b, 'review field is "Review 1 bis"');

is ($review2->{review}, $rev2, 'review field is "Review 2"');
updatereview($biblionumber1,$b2->borrowernumber,$rev2b);
$review2 = getreview($biblionumber1, $b2->borrowernumber);
is ($review2->{review}, $rev2b, 'review field is "Review 2 bis"');

is ($review3->{review}, $rev3, 'review field is "Review 3"');
updatereview($biblionumber3,$b2->borrowernumber,$rev3b);
$review3 = getreview($biblionumber3, $b2->borrowernumber);
is ($review3->{review}, $rev3b, 'review field is "Review 3 bis"');


# ---------- Testing deletereview -------------------------
my $status0 = 0;
my $status1 = 1;

my $numberOfReviews = numberofreviews($status0) + numberofreviews($status1);
is ($numberOfReviews, 3, 'There are 3 reviews in database');

deletereview($review1->{reviewid});
$review1 = getreview($biblionumber1, $b3->borrowernumber);
ok (!defined($review1), 'Review1 is no longer defined');

$numberOfReviews = numberofreviews($status0) + numberofreviews($status1);
is ($numberOfReviews, 2, 'There are 2 reviews left in database');

deletereview($review2->{reviewid});
$review2 = getreview($biblionumber2, $b2->borrowernumber);
ok (!defined($review2), 'Review2 is no longer defined');

$numberOfReviews = numberofreviews($status0) + numberofreviews($status1);
is ($numberOfReviews, 1, 'There is 1 review left in database');

deletereview($review3->{reviewid});
$review3 = getreview($biblionumber3, $b2->borrowernumber);
ok (!defined($review3), 'Review3 is no longer defined');

$numberOfReviews = numberofreviews($status0) + numberofreviews($status1);
is ($numberOfReviews, 0, 'There is no review left in database');


$dbh->rollback;

Return to bug 13969

Lines 25-36 use C4::Context; Link Here

(-)a/C4/Review.pm (-14 / +68 lines)
25	use vars qw($VERSION @ISA @EXPORT);	25	use vars qw($VERSION @ISA @EXPORT);
26		26
27	BEGIN {	27	BEGIN {
28	# set the version for version checking	28	# set the version for version checking
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 numberofreviewsbybiblionumber
33	getreviews getallreviews approvereview unapprovereview deletereview);	33	getreviews getallreviews approvereview unapprovereview deletereview);
34	}	34	}
35		35
36	=head1 NAME	36	=head1 NAME
Lines 46-53 C4::Review - Perl Module containing routines for dealing with reviews of items Link Here
46	updatereview($biblionumber,$borrowernumber,$review);	46	updatereview($biblionumber,$borrowernumber,$review);
47	my $count=numberofreviews($status);	47	my $count=numberofreviews($status);
48	my $count=numberofreviewsbybiblionumber($biblionumber);	48	my $count=numberofreviewsbybiblionumber($biblionumber);
49	my $reviews=getreviews($biblionumber);	49	my $reviews=getreviews($biblionumber, $status);
50	my $reviews=getallreviews($status);	50	my $reviews=getallreviews($status, [$offset], [$row_count]);
51		51
52	=head1 DESCRIPTION	52	=head1 DESCRIPTION
53		53
Lines 73-88 sub getreview { Link Here
73	return $sth->fetchrow_hashref();	73	return $sth->fetchrow_hashref();
74	}	74	}
75		75
		76	=head2 savereview
		77
		78	savereview($biblionumber,$borrowernumber, $review);
		79
		80	Save a review in the 'reviews' database
		81
		82	=cut
		83
76	sub savereview {	84	sub savereview {
77	my ( $biblionumber, $borrowernumber, $review ) = @_;	85	my ( $biblionumber, $borrowernumber, $review ) = @_;
78	my $dbh = C4::Context->dbh;	86	my $dbh = C4::Context->dbh;
79	my $query = "INSERT INTO reviews (borrowernumber,biblionumber,	87	my $query = "INSERT INTO reviews (borrowernumber,biblionumber,
80	review,approved,datereviewed) VALUES	88	review,approved,datereviewed) VALUES
81	(?,?,?,0,now())";	89	(?,?,?,0,now())";
82	my $sth = $dbh->prepare($query);	90	my $sth = $dbh->prepare($query);
83	$sth->execute( $borrowernumber, $biblionumber, $review);	91	$sth->execute( $borrowernumber, $biblionumber, $review);
84	}	92	}
85		93
		94	=head2 updatereview
		95
		96	updateview($biblionumber,$borrowernumber, $review);
		97
		98	Update the review description in the 'reviews' database
		99
		100	=cut
		101
86	sub updatereview {	102	sub updatereview {
87	my ( $biblionumber, $borrowernumber, $review ) = @_;	103	my ( $biblionumber, $borrowernumber, $review ) = @_;
88	my $dbh = C4::Context->dbh;	104	my $dbh = C4::Context->dbh;
Lines 91-96 sub updatereview { Link Here
91	$sth->execute( $review, $borrowernumber, $biblionumber );	107	$sth->execute( $review, $borrowernumber, $biblionumber );
92	}	108	}
93		109
		110	=head2 numberofreviews
		111
		112	my $count=numberofreviews( [$status] );
		113
		114	Return the number of reviews where in the 'reviews' database : 'approved' = $status
		115	(By default $status = 1)
		116
		117	=cut
		118
94	sub numberofreviews {	119	sub numberofreviews {
95	my ($param) = @_;	120	my ($param) = @_;
96	my $status = (defined($param) ? $param : 1);	121	my $status = (defined($param) ? $param : 1);
Lines 102-107 sub numberofreviews { Link Here
102	return $sth->fetchrow;	127	return $sth->fetchrow;
103	}	128	}
104		129
		130	=head2 numberofreviewsbybiblionumber
		131
		132	my $count=numberofreviewsbybiblionumber($biblionumber);
		133
		134	Return the number of reviews approved for a given biblionumber
		135
		136	=cut
		137
105	sub numberofreviewsbybiblionumber {	138	sub numberofreviewsbybiblionumber {
106	my ($biblionumber) = @_;	139	my ($biblionumber) = @_;
107	my $dbh = C4::Context->dbh;	140	my $dbh = C4::Context->dbh;
Lines 109-136 sub numberofreviewsbybiblionumber { Link Here
109	"SELECT count(*) FROM reviews WHERE biblionumber=? and approved=?";	142	"SELECT count(*) FROM reviews WHERE biblionumber=? and approved=?";
110	my $sth = $dbh->prepare($query);	143	my $sth = $dbh->prepare($query);
111	$sth->execute( $biblionumber, 1 );	144	$sth->execute( $biblionumber, 1 );
112	return $sth->fetchrow;	145	return $sth->fetchrow;
113	}	146	}
114		147
		148	=head2 getreviews
		149
		150	my $reviews=getreviews($biblionumber, $status);
		151
		152	Return all reviews where in the 'reviews' database :
		153	'biblionumber' = $biblionumber and 'approved' = $status
		154
		155	=cut
		156
115	sub getreviews {	157	sub getreviews {
116	my ( $biblionumber, $approved ) = @_;	158	my ( $biblionumber, $approved ) = @_;
117	my $dbh = C4::Context->dbh;	159	my $dbh = C4::Context->dbh;
118	my $query =	160	my $query =
119	"SELECT * FROM reviews WHERE biblionumber=? and approved=? order by datereviewed desc";	161	"SELECT * FROM reviews WHERE biblionumber=? and approved=? order by datereviewed desc";
120	my $sth = $dbh->prepare($query) \|\| warn $dbh->err_str;	162	my $sth = $dbh->prepare($query);
121	$sth->execute( $biblionumber, $approved );	163	$sth->execute( $biblionumber, $approved );
122	return $sth->fetchall_arrayref({});	164	return $sth->fetchall_arrayref({});
123	}	165	}
124		166
		167	=head2 getallreviews
		168
		169	my $reviews=getallreviews($status, [$offset], [$row_count]);
		170
		171	Return all reviews where in the 'reviews' database : 'approved' = $status
		172
		173	If offset and row_count are fiven, it's return all reviews between the
		174	$offset position and the ($offset + $row_count) position.
		175	(By default : $offset = 0 and $row_count = 20)
		176
		177	=cut
		178
125	sub getallreviews {	179	sub getallreviews {
126	my ($status, $offset, $row_count) = @_;	180	my ($status, $offset, $row_count) = @_;
127	my @params = ($status,($offset ? $offset : 0),($row_count ? $row_count : 20));	181	my @params = ($status,($offset ? $offset : 0),($row_count ? $row_count : 20));
128	my $dbh = C4::Context->dbh;	182	my $dbh = C4::Context->dbh;
129	my $query =	183	my $query =
130	"SELECT * FROM reviews WHERE approved=? order by datereviewed desc LIMIT ?, ?";	184	"SELECT * FROM reviews WHERE approved=? order by datereviewed desc LIMIT ?, ?";
131	my $sth = $dbh->prepare($query) \|\| warn $dbh->err_str;	185	my $sth = $dbh->prepare($query);
132	$sth->execute(@params);	186	$sth->execute(@params);
133	return $sth->fetchall_arrayref({});	187	return $sth->fetchall_arrayref({});
134	}	188	}
135		189
136	=head2 approvereview	190	=head2 approvereview

Line 0 Link Here

(-)a/t/db_dependent/Review.t (-1 / +415 lines)
0	-	1	#!/usr/bin/perl
		2
		3	# Copyright 2015 BibLibre
		4	#
		5	# This file is part of Koha.
		6	#
		7	# Koha is free software; you can redistribute it and/or modify it under the
		8	# terms of the GNU General Public License as published by the Free Software
		9	# Foundation; either version 3 of the License, or (at your option) any later
		10	# version.
		11	#
		12	# Koha is distributed in the hope that it will be useful, but WITHOUT ANY
		13	# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
		14	# A PARTICULAR PURPOSE. See the GNU General Public License for more details.
		15	#
		16	# You should have received a copy of the GNU General Public License along
		17	# with Koha; if not, see <http://www.gnu.org/licenses>.
		18
		19	use Modern::Perl;
		20	use Test::More tests => 117;
		21	use Data::Dumper;
		22	use Time::Piece;
		23
		24	BEGIN {
		25	use_ok ('C4::Biblio');
		26	use_ok ('C4::Review');
		27	use_ok ('Koha::Borrower');
		28	use_ok ('MARC::Record');
		29	}
		30
		31	can_ok(
		32	'C4::Review', qw(
		33	getreview
		34	savereview
		35	updatereview
		36	numberofreviews
		37	numberofreviewsbybiblionumber
		38	getreviews
		39	getallreviews
		40	approvereview
		41	unapprovereview
		42	deletereview )
		43	);
		44
		45
		46	my $dbh = C4::Context->dbh;
		47	$dbh->{AutoCommit} = 0;
		48	$dbh->{RaiseError} = 1;
		49	$dbh->do('DELETE FROM reviews');
		50	$dbh->do('DELETE FROM borrowers');
		51
		52	# ---------- Some borrowers for testing -------------------
		53	my $categorycode = Koha::Database->new()->schema()->resultset('Category')->first()->categorycode();
		54	my $branchcode = Koha::Database->new()->schema()->resultset('Branch') ->first()->branchcode();
		55
		56	my $b1 = Koha::Borrower->new(
		57	{
		58	surname => 'Borrower 1',
		59	branchcode => $branchcode,
		60	categorycode => $categorycode
		61	}
		62	);
		63	$b1->store();
		64
		65	my $b2 = Koha::Borrower->new(
66	{
67	surname => 'Borrower 2',
68	branchcode => $branchcode,
69	categorycode => $categorycode
70	}
71	);
72	$b2->store();
73
74	my $b3 = Koha::Borrower->new(
75	{
76	surname => 'Borrower 3',
77	branchcode => $branchcode,
78	categorycode => $categorycode
79	}
80	);
81	$b3->store();
82
83
84	# ---------- Some biblios for testing -------------------
85	my ( $biblionumber1 ) = AddBiblio( MARC::Record->new, '' );
86	my ( $biblionumber2 ) = AddBiblio( MARC::Record->new, '' );
87	my ( $biblionumber3 ) = AddBiblio( MARC::Record->new, '' );
88
89
90	# ---------- Some reviews for testing -------------------
91	my $rev1 = 'Review 1';
92	my $rev2 = 'Review 2';
93	my $rev3 = 'Review 3';
94
95
96	# ---------- Testing savereview ---------------------------
97	my $date = Time::Piece::localtime->strftime('%F %T');
98
99	savereview($biblionumber1,$b1->borrowernumber,$rev1);
100
101	my $query = '
102	SELECT count(*)
103	FROM reviews
104	';
105	my $sth = $dbh->prepare($query);
106	$sth->execute;
107	my $count = $sth->fetchrow_array;
108	is ($count, 1, 'There is 1 review');
109
110	$query = '
111	SELECT reviewid, borrowernumber, biblionumber, review, approved, datereviewed
112	FROM reviews
113	';
114	$sth = $dbh->prepare($query);
115	$sth->execute;
116	my ($reviewid, $borrowernumber, $biblionumber, $review, $approved, $datereviewed) = $sth->fetchrow_array;
117	is ($borrowernumber, $b1->borrowernumber, 'borrowernumber field is good' );
118	is ($biblionumber, $biblionumber1, 'biblionumber field is good' );
119	is ($review, $rev1, 'review field is good' );
120	is ($approved, 0, 'approved field is 0 by default');
121	is ($datereviewed, $date, 'datereviewed field is good' );
122
123	# We add some others reviews
124	savereview($biblionumber1,$b2->borrowernumber,$rev2);
125	savereview($biblionumber3,$b2->borrowernumber,$rev3);
126
127
128	# ---------- Testing getreview ----------------------------
129	my $review1 = getreview($biblionumber1,$b1->borrowernumber);
130	my $review2 = getreview($biblionumber1,$b2->borrowernumber);
131	my $review3 = getreview($biblionumber3,$b2->borrowernumber);
132
133	$query = '
134	SELECT count(*)
135	FROM reviews
136	';
137	$sth = $dbh->prepare($query);
138	$sth->execute;
139	$count = $sth->fetchrow_array;
140	is ($count, 3, 'There are 3 reviews');
141
142	isa_ok ($review1, 'HASH', '$review1 is defined as a hash');
143	is ($review1->{borrowernumber}, $b1->borrowernumber, 'borrowernumber field is good' );
144	is ($review1->{biblionumber}, $biblionumber1, 'biblionumber field is good' );
145	is ($review1->{review}, $rev1, 'review field is good' );
146	is ($review1->{approved}, 0, 'approved field is 0 by default');
147	cmp_ok ($review1->{datereviewed}, 'ge' ,$date , 'datereviewed field is good');
148
149	isa_ok ($review2, 'HASH', '$review2 is defined as a hash');
150	is ($review2->{borrowernumber}, $b2->borrowernumber, 'borrowernumber field is good' );
151	is ($review2->{biblionumber}, $biblionumber1, 'biblionumber field is good' );
152	is ($review2->{review}, $rev2, 'review field is good' );
153	is ($review2->{approved}, 0, 'approved field is 0 by default');
154	cmp_ok ($review2->{datereviewed}, 'ge' ,$date , 'datereviewed field is good');
155
156	isa_ok ($review3, 'HASH', '$review3 is defined as a hash');
157	is ($review3->{borrowernumber}, $b2->borrowernumber, 'borrowernumber field is good' );
158	is ($review3->{biblionumber}, $biblionumber3, 'biblionumber field is good' );
159	is ($review3->{review}, $rev3, 'review field is good' );
160	is ($review3->{approved}, 0, 'approved field is 0 by default');
161	cmp_ok ($review3->{datereviewed}, 'ge' ,$date , 'datereviewed field is good');
162
163
164	# ---------- Testing getreviews ---------------------------
165	my $status = 0;
166	my $reviews = getreviews($biblionumber1, $status);
167
168	$query = '
169	SELECT count(*)
170	FROM reviews
171	WHERE biblionumber = ?
172	AND approved = ?
173	';
174	$sth = $dbh->prepare($query);
175	$sth->execute($biblionumber1, $status);
176	$count = $sth->fetchrow_array;
177	is ($count, 2, 'There are 2 reviews corresponding');
178
179	isa_ok ($reviews, 'ARRAY', '$reviews is defined as an array');
180
181	isa_ok ($reviews->[0], 'HASH', '$reviews->[0] is defined as a hash');
182	is ($reviews->[0]->{reviewid}, $review1->{reviewid}, 'reviewid field is good' );
183	is ($reviews->[0]->{borrowernumber}, $review1->{borrowernumber}, 'borrowernumber field is good' );
184	is ($reviews->[0]->{biblionumber}, $review1->{biblionumber}, 'biblionumber field is good' );
185	is ($reviews->[0]->{review}, $review1->{review}, 'review field is good' );
186	is ($reviews->[0]->{approved}, $review1->{approved}, 'approved field is 0 by default');
187	cmp_ok ($reviews->[0]->{datereviewed}, 'ge', $date, 'datereviewed field is good');
188
189	isa_ok ($reviews->[1], 'HASH', '$reviews->[1] is defined as a hash');
190	is ($reviews->[1]->{reviewid}, $review2->{reviewid}, 'reviewid field is good' );
191	is ($reviews->[1]->{borrowernumber}, $review2->{borrowernumber}, 'borrowernumber field is good' );
192	is ($reviews->[1]->{biblionumber}, $review2->{biblionumber}, 'biblionumber field is good' );
193	is ($reviews->[1]->{review}, $review2->{review}, 'review field is good' );
194	is ($reviews->[1]->{approved}, $review2->{approved}, 'approved field is 0 by default');
195	cmp_ok ($reviews->[1]->{datereviewed}, 'ge', $date, 'datereviewed field is good');
196
197	$status = 1;
198	$reviews = getreviews($biblionumber1, $status);
199	isa_ok ($reviews, 'ARRAY', '$reviews is defined as an array');
200	is_deeply($reviews, [], '$reviews is empty, there is no approved review' );
201
202
203	# ---------- Testing getallreviews ------------------------
204	$status = 1;
205	$reviews = getallreviews($status);
206	isa_ok ($reviews, 'ARRAY', '$reviews is defined as an array');
207	is_deeply($reviews, [], '$reviews is empty, there is no approved review' );
208
209
210	$status = 0;
211	$reviews = getallreviews($status);
212
213	$query = '
214	SELECT count(*)
215	FROM reviews
216	WHERE approved = ?
217	';
218	$sth = $dbh->prepare($query);
219	$sth->execute($status);
220	$count = $sth->fetchrow_array;
221	is ($count, 3, 'There are 3 reviews corresponding');
222
223	my $count2 = numberofreviews($status);
224	is ($count2, $count, 'number of reviews returned is good');
225
226
227	isa_ok ($reviews, 'ARRAY', '$reviews is defined as an array');
228
229	isa_ok ($reviews->[0], 'HASH', '$reviews->[0] is defined as a hash');
230	is ($reviews->[0]->{reviewid}, $review1->{reviewid}, 'reviewid field is good' );
231	is ($reviews->[0]->{borrowernumber}, $review1->{borrowernumber}, 'borrowernumber field is good' );
232	is ($reviews->[0]->{biblionumber}, $review1->{biblionumber}, 'biblionumber field is good' );
233	is ($reviews->[0]->{review}, $review1->{review}, 'review field is good' );
234	is ($reviews->[0]->{approved}, $review1->{approved}, 'approved field is 0 by default');
235	cmp_ok ($reviews->[0]->{datereviewed}, 'ge' ,$date , 'datereviewed field is good');
236
237	isa_ok ($reviews->[1], 'HASH', '$reviews->[1] is defined as a hash');
238	is ($reviews->[1]->{reviewid}, $review2->{reviewid}, 'reviewid field is good' );
239	is ($reviews->[1]->{borrowernumber}, $review2->{borrowernumber}, 'borrowernumber field is good' );
240	is ($reviews->[1]->{biblionumber}, $review2->{biblionumber}, 'biblionumber field is good' );
241	is ($reviews->[1]->{review}, $review2->{review}, 'review field is good' );
242	is ($reviews->[1]->{approved}, $review2->{approved}, 'approved field is 0 by default');
243	cmp_ok ($reviews->[1]->{datereviewed}, 'ge' ,$date , 'datereviewed field is good');
244
245	isa_ok ($reviews->[2], 'HASH', '$reviews->[2] is defined as a hash');
246	is ($reviews->[2]->{reviewid}, $review3->{reviewid}, 'reviewid field is good' );
247	is ($reviews->[2]->{borrowernumber}, $review3->{borrowernumber}, 'borrowernumber field is good' );
248	is ($reviews->[2]->{biblionumber}, $review3->{biblionumber}, 'biblionumber field is good' );
249	is ($reviews->[2]->{review}, $review3->{review}, 'review field is good' );
250	is ($reviews->[2]->{approved}, $review3->{approved}, 'approved field is 0 by default');
251	cmp_ok ($reviews->[2]->{datereviewed}, 'ge' ,$date , 'datereviewed field is good');
252
253	my $offset = 1;
254	my $row_count = 1;
255	$reviews = getallreviews($status, $offset);
256	is (@$reviews, 2, 'There are only 2 Reviews here');
257	is_deeply ($reviews->[0], $review2, 'We have Review2...');
258	is_deeply ($reviews->[1], $review3, '...and Review3');
259
260	$reviews = getallreviews($status, $offset, $row_count);
261	is (@$reviews, 1, 'There is only 1 Review here');
262	is_deeply ($reviews->[0], $review2, 'We have only Review2');
263
264
265	# ---------- Testing numberofreviews ----------------------
266	$status = 0;
267	$count = numberofreviews($status);
268	is($count, 3, 'There are 3 reviews where approved = 0');
269
270	$status = 1;
271	$count = numberofreviews($status);
272	is($count, 0, 'There is no review where approved = 0');
273
274	$count = numberofreviews();
275	is($count, 0, 'There is no review where approved = 0 (Default)');
276
277
278	# ---------- Testing approvereview ------------------------
279	is($review1->{approved}, 0, 'review1 is not approved');
280	approvereview($review1->{reviewid});
281	$review1 = getreview($biblionumber1,$b1->borrowernumber);
282	is($review1->{approved}, 1, 'review1 is approved');
283
284	is($review2->{approved}, 0, 'review2 is not approved');
285	approvereview($review2->{reviewid});
286	$review2 = getreview($biblionumber1,$b2->borrowernumber);
287	is($review2->{approved}, 1, 'review2 is approved');
288
289	is($review3->{approved}, 0, 'review3 is not approved');
290	approvereview($review3->{reviewid});
291	$review3 = getreview($biblionumber3,$b2->borrowernumber);
292	is($review3->{approved}, 1, 'review3 is approved');
293
294	$status = 1;
295	$reviews = getallreviews($status);
296
297	$count = numberofreviews($status);
298	is ($count, 3, '3 reviews are approved');
299
300	$status = 0;
301	$count = numberofreviews($status);
302	is ($count, 0, 'No review are not approved');
303
304
305	# ---------- Testing unapprovereview ----------------------
306	is($review1->{approved}, 1, 'review1 is approved');
307	unapprovereview($review1->{reviewid});
308	$review1 = getreview($biblionumber1,$b1->borrowernumber);
309	is($review1->{approved}, 0, 'review1 is not approved');
310
311	is($review2->{approved}, 1, 'review2 is approved');
312	unapprovereview($review2->{reviewid});
313	$review2 = getreview($biblionumber1,$b2->borrowernumber);
314	is($review2->{approved}, 0, 'review2 is not approved');
315
316	is($review3->{approved}, 1, 'review3 is approved');
317	unapprovereview($review3->{reviewid});
318	$review3 = getreview($biblionumber3,$b2->borrowernumber);
319	is($review3->{approved}, 0, 'review3 is not approved');
320
321	$status = 0;
322	$reviews = getallreviews($status);
323
324	$count = numberofreviews($status);
325	is ($count, 3, '3 reviews are not approved');
326
327	$status = 1;
328	$count = numberofreviews($status);
329	is ($count, 0, 'No review are approved');
330
331
332	# ---------- Testing numberofreviewsbybiblionumber --------
333	approvereview($review1->{reviewid});
334	approvereview($review2->{reviewid});
335	approvereview($review3->{reviewid});
336
337	$biblionumber = $biblionumber1;
338	$count = numberofreviewsbybiblionumber($biblionumber);
339	is($count, 2, 'There are 2 reviews for biblionumber1 and approved = 1');
340
341	$biblionumber = $biblionumber2;
342	$count = numberofreviewsbybiblionumber($biblionumber);
343	is($count, 0, 'There is no review for biblionumber2 and approved = 1');
344
345	$biblionumber = $biblionumber3;
346	$count = numberofreviewsbybiblionumber($biblionumber);
347	is($count, 1, 'There 1 review for biblionumber3 and approved = 1');
348
349	unapprovereview($review1->{reviewid});
350	unapprovereview($review3->{reviewid});
351
352	$biblionumber = $biblionumber1;
353	$count = numberofreviewsbybiblionumber($biblionumber);
354	is($count, 1, 'There is 1 review for biblionumber1 and approved = 1');
355
356	$biblionumber = $biblionumber2;
357	$count = numberofreviewsbybiblionumber($biblionumber);
358	is($count, 0, 'There is no review for biblionumber2 and approved = 1');
359
360	$biblionumber = $biblionumber3;
361	$count = numberofreviewsbybiblionumber($biblionumber);
362	is($count, 0, 'There is no review for biblionumber3 and approved = 1');
363
364
365	# ---------- Testing updatereview -------------------------
366	my $rev1b = 'Review 1 bis';
367	my $rev2b = 'Review 2 bis';
368	my $rev3b = 'Review 3 bis';
369
370	is ($review1->{review}, $rev1, 'review field is "Review 1"');
371	updatereview($biblionumber1,$b1->borrowernumber,$rev1b);
372	$review1 = getreview($biblionumber1, $b1->borrowernumber);
373	is ($review1->{review}, $rev1b, 'review field is "Review 1 bis"');
374
375	is ($review2->{review}, $rev2, 'review field is "Review 2"');
376	updatereview($biblionumber1,$b2->borrowernumber,$rev2b);
377	$review2 = getreview($biblionumber1, $b2->borrowernumber);
378	is ($review2->{review}, $rev2b, 'review field is "Review 2 bis"');
379
380	is ($review3->{review}, $rev3, 'review field is "Review 3"');
381	updatereview($biblionumber3,$b2->borrowernumber,$rev3b);
382	$review3 = getreview($biblionumber3, $b2->borrowernumber);
383	is ($review3->{review}, $rev3b, 'review field is "Review 3 bis"');
384
385
386	# ---------- Testing deletereview -------------------------
387	my $status0 = 0;
388	my $status1 = 1;
389
390	my $numberOfReviews = numberofreviews($status0) + numberofreviews($status1);
391	is ($numberOfReviews, 3, 'There are 3 reviews in database');
392
393	deletereview($review1->{reviewid});
394	$review1 = getreview($biblionumber1, $b3->borrowernumber);
395	ok (!defined($review1), 'Review1 is no longer defined');
396
397	$numberOfReviews = numberofreviews($status0) + numberofreviews($status1);
398	is ($numberOfReviews, 2, 'There are 2 reviews left in database');
399
400	deletereview($review2->{reviewid});
401	$review2 = getreview($biblionumber2, $b2->borrowernumber);
402	ok (!defined($review2), 'Review2 is no longer defined');
403
404	$numberOfReviews = numberofreviews($status0) + numberofreviews($status1);
405	is ($numberOfReviews, 1, 'There is 1 review left in database');
406
407	deletereview($review3->{reviewid});
408	$review3 = getreview($biblionumber3, $b2->borrowernumber);
409	ok (!defined($review3), 'Review3 is no longer defined');
410
411	$numberOfReviews = numberofreviews($status0) + numberofreviews($status1);
412	is ($numberOfReviews, 0, 'There is no review left in database');
413
414
415	$dbh->rollback;