/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void ImportFile(object sender, DirectEventArgs e)
{
try
{
var workbook = new WorkBook();
if (fileExcel.HasFile)
{
var path = UploadFile(fileExcel, Constant.PathTemplate);
if (path == null)
{
return;
}
// Read data from excel
workbook.readXLSX(Path.Combine(Server.MapPath("~/"), Constant.PathTemplate, path));
if (!txtSheetName.IsEmpty)
{
workbook.Sheet = workbook.findSheetByName(txtSheetName.Text);
}
// Export to datatable
var dataTable = workbook.ExportDataTable(1, //first row
0, //first col
workbook.LastRow, //last row
workbook.LastCol + 1, //last col
true, //first row as header
true //convert to DateTime object if it match date pattern
);
// count success create
var successRecords = new List <int>();
foreach (DataRow row in dataTable.Rows)
{
// Check employee code
var employeeCode = row["EmployeeCode"].ToString().Trim();
var idNumber = row["IDNumber"].ToString().Trim();
if (!string.IsNullOrEmpty(employeeCode))
{
var recordList = RecordController.CheckExistIDNumberAndEmployeeCode(null, null, employeeCode);
if (recordList != null && recordList.Count > 0)
{
continue;
}
}
// Check id number
if (!string.IsNullOrEmpty(idNumber))
{
var recordList = RecordController.CheckExistIDNumberAndEmployeeCode(null, idNumber, null);
if (recordList != null && recordList.Count > 0)
{
continue;
}
}
// Create new record
var record = new hr_Record
{
// Set default work status
WorkStatusId = CatalogWorkStatusController.GetByGroup(RecordStatus.Working).Id
};
foreach (DataColumn col in dataTable.Columns)
{
if (string.IsNullOrEmpty(row[col].ToString()))
{
continue;
}
var condition = " [Name] = N'{0}' ".FormatWith(row[col]);
cat_Location location;
switch (col.ColumnName)
{
case nameof(hr_Record.FullName):
record.FullName = row[col].ToString();
// lấy họ và đệm từ họ tên
var position = record.FullName.LastIndexOf(' ');
record.Name = position == -1 ? record.FullName : record.FullName.Substring(position + 1).Trim();
break;
case nameof(hr_Record.Sex):
record.Sex = row[col].ToString().Equals("Nam");
break;
case nameof(hr_Record.BirthPlaceWardId):
location = cat_LocationServices.GetByCondition(condition);
record.BirthPlaceWardId = location.Id;
break;
case nameof(hr_Record.BirthPlaceDistrictId):
location = cat_LocationServices.GetByCondition(condition);
record.BirthPlaceDistrictId = location.Id;
break;
case nameof(hr_Record.BirthPlaceProvinceId):
location = cat_LocationServices.GetByCondition(condition);
record.BirthPlaceProvinceId = location.Id;
break;
case nameof(hr_Record.HometownWardId):
location = cat_LocationServices.GetByCondition(condition);
record.HometownWardId = location.Id;
break;
case nameof(hr_Record.HometownDistrictId):
location = cat_LocationServices.GetByCondition(condition);
record.HometownDistrictId = location.Id;
break;
case nameof(hr_Record.HometownProvinceId):
location = cat_LocationServices.GetByCondition(condition);
record.HometownProvinceId = location.Id;
break;
default:
// TODO : need create util function
var reg = @"\(([^)]*)\)";
var propVal = row[col];
if (Regex.IsMatch(propVal.ToString(), reg))
{
propVal = Regex.Match(propVal.ToString(), reg).Groups[1].Value;
}
//get the property information based on the type
var propInfo = record.GetType().GetProperty(col.ColumnName);
//find the property type
if (propInfo == null)
{
break;
}
var propType = propInfo.PropertyType;
//equivalent to the specified object.
if (propType.IsEnum)
{
propVal = Enum.ToObject(propType, int.Parse(propVal.ToString()));
}
if (propType.Name == typeof(Nullable <>).Name)
{
if (Nullable.GetUnderlyingType(propType) == typeof(DateTime))
{
if (!DateTime.TryParseExact(row[col].ToString(), "dd/MM/yyyy",
CultureInfo.CurrentCulture, DateTimeStyles.None, out var date))
{
break;
}
propVal = Convert.ChangeType(date, typeof(DateTime));
}
else
{
propVal = Convert.ChangeType(propVal, Nullable.GetUnderlyingType(propType));
}
}
else
{
propVal = Convert.ChangeType(propVal, propType);
}
//Set the value of the property
propInfo.SetValue(record, propVal, null);
break;
}
}
// add record
hr_RecordServices.Create(record);
successRecords.Add(dataTable.Rows.IndexOf(row));
}
Dialog.Alert("Thêm thành công " + successRecords.Count + " bản ghi");
grpImportExcel.Reload();
}
else
{
Dialog.Alert("Bạn chưa chọn tệp tin đính kèm. Vui lòng chọn.");
}
}
catch (Exception ex)
{
Dialog.ShowError("Đã có lỗi xảy ra: " + ex.Message);
}
}
protected void ImportFile(object sender, DirectEventArgs e)
{
try
{
DataTable dataTable = JSON.Deserialize <DataTable>(hdfDataTable.Text);
// count success create
var successRecords = new List <int>();
foreach (DataRow row in dataTable.Rows)
{
// Check employee code
var employeeCode = row["EmployeeCode"].ToString().Trim();
var idNumber = row["IDNumber"].ToString().Trim();
if (!string.IsNullOrEmpty(employeeCode))
{
var recordList = RecordController.CheckExistIDNumberAndEmployeeCode(null, null, employeeCode);
if (recordList != null && recordList.Count > 0)
{
continue;
}
}
// Check id number
if (!string.IsNullOrEmpty(idNumber))
{
var recordList = RecordController.CheckExistIDNumberAndEmployeeCode(null, idNumber, null);
if (recordList != null && recordList.Count > 0)
{
continue;
}
}
// Create new record
var record = new hr_Record();
foreach (DataColumn col in dataTable.Columns)
{
if (string.IsNullOrEmpty(row[col].ToString()))
{
continue;
}
var condition = " [Name] LIKE N'%{0}%' ".FormatWith(row[col]);
var location = new cat_Location();
switch (col.ColumnName)
{
case nameof(hr_Record.FullName):
record.FullName = row[col].ToString();
// lấy họ và đệm từ họ tên
var position = record.FullName.LastIndexOf(' ');
record.Name = position == -1 ? record.FullName : record.FullName.Substring(position + 1).Trim();
break;
case nameof(hr_Record.Sex):
record.Sex = row[col].ToString().Equals("Nam");
break;
case nameof(hr_Record.BirthPlaceWardId):
location = cat_LocationServices.GetByCondition(condition);
record.BirthPlaceWardId = location.Id;
break;
case nameof(hr_Record.BirthPlaceDistrictId):
location = cat_LocationServices.GetByCondition(condition);
record.BirthPlaceDistrictId = location.Id;
break;
case nameof(hr_Record.BirthPlaceProvinceId):
location = cat_LocationServices.GetByCondition(condition);
record.BirthPlaceProvinceId = location.Id;
break;
case nameof(hr_Record.HometownWardId):
location = cat_LocationServices.GetByCondition(condition);
record.HometownWardId = location.Id;
break;
case nameof(hr_Record.HometownDistrictId):
location = cat_LocationServices.GetByCondition(condition);
record.HometownDistrictId = location.Id;
break;
case nameof(hr_Record.HometownProvinceId):
location = cat_LocationServices.GetByCondition(condition);
record.HometownProvinceId = location.Id;
break;
default:
var reg = @"\(([^)]*)\)";
var propVal = row[col];
if (Regex.IsMatch(propVal.ToString(), reg))
{
propVal = Regex.Match(propVal.ToString(), reg).Groups[1].Value;
}
//get the property information based on the type
var propInfo = record.GetType().GetProperty(col.ColumnName);
//find the property type
var propType = propInfo.PropertyType;
//equivalent to the specified object.
//if (propType.Name == typeof(Nullable<>).Name)
//{
// if (Nullable.GetUnderlyingType(propType) == typeof(DateTime))
// {
// var result = new DateTime();
// if (double.TryParse(propVal.ToString(), out var value))
// {
// result = DateTime.FromOADate(value);
// }
// propVal = Convert.ChangeType(result, typeof(DateTime));
// }
// else
// {
// propVal = Convert.ChangeType(propVal, Nullable.GetUnderlyingType(propType));
// }
//}
//else
//{
// propVal = Convert.ChangeType(propVal, propType);
//}
propVal = propType.Name == typeof(Nullable <>).Name
? Convert.ChangeType(propVal, Nullable.GetUnderlyingType(propType))
: Convert.ChangeType(propVal, propType);
//Set the value of the property
propInfo.SetValue(record, propVal, null);
break;
}
}
// add record
hr_RecordServices.Create(record);
successRecords.Add(dataTable.Rows.IndexOf(row));
}
Dialog.Alert("Thêm thành công " + successRecords.Count + " bản ghi");
grpImportExcel.Reload();
}
catch (Exception ex)
{
Dialog.ShowError("Đã có lỗi xảy ra: " + ex.Message);
}
}
