public bool CheckingDuplicate(Measurements measure, MqttCfgSettingsOrganiser mqttCfgSettings)
{
// OPENS CONNECTION
SqlConnection.Open();
string sql = "SELECT MeasureTimeStamp, " +
"MachineType, " +
"MachineNumber, " +
"Part, " +
"PartNumber, " +
"ValueKind, " +
"MeasureValue, " +
"TextValue " +
"FROM public.qcmachines " +
"WHERE MeasureTimeStamp = '" + measure.TimeStamp + "' and " +
"MachineType = '" + measure.MachineType + "' and " +
"MachineModel = '" + measure.MachineModel + "' and " +
"MachineNumber = '" + measure.MachineNumber + "' and " +
"Part = '" + measure.Part + "' and " +
"PartNumber = '" + measure.PartNumber + "' and " +
"ValueKind = '" + measure.ValueKind + "' and " +
"MeasureValue = '" + measure.Value + "' and " +
"TextValue = '" + measure.TextValue + "';";
// Define a query
NpgsqlCommand command = new NpgsqlCommand(sql, SqlConnection);
// Execute the query and obtain a result set
NpgsqlDataReader DataReader = command.ExecuteReader();
bool IsDuplicate;
if (DataReader.HasRows)
{
IsDuplicate = true;
}
else
{
IsDuplicate = false;
}
// CLOSES CONNECTION
SqlConnection.Close();
return(IsDuplicate);
}
public void StoreIntoPostgreSQL(Measurements measure, MqttCfgSettingsOrganiser mqttCfgSettings)
{
try
{
// OPENS CONNECTION
SqlConnection.Open();
string sql = "INSERT INTO QCMachines " +
"(MeasureTimeStamp, " +
"MachineType, " +
"MachineModel, " +
"MachineNumber, " +
"Part, " +
"PartNumber, " +
"ValueKind, " +
"MeasureValue, " +
"TextValue) " +
"VALUES ('" + measure.TimeStamp + "',"
+ "'" + measure.MachineType + "',"
+ "'" + measure.MachineModel + "',"
+ "'" + measure.MachineNumber + "',"
+ "'" + measure.Part + "',"
+ "'" + measure.PartNumber + "',"
+ "'" + measure.ValueKind + "',"
+ "'" + measure.Value + "',"
+ "'" + measure.TextValue + "');";
SqlCommand = new NpgsqlCommand(sql, SqlConnection);
SqlCommand.CommandType = System.Data.CommandType.Text;
// EXECUTES QUERY
SqlCommand.ExecuteNonQuery();
// CLOSES CONNECTION
SqlConnection.Close();
}
catch (Exception PostgreError)
{
Console.WriteLine("ERROR: Failed to store measurement on PostgreSQL DB:\n" + PostgreError);
}
}
private void insertIntoMeasurementsList(List <Dictionary <string, string> > rawMeasurements, string dateTime)
{
DateTime oldDateTime = Convert.ToDateTime("01/01/2000 00:00:00");
// INSERTING TIMESTAMP & PART TO ALL
foreach (var measure in rawMeasurements)
{
measurements = new Measurements();
measurements.TimeStamp = Convert.ToDateTime(dateTime + " " + measure["Hour"]);
measurements.Part = measure["FILEPARAM"];
measurements.PartNumber = Convert.ToInt32(measure["BATCH"]);
if (oldDateTime != measurements.TimeStamp)
{
// measurements.ValueKind = "LOTTO";
// measurements.Value = Convert.ToInt32(measure["BATCH"]);
oldDateTime = measurements.TimeStamp;
}
measurementsList.Add(measurements);
}
int cnt = 0;
foreach (var measure in rawMeasurements)
{
measurementsList[cnt].ValueKind = measure["DESC"];
bool IsPositive = checkAllTestsResult(measure);
if (IsPositive == true)
{
measurementsList[cnt].Value = 1;
}
else
{
measurementsList[cnt].Value = -1;
}
cnt++;
}
}
// USING FILE AND PATH FETCHED FROM CONFIG.XML
public List <Measurements> readFromXML(SettingsFromXML settingsFromXML, MqttCfgSettingsOrganiser mqttCfgSettings)
{
string key_dict = "";
XmlTextReader reader = new XmlTextReader(mqttCfgSettings.InputPathDirectory + "\\" + mqttCfgSettings.InputFile);
while (reader.Read())
{
switch (reader.NodeType)
{
case XmlNodeType.Element: // The node is an element.
key_dict = reader.Name;
break;
case XmlNodeType.Text: //Display the text in each element.
{
if (key_dict.ToUpper() == settingsFromXML.firstValue)
{
measurements = new Measurements();
}
Measurements.StoreMeasurements(key_dict, reader.Value, measurements);
if (key_dict.ToUpper() == settingsFromXML.lastValue)
{
measurementsList.Add(measurements);
}
break;
}
case XmlNodeType.EndElement: //Display the end of the element.
break;
}
}
return(measurementsList);
}
public static Measurements StoreMeasurements(string key, string value, Measurements measurements)
{
switch (key.ToUpper())
{
case "TIMESTAMP":
measurements.TimeStamp = Convert.ToDateTime(value);
break;
case "MACHINETYPE":
measurements.MachineType = value;
break;
case "MACHINEMODEL":
measurements.MachineModel = value;
break;
case "MACHINENUMBER":
measurements.MachineNumber = Convert.ToInt32(value);
break;
case "PART":
measurements.Part = value;
break;
case "PARTNUMBER":
measurements.PartNumber = Convert.ToInt32(value);
break;
case "VALUEKIND":
measurements.ValueKind = value;
break;
case "TEXTVALUE":
measurements.TextValue = value;
break;
case "VALUE":
measurements.Value = Convert.ToDouble(value);
break;
case "ROUNDTIMESTAMP":
measurements.RoundTimeStamp = Convert.ToBoolean(value);
break;
case "REPLACEUTCTIME":
measurements.ReplaceUtcTime = Convert.ToBoolean(value);
break;
case "FORWARDMEASURE":
measurements.ForwardMeasure = Convert.ToBoolean(value);
break;
case "STOREMEASURE":
measurements.StoreMeasure = Convert.ToBoolean(value);
break;
case "READCLOCK":
measurements.ReadClock = Convert.ToInt32(value);
break;
default:
Console.WriteLine("Couldn't recognize key: " + key);
break;
}
return(measurements);
}