public static IForm <TicketModel> BuildForm()
{
//Form flow builder being called
OnCompletionAsyncDelegate <TicketModel> ConnectionRequest = async(context, state) =>
{
string sentenceString = state.Desc + "-" + state.DBName + "-" + state.ServerName + "-" + state.MiddlewareName;
/**
* To call the GetQnAMakerResponse to get the responses to the user queries from QnA Maker KB
* The QnA maker sends the appropriate response to the user queries
**/
await context.PostAsync("Let me search my database for a solution to your problem");
try
{
var activity = (Activity)context.MakeMessage();
activity.Text = sentenceString;
/**
* Call to the sentiment analytics api
**/
var sentiment = await TextAnalyticsService.DetermineSentimentAsync(sentenceString.ToString());
var sentimentScore = Math.Round(sentiment * 100, 1) / 10;
/**
* Query to check the user issue in the QnA maker knowledge base
**/
var subscriptionKey = ConfigurationManager.AppSettings["QnaSubscriptionkey"];
var knowledgeBaseId = ConfigurationManager.AppSettings["QnaKnowledgebaseId"];
var responseQuery = new QnAMakerDailog.QnAMakerDialog().GetQnAMakerResponse(sentenceString, subscriptionKey, knowledgeBaseId);
var responseAnswers = responseQuery.answers.FirstOrDefault();
/**
* If the solution to the issue is found in the Kb then send the result to the user
**/
if (responseAnswers != null && responseAnswers.score >= double.Parse(ConfigurationManager.AppSettings["QnAScore"]))
{
await context.PostAsync(responseAnswers.answer);
}
/**
* If no solution is found then the user response from TicketModel is sent to the Dtabase APRDB and stored in dbo.BotDetails Table
**/
else
{
await context.PostAsync("Could not find a solution to you problem . I have raised a ticket for it, revert to you as soon as we get a solution for it");
try
{
string connectionEstablish = ConfigurationManager.ConnectionStrings["APRMConnection"].ConnectionString;
/**
* Establish the connection with the SQL database
**/
SqlConnection connection = new SqlConnection(connectionEstablish);
/**
* Connection to the DB being opened
**/
connection.Open();
if (connection.State == System.Data.ConnectionState.Open)
{
Console.WriteLine("Connection Success");
/**
* SQL command to insert data into the table dbo.BotDetails
**/
SqlCommand insertCommand = new SqlCommand(@"INSERT INTO dbo.BotDetails (TokenDescription, ServerDetails, MiddlewareDetails, DatabaseDetails, TokenDetails,
SentimentScore, UserName, EmailId, ContactNo) VALUES (@TokenDescription, @ServerDetails, @MiddlewareDetails, @DatabaseDetails, @TokenDetails, @SentimentScore,
@UserName, @EmailId, @ContactNo)", connection);
if (state.PhoneContact == null)
{
state.PhoneContact = "0";
}
else
{
state.Contact = "None";
}
/**
* Commands to insert the user response to the database
**/
insertCommand.Parameters.Add(new SqlParameter("TokenDescription", state.Desc));
insertCommand.Parameters.Add(new SqlParameter("ServerDetails", state.ServerName));
insertCommand.Parameters.Add(new SqlParameter("MiddlewareDetails", state.MiddlewareName));
insertCommand.Parameters.Add(new SqlParameter("DatabaseDetails", state.DBName));
insertCommand.Parameters.Add(new SqlParameter("TokenDetails", System.DateTimeOffset.Now));
insertCommand.Parameters.Add(new SqlParameter("SentimentScore", sentimentScore));
insertCommand.Parameters.Add(new SqlParameter("UserName", state.Name));
insertCommand.Parameters.Add(new SqlParameter("EmailId", state.Contact));
insertCommand.Parameters.Add(new SqlParameter("ContactNo", state.PhoneContact));
var DBresult = insertCommand.ExecuteNonQuery();
if (DBresult > 0)
{
connection.Close();
string ReconnectionEstablish = ConfigurationManager.ConnectionStrings["APRMConnection"].ConnectionString;
SqlConnection conn = new SqlConnection(ReconnectionEstablish);
conn.Open();
var selectTicketId = "Select Id from dbo.BotDetails WHERE UserName = @UserName";
SqlCommand selectCommand = new SqlCommand(selectTicketId, conn);
selectCommand.Parameters.AddWithValue("@UserName", state.Name);
using (SqlDataReader queryReader = selectCommand.ExecuteReader())
{
while (queryReader.Read())
{
String retrieveId = queryReader.GetInt32(0).ToString();
//await context.PostAsync("Your ticket has been raised successfully, " + retrieveId + " your token id for the raised ticket");
}
}
}
else
{
await context.PostAsync("Some problem occured, Please try again after sometime");
}
/**
* Close the existing connection to the DB
**/
connection.Close();
}
else
{
/**
* Checks wether the connection is established or not
**/
Console.WriteLine("Not connected");
}
/**
* Snow connection code
**/
string DetailDescription = state.Desc + " the services are running on server " + state.ServerName + ", using " + state.DBName + " database and the" + state.MiddlewareName + " service";
String incidentNo = string.Empty;
string contactOption;
/*
* If else condition to catch the contact option
*/
if (state.ContactType == ContactTypeOptions.Email)
{
contactOption = "Email";
}
else
{
contactOption = "Phone";
}
incidentNo = SnowLogger.CreateIncidentServiceNow(state.Desc, contactOption, DetailDescription, state.CategoryName);
Console.WriteLine(incidentNo);
await context.PostAsync("Your ticket has been raised successfully, " + incidentNo + " your token id for the raised ticket");
await context.PostAsync("Please keep the note of above token number. as it would be used for future references");
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
/**
* Method to check the user sentiment and report it to the user
*/
/*await context.PostAsync($"You rated our service as: {Math.Round(sentiment * 10, 1)}/10");
*
* if (sentiment <= 0.5)
* {
* PromptDialog.Confirm(context, ResumeAfterFeedbackClarification, "I see it wasn't perfect, can we contact you about this?");
* }*/
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
};
return(new FormBuilder <TicketModel>()
.Field(nameof(Name), validate: ValidateNameInfo)
.Field(nameof(Desc))
.Field(nameof(ServerName) /*validate: ValidateServerInfo*/)
.Field(nameof(MiddlewareName), validate: ValidateMiddlewareInfo)
.Field(nameof(DBName), validate: ValidateDatabaseInfo)
.Field(nameof(CategoryName))
//.Field(nameof(Priority))
.Field(nameof(ContactType))
.Field(nameof(Contact), (s) => s.ContactType == ContactTypeOptions.Email, validate: ValidateContactInformation)
.Field(nameof(PhoneContact), (s) => s.ContactType == ContactTypeOptions.Phone, validate: ValidatePhoneContact)
.AddRemainingFields()
.Message("According to the responses entered by you I have generated a statement for you that showscase you problem : " +
"{Desc} running on server {ServerName}, using {DBName} database and the {MiddlewareName} services used by you.")
//"Please enter Yes if this successfully describe your problem.")
.OnCompletion(ConnectionRequest)
.Build());
}
// Method not been used in the class
/*private async Task <double> getSentimentScore(string DocumentText)
* {
* string queryUri = "https://westus.api.cognitive.microsoft.com/text/analytics/v2.0/sentiment";
* HttpClient client = new HttpClient()
* {
* DefaultRequestHeaders =
* {
* {
* "Ocp-Apim-Subscription-Key",
* ""
* },
*
* {
* "Accept",
* "application/json"
* }
* }
* };
*
* var textInput = new BatchInput
* {
* documents = new List<DocumentInput>
* {
* new DocumentInput
* {
* Id = 1,
* Text = DocumentText,
* }
* }
* };
*
* var jsonInput = JsonConvert.SerializeObject(textInput);
* HttpResponseMessage postMessage;
* BatchResult response;
* try
* {
* postMessage = await client.PostAsync(queryUri, new StringContent(jsonInput, Encoding.UTF8, "application/json"));
* response = JsonConvert.DeserializeObject<BatchResult>(await postMessage.Content.ReadAsStringAsync());
* }
* catch(Exception e)
* {
* return 0.0;
* }
* return response?.documents[0]?.score ?? 0.0;
* }*/
/**
* Method tonhandle the interaction with the HP Service Manager - 9.0
**/
// method not used it is defined in the Ticket Model
/**
* Method to calculate the user sentiment score
**/
private async Task getUserSentiment(IDialogContext context, IAwaitable <TicketModel> result)
{
var sentence = await result;
// string sentenceString = sentence.DatabaseName + "-" + sentence.MiddlewareName + "-" + sentence.ServerName;
string sentenceString = sentence.Desc + "-" + sentence.DBName + "-" + sentence.ServerName + "-" + sentence.MiddlewareName;
//string sentenceString = sentence.Desc;
/**
* To call the GetQnAMakerResponse to get the responses to the user queries from QnA Maker KB
* The QnA maker sends the appropriate response to the user queries
**/
await context.PostAsync("Let me search my database for a solution to your problem");
try
{
var activity = (Activity)context.MakeMessage();
activity.Text = sentenceString;
/**
* Call to the sentiment analytics api
**/
var sentiment = await TextAnalyticsService.DetermineSentimentAsync(sentence.ToString());
var sentimentScore = Math.Round(sentiment * 100, 1) / 10;
/**
* Query to check the user issue in the QnA maker knowledge base
**/
var subscriptionKey = ConfigurationManager.AppSettings["QnaSubscriptionkey"];
var knowledgeBaseId = ConfigurationManager.AppSettings["QnaKnowledgebaseId"];
var responseQuery = new QnAMakerDailog.QnAMakerDialog().GetQnAMakerResponse(sentenceString, subscriptionKey, knowledgeBaseId);
var responseAnswers = responseQuery.answers.FirstOrDefault();
/**
* If the solution to the issue is found in the Kb then send the result to the user
**/
if (responseAnswers != null && responseAnswers.score >= double.Parse(ConfigurationManager.AppSettings["QnAScore"]))
{
await context.PostAsync(responseAnswers.answer);
}
/**
* If no solution is found then the user response from TicketModel is sent to the Dtabase APRDB and stored in dbo.BotDetails Table
**/
else
{
await context.PostAsync("Could not find a solution to you problem . I have raised a ticket for it, revert to you as soon as we get a solution for it");
try
{
string connectionEstablish = ConfigurationManager.ConnectionStrings["APRMConnection"].ConnectionString;
/**
* Establish the connection with the SQL database
**/
SqlConnection connection = new SqlConnection(connectionEstablish);
/**
* Connection to the DB being opened
**/
connection.Open();
if (connection.State == System.Data.ConnectionState.Open)
{
Console.WriteLine("Connection Success");
/**
* SQL command to insert data into the table dbo.BotDetails
**/
if (sentence.Contact == null)
{
sentence.Contact = "None";
}
else if (sentence.PhoneContact == null)
{
sentence.PhoneContact = "None";
}
else
{
Console.WriteLine("Some error occured");
}
SqlCommand insertCommand = new SqlCommand(@"INSERT INTO dbo.BotDetails (TokenDescription, ServerDetails, MiddlewareDetails, DatabaseDetails, TokenDetails,
SentimentScore, UserName, EmailId, ContactNo) VALUES (@TokenDescription, @ServerDetails, @MiddlewareDetails, @DatabaseDetails, @TokenDetails, @SentimentScore,
@UserName, @EmailId, @ContactNo)", connection);
/**
* Commands to insert the user response to the database
**/
insertCommand.Parameters.Add(new SqlParameter("TokenDescription", sentence.Desc));
insertCommand.Parameters.Add(new SqlParameter("ServerDetails", sentence.ServerName));
insertCommand.Parameters.Add(new SqlParameter("MiddlewareDetails", sentence.MiddlewareName));
insertCommand.Parameters.Add(new SqlParameter("DatabaseDetails", sentence.DBName));
insertCommand.Parameters.Add(new SqlParameter("TokenDetails", System.DateTimeOffset.Now));
insertCommand.Parameters.Add(new SqlParameter("SentimentScore", sentimentScore));
insertCommand.Parameters.Add(new SqlParameter("UserName", sentence.Name));
insertCommand.Parameters.Add(new SqlParameter("EmailId", sentence.Contact));
insertCommand.Parameters.Add(new SqlParameter("ContactNo", sentence.PhoneContact));
var DBresult = insertCommand.ExecuteNonQuery();
if (DBresult > 0)
{
connection.Close();
string ReconnectionEstablish = ConfigurationManager.ConnectionStrings["APRMConnection"].ConnectionString;
SqlConnection conn = new SqlConnection(ReconnectionEstablish);
conn.Open();
var selectTicketId = "Select Id from dbo.BotDetails WHERE UserName = @UserName";
SqlCommand selectCommand = new SqlCommand(selectTicketId, conn);
selectCommand.Parameters.AddWithValue("@UserName", sentence.Name);
using (SqlDataReader queryReader = selectCommand.ExecuteReader())
{
while (queryReader.Read())
{
String retrieveId = queryReader.GetInt32(0).ToString();
//await context.PostAsync("Your ticket has been raised successfully, " + retrieveId + " your token id for the raised ticket");
}
}
}
else
{
await context.PostAsync("Some problem occured, Please try again after sometime");
}
/**
* Close the existing connection to the DB
**/
connection.Close();
}
else
{
/**
* Checks wether the connection is established or not
**/
Console.WriteLine("Not connected");
}
/**
* Snow connection code
**/
string DetailDescription = sentence.Desc + " the services are running on server " + sentence.ServerName + ", using " + sentence.DBName + " database and the" + sentence.MiddlewareName + " service";
String incidentNo = string.Empty;
incidentNo = SnowLogger.CreateIncidentServiceNow(sentence.Desc, sentence.Contact, DetailDescription, sentence.CategoryName);
Console.WriteLine(incidentNo);
await context.PostAsync("Your ticket has been raised successfully, " + incidentNo + " your token id for the raised ticket");
await context.PostAsync("Please keep the note of above token number. as it would be used for future references");
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
context.Done(this);
}
/**
* Method to check the user sentiment and report it to the user
*/
await context.PostAsync($"You rated our service as: {Math.Round(sentiment * 10, 1)}/10");
if (sentiment <= 0.5)
{
PromptDialog.Confirm(context, ResumeAfterFeedbackClarification, "I see it wasn't perfect, can we contact you about this?");
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}