public async Task <bool> StoreTranscriptionAsync(
Guid transcriptionId,
string locale,
string fileName,
float approximateCost,
SpeechTranscript speechTranscript)
{
if (speechTranscript == null)
{
throw new ArgumentNullException(nameof(speechTranscript));
}
try
{
Connection = new SqlConnection(DBConnectionString);
Connection.Open();
var query = "INSERT INTO dbo.Transcriptions (ID, Locale, Name, Source, Timestamp, Duration, DurationInSeconds, NumberOfChannels, ApproximateCost)" +
" VALUES (@id, @locale, @name, @source, @timestamp, @duration, @durationInSeconds, @numberOfChannels, @approximateCost)";
using (var command = new SqlCommand(query, Connection))
{
command.Parameters.AddWithValue("@id", transcriptionId);
command.Parameters.AddWithValue("@locale", locale);
command.Parameters.AddWithValue("@name", fileName);
command.Parameters.AddWithValue("@source", speechTranscript.Source);
command.Parameters.AddWithValue("@timestamp", speechTranscript.Timestamp);
command.Parameters.AddWithValue("@duration", speechTranscript.Duration ?? string.Empty);
command.Parameters.AddWithValue("@durationInSeconds", TimeSpan.FromTicks(speechTranscript.DurationInTicks).TotalSeconds);
command.Parameters.AddWithValue("@numberOfChannels", speechTranscript.CombinedRecognizedPhrases.Count());
command.Parameters.AddWithValue("@approximateCost", approximateCost);
var result = await command.ExecuteNonQueryAsync().ConfigureAwait(false);
if (result < 0)
{
Logger.LogInformation("Did not store json in Db, command did not update table");
}
else
{
var phrasesByChannel = speechTranscript.RecognizedPhrases.GroupBy(t => t.Channel);
foreach (var phrases in phrasesByChannel)
{
var channel = phrases.Key;
await StoreCombinedRecognizedPhrasesAsync(transcriptionId, channel, speechTranscript, phrases).ConfigureAwait(false);
}
}
}
Connection.Close();
}
catch (SqlException e)
{
Logger.LogInformation(e.ToString());
return(false);
}
return(true);
}
public static string ToHTML(SpeechTranscript transcription, string filename)
{
if (transcription == null)
{
throw new ArgumentNullException(nameof(transcription));
}
var isMultiChannel = transcription?.RecognizedPhrases != null && transcription.RecognizedPhrases.Any(s => s != null && s.Channel != 0);
var html = $@"<html lang='en'> <head> <meta http-equiv='content-type' content='text/html; charset=utf-8'> <title>{filename}</title>";
html += @"<link rel='stylesheet' href='https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/css/bootstrap.min.css' integrity='sha384-Vkoo8x4CGsO3+Hhxv8T/Q5PaXtkKtu6ug5TOeNV6gBiFeWPGFN9MuhOf23Q9Ifjh' crossorigin='anonymous'>";
html += @"<style>a.inline-decoration { color:#000000 !important; text-decoration:none; } .utterance-playing { background-color: lightgray; opacity: 0.9; }</style>";
html += @"<link href='https://amp.azure.net/libs/amp/2.3.3/skins/amp-default/azuremediaplayer.min.css' rel='stylesheet'>";
html += @"<script src='https://amp.azure.net/libs/amp/2.3.3/azuremediaplayer.min.js'></script>";
html += @"</head>";
html += $@"<body> <h3>{filename}</h3> <div class='text-center'><audio id='azuremediaplayer2' class='azuremediaplayer amp-default-skin amp-big-play-centered' tabindex='0'></audio></div><br><br><div class='text-center'><img </div><br><table class='table'><thead><tr><td>Speaker</td>{(isMultiChannel ? "<td>Channel</td><td>Text</td><td>Text</td>" : "<td>Text</td>")}</tr></thead><tbody>";
var htmlBuilder = new StringBuilder(html);
if (transcription.RecognizedPhrases != null)
{
foreach (var recognizedPhrase in transcription.RecognizedPhrases.OrderBy(s => s.Offset))
{
var sentence = recognizedPhrase.NBest.FirstOrDefault();
if (sentence == null)
{
continue;
}
htmlBuilder.Append("<tr>");
htmlBuilder.Append("<td>" + recognizedPhrase.Speaker + @"</td>");
var confidenceIndication = "<span";
if (sentence.Confidence <= .5)
{
confidenceIndication = "<span class='table-danger'";
}
else if (sentence.Confidence <= .7)
{
confidenceIndication = "<span class='table-warning'";
}
if (isMultiChannel)
{
htmlBuilder.Append($"<td>{recognizedPhrase.Channel}</td>");
if (recognizedPhrase.Channel % 2 == 1)
{
htmlBuilder.Append($"<td><a href='javascript:jumpTime({recognizedPhrase.Offset});' class='inline-decoration'></a></td>");
htmlBuilder.Append($"<td><a href='javascript:jumpTime({recognizedPhrase.Offset});' class='inline-decoration'{confidenceIndication}>{sentence.Display}</span></a></td>");
}
else
{
htmlBuilder.Append($"<td><a href='javascript:jumpTime({recognizedPhrase.Offset});' class='inline-decoration'{confidenceIndication}>{sentence.Display}</span></a></td>");
htmlBuilder.Append($"<td><a href='javascript:jumpTime({recognizedPhrase.Offset});' class='inline-decoration'></a></td>");
}
}
else
{
htmlBuilder.Append($"<td><a href='javascript:jumpTime({recognizedPhrase.Offset});' class='inline-decoration'{confidenceIndication}>{sentence.Display}</span></a></td>");
}
htmlBuilder.Append("</tr>");
htmlBuilder.AppendLine(string.Empty);
}
}
htmlBuilder.Append(@"</tbody></table>");
htmlBuilder.Append(@"<script> var myOptions = { controls: true, width: '450', height: '250', poster: 'http://img-prod-cms-rt-microsoft-com.akamaized.net/cms/api/am/imageFileData/RE2qVsJ?ver=3f74' }; </script>");
htmlBuilder.Append(@"</body></html>");
return(htmlBuilder.ToString());
}
private async Task StoreCombinedRecognizedPhrasesAsync(Guid transcriptionId, int channel, SpeechTranscript speechTranscript, IEnumerable <RecognizedPhrase> recognizedPhrases)
{
var combinedRecognizedPhraseID = Guid.NewGuid();
var combinedPhrases = speechTranscript.CombinedRecognizedPhrases.Where(t => t.Channel == channel).FirstOrDefault();
var query = "INSERT INTO dbo.CombinedRecognizedPhrases (ID, TranscriptionID, Channel, Lexical, Itn, MaskedItn, Display, SentimentPositive, SentimentNeutral, SentimentNegative)" +
" VALUES (@id, @transcriptionID, @channel, @lexical, @itn, @maskedItn, @display, @sentimentPositive, @sentimentNeutral, @sentimentNegative)";
using var command = new SqlCommand(query, Connection);
command.Parameters.AddWithValue("@id", combinedRecognizedPhraseID);
command.Parameters.AddWithValue("@transcriptionID", transcriptionId);
command.Parameters.AddWithValue("@channel", channel);
command.Parameters.AddWithValue("@lexical", combinedPhrases?.Lexical ?? string.Empty);
command.Parameters.AddWithValue("@itn", combinedPhrases?.ITN ?? string.Empty);
command.Parameters.AddWithValue("@maskedItn", combinedPhrases?.MaskedITN ?? string.Empty);
command.Parameters.AddWithValue("@display", combinedPhrases?.Display ?? string.Empty);
command.Parameters.AddWithValue("@sentimentPositive", combinedPhrases?.Sentiment?.Positive ?? 0f);
command.Parameters.AddWithValue("@sentimentNeutral", combinedPhrases?.Sentiment?.Neutral ?? 0f);
command.Parameters.AddWithValue("@sentimentNegative", combinedPhrases?.Sentiment?.Negative ?? 0f);
var result = await command.ExecuteNonQueryAsync().ConfigureAwait(false);
if (result < 0)
{
Logger.LogInformation("Did not store combined phrase in Db, command did not update table");
}
else
{
var orderedPhrases = recognizedPhrases.OrderBy(p => p.OffsetInTicks);
var previousEndInMs = 0.0;
foreach (var phrase in orderedPhrases)
{
await StoreRecognizedPhraseAsync(combinedRecognizedPhraseID, phrase, previousEndInMs).ConfigureAwait(false);
previousEndInMs = (TimeSpan.FromTicks(phrase.OffsetInTicks) + TimeSpan.FromTicks(phrase.DurationInTicks)).TotalMilliseconds;
}
}
}
private async Task StoreCombinedRecognizedPhrasesAsync(Guid transcriptionId, int channel, SpeechTranscript speechTranscript, IEnumerable <RecognizedPhrase> recognizedPhrases)
{
var combinedRecognizedPhraseID = Guid.NewGuid();
var combinedPhrases = speechTranscript.CombinedRecognizedPhrases.Where(t => t.Channel == channel).FirstOrDefault();
var query = "INSERT INTO dbo.CombinedRecognizedPhrases (ID, TranscriptionID, Channel, Lexical, Itn, MaskedItn, Display)" +
" VALUES (@id, @transcriptionID, @channel, @lexical, @itn, @maskedItn, @display)";
using (var command = new SqlCommand(query, Connection))
{
command.Parameters.AddWithValue("@id", combinedRecognizedPhraseID);
command.Parameters.AddWithValue("@transcriptionID", transcriptionId);
command.Parameters.AddWithValue("@channel", channel);
if (combinedPhrases != null)
{
command.Parameters.AddWithValue("@lexical", combinedPhrases.Lexical);
command.Parameters.AddWithValue("@itn", combinedPhrases.ITN);
command.Parameters.AddWithValue("@maskedItn", combinedPhrases.MaskedITN);
command.Parameters.AddWithValue("@display", combinedPhrases.Display);
}
else
{
command.Parameters.AddWithValue("@lexical", string.Empty);
command.Parameters.AddWithValue("@itn", string.Empty);
command.Parameters.AddWithValue("@maskedItn", string.Empty);
command.Parameters.AddWithValue("@display", string.Empty);
}
var result = await command.ExecuteNonQueryAsync().ConfigureAwait(false);
if (result < 0)
{
Logger.LogInformation("Did not store combined phrase in Db, command did not update table");
}
else
{
foreach (var phrase in recognizedPhrases)
{
await StoreRecognizedPhraseAsync(combinedRecognizedPhraseID, phrase).ConfigureAwait(false);
}
}
}
}
