public static bool Select(JuMachineDataBelimed aMachineDataBelimed, MySqlConnection aConnection, string aSchema, out long aMDNDX)
{
aMDNDX = 0;
string sql = string.Format("SELECT MDNDX FROM {0}.ORIS_MACHINEDATABELIMED WHERE MACHINEID = @MACHINEID AND CYCLEREFERENCE = @CYCLEREFERENCE", aSchema);
string mdNDXString = "";
using (MySqlCommand cmd = new MySqlCommand(sql, aConnection))
{
cmd.Parameters.AddWithValue("@MACHINEID", aMachineDataBelimed.MachineID);
cmd.Parameters.AddWithValue("@CYCLEREFERENCE", aMachineDataBelimed.CycleReference);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
mdNDXString = reader["MDNDX"].ToString();
}
}
}
if (string.IsNullOrEmpty(mdNDXString))
{
return(false);
}
if (!long.TryParse(mdNDXString, out aMDNDX))
{
return(false);
}
return(aMDNDX > 0);
}
public static bool Insert(JuMachineDataBelimed aMachineDataBelimed, MySqlConnection aConnection, string aSchema)
{
string sql = string.Format("INSERT INTO {0}.ORIS_MACHINEDATABELIMED (MACHINEID, MACHINENAME, PROGRAMREFERENCE, PROGRAMNAME, STARTEDBY, SOFTWAREVERSION, " +
"LASTBDTEST, LASTBDTESTREFERENCE, LASTVACUUMTEST, LASTVACUUMTESTREFERENCE, CYCLEREFERENCE, CYCLESTARTED, CYCLEENDED, ISCYCLEOK, CYCLERESULT, " +
"HOLDTIME, F0VALUE, PROGRAMDATE, CYCLEDURATION, STERILIZATIONTEMPMIN, STERILIZATIONTEMPMAX, LEAKRATE, FUNCTIONALCHECK, " +
"CYCLERELEASED, CYCLERELEASEDBY, CYCLERELEASESTATE, BATCHDATA1, BATCHDATA2, BATCHDATA3, TESTRESULT, RIF1, RIF2, RIF3, DTCREATED) " +
"VALUES(@MACHINEID, @MACHINENAME, @PROGRAMREFERENCE, @PROGRAMNAME, @STARTEDBY, @SOFTWAREVERSION, " +
"@LASTBDTEST, @LASTBDTESTREFERENCE, @LASTVACUUMTEST, @LASTVACUUMTESTREFERENCE, @CYCLEREFERENCE, @CYCLESTARTED, @CYCLEENDED, @ISCYCLEOK, @CYCLERESULT, " +
"@HOLDTIME, @F0VALUE, @PROGRAMDATE, @CYCLEDURATION, @STERILIZATIONTEMPMIN, @STERILIZATIONTEMPMAX, @LEAKRATE, @FUNCTIONALCHECK, " +
"@CYCLERELEASED, @CYCLERELEASEDBY, @CYCLERELEASESTATE, @BATCHDATA1, @BATCHDATA2, @BATCHDATA3, @TESTRESULT, @RIF1, @RIF2, @RIF3, @DTCREATED)", aSchema);
int rowCount = 0;
using (MySqlCommand cmd = new MySqlCommand(sql, aConnection))
{
cmd.Parameters.AddWithValue("@MACHINEID", aMachineDataBelimed.MachineID);
cmd.Parameters.AddWithValue("@MACHINENAME", aMachineDataBelimed.MachineName);
cmd.Parameters.AddWithValue("@PROGRAMREFERENCE", aMachineDataBelimed.ProgramReference);
cmd.Parameters.AddWithValue("@PROGRAMNAME", aMachineDataBelimed.ProgramName);
cmd.Parameters.AddWithValue("@STARTEDBY", aMachineDataBelimed.StartedBy);
cmd.Parameters.AddWithValue("@SOFTWAREVERSION", aMachineDataBelimed.SoftwareVersion);
cmd.Parameters.AddWithValue("@LASTBDTEST", aMachineDataBelimed.LastBDTest);
cmd.Parameters.AddWithValue("@LASTBDTESTREFERENCE", aMachineDataBelimed.LastBDTestReference);
cmd.Parameters.AddWithValue("@LASTVACUUMTEST", aMachineDataBelimed.LastVacuumTest);
cmd.Parameters.AddWithValue("@LASTVACUUMTESTREFERENCE", aMachineDataBelimed.LastVacuumTestReference);
cmd.Parameters.AddWithValue("@CYCLEREFERENCE", aMachineDataBelimed.CycleReference);
cmd.Parameters.AddWithValue("@CYCLESTARTED", aMachineDataBelimed.CycleStarted);
cmd.Parameters.AddWithValue("@CYCLEENDED", aMachineDataBelimed.CycleEnded);
cmd.Parameters.AddWithValue("@ISCYCLEOK", Conversions.BoolToString(aMachineDataBelimed.IsCycleOk));
cmd.Parameters.AddWithValue("@CYCLERESULT", aMachineDataBelimed.CycleResult);
cmd.Parameters.AddWithValue("@HOLDTIME", aMachineDataBelimed.HoldTime);
cmd.Parameters.AddWithValue("@F0VALUE", aMachineDataBelimed.F0Value);
cmd.Parameters.AddWithValue("@PROGRAMDATE", aMachineDataBelimed.ProgramDate);
cmd.Parameters.AddWithValue("@CYCLEDURATION", aMachineDataBelimed.CycleDuration);
cmd.Parameters.AddWithValue("@STERILIZATIONTEMPMIN", aMachineDataBelimed.SterilizationTempMin);
cmd.Parameters.AddWithValue("@STERILIZATIONTEMPMAX", aMachineDataBelimed.SterilizationTempMax);
cmd.Parameters.AddWithValue("@LEAKRATE", aMachineDataBelimed.LeakRate);
cmd.Parameters.AddWithValue("@FUNCTIONALCHECK", aMachineDataBelimed.FunctionalCheck);
cmd.Parameters.AddWithValue("@CYCLERELEASED", aMachineDataBelimed.CycleReleased);
cmd.Parameters.AddWithValue("@CYCLERELEASEDBY", aMachineDataBelimed.CycleReleasedBy);
cmd.Parameters.AddWithValue("@CYCLERELEASESTATE", aMachineDataBelimed.CycleReleaseState);
cmd.Parameters.AddWithValue("@BATCHDATA1", aMachineDataBelimed.BatchData1);
cmd.Parameters.AddWithValue("@BATCHDATA2", aMachineDataBelimed.BatchData2);
cmd.Parameters.AddWithValue("@BATCHDATA3", aMachineDataBelimed.BatchData3);
cmd.Parameters.AddWithValue("@TESTRESULT", aMachineDataBelimed.TestResult);
cmd.Parameters.AddWithValue("@RIF1", aMachineDataBelimed.RIF1);
cmd.Parameters.AddWithValue("@RIF2", aMachineDataBelimed.RIF2);
cmd.Parameters.AddWithValue("@RIF3", aMachineDataBelimed.RIF3);
cmd.Parameters.AddWithValue("@DTCREATED", DateTime.Now);
rowCount = cmd.ExecuteNonQuery();
}
return(rowCount == 1);
}
public static bool MoveFileToArchive(string aFileFullPath)
{
if (!File.Exists(aFileFullPath))
{
return(false);
}
string acFileFullPath = JuMachineDataBelimed.GetCFilePath(aFileFullPath);
if (!MoveFile(aFileFullPath))
{
return(false);
}
if (!MoveFile(acFileFullPath))
{
return(false);
}
return(true);
}
private static void Oncreated(object sender, FileSystemEventArgs e)
{
Console.WriteLine("New file detected: " + e.Name);
MySqlConnection connection = null;
JuMachineDataBelimed machineDataBelimed = new JuMachineDataBelimed(-1);
if (machineDataBelimed.LoadFromFile(e.FullPath))
{
Console.WriteLine(e.Name + " was parsed.");
}
else
{
Console.WriteLine(e.Name + " could not be parsed.");
return;
}
try
{
connection = DBMariaDB.GetConnection(Database, Server, UserID, Password);
if (connection != null)
{
if (!DBMariaDB.Insert(machineDataBelimed, connection, Schema))
{
Console.WriteLine(e.Name + ": MachineData could not be written to database");
return;
}
if (!DBMariaDB.Select(machineDataBelimed, connection, Schema, out long mdNDX))
{
Console.WriteLine(e.Name + ": MachineData has not a valid MDNDX");
return;
}
foreach (JuMachineSensor machineSensor in machineDataBelimed.MachineSensors)
{
if (!DBMariaDB.Insert(machineSensor, mdNDX, connection, Schema))
{
Console.WriteLine(e.Name + ": MachineSensor " + machineSensor.SensorID + " could not be written to database");
return;
}
}
foreach (JuMachineSensorValue machineSensorValue in machineDataBelimed.MachineSensorValues)
{
if (!DBMariaDB.Insert(machineSensorValue, mdNDX, connection, Schema))
{
Console.WriteLine(e.Name + ": MachineSensor " + machineSensorValue.MDNDX + " could not be written to database");
return;
}
}
}
else
{
Console.WriteLine("Connection to database failed");
}
}
catch (Exception exc)
{
Console.WriteLine(e.Name + " database handling error: " + exc.Message);
}
finally
{
if (connection != null)
{
connection.Close();
}
}
if (ArchiveFile)
{
if (MoveFileToArchive(e.FullPath))
{
Console.WriteLine(e.Name + " was archived.");
}
else
{
Console.WriteLine(e.Name + " could not be archived.");
}
}
}
