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