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; }
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); }