public IEnumerable <MyOrder> GetMyOrder(int userId)
{
List <MyOrder> myOrders = new List <MyOrder>();
List <SqlParameter> prms = new List <SqlParameter> {
new SqlParameter("@USERID", userId)
};
DataTable marketTable = DBUtility.GetDataTable(opts.ConnectionString, "spSelectMyOrders", prms);
foreach (DataRow dr in marketTable.Rows)
{
MyOrder myOrder = new MyOrder();
myOrder.Id = Convert.ToInt32(dr["Id"]);
myOrder.OrderId = Guid.Parse(dr["OrderId"].ToString());
myOrder.UserId = Convert.ToInt32(dr["UserId"]);
myOrder.Name = Convert.ToString(dr["Name"]);
myOrder.EntityId = Convert.ToInt32(dr["EntityId"]);
myOrder.TradeTypeId = Convert.ToInt32(dr["TradeTypeId"]);
myOrder.Price = Convert.ToDecimal(dr["Price"]);
myOrder.Date = Convert.ToDateTime(dr["Date"]);
myOrder.Quantity = Convert.ToInt32(dr["Quantity"]);
myOrder.UnsetQuantity = Convert.ToInt32(dr["UnsetQuantity"]);
myOrder.Status = Convert.ToInt32(dr["Status"]);
myOrder.TxHash = Convert.ToString(dr["TxHash"]);
myOrders.Add(myOrder);
}
return(myOrders);
}
public MarketData UpdateMarketData(MarketData marketData)
{
List <SqlParameter> prms = new List <SqlParameter>
{
new SqlParameter("@ENTITYID", marketData.EntityId),
new SqlParameter("@VOLUME", marketData.Volume),
new SqlParameter("@LASTTRADETIME", marketData.LastTradeTime),
new SqlParameter("@LASTTRADEPRICE", marketData.LastTradePrice),
new SqlParameter("@MARKETPRICE", marketData.MarketPrice)
};
DataTable marketTable = DBUtility.GetDataTable(opts.ConnectionString, "spInsertMarketData", prms);
DataRow dr = marketTable.Rows[0];
return(new MarketData
{
EntityId = entityId,
LastTradePrice = dr.IsNull("LastTradePrice") ? (decimal?)null : Convert.ToDecimal(dr["LastTradePrice"]),
LastTradeTime = dr.IsNull("LastTradeTime") ? (DateTime?)null : Convert.ToDateTime(dr["LastTradeTime"]),
MarketPrice = dr.IsNull("MarketPrice") ? (decimal?)null : Convert.ToDecimal(dr["MarketPrice"]),
Volume = dr.IsNull("Volume") ? (int?)null : Convert.ToInt32(dr["Volume"]),
ChangeInPrice = dr.IsNull("ChangeInPrice") ? (decimal?)null : Convert.ToDecimal(dr["ChangeInPrice"]),
CurrentAsk = dr.IsNull("CurrentAsk") ? (decimal?)null : Convert.ToDecimal(dr["CurrentAsk"]),
CurrentBid = dr.IsNull("CurrentBid") ? (decimal?)null : Convert.ToDecimal(dr["CurrentBid"])
});
}
public IEnumerable <Order> GetTradeHistory(int userId, int entityId)
{
List <Order> orders = new List <Order>();
List <SqlParameter> prms = new List <SqlParameter> {
new SqlParameter("@ENTITYID", entityId),
new SqlParameter("@USERID", userId)
};
DataTable marketTable = DBUtility.GetDataTable(opts.ConnectionString, "spSelectTradeHistory", prms);
foreach (DataRow dr in marketTable.Rows)
{
Order order = new Order();
order.Id = Convert.ToInt32(dr["Id"]);
order.OrderId = Guid.Parse(dr["OrderId"].ToString());
order.UserId = Convert.ToInt32(dr["UserId"]);
order.TrendId = Convert.ToInt32(dr["TrendId"]);
order.TradeTypeId = Convert.ToInt32(dr["TradeTypeId"]);
order.Price = Convert.ToDecimal(dr["Price"]);
order.Date = Convert.ToDateTime(dr["Date"]);
order.Quantity = Convert.ToInt32(dr["Quantity"]);
order.UnsetQuantity = Convert.ToInt32(dr["UnsetQuantity"]);
order.Status = Convert.ToInt32(dr["Status"]);
orders.Add(order);
}
return(orders);
}
public decimal GetMyBalance(int userId)
{
decimal myBalance = 0;
List <SqlParameter> prms = new List <SqlParameter> {
new SqlParameter("@USERID", userId)
};
DataTable myBalanceTable = DBUtility.GetDataTable(opts.ConnectionString, "spSelectMyBalance", prms);
decimal.TryParse(myBalanceTable.Rows[0][0].ToString(), out myBalance);
return(myBalance);
}
public decimal GetMyUnits(int userId, int entityId)
{
decimal myUnits = 0;
List <SqlParameter> prms = new List <SqlParameter> {
new SqlParameter("@USERID", userId),
new SqlParameter("@ENTITYID", entityId)
};
DataTable myUnitsTable = DBUtility.GetDataTable(opts.ConnectionString, "spSelectMyUnits", prms);
decimal.TryParse(myUnitsTable.Rows[0][0].ToString(), out myUnits);
return(myUnits);
}
public PriceVol GetPriceVol(string name)
{
List <SqlParameter> prms = new List <SqlParameter> {
new SqlParameter("@NAME", name)
};
var dt = DBUtility.GetDataTable(opts.ConnectionString, "spSelectPriceVolData", prms);
var dr = dt.Rows[0];
PriceVol priceVols = new PriceVol();
priceVols.Name = name;
string priceHistory = dr["PriceDate"].ToString().Trim();
priceHistory = (priceHistory.EndsWith(',')) ? priceHistory.Remove(priceHistory.Length - 1) : priceHistory;
priceVols.Prices = Utility.ConvertToDecArray(dr["Price"].ToString(), true);
priceVols.Times = priceHistory.Split(',');
return(priceVols);
}
public TweetVol GetTweetVol(string name)
{
List <SqlParameter> prms = new List <SqlParameter> {
new SqlParameter("@NAME", name)
};
var dt = DBUtility.GetDataTable(opts.ConnectionString, "spSelectTrendData", prms);
TweetVol tweetVols = new TweetVol();
tweetVols.Name = name;
tweetVols.LoadDate = new List <string>();
tweetVols.TweetVolume = new List <int>();
tweetVols.AvgSentiment = new List <double?>();
foreach (DataRow dr in dt.Rows)
{
tweetVols.LoadDate.Add(Convert.ToDateTime(dr["LoadDate"]).ToString("M'/'dd HH:mm"));
tweetVols.TweetVolume.Add(Convert.ToInt32(dr["TweetVolume"]));
tweetVols.AvgSentiment.Add(Convert.ToDouble(dr["AvgSentiment"]));
}
return(tweetVols);
}
public MarketData GetMarketData(int userId, int entityId)
{
List <SqlParameter> prms = new List <SqlParameter> {
new SqlParameter("@ENTITYID", entityId)
};
DataTable marketTable = DBUtility.GetDataTable(opts.ConnectionString, "spSelectMarketData", prms);
DataRow dr = marketTable.Rows[0];
return(new MarketData
{
EntityId = entityId,
LastTradePrice = dr.IsNull("LastTradePrice") ? (decimal?)null : Convert.ToDecimal(dr["LastTradePrice"]),
LastTradeTime = dr.IsNull("LastTradeTime") ? (DateTime?)null : Convert.ToDateTime(dr["LastTradeTime"]),
MarketPrice = dr.IsNull("MarketPrice") ? (decimal?)null : Convert.ToDecimal(dr["MarketPrice"]),
Volume = dr.IsNull("Volume") ? (int?)null : Convert.ToInt32(dr["Volume"]),
ChangeInPrice = dr.IsNull("ChangeInPrice") ? (decimal?)null : Convert.ToDecimal(dr["ChangeInPrice"]),
CurrentAsk = dr.IsNull("CurrentAsk") ? (decimal?)null : Convert.ToDecimal(dr["CurrentAsk"]),
CurrentBid = dr.IsNull("CurrentBid") ? (decimal?)null : Convert.ToDecimal(dr["CurrentBid"])
});
}
public List <Trend> GetTrends(int filter)
{
//STUPID FILTER PARAMETER
List <SqlParameter> paras = new List <SqlParameter>();
List <Trend> trends = new List <Trend>();
DataTable dt = null;
if (filter == 0)
{
paras.Add(new SqlParameter("@USERENTRY", filter));
dt = DBUtility.GetDataTable(opts.ConnectionString, "spSelectTrends", paras);
}
else
{
paras.Add(new SqlParameter("@USERID", filter));
dt = DBUtility.GetDataTable(opts.ConnectionString, "spSelectUserBoughtTrends", paras);
}
foreach (DataRow dr in dt.Rows)
{
decimal price = Convert.ToDecimal(dr["Price"]);
bool hasGain = price > Convert.ToDecimal(dr["LastPrice"]) ? true : false;
trends.Add(new Trend
{
Id = Convert.ToInt32(dr["Id"]),
LoadDate = Convert.ToDateTime(dr["LoadDate"]),
Name = dr["Name"].ToString(),
TweetVolume = Convert.ToInt32(dr["TweetVolume"]),
URL = dr["URL"].ToString(),
Faved = false,
Notify = false,
Price = price,
ChangeIn = Convert.ToDecimal(dr["ChangeIn"]),
PriceText = price.ToString("C"),
Prices = Utility.ConvertToDecArray(dr["PriceHistory"].ToString(), false),
Gains = hasGain
});
}
return(trends);
}
public static void LoadUserTrends(string sqlConnectionString, string twitterConsumerKey, string twitterConsumerSecret, string twitterAccessToken, string twitterAccessTokenSecret, Action <string> LogError)
{
//sql statement here to get user trend names
var dt = DBUtility.GetDataTable(sqlConnectionString, "spSelectUserTrends", null);
foreach (DataRow dr in dt.Rows)
{
var trendName = dr["TRENDNAME"].ToString();
var tweets = GetTweetsAsync(trendName, 10, twitterConsumerKey, twitterConsumerSecret, twitterAccessToken, twitterAccessTokenSecret);
double?score = GetVaderSentAvgAsync(tweets.Result).Result;
int fakeVolume = Utility.RandomNumber(10000, 500000);
List <SqlParameter> sparams = new List <SqlParameter>();
string enCodedName = (trendName.StartsWith('#') ? "%23" + trendName.Substring(1) : trendName);
sparams.Add(new SqlParameter("@NAME", trendName));
sparams.Add(new SqlParameter("@URL", $"http://twitter.com/search?q={HttpUtility.HtmlEncode(enCodedName)}"));
sparams.Add(new SqlParameter("@TWEETVOLUME", fakeVolume));//random number
sparams.Add(new SqlParameter("@AVGSENTIMENT", score));
sparams.Add(new SqlParameter("@USERENTRY", true));
FinaroEngine.Library.DBUtility.ExecuteQuery(sqlConnectionString, "spInsertTrend", sparams);
}
}
public IEnumerable <TeamPlayer> GetTeamPlayers(int entityTypeId, int entityLeagueId, int groupId)
{
List <TeamPlayer> teamPlayers = new List <TeamPlayer>();
List <SqlParameter> prms = new List <SqlParameter>
{
new SqlParameter("@ENTITYTYPEID", entityTypeId),
new SqlParameter("@ENTITYLEAGUEID", entityLeagueId),
new SqlParameter("@ENTITYGROUPREFID", groupId)
};
DataTable teamPlayerTable = DBUtility.GetDataTable(opts.ConnectionString, "spSelectTeamLeagueData", prms);
foreach (DataRow dr in teamPlayerTable.Rows)
{
TeamPlayer teamPlayer = new TeamPlayer();
teamPlayer.Id = Convert.ToInt32(dr["Id"]);
teamPlayer.Name = Convert.ToString(dr["Name"]);
teamPlayer.CurrentBid = dr.IsNull("CurrentBid") ? (decimal?)null : Convert.ToDecimal(dr["CurrentBid"]);
teamPlayer.CurrentAsk = dr.IsNull("CurrentAsk") ? (decimal?)null : Convert.ToDecimal(dr["CurrentAsk"]);
teamPlayer.LastPrice = dr.IsNull("LastPrice") ? (decimal?)null : Convert.ToDecimal(dr["LastPrice"]);
teamPlayers.Add(teamPlayer);
}
return(teamPlayers);
}
public static void LoadTrends(string sqlConnectionString, string twitterConsumerKey, string twitterConsumerSecret, string twitterAccessToken, string twitterAccessTokenSecret, Action <string> LogError)
{
var request = new RestRequest("1.1/trends/place.json", Method.GET);
request.AddQueryParameter("id", "23424977");
request.AddQueryParameter("result_type", "popular");
var client = new RestClient("https://api.twitter.com")
{
Authenticator = OAuth1Authenticator.ForProtectedResource(
twitterConsumerKey,
twitterConsumerSecret,
twitterAccessToken,
twitterAccessTokenSecret)
};
IRestResponse response = client.Execute(request);
var content = response.Content; // raw content as string
dynamic stuff = JsonConvert.DeserializeObject(content);
string startTime = DBUtility.ExecuteScalar(sqlConnectionString, "SELECT FORMAT(GETDATE() , 'M/d/yy HH:mm')").ToString();
bool updatesMade = false;
foreach (JObject item in stuff)
{
foreach (var trend in item["trends"])
{
string trendName = "";
double?score = 0;
try
{
int volume = 0;
Int32.TryParse(trend["tweet_volume"].ToString(), out volume);
List <SqlParameter> sparams = new List <SqlParameter>();
trendName = trend["name"].ToString();
var tweets = GetTweetsAsync(trendName, 10, twitterConsumerKey, twitterConsumerSecret, twitterAccessToken, twitterAccessTokenSecret);
if (tweets == null || volume == 0)
{
continue;
}
score = GetVaderSentAvgAsync(tweets.Result).Result;
sparams.Add(new SqlParameter("@NAME", trendName));
sparams.Add(new SqlParameter("@URL", trend["url"].ToString()));
sparams.Add(new SqlParameter("@TWEETVOLUME", volume));
sparams.Add(new SqlParameter("@AVGSENTIMENT", score));
sparams.Add(new SqlParameter("@USERENTRY", false));
sparams.Add(new SqlParameter("@DATE", startTime));
DBUtility.ExecuteQuery(sqlConnectionString, "spInsertTrend", sparams);
updatesMade = true;
}
catch (Exception e)
{
LogError($"Trend:{trendName} Score:{score} {e.ToString()}");
}
}
}
// UPDATES WERE MADE LETS ZERO OUT ORPHANED TRENDS
if (updatesMade)
{
var dt = DBUtility.GetDataTable(sqlConnectionString, "spSelectOrphanedTrends", new List <SqlParameter> {
new SqlParameter("@LOADTIME", startTime)
});
foreach (DataRow dr in dt.Rows)
{
List <SqlParameter> sqlp = new List <SqlParameter>();
sqlp.Add(new SqlParameter("@NAME", dr["Name"].ToString()));
sqlp.Add(new SqlParameter("@URL", dr["URL"].ToString()));
sqlp.Add(new SqlParameter("@TWEETVOLUME", Convert.ToInt32(0)));
sqlp.Add(new SqlParameter("@AVGSENTIMENT", Convert.ToInt32(0)));
sqlp.Add(new SqlParameter("@USERENTRY", true));
sqlp.Add(new SqlParameter("@DATE", startTime));
DBUtility.ExecuteQuery(sqlConnectionString, "spInsertTrend", sqlp);
}
}
}
