private void btnSave11_Click(object sender, EventArgs e)
{// Format entered phone number to international format
string phone = txtPhone.Text;
string first_xter = phone.Substring(0, 1);
if (first_xter == "0")
{
phone = "+254" + phone.Remove(0, 1);
}
// Check to see if the student already exist or duplicate phone number // Put this later #" OR PhoneNo= '" + phone + "'#
try
{
adapter = new SqlDataAdapter("select * from tblStudents WHERE AdmNo= " + txtAdmNo.Text + "", con);
ds = new DataSet(); adapter.Fill(ds, "tblStudents");
if (ds.Tables[0].Rows.Count > 0)
{
this.Alert("A student with this Admission and/ or Phone Number already Exists!!", SDRS.Alert.alertTypeEnum.Error);
}
// If the student is not registered/ phone number not used then proceed to register
else
{
try
{
cmd = new SqlCommand("INSERT into tblStudents(AdmNo,Name,Form,Stream,DOB,Gender,BillGroup,Photo,ParentName,PhoneNo,Address,Relation,CurrentTerm) values(" + txtAdmNo.Text + ",'" + txtStdName.Text + "'," + cbxForm.Text + ",'" + cbxStream.Text + "','" + DateTime.Parse(dpDOB.Text) + "'," +
"'" + cbxGender.Text + "','" + cbxBillGroup.Text + "',@photo, '" + txtPrtName.Text + "','" + phone + "','" + txtAddress.Text + "','" + cbxRelation.Text + "',1)", con);
conv_photo();
con.Open();
int n = cmd.ExecuteNonQuery();
con.Close();
if (n > 0)
{
this.Alert("Student Registered Successfully!", SDRS.Alert.alertTypeEnum.Success);
loaddata();
setInitialMark();
setInitialPay();
// Send SMS to Parent informing of successful student admission
frmDashb smpf = new frmDashb();
smpf.AutoSMS("Dear " + txtPrtName.Text + ", " + txtStdName.Text + " has been successfully admitted to Form " + cbxForm.Text + "" + cbxStream.Text + ". You will be required to provide Admission Number " + txtAdmNo.Text + " everytime you want to access Student data. We are glad to have you at Singoronik Secondary School. ^BN", txtPhone.Text);
}
else
{
this.Alert("Unable to register Student!", SDRS.Alert.alertTypeEnum.Warning);
}
}
catch (Exception ex)
{
this.Alert("Unable to perform Insertion Operation!!", SDRS.Alert.alertTypeEnum.Error);
// MessageBox.Show(ex.Message);
con.Close();
}
}
}
catch (Exception ex)
{
this.Alert("Unable to perform Query Operation!!", SDRS.Alert.alertTypeEnum.Error);
}
}
private void btnLogin_Click(object sender, EventArgs e)
{
frmDashb db = new frmDashb();
db.Show();
this.Close();
db.labelUser = "******";
db.labelRole = "Admin";
// getUsers();
}
void getUsers()
{
string user = txtUser.Text;
string pass = txtPass.Text;
try
{
adapter = new SqlDataAdapter("SELECT Username, Role from tblUsers WHERE Username= '******' AND Password= '******' AND Status= 'Access'", con);
ds = new DataSet(); adapter.Fill(ds, "Users");
if (ds.Tables[0].Rows.Count > 0)
{
// display on input controls
string uname = ds.Tables[0].Rows[rno][0].ToString();
string role = ds.Tables[0].Rows[rno][1].ToString();
if (role == "Admin")
{
frmDashb db = new frmDashb();
db.Show();
this.Close();
db.labelUser = uname;
db.labelRole = role;
}
else if (role == "Exams")
{
frmDashb db = new frmDashb();
db.Show();
this.Close();
db.labelUser = uname;
db.labelRole = role;
db.UpdateExam(true);
}
else if (role == "Fees")
{
frmDashb db = new frmDashb();
db.Show();
this.Close();
db.labelUser = uname;
db.labelRole = role;
db.UpdateFees(true);
}
}
else
{
MessageBox.Show("Incorrect Login Credentials or Access Denied!", "User Login", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
catch (Exception ex)
{
MessageBox.Show("Login Failed!", "User Login", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public void PullExam(string phone, int adm)
{
// Search student to pay Fees
try
{
adapter = new SqlDataAdapter("SELECT* FROM tblResult,(SELECT PhoneNO FROM tblStudents WHERE AdmNo=" + adm + ") AS number, (SELECT Stream FROM tblStudents WHERE AdmNo= " + adm + ") AS Stream WHERE AdmNo= " + adm + "", con);
ds = new DataSet();
adapter.Fill(ds, "Results");
if (ds.Tables[0].Rows.Count > 0)
{
string name = ds.Tables[0].Rows[rno][2].ToString();
string exam = ds.Tables[0].Rows[rno][3].ToString();
string term = ds.Tables[0].Rows[rno][5].ToString();
string stream = ds.Tables[0].Rows[rno][11].ToString();
string pno = ds.Tables[0].Rows[rno][10].ToString();
string grade = ds.Tables[0].Rows[rno][8].ToString();
int pos = Convert.ToInt32(ds.Tables[0].Rows[rno][9]);
int form = Convert.ToInt32(ds.Tables[0].Rows[rno][4]);
int mark = Convert.ToInt32(ds.Tables[0].Rows[rno][6]);
if (pno == phone)
{
string score = setResult(adm);
string s = exam + ", " + term + " NAME: " + name + " " + "ADMNO: " + adm + " " + "Form: " + form.ToString() + stream + " " + score + "MEAN GRADE: " + grade + " " + "MEAN MARK: " + mark.ToString() + "%" + " " + "POS:" + pos.ToString();
frmDashb smspe = new frmDashb();
smspe.AutoSMS(s, phone);
//Thread.Sleep(3000);
}
else
{
frmDashb dsm = new frmDashb();
dsm.AutoSMS("Failed. The details you provided does not match our records. Please ensure you provide the correct Admission number", phone);
}
//MessageBox.Show(s);
}
// If the student is not found
else
{
frmDashb ds = new frmDashb();
ds.AutoSMS("Failed. We could not process that request! Please try again later or contact 0707142565 for assistance", phone);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + "Pull Exam");
this.Alert("Could Perform query Operation" + ex.Message, SDRS.Alert.alertTypeEnum.Warning);
}
}
void insertDB()
{
SqlConnection conn = new SqlConnection("Data Source=ENGINEERNGENO;Initial Catalog=SDRS;Integrated Security=True");
SqlCommand comm;
for (int i = 0; i < dgvViewStudents.Rows.Count; i++)
{
int admno = Convert.ToInt32(dgvViewStudents.Rows[i].Cells["AdmNo"].Value);
int Form = Convert.ToInt32(dgvViewStudents.Rows[i].Cells["Form"].Value);
int CTerm = Convert.ToInt32(dgvViewStudents.Rows[i].Cells["CurrentTerm"].Value);
string name = dgvViewStudents.Rows[i].Cells["Name"].Value.ToString();
string Stream = dgvViewStudents.Rows[i].Cells["Stream"].Value.ToString();
string DOB = dgvViewStudents.Rows[i].Cells["DOB"].Value.ToString();
string Gender = dgvViewStudents.Rows[i].Cells["Gender"].Value.ToString();
string BillGroup = dgvViewStudents.Rows[i].Cells["BillGroup"].Value.ToString();
string ParentName = dgvViewStudents.Rows[i].Cells["ParentName"].Value.ToString();
string PhoneNo = "+254" + dgvViewStudents.Rows[i].Cells["PhoneNo"].Value.ToString();
string Address = dgvViewStudents.Rows[i].Cells["Address"].Value.ToString();
string Relation = dgvViewStudents.Rows[i].Cells["Relation"].Value.ToString();
string StrQuery = @"INSERT INTO tblStudents(AdmNo,Name,Form,Stream,DOB,Gender,BillGroup,ParentName,PhoneNo,Address,Relation,CurrentTerm) VALUES (" + admno + ", '" + name + "'," + Form + "," + "'" + Stream + "','" + DOB + "', '" + Gender + "','" + BillGroup + "','" + ParentName + "','" + PhoneNo + "','" + Address + "','" + Relation + "'," + CTerm + ");";
try
{
using (conn)
{
using (comm = new SqlCommand(StrQuery, conn))
{
conn.Open();
comm.ExecuteNonQuery();
frmDashb sm = new frmDashb();
sm.AutoSMS("Dear " + ParentName + ", " + name + " has been successfully admitted to Form " + Form + "" + Stream + ". You will be required to provide Admission Number " + admno + " everytime you want to access Student data. We are glad to have you at Singoronik Secondary School. ^BN", PhoneNo);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
//this.Alert("Fatal Error occurred" + ex.Message, SDRS.Alert.alertTypeEnum.Error);
}
}
conn.Close();
this.Alert("Mass Data Insertion Completed", SDRS.Alert.alertTypeEnum.Success);
}
public void PullFeesBal(string phone, int adm)
{
// Search student to pay Fees
try
{
adapter = new SqlDataAdapter("SELECT (SELECT Name FROM tblStudents WHERE AdmNo=" + adm + ") AS Name, (SELECT PhoneNO FROM tblStudents WHERE AdmNo=" + adm + ") AS number,Balance FROM tblPayment WHERE PaymentID= (select MAX(PaymentID) FROM tblPayment WHERE AdmNo = " + adm + ")", con);
ds = new DataSet(); adapter.Fill(ds, "student");
if (ds.Tables[0].Rows.Count > 0)
{
string name = ds.Tables[0].Rows[rno][0].ToString();
string pno = ds.Tables[0].Rows[rno][1].ToString();
int Bal = Convert.ToInt32(ds.Tables[0].Rows[rno][2]);
if (pno == phone)
{
string s = "Current Fees Balance for " + name + "," + adm + " " + "is KES" + Bal + ".00" + ". Thank you for being part of Singoronik Secondary School. ^BN";
frmDashb sms = new frmDashb();
sms.AutoSMS(s, phone);
//Thread.Sleep(3000);
}
else
{
frmDashb dsm = new frmDashb();
dsm.AutoSMS("Failed. The details you provided does not match our records. Please ensure you provide the correct Admission number", phone);
}
// MessageBox.Show(s);
}
// If the student is not found
else
{
frmDashb smsb = new frmDashb();
smsb.AutoSMS("Failed. We could not process that request! Please try again later or contact 0707142565 for assistance. ^BN", phone);
}
//this.Alert("A student with this Admission Number does not Exist!", SDRS.Alert.alertTypeEnum.Error);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + "Pull Fees Balance");
this.Alert("Could Perform query Operation" + ex.Message, SDRS.Alert.alertTypeEnum.Warning);
}
}
// Rank Student and update the POS column
public void PushExam()
{
try
{
adapter = new SqlDataAdapter("SELECT* FROM tblResult", con);
ds = new DataSet();
adapter.Fill(ds, "Results");
adapter.Fill(dt);
if (ds.Tables[0].Rows.Count > 0)
{
// Open Connection and perform the insert until the end of the rows
// con.Open();
foreach (DataRow row in dt.Rows)
{
int pos = Convert.ToInt32(row["POS"]);
int admno = Convert.ToInt32(row["AdmNo"]);
string nme = row["Name"].ToString();
string tm = row["Term"].ToString();
string Exm = row["Exam"].ToString();
int Form = Convert.ToInt32(row["Form"]);
int mrk = Convert.ToInt32(row["Score"]);
string phone = row["PhoneNo"].ToString();
string mg = row["MeanGrade"].ToString();
string score = setResult(admno);
string s = Exm + ", " + tm + " NAME: " + nme + " " + "ADMNO: " + admno + " " + "Form: " + Form.ToString() + " " + score + "MEAN GRADE: " + mg + " " + "MEAN MARK: " + mrk.ToString() + "%" + " " + "POS:" + pos.ToString();
// string score = setResult(admno);
frmDashb smex = new frmDashb();
smex.AutoSMS(s, phone);
//MessageBox.Show( s);
Thread.Sleep(1000);
}
con.Close();
}
}
catch (Exception ex)
{
this.Alert("Unable to perform Insertion Operation!!", SDRS.Alert.alertTypeEnum.Error);
MessageBox.Show(ex.Message);
}
}
private void btnSave21_Click(object sender, EventArgs e)
{
DateTime dt = new DateTime();
dt = DateTime.Now;
// Perform Fees Updation Calculations
getCurrentBalance();
paid = Convert.ToInt32(txtAmount21.Text);
//int Billed = Convert.ToInt32(txtBilled21.Text);
if (paid > cBalance)
{
prepaid = paid - cBalance;
cBalance = 0;
}
else
{
cBalance = cBalance - paid;
prepaid = 0;
}
try
{
cmd = new SqlCommand("INSERT into tblPayment(AdmNo,TotalAmount,Paid,Date,Balance,Prepayment,PayMode) values(" + txtAdmNo21.Text + "," + txtBilled21.Text + "," + paid + ",'" + DateTime.Parse(dpPayDate.Text) + "'," + cBalance + "," + prepaid + ", '" + cbxMode.Text + "')", con);
con.Open();
int n = cmd.ExecuteNonQuery();
con.Close();
this.Alert("Payment processed", SDRS.Alert.alertTypeEnum.Info);
getStudent();
// Send SMS to the parent of successful payment
frmDashb smpf = new frmDashb();
smpf.AutoSMS("Confirmed. KES" + paid + ".00 received through " + cbxMode.Text + " for " + txtAdmNo21.Text + " on " + dt + ", new Fees Balance is " + cBalance + "", txtMobile21.Text);
}
catch (Exception ex)
{
this.Alert("Unable to Bill Current Student!!", SDRS.Alert.alertTypeEnum.Error);
con.Close();
}
}
public void PullFeeState(string phone, int adm)
{
try
{
adapter = new SqlDataAdapter("SELECT TOP 5 CONCAT(Date,Paid,PayMode)AS statement,(SELECT PhoneNO FROM tblStudents WHERE AdmNo= " + adm + ") AS number FROM tblPayment WHERE AdmNo= " + adm + " AND PayMode !='NULL' ", con);
ds = new DataSet();
adapter.Fill(ds, "States");
var dt = new DataTable();
adapter.Fill(dt);
if (ds.Tables[0].Rows.Count > 0)
{
string pno = ds.Tables[0].Rows[rno][1].ToString();
if (pno == phone)
{
string s = "Fee Statement for" + adm + ": " + string.Join(", | ", dt.Rows.OfType <DataRow>().Select(r => r[0].ToString()));
frmDashb smss = new frmDashb();
smss.AutoSMS(s, phone);
}
else
{
frmDashb dsm = new frmDashb();
dsm.AutoSMS("Failed. The details you provided does not match our records. Please ensure you provide the correct Admission number", phone);
}
}
else
{
frmDashb smsf = new frmDashb();
smsf.AutoSMS("Failed. We could not process that request! Please try again later or contact 0707142565 for assistance", phone);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + "Pull Fees Statement");
}
}
