public async Task <IActionResult> Edit(int id, [Bind("ID,FwdDate,BankName,AuctionBidID,FwdRate,AmountBid,CouponAmount,WinAmount")] WinResults winResults)
{
if (id != winResults.ID)
{
return(NotFound());
}
if (ModelState.IsValid)
{
try
{
_context.Update(winResults);
await _context.SaveChangesAsync();
}
catch (DbUpdateConcurrencyException)
{
if (!WinResultsExists(winResults.ID))
{
return(NotFound());
}
else
{
throw;
}
}
return(RedirectToAction(nameof(Index)));
}
ViewData["AuctionBidID"] = new SelectList(_context.AuctionBids, "ID", "ID", winResults.AuctionBidID);
return(View(winResults));
}
public async Task <IActionResult> Create([Bind("ID,FwdDate,BankName,AuctionBidID,FwdRate,AmountBid,CouponAmount,WinAmount")] WinResults winResults)
{
if (ModelState.IsValid)
{
_context.Add(winResults);
await _context.SaveChangesAsync();
return(RedirectToAction(nameof(Index)));
}
ViewData["AuctionBidID"] = new SelectList(_context.AuctionBids, "ID", "ID", winResults.AuctionBidID);
return(View(winResults));
}
// Convert DataTable rows to Object
public WinResults ConvertRowToWinResult(DataRow dr)
{
WinResults winResult = new WinResults();
winResult.AuctionBidID = Convert.ToInt16(dr["ID"]);
winResult.FwdDate = Convert.ToDateTime(dr["FwdDate"]);
winResult.CouponAmount = Convert.ToDecimal(dr["CouponAmount"]);
winResult.BankName = Convert.ToString(dr["BankName"]);
winResult.AmountBid = Convert.ToDecimal(dr["AmountBid"]);
winResult.FwdRate = Convert.ToDouble(dr["FwdRate"]);
winResult.WinAmount = Convert.ToDecimal(dr["awarded_amount"]);
winResult.BatchRef = Convert.ToString(dr["BatchRef"]);
return(winResult);
}
public async Task <IActionResult> PerAuctionRun(BankPercViewModel data)
{
var testTotal = new Double();
foreach (var item in data.perbank)
{
testTotal += Convert.ToDouble(item.Percent);
}
//Get a list of dates in this Batch
List <DateTime> res = (from a in _context.AuctionBids
where a.BatchRef.ToString() == data.bidBatch
orderby a.FwdDate
select a.FwdDate).Distinct().ToList();
// Delete wins before running auction again
_context.WinResults.Where(w => w.BatchRef == data.bidBatch).ToList().ForEach(p => _context.WinResults.Remove(p));
if (testTotal == 100)
{
foreach (var date in res)
{
IQueryable <AuctionBid> bids = from j in _context.AuctionBids
where j.FwdDate == date
select j;
DataTable dTable = new DataTable();
dTable.Columns.Add("ID", typeof(int));
dTable.Columns.Add("FwdDate", typeof(DateTime));
dTable.Columns.Add("CouponAmount", typeof(decimal));
dTable.Columns.Add("BankName", typeof(string));
dTable.Columns.Add("AmountBid", typeof(decimal));
dTable.Columns.Add("FwdRate", typeof(double));
dTable.Columns.Add("awarded_amount", typeof(decimal));
dTable.Columns.Add("BatchRef", typeof(string));
foreach (var row in bids)
{
Decimal ratio = new Decimal();
ratio = Convert.ToDecimal(data.perbank.Where(x => x.BankName == row.BankName).Select(x => x.Percent).First());
DataRow dRow = dTable.NewRow();
dRow[0] = row.ID;
dRow[1] = row.FwdDate;
dRow[2] = row.CouponAmount;
dRow[3] = row.BankName;
dRow[4] = row.AmountBid;
dRow[5] = row.FwdRate;
dRow[6] = row.CouponAmount * ratio / 100;
dRow[7] = row.BatchRef;
dTable.Rows.Add(dRow);
}
// Convert Date wins to object and write to entity
foreach (DataRow row in dTable.Rows)
{
WinResults convertedObject = ConvertRowToWinResult(row);
if (convertedObject.WinAmount != 0)
{
_context.WinResults.Add(convertedObject);
}
}
await _context.SaveChangesAsync();
}
return(RedirectToAction("Index", "WinResults"));
}
else
{
ViewData["Message"] = "The total of the percentages is more or less than 100.";
return(View());
}
}
// Run Auction for a Batch
public async Task <IActionResult> RunAuction(string bidBatch, string auctionType)
{
//Get a list of dates in this Batch
List <DateTime> res = (from a in _context.AuctionBids
where a.BatchRef.ToString() == bidBatch
orderby a.FwdDate
select a.FwdDate).Distinct().ToList();
// Delete wins before running auction again
_context.WinResults.Where(x => x.BatchRef == bidBatch).ForEach(p => _context.WinResults.Remove(p));
if (auctionType == "bestrate")
{
foreach (var date in res)
{
// Direct SQL to pick winners for a date
string connectionString = "Server=JEROME-SBOOK\\SQLEXPRESS01;Database=MathaRx;Trusted_Connection=True;MultipleActiveResultSets=true;";
string sqlQry = "select ID, FwdDate, CouponAmount, BankName, AmountBid, FwdRate, BatchRef, case when remainder < 0 then remainder_1 else " +
"AmountBid end awarded_amount from (select *, LAG(remainder) over(order by FwdRate desc) remainder_1 " +
"from (select ID, FwdDate, CouponAmount, BankName, AmountBid, FwdRate, BatchRef, SUM(AmountBid) over (order by FwdRate desc) rtotal, " +
"CouponAmount - SUM(AmountBid) over (order by FwdRate desc) as remainder from AuctionBid where FwdDate=@fwdDate and BatchRef=@batchRef) tb) a where case when remainder < 0 then remainder_1 else AmountBid end >= 0";
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand comm = new SqlCommand(null, conn))
{
conn.Open();
comm.CommandText = sqlQry;
SqlParameter param1 = comm.Parameters.Add("@fwdDate", SqlDbType.DateTime2, 8);
SqlParameter param2 = comm.Parameters.Add("@batchRef", SqlDbType.NVarChar, 100);
param1.Value = date;
param2.Value = bidBatch;
comm.Prepare();
SqlDataReader reader = comm.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(reader);
conn.Close();
// Convert Date wins to object and write to entity
foreach (DataRow row in dt.Rows)
{
WinResults convertedObject = ConvertRowToWinResult(row);
if (convertedObject.WinAmount != 0)
{
_context.WinResults.Add(convertedObject);
}
}
await _context.SaveChangesAsync();
}
}
}
}
else if (auctionType == "waverage")
{
foreach (var date in res)
{
IQueryable <AuctionBid> bids = from j in _context.AuctionBids
where j.FwdDate == date
select j;
var totalAmt = bids.Select(x => x.AmountBid).Sum();
var bidArray = bids.ToArray();
DataTable dTable = new DataTable();
dTable.Columns.Add("ID", typeof(int));
dTable.Columns.Add("FwdDate", typeof(DateTime));
dTable.Columns.Add("CouponAmount", typeof(decimal));
dTable.Columns.Add("BankName", typeof(string));
dTable.Columns.Add("AmountBid", typeof(decimal));
dTable.Columns.Add("FwdRate", typeof(double));
dTable.Columns.Add("awarded_amount", typeof(decimal));
dTable.Columns.Add("BatchRef", typeof(string));
foreach (var row in bids)
{
DataRow dRow = dTable.NewRow();
dRow[0] = row.ID;
dRow[1] = row.FwdDate;
dRow[2] = row.CouponAmount;
dRow[3] = row.BankName;
dRow[4] = row.AmountBid;
dRow[5] = row.FwdRate;
dRow[6] = row.AmountBid / totalAmt * row.CouponAmount;
dRow[7] = row.BatchRef;
dTable.Rows.Add(dRow);
}
// Convert Date wins to object and write to entity
foreach (DataRow row in dTable.Rows)
{
WinResults convertedObject = ConvertRowToWinResult(row);
if (convertedObject.WinAmount != 0)
{
_context.WinResults.Add(convertedObject);
}
}
await _context.SaveChangesAsync();
}
}
else if (auctionType == "percentage")
{
List <string> banks = (from d in _context.AuctionBids
select d.BankName).Distinct().ToList();
var bankQ = _context.AuctionBids.Select(u => u.BankName).Distinct().ToList();
BankPercViewModel bankVM = new BankPercViewModel();
bankVM.ID = 1;
foreach (var item in bankQ)
{
bankVM.perbank.Add(new BankPercent {
BankName = item, Percent = "", ID = 1
});
}
bankVM.bidBatch = bidBatch;
return(View(bankVM));
}
return(RedirectToAction("Index", "WinResults"));
}