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