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