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