protected bool Equals(AgdFile other)
{
return string.Equals(FileName, other.FileName);
}
private void AdjustFileForFallDst(AgdFile agdFile, IDbConnection db, DaylightTime daylightTime)
{
//see if there's any epochs before DST
int epochsBeforeFallDst =
db.Scalar<int>(
db.From<AgdTableTimestampAxis1>()
.Select(Sql.Count("*"))
.Where(q => q.TimestampTicks < daylightTime.End.Ticks));
bool dataBeforeFallDst = epochsBeforeFallDst > 0;
int epochsAfterFallDst =
db.Scalar<int>(
db.From<AgdTableTimestampAxis1>()
.Select(Sql.Count("*"))
.Where(q => q.TimestampTicks > daylightTime.End.Ticks));
bool dataAfterFallDst = epochsAfterFallDst > 0;
if (dataBeforeFallDst && !dataAfterFallDst)
{
//there's only data before
//no need to do anything
richTextBox1.AppendText(string.Format("{0}: not adjusting file for fall DST\r\n", agdFile));
return;
}
richTextBox1.AppendText(string.Format("{0}: adjusting file for fall DST\r\n", agdFile));
//delete the extra hour of data (October 25th 0200)
string sql = string.Format("DELETE FROM data WHERE dataTimestamp >= {0} AND dataTimeStamp < {1}",
daylightTime.End.Ticks, daylightTime.End.Add(daylightTime.Delta).Ticks);
db.ExecuteSql(sql);
//adjust timestamps for data after DST by subtracting an hour
sql =
string.Format(
"UPDATE data SET dataTimestamp = dataTimestamp - {0} WHERE dataTimestamp >= {1}",
daylightTime.Delta.Ticks, daylightTime.End.Ticks);
db.ExecuteSql(sql);
//adjust WTV
if (db.TableExists("filters"))
{
sql =
string.Format(
"UPDATE filters SET filterStartTimestamp = filterStartTimestamp - {0} WHERE filterStartTimestamp >= {1}",
daylightTime.Delta.Ticks, daylightTime.End.Ticks);
db.ExecuteSql(sql);
sql =
string.Format(
"UPDATE filters SET filterStopTimestamp = filterStopTimestamp - {0} WHERE filterStopTimestamp >= {1}",
daylightTime.Delta.Ticks, daylightTime.End.Ticks);
db.ExecuteSql(sql);
}
if (db.TableExists("wtvBouts"))
{
sql =
string.Format(
"UPDATE wtvBouts SET startTicks = startTicks - {0} WHERE startTicks >= {1}",
daylightTime.Delta.Ticks, daylightTime.End.Ticks);
db.ExecuteSql(sql);
sql =
string.Format(
"UPDATE wtvBouts SET stopTicks = stopTicks - {0} WHERE stopTicks >= {1}",
daylightTime.Delta.Ticks, daylightTime.End.Ticks);
db.ExecuteSql(sql);
}
//adjust capsense
if (db.TableExists("capsense"))
{
//delete the extra hour of data (October 25th 0200)
sql = string.Format("DELETE FROM capsense WHERE timeStamp >= {0} AND timeStamp < {1}",
daylightTime.End.Ticks, daylightTime.End.Add(daylightTime.Delta).Ticks);
db.ExecuteSql(sql);
//adjust timestamps for data after DST by subtracting an hour
sql =
string.Format(
"UPDATE capsense SET timeStamp = timeStamp - {0} WHERE timeStamp >= {1}",
daylightTime.Delta.Ticks, daylightTime.End.Ticks);
db.ExecuteSql(sql);
}
}
private void AdjustFileForSpringDst(AgdFile agdFile, IDbConnection db, DaylightTime daylightTime)
{
//see if there's any epochs before DST
int epochsBeforeSpringDst =
db.Scalar<int>(
db.From<AgdTableTimestampAxis1>()
.Select(Sql.Count("*"))
.Where(q => q.TimestampTicks < daylightTime.Start.Ticks));
bool dataBeforeSpringDst = epochsBeforeSpringDst > 0;
int epochsAfterSpringDst =
db.Scalar<int>(
db.From<AgdTableTimestampAxis1>()
.Select(Sql.Count("*"))
.Where(q => q.TimestampTicks > daylightTime.Start.Ticks));
bool dataAfterSpringDst = epochsAfterSpringDst > 0;
if (!dataBeforeSpringDst && dataAfterSpringDst)
{
//no need to do anything
richTextBox1.AppendText(string.Format("{0}: NOT adjusting file for spring DST\r\n", agdFile));
return;
}
richTextBox1.AppendText(string.Format("{0}: adjusting file for spring DST\r\n", agdFile));
//adjust timestamps for data after DST by subtracting an hour
string sql =
string.Format(
"UPDATE data SET dataTimestamp = dataTimestamp + {0} WHERE dataTimestamp >= {1}",
daylightTime.Delta.Ticks, daylightTime.Start.Ticks);
db.ExecuteSql(sql);
//add the extra hour of data (October 25th 0200)
var totalEpochsToInsert = daylightTime.Delta.TotalSeconds / agdFile.EpochLengthInSeconds;
var ticksPerEpoch = TimeSpan.FromSeconds(agdFile.EpochLengthInSeconds).Ticks;
for (int i = 0; i < totalEpochsToInsert; i++)
{
sql = string.Format("insert into data(dataTimestamp) values ({0});",
(daylightTime.Start.Ticks + (i * ticksPerEpoch)));
db.ExecuteSql(sql);
}
var columnNames = db.GetColumnNames("data");
foreach (var columnName in columnNames)
{
sql = string.Format("UPDATE data SET {0} = 0 WHERE {0} IS NULL", columnName);
db.ExecuteSql(sql);
}
//adjust WTV
if (db.TableExists("filters"))
{
sql =
string.Format(
"UPDATE filters SET filterStartTimestamp = filterStartTimestamp + {0} WHERE filterStartTimestamp >= {1}",
daylightTime.Delta.Ticks, daylightTime.Start.Ticks);
db.ExecuteSql(sql);
sql =
string.Format(
"UPDATE filters SET filterStopTimestamp = filterStopTimestamp + {0} WHERE filterStopTimestamp >= {1}",
daylightTime.Delta.Ticks, daylightTime.Start.Ticks);
db.ExecuteSql(sql);
}
if (db.TableExists("wtvBouts"))
{
sql =
string.Format(
"UPDATE wtvBouts SET startTicks = startTicks + {0} WHERE startTicks >= {1}",
daylightTime.Delta.Ticks, daylightTime.Start.Ticks);
db.ExecuteSql(sql);
sql =
string.Format(
"UPDATE wtvBouts SET stopTicks = stopTicks + {0} WHERE stopTicks >= {1}",
daylightTime.Delta.Ticks, daylightTime.Start.Ticks);
db.ExecuteSql(sql);
}
//adjust capsense
if (db.TableExists("capsense"))
{
//adjust timestamps for data after DST by subtracting an hour
sql = string.Format(
"UPDATE capsense SET timeStamp = timeStamp + {0} WHERE timeStamp >= {1}",
daylightTime.Delta.Ticks, daylightTime.Start.Ticks);
db.ExecuteSql(sql);
}
}
private void AdjustFile(AgdFile agdFile, string timeZoneInfoId)
{
if (agdFile == null)
throw new NullReferenceException("agdFile can't be null");
if (string.IsNullOrEmpty(timeZoneInfoId))
throw new NullReferenceException("timeZoneInfoId can't be null");
var daylightTime = TimeZoneHelpers.GetDaylightChanges(timeZoneInfoId, agdFile.LastEpoch.Year);
richTextBox1.AppendText(string.Format("{0}: started adjusting\r\n", agdFile));
using (var db = new OrmLiteConnectionFactory(agdFile.GetSQLiteConnectionString(), SqliteDialect.Provider).OpenDbConnection())
{
AdjustFileForFallDst(agdFile, db, daylightTime);
AdjustFileForSpringDst(agdFile, db, daylightTime);
var oldFirstEpoch = agdFile.FirstEpoch;
var oldLastEpoch = agdFile.LastEpoch;
var totalEpochs = agdFile.EpochCount;
agdFile.GetFirstAndLastEpoch();
richTextBox1.AppendText(string.Format("{0}: original first epoch - {1:G} | new first epoch - {2:G}\r\n", agdFile, oldFirstEpoch, agdFile.FirstEpoch));
richTextBox1.AppendText(string.Format("{0}: original last epoch - {1:G} | new last epoch - {2:G}\r\n", agdFile, oldLastEpoch, agdFile.LastEpoch));
richTextBox1.AppendText(string.Format("{0}: original totalEpochs - {1:G} | new total epochs - {2:G}\r\n", agdFile, totalEpochs, agdFile.EpochCount));
richTextBox1.AppendText(string.Format("{0}: finished adjusting file\r\n", agdFile));
}
}
