// gets sum of ratings by member from the database
public static List <MemberAnalytic> getMemberFeedbackAnalytic()
{
List <MemberAnalytic> feedList = new List <MemberAnalytic>();
try
{
SqlCommand command = new SqlCommand("SELECT feedbackTo, sum(case when rating='Positive' then 1 else 0 end) AS positiveCount, sum(case when rating = 'Neutral' then 1 else 0 end) AS neutralCount, sum(case when rating = 'Negative' then 1 else 0 end) AS negativeCount FROM Feedback F, Member M WHERE F.feedbackTo = M.memberID and M.status<>'Banned' GROUP BY feedbackTo");
command.Connection = connection;
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
MemberAnalytic ma = new MemberAnalytic();
ma.Member = MemberDB.getMemberbyID(reader["feedbackTo"].ToString());
ma.NoOfPositiveFeedback = Convert.ToInt32(reader["positiveCount"]);
ma.NoOfNeutralFeedback = Convert.ToInt32(reader["neutralCount"]);
ma.NoOfNegativeFeedback = Convert.ToInt32(reader["negativeCount"]);
ma.NoOfRentalAsRenter = RentalDB.getNoofRentalAsRenter(ma.Member.MemberID);
ma.NoOfRentalAsRentee = RentalDB.getNoofRentalAsRentee(ma.Member.MemberID);
feedList.Add(ma);
}
reader.Close();
}
finally
{
connection.Close();
}
return(feedList);
}
