private void SetClassAssessmentScoreData(out decimal?score, out decimal?projection, out int assessmentId, out int assessmentTypeId, out string assessmentDesc, out string assessmentCode, out int assessmentScoreId, out int?scoreMin, out int?scoreMax, out int schoolTermId, out int?gradeLevel, SqlDataReader reader)
{
score = (reader["Score"] == DBNull.Value) ? (decimal?)null : HelperService.GetRoundedValue(RoundingType.Default, Convert.ToDecimal(reader["Score"]));
projection = (reader["Projection"] == DBNull.Value) ? (decimal?)null : HelperService.GetRoundedValue(RoundingType.Default, Convert.ToDecimal(reader["Projection"]));
assessmentId = (reader["AssessmentId"] == DBNull.Value) ? 0 : Convert.ToInt32(reader["AssessmentId"]);
assessmentTypeId = Convert.ToInt32(reader["AssessmentTypeId"]);
assessmentDesc = reader["AssessmentDesc"] == DBNull.Value ? null : reader["AssessmentDesc"].ToString();
assessmentCode = reader["AssessmentCode"] == DBNull.Value ? null : reader["AssessmentCode"].ToString();
assessmentScoreId = (reader["AssessmentScoreId"] == DBNull.Value) ? 0 : Convert.ToInt32(reader["AssessmentScoreId"]);
scoreMin = (reader["ScoreMin"] == DBNull.Value) ? (int?)null : Convert.ToInt32(reader["ScoreMin"]);
scoreMax = (reader["ScoreMax"] == DBNull.Value) ? (int?)null : Convert.ToInt32(reader["ScoreMax"]);
schoolTermId = Convert.ToInt32(reader["SchoolTermId"]);
gradeLevel = (reader["GradeLevel"] == DBNull.Value) ? (int?)null : Convert.ToInt32(reader["GradeLevel"]);
}
public List <Student> GetViewData(string schoolYearFilter, string schoolFilter, string searchFilter)
{
int siteSchoolYear = Convert.ToInt32(schoolYearFilter);
int schoolYearId = _db.tblSchoolYears.Where(s => s.SchoolYear == siteSchoolYear).Select(s => s.SchoolYearId).FirstOrDefault();
IQueryable <Student> query;
if (_siteUser.isTeacher)
{
query = (
from classTeacher in _db.tblClassTeachers
join classStudent in _db.tblClassStudents on classTeacher.ClassId equals classStudent.ClassId
join student in _db.tblStudents on classStudent.StudentId equals student.StudentId
join studentSchoolYear in _db.tblStudentSchoolYears on student.StudentId equals studentSchoolYear.StudentId
join schoolClass in _db.tblClasses on classStudent.ClassId equals schoolClass.ClassId
join school in _db.tblSchools on schoolClass.SchoolId equals school.SchoolId
join schoolYear in _db.tblSchoolYears on student.SchoolYearId equals schoolYear.SchoolYearId
where classTeacher.UserId == _siteUser.EdsUserId &&
schoolYear.SchoolYear == siteSchoolYear &&
studentSchoolYear.SchoolYearId == schoolYearId &&
studentSchoolYear.ServingSchoolId == school.SchoolId
select new Student()
{
StudentId = student.StudentId,
FirstName = student.FirstName,
LastName = student.LastName,
StateId = studentSchoolYear.StateId,
LocalId = studentSchoolYear.LocalId,
GradeLevel = studentSchoolYear.GradeLevel,
HomeSchool = school.SchoolDesc,
TeacherId = classTeacher.UserId,
SchoolId = school.SchoolId
});
}
else
{
int userAssignedDistrict = _siteUser.Districts[0].Id;
query = (
from userSchool in _db.tblUserSchools
join school in _db.tblSchools on userSchool.SchoolId equals school.SchoolId
join studentSchoolYear in _db.tblStudentSchoolYears on school.SchoolId equals studentSchoolYear.ServingSchoolId
join student in _db.tblStudents on studentSchoolYear.StudentId equals student.StudentId
where userSchool.UserId == _siteUser.EdsUserId &&
userSchool.SchoolYearId == schoolYearId &&
studentSchoolYear.SchoolYearId == schoolYearId
select new Student()
{
StudentId = student.StudentId,
FirstName = student.FirstName,
LastName = student.LastName,
StateId = studentSchoolYear.StateId,
LocalId = studentSchoolYear.LocalId,
GradeLevel = studentSchoolYear.GradeLevel,
HomeSchool = school.SchoolDesc,
SchoolId = school.SchoolId
});
}
// Filter by UI
if (!String.IsNullOrEmpty(schoolFilter) && schoolFilter != "-1")
{
var schoolsForUser = HelperService.GetUserSchoolsFilter(_siteUser.EdsUserId, _siteUser.Districts[0].Id, Convert.ToInt32(schoolFilter), _db);
query = query.Where(x => schoolsForUser.Contains(x.SchoolId));
}
if (!String.IsNullOrEmpty(searchFilter))
{
query = query.Where(x => x.FirstName.ToUpper().Contains(searchFilter.ToUpper()) || x.LastName.ToUpper().Contains(searchFilter.ToUpper()));
}
return(query.AsEnumerable()
.Distinct(new EdsUserIdStudentComparer())
.ToList());
}
public List <AssessmentClassScoreDetail> GetExistingClassAssessmentScoreData(int districtId, AssessmentClassScoreViewModel model)
{
List <AssessmentClassScoreDetail> results = new List <AssessmentClassScoreDetail>();
SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString);
connection.Open();
SqlCommand command = new SqlCommand("spGetClassAssessmentScore", connection);
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@AssessmentTypeId", int.Parse(model.AssessmentTypeId)));
command.Parameters.Add(new SqlParameter("@SubjectId", int.Parse(model.SubjectID)));
command.Parameters.Add(new SqlParameter("@SchoolYearId", int.Parse(model.SchoolYearId)));
command.Parameters.Add(new SqlParameter("@ClassID", int.Parse(model.ClassId)));
command.Parameters.Add(new SqlParameter("@SchoolTermId", int.Parse(model.SchoolTermId)));
SqlDataReader reader = command.ExecuteReader();
AssessmentClassScoreDetail tempScoreDetail = null;
int prevStudent = -1;
int assessmentIndex = 0; //its range is [0:4]
while (reader.Read())
{
var currentStudentId = Convert.ToInt32(reader["StudentId"]);
if (prevStudent != currentStudentId)
{
prevStudent = currentStudentId;
if (tempScoreDetail != null)
{
//creating new row
tempScoreDetail.AssessmentCount = assessmentIndex;
results.Add(tempScoreDetail);
assessmentIndex = 0;
}
tempScoreDetail = new AssessmentClassScoreDetail();
tempScoreDetail.StudentId = currentStudentId;
tempScoreDetail.StudentName = reader["StudentName"].ToString();
tempScoreDetail.LastName = reader["LastName"].ToString();
tempScoreDetail.ReportTemplateId = int.Parse(reader["ReportTemplateId"].ToString());
}
decimal?score = null;
decimal?projection = null;
int assessmentId;
int assessmentTypeId;
int assessmentScoreId;
string assessmentDesc = null;
string assessmentCode = null;
int? scoreMin;
int? scoreMax;
int schoolTermId;
int? gradeLevel;
SetClassAssessmentScoreData(out score, out projection, out assessmentId, out assessmentTypeId, out assessmentDesc, out assessmentCode, out assessmentScoreId, out scoreMin, out scoreMax, out schoolTermId, out gradeLevel, reader);
try
{
//for N assessment column, the record will contain N ScoreData objects in the ScoreDatacollection
var propertyIndex = assessmentIndex + 1;
var scoreData = new ClassScoreData();
tempScoreDetail.ScoreDataCollection.Add(scoreData);
scoreData.RoundingType = HelperService.GetDecimalDisplayFormat(RoundingType.NoDecimalPlace);
scoreData.Score = score;
scoreData.Projection = projection;
scoreData.AssessmentDesc = assessmentDesc;
scoreData.AssessmentId = assessmentId;
scoreData.AssessmentTypeId = assessmentTypeId;
scoreData.AssessmentCode = assessmentCode;
scoreData.AssessmentScoreId = assessmentScoreId;
scoreData.ScoreMin = scoreMin;
scoreData.ScoreMax = scoreMax;
scoreData.SchoolTermId = schoolTermId;
scoreData.StudentId = currentStudentId;
scoreData.GradeLevel = gradeLevel;
}
catch (Exception ex)
{
throw new Exception("Check if field exists belongs to correct assessment", ex);
}
assessmentIndex++;
}
if (tempScoreDetail != null)
{
tempScoreDetail.AssessmentCount = assessmentIndex;
results.Add(tempScoreDetail);
}
reader.Close();
connection.Close();
return(results);
}