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