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);
}
