Exemplo n.º 1
0
        public HttpResponseMessage ChangePassword()
        {
            string success = "no_user";
            // Check old password

            var u = Request.Content.ReadAsAsync<ChangeCredentials>().Result;

            // Need to check that we're logged on
            var userId = CredentialController.Authenticate();
            if (!string.IsNullOrEmpty(userId))
            {
                string sql = @"select password from cycli_riders where UserId=@u and AccountStatus='Active'";
                // Check against the database
                SQLiteDatabase db = new SQLiteDatabase();
                string oldHashedPassword = db.ExecuteScalar(sql, "@u", userId);
                if (!string.IsNullOrEmpty(oldHashedPassword) && PasswordHash.ValidatePassword(u.oldPassword, oldHashedPassword))
                {
                    string newHashPassword = PasswordHash.CreateHash(u.newPassword);
                    // Check against the database
                    sql = @"update cycli_riders set password=@new where userid=@u and AccountStatus='Active'";
                    if (db.ExecuteNonQuery(sql, "@new", newHashPassword, "@u", userId, "@old", newHashPassword) > 0)
                    {
                        success = "ok";
                        //                    Emailer.SendRecoveryConfirmation(u.username, userId, code, u.email);
                    }
                    else
                    {
                        success = "db_failed";
                    }
                }
                else
                {
                    success = "wrong_password";
                }
                db.Close();
            }
            var response = Request.CreateResponse<string>(HttpStatusCode.OK, success,
                         new System.Net.Http.Formatting.JsonMediaTypeFormatter());
            return response;
        }
Exemplo n.º 2
0
        public static TrainingLoad[] GetTrainingLoad(string userId)
        {
            List<TrainingLoad> tls = new List<TrainingLoad>();
            // Because we look over the past year, we need data for 3 * chronic training load time constant
            DateTime now = DateTime.UtcNow.Date.AddDays(1);
            DateTime yearAgo = now.AddYears(-1);
            long secsYearAgo = Utilities.DbTime.ToDbSecs(yearAgo);
            long secsNow = Utilities.DbTime.ToDbSecs(now);
            DateTime startCalcTime = yearAgo.AddDays(-3 * CTL_TIME_CONSTANT);
            long startms = Utilities.DbTime.ToDbSecs(startCalcTime);
            DataTable dtTSS = null;
            string sql = @"select (r.startdatetime - r.startdatetime % 86400) as startdatetime, " +
                         "sum(rr.TSS) as TSS " +
                        "from cycli_race_riders rr, cycli_races r " +
                        "where rr.raceid = r.raceid " +
                        "and rr.userid = @u1 " +
                        "and r.startdatetime >= @st " +
                        "group by r.startdatetime - r.startdatetime % 86400 " +
                        "order by startdatetime";

            SQLiteDatabase db = new SQLiteDatabase();
            try
            {
                dtTSS = db.GetDataTable(sql, "@u1", userId, "@st", startms);
                float lastAtl = 0f;
                float lastCtl = 0f;

                if (dtTSS.Rows.Count > 0)
                {

                    DateTime d = startCalcTime;
                    while (d < now)
                    {

                        long s = Utilities.DbTime.ToDbSecs(d);
                        long e = Utilities.DbTime.ToDbSecs(d.AddDays(1));

                        DataRow[] dr = dtTSS.Select("startdatetime >= " + s + " and startdatetime < " + e);

                        TrainingLoad tl = new TrainingLoad() { DeltaDate = s - secsNow };
                        int t = 0;
                        if (dr.Length > 0)
                        {
                            tl.Tss = (int)dr[0].Field<long>("TSS");
                            t = tl.Tss;
                        }
                        else
                        {
                            tl.Tss = int.MinValue;   // Will hide it on the chart
                            t = 0;
                        }
                        tl.Atl = lastAtl + (float)(t - lastAtl) / ATL_TIME_CONSTANT;
                        tl.Ctl = lastCtl + (float)(t - lastCtl) / CTL_TIME_CONSTANT;
                        tl.Tsb = tl.Ctl - tl.Atl;

                        // Only add to the list if we're in the last year
                        if (d >= yearAgo)
                        {
                            tls.Add(tl);
                        }
                        lastAtl = tl.Atl;
                        lastCtl = tl.Ctl;
                        d = d.AddDays(1);
                    }
                }
            }
            finally
            {
                db.Close();
            }
            return tls.ToArray();
        }
