public static bool Delete(string userId, string profileId) { bool deleted = false; SQLiteDatabase db = new SQLiteDatabase(true); // Can only delete planned string sql = @"delete from cycli_profile_spots where ProfileId = @p"; db.ExecuteNonQuery(sql, "@p", profileId); sql = @"delete from cycli_profiles where ProfileId = @p"; db.ExecuteNonQuery(sql, "@p", profileId); try { db.CommitTransaction(); deleted = true; } // No need to close Db as this is done by CommitTranaction catch (Exception ex) { db.RollbackTransaction(); Console.WriteLine(ex); } return deleted; }
public void Save(string userId) { if (userId != this.UserId) { // stop anyone other than the owner changing return; } long timeNowDb = Utilities.DbTime.ToDbSecs(DateTime.UtcNow); // It's an update string sqlProfile = @"update cycli_profiles set Name=@n, Description=@d, Favourite=@f, ModifiedOn=@m where ProfileId=@p"; string sqlDeleteSpots = @"delete from cycli_profile_spots where ProfileId=@p"; string sqlSpot = @"insert into cycli_profile_spots (ProfileId, X, Y, PointOrder) values (@p, @x, @y, @i)"; SQLiteDatabase db = new SQLiteDatabase(true); try { db.ExecuteNonQuery(sqlProfile, "@n", this.Name, "@d", this.Description, "@f", this.Favourite.ToString(), "@m", timeNowDb, "@p", this.ProfileId); db.ExecuteNonQuery(sqlDeleteSpots,"@p", this.ProfileId); int i = 0; foreach (float[] pt in data) { db.ExecuteNonQuery(sqlSpot, "@p", this.ProfileId, "@x", pt[0], "@y", pt[1], "@i",i); i++; } db.CommitTransaction(); } catch (Exception ex) { try { db.RollbackTransaction(); } catch (Exception ex1) { } } finally { } }
public static void Delete(string userId, string riderId) { // Has this rider been employed in a race string sqlPlayed = @"select count(*) from cycli_race_riders where userid=@u and (Status='Finished' or Status='Abandoned')"; string sqlRiderDelete = @"delete from cycli_virtual_riders where ownerid = @o and userid = @u"; string sqlRiderRaceDelete = @"delete from cycli_race_riders where userid = @u"; string sqlRiderInactive = @"update cycli_virtual_riders set status='Inactive' where ownerid = @o and userid = @u"; bool riderPlayed = false; SQLiteDatabase db = new SQLiteDatabase(); try { riderPlayed = (int.Parse(db.ExecuteScalar(sqlPlayed,"@u",riderId)) > 0); } finally { db.Close(); } // Now reopen for transactions db = new SQLiteDatabase(true); try { if (riderPlayed) { db.ExecuteNonQuery(sqlRiderInactive, "@o", userId, "@u", riderId); } else { // Get rid of rider from any pending races db.ExecuteNonQuery(sqlRiderRaceDelete, "@u", riderId); db.ExecuteNonQuery(sqlRiderDelete, "@o", userId, "@u", riderId); } // Commit closes db db.CommitTransaction(); } catch (Exception ex) { db.RollbackTransaction(); } }
public static Profile New(string userId) { Profile p = new Profile(); long timeNowDb = Utilities.DbTime.ToDbSecs(DateTime.UtcNow); p.ProfileId = Guid.NewGuid().ToString(); p.UserId = userId; p.Name = "New Profile"; p.Description = ""; p.Favourite = false; // It's a new profile string sqlProfile = @"insert into cycli_profiles (ProfileId, UserId, Name, Description, Favourite, CreatedOn, ModifiedOn) " + "values (@p, @u, @n, @d, @f, @c, @m)"; string sqlSpot = @"insert into cycli_profile_spots (ProfileId, X, Y) values (@p, @x, @y)"; SQLiteDatabase db = new SQLiteDatabase(true); try { db.ExecuteNonQuery(sqlProfile, "@p", p.ProfileId, "@u", p.UserId, "@n", p.Name, "@d", p.Description, "@f", p.Favourite.ToString(), "@c", timeNowDb, "@m", timeNowDb); db.ExecuteNonQuery(sqlSpot, "@p", p.ProfileId, "@x", 0, "@y", 50); db.ExecuteNonQuery(sqlSpot, "@p", p.ProfileId, "@x", 100, "@y", 50); db.CommitTransaction(); } catch (Exception ex) { try { db.RollbackTransaction(); } catch(Exception ex1) { } } finally { } return p; }
public void Schedule() { // Set race to scheduled string sqlRiderUpdate = @"update cycli_race_riders set Status='Scheduled' " + "where RaceId=@r and Status='Joined'"; string sqlRaceUpdate = @"update cycli_races set Status='Scheduled' " + "where RaceId=@r and Status='Planned'"; SQLiteDatabase db = new SQLiteDatabase(true); try { db.ExecuteNonQuery(sqlRaceUpdate, "@r", this.RaceId); db.ExecuteNonQuery(sqlRiderUpdate, "@r", this.RaceId); db.CommitTransaction(); } catch (Exception ex) { try { db.RollbackTransaction(); } catch (Exception ex1) { } } finally { // Don't need to close on a transaction } this.Status = @"Scheduled"; foreach (Participant p in this.Participants) { if (p.Status == @"Joined") { p.Status = @"Scheduled"; } } }
public void Start() { // If real person has joined - finish the race straight away if (Participants.Where(p => p.Status == @"Scheduled" && p.RiderType == Rider.RiderType.User).Count() == 0) { this.Status = @"Finished"; } else { this.Status = @"Started"; } System.Diagnostics.Debug.WriteLine("BANG! " + this.Name + " started at " + DateTime.UtcNow.ToString()); SQLiteDatabase db = new SQLiteDatabase(true); try { string sqlUpdate = @"update cycli_races set Status='" + this.Status + "' " + "where RaceId=@r"; db.ExecuteNonQuery(sqlUpdate, "@r", this.RaceId); // Next query won't set any unscheduled to started sqlUpdate = @"update cycli_race_riders set Status='Started' " + "where RaceId=@r and Status='Scheduled'"; db.ExecuteNonQuery(sqlUpdate, "@r", this.RaceId); // Tell the ride manager db.CommitTransaction(); } catch (Exception ex) { Console.WriteLine(ex.Message); db.RollbackTransaction(); } foreach (Participant p in Participants) { if (p.Status == @"Scheduled") { p.Status = @"Started"; } } }
public void Save(string userId) { // Check the time - only allow a minimum of 2 minutes long timeNow = Utilities.DbTime.ToDbSecs(DateTime.UtcNow); if (this.StartDateTime < timeNow - (timeNow % 60) + 120) { this.StartDateTime = timeNow - (timeNow % 60) + 120; } SQLiteDatabase db = new SQLiteDatabase(true); // Query will only allow the race director to save string sqlRace = @"update cycli_races set Name=@n, Description=@de, TargetType=@tt, Configuration=@c, StartDateTime=@d, PNS=@p, " + "Drafting=@dr, HandicapType=@h, " + "ProfileId=@pi, ProfileType=@pt, Minimum=@mn, Maximum=@mx " + "where RaceId=@r and Status='Planned' and RaceDirectorId=@u"; db.ExecuteNonQuery(sqlRace, "@n", this.Name, "@de", this.Description, "@tt", this.TargetType, "@c", this.Configuration, "@d", this.StartDateTime, "@p", this.PowerNormalisedSpeed.ToString(), "@dr", this.Drafting.ToString(), "@h", this.HandicapType.ToString(), "@pi", string.IsNullOrEmpty(this.ProfileId) ? "" : this.ProfileId, "@pt", string.IsNullOrEmpty(this.ProfileType) ? "" : this.ProfileType, "@mn", this.ProfileMin, "@mx", this.ProfileMax, "@r", this.RaceId, "@u", userId); // Delete any old riders string sqlDelete = "delete from cycli_race_riders where raceId = @r"; db.ExecuteNonQuery(sqlDelete, "@r", this.RaceId); // Add in the new riders string sqlParticipant = "insert into cycli_race_riders (UserId, RaceId, Status, RiderType, Handicap) " + "values (@u, @r, @s, @t, @h)"; foreach (Participant p in this.Participants) { db.ExecuteNonQuery(sqlParticipant, "@u", p.UserId, "@r", this.RaceId, "@s", p.Status, "@t", (p.RiderType == Rider.RiderType.User ? "USER" : "VIRTUAL"), "@h", p.Handicap); } try { db.CommitTransaction(); } // No need to close Db as this is done by CommitTranaction catch (Exception ex) { db.RollbackTransaction(); Console.WriteLine(ex); } }
public void Finish(RaceSpot[] rss, Dictionary<string, Stack<RaceSpot>> history) { string sqlRiderUpdate = @"update cycli_race_riders set Position=@p, Distance=@d, Time=@t, Energy=@e, PedalStrokes=@k, Heartbeats=@b, TSS=@i " + "where RaceId=@r and UserId=@u"; string sqlRaceUpdate = @"update cycli_races set Status='Finished' " + "where RaceId=@r and Status='Started'"; SQLiteDatabase db = new SQLiteDatabase(true); try { db.ExecuteNonQuery(sqlRaceUpdate, "@r", this.RaceId); foreach (RaceSpot rs in rss) { db.ExecuteNonQuery(sqlRiderUpdate, "@p", rs.pos, "@d", rs.d, "@t", rs.t, "@e", (int)rs.e, "@k", (int)rs.k, "@b", (int)rs.b, "@i", (int)rs.i, "@r", this.RaceId, "@u", rs.id); } db.CommitTransaction(); } catch (Exception ex) { try { db.RollbackTransaction(); } catch (Exception ex1) { } } finally { // Don't need to close on a transaction } // Do points as a separate transaction string raceSpotSql = @"insert into cycli_race_spots (UserId, RaceId, Time, Distance, Speed, Cadence, HR, Power) " + "values (@u, @r, @t, @d, @s, @c, @h, @p)"; db = new SQLiteDatabase(true); try { foreach (string userId in history.Keys) { Stack<RaceSpot> userHistory = history[userId]; foreach (RaceSpot rs in userHistory) { db.ExecuteNonQuery(raceSpotSql, "@r", this.RaceId, "@u", userId, "@t", rs.t, "@d", rs.d, "@s", rs.s, "@c", rs.c, "@h", rs.h, "@p", rs.p); } } db.CommitTransaction(); } catch (Exception ex) { try { db.RollbackTransaction(); } catch (Exception ex1) { } } finally { } // Mark the race as Finished this.Status = @"Finished"; }
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 New(string userId) { Race race = null; UserRider rider = UserRider.Load(userId); if (rider != null) { // Check we haven't exeeded our limit - this is a backstop test // First check prevents a user creating races Race[] existingRaces = Race.LoadPlannedOrRacing(userId); if (existingRaces.Length < rider.MaximumRaces) { string newRaceId = Guid.NewGuid().ToString(); long startDateTime = Utilities.DbTime.ToDbSecs(DateTime.UtcNow.AddDays(7)); SQLiteDatabase db = new SQLiteDatabase(true); string sql = "insert into cycli_races (RaceId, StartDateTime, RaceDirectorId) " + "values (@r, @s, @rd)"; db.ExecuteNonQuery(sql, "@r", newRaceId, "@s", startDateTime, "@rd", userId); sql = "insert into cycli_race_riders (UserId, RaceId) " + "values (@u, @r)"; db.ExecuteNonQuery(sql, "@r", newRaceId, "@u", userId); try { db.CommitTransaction(); try { race = Load(newRaceId); } catch (Exception ex1) { Console.WriteLine(ex1); } } // No need to close Db as this is done by CommitTranaction catch (Exception ex) { db.RollbackTransaction(); Console.WriteLine(ex); } } } return race; }
// Returns invites, joins and finishes - in that order public static void Cancel(string userId, string raceId) { // Load the race as we need to check status - can only delete planned Race r = Load(raceId); // Protection - we can only cancel if we're the race director. Good try Mr, Mrs or Ms Hacker if (r != null && r.RaceDirectorId == userId && r.Status == @"Planned") { SQLiteDatabase db = new SQLiteDatabase(true); // Can only delete planned string sql = @"delete from cycli_race_riders where RaceId = @r"; db.ExecuteNonQuery(sql, "@r", r.RaceId); sql = @"delete from cycli_races where RaceId = @r"; db.ExecuteNonQuery(sql, "@r", r.RaceId); try { db.CommitTransaction(); } // No need to close Db as this is done by CommitTranaction catch (Exception ex) { db.RollbackTransaction(); Console.WriteLine(ex); } } }