public void readTables() { subSystemsByTables = new Dictionary <string, int>(); SqlConnection con = DBSettings.getConnection(); con.Open(); tables = new List <string>(); foreach (int ss in SignalsBySubSys.Keys) { string tabName = String.Format("data_{0}_tabs", ss); SqlCommand com = con.CreateCommand(); Status.Text = "Чтение списка таблиц с данными " + ss; com.CommandText = "Select * from " + tabName + " order by time_beg"; SqlDataReader reader = com.ExecuteReader(); while (reader.Read()) { DateTime ds = EPADB.GetDate(reader.GetInt32(1)); DateTime de = EPADB.GetDate(reader.GetInt32(2)); string nm = reader.GetString(0); if (nm.ToLower().Contains("tmp") || (de >= DateStart && ds <= DateEnd) /*(ds < DateStart && !(de < DateStart)) || (de > DateEnd && !(ds > DateEnd)) || (ds >= DateStart && de <= DateEnd)*/) { string stTab = reader.GetString(0); tables.Add(stTab); subSystemsByTables.Add(stTab, ss); } } reader.Close(); } con.Close(); }
public void linkSignals() { ASUTechDict = new Dictionary <string, string>(); TechASUDict = new Dictionary <string, string>(); SqlConnection con = DBSettings.getConnection(); con.Open(); SqlCommand com = con.CreateCommand(); Status.Text = "Чтение связанных сигналов"; com.CommandText = "Select * from link_signals where kks_id_param is not null and kks_id_signal is not null"; SqlDataReader reader = com.ExecuteReader(); while (reader.Read()) { string kksAsu = reader.GetString(2); string kksTech = reader.GetString(1); try { ASUTechDict.Add(kksAsu, kksTech); } catch { ASUTechDict[kksAsu] += ";" + kksTech; } try { TechASUDict.Add(kksTech, kksAsu); } catch { TechASUDict[kksTech] += ";" + kksAsu; } } reader.Close(); con.Close(); }
public void readASU() { SqlConnection con = DBSettings.getConnection(); con.Open(); List <SignalInfo> asufull = new List <SignalInfo>(); SqlCommand com = con.CreateCommand(); Status.Text = "Чтение АСУ сигналов из БД"; com.CommandText = "Select kks_id_signal, full_name,short_name,sub_sys,value_step,kks_id_dev,id_level from asu_signals_full"; SqlDataReader reader = com.ExecuteReader(); while (reader.Read()) { SignalInfo si = new SignalInfo(); si.KKS = reader.GetString(0); si.FullName = reader.GetString(1); si.ShortName = reader.GetString(2); si.FullNameUp = si.FullName.ToUpper(); si.ShortNameUp = si.ShortName.ToUpper(); si.subSys = reader.GetInt32(3); si.valueStep = reader.GetString(4); si.kksDev = reader.GetString(5); si.idLevel = reader.GetInt32(6); try { si.numSign = NumSignals[si.KKS]; } catch { } asufull.Add(si); } reader.Close(); NumSignals.Clear(); SubSysTables.Clear(); Status.Text = "Чтение АСУ дерева из БД"; Dictionary <string, string> asu_tree = new Dictionary <string, string>(); com.CommandText = "Select * from asu_tree"; reader = com.ExecuteReader(); while (reader.Read()) { string kks = reader.GetString(3); string name = reader.GetString(2); asu_tree.Add(kks, name); } reader.Close(); con.Close(); SignalInfo root = new SignalInfo(); root.KKS = asu_tree.Keys.ToArray()[0]; root.FullName = asu_tree.Values.ToArray()[0]; createTree(root, asu_tree); Status.Text = "Формирование АСУ дерева"; fillTree(root, asufull); Status.Text = "Формирование АСУ групп"; fillSubTree(root); ASURoot = root.Children[0]; }
public void readTablesStates(DateTime date) { SqlConnection con = DBSettings.getConnection(); con.Open(); tablesState = new Dictionary <int, List <string> >(); foreach (int ss in SignalsBySubSys.Keys) { string tabName = String.Format("data_{0}_tabs", ss); SqlCommand com = con.CreateCommand(); Status.Text = "Чтение списка таблиц с данными " + ss; com.CommandText = "Select * from " + tabName; SqlDataReader reader = com.ExecuteReader(); while (reader.Read()) { DateTime ds = EPADB.GetDate(reader.GetInt32(1)); DateTime de = EPADB.GetDate(reader.GetInt32(2)); string nm = reader.GetString(0); if (nm.ToLower().Contains("tmp") || (date >= ds && date <= de)) { string stTab = reader.GetString(0); if (!tablesState.ContainsKey(ss)) { tablesState.Add(ss, new List <string>()); } tablesState[ss].Add(stTab); } } reader.Close(); } con.Close(); }
public void readTables() { List <int> DiscrSubSystems = new List <int>(); subSystemsByTables = new Dictionary <string, int>(); foreach (KeyValuePair <int, string> de in epa.SubSystems) { if (de.Value.Contains("Дискр")) { DiscrSubSystems.Add(de.Key); } if (epa.SelectedDiscrSignals.Count > 0) { if (!SignalsBySubSys.ContainsKey(de.Key)) { DiscrSubSystems.Remove(de.Key); } } } SqlConnection con = DBSettings.getConnection(); con.Open(); tables = new List <string>(); foreach (int ss in DiscrSubSystems) { string tabName = String.Format("data_{0}_tabs", ss); SqlCommand com = con.CreateCommand(); Status.Text = "Чтение списка таблиц с данными " + ss; com.CommandText = "Select * from " + tabName; SqlDataReader reader = com.ExecuteReader(); while (reader.Read()) { DateTime ds = EPADB.GetDate(reader.GetInt32(1)); DateTime de = EPADB.GetDate(reader.GetInt32(2)); string nm = reader.GetString(0); if (nm.ToLower().Contains("tmp") || (de >= DateStart && ds <= DateEnd) /*(ds < DateStart && !(de < DateStart)) || (de > DateEnd && !(ds > DateEnd)) || (ds >= DateStart && de <= DateEnd)*/) { string evTab = reader.GetString(6); tables.Add(evTab); subSystemsByTables.Add(evTab, ss); /*string stTab = reader.GetString(0); * tables.Add(stTab);*/ } } reader.Close(); } con.Close(); }
public void readSubSystems() { SubSystems = new Dictionary <int, string>(); SubSysTables = new Dictionary <int, string>(); SqlConnection con = DBSettings.getConnection(); con.Open(); SqlCommand com = con.CreateCommand(); Status.Text = "Чтение подсистем"; com.CommandText = "Select * from Subsys_collection"; SqlDataReader reader = com.ExecuteReader(); while (reader.Read()) { int subSys = reader.GetInt32(0); string name = reader.GetString(3); string tab = reader.GetString(2); try { SubSystems.Add(subSys, name); SubSysTables.Add(subSys, tab); } catch { } } reader.Close(); NumSignals = new Dictionary <string, int>(); foreach (string tabName in SubSysTables.Values) { Status.Text = "Чтение подсистем " + tabName; try { com.CommandText = String.Format("Select * from {0}", tabName); reader = com.ExecuteReader(); while (reader.Read()) { int num = reader.GetInt32(1); string kks = reader.GetString(0); NumSignals.Add(kks, num); } } finally { reader.Close(); } } con.Close(); }
public void readTech() { SqlConnection con = DBSettings.getConnection(); con.Open(); List <SignalInfo> techfull = new List <SignalInfo>(); SqlCommand com = con.CreateCommand(); com.CommandText = "Select * from tech_parameters_full"; Status.Text = "Чтение Тех сигналов из БД"; SqlDataReader reader = com.ExecuteReader(); while (reader.Read()) { SignalInfo si = new SignalInfo(); si.KKS = reader.GetString(0); si.FullName = reader.GetString(1); si.ShortName = reader.GetString(2); si.FullNameUp = si.FullName.ToUpper(); si.ShortNameUp = si.ShortName.ToUpper(); techfull.Add(si); } reader.Close(); Status.Text = "Чтение Тех дерева из БД"; Dictionary <string, string> tech_tree = new Dictionary <string, string>(); com.CommandText = "Select * from tech_tree"; reader = com.ExecuteReader(); while (reader.Read()) { string kks = reader.GetString(3); string name = reader.GetString(2); tech_tree.Add(kks, name); } reader.Close(); con.Close(); SignalInfo root = new SignalInfo(); root.KKS = tech_tree.Keys.ToArray()[0]; root.FullName = tech_tree.Values.ToArray()[0]; Status.Text = "Формирование Тех дерева"; createTree(root, tech_tree); fillTree(root, techfull); TechRoot = root.Children[0]; }
public void readkksDev() { SubSystems = new Dictionary <int, string>(); SqlConnection con = DBSettings.getConnection(); con.Open(); SqlCommand com = con.CreateCommand(); Status.Text = "Чтение групп сигналов"; com.CommandText = "Select * from ASU_signal_group"; SqlDataReader reader = com.ExecuteReader(); Signals = new Dictionary <string, List <SignalGroup> >(); while (reader.Read()) { SignalGroup gr = new SignalGroup(); gr.KKSDev = reader.GetString(0); gr.name = reader.GetString(3); try { gr.level = reader.GetInt32(1); } catch { gr.level = -1; } try { gr.parent = reader.GetInt32(2); } catch { gr.parent = -1; } if (!Signals.ContainsKey(gr.KKSDev)) { Signals.Add(gr.KKSDev, new List <SignalGroup>()); } Signals[gr.KKSDev].Add(gr); } reader.Close(); con.Close(); }
public void readAlgoritms() { DictAlgo = new Dictionary <string, bool>(); SqlConnection con = DBSettings.getConnection(); con.Open(); SqlCommand com = con.CreateCommand(); Status.Text = "Чтение алгоритмов"; com.CommandText = "Select * from algorithm_collection_binary"; SqlDataReader reader = com.ExecuteReader(); while (reader.Read()) { string kks = reader.GetString(1); try { DictAlgo.Add(kks, true); } catch { } } reader.Close(); con.Close(); }
public void readData(string fn, GraphPane graph) { SqlConnection con = DBSettings.getConnection(); con.Open(); Int64 timeStart = EPADB.GetIntDate(DateStart); Int64 timeEnd = EPADB.GetIntDate(DateEnd); Dictionary <Int64, Dictionary <string, double> > Data = new Dictionary <long, Dictionary <string, double> >(); Int64 time = timeStart; while (time <= timeEnd) { Data.Add(time, new Dictionary <string, double>()); foreach (SignalInfo si in epa.SelectedAnalogSignals) { Data[time].Add(si.KKS, double.NaN); } time += Step; } List <string> kksQList = new List <string>(); List <int> numSignQList = new List <int>(); List <int> timesQList = new List <int>(); Dictionary <int, int> timesDict = new Dictionary <int, int>(); List <int> timesList = new List <int>(); foreach (int ss in SignalsBySubSys.Keys) { foreach (string table in tables) { if (subSystemsByTables[table] != ss) { continue; } Status.Text = "Обработка таблицы " + table; SqlCommand com = con.CreateCommand(); com.CommandText = String.Format("Select * from {0} where time_page>={1} and time_page<={2}", table.Replace("state", "time"), timeStart, timeEnd); List <Int32> times = new List <int>(); try { Status.Text += "---|"; SqlDataReader reader = com.ExecuteReader(); while (reader.Read()) { try { int tm = reader.GetInt32(0); times.Add(tm); } catch { } } times.Sort(); reader.Close(); foreach (SignalInfo si in SignalsBySubSys[ss]) { kksQList.Add("'" + si.KKS + "'"); numSignQList.Add(si.numSign); if (kksQList.Count() <= 10 && si != SignalsBySubSys[ss].Last()) { continue; } string kksQ = String.Join(",", kksQList); string numQ = String.Join(",", numSignQList); kksQList.Clear(); timesDict.Clear(); numSignQList.Clear(); timesQList.Clear(); foreach (int t in Data.Keys) { if (t >= times.First() && t <= times.Last()) { int valT = times.First(tempT => { return(tempT >= t); }); if (valT - t < Step) { timesQList.Add(valT); timesDict.Add(valT, t); } } if (timesQList.Count < 100 && t != Data.Keys.Last()) { continue; } string timesQ = String.Join(",", timesQList); timesQList.Clear(); try { com = con.CreateCommand(); //com.CommandText = String.Format("Select kks_id_signal,time_page,data from {0} where time_page in ({2}) and kks_id_signal in ({1})", table, kksQ, timesQ); //com.CommandText = String.Format("Select kks_id_signal,time_page,data from {0} where time_page={2} and kks_id_signal = '{1}'", table, kks, valT); if (!epa.UseNumSignals) { com.CommandText = String.Format("Select kks_id_signal,time_page,data from {0} where time_page in ({2}) and kks_id_signal in ({1})", table, kksQ, timesQ); } else { com.CommandText = String.Format("Select kks_id_signal,time_page,data from {0} where time_page in ({2}) and num_sign in ({1})", table, numQ, timesQ); } Status.Text += "---|"; reader = com.ExecuteReader(); while (reader.Read()) { try { int timeRes = reader.GetInt32(1); string kksAsu = reader.GetString(0); double val = reader.GetFloat(2); long resultTime = timesDict[timeRes]; Data[resultTime][kksAsu] = val; } catch (Exception e) { Logger.Info(e.ToString()); } } reader.Close(); } catch (Exception e) { Logger.Info(e.ToString()); } } } } catch (Exception e) { Logger.Info(e.ToString()); } } } con.Close(); Status.Text = "Чтение завершено"; List <string> thAsuList = new List <string>(); List <string> thTechList = new List <string>(); foreach (SignalInfo si in epa.SelectedAnalogSignals) { thAsuList.Add(String.Format("<th>{0}</th>", si.ShortName)); try { string kksTech = epa.ASUTechDict[si.KKS]; SignalInfo tech = epa.FindSignal(epa.TechRoot, kksTech, null); thTechList.Add(String.Format("<th>{0}</th>", tech.ShortName)); } catch { thTechList.Add("<th>-</th>"); } } OutputData.writeToOutput(fn, String.Format("<table border='1'><tr><th rowspan='2'>Дата</th>{0}</tr><tr>{1}</tr>", string.Join(" ", thAsuList), string.Join(" ", thTechList))); foreach (int tm in Data.Keys) { OutputData.writeToOutput(fn, String.Format("<tr><th>{0}</th><td>{1}</td></tr>", EPADB.GetDate(tm), String.Join("</td><td>", Data[tm].Values))); } OutputData.writeToOutput(fn, "</table>"); graph.CurveList.Clear(); graph.XAxis.Scale.Min = Data.Keys.First(); graph.XAxis.Scale.Max = Data.Keys.Last(); graph.XAxis.Scale.MinAuto = false; graph.XAxis.Scale.MaxAuto = false; graph.XAxis.Title.IsVisible = false; graph.YAxis.IsVisible = false; graph.YAxis.Title.IsVisible = false; graph.Legend.FontSpec.Size = 6; graph.Legend.Location.X = 0; graph.Legend.Location.Y = 0; graph.Title.IsVisible = false; graph.YAxis.Scale.FontSpec.Size = 6; graph.YAxis.IsVisible = false; graph.XAxis.Scale.FontSpec.Size = 6; int index = 0; foreach (SignalInfo si in epa.SelectedAnalogSignals) { try { string name = si.ShortName; int axInd = graph.AddYAxis(""); graph.YAxisList[axInd].Color = Colors[index % 8]; graph.YAxisList[axInd].Scale.FontSpec.Size = 6; graph.YAxisList[axInd].Scale.FontSpec.FontColor = Colors[index % 8]; try { string kksTech = epa.ASUTechDict[si.KKS]; SignalInfo tech = epa.FindSignal(epa.TechRoot, kksTech, null); name = name + " (" + tech.ShortName + ")"; } catch { } PointPairList list = new PointPairList(); foreach (int tm in Data.Keys) { list.Add(new PointPair(tm, Data[tm][si.KKS])); } graph.AddCurve(name, list, Colors[index % 8], SymbolType.None); graph.CurveList[index].YAxisIndex = axInd; } catch (Exception e) { Logger.Info(e.ToString()); } graph.AxisChange(); index++; } }
public void readState(DateTime date, DataGridView grid) { SqlConnection con = DBSettings.getConnection(); con.Open(); Int64 time = EPADB.GetIntDate(date); grid.Rows.Clear(); Dictionary <string, int> gridRows = new Dictionary <string, int>(); Dictionary <string, SignalInfo> signals = new Dictionary <string, SignalInfo>(); foreach (SignalInfo siAsu in epa.SelectedDiscrSignals) { try { int i = grid.Rows.Add(); SignalInfo siTech = null; try { string kksTech = epa.ASUTechDict[siAsu.KKS]; siTech = epa.FindSignal(epa.TechRoot, kksTech, null); } catch { } grid.Rows[i].Cells[0].Value = siAsu.ShortName; grid.Rows[i].Cells[1].Value = siTech != null ? siTech.ShortName : "-"; grid.Rows[i].Cells[2].Value = "-"; grid.Rows[i].Cells[3].Value = "-"; grid.Rows[i].Cells[4].Value = "-"; gridRows.Add(siAsu.KKS, i); signals.Add(siAsu.KKS, siAsu); } catch { } } foreach (int subSys in tablesState.Keys) { Status.Text = "Чтение данных подсистемы " + subSys; foreach (string table in tablesState[subSys]) { try { SqlCommand com = con.CreateCommand(); com.CommandText = String.Format("Select top 1 time_page from {0} where time_page>={1}", table.Replace("state", "time"), time); Object timeObj = com.ExecuteScalar(); if ((Int32)timeObj <= 0) { continue; } List <string> kksQList = new List <string>(); List <int> numsQList = new List <int>(); foreach (SignalInfo si in SignalsBySubSys[subSys]) { try { kksQList.Add("'" + si.KKS + "'"); numsQList.Add(si.numSign); if (kksQList.Count < 10 && si != SignalsBySubSys[subSys].Last()) { continue; } string kksQ = string.Join(",", kksQList); string numsQ = string.Join(",", numsQList); kksQList.Clear(); numsQList.Clear(); Status.Text = Status.Text + "...|"; if (!epa.UseNumSignals) { com.CommandText = String.Format("Select time_page,time,kks_id_signal,mcs,data,bsrc from {0} where time_page={1} and kks_id_signal in ({2})", table, timeObj, kksQ); } else { com.CommandText = String.Format("Select time_page,time,kks_id_signal,mcs,data,bsrc from {0} where time_page={1} and num_sign in ({2})", table, timeObj, numsQ); } SqlDataReader reader = com.ExecuteReader(); while (reader.Read()) { DateTime dt = EPADB.GetDate(reader.GetInt32(0)); DateTime dt_prev = EPADB.GetDate(reader.GetInt32(1)); string kksAsu = reader.GetString(2); int state = Int32.Parse(reader[4].ToString()); int src = Int32.Parse(reader[5].ToString()); int mcs = Int32.Parse(reader[3].ToString()); dt = dt.AddMilliseconds(mcs / 1000.0); int index = gridRows[kksAsu]; grid.Rows[index].Cells[2].Value = dt_prev.ToString("dd.MM.yyyy HH:mm:ss,fff"); grid.Rows[index].Cells[3].Value = EPADB.getSignalState(state, signals[kksAsu]); grid.Rows[index].Cells[4].Value = EPADB.getSignalSrc(src); } reader.Close(); } catch (Exception e) { Logger.Info(e.ToString()); } } } catch (Exception e) { Logger.Info(e.ToString()); } } } Status.Text = "Готово "; }
public void readData(string fn) { OutputData.writeToOutput(fn, "<table border='1'><tr><th>Дата</th><th>kks_asu</th><th>kks_tech</th><th>name_asu</th><th>name_tech</th><th>state</th><th>source</th><th>subsys</th></tr>"); SqlConnection con = DBSettings.getConnection(); con.Open(); Int64 timeStart = EPADB.GetIntDate(DateStart); Int64 timeEnd = EPADB.GetIntDate(DateEnd); Dictionary <int, string> colors = new Dictionary <int, string>(); colors.Add(0, "white"); colors.Add(1, "PaleGreen"); colors.Add(2, "LightSkyBlue"); colors.Add(3, "LightCoral"); SortedList <DateTime, string> outputData = new SortedList <DateTime, string>(); foreach (string tab in tables) { SqlCommand com = con.CreateCommand(); Status.Text = "Чтение данных из таблицы " + tab; int index = 0; try { com.CommandText = String.Format("Select time,mcs,data,kks_id_signal,bsrc from {0} where time>={1} and time<={2}", tab, timeStart, timeEnd); if (epa.SelectedDiscrSignals.Count > 0) { int ss = subSystemsByTables[tab]; List <string> kksList = new List <string>(); List <int> numsList = new List <int>(); foreach (SignalInfo si in SignalsBySubSys[ss]) { kksList.Add("'" + si.KKS + "'"); numsList.Add(si.numSign); } if (!epa.UseNumSignals) { com.CommandText += String.Format(" and kks_id_signal in ({0})", String.Join(",", kksList)); } else { com.CommandText += String.Format(" and num_sign in ({0})", String.Join(",", numsList)); } } SqlDataReader reader = com.ExecuteReader(); while (reader.Read()) { try { index++; if (index % 1000 == 0) { Status.Text += "...|"; } DateTime dt = EPADB.GetDate(reader.GetInt32(0)); string kks = reader.GetString(3); int state = Int32.Parse(reader[2].ToString()); int src = Int32.Parse(reader[4].ToString()); int mcs = Int32.Parse(reader[1].ToString()); dt = dt.AddMilliseconds(mcs / 1000.0); SignalInfo siAsu = epa.FindSignal(epa.ASURoot, kks, null); SignalInfo siTech = null; try { string kksTech = epa.ASUTechDict[kks]; siTech = epa.FindSignal(epa.TechRoot, kksTech, null); } catch { } string color = "white"; try { color = colors[state]; } catch { } string outStr = String.Format("<tr bgColor='{0}'><th>{1}</th><td>{2}</td><td>{3}</td><td>{4}</td><td>{5}</td><td>{6}</td><td>{7}</td><td>{8}</td></tr>", color, dt.ToString("dd.MM.yyyy HH:mm:ss,fff"), kks, siTech == null ? "-" : siTech.KKS, siAsu.ShortName, siTech == null ? "-" : siTech.ShortName, EPADB.getSignalState(state, siAsu), EPADB.getSignalSrc(src), siAsu.subSys); if (!outputData.ContainsKey(dt)) { outputData.Add(dt, ""); } outputData[dt] += "\r\n" + outStr; } catch (Exception e) { Logger.Info(e.ToString()); } } reader.Close(); } catch { } } con.Close(); foreach (string str in outputData.Values) { OutputData.writeToOutput(fn, str); } OutputData.writeToOutput(fn, "</table>"); Status.Text = "Отчет сформирован"; }