Example #1
0
        private DateTime GetSunDate(ImportRosterModel roster)
        {
            DateTime result = DateTime.MinValue;

            if (roster != null)
            {
                if (roster.DateEnd.DayOfWeek == DayOfWeek.Sunday)
                {
                    result = roster.DateEnd;
                }
                else if (roster.DateEnd.DayOfWeek == DayOfWeek.Saturday)
                {
                    result = roster.DateEnd.AddDays(1);
                }
                else if (roster.DateEnd.DayOfWeek == DayOfWeek.Friday)
                {
                    result = roster.DateEnd.AddDays(2);
                }
                else if (roster.DateEnd.DayOfWeek == DayOfWeek.Thursday)
                {
                    result = roster.DateEnd.AddDays(3);
                }
                else if (roster.DateEnd.DayOfWeek == DayOfWeek.Wednesday)
                {
                    result = roster.DateEnd.AddDays(4);
                }
                else if (roster.DateEnd.DayOfWeek == DayOfWeek.Tuesday)
                {
                    result = roster.DateEnd.AddDays(5);
                }
                else if (roster.DateEnd.DayOfWeek == DayOfWeek.Monday)
                {
                    result = roster.DateEnd.AddDays(6);
                }
            }

            return result;
        }
Example #2
0
        public void ImportRoster()
        {
            object missing = Missing.Value;
            Workbook workbook = ExcelHelper.GetWorkbook(FileName);
            Worksheet worksheet = ExcelHelper.GetWorksheet(workbook);

            Aspose.Cells.Cells cells = worksheet.Cells;
            int startRowIndex = 3;//Mẫu roster từ dòng 3
            int startColumnIndex = 4;//Mẫu roster từ cột 4

            //Ngày đầu tháng là Cells[E2] là cells[1,4]
            var dateStart = cells["E2"].DateTimeValue;

            var monthStart = dateStart.Date;
            var monthEnd = monthStart.AddMonths(1);

            if (monthEnd.Day >= monthStart.Day)
            {
                //Tránh trường hợp kết tháng 1 và tháng 2
                monthEnd = monthEnd.AddSeconds(-1);
            }

            RemoveImportObject();
            RemoveInvalidObject();

            var listRosterCorrect = new List<ImportRosterModel>();
            var listRosterError = new List<ImportRosterModel>();
            var listRoster = new List<ImportRosterModel>();
            var listProfileCode = new List<string>();
            var listShiftCode = new List<string>();

            #region Đọc dữ liệu từ excel

            int maxColumnCount = worksheet.Cells.MaxColumn;
            int maxRowCount = worksheet.Cells.MaxRow;

            for (int i = startRowIndex; i <= maxRowCount; i += 1 + SkipRowNumbers)
            {
                var profileCode = ExcelHelper.GetExcelValue(cells[i, 1], typeof(string)).GetString();
                var profileName = ExcelHelper.GetExcelValue(cells[i, 2], typeof(string)).GetString();
                var departmentCode = ExcelHelper.GetExcelValue(cells[i, 3], typeof(string)).GetString();
                var listRosterByProfile = new List<ImportRosterModel>();

                if (!string.IsNullOrWhiteSpace(profileCode))
                {
                    profileCode = profileCode.Trim().ToUpper();

                    if (!listProfileCode.Contains(profileCode))
                    {
                        listProfileCode.Add(profileCode);
                    }

                    for (int j = startColumnIndex; j <= maxColumnCount; j++)
                    {
                        var currentDate = dateStart.AddDays(j - startColumnIndex);
                        var shiftCode = ExcelHelper.GetExcelValue(cells[i, j], typeof(string)).GetString();

                        if (!string.IsNullOrWhiteSpace(shiftCode))
                        {
                            //Dòng cuối cùng sẽ cùng tuần với ngày đang xét
                            var roster = listRosterByProfile.LastOrDefault();

                            if (roster == null || currentDate.DayOfWeek == DayOfWeek.Monday
                                || (roster != null && GetSunDate(roster) < currentDate))
                            {
                                roster = new ImportRosterModel();
                                listRosterByProfile.Add(roster);
                            }

                            if (!listShiftCode.Contains(shiftCode))
                            {
                                listShiftCode.Add(shiftCode);
                            }

                            //Dòng mới tạo thì CodeEmp chưa được set
                            if (string.IsNullOrWhiteSpace(roster.CodeEmp))
                            {
                                roster.CodeEmp = profileCode;
                                roster.ProfileName = profileName;
                                roster.CodeOrg = departmentCode;
                            }

                            if (roster != null)
                            {
                                if (currentDate.DayOfWeek == DayOfWeek.Monday)
                                {
                                    roster.MonShift = shiftCode;
                                    roster.MonDate = currentDate;
                                }
                                else if (currentDate.DayOfWeek == DayOfWeek.Tuesday)
                                {
                                    roster.TueShift = shiftCode;
                                    roster.TueDate = currentDate;
                                }
                                else if (currentDate.DayOfWeek == DayOfWeek.Wednesday)
                                {
                                    roster.WedShift = shiftCode;
                                    roster.WedDate = currentDate;
                                }
                                else if (currentDate.DayOfWeek == DayOfWeek.Thursday)
                                {
                                    roster.ThuShift = shiftCode;
                                    roster.ThuDate = currentDate;
                                }
                                else if (currentDate.DayOfWeek == DayOfWeek.Friday)
                                {
                                    roster.FriShift = shiftCode;
                                    roster.FriDate = currentDate;
                                }
                                else if (currentDate.DayOfWeek == DayOfWeek.Saturday)
                                {
                                    roster.SatShift = shiftCode;
                                    roster.SatDate = currentDate;
                                }
                                else if (currentDate.DayOfWeek == DayOfWeek.Sunday)
                                {
                                    roster.SunShift = shiftCode;
                                    roster.SunDate = currentDate;
                                }

                                roster.DateEnd = currentDate;

                                if (roster.DateStart <= SqlDateTime.MinValue.Value)
                                {
                                    roster.DateStart = currentDate;
                                }

                                if (roster.DateStart.Date < monthStart.Date)
                                {
                                    roster.DateStart = monthStart.Date;
                                }

                                if (roster.DateEnd.Date > monthEnd.Date)
                                {
                                    roster.DateEnd = monthEnd.Date;
                                }
                            }
                        }
                    }

                    if (listRosterByProfile.Any(d => listRoster.Any(p => p.CodeEmp == d.CodeEmp)))
                    {
                        var codeEmp = listRosterByProfile.Select(d => d.CodeEmp).FirstOrDefault();

                        listRosterError.Add(new ImportRosterModel
                        {
                            CodeEmp = codeEmp,
                            Description = "Trùng mã nhân viên trong file [" + codeEmp + "] dòng " + (i + 1)
                        });
                    }
                    else
                    {
                        listRoster.AddRange(listRosterByProfile.Where(d => !string.IsNullOrWhiteSpace(d.CodeEmp)).ToList());
                    }
                }
            }

            #endregion

            if (ImportObjects.ContainsKey(LoginUserID))
            {
                ImportObjects[LoginUserID] = listRosterCorrect;
            }
            else
            {
                ImportObjects.Add(LoginUserID, listRosterCorrect);
            }

            if (InvalidObjects.ContainsKey(LoginUserID))
            {
                InvalidObjects[LoginUserID] = listRosterError;
            }
            else
            {
                InvalidObjects.Add(LoginUserID, listRosterError);
            }

            //Kiểm tra dữ liệu lỗi và dữ liệu bị trùng trong db
            CheckData(listRoster, listProfileCode, listShiftCode);
        }