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