public Staff(string staffID, bool?isMuslim, bool?isTakingSTSPhD, char typeOfEmploy, int noOfSatSession,
int noAsReliefInvi, int noOfExtraSession, bool?isChiefInvi, bool?isInviAbove2Years, char gender)
: base("", gender, isMuslim)
{
MaintainExemptionControl mExemptioncontrol = new MaintainExemptionControl();
MaintainPaperExaminedControl mPaperExaminedControl = new MaintainPaperExaminedControl();
this.staffID = staffID;
this.isTakingSTSPhD = isTakingSTSPhD;
this.typeOfEmploy = typeOfEmploy;
this.noOfExtraSession = noOfExtraSession;
this.noAsReliefInvi = noAsReliefInvi;
this.noOfExtraSession = noOfExtraSession;
this.isInviAbove2Years = isInviAbove2Years;
this.isChiefInvi = isChiefInvi;
this.exemptionList = mExemptioncontrol.searchExemptionList(staffID);
this.paperCodeExamined = mPaperExaminedControl.searchPaperExaminedByStaffID(staffID);
this.invigilationDuty = new List <InvigilationDuty>();
mExemptioncontrol.shutDown();
mPaperExaminedControl.shutDown();
}
private void writeToExcelDM(FileInfo newFile, string type)
{
// EPPlus library is required
ExcelPackage package = new ExcelPackage(newFile);
MaintainStaffControl staffControl = new MaintainStaffControl();
MaintainExaminationControl examControl = new MaintainExaminationControl();
MaintainInvigilationDutyControl dutyControl = new MaintainInvigilationDutyControl();
MaintainExemptionControl exemptionControl = new MaintainExemptionControl();
MaintainPaperExaminedControl paperExaminedControl = new MaintainPaperExaminedControl();
//Check for CHIEF, or others
if (type.Equals("CHIEF"))
{
// Add a worksheet to the empty workbook
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(type);
// Set column width
worksheet.Column(1).Width = 4.5;
worksheet.Column(2).Width = 45;
//print header
worksheet.Cells[1, 1].Value = "No.";
worksheet.Cells[1, 1, 3, 1].Merge = true;
worksheet.Cells[1, 1, 3, 1].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
worksheet.Cells[1, 1, 3, 1].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
worksheet.Cells[1, 2].Value = "Name";
worksheet.Cells[1, 2, 3, 2].Merge = true;
worksheet.Cells[1, 2, 3, 2].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
worksheet.Cells[1, 2, 3, 2].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
List <Staff> staffList = staffControl.getStaffList();
staffControl.shutDown();
List <string> timeslotList = examControl.getTimeslot();
examControl.shutDown();
List <Exemption> exemptionList = null;
List <InvigilationDuty> dutyList = null;
List <PaperExamined> paperExaminedList = null;
//row and column as counter
int rowNoStaff = 4;
int rowNoDate = 1;
int colNoDate = 3;
int colNoTotal = 0;
string timeslotID = "EMPTY";
//array as counter for printing logo purpose
List <DateTime> dateList = new List <DateTime>();
//print timeslot
for (int i = 0; i < timeslotList.Count; i++)
{
//checking to avoid printing the same date twice
if (!timeslotID.Substring(2).Equals(timeslotList[i].Substring(2)))
{
timeslotID = timeslotList[i];
int year = Convert.ToInt32("20" + timeslotList[i].Substring(6, 2));
int month = Convert.ToInt32(timeslotList[i].Substring(4, 2));
int day = Convert.ToInt32(timeslotList[i].Substring(2, 2));
DateTime dt = new DateTime(year, month, day);
dateList.Add(dt);
worksheet.Cells[rowNoDate, colNoDate].Value = dt.ToString("ddd").ToUpper();
worksheet.Cells[rowNoDate, colNoDate, rowNoDate, colNoDate + 2].Merge = true;
worksheet.Cells[rowNoDate, colNoDate, rowNoDate++, colNoDate + 2].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
worksheet.Cells[rowNoDate, colNoDate].Value = dt.ToString("dd/MM/yy").ToUpper();
worksheet.Cells[rowNoDate, colNoDate, rowNoDate, colNoDate + 2].Merge = true;
worksheet.Cells[rowNoDate, colNoDate, rowNoDate++, colNoDate + 2].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
worksheet.Cells[rowNoDate, colNoDate++].Value = "AM";
worksheet.Cells[rowNoDate, colNoDate++].Value = "PM";
worksheet.Cells[rowNoDate, colNoDate++].Value = "EV";
worksheet.Cells[rowNoDate, colNoDate - 3, rowNoDate, colNoDate - 1].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
rowNoDate = 1;
}
}
//print total header
worksheet.Cells[rowNoDate, colNoDate].Value = "Total";
worksheet.Cells[rowNoDate, colNoDate, 3, colNoDate].Merge = true;
worksheet.Cells[rowNoDate, colNoDate, 3, colNoDate].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
worksheet.Cells[rowNoDate, colNoDate, 3, colNoDate].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
//assign the col of "Total" for writing inv duties
colNoTotal = colNoDate;
//set column width
worksheet.Column(colNoDate).Width = 5.5;
while (colNoDate >= 4)
{
worksheet.Column((colNoDate--) - 1).Width = 6.5;
}
//count number of inv printed
int countInvi = 0;
//print staffs
for (int i = 0; i < staffList.Count; i++)
{
//check and print chief invigilator name
if (staffList[i].IsChief == 'Y')
{
//print chief invigilator
worksheet.Cells[countInvi + 4, 1].Value = ++countInvi;
worksheet.Cells["B" + rowNoStaff++.ToString()].Value = staffList[i].Name + " " + staffList[i].Title;
//check and print exemption
exemptionList = exemptionControl.searchExemption(staffList[i].StaffID);
if (exemptionList.Count > 0)
{
for (int a = 0; a < exemptionList.Count; a++)
{
//get the column number to insert
int colToInsert = 3 + (dateList.FindIndex(b => b.Date == exemptionList[a].Date) * 3);
//get the column number if not AM
if (exemptionList[a].Session.Equals("PM"))
{
colToInsert += 1;
}
else if (exemptionList[a].Session.Equals("EV"))
{
colToInsert += 2;
}
//insert exemption
worksheet.Cells[rowNoStaff - 1, colToInsert].IsRichText = true;
ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("X");
}
}
//count total number of duties for each inv
int totalDuty = 0;
//check and print invigilation duty
dutyList = dutyControl.searchInvigilationDuty2(Convert.ToInt32(staffList[i].StaffID));
paperExaminedList = paperExaminedControl.getPaperExaminedList(staffList[i].StaffID);
if (dutyList.Count > 0)
{
totalDuty = 0;
for (int a = 0; a < dutyList.Count; a++)
{
//get timeslot Date to compare with dutyList Date
int year = Convert.ToInt32("20" + dutyList[a].TimeslotID.Substring(6, 2));
int month = Convert.ToInt32(dutyList[a].TimeslotID.Substring(4, 2));
int day = Convert.ToInt32(dutyList[a].TimeslotID.Substring(2, 2));
DateTime dt = new DateTime(year, month, day);
//get the column number to insert
int colToInsert = 3 + (dateList.FindIndex(b => b.Date == dt.Date) * 3);
//get the column number if not AM
if (dutyList[a].TimeslotID.Substring(0, 2).Equals("PM"))
{
colToInsert += 1;
}
else if (dutyList[a].TimeslotID.Substring(0, 2).Equals("EV"))
{
colToInsert += 2;
}
//retrieve course list(exam) on given location and timeslotID
List <string> paperList = paperExaminedControl.searchPaperExamined(dutyList[a].Location, dutyList[a].TimeslotID);
//check if the chief is also an examiner
var result = paperExaminedList.Select(s => s.CourseCode).Intersect(paperList);
//insert only if no exemption is inserted
if (worksheet.Cells[rowNoStaff - 1, colToInsert].Value == null)
{
totalDuty++;
worksheet.Cells[rowNoStaff - 1, colToInsert].IsRichText = true;
//compare duty type
if (dutyList[a].Location.Equals("Block V") && dutyList[a].CategoryOfInvigilator.Equals("Chief"))
{
ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("V");
ert.Size = 7;
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
ert.Size = 12;
if (result.ToList().Count > 0)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
ert.Size = 7;
}
if (dutyList[a].Duration != 2)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add(dutyList[a].Duration.ToString());
ert.Size = 7;
}
}
else if (dutyList[a].Location.Equals("Block SE") && dutyList[a].CategoryOfInvigilator.Equals("Chief"))
{
ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("SE");
ert.Size = 7;
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
ert.Size = 12;
if (result.ToList().Count > 0)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
ert.Size = 7;
}
if (dutyList[a].Duration != 2)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add(dutyList[a].Duration.ToString());
ert.Size = 7;
}
}
else if (dutyList[a].Location.Equals("Block SD") && dutyList[a].CategoryOfInvigilator.Equals("Chief"))
{
ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("SD");
ert.Size = 7;
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
ert.Size = 12;
if (result.ToList().Count > 0)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
ert.Size = 7;
}
if (dutyList[a].Duration != 2)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add(dutyList[a].Duration.ToString());
ert.Size = 7;
}
}
else if (dutyList[a].Location.Equals("Block SB") && dutyList[a].CategoryOfInvigilator.Equals("Chief"))
{
ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("SB");
ert.Size = 7;
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
ert.Size = 12;
if (result.ToList().Count > 0)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
ert.Size = 7;
}
if (dutyList[a].Duration != 2)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add(dutyList[a].Duration.ToString());
ert.Size = 7;
}
}
else if (dutyList[a].Location.Equals("Block R") && dutyList[a].CategoryOfInvigilator.Equals("Chief"))
{
ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("R");
ert.Size = 7;
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
ert.Size = 12;
if (result.ToList().Count > 0)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
ert.Size = 7;
}
if (dutyList[a].Duration != 2)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add(dutyList[a].Duration.ToString());
ert.Size = 7;
}
}
else if (dutyList[a].Location.Equals("Block Q") && dutyList[a].CategoryOfInvigilator.Equals("Chief"))
{
ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("Q");
ert.Size = 7;
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
ert.Size = 12;
if (result.ToList().Count > 0)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
ert.Size = 7;
}
if (dutyList[a].Duration != 2)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add(dutyList[a].Duration.ToString());
ert.Size = 7;
}
}
else if (dutyList[a].Location.Equals("Block PA, PA7-PA12") && dutyList[a].CategoryOfInvigilator.Equals("Chief"))
{
ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("PA7");
ert.Size = 7;
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
ert.Size = 12;
if (result.ToList().Count > 0)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
ert.Size = 7;
}
if (dutyList[a].Duration != 2)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add(dutyList[a].Duration.ToString());
ert.Size = 7;
}
}
else if (dutyList[a].Location.Equals("Block PA, PA1-PA6") && dutyList[a].CategoryOfInvigilator.Equals("Chief"))
{
ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("PA1");
ert.Size = 7;
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
ert.Size = 12;
if (result.ToList().Count > 0)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
ert.Size = 7;
}
if (dutyList[a].Duration != 2)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add(dutyList[a].Duration.ToString());
ert.Size = 7;
}
}
else if (dutyList[a].Location.Equals("Block M") && dutyList[a].CategoryOfInvigilator.Equals("Chief"))
{
ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("M");
ert.Size = 7;
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
ert.Size = 12;
if (result.ToList().Count > 0)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
ert.Size = 7;
}
if (dutyList[a].Duration != 2)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add(dutyList[a].Duration.ToString());
ert.Size = 7;
}
}
else if (dutyList[a].Location.Equals("Block L") && dutyList[a].CategoryOfInvigilator.Equals("Chief"))
{
ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("L");
ert.Size = 7;
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
ert.Size = 12;
if (result.ToList().Count > 0)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
ert.Size = 7;
}
if (dutyList[a].Duration != 2)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add(dutyList[a].Duration.ToString());
ert.Size = 7;
}
}
else if (dutyList[a].Location.Equals("Block KS") && dutyList[a].CategoryOfInvigilator.Equals("Chief"))
{
ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("KS");
ert.Size = 7;
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
ert.Size = 12;
if (result.ToList().Count > 0)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
ert.Size = 7;
}
if (dutyList[a].Duration != 2)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add(dutyList[a].Duration.ToString());
ert.Size = 7;
}
}
else if (dutyList[a].Location.Equals("Block H, H7-H14") && dutyList[a].CategoryOfInvigilator.Equals("Chief"))
{
ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("H7");
ert.Size = 7;
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
ert.Size = 12;
if (result.ToList().Count > 0)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
ert.Size = 7;
}
if (dutyList[a].Duration != 2)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add(dutyList[a].Duration.ToString());
ert.Size = 7;
}
}
else if (dutyList[a].Location.Equals("Block H, H1-H6") && dutyList[a].CategoryOfInvigilator.Equals("Chief"))
{
ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("H1");
ert.Size = 7;
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
ert.Size = 12;
if (result.ToList().Count > 0)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
ert.Size = 7;
}
if (dutyList[a].Duration != 2)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add(dutyList[a].Duration.ToString());
ert.Size = 7;
}
}
else if (dutyList[a].Location.Equals("Block H") && dutyList[a].CategoryOfInvigilator.Equals("Chief"))
{
ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("H");
ert.Size = 7;
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
ert.Size = 12;
if (result.ToList().Count > 0)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
ert.Size = 7;
}
if (dutyList[a].Duration != 2)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add(dutyList[a].Duration.ToString());
ert.Size = 7;
}
}
else if (dutyList[a].Location.Equals("Dewan Utama") && dutyList[a].CategoryOfInvigilator.Equals("Chief"))
{
ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("DU");
ert.Size = 7;
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
ert.Size = 12;
if (result.ToList().Count > 0)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
ert.Size = 7;
}
if (dutyList[a].Duration != 2)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add(dutyList[a].Duration.ToString());
ert.Size = 7;
}
}
else if (dutyList[a].Location.Equals("Block DS") && dutyList[a].CategoryOfInvigilator.Equals("Chief"))
{
ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
ert.Size = 12;
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("DS");
ert.Size = 7;
if (result.ToList().Count > 0)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
ert.Size = 7;
}
if (dutyList[a].Duration != 2)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add(dutyList[a].Duration.ToString());
ert.Size = 7;
}
}
}
//if printing last duty in a duty list, print totalDuty
if (a + 1 == dutyList.Count)
{
worksheet.Cells[rowNoStaff - 1, colNoTotal].Value = totalDuty;
}
}
}
}
}
//shutdown connection
exemptionControl.shutDown();
dutyControl.shutDown();
paperExaminedControl.shutDown();
//center "No." and all the ticks
worksheet.Cells[4, 1, staffList.Count + 4, 1].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
worksheet.Cells[4, 3, rowNoStaff, colNoTotal].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
}
else
{
char facultyCode = '\0';
//compare type to get facultyCode
if (type.Equals("CNBL"))
{
facultyCode = 'E';
}
else if (type.Equals("FEBE"))
{
facultyCode = 'T';
}
else if (type.Equals("FAFB"))
{
facultyCode = 'B';
}
else if (type.Equals("FASC"))
{
facultyCode = 'A';
}
// Add a worksheet to the empty workbook
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(type);
// Set column width
worksheet.Column(1).Width = 4.5;
worksheet.Column(2).Width = 45;
//print header
worksheet.Cells[1, 1].Value = "No.";
worksheet.Cells[1, 1, 3, 1].Merge = true;
worksheet.Cells[1, 1, 3, 1].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
worksheet.Cells[1, 1, 3, 1].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
worksheet.Cells[1, 2].Value = "Name";
worksheet.Cells[1, 2, 3, 2].Merge = true;
worksheet.Cells[1, 2, 3, 2].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
worksheet.Cells[1, 2, 3, 2].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
List <Staff> staffList = staffControl.getStaffList();
staffControl.shutDown();
List <string> timeslotList = examControl.getTimeslot();
examControl.shutDown();
List <Exemption> exemptionList = null;
List <InvigilationDuty> dutyList = null;
List <PaperExamined> paperExaminedList = null;
//row and column as counter
int rowNoStaff = 4;
int rowNoDate = 1;
int colNoDate = 3;
int colNoTotal = 0;
string timeslotID = "EMPTY";
//array as counter for printing logo purpose
List <DateTime> dateList = new List <DateTime>();
//print timeslot
for (int i = 0; i < timeslotList.Count; i++)
{
//checking to avoid printing the same date twice
if (!timeslotID.Substring(2).Equals(timeslotList[i].Substring(2)))
{
timeslotID = timeslotList[i];
int year = Convert.ToInt32("20" + timeslotList[i].Substring(6, 2));
int month = Convert.ToInt32(timeslotList[i].Substring(4, 2));
int day = Convert.ToInt32(timeslotList[i].Substring(2, 2));
DateTime dt = new DateTime(year, month, day);
dateList.Add(dt);
worksheet.Cells[rowNoDate, colNoDate].Value = dt.ToString("ddd").ToUpper();
worksheet.Cells[rowNoDate, colNoDate, rowNoDate, colNoDate + 2].Merge = true;
worksheet.Cells[rowNoDate, colNoDate, rowNoDate++, colNoDate + 2].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
worksheet.Cells[rowNoDate, colNoDate].Value = dt.ToString("dd/MM/yy").ToUpper();
worksheet.Cells[rowNoDate, colNoDate, rowNoDate, colNoDate + 2].Merge = true;
worksheet.Cells[rowNoDate, colNoDate, rowNoDate++, colNoDate + 2].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
worksheet.Cells[rowNoDate, colNoDate++].Value = "AM";
worksheet.Cells[rowNoDate, colNoDate++].Value = "PM";
worksheet.Cells[rowNoDate, colNoDate++].Value = "EV";
worksheet.Cells[rowNoDate, colNoDate - 3, rowNoDate, colNoDate - 1].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
rowNoDate = 1;
}
}
//print total header
worksheet.Cells[rowNoDate, colNoDate].Value = "Total";
worksheet.Cells[rowNoDate, colNoDate, 3, colNoDate].Merge = true;
worksheet.Cells[rowNoDate, colNoDate, 3, colNoDate].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
worksheet.Cells[rowNoDate, colNoDate, 3, colNoDate].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
//assign the col of "Total" for writing inv duties
colNoTotal = colNoDate;
//set column width
worksheet.Column(colNoDate).Width = 5.5;
while (colNoDate >= 4)
{
worksheet.Column((colNoDate--) - 1).Width = 6.5;
}
//count inv number of inv printed
int countInvi = 0;
//print staffs
for (int i = 0; i < staffList.Count; i++)
{
//check and print invigilator based on faculty
if (staffList[i].IsInvi == 'Y' && staffList[i].FacultyCode == facultyCode)
{
//print invigilator
worksheet.Cells[countInvi + 4, 1].Value = ++countInvi;
worksheet.Cells["B" + rowNoStaff++.ToString()].Value = staffList[i].Name + " " + staffList[i].Title;
//check and print exemption
exemptionList = exemptionControl.searchExemption(staffList[i].StaffID);
if (exemptionList.Count > 0)
{
for (int a = 0; a < exemptionList.Count; a++)
{
//get the column number to insert
int colToInsert = 3 + (dateList.FindIndex(b => b.Date == exemptionList[a].Date) * 3);
//get the column number if not AM
if (exemptionList[a].Session.Equals("PM"))
{
colToInsert += 1;
}
else if (exemptionList[a].Session.Equals("EV"))
{
colToInsert += 2;
}
//insert exemption
worksheet.Cells[rowNoStaff - 1, colToInsert].IsRichText = true;
ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("X");
}
}
//count total number of duties for each inv
int totalDuty = 0;
//check and print invigilation duty
dutyList = dutyControl.searchInvigilationDuty2(Convert.ToInt32(staffList[i].StaffID));
paperExaminedList = paperExaminedControl.getPaperExaminedList(staffList[i].StaffID);
if (dutyList.Count > 0)
{
totalDuty = 0;
for (int a = 0; a < dutyList.Count; a++)
{
//get timeslot Date to compare with dutyList Date
int year = Convert.ToInt32("20" + dutyList[a].TimeslotID.Substring(6, 2));
int month = Convert.ToInt32(dutyList[a].TimeslotID.Substring(4, 2));
int day = Convert.ToInt32(dutyList[a].TimeslotID.Substring(2, 2));
DateTime dt = new DateTime(year, month, day);
//get the column number to insert
int colToInsert = 3 + (dateList.FindIndex(b => b.Date == dt.Date) * 3);
//get the column number if not AM
if (dutyList[a].TimeslotID.Substring(0, 2).Equals("PM"))
{
colToInsert += 1;
}
else if (dutyList[a].TimeslotID.Substring(0, 2).Equals("EV"))
{
colToInsert += 2;
}
//retrieve course list(exam) on given location and timeslotID
List <string> paperList = paperExaminedControl.searchPaperExamined(dutyList[a].Location, dutyList[a].TimeslotID);
//check if the chief is also an examiner
var result = paperExaminedList.Select(s => s.CourseCode).Intersect(paperList);
//insert only if no exemption is inserted
if (worksheet.Cells[rowNoStaff - 1, colToInsert].Value == null)
{
totalDuty++;
worksheet.Cells[rowNoStaff - 1, colToInsert].IsRichText = true;
//compare duty type
if (dutyList[a].Location.Equals("Block DS"))
{
ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
ert.Size = 12;
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("DS");
ert.Size = 7;
if (result.ToList().Count > 0)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
ert.Size = 7;
}
if (dutyList[a].Duration != 2)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add(dutyList[a].Duration.ToString());
ert.Size = 7;
}
}
else if (dutyList[a].CategoryOfInvigilator.Equals("Relief"))
{
ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
ert.Size = 12;
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("R");
ert.Size = 7;
if (result.ToList().Count > 0)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
ert.Size = 7;
}
if (dutyList[a].Duration != 2)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add(dutyList[a].Duration.ToString());
ert.Size = 7;
}
}
else if (dutyList[a].Duration == 3)
{
ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
ert.Size = 12;
if (result.ToList().Count > 0)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
ert.Size = 7;
}
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("3");
ert.Size = 7;
}
else if (dutyList[a].Duration == 2)
{
ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
ert.Size = 12;
if (result.ToList().Count > 0)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
ert.Size = 7;
}
}
else if (dutyList[a].Duration == 1)
{
ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
ert.Size = 12;
if (result.ToList().Count > 0)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
ert.Size = 7;
}
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("1");
ert.Size = 7;
}
}
//if printing last duty in a duty list, print totalDuty
if (a + 1 == dutyList.Count)
{
worksheet.Cells[rowNoStaff - 1, colNoTotal].Value = totalDuty;
}
}
}
}
}
//shutdown connection
exemptionControl.shutDown();
dutyControl.shutDown();
//center "No." and all the ticks
worksheet.Cells[4, 1, staffList.Count + 4, 1].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
worksheet.Cells[4, 3, rowNoStaff, colNoTotal].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
}
//save new workbook
package.Save();
}
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);
}
