public Dictionary <Tuple <long, DateTime>, DERMSCommon.SCADACommon.MonthItem> ReadFromDayTable(string connectionString) { DERMSCommon.SCADACommon.MonthItem itemMonth = null; Dictionary <Tuple <long, DateTime>, DERMSCommon.SCADACommon.MonthItem> monthItems = new Dictionary <Tuple <long, DateTime>, DERMSCommon.SCADACommon.MonthItem>(); Tuple <long, DateTime> key = null; Tuple <long, DateTime> keyM = null; Dictionary <Tuple <long, DateTime>, DERMSCommon.SCADACommon.DayItem> dayItemsData = new Dictionary <Tuple <long, DateTime>, DERMSCommon.SCADACommon.DayItem>(); using (System.Data.SqlClient.SqlConnection _con = new System.Data.SqlClient.SqlConnection(connectionString)) { using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("SELECT Timestamp, Gid, E, PMax, PMin, PAvg FROM dbo.Day", _con)) { _con.Open(); using (System.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader()) { // Check is the reader has any rows at all before starting to read. if (reader.HasRows) { // Read advances to the next row. while (reader.Read()) { DERMSCommon.SCADACommon.DayItem c = new DERMSCommon.SCADACommon.DayItem(); // To avoid unexpected bugs access columns by name. try { c.E = reader.GetDouble(reader.GetOrdinal("E")); c.PAvg = reader.GetDouble(reader.GetOrdinal("PAvg")); c.PMax = reader.GetDouble(reader.GetOrdinal("PMax")); c.PMin = reader.GetDouble(reader.GetOrdinal("PMin")); c.Timestamp = reader.GetDateTime(reader.GetOrdinal("Timestamp")); c.Gid = reader.GetInt64(reader.GetOrdinal("Gid")); //c.P = reader.GetDouble(reader.GetOrdinal("P")); key = new Tuple <long, DateTime>(c.Gid, c.Timestamp); dayItemsData.Add(key, c); } catch (Exception e) { } } } } _con.Close(); } } foreach (var d in dayItemsData) { //itemDay = new DERMSCommon.SCADACommon.DayItem(d.Key.Item1, d.Key.Item2.Date.AddHours(d.Key.Item2.Hour), MinProductionPerHour(d.Key.Item2.Hour, d.Key.Item2.DayOfYear, collectItemsData), MaxProductionPerHour(d.Key.Item2.Hour, d.Key.Item2.DayOfYear, collectItemsData), AvgProductionPerHour(d.Key.Item2.Hour, d.Key.Item2.DayOfYear, collectItemsData), 0, 0); itemMonth = new DERMSCommon.SCADACommon.MonthItem(d.Key.Item1, d.Key.Item2.Date, MinProductionPerDay(d.Key.Item2.DayOfYear, d.Key.Item2.Month, dayItemsData, d.Key.Item1), MaxProductionPerDay(d.Key.Item2.DayOfYear, d.Key.Item2.Month, dayItemsData, d.Key.Item1), AvgProductionPerDay(d.Key.Item2.DayOfYear, d.Key.Item2.Month, dayItemsData, d.Key.Item1), 0, d.Value.P); keyM = new Tuple <long, DateTime>(itemMonth.Gid, itemMonth.Timestamp); if (!monthItems.ContainsKey(keyM)) { monthItems.Add(keyM, itemMonth); } } return(monthItems); }
public Dictionary <Tuple <long, DateTime>, DERMSCommon.SCADACommon.YearItem> ReadFromMonthTable(string connectionString) { DERMSCommon.SCADACommon.YearItem itemYear = null; Dictionary <Tuple <long, DateTime>, DERMSCommon.SCADACommon.YearItem> yearItems = new Dictionary <Tuple <long, DateTime>, DERMSCommon.SCADACommon.YearItem>(); Tuple <long, DateTime> key = null; Tuple <long, DateTime> keyY = null; Dictionary <Tuple <long, DateTime>, DERMSCommon.SCADACommon.MonthItem> monthItemsData = new Dictionary <Tuple <long, DateTime>, DERMSCommon.SCADACommon.MonthItem>(); using (System.Data.SqlClient.SqlConnection _con = new System.Data.SqlClient.SqlConnection(connectionString)) { using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("SELECT Timestamp, Gid, E, PMax, PMin, PAvg FROM dbo.Month", _con)) { _con.Open(); using (System.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader()) { // Check is the reader has any rows at all before starting to read. if (reader.HasRows) { // Read advances to the next row. while (reader.Read()) { DERMSCommon.SCADACommon.MonthItem c = new DERMSCommon.SCADACommon.MonthItem(); // To avoid unexpected bugs access columns by name. try { c.E = reader.GetDouble(reader.GetOrdinal("E")); c.PAvg = reader.GetDouble(reader.GetOrdinal("PAvg")); c.PMax = reader.GetDouble(reader.GetOrdinal("PMax")); c.PMin = reader.GetDouble(reader.GetOrdinal("PMin")); c.Timestamp = reader.GetDateTime(reader.GetOrdinal("Timestamp")); c.Gid = reader.GetInt64(reader.GetOrdinal("Gid")); //c.P = reader.GetDouble(reader.GetOrdinal("P")); key = new Tuple <long, DateTime>(c.Gid, c.Timestamp); monthItemsData.Add(key, c); } catch (Exception e) { } } } } _con.Close(); } } bool ok = false; foreach (var d in monthItemsData) { ok = false; //itemDay = new DERMSCommon.SCADACommon.DayItem(d.Key.Item1, d.Key.Item2.Date.AddHours(d.Key.Item2.Hour), MinProductionPerHour(d.Key.Item2.Hour, d.Key.Item2.DayOfYear, collectItemsData), MaxProductionPerHour(d.Key.Item2.Hour, d.Key.Item2.DayOfYear, collectItemsData), AvgProductionPerHour(d.Key.Item2.Hour, d.Key.Item2.DayOfYear, collectItemsData), 0, 0); itemYear = new DERMSCommon.SCADACommon.YearItem(d.Key.Item1, d.Key.Item2.Date, MinProductionPerMonth(d.Key.Item2.Month, d.Key.Item2.Year, monthItemsData, d.Key.Item1), MaxProductionPerMonth(d.Key.Item2.Month, d.Key.Item2.Year, monthItemsData, d.Key.Item1), AvgProductionPerMonth(d.Key.Item2.Month, d.Key.Item2.Year, monthItemsData, d.Key.Item1), 0, d.Value.P); keyY = new Tuple <long, DateTime>(itemYear.Gid, itemYear.Timestamp); if (yearItems.Count > 0) { foreach (var y in yearItems) { if (!(y.Key.Item2.Month == keyY.Item2.Month && y.Key.Item1 == keyY.Item1)) { ok = true; } else { ok = false; break; } } if (ok) { yearItems.Add(keyY, itemYear); } } else { yearItems.Add(keyY, itemYear); } //if (!yearItems.ContainsKey(keyY)) // yearItems.Add(keyY, itemYear); } return(yearItems); }