public MqttPublisher(MqttCfgSettingsOrganiser mqttCfgSettings) { client = new MqttClient(mqttCfgSettings.MqttIpAddressTopicPublish); string clientId = Guid.NewGuid().ToString(); client.Connect(clientId); }
public List <Measurements> readFromTXT(SettingsFromXML settingsFromXML, MqttCfgSettingsOrganiser mqttCfgSettings) { // Retrieve the info from the config.xml string[] rows; if (settingsFromXML.inputFormat == "singleLine") { string content = File.ReadAllText(mqttCfgSettings.InputPathDirectory + "\\" + mqttCfgSettings.InputFile); rows = content.Split(settingsFromXML.splitVariables); } else // separatedRows { rows = File.ReadAllLines(mqttCfgSettings.InputPathDirectory + "\\" + mqttCfgSettings.InputFile); } foreach (var row in rows) { string[] tokens = row.Split(settingsFromXML.splitKeyValue); if (tokens[0].ToUpper() == settingsFromXML.firstValue) { measurements = new Measurements(); } Measurements.StoreMeasurements(tokens[0], tokens[1], measurements); if (tokens[0].ToUpper() == settingsFromXML.lastValue) { measurementsList.Add(measurements); } } return(measurementsList); }
public void Start(SettingsFromXML settingsFromXML, MqttCfgSettingsOrganiser mqttCfgSettings, MqttPublisher mqttPublisher) { List <Measurements> measurementsList = new List <Measurements>(); while (true) { Console.WriteLine("-------------------------------------------------------------"); generatingFileNameToday(); bool IsFileExist = File.Exists(mqttCfgSettings.InputPathDirectory + "\\" + fileName + ".csv"); if (IsFileExist == true) { Console.WriteLine("Reading from input"); measurementsList = readingFromInput(settingsFromXML, mqttCfgSettings); Console.WriteLine("Adding info from MQTT Cfg"); measurementsList = InsertMachineDataFromMqttCfg(measurementsList, mqttCfgSettings); Console.WriteLine("Checking Duplicates & Storing into PostgreSQL Database & forwarding onto MQTT Topic"); writingIntoOutput(measurementsList, mqttCfgSettings, mqttPublisher); Console.WriteLine("Cleaning cache and starting a new detection"); measurementsList.Clear(); } else { Console.WriteLine("The report does not exist in the specified path: " + mqttCfgSettings.InputPathDirectory + "\\" + fileName + ".csv"); } Console.WriteLine("Waiting Cycle time for refresh: " + mqttCfgSettings.Cycle + " minutes"); System.Threading.Thread.Sleep(mqttCfgSettings.Cycle * 60 * 1000); // from minutes to ms } }
public List <Measurements> readFromCSV(MqttCfgSettingsOrganiser mqttCfgSettings) { using (var reader = new StreamReader(mqttCfgSettings.InputPathDirectory + "/" + mqttCfgSettings.InputFile)) { List <string[]> list = new List <string[]>(); string[] variableNames = reader.ReadLine().Split(','); string[] values; string excelRow = ""; string tempTimeStamp = ""; // stores and add the timestamp of each row, at the end of the every "ReadLine()" string product = ""; int k = 0; while (!reader.EndOfStream) { excelRow = reader.ReadLine(); if (excelRow != "") { values = excelRow.Split(','); for (int i = 0; i < variableNames.Length; i++) { measurements = new Measurements(); if (variableNames[i] != "") // skip to next "," when content is empty { if (variableNames[i].ToUpper() == mqttCfgSettings.TimeStampName) // DATA: e ORA: { tempTimeStamp = values[i] + " " + values[i + 1]; i++; } else { measurements.ValueKind = variableNames[i]; try // if VALUE can be a NUMBER { measurements.Value = Convert.ToDouble(values[i]); } catch // otherwise IT MUST BE a STRING { measurements.TextValue = values[i]; } if (measurements.ValueKind == mqttCfgSettings.Part) { product = values[i]; } measurementsList.Add(measurements); } } } // at the end of each row, it adds the stored timestamp for the report for (k = k; k < measurementsList.Count; k++) { measurementsList[k].TimeStamp = Convert.ToDateTime(tempTimeStamp); measurementsList[k].Part = product; } } } } return(measurementsList); }
// --------------------- GENERATING OUTPUT FILE --------------------- // private static void writingIntoOutput(List <Measurements> measurementsList, MqttCfgSettingsOrganiser mqttCfgSettings, MqttPublisher mqttPublisher) { if (measurementsList.Capacity != 0) { //File.WriteAllText(Directory.GetCurrentDirectory() + "\\Output\\output.json", JsonConvert.SerializeObject(measurementsList)); // Opens the SQL Connection to the Postgre Database PostgreSqlManager Sql = new PostgreSqlManager(mqttCfgSettings); int Duplicates = 0; int Stored = 0; foreach (var measure in measurementsList) { string json = JsonConvert.SerializeObject(measure); bool IsDuplicate = true; // CHECK DUPLICATE & STORE INTO DB try { IsDuplicate = Sql.CheckingDuplicate(measure, mqttCfgSettings); if (IsDuplicate == false) { Sql.StoreIntoPostgreSQL(measure, mqttCfgSettings); } } catch (Exception StoreIntoDb) { Console.WriteLine("ERROR: Failed to committ transaction on PostgreSQL DB:\n" + StoreIntoDb); } if (IsDuplicate == false) { Stored++; // PUBLISHING ON MQTT TOPIC try { mqttPublisher.Publish(json, mqttCfgSettings); } catch (Exception Publish) { Console.WriteLine("ERROR: Failed to publish measurement on MQTT:\n" + Publish); } } else { Duplicates++; } } Console.WriteLine("Duplicate measurements found: " + Duplicates); Console.WriteLine("Measurements sent to MQTT topic: " + Stored); } else { Console.WriteLine("The input is empty, no output is going to be generated at this cycle..."); } }
private static string retrieveDateTime(MqttCfgSettingsOrganiser mqttCfgSettings) { string dateTime; dateTime = fileName + ".csv"; dateTime = dateTime.Remove(dateTime.IndexOf('.')); string year = dateTime.Substring(0, 4); string month = dateTime.Substring(4, 2); string day = dateTime.Substring(6, 2); return(dateTime = day + '/' + month + '/' + year); }
public PostgreSqlManager(MqttCfgSettingsOrganiser mqttCfgSettings) { // PostgeSQL-style connection string ConnectionString = String.Format("Server={0};Port={1};" + "User Id={2};Password={3};Database={4};", mqttCfgSettings.DbServer, mqttCfgSettings.DbPort, mqttCfgSettings.DbUser, mqttCfgSettings.DbPassword, mqttCfgSettings.DbName); // Making connection with Npgsql provider SqlConnection = new NpgsqlConnection(ConnectionString); }
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); }
static void Main(string[] args) { SettingsFromXML settingsFromXML = new SettingsFromXML(); // initialize settings from config.xml Console.WriteLine("Subscribing to Mqtt Topic"); MqttSubscriber mqttSubscriber = new MqttSubscriber(); mqttSubscriber.Subscribe(settingsFromXML); System.Threading.Thread.Sleep(1000); // wait for event "Client_MqttMsgPublishReceived" Console.WriteLine("Storing Configuration from Topic"); MqttCfgSettingsOrganiser mqttCfgSettings = new MqttCfgSettingsOrganiser(mqttSubscriber.MqttCfg); MqttPublisher mqttPublisher = new MqttPublisher(mqttCfgSettings); Transform transform = new Transform(); transform.Start(settingsFromXML, mqttCfgSettings, mqttPublisher); }
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); } }
// 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); }
/*********************************************** * INSERIMENTO DATI DAL CFG * * * ***********************************************/ private static List <Measurements> InsertMachineDataFromMqttCfg(List <Measurements> measurementsList, MqttCfgSettingsOrganiser mqttCfgSettings) { foreach (var measurements in measurementsList) { measurements.ReplaceUtcTime = mqttCfgSettings.ReplaceUtcTime; measurements.RoundTimeStamp = mqttCfgSettings.RoundTimeStamp; measurements.ReadClock = mqttCfgSettings.ReadClock; measurements.ForwardMeasure = mqttCfgSettings.ForwardMeasure; measurements.StoreMeasure = mqttCfgSettings.StoreMeasure; // THE "PART" KEY IS BEING SUED FOR THE PRODUCT CODE // measurements.Part = mqttCfgSettings.Part; // This is going to be the Lot Number //measurements.PartNumber = mqttCfgSettings.PartNumber; measurements.MachineNumber = mqttCfgSettings.MachineNumber; measurements.MachineType = mqttCfgSettings.MachineType; measurements.MachineModel = mqttCfgSettings.MachineModel; } return(measurementsList); }
// --------------------- RETRIEVING DATA FROM INPUT --------------------- private static List <Measurements> readingFromInput(SettingsFromXML settingsFromXML, MqttCfgSettingsOrganiser mqttCfgSettings) { List <Measurements> measurementsList = new List <Measurements>(); try { measurementsList = new List <Measurements>(); if (mqttCfgSettings.InputFileFormat == "TXT") { measurementsList = handleText.readFromTXT(settingsFromXML, mqttCfgSettings); } else if (mqttCfgSettings.InputFileFormat == "XML") { measurementsList = handleXML.readFromXML(settingsFromXML, mqttCfgSettings); } else if (mqttCfgSettings.InputFileFormat == "CSV") { string dateTime = retrieveDateTime(mqttCfgSettings); measurementsList = handleCSV.readFromDailyCSV(mqttCfgSettings, dateTime); } } catch (Exception ex1) { Console.WriteLine("ERROR: Failed to read from input:\n" + ex1); throw; } return(measurementsList); }
public List <Measurements> readFromDailyCSV(MqttCfgSettingsOrganiser mqttCfgSettings, string dateTime) { using (var reader = new StreamReader(mqttCfgSettings.InputPathDirectory + "/" + Transform.fileName + ".csv")) { List <string[]> varNamesList = new List <string[]>(); // RETRIEVE ALL THE KEYS (VARIABLE NAMES) FROM THE FIRST 5 ROWS for (int i = 0; i < 5; i++) { string[] varNamesArray = reader.ReadLine().Split(',', StringSplitOptions.RemoveEmptyEntries); varNamesList.Add(varNamesArray); } List <Dictionary <string, string> > rawMeasurements = new List <Dictionary <string, string> >(); Dictionary <string, string> productDict = new Dictionary <string, string>(); Dictionary <string, string> valuesDict; string[] excelRow; // READING ALL THE VALUES IN THE EXCEL FILE while (!reader.EndOfStream) { excelRow = reader.ReadLine().Split(',', StringSplitOptions.RemoveEmptyEntries); // FIRST ELEMENT OF VALUES IS TIMESTAMP if (excelRow[0].Contains(":")) { productDict = new Dictionary <string, string>(); int i = 0; foreach (var varName in varNamesList[0]) { productDict.Add(varName, excelRow[i]); i++; } } else { valuesDict = new Dictionary <string, string>(); foreach (var pippo in productDict) { valuesDict.Add(pippo.Key, pippo.Value); } string COD_N = "COD-" + Convert.ToInt16(excelRow[1]); int variableIndex = 0; switch (COD_N) { case "COD-1": variableIndex = 1; // Use VarNamesList[1] break; case "COD-2": variableIndex = 2; // Use VarNamesList[2] break; case "COD-4": variableIndex = 3; // Use VarNamesList[3] break; case "COD-5": variableIndex = 4; // Use VarNamesList[4] break; default: Console.WriteLine("Invalid selection. Please check COD-Number"); variableIndex = 5; // out of range break; } int i = 0; foreach (var varName in varNamesList[variableIndex]) { valuesDict.Add(varName, excelRow[i]); i++; } rawMeasurements.Add(valuesDict); } } insertIntoMeasurementsList(rawMeasurements, dateTime); } return(measurementsList); }
public void Publish(string message, MqttCfgSettingsOrganiser mqttCfgSettings) { client.Publish(mqttCfgSettings.MqttTopicPublish, Encoding.UTF8.GetBytes(message)); }