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