public string EditAssignment(Assignment assignment)
{
SystemContext context = HttpContext.RequestServices.GetService(typeof(Exam_Management_System.Models.SystemContext)) as SystemContext;
int academic_id = context.GetAcademic().Id;
using (MySqlConnection conn = context.GetConnection())
{
conn.Open();
string sql = $"Update assignment set mark='{assignment.Mark}' where id={assignment.Id}";
using (MySqlCommand command = new MySqlCommand(sql, conn))
{
command.ExecuteNonQuery();
// conn.Close();
}
int total_mark = context.GetTotalMark(assignment.Rollno_id, context.GetAcademic().Id);
int pass = context.GetEditPass(assignment.Rollno_id, academic_id);
string sql1 = $"Update result set total_mark='{total_mark}',pass={pass} where studentrollno_id={assignment.Rollno_id}";
using (MySqlCommand command = new MySqlCommand(sql1, conn))
{
command.ExecuteNonQuery();
conn.Close();
}
}
return("This assignment is edit!");
}
public void PostAddAssignment(Assignment assignment)
{
int c = 0;
string sql = null;
int? mark = 0;
SystemContext context = HttpContext.RequestServices.GetService(typeof(Exam_Management_System.Models.SystemContext)) as SystemContext;
int academic_id = context.GetAcademic().Id;
int student_id = context.GetStudentId(assignment.Rollno, academic_id);
using (MySqlConnection conn = context.GetConnection())
{
conn.Open();
string check = $"Select count(*),mark from assignment where studentrollno_id={student_id} and subject_id={assignment.Subject_id}";
using (MySqlCommand command = new MySqlCommand(check, conn))
{
c = Convert.ToInt32(command.ExecuteScalar());
}
if (c == 0)
{
sql = $"Insert Into assignment (studentrollno_id,subject_id,mark,academic_id) Values ('{student_id}','{assignment.Subject_id}','{assignment.Mark}','{academic_id}')";
}
else
{
string aa = $"Select mark from assignment where studentrollno_id={student_id} and subject_id={assignment.Subject_id}";
using (MySqlCommand command = new MySqlCommand(aa, conn))
{
var reader = command.ExecuteReader();
while (reader.Read())
{
mark = Convert.ToInt32(reader["mark"]);
}
}
sql = $"Update assignment set mark={mark + assignment.Mark} where studentrollno_id={student_id} and subject_id={assignment.Subject_id}";
}
using (MySqlCommand command = new MySqlCommand(sql, conn))
{
command.ExecuteNonQuery();
//conn.Close();
}
int total_mark = context.GetTotalMark(assignment.Rollno_id, context.GetAcademic().Id);
string sql1 = $"Update result set total_mark='{total_mark}' where studentrollno_id={assignment.Rollno_id}";
using (MySqlCommand command = new MySqlCommand(sql1, conn))
{
command.ExecuteNonQuery();
conn.Close();
}
}
}
public JsonResult GetAssignment(int id, int major, int classes, int subject)
{
SystemContext context = HttpContext.RequestServices.GetService(typeof(Exam_Management_System.Models.SystemContext)) as SystemContext;
int academic_id = context.GetAcademic().Id;
List <Assignment> list = new List <Assignment>();
using (MySqlConnection conn = context.GetConnection())
{
conn.Open();
MySqlCommand cmd = new MySqlCommand("select * from assignment,studentrollno,student,subject,student_detail,year,major,class where assignment.studentrollno_id=studentrollno.id and studentrollno.student_id=student.id and assignment.subject_id=subject.id and student_detail.studentrollno_id=studentrollno.id and student_detail.year_id=year.id and student_detail.major_id=major.id and student_detail.class_id=class.id and student_detail.year_id=" + id + " and student_detail.major_id=" + major + " and assignment.academic_id=" + academic_id + " and student_detail.class_id=" + classes + " and assignment.subject_id=" + subject, conn);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
list.Add(new Assignment()
{
Id = Convert.ToInt32(reader["id"]),
Name = reader["student_name"].ToString(),
Rollno = reader["rollno"].ToString(),
Subject = reader["subject_name"].ToString(),
Mark = Convert.ToInt32(reader["mark"]),
Year = reader["year_name"].ToString(),
Major = reader["major_name"].ToString(),
Class = reader["class_name"].ToString(),
});
}
}
}
return(Json(list));
}
public IActionResult AddOldStudent()
{
SystemContext context = HttpContext.RequestServices.GetService(typeof(Exam_Management_System.Models.SystemContext)) as SystemContext;
ViewBag.academic = context.GetAcademic().Name;
return(View());
}
public JsonResult ResultMarkFinal(int id, int major, int class_id)
{
List <Mark> list = new List <Mark>();
SystemContext context = HttpContext.RequestServices.GetService(typeof(Exam_Management_System.Models.SystemContext)) as SystemContext;
int academic_id = context.GetAcademic().Id;
using (MySqlConnection conn1 = context.GetConnection())
{
conn1.Open();
MySqlCommand cmd1 = new MySqlCommand("select * from mark_final,result,subject,studentrollno,student_detail,student,year,major,class where student_detail.class_id=class.id and mark_final.subject_id=subject.id and studentrollno.id=result.studentrollno_id and mark_final.studentrollno_id=studentrollno.id and student_detail.studentrollno_id=studentrollno.id and studentrollno.student_id=student.id and student_detail.year_id=year.id and student_detail.major_id=major.id and student_detail.major_id=" + major + " and student_detail.year_id=" + id + " and mark_final.academic_id=" + academic_id + " and student_detail.class_id=" + class_id + " order by mark_final.studentrollno_id,mark_final.subject_id asc", conn1);
using (var reader = cmd1.ExecuteReader())
{
while (reader.Read())
{
list.Add(new Mark()
{
Id = Convert.ToInt32(reader["id"]),
Rollno = reader["rollno"].ToString(),
Name = reader["student_name"].ToString(),
S_mark = Convert.ToInt32(reader["mark"]),
Year = reader["year_name"].ToString(),
Major = reader["major_name"].ToString(),
Grade = context.Grade(Convert.ToInt32(reader["mark"])),
Rollno_id = Convert.ToInt32(reader["studentrollno_id"]),
Subject = reader["subject_name"].ToString(),
Pass = Convert.ToInt32(reader["pass"]),
Class = reader["class_name"].ToString(),
});
}
}
conn1.Close();
}
return(Json(list));
}
public string EditFinal(Mark mark)
{
SystemContext context = HttpContext.RequestServices.GetService(typeof(Exam_Management_System.Models.SystemContext)) as SystemContext;
int academic_id = context.GetAcademic().Id;
using (MySqlConnection conn = context.GetConnection())
{
conn.Open();
string sql = $"Update mark_final set mark='{mark.S_mark}' where studentrollno_id={mark.Id} and subject_id={mark.Subject_id}";
using (MySqlCommand command = new MySqlCommand(sql, conn))
{
command.ExecuteNonQuery();
// conn.Close();
}
int tmk = context.GetFinal_Mark(mark.Id, academic_id);
int total_mk = context.GetTotalMark(mark.Id, academic_id);
int pass = context.GetEditPass(mark.Id, academic_id);
string sql1 = $"Update result set final_mark='{tmk}',total_mark={total_mk},pass={pass} where studentrollno_id={mark.Id} and academic_id={academic_id}";
using (MySqlCommand command = new MySqlCommand(sql1, conn))
{
command.ExecuteNonQuery();
conn.Close();
}
}
return("OK");
}
public List <Exam> GetExamDetail(int title, int id, int major)
{
SystemContext context = HttpContext.RequestServices.GetService(typeof(Exam_Management_System.Models.SystemContext)) as SystemContext;
int academic_id = context.GetAcademic().Id;
List <Exam> list = new List <Exam>();
using (MySqlConnection conn = context.GetConnection())
{
conn.Open();
MySqlCommand cmd = new MySqlCommand("select * from exam,examtitle,subject,year where exam.examtitle_id=examtitle.id and exam.subject_id=subject.id and exam.year_id=year.id and exam.year_id=" + id + " and exam.academic_id=" + academic_id + " and exam.examtitle_id=" + title, conn);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
ViewBag.year = reader["year_name"].ToString();
list.Add(new Exam()
{
Id = Convert.ToInt32(reader["id"]),
Examtile = reader["examtitle_name"].ToString(),
Subject = reader["subject_name"].ToString(),
Date = reader["date"].ToString(),
Start_time = reader["start"].ToString(),
End_time = reader["end"].ToString(),
Year = reader["year_name"].ToString(),
});
}
}
}
return(list);
}
public List <Exam> ExamOne(int id)
{
SystemContext context = HttpContext.RequestServices.GetService(typeof(Exam_Management_System.Models.SystemContext)) as SystemContext;
int academic_id = context.GetAcademic().Id;
List <Exam> list = new List <Exam>();
using (MySqlConnection conn = context.GetConnection())
{
conn.Open();
MySqlCommand cmd = new MySqlCommand("select * from exam,subject where exam.subject_id=subject.id and exam.id=" + id, conn);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
list.Add(new Exam()
{
Id = Convert.ToInt32(reader["id"]),
Subject = reader["subject_name"].ToString(),
Date = reader["date"].ToString(),
Start_time = reader["start"].ToString(),
End_time = reader["end"].ToString(),
});
}
}
}
return(list);
}
public List <Attendence> GetAttendenceOne(int id, string month)
{
SystemContext context = HttpContext.RequestServices.GetService(typeof(Exam_Management_System.Models.SystemContext)) as SystemContext;
int academic_id = context.GetAcademic().Id;
List <Attendence> list = new List <Attendence>();
using (MySqlConnection conn = context.GetConnection())
{
conn.Open();
MySqlCommand cmd = new MySqlCommand("SELECT * FROM attendance where studentrollno_id=" + id + " and month='" + month + "' and academic_id=" + academic_id, conn);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
list.Add(new Attendence()
{
Id = Convert.ToInt32(reader["id"]),
Month = reader["month"].ToString(),
Total = Convert.ToInt32(reader["total"]),
Current = Convert.ToInt32(reader["current"]),
});
}
}
}
return(list);
}
public List <Class> GetClass(int id, int major)
{
SystemContext context = HttpContext.RequestServices.GetService(typeof(Exam_Management_System.Models.SystemContext)) as SystemContext;
int academic_id = context.GetAcademic().Id;
List <Class> list = new List <Class>();
using (MySqlConnection conn = context.GetConnection())
{
conn.Open();
MySqlCommand cmd = new MySqlCommand("SELECT * FROM class where year_id=" + id + " and major_id=" + major, conn);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
list.Add(new Class()
{
Id = Convert.ToInt32(reader["id"]),
Name = reader["class_name"].ToString(),
});
}
}
}
return(list);
}
public List <Subject> GetSubject(int id, int major)
{
SystemContext context = HttpContext.RequestServices.GetService(typeof(Exam_Management_System.Models.SystemContext)) as SystemContext;
int academic_id = context.GetAcademic().Id;
List <Subject> list = new List <Subject>();
using (MySqlConnection conn = context.GetConnection())
{
conn.Open();
MySqlCommand cmd = new MySqlCommand("select * from subject,subject_major where subject_major.subject_id=subject.id and subject_major.major_id=" + major + " and year_id=" + id, conn);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
list.Add(new Subject()
{
Id = Convert.ToInt32(reader["id"]),
Name = reader["subject_name"].ToString(),
});
}
}
}
return(list);
}
public List <Rollno> GetRollNo(int id)
{
SystemContext context = HttpContext.RequestServices.GetService(typeof(Exam_Management_System.Models.SystemContext)) as SystemContext;
int academic_id = context.GetAcademic().Id;
List <Rollno> list = new List <Rollno>();
using (MySqlConnection conn = context.GetConnection())
{
conn.Open();
MySqlCommand cmd = new MySqlCommand("SELECT * FROM student,studentrollno,student_detail where student.id=studentrollno.student_id and studentrollno.id=student_detail.studentrollno_id and student_detail.year_id=" + id + " and studentrollno.academic_id=" + academic_id, conn);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
list.Add(new Rollno()
{
Id = Convert.ToInt32(reader["id"]),
Roll = reader["rollno"].ToString(),
Name = reader["student_name"].ToString(),
});
}
}
}
return(list);
}
public JsonResult ResultMarkFinalSubject(int id, int subject)
{
List <Mark> list = new List <Mark>();
SystemContext context = HttpContext.RequestServices.GetService(typeof(Exam_Management_System.Models.SystemContext)) as SystemContext;
int academic_id = context.GetAcademic().Id;
using (MySqlConnection conn1 = context.GetConnection())
{
conn1.Open();
MySqlCommand cmd1 = new MySqlCommand("select * from mark_final where studentrollno_id=" + id + " and subject_id=" + subject, conn1);
using (var reader = cmd1.ExecuteReader())
{
while (reader.Read())
{
list.Add(new Mark()
{
Id = Convert.ToInt32(reader["id"]),
S_mark = Convert.ToInt32(reader["mark"]),
});
}
}
conn1.Close();
}
return(Json(list));
}
public IActionResult GenerateRollno()
{
SystemContext context = HttpContext.RequestServices.GetService(typeof(Exam_Management_System.Models.SystemContext)) as SystemContext;
int academic = context.GetAcademic().Id;
List <Student> list = new List <Student>();
using (MySqlConnection conn = context.GetConnection())
{
conn.Open();
MySqlCommand cmd = new MySqlCommand("select * from student,old_student,year where old_student.student_id=student.id and old_student.student_year_id=year.id and old_student.academic_id=" + academic, conn);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
int Id = Convert.ToInt32(reader["id"]);
String name = reader["student_name"].ToString();
list.Add(new Student()
{
Id = Id,
Name = name,
Year = reader["year_name"].ToString(),
Sex = reader["gender"].ToString(),
Father_name = reader["father_name"].ToString(),
Mother_name = reader["mother_name"].ToString(),
Phone = reader["phone"].ToString(),
Address = reader["address"].ToString(),
Mark = Convert.ToInt32(reader["mark"])
});
}
}
}
return(View(list));
}
public IActionResult StopStudent(Student student)
{
SystemContext context = HttpContext.RequestServices.GetService(typeof(Exam_Management_System.Models.SystemContext)) as SystemContext;
int academic = context.GetAcademic().Id;
using (MySqlConnection conn = context.GetConnection())
{
conn.Open();
string sql = $"UPDATE student SET status='stop' where id={student.Id}";
using (MySqlCommand command1 = new MySqlCommand(sql, conn))
{
command1.ExecuteNonQuery();
}
}
return(Redirect("/Student/Index/1?major=1&class_id=1"));
}
public string PostEditExam(Exam exam)
{
SystemContext context = HttpContext.RequestServices.GetService(typeof(Exam_Management_System.Models.SystemContext)) as SystemContext;
int academic_id = context.GetAcademic().Id;
using (MySqlConnection conn = context.GetConnection())
{
conn.Open();
string sql = $"Update exam set date='{exam.Date}',start='{exam.Start_time}',end='{exam.End_time}' where id={exam.Id}";
using (MySqlCommand command = new MySqlCommand(sql, conn))
{
command.ExecuteNonQuery();
conn.Close();
}
}
return("OK");
}
public string EditAttendence(Attendence attendence)
{
SystemContext context = HttpContext.RequestServices.GetService(typeof(Exam_Management_System.Models.SystemContext)) as SystemContext;
int academic_id = context.GetAcademic().Id;
using (MySqlConnection conn = context.GetConnection())
{
conn.Open();
string sql = $"Update attendance set current='{attendence.Current}' where id={attendence.Id}";
using (MySqlCommand command = new MySqlCommand(sql, conn))
{
command.ExecuteNonQuery();
conn.Close();
}
}
return("OK");
}
public string PostAddAttendence(Attendence attendence)
{
int student_id = 0;
string ck = null;
SystemContext context = HttpContext.RequestServices.GetService(typeof(Exam_Management_System.Models.SystemContext)) as SystemContext;
int academic_id = context.GetAcademic().Id;
using (MySqlConnection conn1 = context.GetConnection())
{
conn1.Open();
MySqlCommand cmd1 = new MySqlCommand("SELECT * FROM studentrollno where rollno='" + attendence.Rollno + "' and academic_id=" + academic_id, conn1);
using (var reader = cmd1.ExecuteReader())
{
while (reader.Read())
{
student_id = Convert.ToInt32(reader["id"]);
}
}
conn1.Close();
}
if (context.CheckAttendence(student_id, attendence.Month, academic_id) == 0)
{
using (MySqlConnection conn = context.GetConnection())
{
conn.Open();
string sql = $"Insert Into attendance (studentrollno_id,month,total,current,academic_id) Values ('{student_id}','{attendence.Month}','{attendence.Total}','{attendence.Current}','{academic_id}')";
using (MySqlCommand command = new MySqlCommand(sql, conn))
{
command.ExecuteNonQuery();
conn.Close();
}
}
ck = "OK";
}
else
{
ck = "NO";
}
return(ck);
//return Redirect("/attendence/addAttendence");
}
public string PostAddExam(Exam exam)
{
SystemContext context = HttpContext.RequestServices.GetService(typeof(Exam_Management_System.Models.SystemContext)) as SystemContext;
int academic_id = context.GetAcademic().Id;
using (MySqlConnection conn = context.GetConnection())
{
conn.Open();
string sql = null;
sql = $"Insert Into exam (examtitle_id,subject_id,year_id,academic_id,start,end,date) Values ('{exam.Exam_id}','{exam.Subject_id}','{exam.Year_id}','{academic_id}','{exam.Start_time}','{exam.End_time}','{exam.Date}')";
using (MySqlCommand command = new MySqlCommand(sql, conn))
{
command.ExecuteNonQuery();
conn.Close();
}
}
return("OK");
}
public string GetYearOne(int id)
{
SystemContext context = HttpContext.RequestServices.GetService(typeof(Exam_Management_System.Models.SystemContext)) as SystemContext;
int academic_id = context.GetAcademic().Id;
string year_name = null;
using (MySqlConnection conn = context.GetConnection())
{
conn.Open();
MySqlCommand cmd = new MySqlCommand("SELECT * FROM year where id=" + id, conn);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
year_name = reader["year_name"].ToString();
}
}
}
return(year_name);
}
public List <Student> GetStudentYear(string id)
{
SystemContext context = HttpContext.RequestServices.GetService(typeof(Exam_Management_System.Models.SystemContext)) as SystemContext;
int academic_id = context.GetAcademic().Id;
int secondaca = context.GetAcademicSecond().Id;
List <Student> list = new List <Student>();
using (MySqlConnection conn = context.GetConnection())
{
conn.Open();
MySqlCommand cmd = new MySqlCommand("select * from studentrollno,student,old_student,result,major,year where result.studentrollno_id=studentrollno.id and studentrollno.student_id=student.id and old_student.student_id=student.id and studentrollno.rollno='" + id + "' and old_student.major_id=major.id and old_student.student_year_id=year.id order by result.academic_id", conn);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
list.Add(new Student()
{
Id = Convert.ToInt32(reader["id"]),
Rollno = reader["rollno"].ToString(),
Name = reader["student_name"].ToString(),
Major = reader["major_name"].ToString(),
Major_id = Convert.ToInt32(reader["major_id"]),
Year = reader["year_name"].ToString(),
Year_id = Convert.ToInt32(reader["student_year_id"]),
Pass = Convert.ToInt32(reader["pass"]),
Father_name = reader["father_name"].ToString(),
Mother_name = reader["mother_name"].ToString(),
Img = reader["img"].ToString(),
Phone = reader["phone"].ToString(),
Father_phone = reader["father_phone"].ToString(),
Mother_phone = reader["mother_phone"].ToString(),
Email = reader["email"].ToString(),
Address = reader["address"].ToString(),
});
}
}
}
return(list);
}
public JsonResult GetStudent(int id, int major, int class_id)
{
SystemContext context = HttpContext.RequestServices.GetService(typeof(Exam_Management_System.Models.SystemContext)) as SystemContext;
List <Student> list = new List <Student>();
int academic = context.GetAcademic().Id;
using (MySqlConnection conn = context.GetConnection())
{
conn.Open();
MySqlCommand cmd = new MySqlCommand("SELECT * FROM studentrollno,student,student_detail,class,year,major WHERE student.id=studentrollno.student_id and studentrollno.id=student_detail.studentrollno_id and student_detail.year_id=year.id and student_detail.class_id=class.id and student_detail.year_id=" + id + " and student_detail.major_id=major.id and student_detail.major_id=" + major + " and student_detail.class_id=" + class_id + " and student_detail.academic_id=" + academic, conn);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
ViewData["year"] = reader["year_name"].ToString();
ViewData["year_id"] = Convert.ToInt32(reader["year_id"]);
int Id = Convert.ToInt32(reader["id"]);
String name = reader["student_name"].ToString();
list.Add(new Student()
{
Id = Id,
Name = name,
Year = reader["year_name"].ToString(),
Class_id = Convert.ToInt32(reader["class_id"]),
Class = reader["class_name"].ToString(),
Year_id = Convert.ToInt32(reader["year_id"]),
Major_id = Convert.ToInt32(reader["major_id"]),
Major = reader["major_name"].ToString(),
Rollno = reader["rollno"].ToString(),
Student_id = Convert.ToInt32(reader["student_id"]),
Status = reader["status"].ToString(),
});
}
}
}
return(Json(list));
}
public JsonResult ResultFinalResultOne(int id)
{
List <Mark> list = new List <Mark>();
SystemContext context = HttpContext.RequestServices.GetService(typeof(Exam_Management_System.Models.SystemContext)) as SystemContext;
int academic_id = context.GetAcademic().Id;
int mark_mid = 0;
int mark = 0;
int final_mark = 0;
int ass_mark = 0;
using (MySqlConnection conn1 = context.GetConnection())
{
conn1.Open();
//MySqlCommand cmd1 = new MySqlCommand("select * from mark_final,result,subject,studentrollno,student_detail,student,year,major,class where student_detail.class_id=class.id and mark_final.subject_id=subject.id and studentrollno.id=result.studentrollno_id and mark_final.studentrollno_id=studentrollno.id and student_detail.studentrollno_id=studentrollno.id and studentrollno.student_id=student.id and student_detail.year_id=year.id and student_detail.major_id=major.id and student_detail.major_id=" + major + " and student_detail.year_id=" + id + " and mark_final.academic_id=" + academic_id + " and student_detail.class_id=" + class_id+ " order by mark_final.studentrollno_id,mark_final.subject_id asc", conn1);
MySqlCommand cmd1 = new MySqlCommand("select * from mark_final,subject where mark_final.subject_id=subject.id and studentrollno_id=" + id + " and academic_id=" + academic_id + " order By subject_id asc", conn1);
using (var reader = cmd1.ExecuteReader())
{
while (reader.Read())
{
mark_mid = context.GetMidMarkOne(Convert.ToInt32(reader["studentrollno_id"]), Convert.ToInt32(reader["subject_id"]), academic_id);
final_mark = Convert.ToInt32(reader["mark"]);
ass_mark = context.GetAss_MarkOne(Convert.ToInt32(reader["studentrollno_id"]), Convert.ToInt32(reader["subject_id"]), academic_id);
mark = (mark_mid + final_mark) * 80 / 200;
mark = mark + ass_mark;
list.Add(new Mark()
{
Id = Convert.ToInt32(reader["id"]),
Subject = reader["subject_name"].ToString(),
Subject_id = Convert.ToInt32(reader["subject_id"]),
S_mark = mark,
Rollno_id = Convert.ToInt32(reader["studentrollno_id"]),
});
}
}
conn1.Close();
}
return(Json(list));
}
public void Generate()
{
SystemContext context = HttpContext.RequestServices.GetService(typeof(Exam_Management_System.Models.SystemContext)) as SystemContext;
int academic = context.GetAcademic().Id;
List <Year> years = new List <Year>();
List <Student> list = new List <Student>();
List <Student> oldstudent = new List <Student>();
int roll = 1;
int roll2 = 1; int ct_roll2 = 1;
int roll3 = 1; int ct_roll3 = 1;
int roll4 = 1; int ct_roll4 = 1;
int roll5 = 1; int ct_roll5 = 1;
using (MySqlConnection conn = context.GetConnection())
{
conn.Open();
MySqlCommand cmd1 = new MySqlCommand("select * from year", conn);
using (var reader = cmd1.ExecuteReader())
{
while (reader.Read())
{
years.Add(new Year()
{
Id = Convert.ToInt32(reader["id"]),
Name = reader["year_name"].ToString(),
});
}
}
foreach (var year in years)
{
list.Clear();
MySqlCommand cmd = new MySqlCommand("select * from old_student where academic_id=" + academic + " and student_year_id=" + year.Id + " order by mark desc ", conn);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
list.Add(new Student()
{
Id = Convert.ToInt32(reader["id"]),
Student_id = Convert.ToInt32(reader["student_id"]),
//Name = reader["student_name"].ToString(),
Year_id = Convert.ToInt32(reader["student_year_id"]),
Major_id = Convert.ToInt32(reader["major_id"])
});
}
}
foreach (var student in list)
{
int studentrollno_id = 0;
int class_id = 0;
string rollno1 = null;
if (student.Year_id == 1)
{
rollno1 = "1CST-" + roll;
class_id = context.GetClassStudent(student.Year_id, student.Major_id, roll);
roll++;
}
else if (student.Year_id == 2)
{
if (student.Major_id == 2)
{
rollno1 = "2CS-" + roll2;
class_id = context.GetClassStudent(student.Year_id, student.Major_id, roll2);
roll2++;
}
else
{
rollno1 = "2CT-" + ct_roll2;
class_id = context.GetClassStudent(student.Year_id, student.Major_id, roll2);
ct_roll2++;
}
}
else if (student.Year_id == 3)
{
if (student.Major_id == 2)
{
rollno1 = "3CS-" + roll3;
class_id = context.GetClassStudent(student.Year_id, student.Major_id, roll3);
roll3++;
}
else
{
rollno1 = "3CT-" + ct_roll3;
class_id = context.GetClassStudent(student.Year_id, student.Major_id, roll3);
ct_roll3++;
}
}
else if (student.Year_id == 4)
{
if (student.Major_id == 2)
{
rollno1 = "4CS-" + roll4;
class_id = context.GetClassStudent(student.Year_id, student.Major_id, roll4);
roll4++;
}
else
{
rollno1 = "4CT-" + ct_roll4;
class_id = context.GetClassStudent(student.Year_id, student.Major_id, roll4);
ct_roll4++;
}
}
else
{
if (student.Major_id == 2)
{
rollno1 = "5CS-" + roll5;
class_id = context.GetClassStudent(student.Year_id, student.Major_id, roll5);
roll5++;
}
else
{
rollno1 = "5CT-" + ct_roll5;
class_id = context.GetClassStudent(student.Year_id, student.Major_id, roll5);
ct_roll5++;
}
}
string sql = $"Insert Into studentrollno (student_id,academic_id,rollno) Values ('{student.Student_id}','{academic}','{rollno1}')";
using (MySqlCommand command = new MySqlCommand(sql, conn))
{
command.ExecuteNonQuery();
studentrollno_id = context.GetlastId();
}
string sql1 = $"Insert Into student_detail (studentrollno_id,year_id,major_id,class_id,academic_id) Values ('{studentrollno_id}','{student.Year_id}','{student.Year_id}','{class_id}','{academic}')";
using (MySqlCommand command1 = new MySqlCommand(sql1, conn))
{
command1.ExecuteNonQuery();
}
}
}
//////////////////////////////////////////////////////////////////////////////////////
}
}
public string PostAddMark(Mark mark)
{
SystemContext context = HttpContext.RequestServices.GetService(typeof(Exam_Management_System.Models.SystemContext)) as SystemContext;
int academic_id = context.GetAcademic().Id;
int student_id = context.GetStudentId(mark.Rollno, academic_id);
using (MySqlConnection conn = context.GetConnection())
{
conn.Open();
string sql = null;
if (mark.Exam_id == 1)
{
if (context.CheckMidMark(student_id, mark.Subject_id, academic_id) == 0)
{
sql = $"Insert Into mark_mid (studentrollno_id,mark,subject_id,academic_id) Values ('{student_id}','{mark.S_mark}','{mark.Subject_id}','{academic_id}')";
using (MySqlCommand command = new MySqlCommand(sql, conn))
{
command.ExecuteNonQuery();
conn.Close();
}
}
else
{
return("NO");
}
}
else
{
if (context.CheckFinalMark(student_id, mark.Subject_id, academic_id) == 0)
{
sql = $"Insert Into mark_final (studentrollno_id,mark,subject_id,academic_id) Values ('{student_id}','{mark.S_mark}','{mark.Subject_id}','{academic_id}')";
using (MySqlCommand command = new MySqlCommand(sql, conn))
{
command.ExecuteNonQuery();
//conn.Close();
}
int mid_mark = context.GetMid_Mark(student_id, academic_id);
int assignment = context.GetAss_Mark(student_id, academic_id);
int pass = context.GetPass(student_id, academic_id, mark.Subject_id, mark.S_mark);
// int credit = context.GetCredit(student_id, academic_id, mark.S_mark, mark.Subject_id);
int final_mark = context.GetFinal_Mark(student_id, academic_id);
int totalMark = context.GetTotalMark(student_id, academic_id);
if (context.CheckResult(student_id, academic_id) == 0)
{
sql = $"Insert Into result (studentrollno_id,mid_mark,final_mark,assigment_mark,pass,academic_id,total_mark) Values ('{student_id}','{mid_mark}','{final_mark}','{assignment}','{pass}','{academic_id}','{totalMark}')";
}
else
{
sql = $"UPDATE result SET final_mark={final_mark},pass={pass},attendence_mark={context.tt},total_mark={totalMark} where studentrollno_id={student_id} and academic_id={academic_id}";
}
using (MySqlCommand command = new MySqlCommand(sql, conn))
{
command.ExecuteNonQuery();
conn.Close();
}
}
else
{
return("NO");
}
}
}
return("OK");
}