public static List<RunnerData> GetRunnerEnrolmentData(long raceId, long organizationId)
{
using (var db = new CCEntities())
{
return db.ExecuteStoreQuery<RunnerData>(@"
SELECT
u.UserId,
rr.RunnerRaceRecordId,
u.Firstname + ' ' + u.Lastname AS Name,
CAST(IIF(rr.RunnerRaceRecordId IS NOT NULL, 1, 0) AS BIT) AS UserIsEnrolled,
CAST(IIF(r.CompletedOn IS NOT NULL, 1, 0) AS BIT) AS RaceIsComplete,
ISNULL(rr.VarsityLevelId, u.DefaultVarsityLevelId) AS VarsityLevelId,
ISNULL(rr.RunnerClassificationId, u.DefaultRunnerClassificationId) AS RunnerClassificationId
FROM Race r
INNER JOIN Organization o ON r.OrganizationId = o.OrganizationId
INNER JOIN [User] u ON u.OrganizationId = o.OrganizationId
LEFT OUTER JOIN RunnerRaceRecord rr ON u.UserId = rr.UserId
WHERE r.RaceId = @raceId AND r.OrganizationId = @organizationId
AND (u.Gender = r.GenderRestriction OR r.GenderRestriction = '" + Genders.Unspecified + "')",
new SqlParameter[] {
new SqlParameter("raceId", raceId),
new SqlParameter("organizationId", organizationId)
}).ToList();
}
}
public static List<Split> GetSplitsForRunnerRaceRecord(long runnerRaceRecordId)
{
using (var db = new CCEntities())
{
var rrrs = db.RunnerRaceRecordSegments.Where(r => r.RunnerRaceRecordId == runnerRaceRecordId)
.OrderBy(rrs => rrs.ElapsedTimeInSeconds).ToArray();
var splits = new List<Split>();
for (int i = 0; i < rrrs.Length; i++)
{
var split = new Split
{
RunnerRaceRecordSegmentId = rrrs[i].RunnerRaceRecordSegmentId,
ElapsedTimeInSeconds = rrrs[i].ElapsedTimeInSeconds,
};
if (i == 0)
split.IntervalFromPriorSplit = rrrs[i].ElapsedTimeInSeconds;
else
split.IntervalFromPriorSplit = rrrs[i].ElapsedTimeInSeconds - rrrs[i - 1].ElapsedTimeInSeconds;
splits.Add(split);
}
return splits;
}
}
public static void DeleteSegmentsByRunnerRaceRecordId(long runnerRaceRecordId)
{
using (var db = new CCEntities())
{
db.ExecuteStoreCommand(@"
DELETE FROM RunnerRaceRecordSegment WHERE RunnerRaceRecordId = @runnerRaceRecordId",
new SqlParameter("runnerRaceRecordId", runnerRaceRecordId));
}
}
public static List<Role> GetRolesForOrganizationCreatableByRole(long organizationId, int creatorRoleId)
{
using (var db = new CCEntities())
{
if (creatorRoleId == Ids.Admin && organizationId == Organization.Ids.CCIS)
return db.Roles.ToList();
else
return db.Roles.Where(r => r.RoleId != Ids.Admin).ToList();
}
}
public override string[] GetUsersInRole(string roleName)
{
using (var db = new CCEntities())
{
return (from r in db.Roles
join u in db.Users on r.RoleId equals u.RoleId
where r.RoleName.Equals(roleName, StringComparison.CurrentCultureIgnoreCase)
select u.Username).ToArray();
}
}
public static void UnEnrollRunners(IEnumerable<long> unenrolledRunnerIds)
{
if (unenrolledRunnerIds.Count() > 0)
{
using (var db = new CCEntities())
{
var ids = string.Join(",", unenrolledRunnerIds);
db.ExecuteStoreCommand(@"
DELETE FROM RunnerRaceRecordSegment
WHERE RunnerRaceRecordId IN(" + ids + @");
DELETE FROM RunnerRaceRecord
WHERE RunnerRaceRecordId IN(" + ids + @");");
}
}
}
public IEnumerable<DbValidationError> SaveValidate()
{
var errors = new List<DbValidationError>();
if (string.IsNullOrWhiteSpace(Name))
errors.Add(new DbValidationError("Organization Name is required", "Name"));
if (errors.Count == 0)
{
using (var db = new CCEntities())
{
if (db.Organizations.Any(o => o.Name == Name && o.OrganizationId != OrganizationId))
errors.Add(new DbValidationError("Sorry, but this name is already in use", "Name"));
}
}
return errors;
}
public IEnumerable<DbValidationError> SaveValidate()
{
var errors = new List<DbValidationError>();
if (string.IsNullOrWhiteSpace(Firstname))
errors.Add(new DbValidationError("First Name is required", "Firstname"));
if (string.IsNullOrWhiteSpace(Lastname))
errors.Add(new DbValidationError("Last Name is required", "Lastname"));
if (string.IsNullOrWhiteSpace(Username))
errors.Add(new DbValidationError("Username is required", "Username"));
if (string.IsNullOrWhiteSpace(Password))
errors.Add(new DbValidationError("Password is required", "Password"));
if (!(Gender == Genders.Male || Gender == Genders.Female || Gender == Genders.Unspecified))
errors.Add(new DbValidationError("Gender is required", "Gender"));
if (string.IsNullOrWhiteSpace(Email))
errors.Add(new DbValidationError("Email Address is required", "Email"));
else
{
try { new MailAddress(Email); }
catch (FormatException)
{ errors.Add(new DbValidationError("Invalid Email Address", "Email")); }
}
if (errors.Count == 0)
{
using (var db = new CCEntities())
{
if(db.Users.Any(h => h.Username.Equals(Username, StringComparison.InvariantCultureIgnoreCase)
&& h.UserId != UserId))
errors.Add(new DbValidationError("Username must be unique", "Username"));
if (!db.Roles.Any(r => r.RoleId == RoleId))
errors.Add(new DbValidationError("Invalid Role", "RoleId"));
}
}
return errors;
}
public override string[] GetRolesForUser(string username)
{
using (var db = new CCEntities())
{
string[] array;
User user = db.Users.FirstOrDefault(u => u.Username.Equals(username, StringComparison.CurrentCultureIgnoreCase));
var roles = (from ur in db.Roles
join u in db.Users on ur.RoleId equals u.RoleId
where u.Username == username
select ur.RoleName);
if (roles != null)
{
array = roles.ToArray();
return roles.ToArray();
}
else
return new string[] { };
}
}
public static void DeleteWithId(long raceId)
{
using (var db = new CCEntities())
{
var runnerRaceRecordIds = db.RunnerRaceRecords.Where(rr => rr.RaceId == raceId)
.Select(rr => rr.RunnerRaceRecordId);
if (runnerRaceRecordIds.Count() > 0)
{
db.ExecuteStoreCommand(@"
DELETE FROM RunnerRaceRecordSegment
WHERE RunnerRaceRecordId IN(" + string.Join(",", runnerRaceRecordIds) + ");");
}
db.ExecuteStoreCommand(@"
DELETE FROM RunnerRaceRecord WHERE RaceId = @raceId;
DELETE FROM Race WHERE RaceId = @raceId;",
new SqlParameter("raceId", raceId));
}
}
public IEnumerable<DbValidationError> SaveValidate()
{
var errors = new List<DbValidationError>();
if (errors.Count == 0)
{
using (var db = new CCEntities())
{
if (!db.Races.Any(r => r.RaceId == RaceId))
errors.Add(new DbValidationError("Invalid Race", "RaceId"));
if(!db.RunnerClassifications.Any(rc=> rc.RunnerClassificationId == RunnerClassificationId))
errors.Add(new DbValidationError("Invalid Runner Classification", "RunnerClassificationId"));
if (!db.VarsityLevels.Any(v => v.VarsityLevelId == VarsityLevelId))
errors.Add(new DbValidationError("Invalid Varsity Level", "VarsityLevelId"));
}
}
return errors;
}
public override bool IsUserInRole(string username, string roleName)
{
using (var db = new CCEntities())
{
// Find user by username
User user = db.Users.FirstOrDefault(u => u.Username.Equals(username, StringComparison.CurrentCultureIgnoreCase));
// Select user role by username, compare
var role = (from ur in db.Roles
join u in db.Users on ur.RoleId equals u.RoleId
where u.Username == username
select ur.RoleName);
if (user != null)
return role.Any(r => r.Equals(roleName, StringComparison.CurrentCultureIgnoreCase));
else
return false;
}
}
public override bool RoleExists(string roleName)
{
using (var db = new CCEntities())
{
return db.Roles.Any(r => r.RoleName.Equals(roleName, StringComparison.CurrentCultureIgnoreCase));
}
}
public CCMembershipUser GetUser(string username, string password)
{
using (var db = new CCEntities())
{
var user = db.Users.SingleOrDefault(u => u.Username == username);
if (user == null)
throw new InvalidCredentialsException();
else
{
if (!PasswordHash.ValidatePassword(password, user.Password))
throw new InvalidCredentialsException();
else
return Initialize(user);
}
}
}