Exemplo n.º 3
0
        public static Dictionary<string, Effort[]> GetSustainedPower(string userId)
        {
            Dictionary<string, Effort[]> d = new Dictionary<string, Effort[]>();
            DateTime now = DateTime.Now;
            long weekAgo = Utilities.DbTime.ToDbSecs(now.AddDays(-7));
            long monthAgo = Utilities.DbTime.ToDbSecs(now.AddMonths(-1));
            long yearAgo = Utilities.DbTime.ToDbSecs(now.AddYears(-1));

            SQLiteDatabase db = new SQLiteDatabase();
            try
            {
                string sql = @"select s.raceid as id, r.startdatetime as startdatetime, s.time as time, s.power as power " +
                              "from cycli_race_spots s, cycli_race_riders rr, cycli_races r, cycli_riders u " +
                              "where s.userid = rr.userid " +
                              "and s.raceid = rr.raceid " +
                              "and rr.raceid = r.raceid " +
                              "and rr.userid = u.userid " +
                              "and u.userid = @u1 " +
                              "and r.startdatetime >= @st " +
                              "union " +
                              "select r.raceid as id, r.startdatetime as startdatetime, -1 as time,-1 as power " +
                              "from cycli_race_riders rr, cycli_races r, cycli_riders u " +
                              "where u.userid = @u2 " +
                              "and rr.userid = u.userid " +
                              "and rr.raceid = r.raceid " +
                              "order by id,time";

                DataTable dt = db.GetDataTable(sql, "@u1", userId, "@u2", userId, "@st", yearAgo);
                // filter out any power spikes -> this calc is very sensitve to outliers
                DataTable dtFiltered = dt.Clone();
                if (dt.Rows.Count > 0)
                {
                    float ma = (float)dt.Rows[0].Field<long>("power");
                    dtFiltered.ImportRow(dt.Rows[0]);
                    for (int i = 1; i < dt.Rows.Count - 1; i++)
                    {
                        float v = (float)dt.Rows[i].Field<long>("power");
                        ma = 0.75f * ma + 0.25f * v;
                        if (ma > 0)
                        {
                            if (v / ma < 2)
                            {
                                dtFiltered.ImportRow(dt.Rows[i]);
                            }
                        }
                        else
                        {
                            dtFiltered.ImportRow(dt.Rows[i]);
                        }

                    }
                }
                Effort[] sustainedWeek = GetBestPowerForTime(dtFiltered.Select("startdatetime >= " + weekAgo));
                Effort[] sustainedMonth = GetBestPowerForTime(dtFiltered.Select("startdatetime >= " + monthAgo));
                Effort[] sustainedYear = GetBestPowerForTime(dtFiltered.Select());

                d.Add("SustainedWeek", sustainedWeek);
                d.Add("SustainedMonth", sustainedMonth);
                d.Add("SustainedYear", sustainedYear);
            }
            finally
            {
                db.Close();
            }
            return d;
        }
