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 WriteToAlarmsSqlDB(SqlConnection cnn, sqlObjects sql, Alarms_for_SCADA Alarmsdb)
{
//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 currentAlarmPointer = Alarmsdb.Alarms.Value.Alarm_Pointer.Value - 1;
int currentAlarmID = Alarmsdb.Alarms.Value.Alarm[currentAlarmPointer].Value.Alarm_ID.Value;
DateTime currentAlarmDateStamp = Alarmsdb.Alarms.Value.Alarm[currentAlarmPointer].Value.Alarm_Timestamp.Value;
long currentAlarmDateStampTicks = currentAlarmDateStamp.Ticks;
//Data from last entry in the database
int lastDBEntryAlarmPointer = 999; //can never be 999 as the buffer can fit 200 entires only
bool lastDBEntryAlarmPointerNULL = false, lastDBEntryAlarmIDNULL = false, lastDBEntryAlarmDateStampNULL = false, lastDBEntryAlarmDateStampTicksNULL = false, databaseEmpty = false;;
int lastDBEntryAlarmID = 0; //Alarm being a physical quantity should not have a ID = 0
DateTime lastDBEntryAlarmDateStamp = new DateTime();
long lastDBEntryAlarmDateStampTicks = 0;
String databaseEmptyQuery = "SELECT Pointer, AlarmID, AlarmTimestamp FROM Alarms";
string lastDBEntryQuery = "SELECT Pointer, AlarmID, AlarmTimestamp, Ticks FROM Alarms where AlarmTimestamp=(SELECT MAX (AlarmTimestamp) FROM Alarms)";
//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
{
lastDBEntryAlarmPointer = Convert.ToInt16(dataReader.GetValue(0));
}
else
{
lastDBEntryAlarmPointerNULL = true;
}
if (Convert.ToString(dataReader.GetValue(1)) != "") // check if beamID is Null
{
lastDBEntryAlarmID = Convert.ToInt16(dataReader.GetValue(1));
}
else
{
lastDBEntryAlarmIDNULL = true;
}
if (Convert.ToString(dataReader.GetValue(2)) != "") // check if Datetime is Null,
{
lastDBEntryAlarmDateStamp = Convert.ToDateTime(dataReader.GetValue(2));
}
else
{
lastDBEntryAlarmDateStampNULL = true;
}
if (Convert.ToString(dataReader.GetValue(3)) != "") // check if Datetime is Null,
{
lastDBEntryAlarmDateStampTicks = Convert.ToInt64(dataReader.GetValue(3));
}
else
{
lastDBEntryAlarmDateStampTicksNULL = 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 AlarmID= 0
foreach (var Alarms in Alarmsdb.Alarms.Value.Alarm)
{
sql.writeAlarmToDB(Alarms.Value, command, currentAlarmPointer, cnn);
}
}
// backward compatibility, when the last entry in Db has no pointer information
//Case 2
if (lastDBEntryAlarmPointerNULL)
{
// iterate thru plc buffer, search for entries not in database and write them to Database
foreach (var Alarms in Alarmsdb.Alarms.Value.Alarm)
{
string AlarmDateStampToSearch = Alarms.Value.Alarm_Timestamp.Value.ToString("s", new CultureInfo("en-US"));
string searchQuery = "SELECT AlarmTimestamp FROM Alarms where AlarmTimestamp='" + AlarmDateStampToSearch + "'";
//string searchQuery = "SELECT AlarmTimestamp FROM Alarms where AlarmTimestamp='2019-09-23T12:41:00'";
//string searchQuery = "SELECTAlarmTimestamp FROM Alarms where AlarmID = 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.writeAlarmToDB(Alarms.Value, command, currentAlarmPointer, 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 (currentAlarmPointer == lastDBEntryAlarmPointer)
{
if (currentAlarmDateStampTicks < lastDBEntryAlarmDateStampTicks)
{
// 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 ((currentAlarmID == lastDBEntryAlarmID) && (currentAlarmDateStampTicks == lastDBEntryAlarmDateStampTicks))
{
// do nothing
Console.WriteLine($" ....new Alarm ID Not Detected, no entry made in Database.");
}
if (currentAlarmDateStampTicks > lastDBEntryAlarmDateStampTicks)
{
// 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 AlarmID= 0
foreach (var Alarms in Alarmsdb.Alarms.Value.Alarm)
{
sql.writeAlarmToDB(Alarms.Value, command, currentAlarmPointer, cnn);
}
}
}
//Check where the pointer from last DB entry sits with respect to current pointer in PLC buffer
//Case 4
if (currentAlarmPointer > lastDBEntryAlarmPointer)
{
if (currentAlarmDateStampTicks < lastDBEntryAlarmDateStampTicks)
{
// 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 (currentAlarmDateStampTicks == lastDBEntryAlarmDateStampTicks)
{
// 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 (Alarmsdb.Alarms.Value.Alarm[lastDBEntryAlarmPointer].Value.Alarm_Timestamp.Value.Ticks == lastDBEntryAlarmDateStampTicks)
{
// 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 AlarmID= 0
for (int i = lastDBEntryAlarmPointer + 1; i <= currentAlarmPointer; i++)
{
sql.writeAlarmToDB(Alarmsdb.Alarms.Value.Alarm[i].Value, command, currentAlarmPointer, cnn);
}
}
// compare last DB entry with the same location on PLC buffer to determine roll overs
if (Alarmsdb.Alarms.Value.Alarm[lastDBEntryAlarmPointer].Value.Alarm_Timestamp.Value.Ticks > lastDBEntryAlarmDateStampTicks)
{
// 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 AlarmID= 0
foreach (var Alarms in Alarmsdb.Alarms.Value.Alarm)
{
sql.writeAlarmToDB(Alarms.Value, command, currentAlarmPointer, cnn);
}
}
}
//Check where the pointer from last DB entry sits with respect to current pointer in PLC buffer
//Case 5
if (currentAlarmPointer < lastDBEntryAlarmPointer)
{
if (currentAlarmDateStampTicks < lastDBEntryAlarmDateStampTicks)
{
// 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 (currentAlarmDateStampTicks == lastDBEntryAlarmDateStampTicks)
{
// 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 (Alarmsdb.Alarms.Value.Alarm[lastDBEntryAlarmPointer].Value.Alarm_Timestamp.Value.Ticks == lastDBEntryAlarmDateStampTicks)
{
// 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 = lastDBEntryAlarmPointer + 1; i <= 200; i++)
{
sql.writeAlarmToDB(Alarmsdb.Alarms.Value.Alarm[i].Value, command, currentAlarmPointer, cnn);
}
for (int i = 0 + 1; i <= currentAlarmPointer; i++)
{
sql.writeAlarmToDB(Alarmsdb.Alarms.Value.Alarm[i].Value, command, currentAlarmPointer, cnn);
}
}
// compare last DB entry with the same location on PLC buffer to determine roll overs
if (Alarmsdb.Alarms.Value.Alarm[lastDBEntryAlarmPointer].Value.Alarm_Timestamp.Value.Ticks > lastDBEntryAlarmDateStampTicks)
{
// 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 AlarmID= 0
foreach (var Alarms in Alarmsdb.Alarms.Value.Alarm)
{
sql.writeAlarmToDB(Alarms.Value, command, currentAlarmPointer, cnn);
}
}
}
}
