/// <summary>
/// Import file danh mục
/// </summary>
/// <param name="stream"></param>
/// <param name="createdBy"></param>
/// <returns></returns>
public virtual async Task <AppDomainImportResult> ImportTemplateFile(Stream stream, string createdBy)
{
AppDomainImportResult appDomainImportResult = new AppDomainImportResult();
var dataTable = SetDataTable();
using (var package = new ExcelPackage(stream))
{
int totalFailed = 0;
int totalSuccess = 0;
var ws = package.Workbook.Worksheets.FirstOrDefault();
if (ws == null)
{
throw new Exception("Sheet name không tồn tại");
}
var existItems = Queryable.Where(e => !e.Deleted);
var catalogueMappers = new ExcelMapper(stream)
{
HeaderRow = false, MinRowNumber = 1
}.Fetch <CatalogueMapper>().ToList();
if (catalogueMappers != null && catalogueMappers.Any())
{
List <string> codeImports = new List <string>();
foreach (var catalogueMapper in catalogueMappers)
{
int index = catalogueMappers.IndexOf(catalogueMapper);
int resultIndex = index + 2;
IList <string> errors = new List <string>();
if (string.IsNullOrEmpty(catalogueMapper.Code))
{
errors.Add("Vui lòng nhập mã");
}
if (existItems.Any(x => x.Code == catalogueMapper.Code) || codeImports.Any(x => x == catalogueMapper.Code))
{
errors.Add("Mã đã tồn tại");
}
if (string.IsNullOrEmpty(catalogueMapper.Name))
{
errors.Add("Vui lòng nhập tên");
}
if (errors.Any())
{
ws.Cells["D" + resultIndex].Value = string.Join(", ", errors);
totalFailed += 1;
}
else
{
ws.Cells["D" + resultIndex].Value = "Thành công";
totalSuccess += 1;
E item = new E()
{
Code = catalogueMapper.Code,
Name = catalogueMapper.Name,
Description = catalogueMapper.Description,
Deleted = false,
Active = true,
Created = DateTime.Now,
CreatedBy = createdBy,
};
codeImports.Add(catalogueMapper.Code);
dataTable = AddDataTableRow(dataTable, item);
}
}
}
ws.Column(4).Hidden = false;
ws.Cells.AutoFitColumns();
appDomainImportResult.Data = new
{
TotalSuccess = totalSuccess,
TotalFailed = totalFailed,
};
appDomainImportResult.ResultFile = package.GetAsByteArray();
}
if (dataTable.Rows.Count > 0)
{
var connectionString = string.Format(configuration.GetConnectionString("MedicalDbContext"));
using (SqlBulkCopy bc = new SqlBulkCopy(connectionString))
{
bc.DestinationTableName = this.GetTableName();
bc.BulkCopyTimeout = 4000;
await bc.WriteToServerAsync(dataTable);
}
}
appDomainImportResult.Success = true;
return(appDomainImportResult);
}
/// <summary>
/// Import file chuyên khoa
/// </summary>
/// <param name="stream"></param>
/// <param name="createdBy"></param>
/// <returns></returns>
public override async Task <AppDomainImportResult> ImportTemplateFile(Stream stream, string createdBy)
{
AppDomainImportResult appDomainImportResult = new AppDomainImportResult();
var dataTable = SetDataTable();
using (var package = new ExcelPackage(stream))
{
int totalFailed = 0;
int totalSuccess = 0;
var ws = package.Workbook.Worksheets.FirstOrDefault();
if (ws == null)
{
throw new Exception("Sheet name không tồn tại");
}
var existItems = Queryable.Where(e => !e.Deleted);
var existHospitals = this.unitOfWork.Repository <Hospitals>().GetQueryable().Where(e => !e.Deleted && e.Active);
var catalogueMappers = new ExcelMapper(stream)
{
HeaderRow = false, MinRowNumber = 1
}.Fetch <SpecialListTypeMapper>().ToList();
if (catalogueMappers != null && catalogueMappers.Any())
{
List <SpecialistTypes> specialistTypeImports = new List <SpecialistTypes>();
foreach (var catalogueMapper in catalogueMappers)
{
int index = catalogueMappers.IndexOf(catalogueMapper);
double? price = null;
Hospitals hospitalInfo = new Hospitals();
int resultIndex = index + 2;
IList <string> errors = new List <string>();
if (string.IsNullOrEmpty(catalogueMapper.HospitalCode))
{
errors.Add("Vui lòng nhập mã bệnh viện");
}
if (!existHospitals.Any(x => x.Code == catalogueMapper.HospitalCode))
{
errors.Add("Mã bệnh viện không tồn tại");
}
else
{
hospitalInfo = await existHospitals.Where(e => e.Code == catalogueMapper.HospitalCode).FirstOrDefaultAsync();
}
if (hospitalInfo == null)
{
errors.Add("Thông tin bệnh viện không tồn tại");
}
if (string.IsNullOrEmpty(catalogueMapper.Code))
{
errors.Add("Vui lòng nhập mã chuyên khoa");
}
if (hospitalInfo != null && (existItems.Any(x => x.HospitalId == hospitalInfo.Id && x.Code == catalogueMapper.Code) ||
specialistTypeImports.Any(x => x.HospitalId == hospitalInfo.Id && x.Code == catalogueMapper.Code)))
{
errors.Add("Mã chuyên khoa đã tồn tại");
}
if (string.IsNullOrEmpty(catalogueMapper.Name))
{
errors.Add("Vui lòng nhập tên chuyên khoa");
}
price = TryParseUtilities.TryParseDouble(catalogueMapper.Price);
if (price == null)
{
errors.Add("Giá khám không hợp lệ");
}
if (errors.Any())
{
ws.Cells["F" + resultIndex].Value = string.Join(", ", errors);
totalFailed += 1;
}
else
{
ws.Cells["F" + resultIndex].Value = "Thành công";
totalSuccess += 1;
SpecialistTypes item = new SpecialistTypes()
{
Code = catalogueMapper.Code,
Name = catalogueMapper.Name,
Description = catalogueMapper.Description,
Deleted = false,
Active = true,
Created = DateTime.Now,
HospitalId = hospitalInfo.Id,
CreatedBy = createdBy,
Price = price
};
specialistTypeImports.Add(item);
dataTable = AddDataTableRow(dataTable, item);
}
}
}
ws.Column(6).Hidden = false;
ws.Cells.AutoFitColumns();
appDomainImportResult.Data = new
{
TotalSuccess = totalSuccess,
TotalFailed = totalFailed,
};
appDomainImportResult.ResultFile = package.GetAsByteArray();
}
if (dataTable.Rows.Count > 0)
{
var connectionString = string.Format(configuration.GetConnectionString("MedicalDbContext"));
using (SqlBulkCopy bc = new SqlBulkCopy(connectionString))
{
bc.DestinationTableName = this.GetTableName();
bc.BulkCopyTimeout = 4000;
await bc.WriteToServerAsync(dataTable);
}
}
appDomainImportResult.Success = true;
return(appDomainImportResult);
}