public static List <Entities.PortfolioStock> GetAll(Entities.Portfolio p)
{
List <Entities.PortfolioStock> result = new List <Entities.PortfolioStock>();
string SelecetUserPortfolio = "SELECT * FROM PortfolioStock WHERE GameID=@GameID";
SqlCommand cmdGetPortfolioStock = new SqlCommand(SelecetUserPortfolio, Globals.Db.conn);
cmdGetPortfolioStock.Parameters.Add("@GameID", SqlDbType.Int).Value = p.PortfolioID;
using (SqlDataReader reader = cmdGetPortfolioStock.ExecuteReader())
{
while (reader.Read())
{
string symbol = (string)reader["Symbol"];
Console.Write(symbol);
int id = Convert.ToInt32(reader["GameID"]);
int numberOfSharesOwned = Convert.ToInt32(reader["NumberOfSharesOwned"]);
Decimal averagePurchasePrice = Convert.ToDecimal(reader["averagePurchasePrice"]);
Entities.PortfolioStock pn = new Entities.PortfolioStock(symbol, id, numberOfSharesOwned, averagePurchasePrice);
result.Add(pn);
}
}
return(result);
}
public static List <Entities.Transaction> GetAll(Entities.Portfolio p)
{
List <Entities.Transaction> result = new List <Entities.Transaction>();
string sql = "SELECT * FROM Transactions WHERE PortfolioID=@PortfolioID";
SqlCommand cmdGetAllTransactions = new SqlCommand(sql, Globals.Db.conn);
cmdGetAllTransactions.Parameters.Add("@PortfolioId", SqlDbType.Int).Value = p.PortfolioID;
using (SqlDataReader reader = cmdGetAllTransactions.ExecuteReader())
{
while (reader.Read())
{
int id = (int)reader["Id"];
int portfolioID = (int)reader["portfolioId"];
string type = (string)reader["Type"];
string symbol = (string)reader["Symbol"];
decimal price = (decimal)reader["BuySellPrice"];
int sharesBoughtSold = (int)reader["SharesBoughtSold"];
DateTime date = (DateTime)reader["date"];
Entities.Transaction t = new Entities.Transaction(id, portfolioID, type, symbol, price, sharesBoughtSold, date);
result.Add(t);
}
}
return(result);
}
public static Entities.Portfolio GetUpdatedPortfolio(Entities.Portfolio p)
{
Entities.Portfolio updatedPortfolio = new Entities.Portfolio(1, "temp", "Temp", 0, 0, 0);
String sql = "SELECT * FROM Portfolio WHERE PortfolioId=@PortfolioId";
SqlCommand cmdGetPortfolio = new SqlCommand(sql, Globals.Db.conn);
cmdGetPortfolio.Parameters.Add("@PortfolioId", SqlDbType.Int).Value = p.PortfolioID;
using (SqlDataReader reader = cmdGetPortfolio.ExecuteReader())
{
while (reader.Read())
{
int id = (int)reader["portfolioId"];
string name = (string)reader["Name"];
string email = (string)reader["Email"];
decimal cash = (decimal)reader["Cash"];
decimal net = (decimal)reader["Net"];
decimal balance = (decimal)reader["Balance"];
updatedPortfolio = new Entities.Portfolio(id, name, email, cash, net, balance);
}
}
return(updatedPortfolio);
}
public static void AddNewPortfolioToTable(Entities.Portfolio p)
{
string sql = "INSERT INTO Portfolio (Name, Email) VALUES (@Name,@Email)";
SqlCommand cmd = new SqlCommand(sql, Globals.Db.conn);
cmd.Parameters.Add("@Name", SqlDbType.NVarChar).Value = p.Name;
cmd.Parameters.Add("@Email", SqlDbType.NVarChar).Value = p.Email;
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
public static void UpdateNet(decimal net, Entities.Portfolio p)
{
string sql = "UPDATE Portfolio " +
"SET Net=@Net " +
"WHERE PortfolioId=@PortfolioId";
SqlCommand cmd = new SqlCommand(sql, Globals.Db.conn);
cmd.Parameters.Add("@Net", SqlDbType.Money).Value = net;
cmd.Parameters.Add("@PortfolioId", SqlDbType.Int).Value = p.PortfolioID;
cmd.ExecuteNonQuery();
}
public void AddPortfolioStock(Entities.Portfolio p, Entities.StockDb s, int quantity)
{
string sqlAddToPortfolioStock = "INSERT INTO PortfolioStock (Symbol, GameID, NumberOfSharesOwned, AveragePurchasePrice)" +
"Values (@Symbol, @GameID, @NumberOfSharesOwned, @AveragePurchasePrice)";
SqlCommand cmdInsertStock = new SqlCommand(sqlAddToPortfolioStock, conn);
cmdInsertStock.Parameters.Add("@Symbol", SqlDbType.NChar).Value = s.Symbol;
cmdInsertStock.Parameters.Add("@GameID", SqlDbType.Int).Value = p.PortfolioID;
cmdInsertStock.Parameters.Add("@NumberOfSharesOwned", SqlDbType.Int).Value = quantity;
cmdInsertStock.Parameters.Add("@AveragePurchasePrice", SqlDbType.Money).Value = s.Ask;
cmdInsertStock.ExecuteNonQuery();
}
public List <String> GetAllStockOwnedByUser(Entities.Portfolio p)
{
List <String> result = new List <String>();
String sqlGetAllStockOwnedByUser = "******";
SqlCommand cmdInsertStock = new SqlCommand(sqlGetAllStockOwnedByUser, conn);
cmdInsertStock.Parameters.Add("@GameID", SqlDbType.Int).Value = p.PortfolioID;
using (SqlDataReader reader = cmdInsertStock.ExecuteReader())
{
while (reader.Read())
{
result.Add(reader.GetString(0));
}
}
return(result);
}
public static List <Entities.Portfolio> GetAll()
{
List <Entities.Portfolio> result = new List <Entities.Portfolio>();
using (SqlCommand command = new SqlCommand("SELECT * FROM Portfolio", Globals.Db.conn))
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
int id = (int)reader["portfolioId"];
string name = (string)reader["Name"];
string email = (string)reader["Email"];
decimal cash = (decimal)reader["Cash"];
decimal net = (decimal)reader["Net"];
decimal balance = (decimal)reader["Balance"];
Entities.Portfolio p = new Entities.Portfolio(id, name, email, cash, net, balance);
result.Add(p);
}
}
return(result);
}
////Adding a transaction buy
public void AddBuyTransaction(Entities.Portfolio p, Entities.StockDb s, int quantity)
{
string sqlBuy = "INSERT INTO Transactions (PortfolioId, Type, Symbol, BuySellPrice, SharesBoughtSold, Date)"
+ "VALUES (@PortfolioId, @Type, @Symbol, @Ask, @SharesBought, @Date)";
SqlCommand cmd = new SqlCommand(sqlBuy, conn);
cmd.Parameters.Add("@PortfolioId", SqlDbType.Int).Value = p.PortfolioID;
cmd.Parameters.Add("@Type", SqlDbType.NChar).Value = "Buy";
cmd.Parameters.Add("@Symbol", SqlDbType.NChar).Value = s.Symbol;
cmd.Parameters.Add("@Ask", SqlDbType.Money).Value = s.Ask;
cmd.Parameters.Add("@SharesBought", SqlDbType.Int).Value = quantity;
cmd.Parameters.Add("@Date", SqlDbType.DateTime).Value = DateTime.Now;
cmd.ExecuteNonQuery();
string sqlUpdateCash = "Update Portfolio SET Cash=@Cash Where PortfolioId=@PortfolioId";
SqlCommand cmdUpdate = new SqlCommand(sqlUpdateCash, conn);
cmdUpdate.Parameters.Add("@PortfolioId", SqlDbType.Int).Value = p.PortfolioID;
cmdUpdate.Parameters.Add("@Cash", SqlDbType.Money).Value = p.Cash - (s.Ask * quantity);
cmdUpdate.ExecuteNonQuery();
}
public void UpdatePortfolioStock(Entities.Portfolio p, Entities.StockDb s, int quantity)
{
int finalQty = 0;
decimal newAverage = 0;
string sqlGetVolumePrice = "SELECT NumberOfSharesOwned, AveragePurchasePrice FROM PortfolioStock WHERE Symbol=@Symbol AND GameID=@GameID";
SqlCommand cmdGetVolumePrice = new SqlCommand(sqlGetVolumePrice, conn);
cmdGetVolumePrice.Parameters.Add("@Symbol", SqlDbType.NVarChar).Value = s.Symbol;
cmdGetVolumePrice.Parameters.Add("@GameID", SqlDbType.Int).Value = p.PortfolioID;
int quantityDB;
decimal priceDB;
// this part is needed to convert decimal? to decimal
decimal askPrice = (decimal)s.Ask;
using (SqlDataReader rd = cmdGetVolumePrice.ExecuteReader())
{
while (rd.Read())
{
//rd.NextResult();
//var quantityDB = (int)rd["NumberOfSharesOwned"];
quantityDB = Convert.ToInt32(rd["NumberOfSharesOwned"]);
priceDB = (decimal)rd["AveragePurchasePrice"];
decimal DatabaseTotalPrice = priceDB * quantityDB;
decimal NewTotalPrice = quantity * askPrice;
finalQty = quantityDB + quantity;
newAverage = (DatabaseTotalPrice + NewTotalPrice) / finalQty;
finalQty = quantityDB + quantity;
newAverage = (DatabaseTotalPrice + NewTotalPrice) / finalQty;
}
}
string sqlAddToPortfolioStock = "UPDATE PortfolioStock SET NumberOfSHaresOWned=@NumberOfSHaresOWned, AveragePurchasePrice=@AveragePurchasePrice " +
"WHERE Symbol=@Symbol AND GameID=@GameID";
SqlCommand cmdUpdate = new SqlCommand(sqlAddToPortfolioStock, conn);
cmdUpdate.Parameters.Add("@Symbol", SqlDbType.NChar).Value = s.Symbol;
cmdUpdate.Parameters.Add("@GameID", SqlDbType.Int).Value = p.PortfolioID;
cmdUpdate.Parameters.Add("@NumberOfSharesOwned", SqlDbType.Int).Value = finalQty;
cmdUpdate.Parameters.Add("@AveragePurchasePrice", SqlDbType.Money).Value = newAverage;
cmdUpdate.ExecuteNonQuery();
Console.Write("END");
cmdUpdate.ExecuteNonQuery();
}
public void AddSellTransaction(String symbol, int qty, decimal sellPrice, Entities.PortfolioStock p, Entities.Portfolio up)
{
string sqlBuy = "INSERT INTO Transactions (PortfolioId, Type, Symbol, BuySellPrice, SharesBoughtSold, Date)"
+ "VALUES (@PortfolioId, @Type, @Symbol, @Bit, @SharesBought, @Date)";
SqlCommand cmd = new SqlCommand(sqlBuy, conn);
cmd.Parameters.Add("@PortfolioId", SqlDbType.Int).Value = p.PortfolioId;
cmd.Parameters.Add("@Type", SqlDbType.NChar).Value = "Sell";
cmd.Parameters.Add("@Symbol", SqlDbType.NChar).Value = symbol;
cmd.Parameters.Add("@Bit", SqlDbType.Money).Value = sellPrice;
cmd.Parameters.Add("@SharesBought", SqlDbType.Int).Value = qty;
cmd.Parameters.Add("@Date", SqlDbType.DateTime).Value = DateTime.Now;
cmd.ExecuteNonQuery();
string sqlUpdateCash = "Update Portfolio SET Cash=@Cash Where PortfolioId=@PortfolioId";
SqlCommand cmdUpdate = new SqlCommand(sqlUpdateCash, conn);
cmdUpdate.Parameters.Add("@PortfolioId", SqlDbType.Int).Value = p.PortfolioId;
cmdUpdate.Parameters.Add("@Cash", SqlDbType.Money).Value = up.Cash + (sellPrice * qty);
cmdUpdate.ExecuteNonQuery();
string sqlUpdatPortfolioStock = "Update PortfolioStock SET NumberOfSharesOwned=@NumberOfSharesOwned Where GameID=@GameID AND Symbol=@Symbol";
SqlCommand cmdUpdatePortfolioStock = new SqlCommand(sqlUpdatPortfolioStock, conn);
cmdUpdatePortfolioStock.Parameters.Add("@GameID", SqlDbType.Int).Value = p.PortfolioId;
cmdUpdatePortfolioStock.Parameters.Add("@Symbol", SqlDbType.NChar).Value = symbol;
cmdUpdatePortfolioStock.Parameters.Add("@NumberOfSharesOwned", SqlDbType.Int).Value = p.SharesOwned - qty;
cmdUpdatePortfolioStock.ExecuteNonQuery();
}
