Я пытаюсь добавить отзыв об отеле, используя вставленную мной базу данных. Я передаю его в тело Postman вот так
{
"userId": "123e4567-e89b-12d3-a456-426614174006",
"hotelId": "bf544691-cc30-484e-9003-386a7c655ddb",
"isActive": true,
"score": 3,
"title": "good",
"body": "good"
}
но всегда возвращается: Не удалось оставить отзыв.
Буду очень рад, если кто-нибудь найдет ошибку или, может быть, это работает, но я просто не проверил это должным образом. . Если я оставил недостаточно кода, чтобы кто-то мог его понять, обязательно дайте мне знать, чтобы я мог обновить.
Уровень контроллера:
public class ReviewController : ControllerBase
{
private readonly IReviewService _reviewService;
public ReviewController(IReviewService reviewService)
{
_reviewService = reviewService;
}
[HttpPost]
[Route("leave-review")]
public async Task LeaveReview([FromBody] Review review)
{
var result = await _reviewService.LeaveReview(review);
if (!result)
{
return BadRequest("Failed to leave a review.");
}
return Ok("Review successfully submitted.");
}
}
Service layer:
public class ReviewService : IReviewService
{
private readonly IReviewRepository _reviewRepository;
private readonly IHotelRepository _hotelRepository;
public ReviewService(IReviewRepository reviewRepository, IHotelRepository hotelRepository)
{
_reviewRepository = reviewRepository;
_hotelRepository = hotelRepository;
}
public async Task LeaveReview(Review review)
{
// Ensure the user can leave a review (reservation must be completed)
if (!await _reviewRepository.CanUserLeaveReview(review.UserId, review.HotelId))
{
return false; // User is not allowed to leave a review
}
// Insert the review
var reviewInserted = await _reviewRepository.AddReview(review);
if (!reviewInserted)
{
return false;
}
// Update hotel average score
var reviews = await _reviewRepository.GetReviewsByHotelId(review.HotelId);
if (reviews != null && reviews.Any())
{
var averageScore = (decimal)reviews.Average(r => r.Score);
await _hotelRepository.UpdateHotelAverageScore(review.HotelId, averageScore);
}
return true;
}
}
Repository layer:
public class ReviewRepository : IReviewRepository
{
private readonly string _connectionString = "Host=localhost;Port=5432;Database=Users;Username=postgres;Password=postgres";
public async Task CanUserLeaveReview(Guid userId, Guid hotelId)
{
using var connection = new NpgsqlConnection(_connectionString);
var commandText = @"
SELECT COUNT(*)
FROM ""Reservation"" r
JOIN ""RoomReservation"" rr ON r.""Id"" = rr.""ReservationId""
JOIN ""Room"" rm ON rr.""RoomId"" = rm.""Id""
WHERE r.""UserId"" = @userId
AND rm.""HotelId"" = @hotelId
AND r.""DateTo"" < NOW()
AND r.""Canceled"" = FALSE";
using var command = new NpgsqlCommand(commandText, connection);
command.Parameters.AddWithValue("@userId", userId);
command.Parameters.AddWithValue("@hotelId", hotelId);
await connection.OpenAsync();
var count = (long)await command.ExecuteScalarAsync();
return count > 0;
}
public async Task AddReview(Review review)
{
try
{
using var connection = new NpgsqlConnection(_connectionString);
var commandText = "INSERT INTO \"Review\" (\"Id\", \"UserId\", \"HotelId\", \"Score\", \"Title\", \"Body\", \"DateCreated\", \"IsActive\") " +
"VALUES (@id, @userId, @hotelId, @score, @title, @body, NOW(), TRUE)";
using var command = new NpgsqlCommand(commandText, connection);
command.Parameters.AddWithValue("@id", Guid.NewGuid());
command.Parameters.AddWithValue("@userId", review.UserId);
command.Parameters.AddWithValue("@hotelId", review.HotelId);
command.Parameters.AddWithValue("@score", review.Score);
command.Parameters.AddWithValue("@title", review.Title);
command.Parameters.AddWithValue("@body", review.Body);
await connection.OpenAsync();
await command.ExecuteNonQueryAsync();
return true;
}
catch (Exception ex)
{
Console.WriteLine($"Error: {ex.Message}");
return false;
}
}
public async Task GetReviewsByHotelId(Guid hotelId)
{
var reviews = new List();
using var connection = new NpgsqlConnection(_connectionString);
var commandText = "SELECT * FROM \"Review\" WHERE \"HotelId\" = @hotelId AND \"IsActive\" = TRUE";
using var command = new NpgsqlCommand(commandText, connection);
command.Parameters.AddWithValue("@hotelId", hotelId);
await connection.OpenAsync();
using var reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
var review = new Review
{
Id = reader.GetGuid(reader.GetOrdinal("Id")),
UserId = reader.GetGuid(reader.GetOrdinal("UserId")),
HotelId = reader.GetGuid(reader.GetOrdinal("HotelId")),
Score = reader.GetInt32(reader.GetOrdinal("Score")),
Title = reader.IsDBNull(reader.GetOrdinal("Title")) ? null : reader.GetString(reader.GetOrdinal("Title")),
Body = reader.IsDBNull(reader.GetOrdinal("Body")) ? null : reader.GetString(reader.GetOrdinal("Body")),
DateCreated = reader.GetDateTime(reader.GetOrdinal("DateCreated")),
IsActive = reader.GetBoolean(reader.GetOrdinal("IsActive"))
};
reviews.Add(review);
}
return reviews;
}
}
Я поставил точку останова в начале метода CanUserLeaveReview(Guid userId, Guid hotelId). Он проходит через весь метод, а затем переходит к LeaveReview (обзор обзора) на уровне сервиса. Проверяет if(!reviewInserted) и возвращает false. Затем возвращается к контроллеру и вводит if (!result), который затем возвращает «Не удалось оставить отзыв». Я новичок в этом, так что угадайте, что источник моей проблемы в AddReview?
DB:CREATE TABLE "User" (
"Id" UUID PRIMARY KEY,
"RoleId" UUID NOT NULL,
"IsActive" BOOLEAN NOT NULL DEFAULT TRUE,
"Username" VARCHAR(30) UNIQUE NOT NULL,
"Password" VARCHAR(30) NOT NULL,
"FirstName" VARCHAR NOT NULL,
"LastName" VARCHAR NOT NULL,
"Email" VARCHAR(40) NOT NULL,
"PhoneNumber" VARCHAR(20) NOT NULL,
"DateCreated" TIMESTAMPTZ NOT NULL,
"DateUpdated" TIMESTAMPTZ
);
CREATE TABLE "Hotel" (
"Id" UUID PRIMARY KEY,
"CityId" UUID NOT NULL,
"MainImageUrl" VARCHAR,
"IsActive" BOOLEAN NOT NULL,
"NumberOfStars" INT,
"AverageScore" DECIMAL,
"Description" TEXT,
"Name" VARCHAR(60) NOT NULL,
"DateCreated" TIMESTAMPTZ NOT NULL,
"DateUpdated" TIMESTAMPTZ,
"CreatedByUserId" UUID,
"UpdatedByUserId" UUID
);
CREATE TABLE "Reservation" (
"Id" UUID PRIMARY KEY,
"UserId" UUID NOT NULL,
"Canceled" BOOLEAN NOT NULL DEFAULT FALSE,
"Price" DECIMAL NOT NULL,
"DateFrom" TIMESTAMPTZ NOT NULL,
"DateTo" TIMESTAMPTZ NOT NULL,
"ReservationTypeId" UUID NOT NULL,
"DateCreated" TIMESTAMPTZ NOT NULL,
"DateUpdated" TIMESTAMPTZ,
"UpdatedByUserId" UUID
);
CREATE TABLE "City" (
"Id" UUID PRIMARY KEY,
"CountryId" UUID NOT NULL,
"Name" VARCHAR NOT NULL,
"DateCreated" TIMESTAMPTZ NOT NULL,
"DateUpdated" TIMESTAMPTZ,
"CreatedByUserId" UUID,
"UpdatedByUserId" UUID
);
CREATE TABLE "Country" (
"Id" UUID PRIMARY KEY,
"Name" VARCHAR NOT NULL,
"DateCreated" TIMESTAMPTZ NOT NULL,
"DateUpdated" TIMESTAMPTZ,
"CreatedByUserId" UUID,
"UpdatedByUserId" UUID
);
CREATE TABLE "Room" (
"Id" UUID PRIMARY KEY,
"RoomTypeId" UUID NOT NULL,
"IsActive" BOOLEAN NOT NULL DEFAULT TRUE,
"RoomNumber" INT NOT NULL,
"HotelId" UUID REFERENCES "Hotel"("Id"),
"Price" DECIMAL NOT NULL,
"DateCreated" TIMESTAMPTZ NOT NULL,
"DateUpdated" TIMESTAMPTZ,
"CreatedByUserId" UUID,
"UpdatedByUserId" UUID
);
CREATE TABLE "HotelManager"(
"ManagerUserId" UUID PRIMARY KEY,
"HotelId" UUID
);
CREATE TABLE "Review" (
"Id" UUID PRIMARY KEY,
"UserId" UUID NOT NULL,
"HotelId" UUID NOT NULL,
"IsActive" BOOLEAN NOT NULL DEFAULT TRUE,
"Score" INT NOT NULL,
"Title" VARCHAR(50),
"Body" TEXT,
"DateCreated" TIMESTAMPTZ NOT NULL,
"DateUpdated" TIMESTAMPTZ,
"UpdatedByUserId" UUID
);
CREATE TABLE "RoomReservation" (
"Id" UUID PRIMARY KEY,
"RoomId" UUID NOT NULL,
"ReservationId" UUID NOT NULL,
"DateCreated" TIMESTAMPTZ NOT NULL,
"DateUpdated" TIMESTAMPTZ,
"CreatedByUserId" UUID,
"UpdatedByUserId" UUID
);
CREATE TABLE "RoomType" (
"Id" UUID PRIMARY KEY,
"Type" VARCHAR(60) NOT NULL,
"NumberOfBeds" INT NOT NULL,
"DateCreated" TIMESTAMPTZ NOT NULL,
"DateUpdated" TIMESTAMPTZ,
"CreatedByUserId" UUID,
"UpdatedByUserId" UUID
);
CREATE TABLE "Amenities" (
"Id" UUID PRIMARY KEY,
"Name" VARCHAR NOT NULL,
"DateCreated" TIMESTAMPTZ NOT NULL,
"DateUpdated" TIMESTAMPTZ,
"CreatedByUserId" UUID,
"UpdatedByUserId" UUID
);
CREATE TABLE "HotelAmenities" (
"Id" UUID PRIMARY KEY,
"HotelId" UUID NOT NULL,
"AmenityId" UUID NOT NULL,
"DateCreated" TIMESTAMPTZ NOT NULL,
"IsActive" BOOLEAN NOT NULL DEFAULT TRUE,
"DateUpdated" TIMESTAMPTZ,
"CreatedByUserId" UUID,
"UpdatedByUserId" UUID
);
CREATE TABLE "Role" (
"Id" UUID PRIMARY KEY,
"Name" VARCHAR NOT NULL,
"DateCreated" TIMESTAMPTZ NOT NULL,
"DateUpdated" TIMESTAMPTZ,
"CreatedByUserId" UUID,
"UpdatedByUserId" UUID
);
CREATE TABLE "HotelImage" (
"Id" UUID PRIMARY KEY,
"HotelId" UUID NOT NULL,
"Url" VARCHAR NOT NULL,
"DateCreated" TIMESTAMPTZ NOT NULL,
"DateUpdated" TIMESTAMPTZ,
"CreatedByUserId" UUID,
"UpdatedByUserId" UUID
);
CREATE TABLE "RoomImage" (
"Id" UUID PRIMARY KEY,
"RoomId" UUID NOT NULL,
"Url" VARCHAR NOT NULL,
"DateCreated" TIMESTAMPTZ NOT NULL,
"DateUpdated" TIMESTAMPTZ,
"CreatedByUserId" UUID,
"UpdatedByUserId" UUID
);
CREATE TABLE "ReservationType" (
"Id" UUID PRIMARY KEY,
"Type" VARCHAR NOT NULL,
"DateCreated" TIMESTAMPTZ NOT NULL,
"DateUpdated" TIMESTAMPTZ,
"CreatedByUserId" UUID,
"UpdatedByUserId" UUID
);
CREATE TABLE "HotelReservationType" (
"Id" UUID PRIMARY KEY,
"ReservationTypeId" UUID NOT NULL,
"HotelId" UUID NOT NULL,
"Price" DECIMAL NOT NULL,
"DateCreated" TIMESTAMPTZ NOT NULL,
"DateUpdated" TIMESTAMPTZ,
"CreatedByUserId" UUID,
"UpdatedByUserId" UUID
);
Подробнее здесь: https://stackoverflow.com/questions/790 ... ific-hotel
