public Portfolio GetPortfolio(string tradingAccountID)
{
Portfolio portfolio = new Portfolio();
Buy buy = null;
Sell sell = null;
using (SqlConnection con = new SqlConnection(databaseConnection))
{
using (SqlCommand cmd = new SqlCommand("spGetPortfolio", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("TradingAccountID", tradingAccountID));
con.Open();
cmd.ExecuteNonQuery();
SqlDataReader sqlReader = cmd.ExecuteReader();
while (sqlReader.Read())
{
portfolio.PortfolioID = Convert.ToInt32(sqlReader["PortfolioID"].ToString());
portfolio.TradingAccountID = sqlReader["TradingAccountID"].ToString();
}
}
con.Close();
}
portfolio.Buys = new List <Buy>();
using (SqlConnection con = new SqlConnection(databaseConnection))
{
using (SqlCommand cmd = new SqlCommand("spGetBuys", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@PortfolioID", portfolio.PortfolioID));
con.Open();
cmd.ExecuteNonQuery();
SqlDataReader sqlReader = cmd.ExecuteReader();
while (sqlReader.Read())
{
if (Convert.ToInt32(sqlReader["Quantity"].ToString()) > 0)
{
buy = new Buy();
buy.BuyID = Convert.ToInt32(sqlReader["BuyID"].ToString());
buy.TradeDate = Convert.ToDateTime(sqlReader["TradeDate"].ToString());
buy.PortfolioId = Convert.ToInt32(sqlReader["PortfolioId"].ToString());
buy.TickerSymbol = sqlReader["TickerSymbol"].ToString();
buy.Quantity = Convert.ToInt32(sqlReader["Quantity"].ToString());
buy.PurchasePrice = Convert.ToDecimal(sqlReader["PurchasePrice"].ToString());
buy.TransactionAmount = Convert.ToDecimal(sqlReader["TransactionAmount"].ToString());
buy.TransactionCost = Convert.ToDecimal(sqlReader["TransactionCost"].ToString());
}
}
}
con.Close();
}
portfolio.Sells = new List <Sell>();
using (SqlConnection con = new SqlConnection(databaseConnection))
{
using (SqlCommand cmd = new SqlCommand("spGetSells", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@PortfolioID", portfolio.PortfolioID));
con.Open();
cmd.ExecuteNonQuery();
SqlDataReader sqlReader = cmd.ExecuteReader();
while (sqlReader.Read())
{
sell = new Sell();
sell.SellID = Convert.ToInt32(sqlReader["SellID"].ToString());
sell.TradeDate = Convert.ToDateTime(sqlReader["TradeDate"].ToString());
sell.BuyID = Convert.ToInt32(sqlReader["BuyID"].ToString());
sell.TickerSymbol = sqlReader["TickerSymbol"].ToString();
sell.Quantity = Convert.ToInt32(sqlReader["Quantity"].ToString());
sell.PurchasePrice = Convert.ToDecimal(sqlReader["PurchasePrice"].ToString());
sell.SoldPrice = Convert.ToDecimal(sqlReader["SoldPrice"].ToString());
sell.TransactionAmount = Convert.ToDecimal(sqlReader["TransactionAmount"].ToString());
sell.TransactionCost = Convert.ToDecimal(sqlReader["TransactionCost"].ToString());
sell.PortfolioID = Convert.ToInt32(sqlReader["PortfolioID"].ToString());
portfolio.Sells.Add(sell);
}
}
con.Close();
}
return(portfolio);
}
public List <SortWinner> GetTopThree(ref List <TraderAccount> traders, ref List <Owner> owners, ref List <SortWinner> winners)
{
Owner owner = null;
TraderAccount trader = null;
List <Buy> buys = new List <Buy>();
List <Sell> sells = new List <Sell>();
List <OlympianBoard> board = new List <OlympianBoard>();
Portfolio portfolio = null;
List <Portfolio> portfolios = new List <Portfolio>();
List <PortfolioViewModel> portfolioView = new List <PortfolioViewModel>();
using (SqlConnection con = new SqlConnection(databaseConnection))
{
using (SqlCommand cmd = new SqlCommand("spWildcardOwner", con))
{
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
cmd.ExecuteNonQuery();
SqlDataReader sqlReader = cmd.ExecuteReader();
while (sqlReader.Read())
{
owner = new Owner();
owner.OwnerID = sqlReader["OwnerID"].ToString();
owner.UserID = sqlReader["UserID"].ToString();
owner.FirstName = sqlReader["FirstName"].ToString();
owner.MiddleName = sqlReader["MiddleName"].ToString();
owner.LastName = sqlReader["LastName"].ToString();
owner.DOB = sqlReader["DOB"].ToString();
owner.AddressNumber = sqlReader["AddressNumber"].ToString();
owner.AddressName = sqlReader["AddressName"].ToString();
owner.PostcodeID = sqlReader["PostcodeID"].ToString();
owners.Add(owner);
}
}
con.Close();
}
using (SqlConnection con = new SqlConnection(databaseConnection))
{
using (SqlCommand cmd = new SqlCommand("spWildcardPortfolio", con))
{
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
cmd.ExecuteNonQuery();
SqlDataReader sqlReader = cmd.ExecuteReader();
while (sqlReader.Read())
{
portfolio = new Portfolio();
portfolio.PortfolioID = Convert.ToInt32(sqlReader["PortfolioID"].ToString());
portfolio.TradingAccountID = sqlReader["TradingAccountID"].ToString();
portfolios.Add(portfolio);
}
}
con.Close();
}
using (SqlConnection con = new SqlConnection(databaseConnection))
{
using (SqlCommand cmd = new SqlCommand("spWildcardTraderAccount", con))
{
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
cmd.ExecuteNonQuery();
SqlDataReader sqlReader = cmd.ExecuteReader();
while (sqlReader.Read())
{
trader = new TraderAccount();
trader.TradingAccountID = sqlReader["TradingAccountID"].ToString();
trader.OwnerID = sqlReader["OwnerID"].ToString();
trader.CreationDate = Convert.ToDateTime(sqlReader["CreationDate"].ToString());
trader.Balance = Convert.ToDecimal(sqlReader["Balance"].ToString());
traders.Add(trader);
}
}
con.Close();
}
List <PortfolioViewModel> PortfolioModelList = new List <PortfolioViewModel>();
PortfolioViewModel portfolioViewModel = null;
foreach (var trade in traders)
{
portfolioViewModel = null;
portfolioViewModel = GetPortfolioVodelModel(trade.TradingAccountID);
DataSerializer <string> .GetCurrentPerformance(ref portfolioViewModel);
PortfolioModelList.Add(portfolioViewModel);
}
foreach (var item in PortfolioModelList)
{
SortWinner winner = new SortWinner();
winner.TradingAcountID = item.TradingAccountID;
winner.PortfolioID = item.PortfolioID;
winners.Add(winner);
}
foreach (var winner in winners)
{
foreach (var item in PortfolioModelList)
{
foreach (var items in item.Buys)
{
if (winner.PortfolioID == items.PortfolioId)
{
winner.Amount += items.Performance;
winner.Amount += items.TransactionAmount;
winner.Amount = winner.Amount - items.TransactionCost;
winner.Amount = Math.Round(winner.Amount, 2);
items.PortfolioId = 0;
}
}
}
}
foreach (var winner in winners)
{
foreach (var trade in traders)
{
if (trade.TradingAccountID.Equals(winner.TradingAcountID))
{
winner.OwnerID = trade.OwnerID;
winner.Amount += trade.Balance;
break;
}
}
}
foreach (var itemOwner in owners)
{
foreach (var itemWinner in winners)
{
if (itemWinner.OwnerID.Equals(itemOwner.OwnerID))
{
itemWinner.Name = itemOwner.FirstName + " " + itemOwner.LastName;
break;
}
}
}
for (int i = 0; i < winners.Count; i++)
{
for (int j = 0; j < winners.Count; j++)
{
if (winners[i].Amount > winners[j].Amount)
{
decimal tempAmount = winners[i].Amount;
string tradeIdTemp = winners[i].TradingAcountID;
int portIdTemp = winners[i].PortfolioID;
string ownerIdTemp = winners[i].OwnerID;
string nameTemp = winners[i].Name;
winners[i].Amount = winners[j].Amount;
winners[i].TradingAcountID = winners[j].TradingAcountID;
winners[i].PortfolioID = winners[j].PortfolioID;
winners[i].OwnerID = winners[j].OwnerID;
winners[i].Name = winners[j].Name;
winners[j].Amount = tempAmount;
winners[j].TradingAcountID = tradeIdTemp;
winners[j].PortfolioID = portIdTemp;
winners[j].OwnerID = ownerIdTemp;
winners[j].Name = nameTemp;
}
}
}
return(winners);
}
public Buy Finalizebuy(Buy model, string userID)
{
Owner owner = null;
TraderAccount account = null;
Portfolio portfolio = null;
List <Portfolio> portfolios = new List <Portfolio>();
int last = 0;
using (SqlConnection con = new SqlConnection(databaseConnection))
{
using (SqlCommand cmd = new SqlCommand("spGetOwner", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@UserID", userID));
con.Open();
cmd.ExecuteNonQuery();
SqlDataReader sqlReader = cmd.ExecuteReader();
while (sqlReader.Read())
{
owner = new Owner();
owner.OwnerID = sqlReader["OwnerID"].ToString();
owner.UserID = sqlReader["UserID"].ToString();
}
}
con.Close();
}
using (SqlConnection con = new SqlConnection(databaseConnection))
{
using (SqlCommand cmd = new SqlCommand("spTradingAccount", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@OwnerID", owner.OwnerID));
con.Open();
cmd.ExecuteNonQuery();
SqlDataReader sqlReader = cmd.ExecuteReader();
while (sqlReader.Read())
{
account = new TraderAccount();
account.TradingAccountID = sqlReader["TradingAccountID"].ToString();
account.OwnerID = sqlReader["OwnerID"].ToString();
account.CreationDate = Convert.ToDateTime(sqlReader["CreationDate"].ToString());
account.Balance = Convert.ToDecimal(sqlReader["Balance"].ToString());
}
}
con.Close();
}
using (SqlConnection con = new SqlConnection(databaseConnection))
{
using (SqlCommand cmd = new SqlCommand("spGetPortfolio", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@TradingAccountID", account.TradingAccountID));
con.Open();
cmd.ExecuteNonQuery();
SqlDataReader sqlReader = cmd.ExecuteReader();
while (sqlReader.Read())
{
portfolio = new Portfolio();
portfolio.PortfolioID = Convert.ToInt32(sqlReader["PortfolioID"].ToString());
portfolio.TradingAccountID = sqlReader["TradingAccountID"].ToString();
}
}
con.Close();
}
if (portfolio == null)
{
using (SqlConnection con = new SqlConnection(databaseConnection))
{
using (SqlCommand cmd = new SqlCommand("spGetLastPortfolio", con))
{
con.Open();
cmd.ExecuteNonQuery();
SqlDataReader sqlReader = cmd.ExecuteReader();
while (sqlReader.Read())
{
last = Convert.ToInt32(sqlReader["PortfolioID"].ToString());
}
}
con.Close();
}
DefaultConnection.Portfolios.Add(new Portfolio()
{
PortfolioID = last + 1,
TradingAccountID = account.TradingAccountID
});
DefaultConnection.SaveChanges();
model.PortfolioId = last + 1;
DefaultConnection.Buys.Add(model);
DefaultConnection.SaveChanges();
account.Balance = account.Balance - model.TransactionAmount;
DefaultConnection.Entry(account).State = EntityState.Modified;
DefaultConnection.SaveChanges();
}
else
{
model.PortfolioId = portfolio.PortfolioID;
DefaultConnection.Buys.Add(model);
DefaultConnection.SaveChanges();
account.Balance = account.Balance - model.TransactionAmount;
DefaultConnection.Entry(account).State = EntityState.Modified;
DefaultConnection.SaveChanges();
}
return(model);
}