Exemplo n.º 4
0
        public static Analysis GetAnalysis(string userId, string raceId, long startms, long endms)
        {
            Analysis analysis = new Analysis();

            SQLiteDatabase db = new SQLiteDatabase();

            try
            {

                string sql = @"select max(s.time) max_t, max(s.distance) max_d, (CASE WHEN rr.Status = 'Finished' THEN 1 ELSE 0 END) Dnf " +
                  "from cycli_race_spots s, cycli_race_riders rr, cycli_races r " +
                      "where rr.UserId=@u " +
                      "and s.RaceId = rr.RaceId " +
                      "and r.RaceId = rr.RaceId " +
                      "and r.StartDateTime >= @st and r.StartDateTime <= @end " +
                      "and not rr.Status = 'Invited' " +
                      "group by s.RaceId, rr.Status";
                DataTable dtSummary = db.GetDataTable(sql, "@u", userId, "@st", startms, "@end", endms);

                Summary s = new Summary();

                s.TotalRaces = dtSummary.Rows.Count;
                if (s.TotalRaces > 0)
                {
                    if (endms > startms)
                    {
                        // seconds per week
                        s.RacesPerWeek = (float)(604800 * s.TotalRaces) / (endms - startms);
                    }
                    s.TotalDistance = (double)dtSummary.Compute("SUM(max_d)", null) / 1000;
                    s.TotalTime = (long)dtSummary.Compute("SUM(max_t)", null) / 1000;
                    s.DnfRaces = s.TotalRaces - (int)(long)dtSummary.Compute("SUM(dnf)", null);
                    s.MeanDistance = s.TotalDistance / s.TotalRaces;
                    s.MeanTime = s.TotalTime / s.TotalRaces;
                    analysis.Summary = s;
                }

            }
            catch (Exception ex1)
            {
            }
            try
            {

                DataTable dtRace = null;
                if (string.IsNullOrEmpty(raceId))
                {
                    string sql = @"select s.time, s.distance, s.speed, s.cadence, s.hr, s.power from cycli_race_spots s, cycli_races r " +
                        "where s.UserId=@u " +
                        "and s.RaceId = r.RaceId " +
                        "and r.StartDateTime >= @st and r.StartDateTime <= @end " +
                        "and s.speed > 0 and s.cadence > 0 and s.hr > 0 and s.power > 0 " +
                        "order by s.time";
                    dtRace = db.GetDataTable(sql, "@u", userId, "@st", startms, "@end", endms);
                }
                else
                {
                    string sql = @"select time, distance, speed, cadence, hr, power from cycli_race_spots " +
                        "where RaceId=@r and UserId=@u " +
                        "and speed > 0 and cadence > 0 and hr > 0 and power > 0 " +
                        "order by time";

                    dtRace = db.GetDataTable(sql, "@r", raceId, "@u", userId);
                }

                string[] vars = new string[] { "speed", "cadence", "hr", "power" };
                double[] steps = new double[] { POWER_STEP };

                Dictionary<string, double[]> rawData = new Dictionary<string, double[]>();

                for (int i = 0; i < vars.Length; i++)
                {
                    double[] vardata = dtRace.AsEnumerable().Select(dr => (double)dr.Field<dynamic>(vars[i])).ToArray();
                    // QuantileInPlace mangles this array - so make a copy
                    if (vardata.Length > 0)
                    {
                        double[] vd = new double[vardata.Length];
                        vardata.CopyTo(vd, 0);
                        rawData.Add(vars[i], vd);

                        Variable v = new Variable();
                        v.Type = vars[i];
                        v.Step = steps[i];
                        v.Mean = vardata.Average();
                        v.Minimum = vardata.Min(p => p);
                        v.Maximum = vardata.Max(p => p);

                        v.Percentile00_5 = ArrayStatistics.QuantileInplace(vardata, 0.005);
                        v.Percentile80 = ArrayStatistics.QuantileInplace(vardata, 0.8);
                        v.Percentile90 = ArrayStatistics.QuantileInplace(vardata, 0.9);
                        v.Percentile95 = ArrayStatistics.QuantileInplace(vardata, 0.95);
                        v.Percentile99 = ArrayStatistics.QuantileInplace(vardata, 0.99);
                        v.Percentile99_5 = ArrayStatistics.QuantileInplace(vardata, 0.995);
                        var vargroup = vardata.GroupBy(p => (int)Math.Floor(p / steps[i]));
                        int maxCount = vargroup.Max(p => p.Count());
                        v.Modal = vargroup.First(p => p.Count() == maxCount).Key * steps[i];

                        analysis.Variables.Add(vars[i], v);
                    }
                }

                for (int i = 0; i < vars.Length; i++)
                {
                    if (rawData.ContainsKey(vars[i]))
                    {
                        for (int j = 0; j < vars.Length; j++)
                        {
                            if (rawData.ContainsKey(vars[i]))
                            {
                                {
                                    // Corerlation
                                    Correlation corr = new Correlation();
                                    corr.Build(rawData[vars[i]], analysis.Variables[vars[i]].Step, rawData[vars[j]], analysis.Variables[vars[j]].Step);
                                    analysis.Correlations.Add(correlationKey(vars[i], vars[j]), corr);
                                }
                            }
                        }
                    }
                }
            }
            finally
            {
                db.Close();
            }
            return analysis;
        }
