// GET: Products/LoadPremiums
public async Task <IActionResult> LoadPremiums(Guid userId, Guid productId, int riskId)
{
var premiumtemps = await(from p in _context.PremiumTemps
.Where(u => u.UserID == userId)
.Include(p => p.PremiumType)
.OrderBy(i => i.IDNumber)
select p).ToListAsync();
var premiumTotal = premiumtemps.Sum(p => p.Amount);
PremiumTempsViewModel viewModel = new PremiumTempsViewModel
{
PremiumTemps = premiumtemps,
ProductID = productId,
UserID = userId,
RiskID = riskId,
PremiumTotal = premiumTotal,
Reference = _context.PremiumTemps.FirstOrDefault().Reference,
ReceivableDate = _context.PremiumTemps.FirstOrDefault().ReceivableDate,
PaymentTypeID = _context.PremiumTemps.FirstOrDefault().PaymentTypeID,
PaymentAmount = _context.PremiumTemps.FirstOrDefault().PaymentAmount,
BatchNumber = _context.PremiumTemps.FirstOrDefault().BatchNumber
};
return(View(viewModel));
}
public async Task <IActionResult> LoadPremiumsConfirmed(PremiumTempsViewModel viewModel)
{
var currentUserId = viewModel.UserID;
var currentProductId = viewModel.ProductID;
var currentRiskId = viewModel.RiskID;
BulkHandles bulkhandle = new BulkHandles();
int newbulkhandle = bulkhandle.GetBulkHandle(_context);
var premiumtemps = from p in _context.PremiumTemps
.Where(u => u.UserID == currentUserId)
.Include(p => p.PremiumType)
.OrderBy(i => i.IDNumber)
select p;
int recCount = premiumtemps.Count();
var recParam = new object[] { Guid.NewGuid(), currentProductId, viewModel.Reference, viewModel.ReceivableDate, viewModel.PaymentTypeID,
viewModel.PaymentAmount, viewModel.BatchNumber, DateTime.Now, currentUserId, null, null };
await _context.Database.ExecuteSqlCommandAsync(
"INSERT INTO Receivable(ID, ProductID, Reference, ReceivableDate, PaymentTypeID, Amount, BatchNumber, " +
"DateAdded, AddedBy, DateModified, ModifiedBy) " +
"Values({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10})",
parameters : recParam);
Guid receivableId = GetReceivableId(viewModel.Reference);
foreach (var p in premiumtemps)
{
if (PolicyExists(p.IDNumber))
{
var policyId = GetPolicyId(p.IDNumber);
// Collect RiskItemID using PolicyID and RiskID
// Split the premium if more than 1 RiskItemIDs
object[,] riskItemPremium = GetRiskItem(policyId, currentRiskId);
var availablePremium = p.Amount;
if (riskItemPremium.Length > 0)
{
for (int i = 0; i < riskItemPremium.GetUpperBound(0) + 1; i++)
{
var currentRiskItemId = Guid.Parse(riskItemPremium[i, 0].ToString());
// Split the Premium Amount if more than one risk item
var riskitempremium = decimal.Parse(riskItemPremium[i, 1].ToString());
var balancePremium = availablePremium - riskitempremium;
if (balancePremium > -1)
{
p.Amount = riskitempremium;
availablePremium = balancePremium;
}
else
{
p.Amount = availablePremium;
availablePremium = 0;
}
// Calculate Policy Fee, Commission and Admin Fee
// using currentproductId and premium amount
List <decimal> BrokerCharges = GetBrokerCharges(viewModel.ProductID, currentRiskId, p.Amount);
p.PolicyFee = BrokerCharges[0];
p.Commission = BrokerCharges[1];
p.AdminFee = BrokerCharges[2];
var premiumParams = new object[] { Guid.NewGuid(), policyId, p.RiskID, currentRiskItemId, p.PremiumDate, p.PremiumTypeID,
p.Amount, p.PolicyFee, p.Commission, p.AdminFee, receivableId, newbulkhandle, DateTime.Now, currentUserId, null, null };
await _context.Database.ExecuteSqlCommandAsync(
"INSERT INTO Premium(ID, PolicyID, RiskID, RiskItemID, PremiumDate, PremiumTypeID, Amount, PolicyFee, " +
"Commission, AdminFee, ReceivableID, " +
"BulkHandle, DateAdded, AddedBy, DateModified, ModifiedBy) " +
"Values ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, {13}, {14}, {15})",
parameters : premiumParams);
}
}
}
else
{
// Generate a log file and ask user to load on Client and Policy before loading premiums again.
}
}
Guid myParam = currentUserId;
await _context.Database.ExecuteSqlCommandAsync(
"DELETE FROM PremiumTemp WHERE UserID = {0}",
parameters : myParam);
var bulkParams = new object[] { newbulkhandle, "Premium", DateTime.Now, recCount, DateTime.Now, currentUserId };
await _context.Database.ExecuteSqlCommandAsync(
"INSERT INTO BulkHandleGenerator(BulkNumber, TableName, BulkDate, RecordCount, DateAdded, AddedBy) " +
"Values ({0}, {1}, {2}, {3}, {4}, {5})",
parameters : bulkParams);
return(RedirectToAction("ProductClients", "Products", new { productId = currentProductId }));
}
