public List <Staff> searchLecturer(DateTime date, string session, string faculty, string categoryOfInvigilator, bool isFacultyCheckOnly, double totalLoadOfDutyForEach)
{
List <Staff> staffList = new List <Staff>();
List <InvigilationDuty> invigilationDutiesList = new List <InvigilationDuty>();
string strSearch = "";
SqlCommand cmdSearch;
try
{
/*Step 2: Create Sql Search statement and Sql Search Object*/
if (!isFacultyCheckOnly)
{
if (categoryOfInvigilator.Equals("InviAbove2Years"))
{
strSearch = "Select S.name, S.gender, S.isMuslim, S.StaffID, S.title, S.position, S.facultyCode, S.department, S.isTakingSTSPhD, S.typeOfEmploy, S.NoOfSatSession, S.NoAsQuarantineInvi, S.NoAsReliefInvi, S.NoOfExtraSession, S.isChiefInvi, S.isInviAbove2Years From dbo.Staff S, dbo.InvigilationDuty I, dbo.Timeslot T Where S.typeOfEmploy = 'F' and S.isInviAbove2Years = 'Y' and isChiefInvi = 'N' and S.FacultyCode = @Faculty and T.Date = @Date And T.Session != @Session And T.TimeslotID = I.TimeslotID And I.StaffID = S.StaffID Group by S.name, S.gender, S.isMuslim, S.StaffID, S.title, S.position, S.facultyCode, S.department, S.isTakingSTSPhD, S.typeOfEmploy, S.NoOfSatSession, S.NoAsQuarantineInvi, S.NoAsReliefInvi, S.NoOfExtraSession, S.isChiefInvi, S.isInviAbove2Years Having COUNT(InvigilationDutyID) < @TotalLoad";
}
else if (categoryOfInvigilator.Equals("Invigilators"))
{
strSearch = "Select S.name, S.gender, S.isMuslim, S.StaffID, S.title, S.position, S.facultyCode, S.department, S.isTakingSTSPhD, S.typeOfEmploy, S.NoOfSatSession, S.NoAsQuarantineInvi, S.NoAsReliefInvi, S.NoOfExtraSession, S.isChiefInvi, S.isInviAbove2Years From dbo.Staff S, dbo.InvigilationDuty I, dbo.Timeslot T Where S.typeOfEmploy = 'F' and (isInvi = 'Y' or S.isInviAbove2Years = 'Y') and isChiefInvi = 'N' and S.FacultyCode = @Faculty and T.Date = @Date And T.Session != @Session And T.TimeslotID = I.TimeslotID And I.StaffID = S.StaffID Group by S.name, S.gender, S.isMuslim, S.StaffID, S.title, S.position, S.facultyCode, S.department, S.isTakingSTSPhD, S.typeOfEmploy, S.NoOfSatSession, S.NoAsQuarantineInvi, S.NoAsReliefInvi, S.NoOfExtraSession, S.isChiefInvi, S.isInviAbove2Years Having COUNT(InvigilationDutyID) < @TotalLoad";
}
else if (categoryOfInvigilator.Equals("Chief"))
{
strSearch = "Select S.name, S.gender, S.isMuslim, S.StaffID, S.title, S.position, S.facultyCode, S.department, S.isTakingSTSPhD, S.typeOfEmploy, S.NoOfSatSession, S.NoAsQuarantineInvi, S.NoAsReliefInvi, S.NoOfExtraSession, S.isChiefInvi, S.isInviAbove2Years From dbo.Staff S, dbo.InvigilationDuty I, dbo.Timeslot T Where S.typeOfEmploy = 'F' and S.isChiefInvi = 'Y' and S.FacultyCode = @Faculty and T.Date = @Date And T.Session != @Session And T.TimeslotID = I.TimeslotID And I.StaffID = S.StaffID Group by S.name, S.gender, S.isMuslim, S.StaffID, S.title, S.position, S.facultyCode, S.department, S.isTakingSTSPhD, S.typeOfEmploy, S.NoOfSatSession, S.NoAsQuarantineInvi, S.NoAsReliefInvi, S.NoOfExtraSession, S.isChiefInvi, S.isInviAbove2Years Having COUNT(InvigilationDutyID) < @TotalLoad";
}
}
else
{
if (categoryOfInvigilator.Equals("InviAbove2Years"))
{
strSearch = "Select S.name, S.gender, S.isMuslim, S.StaffID, S.title, S.position, S.facultyCode, S.department, S.isTakingSTSPhD, S.typeOfEmploy, S.NoOfSatSession, S.NoAsQuarantineInvi, S.NoAsReliefInvi, S.NoOfExtraSession, S.isChiefInvi, S.isInviAbove2Years From dbo.Staff S, dbo.InvigilationDuty I, dbo.Timeslot T Where S.typeOfEmploy = 'F' and S.isInviAbove2Years = 'Y' and isChiefInvi = 'N' and S.FacultyCode = @Faculty and T.Date = @Date And T.Session = @Session And T.TimeslotID != I.TimeslotID And I.StaffID = S.StaffID Group by S.name, S.gender, S.isMuslim, S.StaffID, S.title, S.position, S.facultyCode, S.department, S.isTakingSTSPhD, S.typeOfEmploy, S.NoOfSatSession, S.NoAsQuarantineInvi, S.NoAsReliefInvi, S.NoOfExtraSession, S.isChiefInvi, S.isInviAbove2Years Having COUNT(InvigilationDutyID) < @TotalLoad";
}
else if (categoryOfInvigilator.Equals("Invigilators"))
{
strSearch = "Select S.name, S.gender, S.isMuslim, S.StaffID, S.title, S.position, S.facultyCode, S.department, S.isTakingSTSPhD, S.typeOfEmploy, S.NoOfSatSession, S.NoAsQuarantineInvi, S.NoAsReliefInvi, S.NoOfExtraSession, S.isChiefInvi, S.isInviAbove2Years From dbo.Staff S, dbo.InvigilationDuty I, dbo.Timeslot T Where S.typeOfEmploy = 'F' and (isInvi = 'Y' or S.isInviAbove2Years = 'Y') and isChiefInvi = 'N' and S.FacultyCode = @Faculty and T.Date = @Date And T.Session = @Session And T.TimeslotID != I.TimeslotID And I.StaffID = S.StaffID Group by S.name, S.gender, S.isMuslim, S.StaffID, S.title, S.position, S.facultyCode, S.department, S.isTakingSTSPhD, S.typeOfEmploy, S.NoOfSatSession, S.NoAsQuarantineInvi, S.NoAsReliefInvi, S.NoOfExtraSession, S.isChiefInvi, S.isInviAbove2Years Having COUNT(InvigilationDutyID) < @TotalLoad";
}
else if (categoryOfInvigilator.Equals("Chief"))
{
strSearch = "Select S.name, S.gender, S.isMuslim, S.StaffID, S.title, S.position, S.facultyCode, S.department, S.isTakingSTSPhD, S.typeOfEmploy, S.NoOfSatSession, S.NoAsQuarantineInvi, S.NoAsReliefInvi, S.NoOfExtraSession, S.isChiefInvi, S.isInviAbove2Years From dbo.Staff S, dbo.InvigilationDuty I, dbo.Timeslot T Where S.typeOfEmploy = 'F' and S.isChiefInvi = 'Y' and S.FacultyCode = @Faculty and T.Date = @Date And T.Session = @Session And T.TimeslotID != I.TimeslotID And I.StaffID = S.StaffID Group by S.name, S.gender, S.isMuslim, S.StaffID, S.title, S.position, S.facultyCode, S.department, S.isTakingSTSPhD, S.typeOfEmploy, S.NoOfSatSession, S.NoAsQuarantineInvi, S.NoAsReliefInvi, S.NoOfExtraSession, S.isChiefInvi, S.isInviAbove2Years Having COUNT(InvigilationDutyID) < @TotalLoad";
}
}
cmdSearch = new SqlCommand(strSearch, conn);
cmdSearch.Parameters.AddWithValue("@Faculty", faculty);
cmdSearch.Parameters.AddWithValue("@Date", date);
cmdSearch.Parameters.AddWithValue("@Session", session);
cmdSearch.Parameters.AddWithValue("@TotalLoad", totalLoadOfDutyForEach);
/*Step 3: Execute command to retrieve data*/
SqlDataReader dtr = cmdSearch.ExecuteReader();
/*Step 4: Get result set from the query*/
if (dtr.HasRows)
{
while (dtr.Read())
{
string staffID = dtr["StaffID"].ToString();
MaintainInvigilationDutyControl maintainInvigilationDutyControl = new MaintainInvigilationDutyControl();
bool isMuslim = false;
bool isTakingSTSPhD = false;
bool isChiefInvi = false;
bool isInviAbove2Years = false;
if (dtr["isMuslim"].ToString().Equals("Y"))
{
isMuslim = true;
}
else
{
isMuslim = false;
}
if (dtr["isTakingSTSPhD"].ToString().Equals("Y"))
{
isTakingSTSPhD = true;
}
else
{
isTakingSTSPhD = false;
}
if (dtr["isChiefInvi"].ToString().Equals("Y"))
{
isChiefInvi = true;
}
else
{
isChiefInvi = false;
}
if (dtr["isInviAbove2Years"].ToString().Equals("Y"))
{
isInviAbove2Years = true;
}
else
{
isInviAbove2Years = false;
}
Staff staff = new Staff(dtr["name"].ToString(), char.Parse(dtr["gender"].ToString()), isMuslim, dtr["StaffID"].ToString(),
dtr["title"].ToString(), dtr["position"].ToString(), dtr["facultyCode"].ToString(), dtr["department"].ToString(),
isTakingSTSPhD, char.Parse(dtr["typeOfEmploy"].ToString()), int.Parse(dtr["NoOfSatSession"].ToString()),
int.Parse(dtr["NoAsQuarantineInvi"].ToString()), int.Parse(dtr["NoAsReliefInvi"].ToString()), int.Parse(dtr["NoOfExtraSession"].ToString()),
isChiefInvi, isInviAbove2Years, new List <string>(), new List <Exemption>(), maintainInvigilationDutyControl.searchInvigilationDuty(staffID));
maintainInvigilationDutyControl.shutDown();
MaintainExemptionControl maintainExemptionControl = new MaintainExemptionControl();
staff.ExemptionList = maintainExemptionControl.searchExemptionList(staffID);
maintainExemptionControl.shutDown();
staffList.Add(staff);
}
}
dtr.Close();
}
catch (SqlException)
{
throw;
}
return(staffList);
}
public List <Staff> searchReliefInvigilators(DateTime date, string session)
{
List <Staff> reliefInvigilatorsList = new List <Staff>();
try
{
/*Step 2: Create Sql Search statement and Sql Search Object*/
strSearch = "Select S.name, S.gender, S.isMuslim, S.StaffID, S.title, S.position, S.facultyCode, S.department, S.isTakingSTSPhD, S.typeOfEmploy, S.NoOfSatSession, S.NoAsQuarantineInvi, S.NoAsReliefInvi, S.NoOfExtraSession, S.isChiefInvi, S.isInviAbove2Years From dbo.Staff S, Timeslot T, dbo.InvigilationDuty I Where S.typeOfEmploy = 'F' and T.Date = @Date And T.Session = @Session And T.TimeslotID = I.TimeslotID And I.StaffID = S.StaffID And I.CatOfInvi = 'Relief' Or I.CatOfInvi = 'Quarantine'";
cmdSearch = new SqlCommand(strSearch, conn);
cmdSearch.Parameters.AddWithValue("@Date", date);
cmdSearch.Parameters.AddWithValue("@Session", session);
/*Step 3: Execute command to retrieve data*/
SqlDataReader dtr = cmdSearch.ExecuteReader();
/*Step 4: Get result set from the query*/
if (dtr.HasRows)
{
while (dtr.Read())
{
string staffID = dtr["StaffID"].ToString();
MaintainInvigilationDutyControl maintainInvigilationDutyControl = new MaintainInvigilationDutyControl();
bool isMuslim = false;
bool isTakingSTSPhD = false;
bool isChiefInvi = false;
bool isInviAbove2Years = false;
if (dtr["isMuslim"].ToString().Equals("Y"))
{
isMuslim = true;
}
else
{
isMuslim = false;
}
if (dtr["isTakingSTSPhD"].ToString().Equals("Y"))
{
isTakingSTSPhD = true;
}
else
{
isTakingSTSPhD = false;
}
if (dtr["isChiefInvi"].ToString().Equals("Y"))
{
isChiefInvi = true;
}
else
{
isChiefInvi = false;
}
if (dtr["isInviAbove2Years"].ToString().Equals("Y"))
{
isInviAbove2Years = true;
}
else
{
isInviAbove2Years = false;
}
Staff reliefInvigilator = new Staff(dtr["name"].ToString(), char.Parse(dtr["gender"].ToString()), isMuslim, dtr["StaffID"].ToString(),
dtr["title"].ToString(), dtr["position"].ToString(), dtr["facultyCode"].ToString(), dtr["department"].ToString(),
isTakingSTSPhD, char.Parse(dtr["typeOfEmploy"].ToString()), int.Parse(dtr["NoOfSatSession"].ToString()),
int.Parse(dtr["NoAsQuarantineInvi"].ToString()), int.Parse(dtr["NoAsReliefInvi"].ToString()), int.Parse(dtr["NoOfExtraSession"].ToString()),
isChiefInvi, isInviAbove2Years, new List <string>(), new List <Exemption>(), maintainInvigilationDutyControl.searchInvigilationDuty(staffID));
maintainInvigilationDutyControl.shutDown();
MaintainExemptionControl maintainExemptionControl = new MaintainExemptionControl();
reliefInvigilator.ExemptionList = maintainExemptionControl.searchExemptionList(staffID);
maintainExemptionControl.shutDown();
reliefInvigilatorsList.Add(reliefInvigilator);
}
}
dtr.Close();
}
catch (SqlException)
{
throw;
}
for (int i = 0; i < reliefInvigilatorsList.Count; i++)
{
reliefInvigilatorsList[i].PaperCodeExamined = searchPaperExamined(reliefInvigilatorsList[i].StaffID);
}
return(reliefInvigilatorsList);
}
public List <Staff> searchLecturer(string input, string criteria)
{
List <Staff> staffList = new List <Staff>();
List <InvigilationDuty> invigilationDutiesList = new List <InvigilationDuty>();
string strSearch;
SqlCommand cmdSearch;
try
{
if (criteria.Equals("faculty"))
{
/*Step 2: Create Sql Search statement and Sql Search Object*/
strSearch = "Select S.name, S.gender, S.isMuslim, S.StaffID, S.title, S.position, S.facultyCode, S.department, S.isTakingSTSPhD, S.typeOfEmploy, S.NoOfSatSession, S.NoAsQuarantineInvi, S.NoAsReliefInvi, S.NoOfExtraSession, S.isChiefInvi, S.isInviAbove2Years From dbo.Staff S Where S.typeOfEmploy = 'F' and S.typeOfEmploy = 'F' and S.faculty = @Faculty";
cmdSearch = new SqlCommand(strSearch, conn);
cmdSearch.Parameters.AddWithValue("@Faculty", input);
}
else if (criteria.Equals("examAsInvi"))
{
strSearch = "Select S.name, S.gender, S.isMuslim, S.StaffID, S.title, S.position, S.facultyCode, S.department, S.isTakingSTSPhD, S.typeOfEmploy, S.NoOfSatSession, S.NoAsQuarantineInvi, S.NoAsReliefInvi, S.NoOfExtraSession, S.isChiefInvi, S.isInviAbove2Years From dbo.Staff S, dbo.PaperExamined P Where S.typeOfEmploy = 'F' and P.CourseCode = @CourseCode And S.StaffID = P.StaffID And S.isExam = 'Y' And (S.isInvi = 'Y' or S.isInviAbove2Years = 'Y') And isChiefInvi = 'N'";
cmdSearch = new SqlCommand(strSearch, conn);
cmdSearch.Parameters.AddWithValue("@CourseCode", input);
}
else if (criteria.Equals("examAsChief"))
{
strSearch = "Select S.name, S.gender, S.isMuslim, S.StaffID, S.title, S.position, S.facultyCode, S.department, S.isTakingSTSPhD, S.typeOfEmploy, S.NoOfSatSession, S.NoAsQuarantineInvi, S.NoAsReliefInvi, S.NoOfExtraSession, S.isChiefInvi, S.isInviAbove2Years From dbo.Staff S, dbo.PaperExamined P Where S.typeOfEmploy = 'F' and P.CourseCode = @CourseCode And S.StaffID = P.StaffID And S.isExam = 'Y' And isChiefInvi = 'Y'";
cmdSearch = new SqlCommand(strSearch, conn);
cmdSearch.Parameters.AddWithValue("@CourseCode", input);
}
else if (criteria.Equals("categoryOfInvigilator") && input.Equals("Chief"))
{
strSearch = "Select S.name, S.gender, S.isMuslim, S.StaffID, S.title, S.position, S.facultyCode, S.department, S.isTakingSTSPhD, S.typeOfEmploy, S.NoOfSatSession, S.NoAsQuarantineInvi, S.NoAsReliefInvi, S.NoOfExtraSession, S.isChiefInvi, S.isInviAbove2Years From dbo.Staff S Where S.typeOfEmploy = 'F' and S.isChiefInvi = 'Y'";
cmdSearch = new SqlCommand(strSearch, conn);
}
else if (criteria.Equals("categoryOfInvigilator") && input.Equals("InviAbove2Years"))
{
strSearch = "Select S.name, S.gender, S.isMuslim, S.StaffID, S.title, S.position, S.facultyCode, S.department, S.isTakingSTSPhD, S.typeOfEmploy, S.NoOfSatSession, S.NoAsQuarantineInvi, S.NoAsReliefInvi, S.NoOfExtraSession, S.isChiefInvi, S.isInviAbove2Years From dbo.Staff S Where S.typeOfEmploy = 'F' and S.isInviAbove2Years = 'Y' and isChiefInvi = 'N'";
cmdSearch = new SqlCommand(strSearch, conn);
}
else if (criteria.Equals("categoryOfInvigilator") && input.Equals("Invigilator"))
{
strSearch = "Select S.name, S.gender, S.isMuslim, S.StaffID, S.title, S.position, S.facultyCode, S.department, S.isTakingSTSPhD, S.typeOfEmploy, S.NoOfSatSession, S.NoAsQuarantineInvi, S.NoAsReliefInvi, S.NoOfExtraSession, S.isChiefInvi, S.isInviAbove2Years From dbo.Staff S Where S.typeOfEmploy = 'F' and (isInvi = 'Y' or S.isInviAbove2Years = 'Y') and isChiefInvi = 'N'";
cmdSearch = new SqlCommand(strSearch, conn);
}
else if (criteria.Equals("isMuslim") && input.Equals("Muslim"))
{
strSearch = "Select S.name, S.gender, S.isMuslim, S.StaffID, S.title, S.position, S.facultyCode, S.department, S.isTakingSTSPhD, S.typeOfEmploy, S.NoOfSatSession, S.NoAsQuarantineInvi, S.NoAsReliefInvi, S.NoOfExtraSession, S.isChiefInvi, S.isInviAbove2Years From dbo.Staff S Where S.typeOfEmploy = 'F' and (isInvi = 'Y' or S.isInviAbove2Years = 'Y') and isChiefInvi = 'N' and S.isMuslim = 'Y'";
cmdSearch = new SqlCommand(strSearch, conn);
}
else
{
strSearch = "Select S.name, S.gender, S.isMuslim, S.StaffID, S.title, S.position, S.facultyCode, S.department, S.isTakingSTSPhD, S.typeOfEmploy, S.NoOfSatSession, S.NoAsQuarantineInvi, S.NoAsReliefInvi, S.NoOfExtraSession, S.isChiefInvi, S.isInviAbove2Years From dbo.Staff S Where S.typeOfEmploy = 'F'";
cmdSearch = new SqlCommand(strSearch, conn);
}
/*Step 3: Execute command to retrieve data*/
SqlDataReader dtr = cmdSearch.ExecuteReader();
/*Step 4: Get result set from the query*/
if (dtr.HasRows)
{
while (dtr.Read())
{
string staffID = dtr["StaffID"].ToString();
MaintainInvigilationDutyControl maintainInvigilationDutyControl = new MaintainInvigilationDutyControl();
bool isMuslim = false;
bool isTakingSTSPhD = false;
bool isChiefInvi = false;
bool isInviAbove2Years = false;
if (dtr["isMuslim"].ToString().Equals("Y"))
{
isMuslim = true;
}
else
{
isMuslim = false;
}
if (dtr["isTakingSTSPhD"].ToString().Equals("Y"))
{
isTakingSTSPhD = true;
}
else
{
isTakingSTSPhD = false;
}
if (dtr["isChiefInvi"].ToString().Equals("Y"))
{
isChiefInvi = true;
}
else
{
isChiefInvi = false;
}
if (dtr["isInviAbove2Years"].ToString().Equals("Y"))
{
isInviAbove2Years = true;
}
else
{
isInviAbove2Years = false;
}
Staff staff = new Staff(dtr["name"].ToString(), char.Parse(dtr["gender"].ToString()), isMuslim, dtr["StaffID"].ToString(),
dtr["title"].ToString(), dtr["position"].ToString(), dtr["facultyCode"].ToString(), dtr["department"].ToString(),
isTakingSTSPhD, char.Parse(dtr["typeOfEmploy"].ToString()), int.Parse(dtr["NoOfSatSession"].ToString()),
int.Parse(dtr["NoAsQuarantineInvi"].ToString()), int.Parse(dtr["NoAsReliefInvi"].ToString()), int.Parse(dtr["NoOfExtraSession"].ToString()),
isChiefInvi, isInviAbove2Years, new List <string>(), new List <Exemption>(), maintainInvigilationDutyControl.searchInvigilationDuty(staffID));
maintainInvigilationDutyControl.shutDown();
MaintainExemptionControl maintainExemptionControl = new MaintainExemptionControl();
staff.ExemptionList = maintainExemptionControl.searchExemptionList(staffID);
maintainExemptionControl.shutDown();
staffList.Add(staff);
}
}
dtr.Close();
}
catch (SqlException)
{
throw;
}
return(staffList);
}