public CashingService() { config = new LocalConfig(); placeCash = new PlaceCash(config); userCash = new UserCash(config); }
public List <UserCash> GetCashAmounts() { List <UserCash> rtnList = new List <UserCash>(); List <int> ids = new List <int>(); using (SqlConnection conn = new SqlConnection(_connectionString)) { conn.Open(); string getIds = "Select [User_Game].UserId From [User_Game] Where IsReady = 1"; SqlCommand cmd = new SqlCommand(getIds, conn); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { int id = Convert.ToInt32(reader["UserId"]); ids.Add(id); } } using (SqlConnection conn = new SqlConnection(_connectionString)) { string updateStats = ""; foreach (int userId in ids) { updateStats += "Update[User_Game] Set Total = CurrentCash + (Select Sum([User_Stocks].NumberOfShares *[Stock].CurrentPrice) from[User_Stocks] " + "Join [Stock] on Stock.StockId = [User_Stocks].StockId where[User_Stocks].UserId = " + userId + ") Where[User_Game].UserId = " + userId + ";"; } conn.Open(); SqlCommand cmd = new SqlCommand(updateStats, conn); int numberOfRowsAffected = cmd.ExecuteNonQuery(); if (numberOfRowsAffected > 0) { bool didWork = true; } } using (SqlConnection conn = new SqlConnection(_connectionString)) { conn.Open(); string sql = "Select * from [User_Game] Join [User] on [User].Id = [User_Game].UserId ORDER By Total DESC"; SqlCommand cmd = new SqlCommand(sql, conn); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { UserCash userCash = new UserCash(); UserItem item = new UserItem(); userCash.CurrentCash = Convert.ToDouble(reader["CurrentCash"]); userCash.TotalCash = Convert.ToDouble(reader["Total"]); userCash.IdOfUser = Convert.ToInt32(reader["UserId"]); item.FirstName = reader["FirstName"].ToString(); item.LastName = reader["LastName"].ToString(); item.Username = reader["Username"].ToString(); userCash.UserInfo = item; rtnList.Add(userCash); } } using (SqlConnection conn = new SqlConnection(_connectionString)) { conn.Open(); string sql = @"Select [User].Id, Count([User].Id) As 'Owned' From [User] Join [User_Stocks] on [User_Stocks].UserId= [User].Id Where NumberOfShares > (Select [Settings].[Value] / 2 From [Settings] Where [Settings].[Key] = 'AvailableStocks') Group By [User].Id"; SqlCommand cmd = new SqlCommand(sql, conn); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { int owned = Convert.ToInt32(reader["Owned"]); int user = Convert.ToInt32(reader["Id"]); foreach (UserCash person in rtnList) { if (person.IdOfUser == user) { person.OwnedStocks = owned; break; } } } } return(rtnList); }