public List <DBQuestion> getByKeys(List <int> keys)
{
List <DBQuestion> result = new List <DBQuestion>();
DBManager manager = new DBManager(true);
String sqlCommand = "Select * From dbo.Questions answer Where ";
for (int i = 0; i < keys.Count; i++)
{
if (i != keys.Count - 1)
{
sqlCommand += "answer.question_id = " + keys[i] + " OR ";
}
else
{
sqlCommand += "answer.question_id = " + keys[i] + ";";
}
}
var reader = manager.Read(sqlCommand);
while (reader.Read())
{
DBQuestion answer = new DBQuestion();
answer.Question_id = (int)reader["question_id"];
answer.Question = (String)reader["question"];
answer.Answer_id = (int)reader["answer_id"];
result.Add(answer);
}
manager.Close();
return(result);
}
public void CreateQuestionTest()
{
DBQuestion dbquestion = new DBQuestion();
Question question = new Question();
question.Header = "TestQuestion";
question.Text = "What is your favorite colour?";
question.CreateDate = DateTime.Now;
Answer answer = new Answer();
answer.Text = "blue";
Answer correct = new Answer();
correct.Text = "yellow";
Answer answer2 = new Answer();
answer2.Text = "green";
Answer answer3 = new Answer();
answer3.Text = "red";
question.Answers.Add(answer);
question.Answers.Add(correct);
question.Answers.Add(answer2);
question.Answers.Add(answer3);
question.CorrectAnswer = correct;
dbquestion.SaveQuestion(question);
}
/// <summary>
/// Is to be called when a user answers a question, or is timeouted.
/// Returns whether it was successful or not.
/// </summary>
/// <param name="contestant">The contestant, who attempts</param>
/// <param name="question">The question that is being attempted</param>
/// <param name="answer">The answer the contestant attempts with. This should be null, if it was a timeout</param>
/// <returns>Whether it was successful or not</returns>
public bool AttemptAnswer(int quizInstanceId, Guid contestantId, int questionId, int?answerId)
{
DBQuestion dbQuestion = new DBQuestion();
bool result;
try
{
if (answerId != null)
{
result = dbQuestion.IsAnswerCorrect(quizInstanceId, questionId, answerId);
}
else
{
throw new TimeoutException();
}
}
catch (TimeoutException)
{
result = false;
answerId = null;
}
if (result)
{
dbQuestion.AnsweredCorrectly(quizInstanceId, contestantId, questionId);
}
else
{
dbQuestion.AnsweredWrong(quizInstanceId, contestantId, questionId, answerId);
}
return(result);
}
public void getQuestionWithAnswers()
{
DBQuestion dbQuestion = new DBQuestion();
Question question = dbQuestion.GetQuestionWithAnswers(239);
Assert.IsTrue(question != null);
Assert.IsTrue(question.CorrectAnswer == null);
Assert.IsTrue(question.Answers.Count == 4);
Assert.AreEqual(question.Answers[0].Text, "England");
}
private bool Update()
{
return(DBQuestion.Update(
this.questionGuid,
this.pageGuid,
this.questionText,
this.questionTypeId,
this.answerIsRequired,
this.questionOrder,
this.validationMessage
));
}
private bool Update()
{
return(DBQuestion.Update(
questionGuid,
pageGuid,
questionName,
questionText,
questionTypeId,
answerIsRequired,
questionOrder,
validationMessage
));
}
/// <summary>
/// Get all the unanswered questions and wrap them into a MatchQuestionModelRequest.
/// </summary>
/// <param name="list">A list of ChatbotNewQuestionModels to process.</param>
/// <returns>A MatchQuestionModelRequest containing all the answered questions from the forum.</returns>
public static MatchQuestionModelRequest GenerateModelCompareToOpenQuestions(NewQuestion newQuestion)
{
MatchQuestionModelRequest mqmr = new MatchQuestionModelRequest();
List <DBQuestion> result = new List <DBQuestion>();
DBManager manager = new DBManager(true);
StringBuilder sb = new StringBuilder();
sb.Append("SELECT * ");
sb.Append("FROM Questions q ");
sb.Append("WHERE q.answer_id IS NOT NULL; ");
String sqlCommand = sb.ToString();
var reader = manager.Read(sqlCommand);
while (reader.Read())
{
DBQuestion answer = new DBQuestion();
answer.Question_id = (int)reader["question_id"];
answer.Question = (String)reader["question"];
answer.Answer_id = (int)reader["answer_id"];
/****/
answer.Question = ServerUtilities.SQLSafeToUserInput(answer.Question);
/****/
result.Add(answer);
}
manager.Close(); //IMPORTANT! Should happen automatically, but better safe than sorry.
mqmr.action = "MATCH_QUESTIONS".ToLower(); //Standard
mqmr.question = newQuestion.question;
mqmr.question_id = -1; // This id does not exist at this point
List <NLPQuestionModelInfo> comparisonQuestions = new List <NLPQuestionModelInfo>();
for (int i = 0; i < result.Count; i++)
{
comparisonQuestions.Add(new NLPQuestionModelInfo()
{
question = result[i].Question, question_id = result[i].Question_id
});
}
mqmr.compare_questions = comparisonQuestions.ToArray();
mqmr.msg_id = ClusterConnector.ServerUtilities.getAndGenerateMsgIDOpenQuestions(newQuestion.chatbot_temp_id, newQuestion.question, newQuestion.user_id);
return(mqmr);
}
public void findQuestionsTest()
{
DBQuestion dbQuestion = new DBQuestion();
var questions = dbQuestion.GetAll();
Assert.IsTrue(questions.Count >= 2);
foreach (Question question in questions)
{
Assert.IsTrue(question != null, "Question is null");
Assert.IsTrue(question.CreateDate != null, "Question has no create date");
Assert.IsTrue(question.Id != 0, "Question id is 0");
Assert.IsTrue(question.Text != null, "Question has no text.");
}
}
private bool Create()
{
this.questionGuid = Guid.NewGuid();
int rowsAffected = DBQuestion.Add(
this.questionGuid,
this.pageGuid,
this.questionText,
this.questionTypeId,
this.answerIsRequired,
this.validationMessage
);
return(rowsAffected > 0);
}
private bool Create()
{
questionGuid = Guid.NewGuid();
int rowsAffected = DBQuestion.Add(
questionGuid,
pageGuid,
questionName,
questionText,
questionTypeId,
answerIsRequired,
validationMessage
);
return(rowsAffected > 0);
}
private void GetQuestion(Guid guidQuestionGuid)
{
using (IDataReader reader = DBQuestion.GetOne(guidQuestionGuid))
{
if (reader.Read())
{
this.questionGuid = new Guid(reader["QuestionGuid"].ToString());
this.pageGuid = new Guid(reader["PageGuid"].ToString());
this.questionText = reader["QuestionText"].ToString();
this.questionTypeId = Convert.ToInt32(reader["QuestionTypeId"]);
this.answerIsRequired = Convert.ToBoolean(reader["AnswerIsRequired"]);
this.questionOrder = Convert.ToInt32(reader["QuestionOrder"]);
this.validationMessage = reader["ValidationMessage"].ToString();
}
}
}
/// <summary>
/// Takes a certain userID AND/OR questionID and checks the database for new answers on possible open questions
/// </summary>
/// <param name="userID"></param>
/// <param name="questionID"></param>
/// <returns> Returns new answers on possible open questions for a certain user or question </returns>
public static List <ChatbotNewAnswerModel> CheckAndGetNewAnswers(string userID = null, int questionID = -1)
{
// connect to database and define query
DBManager manager = new DBManager(true);
String query = "Select q.question_id, q.question, q.answer_id, a.answer " +
"from dbo.Answers a, dbo.Questions q " +
$"where q.question_id = {questionID} and q.answer_id = a.answer_id;";// and " +
//"a.approved = 'true';";
// execute query and read answer
DBQuestion sqlResult = null;
using (SqlDataReader reader = manager.Read(query))
{
if (reader.Read())
{
sqlResult = new DBQuestion();
sqlResult.Question_id = (int)reader["question_id"];
sqlResult.Question = (String)reader["question"];
sqlResult.Answer_id = (int)reader["answer_id"];
sqlResult.Answer = (String)reader["answer"];
/****/
sqlResult.Answer = ServerUtilities.SQLSafeToUserInput(sqlResult.Answer);
sqlResult.Question = ServerUtilities.SQLSafeToUserInput(sqlResult.Question);
/****/
}
}
// Close the connection
manager.Close();
if (sqlResult == null)
{
return(new List <ChatbotNewAnswerModel>()
{
new ChatbotNewAnswerModel(userID, null, questionID, -1, null, -1, 0)
});
}
// Return new output-model
return(new List <ChatbotNewAnswerModel>()
{
new ChatbotNewAnswerModel(userID, sqlResult.Question, sqlResult.Question_id, -1 /**no temporary chatbot id needed*/,
sqlResult.Answer, sqlResult.Answer_id, 1 /**Where is the certainty found?*/)
});
}
public bool Ask(string content, int reward) //提问
{
//传过去生成问题，同时传回questionid，生成question,返回question
HttpClient client = new HttpClient();
client.DefaultRequestHeaders.Accept.Clear();
client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
string url = BaseUrl + "question/newQuestion";
DBQuestion dbQuestion = new DBQuestion()
{
UserID = this.UserId, Content = content, Reward = reward
};
HttpContent question = new StringContent(JsonConvert.SerializeObject(dbQuestion), Encoding.UTF8, "application/json");
var task = client.PostAsync(url, question);
bool success = task.Result.IsSuccessStatusCode;
return(success);
}
/// <summary>
///
/// </summary>
/// <param name="list"></param>
/// <returns> This functions should have a response, if the response == null, then no response will be given through the websocket</returns>
public static List <ChatbotAnswerRequestResponseModel> ProcessChatbotRequestAnswerToQuestion(List <ChatbotAnswerRequestModel> list)
{
ChatbotAnswerRequestModel answerRequest = list[0];
int question_id = answerRequest.question_id;
// connect to database and define query
DBManager manager = new DBManager(true); //this false
String query = "Select a.answer_id, a.answer " +
"from dbo.Answers as a, dbo.Questions as q " +
$"where q.question_id = {question_id} and q.answer_id = a.answer_id ";
// "+ and a.approved == true;";
// execute query and read answer
DBQuestion sqlResult = null;
using (SqlDataReader reader = manager.Read(query))
{
while (reader.Read())
{
sqlResult = new DBQuestion();
sqlResult.Answer_id = (int)reader["answer_id"];
sqlResult.Answer = (String)reader["answer"];
/****/
sqlResult.Answer = ServerUtilities.SQLSafeToUserInput(sqlResult.Answer);
/****/
}
}
// Close the connection
manager.Close();
if (sqlResult == null)
{
return(null);
}
return(new List <ChatbotAnswerRequestResponseModel>()
{
new ChatbotAnswerRequestResponseModel(answerRequest.user_id, sqlResult.Answer_id, question_id, true, sqlResult.Answer)
});
}
public DBQuestion getByKey(int answer_id)
{
String sqlCommand = "Select * From dbo.Questions answer Where answer.question_id = " + answer_id + ";";
DBManager manager = new DBManager(true);
var reader = manager.Read(sqlCommand);
if (!reader.Read())
{
return(null);
}
DBQuestion answer = new DBQuestion();
answer.Question_id = (int)reader["question_id"];
answer.Question = (String)reader["question"];
answer.Answer_id = (int)reader["answer_id"];
manager.Close();
return(answer);
}
public List <Question> GetMyQuestions(int page) //我的提问，分页版
{
HttpClient client = new HttpClient();
client.DefaultRequestHeaders.Accept.Clear();
client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
string url = BaseUrl + $"question/questionQuery?userid=" + this.UserId + "&page=" + page;
var task = client.GetAsync(url);
if (task.Result.IsSuccessStatusCode == false)
{
return(null);
}
else
{
string i = task.Result.Content.ReadAsStringAsync().Result;
List <DBQuestion> dbq = JsonConvert.DeserializeObject <List <DBQuestion> >(i);
List <Question> qlist = new List <Question>();
qlist = DBQuestion.ConvertToQList(dbq);
return(qlist);
}
}
static public List <Question> SearchQuestion(string keyword, int page) //查询问题
{
HttpClient client = new HttpClient();
client.DefaultRequestHeaders.Accept.Clear();
client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
string url = CommunityService.BaseUrl + $"question/questionQuery?keyword=" + keyword + "&page=" + page;
var task = client.GetAsync(url);
if (task.Result.IsSuccessStatusCode == false)
{
return(null);
}
else
{
string i = task.Result.Content.ReadAsStringAsync().Result;
List <DBQuestion> dbq = JsonConvert.DeserializeObject <List <DBQuestion> >(i);
List <Question> qlist = new List <Question>();
qlist = DBQuestion.ConvertToQList(dbq);
return(qlist);
}
}
public static List <Question> GetAll(Guid pageGuid)
{
List <Question> pageQuestionList
= new List <Question>();
using (IDataReader reader = DBQuestion.GetAllByPage(pageGuid))
{
while (reader.Read())
{
Question question = new Question();
question.questionGuid = new Guid(reader["QuestionGuid"].ToString());
question.pageGuid = new Guid(reader["PageGuid"].ToString());
question.questionText = reader["QuestionText"].ToString();
question.questionTypeId = Convert.ToInt32(reader["QuestionTypeId"]);
question.answerIsRequired = Convert.ToBoolean(reader["AnswerIsRequired"]);
question.questionOrder = Convert.ToInt32(reader["QuestionOrder"]);
question.validationMessage = reader["ValidationMessage"].ToString();
pageQuestionList.Add(question);
}
}
return(pageQuestionList);
}
static public List <Question> GetAllQuestions(int page) //浏览社区问题,分页版
{
//获得所有提问与回答并返回到list ,question里带List<answer>
HttpClient client = new HttpClient();
client.DefaultRequestHeaders.Accept.Clear();
client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
string url = CommunityService.BaseUrl + $"question/questionQuery?page=" + page;
var task = client.GetAsync(url);
if (task.Result.IsSuccessStatusCode == false)
{
return(null);
}
else
{
string i = task.Result.Content.ReadAsStringAsync().Result;
List <DBQuestion> dbq = JsonConvert.DeserializeObject <List <DBQuestion> >(i);
List <Question> qlist = new List <Question>();
qlist = DBQuestion.ConvertToQList(dbq);
return(qlist);
}
}
/// <summary>
/// Function to get a number of open questions from the database
/// </summary>
/// <param name="nbQuestions"></param>
/// <param name="user_id"></param>
/// <returns> Returns a model with a list of unanswered questions </returns>
public static ChatbotResponseUnansweredQuestionsModel RetrieveOpenQuestions(string user_id = null, int nbQuestions = 3)
{
List <DBQuestion> result = new List <DBQuestion>();
DBManager manager = new DBManager(true); //this false
StringBuilder sb = new StringBuilder();
sb.Append($"SELECT TOP {nbQuestions} q.question_id, q.question ");
sb.Append("FROM Questions q ");
sb.Append("WHERE q.answer_id is NULL");
String sqlCommand = sb.ToString();
var reader = manager.Read(sqlCommand);
while (reader.Read()) //reader.Read() reads entries one-by-one for all matching entries to the query
{
DBQuestion answer = new DBQuestion();
answer.Question_id = (int)reader["question_id"];
answer.Question = (String)reader["question"];
/****/
answer.Question = ServerUtilities.SQLSafeToUserInput(answer.Question);
/****/
result.Add(answer);
}
manager.Close(); //IMPORTANT! Should happen automatically, but better safe than sorry.
List <ChatbotQuestionHasNoAnswerModel> openQuestions = new List <ChatbotQuestionHasNoAnswerModel>();
for (int i = 0; i < result.Count; i++)
{
openQuestions.Add(new ChatbotQuestionHasNoAnswerModel(result[i].Question, result[i].Question_id));
}
return(new ChatbotResponseUnansweredQuestionsModel(openQuestions.ToArray(), user_id));
}
/// <summary>
/// Get all the answered questions and wrap them into a MatchQuestionModelRequest.
/// </summary>
/// <param name="list">A list of ChatbotNewQuestionModels to process.</param>
/// <returns>A MatchQuestionModelRequest containing all the answered questions from the forum.</returns>
public static MatchQuestionModelRequest ProcessChatbotReceiveQuestion(List <ChatbotNewQuestionModel> list)
{
MatchQuestionModelRequest mqmr = new MatchQuestionModelRequest();
//Example on how to turn a Query String into data from the SQL database
List <DBQuestion> result = new List <DBQuestion>();
DBManager manager = new DBManager(true);
StringBuilder sb = new StringBuilder();
sb.Append("SELECT * ");
sb.Append("FROM Questions q ");
sb.Append("WHERE q.answer_id IS NOT NULL; ");
String sqlCommand = sb.ToString();
var reader = manager.Read(sqlCommand);
while (reader.Read()) //reader.Read() reads entries one-by-one for all matching entries to the query
{
//
//reader["xxx"] where 'xxx' is the collumn name of the particular table you get as result from the query.
//You get these values a generic 'Object' so typecasting to the proper value should be safe. eg. (int)reader["xxx"]
//
DBQuestion answer = new DBQuestion();
answer.Question_id = (int)reader["question_id"];
answer.Question = (String)reader["question"];
answer.Answer_id = (int)reader["answer_id"];
/****/
answer.Question = ServerUtilities.SQLSafeToUserInput(answer.Question);
/****/
result.Add(answer);
}
manager.Close(); //IMPORTANT! Should happen automatically, but better safe than sorry.
//**********************************
mqmr.action = "MATCH_QUESTIONS".ToLower(); //Standard
mqmr.question = list[0].question;
mqmr.msg_id = 0; //Currently not really used
mqmr.question_id = 0; //This id does not exist at this point
List <NLPQuestionModelInfo> comparisonQuestions = new List <NLPQuestionModelInfo>();
for (int i = 0; i < result.Count; i++)
{
comparisonQuestions.Add(new NLPQuestionModelInfo()
{
question = result[i].Question, question_id = result[i].Question_id
});
}
mqmr.compare_questions = comparisonQuestions.ToArray();
// IMPORTANT: DO NOT REMOVE THIS LINE
mqmr.msg_id = ClusterConnector.ServerUtilities.getAndGenerateMsgID(list[0].chatbot_temp_id, list[0].question, list[0].user_id);
return(mqmr);
}
public static bool Delete(Guid questionGuid)
{
return(DBQuestion.Delete(questionGuid));
}
public QuestionCtrl()
{
dbQuestion = new DBQuestion();
}
private static bool isQuestionRequired(DBQuestion question)
{
return(question.required);
}
