private DataTable CheckForInterviewCompleteness() { try { var query = "select AnswerResults.id " + " from main.AnswerResults, " + " main.Interview " + " where Interview.theme_id = '" + _interviewThemeId + "'" + " and AnswerResults.respondent_id = '" + _respondentId + "' " + " and Interview.id = AnswerResults.interview_id "; var check = DbConnection.SelectFromDb(query); if (check.Rows.Count == 0) { check.Columns.Add(new DataColumn() { DataType = typeof(int) }); var newRow = check.NewRow(); newRow[0] = 0; check.Rows.Add(newRow); return(check); } query = " select " + "( " + " select count(distinct Interview.question_id) " + " from main.Interview " + " where Interview.theme_id = '" + _interviewThemeId + "'" + ") = " + "( " + " select count(AnswerResults.interview_id) " + " from main.AnswerResults, " + " main.Interview " + " where AnswerResults.interview_id = Interview.id " + " and Interview.theme_id = '" + _interviewThemeId + "'" + " and AnswerResults.respondent_id = '" + _respondentId + "' " + " and AnswerResults.Interview_number = '" + _nextInterviewNum + "'" + " )"; var identityRow = DbConnection.SelectFromDb(query); var identity = Convert.ToInt32(identityRow.Rows[0][0]); if (identity == 0) { identityRow.Rows[0][0] = 1; } else { identityRow.Rows[0][0] = 0; if (InterView.GetHaveHistory()) { _nextInterviewNum++; } } return(identityRow); } catch (Exception exp) { throw new Exception("GetLastQuestionId " + exp); } }
private void UpdateAnswerResult(Question currQuestion, object interviewId) { var query = "select max(AnswerResults.id) " + " from main.AnswerResults, " + " main.Interview," + " main.Questions " + " where Interview.theme_id = '" + _interviewThemeId + "'" + " and AnswerResults.respondent_id = '" + _respondentId + "' " + " and Interview.id = AnswerResults.interview_id " + " and Interview.question_id = Questions.id " + " and Questions.Name = '" + currQuestion.Name + "'"; try { var answerResultRow = DbConnection.SelectFromDb(query); if (answerResultRow.Rows[0][0].ToString() != "") { var answerResultId = answerResultRow.Rows[0][0]; if (InterView.GetHaveHistory()) { query = " update main.AnswerResults" + " set Interview_id = '" + interviewId + "'" + ", answer_date = current_date " + " where id = '" + answerResultId + "'"; } else { var nextInterviewNum = InterView.GetInterviewCompleteness() ? _nextInterviewNum : _nextInterviewNum + 1; query = " update main.AnswerResults" + " set Interview_id = '" + interviewId + "'" + ", interview_number = '" + nextInterviewNum + "'" + ", answer_date = current_date " + " where id = '" + answerResultId + "'"; } DbConnection.DmlOperation(query); } else { AddAnswerResult(interviewId); } } catch (Exception exp) { throw new Exception("UpdateAnswerResult" + exp); } }