public async Task <IActionResult> UploadExcelFile([FromForm] UploadExcelFile uploadExcelFile)
{
try
{
if (uploadExcelFile == null)
{
return(BadRequest());
}
var Create = await _tdsService.UploadExcelFile(uploadExcelFile);
return(Ok(Create));
}
catch (Exception Ex)
{
throw Ex;
}
}
public async Task <ResponseModel> UploadExcelFile(UploadExcelFile uploadExcelFile)
{
try
{
var GetTds = _dBContext.TblTdsCertificates.Where(f => f.TdsId == uploadExcelFile.TdsId).Include(f => f.TblAllTdsEmails).FirstOrDefault();
if (GetTds == null)
{
return(new ResponseModel {
StatusCode = 200, ResponseMessage = "No Tds Certificates found"
});
}
//var MailsList=await _dBContext.TblAllTdsEmails.Where(f => f.TdsId == uploadExcelFile.TdsId).ToListAsync();
if (GetTds.TblAllTdsEmails.Count != 0)
{
_dBContext.RemoveRange(GetTds.TblAllTdsEmails);
await _dBContext.SaveChangesAsync();
}
string filePath = _configuration.GetSection("KeyValue").GetSection("PhysicalPath").Value;
string fileVirtualPathQP = _configuration.GetSection("KeyValue").GetSection("QPVirtualPath").Value;
filePath = filePath + GetTds.TdsTxnId + "";
if (!Directory.Exists(filePath))
{
Directory.CreateDirectory(filePath);
}
if (uploadExcelFile.ExcelFile != null)
{
var ExcelfileName = Path.GetFileName(uploadExcelFile.ExcelFile.FileName);
string ExcelfilePath = Path.Combine(filePath, uploadExcelFile.ExcelFile.FileName);
if (System.IO.File.Exists(ExcelfilePath))
{
System.IO.File.Delete(ExcelfilePath);
}
var list = new List <AllTdsEmailViewModel>();
List <string> InValidMailsList = new List <string>();
using (var fileStream = new FileStream(ExcelfilePath, FileMode.Create))
{
await uploadExcelFile.ExcelFile.CopyToAsync(fileStream);
using (var package = new ExcelPackage(fileStream))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets[0];
for (int rowNum = 1; rowNum <= worksheet.Dimension.End.Row; rowNum++)
{
var rowCells = from cell in worksheet.Cells
where (cell.Start.Row == rowNum)
select cell;
if (!rowCells.Any(cell => cell.Value != null))
{
worksheet.DeleteRow(rowNum);
}
}
var rowCount = worksheet.Dimension.Rows;
for (int row = 2; row <= rowCount; row++)
{
if (worksheet.Cells[row, 2].Value == null)
{
break;
}
string NewEmail = worksheet.Cells[row, 2].Value.ToString().Trim();
bool ValidateEmail = await IsValidEmail(NewEmail);
if (ValidateEmail == false)
{
InValidMailsList.Add(NewEmail);
continue;
}
if (!list.Exists(x => x.TdsMailId == NewEmail))
{
var NewGuid = Guid.NewGuid().ToString();
var NewEmailBody = GetTds.TdsEmailBody.Replace("InsertAPIKey", "" + NewGuid + "");
list.Add(new AllTdsEmailViewModel
{
TdsUserName = worksheet.Cells[row, 1].Value.ToString().Trim(),
TdsMailId = NewEmail,
TdsPdfName = (worksheet.Cells[row, 3].Value == null ? null : worksheet.Cells[row, 3].Value.ToString().Trim()),
TdsId = uploadExcelFile.TdsId,
TdsPdfUrl = (worksheet.Cells[row, 3].Value == null ? null : fileVirtualPathQP + "/" + GetTds.TdsTxnId + "/" + worksheet.Cells[row, 3].Value.ToString().Trim() + ".pdf"),
IndividualEmailBody = GetTds.IsIndividualEmailBody == true ? NewEmailBody : null,
RestructuringKey = GetTds.IsIndividualEmailBody == true ? NewGuid : null,
});
}
}
}
}
if (list.Count() > 0)
{
var mapdata = _mapper.Map <List <TblAllTdsEmail> >(list);
await _dBContext.AddRangeAsync(mapdata);
await _dBContext.SaveChangesAsync();
}
string responseUrlL = fileVirtualPathQP + "/" + GetTds.TdsTxnId + "/" + uploadExcelFile.ExcelFile.FileName;
GetTds.TdsExcelName = ExcelfileName; GetTds.TdsExcelUrl = responseUrlL;
_dBContext.Update <TblTdsCertificate>(GetTds);
await _dBContext.SaveChangesAsync();
if (InValidMailsList.Count > 0)
{
return(new ResponseModel {
StatusCode = 200, ResponseMessage = "Excel uploaded successfull.Some Email ids from the excel are invalid,please recheck and upload with valid emails again"
});
}
}
return(new ResponseModel {
StatusCode = 200, ResponseMessage = "Success"
});
}
catch (Exception)
{
throw new Exception();
}
}
