public void EditDiscipline(mst_discipline mst)
{
try
{
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
mst_sessionMain session = new mst_sessionMain();
string query = "UPDATE mst_discipline SET discipline_name = @discipline_name WHERE discipline_id = @discipline_id and session = @session";
mst.session = session.findFinal_Session();
con.Execute(query, mst);
}
}
catch (Exception ex)
{
throw ex;
}
}
public void AddDiscipline(mst_discipline mst)
{
try
{
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
mst_sessionMain session = new mst_sessionMain();
string query = "INSERT INTO mst_discipline (session,discipline_id,discipline_name) VALUES (@session,@discipline_id,@discipline_name)";
string maxid = @"SELECT
IFNULL(MAX(discipline_id), 0) + 1
FROM
mst_discipline
where
session = (SELECT
session
FROM
mst_session
WHERE
session_finalize = 'Y')";
int id = con.ExecuteScalar <int>(maxid);
mst.session = session.findFinal_Session();
mst.discipline_id = id;
mst.discipline_name = mst.discipline_name.Trim();
con.Execute(query, new
{
mst.session,
mst.discipline_id,
mst.discipline_name
});
}
}
catch (Exception ex)
{
throw ex;
}
}
public IEnumerable <mst_exam_marks> student_list_for_marks(int class_id, int section_id)
{
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
mst_sessionMain sess = new mst_sessionMain();
string session_name = sess.findFinal_Session();
string query = @"SELECT
e.class_id,
d.section_id,
c.roll_number roll_no,
a.sr_number sr_num,
CONCAT(IFNULL(a.std_first_name, ''),
' ',
IFNULL(std_last_name, '')) std_name
FROM
sr_register a,
mst_section b,
mst_rollnumber c,
mst_std_section d,
mst_std_class e
WHERE
d.section_id = b.section_id
AND d.section_id = @section_id
AND e.class_id = @class_id
AND e.class_id = b.class_id
AND d.section_id = b.section_id
AND b.session = c.session
AND c.session = d.session
AND d.session = e.session
AND e.session = @session
AND a.sr_number = c.sr_num
AND c.sr_num = d.sr_num
AND d.sr_num = e.sr_num
AND a.std_active = 'Y'
ORDER BY roll_no";
return(con.Query <mst_exam_marks>(query, new { class_id = class_id, section_id = section_id, session = session_name }));
}
}
public IEnumerable <mst_discipline_grades> student_list_for_Discipline_Grades(int class_id, int section_id)
{
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
mst_sessionMain sess = new mst_sessionMain();
string session_name = sess.findFinal_Session();
string query = @"SELECT
b.class_id,
b.section_id,
c.roll_number roll_no,
a.sr_number sr_num,
CONCAT(IFNULL(a.std_first_name, ''),
' ',
IFNULL(std_last_name, '')) std_name
FROM
sr_register a,
mst_section b,
mst_rollnumber c,
mst_std_section e,
mst_std_class f
WHERE
a.sr_number = e.sr_num
AND e.sr_num = f.sr_num
AND f.sr_num = c.sr_num
AND e.section_id = b.section_id
AND b.section_id = @section_id
AND b.class_id = @class_id
AND f.class_id = b.class_id
AND e.section_id = b.section_id
AND b.session = c.session
AND c.session = e.session
AND e.session = f.session
AND f.session = @session
ORDER BY roll_no";
return(con.Query <mst_discipline_grades>(query, new { class_id = class_id, section_id = section_id, session = session_name }));
}
}
public IEnumerable <mst_exam_class> AllExamClassList()
{
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
mst_sessionMain sess = new mst_sessionMain();
string query = @"SELECT
a.session, a.class_id, a.exam_id, c.class_name, b.exam_name
FROM
mst_exam_class a,
mst_exam b,
mst_class c
WHERE
a.class_id = c.class_id
AND a.exam_id = b.exam_id
AND a.session = @session
AND a.session = b.session
AND b.session = c.session";
var result = con.Query <mst_exam_class>(query, new { session = sess.findFinal_Session() });
return(result);
}
}
public void AddExamMarks(List <mst_exam_marks> mst)
{
try
{
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
mst_sessionMain sess = new mst_sessionMain();
string session = sess.findFinal_Session();
string query = @"INSERT INTO `mst_exam_marks`
(`session`,
`sr_num`,
`exam_id`,
`subject_id`,
`user_id`,
`marks_assigned_user_id`,
`marks`,
`present`)
VALUES
(@session,
@sr_num,
@exam_id,
@subject_id,
@user_id,
@marks_assigned_user_id,
@marks,
@present)";
string query1 = @"UPDATE `mst_exam_marks`
SET
`marks` = @marks,
`present` = @present,
`user_id` = @user_id,
`marks_assigned_user_id` = @marks_assigned_user_id
WHERE
`session` = @session
AND `sr_num` = @sr_num
AND `exam_id` = @exam_id
AND `subject_id` = @subject_id";
string query2 = @"SELECT
COUNT(*)
FROM
mst_exam_marks
WHERE
`session` = @session
AND `sr_num` = @sr_num
AND `exam_id` = @exam_id
AND `subject_id` = @subject_id";
foreach (var marks in mst)
{
marks.session = session;
int count = con.Query <int>(query2, new { session = marks.session, sr_num = marks.sr_num, exam_id = marks.exam_id, subject_id = marks.subject_id, class_id = marks.class_id, section_id = marks.section_id }).SingleOrDefault();
if (count > 0)
{
con.Execute(query1, new
{
marks.session,
marks.sr_num,
marks.exam_id,
marks.subject_id,
marks.marks,
marks.user_id,
marks.marks_assigned_user_id,
marks.present
});
}
else
{
con.Execute(query, new
{
marks.session,
marks.sr_num,
marks.exam_id,
marks.subject_id,
marks.user_id,
marks.marks_assigned_user_id,
marks.marks,
marks.present
});
}
}
}
}
catch (Exception ex)
{
throw ex;
}
}
public IEnumerable <mst_discipline_grades> FindDisciplineGrades(int class_id, int section_id, int discipline_id, int term_id)
{
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
mst_sessionMain session = new mst_sessionMain();
string Query = @"SELECT
d.class_id,
e.section_id,
b.roll_number roll_no,
a.grade,
a.sr_num,
CONCAT(IFNULL(c.std_first_name, ''),
' ',
IFNULL(c.std_last_name, '')) std_name
FROM
mst_discipline_grades a,
mst_rollnumber b,
sr_register c,
mst_std_class d,
mst_std_section e
WHERE
a.sr_num = b.sr_num
AND b.sr_num = c.sr_number
AND c.sr_number = d.sr_num
AND d.sr_num = e.sr_num
AND a.session = b.session
AND b.session = d.session
AND d.session = e.session
AND e.session = @session
AND d.class_id = @class_id
AND e.section_id = @section_id
AND a.discipline_id = @discipline_id
AND a.term_id = @term_id
ORDER BY roll_number";
return(con.Query <mst_discipline_grades>(Query, new { class_id = class_id, discipline_id = discipline_id, section_id = section_id, session = session.findFinal_Session(), term_id = term_id }));
}
}
public IEnumerable <mst_discipline_grades> AllDisciplineList()
{
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
mst_sessionMain sess = new mst_sessionMain();
string query = @"SELECT
a.session,
d.class_id,
a.discipline_id,
c.class_name,
b.discipline_name
FROM
mst_discipline_grades a,
mst_discipline b,
mst_class c,
mst_std_class d
WHERE
a.sr_num = d.sr_num
AND d.class_id = c.class_id
AND a.discipline_id = b.discipline_id
AND b.session = a.session
AND a.session = c.session
AND c.session = d.session
AND d.session = @session";
var result = con.Query <mst_discipline_grades>(query, new { session = sess.findFinal_Session() });
return(result);
}
}
public void AddDisciplineGrades(List <mst_discipline_grades> mst)
{
try
{
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
mst_sessionMain sess = new mst_sessionMain();
string session = sess.findFinal_Session();
string query = @"INSERT INTO `mst_discipline_grades`
(`session`,
`sr_num`,
`term_id`,
`discipline_id`,
`grade`,
`user_id`)
VALUES
(@session,
@sr_num,
@term_id,
@discipline_id,
@grade,
@user_id)";
string update = @"UPDATE `mst_discipline_grades`
SET
`grade` = @grade,
`user_id` = @user_id
WHERE
`session` = @session
AND `sr_num` = @sr_num
AND `term_id` = @term_id
AND `discipline_id` = @discipline_id";
string query1 = @"SELECT
COUNT(*)
FROM
`mst_discipline_grades`
WHERE
`session` = @session
AND `sr_num` = @sr_num
AND `term_id` = @term_id
AND `discipline_id` = @discipline_id";
foreach (var marks in mst)
{
marks.session = session;
int count = con.Query <int>(query1, new { session = marks.session, sr_num = marks.sr_num, term_id = marks.term_id, discipline_id = marks.discipline_id, class_id = marks.class_id, section_id = marks.section_id }).SingleOrDefault();
if (count > 0)
{
con.Execute(update, new
{
marks.session,
marks.sr_num,
marks.term_id,
marks.discipline_id,
marks.grade,
marks.user_id
});
}
else
{
con.Execute(query, new
{
marks.session,
marks.sr_num,
marks.term_id,
marks.discipline_id,
marks.grade,
marks.user_id
});
}
}
}
}
catch (Exception ex)
{
throw ex;
}
}
public async Task mark_attendance(List <attendance_register> attendance, int user_id)
{
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
mst_sessionMain sess = new mst_sessionMain();
string session_name = sess.findFinal_Session();
int class_id = 0;
int section_id = 0;
int month_no = 0;
DateTime att_date = DateTime.Now.Date;
if (sess.checkSessionNotExpired())
{
string query = @"INSERT INTO attendance_register
(`session`,
`user_id`,
`att_date`,
`sr_num`,
`attendance`)
VALUES
(@session,
@user_id,
@att_date,
@sr_num,
@attendance)";
foreach (attendance_register att in attendance)
{
att.session = session_name;
att.user_id = user_id;
att.att_date = System.DateTime.Now.AddMinutes(dateTimeOffSet);
await con.ExecuteAsync(query,
new
{
att.session,
att.user_id,
att.att_date,
att.sr_num,
att.attendance
});
class_id = att.class_id;
section_id = att.section_id;
month_no = att.att_date.Month;
att_date = att.att_date.Date;
}
repAttendance_sheetMain sendAttSheet = new repAttendance_sheetMain();
query = @"select c.Email from mst_attendance a,emp_profile b,emp_profile c
where
a.user_id = b.user_id
and
a.class_id = @class_id
and
a.user_id = @user_id
and
a.section_id = @section_id
and
a.finalizer = c.user_id";
string email_id = con.Query <string>(query, new { class_id = class_id, user_id = user_id, section_id = section_id }).SingleOrDefault();
sendAttSheet.MailAttendanceSheet(section_id, month_no, session_name, email_id, att_date);
}
}
}
public IEnumerable <mst_exam_marks> find_marks(int exam_id, int subject_id, int class_id, int section_id)
{
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
mst_sessionMain session = new mst_sessionMain();
string Query = @"SELECT
*
FROM
mst_exam_marks a,
mst_std_class b,
mst_std_section c
WHERE
exam_id = @exam_id
AND a.session = @session
AND a.subject_id = @subject_id
AND b.class_id = @class_id
AND c.section_id = @section_id
AND a.session = b.session
AND b.session = c.session
AND c.session = (SELECT
session
FROM
mst_session
WHERE
session_finalize = 'Y')
AND a.sr_num = b.sr_num
AND b.sr_num = c.sr_num";
return(con.Query <mst_exam_marks>(Query, new { class_id = class_id, section_id = section_id, exam_id = exam_id, subject_id = subject_id, session = session.findFinal_Session() }));
}
}
public void AddRemark(List <teacher_exam_remark> mst)
{
try
{
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
mst_sessionMain sess = new mst_sessionMain();
string session = sess.findFinal_Session();
string query = @"INSERT INTO `teacher_exam_remark`
(`session`,
`term_id`,
`sr_number`,
`remark`,
`user_id`)
VALUES
(@session,
@term_id,
@sr_number,
@remark,
@user_id)";
string update = @"UPDATE `teacher_exam_remark`
SET
`user_id` = @user_id,
`remark` = @remark
WHERE
`session` = @session
AND `term_id` = @term_id
AND `sr_number` = @sr_number";
string query1 = @"SELECT
COUNT(*)
FROM
`teacher_exam_remark`
WHERE
`session` = @session
AND `term_id` = @term_id
AND `sr_number` = @sr_number";
foreach (var remark in mst)
{
remark.session = session;
int count = con.Query <int>(query1, new { session = remark.session, sr_number = remark.sr_number, term_id = remark.term_id, class_id = remark.class_id, section_id = remark.section_id }).SingleOrDefault();
if (count > 0)
{
con.Execute(update, new
{
remark.session,
remark.sr_number,
remark.term_id,
remark.remark,
remark.user_id
});
}
else
{
con.Execute(query, new
{
remark.session,
remark.term_id,
remark.sr_number,
remark.remark,
remark.user_id
});
}
}
}
}
catch (Exception ex)
{
throw ex;
}
}
public mst_co_scholastic FindCoScholastic(int?id)
{
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
mst_sessionMain session = new mst_sessionMain();
string Query = @"SELECT
co_scholastic_id, co_scholastic_name
FROM
mst_co_scholastic
WHERE
co_scholastic_id = @co_scholastic_id
AND session = @session";
return(con.Query <mst_co_scholastic>(Query, new { co_scholastic_id = id, session = session.findFinal_Session() }).SingleOrDefault());
}
}
public IEnumerable <mst_exam_marks> student_list_for_marks_update(int subject_id, int class_id, int section_id, int exam_id)
{
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
mst_sessionMain sess = new mst_sessionMain();
string session_name = sess.findFinal_Session();
//string query = @"SELECT
// d.class_id,
// e.section_id,
// c.roll_number roll_no,
// a.sr_num,
// CONCAT(IFNULL(b.std_first_name, ''),
// ' ',
// IFNULL(b.std_last_name, '')) std_name,
// a.marks,
// a.present
// FROM
// mst_exam_marks a,
// sr_register b,
// mst_rollnumber c,
// mst_std_class d,
// mst_std_section e
// WHERE
// a.exam_id = @exam_id
// AND e.session = @session
// AND a.subject_id = @subject_id
// AND d.class_id = @class_id
// AND e.section_id = @section_id
// AND a.sr_num = b.sr_number
// AND b.sr_number = c.sr_num
// AND a.session = c.session
// AND c.session = d.session
// AND d.session = e.session
// AND a.sr_num = b.sr_number
// AND b.sr_number = c.sr_num
// AND c.sr_num = d.sr_num
// AND d.sr_num = e.sr_num
// AND b.std_active = 'Y'
// ORDER BY c.roll_number";
string query = @"SELECT
e.class_id,
d.section_id,
c.roll_number roll_no,
a.sr_number sr_num,
CONCAT(IFNULL(a.std_first_name, ''),
' ',
IFNULL(std_last_name, '')) std_name,
(SELECT
MARKS
FROM
mst_exam_marks
WHERE
sr_num = a.sr_number
AND session = e.session
AND exam_id = @exam_id
AND subject_id = @subject_id) marks,
ifnull((SELECT
present
FROM
mst_exam_marks
WHERE
sr_num = a.sr_number
AND session = e.session
AND exam_id = @exam_id
AND subject_id = @subject_id),1) present
FROM
sr_register a,
mst_section b,
mst_rollnumber c,
mst_std_section d,
mst_std_class e
WHERE
d.section_id = b.section_id
AND d.section_id = @section_id
AND e.class_id = @class_id
AND e.class_id = b.class_id
AND d.section_id = b.section_id
AND b.session = c.session
AND c.session = d.session
AND d.session = e.session
AND e.session = @session
AND a.sr_number = c.sr_num
AND c.sr_num = d.sr_num
AND d.sr_num = e.sr_num
AND a.std_active = 'Y'
ORDER BY roll_no";
return(con.Query <mst_exam_marks>(query, new { subject_id = subject_id, class_id = class_id, section_id = section_id, session = session_name, exam_id = exam_id }));
}
}
public mst_subject DeleteSubject(int id)
{
try
{
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
mst_sessionMain session = new mst_sessionMain();
string Query = @"DELETE FROM mst_subject
WHERE
subject_id = @subject_id
AND session = @session";
return(con.Query <mst_subject>(Query, new { subject_id = id, session = session.findFinal_Session() }).SingleOrDefault());
}
}
catch (Exception ex)
{
throw ex;
}
}
public mst_discipline FindDiscipline(int?id)
{
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
mst_sessionMain session = new mst_sessionMain();
string Query = "SELECT discipline_id,discipline_name FROM mst_discipline where discipline_id = @discipline_id and session = @session";
return(con.Query <mst_discipline>(Query, new { discipline_id = id, session = session.findFinal_Session() }).SingleOrDefault());
}
}
public mst_subject FindSubject(int?id)
{
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
mst_sessionMain session = new mst_sessionMain();
string Query = @"SELECT
subject_id, subject_name
FROM
mst_subject
WHERE
subject_id = @subject_id
AND session = @session";
return(con.Query <mst_subject>(Query, new { subject_id = id, session = session.findFinal_Session() }).SingleOrDefault());
}
}
public mst_discipline DeleteDiscipline(int id)
{
try
{
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
mst_sessionMain session = new mst_sessionMain();
string Query = "DELETE FROM mst_discipline WHERE discipline_id = @discipline_id and session = @session";
return(con.Query <mst_discipline>(Query, new { discipline_id = id, session = session.findFinal_Session() }).SingleOrDefault());
}
}
catch (Exception ex)
{
throw ex;
}
}
public IEnumerable <teacher_exam_remark> FindRemarks(int class_id, int section_id, int term_id)
{
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
mst_sessionMain session = new mst_sessionMain();
string Query = @"SELECT
*
FROM
(SELECT
d.class_id,
e.section_id,
b.roll_number roll_no,
a.remark,
a.sr_number,
CONCAT(IFNULL(c.std_first_name, ''), ' ', IFNULL(c.std_last_name, '')) std_name
FROM
teacher_exam_remark a, mst_rollnumber b, sr_register c, mst_std_class d, mst_std_section e
WHERE
a.sr_number = b.sr_num
AND b.sr_num = c.sr_number
AND c.sr_number = d.sr_num
AND d.sr_num = e.sr_num
AND a.session = b.session
AND b.session = d.session
AND d.session = e.session
AND e.session = @session
AND d.class_id = @class_id
AND e.section_id = @section_id
AND a.term_id = @term_id UNION ALL SELECT
b.class_id,
b.section_id,
c.roll_number roll_no,
'',
a.sr_number,
CONCAT(IFNULL(a.std_first_name, ''), ' ', IFNULL(std_last_name, '')) std_name
FROM
sr_register a, mst_section b, mst_rollnumber c, mst_std_class d, mst_std_section e
WHERE
e.section_id = b.section_id
AND d.class_id = b.class_id
AND a.sr_number = c.sr_num
AND c.sr_num = d.sr_num
AND d.sr_num = e.sr_num
AND b.session = c.session
AND c.session = d.session
AND d.session = e.session
AND e.session = @session
AND b.section_id = @section_id
AND b.class_id = @class_id
AND a.sr_number = c.sr_num
AND a.std_active = 'Y'
AND sr_number NOT IN (SELECT
sr_number
FROM
teacher_exam_remark a, mst_std_class b, mst_std_section c
WHERE
a.session = b.session
AND b.session = c.session
AND c.session = @session
AND a.sr_number = b.sr_num
AND b.sr_num = c.sr_num
AND a.term_id = @term_id
AND b.class_id = @class_id
AND c.section_id = @section_id)) a
ORDER BY a.roll_no";
return(con.Query <teacher_exam_remark>(Query, new { class_id = class_id, term_id = term_id, session = session.findFinal_Session(), section_id = section_id }));
}
}
public void DeleteCoScholastic(int id)
{
try
{
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
mst_sessionMain session = new mst_sessionMain();
string Query = @"DELETE FROM mst_co_scholastic
WHERE
co_scholastic_id = @co_scholastic_id
AND session = @session";
con.Query <mst_co_scholastic>(Query, new { co_scholastic_id = id, session = session.findFinal_Session() }).SingleOrDefault();
}
}
catch (Exception ex)
{
throw ex;
}
}