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 ResimIndir_btn_Click(object sender, EventArgs e)
{
progressBar1.Maximum = 100;
progressBar1.Step = 1;
progressBar1.Value = 0;
mysqlconn.Open();
List <string> list_url = new List <string>();
MySqlDataReader Reader;
try
{
MySqlCommand cmd = mysqlconn.CreateCommand();
cmd.CommandText = "SELECT ImageURL_M FROM kitaplar";
Reader = cmd.ExecuteReader();
while (Reader.Read())
{
//Console.WriteLine(Reader[0].ToString());
list_url.Add(Reader[0].ToString());
}
mysqlconn.Close();
}
catch (Exception)
{
Console.WriteLine("Error!!!");
mysqlconn.Close();
}
mysqlconn.Open();
List <string> list_isbn = new List <string>();
MySqlDataReader Reader2;
try
{
MySqlCommand cmd = mysqlconn.CreateCommand();
cmd.CommandText = "SELECT ISBN FROM kitaplar";
Reader2 = cmd.ExecuteReader();
while (Reader2.Read())
{
//Console.WriteLine(Reader[0].ToString());
list_isbn.Add(Reader2[0].ToString());
}
mysqlconn.Close();
}
catch (Exception)
{
Console.WriteLine("Error!!!");
mysqlconn.Close();
}
progressBar1.Maximum = 100;
progressBar1.Step = 1;
progressBar1.Value = 0;
for (int i = 0; i < 100; i++)
{
using (WebClient webClient = new WebClient())
{
byte[] data = webClient.DownloadData(list_url.ElementAt(i));
using (MemoryStream mem = new MemoryStream(data))
{
using (var yourImage = Image.FromStream(mem))
{
yourImage.Save("C:/Users/Cenk/Desktop/Kitap Resimler/" + i.ToString() + ".jpg", ImageFormat.Jpeg);
}
}
mysqlconn.Open();
progressBar1.Value = i;
FileStream fs;
Byte[] bindata;
MySqlParameter picpara;
MySqlCommand cmd = new MySqlCommand("UPDATE kitaplar SET Resim=@Resim WHERE ISBN=@ISBN;", mysqlconn);
picpara = cmd.Parameters.Add("@Resim", MySqlDbType.MediumBlob);
cmd.Parameters.AddWithValue("@ISBN", list_isbn.ElementAt(i));
cmd.Prepare();
//txtPicPath is the path of the image, e.g. C:\MyPic.png
fs = new FileStream("C:/Users/Cenk/Desktop/Kitap Resimler/" + i.ToString() + ".jpg", FileMode.Open, FileAccess.Read);
bindata = new byte[Convert.ToInt32(fs.Length)];
fs.Read(bindata, 0, Convert.ToInt32(fs.Length));
fs.Close();
picpara.Value = bindata;
cmd.ExecuteNonQuery();
mysqlconn.Close();
}
}
progressBar1.Value = 0;
MessageBox.Show("Kitap İndirme Tamamlandı!", "SİSTEM", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
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();
}
}
/// <summary>
/// Invoked when this page is about to be displayed in a Frame.
/// </summary>
/// <param name="e">Event data that describes how this page was reached. The Parameter
/// property is typically used to configure the page.</param>
protected override void OnNavigatedTo(NavigationEventArgs e)
{
//============================================= ISSO AQUI PRA PREENCHER O LIST DE ORÇAMENTOS=========================>>
lsbOrcamento.Items.Clear();
string MyConString = "SERVER=localhost;DATABASE=Gessoft;UID=root;PASSWORD=vegas;";
MySqlConnection connection = new MySqlConnection(MyConString);
MySqlCommand command = connection.CreateCommand();
MySqlDataReader Reader;
command.CommandText = "select Cliente.cdCliente, Cliente.nmCliente, Cliente.cpf, Cliente.rg, Cliente.email, Cliente.sexo, Cliente.cdStatus, Telefones.nResidencial, Telefones.nCelular, Endereco.cdEndereco, Endereco.rua, Endereco.bairro, Endereco.cidade, Endereco.estado, Endereco.cep, Endereco.numero, Endereco.complemento, agendaServico.numS, agendaServico.cdOrcamento, agendaServico.cdServico, agendaServico.descricao, agendaServico.cdusuario, agendaServico.dia, agendaServico.mes, agendaServico.ano, agendaServico.dtgeracao from Cliente inner join endereco on endereco.cdCliente = cliente.cdCliente inner join telefones on telefones.cdCliente = cliente.cdCliente inner join agendaservico on agendaservico.cdCliente = cliente.cdCliente where cliente.cdCliente = " + Gambis.BadCodigo + " and cdOrcamento <> 'null' and Dia <> 'null' and Mes <> 'null' and Ano <> 'null' and dtgeracao <> 'null';";
connection.Open();
Reader = command.ExecuteReader();
int cont = 0;
string cdOrcamento;
string ClienteNome;
string DiaAgendameto;
string MesAgendameto;
string AnoAgendameto;
string DataGeracao;
while (Reader.Read())
{
cont++;
cdOrcamento = (Reader.GetString("cdOrcamento"));
ClienteNome = (Reader.GetString("nmCliente"));
DiaAgendameto = (Reader.GetString("dia"));
MesAgendameto = (Reader.GetString("mes"));
AnoAgendameto = (Reader.GetString("ano"));
DataGeracao = (Reader.GetString("dtgeracao"));
TextBlock Orcamento = new TextBlock();
Orcamento.Text = " Orçamento Nº: " + cdOrcamento;
Orcamento.FontSize = 20;
Orcamento.FontWeight = FontWeights.Bold;
Orcamento.Foreground = new SolidColorBrush(Colors.Black);
Orcamento.TextAlignment = TextAlignment.Left;
Orcamento.VerticalAlignment = VerticalAlignment.Center;
TextBlock Data = new TextBlock();
Data.Text = " Data de agendamento: " + DiaAgendameto + "/" + MesAgendameto + "/" + AnoAgendameto;
Data.FontSize = 13;
Data.Foreground = new SolidColorBrush(Colors.Black);
Data.TextAlignment = TextAlignment.Left;
Data.VerticalAlignment = VerticalAlignment.Center;
TextBlock DataGera = new TextBlock();
DataGera.Text = " Data de Criação: " + DataGeracao;
DataGera.FontSize = 13;
DataGera.Foreground = new SolidColorBrush(Colors.Black);
DataGera.TextAlignment = TextAlignment.Left;
DataGera.VerticalAlignment = VerticalAlignment.Center;
StackPanel UserBlock = new StackPanel();
UserBlock.Background = new SolidColorBrush(Colors.SkyBlue);
UserBlock.Height = 110;
UserBlock.Width = 650;
UserBlock.VerticalAlignment = VerticalAlignment.Center;
UserBlock.HorizontalAlignment = HorizontalAlignment.Center;
UserBlock.Children.Add(Orcamento);
UserBlock.Children.Add(Data);
UserBlock.Children.Add(DataGera);
lsbOrcamento.Items.Add(UserBlock);
}
connection.Close();
// ===============================================================================================================>>
lsbServico.Items.Clear();
string MyConString2 = "SERVER=localhost;DATABASE=Gessoft;UID=root;PASSWORD=vegas;";
MySqlConnection connection2 = new MySqlConnection(MyConString2);
MySqlCommand command2 = connection2.CreateCommand();
MySqlDataReader Reader2;
command2.CommandText = "select Cliente.cdCliente, Cliente.nmCliente, Cliente.cpf, Cliente.rg, Cliente.email, Cliente.sexo, Cliente.cdStatus, Telefones.nResidencial, Telefones.nCelular, Endereco.cdEndereco, Endereco.rua, Endereco.bairro, Endereco.cidade, Endereco.estado, Endereco.cep, Endereco.numero, Endereco.complemento, agendaServico.numS, agendaServico.cdOrcamento, agendaServico.cdServico, agendaServico.descricao, agendaServico.cdusuario, agendaServico.dia, agendaServico.mes, agendaServico.ano, agendaServico.dtgeracao from Cliente inner join endereco on endereco.cdCliente = cliente.cdCliente inner join telefones on telefones.cdCliente = cliente.cdCliente inner join agendaservico on agendaservico.cdCliente = cliente.cdCliente where cliente.cdCliente = " + Gambis.BadCodigo + " and cdServico <> 'null' and Dia <> 'null' and Mes <> 'null' and Ano <> 'null' and dtgeracao <> 'null';";
connection2.Open();
Reader2 = command2.ExecuteReader();
int contS = 0;
string cdServico;
string ClienteNomeS;
string DiaAgendametoS;
string MesAgendametoS;
string AnoAgendametoS;
string DataGeracaoS;
while (Reader2.Read())
{
cont++;
cdServico = (Reader2.GetString("cdServico"));
ClienteNomeS = (Reader2.GetString("nmCliente"));
DiaAgendametoS = (Reader2.GetString("dia"));
MesAgendametoS = (Reader2.GetString("mes"));
AnoAgendametoS = (Reader2.GetString("ano"));
DataGeracaoS = (Reader2.GetString("dtgeracao"));
TextBlock Servico = new TextBlock();
Servico.Text = " Serviço Nº: " + cdServico;
Servico.FontSize = 20;
Servico.FontWeight = FontWeights.Bold;
Servico.Foreground = new SolidColorBrush(Colors.Black);
Servico.TextAlignment = TextAlignment.Left;
Servico.VerticalAlignment = VerticalAlignment.Center;
TextBlock DataS = new TextBlock();
DataS.Text = " Data de agendamento: " + DiaAgendametoS + "/" + MesAgendametoS + "/" + AnoAgendametoS;
DataS.FontSize = 13;
DataS.Foreground = new SolidColorBrush(Colors.Black);
DataS.TextAlignment = TextAlignment.Left;
DataS.VerticalAlignment = VerticalAlignment.Center;
TextBlock DataGeraS = new TextBlock();
DataGeraS.Text = " Data de Criação: " + DataGeracaoS;
DataGeraS.FontSize = 13;
DataGeraS.Foreground = new SolidColorBrush(Colors.Black);
DataGeraS.TextAlignment = TextAlignment.Left;
DataGeraS.VerticalAlignment = VerticalAlignment.Center;
StackPanel UserBlockS = new StackPanel();
UserBlockS.Background = new SolidColorBrush(Colors.SkyBlue);
UserBlockS.Height = 110;
UserBlockS.Width = 650;
UserBlockS.VerticalAlignment = VerticalAlignment.Center;
UserBlockS.HorizontalAlignment = HorizontalAlignment.Center;
UserBlockS.Children.Add(Servico);
UserBlockS.Children.Add(DataS);
UserBlockS.Children.Add(DataGeraS);
lsbServico.Items.Add(UserBlockS);
}
connection.Close();
//.Children.Add(UserBlock); Width="452" Height="126"
//ListBox1.Items.Add(codigo+"--"+email+"--"+nome);
//new TextBlock() { Name = "teste", Text = "Olaaaaaaaaa poha ", FontSize = 50 };
//new TextBox() { Name = "asd", Width=11, Height=11 };
}
private void loadDis()
{
MySqlConnection connection = new MySqlConnection(DBConnect.conn);
MySqlCommand command = connection.CreateCommand();
MySqlDataReader Reader;
command.CommandText = "SELECT *,file.name As file,client.name As client FROM disbursements LEFT JOIN client ON client.clientID = disbursements.clientID LEFT JOIN file ON file.fileID = disbursements.fileID WHERE disbursements.fileID='" + id + "' ;";
connection.Open();
Reader = command.ExecuteReader();
// create and execute query
t = new DataTable();
t.Columns.Add("DATE", typeof(string));
t.Columns.Add("No.", typeof(string));
t.Columns.Add("AMOUNT", typeof(string));
t.Columns.Add("BAL.", typeof(string));
t.Columns.Add("METHOD", typeof(string));
t.Columns.Add("DETAILS", typeof(string));
t.Rows.Add(new object[] { "", " ", "", "FILE SUMMARY", "", "" });
t.Rows.Add(new object[] { " ", " ", "", "", "", "" });
t.Rows.Add(new object[] { "DISBURSEMENTS", " ", "", "", "", "" });
t.Rows.Add(new object[] { "Date", "Invoice No.", "Amount", "Balance", "Method", "Details" });
DisDictionary.Clear();
while (Reader.Read())
{
DisDictionary.Add((Reader.IsDBNull(0) ? "none" : Reader.GetString(0)), (Reader.IsDBNull(9) ? "0" : Reader.GetString(9)));
t.Rows.Add(new object[] { (Reader.IsDBNull(14) ? "none" : Reader.GetString(14)), (Reader.IsDBNull(7) ? "none" : Reader.GetString(7)), Convert.ToDouble(Reader.IsDBNull(9) ? "0" : Reader.GetString(9)).ToString("n0"), Convert.ToDouble(Reader.IsDBNull(11) ? "none" : Reader.GetString(11)).ToString("n0"), (Reader.IsDBNull(8) ? "none" : Reader.GetString(8)), (Reader.IsDBNull(35) ? "none" : Reader.GetString(35)) });
}
totalDis = DisDictionary.Sum(m => Convert.ToDouble(m.Value));
t.Rows.Add(new object[] { "", " Total", totalDis.ToString("n0"), "", "", "" });
connection.Close();
MySqlConnection connection2 = new MySqlConnection(DBConnect.conn);
MySqlCommand command2 = connection2.CreateCommand();
MySqlDataReader Reader2;
command2.CommandText = "SELECT * FROM expenses LEFT JOIN client ON client.clientID = expenses.clientID LEFT JOIN file ON file.fileID = expenses.fileID WHERE expenses.fileID='" + id + "';";
connection2.Open();
Reader2 = command2.ExecuteReader();
t.Rows.Add(new object[] { "", " ", "", "", "", "" });
t.Rows.Add(new object[] { "EXPENSES", " ", "", "", "", "" });
t.Rows.Add(new object[] { "Date", "Invoice No.", "Amount", "Balance", "Method", "Details" });
ExpDictionary.Clear();
while (Reader2.Read())
{
ExpDictionary.Add((Reader2.IsDBNull(0) ? "none" : Reader2.GetString(0)), (Reader2.IsDBNull(7) ? "0" : Reader2.GetString(7)));
t.Rows.Add(new object[] { (Reader2.IsDBNull(11) ? "none" : Reader2.GetString(11)), " ", Convert.ToDouble(Reader2.IsDBNull(7) ? "0" : Reader2.GetString(7)).ToString("n0"), (Reader2.IsDBNull(8) ? "none" : Reader2.GetString(8)), (Reader2.IsDBNull(36) ? "none" : Reader2.GetString(36)), (Reader2.IsDBNull(6) ? "none" : Reader2.GetString(6)) });
}
totalExp = ExpDictionary.Sum(m => Convert.ToDouble(m.Value));
t.Rows.Add(new object[] { "", "Total ", totalExp.ToString("n0"), "", "", "" });
connection2.Close();
MySqlConnection connection3 = new MySqlConnection(DBConnect.conn);
MySqlCommand command3 = connection3.CreateCommand();
MySqlDataReader Reader3;
command3.CommandText = "SELECT * FROM events WHERE file ='" + nameTxt.Text + "';";
connection3.Open();
Reader3 = command3.ExecuteReader();
t.Rows.Add(new object[] { "", " ", "", "", "", "" });
t.Rows.Add(new object[] { "EVENTS", "SCHEDULES ", "", "", "", "" });
t.Rows.Add(new object[] { "Date", "Event", "Start", "End", "Progress", "Cost" });
while (Reader3.Read())
{
EventDictionary.Add((Reader3.IsDBNull(0) ? "none" : Reader3.GetString(0)), (Reader3.IsDBNull(18) ? "0" : Reader3.GetString(18)));
t.Rows.Add(new object[] { (Reader3.IsDBNull(10) ? "none" : Reader3.GetString(10)), (Reader3.IsDBNull(1) ? "none" : Reader3.GetString(1)), Convert.ToDateTime(Reader3.IsDBNull(2) ? "none" : Reader3.GetString(2)).ToString("H:mm:s"), Convert.ToDateTime(Reader3.IsDBNull(3) ? "none" : Reader3.GetString(3)).ToString("H:mm:s"), (Reader3.IsDBNull(16) ? "none" : Reader3.GetString(16)) + " " + (Reader3.IsDBNull(8) ? "none" : Reader3.GetString(8)), Convert.ToDouble(Reader3.IsDBNull(18) ? "0" : Reader3.GetString(18)).ToString("n0") });
}
totalEvent = EventDictionary.Sum(m => Convert.ToDouble(m.Value));
t.Rows.Add(new object[] { "", " ", "", "", "Total", totalEvent.ToString("n0") });
t.Rows.Add(new object[] { "", " ", "", "", "", "" });
t.Rows.Add(new object[] { "", " ", "", "", "Total", (totalDis - (totalEvent + totalExp)).ToString("n0") });
connection3.Close();
dtGrid.DataSource = t;
dtGrid.Rows[1].DefaultCellStyle.BackColor = Color.Beige;
}
private void button2_Click(object sender, EventArgs e)
{
loginBtn.Visible = false;
MySqlConnection connection = new MySqlConnection(DBConnect.conn);
MySqlCommand command = connection.CreateCommand();
MySqlDataReader Reader;
if (contactTxt.Text == "" || passwordTxt.Text == "")
{
MessageBox.Show("Insert login credentials");
loginBtn.Visible = true;
return;
}
command.CommandText = "SELECT * FROM users WHERE contact = '" + contactTxt.Text + "' AND password = '******'";
try
{
connection.Open();
}
catch {
MessageBox.Show("Error connecting to server !");
}
Reader = command.ExecuteReader();
while (Reader.Read())
{
if ((Reader.IsDBNull(7) ? "" : Reader.GetString(7)) == "")
{
MessageBox.Show("Access denied");
loginBtn.Visible = true;
}
else
{
Helper.username = (Reader.IsDBNull(2) ? "none" : Reader.GetString(2));
Helper.contact = (Reader.IsDBNull(7) ? "none" : Reader.GetString(7));
Helper.designation = (Reader.IsDBNull(5) ? "none" : Reader.GetString(5));
Helper.email = (Reader.IsDBNull(3) ? "none" : Reader.GetString(3));
Helper.image = (Reader.IsDBNull(8) ? "none" : Reader.GetString(8));
Helper.orgID = (Reader.IsDBNull(1) ? "none" : Reader.GetString(1));
loginBtn.Visible = false;
}
}
connection.Close();
MySqlConnection connection2 = new MySqlConnection(DBConnect.conn);
MySqlCommand command2 = connection2.CreateCommand();
MySqlDataReader Reader2;
command2.CommandText = "SELECT * FROM org WHERE orgID = '" + Helper.orgID + "'";
connection2.Open();
Reader2 = command2.ExecuteReader();
while (Reader2.Read())
{
Helper.logo = (Reader2.IsDBNull(9) ? "none" : Reader2.GetString(9));
Helper.address = (Reader2.IsDBNull(6) ? "none" : Reader2.GetString(6));
Helper.orgName = (Reader2.IsDBNull(1) ? "none" : Reader2.GetString(1));
Helper.code = (Reader2.IsDBNull(5) ? "none" : Reader2.GetString(5));
}
connection2.Close();
if (string.IsNullOrEmpty(Helper.contact) || string.IsNullOrEmpty(Helper.orgID))
{
MessageBox.Show("Access denied");
loginBtn.Visible = true;
}
else
{
loginBtn.Visible = false;
MainForm frm = new MainForm();
frm.Show();
this.Hide();
}
}
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;
}
}
private void loadDis()
{
MySqlConnection connection = new MySqlConnection(DBConnect.conn);
MySqlCommand command = connection.CreateCommand();
MySqlDataReader Reader;
command.CommandText = "SELECT fees.date As date,fees.invoice AS invoice,client.name As client,file.name As file,fees.amount As amount,fees.balance AS balance,fees.method as method,fees.details AS details,fees.paid AS paid FROM fees LEFT JOIN client ON client.clientID = fees.clientID LEFT JOIN file ON file.fileID = fees.fileID WHERE fees.date LIKE '%" + month + "%';";
connection.Open();
Reader = command.ExecuteReader();
// create and execute query
t = new DataTable();
t.Columns.Add("DATE", typeof(string)); //0
t.Columns.Add("No.", typeof(string)); //1
t.Columns.Add("CLIENT", typeof(string)); //2
t.Columns.Add("FILE", typeof(string)); //3
t.Columns.Add("AMOUNT", typeof(string)); //4
t.Columns.Add("BAL.", typeof(string)); //5
t.Columns.Add("METHOD", typeof(string)); //6
t.Columns.Add("DETAILS", typeof(string)); //7
t.Columns.Add("PAID", typeof(string)); //8
t.Columns.Add(" ", typeof(string)); //8
t.Rows.Add(new object[] { " ", " ", "", "", "FINANCIAL", "REPORT ", "" + month, "", "", "" });
t.Rows.Add(new object[] { "FEES", " ", "", "", "", "", "", "", "", "" });
t.Rows.Add(new object[] { "Date", "Invoice No.", "Client", "File", "Amount", "Balance", "Method", "Details", "Paid", "" });
while (Reader.Read())
{
t.Rows.Add(new object[] { (Reader.IsDBNull(0) ? "none" : Reader.GetString(0)), (Reader.IsDBNull(1) ? "none" : Reader.GetString(1)), (Reader.IsDBNull(2) ? "none" : Reader.GetString(2)), (Reader.IsDBNull(3) ? "none" : Reader.GetString(3)), Convert.ToDouble((Reader.IsDBNull(4) ? "none" : Reader.GetString(4))).ToString("n0"), (Reader.IsDBNull(5) ? "none" : Reader.GetString(5)), (Reader.IsDBNull(6) ? "none" : Reader.GetString(6)), (Reader.IsDBNull(7) ? "none" : Reader.GetString(7)), (Reader.IsDBNull(8) ? "none" : Reader.GetString(8)) });
FeesDictionary.Add((Reader.IsDBNull(0) ? "none" : Reader.GetString(0)), (Reader.IsDBNull(4) ? "none" : Reader.GetString(4)));
}
try
{
totalFees = FeesDictionary.Sum(m => Convert.ToDouble(m.Value));
// double amount = totalRent - totalExpense;
t.Rows.Add(new object[] { " ", " ", "TOTAL FEES:", "", "" + totalFees.ToString("n0"), "", "", "", "", "" });
}
catch
{
}
connection.Close();
MySqlConnection connection2 = new MySqlConnection(DBConnect.conn);
MySqlCommand command2 = connection2.CreateCommand();
MySqlDataReader Reader2;
command2.CommandText = "SELECT * FROM petty WHERE date LIKE '%" + month + "%';";
connection2.Open();
Reader2 = command2.ExecuteReader();
t.Rows.Add(new object[] { "", " ", "", "", "", "", "", "", "", "" });
t.Rows.Add(new object[] { "FIRM EXPENSES", " ", "", "", "", "", "", "", "", "" });
t.Rows.Add(new object[] { "Date", "Invoice No.", "Item", "Unit cost", "Qty", "Total Cost", "Method", "Reason", "Paid", "Approved" });
while (Reader2.Read())
{
t.Rows.Add(new object[] { (Reader2.IsDBNull(5) ? "none" : Reader2.GetString(5)), (Reader2.IsDBNull(0) ? "none" : Reader2.GetString(0)), (Reader2.IsDBNull(1) ? "none" : Reader2.GetString(1)), Convert.ToDouble((Reader2.IsDBNull(2) ? "none" : Reader2.GetString(2))).ToString("n0"), (Reader2.IsDBNull(3) ? "none" : Reader2.GetString(3)), Convert.ToDouble((Reader2.IsDBNull(4) ? "none" : Reader2.GetString(4))).ToString("n0"), (Reader2.IsDBNull(10) ? "none" : Reader2.GetString(10)), (Reader2.IsDBNull(9) ? "none" : Reader2.GetString(9)), (Reader2.IsDBNull(6) ? "none" : Reader2.GetString(6)), (Reader2.IsDBNull(11) ? "none" : Reader2.GetString(11)) });
ExpenseDictionary.Add((Reader2.IsDBNull(0) ? "none" : Reader2.GetString(0)), (Reader2.IsDBNull(4) ? "none" : Reader2.GetString(4)));
}
connection2.Close();
dtGrid.DataSource = t;
dtGrid.Rows[1].DefaultCellStyle.BackColor = Color.Beige;
try
{
totalExpense = ExpenseDictionary.Sum(m => Convert.ToDouble(m.Value));
amount = totalFees - totalExpense;
t.Rows.Add(new object[] { " ", " ", "", "TOTAL EXPENSES:", "", "" + totalExpense.ToString("n0"), "", "", "", "" });
}
catch
{
}
// double amount = totalRent - totalExpense;
t.Rows.Add(new object[] { " ", " ", "", "", "PROFIT DECLARATION: ", "" + amount.ToString("n0"), "", "", "", "" });
}
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();
}
}
}
}
}
}
public SchoolInformation GetSchoolData(int SchoolID, int SchoolCategory)
{
SchoolInformation SkuInfo = null;
using (var Con = new SqlConnection(GC.ConnectionString))
{
Con.Open();
Query = "Select * from SchoolsView where id='" + SchoolID + "'";
using (var Com = new SqlCommand(Query, Con))
{
Reader = Com.ExecuteReader();
while (Reader.Read())
{
SkuInfo = new SchoolInformation();
SkuInfo.SchoolId = Convert.ToInt32(Reader["id"].ToString());
SkuInfo.SchoolName = Reader["sch_name"].ToString();
SkuInfo.Moto = Reader["Moto"].ToString();
SkuInfo.RegistrationNumber = Reader["sch_reg_no"].ToString();
SkuInfo.SchoolGeography = new Geography();
SkuInfo.SchoolGeography.Country = new Geography.Countries();
SkuInfo.SchoolGeography.City = new Geography.Cities();
SkuInfo.WebSite = Reader["Website"].ToString();
SkuInfo.SchoolGeography.Country.Name = Reader["Country"].ToString();
SkuInfo.SchoolGeography.City.Name = Reader["City"].ToString();
SkuInfo.SchoolGeography.Town = Reader["Town"].ToString();
if (Reader["Logo"] != DBNull.Value)
{
byte[] LogoByte = (byte[])(Reader["Logo"]);
SkuInfo.LogoString = Convert.ToBase64String(LogoByte);
MemoryStream ms = new MemoryStream(LogoByte);
SkuInfo.Logo = new Bitmap(ms);
}
else
{
SkuInfo.Logo = null;
}
List <Classes> MyClass = null;
MyClass = new List <Classes>();
string ClassesQuery = "select * from ClassesView where School_id='" + SchoolID + "'";
SkuInfo.ClassInformation = new List <Classes>();
using (var Con2 = new SqlConnection(GC.ConnectionString))
{
Con2.Open();
using (var Command = new SqlCommand(ClassesQuery, Con2))
{
Reader2 = Command.ExecuteReader();
while (Reader2.Read())
{
Classes cs = new Classes();
cs.ClassId = Convert.ToInt32(Reader2["id"].ToString());
cs.ClassName = Reader2["class_name"].ToString();
MyClass.Add(cs);
}
Command.Dispose();
}
Con2.Close();
}
SkuInfo.ClassInformation = MyClass;
}
Com.Dispose();
}
Con.Close();
}
return(SkuInfo);
}