//this method fills the list that includes the information about the weekly shift table
private void fillWeeklyShiftTable(string org_name)
{
SqlConnection conn = new SqlConnection(getConnectionString());
string sql = "SELECT [Day], [Begin Time], [End Time], [Shift Info], [Worker ID] FROM [Shift Schedule] WHERE [Organization Name] = '" + org_name + "'";
try
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataReader myReader = cmd.ExecuteReader();
if (!myReader.HasRows)
{
return;
}
while (myReader.Read())
{
Shift shiftInWeek = new Shift(null, null, null, null, null, null, null, null);
shiftInWeek.setDay(myReader[0].ToString().Trim());
shiftInWeek.setBegin_Time(myReader[1].ToString().Trim());
shiftInWeek.setEnd_Time(myReader[2].ToString().Trim());
shiftInWeek.setWorker_ID(myReader[4].ToString().Trim());
shiftInWeek.setOrganization(org_name);
weeklyShiftTable.AddShift(shiftInWeek);
}
myReader.Close();
if (weeklyShiftTable.getShiftFromTable(0).getWroker_ID() != null && weeklyShiftTable.getShiftFromTable(0).getWroker_ID() != "NULL")
{
sql = "SELECT [ID], [First Name], [Last Name] FROM [Worker] WHERE [Organization Name] = '" + org_name + "'";
cmd = new SqlCommand(sql, conn);
myReader = cmd.ExecuteReader();
while (myReader.Read())
{
for (int i = 0; i < weeklyShiftTable.tableSize(); i++)
{
if (weeklyShiftTable.getShiftFromTable(i).getWroker_ID().Equals(myReader[0].ToString().Trim()))
{
weeklyShiftTable.getShiftFromTable(i).setName(myReader[1].ToString().Trim() + " " + myReader[2].ToString().Trim());
}
}
}
}
}
catch (System.Data.SqlClient.SqlException ex)
{
string msg = "Insert Error:";
msg += ex.Message;
throw new Exception(msg);
}
finally
{
conn.Close();
}
}
private void fillShiftOptionsTable(string org_name)
{
SqlConnection conn = new SqlConnection(getConnectionString());
string sql = "SELECT [Worker ID], [Day], [Begin Time], [End Time], [Organization Name], [Priority] FROM [Shift Options] WHERE [Organization Name] = '" + org_name + "'";
try
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataReader myReader = cmd.ExecuteReader();
while (myReader.Read())
{
Shift shiftOption = new Shift(null, null, null, null, null, null, null, null);
shiftOption.setWorker_ID(myReader[0].ToString().Trim());
shiftOption.setDay(myReader[1].ToString().Trim());
shiftOption.setBegin_Time(myReader[2].ToString().Trim());
shiftOption.setEnd_Time(myReader[3].ToString().Trim());
shiftOption.setOrganization(myReader[4].ToString().Trim());
shiftOption.setPriority(myReader[5].ToString().Trim());
shiftOptionsTable.AddShift(shiftOption);
}
}
catch (System.Data.SqlClient.SqlException ex)
{
string msg = "Insert Error:";
msg += ex.Message;
throw new Exception(msg);
}
finally
{
conn.Close();
}
}
