static void Main(string[] args)
{
var connectionString = args[0];
var key = args[1];
using (var conn = new SqlConnection(connectionString)) {
conn.Open();
var usages = conn.Query<Db.Usage>("SELECT U.* FROM Usages U INNER JOIN Users ON Users.UserId = U.UserId WHERE ApiKey = @Key ORDER BY [Timestamp]", new { Key = key }, commandType: CommandType.Text).ToList();
var references = conn.Query<Db.Reference>("SELECT R.* FROM [References] R INNER JOIN Users ON Users.UserId = R.UserId WHERE ApiKey = @Key ORDER BY [Date]", new { Key = key }, commandType: CommandType.Text).ToList();
var startDate = usages.First().Timestamp.Date;
var endDate = usages.Last().Timestamp.Date;
var lastUsage = usages.First();
var newUsages = new List<DailyUsage>();
for (DateTime date = startDate; date <= endDate; date = date.AddDays(1)) {
var newDailyUsage = new DailyUsage() { Date = date };
var reference = references.SingleOrDefault(x => x.Date == date);
newDailyUsage.Reference = new Reference();
if (reference != null) {
newDailyUsage.Reference.Electricity = reference.Electricity;
newDailyUsage.Reference.Gas = reference.Gas;
}
var dateUsages = usages.Where(x => x.Timestamp >= date && x.Timestamp < date.AddDays(1)).OrderBy(x=> x.Timestamp).ToList();
newDailyUsage.Standings = new HourlyStanding[25];
for (int i = 0; i <= 24; i++) {
var hourlyUsage = dateUsages.SingleOrDefault(x => x.Timestamp == date.AddHours(i));
if (hourlyUsage != null)
lastUsage = hourlyUsage;
newDailyUsage.Standings[i] = new HourlyStanding() {
Hour = i,
E1 = i == 24 ? lastUsage.E1Current : lastUsage.E1Start,
E2 = i == 24 ? lastUsage.E2Current: lastUsage.E2Start,
E1Retour = i == 24 ? lastUsage.E1RetourCurrent : lastUsage.E1RetourStart,
E2Retour = i == 24 ? lastUsage.E2RetourCurrent : lastUsage.E2RetourStart,
PvProduction = 0,
Gas = i == 24 ? lastUsage.GasCurrent : lastUsage.GasStart
};
}
newUsages.Add(newDailyUsage);
}
string json = JsonConvert.SerializeObject(newUsages.ToArray());
System.IO.File.WriteAllText(@"C:\temp\mongousages.txt", json);
}
}
static void Main(string[] args)
{
var connectionString = args[0];
var key = args[1];
using (var conn = new SqlConnection(connectionString)) {
conn.Open();
var usages = conn.Query <Db.Usage>("SELECT U.* FROM Usages U INNER JOIN Users ON Users.UserId = U.UserId WHERE ApiKey = @Key ORDER BY [Timestamp]", new { Key = key }, commandType: CommandType.Text).ToList();
var references = conn.Query <Db.Reference>("SELECT R.* FROM [References] R INNER JOIN Users ON Users.UserId = R.UserId WHERE ApiKey = @Key ORDER BY [Date]", new { Key = key }, commandType: CommandType.Text).ToList();
var startDate = usages.First().Timestamp.Date;
var endDate = usages.Last().Timestamp.Date;
var lastUsage = usages.First();
var newUsages = new List <DailyUsage>();
for (DateTime date = startDate; date <= endDate; date = date.AddDays(1))
{
var newDailyUsage = new DailyUsage()
{
Date = date
};
var reference = references.SingleOrDefault(x => x.Date == date);
newDailyUsage.Reference = new Reference();
if (reference != null)
{
newDailyUsage.Reference.Electricity = reference.Electricity;
newDailyUsage.Reference.Gas = reference.Gas;
}
var dateUsages = usages.Where(x => x.Timestamp >= date && x.Timestamp < date.AddDays(1)).OrderBy(x => x.Timestamp).ToList();
newDailyUsage.Standings = new HourlyStanding[25];
for (int i = 0; i <= 24; i++)
{
var hourlyUsage = dateUsages.SingleOrDefault(x => x.Timestamp == date.AddHours(i));
if (hourlyUsage != null)
{
lastUsage = hourlyUsage;
}
newDailyUsage.Standings[i] = new HourlyStanding()
{
Hour = i,
E1 = i == 24 ? lastUsage.E1Current : lastUsage.E1Start,
E2 = i == 24 ? lastUsage.E2Current: lastUsage.E2Start,
E1Retour = i == 24 ? lastUsage.E1RetourCurrent : lastUsage.E1RetourStart,
E2Retour = i == 24 ? lastUsage.E2RetourCurrent : lastUsage.E2RetourStart,
PvProduction = 0,
Gas = i == 24 ? lastUsage.GasCurrent : lastUsage.GasStart
};
}
newUsages.Add(newDailyUsage);
}
string json = JsonConvert.SerializeObject(newUsages.ToArray());
System.IO.File.WriteAllText(@"C:\temp\mongousages.txt", json);
}
}