public Dictionary<string, List<Stock>> GetStockHistoryForDownload(string userId)
{
Dictionary<string, List<Stock>> dict = new Dictionary<string, List<Stock>>();
if (userId != null)
{
MySqlConnection conn = new MySqlConnection();
conn.CreateConn();
SqlCommand cmd = new SqlCommand("GetStockHistory", conn.Connection);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@UserId", userId));
conn.DataReader = cmd.ExecuteReader();
while (conn.DataReader.Read())
{
Stock userStock = new Stock();
string sname = conn.DataReader["StockName"].ToString();
int quantity = Convert.ToInt16(conn.DataReader["Quantity"]);
decimal price = Convert.ToDecimal(conn.DataReader["TransactionPrice"]);
int hasSold = Convert.ToInt16(conn.DataReader["HasSold"]);
if (hasSold == 1)
{
userStock.SoldPrice = price;
userStock.BoughtPrice = 0;
}
else
{
userStock.BoughtPrice = price;
userStock.SoldPrice = 0;
}
userStock.Symbol = sname;
userStock.NumShares = quantity;
if (dict.ContainsKey(sname))
{
dict[sname].Add(userStock);
}
else
{
List<Stock> userTransactions = new List<Stock>();
userTransactions.Add(userStock);
dict.Add(sname, userTransactions);
}
}
}
return dict;
}
public ActionResult AddNote(string stock, string note)
{
string userId = User.Identity.GetUserId();
stock = stock.ToUpper();
MySqlConnection conn = new MySqlConnection();
conn.CreateConn();
SqlCommand cmd = new SqlCommand("AddStockNote", conn.Connection);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@UserId", userId));
cmd.Parameters.Add(new SqlParameter("@StockName", stock));
cmd.Parameters.Add(new SqlParameter("@StockNote", note));
conn.Command = cmd;
conn.Command.Prepare();
conn.Command.ExecuteNonQuery();
return RedirectToAction("Index");
}
public ActionResult DeleteStockHistory()
{
string userId = User.Identity.GetUserId();
if (userId != null)
{
MySqlConnection conn = new MySqlConnection();
conn.CreateConn();
SqlCommand cmd = new SqlCommand("DeleteStockHistory", conn.Connection);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@UserId", userId));
conn.Command = cmd;
conn.Command.Prepare();
conn.Command.ExecuteNonQuery();
}
return RedirectToAction("index");
}
public ActionResult DeleteEvent(CalendarModel c)
{
int EventID = Int32.Parse(c.currentEventID);
string UserID = User.Identity.GetUserId();
if (UserID != null) {
MySqlConnection db = new MySqlConnection();
db.CreateConn();
SqlCommand cmd = new SqlCommand("DeleteCalendarEvent", db.Connection);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@UserId", UserID));
cmd.Parameters.Add(new SqlParameter("@EventId", EventID));
db.Command = cmd;
db.Command.Prepare();
db.Command.ExecuteNonQuery();
}
return RedirectToAction("Index");
}
public ActionResult BuyStock(string stock, string buy)
{
string userId = User.Identity.GetUserId();
Stock StockNote = new Stock();
var transactionType = 0;
decimal transPrice = 0;
int numTrans = 0;
if (stock != null | stock != "")
{
transPrice = QueryStockPrice(stock);
}
if (buy != "")
{
numTrans = Convert.ToInt16(buy);
}
if (userId != null & numTrans > 0)
{
MySqlConnection conn = new MySqlConnection();
conn.CreateConn();
SqlCommand cmd = new SqlCommand("CreateStockTransaction", conn.Connection);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@UserId", userId));
cmd.Parameters.Add(new SqlParameter("@StockName", stock.ToUpper()));
cmd.Parameters.Add(new SqlParameter("@Quantity", numTrans));
cmd.Parameters.Add(new SqlParameter("@TransactionPrice", transPrice));
cmd.Parameters.Add(new SqlParameter("@HasSold", transactionType));
conn.Command = cmd;
conn.Command.Prepare();
conn.Command.ExecuteNonQuery();
}
return RedirectToAction("index");
}
public void UploadTransaction(string stock, int numShares, decimal price, int buySell)
{
string userId = User.Identity.GetUserId();
var transactionType = 0;
if (buySell == 1)
{
transactionType = 1;
}
if (userId != null)
{
MySqlConnection conn = new MySqlConnection();
conn.CreateConn();
SqlCommand cmd = new SqlCommand("CreateStockTransaction", conn.Connection);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@UserId", userId));
cmd.Parameters.Add(new SqlParameter("@StockName", stock));
cmd.Parameters.Add(new SqlParameter("@Quantity", numShares));
cmd.Parameters.Add(new SqlParameter("@TransactionPrice", price));
cmd.Parameters.Add(new SqlParameter("@HasSold", transactionType));
conn.Command = cmd;
conn.Command.Prepare();
conn.Command.ExecuteNonQuery();
}
//return RedirectToAction("index");
}
private ActionResult CheckLoggedInUser(string UserID, ChatModel chatModel)
{
if (chatModel.Users.FirstOrDefault(u => u.ChatUserID == User.Identity.GetUserId()) == null)
{
#region create new user and add to lobby
ChatModel.ChatUser currentUser = new ChatModel.ChatUser()
{
ChatUserID = UserID,
Name = User.Identity.GetUserName(),
LoggedOnTime = DateTime.Now,
LastPing = DateTime.Now
};
chatModel.Users.Add(currentUser);
//stored procedure select
MySqlConnection selectConn = new MySqlConnection();
selectConn.CreateConn();
SqlCommand command = new SqlCommand("GetChatMessages", selectConn.Connection);
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@UserID", UserID));
selectConn.DataReader = command.ExecuteReader();
while (selectConn.DataReader.Read())
{
currentUser.ChatHistory.Add(new ChatModel.ChatMessage
{
Message = selectConn.DataReader["MessageContent"].ToString(),
Username = selectConn.DataReader["UserName"].ToString()
});
}
#endregion
}
ChatModel.ChatUser curUser = chatModel.Users.FirstOrDefault(u => u.ChatUserID == User.Identity.GetUserId());
ChatViewModel chatViewModel = new ChatViewModel();
chatViewModel.Users = chatModel.Users;
chatViewModel.ChatHistory = curUser.ChatHistory;
return PartialView("Lobby", chatViewModel);
}
private void AddMessage(string UserID, string chatMessage, ChatModel chatModel)
{
DateTime currentTime = DateTime.Now;
MySqlConnection conn = new MySqlConnection();
conn.CreateConn();
SqlCommand cmd = new SqlCommand("AddChatMessage", conn.Connection);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@UserId", UserID));
cmd.Parameters.Add(new SqlParameter("@MessageContent", chatMessage));
cmd.Parameters.Add(new SqlParameter("@TimeReceived", currentTime));
conn.Command = cmd;
conn.Command.Prepare();
conn.Command.ExecuteNonQuery();
foreach (ChatModel.ChatUser usr in chatModel.Users)
{
SqlCommand cmd2 = new SqlCommand("AddReceivedMessage", conn.Connection);
cmd2.CommandType = System.Data.CommandType.StoredProcedure;
cmd2.Parameters.Add(new SqlParameter("@ReceivedUserID", usr.ChatUserID));
cmd2.Parameters.Add(new SqlParameter("@MessageContent", chatMessage));
cmd2.Parameters.Add(new SqlParameter("@CurrentTime", currentTime));
conn.Command = cmd2;
conn.Command.Prepare();
conn.Command.ExecuteNonQuery();
usr.ChatHistory.Add(new ChatModel.ChatMessage { Message = chatMessage, Username = User.Identity.Name });
}
conn.CloseConn();
}
private List<EventModel> QueryEvents()
{
string userId = User.Identity.GetUserId();
List<EventModel> events = new List<EventModel>();
if (userId != null)
{
MySqlConnection conn = new MySqlConnection();
conn.CreateConn();
SqlCommand cmd = new SqlCommand("GetCalendarEvents", conn.Connection);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@UserId", userId));
conn.DataReader = cmd.ExecuteReader();
while (conn.DataReader.Read())
{
string name = conn.DataReader["EventName"].ToString();
string start = conn.DataReader["EventStartTime"].ToString();
string end = conn.DataReader["EventEndTime"].ToString();
string desc = conn.DataReader["EventDescription"].ToString();
string eid = conn.DataReader["EventID"].ToString();
EventModel anEvent = new EventModel { UserId = userId, EventId = eid, Name = name, DateFrom = start, DateTo = end, Description = desc };
events.Add(anEvent);
}
}
return events;
}
private void InsertEvent(string EventName, string EventDescription, DateTime dtStart, DateTime dtEnd)
{
string UserID = User.Identity.GetUserId();
if (UserID != null)
{
MySqlConnection db = new MySqlConnection();
db.CreateConn();
SqlCommand cmd = new SqlCommand("AddCalendarEvent", db.Connection);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@UserId", UserID));
cmd.Parameters.Add(new SqlParameter("@EventName", EventName));
cmd.Parameters.Add(new SqlParameter("@EventDescription", EventDescription));
cmd.Parameters.Add(new SqlParameter("@EventStart", dtStart));
cmd.Parameters.Add(new SqlParameter("@EventEnd", dtEnd));
db.Command = cmd;
db.Command.Prepare();
db.Command.ExecuteNonQuery();
}
}
public List<string> GetUniqueStocks()
{
List<string> uniqueStocks = new List<string>();
MySqlConnection conn = new MySqlConnection();
conn.CreateConn();
string userId = User.Identity.GetUserId();
if (userId != null)
{
SqlCommand cmd = new SqlCommand("GetUniqueStocks", conn.Connection);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@UserId", userId));
conn.DataReader = cmd.ExecuteReader();
while (conn.DataReader.Read())
{
string sname = conn.DataReader["StockName"].ToString();
uniqueStocks.Add(sname);
}
}
return uniqueStocks;
}
public Dictionary<string, List<List<decimal>>> GetTransactionHistory(string stock, string userId)
{
Dictionary<string,List<List<decimal>>> dict = new Dictionary<string, List<List<decimal>>>();
List<List<decimal>> transactionList = new List<List<decimal>>();
MySqlConnection conn = new MySqlConnection();
conn.CreateConn();
SqlCommand cmd = new SqlCommand("GetStockTransaction", conn.Connection);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@UserId", userId));
cmd.Parameters.Add(new SqlParameter("@StockName", stock));
conn.DataReader = cmd.ExecuteReader();
while (conn.DataReader.Read())
{
List<decimal> stockValues = new List<decimal>();
string sname = conn.DataReader["StockName"].ToString();
int quantity = Convert.ToInt16(conn.DataReader["Quantity"]);
decimal price = Convert.ToDecimal(conn.DataReader["TransactionPrice"]);
int hasSold = Convert.ToInt16(conn.DataReader["HasSold"]);
if (hasSold == 1)
{
price = -price;
}
while (quantity > 0)
{
stockValues.Add(price);
quantity -= 1;
}
if(dict.ContainsKey(stock)){
dict[stock].Add(stockValues);
}
else{
transactionList.Add(stockValues);
dict.Add(stock, transactionList);
}
}
return dict;
}
public ActionResult GetNote(string stock)
{
string userId = User.Identity.GetUserId();
string noteString = "";
Stock StockNote = new Stock();
if (userId != null)
{
MySqlConnection conn = new MySqlConnection();
conn.CreateConn();
SqlCommand cmd = new SqlCommand("GetStockNote", conn.Connection);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@UserId", userId));
cmd.Parameters.Add(new SqlParameter("@StockName", stock));
conn.DataReader = cmd.ExecuteReader();
while (conn.DataReader.Read())
{
noteString = conn.DataReader["StockNote"].ToString();
}
StockNote.Note = noteString.Trim();
StockNote.Name = stock;
}
return PartialView(StockNote);
}
