コード例 #1
0
ファイル: Staff.cs プロジェクト: cslim1996/FYP
        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();
        }
コード例 #2
0
        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();
        }
コード例 #3
0
ファイル: StaffDA.cs プロジェクト: cslim1996/FYP
        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);
        }
コード例 #4
0
ファイル: StaffDA.cs プロジェクト: cslim1996/FYP
        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);
        }
コード例 #5
0
ファイル: StaffDA.cs プロジェクト: cslim1996/FYP
        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);
        }