public async Task finalize_attendance(List <attendance_register> attendance, bool send_sms) { using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString())) { string query = @"UPDATE attendance_register SET `attendance` = @attendance, `finalize` = 1 WHERE `session` = @session AND `att_date` = @att_date and sr_num = @sr_num"; foreach (attendance_register att in attendance) { await con.ExecuteAsync(query, new { att.session, att.att_date, att.attendance, att.sr_num }); #if !DEBUG if (!att.attendance && send_sms) { sr_register std = new sr_register(); string phone_query = @"SELECT COALESCE(std_contact, std_contact1, std_contact2) std_contact, CONCAT(IFNULL(std_first_name, ''), ' ', IFNULL(std_last_name, ' ')) std_first_name FROM sr_register WHERE sr_number = @sr_number"; std = con.Query <sr_register>(phone_query, new { sr_number = att.sr_num }).SingleOrDefault(); SMSMessage sms = new SMSMessage(); foreach (var item in sms.smsbody("absent")) { string body = item.Replace("#student_name#", std.std_first_name); body = body.Replace("#current_date#", att.att_date.ToString("dd/MM/yyyy")); await sms.SendSMS(body, std.std_contact, true); } } #endif } } }
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; } }
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; } }