public ActionResult CellEditInsert([FromBody] CRUDModel <Coverage> coverage) { coverage.Value.ID = Guid.NewGuid(); _context.Add(coverage.Value); _context.SaveChanges(); return(Json(coverage)); }
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)); }
public ActionResult CellEditUpdate([FromBody] CRUDModel <Client> client) { _context.Update(client.Value); _context.SaveChanges(); return(Json(client)); }
// GET: Payables/ProcessPayments public async Task <IActionResult> ProcessPayments(Guid productId, string errReference, string errBankAccounts) { if (!string.IsNullOrEmpty(errReference)) { ViewData["ErrReference"] = errReference; } if (!string.IsNullOrEmpty(errBankAccounts)) { ViewData["ErrBankAccounts"] = errBankAccounts; } var myParam = productId; await _context.Database.ExecuteSqlCommandAsync( "DELETE FROM ChequeTemp WHERE ProductID = {0}", parameters : myParam); await _context.Database.ExecuteSqlCommandAsync( "DELETE FROM ChequeSummaryTemp WHERE ProductID = {0}", parameters : myParam); string query = "SELECT CT.ID AS ID, PD.ID AS ProductID, CT.ClaimID AS ClaimID, " + "PY.ID AS PayeeID, PY.Name AS Payee, CT.InvoiceNumber AS InvoiceNumber, " + "AC.AccountCode AS AccountCode, AF.Name AS Affected, " + "CT.Authorised AS Authorised, CT.Amount AS Amount, " + "CN.LastName + ' ' + CN.FirstName AS Client, IR.Name AS Insurer, " + "PD.Name AS Product " + "FROM ClaimTransaction AS CT INNER JOIN Payee AS PY ON CT.PayeeID = PY.ID " + "INNER JOIN Claim AS CL ON CT.ClaimID = CL.ID " + "INNER JOIN Policy AS PL ON CL.PolicyID = PL.ID " + "INNER JOIN Client AS CN ON PL.ClientID = CN.ID " + "INNER JOIN Affected AS AF ON CT.AffectedID = AF.ID " + "INNER JOIN AccountChart AS AC ON CT.AccountID = AC.ID " + "INNER JOIN Insurer AS IR ON PL.InsurerID = IR.ID " + "INNER JOIN Product PD ON PL.ProductID = PD.ID " + "WHERE CT.Authorised = 1 AND CT.Amount > 0 " + "AND CT.HoldForPayment = 0 AND PassForPayment = 1 " + "AND PayableID IS NULL AND PD.ID = {0} " + "ORDER BY PY.Name"; var chequetransactions = await _context.ChequeTemps .FromSql(query, myParam) .AsNoTracking() .ToListAsync(); foreach (ChequeTemp c in chequetransactions) { _context.ChequeTemps.Add(c); } _context.SaveChanges(); string query1 = "SELECT PY.ID AS ID, PD.ID AS ProductID, PY.Name AS Payee, " + "NULL AS PostalAddress, NULL AS City, " + "SUM(CT.Amount) AS Amount, COUNT(*) AS PayeeCount " + "FROM ClaimTransaction AS CT INNER JOIN Payee AS PY ON CT.PayeeID = PY.ID " + "INNER JOIN Claim AS CL ON CT.ClaimID = CL.ID " + "INNER JOIN Policy AS PL ON CL.PolicyID = PL.ID " + "INNER JOIN Client AS CN ON PL.ClientID = CN.ID " + "INNER JOIN Affected AS AF ON CT.AffectedID = AF.ID " + "INNER JOIN AccountChart AS AC ON CT.AccountID = AC.ID " + "INNER JOIN Insurer AS IR ON PL.InsurerID = IR.ID " + "INNER JOIN Product PD ON PL.ProductID = PD.ID " + "WHERE CT.Authorised = 1 AND CT.Amount > 0 " + "AND CT.HoldForPayment = 0 AND PassForPayment = 1 " + "AND PayableID IS NULL AND PD.ID = {0} " + "GROUP BY PY.ID, PD.ID, PY.Name " + "ORDER BY PY.Name"; var chequesummary = await _context.ChequeSummaryTemps .FromSql(query1, myParam) .AsNoTracking() .ToListAsync(); int rowCount = chequesummary.Count(); object[,] bankaccounts = new object[rowCount, 5]; int i = 0; bool allaccounts = true; foreach (ChequeSummaryTemp c in chequesummary) { // Get PayeeID and check if Payee has Bank Account Number // Then populate the BankAccount Array List <string> accountnumber = GetBankAccount(c.PayeeID); bankaccounts[i, 0] = c.PayeeID; bankaccounts[i, 1] = c.Payee; bankaccounts[i, 2] = accountnumber[0]; // Account Number bankaccounts[i, 3] = accountnumber[1]; // BIC bankaccounts[i, 4] = accountnumber[2]; // Bank allaccounts = (accountnumber.Count == 0) ? false : true; // Get Payee Address List <string> PayeeAddress = GetPayeeAddress(c.PayeeID); c.PostalAddress = PayeeAddress[0]; c.City = PayeeAddress[1]; _context.ChequeSummaryTemps.Add(c); i++; } _context.SaveChanges(); PaymentProcessingViewModel viewModel = new PaymentProcessingViewModel { ProductID = productId, ProductName = ProductName(productId), ChequeTemps = chequetransactions, ChequeSummaryTemps = chequesummary, BankAccounts = bankaccounts, AllAccounts = allaccounts, PaymentTypeList = new SelectList(_context.PaymentTypes, "ID", "Name", _context.PaymentTypes.FirstOrDefault().ID), PaymentTypeID = _context.PaymentTypes.FirstOrDefault().ID }; return(View(viewModel)); }
void PopulateFormatTypes(Guid loadFormatTypeId) { var formatTypes = new FormatType[] { new FormatType { ID = Guid.NewGuid(), LoadFormatID = loadFormatTypeId, TableName = "Client", FieldName = "Title", FieldLabel = "Title", Position = null, ColumnLength = 0, IsKey = false }, new FormatType { ID = Guid.NewGuid(), LoadFormatID = loadFormatTypeId, TableName = "Client", FieldName = "FirstName", FieldLabel = "First Name", Position = null, ColumnLength = 0, IsKey = false }, new FormatType { ID = Guid.NewGuid(), LoadFormatID = loadFormatTypeId, TableName = "Client", FieldName = "LastName", FieldLabel = "Last Name", Position = null, ColumnLength = 0, IsKey = false }, new FormatType { ID = Guid.NewGuid(), LoadFormatID = loadFormatTypeId, TableName = "Client", FieldName = "BirthDate", FieldLabel = "Birth Date", Position = null, ColumnLength = 0, IsKey = false }, new FormatType { ID = Guid.NewGuid(), LoadFormatID = loadFormatTypeId, TableName = "Client", FieldName = "Gender", FieldLabel = "Gender", Position = null, ColumnLength = 0, IsKey = false }, new FormatType { ID = Guid.NewGuid(), LoadFormatID = loadFormatTypeId, TableName = "Client", FieldName = "IDNumber", FieldLabel = "ID Number", Position = null, ColumnLength = 0, IsKey = true }, new FormatType { ID = Guid.NewGuid(), LoadFormatID = loadFormatTypeId, TableName = "Client", FieldName = "Occupation", FieldLabel = "Occupation", Position = null, ColumnLength = 0, IsKey = false }, new FormatType { ID = Guid.NewGuid(), LoadFormatID = loadFormatTypeId, TableName = "Client", FieldName = "Country", FieldLabel = "Country", Position = null, ColumnLength = 0, IsKey = false }, new FormatType { ID = Guid.NewGuid(), LoadFormatID = loadFormatTypeId, TableName = "Policy", FieldName = "IDNumber", FieldLabel = "ID Number", Position = null, ColumnLength = 0, IsKey = true }, new FormatType { ID = Guid.NewGuid(), LoadFormatID = loadFormatTypeId, TableName = "Policy", FieldName = "InsurerNumber", FieldLabel = "Insurer Number", Position = null, ColumnLength = 0, IsKey = false }, new FormatType { ID = Guid.NewGuid(), LoadFormatID = loadFormatTypeId, TableName = "Policy", FieldName = "CoverStartDate", FieldLabel = "Cover Start Date", Position = null, ColumnLength = 0, IsKey = false }, new FormatType { ID = Guid.NewGuid(), LoadFormatID = loadFormatTypeId, TableName = "Policy", FieldName = "CoverEndDate", FieldLabel = "Cover End Date", Position = null, ColumnLength = 0, IsKey = false }, new FormatType { ID = Guid.NewGuid(), LoadFormatID = loadFormatTypeId, TableName = "Premium", FieldName = "IDNumber", FieldLabel = "ID Number", Position = null, ColumnLength = 0, IsKey = true }, new FormatType { ID = Guid.NewGuid(), LoadFormatID = loadFormatTypeId, TableName = "Premium", FieldName = "PremiumDate", FieldLabel = "Premium Date", Position = null, ColumnLength = 0, IsKey = true }, new FormatType { ID = Guid.NewGuid(), LoadFormatID = loadFormatTypeId, TableName = "Premium", FieldName = "Amount", FieldLabel = "Premium", Position = null, ColumnLength = 0, IsKey = false }, new FormatType { ID = Guid.NewGuid(), LoadFormatID = loadFormatTypeId, TableName = "Claim", FieldName = "ClaimID", FieldLabel = "Claim Number", Position = null, ColumnLength = 0, IsKey = true }, new FormatType { ID = Guid.NewGuid(), LoadFormatID = loadFormatTypeId, TableName = "Claim", FieldName = "ReportDate", FieldLabel = "Report Date", Position = null, ColumnLength = 0, IsKey = false }, new FormatType { ID = Guid.NewGuid(), LoadFormatID = loadFormatTypeId, TableName = "Claim", FieldName = "IncidentDate", FieldLabel = "Incident Date", Position = null, ColumnLength = 0, IsKey = false }, new FormatType { ID = Guid.NewGuid(), LoadFormatID = loadFormatTypeId, TableName = "Claim", FieldName = "RegisterDate", FieldLabel = "Register Date", Position = null, ColumnLength = 0, IsKey = false }, new FormatType { ID = Guid.NewGuid(), LoadFormatID = loadFormatTypeId, TableName = "Claim", FieldName = "ReserveInsured", FieldLabel = "Reserve Insured", Position = null, ColumnLength = 0, IsKey = false }, new FormatType { ID = Guid.NewGuid(), LoadFormatID = loadFormatTypeId, TableName = "Claim", FieldName = "ReserveThirdParty", FieldLabel = "Reserve Third Party", Position = null, ColumnLength = 0, IsKey = false }, new FormatType { ID = Guid.NewGuid(), LoadFormatID = loadFormatTypeId, TableName = "Claim", FieldName = "ReserveInsuredRevised", FieldLabel = "Reserve Insured Revised", Position = null, ColumnLength = 0, IsKey = false }, new FormatType { ID = Guid.NewGuid(), LoadFormatID = loadFormatTypeId, TableName = "Claim", FieldName = "ReserveThirdPartyRevised", FieldLabel = "Reserve Third Party Revised", Position = null, ColumnLength = 0, IsKey = false }, new FormatType { ID = Guid.NewGuid(), LoadFormatID = loadFormatTypeId, TableName = "Claim", FieldName = "ClaimExcess", FieldLabel = "Claim Excess", Position = null, ColumnLength = 0, IsKey = false }, new FormatType { ID = Guid.NewGuid(), LoadFormatID = loadFormatTypeId, TableName = "Claim", FieldName = "RecoverFromThirdParty", FieldLabel = "Recover From Third Party", Position = null, ColumnLength = 0, IsKey = false }, new FormatType { ID = Guid.NewGuid(), LoadFormatID = loadFormatTypeId, TableName = "Claim", FieldName = "ManualClaimNumber", FieldLabel = "Manual Claim Number", Position = null, ColumnLength = 0, IsKey = false }, new FormatType { ID = Guid.NewGuid(), LoadFormatID = loadFormatTypeId, TableName = "Claim", FieldName = "InsurerClaimNumber", FieldLabel = "Insurer Claim Number", Position = null, ColumnLength = 0, IsKey = false }, }; foreach (FormatType f in formatTypes) { _context.FormatTypes.Add(f); } _context.SaveChanges(); }