public IActionResult GetPhotoById(int id, int userId)
{
User user = userDAO.GetUser(User.Identity.Name);
userId = user.Id;
DeepPhoto photo = photoDAO.GetDeepPhotoById(id, userId);
return(Ok(photo));
}
/// <summary>
/// This gets a photo with a given ID as well as data about the user that submitted the photo, and all comments and likes associated with that photo
/// </summary>
/// <param name="photo"></param>
/// <returns></returns>
public DeepPhoto GetDeepPhotoById(int id, int userId)
{
DeepPhoto deepPhoto = new DeepPhoto();
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand("select *, isLikedByUser = CASE WHEN EXISTS(SELECT * FROM likes WHERE photoId = photos.id and userId = @userId) THEN 1 ELSE 0 END, isFavoritedByUser = CASE WHEN EXISTS(SELECT * FROM favorites WHERE photoId = photos.id and userId = @userId) THEN 1 ELSE 0 END from photos where Id = @id select * from comments left join users on comments.commenterId = users.id where photoId = @id select count(*) as 'Total Likes' from likes where likes.photoId = @id select username from users join photos on photos.userId = users.id where photos.id = @id", conn);
cmd.Parameters.AddWithValue("@id", id);
cmd.Parameters.AddWithValue("@userId", userId);
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read()) // THere will be zero or one
{
// Create the deepPhoto object
deepPhoto.Id = (Convert.ToInt32(reader["id"]));
deepPhoto.Caption = (Convert.ToString(reader["caption"]));
deepPhoto.UserId = (Convert.ToInt32(reader["userId"]));
deepPhoto.ImageUrl = (Convert.ToString(reader["imageUrl"]));
deepPhoto.DateAdded = (Convert.ToDateTime(reader["dateAdded"]));
deepPhoto.IsVisible = (Convert.ToBoolean(reader["isVisible"]));
deepPhoto.IsFavoritedByUser = (Convert.ToBoolean(reader["isFavoritedByUser"]));
deepPhoto.IsLikedByUser = (Convert.ToBoolean(reader["isLikedByUser"]));
}
else
{
// Not found - return NULL
return(deepPhoto);
}
/***
* Get the comments. reader.NextResult moves us to the second rowset returned
* from our query (the comments)
* ***/
if (reader.NextResult())
{
while (reader.Read())
{
Comment comment = new Comment();
{
comment.CommentString = Convert.ToString(reader["comment"]);
comment.Id = (Convert.ToInt32(reader["id"]));
comment.PhotoId = (Convert.ToInt32(reader["photoId"]));
comment.CommenterId = Convert.ToInt32(reader["commenterId"]);
comment.DateCommented = Convert.ToDateTime(reader["dateCommented"]);
comment.CommenterName = Convert.ToString(reader["username"]);
};
deepPhoto.AllComments.Add(comment);
}
}
/***
* Get the comments. reader.NextResult moves us to the second rowset returned
* from our query (the likes)
* ***/
if (reader.NextResult())
{
while (reader.Read())
{
deepPhoto.totalLikes = (Convert.ToInt32(reader["Total Likes"]));
}
}
if (reader.NextResult())
{
while (reader.Read())
{
deepPhoto.PhotoOwner = (Convert.ToString(reader["username"]));
}
}
}
}
catch (SqlException)
{
throw;
}
return(deepPhoto);
}