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