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