private static void InsertAlert(AlertData alert,
rt_alertRow existingAlert,
rt_alertDataTable alertDataTable,
rt_alert_active_periodDataTable activePeriodDataTable,
rt_alert_informed_entityDataTable informedEntityDataTable)
{
var newAlertRow = alertDataTable.Newrt_alertRow();
newAlertRow.file_time = (int)alert.HeaderTimestamp;
newAlertRow.alert_id = alert.AlertId;
newAlertRow.version_id = existingAlert?.version_id + 1 ?? 1;
newAlertRow.cause = alert.Cause;
newAlertRow.effect = alert.Effect;
newAlertRow.header_text = alert.HeaderText;
newAlertRow.description_text = alert.DescriptionText;
newAlertRow.url = alert.Url;
newAlertRow.closed = alert.Closed;
newAlertRow.first_file_time = (int)alert.HeaderTimestamp;
newAlertRow.last_file_time = (int)alert.HeaderTimestamp;
alertDataTable.Rows.Add(newAlertRow);
//Console.WriteLine("*Alert: " + newAlertRow.Alert);
foreach (var activePeriod in alert.ActivePeriods)
{
var newActivePeriodRow = activePeriodDataTable.Newrt_alert_active_periodRow();
newActivePeriodRow.version_id = newAlertRow.version_id;
newActivePeriodRow.active_period_start = (int)activePeriod.ActivePeriodStart;
if (activePeriod.ActivePeriodEnd > 0)
{
newActivePeriodRow.active_period_end = (int)activePeriod.ActivePeriodEnd;
}
newActivePeriodRow.alert_id = activePeriod.AlertId;
activePeriodDataTable.Rows.Add(newActivePeriodRow);
//Console.WriteLine(" *Active Period: " + newActivePeriodRow.ActivePeriod);
}
foreach (var informedEntity in alert.InformedEntities)
{
var newInformedEntityRow = informedEntityDataTable.Newrt_alert_informed_entityRow();
newInformedEntityRow.version_id = newAlertRow.version_id;
newInformedEntityRow.alert_id = informedEntity.AlertId;
newInformedEntityRow.agency_id = informedEntity.AgencyId;
if (!string.IsNullOrEmpty(informedEntity.RouteId))
{
newInformedEntityRow.route_id = informedEntity.RouteId;
}
newInformedEntityRow.route_type = informedEntity.RouteType;
if (!string.IsNullOrEmpty(informedEntity.TripId))
{
newInformedEntityRow.trip_id = informedEntity.TripId;
}
if (!string.IsNullOrEmpty(informedEntity.StopId))
{
newInformedEntityRow.stop_id = informedEntity.StopId;
}
informedEntityDataTable.Rows.Add(newInformedEntityRow);
//Console.WriteLine(" *Informed Entity: " + newInformedEntityRow.InformedEntity);
}
}
public int CheckClosedAlerts(List <AlertData> alerts, List <string> alertIdsToClose, out int moreToClose, int closeMax = 200, bool useTemporaryTables = false)
{
var alertsSaved = 0;
moreToClose = 0;
using (var connection = new SqlConnection(SqlConnectionString))
{
connection.Open();
var alertsTableAdapter = new rt_alertTableAdapter {
Connection = connection
};
var alertsTimeTableAdapter = new rt_alert_timesTableAdapter {
Connection = connection
};
var activePeriodsTableAdapter = new rt_alert_active_periodTableAdapter {
Connection = connection, ClearBeforeFill = false
};
var informedEntitiesTableAdapter = new rt_alert_informed_entityTableAdapter {
Connection = connection, ClearBeforeFill = false
};
if (useTemporaryTables)
{
alertsTableAdapter.SetTempTable();
activePeriodsTableAdapter.SetTempTable();
informedEntitiesTableAdapter.SetTempTable();
alertsTimeTableAdapter.SetTempTable();
//rt_alert.TableName = rt_alert.TableName + "_temp";
//rt_alert_active_period.TableName = rt_alert_active_period.TableName + "_temp";
//rt_alert_informed_entity.TableName = rt_alert_informed_entity.TableName + "_temp";
}
if (alertIdsToClose == null || !alertIdsToClose.Any())
{
alertsTableAdapter.FillByOpenAlerts(rt_alert);
}
else
{
var alertIds = string.Join(",", alertIdsToClose.Select(x => $"'{x}'"));
var query = $" and (alerts.alert_id in ({alertIds}))";
alertsTableAdapter.SelectOpenAlertsCommand.CommandText += query;
alertsTableAdapter.FillByOpenAlerts(rt_alert);
}
if (!rt_alert.Any())
{
return(0);
}
var currentTime = DateTime.UtcNow;
var currentAlertIds = alerts.Select(x => x.AlertId).OrderBy(x => x).ToList();
var fileTime = alerts.FirstOrDefault()?.HeaderTimestamp ?? Utils.GetSecondsFromUtc(currentTime);
var alertsToClose = rt_alert.Where(x => !currentAlertIds.Contains(x.alert_id)).OrderBy(x => x.alert_id).ToList();
var closedAlertsDataTable = new rt_alertDataTable {
TableName = rt_alert.TableName
};
var closedActivePeriodsDataTable = new rt_alert_active_periodDataTable {
TableName = rt_alert_active_period.TableName
};
var closedInformedEntitiesDataTable = new rt_alert_informed_entityDataTable {
TableName = rt_alert_informed_entity.TableName
};
moreToClose = Math.Max(alertsToClose.Count - closeMax, 0);
if (alertsToClose.Count == 0)
{
return(0);
}
foreach (var alertRow in alertsToClose.Take(closeMax))
{
activePeriodsTableAdapter.ClearBeforeFill = true;
informedEntitiesTableAdapter.ClearBeforeFill = true;
alertsTimeTableAdapter.ClearBeforeFill = true;
activePeriodsTableAdapter.FillByAlertIdVersionId(rt_alert_active_period, alertRow.alert_id, alertRow.version_id);
informedEntitiesTableAdapter.FillByAlertIdVersionId(rt_alert_informed_entity, alertRow.alert_id, alertRow.version_id);
alertsTimeTableAdapter.FillByNextFileTimeAfterAlert(rt_alert_times, alertRow.record_id);
if (alertIdsToClose == null || !alertIdsToClose.Any())
{
var nextAlertTime = rt_alert_times.FirstOrDefault();
if (nextAlertTime != null)
{
fileTime = (ulong)nextAlertTime.file_time;
}
}
var alert = alertRow.Alert;
alert.Closed = true;
alert.HeaderTimestamp = fileTime;
InsertAlert(alert, alertRow, closedAlertsDataTable, closedActivePeriodsDataTable, closedInformedEntitiesDataTable);
alertsSaved++;
}
using (var transaction = connection.BeginTransaction())
{
using (var sqlBulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.KeepIdentity, transaction))
{
Utils.BulkInsert(closedAlertsDataTable, sqlBulkCopy, new List <string> {
"record_id"
});
Utils.BulkInsert(closedActivePeriodsDataTable, sqlBulkCopy);
Utils.BulkInsert(closedInformedEntitiesDataTable, sqlBulkCopy);
transaction.Commit();
}
}
}
return(alertsSaved);
}
