示例#1
0
        public MqttPublisher(MqttCfgSettingsOrganiser mqttCfgSettings)
        {
            client = new MqttClient(mqttCfgSettings.MqttIpAddressTopicPublish);
            string clientId = Guid.NewGuid().ToString();

            client.Connect(clientId);
        }
示例#2
0
        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);
        }
示例#3
0
        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
            }
        }
示例#4
0
        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);
        }
示例#5
0
        // --------------------- 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...");
            }
        }
示例#6
0
        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);
        }
示例#7
0
        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);
        }
示例#8
0
        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);
        }
示例#9
0
        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);
        }
示例#10
0
        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);
            }
        }
示例#11
0
        // 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);
        }
示例#12
0
        /***********************************************
        *          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);
        }
示例#13
0
        // --------------------- 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);
        }
示例#14
0
        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);
        }
示例#15
0
 public void Publish(string message, MqttCfgSettingsOrganiser mqttCfgSettings)
 {
     client.Publish(mqttCfgSettings.MqttTopicPublish, Encoding.UTF8.GetBytes(message));
 }