public HttpResponseMessage ChangePassword()
{
string success = "no_user";
// Check old password
var u = Request.Content.ReadAsAsync<ChangeCredentials>().Result;
// Need to check that we're logged on
var userId = CredentialController.Authenticate();
if (!string.IsNullOrEmpty(userId))
{
string sql = @"select password from cycli_riders where UserId=@u and AccountStatus='Active'";
// Check against the database
SQLiteDatabase db = new SQLiteDatabase();
string oldHashedPassword = db.ExecuteScalar(sql, "@u", userId);
if (!string.IsNullOrEmpty(oldHashedPassword) && PasswordHash.ValidatePassword(u.oldPassword, oldHashedPassword))
{
string newHashPassword = PasswordHash.CreateHash(u.newPassword);
// Check against the database
sql = @"update cycli_riders set password=@new where userid=@u and AccountStatus='Active'";
if (db.ExecuteNonQuery(sql, "@new", newHashPassword, "@u", userId, "@old", newHashPassword) > 0)
{
success = "ok";
// Emailer.SendRecoveryConfirmation(u.username, userId, code, u.email);
}
else
{
success = "db_failed";
}
}
else
{
success = "wrong_password";
}
db.Close();
}
var response = Request.CreateResponse<string>(HttpStatusCode.OK, success,
new System.Net.Http.Formatting.JsonMediaTypeFormatter());
return response;
}
private string ValidateRecoveryCredentials(RegisterCredentials u)
{
string sql = @"select UserId from cycli_riders where UserName=@username and Email=@email and (AccountStatus='Active' or AccountStatus='Reset')";
// Check against the database
SQLiteDatabase db = new SQLiteDatabase();
string userId = db.ExecuteScalar(sql, "@username", u.username, "@email", u.email);
if (!string.IsNullOrEmpty(userId))
{
string hash = PasswordHash.CreateHash(u.password);
string code = Guid.NewGuid().ToString();
sql = @"update cycli_riders set activationcode=@a, AccountStatus='Reset', password=@p where userid=@u and AccountStatus='Active'";
if (db.ExecuteNonQuery(sql, "@a", code,"@p", hash, "@u", userId) > 0)
{
Emailer.SendRecoveryConfirmation(u.username, userId, code, u.email);
}
}
db.Close();
return userId;
}
private bool NameIsValid(string username)
{
bool valid = (username.Length >= 5 && username.Length <= 20);
if (valid)
{
// Check against the database
SQLiteDatabase db = new SQLiteDatabase();
string result = db.ExecuteScalar("select 1 from cycli_riders where username=@u", "@u", username);
valid = string.IsNullOrEmpty(result);
db.Close();
}
return valid;
}
public static 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 void Save()
{
Updated = DateTime.UtcNow;
// Query checks that race is in the right mode, the invitee is not already invited and that the invite limit has not been exceeded
SQLiteDatabase db = new SQLiteDatabase(true);
try
{
// This query checks that the user has not already been invited
string sql = "select _action from cycli_content where lower(_action) = lower(@a) ";
string a = db.ExecuteScalar(sql, "@a", this.Action);
if (string.IsNullOrEmpty(a))
{
// It's a new one
sql = "insert into cycli_content (title, body, updated, footerLink, _action) values (@t, @b, @u, @f, @a) ";
}
else
{
sql = "update cycli_content set title=@t, body=@b, updated=@u, footerLink=@f where _action=@a";
}
db.ExecuteNonQuery(sql, "@t", Title, "@b", Body, "@u",
DbTime.ToDbSecs(Updated), "@f", FooterLink.ToString(), "@a", this.Action);
// Remove any links
string deletelinksql = "delete from cycli_content_links where sourceaction = @s";
db.ExecuteNonQuery(deletelinksql, "@s", this.Action);
// and add new ones
string insertlinksql = "insert into cycli_content_links (sourceaction, destinationaction) values (@s, @d)";
foreach (KeyValuePair<string, string> l in Links)
{
db.ExecuteNonQuery(insertlinksql, "@s", this.Action, "@d", l.Key);
}
db.CommitTransaction();
}
catch (Exception ex)
{
db.RollbackTransaction();
}
finally
{
}
}
public static VirtualRider New(string userId)
{
VirtualRider r = new VirtualRider();
UserRider u = UserRider.Load(userId);
r.UserId = Guid.NewGuid().ToString();
r.OwnerId = userId;
r.UserName = "******";
r.BikeWheelSizeMm = 700;
r.EstimatedPower = false;
r.TurboIsCalibrated = false;
r.CurrentTurbo = "";
r.Aggression = 50;
r.PowerMin = 150;
r.Power1Hr = 200;
r.Power5Min = 250;
r.Power1Min = 300;
r.Power5Sec = 500;
// It's a new profile
string sqlCount = @"select count(*) from cycli_virtual_riders where OwnerId=@o";
string sqlRider = @"insert into cycli_virtual_riders (UserId, OwnerId, Username, PowerMinimum, Power1Hr, Power5Min, Power1Min, Power5Sec, Aggression) " +
"values (@u, @o, @n, @p4, @p3, @p2, @p1, @p0, @a) ";
SQLiteDatabase db = new SQLiteDatabase();
try
{
// This is a backstop test - the front end should prevent extra riders
if (int.Parse(db.ExecuteScalar(sqlCount, "@o", userId)) < u.MaximumVirtualRiders)
{
db.ExecuteNonQuery(sqlRider,
"@u", r.UserId,
"@o", r.OwnerId,
"@n", r.UserName,
"@p4", r.PowerMin,
"@p3", r.Power1Hr,
"@p2", r.Power5Min,
"@p1", r.Power1Min,
"@p0", r.Power5Sec,
"@a", r.Aggression);
}
}
finally
{
db.Close();
}
return r;
}
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();
}
}
