//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); }