private void take_attendance_Click(object sender, EventArgs e)
{
imageBoxFrameGrabber.Visible = false;
panel1.Visible = false;
panel2.Visible = false;
imageBoxFrameGrabber.Visible = true;
panel1.Visible = true;
Boolean a = false;
string connectionString = "Data Source = localhost; User ID = root; Password = toor123; Database=attendance; pooling = false; port = 3306; Allow User Variables = true; SslMode = none";
MySqlConnection Conn = new MySqlConnection(connectionString);
Conn.Open();
if (Conn.State == ConnectionState.Open)
{
//MessageBox.Show("Connection is Active.");
//konek.Close();
}
MySqlCommand command = Conn.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = "SELECT COLUMN_NAME FROM Information_Schema.columns WHERE TABLE_SCHEMA='attendance';";
DateTime date = DateTime.Now;
MySqlDataReader Reader2;
Reader2 = command.ExecuteReader();
while (Reader2.Read())
{
string b = Convert.ToString(Reader2[0]);
if (b == date.ToString("M/d/yy"))
{
a = false;
break;
}
else
{
a = true;
}
}
Reader2.Close();
Reader2.Dispose();
if (a == true)
{
string add_column = "ALTER TABLE `students` ADD `" + date.ToString("M/d/yy") + "` VARCHAR(10) NOT NULL AFTER `Name`;";
MySqlCommand cmd = Conn.CreateCommand();
cmd.CommandText = add_column;
Reader2 = cmd.ExecuteReader();
Reader2.Close();
Reader2.Dispose();
}
Conn.Close();
}
private void metroButton1_Click(object sender, EventArgs e)
{
DialogResult dialogResult = MetroFramework.MetroMessageBox.Show(this, "Are you sure you want to Delete this Vineyard Tour?", "Message", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
if (dialogResult == DialogResult.Yes)
{
try
{
SqlConnection sqlcon = new SqlConnection(Globals_Class.ConnectionString);
sqlcon.Open();
string cmd = "DELETE FROM VineyardTours WHERE VineyardTourID ='" + SelectedWineTastingID.ToString() + "'";
SqlCommand sqlcom = new SqlCommand(cmd, sqlcon);
sqlcom.ExecuteNonQuery();
sqlcon.Close();
MetroFramework.MetroMessageBox.Show(this, "The Booking has been successfully Deleted!", "Message", MessageBoxButtons.OK, MessageBoxIcon.None);
//Update List
listBox1.Items.Clear();
SqlConnection sqlcon2 = new SqlConnection(Globals_Class.ConnectionString);
sqlcon2.Open();
string CMD2 = "SELECT CustomerFullName FROM VineyardTours";
SqlCommand sqlcom2 = new SqlCommand(CMD2, sqlcon2);
SqlDataReader Reader2;
Reader2 = sqlcom2.ExecuteReader();
if (Reader2.HasRows)
{
while (Reader2.Read())
{
listBox1.Items.Add(Reader2["CustomerFullName"].ToString());
}
}
Reader2.Close();
sqlcon2.Close();
txtCustomerPNumber.Text = "";
txtOldDate.Text = "";
txtOldPArtySize.Text = "";
txtOldTime.Text = "";
}
catch
{
MetroFramework.MetroMessageBox.Show(this, "A Connection to the Database could not be Made!", "Message", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
}
public int catchDB(DateTime date, string category)
{
string datedb = date.ToString("yyyy-MM-dd");
int result;
MySqlConnection con = null;
MySqlCommand command, command2;
con = new MySqlConnection(conectabanco);
command = new MySqlCommand("SELECT SUM(Tempo) FROM `concurso` WHERE Categoria = '" + category + "' AND DataRevisao = '" + datedb + "'", con);
command2 = new MySqlCommand("SELECT count(DataRevisao) FROM concurso WHERE Categoria = '" + category + "' AND DataRevisao = '" + datedb + "'", con);
con.Open();
MySqlDataReader Reader1, Reader2;
Reader2 = command2.ExecuteReader();
Reader2.Read();
string valid = Reader2.GetString(0);
Reader2.Close();
if (Convert.ToInt32(valid) > 0)
{
Reader1 = command.ExecuteReader();
Reader1.Read();
result = Convert.ToInt32(Reader1.GetString(0));
Reader1.Close();
return(result);
}
else
{
return(0);
}
}
public (string, string, string, string, string) ConnectDataBase(string Data, int nextresult)
{
string name, path, cate, time, revision;
name = "0"; path = "0"; cate = "0"; time = "0";
try
{
con = new MySqlConnection(conectabanco);
command2 = new MySqlCommand("SELECT count(Data) FROM concurso where DataRevisao='" + Data + "'", con);
command = new MySqlCommand("SELECT * FROM concurso where DataRevisao='" + Data + "'", con);
con.Open();
MySqlDataReader Reader1, Reader2;
Reader2 = command2.ExecuteReader();
Reader2.Read();
string v = Reader2.GetString(0);
int tam = Convert.ToInt32(v);
string[] namestr, pathstr, catestr, timestr, revistr;
namestr = new string[tam];
pathstr = new string[tam];
catestr = new string[tam];
timestr = new string[tam];
revistr = new string[tam];
Reader2.Close();
Reader1 = command.ExecuteReader();
int valid = Convert.ToInt32(v) - nextresult;
if (Convert.ToInt32(valid) > 0)
{
while (Reader1.HasRows)
{
int aux = 0;
while (Reader1.Read())
{
namestr[aux] = Reader1.GetString(0);
pathstr[aux] = Reader1.GetString(1);
catestr[aux] = Reader1.GetString(2);
revistr[aux] = Reader1.GetString(5);
timestr[aux] = Reader1.GetString(6);
aux++;
}
Reader1.NextResult();
}
name = namestr[nextresult];
path = pathstr[nextresult];
cate = catestr[nextresult];
time = timestr[nextresult];
revision = revistr[nextresult];
}
else
{
name = "0"; path = "0"; cate = "0"; time = "0"; revision = "0";
}
}
catch (Exception erro)
{
name = "ERRO"; path = "ERRO"; cate = "ERRO"; time = "ERRO"; revision = "ERRO";
MessageBox.Show(Convert.ToString(erro));
}
return(name, path, cate, time, revision);
}
private void btnAdd_Click(object sender, EventArgs e)
{
DialogResult result = MetroFramework.MetroMessageBox.Show(this, "Are you Sure you want to Add this Customer to the Loyalty Program?", "Message", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
if (result == DialogResult.Yes)
{
try
{
SqlConnection sqlcon = new SqlConnection(Globals_Class.ConnectionString);
sqlcon.Open();
string Update = "UPDATE Customers SET isMember ='" + Globals_Class.isLoyaltyMember.ToString() + "' WHERE CustomerID ='" + CustomerID + "'";
SqlCommand sqlcom = new SqlCommand(Update, sqlcon);
sqlcom.ExecuteNonQuery();
sqlcon.Close();
MetroFramework.MetroMessageBox.Show(this, "User Added to The Loyalty Program!", "Message", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
btnAdd.Enabled = false;
//Refresh List
listBox1.Text = "";
listBox1.Items.Clear();
SqlConnection sqlcon2 = new SqlConnection(Globals_Class.ConnectionString);
sqlcon2.Open();
string CMD2 = "SELECT CustomerFullName FROM Customers";
SqlCommand sqlcom2 = new SqlCommand(CMD2, sqlcon2);
SqlDataReader Reader2;
Reader2 = sqlcom2.ExecuteReader();
if (Reader2.HasRows)
{
while (Reader2.Read())
{
listBox1.Items.Add(Reader2["CustomerFullName"].ToString());
}
}
Reader2.Close();
sqlcon2.Close();
}
catch
{
MetroFramework.MetroMessageBox.Show(this, "Could not Connect to the Database!", "Message", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
else
{
btnAdd.Enabled = false;
listBox1.Items.Clear();
SqlConnection sqlcon = new SqlConnection(Globals_Class.ConnectionString);
sqlcon.Open();
string CMD = "SELECT CustomerFullName FROM Customers";
SqlCommand sqlcom = new SqlCommand(CMD, sqlcon);
SqlDataReader Reader;
Reader = sqlcom.ExecuteReader();
if (Reader.HasRows)
{
while (Reader.Read())
{
listBox1.Items.Add(Reader["CustomerFullName"].ToString());
}
}
Reader.Close();
sqlcon.Close();
}
}
void FrameGrabber(object sender, EventArgs e)
{
label3.Text = "0";
//label4.Text = "";
NamePersons.Add("");
//Get the current frame form capture device
currentFrame = grabber.QueryFrame().Resize(320, 240, Emgu.CV.CvEnum.INTER.CV_INTER_CUBIC);
//Convert it to Grayscale
gray = currentFrame.Convert <Gray, Byte>();
//Face Detector
MCvAvgComp[][] facesDetected = gray.DetectHaarCascade(
face,
1.2,
10,
Emgu.CV.CvEnum.HAAR_DETECTION_TYPE.DO_CANNY_PRUNING,
new Size(20, 20));
//Action for each element detected
foreach (MCvAvgComp f in facesDetected[0])
{
t = t + 1;
result = currentFrame.Copy(f.rect).Convert <Gray, byte>().Resize(100, 100, Emgu.CV.CvEnum.INTER.CV_INTER_CUBIC);
//draw the face detected in the 0th (gray) channel with blue color
currentFrame.Draw(f.rect, new Bgr(Color.Red), 2);
if (trainingImages.ToArray().Length != 0)
{
MCvTermCriteria termCrit = new MCvTermCriteria(ContTrain, 0.001);
EigenObjectRecognizer recognizer = new EigenObjectRecognizer(
trainingImages.ToArray(),
labels.ToArray(),
3000,
ref termCrit);
name = recognizer.Recognize(result);
currentFrame.Draw(name, ref font, new Point(f.rect.X - 2, f.rect.Y - 2), new Bgr(Color.LightGreen));
}
NamePersons[t - 1] = name;
NamePersons.Add("");
label3.Text = facesDetected[0].Length.ToString();
}
t = 0;
for (int nnn = 0; nnn < facesDetected[0].Length; nnn++)
{
names = names + NamePersons[nnn] + ", ";
if (panel2.Visible == false)
{
string connectionString = "Data Source = localhost; User ID = root; Password = toor123; Database=attendance; pooling = false; port = 3306; Allow User Variables = true; SslMode = none";
MySqlConnection Conn = new MySqlConnection(connectionString);
Conn.Open();
if (Conn.State == ConnectionState.Open)
{
MySqlDataReader Reader2;
//konek.Close();
DateTime date = DateTime.Now;
string mark_attendance = "UPDATE `students` SET `" + date.ToString("M/d/yy") + "` = 'P' WHERE `students`.`Name` = '" + NamePersons[nnn] + "';";
MySqlCommand cmd2 = Conn.CreateCommand();
cmd2.CommandText = mark_attendance;
Reader2 = cmd2.ExecuteReader();
Reader2.Close();
Reader2.Dispose();
}
Conn.Close();
}
}
imageBoxFrameGrabber.Image = currentFrame;
label4.Text = names;
names = "";
NamePersons.Clear();
}
private void metroButton1_Click(object sender, EventArgs e)
{
if (dtpDate.Text == "" || txtGroupSize.Text == "" || txtTime.Text == "")
{
MetroFramework.MetroMessageBox.Show(this, "Not all information required has been Provided!", "Message", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
DialogResult dialog = MetroFramework.MetroMessageBox.Show(this, "Are you sure you want to update this Wine Tasting?", "Message", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
if (dialog == DialogResult.Yes)
{
metroPanel1.Enabled = false;
try
{
//Variables
string WineTastingDate = dtpDate.Text;
string WineTastingTime = txtTime.Text;
int PartySize = Convert.ToInt32(txtGroupSize.Text);
SqlConnection sqlcon = new SqlConnection(Globals_Class.ConnectionString);
sqlcon.Open();
string Query = "UPDATE WineTasting SET WineTastingDate = @WineTastingDate, WineTastingTime = @WineTastingTime, PartySize = @PartySize WHERE WineTastingID ='" + SelectedWineTastingID.ToString() + "'";
SqlCommand sqlcom = new SqlCommand(Query, sqlcon);
sqlcom.Parameters.Add(new SqlParameter("@WineTastingDate", WineTastingDate));
sqlcom.Parameters.Add(new SqlParameter("@WineTastingTime", WineTastingTime));
sqlcom.Parameters.Add(new SqlParameter("@PartySize", PartySize));
sqlcom.ExecuteNonQuery();
sqlcon.Close();
MetroFramework.MetroMessageBox.Show(this, "Wine Tasting Booking Made Successfully!", "Message", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
//Update Listbox
listBox1.Items.Clear();
SqlConnection sqlcon2 = new SqlConnection(Globals_Class.ConnectionString);
sqlcon2.Open();
string CMD2 = "SELECT CustomerFullName FROM WineTasting";
SqlCommand sqlcom2 = new SqlCommand(CMD2, sqlcon2);
SqlDataReader Reader2;
Reader2 = sqlcom2.ExecuteReader();
if (Reader2.HasRows)
{
while (Reader2.Read())
{
listBox1.Items.Add(Reader2["CustomerFullName"].ToString());
}
}
Reader2.Close();
sqlcon2.Close();
}
catch
{
MetroFramework.MetroMessageBox.Show(this, "A Connection to the Database could not be made!", "Message", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
txtTime.Text = "";
txtGroupSize.Text = "";
dtpDate.Text = "";
metroPanel1.Enabled = true;
panel2.Enabled = false;
}
}
public Form3()
{
InitializeComponent();
string myConnnection = "datasource=localhost; port=3306;username=root;password=Sercan.123; ";
MySqlConnection myConn = new MySqlConnection(myConnnection);
myConn.Open();
MySqlCommand command = new MySqlCommand();
command = myConn.CreateCommand();
command.CommandText = "SELECT schema_name FROM information_schema.schemata where schema_name not like '%_schema' and schema_name not like 'mysql';";
MySqlDataReader Reader1;
Reader1 = command.ExecuteReader();
while (Reader1.Read())
{
string row = "";
for (int i = 0; i < Reader1.FieldCount; i++)
{
row += Reader1.GetValue(i).ToString();
}
trV1.Nodes.Add(row);
}
for (int i = 0; i < trV1.Nodes.Count; i++)
{
trV1.Nodes[i].Nodes.Add("Tables");
trV1.Nodes[i].Nodes.Add("Views");
trV1.Nodes[i].Nodes.Add("Stored Procedure");
trV1.Nodes[i].Nodes.Add("Functions ");
}
Reader1.Close();
Reader1.Dispose();
myConn.Close();
myConn.Open();
MySqlCommand command_table = new MySqlCommand();
command_table = myConn.CreateCommand();
for (int i = 0; i < trV1.Nodes.Count; i++)
{
command_table.CommandText = "SELECT table_name FROM information_schema.tables WHERE table_schema ='" + trV1.Nodes[i].Text + "' ";
MySqlDataReader Reader2;
Reader2 = command_table.ExecuteReader();
while (Reader2.Read())
{
string table = "";
for (int j = 0; j < Reader2.FieldCount; j++)
{
table += Reader2.GetValue(j).ToString();
}
trV1.Nodes[i].Nodes[0].Nodes.Add(table);
}
Reader2.Close();
Reader2.Dispose();
}
myConn.Close();
myConn.Open();
MySqlCommand command_view = new MySqlCommand();
command_view = myConn.CreateCommand();
for (int i = 0; i < trV1.Nodes.Count; i++)
{
command_view.CommandText = "SELECT table_name FROM information_schema.tables WHERE table_type='VIEW' AND table_schema ='" + trV1.Nodes[i].Text + "' ";
MySqlDataReader Reader3;
Reader3 = command_view.ExecuteReader();
while (Reader3.Read())
{
string view = "";
for (int j = 0; j < Reader3.FieldCount; j++)
{
view += Reader3.GetValue(j).ToString();
}
trV1.Nodes[i].Nodes[1].Nodes.Add(view);
}
Reader3.Close();
Reader3.Dispose();
}
myConn.Close();
myConn.Open();
MySqlCommand command_procedure = new MySqlCommand();
command_procedure = myConn.CreateCommand();
for (int i = 0; i < trV1.Nodes.Count; i++)
{
command_procedure.CommandText = "SHOW PROCEDURE STATUS WHERE Db ='" + trV1.Nodes[i].Text + "' ";
MySqlDataReader Reader4;
Reader4 = command_procedure.ExecuteReader();
while (Reader4.Read())
{
string procedure = "";
for (int j = 0; j < Reader4.FieldCount; j++)
{
procedure += Reader4.GetValue(j).ToString();
}
trV1.Nodes[i].Nodes[2].Nodes.Add(procedure);
}
Reader4.Close();
Reader4.Dispose();
}
myConn.Close();
myConn.Open();
MySqlCommand command_function = new MySqlCommand();
command_function = myConn.CreateCommand();
for (int i = 0; i < trV1.Nodes.Count; i++)
{
command_function.CommandText = "SHOW FUNCTION STATUS WHERE Db ='" + trV1.Nodes[i].Text + "' ";
MySqlDataReader Reader5;
Reader5 = command_function.ExecuteReader();
while (Reader5.Read())
{
string function = "";
for (int j = 0; j < Reader5.FieldCount; j++)
{
function += Reader5.GetValue(j).ToString();
}
trV1.Nodes[i].Nodes[3].Nodes.Add(function);
}
Reader5.Close();
Reader5.Dispose();
}
ContextMenuStrip docMenu = new ContextMenuStrip();
ToolStripMenuItem SelectRows = new ToolStripMenuItem();
SelectRows.Text = "Select Rows";
ToolStripMenuItem AlterTable = new ToolStripMenuItem();
AlterTable.Text = "Alter Table";
ToolStripMenuItem DropTable = new ToolStripMenuItem();
DropTable.Text = "Drop Table";
docMenu.Items.AddRange(new ToolStripMenuItem[] { SelectRows, AlterTable, DropTable });
for (int i = 0; i < trV1.Nodes.Count; i++)
{
for (int j = 0; j < trV1.Nodes[i].Nodes[0].Nodes.Count; j++)
{
trV1.Nodes[i].Nodes[0].Nodes[j].ContextMenuStrip = docMenu;
}
}
SelectRows.Click += SelectRows_Click;
void SelectRows_Click(object sender, System.EventArgs e)
{
MySqlConnection connection = new MySqlConnection("datasource=localhost;port=3306;username=root;password=Sercan.123");
connection.Open();
try
{
MySqlDataAdapter adapter = new MySqlDataAdapter("SELECT * FROM " + trV1.SelectedNode.Parent.Parent.Text + "." + trV1.SelectedNode.Text + " ", connection);
DataSet ds = new DataSet();
adapter.Fill(ds, trV1.SelectedNode.Text);
dataGridView1.DataSource = ds.Tables[trV1.SelectedNode.Text];
string a = ds.Tables[0].Columns.ToString();
//MySqlDataAdapter adapter = new MySqlDataAdapter("SELECT * FROM blm437.ogrenci ", connection);
//DataSet ds = new DataSet();
//adapter.Fill(ds, "ogrenci");
//dataGridView1.DataSource = ds.Tables["ogrenci"];
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
private void listBox1_Click(object sender, EventArgs e)
{
SqlConnection sqlcon1 = new SqlConnection(Globals_Class.ConnectionString);
sqlcon1.Open();
string Select = "SELECT UserID From Users WHERE UserName ='******'";
SqlCommand sqlcom1 = new SqlCommand(Select, sqlcon1);
SqlDataReader Reader;
Reader = sqlcom1.ExecuteReader();
if (Reader.HasRows)
{
while (Reader.Read())
{
isAdmin = Convert.ToInt32((Reader["UserID"]));
if (isAdmin == 1)
{
MetroFramework.MetroMessageBox.Show(this, "The Admin Account Cannot be Deleted!", "Message", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
DialogResult dialogResult = MetroFramework.MetroMessageBox.Show(this, "Are you sure you want to delete this user?", "Message", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
if (dialogResult == DialogResult.Yes)
{
try
{
SqlConnection sqlcon = new SqlConnection(Globals_Class.ConnectionString);
sqlcon.Open();
string Command = "DELETE FROM Users WHERE UserName ='******'";
SqlCommand sqlcom = new SqlCommand(Command, sqlcon);
sqlcom.ExecuteNonQuery();
MetroFramework.MetroMessageBox.Show(this, "The Selected User was Deleted Successfully!", "Message", MessageBoxButtons.OK, MessageBoxIcon.None);
listBox1.Items.Clear();
SqlConnection sqlcon2 = new SqlConnection(Globals_Class.ConnectionString);
sqlcon2.Open();
string CMD2 = "SELECT UserName FROM Users";
SqlCommand sqlcom2 = new SqlCommand(CMD2, sqlcon2);
SqlDataReader Reader2;
Reader2 = sqlcom2.ExecuteReader();
if (Reader2.HasRows)
{
while (Reader2.Read())
{
listBox1.Items.Add(Reader2["UserName"].ToString());
}
}
Reader2.Close();
sqlcon2.Close();
}
catch
{
MetroFramework.MetroMessageBox.Show(this, "An Error Occurred whislt Deleting the User!", "Message", MessageBoxButtons.OK, MessageBoxIcon.Warning);
listBox1.Items.Clear();
SqlConnection sqlcon3 = new SqlConnection(Globals_Class.ConnectionString);
sqlcon3.Open();
string CMD3 = "SELECT UserName FROM Users";
SqlCommand sqlcom3 = new SqlCommand(CMD3, sqlcon3);
SqlDataReader Reader3;
Reader3 = sqlcom3.ExecuteReader();
if (Reader3.HasRows)
{
while (Reader3.Read())
{
listBox1.Items.Add(Reader3["UserName"].ToString());
}
}
Reader3.Close();
sqlcon3.Close();
}
}
}
}
}
}