public static RiderMessage[] GetMessages(string userId,string raceId)
{
List<RiderMessage> messages = new List<RiderMessage>();
SQLiteDatabase db = new SQLiteDatabase();
string sql = "select m.RaceId, u.userid,u.username, m.message, m.SendTime " +
"from cycli_rider_messages m, cycli_riders u " +
"where m.SenderId = u.UserId and " +
"m.RaceId = @r " +
"order by m.SendTime";
DataTable dt = db.GetDataTable(sql,"@r",raceId, "@t", "fromTime");
db.Close();
foreach (DataRow dr in dt.Rows)
{
RiderMessage message = new RiderMessage();
message.SenderId = (string)dr["userid"];
message.Sender = (string)dr["username"];
message.RaceId = (string)dr["RaceId"];
message.Message = (string)dr["Message"];
message.SendTime = (long)(int)dr["SendTime"];
messages.Add(message);
}
return messages.ToArray();
}
public static Friend[] Load(string userId)
{
List<Friend> friends = new List<Friend>();
SQLiteDatabase db = new SQLiteDatabase();
string sql = @"select * from (select r.UserId as UserId, r.UserName as UserName, " +
"r.Turbo, t.Power_Model_C1, t.Power_Model_C2, t.Power_Model_C3, " +
"f.Status as Status " +
"from cycli_riders r, cycli_friends f, cycli_turbos t " +
"where f.UserId = '" + userId + "' and r.UserId=f.FriendId " +
"and r.Turbo = t.Type " +
"union " +
"select r.UserId as UserId, r.UserName as UserName, "+
"r.Turbo, t.Power_Model_C1, t.Power_Model_C2, t.Power_Model_C3, " +
"f.Status as Status " +
"from cycli_riders r, cycli_friends f, cycli_turbos t " +
"where f.FriendId= '" + userId + "' and r.UserId=f.UserId "+
"and r.Turbo = t.Type " +
") order by UserName";
DataTable dtFriends = db.GetDataTable(sql);
foreach (DataRow dr in dtFriends.Rows)
{
Friend f = new Friend()
{
UserId = dr.Field<string>("UserId"),
UserName = dr.Field<string>("UserName"),
Turbo = dr.Field<string>("Turbo"),
Coefficients = new double[]{dr.Field<double>("Power_Model_C1"),dr.Field<double>("Power_Model_C2"),dr.Field<double>("Power_Model_C3")},
Status = (string)dr["Status"]
};
friends.Add(f);
}
return friends.ToArray();
}
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, int> SetHandicaps(string raceId)
{
Dictionary<string, int> handicaps = new Dictionary<string, int>();
Race race = Load(raceId);
foreach (Participant p in race.Participants)
{
handicaps.Add(p.UserId, 100);
}
if (race != null)
{
SQLiteDatabase db = new SQLiteDatabase();
long yearAgo = DbTime.ToDbSecs(DateTime.UtcNow);
string sql = "select rr1.UserId, " +
"r.StartDateTime, " +
"(1000 * cast(rr1.Energy as real)/ cast(rr1.Time as real)) as Power " +
"From cycli_race_riders rr1, cycli_race_riders rr2, cycli_races r " +
"where r.StartDateTime > @t " +
"and r.RaceId = rr2.RaceId " +
"and rr1.Time > 0 " +
"and rr1.Energy > 0 " +
"and (rr1.Status='Finished' or rr1.Status='Abandoned') " +
"and rr1.UserId = rr2.UserId " +
"and r.RaceId = @r " +
"order by rr1.Time";
DataTable dt = db.GetDataTable(sql, "@t", yearAgo, "@r", raceId);
db.Close();
// Weight the average power by (a) time in the past
// (b) closeness to the selected distance
Dictionary<string, double> userPowers = dt.AsEnumerable()
.GroupBy(p => p.Field<string>("UserId"))
.ToDictionary(p => p.Key,
p => p.Sum(q => (double)(yearAgo - q.Field<long>("StartDateTime")) * q.Field<double>("Power"))/
p.Sum(q => yearAgo - q.Field<long>("StartDateTime")));
// Total AveragePower across all users
double meanUserPower = userPowers.Values.Average(p => p);
if (meanUserPower > 0)
{
foreach (KeyValuePair<string, double> k in userPowers)
{
int handicap = (int)Math.Round(100 * meanUserPower / k.Value);
handicaps[k.Key] = handicap;
}
}
string updateSql = "update cycli_race_riders set handicap=@h where raceid=@r and userid=@u";
db = new SQLiteDatabase(true);
try
{
foreach (KeyValuePair<string, int> h in handicaps)
{
db.ExecuteNonQuery(updateSql, "@h", h.Value, "@r", raceId, "@u", h.Key);
}
try
{
db.CommitTransaction();
}
catch (Exception ex)
{
db.RollbackTransaction();
}
}
finally
{
}
}
return handicaps;
}
public static Race[] LoadScheduledOrRacing(string userId)
{
SQLiteDatabase db = new SQLiteDatabase();
// Only load if
// (a) All planned associated with the user
// (b) Started or Finished if the user is Joined, Finished or Abandoned
// REVIEW PERFORMANCE OF THE UNION IN THIS QUERY
string sql = "select r.RaceId as RaceId, r.Name as Name, r.Description as Description, " +
"r.StartDateTime as StartDateTime, r.TargetType as TargetType, r.Configuration as Configuration , r.RaceDirectorId as RaceDirectorId, " +
"r.PNS as PNS, r.Drafting as Drafting, r.HandicapType as HandicapType, r.ProfileId as ProfileId, " +
"r.ProfileType as ProfileType, " +
"r.Minimum as Minimum, r.Maximum as Maximum,r.Status as RaceStatus, u.UserId as UserId , u.UserName as UserName, " +
"u.ThresholdPower as ThresholdPower,rr1.Status as Status, rr1.Position as Position, rr1.Distance as Distance, rr1.Time as Time, rr1.Energy as Energy, " +
"rr1.PedalStrokes as PedalStrokes, rr1.Heartbeats as Heartbeats, rr1.TSS as TSS, " +
"rr1.RiderType as RiderType, rr1.Handicap as Handicap " +
"From cycli_races r, cycli_race_riders rr, cycli_race_riders rr1, " +
"cycli_riders u " +
"where rr.UserId = @u1 and " +
"rr.RaceId = rr1.RaceId and " +
"u.UserId = rr1.UserId and " +
"rr.RaceId = r.RaceId and " +
"(r.Status = 'Scheduled' or r.Status = 'Started') " +
"union " +
"select r.RaceId as RaceId, r.Name as Name, r.Description as Description, " +
"r.StartDateTime as StartDateTime, r.TargetType as TargetType, r.Configuration as Configuration , r.RaceDirectorId as RaceDirectorId, " +
"r.PNS as PNS, r.Drafting as Drafting, r.HandicapType as HandicapType, r.ProfileId as ProfileId, r.ProfileType as ProfileType, " +
"r.Minimum as Minimum, r.Maximum as Maximum,r.Status as RaceStatus, u.UserId as UserId , u.UserName as UserName, " +
"u.Power1Hr as ThresholdPower,rr1.Status as Status, rr1.Position as Position, rr1.Distance as Distance, rr1.Time as Time, rr1.Energy as Energy, " +
"rr1.PedalStrokes as PedalStrokes, rr1.Heartbeats as Heartbeats, rr1.TSS as TSS, " +
"rr1.RiderType as RiderType, rr1.Handicap as Handicap " +
"From cycli_races r, cycli_race_riders rr, cycli_race_riders rr1, " +
"cycli_virtual_riders u " +
"where rr.UserId = @u2 and " +
"rr.RaceId = rr1.RaceId and " +
"u.UserId = rr1.UserId and " +
"rr.RaceId = r.RaceId and " +
"(r.Status = 'Scheduled' or r.Status = 'Started') " +
" order by r.StartDateTime, r.RaceId";
DataTable dtRaces = db.GetDataTable(sql, "@u1", userId, "@u2", userId);
db.Close();
Race[] rs = BuildRaceArray(dtRaces);
return rs;
}
public static RaceResult[] LoadHistories(string userId)
{
RaceResult[] res = new RaceResult[] { };
SQLiteDatabase db = new SQLiteDatabase();
string sql = "select r.raceId as RaceId, r.Name as Name, r.StartDateTime as StartDateTime, " +
"u.UserId as UserId, u.UserName as UserName, rr.Distance as Distance, rr.Time as Time, rr.Energy as Energy, " +
"rr.PedalStrokes as PedalStrokes, rr.Heartbeats as Heartbeats, rr.TSS as TSS " +
"from cycli_race_riders rr, cycli_races r, cycli_riders u " +
"where u.userId = @u " +
"and rr.raceId = r.raceId " +
"and rr.userid = u.userid " +
"and (rr.Status = 'Finished' or rr.Status = 'Abandoned') and rr.Distance > 0 and rr.Time > 0 " +
"order by r.StartDateTime";
long secsNow = DbTime.ToDbSecs(DateTime.UtcNow);
DataTable dtResults = db.GetDataTable(sql, "@u", userId);
if(dtResults.Rows.Count > 0) {
res = dtResults.AsEnumerable()
.Where(p => p.Field<long>("Time") > 0)
.Select(p => new RaceResult() {
RaceId = p.Field<string>("RaceId"),
RaceName = p.Field<string>("Name"),
DeltaStartDateTime = p.Field<long>("StartDateTime") - secsNow,
UserId = p.Field<string>("UserId"),
UserName = p.Field<string>("UserName"),
Distance = 0.001f * (float)p.Field<double>("Distance"),
Time = (float)p.Field<long>("Time")/60000f,
Energy = (int)(p.Field<long>("Energy")),
TSS = (int)(p.Field<long>("TSS")),
MeanSpeed = 3600f * (float)p.Field<double>("Distance") / (float)p.Field<long>("Time"),
MeanCadence = (int)(60000 * p.Field<long>("PedalStrokes") / (float)p.Field<long>("Time")),
MeanHeartrate = (int)(60000 * p.Field<long>("Heartbeats") / (float)p.Field<long>("Time")),
MeanPower = (int)(1000 * p.Field<long>("Energy") / (float)p.Field<long>("Time"))
}).ToArray();
}
db.Close();
return res;
}
public static long[] GetDates(string userId)
{
SQLiteDatabase db = new SQLiteDatabase();
string sql = "select r.StartDateTime " +
"From cycli_races r, cycli_race_riders rr " +
"where rr.UserId ='" + userId + "' and " +
"rr.RaceId = r.RaceId and " +
"r.Status = 'Finished' and " +
"not rr.Status ='Invited' " +
"order by r.StartDateTime";
DataTable dtRaces = db.GetDataTable(sql);
db.Close();
List<long> dates = new List<long>();
foreach (DataRow dr in dtRaces.Rows)
{
dates.Add(dr.Field<long>("StartDateTime"));
}
return dates.ToArray();
}
public static Dictionary<string, float[][]> Biometrics(string userId, string riderId, string raceId)
{
Race race = Race.Load(raceId);
Dictionary<string, float[][]> biometrics = new Dictionary<string, float[][]>();
SQLiteDatabase db = new SQLiteDatabase();
string sql = "select s.Time, s.Distance, s.Speed, s.Cadence, s.Hr, s.Power, r.TargetType, r.Configuration " +
"From cycli_race_spots s, cycli_races r " +
"where s.UserId =@u " +
"and s.RaceId = @r " +
"and s.RaceId = r.RaceId " +
"order by s.Time";
DataTable dt = db.GetDataTable(sql, "@u", riderId, "@r", raceId);
if (dt.Rows.Count > 0)
{
// Need to scale by race type
float[][] speedProfile = dt.AsEnumerable().Select(p => new float[] { (float)p.Field<dynamic>(race.RaceType.TargetProgress), (float)p.Field<double>("Speed") }).ToArray();
biometrics.Add("speed", speedProfile);
float[][] cadenceProfile = dt.AsEnumerable().Select(p => new float[] { (float)p.Field<dynamic>(race.RaceType.TargetProgress), (float)p.Field<long>("Cadence") }).ToArray();
biometrics.Add("cadence", cadenceProfile);
float[][] hrProfile = dt.AsEnumerable().Select(p => new float[] { (float)p.Field<dynamic>(race.RaceType.TargetProgress), (float)p.Field<long>("Hr") }).ToArray();
biometrics.Add("hr", hrProfile);
float[][] powerProfile = dt.AsEnumerable().Select(p => new float[] { (float)p.Field<dynamic>(race.RaceType.TargetProgress), (float)p.Field<long>("Power") }).ToArray();
biometrics.Add("power", powerProfile);
}
db.Close();
return biometrics;
}
public static Profile[] LoadAll(string userId)
{
List<Profile> profiles = new List<Profile>();
string sql = "select p.UserId, p.ProfileId, p.Name, p.Description, p.Favourite, p.CreatedOn, p.ModifiedOn, s.X, s.Y " +
"from cycli_profiles p, cycli_profile_spots s " +
"where " +
"s.ProfileId = p.ProfileId " +
"and p.UserId = '"+userId+"' order by p.ProfileId, s.PointOrder";
SQLiteDatabase db = new SQLiteDatabase();
DataTable dt = db.GetDataTable(sql);
string profileId = "";
Profile thisProfile = null;
foreach (DataRow dr in dt.Rows)
{
if (profileId != (string)dr["ProfileId"])
{
// New profile
profileId = (string)dr["ProfileId"];
Profile p = new Profile();
p.UserId = userId;
p.ProfileId = profileId;
p.Favourite = ((string)dr["Favourite"] == bool.TrueString);
p.Name = (string)dr["Name"];
p.Description = (string)dr["Description"];
profiles.Add(p);
thisProfile = p;
}
float[] pt = {(float)(long)dr["X"], (float)(long)dr["Y"]};
thisProfile.data.Add(pt);
}
db.Close();
return profiles.ToArray();
}
public static VirtualRider Load(string riderId)
{
VirtualRider thisRider = null;
SQLiteDatabase db = new SQLiteDatabase();
string sql = @"select OwnerId,UserId, Username, PowerMinimum, Power1Hr, Power5Min, Power1Min, Power5Sec, Aggression " +
"From cycli_virtual_riders r " +
"where UserId=@u and Status='Active'";
// Only load active accounts
DataTable dtUser = db.GetDataTable(sql,"@u", riderId);
if (dtUser.Rows.Count > 0)
{
DataRow dr = dtUser.Rows[0];
thisRider = new VirtualRider();
thisRider.UserName = dr.Field<string>("Username");
thisRider.UserId = dr.Field<string>("UserId");
thisRider.OwnerId = dr.Field<string>("OwnerId");
thisRider.BikeWheelSizeMm = 700;
thisRider.PowerMin = (int)dr.Field<long>("PowerMinimum");
thisRider.Power1Hr = (int)dr.Field<long>("Power1Hr");
thisRider.Power5Min = (int)dr.Field<long>("Power5Min");
thisRider.Power1Min = (int)dr.Field<long>("Power1Min");
thisRider.Power5Sec = (int)dr.Field<long>("Power5Sec");
thisRider.Aggression = (int)dr.Field<long>("Aggression");
}
db.Close();
return thisRider;
}
public static UserRider Load(string userId)
{
UserRider thisRider = null;
SQLiteDatabase db = new SQLiteDatabase();
string sql = @"select r.UserId, r.FirstName, r.Surname, r.Username, r.Email, r.Nationality, n.Flag, r.DoB, r.Sex, r.BikeWheelSizeMm, r.Turbo, " +
"r.TurboIsCalibrated, r.EstimatedPower,r.HrAsPercentage, r.MaxHr, r.MaxHrFromAge, r.HeightCm, r.WeightKg, r.ThresholdPower, r.FriendCode, r.AccountType " +
"From cycli_riders r LEFT OUTER JOIN cycli_nationalities n " +
"ON r.Nationality = n.Nationality " +
"where UserId=@u and AccountStatus='Active'";
// Only load active accounts
DataTable dtUser = db.GetDataTable(sql, "@u", userId);
if (dtUser.Rows.Count > 0)
{
DataRow dr = dtUser.Rows[0];
thisRider = new UserRider();
thisRider.FirstName = dr["FirstName"] == DBNull.Value ? "" : dr.Field<string>("FirstName");
thisRider.Surname= (dr["Surname"] == DBNull.Value ? "" : dr.Field<string>("Surname"));
thisRider.UserName = dr.Field<string>("Username");
thisRider.UserId= dr.Field<string>("UserId");
thisRider.Email = dr.Field<string>("Email");
thisRider.Nationality = dr.Field<string>("Nationality");
thisRider.FlagFile = dr["Flag"] == DBNull.Value ? "" :dr.Field<string>("Flag");
thisRider.DoB = dr.Field<long>("DoB");
thisRider.IsMale = dr.Field<string>("Sex") == bool.TrueString;
thisRider.BikeWheelSizeMm = (int)dr.Field<long>("BikeWheelSizeMm");
thisRider.CurrentTurbo = dr.Field<string>("Turbo");
thisRider.TurboIsCalibrated = (dr.Field<string>("TurboIsCalibrated") == bool.TrueString);
thisRider.EstimatedPower = (dr.Field<string>("EstimatedPower") == bool.TrueString);
thisRider.HrAsPercentage = dr.Field<long>("HrAsPercentage") == 1;
thisRider.MaxHr = (int)dr.Field<long>("MaxHr");
thisRider.MaxHrFromAge = dr.Field<long>("MaxHrFromAge") == 1;
thisRider.HeightCm = (int)dr.Field<long>("HeightCm");
thisRider.WeightKg = (int)dr.Field<long>("WeightKg");
thisRider.ThresholdPower = (int)dr.Field<long>("ThresholdPower");
thisRider.FriendCode = dr.Field<string>("FriendCode");
thisRider.AccountType = dr.Field<string>("AccountType");
thisRider.MaximumRiders = thisRider.AccountType == @"FREE" ? Properties.Settings.Default.MAX_RIDERS_FREE : Properties.Settings.Default.MAX_RIDERS_PREMIUM;
thisRider.MaximumFriends = thisRider.AccountType == @"FREE" ? Properties.Settings.Default.MAX_FRIENDS_FREE : Properties.Settings.Default.MAX_FRIENDS_PREMIUM;
thisRider.MaximumRaces = thisRider.AccountType == @"FREE" ? Properties.Settings.Default.MAX_RACES_FREE : Properties.Settings.Default.MAX_RACES_PREMIUM;
thisRider.MaximumVirtualRiders = thisRider.AccountType == @"FREE" ? Properties.Settings.Default.MAX_VIRTUAL_RIDERS_FREE : Properties.Settings.Default.MAX_VIRTUAL_RIDERS_PREMIUM;
}
db.Close();
return thisRider;
}
public static string[] Nationalities()
{
SQLiteDatabase db = new SQLiteDatabase();
string sql = @"select nationality from cycli_nationalities";
DataTable dtNationality = db.GetDataTable(sql);
string[] nationalities = new string[] { };
if (dtNationality.Rows.Count > 0)
{
nationalities = dtNationality.AsEnumerable().Select(p => (string)p[0]).ToArray();
}
return nationalities;
}
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 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 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 static VirtualRider[] LoadAll(string userId)
{
List<VirtualRider> riders = new List<VirtualRider>();
SQLiteDatabase db = new SQLiteDatabase();
string sql = @"select UserId, Username, PowerMinimum, Power1Hr, Power5Min, Power1Min, Power5Sec, Aggression " +
"From cycli_virtual_riders r " +
"where OwnerId=@u and Status='Active'";
// Only load active accounts
DataTable dtUser = db.GetDataTable(sql,"@u", userId);
foreach (DataRow dr in dtUser.Rows)
{
VirtualRider thisRider = new VirtualRider();
thisRider.UserName = dr.Field<string>("Username");
thisRider.UserId = dr.Field<string>("UserId");
thisRider.OwnerId = userId;
thisRider.BikeWheelSizeMm = 700;
thisRider.PowerMin = (int)dr.Field<long>("PowerMinimum");
thisRider.Power1Hr = (int)dr.Field<long>("Power1Hr");
thisRider.Power5Min = (int)dr.Field<long>("Power5Min");
thisRider.Power1Min = (int)dr.Field<long>("Power1Min");
thisRider.Power5Sec = (int)dr.Field<long>("Power5Sec");
thisRider.Aggression = (int)dr.Field<long>("Aggression");
riders.Add(thisRider);
}
db.Close();
return riders.ToArray();
}
public static Profile Load(string profileId)
{
Profile profile = null;
string sql = "select p.UserId, p.ProfileId, p.Name, p.Description, p.Favourite, p.CreatedOn, p.ModifiedOn, s.X, s.Y " +
"from cycli_profiles p, cycli_profile_spots s " +
"where " +
"s.ProfileId = p.ProfileId " +
"and p.ProfileId = '" + profileId + "' " +
"order by s.PointOrder";
SQLiteDatabase db = new SQLiteDatabase();
DataTable dt = db.GetDataTable(sql);
foreach (DataRow dr in dt.Rows)
{
if (profile == null)
{
// New profile
profile = new Profile();
profileId = (string)dr["ProfileId"];
profile.UserId = (string)dr["ProfileId"];
profile.ProfileId = profileId;
profile.Favourite = ((string)dr["Favourite"] == bool.TrueString);
profile.Name = (string)dr["Name"];
profile.Description = (string)dr["Description"];
}
float[] pt = { (float)(long)dr["X"], (float)(long)dr["Y"] };
profile.data.Add(pt);
}
db.Close();
return profile;
}
public static Rider LoadAny(string userId)
{
Rider thisRider = null;
SQLiteDatabase db = new SQLiteDatabase();
string sql = @"select r.UserId as UserId, r.Username as Username, r.BikeWheelSizeMm as BikeWheelSizeMm, r.Turbo as Turbo, " +
"r.TurboIsCalibrated as TurboIsCalibrated, r.EstimatedPower as EstimatedPower " +
"From cycli_riders r " +
"where UserId=@u1 and AccountStatus='Active'" +
"union " +
"select r.UserId as UserId, r.Username as Username, 700 as BikeWheelSizeMm, null as Turbo, " +
"'False' as TurboIsCalibrated, 'False' as EstimatedPower " +
"From cycli_virtual_riders r " +
"where UserId=@u2 and Status='Active'";
// Only load active accounts
DataTable dtUser = db.GetDataTable(sql, "@u1", userId, "@u2",userId);
if (dtUser.Rows.Count > 0)
{
DataRow dr = dtUser.Rows[0];
thisRider = new Rider();
thisRider.UserName = dr.Field<string>("Username");
thisRider.UserId= dr.Field<string>("UserId");
thisRider.BikeWheelSizeMm = (int)dr.Field<long>("BikeWheelSizeMm");
thisRider.CurrentTurbo = dr.Field<string>("Turbo");
thisRider.TurboIsCalibrated = (dr.Field<string>("TurboIsCalibrated") == bool.TrueString);
thisRider.EstimatedPower = (dr.Field<string>("EstimatedPower") == bool.TrueString);
}
db.Close();
return thisRider;
}
public static int[] CountBetween(string userId, long start, long end)
{
SQLiteDatabase db = new SQLiteDatabase();
string sql = "select r.Status RaceStatus, rr.Status RiderStatus " +
"From cycli_races r, cycli_race_riders rr " +
"where rr.UserId ='" + userId + "' and " +
"rr.RaceId = r.RaceId and " +
"r.StartDateTime between " + start + " and " + end +
" and (RaceStatus = 'Planned' or not rr.Status ='Invited') ";
DataTable dtRaces = db.GetDataTable(sql);
db.Close();
int[] counts = new int[] { 0, 0, 0 };
foreach (DataRow dr in dtRaces.Rows)
{
if ((string)dr["RaceStatus"] == @"Finished")
{
// Only interested if we actually raced - not if we were only invited
if ((string)dr["RiderStatus"] == @"Abandoned" || (string)dr["RiderStatus"] == @"Finished")
{
counts[2]++;
}
}
else
{
if ((string)dr["RiderStatus"] == @"Joined")
{
counts[1]++;
}
else
{
counts[0]++;
}
}
}
return counts;
}
public static TurboTrainer[] LoadAll()
{
TurboTrainer[] turbos = new TurboTrainer[]{};
SQLiteDatabase db = new SQLiteDatabase();
string sql = @"select type, power_model_c1, power_model_c2, power_model_c3 from cycli_turbos order by type";
DataTable dtTurbos = db.GetDataTable(sql);
db.Close();
if (dtTurbos.Rows.Count > 0)
{
turbos = dtTurbos.AsEnumerable().Select(p => new TurboTrainer
{
Type = (string)(p["type"]),
Coefficients = new double[] { (double)p["power_model_c1"], (double)p["power_model_c2"], (double)p["power_model_c3"] }
}).ToArray();
}
return turbos;
}
public static Race Load(string raceId)
{
Race race = null;
SQLiteDatabase db = new SQLiteDatabase();
string sql = "select r.RaceId as RaceId, r.Name as Name, r.Description as Description, " +
"r.StartDateTime as StartDateTime, r.TargetType as TargetType, r.Configuration as Configuration , " +
"r.RaceDirectorId as RaceDirectorId, " +
"r.PNS as PNS, r.Drafting as Drafting, r.HandicapType as HandicapType, " +
"r.ProfileId as ProfileId, r.ProfileType as ProfileType, " +
"r.Minimum as Minimum, r.Maximum as Maximum,r.Status as RaceStatus, u.UserId as UserId , u.UserName as UserName, " +
"u.ThresholdPower as ThresholdPower,rr.Status as Status, rr.Position as Position, rr.Distance as Distance, rr.Time as Time, rr.Energy as Energy, " +
"rr.PedalStrokes as PedalStrokes, rr.Heartbeats as Heartbeats, rr.TSS as TSS, " +
"rr.RiderType as RiderType, rr.Handicap as Handicap " +
"From cycli_races r, cycli_race_riders rr, cycli_riders u " +
"where " +
"u.UserId = rr.UserId and " +
"rr.RaceId = r.RaceId and " +
"r.RaceId = @r1 " +
"union " +
"select r.RaceId as RaceId, r.Name as Name, r.Description as Description, " +
"r.StartDateTime as StartDateTime, r.TargetType as TargetType, r.Configuration as Configuration , " +
"r.RaceDirectorId as RaceDirectorId, " +
"r.PNS as PNS, r.Drafting as Drafting, r.HandicapType as HandicapType, " +
"r.ProfileId as ProfileId, r.ProfileType as ProfileType, " +
"r.Minimum as Minimum, r.Maximum as Maximum,r.Status as RaceStatus, u.UserId as UserId , u.UserName as UserName, " +
"u.Power1Hr as ThresholdPower,rr.Status as Status, rr.Position as Position, rr.Distance as Distance, rr.Time as Time, rr.Energy as Energy, " +
"rr.PedalStrokes as PedalStrokes, rr.Heartbeats as Heartbeats, rr.TSS as TSS, "+
"rr.RiderType as RiderType, rr.Handicap as Handicap " +
"From cycli_races r, cycli_race_riders rr, cycli_virtual_riders u " +
"where " +
"u.UserId = rr.UserId and " +
"rr.RaceId = r.RaceId and " +
"r.RaceId = @r2 " +
" order by UserName";
DataTable dtRaces = db.GetDataTable(sql, "@r1", raceId, "@r2", raceId);
db.Close();
Race[] races = BuildRaceArray(dtRaces);
if (races.Length > 0)
{
race = races[0];
}
return race;
}
public static TurboTrainer Load(string userId, string turboType, bool turboIsCalibrated)
{
TurboTrainer thisTurbo = null;
SQLiteDatabase db = new SQLiteDatabase();
string sqlNonCalibrated = @"select t.type, t.power_model_c1, t.power_model_c2, t.power_model_c3 from " +
"cycli_turbos t where t.type=@t";
string sqlCalibrated = @"select t.type, t.power_model_c1, t.power_model_c2, t.power_model_c3 from " +
"cycli_rider_turbos t where t.UserId=@u and t.type=@t";
DataTable dtUser = null;
if (turboIsCalibrated)
{
dtUser = db.GetDataTable(sqlCalibrated,"@u",userId,"@t",turboType);
// Not defined yet - use the non standard values
if (dtUser.Rows.Count == 0)
{
dtUser = db.GetDataTable(sqlNonCalibrated, "@t", turboType);
}
}
else
{
dtUser = db.GetDataTable(sqlNonCalibrated, "@t", turboType);
}
if (dtUser.Rows.Count > 0)
{
DataRow dr = dtUser.Rows[0];
thisTurbo = new TurboTrainer();
thisTurbo.UserId = userId;
thisTurbo.Type = (string)(dr["type"]);
thisTurbo.Coefficients = new double[]{(double)dr["power_model_c1"],(double)dr["power_model_c2"],(double)dr["power_model_c3"]};
}
db.Close();
return thisTurbo;
}
public static Race LoadLastRace(string userId)
{
Race[] rs = new Race[] { };
SQLiteDatabase db = new SQLiteDatabase();
string lastSql = "select r.raceId from cycli_race_riders rr, cycli_races r " +
"where rr.userId = @u " +
"and rr.raceId = r.raceId " +
"and (r.Status = 'Finished' or r.Status = 'Abandoned') " +
"order by r.StartDateTime desc limit 1";
string raceId = db.ExecuteScalar(lastSql, "@u", userId);
if (!string.IsNullOrEmpty(raceId))
{
string sql = "select r.RaceId as RaceId, r.Name as Name, r.Description as Description, " +
"r.StartDateTime as StartDateTime, r.TargetType as TargetType, r.Configuration as Configuration , r.RaceDirectorId as RaceDirectorId, " +
"r.PNS as PNS, r.Drafting as Drafting, r.HandicapType as HandicapType, r.ProfileId as ProfileId, r.ProfileType as ProfileType, " +
"r.Minimum as Minimum, r.Maximum as Maximum,r.Status as RaceStatus, u.UserId as UserId , u.UserName as UserName, " +
"u.ThresholdPower as ThresholdPower,rr1.Status as Status, rr1.Position as Position, rr1.Distance as Distance, rr1.Time as Time, rr1.Energy as Energy, " +
"rr1.PedalStrokes as PedalStrokes, rr1.Heartbeats as Heartbeats, rr1.TSS as TSS, " +
"rr1.RiderType as RiderType, rr1.Handicap as Handicap " +
"From cycli_races r, cycli_race_riders rr1, cycli_riders u " +
"where u.UserId = rr1.UserId " +
"and r.RaceId = rr1.RaceId " +
"and r.RaceId = @r " +
"and not rr1.Status ='Invited' " +
"union " +
"select r.RaceId as RaceId, r.Name as Name, r.Description as Description, " +
"r.StartDateTime as StartDateTime, r.TargetType as TargetType, r.Configuration as Configuration , r.RaceDirectorId as RaceDirectorId, " +
"r.PNS as PNS, r.Drafting as Drafting, r.HandicapType as HandicapType, r.ProfileId as ProfileId, r.ProfileType as ProfileType, " +
"r.Minimum as Minimum, r.Maximum as Maximum,r.Status as RaceStatus, u.UserId as UserId , u.UserName as UserName, " +
"u.Power1Hr as ThresholdPower,rr1.Status as Status, rr1.Position as Position, rr1.Distance as Distance, rr1.Time as Time, rr1.Energy as Energy, " +
"rr1.PedalStrokes as PedalStrokes, rr1.Heartbeats as Heartbeats, rr1.TSS as TSS, " +
"rr1.RiderType as RiderType, rr1.Handicap as Handicap " +
"From cycli_races r, cycli_race_riders rr1, " +
"cycli_virtual_riders u " +
"where u.UserId = rr1.UserId " +
"and r.RaceId = rr1.RaceId " +
"and r.RaceId = @r " +
" order by Position";
DataTable dtRaces = db.GetDataTable(sql, "@r", raceId);
rs = BuildRaceArray(dtRaces);
}
db.Close();
return rs.Length > 0 ? rs[0] : null;
}
public static Friend MakeFriend(string userId, string friendCode)
{
Friend friend = null;
UserRider rider = UserRider.Load(userId);
if (rider != null)
{
Friend[] existingFriends = Load(userId);
SQLiteDatabase db = new SQLiteDatabase();
// Some logic here - we only allow a new friend if
// (a) The friendship does not already exist
// (b) The friendCode is correct
// (c) We haven't exeeded out friend limit
string sqlFriend = "select UserId, UserName from cycli_riders where FriendCode = @c";
DataTable dt = db.GetDataTable(sqlFriend, "@c", friendCode);
if (dt.Rows.Count > 0)
{
string friendId = dt.Rows[0].Field<string>("UserId");
string friendName = dt.Rows[0].Field<string>("UserName");
if (existingFriends.Count() < rider.MaximumFriends && existingFriends.Count(p => p.UserId == friendId) == 0)
{
string sql = "insert into cycli_friends (UserId, FriendId, Status) values (@u, @f, 'Accepted')";
if (db.ExecuteNonQuery(sql, "@u", userId, "@f", friendId) == 1)
{
friend = new Friend { UserName = friendName, UserId = friendId, Status = "Accepted" };
}
}
}
// This query checks that the user has not already been invited
db.Close();
}
return friend;
}
// Initiates race processors when required. Responsbile for creating the
// race object passed to the client during a race
public static Race[] ScheduleRaces(long start)
{
SQLiteDatabase db = new SQLiteDatabase();
string sql = "select r.RaceId as RaceId, r.Name as Name, r.Description as Description, " +
"r.StartDateTime as StartDateTime, r.TargetType as TargetType, r.Configuration as Configuration , r.RaceDirectorId as RaceDirectorId, " +
"r.PNS as PNS, r.Drafting as Drafting, r.HandicapType as HandicapType, r.ProfileId as ProfileId, r.ProfileType as ProfileType, " +
"r.Minimum as Minimum, r.Maximum as Maximum,r.Status as RaceStatus, u.UserId as UserId , u.UserName as UserName, " +
"u.ThresholdPower as ThresholdPower,rr.Status as Status, rr.Position as Position, rr.Result as Result, rr.Distance as Distance, rr.Time as Time, rr.Energy as Energy, " +
"rr.PedalStrokes as PedalStrokes, rr.Heartbeats as Heartbeats, rr.TSS as TSS, " +
"rr.RiderType as RiderType, rr.Handicap as Handicap " +
"From cycli_races r, cycli_race_riders rr, cycli_riders u " +
"where " +
"rr.RaceId = r.RaceId and " +
"rr.UserId = u.UserId and " +
"RaceStatus ='Planned' and " +
"r.StartDateTime < @s1 " +
"union " +
"select r.RaceId as RaceId, r.Name as Name, r.Description as Description, " +
"r.StartDateTime as StartDateTime, r.TargetType as TargetType, r.Configuration as Configuration , r.RaceDirectorId as RaceDirectorId, " +
"r.PNS as PNS, r.Drafting as Drafting, r.HandicapType as HandicapType, r.ProfileId as ProfileId, r.ProfileType as ProfileType, " +
"r.Minimum as Minimum, r.Maximum as Maximum,r.Status as RaceStatus, u.UserId as UserId , u.UserName as UserName, " +
"u.Power1Hr as ThresholdPower,rr.Status as Status, rr.Position as Position, rr.Result as Result, rr.Distance as Distance, rr.Time as Time, rr.Energy as Energy, " +
"rr.PedalStrokes as PedalStrokes, rr.Heartbeats as Heartbeats, rr.TSS as TSS, " +
"rr.RiderType as RiderType, rr.Handicap as Handicap " +
"From cycli_races r, cycli_race_riders rr, cycli_virtual_riders u " +
"where " +
"rr.RaceId = r.RaceId and " +
"rr.UserId = u.UserId and " +
"RaceStatus ='Planned' and " +
"r.StartDateTime < @s2 " +
" order by r.StartDateTime, r.RaceId";
DataTable dtRaces = db.GetDataTable(sql, "@s1", start, "@s2", start);
db.Close();
return BuildRaceArray(dtRaces);
}
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;
}
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;
}
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;
}
