/// <summary>
/// returns minutewise measures prepared for the flot line chart
/// </summary>
/// <param name="plantId">solar plant id</param>
/// <param name="date">The requested day</param>
/// <returns>multiple rows of type 'long'</returns>
public FlotLineChartTable GetCumulatedMinuteWiseWattageChartData(int plantId, DateTime date)
{
var startDate = Utils.CropHourMinuteSecond(date);
var endDate = startDate.AddDays(1);
var utcOffsetMs = TimeZoneInfo.FindSystemTimeZoneById("W. Europe Standard Time").GetUtcOffset(Utils.GetGermanNow()).TotalMilliseconds;
string text = @"
SELECT (UNIX_TIMESTAMP(m.DateTime)*1000) as timeValue , ROUND(SUM(m.OutputWattage))
FROM inverter i
INNER JOIN measure m
ON i.InverterId = m.InverterId
WHERE i.PlantId = @plantId
AND DateTime BETWEEN @startDate AND @endDate
GROUP BY timeValue
ORDER BY timeValue ASC;";
var sqlCom = base.GetReadCommand(text);
sqlCom.Parameters.AddWithValue("@plantId", plantId);
sqlCom.Parameters.AddWithValue("@startDate", startDate);
sqlCom.Parameters.AddWithValue("@endDate", endDate);
var result = new FlotLineChartTable();
using (var rdr = sqlCom.ExecuteReader())
{
while (rdr.Read())
{
result.AddValue(rdr.GetDouble(0) + utcOffsetMs, rdr.GetDouble(1));
}
}
return result;
}
public List<FlotLineChartTable> GetInverterWiseMinuteWiseWattageChartData(int plantId, DateTime date)
{
var utcOffsetMs = Utils.GetGermanTimeZone().GetUtcOffset(Utils.GetGermanNow()).TotalMilliseconds;
var startDate = Utils.CropHourMinuteSecond(date);
var endDate = startDate.AddDays(1);
// sorting the measures is important, first publicInverterId then DateTime
string text = @"
SELECT i.PublicInverterId, (UNIX_TIMESTAMP(m.DateTime)*1000) as timeValue , ROUND(m.OutputWattage)
FROM inverter i
INNER JOIN measure m
ON i.InverterId = m.InverterId
WHERE i.PlantId = @plantId
AND DateTime BETWEEN @startDate AND @endDate
ORDER BY i.PublicInverterId ASC, timeValue ASC;";
var sqlCom = base.GetReadCommand(text);
sqlCom.Parameters.AddWithValue("@plantId", plantId);
sqlCom.Parameters.AddWithValue("@startDate", startDate);
sqlCom.Parameters.AddWithValue("@endDate", endDate);
List<FlotLineChartTable> result = new List<FlotLineChartTable>();
FlotLineChartTable currentTable = new FlotLineChartTable();
bool isFirst = true;
int publicInverterId = -1;
int currentInverterId;
using (var rdr = sqlCom.ExecuteReader())
{
while (rdr.Read())
{
//get the current inverter Id
currentInverterId = rdr.GetInt32(0);
if (isFirst)
{
//if this is the first read, initialize the public inverterId
publicInverterId = currentInverterId;
isFirst = false;
currentTable.SetSeriesNameForInverter(currentInverterId);
}
//read all measures one inverter after another
if (publicInverterId != currentInverterId)
{
// we have completed one wattage series and can build the next one
result.Add(currentTable);
currentTable = new FlotLineChartTable();
publicInverterId = currentInverterId;
currentTable.SetSeriesNameForInverter(currentInverterId);
}
// read the current row
currentTable.AddValue(rdr.GetDouble(1) + utcOffsetMs, rdr.GetDouble(2));
}
// add the last result table
result.Add(currentTable);
}
return result;
}