static int limitForPointView = 10000; //количество точек для отображения на Zedgraph
#endregion Fields
#region Methods
public static List<List<string>> GetAllExperimentGroups(ADatabase db)
{
try
{
List<List<string>> result = new List<List<string>>();
string sql = "SELECT experimentgroups.experimentgroupsid, experimentgroups.datestart, experimentgroups.datestop - experimentgroups.datestart,(select Count(*) from experiments where experimentgroups.experimentgroupsid = experiments.experimentgroupsid) as cn ,(select Count(*) as pointcount from parametersvalues where parametersvalues.experimentid = (select experimentid from experiments where experiments.experimentgroupsID = experimentgroups.experimentgroupsID FETCH FIRST ROW ONLY) and parametersvalues.parametersid = 1), experimentgroups.powerdelay, experimentgroups.commdelay, experimentgroups.description FROM ExperimentGroups order by experimentgroups.experimentgroupsid";
NpgsqlCommand readCommand = new NpgsqlCommand(sql, db.Connection);
NpgsqlDataReader myreader = readCommand.ExecuteReader();
List<string> line;
while (myreader.Read())
{
line = new List<string>();
// load the combobox with the names of the people inside.
// myreader[0] reads from the 1st Column
for (int i = 0; i < myreader.FieldCount; i++)
{
line.Add(myreader[i].ToString());
}
result.Add(line);
}
myreader.Close(); // we are done with the reader
return result;
}
catch (Exception ex)
{
FileWorker.WriteEventFile(DateTime.Now, "DatabaseWorker", "GetAllExperimentGroups", ex.Message);
return null;
}
}
public MainWindow()
{
try
{
InitializeComponent();
db = new ADatabase("database");
if (db.Connect())
{
if (!ADatabase.MainDatabaseStructureIsOK(db.Connection))
{
db.CreateDBStructure();
db.WriteParameters();
}
ALayout.SetStatisticParametersListbox(allParameters_listbox, parametersToShow_listbox, ADatabaseWorker.GetAllParameters(db));
ALayout.SetStatisticZedgraphStyle(statistic_graph);
ALayout.SetReflectogramZedgraphStyle(refl_graph);
ScanTasks();
}
else
{
MessageBox.Show("Не удалось соединиться с базой данных");
this.Close();
}
}
catch(Exception ex)
{
FileWorker.WriteEventFile(DateTime.Now, "MainWindow", "Ini", ex.Message);
}
}
public AThread(ABaseblock _device, int _experimentGroup, ADatabase _db, int _powerDelay, int _commDelay, bool isStartWithInitialize = false)
{
try
{
// TODO: Complete member initialization
device = _device;
powerDelay = _powerDelay;
commDelay = _commDelay;
experimentGroup = _experimentGroup;
db = _db;
bw.DoWork += bw_DoWork;
bw.ProgressChanged += bw_ProgressChanged;
bw.RunWorkerCompleted += bw_RunWorkerCompleted;
bw.WorkerReportsProgress = true;
bw.WorkerSupportsCancellation = true;
foreach (byte channel in device.channelsWithSensors)
{
sensorsData.Add(new Sensor());
driver = new ADriver(device.comport, powerDelay);
}
if (isStartWithInitialize)
{
Start();
}
}
catch (Exception ex)
{
FileWorker.WriteEventFile(DateTime.Now, "AThread", "AThread", ex.Message);
}
}
internal static int WriteCalibrationGroup(ADatabase db, int average, double firmware, string serial)
{
try
{
NumberFormatInfo formatInfo = new NumberFormatInfo();
formatInfo.NumberDecimalSeparator = ".";
string sql = "INSERT INTO CalibrationGroup (NumOfAverage, Datetime, SensorSerial, SensorFirmware) VALUES (" + average.ToString() + ",'" + DateTime.Now.ToString("dd.MM.yy HH:mm:ss.ff") + "', '" + serial + "', " + firmware.ToString(formatInfo) + ")";
NpgsqlCommand command = new NpgsqlCommand(sql, db.Connection);
command.ExecuteNonQuery();
sql = "SELECT currval(pg_get_serial_sequence('CalibrationGroup', 'calibrationgroupid'));";
command = new NpgsqlCommand(sql, db.Connection);
NpgsqlDataReader myreader = command.ExecuteReader();
if (myreader.Read())
{
int result = Convert.ToInt32(myreader[0].ToString());
myreader.Close();
return result;
}
else
{
myreader.Close();
return -1;
}
}
catch (Exception ex)
{
FileWorker.WriteEventFile(DateTime.Now, "ACalibrationDatabaseWorker", "WriteCalibrationGroup", ex.Message);
return -1;
}
}
internal static List<List<string>> GetAllCalibrationGroup(ADatabase db)
{
try
{
List<List<string>> result = new List<List<string>>();
List<string> line = new List<string>();
string sql = "select * from CalibrationGroup";
NpgsqlCommand command = new NpgsqlCommand(sql, db.Connection);
NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
line = new List<string>();
if (reader.FieldCount == 5)
{
line.Add(reader[0].ToString());
line.Add(reader[1].ToString());
line.Add(reader[2].ToString());
line.Add(reader[3].ToString());
line.Add(reader[4].ToString());
result.Add(line);
}
}
reader.Close();
return result;
}
catch (Exception ex)
{
FileWorker.WriteEventFile(DateTime.Now, "ACalibrationDatabaseWorker", "GetAllCalibrationGroup", ex.Message);
return null;
}
}
public static async void ExportData(ADatabase db, List<int> experiments, bool showMessagebox = true)
{
try
{
List<int> test = ADatabaseWorker.GetAllParametersIDs(db);
foreach (int experiment in experiments)
{
List<string> writeData = ADatabaseWorker.GetAllDataForExport(db, experiment, test);
if (!Directory.Exists("export"))
Directory.CreateDirectory("export");
using (StreamWriter outfile = new StreamWriter("export\\ExperimentID" + experiment.ToString() + ".csv", false, Encoding.UTF8))
{
string header = "experimentID; Дата; ";
foreach (string parameterName in ADatabase.Parameters)
{
header += parameterName + "; ";
}
await outfile.WriteAsync(header + Environment.NewLine);
foreach (string line in writeData)
await outfile.WriteAsync(line);
}
if (showMessagebox)
MessageBox.Show("Export complete!");
}
}
catch (Exception ex)
{
}
}
internal static void WriteCalibrationData(ADatabase db, int calibrationGroup, string realValue, string measureValue)
{
string sql = "INSERT INTO Calibration (RealValue , MeasureValue, CalibrationGroup) VALUES ("
+ realValue.ToString().Replace(",", ".") + "," + realValue.ToString().Replace(",", ".")
+ ", " + calibrationGroup.ToString() + ")";
NpgsqlCommand command = new NpgsqlCommand(sql, db.Connection);
command.ExecuteNonQuery();
}
internal static void WriteCalibrationData(ADatabase db, int calibrationGroup, string realValue, string measureValue)
{
try
{
string sql = "INSERT INTO Calibration (RealValue , MeasureValue, CalibrationGroup) VALUES ("
+ realValue.ToString().Replace(",", ".") + "," + measureValue.ToString().Replace(",", ".")
+ ", " + calibrationGroup.ToString() + ")";
NpgsqlCommand command = new NpgsqlCommand(sql, db.Connection);
command.ExecuteNonQuery();
}
catch (Exception ex)
{
FileWorker.WriteEventFile(DateTime.Now, "ACalibrationDatabaseWorker", "WriteCalibrationData", ex.Message);
}
}
public Calibration(bool _twoZond, Dictionary<string, Dictionary<string, byte>> _devices)
{
InitializeComponent();
db = new ADatabase("calibration");
if (db.Connect())
{
if (!ADatabase.CalibrationDatabaseStructureIsOK(db.Connection))
{
db.CreateCalibrationDBStructure();
}
twoZond = _twoZond;
devices = _devices;
List<string> devicesForCalibration = new List<string>();
foreach (KeyValuePair<string, Dictionary<string, byte>> baseblock in devices)
{
foreach (KeyValuePair<string, byte> channel in baseblock.Value)
{
devicesForCalibration.Add("ББ:" + baseblock.Key + " - CH" + channel.Value.ToString() + " Д:" + channel.Key.ToString());
}
}
channel_combobox.Items.Clear();
foreach (string record in devicesForCalibration)
{
channel_combobox.Items.Add(record);
}
channel_combobox.SelectedIndex = 0;
dataGridView.Focus();
bw = new BackgroundWorker();
bw.DoWork += bw_DoWork;
bw.ProgressChanged += bw_ProgressChanged;
bw.RunWorkerCompleted += bw_RunWorkerCompleted;
bw.WorkerReportsProgress = true;
}
else
{
MessageBox.Show("Не удалось соединиться с базой данных");
this.Close();
}
}
public SqlWindow()
{
InitializeComponent();
db = new ADatabase("database");
if (db.Connect())
{
if (!ADatabase.MainDatabaseStructureIsOK(db.Connection))
{
db.CreateDBStructure();
db.WriteParameters();
}
}
else
{
MessageBox.Show("Не удалось соединиться с базой данных");
this.Close();
}
}
/*async*/
public static void ExportData(ADatabase db, List<int> experiments, bool showMessagebox = true)
{
try
{
foreach (int experiment in experiments)
{
List<int> test = ADatabaseWorker.GetParametersForExport(db, experiment);
List<string> writeData = ADatabaseWorker.GetAllDataForExport(db, experiment, test);
if (!Directory.Exists("export"))
Directory.CreateDirectory("export");
using (StreamWriter outfile = new StreamWriter("export\\ExperimentID" + experiment.ToString() + ".csv", false, Encoding.UTF8))
{
string header = "experimentID; Серийный номер датчика; Параметр калибровки; Дата; ";
foreach (string parameterName in ADatabase.Parameters)
{
header += parameterName + "; ";
}
//await outfile.WriteAsync(header + Environment.NewLine);
outfile.Write(header + Environment.NewLine);
List<string> experimentInfo = ADatabaseWorker.GetExperimentIDInfo(db, experiment);
foreach (string line in writeData)
{
string lineToWrite = "";
if (experimentInfo.Count == 3)
lineToWrite = experimentInfo[0] + "; " + experimentInfo[1] + "; " + experimentInfo[2] + "; ";
else
lineToWrite = "null; null; null; ";
lineToWrite += line;
//await outfile.WriteAsync(lineToWrite);
outfile.Write(lineToWrite);
}
}
if (showMessagebox)
MessageBox.Show("Export complete!");
}
}
catch (Exception ex)
{
FileWorker.WriteEventFile(DateTime.Now, "Export", "ExportData", ex.Message);
}
}
internal static List<List<string>> GetAllCalibrationGroup(ADatabase db)
{
List<List<string>> result = new List<List<string>>();
List<string> line = new List<string>();
string sql = "select * from CalibrationGroup";
NpgsqlCommand command = new NpgsqlCommand(sql, db.Connection);
NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
line = new List<string>();
if(reader.FieldCount == 5)
{
line.Add(reader[0].ToString());
line.Add(reader[1].ToString());
line.Add(reader[2].ToString());
line.Add(reader[3].ToString());
line.Add(reader[4].ToString());
result.Add(line);
}
}
reader.Close();
return result;
}
public static List<int> GetExperimentIDs(ADatabase db, int experimentGroup)
{
try
{
List<int> result = new List<int>();
string sql = "SELECT ExperimentID FROM Experiments Where ExperimentGroupsID=" + experimentGroup;
NpgsqlCommand readCommand = new NpgsqlCommand(sql, db.Connection);
NpgsqlDataReader myreader = readCommand.ExecuteReader();
while (myreader.Read())
{
result.Add(Convert.ToInt32(myreader[0].ToString()));
}
myreader.Close();
return result;
}
catch (Exception ex)
{
FileWorker.WriteEventFile(DateTime.Now, "DatabaseWorker", "GetExperimentIDs", ex.Message);
return null;
}
}
public static List<string> GetAllParameters(ADatabase db)
{
try
{
List<string> result = new List<string>();
string sql = "SELECT ParamName FROM PARAMETERS";
NpgsqlCommand readCommand = new NpgsqlCommand(sql, db.Connection);
NpgsqlDataReader myreader = readCommand.ExecuteReader();
while (myreader.Read())
{
// load the combobox with the names of the people inside.
// myreader[0] reads from the 1st Column
result.Add(myreader[0].ToString());
}
myreader.Close(); // we are done with the reader
return result;
}
catch (Exception ex)
{
FileWorker.WriteEventFile(DateTime.Now, "DatabaseWorker", "GetAllParameters", ex.Message);
return null;
}
}
internal static List<string> GetExperimentIDsInfo(ADatabase db, int experimentGroup)
{
try
{
List<string> result = new List<string>();
string sql = "SELECT ExperimentID, baseblocks.baseblockserial, SensorSerial, SensorFirmware FROM Experiments, baseblocks, channels Where baseblocks.baseblockid = channels.baseblockid and experiments.syschannelid = channels.syschannelid and ExperimentGroupsID=" + experimentGroup;
NpgsqlCommand readCommand = new NpgsqlCommand(sql, db.Connection);
NpgsqlDataReader myreader = readCommand.ExecuteReader();
while (myreader.Read())
{
if (myreader.FieldCount == 4)
result.Add("ID: " + myreader[0].ToString() + " - ББ: " + myreader[1].ToString() +
"; Серийный номер датч.: " + myreader[2].ToString() + "; Прошивка датч.: " + myreader[3].ToString());
}
myreader.Close();
return result;
}
catch (Exception ex)
{
FileWorker.WriteEventFile(DateTime.Now, "DatabaseWorker", "GetExperimentIDsInfo", ex.Message);
return null;
}
}
internal static void WriteSensorStatistic(ADatabase db, DateTime dt, Sensor sensor, ABaseblock device, int experimentGroup, int count)
{
try
{
int expID = GetExperimentsID(db, experimentGroup, device.sysChannelsIDs[count]);
WriteParameter(db, dt, expID, sensor.SensorCalcLevel, ParametersID.LevelSensor);
WriteParameter(db, dt, expID, sensor.SensorTemperature, ParametersID.PlateTemperature);
if (sensor.SensorDelays != null)
{
WriteParameter(db, dt, expID, sensor.SensorDelays[0], ParametersID.ZondPositionSensor);
WriteParameter(db, dt, expID, sensor.SensorDelays[1], ParametersID.OtrPositionSensor);
WriteParameter(db, dt, expID, sensor.SensorDelays[2], ParametersID.SecondZondPositionSensor);
}
WriteParameter(db, dt, expID, sensor.ComputerCalcLevel, ParametersID.LevelComputer);
if (sensor.ComputerDelays != null)
{
WriteParameter(db, dt, expID, sensor.ComputerDelays[0], ParametersID.ZondPositionComputer);
WriteParameter(db, dt, expID, sensor.ComputerDelays[1], ParametersID.OtrPositionComputer);
WriteParameter(db, dt, expID, sensor.ComputerDelays[2], ParametersID.SecondZondPositionComputer);
}
if (sensor.ComputerAmplitudes != null)
{
WriteParameter(db, dt, expID, sensor.ComputerAmplitudes[0], ParametersID.FirstZondAmplitude);
WriteParameter(db, dt, expID, sensor.ComputerAmplitudes[1], ParametersID.OtrAmplitude);
WriteParameter(db, dt, expID, sensor.ComputerAmplitudes[2], ParametersID.SecondZondAmplitude);
}
if (sensor.TemperatureArray != null)
WriteTemperatureHanger(db, dt, expID, sensor.TemperatureArray);
}
catch (Exception ex)
{
FileWorker.WriteEventFile(DateTime.Now, "DatabaseWorker", "WriteSensorStatistic", ex.Message);
}
}
internal static void WriteSensorReflectogram(ADatabase db, DateTime dt, Sensor sensor, ABaseblock device, int experimentGroup, int count)
{
try
{
int expID = GetExperimentsID(db, experimentGroup, device.sysChannelsIDs[count]);
WriteReflectogram(db, dt, sensor.Reflectogram, expID);
}
catch (Exception ex)
{
FileWorker.WriteEventFile(DateTime.Now, "DatabaseWorker", "WriteSensorReflectogram", ex.Message);
}
}
internal static bool WriteGroupExperimentEndDate(ADatabase db, int experimentGroup)
{
try
{
string sql = "UPDATE experimentgroups SET datestop='" + DateTime.Now.ToString("dd.MM.yy HH:mm:ss.ff") + "' where experimentgroups.experimentgroupsid=" + experimentGroup.ToString();
NpgsqlCommand command = new NpgsqlCommand(sql, db.Connection);
command.ExecuteNonQuery();
return true;
}
catch (Exception ex)
{
FileWorker.WriteEventFile(DateTime.Now, "DatabaseWorker", "WriteGroupExperimentEndDate", ex.Message);
return false;
}
}
internal static void WriteExperiments(ADatabase db, ref int experimentGroup, List<ABaseblock> baseblockInfo, DataGridViewRowCollection calibvalues, int p1, int p2, string p3)
{
try
{
int groupID = WriteExperimentGroup(db, p1, p2, p3);
foreach (ABaseblock baseblock in baseblockInfo)
{
int count = 0;
foreach (int sysChannel in baseblock.sysChannelsIDs)
{
int experimentID = WriteExperiment(db, groupID, sysChannel, baseblock.comport, baseblock.channelsWithSensors, calibvalues[count].Cells[1].Value, count);
if (experimentID <= 0)
{
}
count++;
}
}
experimentGroup = groupID;
}
catch (Exception ex)
{
FileWorker.WriteEventFile(DateTime.Now, "DatabaseWorker", "WriteExperiments", ex.Message);
}
}
internal static void WriteChannels(ADatabase db, ref List<ABaseblock> baseblockInfo)
{
try
{
int count = 0;
foreach (ABaseblock baseblock in baseblockInfo)
{
List<int> listSysChannelIDs = new List<int>();
foreach (byte channel in baseblock.channelsWithSensors)
{
int channelID = GetSysChannelID(db, baseblock.baseblockID, channel);
if (channelID == -1)
{
string sql = "INSERT INTO Channels (BaseblockID, BaseblockChannel) VALUES (" + baseblock.baseblockID + "," + channel + ")";
NpgsqlCommand command = new NpgsqlCommand(sql, db.Connection);
command.ExecuteNonQuery();
sql = "SELECT currval(pg_get_serial_sequence('Channels', 'syschannelid'));";
NpgsqlCommand readCommand = new NpgsqlCommand(sql, db.Connection);
NpgsqlDataReader myreader = readCommand.ExecuteReader();
myreader.Read();
listSysChannelIDs.Add(Convert.ToInt32(myreader[0].ToString()));
myreader.Close();
}
else
listSysChannelIDs.Add(channelID);
}
baseblockInfo[count].sysChannelsIDs = listSysChannelIDs;
count++;
}
}
catch (Exception ex)
{
FileWorker.WriteEventFile(DateTime.Now, "DatabaseWorker", "WriteChannels", ex.Message);
}
}
internal static double GetTemperature(ADatabase db, int experimentGroup, string key, byte experimentChannel, string time)
{
try
{
if (key != null)
{
int comStart = key.IndexOf('(') + 1;
int comEnd = key.IndexOf(')') - comStart;
string comport = key.Substring(comStart, comEnd);
string baseblockSerial = key.Substring(0, comStart - 2);
string sql = "select parametersvalues.parametervalue from parametersvalues where parametersvalues.datetime= '" + time + "' "
+ " and parametersvalues.parametersid=" + ((int)ParametersID.PlateTemperature).ToString() + " and parametersvalues.experimentid=(select experiments.experimentid from experiments where experiments.experimentgroupsid="
+ experimentGroup.ToString() + " and experiments.syschannelid=(select channels.syschannelid from channels where channels.baseblockchannel="
+ experimentChannel.ToString() + "and channels.baseblockid=(select baseblocks.baseblockid from baseblocks where baseblocks.comport='" + comport + "' and baseblocks.baseblockserial='" + baseblockSerial + "')))";
NpgsqlCommand readCommand = new NpgsqlCommand(sql, db.Connection);
NpgsqlDataReader myreader = readCommand.ExecuteReader();
if (myreader.Read())
{
double result = ConvertToDoubleWithCheck(myreader[0].ToString(), -1);
myreader.Close();
return result;
}
else
{
myreader.Close();
return -1;
}
}
else
{
return -1;
}
}
catch (Exception ex)
{
FileWorker.WriteEventFile(DateTime.Now, "DatabaseWorker", "GetTemperature", ex.Message);
return -1;
}
}
private static void DeleteExperimentGroup(ADatabase db, int experiment)
{
try
{
string sql = "DELETE FROM ExperimentGroups WHERE ExperimentGroups.ExperimentGroupsID=" + experiment.ToString();
NpgsqlCommand command = new NpgsqlCommand(sql, db.Connection);
command.ExecuteNonQuery();
}
catch (Exception ex)
{
FileWorker.WriteEventFile(DateTime.Now, "DatabaseWorker", "DeleteExperimentGroup", ex.Message);
}
}
private static int WriteExperiment(ADatabase db, int groupID, int sysChannel, string comport, List<byte> channelsWithSensors, object calibValue, int i)
{
try
{
ADriver dr = new ADriver(comport, 50);
string error = "";
if (dr.OpenPort(ref error))
{
if (dr.SetChannel(channelsWithSensors[i], ref error))
{
string serial = dr.GetSerial(ref error);
if (error != "")
{
return -1;
}
double firmware = dr.GetFirmware(ref error);
if (error != "")
{
return -2;
}
if (!dr.ClosePort())
{
return -3;
}
string calibValueString;
if (calibValue != null)
calibValueString = ConvertToDoubleWithCheck(calibValue.ToString(), -1).ToString();
else
calibValueString = "null";
string sql = "INSERT INTO Experiments (ExperimentGroupsID, SysChannelID, SensorSerial, SensorFirmware, CalibrationValue) VALUES (" + groupID + ","
+ sysChannel + "," + serial + "," + firmware + "," + calibValueString + ")";
NpgsqlCommand command = new NpgsqlCommand(sql, db.Connection);
command.ExecuteNonQuery();
sql = "SELECT currval(pg_get_serial_sequence('Experiments', 'experimentid'));";
NpgsqlCommand readCommand = new NpgsqlCommand(sql, db.Connection);
NpgsqlDataReader myreader = readCommand.ExecuteReader();
myreader.Read();
int result = Convert.ToInt32(myreader[0].ToString());
myreader.Close();
return result;
}
else
{
return -4;
}
}
else
{
return -5;
}
}
catch (Exception ex)
{
FileWorker.WriteEventFile(DateTime.Now, "DatabaseWorker", "WriteExperiment", ex.Message);
return -1;
}
}
private static int GetExperimentsID(ADatabase db, int experimentGroup, int sysChannel)
{
try
{
string sql = "SELECT ExperimentID FROM Experiments Where SysChannelID=" + sysChannel + " AND ExperimentGroupsID=" + experimentGroup;
NpgsqlCommand readCommand = new NpgsqlCommand(sql, db.Connection);
NpgsqlDataReader myreader = readCommand.ExecuteReader();
if (myreader.Read())
{
int result = Convert.ToInt32(myreader[0].ToString());
myreader.Close();
return result;
}
else
{
myreader.Close();
return -1;
}
}
catch (Exception ex)
{
FileWorker.WriteEventFile(DateTime.Now, "DatabaseWorker", "GetExperimentsID", ex.Message);
return -1;
}
}
private static void DeleteExperimentReflectogramsAndData(ADatabase db, int experiment)
{
try
{
NpgsqlTransaction t = db.Connection.BeginTransaction();
string sql = "DELETE FROM Reflectograms WHERE Reflectograms.ExperimentID=" + experiment.ToString();
NpgsqlCommand command = new NpgsqlCommand(sql, db.Connection);
command.ExecuteNonQuery();
sql = "DELETE FROM ParametersValues WHERE ParametersValues.ExperimentID=" + experiment.ToString();
command = new NpgsqlCommand(sql, db.Connection);
command.ExecuteNonQuery();
sql = "DELETE FROM TemperatureHangers WHERE TemperatureHangers.ExperimentID=" + experiment.ToString();
command = new NpgsqlCommand(sql, db.Connection);
command.ExecuteNonQuery();
sql = "DELETE FROM Experiments WHERE Experiments.ExperimentID=" + experiment.ToString();
command = new NpgsqlCommand(sql, db.Connection);
command.ExecuteNonQuery();
t.Commit();
}
catch (Exception ex)
{
FileWorker.WriteEventFile(DateTime.Now, "DatabaseWorker", "DeleteExperimentReflectogramsAndData", ex.Message);
}
}
internal static List<string> GetLastReflectograms(ADatabase db, int experimentGroup, string key, byte experimentChannel)
{
try
{
int comStart = key.IndexOf('(') + 1;
int comEnd = key.IndexOf(')') - comStart;
string comport = key.Substring(comStart, comEnd);
string baseblockSerial = key.Substring(0, comStart - 2);
List<string> result = new List<string>();
string sql = "select reflectograms.datetime from reflectograms where reflectograms.experimentid=(select experiments.experimentid from experiments where experiments.experimentgroupsid="
+ experimentGroup.ToString() + " and experiments.syschannelid=(select channels.syschannelid from channels where channels.baseblockchannel="
+ experimentChannel.ToString() + " and channels.baseblockid=(select baseblocks.baseblockid from baseblocks where baseblocks.comport='" + comport + "' and baseblocks.baseblockserial='" + baseblockSerial + "')))" + " order by reflectograms.datetime desc limit 1";
NpgsqlCommand readCommand = new NpgsqlCommand(sql, db.Connection);
NpgsqlDataReader myreader = readCommand.ExecuteReader();
while (myreader.Read())
{
DateTime date = Convert.ToDateTime(myreader[0]);
result.Add(date.ToString("dd.MM.yy HH:mm:ss.ff"));
}
myreader.Close();
return result;
}
catch (Exception ex)
{
FileWorker.WriteEventFile(DateTime.Now, "DatabaseWorker", "GetLastReflectograms", ex.Message);
return null;
}
}
private static int GetSysChannelID(ADatabase db, int baseblockID, byte channel)
{
try
{
string sql = "SELECT SysChannelID FROM Channels Where BaseblockID=" + baseblockID.ToString() + " AND BaseblockChannel=" + channel;
NpgsqlCommand readCommand = new NpgsqlCommand(sql, db.Connection);
NpgsqlDataReader myreader = readCommand.ExecuteReader();
if (myreader.Read())
{
// load the combobox with the names of the people inside.
// myreader[0] reads from the 1st Column
int result = Convert.ToInt32(myreader[0]);
myreader.Close(); // we are done with the reader
return result;
}
else
{
myreader.Close();
return -1;
}
}
catch (Exception ex)
{
FileWorker.WriteEventFile(DateTime.Now, "DatabaseWorker", "GetSysChannelID", ex.Message);
return -1;
}
}
internal static List<int> GetParametersForExport(ADatabase db, int experimentID)
{
try
{
List<int> result = new List<int>();
string sql = "select distinct parametersid from parametersvalues where experimentid=" + experimentID.ToString() + " order by parametersid";
NpgsqlCommand readCommand = new NpgsqlCommand(sql, db.Connection);
NpgsqlDataReader myreader = readCommand.ExecuteReader();
while (myreader.Read())
{
if (myreader.FieldCount > 0)
result.Add(Convert.ToInt32(myreader[0].ToString()));
}
myreader.Close();
return result;
}
catch (Exception ex)
{
FileWorker.WriteEventFile(DateTime.Now, "DatabaseWorker", "GetParametersForExport", ex.Message);
return null;
}
}
private static int WriteExperimentGroup(ADatabase db, int p1, int p2, string p3)
{
try
{
string sql = "INSERT INTO ExperimentGroups (DateStart, PowerDelay, CommDelay, Description) VALUES ('" + DateTime.Now.ToString("dd.MM.yy HH:mm:ss.ff") + "'," + p1 + "," + p2 + ",'" + p3 + "')";
NpgsqlCommand command = new NpgsqlCommand(sql, db.Connection);
command.ExecuteNonQuery();
sql = "SELECT currval(pg_get_serial_sequence('ExperimentGroups', 'experimentgroupsid'));";
NpgsqlCommand readCommand = new NpgsqlCommand(sql, db.Connection);
NpgsqlDataReader myreader = readCommand.ExecuteReader();
myreader.Read();
int result = Convert.ToInt32(myreader[0].ToString());
myreader.Close();
return result;
}
catch (Exception ex)
{
FileWorker.WriteEventFile(DateTime.Now, "DatabaseWorker", "WriteExperimentGroup", ex.Message);
return -1;
}
}
internal static ZedGraph.PointPairList GetReflectogramData(ADatabase db, int experimentGroup, string key, byte experimentChannel, string time)
{
try
{
int comStart = key.IndexOf('(') + 1;
int comEnd = key.IndexOf(')') - comStart;
string comport = key.Substring(comStart, comEnd);
string baseblockSerial = key.Substring(0, comStart - 2);
string sql = "select reflectograms.refldata from reflectograms where reflectograms.datetime='" + time + "'"
+ " and reflectograms.experimentid=(select experiments.experimentid from experiments where experiments.experimentgroupsid="
+ experimentGroup.ToString() + " and experiments.syschannelid=(select channels.syschannelid from channels where channels.baseblockchannel="
+ experimentChannel.ToString() + "and channels.baseblockid=(select baseblocks.baseblockid from baseblocks where baseblocks.comport='" + comport + "' and baseblocks.baseblockserial='" + baseblockSerial + "')))";
NpgsqlCommand readCommand = new NpgsqlCommand(sql, db.Connection);
NpgsqlDataReader myreader = readCommand.ExecuteReader();
if (myreader.Read())
{
double[] refldata = myreader[0] as double[];
myreader.Close();
return ReflectogramDataToPointPairList(refldata);
}
else
{
myreader.Close();
return null;
}
}
catch (Exception ex)
{
FileWorker.WriteEventFile(DateTime.Now, "DatabaseWorker", "GetReflectogramData", ex.Message);
return null;
}
}
