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