public IEnumerable <mst_class_discipline> AllClassDisciplineList()
{
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
mst_sessionMain sess = new mst_sessionMain();
string query = @"SELECT
a.session,
a.class_id,
a.discipline_id,
c.class_name,
b.discipline_name
FROM
mst_class_discipline a,
mst_discipline b,
mst_class c
WHERE
a.class_id = c.class_id
AND a.discipline_id = b.discipline_id
AND a.session = @session
AND b.session = a.session
AND c.session = b.session";
var result = con.Query <mst_class_discipline>(query, new { session = sess.findFinal_Session() });
return(result);
}
}
public void EditSubject(mst_subject mst)
{
try
{
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
mst_sessionMain session = new mst_sessionMain();
mst.session = session.findActive_finalSession();
string query = @"UPDATE mst_subject
SET
subject_name = @subject_name
WHERE
subject_id = @subject_id
AND session = @session";
con.Execute(query, mst);
}
}
catch (Exception ex)
{
throw ex;
}
}
public int max_roll_number(int class_id, int section_id)
{
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
mst_sessionMain session = new mst_sessionMain();
string sess = session.findActive_finalSession();
string query = @"SELECT
IFNULL(MAX(roll_number), 0)
FROM
mst_rollnumber 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_num = b.sr_num
AND b.sr_num = c.sr_num
AND b.class_id = @class_id
AND c.section_id = @section_id";
var result = con.Query <int>(query, new { class_id = class_id, session = sess, section_id = section_id }).SingleOrDefault();
return(result);
}
}
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 update_roll_no(List <mst_rollnumber> list)
{
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
string query = "";
mst_sessionMain session = new mst_sessionMain();
string sess = session.findActive_finalSession();
foreach (var li in list)
{
query = @"INSERT INTO `mst_rollnumber`
(`session`,
`sr_num`,
`roll_number`)
VALUES
(@session,
@sr_num,
@roll_number)";
con.Query <mst_rollnumber>(query, new { session = sess, sr_num = li.sr_num, roll_number = li.roll_number, class_id = li.class_id, section_id = li.section_id });
}
}
}
public void AddClassDiscipline(mst_class_discipline mst)
{
try
{
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
mst_sessionMain sess = new mst_sessionMain();
string query = @"INSERT INTO mst_class_discipline
(session
,class_id
,discipline_id)
VALUES
(@session,
@class_id,
@discipline_id)";
mst.session = sess.findFinal_Session();
con.Execute(query, new
{
mst.session,
mst.class_id,
mst.discipline_id
});
}
}
catch (Exception ex)
{
throw ex;
}
}
public void EditCoScholastic(mst_co_scholastic mst)
{
try
{
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
mst_sessionMain session = new mst_sessionMain();
mst.session = session.findFinal_Session();
string query = @"UPDATE mst_co_scholastic
SET
co_scholastic_name = @co_scholastic_name
WHERE
co_scholastic_id = @co_scholastic_id
AND session = @session";
con.Execute(query, mst);
}
}
catch (Exception ex)
{
throw ex;
}
}
public void AddTermRule(mst_term_rules mst)
{
try
{
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
mst_sessionMain sess = new mst_sessionMain();
string maxid = @"SELECT
IFNULL(MAX(evaluation_id), 0) + 1
FROM
mst_term_rules
WHERE
session = @session";
int id = con.Query <int>(maxid, new { session = sess.findFinal_Session() }).SingleOrDefault();
string query = @"INSERT INTO `mst_term_rules`
(`session`,
`evaluation_id`,
`term_id`,
`class_id`,
`evaluation_name`,
`exam_id1`,
`exam_id2`,
`rule`)
VALUES
(@session,
@evaluation_id,
@term_id,
@class_id,
@evaluation_name,
@exam_id1,
@exam_id2,
@rule)";
mst.session = sess.findFinal_Session();
mst.evaluation_id = id;
con.Execute(query, new
{
mst.session,
mst.evaluation_id,
mst.term_id,
mst.class_id,
mst.evaluation_name,
mst.exam_id1,
mst.exam_id2,
mst.rule
});
}
}
catch (Exception ex)
{
throw ex;
}
}
public void AddExam(mst_exam mst)
{
try
{
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
mst_sessionMain session = new mst_sessionMain();
string query = @"INSERT INTO `mst_exam`
(session,
`exam_id`,
`exam_name`,
`max_no`,
`convert_to`)
VALUES
(@session,
@exam_id,
@exam_name,
@max_no,
@convert_to)";
string maxid = @"SELECT
IFNULL(MAX(exam_id), 0) + 1
FROM
mst_exam
WHERE
session = (SELECT
session
FROM
mst_session
WHERE
session_finalize = 'Y')";
int id = con.ExecuteScalar <int>(maxid);
mst.session = session.findFinal_Session();
mst.exam_id = id;
mst.exam_name = mst.exam_name.Trim();
con.Execute(query, new
{
mst.session,
mst.exam_id,
mst.exam_name,
mst.max_no,
mst.convert_to
});
}
}
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_exam FindExam(int?id)
{
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
mst_sessionMain session = new mst_sessionMain();
string Query = "SELECT * FROM mst_exam where exam_id = @exam_id and session = @session ";
return(con.Query <mst_exam>(Query, new { exam_id = id, session = session.findFinal_Session() }).SingleOrDefault());
}
}
public mst_acc_head FindAccount(int?id)
{
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
mst_sessionMain sess = new mst_sessionMain();
string ses = sess.findActive_Session();
String Query = "SELECT acc_id,acc_name FROM mst_acc_head where acc_id = @acc_id and session = @session";
return(con.Query <mst_acc_head>(Query, new { acc_id = id, session = ses }).SingleOrDefault());
}
}
public IEnumerable <mst_discipline> AllDisciplineList()
{
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 session = @session";
var result = con.Query <mst_discipline>(query, new { session = session.findFinal_Session() });
return(result);
}
}
public IEnumerable <mst_exam> AllExamList()
{
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
mst_sessionMain session = new mst_sessionMain();
string query = "SELECT * FROM mst_exam where session = @session;";
var result = con.Query <mst_exam>(query, new { session = session.findFinal_Session() });
return(result);
}
}
public IEnumerable <mst_acc_head> AllAccountList()
{
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
mst_sessionMain sess = new mst_sessionMain();
string ses = sess.findActive_Session();
String query = "SELECT acc_id,acc_name FROM mst_acc_head where session = @session";
var result = con.Query <mst_acc_head>(query, new { session = ses });
return(result);
}
}
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 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_exam DeleteExam(int id)
{
try
{
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
mst_sessionMain session = new mst_sessionMain();
string Query = "DELETE FROM mst_exam WHERE exam_id = @exam_id and session = @session";
return(con.Query <mst_exam>(Query, new { exam_id = id, session = session.findFinal_Session() }).SingleOrDefault());
}
}
catch (Exception ex)
{
throw ex;
}
}
public IEnumerable <mst_subject> AllSubjectList()
{
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
session = @session";
var result = con.Query <mst_subject>(query, new { session = session.findFinal_Session() });
return(result);
}
}
public void AddClass(mst_class mst)
{
try
{
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
mst_sessionMain session = new mst_sessionMain();
string sess = session.findActive_Session();
string query = "INSERT INTO mst_class (session,class_id,class_name) VALUES (@session,@class_id,@class_name)";
string maxid = @"SELECT
IFNULL(MAX(class_id), 0) + 1
FROM
mst_class
WHERE
session = (SELECT
session
FROM
mst_session
WHERE
session_active = 'Y')";
// var id = con.Query<mst_section>(maxid).ToString().Trim();
int id = con.ExecuteScalar <int>(maxid);
mst.class_id = id;
mst.class_name = mst.class_name.Trim();
con.Execute(query, new
{
session = sess,
mst.class_id,
mst.class_name
});
}
}
catch (Exception ex)
{
throw ex;
}
}
public void AddTerm(mst_term mst)
{
try
{
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
mst_sessionMain sess = new mst_sessionMain();
string query = "INSERT INTO mst_term (session,term_id,term_name) VALUES (@session,@term_id,@term_name)";
string maxid = @"SELECT
IFNULL(MAX(term_id), 0) + 1
FROM
mst_term
WHERE
session = (SELECT
session
FROM
mst_session
WHERE
session_finalize = 'Y')";
// var id = con.Query<mst_section>(maxid).ToString().Trim();
int id = con.ExecuteScalar <int>(maxid);
mst.term_id = id;
mst.term_name = mst.term_name.Trim();
mst.session = sess.findFinal_Session();
con.Execute(query, new
{
mst.session,
mst.term_id,
mst.term_name
});
}
}
catch (Exception ex)
{
throw ex;
}
}
public IEnumerable <mst_rollnumber> student_list_for_rollnumber(int class_id, int section_id)
{
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
mst_sessionMain session = new mst_sessionMain();
string sess = session.findActive_finalSession();
string query = @"SELECT
c.class_id,
b.section_id,
a.sr_number sr_num,
CONCAT(IFNULL(a.std_first_name, ''),
' ',
IFNULL(std_last_name, '')) std_name
FROM
sr_register a,
mst_std_section b,
mst_std_class c
WHERE
a.sr_number = b.sr_num
AND b.sr_num = c.sr_num
AND b.section_id = @section_id
AND c.class_id = @class_id
AND b.session = @session
AND b.session = c.session
AND a.sr_number NOT IN (SELECT
sr_num
FROM
mst_rollnumber
WHERE
session = @session
AND class_id = @class_id
AND section_id = @section_id)
AND a.std_active = 'Y'
ORDER BY std_name";
return(con.Query <mst_rollnumber>(query, new { class_id = class_id, section_id = section_id, session = sess }));
}
}
public void AddHead(mst_acc_head mst)
{
try
{
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
string query = @"INSERT INTO mst_acc_head
(session
,acc_id
,acc_name)
VALUES
(@session
,@acc_id
,@acc_name)";
string maxid = "select ifnull(MAX(acc_id),0)+1 from mst_acc_head where session = @session";
mst_sessionMain sess = new mst_sessionMain();
string ses = sess.findActive_Session();
int id = con.ExecuteScalar <int>(maxid, new { session = ses });
mst.acc_id = id;
mst.session = ses;
con.Execute(query, new
{
mst.acc_id,
mst.acc_name,
mst.session
});
}
}
catch (Exception ex)
{
throw ex;
}
}
public IEnumerable <mst_term_rules> AllTermRuleList()
{
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
mst_sessionMain sess = new mst_sessionMain();
string query = @"SELECT
a.session,
a.term_id,
a.class_id,
c.term_name,
b.class_name,
evaluation_name,
evaluation_id,
d.exam_name exam_name1,
(SELECT
exam_name
FROM
mst_exam
WHERE
exam_id = a.exam_id2
AND session = @session) exam_name2,
a.rule
FROM
mst_term_rules a,
mst_class b,
mst_term c,
mst_exam d
WHERE
a.class_id = b.class_id
AND a.term_id = c.term_id
AND a.session = @session
AND a.session = b.session
AND b.session = c.session
AND c.session = d.session
AND a.exam_id1 = d.exam_id";
var result = con.Query <mst_term_rules>(query, new { session = sess.findFinal_Session() });
return(result);
}
}
public IEnumerable <teacher_exam_remark> student_list_for_remark(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.findActive_finalSession();
string query = @"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
a.sr_number = d.sr_num
AND d.sr_num = e.sr_num
AND e.sr_num = c.sr_num
AND e.section_id = b.section_id
AND d.class_id = b.class_id
AND b.section_id = @section_id
AND b.class_id = @class_id
AND d.class_id = b.class_id
AND e.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.std_active = 'Y'
ORDER BY roll_no";
return(con.Query <teacher_exam_remark>(query, new { class_id = class_id, section_id = section_id, session = session_name }));
}
}
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 EditExam(mst_exam mst)
{
try
{
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
mst_sessionMain session = new mst_sessionMain();
string query = "UPDATE mst_exam SET exam_name = @exam_name,max_no = @max_no,convert_to = @convert_to WHERE exam_id = @exam_id and session = @session ";
mst.session = session.findFinal_Session();
con.Execute(query, mst);
}
}
catch (Exception ex)
{
throw ex;
}
}
public mst_acc_head DeleteAccount(int id)
{
try
{
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
mst_sessionMain sess = new mst_sessionMain();
string ses = sess.findActive_Session();
String Query = "DELETE FROM mst_acc_head WHERE acc_id = @acc_id and session = @session";
return(con.Query <mst_acc_head>(Query, new { acc_id = id, session = ses }).SingleOrDefault());
}
}
catch (Exception ex)
{
throw ex;
}
}
public void AddCoScholastic(mst_co_scholastic mst)
{
try
{
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
string query = "INSERT INTO mst_co_scholastic (session,co_scholastic_id,co_scholastic_name) VALUES (@session,@co_scholastic_id,@co_scholastic_name)";
string maxid = @"SELECT
IFNULL(MAX(co_scholastic_id), 0) + 1
FROM
mst_co_scholastic
WHERE
session = (SELECT
session
FROM
mst_session
WHERE
session_finalize = 'Y')";
int id = con.ExecuteScalar <int>(maxid);
mst_sessionMain session = new mst_sessionMain();
mst.session = session.findActive_finalSession();
mst.co_scholastic_id = id;
mst.co_scholastic_name = mst.co_scholastic_name.Trim();
con.Execute(query, new
{
mst.co_scholastic_id,
mst.co_scholastic_name,
mst.session
});
}
}
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;
}
}