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 bool Delete(string action)
{
// Query checks that race is in the right mode, the invitee is not already invited and that the invite limit has not been exceeded
SQLiteDatabase db = new SQLiteDatabase(true);
bool deleted = false;
try
{
// This query checks that the user has not already been invited
string sql = "delete from cycli_content where lower(_action) = lower(@a) ";
deleted = (db.ExecuteNonQuery(sql, "@a", action) == 1);
string deletelinksql = "delete from cycli_content_links where sourceaction = @s";
db.ExecuteNonQuery(deletelinksql, "@s", action);
db.CommitTransaction();
}
catch (Exception ex)
{
db.RollbackTransaction();
}
finally
{
}
return deleted;
}
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;
}
public static SupportArticle Load(string action)
{
SupportArticle s = new SupportArticle();
// Query checks that race is in the right mode, the invitee is not already invited and that the invite limit has not been exceeded
SQLiteDatabase db = new SQLiteDatabase();
try
{
// This query checks that the user has not already been invited
string sql = "select a._action, a.title, a.body, a.footerLink, a.updated " +
"from cycli_content a " +
"where lower(a._action) = lower(@a) ";
DataTable dtAction = db.GetDataTable(sql, "@a", action);
if (dtAction.Rows.Count > 0)
{
s = new SupportArticle
{
Action = dtAction.Rows[0].Field<string>("_action"),
Title = dtAction.Rows[0].Field<string>("title"),
Body = dtAction.Rows[0].Field<string>("body"),
FooterLink = bool.Parse(dtAction.Rows[0].Field<string>("footerLink")),
Updated = DbTime.FromDbSecs(dtAction.Rows[0].Field<long>("updated"))
};
sql = "select b._action, b.title " +
"from cycli_content_links a, cycli_content b " +
"where lower(a.sourceaction) = lower(@a) " +
"and a.destinationaction = b._action";
DataTable dtLink = db.GetDataTable(sql, "@a", action);
if (dtLink.Rows.Count > 0)
{
s.Links = dtLink.AsEnumerable().ToDictionary(key => key.Field<string>("_action"), value => value.Field<string>("title"));
}
string path = VirtualPathUtility.ToAbsolute("~/media/");
// Find images, movies etc
s.Body = _ImageRegex.Replace(s.Body, delegate(Match m)
{
string filename = path + m.Groups["src"].Value;
return "<img src='"+filename+"' alt='"+m.Groups["alt"].Value+"' />";
});
}
}
finally
{
db.Close();
}
return s;
}
public void Save()
{
Updated = DateTime.UtcNow;
// Query checks that race is in the right mode, the invitee is not already invited and that the invite limit has not been exceeded
SQLiteDatabase db = new SQLiteDatabase(true);
try
{
// This query checks that the user has not already been invited
string sql = "select _action from cycli_content where lower(_action) = lower(@a) ";
string a = db.ExecuteScalar(sql, "@a", this.Action);
if (string.IsNullOrEmpty(a))
{
// It's a new one
sql = "insert into cycli_content (title, body, updated, footerLink, _action) values (@t, @b, @u, @f, @a) ";
}
else
{
sql = "update cycli_content set title=@t, body=@b, updated=@u, footerLink=@f where _action=@a";
}
db.ExecuteNonQuery(sql, "@t", Title, "@b", Body, "@u",
DbTime.ToDbSecs(Updated), "@f", FooterLink.ToString(), "@a", this.Action);
// Remove any links
string deletelinksql = "delete from cycli_content_links where sourceaction = @s";
db.ExecuteNonQuery(deletelinksql, "@s", this.Action);
// and add new ones
string insertlinksql = "insert into cycli_content_links (sourceaction, destinationaction) values (@s, @d)";
foreach (KeyValuePair<string, string> l in Links)
{
db.ExecuteNonQuery(insertlinksql, "@s", this.Action, "@d", l.Key);
}
db.CommitTransaction();
}
catch (Exception ex)
{
db.RollbackTransaction();
}
finally
{
}
}
public static SupportArticle[] LoadAll()
{
SupportArticle[] s = new SupportArticle[] { };
// Query checks that race is in the right mode, the invitee is not already invited and that the invite limit has not been exceeded
SQLiteDatabase db = new SQLiteDatabase();
try
{
// This query checks that the user has not already been invited
string sql = "select _action, title, body, footerLink, updated from cycli_content order by title";
DataTable dtContent = db.GetDataTable(sql);
if (dtContent.Rows.Count > 0)
{
s = dtContent.AsEnumerable().Select(dr => new SupportArticle
{
Action = dr.Field<string>("_action"),
Title = dr.Field<string>("title"),
Body = (dr.Field<string>("body").Length > 80 ? dr.Field<string>("body").Substring(0, 77) + "..." : dr.Field<string>("body")),
FooterLink = bool.Parse(dr.Field<string>("footerLink")),
Updated = DbTime.FromDbSecs(dr.Field<long>("updated")),
Links = new Dictionary<string, string>()
}).ToArray();
}
}
finally
{
db.Close();
}
return s;
}
public static Dictionary<string, string> LoadFooterLinks()
{
Dictionary<string, string> l = new Dictionary<string, string>();
// Query checks that race is in the right mode, the invitee is not already invited and that the invite limit has not been exceeded
SQLiteDatabase db = new SQLiteDatabase();
try
{
// This query checks that the user has not already been invited
string sql = "select _action, title from cycli_content where lower(footerLink) = 'true' order by title";
DataTable dtContent = db.GetDataTable(sql);
if (dtContent.Rows.Count > 0)
{
l = dtContent.AsEnumerable().ToDictionary(p => p.Field<string>("_action"), q => q.Field<string>("title"));
}
}
finally
{
db.Close();
}
return l;
}
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;
}
