static void Main(string[] args) { Console.WriteLine("One Piece Flow Data Grabber V6.3"); Console.WriteLine("==========================="); // ================================================= // Connecting to Database // ================================================= string connetionString = @"Data Source=localhost\SQLEXPRESS;Initial Catalog=BeamDatabase;Integrated Security=True;MultipleActiveResultSets=true"; bool useDatabase = true; SqlConnection cnn = new SqlConnection(connetionString); try { cnn.Open(); } catch (Exception e) { useDatabase = false; } if (useDatabase) { Console.WriteLine("Using SQL Database"); } else { Console.WriteLine("Could not use SQL Database"); } // ================================================= // Connecting to PLC // ================================================= JPLCConnection plc = new JPLCConnection("192.168.0.1", 0, 1); string csvFilepath = System.Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + "\\OPF.csv"; double timeBetweenRetries = 6; // seconds double timeBetweenReads = 5; //seconds // ================================================= // Setup program // ================================================= var scadaDB = new Beams_for_SCADA(); var faultDB = new Alarms_for_SCADA(); var sql = new sqlObjects(); var scadaDBNumber = 113; var faultDBNumber = 114; // ================================================= // Reading From PLC // ================================================= // BEAM DATA var readDBObservable = Observable.Create <Beams_for_SCADA>(o => { var result = scadaDB.ReadFromDB(plc, scadaDBNumber); if (result != 0) { o.OnError(new Exception("Could not read from DB")); Console.WriteLine("Read failure"); } else { o.OnNext(scadaDB); o.OnCompleted(); } return(Disposable.Empty); }); // FAULT DATA var readFaultDBObservable = Observable.Create <Alarms_for_SCADA>(o => { var result = faultDB.ReadFromDB(plc, faultDBNumber); if (result != 0) { o.OnError(new Exception("Could not read from DB")); Console.WriteLine("Read failure"); } else { o.OnNext(faultDB); o.OnCompleted(); } return(Disposable.Empty); }); var combinedDBObservable = Observable.Zip(readDBObservable, readFaultDBObservable, (beamdb, faultdb) => (beamdb, faultdb)); var observable = Observable.Create <(Beams_for_SCADA, Alarms_for_SCADA)>(o => { Console.WriteLine($"Attempting to connect to PLC on ip={plc.IPAddress}, rack={plc.Rack}, slot={plc.Slot}"); if (plc.Connect() != 0) { Console.WriteLine("Failed to connect"); o.OnError(new Exception("Failed to connect to PLC")); } else { Console.WriteLine("Successfully Connected"); o.OnCompleted(); } return(Disposable.Empty); }) .Concat( Observable.Interval(TimeSpan.FromSeconds(timeBetweenReads)) .Zip(combinedDBObservable, (interval, dbs) => dbs).Repeat() ); var disposable = observable .ObserveOn(System.Reactive.Concurrency.TaskPoolScheduler.Default) .RetryWhen(errors => errors.SelectMany(Observable.Timer(TimeSpan.FromSeconds(timeBetweenRetries)))) .Subscribe(dbs => { var(beamdb, faultdb) = dbs; //=============================================================================== // THIS SECTION OF CODE WILL REPEAT Console.WriteLine("\n" + $"Reading from PLC Beam / Fault DataBase on {DateTime.Now}"); Console.WriteLine(beamdb); WriteToBeamsSqlDB(cnn, sql, beamdb); WriteToAlarmsSqlDB(cnn, sql, faultdb); Console.WriteLine("\n" + "---------------------------------------------------------" + "\n"); }); // ================================================= // Prepare to exit // ================================================= Console.WriteLine("Press any key to exit"); Console.ReadKey(); disposable.Dispose(); Console.WriteLine("Disconnecting"); plc.Disconnect(); cnn.Close(); }
static void WriteToBeamsSqlDB(SqlConnection cnn, sqlObjects sql, Beams_for_SCADA Beamsdb) { //The 'SQLCommand' is a class defined within C#. //This class defines objects which are used to perform SQL operations against a database. //This object will hold the SQL command which will run against our SQL Server database. SqlCommand command; //DataReader object is used to get all the data returned by an SQL query. //We can then read all the table rows one by one using the data reader. SqlDataReader dataReader; //Data from PLC int currentBeamPointer = Beamsdb.Beams.Value.Beam_Pointer.Value - 1; int currentBeamID = Beamsdb.Beams.Value.Beam[currentBeamPointer].Value.Beam_Parameters.Value.ID.Value; DateTime currentBeamDateStamp = Beamsdb.Beams.Value.Beam[currentBeamPointer].Value.Beam_Processing_Complete_Timestamp.Value; long currentBeamDateStampTicks = currentBeamDateStamp.Ticks; //Data from last entry in the database int lastDBEntryBeamPointer = 999; //can never be 999 as the buffer can fit 200 entires only bool lastDBEntryBeamPointerNULL = false, lastDBEntryBeamIDNULL = false, lastDBEntryBeamDateStampNULL = false, lastDBEntryBeamDateStampTicksNULL = false, databaseEmpty = false;; int lastDBEntryBeamID = 0; //beam being a physical quantity should not have a ID = 0 DateTime lastDBEntryBeamDateStamp = new DateTime(); long lastDBEntryBeamDateStampTicks = 0; String databaseEmptyQuery = "SELECT Pointer, BeamID, [Beam Processed Timestamp] FROM Beams"; string lastDBEntryQuery = "SELECT Pointer, BeamID, [Beam Processed Timestamp], Ticks FROM Beams where [Beam Processed Timestamp]=(SELECT MAX ([Beam Processed Timestamp]) FROM Beams)"; //Read last entry from Database //Command object, which is used to execute the SQL statement against the database command = new SqlCommand(lastDBEntryQuery, cnn); dataReader = command.ExecuteReader(); while (dataReader.Read()) { if (Convert.ToString(dataReader.GetValue(0)) != "") // check if pointer entry is null, this is for backward compatibility { lastDBEntryBeamPointer = Convert.ToInt16(dataReader.GetValue(0)); } else { lastDBEntryBeamPointerNULL = true; } if (Convert.ToString(dataReader.GetValue(1)) != "") // check if beamID is Null { lastDBEntryBeamID = Convert.ToInt16(dataReader.GetValue(1)); } else { lastDBEntryBeamIDNULL = true; } if (Convert.ToString(dataReader.GetValue(2)) != "") // check if Datetime is Null, { lastDBEntryBeamDateStamp = Convert.ToDateTime(dataReader.GetValue(2)); } else { lastDBEntryBeamDateStampNULL = true; } if (Convert.ToString(dataReader.GetValue(3)) != "") // check if Datetime is Null, { lastDBEntryBeamDateStampTicks = Convert.ToInt64(dataReader.GetValue(3)); } else { lastDBEntryBeamDateStampTicksNULL = true; } } ; //Dispose of all temp objects created dataReader.Close(); command.Dispose(); //Check to see if Database is empty or new //Command object, which is used to execute the SQL statement against the database command = new SqlCommand(databaseEmptyQuery, cnn); dataReader = command.ExecuteReader(); //dataReader.Read() will return a False if no entries were found in database if (!dataReader.Read()) { databaseEmpty = true; } ; //Dispose of all temp objects created dataReader.Close(); command.Dispose(); //If database is empty //Case 1 if (databaseEmpty) { // Write everything from PLC buffer to DB except for anything with beamID= 0 foreach (var Beams in Beamsdb.Beams.Value.Beam) { sql.writeBeamToDB(Beams.Value, command, currentBeamPointer, cnn); /*if (Beams.Value.Beam_Parameters.Value.ID.Value != 0) * { * Console.WriteLine("Writing Beam with ID:{0} to the database.", Beams.Value.Beam_Parameters.Value.ID.Value); * //command is use to perform read and write operations in the database * //Command object, which is used to execute the SQL statement against the database * command = new SqlCommand(sql.SqlQueryInsertIntoBeam(Beams.Value, currentBeamPointer), cnn); * //DataAdapter object is used to perform specific SQL operations such as insert, delete and update commands * SqlDataAdapter adapter = new SqlDataAdapter(); * //we now associate the insert SQL command to our adapter * adapter.InsertCommand = new SqlCommand(sql.SqlQueryInsertIntoBeam(Beams.Value, currentBeamPointer), cnn); * //then issue the ExecuteNonQuery method which is used to execute the Insert statement against our database * adapter.InsertCommand.ExecuteNonQuery(); * //Dispose of all temp objects created * adapter.Dispose(); * command.Dispose(); * }*/ } } // backward compatibility, when the last entry in Db has no pointer information //Case 2 if (lastDBEntryBeamPointerNULL) { // iterate thru plc buffer, search for entries not in database and write them to Database foreach (var Beams in Beamsdb.Beams.Value.Beam) { string BeamDateStampToSearch = Beams.Value.Beam_Processing_Complete_Timestamp.Value.ToString("s", new CultureInfo("en-US")); string searchQuery = "SELECT [Beam Processed Timestamp] FROM Beams where [Beam Processed Timestamp]='" + BeamDateStampToSearch + "'"; //string searchQuery = "SELECT [Beam Processed Timestamp] FROM Beams where [Beam Processed Timestamp]='2019-09-23T12:41:00'"; //string searchQuery = "SELECT[Beam Processed Timestamp] FROM Beams where[BeamID] = 15"; command = new SqlCommand(searchQuery, cnn); dataReader = command.ExecuteReader(); //dataReader.Read() will return a False if no entries were found in database if (!dataReader.Read()) { sql.writeBeamToDB(Beams.Value, command, currentBeamPointer, cnn); } //Dispose of all temp objects created dataReader.Close(); command.Dispose(); } } //Check where the pointer from last DB entry sits with respect to current pointer in PLC buffer //Case 3 if (currentBeamPointer == lastDBEntryBeamPointer) { if (currentBeamDateStampTicks < lastDBEntryBeamDateStampTicks) { // PLC datetime configuration error Console.WriteLine($" ....PLC datetime settings error! PLC datestamp shouldn't be older than the last record in database, entry rejected!"); } if ((currentBeamID == lastDBEntryBeamID) && (currentBeamDateStampTicks == lastDBEntryBeamDateStampTicks)) { // do nothing Console.WriteLine($" ....new Beam ID Not Detected, no entry made in Database."); } if (currentBeamDateStampTicks > lastDBEntryBeamDateStampTicks) { // means PLC filled up the buffer and the buffer rolled over all the way back to the current pointer // Write everything from PLC buffer to DB except for anything with beamID= 0 foreach (var Beams in Beamsdb.Beams.Value.Beam) { sql.writeBeamToDB(Beams.Value, command, currentBeamPointer, cnn); } } } //Check where the pointer from last DB entry sits with respect to current pointer in PLC buffer //Case 4 if (currentBeamPointer > lastDBEntryBeamPointer) { if (currentBeamDateStampTicks < lastDBEntryBeamDateStampTicks) { // PLC datetime configuration error Console.WriteLine($" ....PLC datetime settings error! PLC datestamp shouldn't be older than the last record in database, entry rejected!"); } if (currentBeamDateStampTicks == lastDBEntryBeamDateStampTicks) { // PLC datetime configuration error Console.WriteLine($" ....PLC datetime settings error! PLC datestamp should not be the same as the last recorded entry in database if the pointer has moved, entry rejected!"); } // compare last DB entry with the same location on PLC buffer to determine roll overs if (Beamsdb.Beams.Value.Beam[lastDBEntryBeamPointer].Value.Beam_Processing_Complete_Timestamp.Value.Ticks == lastDBEntryBeamDateStampTicks) { // means PLC buffer is ahead of the Database and the PLC buffer has not rolled over all the way to the current PLC pointer // Write block of entries from the last DB entry pointer till the current pointer on PLC buffer to DB except for anything with beamID= 0 for (int i = lastDBEntryBeamPointer + 1; i <= currentBeamPointer; i++) { sql.writeBeamToDB(Beamsdb.Beams.Value.Beam[i].Value, command, currentBeamPointer, cnn); /*if (Beamsdb.Beams.Value.Beam[i].Value.Beam_Parameters.Value.ID.Value != 0) * { * Console.WriteLine("Writing Beam with ID:{0} to the database.", Beamsdb.Beams.Value.Beam[i].Value.Beam_Parameters.Value.ID.Value); * //command is use to perform read and write operations in the database * //Command object, which is used to execute the SQL statement against the database * command = new SqlCommand(sql.SqlQueryInsertIntoBeams(Beamsdb.Beams.Value.Beam[i].Value, currentBeamPointer), cnn); * //DataAdapter object is used to perform specific SQL operations such as insert, delete and update commands * SqlDataAdapter adapter = new SqlDataAdapter(); * //we now associate the insert SQL command to our adapter * adapter.InsertCommand = new SqlCommand(sql.SqlQueryInsertIntoBeams(Beamsdb.Beams.Value.Beam[i].Value, currentBeamPointer), cnn); * //then issue the ExecuteNonQuery method which is used to execute the Insert statement against our database * adapter.InsertCommand.ExecuteNonQuery(); * //Dispose of all temp objects created * adapter.Dispose(); * command.Dispose(); * }*/ } } // compare last DB entry with the same location on PLC buffer to determine roll overs if (Beamsdb.Beams.Value.Beam[lastDBEntryBeamPointer].Value.Beam_Processing_Complete_Timestamp.Value.Ticks > lastDBEntryBeamDateStampTicks) { // means PLC filled up the buffer and the buffer rolled over all the way back to the current pointer // Write everything from PLC buffer to DB except for anything with beamID= 0 foreach (var Beams in Beamsdb.Beams.Value.Beam) { sql.writeBeamToDB(Beams.Value, command, currentBeamPointer, cnn); } } } //Check where the pointer from last DB entry sits with respect to current pointer in PLC buffer //Case 5 if (currentBeamPointer < lastDBEntryBeamPointer) { if (currentBeamDateStampTicks < lastDBEntryBeamDateStampTicks) { // PLC datetime configuration error Console.WriteLine($" ....PLC datetime settings error! PLC datestamp shouldn't be older than the last record in database, entry rejected!"); } if (currentBeamDateStampTicks == lastDBEntryBeamDateStampTicks) { // PLC datetime configuration error Console.WriteLine($" ....PLC datetime settings error! PLC datestamp should not be the same as the last recorded entry in database if the pointer has moved, entry rejected!"); } // compare last DB entry with the same location on PLC buffer to determine roll overs if (Beamsdb.Beams.Value.Beam[lastDBEntryBeamPointer].Value.Beam_Processing_Complete_Timestamp.Value.Ticks == lastDBEntryBeamDateStampTicks) { // means PLC buffer filled up and now the PLC buffer pointers sits above the pointer from DB entry // PLC buffer reached pointer 200 and reset to 0 before incrementing again // Write block of entries from the last DB entry pointer till pointer 200 from PLC buffer and then again from 0 till current pointer from PLC buffer for (int i = lastDBEntryBeamPointer + 1; i <= 200; i++) { sql.writeBeamToDB(Beamsdb.Beams.Value.Beam[i].Value, command, currentBeamPointer, cnn); } for (int i = 0 + 1; i <= currentBeamPointer; i++) { sql.writeBeamToDB(Beamsdb.Beams.Value.Beam[i].Value, command, currentBeamPointer, cnn); } } // compare last DB entry with the same location on PLC buffer to determine roll overs if (Beamsdb.Beams.Value.Beam[lastDBEntryBeamPointer].Value.Beam_Processing_Complete_Timestamp.Value.Ticks > lastDBEntryBeamDateStampTicks) { // means PLC filled up the buffer and the buffer rolled over all the way back to the current pointer // Write everything from PLC buffer to DB except for anything with beamID= 0 foreach (var Beams in Beamsdb.Beams.Value.Beam) { sql.writeBeamToDB(Beams.Value, command, currentBeamPointer, cnn); } } } }