public object Save(personalscroce personalscroce)
{
if (personalscroce == null || string.IsNullOrEmpty(personalscroce.PGuid) || string.IsNullOrEmpty(personalscroce.SubjectGuid) || personalscroce.AchieveDate.HasValue == false)
{
return("成绩信息不完整，录入失败");
}
var temp = personalscroceManager.CurrentDb.AsQueryable().Where(t => t.SubjectGuid == personalscroce.SubjectGuid && t.AchieveDate == personalscroce.AchieveDate && t.PGuid == personalscroce.PGuid).First();
if (temp != null)
{
temp.Score = personalscroce.Score;
return(personalscroceManager.Update(temp));
}
if (string.IsNullOrEmpty(personalscroce.Guid))
{
personalscroce.Create();
return(personalscroceManager.Insert(personalscroce));
}
else if (personalscroceManager.GetById(personalscroce.Guid) != null)
{
return(personalscroceManager.Update(personalscroce));
}
else
{
return(personalscroceManager.Insert(personalscroce));
}
}
public object GetSportRecord(string pguid, string subguid)
{
var pInfo = personalfilesManager.GetById(pguid);
//获取近十次考核时间
List <DateTime?> dateTimes = personalscroceManager.CurrentDb.AsQueryable().GroupBy(t => t.AchieveDate).Where(t => t.SubjectGuid == subguid).OrderBy(t => t.AchieveDate, OrderByType.Desc).Select(t => t.AchieveDate).ToPageList(1, 11); //最后一次单次名次需要上次成绩，故为11
var scoreInfo = personalscroceManager.CurrentDb.AsQueryable().Where(t => t.PGuid == pguid && t.SubjectGuid == subguid).OrderBy(t => t.AchieveDate, OrderByType.Desc).ToPageList(1, 11);
List <personalscroce> result = new List <personalscroce>();
//获取今年第一次考试分数
DateTime startDate = new DateTime(dateTimes.Min().Value.Year, 1, 1);
DateTime endDate = new DateTime(dateTimes.Max().Value.Year, 12, 31);
int firstScore = personalscroceManager.CurrentDb.AsQueryable().Where(t => t.SubjectGuid == subguid && t.PGuid == pguid && t.AchieveDate >= startDate && t.AchieveDate <= endDate).OrderBy(t => t.AchieveDate, OrderByType.Asc).Select(t => t.Score).First().Value;
//获取今年第一次考试旅排名
string whersql = $"and t1.Brigade='{pInfo.Brigade}' and t.SubjectGuid='{subguid}' and t.AchieveDate >= '{dateTimes.Min().Value.Year}-1-1' and t.AchieveDate <= '{dateTimes.Max().Value.Year}-12-31'";
string sumsql = $"select PGuid,MAX(Score) as Score from (select g.PGuid as PGuid,g.AchieveDate as AchieveDate,Sum(g.Score) as Score from (select t.PGuid,t.Score,t.AchieveDate from personalscroce t JOIN personalfiles t1 ON t.PGuid=t1.Guid {whersql}) g GROUP BY g.PGuid,g.AchieveDate) s GROUP BY PGuid ORDER BY Score desc";
int firstBrigadeRank = personalscroceManager.Db.Ado.GetInt($"SELECT Rank from (SELECT PGuid,Score,(SELECT COUNT(DISTINCT Score) from ({sumsql}) m where m.Score>=l.Score) Rank from ({sumsql}) l) k WHERE PGuid='{pguid}'");
foreach (var date in dateTimes)
{
var temp = scoreInfo.Where(t => t.AchieveDate.Value == date.Value).FirstOrDefault();
if (temp == null)
{
temp = new personalscroce()
{
AchieveDate = date,
Score = 0
};
}
else
{
//获取旅排名
whersql = $"and t1.Brigade='{pInfo.Brigade}' and t.SubjectGuid='{subguid}' and t.AchieveDate = '{date.Value.ToString("yyyy-MM-dd")}'";
sumsql = $"select PGuid,MAX(Score) as Score from (select g.PGuid as PGuid,g.AchieveDate as AchieveDate,Sum(g.Score) as Score from (select t.PGuid,t.Score,t.AchieveDate from personalscroce t JOIN personalfiles t1 ON t.PGuid=t1.Guid {whersql}) g GROUP BY g.PGuid,g.AchieveDate) s GROUP BY PGuid ORDER BY Score desc";
//旅排名
int BrigadeRank = personalscroceManager.Db.Ado.GetInt($"SELECT Rank from (SELECT PGuid,Score,(SELECT COUNT(DISTINCT Score) from ({sumsql}) m where m.Score>=l.Score) Rank from ({sumsql}) l) k WHERE PGuid='{pguid}'");
temp.Rank = BrigadeRank;
}
temp.Evaluate = GetEvaluate(temp.Score.Value); //评定
temp.YearProScore = temp.Score.Value - firstScore; //年度进步分数
temp.YearProRank = temp.Rank - firstBrigadeRank; //年度进步名次
result.Add(temp);
}
//计算单次进步名次
for (int i = 0; i < 10; i++)
{
result[i].CurrentProScore = (result[i].Score - result[i + 1].Score).Value;
result[i].CurrentProRank = result[i].Rank - result[i + 1].Rank;
}
return(result);
}
public bool Insert(personalscroce dto)
{
dto.Create();
return(personalscroceManager.Insert(dto));
}
