public List <PersonScoreEntity> GetPersonScore(string yearMonth, String specialField) { if (string.IsNullOrEmpty(yearMonth)) { return(null); } List <PersonScoreEntity> Result = new List <PersonScoreEntity>(); int Year = Convert.ToInt32(yearMonth.Substring(0, 4)); int Month = Convert.ToInt32(yearMonth.Substring(4, 2)); DateTime StartDate = new DateTime(Year, Month, 1); DateTime EndDate = StartDate.AddMonths(1); string SqlText = @"WITH PersonScoreCTE AS(SELECT PersonID,SUM(ISNULL(Score,0)) Score FROM KPI_PersonScore WHERE CheckDate BETWEEN @StartDate AND @EndDate GROUP BY PersonID) SELECT A.PersonID,PersonCode,PersonName,A.Shift,PositionName,B.PositionID,PositionWeight,C.Score,D.Bonus FROM KPI_Person A JOIN KPI_Position B ON A.PositionID=B.PositionID AND A.PersonIsValid='1' LEFT JOIN PersonScoreCTE C ON A.PersonID=C.PersonID JOIN KPI_PersonBonus D ON A.PersonID=D.PersonID WHERE A.SpecialField =@SpecialField AND D.CheckYearMonth=@YearMonth ORDER BY A.Shift,C.Score desc"; IDbDataParameter[] parames = new SqlParameter[] { new SqlParameter("@StartDate", DbType.DateTime), new SqlParameter("@EndDate", DbType.DateTime), new SqlParameter("@SpecialField", DbType.String), new SqlParameter("@YearMonth", DbType.String) }; parames[0].Value = StartDate; parames[1].Value = EndDate; parames[2].Value = specialField; parames[3].Value = yearMonth; using (IDataReader DataReader = DataBase.ExecuteReader(CommandType.Text, SqlText, parames)) { Result = DataReader.FillGenericList <PersonScoreEntity>(); DataReader.Close(); } PersonScoreEntity Sum = new PersonScoreEntity(); Sum.Shift = ""; Sum.PersonName = "合计"; Sum.Score = Result.Sum(p => p.Score); Sum.Bonus = Result.Sum(p => p.Bonus); Result.Add(Sum); return(Result); }
public IEnumerable <PersonScoreEntity> GetScoresFromFile(string fileName) { if (!File.Exists(fileName)) { throw new Exception($"Input file name doesn't exist. Please make sure you have written the whole route including location and extension: {fileName}"); } var peopleScores = File.ReadAllLines(fileName); foreach (var personScore in peopleScores) { var entityProperties = personScore.Split(',', StringSplitOptions.RemoveEmptyEntries); decimal.TryParse(entityProperties[2], out decimal score); var entity = new PersonScoreEntity { FirstName = entityProperties[0], LastName = entityProperties[1], Score = score }; yield return(entity); } }