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_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 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 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 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 EditAccount(mst_acc_head mst)
{
try
{
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
mst_sessionMain sess = new mst_sessionMain();
string ses = sess.findActive_Session();
mst.session = ses;
string query = "UPDATE mst_acc_head SET acc_name = @acc_name WHERE acc_id = @acc_id and session = @session";
con.Execute(query, mst);
}
}
catch (Exception ex)
{
throw ex;
}
}
public void AddParameters(mst_subject_parameters mst)
{
try
{
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
mst_sessionMain sess = new mst_sessionMain();
string session = sess.findActive_Session();
string duplicate = @"SELECT
COUNT(*)
FROM
mst_subject_parameters
WHERE
subject_id = @subject_id
AND parameter_name = @parameter_name
AND session = (SELECT
session
FROM
mst_session
WHERE
session_active = 'Y')";
int dup = con.ExecuteScalar <int>(duplicate, new { mst.subject_id, mst.parameter_name });
if (dup > 0)
{
throw new DuplicateWaitObjectException();
}
else
{
string query = @"INSERT INTO `mst_subject_parameters`
(`session`,
`parameter_id`,
`subject_id`,
`parameter_name`)
VALUES
(@session,
@parameter_id,
@subject_id,
@parameter_name);";
string maxid = @"SELECT
IFNULL(MAX(parameter_id), 0) + 1
FROM
mst_subject_parameters
WHERE
session = @session";
// var id = con.Query<mst_section>(maxid).ToString().Trim();
int id = con.ExecuteScalar <int>(maxid, new { session = session });
mst.parameter_id = id;
mst.parameter_name = mst.parameter_name.Trim();
con.Execute(query, new
{
mst.parameter_id,
mst.parameter_name,
mst.subject_id,
session = session
});
}
}
}
catch (Exception ex)
{
throw ex;
}
}
public async Task AddStudent(sr_register std)
{
try
{
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
mst_sessionMain sess = new mst_sessionMain();
string maxid = @"SELECT
IFNULL(MAX(sr_number), 0) + 1
FROM
sr_register";
// var id = con.Query<mst_section>(maxid).ToString().Trim();
int id = con.ExecuteScalar <int>(maxid);
string query = @"INSERT INTO sr_register
(sr_number
,std_first_name
,std_last_name
,std_father_name
,std_mother_name
,std_address
,std_address1
,std_address2
,std_district
,std_state
,std_country
,std_pincode
,std_contact
,std_contact1
,std_contact2
,std_email
,std_father_occupation
,std_mother_occupation
,std_blood_gp
,std_house_income
,std_nationality
,std_category
,std_cast
,std_dob
,std_sex
,std_last_school
,std_admission_date
,std_house
,std_remark
,std_active
,std_pickup_id
,std_admission_class
,adm_session
,reg_no
,reg_date
,std_aadhar)
VALUES
(@sr_number
,@std_first_name
,@std_last_name
,@std_father_name
,@std_mother_name
,@std_address
,@std_address1
,@std_address2
,@std_district
,@std_state
,@std_country
,@std_pincode
,@std_contact
,@std_contact1
,@std_contact2
,@std_email
,@std_father_occupation
,@std_mother_occupation
,@std_blood_gp
,@std_house_income
,@std_nationality
,@std_category
,@std_cast
,@std_dob
,@std_sex
,@std_last_school
,@std_admission_date
,@std_house
,@std_remark
,@std_active
,@std_pickup_id
,@std_admission_class
,@adm_session
,@reg_no
,@reg_date
,@std_aadhar)";
std.std_active = "Y";
std.sr_number = id;
std.std_admission_date = DateTime.Parse(std.std_admission_date_str);
std.std_dob = DateTime.Parse(std.std_dob_str);
await con.ExecuteAsync(query,
new
{
std.sr_number
,
std.std_first_name
,
std.std_last_name
,
std.std_father_name
,
std.std_mother_name
,
std.std_address
,
std.std_address1
,
std.std_address2
,
std.std_district
,
std.std_state
,
std.std_country
,
std.std_pincode
,
std.std_contact
,
std.std_contact1
,
std.std_contact2
,
std.std_email
,
std.std_father_occupation
,
std.std_mother_occupation
,
std.std_blood_gp
,
std.std_house_income
,
std.std_nationality
,
std.std_category
,
std.std_cast
,
std.std_dob
,
std.std_sex
,
std.std_last_school
,
std.std_admission_date
,
std.std_admission_class
,
std.std_house
,
std.std_remark
,
std.std_pickup_id
,
std.std_active
,
std.adm_session
,
std.reg_no
,
std.reg_date
,
std.std_aadhar
});
query = @"INSERT INTO `mst_std_class`
(`session`,
`sr_num`,
`class_id`)
VALUES
(@session,
@sr_num,
@class_id)";
await con.ExecuteAsync(query, new { session = sess.findActive_Session(), sr_num = std.sr_number, class_id = std.class_id });
query = @"INSERT INTO `mst_std_section`
(`session`,
`sr_num`,
`section_id`)
VALUES
(@session,
@sr_num,
@section_id)";
await con.ExecuteAsync(query, new { session = sess.findActive_Session(), sr_num = std.sr_number, section_id = std.std_section_id });
std_registrationMain main = new std_registrationMain();
main.DeleteRegistrationOnly(std.adm_session, std.reg_no, std.reg_date);
fees_receiptMain mstfees = new fees_receiptMain();
fees_receipt fees = new fees_receipt();
fees.sr_number = std.sr_number;
fees.class_id = std.class_id;
fees.section_id = std.std_section_id;
fees.reg_no = std.reg_no;
fees.reg_date = std.reg_date;
mstfees.updateReceipt(fees);
out_standing out_std = new out_standing();
out_std.acc_id = 2;
out_std.outstd_amount = std.fees_amount;
out_standingMain out_stdMain = new out_standingMain();
out_std.sr_number = std.sr_number;
out_std.class_id = std.class_id;
out_stdMain.AddOutStanding(out_std);
query = @"SELECT
acc_id, fees_amount outstd_amount
FROM
mst_fees
WHERE
session = @session AND bl_onetime = 1
AND acc_id not in (2,1)
AND class_id = @class_id";
out_standing out_std_onetime = new out_standing();
out_std_onetime = con.Query <out_standing>(query, new { session = sess.findActive_Session(), class_id = std.class_id }).SingleOrDefault();
if (out_std_onetime != null)
{
out_std_onetime.sr_number = std.sr_number;
out_std_onetime.class_id = std.class_id;
out_stdMain.AddOutStanding(out_std_onetime);
}
out_std.reg_num = std.reg_no;
out_std.dt_date = std.reg_date;
out_std.class_id = std.class_id;
out_stdMain.updateOutstanding(out_std);
var p = new DynamicParameters();
#if !DEBUG
SMSMessage sms = new SMSMessage();
foreach (var item in sms.smsbody("admission"))
{
string body = item.Replace("#student_name#", std.std_first_name + " " + std.std_last_name);
body = body.Replace("#class#", std.std_admission_class);
body = body.Replace("#sr_number#", std.sr_number.ToString());
await sms.SendSMS(body, std.std_contact, true);
}
#endif
//string text = @"Admission of " + std.std_first_name + " " + std.std_last_name + " is confirmed in class " + std.std_admission_class + " via admission number " + std.sr_number + ". Congratulation for being a part of hariti family. Thank You. Hariti Public School.";
//sms.SendSMS(text, std.std_contact);
// text = std.std_first_name + " " + std.std_last_name + " का प्रवेश कक्षा "+std.std_admission_class+" में होना सुनिश्चित हुआ है। जिसका प्रवेश क्रमांक " + std.sr_number + " है। हरिति परिवार से जुड़ने के लिये आपका धन्यवाद। Hariti Public School.";
//sms.SendSMS(text, std.std_contact);
p.Add("@sr_num", std.sr_number);
con.Execute("MonthlyFeesFullYear", p, commandType: System.Data.CommandType.StoredProcedure);
con.Execute("MonthlyTransportFullYear", p, commandType: System.Data.CommandType.StoredProcedure);
DashboardHub hub = new DashboardHub();
hub.DashboardSchoolStrength();
}
}
catch (Exception ex)
{
throw ex;
}
}
public void AddSection(mst_section mst)
{
try
{
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
mst_sessionMain sess = new mst_sessionMain();
string session = sess.findActive_Session();
string duplicate = @"SELECT
COUNT(*)
FROM
mst_section
WHERE
class_id = @class_id
AND section_name = @section_name
AND session = (SELECT
session
FROM
mst_session
WHERE
session_active = 'Y')";
int dup = con.ExecuteScalar <int>(duplicate, new { mst.class_id, mst.Section_name });
if (dup > 0)
{
throw new DuplicateWaitObjectException();
}
else
{
string query = "INSERT INTO mst_section(section_id,class_id,section_name,session) VALUES (@section_id,@class_id,@section_name,@session)";
string maxid = @"SELECT
IFNULL(MAX(section_id), 0) + 1
FROM
mst_section
WHERE
session = @session";
// var id = con.Query<mst_section>(maxid).ToString().Trim();
int id = con.ExecuteScalar <int>(maxid, new { session = session });
if (id == 1)
{
id = 100;
}
mst.section_id = id;
mst.Section_name = mst.Section_name.Trim();
con.Execute(query, new
{
mst.section_id,
mst.class_id,
mst.Section_name,
session = session
});
}
}
}
catch (Exception ex)
{
throw ex;
}
}