/// <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; }