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