public static string getQuestionFromID(int question_id)
{
DBManager manager = new DBManager(true);
String result = null;
StringBuilder sb = new StringBuilder();
sb.Append("SELECT question ");
sb.Append("FROM Questions q ");
sb.Append($"WHERE q.question_id = {question_id}; ");
String sql = sb.ToString();
using (SqlDataReader reader = manager.Read(sql))
{
// This query should only return 0 or 1 result
if (reader.Read())
{
result = reader.GetString(0);
/****/
result = ServerUtilities.SQLSafeToUserInput(result);
/****/
}
}
manager.Close();
return(result);
}
/// <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);
}
/// <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?*/)
});
}
/// <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)
});
}
/// <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>
/// Recursive function that looks for the best possible match that also has an aswer in the database
/// </summary>
/// <param name="match_candidates"></param>
/// <param name="matchQuestionModel"></param>
/// <returns></returns>
private static MatchQuestionLogicResponse QueryHelperRecursive(List <MatchQuestionModelInfo> match_candidates, MatchQuestionModelResponse matchQuestionModel)
{
MatchQuestionModelInfo bestInfo = null;
if (match_candidates.Count == 0)
{
return(new MatchQuestionLogicResponse()
{
Msg_id = matchQuestionModel.msg_id
});
}
else
{
// get best match of all candidates
double bestMatch = 0.0;
foreach (MatchQuestionModelInfo info in match_candidates)
{
if (info.prob > bestMatch)
{
bestInfo = info;
bestMatch = info.prob;
}
}
}
if (bestInfo == null)
{
return(new MatchQuestionLogicResponse()
{
Msg_id = matchQuestionModel.msg_id
});
}
// Perform query to get the answer to the best match
DBManager manager = new DBManager(true);
MatchQuestionLogicResponse result = null;
StringBuilder sb = new StringBuilder();
sb.Append("SELECT answer ");
sb.Append("FROM Answers a, Questions q ");
sb.Append($"WHERE q.question_id = {bestInfo.question_id} AND q.answer_id = a.answer_id; ");
String sql = sb.ToString();
using (SqlDataReader reader = manager.Read(sql))
{
// This query should only return 0 or 1 result
if (reader.Read())
{
/****/
String sqlSafeAnswer = ServerUtilities.SQLSafeToUserInput(reader.GetString(0));
/****/
result = new MatchQuestionLogicResponse(matchQuestionModel.question_id, matchQuestionModel.msg_id, bestInfo.question_id, true, sqlSafeAnswer);
}
}
manager.Close();
// If a result is found, return the result, else look for another option that does have an answer
if (result != null)
{
return(result);
}
else
{
match_candidates.Remove(bestInfo);
return(QueryHelperRecursive(match_candidates, matchQuestionModel));
}
}
/// <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);
}
