public async Task AddRegistration(std_registration std)
{
try
{
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
mst_fin fin = new mst_fin();
string query1 = @"SELECT fin_id
,fin_start_date
,fin_end_date
,fin_close
FROM mst_fin
where fin_close = 'N'";
fin = con.Query <mst_fin>(query1).SingleOrDefault();
if (std.reg_date > fin.fin_start_date && std.reg_date < fin.fin_end_date)
{
string sess = @"SELECT
session
FROM
mst_session
WHERE
session_active = 'Y'";
string session = con.ExecuteScalar <string>(sess);
string maxid = @"SELECT
IFNULL(MAX(reg_no), 0) + 1
FROM
sr_register
WHERE
adm_session = @adm_session";
int id = con.Query <int>(maxid, new { adm_session = session }).SingleOrDefault();
string max = @"SELECT
IFNULL(MAX(reg_no), 0) + 1
FROM
std_registration
WHERE
session = @adm_session";
int id1 = con.Query <int>(max, new { adm_session = session }).SingleOrDefault();
string query = @"INSERT INTO std_registration
(session
,reg_no
,reg_date
,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_class_id)
VALUES
(@session
,@reg_no
,@reg_date
,@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_class_id)";
std.session = session;
if (id1 < id)
{
std.reg_no = id;
}
else
{
std.reg_no = id1;
}
std.reg_date = System.DateTime.Now.AddMinutes(dateTimeOffSet);
await con.ExecuteAsync(query,
new
{
std.session
,
std.reg_no
,
std.reg_date
,
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_class_id
});
out_standing out_std = new out_standing();
out_std.acc_id = 1;
out_std.outstd_amount = std.fees_amount;
out_std.reg_num = std.reg_no;
out_standingMain out_stdMain = new out_standingMain();
out_stdMain.AddOutStanding(out_std);
#if !DEBUG
SMSMessage sms = new SMSMessage();
foreach (var item in sms.smsbody("student_registration"))
{
string qry = @"SELECT
class_name
FROM
mst_class
WHERE
class_id = @class_id
AND session = (SELECT
session
FROM
mst_session
WHERE
session_active = 'Y')";
string className = con.Query <string>(qry, new { class_id = std.std_class_id }).SingleOrDefault();
string body = item.Replace("#student_name#", std.std_first_name + " " + std.std_last_name);
body = body.Replace("#class#", className);
await sms.SendSMS(body, std.std_contact, true);
}
//string text = std.std_first_name+" "+ std.std_last_name+" is successfully registered in class "+ className + @". This registration is valid for 3 days subject to availability of seats. Thank You. Hariti Public School.";
//sms.SendSMS(text, std.std_contact);
// text = std.std_first_name + " " + std.std_last_name + " का पंजीकरण कक्षा " + className + " में सफलतापूर्वक हो गया है। यह पंजीकरण 3 दिन तक मान्य रहेगा। कक्षा में सीटों की उपलब्धता सीमित हैं। धन्यवाद। Hariti Public School";
//sms.SendSMS(text, std.std_contact);
#endif
}
}
}
catch (Exception ex)
{
throw ex;
}
}
public int AddReceipt(List <fees_receipt> fees)
{
string phone;
int sr_num = 0;
decimal amount = 0;
string flag = "";
try
{
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
out_standingMain out_main = new out_standingMain();
out_standing ot_std = new out_standing();
mst_finMain fin = new mst_finMain();
if (fin.checkFYnotExpired())
{
string fin_id = fin.FindActiveFinId();
string query1 = "";
string maxid = "select ifnull(MAX(receipt_no),0)+1 from fees_receipt where fin_id = @fin_id";
int rect_no = con.Query <int>(maxid, new { fin_id = fin_id }).SingleOrDefault();
DateTime rect_date = System.DateTime.Now.AddMinutes(dateTimeOffSet);
con.Open();
MySqlCommand myCommand = con.CreateCommand();
MySqlTransaction myTrans;
myTrans = con.BeginTransaction();
myCommand.Connection = con;
myCommand.Transaction = myTrans;
string sess = "";
decimal total_amount = 0;
try
{
foreach (fees_receipt fee in fees)
{
total_amount = total_amount + fee.amount;
}
foreach (fees_receipt fee in fees)
{
query1 = @"SELECT
IFNULL(outstd_amount, 0) - IFNULL(rmt_amount, 0)
FROM
out_standing
WHERE
serial = @serial AND session = @session";
decimal dues = con.Query <decimal>(query1, new { serial = fee.serial, session = fee.session }).SingleOrDefault();
if (dues != fee.due_amount)
{
rect_no = 0;
myTrans.Rollback();
throw new System.InvalidOperationException("trying to update double entry in a single out_standing serial");
}
if (fee.fin_id == null)
{
fee.fin_id = fin_id;
}
if (fee.class_id == 0)
{
query1 = @"SELECT
section_id
FROM
mst_std_section
WHERE
sr_num = @sr_number
AND session = @session";
int id = con.Query <int>(query1, new { sr_number = fee.sr_number, session = fee.session }).SingleOrDefault();
fee.section_id = id;
query1 = @"SELECT
class_id
FROM
mst_std_class
WHERE
sr_num = @sr_number
AND session = @session";
id = con.Query <int>(query1, new { sr_number = fee.sr_number, session = fee.session }).SingleOrDefault();
fee.class_id = id;
}
fee.receipt_no = rect_no;
fee.receipt_date = rect_date;
fee.dt_date = rect_date;
string reg_date;
string chq_date;
if (fee.reg_date == DateTime.MinValue)
{
reg_date = "null";
}
else
{
reg_date = String.Format("'{0}'", fee.reg_date.Value.ToString("yyyy-MM-dd"));
}
if (fee.chq_date == DateTime.MinValue)
{
chq_date = "null";
}
else
{
chq_date = String.Format("'{0}'", fee.chq_date.Value.ToString("yyyy-MM-dd"));
}
ot_std.serial = fee.serial;
ot_std.rmt_amount = fee.amount;
ot_std.receipt_no = fee.receipt_no;
ot_std.receipt_date = fee.receipt_date;
ot_std.session = fee.session;
ot_std.dt_date = fee.dt_date;
ot_std.clear_flag = fee.clear_flag;
ot_std.month_no = fee.month_no;
ot_std.dc_fine = fee.dc_fine;
ot_std.dc_discount = fee.dc_discount;
out_main.updateOutstandingReceipt(ot_std, myCommand);
string query = String.Format(@"INSERT INTO fees_receipt
(fin_id
,session
,receipt_no
,receipt_date
,acc_id
,fees_name
,sr_number
,class_id
,section_id
,amount
,reg_no
,reg_date
,dc_fine
,dc_discount
,narration
,serial
,dt_date
,bnk_name
,chq_no
,chq_date
,mode_flag
,clear_flag
,user_id
,secret_code)
VALUES
('{0}'
,'{1}'
,{2}
,'{3}'
,{4}
,'{5}'
,{6}
,{7}
,{8}
,{9}
,{10}
,{11}
,{12}
,{13}
,'{14}'
,{15}
,'{16}'
,'{17}'
,'{18}'
,{19}
,'{20}'
,{21}
,{22},reverse(concat(unix_timestamp(),'-',base64_encode({2}),'-',base64_encode({6}),'-',base64_encode({23}))));", fee.fin_id,
fee.session,
fee.receipt_no,
fee.receipt_date.ToString("yyyy-MM-dd"),
fee.acc_id,
fee.fees_name,
fee.sr_number,
fee.class_id,
fee.section_id,
fee.amount,
fee.reg_no,
reg_date,
fee.dc_fine,
fee.dc_discount,
fee.narration,
fee.serial,
fee.dt_date.ToString("yyyy-MM-dd"),
fee.bnk_name,
fee.chq_no,
chq_date,
fee.mode_flag,
fee.clear_flag,
fee.user_id,
total_amount);
myCommand.CommandText = query;
myCommand.ExecuteNonQuery();
sr_num = fee.sr_number;
sess = fee.session;
amount = amount + fee.amount;
amount = amount + fee.dc_fine;
amount = amount - fee.dc_discount;
flag = fee.mode_flag;
}
myTrans.Commit();
query1 = @"SELECT
std_email
FROM
sr_register
WHERE
sr_number = @sr_number";
string email_id = con.Query <string>(query1, new { sr_number = sr_num }).SingleOrDefault();
if (email_id != null && email_id.Trim() != "")
{
query1 = @"SELECT
sr_number num,
CONCAT(ifnull(std_first_name,''), ' ',ifnull(std_last_name,'')) name,
std_father_name father_name,
CONCAT(c.class_name, ' Sec. ', b.section_name) class_name
FROM
sr_register a,
mst_section b,
mst_class c,
mst_std_section d,
mst_std_class e
WHERE
d.section_id = b.section_id
AND b.class_id = c.class_id
AND e.class_id = b.class_id
AND a.sr_number = d.sr_num
AND d.sr_num = e.sr_num
AND a.sr_number = @sr_number
AND b.session = c.session
AND c.session = d.session
AND d.session = e.session
AND e.session = @session";
var rep = con.Query <rep_fees>(query1, new { sr_number = sr_num, session = sess }).SingleOrDefault();
query1 = @"SELECT
mode_flag,
chq_no,
chq_date,
receipt_date,
fees_name,
sr_number,
class_id,
amount fees,
dc_fine fine,
dc_discount discount,
amount + dc_fine - dc_discount amount,
reg_no,
reg_date,
session
FROM
fees_receipt
WHERE
fin_id = (SELECT
fin_id
FROM
mst_fin
WHERE
fin_close = 'N')
AND receipt_no = @receipt_no";
IEnumerable <fees_receipt> result = con.Query <fees_receipt>(query1, new { receipt_no = rect_no });
string fees_Table = "";
decimal total = 0;
foreach (var fee in result)
{
fees_Table = fees_Table + @" <tr>
<td>
<div style='border-radius:2px;font-size:12px;color:#999;float:left;width:100%;border:solid 1px #f4f4f4'>
<ul style='list-style:none;width:96%;float:left;border-bottom:1px solid #e2e2e2;padding:2%;margin:0'>
<li style='float:left;width:40%;text-align:center;margin-left: 0px;'>
" + fee.fees_name + @"
</li>
<li style='float:left;width:15%;text-align:center;margin-left: 0px;'>
" + fee.fees + @"
</li>
<li style='float:left;width:15%;text-align:center;margin-left: 0px;'>
" + fee.fine + @"
</li>
<li style='float:left;width:15%;text-align:center;margin-left: 0px;'>
" + fee.discount + @"
</li>
<li style='float:left;width:15%;text-align:center;margin-left: 0px;'>
" + fee.amount + @"
</li>
</ul>
</div>
</td>
</tr>";
total = total + fee.amount;
}
string Affiliation = ConfigurationManager.AppSettings["Affiliation"].ToString();
string SchoolName = ConfigurationManager.AppSettings["SchoolName"].ToString();
string body = @"<div style='width:584px;margin:0 auto;border:#ececec solid 1px'>
<div style='padding:22px 34px 15px 34px'>
<div style='float:left'><img title='Institute Logo' src='" + HttpContext.Current.Request.Url.GetLeftPart(UriPartial.Authority) + @"/images/logo.jpg' alt='Institute Logo' width='100px' class='CToWUd'></div>
<div style='float:right; font:bold 30px Arial,Helvetica,sans-serif;margin-top:20px'>" + SchoolName + @"</div>
<div style='float:right; font:12px Arial,Helvetica,sans-serif;margin-top:5px'>(" + Affiliation + @")</div>
</div>
<div style='clear:both'></div>
<div style='float:left;color:#333333;font:normal 14px Arial,Helvetica,sans-serif;width:250px'>
<!--<div>Order no: #8846417200 <br> 2019-07-27T07:13:42.000Z </div>-->
<div style='padding-top:10px'></div>
</div>
<div style='clear:both'> </div>
</div>
<div style='width:584px;background-color:#ffffff;border:#e8e7e7 solid 1px;padding:27px 0;margin:0 auto;border-bottom:0'>
<div style='border-bottom:#717171 dotted 1px;font:normal 14px Arial,Helvetica,sans-serif;color:#666666;padding:0px 33px 10px'>
<table style='width:100%' border='0' cellspacing='0' cellpadding='2'>
<tbody>
<tr>
<td width='450px'>Receipt No:</td>
<td width='450px'>" + rect_no + @"</td>
<td width='450px'>Receipt Date:</td>
<td width='450px'>" + result.First().receipt_date.ToString("dd-MMM-yyyy") + @"</td>
</tr>
<tr>
<td width='450px'>Admission No:</td>
<td width='450px'>" + rep.num + @"</td>
<td width='450px'>Class:</td>
<td width='450px'>" + rep.class_name + @"</td>
</tr>
<tr>
<td width='450px'>Name:</td>
<td width='450px'>" + rep.name + @"</td>
</tr>
<tr>
<td width='450px'>Father's Name: </td>
<td width='450px'>" + rep.father_name + @"</td>
</tr>
</tbody>
</table>
</div>
<div style='border-bottom:#717171 dotted 1px;font:normal 14px Arial,Helvetica,sans-serif;color:#666666;padding:10px 33px 10px'>
<br>
<table style='width:100%' border='0' cellspacing='0' cellpadding='2'>
<tbody>
<tr>
<td>
<div style='border-radius:2px;font-size:12px;color:#999;float:left;width:100%;border:solid 1px #f4f4f4'>
<ul style='list-style:none;width:96%;float:left;border-bottom:1px solid #e2e2e2;padding:2%;margin:0'>
<li style='float:left;width:40%;text-align:center;margin-left: 0px;'>
Particulars
</li>
<li style='float:left;width:15%;text-align:center;margin-left: 0px;'>
Fees
</li>
<li style='float:left;width:15%;text-align:center;margin-left: 0px;'>
Fine
</li>
<li style='float:left;width:15%;text-align:center;margin-left: 0px;'>
Discount
</li>
<li style='float:left;width:15%;text-align:center;margin-left: 0px;'>
Paid
</li>
</ul>
</div>
</td>
</tr>
" + fees_Table + @"
</tbody>
</table>
<div style='border-bottom:#717171 dotted 1px;font:600 14px Arial,Helvetica,sans-serif;color:#333333;padding:17px 33px 17px'>
<table style='width:100%' border='0' cellspacing='0' cellpadding='2'>
<tbody>
<tr>
<td width='450px'>Amount Paid</td>
<td width='313px' style='text-align:right'>Rs." + total + @"</td>
</tr>
</tbody>
</table>
</div>
</div>
<div style='border-bottom:#717171 dotted 1px;font:normal 14px Arial,Helvetica,sans-serif;color:#666666;padding:10px 33px 10px'>
<br>
<table style='width:100%' border='0' cellspacing='0' cellpadding='2'>
<tbody>
<tr>
<td colspan='2'>
Received with thanks:
</td>
<td colspan='2'>
" + repFees_receipt.NumbersToWords(Decimal.ToInt32(total)) + @"
</td>
</tr>
</tbody>
</table>
</div>
</div>
<div style='margin:0 auto;width:594px'><img title='' src='" + HttpContext.Current.Request.Url.GetLeftPart(UriPartial.Authority) + @"/images/fee_receipt.png' alt='' class='CToWUd'></div>";
repFees_receipt rep_byte = new repFees_receipt();
byte[] bytes = rep_byte.pdf_bytes(rect_no, result.First().receipt_date);
if (total != 0)
{
_sendMail(bytes, email_id, rect_no.ToString(), result.First().receipt_date.ToString("dd-MMM-yyyy"), body);
}
}
}
catch (Exception e)
{
try
{
rect_no = 0;
myTrans.Rollback();
}
catch (MySqlException ex)
{
if (myTrans.Connection != null)
{
Console.WriteLine("An exception of type " + ex.GetType() +
" was encountered while attempting to roll back the transaction.");
}
}
Console.WriteLine("An exception of type " + e.GetType() +
" was encountered while inserting the data.");
Console.WriteLine("Neither record was written to database.");
}
finally
{
con.Close();
}
#if !DEBUG
query1 = @"select coalesce(std_contact, std_contact1, std_contact2) from sr_register where sr_number = @sr_number";
phone = con.Query <string>(query1, new { sr_number = sr_num }).SingleOrDefault();
if (phone != null && amount != 0)
{
query1 = @"SELECT concat(std_first_name,' ',std_last_name)
FROM sr_register
where sr_number = @sr_number";
string name = con.Query <string>(query1, new { sr_number = sr_num }).SingleOrDefault();
if (flag == "Cash")
{
string dt = DateTime.Now.AddMinutes(dateTimeOffSet).ToString();
SMSMessage sms = new SMSMessage();
foreach (var item in sms.smsbody("cash_fees_submit"))
{
string body = item.Replace("#student_name#", name);
body = body.Replace("#current_date#", dt);
body = body.Replace("#fees_amount#", amount.ToString());
sms.SendSMS(body, phone, true);
}
//string dt = DateTime.Now.AddMinutes(dateTimeOffSet).ToString();
//SMSMessage sms = new SMSMessage();
//string text = @"School fees of " + name + " INR " + amount + " is successfully submitted on " + dt + ". Thank you for your cooperation. Hariti Public School";
//sms.SendSMS(text, phone);
//SMSMessage sms1 = new SMSMessage();
//string text1 = name + @" की स्कूल फीस INR " + amount + " दिनांक " + dt + " को सफलतापूर्वक जमा हो चुकी है। आपके सहयोग के लिए धन्यवाद। Hariti Public School";
//sms1.SendSMS(text1, phone);
}
else
{
string dt = DateTime.Now.AddMinutes(dateTimeOffSet).ToString();
SMSMessage sms = new SMSMessage();
foreach (var item in sms.smsbody("bank_fees_submit"))
{
string body = item.Replace("#student_name#", name);
body = body.Replace("#current_date#", dt);
body = body.Replace("#fees_amount#", amount.ToString());
sms.SendSMS(body, phone, true);
}
//string dt = DateTime.Now.AddMinutes(dateTimeOffSet).ToString();
//SMSMessage sms = new SMSMessage();
//string text = @"School fees of " + name + " INR " + amount + " is successfully submitted on " + dt + ". Subject to Bank Clearance. Thank you for your cooperation. Hariti Public School";
//sms.SendSMS(text, phone);
//SMSMessage sms1 = new SMSMessage();
//string text1 = name + @" की स्कूल फीस INR " + amount + " दिनांक " + dt + " को सफलतापूर्वक जमा हो चुकी है। राशि बैंक निकासी के अधीन है। आपके सहयोग के लिए धन्यवाद। Hariti Public School";
//sms1.SendSMS(text1, phone);
}
}
DashboardHub dash = new DashboardHub();
dash.DailyFeesUpdate();
#endif
return(rect_no);
}
else
{
throw new System.InvalidOperationException("Financial Year Expired");
}
}
}
catch (Exception ex)
{
throw ex;
}
}
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 Updatefees_Bounce(uncleared_cheque unclear)
{
try
{
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
string query = @"UPDATE fees_receipt
SET
chq_reject = @chq_reject,
nt_clear_reason = @narration,
clear_flag = 1
WHERE
bnk_name = @bnk_name
AND chq_date = DATE_FORMAT(@chq_date, '%Y-%m-%d')
AND chq_no = @chq_no
AND clear_flag = 0";
con.Execute(query, unclear);
// fees_receipt fee = new fees_receipt();
query = @"SELECT DISTINCT
serial, session, amount, dc_fine, dc_discount
FROM
fees_receipt
WHERE
bnk_name = @bnk_name
AND chq_date = DATE_FORMAT(@chq_date, '%Y-%m-%d')
AND chq_no = @chq_no";
var result = con.Query <uncleared_cheque>(query, new { bnk_name = unclear.bnk_name, chq_date = unclear.chq_date, chq_no = unclear.chq_no });
foreach (uncleared_cheque val in result)
{
query = @"UPDATE out_standing
SET
rmt_amount = rmt_amount - @rmt_amount,
dc_fine = dc_fine - @dc_fine,
dc_discount = dc_discount - @dc_discount
WHERE
serial = @serial AND session = @session";
con.Execute(query, new { rmt_amount = val.amount, dc_fine = val.dc_fine, dc_discount = val.dc_discount, serial = val.serial, session = val.session });
}
if (unclear.bnk_charges != 0)
{
query = @"SELECT DISTINCT
sr_number, session, reg_no, class_id
FROM
fees_receipt
WHERE
bnk_name = @bnk_name
AND chq_date = DATE_FORMAT(@chq_date, '%Y-%m-%d')
AND chq_no = @chq_no";
result = con.Query <uncleared_cheque>(query, new { bnk_name = unclear.bnk_name, chq_date = unclear.chq_date, chq_no = unclear.chq_no });
out_standing std = new out_standing();
out_standingMain outstd = new out_standingMain();
std.acc_id = 3;
std.clear_flag = false;
std.outstd_amount = (unclear.bnk_charges) / result.Count();
foreach (uncleared_cheque val in result)
{
std.sr_number = val.sr_number;
std.reg_num = val.reg_no;
std.class_id = val.class_id;
std.session = val.session;
outstd.AddOutStanding(std);
}
#if !DEBUG
if (unclear.chq_reject == "Bounce")
{
query = @"select coalesce(std_contact, std_contact1, std_contact2) from sr_register where sr_number = @sr_number";
string phone = con.Query <string>(query, new { sr_number = std.sr_number }).SingleOrDefault();
SMSMessage sms = new SMSMessage();
foreach (var item in sms.smsbody("cheque_bounce"))
{
string body = item.Replace("#cheque_number#", unclear.chq_no);
body = body.Replace("#bounce_charge#", unclear.bnk_charges.ToString());
await sms.SendSMS(body, phone, true);
}
}
#endif
}
}
}
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;
}
}
