///<summary>Implements IUserManagemetService.GetOwnersDogs</summary>
public List <DogForDisplayWithId> GetOwnersDogs(Guid ownersId)
{
using (var connection = new SqlConnection(_connectionString))
{
var query = "SELECT * FROM Dog WHERE Owner = @Id";
var dogs = connection.Query <DogFromDB>(query, new { Id = ownersId }).ToList();
var result = new List <DogForDisplayWithId>();
foreach (var item in dogs)
{
var dog = new DogForDisplayWithId();
dog.Id = item.Id;
dog.Name = item.Name;
dog.Age = item.Age;
dog.Gender = item.Gender;
dog.Breed = GetBreedById(connection, item.Breed);
dog.Owner = item.Owner;
dog.Specifics = item.Specifics;
dog.ProfilePicturePath = item.ProfilePicturePath;
result.Add(dog);
}
return(result);
}
}
///<summary>
///Implements IUserManagemetService.GetAllDogs
///</summary>
public List <DogForDisplayWithId> GetAllDogs(int page, Guid currentOwnerId)
{
using (var connection = new SqlConnection(_connectionString))
{
var query = @"SELECT * FROM Dog
WHERE (NOT Owner=@Owner)
ORDER BY [Name]
OFFSET (@Page-1)*4 ROWS
FETCH NEXT 4 ROWS ONLY";
var dogsFromDB = connection.Query <DogFromDB>(query, new { Page = page, Owner = currentOwnerId }).ToList();
var result = new List <DogForDisplayWithId>();
foreach (var item in dogsFromDB)
{
var dog = new DogForDisplayWithId();
dog.Id = item.Id;
dog.Name = item.Name;
dog.Age = item.Age;
dog.Gender = item.Gender;
dog.Breed = GetBreedById(connection, item.Breed);
dog.Owner = item.Owner;
dog.Specifics = item.Specifics;
dog.ProfilePicturePath = item.ProfilePicturePath;
result.Add(dog);
}
return(result);
}
}
/// <summary>
/// Implements IUserManagemetService.GetLikedDogs
/// </summary>
public List <DogForDisplay> GetLikedDogs(int page, Guid currentOwnerId)
{
using (var connection = new SqlConnection(_connectionString))
{
string query = @"SELECT Dog_ID2 FROM LikedDogs
WHERE User_ID1=@Owner
ORDER BY [User_ID1]
OFFSET (@Page-1)*4 ROWS
FETCH NEXT 4 ROWS ONLY";
var likedDogsGuids = connection.Query <Guid>(query, new
{
Page = page,
Owner = currentOwnerId
}).ToList();
List <DogForDisplay> likedDogs = new List <DogForDisplay>();
foreach (var guid in likedDogsGuids)
{
DogForDisplayWithId dogForDisplay = new DogForDisplayWithId();
string queryForOneDog = @"SELECT * FROM Dog
WHERE Id=@Id";
var dog = connection.QueryFirstOrDefault <DogFromDB>(queryForOneDog, new { Id = guid });
dogForDisplay.Age = dog.Age;
dogForDisplay.Breed = GetBreedById(connection, dog.Breed);
dogForDisplay.Id = dog.Id;
dogForDisplay.Gender = dog.Gender;
dogForDisplay.Name = dog.Name;
dogForDisplay.Owner = dog.Owner;
dogForDisplay.ProfilePicturePath = dog.ProfilePicturePath;
dogForDisplay.Specifics = dog.Specifics;
likedDogs.Add(dogForDisplay);
}
return(likedDogs);
}
}
///<summary>
///Implements IUserManagemetService.FilterDogs
///</summary>
public List <DogForDisplayWithId> FilterDogs(int page, Filters filters, Guid currentOwnerId)
{
using (var connection = new SqlConnection(_connectionString))
{
string cityId = string.Empty;
string breedId = string.Empty;
if (!string.IsNullOrEmpty(filters.Breed))
{
breedId = GetBreedId(connection, filters.Breed).ToString();
}
if (string.IsNullOrEmpty(filters.Gender))
{
filters.Gender = string.Empty;
}
if (!string.IsNullOrEmpty(filters.City))
{
cityId = GetCityId(connection, filters.City).ToString();
}
if ((filters.MinAge == 0 && filters.MaxAge == 0) || (filters.MinAge != 0 && filters.MaxAge == 0))
{
filters.MaxAge = 30;
}
string query = @"SELECT * FROM Dog
WHERE (Age BETWEEN @MinAge and @MaxAge) AND (Breed LIKE '%' + @Breed ) AND (Gender LIKE '%' + @Gender ) AND (City LIKE '%' + @City) AND (NOT Owner=@Owner)
ORDER BY [Name]
OFFSET (@Page-1)*4 ROWS
FETCH NEXT 4 ROWS ONLY";
var filteredDogs = connection.Query <DogFromDB>(query, new
{
MinAge = filters.MinAge,
MaxAge = filters.MaxAge,
Breed = breedId,
Gender = filters.Gender,
City = cityId,
Owner = currentOwnerId,
Page = page
}).ToList();
if (filteredDogs.Count == 0)
{
throw new NoEntriesException();
}
var result = new List <DogForDisplayWithId>();
foreach (var item in filteredDogs)
{
var dog = new DogForDisplayWithId();
dog.Id = item.Id;
dog.Name = item.Name;
dog.Age = item.Age;
dog.Gender = item.Gender;
dog.Breed = GetBreedById(connection, item.Breed);
dog.Owner = item.Owner;
dog.Specifics = item.Specifics;
dog.ProfilePicturePath = item.ProfilePicturePath;
result.Add(dog);
}
return(result);
}
}
