internal static MeasureKwH GetKwhDay(double kwhValue)
{
MeasureKwH kwh = new MeasureKwH();
kwh.DateTime = Utils.GetTodaysDate();
kwh.PrivateInverterId = 2;
kwh.TimeMode = PVLog.Enums.E_TimeMode.day;
kwh.Value = kwhValue;
return kwh;
}
public SortedKwhTable GetDayKwhByDateRange(DateTime startDate, DateTime endDate, int systemID)
{
startDate = Utils.CropHourMinuteSecond(startDate);
//mysql handles "between date" inclusive so we have to shrink the timeframe
endDate = Utils.CropHourMinuteSecond(endDate).AddDays(-1);
SortedKwhTable result = new SortedKwhTable();
string text = @"
SELECT ID, Date, i.InverterId, i.PublicInverterId, kwh
FROM kwh_by_day k
INNER JOIN inverter i
ON k.InverterID = i.InverterId
AND i.PlantId = @plantId
WHERE
(DATE BETWEEN @startDate AND @endDate)
ORDER BY Date ASC, PublicInverterId ASC;";
var sqlCom = base.GetReadCommand(text);
//Add Parameters
sqlCom.Parameters.AddWithValue("@plantID", systemID);
sqlCom.Parameters.AddWithValue("@startDate", startDate);
sqlCom.Parameters.AddWithValue("@endDate", endDate);
//Execute SQL and read data
using (var rdr = sqlCom.ExecuteReader())
{
while (rdr.Read())
{
var measureKwh = new MeasureKwH();
measureKwh.Value = rdr.GetDouble("kwh");
measureKwh.ID = rdr.GetInt32("ID");
measureKwh.DateTime = rdr.GetDateTime("Date");
measureKwh.PublicInverterId = rdr.GetInt32("PublicInverterId");
measureKwh.TimeMode = Enums.E_TimeMode.day;
measureKwh.PrivateInverterId = rdr.GetInt32("InverterId");
result.AddMeasure(measureKwh);
}
}
return result;
}
public void InsertDayKwh(MeasureKwH kwhDay)
{
string text = @"INSERT INTO kwh_by_day (Date, InverterId, kwh)
VALUES (@date, @inverterId, @kwh)
ON DUPLICATE KEY UPDATE kwh = @kwh;";
try
{
ProfiledWriteConnection.Execute(text, new
{
inverterId = kwhDay.PrivateInverterId,
date = kwhDay.DateTime,
kwh = kwhDay.Value
});
}
catch (MySqlException ex)
{
Logger.LogError(ex);
throw ex;
}
}
public void SummarizeKwhToDaysTest()
{
//Create some measures for 10 days starting at 8am to 18 pm
SortedKwhTable hourSums = new SortedKwhTable();
int inverterID = 1;
var startDate = new DateTime(2010, 1, 1);
var endDate = new DateTime(2010, 2, 1).AddDays(-1);
var countDate = new DateTime(2010, 1, 10, 8, 0, 0);
//10 days from 10th to 20th
for (int i = 0; i < 10; i++)
{
//8 - 17 o'clock
while (countDate.Hour < 18)
{
MeasureKwH measure = new MeasureKwH();
measure.DateTime = countDate;
measure.PrivateInverterId = inverterID;
measure.TimeMode = PVLog.Enums.E_TimeMode.hour;
measure.Value = 1; // 1kwh
//add to list
hourSums.AddMeasure(measure);
countDate = countDate.AddHours(1);
}
countDate = countDate.AddDays(1).AddHours(-10);
}
//we should get 10 kw for each day
SortedKwhTable actual = KwhCalculator.SummarizeKwh(hourSums, startDate, endDate, E_TimeMode.day, true);
for (int i = 1; i < 10; i++)
{
var currentDaySum = actual.ToList()[i];
// day 1 - 9 = 0 kwh
if (i < 9)
{
Assert.AreEqual(0, currentDaySum.Value);
}
// day 10 - 19 10 kwh per day
else if (i < 19)
{
Assert.AreEqual(10, currentDaySum.Value);
}
// day 20 - 31 = 0 kwh
else if (i <= 31)
{
Assert.AreEqual(0, currentDaySum.Value);
}
Assert.AreEqual(inverterID, currentDaySum.PrivateInverterId);
}
//null testing
hourSums = new SortedKwhTable();
actual = KwhCalculator.SummarizeKwh(hourSums, startDate, endDate, E_TimeMode.month, true);
foreach (var dayItem in actual.ToList())
{
Assert.AreEqual(0, dayItem.Value);
}
}
/// <summary>
/// Initializes the resultlist. It can be chosen wether we want to fill gaps or not.
/// This is important for the "kwh by day" calculation because if we would fill gaps there, kwh_days would be
/// deleted when there is no more measurement data for the timeframe
/// </summary>
/// <param name="measures"></param>
/// <param name="startDate"></param>
/// <param name="endDate"></param>
/// <param name="timeMode"></param>
/// <param name="systemID"></param>
/// <param name="fillGaps"></param>
/// <returns></returns>
private static SortedKwhTable InitializeResultList(SortedKwhTable measures
, DateTime startDate, DateTime endDate
, E_TimeMode timeMode
, bool fillGaps)
{
//init day sum list for the hole time frame
SortedKwhTable result = new SortedKwhTable();
// Initialize the result with empty values first
var countDate = startDate;
while (countDate < endDate)
{
foreach (var inverterInfo in measures.KnownInverters)
{
if (fillGaps || ContainsMeasuresForThisTimePoint(measures, countDate, timeMode, inverterInfo.Value))
{
MeasureKwH dummy = new MeasureKwH();
dummy.DateTime = countDate;
dummy.PrivateInverterId = inverterInfo.Key;
dummy.PublicInverterId = inverterInfo.Value;
dummy.Value = 0;
result.AddMeasure(dummy);
}
}
//increase countdate
countDate = IncreaseCountDate(timeMode, countDate);
}
return result;
}
/// <summary>
/// Calculates the kwh for one day only
/// </summary>
/// <param name="measures">The minutewise measures of one day and one inverter</param>
/// <returns>It returns a SortedList where the key is a full hour DateTime and the value
/// the kwh result</returns>
public static SortedList<DateTime, MeasureKwH> GetKwhHourlyForOneDay(List<IMeasure> measures)
{
//result variable
SortedList<DateTime, MeasureKwH> result = new SortedList<DateTime, MeasureKwH>();
//break if we don't have any data
if (measures.Count <= 1)
return result;
//Convert to SortedList
var sortedMeasures = Utils.ConvertToSortedList(measures);
//Get private and public inverterID
int privateInverterID = measures.First().PrivateInverterId;
int publicInverterId = measures.First().PublicInverterId;
var date = sortedMeasures.Values[0].DateTime;
var currentDay = new DateTime(date.Year, date.Month, date.Day);
// calculation vars
double currentHourSum = 0;
int measureCounter = 0;
//Get first and last test hour
DateTime firstMeasureCurrentDayTime = FirstMeasureTimeOfDay(currentDay, sortedMeasures.Keys);
DateTime lastMeasureCurrentDayTime = LastMeasureTimeOfDay(currentDay, sortedMeasures.Keys);
//temp variable
DateTime lastMeasureOfHourTimePoint = new DateTime();
var isHourCompleted = true;
double incompletenesFactor = 1;
foreach (var currentMeasure in sortedMeasures.Values)
{
//add to current hour total and increase measure counter
currentHourSum += currentMeasure.Value;
measureCounter++;
//if hour was completed with the last iteration
if (isHourCompleted)
{
//set latest measure dateTime current hour
lastMeasureOfHourTimePoint = GetLastMeasureTimeOfHour(sortedMeasures.Keys, currentMeasure.DateTime);
isHourCompleted = false;
}
// if this is the last hour of the current day
if (currentMeasure.DateTime == lastMeasureOfHourTimePoint) //last measure of hour
{
//evaluate the incompletenes factor and then build the kwh result
if (AreInSameHour(currentMeasure, firstMeasureCurrentDayTime))
{
// if this measure lies in the first of hour the current day
int firstMinuteFirstMeasureHour = firstMeasureCurrentDayTime.Minute;
incompletenesFactor = ((60 - firstMinuteFirstMeasureHour) / 60.0);
}
else if (AreInSameHour(currentMeasure, lastMeasureCurrentDayTime))
{
// if this measure lies in the last hour of the current day
int lastMinute = currentMeasure.DateTime.Minute;
incompletenesFactor = ((lastMinute + 1) / 60.0);
}
//calculate kwh and add to result
MeasureKwH hourKwh = new MeasureKwH();
hourKwh.DateTime = ExtractHourDateTime(currentMeasure);
hourKwh.Value = (currentHourSum / measureCounter) * incompletenesFactor / 1000;// divided through 1000 because of "k"wh
hourKwh.PrivateInverterId = privateInverterID;
hourKwh.PublicInverterId = publicInverterId;
hourKwh.TimeMode = Enums.E_TimeMode.hour;
result.Add(hourKwh.DateTime, hourKwh);
//reset calculation variables for the next hour to calculate
currentHourSum = 0; measureCounter = 0; incompletenesFactor = 1;
isHourCompleted = true;
}
}
return result;
}
internal static SortedList<DateTime, MeasureKwH> FillHourGaps(SortedList<DateTime, MeasureKwH> hourlyKwH
, int startHour, int endHour)
{
//break if we have no data
if (hourlyKwH.Count == 0)
{
return hourlyKwH;
}
int privateInverterID = hourlyKwH.Values.First().PrivateInverterId;
int publicInverterId = hourlyKwH.Values.First().PublicInverterId;
DateTime firstMeasureDate = hourlyKwH.First().Value.DateTime;
DateTime lastMeasureDate = hourlyKwH.Last().Value.DateTime;
//build startDate and endDate we need
DateTime startDate = new DateTime(firstMeasureDate.Year
, firstMeasureDate.Month
, firstMeasureDate.Day
, startHour, 0, 0);
DateTime endDate = new DateTime(lastMeasureDate.Year
, lastMeasureDate.Month
, lastMeasureDate.Day
, endHour, 0, 0);
//counter
var countDate = startDate;
while (countDate <= endDate)
{
if (!hourlyKwH.ContainsKey(countDate))
{
MeasureKwH dummyMeasure = new MeasureKwH();
dummyMeasure.DateTime = countDate;
dummyMeasure.Value = 0;
dummyMeasure.PrivateInverterId = privateInverterID;
dummyMeasure.PublicInverterId = publicInverterId;
hourlyKwH.Add(countDate, dummyMeasure);
}
//increase hour until endHour, then jump to the next day
countDate = countDate.AddHours(1);
if (countDate.Hour > endHour)
{
countDate = countDate.AddHours(24 - countDate.Hour + startHour);
}
}
return hourlyKwH;
}