// удалить запись
private void DeleteBtn_Click(object sender, EventArgs e)
{
DialogResult result = MessageBox.Show("Вы точно хотите удалить запись?", "Подтверждение",
MessageBoxButtons.YesNo, MessageBoxIcon.Information,
MessageBoxDefaultButton.Button1,
MessageBoxOptions.DefaultDesktopOnly);
if (result == DialogResult.Yes)
{
try
{
string id = dataGridView1.CurrentCell.Value.ToString();
Database database = new Database();
DataTable table = new DataTable();
MySqlDataAdapter adapter = new MySqlDataAdapter();
MySqlCommand command = new MySqlCommand();
MySqlCommand output = new MySqlCommand();
if (Helper == 1)
{
command = new MySqlCommand("delete from organizations where id_org = @id;", database.getConnection());
output = new MySqlCommand("select * from organizations", database.getConnection());
}
else if (Helper == 2)
{
command = new MySqlCommand("delete from regions where id_org = @id;", database.getConnection());
output = new MySqlCommand("select * from regions", database.getConnection());
}
else if (Helper == 3)
{
command = new MySqlCommand("delete from streets where id_org = @id;", database.getConnection());
output = new MySqlCommand("select * from streets", database.getConnection());
}
else if (Helper == 4)
{
command = new MySqlCommand("delete from accounts where id_org = @id;", database.getConnection());
output = new MySqlCommand("select * from accounts", database.getConnection());
}
else if (Helper == 5)
{
command = new MySqlCommand("delete from roles where id_org = @id;", database.getConnection());
output = new MySqlCommand("select * from roles", database.getConnection());
}
command.Parameters.Add("@id", MySqlDbType.Text).Value = id;
adapter.SelectCommand = command;
adapter.Fill(table);
adapter.SelectCommand = output;
adapter.Fill(table);
dataGridView1.DataSource = table;
}
catch (Exception a)
{
MessageBox.Show(a.Message);
}
}
}
// Сброс фильтра
private void button5_Click(object sender, EventArgs e)
{
comboBox1.SelectedIndex = comboBox1.Items.IndexOf(0);
comboBox2.SelectedIndex = comboBox2.Items.IndexOf(0);
textBox1.Text = "";
Database database = new Database();
DataTable table = new DataTable();
MySqlDataAdapter adapter = new MySqlDataAdapter();
MySqlCommand command = new MySqlCommand();
if (Helper == 1)
{
command = new MySqlCommand("select * from organizations", database.getConnection());
}
else if (Helper == 2)
{
command = new MySqlCommand("select * from regions", database.getConnection());
}
else if (Helper == 3)
{
command = new MySqlCommand("select * from streets", database.getConnection());
}
else if (Helper == 4)
{
command = new MySqlCommand("select * from accounts", database.getConnection());
}
else if (Helper == 5)
{
command = new MySqlCommand("select * from roles", database.getConnection());
}
adapter.SelectCommand = command;
adapter.Fill(table);
dataGridView1.DataSource = table;
}
public WorkerForm()
{
InitializeComponent();
StartPosition = FormStartPosition.CenterScreen;
// создание элементов выпадающего меню
ToolStripMenuItem VisionColumn = new ToolStripMenuItem("Настройка видимости столбцов");
ToolStripMenuItem ExportExcel = new ToolStripMenuItem("Экспорт в Excel");
// добавление элементов в меню
contextMenuStrip1.Items.AddRange(new[] { VisionColumn, ExportExcel });
// ассоциация контекстного меню с БД
dataGridView1.ContextMenuStrip = contextMenuStrip1;
// установка обработки событий
VisionColumn.Click += VisionColumn_Click;
ExportExcel.Click += ExportExcel_Click;
Database database = new Database();
DataTable table = new DataTable();
MySqlDataAdapter adapter = new MySqlDataAdapter();
MySqlCommand command = new MySqlCommand();
command = new MySqlCommand("select * from organizations", database.getConnection());
adapter.SelectCommand = command;
adapter.Fill(table);
dataGridView1.DataSource = table;
}
private void button1_Click(object sender, EventArgs e)
{
try
{
Database database = new Database();
DataTable table = new DataTable();
MySqlDataAdapter adapter = new MySqlDataAdapter();
name = NameTextBox.Text;
MySqlConnection conn = new MySqlConnection("server=localhost;user=root;database=pp;password=root;");
conn.Open();
MySqlCommand id_reg = new MySqlCommand("select max(id_reg) from regions", conn);
MySqlDataReader reader = id_reg.ExecuteReader();
reader.Read();
id = Convert.ToInt32(reader["max(id_reg)"].ToString()) + 1;
reader.Close();
conn.Close();
string cmd = $"insert into regions value ({id}, '{name}')";
MySqlCommand command = new MySqlCommand(cmd, database.getConnection());
adapter.SelectCommand = command;
adapter.Fill(table);
Close();
}
catch (Exception a)
{
MessageBox.Show(a.ToString());
}
}
// все районы
private void toolStripMenuItem2_Click(object sender, EventArgs e)
{
Database database = new Database();
DataTable table = new DataTable();
MySqlDataAdapter adapter = new MySqlDataAdapter();
MySqlCommand command = new MySqlCommand();
if (Helper == 1)
{
command = new MySqlCommand("select * from organizations", database.getConnection());
}
else
{
command = new MySqlCommand("select * from streets", database.getConnection());
}
adapter.SelectCommand = command;
adapter.Fill(table);
dataGridView1.DataSource = table;
}
// Фильтрация
private void button3_Click(object sender, EventArgs e)
{
try
{
if (textBox1.Text.Length != 0 && comboBox1.SelectedIndex == 0 && comboBox2.SelectedIndex == 0)
{
string parameter1 = comboBox1.SelectedItem.ToString();
string condition = comboBox2.SelectedItem.ToString();
string parameter2 = textBox1.Text;
string TableComm = "";
if (Helper == 1)
{
TableComm = "organizations";
}
else if (Helper == 2)
{
TableComm = "regions";
}
else if (Helper == 3)
{
TableComm = "streets";
}
else if (Helper == 4)
{
TableComm = "accounts";
}
else if (Helper == 5)
{
TableComm = "roles";
}
string cmd = $"select * from {TableComm} where {parameter1} {condition} '{parameter2}'";
Database database = new Database();
DataTable table = new DataTable();
MySqlDataAdapter adapter = new MySqlDataAdapter();
MySqlCommand command = new MySqlCommand(cmd, database.getConnection());
adapter.SelectCommand = command;
adapter.Fill(table);
dataGridView1.DataSource = table;
}
else
{
MessageBox.Show("Введите значения для фильтра");
}
}
catch (Exception a)
{
MessageBox.Show(a.Message);
}
}
// таблица улиц
private void toolStripMenuItem13_Click(object sender, EventArgs e)
{
Helper = 3;
toolStripMenuItem1.Visible = true;
Database database = new Database();
DataTable table = new DataTable();
MySqlDataAdapter adapter = new MySqlDataAdapter();
MySqlCommand command = new MySqlCommand("select * from streets", database.getConnection());
adapter.SelectCommand = command;
adapter.Fill(table);
dataGridView1.DataSource = table;
}
// Таблица организаций
private void toolStripMenuItem11_Click(object sender, EventArgs e)
{
button1.Visible = true; label1.Visible = true; comboBox1.Visible = true;
button2.Visible = true; label2.Visible = true; comboBox2.Visible = true;
button3.Visible = true; label3.Visible = true; textBox1.Visible = true;
button4.Visible = true;
button5.Visible = true;
Helper = 1;
dataGridView1.Height = 522;
dataGridView1.Location = new Point(186, 120);
Database database = new Database();
DataTable table = new DataTable();
MySqlDataAdapter adapter = new MySqlDataAdapter();
MySqlCommand command = new MySqlCommand("select * from organizations", database.getConnection());
adapter.SelectCommand = command;
adapter.Fill(table);
dataGridView1.DataSource = table;
MySqlConnection conn = new MySqlConnection("server = localhost; username = root; password = root; database = pp");
conn.Open();
MySqlCommand command1 = new MySqlCommand("select distinct column_name from information_schema.columns where table_name = 'organizations'", conn);
MySqlDataReader reader = command1.ExecuteReader();
comboBox1.Items.Clear();
while (reader.Read())
{
comboBox1.Items.Add(reader[0].ToString());
}
reader.Close();
conn.Close();
}
private void button1_Click(object sender, EventArgs e)
{
Database database = new Database();
DataTable table = new DataTable();
MySqlDataAdapter adapter = new MySqlDataAdapter();
name = NameTextBox.Text;
MySqlConnection conn = new MySqlConnection("server=localhost;user=root;database=pp;password=root;");
conn.Open();
MySqlCommand id_str = new MySqlCommand("select max(id_street) from streets", conn);
MySqlDataReader readerStr = id_str.ExecuteReader();
readerStr.Read();
id = Convert.ToInt32(readerStr["max(id_street)"].ToString()) + 1;
readerStr.Close();
MySqlCommand id_reg = new MySqlCommand("select id_reg from regions where name_reg = @name_reg", conn);
id_reg.Parameters.Add("@name_reg", MySqlDbType.Text).Value = comboBox1.SelectedItem.ToString();
MySqlDataReader readerReg = id_reg.ExecuteReader();
readerReg.Read();
region = Convert.ToInt32(readerReg["id_reg"].ToString());
readerReg.Close();
conn.Close();
string cmd = $"insert into streets value ({id}, {region}, '{name}')";
MySqlCommand command = new MySqlCommand(cmd, database.getConnection());
adapter.SelectCommand = command;
adapter.Fill(table);
Close();
}
private void button1_Click(object sender, EventArgs e)
{
try
{
Database database = new Database();
DataTable table = new DataTable();
MySqlDataAdapter adapter = new MySqlDataAdapter();
date = DateTime.Today.ToString("d");
name = NameTextBox.Text;
INN = INNtextBox.Text;
dataRegOrg = DataRegOrgTextBox.Text;
commis = CommisTextBox.Text;
mail = MailTextBox.Text;
phone = PhoneTextBox.Text;
NbrStr = NmbStrTextBox.Text;
MySqlConnection conn = new MySqlConnection("server=localhost;user=root;database=pp;password=root;");
conn.Open();
MySqlCommand id_reg = new MySqlCommand("select id_reg from regions where name_reg = @name_reg", conn);
id_reg.Parameters.Add("@name_reg", MySqlDbType.Text).Value = comboBox1.SelectedItem.ToString();
MySqlDataReader reader1 = id_reg.ExecuteReader();
reader1.Read();
region = Convert.ToInt32(reader1["id_reg"].ToString());
reader1.Close();
MySqlCommand id_str = new MySqlCommand("select id_street from streets where name_street = @name_street", conn);
id_str.Parameters.Add("@name_street", MySqlDbType.Text).Value = comboBox2.SelectedItem.ToString();
MySqlDataReader reader2 = id_str.ExecuteReader();
reader2.Read();
str = Convert.ToInt32(reader2["id_street"].ToString());
reader2.Close();
MySqlCommand id_org = new MySqlCommand("select max(id_org) from organizations", conn);
MySqlDataReader reader3 = id_org.ExecuteReader();
reader3.Read();
id = Convert.ToInt32(reader3["max(id_org)"].ToString()) + 1;
reader3.Close();
conn.Close();
LoginForm loginForm = new LoginForm();
if (loginForm.Helper == 1)
{
idAcc = 1;
}
else if (loginForm.Helper == 2)
{
idAcc = 2;
}
string cmd = $"insert into organizations value ({id}, '{name}', '{INN}', '{dataRegOrg}', '{commis}', '{mail}', '{phone}', {region}, {str}, '{NbrStr}', '{date}', {idAcc})";
MySqlCommand command = new MySqlCommand(cmd, database.getConnection());
adapter.SelectCommand = command;
adapter.Fill(table);
Close();
}
catch (Exception a)
{
MessageBox.Show(a.ToString());
}
}
private void buttonLogin_Click(object sender, EventArgs e)
{
try
{
string login = textLogin.Text;
string password = textPass.Text;
Database database = new Database();
DataTable table = new DataTable();
MySqlDataAdapter adapter = new MySqlDataAdapter();
MySqlCommand command = new MySqlCommand("SELECT * from `Accounts` WHERE `login` = @login AND `password` = @password", database.getConnection());
MySqlCommand permission = new MySqlCommand("SELECT `id_role` FROM `Accounts` WHERE `login` = @login AND `password` = @password", database.getConnection());
command.Parameters.Add("@login", MySqlDbType.Text).Value = login;
command.Parameters.Add("@password", MySqlDbType.Text).Value = password;
permission.Parameters.Add("@login", MySqlDbType.Text).Value = login;
permission.Parameters.Add("@password", MySqlDbType.Text).Value = password;
adapter.SelectCommand = command;
adapter.Fill(table);
if (table.Rows.Count > 0)
{
adapter.SelectCommand = permission;
adapter.Fill(table);
if (table.Rows[0][1].ToString() == "1")
{
Hide();
AdminForm adminForm = new AdminForm();
adminForm.Show();
Helper = 1;
}
else
{
Hide();
WorkerForm workerForm = new WorkerForm();
workerForm.Show();
Helper = 2;
}
textLogin.Clear();
textPass.Clear();
textLogin.Select();
}
else
{
MessageBox.Show("Неверный логин или пароль.");
}
}
catch (Exception a)
{
MessageBox.Show(a.Message);
}
}