public async Task <ActionResult> ImportAsync(PharmnetInputModel pharmnetInput)
{
IFormFile file = Request.Form.Files[0];
DateTime dateForDb = DateTime.ParseExact(pharmnetInput.Date, "dd-MM-yyyy", null);
string folderName = "UploadExcel";
string webRootPath = hostEnvironment.WebRootPath;
string newPath = Path.Combine(webRootPath, folderName);
var errorDictionary = new Dictionary <int, string>();
if (!Directory.Exists(newPath))
{
Directory.CreateDirectory(newPath);
}
if (file.Length > 0)
{
string sFileExtension = Path.GetExtension(file.FileName).ToLower();
ISheet sheet;
string fullPath = Path.Combine(newPath, file.FileName);
using (var stream = new FileStream(fullPath, FileMode.Create))
{
file.CopyTo(stream);
stream.Position = 0;
if (sFileExtension == ".xls")
{
HSSFWorkbook hssfwb = new HSSFWorkbook(stream); //This will read the Excel 97-2000 formats
sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook
}
else
{
XSSFWorkbook hssfwb = new XSSFWorkbook(stream); //This will read 2007 Excel format
sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook
}
IRow headerRow = sheet.GetRow(0); //Get Header Row
int cellCount = headerRow.LastCellNum;
for (int j = 0; j < cellCount; j++)
{
ICell cell = headerRow.GetCell(j);
if (cell == null || string.IsNullOrWhiteSpace(cell.ToString()))
{
continue;
}
}
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) //Read Excel File
{
IRow row = sheet.GetRow(i);
if (row == null)
{
continue;
}
if (row.Cells.All(d => d.CellType == CellType.Blank))
{
continue;
}
var newSale = new SaleInputModel();
newSale.Date = dateForDb;
for (int j = row.FirstCellNum; j < cellCount; j++)
{
string currentRow = "";
if (row.GetCell(j) != null)
{
currentRow = row.GetCell(j).ToString().TrimEnd();
}
switch (j)
{
case 2:
if (numbersChecker.WholeNumberCheck(currentRow))
{
var producId = await this.productsService.ProductIdByDistributor(currentRow, Pharmnet);
if (producId != 0)
{
//var producId = await this.productsService.ProductIdByDistributor(currentRow, Pharmnet);
newSale.ProductId = producId;
}
else
{
errorDictionary[i] = currentRow;
}
}
else
{
errorDictionary[i] = currentRow;
}
break;
case 4:
if (numbersChecker.WholeNumberCheck(currentRow))
{
var pharmacyId = await this.pharmaciesService.PharmacyIdByDistributor(currentRow, Pharmnet);
if (pharmacyId != 0)
{
//var pharmacyId = await this.pharmaciesService.PharmacyIdByDistributor(currentRow, Pharmnet);
newSale.PharmacyId = pharmacyId;
}
else
{
errorDictionary[i] = currentRow;
}
}
else
{
errorDictionary[i] = currentRow;
}
break;
case 9:
var currRowDate = DateTime.Parse(currentRow);
if (currentRow != null)
{
newSale.Date = currRowDate;
}
break;
case 11:
if (this.numbersChecker.NegativeNumberIncludedCheck(currentRow))
{
int countProduct = int.Parse(currentRow);
newSale.Count = countProduct;
}
else
{
errorDictionary[i] = currentRow;
}
break;
}
}
await salesService.CreateSale(newSale, Pharmnet);
}
}
}
var pharmnetOutputModel = new PharmnetOutputModel {
Date = pharmnetInput.Date,
Errors = errorDictionary
};
return(this.View(pharmnetOutputModel));
}
public async Task <ActionResult> ImportAsync()
{
IFormFile file = Request.Form.Files[0];
string folderName = "UploadExcel";
string webRootPath = hostEnvironment.WebRootPath;
string newPath = Path.Combine(webRootPath, folderName);
var errorDictionary = new Dictionary <int, string>();
if (!Directory.Exists(newPath))
{
Directory.CreateDirectory(newPath);
}
if (file.Length > 0)
{
string sFileExtension = Path.GetExtension(file.FileName).ToLower();
ISheet sheet;
string fullPath = Path.Combine(newPath, file.FileName);
using (var stream = new FileStream(fullPath, FileMode.Create))
{
file.CopyTo(stream);
stream.Position = 0;
if (sFileExtension == ".xls")
{
HSSFWorkbook hssfwb = new HSSFWorkbook(stream); //This will read the Excel 97-2000 formats
sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook
}
else
{
XSSFWorkbook hssfwb = new XSSFWorkbook(stream); //This will read 2007 Excel format
sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook
}
IRow headerRow = sheet.GetRow(0); //Get Header Row
int cellCount = headerRow.LastCellNum;
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) //Read Excel File
{
IRow row = sheet.GetRow(i);
if (row == null)
{
continue;
}
if (row.Cells.All(d => d.CellType == CellType.Blank))
{
continue;
}
var newPharmacy = new PharmacyInputModel();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
string currentRow = "";
if (row.GetCell(j) != null)
{
currentRow = row.GetCell(j).ToString().TrimEnd();
}
switch (j)
{
case 0:
if (numbersChecker.WholeNumberCheck(currentRow))
{
newPharmacy.BrandexId = int.Parse(currentRow);
}
else
{
errorDictionary[i] = currentRow;
}
break;
case 1:
break;
case 2:
if (currentRow == "")
{
newPharmacy.PharmacyClass = PharmacyClass.Other;
}
else
{
newPharmacy.PharmacyClass = (PharmacyClass)Enum.Parse(typeof(PharmacyClass), currentRow, true);
}
break;
case 3:
if (currentRow == "1")
{
newPharmacy.Active = true;
}
else
{
newPharmacy.Active = false;
}
break;
case 4:
int companyId = await this.companiesService.IdByName(currentRow);
if (companyId != 0)
{
//int companyId = await this.companiesService.IdByName(currentRow);
newPharmacy.CompanyId = companyId;
}
else
{
errorDictionary[i] = currentRow;
}
break;
case 5:
newPharmacy.Name = currentRow;
break;
case 6:
int chainId = await this.pharmacyChainsService.IdByName(currentRow);
if (chainId != 0)
{
newPharmacy.PharmacyChainId = chainId;
}
else
{
errorDictionary[i] = currentRow;
}
break;
case 7:
newPharmacy.Address = currentRow;
break;
case 9:
int regionId = await this.regionsService.IdByName(currentRow);
if (regionId != 0)
{
newPharmacy.RegionId = regionId;
}
else
{
errorDictionary[i] = currentRow;
}
break;
case 10:
break;
case 11:
break;
case 12:
break;
case 13:
break;
case 14:
break;
case 15:
if (currentRow != "")
{
if (numbersChecker.WholeNumberCheck(currentRow))
{
newPharmacy.PharmnetId = int.Parse(currentRow);
}
}
break;
case 16:
if (currentRow != "")
{
if (numbersChecker.WholeNumberCheck(currentRow))
{
newPharmacy.PhoenixId = int.Parse(currentRow);
}
}
break;
case 17:
if (currentRow != "")
{
if (numbersChecker.WholeNumberCheck(currentRow))
{
newPharmacy.SopharmaId = int.Parse(currentRow);
}
}
break;
case 18:
if (currentRow != "")
{
if (numbersChecker.WholeNumberCheck(currentRow))
{
newPharmacy.StingId = int.Parse(currentRow);
}
}
break;
case 19:
break;
case 20:
break;
case 21:
int cityId = await this.citiesService.IdByName(currentRow);
if (cityId != 0)
{
//int cityId = await this.citiesService.IdByName(currentRow);
newPharmacy.CityId = cityId;
}
else
{
errorDictionary[i] = currentRow;
}
break;
}
}
await this.pharmaciesService.CreatePharmacy(newPharmacy);
}
}
}
var pharmacyErrorModel = new CustomErrorDictionaryOutputModel
{
Errors = errorDictionary
};
return(this.View(pharmacyErrorModel));
}