private string getStudentSSN(DataTable currentSheet, DataTableReader reader, StudentCacheDTO cacheItem, List <string> recordErrors)
{
string Student_SSN;
if (reader.IsDBNull(reader.GetOrdinal(FileHeaderDefinitions.StudentSSN)))
{
// generate SSN
var random = new Random();
Student_SSN = $"{random.Next(1, 999)}-{random.Next(1, 99)}-{random.Next(1, 9999)}";
//chceck if in cache
while (currentSheet.Select($"[{FileHeaderDefinitions.StudentSSN}] = '{Student_SSN}'").Any())
{
Student_SSN = $"{random.Next(1, 999)}-{random.Next(1, 99)}-{random.Next(1, 9999)}";
}
// check that same Name,DOB and grade do not exist in the same school
//Grade condition
var gradeFilter = cacheItem.Grade != null ? "Grade = " + cacheItem.Grade : "Grade Is Null";
var duplicateStudentQuery = $"FirstName = '{cacheItem.FirstName}' AND LastName = '{cacheItem.LastName}' AND DOB = '{cacheItem.DOB}' AND {gradeFilter} AND SchoolCode = '{ cacheItem.SchoolCode }'";
var hasMultipleEntires = currentSheet.Select(duplicateStudentQuery).Count() > 1;
if (hasMultipleEntires)
{
recordErrors.Add($"Another student has the same record details in this school");
}
}
else
{
Student_SSN = reader.GetString(reader.GetOrdinal(FileHeaderDefinitions.StudentSSN));
}
return(Student_SSN);
}
private StudentCacheDTO GetStudentCacheDTO(DataTableReader reader)
{
var cacheItem = new StudentCacheDTO();
cacheItem.FirstName = reader.IsDBNull(reader.GetOrdinal(FileHeaderDefinitions.FirstName)) ? null : reader.GetString(reader.GetOrdinal(FileHeaderDefinitions.FirstName));
cacheItem.LastName = reader.IsDBNull(reader.GetOrdinal(FileHeaderDefinitions.LastName)) ? null : reader.GetString(reader.GetOrdinal(FileHeaderDefinitions.LastName));
var dateTime = reader.GetDateTime(reader.GetOrdinal(FileHeaderDefinitions.DOB));
cacheItem.DOB = reader.IsDBNull(reader.GetOrdinal(FileHeaderDefinitions.DOB)) ? (DateTime?)null : dateTime;
cacheItem.SchoolCode = reader.IsDBNull(reader.GetOrdinal(FileHeaderDefinitions.SchoolCode)) ? null : reader.GetString(reader.GetOrdinal(FileHeaderDefinitions.SchoolCode));
cacheItem.SchoolName = reader.IsDBNull(reader.GetOrdinal(FileHeaderDefinitions.SchoolName)) ? null : reader.GetString(reader.GetOrdinal(FileHeaderDefinitions.SchoolName));
cacheItem.Grade = reader.IsDBNull(reader.GetOrdinal(FileHeaderDefinitions.Grade)) ? (double?)null : reader.GetDouble(reader.GetOrdinal(FileHeaderDefinitions.Grade));
cacheItem.StudentID = reader.IsDBNull(reader.GetOrdinal(FileHeaderDefinitions.StudentID)) ? null : Convert.ToString(reader.GetValue(reader.GetOrdinal(FileHeaderDefinitions.StudentID)));
return(cacheItem);
}