Exemplo n.º 5
0
        public static MonthlySummary[] GetMonthlySummary(string userId)
        {
            List<MonthlySummary> mss = new List<MonthlySummary>();
            try
            {
                SQLiteDatabase db = new SQLiteDatabase();
                long startms = DbTime.ToDbSecs(DateTime.UtcNow.AddYears(-1));
                string sql = @"select r.StartDateTime, rr.time, rr.distance, rr.energy, rr.pedalstrokes, rr.heartbeats, (CASE WHEN rr.Status = 'Abandoned' THEN 1 ELSE 0 END) Dnf " +
                  "from cycli_race_riders rr, cycli_races r " +
                      "where rr.UserId=@u " +
                      "and r.RaceId = rr.RaceId " +
                      "and r.StartDateTime >= @st " +
                      "and not rr.Status = 'Invited'";

                DataTable dtSummary = db.GetDataTable(sql, "@u", userId, "@st", startms);

                // Group the results by month
                var drMonths = dtSummary.AsEnumerable().GroupBy(p =>
                {
                    DateTime t = DbTime.FromDbSecs(p.Field<long>("StartDateTime"));
                    return t.ToString("MMM-yy");
                });
                foreach (var drMonth in drMonths)
                {
                    // Remove zero values - they'll just set the averages artifically low

                    MonthlySummary ms = new MonthlySummary()
                    {
                        Month = drMonth.Key,
                        TotalRaces = drMonth.Count(),
                        DnfRaces = (int)drMonth.Sum(p => p.Field<long>("Dnf")),
                        TotalDistance = drMonth.Sum(p => p.Field<double>("distance")),
                        TotalTime = drMonth.Sum(p => p.Field<long>("time")),
                        TotalWork = (int)(drMonth.Sum(p => p.Field<long>("energy"))),
                        MeanDistance = drMonth.Where(p => p.Field<long>("energy") > 0).Sum(p => p.Field<double>("distance")),
                        MeanTime = (long)drMonth.Where(p => p.Field<long>("time") > 0).Sum(p => p.Field<long>("time")),
                        MeanWork = (int)(drMonth.Where(p => p.Field<long>("energy") > 0).Sum(p => p.Field<long>("energy")))
                    };
                    if (ms.TotalRaces > 0)
                    {
                        ms.MeanDistance /= ms.TotalRaces;
                        ms.MeanTime /= ms.TotalRaces;
                        ms.MeanWork /= ms.TotalRaces;
                    }

                    // Prevent div by zero where there is no data
                    long speedTime = Math.Max(1, drMonth.Where(p => p.Field<double>("distance") > 0).Sum(p => p.Field<long>("time")));
                    long cadenceTime = Math.Max(1, drMonth.Where(p => p.Field<long>("pedalstrokes") > 0).Sum(p => p.Field<long>("time")));
                    long heartrateTime = Math.Max(1, drMonth.Where(p => p.Field<long>("heartbeats") > 0).Sum(p => p.Field<long>("time")));
                    long powerTime = Math.Max(1, drMonth.Where(p => p.Field<long>("energy") > 0).Sum(p => p.Field<long>("time")));

                    ms.MeanSpeed = MPERMILLISEC_TO_KMH * drMonth.Where(p => p.Field<double>("distance") > 0)
                            .Sum(p => p.Field<double>("distance")) / speedTime;

                    ms.MeanPower = (int)(1000 * drMonth.Where(p => p.Field<long>("energy") > 0)
                                .Sum(p => p.Field<long>("energy")) / powerTime);
                    ms.MeanCadence = (int)(60000 * drMonth.Where(p => p.Field<long>("pedalstrokes") > 0)
                            .Sum(p => p.Field<long>("pedalstrokes")) / cadenceTime);
                    ms.MeanHeartrate = (int)(60000 * drMonth.Where(p => p.Field<long>("heartbeats") > 0)
                            .Sum(p => p.Field<long>("heartbeats")) / heartrateTime);

                    mss.Add(ms);
                }
            }
            catch (Exception ex1)
            {
            }
            return mss.ToArray();
        }
