// GET: Products/BulkPremium
public async Task <IActionResult> BulkPremium(Guid loadFormatId, UploadFileTypes uploadFileType, string delimiter)
{
UpLoadPremiumViewModel viewModel = new UpLoadPremiumViewModel
{
LoadFormatID = loadFormatId,
UploadFileType = uploadFileType,
Delimiter = delimiter,
TableName = "Premium",
ReceivableDate = DateTime.Now,
PaymentTypeList = new SelectList(await _context.PaymentTypes.ToListAsync(), "ID", "Name", await _context.PaymentTypes.FirstOrDefaultAsync()),
PremiumTypeList = new SelectList(await _context.PremiumTypes.ToListAsync(), "ID", "Name", await _context.PremiumTypes.FirstOrDefaultAsync())
};
return(View(viewModel));
}
public async Task <IActionResult> BulkPremium(UpLoadPremiumViewModel viewModel)
{
Guid currentproductId = Guid.Parse(HttpContext.Session.GetString("ProductID"));
Guid currentUserId = Guid.Parse(User.FindFirstValue(ClaimTypes.NameIdentifier));
int startRow = viewModel.StartRow;
string IDNumberPos = string.Empty;
string PremiumDatePos = string.Empty;
string AmountPos = string.Empty;
// Uncomment/comment the below if residue data was not deleted in the ClientTemp table.
Guid myParam = currentUserId;
await _context.Database.ExecuteSqlCommandAsync(
"DELETE FROM PremiumTemp WHERE UserID = {0}",
parameters : myParam);
var formattypes = _context.FormatTypes
.Where(l => l.LoadFormatID == viewModel.LoadFormatID &&
l.TableName == viewModel.TableName)
.ToList();
foreach (var row in formattypes)
{
switch (row.FieldName)
{
case "IDNumber":
IDNumberPos = row.Position;
break;
case "PremiumDate":
PremiumDatePos = row.Position;
break;
case "Amount":
AmountPos = row.Position;
break;
case "default":
break;
}
}
IFormFile uploadFile = viewModel.UpLoadFile;
IList <PremiumTemp> premiumtemps = new List <PremiumTemp>();
using (MemoryStream ms = new MemoryStream())
{
await uploadFile.CopyToAsync(ms);
try
{
if (viewModel.UploadFileType == UploadFileTypes.Excel)
{
using (OfficeOpenXml.ExcelPackage package = new OfficeOpenXml.ExcelPackage(ms))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
int rowCount = worksheet.Dimension.Rows;
for (int row = startRow; row <= rowCount; row++)
{
PremiumTemp premiumtemp = new PremiumTemp
{
UserID = currentUserId,
ProductID = currentproductId,
PremiumTypeID = viewModel.PremiumTypeID,
Reference = viewModel.Reference,
ReceivableDate = viewModel.ReceivableDate,
PaymentTypeID = viewModel.PaymentTypeID,
PaymentAmount = viewModel.PaymentAmount,
BatchNumber = viewModel.BatchNumber
};
if (worksheet.Cells[IDNumberPos + row].Value != null && IDNumberPos != null)
{
premiumtemp.IDNumber = worksheet.Cells[IDNumberPos + row].Value.ToString().Trim();
}
else
{
break;
}
if (worksheet.Cells[PremiumDatePos + row].Value != null && PremiumDatePos != null)
{
var premiumdate = worksheet.Cells[PremiumDatePos + row].Value.ToString().Trim();
DateTime dt = Convert.ToDateTime(premiumdate);
premiumtemp.PremiumDate = dt;
}
else
{
premiumtemp.PremiumDate = viewModel.PremiumDate;
}
if (worksheet.Cells[AmountPos + row].Value != null && AmountPos != null)
{
premiumtemp.Amount = decimal.Parse(worksheet.Cells[AmountPos + row].Value.ToString().Trim());
}
else
{
premiumtemp.Amount = 0;
}
premiumtemps.Add(premiumtemp);
}
}
}
else if (viewModel.UploadFileType == UploadFileTypes.CSV)
{
char[] delimiter = viewModel.Delimiter.ToCharArray(); // Get Delimiter
using (StreamReader sr = new StreamReader(uploadFile.OpenReadStream()))
{
string line = string.Empty;
// Skip rows to where valid data row starts
if (startRow > 0)
{
for (int i = 0; i < startRow - 1; i++)
{
sr.ReadLine();
}
}
while ((line = sr.ReadLine()) != null)
{
PremiumTemp premiumtemp = new PremiumTemp
{
UserID = currentUserId,
ProductID = currentproductId,
PremiumTypeID = viewModel.PremiumTypeID
};
string[] cols = line.Split(delimiter);
if (cols[int.Parse(IDNumberPos)] != null && IDNumberPos != null)
{
premiumtemp.IDNumber = cols[int.Parse(IDNumberPos)];
}
else
{
break;
}
if (cols[int.Parse(PremiumDatePos)] != null && PremiumDatePos != null)
{
var premiumdate = cols[int.Parse(PremiumDatePos)];
DateTime dt = Convert.ToDateTime(premiumdate);
premiumtemp.PremiumDate = dt;
}
else
{
premiumtemp.PremiumDate = viewModel.PremiumDate;
}
if (cols[int.Parse(AmountPos)] != null && AmountPos != null)
{
premiumtemp.Amount = decimal.Parse(cols[int.Parse(AmountPos)]);
}
else
{
premiumtemp.Amount = 0;
}
premiumtemps.Add(premiumtemp);
}
}
}
else if (viewModel.UploadFileType == UploadFileTypes.FixedLengthDelimited)
{
int IDNumberLen = 0;
int PremiumDateLen = 0;
int AmountLen = 0;
foreach (var row in formattypes)
{
switch (row.FieldName)
{
case "IDNumber":
IDNumberLen = row.ColumnLength;
break;
case "PremiumDate":
PremiumDateLen = row.ColumnLength;
break;
case "Amount":
AmountLen = row.ColumnLength;
break;
case "default":
break;
}
}
using (StreamReader sr = new StreamReader(uploadFile.OpenReadStream()))
{
string line = string.Empty;
// Skip rows to where valid data row starts
if (startRow > 0)
{
for (int i = 0; i < startRow - 1; i++)
{
sr.ReadLine();
}
}
while ((line = sr.ReadLine()) != null)
{
PremiumTemp premiumtemp = new PremiumTemp
{
UserID = currentUserId,
ProductID = currentproductId,
PremiumTypeID = viewModel.PremiumTypeID
};
if (line.Substring(int.Parse(IDNumberPos), IDNumberLen) != null && IDNumberPos != null)
{
premiumtemp.IDNumber = line.Substring(int.Parse(IDNumberPos), IDNumberLen);
}
else
{
break;
}
if (line.Substring(int.Parse(PremiumDatePos), PremiumDateLen) != null && PremiumDatePos != null)
{
var premiumdate = line.Substring(int.Parse(PremiumDatePos), PremiumDateLen);
DateTime dt = Convert.ToDateTime(premiumdate);
premiumtemp.PremiumDate = dt;
}
else
{
premiumtemp.PremiumDate = viewModel.PremiumDate;
}
if (line.Substring(int.Parse(AmountPos), AmountLen) != null && AmountPos != null)
{
premiumtemp.Amount = decimal.Parse(line.Substring(int.Parse(AmountPos), AmountLen));
}
else
{
premiumtemp.Amount = 0;
}
premiumtemps.Add(premiumtemp);
}
}
}
ms.Flush();
ViewData["Message"] = "The records are all the data that has been successfully uploaded from the input file." + "\n" +
"You can proceed to load them to the database.";
foreach (PremiumTemp p in premiumtemps)
{
_context.PremiumTemps.Add(p);
}
_context.SaveChanges();
return(RedirectToAction("LoadPremiums", new
{
userId = currentUserId,
productId = currentproductId
}));
}
catch (Exception ex)
{
ModelState.AddModelError("Some error occured while exporting. ", ex.Message);
}
ms.Flush();
}
viewModel.UpLoadFile = uploadFile;
viewModel.PaymentTypeList = new SelectList(await _context.PaymentTypes.ToListAsync(), "ID", "Name", viewModel.PaymentTypeID);
viewModel.PremiumTypeList = new SelectList(await _context.PremiumTypes.ToListAsync(), "ID", "Name", viewModel.PremiumTypeID);
return(View(viewModel));
}
