static bool GetAndPostAdherence(AdherenceType type)
{
bool needsStaticData = false;
string realtimeConnectionString = "Data Source=10.63.3.6;Initial Catalog=TMDailyLog;Connection Timeout=15;Integrated Security=false;User ID=CfA;Password=CfA2012";
using (SqlConnection connection = new SqlConnection(realtimeConnectionString))
{
try
{
connection.Open();
String adherenceData = GetAdherenceData(connection, type);
needsStaticData = PostAdherenceData(adherenceData);
}
catch (SqlException e)
{
Console.WriteLine(e.Message);
}
}
return(needsStaticData);
}
static string GetAdherenceData(SqlConnection connection, AdherenceType type)
{
//string queryString = "SELECT TOP 10 * FROM dbo.TIME_POINT_CROSSING;";
//string queryString = "SELECT T.ROUTE_ID, T.TRIP_ID, (T.ACT_ARRIVAL_TIME - T.SCHEDULED_TIME) AS Adherence, " +
//string queryString = "SELECT T.ROUTE_ID, T.TRIP_ID, (T.ACT_DEPARTURE_TIME - T.SCHEDULED_TIME) AS Adherence, " +
//"T.GEO_NODE_ID, T.TIME_POINT_ID FROM dbo.TIME_POINT_CROSSING AS T " +
// "JOIN (SELECT TREF.TRIP_ID, MAX(TREF.ACT_ARRIVAL_TIME) AS MAX_ACT_ARRIVAL_TIME FROM dbo.TIME_POINT_CROSSING AS TREF " +
// "WHERE TREF.CALENDAR_ID=@today GROUP BY TREF.TRIP_ID) AS AT " +
// "ON (T.TRIP_ID = AT.TRIP_ID AND T.ACT_ARRIVAL_TIME = AT.MAX_ACT_ARRIVAL_TIME) " +
// "WHERE T.CALENDAR_ID=@today AND T.TRIP_ID IS NOT NULL;";
string queryString;
if (type == AdherenceType.Short)
{
queryString = "SELECT TOP 200 L.LOGGED_MESSAGE_SHORT_ID, L.ADHERENCE, W.WORK_PIECE_ID, L.MESSAGE_TIMESTAMP " +
"FROM TMDailyLog.dbo.LOGGED_MESSAGE_SHORT AS L " +
"JOIN TMMain.dbo.VEHICLE AS V " +
"ON L.SOURCE_HOST=V.RNET_ADDRESS " +
"JOIN (SELECT * FROM TMDailyLog.dbo.DAILY_WORK_PIECE AS DWP " +
"WHERE DWP.CALENDAR_ID=@today " +
"AND DWP.SCHEDULED_LOGON_TIME < @seconds " +
"AND DWP.SCHEDULED_LOGOFF_TIME > @seconds) AS W " +
"ON W.CURRENT_VEHICLE_ID=V.VEHICLE_ID " +
"WHERE L.LOGGED_MESSAGE_SHORT_ID>@lastId " +
"AND L.CALENDAR_ID=@today " +
"ORDER BY L.LOGGED_MESSAGE_SHORT_ID DESC;";
}
else
{
queryString = "SELECT TOP 200 L.TRANSMITTED_MESSAGE_ID, L.ADHERENCE, W.WORK_PIECE_ID, L.MESSAGE_TIMESTAMP " +
"FROM TMDailyLog.dbo.LOGGED_MESSAGE AS L " +
"JOIN TMMain.dbo.VEHICLE AS V " +
"ON L.SOURCE_HOST=V.RNET_ADDRESS " +
"JOIN (SELECT * FROM TMDailyLog.dbo.DAILY_WORK_PIECE AS DWP " +
"WHERE DWP.CALENDAR_ID=@today " +
"AND DWP.SCHEDULED_LOGON_TIME < @seconds " +
"AND DWP.SCHEDULED_LOGOFF_TIME > @seconds) AS W " +
"ON W.CURRENT_VEHICLE_ID=V.VEHICLE_ID " +
"WHERE L.TRANSMITTED_MESSAGE_ID>@lastId " +
"AND L.CALENDAR_ID=@today " +
"AND L.ADHERENCE IS NOT NULL " +
"ORDER BY L.TRANSMITTED_MESSAGE_ID DESC;";
}
SqlCommand command = new SqlCommand(queryString, connection);
// TODO: make sure we operate properly in Detroit time, considering
// daylight savings.
DateTime today = DateTime.Today;
// The calendar ID should have the form 120120612
int todayNum = 100000000 + (today.Year * 10000) + (today.Month * 100) + today.Day;
command.Parameters.Add(new SqlParameter("@today", todayNum));
// TODO: make sure we operate properly in Detroit time, considering
// daylight savings and "transit time", which would count 1am as
// part of the previous day.
var now = DateTime.UtcNow.AddHours(-4);
var seconds = (now.Hour * 3600) + (now.Minute * 60) + now.Second;
command.Parameters.Add(new SqlParameter("@seconds", seconds));
if (type == AdherenceType.Short)
{
command.Parameters.Add(new SqlParameter("@lastId", lastShortId));
}
else
{
command.Parameters.Add(new SqlParameter("@lastId", lastRegularId));
}
SqlDataReader reader = command.ExecuteReader();
string data = String.Empty;
var readId = false;
try
{
StringBuilder sb = new StringBuilder();
while (reader.Read())
{
if (!readId)
{
int? id = reader[0] as int?;
if (id.HasValue)
{
if (type == AdherenceType.Short)
{
lastShortId = id.Value;
}
else
{
lastRegularId = id.Value;
}
}
readId = true;
}
sb.Append(String.Format("{0},{1},{2},{3}\n", reader[0], reader[1], reader[2], reader[3]));
Console.WriteLine(String.Format("{0}, {1}, {2}, {3}", reader[0], reader[1], reader[2], reader[3]));
}
data = sb.ToString();
}
finally
{
reader.Close();
}
return data;
}
static string GetAdherenceData(SqlConnection connection, AdherenceType type)
{
//string queryString = "SELECT TOP 10 * FROM dbo.TIME_POINT_CROSSING;";
//string queryString = "SELECT T.ROUTE_ID, T.TRIP_ID, (T.ACT_ARRIVAL_TIME - T.SCHEDULED_TIME) AS Adherence, " +
//string queryString = "SELECT T.ROUTE_ID, T.TRIP_ID, (T.ACT_DEPARTURE_TIME - T.SCHEDULED_TIME) AS Adherence, " +
//"T.GEO_NODE_ID, T.TIME_POINT_ID FROM dbo.TIME_POINT_CROSSING AS T " +
// "JOIN (SELECT TREF.TRIP_ID, MAX(TREF.ACT_ARRIVAL_TIME) AS MAX_ACT_ARRIVAL_TIME FROM dbo.TIME_POINT_CROSSING AS TREF " +
// "WHERE TREF.CALENDAR_ID=@today GROUP BY TREF.TRIP_ID) AS AT " +
// "ON (T.TRIP_ID = AT.TRIP_ID AND T.ACT_ARRIVAL_TIME = AT.MAX_ACT_ARRIVAL_TIME) " +
// "WHERE T.CALENDAR_ID=@today AND T.TRIP_ID IS NOT NULL;";
string queryString;
if (type == AdherenceType.Short)
{
queryString = "SELECT TOP 200 L.LOGGED_MESSAGE_SHORT_ID, L.ADHERENCE, W.WORK_PIECE_ID, L.MESSAGE_TIMESTAMP " +
"FROM TMDailyLog.dbo.LOGGED_MESSAGE_SHORT AS L " +
"JOIN TMMain.dbo.VEHICLE AS V " +
"ON L.SOURCE_HOST=V.RNET_ADDRESS " +
"JOIN (SELECT * FROM TMDailyLog.dbo.DAILY_WORK_PIECE AS DWP " +
"WHERE DWP.CALENDAR_ID=@today " +
"AND DWP.SCHEDULED_LOGON_TIME < @seconds " +
"AND DWP.SCHEDULED_LOGOFF_TIME > @seconds) AS W " +
"ON W.CURRENT_VEHICLE_ID=V.VEHICLE_ID " +
"WHERE L.LOGGED_MESSAGE_SHORT_ID>@lastId " +
"AND L.CALENDAR_ID=@today " +
"ORDER BY L.LOGGED_MESSAGE_SHORT_ID DESC;";
}
else
{
queryString = "SELECT TOP 200 L.TRANSMITTED_MESSAGE_ID, L.ADHERENCE, W.WORK_PIECE_ID, L.MESSAGE_TIMESTAMP " +
"FROM TMDailyLog.dbo.LOGGED_MESSAGE AS L " +
"JOIN TMMain.dbo.VEHICLE AS V " +
"ON L.SOURCE_HOST=V.RNET_ADDRESS " +
"JOIN (SELECT * FROM TMDailyLog.dbo.DAILY_WORK_PIECE AS DWP " +
"WHERE DWP.CALENDAR_ID=@today " +
"AND DWP.SCHEDULED_LOGON_TIME < @seconds " +
"AND DWP.SCHEDULED_LOGOFF_TIME > @seconds) AS W " +
"ON W.CURRENT_VEHICLE_ID=V.VEHICLE_ID " +
"WHERE L.TRANSMITTED_MESSAGE_ID>@lastId " +
"AND L.CALENDAR_ID=@today " +
"AND L.ADHERENCE IS NOT NULL " +
"ORDER BY L.TRANSMITTED_MESSAGE_ID DESC;";
}
SqlCommand command = new SqlCommand(queryString, connection);
// TODO: make sure we operate properly in Detroit time, considering
// daylight savings.
DateTime today = DateTime.Today;
// The calendar ID should have the form 120120612
int todayNum = 100000000 + (today.Year * 10000) + (today.Month * 100) + today.Day;
command.Parameters.Add(new SqlParameter("@today", todayNum));
// TODO: make sure we operate properly in Detroit time, considering
// daylight savings and "transit time", which would count 1am as
// part of the previous day.
var now = DateTime.UtcNow.AddHours(-4);
var seconds = (now.Hour * 3600) + (now.Minute * 60) + now.Second;
command.Parameters.Add(new SqlParameter("@seconds", seconds));
if (type == AdherenceType.Short)
{
command.Parameters.Add(new SqlParameter("@lastId", lastShortId));
}
else
{
command.Parameters.Add(new SqlParameter("@lastId", lastRegularId));
}
SqlDataReader reader = command.ExecuteReader();
string data = String.Empty;
var readId = false;
try
{
StringBuilder sb = new StringBuilder();
while (reader.Read())
{
if (!readId)
{
int?id = reader[0] as int?;
if (id.HasValue)
{
if (type == AdherenceType.Short)
{
lastShortId = id.Value;
}
else
{
lastRegularId = id.Value;
}
}
readId = true;
}
sb.Append(String.Format("{0},{1},{2},{3}\n", reader[0], reader[1], reader[2], reader[3]));
Console.WriteLine(String.Format("{0}, {1}, {2}, {3}", reader[0], reader[1], reader[2], reader[3]));
}
data = sb.ToString();
}
finally
{
reader.Close();
}
return(data);
}
static bool GetAndPostAdherence(AdherenceType type)
{
bool needsStaticData = false;
string realtimeConnectionString = "Data Source=10.63.3.6;Initial Catalog=TMDailyLog;Connection Timeout=15;Integrated Security=false;User ID=CfA;Password=CfA2012";
using (SqlConnection connection = new SqlConnection(realtimeConnectionString))
{
try
{
connection.Open();
String adherenceData = GetAdherenceData(connection, type);
needsStaticData = PostAdherenceData(adherenceData);
}
catch (SqlException e)
{
Console.WriteLine(e.Message);
}
}
return needsStaticData;
}
