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); }