/// <summary> /// Call an intrastage query for each station where Intrastage is enabled. /// </summary> /// <param name="thisCell"></param> /// <param name="i"></param> /// <param name="j"></param> /// <returns></returns> public override StationData eachStation(StationData thisCell, int i, int j) { //Check if intrastage is enabled (bool) if (thisCell.Intrastage) { List<String> newData = new List<String>(); //Perform the multiple queries myConnection = new SqlConnection("Data Source=BHX4SQ01;Initial Catalog=IS_Main;User ID=is_viewer;Password=is!viewer"); //Try to open the connection, otherwise log the error. try { myConnection.Open(); } catch (Exception e) { ConsolePost newPost = new ConsolePost(); newPost.type = 4; newPost.issue = "Could not connect to Intrastage"; newPost.trace = e.ToString(); Program.updates.Add(newPost); } //try to read the data and parse it appropriately. try { SqlDataReader myReader = null; String thisQuery = queryPartOne + thisCell.Station_Name + queryPartTwo; SqlCommand myCommand = new SqlCommand(thisQuery, myConnection); myReader = myCommand.ExecuteReader(); while (myReader.Read()) { Object[] thisData = new Object[myReader.FieldCount]; myReader.GetValues(thisData); switch (thisData[1].ToString()) { case "Passed": thisData[1] = "#AAD178"; break; case "Error": thisData[1] = "#F7BE64"; break; case "Aborted": thisData[1] = "#6AD2EB"; break; case "Terminated": thisData[1] = "#6AD2EB"; break; case "Failed": thisData[1] = "#EF8A80"; break; default: thisData[1] = "#808080"; break; } newData = thisData.OfType<String>().ToList(); } myReader.Close(); myConnection.Close(); } catch (Exception e) { ConsolePost newPost = new ConsolePost(); newPost.type = 4; newPost.issue = "Could not read data"; newPost.trace = e.ToString(); Program.updates.Add(newPost); } thisCell.cellData = newData; } return thisCell; }
public override List<SiteData> before(List<SiteData> data) { OleDbDataReader reader = null; OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\\\bhx4ns01\\Module D\\CCADSystem\\DAT\\Andon_System.mdb"); //As the database is sometimes in use, we attempt to connect 10 times before giving up int y = 0; while (y < 10) { try { conn.Open(); String connStr = "SELECT Tbl_Rig_IDs.Rig_Identification, Tbl_Countermeasure.Date_Time, Tbl_Rig_Faults.DT_ID, Now() AS Expr1, Tbl_Rig_Faults.DownTime_Type, Tbl_Rig_Faults.Operator, Tbl_Rig_Faults.Concern_Type" + " FROM (Tbl_Rig_Faults LEFT JOIN Tbl_Rig_IDs ON Tbl_Rig_Faults.Rig_ID = Tbl_Rig_IDs.Rig_ID) LEFT JOIN Tbl_Countermeasure ON Tbl_Rig_Faults.DT_ID = Tbl_Countermeasure.Fault_ID" + " WHERE (((Tbl_Countermeasure.Date_Time) Is Null));"; OleDbCommand cmd = new OleDbCommand(connStr, conn); reader = cmd.ExecuteReader(); while (reader.Read()) { //AndonParse is a class which solves a problem with naming consistency, which is not //important to this tutorial if (!(new AndonParse().getIntraValue(reader.GetValue(0).ToString()).Equals(""))) { AndonDump newDump = new AndonDump(); newDump.ID = new AndonParse().getIntraValue(reader.GetValue(0).ToString()); newDump.Countermeasure = reader.GetValue(1).ToString(); newDump.DTID = reader.GetValue(2); newDump.LoadTime = reader.GetValue(3).ToString(); newDump.DowntimeType = reader.GetValue(4).ToString(); newDump.Operator = reader.GetValue(5).ToString(); newDump.ConcernType = reader.GetValue(6).ToString(); //Write to DumpData List to be used in the eachStation() loop DumpData.Add(newDump); } } //Database has connected so we need to exit the loop y = 11; } catch (Exception e) { if (y == 9) { //if the software can connect after 10 attempts, log as an error. ConsolePost newPost = new ConsolePost(); newPost.type = 3; newPost.issue = "Andon Database was in use"; newPost.trace = e.ToString(); Program.updates.Add(newPost); } } y++; } //save the dump files for the JSON API File.WriteAllText(Directory.GetCurrentDirectory() + "\\andonDump.json", JsonConvert.SerializeObject(DumpData, Formatting.Indented)); //Save the dump files for the XML API. FileStream pathData = File.Create(Directory.GetCurrentDirectory() + "\\andonDump.xml"); XmlSerializer x = new XmlSerializer(DumpData.GetType()); x.Serialize(pathData, DumpData); //return origin data because we don't want to edit it. return data; }