/// <summary>
/// Connects to Camstar with the username and password provided.
/// </summary>
private void CamstarConnect(string UserName, string Password)
{
MainForm.DiagnosticOut("Connecting Camstar", 2);
string DataReceived;
try
{
string PacketString = "<__InSite __encryption=\"2\" __version=\"1.1\"><__session><__connect><user><__name>" + UserName + "</__name></user><password __encrypted=\"no\">" + Password + "</password></__connect></__session></__InSite>";
var connection = new ServerConnection();
var connected = connection.Connect(ConfigurationManager.AppSettings["CamstarIP"], Convert.ToInt32(ConfigurationManager.AppSettings["CamstarPort"])); // try connecting
if (!connected)
{
return; // return nothing if cant connect
}
connection.Send(PacketString.ToString()); // send data
connection.Receive(out var result); // reviece message from server, and store into variable
connection.Disconnect(); // Close connection
try
{
DataReceived = XDocument.Parse(result).ToString(); // format recieved message into xml
}
catch
{
DataReceived = result;
}
MainForm.DiagnosticOut(DataReceived, 5);
}
catch (Exception ex) { MainForm.DiagnosticOut(ex.ToString(), 1); }
}
/// <summary>
/// Called whenever a new machine is detected
/// </summary>
public void NewMachinePacket(string message)
{
string jsonString = message.Substring(7, message.Length - 7);//grab json data from the end.
JObject receivedPacket = JsonConvert.DeserializeObject(jsonString) as JObject;
string machineName = receivedPacket["Machine"].ToString();
string Line = receivedPacket["Line"].ToString();
string Theo = receivedPacket["Theo"].ToString();
int snp_ID = Convert.ToInt32((byte)message[2]);
string Errors = "[";
try
{
string ErrorString = receivedPacket["Errors"].ToString();
string[] ErrorArray = ErrorString.Split(',');
foreach (string error in ErrorArray)//foreach error add it to the Errors Section
{
Errors += error + "] [bit] NOT NULL, [";
}
Errors = Errors.Substring(0, Errors.Length - 1);
}
catch//no errors are being recorded
{
Errors = "";
}
try //try loop in case command fails.
{
StringBuilder sqlStringBuilder = new StringBuilder();
sqlStringBuilder.Append(" USE [Pac-LiteDb ] ");
sqlStringBuilder.Append(" CREATE TABLE [dbo].[" + machineName + "ShortTimeStatistics](");
sqlStringBuilder.Append(" [MachineID] [int] NULL, [Good] [bit] NULL, [Bad] [bit] NULL, [Empty] [bit] NULL, [Attempt] [bit] NULL, "+ Errors + " [Other] [bit] NULL, [HeadNumber] [int] NULL ");
sqlStringBuilder.Append(" ) ON [PRIMARY] ");
sqlStringBuilder.Append(" CREATE TABLE [dbo].[" + machineName + "](");
sqlStringBuilder.Append(" [EntryID] [int] IDENTITY(1,1) NOT NULL, [MachineID] [int] NULL, [Good] [int] NULL, [Bad] [int] NULL, [Empty] [int] NULL, [Indexes] [int] NULL, [NAED] [varchar](20) NULL, [UOM] [varchar](10) NULL, [Time] [datetime] NULL) ON [PRIMARY] ");
sqlStringBuilder.Append(" CREATE TABLE [dbo].[" + machineName + "DownTimes](");
sqlStringBuilder.Append(" [Time] [datetime] NULL, [MReason] [varchar](255) NULL, [UReason] [varchar](255) NULL, [NAED] [varchar](20) NULL, [MachineID] [int] NULL, [Status] [int] NULL) ON [PRIMARY]; ");
sqlStringBuilder.Append(" insert into MachineInfoTable (MachineName, Line, SNPID , Theo) values( @machine , @Line , @SNPID , @Theo);");
string SQLString = sqlStringBuilder.ToString();//convert to string
using (SqlCommand command = new SqlCommand(SQLString, MainForm.ENGDBConnection))
{
command.Parameters.AddWithValue("@machine", machineName);
command.Parameters.AddWithValue("@Line", Line);
command.Parameters.AddWithValue("@SNPID", snp_ID);
command.Parameters.AddWithValue("@Theo", Theo);
command.ExecuteNonQuery();// execute the command returning number of rows affected
}
}
catch (Exception ex)
{
if (ex.Message.Contains("ExecuteNonQuery requires an open and available Connection."))
{
MainForm.ReastablishSQL(SQLShortTimeStatisticPacket, message);
}
MainForm.DiagnosticOut(ex.ToString(), 1);
}
}
/// <summary>
/// Packet Sent every index for the EMP system. Simply insert into SQL for recording ( and grab a time stamp if missing)
/// </summary>
public void IndexPacket(string message)
{
MainForm.DiagnosticOut("EMPIndexPacketReceived!", 2);
string SQLString = "";
try //try loop in case command fails.
{
string jsonString = message.Substring(7, message.Length - 7); //grab json data from the end.
JObject receivedPacket = JsonConvert.DeserializeObject(jsonString) as JObject;
StringBuilder sqlStringBuilder = new StringBuilder();
sqlStringBuilder.Append("INSERT INTO EMPTable (");
IList <string> keys = receivedPacket.Properties().Select(p => p.Name).ToList();//gets list of all keys in json object
string keySection = "";
string valueSection = "";
bool MissingStamp = true;
foreach (string key in keys) //foreach key
{
keySection += key + ", "; //Make a key
valueSection += "@" + key + ", "; //and value Reference to be replaced later
if (key == "TimeStamp")
{
MissingStamp = false;
}
}
if (MissingStamp)
{
keySection += "TimeStamp"; //Make a Time key
valueSection += "@TimeStamp"; //and value Reference to be replaced later
}
else
{
valueSection = valueSection.Substring(0, valueSection.Length - 2);
keySection = keySection.Substring(0, keySection.Length - 2);
}
sqlStringBuilder.Append(keySection + " )");
sqlStringBuilder.Append("Values ( " + valueSection + " );"); //append both to the command string
SQLString = sqlStringBuilder.ToString(); //convert to string
using (SqlCommand command = new SqlCommand(SQLString, MainForm.ENGDBConnection))
{
foreach (string key in keys)//foreach key
{
switch (key)
{
case "Temperature":
command.Parameters.AddWithValue("@" + key, Convert.ToDecimal(receivedPacket[key]));
break;
case "Humidity":
command.Parameters.AddWithValue("@" + key, Convert.ToDecimal(receivedPacket[key].ToString()));
break;
case "FlowRate":
command.Parameters.AddWithValue("@" + key, Convert.ToDecimal((receivedPacket[key])));
break;
case "ChangeOver5Seconds":
command.Parameters.AddWithValue("@" + key, Convert.ToDecimal(receivedPacket[key]));
break;
case "TimeStamp":
if (MissingStamp)
{
command.Parameters.AddWithValue("@" + key, DateTime.Now);
}
else
{
string TimeStamp = receivedPacket[key].ToString();
int year = 2000 + Convert.ToInt32(TimeStamp.Substring(0, 2));
int month = Convert.ToInt32(TimeStamp.Substring(3, 2));
int day = Convert.ToInt32(TimeStamp.Substring(6, 2));
int hour = Convert.ToInt32(TimeStamp.Substring(9, 2));
int minute = Convert.ToInt32(TimeStamp.Substring(12, 2));
int second = Convert.ToInt32(TimeStamp.Substring(15, 2));
command.Parameters.AddWithValue("@" + key, new DateTime(year, month, day, hour, minute, second));
}
break;
case "Location":
command.Parameters.AddWithValue("@" + key, receivedPacket[key].ToString());
break;
default:
break;
}
}
int rowsAffected = command.ExecuteNonQuery(); // execute the command returning number of rows affected
MainForm.DiagnosticOut(rowsAffected + " row(s) inserted", 2); //logit
}
}
catch (Exception ex)
{
if (ex.Message.Contains("ExecuteNonQuery requires an open and available Connection."))
{
MainForm.ReastablishSQL(IndexPacket, message);
}
MainForm.DiagnosticOut(ex.ToString(), 1);
}
}
public void LoggingLevel(string message)
{
try
{
var OldSetting = MainForm.LogggingLevel;
string jsonString = message.Substring(7, message.Length - 7);//grab json data from the end.
JObject receivedPacket = JsonConvert.DeserializeObject(jsonString) as JObject;
MainForm.LogggingLevel = Convert.ToInt32(receivedPacket["LoggingLevel"]);
MainForm.DiagnosticOut("Logging Level Has been set to" + receivedPacket["LoggingLevel"].ToString(), 2);
if (Convert.ToInt32(receivedPacket["IntTimeInSeconds"] ?? 0) != 0)
{
Thread.Sleep(Convert.ToInt32(receivedPacket["IntTimeInSeconds"]) * 1000);//a bit worried about exhuasting the number of threads in the threadpool. However there shouldnt be many threads consumed by control Messages so it should be ok.
MainForm.LogggingLevel = OldSetting;
MainForm.DiagnosticOut("Logging Level Has been set to " + OldSetting.ToString(), 2);
}
}
catch (Exception ex)
{
MainForm.DiagnosticOut(ex.ToString(), 1);
}
}
