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