/// <summary>
/// Get tag ID data from the SQL database table.
/// </summary>
public void GetData(ListView listview)
{
using (sqlConn = new SqlConnection(connectionString))
{
sqlConn.Open();
using (SqlDataReader reader = new SqlCommand("SELECT * FROM TagInfo", sqlConn).ExecuteReader())
{
try
{
while (reader.Read())
{
string[] rowitem = new string[2];
rowitem[0] = reader.GetString(reader.GetOrdinal("TagId"));
rowitem[1] = reader.GetDateTime(reader.GetOrdinal("TagTime")).ToString();
ListViewItem listitem = new ListViewItem(rowitem);
//table.Rows.Add(epc, time);
listview.Items.Add(listitem);
}
reader.Close();
}
catch (System.Data.SqlClient.SqlException ee)
{
MessageBox.Show(ee.Message.ToString());
}
}
sqlConn.Close();
}
}
public void readFromDataBase()
{
items.Clear();
SqlDataReader reader = new SqlCommand(string.Format("SELECT * FROM Items ORDER BY Id;SELECT * FROM Jobs ORDER BY Item;SELECT * FROM Jobs ORDER BY Item;"), cn).ExecuteReader();
//читаем лист items
while (reader.Read())
{
item newItem = new item();
newItem.id = reader.GetInt32(0);
newItem.firstName = reader.GetString(1);
newItem.lastName = reader.GetString(2);
items.Add(newItem);
}
int i;
int lastid;
//читаем лист jobs
reader.NextResult();
lastid = i = -1;
while (reader.Read())
{
int k = reader.GetInt32(4);
if (lastid != k)
{
do ++i;
while (k != items[i].id);
lastid = reader.GetInt32(4);
}
items[i].jobs.Add(new job(reader.GetDateTime(0), reader.GetString(1), reader.GetString(2), reader.GetString(3)));
}
//читаем лист positions
reader.NextResult();
lastid = i = -1;
while (reader.Read())
{
if (lastid != reader.GetInt32(4))
{
do i++;
while (reader.GetInt32(4) != items[i].id);
lastid = reader.GetInt32(4);
}
items[i].positions.Add(new position(reader.GetInt64(0),reader.GetInt64(1),reader.GetInt32(2),reader.GetDateTime(3)));
}
reader.Close();
}
private void btnNewMatches_Click(object sender, EventArgs e)
{
SqlDataReader reader;
string q = @"SELECT sr.Date FROM dbo.Ivnet i, dbo.SportRadar sr
WHERE i.Guessed IS NULL AND i.SportRadarID=sr.ID AND sr.Date>=CONVERT(date,GETDATE()) GROUP BY sr.Date ORDER BY sr.Date";
List<string> dates = new List<string>();
using (SqlConnection connection = new SqlConnection(MainForm.STRCONN))
{
connection.Open();
using (reader = new SqlCommand(q, connection).ExecuteReader())
{
while (reader.Read())
{
dates.Add(reader.GetDateTime(0).ToString("yyyy-MM-dd"));
}
}
int tipWinner, matchIndex = 0;
double odds, currentInvestment, smallBet, mediumBet, bigBet;
SqlCommand selectCmd;
string html = "", rowFormat = @"<tr><td class='checkbox'><input type='checkbox' /></td><td class='No'>{0}</td>
<td class='Country'>{1}</td><td class='League'>{2}</td>
<td class='HomeTeam'>{3}</td><td class='AwayTeam'>{4}</td><td class='Tip'>{5}</td><td class='Odds'>{6}</td></tr>";
parseBets(out smallBet, out mediumBet, out bigBet);
foreach (string date in dates)
{
q = @"SELECT i.ID ID, sr.Country Country, sr.League League, sr.Season Season, sr.Round Round, sr.Date Date,
sr.HomeTeam HomeTeam, sr.AwayTeam AwayTeam, i.TipGoalsH TipGoalsH, i.TipGoalsA TipGoalsA,
sr.HomeOdds HomeOdds, sr.DrawOdds DrawOdds, sr.AwayOdds AwayOdds
FROM dbo.Ivnet i, SoccerBase.dbo.SportRadar sr
WHERE i.SportRadarID = sr.ID AND i.Guessed IS NULL AND sr.Date = @Date";
selectCmd = new SqlCommand(q, connection);
selectCmd.Parameters.AddWithValue("@Date", date);
using (reader = selectCmd.ExecuteReader())
{
html += String.Format("<p2>{0:dddd, MMMM d, yyyy}:</p2>", DateTime.Parse(date));
html += @"<table border='1' style='font-family:Verdana;font-size:12px;'><thead><tr>
<th></th><th>No:</th><th>Држава</th><th>Првенство</th>
<th>Домашен Тим</th><th>Гостински Тим</th><th>Тип</th><th>BWin Коефициенти</th></tr></thead><tbody>";
currentInvestment = 0.0;
while (reader.Read())
{
tipWinner = (byte)reader["TipGoalsH"] - (byte)reader["TipGoalsA"];
tipWinner = tipWinner >= 0 ? (tipWinner > 0 ? 0 : 1) : 2;
if (tipWinner == 0)
{
odds = (double)reader["HomeOdds"];
}
else if (tipWinner == 1)
{
odds = (double)reader["DrawOdds"];
}
else
{
odds = (double)reader["AwayOdds"];
}
currentInvestment = 0.0;
if (odds < 1.25) continue;
if (odds >= 1.25 && odds <= 1.5 && cbBigBet.Checked) currentInvestment = bigBet;
else if (odds > 1.5 && odds <= 2.2 && cbMediumBet.Checked) currentInvestment = mediumBet;
else if (odds > 2.2 && cbSmallBet.Checked) currentInvestment = smallBet;
else continue;
html += String.Format(rowFormat, ++matchIndex,
reader["Country"], reader["League"], reader["HomeTeam"], reader["AwayTeam"],
reader["TipGoalsH"] + ":" + reader["TipGoalsA"],
reader["HomeOdds"].ToString() + " " + reader["DrawOdds"].ToString() + " " + reader["AwayOdds"].ToString()
);
}
html += "</tbody></table><br/>";
}
}
tempHTML = html;
browser.DocumentText = @"<html><head>
<script type='text/javascript'>
function func() {
var inputs = document.getElementsByTagName('input');
var tr;
for (var i = 0; i < inputs.length; i++) {
if (inputs[i].getAttribute('type')=='checkbox') {
inputs[i].onclick = function() {
tr = this.parentNode.parentNode;
if (this.checked) tr.style.backgroundColor = 'cyan';
else tr.style.backgroundColor = '';
}
}
}
}
</script>
</head>
<body style='font-family:Verdana;font-size:12px;' onload='func();'>" + html +
@"<p>Send to email:
<select id='emailsSelect'>
<option value='*****@*****.**'>[email protected]</option>
<option value='*****@*****.**'>[email protected]</option>
</select>
<button onclick='window.external.sendEmail(document.getElementById(""emailsSelect"").value);'>Send</button>
</p></body></html>";
}
}
public static void Main(string[] args)
{
//Console.WriteLine(response);
try
{
while (true)
{
using (var sql = new SqlConnection(Secrets.ConnectionString))
{
sql.Open();
// dbo.StravaKeys contains registered API keys; let's collect all of them
#region strava
var api_base = "https://www.strava.com/api/v3/";
var keysQuery = "SELECT * from dbo.StravaKeys;";
Log.Debug(keysQuery);
var results = new List<Tuple<int, string>>();
using (var keysResults = new SqlCommand(keysQuery, sql).ExecuteReader())
{
while (keysResults.Read())
{
results.Add(new Tuple<int, string>(keysResults.GetInt32(0), keysResults.GetString(1)));
}
}
Log.Info("Got {0} keys to check", results.Count);
// get updated results for each StravaKey and stick them in the DB.
foreach (var res in results)
{
var accessToken = $"&access_token={res.Item2}";
var epoch = 1451624400; // jan 1 2016
long secondsSinceEpoch;
// select the last time we updated this user; if we've never updated them, get events since January 1, 2016
var lastRunQuery =
$"SELECT activity_time FROM dbo.StravaActivities WHERE strava_id={res.Item1} ORDER BY activity_time DESC;";
Log.Debug(lastRunQuery);
using (var lastRun = new SqlCommand(lastRunQuery, sql).ExecuteReader())
{
if (lastRun.HasRows)
{
lastRun.Read();
secondsSinceEpoch =
(long)
Math.Max(epoch,
lastRun.GetDateTime(0).Subtract(new DateTime(1970, 1, 1)).TotalSeconds);
}
else
{
secondsSinceEpoch = epoch;
}
}
// transform our DB results into <DateTime, double, string> tuples
var apiUrl =
$"{api_base}athlete/activities/?per_page=200&after={secondsSinceEpoch}{accessToken}";
Log.Info("Hitting API at {0}", apiUrl);
var runs = GetArrayFromApi(apiUrl)
.Select(e =>
new Tuple<DateTime, double, string>(
DateTime.Parse((string)e["start_date"]),
(double)e["distance"] / 1609, // translate meters to miles
(string)e["type"]))
.ToList();
// if we actually got any tuples, transform them into an INSERT query
Log.Info("Got {0} results from Strava API", runs.Count);
if (runs.Any())
{
// aggregate the tuples into a StringBuilder
var insertionQuery = runs.Aggregate(
new StringBuilder(
"INSERT INTO dbo.StravaActivities (strava_id, activity_time, activity_distance, activity_type) VALUES "),
(acc, e) => acc.Append($"({res.Item1}, '{e.Item1}', {e.Item2}, '{e.Item3}'), "));
// trim the last ", " from the aggregated tuples and append a semicolon
insertionQuery.Remove(insertionQuery.Length - 2, 2).Append(";");
// insert!
Log.Debug(insertionQuery);
using (var inserter = new SqlCommand(insertionQuery.ToString(), sql))
inserter.ExecuteNonQuery();
}
}
#endregion
#region withings
Log.Info("Hitting Withings API");
var lastMeasure = new DateTime(2016, 1, 1);
var unixTimeStart = new DateTime(1970, 1, 1);
var lastEntryQuery = "SELECT measure_date FROM dbo.bodyFat ORDER BY measure_date DESC;";
using (var lastEntry = new SqlCommand(lastEntryQuery, sql).ExecuteReader())
{
if (lastEntry.Read())
{
lastMeasure = lastEntry.GetDateTime(0);
}
}
var withingsResponse = GetObjectFromApi(Secrets.WithingsApi);
var bodyFatMeasures = ((JArray)withingsResponse["body"]["measuregrps"])
.Where(e => (long)e["date"] > (long)lastMeasure.Subtract(unixTimeStart).TotalSeconds && ((JArray)e["measures"]).Any(f => (int)f["type"] == 8))
.Select(e => new Tuple<DateTime, double>(unixTimeStart.AddSeconds((int)e["date"]), (double)(((JArray)e["measures"]).First(token => (int)token["type"] == 8)["value"]) * Math.Pow(10, (double)((JArray)e["measures"]).First(token => (int)token["type"] == 8)["unit"])))
.ToList();
if (bodyFatMeasures.Any())
{
Log.Info("Got {0} new body fat measurements", bodyFatMeasures.Count);
var bodyFatQuery =
bodyFatMeasures.Aggregate(
new StringBuilder("INSERT INTO dbo.bodyFat (measure_date, measure) VALUES "),
(a, b) => a.Append($"('{b.Item1}', {b.Item2}), "));
bodyFatQuery.Remove(bodyFatQuery.Length - 2, 2).Append(";");
Log.Debug(bodyFatQuery);
using (var insertionQuery = new SqlCommand(bodyFatQuery.ToString(), sql))
{
insertionQuery.ExecuteNonQuery();
}
}
else
{
Log.Info("No new body fat measurements.");
}
#endregion
}
Log.Info("Sleeping...");
System.Threading.Thread.Sleep(15 * 1000 * 60);
}
}
catch (WebException ex)
{
Log.Error(ex);
}
catch (Exception ex)
{
Log.Fatal(ex);
throw;
}
}