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