public void manDataEntry(string from, string to, string description) { dr = dh.getReader("SELECT descript FROM ManualData WHERE descript IS NOT NULL AND fromtime = '" + from + "'"); while (dr.Read()) { sqldescript = (string)dr["descript"]; } if (sqldescript.Length > 0) { sqldescript += ", " + description; try { dh.executeSql("Update ManualData set descript = '" + sqldescript + "' WHERE fromtime = '" + from + "'"); dh.executeSql("Update DetailedReport set mandata = '" + sqldescript + "' WHERE starttime = '" + from + "'"); } catch (SqlException se) { using (System.IO.StreamWriter file = new System.IO.StreamWriter(@"C:\Users\KE_PLCData\Documents\ReportErrorLogging\ErrorLog.txt", true)) { file.WriteLine("Error occurred updating database: " + DateTime.Now + " \r\n" + se + "\r\n"); } MessageBox.Show("There has been an error updating the Downtime Event!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } MessageBox.Show("Manual Data Entry of time range:\nFROM: " + from + " TO: " + to + "\nDescription: " + description, "Successful", MessageBoxButtons.OK, MessageBoxIcon.Information); } else { try { dh.executeSql("Insert INTO ManualData (fromtime, totime, descript) values ('" + from + "', '" + to + "', '" + description + "')"); dh.executeSql("Update DetailedReport set mandata = '" + description + "' WHERE starttime = '" + from + "'"); } catch (SqlException ee) { using (System.IO.StreamWriter file = new System.IO.StreamWriter(@"C:\Users\KE_PLCData\Documents\ReportErrorLogging\ErrorLog.txt", true)) { file.WriteLine("Error occurred updating database: " + DateTime.Now + " \r\n" + ee + "\r\n"); } MessageBox.Show("There has been an error updating the Downtime Event!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } MessageBox.Show("Manual Data Entry of time range:\nFROM: " + from + " TO: " + to + "\nDescription: " + description, "Successful", MessageBoxButtons.OK, MessageBoxIcon.Information); } }
public Manual_Data1() { InitializeComponent(); //using the sqlDataHandler class for sql connections and commands dh = new SQLDataHandler(); //sql data reader to read the start time from the configuration settings dr = dh.getReader("SELECT currshiftstarttime From ConfigurationSettings Where plantname = 'Kanawha Eagle'"); //read while there are rows while (dr.Read()) { //store the datetime from sql stime = (String)dr["currshiftstarttime"].ToString(); } //split the date time on the colon string[] starttimehr = stime.Split(':'); //store the hour as an integer int starthr = Convert.ToInt32(starttimehr[0]); loadDDL(starthr); //display in message box to verify correct number //this code can be deleted once this part of the code is finished //MessageBox.Show(Convert.ToString(starthr)); //This was Garretts code to load the drop down lists //Chris removed one line of code for the second drop down list //var item = DateTime.Today.AddHours(0); // 0:00:00 //while (item <= DateTime.Today.AddHours(24)) // 24:00:00 //12:00 AM //{ // ddlTimeRange.Items.Add(item.TimeOfDay.ToString(@"hh\:mm")); // item = item.AddMinutes(30); //} }
public Edit_Scheduled_Maintenance() { InitializeComponent(); //using the sqlDataHandler class for sql connections and commands dh = new SQLDataHandler(); //sql data reader to read the start time from the configuration settings dr = dh.getReader("SELECT currshiftstarttime From ConfigurationSettings Where plantname = 'Kanawha Eagle'"); //read while there are rows while (dr.Read()) { //store the datetime from sql stime = (String)dr["currshiftstarttime"].ToString(); } //split the date time on the colon string[] starttimehr = stime.Split(':'); //store the hour as an integer int starthr = Convert.ToInt32(starttimehr[0]); loadDDL(starthr); }
public void downtime(int bitCheck, Boolean statBool, DataGridView dgv) { try { //Checking to see if the bit has changed from a 1 to a 0. If the bit is 0 and the //boolean is true that means the bit has just changed. If the boolean had been false //the bit had already been read as a 0 if (bitCheck.Equals(0) && statBool.Equals(true)) { try { //setting the boolean value to false on first read of the bit being 0 setdowntimeMonitor(false); dh = new SQLDataHandler(); //Inserting the downtime date and time into the database dh.executeSql("Insert into Downtime(plantName, downtime, uptime) values ('Kanawha Eagle', GETDATE(), NULL)"); }catch (SqlException se) { using (System.IO.StreamWriter file = new System.IO.StreamWriter(@"C:\Users\KE_PLCData\Documents\ReportErrorLogging\ErrorLog.txt", true)) { file.WriteLine("Error occurred at Downtime Monitoring Downtime insert: " + DateTime.Now + " \r\n" + se + "\r\n"); } return; } } //Checking to see if the bit has changed back to a 1. If the bit is 1 and the //boolean value is false that means the bit has just changed. If the boolean had been true //the bit had already been read as a 1 if (bitCheck.Equals(1) && statBool.Equals(false)) { //setting the boolean value back to true setdowntimeMonitor(true); //Instantiating a new data handler dh = new SQLDataHandler(); try { //This finds the last auto incremented id in the downtime table so that we //know what tuple to update with the uptime date and time dr = dh.getReader("SELECT TOP 1 id FROM Downtime Order By id DESC"); //Reading the value of the id from the select statement above dr.Read(); if (dr.HasRows) { Int32 idlocation = dr.GetInt32(0); //Updating the correct tuple that corresponds to the downtime date and time dh.executeSql("Update Downtime set uptime = GETDATE() WHERE id = '" + idlocation + "'"); //This select statement calculates the minutes that passed between the downtime date and time and the uptime date and time dr = dh.getReader("Select DATEDIFF(second, downtime, uptime) From Downtime Where id = '" + idlocation + "'"); dr.Read(); Int32 downsec = dr.GetInt32(0); double downmins = (Convert.ToDouble(downsec) / 60); //Updating the tuple with the downtime minutes calculated above using the id that we found above dh.executeSql("Update Downtime set downmins = '" + downmins + "' WHERE id = '" + idlocation + "'"); } }catch (SqlException ex) { using (System.IO.StreamWriter file = new System.IO.StreamWriter(@"C:\Users\KE_PLCData\Documents\ReportErrorLogging\ErrorLog.txt", true)) { file.WriteLine("Error occurred at Downtime Monitoring Update: " + DateTime.Now + " \r\n" + ex + "\r\n"); } return; } } }catch (Exception ee) { using (System.IO.StreamWriter file = new System.IO.StreamWriter(@"C:\Users\KE_PLCData\Documents\ReportErrorLogging\ErrorLog.txt", true)) { file.WriteLine("Error occurred at Downtime Monitoring overall try catch: " + DateTime.Now + " \r\n" + ee + "\r\n"); } return; } }
public void editSchMins(string fromTime, string from, int schMinutes) { //Get previous day for use during midnight changes DateTime previous = DateTime.Today; DateTime previousday = previous.AddDays(-1); string prev = Convert.ToString(previousday); string[] prevday = prev.Split(' '); //Get the time string now = DateTime.Now.ToShortTimeString(); //Split time to use as comparison string[] timesplit = now.Split(' '); string time = timesplit[0]; string[] hrmin = time.Split(':'); int hr = Convert.ToInt32(hrmin[0]); int min = Convert.ToInt32(hrmin[1]); //get the shift start time dh = new SQLDataHandler(); dr = dh.getReader("SELECT currshiftstarttime, currnumshifts from ConfigurationSettings WHERE plantname = 'Kanawha Eagle'"); dr.Read(); //where shift nums equals 2 string stime = (string)dr["currshiftstarttime"].ToString(); int numshifts = (int)dr["currnumshifts"]; string[] endtime = stime.Split(':'); int edtm = Convert.ToInt32(endtime[0]); //if it is after midnight then the pm time ranges need updated for the previous day //which is what will show on the dashboard until first shift starts for the current day if (hr == 12 && timesplit[1].Equals("AM") || hr < edtm && timesplit[1].Equals("AM") && ampm[1].Equals("PM")) { try { dh.executeSql("Update DetailedReport set schrunmins = " + schMinutes + " WHERE starttime = '" + prevday[0] + " " + fromTime + "'"); } catch (SqlException se) { using (System.IO.StreamWriter file = new System.IO.StreamWriter(@"C:\Users\KE_PLCData\Documents\ReportErrorLogging\ErrorLog.txt", true)) { file.WriteLine("Error occurred updating database: " + DateTime.Now + " \r\n" + se + "\r\n"); } MessageBox.Show("There has been an error updating the scheduled run minutes!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } MessageBox.Show("Scheduled Minutes Changed To: " + schMinutes, " Successfully", MessageBoxButtons.OK, MessageBoxIcon.Information); } else { try { dh.executeSql("Update DetailedReport set schrunmins = " + schMinutes + " WHERE starttime = '" + from + "'"); } catch (SqlException se) { using (System.IO.StreamWriter file = new System.IO.StreamWriter(@"C:\Users\KE_PLCData\Documents\ReportErrorLogging\ErrorLog.txt", true)) { file.WriteLine("Error occurred updating database: " + DateTime.Now + " \r\n" + se + "\r\n"); } MessageBox.Show("There has been an error updating the scheduled run minutes!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } MessageBox.Show("Scheduled Minutes Changed To: " + schMinutes, " Successfully", MessageBoxButtons.OK, MessageBoxIcon.Information); } }
public void setTimeRanges() { dh = new SQLDataHandler(); dr = dh.getReader("Select schrunmins, currshiftstarttime, newshiftstarttime, datetotakeeffect FROM ConfigurationSettings Where plantname = 'Kanawha Eagle';"); dr.Read(); int schrunmins = (int)dr["schrunmins"]; String stime = dr["currshiftstarttime"].ToString(); string newstime = dr["newshiftstarttime"].ToString(); string datetochange = dr["datetotakeeffect"].ToString(); string[] changedate = datetochange.Split(' '); string firstshiftstarttime = stime.Substring(1, 4) + " AM"; string[] newshiftsttime = newstime.Split(' '); string nshiftstarttime = newshiftsttime[0] + " AM"; string endingtime = ""; //get todays date plus add the shift start time to the date and check if any rows exist string checktime = DateTime.Today.ToShortDateString() + " " + firstshiftstarttime; string updatedtime = DateTime.Today.ToShortDateString() + " " + nshiftstarttime; //todays date equals datetotakeeffect from configuration settings table we need to //add the new time ranges to the detailed report and start end nums tables DateTime sqldatetochange = Convert.ToDateTime(datetochange); string newsqlchanged = Convert.ToString(sqldatetochange); string[] newdatechanged = newsqlchanged.Split(' '); if (DateTime.Today.ToShortDateString().Equals(newdatechanged[0])) { //update end time on last timerange in detailedreport and startendnums to new shiftstarttime dr = dh.getReader("SELECT TOP 1 endtime FROM DetailedReport Order By endtime DESC"); dr.Read(); //if the starting numbers were not null do calculations if (!DBNull.Value.Equals(dr["endtime"])) { endingtime = dr["endtime"].ToString(); dh.executeSql("Update DetailedReport set endtime = '" + updatedtime + "', datetotakeeffect = NULL WHERE endtime = '" + endingtime + "'"); } dr = dh.getReader("SELECT TOP 1 totime FROM StartEndNums Order By totime DESC"); dr.Read(); //if the starting numbers were not null do calculations if (!DBNull.Value.Equals(dr["totime"])) { endingtime = dr["totime"].ToString(); } dh.executeSql("Update StartEndNums set totime = '" + updatedtime + "' WHERE totime = '" + endingtime + "'"); //take number of hours in day and divide by number of shifts from ConfigurationSettings table //this is how many hours we will add to find the end time string[] time = stime.Split(':'); int stTime = Convert.ToInt32(time[0]); string[] newtime = newstime.Split(':'); int newstTime = Convert.ToInt32(newtime[0]); //converting shift start time string to datetime data type DateTime time1 = Convert.ToDateTime(nshiftstarttime); DateTime time2 = time1.AddHours(2); DateTime time3 = Convert.ToDateTime(nshiftstarttime); DateTime time4 = time1.AddHours(2); //get the shift start time dr = dh.getReader("SELECT currshiftstarttime from ConfigurationSettings WHERE plantname = 'Kanawha Eagle'"); dr.Read(); string shiftstart = (string)dr["currshiftstarttime"].ToString(); //Get the time string now = DateTime.Now.ToShortTimeString(); //Split time to use as comparison string[] timesplit = now.Split(' '); string timerangenums = timesplit[0]; string[] hrmin = timerangenums.Split(':'); int hr = Convert.ToInt32(hrmin[0]); int min = Convert.ToInt32(hrmin[1]); //loop through 12 times for each 2 hr time range for a 24 hour period //for the DetailedReport Table these will be updated as data is read from PLC for (int i = 0; i < 12; i++) { string converttime = Convert.ToString(time1); dh.executeSql("IF NOT EXISTS (Select starttime From DetailedReport WHERE starttime = '" + converttime + "')" + " Insert INTO DetailedReport (starttime, endtime, schrunmins) VALUES ('" + time1 + "', '" + time2 + "', " + schrunmins + ")"); string converttime1 = Convert.ToString(time1); checktime = DateTime.Today.ToShortDateString() + " " + converttime1; time1 = time2; time2 = time2.AddHours(2); } //loop through the time ranges to insert in the StartEndNums Table //these will be used for calculations for displaying detailed report //on the desktop app as well as the network application for (int i = 0; i < 12; i++) { string converttime = Convert.ToString(time3); dh.executeSql("IF NOT EXISTS (Select fromtime From StartEndNums WHERE fromtime = '" + converttime + "')" + " Insert INTO StartEndNums (fromtime, totime) VALUES ('" + time3 + "', '" + time4 + "')"); string converttime1 = Convert.ToString(time3); checktime = DateTime.Today.ToShortDateString() + " " + converttime1; time3 = time4; time4 = time4.AddHours(2); } } else { //if rows don't exist then insert the time ranges from todays shift start time until tomorrows last shift end time //this is done incase the program isn't running at the beginning of the shift. We still need to add a start number as soon //as the program is back up and running as well as the time ranges used for the detailed report //converting shift start time string to datetime data type DateTime time1 = Convert.ToDateTime(firstshiftstarttime); DateTime time2 = time1.AddHours(2); DateTime time3 = Convert.ToDateTime(firstshiftstarttime); DateTime time4 = time1.AddHours(2); //get the shift start time dr = dh.getReader("SELECT currshiftstarttime from ConfigurationSettings WHERE plantname = 'Kanawha Eagle'"); dr.Read(); string shiftstart = (string)dr["currshiftstarttime"].ToString(); //Get the time string now = DateTime.Now.ToShortTimeString(); //Split time to use as comparison string[] timesplit = now.Split(' '); string timerangenums = timesplit[0]; string[] hrmin = timerangenums.Split(':'); int hr = Convert.ToInt32(hrmin[0]); int min = Convert.ToInt32(hrmin[1]); //loop through 12 times for each 2 hr time range for a 24 hour period //for the DetailedReport Table these will be updated as data is read from PLC for (int i = 0; i < 12; i++) { string converttime = Convert.ToString(time1); dh.executeSql("IF NOT EXISTS (Select starttime From DetailedReport WHERE starttime = '" + converttime + "')" + " Insert INTO DetailedReport (starttime, endtime, schrunmins) VALUES ('" + time1 + "', '" + time2 + "', " + schrunmins + ")"); string converttime1 = Convert.ToString(time1); checktime = DateTime.Today.ToShortDateString() + " " + converttime1; time1 = time2; time2 = time2.AddHours(2); } //loop through the time ranges to insert in the StartEndNums Table //these will be used for calculations for displaying detailed report //on the desktop app as well as the network application for (int i = 0; i < 12; i++) { string converttime = Convert.ToString(time3); dh.executeSql("IF NOT EXISTS (Select fromtime From StartEndNums WHERE fromtime = '" + converttime + "')" + " Insert INTO StartEndNums (fromtime, totime) VALUES ('" + time3 + "', '" + time4 + "')"); string converttime1 = Convert.ToString(time3); checktime = DateTime.Today.ToShortDateString() + " " + converttime1; time3 = time4; time4 = time4.AddHours(2); } } }