//returns a list of clients' posts for a therapist
public List <Post> GetPostsByTherapistId(int id)
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"
SELECT p.Id, p.UserId, p.CreateDate, p.MoodId,
p.Content, p.EditTime, p.Flagged, p.TherapistId,
p.ViewTime, p.Comment, p.Deleted
FROM Post p
JOIN UserRelationship ur ON p.UserId = ur.UserId
WHERE ur.TherapistId = id
";
cmd.Parameters.AddWithValue("@id", id);
SqlDataReader reader = cmd.ExecuteReader();
List <Post> posts = new List <Post>();
while (reader.Read())
{
Post post = new Post
{
Id = reader.GetInt32(reader.GetOrdinal("Id")),
CreateDate = reader.GetDateTime(reader.GetOrdinal("CreateDate")),
MoodId = reader.GetInt32(reader.GetOrdinal("MoodId")),
Content = ReaderUtils.GetNullableString(reader, "Content"),
EditTime = ReaderUtils.GetNullableDateTime(reader, "EditTime"),
Flagged = reader.GetBoolean(reader.GetOrdinal("Flagged")),
TherapistId = ReaderUtils.GetNullableInt(reader, "TherapistId"),
ViewTime = ReaderUtils.GetNullableDateTime(reader, "ViewTime"),
Comment = ReaderUtils.GetNullableString(reader, "Ccomment"),
Deleted = reader.GetBoolean(reader.GetOrdinal("Deleted"))
};
posts.Add(post);
}
reader.Close();
return(posts);
}
}
}
public List <Comment> GetCommentByPostId(int id)
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"
SELECT c.Id, c.PostId, c.UserProfileId, c.Subject, c.Content, c.CreateDateTime,
p.Id, p.Title, p.Content, p.ImageLocation, p.CreateDateTime, p.PublishDateTime,
p.IsApproved, p.CategoryId, p.UserProfileId,
u.Id, u.DisplayName, u.FirstName, u.LastName, u.Email,
u.CreateDateTime, u.ImageLocation, u.UserTypeId,
ut.Id, ut.Name
FROM Comment c
LEFT JOIN Post p ON p.Id = c.PostId
LEFT JOIN UserProfile u ON u.Id = p.UserProfileId
LEFT JOIN UserType ut ON ut.Id = u.UserTypeId
WHERE c.PostId = @id
";
cmd.Parameters.AddWithValue("@id", id);
SqlDataReader reader = cmd.ExecuteReader();
List <Comment> comments = new List <Comment>();
while (reader.Read())
{
Comment comment = new Comment
{
Id = reader.GetInt32(reader.GetOrdinal("Id")),
PostId = reader.GetInt32(reader.GetOrdinal("PostId")),
UserProfileId = reader.GetInt32(reader.GetOrdinal("UserProfileId")),
Subject = reader.GetString(reader.GetOrdinal("Subject")),
Content = reader.GetString(reader.GetOrdinal("Content")),
CreateDataTime = reader.GetDateTime(reader.GetOrdinal("CreateDateTime")),
Post = new Post()
{
Id = reader.GetInt32(reader.GetOrdinal("PostId")),
Title = reader.GetString(reader.GetOrdinal("Title")),
Content = reader.GetString(reader.GetOrdinal("Content")),
ImageLocation = ReaderUtils.GetNullableString(reader, "ImageLocation"),
CreateDateTime = reader.GetDateTime(reader.GetOrdinal("CreateDateTime")),
PublishDateTime = ReaderUtils.GetNullableDateTime(reader, "PublishDateTime"),
IsApproved = reader.GetBoolean(reader.GetOrdinal("IsApproved")),
CategoryId = reader.GetInt32(reader.GetOrdinal("CategoryId")),
},
UserProfile = new UserProfile()
{
Id = reader.GetInt32(reader.GetOrdinal("UserProfileId")),
DisplayName = reader.GetString(reader.GetOrdinal("DisplayName")),
FirstName = reader.GetString(reader.GetOrdinal("FirstName")),
LastName = reader.GetString(reader.GetOrdinal("LastName")),
Email = reader.GetString(reader.GetOrdinal("Email")),
CreateDateTime = reader.GetDateTime(reader.GetOrdinal("CreateDateTime")),
ImageLocation = ReaderUtils.GetNullableString(reader, "ImageLocation"),
UserTypeId = reader.GetInt32(reader.GetOrdinal("UserTypeId")),
UserType = new UserType()
{
Id = reader.GetInt32(reader.GetOrdinal("UserTypeId")),
Name = reader.GetString(reader.GetOrdinal("Name"))
}
}
};
comments.Add(comment);
}
reader.Close();
return(comments);
}
}
}