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); }