/// <summary>
/// Callback on insert of Watson results - using the transaction submitted but NOT committed
///
/// For each action, use the most likely sentiment (highest SentimentScore)
/// Use Net Promoter score (promoters - detractors), normalized to [0, 1000] where 500 is neutral
/// </summary>
/// <param name="transaction">data associated with the watson transaction</param>
public static void TicketSentimentStrategy(DataContext db, ActionToAnalyze actionToAnalyze, ActionSentimentScore maxScore)
{
if (actionToAnalyze.IsAgent)
{
return;
}
try
{
// normalize to [0, 1000]
double actionScore = Convert.ToDouble(ToneSentiment.ToneSentiments[maxScore.SentimentID].SentimentMultiplier.Value) * Convert.ToDouble(maxScore.SentimentScore);
actionScore = 500 * actionScore + 500;
// submit TicketSentiment
TicketSentiment score = null;
CreateTicketSentiment(db, actionToAnalyze, actionScore, out score);
score.TicketSentimentScore = (int)Math.Round(score.AverageActionSentiment);
score.SetSentimentID(maxScore.SentimentID);
db.SubmitChanges();
}
catch (Exception e2)
{
WatsonEventLog.WriteEntry("Exception caught at select from ACtionsToAnalyze or HttpPOST:", e2);
Console.WriteLine(e2.ToString());
}
}
static void PublishActionSentiment(ActionToAnalyze actionToAnalyze)
{
// Process The ActionToAnalyze results
WatsonTransaction transaction = null; // Transaction that can be rolled back
try
{
// 1. Insert ActionSentiment and ActionSentimentScores
// 2. run the TicketSentimentStrategy to create TicketSentimentScore
// 3. delete the ActionToAnalyze
_singleThreadedTransactions.WaitOne(); // connection does not support parallel transactions
transaction = new WatsonTransaction();
transaction.RecordWatsonResults(actionToAnalyze);
transaction.Commit();
}
catch (Exception e2)
{
if (transaction != null)
{
transaction.Rollback();
}
WatsonEventLog.WriteEntry("Watson analysis failed - system will retry", e2);
Console.WriteLine(e2.ToString());
}
finally
{
if (transaction != null)
{
transaction.Dispose();
}
_singleThreadedTransactions.ReleaseMutex();
}
}
static bool CreateTicketSentiment(DataContext db, ActionToAnalyze actionToAnalyze, double actionScore, out TicketSentiment score)
{
Table <TicketSentiment> table = db.GetTable <TicketSentiment>();
score = table.Where(t => (t.TicketID == actionToAnalyze.TicketID) && (t.IsAgent == actionToAnalyze.IsAgent)).FirstOrDefault();
if (score == null)
{
score = new TicketSentiment()
{
TicketID = actionToAnalyze.TicketID,
OrganizationID = actionToAnalyze.OrganizationID,
IsAgent = actionToAnalyze.IsAgent,
TicketDateCreated = actionToAnalyze.DateCreated,
TicketSentimentScore = 0,
Sad = false,
Frustrated = false,
Satisfied = false,
Excited = false,
Polite = false,
Impolite = false,
Sympathetic = false,
AverageActionSentiment = actionScore,
ActionSentimentCount = 1
};
table.InsertOnSubmit(score);
return(true);
}
return(false);
}
/// <summary> Pack Action to Message </summary>
private static Task PackActionToMessage(ActionToAnalyze actionToAnalyze, ref WatsonMessage message)
{
Task result = null;
// Action fit into a single utterance?
Utterance utterance;
if (actionToAnalyze.TryGetUtterance(out utterance))
{
// Utterance fit into message?
if (!message.TryAdd(actionToAnalyze, utterance))
{
result = SendMessage(ref message); // send this one
if (!message.TryAdd(actionToAnalyze, utterance)) // start a new message
{
Debugger.Break();
}
}
}
else
{
// multiple utterances
List <Utterance> utterances = actionToAnalyze.PackActionToUtterances();
if (!message.TryAdd(actionToAnalyze, utterances))
{
result = SendMessage(ref message); // send this one
if (!message.TryAdd(actionToAnalyze, utterances)) // start a new message
{
Debugger.Break();
}
}
}
return(result);
}
public bool TryAdd(ActionToAnalyze actionToAnalyze, Utterance utterance)
{
// Max 50 per call
if (UtteranceCount + 1 > WatsonUtterancePerAPICall)
{
return(false);
}
_actions.Add(actionToAnalyze);
_messageUtterances.Add(new MessageUtterance(actionToAnalyze, utterance));
return(true);
}
public bool TryAdd(ActionToAnalyze actionToAnalyze, List <Utterance> utterances)
{
// Max 50 per call
if (UtteranceCount + utterances.Count > WatsonUtterancePerAPICall)
{
return(false);
}
_actions.Add(actionToAnalyze);
foreach (Utterance utterance in utterances)
{
_messageUtterances.Add(new MessageUtterance(actionToAnalyze, utterance));
}
return(true);
}
/// <summary>
/// Get the actions to analyze (dbo.ActionToAnalyze) and post to Watson on the BlueMix account
/// </summary>
static public void AnalyzeActions()
{
// without this the HTTP message to Watson returns 405 - failure on send
ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12 | SecurityProtocolType.Tls11 | SecurityProtocolType.Tls;
// keep a handle to all the async transactions we start
List <Task> asyncTransactionsInProcess = new List <Task>();
try
{
//opens a sqlconnection at the specified location
string connectionString = ConfigurationManager.AppSettings.Get("ConnectionString");
using (SqlConnection connection = new SqlConnection(connectionString))
using (DataContext db = new DataContext(connection))
{
// initialize static data
ToneSentiment.Initialize(db);
ActionToAnalyze.Initialize(db);
// read ActionToAnalyze
Table <ActionSentiment> actionSentimentTable = db.GetTable <ActionSentiment>();
Table <ActionToAnalyze> actionToAnalyzeTable = db.GetTable <ActionToAnalyze>();
IQueryable <ActionToAnalyze> actionToAnalyzeQuery = from action in actionToAnalyzeTable select action;
ActionToAnalyze[] actions = actionToAnalyzeQuery.ToArray();
// Pack the actions to analyze into watson messages
PackActionsToMessages(asyncTransactionsInProcess, db, actionSentimentTable, actions);
// ...the remainder can wait to be packed into a call when we have more
}
}
catch (Exception e2)
{
WatsonEventLog.WriteEntry("Exception in AnalyzeActions", e2);
Console.WriteLine(e2.ToString());
}
finally
{
// wait until all the tone chat messages have been received and recorded
Task.WaitAll(asyncTransactionsInProcess.ToArray(), 5 * 60 * 1000); // 5 minute timeout just in case...
}
}
/// <summary> Write the message results back to the database </summary>
public void PublishWatsonResponse(UtteranceToneList watsonResponse)
{
foreach (UtteranceResponse response in watsonResponse.utterances_tone)
{
MessageUtterance messageUtterance = _messageUtterances[response.utterance_id];
Utterance sent = messageUtterance._utterance;
if (string.CompareOrdinal(sent.text, response.utterance_text) != 0)
{
Debugger.Break();
}
ActionToAnalyze action = messageUtterance._action;
action.AddSentiment(response);
}
// update the action with the accumulated results
foreach (ActionToAnalyze action in _actions)
{
PublishActionSentiment(action);
}
}
public static void FindActionsToAnalyze()
{
try
{
string connectionString = ConfigurationManager.AppSettings.Get("ConnectionString");
using (SqlConnection sqlConnection1 = new SqlConnection(connectionString))
using (DataContext db = new DataContext(sqlConnection1))
{
var results = db.ExecuteQuery <ActionGetForWatson>("Exec " + "dbo.ActionsGetForWatson");
Table <ActionToAnalyze> table = db.GetTable <ActionToAnalyze>();
foreach (ActionGetForWatson a in results)
{
ActionToAnalyze actionToAnalyze = new ActionToAnalyze()
{
ActionID = a.ActionID,
TicketID = a.TicketID,
UserID = a.UserID,
OrganizationID = a.OrganizationID,
IsAgent = a.IsAgent == 1,
DateCreated = a.DateCreated,
ActionDescription = ActionToAnalyze.CleanString(a.ActionDescription), // clean the text of HTML and special characters
};
if (!table.Where(u => u.ActionID == actionToAnalyze.ActionID).Any())
{
table.InsertOnSubmit(actionToAnalyze);
}
}
db.SubmitChanges();
}
}
catch (Exception e)
{
WatsonEventLog.WriteEntry("Exception while reading from action table:", e);
Console.WriteLine(e.ToString());
}
}
public void RecordWatsonResults(ActionToAnalyze actionToAnalyze)
{
// insert ActionSentiment
ActionSentiment sentiment = InsertActionSentiment(_db, actionToAnalyze);
_db.SubmitChanges(); // get the DB generated ID
int actionSentimentID = sentiment.ActionSentimentID;
// insert child records - ActionSentimentScore(s)
List <Tones> tones = actionToAnalyze.GetTones();
List <ActionSentimentScore> scores = InsertSentimentScores(tones, _db, actionSentimentID);
// update the corresponding ticket sentiment
if (!actionToAnalyze.IsAgent)
{
ActionSentimentScore maxScore = scores.Where(s => s.SentimentScore == scores.Max(a => a.SentimentScore)).First();
TicketSentiment.TicketSentimentStrategy(_db, actionToAnalyze, maxScore);
}
// Delete ActionToAnalyze
actionToAnalyze.DeleteOnSubmit(_db);
_db.SubmitChanges();
}
/// <summary>
/// Create the ActionSentiment for the ActionID
/// </summary>
/// <param name="db">context for insert/submit</param>
/// <param name="actionToAnalyze">action to analyze</param>
/// <returns></returns>
static ActionSentiment InsertActionSentiment(DataContext db, ActionToAnalyze actionToAnalyze)
{
// already exists?
Table <ActionSentiment> table = db.GetTable <ActionSentiment>();
if (table.Where(u => u.ActionID == actionToAnalyze.ActionID).Any())
{
WatsonEventLog.WriteEntry("duplicate ActionID in ActionSentiment table " + actionToAnalyze.ActionID, EventLogEntryType.Error);
}
// Insert
ActionSentiment sentiment = new ActionSentiment
{
ActionID = actionToAnalyze.ActionID,
TicketID = actionToAnalyze.TicketID,
UserID = actionToAnalyze.UserID,
OrganizationID = actionToAnalyze.OrganizationID,
IsAgent = actionToAnalyze.IsAgent,
DateCreated = DateTime.UtcNow
};
table.InsertOnSubmit(sentiment);
return(sentiment);
}
/// <summary>
/// Create the ActionSentiment for the ActionID
/// </summary>
/// <param name="db">context for insert/submit</param>
/// <param name="a">action to analyze</param>
/// <returns></returns>
static ActionSentiment InsertActionSentiment(DataContext db, ActionToAnalyze a)
{
// already exists?
Table <ActionSentiment> table = db.GetTable <ActionSentiment>();
if (table.Where(u => u.ActionID == a.ActionID).Any())
{
throw new Exception("Error: ActionSentiment already exists?");
}
// Insert
ActionSentiment sentiment = new ActionSentiment
{
ActionID = a.ActionID,
TicketID = a.TicketID,
UserID = a.UserID,
OrganizationID = a.OrganizationID,
IsAgent = a.IsAgent,
DateCreated = DateTime.Now
};
table.InsertOnSubmit(sentiment);
return(sentiment);
}
public MessageUtterance(ActionToAnalyze actionToAnalyze, Utterance utterance)
{
_action = actionToAnalyze;
_utterance = utterance;
}
/// <summary>
/// Constructor to wrap the "using" of SqlConnection, SqlTransaction, and DataContext
/// </summary>
/// <param name="tones"></param>
/// <param name="actionToAnalyze"></param>
public WatsonResultsTransaction(Utterance utterance, ActionToAnalyze actionToAnalyze, Action <WatsonTransactionCallback> callback)
{
List <Tones> tones = utterance.tones;
if (tones == null)
{
return; // ?
}
// open the connection
try
{
_singleThreadedTransactions.WaitOne();
string connectionString = ConfigurationManager.AppSettings.Get("ConnectionString");
_connection = new SqlConnection(connectionString); // using
_connection.Open(); // connection must be open to begin transaction
// start the transaction
_transaction = _connection.BeginTransaction(); // using
// create a data context
_db = new DataContext(_connection); // using
_db.Transaction = _transaction;
// insert ActionSentiment
ActionSentiment sentiment = InsertActionSentiment(_db, actionToAnalyze);
_db.SubmitChanges(); // get the DB generated ID
int actionSentimentID = sentiment.ActionSentimentID;
// insert child records - ActionSentimentScore(s)
List <ActionSentimentScore> scores = InsertSentimentScores(tones, _db, actionSentimentID);
// Delete ActionToAnalyze
actionToAnalyze.DeleteOnSubmit(_db);
_db.SubmitChanges();
if (callback != null)
{
WatsonTransactionCallback transaction = new WatsonTransactionCallback()
{
_db = _db,
_sentiment = sentiment,
_scores = scores
};
callback(transaction);
}
// Success!
_transaction.Commit();
}
catch (Exception e)
{
if (_transaction != null)
{
_transaction.Rollback();
}
EventLog.WriteEntry(EVENT_SOURCE, "********************PublishToTable SubmitTransaction: Exception " + e.Message + " ### " + e.Source + " ### " + e.StackTrace.ToString());
Console.WriteLine("Exception at insert into Action Sentiment:" + e.Message + "###" + e.Source + " ----- STACK: " + e.StackTrace.ToString());
}
finally
{
_singleThreadedTransactions.ReleaseMutex();
}
}
