// GET: Loans/BulkLoan
public async Task <IActionResult> BulkLoan(Guid loadFormatId, UploadFileTypes uploadFileType, string delimiter)
{
UpLoadLoanViewModel viewModel = new UpLoadLoanViewModel
{
LoadFormatID = loadFormatId,
UploadFileType = uploadFileType,
Delimiter = delimiter,
TableName = "Loan",
ComponentList = new SelectList(await _context.Components.ToListAsync(), "ID", "Name", await _context.Components.FirstOrDefaultAsync())
};
return(View(viewModel));
}
public async Task <IActionResult> BulkLoan(UpLoadLoanViewModel 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 AccountNumberPos = string.Empty;
string TermPos = string.Empty;
string RatePos = string.Empty;
string LoanDatePos = string.Empty;
string ValuePos = string.Empty;
string PremiumPos = string.Empty;
string SettlementDatePos = 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 LoanTemp 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 "AccountNumber":
AccountNumberPos = row.Position;
break;
case "Term":
TermPos = row.Position;
break;
case "Rate":
RatePos = row.Position;
break;
case "LoanDate":
LoanDatePos = row.Position;
break;
case "Value":
ValuePos = row.Position;
break;
case "Premium":
PremiumPos = row.Position;
break;
case "SettlementDate":
SettlementDatePos = row.Position;
break;
case "default":
break;
}
}
IFormFile uploadFile = viewModel.UpLoadFile;
IList <LoanTemp> loantemps = new List <LoanTemp>();
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++)
{
LoanTemp loantemp = new LoanTemp
{
UserID = currentUserId,
ProductID = currentproductId,
ComponentID = viewModel.ComponentID
};
if (worksheet.Cells[IDNumberPos + row].Value != null && IDNumberPos != null)
{
loantemp.IDNumber = worksheet.Cells[IDNumberPos + row].Value.ToString().Trim();
}
else
{
break;
}
if (worksheet.Cells[AccountNumberPos + row].Value != null && AccountNumberPos != null)
{
loantemp.AccountNumber = worksheet.Cells[AccountNumberPos + row].Value.ToString().Trim();
}
else
{
loantemp.AccountNumber = string.Empty;
}
if (worksheet.Cells[TermPos + row].Value != null && TermPos != null)
{
loantemp.Term = decimal.Parse(worksheet.Cells[TermPos + row].Value.ToString().Trim());
}
else
{
loantemp.Term = 0;
}
if (worksheet.Cells[RatePos + row].Value != null && RatePos != null)
{
loantemp.Rate = decimal.Parse(worksheet.Cells[RatePos + row].Value.ToString().Trim());
}
else
{
loantemp.Rate = 0;
}
if (worksheet.Cells[LoanDatePos + row].Value != null && LoanDatePos != null)
{
var loandate = worksheet.Cells[LoanDatePos + row].Value.ToString().Trim();
DateTime dt = Convert.ToDateTime(loandate);
loantemp.LoanDate = dt;
}
else
{
loantemp.LoanDate = null;
}
if (worksheet.Cells[ValuePos + row].Value != null && ValuePos != null)
{
loantemp.Value = decimal.Parse(worksheet.Cells[ValuePos + row].Value.ToString().Trim());
}
else
{
loantemp.Value = 0;
}
if (worksheet.Cells[PremiumPos + row].Value != null && PremiumPos != null)
{
loantemp.Premium = decimal.Parse(worksheet.Cells[PremiumPos + row].Value.ToString().Trim());
}
else
{
loantemp.Premium = 0;
}
if (worksheet.Cells[SettlementDatePos + row].Value != null && SettlementDatePos != null)
{
var settlementdate = worksheet.Cells[SettlementDatePos + row].Value.ToString().Trim();
DateTime dt = Convert.ToDateTime(settlementdate);
loantemp.SettlementDate = dt;
}
else
{
loantemp.SettlementDate = loantemp.LoanDate.Value.AddMonths(int.Parse(loantemp.Term.ToString()));
};
loantemps.Add(loantemp);
}
}
}
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)
{
LoanTemp loantemp = new LoanTemp
{
UserID = currentUserId,
ProductID = currentproductId,
ComponentID = viewModel.ComponentID
};
string[] cols = line.Split(delimiter);
if (cols[int.Parse(IDNumberPos)] != null && IDNumberPos != null)
{
loantemp.IDNumber = cols[int.Parse(IDNumberPos)];
}
else
{
break;
}
if (cols[int.Parse(AccountNumberPos)] != null && AccountNumberPos != null)
{
loantemp.AccountNumber = cols[int.Parse(AccountNumberPos)];
}
else
{
loantemp.AccountNumber = string.Empty;
}
if (cols[int.Parse(TermPos)] != null && TermPos != null)
{
loantemp.Term = decimal.Parse(cols[int.Parse(TermPos)]);
}
else
{
loantemp.Term = 0;
}
if (cols[int.Parse(RatePos)] != null && RatePos != null)
{
loantemp.Rate = decimal.Parse(cols[int.Parse(RatePos)]);
}
else
{
loantemp.Rate = 0;
}
if (cols[int.Parse(LoanDatePos)] != null && LoanDatePos != null)
{
var loandate = cols[int.Parse(LoanDatePos)];
DateTime dt = Convert.ToDateTime(loandate);
loantemp.LoanDate = dt;
}
else
{
loantemp.LoanDate = null;
}
if (cols[int.Parse(ValuePos)] != null && ValuePos != null)
{
loantemp.Value = decimal.Parse(cols[int.Parse(ValuePos)]);
}
else
{
loantemp.Value = 0;
}
if (cols[int.Parse(PremiumPos)] != null && PremiumPos != null)
{
loantemp.Premium = decimal.Parse(cols[int.Parse(PremiumPos)]);
}
else
{
loantemp.Premium = 0;
}
if (cols[int.Parse(SettlementDatePos)] != null && SettlementDatePos != null)
{
var settlementdate = cols[int.Parse(SettlementDatePos)];
DateTime dt = Convert.ToDateTime(settlementdate);
loantemp.SettlementDate = dt;
}
else
{
loantemp.SettlementDate = loantemp.LoanDate.Value.AddMonths(int.Parse(loantemp.Term.ToString()));
}
loantemps.Add(loantemp);
}
}
}
else if (viewModel.UploadFileType == UploadFileTypes.FixedLengthDelimited)
{
int IDNumberLen = 0;
int AccountNumberLen = 0;
int TermLen = 0;
int RateLen = 0;
int LoanDateLen = 0;
int ValueLen = 0;
int PremiumLen = 0;
int SettlementDateLen = 0;
foreach (var row in formattypes)
{
switch (row.FieldName)
{
case "IDNumber":
IDNumberLen = row.ColumnLength;
break;
case "AccountNumber":
AccountNumberLen = row.ColumnLength;
break;
case "Term":
TermLen = row.ColumnLength;
break;
case "Rate":
RateLen = row.ColumnLength;
break;
case "LoanDate":
LoanDateLen = row.ColumnLength;
break;
case "Value":
ValueLen = row.ColumnLength;
break;
case "Premium":
PremiumLen = row.ColumnLength;
break;
case "SettlementDate":
SettlementDateLen = 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)
{
LoanTemp loantemp = new LoanTemp
{
UserID = currentUserId,
ProductID = currentproductId,
ComponentID = viewModel.ComponentID
};
if (line.Substring(int.Parse(IDNumberPos), IDNumberLen) != null && IDNumberPos != null)
{
loantemp.IDNumber = line.Substring(int.Parse(IDNumberPos), IDNumberLen);
}
else
{
break;
}
if (line.Substring(int.Parse(AccountNumberPos), AccountNumberLen) != null && AccountNumberPos != null)
{
loantemp.AccountNumber = line.Substring(int.Parse(AccountNumberPos), AccountNumberLen);
}
else
{
loantemp.AccountNumber = string.Empty;
}
if (line.Substring(int.Parse(TermPos), TermLen) != null && TermPos != null)
{
loantemp.Term = int.Parse(line.Substring(int.Parse(TermPos), TermLen));
}
else
{
loantemp.Term = 0;
}
if (line.Substring(int.Parse(RatePos), RateLen) != null && RatePos != null)
{
loantemp.Rate = decimal.Parse(line.Substring(int.Parse(RatePos), RateLen));
}
else
{
loantemp.Rate = 0;
}
if (line.Substring(int.Parse(LoanDatePos), LoanDateLen) != null && LoanDatePos != null)
{
var loandate = line.Substring(int.Parse(LoanDatePos), LoanDateLen);
DateTime dt = Convert.ToDateTime(loandate);
loantemp.LoanDate = dt;
}
else
{
loantemp.LoanDate = null;
}
if (line.Substring(int.Parse(ValuePos), ValueLen) != null && ValuePos != null)
{
loantemp.Value = decimal.Parse(line.Substring(int.Parse(ValuePos), ValueLen));
}
else
{
loantemp.Value = 0;
}
if (line.Substring(int.Parse(PremiumPos), PremiumLen) != null && PremiumPos != null)
{
loantemp.Premium = decimal.Parse(line.Substring(int.Parse(PremiumPos), PremiumLen));
}
else
{
loantemp.Premium = 0;
}
if (line.Substring(int.Parse(SettlementDatePos), SettlementDateLen) != null && SettlementDatePos != null)
{
var settlementdate = line.Substring(int.Parse(SettlementDatePos), SettlementDateLen);
DateTime dt = Convert.ToDateTime(settlementdate);
loantemp.SettlementDate = dt;
}
else
{
loantemp.SettlementDate = loantemp.LoanDate.Value.AddMonths(int.Parse(loantemp.Term.ToString()));
}
loantemps.Add(loantemp);
}
}
}
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 (LoanTemp l in loantemps)
{
_context.LoanTemps.Add(l);
}
_context.SaveChanges();
return(RedirectToAction("LoadLoans", new
{
userId = currentUserId,
productId = currentproductId
}));
}
catch (Exception ex)
{
ModelState.AddModelError("Some error occured while exporting. ", ex.Message);
}
ms.Flush();
}
viewModel.UpLoadFile = uploadFile;
viewModel.ComponentList = new SelectList(await _context.Components.ToListAsync(), "ID", "Name", viewModel.ComponentID);
return(View(viewModel));
}
