/// <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();
     }
 }
Example #2
0
        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() + "&nbsp;&nbsp;" + reader["DrawOdds"].ToString() + "&nbsp;&nbsp;" + 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>";
     }
 }
Example #4
0
        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;
            }
        }