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();
}
private void writeToExcelTT(FileInfo newFile)
{
// EPPlus library is required
ExcelPackage package = new ExcelPackage(newFile);
// Add a worksheet to the empty workbook
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Inv.TT");
// Set column width
worksheet.Column(1).Width = 4;
worksheet.Column(2).Width = 12;
worksheet.Column(3).Width = 0.8;
worksheet.Column(4).Width = 19;
worksheet.Column(5).Width = 0.8;
worksheet.Column(6).Width = 19;
worksheet.Column(7).Width = 11;
worksheet.Column(8).Width = 12;
worksheet.Column(9).Width = 0.8;
worksheet.Column(10).Width = 25;
MaintainExaminationControl examControl = new MaintainExaminationControl();
MaintainInvigilationDutyControl invControl = new MaintainInvigilationDutyControl();
MaintainStaffControl staffControl = new MaintainStaffControl();
MaintainCourseControl courseControl = new MaintainCourseControl();
//Get lists of examination, timeslot and invigilationDuty
List <string> timeslotList = examControl.getTimeslot();
List <Examination> examList = null;
List <InvigilationDuty> invDutyList = null;
//To keep track of the column number for A, B, D, F, G, H, J
int[] columnNo = new int[7] {
2, 2, 2, 2, 2, 2, 2
};
//Loop through examList and invDutyList with each timeslotID in timeslotList
for (int y = 0; y < timeslotList.Count; y++)
{
//Get new list for every iteration with different timeslotID passed in
examList = examControl.searchExamination(timeslotList[y]);
invDutyList = invControl.searchInvigilationDuty2(timeslotList[y]);
//Get date in string
int year = Convert.ToInt32("20" + timeslotList[y].Substring(6, 2));
int month = Convert.ToInt32(timeslotList[y].Substring(4, 2));
int day = Convert.ToInt32(timeslotList[y].Substring(2, 2));
DateTime dt = new DateTime(year, month, day);
//Get session in string
string session = timeslotList[y].Substring(0, 2);
if (session.Equals("AM"))
{
session = "MORNING SESSION";
}
else if (session.Equals("PM"))
{
session = "AFTERNOON SESSION";
}
else
{
session = "EVENING SESSION";
}
// Add data into cells, Date and Sessiom
worksheet.Cells["A" + columnNo[0].ToString()].Value = dt.DayOfWeek.ToString().ToUpper() + ", " + dt.Day + " " + dt.ToString("MMMM").ToUpper() + " " + dt.Year;
worksheet.Cells["A" + columnNo[0].ToString() + ":" + "J" + columnNo[0].ToString()].Merge = true;
worksheet.Cells["A" + columnNo[0].ToString() + ":" + "J" + columnNo[0]++.ToString()].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
worksheet.Cells["A" + columnNo[0].ToString()].Value = session;
worksheet.Cells["A" + columnNo[0].ToString() + ":" + "J" + columnNo[0].ToString()].Merge = true;
worksheet.Cells["A" + columnNo[0].ToString() + ":" + "J" + columnNo[0]++.ToString()].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
// New row
for (int a = 0; a < columnNo.Length; a++)
{
if (a != 0)
{
columnNo[a] = columnNo[0] + 1;
}
}
columnNo[0]++;
// Add data into cells, Chief and Relief Invigilator for West Campus
worksheet.Cells["B" + columnNo[1].ToString()].Value = "West Campus";
worksheet.Cells["B" + columnNo[1]++.ToString()].Style.Font.UnderLine = true;
worksheet.Cells["D" + columnNo[2]++.ToString()].Value = "";
worksheet.Cells["F" + columnNo[3]++.ToString()].Value = "";
worksheet.Cells["G" + columnNo[4]++.ToString()].Value = "";
worksheet.Cells["H" + columnNo[5].ToString()].Value = "West Campus";
worksheet.Cells["H" + columnNo[5]++.ToString()].Style.Font.UnderLine = true;
worksheet.Cells["J" + columnNo[6]++.ToString()].Value = "";
// New row
for (int a = 0; a < columnNo.Length; a++)
{
columnNo[a] += 1;
}
//print Chief and Relief title
worksheet.Cells["B" + columnNo[1]++.ToString()].Value = "Chief";
worksheet.Cells["B" + columnNo[1]++.ToString()].Value = "Invigilators";
worksheet.Cells["H" + columnNo[5]++.ToString()].Value = "Relief";
worksheet.Cells["H" + columnNo[5]++.ToString()].Value = "Invigilators";
//print Chief and Relief invigilators for West Campus
for (int i = 0; i < invDutyList.Count; i++)
{
Staff staff = staffControl.getStaffName(invDutyList[i].StaffID);
string staffName = staff.Title + " " + staff.Name;
if (invDutyList[i].CategoryOfInvigilator.Equals("Chief") && !invDutyList[i].Location.Equals("Block SA") && !invDutyList[i].Location.Equals("Block SB") && !invDutyList[i].Location.Equals("Block SC") && !invDutyList[i].Location.Equals("Block SD") && !invDutyList[i].Location.Equals("Block SE") && !invDutyList[i].Location.Equals("Block SF") && !invDutyList[i].Location.Equals("Block SG"))
{
worksheet.Cells["D" + columnNo[2].ToString()].Value = "(" + invDutyList[i].Location + ")";
worksheet.Cells["E" + columnNo[2]++.ToString()].Value = "-";
worksheet.Cells["F" + columnNo[3]++.ToString()].Value = staffName;
}
else if (invDutyList[i].CategoryOfInvigilator.Equals("Relief") && invDutyList[i].Location.Equals("West Campus"))
{
worksheet.Cells["I" + columnNo[6].ToString()].Value = "-";
worksheet.Cells["J" + columnNo[6]++.ToString()].Value = staffName;
}
}
//new row
for (int a = 0; a < columnNo.Length; a++)
{
if (a != 6)
{
columnNo[a] = columnNo[6] + 2;
}
}
columnNo[6] += 2;
// Add data into cells, Chief and Relief Invigilator for East Campus
worksheet.Cells["B" + columnNo[1].ToString()].Value = "East Campus";
worksheet.Cells["B" + columnNo[1]++.ToString()].Style.Font.UnderLine = true;
worksheet.Cells["D" + columnNo[2]++.ToString()].Value = "";
worksheet.Cells["F" + columnNo[3]++.ToString()].Value = "";
worksheet.Cells["G" + columnNo[4]++.ToString()].Value = "";
worksheet.Cells["H" + columnNo[5].ToString()].Value = "East Campus";
worksheet.Cells["H" + columnNo[5]++.ToString()].Style.Font.UnderLine = true;
worksheet.Cells["J" + columnNo[6]++.ToString()].Value = "";
// New row
for (int a = 0; a < columnNo.Length; a++)
{
columnNo[a] += 1;
}
//print Chief and Relief title
worksheet.Cells["B" + columnNo[1]++.ToString()].Value = "Chief";
worksheet.Cells["B" + columnNo[1]++.ToString()].Value = "Invigilators";
worksheet.Cells["H" + columnNo[5]++.ToString()].Value = "Relief";
worksheet.Cells["H" + columnNo[5]++.ToString()].Value = "Invigilators";
//print Chief and Relief invigilators for East Campus
for (int i = 0; i < invDutyList.Count; i++)
{
Staff staff = staffControl.getStaffName(invDutyList[i].StaffID);
string staffName = staff.Title + " " + staff.Name;
if (invDutyList[i].CategoryOfInvigilator.Equals("Chief") && (invDutyList[i].Location.Equals("Block SA") || invDutyList[i].Location.Equals("Block SB") || invDutyList[i].Location.Equals("Block SC") || invDutyList[i].Location.Equals("Block SD") || invDutyList[i].Location.Equals("Block SE") || invDutyList[i].Location.Equals("Block SF") || invDutyList[i].Location.Equals("Block SG")))
{
worksheet.Cells["D" + columnNo[2].ToString()].Value = "(" + invDutyList[i].Location + ")";
worksheet.Cells["E" + columnNo[2]++.ToString()].Value = "-";
worksheet.Cells["F" + columnNo[3]++.ToString()].Value = staffName;
}
else if (invDutyList[i].CategoryOfInvigilator.Equals("Relief") && invDutyList[i].Location.Equals("East Campus"))
{
worksheet.Cells["I" + columnNo[6].ToString()].Value = "-";
worksheet.Cells["J" + columnNo[6]++.ToString()].Value = staffName;
}
}
// New row
for (int a = 0; a < columnNo.Length; a++)
{
if (a != 6)
{
columnNo[a] = columnNo[6] + 2;
}
}
columnNo[6] += 2;
//print header
worksheet.Cells["A" + columnNo[0].ToString()].Value = "Venue";
worksheet.Cells["A" + columnNo[0].ToString() + ":" + "B" + columnNo[0].ToString()].Merge = true;
worksheet.Cells["A" + columnNo[0].ToString() + ":" + "B" + columnNo[0]++.ToString()].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
worksheet.Cells["D" + columnNo[2].ToString()].Value = "Paper";
worksheet.Cells["D" + columnNo[2].ToString() + ":" + "F" + columnNo[2].ToString()].Merge = true;
worksheet.Cells["D" + columnNo[2].ToString() + ":" + "F" + columnNo[2]++.ToString()].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
worksheet.Cells["G" + columnNo[4].ToString()].Value = "Programme";
worksheet.Cells["G" + columnNo[4]++.ToString()].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
worksheet.Cells["H" + columnNo[5].ToString()].Value = "Duration";
worksheet.Cells["H" + columnNo[5]++.ToString()].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
worksheet.Cells["H" + columnNo[5].ToString()].Value = "(Hours)";
worksheet.Cells["H" + columnNo[5]++.ToString()].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
worksheet.Cells["J" + columnNo[6].ToString()].Value = "Invigilators";
worksheet.Cells["J" + columnNo[6]++.ToString()].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
//temp var used to store cell index for adding border
int rowFrom = 0;
//set top and bottom border for header
using (var range = worksheet.Cells["A" + (columnNo[0] - 2).ToString() + ":" + "J" + (columnNo[0] - 2).ToString()])
{
range.Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
rowFrom = columnNo[0] - 2;
}
using (var range = worksheet.Cells["A" + columnNo[0].ToString() + ":" + "J" + columnNo[0].ToString()])
{
range.Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
}
//new row
for (int a = 0; a < columnNo.Length; a++)
{
if (a != 5)
{
columnNo[a] = columnNo[5] + 1;
}
}
columnNo[5]++;
string venueID = "";
string staffID = "";
int examCount = 0;
int invCount = 0;
//print examinations and invigilators
for (int i = 0; i < examList.Count(); i++)
{
//To avoid same repeating VenueID
if (!venueID.Equals(examList[i].VenueID))
{
if (invCount > examCount)
{
//new row
for (int a = 0; a < columnNo.Length; a++)
{
if (a != 6)
{
columnNo[a] = columnNo[6] + 1;
}
}
columnNo[6]++;
}
else
{
//new row
for (int a = 0; a < columnNo.Length; a++)
{
if (a != 1)
{
columnNo[a] = columnNo[1] + 1;
}
}
columnNo[1]++;
}
examCount = 0;
invCount = 0;
venueID = examList[i].VenueID;
worksheet.Cells["A" + columnNo[0].ToString()].Value = venueID;
worksheet.Cells["A" + columnNo[0]++.ToString()].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
}
examCount++;
worksheet.Cells["B" + columnNo[1].ToString()].Value = "(" + examList[i].SitFrom + "-" + examList[i].SitTo + ")";
worksheet.Cells["B" + columnNo[1]++.ToString()].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
worksheet.Cells["D" + columnNo[2]++.ToString()].Value = examList[i].CourseCode + " " + courseControl.getCourseTitle(examList[i].CourseCode);
if (examList[i].PaperType == 'M')
{
worksheet.Cells["G" + columnNo[4].ToString()].Value = examList[i].ExamType + examList[i].ProgrammeCode + examList[i].Year;
}
else
{
worksheet.Cells["G" + columnNo[4].ToString()].Value = examList[i].PaperType + "(" + examList[i].ExamType + examList[i].ProgrammeCode + examList[i].Year + ")";
}
worksheet.Cells["G" + columnNo[4]++.ToString()].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
//Find duration in invDutyList with the same venueID
var value = invDutyList.First(x => x.VenueID.Equals(examList[i].VenueID));
worksheet.Cells["H" + columnNo[5].ToString()].Value = value.Duration;
worksheet.Cells["H" + columnNo[5]++.ToString()].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
//Get the first staffID provided with timeslotID and venueID, to prevent printing repeated invigilator for same venueID
var v = invDutyList.First(x => x.TimeslotID.Equals(timeslotList[y]) && x.VenueID.Equals(venueID));
staffID = v.StaffID;
//Print invigilator
for (int b = 0; b < invDutyList.Count; b++)
{
if (invDutyList[b].TimeslotID.Equals(timeslotList[y]) && invDutyList[b].VenueID.Equals(examList[i].VenueID))
{
//Avoid printing repeated invigilator for same venueID
if (!staffID.Equals(invDutyList[b].StaffID) || invCount == 0)
{
invCount++;
Staff staff = staffControl.getStaffName(invDutyList[b].StaffID);
string staffName = staff.Title + " " + staff.Name;
//check for in-charge
if (invDutyList[b].CategoryOfInvigilator.Equals("In-charge"))
{
worksheet.Cells["J" + columnNo[6]++.ToString()].Value = staffName + "*";
}
else
{
worksheet.Cells["J" + columnNo[6]++.ToString()].Value = staffName;
}
}
else
{
break;
}
}
}
}
//set border
using (var range = worksheet.Cells["A" + columnNo[1].ToString() + ":" + "J" + columnNo[1].ToString()])
{
range.Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
}
using (var range = worksheet.Cells["B" + rowFrom.ToString() + ":" + "B" + columnNo[1].ToString()])
{
range.Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
}
using (var range = worksheet.Cells["F" + rowFrom.ToString() + ":" + "F" + columnNo[1].ToString()])
{
range.Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
}
using (var range = worksheet.Cells["G" + rowFrom.ToString() + ":" + "G" + columnNo[1].ToString()])
{
range.Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
}
using (var range = worksheet.Cells["H" + rowFrom.ToString() + ":" + "H" + columnNo[1].ToString()])
{
range.Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
}
//leaves 20 rows between session
for (int a = 0; a < columnNo.Length; a++)
{
if (a != 1)
{
columnNo[a] = columnNo[1] + 20;
}
}
columnNo[1] += 20;
}
// save new workbook
package.Save();
}