Exemplo n.º 6
0
        public static Dictionary<string, object> GetEfforts(string userId, long startms, long endms)
        {
            Dictionary<string, object> d = new Dictionary<string, object>();

            SQLiteDatabase db = new SQLiteDatabase();
            try
            {
                DataTable dtRace = null;
                string sql = @"select r.raceid as id, r.name as name, max(s.distance)/1000 as distance, max(s.time)/60000 as time, " +
                             "rr.energy as energy " +
                            "from cycli_race_spots s, cycli_race_riders rr, cycli_races r " +
                            "where s.userid = rr.userid " +
                            "and s.raceid = rr.raceid " +
                            "and rr.raceid = r.raceid " +
                            "and rr.userid = @u1 " +
                            "and r.startdatetime >= @st and r.startdatetime <= @end " +
                            "group by r.raceid, r.name, rr.energy " +
                            "order by r.startdatetime";

                dtRace = db.GetDataTable(sql, "@u1", userId, "@st", startms, "@end", endms);

                string[] vars = new string[] { "distance", "time", "energy" };

                for (int i = 0; i < vars.Length; i++)
                {
                    dynamic[][] best = dtRace.AsEnumerable().Select(p => new dynamic[] { p["id"], p["name"], p[vars[i]] }).ToArray();
                    d.Add(vars[i], best);
                }
            }
            finally
            {
                db.Close();
            }
            return d;
        }
Exemplo n.º 7
0
 private string ValidateRecoveryCredentials(RegisterCredentials u)
 {
     string sql = @"select UserId from cycli_riders where UserName=@username and Email=@email and (AccountStatus='Active' or AccountStatus='Reset')";
     // Check against the database
     SQLiteDatabase db = new SQLiteDatabase();
     string userId = db.ExecuteScalar(sql, "@username", u.username, "@email", u.email);
     if (!string.IsNullOrEmpty(userId))
     {
         string hash = PasswordHash.CreateHash(u.password);
         string code = Guid.NewGuid().ToString();
         sql = @"update cycli_riders set activationcode=@a, AccountStatus='Reset', password=@p where userid=@u and AccountStatus='Active'";
         if (db.ExecuteNonQuery(sql, "@a", code,"@p", hash, "@u", userId) > 0)
         {
             Emailer.SendRecoveryConfirmation(u.username, userId, code, u.email);
         }
     }
     db.Close();
     return userId;
 }
Exemplo n.º 8
0
        private string ValidateCredentials(UserCredentials u)
        {
            string userId = null;
            string sql = @"select UserId, Password from cycli_riders where UserName=@username and AccountStatus='Active'";
            // Check against the database
            SQLiteDatabase db = new SQLiteDatabase();
            DataTable dt = db.GetDataTable(sql, "@username", u.username);
            db.Close();
            if (dt.Rows.Count > 0)
            {
                string hash = dt.Rows[0].Field<string>("Password");

                // Test the hash
                if (!string.IsNullOrEmpty(hash) && PasswordHash.ValidatePassword(u.password, hash))
                {
                    userId = dt.Rows[0].Field<string>("UserId");
                    CreateAuthenticationCookie(userId);
                }
            }

            return userId;
        }
Exemplo n.º 9
0
 private bool NameIsValid(string username)
 {
     bool valid = (username.Length >= 5 && username.Length <= 20);
     if (valid)
     {
         // Check against the database
         SQLiteDatabase db = new SQLiteDatabase();
         string result = db.ExecuteScalar("select 1 from cycli_riders where username=@u", "@u", username);
         valid = string.IsNullOrEmpty(result);
         db.Close();
     }
     return valid;
 }