private void btnDelete_Click(object sender, EventArgs e)
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
try
{
if (MessageBox.Show("Are you sure", "Message", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
// mainDataBaseBindingSource.RemoveCurrent();
if (maindataGridView.SelectedRows.Count > 0) // make sure user select at least 1 row
{
string name = maindataGridView.SelectedRows[6].Cells[0].Value + string.Empty;
conn.Open();
string sqlquery = string.Format("delete " + MainData + " where SchoolId = '" + name + "'");
SqlCommand cmd = new SqlCommand(sqlquery, conn);
cmd.ExecuteNonQuery();
conn.Close();
//clearAll();
display2();
popScreen("Database Alert", "Data has been deleted Successful", "Info");
ffd = new foodCollectionQuery();
ffd.actSQL("Delected Data", DateTime.Now.ToLongTimeString(), date, name);
}
}
}
catch (Exception ex)
{
popScreen("Database Error", ex.Message, "Warn");
}
}
///<summary> Closing and Exporting</summary>
public void Collection_FormClosing()
{
if (!checkBox1.Checked)
{
return;
}
try {
SqlConnection conn = new SqlConnection(Form1.query);
Console.WriteLine("Closing");
// return;
string mealBox = null;
if (radiobreakfast.Checked)
{
mealBox = "Breakfast";
}
if (radiolaunch.Checked)
{
mealBox = "Launch";
}
if (radiosupper.Checked)
{
mealBox = "Supper";
}
if (radioOther.Checked)
{
mealBox = txtOther.Text;
}
string Loc2 = @"C:\Users\" + Environment.UserName + @"\Documents\Food-Manager";
if (!Directory.Exists(Loc2))
{
Directory.CreateDirectory(Loc2);
}
string loc2 = @"C:\Users\" + Environment.UserName + @"\Documents\Food-Manager\" + mealBox + " " + date + ".pdf";
Console.WriteLine(loc2);
ExportDataTableToPdf(loc2, "Adeleke University Ventures \n\n", "Cafeteria Department", mealBox);
conn.Open();
string sql = @"DELETE FROM PrintDB;";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();
conn.Close();
conn.Open();
ffd = new foodCollectionQuery();
ffd.actSQL("Food Collection", DateTime.Now.ToLongTimeString(), date, "Student food Collection | Total: " + checkCount());
// System.Diagnostics.Process.Start(loc);
}
catch (Exception ex) {
MessageBox.Show(ex.Message);
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Open();
}
}
//this.WindowState = System.Windows.Forms.FormWindowState.Minimized;
}
private void upGrade_Click(object sender, EventArgs e)
{
string varOld = null; int varNew = 0; int varUpdate = 0;
try
{
conn = new SqlConnection(Form1.query);
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
string sqlSelectQuery1 = string.Format("SELECT * FROM {0}", MainData);
SqlDataAdapter SDA = new SqlDataAdapter(sqlSelectQuery1, Form1.query);
DataTable dt = new DataTable();
SDA.Fill(dt);
foreach (DataRow dr in dt.Rows)
{
varOld = (dr["IDMain"].ToString());
varNew = Convert.ToInt32(varOld);
try
{
varUpdate = Convert.ToInt32(textMass.Text);
}
catch (Exception ex)
{
popScreen("Database Alert", ex.Message, "Error");
return;
}
varNew = varNew + varUpdate;
string sqlquery = string.Format("Update {0} SET IDMain = '" + varNew.ToString() + "' where SchoolId = '" + (dr["SchoolId"].ToString()) + "'", MainData);
conn.Close();
SqlCommand cmd3 = new SqlCommand(sqlquery, conn);
conn.Open();
cmd3.CommandType = CommandType.Text;
cmd3.ExecuteNonQuery();
}
conn.Close();
display2();
popScreen("Database Alert", "Data has been deleted Successful", "Info");
ffd = new foodCollectionQuery();
ffd.actSQL("Mass Ticket Upgrade", DateTime.Now.ToLongTimeString(), date, "Mass Ticket update of " + textMass.Text);
textMass.Text = "";
}
catch (Exception ex)
{
popScreen("Database Error", ex.Message, "Warn");
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
}
private void transferBtn_Click(object sender, EventArgs e)
{
int senda = 0, tranfer = 0, NewReciever = 0, NewSenda = 0;
string snd = null;
int reciever = 0;
try
{
try
{
tranfer = Int32.Parse(sendTicket.Text);
}
catch (Exception ex)
{
popScreen("Transfer Error", "Invalid Number \n" + ex.Message, "Warn");
return;
}
conn.Open();
string sqlSelectQuery1 = string.Format("SELECT * FROM {0} WHERE SchoolId = '" + fromTxt.Text + "'", MainData);
SqlCommand cmd = new SqlCommand(sqlSelectQuery1, conn);
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
senda = Int32.Parse((dr["IDMain"].ToString()));
snd = (dr["SchoolId"].ToString());
conn.Close();
if (senda == 0 || senda <= tranfer)
{
popScreen("Transfer Error", " Not enongh ticket from Philanthrophist \n Please Verify School ID", "Warn");
return;
}
conn.Open();
string sqlSelectQuery2 = string.Format("SELECT * FROM {0} WHERE SchoolId = '" + ToTxt.Text + "'", MainData);
SqlCommand cmd2 = new SqlCommand(sqlSelectQuery2, conn);
SqlDataReader dr2 = cmd2.ExecuteReader();
if (dr2.Read())
{
reciever = Int32.Parse((dr2["PhiliTicket"].ToString()));
}
else
{
popScreen("Transfer Error", "Invalid Receiver \n Please Verify School ID", "Warn");
conn.Close();
return;
}
conn.Close();
NewReciever = reciever + tranfer;
NewSenda = senda - tranfer;
conn.Open();
string sqlquery = string.Format("Update {0} SET PhiliTicket ='" + NewReciever.ToString() + "', Phili = '" + snd + "' where SchoolId = '" + ToTxt.Text + "'", "MainDataBase");
SqlDataAdapter adapt = new SqlDataAdapter(string.Format("select * from " + MainData + " where SchoolId like '" + ToTxt.Text + "%'"), conn);
cmd = new SqlCommand(sqlquery, conn);
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
conn.Close();
conn.Open();
string sqlquery3 = string.Format("Update {0} SET IDMain ='" + NewSenda.ToString() + "' where SchoolId = '" + fromTxt.Text + "'", "MainDataBase");
SqlDataAdapter adapt2 = new SqlDataAdapter(string.Format("select * from " + MainData + " where SchoolId like '" + fromTxt.Text + "%'"), conn);
cmd2 = new SqlCommand(sqlquery3, conn);
cmd2.CommandType = CommandType.Text;
cmd2.ExecuteNonQuery();
conn.Close();
}
else
{
popScreen("Transfer Error", "Invalid Philanthrophist \n Please Verify School ID", "Warn");
return;
}
Console.WriteLine("Updated");
popScreen("Database Alert", "Database has been updated Successful", "Info");
ffd = new foodCollectionQuery();
ffd.actSQL("Ticket Transfer", DateTime.Now.ToLongTimeString(), date, "Ticket transfer from " + fromTxt.Text + " to " + ToTxt.Text);
clearAll();
display2();
}
catch (Exception ex)
{
popScreen("Transfer Error", ex.Message, "Warn");
return;
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
}
private void btnUpdate_Click(object sender, EventArgs e)
{
try
{
Image img = pictureBox1.Image;
MemoryStream ms = new MemoryStream();
img.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);
byte[] bytes = ms.ToArray();
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
if (txtName.Text.Equals(""))
{
return;
}
string aa = "0", idTemp = "", ab = ""; bool upda = false;
try
{
string paymentQuery = null, hunt = null;
if (radioFalse.Checked)
{
hunt = "False";
}
if (radioTrue.Checked)
{
hunt = "True";
}
conn = new SqlConnection(Form1.query);
if (boxPayment.Text == "Full" && addTicket.Checked)
{
conn.Open();
paymentQuery = string.Format("select * from " + MainData + " where SchoolId like '" + txtMatricNumber.Text + "%'");
SqlCommand cmd = new SqlCommand(paymentQuery, conn);
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
int a = 0;
idTemp = (dr["IDMain"].ToString());
ab = (dr["Payment"].ToString());
a = Convert.ToInt32(idTemp);
if (ab == "Half")
{
conn.Close();
a = a + 4;
aa = a.ToString();
string sqlSelectQuery3 = "UPDATE " + MainData + " SET SName=@SName, OName=@OName, Image=@Image, Course=@Course, UID=@UID, Payment=@Payment, SchoolId=@SchoolId, IDMain=@IDMain, Sex=@Sex, ManHuntP=@ManHuntP WHERE SchoolId = @SchoolId";
using (SqlCommand Command = new SqlCommand(sqlSelectQuery3, conn))
{
Command.Parameters.Add("@SName", SqlDbType.NVarChar, 50).Value = txtName.Text;
Command.Parameters.Add("@OName", SqlDbType.NVarChar, 50).Value = txtOtherNames.Text;
Command.Parameters.Add("@Image", SqlDbType.Image, bytes.Length).Value = bytes;
Command.Parameters.Add("@Course", SqlDbType.NVarChar, 50).Value = boxDepartment.Text;
Command.Parameters.Add("@UID", SqlDbType.NVarChar, 50).Value = txtUID.Text;
Command.Parameters.Add("@Payment", SqlDbType.NVarChar, 10).Value = boxPayment.Text;
Command.Parameters.Add("@SchoolId", SqlDbType.NVarChar, 10).Value = txtMatricNumber.Text;
Command.Parameters.Add("@IDMain", SqlDbType.NVarChar, 10).Value = aa;
Command.Parameters.Add("@Sex", SqlDbType.NVarChar, 10).Value = bxSex.Text;
Command.Parameters.Add("@ManHuntP", SqlDbType.NVarChar, 10).Value = hunt;
conn.Open();
Command.ExecuteNonQuery();
conn.Close();
}
clearAll();
Console.WriteLine("Half");
upda = true;
}
}
}
else if (boxPayment.Text == "Half" && addTicket.Checked)
{
conn.Open();
paymentQuery = string.Format("select * from " + MainData + " where SchoolId like '" + txtOtherNames.Text + "%'");
SqlCommand cmd = new SqlCommand(paymentQuery, conn);
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
int a = 0;
idTemp = (dr["IDMain"].ToString());
ab = (dr["Payment"].ToString());
a = Convert.ToInt32(idTemp);
if (ab == "Full")
{
conn.Close();
a = a + 8;
aa = a.ToString();
string sqlSelectQuery3 = "UPDATE " + MainData + " SET SName=@SName,OName=@OName, Image=@Image, Course=@Course, UID=@UID, Payment=@Payment, SchoolId=@SchoolId, IDMain=@IDMain, Sex=@Sex, ManHuntP=@ManHuntP WHERE SchoolId = @SchoolId";
using (SqlCommand Command = new SqlCommand(sqlSelectQuery3, conn))
{
Command.Parameters.Add("@SName", SqlDbType.NVarChar, 50).Value = txtName.Text;
Command.Parameters.Add("@OName", SqlDbType.NVarChar, 50).Value = txtOtherNames.Text;
Command.Parameters.Add("@Image", SqlDbType.Image, bytes.Length).Value = bytes;
Command.Parameters.Add("@Course", SqlDbType.NVarChar, 50).Value = boxDepartment.Text;
Command.Parameters.Add("@UID", SqlDbType.NVarChar, 50).Value = txtUID.Text;
Command.Parameters.Add("@Payment", SqlDbType.NVarChar, 10).Value = boxPayment.Text;
Command.Parameters.Add("@SchoolId", SqlDbType.NVarChar, 10).Value = txtMatricNumber.Text;
Command.Parameters.Add("@IDMain", SqlDbType.NVarChar, 10).Value = aa;
Command.Parameters.Add("@Sex", SqlDbType.NVarChar, 10).Value = bxSex.Text;
Command.Parameters.Add("@ManHuntP", SqlDbType.NVarChar, 10).Value = hunt;
conn.Open();
Command.ExecuteNonQuery();
conn.Close();
}
clearAll();
Console.WriteLine("Full");
upda = true;
}
}
}
else
{
string sqlquery = string.Format("Update {0} SET Name = '" + txtName.Text + "', Image = '" + bytes + "', UID='" + txtUID.Text + "' , SchoolId='" + txtMatricNumber.Text + "', Course='" + boxDepartment.Text + "', ManHuntP='" + hunt + "', Sex='" + bxSex.Text + "' where Name = '" + txtName.Text + "'", MainData);
string sqlSelectQuery3 = "UPDATE " + MainData + " SET SName=@SName, OName=@OName, Image=@Image, Course=@Course, UID=@UID, SchoolId=@SchoolId, Sex=@Sex, ManHuntP=@ManHuntP WHERE SchoolId = @SchoolId";
using (SqlCommand Command = new SqlCommand(sqlSelectQuery3, conn))
{
Command.Parameters.Add("@SName", SqlDbType.NVarChar, 50).Value = txtName.Text;
Command.Parameters.Add("@OName", SqlDbType.NVarChar, 50).Value = txtOtherNames.Text;
Command.Parameters.Add("@Image", SqlDbType.Image, bytes.Length).Value = bytes;
Command.Parameters.Add("@Course", SqlDbType.NVarChar, 50).Value = boxDepartment.Text;
Command.Parameters.Add("@UID", SqlDbType.NVarChar, 50).Value = txtUID.Text;
Command.Parameters.Add("@SchoolId", SqlDbType.NVarChar, 10).Value = txtMatricNumber.Text;
Command.Parameters.Add("@Sex", SqlDbType.NVarChar, 10).Value = bxSex.Text;
Command.Parameters.Add("@ManHuntP", SqlDbType.NVarChar, 10).Value = hunt;
conn.Open();
Command.ExecuteNonQuery();
conn.Close();
}
upda = true;
}
display2();
}
catch (Exception ex)
{
popScreen("Database Error", ex.Message, "Warn");
}
if (upda)
{
popScreen("Database Alert", "Database has been updated Successful", "Info");
ffd = new foodCollectionQuery();
ffd.actSQL(txtMatricNumber.Text, DateTime.Now.ToLongTimeString(), date, "Student updated bio data");
clearAll();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
private void btnRegister_Click(object sender, EventArgs e)
{
string hunt = null;
string aa = null;
if (boxPayment.Text == "Full" || boxPayment.Text == "Half")
{
aa = returnTicket().ToString();
}
else
{
return;
}
if (radioFalse.Checked)
{
hunt = "False";
}
if (radioTrue.Checked)
{
hunt = "True";
}
try {
if (checkDB())
{
popScreen("Database Alert", "Student informantion already exist", "Info");
return;
}
if (checkUID())
{
popScreen("Database Alert", "Card information already exist", "Info");
return;
}
Image img = pictureBox1.Image;
MemoryStream ms = new MemoryStream();
img.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);
byte[] bytes = ms.ToArray();
string sqlSelectQuery3 = "INSERT INTO " + MainData + " VALUES (@SName, @OName, @Image, @Course, @UID, @Payment, @SchoolId, @IDMain, @Sex, @ManHuntP, @phili, @PhiliTicket);";
using (SqlCommand Command = new SqlCommand(sqlSelectQuery3, conn))
{
Command.Parameters.Add("@SName", SqlDbType.NVarChar, 50).Value = txtName.Text;
Command.Parameters.Add("@OName", SqlDbType.NVarChar, 50).Value = txtOtherNames.Text;
Command.Parameters.Add("@Image", SqlDbType.Image, bytes.Length).Value = bytes;
Command.Parameters.Add("@Course", SqlDbType.NVarChar, 50).Value = boxDepartment.Text;
Command.Parameters.Add("@UID", SqlDbType.NVarChar, 50).Value = txtUID.Text;
Command.Parameters.Add("@Payment", SqlDbType.NVarChar, 10).Value = boxPayment.Text;
Command.Parameters.Add("@SchoolId", SqlDbType.NVarChar, 10).Value = txtMatricNumber.Text;
Command.Parameters.Add("@IDMain", SqlDbType.NVarChar, 10).Value = aa;
Command.Parameters.Add("@Sex", SqlDbType.NVarChar, 10).Value = bxSex.Text;
Command.Parameters.Add("@ManHuntP", SqlDbType.NVarChar, 10).Value = hunt;
Command.Parameters.Add("@phili", SqlDbType.NVarChar, 50).Value = "None";
Command.Parameters.Add("@PhiliTicket", SqlDbType.NVarChar, 10).Value = "0";
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
Command.ExecuteNonQuery();
conn.Close();
display2();
ffd = new foodCollectionQuery();
ffd.actSQL("New Registration", DateTime.Now.ToLongTimeString(), date, "Student registeration of Name: " + txtName.Text + " School ID: " + txtMatricNumber.Text);
clearAll();
popScreen("Database Alert", "Data has been entered Successful", "Info");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
popScreen("Database Error", ex.Message, "Warn");
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
}