public static void SaveUserQueues(IList <UserQueue> userQueue, string connectionString)
{
try
{
var counter = new TransactionCounter();
DatabaseContext dbCtx = null;
foreach (var user in userQueue)
{
ManageDatabaseContext(connectionString, counter, ref dbCtx);
if (dbCtx.UserQueues.Any(x => x.UserId.Equals(user.UserId, StringComparison.OrdinalIgnoreCase) && x.Queue.Equals(user.Queue, StringComparison.OrdinalIgnoreCase)))
{
counter.RowsIgnored++;
continue;
}
dbCtx.UserQueues.Add(user);
counter.RowsAdded++;
}
Trace.Info($"User Queues added:{counter.RowsAdded}, ignored:{counter.RowsIgnored}");
if (dbCtx == null)
{
return;
}
dbCtx.SaveChanges();
dbCtx.Dispose();
}
catch (Exception ex)
{
Trace.Error(ex);
}
}
public static void SaveGroupMembers(IList <GroupMember> groupMember, string connectionString)
{
try
{
var counter = new TransactionCounter();
DatabaseContext dbCtx = null;
foreach (var member in groupMember)
{
ManageDatabaseContext(connectionString, counter, ref dbCtx);
if (dbCtx.GroupMembers.Any(x => x.id.Equals(member.id, StringComparison.OrdinalIgnoreCase) && x.groupId.Equals(member.groupId, StringComparison.OrdinalIgnoreCase)))
{
counter.RowsIgnored++;
continue;
}
dbCtx.GroupMembers.Add(member);
counter.RowsAdded++;
}
Trace.Info($"Group Members added:{counter.RowsAdded}, ignored:{counter.RowsIgnored}");
if (dbCtx == null)
{
return;
}
dbCtx.SaveChanges();
dbCtx.Dispose();
}
catch (Exception ex)
{
Trace.Error(ex);
}
}
public static void SaveDataTablesRows(IList <DataTableRows> dataTableId, string connectionString)
{
try
{
var counter = new TransactionCounter();
DatabaseContext dbCtx = null;
foreach (var row in dataTableId)
{
ManageDatabaseContext(connectionString, counter, ref dbCtx);
if (dbCtx.DataTableRows.Any(x => x.dataTableId.Equals(row.dataTableId, StringComparison.OrdinalIgnoreCase)))
{
counter.RowsIgnored++;
continue;
}
dbCtx.DataTableRows.Add(row);
counter.RowsAdded++;
}
Trace.Info($"DataTables Rows added:{counter.RowsAdded}, ignored:{counter.RowsIgnored}");
if (dbCtx == null)
{
return;
}
dbCtx.SaveChanges();
dbCtx.Dispose();
}
catch (Exception ex)
{
Trace.Error(ex);
}
}
public static void SaveMetrics(IList <ConversationMetric> metrics, string connectionString)
{
try
{
var counter = new TransactionCounter();
DatabaseContext dbCtx = null;
foreach (var metric in metrics)
{
ManageDatabaseContext(connectionString, counter, ref dbCtx);
if (dbCtx.ConversationMetrics.Any(x => x.SessionId.Equals(metric.SessionId, StringComparison.OrdinalIgnoreCase) && x.attrName.Equals(metric.attrName, StringComparison.OrdinalIgnoreCase)))
{
counter.RowsIgnored++;
continue;
}
dbCtx.ConversationMetrics.Add(metric);
counter.RowsAdded++;
}
Trace.Info($"Conversation metrics added:{counter.RowsAdded}, ignored:{counter.RowsIgnored}");
if (dbCtx == null)
{
return;
}
dbCtx.SaveChanges();
dbCtx.Dispose();
}
catch (Exception ex)
{
Trace.Error(ex);
}
}
public static void SaveAttrs(IList <ParticipantAttr> participantAttrs, string connectionString)
{
try
{
var counter = new TransactionCounter();
DatabaseContext dbCtx = null;
foreach (var attr in participantAttrs)
{
ManageDatabaseContext(connectionString, counter, ref dbCtx);
if (dbCtx.ParticipantAttrs.Any(x => x.conversationId.Equals(attr.conversationId, StringComparison.OrdinalIgnoreCase) && x.participantId.Equals(attr.participantId, StringComparison.OrdinalIgnoreCase) && x.attrName.Equals(attr.attrName, StringComparison.OrdinalIgnoreCase)))
{
counter.RowsIgnored++;
continue;
}
dbCtx.ParticipantAttrs.Add(attr);
counter.RowsAdded++;
}
Trace.Info($"Participant Attrs added:{counter.RowsAdded}, ignored:{counter.RowsIgnored}");
if (dbCtx == null)
{
return;
}
dbCtx.SaveChanges();
dbCtx.Dispose();
}
catch (Exception ex)
{
Trace.Error(ex);
}
}
public static void SavePresenceDefinitions(Dictionary <string, string> presence, string connectionString)
{
using (var dbCtx = new DatabaseContext(connectionString))
{
try
{
var counter = new TransactionCounter();
foreach (var q in presence)
{
var pDef = q.Value.Split('|');
if (dbCtx.PresenceDefinitions.Any(x => x.id == q.Key)) // does id exist
{
// update name if id does exist
if (pDef[0] == null)
{
continue;
}
var existingItem = dbCtx.PresenceDefinitions.FirstOrDefault(x => x.id == q.Key);
if (existingItem == null || existingItem.name == pDef[0] || existingItem.systemPresence == pDef[1])
{
continue;
}
existingItem.name = pDef[0];
existingItem.systemPresence = pDef[1];
counter.RowsUpdated++;
}
else
{
// create an item if id doesn't exist
dbCtx.PresenceDefinitions.Add(new PresenceDefinitions()
{
id = q.Key, name = pDef[0], systemPresence = pDef[1]
});
counter.RowsAdded++;
}
}
dbCtx.SaveChanges();
Trace.Info($"PresenceDefinitions added:{counter.RowsAdded}, updated:{counter.RowsUpdated}");
}
catch (Exception ex)
{
Trace.Fatal(ex);
}
}
}
private static void ManageDatabaseContext(string connectionString, TransactionCounter counter, ref DatabaseContext databaseContext)
{
const long batchSize = 1000; // number of rows in one inserting batch
if (databaseContext == null)
{
Trace.Debug("Db context is null, creating a new one.");
databaseContext = new DatabaseContext(connectionString);
return;
}
if (counter.RowsAdded == 0 || counter.RowsAdded % batchSize != 0)
{
return;
}
Trace.Debug($"Batch size reached {batchSize}, saving changes and recreating db context.");
databaseContext.SaveChanges();
databaseContext.Dispose();
databaseContext = new DatabaseContext(connectionString);
}
public static void SaveLanguages(Dictionary <string, string> languages, string connectionString)
{
using (var dbCtx = new DatabaseContext(connectionString))
{
try
{
var counter = new TransactionCounter();
foreach (var q in languages)
{
if (dbCtx.Languages.Any(x => x.id == q.Key)) // does id exist
{
// update name if id does exist
if (q.Value == null)
{
continue;
}
var existingItem = dbCtx.Languages.FirstOrDefault(x => x.id == q.Key);
if (existingItem == null || existingItem.name == q.Value)
{
continue;
}
existingItem.name = q.Value;
counter.RowsUpdated++;
}
else
{
// create an item if id doesn't exist
dbCtx.Languages.Add(new Language()
{
id = q.Key, name = q.Value
});
counter.RowsAdded++;
}
}
dbCtx.SaveChanges();
Trace.Info($"Languages added:{counter.RowsAdded}, updated:{counter.RowsUpdated}");
}
catch (Exception ex)
{
Trace.Fatal(ex);
}
}
}
public static void SaveConversationAggregates(IEnumerable <ConversationAggregate> conversationAggregateList, string connectionString)
{
try
{
var counter = new TransactionCounter();
DatabaseContext dbCtx = null;
foreach (var ca in conversationAggregateList)
{
ManageDatabaseContext(connectionString, counter, ref dbCtx);
if (!dbCtx.ConversationAggregates.Any(x =>
x.queueId.Equals(ca.queueId, StringComparison.CurrentCultureIgnoreCase) &&
x.mediaType.Equals(ca.mediaType, StringComparison.CurrentCultureIgnoreCase) &&
x.intervalUtc.Equals(ca.intervalUtc, StringComparison.CurrentCultureIgnoreCase) &&
x.metric.Equals(ca.metric, StringComparison.CurrentCultureIgnoreCase)))
{
// add conversation aggregate
dbCtx.ConversationAggregates.Add(ca);
counter.RowsAdded++;
}
else
{
// conversation aggregate does exist
counter.RowsIgnored++;
}
}
Trace.Info($"Conversations aggregates added:{counter.RowsAdded}, ignored:{counter.RowsIgnored}");
if (dbCtx == null)
{
return;
}
dbCtx.SaveChanges();
dbCtx.Dispose();
}
catch (Exception ex)
{
Trace.Error(ex);
}
}
private static void ManageDatabaseContext(string connectionString, TransactionCounter counter, ref DatabaseContext databaseContext)
{
//
// Dividing inserts for smaller batches.
// It is fix for the issue:
// https://bitbucket.org/eccemea/purecloud-stats-dispatcher/issues/20/outofmemoryexception
//
const long batchSize = 200; // number of conversations in one inserting batch
if (databaseContext == null)
{
Trace.Debug("Db context is null, creating a new one.");
databaseContext = new DatabaseContext(connectionString);
return;
}
if (counter.RowsAdded == 0 || counter.RowsAdded % batchSize != 0)
{
return;
}
Trace.Debug($"Batch size reached {batchSize}, saving changes and recreating db context.");
databaseContext.SaveChanges();
databaseContext.Dispose();
databaseContext = new DatabaseContext(connectionString);
}
internal ReplayableCommitProcess(TransactionCommitProcess localCommitProcess, TransactionCounter transactionCounter)
{
this._localCommitProcess = localCommitProcess;
this._transactionCounter = transactionCounter;
}
public static void SaveConversations(IList <Conversation> conversations, string connectionString)
{
try
{
var counter = new TransactionCounter();
DatabaseContext dbCtx = null;
foreach (var c in conversations)
{
ManageDatabaseContext(connectionString, counter, ref dbCtx);
if (c.IsFinished())
{
if (!dbCtx.Conversations.Any(x => x.conversationId == c.conversationId)) // if conversation does not exist in conversations
{
// add to conversations a new finished conversation...
dbCtx.Conversations.Add(c);
}
else
{
// update the existing conversation when it is finished again...
// (it handles email replies that have the same conversation id)
var exisitngEntity = dbCtx.Conversations.Include(x => x.participants.Select(y => y.sessions.Select(z => z.segments))).FirstOrDefault(a => a.conversationId == c.conversationId);
if (exisitngEntity != null)
{
dbCtx.Entry(exisitngEntity).Entity.conversationStart = c.conversationStart;
dbCtx.Entry(exisitngEntity).Entity.conversationEnd = c.conversationEnd;
dbCtx.Entry(exisitngEntity).Entity.participants = c.participants;
dbCtx.Entry(exisitngEntity).Entity.divisionIds = c.divisionIds;
counter.RowsUpdated++;
}
}
// ...and eventually remove it from ongoing conversation bag
var oc = dbCtx.OngoingConversation.FirstOrDefault(x => x.conversationId == c.conversationId);
if (oc == null)
{
continue;
}
dbCtx.OngoingConversation.Remove(oc);
counter.RowsRemovedFromTheBag++;
}
else if (!dbCtx.OngoingConversation.Any(x => x.conversationId == c.conversationId)) // if conversation does not exist in ongoing conversations bag
{
// add to ongoing conversations bag a new ongoing conversation
var oc = new OngoingConversation()
{
conversationId = c.conversationId
};
dbCtx.OngoingConversation.Add(oc);
counter.RowsPutIntoTheBag++;
}
else
{
// ignore duplicate
Trace.Debug($"Ignoring conversation for conversationId:{c.conversationId}.");
counter.RowsIgnored++;
}
}
Trace.Info($"Conversations added:{counter.RowsAdded}, updated:{counter.RowsUpdated}, ignored:{counter.RowsIgnored}, added ongoing:{counter.RowsPutIntoTheBag}, removed ongoing:{counter.RowsRemovedFromTheBag}");
if (dbCtx == null)
{
return;
}
dbCtx.SaveChanges();
dbCtx.Dispose();
}
catch (Exception ex)
{
Trace.Error(ex);
}
}
public static void SaveUserDetails(IList <UserDetail> userDetails, string connectionString)
{
try
{
var counterPrimaryPresence = new TransactionCounter();
var counterRoutingStatus = new TransactionCounter();
DatabaseContext dbCtx = null;
foreach (var ud in userDetails)
{
ManageDatabaseContext(connectionString, counterPrimaryPresence, ref dbCtx);
// <primary presence>
if (ud.primaryPresence != null && ud.primaryPresence.Any())
{
foreach (var pp in ud.primaryPresence)
{
if (pp.endTime == DateTime.MinValue)
{
continue;
}
if (!dbCtx.PrimaryPresence.Any(x => x.userId.Equals(ud.userId) && x.startTime.Equals(pp.startTime) && x.systemPresence.Equals(pp.systemPresence, StringComparison.OrdinalIgnoreCase)))
{
pp.userId = ud.userId; // as per API response the user id is parsed on UserDetai level so it must be assigned here before pushing row to the database
dbCtx.PrimaryPresence.Add(pp);
counterPrimaryPresence.RowsAdded++;
}
else
{
counterPrimaryPresence.RowsIgnored++;
}
}
}
// </primary presence>
// <routing status>
if (ud.routingStatus != null && ud.routingStatus.Any())
{
foreach (var rs in ud.routingStatus)
{
if (rs.endTime == DateTime.MinValue)
{
continue;
}
if (!dbCtx.RoutingStatus.Any(x => x.userId.Equals(ud.userId) && x.startTime.Equals(rs.startTime) && x.routingStatus.Equals(rs.routingStatus, StringComparison.OrdinalIgnoreCase)))
{
rs.userId = ud.userId; // as per API response the user id is parsed on UserDetai level so it must be assigned here before pushing row to the database
dbCtx.RoutingStatus.Add(rs);
counterRoutingStatus.RowsAdded++;
}
else
{
counterRoutingStatus.RowsIgnored++;
}
}
}
// </routing status>
}
Trace.Info($"Primary presences added:{counterPrimaryPresence.RowsAdded}, ignored:{counterPrimaryPresence.RowsIgnored}");
Trace.Info($"Routing statuses added:{counterRoutingStatus.RowsAdded}, ignored:{counterRoutingStatus.RowsIgnored}");
if (dbCtx == null)
{
return;
}
dbCtx.SaveChanges();
dbCtx.Dispose();
}
catch (Exception ex)
{
Trace.Error(ex);
}
}
