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 = "Отчет сформирован";
}