public async Task <IActionResult> LoadLoansConfirmed(LoanTempsViewModel viewModel) { Guid currentUserId = viewModel.UserID; Guid currentproductId = viewModel.ProductID; BulkHandles bulkhandle = new BulkHandles(); int newbulkhandle = bulkhandle.GetBulkHandle(_context); var loantemps = from p in _context.LoanTemps .Where(u => u.UserID == currentUserId) .Include(p => p.Component) .OrderBy(i => i.IDNumber) select p; int recCount = loantemps.Count(); foreach (var l in loantemps) { Loan loan = new Loan(); if (PolicyExists(l.IDNumber)) { var loanParams = new object[] { Guid.NewGuid(), GetPolicyId(l.IDNumber), l.ComponentID, l.AccountNumber, l.Term, l.Rate, l.LoanDate, l.Value, l.Premium, l.SettlementDate, newbulkhandle, DateTime.Now, currentUserId, null, null }; await _context.Database.ExecuteSqlCommandAsync( "INSERT INTO Loan(ID, PolicyID, ComponentID, AccountNumber, Term, Rate, LoanDate, " + "Value, Premium, SettlementDate, BulkHandle, DateAdded, AddedBy, DateModified, ModifiedBy) " + "Values ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, {13}, {14})", parameters : loanParams); } 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 LoanTemp WHERE UserID = {0}", parameters : myParam); var bulkParams = new object[] { newbulkhandle, "Loan", 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 })); }
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 })); }