public Boolean EditMark(string Faculty, string CourseCode, Marks mark)
{
SqlConnection con = new SqlConnectionGenerator().FromFaculty(Faculty);
int res = -1;
SqlCommand cmd = new SqlCommand("UPDATE " + CourseCode + " SET Mid=@mid, Attendence=@attendence, Assignment=@assignment, Final=@final WHERE StudentId=@id;", con);
cmd.Parameters.AddWithValue("@mid", mark.Mid);
cmd.Parameters.AddWithValue("@attendence", mark.Attendence);
cmd.Parameters.AddWithValue("@assignment", mark.Assignment);
cmd.Parameters.AddWithValue("@final", mark.Final);
cmd.Parameters.AddWithValue("@id", mark.StudentId);
try
{
con.Open();
res = cmd.ExecuteNonQuery();
if (res <= 0)
{
return(false);
}
}
catch (SqlException ex)
{
return(false);
}
finally
{
con.Close();
}
return(true);
}
public Marks GetMark(string Faculty, string CoursCode, int StudentId)
{
Marks mark = new Marks();
SqlConnection con = new SqlConnectionGenerator().FromFaculty(Faculty);
con.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM " + CoursCode + " WHERE StudentId=" + StudentId, con);
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
mark.StudentId = Convert.ToInt32(reader.GetValue(0));
mark.RegNo = Convert.ToInt32(reader.GetValue(1));
mark.Mid = Convert.ToSingle(reader.GetValue(2));
mark.Attendence = Convert.ToSingle(reader.GetValue(3));
mark.Assignment = Convert.ToSingle(reader.GetValue(4));
mark.Final = Convert.ToSingle(reader.GetValue(5));
mark.Submitted = Convert.ToBoolean(reader.GetValue(6));
reader.Close();
}
else
{
return(null);
}
}
con.Close();
return(mark);
}
public static string CheckRemarks(string Faculty, int StudentId, string Remarks)
{
if (Faculty != null || StudentId <= 0 || Remarks == null)
{
return("");
}
string CheckedRemarks = "";
string[] Courses = Remarks.Split(',');
foreach (string CourseCode in Courses)
{
Marks mark = new Marks().GetMark(Faculty, CourseCode, StudentId);
if (mark != null)
{
float TotalMark = 0;
if (mark.Mid != -1)
{
TotalMark += mark.Mid;
}
if (mark.Assignment != -1)
{
TotalMark += mark.Assignment;
}
if (mark.Attendence != -1)
{
TotalMark += mark.Attendence;
}
if (mark.Final != -1)
{
TotalMark += mark.Final;
}
if (!Marks.IsPassed(TotalMark))
{
CheckedRemarks += CourseCode + ",";
}
}
}
if (CheckedRemarks != "")
{
CheckedRemarks = CheckedRemarks.Substring(0, CheckedRemarks.Length - 1);
}
return(CheckedRemarks);
}
public string InserMark(string Faculty, string CourseCode, Marks mark)
{
try
{
SqlConnection con = new SqlConnectionGenerator().FromFaculty(Faculty);
con.Open();
int res = -1;
SqlCommand cmd = new SqlCommand("SELECT * FROM " + CourseCode + " WHERE StudentId=" + mark.StudentId, con);
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
cmd = new SqlCommand("UPDATE " + CourseCode + " SET Final=" + mark.Final + " WHERE StudentId=@studentId AND Submitted=@submitted;", con);
cmd.Parameters.AddWithValue("@studentId", mark.StudentId);
cmd.Parameters.AddWithValue("@submitted", false);
}
else
{
cmd = new SqlCommand("INSERT INTO " + CourseCode + " VALUES(@StudentId, @RegNo, @Mid, @Attendence, @Assignment, @Final, @Submitted)", con);
cmd.Parameters.AddWithValue("@StudentId", mark.StudentId);
cmd.Parameters.AddWithValue("@RegNo", mark.RegNo);
cmd.Parameters.AddWithValue("@Mid", mark.Mid);
cmd.Parameters.AddWithValue("@Attendence", mark.Attendence);
cmd.Parameters.AddWithValue("@Assignment", mark.Assignment);
cmd.Parameters.AddWithValue("@Final", mark.Final);
cmd.Parameters.AddWithValue("@Submitted", false);
}
}
res = cmd.ExecuteNonQuery();
con.Close();
if (res <= 0)
{
return(Messasges.InsertionFailed);
}
}
catch (SqlException ex)
{
return(Messasges.InsertionFailed + " " + ex.ToString());
}
return(Messasges.DataInsertedSuccessfully);
}
public List <Marks> GetMarkList(string faculty, string course_code, string session)
{
int batchId = Convert.ToInt32(session.Substring(2, 2)); //startIndex, Length
int startRange = batchId * 100000;
int endRange = ((batchId + 1) * 100000) - 1;
SqlConnection con = new SqlConnectionGenerator().FromFaculty(faculty);
con.Open();
List <Marks> marks = new List <Marks>();
SqlCommand cmd = new SqlCommand("SELECT * FROM " + course_code + " WHERE StudentId BETWEEN " + startRange + " AND " + endRange + ";", con);
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
Marks mark = new Marks();
mark.StudentId = Convert.ToInt32(reader.GetValue(0));
mark.RegNo = Convert.ToInt32(reader.GetValue(1));
mark.Mid = Convert.ToSingle(reader.GetValue(2));
mark.Attendence = Convert.ToSingle(reader.GetValue(3));
mark.Assignment = Convert.ToSingle(reader.GetValue(4));
mark.Final = Convert.ToSingle(reader.GetValue(5));
mark.Submitted = Convert.ToBoolean(reader.GetValue(6));
marks.Add(mark);
}
reader.Close();
}
else
{
return(null);
}
}
con.Close();
return(marks);
}
public List <Marks> readMarksFromExcel(HttpPostedFileBase file)
{
List <Marks> marks = new List <Marks>();
//byte[] fileBytes = new byte[file.ContentLength];
//var data = file.InputStream.Read(fileBytes, 0, Convert.ToInt32(file.ContentLength));
using (var package = new ExcelPackage(file.InputStream))
{
var currentSheet = package.Workbook.Worksheets;
var workSheet = currentSheet.First();
var noOfCol = workSheet.Dimension.End.Column;
var noOfRow = workSheet.Dimension.End.Row;
try
{
for (int rowIterator = 2; rowIterator <= noOfRow; rowIterator++)
{
var mark = new Marks();
mark.StudentId = Convert.ToInt32(workSheet.Cells[rowIterator, 1].Value);
mark.RegNo = Convert.ToInt32(workSheet.Cells[rowIterator, 2].Value);
try
{
mark.Mid = Convert.ToSingle(workSheet.Cells[rowIterator, 3].Value);
}
catch (Exception ex)
{
mark.Mid = -1;
}
try
{
mark.Attendence = Convert.ToSingle(workSheet.Cells[rowIterator, 4].Value);
}
catch (Exception ex)
{
mark.Attendence = -1;
}
try
{
mark.Assignment = Convert.ToSingle(workSheet.Cells[rowIterator, 5].Value);
}
catch (Exception ex)
{
mark.Assignment = -1;
}
try
{
mark.Final = Convert.ToSingle(workSheet.Cells[rowIterator, 6].Value);
}
catch (Exception ex)
{
mark.Final = -1;
}
marks.Add(mark);
}
}
catch (Exception ex)
{
return(null);
}
}
return(marks);
}
public static Boolean CalculateResult(List <StudentInfo> Students, int Semester, List <Course> Courses)
{
if (Students == null || Semester == 0 || Courses == null)
{
return(false);
}
ProjectDB db = new ProjectDB();
JavaScriptSerializer js = new JavaScriptSerializer();
foreach (StudentInfo Student in Students)
{
Result Result = new Result();
Result.Name = Student.Name;
Result.StudentId = Student.StudentId;
Result.RegNo = Student.Reg;
Result.Faculty = Student.Faculty;
Result.Session = Student.Session;
Result.Semester = Semester;
Result.Degree = "";
float TotalCHGP = 0, TotalCH = 0;
string Remarks = "";
List <CourseResult> CourseResults = new List <CourseResult>();
foreach (Course Course in Courses)
{
Marks mark = new Marks().GetMark(Course.UnderFaculty, Course.Course_code, Student.StudentId);
if (mark != null)
{
float TotalMark = 0;
if (mark.Mid != -1)
{
TotalMark += mark.Mid;
}
if (mark.Assignment != -1)
{
TotalMark += mark.Assignment;
}
if (mark.Attendence != -1)
{
TotalMark += mark.Attendence;
}
if (mark.Final != -1)
{
TotalMark += mark.Final;
}
CourseResult CourseResult = new CourseResult();
CourseResult.CourseCode = Course.Course_code;
CourseResult.CourseTitle = Course.Course_title;
CourseResult.CreditHours = Course.Credit_hour;
CourseResult.TotalMark = TotalMark;
CourseResult.LetterGrade = Marks.CalculateLetterGrade(TotalMark);
float GP = Marks.WrapToFloatPoint3(Marks.CalculateGP(TotalMark));
CourseResult.GP = GP;
Boolean IsPassed = Marks.IsPassed(TotalMark);
CourseResult.IsPassed = IsPassed;
CourseResults.Add(CourseResult);
if (IsPassed)
{
TotalCHGP += (Course.Credit_hour * GP);
TotalCH += Course.Credit_hour;
}
else
{
Remarks += Course.Course_code + ",";
}
}
}
float GPA = 0;
if (CourseResults != null)
{
Result.CourseResults = js.Serialize(CourseResults);
}
if (TotalCHGP != 0 && TotalCH != 0)
{
GPA = Marks.WrapToFloatPoint3(TotalCHGP / TotalCH);
}
Result.GPA = GPA;
if (Remarks != "")
{
Remarks = Remarks.Substring(0, Remarks.Length - 1);
}
if (Semester == 1)
{
Result.PrevCGPA = 0;
Result.PrevCCH = 0;
Result.CGPA = GPA;
Result.CCH = TotalCH;
Result.Remarks = Remarks;
if (Remarks == "")
{
Result.IsMeritListed = true;
}
else
{
Result.IsMeritListed = false;
}
}
else
{
int PrevSem = Semester - 1;
Result PrevRes = db.FinalResults.Where(fr => fr.StudentId == Result.StudentId && fr.Semester == PrevSem).FirstOrDefault();
if (PrevRes != null)
{
Result.PrevCGPA = PrevRes.CGPA;
Result.PrevCCH = PrevRes.CCH;
Result.CGPA = Marks.WrapToFloatPoint3(Marks.CalculateCGPA(GPA, TotalCH, PrevRes.CGPA, PrevRes.CCH));
Result.CCH = TotalCH + PrevRes.CCH;
if (PrevRes.Remarks == "")
{
Result.Remarks = Remarks;
}
else
{
string PrevRemarks = Course.CheckRemarks(Student.Faculty, Student.StudentId, PrevRes.Remarks);
if (Remarks != "")
{
if (PrevRemarks != "" || PrevRemarks != null)
{
Result.Remarks = PrevRes.Remarks + "," + Remarks;
}
else
{
Result.Remarks = Remarks;
}
}
else
{
Result.Remarks = PrevRes.Remarks;
}
}
if (!PrevRes.IsMeritListed)
{
Result.IsMeritListed = false;
}
else
{
if (Remarks == "")
{
Result.IsMeritListed = true;
}
else
{
Result.IsMeritListed = false;
}
}
}
}
Result OldRes = db.FinalResults.Where(fr => fr.StudentId == Result.StudentId && fr.Semester == Semester).FirstOrDefault();
if (OldRes != null)
{
db.FinalResults.Remove(OldRes);
}
db.FinalResults.Add(Result);
db.SaveChanges();
}
return(true);
}
