//INFO: Return all questions in the database.
public List <QuestionDO> GetQuestions(int id)
{
List <QuestionDO> questionDOs = new List <QuestionDO>();
using (riverkeeperEntities RKEntities = new riverkeeperEntities())
{
List <Question> questions = (from q in RKEntities.Questions
where q.SurveyId == id
select q).ToList();
foreach (var question in questions)
{
if (question != null)
{
QuestionDO questionDO = new QuestionDO()
{
QuestionId = question.QuestionId,
Type = (short)question.Type,
Wording = question.Wording,
PossibleAnswers = (String.IsNullOrEmpty(question.PossibleAnswers)) ? new string[0] : question.PossibleAnswers.Split(','),
SurveyId = question.SurveyId
};
questionDOs.Add(questionDO);
}
if (question == null)
{
throw new Exception("No questions in DB");
}
}
}
return(questionDOs);
}
//INFO: Return all users in the database.
public List <UserDO> GetUsers()
{
List <UserDO> UserDOs = new List <UserDO>();
using (riverkeeperEntities RKEntities = new riverkeeperEntities())
{
List <User> Users = (from u in RKEntities.Users
select u).ToList();
foreach (var user in Users)
{
if (user != null)
{
UserDO userDO = new UserDO()
{
FirstName = user.FirstName,
LastName = user.LastName,
PhoneNumber = user.PhoneNumber,
Type = user.Type,
EmailAddress = user.EmailAddress,
ZipCode = user.ZipCode
};
UserDOs.Add(userDO);
}
if (user == null)
{
throw new Exception("No user in DB");
}
}
}
return(UserDOs);
}
//INFO: Returns survey with given Id
public SurveyDO GetSurvey(int id)
{
SurveyDO surveyDO = new SurveyDO();
using (riverkeeperEntities RKEntities = new riverkeeperEntities())
{
List <Survey> surveys = (from s in RKEntities.Surveys where s.SurveyId == id
select s).ToList();
if (surveys.Count == 0)
{
throw new Exception("No survey in DB with that ID");
}
Survey survey = surveys.ElementAt(0);
surveyDO.SurveyId = survey.SurveyId;
surveyDO.Name = survey.Name;
surveyDO.CreationDate = survey.CreationDate;
int numberOfQuestions = survey.Answers.Count();
for (int i = 0; i < numberOfQuestions; i++)
{
AnswerDO answerDO = new AnswerDO();
Answer answerdb = survey.Answers.ElementAt(i);
answerDO.Response = answerdb.Response;
answerDO.QuestionId = answerdb.QuestionId;
surveyDO.Answers.Add(answerDO);
}
}
return(surveyDO);
}
//INFO: Adds a new survey to the database
public bool SubmitSurvey(SurveyDO surveyDO)
{
using (riverkeeperEntities RKEntities = new riverkeeperEntities())
{
Console.WriteLine("Attempting to submit survey");
Survey surveydb = new Survey();
if (surveyDO != null)
{
surveydb.Name = surveyDO.Name;
surveydb.CreationDate = DateTime.Now;
surveydb.UserId = (from u in RKEntities.Users
where u.EmailAddress.Equals(surveyDO.Email)
select u.UserId).FirstOrDefault();
int numberOfQuestions = surveyDO.Answers.Count();
for (int i = 0; i < numberOfQuestions; i++)
{
AnswerDO answerDO = surveyDO.Answers.ElementAt(i);
Answer answerdb = new Answer();
answerdb.Response = answerDO.Response;
answerdb.QuestionId = answerDO.QuestionId;
surveydb.Answers.Add(answerdb);
}
RKEntities.Surveys.Add(surveydb);
}
int dbResult = 0;
try
{
dbResult = RKEntities.SaveChanges();
}
catch (DbEntityValidationException dbEx)
{
foreach (var validationErrors in dbEx.EntityValidationErrors)
{
foreach (var validationError in validationErrors.ValidationErrors)
{
Debug.WriteLine("Property: {0} Error: {1}", validationError.PropertyName, validationError.ErrorMessage);
}
}
}
catch (DbUpdateException e)
{
Debug.WriteLine(e.InnerException);
}
if (dbResult == 0)
{
return(false);
}
return(true);
}
}
//INFO: Get user by email address
public UserDO GetUser(string email)
{
UserDO userDO = new UserDO();
using (riverkeeperEntities RKEntities = new riverkeeperEntities())
{
User user = (from u in RKEntities.Users
where u.EmailAddress.Equals(email)
select u).FirstOrDefault();
userDO = translateUserDBtoUserDO(user);
}
return(userDO);
}
//INFO: Creates new user
public bool CreateUser(UserDO userDO)
{
using (riverkeeperEntities RKEntities = new riverkeeperEntities())
{
User userdb = new User();
if (userDO != null)
{
userdb.FirstName = userDO.FirstName;
userdb.LastName = userDO.LastName;
userdb.EmailAddress = userDO.EmailAddress;
userdb.PhoneNumber = userDO.PhoneNumber;
userdb.Type = userDO.Type;
userdb.ZipCode = userDO.ZipCode;
RKEntities.Users.Add(userdb);
}
int dbResult = 0;
try
{
dbResult = RKEntities.SaveChanges();
}
catch (DbEntityValidationException dbEx)
{
foreach (var validationErrors in dbEx.EntityValidationErrors)
{
foreach (var validationError in validationErrors.ValidationErrors)
{
Debug.WriteLine("Property: {0} Error: {1}", validationError.PropertyName, validationError.ErrorMessage);
}
}
}
if (dbResult != 1)
{
return(false);
}
return(true);
}
}
//INFO: Gets list of all submitted surveys organized by creation date
public List <SurveyDO> GetSurveys()
{
List <SurveyDO> surveyDOs = new List <SurveyDO>();
using (riverkeeperEntities RKEntities = new riverkeeperEntities())
{
List <Survey> surveys = (from s in RKEntities.Surveys
orderby s.CreationDate
select s).ToList();
if (surveys.Count == 0)
{
throw new Exception("No surveys in DB");
}
foreach (var survey in surveys)
{
SurveyDO surveyDO = new SurveyDO();
surveyDO.SurveyId = survey.SurveyId;
surveyDO.Name = survey.Name;
surveyDO.CreationDate = survey.CreationDate;
surveyDO.Email = (from u in RKEntities.Users
where u.UserId.Equals(survey.UserId)
select u.EmailAddress).FirstOrDefault();
int numberOfQuestions = survey.Answers.Count();
for (int i = 0; i < numberOfQuestions; i++)
{
AnswerDO answerDO = new AnswerDO();
Answer answerdb = survey.Answers.ElementAt(i);
answerDO.Response = answerdb.Response;
answerDO.QuestionId = answerdb.QuestionId;
surveyDO.Answers.Add(answerDO);
}
surveyDOs.Add(surveyDO);
}
}
return(surveyDOs);
}
//INFO: Adds a question to the database.
public bool CreateQuestion(QuestionDO questionDO)
{
using (riverkeeperEntities RKEntities = new riverkeeperEntities())
{
Question questiondb = new Question();
questiondb.SurveyId = questionDO.SurveyId;
questiondb.Wording = questionDO.Wording;
questiondb.Type = (ResponseFormat)questionDO.Type;
questiondb.PossibleAnswers = String.Join(",", questionDO.PossibleAnswers);
RKEntities.Questions.Add(questiondb);
int dbResult = 0;
try
{
dbResult = RKEntities.SaveChanges();
}
catch (DbEntityValidationException dbEx)
{
foreach (var validationErrors in dbEx.EntityValidationErrors)
{
foreach (var validationError in validationErrors.ValidationErrors)
{
Debug.WriteLine("Property: {0} Error: {1}", validationError.PropertyName, validationError.ErrorMessage);
}
}
}
if (dbResult != 1)
{
return(false);
}
return(true);
}
}
//INFO: Retrieves the question with the given id
public QuestionDO GetQuestionById(int id)
{
QuestionDO questionDO = new QuestionDO();
using (riverkeeperEntities RKEntities = new riverkeeperEntities())
{
List <Question> questions = (from q in RKEntities.Questions where q.QuestionId == id
select q).ToList();
Question question = questions.ElementAt(0);
if (question != null)
{
questionDO.QuestionId = question.QuestionId;
questionDO.Type = (short)question.Type;
questionDO.Wording = question.Wording;
questionDO.PossibleAnswers = question.PossibleAnswers.Split(',');
}
else
{
throw new Exception("No questions with that ID in DB");
}
}
return(questionDO);
}
//INFO: Gets a list of all answers for a given survey
public List <Dictionary <string, string> > GetSurveyResponse(int surveyID)
{
List <Dictionary <string, string> > allAnswers = new List <Dictionary <string, string> >();
using (riverkeeperEntities RKEntities = new riverkeeperEntities())
{
//get all questions for the survey
List <Question> questions = (from q in RKEntities.Questions
where q.SurveyId == surveyID
select q).ToList();
List <int> uniqueSurveyIds = (from s in RKEntities.Answers
select(int) s.Survey.SurveyId).Distinct().OrderBy(surveyid => surveyid).ToList();
if (questions == null || questions.Count == 0)
{
allAnswers.Add(new Dictionary <string, string>()
{
{ "error", "Survey " + surveyID.ToString() + " doesn't have any answers" }
});
}
else
{
foreach (var id in uniqueSurveyIds)
{
List <Answer> answers = (from a in RKEntities.Answers
where a.Survey.SurveyId == id
select a).ToList();
Dictionary <string, string> curAnswer = new Dictionary <string, string>();
//set up survey general data. ie. ID, completed by, creation date
curAnswer["SurveyID"] = id.ToString();
curAnswer["CreatedBy"] = (from x in RKEntities.Users
join surv in RKEntities.Surveys on x.UserId equals surv.UserId
where surv.SurveyId == id
select x.EmailAddress).FirstOrDefault();
curAnswer["CreatedOn"] = (from su in RKEntities.Surveys
where su.SurveyId == id
select su.CreationDate).FirstOrDefault().ToString();
foreach (var item in questions)
{
//add each question to the dictionary
foreach (var ans in answers)
{
//if there is an answer, provide it
if (ans.QuestionId == item.QuestionId)
{
curAnswer[item.Wording] = ans.Response;
}
//otherwise leave it blank.
else
{
if (!curAnswer.ContainsKey(item.Wording))
{
curAnswer[item.Wording] = "";
}
}
}
}
allAnswers.Add(curAnswer);
}
}
}
return(allAnswers);
}
