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 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 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 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 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 IEnumerable <mst_attendance> assign_class_list(int user_id)
{
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
mst_sessionMain session = new mst_sessionMain();
string sess = session.findActive_finalSession();
string query = @"SELECT
b.class_id, b.class_name, a.section_id, c.section_name
FROM
mst_attendance a,
mst_class b,
mst_section c
WHERE
a.user_id = @user_id
AND a.class_id = b.class_id
AND c.session = @session
AND b.session = c.session
AND a.section_id = c.section_id
AND c.section_id IN (SELECT DISTINCT
b.section_id
FROM
sr_register a,
mst_std_section b
WHERE
a.sr_number = b.sr_num
AND a.std_active = 'Y'
AND b.session = @session
AND a.sr_number NOT IN (SELECT
sr_num
FROM
mst_rollnumber where session = @session))";
var result = con.Query <mst_attendance>(query, new { user_id = user_id, session = sess });
return(result);
}
}
public void AddFees(std_discount std)
{
try
{
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
mst_sessionMain sess = new mst_sessionMain();
string query = @"INSERT INTO std_discount
(session
,sr_num
,acc_id
,percent
,bl_exempt
,bl_apr
,bl_may
,bl_jun
,bl_jul
,bl_aug
,bl_sep
,bl_oct
,bl_nov
,bl_dec
,bl_jan
,bl_feb
,bl_mar
,remark)
VALUES
(@session
,@sr_num
,@acc_id
,@percent
,@bl_exempt
,@bl_apr
,@bl_may
,@bl_jun
,@bl_jul
,@bl_aug
,@bl_sep
,@bl_oct
,@bl_nov
,@bl_dec
,@bl_jan
,@bl_feb
,@bl_mar
,@std_remarks)";
std.session = sess.findActive_finalSession();
con.Execute(query, new
{
std.session,
std.sr_num,
std.acc_id,
std.percent,
std.bl_exempt,
std.bl_apr,
std.bl_may,
std.bl_jun,
std.bl_jul,
std.bl_aug,
std.bl_sep,
std.bl_oct,
std.bl_nov,
std.bl_dec,
std.bl_jan,
std.bl_feb,
std.bl_mar,
std.std_remarks
});
var p = new DynamicParameters();
p.Add("@sr_num", std.sr_num);
p.Add("@ac_id", std.acc_id);
con.Execute("stdMiddiscount", p, commandType: System.Data.CommandType.StoredProcedure);
}
}
catch
{
throw;
}
}
public void EditStudent(sr_register std)
{
try
{
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
mst_sessionMain sess = new mst_sessionMain();
string session = sess.findActive_finalSession();
string query1 = @"select std_pickup_id from sr_register where sr_number = @sr_number";
int pick_id = con.Query <int>(query1, new { sr_number = std.sr_number }).SingleOrDefault();
query1 = @"select class_id from mst_std_class where sr_num = @sr_number and session = @session";
int class_id = con.Query <int>(query1, new { sr_number = std.sr_number, session = session }).SingleOrDefault();
query1 = @"select section_id from mst_std_section where sr_num = @sr_number and session = @session";
int sec_id = con.Query <int>(query1, new { sr_number = std.sr_number, session = session }).SingleOrDefault();
query1 = @"SELECT
std_active
FROM
sr_register
WHERE
sr_number = @sr_number";
string old_active_status = con.Query <string>(query1, new { sr_number = std.sr_number }).SingleOrDefault();
string query = @"UPDATE sr_register
SET std_first_name = @std_first_name
,std_last_name = @std_last_name
,std_father_name = @std_father_name
,std_mother_name = @std_mother_name
,std_address = @std_address
,std_address1 = @std_address1
,std_address2 = @std_address2
,std_district = @std_district
,std_state = @std_state
,std_country = @std_country
,std_pincode = @std_pincode
,std_contact = @std_contact
,std_contact1 = @std_contact1
,std_contact2 = @std_contact2
,std_email = @std_email
,std_father_occupation = @std_father_occupation
,std_mother_occupation = @std_mother_occupation
,std_blood_gp = @std_blood_gp
,std_house_income = @std_house_income
,std_nationality = @std_nationality
,std_category = @std_category
,std_cast = @std_cast
,std_dob = @std_dob
,std_sex = @std_sex
,std_last_school = @std_last_school
,std_admission_date = @std_admission_date
,std_house = @std_house
,std_remark = @std_remark
,std_active = @std_active
,std_pickup_id = @std_pickup_id
,std_admission_class = @std_admission_class
,std_aadhar = @std_aadhar
,adm_form_link = @adm_form_link
WHERE sr_number = @sr_number";
con.Execute(query, std);
if (!std.active && old_active_status == "Y")
{
out_standingMain otsd = new out_standingMain();
otsd.markStdNSO(std.sr_number);
DashboardHub hub = new DashboardHub();
hub.DashboardSchoolStrength();
}
else
{
if (old_active_status != std.std_active)
{
var p = new DynamicParameters();
p.Add("@sr_num", std.sr_number);
p.Add("@from_month_no", std.from_month_no);
con.Execute("StdMidSessionTransportChange", p, commandType: System.Data.CommandType.StoredProcedure);
DashboardHub hub = new DashboardHub();
hub.DashboardSchoolStrength();
p = new DynamicParameters();
p.Add("@sr_num", std.sr_number);
con.Execute("stdMidSessionMonthlyCharge", p, commandType: System.Data.CommandType.StoredProcedure);
string query2 = @"UPDATE sr_register
SET
nso_date = NULL
WHERE
sr_number = @sr_num";
con.Execute(query2, new { sr_num = std.sr_number });
}
else
{
if (pick_id != std.std_pickup_id && std.std_active == "Y")
{
//call procedure to change the pickup point
var p = new DynamicParameters();
p.Add("@sr_num", std.sr_number);
p.Add("@from_month_no", std.from_month_no);
con.Execute("StdMidSessionTransportChange", p, commandType: System.Data.CommandType.StoredProcedure);
DashboardHub hub = new DashboardHub();
hub.DashboardSchoolStrength();
}
if (class_id != std.class_id)
{
//call procedure to change the class
query = @"UPDATE `mst_std_class`
SET
`class_id` = @class_id
WHERE
`session` = @session
AND `sr_num` = @sr_num";
con.Execute(query, new { class_id = std.class_id, session = session, sr_num = std.sr_number });
if (std.std_active == "Y")
{
var p = new DynamicParameters();
p.Add("@sr_num", std.sr_number);
con.Execute("stdMidSessionMonthlyCharge", p, commandType: System.Data.CommandType.StoredProcedure);
}
}
if (sec_id != std.std_section_id)
{
query = @"UPDATE `mst_std_section`
SET
`section_id` = @section_id
WHERE
`session` = @session
AND `sr_num` = @sr_num";
con.Execute(query, new { section_id = std.std_section_id, session = session, sr_num = std.sr_number });
query = @"DELETE FROM `mst_rollnumber`
WHERE session = @session
and
sr_num = @sr_num";
con.Execute(query, new { sr_num = std.sr_number, session = session });
}
}
}
}
}
catch (Exception ex)
{
throw ex;
}
}