public static void RefreshExerciseGameContestant(this GenericManager <BFDataContext> models, ExerciseGameContestant contestant)
{
try
{
if (contestant != null && contestant.Status == (int)Naming.GeneralStatus.Failed)
{
models.ExecuteCommand(@"UPDATE ExerciseGameRank
SET RankingScore = NULL, Rank = NULL
WHERE (UID = {0})", contestant.UID);
}
foreach (var item in models.GetTable <ExerciseGameItem>())
{
models.RefreshExerciseGameRank(item.ExerciseID);
}
models.RefreshPersonalRank();
}
catch (Exception ex)
{
ApplicationLogging.LoggerFactory.CreateLogger(typeof(ExerciseGameExtensionMethods))
.LogError(ex, ex.Message);
}
}
public static void TerminateRegisterLesson(this CourseContract contract, GenericManager <BFDataContext> models)
{
models.ExecuteCommand(@"UPDATE RegisterLesson
SET Attended = {0}
FROM RegisterLessonContract INNER JOIN
RegisterLesson ON RegisterLessonContract.RegisterID = RegisterLesson.RegisterID
WHERE (RegisterLessonContract.ContractID = {1})", (int)Naming.LessonStatus.課程結束, contract.ContractID);
}
public static void InitializeSystemAnnouncement(this UserProfile profile, GenericManager <BFDataContext> models)
{
models.ExecuteCommand(@"INSERT INTO UserEvent
(UID, Title, StartDate, EndDate, SystemEventID)
SELECT {0}, Title, StartDate, EndDate, EventID
FROM SystemEventBulletin
where EndDate >= {1} And (EventID NOT IN
(SELECT SystemEventID
FROM UserEvent
WHERE (UID = {0})))", profile.UID, DateTime.Today);
}
public static void RefreshPersonalRank(this GenericManager <BFDataContext> models)
{
int rankIndex;
models.ExecuteCommand("DELETE FROM ExerciseGamePersonalRank");
rankIndex = 1;
var personalRank = models.GetTable <ExerciseGamePersonalRank>();
foreach (var p in models.GetTable <ExerciseGameRank>()
.Where(r => r.RankingScore.HasValue)
.GroupBy(r => r.UID).Select(g => new { UID = g.Key, TotalScore = g.Sum(p => p.RankingScore) })
.GroupBy(s => s.TotalScore)
.OrderByDescending(s => s.Key))
{
foreach (var rank in p)
{
var item = models.GetTable <ExerciseGameContestant>().Where(c => c.UID == rank.UID).First();
item.TotalScope = p.Key;
item.Rank = rankIndex;
item.ExerciseGamePersonalRank = new ExerciseGamePersonalRank
{
TotalScope = p.Key.Value,
Rank = rankIndex
};
//personalRank.Add(new ExerciseGamePersonalRank
//{
// UID = rank.UID,
// TotalScope = p.Key.Value,
// Rank = rankIndex
//});
}
rankIndex++;
}
models.SubmitChanges();
}
public static void RefreshExerciseGameRank(this GenericManager <BFDataContext> models, int exerciseID)
{
var exerciseItem = models.GetTable <ExerciseGameItem>().Where(r => r.ExerciseID == exerciseID).First();
var table = models.GetTable <ExerciseGameRank>();
models.ExecuteCommand(@"
UPDATE ExerciseGameRank
SET RankingScore = NULL, Rank = NULL
WHERE (ExerciseID = {0})", exerciseID);
models.ExecuteCommand(@"
UPDATE ExerciseGameRank
SET RecordID = NULL
FROM ExerciseGameRank INNER JOIN
ExerciseGameResult ON ExerciseGameRank.RecordID = ExerciseGameResult.TestID
WHERE (ExerciseGameRank.ExerciseID = {0}) AND (ExerciseGameResult.TestDate < {1})",
exerciseID, DateTime.Today.AddMonths(-3));
IOrderedQueryable <IGrouping <decimal, ExerciseGameRank> > items;
if (exerciseItem.Descending == false)
{
items = table
.Join(models.GetTable <ExerciseGameContestant>().Where(c => c.Status == (int)Naming.GeneralStatus.Successful),
r => r.UID, c => c.UID, (r, c) => r)
.Where(r => r.ExerciseID == exerciseID && r.RecordID.HasValue)
.OrderBy(r => r.ExerciseGameResult.Score) //.Take(10)
.GroupBy(r => r.ExerciseGameResult.Score)
.OrderBy(g => g.Key);
}
else
{
items = table
.Join(models.GetTable <ExerciseGameContestant>().Where(c => c.Status == (int)Naming.GeneralStatus.Successful),
r => r.UID, c => c.UID, (r, c) => r)
.Where(r => r.ExerciseID == exerciseID && r.RecordID.HasValue)
.OrderByDescending(r => r.ExerciseGameResult.Score) //.Take(10)
.GroupBy(r => r.ExerciseGameResult.Score)
.OrderByDescending(g => g.Key);
}
//int score = Math.Min(10, models.GetTable<ExerciseGameContestant>().Where(c => c.Status == (int)Naming.GeneralStatus.Successful).Count());
int score = models.GetTable <ExerciseGameContestant>().Where(c => c.Status == (int)Naming.GeneralStatus.Successful).Count();
int range, rankIndex = 1;
foreach (var g in items)
{
range = 0;
foreach (var rank in g)
{
rank.RankingScore = score;
rank.Rank = rankIndex;
range++;
}
score -= range;
rankIndex++;
}
models.SubmitChanges();
}
